RSFD_MGR_CREV_Q

(SQL View)
 Index Back

Manager Quarterly Closed

This view is used to populate summarized Annual revenue at a manager level in sales dashboard.

SELECT MGR.PERSON_ID_MANAGER , O.OPPORTUNITY_STATUS , AQ.TIMEFRAME_PERIOD , INST.PT_CDB_DEF_CUR_CD , %Round(SUM(%DecDiv(%DecMult(%DecMult(CUR.INST_MULT, O.ACT_REVENUE),REP.PCT_ALLOC),100)),0) FROM PS_RSF_OPPORTUNITY O , PS_RSF_OPP_SLSREP REP , PS_RSF_ACC_MGR_VW MGR , PS_RSFD_TFP_QTR_VW AQ , PS_RBD_CURRT CUR , PSCDBINSTALL INST WHERE OPPORTUNITY_STATUS IN ('1', '2') AND O.ACT_CLOSE_DT BETWEEN AQ.BEGIN_DT AND AQ.END_DT AND O.OPPORTUNITY_ID = REP.OPPORTUNITY_ID AND REP.PERSON_ID = MGR.PERSON_ID AND O.CURRENCY_CD = CUR.CURRENCY_CD GROUP BY MGR.PERSON_ID_MANAGER , O.OPPORTUNITY_STATUS, AQ.TIMEFRAME_PERIOD , INST.PT_CDB_DEF_CUR_CD

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 PERSON_ID_MANAGER Character(15) VARCHAR2(15) NOT NULL Contact Identifier for Treasury Contacts
2 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
3 TIMEFRAME_PERIOD Character(32) VARCHAR2(32) NOT NULL Time Frame
4 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL This field stores the Currency Code value.
5 REVENUE_ACTUAL Number(27,3) DECIMAL(26,3) NOT NULL Actual Revenue