RE_AM_DPR_AA_VW(SQL View) |
Index Back |
|---|---|
Lease Depr and Amort AmountsLease Depr and Amort Amounts |
| SELECT DEPR.BUSINESS_UNIT , DEPR.ASSET_ID , 'C' , DEPR.BOOK , DEPR.FISCAL_YEAR , DEPR.START_PD , DEPR.END_PD , DEPR.COST_TYPE , DEPR.TRANS_TYPE , DEPR.DEPR , 1 , 1 FROM PS_DEPRECIATION DEPR UNION ALL SELECT A.BUSINESS_UNIT , A.ASSET_ID , 'O' , A.LEASE_BOOK , A.FISCAL_YEAR , A.ACCOUNTING_PERIOD , A.ACCOUNTING_PERIOD , CASE WHEN A.TERM_RENT_TYPE IN ('04' ,'10') THEN B.COST_TYPE WHEN A.TERM_RENT_TYPE = '09' THEN D.COST_TYPE ELSE ' ' END , ' ' , CASE WHEN A.ASSET_CLASSIFY = 'O' AND A.FIRST_PYMNT_SW = 'Y' THEN (-1 * A.INTEREST_EXPENSE) ELSE (A.OBLIGATION_REDUCE) END , 1 , 1 FROM PS_RE_AM_CRT_AA_VW A , PS_COST_TYPE_TBL B , PS_COST_TYPE_TBL D WHERE B.SETID= ( SELECT SETID FROM PS_SET_CNTRL_REC WHERE RECNAME = 'COST_TYPE_TBL' AND SETCNTRLVALUE = A.BUSINESS_UNIT) AND B.EFFDT = ( SELECT MAX(C.EFFDT) FROM PS_COST_TYPE_TBL C WHERE C.SETID = B.SETID AND C.COST_TYPE = B.COST_TYPE AND C.EFFDT <= %CurrentDateIn) AND B.RENT_EXP_SW = 'Y' AND D.SETID = B.SETID AND D.EFFDT = ( SELECT MAX(E.EFFDT) FROM PS_COST_TYPE_TBL E WHERE E.SETID = D.SETID AND E.COST_TYPE = D.COST_TYPE AND E.EFFDT <= %CurrentDateIn) AND D.LSE_INCTV_SW = 'Y' AND B.EFF_STATUS = 'A' AND D.EFF_STATUS = 'A' AND A.TERM_RENT_TYPE IN ('04','09','10', '00') |
| # | 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 | ASSET_CLASSIFY | Character(1) | VARCHAR2(1) NOT NULL |
Field for Lease Classification
C=Finance O=Operating |
| 4 | LEASE_BOOK | Character(10) | VARCHAR2(10) NOT NULL | Book used by Lease |
| 5 | FISCAL_YEAR | Number(4,0) | SMALLINT NOT NULL | Fiscal Year |
| 6 | START_PD | Number(3,0) | SMALLINT NOT NULL | Start Period |
| 7 | END_PD | Number(3,0) | SMALLINT NOT NULL | End Period |
| 8 | COST_TYPE | Character(1) | VARCHAR2(1) NOT NULL | Cost Type |
| 9 | TRANS_TYPE | Character(3) | VARCHAR2(3) NOT NULL |
Types of Asset Management Transactions
ACR=Accretion Expense ADD=Asset Addition ADJ=Asset Cost Adjustment BKS=Book Adjustment BUD=Budgeted Depreciation DPR=Depreciation INF=Inflation Adjustment LEX=Lease Expense LPB=Budgeted Lease Payments LPY=Lease Payment PDP=Prior Period Depreciation PLP=Prior Lease Payment RAD=Manual Reserve Adjustment RCT=Asset Recategorization REI=Asset Reinstatement RES=Resume Depreciation RET=Asset Retirement RRC=Retro rate change for grp ast SUS=Suspend Depreciation TRF=Asset Transfer |
| 10 | DEPR | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Depreciation Amt |
| 11 | RATE_MULT | Signed Number(17,8) | DECIMAL(15,8) NOT NULL | Rate Multiplier |
| 12 | RATE_DIV | Number(16,8) | DECIMAL(15,8) NOT NULL | Rate Divisor |