RSFD_OP_FCST_V2 (SQL View)	Index Back
Dash Opportunity Forecast The view which bridges between specific product forecasts within opportunities and the corresponding Sales Rep.

RSFD_OP_FCST_V2

(SQL View)

Dash Opportunity Forecast

The view which bridges between specific product forecasts within opportunities and the corresponding Sales Rep.

SELECT S.OPPORTUNITY_ID , S.PERSON_ID , 0 , ' ' , %DateNull , 0 , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , %DateNull , %DateNull , %DateNull , %DateNull , O.OPPORTUNITY_NAME , O.OPPORTUNITY_STATUS , O.EST_CLOSE_DT , O.CURRENCY_CD , %DateNull , O.SALES_MODEL , O.SALES_STAGE , O.STAGE_PCT_CLOSE , O.BO_ID_CUST , O.ROLE_TYPE_ID , O.BUSINESS_UNIT , O.SETID , O.BO_ID_PARTNER , O.CONFIDENCE_PCT , O.SALES_PRIORITY , O.BUSINESS_UNIT_RA , O.RA_CAMPAIGN_ID , O.REGION_ID , O.TREE_NAME , O.TERRITORY_ID , O.INDUSTRY_ID , O.LEAD_ID , ' ' , 0 , EA.TIMEFRAME_PERIOD , EQ.TIMEFRAME_PERIOD , O.ROW_LASTMANT_DTTM , UTFA.TIMEFRAME_PERIOD , UTFQ.TIMEFRAME_PERIOD , O.EST_REVENUE , O.ACT_REVENUE , O.ROW_ADDED_DTTM , S.PRIMARY_FLG , S.ROLE_CD , S.PCT_ALLOC , O.FORECAST_TOTAL , 'N' FROM PS_RSF_OPP_SLSREP S , PS_RSFD_OPPBASE_VW O , PS_RSFD_TFP_ANN_VW EA , PS_RSFD_TFP_QTR_VW EQ , PS_RSFD_TFP_ANN_VW UTFA , PS_RSFD_TFP_QTR_VW UTFQ WHERE S.OPPORTUNITY_ID = O.OPPORTUNITY_ID AND S.PCT_ALLOC > 0 AND NOT EXISTS( SELECT 'X' FROM PS_RSFD_OPF_TF_VW F WHERE O.OPPORTUNITY_ID = F.OPPORTUNITY_ID) AND O.EST_CLOSE_DT BETWEEN EA.BEGIN_DT AND EA.END_DT AND O.EST_CLOSE_DT BETWEEN EQ.BEGIN_DT AND EQ.END_DT AND %DatePart(S.ROW_LASTMANT_DTTM) BETWEEN UTFA.BEGIN_DT AND UTFA.END_DT AND %DatePart(S.ROW_LASTMANT_DTTM) BETWEEN UTFQ.BEGIN_DT AND UTFQ.END_DT

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 OPPORTUNITY_ID Character(15) VARCHAR2(15) NOT NULL Opportunity ID

2 PERSON_ID Character(15) VARCHAR2(15) NOT NULL Person ID

3 FORECAST_TOTAL Signed Number(28,3) DECIMAL(26,3) NOT NULL Forecast Total

4 FORECAST_TYPE Character(10) VARCHAR2(10) NOT NULL Type of forecast, eg 'Open' or 'closed'

5 FC_EST_CLOSE_DT Date(10) DATE Estimated Close Date of an Opportunity

6 FC_CONFIDENCE_PCT Number(3,0) SMALLINT NOT NULL Confidence percent as used for dashboard.

7 PROD_SETID Character(5) VARCHAR2(5) NOT NULL Product SET ID

8 PRODUCT_GROUP Character(10) VARCHAR2(10) NOT NULL PRoduct Group

9 PRODUCT_ID Character(18) VARCHAR2(18) NOT NULL Product ID

10 RECUR_PERIOD_NO Character(5) VARCHAR2(5) NOT NULL The specific recurring period of this revenue flow.

11 REVENUE_TYPE Character(15) VARCHAR2(15) NOT NULL This field is used to store revenue type related to a product sales and used within forecasting.

12 FCST_TF_ANNUAL Character(32) VARCHAR2(32) NOT NULL Timeframe period used in dashboard for estimated close date range

13 FCST_TF_QTR Character(32) VARCHAR2(32) NOT NULL Timeframe period used in dashboard for estimated close date range

14 BEGIN_DT_ANN_FCST Date(10) DATE Timeframe dates for dashboard: annual begin

15 END_DT_ANN_FCST Date(10) DATE Timeframe dates for dashboard: annual end

16 BEGIN_DT_QTR_FCST Date(10) DATE Timeframe dates for dashboard: quarter begin

17 END_DT_QTR_FCST Date(10) DATE Timeframe dates for dashboard: quarter end

18 OPPORTUNITY_NAME Character(50) VARCHAR2(50) NOT NULL Opportunity Name - User Enters this field in Mixed Case.

19 OPPORTUNITY_STATUS Character(1) VARCHAR2(1) NOT NULL Opportunity Status. The values are XLat Values
0=Open
1=Closed - Lost
2=Closed - Won
3=Inactive

20 EST_CLOSE_DT Date(10) DATE Estimated Close Date of an Opportunity

21 FROM_CUR Character(3) VARCHAR2(3) NOT NULL From Currency Code

22 ACT_CLOSE_DT Date(10) DATE Actual Close Date of an Opportunity

23 SALES_MODEL Character(18) VARCHAR2(18) NOT NULL Sales Model Id

24 SALES_STAGE Character(24) VARCHAR2(24) NOT NULL Sales Stage Identifier

25 STAGE_PCT_CLOSE Number(3,0) SMALLINT NOT NULL To indicate a % of how much a stage is completed

26 BO_ID_CUST Number(31,0) DECIMAL(31) NOT NULL This field stores the Business Object ID for the customer.

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

28 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

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

30 BO_ID_PARTNER Signed Number(32,0) DECIMAL(31) NOT NULL Business Object ID of Partner Company.

31 CONFIDENCE_PCT Number(3,0) SMALLINT NOT NULL Confidence Percentage

32 SALES_PRIORITY Character(3) VARCHAR2(3) NOT NULL Priority of sales lead and opportunity
1=1-One
2=2-Two
3=3-Three
4=4-Four
5=5-Five
6=6-Six
7=7-Seven
8=8-Eight
9=9-Nine
Default Value: 5

33 BUSINESS_UNIT_RA Character(5) VARCHAR2(5) NOT NULL Business Unit
Prompt Table: SP_BU_RA_NONVW

34 RA_CAMPAIGN_ID Character(15) VARCHAR2(15) NOT NULL Marketing - Campaign Identification field. Character field that will contain a generated campaign or free form entered campaign id.
Prompt Table: RSF_CAMPAIGN_VW

35 REGION_ID Character(15) VARCHAR2(15) NOT NULL This field stores the region id value.

36 TREE_NAME Character(18) VARCHAR2(18) NOT NULL Tree Name
Default Value: RSF_OPR_DEF_VW.TREE_NAME
Prompt Table: RSF_OPR_TREE_VW

37 TERRITORY_ID Character(20) VARCHAR2(20) NOT NULL Territory ID

38 INDUSTRY_ID Character(30) VARCHAR2(30) NOT NULL Industry ID.

39 LEAD_ID Character(15) VARCHAR2(15) NOT NULL Used by Sales Force Automation, this field uniquely identifies a Sales Lead.

40 FALLOUT_RSN_CD Character(10) VARCHAR2(10) NOT NULL Fall Out Reason code
Prompt Table: RSF_FALLOUT_RSN

41 BO_ID1 Number(31,0) DECIMAL(31) NOT NULL Business Object ID

42 EST_TF_ANNUAL Character(32) VARCHAR2(32) NOT NULL Timeframe period used in dashboard for estimated close date range

43 EST_TF_QTR Character(32) VARCHAR2(32) NOT NULL Timeframe period used in dashboard for estimated close date range

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

45 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.

46 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.

47 EST_REVENUE Signed Number(28,3) DECIMAL(26,3) NOT NULL The expected revenue (amount) associated with an Opportunity. This is particularly used for forecasting.

48 ACT_REVENUE Number(27,3) DECIMAL(26,3) NOT NULL Actual Revenue

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

50 PRIMARY_FLG Character(1) VARCHAR2(1) NOT NULL This is a flag which takes yes or no values and indicates whether the row is primary.
N=No
Y=Yes
Y/N Table Edit
Default Value: N

51 ROLE_CD Character(4) VARCHAR2(4) NOT NULL Sales User Role Code
0001=Manager
0002=Administrator
0003=Consultant
0004=Legal
0005=Other
0006=Liaison
0007=Partner
0007=Partner
0008=Sales Rep
0009=Team Leader
0010=Technical Sales
0011=Technical
0012=Training

52 PCT_ALLOC Number(3,0) SMALLINT NOT NULL Allocation Percentage

53 FORECAST_GRP_TOTAL Signed Number(28,3) DECIMAL(26,3) NOT NULL Subtotal

54 FORECAST Character(1) VARCHAR2(1) NOT NULL Include in Cash Forecast
N=Not Forecasted
Y=Forecasted

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	OPPORTUNITY_ID	Character(15)	VARCHAR2(15) NOT NULL	Opportunity ID
2	PERSON_ID	Character(15)	VARCHAR2(15) NOT NULL	Person ID
3	FORECAST_TOTAL	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Forecast Total
4	FORECAST_TYPE	Character(10)	VARCHAR2(10) NOT NULL	Type of forecast, eg 'Open' or 'closed'
5	FC_EST_CLOSE_DT	Date(10)	DATE	Estimated Close Date of an Opportunity
6	FC_CONFIDENCE_PCT	Number(3,0)	SMALLINT NOT NULL	Confidence percent as used for dashboard.
7	PROD_SETID	Character(5)	VARCHAR2(5) NOT NULL	Product SET ID
8	PRODUCT_GROUP	Character(10)	VARCHAR2(10) NOT NULL	PRoduct Group
9	PRODUCT_ID	Character(18)	VARCHAR2(18) NOT NULL	Product ID
10	RECUR_PERIOD_NO	Character(5)	VARCHAR2(5) NOT NULL	The specific recurring period of this revenue flow.
11	REVENUE_TYPE	Character(15)	VARCHAR2(15) NOT NULL	This field is used to store revenue type related to a product sales and used within forecasting.
12	FCST_TF_ANNUAL	Character(32)	VARCHAR2(32) NOT NULL	Timeframe period used in dashboard for estimated close date range
13	FCST_TF_QTR	Character(32)	VARCHAR2(32) NOT NULL	Timeframe period used in dashboard for estimated close date range
14	BEGIN_DT_ANN_FCST	Date(10)	DATE	Timeframe dates for dashboard: annual begin
15	END_DT_ANN_FCST	Date(10)	DATE	Timeframe dates for dashboard: annual end
16	BEGIN_DT_QTR_FCST	Date(10)	DATE	Timeframe dates for dashboard: quarter begin
17	END_DT_QTR_FCST	Date(10)	DATE	Timeframe dates for dashboard: quarter end
18	OPPORTUNITY_NAME	Character(50)	VARCHAR2(50) NOT NULL	Opportunity Name - User Enters this field in Mixed Case.
19	OPPORTUNITY_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Opportunity Status. The values are XLat Values 0=Open 1=Closed - Lost 2=Closed - Won 3=Inactive
20	EST_CLOSE_DT	Date(10)	DATE	Estimated Close Date of an Opportunity
21	FROM_CUR	Character(3)	VARCHAR2(3) NOT NULL	From Currency Code
22	ACT_CLOSE_DT	Date(10)	DATE	Actual Close Date of an Opportunity
23	SALES_MODEL	Character(18)	VARCHAR2(18) NOT NULL	Sales Model Id
24	SALES_STAGE	Character(24)	VARCHAR2(24) NOT NULL	Sales Stage Identifier
25	STAGE_PCT_CLOSE	Number(3,0)	SMALLINT NOT NULL	To indicate a % of how much a stage is completed
26	BO_ID_CUST	Number(31,0)	DECIMAL(31) NOT NULL	This field stores the Business Object ID for the customer.
27	ROLE_TYPE_ID	Number(12,0)	DECIMAL(12) NOT NULL	Role Type ID
28	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit
29	SETID	Character(5)	VARCHAR2(5) NOT NULL	This field is used to store the value of SetID on various setup tables.
30	BO_ID_PARTNER	Signed Number(32,0)	DECIMAL(31) NOT NULL	Business Object ID of Partner Company.
31	CONFIDENCE_PCT	Number(3,0)	SMALLINT NOT NULL	Confidence Percentage
32	SALES_PRIORITY	Character(3)	VARCHAR2(3) NOT NULL	Priority of sales lead and opportunity 1=1-One 2=2-Two 3=3-Three 4=4-Four 5=5-Five 6=6-Six 7=7-Seven 8=8-Eight 9=9-Nine Default Value: 5
33	BUSINESS_UNIT_RA	Character(5)	VARCHAR2(5) NOT NULL	Business Unit Prompt Table: SP_BU_RA_NONVW
34	RA_CAMPAIGN_ID	Character(15)	VARCHAR2(15) NOT NULL	Marketing - Campaign Identification field. Character field that will contain a generated campaign or free form entered campaign id. Prompt Table: RSF_CAMPAIGN_VW
35	REGION_ID	Character(15)	VARCHAR2(15) NOT NULL	This field stores the region id value.
36	TREE_NAME	Character(18)	VARCHAR2(18) NOT NULL	Tree Name Default Value: RSF_OPR_DEF_VW.TREE_NAME Prompt Table: RSF_OPR_TREE_VW
37	TERRITORY_ID	Character(20)	VARCHAR2(20) NOT NULL	Territory ID
38	INDUSTRY_ID	Character(30)	VARCHAR2(30) NOT NULL	Industry ID.
39	LEAD_ID	Character(15)	VARCHAR2(15) NOT NULL	Used by Sales Force Automation, this field uniquely identifies a Sales Lead.
40	FALLOUT_RSN_CD	Character(10)	VARCHAR2(10) NOT NULL	Fall Out Reason code Prompt Table: RSF_FALLOUT_RSN
41	BO_ID1	Number(31,0)	DECIMAL(31) NOT NULL	Business Object ID
42	EST_TF_ANNUAL	Character(32)	VARCHAR2(32) NOT NULL	Timeframe period used in dashboard for estimated close date range
43	EST_TF_QTR	Character(32)	VARCHAR2(32) NOT NULL	Timeframe period used in dashboard for estimated close date range
44	ROW_LASTMANT_DTTM	DateTime(26)	TIMESTAMP	The date and time that the row was last updated.
45	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.
46	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.
47	EST_REVENUE	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	The expected revenue (amount) associated with an Opportunity. This is particularly used for forecasting.
48	ACT_REVENUE	Number(27,3)	DECIMAL(26,3) NOT NULL	Actual Revenue
49	ROW_ADDED_DTTM	DateTime(26)	TIMESTAMP	This is a datetime field for record creation.
50	PRIMARY_FLG	Character(1)	VARCHAR2(1) NOT NULL	This is a flag which takes yes or no values and indicates whether the row is primary. N=No Y=Yes Y/N Table Edit Default Value: N
51	ROLE_CD	Character(4)	VARCHAR2(4) NOT NULL	Sales User Role Code 0001=Manager 0002=Administrator 0003=Consultant 0004=Legal 0005=Other 0006=Liaison 0007=Partner 0007=Partner 0008=Sales Rep 0009=Team Leader 0010=Technical Sales 0011=Technical 0012=Training
52	PCT_ALLOC	Number(3,0)	SMALLINT NOT NULL	Allocation Percentage
53	FORECAST_GRP_TOTAL	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Subtotal
54	FORECAST	Character(1)	VARCHAR2(1) NOT NULL	Include in Cash Forecast N=Not Forecasted Y=Forecasted