[Home] [Help]
12: procedure delete_from_results_table(p_query_id in number,
13: p_plan_id in number) is
14: BEGIN
15: --KSA_DEBUG(SYSDATE,' p_query_id <> '||p_query_id,'delete_from_results_table');
16: delete msc_pq_results
17: where query_id = p_query_id
18: and plan_id = p_plan_id ;
19: end delete_from_results_table;
20:
21: PROCEDURE delete_from_results_table(p_query_id IN NUMBER,
22: p_plan_id IN NUMBER,
23: p_detail_query_id IN NUMBER) IS
24: BEGIN
25: DELETE msc_pq_results
26: WHERE query_id = p_query_id
27: AND plan_id = p_plan_id
28: AND detail_query_id = p_detail_query_id;
29: END delete_from_results_table;
36: P_MASTER_QUERY_ID in NUMBER DEFAULT NULL,
37: p_sequence_id in NUMBER DEFAULT NULL) is
38: l_sql_stmt varchar2(32000);
39:
40: l_insert_begin varchar2(1000) := 'INSERT INTO MSC_PQ_RESULTS ('
41: || ' QUERY_ID, PLAN_ID, ORGANIZATION_ID, SR_INSTANCE_ID, SUMMARY_DATA,';
42: l_insert_end varchar2(1000) := 'created_by, creation_date, last_update_date, '||
43: ' last_updated_by, last_update_login) ';
44:
311:
312: end loop;
313: close c_excp_types;
314:
315: INSERT INTO MSC_PQ_RESULTS(query_id,plan_id,sr_instance_id,
316: organization_id, exception_type,
317: source_type, summary_data, sequence_id,
318: exception_count, groupby_org,
319: INVENTORY_ITEM_ID, --groupby_supply_item,
333: CUSTOMER_SITE_ID, SUPPLIER_SITE_ID,
334: fnd_global.user_id, sysdate,
335: fnd_global.user_id, SYSDATE,
336: detail_query_id,PRIORITY
337: FROM MSC_PQ_RESULTS
338: WHERE query_id = l_temp_query_id
339: AND plan_id = p_plan_id
340: AND ((detail_query_id <> query_id
341: AND detail_query_id = l_detail_query_id)
466:
467: end loop;
468: close c_excp_types;
469:
470: INSERT INTO MSC_PQ_RESULTS (
471: query_id,
472: plan_id,
473: sr_instance_id,
474: organization_id,
507: fnd_global.user_id,
508: sysdate,
509: fnd_global.user_id,
510: sysdate
511: FROM MSC_PQ_RESULTS
512: WHERE query_id = l_temp_query_id --p_query_id
513: and plan_id = p_plan_id
514: GROUP BY query_id, plan_id, source_type,exception_type,sequence_id,
515: groupby_org, groupby_item, groupby_supply_item, groupby_category,
566: and sr_instance_id = p_inst;
567:
568: cursor cat_id(p_item number, p_seq number , p_org number, p_inst number, p_query_id number) IS
569: select category_id
570: from msc_pq_results
571: where query_id = p_query_id
572: and inventory_item_id = p_item
573: and organization_id = p_org
574: and sr_instance_id = p_inst
590: -- up overlapping items.
591:
592: select inventory_item_id, organization_id, sr_instance_id, max(sequence_id)
593: bulk collect into v_item_id, v_org_id, v_inst_id, v_seq
594: from msc_pq_results
595: where query_id = p_query_id
596: group by inventory_item_id, organization_id, sr_instance_id;
597:
598:
863: end if;
864:
865: SELECT count(*) into l_count
866: from ALL_TAB_PARTITIONS
867: where TABLE_NAME = 'MSC_PQ_RESULTS' and TABLE_OWNER= l_msc_schema;
868:
869: l_sql_stmt :=
870: 'alter table '||l_msc_schema||'.msc_pq_results'||
871: ' truncate partition PQ_RESULTS_'||to_char(p_plan_id);
866: from ALL_TAB_PARTITIONS
867: where TABLE_NAME = 'MSC_PQ_RESULTS' and TABLE_OWNER= l_msc_schema;
868:
869: l_sql_stmt :=
870: 'alter table '||l_msc_schema||'.msc_pq_results'||
871: ' truncate partition PQ_RESULTS_'||to_char(p_plan_id);
872: --5768202 partition name changed from pq_results_all_ to pq_results_
873:
874: if l_share_partition = 'N' and l_count >0 then
873:
874: if l_share_partition = 'N' and l_count >0 then
875: EXECUTE IMMEDIATE l_sql_stmt;
876: else
877: delete from msc_pq_results
878: where plan_id = p_plan_id;
879: end if;
880: end purge_plan;
881:
894:
895: delete from msc_among_values
896: where folder_id = p_query_id;
897:
898: delete from msc_pq_results
899: where query_id = p_query_id;
900:
901: commit;
902: end delete_query;