RE_AM_PY_ACC_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_EXP , A.PYMNT_DT , A.INT_WITH_AP_BI , A.FIRST_PYMNT_SW , A.ASSET_CLASSIFY , A.TERM_RENT_TYPE , SUM(A.INTEREST_EXPENSE) , SUM(A.OBLIGATION_REDUCE) , A.CURRENCY_CD , A.TXN_CURRENCY_CD , A.TRANS_DT , A.ACCOUNTING_DT , A.CAL_SETID , A.CAL_DEPR_PD , 'N' FROM PS_RE_AM_CHART_VW A WHERE EXISTS ( SELECT 'X' FROM PS_RE_LS_AM_PLP_VW D , PS_CAL_DETP_TBL E WHERE A.BUSINESS_UNIT = D.BUSINESS_UNIT AND A.ASSET_ID = D.ASSET_ID AND A.CAL_SETID = E.SETID AND A.CAL_DEPR_PD = E.CALENDAR_ID AND D.SCHED_DATE BETWEEN E.BEGIN_DT AND E.END_DT AND A.FISCAL_YEAR = E.FISCAL_YEAR AND A.ACCOUNTING_PERIOD = E.ACCOUNTING_PERIOD AND A.TRANS_DT = D.TRANS_DT AND A.ACCOUNTING_DT=D.ACCOUNTING_DT) AND A.LEASE_EXPIRED_FLG<>'Y' GROUP BY A.BUSINESS_UNIT , A.ASSET_ID , A.LEASE_BOOK , A.SCHED_PYMNT_TYPE , A.LEASE_TYPE , A.CF_SEQNO , A.CF_SEQNO_EXP , A.PYMNT_DT , A.INT_WITH_AP_BI , A.FIRST_PYMNT_SW , 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 UNION ALL SELECT A.BUSINESS_UNIT , A.ASSET_ID , A.LEASE_BOOK , A.SCHED_PYMNT_TYPE , A.LEASE_TYPE , A.CF_SEQNO , A.CF_SEQNO_EXP , A.PYMNT_DT , A.INT_WITH_AP_BI , A.FIRST_PYMNT_SW , A.ASSET_CLASSIFY , A.TERM_RENT_TYPE , SUM(A.INTEREST_EXPENSE) , SUM(A.OBLIGATION_REDUCE) , A.CURRENCY_CD , A.TXN_CURRENCY_CD , A.TRANS_DT , A.PYMNT_DT , A.CAL_SETID , A.CAL_DEPR_PD , 'N' FROM PS_RE_AM_CHART_VW A WHERE NOT EXISTS ( SELECT 'X' FROM PS_RE_LS_AM_PLP_VW D , PS_CAL_DETP_TBL E WHERE A.BUSINESS_UNIT = D.BUSINESS_UNIT AND A.ASSET_ID = D.ASSET_ID AND A.CAL_SETID = E.SETID AND A.CAL_DEPR_PD = E.CALENDAR_ID AND D.SCHED_DATE BETWEEN E.BEGIN_DT AND E.END_DT AND A.FISCAL_YEAR = E.FISCAL_YEAR AND A.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' GROUP BY A.BUSINESS_UNIT , A.ASSET_ID , A.LEASE_BOOK , A.SCHED_PYMNT_TYPE , A.LEASE_TYPE , A.CF_SEQNO , A.CF_SEQNO_EXP , A.PYMNT_DT , A.INT_WITH_AP_BI , A.FIRST_PYMNT_SW , 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 UNION ALL SELECT A.BUSINESS_UNIT , A.ASSET_ID , B.LEASE_BOOK , A.SCHED_TYPE , CASE WHEN A.RECOGNITION_EXEMPT = 'Y' THEN 'N' ELSE A.CAPITALIZE_SW END , A.CF_SEQNO , A.CF_SEQNO_EXP , A.SCHED_DATE , A.INT_WITH_AP_BI , A.FIRST_PYMNT_SW , A.ASSET_CLASSIFY , A.TERM_RENT_TYPE , SUM(%DecMult(A.INTEREST_AMOUNT,-1)) , SUM(%DecMult(A.OBLIGATION_REDUCE,-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 , 'N' FROM PS_RE_LS_AM_PLP_VW A , PS_LEASE_CHART1_VW B , ps_Cal_Detp_Tbl C , ps_Cal_Detp_Tbl D WHERE A.BUSINESS_UNIT=B.BUSINESS_UNIT AND A.ASSET_ID=B.ASSET_ID AND A.LEASE_EXPIRED_FLG <>'Y' 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 GROUP BY A.BUSINESS_UNIT , A.ASSET_ID , B.LEASE_BOOK , A.SCHED_TYPE , A.CAPITALIZE_SW , A.CF_SEQNO , A.CF_SEQNO_EXP , A.SCHED_DATE , A.INT_WITH_AP_BI , A.FIRST_PYMNT_SW , A.ASSET_CLASSIFY , A.TERM_RENT_TYPE , B.CURRENCY_CD , A.CURRENCY_CD , A.TRANS_DT , A.ACCOUNTING_DATE , B.CAL_SETID , B.CAL_DEPR_PD , A.RECOGNITION_EXEMPT,C.FISCAL_YEAR ,D.FISCAL_YEAR ,C.ACCOUNTING_PERIOD , D.ACCOUNTING_PERIOD , A.TRANS_DATE UNION ALL SELECT A.BUSINESS_UNIT , A.ASSET_ID , A.LEASE_BOOK , A.SCHED_PYMNT_TYPE , A.LEASE_TYPE , A.CF_SEQNO , A.CF_SEQNO_EXP , A.PYMNT_DT , A.INT_WITH_AP_BI , A.FIRST_PYMNT_SW , A.ASSET_CLASSIFY , A.TERM_RENT_TYPE , SUM(A.INTEREST_EXPENSE) , SUM(A.OBLIGATION_REDUCE) , A.CURRENCY_CD , A.TXN_CURRENCY_CD , A.TRANS_DT , A.ACCOUNTING_DT , A.CAL_SETID , A.CAL_DEPR_PD , 'Y' FROM PS_RE_AM_CHART_VW A WHERE EXISTS ( SELECT 'X' FROM PS_RE_LS_AM_PLP_VW D , PS_CAL_DETP_TBL E WHERE A.BUSINESS_UNIT = D.BUSINESS_UNIT AND A.ASSET_ID = D.ASSET_ID /* AND D.LEASE_EXPIRED_FLG = 'Y' */ AND A.CAL_SETID = E.SETID AND A.CAL_DEPR_PD = E.CALENDAR_ID AND D.SCHED_DATE BETWEEN E.BEGIN_DT AND E.END_DT AND A.FISCAL_YEAR = E.FISCAL_YEAR AND A.ACCOUNTING_PERIOD = E.ACCOUNTING_PERIOD AND A.TRANS_DT = D.TRANS_DT AND A.ACCOUNTING_DT =D.ACCOUNTING_DT ) AND A.LEASE_EXPIRED_FLG = 'Y' GROUP BY A.BUSINESS_UNIT , A.ASSET_ID , A.LEASE_BOOK , A.SCHED_PYMNT_TYPE , A.LEASE_TYPE , A.CF_SEQNO , A.CF_SEQNO_EXP, A.PYMNT_DT , A.INT_WITH_AP_BI , A.FIRST_PYMNT_SW , 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 UNION ALL SELECT A.BUSINESS_UNIT , A.ASSET_ID , B.LEASE_BOOK , A.SCHED_TYPE , CASE WHEN A.RECOGNITION_EXEMPT = 'Y' THEN 'N' ELSE A.CAPITALIZE_SW END , A.CF_SEQNO , A.CF_SEQNO_EXP , A.SCHED_DATE , A.INT_WITH_AP_BI , A.FIRST_PYMNT_SW , A.ASSET_CLASSIFY , A.TERM_RENT_TYPE , SUM(%DecMult(A.INTEREST_AMOUNT,-1)) , SUM(%DecMult(A.OBLIGATION_REDUCE,-1)) , B.CURRENCY_CD , A.CURRENCY_CD , A.TRANS_DT ,CASE WHEN (C.FISCAL_YEAR = D1.FISCAL_YEAR AND C.ACCOUNTING_PERIOD = D1.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 , 'Y' FROM PS_RE_LS_AM_PLP_VW A , PS_LEASE_CHART1_VW B , ps_Cal_Detp_Tbl C , ps_Cal_Detp_Tbl D1 WHERE A.BUSINESS_UNIT=B.BUSINESS_UNIT AND A.ASSET_ID =B.ASSET_ID AND A.LEASE_EXPIRED_FLG = 'Y' 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 D1.SetID = B.Cal_SetID AND D1.Calendar_ID = B.Cal_Depr_Pd AND A.accounting_dAtE BETWEEN D1.Begin_Dt AND D1.End_Dt GROUP BY A.BUSINESS_UNIT , A.ASSET_ID , B.LEASE_BOOK , A.SCHED_TYPE , A.CAPITALIZE_SW , A.CF_SEQNO , A.CF_SEQNO_EXP , A.SCHED_DATE , A.INT_WITH_AP_BI , A.FIRST_PYMNT_SW , A.ASSET_CLASSIFY , A.TERM_RENT_TYPE , B.CURRENCY_CD , A.CURRENCY_CD , A.TRANS_DT , A.ACCOUNTING_DATE , B.CAL_SETID , B.CAL_DEPR_PD , A.RECOGNITION_EXEMPT, C.FISCAL_YEAR , D1.FISCAL_YEAR , C.ACCOUNTING_PERIOD , D1.ACCOUNTING_PERIOD, A.TRANS_DATE

# 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 INT_WITH_AP_BI Character(1) VARCHAR2(1) NOT NULL Integrate with Accounts Payable
10 FIRST_PYMNT_SW Character(1) VARCHAR2(1) NOT NULL First Payment Switch
11 ASSET_CLASSIFY Character(1) VARCHAR2(1) NOT NULL Field for Lease Classification
C=Finance
O=Operating
12 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
13 INTEREST_EXPENSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Interest Expense
14 OBLIGATION_REDUCE 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