RA_WHATIFQRY_VW

(SQL View)
 Index Back

View used in BAM reports

Business Analysis Modeler - view used for various marketing reporting

SELECT A.BUSINESS_UNIT ,PRG.RA_ROLLUP_CMPGN_ID ,PRG.RA_ROLLUP_CM_NAME ,R.RA_LIST_ID ,R.RA_LIST_NAME ,A.RA_CAMPAIGN_ID ,B.RA_CMPGN_WAVE_ID ,A.RA_CMPGN_NAME , A.RA_CMPGN_STATUS_CD ,A.START_DATE ,A.END_DATE ,B.RA_WAVE_NAME ,L.DESCR ,CASE WHEN R.LIST_CURR_COUNT IS NULL THEN 0 WHEN R.LIST_CURR_COUNT IS NOT NULL THEN R.LIST_CURR_COUNT END ,CASE WHEN C.NO_OF_LEADS IS NULL THEN 0 WHEN C.NO_OF_LEADS IS NOT NULL THEN C.NO_OF_LEADS END ,CASE WHEN P.NO_OF_OPPOR IS NULL THEN 0 WHEN P.NO_OF_OPPOR IS NOT NULL THEN P.NO_OF_OPPOR END ,CASE WHEN J.NO_OF_CUSTOMERS IS NULL THEN 0 WHEN J.NO_OF_CUSTOMERS IS NOT NULL THEN J.NO_OF_CUSTOMERS END ,CASE WHEN WC.RA_ACTUAL_AMT IS NULL THEN 0 WHEN WC.RA_ACTUAL_AMT IS NOT NULL THEN WC.RA_ACTUAL_AMT END ,CASE WHEN J.REV_AMT IS NULL THEN 0 WHEN J.REV_AMT IS NOT NULL THEN J.REV_AMT END FROM PS_RA_CAMPAIGN A ,PS_RA_CMPGN_WAVE B ,PS_RA_LIST_WAVE_VW R ,PS_RA_WAVE_STAT_VW RR ,PS_RA_LEAD_COUNT C ,PS_RA_OPP_COUNT P ,PS_RA_CMPG_WAV_CST WC ,PS_RA_CUST_COUNT J ,PS_RA_UD_CHNL_TYPE L ,PS_RA_PGM_GRD_VW1 PRG WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.RA_CAMPAIGN_ID = B.RA_CAMPAIGN_ID AND A.BUSINESS_UNIT = PRG.BUSINESS_UNIT AND A.RA_CAMPAIGN_ID = PRG.RA_CAMPAIGN_ID AND B.BUSINESS_UNIT = R.BUSINESS_UNIT(+) AND B.RA_CAMPAIGN_ID = R.RA_CAMPAIGN_ID(+) AND B.RA_CMPGN_WAVE_ID = R.RA_CMPGN_WAVE_ID(+) AND B.BUSINESS_UNIT = RR.BUSINESS_UNIT(+) AND B.RA_CAMPAIGN_ID = RR.RA_CAMPAIGN_ID(+) AND B.RA_CMPGN_WAVE_ID = RR.RA_CMPGN_WAVE_ID(+) AND B.RA_ACTIVITY_CD = L.RA_ACTIVITY_CD(+) AND B.RA_CHANNEL_TYPE = L.RA_CHANNEL_TYPE(+) AND B.BUSINESS_UNIT = C.BUSINESS_UNIT(+) AND B.RA_CAMPAIGN_ID = C.RA_CAMPAIGN_ID(+) AND B.RA_CMPGN_WAVE_ID = C.RA_CMPGN_WAVE_ID(+) AND B.BUSINESS_UNIT = P.BUSINESS_UNIT(+) AND B.RA_CAMPAIGN_ID = P.RA_CAMPAIGN_ID(+) AND B.RA_CMPGN_WAVE_ID = P.RA_CMPGN_WAVE_ID(+) AND B.BUSINESS_UNIT = WC.BUSINESS_UNIT(+) AND B.RA_CAMPAIGN_ID = WC.RA_CAMPAIGN_ID(+) AND B.RA_CMPGN_WAVE_ID = WC.RA_CMPGN_WAVE_ID(+) AND B.RA_PROMOTION_CODE = J.RA_PROMOTION_CODE(+) AND L.SETID = RR.SETID GROUP BY A.BUSINESS_UNIT,PRG.RA_ROLLUP_CMPGN_ID,PRG.RA_ROLLUP_CM_NAME,R.RA_LIST_ID,R.RA_LIST_NAME,A.RA_CAMPAIGN_ID,A.RA_CMPGN_NAME, A.RA_CMPGN_STATUS_CD,A.START_DATE,A.END_DATE, B.RA_CMPGN_WAVE_ID,B.RA_WAVE_NAME,L.DESCR,B.RA_ACTIVITY_CD,B.RA_CHANNEL_TYPE, R.LIST_CURR_COUNT,C.NO_OF_LEADS, P.NO_OF_OPPOR, J.NO_OF_CUSTOMERS, WC.RA_ACTUAL_AMT,J.REV_AMT

  • Related Language Record: RA_WHATIF_LNG

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
    2 RA_ROLLUP_CMPGN_ID Character(15) VARCHAR2(15) NOT NULL Campaign - Roll Up (Parent) Campaign Identification
    3 RA_ROLLUP_CM_NAME Character(50) VARCHAR2(50) NOT NULL Rollup Campaign Name - used in view for Home Grd. This is the name of the rollup campaign
    4 RA_LIST_ID Character(15) VARCHAR2(15) NOT NULL Audience ID
    5 RA_LIST_NAME Character(30) VARCHAR2(30) NOT NULL Audience / List Name
    6 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.
    7 RA_CMPGN_WAVE_ID Character(15) VARCHAR2(15) NOT NULL Used to uniquely identify a Marketing - Campaign Activity (wave) ID

    Prompt Table: RA_UD_CPGN_STAT

    8 RA_CMPGN_NAME Character(50) VARCHAR2(50) NOT NULL Campaign Name - field containing the name of the campaign.
    9 RA_CMPGN_STATUS_CD Character(4) VARCHAR2(4) NOT NULL Valid Campaign and Wave Status Codes
    APPR=Approved
    ARCH=Archived
    CMPL=Completed
    DCLN=Declined
    EXEC=Executing
    IREV=In Review
    NEW=New
    STOP=Stop
    TRIG=Trigger Approved
    10 START_DATE Date(10) DATE Start Date for Gen Standing PO
    11 END_DATE Date(10) DATE End date field to store an end date.
    12 RA_WAVE_NAME Character(50) VARCHAR2(50) NOT NULL Campaign Activity (Wave) Name - field containing the campaign activity (wave) name. Campaign Activity (Wave Name) could be either program generated or free form entry.
    13 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    14 LIST_CURR_COUNT Number(18,0) DECIMAL(18) NOT NULL Audience current count
    15 NO_OF_LEADS Number(10,0) DECIMAL(10) NOT NULL Number of leads field
    16 NO_OF_OPPOR Number(10,0) DECIMAL(10) NOT NULL Field used in Interactive report. RA_WHATIF
    17 NO_OF_CUSTOMERS Number(10,0) DECIMAL(10) NOT NULL No. of Customers
    18 RA_CMPGN_COST Number(20,2) DECIMAL(19,2) NOT NULL Campaign Cost
    19 RA_CMPGN_REVENUE Number(20,2) DECIMAL(19,2) NOT NULL Field used in Interactive report. RA_WHATIF