RA_ORDERWAVE_VW

(SQL View)
 Index Back

Link Order customer to Wave

View to link order customer to the campaign wave record

SELECT DISTINCT A.BO_ID ,A.ROLE_TYPE_ID ,A.BO_ID_COMPANY ,P.BO_ID ,B.BUSINESS_UNIT ,B.RA_CAMPAIGN_ID ,B.RA_CMPGN_WAVE_ID ,B.RA_WAVE_NAME ,B.RA_PRIORITY ,B.RA_OFFER_ID ,C.SETID ,C.RA_OFFER_NAME ,C.PRICE_SET_ID ,B.RA_SCRIPT_ID_INBN ,D.XLATSHORTNAME ,F.SETID ,F.PRODUCT_ID ,F.DESCR ,F.UNIT_OF_MEASURE ,B.RA_PROMOTION_CODE FROM PS_RA_LIST_REC A , PS_RA_CMPGN_WAVE B , PS_RA_OFFER C , PS_RA_OFFER_PARTNE P , PSXLATITEM D , PS_RA_OFFPRDPRC_VW F , PS_RA_UD_CHNL_TYPE G WHERE C.SETID = P.SETID(+) AND C.RA_OFFER_ID = P.RA_OFFER_ID(+) AND C.PRICE_SET_ID = P.PRICE_SET_ID(+) AND A.RA_LIST_ID = B.RA_LIST_ID AND A.SETID = ( SELECT SETID FROM PS_SET_CNTRL_REC WHERE SETCNTRLVALUE = B.BUSINESS_UNIT AND RECNAME = 'RA_LIST_REC') AND A.DELETED_FLG = 'N' AND C.SETID = ( SELECT SETID FROM PS_SET_CNTRL_REC WHERE SETCNTRLVALUE = B.BUSINESS_UNIT AND RECNAME = 'RA_OFFER') AND B.RA_CMPGN_STATUS_CD = 'EXEC' AND B.RA_OFFER_ID = C.RA_OFFER_ID AND B.RA_SCRIPT_ID_INBN <> 0 AND B.RA_ACTIVITY_CD = G.RA_ACTIVITY_CD AND B.RA_CHANNEL_TYPE = G.RA_CHANNEL_TYPE AND G.SETID = ( SELECT SETID FROM PS_SET_CNTRL_REC WHERE SETCNTRLVALUE = B.BUSINESS_UNIT AND RECNAME = 'RA_UD_CHNL_TYPE') AND G.RA_CHNL_EXECUTION= '030' AND D.FIELDNAME = 'RA_PRIORITY' AND D.FIELDVALUE = B.RA_PRIORITY AND D.EFFDT = ( SELECT MAX(E.EFFDT) FROM PSXLATITEM E WHERE E.FIELDNAME = D.FIELDNAME AND E.FIELDVALUE = D.FIELDVALUE AND E.EFFDT <= %CurrentDateIn) AND D.EFF_STATUS = 'A' AND F.SETID = C.SETID AND F.RA_OFFER_ID = C.RA_OFFER_ID AND F.PRICE_SET_ID = C.PRICE_SET_ID

  • Related Language Record: RA_ODERWAVE_LVW

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 BO_ID Signed Number(32,0) DECIMAL(31) NOT NULL Business Object ID
    2 ROLE_TYPE_ID Number(12,0) DECIMAL(12) NOT NULL Role Type ID
    3 BO_ID_COMPANY Signed Number(32,0) DECIMAL(31) NOT NULL Business Object ID
    4 BO_ID_PARTNER Signed Number(32,0) DECIMAL(31) NOT NULL Business Object ID of Partner Company.
    5 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
    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
    8 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.
    9 RA_PRIORITY Character(1) VARCHAR2(1) NOT NULL Marketing - campaign wave content task priority
    1=High Priority
    2=Medium Priority
    3=Low Priority
    10 RA_OFFER_ID Character(15) VARCHAR2(15) NOT NULL Marketing Offer ID
    11 SETID Character(5) VARCHAR2(5) NOT NULL This field is used to store the value of SetID on various setup tables.
    12 RA_OFFER_NAME Character(50) VARCHAR2(50) NOT NULL Marketing Offer Name
    13 PRICE_SET_ID Character(10) VARCHAR2(10) NOT NULL Price Set ID
    14 RA_SCRIPT_ID_INBN Number(12,0) DECIMAL(12) NOT NULL Inbound Script ID - Numeric
    15 XLATSHORTNAME Character(10) VARCHAR2(10) NOT NULL Translate Value Short Name
    16 SETID_PRODUCT Character(5) VARCHAR2(5) NOT NULL Product SetID
    17 PRODUCT_ID Character(18) VARCHAR2(18) NOT NULL Product ID
    18 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    19 UNIT_OF_MEASURE Character(3) VARCHAR2(3) NOT NULL Used on an approval rule set.
    CS=Case
    EA=Each
    MHR=Muti Hourly
    PER=Percentage
    SQF=Square Footage
    20 RA_PROMOTION_CODE Character(35) VARCHAR2(35) NOT NULL Marketing - campaign wave promotion code