RE_AM_PY_LC_VW

(SQL View)
 Index Back

Lease Chart Temp Table


SELECT A.BUSINESS_UNIT , A.ASSET_ID , B.LEASE_BOOK , A.SCHED_TYPE , 'Y' , A.CF_SEQNO , A.CF_SEQNO_EXP , A.SCHED_DATE , A.ASSET_CLASSIFY , '00' , SUM(A.INTEREST_EXPENSE) , CASE WHEN X.FISCAL_YEAR = C.FISCAL_YEAR AND X.ACCOUNTING_PERIOD = C.ACCOUNTING_PERIOD THEN SUM(A.INTEREST_ACCRUAL * -1) ELSE SUM(A.INTEREST_EXPENSE) END , SUM(A.AMORTIZATION_AMT) , B.CURRENCY_CD , A.CURRENCY_CD , A.TRANS_DT , A.ACCOUNTING_DT , B.CAL_SETID , B.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_LS_AM_IE_VW A , PS_LEASE_CHART1_VW B , ps_Cal_Detp_Tbl C , ps_Cal_Detp_Tbl X WHERE EXISTS (SELECT 'X' FROM PS_RE_LS_AM_PIE_VW D , PS_CAL_DETP_TBL E WHERE A.BUSINESS_UNIT = D.BUSINESS_UNIT AND A.ASSET_ID = D.ASSET_ID AND B.CAL_SETID = E.SETID AND B.CAL_DEPR_PD = E.CALENDAR_ID AND D.SCHED_DATE BETWEEN E.BEGIN_DT AND E.END_DT AND C.FISCAL_YEAR = E.FISCAL_YEAR AND C.ACCOUNTING_PERIOD = E.ACCOUNTING_PERIOD AND A.TRANS_DT = D.TRANS_DT AND A.ACCOUNTING_DT =D.ACCOUNTING_DT ) AND A.BUSINESS_UNIT =B.BUSINESS_UNIT AND A.ASSET_ID =B.ASSET_ID AND C.SetID = B.Cal_SetID AND C.Calendar_ID = B.Cal_Depr_Pd AND A.SCHED_DATE BETWEEN C.Begin_Dt AND C.End_Dt AND X.SetID = B.Cal_SetID AND X.Calendar_ID = B.Cal_Depr_Pd AND A.PYMNT_DT BETWEEN X.Begin_Dt AND X.End_Dt GROUP BY A.BUSINESS_UNIT , A.ASSET_ID , B.LEASE_BOOK , A.SCHED_TYPE , A.CF_SEQNO, A.CF_SEQNO_EXP , A.SCHED_DATE , A.ASSET_CLASSIFY , A.CURRENCY_CD , B.CURRENCY_CD , A.TRANS_DT , A.ACCOUNTING_DT , B.CAL_SETID , B.CAL_DEPR_PD, A.PRORATE_LS_EXP , C.FISCAL_YEAR , C.ACCOUNTING_PERIOD, X.FISCAL_YEAR , X.ACCOUNTING_PERIOD,A.LEASE_EXPIRED_FLG UNION ALL SELECT A.BUSINESS_UNIT , A.ASSET_ID , B.LEASE_BOOK , A.SCHED_TYPE , 'Y' , A.CF_SEQNO , A.CF_SEQNO_EXP , A.SCHED_DATE , A.ASSET_CLASSIFY , '00' , SUM(A.INTEREST_EXPENSE) , CASE WHEN X.FISCAL_YEAR = C.FISCAL_YEAR AND X.ACCOUNTING_PERIOD = C.ACCOUNTING_PERIOD THEN SUM(A.INTEREST_ACCRUAL * -1) ELSE SUM(A.INTEREST_EXPENSE) END , SUM(A.AMORTIZATION_AMT) , B.CURRENCY_CD , A.CURRENCY_CD , A.TRANS_DT , A.SCHED_DATE , B.CAL_SETID , B.CAL_DEPR_PD , 'N' , A.PRORATE_LS_EXP FROM PS_RE_LS_AM_IE_VW A , PS_LEASE_CHART1_VW B , ps_Cal_Detp_Tbl C , ps_Cal_Detp_Tbl X WHERE NOT EXISTS (SELECT 'X' FROM PS_RE_LS_AM_PIE_VW D , PS_CAL_DETP_TBL E WHERE A.BUSINESS_UNIT = D.BUSINESS_UNIT AND A.ASSET_ID = D.ASSET_ID AND B.CAL_SETID = E.SETID AND B.CAL_DEPR_PD = E.CALENDAR_ID AND D.SCHED_DATE BETWEEN E.BEGIN_DT AND E.END_DT AND C.FISCAL_YEAR = E.FISCAL_YEAR AND C.ACCOUNTING_PERIOD = E.ACCOUNTING_PERIOD AND ((A.TRANS_DT = D.TRANS_DT AND A.ACCOUNTING_DT = D.ACCOUNTING_DT AND D.LEASE_EXPIRED_FLG<>'Y') OR (D.LEASE_EXPIRED_FLG ='Y' /*AND A.TXN_KEY = D.TXN_KEY */ AND D.SCHED_DATE BETWEEN D.TRANS_DT AND D.ACCOUNTING_DT)) ) AND A.LEASE_EXPIRED_FLG<>'Y' AND A.BUSINESS_UNIT =B.BUSINESS_UNIT AND A.ASSET_ID =B.ASSET_ID AND C.SetID = B.Cal_SetID AND C.Calendar_ID = B.Cal_Depr_Pd AND A.SCHED_DATE BETWEEN C.Begin_Dt AND C.End_Dt AND X.SetID = B.Cal_SetID AND X.Calendar_ID = B.Cal_Depr_Pd AND A.PYMNT_DT BETWEEN X.Begin_Dt AND X.End_Dt GROUP BY A.BUSINESS_UNIT , A.ASSET_ID , B.LEASE_BOOK , A.SCHED_TYPE , A.CF_SEQNO, A.CF_SEQNO_EXP , A.SCHED_DATE , A.ASSET_CLASSIFY , A.CURRENCY_CD , B.CURRENCY_CD , A.TRANS_DT , A.ACCOUNTING_DT , B.CAL_SETID , B.CAL_DEPR_PD , A.PRORATE_LS_EXP , C.FISCAL_YEAR , C.ACCOUNTING_PERIOD, X.FISCAL_YEAR , X.ACCOUNTING_PERIOD UNION ALL SELECT A.BUSINESS_UNIT , A.ASSET_ID , B.LEASE_BOOK , ' ' , 'Y' , A.CF_SEQNO , A.CF_SEQNO_EXP , A.SCHED_DATE , A.ASSET_CLASSIFY , '00' , CASE WHEN A.LEASE_EXPIRED_FLG<>'Y' THEN SUM(A.INTEREST_EXPENSE) * -1 ELSE CASE WHEN A.LEASE_EXPIRED_FLG = 'Y' THEN 0 END END , CASE WHEN A.LEASE_EXPIRED_FLG<>'Y' THEN SUM( CASE WHEN X.FISCAL_YEAR = Y.FISCAL_YEAR AND X.ACCOUNTING_PERIOD = Y.ACCOUNTING_PERIOD THEN (A.INTEREST_ACCRUAL* -1) ELSE (A.INTEREST_EXPENSE ) END) * -1 ELSE CASE WHEN A.LEASE_EXPIRED_FLG = 'Y' THEN CASE WHEN X.FISCAL_YEAR = C.FISCAL_YEAR AND X.ACCOUNTING_PERIOD = C.ACCOUNTING_PERIOD THEN SUM(A.INTEREST_ACCRUAL* -1) ELSE SUM(A.INTEREST_EXPENSE ) END END END, SUM(%DecMult(A.AMORTIZATION_AMT,-1)) , B.CURRENCY_CD , A.CURRENCY_CD , A.TRANS_DT , CASE WHEN (C.FISCAL_YEAR = D.FISCAL_YEAR AND C.ACCOUNTING_PERIOD = D.ACCOUNTING_PERIOD) THEN A.SCHED_DATE ELSE CASE WHEN A.SCHED_DATE BETWEEN A.TRANS_DATE AND A.ACCOUNTING_DATE THEN A.ACCOUNTING_DATE ELSE A.SCHED_DATE END END , B.CAL_SETID , B.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_LS_AM_PIE_VW A , PS_LEASE_CHART1_VW B , ps_Cal_Detp_Tbl C , ps_Cal_Detp_Tbl D , ps_Cal_Detp_Tbl X, ps_Cal_Detp_Tbl Y WHERE A.BUSINESS_UNIT =B.BUSINESS_UNIT AND A.ASSET_ID =B.ASSET_ID AND C.SetID = b.Cal_SetID AND C.Calendar_ID = b.Cal_Depr_Pd AND A.TRANS_DATE BETWEEN C.Begin_Dt AND C.End_Dt AND d.SetID = b.Cal_SetID AND d.Calendar_ID = b.Cal_Depr_Pd AND A.accounting_dAtE BETWEEN d.Begin_Dt AND d.End_Dt AND X.SetID = B.Cal_SetID AND X.Calendar_ID = B.Cal_Depr_Pd AND A.PYMNT_DT BETWEEN X.Begin_Dt AND X.End_Dt AND Y.SetID = B.Cal_SetID AND Y.Calendar_ID = B.Cal_Depr_Pd AND A.SCHED_DATE BETWEEN Y.Begin_Dt AND Y.End_Dt GROUP BY A.BUSINESS_UNIT , A.ASSET_ID , B.LEASE_BOOK , A.CF_SEQNO, A.CF_SEQNO_EXP , A.SCHED_DATE , A.ASSET_CLASSIFY , B.CURRENCY_CD , A.CURRENCY_CD , A.TRANS_DT , A.ACCOUNTING_DATE , B.CAL_SETID , B.CAL_DEPR_PD , C.FISCAL_YEAR , D.FISCAL_YEAR , C.ACCOUNTING_PERIOD , D.ACCOUNTING_PERIOD , A.TRANS_DATE , A.PRORATE_LS_EXP , X.FISCAL_YEAR , X.ACCOUNTING_PERIOD, A.LEASE_EXPIRED_FLG

# 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 INTEREST_ACCRUAL Signed Number(28,3) DECIMAL(26,3) NOT NULL Interest calculated
13 OBLIGATION_REDUCE Signed Number(28,3) DECIMAL(26,3) NOT NULL Obligation Reduction
14 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
15 TXN_CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Transaction Currency
16 TRANS_DT Date(10) DATE Transaction Date
17 ACCOUNTING_DT Date(10) DATE The accounting entry construction date for a given transaction (a generic field that crosses multiple
18 CAL_SETID Character(5) VARCHAR2(5) NOT NULL Calendar SetID
19 CAL_DEPR_PD Character(2) VARCHAR2(2) NOT NULL Calendar
20 LEASE_EXPIRED_FLG Character(1) VARCHAR2(1) NOT NULL Lease Expire Flag
21 PRORATE_LS_EXP Character(1) VARCHAR2(1) NOT NULL Recognize Monthly Expense