AP Supplier Queries

--+++++++++++++Supplier and Contacts++++++++++++++

SELECT

asp.vendor_id ,

asp.segment1 "Supplier Num" ,

asp.vendor_name "Supplier Name" ,

ass.vendor_site_code "site name" ,

hou.name "Operating Unit Name" ,

ass.address_line1 ,

ass.city ,

ass.state ,

ass.zip ,

ass.country ,

ass.phone ,

person.person_first_name ,

person.person_last_name ,

pty_rel.primary_phone_number ,

pty_rel.email_address

FROM

ap_suppliers asp ,

ap_supplier_sites_all ass ,

APPS.ap_supplier_contacts apsc ,

hz_parties person ,

hz_parties pty_rel,

hr_operating_units hou

WHERE

ass.vendor_id = asp.vendor_id

AND apsc.per_party_id = person.party_id

AND apsc.rel_party_id = pty_rel.party_id

AND ass.org_id = hou.organization_id

AND apsc.org_party_site_id = ass.party_site_id

--//--++++++++++++++Supplier Email Address++++++++++++++

SELECT HCP.EMAIL_ADDRESS

  FROM HZ_PARTY_SITES HPS, HZ_CONTACT_POINTS HCP, AP_SUPPLIERS ASS

 WHERE     1 = 1

       AND ASS.VENDOR_NAME = :VENDOR_NAME

       AND HCP.OWNER_TABLE_NAME = 'HZ_PARTY_SITES'

       AND HPS.PARTY_ID = ASS.PARTY_ID

       AND HCP.OWNER_TABLE_ID = HPS.PARTY_SITE_ID

       AND HCP.CONTACT_POINT_TYPE = 'EMAIL';

--//--++++++++++++++Supplier Phone++++++++++++++

SELECT ROWNUM NO, HCP.PHONE_AREA_CODE||' '||HCP.PHONE_NUMBER PHONE

FROM   HZ_CONTACT_POINTS HCP,

       HZ_PARTY_SITES HPS,

       AP_SUPPLIERS ASS

WHERE  1=1

AND    ASS.VENDOR_NAME=:VENDOR_NAME

AND    HPS.PARTY_ID=ASS.PARTY_ID

AND    HCP.OWNER_TABLE_ID=HPS.PARTY_SITE_ID

AND    HCP.PRIMARY_FLAG='Y'

AND    HCP_OWNER_TABLE_NAME='HZ_PARTY_SITES'

AND    HCP.STATUS='A'

AND    HCP.CONTACT_POINT_TYPE='PHONE'

AND    HCP.PHONE_LINE_TYPE='GEN';

--//--++++++++++++++Supplier Fax++++++++++++++

SELECT ROWNUM NO, HCP.PHONE_AREA_CODE||' '||HCP.PHONE_NUMBER PHONE

FROM   HZ_CONTACT_POINTS HCP,

       HZ_PARTY_SITES HPS,

       AP_SUPPLIERS ASS

WHERE  1=1

AND    ASS.VENDOR_NAME=:VENDOR_NAME

AND    HPS.PARTY_ID=ASS.PARTY_ID

AND    HCP.OWNER_TABLE_NAME='HZ_PARTY_SITES'

AND    HCP.OWNER_TABLE_ID=HPS.PARTY_SITE_ID

AND    HCP.STATUS='A'

AND    HCP.CONTACT_POINT_TYPE='PHONE'

AND    HCP.PHONE_LINE_TYPE='FAX';

--//--++++++++++++++Supplier Contact Email++++++++++++++

SELECT  HCP.EMAIL_ADDRESS

FROM    HZ_RELATIONSHIPS HR

       ,HZ_CONTACT_POINTS HCP

       ,AP_SUPPLIERS ASS

WHERE  1=1

AND    ASS.VENDOR_NAME=:VENDOR_NAME

AND    HR.SUBJECT_TYPE='ORGANIZATION'

AND    HR.RELATIONSHIP_CODE='CONTACT'

AND    HR.STATUS='A'

AND    HCP.OWNER_TABLE_NAME='HZ_PARTIES'

AND    HR.SUBJECT_ID=ASS.PARTY_ID

AND    HCP.OWNER_TABLE_ID=HR.PARTY_ID

AND    HCP.PRIMARY_FLAG='Y'

AND    HCP.STATUS='A'

AND    HCP.CONTACT_POINT_TYPE='EMAIL';

--//--++++++++++++++Supplier Contact Phone++++++++++++++

SELECT  HCP.PHONE_AREA_CODE||HCP.PHONE_NUMBER

FROM    HZ_RELATIONSHIPS HR

       ,HZ_CONTACT_POINTS HCP

       ,AP_SUPPLIERS ASS

WHERE  1=1

AND    ASS.VENDOR_NAME=:VENDOR_NAME

AND    HR.SUBJECT_TYPE='ORGANIZATION'

AND    HR.RELATIONSHIP_CODE='CONTACT'

AND    HR.STATUS='A'

AND    HCP.OWNER_TABLE_NAME='HZ_PARTIES'

AND    HR.SUBJECT_ID=ASS.PARTY_ID

AND    HCP.OWNER_TABLE_ID=HR.PARTY_ID

AND    HCP.PRIMARY_FLAG='Y'

AND    HCP.STATUS='A'

AND    HCP.CONTACT_POINT_TYPE='PHONE'

AND    HCP.PHONE_LINE_TYPE='GEN';

--//--++++++++++++++Supplier Contact Fax++++++++++++++

select hcp.phone_area_code||hcp.phone_number

from    hz_relationships hr

       ,hz_contact_points hcp

       ,AP_SUPPLIERS ASS

where  1=1

AND    ASS.VENDOR_NAME=:VENDOR_NAME

and    hr.SUBJECT_TYPE='ORGANIZATION'

and    hr.RELATIONSHIP_CODE='CONTACT'

and    hr.STATUS='A'

AND    hcp.owner_table_name='HZ_PARTIES'

and    hr.SUBJECT_ID=ASS.PARTY_ID

and    hcp.owner_table_id=hr.party_ID

and    hcp.STATUS='A'

and    hcp.CONTACT_POINT_TYPE='PHONE'

and    hcp.PHONE_LINE_TYPE='FAX';

---//+++++++++++Supplier Details+++++++++++++++++++

select asa.vendor_name,

asa.vendor_id,

asa.segment1 vendor_number,

asaa.vendor_site_code,

hps.party_site_name,

asaa.vendor_site_id,

hou.name ou_name,

asaa.org_id,

asa.party_id,

asaa.party_site_id

from ap_supplier_sites_all asaa

,ap_suppliers asa

,hr_operating_units hou

,hz_party_sites hps

where asa.vendor_id = asaa.vendor_id

and asa.segment1 = NVL(:p_segment1,asa.segment1)

and asa.vendor_name = NVL(:p_vendor_name,asa.vendor_name)

and asaa.vendor_site_code = NVL(:p_ven_site_code,asaa.vendor_site_code)

and asaa.org_id = hou.organization_id

and hps.party_site_id = asaa.party_site_id;

--//--++++++++++++++Supplier Contacts+++++++++++++++++++++++++

SELECT DISTINCT asu.party_id

, asu.segment1 Supp_Num

,asu.vendor_name

,hpc.party_name Contact_Name

,hpr.primary_phone_country_code cnt_cntry

,hpr.primary_phone_area_code cnt_area

,hpr.primary_phone_number cnt_phone

,assa.vendor_site_code

,assa.vendor_site_id

,asco.vendor_contact_id

FROM

 hz_relationships hr

,ap_suppliers asu

,ap_supplier_sites_all assa

,ap_supplier_contacts asco

,hz_org_contacts hoc

,hz_parties hpc

,hz_parties hpr

,hz_contact_points hpcp

WHERE hoc.party_relationship_id = hr.relationship_id

AND hr.subject_id = asu.party_id

AND hr.relationship_code = 'CONTACT'

AND hr.object_table_name = 'HZ_PARTIES'

AND asu.vendor_id = assa.vendor_id

AND hr.object_id = hpc.party_id

AND hr.party_id = hpr.party_id

AND asco.relationship_id  = hoc.party_relationship_id

AND assa.party_site_id = asco.org_party_site_id

AND hpr.party_type='PARTY_RELATIONSHIP'

AND hpr.party_id = hpcp.owner_table_id

AND hpcp.owner_table_name = 'HZ_PARTIES'

--//--------Supplier Payment Method at Supplier Level----------

SELECT

    supp.segment1 supplier_num,

    paym.payment_method_code

FROM

    ap_suppliers supp,

    iby_external_payees_all payee,

    iby_ext_party_pmt_mthds paym

WHERE

           supp.party_id = payee.payee_party_id

    AND    payee.ext_payee_id = paym.ext_pmt_party_id

    AND    supp.segment1 =:supplier_num

    AND    supplier_site_id IS NULL

    AND    paym.primary_flag = 'Y';

--//--------Supplier Payment Method at Site Level--------

SELECT

    supp.segment1 supplier_num,

    sites.vendor_site_code,

    paym.payment_method_code

FROM

    ap_suppliers supp,

    ap_supplier_sites_all sites,

    iby_external_payees_all payee,

    iby_ext_party_pmt_mthds paym

WHERE

           supp.party_id = payee.payee_party_id

    AND    payee.ext_payee_id = paym.ext_pmt_party_id

    AND    supp.segment1 =:supplier_num

    AND    supp.vendor_id = sites.vendor_id

    AND    sites.vendor_site_id = payee.supplier_site_id

    AND    paym.primary_flag = 'Y';

--//-----------Supplier Remit Site-------------

SELECT asa.vendor_site_code, asa1.vendor_site_code

  FROM iby_ext_payee_relationships  iep,

       ap_supplier_sites_all        asa,

       ap_supplier_sites_all        asa1

 WHERE     iep.supplier_site_id = asa.vendor_site_id

       AND asa1.vendor_site_id = iep.REMIT_SUPPLIER_SITE_ID

       AND asa.vendor_site_code IN ('10230908E');

--//-----------Supplier Extract for India Localization-------------

SELECT (SELECT name

          FROM hr_operating_units hca

         WHERE hca.organization_id = assa.org_id) OU_Name,

       asp.vendor_name,

       asp.segment1 vendor_num,

       assa.vendor_site_code,

       assa.address_line1,

       assa.address_line2,

       assa.city,

       assa.state,

       assa.zip,

       assa.country,

       (SELECT registration_number

          FROM jai_party_reg_lines c

         WHERE     c.party_reg_id = a.party_reg_id

               AND registration_type_code = 'PAN'

               AND effective_to IS NULL)THIRD_PARTY_PAN,

       (SELECT registration_number

          FROM jai_party_reg_lines c

         WHERE     c.party_reg_id = a.party_reg_id

               AND registration_type_code = 'GSTN'

               AND effective_to IS NULL)THIRD_PARTY_GST,

       (SELECT pan_no

          FROM JAI_AP_TDS_VENDOR_HDRS

         WHERE     vendor_id = a.party_id

               AND vendor_site_id = a.party_site_id

               AND ROWNUM = 1)addl_supp_pan,

       (SELECT SERVICE_TAX_REGNO

          FROM JAI_CMN_VENDOR_SITES

         WHERE     vendor_id = a.party_id

               AND vendor_site_id = a.party_site_id

               AND ROWNUM = 1) addl_supp_reg,

       assa.inactive_date

FROM jai_party_regs a,

     ap_suppliers asp,

     ap_supplier_sites_all assa

WHERE     1 = 1

AND a.party_id = asp.vendor_id

AND asp.vendor_id = assa.vendor_id

AND assa.vendor_site_id = a.party_site_id