Supplier Unmasked bank details Report Query
Introduction:
This SQL query is used to fetching the data of supplier unmasked bank details to identify if there were any setup was missed for giving masked account number to supplier.
Cause of the issue:
Business wants a report that contains how many supplier having unmasked bank details
How do we solve:
Create a report in BI publisher using below SQL query to extract the Supplier Unmasked bank details report.
SQL Query:
SELECT DISTINCT
HOU.NAME
,B.VENDOR_NAME VENDOR_NAME
,B.VENDOR_ID VENDOR_ID
,B.SEGMENT1 VENDOR_NUMBER
,case when nvl(B.END_DATE_ACTIVE,sysdate) >= sysdate then ‘Active’ else ‘Inactive’ end as SUP_ENABLED_FLAG
,B.START_DATE_ACTIVE SUP_START_DATE_ACTIVE
,B.END_DATE_ACTIVE SUP_END_DATE_ACTIVE
,B.SUPPLIER_LOCKED_FLAG SUP_SUPPLIER_LOCKED_FLAG
,B.LAST_UPDATE_DATE LAST_UPDATE_DATE
,B.LAST_UPDATED_BY LAST_UPDATED_BY
,A.EFFECTIVE_START_DATE SUP_SITE_EFFECTIVE_START_DATE
,A.EFFECTIVE_END_DATE SUP_SITE_EFFECTIVE_END_DATE
,A.INACTIVE_DATE SUP_SITE_INACTIVE_DATE
,A.VENDOR_SITE_CODE SUP_SITE_VENDOR_SITE_CODE
,case when nvl(A.INACTIVE_DATE,sysdate) >= sysdate then ‘Active’ else ‘Inactive’ end as SUP_SITE_ENABLED_FLAG
,G.COUNTRY_CODE ACCTS_COUNTRY_CODE
,G.BRANCH_ID ACCTS_BRANCH_ID
,G.BANK_ID ACCTS_BANK_ID
,G.BANK_ACCOUNT_NUM ACCTS_BANK_ACCOUNT_NUM
,G.MASKED_BANK_ACCOUNT_NUM ACCTS_MASKED_BANK_ACCOUNT_NUM
,G.CURRENCY_CODE ACCTS_CURRENCY_CODE
,G.CHECK_DIGITS ACCTS_CHECK_DIGITS
,G.BANK_ACCOUNT_TYPE ACCTS_BANK_ACCOUNT_TYPE
,G.ACCOUNT_CLASSIFICATION ACCTS_ACCOUNT_CLASSIFICATION
,G.BANK_ACCOUNT_NAME ACCTS_BANK_ACCOUNT_NAME
,G.BANK_ACCOUNT_NAME_ALT ACCTS_BANK_ACCOUNT_NAME_ALT
,G.BANK_ACCOUNT_NUM_ELECTRONIC ACCTS_BANK_ACCOUNT_NUM_ELECTRONIC
,G.BA_NUM_ELEC_SEC_SEGMENT_ID ACCTS_BA_NUM_ELEC_SEC_SEGMENT_ID
,H.BANK_NAME ACCTS_BANK_NAME
,H.BANK_NUMBER ACCTS_BANK_NUMBER
,H.BANK_BRANCH_NAME ACCTS_BANK_BRANCH_NAME
,H.BRANCH_NUMBER ACCTS_BRANCH_NUMBER
,G.START_DATE ACCTS_START_DATE
,G.END_DATE ACCTS_END_DATE
,G.FOREIGN_PAYMENT_USE_FLAG ALLOW_INTERNATIONAL_PAYMENTS
,G.IBAN IBANK
,G.MASKED_IBAN MASKED_IBAN
,N.PARTY_SITE_NAME ADDR_SITE
,N.LOCATION_ID ADDR_LOCATION_ID
,N.ADDRESS1 ADDR_ADDRESS1
,N.ADDRESS2 ADDR_ADDRESS2
,N.ADDRESS3 ADDR_ADDRESS3
,N.ADDRESS4 ADDR_ADDRESS4
,N.CITY ADDR_CITY
,N.COUNTY ADDR_COUNTY
,N.STATE ADDR_STATE
,N.PROVINCE ADDR_PROVINCE
,N.COUNTRY ADDR_COUNTRY
,N.POSTAL_CODE ADDR_POSTAL_CODE
,H.branch_number ROUTING_NUMBER
,(glc.SEGMENT1 ||’.’|| glc.SEGMENT2 || ‘.’ || glc.SEGMENT3 || ‘.’ || glc.SEGMENT4 || ‘.’ || glc.SEGMENT5 || ‘.’ || glc.SEGMENT6 || ‘.’ || glc.SEGMENT7 || ‘.’ || glc.SEGMENT8 || ‘.’ || glc.SEGMENT9) Liability_Distributions, SUBSTR (
GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
GLC.CHART_OF_ACCOUNTS_ID,
1,
GLC.SEGMENT1
),
1,
40
)
LEGAL_ENTITY,H.EFT_SWIFT_CODE BIC_NUMBER
FROM
POZ_SUPPLIER_SITES_ALL_M A,
POZ_SUPPLIERS_V B,
HR_OPERATING_UNITS HOU,
IBY_EXTERNAL_PAYEES_ALL D ,
iby_pmt_instr_uses_all E,
iby_ext_bank_accounts G,
iby_account_owners F,
ce_bank_branches_v H,
POZ_SUPPLIERS_PII I,
AP_TERMS J,
IBY_EXT_PARTY_PMT_MTHDS K,
poz_site_assignments_all_m L,
hr_locations hla_ship,
hr_locations hla_bill,
POZ_SUPPLIER_ADDRESS_V N,
gl_code_combinations glc
WHERE 1=1
AND hou.ORGANIZATION_ID=A.PRC_BU_ID
AND A.VENDOR_ID=B.VENDOR_ID
AND A.TERMS_ID=J.TERM_ID(+)
AND K.EXT_PMT_PARTY_ID(+)=D.EXT_PAYEE_ID
AND B.PARTY_ID=D.payee_party_id(+)
AND A.VENDOR_SITE_ID=D.SUPPLIER_SITE_ID(+)
AND H.branch_party_id(+) = G.branch_id
AND F.ext_bank_account_id = G.ext_bank_account_id(+)
AND F.ext_bank_account_id(+) = E.instrument_id
AND F.account_owner_party_id(+)=D.payee_party_id
AND D.ext_payee_id = E.ext_pmt_party_id(+)
AND B.VENDOR_ID=I.VENDOR_ID(+)
and hla_ship.location_id(+) = L.ship_to_location_id
and hla_bill.location_id (+) = L.bill_to_location_id
and L.VENDOR_SITE_ID(+)=A.VENDOR_SITE_ID
AND N.VENDOR_ID(+)=B.VENDOR_ID
AND A.PARTY_SITE_ID=N.PARTY_SITE_ID(+)
and glc.code_combination_id = L.ACCTS_PAY_CODE_COMBINATION_ID
