AM_KIB_DPR_VW(SQL View) |
Index Back |
|---|---|
AM Metrics Insights |
| SELECT A.BUSINESS_UNIT , A.ASSET_ID , A.ASSET_STATUS , B.BOOK , B.FISCAL_YEAR , B.ACCOUNTING_PERIOD , A.ASSET_TYPE , A.ASSET_SUBTYPE , A.TAG_NUMBER , A.SERIAL_ID , A.DESCR , B.CATEGORY , A.PROFILE_ID , A.ACQUISITION_CD , G.END_DT , B.CURRENCY_CD , CASE WHEN A.SES_LAST_DTTM > C.SES_LAST_DTTM THEN A.SES_LAST_DTTM ELSE C.SES_LAST_DTTM END , 'Capitalized' , A.FINANCIAL_ASSET_SW , CD.DESCR , CASE WHEN A.IN_SERVICE_DT BETWEEN G.BEGIN_DT AND G.END_DT THEN 1 ELSE 0 END , SUM(%Coalesce( CASE WHEN A.ASSET_STATUS ='D' AND G.FISCAL_YEAR = L.FISCAL_YEAR AND G.ACCOUNTING_PERIOD = L.ACCOUNTING_PERIOD THEN 1 ELSE 0 END ,0)) , SUM(B.COST) , SUM(B.COST_ADD) , SUM(B.DEPR + B.AMORTIZATION_AMT + B.DEPR_PDP + B.AMORT_PLP) , SUM(B.DEPR + B.AMORTIZATION_AMT) , SUM(B.DEPR_PDP + B.AMORT_PLP) , SUM(B.DEPR_YTD + B.AMORT_YTD) , SUM(B.DEPR_LTD + B.AMORT_LTD) , SUM(B.COST - B.DEPR_LTD - B.AMORT_LTD) , %Coalesce(SUM(L.GAIN_LOSS) ,0) , %Coalesce(SUM(L.AMOUNT_1) ,0) , %Coalesce(SUM(L.AMOUNT_2) ,0) , SUM(B.SALVAGE_VALUE) FROM PS_ASSET A , PS_DEPR_RPT B LEFT OUTER JOIN PS_RET_GL_FY_VW L ON B.BUSINESS_UNIT = L.BUSINESS_UNIT AND B.ASSET_ID = L.ASSET_ID AND B.BOOK = L.BOOK AND B.FISCAL_YEAR = L.FISCAL_YEAR AND B.ACCOUNTING_PERIOD = L.ACCOUNTING_PERIOD AND B.CATEGORY = L.CATEGORY AND B.CF_SEQNO = L.CF_SEQNO , PS_DEPR_RPT_DTTM C , PS_BU_AM_BOOK_VW E , PS_SET_CNTRL_REC F , PS_CAL_DETP_TBL G ,PS_CURRENCY_CD_TBL CD WHERE A.BUSINESS_UNIT =B.BUSINESS_UNIT AND A.ASSET_ID =B.ASSET_ID AND A.FINANCIAL_ASSET_SW = 'Y' AND B.BUSINESS_UNIT =C.BUSINESS_UNIT AND B.ASSET_ID =C.ASSET_ID AND B.BOOK = C.BOOK AND B.FISCAL_YEAR = C.FISCAL_YEAR AND B.ACCOUNTING_PERIOD = C.ACCOUNTING_PERIOD AND B.CF_SEQNO = C.CF_SEQNO AND B.BUSINESS_UNIT = E.BUSINESS_UNIT AND B.BOOK = E.BOOK AND F.SETCNTRLVALUE = B.BUSINESS_UNIT AND F.REC_GROUP_ID = 'FS_02' AND F.RECNAME ='CAL_DEFN_TBL' AND F.SETID = G.SETID AND E.CAL_DEPR_PD = G.CALENDAR_ID AND B.FISCAL_YEAR = G.FISCAL_YEAR AND B.ACCOUNTING_PERIOD = G.ACCOUNTING_PERIOD AND B.CURRENCY_CD = CD.CURRENCY_CD %Sql(AM_CUR_CD_EFFDT) GROUP BY A.BUSINESS_UNIT , A.ASSET_ID , A.ASSET_STATUS , B.BOOK , B.FISCAL_YEAR , B.ACCOUNTING_PERIOD , A.ASSET_TYPE , A.ASSET_SUBTYPE , A.TAG_NUMBER , A.SERIAL_ID , A.DESCR , B.CATEGORY , A.PROFILE_ID , A.ACQUISITION_CD , B.CATEGORY ,G.BEGIN_DT , G.END_DT , B.CURRENCY_CD , A.SES_LAST_DTTM , C.SES_LAST_DTTM , A.FINANCIAL_ASSET_SW ,A.IN_SERVICE_DT, CD.DESCR UNION SELECT A.BUSINESS_UNIT , A.ASSET_ID , A.ASSET_STATUS , B.BOOK , G.FISCAL_YEAR , G.ACCOUNTING_PERIOD , A.ASSET_TYPE , A.ASSET_SUBTYPE , A.TAG_NUMBER , A.SERIAL_ID , A.DESCR , B.CATEGORY , A.PROFILE_ID , A.ACQUISITION_CD , G.END_DT , B.FROM_CUR , A.SES_LAST_DTTM , 'Non-Capitalized' , A.FINANCIAL_ASSET_SW , CD.DESCR , CASE WHEN A.IN_SERVICE_DT BETWEEN G.BEGIN_DT AND G.END_DT THEN 1 ELSE 0 END , SUM(%Coalesce( CASE WHEN A.ASSET_STATUS='D' AND L.RETIREMENT_DT BETWEEN G.BEGIN_DT AND G.END_DT THEN 1 ELSE 0 END,0)) , SUM(B.COST) , SUM( CASE WHEN B.TRANS_DT BETWEEN G.BEGIN_DT AND G.END_DT THEN B.COST_ADD ELSE 0 END) , 0 , 0 , 0 , 0 , 0 , SUM(B.COST) , %Coalesce(SUM(%Round( %DecMult(L.Gain_Loss , %decmult(B.Cost, %decdiv((1+0.0), L.Retirement_Amt))) ,CD.DECIMAL_POSITIONS)),0) , %Coalesce(SUM ( CASE WHEN L.GAIN_LOSS > 0 THEN %Round( %DecMult(L.Gain_Loss , %decmult(B.Cost, %decdiv((1+0.0), L.Retirement_Amt))) ,CD.DECIMAL_POSITIONS) ELSE 0 END),0) , %Coalesce(SUM ( CASE WHEN L.GAIN_LOSS < 0 THEN %DecMult(%Round( %DecMult(L.Gain_Loss , %decmult(B.Cost, %decdiv((1+0.0), L.Retirement_Amt))) ,CD.DECIMAL_POSITIONS),-1) ELSE 0 END),0) , 0 FROM PS_ASSET A ,PS_COST_NONCAP_VW1 B LEFT OUTER JOIN PS_RETIREMENT_NF L ON B.BUSINESS_UNIT = L.BUSINESS_UNIT AND B.ASSET_ID = L.ASSET_ID AND B.BOOK = L.BOOK , PS_BU_AM_BOOK_VW E , PS_SET_CNTRL_REC F , PS_CAL_DETP_TBL G,PS_CURRENCY_CD_TBL CD WHERE A.BUSINESS_UNIT =B.BUSINESS_UNIT AND A.ASSET_ID =B.ASSET_ID AND A.FINANCIAL_ASSET_SW = 'N' AND B.BUSINESS_UNIT = E.BUSINESS_UNIT AND B.BOOK = E.BOOK AND F.SETCNTRLVALUE = B.BUSINESS_UNIT AND F.REC_GROUP_ID = 'FS_02' AND F.RECNAME ='CAL_DEFN_TBL' AND F.SETID = G.SETID AND E.CAL_DEPR_PD = G.CALENDAR_ID AND (G.END_DT BETWEEN A.IN_SERVICE_DT AND %CurrentDateIn OR G.BEGIN_DT BETWEEN A.IN_SERVICE_DT AND %CurrentDateIn) AND B.FROM_CUR = CD.CURRENCY_CD %Sql(AM_CUR_CD_EFFDT) GROUP BY A.BUSINESS_UNIT , A.ASSET_ID , A.ASSET_STATUS , B.BOOK , G.FISCAL_YEAR , G.ACCOUNTING_PERIOD , A.ASSET_TYPE , A.ASSET_SUBTYPE , A.TAG_NUMBER , A.SERIAL_ID , A.DESCR , B.CATEGORY , A.PROFILE_ID , A.ACQUISITION_CD , B.CATEGORY ,G.BEGIN_DT , G.END_DT , B.FROM_CUR , A.SES_LAST_DTTM, A.FINANCIAL_ASSET_SW, CD.DESCR,A.IN_SERVICE_DT |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  BUSINESS_UNIT |
Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 2 | ASSET_ID |
Character(12) | VARCHAR2(12) NOT NULL | Asset Identification |
| 3 | ASSET_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Asset Status
A=Received (Not in Service) B=Budgeted C=Commitment D=Disposed I=In Service M=Suspended R=Requisitioned T=Transferred W=Work In Progress |
| 4 | BOOK |
Character(10) | VARCHAR2(10) NOT NULL | Asset Book Name |
| 5 | FISCAL_YEAR |
Number(4,0) | SMALLINT NOT NULL | Fiscal Year |
| 6 | ACCOUNTING_PERIOD |
Number(3,0) | SMALLINT NOT NULL | Identifies a time period to which you post transactions. Typically, an accounting period represents a month, but it can also represent a week, a day, or any user-defined interval. An accounting period has a beginning date and an ending date, and is defined in the calendar table. |
| 7 | ASSET_TYPE | Character(3) | VARCHAR2(3) NOT NULL |
Asset Type
010=IT Hardware 020=IT Software 040=Equipment 050=Property 060=Fleet 070=Machinery 080=Furniture 090=Facility 100=Intangible 200=Asset Retirement Obligations 999=Other |
| 8 | ASSET_SUBTYPE | Character(15) | VARCHAR2(15) NOT NULL | Asset Subtype |
| 9 | TAG_NUMBER | Character(12) | VARCHAR2(12) NOT NULL | Tag Number |
| 10 | SERIAL_ID | Character(20) | VARCHAR2(20) NOT NULL | Serial ID |
| 11 | ASSET_DESCR30 | Character(30) | VARCHAR2(30) NOT NULL | Asset Description |
| 12 | CATEGORY | Character(5) | VARCHAR2(5) NOT NULL | Asset Category |
| 13 | PROFILE_ID | Character(10) | VARCHAR2(10) NOT NULL | Asset Profile ID |
| 14 | ACQUISITION_CD | Character(1) | VARCHAR2(1) NOT NULL |
Acquisition Code
C=Constructed D=Donated I=Trade In K=Like Exchange L=Leased P=Purchased T=Transferred |
| 15 | END_DT | Date(10) | DATE | End Date |
| 16 | CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Currency Code |
| 17 | SES_LAST_DTTM | DateTime(26) | TIMESTAMP |
PTSF Last Modified DateTimestamp
Default Value: %DATETIME |
| 18 | CLASSDEFNDESC | Character(30) | VARCHAR2(30) NOT NULL | Permission List Description |
| 19 | FINANCIAL_ASSET_SW | Character(1) | VARCHAR2(1) NOT NULL |
Capitalized Asset
N=Non Cap Y=Capitalize |
| 20 | CURRENCY_CD_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Currency Code Description |
| 21 | ASSET_COUNT2 | Number(12,0) | DECIMAL(12) NOT NULL | Assets in Group |
| 22 | COUNTER1 | Number(16,2) | DECIMAL(15,2) NOT NULL | Counter 1 |
| 23 | COST | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Total Cost |
| 24 | COST_ADD | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Cost Addition |
| 25 | DEPR | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Depreciation Amt |
| 26 | DEPR_AMOUNT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Depreciation Amount |
| 27 | DEPR_PDP | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Prior Period Depreciation |
| 28 | DEPR_YTD | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Year to Date Depreciation |
| 29 | DEPR_LTD | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Life to Date Depreciation |
| 30 | NET_BK_VALUE | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Net Book Value |
| 31 | GAIN_LOSS | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Retirement Gain/Loss |
| 32 | AMOUNT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Amount |
| 33 | AMOUNT_1 | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Amount Work Field |
| 34 | SALVAGE_VALUE | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Salvage Value |