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
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
Parameter NameSQL textValidation
Include Receipts at Risk
 
LOV Oracle
Currency Code
 
LOV Oracle
Customer To
 
LOV Oracle
Customer From
 
LOV Oracle
Dunning Letter
 
LOV Oracle
As of Date
 
Date
Download
Related Blitz Reports
JG Receipt Acknowledgment Letter - draft AR Transactions and Payments AR Receipt Analysis - Days Late JL Colombian Receivables Cash Receipt - draft AR iReceivables Customers Statement Data Definition AR Format Automatic Receipts AR Transactions Awaiting Consolidation JA India - Debtors Ledger - draft AR Open Balances Revaluation
Blitz Report In Action