RSF_PLOR_QL_VW

(SQL View)
Index Back

Opp Rev:Quota Period-Leaks

View underlying pipeline calculations for sales user leaks.

SELECT '3' , M.PERSON_ID_MANAGER , S.PERSON_ID , TP.TIMEFRAME_ID , TP.PERIOD_ID , TP.PERIOD_ID , TP.ROLLUP_SEQNUM , TP.BEGIN_DT , TP.END_DT , O.OPPORTUNITY_ID , O.OPPORTUNITY_STATUS , O.EST_CLOSE_DT , 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 , PS.PIPELINE_SEGMENT , O.CURRENCY_CD , %Round(%decdiv(%DECMULT(O.EST_REVENUE, S.PCT_ALLOC), 100), 3) , %Round(%decdiv(%DECMULT(O.EST_REVENUE, S.SHADOW_PCT), 100), 3) FROM PS_RSF_OPP_SLSREP S , PS_RSF_PIPE_TF_VW TP , PS_RSF_ACC_MGR_VW M , PS_RSF_OPPORTUNITY O , PS_RSF_PL_OPPPS_VW PS WHERE S.PERSON_ID = M.PERSON_ID AND O.EST_CLOSE_DT >= TP.BEGIN_DT AND O.EST_CLOSE_DT <= TP.END_DT AND O.OPPORTUNITY_ID = PS.OPPORTUNITY_ID AND O.OPPORTUNITY_ID = S.OPPORTUNITY_ID AND O.OPPORTUNITY_STATUS = '1' AND TP.ROLLUP_SEQNUM = 1

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 PL_DATATYPE Character(1) VARCHAR2(1) NOT NULL Pipeline Data Type
1=Quota
2=Actual
3=Leak
2 PERSON_ID_MANAGER Character(15) VARCHAR2(15) NOT NULL Contact Identifier for Treasury Contacts
3 PERSON_ID Character(15) VARCHAR2(15) NOT NULL Person ID
4 TIMEFRAME_ID Character(15) VARCHAR2(15) NOT NULL Time Frame
5 PERIOD_ROLLUP_ID Character(15) VARCHAR2(15) NOT NULL Quota Rollup Period
6 PERIOD_ID Character(15) VARCHAR2(15) NOT NULL Period
7 ROLLUP_SEQNUM Number(3,0) SMALLINT NOT NULL Sequence Number
8 BEGIN_DT Date(10) DATE Begin Date
9 END_DT Date(10) DATE This field stores the end date values.
10 OPPORTUNITY_ID Character(15) VARCHAR2(15) NOT NULL Opportunity ID
11 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
12 EST_CLOSE_DT Date(10) DATE Estimated Close Date of an Opportunity
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
22 TERRITORY_ID Character(20) VARCHAR2(20) NOT NULL Territory ID
23 INDUSTRY_ID Character(30) VARCHAR2(30) NOT NULL Industry ID.
24 PIPELINE_SEGMENT Character(15) VARCHAR2(15) NOT NULL Segment corresponds to sales stage.
25 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL This field stores the Currency Code value.
26 REVENUE_AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Revenue Amount
27 SHADOW_AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Shadow Amount