SELECT FALU.MEANING ASSET_TYPE,
--nvl(&ACCT_FLEX_BAL_SEG,'None') comp_code,
nvl(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'),'None') comp_code_dsp1,
--NVL(&ACCT_FLEX_COST_SEG,'None') cost_center,
NVL(fnd_flex_xml_publisher_apis.process_kff_combination_1('cost_center_dsp', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE'),'None') cost_center_dsp1,
DECODE(AH.ASSET_TYPE,
'CIP',CB.CIP_COST_ACCT,
CB.ASSET_COST_ACCT) ACCOUNT,
AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION NO,
books.date_placed_in_service DPIS,
RET.DATE_RETIRED DR,
DP.PERIOD_NAME PREIN,
ROUND(sum(decode(ret.units, NUll, ret.cost_retired
* (dh.units_assigned /ah.units),
ret.cost_retired * (abs(dh.transaction_units) / ret.units))),:PRECISION)
cost,
ROUND(sum(decode(ret.units, NUll, ret.cost_retired
* (dh.units_assigned /ah.units),
ret.cost_retired * (abs(dh.transaction_units) / ret.units))) -
sum(decode(ret.units, NUll, ret.nbv_retired
* (dh.units_assigned /ah.units),
ret.nbv_retired * (abs(dh.transaction_units) / ret.units))),:PRECISION)
reserve,
ROUND(sum(decode(ret.units, NUll, ret.gain_loss_amount
* (dh.units_assigned /ah.units),
ret.gain_loss_amount * (abs(dh.transaction_units) / ret.units))),:PRECISION)
gl,
ROUND(sum(decode(ret.units, NUll, nvl(ret.itc_recaptured,0.00)
* (dh.units_assigned /ah.units),
nvl(ret.itc_recaptured,0.00)
* (abs(dh.transaction_units) / ret.units))),:PRECISION)
itc
/*,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('cost_center_dsp', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') cost_center_dsp*/
FROM
FA_LOOKUPS FALU,
FA_DISTRIBUTION_HISTORY DH,
GL_CODE_COMBINATIONS dhCC,
FA_DEPRN_PERIODS DP,
FA_ASSET_HISTORY AH,
FA_ADDITIONS AD,
FA_CATEGORY_BOOKS CB,
FA_BOOKS BOOKS2,
FA_BOOKS BOOKS,
FA_RETIREMENTS RET,
FA_TRANSACTION_HEADERS TH
WHERE
DP.BOOK_TYPE_CODE = :P_Book
AND
TH.DATE_EFFECTIVE BETWEEN
DP.PERIOD_OPEN_DATE AND
NVL (DP.PERIOD_CLOSE_DATE, SYSDATE) AND
TH.BOOK_TYPE_CODE = :P_Book AND
TH.TRANSACTION_TYPE_CODE = 'REINSTATEMENT' AND
TH.DATE_EFFECTIVE BETWEEN
:Period1_POD and :Period2_PCD
AND
BOOKS.TRANSACTION_HEADER_ID_OUT = TH.TRANSACTION_HEADER_ID AND
BOOKS.BOOK_TYPE_CODE = :P_Book
AND
BOOKS2.BOOK_TYPE_CODE = BOOKS.BOOK_TYPE_CODE AND
BOOKS2.ASSET_ID = BOOKS.ASSET_ID AND
BOOKS2.DATE_INEFFECTIVE IS NULL AND
BOOKS2.DATE_EFFECTIVE BETWEEN :PERIOD1_POD AND :PERIOD2_PCD AND
BOOKS2.PERIOD_COUNTER_FULLY_RETIRED IS NULL
AND
RET.TRANSACTION_HEADER_ID_OUT = TH.TRANSACTION_HEADER_ID AND
RET.BOOK_TYPE_CODE = :P_Book AND
AD.ASSET_ID = TH.ASSET_ID
AND
AH.ASSET_ID = TH.ASSET_ID AND
AH.DATE_EFFECTIVE <= TH.DATE_EFFECTIVE AND
(AH.DATE_INEFFECTIVE IS NULL OR
AH.DATE_INEFFECTIVE > TH.DATE_EFFECTIVE)
AND
FALU.LOOKUP_CODE = AH.ASSET_TYPE AND
FALU.LOOKUP_TYPE = 'ASSET TYPE'
AND
CB.CATEGORY_ID = AH.CATEGORY_ID AND
CB.BOOK_TYPE_CODE = :P_Book
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
GROUP BY
-- nvl(&ACCT_FLEX_BAL_SEG,'None'),
nvl(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'),'None'),
FALU.MEANING,
DECODE(AH.ASSET_TYPE,
'CIP',CB.CIP_COST_ACCT,
CB.ASSET_COST_ACCT),
-- NVL(&ACCT_FLEX_COST_SEG,'None'),
NVL(fnd_flex_xml_publisher_apis.process_kff_combination_1('cost_center_dsp', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE'),'None'),
AD.ASSET_NUMBER,
AD.DESCRIPTION,
books.date_placed_in_service,
RET.DATE_RETIRED,
DP.PERIOD_NAME
ORDER BY
/* nvl(&ACCT_FLEX_BAL_SEG,'None'),
falu.meaning,
DECODE(AH.ASSET_TYPE,
'CIP',CB.CIP_COST_ACCT,
CB.ASSET_COST_ACCT),
NVL(&ACCT_FLEX_COST_SEG,'None'),
ASSET_NUMBER*/
2, 1, 4, 3, 5, 6, 7, 8, 9, 10, 11, 12 |
