RA_REVENUE_VW (SQL View)	Index Back
View for Revenue Metrics This view will be used for both Campaign and Wave. It uses two other views PS_RA_RATE_TBL_VW and PS_RA_BUD_CURR_VW which should be built before this.

RA_REVENUE_VW

(SQL View)

View for Revenue Metrics

This view will be used for both Campaign and Wave. It uses two other views PS_RA_RATE_TBL_VW and PS_RA_BUD_CURR_VW which should be built before this.

SELECT A.BUSINESS_UNIT , A.RA_CAMPAIGN_ID , B.RA_CMPGN_WAVE_ID , A.RA_BUDGET_CURR , B.LEAD_ID , C.OPPORTUNITY_ID , C.CURRENCY_CD , C.ACT_REVENUE , D.RATE_MULT FROM PS_RA_BUD_CURR_VW A , PS_RSF_LEAD B , PS_RSF_OPPORTUNITY C , PS_RA_RATE_TBL_VW D WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT_RA AND A.RA_CAMPAIGN_ID = B.RA_CAMPAIGN_ID AND B.OPPORTUNITY_ID = C.OPPORTUNITY_ID AND C.CURRENCY_CD = D.FROM_CUR AND A.RA_BUDGET_CURR = D.TO_CUR AND D.EFFDT = ( SELECT MAX(EFFDT) FROM PS_RA_RATE_TBL_VW WHERE FROM_CUR = D.FROM_CUR AND TO_CUR = D.TO_CUR AND EFFDT <= %CurrentDateIn)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

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

3 RA_CMPGN_WAVE_ID Character(15) VARCHAR2(15) NOT NULL Used to uniquely identify a Marketing - Campaign Activity (wave) ID

4 RA_BUDGET_CURR Character(4) VARCHAR2(4) NOT NULL Campaign Budget Currency removed xlat values - use currency code table
Prompt Table: CURRENCY_CD_TBL

5 LEAD_ID Character(15) VARCHAR2(15) NOT NULL Used by Sales Force Automation, this field uniquely identifies a Sales Lead.

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

7 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL This field stores the Currency Code value.

8 ACT_REVENUE Number(27,3) DECIMAL(26,3) NOT NULL Actual Revenue

9 RATE_MULT Signed Number(17,8) DECIMAL(15,8) NOT NULL Rate Multiplier

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit
2	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.
3	RA_CMPGN_WAVE_ID	Character(15)	VARCHAR2(15) NOT NULL	Used to uniquely identify a Marketing - Campaign Activity (wave) ID
4	RA_BUDGET_CURR	Character(4)	VARCHAR2(4) NOT NULL	Campaign Budget Currency removed xlat values - use currency code table Prompt Table: CURRENCY_CD_TBL
5	LEAD_ID	Character(15)	VARCHAR2(15) NOT NULL	Used by Sales Force Automation, this field uniquely identifies a Sales Lead.
6	OPPORTUNITY_ID	Character(15)	VARCHAR2(15) NOT NULL	Opportunity ID
7	CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	This field stores the Currency Code value.
8	ACT_REVENUE	Number(27,3)	DECIMAL(26,3) NOT NULL	Actual Revenue
9	RATE_MULT	Signed Number(17,8)	DECIMAL(15,8) NOT NULL	Rate Multiplier