Oracle Cloud Fusion R13 Account Payable (AP) Payment Register - draft SQL 

========================================================================= 

SELECT  nvl(cpd.payment_document_name, :c_nls_none_ep) c_check_stock_name,

        ch.check_number c_check_number,

        ch.check_date  c_check_date,

        ch.amount  c_amount ,

        substr(ch.vendor_name,1,39)  c_vendor_name,

        substr(ch.vendor_site_code,1,10)

        c_vendor_site_code,

        substr(ch.address_line1,1,23)

        c_address_line1,

        substr(ch.address_line2,1,23)

        c_address_line2,

        substr(ch.address_line3,1,23)

        c_address_line3,

        substr(ch.city,1,13) c_city,

        substr(ch.state,1,4)  c_state,

        substr(ch.zip,1,6) c_zip,

        substr(ft.territory_short_name,1,23) c_country,

        ch.cleared_date  c_cleared_date,

        ch.cleared_amount c_cleared_amount,

        lk2.displayed_field c_nls_status,

        br.bank_name  c_bank,

        br.bank_branch_name  c_branch,

        ch.bank_account_name  c_account,

        ch.bank_account_id  c_accountid,

        br.branch_party_id  c_bank_branch,

        ba.currency_code  c_currency_code,

        ch.currency_code  c_pay_currency_code, 

/* AP_APXMTDCR_XMLP_PKG.c_currency_descformula(ba.currency_code) C_CURRENCY_DESC, 

AP_APXMTDCR_XMLP_PKG.c_pay_currency_descformula(ch.currency_code) C_PAY_CURRENCY_DESC, 

AP_APXMTDCR_XMLP_PKG.check_flag(ba.currency_code, ch.currency_code) C_CHECK_CURR_FLAG  */

--,&c_amount C_AMOUNT_FOR_SUM

FROM    ap_checks ch, 

        ce_payment_documents cpd,

        ce_bank_accounts ba,

        ce_bank_acct_uses_all cbau, 

        ce_bank_branches_v br,

        ap_lookup_codes  lk2,

        fnd_territories_vl ft

where  cpd.payment_document_id = ch.payment_document_id(+)   

  and   ch.ce_bank_acct_use_id = cbau.bank_acct_use_id

  and   cbau.bank_account_id = ba.bank_account_id

  and   ba.bank_branch_id = br.branch_party_id

  and   ch.payment_type_flag = nvl(:P_PAYMENT_TYPE,

ch.payment_type_flag )

  and   ch.check_date between

        (:p_start_date) and

        (:p_end_date)

  and   ch.country = ft.territory_code(+)

  and   lk2.lookup_type = 'CHECK STATE'

  and   lk2.lookup_code =

        ch.status_lookup_code

order by upper(br.bank_name), 

      upper(br.bank_branch_name), 

      upper(ch.bank_account_name),

      ch.currency_code,

      cpd.payment_document_name,

      ch.check_number