[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT activity_name,activity_result
INTO v_pre_activity,
v_pre_result
FROM oe_upgrade_wf_act_map
WHERE activity_seq = p_sequence_id - 1
AND action_id = p_action_id;
SELECT activity_name
INTO r_post_activity_name
FROM oe_upgrade_wf_act_map
WHERE activity_seq = p_sequence_id + 1
AND action_id = p_action_id;
SELECT nvl(instance_id, -1)
INTO r_instance_id
FROM wf_process_activities
WHERE process_name = p_process_name
AND activity_name = p_activity_name
AND instance_label = p_instance_label
AND process_version = (SELECT max(process_version)
FROM wf_process_activities
WHERE process_name = p_process_name
AND activity_name = p_activity_name
AND instance_label = p_instance_label);
PROCEDURE Insert_Into_Wf_Table
(
p_from_instance_id IN NUMBER,
p_to_instance_id IN NUMBER,
p_result_code IN VARCHAR2,
p_level_error OUT NOCOPY NUMBER
)
IS
BEGIN
wf_core.session_level := 20;
/* Insert into table wf_activity_transitions */
BEGIN
SELECT 'x'
INTO v_dummy
FROM wf_activity_transitions
WHERE from_process_activity = p_from_instance_id
AND result_code = p_result_code
AND to_process_activity = p_to_instance_id;
END Insert_Into_Wf_Table;
SELECT 'x' INTO v_dummy
FROM wf_activities
WHERE item_type = p_item_type
AND version = 1
AND name = 'UPG_PN_'||p_item_type||'_'||p_line_type||'_'||to_char(p_cycle_id);
SELECT
'UPG_PN_'||p_item_type||'_'||p_line_type||'_'||to_char(cycle_id),
'UPG_PN_'||p_item_type||'_'||p_line_type||'_'||rtrim(name)
INTO v_process_name,
v_display_name
FROM
so_cycles
WHERE cycle_id = p_cycle_id
AND p_item_type in ('OEOH', 'OEOL');
/* check if already inserted */
begin
select 'x' into v_dummy
from wf_activities
where item_Type = P_ITEM_TYPE
AND NAME = v_process_name
and version = v_version;
/* Insert data into WF_ACTIVITIES */
wf_load.upload_activity (
x_item_type => p_item_type,
x_name => v_process_name,
x_display_name => v_display_name,
x_description => NULL,
x_type => 'PROCESS',
x_rerun => 'RESET',
x_protect_level => 20,
x_custom_level => 20,
x_effective_date => sysdate - 1,
x_function => null,
x_function_type => null,
x_result_type => '*',
x_cost => 0,
x_read_role => null,
x_write_role => null,
x_execute_role => null,
x_icon_name => 'PROCESS.ICO',
x_message => null,
x_error_process => 'RETRY_ONLY',
x_expand_role => 'N',
x_error_item_type => 'WFERROR',
x_runnable_flag => 'Y',
x_version => v_version,
x_level_error => v_api_error_code
);
/* Description: Inserts Lookup types using the WF API. One lookup type is
created per custom action.
*/
PROCEDURE Create_Lookup_Type
(
p_item_type IN VARCHAR2
)
IS
CURSOR c1 IS
SELECT
'UPG_RT_'||to_char(M.action_id) lookup_type,
ltrim(rtrim('UPG_RT_'||substr(M.name,1,26))) display_name,
decode(M.result_table,'SO_HEADERS','OEOH','SO_LINES','OEOL','ERROR') item_type,
20 protect_level,
20 custom_level,
M.description description
FROM
so_actions M,
oe_upgrade_wf_act_map U
WHERE decode(M.result_table,'SO_HEADERS','OEOH',
'SO_LINES','OEOL','ERROR') = p_item_type
AND M.action_id not in
( SELECT name from oe_upgrade_wf_obs_codes
WHERE type = 'ACTION' )
AND M.action_id = U.action_id(+)
AND U.action_id is null;
SELECT 'x' INTO v_dummy
FROM wf_lookup_types
WHERE lookup_type = c2.lookup_type
OR display_name = c2.display_name;
/* Insert data into WF_LOOKUP_TYPES_TL */
wf_load.upload_lookup_type (
x_lookup_type => c2.lookup_type,
x_display_name => c2.display_name,
x_description => c2.description,
x_protect_level => c2.protect_level,
x_custom_level => c2.custom_level,
x_item_type => c2.item_type,
x_level_error => v_api_error_code
);
SELECT
'UPG_RT_'||to_char(SA.action_id) lookup_type,
'UPG_RC_'||to_char(SR.result_id) lookup_code,
max(SR.name) meaning,
max(20) protect_level,
max(20) custom_level,
max(SR.Description) description
FROM
so_actions SA,
so_action_results SAR,
so_results SR,
oe_upgrade_wf_act_map U
WHERE decode(SA.result_table,'SO_HEADERS','OEOH',
'SO_LINES','OEOL','ERROR') = p_item_type
AND SA.action_id = SAR.action_id
AND SAR.result_id = SR.result_id
AND SR.result_id not in
( SELECT name from oe_upgrade_wf_obs_codes
WHERE type = 'RESULT' )
AND SA.action_id = U.action_id (+)
AND U.action_id is null
GROUP BY
'UPG_RT_'||to_char(SA.action_id),
'UPG_RC_'||to_char(SR.result_id);
SELECT 'x' INTO v_dummy
FROM wf_lookups
WHERE ( lookup_type = c2.lookup_type AND
lookup_code = c2.lookup_code)
OR ( lookup_type = c2.lookup_type AND
meaning = c2.meaning);
/* Insert data into WF_LOOKUPS_TL */
wf_load.upload_lookup
(
x_lookup_type => c2.lookup_type,
x_lookup_code => c2.lookup_code,
x_meaning => c2.meaning,
x_description => c2.description,
x_protect_level => c2.protect_level,
x_custom_level => c2.custom_level,
x_level_error => v_api_error_code
);
SELECT
decode(result_table,'SO_HEADERS','OEOH',
'SO_LINES','OEOL','ERROR') item_type,
'UPG_AN_'||to_Char(SA.action_id) activity_name,
SA.Name display_name,
1 version,
decode(sa.action_approval,'Y','NOTICE','FUNCTION') type,
'LOOP' rerun,
'N' expand_role,
sysdate - 1 BEGIN_date,
null end_date,
decode(sa.action_approval,'Y','',
'OE_WF_UPGRADE_UTIL.UPGRADE_CUSTOM_ACTIVITY_BLOCK') function,
'UPG_RT_'||to_Char(SA.action_id) result_Type,
decode(sa.action_approval,'Y', 60, null) cost,
null read_role,
null write_role,
null execute_role,
decode(sa.action_approval,'Y','NOTIFY.ICO','FUNCTION.ICO') icon_name,
decode(sa.action_approval,'Y',
'UPG_AN_'||to_char(sa.action_id), NULL) message,
'RETRY_ONLY' error_process,
'WFERROR' error_item_type,
'N' runnable_flag,
null function_type,
result_column result_column,
sa.action_id action_id
FROM
so_actions SA,
oe_upgrade_wf_act_map U
WHERE SA.action_id = U.action_id(+)
AND U.action_id is null
AND SA.action_id not in
( SELECT name from oe_upgrade_wf_obs_codes
WHERE type = 'ACTION' )
AND decode(result_table,'SO_HEADERS','OEOH',
'SO_LINES','OEOL','ERROR') = p_item_type
AND sa.action_id in (select action_id from so_cycle_actions);
select 'N' into v_fyi_flag from so_action_results
where action_id = c2.action_id
and rownum = 1;
SELECT 'x' INTO v_dummy
FROM wf_activities
WHERE item_type = p_item_type
AND version = 1
AND name = c2.activity_name;
/* Insert data into WF_ACITVITIES */
WF_LOAD.UPLOAD_ACTIVITY (
x_item_type => c2.item_type,
x_name => c2.activity_name,
x_display_name => c2.display_name,
x_description => c2.activity_name,
x_type => c2.type,
x_rerun => c2.rerun,
x_protect_level => 20,
x_custom_level => 20,
x_effective_date => c2.BEGIN_date,
x_function => c2.function,
x_function_type => c2.function_type,
x_result_type => v_result_type,
x_cost => c2.cost,
x_read_role => c2.read_role,
x_write_role => c2.write_role,
x_execute_role => c2.execute_role,
x_icon_name => c2.icon_name,
x_message => c2.message,
x_error_process => c2.error_process,
x_expand_role => c2.expand_role,
x_error_item_type => c2.error_item_type,
x_runnable_flag => c2.runnable_flag,
x_version => v_version,
x_level_error => v_api_error_code
);
SELECT
p_item_type process_item_type,
'UPG_PN_'||p_item_type||'_'
||p_line_type||'_'||to_char(p_cycle_id) process_name,
1 process_version,
nvl(oemap.activity_item_type,p_item_type) activity_item_type,
nvl(oemap.activity_name,
'UPG_AN_'||to_char(soac.action_id)) activity_name,
wf_process_activities_s.nextval instance_id,
nvl(oemap.activity_name,
'UPG_ILN_'||to_char(sca.cycle_action_id)) instance_label,
decode(nvl(oemap.activity_name,
'UPG_AN_'||to_char(soac.action_id)),
'START','START','END','END',NULL) start_end,
decode(soac.action_approval,'Y','ITEMATTR','CONSTANT') perform_role_type,
decode(soac.action_approval,'Y','NOTIFICATION_APPROVER','') perform_role,
soac.result_column result_column,
soac.action_approval approval
FROM
so_cycle_actions sca,
so_actions soac,
oe_upgrade_wf_act_map oemap
WHERE sca.cycle_id = p_cycle_id
AND sca.action_id = soac.action_id
AND sca.action_id = oemap.action_id (+)
AND ( (soac.action_id in (SELECT action_id FROM oe_upgrade_wf_act_map
WHERE line_type in ( p_line_type , 'BOTH')) )
OR (soac.action_id not in (SELECT action_id FROM oe_upgrade_wf_act_map )))
AND SOAC.action_id not in
( SELECT name FROM oe_upgrade_wf_obs_codes
WHERE type = 'ACTION' )
AND ( sca.action_id in (SELECT action_id from so_action_pre_reqs)
OR sca.cycle_action_id in (SELECT cycle_action_id from so_action_pre_reqs))
AND decode(soac.result_table,'SO_HEADERS','OEOH',
'SO_LINES','OEOL','ERROR') = p_item_type;
SELECT count(1)
INTO v_booked_count
FROM so_action_pre_reqs
WHERE action_id = 1
AND cycle_action_id in (SELECT cycle_action_id
FROM so_cycle_actions
WHERE cycle_id = p_cycle_id)
AND result_id = 1;
SELECT 'Y' INTO v_check_duplicate_flag
FROM wf_process_activities
WHERE process_item_type = c2.process_item_Type
AND process_name = c2.process_name
AND process_version = c2.process_version
AND instance_label = c2.instance_label;
SELECT 'x' INTO v_dummy
FROM wf_process_activities
WHERE process_name = 'UPG_PN_OEOL_'||p_line_type||'_'||to_char(p_cycle_id)
AND activity_name = 'START';
SELECT wf_process_activities_s.nextval
INTO v_instance_id
FROM dual;
SELECT
spr.cycle_action_id,
spr.action_id,
spr.result_id,
spr.group_number
FROM so_action_pre_reqs spr,
so_cycle_actions sca,
so_actions sa,
so_actions sa2
WHERE spr.cycle_action_id = sca.cycle_action_id
AND sca.cycle_id = p_cycle_id
AND spr.action_id = sa.action_id
AND sca.action_id = sa2.action_id
AND decode(sa2.result_table,'SO_HEADERS','OEOH','SO_LINES','OEOL','ERROR')=p_item_type
AND spr.action_id IN
(SELECT action_id
FROM so_cycle_actions
WHERE cycle_id = p_cycle_id)
AND spr.cycle_action_id IN
(SELECT cycle_action_id
FROM so_Cycle_actions
WHERE cycle_id = p_cycle_id)
AND decode(sa.result_table,'SO_HEADERS','OEOH','SO_LINES','OEOL','ERROR')=p_item_type
AND spr.result_id NOT IN
( SELECT result_id FROM so_results, oe_upgrade_wf_obs_codes
WHERE type ='RESULT'
AND so_results.result_id = oe_upgrade_wf_obs_codes.name )
AND spr.action_id NOT IN
( SELECT action_id FROM so_actions a, oe_upgrade_wf_obs_codes b
WHERE type = 'ACTION'
AND a.action_id = b.name )
/* Transition from Pick Release and Backorder Release obsoleted */
AND spr.action_id NOT IN (2,4)
AND (sa.action_id <> 3
/* Transition from Ship confirm to Inv. Intfce. obsoleted */
OR (sa.action_id = 3 and sa2.action_id <> 11))
AND ( SPR.ACTION_ID NOT IN (2,3,4,11,16) or
(spr.action_id in (2,3,4,11,16) and sca.action_id not in (2,3,4,11,16)) )
AND ( (sa.action_id in (SELECT action_id FROM oe_upgrade_wf_act_map
WHERE line_type in ( p_line_type , 'BOTH')) )
OR (sa.action_id not in (SELECT action_id FROM oe_upgrade_wf_act_map )))
AND ( (sa2.action_id in (SELECT action_id FROM oe_upgrade_wf_act_map
WHERE line_type in ( p_line_type , 'BOTH')) )
OR (sa2.action_id not in (SELECT action_id FROM oe_upgrade_wf_act_map )))
ORDER BY spr.cycle_action_id, spr.group_number;
SELECT
cycle_id,
action_id
INTO
v_cycle_id,
v_action_id
FROM so_cycle_actions
WHERE cycle_action_id = v_cycle_Action_id;
SELECT 'Y' INTO v_mul_rec_grp_flag
FROM oe_upgrade_wf_mulgrp_v
WHERE cycle_action_id = c2.cycle_action_id
AND action_id = c2.action_id;
INSERT INTO oe_action_pre_reqs
(
cycle_action_id,
action_id,
result_id,
group_number,
cycle_id,
type,
line_type,
instance_label,
instance_id
)
VALUES
(
c2.cycle_action_id,
c2.action_id,
c2.result_id,
c2.group_number,
v_cycle_id,
p_item_type,
p_line_type,
null,
null
);
INSERT INTO oe_action_pre_reqs
(
cycle_action_id,
action_id,
result_id,
group_number,
cycle_id,
type,
line_type,
instance_label,
instance_id
)
VALUES
(
null,
c2.action_id,
c2.result_id,
c2.group_number,
v_cycle_id,
p_item_type,
p_line_type,
v_instance_label,
v_last_instance_id
);
SELECT wf_process_activities_s.nextval
INTO v_last_instance_id
FROM dual;
/* Insert data into WF_PROCESS_ACTIVITIES */
WF_LOAD.UPLOAD_PROCESS_ACTIVITY (
x_process_item_type => p_item_type,
x_process_name => v_process_name,
x_process_version => 1,
x_activity_item_type => 'WFSTD',
x_activity_name => 'AND',
x_instance_id => v_last_instance_id,
x_instance_label => v_instance_label,
x_protect_level => 20,
x_custom_level => 20,
x_start_end => null,
x_default_result => null,
x_icon_geometry => '0,0',
x_perform_role => null,
x_perform_role_type => 'CONSTANT',
x_user_comment => null,
x_level_error => v_api_error_level
);
INSERT INTO oe_action_pre_reqs
(
cycle_action_id,
action_id,
result_id,
group_number,
cycle_id,
type,
line_type,
instance_label,
instance_id
)
values
(
c2.cycle_action_id,
null,
null,
c2.group_number,
v_cycle_id,
p_item_type,
p_line_type,
v_instance_label,
v_last_instance_id
);
INSERT INTO oe_action_pre_reqs
(
cycle_action_id,
action_id,
result_id,
group_number,
cycle_id,
type,
line_type,
instance_label,
instance_id
)
VALUES
(
null,
c2.action_id,
c2.result_id,
c2.group_number,
v_cycle_id,
p_item_type,
p_line_type,
v_instance_label,
v_last_instance_id
);
SELECT
b.cycle_id,
b.action_id lin_action_id,
a.cycle_action_id,
a.result_id,
a.action_id hdr_action_id,
a.group_number
FROM so_action_pre_reqs a, so_Cycle_actions b
WHERE b.cycle_id = p_cycle_id
AND ((a.action_id IN (SELECT action_id
FROM oe_upgrade_wf_act_map
WHERE line_type IN ( p_line_type , 'BOTH')) )
OR (a.action_id NOT IN (SELECT action_id
FROM oe_upgrade_wf_act_map )))
AND ((b.action_id IN (SELECT action_id
FROM oe_upgrade_wf_act_map
WHERE line_type IN ( p_line_type , 'BOTH')) )
OR (b.action_id NOT IN (SELECT action_id
FROM oe_upgrade_wf_act_map )))
AND a.action_id IN
(SELECT action_id
FROM so_actions
WHERE result_table = 'SO_HEADERS'
AND (action_id = 1 or action_id not in (select action_id from oe_upgrade_wf_act_map)))
AND a.cycle_action_id IN
(SELECT cycle_action_id
FROM so_cycle_actions
WHERE action_id in
(SELECT action_id
FROM so_actions
WHERE result_table = 'SO_LINES'))
AND a.cycle_action_id = b.cycle_action_id
AND a.cycle_action_id IN
(SELECT cycle_action_id
FROM so_Cycle_actions
WHERE cycle_id = p_cycle_id);
SELECT
pr.instance_id,
pr.instance_label,
pr.rowid
FROM oe_action_pre_reqs pr
WHERE pr.cycle_id = v_cycle_id
AND pr.action_id = v_action_id
and pr.line_type = p_line_type -- included 3/24/00
FOR UPDATE;
SELECT
pr.instance_id,
pr.rowid
FROM oe_action_pre_reqs pr
WHERE pr.cycle_id = v_cycle_id
AND pr.cycle_action_id = v_cycle_action_id
AND pr.line_type = p_line_type -- included 3/24/00
FOR UPDATE;
SELECT 0 INTO v_error_level
FROM WF_ACTIVITIES
WHERE NAME = v_hdr_activity_name
AND ITEM_TYPE = 'OEOH'
AND VERSION = 1;
SELECT 'Y',instance_id
INTO
v_check_duplicate_flag,
v_instance_id
FROM wf_process_activities
WHERE process_item_type = 'OEOH'
AND process_name = v_hdr_process_name
AND process_version = 1
AND activity_item_type= 'OEOH'
AND activity_name = v_hdr_activity_name;
SELECT
wf_process_activities_s.nextval
INTO
v_instance_id
FROM dual;
UPDATE oe_action_pre_reqs
SET
action_id = null,
result_id = null,
instance_label = 'Interdependecy-Hdr',
instance_id = v_instance_id
WHERE rowid = c4.rowid;
UPDATE oe_action_pre_reqs
SET
instance_id2 = c4.instance_id,
instance_label2 = c4.instance_label,
instance_id= v_instance_id,
instance_label = 'Interdependecy-Hdr'
WHERE rowid = c4.rowid;
INSERT INTO oe_action_pre_reqs
(
cycle_action_id,
action_id,
result_id,
group_number,
cycle_id,
type,
line_type,
instance_label,
instance_id
)
VALUES
(
null,
c2.hdr_action_id,
c2.result_id,
c2.group_number,
c2.cycle_id,
'OEOH',
'REG',
'Interdependecy-Hdr',
v_instance_id
);
SELECT 0 INTO v_error_level
FROM WF_ACTIVITIES
WHERE NAME = v_lin_activity_name
AND ITEM_TYPE = 'OEOL'
AND VERSION = 1;
SELECT 'Y',instance_id INTO v_check_duplicate_flag, v_instance_id
FROM wf_process_activities
WHERE process_item_type = 'OEOL'
AND process_name = v_lin_process_name
AND process_version = 1
AND activity_item_type= 'OEOL'
AND activity_name = v_lin_activity_name;
SELECT wf_process_activities_s.nextval INTO v_instance_id FROM dual;
INSERT INTO oe_action_pre_reqs
(
group_number,
cycle_id,
type,
line_type,
action_id,
instance_label,
instance_id
)
VALUES
(
c2.group_number,
c2.cycle_id,
'OEOL',
p_line_type,
'-3',
'Interdependecy-Line',
v_instance_id
);
INSERT INTO oe_action_pre_reqs
(
cycle_action_id,
action_id,
result_id,
group_number,
cycle_id,
type,
line_type,
instance_label,
instance_id
)
VALUES
(
c2.cycle_action_id,
'',
'',
c2.group_number,
c2.cycle_id,
'OEOL',
p_line_type,
'Interdependecy-Line',
v_instance_id
);
select instance_id into v_instance_id
from wf_process_activities
where process_item_type = 'OEOL'
and process_version =1
and process_name = v_lin_process_name
and instance_label = 'ENTER';
SELECT wf_process_activities_s.nextval INTO v_instance_id FROM dual;
UPDATE oe_action_pre_reqs
SET
cycle_action_id = null,
instance_label = 'ENTER',
instance_id = v_instance_id
WHERE rowid = c6.rowid;
UPDATE oe_action_pre_reqs
SET
cycle_action_id = null,
instance_id2 = v_instance_id,
instance_label2 = 'ENTER'
WHERE rowid = c6.rowid;
INSERT INTO oe_action_pre_reqs
(
cycle_action_id,
action_id,
result_id,
group_number,
cycle_id,
type,
line_type,
instance_label,
instance_id
)
VALUES
(
c2.cycle_action_id,
'',
'',
c2.group_number,
c2.cycle_id,
'OEOL',
p_line_Type,
'ENTER',
v_instance_id
);
INSERT INTO oe_action_pre_reqs
(
cycle_action_id,
action_id,
result_id,
group_number,
cycle_id,
type,
line_type,
instance_label,
instance_id
)
VALUES
(
c2.cycle_action_id,
'',
'',
c2.group_number,
c2.cycle_id,
'OEOL',
p_line_Type,
'ENTER',
v_instance_id
);
SELECT cycle_action_id, action_id, result_id, group_number, rowid
FROM oe_action_pre_reqs
WHERE cycle_action_id IS NOT null
AND type = p_item_type
AND line_type = p_line_type
AND cycle_action_id in
(SELECT cycle_action_id
FROM oe_action_pre_reqs
WHERE cycle_action_id is not null
AND cycle_id = p_cycle_id
GROUP BY cycle_action_id
HAVING count(*) > 1)
ORDER BY cycle_action_id
FOR UPDATE;
SELECT count(distinct group_number)
INTO v_count
FROM oe_action_pre_reqs
WHERE cycle_action_id = c2.cycle_action_id;
SELECT
'UPG_PN_'||p_item_type||'_'||p_line_type||'_'||to_char(cycle_id) ,
decode(v_count, 1, 'AND_'||to_char(c2.cycle_action_id), 'OR_'||to_char(c2.cycle_action_id)),
cycle_id
INTO
v_process_name,
v_instance_label,
v_cycle_id
FROM so_cycle_actions
WHERE cycle_action_id = c2.cycle_action_id;
SELECT wf_process_activities_s.nextval
INTO v_last_instance_id
FROM dual;
INSERT INTO oe_action_pre_reqs
(
cycle_action_id,
action_id,
result_id,
group_number,
cycle_id,
type,
line_type,
instance_label,
instance_id,
instance_label2,
instance_id2
)
VALUES
(
c2.cycle_action_id,
null,
null,
c2.group_number,
v_cycle_id,
p_item_type,
p_line_type,
v_instance_label,
v_last_instance_id,
null,
null
);
UPDATE oe_action_pre_reqs
SET cycle_action_id = null,
instance_label2 = v_instance_label,
instance_id2 = v_last_instance_id
WHERE rowid = c2.rowid;
SELECT
cycle_action_id,
action_id,
decode(result_id,null,'*','UPG_RC_'||to_char(result_id)) result_code,
cycle_id,
instance_id,
instance_id2
FROM oe_action_pre_reqs
WHERE type = p_item_type
AND cycle_id = p_cycle_id
AND line_type = p_line_type;
SELECT
a.action_id action_id_c3,
nvl(m.activity_name,'UPG_AN_'
||to_char(a.action_id)) act_name_t,
'UPG_PN_'||p_item_type||'_'
||p_line_type||'_'||to_char(p_cycle_id) proc_name,
nvl(m.activity_name,
'UPG_ILN_'||to_char(ca.cycle_action_id)) instance_label,
nvl(m.activity_seq, 0) act_seq
FROM so_actions a,
so_cycle_actions ca,
oe_upgrade_wf_act_map m
WHERE ca.cycle_id = p_cycle_id
AND ca.action_id = a.action_id
AND ca.cycle_action_id = c2.cycle_action_id
AND a.action_id = m.action_id(+)
ORDER BY act_seq;
SELECT a.action_id action_id_c5,
nvl(m.activity_name,'UPG_AN_'
||to_char(a.action_id)) act_name_f,
nvl(m.activity_seq, 0) act_seq,
nvl(m.activity_name,
'UPG_ILN_'||to_char(ca.cycle_action_id)) instance_label,
nvl(m.activity_result,c2.result_code) act_result
FROM so_actions a,
so_cycle_actions ca,
oe_upgrade_wf_act_map m
WHERE a.action_id = c2.action_id
AND a.action_id = ca.action_id
AND ca.cycle_id = p_cycle_id
AND a.action_id = m.action_id(+)
ORDER BY act_seq desc;
/* Insert INTO table wf_activity_transitions */
oe_upgrade_wf2.insert_into_wf_table
( v_from_instance_id,
v_to_instance_id,
v_result_code,
v_level_error
);
/* Insert into table wf_activity_transitions */
v_error_level := 2097;
oe_upgrade_wf2.insert_into_wf_table
(
v_from_instance_id,
v_to_instance_id,
v_result_code,
v_level_error
);
SELECT max(activity_seq)
INTO v_max_seq
FROM oe_upgrade_wf_act_map
WHERE action_id = c6.action_id_c5;
/* Insert into table wf_activity_transitions */
-- dbms_output.put_line('************* 2 **************');
SELECT 'x' INTO v_dummy
FROM wf_lookups
WHERE lookup_type = 'UPG_RT_'||to_char(c6.action_id_c5)
AND lookup_code = c6.act_result;
oe_upgrade_wf2.insert_into_wf_table
(
v_from_instance_id,
v_to_instance_id,
v_result_code,
v_level_error
);
SELECT max(activity_seq)
INTO v_max_seq
FROM oe_upgrade_wf_act_map
WHERE action_id = c6.action_id_c5;
/* Insert into table wf_activity_transitions */
-- dbms_output.put_line('************* 9 **************'|| to_char(c2.action_id));
SELECT 'x' INTO v_dummy
FROM wf_lookups
WHERE lookup_type = 'UPG_RT_'||to_char(c6.action_id_c5)
AND lookup_code = c6.act_result;
oe_upgrade_wf2.insert_into_wf_table
(
v_from_instance_id,
v_to_instance_id,
v_result_code,
v_level_error
);
/* Insert into table wf_activity_transitions */
-- dbms_output.put_line('************* 5 **************');
oe_upgrade_wf2.insert_into_wf_table
(
v_from_instance_id,
v_to_instance_id,
'*',
v_level_error
);
/* Insert into table wf_activity_transitions */
-- dbms_output.put_line('************* 5 **************');
oe_upgrade_wf2.insert_into_wf_table
(
v_from_instance_id,
v_to_instance_id,
v_result_code,
v_level_error
);
select cycle_id from so_cycles
where cycle_id in (select cycle_id from so_cycle_actions
where action_id = 7)
and cycle_id not in (select cycle_id from so_cycle_actions
where action_id in (3, 13))
and cycle_id = p_cycle_id;
select from_process_activity, result_code
from wf_activity_transitions
where to_process_activity = v_instance_id;
select instance_id into v_instance_id
from wf_process_activities
where process_name =
'UPG_PN_OEOL_'||p_line_type||'_'||to_char(p_cycle_id)
and activity_name = 'UPG_FULFILLMENT_SUB'
and rownum = 1
order by process_version desc;
select to_process_activity into v_to_instance_id
from wf_activity_transitions
where from_process_activity = v_instance_id
and rownum = 1;
oe_upgrade_wf2.insert_into_wf_table
(
c4.from_process_activity,
v_to_instance_id,
c4.result_code,
v_level_error
);
WF_LOAD.Delete_Process_Activity
(
p_step => v_instance_id
);
select cycle_id from so_cycles
where cycle_id in (select cycle_id from so_cycle_actions
where action_id = 15);
select to_process_activity
from wf_activity_transitions
where from_process_activity = v_instance_id;
select from_process_activity, result_code
from wf_activity_transitions
where to_process_activity = v_instance_id;
select instance_id into v_mfg_instance_id
from wf_process_activities
where process_name = 'UPG_PN_OEOL_REG_'||to_char(p_cycle_id)
and activity_name = 'UPG_MODEL_MFG_RELEASE'
and rownum = 1
order by process_version desc;
select instance_id into v_instance_id
from wf_process_activities
where process_name = 'UPG_PN_OEOL_CFG_'||to_char(p_cycle_id)
and activity_name = 'UPG_MODEL_MFG_RELEASE'
and rownum = 1
order by process_version desc;
SELECT instance_id into v_cfg_shipping_instance_id
from wf_process_activities
where process_name = 'UPG_PN_OEOL_CFG_'||to_char(p_cycle_id)
and activity_name = 'UPG_SHIPPING_SUB'
and rownum = 1
order by process_version desc;
SELECT instance_id into v_mfg_shipping_instance_id
from wf_process_activities
where process_name = 'UPG_PN_OEOL_REG_'||to_char(p_cycle_id)
and activity_name = 'UPG_SHIPPING_SUB'
and rownum = 1
order by process_version desc;
select wf_process_activities_s.nextval
into v_new_instance_id
from dual;
oe_upgrade_wf2.insert_into_wf_table
(
c6.from_process_activity,
v_new_instance_id,
c6.result_code,
v_level_error
);
oe_upgrade_wf2.insert_into_wf_table
(
v_mfg_shipping_instance_id,
v_mfg_instance_id,
'UNRESERVE',
v_level_error
);
oe_upgrade_wf2.insert_into_wf_table
(
v_cfg_shipping_instance_id,
v_new_instance_id,
'UNRESERVE',
v_level_error
);
oe_upgrade_wf2.insert_into_wf_table
(
v_new_instance_id,
c4.to_process_activity,
'*',
v_level_error
);
WF_LOAD.Delete_Process_Activity
(
p_step => v_instance_id
);
select cycle_id from so_cycles
where cycle_id in (select cycle_id from so_cycle_actions
where action_id in (2,3,4,11,16))
and cycle_id in (select cycle_id from so_cycle_actions
where action_id = 13)
and cycle_id = p_cycle_id;
select from_process_activity, result_code
from wf_activity_transitions
where to_process_activity = v_shipping_instance_id;
select from_process_activity, result_code
from wf_activity_transitions
where to_process_activity = v_rma_instance_id;
select instance_id into v_shipping_instance_id
from wf_process_activities
where process_name =
'UPG_PN_OEOL_'||p_line_type||'_'||to_char(p_cycle_id)
and activity_name = 'UPG_SHIPPING_SUB'
and rownum = 1
order by process_version desc;
SELECT wf_process_activities_s.nextval
INTO v_and_instance_id1
FROM dual;
oe_upgrade_wf2.insert_into_wf_table
( c4.from_process_activity,
v_and_instance_id1,
c4.result_code,
v_level_error
);
delete from wf_activity_transitions
where from_process_activity = c4.from_process_activity
and to_process_activity = v_shipping_instance_id;
oe_upgrade_wf2.insert_into_wf_table
( v_and_instance_id1,
v_shipping_instance_id,
'*',
v_level_error
);
select instance_id into v_rma_instance_id
from wf_process_activities
where process_name =
'UPG_PN_OEOL_'||p_line_type||'_'||to_char(p_cycle_id)
and activity_name = 'UPG_RMA_RECEIVING_SUB'
and rownum = 1
order by process_version desc;
SELECT wf_process_activities_s.nextval
INTO v_and_instance_id2
FROM dual;
oe_upgrade_wf2.insert_into_wf_table
( c6.from_process_activity,
v_and_instance_id2,
c6.result_code,
v_level_error
);
delete from wf_activity_transitions
where from_process_activity =c6.from_process_activity
and to_process_activity = v_rma_instance_id;
oe_upgrade_wf2.insert_into_wf_table
( v_and_instance_id2,
v_rma_instance_id,
'*',
v_level_error
);
select instance_id into v_start_instance_id
from wf_process_activities
where process_name = 'UPG_PN_OEOL_'||p_line_type||'_'||
to_char(p_cycle_id)
and activity_name = 'START'
and rownum = 1
order by process_version desc;
SELECT wf_process_activities_s.nextval
INTO v_get_category_instance_id
FROM dual;
oe_upgrade_wf2.insert_into_wf_table
( v_start_instance_id,
v_get_category_instance_id,
'*',
v_level_error
);
oe_upgrade_wf2.insert_into_wf_table
( v_get_category_instance_id,
v_and_instance_id1,
'ORDER',
v_level_error
);
oe_upgrade_wf2.insert_into_wf_table
( v_get_category_instance_id,
v_and_instance_id2,
'RETURN',
v_level_error
);
SELECT b.process_item_type,b.process_name,b.activity_name, t.from_process_activity
FROM wf_activity_transitions t, wf_process_activities b
WHERE b.process_name = 'UPG_PN_'||p_item_type||'_'||p_line_type||'_'||to_char(p_cycle_id)
AND t.from_process_activity = b.instance_id
AND b.activity_name like 'UPG_AN%'
AND b.activity_name not like 'UPG_AN%WAIT_FOR_H'
AND b.activity_name not like 'UPG_AN%CONT_L'
AND b.process_version = (select max(c.process_version) from wf_process_activities c
where c.process_name = b.process_name
and c.activity_name = b.activity_name )
AND t.from_process_activity not in
(
SELECT from_process_activity
FROM wf_activity_transitions
WHERE result_code = '*'
)
GROUP BY b.process_item_type,b.process_name, b.activity_name,t.from_process_activity;
/* Insert into transtion table */
SELECT count(distinct result_code)
INTO v_transitions
FROM wf_activity_transitions
WHERE from_process_activity = c2.from_process_activity;
SELECT t.from_process_activity
INTO v_and_upg_close_instance_id
FROM wf_activity_transitions t, wf_process_activities b
WHERE b.process_name = 'UPG_PN_'||p_item_type||'_'||p_line_type||'_'
||to_char(p_cycle_id)
AND b.activity_name = 'UPG_CLOSE_' || decode(p_item_type, 'OEOH',
'HEADER', 'OEOL', 'LINE', '') || '_PROCESS'
AND b.process_version = (select max(c.process_version)
from wf_process_activities c
where c.process_name = b.process_name
and c.activity_name = b.activity_name )
AND t.to_process_activity = b.instance_id;
oe_upgrade_wf2.insert_into_wf_table
(
c2.from_process_activity,
v_and_upg_close_instance_id,
'*',
v_level_error
);
SELECT count(*)
INTO v_lookup_count
FROM wf_lookups
WHERE lookup_type = (SELECT result_type
FROM wf_activities
WHERE name = c2.activity_name
AND item_type = p_item_type
AND rownum = 1);
SELECT wf_process_activities_s.nextval
INTO v_instance_id
FROM dual;
oe_upgrade_wf2.insert_into_wf_table
(
c2.from_process_activity,
v_instance_id,
'*',
v_level_error
);
oe_upgrade_wf2.insert_into_wf_table
(
v_instance_id,
c2.from_process_activity,
'*',
v_level_error
);
SELECT distinct from_process_activity
FROM wf_activity_transitions
WHERE from_process_activity IN
(
SELECT instance_id
FROM wf_process_activities
WHERE process_name = 'UPG_PN_OEOL_'||p_line_type
||'_'||to_char(p_cycle_id)
AND activity_name <> 'START'
)
AND from_process_activity NOT IN
(
SELECT to_process_activity
FROM wf_activity_transitions a,
wf_process_activities b
WHERE a.to_process_activity = b.instance_id
AND b.process_name = 'UPG_PN_OEOL_'||p_line_type
||'_'||to_char(p_cycle_id)
AND a.from_process_activity <> a.to_process_activity
);
/* Insert into transtion table */
oe_upgrade_wf2.insert_into_wf_table
(
v_instance_id,
c2.from_process_activity,
'*',
v_level_error
);
-- dbms_output.put_line('inserted trans');
v_delete_wait_instance NUMBER;
SELECT a.instance_id, a.instance_label, attr_value1.text_value
FROM wf_process_activities a, wf_process_activities b,
wf_activity_attr_values attr_value1, wf_activity_attr_values attr_value2
WHERE a.process_name = 'UPG_PN_'||p_item_type||'_'||p_line_type||'_'||to_char(p_cycle_id)
AND a.process_name = b.process_name
AND a.activity_name like 'UPG_AN_OEOL_%WAIT_FOR_H'
AND b.activity_name like 'UPG_AN_OEOL_%WAIT_FOR_H'
AND a.instance_id <> b.instance_id
AND attr_value1.name = 'CONTINUATION_ACTIVITY'
AND attr_value1.process_activity_id = a.instance_id
AND attr_value2.name = 'CONTINUATION_ACTIVITY'
AND attr_value2.process_activity_id = b.instance_id
AND attr_value1.text_value = attr_value2.text_value;
SELECT from_process_activity, result_code
FROM wf_activity_transitions
WHERE to_process_activity = v_delete_wait_instance;
SELECT to_process_activity, result_code
FROM wf_activity_transitions
WHERE from_process_activity = v_delete_wait_instance;
SELECT attr_value.text_value
INTO v_continue_line_flow_label
FROM wf_process_activities a, wf_activity_attr_values attr_value
WHERE a.instance_label = c2.text_value
AND a.process_name = 'UPG_PN_OEOH_REG_'||to_char(p_cycle_id)
AND attr_value.process_activity_id = a.instance_id
AND attr_value.name = 'WAITING_ACTIVITY';
SELECT instance_id
INTO v_cont_act_instance_id
FROM wf_process_activities
WHERE instance_label=v_continue_line_flow_label
AND process_name = 'UPG_PN_'||p_item_type||'_'||p_line_type||'_'||to_char(p_cycle_id);
v_delete_wait_instance := c2.instance_id;
SELECT 1
INTO v_tmp
FROM wf_activity_transitions
WHERE from_process_activity = c4.from_process_activity
AND to_process_activity = v_cont_act_instance_id
AND result_code = c4.result_code;
update wf_activity_transitions
set to_process_activity = v_cont_act_instance_id
where from_process_activity = c4.from_process_activity
and to_process_activity = v_delete_wait_instance;
SELECT 1
INTO v_tmp
FROM wf_activity_transitions
WHERE from_process_activity = v_cont_act_instance_id
AND to_process_activity = c6.to_process_activity
AND result_code = c6.result_code;
update wf_activity_transitions
set from_process_activity = v_cont_act_instance_id
where from_process_activity = v_delete_wait_instance
and to_process_activity = c6.to_process_activity;
WF_LOAD.Delete_Process_Activity(p_step=> v_delete_wait_instance);
SELECT max(wpa2.icon_geometry) icon,
wpa.instance_id id
FROM wf_activity_transitions wat,
wf_process_activities wpa,
wf_process_activities wpa2
WHERE wat.to_process_activity = wpa.instance_id
AND wpa.process_name = 'UPG_PN_'||p_item_type||'_'
||p_line_type||'_'||to_char(p_cycle_id)
AND wpa.icon_geometry = '0,0'
AND wat.from_process_activity = wpa2.instance_id
AND wpa2.icon_geometry <> '0,0'
GROUP BY wpa.instance_id;
SELECT icon_geometry icon
FROM wf_process_activities
WHERE process_name = 'UPG_PN_'||p_item_type||'_'
||p_line_type||'_'||to_char(p_cycle_id)
GROUP BY icon_geometry
HAVING count(icon_geometry) > 1;
SELECT a.icon_geometry icon_from,
a.instance_id from_id,
b.icon_geometry icon_to,
b.instance_id to_id
FROM wf_process_activities a,
wf_process_activities b,
wf_activity_transitions c
WHERE a.instance_id = c.from_process_activity
AND b.instance_id = c.to_process_activity
AND a.process_name = 'UPG_PN_'||p_item_type||'_'
||p_line_type||'_'||to_char(p_cycle_id);
UPDATE wf_process_activities
SET icon_geometry = '-312,0'
WHERE activity_name = 'START'
and process_name like 'UPG_PN%'
and icon_geometry = '0,0';
UPDATE wf_process_activities
SET icon_geometry = to_char(v_icon_row+110)||',0'
WHERE instance_id = c2.id;
SELECT instance_id id
FROM wf_process_activities
WHERE process_name = 'UPG_PN_'||p_item_type||'_'
||p_line_type||'_'||to_char(p_cycle_id)
AND icon_geometry = c4.icon;
UPDATE wf_process_activities
SET icon_geometry = to_char(v_icon_x_value)||','
||to_char(v_icon_col)
WHERE instance_id = c6.id;
UPDATE wf_activity_transitions
SET arrow_geometry = '1;0;0;0;0.30000;'||c8.icon_from||':'||c8.icon_to||':'
SELECT
to_process_activity ,
process_name,
process_item_type
FROM
wf_activity_transitions WAT,
wf_process_activities
WHERE
process_name = 'UPG_PN_'||p_item_type||'_'
||p_line_type||'_'||to_char(p_cycle_id)
AND wat.to_process_activity NOT IN
(
SELECT from_process_activity
FROM wf_activity_transitions
)
AND instance_id = to_process_activity
AND to_process_activity IN
(
SELECT instance_id
FROM wf_process_activities wpa2
WHERE wpa2.activity_name <> 'END'
AND wpa2.process_name = 'UPG_PN_'||p_item_type||'_'||p_line_type
||'_'||to_char(p_cycle_id));
SELECT
'Y',
instance_id
INTO
v_end_found_flag,
v_end_activity_id
FROM wf_process_activities
WHERE process_name = c2.process_name
AND instance_label = 'AND'
AND activity_name = 'AND';
SELECT wf_process_activities_s.nextval
INTO v_and_activity_id
FROM dual;
SELECT sc.cycle_id
FROM so_cycles sc
WHERE sc.cycle_id NOT IN
(select cycle_id
from oe_upgrade_log
where (module='NU'
and cycle_id is not null)
or (module is null
and cycle_id is not null))
AND (exists (select 1
from so_headers_all sh
where sh.cycle_id = sc.cycle_id
and open_flag = 'Y')
OR exists (select 1
from so_lines_all sl
where sl.cycle_id = sc.cycle_id
and open_flag = 'Y'));
select cycle_id from so_cycles;
SELECT 'Y'
INTO v_cfg_item
FROM so_cycle_actions
WHERE action_id = 15
AND cycle_id = c4.cycle_id;
SELECT 'Y'
INTO v_cfg_item
FROM so_cycle_actions
WHERE action_id = 15
AND cycle_id = c2.cycle_id;
update oe_action_pre_reqs
set action_id = -2
where action_id = 1
and type = 'OEOL'
and result_id in (5, 15);
update oe_action_pre_reqs
set action_id = -1
where action_id = 1
and type = 'OEOH'
and result_id in (5, 15);
INSERT INTO oe_upgrade_errors
(module,error_level,comments,creation_date)
VALUES
('WF',v_error_level,v_error_message,sysdate - 1);
INSERT INTO oe_upgrade_log ( creation_date,cycle_id)
VALUES (sysdate,c2.cycle_id);
select
instance_id,
wa.name,
wa.result_type
from
wf_process_activities wpa,
wf_activities wa
where wpa.activity_name = wa.name
and wpa.process_name = 'UPG_PN_'||p_item_type||'_'||p_line_type||'_'||to_char(p_cycle_id)
and wa.type = 'NOTICE';
select to_process_activity, result_code
from wf_activity_transitions
where from_process_activity = v_notn_instance_id;
select from_process_activity, result_code
from wf_activity_transitions
where to_process_activity = v_notn_instance_id;
select
result_column,
action_id,
'UPG_RT_'||to_char(sa.action_id)
into
v_result_column,
v_action_id,
v_result_Type
from
so_actions sa
where sa.action_id = to_number(substr(c2.name,8,10))
and substr(c2.name,8,10) between '0000000000' and '9999999999';
select 'N' into v_fyi_Flag from so_action_results
where action_id = v_action_id
and rownum = 1;
select
wf_process_activities_s.nextval into v_instance_id
from dual;
/* Insert process activity OR - */
select
wf_process_activities_s.nextval into v_or_instance_id
from dual;
oe_upgrade_wf2.insert_into_wf_table
(
c6.from_process_activity,
v_instance_id,
c6.result_code,
v_level_error
);
/* Delete the transition between A and Notfn. */
wf_load.delete_transition (
p_previous_step => c6.from_process_activity,
p_next_step => c2.instance_id,
p_result_code => v_result_code
);
select wf_process_activities_s.nextval into v_or_instance_id2
from dual;
/* update notification to point to the OR just created */
update wf_activity_transitions
set to_process_activity = v_or_instance_id2
where from_process_activity = v_notn_instance_id
and to_process_activity = c4.to_process_activity;
oe_upgrade_wf2.insert_into_wf_table
(
v_instance_id,
v_or_instance_id2,
c4.result_code,
v_level_error
);
oe_upgrade_wf2.insert_into_wf_table
(
v_or_instance_id2,
c4.to_process_activity,
'*',
v_level_error
);
oe_upgrade_wf2.insert_into_wf_table
(
v_instance_id,
v_or_instance_id,
'*',
v_level_error
);
oe_upgrade_wf2.insert_into_wf_table
(
v_or_instance_id,
c2.instance_id,
'*',
v_level_error
);
oe_upgrade_wf2.insert_into_wf_table
(
c2.instance_id,
v_or_instance_id,
'NOT_PROCESSED',
v_level_error
);