RT_SALE_CONV_VW (SQL View)	Index Back
Sales Conversion Rate View View to get data on Telemarketing conversions to Sales

RT_SALE_CONV_VW

(SQL View)

Sales Conversion Rate View

View to get data on Telemarketing conversions to Sales

SELECT A.LEAD_ID , A.RA_CAMPAIGN_ID , A.RA_CMPGN_WAVE_ID , A.BUSINESS_UNIT , B.RC_FINAL_DISPOSTN , B.ROW_LASTMANT_DTTM , B.SETID_PROV_GRP , B.PROVIDER_GRP_ID , B.ASSIGNED_TO FROM PS_RSF_LEAD A , PS_RC_CALL_RESULT B , PS_RA_CMPGN_WAVE D WHERE A.RC_PROSPECT_SW = 'Y' AND A.LEAD_ID = B.LEAD_ID AND B.RC_CALL_STATUS = 'CL' AND B.SEQNO = ( SELECT MAX( C.SEQNO) FROM PS_RC_CALL_RESULT C WHERE C.LEAD_ID = B.LEAD_ID) AND A.RA_CAMPAIGN_ID = D.RA_CAMPAIGN_ID AND A.RA_CMPGN_WAVE_ID = D.RA_CMPGN_WAVE_ID AND A.BUSINESS_UNIT = D.BUSINESS_UNIT AND (D.RA_INTEGRATE_MTHD = 'OCD' OR D.RA_INTEGRATE_MTHD = 'OCI')

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

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

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 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

5 RC_FINAL_DISPOSTN Character(2) VARCHAR2(2) NOT NULL Outbound Marketing final disposition status field
DN=Do Not Call Again
GC=To Capture an Order
GL=Generate a Lead
NI=Not Interested
SA=Sale
SE=Send Collateral by Email
SM=Send Collateral by Mail

6 ROW_LASTMANT_DTTM DateTime(26) TIMESTAMP The date and time that the row was last updated.

7 SETID_PROV_GRP Character(5) VARCHAR2(5) NOT NULL SetID

8 PROVIDER_GRP_ID Character(10) VARCHAR2(10) NOT NULL Identification number for the provider group.

9 ASSIGNED_TO Character(15) VARCHAR2(15) NOT NULL This is a field to store assignment information.

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	LEAD_ID	Character(15)	VARCHAR2(15) NOT NULL	Used by Sales Force Automation, this field uniquely identifies a Sales Lead.
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	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit
5	RC_FINAL_DISPOSTN	Character(2)	VARCHAR2(2) NOT NULL	Outbound Marketing final disposition status field DN=Do Not Call Again GC=To Capture an Order GL=Generate a Lead NI=Not Interested SA=Sale SE=Send Collateral by Email SM=Send Collateral by Mail
6	ROW_LASTMANT_DTTM	DateTime(26)	TIMESTAMP	The date and time that the row was last updated.
7	SETID_PROV_GRP	Character(5)	VARCHAR2(5) NOT NULL	SetID
8	PROVIDER_GRP_ID	Character(10)	VARCHAR2(10) NOT NULL	Identification number for the provider group.
9	ASSIGNED_TO	Character(15)	VARCHAR2(15) NOT NULL	This is a field to store assignment information.