AR Invoices Posted to Suspense

SELECT
        null                              Company,
	trx.invoice_currency_code			Currency_A,
        decode(type.post_to_gl, 'Y',&p_yes ,&p_no )	Postable,
	look.meaning      				Class,
	trx.trx_number 					Invoice_Number,
        type.name                           		Type,
       	substrb(party.party_name,1,50)			Customer_Name,
       	cust.account_number				Customer_Number,
       	trx.trx_date					Invoice_Date,
       	suspdist.gl_date				GL_Date,
	sum(suspdist.amount)				Foreign_Currency,
	sum(suspdist.acctd_amount) Functional_Currency_A,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_company', 'SQLGL', 'GL#', gl.CHART_OF_ACCOUNTS_ID, NULL, gl.CODE_COMBINATION_ID, '1', 'Y', 'VALUE') D_Company,
	AR_RAXINPS_XMLP_PKG.c_company_labelformula(:D_Company) C_COMPANY_LABEL,
	AR_RAXINPS_XMLP_PKG.c_data_not_foundformula(gl.segment1) C_DATA_NOT_FOUND,
	AR_RAXINPS_XMLP_PKG.c_post_labelformula(decode ( type.post_to_gl , 'Y' , &p_yes , &p_no )) C_POST_LABEL,
	AR_RAXINPS_XMLP_PKG.c_currency_labelformula(trx.invoice_currency_code) C_CURRENCY_LABEL,
	AR_RAXINPS_XMLP_PKG.c_class_labelformula(look.meaning) C_CLASS_LABEL
FROM 	ra_customer_trx			trx,
	ra_customer_trx_lines		lines,
	ra_cust_trx_types		type,
	gl_code_combinations		gl,
	hz_cust_accounts			cust,
	hz_parties				party,
	ar_lookups			look,
                ar_xla_ctlgd_lines_v                     suspdist,
                ar_xla_ctlgd_lines_v                     recdist
WHERE	lines.customer_trx_id = trx.customer_trx_id
AND	suspdist.customer_trx_line_id = lines.customer_trx_line_id
AND	type.cust_trx_type_id = trx.cust_trx_type_id
AND     recdist.customer_trx_id = trx.customer_trx_id
AND	gl.code_combination_id = recdist.code_combination_id
AND	cust.cust_account_id = trx.bill_to_customer_id
AND	cust.party_id = party.party_id
AND	look.lookup_type = 'INV/CM'
AND	look.lookup_code = type.type
AND     trx.complete_flag = 'Y'
AND	suspdist.account_class = 'SUSPENSE'
AND     recdist.account_class = 'REC'
AND     recdist.latest_rec_flag = 'Y'
AND	type.type in ('INV','DEP','GUAR','CM')
&lp_gl_start_date
&lp_gl_end_date
&lp_trx_start_date
&lp_trx_end_date
&lp_type_low
&lp_type_high
&lp_start_currency_code
&lp_end_currency_code
and suspdist.gl_date  is NOT NULL
and trx.trx_date  is NOT NULL
GROUP BY look.meaning,
         &ACCT_FLEX_BAL_SEG,
         type.post_to_gl,
	 trx.invoice_currency_code,
	 trx.trx_number,
         type.name,
       	 party.party_name,
       	 cust.account_number,
       	 trx.trx_date,
       	 suspdist.gl_date
--Added during DT Fix
		,fnd_flex_xml_publisher_apis.process_kff_combination_1('d_company', 'SQLGL', 'GL#', gl.CHART_OF_ACCOUNTS_ID, NULL, gl.CODE_COMBINATION_ID, '1', 'Y', 'VALUE')
		,gl.segment1
		,type.post_to_gl
--End of DT Fix
ORDER BY
	&ACCT_FLEX_BAL_SEG ,
	type.post_to_gl	desc,
	trx.invoice_currency_code,
	look.meaning,
	type.name,
	decode(upper(:p_order_by), 'CUSTOMER', party.party_name,
				 'INVOICE NUMBER', trx.trx_number,
				 trx.trx_number),
	trx.trx_number,
	suspdist.gl_date