The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT p.plan_start_date, p.plan_type, p.curr_cutoff_date
FROM msc_plans p
WHERE p.plan_id = p_plan_id;
SELECT category_set_id
FROM msc_category_sets
WHERE default_flag = 1;
SELECT distinct sr_instance_id
from msc_plan_organizations
WHERE plan_id = p_plan_id;
' INSERT INTO msc_form_query' ||
'(query_id, ' ||
'last_update_date, ' ||
'last_updated_by, ' ||
'last_update_login, ' ||
'creation_date, ' ||
'created_by, ' ||
count_list_mfq || ' ,number16, number5, number13) ' ||
' SELECT :query_id ' ||
', TRUNC(SYSDATE),' ||
'-1, -1,' ||
'TRUNC(SYSDATE),' ||
'-1, ' ||
count_list || ' ,exception_group, sum(exception_count), sum(nvl(new_exception_count,exception_count)) ';
' insert into msc_form_query (query_id, '||
' last_update_date, last_updated_by, '||
' creation_date, created_by, last_update_login, '||
' NUMBER2,NUMBER14, '||
' number16, number5, number13 '||ship_list_mfq ||') ' ||
' select :var_exception_id,trunc(sysdate),-1,trunc(sysdate),-1,-1, '||
' 100,PLAN_ID, 10, count(distinct shipment_id), count(distinct shipment_id) '||ship_count_list ||
' from msc_shipment_details_v '||
' where plan_id = :p_plan_id '||
' and category_set_id = :v_cat_id '||
ship_stat ||
' group by plan_id '||ship_count_list;
PROCEDURE insert_exc_groups(var_exception_id NUMBER) IS
statement varchar2(20000);
' INSERT INTO msc_form_query' ||
'(query_id, ' ||
'last_update_date, ' ||
'last_updated_by, ' ||
'last_update_login, ' ||
'creation_date, ' ||
'created_by, ' ||
-- 'char1, ' || -- plan name
'number14, ' || -- plan id
'number1, ' || -- version
'number2, ' || -- exception type
'number16, ' || -- exception group
'number5, ' || -- exception count
'number3, ' || -- item id
'number11, ' || -- pf id
'number4, ' || -- org id
'number15, ' || -- sr instance id
'number6, ' || -- project id
'number7, ' || -- task id
'number8, ' || -- category id
'number9, ' || -- dept id
'number10, ' || -- res id
'number12, ' || -- supplier id
'number13, ' || -- new exception count
'char2, ' || --planner code
'char3, ' || -- dept class
'char4, ' || -- planning group
'char5, ' || -- item name
'char6, ' || -- org code
'char7, ' || -- dept code
'char8, ' || -- res group
'char9, ' || -- buyer
'char10, ' || -- res code
'char11, ' || -- res type code
'char12, ' || --?
'char13, ' || -- version text
'char14) ' || -- category name
' SELECT ' ||
'query_id, ' ||
'last_update_date, ' ||
'last_updated_by, ' ||
'last_update_login, ' ||
'creation_date, ' ||
'created_by, ' ||
-- 'char1, ' ||
'number14, ' ||
'number1, ' ||
'number16, ' ||
'NULL, ' ||
'sum(number5), '||
'number3, ' ||
'number11, ' ||
'number4, ' ||
'number15, ' ||
'number6, ' ||
'number7, ' ||
'number8, ' ||
'number9, ' ||
'number10, ' ||
'number12, ' ||
'sum(number13), ' ||
'char2, ' ||
'char3, ' ||
'char4, ' ||
'char5, ' ||
'char6, ' ||
'char7, ' ||
'char8, ' ||
'char9, ' ||
'char10, ' ||
'char11, ' ||
'char12, ' ||
'char13, ' ||
'char14 ' ||
' FROM msc_form_query ' ||
' WHERE query_id = :query_id '||
' GROUP BY query_id, last_update_login, last_updated_by, '||
' creation_date, created_by, last_update_date, ' ||
' char1, number14, number1, number16, number3, number11, '||
' number4, number15, number6, number7, number8, number9, number10, '||
' number12, char2, char3, char4, char5, '||
' char6, char7, char8, char9, char10, char11, char12, char13, char14 ';
SELECT MAX(NVL(version,0))+1
INTO version_id
FROM msc_item_exceptions
WHERE plan_id= plan;
INSERT INTO msc_item_exceptions
(exception_type, inventory_item_id, plan_id, sr_instance_id,
organization_id, last_update_date, last_updated_by,
creation_date, created_by, last_update_login, display, request_id,
program_application_id, program_id,
program_update_date, exception_count, project_id,
task_id, version, planning_group,
department_id, resource_id, exception_group)
SELECT exception_type, inventory_item_id, plan_id, sr_instance_id,
organization_id, last_update_date, last_updated_by,
creation_date, created_by, last_update_login, display, request_id,
program_application_id, program_id,
program_update_date, exception_count, project_Id,
task_id, version_id, planning_group,
department_id, resource_id, exception_group
FROM msc_item_exceptions
WHERE version is null
AND plan_id= plan;
PROCEDURE update_row(p_exception_id number,
p_omit_list VARCHAR2,
p_row_id VARCHAR2,
p_last_update_login NUMBER,
p_last_updated_by NUMBER) IS
c integer;
'update msc_item_exceptions
set display = 2,
last_update_login = :last_update_login,
last_updated_by = :last_updated_by,
last_update_date = sysdate
where rowid in (
select chartorowid(char8)
from msc_form_query
where ' ||p_omit_list||
' and query_id = :exception_id )';
dbms_sql.bind_variable(c,'last_update_login',p_last_update_login);
dbms_sql.bind_variable(c,'last_updated_by',p_last_updated_by);
END update_row;
'select display
from msc_item_exceptions
where rowid in (
select chartorowid(char8)
from msc_form_query
where ' ||p_omit_list||
' and query_id = :exception_id )'||
' for update of display nowait ';