Useful Supplier Queries

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');