FA Asset Retirement Report
SELECT
al1.period,
al1.book_name,
al1.asset_category,
al1.asset_subcategory,
al1.asset_number,
al1.asset_description,
al1.country,
al1.state,
al1.city,
al1.dist_units_retired,
al1.total_units_retired,
al1.units_assigned,
al1.exp_dept,
al1.cost_retired * ( al1.dist_units_retired / al1.total_units_retired ) cost_retired,
al1.reserve_retired * ( al1.dist_units_retired / al1.total_units_retired ) reserve_retired,
al1.nbv_retired * ( al1.dist_units_retired / al1.total_units_retired ) nbv_retired,
al1.proceeds_of_sale * ( al1.dist_units_retired / al1.total_units_retired ) proceeds_of_sale,
al1.cost_of_removal * ( al1.dist_units_retired / al1.total_units_retired ) cost_of_removal,
al1.gain_loss_amt * ( al1.dist_units_retired / al1.total_units_retired ) gain_loss_amt,
al1.cost_ret_local_calc * ( al1.dist_units_retired / al1.total_units_retired ) cost_ret_local_calc,
al1.reserve_ret_local_calc * ( al1.dist_units_retired / al1.total_units_retired ) reserve_ret_local_calc,
al1.nbv_ret_local_calc * ( al1.dist_units_retired / al1.total_units_retired ) nbv_ret_local_calc,
al1.proceeds_sale_local_calc * ( al1.dist_units_retired / al1.total_units_retired ) proceeds_sale_local_calc,
al1.cost_of_removal_local_calc * ( al1.dist_units_retired / al1.total_units_retired ) cost_of_removal_local_calc,
al1.gain_loss_local_calc * ( al1.dist_units_retired / al1.total_units_retired ) gain_loss_local_calc,
to_char(al1.placed_in_service,'MM/DD/YYYY') placed_in_service,
to_char(al1.date_retired,'MM/DD/YYYY') date_retired,
to_char(al1.date_effective,'MM/DD/YYYY') date_effective,
al1.retirement_categ,
al1.retirement_type,
al1.retirement_comments,
al1.sold_to,
al1.ret_status,
al1.cost_glbl_acct,
al1.reserve_glbl_acct,
impairment_amt_attr5,
to_char(sysdate ,'mm/dd/yyyy hh:mi:ss AM') timestamp
FROM
(
SELECT
far.book_type_code book_name,
fabc.book_class book_type,
Fcb.segment1 asset_category,
Fcb.segment2 asset_subcategory,
fadb.attribute_category_code category_subcategory,
fadb.asset_number asset_number,
fadt.description asset_description,
fadb.tag_number tag_number,
glp.period_name period,
far.date_retired date_retired,
trunc(far.date_effective) date_effective,
CASE
WHEN substr(far.retirement_type_code, 1, 3) = 'ICO' THEN
'Intercompany Transfers'
WHEN falt.LOOKUP_CODE = 'SCRAPPED' THEN
'Retirements'
WHEN falt.LOOKUP_CODE = 'EXTSALE' THEN
'Sales to Third Parties'
ELSE
'Other'
END retirement_categ,
substr(far.retirement_type_code, 8, 3) receiving_company,
far.retirement_type_code retirement_type,
falt.description ret_type_description,
far.status ret_status,
glcc.segment1 exp_company,
glcc.segment3 exp_glbl_acct,
glcc.segment4 exp_local_acct,
glcc.segment5 exp_dept,
glcc.segment7 exp_interco,
glcc.segment8 exp_project,
glcc.segment6 exp_bu,
glcc.segment9 exp_future,
glc2.segment3 cost_glbl_acct,
glc2.segment4 cost_local_acct,
glc3.segment3 reserve_glbl_acct,
glc3.segment4 reserve_local_acct,
--null cost_glbl_acct,
--null cost_local_acct,
--null reserve_glbl_acct,
--null reserve_local_acct,
fask.segment1 asset_key,
far.last_update_date last_updt_date,
far.sold_to sold_to,
farc.units total_units_retired,
abs(fadh.transaction_units) dist_units_retired,
far.cost_retired cost_retired,
( nvl(far.cost_retired, 0) - nvl(far.nbv_retired, 0) ) reserve_retired,
far.nbv_retired nbv_retired,
far.cost_of_removal cost_of_removal,
far.gain_loss_amount gain_loss_amt,
far.proceeds_of_sale proceeds_of_sale,
CASE
WHEN fabc.book_class = 'TAX'
AND fabc.book_type_code NOT LIKE '%FA ENP%' THEN
0
WHEN substr(far.book_type_code, 1, 3) IN ( '612', '613', '624' ) THEN
round((far.cost_retired * rat.conversion_rate), 2)
ELSE
nvl(famr.cost_retired, far.cost_retired)
END cost_ret_local_calc,
CASE
WHEN fabc.book_class = 'TAX'
AND fabc.book_type_code NOT LIKE '%FA ENP%' THEN
0
WHEN substr(far.book_type_code, 1, 3) IN ( '612', '613', '624' ) THEN
round(((far.cost_retired - far.nbv_retired) * rat.conversion_rate), 2)
ELSE
nvl((famr.cost_retired - famr.nbv_retired),(far.cost_retired - far.nbv_retired))
END reserve_ret_local_calc,
CASE
WHEN fabc.book_class = 'TAX'
AND fabc.book_type_code NOT LIKE '%FA ENP%' THEN
0
WHEN substr(far.book_type_code, 1, 3) IN ( '612', '613', '624' ) THEN
round((far.nbv_retired * rat.conversion_rate), 2)
ELSE
nvl(famr.nbv_retired, far.nbv_retired)
END nbv_ret_local_calc,
CASE
WHEN fabc.book_class = 'TAX'
AND fabc.book_type_code NOT LIKE '%FA ENP%' THEN
0
WHEN substr(far.book_type_code, 1, 3) IN ( '612', '613', '624' ) THEN
round((far.proceeds_of_sale * rat.conversion_rate), 2)
ELSE
nvl(famr.proceeds_of_sale, far.proceeds_of_sale)
END proceeds_sale_local_calc,
CASE
WHEN fabc.book_class = 'TAX'
AND fabc.book_type_code NOT LIKE '%FA ENP%' THEN
0
WHEN substr(far.book_type_code, 1, 3) IN ( '612', '613', '624' ) THEN
round((far.cost_of_removal * rat.conversion_rate), 2)
ELSE
nvl(famr.cost_of_removal, far.cost_of_removal)
END cost_of_removal_local_calc,
CASE
WHEN fabc.book_class = 'TAX'
AND fabc.book_type_code NOT LIKE '%FA ENP%' THEN
0
WHEN substr(far.book_type_code, 1, 3) IN ( '612', '613', '624' ) THEN
round((far.gain_loss_amount * rat.conversion_rate), 2)
ELSE
nvl(famr.gain_loss_amount, far.gain_loss_amount)
END gain_loss_local_calc,
fadh.units_assigned units_assigned,
fab.date_placed_in_service placed_in_service,
--fab.life_in_months life_months,
null life_months,
fal.segment1 country,
fal.segment2 state,
fal.segment3 city,
fal.segment4 building,
fal.segment5 room,
fadb.serial_number serial_number,
fadb.manufacturer_name manufacturer_name,
fadb.model_number model_number,
thdr.transaction_name retirement_comments,
fadb.attribute5 impairment_amt_attr5
-- farc.units new_ut
FROM
fa_retirements far,
fa_retirements farc,
fa_mc_retirements famr,
fa_transaction_headers thdr,
fa_book_controls fabc,
fa_additions_b fadb,
Fa_Categories_B Fcb,
fa_asset_keywords fask,
fa_additions_tl fadt,
FND_LOOKUP_VALUES falt,
fa_distribution_history fadh,
--fa_distribution_accounts fada,
fa_category_books fcbk,
gl_code_combinations glcc,
gl_code_combinations glc2,
gl_code_combinations glc3,
fa_locations fal,
fa_books fab,
gl_daily_rates rat,
gl_ledgers lgr,
gl_ledgers lgs,
fa_deprn_periods glp
WHERE
far.book_type_code = fabc.book_type_code
AND far.asset_id = fadb.asset_id
AND far.asset_id = fadt.asset_id
AND Fadb.asset_category_id = fcb.category_id
AND far.retirement_type_code = falt.lookup_code (+)
AND far.asset_id = fadh.asset_id
AND famr.retirement_id (+) = far.retirement_id
AND fadh.code_combination_id = glcc.code_combination_id
AND fask.code_combination_id (+) = fadb.asset_key_ccid
AND fadh.location_id = fal.location_id
AND far.asset_id = fab.asset_id
AND far.book_type_code = fab.book_type_code
AND fadt.language = 'US'
AND falt.language (+) = 'US'
AND falt.lookup_type (+) = 'FA_RETIREMENT_TYPE'
AND fadh.retirement_id = (
CASE
WHEN fabc.book_class = 'CORPORATE' THEN
far.retirement_id
ELSE
(
SELECT
fr.retirement_id
FROM
fa_retirements fr,
fa_transaction_headers fth
WHERE
fth.transaction_header_id = fr.transaction_header_id_in
AND fth.transaction_header_id = thdr.source_transaction_header_id
)
END
)
AND farc.retirement_id = (
CASE
WHEN fabc.book_class = 'CORPORATE' THEN
far.retirement_id
ELSE
(
SELECT
fr.retirement_id
FROM
fa_retirements fr,
fa_transaction_headers fth
WHERE
fth.transaction_header_id = fr.transaction_header_id_in
AND fth.transaction_header_id = thdr.source_transaction_header_id
)
END
)
/*AND Fadh.Date_Ineffective IS NULL*/
AND fab.date_ineffective IS NULL
AND far.date_effective BETWEEN glp.period_open_date AND nvl(glp.period_close_date,(sysdate + 1))
AND far.book_type_code = glp.book_type_code
--AND fada.distribution_id = fadh.distribution_id
--AND fada.book_type_code = far.book_type_code
AND fcbk.category_id = fadb.asset_category_id
AND fcbk.book_type_code = fab.book_type_code
--AND glc2.code_combination_id = fada.asset_cost_account_ccid
--AND glc3.code_combination_id = fada.deprn_reserve_account_ccid
AND glc2.code_combination_id = fcbk.asset_cost_account_ccid
AND glc3.code_combination_id = fcbk.reserve_account_ccid
AND far.status = 'PROCESSED'
AND rat.conversion_type = 'Corporate'
AND rat.from_currency = decode(lgr.currency_code, 'USD', 'BMD', lgr.currency_code)
AND rat.to_currency = 'USD'
AND rat.conversion_date = trunc(far.date_effective)
AND lgr.ledger_id = fabc.set_of_books_id
AND thdr.transaction_header_id = far.transaction_header_id_in
AND lgs.ledger_category_code (+) = 'PRIMARY'
AND substr(lgs.name(+), 1, 3) = substr(far.retirement_type_code, 8, 3)
AND glp.period_name = nvl(:p_period_name,glp.period_name)
AND far.book_type_code = nvl(:p_book_type_code,far.book_type_code)
-- AND far.status = 'DELETED'
) al1
ORDER BY
al1.period,
al1.book_name,
al1.asset_category,
al1.asset_subcategory,
al1.asset_number
No comments:
Post a Comment