RE_AM_MIGRATED(SQL View) |
Index Back |
|---|
| SELECT L.BUSINESS_UNIT ,P.BUSINESS_UNIT_AM ,L.LS_KEY ,L.LS_NBR ,P.ASSET_ID ,L.LEASE_NAME ,L.REGION_CD ,L.LEASE_STATUS ,( SELECT AST.ASSET_STATUS FROM PS_ASSET AST WHERE AST.BUSINESS_UNIT = P.BUSINESS_UNIT_AM AND AST.ASSET_ID = P.ASSET_ID) ,L.PRINCIPAL_ID ,L.LEASE_START_DT ,L.LEASE_END_DT ,L.LS_ADMIN ,L.PRTFL_MGR ,L.LSE_TYPE_CD ,L.VNDR_LOC ,( SELECT T.TERM_ID FROM PS_RE_LS_TRM T WHERE L.LS_KEY = T.LS_KEY AND T.TXN_GRP = 1 AND T.PAYMENT_GROUP = 1 AND TERM_RENT_TYPE = '01' AND T.EFFDT_FROM = ( SELECT MIN(T1.EFFDT_FROM) FROM PS_RE_LS_TRM T1 WHERE T1.LS_KEY = T.LS_KEY AND T1.TXN_GRP = 1 AND T1.PAYMENT_GROUP = 1 AND TERM_RENT_TYPE = '01') AND T.TERM_ID = ( SELECT MIN(T2.TERM_ID) FROM PS_RE_LS_TRM T2 WHERE T2.LS_KEY = T.LS_KEY AND T2.TXN_GRP = 1 AND T2.PAYMENT_GROUP = 1 AND TERM_RENT_TYPE = '01' AND T2.EFFDT_FROM = T.EFFDT_FROM)) AS BaseRent ,( SELECT T.TERM_ID FROM PS_RE_LS_TRM T WHERE L.LS_KEY = T.LS_KEY AND T.TXN_GRP = 1 AND T.PAYMENT_GROUP = 1 AND TERM_RENT_TYPE = '06' AND T.EFFDT_FROM = ( SELECT MIN(T1.EFFDT_FROM) FROM PS_RE_LS_TRM T1 WHERE T1.LS_KEY = T.LS_KEY AND T1.TXN_GRP = 1 AND T1.PAYMENT_GROUP = 1 AND TERM_RENT_TYPE = '06') AND T.TERM_ID = ( SELECT MIN(T2.TERM_ID) FROM PS_RE_LS_TRM T2 WHERE T2.LS_KEY = T.LS_KEY AND T2.TXN_GRP = 1 AND T2.PAYMENT_GROUP = 1 AND TERM_RENT_TYPE = '06' AND T2.EFFDT_FROM = T.EFFDT_FROM)) AS PurchaseOption ,( SELECT T.TERM_ID FROM PS_RE_LS_TRM T WHERE L.LS_KEY = T.LS_KEY AND T.TXN_GRP = 1 AND T.PAYMENT_GROUP = 1 AND TERM_RENT_TYPE = '08' AND T.EFFDT_FROM = ( SELECT MIN(T1.EFFDT_FROM) FROM PS_RE_LS_TRM T1 WHERE T1.LS_KEY = T.LS_KEY AND T1.TXN_GRP = 1 AND T1.PAYMENT_GROUP = 1 AND TERM_RENT_TYPE = '08') AND T.TERM_ID = ( SELECT MIN(T2.TERM_ID) FROM PS_RE_LS_TRM T2 WHERE T2.LS_KEY = T.LS_KEY AND T2.TXN_GRP = 1 AND T2.PAYMENT_GROUP = 1 AND TERM_RENT_TYPE = '08' AND T2.EFFDT_FROM = T.EFFDT_FROM)) AS Penalty ,( SELECT T.TERM_ID FROM PS_RE_LS_TRM T WHERE L.LS_KEY = T.LS_KEY AND T.TXN_GRP = 6 AND T.PAYMENT_GROUP = 1 AND TERM_RENT_TYPE = '10' AND T.EFFDT_FROM = ( SELECT MIN(T1.EFFDT_FROM) FROM PS_RE_LS_TRM T1 WHERE T1.LS_KEY = T.LS_KEY AND T1.TXN_GRP = 6 AND T1.PAYMENT_GROUP = 1 AND TERM_RENT_TYPE = '10') AND T.TERM_ID = ( SELECT MIN(T2.TERM_ID) FROM PS_RE_LS_TRM T2 WHERE T2.LS_KEY = T.LS_KEY AND T2.TXN_GRP = 6 AND T2.PAYMENT_GROUP = 1 AND TERM_RENT_TYPE = '10' AND T2.EFFDT_FROM = T.EFFDT_FROM)) AS InterimRent FROM PS_RE_LS L , PS_RE_LS_PPTY P WHERE L.LS_KEY = P.LS_KEY AND L.LEASE_OBLG = '1' AND P.ASSET_SEQ_NBR = 1 AND L.SYSTEM_SOURCE = 'BAM' |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 2 | BUSINESS_UNIT_AM | Character(5) | VARCHAR2(5) NOT NULL | AM Business Unit |
| 3 | LS_KEY | Number(15,0) | DECIMAL(15) NOT NULL | Lease Key, Unique Key |
| 4 | LS_NBR | Character(10) | VARCHAR2(10) NOT NULL | Lease Number, User Enterable or Generated Sequence Number |
| 5 | ASSET_ID | Character(12) | VARCHAR2(12) NOT NULL | Asset Identification |
| 6 | LEASE_NAME | Character(60) | VARCHAR2(60) NOT NULL | Lease Name |
| 7 | 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. |
| 8 | LEASE_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Lease Status
1=Pending 2=Active 3=Canceled 4=Disputed 5=Expired 6=Holdover 7=Closed 8=Transferred |
| 9 | 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 |
| 10 | PRINCIPAL_ID | Character(15) | VARCHAR2(15) NOT NULL | Landlord/Tenant |
| 11 | LEASE_START_DT | Date(10) | DATE | Lease Start Date |
| 12 | LEASE_END_DT | Date(10) | DATE | Lease End Date |
| 13 | LS_ADMIN | Number(10,0) | DECIMAL(10) NOT NULL | Lease Administrator |
| 14 | PRTFL_MGR | Number(10,0) | DECIMAL(10) NOT NULL | Portfolio Manager |
| 15 | LSE_TYPE_CD | Character(10) | VARCHAR2(10) NOT NULL | This field contains the code for the Lease Types |
| 16 | VNDR_LOC | Character(10) | VARCHAR2(10) NOT NULL | Vendor Location |
| 17 | TERM_ID1 | Number(10,0) | DECIMAL(10) NOT NULL | Base Rent Term ID |
| 18 | TERM_ID2 | Number(10,0) | DECIMAL(10) NOT NULL | Purchase Option Term ID |
| 19 | TERM_ID3 | Number(10,0) | DECIMAL(10) NOT NULL | Penalties Term ID |
| 20 | TERM_ID4 | Number(10,0) | DECIMAL(10) NOT NULL | Interim Rent Term ID |