JL Colombian Receivables Cash Receipt Report- Not Supported: Reserved For Future Use
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Colombian Receivables Cash Receipt Report
Application: Latin America Localizations
Source: Colombian Receivables Cash Receipt Report (XML) - Not Supported: Reserved For Future Use
Short Name: JLCOARCR_XML
DB package: JL_JLCOARCR_XMLP_PKG
Description: Colombian Receivables Cash Receipt Report
Application: Latin America Localizations
Source: Colombian Receivables Cash Receipt Report (XML) - Not Supported: Reserved For Future Use
Short Name: JLCOARCR_XML
DB package: JL_JLCOARCR_XMLP_PKG
Run
JL Colombian Receivables Cash Receipt Report- Not Supported: Reserved For Future Use and other Oracle EBS reports with Blitz Report™ on our demo environment
select pty.party_name customer_name, pty.jgzz_fiscal_code customer_nit, loc.address1 address1, loc.address2 address2, loc.address3 address3, loc.city || ' '|| loc.province province, ft.territory_short_name country, cr.receipt_number receipt_number, to_char(cr.receipt_date,'DD-MON-YYYY') receipt_date, cr.amount receipt_amount, cr.currency_code currency_code, rm.printed_name payment_mode, DECODE(ct.trx_number, NULL,DECODE(ra.status,'ACC','ACC', 'UNAPP','UNAPP'), ct.trx_number) invoice_number, to_char(ct.trx_date,'DD-MON-YYYY') invoice_date, SUM(NVL(ra.amount_applied,0)) amount_applied, JL_JLCOARCR_XMLP_PKG.cf_invoice_numformula(DECODE ( ct.trx_number , NULL , DECODE ( ra.status , 'ACC' , 'ACC' , 'UNAPP' , 'UNAPP' ) , ct.trx_number )) CF_INVOICE_NUM FROM hz_cust_acct_sites adr, ra_customer_trx ct, hz_cust_accounts cust, hz_parties pty, hz_locations loc, hz_party_sites pst, ar_receipt_methods rm, ar_receivable_applications ra, ar_cash_receipts cr , hz_cust_site_uses su, fnd_territories_vl ft WHERE cr.receipt_date BETWEEN (:p_receipt_date_from) AND (:p_receipt_date_to) AND cr.status IN ('APP','UNAPP','UNID') AND su.site_use_id (+) = cr.customer_site_use_id AND adr.cust_acct_site_id(+) = su.cust_acct_site_id AND adr.party_site_id = pst.party_site_id AND loc.location_id = pst.location_id AND cr.pay_from_customer = cust.cust_account_id(+) AND cust.party_id = pty.party_id AND ra.cash_receipt_id(+) = cr.cash_receipt_id AND ra.status IN( 'APP','ACC','UNAPP') AND ct.customer_trx_id(+) = ra.applied_customer_trx_id AND cr.receipt_method_id = rm.receipt_method_id AND cust.account_number BETWEEN NVL( :p_customer_number_from ,cust.account_number) AND NVL( :p_customer_number_to ,cust.account_number) AND pty.party_name BETWEEN NVL(:p_customer_name_from,pty.party_name) AND NVL( :p_customer_name_to,pty.party_name) AND rm.name = NVL(:p_payment_method,rm.name) AND rm.printed_name= NVL(:p_payment_mode,rm.printed_name) AND pty.jgzz_fiscal_code BETWEEN NVL(:p_third_party_id_from,pty.jgzz_fiscal_code) AND NVL(:p_third_party_id_to,pty.jgzz_fiscal_code) AND cr.receipt_number BETWEEN NVL(:p_receipt_number_from,cr.receipt_number) AND NVL(:p_receipt_number_to,cr.receipt_number) AND ft.territory_code = loc.country GROUP BY cr.receipt_number, pty.party_name, pty.jgzz_fiscal_code, loc.address1, loc.address2, loc.address3, loc.city, loc.province, ft.territory_short_name, cr.receipt_date, cr.amount, cr.currency_code, rm.printed_name, ra.status, ct.trx_number, ct.trx_date ORDER BY cr.receipt_number, ct.trx_number |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Operating Unit |
|
LOV | |
LEGAL_ENTITY |
|
LOV Oracle | |
Receipt Date From |
|
Date | |
Receipt Date To |
|
Date | |
Receipt Number From |
|
LOV Oracle | |
Receipt Number To |
|
LOV Oracle | |
Customer Name From |
|
LOV Oracle | |
Customer Name To |
|
LOV Oracle | |
Customer Number From |
|
LOV Oracle | |
Customer Number To |
|
LOV Oracle | |
Third Party Id From |
|
LOV Oracle | |
Third Party Id To |
|
LOV Oracle | |
Payment Method |
|
LOV Oracle | |
Payment Mode |
|
LOV Oracle |