RE_AM_CR_SLB_VW(SQL View) |
Index Back |
|---|---|
Lease Chart Temp View |
| SELECT A.BUSINESS_UNIT , A.ASSET_ID ,A.GUAR_RESIDUAL ,'Y' , B1.LEASE_BOOK , B.CAL_DEPR_PD , C.SETID ,D.FISCAL_YEAR ,D.ACCOUNTING_PERIOD , C1.END_DT , C1.CF_SEQNO , 0 , %Sql(RE_BASE_AMT_CONV, C1.DECIMAL_POSITIONS, C1.ACCR_DEFR_AMT, C1.RATE_MULT, C1.RATE_DIV) ,B.CURRENCY_CD , ' ' , C1.CURRENCY_CD , ' ' , C1.ASSET_CLASSIFY , C1.PRORATE_LS_EXP FROM PS_LEASE A , PS_BU_BOOK_TBL B , PS_BUS_UNIT_TBL_AM B1 , PS_SET_CNTRL_REC C , PS_CAL_DETP_TBL D , PS_RE_LS_SL C1 WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND B.BUSINESS_UNIT = B1.BUSINESS_UNIT AND B1.LEASE_BOOK = B.BOOK AND C.SETCNTRLVALUE = A.BUSINESS_UNIT AND C.RECNAME='CAL_DEFN_TBL' AND C.SETID = D.SETID AND D.CALENDAR_ID = B.CAL_DEPR_PD AND A.BUSINESS_UNIT = C1.BUSINESS_UNIT AND A.ASSET_ID = C1.ASSET_ID AND C1.TERM_RENT_TYPE IN ('04','09','10') AND C1.END_DT BETWEEN D.BEGIN_DT AND D.END_DT UNION ALL SELECT A.BUSINESS_UNIT , A.ASSET_ID ,A.GUAR_RESIDUAL ,'Y' , B1.LEASE_BOOK , B.CAL_DEPR_PD , C.SETID ,D.FISCAL_YEAR ,D.ACCOUNTING_PERIOD , C1.END_DT , C1.CF_SEQNO , 0 , C1.ACR_DFR_AMT_BS ,B.CURRENCY_CD , ' ' , C1.CURRENCY_CD , ' ' , C1.ASSET_CLASSIFY , C1.PRORATE_LS_EXP FROM PS_LEASE A , PS_BU_BOOK_TBL B , PS_BUS_UNIT_TBL_AM B1 , PS_SET_CNTRL_REC C , PS_CAL_DETP_TBL D , ( SELECT B.BUSINESS_UNIT_AM AS BUSINESS_UNIT , B.ASSET_ID , B.ASSET_CLASSIFY , A.LEASE_END_DT AS END_DT , CASE WHEN STL.TERM_RENT_TYPE = '09' THEN %Coalesce(C.CF_SEQNO_INC, B.CF_SEQNO_INC) ELSE %Coalesce(C.CF_SEQNO_EXP, B.CF_SEQNO_EXP) END AS CF_SEQNO , A.CURRENCY_CD , CASE WHEN STL.TERM_RENT_TYPE = '09' THEN %Coalesce(C.ADJ_AMOUNT_LN_BSE, B.ADJ_AMOUNT_LN_BSE) ELSE %Coalesce(C.ADJ_AMOUNT_RE_BSE, B.ADJ_AMOUNT_RE_BSE) END AS ACR_DFR_AMT_BS , A.PRORATE_LS_EXP FROM PS_RE_LS A INNER JOIN PS_RE_STLINE STL ON (STL.LS_KEY = A.LS_KEY) INNER JOIN PS_RE_LS_PPTY B ON (B.LS_KEY = A.LS_KEY) LEFT OUTER JOIN PS_RE_LS_AST_DST C ON (C.LS_KEY = B.LS_KEY AND C.ASSET_SEQ_NBR = B.ASSET_SEQ_NBR) WHERE A.LEASE_OBLG = '1' AND A.LS_CLASSIFICATION = 'C' AND STL.REGENERATE = 'N' AND STL.TERM_RENT_TYPE IN ('04','09','10') AND EXISTS ( SELECT 'X' FROM PS_RE_LSA AMND WHERE A.LS_KEY = AMND.LS_KEY)) C1 WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND B.BUSINESS_UNIT = B1.BUSINESS_UNIT AND B1.LEASE_BOOK = B.BOOK AND C.SETCNTRLVALUE = A.BUSINESS_UNIT AND C.RECNAME='CAL_DEFN_TBL' AND C.SETID = D.SETID AND D.CALENDAR_ID = B.CAL_DEPR_PD AND A.BUSINESS_UNIT = C1.BUSINESS_UNIT AND A.ASSET_ID = C1.ASSET_ID AND C1.END_DT BETWEEN D.BEGIN_DT AND D.END_DT |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  BUSINESS_UNIT |
Character(5) | VARCHAR2(5) NOT NULL |
Business Unit
Default Value: OPR_DEF_TBL_FS.BUSINESS_UNIT |
| 2 | ASSET_ID |
Character(12) | VARCHAR2(12) NOT NULL | Asset Identification |
| 3 | GUAR_RESIDUAL | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Guaranteed Residual Value |
| 4 | LEASE_TYPE | Character(1) | VARCHAR2(1) NOT NULL |
Lease Type
C=Capital O=Operating |
| 5 | LEASE_BOOK | Character(10) | VARCHAR2(10) NOT NULL | Book used by Lease |
| 6 | CAL_DEPR_PD | Character(2) | VARCHAR2(2) NOT NULL | Calendar |
| 7 | CAL_SETID | Character(5) | VARCHAR2(5) NOT NULL | Calendar SetID |
| 8 | FISCAL_YEAR | Number(4,0) | SMALLINT NOT NULL | Fiscal Year |
| 9 | ACCOUNTING_PERIOD | Number(3,0) | SMALLINT NOT NULL | Identifies a time period to which you post transactions. Typically, an accounting period represents a month, but it can also represent a week, a day, or any user-defined interval. An accounting period has a beginning date and an ending date, and is defined in the calendar table. |
| 10 | PYMNT_DT | Date(10) | DATE | Payment Date |
| 11 | CF_SEQNO | Number(6,0) | INTEGER NOT NULL | Chartfield Sequence Number |
| 12 | INTRST_EXPENSE_BSE | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Interest Expense |
| 13 | OBLG_REDUCE_BSE | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Obligation Reduction |
| 14 | CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Currency Code |
| 15 | SCHED_PYMNT_TYPE | Character(1) | VARCHAR2(1) NOT NULL |
Lease Payment Type
C=Capital Interim Rent I=Interim Rent N=Normal Rent R=Reversal Payment S=Reversal Interim T=Reversal Capital Interim X=Payment Reverted Y=Interim Reverted Z=Capital Interim Reverted |
| 16 | TXN_CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Transaction Currency |
| 17 | FIRST_PYMNT_SW | Character(1) | VARCHAR2(1) NOT NULL | First Payment Switch |
| 18 | ASSET_CLASSIFY | Character(1) | VARCHAR2(1) NOT NULL |
Field for Lease Classification
C=Finance O=Operating |
| 19 | PRORATE_LS_EXP | Character(1) | VARCHAR2(1) NOT NULL | Recognize Monthly Expense |