IT_FIN_STAT_SMV

(SQL View)
 Index Back

IT Fin Stat summary view

IT Asset financial statistics summary view

SELECT A.OPRID , A.TREE_SETID , A.TREE_NAME , A.TREE_LEVEL , B.TREE_NODE , A.CURRENCY_CD , A.AMT_DISPLAY_IN , CAST(%Round(B.AVG_LIFE_REM,2) AS CHAR(8)) , %Round(%DecDiv(%DecMult(%DecDiv(%DecMult(B.DEPR_YTD, 1.0), F.RATE_DIV), F.RATE_MULT) , A.AMT_DISPLAY_IN), D.DECIMAL_POSITIONS) , %Round(B.QUANTITY,0) , %Round(%DecDiv (%DecMult(%DecDiv(%DecMult(B.NET_BK_VALUE, 1.0), F.RATE_DIV), F.RATE_MULT) , A.AMT_DISPLAY_IN) , D.DECIMAL_POSITIONS) , B.ASOFDATE , A.RT_TYPE FROM PS_IT_OPRID_TSETID A , PS_IT_FIN_STAT_SUM B , PS_CURRENCY_CD_TBL D , PS_RT_DFLT_VW F WHERE A.TREE_SETID = B.TREE_SETID AND A.TREE_NAME = B.TREE_NAME AND A.TREE_LEVEL = B.TREE_LEVEL AND F.FROM_CUR = 'USD' AND F.TO_CUR = A.CURRENCY_CD AND F.RT_TYPE = A.RT_TYPE AND F.EFFDT = ( SELECT MAX(G.EFFDT) FROM PS_RT_DFLT_VW G WHERE G.FROM_CUR = F.FROM_CUR AND G.TO_CUR = F.TO_CUR AND G.RT_TYPE = F.RT_TYPE AND G.EFFDT <= B.ASOFDATE ) AND D.CURRENCY_CD = A.CURRENCY_CD AND D.EFFDT = ( SELECT MAX(D2.EFFDT) FROM PS_CURRENCY_CD_TBL D2 WHERE D2.CURRENCY_CD = A.CURRENCY_CD AND D2.EFFDT <= B.ASOFDATE )

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
2 TREE_SETID Character(5) VARCHAR2(5) NOT NULL Tree SetID
3 TREE_NAME Character(18) VARCHAR2(18) NOT NULL Tree Name
4 TREE_LEVEL Character(10) VARCHAR2(10) NOT NULL Tree Level Number
5 TREE_NODE Character(20) VARCHAR2(20) NOT NULL Node name
6 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code

Default Value: USD

Prompt Table: CURRENCY_CD_TBL

7 AMT_DISPLAY_IN Number(9,0) DECIMAL(9) NOT NULL Amount display in ex: 1000 or 1000000

Default Value: 1

8 AVG_LIFE_REM_DIS Character(8) VARCHAR2(8) NOT NULL Avg Life Remaining
9 DEPR_YTD Signed Number(28,3) DECIMAL(26,3) NOT NULL Year to Date Depreciation
10 UNITS_DISPLAY Number(11,0) DECIMAL(11) NOT NULL Units field with no decimal
11 NET_BK_VALUE Signed Number(28,3) DECIMAL(26,3) NOT NULL Net Book Value
12 ASOFDATE Date(10) DATE As of Date
13 RT_TYPE Character(5) VARCHAR2(5) NOT NULL Defines a category of market rates for currency conversion. Some examples of rate types are commercial, average, floating, and historical.