SELECT --null COMP_CODE,
&ACCT_FLEX_BAL_SEG COMP_CODE,
FY.FISCAL_YEAR FISCAL_YEAR,
CB.ASSET_COST_ACCT GL_ACCOUNT,
CB.DEPRN_RESERVE_ACCT RES_ACCOUNT,
AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION ASSET_NUMBER,
BOOKS.DATE_PLACED_IN_SERVICE START_DATE,
BOOKS.DEPRN_METHOD_CODE METHOD,
BOOKS.LIFE_IN_MONTHS LIFE,
BOOKS.PRODUCTION_CAPACITY PROD,
BOOKS.ADJUSTED_RATE ADJ_RATE,
DS.BONUS_RATE BONUS_RATE,
SUM(NVL(BOOKS.COST,0)) COST,
sum(nvl(DD.DEPRN_RESERVE,0)) DEPRN_RESERVE,
fnd_flex_xml_publisher_apis.process_kff_combination_1('d_comp_code', 'SQLGL', 'GL#', COMCC.CHART_OF_ACCOUNTS_ID, NULL, COMCC.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') D_COMP_CODE,
FA_FAS540_XMLP_PKG.d_lifeformula(BOOKS.LIFE_IN_MONTHS, BOOKS.ADJUSTED_RATE , DS.BONUS_RATE , BOOKS.PRODUCTION_CAPACITY) D_LIFE
FROM
FA_DEPRN_DETAIL DD,
FA_ADDITIONS AD,
FA_BOOKS BOOKS,
FA_ASSET_HISTORY AH,
FA_DISTRIBUTION_HISTORY DH,
FA_DEPRN_PERIODS DP,
FA_CATEGORY_BOOKS CB,
FA_FISCAL_YEAR FY,
FA_DEPRN_SUMMARY DS,
GL_CODE_COMBINATIONS COMCC,
FA_TRANSACTION_HEADERS THDIS,
FA_TRANSACTION_HEADERS THADD
WHERE DP.BOOK_TYPE_CODE =:P_BOOK AND
DP.PERIOD_COUNTER >= :PERIOD1_PC AND
DP.PERIOD_COUNTER <= nvl(:PERIOD2_PC, DP.PERIOD_COUNTER)AND
THADD.DATE_EFFECTIVE >= DP.PERIOD_OPEN_DATE AND
THADD.DATE_EFFECTIVE < nvl(DP.PERIOD_CLOSE_DATE, sysdate) AND
THADD.BOOK_TYPE_CODE =:P_BOOK AND
THADD.TRANSACTION_TYPE_CODE = 'ADDITION'
AND
THDIS.BOOK_TYPE_CODE =:DIST_BOOK AND
THDIS.TRANSACTION_TYPE_CODE = 'TRANSFER IN' AND
THDIS.ASSET_ID = THADD.ASSET_ID
AND
BOOKS.TRANSACTION_HEADER_ID_IN = THADD.TRANSACTION_HEADER_ID AND
BOOKS.BOOK_TYPE_CODE = :P_BOOK AND
BOOKS.ASSET_ID = THADD.ASSET_ID
AND
DS.BOOK_TYPE_CODE = :P_BOOK AND
DS.ASSET_ID = BOOKS.ASSET_ID AND
DS.DEPRN_SOURCE_CODE = 'BOOKS'
AND
DD.BOOK_TYPE_CODE = :P_BOOK AND
DD.ASSET_ID = THADD.ASSET_ID AND
DD.DEPRN_SOURCE_CODE = 'B' AND
DD.DISTRIBUTION_ID = DH.DISTRIBUTION_ID AND
COMCC.CODE_COMBINATION_ID = DH.CODE_COMBINATION_ID
AND
AD.ASSET_ID = THADD.ASSET_ID
AND
AH.ASSET_ID = AD.ASSET_ID AND
AH.DATE_EFFECTIVE <= THADD.DATE_EFFECTIVE AND
nvl(AH.DATE_INEFFECTIVE,sysdate)> THADD.DATE_EFFECTIVE
AND
CB.CATEGORY_ID = AH.CATEGORY_ID AND
CB.BOOK_TYPE_CODE = :P_BOOK
AND
FY.FISCAL_YEAR_NAME =:FISCAL_YEAR_NAME AND
FY.START_DATE <= BOOKS.DATE_PLACED_IN_SERVICE AND
FY.END_DATE >= BOOKS.DATE_PLACED_IN_SERVICE
GROUP BY
&ACCT_FLEX_BAL_SEG,
fnd_flex_xml_publisher_apis.process_kff_combination_1('d_comp_code', 'SQLGL', 'GL#', COMCC.CHART_OF_ACCOUNTS_ID, NULL, COMCC.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE'),
FY.FISCAL_YEAR,
CB.ASSET_COST_ACCT,
CB.DEPRN_RESERVE_ACCT,
AD.ASSET_NUMBER,
AD.DESCRIPTION,
BOOKS.DATE_PLACED_IN_SERVICE,
BOOKS.DEPRN_METHOD_CODE,
BOOKS.LIFE_IN_MONTHS,
BOOKS.PRODUCTION_CAPACITY,
BOOKS.ADJUSTED_RATE,
DS.BONUS_RATE
--ORDER BY 1, 2, 3, 4, 5
order by
COMP_CODE,FISCAL_YEAR,GL_ACCOUNT,RES_ACCOUNT,ASSET_NUMBER,START_DATE,METHOD,LIFE,COST,
DEPRN_RESERVE,ADJ_RATE,PROD,BONUS_RATE |