RE_LS_AM_INT_VW

(SQL View)
 Index Back

Lease Interest by Asset

View to get the Interest Expense by asset

SELECT B.LS_KEY , AMND.AMND_NBR , AMND.TRANS_DT , AMND.ACCOUNTING_DT , G.LS_CLASSIFICATION , C.BUSINESS_UNIT_AM , C.ASSET_ID , C.ASSET_SEQ_NBR , C.ASSET_CLASSIFY , C.ROUNDING_APPLIED , A.TERM_SCHED_ID , A.SEQ_NUM , A.SCHED_TYPE , A.SCHED_DATE , B.SCHED_DATE , CASE WHEN (B.SCHED_DATE = G.LEASE_START_DT AND E.LEASE_PYMT_TYPE = 'A' AND A.SEQ_NUM = 1) THEN 'Y' ELSE 'N' END , CASE WHEN (B.SCHED_DATE = G.LEASE_START_DT AND E.LEASE_PYMT_TYPE = 'A') THEN E.INT_WITH_AP_BI ELSE ' ' END , G.PRORATE_LS_EXP , C.DST_ALLOC_METHOD , DST.DISTRIBUTION_ID , DST.PERCENTAGE , %Coalesce(DST.CF_SEQNO, C.CF_SEQNO) , %Coalesce( CASE WHEN (B.SCHED_DATE = G.LEASE_START_DT AND E.LEASE_PYMT_TYPE = 'A') THEN DST.CF_SEQNO_EXP ELSE DST.CF_SEQNO END, CASE WHEN (B.SCHED_DATE = G.LEASE_START_DT AND E.LEASE_PYMT_TYPE = 'A') THEN C.CF_SEQNO_EXP ELSE C.CF_SEQNO END) , B.CURRENCY_CD , CASE WHEN B.SCHED_DATE = G.LEASE_START_DT AND E.LEASE_PYMT_TYPE = 'A' THEN %Sql(RE_ASSET_AMT_CALC, D.DECIMAL_POSITIONS, A.INTEREST_EXPENSE, C.PRORATION_RATE) ELSE %Sql(RE_ASSET_AMT_CALC, D.DECIMAL_POSITIONS, A.INTEREST_EXPENSE, C.PRORATION_RATE) END , CASE WHEN B.SCHED_DATE = G.LEASE_START_DT AND E.LEASE_PYMT_TYPE = 'A' THEN %Sql(RE_ASSET_AMT_CALC, D.DECIMAL_POSITIONS, A.INTEREST_ACCRUAL, C.PRORATION_RATE) ELSE %Sql(RE_ASSET_AMT_CALC, D.DECIMAL_POSITIONS, A.INTEREST_ACCRUAL, C.PRORATION_RATE) END , %Sql(RE_ASSET_AMT_CALC, D.DECIMAL_POSITIONS, A.AMORTIZATION_AMT, C.IDC_PRORATION_RATE) , A.INTEREST_EXPENSE , A.INTEREST_ACCRUAL , A.AMORTIZATION_AMT , C.RATE_MULT , C.RATE_DIV , D.DECIMAL_POSITIONS , D2.DECIMAL_POSITIONS FROM PS_RE_LS_INT_SCHED A , PS_RE_LS_TRM_SCHED B , PS_BU_AM_BOOK_VW BK , PS_CURRENCY_CD_TBL D2 , PS_CURRENCY_CD_TBL D , PS_RE_LSA_TRM E , PS_INSTALLATION_RE F , PS_RE_LS G , PS_RE_LSA AMND , PS_RE_LSA_PPTY C LEFT OUTER JOIN PS_RE_LSA_AST_DST DST ON (DST.LS_KEY = C.LS_KEY AND DST.AMND_NBR = C.AMND_NBR AND DST.ASSET_SEQ_NBR = C.ASSET_SEQ_NBR) WHERE A.TERM_SCHED_ID = B.TERM_SCHED_ID AND B.LS_KEY = G.LS_KEY AND B.LS_KEY = C.LS_KEY AND AMND.LS_KEY = B.LS_KEY AND AMND.AMND_NBR = C.AMND_NBR AND B.CURRENCY_CD = D.CURRENCY_CD AND E.LS_KEY = B.LS_KEY AND E.AMND_NBR = B.AMND_NBR AND E.TERM_ID = B.TERM_ID AND BK.BUSINESS_UNIT = C.BUSINESS_UNIT_AM AND BK.BOOK = BK.LEASE_BOOK AND D2.CURRENCY_CD = BK.CURRENCY_CD AND %EffdtCheck(CURRENCY_CD_TBL, D, %CURRENTDATEIN) AND %EffdtCheck(CURRENCY_CD_TBL, D2, %CURRENTDATEIN) AND D.EFF_STATUS = 'A' AND D2.EFF_STATUS = 'A' AND E.TERM_RENT_TYPE NOT IN ('04','09','10') AND E.CAPITALIZE_SW = 'Y' AND G.LEASE_OBLG = '1' AND C.AMND_NBR = %Coalesce( (SELECT MAX(AMND_NBR) FROM PS_RE_LSA H WHERE H.LS_KEY = A.LS_KEY AND H.AMND_STATUS <> '1' AND (H.TRANS_DT <= A.SCHED_DATE) ), 0)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 LS_KEY Number(15,0) DECIMAL(15) NOT NULL Lease Key, Unique Key
2 AMND_NBR Number(3,0) SMALLINT NOT NULL Amendment Number
3 TRANS_DT Date(10) DATE Transaction Date
4 ACCOUNTING_DT Date(10) DATE The accounting entry construction date for a given transaction (a generic field that crosses multiple
5 LS_CLASSIFICATION Character(1) VARCHAR2(1) NOT NULL Field for Lease Classification
C=Finance
O=Operating
6 BUSINESS_UNIT_AM Character(5) VARCHAR2(5) NOT NULL AM Business Unit
7 ASSET_ID Character(12) VARCHAR2(12) NOT NULL Asset Identification
8 ASSET_SEQ_NBR Number(5,0) INTEGER NOT NULL Asset Sequence Number
9 ASSET_CLASSIFY Character(1) VARCHAR2(1) NOT NULL Field for Lease Classification
C=Finance
O=Operating
10 ROUNDING_APPLIED Character(1) VARCHAR2(1) NOT NULL Rounding Applied
N=No
Y=Yes
11 TERM_SCHED_ID Number(30,0) DECIMAL(30) NOT NULL Lease Term Schedule ID
12 SEQ_NUM Number(3,0) SMALLINT NOT NULL Sequence
13 SCHED_TYPE Character(1) VARCHAR2(1) NOT NULL Schedule Type
C=Capital Interim Rent
I=Interim Rent
N=Normal Rent
R=Reversal Normal Rent
S=Reversal Interim Rent
T=Reversal Capital Interim
X=Normal Rent Reverted
Y=Interim Reverted
Z=Capital Interim Reverted

Default Value: N

14 SCHED_DATE Date(10) DATE Schedule Date
15 PYMNT_DT Date(10) DATE Payment Date
16 FIRST_PYMNT_SW Character(1) VARCHAR2(1) NOT NULL First Payment Switch
17 INT_WITH_AP_BI Character(1) VARCHAR2(1) NOT NULL Integrate with Accounts Payable
18 PRORATE_LS_EXP Character(1) VARCHAR2(1) NOT NULL Recognize Monthly Expense
19 DST_ALLOC_METHOD Character(1) VARCHAR2(1) NOT NULL Distribution Allocation Method
1=Percentage
2=Amount
3=Area
4=None
5=Quantity
20 DISTRIBUTION_ID Number(5,0) INTEGER NOT NULL Distribution ID
21 PERCENTAGE Signed Number(7,2) DECIMAL(5,2) NOT NULL Percentage
22 CF_SEQNO Number(6,0) INTEGER NOT NULL Chartfield Sequence Number
23 CF_SEQNO_EXP Number(6,0) INTEGER NOT NULL Chartfield Sequence Number
24 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
25 INTEREST_EXPENSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Interest Expense
26 INTEREST_ACCRUAL Signed Number(28,3) DECIMAL(26,3) NOT NULL Interest calculated
27 AMORTIZATION_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Amortization Amount
28 AMOUNT_1 Signed Number(28,3) DECIMAL(26,3) NOT NULL Amount Work Field
29 AMOUNT_2 Signed Number(28,3) DECIMAL(26,3) NOT NULL Amount Work Field
30 AMOUNT_3 Signed Number(28,3) DECIMAL(26,3) NOT NULL Amount
31 RATE_MULT Signed Number(17,8) DECIMAL(15,8) NOT NULL Rate Multiplier
32 RATE_DIV Number(16,8) DECIMAL(15,8) NOT NULL Rate Divisor
33 DECIMAL_POSITIONS Number(2,0) SMALLINT NOT NULL Decimal Positions
34 DECIMAL_POSITIONS2 Number(2,0) SMALLINT NOT NULL Decimal Positions