RSFD_OPF_TF_VW (SQL View)	Index Back
Opportunity Forecast Timeframe This is for the opportunity forecast as a lookup for dashboard

RSFD_OPF_TF_VW

(SQL View)

Opportunity Forecast Timeframe

This is for the opportunity forecast as a lookup for dashboard

SELECT F.OPPORTUNITY_ID , F.FORECAST_TYPE , F.EST_CLOSE_DT , F.PRODUCT_GROUP , F.PRODUCT_ID , F.RECUR_PERIOD_NO , F.REVENUE_TYPE , F.EST_REVENUE , F.CONFIDENCE_PCT , F.RECUR_FLAG , TFA.TIMEFRAME_PERIOD , TFA.BEGIN_DT , TFA.END_DT , TFQ.TIMEFRAME_PERIOD , TFQ.BEGIN_DT , TFQ.END_DT , F.ROW_LASTMANT_DTTM , UTFA.TIMEFRAME_PERIOD , UTFQ.TIMEFRAME_PERIOD FROM PS_RSF_OPP_FORCST F , PS_RSFD_TFP_ANN_VW TFA , PS_RSFD_TFP_QTR_VW TFQ , PS_RSFD_TFP_ANN_VW UTFA , PS_RSFD_TFP_QTR_VW UTFQ WHERE F.EST_CLOSE_DT >= TFA.BEGIN_DT AND F.EST_CLOSE_DT <= TFA.END_DT AND F.EST_CLOSE_DT >= TFQ.BEGIN_DT AND F.EST_CLOSE_DT <= TFQ.END_DT AND %DatePart(F.ROW_LASTMANT_DTTM) BETWEEN UTFA.BEGIN_DT AND UTFA.END_DT AND %DatePart(F.ROW_LASTMANT_DTTM) BETWEEN UTFQ.BEGIN_DT AND UTFQ.END_DT AND F.FORECAST= 'Y' AND F.EST_REVENUE > 0

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

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

2 FORECAST_TYPE Character(10) VARCHAR2(10) NOT NULL Type of forecast, eg 'Open' or 'closed'
Prompt Table: RSF_FCAST_TO_VW

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

4 PRODUCT_GROUP Character(10) VARCHAR2(10) NOT NULL PRoduct Group
Prompt Table: RSF_PRODGRP_VW

5 PRODUCT_ID Character(18) VARCHAR2(18) NOT NULL Product ID
Prompt Table: %EDITTABLE2

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

7 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.
Prompt Table: RSF_REV_TYPE_VW

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

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

10 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
Default Value: N

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

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

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

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

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

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

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

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

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

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	OPPORTUNITY_ID	Character(15)	VARCHAR2(15) NOT NULL	Opportunity ID
2	FORECAST_TYPE	Character(10)	VARCHAR2(10) NOT NULL	Type of forecast, eg 'Open' or 'closed' Prompt Table: RSF_FCAST_TO_VW
3	EST_CLOSE_DT	Date(10)	DATE	Estimated Close Date of an Opportunity
4	PRODUCT_GROUP	Character(10)	VARCHAR2(10) NOT NULL	PRoduct Group Prompt Table: RSF_PRODGRP_VW
5	PRODUCT_ID	Character(18)	VARCHAR2(18) NOT NULL	Product ID Prompt Table: %EDITTABLE2
6	RECUR_PERIOD_NO	Character(5)	VARCHAR2(5) NOT NULL	The specific recurring period of this revenue flow.
7	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. Prompt Table: RSF_REV_TYPE_VW
8	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.
9	CONFIDENCE_PCT	Number(3,0)	SMALLINT NOT NULL	Confidence Percentage
10	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 Default Value: N
11	FCST_TF_ANNUAL	Character(32)	VARCHAR2(32) NOT NULL	Timeframe period used in dashboard for estimated close date range
12	BEGIN_DT_ANN_FCST	Date(10)	DATE	Timeframe dates for dashboard: annual begin
13	END_DT_ANN_FCST	Date(10)	DATE	Timeframe dates for dashboard: annual end
14	FCST_TF_QTR	Character(32)	VARCHAR2(32) NOT NULL	Timeframe period used in dashboard for estimated close date range
15	BEGIN_DT_QTR_FCST	Date(10)	DATE	Timeframe dates for dashboard: quarter begin
16	END_DT_QTR_FCST	Date(10)	DATE	Timeframe dates for dashboard: quarter end
17	ROW_LASTMANT_DTTM	DateTime(26)	TIMESTAMP	The date and time that the row was last updated.
18	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.
19	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.