[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
user_keys.delete(user_keys.last);
owner_roles.delete(owner_roles.last);
itemkeys.delete(itemkeys.last);
select sys_context('USERENV','CURRENT_SCHEMA')
into wf_engine_bulk.schema
from sys.dual;
insert into WF_ITEMS (
ITEM_TYPE,
ITEM_KEY,
ROOT_ACTIVITY,
ROOT_ACTIVITY_VERSION,
OWNER_ROLE,
PARENT_ITEM_TYPE,
PARENT_ITEM_KEY,
PARENT_CONTEXT,
BEGIN_DATE,
END_DATE,
USER_KEY
) values (
itemtype,
itemkeys(arrInd),
wflow,
rootversion,
owner_roles(arrInd),
parent_itemtype,
parent_itemkey,
parent_context,
actdate,
to_date(NULL),
user_keys(arrInd)
);
insert into WF_ITEMS (
ITEM_TYPE,
ITEM_KEY,
ROOT_ACTIVITY,
ROOT_ACTIVITY_VERSION,
PARENT_ITEM_TYPE,
PARENT_ITEM_KEY,
PARENT_CONTEXT,
BEGIN_DATE,
END_DATE,
USER_KEY
) values (
itemtype,
itemkeys(arrInd),
wflow,
rootversion,
parent_itemtype,
parent_itemkey,
parent_context,
actdate,
to_date(NULL),
user_keys(arrInd)
);
insert into WF_ITEMS (
ITEM_TYPE,
ITEM_KEY,
ROOT_ACTIVITY,
ROOT_ACTIVITY_VERSION,
OWNER_ROLE,
PARENT_ITEM_TYPE,
PARENT_ITEM_KEY,
PARENT_CONTEXT,
BEGIN_DATE,
END_DATE
) values (
itemtype,
itemkeys(arrInd),
wflow,
rootversion,
owner_roles(arrInd),
parent_itemtype,
parent_itemkey,
parent_context,
actdate,
to_date(NULL)
);
insert into WF_ITEMS (
ITEM_TYPE,
ITEM_KEY,
ROOT_ACTIVITY,
ROOT_ACTIVITY_VERSION,
PARENT_ITEM_TYPE,
PARENT_ITEM_KEY,
PARENT_CONTEXT,
BEGIN_DATE,
END_DATE
) values (
itemtype,
itemkeys(arrInd),
wflow,
rootversion,
parent_itemtype,
parent_itemkey,
parent_context,
actdate,
to_date(NULL)
);
itemkeys.DELETE(SQL%BULK_EXCEPTIONS(ExceptionInd).ERROR_INDEX);
user_keys.DELETE(SQL%BULK_EXCEPTIONS(ExceptionInd).ERROR_INDEX);
owner_roles.DELETE(SQL%BULK_EXCEPTIONS(ExceptionInd).ERROR_INDEX);
insert into WF_ITEM_ATTRIBUTE_VALUES (
ITEM_TYPE,
ITEM_KEY,
NAME,
TEXT_VALUE,
NUMBER_VALUE,
DATE_VALUE
) select
itemtype,
itemkeys(arrInd),
WIA.NAME,
WIA.TEXT_DEFAULT,
WIA.NUMBER_DEFAULT,
WIA.DATE_DEFAULT
from WF_ITEM_ATTRIBUTES WIA
where WIA.ITEM_TYPE = itemtype;
itemkeys.DELETE(SQL%BULK_EXCEPTIONS(ExceptionInd).ERROR_INDEX);
user_keys.DELETE(SQL%BULK_EXCEPTIONS(ExceptionInd).ERROR_INDEX);
owner_roles.DELETE(SQL%BULK_EXCEPTIONS(ExceptionInd).ERROR_INDEX);
insert into WF_ITEM_ATTRIBUTE_VALUES (
ITEM_TYPE,
ITEM_KEY,
NAME,
TEXT_VALUE
) values (
itemtype,
l_itemkeys(arrInd),
aname,
BulkAddItemAttr.text_values(arrInd)
);
l_itemkeys.DELETE(SQL%BULK_EXCEPTIONS(failIndex).ERROR_INDEX);
succAttrUpdates Wf_Engine.NameTabTyp;
succItemUpdates Wf_Engine_Bulk.ItemKeyTabType;
insert into WF_ITEM_ATTRIBUTE_VALUES
(ITEM_TYPE, ITEM_KEY, NAME, TEXT_VALUE)
select itemtype, itemkeys(arrayIndex), anames(arrayIndex), text_default
from WF_ITEM_ATTRIBUTES WIA
where
WIA.ITEM_TYPE = itemtype
and
WIA.NAME = anames(arrayIndex)
and not exists (select 1 from WF_ITEM_ATTRIBUTE_VALUES WIAV
where WIAV.item_type=itemtype
and WIAV.item_key=itemkeys(arrayIndex)
and WIAV.NAME=anames(arrayIndex));
g_FailedItems.DELETE;
g_FailedAttributes.DELETE;
update WF_ITEM_ATTRIBUTE_VALUES set
TEXT_VALUE = avalues(arrInd)
where ITEM_TYPE = itemtype
and ITEM_KEY = itemkeys(arrInd)
and NAME = anames(arrInd)
returning item_key,name bulk collect into succItemUpdates, succAttrUpdates;
if (succItemUpdates.count <> itemkeys.COUNT) then
-- determine the failed item/atrributes
if succItemUpdates.count=0 then
g_failedItems:=itemkeys;
if ((itemkeys.count - i + 1) = succItemUpdates.count) then
-- we are done, the rest are all successful
exit;
j:=succItemUpdates.first;
while j <= succItemUpdates.last loop
if anames(i) = succAttrUpdates(j)
and itemkeys(i) = succItemUpdates(j) then
-- remove the item/attr from the success table
succItemUpdates.delete(j);
succAttrUpdates.delete(j);
j:=succItemUpdates.next(j);
end if; --successUpdates count=0
end if; --sucessUpdates doesnot match item count
succAttrUpdates Wf_Engine.NameTabTyp;
succItemUpdates Wf_Engine_Bulk.ItemKeyTabType;
insert into WF_ITEM_ATTRIBUTE_VALUES
(ITEM_TYPE, ITEM_KEY, NAME, NUMBER_VALUE)
select itemtype, itemkeys(arrayIndex), anames(arrayIndex), number_default
from WF_ITEM_ATTRIBUTES WIA
where
WIA.ITEM_TYPE = itemtype
and
WIA.NAME = anames(arrayIndex)
and not exists (select 1 from WF_ITEM_ATTRIBUTE_VALUES WIAV
where WIAV.item_type=itemtype
and WIAV.item_key=itemkeys(arrayIndex)
and WIAV.NAME=anames(arrayIndex));
null; -- ignore failures here and let update to handle the logic
g_FailedItems.DELETE;
g_failedAttributes.DELETE;
update WF_ITEM_ATTRIBUTE_VALUES set
NUMBER_VALUE = avalues(arrInd)
where ITEM_TYPE = itemtype
and ITEM_KEY = itemkeys(arrInd)
and NAME = anames(arrInd)
returning item_key,name bulk collect into succItemUpdates, succAttrUpdates;
if (succItemUpdates.count <> itemkeys.COUNT) then
-- determine the failed item/atrributes
if succItemUpdates.count=0 then
g_failedItems:=itemkeys;
if ((itemkeys.count - i + 1) = succItemUpdates.count) then
-- we are done, the rest are all successful
exit;
j:=succItemUpdates.first;
while j <= succItemUpdates.last loop
if anames(i) = succAttrUpdates(j)
and itemkeys(i) = succItemUpdates(j) then
-- remove the item/attr from the success table
succItemUpdates.delete(j);
succAttrUpdates.delete(j);
j:=succItemUpdates.next(j);
end if; --successUpdates count=0
end if; --sucessUpdates doesnot match item count
succAttrUpdates Wf_Engine.NameTabTyp;
succItemUpdates Wf_Engine_Bulk.ItemKeyTabType;
g_FailedItems.DELETE;
g_FailedAttributes.DELETE;
insert into WF_ITEM_ATTRIBUTE_VALUES
(ITEM_TYPE, ITEM_KEY, NAME, DATE_VALUE)
select itemtype, itemkeys(arrayIndex), anames(arrayIndex), Date_default
from WF_ITEM_ATTRIBUTES WIA
where
WIA.ITEM_TYPE = itemtype
and
WIA.NAME = anames(arrayIndex)
and not exists (select 1 from WF_ITEM_ATTRIBUTE_VALUES WIAV
where WIAV.item_type=itemtype
and WIAV.item_key=itemkeys(arrayIndex)
and WIAV.NAME=anames(arrayIndex));
update WF_ITEM_ATTRIBUTE_VALUES set
DATE_VALUE = avalues(arrInd)
where ITEM_TYPE = itemtype
and ITEM_KEY = itemkeys(arrInd)
and NAME = anames(arrInd)
returning item_key,name bulk collect into succItemUpdates, succAttrUpdates;
if (succItemUpdates.count <> itemkeys.COUNT) then
-- determine the failed item/atrributes
if succItemUpdates.count=0 then
g_failedItems:=itemkeys;
if ((itemkeys.count - i + 1) = succItemUpdates.count) then
-- we are done, the rest are all successful
exit;
j:=succItemUpdates.first;
while j <= succItemUpdates.last loop
if anames(i) = succAttrUpdates(j)
and itemkeys(i) = succItemUpdates(j) then
-- remove the item/attr from the success table
succItemUpdates.delete(j);
succAttrUpdates.delete(j);
j:=succItemUpdates.next(j);
end if; --successUpdates count=0
end if; --sucessUpdates doesnot match item count
g_FailedItems.DELETE;
g_SuccessItems.DELETE;
Wf_Core.Raise('WFENG_ITEM_ROOT_SELECTOR');
select INSTANCE_ID
into rootid
from WF_PROCESS_ACTIVITIES PA, WF_ACTIVITIES A
where A.ITEM_TYPE = itemtype
and A.NAME = 'ROOT'
and actdate >= A.BEGIN_DATE
and actdate < NVL(A.END_DATE, actdate+1)
and PA.PROCESS_NAME = 'ROOT'
and PA.PROCESS_ITEM_TYPE = itemtype
and PA.PROCESS_VERSION = A.VERSION
and PA.INSTANCE_LABEL = root;
itemkeys.delete(arrInd);
user_keys.delete(arrInd);
owner_roles.delete(arrInd);
insert into WF_ITEM_ATTRIBUTE_VALUES (
ITEM_TYPE,
ITEM_KEY,
NAME,
TEXT_VALUE
) values (
itemtype,
l_itemkeys(arrInd),
wf_engine.eng_schema,
schemaAttribute
);
l_itemkeys.DELETE(SQL%BULK_EXCEPTIONS(failIndex).ERROR_INDEX);
insert into WF_ITEM_ATTRIBUTE_VALUES (
ITEM_TYPE,
ITEM_KEY,
NAME,
TEXT_VALUE
) values (
itemtype,
l_itemkeys(arrInd),
'#LBL_'||parent_context,
parent_context
);
l_itemkeys.DELETE(SQL%BULK_EXCEPTIONS(failIndex).ERROR_INDEX);
update WF_ITEM_ATTRIBUTE_VALUES
set NUMBER_VALUE = NUMBER_VALUE + l_count
where NAME like '#CNT_%'
and NUMBER_VALUE is not null
and ITEM_TYPE = parent_itemType
and ITEM_KEY = parent_itemKey;
SELECT PROCESS_ITEM_TYPE, PROCESS_NAME, PROCESS_VERSION,
ACTIVITY_ITEM_TYPE, ACTIVITY_NAME, INSTANCE_ID,
INSTANCE_LABEL, PERFORM_ROLE, PERFORM_ROLE_TYPE,
START_END, DEFAULT_RESULT
FROM WF_PROCESS_ACTIVITIES WPA
WHERE WPA.PROCESS_ITEM_TYPE = itemtype
AND WPA.PROCESS_NAME = process
AND WPA.PROCESS_VERSION = version
AND WPA.START_END = wf_engine.eng_start
AND NOT EXISTS (
SELECT NULL
FROM WF_ACTIVITY_TRANSITIONS WAT
WHERE WAT.TO_PROCESS_ACTIVITY = WPA.INSTANCE_ID);
g_failedItems.DELETE;
g_successItems.DELETE;
select WI.ROOT_ACTIVITY, WI.ROOT_ACTIVITY_VERSION
into process,version
from WF_ITEMS WI
where WI.ITEM_TYPE = itemtype
and WI.ITEM_KEY = l_itemkeys(1);
insert
into WF_ITEM_ACTIVITY_STATUSES (
ITEM_TYPE,
ITEM_KEY,
PROCESS_ACTIVITY,
ACTIVITY_STATUS,
ACTIVITY_RESULT_CODE,
ASSIGNED_USER,
NOTIFICATION_ID,
BEGIN_DATE,
END_DATE,
DUE_DATE,
EXECUTION_TIME,
OUTBOUND_QUEUE_ID
) values (
itemtype,
l_itemkeys(arrInd),
processid,
wf_engine.eng_active,
wf_engine.eng_null,
null,
null,
SYSDATE,
null,
null,
g_execCount,
null
);
l_itemkeys.DELETE(sql%bulk_exceptions(arrIndex).ERROR_INDEX);
WF_CACHE.ProcessStartActivities.DELETE;
dueDateTab.delete;
msgIdTab.delete;
l_itemkeys.DELETE(arrInd);
l_itemkeys.delete(l_itemkeys.last);
msgIdTab.delete(msgIdTab.last);
duedateTab.delete(duedateTab.last);
insert
into WF_ITEM_ACTIVITY_STATUSES (
ITEM_TYPE,
ITEM_KEY,
PROCESS_ACTIVITY,
ACTIVITY_STATUS,
ACTIVITY_RESULT_CODE,
ASSIGNED_USER,
NOTIFICATION_ID,
BEGIN_DATE,
END_DATE,
DUE_DATE,
EXECUTION_TIME,
OUTBOUND_QUEUE_ID
) values (
itemtype,
l_itemkeys(arrInd),
childarr(i),
'DEFERRED',
null,
null,
null,
SYSDATE,
null,
duedateTab(arrInd),
g_execCount,
msgIdTab(arrInd)
);
l_itemkeys.DELETE(sql%bulk_exceptions(failindex).error_index);
g_SuccessItems.DELETE;
select WI.ROOT_ACTIVITY_VERSION
into version
from WF_ITEMS WI
where WI.ITEM_TYPE = itemtype
and WI.ITEM_KEY = l_itemkeys(1)
and WI.ROOT_ACTIVITY=process;
SELECT WPA.INSTANCE_ID
into actid
FROM WF_PROCESS_ACTIVITIES WPA
WHERE WPA.PROCESS_ITEM_TYPE = itemtype
AND WPA.PROCESS_NAME = process
AND WPA.PROCESS_VERSION = version
AND WPA.START_END = wf_engine.eng_start
AND WPA.INSTANCE_LABEL=activity;
insert
into WF_ITEM_ACTIVITY_STATUSES (
ITEM_TYPE,
ITEM_KEY,
PROCESS_ACTIVITY,
ACTIVITY_STATUS,
ACTIVITY_RESULT_CODE,
ASSIGNED_USER,
NOTIFICATION_ID,
BEGIN_DATE,
END_DATE,
DUE_DATE,
EXECUTION_TIME,
OUTBOUND_QUEUE_ID
) values (
itemtype,
l_itemkeys(arrInd),
processid,
wf_engine.eng_active,
wf_engine.eng_null,
null,
null,
SYSDATE,
null,
null,
g_execCount,
null
);
l_itemkeys.DELETE(sql%bulk_exceptions(arrIndex).ERROR_INDEX);
dueDateTab.delete;
msgIdTab.delete;
l_itemkeys.DELETE(arrInd);
l_itemkeys.delete(l_itemkeys.last);
msgIdTab.delete(msgIdTab.last);
duedateTab.delete(duedateTab.last);
insert
into WF_ITEM_ACTIVITY_STATUSES (
ITEM_TYPE,
ITEM_KEY,
PROCESS_ACTIVITY,
ACTIVITY_STATUS,
ACTIVITY_RESULT_CODE,
ASSIGNED_USER,
NOTIFICATION_ID,
BEGIN_DATE,
END_DATE,
DUE_DATE,
EXECUTION_TIME,
OUTBOUND_QUEUE_ID
) values (
itemtype,
l_itemkeys(arrInd),
actid,
nvl(activityStatus,'DEFERRED'),
null,
null,
null,
SYSDATE,
null,
duedateTab(arrInd),
g_execCount,
msgIdTab(arrInd)
);
l_itemkeys.DELETE(sql%bulk_exceptions(failindex).error_index);