RET_GL_FY_VW

(SQL View)
 Index Back

Retirement Gain/Loss by FY-AP


SELECT A.BUSINESS_UNIT , A.ASSET_ID ,A.BOOK , B.FISCAL_YEAR , B.ACCOUNTING_PERIOD , A.CATEGORY , A.CF_SEQNO ,A.FROM_CUR , SUM(A.LINE_GAIN_LOSS) , SUM(CASE WHEN A.LINE_GAIN_LOSS > 0 THEN A.LINE_GAIN_LOSS ELSE 0 END) , SUM(CASE WHEN A.LINE_GAIN_LOSS < 0 THEN %DecMult(A.LINE_GAIN_LOSS,-1) ELSE 0 END) FROM PS_RETIRE_VW A , PS_CAL_DETP_TBL B , PS_SET_CNTRL_REC C , PS_BU_AM_BOOK_VW D WHERE C.SETCNTRLVALUE = A.BUSINESS_UNIT AND C.RECNAME='CAL_DEFN_TBL' AND C.SETID = B.SETID AND A.BUSINESS_UNIT = D.BUSINESS_UNIT AND A.BOOK = D.BOOK AND D.CAL_DEPR_PD = B.CALENDAR_ID AND A.TRANS_DT BETWEEN B.BEGIN_DT AND B.END_DT GROUP BY A.BUSINESS_UNIT, A.ASSET_ID,A.BOOK, B.FISCAL_YEAR, B.ACCOUNTING_PERIOD, A.CATEGORY, A.CF_SEQNO,A.FROM_CUR

# 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

Prompt Table: SP_BU_BK_NONVW

4 FISCAL_YEAR Number(4,0) SMALLINT NOT NULL Fiscal Year
5 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.
6 CATEGORY Character(5) VARCHAR2(5) NOT NULL Asset Category
7 CF_SEQNO Number(6,0) INTEGER NOT NULL Chartfield Sequence Number
8 FROM_CUR Character(3) VARCHAR2(3) NOT NULL From Currency Code
9 GAIN_LOSS Signed Number(28,3) DECIMAL(26,3) NOT NULL Retirement Gain/Loss
10 AMOUNT_1 Signed Number(28,3) DECIMAL(26,3) NOT NULL Amount Work Field
11 AMOUNT_2 Signed Number(28,3) DECIMAL(26,3) NOT NULL Amount Work Field