IT_FIN_STAT_DTV

(SQL View)
 Index Back

IT Fin Stat detail view

IT Asset financial statistics detail view for display

SELECT A.OPRID , A.TREE_SETID , A.TREE_NAME , A.TREE_LEVEL , B.TREE_NODE , B.DEPT_BUS_UNIT , B.ASOFDATE , A.CURRENCY_CD , A.AMT_DISPLAY_IN , A.TREE_TYPE_ID , CAST(%Round(B.AVG_LIFE_REM,2) AS CHAR(8)) , %Round(B.QUANTITY,0) , %Round(%DecDiv(%DecMult(%DecDiv(%DecMult(B.COST, 1.0), F.RATE_DIV), F.RATE_MULT) , A.AMT_DISPLAY_IN), D.DECIMAL_POSITIONS) , %Round(%DecDiv(%DecMult(%DecDiv(%DecMult(B.SALVAGE_VALUE, 1.0), F.RATE_DIV), F.RATE_MULT) , A.AMT_DISPLAY_IN) , D.DECIMAL_POSITIONS) , %Round(%DecDiv(%DecMult(%DecDiv(%DecMult(B.DEPR, 1.0), F.RATE_DIV), F.RATE_MULT) , A.AMT_DISPLAY_IN) , D.DECIMAL_POSITIONS) , %Round(%DecDiv(%DecMult(%DecDiv(%DecMult(B.DEPR_YTD, 1.0), F.RATE_DIV), F.RATE_MULT) , A.AMT_DISPLAY_IN) , D.DECIMAL_POSITIONS) , %Round(%DecDiv(%DecMult(%DecDiv(%DecMult(B.DEPR_LTD, 1.0), F.RATE_DIV), F.RATE_MULT) , A.AMT_DISPLAY_IN) , D.DECIMAL_POSITIONS) , %Round(%DecDiv (%DecMult(%DecDiv(%DecMult(B.NET_BK_VALUE, 1.0), F.RATE_DIV), F.RATE_MULT) , A.AMT_DISPLAY_IN) , D.DECIMAL_POSITIONS) , A.RT_TYPE FROM PS_IT_OPRID_TSETID A , PS_IT_FIN_STAT_DET 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).

Default Value: OPR_DEF_TBL_IT.OPRID

2 TREE_SETID Character(5) VARCHAR2(5) NOT NULL Tree SetID

Prompt Table: SP_SETID_NONVW

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

Prompt Table: IT_FIN_STATSRCH

6 DEPT_BUS_UNIT Character(10) VARCHAR2(10) NOT NULL Department or Business unit
7 ASOFDATE Date(10) DATE As of Date
8 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
9 AMT_DISPLAY_IN Number(9,0) DECIMAL(9) NOT NULL Amount display in ex: 1000 or 1000000

Default Value: 1

10 TREE_TYPE_ID Character(1) VARCHAR2(1) NOT NULL Tree Type Id
B=Business Unit
D=Department
11 AVG_LIFE_REM_DIS Character(8) VARCHAR2(8) NOT NULL Avg Life Remaining
12 UNITS_DISPLAY Number(11,0) DECIMAL(11) NOT NULL Units field with no decimal
13 COST Signed Number(28,3) DECIMAL(26,3) NOT NULL Total Cost
14 SALVAGE_VALUE Signed Number(28,3) DECIMAL(26,3) NOT NULL Salvage Value
15 DEPR Signed Number(28,3) DECIMAL(26,3) NOT NULL Depreciation Amt
16 DEPR_YTD Signed Number(28,3) DECIMAL(26,3) NOT NULL Year to Date Depreciation
17 DEPR_LTD Signed Number(28,3) DECIMAL(26,3) NOT NULL Life to Date Depreciation
18 NET_BK_VALUE Signed Number(28,3) DECIMAL(26,3) NOT NULL Net Book Value
19 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.