[Home] [Help]
PACKAGE BODY: APPS.MSC_PERS_QUERIES
Source
1 package body MSC_pers_queries as
2 /* $Header: MSCPQB.pls 120.31 2010/12/22 00:06:32 pabram ship $ */
3
4 procedure get_wl_groupby(p_query_id number,
5 p_groupby_cols1 in out nocopy varchar2,
6 p_groupby_cols2 in out nocopy varchar2,
7 p_groupby_cols3 in out nocopy varchar2);
8
9 procedure Summarize_wklst_results( p_query_id IN NUMBER,
10 p_plan_id IN NUMBER);
11
12 procedure put_line(p_msg in varchar2) is
13 begin
14 --dbms_output.put_line(p_msg);
15 --insert into msc_test values(p_msg);
16 --commit;
17 null;
18 end put_line;
19
20 procedure delete_from_results_table(p_query_id in number,
21 p_plan_id in number) is
22 BEGIN
23 --KSA_DEBUG(SYSDATE,' p_query_id <> '||p_query_id,'delete_from_results_table');
24 delete msc_pq_results
25 where query_id = p_query_id
26 and plan_id = p_plan_id ;
27 end delete_from_results_table;
28
29 PROCEDURE delete_from_results_table(p_query_id IN NUMBER,
30 p_plan_id IN NUMBER,
31 p_detail_query_id IN NUMBER) IS
32 BEGIN
33 DELETE msc_pq_results
34 WHERE query_id = p_query_id
35 AND plan_id = p_plan_id
36 AND detail_query_id = p_detail_query_id;
37 END delete_from_results_table;
38
39 procedure populate_result_table(p_query_id in number,
40 p_query_type in number,
41 p_plan_id in number,
42 p_where_clause in varchar2,
43 p_execute_flag in BOOLEAN,
44 P_MASTER_QUERY_ID in NUMBER DEFAULT NULL,
45 p_sequence_id in NUMBER DEFAULT NULL) is
46 l_sql_stmt varchar2(32000);
47
48 l_insert_begin varchar2(1000) := 'INSERT INTO MSC_PQ_RESULTS ('
49 || ' QUERY_ID, PLAN_ID, ORGANIZATION_ID, SR_INSTANCE_ID, SUMMARY_DATA,';
50 l_insert_end varchar2(1000) := 'created_by, creation_date, last_update_date, '||
51 ' last_updated_by, last_update_login) ';
52
53 l_insert_end_new varchar2(1000) := 'sequence_id, created_by, creation_date, last_update_date, '||
54 ' last_updated_by, last_update_login ) ';
55
56 l_item_cols varchar2(1000) := 'INVENTORY_ITEM_ID, CATEGORY_ID,PLANNER_CODE,';
57 l_res_cols varchar2(1000) := 'DEPARTMENT_ID, RESOURCE_ID,RESOURCE_TYPE,';
58 l_supp_cols varchar2(1000) := 'SUPPLIER_ID, SUPPLIER_SITE_ID,INVENTORY_ITEM_ID,';
59 l_excp_cols varchar2(1000) := 'EXCEPTION_TYPE, EXCEPTION_ID,';
60 l_excp_cols2 varchar2(1000);
61 l_shipment_cols varchar2(1000) := 'SHIPMENT_ID, FROM_ORG_ID, FROM_ORG_INSTANCE_ID, '||
62 ' TO_ORG_ID, TO_ORG_INSTANCE_ID,';
63 l_Order_cols varchar2(1000) := 'TRANSACTION_ID,SOURCE_TABLE,ORDER_TYPE,ROW_ID,INVENTORY_ITEM_ID,SUPPLIER_ID,SUPPLIER_SITE_ID,CATEGORY_ID,PLANNER_CODE, CUSTOMER_ID, CUSTOMER_SITE_ID,';
64 l_Order_cols1 varchar2(1000) := 'TRANSACTION_ID,SOURCE_TABLE,ORDER_TYPE,ROW_ID,INVENTORY_ITEM_ID,VENDOR_ID,VENDOR_SITE_ID,CATEGORY_ID,PLANNER_CODE, CUSTOMER_ID, CUSTOMER_SITE_ID,';
65 l_summary_col constant varchar2(10) := '1,';
66 l_summary_col2 constant varchar2(10) := '-99,';
67 l_detail_col constant varchar2(10) := '2,';
68
69 l_select_begin varchar2(1000) :=' select distinct '||to_char(p_query_id)
70 ||', nvl(plan_id,-1), nvl(organization_id,-1), nvl(sr_instance_id,-1), ';
71
72 l_who_cols varchar2(1000) := fnd_global.user_id||',sysdate,sysdate,'
73 ||fnd_global.user_id||', null';
74 l_who_cols1 varchar2(1000);
75
76 l_cp_context varchar2(200) := 'and ( COMPANY_ID= SYS_CONTEXT(''MSC'',''COMPANY_ID'') OR OWNING_COMPANY_ID= SYS_CONTEXT(''MSC'',''COMPANY_ID'') ) ';
77 l_view varchar2(100);
78 l_source_type number;
79
80 CURSOR cur_priority(p_query_id NUMBER,p_detail_query_id NUMBER) IS
81 SELECT NVL(priority,999)
82 FROM msc_pq_types
83 WHERE query_id= p_query_id
84 AND NVL(detail_query_id,query_id) = p_detail_query_id;
85
86 cursor c_groupby (l_query_id in number,
87 l_source_type in number, l_object_type in number) is
88 select group_by, sequence
89 from msc_selection_criteria_v
90 where folder_id = l_query_id
91 and nvl(source_type, -1) = nvl(l_source_type, -1)
92 and nvl(object_type, -1) = nvl(l_object_type, -1)
93 and nvl(count_by, 2) = 1
94 and nvl(active_flag,2) = 1
95 order by sequence;
96
97 CURSOR c_wl_groupby(l_query IN NUMBER) IS
98 SELECT distinct ATTRIBUTE_NAME group_by
99 FROM MSC_WORKLIST_GROUPBY
100 WHERE QUERY_ID = l_query;
101
102 cursor c_excp_types (l_query_id number) is
103 select source_type, object_type, sequence_id,NVL(priority,999), frequency
104 from msc_pq_types
105 where query_id = l_query_id;
106
107 CURSOR c_frequency(p_query_id NUMBER, p_detail_query_id IN NUMBER) is
108 SELECT frequency
109 FROM msc_pq_types
110 WHERE query_id = p_query_id
111 AND detail_query_id = p_detail_query_id;
112
113 l_group_by varchar2(30);
114 l_seq number;
115 l_seq_id number; -- sequence_id in msc_pq_types
116
117 l_source number;
118 l_object number;
119
120 l_group_by_cols1 varchar2(100);
121 l_group_by_cols2 varchar2(300);
122 l_new_group_by_cols1 varchar2(300);
123 l_new_group_by_cols2 varchar2(300);
124 l_new_group_by_col1 varchar2(300);
125 l_new_group_by_col2 varchar2(300);
126 l_new_group_by_col boolean := false;
127 l_worklist_cols VARCHAR2(300);
128 l_group_by_count number;
129 v_plan_type number;
130 l_temp_query_id NUMBER;
131 l_detail_query_id NUMBER;
132 l_temp_query_type NUMBER;
133 l_priority NUMBER;
134 l_priority_wl NUMBER;
135 l_frequency NUMBER;
136 l_frequency_wl NUMBER;
137 l_check_frequency VARCHAR2(500);
138 begin
139 --KSA_DEBUG(SYSDATE,' p_query_type <> '||p_query_type,'populate_result_table');
140 if (p_query_type <> 12 ) then
141 if p_query_type = -99 then
142 Summarize_wklst_results(p_query_id,p_plan_id);
143 return;
144 end if;
145 IF p_MASTER_QUERY_ID IS NOT NULL THEN
146 DECLARE
147 l_group_by_cols varchar2(2000);
148 l_group_by_cols_excep varchar2(2000);
149 BEGIN
150 l_select_begin :=' select distinct '||to_char(p_MASTER_QUERY_ID)
151 ||', nvl(plan_id,-1), ';
152 For rec_groupby in c_wl_groupby (p_MASTER_QUERY_ID) loop
153 if rec_groupby.group_by = 'ORGANIZATION_CODE' then
154 l_group_by_cols := 'nvl(organization_id,-1), nvl(sr_instance_id,-1),';
155 end if;
156 if rec_groupby.group_by = 'EXCEPTION_TYPE' then
157 l_group_by_cols_excep := 'EXCEPTION_TYPE';
158 end if;
159 end loop;
160 if l_group_by_cols is not null then
161 l_select_begin :=l_select_begin ||l_group_by_cols;
162 else
163 l_select_begin :=l_select_begin ||'-1,-1,';
164 end if;
165 if l_group_by_cols_excep is not null then
166 l_excp_cols2 := 'EXCEPTION_TYPE, EXCEPTION_ID,';
167 else
168 l_excp_cols2 := '-99, EXCEPTION_ID,';
169 end if;
170
171 END;
172 l_detail_query_id := p_query_id;
173 l_temp_query_id := p_MASTER_QUERY_ID;
174 OPEN cur_priority(p_MASTER_QUERY_ID,l_detail_query_id);
175 FETCH cur_priority INTO l_priority;
176 CLOSE cur_priority;
177
178 l_insert_end := 'created_by, creation_date, last_update_date, '||
179 ' last_updated_by, last_update_login , detail_query_id,PRIORITY ) ';
180 l_who_cols1 := l_who_cols;
181 l_who_cols := l_who_cols||','||TO_CHAR(l_detail_query_id)||','||TO_CHAR(l_PRIORITY)||' ';
182
183 -- delete_from_results_table(l_temp_query_id, p_plan_id, l_detail_query_id);
184 l_temp_query_type := p_wl_type;
185 ELSE
186 l_temp_query_id := p_query_id;
187 l_temp_query_type := P_QUERY_TYPE;
188 delete_from_results_table(p_query_id, p_plan_id);
189 END IF;
190 end if;
191
192 if (p_query_type = p_item_type) then
193 --KSA_DEBUG(SYSDATE,' p_query_type <> '||p_query_type,'populate_result_table');
194 declare
195 l_summary_col2 varchar2(10) := '-99,';
196 begin
197 if l_temp_query_type <> p_wl_type then
198 l_summary_col2 := l_summary_col;
199 end if;
200 l_sql_stmt := l_insert_begin||l_item_cols||l_insert_end||
201 l_select_begin||l_summary_col2||l_item_cols||l_who_cols||
202 ' FROM '||p_item_view||
203 ' where plan_id = '||p_plan_id||' and '||p_where_clause;
204 put_line(l_sql_stmt);
205 --KSA_DEBUG(SYSDATE,'*1* l_sql_stmt <> '||l_sql_stmt,'populate_result_table');
206 msc_get_name.execute_dsql(l_sql_stmt);
207 end;
208 elsif (p_query_type = p_res_type) then
209 l_sql_stmt := l_insert_begin||l_res_cols||l_insert_end||
210 l_select_begin||l_summary_col||l_res_cols||l_who_cols||
211 ' FROM '||p_res_view||
212 ' where plan_id = '||p_plan_id||' and '||p_where_clause;
213 put_line(l_sql_stmt);
214 msc_get_name.execute_dsql(l_sql_stmt);
215 elsif (p_query_type = p_supp_type) then
216 l_sql_stmt := l_insert_begin||l_supp_cols||l_insert_end||
217 l_select_begin||l_summary_col||l_supp_cols||l_who_cols||
218 ' FROM '||p_supp_view||
219 ' where plan_id = '||p_plan_id||' and '||p_where_clause;
220 put_line(l_sql_stmt);
221 msc_get_name.execute_dsql(l_sql_stmt);
222 ELSIF (p_query_type = p_order_type) then
223 declare
224 l_summary_col2 varchar2(10) := '-99,';
225 begin
226 if l_temp_query_type <> p_wl_type then
227 l_summary_col2 := l_summary_col;
228 end if;
229 l_sql_stmt := l_insert_begin||l_order_cols||l_insert_end||
230 l_select_begin||l_summary_col2||l_Order_cols1||l_who_cols||
231 ' FROM '||p_order_view||
232 ' WHERE plan_id = '||p_plan_id||' AND '||p_where_clause;
233 --KSA_DEBUG(SYSDATE,'l_sql_stmt is '||l_sql_stmt,'populate_result_table');
234 put_line(l_sql_stmt);
235 msc_get_name.execute_dsql(l_sql_stmt);
236 end;
237 elsif (p_query_type IN (p_excp_type,p_wl_type)) then
238 --KSA_DEBUG(SYSDATE,' p_query_type <> '||p_query_type,'populate_result_table');
239 IF l_temp_query_type = p_wl_type THEN
240 IF p_query_type <> p_wl_type THEN
241 OPEN c_frequency(l_temp_query_id,l_detail_query_id);
242 FETCH c_frequency INTO l_frequency;
243 IF c_frequency%FOUND AND l_frequency IS NOT NULL THEN
244 --l_check_frequency := ' trunc(sysdate) > (nvl(ACTION_TAKEN_DATE,(SYSDATE-1)) + '||l_frequency||')';
245 l_check_frequency := ' ACTION_TAKEN_DATE IS NULL OR '||
246 ' sysdate > (ACTION_TAKEN_DATE + '||l_frequency||')';
247 END IF;
248 CLOSE c_frequency;
249 END IF;
250 OPEN c_excp_types(p_query_id);
251 LOOP
252 FETCH c_excp_types INTO l_source, l_object, l_seq_id,l_priority_wl,l_frequency_wl;
253 EXIT WHEN c_excp_types%NOTFOUND;
254
255 l_group_by_count := 0;
256 l_new_group_by_cols1 := null;
257 l_new_group_by_cols2 := null;
258
259 IF p_query_type = p_wl_type AND l_frequency_wl IS NOT NULL THEN
260 --l_check_frequency := ' trunc(sysdate) > (nvl(ACTION_TAKEN_DATE,(SYSDATE-1)) + '||l_frequency_wl||')';
261 l_check_frequency := ' ACTION_TAKEN_DATE IS NULL OR '||
262 ' sysdate > (ACTION_TAKEN_DATE + '||l_frequency_wl||')';
263 END IF;
264 l_frequency_wl := NULL;
265
266 OPEN c_wl_groupby (l_temp_query_id) ;
267 LOOP
268 FETCH c_wl_groupby INTO l_group_by;
269 EXIT WHEN c_wl_groupby%NOTFOUND;
270 --3631530 bug fix
271 l_new_group_by_col := FALSE;
272 IF l_group_by = 'ORGANIZATION_CODE' THEN
273 l_new_group_by_col1 := l_group_by;
274 l_new_group_by_col2 := 'GROUPBY_ORG';
275 l_new_group_by_col := true;
276 ELSIF l_group_by = 'ITEM_SEGMENTS' THEN
277 l_new_group_by_col1 := 'INVENTORY_ITEM_ID';
278 l_new_group_by_col2 := 'INVENTORY_ITEM_ID';
279 l_new_group_by_col := TRUE;
280 ELSIF l_group_by = 'CATEGORY_NAME' THEN
281 l_new_group_by_col1 := 'CATEGORY_ID';
282 l_new_group_by_col2 := 'CATEGORY_ID';
283 l_new_group_by_col := TRUE;
284 ELSIF l_group_by = 'PLANNER_CODE' THEN
285 l_new_group_by_col1 := 'PLANNER_CODE';
286 l_new_group_by_col2 := 'PLANNER_CODE';
287 l_new_group_by_col := TRUE;
288 ELSIF l_group_by = 'CUSTOMER_NAME' THEN
289 l_new_group_by_col1 := 'CUSTOMER_ID';
290 l_new_group_by_col2 := 'CUSTOMER_ID';
291 l_new_group_by_col := TRUE;
292 ELSIF l_group_by = 'CUSTOMER_SITE' THEN
293 l_new_group_by_col1 := 'CUSTOMER_SITE_ID';
294 l_new_group_by_col2 := 'CUSTOMER_SITE_ID';
295 l_new_group_by_col := TRUE;
296 ELSIF l_group_by = 'SUPPLIER_NAME' THEN
297 l_new_group_by_col1 := 'SUPPLIER_ID';
298 l_new_group_by_col2 := 'SUPPLIER_ID';
299 l_new_group_by_col := TRUE;
300 ELSIF l_group_by = 'EXCEPTION_TYPE' THEN
301 /*l_new_group_by_col1 := 'EXCEPTION_TYPE';
302 l_new_group_by_col2 := 'EXCEPTION_TYPE';*/
303 -- Not REQUIRED as this field will be there in the group by.
304 l_new_group_by_col := FALSE;
305 ELSIF l_group_by = 'SUPPLIER_SITE' THEN
306 l_new_group_by_col1 := 'SUPPLIER_SITE_ID';
307 l_new_group_by_col2 := 'SUPPLIER_SITE_ID';
308 l_new_group_by_col := TRUE;
309 END IF;
310 IF (l_new_group_by_col) then
311 if l_new_group_by_cols1 is null then
312 l_new_group_by_cols1 := l_new_group_by_col2||', ';
313 l_new_group_by_cols2 := l_new_group_by_col1||', ';
314 else
315 l_new_group_by_cols1 := l_new_group_by_cols1||l_new_group_by_col2||', ';
316 l_new_group_by_cols2 := l_new_group_by_cols2||l_new_group_by_col1||', ';
317 end if;
318 end if;
319 end loop;
320 close c_wl_groupby;
321
322 select plan_type
323 into v_plan_type
324 from msc_plans
325 where plan_id = p_plan_id;
326
327 if p_plan_id = -1 then
328 msc_x_security.set_context;
329 --10350610 bug set cp_context explicitly
330
331 l_view := p_cp_excp_view;
332 l_source_type := 2;
333 l_sql_stmt := l_insert_begin||'source_type ,'|| l_excp_cols || l_insert_end||
334 l_select_begin||l_detail_col||l_source_type||','||l_excp_cols || l_who_cols||
335 ' FROM '||l_view||
336 ' where plan_id = '||p_plan_id||l_cp_context||' and '||p_where_clause
337 ||' and ( source_type = 2 and exception_type = '||l_object||')';
338 else
339 if v_plan_type IN ( 5,8,9) then
340 l_view := 'MSC_DRP_EXC_DETAILS_V';
341 else -- v_plan_type <> 5
342 l_view := p_excp_view;
343 end if;
344 l_source_type := 1;
345 IF p_query_type = p_wl_type THEN
346 l_who_cols := l_who_cols1||','||TO_CHAR(l_detail_query_id)||','||TO_CHAR(l_priority_wl)||' ';
347 END IF;
348 --l_worklist_cols := ' INVENTORY_ITEM_ID,SUPPLIER_ID,SUPPLIER_SITE_ID,CUSTOMER_ID,CUSTOMER_SITE_ID,CATEGORY_ID,PLANNER_CODE, ';
349 l_sql_stmt := l_insert_begin||'source_type , sequence_id, '||
350 l_excp_cols ||l_new_group_by_cols1|| --l_worklist_cols|| --l_new_group_by_cols1||
351 l_insert_end||
352 l_select_begin||l_detail_col||
353 l_source_type||','||l_seq_id||','||
354 l_excp_cols2 ||l_new_group_by_cols2|| --l_worklist_cols|| --l_new_group_by_cols2||
355 l_who_cols||
356 ' FROM '||l_view||' where plan_id = '||p_plan_id||
357 ' and '||p_where_clause||
358 ' and ( source_type = 1 and exception_type = '||
359 l_object||')';
360 IF l_check_frequency IS NOT NULL THEN
361 l_sql_stmt := l_sql_stmt||' and '||l_check_frequency;
362 l_check_frequency:= NULL;
363 END IF;
364 end if;
365 put_line(l_sql_stmt);
366 --KSA_DEBUG(SYSDATE,'*1* l_sql_stmt <> '||l_sql_stmt,'populate_result_table');
367 msc_get_name.execute_dsql(l_sql_stmt);
368
369 end loop;
370 close c_excp_types;
371
372 INSERT INTO MSC_PQ_RESULTS(query_id,plan_id,sr_instance_id,
373 organization_id, exception_type,
374 source_type, summary_data, sequence_id,
375 exception_count, groupby_org,
376 INVENTORY_ITEM_ID, --groupby_supply_item,
377 CATEGORY_ID, PLANNER_CODE,
378 CUSTOMER_iD, SUPPLIER_ID,
379 CUSTOMER_SITE_ID, SUPPLIER_SITE_ID,
380 created_by,creation_date,
381 last_updated_by, last_update_date,
382 detail_query_id,Priority)
383 SELECT l_temp_query_id, NVL(p_plan_id,-1), sr_instance_id,
384 organization_id, exception_type,
385 source_type, -99, sequence_id,
386 COUNT(*), groupby_org,
387 INVENTORY_ITEM_ID, --groupby_supply_item,
388 CATEGORY_ID, PLANNER_CODE,
389 CUSTOMER_iD, SUPPLIER_ID,
390 CUSTOMER_SITE_ID, SUPPLIER_SITE_ID,
391 fnd_global.user_id, sysdate,
392 fnd_global.user_id, SYSDATE,
393 detail_query_id,PRIORITY
394 FROM MSC_PQ_RESULTS
395 WHERE query_id = l_temp_query_id
396 AND plan_id = p_plan_id
397 AND ((detail_query_id <> query_id
398 AND detail_query_id = l_detail_query_id)
399 OR detail_query_id = query_id)
400 GROUP BY query_id, plan_id, sr_instance_id,
401 organization_id, exception_type,
402 source_type, sequence_id,
403 groupby_org, INVENTORY_ITEM_ID,
404 CATEGORY_ID, PLANNER_CODE,
405 CUSTOMER_iD, SUPPLIER_ID,
406 CUSTOMER_SITE_ID, SUPPLIER_SITE_ID,
407 detail_query_id,PRIORITY;
408 ELSE
409 --(begin if not part of worklist
410
411 open c_excp_types(p_query_id);
412 loop
413 fetch c_excp_types into l_source, l_object, l_seq_id,l_priority_wl,l_frequency_wl;
414 exit when c_excp_types%notfound;
415
416 l_group_by_count := 0;
417 l_group_by_cols1 := null;
418 l_group_by_cols2 := null;
419 l_new_group_by_cols1 := null;
420 l_new_group_by_cols2 := null;
421 open c_groupby (p_query_id, l_source, l_object) ;
422 loop
423 fetch c_groupby into l_group_by, l_seq;
424 exit when c_groupby%notfound;
425 --3631530 bug fix
426 l_new_group_by_col := false;
427 if (l_group_by = 'ORGANIZATION_CODE') then
428 l_new_group_by_col1 := l_group_by;
429 l_new_group_by_col2 := 'GROUPBY_ORG';
430 l_new_group_by_col := true;
431 elsif (l_group_by = 'ITEM_SEGMENTS') then
432 l_new_group_by_col1 := l_group_by;
433 l_new_group_by_col2 := 'GROUPBY_ITEM';
434 l_new_group_by_col := true;
435 elsif (l_group_by = 'SUPPLY_ITEM_SEGMENTS') then
436 l_new_group_by_col1 := l_group_by;
437 l_new_group_by_col2 := 'GROUPBY_SUPPLY_ITEM';
438 l_new_group_by_col := true;
439 elsif (l_group_by = 'CATEGORY_NAME') then
440 l_new_group_by_col1 := l_group_by;
441 l_new_group_by_col2 := 'GROUPBY_CATEGORY';
442 l_new_group_by_col := true;
443 elsif (l_group_by = 'PLANNER_CODE') then
444 l_new_group_by_col1 := l_group_by;
445 l_new_group_by_col2 := 'GROUPBY_PLANNER';
446 l_new_group_by_col := true;
447 elsif (l_group_by = 'DEPARTMENT_LINE_CODE') then
448 l_new_group_by_col1 := l_group_by;
449 l_new_group_by_col2 := 'GROUPBY_DEPT';
450 l_new_group_by_col := true;
451 elsif (l_group_by = 'RESOURCE_CODE') then
452 l_new_group_by_col1 := l_group_by;
453 l_new_group_by_col2 := 'GROUPBY_RES';
454 l_new_group_by_col := true;
455 elsif (l_group_by = 'CUSTOMER_NAME') then
456 l_new_group_by_col1 := l_group_by;
457 l_new_group_by_col2 := 'GROUPBY_CUSTOMER';
458 l_new_group_by_col := true;
459 elsif (l_group_by = 'SUPPLIER_NAME' ) then
460 l_new_group_by_col1 := l_group_by;
461 l_new_group_by_col2 := 'GROUPBY_SUPPLIER';
462 l_new_group_by_col := true;
463 else
464 l_group_by_count := l_group_by_count + 1;
465 if l_group_by_cols1 is null then
466 l_group_by_cols1 := ' CHAR'||to_char(l_group_by_count)||', ';
467 l_group_by_cols2 := ' '||l_group_by||', ';
468 elsif l_group_by_count <=5 then
469 l_group_by_cols1 := l_group_by_cols1||' CHAR'||to_char(l_group_by_count)||', ';
470 l_group_by_cols2 := l_group_by_cols2||l_group_by||',';
471 end if;
472 end if;
473 if (l_new_group_by_col) then
474 if l_new_group_by_cols1 is null then
475 l_new_group_by_cols1 := l_new_group_by_col2||', ';
476 l_new_group_by_cols2 := l_new_group_by_col1||', ';
477 else
478 l_new_group_by_cols1 := l_new_group_by_cols1||l_new_group_by_col2||', ';
479 l_new_group_by_cols2 := l_new_group_by_cols2||l_new_group_by_col1||', ';
480 end if;
481 end if;
482 end loop;
483 close c_groupby;
484
485 select plan_type
486 into v_plan_type
487 from msc_plans
488 where plan_id = p_plan_id;
489
490 if p_plan_id = -1 then
491 msc_x_security.set_context;
492 --10350610 bug set cp_context explicitly
493
494 l_view := p_cp_excp_view;
495 l_source_type := 2;
496 l_sql_stmt := l_insert_begin||'source_type , sequence_id, '|| l_excp_cols || l_group_by_cols1 ||l_insert_end||
497 l_select_begin||l_detail_col||l_source_type||','||l_seq_id||','||l_excp_cols || l_group_by_cols2 ||l_who_cols||
498 ' FROM '||l_view||
499 ' where plan_id = '||p_plan_id||l_cp_context||' and '||p_where_clause
500 ||' and ( source_type = 2 and exception_type = '||l_object||')';
501
502 else
503 if v_plan_type IN (5,8,9) then
504 l_view := 'MSC_DRP_EXC_DETAILS_V';
505 else -- v_plan_type <> 5
506 l_view := p_excp_view;
507 end if;
508 l_source_type := 1;
509 l_sql_stmt := l_insert_begin || 'source_type , sequence_id, '||
510 l_excp_cols || l_new_group_by_cols1||
511 l_group_by_cols1 || l_insert_end||
512 l_select_begin || l_detail_col||
513 l_source_type || ','||l_seq_id||','||
514 l_excp_cols || l_new_group_by_cols2||
515 l_group_by_cols2 || l_who_cols||
516 ' FROM '||l_view|| ' where plan_id = '||
517 p_plan_id||' and '||p_where_clause||
518 ' and ( source_type = 1 and exception_type = '||l_object||')';
519 IF l_check_frequency IS NOT NULL THEN
520 l_sql_stmt := l_sql_stmt||' and '||l_check_frequency;
521 END IF;
522 end if;
523 put_line(l_sql_stmt);
524 --KSA_DEBUG(SYSDATE,'*2* l_sql_stmt <> '||l_sql_stmt,'populate_result_table');
525 msc_get_name.execute_dsql(l_sql_stmt);
526
527 end loop;
528 close c_excp_types;
529
530 INSERT INTO MSC_PQ_RESULTS (
531 query_id,
532 plan_id,
533 sr_instance_id,
534 organization_id,
535 exception_type,
536 source_type,
537 summary_data,
538 sequence_id,
539 exception_count,
540 groupby_org, groupby_item, groupby_supply_item, groupby_category,
541 groupby_planner, groupby_dept, groupby_res, groupby_customer, groupby_supplier,
542 char1,
543 char2,
544 char3,
545 char4,
546 char5,
547 created_by,
548 creation_date,
549 last_updated_by,
550 last_update_date)
551 SELECT l_temp_query_id, --p_query_id
552 nvl(p_plan_id,-1),
553 -1,
554 -1,
555 exception_type,
556 source_type,
557 1,
558 sequence_id,
559 count(*),
560 groupby_org, groupby_item, groupby_supply_item, groupby_category,
561 groupby_planner, groupby_dept, groupby_res, groupby_customer, groupby_supplier,
562 char1,
563 char2,
564 char3,
565 char4,
566 char5,
567 fnd_global.user_id,
568 sysdate,
569 fnd_global.user_id,
570 sysdate
571 FROM MSC_PQ_RESULTS
572 WHERE query_id = l_temp_query_id --p_query_id
573 and plan_id = p_plan_id
574 GROUP BY query_id, plan_id, source_type,exception_type,sequence_id,
575 groupby_org, groupby_item, groupby_supply_item, groupby_category,
576 groupby_planner, groupby_dept, groupby_res, groupby_customer, groupby_supplier,
577 char1, char2, char3, char4, char5;
578 END IF;
579 --) end If not part of the worklist
580 elsif (p_query_type = p_crit_type) then
581 l_view := p_item_attributes_view;
582 l_cp_context := null;
583 l_source_type := 100;
584 l_sql_stmt := l_insert_begin||l_item_cols||l_insert_end_new||
585 l_select_begin||l_summary_col||' inventory_item_id , ' || p_master_query_id||' , ' ||
586 'NULL ' ||' , ' ||
587 p_sequence_id || ' , ' ||
588 l_who_cols||
589 ' FROM '||p_item_attributes_view||
590 ' where simulation_set_id is NULL and plan_id = '||p_plan_id||' and '||p_where_clause;
591
592 msc_get_name.execute_dsql(l_sql_stmt);
593
594 return;
595
596 elsif (p_query_type = p_shipment_type) then
597 l_sql_stmt := l_insert_begin|| l_shipment_cols||l_insert_end||
598 l_select_begin||l_summary_col||l_shipment_cols||l_who_cols||
599 ' FROM '||p_shipment_view||
600 ' where plan_id = '||p_plan_id||' and '||p_where_clause ||
601 ' GROUP BY PLAN_ID, SHIPMENT_ID, FROM_ORG_ID, FROM_ORG_INSTANCE_ID, '||
602 ' TO_ORG_ID, TO_ORG_INSTANCE_ID, ORGANIZATION_ID, SR_INSTANCE_ID ';
603 put_line(l_sql_stmt);
604 msc_get_name.execute_dsql(l_sql_stmt);
605 end if;
606
607
608 update msc_personal_queries
609 set execute_flag = 1,
610 EXECUTION_DATE = TRUNC(SYSDATE)
611 where query_id = l_temp_query_id ;
612 --where query_id = p_query_id ;
613 end populate_result_table;
614
615 procedure update_category( ERRBUF OUT NOCOPY VARCHAR2,
616 RETCODE OUT NOCOPY NUMBER,
617 p_query_id IN NUMBER) IS
618
619 cursor item_exist(p_item number, p_org number, p_inst number) IS
620 select 1
621 from msc_item_attributes
622 where simulation_set_id = -1
623 and plan_id = -1
624 and inventory_item_id = p_item
625 and organization_id = p_org
626 and sr_instance_id = p_inst;
627
628 cursor cat_id(p_item number, p_seq number , p_org number, p_inst number, p_query_id number) IS
629 select category_id
630 from msc_pq_results
631 where query_id = p_query_id
632 and inventory_item_id = p_item
633 and organization_id = p_org
634 and sr_instance_id = p_inst
635 and sequence_id = p_seq;
636
637
638 type number_arr is table of NUMBER INDEX BY BINARY_INTEGER;
639 v_item_id number_arr;
640 v_cat_id number_arr;
641 v_seq number_arr;
642 v_org_id number_arr;
643 v_inst_id number_arr;
644 p_cat number;
645 p_exist number :=0;
646
647 begin
648
649 -- select the records with highest seq number so we do not pick
650 -- up overlapping items.
651
652 select inventory_item_id, organization_id, sr_instance_id, max(sequence_id)
653 bulk collect into v_item_id, v_org_id, v_inst_id, v_seq
654 from msc_pq_results
655 where query_id = p_query_id
656 group by inventory_item_id, organization_id, sr_instance_id;
657
658
659 if v_item_id.COUNT > 0 then
660 for j IN v_item_id.FIRST .. v_item_id.LAST LOOP
661 open item_exist(v_item_id(j), v_org_id(j), v_inst_id(j));
662 fetch item_exist into p_exist;
663 close item_exist;
664
665 open cat_id(v_item_id(j), v_seq(j), v_org_id(j), v_inst_id(j), p_query_id);
666 fetch cat_id into p_cat;
667 close cat_id;
668
669 if p_exist = 1 then
670 update msc_item_attributes
671 set criticality_category = p_cat
672 where inventory_item_id = v_item_id(j)
673 and organization_id = v_org_id(j)
674 and sr_instance_id = v_inst_id(j)
675 and simulation_set_id = -1
676 and plan_id = -1;
677 else
678 insert into msc_item_attributes(simulation_set_id,
679 inventory_item_id, organization_id,
680 sr_instance_id, last_update_date,
681 last_updated_by, creation_date, created_by,
682 criticality_category, plan_id, updated_columns_count
683 ) values
684 ( -1 , v_item_id(j), v_org_id(j),
685 v_inst_id(j), sysdate , -1,
686 sysdate, -1, p_cat, -1, 1);
687 end if;
688 p_exist := 0;
689
690 END LOOP;
691 commit;
692 end if;
693
694 EXCEPTION
695 WHEN OTHERS THEN
696 RAISE;
697 end update_category;
698
699 function get_user(p_user_id in number) return varchar2 IS
700 l_name fnd_user.user_name%type;
701 begin
702 select user_name
703 into l_name
704 from fnd_user
705 where user_id = p_user_id;
706
707 return l_name;
708 exception
709 when others then
710 return null;
711 end get_user;
712
713 function get_query_name(p_query_id in number) return varchar2 is
714 l_name varchar2(80);
715 begin
716 select query_name
717 into l_name
718 from msc_personal_queries
719 where query_id = p_query_id;
720
721 return l_name;
722 exception
723 when others then
724 return null;
725 end get_query_name;
726
727 function get_query_type(p_query_id in number) return number is
728 l_name number;
729 begin
730 select query_type
731 into l_name
732 from msc_personal_queries
733 where query_id = p_query_id;
734
735 return l_name;
736 exception
737 when others then
738 return null;
739 end get_query_type;
740
741 procedure populate_cp_temp_table(p_query_id in number) is
742 -- PRAGMA AUTONOMOUS_TRANSACTION;
743 begin
744 insert into msc_query (query_id, number1,
745 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY)
746 select p_query_id, number1 ,
747 sysdate, -1, sysdate, -1
748 from msc_form_query
749 where query_id = p_query_id;
750
751 commit;
752 end populate_cp_temp_table;
753
754 FUNCTION copy_query(p_query_id in number,
755 p_query_name in varchar2,
756 p_query_desc in varchar2,
757 p_public_flag in number) return number is
758
759 cursor c_pers_queries (p_query in number) is
760 select QUERY_ID, QUERY_NAME, DESCRIPTION,
761 QUERY_TYPE, PUBLIC_FLAG, AND_OR_FLAG, EXECUTE_FLAG,
762 CREATED_BY, CREATION_DATE, LAST_UPDATE_DATE,
763 LAST_UPDATED_BY, LAST_UPDATE_LOGIN, PLAN_TYPE,
764 AUTO_RELEASE,GROUP_ID,UPDATED_FLAG
765 from msc_personal_queries
766 where query_id = p_query;
767
768 cursor c_pq_types (p_query in number) is
769 select QUERY_ID, SOURCE_TYPE, OBJECT_TYPE,
770 SEQUENCE_ID, AND_OR_FLAG, CREATION_DATE,
771 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
772 CUSTOMIZED_TEXT, OBJECT_TYPE_TEXT, ACTIVE_FLAG,
773 DETAIL_QUERY_ID,FREQUENCY,PRIORITY
774 from msc_pq_types
775 where query_id = p_query;
776
777 cursor c_pers_criteria (p_query in number) is
778 select SEQUENCE, FOLDER_ID, OBJECT_SEQUENCE_ID,
779 FIELD_NAME, FIELD_TYPE,
780 HIDDEN_FROM_FIELD, CONDITION,
781 FROM_FIELD, TO_FIELD, FOLDER_OBJECT,
782 TREE_NODE, CREATION_DATE, CREATED_BY,
783 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
784 AND_OR, COUNT_BY, SEARCH_QUERY_ID,
785 SEARCH_QUERY_NAME, DEFAULT_FLAG,
786 PUBLIC_FLAG, FROM_FIELD_VALUE, TO_FIELD_VALUE,
787 OBJECT_TYPE, SOURCE_TYPE, ACTIVE_FLAG
788 from msc_selection_criteria
789 where folder_id = p_query;
790
791 cursor c_among_criteria(p_query_id in number) is
792 select SEQUENCE,FIELD_NAME, OR_VALUES,
793 HIDDEN_VALUES,OBJECT_SEQUENCE,ORDER_BY_SEQUENCE,
794 CREATION_DATE, CREATED_BY,
795 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN
796 from msc_among_values
797 where folder_id = p_query_id;
798
799 cursor c_query is select msc_personal_queries_s.nextval from dual;
800 --5746041 bugfix, changed from msc_form_query_s to msc_personal_queries_s
801
802 CURSOR c_group_by(p_query_id IN NUMBER) IS
803 SELECT distinct ATTRIBUTE_NAME group_by
804 FROM MSC_WORKLIST_GROUPBY
805 WHERE QUERY_ID = p_query_id;
806
807
808 l_save_as_query_id number;
809
810 rec_pers_queries c_pers_queries%ROWTYPE;
811 rec_pers_criteria c_pers_criteria%ROWTYPE;
812
813
814 begin
815 open c_query;
816 fetch c_query into l_save_as_query_id;
817 close c_query;
818
819 open c_pers_queries(p_query_id);
820 fetch c_pers_queries into rec_pers_queries;
821 close c_pers_queries;
822
823 if rec_pers_queries.query_type not in (7,8) then -- org/customer_list
824 l_save_as_query_id := -1 * l_save_as_query_id;
825 end if;
826
827 INSERT INTO msc_personal_queries( QUERY_ID, QUERY_NAME, DESCRIPTION,
828 QUERY_TYPE, PUBLIC_FLAG, AND_OR_FLAG, EXECUTE_FLAG,
829 CREATED_BY, CREATION_DATE, LAST_UPDATE_DATE,
830 LAST_UPDATED_BY, LAST_UPDATE_LOGIN, PLAN_TYPE,
831 AUTO_RELEASE,GROUP_ID,UPDATED_FLAG)
832 VALUES (l_save_as_query_id, p_query_name, p_query_desc,
833 rec_pers_queries.QUERY_TYPE, p_public_flag, rec_pers_queries.AND_OR_FLAG,
834 2, rec_pers_queries.CREATED_BY, rec_pers_queries.CREATION_DATE,
835 rec_pers_queries.LAST_UPDATE_DATE, rec_pers_queries.LAST_UPDATED_BY,
836 rec_pers_queries.LAST_UPDATE_LOGIN, rec_pers_queries.PLAN_TYPE,
837 rec_pers_queries.AUTO_RELEASE,rec_pers_queries.GROUP_ID,rec_pers_queries.UPDATED_FLAG);
838
839 for rec_pq_types in c_pq_types(p_query_id)
840 loop
841 INSERT INTO MSC_PQ_TYPES (QUERY_ID, SOURCE_TYPE, OBJECT_TYPE,
842 SEQUENCE_ID, AND_OR_FLAG, CREATION_DATE,
843 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
844 CUSTOMIZED_TEXT, OBJECT_TYPE_TEXT, ACTIVE_FLAG,
845 DETAIL_QUERY_ID,FREQUENCY,PRIORITY)
846 VALUES (l_save_as_query_id, rec_pq_types.SOURCE_TYPE, rec_pq_types.OBJECT_TYPE,
847 rec_pq_types.SEQUENCE_ID, rec_pq_types.AND_OR_FLAG,
848 rec_pq_types.CREATION_DATE, rec_pq_types.LAST_UPDATE_DATE,
849 rec_pq_types.LAST_UPDATED_BY, rec_pq_types.LAST_UPDATE_LOGIN,
850 rec_pq_types.CUSTOMIZED_TEXT, rec_pq_types.OBJECT_TYPE_TEXT, rec_pq_types.ACTIVE_FLAG,
851 rec_pq_types.DETAIL_QUERY_ID,rec_pq_types.FREQUENCY,rec_pq_types.PRIORITY);
852 end loop;
853
854 if rec_pers_queries.query_type in (7,8) then -- org/customer list
855 return l_save_as_query_id;
856 end if;
857
858 for rec_pers_criteria in c_pers_criteria(p_query_id)
859 loop
860 INSERT INTO msc_selection_criteria
861 (SEQUENCE, FOLDER_ID, OBJECT_SEQUENCE_ID, FIELD_NAME, FIELD_TYPE,
862 HIDDEN_FROM_FIELD, CONDITION,
863 FROM_FIELD, TO_FIELD, FOLDER_OBJECT,
864 TREE_NODE, CREATION_DATE, CREATED_BY,
865 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
866 AND_OR, COUNT_BY, SEARCH_QUERY_ID,
867 SEARCH_QUERY_NAME, DEFAULT_FLAG,
868 PUBLIC_FLAG, FROM_FIELD_VALUE, TO_FIELD_VALUE,
869 OBJECT_TYPE, SOURCE_TYPE, ACTIVE_FLAG)
870 VALUES (rec_pers_criteria.SEQUENCE, l_save_as_query_id,
871 rec_pers_criteria.OBJECT_SEQUENCE_ID ,
872 rec_pers_criteria.FIELD_NAME, rec_pers_criteria.FIELD_TYPE,
873 rec_pers_criteria.HIDDEN_FROM_FIELD, rec_pers_criteria.CONDITION,
874 rec_pers_criteria.FROM_FIELD, rec_pers_criteria.TO_FIELD,
875 rec_pers_criteria.FOLDER_OBJECT, rec_pers_criteria.TREE_NODE,
876 rec_pers_criteria.CREATION_DATE, rec_pers_criteria.CREATED_BY,
877 rec_pers_criteria.LAST_UPDATE_DATE, rec_pers_criteria.LAST_UPDATED_BY,
878 rec_pers_criteria.LAST_UPDATE_LOGIN, rec_pers_criteria.AND_OR,
879 rec_pers_criteria.COUNT_BY, rec_pers_criteria.SEARCH_QUERY_ID,
880 rec_pers_criteria.SEARCH_QUERY_NAME, rec_pers_criteria.DEFAULT_FLAG,
881 rec_pers_criteria.PUBLIC_FLAG, rec_pers_criteria.FROM_FIELD_VALUE,
882 rec_pers_criteria.TO_FIELD_VALUE,rec_pers_criteria.OBJECT_TYPE,
883 rec_pers_criteria.SOURCE_TYPE, rec_pers_criteria.ACTIVE_FLAG) ;
884 end loop;
885
886 for rec_among_criteria in c_among_criteria(p_query_id) loop
887 INSERT INTO msc_among_values
888 (FOLDER_ID,SEQUENCE,FIELD_NAME, OR_VALUES,
889 HIDDEN_VALUES,OBJECT_SEQUENCE,ORDER_BY_SEQUENCE,
890 CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE,
891 LAST_UPDATED_BY, LAST_UPDATE_LOGIN)
892 VALUES(l_save_as_query_id, rec_among_criteria.SEQUENCE,
893 rec_among_criteria.FIELD_NAME,
894 rec_among_criteria.OR_VALUES,
895 rec_among_criteria.HIDDEN_VALUES,
896 rec_among_criteria.OBJECT_SEQUENCE,
897 rec_among_criteria.ORDER_BY_SEQUENCE,
898 rec_among_criteria.CREATION_DATE,
899 rec_among_criteria.CREATED_BY,
900 rec_among_criteria.LAST_UPDATE_DATE,
901 rec_among_criteria.LAST_UPDATED_BY,
902 rec_among_criteria.LAST_UPDATE_LOGIN);
903 END LOOP;
904
905 INSERT INTO MSC_WORKLIST_GROUPBY(
906 QUERY_ID ,
907 ATTRIBUTE_NAME ,
908 CHAR1 ,
909 CHAR2 ,
910 CHAR3 ,
911 CHAR4 ,
912 SEQUENCE ,
913 LAST_UPDATE_DATE ,
914 LAST_UPDATED_BY ,
915 CREATION_DATE ,
916 CREATED_BY )
917 SELECT l_save_as_query_id ,
918 ATTRIBUTE_NAME ,
919 CHAR1 ,
920 CHAR2 ,
921 CHAR3 ,
922 CHAR4 ,
923 SEQUENCE ,
924 LAST_UPDATE_DATE ,
925 LAST_UPDATED_BY ,
926 CREATION_DATE ,
927 CREATED_BY
928 FROM MSC_WORKLIST_GROUPBY
929 WHERE query_id = p_query_id;
930 return l_save_as_query_id;
931 end copy_query;
932
933
934 Procedure purge_plan(p_plan_id IN NUMBER) is
935 l_sql_stmt varchar2(300);
936 l_share_partition varchar2(5):= fnd_profile.value('MSC_SHARE_PARTITIONS');
937 l_count number;
938
939 l_msc_schema varchar2(30);
940 l_status varchar2(50);
941 l_industry varchar2(50);
942 retval boolean;
943
944 begin
945 retval := fnd_installation.get_app_info_other('MSC', 'MSC',
946 l_status, l_industry, l_msc_schema);
947
948 if (not retval) then
949 return;
950 end if;
951
952 SELECT count(*) into l_count
953 from ALL_TAB_PARTITIONS
954 where TABLE_NAME = 'MSC_PQ_RESULTS' and TABLE_OWNER= l_msc_schema;
955
956 l_sql_stmt :=
957 'alter table '||l_msc_schema||'.msc_pq_results'||
958 ' truncate partition PQ_RESULTS_'||to_char(p_plan_id);
959 --5768202 partition name changed from pq_results_all_ to pq_results_
960
961 if l_share_partition = 'N' and l_count >0 then
962 EXECUTE IMMEDIATE l_sql_stmt;
963 else
964 delete from msc_pq_results
965 where plan_id = p_plan_id;
966 end if;
967 end purge_plan;
968
969 procedure delete_query(p_query_id in number,
970 p_query_name in varchar2 default null) is
971 PRAGMA AUTONOMOUS_TRANSACTION;
972 begin
973 delete from msc_personal_queries
974 where query_id = p_query_id;
975
976 delete from msc_pq_types
977 where query_id = p_query_id;
978
979 delete from msc_selection_criteria
980 where folder_id = p_query_id;
981
982 delete from msc_among_values
983 where folder_id = p_query_id;
984
985 delete from msc_pq_results
986 where query_id = p_query_id;
987
988 commit;
989 end delete_query;
990
991 procedure save_as(p_query_id in number,
992 p_query_name in varchar2,
993 p_query_desc in varchar2,
994 p_public_flag in number) is
995 p_new_query_id number;
996 BEGIN
997 p_new_query_id :=
998 copy_query(p_query_id,
999 p_query_name,
1000 p_query_desc,
1001 p_public_flag);
1002 END save_as;
1003
1004 FUNCTION save_as(p_query_id in number,
1005 p_query_name in varchar2,
1006 p_query_desc in varchar2,
1007 p_public_flag in number) return number is
1008 p_new_query_id number;
1009 BEGIN
1010 p_new_query_id :=
1011 copy_query(p_query_id,
1012 p_query_name,
1013 p_query_desc,
1014 p_public_flag);
1015
1016 return p_new_query_id;
1017 END save_as;
1018
1019 procedure Summarize_wklst_results( p_query_id IN NUMBER,
1020 p_plan_id IN NUMBER) IS
1021 l_sql_stmt varchar2(32000);
1022 l_Insert_stmt varchar2(32000);
1023 l_insert_begin varchar2(1000) := 'INSERT INTO MSC_PQ_RESULTS ('||
1024 ' QUERY_ID, PLAN_ID, ORGANIZATION_ID,'||
1025 ' SR_INSTANCE_ID, SUMMARY_DATA,';
1026 l_insert_end_new varchar2(1000) := 'sequence_id, created_by, creation_date,'||
1027 ' last_update_date, last_updated_by,'||
1028 ' last_update_login ) ';
1029
1030 l_new_group_by_cols1 varchar2(1000);
1031 l_new_group_by_cols2 varchar2(1000);
1032 l_new_group_by_cols3 varchar2(1000);
1033 l_new_group_by_cols4 varchar2(1000);
1034
1035 begin
1036 get_wl_groupby(p_query_id,l_new_group_by_cols1,l_new_group_by_cols2,l_new_group_by_cols3);
1037 --KSA_DEBUG(SYSDATE,' l_new_group_by_cols1 >> '||l_new_group_by_cols1,'Summarize_wklst_results');
1038 --KSA_DEBUG(SYSDATE,' l_new_group_by_cols2 >> '||l_new_group_by_cols2,'Summarize_wklst_results');
1039 IF l_new_group_by_cols2 IS NOT NULL THEN
1040 l_new_group_by_cols1 := ','||l_new_group_by_cols1;
1041 l_new_group_by_cols2 := ','||l_new_group_by_cols2;
1042 l_new_group_by_cols3 := ','||l_new_group_by_cols3;
1043 l_new_group_by_cols4 := ' GROUP BY MPR1.PLAN_ID,MPR1.SR_INSTANCE_ID, MPR1.ORGANIZATION_ID'||
1044 l_new_group_by_cols3||' MPR1.QUERY_ID';
1045 ELSE
1046 l_new_group_by_cols1 := ',';
1047 l_new_group_by_cols2 := ',';
1048 l_new_group_by_cols3 := ',';
1049 l_new_group_by_cols4 := ' GROUP BY MPR1.QUERY_ID, MPR1.PLAN_ID,MPR1.SR_INSTANCE_ID, MPR1.ORGANIZATION_ID';
1050 END IF;
1051 l_sql_stmt := ' Select mpr1.query_id, mpr1.plan_id,'||
1052 'mpr1.sr_instance_id, mpr1.organization_id,'||
1053 'sum(mpr1.exception_count) exception_count,'||
1054 'MIN(mpr1.priority) priority,'||1||
1055 l_new_group_by_cols3||
1056 fnd_global.user_id||', SYSDATE, '||
1057 fnd_global.user_id||', SYSDATE'||
1058 ' from ( select mpr.query_id, mpr.plan_id,'||
1059 'mpr.sr_instance_id, mpr.organization_id,'||
1060 'nvl(mpr.exception_count,1) exception_count,'||
1061 'mpr.priority'||
1062 l_new_group_by_cols1||
1063 '9999'||
1064 ' FROM msc_pq_results mpr '||
1065 ' WHERE mpr.query_id = '||p_query_id ||
1066 ' AND mpr.plan_id = '||p_plan_id ||
1067 ' AND mpr.summary_data = -99 ) mpr1 '||
1068 l_new_group_by_cols4;
1069
1070 l_Insert_stmt:= 'Insert into MSC_PQ_RESULTS ( '||
1071 'query_id, plan_id,'||
1072 'sr_instance_id, organization_id,'||
1073 'exception_count, priority, summary_data'||
1074 l_new_group_by_cols2||
1075 'created_by, creation_date, '||
1076 'last_updated_by,last_update_date)';
1077 msc_get_name.execute_dsql(l_Insert_stmt||l_sql_stmt);
1078
1079 exception
1080 when others then
1081 --KSA_DEBUG(SYSDATE,' l_Insert_stmt >> '||l_Insert_stmt,'Summarize_wklst_results');
1082 --KSA_DEBUG(SYSDATE,' l_sql_stmt >> '||l_sql_stmt,'Summarize_wklst_results');
1083 --KSA_DEBUG(SYSDATE,' Error >> '||sqlerrm(sqlcode),'Summarize_wklst_results');
1084 raise;
1085 end Summarize_wklst_results;
1086
1087 procedure get_wl_groupby(p_query_id number,
1088 p_groupby_cols1 in out nocopy varchar2,
1089 p_groupby_cols2 in out nocopy varchar2,
1090 p_groupby_cols3 in out nocopy varchar2) is
1091 l_new_group_by_col1 varchar2(300);
1092 l_new_group_by_col2 varchar2(300);
1093 l_new_group_by_col boolean := false;
1094 l_group_by varchar2(30);
1095
1096 CURSOR c_wl_groupby(l_query IN NUMBER) IS
1097 SELECT distinct ATTRIBUTE_NAME group_by
1098 FROM MSC_WORKLIST_GROUPBY
1099 WHERE QUERY_ID = l_query;
1100 begin
1101 OPEN c_wl_groupby (p_query_id) ;
1102 LOOP
1103 FETCH c_wl_groupby INTO l_group_by;
1104 EXIT WHEN c_wl_groupby%NOTFOUND;
1105 l_new_group_by_col := FALSE;
1106 IF l_group_by = 'ORGANIZATION_CODE' THEN
1107 l_new_group_by_col1 := 'GROUPBY_ORG';
1108 l_new_group_by_col1 := 'nvl(MPR.GROUPBY_ORG,
1109 msc_get_name.org_code
1110 (MPR.organization_id,
1111 MPR.sr_instance_id)) GROUPBY_ORG';
1112 l_new_group_by_col2 := 'GROUPBY_ORG';
1113 l_new_group_by_col := true;
1114 ELSIF l_group_by = 'ITEM_SEGMENTS' THEN
1115 l_new_group_by_col1 := 'MPR.INVENTORY_ITEM_ID';
1116 l_new_group_by_col2 := 'INVENTORY_ITEM_ID';
1117 l_new_group_by_col := TRUE;
1118 ELSIF l_group_by = 'CATEGORY_NAME' THEN
1119 l_new_group_by_col1 := 'MPR.CATEGORY_ID';
1120 l_new_group_by_col2 := 'CATEGORY_ID';
1121 l_new_group_by_col := TRUE;
1122 ELSIF l_group_by = 'PLANNER_CODE' THEN
1123 l_new_group_by_col1 := 'MPR.PLANNER_CODE';
1124 l_new_group_by_col2 := 'PLANNER_CODE';
1125 l_new_group_by_col := TRUE;
1126 ELSIF l_group_by = 'CUSTOMER_NAME' THEN
1127 l_new_group_by_col1 := 'MPR.CUSTOMER_ID';
1128 l_new_group_by_col2 := 'CUSTOMER_ID';
1129 l_new_group_by_col := TRUE;
1130 ELSIF l_group_by = 'CUSTOMER_SITE' THEN
1131 l_new_group_by_col1 := 'MPR.CUSTOMER_SITE_ID';
1132 l_new_group_by_col2 := 'CUSTOMER_SITE_ID';
1133 l_new_group_by_col := TRUE;
1134 ELSIF l_group_by = 'SUPPLIER_NAME' THEN
1135 l_new_group_by_col1 := 'MPR.SUPPLIER_ID';
1136 l_new_group_by_col2 := 'SUPPLIER_ID';
1137 l_new_group_by_col := TRUE;
1138 ELSIF l_group_by = 'EXCEPTION_TYPE' THEN
1139 l_new_group_by_col1 := 'MPR.SOURCE_TYPE,MPR.EXCEPTION_TYPE';
1140 l_new_group_by_col2 := 'SOURCE_TYPE,EXCEPTION_TYPE';
1141 l_new_group_by_col := TRUE;
1142 ELSIF l_group_by = 'SUPPLIER_SITE' THEN
1143 l_new_group_by_col1 := 'MPR.SUPPLIER_SITE_ID';
1144 l_new_group_by_col2 := 'SUPPLIER_SITE_ID';
1145 l_new_group_by_col := TRUE;
1146 END IF;
1147 IF (l_new_group_by_col) then
1148 if p_groupby_cols1 is null then
1149 --p_groupby_cols1 := l_new_group_by_col2||', ';
1150 p_groupby_cols1 := l_new_group_by_col1||', ';
1151 p_groupby_cols2 := l_new_group_by_col2||', ';
1152 IF l_group_by = 'EXCEPTION_TYPE' THEN
1153 p_groupby_cols3 := 'MPR1.SOURCE_TYPE,MPR1.EXCEPTION_TYPE, ';
1154 ELSE
1155 p_groupby_cols3 := 'MPR1.'||l_new_group_by_col2||', ';
1156 END IF;
1157 else
1158 p_groupby_cols1 := p_groupby_cols1||l_new_group_by_col1||', ';
1159 p_groupby_cols2 := p_groupby_cols2||l_new_group_by_col2||', ';
1160 IF l_group_by = 'EXCEPTION_TYPE' THEN
1161 p_groupby_cols3 := p_groupby_cols3||'MPR1.SOURCE_TYPE,MPR1.EXCEPTION_TYPE, ';
1162 ELSE
1163 p_groupby_cols3 := p_groupby_cols3||'MPR1.'||l_new_group_by_col2||', ';
1164 end if;
1165 end if;
1166 end if;
1167 end loop;
1168 close c_wl_groupby;
1169 end get_wl_groupby;
1170
1171 end MSC_pers_queries;