[Home] [Help]
PACKAGE BODY: APPS.MSC_ACTIONS
Source
1 PACKAGE BODY MSC_ACTIONS AS
2 /* $Header: MSCPACTB.pls 120.4 2011/02/25 04:48:28 skathera ship $ */
3
4 ---------------------------------------------------------------
5 -- insert into msc_form_query the selected exception counts
6 ---------------------------------------------------------------
7 PROCEDURE group_by(
8 l_plan_node NUMBER,
9 var_exception_id NUMBER,
10 count_list VARCHAR2, count_list_mfq VARCHAR2,
11 where_clause VARCHAR2,
12 p_plan_id NUMBER,
13 p_inst_id NUMBER,
14 p_org_id NUMBER,
15 p_item_id NUMBER,
16 p_planning_grp VARCHAR2,
17 p_project_id NUMBER,
18 p_task_id NUMBER,
19 p_category_name varchar2,
20 p_pf_id NUMBER,
21 p_dept_id NUMBER,
22 p_resource_id NUMBER,
23 p_supplier_id NUMBER,
24 p_version NUMBER,
25 p_exc_grp_id NUMBER,
26 p_exception_id NUMBER,
27 p_dept_class VARCHAR2,
28 p_res_group VARCHAR2,
29 p_supplier_site_id NUMBER) IS
30
31 statement varchar2(20000);
32 l_exc_grp_id NUMBER := NULL;
33 v_date date;
34 v_days number:=0;
35 v_cat_id number;
36
37 CURSOR date_cur IS
38 SELECT p.plan_start_date, p.plan_type, p.curr_cutoff_date
39 FROM msc_plans p
40 WHERE p.plan_id = p_plan_id;
41
42 CURSOR cat_cur IS
43 SELECT category_set_id
44 FROM msc_category_sets
45 WHERE default_flag = 1;
46
47 CURSOR inst_c IS
48 SELECT distinct sr_instance_id
49 from msc_plan_organizations
50 WHERE plan_id = p_plan_id;
51 v_inst_list varchar2(3000);
52 v_id number;
53 v_plan_type number;
54 ship_stat varchar2(3000);
55 v_ship_cat_id number;
56 startPos number;
57 endPos number;
58 a number :=0;
59 l_len number;
60 ship_count_list varchar2(3000);
61 ship_list_mfq varchar2(3000);
62 p_column varchar2(200);
63 l_def_pref_id number;
64 v_plan_end_date date;
65 BEGIN
66
67 statement :=
68 ' INSERT INTO msc_form_query' ||
69 '(query_id, ' ||
70 'last_update_date, ' ||
71 'last_updated_by, ' ||
72 'last_update_login, ' ||
73 'creation_date, ' ||
74 'created_by, ' ||
75 count_list_mfq || ' ,number16, number5, number13) ' ||
76 ' SELECT :query_id ' ||
77 ', TRUNC(SYSDATE),' ||
78 '-1, -1,' ||
79 'TRUNC(SYSDATE),' ||
80 '-1, ' ||
81 count_list || ' ,exception_group, sum(exception_count), sum(nvl(new_exception_count,exception_count)) ';
82
83 l_def_pref_id := msc_get_name.get_default_pref_id(fnd_global.user_id);
84 v_cat_id:= msc_get_name.GET_preference('CATEGORY_SET_ID',l_def_pref_id, v_plan_type);
85 v_days := msc_get_name.get_preference('RECOMMENDATION_DAYS',l_def_pref_id, v_plan_type);
86
87 v_ship_cat_id := v_cat_id;
88
89 OPEN date_cur;
90 FETCH date_cur INTO v_date, v_plan_type, v_plan_end_date;
91 CLOSE date_cur;
92
93
94 if v_days is null then
95 v_days := 30;
96 end if;
97
98 if v_plan_type in (8,9) then
99 v_date := v_plan_end_date;
100 else
101 v_date := v_date + v_days;
102 end if;
103
104 if v_cat_id is null then
105 OPEN cat_cur;
106 FETCH cat_cur INTO v_cat_id;
107 CLOSE cat_cur;
108
109 end if;
110
111 if (l_plan_node = 1 or
112 (p_dept_class is null and
113 p_res_group is null ))
114 and l_plan_node <>3
115 and where_clause is null then
116
117 if l_plan_node = 1 then
118 v_cat_id := -1;
119 statement := statement || ' FROM msc_item_exception_v1 '||
120 ' WHERE 1=1 '||
121 ' AND (schedule_date is null or '||
122 ' schedule_date <= :the_date) ' ||
123 ' AND -1 = :v_cat_id '||
124 ' AND plan_id = :plan_id ';
125 else
126
127 open inst_c;
128 loop
129 fetch inst_c into v_id;
130 exit when inst_c%NOTFOUND;
131 if v_inst_list is null then
132 v_inst_list := v_id;
133 else
134 v_inst_list := v_inst_list || ','|| v_id;
135 end if;
136 end loop;
137 close inst_c;
138
139 if v_inst_list is not null then
140 v_inst_list := 'AND sr_instance_id in ('||v_inst_list||')';
141 end if;
142 statement := statement || ' FROM msc_item_exception_v2 '||
143 ' WHERE 1=1 '|| v_inst_list ||
144 ' AND (schedule_date is null or '||
145 ' schedule_date <= :the_date) ' ||
146 ' AND (category_set_id is null or '||
147 ' category_set_id = :v_cat_id) '||
148 ' AND plan_id = :plan_id ';
149 end if;
150
151 else
152
153 statement := statement || ' FROM msc_item_exception_v '||
154 ' WHERE 1=1 '||where_clause ||
155 ' AND trunc(sysdate) = :the_date ';
156
157 v_date := trunc(sysdate);
158 if instr(where_clause,'PLANNER_CODE') > 0 or
159 instr(where_clause,'BUYER_NAME') > 0 or
160 instr(where_clause,'ITEM_SEGMENTS') > 0 or
161 instr(where_clause,'CATEGORY_NAME') > 0 or
162 instr(where_clause,'SUPPLIER_NAME') > 0 then
163 statement := statement ||
164 ' AND category_set_id = :v_cat_id ';
165 else
166 statement := statement ||
167 ' AND (category_set_id is null or '||
168 ' category_set_id = :v_cat_id) ';
169 end if;
170 statement := statement ||
171 ' AND plan_id = :plan_id ';
172 end if;
173
174 IF p_org_id IS NOT NULL THEN
175 statement := statement || ' AND organization_id = :org_id '||
176 ' AND sr_instance_id = :inst_id ';
177 ship_stat := ' AND organization_id = '||p_org_id;
178 ELSE
179 statement := statement || ' AND NVL(:org_id,1) = 1 ' ||
180 ' AND NVL(:inst_id,1) = 1 ';
181 END IF;
182 IF p_item_id IS NOT NULL THEN
183 statement := statement || ' AND inventory_item_id = :item_id ';
184 ship_stat := ship_stat || ' AND inventory_item_id = '||p_item_id;
185 ELSE
186 statement := statement || ' AND NVL(:item_id,1) = 1 ';
187 END IF;
188 IF p_planning_grp = '''_COMMON''' THEN
189 statement := statement || ' AND planning_group IS NULL '||
190 ' AND project_id IS NULL ' ||
191 ' AND rtrim(ltrim(:planning_grp,''''''''),'''''''') = ''_COMMON'' ';
192 ELSIF p_planning_grp = '_NONE' THEN
193 statement := statement || ' AND planning_group IS NULL '||
194 ' AND project_id IS NOT NULL ' ||
195 ' AND :planning_grp = ''_NONE'' ';
196 ELSIF p_planning_grp IS NOT NULL THEN
197 statement := statement || ' AND planning_group = rtrim(ltrim(:planning_grp,''''''''),'''''''') ';
198 ELSE
199 statement := statement || ' AND NVL(:planning_grp,''A'') = ''A'' ';
200 END IF;
201 IF p_project_id IS NOT NULL THEN
202 statement := statement || ' AND project_id = :project_id ';
203 ELSE
204 statement := statement || ' AND NVL(:project_id,1) = 1 ';
205 END IF;
206 IF p_task_id IS NOT NULL THEN
207 statement := statement || ' AND task_id = :task_id ';
208 ELSE
209 statement := statement || ' AND NVL(:task_id,1) = 1 ';
210 END IF;
211 IF p_category_name IS NOT NULL THEN
212 statement := statement || ' AND category_name = :category_name ';
213 ship_stat := ship_stat || ' AND category_name = '||'''||
214 p_category_name||''';
215 ELSE
216 statement := statement || ' AND NVL(:category_name,''A'') = ''A'' ';
217 END IF;
218 IF p_pf_id IS NOT NULL THEN
219 statement := statement || ' AND (product_family_id = :pf_id OR inventory_item_id = :pf_id) ';
220 ELSE
221 statement := statement || ' AND NVL(:pf_id,1) = 1 AND NVL(:pf_id,1) = 1';
222 END IF;
223 IF p_dept_id IS NOT NULL THEN
224 statement := statement || ' AND department_id = :dept_id ';
225 ELSE
226 statement := statement || ' AND NVL(:dept_id,1) = 1 ';
227 END IF;
228 IF p_resource_id IS NOT NULL THEN
229 statement := statement || ' AND resource_id = :resource_id ';
230 ELSE
231 statement := statement || ' AND NVL(:resource_id,1) = 1 ';
232 END IF;
233 IF p_supplier_id IS NOT NULL THEN
234 statement := statement || ' AND supplier_id = :supplier_id ';
235 ship_stat := ship_stat || ' AND supplier_id = '||p_supplier_id;
236 ELSE
237 statement := statement || ' AND NVL(:supplier_id,1) = 1 ';
238 END IF;
239
240 IF p_supplier_site_id IS NOT NULL THEN
241 statement := statement || ' AND supplier_site_id = :supplier_site_id ';
242 ELSE
243 statement := statement || ' AND NVL(:supplier_site_id,1) = 1 ';
244 END IF;
245
246 IF p_version IS NOT NULL THEN
247 IF p_version = -1 THEN
248 statement := statement || ' AND version IS NULL ' ||
249 ' AND :version = -1 ';
250 ELSE
251 statement := statement || ' AND version = :version ';
252 END IF;
253 ELSE
254 statement := statement || ' AND NVL(:version,1) = 1 ';
255 END IF;
256 IF p_exception_id IS NOT NULL THEN
257 IF p_exc_grp_id IS NULL THEN
258 l_exc_grp_id := 10;
259 ELSE
260 l_exc_grp_id := p_exc_grp_id;
261 END IF;
262 statement := statement || ' AND exception_group = :exc_grp_id ' ||
263 ' AND exception_type = :exception_id ';
264 ELSIF p_exc_grp_id IS NOT NULL THEN
265 l_exc_grp_id := p_exc_grp_id;
266 statement := statement || ' AND exception_group = :exc_grp_id '||
270 ' AND NVL(:exception_id,1) = 1 ';
267 ' AND NVL(:exception_id,1) = 1 ';
268 ELSE
269 statement := statement || ' AND NVL(:exc_grp_id,1) = 1 '||
271 END IF;
272 IF p_dept_class IN ('''@@@''', '@@@') THEN
273 --IF p_dept_class = '''@@@''' THEN
274 statement := statement || ' AND department_class IS NULL '||
275 ' AND rtrim(ltrim(:dept_class,''''''''),'''''''') = ''@@@'' '||
276 ' AND (department_id IS NOT NULL AND department_id <> -1 '||
277 ' AND resource_id <> -1 ) ';
278 ELSIF p_dept_class IS NOT NULL THEN
279 statement := statement || ' AND department_class = rtrim(ltrim(:dept_class,''''''''),'''''''') ';
280 ELSE
281 statement := statement || ' AND NVL(:dept_class,''A'') = ''A'' ';
282 END IF;
283 IF p_res_group = '''@@@''' THEN
284 statement := statement || ' AND resource_group IS NULL '||
285 ' AND rtrim(ltrim(:res_group,''''''''),'''''''') = ''@@@'' '||
286 ' AND (department_id IS NOT NULL AND department_id <> -1 '||
287 ' AND resource_id <> -1 ) ';
288 ELSIF p_res_group IS NOT NULL THEN
289 statement := statement || ' AND resource_group = rtrim(ltrim(:res_group,''''''''),'''''''') ';
290 ELSE
291 statement := statement || ' AND NVL(:res_group,''A'') = ''A'' ';
292 END IF;
293 statement := statement ||
294 ' GROUP BY plan_id, exception_group,' ||
295 count_list || ',1';
296
297 EXECUTE IMMEDIATE statement
298 USING var_exception_id, v_date,v_cat_id,
299 p_plan_id, p_org_id, p_inst_id, p_item_id,
300 p_planning_grp, p_project_id, p_task_id, p_category_name,
301 p_pf_id, p_pf_id, p_dept_id, p_resource_id, p_supplier_id,p_supplier_site_id,
302 p_version, l_exc_grp_id, p_exception_id,
303 p_dept_class, p_res_group;
304
305 if v_plan_type = 5 then
306 if count_list <> 'VERSION,EXCEPTION_TYPE,PLAN_ID' then
307 a :=1;
308 startPos :=1;
309 endPos := instr(count_list||',', ',',1,a);
310 while endPos >0 loop
311 l_len := endPos - startPos;
312 p_column := substr(count_list||',',startPos, l_len);
313 if p_column in ('SR_INSTANCE_ID','ORGANIZATION_ID','PLANNER_CODE',
314 'BUYER_NAME','INVENTORY_ITEM_ID','CATEGORY_ID',
315 'CATEGORY_NAME','SUPPLIER_ID') then
316 ship_count_list := ship_count_list ||','|| p_column;
317 startPos := instr(count_list_mfq||',', ',',1,a-1)+1;
318 l_len := instr(count_list_mfq||',', ',',1,a) - startPos;
319 p_column := substr(count_list_mfq||',',startPos, l_len);
320 ship_list_mfq := ship_list_mfq || ','|| p_column;
321 end if;
322 a := a+1;
323 startPos := endPos+1;
324 endPos := instr(count_list||',', ',',1,a);
325 end loop;
326 end if; -- if count_list <> 'VERSION,EXCEPTION_TYPE,PLAN_ID' then
327
328 statement :=
329 ' insert into msc_form_query (query_id, '||
330 ' last_update_date, last_updated_by, '||
334 ' select :var_exception_id,trunc(sysdate),-1,trunc(sysdate),-1,-1, '||
331 ' creation_date, created_by, last_update_login, '||
332 ' NUMBER2,NUMBER14, '||
333 ' number16, number5, number13 '||ship_list_mfq ||') ' ||
335 ' 100,PLAN_ID, 10, count(distinct shipment_id), count(distinct shipment_id) '||ship_count_list ||
336 ' from msc_shipment_details_v '||
337 ' where plan_id = :p_plan_id '||
338 ' and category_set_id = :v_cat_id '||
339 ship_stat ||
340 ' group by plan_id '||ship_count_list;
341
342 EXECUTE IMMEDIATE statement
343 USING var_exception_id, p_plan_id, v_ship_cat_id;
344
345 end if; -- if v_plan_type = 5 then
346
347 -- commit;
348
349 EXCEPTION
353 raise_application_error(-20000,sqlerrm||':'||statement);
350 when no_data_found
351 then null;
352 when others then
354 END;
355
356 PROCEDURE insert_exc_groups(var_exception_id NUMBER) IS
357
358 statement varchar2(20000);
359
360 BEGIN
361 -- The following SQL statement adds rows for exception group into
362 -- the temp table.
363 statement :=
364 ' INSERT INTO msc_form_query' ||
365 '(query_id, ' ||
366 'last_update_date, ' ||
367 'last_updated_by, ' ||
368 'last_update_login, ' ||
369 'creation_date, ' ||
370 'created_by, ' ||
371 -- 'char1, ' || -- plan name
372 'number14, ' || -- plan id
373 'number1, ' || -- version
374 'number2, ' || -- exception type
375 'number16, ' || -- exception group
376 'number5, ' || -- exception count
377 'number3, ' || -- item id
378 'number11, ' || -- pf id
379 'number4, ' || -- org id
380 'number15, ' || -- sr instance id
381 'number6, ' || -- project id
382 'number7, ' || -- task id
383 'number8, ' || -- category id
384 'number9, ' || -- dept id
385 'number10, ' || -- res id
386 'number12, ' || -- supplier id
387 'number13, ' || -- new exception count
388 'request_id, ' || -- supplier site id
389 'char2, ' || --planner code
390 'char3, ' || -- dept class
391 'char4, ' || -- planning group
392 'char5, ' || -- item name
393 'char6, ' || -- org code
394 'char7, ' || -- dept code
395 'char8, ' || -- res group
396 'char9, ' || -- buyer
397 'char10, ' || -- res code
398 'char11, ' || -- res type code
399 'char12, ' || --?
400 'char13, ' || -- version text
401 'char14) ' || -- category name
402 ' SELECT ' ||
403 'query_id, ' ||
404 'last_update_date, ' ||
405 'last_updated_by, ' ||
406 'last_update_login, ' ||
407 'creation_date, ' ||
408 'created_by, ' ||
409 -- 'char1, ' ||
410 'number14, ' ||
411 'number1, ' ||
412 'number16, ' ||
413 'NULL, ' ||
414 'sum(number5), '||
415 'number3, ' ||
416 'number11, ' ||
417 'number4, ' ||
418 'number15, ' ||
419 'number6, ' ||
420 'number7, ' ||
421 'number8, ' ||
422 'number9, ' ||
423 'number10, ' ||
424 'number12, ' ||
425 'sum(number13), ' ||
426 'request_id, ' ||
427 'char2, ' ||
428 'char3, ' ||
429 'char4, ' ||
430 'char5, ' ||
431 'char6, ' ||
432 'char7, ' ||
433 'char8, ' ||
434 'char9, ' ||
435 'char10, ' ||
436 'char11, ' ||
437 'char12, ' ||
438 'char13, ' ||
439 'char14 ' ||
440 ' FROM msc_form_query ' ||
441 ' WHERE query_id = :query_id '||
442 ' GROUP BY query_id, last_update_login, last_updated_by, '||
443 ' creation_date, created_by, last_update_date, ' ||
444 ' char1, number14, number1, number16, number3, number11, '||
445 ' number4, number15, number6, number7, number8, number9, number10, '||
446 ' number12, request_id, char2, char3, char4, char5, '||
447 ' char6, char7, char8, char9, char10, char11, char12, char13, char14 ';
448
449 EXECUTE IMMEDIATE statement
450 USING var_exception_id;
451 -- commit;
452
453 EXCEPTION
454 when no_data_found
455 then null;
456 when others then
457 raise_application_error(-20000,sqlerrm||':'||statement);
458 END;
459
460 ---------------------------------------------------------------------
461 -- to save the current exception summary into msc_item_exceptions
462 ---------------------------------------------------------------------
463 FUNCTION save_as(plan NUMBER) RETURN NUMBER IS
464 version_id NUMBER;
465 BEGIN
466
467 SELECT MAX(NVL(version,0))+1
468 INTO version_id
469 FROM msc_item_exceptions
470 WHERE plan_id= plan;
471
472 INSERT INTO msc_item_exceptions
473 (exception_type, inventory_item_id, plan_id, sr_instance_id,
474 organization_id, last_update_date, last_updated_by,
475 creation_date, created_by, last_update_login, display, request_id,
476 program_application_id, program_id,
477 program_update_date, exception_count, project_id,
478 task_id, version, planning_group,
479 department_id, resource_id, exception_group)
480 SELECT exception_type, inventory_item_id, plan_id, sr_instance_id,
481 organization_id, last_update_date, last_updated_by,
482 creation_date, created_by, last_update_login, display, request_id,
483 program_application_id, program_id,
484 program_update_date, exception_count, project_Id,
485 task_id, version_id, planning_group,
486 department_id, resource_id, exception_group
487 FROM msc_item_exceptions
488 WHERE version is null
489 AND plan_id= plan;
490 commit;
491 Return version_id;
492 END save_as;
493
494 ---------------------------------------------------------------------
495 -- update mrp-item-exceptions and msc_form_query tables
496 ---------------------------------------------------------------------
497 PROCEDURE update_row(p_exception_id number,
498 p_omit_list VARCHAR2,
499 p_row_id VARCHAR2,
500 p_last_update_login NUMBER,
501 p_last_updated_by NUMBER) IS
502 c integer;
503 statement varchar2(2000);
504 rows_processed integer;
505 BEGIN
506
507 -- update msc_item_exceptions by the their rowid (stored as char8 in
511 statement :=
508 -- msc_form_query) by applying the criteria on the second insert
509 -- of msc_form_query
510
512 'update msc_item_exceptions
513 set display = 2,
514 last_update_login = :last_update_login,
515 last_updated_by = :last_updated_by,
516 last_update_date = sysdate
517 where rowid in (
518 select chartorowid(char8)
519 from msc_form_query
520 where ' ||p_omit_list||
521 ' and query_id = :exception_id )';
522
523 c := dbms_sql.open_cursor;
524 dbms_sql.parse(c, statement, dbms_sql.native);
525 dbms_sql.bind_variable(c,'last_update_login',p_last_update_login);
526 dbms_sql.bind_variable(c,'last_updated_by',p_last_updated_by);
527 dbms_sql.bind_variable(c,'exception_id',p_exception_id);
528 rows_processed := dbms_sql.execute(c);
529 dbms_sql.close_cursor(c);
530
531 Exception
532 When no_data_found Then
533 raise no_data_found;
534
535 END update_row;
536 ---------------------------------------------------------------------
537 -- to lock the row in msc_item_exceptions
538 -- if the row is deleted, return 1. if the data is changed, return 2.
539 -- if the row is locked successfully, return 3.
540 ---------------------------------------------------------------------
541 FUNCTION lock_row(p_exception_id number, p_omit_list VARCHAR2)
542 RETURN NUMBER IS
543 c integer;
544 statement varchar2(20000);
545 rows_processed integer;
546 display_flag NUMBER;
547 counter NUMBER;
548 BEGIN
549
550 -- lock the row in msc_item_exceptions by the their rowid (stored as char8 in
551 -- msc_form_query) by applying the criteria on the second insert
552 -- of msc_form_query
553
554 statement :=
555 'select display
556 from msc_item_exceptions
557 where rowid in (
558 select chartorowid(char8)
559 from msc_form_query
560 where ' ||p_omit_list||
561 ' and query_id = :exception_id )'||
562 ' for update of display nowait ';
563
564 c := dbms_sql.open_cursor;
565 dbms_sql.parse(c, statement, dbms_sql.native);
566 dbms_sql.define_column(c, 1, display_flag);
567 dbms_sql.bind_variable(c,'exception_id',p_exception_id);
568 rows_processed := dbms_sql.execute(c);
569 counter :=0;
570
571 Loop
572 IF (dbms_sql.fetch_rows(c) >0) THEN
573 dbms_sql.column_value(c, 1, display_flag);
574 IF display_flag <> 1 THEN
575 -- record already changed
576 return 2;
577 END IF;
578 counter :=counter+1;
579 ELSE
580 exit;
581 END IF;
582 END Loop;
583 dbms_sql.close_cursor(c);
584 IF counter = 0 THEN
585 -- no matching records , record already deleted
586 return 1;
587 ELSE
588 -- with matching records, and the display flag is not changed yet
589 return 3;
590 END IF;
591
592
593 EXCEPTION
594 WHEN others THEN
595 --can not lock the record, because the record is locked by other users already
596 IF dbms_sql.is_open(c) THEN
597 dbms_sql.close_cursor(c);
598 END IF;
599 raise_application_error(-20020,sqlerrm||':'||statement);
600
601 END lock_row;
602
603 END MSC_ACTIONS;