FA Asset Additions - draft

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
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(