[Home] [Help]
PACKAGE BODY: APPS.MSC_PERS_QUERIES
Source
1 package body MSC_pers_queries as
2 /* $Header: MSCPQB.pls 120.26 2007/10/10 23:24:41 cnazarma ship $ */
3
4 procedure put_line(p_msg in varchar2) is
5 begin
6 --dbms_output.put_line(p_msg);
7 --insert into msc_test values(p_msg);
8 --commit;
9 null;
10 end put_line;
11
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;
30
31 procedure populate_result_table(p_query_id in number,
32 p_query_type in number,
33 p_plan_id in number,
34 p_where_clause in varchar2,
35 p_execute_flag in BOOLEAN,
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
45 l_insert_end_new varchar2(1000) := 'sequence_id, created_by, creation_date, last_update_date, '||
46 ' last_updated_by, last_update_login ) ';
47
48 l_item_cols varchar2(1000) := 'INVENTORY_ITEM_ID, CATEGORY_ID,PLANNER_CODE,';
49 l_res_cols varchar2(1000) := 'DEPARTMENT_ID, RESOURCE_ID,RESOURCE_TYPE,';
50 l_supp_cols varchar2(1000) := 'SUPPLIER_ID, SUPPLIER_SITE_ID,INVENTORY_ITEM_ID,';
51 l_excp_cols varchar2(1000) := 'EXCEPTION_TYPE, EXCEPTION_ID,';
52 l_shipment_cols varchar2(1000) := 'SHIPMENT_ID, FROM_ORG_ID, FROM_ORG_INSTANCE_ID, '||
53 ' TO_ORG_ID, TO_ORG_INSTANCE_ID,';
54 l_Order_cols varchar2(1000) := 'TRANSACTION_ID,INVENTORY_ITEM_ID,SUPPLIER_ID,SUPPLIER_SITE_ID,CATEGORY_ID,PLANNER_CODE, ';
55 l_Order_cols1 varchar2(1000) := 'TRANSACTION_ID,INVENTORY_ITEM_ID,VENDOR_ID,VENDOR_SITE_ID,CATEGORY_ID,PLANNER_CODE, ';
56 l_summary_col constant varchar2(10) := '1,';
57 l_detail_col constant varchar2(10) := '2,';
58
59 l_select_begin varchar2(1000) :=' select distinct '||to_char(p_query_id)
60 ||', nvl(plan_id,-1), nvl(organization_id,-1), nvl(sr_instance_id,-1), ';
61
62 l_who_cols varchar2(1000) := fnd_global.user_id||',sysdate,sysdate,'
63 ||fnd_global.user_id||', null';
64 l_who_cols1 varchar2(1000);
65
66 l_cp_context varchar2(200) := 'and ( COMPANY_ID= SYS_CONTEXT(''MSC'',''COMPANY_ID'') OR OWNING_COMPANY_ID= SYS_CONTEXT(''MSC'',''COMPANY_ID'') ) ';
67 l_view varchar2(100);
68 l_source_type number;
69
70 CURSOR cur_priority(p_query_id NUMBER,p_detail_query_id NUMBER) IS
71 SELECT NVL(priority,999)
72 FROM msc_pq_types
73 WHERE query_id= p_query_id
74 AND NVL(detail_query_id,query_id) = p_detail_query_id;
75
76 cursor c_groupby (l_query_id in number,
77 l_source_type in number, l_object_type in number) is
78 select group_by, sequence
79 from msc_selection_criteria_v
80 where folder_id = l_query_id
81 and nvl(source_type, -1) = nvl(l_source_type, -1)
82 and nvl(object_type, -1) = nvl(l_object_type, -1)
83 and nvl(count_by, 2) = 1
84 and nvl(active_flag,2) = 1
85 order by sequence;
86
87 CURSOR c_wl_groupby(l_query IN NUMBER) IS
88 SELECT distinct ATTRIBUTE_NAME group_by
89 FROM MSC_WORKLIST_GROUPBY
90 WHERE QUERY_ID = l_query;
91
92 cursor c_excp_types (l_query_id number) is
93 select source_type, object_type, sequence_id,NVL(priority,999), frequency
94 from msc_pq_types
95 where query_id = l_query_id;
96
97 CURSOR c_frequency(p_query_id NUMBER, p_detail_query_id IN NUMBER) is
98 SELECT frequency
99 FROM msc_pq_types
100 WHERE query_id = p_query_id
101 AND detail_query_id = p_detail_query_id;
102
103 l_group_by varchar2(30);
104 l_seq number;
105 l_seq_id number; -- sequence_id in msc_pq_types
106
107 l_source number;
108 l_object number;
109
110 l_group_by_cols1 varchar2(100);
111 l_group_by_cols2 varchar2(300);
112 l_new_group_by_cols1 varchar2(300);
113 l_new_group_by_cols2 varchar2(300);
114 l_new_group_by_col1 varchar2(300);
115 l_new_group_by_col2 varchar2(300);
116 l_new_group_by_col boolean := false;
117 l_worklist_cols VARCHAR2(300);
118 l_group_by_count number;
119 v_plan_type number;
120 l_temp_query_id NUMBER;
121 l_detail_query_id NUMBER;
122 l_temp_query_type NUMBER;
123 l_priority NUMBER;
124 l_priority_wl NUMBER;
125 l_frequency NUMBER;
126 l_frequency_wl NUMBER;
127 l_check_frequency VARCHAR2(500);
128 begin
129 if (p_query_type <> 12 ) then
130 IF p_MASTER_QUERY_ID IS NOT NULL THEN
131 l_select_begin :=' select distinct '||to_char(p_MASTER_QUERY_ID)
132 ||', nvl(plan_id,-1), nvl(organization_id,-1), nvl(sr_instance_id,-1), ';
133 l_detail_query_id := p_query_id;
134 l_temp_query_id := p_MASTER_QUERY_ID;
135 OPEN cur_priority(p_MASTER_QUERY_ID,l_detail_query_id);
136 FETCH cur_priority INTO l_priority;
137 CLOSE cur_priority;
138 l_insert_end := 'created_by, creation_date, last_update_date, '||
139 ' last_updated_by, last_update_login , detail_query_id,PRIORITY ) ';
140 l_who_cols1 := l_who_cols;
141 l_who_cols := l_who_cols||','||TO_CHAR(l_detail_query_id)||','||TO_CHAR(l_PRIORITY)||' ';
142
143 -- delete_from_results_table(l_temp_query_id, p_plan_id, l_detail_query_id);
144 l_temp_query_type := p_wl_type;
145 ELSE
146 l_temp_query_id := p_query_id;
147 l_temp_query_type := P_QUERY_TYPE;
148 delete_from_results_table(p_query_id, p_plan_id);
149 END IF;
150 end if;
151
152 if (p_query_type = p_item_type) then
153 --KSA_DEBUG(SYSDATE,' p_query_type <> '||p_query_type,'populate_result_table');
154 l_sql_stmt := l_insert_begin||l_item_cols||l_insert_end||
155 l_select_begin||l_summary_col||l_item_cols||l_who_cols||
156 ' FROM '||p_item_view||
157 ' where plan_id = '||p_plan_id||' and '||p_where_clause;
158 put_line(l_sql_stmt);
159 --KSA_DEBUG(SYSDATE,'*1* l_sql_stmt <> '||l_sql_stmt,'populate_result_table');
160 msc_get_name.execute_dsql(l_sql_stmt);
161 elsif (p_query_type = p_res_type) then
162 l_sql_stmt := l_insert_begin||l_res_cols||l_insert_end||
163 l_select_begin||l_summary_col||l_res_cols||l_who_cols||
164 ' FROM '||p_res_view||
165 ' where plan_id = '||p_plan_id||' and '||p_where_clause;
166 put_line(l_sql_stmt);
167 msc_get_name.execute_dsql(l_sql_stmt);
168 elsif (p_query_type = p_supp_type) then
169 l_sql_stmt := l_insert_begin||l_supp_cols||l_insert_end||
170 l_select_begin||l_summary_col||l_supp_cols||l_who_cols||
171 ' FROM '||p_supp_view||
172 ' where plan_id = '||p_plan_id||' and '||p_where_clause;
173 put_line(l_sql_stmt);
174 msc_get_name.execute_dsql(l_sql_stmt);
175 ELSIF (p_query_type = p_order_type) then
176 l_sql_stmt := l_insert_begin||l_order_cols||l_insert_end||
177 l_select_begin||l_summary_col||l_Order_cols1||l_who_cols||
178 ' FROM '||p_order_view||
179 ' WHERE plan_id = '||p_plan_id||' AND '||p_where_clause;
180 --KSA_DEBUG(SYSDATE,'l_sql_stmt is '||l_sql_stmt,'populate_result_table');
181 put_line(l_sql_stmt);
182 msc_get_name.execute_dsql(l_sql_stmt);
183 elsif (p_query_type IN (p_excp_type,p_wl_type)) then
184 --KSA_DEBUG(SYSDATE,' p_query_type <> '||p_query_type,'populate_result_table');
185 IF l_temp_query_type = p_wl_type THEN
186 IF p_query_type <> p_wl_type THEN
187 OPEN c_frequency(l_temp_query_id,l_detail_query_id);
188 FETCH c_frequency INTO l_frequency;
189 IF c_frequency%FOUND AND l_frequency IS NOT NULL THEN
190 --l_check_frequency := ' trunc(sysdate) > (nvl(ACTION_TAKEN_DATE,(SYSDATE-1)) + '||l_frequency||')';
191 l_check_frequency := ' ACTION_TAKEN_DATE IS NULL OR '||
192 ' sysdate > (ACTION_TAKEN_DATE + '||l_frequency||')';
193 END IF;
194 CLOSE c_frequency;
195 END IF;
196 OPEN c_excp_types(p_query_id);
197 LOOP
198 FETCH c_excp_types INTO l_source, l_object, l_seq_id,l_priority_wl,l_frequency_wl;
199 EXIT WHEN c_excp_types%NOTFOUND;
200
201 l_group_by_count := 0;
202 l_new_group_by_cols1 := null;
203 l_new_group_by_cols2 := null;
204
205 IF p_query_type = p_wl_type AND l_frequency_wl IS NOT NULL THEN
206 --l_check_frequency := ' trunc(sysdate) > (nvl(ACTION_TAKEN_DATE,(SYSDATE-1)) + '||l_frequency_wl||')';
207 l_check_frequency := ' ACTION_TAKEN_DATE IS NULL OR '||
208 ' sysdate > (ACTION_TAKEN_DATE + '||l_frequency_wl||')';
209 END IF;
210 l_frequency_wl := NULL;
211
212 OPEN c_wl_groupby (l_temp_query_id) ;
213 LOOP
214 FETCH c_wl_groupby INTO l_group_by;
215 EXIT WHEN c_wl_groupby%NOTFOUND;
216 --3631530 bug fix
217 l_new_group_by_col := FALSE;
218 IF l_group_by = 'ORGANIZATION_CODE' THEN
219 l_new_group_by_col1 := l_group_by;
220 l_new_group_by_col2 := 'GROUPBY_ORG';
221 l_new_group_by_col := true;
222 ELSIF l_group_by = 'ITEM_SEGMENTS' THEN
223 l_new_group_by_col1 := 'INVENTORY_ITEM_ID';
224 l_new_group_by_col2 := 'INVENTORY_ITEM_ID';
225 l_new_group_by_col := TRUE;
226 ELSIF l_group_by = 'CATEGORY_NAME' THEN
227 l_new_group_by_col1 := 'CATEGORY_ID';
228 l_new_group_by_col2 := 'CATEGORY_ID';
229 l_new_group_by_col := TRUE;
230 ELSIF l_group_by = 'PLANNER_CODE' THEN
231 l_new_group_by_col1 := 'PLANNER_CODE';
232 l_new_group_by_col2 := 'PLANNER_CODE';
233 l_new_group_by_col := TRUE;
234 ELSIF l_group_by = 'CUSTOMER_NAME' THEN
235 l_new_group_by_col1 := 'CUSTOMER_ID';
236 l_new_group_by_col2 := 'CUSTOMER_ID';
237 l_new_group_by_col := TRUE;
238 ELSIF l_group_by = 'CUSTOMER_SITE' THEN
239 l_new_group_by_col1 := 'CUSTOMER_SITE_ID';
240 l_new_group_by_col2 := 'CUSTOMER_SITE_ID';
241 l_new_group_by_col := TRUE;
242 ELSIF l_group_by = 'SUPPLIER_NAME' THEN
243 l_new_group_by_col1 := 'SUPPLIER_ID';
244 l_new_group_by_col2 := 'SUPPLIER_ID';
245 l_new_group_by_col := TRUE;
246 ELSIF l_group_by = 'EXCEPTION_TYPE' THEN
247 /*l_new_group_by_col1 := 'EXCEPTION_TYPE';
248 l_new_group_by_col2 := 'EXCEPTION_TYPE';*/
249 -- Not REQUIRED as this field will be there in the group by.
250 l_new_group_by_col := FALSE;
251 ELSIF l_group_by = 'SUPPLIER_SITE' THEN
252 l_new_group_by_col1 := 'SUPPLIER_SITE_ID';
253 l_new_group_by_col2 := 'SUPPLIER_SITE_ID';
254 l_new_group_by_col := TRUE;
255 END IF;
256 IF (l_new_group_by_col) then
257 if l_new_group_by_cols1 is null then
258 l_new_group_by_cols1 := l_new_group_by_col2||', ';
259 l_new_group_by_cols2 := l_new_group_by_col1||', ';
260 else
261 l_new_group_by_cols1 := l_new_group_by_cols1||l_new_group_by_col2||', ';
262 l_new_group_by_cols2 := l_new_group_by_cols2||l_new_group_by_col1||', ';
263 end if;
264 end if;
265 end loop;
266 close c_wl_groupby;
267
268 select plan_type
269 into v_plan_type
270 from msc_plans
271 where plan_id = p_plan_id;
272
273 if p_plan_id = -1 then
274 l_view := p_cp_excp_view;
275 l_source_type := 2;
276 l_sql_stmt := l_insert_begin||'source_type ,'|| l_excp_cols || l_insert_end||
277 l_select_begin||l_detail_col||l_source_type||','||l_excp_cols || l_who_cols||
278 ' FROM '||l_view||
279 ' where plan_id = '||p_plan_id||l_cp_context||' and '||p_where_clause
280 ||' and ( source_type = 2 and exception_type = '||l_object||')';
281 else
282 if v_plan_type IN ( 5,8,9) then
283 l_view := 'MSC_DRP_EXC_DETAILS_V';
284 else -- v_plan_type <> 5
285 l_view := p_excp_view;
286 end if;
287 l_source_type := 1;
288 IF p_query_type = p_wl_type THEN
289 l_who_cols := l_who_cols1||','||TO_CHAR(l_detail_query_id)||','||TO_CHAR(l_priority_wl)||' ';
290 END IF;
291 --l_worklist_cols := ' INVENTORY_ITEM_ID,SUPPLIER_ID,SUPPLIER_SITE_ID,CUSTOMER_ID,CUSTOMER_SITE_ID,CATEGORY_ID,PLANNER_CODE, ';
292 l_sql_stmt := l_insert_begin||'source_type , sequence_id, '||
293 l_excp_cols ||l_new_group_by_cols1|| --l_worklist_cols|| --l_new_group_by_cols1||
294 l_insert_end||
295 l_select_begin||l_detail_col||
296 l_source_type||','||l_seq_id||','||
297 l_excp_cols ||l_new_group_by_cols2|| --l_worklist_cols|| --l_new_group_by_cols2||
298 l_who_cols||
299 ' FROM '||l_view||' where plan_id = '||p_plan_id||
300 ' and '||p_where_clause||
301 ' and ( source_type = 1 and exception_type = '||
302 l_object||')';
303 IF l_check_frequency IS NOT NULL THEN
304 l_sql_stmt := l_sql_stmt||' and '||l_check_frequency;
305 l_check_frequency:= NULL;
306 END IF;
307 end if;
308 put_line(l_sql_stmt);
309 --KSA_DEBUG(SYSDATE,'*1* l_sql_stmt <> '||l_sql_stmt,'populate_result_table');
310 msc_get_name.execute_dsql(l_sql_stmt);
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,
320 CATEGORY_ID, PLANNER_CODE,
321 CUSTOMER_iD, SUPPLIER_ID,
322 CUSTOMER_SITE_ID, SUPPLIER_SITE_ID,
323 created_by,creation_date,
324 last_updated_by, last_update_date,
325 detail_query_id,Priority)
326 SELECT l_temp_query_id, NVL(p_plan_id,-1), sr_instance_id,
327 organization_id, exception_type,
328 source_type, 1, sequence_id,
329 COUNT(*), groupby_org,
330 INVENTORY_ITEM_ID, --groupby_supply_item,
331 CATEGORY_ID, PLANNER_CODE,
332 CUSTOMER_iD, SUPPLIER_ID,
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)
342 OR detail_query_id = query_id)
343 GROUP BY query_id, plan_id, sr_instance_id,
344 organization_id, exception_type,
345 source_type, sequence_id,
346 groupby_org, INVENTORY_ITEM_ID,
347 CATEGORY_ID, PLANNER_CODE,
348 CUSTOMER_iD, SUPPLIER_ID,
349 CUSTOMER_SITE_ID, SUPPLIER_SITE_ID,
350 detail_query_id,PRIORITY;
351 ELSE
352 --(begin if not part of worklist
353
354 open c_excp_types(p_query_id);
355 loop
356 fetch c_excp_types into l_source, l_object, l_seq_id,l_priority_wl,l_frequency_wl;
357 exit when c_excp_types%notfound;
358
359 l_group_by_count := 0;
360 l_group_by_cols1 := null;
361 l_group_by_cols2 := null;
362 l_new_group_by_cols1 := null;
363 l_new_group_by_cols2 := null;
364 open c_groupby (p_query_id, l_source, l_object) ;
365 loop
366 fetch c_groupby into l_group_by, l_seq;
367 exit when c_groupby%notfound;
368 --3631530 bug fix
369 l_new_group_by_col := false;
370 if (l_group_by = 'ORGANIZATION_CODE') then
371 l_new_group_by_col1 := l_group_by;
372 l_new_group_by_col2 := 'GROUPBY_ORG';
373 l_new_group_by_col := true;
374 elsif (l_group_by = 'ITEM_SEGMENTS') then
375 l_new_group_by_col1 := l_group_by;
376 l_new_group_by_col2 := 'GROUPBY_ITEM';
377 l_new_group_by_col := true;
378 elsif (l_group_by = 'SUPPLY_ITEM_SEGMENTS') then
379 l_new_group_by_col1 := l_group_by;
380 l_new_group_by_col2 := 'GROUPBY_SUPPLY_ITEM';
381 l_new_group_by_col := true;
382 elsif (l_group_by = 'CATEGORY_NAME') then
383 l_new_group_by_col1 := l_group_by;
384 l_new_group_by_col2 := 'GROUPBY_CATEGORY';
385 l_new_group_by_col := true;
386 elsif (l_group_by = 'PLANNER_CODE') then
387 l_new_group_by_col1 := l_group_by;
388 l_new_group_by_col2 := 'GROUPBY_PLANNER';
389 l_new_group_by_col := true;
390 elsif (l_group_by = 'DEPARTMENT_LINE_CODE') then
391 l_new_group_by_col1 := l_group_by;
392 l_new_group_by_col2 := 'GROUPBY_DEPT';
393 l_new_group_by_col := true;
394 elsif (l_group_by = 'RESOURCE_CODE') then
395 l_new_group_by_col1 := l_group_by;
396 l_new_group_by_col2 := 'GROUPBY_RES';
397 l_new_group_by_col := true;
398 elsif (l_group_by = 'CUSTOMER_NAME') then
399 l_new_group_by_col1 := l_group_by;
400 l_new_group_by_col2 := 'GROUPBY_CUSTOMER';
401 l_new_group_by_col := true;
402 elsif (l_group_by = 'SUPPLIER_NAME' ) then
403 l_new_group_by_col1 := l_group_by;
404 l_new_group_by_col2 := 'GROUPBY_SUPPLIER';
405 l_new_group_by_col := true;
406 else
407 l_group_by_count := l_group_by_count + 1;
408 if l_group_by_cols1 is null then
409 l_group_by_cols1 := ' CHAR'||to_char(l_group_by_count)||', ';
410 l_group_by_cols2 := ' '||l_group_by||', ';
411 elsif l_group_by_count <=5 then
412 l_group_by_cols1 := l_group_by_cols1||' CHAR'||to_char(l_group_by_count)||', ';
413 l_group_by_cols2 := l_group_by_cols2||l_group_by||',';
414 end if;
415 end if;
416 if (l_new_group_by_col) then
417 if l_new_group_by_cols1 is null then
418 l_new_group_by_cols1 := l_new_group_by_col2||', ';
419 l_new_group_by_cols2 := l_new_group_by_col1||', ';
420 else
421 l_new_group_by_cols1 := l_new_group_by_cols1||l_new_group_by_col2||', ';
422 l_new_group_by_cols2 := l_new_group_by_cols2||l_new_group_by_col1||', ';
423 end if;
424 end if;
425 end loop;
426 close c_groupby;
427
428 select plan_type
429 into v_plan_type
430 from msc_plans
431 where plan_id = p_plan_id;
432
433 if p_plan_id = -1 then
434 l_view := p_cp_excp_view;
435 l_source_type := 2;
436 l_sql_stmt := l_insert_begin||'source_type , sequence_id, '|| l_excp_cols || l_group_by_cols1 ||l_insert_end||
437 l_select_begin||l_detail_col||l_source_type||','||l_seq_id||','||l_excp_cols || l_group_by_cols2 ||l_who_cols||
438 ' FROM '||l_view||
439 ' where plan_id = '||p_plan_id||l_cp_context||' and '||p_where_clause
440 ||' and ( source_type = 2 and exception_type = '||l_object||')';
441
442 else
443 if v_plan_type IN (5,8,9) then
444 l_view := 'MSC_DRP_EXC_DETAILS_V';
445 else -- v_plan_type <> 5
446 l_view := p_excp_view;
447 end if;
448 l_source_type := 1;
449 l_sql_stmt := l_insert_begin || 'source_type , sequence_id, '||
450 l_excp_cols || l_new_group_by_cols1||
451 l_group_by_cols1 || l_insert_end||
452 l_select_begin || l_detail_col||
453 l_source_type || ','||l_seq_id||','||
454 l_excp_cols || l_new_group_by_cols2||
455 l_group_by_cols2 || l_who_cols||
456 ' FROM '||l_view|| ' where plan_id = '||
457 p_plan_id||' and '||p_where_clause||
458 ' and ( source_type = 1 and exception_type = '||l_object||')';
459 IF l_check_frequency IS NOT NULL THEN
460 l_sql_stmt := l_sql_stmt||' and '||l_check_frequency;
461 END IF;
462 end if;
463 put_line(l_sql_stmt);
464 --KSA_DEBUG(SYSDATE,'*2* l_sql_stmt <> '||l_sql_stmt,'populate_result_table');
465 msc_get_name.execute_dsql(l_sql_stmt);
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,
475 exception_type,
476 source_type,
477 summary_data,
478 sequence_id,
479 exception_count,
480 groupby_org, groupby_item, groupby_supply_item, groupby_category,
481 groupby_planner, groupby_dept, groupby_res, groupby_customer, groupby_supplier,
482 char1,
483 char2,
484 char3,
485 char4,
486 char5,
487 created_by,
488 creation_date,
489 last_updated_by,
490 last_update_date)
491 SELECT l_temp_query_id, --p_query_id
492 nvl(p_plan_id,-1),
493 -1,
494 -1,
495 exception_type,
496 source_type,
497 1,
498 sequence_id,
499 count(*),
500 groupby_org, groupby_item, groupby_supply_item, groupby_category,
501 groupby_planner, groupby_dept, groupby_res, groupby_customer, groupby_supplier,
502 char1,
503 char2,
504 char3,
505 char4,
506 char5,
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,
516 groupby_planner, groupby_dept, groupby_res, groupby_customer, groupby_supplier,
517 char1, char2, char3, char4, char5;
518 END IF;
519 --) end If not part of the worklist
520 elsif (p_query_type = p_crit_type) then
521 l_view := p_item_attributes_view;
522 l_cp_context := null;
523 l_source_type := 100;
524 l_sql_stmt := l_insert_begin||l_item_cols||l_insert_end_new||
525 l_select_begin||l_summary_col||' inventory_item_id , ' || p_master_query_id||' , ' ||
526 'NULL ' ||' , ' ||
527 p_sequence_id || ' , ' ||
528 l_who_cols||
529 ' FROM '||p_item_attributes_view||
530 ' where simulation_set_id is NULL and plan_id = '||p_plan_id||' and '||p_where_clause;
531
532 msc_get_name.execute_dsql(l_sql_stmt);
533
534 return;
535
536 elsif (p_query_type = p_shipment_type) then
537 l_sql_stmt := l_insert_begin|| l_shipment_cols||l_insert_end||
538 l_select_begin||l_summary_col||l_shipment_cols||l_who_cols||
539 ' FROM '||p_shipment_view||
540 ' where plan_id = '||p_plan_id||' and '||p_where_clause ||
541 ' GROUP BY PLAN_ID, SHIPMENT_ID, FROM_ORG_ID, FROM_ORG_INSTANCE_ID, '||
542 ' TO_ORG_ID, TO_ORG_INSTANCE_ID, ORGANIZATION_ID, SR_INSTANCE_ID ';
543 put_line(l_sql_stmt);
544 msc_get_name.execute_dsql(l_sql_stmt);
545 end if;
546
547
548 update msc_personal_queries
549 set execute_flag = 1,
550 EXECUTION_DATE = TRUNC(SYSDATE)
551 where query_id = l_temp_query_id ;
552 --where query_id = p_query_id ;
553 end populate_result_table;
554
555 procedure update_category( ERRBUF OUT NOCOPY VARCHAR2,
556 RETCODE OUT NOCOPY NUMBER,
557 p_query_id IN NUMBER) IS
558
559 cursor item_exist(p_item number, p_org number, p_inst number) IS
560 select 1
561 from msc_item_attributes
562 where simulation_set_id = -1
563 and plan_id = -1
564 and inventory_item_id = p_item
565 and organization_id = p_org
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
575 and sequence_id = p_seq;
576
577
578 type number_arr is table of NUMBER INDEX BY BINARY_INTEGER;
579 v_item_id number_arr;
580 v_cat_id number_arr;
581 v_seq number_arr;
582 v_org_id number_arr;
583 v_inst_id number_arr;
584 p_cat number;
585 p_exist number :=0;
586
587 begin
588
589 -- select the records with highest seq number so we do not pick
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
599 if v_item_id.COUNT > 0 then
600 for j IN v_item_id.FIRST .. v_item_id.LAST LOOP
601 open item_exist(v_item_id(j), v_org_id(j), v_inst_id(j));
602 fetch item_exist into p_exist;
603 close item_exist;
604
605 open cat_id(v_item_id(j), v_seq(j), v_org_id(j), v_inst_id(j), p_query_id);
606 fetch cat_id into p_cat;
607 close cat_id;
608
609 if p_exist = 1 then
610 update msc_item_attributes
611 set criticality_category = p_cat
612 where inventory_item_id = v_item_id(j)
613 and organization_id = v_org_id(j)
614 and sr_instance_id = v_inst_id(j)
615 and simulation_set_id = -1
616 and plan_id = -1;
617 else
618 insert into msc_item_attributes(simulation_set_id,
619 inventory_item_id, organization_id,
620 sr_instance_id, last_update_date,
621 last_updated_by, creation_date, created_by,
622 criticality_category, plan_id, updated_columns_count
623 ) values
624 ( -1 , v_item_id(j), v_org_id(j),
625 v_inst_id(j), sysdate , -1,
626 sysdate, -1, p_cat, -1, 1);
627 end if;
628 p_exist := 0;
629
630 END LOOP;
631 commit;
632 end if;
633
634 EXCEPTION
635 WHEN OTHERS THEN
636 RAISE;
637 end update_category;
638
639 function get_user(p_user_id in number) return varchar2 IS
640 l_name fnd_user.user_name%type;
641 begin
642 select user_name
643 into l_name
644 from fnd_user
645 where user_id = p_user_id;
646
647 return l_name;
648 exception
649 when others then
650 return null;
651 end get_user;
652
653 function get_query_name(p_query_id in number) return varchar2 is
654 l_name varchar2(80);
655 begin
656 select query_name
657 into l_name
658 from msc_personal_queries
659 where query_id = p_query_id;
660
661 return l_name;
662 exception
663 when others then
664 return null;
665 end get_query_name;
666
667 function get_query_type(p_query_id in number) return number is
668 l_name number;
669 begin
670 select query_type
671 into l_name
672 from msc_personal_queries
673 where query_id = p_query_id;
674
675 return l_name;
676 exception
677 when others then
678 return null;
679 end get_query_type;
680
681 procedure populate_cp_temp_table(p_query_id in number) is
682 -- PRAGMA AUTONOMOUS_TRANSACTION;
683 begin
684 insert into msc_query (query_id, number1,
685 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY)
686 select p_query_id, number1 ,
687 sysdate, -1, sysdate, -1
688 from msc_form_query
689 where query_id = p_query_id;
690
691 commit;
692 end populate_cp_temp_table;
693
694 FUNCTION copy_query(p_query_id in number,
695 p_query_name in varchar2,
696 p_query_desc in varchar2,
697 p_public_flag in number) return number is
698
699 cursor c_pers_queries (p_query in number) is
700 select QUERY_ID, QUERY_NAME, DESCRIPTION,
701 QUERY_TYPE, PUBLIC_FLAG, AND_OR_FLAG, EXECUTE_FLAG,
702 CREATED_BY, CREATION_DATE, LAST_UPDATE_DATE,
703 LAST_UPDATED_BY, LAST_UPDATE_LOGIN, PLAN_TYPE,
704 AUTO_RELEASE,GROUP_ID,UPDATED_FLAG
705 from msc_personal_queries
706 where query_id = p_query;
707
708 cursor c_pq_types (p_query in number) is
709 select QUERY_ID, SOURCE_TYPE, OBJECT_TYPE,
710 SEQUENCE_ID, AND_OR_FLAG, CREATION_DATE,
711 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
712 CUSTOMIZED_TEXT, OBJECT_TYPE_TEXT, ACTIVE_FLAG,
713 DETAIL_QUERY_ID,FREQUENCY,PRIORITY
714 from msc_pq_types
715 where query_id = p_query;
716
717 cursor c_pers_criteria (p_query in number) is
718 select SEQUENCE, FOLDER_ID, OBJECT_SEQUENCE_ID,
719 FIELD_NAME, FIELD_TYPE,
720 HIDDEN_FROM_FIELD, CONDITION,
721 FROM_FIELD, TO_FIELD, FOLDER_OBJECT,
722 TREE_NODE, CREATION_DATE, CREATED_BY,
723 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
724 AND_OR, COUNT_BY, SEARCH_QUERY_ID,
725 SEARCH_QUERY_NAME, DEFAULT_FLAG,
726 PUBLIC_FLAG, FROM_FIELD_VALUE, TO_FIELD_VALUE,
727 OBJECT_TYPE, SOURCE_TYPE, ACTIVE_FLAG
728 from msc_selection_criteria
729 where folder_id = p_query;
730
731 cursor c_query is select msc_personal_queries_s.nextval from dual;
732 --5746041 bugfix, changed from msc_form_query_s to msc_personal_queries_s
733
734 CURSOR c_group_by(p_query_id IN NUMBER) IS
735 SELECT distinct ATTRIBUTE_NAME group_by
736 FROM MSC_WORKLIST_GROUPBY
737 WHERE QUERY_ID = p_query_id;
738
739
740 l_save_as_query_id number;
741
742 rec_pers_queries c_pers_queries%ROWTYPE;
743 rec_pers_criteria c_pers_criteria%ROWTYPE;
744
745
746 begin
747 open c_query;
748 fetch c_query into l_save_as_query_id;
749 close c_query;
750
751 open c_pers_queries(p_query_id);
752 fetch c_pers_queries into rec_pers_queries;
753 close c_pers_queries;
754
755 if rec_pers_queries.query_type not in (7,8) then -- org/customer_list
756 l_save_as_query_id := -1 * l_save_as_query_id;
757 end if;
758
759 INSERT INTO msc_personal_queries( QUERY_ID, QUERY_NAME, DESCRIPTION,
760 QUERY_TYPE, PUBLIC_FLAG, AND_OR_FLAG, EXECUTE_FLAG,
761 CREATED_BY, CREATION_DATE, LAST_UPDATE_DATE,
762 LAST_UPDATED_BY, LAST_UPDATE_LOGIN, PLAN_TYPE,
763 AUTO_RELEASE,GROUP_ID,UPDATED_FLAG)
764 VALUES (l_save_as_query_id, p_query_name, p_query_desc,
765 rec_pers_queries.QUERY_TYPE, p_public_flag, rec_pers_queries.AND_OR_FLAG,
766 2, rec_pers_queries.CREATED_BY, rec_pers_queries.CREATION_DATE,
767 rec_pers_queries.LAST_UPDATE_DATE, rec_pers_queries.LAST_UPDATED_BY,
768 rec_pers_queries.LAST_UPDATE_LOGIN, rec_pers_queries.PLAN_TYPE,
769 rec_pers_queries.AUTO_RELEASE,rec_pers_queries.GROUP_ID,rec_pers_queries.UPDATED_FLAG);
770
771 for rec_pq_types in c_pq_types(p_query_id)
772 loop
773 INSERT INTO MSC_PQ_TYPES (QUERY_ID, SOURCE_TYPE, OBJECT_TYPE,
774 SEQUENCE_ID, AND_OR_FLAG, CREATION_DATE,
775 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
776 CUSTOMIZED_TEXT, OBJECT_TYPE_TEXT, ACTIVE_FLAG,
777 DETAIL_QUERY_ID,FREQUENCY,PRIORITY)
778 VALUES (l_save_as_query_id, rec_pq_types.SOURCE_TYPE, rec_pq_types.OBJECT_TYPE,
779 rec_pq_types.SEQUENCE_ID, rec_pq_types.AND_OR_FLAG,
780 rec_pq_types.CREATION_DATE, rec_pq_types.LAST_UPDATE_DATE,
781 rec_pq_types.LAST_UPDATED_BY, rec_pq_types.LAST_UPDATE_LOGIN,
782 rec_pq_types.CUSTOMIZED_TEXT, rec_pq_types.OBJECT_TYPE_TEXT, rec_pq_types.ACTIVE_FLAG,
783 rec_pq_types.DETAIL_QUERY_ID,rec_pq_types.FREQUENCY,rec_pq_types.PRIORITY);
784 end loop;
785
786 if rec_pers_queries.query_type in (7,8) then -- org/customer list
787 return l_save_as_query_id;
788 end if;
789
790 for rec_pers_criteria in c_pers_criteria(p_query_id)
791 loop
792 INSERT INTO msc_selection_criteria
793 (SEQUENCE, FOLDER_ID, OBJECT_SEQUENCE_ID, FIELD_NAME, FIELD_TYPE,
794 HIDDEN_FROM_FIELD, CONDITION,
795 FROM_FIELD, TO_FIELD, FOLDER_OBJECT,
796 TREE_NODE, CREATION_DATE, CREATED_BY,
797 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
798 AND_OR, COUNT_BY, SEARCH_QUERY_ID,
799 SEARCH_QUERY_NAME, DEFAULT_FLAG,
800 PUBLIC_FLAG, FROM_FIELD_VALUE, TO_FIELD_VALUE,
801 OBJECT_TYPE, SOURCE_TYPE, ACTIVE_FLAG)
802 VALUES (rec_pers_criteria.SEQUENCE, l_save_as_query_id,
803 rec_pers_criteria.OBJECT_SEQUENCE_ID ,
804 rec_pers_criteria.FIELD_NAME, rec_pers_criteria.FIELD_TYPE,
805 rec_pers_criteria.HIDDEN_FROM_FIELD, rec_pers_criteria.CONDITION,
806 rec_pers_criteria.FROM_FIELD, rec_pers_criteria.TO_FIELD,
807 rec_pers_criteria.FOLDER_OBJECT, rec_pers_criteria.TREE_NODE,
808 rec_pers_criteria.CREATION_DATE, rec_pers_criteria.CREATED_BY,
809 rec_pers_criteria.LAST_UPDATE_DATE, rec_pers_criteria.LAST_UPDATED_BY,
810 rec_pers_criteria.LAST_UPDATE_LOGIN, rec_pers_criteria.AND_OR,
811 rec_pers_criteria.COUNT_BY, rec_pers_criteria.SEARCH_QUERY_ID,
812 rec_pers_criteria.SEARCH_QUERY_NAME, rec_pers_criteria.DEFAULT_FLAG,
813 rec_pers_criteria.PUBLIC_FLAG, rec_pers_criteria.FROM_FIELD_VALUE,
814 rec_pers_criteria.TO_FIELD_VALUE,rec_pers_criteria.OBJECT_TYPE,
815 rec_pers_criteria.SOURCE_TYPE, rec_pers_criteria.ACTIVE_FLAG) ;
816 end loop;
817
818 INSERT INTO MSC_WORKLIST_GROUPBY(
819 QUERY_ID ,
820 ATTRIBUTE_NAME ,
821 CHAR1 ,
822 CHAR2 ,
823 CHAR3 ,
824 CHAR4 ,
825 SEQUENCE ,
826 LAST_UPDATE_DATE ,
827 LAST_UPDATED_BY ,
828 CREATION_DATE ,
829 CREATED_BY )
830 SELECT l_save_as_query_id ,
831 ATTRIBUTE_NAME ,
832 CHAR1 ,
833 CHAR2 ,
834 CHAR3 ,
835 CHAR4 ,
836 SEQUENCE ,
837 LAST_UPDATE_DATE ,
838 LAST_UPDATED_BY ,
839 CREATION_DATE ,
840 CREATED_BY
841 FROM MSC_WORKLIST_GROUPBY
842 WHERE query_id = p_query_id;
843 return l_save_as_query_id;
844 end copy_query;
845
846
847 Procedure purge_plan(p_plan_id IN NUMBER) is
848 l_sql_stmt varchar2(300);
849 l_share_partition varchar2(5):= fnd_profile.value('MSC_SHARE_PARTITIONS');
850 l_count number;
851
852 l_msc_schema varchar2(30);
853 l_status varchar2(50);
854 l_industry varchar2(50);
855 retval boolean;
856
857 begin
858 retval := fnd_installation.get_app_info_other('MSC', 'MSC',
859 l_status, l_industry, l_msc_schema);
860
861 if (not retval) then
862 return;
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);
872 --5768202 partition name changed from pq_results_all_ to pq_results_
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
882 procedure delete_query(p_query_id in number,
883 p_query_name in varchar2 default null) is
884 PRAGMA AUTONOMOUS_TRANSACTION;
885 begin
886 delete from msc_personal_queries
887 where query_id = p_query_id;
888
889 delete from msc_pq_types
890 where query_id = p_query_id;
891
892 delete from msc_selection_criteria
893 where folder_id = p_query_id;
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;
903
904 procedure save_as(p_query_id in number,
905 p_query_name in varchar2,
906 p_query_desc in varchar2,
907 p_public_flag in number) is
908 p_new_query_id number;
909 BEGIN
910 p_new_query_id :=
911 copy_query(p_query_id,
912 p_query_name,
913 p_query_desc,
914 p_public_flag);
915 END save_as;
916
917 FUNCTION save_as(p_query_id in number,
918 p_query_name in varchar2,
919 p_query_desc in varchar2,
920 p_public_flag in number) return number is
921 p_new_query_id number;
922 BEGIN
923 p_new_query_id :=
924 copy_query(p_query_id,
925 p_query_name,
926 p_query_desc,
927 p_public_flag);
928
929 return p_new_query_id;
930 END save_as;
931
932 end MSC_pers_queries;