Fixed Assets - Oracle EBS R12: FA Retirement Forms Calculation before Retirement is Processed

http://fixedasstets.blogspot.com/2016/11/fa-retirement-forms-calculation-before.html

The Below Report can be used to get Gain and Loss Amount when Asset Retired is in Pending Status

Assets Retirement Report

select *

from (SELECT /*+ ordered */

--&ACCT_FLEX_BAL_SEG comp_code,

falu.meaning asset_type,

decode(ah.asset_type, 'CIP', cb.cip_cost_acct, cb.asset_cost_acct) account,

--&ACCT_FLEX_COST_SEG cost_center,

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_Retired,

/* abs( sum(decode(aj.adjustment_type, 'PROCEEDS CLR', 1, 'PROCEEDS', 1, 0) *

decode(aj.debit_credit_flag, 'DR', 1, 'CR', -1, 0) *

aj.adjustment_amount) - RET.GAIN_LOSS_AMOUNT -

sum(decode(aj.adjustment_type, 'COST', 1, 'CIP COST', 1, 0) *

decode(aj.debit_credit_flag, 'DR', -1, 'CR', 1, 0) *

aj.adjustment_amount) ) RTRD_ACCDEPRN, */

decode(th.transaction_type_code,

'REINSTATEMENT',

-1 *

(sum(decode(aj.adjustment_type,

'PROCEEDS CLR',

1,

'PROCEEDS',

1,

0) *

decode(aj.debit_credit_flag, 'DR', 1, 'CR', -1, 0) *

aj.adjustment_amount) - (-1 * RET.GAIN_LOSS_AMOUNT) -

sum(decode(aj.adjustment_type, 'COST', 1, 'CIP COST', 1, 0) *

decode(aj.debit_credit_flag, 'DR', -1, 'CR', 1, 0) *

aj.adjustment_amount)),

-1 *

(sum(decode(aj.adjustment_type,

'PROCEEDS CLR',

1,

'PROCEEDS',

1,

0) *

decode(aj.debit_credit_flag, 'DR', 1, 'CR', -1, 0) *

aj.adjustment_amount) - RET.GAIN_LOSS_AMOUNT -

sum(decode(aj.adjustment_type, 'COST', 1, 'CIP COST', 1, 0) *

decode(aj.debit_credit_flag, 'DR', -1, 'CR', 1, 0) *

aj.adjustment_amount))

) RTRD_ACCDEPRN,

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,

decode(th.transaction_type_code,

'REINSTATEMENT',
RET.GAIN_LOSS_AMOUNT,
-1 * RET.GAIN_LOSS_AMOUNT) GAIN_LOSS_AMOUNT,

th.transaction_header_id,

decode(th.transaction_type_code,

'REINSTATEMENT',

'*',

'PARTIAL RETIREMENT',

'P',

to_char(null)) code

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

/* th.date_effective >=&PERIOD1_POD

AND th.date_effective <= &PERIOD2_PCD */

ret.DATE_RETIRED >= &PERIOD1_POD

AND ret.DATE_RETIRED <= &PERIOD2_PCD

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,

-- &ACCT_FLEX_BAL_SEG,

-- &ACCT_FLEX_COST_SEG,

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

UNION

SELECT /*+ ordered */ --added query for bug10255794

-- &ACCT_FLEX_BAL_SEG comp_code,

falu.meaning asset_type,

decode(ah.asset_type, 'CIP', cb.cip_cost_acct, CB.ASSET_COST_ACCT) ACCOUNT,

-- &ACCT_FLEX_COST_SEG cost_center,

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,

0 cost_Retired,

0 RTRD_ACCDEPRN,

0 nbv,

nvl(ret.proceeds_of_sale, 0) proceeds,

nvl(ret.cost_of_removal, 0) removal,

0 reval_rsv_ret,

decode(th.transaction_type_code,

'REINSTATEMENT',
RET.GAIN_LOSS_AMOUNT,
-1 * RET.GAIN_LOSS_AMOUNT) GAIN_LOSS_AMOUNT,

TH.TRANSACTION_HEADER_ID,

DECODE(RET.STATUS, 'DELETED', '*', to_char(null)) code

FROM fa_transaction_headers th,

fa_additions ad,

fa_books books,

fa_retirements RET,

(SELECT DH.*

FROM FA_TRANSACTION_HEADERS TH1,

FA_DISTRIBUTION_HISTORY DH,

FA_BOOK_CONTROLS BC,

FA_TRANSACTION_HEADERS TH2

WHERE TH1.BOOK_TYPE_CODE = &P_BOOK

AND TH1.TRANSACTION_TYPE_CODE = 'FULL RETIREMENT'

AND TH1.DATE_EFFECTIVE BETWEEN &PERIOD1_POD AND

&PERIOD2_PCD

AND TH1.ASSET_ID = DH.ASSET_ID

AND BC.BOOK_TYPE_CODE = th1.BOOK_TYPE_CODE

AND bC.DISTRIBUTION_SOURCE_BOOK = DH.BOOK_TYPE_CODE

AND TH1.DATE_EFFECTIVE <=

NVL(DH.DATE_INEFFECTIVE, TH1.DATE_EFFECTIVE)

AND TH1.ASSET_ID = TH2.ASSET_ID

AND TH2.BOOK_TYPE_CODE = &P_BOOK

AND th2.TRANSACTION_TYPE_CODE = 'REINSTATEMENT'

AND TH2.DATE_EFFECTIVE BETWEEN &PERIOD1_POD AND

&PERIOD2_PCD

and th2.date_effective >= dh.DATE_EFFECTIVE) dh,

gl_code_combinations dhcc,

fa_asset_history ah,

fa_category_books cb,

fa_lookups falu

WHERE

/* th.date_effective >= &PERIOD1_POD

AND TH.DATE_EFFECTIVE <= &PERIOD2_PCD*/

ret.DATE_RETIRED >= &PERIOD1_POD

AND ret.DATE_RETIRED <= &PERIOD2_PCD

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 ret.transaction_header_id_out = th.transaction_header_id

AND ad.asset_id = th.asset_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 th.asset_id = dh.asset_id

AND DHCC.CODE_COMBINATION_ID = DH.CODE_COMBINATION_ID

AND TH.TRANSACTION_TYPE_CODE = 'REINSTATEMENT'

AND RET.COST_RETIRED = 0

and ret.cost_of_removal = 0

and ret.proceeds_of_sale = 0

GROUP BY falu.meaning,

-- &ACCT_FLEX_BAL_SEG,

--&ACCT_FLEX_COST_SEG,

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,

RET.STATUS,

ret.proceeds_of_sale,

ret.cost_of_removal

)

ORDER BY TO_NUMBER(ASSET_NUMBER)

#############################################################################

For My Case

select jj.ASSET_NUMBER,

fb.DESCRIPTION,

fb.TAG_NUMBER new_asset_no,

pp.segment1 || '-' || pp.segment2 || '-' || pp.segment3 CATEGORY_CONCAT,

fl.SEGMENT1 || '-' || fl.SEGMENT2 || '-' || fl.SEGMENT3 LOCATION_CONCAT,

ak.segment1 AssetKey,

fb.CURRENT_UNITS,

fb.PROPERTY_TYPE_CODE,

zz.date_placed_in_service,

jj.DATE_RETIRED,

zz.life_in_months,

MONTHS_BETWEEN(TRUNC(jj.DATE_RETIRED, 'MONTH'),

TRUNC(zz.DATE_PLACED_IN_SERVICE, 'MONTH')) LIFE_USED,

zz.COST,

jj.COST_RETIRED,

(select sum(dd.deprn_adjustment_amount)

from fa_deprn_detail dd

where dd.book_type_code = &p_book_type

and dd.asset_id = fb.ASSET_ID

and (

(&P_DATE_RETIRED1 is null and &P_DATE_RETIRED2 is null) or

(dd.PERIOD_COUNTER between

(SELECT max(P.PERIOD_COUNTER)

FROM FA_DEPRN_PERIODS P

WHERE BOOK_TYPE_CODE = &P_BOOK_TYPE

AND CALENDAR_PERIOD_OPEN_DATE <= &P_DATE_RETIRED1

AND &P_DATE_RETIRED1 is not null)

and

(SELECT min(P.PERIOD_COUNTER)

FROM FA_DEPRN_PERIODS P

WHERE BOOK_TYPE_CODE = &P_BOOK_TYPE

AND CALENDAR_PERIOD_CLOSE_DATE >= &P_DATE_RETIRED2

AND &P_DATE_RETIRED2 is not null)))) DEPRN_ADJUSTMENT_AMOUNT,

(abs(NVL(sum(jj.PROCEEDS_OF_SALE), 0) -

nvl(sum(jj.GAIN_LOSS_AMOUNT), 0) - nvl(sum(jj.COST_RETIRED), 0))) RTRD_ACCDEPRN,

NVL(sum(jj.PROCEEDS_OF_SALE), 0) - nvl(sum(jj.GAIN_LOSS_AMOUNT), 0) -
nvl(sum(jj.COST_OF_REMOVAL), 0) RTRD_NBV,

jj.PROCEEDS_OF_SALE,

-- jj.GAIN_LOSS_AMOUNT ,

-1 * jj.GAIN_LOSS_AMOUNT GAIN_LOSS_AMOUNT, --for changing sign added on 07NOV2016

jj.RETIREMENT_TYPE_CODE MODE_OF_DISPOSAL,

jj.SOLD_TO,

C.SEGMENT4 SEGMENT4_1,

jj.ATTRIBUTE4 ATTRIBUTE4_1,

jj.ATTRIBUTE3 ATTRIBUTE3_1,

jj.RET_ATTRIBUTE1 COMMENT1,

------ACTUAL_LIFE_COMPLETED_ASSIGNED--------

ff.life_in_months -

GREATEST(NVL(ff.life_in_months, 0) -

MONTHS_BETWEEN(TRUNC(jj.DATE_RETIRED, 'MONTH'),

TRUNC(ff.DATE_PLACED_IN_SERVICE, 'MONTH')),

0

) Life_Used2

/*,

zz.ADJUSTED_COST,*/

from fa_retirements_v jj,

fa_books zz,

fa_additions fb,

fa_categories_b pp,

fa_category_books cb,

fa_distribution_history dh,

fa_locations fl,

GL_CODE_COMBINATIONS C,

fa_asset_keywords ak,

fa_books_v ff

/* (select lookup_code from fnd_lookups where lookup_type ='YES_NO'

and lookup_code= 'N')*/

where 1 = 1

and fb.ASSET_ID = zz.ASSET_ID

and jj.ASSET_ID = zz.ASSET_ID

and ff.asset_id = zz.ASSET_ID

and jj.RETIREMENT_ID = zz.RETIREMENT_ID

and zz.book_type_code = cb.BOOK_TYPE_CODE

and zz.BOOK_TYPE_CODE = dh.BOOK_TYPE_CODE

and jj.BOOK_TYPE_CODE = zz.BOOK_TYPE_CODE

and fb.ASSET_ID = jj.ASSET_ID

and zz.RETIREMENT_ID = jj.RETIREMENT_ID

and dh.ASSET_ID = zz.ASSET_ID

and ak.CODE_COMBINATION_ID = fb.ASSET_KEY_CCID

and pp.CATEGORY_ID = fb.ASSET_CATEGORY_ID

and pp.CATEGORY_id = cb.CATEGORY_ID

and cb.CATEGORY_ID = fb.ASSET_CATEGORY_ID

and c.CODE_COMBINATION_ID = dh.CODE_COMBINATION_ID

and fl.LOCATION_ID = dh.LOCATION_ID

AND DH.DATE_INEFFECTIVE IS NULL

and jj.BOOK_TYPE_CODE = &P_BOOK_TYPE

and pp.segment1 like nvl(&p_major_cat, pp.segment1)

and pp.segment2 like nvl(&p_minor_cat, pp.segment2)

and pp.segment3 like nvl(&p_type, pp.segment3)

and ak.segment1 like nvl(&p_asset_key, ak.segment1)

and nvl(fl.segment1, 'NA') like nvl(&p_city, nvl(fl.segment1, 'NA'))

and nvl(fl.segment2, 'NA') like nvl(&p_area, nvl(fl.segment2, 'NA'))

and nvl(fl.segment3, 'NA') like nvl(&p_nature, nvl(fl.segment3, 'NA'))

/* and decode(decode(&p_property_type_code,

'ALL',

&p_property_type_code,

fb.PROPERTY_TYPE_CODE),

'ALL',

'A',

'CAPITALIZE',

'CAPITALIZE',

'NONE') = decode(&p_property_type_code,

'ALL',

'A',

'CAPITALIZE',

'CAPITALIZE',

'NONE')*/ -- added on January 04, 2011

AND fb.ASSET_NUMBER = NVL(&P_ASSET_NUMBER, fb.ASSET_NUMBER)

AND jj.STATUS = 'PROCESSED'

AND trunc(jj.DATE_RETIRED) BETWEEN
NVL(&P_DATE_RETIRED1, jj.DATE_RETIRED) AND

NVL(&P_DATE_RETIRED2, jj.DATE_RETIRED)

--AND TO_DATE( &P_PERIOD ,'MON-RR') >= TRUNC(NVL(jj.DATE_RETIRED,TO_DATE( &P_PERIOD ,'MON-RR')),'MONTH')

--and Lookup_Code= &P_YES_NO

group by fb.ASSET_ID,

dh.distribution_id,

jj.ASSET_NUMBER,

zz.COST,

jj.COST_RETIRED,

jj.PROCEEDS_OF_SALE,

jj.GAIN_LOSS_AMOUNT,

zz.ADJUSTED_COST,

fb.DESCRIPTION,

fb.TAG_NUMBER,

pp.segment1 || '-' || pp.segment2 || '-' || pp.segment3,

fl.SEGMENT1 || '-' || fl.SEGMENT2 || '-' || fl.SEGMENT3,

ak.segment1,

fb.CURRENT_UNITS,

fb.PROPERTY_TYPE_CODE,

zz.date_placed_in_service,

jj.DATE_RETIRED,

zz.life_in_months,

jj.RETIREMENT_TYPE_CODE,

jj.SOLD_TO,

C.SEGMENT4,

jj.ATTRIBUTE4,

jj.ATTRIBUTE3,

jj.RET_ATTRIBUTE1,

ff.life_in_months,

MONTHS_BETWEEN(TRUNC(jj.DATE_RETIRED, 'MONTH'),

TRUNC(ff.DATE_PLACED_IN_SERVICE, 'MONTH'))

ORDER BY TO_NUMBER(JJ.ASSET_NUMBER)

Fixed Assets - Oracle EBS R12

Friday, 4 November 2016

FA Retirement Forms Calculation before Retirement is Processed - Oracle EBS R12

No comments:

Post a Comment