RSF_FCAST_OS_VW (SQL View)	Index Back
Opportunity Shadow Forecast VW The view which bridges between specific product forecasts within opportunities and the forecast detail record which is the foundation for all forecasting. This is however only shadow information, the primary record is the revenue.

RSF_FCAST_OS_VW

(SQL View)

Opportunity Shadow Forecast VW

The view which bridges between specific product forecasts within opportunities and the forecast detail record which is the foundation for all forecasting. This is however only shadow information, the primary record is the revenue.

SELECT DISTINCT 'S' , S.PERSON_ID , S.PERSON_ID , N.NAME , F.FORECAST_TYPE , O.CURRENCY_CD , (%Round(%decdiv(%DECMULT(F.EST_REVENUE, S.SHADOW_PCT) , 100), 3) + SHADOW_AMT) , F.EST_CLOSE_DT , F.CONFIDENCE_PCT , O.OPPORTUNITY_ID , O.OPPORTUNITY_NAME , O.OPPORTUNITY_STATUS , O.SALES_MODEL , O.SALES_STAGE , O.STAGE_PCT_CLOSE , O.ROLE_TYPE_ID_CUST , O.BO_ID_CUST , O.CUST_SETID , O.BUSINESS_UNIT , O.REGION_ID , O.TREE_NAME , O.TERRITORY_ID , O.INDUSTRY_ID , O.PROD_SETID , F.PRODUCT_GROUP , F.PRODUCT_ID , F.REVENUE_TYPE , F.RECUR_FLAG , F.RECUR_PERIOD_NO , F.RECUR_PERIODS , F.COMMENTSHORT , O.LEAD_ID FROM PS_RSF_OPPORTUNITY O , PS_RSF_OPP_SLSREP S , PS_RSF_OPP_FORCST F , PS_RB_PRSN_P_NM_VW N WHERE (O.OPPORTUNITY_STATUS = '0' OR O.OPPORTUNITY_STATUS = '2') AND S.PERSON_ID = N.PERSON_ID AND O.CURRENCY_CD <> ' ' AND F.OPPORTUNITY_ID = O.OPPORTUNITY_ID AND S.OPPORTUNITY_ID = O.OPPORTUNITY_ID AND (S.SHADOW_PCT <> 0 OR S.SHADOW_AMT <> 0) AND F.FORECAST = 'Y' AND S.PERSON_ID <> ' '

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 FORECAST_CATEGORY Character(1) VARCHAR2(1) NOT NULL Forecast Category
R=Revenue Forecast
S=Shadow Forecast

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

3 PERSON_ID_ADJUSTBY Character(15) VARCHAR2(15) NOT NULL Person id of person who adjusted a forecast.

4 SALES_USER_NAME Character(50) VARCHAR2(50) NOT NULL Sales User Name field

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

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

7 FROM_REVENUE Signed Number(28,3) DECIMAL(26,3) NOT NULL From Revenue For Forecast Comparison Ace Report

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

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

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

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

12 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

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

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

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

16 ROLE_TYPE_ID_CUST Number(12,0) DECIMAL(12) NOT NULL Stores ROLE_TYPE_ID for the customer

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

18 CUST_SETID Character(5) VARCHAR2(5) NOT NULL Customer Setid

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

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

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

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

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

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

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

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

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

28 RECUR_FLAG Character(1) VARCHAR2(1) NOT NULL Identifies if a revenue flow is one of a set of recurring.
N=Non-Recuring Revenue
Y=Recurring Revenue

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

30 RECUR_PERIODS Number(5,0) INTEGER NOT NULL Number of periods of recurring price. That is, if a monthly payment plan, how many months of payment.

31 COMMENTS_254 Character(254) VARCHAR2(254) NOT NULL Comments

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

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	FORECAST_CATEGORY	Character(1)	VARCHAR2(1) NOT NULL	Forecast Category R=Revenue Forecast S=Shadow Forecast
2	PERSON_ID	Character(15)	VARCHAR2(15) NOT NULL	Person ID
3	PERSON_ID_ADJUSTBY	Character(15)	VARCHAR2(15) NOT NULL	Person id of person who adjusted a forecast.
4	SALES_USER_NAME	Character(50)	VARCHAR2(50) NOT NULL	Sales User Name field
5	FORECAST_TYPE	Character(10)	VARCHAR2(10) NOT NULL	Type of forecast, eg 'Open' or 'closed'
6	FROM_CUR	Character(3)	VARCHAR2(3) NOT NULL	From Currency Code
7	FROM_REVENUE	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	From Revenue For Forecast Comparison Ace Report
8	EST_CLOSE_DT	Date(10)	DATE	Estimated Close Date of an Opportunity
9	CONFIDENCE_PCT	Number(3,0)	SMALLINT NOT NULL	Confidence Percentage
10	OPPORTUNITY_ID	Character(15)	VARCHAR2(15) NOT NULL	Opportunity ID
11	OPPORTUNITY_NAME	Character(50)	VARCHAR2(50) NOT NULL	Opportunity Name - User Enters this field in Mixed Case.
12	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
13	SALES_MODEL	Character(18)	VARCHAR2(18) NOT NULL	Sales Model Id
14	SALES_STAGE	Character(24)	VARCHAR2(24) NOT NULL	Sales Stage Identifier
15	STAGE_PCT_CLOSE	Number(3,0)	SMALLINT NOT NULL	To indicate a % of how much a stage is completed
16	ROLE_TYPE_ID_CUST	Number(12,0)	DECIMAL(12) NOT NULL	Stores ROLE_TYPE_ID for the customer
17	BO_ID_CUST	Number(31,0)	DECIMAL(31) NOT NULL	This field stores the Business Object ID for the customer.
18	CUST_SETID	Character(5)	VARCHAR2(5) NOT NULL	Customer Setid
19	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit
20	REGION_ID	Character(15)	VARCHAR2(15) NOT NULL	This field stores the region id value.
21	TREE_NAME	Character(18)	VARCHAR2(18) NOT NULL	Tree Name Default Value: RSF_OPR_DEF_VW.TREE_NAME Prompt Table: RSF_OPR_TREE_VW
22	TERRITORY_ID	Character(20)	VARCHAR2(20) NOT NULL	Territory ID
23	INDUSTRY_ID	Character(30)	VARCHAR2(30) NOT NULL	Industry ID.
24	PROD_SETID	Character(5)	VARCHAR2(5) NOT NULL	Product SET ID
25	PRODUCT_GROUP	Character(10)	VARCHAR2(10) NOT NULL	PRoduct Group
26	PRODUCT_ID	Character(18)	VARCHAR2(18) NOT NULL	Product ID
27	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.
28	RECUR_FLAG	Character(1)	VARCHAR2(1) NOT NULL	Identifies if a revenue flow is one of a set of recurring. N=Non-Recuring Revenue Y=Recurring Revenue
29	RECUR_PERIOD_NO	Character(5)	VARCHAR2(5) NOT NULL	The specific recurring period of this revenue flow.
30	RECUR_PERIODS	Number(5,0)	INTEGER NOT NULL	Number of periods of recurring price. That is, if a monthly payment plan, how many months of payment.
31	COMMENTS_254	Character(254)	VARCHAR2(254) NOT NULL	Comments
32	LEAD_ID	Character(15)	VARCHAR2(15) NOT NULL	Used by Sales Force Automation, this field uniquely identifies a Sales Lead.