JL Colombian Receivables Cash Receipt - draft

Description
Categories: BI Publisher
Columns: Customer Name, Customer Nit, Address1, Address2, Address3, Province, Country, Receipt Number, Receipt Date, Receipt Amount ...
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
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