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 |