FA Retired Assets Without Retirement Types - draft

SELECT  distinct nvl(&ACCT_FLEX_BAL_SEG,'None')			   COMP_CODE,
	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,
        decode(ah.asset_type,'CIP',cb.cip_cost_acct,
		cb.asset_cost_acct)			account,
	ad.asset_number || ' - ' || ad.description	asset_num,
	ad.property_type_code			type,
	ret.date_retired				date_retired,
                 ad.asset_id asset_id
FROM	
	fa_retirements				ret,
	fa_additions				ad,
	fa_asset_history			ah,
	fa_category_books			cb,
	fa_transaction_headers			th,
	fa_distribution_history			dh,
	gl_code_combinations			dhcc
WHERE
	th.date_effective between :PERIOD1_POD and 
	nvl(:PERIOD2_PCD,sysdate)
AND	th.book_type_code = UPPER(:P_BOOK) AND
th.transaction_type_code in
				('FULL RETIREMENT', 'PARTIAL RETIREMENT')
AND
	ret.transaction_header_id_in = th.transaction_header_id		AND
	ret.retirement_type_code	is null	AND
	ret.status = 'PROCESSED'
AND
	ad.asset_id 		     = th.asset_id
AND	
	cb.category_id         	     = ah.category_id			AND
	cb.book_type_code            = UPPER(:P_BOOK)
AND	
	ah.asset_id		     = ad.asset_id			AND
	ah.date_effective            <= th.date_effective		AND
	(ah.date_ineffective is null					OR
	ah.date_ineffective 	     > th.date_effective)
and
	dh.asset_id = th.asset_id				AND
	dh.book_type_code =:Distribution_Source_Book AND 
	(	dh.retirement_id = ret.retirement_id
or (ret.date_effective >= dh.date_effective       and
    ret.date_effective <= nvl(dh.date_ineffective,sysdate)
    and ret.units is null))
AND
	dhcc.code_combination_id = dh.code_combination_id
ORDER BY 
	 /*nvl(&ACCT_FLEX_BAL_SEG,'None'),
	decode(ah.asset_type,'CIP',cb.cip_cost_acct,
		cb.asset_cost_acct),     3 */
		D_COMP_CODE,	account,asset_num,	type,date_retired,
                 asset_id