FA CIP Assets - draft

select	
	&ACCT_FLEX_BAL_SEG			COMP_CODE,
	FACB.CIP_COST_ACCT			ASSET_ACCOUNT,
	&ACCT_FLEX_COST_SEG			COST_CENTER,
	FADD.ASSET_NUMBER                                	ASSET_NUMBER,
	POV.SEGMENT1 || DECODE(POV.SEGMENT1, NULL, ' ',' - ') || 
		POV.VENDOR_NAME		VEND_NUM_NAME,
	INV.INVOICE_NUMBER		INV_NUM,
	INV.INVOICE_LINE_NUMBER||' - '||INV.AP_DISTRIBUTION_LINE_NUMBER		LINE_NUM,
	FADD.ASSET_ID				ASSET_ID,
	FADD.ASSET_NUMBER||' - '||FADD.DESCRIPTION	ASSET_NUM_DESC,
	INV.DESCRIPTION				DESCRIPTION,
	ROUND (NVL(MC.FIXED_ASSETS_COST,INV.FIXED_ASSETS_COST) *
	     SUM (NVL (FADH.UNITS_ASSIGNED, FAH.UNITS) / FAH.UNITS),
	     :PRECISION) 				COST,
	FAH.CATEGORY_ID				ASSET_CAT, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('comp_code_dsp', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') COMP_CODE_DSP, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('cost_center_dsp', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') COST_CENTER_DSP
from
	GL_CODE_COMBINATIONS		DHCC,
	PO_VENDORS			POV,
	FA_CATEGORY_BOOKS		FACB,
	FA_MC_ASSET_INVOICES		MC,
	FA_ASSET_INVOICES_BAS		INV,
	FA_ADDITIONS			FADD,
	FA_ASSET_HISTORY		FAH,
	FA_DISTRIBUTION_HISTORY  		FADH,
	FA_BOOKS			FAB
where
	FAH.ASSET_TYPE		= 'CIP'			and
	:PERIOD1_PCD between
		FAH.DATE_EFFECTIVE AND 
		NVL (FAH.DATE_INEFFECTIVE, :PERIOD1_PCD)
and
	FAB.ASSET_ID		= FAH.ASSET_ID		and
	FAB.BOOK_TYPE_CODE = :P_BOOK and
	:PERIOD1_PCD between 
	FAB.DATE_EFFECTIVE AND 
		NVL (FAB.DATE_INEFFECTIVE, :PERIOD1_PCD)	and
	NVL(FAB.PERIOD_COUNTER_FULLY_RETIRED,(:PERIOD1_PC + 1)) >:PERIOD1_PC
	and
	FADD.ASSET_ID		= FAB.ASSET_ID
and
	INV.ASSET_ID(+)             	= FADD.ASSET_ID		and
	MC.ASSET_ID(+) = INV.ASSET_ID		and
	MC.ASSET_INVOICE_ID(+) = INV.ASSET_INVOICE_ID  and
	MC.INVOICE_TRANSACTION_ID_IN(+) = INV.INVOICE_TRANSACTION_ID_IN  and
	MC.SET_OF_BOOKS_ID(+) = :set_of_books_id and
	INV.DELETED_FLAG(+)	= 'NO'		and :PERIOD1_PCD 
	between
		INV.DATE_EFFECTIVE(+) AND 
		NVL (INV.DATE_INEFFECTIVE(+), :PERIOD1_PCD)
and
	POV.VENDOR_ID(+)	= INV.PO_VENDOR_ID
and
	FACB.CATEGORY_ID	= FAH.CATEGORY_ID	and
	FACB.BOOK_TYPE_CODE = :P_BOOK
	and
	FADH.BOOK_TYPE_CODE = :P_BOOK and
	FADH.ASSET_ID		= FADD.ASSET_ID		and
	:PERIOD1_PCD BETWEEN
		FADH.DATE_EFFECTIVE AND 
		NVL (FADH.DATE_INEFFECTIVE,:PERIOD1_PCD)
		and
	DHCC.CODE_COMBINATION_ID	= FADH.CODE_COMBINATION_ID
group by
	FADD.ASSET_ID,
	FADD.ASSET_NUMBER,
	FAH.CATEGORY_ID,
	FADD.DESCRIPTION,
	POV.SEGMENT1,
	POV.VENDOR_NAME,
	INV.INVOICE_NUMBER,
	INV.INVOICE_LINE_NUMBER||' - '||INV.AP_DISTRIBUTION_LINE_NUMBER,
	INV.DESCRIPTION,
	NVL(MC.FIXED_ASSETS_COST,INV.FIXED_ASSETS_COST),
	FAH.UNITS,
	&ACCT_FLEX_COST_SEG,
	FACB.CIP_COST_ACCT,
	&ACCT_FLEX_BAL_SEG
	--added
	,fnd_flex_xml_publisher_apis.process_kff_combination_1('comp_code_dsp', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') , 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('cost_center_dsp', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') 
--order by	1,2,3,4,5,6,7
ORDER BY COST_CENTER_DSP