Fixed Assets - Oracle EBS R12

Thursday, 5 October 2017

FA Projections Report (Depreciation Projection Report) - Oracle EBS R12

https://fixedasstets.blogspot.com/2017/10/fa-projections-report-depreciation.html

The Below PLSQL Query is useful for Depreciation Forecasting

Please run Projections before running the below Query. Navigation Depreciation>Projections

SELECT

pi.BOOK_TYPE_CODE,
ad.ASSET_NUMBER,
pi.period_name,
pi.fiscal_year,
cc.SEGMENT1 || '-' || cc.SEGMENT2 || '-' || cc.SEGMENT3 || '-' ||
cc.SEGMENT4 Expense_Account,
sum(pi.DEPRECIATION)

FROM fa_system_controls,
fa_proj_interim_rpt pi,
fa_additions ad,
gl_code_combinations cc

WHERE pi.code_combination_id = cc.code_combination_id
AND pi.asset_id = ad.asset_id
AND pi.request_id in (select max(request_id) from fa_proj_interim_rpt)
AND pi.BOOK_TYPE_CODE = &Book

GROUP BY pi.BOOK_TYPE_CODE,
ad.ASSET_NUMBER,
pi.fiscal_year,
pi.period_index,
pi.period_name,
cc.SEGMENT1,
cc.SEGMENT2,
cc.SEGMENT3,
cc.SEGMENT4

ORDER BY pi.fiscal_year,
pi.period_index,
pi.period_name

Wednesday, 4 October 2017

Journal Entry Reserve Ledger Report / Fixed Assets Register - Oracle EBS R12

https://fixedasstets.blogspot.com/2017/10/journal-entry-reserve-ledger-report-or.html

The Below PLSQL Query is Useful to get Asset Cost, Accumulated Depreciation, NBV and Charge Account of Assets.

Please run Standard Oracle Report "Journal Entry Reserve Ledger Report" in Fixed Assets before running the below Query

SELECT

AD.ASSET_NUMBER,
AD.DESCRIPTION,
DATE_PLACED_IN_SERVICE,
FC.SEGMENT1,
fc.SEGMENT2,
fc.SEGMENT3,

(select GCC1.SEGMENT1 || '-' || GCC1.SEGMENT2 || '-' || GCC1.SEGMENT3 || '-' ||
GCC1.SEGMENT4 || '-' || GCC1.SEGMENT5 || '-' || GCC1.SEGMENT6 || '-' ||
GCC1.SEGMENT7 || '-' || GCC1.SEGMENT8
from GL_CODE_COMBINATIONS gcc1
where gcc1.CODE_COMBINATION_ID = FCB.ASSET_COST_ACCOUNT_CCID) CATG_ASSET_COST_ACCOUNT,

(select GCC2.SEGMENT1 || '-' || GCC2.SEGMENT2 || '-' || GCC2.SEGMENT3 || '-' ||
GCC2.SEGMENT4 || '-' || GCC2.SEGMENT5 || '-' || GCC2.SEGMENT6 || '-' ||
GCC2.SEGMENT7 || '-' || GCC2.SEGMENT8
from GL_CODE_COMBINATIONS gcc2
where gcc2.CODE_COMBINATION_ID = FCB.ASSET_CLEARING_ACCOUNT_CCID) CATG_ASSET_CLEARING_ACCOUNT,

(select GCC3.SEGMENT1 || '-' || GCC3.SEGMENT2 || '-' || GCC3.SEGMENT3 || '-' ||
GCC3.SEGMENT4 || '-' || GCC3.SEGMENT5 || '-' || GCC3.SEGMENT6 || '-' ||
GCC3.SEGMENT7 || '-' || GCC3.SEGMENT8
from GL_CODE_COMBINATIONS gcc3
where GCC3.CODE_COMBINATION_ID = FCB.DEPRN_EXPENSE_ACCOUNT_CCID) CATG_DEPRN_EXPENSE_ACCOUNT,

(select GCC4.SEGMENT1 || '-' || GCC4.SEGMENT2 || '-' || GCC4.SEGMENT3 || '-' ||
GCC4.SEGMENT4 || '-' || GCC4.SEGMENT5 || '-' || GCC4.SEGMENT6 || '-' ||
GCC4.SEGMENT7 || '-' || GCC4.SEGMENT8
from GL_CODE_COMBINATIONS gcc4
where gcc4.CODE_COMBINATION_ID = FCB.RESERVE_ACCOUNT_CCID) CATG_DEPRN_RESERVE_ACCT,

(select GCC5.SEGMENT1 || '-' || GCC5.SEGMENT2 || '-' || GCC5.SEGMENT3 || '-' ||
GCC5.SEGMENT4 || '-' || GCC5.SEGMENT5 || '-' || GCC5.SEGMENT6 || '-' ||
GCC5.SEGMENT7 || '-' || GCC5.SEGMENT8
from FA_DISTRIBUTION_HISTORY dh, gl_code_combinations GCC5
where dh.CODE_COMBINATION_ID = GCC5.CODE_COMBINATION_ID
and dh.ASSET_ID = AD.ASSET_ID
and dh.DATE_INEFFECTIVE is null
and dh.BOOK_TYPE_CODE = &Book) DIST_DEPRN_EXPENSE_ACCOUNT,
METHOD_CODE METHOD,
RSV.LIFE LIFE,
RSV.RATE ADJ_RATE,
DS.BONUS_RATE,
RSV.CAPACITY,
SUM(decode(transaction_type, 'B', NULL, COST)) COST,
SUM(RSV.DEPRN_AMOUNT) DEPRN_AMOUNT,
SUM(RSV.YTD_DEPRN) YTD_DEPRN,
SUM(RSV.DEPRN_RESERVE) DEPRN_RESERVE,
SUM(decode(transaction_type, 'B', NULL, COST)) - SUM(RSV.DEPRN_RESERVE) NBV,
sum(decode(transaction_type, 'B', NULL, nvl(PERCENT, 0))) PERCENT,
TRANSACTION_TYPE T_TYPE

FROM fa_reserve_ledger_itf RSV,
fa_deprn_periods g,
FA_ADDITIONS AD,
GL_CODE_COMBINATIONS CC,
fa_deprn_summary DS,
FA_CATEGORY_BOOKS FCB,
FA_CATEGORIES FC

WHERE RSV.ASSET_ID = AD.ASSET_ID
AND RSV.DH_CCID = CC.CODE_COMBINATION_ID
AND g.PERIOD_COUNTER = RSV.PERIOD_COUNTER
AND g.BOOK_TYPE_CODE = '&BOOK'
AND DS.PERIOD_COUNTER(+) = RSV.PERIOD_COUNTER
AND DS.BOOK_TYPE_CODE(+) = &Book
AND DS.ASSET_ID(+) = RSV.ASSET_ID
AND FCB.CATEGORY_ID = AD.ASSET_CATEGORY_ID
AND FC.CATEGORY_ID = FCB.CATEGORY_ID
AND FC.CATEGORY_ID = AD.ASSET_CATEGORY_ID
AND FCB.BOOK_TYPE_CODE = '&Book'
AND RSV.REQUEST_ID in
(select max(s.request_id) from fa_reserve_ledger_itf s)

GROUP BY FCB.ASSET_COST_ACCOUNT_CCID,
FCB.ASSET_CLEARING_ACCOUNT_CCID,
FCB.DEPRN_EXPENSE_ACCOUNT_CCID,
FCB.RESERVE_ACCOUNT_CCID,
AD.ASSET_ID,
FC.SEGMENT1,
fc.SEGMENT2,
fc.SEGMENT3,
AD.ASSET_NUMBER,
AD.DESCRIPTION,
DATE_PLACED_IN_SERVICE,
METHOD_CODE,
RSV.LIFE,
RSV.RATE,
RSV.CAPACITY,
DS.BONUS_RATE,
TRANSACTION_TYPE

ORDER BY AD.ASSET_NUMBER

Tuesday, 3 October 2017

APP-OFA-47333: You Cannot Perform this transaction because the asset is fully retired. Choose a book in which you have not yet retired the asset, or reinstate the asset, and then perform the transaction. If you wish to cancel or undo the retirement, Please query it first.

https://fixedasstets.blogspot.com/2017/10/app-ofa-47333-you-cannot-perform-this.html

ERROR

APP-OFA-47333: You Cannot Perform this transaction because the asset is fully retired. Choose a book in which you have not yet retired the asset, or reinstate the asset, and then perform the transaction. If you wish to cancel or undo the retirement, Please query it first.

SOLUTION

Step 1. Navigate to Inquiry > Transaction History.

Step 2. Enter the Book & Asset Number.

Step 3. Press the Find button.

Step 4. Click the Transaction type field for the Retired Asset.

Step 5. Note the Reference Number.

Step 6. Go to the Asset Workbench and query the Asset.

Step 7. Click the Retirements button.

Step 8. As the Retirements screen is displayed:
a. Press F11 to query or select Query>Enter on the menu bar.
b. Enter the Reference Number noted above.
c. Press CTRL F11 to perform the query or select Query>Run from the menu bar.

Step 9. As information appears on the screen, verify if it is correct and press the Reinstate button.

Step 10. Run the Calculate Gains and Losses program.

Thursday, 28 September 2017

Asset Category Listing - Oracle EBS R12

https://fixedasstets.blogspot.com/2017/09/asset-category-listing.html

The Below PLSQL Query is useful to get Asset Categories Accounts, Methods and Calendars attached to a Category

SELECT

AD.ASSET_NUMBER,
AD.DESCRIPTION,
DATE_PLACED_IN_SERVICE,
FC.SEGMENT1,
fc.SEGMENT2,
fc.SEGMENT3,

(select GCC1.SEGMENT1 || '-' || GCC1.SEGMENT2 || '-' || GCC1.SEGMENT3 || '-' ||
GCC1.SEGMENT4 || '-' || GCC1.SEGMENT5 || '-' || GCC1.SEGMENT6 || '-' ||
GCC1.SEGMENT7 || '-' || GCC1.SEGMENT8
from GL_CODE_COMBINATIONS gcc1
where gcc1.CODE_COMBINATION_ID = FCB.ASSET_COST_ACCOUNT_CCID) CATG_ASSET_COST_ACCOUNT,

(select GCC2.SEGMENT1 || '-' || GCC2.SEGMENT2 || '-' || GCC2.SEGMENT3 || '-' ||
GCC2.SEGMENT4 || '-' || GCC2.SEGMENT5 || '-' || GCC2.SEGMENT6 || '-' ||
GCC2.SEGMENT7 || '-' || GCC2.SEGMENT8
from GL_CODE_COMBINATIONS gcc2
where gcc2.CODE_COMBINATION_ID = FCB.ASSET_CLEARING_ACCOUNT_CCID) CATG_ASSET_CLEARING_ACCOUNT,

(select GCC3.SEGMENT1 || '-' || GCC3.SEGMENT2 || '-' || GCC3.SEGMENT3 || '-' ||
GCC3.SEGMENT4 || '-' || GCC3.SEGMENT5 || '-' || GCC3.SEGMENT6 || '-' ||
GCC3.SEGMENT7 || '-' || GCC3.SEGMENT8
from GL_CODE_COMBINATIONS gcc3
where GCC3.CODE_COMBINATION_ID = FCB.DEPRN_EXPENSE_ACCOUNT_CCID) CATG_DEPRN_EXPENSE_ACCOUNT,

(select GCC4.SEGMENT1 || '-' || GCC4.SEGMENT2 || '-' || GCC4.SEGMENT3 || '-' ||
GCC4.SEGMENT4 || '-' || GCC4.SEGMENT5 || '-' || GCC4.SEGMENT6 || '-' ||
GCC4.SEGMENT7 || '-' || GCC4.SEGMENT8
from GL_CODE_COMBINATIONS gcc4
where gcc4.CODE_COMBINATION_ID = FCB.RESERVE_ACCOUNT_CCID) CATG_DEPRN_RESERVE_ACCT,

(select GCC5.SEGMENT1 || '-' || GCC5.SEGMENT2 || '-' || GCC5.SEGMENT3 || '-' ||
GCC5.SEGMENT4 || '-' || GCC5.SEGMENT5 || '-' || GCC5.SEGMENT6 || '-' ||
GCC5.SEGMENT7 || '-' || GCC5.SEGMENT8
from FA_DISTRIBUTION_HISTORY dh, gl_code_combinations GCC5
where dh.CODE_COMBINATION_ID = GCC5.CODE_COMBINATION_ID
and dh.ASSET_ID = AD.ASSET_ID
and dh.DATE_INEFFECTIVE is null
and dh.BOOK_TYPE_CODE = &Book) DIST_DEPRN_EXPENSE_ACCOUNT,
METHOD_CODE METHOD,
RSV.LIFE LIFE,
RSV.RATE ADJ_RATE,
DS.BONUS_RATE,
RSV.CAPACITY,
SUM(decode(transaction_type, 'B', NULL, COST)) COST,
SUM(RSV.DEPRN_AMOUNT) DEPRN_AMOUNT,
SUM(RSV.YTD_DEPRN) YTD_DEPRN,
SUM(RSV.DEPRN_RESERVE) DEPRN_RESERVE,
SUM(decode(transaction_type, 'B', NULL, COST)) - SUM(RSV.DEPRN_RESERVE) NBV,
sum(decode(transaction_type, 'B', NULL, nvl(PERCENT, 0))) PERCENT,
TRANSACTION_TYPE T_TYPE

FROM fa_reserve_ledger_itf RSV,
fa_deprn_periods g,
FA_ADDITIONS AD,
GL_CODE_COMBINATIONS CC,
fa_deprn_summary DS,
FA_CATEGORY_BOOKS FCB,
FA_CATEGORIES FC

WHERE RSV.ASSET_ID = AD.ASSET_ID
AND RSV.DH_CCID = CC.CODE_COMBINATION_ID
AND g.PERIOD_COUNTER = RSV.PERIOD_COUNTER
AND g.BOOK_TYPE_CODE = '&BOOK'
AND DS.PERIOD_COUNTER(+) = RSV.PERIOD_COUNTER
AND DS.BOOK_TYPE_CODE(+) = &Book
AND DS.ASSET_ID(+) = RSV.ASSET_ID
AND FCB.CATEGORY_ID = AD.ASSET_CATEGORY_ID
AND FC.CATEGORY_ID = FCB.CATEGORY_ID
AND FC.CATEGORY_ID = AD.ASSET_CATEGORY_ID
AND FCB.BOOK_TYPE_CODE = '&Book'
AND RSV.REQUEST_ID in
(select max(s.request_id) from fa_reserve_ledger_itf s)

GROUP BY FCB.ASSET_COST_ACCOUNT_CCID,
FCB.ASSET_CLEARING_ACCOUNT_CCID,
FCB.DEPRN_EXPENSE_ACCOUNT_CCID,
FCB.RESERVE_ACCOUNT_CCID,
AD.ASSET_ID,
FC.SEGMENT1,
fc.SEGMENT2,
fc.SEGMENT3,
AD.ASSET_NUMBER,
AD.DESCRIPTION,
DATE_PLACED_IN_SERVICE,
METHOD_CODE,
RSV.LIFE,
RSV.RATE,
RSV.CAPACITY,
DS.BONUS_RATE,
TRANSACTION_TYPE

ORDER BY AD.ASSET_NUMBER

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)