RE_LS_AM_PSH_VW

(SQL View)
 Index Back

View for Lease Payment Entries

View for creating Lease Payment Entries by Depr Close Process

SELECT 'S' , A.TERM_SCHED_ID , D.ASSET_SEQ_NBR , D.BUSINESS_UNIT_AM , D.ASSET_ID , A.LS_KEY , C.LEASE_OBLG , C.LS_CLASSIFICATION , C.RECOGNITION_EXEMPT , D.ASSET_CLASSIFY , D.ROUNDING_APPLIED , A.AMND_NBR , A.TRANS_DT , A.ACCOUNTING_DT , D.CATEGORY , A.TERM_ID , CASE WHEN ((B.TERM_RENT_TYPE = '04' OR B.TERM_RENT_TYPE = '09' OR B.TERM_RENT_TYPE = '10')) THEN B.TERM_RENT_TYPE ELSE '00' END , B.TXN_GRP , B.CAPITALIZE_SW , CASE WHEN ((A.SCHED_DATE = C.LEASE_START_DT AND B.LEASE_PYMT_TYPE = 'A') OR (B.TERM_RENT_TYPE = '04' OR B.TERM_RENT_TYPE = '09' OR B.TERM_RENT_TYPE = '10')) THEN B.INT_WITH_AP_BI ELSE ' ' END , C.PRORATE_LS_EXP , CASE WHEN (A.SCHED_DATE = C.LEASE_START_DT AND B.LEASE_PYMT_TYPE = 'A') THEN 'Y' ELSE 'N' END , A.LEASE_EXPIRED_FLG , A.SCHED_DATE , A.SCHED_TYPE , D.PRORATION_RATE , D.IDC_PRORATION_RATE , D.DST_ALLOC_METHOD , G.DISTRIBUTION_ID , %Coalesce(G.PERCENTAGE, 100) , %Coalesce( CASE WHEN B.TERM_RENT_TYPE IN ('04', '10') THEN G.CF_SEQNO_EXP ELSE G.CF_SEQNO END, CASE WHEN B.TERM_RENT_TYPE IN ('04', '10') THEN D.CF_SEQNO_EXP ELSE D.CF_SEQNO END) , %Coalesce( CASE WHEN (A.SCHED_DATE = C.LEASE_START_DT AND B.LEASE_PYMT_TYPE = 'A') THEN G.CF_SEQNO_EXP ELSE G.CF_SEQNO END, CASE WHEN (A.SCHED_DATE = C.LEASE_START_DT AND B.LEASE_PYMT_TYPE = 'A') THEN D.CF_SEQNO_EXP ELSE D.CF_SEQNO END) , A.CURRENCY_CD , CASE WHEN B.CAPITALIZE_SW = 'N' THEN A.AMOUNT ELSE CASE WHEN ((A.SCHED_DATE = C.LEASE_START_DT AND B.LEASE_PYMT_TYPE = 'A') OR (TERM_RENT_TYPE = '04' OR TERM_RENT_TYPE = '10')) THEN %Sql(RE_ASSET_AMT_CALC, CD.DECIMAL_POSITIONS, A.AMOUNT, D.IDC_PRORATION_RATE) ELSE %Sql(RE_ASSET_AMT_CALC, CD.DECIMAL_POSITIONS, A.AMOUNT, D.PRORATION_RATE) END END , A.AMOUNT , CASE WHEN B.CAPITALIZE_SW = 'N' THEN %Sql(RE_ASSET_AMT_CALC, CD.DECIMAL_POSITIONS, A.AMOUNT, D.PRORATION_RATE) ELSE CASE WHEN F.NEW_ACCT_RULE = 'N' THEN %Sql(RE_ASSET_AMT_CALC, CD.DECIMAL_POSITIONS, A.OBLIGATION_REDUCE, D.PRORATION_RATE) ELSE CASE WHEN D.ASSET_CLASSIFY = 'C' THEN CASE WHEN ((A.SCHED_DATE = C.LEASE_START_DT AND B.LEASE_PYMT_TYPE = 'A') OR (TERM_RENT_TYPE = '04' OR TERM_RENT_TYPE = '10')) THEN %Sql(RE_ASSET_AMT_CALC, CD.DECIMAL_POSITIONS, A.AMOUNT, D.IDC_PRORATION_RATE) ELSE %Sql(RE_ASSET_AMT_CALC, CD.DECIMAL_POSITIONS, A.AMOUNT, D.PRORATION_RATE) END ELSE CASE WHEN ((A.SCHED_DATE = C.LEASE_START_DT AND B.LEASE_PYMT_TYPE = 'A') OR (TERM_RENT_TYPE = '04' OR TERM_RENT_TYPE = '10')) THEN %Sql(RE_ASSET_AMT_CALC, CD.DECIMAL_POSITIONS,(A.AMOUNT - A.INTEREST_AMOUNT), D.IDC_PRORATION_RATE) ELSE %Sql(RE_ASSET_AMT_CALC, CD.DECIMAL_POSITIONS, A.OBLIGATION_REDUCE, D.PRORATION_RATE) END END END END , CASE WHEN B.CAPITALIZE_SW = 'N' THEN A.AMOUNT ELSE CASE WHEN F.NEW_ACCT_RULE = 'N' THEN A.OBLIGATION_REDUCE ELSE CASE WHEN D.ASSET_CLASSIFY = 'C' THEN CASE WHEN ((A.SCHED_DATE = C.LEASE_START_DT AND B.LEASE_PYMT_TYPE = 'A') OR (TERM_RENT_TYPE = '04' OR TERM_RENT_TYPE = '10')) THEN A.AMOUNT ELSE A.AMOUNT END ELSE CASE WHEN ((A.SCHED_DATE = C.LEASE_START_DT AND B.LEASE_PYMT_TYPE = 'A') OR (TERM_RENT_TYPE = '04' OR TERM_RENT_TYPE = '10')) THEN (A.AMOUNT - A.INTEREST_AMOUNT) ELSE A.OBLIGATION_REDUCE END END END END , %Sql(RE_ASSET_AMT_CALC, CD.DECIMAL_POSITIONS, A.INTEREST_AMOUNT, D.PRORATION_RATE) , A.INTEREST_AMOUNT , 0 , D.RATE_MULT , D.RATE_DIV , CD.DECIMAL_POSITIONS , C2.DECIMAL_POSITIONS , A.START_DT , A.END_DT , AMND.ACCOUNTING_DT , AMND.TRANS_DT FROM PS_RE_LS_TSCH_HIST A , PS_RE_LS_TRM B , PS_RE_LS C , PS_RE_LSA AMND , PS_RE_BUS_UNIT E , PS_INSTALLATION_RE F , PS_BU_AM_BOOK_VW BK , PS_CURRENCY_CD_TBL C2 , PS_CURRENCY_CD_TBL CD , PS_RE_LSA_PPTY D LEFT OUTER JOIN PS_RE_LSA_AST_DST G ON (G.LS_KEY = D.LS_KEY AND G.AMND_NBR = D.AMND_NBR AND G.ASSET_SEQ_NBR = D.ASSET_SEQ_NBR) WHERE A.LS_KEY = B.LS_KEY AND A.TERM_ID = B.TERM_ID AND A.LS_KEY = C.LS_KEY AND A.LS_KEY = D.LS_KEY AND A.AMND_NBR = D.AMND_NBR AND AMND.LS_KEY = A.LS_KEY AND AMND.AMND_NBR = A.AMND_NBR AND C.LEASE_OBLG = '1' AND E.BUSINESS_UNIT = C.BUSINESS_UNIT AND BK.BUSINESS_UNIT = D.BUSINESS_UNIT_AM AND BK.BOOK = BK.LEASE_BOOK AND CD.CURRENCY_CD = C.CURRENCY_CD AND C2.CURRENCY_CD = BK.CURRENCY_CD AND %EffdtCheck(CURRENCY_CD_TBL, CD, %CURRENTDATEIN) AND %EffdtCheck(CURRENCY_CD_TBL, C2, %CURRENTDATEIN) AND CD.EFF_STATUS = 'A' AND C2.EFF_STATUS = 'A' AND B.TERM_RENT_TYPE NOT IN ('09') AND ((B.CAPITALIZE_SW = 'N' AND E.USE_AM_AET = 'Y') OR B.CAPITALIZE_SW = 'Y')

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 REC_SOURCE Character(1) VARCHAR2(1) NOT NULL Record Source
S=Schedule
T=Transaction
2 TXN_KEY Number(30,0) DECIMAL(30) NOT NULL Transaction Key
3 ASSET_SEQ_NBR Number(5,0) INTEGER NOT NULL Asset Sequence Number
4 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
5 ASSET_ID Character(12) VARCHAR2(12) NOT NULL Asset Identification
6 LS_KEY Number(15,0) DECIMAL(15) NOT NULL Lease Key, Unique Key
7 LEASE_OBLG Character(1) VARCHAR2(1) NOT NULL Lease Obligation either Payables or Receivables Lease
1=Payables
2=Receivables
8 LS_CLASSIFICATION Character(1) VARCHAR2(1) NOT NULL Field for Lease Classification
C=Finance
O=Operating
9 RECOGNITION_EXEMPT Character(1) VARCHAR2(1) NOT NULL Exempt from Recognition Flag
10 ASSET_CLASSIFY Character(1) VARCHAR2(1) NOT NULL Field for Lease Classification
C=Finance
O=Operating
11 ROUNDING_APPLIED Character(1) VARCHAR2(1) NOT NULL Rounding Applied
N=No
Y=Yes
12 AMND_NBR Number(3,0) SMALLINT NOT NULL Amendment Number
13 TRANS_DT Date(10) DATE Transaction Date
14 ACCOUNTING_DT Date(10) DATE The accounting entry construction date for a given transaction (a generic field that crosses multiple
15 CATEGORY Character(5) VARCHAR2(5) NOT NULL Asset Category
16 TERM_ID Number(10,0) DECIMAL(10) NOT NULL Financial Term ID
17 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
18 TXN_GRP Character(1) VARCHAR2(1) NOT NULL Transaction Group
1=Base Rent
2=Security Deposit
3=Straightline Accounting
4=Operating Expense
5=Percent Rent
6=Miscellaneous Rent
7=Manual Fee
8=Lease Obligation
9=Lease Interest Expense
A=Prior Period Lease Obligation
B=Prior Period Lease Interest
C=Rent Expense
D=Lease Incentive
19 CAPITALIZE_SW Character(1) VARCHAR2(1) NOT NULL Capitalized
20 INT_WITH_AP_BI Character(1) VARCHAR2(1) NOT NULL Integrate with Accounts Payable
21 PRORATE_LS_EXP Character(1) VARCHAR2(1) NOT NULL Recognize Monthly Expense
22 FIRST_PYMNT_SW Character(1) VARCHAR2(1) NOT NULL First Payment Switch
23 LEASE_EXPIRED_FLG Character(1) VARCHAR2(1) NOT NULL Lease Expire Flag
24 SCHED_DATE Date(10) DATE Schedule Date
25 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
26 PRORATION_RATE Number(14,10) DECIMAL(13,10) NOT NULL Proration Rate
27 IDC_PRORATION_RATE Number(14,10) DECIMAL(13,10) NOT NULL IDC Proration Rate
28 DST_ALLOC_METHOD Character(1) VARCHAR2(1) NOT NULL Distribution Allocation Method
1=Percentage
2=Amount
3=Area
4=None
5=Quantity
29 DISTRIBUTION_ID Number(5,0) INTEGER NOT NULL Distribution ID
30 PERCENTAGE Signed Number(7,2) DECIMAL(5,2) NOT NULL Percentage
31 CF_SEQNO Number(6,0) INTEGER NOT NULL Chartfield Sequence Number
32 CF_SEQNO_EXP Number(6,0) INTEGER NOT NULL Chartfield Sequence Number
33 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
34 AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Amount
35 AMOUNT_3 Signed Number(28,3) DECIMAL(26,3) NOT NULL Amount
36 OBLIGATION_REDUCE Signed Number(28,3) DECIMAL(26,3) NOT NULL Obligation Reduction
37 AMOUNT_1 Signed Number(28,3) DECIMAL(26,3) NOT NULL Amount Work Field
38 INTEREST_AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Interest Amount
39 AMOUNT_2 Signed Number(28,3) DECIMAL(26,3) NOT NULL Amount Work Field
40 LS_TXN_SRC_ID Number(30,0) DECIMAL(30) NOT NULL Lease Transaction Source ID
41 RATE_MULT Signed Number(17,8) DECIMAL(15,8) NOT NULL Rate Multiplier
42 RATE_DIV Number(16,8) DECIMAL(15,8) NOT NULL Rate Divisor
43 DECIMAL_POSITIONS Number(2,0) SMALLINT NOT NULL Decimal Positions
44 DECIMAL_POSITIONS2 Number(2,0) SMALLINT NOT NULL Decimal Positions
45 START_DT Date(10) DATE Start Date
46 END_DT Date(10) DATE End Date
47 ACCOUNTING_DATE Date(10) DATE ACCOUNTING_DATE
48 TRANS_DATE Date(10) DATE Transaction Date