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(