SELECT
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 account,
ad.property_1245_1250_code class,
ad.asset_number || ' - ' || ad.description asset_num,
bk.date_placed_in_service dpis,
ret.date_retired date_sold,
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 - ret.nbv_retired)
*(dh.units_assigned/ah.units),
(ret.cost_retired - ret.nbv_retired)
*(abs(dh.transaction_units)/ret.units))),:PRECISION)
reserve,
ROUND(sum(decode(ret.units, NULL,
(ret.cost_retired + nvl(ret.cost_of_removal, 0))
*(dh.units_assigned/ah.units),
(ret.cost_retired + nvl(ret.cost_of_removal, 0))
*(abs(dh.transaction_units)/ret.units))),:PRECISION)
basis,
Decode (Sign(ret.gain_loss_amount), -1, :LOSS_NLS, :GAIN_NLS) gain_loss,
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
FROM
fa_retirements ret,
fa_additions ad,
fa_asset_history ah,
fa_system_controls sc,
fa_category_books cb,
fa_transaction_headers th,
fa_books bk,
fa_distribution_history dh,
gl_code_combinations dhcc,
fa_category_book_defaults cbd
WHERE
th.date_effective >= :PERIOD1_POD AND
th.date_effective <= :PERIOD2_PCD AND
th.book_type_code = :P_BOOK AND
th.transaction_type_code || '' in
('FULL RETIREMENT',
'PARTIAL RETIREMENT') AND
th.transaction_key = 'R'
AND
ah.asset_id = th.asset_id AND
ah.date_effective <= th.date_effective AND
nvl(ah.date_ineffective,sysdate+1) > th.date_effective
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.asset_id = th.asset_id AND
ret.status = 'PROCESSED'
AND
bk.transaction_header_id_out = th.transaction_header_id AND
bk.book_type_code = :P_BOOK
AND
cbd.category_id = ah.category_id AND
cbd.book_type_code = th.book_type_code AND
(bk.date_placed_in_service between cbd.start_dpis and
nvl(cbd.end_dpis,sysdate)) AND
(months_between(ret.date_retired, bk.date_placed_in_service) <=
nvl(cbd.capital_gain_threshold,12))
AND
ad.asset_id = th.asset_id AND
ad.property_1245_1250_code in ('1245', '1250')
AND
cb.category_id = ad.asset_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
sign(ret.gain_loss_amount),
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'),
ad.property_1245_1250_code,
CB.ASSET_COST_ACCT,
ad.asset_number,
ad.description,
bk.date_placed_in_service,
ret.date_retired
ORDER BY
/* 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'),
Decode (Sign(ret.gain_loss_amount), -1, 'LOSS', 'GAIN'),
ad.property_1245_1250_code,
CB.ASSET_COST_ACCT,
ad.asset_number*/
1, 10, 3, 2, 4, 5, 6, 7, 8, 9, 10 |
