AM_PRD_PDP_VW

(SQL View)
 Index Back

Period wise PDP view


SELECT C.BUSINESS_UNIT ,C.ASSET_ID ,C.BOOK , C.CF_SEQNO ,C.DTTM_STAMP ,C1.FISCAL_YEAR , C1.ACCOUNTING_PERIOD ,C.COST_TYPE ,C.TRANS_DT ,C1.BEGIN_DT ,C1.END_DT , SUM(C.DEPR_ACTUAL) DEPR FROM ( SELECT C.SETID ,C.CALENDAR_ID , D.BUSINESS_UNIT , D.ASSET_ID , D.BOOK , D.CF_SEQNO ,D.DTTM_STAMP , C.FISCAL_YEAR YEAR1 , C.ACCOUNTING_PERIOD STARTPD ,D.COST_TYPE ,D.TRANS_DT ,C.BEGIN_DT ,C.END_DT , C.ACCOUNTING_PERIOD +((D.FISCAL_YEAR *NUMBER_OF_PERIODS+D.START_PD) - (C.FISCAL_YEAR*NUMBER_OF_PERIODS+C.ACCOUNTING_PERIOD))-1 ENDPD , CASE WHEN D.DEPR=0 THEN D.DEPR_YTD WHEN D.DEPR<>0 THEN D.DEPR + D.DEPR_YTD ELSE D.DEPR END / ((D.FISCAL_YEAR *NUMBER_OF_PERIODS+D.START_PD) - (C.FISCAL_YEAR*NUMBER_OF_PERIODS+C.ACCOUNTING_PERIOD)) DEPR_ACTUAL FROM PS_DEPRECIATION D ,PS_CAL_DETP_TBL C , PS_CAL_DEFN_TBL CC , PS_INSTALLATION_AM I , PS_SET_CNTRL_TBL S , PS_BU_BOOK_TBL BB WHERE D.TRANS_TYPE = I.TYPE_PRIOR_DEPR AND D.BUSINESS_UNIT=S.SETCNTRLVALUE AND D.BUSINESS_UNIT=BB.BUSINESS_UNIT AND D.BOOK=BB.BOOK AND C.SETID =S.SETID AND C.CALENDAR_ID = BB.CAL_DEPR_PD AND C.SETID = CC.SETID AND C.CALENDAR_ID = CC.CALENDAR_ID AND D.TRANS_DT BETWEEN C.BEGIN_DT AND C.END_DT) C, PS_CAL_DETP_TBL C1 WHERE C.SETID = C1.SETID AND C.CALENDAR_ID= C1.CALENDAR_ID AND C.YEAR1 = C1.FISCAL_YEAR AND C1.ACCOUNTING_PERIOD >=STARTPD AND C1.ACCOUNTING_PERIOD <=ENDPD GROUP BY C.BUSINESS_UNIT,C.ASSET_ID,C.BOOK, C.CF_SEQNO,C.DTTM_STAMP,C1.FISCAL_YEAR, C1.ACCOUNTING_PERIOD,C.COST_TYPE,C.TRANS_DT,C1.BEGIN_DT,C1.END_DT

# 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 CF_SEQNO Number(6,0) INTEGER NOT NULL Chartfield Sequence Number
5 DTTM_STAMP DateTime(26) TIMESTAMP Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.
6 FISCAL_YEAR Number(4,0) SMALLINT NOT NULL Fiscal Year
7 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.
8 COST_TYPE Character(1) VARCHAR2(1) NOT NULL Cost Type
9 TRANS_DT Date(10) DATE Transaction Date
10 BEGIN_DT Date(10) DATE Begin Date
11 END_DT Date(10) DATE End Date
12 DEPR Signed Number(28,3) DECIMAL(26,3) NOT NULL Depreciation Amt