IGI Standing Charges: Transaction - draft

Description
Categories: BI Publisher, Financials
Application: Public Sector Financials International
Source: Standing Charges: Transaction Report (XML) - Not Supported: Reserved For Future Use
Short Name: IGIRRTRN_XML
DB package: IGI_IGIRRTRN_XMLP_PKG
SELECT	gsob.NAME		"sob_name"
,	sc.charge_reference 		"Charge Reference"
,	hp.party_name			"Customer"
,	bs.name			"Batch Source"
,	ty.name			"Trans Type"
,	sc.status			"Status"
,	sc.suppress_inv_print	"Stop"
,	sr.name			"Department"
,	sc.description		"Description"
,	tx.trx_number		"Invoice number"
,	tx.trx_date		"Invoice Date"
,	max(gd.gl_Date)           	"GL Date"
,	sum(nvl(gd.amount,0)) "Amount"
,	tx.comments		"inv_desc"
FROM	
                   RA_CUST_TRX_LINE_GL_DIST            gd
,	RA_CUSTOMER_TRX		tx
,	HZ_PARTIES 			hp	
,	HZ_CUST_ACCOUNTS		ca	
,	RA_CUST_TRX_TYPES		ty
,	RA_SALESREPS			sr
,	IGI_RPI_STANDING_CHARGES	sc
,	RA_BATCH_SOURCES		bs
,	GL_SETS_OF_BOOKS		gsob
WHERE	hp.PARTY_ID = ca.PARTY_ID	
AND 	gsob.SET_OF_BOOKS_ID = :P_SOB_ID
AND	bs.BATCH_SOURCE_ID = 
	NVL(:P_BATCH_SOURCE_ID, bs.BATCH_SOURCE_ID)
AND	sc.SET_OF_BOOKS_ID = gsob.SET_OF_BOOKS_ID
AND	sc.BATCH_SOURCE_ID = bs.BATCH_SOURCE_ID
AND	sc.STANDING_CHARGE_ID = 
	NVL(:P_STANDING_CHARGE_ID, sc.STANDING_CHARGE_ID)
AND	sc.SALESREP_ID = sr.SALESREP_ID
AND	ty.CUST_TRX_TYPE_ID = 
	NVL(:P_CUST_TRX_ID, ty.CUST_TRX_TYPE_ID)
AND	sc.BILL_TO_CUSTOMER_ID = ca.CUST_ACCOUNT_ID	
AND	sc.CUST_TRX_TYPE_ID = ty.CUST_TRX_TYPE_ID
AND	tx.BILL_TO_CUSTOMER_ID = ca.CUST_ACCOUNT_ID
AND	tx.INTERFACE_HEADER_ATTRIBUTE1 = 
	to_char(sc.STANDING_CHARGE_ID)                 
AND	gd.CUSTOMER_TRX_ID = tx.CUSTOMER_TRX_ID
AND          gd.account_class = 'REC'
AND          gd.latest_Rec_flag = 'Y'
GROUP BY 
	gsob.NAME 	,	sc.charge_reference
,	hp.party_name 	, 	bs.name
,	ty.name 		,	sc.status
,	sc.suppress_inv_print,	sr.name
,	sc.description 	,	tx.trx_number
,	tx.trx_date 	,	tx.comments
ORDER BY
	sc.suppress_inv_print,	bs.name
,	hp.party_name 	,	sc.charge_reference
Parameter NameSQL textValidation
Ledger Id
 
LOV Oracle
Standing Charge Reference
 
LOV Oracle
Customer Transaction Type
 
LOV Oracle
Batch Source Name
 
LOV Oracle