RE_MN_OB_CON_VW(SQL View) |
Index Back |
|---|---|
Min Oblg Piv VwView created to resolve Sybase issue with the base view for Pivot grid |
| 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 |
| # | 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 | 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. |
| 7 | LSE_TYPE_CD | Character(10) | VARCHAR2(10) NOT NULL | This field contains the code for the Lease Types |
| 8 | LEASE_OBLG | Character(1) | VARCHAR2(1) NOT NULL |
Lease Obligation either Payables or Receivables Lease
1=Payables 2=Receivables |
| 9 | LS_ADMIN | Number(10,0) | DECIMAL(10) NOT NULL | Lease Administrator |
| 10 | PRTFL_MGR | Number(10,0) | DECIMAL(10) NOT NULL | Portfolio Manager |
| 11 | LS_KEY | Number(15,0) | DECIMAL(15) NOT NULL | Lease Key, Unique Key |
| 12 | LS_NBR | Character(10) | VARCHAR2(10) NOT NULL | Lease Number, User Enterable or Generated Sequence Number |
| 13 | LEASE_NAME | Character(60) | VARCHAR2(60) NOT NULL | Lease Name |
| 14 | 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 |
| 15 | CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Currency Code |
| 16 | FISCAL_YEAR | Number(4,0) | SMALLINT NOT NULL | Fiscal Year |
| 17 | AMOUNT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Amount |
| 18 | LEASE_START_DT | Date(10) | DATE | Lease Start Date |
| 19 | LEASE_END_DT | Date(10) | DATE | Lease End Date |
| 20 | PROPERTY_NM | Character(50) | VARCHAR2(50) NOT NULL | Used to store an Asset Property Name |
| 21 | ASSET_ID | Character(12) | VARCHAR2(12) NOT NULL | Asset Identification |
| 22 | BUSINESS_UNIT_AM | Character(5) | VARCHAR2(5) NOT NULL | AM Business Unit |