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