RE_AM_CHARTB_VW

(SQL View)
 Index Back

Lease Chart Temp View


SELECT A.BUSINESS_UNIT , A.ASSET_ID ,A.GUAR_RESIDUAL ,C1.CAPITALIZE_SW , B1.LEASE_BOOK , B.CAL_DEPR_PD , C.SETID ,D.FISCAL_YEAR ,D.ACCOUNTING_PERIOD , C1.SCHED_DATE , C1.CF_SEQNO , C1.CF_SEQNO_EXP , C1.INTRST_AMOUNT_BSE , C1.AMORTIZ_AMT_BSE ,B.CURRENCY_CD , C1.SCHED_TYPE , C1.CURRENCY_CD , C1.FIRST_PYMNT_SW , 'O' AS 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 BUSINESS_UNIT , ASSET_ID , 'O' AS ASSET_CLASSIFY ,CAPITALIZE_SW , FIRST_PYMNT_SW ,PRORATE_LS_EXP , SCHED_DATE , SCHED_TYPE , CF_SEQNO , CF_SEQNO_EXP , CURRENCY_CD , %Sql(RE_BASE_AMT_CONV, DECIMAL_POSITIONS2, INTEREST_AMOUNT, RATE_MULT, RATE_DIV) AS INTRST_AMOUNT_BSE , %Sql(RE_BASE_AMT_CONV, DECIMAL_POSITIONS2, OBLIGATION_REDUCE, RATE_MULT, RATE_DIV) AS AMORTIZ_AMT_BSE FROM PS_RE_LS_AM_SRC WHERE ASSET_CLASSIFY = 'O' AND TERM_RENT_TYPE NOT IN ('04', '10') UNION ALL SELECT B.BUSINESS_UNIT_AM AS BUSINESS_UNIT , B.ASSET_ID , 'O' AS ASSET_CLASSIFY , 'N' AS CAPITALIZE_SW , 'N' AS FIRST_PYMNT_SW ,'N' AS PRORATE_LS_EXP , A.SCHED_DATE ,'N' AS SCHED_TYPE , -1 AS CF_SEQNO , -1 AS CF_SEQNO_EXP , C.CURRENCY_CD , 0 AS INTRST_AMOUNT_BSE , 1 AS AMORTIZ_AMT_BSE FROM PS_RE_LS_TXN A , PS_RE_LSA_PPTY B , PS_RE_LS C , PS_RE_BUS_UNIT D WHERE A.TXN_GRP NOT IN ('1', '4', '6') AND A.LS_KEY = B.LS_KEY AND A.LEASE_OBLG = '1' AND A.LS_KEY = C.LS_KEY AND C.BUSINESS_UNIT = D.BUSINESS_UNIT AND D.USE_AM_AET = 'Y' AND A.LS_TXN_STATUS IN ('1','2','5','7') AND B.ASSET_CLASSIFY = 'O' AND B.AMND_NBR = ( SELECT MAX(E.AMND_NBR) FROM PS_RE_LSA E WHERE E.LS_KEY = C.LS_KEY AND E.AMND_STATUS <> '1') ) 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.SCHED_DATE BETWEEN D.BEGIN_DT AND D.END_DT UNION ALL SELECT A.BUSINESS_UNIT , A.ASSET_ID ,A.GUAR_RESIDUAL ,C1.CAPITALIZE_SW , B1.LEASE_BOOK , B.CAL_DEPR_PD , C.SETID ,D.FISCAL_YEAR ,D.ACCOUNTING_PERIOD , C1.SCHED_DATE , C1.CF_SEQNO , C1.CF_SEQNO_EXP , C1.INTRST_AMOUNT_BSE , C1.AMORTIZ_AMT_BSE ,B.CURRENCY_CD , C1.SCHED_TYPE , C1.CURRENCY_CD , C1.FIRST_PYMNT_SW , 'O' AS 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 , 'O' AS ASSET_CLASSIFY , %Coalesce((SELECT MAX(SCH.SCHED_DATE) FROM PS_RE_LS_TRM_SCHED SCH, PS_RE_LS_TRM TRM WHERE SCH.LS_KEY = TRM.LS_KEY AND SCH.TERM_ID = TRM.TERM_ID AND SCH.LS_KEY = A.LS_KEY AND SCHED_TYPE = 'N' AND CAPITALIZE_SW = 'Y'), A.LEASE_END_DT) AS SCHED_DATE , %Coalesce(C.CF_SEQNO, B.CF_SEQNO) AS CF_SEQNO , %Coalesce(C.CF_SEQNO, B.CF_SEQNO) AS CF_SEQNO_EXP , %DecMult(%Coalesce(C.ADJ_AMOUNT_LO_BSE, B.ADJ_AMOUNT_LO_BSE) , -1) AS INTRST_AMOUNT_BSE , %Coalesce(C.ADJ_AMOUNT_A1_BSE, B.ADJ_AMOUNT_A1_BSE) AS AMORTIZ_AMT_BSE , A.CURRENCY_CD , 'N' AS SCHED_TYPE , 'Y' AS CAPITALIZE_SW , 'N' AS FIRST_PYMNT_SW , A.PRORATE_LS_EXP FROM PS_RE_LSA AMND INNER JOIN PS_RE_LS A ON (A.LS_KEY = AMND.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 B.ASSET_CLASSIFY = 'O' AND AMND.AMND_NBR = ( SELECT MAX(LSA.AMND_NBR) FROM PS_RE_LSA LSA WHERE LSA.LS_KEY = A.LS_KEY AND AMND_STATUS <> '1')) 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.SCHED_DATE 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 CF_SEQNO_EXP Number(6,0) INTEGER NOT NULL Chartfield Sequence Number
13 INTRST_EXPENSE_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Interest Expense
14 AMORTIZ_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Amortization Amount
15 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
16 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
17 TXN_CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Transaction Currency
18 FIRST_PYMNT_SW Character(1) VARCHAR2(1) NOT NULL First Payment Switch
19 ASSET_CLASSIFY Character(1) VARCHAR2(1) NOT NULL Field for Lease Classification
C=Finance
O=Operating
20 PRORATE_LS_EXP Character(1) VARCHAR2(1) NOT NULL Recognize Monthly Expense