FA Asset Additions - draft

➜ Back to Library
Description
Categories: BI Publisher
Application: Assets
Source: Asset Additions Report (XML) - Not Supported: Reserved For Future Use
Short Name: FAS420_XML
DB package: FA_FAS420_XMLP_PKG
Run FA Asset Additions - draft and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT 	&ACCT_FLEX_BAL_SEG              				COMP_CODE,
	FALU.MEANING					ASSET_TYPE, 
 	decode(AH.ASSET_TYPE, 'CIP', CB.CIP_COST_ACCT,
	       CB.ASSET_COST_ACCT)				GL_ACCOUNT,
       	&ACCT_FLEX_COST_SEG                     			COST_CENTER,
        	decode(AH.ASSET_TYPE, 'CIP', NULL, 
	       CB.DEPRN_RESERVE_ACCT)			RES_ACCOUNT,
        	AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION		ASSET_NUMBER,
	BKS.DATE_PLACED_IN_SERVICE			START_DATE,
	BKS.DEPRN_METHOD_CODE				METHOD,
       	BKS.LIFE_IN_MONTHS   				LIFE,
       	BKS.PRODUCTION_CAPACITY 				PROD,
       	BKS.ADJUSTED_RATE 				ADJ_RATE,
       	0						BONUS_RATE,
	sum(DECODE(ADJ.SOURCE_TYPE_CODE, 'CIP ADJUSTMENT', 
	  decode(th_captest.transaction_type_code,'', 0,
	  decode(adj.debit_credit_flag,'DR',1,-1) * nvl(adj.adjustment_amount,0)),
	  decode(adj.debit_credit_flag,'DR',1,-1) * nvl(adj.adjustment_amount,0) )) COST,
	SUM(0)				YTD_DEPRN,
	SUM(0)				DEPRN_RESERVE,
	thcipadd.TRANSACTION_HEADER_ID				THID, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_comp_code', 'SQLGL', 'GL#', DHCC.CHART_OF_ACCOUNTS_ID, NULL, DHCC.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') d_comp_code, 
	&D_GL_ACCOUNT d_gl_account, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_center', 'SQLGL', 'GL#', DHCC.CHART_OF_ACCOUNTS_ID, NULL, DHCC.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') d_cost_center, 
	FA_FAS420_XMLP_PKG.d_lifeformula(BKS.LIFE_IN_MONTHS, BKS.ADJUSTED_RATE, 0, BKS.PRODUCTION_CAPACITY) D_LIFE
FROM	FA_ADDITIONS           		AD,
	FA_LOOKUPS		FALU,
	FA_CATEGORY_BOOKS	CB,
	GL_CODE_COMBINATIONS	DHCC,
	fa_transaction_headers 	thcipadd,
	FA_ASSET_HISTORY		AH,
		&LP_FA_BOOKS		BKS, 	fa_distribution_history 	dh2,
    		&LP_FA_DEPRN_PERIODS	DP,   		&LP_FA_ADJUSTMENTS	ADJ, 	FA_TRANSACTION_HEADERS	TH_CAPTEST
WHERE	
DP.BOOK_TYPE_CODE = :P_BOOK
and	dp.period_counter between
 :PERIOD1_PC and :PERIOD2_PC
and     dp.period_counter = adj.period_counter_created
AND 	BKS.BOOK_TYPE_CODE = dp.book_type_code
AND	BKS.ASSET_ID = ad.asset_id
AND	BKS.DATE_INEFFECTIVE IS NULL
AND	AD.ASSET_TYPE = 'CIP'  AND	AH.ASSET_ID			=  ad.ASSET_ID			
and 	AH.TRANSACTION_HEADER_ID_IN = (SELECT MAX(TRANSACTION_HEADER_ID_IN)
				FROM FA_ASSET_HISTORY
				WHERE ASSET_ID = AD.ASSET_ID
				AND     ASSET_TYPE = 'CIP'
				AND    DATE_EFFECTIVE BETWEEN 
				:PERIOD1_POD AND :PERIOD2_PCD)
AND 	CB.CATEGORY_ID			=  AH.CATEGORY_ID		AND
	CB.BOOK_TYPE_CODE		=  bks.book_type_code 
AND	AH.ASSET_TYPE			=  FALU.LOOKUP_CODE	AND
	FALU.LOOKUP_TYPE		= 'ASSET TYPE'
AND	TH_CAPTEST.BOOK_TYPE_CODE(+) = BKS.BOOK_TYPE_CODE
AND 	TH_CAPTEST.ASSET_ID(+) = BKS.ASSET_ID
AND	TH_CAPTEST.TRANSACTION_TYPE_CODE(+)  =  'ADDITION' 
and 	th_captest.date_effective(+) between :Period1_POD and :Period2_PCD
AND 	ADJ.BOOK_TYPE_CODE = bks.book_type_code
AND	ADJ.ASSET_ID  = bks.ASSET_ID
AND	ADJ.ADJUSTMENT_TYPE  LIKE '%COST'
AND	ADJ.PERIOD_COUNTER_CREATED  BETWEEN 
:PERIOD1_PC AND :PERIOD2_PC
AND	ADJ.SOURCE_TYPE_CODE  like 'CIP AD%'
AND     	thcipadd.book_type_code = bks.book_type_code
and 	thcipadd.asset_id	= bks.asset_id
anD	thcipadd.transaction_header_id in (
			select t.transaction_header_id
			from fa_transaction_headers t
			where t.book_type_code = bks.book_type_code
			and    t.asset_id     = bks.asset_id
			and   t.transaction_type_code = 'CIP ADDITION'
			and  t.date_effective between :period1_pod and :period2_pcd)
and	adj.distribution_id = dh2.distribution_id
and	dh2.code_combination_id = dhcc.code_combination_id
GROUP BY
	&ACCT_FLEX_BAL_SEG,
	FALU.MEANING, 
 	decode(AH.ASSET_TYPE, 'CIP', CB.CIP_COST_ACCT,
	       CB.ASSET_COST_ACCT),
       	&ACCT_FLEX_COST_SEG ,
        	decode(AH.ASSET_TYPE, 'CIP', NULL, 
	       CB.DEPRN_RESERVE_ACCT),
        	AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION,
	BKS.DATE_PLACED_IN_SERVICE,
	BKS.DEPRN_METHOD_CODE,
        	BKS.LIFE_IN_MONTHS,
        	BKS.PRODUCTION_CAPACITY,
        	BKS.ADJUSTED_RATE,
	THcipadd.TRANSACTION_HEADER_ID,
	--ADDED FOR DT FIXES
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_comp_code', 'SQLGL', 'GL#', DHCC.CHART_OF_ACCOUNTS_ID, NULL, DHCC.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') , 
	&D_GL_ACCOUNT , 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_center', 'SQLGL', 'GL#', DHCC.CHART_OF_ACCOUNTS_ID, NULL, DHCC.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') 
UNION ALL
SELECT 	&ACCT_FLEX_BAL_SEG              			COMP_CODE,
	FALU.MEANING					ASSET_TYPE, 
 	decode(AH.ASSET_TYPE, 'CIP', CB.CIP_COST_ACCT,
	       CB.ASSET_COST_ACCT)				GL_ACCOUNT,
       	&ACCT_FLEX_COST_SEG                     			COST_CENTER,
        decode(AH.ASSET_TYPE, 'CIP', NULL, 
	       CB.DEPRN_RESERVE_ACCT)			RES_ACCOUNT,
        	AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION		ASSET_NUMBER,
	BKS.DATE_PLACED_IN_SERVICE			START_DATE,
	BKS.DEPRN_METHOD_CODE				METHOD,
        	BKS.LIFE_IN_MONTHS   				LIFE,
        	BKS.PRODUCTION_CAPACITY 			PROD,
        	BKS.ADJUSTED_RATE 				ADJ_RATE,
        	NVL(DS.BONUS_RATE,0)				BONUS_RATE,
                 bks.cost COST,
	SUM(DD.YTD_DEPRN)				YTD_DEPRN,
	SUM(DD.DEPRN_RESERVE)				DEPRN_RESERVE,
	TH.TRANSACTION_HEADER_ID				THID, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_comp_code', 'SQLGL', 'GL#', DHCC.CHART_OF_ACCOUNTS_ID, NULL, DHCC.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') d_comp_code, 
	&D_GL_ACCOUNT d_gl_account, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_center', 'SQLGL', 'GL#', DHCC.CHART_OF_ACCOUNTS_ID, NULL, DHCC.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') d_cost_center, 
	FA_FAS420_XMLP_PKG.d_lifeformula(BKS.LIFE_IN_MONTHS, BKS.ADJUSTED_RATE, 0, BKS.PRODUCTION_CAPACITY) D_LIFE
FROM		&LP_FA_DEPRN_SUMMARY	DS, 	FA_ADDITIONS           	AD,
	GL_CODE_COMBINATIONS	DHCC,
	FA_DISTRIBUTION_HISTORY	DH,
	FA_LOOKUPS		FALU,
	FA_CATEGORY_BOOKS	CB,
	FA_TRANSACTION_HEADERS	TH,
	FA_ASSET_HISTORY		AH,
		&LP_FA_BOOKS		BKS,     		&LP_FA_DEPRN_PERIODS	DP,   		&LP_FA_DEPRN_DETAIL	DD    WHERE	DS.BOOK_TYPE_CODE = :P_BOOK
AND	DS.ASSET_ID 			=  DD.ASSET_ID			
AND	DS.PERIOD_COUNTER =
		 (SELECT nvl(MIN(DS_SUB.PERIOD_COUNTER),dd.period_counter)
			     FROM FA_DEPRN_SUMMARY DS_SUB
			     WHERE DS_SUB.BOOK_TYPE_CODE = DD.BOOK_TYPE_CODE
			    AND DS_SUB.ASSET_ID 	= DD.ASSET_ID
			    AND DS_SUB.DEPRN_SOURCE_CODE = 'DEPRN')			
AND
	DD.DISTRIBUTION_ID		=  DH.DISTRIBUTION_ID	
AND	DHCC.CODE_COMBINATION_ID	=  DH.CODE_COMBINATION_ID
AND	AD.ASSET_ID			=  DD.ASSET_ID		
AND	DP.BOOK_TYPE_CODE = :P_BOOK
AND	DP.PERIOD_COUNTER	= dd.period_counter + 1
AND	DD.BOOK_TYPE_CODE		= DS.BOOK_TYPE_CODE
AND 	DD.DEPRN_SOURCE_CODE	=  'B'			
AND	DD.PERIOD_COUNTER        >= :PERIOD1_PC-1
and	dd.period_counter <= :PERIOD2_PC-1
AND 	BKS.BOOK_TYPE_CODE = DD.BOOK_TYPE_CODE
AND	BKS.ASSET_ID = DD.ASSET_ID
and     bks.transaction_header_id_in = th.transaction_header_id
AND	AH.ASSET_ID			=  DD.ASSET_ID			AND
	AH.DATE_EFFECTIVE <=  NVL(dp.period_close_date, SYSDATE)	AND
	NVL(AH.DATE_INEFFECTIVE,SYSDATE+1) >  
		NVL(dp.period_close_date, SYSDATE)
AND 	CB.CATEGORY_ID			=  AH.CATEGORY_ID		AND
	CB.BOOK_TYPE_CODE = :P_BOOK
AND	AH.ASSET_TYPE			=  FALU.LOOKUP_CODE	AND
	FALU.LOOKUP_TYPE		= 'ASSET TYPE'
AND	AD.ASSET_ID = TH.ASSET_ID
AND	TH.BOOK_TYPE_CODE = :P_BOOK
AND	((AH.ASSET_TYPE <> 'CIP' AND TH.TRANSACTION_TYPE_CODE = 'ADDITION'))
GROUP BY
	&ACCT_FLEX_BAL_SEG,
	FALU.MEANING, 
 	decode(AH.ASSET_TYPE, 'CIP', CB.CIP_COST_ACCT,
	       CB.ASSET_COST_ACCT),
       	&ACCT_FLEX_COST_SEG ,
        	decode(AH.ASSET_TYPE, 'CIP', NULL, 
	       CB.DEPRN_RESERVE_ACCT),
        	AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION,
	BKS.DATE_PLACED_IN_SERVICE,
	BKS.DEPRN_METHOD_CODE,
        	BKS.LIFE_IN_MONTHS,
        	BKS.PRODUCTION_CAPACITY,
        	BKS.ADJUSTED_RATE,
        	NVL(DS.BONUS_RATE,0),
	TH.TRANSACTION_HEADER_ID,bks.cost,
		--ADDED FOR DT FIXES
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_comp_code', 'SQLGL', 'GL#', DHCC.CHART_OF_ACCOUNTS_ID, NULL, DHCC.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') , 
	&D_GL_ACCOUNT , 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_center', 'SQLGL', 'GL#', DHCC.CHART_OF_ACCOUNTS_ID, NULL, DHCC.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') 
UNION ALL
SELECT 	&ACCT_FLEX_BAL_SEG              			COMP_CODE,
	FALU.MEANING					ASSET_TYPE, 
 	decode(AH.ASSET_TYPE, 'CIP', CB.CIP_COST_ACCT,
	       CB.ASSET_COST_ACCT)				GL_ACCOUNT,
       	&ACCT_FLEX_COST_SEG                     			COST_CENTER,
        	decode(AH.ASSET_TYPE, 'CIP', NULL, 
	       CB.DEPRN_RESERVE_ACCT)			RES_ACCOUNT,
        	AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION		ASSET_NUMBER,
	BKS.DATE_PLACED_IN_SERVICE			START_DATE,
	BKS.DEPRN_METHOD_CODE				METHOD,
        	BKS.LIFE_IN_MONTHS   				LIFE,
        	BKS.PRODUCTION_CAPACITY 			PROD,
        	BKS.ADJUSTED_RATE 				ADJ_RATE,
        	NVL(DS.BONUS_RATE, 0)				BONUS_RATE,
	sum(decode(adjadd.debit_credit_flag,'DR',1,-1) * nvl(adjadd.adjustment_amount,0)) COST,
	0						YTD_DEPRN,
	0						DEPRN_RESERVE,
	TH.TRANSACTION_HEADER_ID			THID, 
	--ADDED FOR DT FIXES
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_comp_code', 'SQLGL', 'GL#', DHCC.CHART_OF_ACCOUNTS_ID, NULL, DHCC.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') d_comp_code, 
	&D_GL_ACCOUNT d_gl_account, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_center', 'SQLGL', 'GL#', DHCC.CHART_OF_ACCOUNTS_ID, NULL, DHCC.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') d_cost_center, 
	FA_FAS420_XMLP_PKG.d_lifeformula(BKS.LIFE_IN_MONTHS, BKS.ADJUSTED_RATE, 0, BKS.PRODUCTION_CAPACITY) D_LIFE
FROM
	FA_DISTRIBUTION_HISTORY 	DH,
	GL_CODE_COMBINATIONS	DHCC,
		&LP_FA_DEPRN_SUMMARY	DS,		FA_TRANSACTION_HEADERS	THDIS,
		&LP_FA_BOOKS		BKS,		FA_ADDITIONS           	AD,
	FA_CATEGORY_BOOKS	CB,
	FA_LOOKUPS		FALU,
	FA_ASSET_HISTORY	AH,
		&LP_FA_DEPRN_PERIODS	DP,		FA_TRANSACTION_HEADERS TH,
		&LP_FA_ADJUSTMENTS	ADJADD	WHERE	
DS.BOOK_TYPE_CODE = :P_BOOK	 AND
	DS.ASSET_ID 		=  adjadd.asset_id   and
	DS.DEPRN_SOURCE_CODE 	=  'DEPRN'		AND
	DS.PERIOD_COUNTER 		= ADJADD.PERIOD_COUNTER_CREATED
AND
	DH.DISTRIBUTION_ID		= ADJADD.DISTRIBUTION_ID
AND
	DH.CODE_COMBINATION_ID 		= DHCC.CODE_COMBINATION_ID
AND	ADJADD.BOOK_TYPE_CODE		= :P_BOOK AND
	ADJADD.ASSET_ID 			= TH.ASSET_ID			AND
	DP.PERIOD_COUNTER >= :PERIOD1_PC AND
	DP.PERIOD_COUNTER <= :PERIOD2_PC AND 
	TH.DATE_EFFECTIVE 	       >=  DP.PERIOD_OPEN_DATE				AND
	TH.DATE_EFFECTIVE	<  nvl(DP.PERIOD_CLOSE_DATE,th.date_effective+1) 		AND
	TH.BOOK_TYPE_CODE = :P_BOOK AND
	th.asset_id			=  ADJADD.ASSET_ID   			AND
	TH.TRANSACTION_TYPE_CODE 	= 'ADDITION'
AND     THDIS.TRANSACTION_TYPE_CODE	= 'CIP ADDITION' 				AND 
	THDIS.BOOK_TYPE_CODE = :P_BOOK AND 
	THDIS.ASSET_ID			= TH.ASSET_ID		
AND	BKS.TRANSACTION_HEADER_ID_IN	= TH.TRANSACTION_HEADER_ID
AND	DP.BOOK_TYPE_CODE		=  ADJADD.BOOK_TYPE_CODE
and	DP.PERIOD_COUNTER	        	=  ADJADD.PERIOD_COUNTER_CREATED
AND	AH.ASSET_ID			=  ADJADD.ASSET_ID
AND	ADJADD.SOURCE_TYPE_CODE 	= 'ADDITION'
AND 	ADJADD.ADJUSTMENT_TYPE		= 'COST'				AND
	AH.DATE_EFFECTIVE	<=  NVL(DP.PERIOD_CLOSE_DATE, ah.date_effective+1) 	AND
	NVL(AH.DATE_INEFFECTIVE,SYSDATE+1) >  
		NVL(DP.PERIOD_CLOSE_DATE, SYSDATE)
AND	AD.ASSET_ID			=  ah.ASSET_ID		
AND	CB.CATEGORY_ID			=  AH.CATEGORY_ID		AND
	CB.BOOK_TYPE_CODE = :P_BOOK 
	AND	AH.ASSET_TYPE = FALU.LOOKUP_CODE	AND
	FALU.LOOKUP_TYPE		= 'ASSET TYPE'
GROUP BY
	&ACCT_FLEX_BAL_SEG,
	FALU.MEANING, 
 	decode(AH.ASSET_TYPE, 'CIP', CB.CIP_COST_ACCT,
	       CB.ASSET_COST_ACCT),
       	&ACCT_FLEX_COST_SEG ,
        	decode(AH.ASSET_TYPE, 'CIP', NULL, 
	       CB.DEPRN_RESERVE_ACCT),
        	AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION,
	BKS.DATE_PLACED_IN_SERVICE,
	BKS.DEPRN_METHOD_CODE,
        	BKS.LIFE_IN_MONTHS,
        	BKS.PRODUCTION_CAPACITY,
        	BKS.ADJUSTED_RATE,
        	NVL(DS.BONUS_RATE, 0),
      	ADJADD.distribution_id,
        	ADJADD.ADJUSTMENT_AMOUNT,
	TH.TRANSACTION_HEADER_ID,
		--ADDED FOR DT FIXES
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_comp_code', 'SQLGL', 'GL#', DHCC.CHART_OF_ACCOUNTS_ID, NULL, DHCC.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') , 
	&D_GL_ACCOUNT , 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_center', 'SQLGL', 'GL#', DHCC.CHART_OF_ACCOUNTS_ID, NULL, DHCC.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') 
UNION ALL
SELECT 	&ACCT_FLEX_BAL_SEG              			COMP_CODE,
	FALU.MEANING				ASSET_TYPE,
        	decode(AH.ASSET_TYPE, 'CIP', CB.CIP_COST_ACCT,
               	CB.ASSET_COST_ACCT)                              		GL_ACCOUNT,  
	&ACCT_FLEX_COST_SEG                     		COST_CENTER,
                	decode(AH.ASSET_TYPE, 'CIP', NULL,
               	CB.DEPRN_RESERVE_ACCT)                  		RES_ACCOUNT,
                	AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION              ASSET_NUMBER,
        	BKS.DATE_PLACED_IN_SERVICE                      	START_DATE,
        	BKS.DEPRN_METHOD_CODE                           	METHOD,
                	BKS.LIFE_IN_MONTHS                              		LIFE,
               	BKS.PRODUCTION_CAPACITY                        	 PROD,
               	BKS.ADJUSTED_RATE                               		ADJ_RATE,
                	0                                               			BONUS_RATE,
                	sum(decode(adj.debit_credit_flag,'CR',1,-1) * nvl(adj.adjustment_amount,0))
                                  					COST,
        	0                                               			YTD_DEPRN,
        	0                                               			DEPRN_RESERVE,
        	TH.TRANSACTION_HEADER_ID                        	THID, 
			--ADDED FOR DT FIXES
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_comp_code', 'SQLGL', 'GL#', DHCC.CHART_OF_ACCOUNTS_ID, NULL, DHCC.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') d_comp_code, 
	&D_GL_ACCOUNT d_gl_account, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_center', 'SQLGL', 'GL#', DHCC.CHART_OF_ACCOUNTS_ID, NULL, DHCC.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') d_cost_center, 
	FA_FAS420_XMLP_PKG.d_lifeformula(BKS.LIFE_IN_MONTHS, BKS.ADJUSTED_RATE, 0, BKS.PRODUCTION_CAPACITY) D_LIFE
FROM
        	FA_DISTRIBUTION_HISTORY         DH,
       	GL_CODE_COMBINATIONS    DHCC,
        	FA_TRANSACTION_HEADERS  THDIS,
        		&LP_FA_BOOKS	 BKS,		        	FA_ADDITIONS            AD,
        	FA_CATEGORY_BOOKS       CB,
        	FA_LOOKUPS              FALU,
        	FA_ASSET_HISTORY        AH,
        		&LP_FA_DEPRN_PERIODS	DP,	        	FA_TRANSACTION_HEADERS TH,
        		&LP_FA_ADJUSTMENTS  ADJ		WHERE	DH.CODE_COMBINATION_ID 	= DHCC.CODE_COMBINATION_ID
AND       	DP.PERIOD_COUNTER >= :PERIOD1_PC   
AND       	DP.PERIOD_COUNTER <= :PERIOD2_PC
AND     	DP.PERIOD_CLOSE_DATE 	IS NULL
AND	DP.DEPRN_RUN		IS NULL
AND     	TH.DATE_EFFECTIVE              	>=  DP.PERIOD_OPEN_DATE         
AND       	TH.DATE_EFFECTIVE <  nvl(DP.PERIOD_CLOSE_DATE,th.date_effective+1)
AND        	TH.BOOK_TYPE_CODE               	=  :P_BOOK   
AND       	TH.ASSET_ID                     	= DH.ASSET_ID
AND     	TH.TRANSACTION_TYPE_CODE  = 'ADDITION'
AND     	THDIS.TRANSACTION_TYPE_CODE  = 'TRANSFER IN'
AND        	THDIS.BOOK_TYPE_CODE       	= :DISTRIBUTION_SOURCE_BOOK AND        	THDIS.ASSET_ID                  	= TH.ASSET_ID
AND        	THDIS.DATE_EFFECTIVE            	< DP.PERIOD_OPEN_DATE
AND     	adj.book_type_code = :P_BOOK 
AND	adj.asset_id 		= dh.asset_id
AND     	adj.source_type_code 		= 'ADDITION'
AND    	adj.adjustment_type 		= 'CIP COST'
AND     	adj.period_counter_created 	= dp.period_counter
AND     	adj.distribution_id 		= dh.distribution_id
AND     	BKS.TRANSACTION_HEADER_ID_IN = TH.TRANSACTION_HEADER_ID
AND     	DP.BOOK_TYPE_CODE            	= TH.BOOK_TYPE_CODE
AND     	AH.ASSET_ID                     	= TH.ASSET_ID                 
AND	AH.DATE_EFFECTIVE <=  NVL(DP.PERIOD_CLOSE_DATE, ah.date_effective+1)
AND       	NVL(AH.DATE_INEFFECTIVE,SYSDATE+1) > NVL(DP.PERIOD_CLOSE_DATE, SYSDATE)
AND	AD.ASSET_ID                     	=  ah.ASSET_ID
AND	CB.CATEGORY_ID                  	=  AH.CATEGORY_ID               
AND 	CB.BOOK_TYPE_CODE               	=  :P_BOOK
AND     	AH.ASSET_TYPE                   	=  FALU.LOOKUP_CODE     
AND 	FALU.LOOKUP_TYPE                	= 'ASSET TYPE'
GROUP BY	
	&ACCT_FLEX_BAL_SEG,
        	FALU.MEANING,
        	decode(AH.ASSET_TYPE, 'CIP', CB.CIP_COST_ACCT,
               	CB.ASSET_COST_ACCT),
         	&ACCT_FLEX_COST_SEG,
                	decode(AH.ASSET_TYPE, 'CIP', NULL,
               	CB.DEPRN_RESERVE_ACCT),
                	AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION,
        	BKS.DATE_PLACED_IN_SERVICE,
        	BKS.DEPRN_METHOD_CODE,
                	BKS.LIFE_IN_MONTHS,
                	BKS.PRODUCTION_CAPACITY,
                	BKS.ADJUSTED_RATE,
        	dh.distribution_id,
                	adj.adjustment_amount,
        	TH.TRANSACTION_HEADER_ID,
				--ADDED FOR DT FIXES
			fnd_flex_xml_publisher_apis.process_kff_combination_1('d_comp_code', 'SQLGL', 'GL#', DHCC.CHART_OF_ACCOUNTS_ID, NULL, DHCC.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') , 
	&D_GL_ACCOUNT , 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_center', 'SQLGL', 'GL#', DHCC.CHART_OF_ACCOUNTS_ID, NULL, DHCC.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE')
UNION All
SELECT 	&ACCT_FLEX_BAL_SEG COMP_CODE,
	     FALU.MEANING ASSET_TYPE,
        	decode(AH.ASSET_TYPE, 'CIP', CB.CIP_COST_ACCT, CB.ASSET_COST_ACCT) GL_ACCOUNT,  
	     &ACCT_FLEX_COST_SEG COST_CENTER,
          decode(AH.ASSET_TYPE, 'CIP', NULL,CB.DEPRN_RESERVE_ACCT) RES_ACCOUNT,
          AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION ASSET_NUMBER,
        	BKS.DATE_PLACED_IN_SERVICE START_DATE,
        	BKS.DEPRN_METHOD_CODE METHOD,
          BKS.LIFE_IN_MONTHS LIFE,
          BKS.PRODUCTION_CAPACITY PROD,
          BKS.ADJUSTED_RATE ADJ_RATE,
          0 BONUS_RATE,
          BKS.cost COST,
        	0 YTD_DEPRN,
        	0 DEPRN_RESERVE,
        	TH.TRANSACTION_HEADER_ID THID, 
     	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_comp_code', 'SQLGL', 'GL#', DHCC.CHART_OF_ACCOUNTS_ID, NULL, DHCC.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') d_comp_code, 
     	&D_GL_ACCOUNT d_gl_account, 
          fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_center', 'SQLGL', 'GL#', DHCC.CHART_OF_ACCOUNTS_ID, NULL, DHCC.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') d_cost_center, 
	     FA_FAS420_XMLP_PKG.d_lifeformula(BKS.LIFE_IN_MONTHS, BKS.ADJUSTED_RATE, 0, BKS.PRODUCTION_CAPACITY) D_LIFE
FROM	     &LP_FA_DEPRN_SUMMARY	DS, 
	     FA_ADDITIONS           		AD,
	     GL_CODE_COMBINATIONS 	DHCC,
	     FA_DISTRIBUTION_HISTORY	DH,
	     FA_LOOKUPS		FALU,
	     FA_CATEGORY_BOOKS	CB,
	     FA_TRANSACTION_HEADERS	TH,
	     FA_ASSET_HISTORY		AH,
	     &LP_FA_BOOKS		BKS, 
	     &LP_FA_DEPRN_PERIODS	DP,    
	     &LP_FA_DEPRN_DETAIL	DD