RSFD_OPPREP_VW2

(SQL View)
 Index Back

Dashboard Sales Opportunity

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

SELECT S.OPPORTUNITY_ID , S.PERSON_ID , O.FORECAST_TOTAL , S.PCT_ALLOC , S.ROLE_CD , S.PRIMARY_FLG , O.OPPORTUNITY_NAME , O.OPPORTUNITY_STATUS , O.EST_CLOSE_DT , O.CURRENCY_CD , O.ACT_CLOSE_DT , 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 , L.FALLOUT_RSN_CD , L.BO_ID_CUST , EA.TIMEFRAME_PERIOD , EQ.TIMEFRAME_PERIOD , EA.BEGIN_DT , EA.END_DT , EQ.BEGIN_DT , EQ.END_DT , O.ROW_LASTMANT_DTTM , UTFA.TIMEFRAME_PERIOD , UTFQ.TIMEFRAME_PERIOD , O.EST_REVENUE , O.ACT_REVENUE , O.ROW_ADDED_DTTM , 'Y' FROM PS_RSF_OPP_SLSREP S , 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 UTFA , PS_RSFD_TFP_QTR_VW UTFQ WHERE S.OPPORTUNITY_ID = O.OPPORTUNITY_ID AND O.OPPORTUNITY_STATUS IN ('1' , '2') AND S.OPPORTUNITY_ID = L.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(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 PCT_ALLOC Number(3,0) SMALLINT NOT NULL Allocation Percentage
5 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
6 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

7 OPPORTUNITY_NAME Character(50) VARCHAR2(50) NOT NULL Opportunity Name - User Enters this field in Mixed Case.
8 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
9 EST_CLOSE_DT Date(10) DATE Estimated Close Date of an Opportunity
10 FROM_CUR Character(3) VARCHAR2(3) NOT NULL From Currency Code
11 ACT_CLOSE_DT Date(10) DATE Actual Close Date of an Opportunity
12 SALES_MODEL Character(18) VARCHAR2(18) NOT NULL Sales Model Id
13 SALES_STAGE Character(24) VARCHAR2(24) NOT NULL Sales Stage Identifier
14 STAGE_PCT_CLOSE Number(3,0) SMALLINT NOT NULL To indicate a % of how much a stage is completed
15 BO_ID_CUST Number(31,0) DECIMAL(31) NOT NULL This field stores the Business Object ID for the customer.
16 ROLE_TYPE_ID Number(12,0) DECIMAL(12) NOT NULL Role Type ID
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 BEGIN_DT_ANN_EST Date(10) DATE Timeframe dates for dashboard: annual begin
34 END_DT_ANN_EST Date(10) DATE Timeframe dates for dashboard: annual end
35 BEGIN_DT_QTR_EST Date(10) DATE Timeframe dates for dashboard: quarter begin
36 END_DT_QTR_EST Date(10) DATE Timeframe dates for dashboard: quarter end
37 ROW_LASTMANT_DTTM DateTime(26) TIMESTAMP The date and time that the row was last updated.
38 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.
39 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.
40 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.
41 ACT_REVENUE Number(27,3) DECIMAL(26,3) NOT NULL Actual Revenue
42 ROW_ADDED_DTTM DateTime(26) TIMESTAMP This is a datetime field for record creation.
43 INCL_IN_PLAN Character(1) VARCHAR2(1) NOT NULL Include in Planning