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