RE_AM_PR_OPB_VW(SQL View) |
Index Back |
|---|---|
Lease Chart Temp View |
| SELECT A.BUSINESS_UNIT , A.ASSET_ID , A.GUAR_RESIDUAL , 'Y' , B1.LEASE_BOOK , B2.CAL_DEPR_PD , C1.SETID , D.FISCAL_YEAR , D.ACCOUNTING_PERIOD , B.CF_SEQNO , B.SCHED_DATE , B.INTRST_EXPENSE_BSE , CASE WHEN D.FISCAL_YEAR = C.FISCAL_YEAR AND D.ACCOUNTING_PERIOD = C.ACCOUNTING_PERIOD THEN (B.INTRST_ACCRUAL_BSE * -1) ELSE B.INTRST_EXPENSE_BSE END , 0 , B.AMORTIZ_AMT_BSE , B2.CURRENCY_CD , B.SCHED_TYPE , B.CURRENCY_CD , B.FIRST_PYMNT_SW , B.ASSET_CLASSIFY , B.PRORATE_LS_EXP FROM PS_LEASE A , PS_BU_BOOK_TBL B2 , PS_BUS_UNIT_TBL_AM B1 , PS_SET_CNTRL_REC C1 , ( SELECT MIE.SCHED_TYPE , 'O' AS ASSET_CLASSIFY , MIE.BUSINESS_UNIT , MIE.ASSET_ID , MIE.SCHED_DATE , MIE.PYMNT_DT , MIE.FIRST_PYMNT_SW , MIE.PRORATE_LS_EXP , MIE.CF_SEQNO , MIE.CURRENCY_CD , %Sql(RE_BASE_AMT_CONV, MIE.DECIMAL_POSITIONS2, MIE.INTEREST_EXPENSE, MIE.RATE_MULT, MIE.RATE_DIV) AS INTRST_EXPENSE_BSE , %Sql(RE_BASE_AMT_CONV, MIE.DECIMAL_POSITIONS2, MIE.INTEREST_ACCRUAL, MIE.RATE_MULT, MIE.RATE_DIV) AS INTRST_ACCRUAL_BSE , %Sql(RE_BASE_AMT_CONV, MIE.DECIMAL_POSITIONS2, MIE.AMORTIZATION_AMT, MIE.RATE_MULT, MIE.RATE_DIV) AS AMORTIZ_AMT_BSE FROM PS_RE_LS_AM_MIE MIE WHERE MIE.ASSET_CLASSIFY = 'O' ) B , PS_CAL_DETP_TBL C , PS_CAL_DETP_TBL D WHERE A.BUSINESS_UNIT = B2.BUSINESS_UNIT AND B2.BUSINESS_UNIT = B1.BUSINESS_UNIT AND B1.LEASE_BOOK = B2.BOOK AND C1.SETCNTRLVALUE = A.BUSINESS_UNIT AND C1.RECNAME='CAL_DEFN_TBL' AND D.SETID = C1.SETID AND D.CALENDAR_ID = B2.CAL_DEPR_PD AND B.SCHED_DATE BETWEEN D.BEGIN_DT AND D.END_DT AND A.BUSINESS_UNIT=B.BUSINESS_UNIT AND A.ASSET_ID =B.ASSET_ID AND C.SETID = C1.SETID AND C.CALENDAR_ID = B2.CAL_DEPR_PD AND B.PYMNT_DT BETWEEN C.BEGIN_DT AND C.END_DT UNION ALL SELECT A.BUSINESS_UNIT , A.ASSET_ID , A.GUAR_RESIDUAL , A.LEASE_TYPE , A.LEASE_BOOK , A.CAL_DEPR_PD , A.CAL_SETID , A.FISCAL_YEAR , A.ACCOUNTING_PERIOD , A.CF_SEQNO , A.PYMNT_DT , A.INTRST_EXPENSE_BSE , 0 , A.OBLG_REDUCE_BSE , 0 , A.CURRENCY_CD , 'N' , A.TXN_CURRENCY_CD , 'N' , A.ASSET_CLASSIFY , A.PRORATE_LS_EXP FROM PS_RE_AM_CR_SLY_VW A WHERE A.PRORATE_LS_EXP = 'Y' AND A.ASSET_CLASSIFY = 'O' |
| # | 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 | CF_SEQNO | Number(6,0) | INTEGER NOT NULL | Chartfield Sequence Number |
| 11 | PYMNT_DT | Date(10) | DATE | Payment Date |
| 12 | INTRST_EXPENSE_BSE | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Interest Expense |
| 13 | INTRST_ACCRUAL_BSE | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Interest calculated |
| 14 | OBLG_REDUCE_BSE | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Obligation Reduction |
| 15 | AMORTIZ_AMT_BSE | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Amortization Amount |
| 16 | CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Currency Code |
| 17 | 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 |
| 18 | TXN_CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Transaction Currency |
| 19 | FIRST_PYMNT_SW | Character(1) | VARCHAR2(1) NOT NULL | First Payment Switch |
| 20 | ASSET_CLASSIFY | Character(1) | VARCHAR2(1) NOT NULL |
Field for Lease Classification
C=Finance O=Operating |
| 21 | PRORATE_LS_EXP | Character(1) | VARCHAR2(1) NOT NULL | Recognize Monthly Expense |