AM_OPAST_AMT_VW

(SQL View)
 Index Back

SELECT CRT.BUSINESS_UNIT ,CRT.ASSET_ID ,CRT.LEASE_BOOK ,LS.BUSINESS_UNIT ,LS.LS_NBR ,LS.LEASE_NAME ,LS.LEASE_STATUS ,CRT.FISCAL_YEAR ,CRT.ACCOUNTING_PERIOD ,COST.CF_SEQNO ,%subrec(CF12_AN_SBR, COST) ,COST.PROJECT_ID ,COST.DEPTID ,COST.CATEGORY ,COST.COST_TYPE ,CRT.TXN_CURRENCY_CD ,CRT.PYMNT_DT ,CASE WHEN COST.COST_TYPE NOT IN ( SELECT COST_TYPE.COST_TYPE FROM PS_COST_TYPE_TBL COST_TYPE , PS_SET_CNTRL_REC SETCNTRL WHERE SETCNTRL.SetCntrlValue = CRT.Business_Unit AND SETCNTRL.RecName='COST_TYPE_TBL' AND SETCNTRL.SETID = COST_TYPE.SETID AND (COST_TYPE.RENT_EXP_SW = 'Y' OR COST_TYPE.LSE_INCTV_SW = 'Y' OR COST_TYPE.LSE_REMSR_SW = 'Y') AND COST_TYPE.EFFDT = ( SELECT MAX(COST_TYPE1.EFFDT) FROM PS_COST_TYPE_TBL COST_TYPE1 WHERE COST_TYPE1.SETID = COST_TYPE.SETID AND COST_TYPE1.COST_TYPE = COST_TYPE.COST_TYPE AND COST_TYPE1.EFFDT <=%CurrentDateIn AND COST_TYPE1.EFF_STATUS = 'A')) THEN %Round(SUM(( CASE WHEN CRT.FIRST_PYMNT_SW = 'Y' THEN %DecMult(CRT.INTRST_EXPENSE_BSE, - 1) ELSE CASE WHEN CRT.ASSET_CLASSIFY = 'C' THEN (CRT.OBLG_REDUCE_BSE - CRT.INTRST_EXPENSE_BSE) ELSE CRT.OBLG_REDUCE_BSE END END)),AA.DECIMAL_POSITIONS) ELSE CASE WHEN COST.COST_TYPE = ( SELECT COST_TYPE.COST_TYPE FROM PS_COST_TYPE_TBL COST_TYPE , PS_SET_CNTRL_REC SETCNTRL WHERE SETCNTRL.SetCntrlValue = CRT.Business_Unit AND SETCNTRL.RecName='COST_TYPE_TBL' AND SETCNTRL.SETID = COST_TYPE.SETID AND COST_TYPE.RENT_EXP_SW = 'Y' AND COST_TYPE.EFFDT = ( SELECT MAX(COST_TYPE1.EFFDT) FROM PS_COST_TYPE_TBL COST_TYPE1 WHERE COST_TYPE1.SETID = COST_TYPE.SETID AND COST_TYPE1.COST_TYPE = COST_TYPE.COST_TYPE AND COST_TYPE1.EFFDT <=%CurrentDateIn AND COST_TYPE1.EFF_STATUS = 'A')) THEN %Round(SUM( CASE WHEN CRT.FIRST_PYMNT_SW = 'Y' THEN (CRT.OBLG_REDUCE_BSE + CRT.INTRST_EXPENSE_BSE) ELSE CASE WHEN CRT.AMORTIZ_AMT_BSE <> 0 THEN CRT.AMORTIZ_AMT_BSE ELSE CRT.OBLG_REDUCE_BSE END END),AA.DECIMAL_POSITIONS) ELSE CASE WHEN COST.COST_TYPE = ( SELECT COST_TYPE.COST_TYPE FROM PS_COST_TYPE_TBL COST_TYPE , PS_SET_CNTRL_REC SETCNTRL WHERE SETCNTRL.SetCntrlValue = CRT.Business_Unit AND SETCNTRL.RecName='COST_TYPE_TBL' AND SETCNTRL.SETID = COST_TYPE.SETID AND COST_TYPE.LSE_INCTV_SW = 'Y' AND COST_TYPE.EFFDT = ( SELECT MAX(COST_TYPE1.EFFDT) FROM PS_COST_TYPE_TBL COST_TYPE1 WHERE COST_TYPE1.SETID = COST_TYPE.SETID AND COST_TYPE1.COST_TYPE = COST_TYPE.COST_TYPE AND COST_TYPE1.EFFDT <=%CurrentDateIn AND COST_TYPE1.EFF_STATUS = 'A')) THEN %Round(SUM(CRT.OBLG_REDUCE_BSE),AA.DECIMAL_POSITIONS) END END END FROM PS_COST_CF_VW COST LEFT OUTER JOIN PS_RE_AM_CRT_AA_VW CRT ON CRT.BUSINESS_UNIT = COST.BUSINESS_UNIT LEFT OUTER JOIN PS_RE_LS_PPTY PPTY ON PPTY.ASSET_ID = COST.ASSET_ID , PS_RE_LS LS , %Table(LEASE) LEASE , %Table(CURRENCY_CD_TBL) AA WHERE PPTY.ASSET_ID = CRT.ASSET_ID AND PPTY.BUSINESS_UNIT_AM = CRT.BUSINESS_UNIT AND LS.LS_KEY = PPTY.LS_KEY AND COST.BUSINESS_UNIT = CRT.BUSINESS_UNIT AND COST.ASSET_ID = CRT.ASSET_ID AND COST.BOOK = CRT.LEASE_BOOK AND (COST.CF_SEQNO = CRT.CF_SEQNO OR COST.CF_SEQNO = CRT.CF_SEQNO_EXP ) AND CRT.BUSINESS_UNIT = LEASE.BUSINESS_UNIT AND CRT.ASSET_ID = LEASE.ASSET_ID AND AA.CURRENCY_CD = CRT.CURRENCY_CD AND AA.EFF_STATUS = 'A' AND AA.EFFDT = ( SELECT MAX(B1.EFFDT) FROM %Table(CURRENCY_CD_TBL) B1 WHERE B1.CURRENCY_CD = AA.CURRENCY_CD AND B1.EFFDT <= LEASE.LEASE_COMMENCE_DT ) GROUP BY CRT.BUSINESS_UNIT , CRT.ASSET_ID ,CRT.LEASE_BOOK ,LS.BUSINESS_UNIT ,LS.LS_NBR ,LS.LEASE_NAME ,LS.LEASE_STATUS ,CRT.FISCAL_YEAR ,CRT.ACCOUNTING_PERIOD ,COST.CF_SEQNO ,%subrec(CF12_AN_SBR, COST) ,COST.PROJECT_ID ,COST.DEPTID ,COST.CATEGORY ,COST.COST_TYPE,CRT.TXN_CURRENCY_CD,CRT.PYMNT_DT,AA.DECIMAL_POSITIONS

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 ASSET_ID Character(12) VARCHAR2(12) NOT NULL Asset Identification
3 BOOK Character(10) VARCHAR2(10) NOT NULL Asset Book Name
4 BUSINESS_UNIT_1 Character(5) VARCHAR2(5) NOT NULL Non-Joint Venture BU
5 LS_NBR Character(10) VARCHAR2(10) NOT NULL Lease Number, User Enterable or Generated Sequence Number
6 LEASE_NAME Character(60) VARCHAR2(60) NOT NULL Lease Name
7 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
8 FISCAL_YEAR Number(4,0) SMALLINT NOT NULL Fiscal Year
9 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.
10 CF_SEQNO Number(6,0) INTEGER NOT NULL Chartfield Sequence Number
11 OPERATING_UNIT Character(8) VARCHAR2(8) NOT NULL Operating Unit ChartField
12 PRODUCT Character(6) VARCHAR2(6) NOT NULL Product ChartField
13 FUND_CODE Character(5) VARCHAR2(5) NOT NULL Fund Code
14 CLASS_FLD Character(5) VARCHAR2(5) NOT NULL Class Field
15 PROGRAM_CODE Character(5) VARCHAR2(5) NOT NULL Program Code ChartField
16 BUDGET_REF Character(8) VARCHAR2(8) NOT NULL Budget Reference
17 AFFILIATE Character(5) VARCHAR2(5) NOT NULL Affiliate
18 AFFILIATE_INTRA1 Character(10) VARCHAR2(10) NOT NULL IntraUnit Affiliate1
19 AFFILIATE_INTRA2 Character(10) VARCHAR2(10) NOT NULL Operating Unit Affiliate
20 CHARTFIELD1 Character(10) VARCHAR2(10) NOT NULL Expansion chartfield 1
21 CHARTFIELD2 Character(10) VARCHAR2(10) NOT NULL Expansion Chartfield 2
22 CHARTFIELD3 Character(10) VARCHAR2(10) NOT NULL Expansion Chartfield 3
23 PROJECT_ID Character(15) VARCHAR2(15) NOT NULL Project Id ChartField
24 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
25 CATEGORY Character(5) VARCHAR2(5) NOT NULL Asset Category
26 COST_TYPE Character(1) VARCHAR2(1) NOT NULL Cost Type
27 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
28 PYMNT_DT Date(10) DATE Payment Date
29 AMORTIZATION_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Amortization Amount