External Bank Account Assigned to Suppliers

  SELECT APS.vendor_name,
         APS.segment1 vendor_num,
         APSS.vendor_site_code vendor_Site,
         HZP.party_name Bank_Name,
         IEBA.bank_Account_name,
         IEBA.bank_account_num,
         IEBA.IBAN,
       IPUA.ORDER_OF_PREFERENCE Priority,
       IPUA.START_DATE,
       IPUA.END_DATE,
         BranchOrgProfile.bank_or_branch_number Sort_Code
    FROM apps.IBY_EXT_BANK_ACCOUNTS IEBA,
         apps.IBY_PMT_INSTR_USES_ALL IPUA,
         apps.IBY_EXTERNAL_PAYEES_ALL IEPA,
         apps.AP_SUPPLIERS APS,
         apps.AP_SUPPLIER_SITES_ALL APSS,
         apps.HZ_PARTIES HZP,
         apps.HZ_ORGANIZATION_PROFILES BranchOrgProfile
   --CE_BANK_BRANCHES_V
   WHERE     1 = 1
         AND IEBA.ext_bank_account_id = IPUA.instrument_id
         AND IPUA.instrument_type = 'BANKACCOUNT'
         AND IPUA.ext_pmt_party_id = IEPA.ext_payee_id
         AND IEPA.payee_party_id = APS.party_id
         AND IEPA.party_site_id = APSS.party_site_id(+)
         AND IEPA.payment_function = 'PAYABLES_DISB'
         AND IEPA.supplier_site_id = APSS.vendor_site_id(+)
         --Bank as Party
         AND IEBA.bank_id = HZP.party_id(+)
         --         AND  IEBA.bank_id = HZ_ORGANIZATION_PROFILES.party_id(+)
         --         AND  IEBA.branch_id = CE_BANK_BRANCHES_V.branch_party_id(+)
--         AND IEBA.BANK_ACCOUNT_NUM = '02249990'
         --              AND APS.segment1 = '95921'
         AND apss.org_id = 84
         AND payment_flow = 'DISBURSEMENTS'
         AND IEBA.branch_id = BranchOrgProfile.PARTY_ID(+)
ORDER BY aps.segment1, apss.vendor_site_Code, ipua.order_of_preference

AR Lockbox Queries

select * from apps.ar_transmissions_all
where org_id = XX
and transmission_name = 'BANK_XXXX_14290534'

select * from apps.ar_payments_interface_all
where transmission_request_id = XXX --- Get the transmission_request_id from above query
--- Need to check the status column for errors

select * from apps.AR_BATCHES_ALL
where TRANSMISSION_REQUEST_ID = XXX
-- Get the batchid here using transmission_request_id

select * from apps.ar_interim_cash_receipts_all
where batch_id = XXX
-- Check for any stuck receipts, normally this table should be empty otherwise period will not be closed

select * from apps.ar_cash_Receipt_history_all
where batch_id = XXX
-- Get the batch_id from AR_BATCHES_ALL table
-- To get all the receipts created under this batch
-- Take the event_id to get the GL Posting details

select * from apps.ar_cash_Receipts_all
where cash_Receipt_id = XXX
-- Get the receipt details, based on the cash_Receipt_id from history table

select * from apps.ar_receivable_applications_all
where cash_Receipt_id = XXX
-- To get the receipt application details

Journal Related to AP Invoice

SELECT DISTINCT
       aia.invoice_num,
       --eve_line.ENTERED_DR "Entered DR in SLA",
       --    eve_line.ENTERED_CR "Entered CR in SLA",
       --    eve_line.ACCOUNTED_DR "Accounted DR in SLA",
       --    eve_line.ACCOUNTED_CR "Accounted CR in SLA",
       --    je_lin.ENTERED_DR "Entered DR in GL",
       --    je_lin.ACCOUNTED_DR "Accounted DR in GL",
       --    eve_line.ACCOUNTING_CLASS_CODE "Accounting Class",
       eve_hed.JE_CATEGORY_NAME "JE Category Name",
       je_bat.NAME "Journal Batch Name",
       je_hed.NAME "Journal Header Name",
       je_lin.JE_LINE_NUM "Journal Line Number",
       je_lin.Description "Journal Line Desc",
          gcc.SEGMENT1
       || '.'
       || gcc.SEGMENT2
       || '.'
       || gcc.SEGMENT3
       || '.'
       || gcc.SEGMENT4
       || '.'
       || gcc.SEGMENT5
          "Code Combination"
  FROM apps.ap_invoices_all aia,
       apps.ap_invoice_distributions_all aida,
       apps.xla_events eve,
       apps.xla_ae_headers eve_hed,
       apps.xla_ae_lines eve_line,
       apps.GL_IMPORT_REFERENCES imp_ref,
       apps.gl_code_combinations gcc,
       apps.gl_je_batches je_bat,
       apps.gl_je_headers je_hed,
       apps.gl_je_lines je_lin
 WHERE     1 = 1
       AND aia.invoice_Id = aida.invoice_id
       AND aida.accounting_event_id = eve.event_id
       AND eve.event_Id = eve_hed.event_id
       AND aida.accounting_event_id = eve_hed.event_id
       AND eve_hed.ae_header_id = eve_line.ae_header_id
       AND imp_ref.GL_SL_LINK_ID = eve_line.GL_SL_LINK_ID
       AND imp_ref.GL_SL_LINK_TABLE = eve_line.GL_SL_LINK_TABLE
       AND imp_ref.je_header_id = je_hed.je_header_Id
       AND imp_ref.je_header_id = je_lin.je_header_Id
       AND imp_ref.je_batch_Id = je_bat.je_batch_Id
       AND je_bat.je_batch_Id = je_hed.je_batch_id
       AND je_hed.je_header_id = je_lin.je_header_Id
       AND eve_line.DESCRIPTION = je_lin.DESCRIPTION
       AND gcc.CODE_COMBINATION_ID = eve_line.CODE_COMBINATION_ID
       AND gcc.CODE_COMBINATION_ID = je_lin.CODE_COMBINATION_ID
       AND aia.invoice_Id = XXXXXX

Query to get the bank account assigned to supplier

SELECT sup.segment1 Vendor_Num,
       sup.vendor_name,
       sit.vendor_site_code,
       ext.BANK_ACCOUNT_NUM,
       inst.creation_date Bank_Acct_Assign_Date
  FROM apps.ap_suppliers sup,
       apps.ap_supplier_sites_all sit,
       apps.iby_ext_bank_Accounts ext,
       apps.iby_account_owners own,
       apps.iby_external_payees_all pay,
       apps.iby_pmt_instr_uses_all inst
 WHERE     1 = 1
       AND sup.vendor_id = sit.vendor_id
       AND own.ACCOUNT_OWNER_PARTY_ID = sup.party_id
       AND ext.EXT_BANK_ACCOUNT_ID = own.EXT_BANK_ACCOUNT_ID
       AND pay.supplier_site_id = sit.vendor_site_id
       AND pay.payee_party_id = sup.party_id
       AND pay.EXT_PAYEE_ID = inst.ext_pmt_party_id
       AND inst.INSTRUMENT_ID = ext.EXT_BANK_ACCOUNT_ID
       AND sup.segment1 = '201671'

Query to check Bank Account Assignment

SELECT cust.account_number supp_cust_num, 'Customer' TYPE,party.PARTY_NAME, bank.bank_account_num
  FROM apps.iby_ext_bank_accounts bank,
       apps.hz_cust_accounts cust,
       apps.iby_Account_owners own,
       apps.hz_parties party
 WHERE     1 = 1
       AND bank.ext_bank_account_id = own.ext_bank_account_id
       AND cust.party_id = own.ACCOUNT_OWNER_PARTY_ID
       AND party.party_id = own.ACCOUNT_OWNER_PARTY_ID
       AND bank.bank_account_num = '&bank_account'
UNION
SELECT supp.segment1 supp_cust_num, 'Supplier' TYPE,party.PARTY_NAME, bank.bank_account_num
  FROM apps.iby_ext_bank_accounts bank,
       apps.ap_suppliers supp,
       apps.iby_Account_owners own,
       apps.hz_parties party
 WHERE     1 = 1
       AND bank.ext_bank_account_id = own.ext_bank_account_id
       AND supp.party_id = own.ACCOUNT_OWNER_PARTY_ID
       AND party.party_id = own.ACCOUNT_OWNER_PARTY_ID
       AND bank.bank_account_num = '&bank_account'