RE_MGRTD_PY_VW

(SQL View)
Index Back

SELECT P.BUSINESS_UNIT_AM , L.BUSINESS_UNIT , A.ASSET_STATUS , L.LEASE_STATUS , TRM.TXN_GRP , TRM.TERM_RENT_TYPE , CASE TRM.TXN_GRP || TRM.TERM_RENT_TYPE WHEN '101' THEN 'Base Rent' WHEN '106' THEN 'Purchase Option' WHEN '108' THEN 'Penalty' WHEN '610' THEN 'Interim Rent as Base' END AS PAYMENT_TYPE , COUNT(TSCH.TERM_SCHED_ID) FROM PS_RE_LS L LEFT OUTER JOIN PS_RE_LS_TRM TRM ON L.LS_KEY = TRM.LS_KEY LEFT OUTER JOIN PS_RE_LS_TRM_SCHED TSCH ON TRM.LS_KEY = TSCH.LS_KEY AND TRM.TERM_ID = TSCH.TERM_ID , PS_RE_LS_PPTY P , PS_ASSET A WHERE L.LS_KEY = P.LS_KEY AND P.ASSET_ID = A.ASSET_ID AND P.BUSINESS_UNIT_AM = A.BUSINESS_UNIT AND P.ASSET_SEQ_NBR = 1 AND ((TRM.TXN_GRP = '1' AND TRM.TERM_RENT_TYPE IN ('01', '06', '08')) OR (TRM.TXN_GRP = '6' AND TRM.TERM_RENT_TYPE = '10')) AND L.SYSTEM_SOURCE = 'BAM' GROUP BY P.BUSINESS_UNIT_AM, L.BUSINESS_UNIT, A.ASSET_STATUS, L.LEASE_STATUS, TRM.TXN_GRP, TRM.TERM_RENT_TYPE

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT_AM Character(5) VARCHAR2(5) NOT NULL AM Business Unit
2 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
3 ASSET_STATUS Character(1) VARCHAR2(1) NOT NULL Asset Status
A=Received (Not in Service)
B=Budgeted
C=Commitment
D=Disposed
I=In Service
M=Suspended
R=Requisitioned
T=Transferred
W=Work In Progress
4 LEASE_STATUS Character(1) VARCHAR2(1) NOT NULL Lease Status
1=Pending
2=Active
3=Canceled
4=Disputed
5=Expired
6=Holdover
7=Closed
8=Transferred
5 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
6 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
7 PYMNT_TERMS_DESCR Character(30) VARCHAR2(30) NOT NULL Payment Terms Description
8 ROWCOUNT1 Number(12,0) DECIMAL(12) NOT NULL Row Count