FA Retired Assets Without Property Classes - draft

SELECT	--NVL(&ACCT_FLEX_BAL_SEG,'NONE')			COMP_CODE,
		NVL(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'),'NONE')			D_COMP_CODE1,
        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
FROM	
	FA_DEPRN_PERIODS			DP,
	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,
	FA_BOOK_CONTROLS			BC
WHERE
	DP.BOOK_TYPE_CODE	     = :P_BOOK  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,SYSDATE)AND
	TH.BOOK_TYPE_CODE            = :P_BOOK  AND
	TH.TRANSACTION_TYPE_CODE IN ('FULL RETIREMENT',
					'PARTIAL RETIREMENT')   
AND
	RET.TRANSACTION_HEADER_ID_IN = TH.TRANSACTION_HEADER_ID
AND
	AD.ASSET_ID 		     = TH.ASSET_ID			AND
	AD.PROPERTY_1245_1250_CODE  IS NULL
AND	
	CB.CATEGORY_ID         	     = AH.CATEGORY_ID			AND
	CB.BOOK_TYPE_CODE            = :P_BOOK
AND	
	AH.ASSET_ID		     = AD.ASSET_ID			AND
	TH.TRANSACTION_HEADER_ID >= AH.TRANSACTION_HEADER_ID_IN		AND
	TH.TRANSACTION_HEADER_ID < NVL(AH.TRANSACTION_HEADER_ID_OUT,
					TH.TRANSACTION_HEADER_ID + 1)
AND
	BC.BOOK_TYPE_CODE = :P_BOOK  AND
	DH.BOOK_TYPE_CODE = BC.DISTRIBUTION_SOURCE_BOOK
AND
	DH.ASSET_ID = TH.ASSET_ID				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 1,2,3
ORDER BY 1,2,3,4,5