1. Supplier Details:
SELECT s.segment1 supplier_number,
s.vendor_name supplier_name,
s.vendor_name_alt supplier_alternate_name,
ss.vendor_site_code supplier_site_code,
ss.vendor_site_id supplier_site_id,
hp.jgzz_fiscal_code tax_payer_id,
ss.org_id operating_unit,
s.end_date_active supplier_inactive_date,
ss.inactive_date supplier_site_inactive_date,
NVL (ss.terms_id, s.terms_id) payment_term_id,
hp.jgzz_fiscal_code national_id,
ss.vat_code tax_code,
NVL (ss.awt_group_id, s.awt_group_id)
withholding_tax_group_id,
s.party_id,
ss.party_site_id
FROM ap_suppliers s,
ap_supplier_sites_all ss
hz_parties hp,
hz_party_sites hps
WHERE 1 = 1
AND s.vendor_type_lookup_code = lv.lookup_code
AND ss.org_id = g_org_id
AND s.vendor_id = ss.vendor_id
AND hp.party_id = s.party_id
AND hps.party_site_id = ss.party_site_id;
2. Query to get Supplier Tax Registration Number:
SELECT tax_reference
FROM hz_organization_profiles hzop, ap_suppliers aps
WHERE 1 = 1
AND aps.segment1='1000' --> Supplier Number
AND hzop.party_id = aps.party_id
AND hzop.effective_end_date IS NULL;
3.Payment Terms:
SELECT name
FROM ap_terms_vl
WHERE term_id = <Terms Id from Query 1>;
4. Withhold Tax Group:
SELECT name, last_update_date
FROM ap_awt_groups
WHERE GROUP_ID = <withholding_tax_group_id from Query 1>
5.Supplier Payment Method:
SELECT pv.payment_method_name
FROM iby_external_payees_all ip,
iby_ext_party_pmt_mthds pm,
iby_payment_methods_vl pv
WHERE 1 = 1
AND ip.supplier_site_id IS NULL
AND ip.payee_party_id = <Party Id from query 1> --supp party id
AND ip.ext_payee_id = pm.ext_pmt_party_id
AND pm.payment_method_code = pv.payment_method_code
ORDER BY pm.primary_flag DESC, pm.last_update_date DESC;
6.Supplier site Payment method:
SELECT pv.payment_method_name,
pm.last_update_date,
pv.last_update_date pm_last_update_date
FROM iby_external_payees_all ip,
iby_ext_party_pmt_mthds pm,
iby_payment_methods_vl pv
WHERE ip.supplier_site_id = <Supp site Id from query 1>
AND ip.org_id = p_org_id
AND ip.ext_payee_id = pm.ext_pmt_party_id
AND pm.payment_method_code = pv.payment_method_code
ORDER BY pm.primary_flag DESC, pm.last_update_date DESC;
7. Supplier Contact details Including phone, email and fax details:
SELECT hp.person_last_name || ' ' || hp.person_first_name
contact_name,
hcpe.email_address,
LTRIM (
RTRIM (hcpp.phone_area_code || ' ' || hcpp.phone_number))
AS primary_phone_number,
LTRIM (
RTRIM (hcpf.phone_area_code || ' ' || hcpf.phone_number))
fax_number,
hzr.end_date,
hzr.last_update_date
FROM hz_parties hp,
hz_relationships hzr,
hz_party_usg_assignments hpua,
hz_contact_points hcpp,
hz_contact_points hcpe,
hz_contact_points hcpf
WHERE hp.party_id = hzr.subject_id
AND hzr.object_id = <<p_supplier_party_id>> -- party of supplier
AND hzr.relationship_type = 'CONTACT'
AND hzr.relationship_code = 'CONTACT_OF'
AND hzr.subject_type = 'PERSON'
AND hzr.object_type = 'ORGANIZATION'
AND hzr.status IN ('A', 'I')
AND hpua.party_id = hp.party_id
AND hpua.status_flag IN ('A', 'I')
AND hpua.party_usage_code = 'SUPPLIER_CONTACT'
AND hcpp.owner_table_name(+) = 'HZ_PARTIES'
AND hcpp.owner_table_id(+) = hzr.party_id
AND hcpp.phone_line_type(+) = 'GEN'
AND hcpp.contact_point_type(+) = 'PHONE'
AND hcpp.primary_flag(+) = 'Y'
AND hcpe.owner_table_name(+) = 'HZ_PARTIES'
AND hcpe.owner_table_id(+) = hzr.party_id
AND hcpe.contact_point_type(+) = 'EMAIL'
AND hcpe.primary_flag(+) = 'Y'
AND (hcpe.status IS NULL OR hcpe.status IN ('A', 'I'))
AND hcpf.owner_table_name(+) = 'HZ_PARTIES'
AND hcpf.owner_table_id(+) = hzr.party_id
AND hcpf.contact_point_type(+) = 'PHONE'
AND hcpf.phone_line_type(+) = 'FAX'
AND (hcpf.status IS NULL OR hcpf.status IN ('A', 'I'))
AND (hcpp.status IS NULL OR hcpp.status IN ('A', 'I'))
ORDER BY hzr.end_date DESC;
8. Supplier site contact details:
SELECT hp.person_last_name || ' ' || hp.person_first_name
contact_name,
hcpe.email_address,
LTRIM (
RTRIM (hcpp.phone_area_code || ' ' || hcpp.phone_number))
AS primary_phone_number,
LTRIM (
RTRIM (hcpf.phone_area_code || ' ' || hcpf.phone_number))
fax_number,
hzr.end_date,
hzr.last_update_date
FROM hz_parties hp,
hz_party_sites hps,
hz_relationships hzr,
ap_supplier_contacts ascs,
hz_party_usg_assignments hpua,
hz_contact_points hcpp,
hz_contact_points hcpe,
hz_contact_points hcpf
WHERE 1 = 1
AND NVL (hps.end_date_active, SYSDATE) >= SYSDATE
AND hzr.relationship_type = 'CONTACT'
AND hzr.relationship_code = 'CONTACT_OF'
AND hzr.subject_type = 'PERSON'
AND hzr.subject_table_name = 'HZ_PARTIES'
AND hzr.object_type = 'ORGANIZATION'
AND hzr.object_table_name = 'HZ_PARTIES'
AND hzr.status = 'A'
AND hps.party_id = hzr.object_id
AND hps.party_site_id = p_supplier_party_site_id
AND hzr.relationship_id = ascs.relationship_id
AND hzr.party_id = ascs.rel_party_id
AND hps.party_site_id = ascs.org_party_site_id
AND hzr.subject_id = ascs.per_party_id
AND hp.party_id = hzr.subject_id
AND hpua.party_id = hp.party_id
AND hpua.status_flag IN ('A', 'I')
AND hpua.party_usage_code = 'SUPPLIER_CONTACT'
AND hcpp.owner_table_name(+) = 'HZ_PARTIES'
AND hcpp.owner_table_id(+) = hzr.party_id
AND hcpp.phone_line_type(+) = 'GEN'
AND hcpp.contact_point_type(+) = 'PHONE'
AND hcpp.primary_flag(+) = 'Y'
AND hcpe.owner_table_name(+) = 'HZ_PARTIES'
AND hcpe.owner_table_id(+) = hzr.party_id
AND hcpe.contact_point_type(+) = 'EMAIL'
AND hcpe.primary_flag(+) = 'Y'
AND (hcpe.status IS NULL OR hcpe.status IN ('A', 'I'))
AND hcpf.owner_table_name(+) = 'HZ_PARTIES'
AND hcpf.owner_table_id(+) = hzr.party_id
AND hcpf.contact_point_type(+) = 'PHONE'
AND hcpf.phone_line_type(+) = 'FAX'
AND (hcpf.status IS NULL OR hcpf.status IN ('A', 'I'))
AND (hcpp.status IS NULL OR hcpp.status IN ('A', 'I'))
ORDER BY hzr.end_date DESC;
9. Bank account owner details:
SELECT hp.party_name, iba.last_update_date
FROM iby_account_owners iba, hz_parties hp
WHERE iba.ext_bank_account_id = p_bank_account_id
AND iba.account_owner_party_id = hp.party_id
ORDER BY NVL (primary_flag, 'N') DESC;
10. Supplier Bank Details:
SELECT s.segment1 supplier_number,
hps.attribute1 legacy_supplier_number,
ss.vendor_site_code supplier_site_code,
ss.org_id operating_unit,
ss.vendor_site_id supplier_site_id,
s.party_id party_id,
ib_pmt_instr.order_of_preference priority_number,
ib_pmt_instr.start_date,
ib_pmt_instr.end_date,
ib_pmt_instr.last_update_date instr_last_update_date -- bank account details
,
bank_ac.bank_account_num bank_account_number,
bank_ac.ext_bank_account_id bank_account_id,
bank_ac.bank_account_name payee,
bank_ac.last_update_date bank_ac_last_update_date -- bank branch details
,
bank_branch.bank_branch_name_alt supp_bank_name,
bank_branch.branch_number supp_bank_branch_num,
bank_branch.bank_number supp_bank_num,
bank_branch.address_line1
|| ','
|| bank_branch.address_line2
|| ','
|| bank_branch.address_line3
|| ','
|| bank_branch.address_line4
|| ','
|| bank_branch.city
|| ','
|| bank_branch.state
supp_bank_branch_address,
bank_branch.eft_swift_code swift_code
FROM ap_suppliers s,
ap_supplier_sites_all ss,
hz_party_sites hps,
iby_external_payees_all ib_xt_payee,
iby_pmt_instr_uses_all ib_pmt_instr,
iby_ext_bank_accounts bank_ac,
ce_bank_branches_v bank_branch
WHERE 1 = 1
AND ss.org_id = <<g_org_id>>
AND s.vendor_id = ss.vendor_id
AND hps.party_site_id = ss.party_site_id
AND ib_xt_payee.supplier_site_id = ss.vendor_site_id
AND ib_xt_payee.payee_party_id = hps.party_id
AND ib_pmt_instr.ext_pmt_party_id = ib_xt_payee.ext_payee_id
AND ib_pmt_instr.instrument_type = 'BANKACCOUNT'
AND ib_pmt_instr.instrument_id = bank_ac.ext_bank_account_id
AND bank_ac.branch_id = bank_branch.branch_party_id(+)
11. Banks at Supplier Site Level:
SELECT s.segment1 supplier_number,
hps.attribute1 legacy_supplier_number,
ss.vendor_site_code supplier_site_code,
ss.org_id operating_unit,
ss.vendor_site_id supplier_site_id,
s.party_id party_id,
ib_pmt_instr.order_of_preference priority_number,
ib_pmt_instr.start_date,
ib_pmt_instr.end_date,
ib_pmt_instr.last_update_date instr_last_update_date -- bank account details
,
bank_ac.bank_account_num bank_account_number,
bank_ac.ext_bank_account_id bank_account_id,
bank_ac.bank_account_name,
bank_ac.last_update_date bank_ac_last_update_date -- bank branch details
,
bank_branch.bank_branch_name_alt supp_bank_name,
bank_branch.branch_number supp_bank_branch_num,
bank_branch.bank_number supp_bank_num,
bank_branch.address_line1
|| ','
|| bank_branch.address_line2
|| ','
|| bank_branch.address_line3
|| ','
|| bank_branch.address_line4
|| ','
|| bank_branch.city
|| ','
|| bank_branch.state
supp_bank_branch_address,
bank_branch.eft_swift_code swift_code
FROM ap_suppliers s,
ap_supplier_sites_all ss,
hz_party_sites hps,
iby_external_payees_all ib_xt_payee,
iby_pmt_instr_uses_all ib_pmt_instr,
iby_ext_bank_accounts bank_ac,
ce_bank_branches_v bank_branch
WHERE 1 = 1
AND ss.org_id = <<g_org_id>>
AND s.vendor_id = ss.vendor_id
AND hps.party_site_id = ss.party_site_id
AND ib_xt_payee.supplier_site_id IS NULL
AND ib_xt_payee.payee_party_id = hps.party_id
AND ib_pmt_instr.ext_pmt_party_id = ib_xt_payee.ext_payee_id
AND ib_pmt_instr.instrument_type = 'BANKACCOUNT'
AND ib_pmt_instr.PAYMENT_FUNCTION = 'PAYABLES_DISB'
AND ib_pmt_instr.instrument_id = bank_ac.ext_bank_account_id
AND bank_ac.branch_id = bank_branch.branch_party_id(+)
AND NOT EXISTS
(SELECT 'Y'
FROM iby_external_payees_all ib_xt_payee,
iby_pmt_instr_uses_all ib_pmt_instr
WHERE ib_xt_payee.supplier_site_id =
ss.vendor_site_id
AND ib_pmt_instr.ext_pmt_party_id =
ib_xt_payee.ext_payee_id
AND ib_pmt_instr.instrument_type =
'BANKACCOUNT');
