SELECT
nvl(&ACCT_FLEX_BAL_SEG, 'None') 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.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),
nvl(&ACCT_FLEX_BAL_SEG, 'None'),
ad.property_1245_1250_code,
cb.asset_cost_acct,
ad.asset_number,
ad.description,
bk.date_placed_in_service,
ret.date_retired,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
nvl(&ACCT_FLEX_BAL_SEG, 'None'),
ad.property_1245_1250_code,
cb.asset_cost_acct,
lpad(ad.asset_number,15,0)*/
ORDER BY comp_code,GAIN_loss,class,account,ASSET_NUM,DPIS,DATE_SOLD,PROCEEDS,
RESERVE,BASIS,GAIN |
