FA Leased Assets - draft

SELECT 	&Acct_Flex_Bal_Seg			comp_code,
	&Cat_Flex_All_Seg				category,
	le.lease_number,
	ad.asset_number || ' - ' || ad.description		asset_number_desc,
	books.date_placed_in_service,
	sum(decode (dd.deprn_source_code,
		'D', dd.cost,
		dd.addition_cost_to_clear))		cost,
	sum(dd.deprn_reserve)			reserve,
	sum(dd.cost) - sum(dd.deprn_reserve)		nbv,
	--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') COMP_CODE_DSP,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_category', 'OFA', 'CAT#', 101, NULL, cat.CATEGORY_ID, 'ALL', 'Y', 'VALUE') D_Category
FROM	
	fa_additions				ad,
	fa_leases					le,
	fa_deprn_detail				dd,
	fa_categories				cat,
	fa_books					books,
	GL_CODE_COMBINATIONS			DHCC,
	FA_DISTRIBUTION_HISTORY			DH
WHERE 	
	(cat.owned_leased		= 'LEASED'	or
	 cat.category_type		= 'LEASE' )
AND
	ad.asset_category_id		= cat.category_id
AND
	le.lease_id			= ad.lease_id
AND
	books.book_type_code = :P_Book AND
	books.asset_id			= ad.asset_id		AND
	books.transaction_header_id_out is NULL
AND
	dh.book_type_code			= books.book_type_code	and
	dh.asset_id			= books.asset_id		and
	dh.date_ineffective			is null	
AND
	dhcc.code_combination_id		= dh.code_combination_id
AND
	dd.asset_id			= books.asset_id		AND
	dd.book_type_code			= books.book_type_code	AND
	dd.distribution_id			= dh.distribution_id		AND
	dd.period_counter			=
		(select	max (dd_sub.period_counter)
		 from	fa_deprn_detail dd_sub
		 where	dd_sub.asset_id		= books.asset_id
		 and	dd_sub.book_type_code	= books.book_type_code
		 and	dd_sub.distribution_id	= dh.distribution_id)
GROUP BY
	books.book_type_code,
	&Acct_Flex_Bal_Seg,
	&Cat_Flex_All_Seg,
	le.lease_number,
	ad.asset_number,
	ad.description,
	books.date_placed_in_service
		--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('d_category', 'OFA', 'CAT#', 101, NULL, cat.CATEGORY_ID, 'ALL', 'Y', 'VALUE')
ORDER BY 
	&Acct_Flex_Bal_Seg,
	&Cat_Flex_All_Seg,
	le.lease_number,
	ad.asset_number
	--added
	,DATE_PLACED_IN_SERVICE,COST,RESERVE,NBV