AR Invoices Posted to Suspense

Description
Categories: BI Publisher, Financials
Application: Receivables
Source: Invoices Posted to Suspense (XML)
Short Name: RAXINPS_XML
DB package: AR_RAXINPS_XMLP_PKG
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
Parameter Name SQL text Validation
Ledger Id
 
LOV Oracle
END
 
Chart of Accounts Id
 
LOV Oracle
Currency Code High
 
LOV Oracle
Currency Code Low
 
LOV Oracle
Invoice Type High
 
LOV Oracle
Invoice Type Low
 
LOV Oracle
Transaction Date High
 
Date
Transaction Date Low
 
Date
GL Date High
 
Date
GL Date Low
 
Date
Order By
 
LOV Oracle