SELECT
--nvl(&ACCT_FLEX_BAL_SEG,'None') COMP_CODE,
-- &ACCT_FLEX_ACCT_SEG ACCOUNT,
--Added during DT Fix
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,
fnd_flex_xml_publisher_apis.process_kff_combination_1('account_dsp', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'Y', 'VALUE') ACCOUNT_DSP1,
--End of DT Fix
ad.asset_number || ' - ' || AD.DESCRIPTION ASSET_NUMBER,
ad.asset_id ASSET_ID,
ROUND(sum(decode(ret.units, NUll, ret.proceeds_of_sale
* (dh.units_assigned /ah.units),
ret.proceeds_of_sale * (abs(dh.transaction_units) / ret.units))),:PRECISION)
PROCEEDS,
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_RETIRED,
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)
GAIN,
ROUND(sum(decode(sign(ret.gain_loss_amount), 1,
decode(sign(ret.cost_retired - ret.nbv_retired-ret.gain_loss_amount),
-1,
(decode(ret.units, NUll,
(ret.gain_loss_amount - ret.cost_retired + ret.nbv_retired)
* (dh.units_assigned /ah.units),
(ret.gain_loss_amount - ret.cost_retired + ret.nbv_retired)
* (abs(dh.transaction_units) / ret.units))), (0.00)), (0.00))),:PRECISION) CAP_GAIN,
ROUND(sum(decode(sign(ret.gain_loss_amount), 1,
decode(sign(ret.cost_retired - ret.nbv_retired-ret.gain_loss_amount),
-1,
(decode(ret.units, NUll, (ret.cost_retired-ret.nbv_retired)
* (dh.units_assigned /ah.units),
(ret.cost_retired - ret.nbv_retired)
* (abs(dh.transaction_units) / ret.units))), (decode(ret.units, NUll, ret.gain_loss_amount
* (dh.units_assigned /ah.units),
ret.gain_loss_amount * (abs(dh.transaction_units) / ret.units)))),0.00)),:PRECISION) ORD_INCOME,
ROUND(sum(decode(sign(ret.gain_loss_amount), -1,
(decode(ret.units, NUll, ret.gain_loss_amount
* (dh.units_assigned /ah.units),
ret.gain_loss_amount * (abs(dh.transaction_units) / ret.units))),
0.00)),:PRECISION) CAP_LOSS,
Decode (Sign(ret.gain_loss_amount), -1, :LOSS_NLS, :GAIN_NLS) GAIN_LOSS
FROM
fa_deprn_periods dp,
fa_retirements ret,
fa_lookups lu,
fa_additions ad,
fa_asset_history ah,
fa_category_books cb,
fa_transaction_headers th,
fa_books books,
fa_distribution_history dh,
fa_category_book_defaults cbd,
gl_code_combinations dhcc
WHERE
dp.book_type_code = UPPER(:P_BOOK) AND
dp.period_counter >= :PERIOD1_PC AND
dp.period_counter <= nvl(:PERIOD2_PC,
dp.period_counter)
AND
th.date_effective >= dp.period_open_date AND
th.date_effective <= NVL(dp.period_close_date,sysdate)AND
th.book_type_code = dp.book_type_code AND
(th.transaction_type_code = 'FULL RETIREMENT' OR
th.transaction_type_code = 'PARTIAL RETIREMENT')
AND
ret.transaction_header_id_in = th.transaction_header_id AND
((UPPER(ret.retirement_type_code) like '%SALE%') OR
((ret.retirement_type_code is NULL) AND (ret.proceeds_of_sale > 0)))
AND
ret.status = 'PROCESSED'
AND
books.transaction_header_id_out = th.transaction_header_id AND
books.book_type_code = th.book_type_code
AND
cbd.category_id = ah.category_id AND
cbd.book_type_code = th.book_type_code AND
(books.date_placed_in_service between cbd.start_dpis and
nvl(cbd.end_dpis,sysdate)) AND
(months_between(ret.date_retired, books.date_placed_in_service) >
nvl(cbd.capital_gain_threshold,12))
AND
cb.category_id = ah.category_id AND
cb.book_type_code = th.book_type_code
AND
lu.lookup_type(+) = 'GAINLOSS' AND
lu.lookup_code(+) = Decode (Sign(ret.gain_loss_amount), -1, 'LOSS', 'GAIN')
AND
ad.asset_id = th.asset_id AND
ad.property_1245_1250_code = '1245'
AND
ah.asset_id = ad.asset_id AND
ah.date_effective <= th.date_effective AND
(ah.date_ineffective is null OR
ah.date_ineffective > th.date_effective)
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
Sign(ret.gain_loss_amount),
lu.meaning,
:PERIOD1_PC ,
nvl(:PERIOD2_PC, '&3'),
--Start DT Fix
--nvl(&ACCT_FLEX_BAL_SEG,'None'),
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'),
--End of DT Fix
th.book_type_code,
--Start DT Fix
--&ACCT_FLEX_ACCT_SEG,
fnd_flex_xml_publisher_apis.process_kff_combination_1('account_dsp', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'Y', 'VALUE'),
--End of DT Fix
ad.asset_number,
ad.description,
ad.asset_id
ORDER BY
/*
nvl(&ACCT_FLEX_BAL_SEG,'None'),
&ACCT_FLEX_ACCT_SEG,
ad.asset_number
*/
1, 12, 2, 4, 3, 5, 6, 7, 8, 9, 10, 11 |