Query to find supplier Contacts in Oracle Fusion

Query to find supplier Contacts in Oracle Fusion

We are going to share the sql query to find supplier contacts in oracle fusion. This sql query will extract all the supplier contacts created for the suppliers in oracle fusion. We will be able to get the complete contact related informations for the supplier using this query. We will be able to extract the complete informations about supplier Contacts in Oracle Fusion. Here below is the detail sql supplier Contacts in Oracle Fusion.

Query to find supplier Contacts in Oracle Fusion

6 Important Tables of supplier Contacts in Oracle Fusion

  1.POZ_SUPPLIERS_V

  2.POZ_SUPPLIERS_PII

  3.POZ_SUPPLIER_SITES_V

  4.POZ_ALL_SUPPLIER_CONTACTS_V

  5.POZ_SUPPLIER_ADDRESS_V

  6.POZ_SITE_ASSIGNMENTS_ALL_M

Important Query to find supplier Contacts in Oracle Fusion

Here below is the sql query to extract the supplier contact information's in oracle fusion. Please find below the detail about Query to find supplier Contacts in Oracle Fusion.

SELECT PSV.VENDOR_NAME ,

  PSV.SEGMENT1 ,

  PSV.VENDOR_TYPE_LOOKUP_CODE ,

  PSV.ORGANIZATION_TYPE_LOOKUP_CODE " Organization Type" ,

  TAX_PRF.COUNTRY_CODE "TAXPAYER_COUNTRY",

  PSP.INCOME_TAX_ID "TAXPAYER_ID",

  PSV.FEDERAL_REPORTABLE_FLAG,

  PSV.TYPE_1099 "FEDERAL_INCOME_TAX_TYPE",

  PSV.STATE_REPORTABLE_FLAG STATE_REPORTABLE ,

  TAX_PRF.TAX_CLASSIFICATION_CODE "TAX_CLASSIFICATION_CODE",

  ZXR.TAX_REGIME_CODE "TAX_REGIME_CODE",

  ZXR.TAX "TAX",

  TAX_PRF.REP_REGISTRATION_NUMBER "REGISTRATION_NUMBER",

  ZXR.TAX_JURISDICTION_CODE "TAX_JURISDICTION_CODE",

  PSV.TAX_REPORTING_NAME,

  PSAV.PARTY_SITE_NAME "Supplier Address Name",

  PSAV.ADDRESS1 ,

  PSAV.ADDRESS2 ,

  PSAV.ADDRESS3 ,

  PSAV.ADDRESS4 ,

  PSAV.CITY ,

  PSAV.STATE ,

  PSAV.COUNTY ,

  PSAV.POSTAL_CODE ,

  PSAV.COUNTRY ,

  PSAV.EMAIL_ADDRESS ,

  PSSV.VENDOR_SITE_CODE ,

  PSC.NAME "CONTACT_NAME",

  PSC.EMAIL_ADDRESS "CONTACT_EMAIL",

  PSC.PHONE_AREA_CODE ,

  PSC.PHONE_COUNTRY_CODE,

  PSC.PHONE_NUMBER  ,

  PSC.PHONE_EXTENSION ,

  PSC.FAX_COUNTRY_CODE ,

  PSC.FAX_NUMBER FAX ,

  PSC.FAX_AREA_CODE 

FROM POZ_SUPPLIERS_V PSV ,

  POZ_SUPPLIERS_PII PSP,

  ZX_PARTY_TAX_PROFILE TAX_PRF,

  FND_LOOKUP_VALUES_TL REGT,

  HZ_CODE_ASSIGNMENTS HCA,

  FND_LOOKUP_VALUES_TL HCAVAL,

  ZX_REGISTRATIONS ZXR,

  POZ_SUPPLIER_SITES_V PSSV,

  POZ_ALL_SUPPLIER_CONTACTS_V PSC,

  HZ_RELATIONSHIPS hr,

  HZ_ORG_CONTACTS hoc,

  IBY_EXTERNAL_PAYEES_ALL IEPA,

  IBY_EXT_PARTY_PMT_MTHDS IEPPM,

  IBY_PMT_INSTR_USES_ALL IPIUA ,

  IBY_EXT_BANK_ACCOUNTS IEBA,

  CE_BANK_BRANCHES_V EBB,

  HZ_PARTIES HP,

  IBY_EXTERNAL_PAYEES_ALL BANK,

  POZ_SUPPLIER_ADDRESS_V PSAV,

  AP_TERMS_TL TERMS,

  POZ_SITE_ASSIGNMENTS_ALL_M PSAA,

  HR_OPERATING_UNITS HRO,

  HR_OPERATING_UNITS HRO1,

  IBY_EXT_PARTY_PMT_MTHDS PAY_METHOD

WHERE PSP.VENDOR_ID(+)               =PSV.VENDOR_ID

AND TAX_PRF.PARTY_ID(+)                =PSV.PARTY_ID

AND TAX_PRF.PARTY_TAX_PROFILE_ID       = HCA.OWNER_TABLE_ID(+)

AND (HCA.OWNER_TABLE_NAME          = 'ZX_PARTY_TAX_PROFILE'

OR HCA.OWNER_TABLE_NAME           IS NULL)

AND HCAVAL.LOOKUP_CODE(+)          =HCA.CLASS_CODE

AND (REGT.LOOKUP_TYPE              ='ZX_REGISTRATIONS_TYPE'

OR REGT.LOOKUP_TYPE               IS NULL)

AND TAX_PRF.REGISTRATION_TYPE_CODE     =REGT.LOOKUP_CODE(+)

AND TAX_PRF.PARTY_TAX_PROFILE_ID       =ZXR.PARTY_TAX_PROFILE_ID(+)

AND PSSV.VENDOR_ID(+)              =PSV.VENDOR_ID

AND PSSV.VENDOR_SITE_ID            = IEPA.SUPPLIER_SITE_ID(+)

AND PSC.SUP_PARTY_ID(+)            = PSV.PARTY_ID

AND hr.subject_id(+)               =PSV.PARTY_ID

AND( hr.relationship_code          = 'CONTACT'

OR hr.relationship_code           IS NULL)

AND (hr.object_table_name          = 'HZ_PARTIES'

OR hr.object_table_name           IS NULL)

AND hoc.party_relationship_id(+)   = hr.relationship_id

AND IEPA.EXT_PAYEE_ID              = IEPPM.EXT_PMT_PARTY_ID(+)

AND ((IEPPM.INACTIVE_DATE         IS NULL)

OR (IEPPM.INACTIVE_DATE            > SYSDATE) )

AND IPIUA.EXT_PMT_PARTY_ID (+)     = IEPA.EXT_PAYEE_ID

AND ( IPIUA.INSTRUMENT_TYPE        = 'BANKACCOUNT'

OR IPIUA.INSTRUMENT_TYPE          IS NULL)

AND ( IPIUA.PAYMENT_FLOW           = 'DISBURSEMENTS'

OR IPIUA.PAYMENT_FLOW             IS NULL)

AND ( IPIUA.ORDER_OF_PREFERENCE    = 1

OR IPIUA.ORDER_OF_PREFERENCE      IS NULL)

AND IPIUA.INSTRUMENT_ID            = IEBA.EXT_BANK_ACCOUNT_ID(+)

AND HP.PARTY_ID (+)                = IEBA.BANK_ID

AND IEBA.BRANCH_ID                 = EBB.BRANCH_PARTY_ID(+)

AND BANK.EXT_PAYEE_ID(+)           =PSV.VENDOR_ID

AND PSAV.LOCATION_ID(+)            =PSSV.LOCATION_ID

AND TERMS.TERM_ID(+)               =PSSV.TERMS_ID

AND PSAA.VENDOR_SITE_ID(+)         =PSSV.VENDOR_SITE_ID

AND HRO.ORGANIZATION_ID(+)         =PSAA.BU_ID

AND HRO1.ORGANIZATION_ID(+)        =PSAA.BILL_TO_BU_ID

AND PAY_METHOD.EXT_PMT_PARTY_ID(+) =BANK.EXT_PAYEE_ID

AND BANK.EXT_PAYEE_ID              =PAY_METHOD.EXT_PMT_PARTY_ID(+)

Query to find supplier Contacts in Oracle Fusion