Fixed Assets - Oracle EBS R12: 2016

Friday, 4 November 2016

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

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)

Remaining Life of an Asset for STL Method - Oracle EBS R12

http://fixedasstets.blogspot.com/2016/11/select-decode-books.html

PLSQL Query to Get Remaining Life of an Asset

1st Query

SELECT fb.ASSET_NUMBER,

fab.BOOK_TYPE_CODE

/* ,

decode(fab.conversion_date,

NULL,

fab.life_in_months -

floor(months_between(fdp.calendar_period_close_date,

fab.prorate_date)),

fab.life_in_months -

floor(months_between(fdp.calendar_period_close_date,

fab.deprn_start_date))) REM_LIFE_IN_MON*/

-----------------------------------

,

(SELECT

case

when decode(faab.conversion_date,

NULL,

faab.life_in_months -

floor(months_between(fdpp.calendar_period_close_date,

faab.prorate_date)),

faab.life_in_months -

floor(months_between(fdpp.calendar_period_close_date,

faab.deprn_start_date))) <= 0 then

0

else

decode(faab.conversion_date,

NULL,

faab.life_in_months -

floor(months_between(fdpp.calendar_period_close_date,

faab.prorate_date)),

faab.life_in_months -

floor(months_between(fdpp.calendar_period_close_date,

faab.deprn_start_date)))

end

FROM fa_books faab, fa_deprn_periods fdpp, fa_additions_b fbb

where faab.ASSET_ID = fbb.ASSET_ID

and faab.date_ineffective IS NULL

AND fdpp.period_counter =

(SELECT MAX(dpp.period_counter)

FROM fa_deprn_periods dpp

where dpp.BOOK_TYPE_CODE = fdpp.BOOK_TYPE_CODE

)

and faab.ASSET_ID = fab.ASSET_ID

and fb.ASSET_ID = fbb.ASSET_ID

and faab.BOOK_TYPE_CODE = fab.BOOK_TYPE_CODE

and fdp.PERIOD_COUNTER = fdpp.PERIOD_COUNTER

and fdp.BOOK_TYPE_CODE = fdpp.BOOK_TYPE_CODE

-- and fbb.ASSET_ID = b.ASSET_ID

--and b.BOOK_TYPE_CODE = fdpp.BOOK_TYPE_CODE

) REM_LIFE_IN_MON

------------------------------------------

,

TRUNC((SELECT

case

when decode(faab.conversion_date,

NULL,

faab.life_in_months -

floor(months_between(fdpp.calendar_period_close_date,

faab.prorate_date)),

faab.life_in_months -

floor(months_between(fdpp.calendar_period_close_date,

faab.deprn_start_date))) <= 0 then

0

else

decode(faab.conversion_date,

NULL,

faab.life_in_months -

floor(months_between(fdpp.calendar_period_close_date,

faab.prorate_date)),

faab.life_in_months -

floor(months_between(fdpp.calendar_period_close_date,

faab.deprn_start_date)))

end

FROM fa_books faab,

fa_deprn_periods fdpp,

fa_additions_b fbb

where faab.ASSET_ID = fbb.ASSET_ID

and faab.date_ineffective IS NULL

AND fdpp.period_counter =

(SELECT MAX(dpp.period_counter)

FROM fa_deprn_periods dpp

where dpp.BOOK_TYPE_CODE = fdpp.BOOK_TYPE_CODE

)

and faab.ASSET_ID = fab.ASSET_ID

and fb.ASSET_ID = fbb.ASSET_ID

and faab.BOOK_TYPE_CODE = fab.BOOK_TYPE_CODE

and fdp.PERIOD_COUNTER = fdpp.PERIOD_COUNTER

and fdp.BOOK_TYPE_CODE = fdpp.BOOK_TYPE_CODE

-- and fbb.ASSET_ID = b.ASSET_ID

--and b.BOOK_TYPE_CODE = fdpp.BOOK_TYPE_CODE

) / 12) YEARS

---------------------------------

,

(SELECT

case

when decode(faab.conversion_date,

NULL,

faab.life_in_months -

floor(months_between(fdpp.calendar_period_close_date,

faab.prorate_date)),

faab.life_in_months -

floor(months_between(fdpp.calendar_period_close_date,

faab.deprn_start_date))) <= 0 then

0

else

decode(faab.conversion_date,

NULL,

faab.life_in_months -

floor(months_between(fdpp.calendar_period_close_date,

faab.prorate_date)),

faab.life_in_months -

floor(months_between(fdpp.calendar_period_close_date,

faab.deprn_start_date)))

end

FROM fa_books faab, fa_deprn_periods fdpp, fa_additions_b fbb

where faab.ASSET_ID = fbb.ASSET_ID

and faab.date_ineffective IS NULL

AND fdpp.period_counter =

(SELECT MAX(dpp.period_counter)

FROM fa_deprn_periods dpp

where dpp.BOOK_TYPE_CODE = fdpp.BOOK_TYPE_CODE

)

and faab.ASSET_ID = fab.ASSET_ID

and fb.ASSET_ID = fbb.ASSET_ID

and faab.BOOK_TYPE_CODE = fab.BOOK_TYPE_CODE

and fdp.PERIOD_COUNTER = fdpp.PERIOD_COUNTER

and fdp.BOOK_TYPE_CODE = fdpp.BOOK_TYPE_CODE

-- and fbb.ASSET_ID = b.ASSET_ID

--and b.BOOK_TYPE_CODE = fdpp.BOOK_TYPE_CODE

) - TRUNC((SELECT

case

when decode(faab.conversion_date,

NULL,

faab.life_in_months -

floor(months_between(fdpp.calendar_period_close_date,

faab.prorate_date)),

faab.life_in_months -

floor(months_between(fdpp.calendar_period_close_date,

faab.deprn_start_date))) <= 0 then

0

else

decode(faab.conversion_date,

NULL,

faab.life_in_months -

floor(months_between(fdpp.calendar_period_close_date,

faab.prorate_date)),

faab.life_in_months -

floor(months_between(fdpp.calendar_period_close_date,

faab.deprn_start_date)))

end

FROM fa_books faab,

fa_deprn_periods fdpp,

fa_additions_b fbb

where faab.ASSET_ID = fbb.ASSET_ID

and faab.date_ineffective IS NULL

AND fdpp.period_counter =

(SELECT MAX(dpp.period_counter)

FROM fa_deprn_periods dpp

where dpp.BOOK_TYPE_CODE = fdpp.BOOK_TYPE_CODE

)

and faab.ASSET_ID = fab.ASSET_ID

and fb.ASSET_ID = fbb.ASSET_ID

and faab.BOOK_TYPE_CODE = fab.BOOK_TYPE_CODE

and fdp.PERIOD_COUNTER = fdpp.PERIOD_COUNTER

and fdp.BOOK_TYPE_CODE = fdpp.BOOK_TYPE_CODE

-- and fbb.ASSET_ID = b.ASSET_ID

--and b.BOOK_TYPE_CODE = fdpp.BOOK_TYPE_CODE

) / 12) * 12 MONTHS

-----------------------------------

/*, TRUNC(decode(fab.conversion_date,

NULL,

fab.life_in_months -

floor(months_between(fdp.calendar_period_close_date,

fab.prorate_date)),

fab.life_in_months -

floor(months_between(fdp.calendar_period_close_date,

fab.deprn_start_date))) / 12) YEARS



---------------------

,

decode(fab.conversion_date,

NULL,

fab.life_in_months -

floor(months_between(fdp.calendar_period_close_date,

fab.prorate_date)),

fab.life_in_months -

floor(months_between(fdp.calendar_period_close_date,

fab.deprn_start_date))) -



TRUNC(decode(fab.conversion_date,

NULL,

fab.life_in_months -

floor(months_between(fdp.calendar_period_close_date,

fab.prorate_date)),

fab.life_in_months -

floor(months_between(fdp.calendar_period_close_date,

fab.deprn_start_date))) / 12) * 12 MONTHS*/

-------------------------------------

,

(select a.TRANSACTION_TYPE_CODE

from fa_transaction_history_trx_v a

where a.ASSET_ID = fab.ASSET_ID

and a.BOOK_TYPE_CODE = fab.BOOK_TYPE_CODE

and a.CATEGORY_ID = fb.ASSET_CATEGORY_ID

and a.ASSET_ID = fb.ASSET_ID

and a.BOOK_TYPE_CODE = fdp.BOOK_TYPE_CODE

and a.TRANSACTION_TYPE_CODE = 'FULL RETIREMENT') FULL_RETIREMENT

--------------------

FROM fa_books fab, fa_deprn_periods fdp, fa_additions_b fb

where fab.ASSET_ID = fb.ASSET_ID

and fab.date_ineffective IS NULL

AND fdp.period_counter = (SELECT MAX(dp.period_counter)

FROM fa_deprn_periods dp

where dp.BOOK_TYPE_CODE = fdp.BOOK_TYPE_CODE

and dp.BOOK_TYPE_CODE = fab.BOOK_TYPE_CODE

)

order by to_number(fb.asset_number)

--------------------------------------------------------------------------------------------------------------------------

2nd Query

select fab.BOOK_TYPE_CODE,

fb.ASSET_ID,

fb.ASSET_NUMBER,

fab.DATE_PLACED_IN_SERVICE,

to_char(fab.DATE_PLACED_IN_SERVICE, 'MON-YY') MONTHS_PLACED_IN_SERVICE,

fdp.PERIOD_NAME LAST_DEPRN_RUN,

to_char(ADD_MONTHS((LAST_DAY(fab.DATE_PLACED_IN_SERVICE) + 1), -1),

'DD-MON-YY') START_DATE_PLACED_IN_SERVICE,

TO_CHAR(last_day(fab.DATE_PLACED_IN_SERVICE), 'DD-MON-YY') END_DATE_PLACED_IN_SERVICE,

to_char(ADD_MONTHS((LAST_DAY('01-' || fdp.PERIOD_NAME) + 1), -1),

'DD-MON-YY') START_DATE_DEPRN_RUN,

TO_CHAR(last_day('01-' || fdp.PERIOD_NAME), 'DD-MON-YY') END_DATE_DEPRN_RUN

--,MONTHS_BETWEEN(last_day( '01-'||fdp.PERIOD_NAME),fab.DATE_PLACED_IN_SERVICE) LIFE_USED_IN_MONTHS

,

fab.LIFE_IN_MONTHS,

/*TRUNC(MONTHS_BETWEEN(last_day('01-' || fdp.PERIOD_NAME),

fab.DATE_PLACED_IN_SERVICE)) LIFE_USED_IN_MONTHS,*/

----------------------------------------------------------------

NVL(MONTHS_BETWEEN((select trunc(v.DATE_RETIRED, 'MONTH')

from fa_retirements_v v

where v.ASSET_ID = fb.ASSET_ID

and v.BOOK_TYPE_CODE = fab.BOOK_TYPE_CODE

and v.ASSET_ID = fb.ASSET_ID

and v.TRANSACTION_HEADER_ID_IN =

fab.TRANSACTION_HEADER_ID_IN),

trunc(fab.DATE_PLACED_IN_SERVICE, 'MONTH'))

,

TRUNC(MONTHS_BETWEEN(last_day('01-' || fdp.PERIOD_NAME),

fab.DATE_PLACED_IN_SERVICE)) + 1

/* CEIL(MONTHS_BETWEEN(last_day('01-' || fdp.PERIOD_NAME),

fab.DATE_PLACED_IN_SERVICE))*/

) TOTAL_LIFE_USED_IN_MONTHS

------------------------------------------------------

,

NVL(fab.life_in_months -

GREATEST(NVL(fab.life_in_months, 0) -

MONTHS_BETWEEN((select trunc(v.DATE_RETIRED, 'MONTH')

from fa_retirements_v v

where v.ASSET_ID = fb.ASSET_ID

and v.BOOK_TYPE_CODE =

fab.BOOK_TYPE_CODE

and v.ASSET_ID = fb.ASSET_ID

and v.TRANSACTION_HEADER_ID_IN =

fab.TRANSACTION_HEADER_ID_IN),

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

0

),

TRUNC(MONTHS_BETWEEN(last_day('01-' || fdp.PERIOD_NAME),

fab.DATE_PLACED_IN_SERVICE)) + 1

/*

CEIL(MONTHS_BETWEEN(last_day('01-' || fdp.PERIOD_NAME),

fab.DATE_PLACED_IN_SERVICE))*/

) ACTUAL_LIFE_COMPLETED

-----------------------------------

/* ,

(

SELECT decode(faab.conversion_date,

NULL,

faab.life_in_months -

floor(months_between(fdpp.calendar_period_close_date,

faab.prorate_date)),

faab.life_in_months -

floor(months_between(fdpp.calendar_period_close_date,

faab.deprn_start_date)))

FROM fa_books faab, fa_deprn_periods fdpp, fa_additions_b fbb

where faab.ASSET_ID = fbb.ASSET_ID

and faab.date_ineffective IS NULL

AND fdpp.period_counter =

(SELECT MAX(dpp.period_counter)

FROM fa_deprn_periods dpp

where dpp.BOOK_TYPE_CODE = fdpp.BOOK_TYPE_CODE

)

and faab.ASSET_ID = fab.ASSET_ID

and fb.ASSET_ID = fbb.ASSET_ID

and faab.BOOK_TYPE_CODE = fab.BOOK_TYPE_CODE

and fbb.ASSET_ID = b.ASSET_ID

and b.BOOK_TYPE_CODE = fdpp.BOOK_TYPE_CODE

) REM_LIFE_IN_MON*/

--------------------------------------------------------

,

(SELECT

case

when decode(faab.conversion_date,

NULL,

faab.life_in_months -

floor(months_between(fdpp.calendar_period_close_date,

faab.prorate_date)),

faab.life_in_months -

floor(months_between(fdpp.calendar_period_close_date,

faab.deprn_start_date))) <= 0 then

0

else

decode(faab.conversion_date,

NULL,

faab.life_in_months -

floor(months_between(fdpp.calendar_period_close_date,

faab.prorate_date)),

faab.life_in_months -

floor(months_between(fdpp.calendar_period_close_date,

faab.deprn_start_date)))

end

FROM fa_books faab, fa_deprn_periods fdpp, fa_additions_b fbb

where faab.ASSET_ID = fbb.ASSET_ID

and faab.date_ineffective IS NULL

AND fdpp.period_counter =

(SELECT MAX(dpp.period_counter)

FROM fa_deprn_periods dpp

where dpp.BOOK_TYPE_CODE = fdpp.BOOK_TYPE_CODE

)

and faab.ASSET_ID = fab.ASSET_ID

and fb.ASSET_ID = fbb.ASSET_ID

and faab.BOOK_TYPE_CODE = fab.BOOK_TYPE_CODE

and fbb.ASSET_ID = b.ASSET_ID

and b.BOOK_TYPE_CODE = fdpp.BOOK_TYPE_CODE

) REM_LIFE_IN_MON

--------------------------------------

/*, ( SELECT

case when a.TRANSACTION_TYPE_CODE = 'FULL RETIREMENT' then 'FULL RETIREMENT'

when decode(faab.conversion_date,

NULL,

faab.life_in_months -

floor(months_between(fdpp.calendar_period_close_date,

faab.prorate_date)),

faab.life_in_months -

floor(months_between(fdpp.calendar_period_close_date,

faab.deprn_start_date))) <= 0 then decode((select a.TRANSACTION_TYPE_CODE

from fa_transaction_history_trx_v a

where a.ASSET_ID = fab.ASSET_ID

and a.BOOK_TYPE_CODE = fab.BOOK_TYPE_CODE

and a.CATEGORY_ID = fb.ASSET_CATEGORY_ID

and a.ASSET_ID = fb.ASSET_ID

and a.ASSET_ID = b.ASSET_ID

and a.BOOK_TYPE_CODE = b.BOOK_TYPE_CODE

and a.BOOK_TYPE_CODE = fdp.BOOK_TYPE_CODE

and a.TRANSACTION_TYPE_CODE = 'FULL RETIREMENT'

) , 'FULL RETIREMENT', 'FULL RETIREMENT', 'FULLY DEPRECIATED')

else null

end

FROM fa_books faab, fa_deprn_periods fdpp, fa_additions_b fbb

where faab.ASSET_ID = fbb.ASSET_ID

and faab.date_ineffective IS NULL

AND fdpp.period_counter =

(SELECT MAX(dpp.period_counter)

FROM fa_deprn_periods dpp

where dpp.BOOK_TYPE_CODE = fdpp.BOOK_TYPE_CODE

)

and faab.ASSET_ID = fab.ASSET_ID

and fb.ASSET_ID = fbb.ASSET_ID

and faab.BOOK_TYPE_CODE = fab.BOOK_TYPE_CODE

and fbb.ASSET_ID = b.ASSET_ID

and b.BOOK_TYPE_CODE = fdpp.BOOK_TYPE_CODE

) STATUS*/

-------------------------------------

,

(select a.TRANSACTION_TYPE_CODE

from fa_transaction_history_trx_v a

where a.ASSET_ID = fab.ASSET_ID

and a.BOOK_TYPE_CODE = fab.BOOK_TYPE_CODE

and a.CATEGORY_ID = fb.ASSET_CATEGORY_ID

and a.ASSET_ID = fb.ASSET_ID

and a.ASSET_ID = b.ASSET_ID

and a.BOOK_TYPE_CODE = b.BOOK_TYPE_CODE

and a.BOOK_TYPE_CODE = fdp.BOOK_TYPE_CODE

and a.TRANSACTION_TYPE_CODE = 'FULL RETIREMENT') FULL_RETIREMENT

--------------------------

,

TRUNC((SELECT

case

when decode(faab.conversion_date,

NULL,

faab.life_in_months -

floor(months_between(fdpp.calendar_period_close_date,

faab.prorate_date)),

faab.life_in_months -

floor(months_between(fdpp.calendar_period_close_date,

faab.deprn_start_date))) <= 0 then

0

else

decode(faab.conversion_date,

NULL,

faab.life_in_months -

floor(months_between(fdpp.calendar_period_close_date,

faab.prorate_date)),

faab.life_in_months -

floor(months_between(fdpp.calendar_period_close_date,

faab.deprn_start_date)))

end

FROM fa_books faab,

fa_deprn_periods fdpp,

fa_additions_b fbb

where faab.ASSET_ID = fbb.ASSET_ID

and faab.date_ineffective IS NULL

AND fdpp.period_counter =

(SELECT MAX(dpp.period_counter)

FROM fa_deprn_periods dpp

where dpp.BOOK_TYPE_CODE = fdpp.BOOK_TYPE_CODE

)

and faab.ASSET_ID = fab.ASSET_ID

and fb.ASSET_ID = fbb.ASSET_ID

and faab.BOOK_TYPE_CODE = fab.BOOK_TYPE_CODE

and fbb.ASSET_ID = b.ASSET_ID

and b.BOOK_TYPE_CODE = fdpp.BOOK_TYPE_CODE

-- and fbb.ASSET_ID = b.ASSET_ID

--and b.BOOK_TYPE_CODE = fdpp.BOOK_TYPE_CODE

) / 12) YEARS

---------------------------------

,

(SELECT

case

when decode(faab.conversion_date,

NULL,

faab.life_in_months -

floor(months_between(fdpp.calendar_period_close_date,

faab.prorate_date)),

faab.life_in_months -

floor(months_between(fdpp.calendar_period_close_date,

faab.deprn_start_date))) <= 0 then

0

else

decode(faab.conversion_date,

NULL,

faab.life_in_months -

floor(months_between(fdpp.calendar_period_close_date,

faab.prorate_date)),

faab.life_in_months -

floor(months_between(fdpp.calendar_period_close_date,

faab.deprn_start_date)))

end

FROM fa_books faab, fa_deprn_periods fdpp, fa_additions_b fbb

where faab.ASSET_ID = fbb.ASSET_ID

and faab.date_ineffective IS NULL

AND fdpp.period_counter =

(SELECT MAX(dpp.period_counter)

FROM fa_deprn_periods dpp

where dpp.BOOK_TYPE_CODE = fdpp.BOOK_TYPE_CODE

)

and faab.ASSET_ID = fab.ASSET_ID

and fb.ASSET_ID = fbb.ASSET_ID

and faab.BOOK_TYPE_CODE = fab.BOOK_TYPE_CODE

and fbb.ASSET_ID = b.ASSET_ID

and b.BOOK_TYPE_CODE = fdpp.BOOK_TYPE_CODE

-- and fbb.ASSET_ID = b.ASSET_ID

--and b.BOOK_TYPE_CODE = fdpp.BOOK_TYPE_CODE

) - TRUNC((SELECT

case

when decode(faab.conversion_date,

NULL,

faab.life_in_months -

floor(months_between(fdpp.calendar_period_close_date,

faab.prorate_date)),

faab.life_in_months -

floor(months_between(fdpp.calendar_period_close_date,

faab.deprn_start_date))) <= 0 then

0

else

decode(faab.conversion_date,

NULL,

faab.life_in_months -

floor(months_between(fdpp.calendar_period_close_date,

faab.prorate_date)),

faab.life_in_months -

floor(months_between(fdpp.calendar_period_close_date,

faab.deprn_start_date)))

end

FROM fa_books faab,

fa_deprn_periods fdpp,

fa_additions_b fbb

where faab.ASSET_ID = fbb.ASSET_ID

and faab.date_ineffective IS NULL

AND fdpp.period_counter =

(SELECT MAX(dpp.period_counter)

FROM fa_deprn_periods dpp

where dpp.BOOK_TYPE_CODE = fdpp.BOOK_TYPE_CODE

)

and faab.ASSET_ID = fab.ASSET_ID

and fb.ASSET_ID = fbb.ASSET_ID

and faab.BOOK_TYPE_CODE = fab.BOOK_TYPE_CODE

and fbb.ASSET_ID = b.ASSET_ID

and b.BOOK_TYPE_CODE = fdpp.BOOK_TYPE_CODE

-- and fbb.ASSET_ID = b.ASSET_ID

--and b.BOOK_TYPE_CODE = fdpp.BOOK_TYPE_CODE

) / 12) * 12 MONTHS

------------------------------

FROM fa_books fab,

fa_deprn_periods fdp,

fa_additions_b fb,

fa_deprn_summary b

where fab.ASSET_ID = fb.ASSET_ID

and b.ASSET_ID = fab.ASSET_ID

and b.BOOK_TYPE_CODE = fab.BOOK_TYPE_CODE

and b.PERIOD_COUNTER = fdp.PERIOD_COUNTER

and fab.date_ineffective IS NULL

AND fdp.period_counter =

(SELECT MAX(dp.period_counter)

FROM fa_deprn_summary dp

where dp.ASSET_ID = fab.ASSET_ID

and dp.ASSET_ID = fb.ASSET_ID

and dp.BOOK_TYPE_CODE = fab.BOOK_TYPE_CODE

and fdp.BOOK_TYPE_CODE = dp.BOOK_TYPE_CODE)

order by to_number(fb.ASSET_NUMBER), fab.BOOK_TYPE_CODE

--------------------------------------------------------------------------------------------------------------------------

For My Case When Asset is Retired (Retired Assets Remaining Life Between Date Retired and Date Placed in Service)

SELECT books.book_type_code,
c.ASSET_NUMBER,

books.life_in_months,

GREATEST(NVL(books.life_in_months, 0) -

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

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

0

) Total_remaining_life_in_months,

TRUNC(GREATEST(NVL(books.life_in_months, 0) -

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

TRUNC(books.DATE_PLACED_IN_SERVICE,

'MONTH')),

0

) / 12) remaining_life_in_years,

--1756 --128980

---------------

GREATEST(NVL(books.life_in_months, 0) -

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

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

0

) -

TRUNC(GREATEST(NVL(books.life_in_months, 0) -

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

TRUNC(books.DATE_PLACED_IN_SERVICE,

'MONTH')),

0

) / 12) * 12 Remainig_Life_In_Months

-----------------

,

(select a.TRANSACTION_TYPE_CODE

from fa_transaction_history_trx_v a

where a.ASSET_ID = tt.ASSET_ID

and a.BOOK_TYPE_CODE = tt.BOOK_TYPE_CODE

and a.TRANSACTION_HEADER_ID = tt.TRANSACTION_HEADER_ID_IN) TYPE

-------------------

FROM fa_books_V books, fa_retirements_v tt, fa_additions_b c

where tt.ASSET_ID = books.ASSET_ID

and c.ASSET_ID = books.asset_id

and books.book_type_code = tt.BOOK_TYPE_CODE

--and books.asset_id=2555

order by to_number(c.asset_number)