RY_AUDDLG360_VW

(SQL View)
 Index Back

Marketing Campaign Record View

Marketing Campaign record view - campaigns with status code not equal to archived

SELECT CASE WHEN A.ROLE_TYPE_ID = 8 AND ROLE_TYPE_ID_ORG=2 THEN A.BO_ID ELSE 0 END AS BO_ID_CONTACT ,CASE WHEN A.ROLE_TYPE_ID = 8 AND ROLE_TYPE_ID_ORG=2 THEN A.ROLE_TYPE_ID ELSE 0 END AS ROLE_TYPE_ID_CNTCT ,CASE WHEN ROLE_TYPE_ID=9 THEN A.BO_ID ELSE CASE WHEN ROLE_TYPE_ID=8 THEN A.BO_ID_COMPANY ELSE CASE WHEN ROLE_TYPE_ID=4 THEN A.BO_ID ELSE 0 END END END AS BO_ID_CUST , CASE WHEN ROLE_TYPE_ID=9 THEN A.ROLE_TYPE_ID ELSE CASE WHEN ROLE_TYPE_ID=8 THEN A.ROLE_TYPE_ID_ORG ELSE CASE WHEN ROLE_TYPE_ID=4 THEN A.ROLE_TYPE_ID ELSE 0 END END END AS ROLE_TYPE_ID_CUST ,CASE WHEN A.ROLE_TYPE_ID = 8 AND ROLE_TYPE_ID_ORG=11 THEN A.BO_ID_COMPANY ELSE 0 END AS BO_ID_PARTNER ,CASE WHEN A.ROLE_TYPE_ID = 8 AND ROLE_TYPE_ID_ORG=11 THEN A.ROLE_TYPE_ID_ORG ELSE 0 END AS ROLE_TYPE_ID_PART ,CASE WHEN A.ROLE_TYPE_ID = 8 AND ROLE_TYPE_ID_ORG=11 THEN A.BO_ID ELSE 0 END AS BO_ID_PART_CONT ,CASE WHEN A.ROLE_TYPE_ID = 8 AND ROLE_TYPE_ID_ORG=11 THEN A.ROLE_TYPE_ID ELSE 0 END AS ROLE_TYPE_ID_PARTC ,E.BUSINESS_UNIT ,E.DIALOG_ID ,E.RY_DIALOG_NAME ,A.RA_LIST_ID ,C.RA_LIST_NAME ,B.RY_LIST_ID ,'EXEC' ,C.SETID , %subrec(RB_AUDIT_SBR,E) FROM PS_RA_LIST_REC A , PS_RY_AUDIENCE B ,PS_RA_LIST C ,PS_RY_AUD_LIST D ,PS_RY_DIALOG E WHERE A.RA_LIST_ID = B.RA_LIST_ID AND B.RY_LIST_ID = D.RY_LIST_ID AND A.RA_LIST_ID = C.RA_LIST_ID AND A.SETID = C.SETID AND D.DIALOG_ID = E.DIALOG_ID AND E.RY_DIALOG_STATUS = 'A' AND A.DELETED_FLG = 'N'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BO_ID_CONTACT Number(31,0) DECIMAL(31) NOT NULL This field indicates the Business Object ID for the contact.
2 ROLE_TYPE_ID_CNTCT Number(12,0) DECIMAL(12) NOT NULL Stores ROLE_TYPE_ID for the contact
3 BO_ID_CUST Number(31,0) DECIMAL(31) NOT NULL This field stores the Business Object ID for the customer.
4 ROLE_TYPE_ID_CUST Number(12,0) DECIMAL(12) NOT NULL Stores ROLE_TYPE_ID for the customer
5 BO_ID_PARTNER Signed Number(32,0) DECIMAL(31) NOT NULL Business Object ID of Partner Company.
6 ROLE_TYPE_ID_PART Number(12,0) DECIMAL(12) NOT NULL Stores ROLE_TYPE_ID for the partner
7 BO_ID_PART_CONT Signed Number(32,0) DECIMAL(31) NOT NULL This is the business object identifier for the partner contact.
8 ROLE_TYPE_ID_PARTC Number(12,0) DECIMAL(12) NOT NULL Stores ROLE_TYPE_ID for the partner contact
9 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
10 DIALOG_ID Number(18,0) DECIMAL(18) NOT NULL Dialog ID field to identify both design time and run time dialog.
11 RY_DIALOG_NAME Character(254) VARCHAR2(254) NOT NULL Field is used to capture a Dialog Name.
12 RA_LIST_ID Character(15) VARCHAR2(15) NOT NULL Audience ID
13 RA_LIST_NAME Character(30) VARCHAR2(30) NOT NULL Audience / List Name
14 RY_LIST_ID Number(18,0) DECIMAL(18) NOT NULL Audience ID for Dialogs, used in the dialog designer.
15 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
16 SETID Character(5) VARCHAR2(5) NOT NULL This field is used to store the value of SetID on various setup tables.
17 ROW_ADDED_DTTM DateTime(26) TIMESTAMP This is a datetime field for record creation.
18 ROW_ADDED_OPRID Character(30) VARCHAR2(30) NOT NULL ID of user who added the row.
19 ROW_LASTMANT_DTTM DateTime(26) TIMESTAMP The date and time that the row was last updated.
20 ROW_LASTMANT_OPRID Character(30) VARCHAR2(30) NOT NULL ID of user who last modified the row.
21 SYNCID Number(10,0) DECIMAL(10) NOT NULL The Synchronization ID field stores a value generated by the sync processor. The value is used to identify the type of object that the sync processor is about to handle.
22 SYNCDTTM DateTime(26) TIMESTAMP Synchronization Last Update Date Time