JA Third Party Detail Trial Balance with Contra Accounts

           	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