RSFD_OPPHDR_VW2

(SQL View)
Index Back

Dashboard Sales Opportunity

The view for dashboard of the opportunities with the corresponding Sales Rep.

SELECT O.OPPORTUNITY_ID , O.PERSON_ID , O.FORECAST_TOTAL , O.OPPORTUNITY_NAME , O.OPPORTUNITY_STATUS , O.EST_CLOSE_DT , O.CURRENCY_CD , O.ACT_CLOSE_DT , O.EST_REVENUE , O.ACT_REVENUE , O.SALES_MODEL , O.SALES_STAGE , O.STAGE_PCT_CLOSE , O.BO_ID_CUST , O.ROLE_TYPE_ID , O.CUST_SETID , 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 , L.FALLOUT_RSN_CD , L.BO_ID_CUST , EA.TIMEFRAME_PERIOD ,EQ.TIMEFRAME_PERIOD , AA.TIMEFRAME_PERIOD , AQ.TIMEFRAME_PERIOD , EA.BEGIN_DT , EA.END_DT , EQ.BEGIN_DT , EQ.END_DT , O.ROW_ADDED_DTTM , O.ROW_LASTMANT_DTTM , UTFA.TIMEFRAME_PERIOD , UTFQ.TIMEFRAME_PERIOD , 'Y' FROM PS_RSFD_OPPBASE_VW O , PS_RSF_OPP_CLOSE L , PS_RSFD_TFP_ANN_VW EA , PS_RSFD_TFP_QTR_VW EQ , PS_RSFD_TFP_ANN_VW AA , PS_RSFD_TFP_QTR_VW AQ , PS_RSFD_TFP_ANN_VW UTFA , PS_RSFD_TFP_QTR_VW UTFQ WHERE O.OPPORTUNITY_STATUS IN ('1' , '2') AND O.OPPORTUNITY_ID = L.OPPORTUNITY_ID AND O.ACT_CLOSE_DT BETWEEN AA.BEGIN_DT AND AA.END_DT AND O.ACT_CLOSE_DT BETWEEN AQ.BEGIN_DT AND AQ.END_DT 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(O.ROW_LASTMANT_DTTM) BETWEEN UTFA.BEGIN_DT AND UTFA.END_DT AND %DatePart(O.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 OPPORTUNITY_NAME Character(50) VARCHAR2(50) NOT NULL Opportunity Name - User Enters this field in Mixed Case.
5 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
6 EST_CLOSE_DT Date(10) DATE Estimated Close Date of an Opportunity
7 FROM_CUR Character(3) VARCHAR2(3) NOT NULL From Currency Code
8 ACT_CLOSE_DT Date(10) DATE Actual Close Date of an Opportunity
9 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.
10 ACT_REVENUE Number(27,3) DECIMAL(26,3) NOT NULL Actual Revenue
11 SALES_MODEL Character(18) VARCHAR2(18) NOT NULL Sales Model Id
12 SALES_STAGE Character(24) VARCHAR2(24) NOT NULL Sales Stage Identifier
13 STAGE_PCT_CLOSE Number(3,0) SMALLINT NOT NULL To indicate a % of how much a stage is completed
14 BO_ID_CUST Number(31,0) DECIMAL(31) NOT NULL This field stores the Business Object ID for the customer.
15 ROLE_TYPE_ID Number(12,0) DECIMAL(12) NOT NULL Role Type ID
16 CUST_SETID Character(5) VARCHAR2(5) NOT NULL Customer Setid
17 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
18 SETID Character(5) VARCHAR2(5) NOT NULL This field is used to store the value of SetID on various setup tables.
19 BO_ID_PARTNER Signed Number(32,0) DECIMAL(31) NOT NULL Business Object ID of Partner Company.
20 CONFIDENCE_PCT Number(3,0) SMALLINT NOT NULL Confidence Percentage
21 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

22 BUSINESS_UNIT_RA Character(5) VARCHAR2(5) NOT NULL Business Unit

Prompt Table: SP_BU_RA_NONVW

23 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

24 REGION_ID Character(15) VARCHAR2(15) NOT NULL This field stores the region id value.
25 TREE_NAME Character(18) VARCHAR2(18) NOT NULL Tree Name

Default Value: RSF_OPR_DEF_VW.TREE_NAME

Prompt Table: RSF_OPR_TREE_VW

26 TERRITORY_ID Character(20) VARCHAR2(20) NOT NULL Territory ID
27 INDUSTRY_ID Character(30) VARCHAR2(30) NOT NULL Industry ID.
28 LEAD_ID Character(15) VARCHAR2(15) NOT NULL Used by Sales Force Automation, this field uniquely identifies a Sales Lead.
29 FALLOUT_RSN_CD Character(10) VARCHAR2(10) NOT NULL Fall Out Reason code

Prompt Table: RSF_FALLOUT_RSN

30 BO_ID1 Number(31,0) DECIMAL(31) NOT NULL Business Object ID
31 EST_TF_ANNUAL Character(32) VARCHAR2(32) NOT NULL Timeframe period used in dashboard for estimated close date range
32 EST_TF_QTR Character(32) VARCHAR2(32) NOT NULL Timeframe period used in dashboard for estimated close date range
33 ACT_TF_ANNUAL Character(32) VARCHAR2(32) NOT NULL Timeframe period used in dashboard for estimated close date range
34 ACT_TF_QTR Character(32) VARCHAR2(32) NOT NULL Timeframe period used in dashboard for estimated close date range
35 BEGIN_DT_ANN_EST Date(10) DATE Timeframe dates for dashboard: annual begin
36 END_DT_ANN_EST Date(10) DATE Timeframe dates for dashboard: annual end
37 BEGIN_DT_QTR_EST Date(10) DATE Timeframe dates for dashboard: quarter begin
38 END_DT_QTR_EST Date(10) DATE Timeframe dates for dashboard: quarter end
39 ROW_ADDED_DTTM DateTime(26) TIMESTAMP This is a datetime field for record creation.
40 ROW_LASTMANT_DTTM DateTime(26) TIMESTAMP The date and time that the row was last updated.
41 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.
42 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.
43 INCL_IN_PLAN Character(1) VARCHAR2(1) NOT NULL Include in Planning