JG Regional Dunning Letter - draft

➜ Back to Library
Description
Categories: BI Publisher
Application: Regional Localizations
Source: Regional Dunning Letter (XML) - Not Supported: Reserved For Future Use
Short Name: JGZZRDLR_XML
DB package: JG_JGZZRDLR_XMLP_PKG
Run JG Regional Dunning Letter - draft and other Oracle EBS reports with Blitz Report™ on our demo environment
select  	distinct
	rcx.customer_trx_id							TRX_ID,
	NVL(rps.exchange_rate,1)						exchange_rate,
	hca.cust_account_id 							Cusid,
	substr(hp.party_name,1, 25)    					Customer,
	nvl(substr(hca.account_number,1,15), 'No customer number')  			Cusno,
	substr(hl.address1,1,20)|| ' '|| substr(hl.city,1,15)  				Site, 
	nvl(substr(hcsu.tax_reference,1, 20),'No TAX ID')    				tax_id, 
	substr(rcx.invoice_currency_code,1,3)					Currency_Code,
	rcx.trx_number							Invoice_number,
	rcx.trx_date							Invoice_date,
	substr(rcxt.type ,1,10)						Trans_Type,
	sum(rps.amount_due_original) 						Original_Frgn_amount,
	sum(rps.amount_due_remaining) 						Amount_frgn_Due_Rem,
	sum(nvl(rps.exchange_rate,1)*rps.amount_due_original) 			Original_func_amount,
	sum(nvl(rps.exchange_rate,1)*nvl(rps.amount_due_remaining, 0)) 			Amount_func_Due_Rem,
	min (rps.due_date)  							Due_Date,
	:P_AS_OF_DATE1-(min(to_date(rps.due_date,'DD-MON-RRRR')))	Days_Past_Due, 
	&C_PRT_TOT_TAX_FUNC C_PRT_TOT_TAX_FUNC,
	&C_PRT_TOT_TAX_FUNC_REM C_PRT_TOT_TAX_FUNC_REM, 
	&C_PRT_TOT_SITE_FUNC C_PRT_TOT_SITE_FUNC, 
	&C_PRT_TOT_SITE_FUNC_REM C_PRT_TOT_SITE_FUNC_REM, 
	&C_PRT_TOT_NET_FOREIGN C_PRT_TOT_NET_FOREIGN, 
	&C_PRT_TOT_NET_FOR_REM C_PRT_TOT_NET_FOR_REM, 
	&C_PRT_TOT_R_FOREIGN C_PRT_TOT_R_FOREIGN, 
	&C_PRT_TOT_O_FOREIGN C_PRT_TOT_O_FOREIGN, 
	&C_PRT_TOT_CUR_FUNC C_PRT_TOT_CUR_FUNC, 
	&C_PRT_TOT_CUR_FUNC_REM C_PRT_TOT_CUR_FUNC_REM,
	JG_JGZZRDLR_XMLP_PKG.CF_INVOICE_DATEFORMULA(rcx.trx_date) CF_INVOICE_DATE, 
--	&C_PRT_ORIG_FRGN_AMOUNT C_PRT_ORIG_FRGN_AMOUNT, 
--	&C_PRT_FRGN_AMT_DUE_REM C_PRT_FRGN_AMT_DUE_REM, 
--	&C_PRT_ORIG_FUNC_AMOUNT C_PRT_ORIG_FUNC_AMOUNT, 
--	&C_PRT_FUNC_AMT_DUE_REM C_PRT_FUNC_AMT_DUE_REM, 
	sum(rps.amount_due_original) C_PRT_ORIG_FRGN_AMOUNT,
	JG_JGZZRDLR_XMLP_PKG.org_cur_amt_remformula(sum ( rps.amount_due_remaining ), JG_JGZZRDLR_XMLP_PKG.applied_amountformula(rcx.customer_trx_id)) C_PRT_FRGN_AMT_DUE_REM,
	sum(nvl(rps.exchange_rate,1)*rps.amount_due_original) C_PRT_ORIG_FUNC_AMOUNT,
	JG_JGZZRDLR_XMLP_PKG.fun_cur_amt_remformula(NVL ( rps.exchange_rate , 1 ), JG_JGZZRDLR_XMLP_PKG.org_cur_amt_remformula(sum ( rps.amount_due_remaining ), JG_JGZZRDLR_XMLP_PKG.applied_amountformula(rcx.customer_trx_id)), sum ( nvl ( rps.exchange_rate , 1 ) * nvl ( rps.amount_due_remaining , 0 ) )) C_PRT_FUNC_AMT_DUE_REM,
	JG_JGZZRDLR_XMLP_PKG.C_DUE_DATEFORMULA(min (rps.due_date)) C_DUE_DATE, 
	JG_JGZZRDLR_XMLP_PKG.applied_amountformula(rcx.customer_trx_id) APPLIED_AMOUNT, 
	JG_JGZZRDLR_XMLP_PKG.org_cur_amt_remformula(sum ( rps.amount_due_remaining ), JG_JGZZRDLR_XMLP_PKG.applied_amountformula(rcx.customer_trx_id)) ORG_CUR_AMT_REM, 
	JG_JGZZRDLR_XMLP_PKG.fun_cur_amt_remformula(NVL ( rps.exchange_rate , 1 ), JG_JGZZRDLR_XMLP_PKG.org_cur_amt_remformula(sum ( rps.amount_due_remaining ), JG_JGZZRDLR_XMLP_PKG.applied_amountformula(rcx.customer_trx_id)), sum ( nvl ( rps.exchange_rate , 1 ) * nvl ( rps.amount_due_remaining , 0 ) )) FUN_CUR_AMT_REM,
	JG_JGZZRDLR_XMLP_PKG.org_cur_amountformula(JG_JGZZRDLR_XMLP_PKG.org_cur_amt_remformula(sum ( rps.amount_due_remaining ), JG_JGZZRDLR_XMLP_PKG.applied_amountformula(rcx.customer_trx_id)), sum ( rps.amount_due_original )) ORG_CUR_AMOUNT, 
	JG_JGZZRDLR_XMLP_PKG.fun_cur_amountformula(JG_JGZZRDLR_XMLP_PKG.org_cur_amt_remformula(sum ( rps.amount_due_remaining ), JG_JGZZRDLR_XMLP_PKG.applied_amountformula(rcx.customer_trx_id)), sum ( nvl ( rps.exchange_rate , 1 ) * rps.amount_due_original )) FUN_CUR_AMOUNT
from 
	ra_cust_trx_types rcxt,
	ar_payment_schedules rps,
	ra_customer_trx rcx,
	hz_parties   hp,      hz_cust_accounts   hca,         
	hz_locations hl,     				
	hz_party_sites hps,				
	hz_cust_acct_sites hcas,		         
	hz_cust_site_uses hcsu		         
where	
rps.customer_trx_id = rcx.customer_trx_id
and 	hp.party_id = hca.party_id 
and	hca.cust_account_id = rcx.bill_to_customer_id
and	rcx.cust_trx_type_id = rcxt.cust_trx_type_id
and	hca.cust_account_id = hcas.cust_account_id          		
and	hcas.cust_acct_site_id = hcsu.cust_acct_site_id  
AND    hcas.party_site_id = hps.party_site_id	
and	hp.party_id=hps.party_id				
and 	hps.location_id=hl.location_id           			
and	hcsu.site_use_id=rcx.bill_to_site_use_id  			
and 	rcx.invoice_currency_code like decode(:P_CURRENCY, 'All','%','','%' ,:P_CURRENCY)
and   TRUNC(rps.gl_date)  <=TRUNC( :P_DATE_TO)
AND TRUNC(rcx.trx_date ) <=TRUNC(:P_DATE_TO)
group by 
	hca.cust_account_id,
	hp.party_name, hca.account_number,
        	hl.address1, hl.city,
	hcsu.tax_reference,
	rcx.invoice_currency_code,
	rcx.trx_date,
	rcx.trx_number,
	rcx.trx_date,
	rcxt.type,
	rcx.customer_trx_id,	
	NVL(rps.exchange_rate,1)
UNION
select  distinct
	-1						TRX_ID,
	-1						exchange_rate,
	hca.cust_account_id 					Cusid,
	substr(hp.party_name,1, 25)    				Customer,
	nvl(substr(hca.account_number,1,15), 'No customer number')  	Cusno,
	substr(hl.address1,1,20)|| ' '|| substr(hl.city,1,15)  		Site,
	nvl(substr(hcsu.tax_reference,1, 20),'No TAX ID')    		tax_id,
	substr(rps.invoice_currency_code,1,3)			Currency_Code,
	'On-Account and Unapplied'      				Invoice_number,
	to_date(NULL)                        				Invoice_date,
	'ONACC'                                        				Trans_type,
	0						Original_Frgn_amount,
	sum(rps.amount_due_remaining) 				Amount_frgn_Due_Rem,
	0 						Original_func_amount,
	sum((-1)*nvl(rps.amount_due_remaining, 0))  	Amount_func_Due_Rem,
	rps.due_date  					Due_Date,
	:P_AS_OF_DATE1-(min(to_date(rps.due_date,'DD-MON-RRRR')))		Days_Past_Due, 
	&C_PRT_TOT_TAX_FUNC C_PRT_TOT_TAX_FUNC, 
	&C_PRT_TOT_TAX_FUNC_REM C_PRT_TOT_TAX_FUNC_REM, 
	&C_PRT_TOT_SITE_FUNC C_PRT_TOT_SITE_FUNC, 
	&C_PRT_TOT_SITE_FUNC_REM C_PRT_TOT_SITE_FUNC_REM, 
	&C_PRT_TOT_NET_FOREIGN C_PRT_TOT_NET_FOREIGN, 
	&C_PRT_TOT_NET_FOR_REM C_PRT_TOT_NET_FOR_REM, 
	&C_PRT_TOT_R_FOREIGN C_PRT_TOT_R_FOREIGN, 
	&C_PRT_TOT_O_FOREIGN C_PRT_TOT_O_FOREIGN, 
	&C_PRT_TOT_CUR_FUNC C_PRT_TOT_CUR_FUNC, 
	&C_PRT_TOT_CUR_FUNC_REM C_PRT_TOT_CUR_FUNC_REM, 
	JG_JGZZRDLR_XMLP_PKG.CF_INVOICE_DATEFORMULA(to_date(NULL)) CF_INVOICE_DATE, 
	&C_PRT_ORIG_FRGN_AMOUNT C_PRT_ORIG_FRGN_AMOUNT, 
	&C_PRT_FRGN_AMT_DUE_REM C_PRT_FRGN_AMT_DUE_REM, 
	&C_PRT_ORIG_FUNC_AMOUNT C_PRT_ORIG_FUNC_AMOUNT, 
	&C_PRT_FUNC_AMT_DUE_REM C_PRT_FUNC_AMT_DUE_REM, 
	JG_JGZZRDLR_XMLP_PKG.C_DUE_DATEFORMULA(rps.due_date) C_DUE_DATE, 
	JG_JGZZRDLR_XMLP_PKG.applied_amountformula(-1) APPLIED_AMOUNT, 
	JG_JGZZRDLR_XMLP_PKG.org_cur_amt_remformula(sum ( rps.amount_due_remaining ), JG_JGZZRDLR_XMLP_PKG.applied_amountformula(-1)) ORG_CUR_AMT_REM, 
	JG_JGZZRDLR_XMLP_PKG.fun_cur_amt_remformula(-1, JG_JGZZRDLR_XMLP_PKG.org_cur_amt_remformula(sum ( rps.amount_due_remaining ), JG_JGZZRDLR_XMLP_PKG.applied_amountformula(-1)), sum ( nvl ( rps.exchange_rate , 1 ) * nvl ( rps.amount_due_remaining , 0 ) )) FUN_CUR_AMT_REM, 
	JG_JGZZRDLR_XMLP_PKG.org_cur_amountformula(JG_JGZZRDLR_XMLP_PKG.org_cur_amt_remformula(sum ( rps.amount_due_remaining ), JG_JGZZRDLR_XMLP_PKG.applied_amountformula(-1)), 0) ORG_CUR_AMOUNT, 
	JG_JGZZRDLR_XMLP_PKG.fun_cur_amountformula(JG_JGZZRDLR_XMLP_PKG.org_cur_amt_remformula(sum ( rps.amount_due_remaining ), JG_JGZZRDLR_XMLP_PKG.applied_amountformula(-1)), 0) FUN_CUR_AMOUNT
from 
	hz_parties   hp,        hz_cust_accounts    hca,           
	hz_locations hl,
	hz_party_sites hps,
	hz_cust_acct_sites hcas,			
	hz_cust_site_uses hcsu,			
	ar_payment_schedules rps,
	ar_cash_receipt_history crh
where	rps.amount_due_remaining <> 0
and            rps.cash_receipt_id IS NOT NULL
AND    hcas.party_site_id = hps.party_site_id
and 	hp.party_id = hca.party_id
and	hca.cust_account_id = hcas.cust_account_id	
and	hcas.cust_acct_site_id=hcsu.cust_acct_site_id	
and	hcsu.site_use_id=rps.customer_site_use_id	
and	hp.party_id=hps.party_id
and 	hps.location_id=hl.location_id
and 	rps.invoice_currency_code like decode(:P_CURRENCY, 'All','%','','%' ,:P_CURRENCY)
and	TRUNC(crh.trx_date ) <= TRUNC( :P_DATE_TO)
and	rps.cash_receipt_id = crh.cash_receipt_id
and	crh.current_record_flag	='Y'
and	crh.status			='CLEARED' 
group by 
	hca.cust_account_id,
	hp.party_name, 
	hca.account_number,
        	hl.address1,				
	hl.city,					
	hcsu.tax_reference,				
	rps.invoice_currency_code,
                rps.due_date,
	-1
HAVING    sum(rps.amount_due_remaining)<>0
UNION
SELECT  DISTINCT
	-1							TRX_ID,
	-1							exchange_rate,
        	hca.cust_account_id 						Cusid,
        	substr(hp.party_name,1, 25)    					Customer,
        	nvl(substr(hca.account_number,1,15), 'No customer number')  		Cusno,
        	substr(hl.address1,1,20)|| ' '|| substr(hl.city,1,15)  			Site,
        	nvl(substr(hcsu.tax_reference,1, 20),'No TAX ID')    			tax_id,
        	rps.invoice_currency_code					Currency_Code,
        	rps.trx_number 						Invoice_Number,
        	rps.trx_date                        					Invoice_date,
        	rps.class							Trans_Type,
       	&P_RISKVAR1						Original_Frgn_amount,
       	0                       						Amount_frgn_Due_Rem,
       	(-1)* &P_RISKVAR1            			Original_func_amount,
       	0                                                                   				Amount_func_Due_Rem,
       	rps.due_date                                                        			Due_Date,
        	:P_AS_OF_DATE1 -to_date(rps.due_date,'DD-MON-RRRR')	Days_Past_Due, 
	&C_PRT_TOT_TAX_FUNC C_PRT_TOT_TAX_FUNC, 
	&C_PRT_TOT_TAX_FUNC_REM C_PRT_TOT_TAX_FUNC_REM, 
	&C_PRT_TOT_SITE_FUNC C_PRT_TOT_SITE_FUNC, 
	&C_PRT_TOT_SITE_FUNC_REM C_PRT_TOT_SITE_FUNC_REM, 
	&C_PRT_TOT_NET_FOREIGN C_PRT_TOT_NET_FOREIGN, 
	&C_PRT_TOT_NET_FOR_REM C_PRT_TOT_NET_FOR_REM, 
	&C_PRT_TOT_R_FOREIGN C_PRT_TOT_R_FOREIGN, 
	&C_PRT_TOT_O_FOREIGN C_PRT_TOT_O_FOREIGN, 
	&C_PRT_TOT_CUR_FUNC C_PRT_TOT_CUR_FUNC, 
	&C_PRT_TOT_CUR_FUNC_REM C_PRT_TOT_CUR_FUNC_REM, 
	JG_JGZZRDLR_XMLP_PKG.CF_INVOICE_DATEFORMULA(rps.trx_date) CF_INVOICE_DATE, 
	&C_PRT_ORIG_FRGN_AMOUNT C_PRT_ORIG_FRGN_AMOUNT, 
	&C_PRT_FRGN_AMT_DUE_REM C_PRT_FRGN_AMT_DUE_REM, 
	&C_PRT_ORIG_FUNC_AMOUNT C_PRT_ORIG_FUNC_AMOUNT, 
	&C_PRT_FUNC_AMT_DUE_REM C_PRT_FUNC_AMT_DUE_REM, 
	JG_JGZZRDLR_XMLP_PKG.C_DUE_DATEFORMULA(rps.due_date) C_DUE_DATE, 
	JG_JGZZRDLR_XMLP_PKG.applied_amountformula(-1) APPLIED_AMOUNT, 
	JG_JGZZRDLR_XMLP_PKG.org_cur_amt_remformula(0, JG_JGZZRDLR_XMLP_PKG.applied_amountformula(-1)) ORG_CUR_AMT_REM, 
	JG_JGZZRDLR_XMLP_PKG.fun_cur_amt_remformula(-1, JG_JGZZRDLR_XMLP_PKG.org_cur_amt_remformula(0, JG_JGZZRDLR_XMLP_PKG.applied_amountformula(-1)) , 0) FUN_CUR_AMT_REM, 
	JG_JGZZRDLR_XMLP_PKG.org_cur_amountformula(JG_JGZZRDLR_XMLP_PKG.org_cur_amt_remformula(0, JG_JGZZRDLR_XMLP_PKG.applied_amountformula(-1)) , 0) ORG_CUR_AMOUNT, 
	JG_JGZZRDLR_XMLP_PKG.fun_cur_amountformula(JG_JGZZRDLR_XMLP_PKG.org_cur_amt_remformula(0, JG_JGZZRDLR_XMLP_PKG.applied_amountformula(-1)) , 0) FUN_CUR_AMOUNT
from
            	ar_payment_schedules rps,
            	ar_cash_receipts cr,
        	ar_cash_receipt_history crh,
            	ar_receivable_applications ara,
            	hz_parties   hp,    hz_cust_accounts  hca,          
            	hz_locations hl,
	hz_party_sites hps,
	hz_cust_acct_sites hcas,			
        	hz_cust_site_uses hcsu			
where       ara.applied_payment_schedule_id = rps.payment_schedule_id
and         	ara.amount_applied = rps.amount_due_original
and	ara.cash_receipt_id = cr.cash_receipt_id
and         	cr.cash_receipt_id = crh.cash_receipt_id
AND    hcas.party_site_id = hps.party_site_id
and 	hp.party_id = hca.party_id
and        	hca.cust_account_id = rps.customer_id
and         	rps.customer_site_use_id = hcsu.site_use_id		
and         	hcas.cust_acct_site_id=hcsu.cust_acct_site_id		
and	cr.customer_site_use_id = hcsu.site_use_id		
and	hp.party_id=hps.party_id
and	hps.location_id=hl.location_id
and         	cr.reversal_date is null
and	crh.current_record_flag||'' = 'Y'
and         	rps.amount_due_remaining = 0
and         	crh.status not in (decode (crh.factor_flag, 'Y', 'RISK_ELIMINATED','N','CLEARED'),'REVERSED')
and 	rps.invoice_currency_code like decode(:P_CURRENCY, 'All','%','','%' ,:P_CURRENCY)
and 	TRUNC(rps.gl_date) <=TRUNC( :P_DATE_TO)
AND 	TRUNC(rps.trx_date)  <=TRUNC( :P_DATE_TO)
and  	TRUNC(ara.apply_date) <= TRUNC( :P_DATE_TO)
AND	(-1)* &P_RISKVAR1  <>0
UNION
SELECT  DISTINCT
	-1							TRX_ID,
	-1							exchange_rate,
        	hca.cust_account_id 						Cusid,
        	substr(hp.party_name,1, 25)    					Customer,
        	nvl(substr(hca.account_number,1,15), 'No customer number')  		Cusno,
        	substr(hl.address1,1,20)|| ' '|| substr(hl.city,1,15)  			Site,
        	nvl(substr(hcsu.tax_reference,1, 20),'No TAX ID')    			tax_id,
        	rcx.invoice_currency_code					Currency_Code,
        	'Receipts at Risk' 						Invoice_Number,
        	to_date(NULL)                        					Invoice_date,
        	'RECT'							Trans_Type,
       	0							Original_Frgn_amount,
       	&P_RISKVAR2						Amount_frgn_Due_Rem,
       	0 							Original_func_amount,
       	nvl(rcx.exchange_rate,1)* &P_RISKVAR2				Amount_func_Due_Rem,
       	aps.due_date     						Due_Date,
        	:P_AS_OF_DATE1 -to_date(aps.due_date,'DD-MON-RRRR') Days_Past_Due, 
	&C_PRT_TOT_TAX_FUNC C_PRT_TOT_TAX_FUNC, 
	&C_PRT_TOT_TAX_FUNC_REM C_PRT_TOT_TAX_FUNC_REM, 
	&C_PRT_TOT_SITE_FUNC C_PRT_TOT_SITE_FUNC, 
	&C_PRT_TOT_SITE_FUNC_REM C_PRT_TOT_SITE_FUNC_REM, 
	&C_PRT_TOT_NET_FOREIGN C_PRT_TOT_NET_FOREIGN, 
	&C_PRT_TOT_NET_FOR_REM C_PRT_TOT_NET_FOR_REM, 
	&C_PRT_TOT_R_FOREIGN C_PRT_TOT_R_FOREIGN, 
	&C_PRT_TOT_O_FOREIGN C_PRT_TOT_O_FOREIGN, 
	&C_PRT_TOT_CUR_FUNC C_PRT_TOT_CUR_FUNC, 
	&C_PRT_TOT_CUR_FUNC_REM C_PRT_TOT_CUR_FUNC_REM, 
	JG_JGZZRDLR_XMLP_PKG.CF_INVOICE_DATEFORMULA(to_date(NULL)) CF_INVOICE_DATE, 
	&C_PRT_ORIG_FRGN_AMOUNT C_PRT_ORIG_FRGN_AMOUNT, 
	&C_PRT_FRGN_AMT_DUE_REM C_PRT_FRGN_AMT_DUE_REM, 
	&C_PRT_ORIG_FUNC_AMOUNT C_PRT_ORIG_FUNC_AMOUNT, 
	&C_PRT_FUNC_AMT_DUE_REM C_PRT_FUNC_AMT_DUE_REM, 
	JG_JGZZRDLR_XMLP_PKG.C_DUE_DATEFORMULA(aps.due_date) C_DUE_DATE, 
	JG_JGZZRDLR_XMLP_PKG.applied_amountformula(-1) APPLIED_AMOUNT, 
	JG_JGZZRDLR_XMLP_PKG.org_cur_amt_remformula(&P_RISKVAR2, JG_JGZZRDLR_XMLP_PKG.applied_amountformula(-1)) ORG_CUR_AMT_REM, 
	JG_JGZZRDLR_XMLP_PKG.fun_cur_amt_remformula((-1), JG_JGZZRDLR_XMLP_PKG.org_cur_amt_remformula(&P_RISKVAR2, JG_JGZZRDLR_XMLP_PKG.applied_amountformula(-1)), nvl(rcx.exchange_rate,1)* &P_RISKVAR2) FUN_CUR_AMT_REM, 
	JG_JGZZRDLR_XMLP_PKG.org_cur_amountformula(JG_JGZZRDLR_XMLP_PKG.org_cur_amt_remformula(&P_RISKVAR2, JG_JGZZRDLR_XMLP_PKG.applied_amountformula(-1)), 0) ORG_CUR_AMOUNT, 
	JG_JGZZRDLR_XMLP_PKG.fun_cur_amountformula(JG_JGZZRDLR_XMLP_PKG.org_cur_amt_remformula(&P_RISKVAR2, JG_JGZZRDLR_XMLP_PKG.applied_amountformula(-1)), 0) FUN_CUR_AMOUNT
from
            	ar_payment_schedules aps,
            	ar_cash_receipts cr,
        	ar_cash_receipt_history crh,
            	ar_receivable_applications ara,
            	hz_parties   hp,     hz_cust_accounts   hca,       
            	ra_customer_trx rcx,
            	hz_locations hl,
	hz_party_sites hps,			
	hz_cust_acct_sites hcas,		       
        	hz_cust_site_uses hcsu		
where       ara.applied_customer_trx_id = rcx.customer_trx_id
and         	ara.payment_schedule_id = aps.payment_schedule_id
and         	cr.cash_receipt_id = aps.cash_receipt_id
and         	cr.cash_receipt_id = crh.cash_receipt_id
and   	hp.party_id = hca.party_id
AND    hcas.party_site_id = hps.party_site_id
and         	hca.cust_account_id = aps.customer_id
and         	hca.cust_account_id = hcas.cust_account_id	
and         	hcas.cust_acct_site_id=hcsu.cust_acct_site_id	
and	cr.customer_site_use_id = hcsu.site_use_id	
and 	hp.party_id=hps.party_id
and 	hps.location_id=hl.location_id
and         	ara.status = 'APP'
and         	cr.reversal_date is null
and	crh.current_record_flag||'' = 'Y'
and         	crh.status not in (decode (crh.factor_flag, 'Y', 'RISK_ELIMINATED','N','CLEARED'),'REVERSED')
and 	rcx.invoice_currency_code like decode(:P_CURRENCY, 'All','%','','%' ,:P_CURRENCY)
and  	TRUNC(ara.apply_date) <= TRUNC( :P_DATE_TO)
and	((TRUNC(crh.trx_date) <=( :P_DATE_TO) and crh.status ='CLEARED'  ) or crh.status <> 'CLEARED')
AND   &P_RISKVAR2   <> 0
UNION
SELECT  DISTINCT
	-1							TRX_ID,
	-1							exchange_rate,
        	hca.cust_account_id 						Cusid,
        	substr(hp.party_name,1, 25)    					Customer,
        	nvl(substr(hca.account_number,1,15), 'No customer number')  		Cusno,
        	substr(hl.address1,1,20)|| ' '|| substr(hl.city,1,15)  			Site,
        	nvl(substr(hcsu.tax_reference,1, 20),'No TAX ID')    			tax_id,
        	rps.invoice_currency_code						Currency_Code,
        	'Receipts at Risk' 						Invoice_Number,
        	to_date(NULL)                        					Invoice_date,
        	'RECT'							Trans_Type,
       	0							Original_Frgn_amount,
       	&P_RISKVAR3						Amount_frgn_Due_Rem,
       	0 							Original_func_amount,
       	(-1)* &P_RISKVAR3				Amount_func_Due_Rem,
       	rps.due_date     						Due_Date,
        	:P_AS_OF_DATE1 -to_date(rps.due_date,'DD-MON-RRRR') Days_Past_Due, 
	&C_PRT_TOT_TAX_FUNC C_PRT_TOT_TAX_FUNC, 
	&C_PRT_TOT_TAX_FUNC_REM C_PRT_TOT_TAX_FUNC_REM, 
	&C_PRT_TOT_SITE_FUNC C_PRT_TOT_SITE_FUNC, 
	&C_PRT_TOT_SITE_FUNC_REM C_PRT_TOT_SITE_FUNC_REM, 
	&C_PRT_TOT_NET_FOREIGN C_PRT_TOT_NET_FOREIGN, 
	&C_PRT_TOT_NET_FOR_REM C_PRT_TOT_NET_FOR_REM, 
	&C_PRT_TOT_R_FOREIGN C_PRT_TOT_R_FOREIGN, 
	&C_PRT_TOT_O_FOREIGN C_PRT_TOT_O_FOREIGN, 
	&C_PRT_TOT_CUR_FUNC C_PRT_TOT_CUR_FUNC, 
	&C_PRT_TOT_CUR_FUNC_REM C_PRT_TOT_CUR_FUNC_REM, 
	JG_JGZZRDLR_XMLP_PKG.CF_INVOICE_DATEFORMULA(to_date(NULL)) CF_INVOICE_DATE, 
	&C_PRT_ORIG_FRGN_AMOUNT C_PRT_ORIG_FRGN_AMOUNT, 
	&C_PRT_FRGN_AMT_DUE_REM C_PRT_FRGN_AMT_DUE_REM, 
	&C_PRT_ORIG_FUNC_AMOUNT C_PRT_ORIG_FUNC_AMOUNT, 
	&C_PRT_FUNC_AMT_DUE_REM C_PRT_FUNC_AMT_DUE_REM, 
	JG_JGZZRDLR_XMLP_PKG.C_DUE_DATEFORMULA(rps.due_date) C_DUE_DATE, 
    JG_JGZZRDLR_XMLP_PKG.applied_amountformula(-1) APPLIED_AMOUNT,
	JG_JGZZRDLR_XMLP_PKG.org_cur_amt_remformula(&P_RISKVAR3, JG_JGZZRDLR_XMLP_PKG.applied_amountformula(-1)) ORG_CUR_AMT_REM, 
	JG_JGZZRDLR_XMLP_PKG.fun_cur_amt_remformula(-1, JG_JGZZRDLR_XMLP_PKG.org_cur_amt_remformula(&P_RISKVAR3, JG_JGZZRDLR_XMLP_PKG.applied_amountformula(-1)), nvl(rps.exchange_rate,1)* &P_RISKVAR3) FUN_CUR_AMT_REM, 
	JG_JGZZRDLR_XMLP_PKG.org_cur_amountformula(JG_JGZZRDLR_XMLP_PKG.org_cur_amt_remformula(&P_RISKVAR3, JG_JGZZRDLR_XMLP_PKG.applied_amountformula(-1)), 0) ORG_CUR_AMOUNT, 
	JG_JGZZRDLR_XMLP_PKG.fun_cur_amountformula(JG_JGZZRDLR_XMLP_PKG.org_cur_amt_remformula(&P_RISKVAR3, JG_JGZZRDLR_XMLP_PKG.applied_amountformula(-1)), 0) FUN_CUR_AMOUNT
from
            	ar_payment_schedules rps,
            	ar_cash_receipts cr,
        	ar_cash_receipt_history crh,
            	ar_receivable_applications ara,
            	hz_parties   hp,    hz_cust_accounts   hca,          
            	hz_locations hl,
	hz_party_sites hps,
	hz_cust_acct_sites hcas,			
	hz_cust_site_uses hcsu		        	
where      	ara.payment_schedule_id = rps.payment_schedule_id
and 	rps.invoice_currency_code like decode(:P_CURRENCY, 'All','%','','%' ,:P_CURRENCY)
and         	cr.cash_receipt_id = ara.cash_receipt_id
and         	cr.cash_receipt_id = rps.cash_receipt_id
and         	cr.cash_receipt_id = crh.cash_receipt_id
and 	hp.party_id = hca.party_id
and         	hca.cust_account_id = rps.customer_id
and         	hca.cust_account_id = hcas.cust_account_id	
and         	hcas.cust_acct_site_id=hcsu.cust_acct_site_id	
AND    hcas.party_site_id = hps.party_site_id
and	cr.customer_site_use_id = hcsu.site_use_id	
and 	hp.party_id=hps.party_id
and 	hps.location_id=hl.location_id
and         	ara.status = 'UNAPP'
and         	cr.reversal_date is null
and	crh.current_record_flag||'' = 'Y'
and         	crh.status not in (decode (crh.factor_flag, 'Y', 'RISK_ELIMINATED','N','CLEARED'),'REVERSED')
and         	rps.status = 'OP'
and 	TRUNC(rps.gl_date) <=TRUNC( :P_DATE_TO)
and	((TRUNC(crh.trx_date) <= TRUNC(:P_DATE_TO) and  crh.status ='CLEARED') or crh .status <> 'CLEARED')
and 	 &P_RISKVAR3	 <> 0 
 and hca.cust_account_id=:Cusid1
ORDER BY 3,6,5,7,10,11