[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Insert_Event_Transitions(
p_Event_Id IN NUMBER ,
p_Version IN NUMBER ,
p_New_Status IN VARCHAR2 ,
p_Old_Status IN VARCHAR2 := NULL ,
p_Module_Name IN VARCHAR2 ,
p_Worker_Id IN NUMBER := NULL )
AS
BEGIN
INSERT INTO ad_event_transitions(
transition_id,
event_id,
version,
new_status,
prior_status,
transition_time,
module_name,
worker_id)
VALUES (
ad_event_transitions_s.NEXTVAL,
p_Event_Id,
p_Version,
p_New_Status,
p_Old_Status,
SYSDATE ,
p_Module_Name,
p_Worker_Id);
END Insert_Event_Transitions;
SELECT last_version,
event_id,
event_type
INTO l_Version,
l_Event_Id,
l_Event_Type
FROM ad_events
WHERE owner = p_Owner
AND event_name = p_Event_Name
AND event_context = l_p_Context;
-- It has to be inserted into AD_EVENT_VERSIONS.
--
INSERT INTO ad_event_versions(
event_id,
version,
status,
creation_date,
last_update_date,
num_workers,
worker_status)
VALUES( l_Event_Id,
l_p_Version,
INITIALIZED_STATUS,
SYSDATE ,
SYSDATE,
p_Num_Workers,
l_Worker_Status_String)
RETURNING status
INTO l_Event_Status ;
-- update date are updated in AD_EVENTS
--
UPDATE ad_events
SET last_version = l_p_Version,
last_update_date = SYSDATE
WHERE event_id = l_Event_Id;
SELECT status,
worker_status,
num_workers
INTO l_Event_Status,
l_Worker_Status_String,
l_Prev_Max_Workers
FROM ad_Event_Versions
WHERE event_id = l_Event_Id
AND version = l_p_Version;
INSERT INTO ad_events(
event_id,
owner,
event_name,
event_context,
event_type,
last_version,
creation_date,
last_update_date)
VALUES(
ad_events_s.NEXTVAL,
p_Owner,
p_Event_Name,
l_p_Context,
l_p_Event_Type,
l_p_Version,
SYSDATE,
SYSDATE )
RETURNING event_id
INTO l_Event_Id;
INSERT INTO ad_event_versions(
event_id,
version,
status,
creation_date,
last_update_date,
num_workers,
worker_status)
VALUES(
l_Event_Id,
l_p_Version,
INITIALIZED_STATUS,
SYSDATE,
SYSDATE,
p_Num_Workers,
l_Worker_Status_String);
UPDATE ad_event_versions
SET num_workers = p_Num_Workers,
worker_status = g_Events_Tbl(l_Event_Index).Worker_Status ,
last_update_date = SYSDATE
WHERE event_id = l_Event_Id
AND version = l_p_Version ;
Insert_Event_Transitions(
p_Event_Id => l_Event_Id,
p_Version => l_p_Version,
p_New_Status => 'RE-SCHEDULED',
p_Old_Status => l_Event_Status,
p_Module_Name => p_Module_Name,
p_Worker_Id => p_Worker_Id);
UPDATE ad_event_versions
SET start_time = SYSDATE ,
last_update_date = SYSDATE
WHERE event_id = l_Event_Id
AND version = l_Version;
Insert_Event_Transitions(
p_Event_Id => l_Event_Id,
p_Version => l_Version,
p_New_Status => INITIALIZED_STATUS,
p_Module_Name => l_Module_Name,
p_Worker_Id => l_Worker_Id);
SELECT status,
event_type,
ae.event_id,
av.version,
worker_status
INTO l_Event_Status,
l_Event_type,
l_Event_Id,
l_Version,
l_Worker_Status_String
FROM ad_events ae, ad_event_versions av
WHERE ae.event_id = av.event_id
AND ae.last_version = av.version
AND ae.owner = p_Owner
AND ae.event_name = p_Event_Name
AND ae.event_context = l_p_Context;
UPDATE ad_event_versions
SET status = l_New_Status,
end_time = l_End_Time,
last_update_date = SYSDATE ,
worker_status = l_Worker_Status_String
WHERE event_id = l_Event_Id
AND version = l_Version;
Insert_Event_Transitions(
p_Event_Id => l_Event_Id,
p_Version => l_Version,
p_New_Status => l_New_Status,
p_Old_Status => l_Event_Status,
p_Module_Name => l_Module_Name,
p_Worker_Id => l_Worker_Id);
UPDATE ad_event_versions
SET status = l_New_Status,
end_time = SYSDATE ,
last_update_date = SYSDATE
WHERE event_id = l_Event_Id
AND version = l_Version;
Insert_Event_Transitions(
p_Event_Id => l_Event_Id,
p_Version => l_Version,
p_New_Status => l_New_Status,
p_Old_Status => l_Event_Status,
p_Module_Name => l_Module_Name);
SELECT 1
INTO l_Temp_Var
FROM ad_events ae,
ad_Event_Versions av
WHERE ae.owner = p_Owner
AND ae.event_name = p_Event_Name
AND ae.event_context = l_p_Context
AND ae.event_id = av.event_id
AND av.version >= p_Min_Completed_Version
AND av.status = COMPLETED_STATUS
AND ROWNUM < 2;
SELECT av.status,
av.worker_status,
av.version,
av.num_workers
INTO l_Event_Status,
l_Worker_Status_String,
l_Event_Version,
l_Num_Workers
FROM ad_events ae,
ad_event_versions av
WHERE ae.owner = p_Owner
AND ae.event_name = p_Event_Name
AND ae.event_context = l_p_Context
AND av.event_id = ae.event_id
AND av.version = NVL(p_Specific_Version,ae.last_version);
SELECT av.status,
av.version
INTO l_Status,
l_Event_Version
FROM
ad_events ae,
ad_event_versions av
WHERE ae.owner = p_Owner
AND ae.event_name = p_Event_Name
AND ae.event_context = l_p_Context
AND av.event_id = ae.event_id
AND av.version = NVL(p_Specific_Version, ae.last_version);
UPDATE ad_event_versions
SET status = p_Status,
last_update_date = SYSDATE
WHERE event_id = l_Event_Id
AND version = l_Version;
Insert_Event_Transitions(
p_Event_Id => l_Event_Id,
p_Version => l_Version,
p_New_Status => p_Status,
p_Old_Status => l_Event_Status,
p_Module_Name => l_Module_Name,
p_Worker_Id => l_Worker_Id);
SELECT ae.event_id,
ae.last_version,
av.status,
av.num_workers
INTO l_Event_Id,
l_Event_Version,
l_Event_Status,
l_Max_Workers
FROM ad_events ae, ad_event_versions av
WHERE ae.owner = p_Owner
AND ae.event_name = p_Event_Name
AND ae.event_context = l_p_Context
AND av.event_id = ae.event_id
AND av.version = ae.last_version;
UPDATE ad_event_versions
SET status = INITIALIZED_STATUS,
start_time = NULL ,
end_time = NULL ,
last_update_date = SYSDATE ,
worker_status = LPAD('N',l_Max_Workers,'N')
WHERE event_id = l_Event_Id
AND version = l_Event_Version;
Insert_Event_Transitions(
p_Event_Id => l_Event_Id,
p_Version => l_Event_Version,
p_New_Status => INITIALIZED_STATUS,
p_Old_Status => l_Event_Status,
p_Module_Name => l_Module_Name,
p_Worker_Id => l_Worker_Id);
SELECT status,
ae.event_id,
worker_status,
num_workers,
av.version
INTO l_Event_Status,
l_Event_Id,
l_Worker_Status_String,
l_Prev_Max_Workers,
l_Event_Version
FROM ad_events ae, ad_event_versions av
WHERE ae.event_id = av.event_id
AND ae.last_version = av.version
AND ae.owner = p_Owner
AND ae.event_name = p_Event_Name
AND ae.event_context = l_p_Context;
-- Inserting a record into the transition record to show that
-- a rescheduling has happened for the event.
Insert_Event_Transitions(
p_Event_Id => l_Event_Id,
p_Version => l_p_Version,
p_New_Status => 'RE-SCHEDULED',
p_Old_Status => 'RE-SCHEDULED',
p_Module_Name => p_Module_Name,
p_Worker_Id => p_Worker_Id);
-- Since the event has completed here, we have to insert an
-- event transition entry as below.
--
Insert_Event_Transitions(
p_Event_Id => l_Event_Id,
p_Version => l_p_Version,
p_New_Status => l_New_Status,
p_Old_Status => 'RE-SCHEDULED',
p_Module_Name => p_Module_Name,
p_Worker_Id => p_Worker_Id);
-- Now update ad_Event_Versions to make the changes happened as a
-- result of the re-scheduling.
--
UPDATE ad_event_versions
SET num_workers = p_Num_Workers,
status = l_New_Status,
worker_status = l_Worker_Status_String,
start_time = DECODE(start_time,null,SYSDATE,
start_time),
last_update_date = SYSDATE ,
end_time = l_End_Time
WHERE event_id = l_Event_Id
AND version = l_p_Version;
UPDATE ad_event_versions
SET status = l_New_Status,
end_time = l_End_Time,
last_update_date = SYSDATE ,
start_time = DECODE(start_time,null,SYSDATE,
start_time),
worker_status = l_Worker_Status_String
WHERE event_id = l_Event_Id
AND version = l_p_Version;
Insert_Event_Transitions(
p_Event_Id => l_Event_Id,
p_Version => l_p_Version,
p_New_Status => COMPLETED_STATUS,
p_Old_Status => COMPLETED_STATUS,
p_Module_Name => p_Module_Name,
p_Worker_Id => p_Worker_Id);
UPDATE ad_event_versions
SET status = COMPLETED_STATUS,
start_time = DECODE(start_time,null,SYSDATE,
start_time),
end_time = SYSDATE ,
last_update_date = SYSDATE
WHERE event_id = l_Event_Id
AND version = l_p_Version;
-- Insert the transition entries here
--
Insert_Event_Transitions(
p_Event_Id => l_Event_Id,
p_Version => l_p_Version,
p_New_Status => COMPLETED_STATUS,
p_Old_Status => l_Event_Status,
p_Module_Name => p_Module_Name);
Insert_Event_Transitions(
p_Event_Id => l_Event_Id,
p_Version => l_p_Version,
p_New_Status => l_New_Status,
p_Old_Status => l_Event_Status,
p_Module_Name => p_Module_Name,
p_Worker_Id => p_Worker_Id);
UPDATE ad_events
SET last_version = l_p_Version,
last_update_date = SYSDATE
WHERE event_id = l_Event_Id;
INSERT INTO ad_event_versions (
event_id,
version,
status,
start_time,
end_time,
creation_date,
last_update_date,
num_workers,
worker_status)
VALUES (
l_Event_Id,
l_p_Version,
l_New_Status,
SYSDATE ,
l_End_Time ,
SYSDATE ,
SYSDATE ,
p_Num_Workers ,
l_Worker_Status_String );
INSERT INTO ad_events(
event_id,
owner,
event_name,
event_context,
event_type,
last_version,
creation_date,
last_update_date)
VALUES(
ad_events_s.NEXTVAL,
p_Owner,
p_Event_Name,
l_p_Context,
l_p_Event_Type,
l_p_Version,
SYSDATE,
SYSDATE )
RETURNING event_id
INTO l_Event_Id;
-- Insert the transition records for single-worker events.
--
Insert_Event_Transitions(
p_Event_Id => l_Event_Id,
p_Version => l_p_Version,
p_New_Status => COMPLETED_STATUS,
p_Old_Status => COMPLETED_STATUS,
p_Module_Name => p_Module_Name,
p_Worker_Id => p_Worker_Id);
INSERT INTO ad_event_versions(
event_id,
version,
status,
start_time,
end_time,
creation_date,
last_update_date,
num_workers,
worker_status)
VALUES(
l_Event_Id,
l_p_Version,
l_Event_Status,
SYSDATE,
l_End_Time,
SYSDATE,
SYSDATE,
p_Num_Workers,
l_Worker_Status_String);