Payable : Find Bank information

 SELECT cba.bank_account_name            "Bank Account Name",
       cba.bank_account_num             "Bank Account Number",
       cba.multi_currency_allowed_flag  "Multi Currency Flag",
       cba.zero_amount_allowed          "Zero Amount Flag",
       cba.account_classification       "Account Classification",
       bb.bank_name                     "Bank Name",
       bb.bank_branch_type              "Bank Branch Type",
       bb.bank_branch_name              "Bank Branch Name",
       bb.bank_branch_number            "Bank Branch Number",
       bb.eft_swift_code                "Swift Code",
       ou.name                          "Operating Unit",
       gcf.concatenated_segments        "GL Code Combination"
  FROM ce_bank_accounts          cba,
       ce_bank_acct_uses_all     bau,
       cefv_bank_branches        bb,
       hr_operating_units        ou,
       gl_code_combinations_kfv  gcf
 WHERE cba.bank_account_id = bau.bank_account_id
   AND cba.bank_branch_id  = bb.bank_branch_id
   AND ou.organization_id  = bau.org_id
   AND cba.asset_code_combination_id = gcf.code_combination_id
   AND (cba.end_date IS NULL OR cba.end_date > TRUNC(SYSDATE))
 ORDER BY TO_NUMBER(cba.bank_account_num);

Read More

AR : Fetch Customer Bank detail

select DISTINCT ac.customer_name
,ac.customer_number
from iby_pmt_instr_uses_all instr_assign,iby_external_payers_all
payee,iby_ext_bank_accounts bankacct,hz_cust_accounts_all hcal,ar_customers acwhere
instr_assign.instrument_id = bankacct.ext_bank_account_id
and instr_assign.ext_pmt_party_id = payee.ext_payer_id
and instr_assign.instrument_type = 'BANKACCOUNT'
and instr_assign.payment_flow ='FUNDS_CAPTURE'
and instr_assign.payment_function = 'CUSTOMER_PAYMENT'
and hcal.cust_account_id = payee.cust_account_id
and ac.customer_number = hcal.account_number
and bankacct.bank_account_num = <Bank account>

Read More

CM: Fetch Invoices related Reconciliation Per Currency

SELECT DISTINCT cel.*

FROM   CE_STATEMENT_LINES cel,

       CE_STATEMENT_HEADERS ceh,

       CE_STATEMENT_RECONCILS_ALL csr,

       AP_INVOICE_PAYMENTS_ALL aip,

       AP_CHECKS_ALL ac

WHERE cel.statement_header_id   = ceh.statement_header_id

AND   ac.check_id               = aip.check_id

AND   aip.invoice_id            = '166014'

AND   aip.check_id              = ac.check_id

AND   ac.bank_account_id        = ceh.bank_account_id

AND   TO_CHAR(ac.check_number)  = cel.bank_trx_number

AND   csr.statement_line_id     = cel.statement_line_id;

Read More