SELECT
null comp_code,
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,
cb.asset_cost_acct account,
ad.asset_number asset,
Round(sum(decode(ret.units, NUll,
nvl(ret.proceeds_of_sale,0.00)
* (dh.units_assigned /ah.units),
nvl(ret.proceeds_of_sale,0.00)
* 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,
Round(sum(decode(ret.units, NUll,
(books.adjusted_recoverable_cost - books_new.adjusted_recoverable_cost)
* (dh.units_assigned /ah.units),
(books.adjusted_recoverable_cost - books_new.adjusted_recoverable_cost)
* abs(dh.transaction_units) / ret.units)), :PRECISION)
recoverable,
decode(greatest(months_between(ret.date_retired,
books.date_placed_in_service)/12, 1), 1, 1,
decode(greatest(piv2.price_index_value/
piv.price_index_value, 1), 1, 1, piv2.price_index_value/
piv.price_index_value))
ifactor,
Round(sum((decode(ret.units, NUll,
ret.cost_retired * (dh.units_assigned /ah.units),
ret.cost_retired * abs(dh.transaction_units) / ret.units)) *
(decode(greatest(months_between(ret.date_retired,
books.date_placed_in_service)/12, 1), 1, 1,
decode(greatest(piv2.price_index_value/
piv.price_index_value, 1), 1, 1, piv2.price_index_value/
piv.price_index_value))) + ret.cost_of_removal), :PRECISION)
icbase,
Round(sum(decode(ret.units, NULL,
least(ret.cost_retired + ret.cost_of_removal,
greatest(ret.proceeds_of_sale *
(books.adjusted_recoverable_cost - books_new.adjusted_recoverable_cost )
/ ret.cost_retired,ret.nbv_retired +
ret.cost_of_removal))
* (dh.units_assigned /ah.units),
least(ret.cost_retired + ret.cost_of_removal,
greatest(ret.proceeds_of_sale
* (books.adjusted_recoverable_cost - books_new.adjusted_recoverable_cost )
/ ret.cost_retired,ret.nbv_retired +
ret.cost_of_removal))
* abs(dh.transaction_units) / ret.units)), :PRECISION)
rcbase,
Round(sum(decode(ret.units, NULL,
greatest(0, least(ret.proceeds_of_sale
* (books.adjusted_recoverable_cost - books_new.adjusted_recoverable_cost )
/ ret.cost_retired - ret.nbv_retired -
ret.cost_of_removal, ret.cost_retired - ret.nbv_retired))
* (dh.units_assigned /ah.units),
greatest(0, least(ret.proceeds_of_sale
* (books.adjusted_recoverable_cost - books_new.adjusted_recoverable_cost )
/ ret.cost_retired - ret.nbv_retired -
ret.cost_of_removal, ret.cost_retired - ret.nbv_retired))
* abs(dh.transaction_units) / ret.units)), :PRECISION)
oincome,
Round(sum(decode(sign(
decode(ret.units, NUll,
nvl(ret.proceeds_of_sale *
(books.adjusted_recoverable_cost - books_new.adjusted_recoverable_cost) /
ret.cost_retired,0.00)
* (dh.units_assigned /ah.units),
nvl(ret.proceeds_of_sale *
(books.adjusted_recoverable_cost - books_new.adjusted_recoverable_cost) /
ret.cost_retired,0.00)
* abs(dh.transaction_units) / ret.units) -
decode(ret.units, NULL,
least(ret.cost_retired + ret.cost_of_removal,
greatest(ret.proceeds_of_sale *
(books.adjusted_recoverable_cost - books_new.adjusted_recoverable_cost) /
ret.cost_retired,
((books.adjusted_recoverable_cost - books_new.adjusted_recoverable_cost ) -
(ret.cost_retired - ret.nbv_retired )) +
ret.cost_of_removal))
* (dh.units_assigned /ah.units),
least(ret.cost_retired + ret.cost_of_removal,
greatest(ret.proceeds_of_sale *
(books.adjusted_recoverable_cost - books_new.adjusted_recoverable_cost) /
ret.cost_retired,
((books.adjusted_recoverable_cost - books_new.adjusted_recoverable_cost ) -
(ret.cost_retired - ret.nbv_retired )) +
ret.cost_of_removal))
* abs(dh.transaction_units) / ret.units)), -1,
(decode(ret.units, NUll,
nvl(ret.proceeds_of_sale *
(books.adjusted_recoverable_cost - books_new.adjusted_recoverable_cost) /
ret.cost_retired,0.00)
* (dh.units_assigned /ah.units),
nvl(ret.proceeds_of_sale *
(books.adjusted_recoverable_cost - books_new.adjusted_recoverable_cost) /
ret.cost_retired,0.00)
* abs(dh.transaction_units) / ret.units) -
decode(ret.units, NULL,
least(ret.cost_retired + ret.cost_of_removal,
greatest(ret.proceeds_of_sale *
(books.adjusted_recoverable_cost - books_new.adjusted_recoverable_cost) /
ret.cost_retired,
((books.adjusted_recoverable_cost - books_new.adjusted_recoverable_cost ) -
(ret.cost_retired - ret.nbv_retired )) +
ret.cost_of_removal))
* (dh.units_assigned /ah.units),
least(ret.cost_retired + ret.cost_of_removal,
greatest(ret.proceeds_of_sale *
(books.adjusted_recoverable_cost - books_new.adjusted_recoverable_cost) /
ret.cost_retired,
((books.adjusted_recoverable_cost - books_new.adjusted_recoverable_cost ) -
(ret.cost_retired - ret.nbv_retired )) +
ret.cost_of_removal))
* abs(dh.transaction_units) / ret.units)),
greatest(0,(decode(ret.units, NUll,
nvl(ret.proceeds_of_sale *
(books.adjusted_recoverable_cost - books_new.adjusted_recoverable_cost) /
ret.cost_retired,0.00)
* (dh.units_assigned /ah.units),
nvl(ret.proceeds_of_sale *
(books.adjusted_recoverable_cost - books_new.adjusted_recoverable_cost) /
ret.cost_retired,0.00)
* abs(dh.transaction_units) / ret.units) -
(decode(ret.units, NUll,
ret.cost_retired * (dh.units_assigned /ah.units),
ret.cost_retired * abs(dh.transaction_units) / ret.units)) *
(decode(greatest(months_between(ret.date_retired,
books.date_placed_in_service)/12, 1), 1, 1,
decode(greatest(piv2.price_index_value/
piv.price_index_value, 1), 1, 1, piv2.price_index_value/
piv.price_index_value))) + ret.cost_of_removal)))), :PRECISION)
gainloss
FROM
fa_retirements ret,
fa_books books,
fa_books books_new,
fa_transaction_headers th,
fa_additions ad,
fa_asset_history ah,
fa_category_books cb,
fa_category_book_defaults cbd,
fa_price_index_values piv,
fa_price_index_values piv2,
fa_price_indexes pdx,
fa_distribution_history dh,
gl_code_combinations dhcc
WHERE
th.date_effective >= :Period1_POD AND
th.date_effective <= :Period2_PCD AND
th.book_type_code = :P_BOOK 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
piv.price_index_id = pdx.price_index_id and
piv2.price_index_id = pdx.price_index_id and
piv.from_date <= books.date_placed_in_service and
piv.to_date >= books.date_placed_in_service and
piv2.from_date <= ret.date_retired and
piv2.to_date >= ret.date_retired
AND
ad.asset_id = th.asset_id
AND
books.transaction_header_id_out = th.transaction_header_id AND
books.book_type_code = :P_BOOK AND
books.date_placed_in_service >= to_date('1985/09/20','YYYY/MM/DD') AND
books.asset_id = th.asset_id
AND
books_new.transaction_header_id_in = th.transaction_header_id
AND
cb.category_id = ah.category_id AND
cbd.price_index_name = pdx.price_index_name AND
cbd.book_type_code = cb.book_type_code AND
cbd.category_id = cb.category_id AND
books.date_placed_in_service between cbd.start_dpis
and nvl(cbd.end_dpis,sysdate) AND
cb.book_type_code = :P_BOOK
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.book_type_code = :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
GROUP BY
null,
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'),
cb.asset_cost_acct,
ad.asset_number,
decode(greatest(months_between(ret.date_retired,
books.date_placed_in_service)/12, 1), 1, 1,
decode(greatest(piv2.price_index_value/
piv.price_index_value, 1), 1, 1, piv2.price_index_value/
piv.price_index_value))
ORDER BY 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'),
cb.asset_cost_acct,
lpad(ad.asset_number,15,0) |
