RSPD_PLNGOAL_VW (SQL View)	Index Back
Account plan and goal for Dash Dashboard view on account plans, goals, and goal attainment

RSPD_PLNGOAL_VW

(SQL View)

Account plan and goal for Dash

Dashboard view on account plans, goals, and goal attainment

SELECT P.RSP_UID20 ,G.UID20 , TF.TIMEFRAME_PERIOD ,P.RSP_NAME , P.RSP_STATUS ,P.START_DATE ,P.END_DATE , P.LAST_CALC_DTTM , P.RSP_PLAN_TYPE , P.CREATED_FROM_UID20 , T.RSP_NAME , P.SETID ,P.BO_ID ,P.ROLE_TYPE_ID ,G.RSP_GOAL_ID ,G.GOAL_NAME ,G.RSP_TARGET_TYPE ,G.RSP_TARGET_VAL ,G.RSP_ACTUAL_VAL , CASE WHEN RSP_TARGET_VAL = 0 OR P.LAST_CALC_DTTM IS NULL THEN 0 ELSE CASE WHEN G.RSP_PRORATE_FLG = 'N' THEN %Round(%DecDiv(%DecMult(G.RSP_ACTUAL_VAL, 100), G.RSP_TARGET_VAL), 2) ELSE %Round(%DecDiv(%DecMult(%DecMult(G.RSP_ACTUAL_VAL, 100), %DateDiff(P.START_DATE, P.END_DATE)), (%DecMult(G.RSP_TARGET_VAL, %DateDiff(P.START_DATE, %DatePart(P.LAST_CALC_DTTM))))),2) END END RSP_ATTAIN_PCT ,G.RSP_PRORATE_FLG ,G.RSP_VARIANCE_IND ,G.ROW_LASTMANT_DTTM ,G.ROW_ADDED_DTTM , UTFA.TIMEFRAME_PERIOD , UTFQ.TIMEFRAME_PERIOD , 'Y' FROM PS_RSP_TP_HEADER P , PS_RSP_TP_HEADER T , PS_RSP_TP_GOAL G , PS_RSFD_TFP_ALL_VW TF , PS_RSFD_TFP_ANN_VW UTFA , PS_RSFD_TFP_QTR_VW UTFQ WHERE P.RSP_STATUS IN ('20', '40') AND P.RSP_UID20 = G.RSP_UID20 AND P.BO_ID > 0 AND P.CREATED_FROM_UID20 = T.RSP_UID20 AND TF.BEGIN_DT = P.START_DATE AND TF.END_DT = P.END_DATE AND %DatePart(G.ROW_LASTMANT_DTTM) BETWEEN UTFA.BEGIN_DT AND UTFA.END_DT AND %DatePart(G.ROW_LASTMANT_DTTM) BETWEEN UTFQ.BEGIN_DT AND UTFQ.END_DT

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 RSP_UID20 Character(20) VARCHAR2(20) NOT NULL This field is used as a primary key in Strategic Planning header table (RSP_TP_HEADER)

2 UID20 Character(20) VARCHAR2(20) NOT NULL This field is for UID of 20 characters long.

3 TIMEFRAME_PERIOD Character(32) VARCHAR2(32) NOT NULL Time Frame

4 RSP_NAME Character(50) VARCHAR2(50) NOT NULL Stores the Strategic plan / template name.

5 RSP_STATUS Character(2) VARCHAR2(2) NOT NULL Status of a Strategic Plan or Template
10=Draft
20=Active
30=Inactive
40=Completed
50=Cancelled

6 START_DATE Date(10) DATE Start Date

7 END_DATE Date(10) DATE End date field to store an end date.

8 LAST_CALC_DTTM DateTime(26) TIMESTAMP Last Calculated

9 RSP_PLAN_TYPE Character(15) VARCHAR2(15) NOT NULL This field is used by the Strategic Planning to define various plan types setup in the system.

10 CREATED_FROM_UID20 Character(20) VARCHAR2(20) NOT NULL This field stores the RSP_UID20 of the template which was used to create a Strategic Plan.

11 RSP_TEMPLATE_NAME Character(50) VARCHAR2(50) NOT NULL Name of a strategic Plan Template

12 SETID Character(5) VARCHAR2(5) NOT NULL This field is used to store the value of SetID on various setup tables.

13 BO_ID Signed Number(32,0) DECIMAL(31) NOT NULL Business Object ID

14 ROLE_TYPE_ID Number(12,0) DECIMAL(12) NOT NULL Role Type ID

15 RSP_GOAL_ID Character(10) VARCHAR2(10) NOT NULL This field is used as a key on the Goal Setup table (RSP_GOAL) and is auto generated on save.

16 GOAL_NAME Character(50) VARCHAR2(50) NOT NULL Used in strategic account planning for identifying goal name.
Prompt Table: %EDITTABLE

17 RSP_TARGET_TYPE Character(1) VARCHAR2(1) NOT NULL This field is used by Strategic Planning to define target type like Amount, Percent etc.
1=Amount
2=Percent
3=Quantity
4=Other

18 RSP_TARGET_VAL Signed Number(32,2) DECIMAL(30,2) NOT NULL Field used in the goal setup page to capture the minimum target value allowed for a goal.

19 RSP_ACTUAL_VAL Signed Number(32,2) DECIMAL(30,2) NOT NULL Field used in the goal setup page to capture the minimum target value allowed for a goal.

20 RSP_ATTAIN_PCT Signed Number(9,2) DECIMAL(7,2) NOT NULL Field used by Strategic Planning to calculate attainment percentage.

21 RSP_PRORATE_FLG Character(1) VARCHAR2(1) NOT NULL Prorate target for goals as defined for strategic plans.
N=No
Y=Yes
Default Value: N

22 RSP_VARIANCE_IND Character(1) VARCHAR2(1) NOT NULL Field used by Strategic Planning to define Variance Indicator (High is positive vs. Low is positive)
H=High is Positive
L=Low is Positive
Default Value: H

23 ROW_LASTMANT_DTTM DateTime(26) TIMESTAMP The date and time that the row was last updated.

24 ROW_ADDED_DTTM DateTime(26) TIMESTAMP This is a datetime field for record creation.

25 RSD_UPD_TF_ANN Character(32) VARCHAR2(32) NOT NULL Timeframe period used in dashboard for the annual period enclosing date when the record was updated.

26 RSD_UPD_TF_QTR Character(32) VARCHAR2(32) NOT NULL Timeframe period used in dashboard for the quarterly period enclosing date when the record was updated.

27 INCL_IN_PLAN Character(1) VARCHAR2(1) NOT NULL Include in Planning

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	RSP_UID20	Character(20)	VARCHAR2(20) NOT NULL	This field is used as a primary key in Strategic Planning header table (RSP_TP_HEADER)
2	UID20	Character(20)	VARCHAR2(20) NOT NULL	This field is for UID of 20 characters long.
3	TIMEFRAME_PERIOD	Character(32)	VARCHAR2(32) NOT NULL	Time Frame
4	RSP_NAME	Character(50)	VARCHAR2(50) NOT NULL	Stores the Strategic plan / template name.
5	RSP_STATUS	Character(2)	VARCHAR2(2) NOT NULL	Status of a Strategic Plan or Template 10=Draft 20=Active 30=Inactive 40=Completed 50=Cancelled
6	START_DATE	Date(10)	DATE	Start Date
7	END_DATE	Date(10)	DATE	End date field to store an end date.
8	LAST_CALC_DTTM	DateTime(26)	TIMESTAMP	Last Calculated
9	RSP_PLAN_TYPE	Character(15)	VARCHAR2(15) NOT NULL	This field is used by the Strategic Planning to define various plan types setup in the system.
10	CREATED_FROM_UID20	Character(20)	VARCHAR2(20) NOT NULL	This field stores the RSP_UID20 of the template which was used to create a Strategic Plan.
11	RSP_TEMPLATE_NAME	Character(50)	VARCHAR2(50) NOT NULL	Name of a strategic Plan Template
12	SETID	Character(5)	VARCHAR2(5) NOT NULL	This field is used to store the value of SetID on various setup tables.
13	BO_ID	Signed Number(32,0)	DECIMAL(31) NOT NULL	Business Object ID
14	ROLE_TYPE_ID	Number(12,0)	DECIMAL(12) NOT NULL	Role Type ID
15	RSP_GOAL_ID	Character(10)	VARCHAR2(10) NOT NULL	This field is used as a key on the Goal Setup table (RSP_GOAL) and is auto generated on save.
16	GOAL_NAME	Character(50)	VARCHAR2(50) NOT NULL	Used in strategic account planning for identifying goal name. Prompt Table: %EDITTABLE
17	RSP_TARGET_TYPE	Character(1)	VARCHAR2(1) NOT NULL	This field is used by Strategic Planning to define target type like Amount, Percent etc. 1=Amount 2=Percent 3=Quantity 4=Other
18	RSP_TARGET_VAL	Signed Number(32,2)	DECIMAL(30,2) NOT NULL	Field used in the goal setup page to capture the minimum target value allowed for a goal.
19	RSP_ACTUAL_VAL	Signed Number(32,2)	DECIMAL(30,2) NOT NULL	Field used in the goal setup page to capture the minimum target value allowed for a goal.
20	RSP_ATTAIN_PCT	Signed Number(9,2)	DECIMAL(7,2) NOT NULL	Field used by Strategic Planning to calculate attainment percentage.
21	RSP_PRORATE_FLG	Character(1)	VARCHAR2(1) NOT NULL	Prorate target for goals as defined for strategic plans. N=No Y=Yes Default Value: N
22	RSP_VARIANCE_IND	Character(1)	VARCHAR2(1) NOT NULL	Field used by Strategic Planning to define Variance Indicator (High is positive vs. Low is positive) H=High is Positive L=Low is Positive Default Value: H
23	ROW_LASTMANT_DTTM	DateTime(26)	TIMESTAMP	The date and time that the row was last updated.
24	ROW_ADDED_DTTM	DateTime(26)	TIMESTAMP	This is a datetime field for record creation.
25	RSD_UPD_TF_ANN	Character(32)	VARCHAR2(32) NOT NULL	Timeframe period used in dashboard for the annual period enclosing date when the record was updated.
26	RSD_UPD_TF_QTR	Character(32)	VARCHAR2(32) NOT NULL	Timeframe period used in dashboard for the quarterly period enclosing date when the record was updated.
27	INCL_IN_PLAN	Character(1)	VARCHAR2(1) NOT NULL	Include in Planning