[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure delete_from_results_table(p_query_id in number,
p_plan_id in number) is
BEGIN
--KSA_DEBUG(SYSDATE,' p_query_id <> '||p_query_id,'delete_from_results_table');
delete msc_pq_results
where query_id = p_query_id
and plan_id = p_plan_id ;
end delete_from_results_table;
PROCEDURE delete_from_results_table(p_query_id IN NUMBER,
p_plan_id IN NUMBER,
p_detail_query_id IN NUMBER) IS
BEGIN
DELETE msc_pq_results
WHERE query_id = p_query_id
AND plan_id = p_plan_id
AND detail_query_id = p_detail_query_id;
END delete_from_results_table;
l_insert_begin varchar2(1000) := 'INSERT INTO MSC_PQ_RESULTS ('
|| ' QUERY_ID, PLAN_ID, ORGANIZATION_ID, SR_INSTANCE_ID, SUMMARY_DATA,';
l_insert_end varchar2(1000) := 'created_by, creation_date, last_update_date, '||
' last_updated_by, last_update_login) ';
l_insert_end_new varchar2(1000) := 'sequence_id, created_by, creation_date, last_update_date, '||
' last_updated_by, last_update_login ) ';
l_select_begin varchar2(1000) :=' select distinct '||to_char(p_query_id)
||', nvl(plan_id,-1), nvl(organization_id,-1), nvl(sr_instance_id,-1), ';
SELECT NVL(priority,999)
FROM msc_pq_types
WHERE query_id= p_query_id
AND NVL(detail_query_id,query_id) = p_detail_query_id;
select group_by, sequence
from msc_selection_criteria_v
where folder_id = l_query_id
and nvl(source_type, -1) = nvl(l_source_type, -1)
and nvl(object_type, -1) = nvl(l_object_type, -1)
and nvl(count_by, 2) = 1
and nvl(active_flag,2) = 1
order by sequence;
SELECT distinct ATTRIBUTE_NAME group_by
FROM MSC_WORKLIST_GROUPBY
WHERE QUERY_ID = l_query;
select source_type, object_type, sequence_id,NVL(priority,999), frequency
from msc_pq_types
where query_id = l_query_id;
SELECT frequency
FROM msc_pq_types
WHERE query_id = p_query_id
AND detail_query_id = p_detail_query_id;
l_select_begin :=' select distinct '||to_char(p_MASTER_QUERY_ID)
||', nvl(plan_id,-1), ';
l_select_begin :=l_select_begin ||l_group_by_cols;
l_select_begin :=l_select_begin ||'-1,-1,';
l_insert_end := 'created_by, creation_date, last_update_date, '||
' last_updated_by, last_update_login , detail_query_id,PRIORITY ) ';
delete_from_results_table(p_query_id, p_plan_id);
l_sql_stmt := l_insert_begin||l_item_cols||l_insert_end||
l_select_begin||l_summary_col2||l_item_cols||l_who_cols||
' FROM '||p_item_view||
' where plan_id = '||p_plan_id||' and '||p_where_clause;
l_sql_stmt := l_insert_begin||l_res_cols||l_insert_end||
l_select_begin||l_summary_col||l_res_cols||l_who_cols||
' FROM '||p_res_view||
' where plan_id = '||p_plan_id||' and '||p_where_clause;
l_sql_stmt := l_insert_begin||l_supp_cols||l_insert_end||
l_select_begin||l_summary_col||l_supp_cols||l_who_cols||
' FROM '||p_supp_view||
' where plan_id = '||p_plan_id||' and '||p_where_clause;
l_sql_stmt := l_insert_begin||l_order_cols||l_insert_end||
l_select_begin||l_summary_col2||l_Order_cols1||l_who_cols||
' FROM '||p_order_view||
' WHERE plan_id = '||p_plan_id||' AND '||p_where_clause;
select plan_type
into v_plan_type
from msc_plans
where plan_id = p_plan_id;
l_sql_stmt := l_insert_begin||'source_type ,'|| l_excp_cols || l_insert_end||
l_select_begin||l_detail_col||l_source_type||','||l_excp_cols || l_who_cols||
' FROM '||l_view||
' where plan_id = '||p_plan_id||l_cp_context||' and '||p_where_clause
||' and ( source_type = 2 and exception_type = '||l_object||')';
l_sql_stmt := l_insert_begin||'source_type , sequence_id, '||
l_excp_cols ||l_new_group_by_cols1|| --l_worklist_cols|| --l_new_group_by_cols1||
l_insert_end||
l_select_begin||l_detail_col||
l_source_type||','||l_seq_id||','||
l_excp_cols2 ||l_new_group_by_cols2|| --l_worklist_cols|| --l_new_group_by_cols2||
l_who_cols||
' FROM '||l_view||' where plan_id = '||p_plan_id||
' and '||p_where_clause||
' and ( source_type = 1 and exception_type = '||
l_object||')';
INSERT INTO MSC_PQ_RESULTS(query_id,plan_id,sr_instance_id,
organization_id, exception_type,
source_type, summary_data, sequence_id,
exception_count, groupby_org,
INVENTORY_ITEM_ID, --groupby_supply_item,
CATEGORY_ID, PLANNER_CODE,
CUSTOMER_iD, SUPPLIER_ID,
CUSTOMER_SITE_ID, SUPPLIER_SITE_ID,
created_by,creation_date,
last_updated_by, last_update_date,
detail_query_id,Priority)
SELECT l_temp_query_id, NVL(p_plan_id,-1), sr_instance_id,
organization_id, exception_type,
source_type, -99, sequence_id,
COUNT(*), groupby_org,
INVENTORY_ITEM_ID, --groupby_supply_item,
CATEGORY_ID, PLANNER_CODE,
CUSTOMER_iD, SUPPLIER_ID,
CUSTOMER_SITE_ID, SUPPLIER_SITE_ID,
fnd_global.user_id, sysdate,
fnd_global.user_id, SYSDATE,
detail_query_id,PRIORITY
FROM MSC_PQ_RESULTS
WHERE query_id = l_temp_query_id
AND plan_id = p_plan_id
AND ((detail_query_id <> query_id
AND detail_query_id = l_detail_query_id)
OR detail_query_id = query_id)
GROUP BY query_id, plan_id, sr_instance_id,
organization_id, exception_type,
source_type, sequence_id,
groupby_org, INVENTORY_ITEM_ID,
CATEGORY_ID, PLANNER_CODE,
CUSTOMER_iD, SUPPLIER_ID,
CUSTOMER_SITE_ID, SUPPLIER_SITE_ID,
detail_query_id,PRIORITY;
select plan_type
into v_plan_type
from msc_plans
where plan_id = p_plan_id;
l_sql_stmt := l_insert_begin||'source_type , sequence_id, '|| l_excp_cols || l_group_by_cols1 ||l_insert_end||
l_select_begin||l_detail_col||l_source_type||','||l_seq_id||','||l_excp_cols || l_group_by_cols2 ||l_who_cols||
' FROM '||l_view||
' where plan_id = '||p_plan_id||l_cp_context||' and '||p_where_clause
||' and ( source_type = 2 and exception_type = '||l_object||')';
l_sql_stmt := l_insert_begin || 'source_type , sequence_id, '||
l_excp_cols || l_new_group_by_cols1||
l_group_by_cols1 || l_insert_end||
l_select_begin || l_detail_col||
l_source_type || ','||l_seq_id||','||
l_excp_cols || l_new_group_by_cols2||
l_group_by_cols2 || l_who_cols||
' FROM '||l_view|| ' where plan_id = '||
p_plan_id||' and '||p_where_clause||
' and ( source_type = 1 and exception_type = '||l_object||')';
INSERT INTO MSC_PQ_RESULTS (
query_id,
plan_id,
sr_instance_id,
organization_id,
exception_type,
source_type,
summary_data,
sequence_id,
exception_count,
groupby_org, groupby_item, groupby_supply_item, groupby_category,
groupby_planner, groupby_dept, groupby_res, groupby_customer, groupby_supplier,
char1,
char2,
char3,
char4,
char5,
created_by,
creation_date,
last_updated_by,
last_update_date)
SELECT l_temp_query_id, --p_query_id
nvl(p_plan_id,-1),
-1,
-1,
exception_type,
source_type,
1,
sequence_id,
count(*),
groupby_org, groupby_item, groupby_supply_item, groupby_category,
groupby_planner, groupby_dept, groupby_res, groupby_customer, groupby_supplier,
char1,
char2,
char3,
char4,
char5,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate
FROM MSC_PQ_RESULTS
WHERE query_id = l_temp_query_id --p_query_id
and plan_id = p_plan_id
GROUP BY query_id, plan_id, source_type,exception_type,sequence_id,
groupby_org, groupby_item, groupby_supply_item, groupby_category,
groupby_planner, groupby_dept, groupby_res, groupby_customer, groupby_supplier,
char1, char2, char3, char4, char5;
l_sql_stmt := l_insert_begin||l_item_cols||l_insert_end_new||
l_select_begin||l_summary_col||' inventory_item_id , ' || p_master_query_id||' , ' ||
'NULL ' ||' , ' ||
p_sequence_id || ' , ' ||
l_who_cols||
' FROM '||p_item_attributes_view||
' where simulation_set_id is NULL and plan_id = '||p_plan_id||' and '||p_where_clause;
l_sql_stmt := l_insert_begin|| l_shipment_cols||l_insert_end||
l_select_begin||l_summary_col||l_shipment_cols||l_who_cols||
' FROM '||p_shipment_view||
' where plan_id = '||p_plan_id||' and '||p_where_clause ||
' GROUP BY PLAN_ID, SHIPMENT_ID, FROM_ORG_ID, FROM_ORG_INSTANCE_ID, '||
' TO_ORG_ID, TO_ORG_INSTANCE_ID, ORGANIZATION_ID, SR_INSTANCE_ID ';
update msc_personal_queries
set execute_flag = 1,
EXECUTION_DATE = TRUNC(SYSDATE)
where query_id = l_temp_query_id ;
procedure update_category( ERRBUF OUT NOCOPY VARCHAR2,
RETCODE OUT NOCOPY NUMBER,
p_query_id IN NUMBER) IS
cursor item_exist(p_item number, p_org number, p_inst number) IS
select 1
from msc_item_attributes
where simulation_set_id = -1
and plan_id = -1
and inventory_item_id = p_item
and organization_id = p_org
and sr_instance_id = p_inst;
select category_id
from msc_pq_results
where query_id = p_query_id
and inventory_item_id = p_item
and organization_id = p_org
and sr_instance_id = p_inst
and sequence_id = p_seq;
select inventory_item_id, organization_id, sr_instance_id, max(sequence_id)
bulk collect into v_item_id, v_org_id, v_inst_id, v_seq
from msc_pq_results
where query_id = p_query_id
group by inventory_item_id, organization_id, sr_instance_id;
update msc_item_attributes
set criticality_category = p_cat
where inventory_item_id = v_item_id(j)
and organization_id = v_org_id(j)
and sr_instance_id = v_inst_id(j)
and simulation_set_id = -1
and plan_id = -1;
insert into msc_item_attributes(simulation_set_id,
inventory_item_id, organization_id,
sr_instance_id, last_update_date,
last_updated_by, creation_date, created_by,
criticality_category, plan_id, updated_columns_count
) values
( -1 , v_item_id(j), v_org_id(j),
v_inst_id(j), sysdate , -1,
sysdate, -1, p_cat, -1, 1);
end update_category;
select user_name
into l_name
from fnd_user
where user_id = p_user_id;
select query_name
into l_name
from msc_personal_queries
where query_id = p_query_id;
select query_type
into l_name
from msc_personal_queries
where query_id = p_query_id;
insert into msc_query (query_id, number1,
LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY)
select p_query_id, number1 ,
sysdate, -1, sysdate, -1
from msc_form_query
where query_id = p_query_id;
select QUERY_ID, QUERY_NAME, DESCRIPTION,
QUERY_TYPE, PUBLIC_FLAG, AND_OR_FLAG, EXECUTE_FLAG,
CREATED_BY, CREATION_DATE, LAST_UPDATE_DATE,
LAST_UPDATED_BY, LAST_UPDATE_LOGIN, PLAN_TYPE,
AUTO_RELEASE,GROUP_ID,UPDATED_FLAG
from msc_personal_queries
where query_id = p_query;
select QUERY_ID, SOURCE_TYPE, OBJECT_TYPE,
SEQUENCE_ID, AND_OR_FLAG, CREATION_DATE,
LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
CUSTOMIZED_TEXT, OBJECT_TYPE_TEXT, ACTIVE_FLAG,
DETAIL_QUERY_ID,FREQUENCY,PRIORITY
from msc_pq_types
where query_id = p_query;
select SEQUENCE, FOLDER_ID, OBJECT_SEQUENCE_ID,
FIELD_NAME, FIELD_TYPE,
HIDDEN_FROM_FIELD, CONDITION,
FROM_FIELD, TO_FIELD, FOLDER_OBJECT,
TREE_NODE, CREATION_DATE, CREATED_BY,
LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
AND_OR, COUNT_BY, SEARCH_QUERY_ID,
SEARCH_QUERY_NAME, DEFAULT_FLAG,
PUBLIC_FLAG, FROM_FIELD_VALUE, TO_FIELD_VALUE,
OBJECT_TYPE, SOURCE_TYPE, ACTIVE_FLAG
from msc_selection_criteria
where folder_id = p_query;
select SEQUENCE,FIELD_NAME, OR_VALUES,
HIDDEN_VALUES,OBJECT_SEQUENCE,ORDER_BY_SEQUENCE,
CREATION_DATE, CREATED_BY,
LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN
from msc_among_values
where folder_id = p_query_id;
cursor c_query is select msc_personal_queries_s.nextval from dual;
SELECT distinct ATTRIBUTE_NAME group_by
FROM MSC_WORKLIST_GROUPBY
WHERE QUERY_ID = p_query_id;
INSERT INTO msc_personal_queries( QUERY_ID, QUERY_NAME, DESCRIPTION,
QUERY_TYPE, PUBLIC_FLAG, AND_OR_FLAG, EXECUTE_FLAG,
CREATED_BY, CREATION_DATE, LAST_UPDATE_DATE,
LAST_UPDATED_BY, LAST_UPDATE_LOGIN, PLAN_TYPE,
AUTO_RELEASE,GROUP_ID,UPDATED_FLAG)
VALUES (l_save_as_query_id, p_query_name, p_query_desc,
rec_pers_queries.QUERY_TYPE, p_public_flag, rec_pers_queries.AND_OR_FLAG,
2, rec_pers_queries.CREATED_BY, rec_pers_queries.CREATION_DATE,
rec_pers_queries.LAST_UPDATE_DATE, rec_pers_queries.LAST_UPDATED_BY,
rec_pers_queries.LAST_UPDATE_LOGIN, rec_pers_queries.PLAN_TYPE,
rec_pers_queries.AUTO_RELEASE,rec_pers_queries.GROUP_ID,rec_pers_queries.UPDATED_FLAG);
INSERT INTO MSC_PQ_TYPES (QUERY_ID, SOURCE_TYPE, OBJECT_TYPE,
SEQUENCE_ID, AND_OR_FLAG, CREATION_DATE,
LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
CUSTOMIZED_TEXT, OBJECT_TYPE_TEXT, ACTIVE_FLAG,
DETAIL_QUERY_ID,FREQUENCY,PRIORITY)
VALUES (l_save_as_query_id, rec_pq_types.SOURCE_TYPE, rec_pq_types.OBJECT_TYPE,
rec_pq_types.SEQUENCE_ID, rec_pq_types.AND_OR_FLAG,
rec_pq_types.CREATION_DATE, rec_pq_types.LAST_UPDATE_DATE,
rec_pq_types.LAST_UPDATED_BY, rec_pq_types.LAST_UPDATE_LOGIN,
rec_pq_types.CUSTOMIZED_TEXT, rec_pq_types.OBJECT_TYPE_TEXT, rec_pq_types.ACTIVE_FLAG,
rec_pq_types.DETAIL_QUERY_ID,rec_pq_types.FREQUENCY,rec_pq_types.PRIORITY);
INSERT INTO msc_selection_criteria
(SEQUENCE, FOLDER_ID, OBJECT_SEQUENCE_ID, FIELD_NAME, FIELD_TYPE,
HIDDEN_FROM_FIELD, CONDITION,
FROM_FIELD, TO_FIELD, FOLDER_OBJECT,
TREE_NODE, CREATION_DATE, CREATED_BY,
LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
AND_OR, COUNT_BY, SEARCH_QUERY_ID,
SEARCH_QUERY_NAME, DEFAULT_FLAG,
PUBLIC_FLAG, FROM_FIELD_VALUE, TO_FIELD_VALUE,
OBJECT_TYPE, SOURCE_TYPE, ACTIVE_FLAG)
VALUES (rec_pers_criteria.SEQUENCE, l_save_as_query_id,
rec_pers_criteria.OBJECT_SEQUENCE_ID ,
rec_pers_criteria.FIELD_NAME, rec_pers_criteria.FIELD_TYPE,
rec_pers_criteria.HIDDEN_FROM_FIELD, rec_pers_criteria.CONDITION,
rec_pers_criteria.FROM_FIELD, rec_pers_criteria.TO_FIELD,
rec_pers_criteria.FOLDER_OBJECT, rec_pers_criteria.TREE_NODE,
rec_pers_criteria.CREATION_DATE, rec_pers_criteria.CREATED_BY,
rec_pers_criteria.LAST_UPDATE_DATE, rec_pers_criteria.LAST_UPDATED_BY,
rec_pers_criteria.LAST_UPDATE_LOGIN, rec_pers_criteria.AND_OR,
rec_pers_criteria.COUNT_BY, rec_pers_criteria.SEARCH_QUERY_ID,
rec_pers_criteria.SEARCH_QUERY_NAME, rec_pers_criteria.DEFAULT_FLAG,
rec_pers_criteria.PUBLIC_FLAG, rec_pers_criteria.FROM_FIELD_VALUE,
rec_pers_criteria.TO_FIELD_VALUE,rec_pers_criteria.OBJECT_TYPE,
rec_pers_criteria.SOURCE_TYPE, rec_pers_criteria.ACTIVE_FLAG) ;
INSERT INTO msc_among_values
(FOLDER_ID,SEQUENCE,FIELD_NAME, OR_VALUES,
HIDDEN_VALUES,OBJECT_SEQUENCE,ORDER_BY_SEQUENCE,
CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE,
LAST_UPDATED_BY, LAST_UPDATE_LOGIN)
VALUES(l_save_as_query_id, rec_among_criteria.SEQUENCE,
rec_among_criteria.FIELD_NAME,
rec_among_criteria.OR_VALUES,
rec_among_criteria.HIDDEN_VALUES,
rec_among_criteria.OBJECT_SEQUENCE,
rec_among_criteria.ORDER_BY_SEQUENCE,
rec_among_criteria.CREATION_DATE,
rec_among_criteria.CREATED_BY,
rec_among_criteria.LAST_UPDATE_DATE,
rec_among_criteria.LAST_UPDATED_BY,
rec_among_criteria.LAST_UPDATE_LOGIN);
INSERT INTO MSC_WORKLIST_GROUPBY(
QUERY_ID ,
ATTRIBUTE_NAME ,
CHAR1 ,
CHAR2 ,
CHAR3 ,
CHAR4 ,
SEQUENCE ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY )
SELECT l_save_as_query_id ,
ATTRIBUTE_NAME ,
CHAR1 ,
CHAR2 ,
CHAR3 ,
CHAR4 ,
SEQUENCE ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY
FROM MSC_WORKLIST_GROUPBY
WHERE query_id = p_query_id;
SELECT count(*) into l_count
from ALL_TAB_PARTITIONS
where TABLE_NAME = 'MSC_PQ_RESULTS' and TABLE_OWNER= l_msc_schema;
delete from msc_pq_results
where plan_id = p_plan_id;
procedure delete_query(p_query_id in number,
p_query_name in varchar2 default null) is
PRAGMA AUTONOMOUS_TRANSACTION;
delete from msc_personal_queries
where query_id = p_query_id;
delete from msc_pq_types
where query_id = p_query_id;
delete from msc_selection_criteria
where folder_id = p_query_id;
delete from msc_among_values
where folder_id = p_query_id;
delete from msc_pq_results
where query_id = p_query_id;
end delete_query;
l_Insert_stmt varchar2(32000);
l_insert_begin varchar2(1000) := 'INSERT INTO MSC_PQ_RESULTS ('||
' QUERY_ID, PLAN_ID, ORGANIZATION_ID,'||
' SR_INSTANCE_ID, SUMMARY_DATA,';
l_insert_end_new varchar2(1000) := 'sequence_id, created_by, creation_date,'||
' last_update_date, last_updated_by,'||
' last_update_login ) ';
l_sql_stmt := ' Select mpr1.query_id, mpr1.plan_id,'||
'mpr1.sr_instance_id, mpr1.organization_id,'||
'sum(mpr1.exception_count) exception_count,'||
'MIN(mpr1.priority) priority,'||1||
l_new_group_by_cols3||
fnd_global.user_id||', SYSDATE, '||
fnd_global.user_id||', SYSDATE'||
' from ( select mpr.query_id, mpr.plan_id,'||
'mpr.sr_instance_id, mpr.organization_id,'||
'nvl(mpr.exception_count,1) exception_count,'||
'mpr.priority'||
l_new_group_by_cols1||
'9999'||
' FROM msc_pq_results mpr '||
' WHERE mpr.query_id = '||p_query_id ||
' AND mpr.plan_id = '||p_plan_id ||
' AND mpr.summary_data = -99 ) mpr1 '||
l_new_group_by_cols4;
l_Insert_stmt:= 'Insert into MSC_PQ_RESULTS ( '||
'query_id, plan_id,'||
'sr_instance_id, organization_id,'||
'exception_count, priority, summary_data'||
l_new_group_by_cols2||
'created_by, creation_date, '||
'last_updated_by,last_update_date)';
msc_get_name.execute_dsql(l_Insert_stmt||l_sql_stmt);
SELECT distinct ATTRIBUTE_NAME group_by
FROM MSC_WORKLIST_GROUPBY
WHERE QUERY_ID = l_query;