The following lines contain the word 'select', 'insert', 'update' or 'delete':
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;
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;
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);
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);
select WL.MEANING, WL.LOOKUP_CODE
from WF_LOOKUPS WL
where WL.LOOKUP_TYPE = lktype
order by WL.MEANING;
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;
htp.formSelectOpen('ittype');
htp.formSelectOption(cvalue=>ittype_list.display_name,
cselected => 'SELECTED',
cattributes=>'value='||ittype_list.message_type);
htp.formSelectOption(cvalue=>ittype_list.display_name,
cattributes=>'value='||ittype_list.message_type);
htp.formSelectOption(cvalue=>wf_core.translate('ALL'),
cselected => 'SELECTED',
cattributes=>'value=*');
htp.formSelectOption(cvalue=>wf_core.translate('ALL'),
cattributes=>'value=*');
htp.formSelectClose;
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'));
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';
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;
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');
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;
** 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;
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;
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;
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;
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);
htp.formSelectOpen(cname=>'COLUMN_NAME');
htp.formSelectOption(cvalue=>orderby.meaning,
cselected => 'SELECTED',
cattributes=>'value='||orderby.lookup_code);
htp.formSelectOption(cvalue=>orderby.meaning,
cattributes=>'value='||orderby.lookup_code);
htp.formSelectOption(cvalue=>wf_core.translate('BLANK'),
cselected => 'SELECTED',
cattributes=>'value=NULL');
htp.formSelectOption(cvalue=>wf_core.translate('BLANK'),
cattributes=>'value=NULL');
htp.formSelectClose;
htp.formSelectOpen('orderkey');
htp.formSelectOption(cvalue=>orderby.meaning,
cselected => 'SELECTED',
cattributes=>'value='||orderby.lookup_code);
htp.formSelectOption(cvalue=>orderby.meaning,
cattributes=>'value='||orderby.lookup_code);
htp.formSelectClose;
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
);
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);
DELETE FROM WF_WORKLIST_COL_DEFINITIONS
WHERE PLUG_ID = TO_NUMBER(l_plug_id);
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
);
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) ;
DELETE FROM WF_WORKLIST_DEFINITIONS
WHERE PLUG_ID = TO_NUMBER(p_plug_id) ;
p_session_id,p_plug_id,p_delete);