RE_PYMT_TYPE_VW(SQL View) |
Index Back |
|---|
| SELECT DISTINCT A.BUSINESS_UNIT , A.LS_KEY , A.LS_NBR , A.LEASE_NAME , A.LEASE_START_DT , A.LEASE_END_DT , B.LEASE_PYMT_TYPE , B.PAYMENT_GROUP , B.TXN_GRP , MIN(B.EFFDT_FROM) FROM PS_RE_LS A , PS_RE_LS_TRM B WHERE A.LS_KEY = B.LS_KEY AND A.LEASE_OBLG = '1' AND A.LS_CLASSIFICATION = 'O' AND A.RECOGNITION_EXEMPT = 'N' AND A.SYSTEM_SOURCE <> 'BAM' AND B.TXN_GRP = 1 GROUP BY A.BUSINESS_UNIT, A.LS_KEY, A.LS_NBR, A.LEASE_NAME, A.LEASE_START_DT , A.LEASE_END_DT, B.LEASE_PYMT_TYPE, B.PAYMENT_GROUP, B.TXN_GRP UNION SELECT DISTINCT A.BUSINESS_UNIT , A.LS_KEY , A.LS_NBR , A.LEASE_NAME , A.LEASE_START_DT , A.LEASE_END_DT , B.LEASE_PYMT_TYPE , B.PAYMENT_GROUP , B.TXN_GRP , MIN(B.EFFDT_FROM) FROM PS_RE_LS A , PS_RE_LS_TRM B , PS_RE_MISC_FEES M WHERE A.LS_KEY = B.LS_KEY AND A.LEASE_OBLG = '1' AND A.LS_CLASSIFICATION = 'O' AND A.RECOGNITION_EXEMPT = 'N' AND A.SYSTEM_SOURCE <> 'BAM' AND B.TXN_GRP = 6 AND B.TERM_RENT_TYPE = '03' AND B.CHG_GRP_CD = M.CHG_GRP_CD AND M.CAPITALIZE_SW = 'Y' AND M.EFF_STATUS = 'A' AND M.EFFDT = ( SELECT MAX(M1.EFFDT) FROM PS_RE_MISC_FEES M1 WHERE M.SETID = M1.SETID AND M.CHG_GRP_CD = M1.CHG_GRP_CD AND M.EFFDT <= %CurrentDateIn) GROUP BY A.BUSINESS_UNIT, A.LS_KEY, A.LS_NBR, A.LEASE_NAME, A.LEASE_START_DT , A.LEASE_END_DT, B.LEASE_PYMT_TYPE, B.PAYMENT_GROUP, B.TXN_GRP UNION SELECT DISTINCT A.BUSINESS_UNIT , A.LS_KEY , A.LS_NBR , A.LEASE_NAME , A.LEASE_START_DT , A.LEASE_END_DT , B.LEASE_PYMT_TYPE , B.PAYMENT_GROUP , B.TXN_GRP , MIN(B.EFFDT_FROM) FROM PS_RE_LS A , PS_RE_LS_TRM B , PS_RE_LS_OPEX_CTG CTG , PS_RE_EXP_CLS CLS WHERE A.LS_KEY = B.LS_KEY AND A.LEASE_OBLG = '1' AND A.LS_CLASSIFICATION = 'O' AND A.RECOGNITION_EXEMPT = 'N' AND A.SYSTEM_SOURCE <> 'BAM' AND B.TXN_GRP = 4 AND B.TERM_ID = CTG.TERM_ID AND CTG.CHG_GRP_CD = CLS.CHG_GRP_CD AND CLS.CAPITALIZE_SW = 'Y' AND CLS.EFF_STATUS = 'A' AND CLS.EFFDT = ( SELECT MAX(CLS1.EFFDT) FROM PS_RE_EXP_CLS CLS1 WHERE CLS.SETID = CLS1.SETID AND CLS.CHG_GRP_CD = CLS1.CHG_GRP_CD AND CLS.EFFDT <= %CurrentDateIn) AND NOT EXISTS( SELECT 'X' FROM PS_RE_LS_OPEX_CTG CTG2 , PS_RE_EXP_CLS CLS2 WHERE B.TERM_ID = CTG2.TERM_ID AND CTG.CHG_GRP_CD <> CTG2.CHG_GRP_CD AND CTG2.CHG_GRP_CD = CLS2.CHG_GRP_CD AND CLS2.CAPITALIZE_SW = 'N' AND CLS2.EFF_STATUS = 'A' AND CLS2.EFFDT = ( SELECT MAX(CLS21.EFFDT) FROM PS_RE_EXP_CLS CLS21 WHERE CLS2.SETID = CLS21.SETID AND CLS2.CHG_GRP_CD = CLS21.CHG_GRP_CD AND CLS2.EFFDT <= %CurrentDateIn)) GROUP BY A.BUSINESS_UNIT, A.LS_KEY, A.LS_NBR, A.LEASE_NAME, A.LEASE_START_DT , A.LEASE_END_DT, B.LEASE_PYMT_TYPE, B.PAYMENT_GROUP, B.TXN_GRP |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 2 | LS_KEY | Number(15,0) | DECIMAL(15) NOT NULL | Lease Key, Unique Key |
| 3 | LS_NBR | Character(10) | VARCHAR2(10) NOT NULL | Lease Number, User Enterable or Generated Sequence Number |
| 4 | LEASE_NAME | Character(60) | VARCHAR2(60) NOT NULL | Lease Name |
| 5 | LEASE_START_DT | Date(10) | DATE | Lease Start Date |
| 6 | LEASE_END_DT | Date(10) | DATE | Lease End Date |
| 7 | LEASE_PYMT_TYPE | Character(1) | VARCHAR2(1) NOT NULL |
Lease Payment Type
A=Advance O=One Time R=Arrears |
| 8 | PAYMENT_GROUP | Number(3,0) | SMALLINT NOT NULL | Field For Payment Group |
| 9 | 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 |
| 10 | LEASE_PYMT_STRT_DT | Date(10) | DATE | Payment Start Date |