-- Supplier
-----------------------------------------------------------------
s.PARTY_ID,
s.segment1 supp_number,
s.vendor_name supp_name,
TRUNC
(s.creation_date)
supp_creation_date,
s_cr.user_name supp_created_by,
TRUNC
(s.last_update_date)
supp_update_date,
s_upd.user_name supp_updated_by,
(SELECT
plc.displayed_field
FROM PO_LOOKUP_CODES plc
WHERE plc.lookup_type
= 'VENDOR TYPE'
AND plc.lookup_code =
s.vendor_type_lookup_code) supp_type,
s.pay_group_lookup_code
supp_pay_group,
s.organization_type_lookup_code supp_org_type,
s.standard_industry_class
supp_sic,
s.tca_sync_num_1099 supp_tax_id,
-----------------------------------------------------------------
-- Business Classification
-----------------------------------------------------------------
(SELECT LISTAGG
(lv.meaning, ', ') WITHIN GROUP (ORDER BY lv.meaning)
FROM POS_BUS_CLASS_ATTR bc,
FND_LOOKUP_VALUES lv
WHERE bc.lookup_code
= lv.lookup_code (+)
AND bc.class_status = 'APPROVED'
AND lv.lookup_type = 'POS_BUSINESS_CLASSIFICATIONS'
AND lv.enabled_flag = 'Y'
AND TRUNC (SYSDATE) BETWEEN TRUNC
(lv.start_date_active) AND TRUNC (NVL (lv.end_date_active, SYSDATE+1))
AND TRUNC (SYSDATE) BETWEEN TRUNC
(bc.start_date_active) AND TRUNC (NVL (bc.end_date_active, SYSDATE+1))
AND bc.party_id (+) = s.party_id
) bus_class,
-----------------------------------------------------------------
-- Supplier Site
-----------------------------------------------------------------
st.vendor_site_code site_code,
TRUNC
(st.creation_date) site_creation_date,
st_cr.user_name site_created_by,
TRUNC
(st.last_update_date)
site_update_date,
st_upd.user_name site_updated_by,
st.pay_group_lookup_code
site_pay_group,
st.pay_site_flag pay_site_flag,
st.purchasing_site_flag
purchasing_site_flag,
(SELECT
territory_short_name
FROM FND_TERRITORIES_VL v
WHERE territory_code
= st.country) site_country,
st.state site_state,
-----------------------------------------------------------------
-- Supplier Site Payment Method
-----------------------------------------------------------------
pm.payment_method_code payment_method,
pm.remit_advice_delivery_method
delivery_method,
pm.remit_advice_email remittance_email,
-----------------------------------------------------------------
-- Supplier Site Bank Account, Bank and Bank Branches
-----------------------------------------------------------------
ba.bank_account_num bank_account_num,
ba.start_date bank_account_start_date,
ba.branch_name branch_name,
ba.branch_number branch_number,
ba.bank_name bank_name,
ba.bank_number bank_number,
-----------------------------------------------------------------
-- Bank Account Owner Name
-----------------------------------------------------------------
(SELECT hp.party_name
FROM HZ_PARTIES hp,
IBY_ACCOUNT_OWNERS iao
WHERE iao.account_owner_party_id
= hp.party_id
AND iao.ext_bank_account_id =
ba.ext_bank_account_id
AND iao.primary_flag = 'Y') account_owner_name
FROM AP_SUPPLIERS s,
AP_SUPPLIER_SITES_ALL st,
--------------------------------------------
-- bank acocunt query
--------------------------------------------
(SELECT
iep.supplier_site_id,
ipi.start_date,
ipi.end_date,
iao.ext_bank_account_id,
-- iep.ext_payee_id,
ieb.bank_account_num,
cbbv.bank_branch_name
branch_name,
cbbv.branch_number,
cbbv.bank_name,
cbbv.bank_number
FROM IBY_EXTERNAL_PAYEES_ALL iep,
IBY_PMT_INSTR_USES_ALL ipi,
IBY_ACCOUNT_OWNERS iao,
IBY_EXT_BANK_ACCOUNTS ieb,
CE_BANK_BRANCHES_V cbbv
WHERE
iao.ext_bank_account_id = ieb.ext_bank_account_id(+)
AND iao.ext_bank_account_id(+) = ipi.instrument_id
AND iep.ext_payee_id =
ipi.ext_pmt_party_id(+)
AND iep.payee_party_id =
iao.account_owner_party_id
AND cbbv.branch_party_id(+) =
ieb.branch_id
AND ipi.instrument_type = 'BANKACCOUNT'
AND TRUNC (SYSDATE) < TRUNC (NVL (ipi.end_date, SYSDATE + 1))
AND iep.payment_function = 'PAYABLES_DISB') ba,
--------------------------------------------
-- payment method, remittance email query
--------------------------------------------
(SELECT
ppm.payment_method_code,
iep.remit_advice_delivery_method,
iep.remit_advice_email,
iep.supplier_site_id,
iep.inactive_Date,
ppm.inactive_date
inactive_date2,
MAX (ppm.object_version_number)
FROM IBY_EXTERNAL_PAYEES_ALL iep,
IBY_EXT_PARTY_PMT_MTHDS ppm
WHERE
iep.ext_payee_id = ppm.ext_pmt_party_id
AND ppm.primary_flag = 'Y'
AND NVL (ppm.inactive_date, SYSDATE+1) >= SYSDATE
AND NVL (iep.inactive_date, SYSDATE+1) >= SYSDATE
GROUP BY ppm.payment_method_code,
iep.remit_advice_delivery_method,
iep.remit_advice_email,
iep.supplier_site_id,
iep.inactive_Date,
ppm.inactive_date
) pm,
--
FND_USER s_cr,
-- supplier create
FND_USER s_upd,
-- supplier update
FND_USER
st_cr, -- site create
FND_USER st_upd
-- site update
WHERE s.enabled_flag = 'Y'
AND UPPER (NVL (s.vendor_type_lookup_code, 'X')) NOT IN ('EMPLOYEE') -- No Employee supplier type
AND TRUNC (SYSDATE) < TRUNC (NVL (st.inactive_date, SYSDATE + 1)) --
Active Supplier Site
AND TRUNC (SYSDATE) < TRUNC (NVL (s.end_date_active, SYSDATE + 1)) -- Active Supplier
--
AND st.last_updated_by =
st_upd.user_id(+)
AND st.created_by =
st_cr.user_id(+)
AND s.last_updated_by =
s_upd.user_id
AND s.created_by = s_cr.user_id
AND st.vendor_site_id =
pm.supplier_site_id(+)
AND st.vendor_site_id =
ba.supplier_site_id(+)
AND s.vendor_id = st.vendor_id(+)