SQL Query to get the Supplier Site Assignment - Oracle Fusion.

 Here is the generic SQL Query to get the Supplier Site Assignment details as per the FBDI template in the Oracle Fusion.

select

  psv.vendor_name,

  pssv.vendor_site_code,

  ( select  bu_name from fun_all_business_units_v where bu_id=pssam.prc_bu_id) procurement_bu,

  ( select  bu_name from fun_all_business_units_v where bu_id=psaam.bu_id) client_bu,

  ( select  bu_name from fun_all_business_units_v where bu_id=psaam.bill_to_bu_id) bill_tobu,

  hla_ship.location_code,

  hla_bill.location_code,

  psaam.allow_awt_flag,

  psaam.awt_group_id,

  gcc.segment1||'.'||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4||'.'||gcc.segment5||'.'||gcc.segment6||'.'||gcc.segment7||'.'||gcc.segment8 liability_distribution,

  gcc2.segment1||'.'||gcc2.segment2||'.'||gcc2.segment3||'.'||gcc2.segment4||'.'||gcc2.segment5||'.'||gcc2.segment6||'.'||gcc2.segment7||'.'||gcc2.segment8  prepayment_distribution,

  gcc1.segment1||'.'||gcc1.segment2||'.'||gcc1.segment3||'.'||gcc1.segment4||'.'||gcc1.segment5||'.'||gcc1.segment6||'.'||gcc1.segment7||'.'||gcc1.segment8 bill_payable_distribution,

  psaam.inactive_date,

  psaam.distribution_set_id distribution_name

 from   poz_suppliers_v psv,

        poz_supplier_sites_v pssv,

poz_site_assignments_all_m psaam,

poz_supplier_sites_all_m pssam,

hr_locations   hla_ship,

hr_locations   hla_bill,

gl_code_combinations  gcc,

gl_code_combinations  gcc1,

gl_code_combinations  gcc2,

fun_all_business_units_v fabuv

where psv.vendor_id             = pssv.vendor_id

and   psaam.vendor_site_id      = pssv.vendor_site_id

and   pssam.vendor_site_id      = psaam.vendor_site_id

and   hla_ship.location_id(+)   = psaam.ship_to_location_id

and   hla_bill.location_id (+)  = psaam.bill_to_location_id

and   gcc.code_combination_id(+)  = psaam.accts_pay_code_combination_id

and   gcc1.code_combination_id(+) = psaam.future_dated_payment_ccid

and   gcc2.code_combination_id(+) = psaam.prepay_code_combination_id

and   fabuv.bu_id                 = pssam.prc_bu_id

SQL Query to get the Supplier Contact Details - Oracle Fusion.

 Here is the generic SQL Query to get the Supplier Contact details as per the FBDI template in Oracle fusion.

select 

hpo.party_name  supplier_name,

hpp.person_pre_name_adjunct,

hpp.person_first_name,

hpp.person_middle_name,

hpp.person_last_name,

hc.job_title,

hps.party_site_name,

hor.role_type,

email.email_address,

phone.phone_country_code,

phone.phone_area_code,

phone.phone_number,

phone.phone_extension,

fax.fax_country_code,

fax.fax_area_code,

fax.fax,

mobile.mobile_country_code,

mobile.mobile_area_code,

mobile.mobile,

poc.inactive_date

FROM

hz_parties hpo,

hz_relationships hzr,

hz_parties hpp,

hz_org_contacts hc,

hz_org_contact_roles hor,

poz_supplier_contacts poc,

hz_party_sites hps,

poz_suppliers ps,

(select email_address, owner_table_id from

hz_contact_points hcp

where

hcp.contact_point_type(+)='email'

and hcp.owner_table_name='hz_parties') email,

(select phone_country_code,phone_area_code,phone_number,phone_extension,owner_table_id from

hz_contact_points hcp

where

hcp.contact_point_type(+)='phone'

and hcp.owner_table_name='hz_parties'

and hcp.phone_line_type(+) = 'gen') phone,

(select phone_country_code fax_country_code,phone_area_code fax_area_code,phone_number fax,owner_table_id from

hz_contact_points hcp

where

hcp.contact_point_type(+)='phone'

and hcp.owner_table_name='hz_parties'

and hcp.phone_line_type(+) = 'fax') fax,

(select phone_country_code mobile_country_code,phone_area_code mobile_area_code, phone_number mobile,owner_table_id from

hz_contact_points hcp

where

hcp.contact_point_type(+)='phone'

and hcp.owner_table_name='hz_parties'

and hcp.phone_line_type(+) = 'mobile') mobile

where

hpo.party_type='organization'

and hzr.object_id=hpo.party_id

and hpp.party_type='person'

and hpp.party_id=hzr.subject_id

and hc.party_relationship_id=hzr.relationship_id

and hps.party_site_id(+) =  poc.party_site_id

and hzr.relationship_id = poc.relationship_id(+)

and hor.org_contact_id(+) = hc.org_contact_id

and ps.party_id=hpo.party_id

and email.owner_table_id(+)=hpp.party_id

and phone.owner_table_id(+)=hpp.party_id

and fax.owner_table_id(+)=hpp.party_id

and mobile.owner_table_id(+)=hpp.party_id

and hzr.relationship_code = 'contact_of'

SQL Query to get the Supplier Address Details - Oracle Fusion.

Here is the generic Suppliers Address Details as per the FBDI Template.

SELECT  

  hp.party_name 

         ,psa.party_site_name AddressName

  ,psa.country

  ,psa.address1

  ,psa.address2

  ,psa.address3

  ,psa.address4

  ,psa.address_lines_phonetic

  ,psa.addr_element_attribute1

  ,psa.addr_element_attribute2

  ,psa.addr_element_attribute3

  ,psa.addr_element_attribute4

  ,psa.addr_element_attribute5

  ,psa.building

  ,psa.floor_number

  ,psa.city

  ,psa.state

  ,psa.province

  ,psa.county

  ,psa.postal_code

  ,psa.postal_plus4_code

  ,psa.location_language

  ,psa.inactive_date

  ,psa.phone_country_code

  ,psa.phone_area_code

  ,psa.phone_number

  ,psa.phone_extension

  ,psa.fax_country_code

  ,psa.fax_phone_area_code

  ,psa.fax_phone_number

  ,psa.address_purpose_rfq_or_bidding

  ,psa.address_purpose_ordering

  ,psa.address_purpose_remit_to

,psa.email_address  email

FROM  poz_suppliers pos

     ,hz_parties hp

     ,poz_supplier_address_v psa 

, hz_party_sites hps

WHERE pos.party_id        = hp.party_id

  AND pos.vendor_id       = psa.vendor_id

  AND psa.party_id        = pos.party_id

  and hps.party_site_id = psa.party_site_id

SQL Query to get the Supplier Site Details - Oracle Fusion.

Here is the generic Supplier Site Details as per the FBDI template in the Oracle fusion. 

select

psv.vendor_name 

,pssam.vendor_site_id

, pssv.vendor_site_code 

,( select  bu_name from fun_all_business_units_v where bu_id=pssv.prc_bu_id) procurement_bu

, psv.vendor_name_alt 

, pssv.party_site_name 

, pssv.rfq_only_site_flag 

, pssv.purchasing_site_flag 

, pssv.pcard_site_flag 

, pssv.pay_site_flag 

, pssv.primary_pay_site_flag 

, pssam.fax_country_code 

, pssam.fax_area_code

, pssam.fax fax

, pssv.inactive_date

, pssv.customer_num 

, pssam.b2b_site_code 

, wcv.carrier_name  

, pssv.mode_of_transport 

, pssv.service_level 

, pssv.freight_terms_lookup_code 

, pssv.fob_lookup_code 

, pssv.terms_date_basis 

, pssv.pay_group_lookup_code 

, pssv.payment_priority 

, term.name 

, pssv.invoice_amount_limit 

, pssv.pay_date_basis_lookup_code 

, pssam.bank_charge_deduction_type 

, pssv.always_take_disc_flag 

, pssv.invoice_currency_code 

, pssv.payment_currency_code 

, pssv.hold_all_payments_flag 

, pssv.hold_future_payments_flag 

, pssv.hold_unmatched_invoices_flag 

, pssv.hold_reason 

, pssv.hold_by 

, pssv.payment_hold_date

, pssv.hold_flag 

, pssv.purchasing_hold_reason

, pssv.auto_calculate_interest_flag

, pssv.tax_reporting_site_flag 

, pssv.exclude_freight_from_discount

, pssv.pay_on_code

, pssam.default_pay_site_id

, pssv.pay_on_receipt_summary_code

, pssv.match_option 

, pssv.country_of_origin_code 

, pssv.pay_on_use_flag 

, pssv.aging_onset_point

, pssv.aging_period_days

, pssv.consumption_advice_frequency

, pssv.consumption_advice_summary

, pssv.create_debit_memo_flag

, pssv.supplier_notif_method

, pssv.email_address

, att.tolerance_name quantitytolerances

, at.tolerance_name amount_tolerance

, pssv.gapless_inv_num_flag

, pssv.selling_company_identifier 

, iepa.bank_charge_bearer 

, iepa.bank_instruction1_code

, iepa.bank_instruction2_code

, iepa.bank_instruction_details

, iepa.payment_reason_code

, iepa.payment_reason_comments

, iepa.delivery_channel_code

, iepa.settlement_priority

, iepa. payment_text_message1

, iepa.payment_text_message2

, iepa.payment_text_message3

, ieppm.payment_method_code

, pssv.allow_substitute_receipts_flag

, pssv.allow_unordered_receipts_flag 

, pssv.enforce_ship_to_location_code 

, pssv.qty_rcv_exception_code 

, pssv.receipt_days_exception_code 

, pssv.days_early_receipt_allowed

, pssv.days_late_receipt_allowed

, decode (pssv.receipt_required_flag,

              'y', decode(pssv.inspection_required_flag,

                          'y', '4-way',

                          'n', '3-way'),

                          'n','2-way',

                          null  

              )  matchapprovallevel

, pssam.exclude_tax_from_discount

, pssv.receiving_routing_id

, pssam.vat_code

, pssam.vat_registration_num

, iepa.remit_advice_delivery_method

, iepa. remit_advice_email 

from poz_suppliers_v psv,

poz_supplier_sites_v  pssv,

poz_supplier_sites_all_m pssam,

ap_tolerance_templates att,

ap_tolerance_templates at,

iby_external_payees_all iepa,

iby_ext_party_pmt_mthds ieppm,

wsh_carriers_v wcv,

ap_terms term,

fun_all_business_units_v fabv 

where pssv.vendor_id=psv.vendor_id

and pssam.vendor_site_id=pssv.vendor_site_id

and term.term_id(+) = pssv.terms_id

and pssam.vendor_site_id(+)= iepa.supplier_site_id

and att.tolerance_id(+) = pssam.tolerance_id

and at.tolerance_id (+) = pssam.services_tolerance_id

and ieppm.ext_pmt_party_id(+)=iepa.ext_payee_id

and wcv.carrier_id(+) = pssam.carrier_id

and pssam.prc_bu_id = fabv.bu_id

SQL Query to get the Suppliers Details - Oracle fusion.

Here is the generic SQL Query to fetch the basic Suppliers Details as per the Oracle FBDI template.  

SELECT pos.vendor_name                                          

      ,NULL                                                     

      ,pos.segment1                                             

      ,pos.vendor_name_alt                                      

      ,ps.organization_type_lookup_code                         

      ,pos.vendor_type_lookup_code                              

      ,pos.end_date_active

      ,pos.business_relationship

      ,hp1.party_name                                           

      ,alias.party_name                                         

      ,pos.duns_number_c                                        

      ,ps.one_time_flag                                         

      ,ps.customer_num                                         

      ,ps.standard_industry_class                             

      ,pS.ni_number                                             

      ,pos.corporate_website

      ,hop.ceo_title chief_executive_title_chk

      ,hp.ceo_name  chief_executive_name_chk

      ,ps.bc_not_applicable_flag

      ,ps.taxpayer_country

  ,psi.income_tax_id taxpayer_id

      ,ps.federal_reportable_flag                               

      ,ps.type_1099                                             

      ,ps.state_reportable_flag                                 

      ,ps.tax_reporting_name

      ,ps.name_control

      ,TO_CHAR(ps.tax_verification_date,'YYYY/MM/DD')           

      ,ps.allow_awt_flag                                        

      ,(select ZW.name from ZX_WHT_TAX_CLASSIFICATION_V ZW

      where   ZW.group_id = pos.awt_group_id)               

      ,ps.vat_code

      ,(select ZT.REP_REGISTRATION_NUMBER  from ZX_PARTY_TAX_PROFILE ZT  

           where   ZT.party_id= ps.party_id)              

      ,ps.auto_tax_calc_override

      ,ibext.payment_method_code                                

      ,ibp.delivery_channel_code                             

      ,ibp.bank_instruction1_code                             

      ,ibp.bank_instruction2_code                             

      ,ibp.bank_instruction_details                             

      ,ibp.settlement_priority

      ,ibp.payment_text_message1

      ,ibp.payment_text_message2

      ,ibp.payment_text_message3

      ,ibp.bank_charge_bearer

      ,ibp.payment_reason_code                                 

      ,ibp.payment_reason_comments                             

      ,ibp.payment_format_code                                 

  FROM poz_suppliers ps,

       poz_suppliers_v pos,

       poz_supplier_registrations psr,

   poz_suppliers_pii psi,

       hz_organization_profiles hop,

       iby_external_payees_all ibp,

       iby_ext_party_pmt_mthds ibext,

       hz_parties HP,

       hz_parties HP1,

       (SELECT party_name

              ,party_id 

          FROM hz_addtnl_party_names hap2

         WHERE hap2.party_name_type='ALIAS') ALIAS 

 WHERE 1 = 1 

   AND ps.vendor_id                                 = pos.vendor_id

   AND ps.vendor_id                                 = psr.vendor_id(+)

   and ps.vendor_id                                 = psi.vendor_id(+)

   AND hp.party_id                                  = alias.party_id(+)

   AND hp1.party_id(+)                              = ps.parent_party_id

   AND hop.party_id(+)                              = ps.party_id

   AND pos.party_id                                 = hp.party_id(+)

   AND ps.party_id                                  = hp.party_id(+) 

   AND ps.party_id                                  = ibp.payee_party_id(+)

   AND pos.party_id                                 = ibp.payee_party_id(+)

   AND ibp.ext_payee_id                             = ibext.ext_pmt_party_id(+)

   AND hp.party_id                                  = ibp.payee_party_id(+)

   AND ibp.party_site_id is null