RE_AM_LSDEF_VW(SQL View) |
Index Back |
|---|
| SELECT A.BUSINESS_UNIT , A.ASSET_ID , B.BUSINESS_UNIT , C.ASSET_STATUS , C.ASSET_TYPE , C.ASSET_SUBTYPE , CASE WHEN A.DESCR = ' ' THEN C.DESCR ELSE A.DESCR END , A.LEASE_COMMENCE_DT , A.EXPIRATION_DT , %Coalesce(B.LSE_TYPE_CD, ' ' ) , CASE WHEN (B.REGION_CD IS NULL OR B.REGION_CD = ' ') THEN C.REGION_CD ELSE B.REGION_CD END , %Coalesce(B.LS_ADMIN,0) , %Coalesce(B.PRTFL_MGR,0) , CASE WHEN (B.VENDOR_ID IS NULL OR B.VENDOR_ID = ' ') THEN A.VENDOR_ID ELSE B.VENDOR_ID END , CASE WHEN A.VENDOR_ID <> ' ' THEN 'N' ELSE 'Y'END FROM PS_LEASE A INNER JOIN PS_ASSET C ON (C.BUSINESS_UNIT = A.BUSINESS_UNIT AND C.ASSET_ID = A.ASSET_ID) LEFT OUTER JOIN PS_RE_AM_LSDEF_TBL B ON (B.BUSINESS_UNIT_AM = A.BUSINESS_UNIT AND B.ASSET_ID = A.ASSET_ID) LEFT OUTER JOIN PS_RE_LS_PPTY D ON (D.BUSINESS_UNIT_AM = A.BUSINESS_UNIT AND D.ASSET_ID = A.ASSET_ID) WHERE D.LS_KEY IS NULL |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | BUSINESS_UNIT_AM | Character(5) | VARCHAR2(5) NOT NULL | AM Business Unit |
| 2 | ASSET_ID | Character(12) | VARCHAR2(12) NOT NULL | Asset Identification |
| 3 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 4 | 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 |
| 5 | 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 |
| 6 | ASSET_SUBTYPE | Character(15) | VARCHAR2(15) NOT NULL | Asset Subtype |
| 7 | DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description |
| 8 | LEASE_COMMENCE_DT | Date(10) | DATE | Commencement Date |
| 9 | EXPIRATION_DT | Date(10) | DATE | Expiration Date |
| 10 | LSE_TYPE_CD | Character(10) | VARCHAR2(10) NOT NULL | This field contains the code for the Lease Types |
| 11 | REGION_CD | Character(10) | VARCHAR2(10) NOT NULL | Identifies the Sales Region. Populated on the Item Table to identify the Sales Region the Item is applied to. |
| 12 | LS_ADMIN | Number(10,0) | DECIMAL(10) NOT NULL | Lease Administrator |
| 13 | PRTFL_MGR | Number(10,0) | DECIMAL(10) NOT NULL | Portfolio Manager |
| 14 | VENDOR_ID | Character(10) | VARCHAR2(10) NOT NULL | Vendor Identifier |
| 15 | UPDATE_FLG | Character(1) | VARCHAR2(1) NOT NULL |
Update Flag
0=No Modification 1=Add 2=Change 3=Delete |