RE_AM_PY_RT_VW

(SQL View)
 Index Back

Lease Chart Temp Table


SELECT A.BUSINESS_UNIT , A.ASSET_ID , A.LEASE_BOOK , A.SCHED_PYMNT_TYPE , A.LEASE_TYPE , A.CF_SEQNO , A.CF_SEQNO , A.PYMNT_DT , A.ASSET_CLASSIFY , A.TERM_RENT_TYPE , SUM(A.INTEREST_EXPENSE) , SUM(A.OBLIGATION_REDUCE) , SUM(A.INTRST_EXPENSE_BSE) , SUM(A.OBLG_REDUCE_BSE) , A.CURRENCY_CD , A.TXN_CURRENCY_CD , A.TRANS_DT , A.ACCOUNTING_DT , A.CAL_SETID , A.CAL_DEPR_PD , CASE WHEN A.LEASE_EXPIRED_FLG<>'Y' THEN 'N' ELSE CASE WHEN A.LEASE_EXPIRED_FLG = 'Y' THEN 'Y' END END , A.PRORATE_LS_EXP FROM PS_RE_AM_CRT_SL_VW A GROUP BY A.BUSINESS_UNIT , A.ASSET_ID , A.LEASE_BOOK , A.SCHED_PYMNT_TYPE , A.LEASE_TYPE , A.CF_SEQNO , A.PYMNT_DT , A.ASSET_CLASSIFY , A.TERM_RENT_TYPE , A.CURRENCY_CD , A.TXN_CURRENCY_CD , A.TRANS_DT , A.ACCOUNTING_DT , A.CAL_SETID , A.CAL_DEPR_PD , A.PRORATE_LS_EXP , A.LEASE_EXPIRED_FLG UNION ALL SELECT b.Business_Unit , b.Asset_ID ,c.book ,'N' ,'Y' ,c.cf_seqno ,c.cf_seqno ,c.trans_dt ,d.asset_classify ,'00' ,0 ,c.amount ,0 ,c.amount_bse ,b.currency_cd ,d.currency_cd , c.TRANS_DT , c.ACCOUNTING_DT , b.CAL_SETID , b.CAL_DEPR_PD ,'N' , e.PRORATE_LS_EXP FROM ps_Cal_Detp_Tbl a , PS_LEASE_CHART1_VW b , PS_RE_LS_Ast_pret c ,PS_RE_LSA_PPTY d ,PS_RE_LS e WHERE a.SetID = b.Cal_SetID AND a.Calendar_ID = b.Cal_Depr_Pd AND b.Business_Unit = c.Business_Unit_am AND b.Asset_ID = c.Asset_ID AND c.Trans_dt BETWEEN a.Begin_Dt AND a.End_Dt AND c.ls_key = d.ls_key AND c.amnd_nbr = d.amnd_nbr AND e.ls_key = d.ls_key AND C.TRF_TYPE IN ('RT')

# 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 LEASE_BOOK Character(10) VARCHAR2(10) NOT NULL Book used by Lease
4 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
5 LEASE_TYPE Character(1) VARCHAR2(1) NOT NULL Lease Type
C=Capital
O=Operating
6 CF_SEQNO Number(6,0) INTEGER NOT NULL Chartfield Sequence Number
7 CF_SEQNO_EXP Number(6,0) INTEGER NOT NULL Chartfield Sequence Number
8 PYMNT_DT Date(10) DATE Payment Date
9 ASSET_CLASSIFY Character(1) VARCHAR2(1) NOT NULL Field for Lease Classification
C=Finance
O=Operating
10 TERM_RENT_TYPE Character(2) VARCHAR2(2) NOT NULL List of Rent Type for Transaction Group
00=Residual Value
01=Base Rent
02=Operating Expense
03=Miscellaneous Rent
04=Initial Direct Cost
05=Prepaid Rent
06=Purchase Option
07=Renewal Option
08=Penality Option
09=Lease Incentives
10=Prepaid Rent
11 INTEREST_EXPENSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Interest Expense
12 OBLIGATION_REDUCE Signed Number(28,3) DECIMAL(26,3) NOT NULL Obligation Reduction
13 INTRST_EXPENSE_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Interest Expense
14 OBLG_REDUCE_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Obligation Reduction
15 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
16 TXN_CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Transaction Currency
17 TRANS_DT Date(10) DATE Transaction Date
18 ACCOUNTING_DT Date(10) DATE The accounting entry construction date for a given transaction (a generic field that crosses multiple
19 CAL_SETID Character(5) VARCHAR2(5) NOT NULL Calendar SetID
20 CAL_DEPR_PD Character(2) VARCHAR2(2) NOT NULL Calendar
21 LEASE_EXPIRED_FLG Character(1) VARCHAR2(1) NOT NULL Lease Expire Flag
22 PRORATE_LS_EXP Character(1) VARCHAR2(1) NOT NULL Recognize Monthly Expense