In this post you’ll see query to get supplier site assignment details in Oracle ERP Fusion Application, this query has some important columns of supplier sites like Supplier name, supplier site, business unit, bill to, ship to location, liability, I hope this query will help you to get supplier site related information in Oracle Cloud.
SELECT
hp.party_name supplier_name,
pss.vendor_site_code supplier_site,
hou.name business_unit,
(SELECT
hou1.name
FROM
hr_organization_units hou1
WHERE
psa.bu_id = hou1.organization_id
) bill_to_bu,
(
SELECT
hl.location_code
FROM
hr_locations hl
WHERE
hl.location_id = psa.ship_to_location_id
AND nvl(hl.active_status,’A’) = ‘A’
AND trunc(SYSDATE) BETWEEN trunc(nvl(hl.effective_start_date,SYSDATE) ) AND trunc(nvl(hl.effective_end_date,SYSDATE) )
) ship_to_location,
(
SELECT
hl.location_code
FROM
hr_locations hl
WHERE
hl.location_id = psa.bill_to_location_id
AND nvl(hl.active_status,’A’) = ‘A’
AND trunc(SYSDATE) BETWEEN trunc(nvl(hl.effective_start_date,SYSDATE) ) AND trunc(nvl(hl.effective_end_date,SYSDATE) )
) bill_to_location,
psa.allow_awt_flag use_withholding_tax,
(
SELECT
( segment1
|| ‘.’
|| segment2
|| ‘.’
|| segment3
|| ‘.’
|| segment4
|| ‘.’
|| segment5
|| ‘.’
|| segment6
|| ‘.’
|| segment7
|| ‘.’
|| segment8 )
FROM
gl_code_combinations
WHERE
code_combination_id = psa.accts_pay_code_combination_id
) liability
FROM
poz_suppliers ps,
poz_supplier_sites_all_m pss,
hz_parties hp,
poz_site_assignments_all_m psa,
hz_party_sites hps,
hr_organization_units hou
WHERE
ps.vendor_id = pss.vendor_id
AND ps.party_id = hp.party_id
AND pss.vendor_site_id = psa.vendor_site_id
AND pss.party_site_id = hps.party_site_id
AND pss.prc_bu_id = hou.organization_id
AND nvl(ps.enabled_flag,’Y’) = ‘Y’
AND trunc(SYSDATE) BETWEEN trunc(nvl(ps.start_date_active,SYSDATE) ) AND trunc(nvl(ps.end_date_active,SYSDATE) )
AND trunc(SYSDATE) BETWEEN trunc(nvl(psa.effective_start_date,SYSDATE) ) AND trunc(nvl(psa.effective_end_date,SYSDATE) )
AND trunc(nvl(pss.inactive_date,SYSDATE) ) >= trunc(SYSDATE)
AND trunc(nvl(psa.inactive_date,SYSDATE) ) >= trunc(SYSDATE)
AND trunc(SYSDATE) BETWEEN trunc(nvl(hps.start_date_active,SYSDATE) ) AND trunc(nvl(hps.end_date_active,SYSDATE) )
AND nvl(hps.status,’A’) = ‘A’
and psa.bu_id is not null