Reports 2017-11-18T12:27:27+00:00

FA Asset Retirements

Description
Categories: Enginatics, Financials, Oracle Standard
Application: Assets
Source: Asset Retirements Report (XML) - Not Supported: Reserved For Future Use
Short Name: FAS440_XML
DB package: FA_FAS440_XMLP_PKG

select
fnd_flex_xml_publisher_apis.process_kff_combination_1('d_comp_code', 'SQLGL', 'GL#', dhcc.chart_of_accounts_id, null, dhcc.code_combination_id, 'GL_BALANCING', 'Y', 'VALUE') d_comp_code1,
falu.meaning asset_type,
decode (ah.asset_type,'CIP', cb.cip_cost_acct,cb.asset_cost_acct) account,
fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_center', 'SQLGL', 'GL#', dhcc.chart_of_accounts_id, null, dhcc.code_combination_id, 'FA_COST_CTR', 'Y', 'VALUE') d_cost_center1,
ad.asset_number,
ret.date_retired,
ad.asset_number || ' - ' || ad.description asset_num_desc,
th.transaction_type_code,
th.asset_id,
books.date_placed_in_service,
sum(decode(aj.adjustment_type, 'COST', 1, 'CIP COST', 1, 0) *
decode(aj.debit_credit_flag, 'DR', -1, 'CR', 1, 0) *
aj.adjustment_amount)		cost,
sum(decode(aj.adjustment_type, 'NBV RETIRED', -1, 0) *
decode(aj.debit_credit_flag, 'DR', -1, 'CR', 1, 0) *
aj.adjustment_amount)		nbv,
sum(decode(aj.adjustment_type, 'PROCEEDS CLR', 1, 'PROCEEDS', 1, 0) *
decode(aj.debit_credit_flag, 'DR', 1, 'CR', -1, 0) *
aj.adjustment_amount)		proceeds,
sum(decode(aj.adjustment_type, 'REMOVALCOST', -1, 0) *
decode(aj.debit_credit_flag, 'DR', -1, 'CR', 1, 0) *
aj.adjustment_amount)		removal,
sum(decode(aj.adjustment_type,'REVAL RSV RET',1,0)*
decode(aj.debit_credit_flag, 'DR',-1,'CR',1,0)*
aj.adjustment_amount)		reval_rsv_ret,
th.transaction_header_id,
decode (th.transaction_type_code,
'REINSTATEMENT', '*','PARTIAL RETIREMENT','P',
to_char(null)) code,
fa_fas440_xmlp_pkg.gain_lossformula(sum ( decode ( aj.adjustment_type , 'NBV RETIRED' , - 1 , 0 ) * decode ( aj.debit_credit_flag , 'DR' , - 1 , 'CR' , 1 , 0 ) * aj.adjustment_amount ), sum ( decode ( aj.adjustment_type , 'PROCEEDS CLR' , 1 , 'PROCEEDS' , 1 , 0 ) * decode ( aj.debit_credit_flag , 'DR' , 1 , 'CR' , - 1 , 0 ) * aj.adjustment_amount ), sum ( decode ( aj.adjustment_type , 'REMOVALCOST' , - 1 , 0 ) * decode ( aj.debit_credit_flag , 'DR' , - 1 , 'CR' , 1 , 0 ) * aj.adjustment_amount ), sum ( decode ( aj.adjustment_type , 'REVAL RSV RET' , 1 , 0 ) * decode ( aj.debit_credit_flag , 'DR' , - 1 , 'CR' , 1 , 0 ) * aj.adjustment_amount )) gain_loss
from
fa_transaction_headers th,
fa_additions			ad,
fa_books				books,
fa_retirements			ret,
fa_adjustments			aj,
fa_distribution_history		dh,
gl_code_combinations		dhcc,
fa_asset_history			ah,
fa_category_books			cb,
fa_lookups			falu
where
1=1 and
th.book_type_code		 =  :p_book  and
th.transaction_key		= 'R'
and
ret.book_type_code		= :p_book  and
ret.asset_id		= books.asset_id		and
decode (th.transaction_type_code,
'REINSTATEMENT', ret.transaction_header_id_out,
ret.transaction_header_id_in)    = th.transaction_header_id and
ad.asset_id        = th.asset_id and
aj.asset_id        = ret.asset_id and
aj.book_type_code    = :p_book and
aj.adjustment_type not in (select  'PROCEEDS' from fa_adjustments aj1
where aj1.book_type_code = aj.book_type_code
and aj1.asset_id = aj.asset_id
and aj1.transaction_header_id = aj.transaction_header_id
and aj1.adjustment_type = 'PROCEEDS CLR') and
aj.transaction_header_id    = th.transaction_header_id and
ah.asset_id        = ad.asset_id and
ah.date_effective        <= th.date_effective and
nvl(ah.date_ineffective, th.date_effective+1)>th.date_effective and
falu.lookup_code        = ah.asset_type and
falu.lookup_type        = 'ASSET TYPE' and
books.transaction_header_id_out= th.transaction_header_id and
books.book_type_code    = :p_book and
books.asset_id        = ad.asset_id and
cb.category_id        = ah.category_id and
cb.book_type_code        = :p_book  and
dh.distribution_id    = aj.distribution_id
and th.asset_id = dh.asset_id and
dhcc.code_combination_id    = dh.code_combination_id
group by
falu.meaning,
fnd_flex_xml_publisher_apis.process_kff_combination_1('d_comp_code', 'SQLGL', 'GL#', dhcc.chart_of_accounts_id, null, dhcc.code_combination_id, 'GL_BALANCING', 'Y', 'VALUE'),
fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_center', 'SQLGL', 'GL#', dhcc.chart_of_accounts_id, null, dhcc.code_combination_id, 'FA_COST_CTR', 'Y', 'VALUE'),
th.transaction_type_code,
th.asset_id,
cb.asset_cost_acct,
cb.cip_cost_acct,
ad.asset_number,
ad.description,
books.date_placed_in_service,
ret.date_retired,
th.transaction_header_id,
ah.asset_type,
ret.gain_loss_amount
order by 1,2,3,4,8, 9, 5, 7, 10, 6, 11, 12, 13, 14, 15, 16, 17

Parameter Name SQL text Validation
Book
 
LOV Oracle
Set of Books Currency
 
LOV Oracle
From Period
th.date_effective>=(select fdp.calendar_period_open_date from fa_deprn_periods fdp where fdp.period_name=:period_from and fdp.book_type_code=:p_book)
LOV
To Period
th.date_effective<(select fdp.calendar_period_close_date+1 from fa_deprn_periods fdp where fdp.period_name=:period_from and fdp.book_type_code=:p_book)
LOV