JL Colombian Receivables Cash Receipt - draft

Description
Categories: BI Publisher
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.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
GL_SET_OF_BOOKS_ID
 
Number
Payment Mode
 
LOV Oracle
Payment Method
 
LOV Oracle
Third Party Id To
 
LOV Oracle
Third Party Id From
 
LOV Oracle
Customer Number To
 
LOV Oracle
Customer Number From
 
LOV Oracle
Customer Name To
 
LOV Oracle
Customer Name From
 
LOV Oracle
Receipt Number To
 
LOV Oracle
Receipt Number From
 
LOV Oracle
Receipt Date To
 
Date
Receipt Date From
 
Date
LEGAL_ENTITY
 
LOV Oracle