RA_CNTMG_RPT_VW

(SQL View)
 Index Back

Content Mgmt Report View

View created to support Content Management Report Query

SELECT A.BUSINESS_UNIT , A.RA_CAMPAIGN_ID , A.START_DATE , A.END_DATE , B.RA_CMPGN_WAVE_ID , C.RA_PAR_CONTENT_ID , C1.DESCR , B.RA_CONTENT_ID , C.DESCR , C.RA_CONTENT_STATUS , C.RA_AVAIL_QTY , D.XLATSHORTNAME FROM PS_RA_CAMPAIGN A , PS_RA_CMPGN_WAVE B , PS_RA_CONTENT C , PS_RA_CONTENT C1 , PSXLATITEM D WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.RA_CAMPAIGN_ID = B.RA_CAMPAIGN_ID AND B.RA_CONTENT_ID <> ' ' AND C.SETID = ( SELECT SETID FROM PS_SET_CNTRL_REC WHERE SETCNTRLVALUE = B.BUSINESS_UNIT AND RECNAME = 'RA_CONTENT') AND C.RA_CONTENT_ID = B.RA_CONTENT_ID AND C.SETID = C1.SETID (+) AND C.RA_PAR_CONTENT_ID = C1.RA_CONTENT_ID (+) AND D.FIELDNAME = 'RA_CONTENT_STATUS' AND D.FIELDVALUE = C.RA_CONTENT_STATUS 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'

  • Related Language Record: RA_CTMG_RPT_LVW

    • # 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 START_DATE Date(10) DATE Start Date for Gen Standing PO
    4 END_DATE Date(10) DATE End date field to store an end date.
    5 RA_CMPGN_WAVE_ID Character(15) VARCHAR2(15) NOT NULL Used to uniquely identify a Marketing - Campaign Activity (wave) ID
    6 RA_PAR_CONTENT_ID Character(15) VARCHAR2(15) NOT NULL Marketing Content Parent ID
    7 DESCR1 Character(30) VARCHAR2(30) NOT NULL Descr
    8 RA_CONTENT_ID Character(15) VARCHAR2(15) NOT NULL Marketing - Content ID
    9 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    10 RA_CONTENT_STATUS Character(4) VARCHAR2(4) NOT NULL Marketing - Content Status
    CMPL=Completed
    EXPR=Expired
    IREV=In Review
    NEW=New
    PLAN=Planning
    RWRK=Re-Work
    11 RA_AVAIL_QTY Signed Number(17,4) DECIMAL(15,4) NOT NULL Requested Quantity to have available
    12 XLATSHORTNAME Character(10) VARCHAR2(10) NOT NULL Translate Value Short Name