AM_KIB_DSH_VW(SQL View) |
Index Back |
|---|---|
AM Metrics Insights |
| SELECT DPR.BUSINESS_UNIT ,DPR.ASSET_ID ,DPR.ASSET_STATUS ,DPR.BOOK ,DPR.FISCAL_YEAR ,DPR.ACCOUNTING_PERIOD ,DPR.ASSET_TYPE ,DPR.ASSET_SUBTYPE ,DPR.TAG_NUMBER ,DPR.SERIAL_ID ,DPR.ASSET_DESCR30 ,DPR.CATEGORY ,DPR.PROFILE_ID ,DPR.ACQUISITION_CD ,D.LOCATION ,D.AREA_ID ,DPR.CURRENCY_CD ,DPR.SES_LAST_DTTM ,DPR.END_DT ,DPR.FINANCIAL_ASSET_SW ,Z.XLATLONGNAME ,Y.XLATLONGNAME ,%Coalesce(I.DESCRSHORT ,' ') ,J.DESCR ,D.DESCR ,%Coalesce(N.DESCR ,' ') ,DPR.CLASSDEFNDESC ,M.XLATLONGNAME ,DPR.CURRENCY_CD_DESCR ,P.DESCR ,DPR.ASSET_COUNT2 ,DPR.COUNTER1 ,DPR.COST ,DPR.COST_ADD ,DPR.DEPR ,DPR.DEPR_AMOUNT ,DPR.DEPR_PDP ,DPR.DEPR_YTD ,DPR.DEPR_LTD ,DPR.NET_BK_VALUE ,DPR.GAIN_LOSS ,DPR.AMOUNT ,DPR.AMOUNT_1 ,DPR.SALVAGE_VALUE FROM PS_AM_KIB_DPR_VW DPR ,PS_ASSET_LOC_VW3 D LEFT OUTER JOIN PS_MTM_AMAREAVW N ON D.LOCATION = N.KEY_CHAR_30_01 AND D.AREA_ID = N.AREA_ID AND D.SETID1 = N.SETID ,PSXLATITEM Z ,PSXLATITEM Y ,PS_ASSET_SES_VW H LEFT OUTER JOIN PS_AM_SUBTYPE I ON H.SETID1 = I.SETID AND H.ASSET_TYPE = I.ASSET_TYPE AND H.ASSET_SUBTYPE = I.ASSET_SUBTYPE ,PS_CATEGORY_TBL J ,PS_SET_CNTRL_REC K ,PSXLATITEM M ,PS_SET_CNTRL_REC O ,PS_PROFILE_TBL P WHERE DPR.BUSINESS_UNIT = D.BUSINESS_UNIT AND DPR.ASSET_ID = D.ASSET_ID AND Z.FIELDNAME = 'ASSET_TYPE' AND Z.FIELDVALUE = DPR.ASSET_TYPE AND Z.EFFDT = ( SELECT MAX(A_ED.EFFDT) FROM PSXLATITEM A_ED WHERE Z.FIELDNAME = A_ED.FIELDNAME AND Z.FIELDVALUE = A_ED.FIELDVALUE AND A_ED.EFFDT <= %CurrentDateIn) AND DPR.BUSINESS_UNIT = H.BUSINESS_UNIT AND DPR.ASSET_ID = H.ASSET_ID AND Y.FIELDNAME = 'ACQUISITION_CD' AND Y.FIELDVALUE = DPR.ACQUISITION_CD AND Y.EFFDT = ( SELECT MAX(A_ED.EFFDT) FROM PSXLATITEM A_ED WHERE Y.FIELDNAME = A_ED.FIELDNAME AND Y.FIELDVALUE = A_ED.FIELDVALUE AND A_ED.EFFDT <= %CurrentDateIn) AND DPR.BUSINESS_UNIT = K.SETCNTRLVALUE AND K.REC_GROUP_ID = 'AM_10' AND K.RECNAME = 'CATEGORY_TBL' AND K.SETID = J.SETID AND DPR.CATEGORY = J.CATEGORY AND J.EFFDT = ( SELECT MAX(J1.EFFDT) FROM PS_CATEGORY_TBL J1 WHERE J1.EFFDT <= %CurrentDateIn AND J1.EFF_STATUS = 'A' AND J1.SETID = J.SETID AND J1.CATEGORY = J.CATEGORY) AND D.EFFDT = ( SELECT MAX(EA.EFFDT) FROM PS_ASSET_LOCATION EA WHERE D.BUSINESS_UNIT = EA.BUSINESS_UNIT AND D.ASSET_ID = EA.ASSET_ID AND EA.EFFDT <= DPR.END_DT) AND D.EFFSEQ = ( SELECT MAX(D1.EFFSEQ) FROM PS_ASSET_LOCATION D1 WHERE D1.BUSINESS_UNIT = D.BUSINESS_UNIT AND D1.ASSET_ID = D.ASSET_ID AND D1.EFFDT = D.EFFDT) AND M.FIELDNAME = 'ASSET_STATUS' AND M.FIELDVALUE = DPR.ASSET_STATUS AND M.EFFDT = ( SELECT MAX(A_ED.EFFDT) FROM PSXLATITEM A_ED WHERE M.FIELDNAME = A_ED.FIELDNAME AND M.FIELDVALUE = A_ED.FIELDVALUE AND A_ED.EFFDT <= %CurrentDateIn) AND O.SETCNTRLVALUE = DPR.BUSINESS_UNIT AND O.REC_GROUP_ID = 'AM_15' AND O.RECNAME='PROFILE_TBL' AND O.SETID = P.SETID AND P.PROFILE_ID = DPR.PROFILE_ID |
| # | 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 | LOCATION | Character(10) | VARCHAR2(10) NOT NULL | Location Code |
| 16 | AREA_ID | Character(10) | VARCHAR2(10) NOT NULL | Area ID |
| 17 | CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Currency Code |
| 18 | SES_LAST_DTTM | DateTime(26) | TIMESTAMP |
PTSF Last Modified DateTimestamp
Default Value: %DATETIME |
| 19 | END_DT | Date(10) | DATE | End Date |
| 20 | FINANCIAL_ASSET_SW | Character(1) | VARCHAR2(1) NOT NULL |
Capitalized Asset
N=Non Cap Y=Capitalize |
| 21 | ASSET_TYPE_SUBTYPE | Character(30) | VARCHAR2(30) NOT NULL | Asset Type/Subtype |
| 22 | ACQUISITION_DESCR | Character(30) | VARCHAR2(30) NOT NULL | DESCR field for ACQ_DETAIL |
| 23 | WM_SUBTYPE_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Asset Subtype |
| 24 | CATEGORY_DESCR | Character(60) | VARCHAR2(60) NOT NULL | Category Description |
| 25 | LOCATION_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Location |
| 26 | AREA_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Area Description |
| 27 | CLASSDEFNDESC | Character(30) | VARCHAR2(30) NOT NULL | Permission List Description |
| 28 | STATUS_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Status Description |
| 29 | CURRENCY_CD_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Currency Code Description |
| 30 | PROFILE_ID_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Profile Id Description |
| 31 | ASSET_COUNT2 | Number(12,0) | DECIMAL(12) NOT NULL | Assets in Group |
| 32 | COUNTER1 | Number(16,2) | DECIMAL(15,2) NOT NULL | Counter 1 |
| 33 | COST | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Total Cost |
| 34 | COST_ADD | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Cost Addition |
| 35 | DEPR | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Depreciation Amt |
| 36 | DEPR_AMOUNT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Depreciation Amount |
| 37 | DEPR_PDP | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Prior Period Depreciation |
| 38 | DEPR_YTD | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Year to Date Depreciation |
| 39 | DEPR_LTD | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Life to Date Depreciation |
| 40 | NET_BK_VALUE | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Net Book Value |
| 41 | GAIN_LOSS | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Retirement Gain/Loss |
| 42 | AMOUNT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Amount |
| 43 | AMOUNT_1 | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Amount Work Field |
| 44 | SALVAGE_VALUE | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Salvage Value |