Below are the list of basic records that can be used while working with Lease Administration Queries:
- PS_RE_LS
- PS_RE_LSA
- PS_RE_LSA_PPTY
- PS_RE_LSA_TRM
- PS_RE_LS_TRM_SCHED
- PS_RE_LS_INT_SCHED
Queries:
Below queries can be used to retrieve the basic information from the Lease records including Asset accounting data.
SELECT A.LS_KEY,B.AMND_NBR,A.BUSINESS_UNIT,A.LS_NBR,A.LSE_TYPE_CD,A.LEASE_NAME,A.LEASE_STATUS,A.REGION_CD,A.PRINCIPAL_ID,A.VNDR_LOC,A.LEASE_SIGNED_DT,A.LEASE_START_DT,A.LEASE_END_DT,A.LEASE_TERM,A.PRIM_PROPERTY_ID,A.CALENDAR_ID,A.IMPLICIT_RATE,A.BORROW_RATE,A.MLP,A.PV_MLP,A.ROU_ASSET,B.PROPERTY_ID,B.BUSINESS_UNIT_AM,B.ASSET_ID,B.CAP_LEASE_AMT,B.COST,B.CATEGORY,B.EST_LIFE,B.ALLOC_LS_PAYMENTS,B.ALLOC_IDC,B.UNDISCOUNTED_ROU,B.DISCOUNTED_PVLP,B.ADJUSTED_PV_MLP,B.INITIAL_DIRECT_CST,B.SUGGESTED_ALLOC,B.CURRENT_LS_PAYMENT,B.CURRENT_PV_MLP,C.ACCOUNT,C.DISTRIBUTION_TYPE,C.TRANS_TYPE,C.ACCOUNTING_DT,C.DEPTID,C.PRODUCT,C.COST_TYPE,C.APPL_JRNL_ID,C.AMOUNT,C.JOURNAL_ID,C.JOURNAL_DATE,C.JOURNAL_LINE
FROM PS_RE_LS A, PS_RE_LSA_PPTY B, PS_DIST_LN C
WHERE A.LS_KEY = B.LS_KEY
AND B.BUSINESS_UNIT_AM = C.BUSINESS_UNIT
AND B.ASSET_ID = C.ASSET_ID
SELECT DISTINCT A.LS_KEY,B.AMND_NBR,A.BUSINESS_UNIT,A.LS_NBR,A.LSE_TYPE_CD,A.LEASE_NAME,A.LEASE_STATUS,F.PRODUCT,C.PAYMENT_GROUP,A.REGION_CD,A.PRINCIPAL_ID,A.VNDR_LOC,A.LEASE_SIGNED_DT,A.LEASE_START_DT,A.LEASE_END_DT,A.LEASE_TERM,A.PRIM_PROPERTY_ID,A.CALENDAR_ID,A.IMPLICIT_RATE,A.BORROW_RATE,A.MLP,A.PV_MLP,A.ROU_ASSET,B.PROPERTY_ID,B.BUSINESS_UNIT_AM,B.ASSET_ID,B.CAP_LEASE_AMT,B.COST,B.CATEGORY,B.EST_LIFE,B.ALLOC_LS_PAYMENTS,B.ALLOC_IDC,B.UNDISCOUNTED_ROU,B.DISCOUNTED_PVLP,B.ADJUSTED_PV_MLP,B.INITIAL_DIRECT_CST,B.SUGGESTED_ALLOC,B.CURRENT_LS_PAYMENT,B.CURRENT_PV_MLP,C.SCHEDULE,(A.PV_MLP+E.INTEREST_AMOUNT)-SUM(D.OBLIGATION_REDUCE)
FROM PS_RE_LS A, PS_RE_LSA_PPTY B,PS_RE_LSA_TRM C,PS_RE_LS_TRM_SCHED D,(SELECT DISTINCT AA.LS_KEY,C.INTEREST_AMOUNT,C.TERM_ID FROM PS_RE_LS AA,PS_RE_LS_TRM_SCHED C WHERE AA.LS_KEY = C.LS_KEY AND C.START_DT = AA.LEASE_START_DT) AS E,PS_ASSET_ACQ_DET F
where A.LS_KEY = B.LS_KEY
AND B.LS_KEY = C.LS_KEY
AND C.LS_KEY = D.LS_KEY
AND D.LS_KEY = E.LS_KEY
AND B.BUSINESS_UNIT_AM = F.BUSINESS_UNIT
AND B.ASSET_ID = F.ASSET_ID
AND C.TERM_ID = E.TERM_ID
AND D.START_DT <= (SELECT MAX(B.END_DT) FROM PS_RE_LS_TRM_SCHED B WHERE B.LS_KEY = D.LS_KEY AND (GETDATE()) BETWEEN B.START_DT AND B.END_DT)
AND B.AMND_NBR = (SELECT MAX(B_E.AMND_NBR) FROM PS_RE_LSA_PPTY B_E WHERE B.LS_KEY = B_E.LS_KEY)
GROUP BY A.LS_KEY,B.AMND_NBR,A.BUSINESS_UNIT,A.LS_NBR,A.LSE_TYPE_CD,A.LEASE_NAME,A.LEASE_STATUS,F.PRODUCT,C.PAYMENT_GROUP,A.REGION_CD,A.PRINCIPAL_ID,A.VNDR_LOC,A.LEASE_SIGNED_DT,A.LEASE_START_DT,A.LEASE_END_DT,A.LEASE_TERM,A.PRIM_PROPERTY_ID,A.CALENDAR_ID,A.IMPLICIT_RATE,A.BORROW_RATE,A.MLP,A.PV_MLP,A.ROU_ASSET,B.PROPERTY_ID,B.BUSINESS_UNIT_AM,B.ASSET_ID,B.CAP_LEASE_AMT,B.COST,B.CATEGORY,B.EST_LIFE,B.ALLOC_LS_PAYMENTS,B.ALLOC_IDC,B.UNDISCOUNTED_ROU,B.DISCOUNTED_PVLP,B.ADJUSTED_PV_MLP,B.INITIAL_DIRECT_CST,B.SUGGESTED_ALLOC,B.CURRENT_LS_PAYMENT,B.CURRENT_PV_MLP,C.SCHEDULE,(A.PV_MLP+E.INTEREST_AMOUNT) --This query is for future pending payment
