-- Query for Supplier ,Supplier Sites and Supplier Bank Information In oracle Fusion SELECT PSV.VENDOR_NAME SUPPLIER_NAME , PSV.VENDOR_ID SUPPLIER_ID , PSV.SEGMENT1 "SupplierNumber" , BUSREL.MEANING "BUSSINESS_REL" , PSV.VENDOR_TYPE_LOOKUP_CODE "Supplier Type" , DECODE(SIGN(NVL(PSV.END_DATE_ACTIVE,SYSDATE)-SYSDATE) , -1,'Inactive' ,'Active') STATUS , TO_CHAR(PSV.CREATION_DATE,'YYYY-MM-DD HH24:MI:SS','NLS_DATE_LANGUAGE=AMERICAN') CREATION_DATE , TO_CHAR(PSV.LAST_UPDATE_DATE,'YYYY-MM-DD HH24:MI:SS','NLS_DATE_LANGUAGE=AMERICAN') LAST_UPDATE_DATE , PSV.ORGANIZATION_TYPE_LOOKUP_CODE "Tax Organization Type" , TAX.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 , PSV.TAX_REPORTING_NAME , PSV.ATTRIBUTE_CATEGORY "ATTRIBUTE_CATEGORY" , PSV.ATTRIBUTE1 "ATTRIBUTE1" , PSV.ATTRIBUTE1 "CERTIFICATE_NUM" , PSV.ATTRIBUTE2 "ATTRIBUTE2" , PSV.ATTRIBUTE2 "PRODUCT" , PSV.ATTRIBUTE3 "ATTRIBUTE3" , PSV.ATTRIBUTE3 "SERVICES" , PSV.ATTRIBUTE4 "ATTRIBUTE4" , PSV.ATTRIBUTE5 "ATTRIBUTE5" , PSV.ATTRIBUTE6 "ATTRIBUTE6" , PSV.ATTRIBUTE7 "ATTRIBUTE7" , PSV.ATTRIBUTE8 "ATTRIBUTE8" , PSV.ATTRIBUTE9 "ATTRIBUTE9" , PSV.ATTRIBUTE10 "ATTRIBUTE10" , PSV.ATTRIBUTE15 "GP_VENDOR_ID" , PSV.ATTRIBUTE_NUMBER1 "ATTRIBUTE_NUMBER1" , PSV.ATTRIBUTE_DATE1 "LAST_AUDIT_DATE" , PSV.ATTRIBUTE_DATE2 "NEXT_AUDIT_DATE" , TO_CHAR(PSV.END_DATE_ACTIVE,'YYYY-MM-DD HH24:MI:SS','NLS_DATE_LANGUAGE=AMERICAN') "SUP_INACTIVE_DATE" , PSAV.PARTY_SITE_NAME "Address Name" , PSAV.COUNTRY "COUNTRY_ADD" , PSAV.ADDRESS1 "Location Address1" , PSAV.ADDRESS2 "Location Address2" , PSAV.ADDRESS3 "Location Address3" , PSAV.ADDRESS4 "Location Address4" , PSAV.CITY "Location City" , PSAV.STATE "Location State" , PSAV.COUNTY "Location County" , PSAV.POSTAL_CODE "Location Postal Code" , PSAV.PROVINCE "Location Province" , PSAV.PHONE_COUNTRY_CODE "PHONE_COUNTRY_CODE_ADD" , PSAV.PHONE_AREA_CODE "Location Phone Area Code_ADD" , PSAV.PHONE_NUMBER "Location Phone Number_ADD" , PSAV.PHONE_EXTENSION "Location Phone Ext_ADD" , PSAV.FAX_COUNTRY_CODE "Fax Country Code_ADD" , PSAV.FAX_PHONE_AREA_CODE "FAX Area Code_ADD" , PSAV.FAX_PHONE_NUMBER "Fax_ADD" , PSAV.EMAIL_ADDRESS "EMAIL_ADDRESS_ADD" , PSAV.ADDRESS_PURPOSE_ORDERING "Ordering_ADD" , PSAV.ADDRESS_PURPOSE_REMIT_TO "pay_ADD" , PSSV.VENDOR_SITE_CODE "Vendor Site Code" , PSSV.PURCHASING_SITE_FLAG "Ordering" , PSSV.PAY_ON_USE_FLAG "pay" , PSSV.PRIMARY_PAY_SITE_FLAG "primary_pay" , PSC.PHONE_AREA_CODE AREA_CODE , PSC.PHONE_COUNTRY_CODE , PSC.PHONE_NUMBER PHONE , PSC.PHONE_EXTENSION , PSC.FAX_COUNTRY_CODE , PSC.FAX_NUMBER FAX , PSC.FAX_AREA_CODE , PSSV.TERMS_DATE_BASIS , PSSV.PAY_GROUP_LOOKUP_CODE , TERMS.NAME "PAYMENT_TERMS" , PSSV.INVOICE_AMOUNT_LIMIT , PSSV.PAY_DATE_BASIS_LOOKUP_CODE , PSSV.INVOICE_CURRENCY_CODE , PSSV.PAYMENT_CURRENCY_CODE , PSSV.TAX_REPORTING_SITE_FLAG , PSSV.MATCH_OPTION "MATCH_OPTION" , PSSV.SUPPLIER_NOTIF_METHOD "Communication Method" , PSSV.EMAIL_ADDRESS "Purchase Order E-Mail" , PSC.NAME "CONTACT_NAME" , hoc.job_title "CONTACT_JOB" , PSC.EMAIL_ADDRESS "CONTACT_EMAIL" , IEPA.REMIT_ADVICE_DELIVERY_METHOD "DELIVERY_METHOD" , IEPA.REMIT_ADVICE_EMAIL "EMAIL_ADDRESS" , IEPPM.PAYMENT_METHOD_CODE "PAYMENT_METHOD_SITE" , HP.PARTY_NAME BANK_PARTY_NAME , IEBA.COUNTRY_CODE BANK_COUNTRY , IEBA.FOREIGN_PAYMENT_USE_FLAG ALLOW_INT , REGEXP_REPLACE (SUBSTR(IEBA.BANK_ACCOUNT_NUM,1,LENGTH(IEBA.BANK_ACCOUNT_NUM)-4) , '[^ ]' , 'X') ||SUBSTR(IEBA.BANK_ACCOUNT_NUM, -4) BANK_ACCOUNT_NUM , IEBA.BANK_ACCOUNT_NAME BANK_ACCOUNT_NAME , IEBA.CURRENCY_CODE BANK_ACCT_CURRENCY_CODE , IEBA.IBAN "IBAN" , IEBA.LAST_UPDATED_BY BANK_ACCT_UPDATED_BY , TO_CHAR(IEBA.LAST_UPDATE_DATE,'YYYY-MM-DD HH24:MI:SS','NLS_DATE_LANGUAGE=AMERICAN') BANK_ACCT_UPDATE_DATE , TO_CHAR(IPIUA.START_DATE,'MM/DD/YY') "From_ASSG_DATE" , DECODE (TO_CHAR(IPIUA.END_DATE,'YYYY') ,'4712', NULL , TO_CHAR(IPIUA.END_DATE,'MM/DD/YY')) "Inactive_ASSG_DATE" , IPIUA.LAST_UPDATED_BY ASSG_DATE_UPDATED_BY , TO_CHAR(IPIUA.LAST_UPDATE_DATE,'YYYY-MM-DD HH24:MI:SS','NLS_DATE_LANGUAGE=AMERICAN') ASSG_DATE_UPDATE_DATE , EBB.BANK_BRANCH_NAME , EBB.BRANCH_NUMBER , BANK.PAYMENT_FORMAT_CODE , BANK.SETTLEMENT_PRIORITY , BANK.PAYMENT_TEXT_MESSAGE1 , BANK.PAYMENT_TEXT_MESSAGE2 , BANK.PAYMENT_TEXT_MESSAGE3 , PAY_METHOD.PAYMENT_METHOD_CODE "PAYMENT_METHOD" , CASE (PSSV.INSPECTION_REQUIRED_FLAG ||PSSV.RECEIPT_REQUIRED_FLAG) WHEN 'NN' THEN '2-Way' WHEN 'YN' THEN '3-Way' WHEN 'YY' THEN '4-Way' ELSE NULL END "MATCH_APPROVAL_LEVEL" , BANK.REMIT_ADVICE_DELIVERY_METHOD , BANK.REMIT_ADVICE_EMAIL , HRO.NAME "CLIENT_BU" , HRO1.NAME "PROCUREMENT_BU" , HRO1.NAME "BILL_TO_BU" , ( SELECT LOCATION_CODE FROM HR_LOCATIONS_ALL HR WHERE HR.LOCATION_ID=PSAA.BILL_TO_LOCATION_ID ) BILL_TO_LOCATION , ( SELECT LOCATION_CODE FROM HR_LOCATIONS_ALL HR WHERE HR.LOCATION_ID=PSAA.SHIP_TO_LOCATION_ID ) SHIP_TO_LOCATION , ( SELECT SEGMENT1 ||'-' ||SEGMENT2 ||'-' ||SEGMENT3 ||'-' ||SEGMENT4 ||'-' ||SEGMENT5 ||'-' ||SEGMENT6 ||'-' ||SEGMENT7 FROM GL_CODE_COMBINATIONS WHERE CODE_COMBINATION_ID =PSAA.ACCTS_PAY_CODE_COMBINATION_ID ) ACCURAL_DIS , PSSV.COUNTRY_OF_ORIGIN_CODE "COUNTRY_CODE" , TAX.ALLOW_OFFSET_TAX_FLAG "ALLOW_OFFSET_TAX" , TAX.PROCESS_FOR_APPLICABILITY_FLAG "ALLOW_TAX_APPLICABILITY" , RLVAL.MEANING "ROUNDING_LEVEL" , RRVAL.MEANING "ROUNDING_RULE" , TAX.INCLUSIVE_TAX_FLAG "TAX_INCLUSIVE" , TAX.TAX_CLASSIFICATION_CODE "TAX_CLASSIFICATION_CODE" , HCA.CLASS_CATEGORY "FISCAL_CLASS_CODE_TYPE" , HCA.CLASS_CODE "FISCAL_CLASS_CODE" , HCAVAL.LOOKUP_TYPE "FISCAL_CLASS_TYPE" , HCAVAL.MEANING "FISCAL_CLASS_NAME" , HCA.START_DATE_ACTIVE "START_DATE" , HCA.END_DATE_ACTIVE "END_DATE" , TAXVAL.MEANING "TAX_REG_COUNTRY" , ZXR.TAX_REGIME_CODE "TAX_REGIME_CODE" , ZXR.TAX "TAX" , TAX.REP_REGISTRATION_NUMBER "REGISTRATION_NUMBER" , REGT.MEANING "REGISTRATION_TYPE_CODE" , ZXR.TAX_JURISDICTION_CODE "TAX_JURISDICTION_CODE" , ZXR.DEFAULT_REGISTRATION_FLAG "DEFAULT_REGISTRATION_FLAG" , ZXR.EFFECTIVE_FROM "START_DATE_REG" , ZXR.EFFECTIVE_TO "END_DATE_REG" FROM POZ_SUPPLIERS_V PSV , FND_LOOKUP_VALUES_VL BUSREL , POZ_SUPPLIERS_PII PSP , ZX_PARTY_TAX_PROFILE TAX , FND_LOOKUP_VALUES_VL TAXVAL , FND_LOOKUP_VALUES_VL RRVAL , FND_LOOKUP_VALUES_VL RLVAL , FND_LOOKUP_VALUES_VL REGT , HZ_CODE_ASSIGNMENTS HCA , FND_LOOKUP_VALUES_VL 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 1 = 1 AND PSP.VENDOR_ID(+) = PSV.VENDOR_ID AND BUSREL.LOOKUP_CODE(+) = PSV.BUSINESS_RELATIONSHIP AND ( BUSREL.LOOKUP_TYPE = 'ORA_POZ_BUSINESS_RELATIONSHIP' OR BUSREL.LOOKUP_TYPE IS NULL ) AND TAX.PARTY_ID(+) = PSV.PARTY_ID AND TAX.PARTY_TAX_PROFILE_ID = HCA.OWNER_TABLE_ID(+) AND TAXVAL.LOOKUP_CODE(+) = TAX.COUNTRY_CODE AND ( TAXVAL.LOOKUP_TYPE = 'JEES_EURO_COUNTRY_CODES' OR TAXVAL.LOOKUP_TYPE IS NULL ) AND RRVAL.LOOKUP_CODE(+) = TAX.ROUNDING_RULE_CODE AND ( RRVAL.LOOKUP_TYPE = 'ZX_ROUNDING_RULE' OR RRVAL.LOOKUP_TYPE IS NULL ) AND RLVAL.LOOKUP_CODE(+) = TAX.ROUNDING_LEVEL_CODE AND ( RLVAL.LOOKUP_TYPE = 'ZX_ROUNDING_LEVEL' OR RLVAL.LOOKUP_TYPE IS NULL ) 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.REGISTRATION_TYPE_CODE = REGT.LOOKUP_CODE(+) AND TAX.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(+) -- AND upper(PSV.VENDOR_NAME) like nvl(upper(:IP_SUPPLIER_NAME),upper(PSV.VENDOR_NAME)) -- AND PSV.SEGMENT1 = nvl(:IP_SUPPLIER_NUMBER,PSV.SEGMENT1) -- AND PSP.INCOME_TAX_ID = nvl(:IP_TAXPAYER_ID,PSP.INCOME_TAX_ID) and PSV.SEGMENT1 in (2000001, 2000002) Order by PSV.VENDOR_NAME , PSV.SEGMENT1