Hi,

In the LATAM region, we have approximately 200,000 customer sites. As part of a custom process, a BIP report is triggered individually for each customer to retrieve their details, which are required by various boundary systems. However, this approach is causing performance issues on the BIP server due to the high volume of requests.
Currently, we use the following query to fetch customer data. Implementing a REST API to return these details would be more efficient and scalable than invoking the BIP report for each customer.

/* Formatted on 9/30/2025 11:26:23 AM (QP5 v5.417) */
SELECT DISTINCT
hp.status
party_status,
hca.cust_account_id,
NULL
cust_acct_site_id,
(SELECT fabu.bu_name
FROM fun_all_business_units_v fabu,
ar_ref_accounts_all arca
WHERE 1 = 1
AND arca.source_ref_account_id = hcsua.site_use_id
AND arca.source_ref_table = 'HZ_CUST_SITE_USES_ALL'
AND arca.bu_id = fabu.bu_id)
business_unit,
(SELECT fabu.bu_id
FROM fun_all_business_units_v fabu,
ar_ref_accounts_all arca
WHERE 1 = 1
AND arca.source_ref_account_id = hcsua.site_use_id
AND arca.source_ref_table = 'HZ_CUST_SITE_USES_ALL'
AND arca.bu_id = fabu.bu_id)
business_unit_id,
hl.address1
address1,
hl.address2
address2,
hl.address3
address3,
hl.address4
address4,
hl.county
county,
'US'
region,
hl.city
city,
hl.province
province,
hl.state
state,
(SELECT ft.territory_short_name --ISO_TERRITORY_CODE -- BDEV-450
FROM fnd_territories_vl ft
WHERE ft.territory_code = hl.country)
country_name,
hl.country
country_code,
hl.postal_code
postalcode,
hcsua.site_use_code
site_use_code,
NULL
location,
hps.party_site_number
primary_billto,
'EPS'
profit_center_org,
NULL
total_credit_exposure_local,
NULL
total_credit_exposure_global,
NVL (hcp.credit_hold, 'N')
credit_hold,
NVL (
(SELECT flv.description
FROM fnd_lookup_values flv
WHERE flv.lookup_type = 'SWC_AR_HRC_CUST_MASTER_MAPPING'
AND language = 'US'
AND flv.enabled_flag = 'Y'
AND flv.lookup_code = :p_ca_person_name),
(SELECT pea.email_address
FROM per_email_addresses pea, per_person_names_f ppf
WHERE 1 = 1
AND pea.email_type = 'W1'
AND hcp.credit_analyst_id = ppf.person_id
AND SYSDATE BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND ppf.name_type = 'GLOBAL'
AND ppf.person_id = pea.person_id))
ca_email,
NVL (
(SELECT flv.description
FROM fnd_lookup_values flv
WHERE flv.lookup_type = 'SWC_AR_HRC_CUST_MASTER_MAPPING'
AND language = 'US'
AND flv.enabled_flag = 'Y'
AND flv.lookup_code = :p_person_name),
(SELECT pea.email_address
FROM ar_collectors ac, per_email_addresses pea
WHERE 1 = 1
AND ac.employee_id = pea.person_id
AND pea.email_type = 'W1'
AND ac.collector_id = hcp.collector_id
AND ac.status = 'A'))
collector_email,
NVL (
(SELECT 'Y'
FROM fnd_lookup_values
WHERE lookup_type = 'SWC_AR_EDI_820_CUSTOMERS'
AND lookup_code = hca.account_number
AND NVL (enabled_flag, 'N') = 'Y'),
'N')
AS edi_customer,
(SELECT flv.description
FROM fnd_lookup_values flv
WHERE lookup_type = 'SWC_AR_HRC_CUST_MASTER_MAPPING'
AND language = 'US'
AND meaning = 'TREND_LINK'
AND flv.enabled_flag = 'Y'
AND enabled_flag = 'Y')
|| hp.party_id
AS trend_report_link,
(SELECT DISTINCT b.meaning
FROM fnd_territories a, fnd_lookup_values_vl b
WHERE b.meaning = a.nls_territory
AND a.territory_code = hl.country
AND b.lookup_type = 'SWC_AR_HRC_CUST_MASTER_MAPPING')
due_diligence_flag,
(SELECT NVL (fl.nls_language, '') language
FROM fnd_languages fl
WHERE fl.language_code = hcasa.acct_site_language)
preferred_language,
NULL
vat_number,
NVL (
(SELECT flv.description
FROM fnd_lookup_values flv
WHERE flv.lookup_type = 'SWC_AR_HRC_CUST_MASTER_MAPPING'
AND language = 'US'
AND flv.enabled_flag = 'Y'
AND flv.lookup_code = :p_da_person_name),
(SELECT pea.email_address
FROM ar_collectors ac, per_email_addresses pea
WHERE 1 = 1
AND ac.employee_id = pea.person_id
AND pea.email_type = 'W1'
AND ac.name = :p_da_person_name
AND ac.status = 'A'))
deduction_email,
'ACCOUNT'
data_level
FROM hz_parties hp,
hz_cust_accounts hca,
hz_cust_acct_sites_all hcasa,
hz_cust_site_uses_all hcsua,
hz_party_sites hps,
hz_locations hl,
hz_customer_profiles_f hcp
WHERE 1 = 1
AND hp.party_id = hca.party_id
AND hca.account_number = :p_account_num
AND hca.cust_account_id = hcasa.cust_account_id
AND hcasa.party_site_id = hps.party_site_id
AND hp.party_id = hps.party_id
AND hcasa.cust_acct_site_id = hcsua.cust_acct_site_id
AND hcsua.site_use_code = 'BILL_TO'
AND hcsua.primary_flag = 'Y'
AND hps.location_id = hl.location_id
AND hps.party_site_number = :p_primary_billto
AND hca.cust_account_id = hcp.cust_account_id(+)
AND hcp.site_use_id IS NULL
UNION ALL
SELECT hp.status
party_status,
hca.cust_account_id,
hcasa.cust_acct_site_id,
CASE
WHEN hcsua.site_use_code = 'BILL_TO'
THEN
(SELECT fabu.bu_name
FROM fun_all_business_units_v fabu,
ar_ref_accounts_all arca
WHERE 1 = 1
AND arca.source_ref_account_id = hcsua.site_use_id
AND arca.source_ref_table = 'HZ_CUST_SITE_USES_ALL'
AND arca.bu_id = fabu.bu_id)
ELSE
(SELECT fabu.bu_name
FROM fun_all_business_units_v fabu,
ar_ref_accounts_all arca,
hz_cust_site_uses_all hcsua_bill
WHERE 1 = 1
AND hcsua_bill.site_use_id = hcsua.bill_to_site_use_id
AND arca.source_ref_account_id =
hcsua_bill.site_use_id
AND arca.source_ref_table = 'HZ_CUST_SITE_USES_ALL'
AND arca.bu_id = fabu.bu_id)
END
business_unit,
CASE
WHEN hcsua.site_use_code = 'BILL_TO'
THEN
(SELECT fabu.bu_id
FROM fun_all_business_units_v fabu,
ar_ref_accounts_all arca
WHERE 1 = 1
AND arca.source_ref_account_id = hcsua.site_use_id
AND arca.source_ref_table = 'HZ_CUST_SITE_USES_ALL'
AND arca.bu_id = fabu.bu_id)
ELSE
(SELECT fabu.bu_id
FROM fun_all_business_units_v fabu,
ar_ref_accounts_all arca,
hz_cust_site_uses_all hcsua_bill
WHERE 1 = 1
AND hcsua_bill.site_use_id = hcsua.bill_to_site_use_id
AND arca.source_ref_account_id =
hcsua_bill.site_use_id
AND arca.source_ref_table = 'HZ_CUST_SITE_USES_ALL'
AND arca.bu_id = fabu.bu_id)
END
business_unit_id,
hl.address1
address1,
hl.address2
address2,
hl.address3
address3,
hl.address4
address4,
hl.county
county,
'NOAM'
region,
hl.city
city,
hl.province
province,
hl.state
state,
(SELECT ft.territory_short_name --ISO_TERRITORY_CODE -- BDEV-450
FROM fnd_territories_vl ft
WHERE ft.territory_code = hl.country)
country_name,
hl.country
country_code,
hl.postal_code
postalcode,
hcsua.site_use_code
site_use_code,
hps.party_site_number
location,
CASE
WHEN hcsua.site_use_code = 'BILL_TO'
THEN
(SELECT hps_bill.party_site_number
FROM hz_party_sites hps_bill,
hz_cust_acct_sites_all hcasa_prim_bill
WHERE hps_bill.party_site_id =
hcasa_prim_bill.party_site_id
AND hcasa_prim_bill.cust_account_id =
hca.cust_account_id
AND hcasa_prim_bill.set_id = hcasa.set_id
AND hcasa_prim_bill.bill_to_flag = 'P')
ELSE
NULL
END
primary_billto,
NULL
profit_center_org,
NULL
total_credit_exposure_local,
NULL
total_credit_exposure_global,
NVL (hcp.credit_hold, 'N')
credit_hold,
NULL
collector_email,
NULL
ca_email,
NVL (
(SELECT 'Y'
FROM fnd_lookup_values
WHERE lookup_type = 'SWC_AR_EDI_820_CUSTOMERS'
AND lookup_code = hca.account_number
AND NVL (enabled_flag, 'N') = 'Y'),
'N')
AS edi_customer,
(SELECT flv.description
FROM fnd_lookup_values flv
WHERE lookup_type = 'SWC_AR_HRC_CUST_MASTER_MAPPING'
AND language = 'US'
AND meaning = 'TREND_LINK'
AND flv.enabled_flag = 'Y'
AND enabled_flag = 'Y')
|| hp.party_id
AS trend_report_link,
(SELECT DISTINCT b.meaning
FROM fnd_territories a, fnd_lookup_values_vl b
WHERE b.meaning = a.nls_territory
AND a.territory_code = hl.country
AND b.lookup_type = 'SWC_AR_HRC_CUST_MASTER_MAPPING')
due_diligence_flag,
(SELECT NVL (fl.nls_language, '') language
FROM fnd_languages fl
WHERE fl.language_code = hcasa.acct_site_language)
preferred_language,
(SELECT zptp.rep_registration_number --zr.registration_number
FROM zx_party_tax_profile zptp
--, zx_registrations zr
WHERE zptp.party_type_code = 'THIRD_PARTY_SITE'
AND zptp.party_id = hps.party_site_id
AND zptp.site_flag = 'Y'-- AND zptp.party_tax_profile_id = zr.party_tax_profile_id(
--AND TRUNC (SYSDATE) BETWEEN zr.effective_from AND NVL (zr.effective_to, SYSDATE + 1)
)
vat_number,
NULL
deduction_email,
'SITE'
data_level
FROM hz_parties hp,
hz_cust_accounts hca,
hz_cust_acct_sites_all hcasa,
hz_cust_site_uses_all hcsua,
hz_party_sites hps,
hz_locations hl,
hz_customer_profiles_f hcp
WHERE 1 = 1
AND hp.party_id = hca.party_id
AND hca.account_number = :p_account_num
AND hca.cust_account_id = hcasa.cust_account_id
AND hcasa.party_site_id = hps.party_site_id
AND hp.party_id = hps.party_id
AND hcasa.cust_acct_site_id = hcsua.cust_acct_site_id
AND hps.location_id = hl.location_id
AND hca.cust_account_id = hcp.cust_account_id
AND hcp.site_use_id IS NULL
AND SYSDATE BETWEEN hcp.effective_start_date
AND hcp.effective_end_date