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
No comments:
Post a Comment