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