RE_AM_PRO_CA_VW

(SQL View)
 Index Back

Lease Chart Temp View


SELECT A.BUSINESS_UNIT , A.ASSET_ID , A.LEASE_ID , A.VENDOR_ID , A.VENDOR_CONTACT , A.LEASE_DT , A.RESPONSIBILITY , A.MRP , A.EXECUTORY_COSTS , A.MLP , A.PV_MLP , A.CAP_LEASE_AMT , A.GUAR_RESIDUAL , A.ACTUAL_RESIDUAL , A.TSFR_OWNERSHIP_FLG , A.RENEWAL_OPTION , A.RENEWAL_PENALTY , A.BPO , A.BPO_PAYMENT , A.IMPLICIT_RATE , A.BORROW_RATE , A.ITC_AMT , A.ITC_TAKEN_YEAR , A.LEASE_TYPE , A.LEASE_TERM , A.FMV , A.EST_LIFE , A.PYMNT_SCHED_ID , A.LEASE_BOOK , A.CAL_DEPR_PD , A.CAL_SETID , C.FISCAL_YEAR , C.ACCOUNTING_PERIOD , CASE WHEN B.TERM_SCHED_ID IS NULL THEN A.PYMNT_DT ELSE B.SCHED_DATE END , A.PERCENTAGE , A.CF_SEQNO , A.CF_SEQNO_EXP , CASE WHEN B.TERM_SCHED_ID IS NULL THEN A.INTEREST_EXPENSE ELSE ( CASE WHEN B.ASSET_CLASSIFY = 'C' THEN B.INTEREST_EXPENSE ELSE 0 END ) END , CASE WHEN B.TERM_SCHED_ID IS NULL THEN 0 ELSE (CASE WHEN B.ASSET_CLASSIFY = 'C' THEN CASE WHEN A.FISCAL_YEAR = C.FISCAL_YEAR AND A.ACCOUNTING_PERIOD = C.ACCOUNTING_PERIOD THEN (B.INTEREST_ACCRUAL * -1) ELSE B.INTEREST_EXPENSE END ELSE 0 END) END , CASE WHEN B.TERM_SCHED_ID IS NULL THEN A.OBLIGATION_REDUCE ELSE (CASE WHEN A.FISCAL_YEAR = C.FISCAL_YEAR AND A.ACCOUNTING_PERIOD = C.ACCOUNTING_PERIOD THEN CASE WHEN A.ASSET_CLASSIFY = 'C' THEN A.OBLIGATION_REDUCE ELSE 0 END ELSE 0 END) END , A.CURRENCY_CD , A.SCHED_PYMNT_TYPE , A.TXN_CURRENCY_CD , A.INT_WITH_AP_BI , CASE WHEN B.TERM_SCHED_ID IS NULL THEN A.FIRST_PYMNT_SW ELSE (CASE WHEN A.FISCAL_YEAR = C.FISCAL_YEAR AND A.ACCOUNTING_PERIOD = C.ACCOUNTING_PERIOD THEN A.FIRST_PYMNT_SW ELSE B.FIRST_PYMNT_SW END) END , CASE WHEN B.TERM_SCHED_ID IS NULL THEN A.ASSET_CLASSIFY ELSE (CASE WHEN A.ASSET_CLASSIFY = 'C' THEN A.ASSET_CLASSIFY ELSE B.ASSET_CLASSIFY END) END , A.TERM_RENT_TYPE , A.TXN_KEY , A.PRORATION_RATE , A.IDC_PRORATION_RATE , A.PRORATE_LS_EXP , A.PLP_FLG , CASE WHEN B.TERM_SCHED_ID IS NULL THEN A.FISCAL_YEAR ELSE C.FISCAL_YEAR END , CASE WHEN B.TERM_SCHED_ID IS NULL THEN A.ACCOUNTING_PERIOD ELSE C.ACCOUNTING_PERIOD END , A.TRANS_DT , A.ACCOUNTING_DT , CASE WHEN B.TERM_SCHED_ID IS NULL THEN 'Y' ELSE (CASE WHEN A.FISCAL_YEAR = C.FISCAL_YEAR AND A.ACCOUNTING_PERIOD = C.ACCOUNTING_PERIOD THEN 'Y' ELSE 'N' END) END FROM PS_RE_AM_CHART_VW A LEFT OUTER JOIN PS_RE_LS_AM_IE_VW B ON (A.TXN_KEY = B.TERM_SCHED_ID AND A.CF_SEQNO = B.CF_SEQNO AND A.BUSINESS_UNIT=B.BUSINESS_UNIT AND A.ASSET_ID = B.ASSET_ID AND A.PYMNT_DT=B.PYMNT_DT AND A.TRANS_DT = B.TRANS_DT AND A.ACCOUNTING_DT = B.ACCOUNTING_DT AND A.PLP_FLG = B.PLP_FLG AND A.SCHED_PYMNT_TYPE=B.SCHED_TYPE) , PS_CAL_DETP_TBL C WHERE (A.ASSET_CLASSIFY = 'C' AND (B.TERM_SCHED_ID IS NULL OR B.ASSET_CLASSIFY = 'C')) AND C.SETID = A.CAL_SETID AND C.CALENDAR_ID = A.CAL_DEPR_PD AND (CASE WHEN B.TERM_SCHED_ID IS NULL THEN A.PYMNT_DT ELSE B.SCHED_DATE END) BETWEEN C.BEGIN_DT AND C.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 LEASE_ID Character(30) VARCHAR2(30) NOT NULL Lease ID
4 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier
5 VENDOR_CONTACT Character(30) VARCHAR2(30) NOT NULL Vendor Contact
6 LEASE_DT Date(10) DATE Lease Date
7 RESPONSIBILITY Character(30) VARCHAR2(30) NOT NULL Responsibility
8 MRP Signed Number(28,3) DECIMAL(26,3) NOT NULL Minimum Rental Payment
9 EXECUTORY_COSTS Signed Number(28,3) DECIMAL(26,3) NOT NULL Executory Costs
10 MLP Signed Number(28,3) DECIMAL(26,3) NOT NULL Minimum Lease Payment
11 PV_MLP Signed Number(28,3) DECIMAL(26,3) NOT NULL Present Value
12 CAP_LEASE_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Capitalized Lease Amount
13 GUAR_RESIDUAL Signed Number(28,3) DECIMAL(26,3) NOT NULL Guaranteed Residual Value
14 ACTUAL_RESIDUAL Signed Number(28,3) DECIMAL(26,3) NOT NULL Actual Residual Value
15 TSFR_OWNERSHIP_FLG Character(1) VARCHAR2(1) NOT NULL Transfer Ownership
16 RENEWAL_OPTION Character(1) VARCHAR2(1) NOT NULL Bargain Renewal Option
17 RENEWAL_PENALTY Signed Number(28,3) DECIMAL(26,3) NOT NULL Renewal Payment Penalty
18 BPO Character(1) VARCHAR2(1) NOT NULL Bargain Purchase Option
19 BPO_PAYMENT Signed Number(28,3) DECIMAL(26,3) NOT NULL Bargain Purchase Option Paymnt
20 IMPLICIT_RATE Number(8,4) DECIMAL(7,4) NOT NULL Implicit Interest Rate
21 BORROW_RATE Number(8,4) DECIMAL(7,4) NOT NULL Incremental Borrowing Rate
22 ITC_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Investment Tax Credit Amount
23 ITC_TAKEN_YEAR Number(4,0) SMALLINT NOT NULL ITC Taken Year
24 LEASE_TYPE Character(1) VARCHAR2(1) NOT NULL Lease Type
C=Capital
O=Operating
25 LEASE_TERM Number(6,0) INTEGER NOT NULL Lease Term (Periods)
26 FMV Signed Number(28,3) DECIMAL(26,3) NOT NULL Fair (Market) Value Amount
27 EST_LIFE Number(6,0) INTEGER NOT NULL Estimated Life
28 PYMNT_SCHED_ID Character(2) VARCHAR2(2) NOT NULL Payment Schedule ID
29 LEASE_BOOK Character(10) VARCHAR2(10) NOT NULL Book used by Lease
30 CAL_DEPR_PD Character(2) VARCHAR2(2) NOT NULL Calendar
31 CAL_SETID Character(5) VARCHAR2(5) NOT NULL Calendar SetID
32 FISCAL_YEAR Number(4,0) SMALLINT NOT NULL Fiscal Year
33 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.
34 PYMNT_DT Date(10) DATE Payment Date
35 PERCENTAGE Signed Number(7,2) DECIMAL(5,2) NOT NULL Percentage
36 CF_SEQNO Number(6,0) INTEGER NOT NULL Chartfield Sequence Number
37 CF_SEQNO_EXP Number(6,0) INTEGER NOT NULL Chartfield Sequence Number
38 INTEREST_EXPENSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Interest Expense
39 INTEREST_ACCRUAL Signed Number(28,3) DECIMAL(26,3) NOT NULL Interest calculated
40 OBLIGATION_REDUCE Signed Number(28,3) DECIMAL(26,3) NOT NULL Obligation Reduction
41 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
42 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
43 TXN_CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Transaction Currency
44 INT_WITH_AP_BI Character(1) VARCHAR2(1) NOT NULL Integrate with Accounts Payable
45 FIRST_PYMNT_SW Character(1) VARCHAR2(1) NOT NULL First Payment Switch
46 ASSET_CLASSIFY Character(1) VARCHAR2(1) NOT NULL Field for Lease Classification
C=Finance
O=Operating
47 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
48 TXN_KEY Number(30,0) DECIMAL(30) NOT NULL Transaction Key
49 PRORATION_RATE Number(14,10) DECIMAL(13,10) NOT NULL Proration Rate
50 IDC_PRORATION_RATE Number(14,10) DECIMAL(13,10) NOT NULL IDC Proration Rate
51 PRORATE_LS_EXP Character(1) VARCHAR2(1) NOT NULL Recognize Monthly Expense
52 PLP_FLG Character(1) VARCHAR2(1) NOT NULL Prior Lease Period Flag
N=No
Y=Yes
53 FISCAL_YEAR2 Number(4,0) SMALLINT NOT NULL Fiscal Year
54 ACCOUNTING_PERIOD2 Number(3,0) SMALLINT NOT NULL Accounting Period
55 TRANS_DT Date(10) DATE Transaction Date
56 ACCOUNTING_DT Date(10) DATE The accounting entry construction date for a given transaction (a generic field that crosses multiple
57 PAYMENT_ACTION Character(1) VARCHAR2(1) NOT NULL What do I do with payment