Supplier details query in oracle fusion

Supplier details query in oracle fusion

We are going to share the Supplier details query in oracle fusion. This Query will help to extract the completes supplier details in oracle fusion. Using this Query , We can able to extract supplier Address & Site Details , Supplier Tax Information's , Supplier Banking information's in oracle fusion. This sql query is the detail query to extract the most important supplier informations in oracle fusion. This is one of the important Supplier query in oracle fusion which we do use in day to day operations. We will also share the important tables used by Supplier details query. Please find below the detail about Supplier details query in oracle fusion.

Supplier details query in oracle fusion

20 Most Important Supplier details query in oracle fusion

  1.POZ_SUPPLIERS_V

  2.POZ_SUPPLIERS_PII

  3.ZX_PARTY_TAX_PROFILE

  4.FND_LOOKUP_VALUES_TL

  5.HZ_CODE_ASSIGNMENTS

  6.FND_LOOKUP_VALUES_TL

  7.ZX_REGISTRATIONS

  8.POZ_SUPPLIER_SITES_V

  9.POZ_ALL_SUPPLIER_CONTACTS_V

  10.HZ_ORG_CONTACTS

  11.IBY_EXTERNAL_PAYEES_ALL

  12.IBY_PMT_INSTR_USES_ALL

  13.IBY_EXT_BANK_ACCOUNTS 

  14.CE_BANK_BRANCHES_V

  15.IBY_EXTERNAL_PAYEES_ALL

  16.POZ_SUPPLIER_ADDRESS_V

  17.AP_TERMS_TL

  18.POZ_SITE_ASSIGNMENTS_ALL_M

  19.HR_OPERATING_UNITS

  20.IBY_EXT_PARTY_PMT_MTHDS

Detail Supplier details query in oracle fusion

Here below is the detail about Supplier details query in oracle fusion which helps to extract the supplier informations 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 ,

  TERMS.NAME "PAYMENT_TERMS",

  PSSV.INVOICE_CURRENCY_CODE ,

  PSSV.PAYMENT_CURRENCY_CODE ,

  PSSV.TAX_REPORTING_SITE_FLAG ,

  HP.PARTY_NAME BANK_PARTY_NAME,

  IEBA.COUNTRY_CODE BANK_COUNTRY,

  IEBA.FOREIGN_PAYMENT_USE_FLAG ALLOW_INT,

 IEBA.BANK_ACCOUNT_NUM BANK_ACCOUNT_NUM,

  IEBA.BANK_ACCOUNT_NAME BANK_ACCOUNT_NAME,

  IEBA.CURRENCY_CODE BANK_ACCT_CURRENCY_CODE,

  IEBA.IBAN ,

  EBB.BANK_BRANCH_NAME ,

  EBB.BRANCH_NUMBER 

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(+)