AR Invoices Posted to Suspense

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Invoices Posted to Suspense
Application: Receivables
Source: Invoices Posted to Suspense (XML)
Short Name: RAXINPS_XML
DB package: AR_RAXINPS_XMLP_PKG
Run AR Invoices Posted to Suspense and other Oracle EBS reports with Blitz Report™ on our demo environment
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
Operating Unit 
 
 LOV
Order By 
 
 LOV Oracle
GL Date Low 
 
 Date
GL Date High 
 
 Date
Transaction Date Low 
 
 Date
Transaction Date High 
 
 Date
Invoice Type Low 
 
 LOV Oracle
Invoice Type High 
 
 LOV Oracle
Currency Code Low 
 
 LOV Oracle
Currency Code High 
 
 LOV Oracle
Download
Related Blitz Reports
AR Transaction Detail AR Discount Projection AR Open Items Revaluation AR Invoice Exception AR Inter Company Invoices AR Sales Journal By Customer GL Account Analysis (Distributions) AR Post Adoption Adjustments Transactions AR Open Balances Revaluation
Blitz Report™

Blitz Report™ provides multiple benefits: