APPS.WF_PLUG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 95

  DESCRIPTION:  	Selects the plug definition and sets the cookie

============================================================================*/
PROCEDURE get_plug_definition (
p_plug_id IN NUMBER,
p_worklist_definition OUT NOCOPY wf_plug.wf_worklist_definition_record
) IS

ii                         NUMBER         := 0;

Line: 114

       SELECT  ROWID ROW_ID,
               PLUG_ID,
   	       USERNAME,
      	       DEFINITION_NAME,
   	       WHERE_STATUS,
	       WHERE_FROM,
	       WHERE_ITEM_TYPE,
	       WHERE_NOTIF_TYPE,
	       WHERE_SUBJECT,
	       WHERE_SENT_START,
	       WHERE_SENT_END,
	       WHERE_DUE_START,
	       WHERE_DUE_END,
	       WHERE_PRIORITY,
	       WHERE_NOTIF_DEL_BY_ME,
	       ORDER_PRIMARY,
	       ORDER_ASC_DESC
       INTO    p_worklist_definition
       FROM    WF_WORKLIST_DEFINITIONS
       WHERE   PLUG_ID = p_plug_id;

Line: 146

           INSERT INTO WF_WORKLIST_DEFINITIONS
           (   PLUG_ID,
   	       USERNAME,
      	       DEFINITION_NAME,
	       WHERE_STATUS,
	       WHERE_FROM,
	       WHERE_ITEM_TYPE,
	       WHERE_NOTIF_TYPE,
	       WHERE_SUBJECT,
	       WHERE_SENT_START,
	       WHERE_SENT_END,
	       WHERE_DUE_START,
	       WHERE_DUE_END,
	       WHERE_PRIORITY,
	       WHERE_NOTIF_DEL_BY_ME,
	       ORDER_PRIMARY,
	       ORDER_ASC_DESC
           )
           SELECT	p_plug_id,
                	null,
                	null,
                        'OPEN',
                	'*',
                        '*',
                        '*',
                        null,
                        null,
                        null,
                        null,
                        null,
                        'HML',
                        '0',
                        'PRIORITY',
                        null
            FROM    SYS.DUAL
            WHERE   NOT EXISTS
                    (SELECT 1
                     FROM   WF_WORKLIST_DEFINITIONS
                     WHERE  PLUG_ID = p_plug_id);

Line: 186

           INSERT INTO WF_WORKLIST_COL_DEFINITIONS
           (	   PLUG_ID			,
   		   USERNAME		,
		   DEFINITION_NAME		,
        	   COLUMN_NUMBER           ,
        	   COLUMN_NAME             ,
	           COLUMN_SIZE
   	   )
           SELECT  p_plug_id,
                         null,
                         null,
                         1,
                         'SUBJECT',
                         100
                FROM    SYS.DUAL
                WHERE   NOT EXISTS
                (SELECT 1
                 FROM   WF_WORKLIST_COL_DEFINITIONS
                 WHERE  PLUG_ID = p_plug_id);

Line: 260

    select WL.MEANING, WL.LOOKUP_CODE
    from WF_LOOKUPS WL
    where WL.LOOKUP_TYPE = lktype
    order by WL.MEANING;

Line: 267

    select unique WIT.DISPLAY_NAME, WN.MESSAGE_TYPE
    from WF_NOTIFICATIONS WN, WF_ITEM_TYPES_VL WIT
    where WN.MESSAGE_TYPE = WIT.NAME
    and WN.RECIPIENT_ROLE = role
    order by WIT.DISPLAY_NAME;

Line: 307

  htp.formSelectOpen('ittype');

Line: 313

       htp.formSelectOption(cvalue=>ittype_list.display_name,
                            cselected => 'SELECTED',
                            cattributes=>'value='||ittype_list.message_type);

Line: 319

       htp.formSelectOption(cvalue=>ittype_list.display_name,
                            cattributes=>'value='||ittype_list.message_type);

Line: 328

     htp.formSelectOption(cvalue=>wf_core.translate('ALL'),
                          cselected => 'SELECTED',
                          cattributes=>'value=*');

Line: 334

     htp.formSelectOption(cvalue=>wf_core.translate('ALL'),
                          cattributes=>'value=*');

Line: 339

  htp.formSelectClose;

Line: 511

    select WN.NOTIFICATION_ID nid,
           WN.PRIORITY,
           WIT.DISPLAY_NAME message_type,
           WN.SUBJECT,
           WN.BEGIN_DATE,
           WN.DUE_DATE,
           WN.END_DATE,
           WL.MEANING display_status,
	   WN.STATUS,
           WN.LANGUAGE
    from WF_NOTIFICATIONS WN, WF_ITEM_TYPES_VL WIT, WF_LOOKUPS WL
    where WN.MESSAGE_TYPE = decode(Worklist.littype, '*', WN.MESSAGE_TYPE,
				     Worklist.littype)
    and WN.MESSAGE_TYPE = WIT.NAME
    and WL.LOOKUP_TYPE = 'WF_NOTIFICATION_STATUS'
    and WN.STATUS = WL.LOOKUP_CODE
    and WN.RECIPIENT_ROLE in
        (select WUR.ROLE_NAME
         from WF_USER_ROLES WUR
         where WUR.USER_ORIG_SYSTEM = Worklist.uorig_system
         and WUR.USER_ORIG_SYSTEM_ID = Worklist.uorig_system_id
         and WUR.USER_NAME = Worklist.username)
    and ((Worklist.lfromuser = '*' ) or
	 (WN.ORIGINAL_RECIPIENT = upper(Worklist.lfromuser)))
    and WN.STATUS = 'OPEN'
    and ((WN.BEGIN_DATE is null)
          or  ((WN.BEGIN_DATE >= decode(Worklist.lbeg_sent, null, WN.BEGIN_DATE,
				   Worklist.lbeg_sent))
          and (WN.BEGIN_DATE  <= decode(Worklist.lend_sent, null, WN.BEGIN_DATE,
				  Worklist.lend_sent))))
    and (PRIORITY between p0 and p1 or PRIORITY between p2 and p3)
    order by decode(upper(Worklist.lorderkey),
             'MESSAGE_TYPE', WIT.DISPLAY_NAME,
             'SUBJECT', WN.SUBJECT,
             'BEGIN_DATE', to_char(WN.BEGIN_DATE, 'J.SSSSS'),
             'DUE_DATE', to_char(WN.DUE_DATE, 'J.SSSSS'),
             'END_DATE', to_char(WN.END_DATE, 'J.SSSSS'),
             'STATUS', WL.MEANING,
	     'RPRIORITY', to_char(100 - WN.PRIORITY, '00000000'),
             to_char(WN.PRIORITY, '00000000'));

Line: 556

    select MA.NAME
    from WF_NOTIFICATION_ATTRIBUTES NA,
         WF_MESSAGE_ATTRIBUTES_VL MA,
         WF_NOTIFICATIONS N
    where N.NOTIFICATION_ID = mnid
    and NA.NOTIFICATION_ID = N.NOTIFICATION_ID
    and MA.MESSAGE_NAME = N.MESSAGE_NAME
    and MA.MESSAGE_TYPE = N.MESSAGE_TYPE
    and MA.NAME = NA.NAME
    and MA.SUBTYPE = 'RESPOND'
    and MA.TYPE <> 'FORM'
    and MA.NAME = 'RESULT';

Line: 571

SELECT
 ROWID    ROW_ID,
 PLUG_ID,
 USERNAME,
 COLUMN_NUMBER,
 COLUMN_NAME,
 COLUMN_SIZE
FROM WF_WORKLIST_COL_DEFINITIONS
WHERE (PLUG_ID = c_plug_id and c_plug_id IS NOT NULL)
OR    (USERNAME = c_username and c_username IS NOT NULL and c_plug_id IS NULL)
ORDER BY COLUMN_NUMBER;

Line: 702

      select WR.ORIG_SYSTEM, WR.ORIG_SYSTEM_ID
      into uorig_system, uorig_system_id
      from WF_ROLES WR
      where WR.NAME = username
      and   WR.ORIG_SYSTEM not in ('HZ_PARTY','POS','ENG_LIST','AMV_CHN',
              'HZ_GROUP','CUST_CONT');

Line: 709

      select WR.ORIG_SYSTEM, WR.ORIG_SYSTEM_ID
      into uorig_system, uorig_system_id
      from WF_ROLES WR
      where WR.ORIG_SYSTEM = substr(username, 1, colon-1)
      and WR.ORIG_SYSTEM_ID = substr(username,  colon+1)
      and WR.NAME = username;

Line: 728

  ** Set the initial ranges for the select to be high and low priority items
  ** If you medium is also selected then add the medium range to the top of
  ** of the high priority range.
  ** Then check for the other flags.  If they are not turned on then remove
  ** them from the priority ranges
  */

  high_bottom_value := 0;

Line: 1036

    SELECT   WL.MEANING, WL.LOOKUP_CODE
    FROM     WF_LOOKUPS WL
    WHERE    WL.LOOKUP_TYPE = lktype
    AND      WL.LOOKUP_CODE IN ('SUBJECT', 'DUE_DATE', 'BEGIN_DATE', 'MESSAGE_TYPE')
    ORDER BY WL.MEANING;

Line: 1043

    SELECT   WL.MEANING, WL.LOOKUP_CODE
    FROM     WF_LOOKUPS WL
    WHERE    WL.LOOKUP_TYPE = lktype
    AND      WL.LOOKUP_CODE IN ('SUBJECT', 'DUE_DATE', 'BEGIN_DATE', 'MESSAGE_TYPE', 'PRIORITY')
    ORDER BY WL.MEANING;

Line: 1051

SELECT
 ROWID    ROW_ID,
 PLUG_ID,
 USERNAME,
 COLUMN_NUMBER,
 COLUMN_NAME,
 COLUMN_SIZE
FROM WF_WORKLIST_COL_DEFINITIONS
WHERE (PLUG_ID = c_plug_id and c_plug_id IS NOT NULL)
OR    (USERNAME = c_username and c_username IS NOT NULL and c_plug_id IS NULL)
ORDER BY COLUMN_NUMBER;

Line: 1084

    SELECT  ROWID ROW_ID,
            PLUG_ID,
	    USERNAME,
   	    DEFINITION_NAME,
	    WHERE_STATUS,
	    WHERE_FROM,
	    WHERE_ITEM_TYPE,
	    WHERE_NOTIF_TYPE,
	    WHERE_SUBJECT,
	    WHERE_SENT_START,
	    WHERE_SENT_END,
	    WHERE_DUE_START,
	    WHERE_DUE_END,
	    WHERE_PRIORITY,
	    WHERE_NOTIF_DEL_BY_ME,
	    ORDER_PRIMARY,
	    ORDER_ASC_DESC
    INTO    l_worklist_definition
    FROM    WF_WORKLIST_DEFINITIONS
    WHERE   (PLUG_ID = p_plug_id AND p_plug_id IS NOT NULL)
    OR      (USERNAME = p_username AND p_username IS NOT NULL and p_plug_id IS NULL);

Line: 1237

     htp.formSelectOpen(cname=>'COLUMN_NAME');

Line: 1246

            htp.formSelectOption(cvalue=>orderby.meaning,
                                 cselected => 'SELECTED',
                                 cattributes=>'value='||orderby.lookup_code);

Line: 1250

             htp.formSelectOption(cvalue=>orderby.meaning,
                                  cattributes=>'value='||orderby.lookup_code);

Line: 1258

        htp.formSelectOption(cvalue=>wf_core.translate('BLANK'),
                              cselected => 'SELECTED',
                                  cattributes=>'value=NULL');

Line: 1264

        htp.formSelectOption(cvalue=>wf_core.translate('BLANK'),
                                  cattributes=>'value=NULL');

Line: 1269

     htp.formSelectClose;

Line: 1296

  htp.formSelectOpen('orderkey');

Line: 1299

      htp.formSelectOption(cvalue=>orderby.meaning,
                           cselected => 'SELECTED',
                           cattributes=>'value='||orderby.lookup_code);

Line: 1303

      htp.formSelectOption(cvalue=>orderby.meaning,
                           cattributes=>'value='||orderby.lookup_code);

Line: 1308

  htp.formSelectClose;

Line: 1458

     INSERT INTO WF_WORKLIST_DEFINITIONS
     (	PLUG_ID			,
	USERNAME		,
	DEFINITION_NAME		,
        WHERE_STATUS		,
	WHERE_FROM		,
        WHERE_ITEM_TYPE 	,
        WHERE_SUBJECT		,
        WHERE_SENT_START	,
        WHERE_SENT_END		,
        WHERE_DUE_START		,
        WHERE_DUE_END		,
        WHERE_PRIORITY		,
        WHERE_NOTIF_DEL_BY_ME	,
        ORDER_PRIMARY
     )
    VALUES
    (
        TO_NUMBER(l_plug_id),
        l_username,
        l_definition_name,
        l_status,
        l_fromuser,
        l_ittype,
        l_msubject,
        l_beg_sent,
        l_end_sent,
        l_beg_due,
        l_end_due,
        l_priority,
        l_delegated_by_me,
        l_orderkey
    );

Line: 1494

     UPDATE WF_WORKLIST_DEFINITIONS
     SET DEFINITION_NAME	= l_definition_name,
        WHERE_STATUS		= l_status,
	WHERE_FROM		= l_fromuser,
        WHERE_ITEM_TYPE 	= l_ittype,
        WHERE_SUBJECT		= l_msubject,
        WHERE_SENT_START	= l_beg_sent,
        WHERE_SENT_END		= l_end_sent,
        WHERE_DUE_START		= l_beg_due,
        WHERE_DUE_END		= l_end_due,
        WHERE_PRIORITY		= l_priority,
        WHERE_NOTIF_DEL_BY_ME	= l_delegated_by_me,
        ORDER_PRIMARY           = l_orderkey
      WHERE   PLUG_ID = TO_NUMBER(l_plug_id);

Line: 1510

      DELETE FROM WF_WORKLIST_COL_DEFINITIONS
      WHERE   PLUG_ID = TO_NUMBER(l_plug_id);

Line: 1525

        INSERT INTO WF_WORKLIST_COL_DEFINITIONS
        (	PLUG_ID			,
   		USERNAME		,
		DEFINITION_NAME		,
        	COLUMN_NUMBER           ,
        	COLUMN_NAME             ,
	        COLUMN_SIZE
	)
	VALUES
        (
	        TO_NUMBER(l_plug_id),
        	l_username,
	        l_definition_name,
        	l_record_num,
	        l_column_name,
                100
        );

Line: 1568

p_delete         IN      VARCHAR2 DEFAULT 'N') IS

BEGIN

   IF (p_delete = 'Y') THEN

      -- Delete all the old column definitions
      DELETE FROM WF_WORKLIST_COL_DEFINITIONS
      WHERE  PLUG_ID = TO_NUMBER(p_plug_id) ;

Line: 1579

      DELETE FROM WF_WORKLIST_DEFINITIONS
      WHERE  PLUG_ID = TO_NUMBER(p_plug_id) ;

Line: 1595

        p_session_id,p_plug_id,p_delete);