SELECT
DP.PERIOD_NAME PERIOD_NAME,
DP.PERIOD_COUNTER PERIOD_COUNTER,
fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') COMP_CODE,
CB.ASSET_COST_ACCT GL_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.ADJUSTED_RATE ADJ_RATE,
DS.BONUS_RATE BONUS_RATE,
BOOKS.PRODUCTION_CAPACITY PROD,
sum(DD.COST) COST,
sum(DD.DEPRN_AMOUNT) DEPRN_AMOUNT,
sum(DD.YTD_DEPRN) YTD_DEPRN,
sum(DD.DEPRN_RESERVE) DEPRN_RESERVE,
--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('comp_code_dsp', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') COMP_CODE_DSP1,
FA_FAS402_XMLP_PKG.d_lifeformula(BOOKS.LIFE_IN_MONTHS, BOOKS.ADJUSTED_RATE, DS.BONUS_RATE, BOOKS.PRODUCTION_CAPACITY) D_LIFE
FROM
&lp_fa_deprn_periods DP,
&lp_fa_deprn_detail DD,
FA_DISTRIBUTION_HISTORY DH,
GL_CODE_COMBINATIONS DHCC,
&lp_fa_books BOOKS,
&lp_fa_book_controls BC,
FA_ADDITIONS_NON_SEC_VL AD,
&lp_fa_deprn_summary DS,
FA_ASSET_HISTORY AH,
FA_CATEGORY_BOOKS CB
WHERE
DP.BOOK_TYPE_CODE = :P_BOOK AND
DP.PERIOD_COUNTER >= :PERIOD1_PC AND
DP.PERIOD_COUNTER <= :PERIOD2_PC
AND
BOOKS.PERIOD_COUNTER_FULLY_RESERVED =
(SELECT MAX(PERIOD_COUNTER_FULLY_RESERVED)
FROM &LP_FA_BOOKS BKS_PC
WHERE BKS_PC.BOOK_TYPE_CODE = BOOKS.BOOK_TYPE_CODE
AND BKS_PC.ASSET_ID = BOOKS.ASSET_ID
AND BKS_PC.PERIOD_COUNTER_FULLY_RESERVED BETWEEN :PERIOD1_PC AND :PERIOD2_PC)
AND
BOOKS.PERIOD_COUNTER_FULLY_RESERVED = DP.PERIOD_COUNTER AND
BOOKS.BOOK_TYPE_CODE = DP.BOOK_TYPE_CODE AND
BOOKS.DATE_EFFECTIVE <= Nvl(DP.PERIOD_CLOSE_DATE, BOOKS.DATE_EFFECTIVE) AND
NVL(BOOKS.DATE_INEFFECTIVE, SYSDATE+1) > Nvl(DP.PERIOD_CLOSE_DATE, SYSDATE)
AND
DS.ASSET_ID = BOOKS.ASSET_ID AND
DS.PERIOD_COUNTER = DP.PERIOD_COUNTER AND
DS.BOOK_TYPE_CODE = DP.BOOK_TYPE_CODE
AND
DD.BOOK_TYPE_CODE = DP.BOOK_TYPE_CODE AND
DD.ASSET_ID = BOOKS.ASSET_ID AND
DD.DISTRIBUTION_ID = DH.DISTRIBUTION_ID AND
DD.PERIOD_COUNTER = DP.PERIOD_COUNTER
AND
AD.ASSET_ID = BOOKS.ASSET_ID
AND
CB.CATEGORY_ID = AH.CATEGORY_ID AND
CB.BOOK_TYPE_CODE = BOOKS.BOOK_TYPE_CODE
AND
AH.ASSET_ID = AD.ASSET_ID AND
AH.DATE_EFFECTIVE <= Nvl(DP.PERIOD_CLOSE_DATE, AH.DATE_EFFECTIVE) AND
nvl(AH.DATE_INEFFECTIVE,SYSDATE + 1) > Nvl(DP.PERIOD_CLOSE_DATE, SYSDATE)
AND
BC.BOOK_TYPE_CODE = BOOKS.BOOK_TYPE_CODE
AND
DH.ASSET_ID = BOOKS.ASSET_ID AND
DH.BOOK_TYPE_CODE = BC.DISTRIBUTION_SOURCE_BOOK AND
DH.DATE_EFFECTIVE <= Nvl(DP.PERIOD_CLOSE_DATE, DH.DATE_EFFECTIVE) and
NVL(DH.DATE_INEFFECTIVE,SYSDATE + 1) > Nvl(DP.PERIOD_CLOSE_DATE, SYSDATE)
AND
DHCC.CODE_COMBINATION_ID = DH.CODE_COMBINATION_ID
GROUP BY
DP.PERIOD_NAME,
DP.PERIOD_COUNTER,
fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE'),
CB.ASSET_COST_ACCT,
AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION,
BOOKS.DATE_PLACED_IN_SERVICE,
BOOKS.DEPRN_METHOD_CODE,
BOOKS.LIFE_IN_MONTHS,
BOOKS.ADJUSTED_RATE,
DS.BONUS_RATE,
BOOKS.PRODUCTION_CAPACITY,
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')
ORDER BY
--Commented during DT Fix
/* DP.PERIOD_COUNTER,
fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE'),
CB.ASSET_COST_ACCT,
AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION*/
--Added during DT Fix
2,1,3,4,5,6,7,8,10,11,9,12,13,14,15
--End of DT Fix |
