JA Third Party Detail Trial Balance with Contra Accounts

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Application: Asia/Pacific Localizations
Source: Third Party Detail Trial Balance with Contra Accounts Report
Short Name: JAJPTPBC
DB package: JA_JP_TP_BAL_CTRL_RPT_PKG
Run JA Third Party Detail Trial Balance with Contra Accounts and other Oracle EBS reports with Blitz Report™ on our demo environment
           	SELECT 
			     aeh.accounting_date              GL_DATE
			    ,ENT.TRANSACTION_NUMBER           TRANSACTION_NUMBER
			    ,fns.name                         DOCUMENT_SEQUENCE_NAME
			    ,aeh.doc_sequence_value           DOCUMENT_SEQUENCE_NUMBER
				,xlk.meaning                      ACCOUNTING_CLASS_NAME
				,ael.description                  LINE_DESCRIPTION            
				,gcck.concatenated_segments       ACCOUNTING_CODE_COMBINATION
				--,xla_report_utility_pkg.get_ccid_desc(gll.chart_of_accounts_id,ael.code_combination_id)  CODE_COMBINATION_DESCRIPTION
				,aeh.entity_id				ENTITY_ID
				,aeh.event_id				EVENT_ID
				,aeh.ae_header_id			HEADER_ID
				,aeh.event_type_code			    EVENT_TYPE_CODE
				,aeh.gl_transfer_status_code		GL_TRANSFER_STATUS
				,DECODE(gl_transfer_status_code,'Y',&G_POSTING_FLAG,'') POSTING_STATUS
				,aeh.accounting_entry_status_code	ACCOUNTING_ENTRY_STATUS
				,aeh.balance_type_code		BALANCE_TYPE_CODE
				,ael.ae_line_num			LINE_NUMBER
				,ael.code_combination_id	CL_CODE_COMBINATION_ID
				,ael.accounted_dr			ACCOUNTED_DR
				,ael.accounted_cr			ACCOUNTED_CR
				,NVL(ael.accounted_dr,0)	M_ACCOUNTED_DR -- for calculating running balace these columns being used.
				,NVL(ael.accounted_cr,0)	M_ACCOUNTED_CR
				,ael.entered_dr		 	    ENTERED_DR
				,ael.entered_cr			    ENTERED_CR
				,ael.currency_code      	ENTERED_CURRENCY
				,ael.control_balance_flag	CONTROL_BALANCE_FLAG
				,ael.displayed_line_number	DISPLAYED_LINE_NUMBER
				,ent.entity_code            ENTITY_CODE
				,ent.source_id_int_1	    SOUCE_ID_INT_1
				,ent.source_id_int_2        SOUCE_ID_INT_2
				,ent.source_id_int_3        SOUCE_ID_INT_3
				,ent.source_id_int_4        SOUCE_ID_INT_4
				,&P_ALIAS_BALANCING_SEGMENT     BALANCING_SEGMENT
		        ,&P_ALIAS_ACCOUNT_SEGMENT       NATURAL_ACCOUNT_SEGMENT        
				,fvl.description  NATURAL_ACCOUNT_SEGMENT_DESC
				,CASE aeh.application_id
					WHEN 200 THEN
				    CASE 
				    WHEN ent.entity_code = 'AP_INVOICES' THEN
				      (select LU.displayed_field 
				        FROM AP_LOOKUP_CODES  LU
				            ,AP_INVOICES_ALL    I
				        WHERE	LU.lookup_type = 'INVOICE TYPE'
				        AND	LU.lookup_code = I.invoice_type_lookup_code
				        AND      I.invoice_id   = ent.source_id_int_1)
				    WHEN ent.entity_code = 'AP_PAYMENTS' THEN
				       (SELECT LU.displayed_field 
				          FROM AP_LOOKUP_CODES   LU
				              ,AP_CHECKS_ALL      C
			             WHERE	LU.lookup_type = 'PAYMENT METHOD'
				          AND	LU.lookup_code = C.payment_method_lookup_code
						  AND   C.check_id     = ent.source_id_int_1)
				    ELSE
				      (SELECT name
						from xla_event_types_vl
				        WHERE application_id = 200
				        AND entity_code = 'MANUAL')  
					END     
					WHEN 222 THEN
					 CASE
					 WHEN ent.entity_code = 'ADJUSTMENTS' THEN
						  (select ttlkp.meaning 
						   from ar_adjustments_all  adj
						       ,fnd_lookups ttlkp
						    WHERE ttlkp.lookup_type = 'JGZZ_SLR_TRANS_TYPE'
							   AND ((adj.adjustment_type= 'E' and
								ttlkp.lookup_code = 'ENDORSEMENT') or
						            (adj.adjustment_type <> 'E' and
					             ttlkp.lookup_code = 'ADJUSTMENT')) 
						      AND adj.adjustment_id = ent.source_id_int_1)
					 WHEN ent.entity_code = 'TRANSACTIONS' THEN
						  (select ttlkp.meaning 
						   from  ra_customer_trx_all ct
						        ,ra_cust_trx_types_all   ctt
							    ,fnd_lookups ttlkp
						   WHERE ttlkp.lookup_code = ctt.type
						     AND  ttlkp.lookup_type = 'JGZZ_SLR_TRANS_TYPE'
						     AND  ctt.cust_trx_type_id = ct.cust_trx_type_id
					         AND  ct.org_id = ctt.org_id
						     AND  ct.customer_trx_id  = ent.source_id_int_1)
					 WHEN ent.entity_code = 'RECEIPTS' THEN
						 (select ttlkp.meaning 
							from  ar_cash_receipts_all  cr
								   ,fnd_lookups ttlkp
							WHERE  ttlkp.lookup_code = decode(cr.type, 'MISC', 'MISCELLANEOUS', 'TRADE_RECEIPTS') 
							 AND  ttlkp.lookup_type = 'JGZZ_SLR_TRANS_TYPE'
							 AND  cr.cash_receipt_id = ent.source_id_int_1)
					 WHEN ent.entity_code = 'BILLS_RECEIVABLE' THEN
						  (select ttlkp.meaning 
						   from  ra_customer_trx_all ct
					        ,ra_cust_trx_types_all   ctt
					        ,fnd_lookups ttlkp
						   WHERE ttlkp.lookup_code = ctt.type
						     AND  ttlkp.lookup_type = 'JGZZ_SLR_TRANS_TYPE'
							 AND  ctt.cust_trx_type_id = ct.cust_trx_type_id
							 AND  ct.customer_trx_id  = ent.source_id_int_1)  
					 ELSE
					  (select name
					   from xla_event_types_vl
					   WHERE application_id = 200
					     AND entity_code = 'MANUAL')
					END 
				END transaction_type
		FROM
		 xla_transaction_entities_upg ent
		,xla_ae_headers          aeh
		,xla_ae_lines            ael            
		,fnd_document_sequences  fns
		,xla_lookups             xlk
		,gl_ledgers                       gll
		,gl_code_combinations_kfv         gcck
		,fnd_id_flex_segments fsg
		,fnd_flex_values_vl fvl
		,fnd_segment_attribute_values     fsav2
	   WHERE   ael.application_id         = aeh.application_id
		          AND   ael.ae_header_id           = aeh.ae_header_id
		          AND   xlk.lookup_type           = 'XLA_ACCOUNTING_CLASS'
		          AND   xlk.lookup_code           = ael.accounting_class_code
		          AND   ent.application_id         = aeh.application_id
		          AND   ent.entity_id              = aeh.entity_id
		          AND   fns.application_id(+)      = aeh.application_id
		          AND   fns.doc_sequence_id(+)         = AEH.DOC_SEQUENCE_ID
		          AND   gll.ledger_id           = aeh.ledger_id
		          AND   gcck.code_combination_id = ael.code_combination_id 
  				  AND   gcck.chart_of_accounts_id            = gll.chart_of_accounts_id
				  AND   fsav2.application_id         = 101
		          AND   fsav2.id_flex_code           = 'GL#'
		          AND   fsav2.id_flex_num            = gll.chart_of_accounts_id
		          AND   fsav2.attribute_value        = 'Y'
		          AND   fsav2.segment_attribute_type = 'GL_ACCOUNT'
	              AND   fsg.application_id         = 101
		          AND   fsg.id_flex_code           = 'GL#'
		          AND   fsg.id_flex_num            = gll.chart_of_accounts_id
			      AND   fvl.flex_value  = &P_ALIAS_ACCOUNT_SEGMENT		
				  AND   fsg.application_column_name  = fsav2.application_column_name
			      AND   fsg.flex_value_set_id  = fvl.flex_value_set_id
				  AND   AEL.LEDGER_ID = AEH.LEDGER_ID
	              AND   AEH.LEDGER_ID                  = :LEDGER_ID
	              AND   AEH.APPLICATION_ID             = :CCID_APPLICATION_ID
	              AND   AEL.PARTY_ID                   = :PARTY_ID
		          AND   NVL(AEL.PARTY_SITE_ID,-999)    = :PARTY_SITE_ID  
		          AND   AEL.PARTY_TYPE_CODE            = :PARTY_TYPE_CODE	          
				  AND   AEH.ACCOUNTING_DATE BETWEEN :P_GL_DATE_FROM AND :P_GL_DATE_TO
		          AND   ael.code_combination_id       <> :code_combination_id          
				  AND	(aeh.gl_transfer_status_code = :P_GL_TRANSFER_STATUS OR :P_GL_TRANSFER_STATUS IS NULL OR :P_GL_TRANSFER_STATUS='A')
		          AND   EXISTS (         
		                          SELECT 1
		                          FROM  XLA_AE_LINES AEL1               
		                          WHERE AEL1.AE_HEADER_ID = AEH.AE_HEADER_ID          
		                          AND   AEL1.APPLICATION_ID = AEH.APPLICATION_ID
		                          --AND   AEL1.CONTROL_BALANCE_FLAG   = 'Y'
		                          AND   AEL1.CODE_COMBINATION_ID = :CODE_COMBINATION_ID)
		          AND ( AEH.DOC_SEQUENCE_ID = :P_DOCUMENT_SEQUENCE_NAME OR  :P_DOCUMENT_SEQUENCE_NAME IS NULL)
		          AND  NVL(AEH.DOC_SEQUENCE_VALUE,-1) BETWEEN NVL(:P_DOC_SEQUENCE_NUMBER_FROM,NVL(AEH.DOC_SEQUENCE_VALUE,-1)) AND NVL(:P_DOC_SEQUENCE_NUMBER_TO,NVL(AEH.DOC_SEQUENCE_VALUE,-1))
		          AND  (  :P_DIST_DR_CR IS NULL 
	                   OR            
		                (( :P_DIST_DR_CR = 'DEBIT' AND AEL.ACCOUNTED_DR BETWEEN 
		                                 NVL(:P_DIST_AMT_FROM,AEL.ACCOUNTED_DR) AND 
		                                 NVL(:P_DIST_AMT_TO,AEL.ACCOUNTED_DR))
		                OR 
		                ( :P_DIST_DR_CR = 'CREDIT' AND AEL.ACCOUNTED_CR BETWEEN 
		                               NVL(:P_DIST_AMT_FROM,AEL.ACCOUNTED_CR) AND 	               
	                                  NVL(:P_DIST_AMT_TO,AEL.ACCOUNTED_CR))
	                  )
		               )
		          &P_DIST_ACCOUNT_FF_RANGE_FILTER
				  &P_OTHER_FILTER
				  ORDER BY   aeh.accounting_date
  							,aeh.doc_sequence_value
				   		    ,ent.transaction_number
Parameter Name SQL text Validation
Ledger 
 
 LOV Oracle
Legal Entity 
 
 LOV Oracle
Accounting Flexfield From 
 
 Char
Accounting Flexfield To 
 
 Char
Period From 
 
 LOV Oracle
Period To 
 
 LOV Oracle
GL Date From 
 
 Date
GL Date To 
 
 Date
Journal Entry Source 
 
 LOV Oracle
Third Party Type 
 
 LOV Oracle
Third Party Name From 
 
 LOV Oracle
Third Party Name To 
 
 LOV Oracle
Third Party Number From 
 
 LOV Oracle
Third Party Number To 
 
 LOV Oracle
Third Party Site Name From 
 
 LOV Oracle
Third Party Site Name To 
 
 LOV Oracle
Distribution Accounting Flexfield From 
 
 Char
Distribution Accounting Flexfield To 
 
 Char
Document Sequence Name 
 
 LOV Oracle
Document Sequence From 
 
 Number
Document Sequence To 
 
 Number
Distribution Amount Debit or Credit 
 
 LOV Oracle
Distribution Amount From 
 
 Number
Distribution Amount To 
 
 Number
Include Draft Activity 
 
 LOV Oracle
GL Transfer Status 
 
 LOV Oracle
Download
Related Blitz Reports
GL Account Analysis GL Account Analysis (Distributions) GL Account Analysis 11g GL Account Analysis (Distributions) 11g JA India - Open Transactions Report for Prior-GST (XML Publisher) CE General Ledger Reconciliation JA Cash Flow Statement Program - Cash Flow Detail XLA Entity ID Mappings XLA Distribution Links Summary
Blitz Report™

Blitz Report™ provides multiple benefits: