I had a request to create a query that would return an asset’s remaining life from PeopleSoft Asset Management 9.0.  This presumes you have loaded the PS_ASSET_NBV_TBL (asset NBV table) for the asset, that you are running it as VP1, and the Run ID used is NBV.  Obviously you should change these variables to match your current configuration.

SELECT A.BUSINESS_UNIT, A.ASSET_ID, A.BOOK, A.IN_SERVICE_DT,
A.BEGIN_DEPR_DT, A.LIFE,
CASE
WHEN A.LIFE-(COUNT(D.ACCOUNTING_PERIOD)+((C.END_DT-A.BEGIN_DEPR_DT)/
     (C.END_DT-C.BEGIN_DT))-1) < 0 THEN 0
ELSE A.LIFE-(COUNT(D.ACCOUNTING_PERIOD)+((C.END_DT-A.BEGIN_DEPR_DT)/
     (C.END_DT-C.BEGIN_DT))-1)
END AS REMAINING_LIFE
FROM PS_BOOK_CUR_VW A, PS_BU_BOOK_TBL B, PS_CAL_DETP_TBL C,
     PS_CAL_DETP_TBL D, PS_SET_CNTRL_REC T
WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT
AND A.BOOK = B.BOOK
AND A.BUSINESS_UNIT = T.SETCNTRLVALUE
AND T.REC_GROUP_ID = 'FS_02'
AND T.RECNAME = 'CAL_DETP_TBL'
AND C.SETID = T.SETID
AND B.CAL_DEPR_PD = C.CALENDAR_ID
AND D.SETID = T.SETID
AND B.CAL_DEPR_PD = D.CALENDAR_ID
AND A.BEGIN_DEPR_DT >= C.BEGIN_DT
AND A.BEGIN_DEPR_DT <= C.END_DT
AND D.END_DT >= C.END_DT
AND D.END_DT <= (SELECT DISTINCT N.AS_OF_DATE FROM PS_ASSET_NBV_TBL N
     WHERE N.OPRID = 'VP1' AND N.RUN_ID = 'NBV' AND A.BOOK = N.BOOK)
GROUP BY A.BUSINESS_UNIT, A.ASSET_ID, A.BOOK, A.LIFE, A.IN_SERVICE_DT,
     A.BEGIN_DEPR_DT, C.END_DT, C.BEGIN_DT
ORDER BY A.BUSINESS_UNIT, A.ASSET_ID, A.BOOK;

Note: The SQL hasn't been performance tuned yet.  I will post ways to tune this SQL, or appropriate indexes for the tables referenced, once I have them identified.