RE_MNOBG_PIV_VW(SQL View) |
Index Back |
|---|---|
Minimum Obligation Pivot VwMinimum Obligation Pivot Vw |
| SELECT SETID ,BUSINESS_UNIT ,TRM_CALENDAR_ID ,VENDOR_ID , PRINCIPAL_NAME1 ,( SELECT LS_TYPE.INSTANCE_NAME FROM PS_RE_LSETYPE_SRCH LS_TYPE WHERE LS_TYPE.SETID=MIN_OBLG.SETID AND LS_TYPE.LSE_TYPE_CD = MIN_OBLG.LSE_TYPE_CD), ( SELECT LA_DESC.LS_ADMIN_DESC FROM PS_RE_LS_LA_DESC LA_DESC WHERE LA_DESC.LS_KEY = MIN_OBLG.LS_KEY AND LA_DESC.LS_NBR = MIN_OBLG.LS_NBR AND LA_DESC.LEASE_OBLG = MIN_OBLG.LEASE_OBLG ) , ( SELECT PM_DESC.PRTFL_MGR_DESC FROM PS_RE_LS_PM_DESC PM_DESC WHERE PM_DESC.LS_KEY = MIN_OBLG.LS_KEY AND PM_DESC.LS_NBR = MIN_OBLG.LS_NBR AND PM_DESC.LEASE_OBLG = MIN_OBLG.LEASE_OBLG) ,REGION_CD ,LS_KEY ,LS_NBR ,LEASE_NAME ,TXN_GRP ,CURRENCY_CD ,FISCAL_YEAR, %Coalesce(AMOUNT,0),LEASE_START_DT,LEASE_END_DT,PROPERTY_NM,ASSET_ID,BUSINESS_UNIT_AM FROM( SELECT W.SETID ,BU.BUSINESS_UNIT ,BU.TRM_CALENDAR_ID , LAND.VENDOR_ID , LAND.PRINCIPAL_NAME1 ,B.REGION_CD ,B.LSE_TYPE_CD , B.LEASE_OBLG ,B.LS_ADMIN ,B.PRTFL_MGR , B.LS_KEY ,B.LS_NBR ,B.LEASE_NAME ,X.TXN_GRP ,B.CURRENCY_CD ,W.FISCAL_YEAR , ( SELECT SUM(Q.AMOUNT) FROM PS_RE_LS_TRM_SCHED Q ,PS_RE_LS_TRM X1 WHERE X1.LS_KEY = B.LS_KEY AND Q.LS_KEY = X1.LS_KEY AND X1.TXN_GRP = X.TXN_GRP AND X1.TERM_ID = Q.TERM_ID AND Q.SCHED_DATE >= ( SELECT MIN(F.BEGIN_DT) FROM PS_CAL_DETP_TBL F WHERE F.CALENDAR_ID = BU.TRM_CALENDAR_ID AND F.FISCAL_YEAR = W.FISCAL_YEAR AND F.SETID = W.SETID) AND Q.SCHED_DATE <= ( SELECT MAX(F.END_DT) FROM PS_CAL_DETP_TBL F WHERE F.CALENDAR_ID = BU.TRM_CALENDAR_ID AND F.FISCAL_YEAR =W.FISCAL_YEAR AND F.SETID = W.SETID ) ) AS AMOUNT,B.LEASE_START_DT,B.LEASE_END_DT, PROP.PROPERTY_NM,PROP.ASSET_ID,PROP.BUSINESS_UNIT AS BUSINESS_UNIT_AM FROM PS_RE_LS B , PS_RE_LS_TRM X , PS_RE_BUS_UNIT BU , PS_SET_CNTRL_REC C ,PS_RE_YEAR_VW W ,PS_RE_LANDLORD_VW LAND ,PS_SET_CNTRL_REC C1 ,PS_ASSET_PROPERTY PROP WHERE BU.BUSINESS_UNIT = B.BUSINESS_UNIT AND C.RECNAME = 'CAL_DETP_TBL' AND C.SETCNTRLVALUE = BU.BUSINESS_UNIT AND B.LEASE_OBLG = '1' AND B.LS_KEY = X.LS_KEY AND (B.LEASE_STATUS = '2' OR B.LEASE_STATUS = '6') AND W.SETID = C.SETID AND W.CALENDAR_ID = BU.TRM_CALENDAR_ID AND LAND.VENDOR_ID = B.PRINCIPAL_ID AND C1.RECNAME = 'RE_LANDLORD_VW' AND C1.SETCNTRLVALUE = BU.BUSINESS_UNIT AND LAND.SETID =C1.SETID AND PROP.PROPERTY_ID = B.PRIM_PROPERTY_ID GROUP BY W.SETID, BU.BUSINESS_UNIT, BU.TRM_CALENDAR_ID,LAND.VENDOR_ID,LAND.PRINCIPAL_NAME1 , B.REGION_CD, B.LSE_TYPE_CD, B.LEASE_OBLG, B.LS_ADMIN ,B.PRTFL_MGR, B.LS_KEY, B.LS_NBR, B.LEASE_NAME, X.TXN_GRP, B.CURRENCY_CD, W.FISCAL_YEAR, B.LEASE_START_DT, B.LEASE_END_DT, PROP.PROPERTY_NM,PROP.ASSET_ID,PROP.BUSINESS_UNIT ) MIN_OBLG |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | SETID | Character(5) | VARCHAR2(5) NOT NULL | SetID |
| 2 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 3 | TRM_CALENDAR_ID | Character(2) | VARCHAR2(2) NOT NULL | Lease Terms Calendar ID |
| 4 | VENDOR_ID | Character(10) | VARCHAR2(10) NOT NULL | Vendor Identifier |
| 5 | PRINCIPAL_NAME1 | Character(40) | VARCHAR2(40) NOT NULL | Used to hold Tenant/Landlord name when switching between VENDOR and CUSTOMER data |
| 6 | INSTANCE_NAME | Character(60) | VARCHAR2(60) NOT NULL | Instance Name |
| 7 | LS_ADMIN_DESC | Character(30) | VARCHAR2(30) NOT NULL | Lease Admin Description |
| 8 | PRTFL_MGR_DESC | Character(30) | VARCHAR2(30) NOT NULL | Portfolio Manager Description |
| 9 | 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. |
| 10 | LS_KEY | Number(15,0) | DECIMAL(15) NOT NULL | Lease Key, Unique Key |
| 11 | LS_NBR | Character(10) | VARCHAR2(10) NOT NULL | Lease Number, User Enterable or Generated Sequence Number |
| 12 | LEASE_NAME | Character(60) | VARCHAR2(60) NOT NULL | Lease Name |
| 13 | TXN_GRP | Character(1) | VARCHAR2(1) NOT NULL |
Transaction Group
1=Base Rent 2=Security Deposit 3=Straightline Accounting 4=Operating Expense 5=Percent Rent 6=Miscellaneous Rent 7=Manual Fee 8=Lease Obligation 9=Lease Interest Expense A=Prior Period Lease Obligation B=Prior Period Lease Interest C=Rent Expense D=Lease Incentive |
| 14 | CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Currency Code |
| 15 | FISCAL_REGIME | Character(6) | VARCHAR2(6) NOT NULL | Fiscal Regime |
| 16 | FIRST_FY_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | First Fiscal Year Amount. Used for the Minimum Lease Obligation (Government) Report. |
| 17 | LEASE_START_DT | Date(10) | DATE | Lease Start Date |
| 18 | LEASE_END_DT | Date(10) | DATE | Lease End Date |
| 19 | PROPERTY_NM | Character(50) | VARCHAR2(50) NOT NULL | Used to store an Asset Property Name |
| 20 | ASSET_ID | Character(12) | VARCHAR2(12) NOT NULL | Asset Identification |
| 21 | BUSINESS_UNIT_AM | Character(5) | VARCHAR2(5) NOT NULL | AM Business Unit |