Query for Supplier ,Supplier Sites and Supplier Bank Information In oracle Fusion

SELECT PSV.VENDOR_NAME SUPPLIER_NAME ,
  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_TL BUSREL,
  POZ_SUPPLIERS_PII PSP,
  ZX_PARTY_TAX_PROFILE TAX,
  FND_LOOKUP_VALUES_TL TAXVAL,
  FND_LOOKUP_VALUES_TL RRVAL,
  FND_LOOKUP_VALUES_TL RLVAL,
  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 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(+)