[Home] [Help]
PACKAGE BODY: APPS.MRP_EXP_WF
Source
1 PACKAGE BODY mrp_exp_wf AS
2 /*$Header: MRPEXWFB.pls 120.1 2005/08/29 07:15:29 gmalhotr noship $ */
3
4 PROCEDURE launch_workflow(errbuf OUT NOCOPY VARCHAR2,
5 retcode OUT NOCOPY NUMBER,
6 p_owning_org_id IN NUMBER,
7 p_designator IN VARCHAR2) IS
8
9 CURSOR EXCEPTION_DETAILS_C IS
10 SELECT exp.exception_id,
11 exp.organization_id,
12 exp.inventory_item_id,
13 exp.exception_type,
14 exp.organization_code,
15 exp.item_segments,
16 exp.exception_type_text,
17 NVL(exp.project_number, 'N/A'),
18 NVL(exp.to_project_number, 'N/A'),
19 NVL(exp.task_number, 'N/A'),
20 NVL(exp.to_task_number, 'N/A'),
21 exp.planning_group,
22 exp.due_date,
23 exp.from_date,
24 exp.to_date,
25 exp.days_compressed,
26 exp.quantity,
27 exp.lot_number,
28 exp.order_number,
29 exp.supply_type,
30 exp.end_item_segments,
31 exp.end_order_number,
32 exp.department_line_code,
33 exp.resource_code,
34 exp.utilization_rate
35 FROM mrp_exception_details_v exp, mrp_plan_organizations_v mpo
36 WHERE exp.exception_type in (1,2,3,6,7,8,9,10,12,13,14,15,16,17,18,19,20)
37 AND mpo.organization_id = p_owning_org_id
38 AND mpo.compile_designator = p_designator
39 AND exp.organization_id = mpo.planned_organization
40 AND exp.compile_designator = mpo.compile_designator
41 ORDER BY exp.exception_id;
42
43 CURSOR SUPPLIER_C(p_exception_id in number) IS
44 SELECT vend.vendor_id,
45 vend.vendor_name
46 FROM po_vendors vend,
47 mrp_recommendations rec,
48 mrp_exception_details_v exp
49 WHERE vend.vendor_id = rec.vendor_id
50 AND rec.transaction_id = exp.transaction_id
51 AND exp.exception_id = p_exception_id;
52
53 CURSOR SUPPLIER_SITE_C(p_exception_id in number) IS
54 SELECT vend.vendor_id,
55 vend.vendor_name,
56 site.vendor_site_id,
57 site.vendor_site_code
58 FROM po_vendor_sites_all site,
59 po_headers_all poh,
60 po_vendors vend,
61 mrp_item_purchase_orders ipo,
62 mrp_recommendations rec,
63 mrp_exception_details_v exp
64 WHERE site.vendor_site_id = poh.vendor_site_id
65 AND site.org_id = poh.org_id
66 AND poh.po_header_id = ipo.purchase_order_id
67 AND ipo.transaction_id = rec.disposition_id
68 AND vend.vendor_id = rec.vendor_id
69 AND rec.transaction_id = exp.transaction_id
70 AND exp.exception_id = p_exception_id;
71
72 CURSOR CUSTOMER_C(p_exception_id in number) IS
73 SELECT cust.cust_account_id,
74 part.party_name
75 FROM hz_cust_accounts cust,
76 hz_parties part,
77 oe_order_headers_all soh,
78 oe_order_types_v sot,
79 mtl_sales_orders mso,
80 mrp_schedule_dates dates,
81 mrp_gross_requirements mgr,
82 mrp_exception_details_v exp
83 WHERE cust.cust_account_id = soh.sold_to_org_id
84 AND soh.order_number = to_number(mso.segment1)
85 AND soh.order_type_id = sot.order_type_id
86 AND sot.name = mso.segment2
87 AND mso.sales_order_id = dates.source_sales_order_id
88 AND dates.supply_demand_type = 1
89 AND dates.schedule_level = 3
90 AND dates.mps_transaction_id = mgr.disposition_id
91 AND mgr.origination_type = 6
92 AND mgr.demand_id = exp.demand_id
93 AND exp.exception_id = p_exception_id
94 AND cust.party_id = part.party_id;
95
96 l_cursor varchar2(30);
97
98 l_exception_id number;
99 l_organization_id number;
100 l_inventory_item_id number;
101 l_exception_type number;
102 l_organization_code varchar2(3);
103 l_item_segments varchar2(40);
104 l_exception_type_text varchar2(80);
105 l_project_number varchar2(4000);
106 l_to_project_number varchar2(4000);
107 l_task_number varchar2(4000);
108 l_to_task_number varchar2(4000);
109 l_planning_group varchar2(80);
110 l_due_date date;
111 l_from_date date;
112 l_to_date date;
113 l_days_compressed number;
114 l_quantity varchar2(40);
115 l_lot_number varchar2(30);
116 l_order_number varchar2(4000);
117 l_order_type_code number := to_number(NULL);
118 l_supply_type varchar2(80);
119 l_end_item_segments varchar2(40);
120 l_end_order_number varchar2(4000);
121 l_department_line_code varchar2(10);
122 l_resource_code varchar2(10);
123 l_utilization_rate number;
124 l_vendor_id number := to_number(NULL);
125 l_vendor_name varchar2(240) := 'N/A'; -- UTF8 Change
126 l_vendor_site_id number := to_number(NULL);
127 l_vendor_site_code varchar2(15) := 'N/A';
128 l_customer_id number := to_number(NULL);
129 l_customer_name varchar2(80) := 'N/A';
130 l_workflow_process varchar2(40);
131 l_plan_type number;
132 l_org_selection number;
133 l_workbench_function varchar2(30);
134 l_min_exception_id number;
135 l_max_exception_id number;
136 l_counter number := 1;
137
138 BEGIN
139
140 -- Cancel notifications from previous plan run and force completion of
141 -- workflows.
142
143 DeleteActivities(p_designator, p_owning_org_id);
144
145 l_cursor := 'EXCEPTION_DETAILS_C';
146 OPEN EXCEPTION_DETAILS_C;
147 LOOP
148 FETCH EXCEPTION_DETAILS_C INTO
149 l_exception_id,
150 l_organization_id,
151 l_inventory_item_id,
152 l_exception_type,
153 l_organization_code,
154 l_item_segments,
155 l_exception_type_text,
156 l_project_number,
157 l_to_project_number,
158 l_task_number,
159 l_to_task_number,
160 l_planning_group,
161 l_due_date,
162 l_from_date,
163 l_to_date,
164 l_days_compressed,
165 l_quantity,
166 l_lot_number,
167 l_order_number,
168 l_supply_type,
169 l_end_item_segments,
170 l_end_order_number,
171 l_department_line_code,
172 l_resource_code,
173 l_utilization_rate;
174 EXIT WHEN EXCEPTION_DETAILS_C%NOTFOUND OR EXCEPTION_DETAILS_C%NOTFOUND IS NULL;
175
176 /***** Bug 2410989 ****/
177 l_vendor_id := NULL;
178 l_vendor_name := NULL;
179 l_vendor_site_id := NULL;
180 l_vendor_site_code := NULL;
181 l_customer_id := NULL;
182 l_customer_name := NULL;
183 /**** Bug 2410989 ****/
184
185 if (l_counter = 1) then
186
187 l_min_exception_id := l_exception_id;
188
189 end if;
190
191 if (l_exception_type in (1, 2, 3, 12, 14, 16, 20)) then
192
193 l_workflow_process := 'EXCEPTION_PROCESS1';
194
195 elsif (l_exception_type in (6, 7, 8, 9, 10)) then
196
197 l_workflow_process := 'EXCEPTION_PROCESS2';
198
199 SELECT rec.order_type
200 INTO l_order_type_code
201 FROM mrp_recommendations rec,
202 mrp_exception_details_v exp
203 WHERE rec.transaction_id = exp.transaction_id
204 AND exp.exception_id = l_exception_id;
205
206 -- Purchase Order
207 if (l_order_type_code = 1) then
208
209 l_cursor := 'SUPPLIER_SITE_C';
210 OPEN SUPPLIER_SITE_C(l_exception_id);
211 LOOP
212 FETCH SUPPLIER_SITE_C INTO
213 l_vendor_id,
214 l_vendor_name,
215 l_vendor_site_id,
216 l_vendor_site_code;
217 EXIT WHEN SUPPLIER_SITE_C%NOTFOUND OR SUPPLIER_SITE_C%NOTFOUND IS NULL;
218 END LOOP;
219 CLOSE SUPPLIER_SITE_C;
220
221 -- Purchase Requisition
222 elsif (l_order_type_code = 2) then
223
224 l_cursor := 'SUPPLIER_C';
225 OPEN SUPPLIER_C(l_exception_id);
226 LOOP
227 FETCH SUPPLIER_C INTO
228 l_vendor_id,
229 l_vendor_name;
230 EXIT WHEN SUPPLIER_C%NOTFOUND OR SUPPLIER_C%NOTFOUND IS NULL;
231 END LOOP;
232 CLOSE SUPPLIER_C;
233
234 end if;
235
236 elsif (l_exception_type in (13, 15)) then
237
238 l_workflow_process := 'EXCEPTION_PROCESS3';
239
240 l_cursor := 'CUSTOMER_C';
241 OPEN CUSTOMER_C(l_exception_id);
242 LOOP
243 FETCH CUSTOMER_C INTO
244 l_customer_id,
245 l_customer_name;
246 EXIT WHEN CUSTOMER_C%NOTFOUND OR CUSTOMER_C%NOTFOUND IS NULL;
247 END LOOP;
248 CLOSE CUSTOMER_C;
249
250 elsif (l_exception_type in (17, 18, 19)) then
251
252 l_workflow_process := 'EXCEPTION_PROCESS4';
253
254 end if;
255
256 -- Determine planner workbench function to be launched from notifications
257
258 l_cursor := 'WORKBENCH_C';
259 SELECT NVL(plan_type, curr_plan_type),
260 NVL(organization_selection, 1)
261 INTO l_plan_type,
262 l_org_selection
263 FROM mrp_plans
264 WHERE organization_id = p_owning_org_id
265 AND compile_designator = p_designator;
266
267 if (l_org_selection = 1) then -- single org
268
269 if (l_plan_type = 1) then -- MRP plan
270 l_workbench_function := 'MRPFPPWB-390';
271
272 else -- MPS plan
273 l_workbench_function := 'MRPFPPWB-392';
274
275 end if;
276
277 else -- multi org
278
279 if (l_plan_type = 1) then -- MRP plan
280 l_workbench_function := 'MRPFPPWB-394';
281
282 elsif (l_plan_type = 2) then -- MPS plan
283 l_workbench_function := 'MRPFPPWB-396';
284
285 else -- DRP plan
286 l_workbench_function := 'MRPFPPWB-398';
287
288 end if;
289
290 end if;
291
292
293 l_cursor := 'StartWFProcess';
294
295 StartWFProcess( 'MRPEXPWF',
296 to_char(l_exception_id),
297 p_designator,
298 l_organization_id,
299 l_inventory_item_id,
300 l_exception_type,
301 l_organization_code,
302 l_item_segments,
303 l_exception_type_text,
304 l_project_number,
305 l_to_project_number,
306 l_task_number,
307 l_to_task_number,
308 l_planning_group,
309 l_due_date,
310 l_from_date,
311 l_to_date,
312 l_days_compressed,
313 l_quantity,
314 l_lot_number,
315 l_order_number,
316 l_order_type_code,
317 l_supply_type,
318 l_end_item_segments,
319 l_end_order_number,
320 l_department_line_code,
321 l_resource_code,
322 l_utilization_rate,
323 l_vendor_id,
324 l_vendor_name,
325 l_vendor_site_id,
326 l_vendor_site_code,
327 l_customer_id,
328 l_customer_name,
329 l_workbench_function,
330 l_workflow_process);
331
332 l_counter := l_counter + 1;
333
334 END LOOP;
335
336 l_max_exception_id := l_exception_id;
337
338 CLOSE EXCEPTION_DETAILS_C;
339
340 UPDATE mrp_plans
341 SET min_wf_except_id = l_min_exception_id,
342 max_wf_except_id = l_max_exception_id
343 WHERE organization_id = p_owning_org_id
344 AND compile_designator = p_designator;
345
346 COMMIT WORK;
347
348 retcode := 0;
349
350 l_cursor := 'End';
351
352 EXCEPTION
353
354 WHEN NO_DATA_FOUND THEN
355 null;
356
357 WHEN OTHERS THEN
358 errbuf := 'Error in mrp_exp_wf.launch_workflow function' ||
359 ' Cursor: ' || l_cursor || ' Exception ID: '
360 || l_exception_id ||
361 ' SQL error: ' || sqlerrm;
362 retcode := 1;
363
364 END launch_workflow;
365
366
367
368 -- PROCEDURE
369 -- StartWFProcess
370 --
371 -- DESCRIPTION
372 -- Initiate workflow for exception message handling
373 --
374 -- IN
375 -- itemtype - type of the current item
376 -- itemkey - key of the current item
377 -- actid - process activity instance id
378 -- funcmode - function execution mode. this is set by the engine
379 -- as either 'RUN', 'CANCEL', 'TIMEOUT'
380 -- OUT
381 -- result - Name of workflow process to run
382 --
383
384 PROCEDURE StartWFProcess ( item_type in varchar2 default null,
385 item_key in varchar2,
386 compile_designator in varchar2,
387 organization_id in number,
388 inventory_item_id in number,
389 exception_type in number,
390 organization_code in varchar2,
391 item_segments in varchar2,
392 exception_type_text in varchar2,
393 project_number in varchar2,
394 to_project_number in varchar2,
395 task_number in varchar2,
396 to_task_number in varchar2,
397 planning_group in varchar2,
398 due_date in date,
399 from_date in date,
400 to_date in date,
401 days_compressed in number,
402 quantity in varchar2,
403 lot_number in varchar2,
404 order_number in varchar2,
405 order_type_code in number,
406 supply_type in varchar2,
407 end_item_segments in varchar2,
408 end_order_number in varchar2,
409 department_line_code in varchar2,
410 resource_code in varchar2,
411 utilization_rate in number,
412 supplier_id in number,
413 supplier_name in varchar2,
414 supplier_site_id in number,
418 workbench_function in varchar2,
415 supplier_site_code in varchar2,
416 customer_id in number,
417 customer_name in varchar2,
419 workflow_process in varchar2 default null) is
420
421 BEGIN
422
423
424 wf_engine.CreateProcess( itemtype => item_type,
425 itemkey => item_key,
426 process => workflow_process);
427
428 wf_engine.SetItemAttrNumber( itemtype => item_type,
429 itemkey => item_key,
430 aname => 'EXCEPTION_ID',
431 avalue => to_number(item_key));
432
433 wf_engine.SetItemAttrText( itemtype => item_type,
434 itemkey => item_key,
435 aname => 'PLAN_NAME',
436 avalue => compile_designator);
437
438 wf_engine.SetItemAttrNumber( itemtype => item_type,
439 itemkey => item_key,
440 aname => 'ORGANIZATION_ID',
441 avalue => organization_id);
442
443 wf_engine.SetItemAttrNumber( itemtype => item_type,
444 itemkey => item_key,
445 aname => 'INVENTORY_ITEM_ID',
446 avalue => inventory_item_id);
447
448 wf_engine.SetItemAttrNumber( itemtype => item_type,
449 itemkey => item_key,
450 aname => 'EXCEPTION_TYPE_ID',
451 avalue => exception_type);
452
453 wf_engine.SetItemAttrText( itemtype => item_type,
454 itemkey => item_key,
455 aname => 'ORGANIZATION_CODE',
456 avalue => organization_code);
457
458 wf_engine.SetItemAttrText( itemtype => item_type,
459 itemkey => item_key,
460 aname => 'ITEM_DISPLAY_NAME',
461 avalue => item_segments);
462
463 wf_engine.SetItemAttrText( itemtype => item_type,
464 itemkey => item_key,
465 aname => 'EXCEPTION_DESCRIPTION',
466 avalue => exception_type_text);
467
468 wf_engine.SetItemAttrText( itemtype => item_type,
469 itemkey => item_key,
470 aname => 'PROJECT_NUMBER',
471 avalue => project_number);
472
473 wf_engine.SetItemAttrText( itemtype => item_type,
474 itemkey => item_key,
475 aname => 'TO_PROJECT_NUMBER',
476 avalue => to_project_number);
477
478 wf_engine.SetItemAttrText( itemtype => item_type,
479 itemkey => item_key,
480 aname => 'TASK_NUMBER',
481 avalue => task_number);
482
483 wf_engine.SetItemAttrText( itemtype => item_type,
484 itemkey => item_key,
485 aname => 'TO_TASK_NUMBER',
486 avalue => to_task_number);
487
488 wf_engine.SetItemAttrText( itemtype => item_type,
489 itemkey => item_key,
490 aname => 'PLANNING_GROUP',
491 avalue => planning_group);
492
493 wf_engine.SetItemAttrDate( itemtype => item_type,
494 itemkey => item_key,
495 aname => 'DUE_DATE',
496 avalue => due_date);
497
498 wf_engine.SetItemAttrDate( itemtype => item_type,
499 itemkey => item_key,
500 aname => 'FROM_DATE',
501 avalue => from_date);
502
503 wf_engine.SetItemAttrDate( itemtype => item_type,
504 itemkey => item_key,
505 aname => 'TO_DATE',
506 avalue => to_date);
507
508 wf_engine.SetItemAttrNumber( itemtype => item_type,
509 itemkey => item_key,
510 aname => 'DAYS_COMPRESSED',
511 avalue => days_compressed);
512
513 wf_engine.SetItemAttrText( itemtype => item_type,
514 itemkey => item_key,
515 aname => 'QUANTITY',
516 avalue => quantity);
517
518 wf_engine.SetItemAttrText( itemtype => item_type,
519 itemkey => item_key,
520 aname => 'LOT_NUMBER',
521 avalue => lot_number);
522
523 wf_engine.SetItemAttrText( itemtype => item_type,
524 itemkey => item_key,
525 aname => 'ORDER_NUMBER',
526 avalue => order_number);
527
528 wf_engine.SetItemAttrNumber( itemtype => item_type,
529 itemkey => item_key,
530 aname => 'ORDER_TYPE_CODE',
531 avalue => order_type_code);
532
533 wf_engine.SetItemAttrText( itemtype => item_type,
534 itemkey => item_key,
535 aname => 'SUPPLY_TYPE',
536 avalue => supply_type);
537
538 wf_engine.SetItemAttrText( itemtype => item_type,
539 itemkey => item_key,
540 aname => 'END_ITEM_DISPLAY_NAME',
541 avalue => end_item_segments);
542
543 wf_engine.SetItemAttrText( itemtype => item_type,
544 itemkey => item_key,
545 aname => 'END_ORDER_NUMBER',
546 avalue => end_order_number);
547
548 wf_engine.SetItemAttrText( itemtype => item_type,
549 itemkey => item_key,
550 aname => 'DEPARTMENT_LINE_CODE',
551 avalue => department_line_code);
552
553 wf_engine.SetItemAttrText( itemtype => item_type,
554 itemkey => item_key,
555 aname => 'RESOURCE_CODE',
556 avalue => resource_code);
557
561 avalue => utilization_rate);
558 wf_engine.SetItemAttrNumber( itemtype => item_type,
559 itemkey => item_key,
560 aname => 'UTILIZATION_RATE',
562
563 wf_engine.SetItemAttrNumber( itemtype => item_type,
564 itemkey => item_key,
565 aname => 'SUPPLIER_ID',
566 avalue => supplier_id);
567
568 wf_engine.SetItemAttrText( itemtype => item_type,
569 itemkey => item_key,
570 aname => 'SUPPLIER_NAME',
571 avalue => supplier_name);
572
573 wf_engine.SetItemAttrNumber( itemtype => item_type,
574 itemkey => item_key,
575 aname => 'SUPPLIER_SITE_ID',
576 avalue => supplier_site_id);
577
578 wf_engine.SetItemAttrText( itemtype => item_type,
579 itemkey => item_key,
580 aname => 'SUPPLIER_SITE_CODE',
581 avalue => supplier_site_code);
582
583 wf_engine.SetItemAttrNumber( itemtype => item_type,
584 itemkey => item_key,
585 aname => 'CUSTOMER_ID',
586 avalue => customer_id);
587
588 wf_engine.SetItemAttrText( itemtype => item_type,
589 itemkey => item_key,
590 aname => 'CUSTOMER_NAME',
591 avalue => customer_name);
592
593 wf_engine.SetItemAttrText( itemtype => item_type,
594 itemkey => item_key,
595 aname => 'PLANNER_WORKBENCH',
596 avalue => (workbench_function ||
597 ': compile_designator_qf=' ||
598 compile_designator || ' org_id=' ||
599 to_char(organization_id)) );
600
601 wf_engine.StartProcess( itemtype => item_type,
602 itemkey => item_key);
603
604 EXCEPTION
605
606 when others then
607 wf_core.context('MRP_EXP_WF', 'StartWFProcess', item_key, compile_designator, organization_code, item_segments, to_char(exception_type));
608 raise;
609
610 END StartWFProcess;
611
612
613
614 PROCEDURE DetermineProceed( itemtype in varchar2,
615 itemkey in varchar2,
616 actid in number,
617 funcmode in varchar2,
618 resultout out NOCOPY varchar2 ) is
619
620 CURSOR CHK_PRODUCTION_C(p_compile_designator in varchar2,
621 p_organization_id in number) IS
622 SELECT production
623 FROM mrp_plans_sc_v
624 /** Bug 2286190 WHERE organization_id = p_organization_id **/
625 WHERE planned_organization = p_organization_id
626 AND compile_designator = p_compile_designator;
627
628 l_compile_designator varchar2(10) :=
629 wf_engine.GetItemAttrText( itemtype => itemtype,
630 itemkey => itemkey,
631 aname => 'PLAN_NAME');
632
633 l_organization_id number :=
634 wf_engine.GetItemAttrNumber( itemtype => itemtype,
635 itemkey => itemkey,
636 aname => 'ORGANIZATION_ID');
637
638 l_production_flag number := 2;
639
640 BEGIN
641
642 if (funcmode = 'RUN') then
643
644 OPEN CHK_PRODUCTION_C(l_compile_designator, l_organization_id);
645 LOOP
646 FETCH CHK_PRODUCTION_C INTO l_production_flag;
647 EXIT WHEN CHK_PRODUCTION_C%NOTFOUND OR CHK_PRODUCTION_C%NOTFOUND IS NULL;
648 END LOOP;
649 CLOSE CHK_PRODUCTION_C;
650
651 --l_production_flag := 1;
652
653 if (l_production_flag = 1) then
654
655 resultout := 'COMPLETE:Y';
656
657 else
658
659 resultout := 'COMPLETE:N';
660
661 end if;
662
663 return;
664
665 end if;
666
667 if (funcmode = 'CANCEL') then
668
669 resultout := 'COMPLETE:';
670 return;
671
672 end if;
673
674 if (funcmode = 'TIMEOUT') then
675
676 resultout := 'COMPLETE:';
677 return;
678
679 end if;
680
681 EXCEPTION
682
683 when others then
684 wf_core.context('MRP_EXP_WF', 'DetermineProceed', itemtype, itemkey, actid, funcmode);
685 raise;
686
687 END DetermineProceed;
688
689
690
691 PROCEDURE SelectPlanner( itemtype in varchar2,
692 itemkey in varchar2,
693 actid in number,
694 funcmode in varchar2,
695 resultout out NOCOPY varchar2 ) is
696
697 CURSOR PLANNER_C(p_compile_designator in varchar2,
698 p_organization_id in number,
699 p_inventory_item_id in number) IS
700 SELECT mp.employee_id
701 FROM mtl_planners mp,
702 mrp_system_items items
703 WHERE mp.organization_id = items.organization_id
704 AND mp.planner_code = items.planner_code
705 AND items.inventory_item_id = p_inventory_item_id
706 AND items.organization_id = p_organization_id
707 AND items.compile_designator = p_compile_designator;
708
709
713 aname => 'PLAN_NAME');
710 l_compile_designator varchar2(10) :=
711 wf_engine.GetItemAttrText( itemtype => itemtype,
712 itemkey => itemkey,
714
715 l_organization_id number :=
716 wf_engine.GetItemAttrNumber( itemtype => itemtype,
717 itemkey => itemkey,
718 aname => 'ORGANIZATION_ID');
719
720 l_inventory_item_id number :=
721 wf_engine.GetItemAttrNumber( itemtype => itemtype,
722 itemkey => itemkey,
723 aname => 'INVENTORY_ITEM_ID');
724
725 l_exception_type number :=
726 wf_engine.GetItemAttrNumber( itemtype => itemtype,
727 itemkey => itemkey,
728 aname => 'EXCEPTION_TYPE_ID');
729
730 l_order_type number :=
731 wf_engine.GetItemAttrNumber( itemtype => itemtype,
732 itemkey => itemkey,
733 aname => 'ORDER_TYPE_CODE');
734
735 l_person_id number;
736 l_forward_to_username varchar2(100) := NULL;
737 l_display_username varchar2(240) := NULL;
738
739 BEGIN
740
741 if (funcmode = 'RUN') then
742
743 OPEN PLANNER_C(l_compile_designator, l_organization_id, l_inventory_item_id);
744 LOOP
745 FETCH PLANNER_C INTO l_person_id;
746 EXIT WHEN PLANNER_C%NOTFOUND OR PLANNER_C%NOTFOUND IS NULL;
747 END LOOP;
748 CLOSE PLANNER_C;
749
750 wf_directory.GetRoleName('PER', to_char(l_person_id),
751 l_forward_to_username, l_display_username);
752
753 wf_engine.SetItemAttrText( itemtype => itemtype,
754 itemkey => itemkey,
755 aname => 'MESSAGE_NAME',
756 avalue => GetMessageName(l_exception_type,
757 l_order_type,
758 'PLANNER'));
759
760 if (l_forward_to_username is not null) then
761
762 wf_engine.SetItemAttrText( itemtype => itemtype,
763 itemkey => itemkey,
764 aname => 'FORWARD_TO_USERNAME',
765 avalue => l_forward_to_username);
766
767 resultout := 'COMPLETE:FOUND';
768
769 else
770
771 resultout := 'COMPLETE:NOT_FOUND';
772
773 end if;
774
775 return;
776
777 end if;
778
779 if (funcmode = 'CANCEL') then
780
781 resultout := 'COMPLETE:';
782 return;
783
784 end if;
785
786 if (funcmode = 'TIMEOUT') then
787
788 resultout := 'COMPLETE:';
789 return;
790
791 end if;
792
793 EXCEPTION
794
795 when others then
796 wf_core.context('MRP_EXP_WF', 'SelectPlanner', itemtype, itemkey, actid, funcmode);
797 raise;
798
799 END SelectPlanner;
800
801
802
803 PROCEDURE SelectBuyer( itemtype in varchar2,
804 itemkey in varchar2,
805 actid in number,
806 funcmode in varchar2,
807 resultout out NOCOPY varchar2) is
808
809 CURSOR BUYER_C(p_compile_designator in varchar2,
810 p_organization_id in number,
811 p_inventory_item_id in number) IS
812 SELECT buyer_id
813 FROM mrp_system_items
814 WHERE inventory_item_id = p_inventory_item_id
815 AND organization_id = p_organization_id
816 AND compile_designator = p_compile_designator;
817
818 l_compile_designator varchar2(10) :=
819 wf_engine.GetItemAttrText( itemtype => itemtype,
820 itemkey => itemkey,
821 aname => 'PLAN_NAME');
822
823 l_organization_id number :=
824 wf_engine.GetItemAttrNumber( itemtype => itemtype,
825 itemkey => itemkey,
826 aname => 'ORGANIZATION_ID');
827
828 l_inventory_item_id number :=
829 wf_engine.GetItemAttrNumber( itemtype => itemtype,
830 itemkey => itemkey,
831 aname => 'INVENTORY_ITEM_ID');
832
833 l_exception_type number :=
834 wf_engine.GetItemAttrNumber( itemtype => itemtype,
835 itemkey => itemkey,
836 aname => 'EXCEPTION_TYPE_ID');
837
838 l_order_type number :=
839 wf_engine.GetItemAttrNumber( itemtype => itemtype,
840 itemkey => itemkey,
841 aname => 'ORDER_TYPE_CODE');
842
843 l_person_id number;
844 l_forward_to_username varchar2(100) := NULL;
845 l_display_username varchar2(240) := NULL;
846
847 BEGIN
848
849 if (funcmode = 'RUN') then
850
851 OPEN BUYER_C(l_compile_designator, l_organization_id, l_inventory_item_id);
852 LOOP
853 FETCH BUYER_C INTO l_person_id;
854 EXIT WHEN BUYER_C%NOTFOUND OR BUYER_C%NOTFOUND IS NULL;
855 END LOOP;
856 CLOSE BUYER_C;
857
858 wf_directory.GetRoleName('PER', to_char(l_person_id),
859 l_forward_to_username, l_display_username);
860
861 wf_engine.SetItemAttrText( itemtype => itemtype,
862 itemkey => itemkey,
863 aname => 'MESSAGE_NAME',
864 avalue => GetMessageName(l_exception_type,
865 l_order_type,
866 'BUYER'));
870 wf_engine.SetItemAttrText( itemtype => itemtype,
867
868 if (l_forward_to_username is not null) then
869
871 itemkey => itemkey,
872 aname => 'FORWARD_TO_USERNAME',
873 avalue => l_forward_to_username);
874
875 resultout := 'COMPLETE:FOUND';
876
877 else
878
879 resultout := 'COMPLETE:NOT_FOUND';
880
881 end if;
882
883 return;
884
885 end if;
886
887 if (funcmode = 'CANCEL') then
888
889 resultout := 'COMPLETE:';
890 return;
891
892 end if;
893
894 if (funcmode = 'TIMEOUT') then
895
896 resultout := 'COMPLETE:';
897 return;
898
899 end if;
900
901 EXCEPTION
902
903 when others then
904 wf_core.context('MRP_EXP_WF', 'SelectBuyer', itemtype, itemkey, actid, funcmode);
905 raise;
906
907 END SelectBuyer;
908
909
910
911 PROCEDURE SelectSupplierCnt( itemtype in varchar2,
912 itemkey in varchar2,
913 actid in number,
914 funcmode in varchar2,
915 resultout out NOCOPY varchar2) is
916
917 l_supplier_id number :=
918 wf_engine.GetItemAttrNumber( itemtype => itemtype,
919 itemkey => itemkey,
920 aname => 'SUPPLIER_ID');
921
922 l_supplier_site_id number :=
923 wf_engine.GetItemAttrNumber( itemtype => itemtype,
924 itemkey => itemkey,
925 aname => 'SUPPLIER_SITE_ID');
926
927 CURSOR SUPPLIER_CONTACT_C(p_supplier_id in number,
928 p_supplier_site_id in number) IS
929 SELECT DECODE(fu.employee_id, NULL, fu.user_id, fu.employee_id),
930 DECODE(fu.employee_id, NULL, 'FND_USR', 'PER')
931 FROM fnd_user fu,
932 po_vendor_contacts cont
933 WHERE fu.supplier_id = cont.vendor_contact_id
934 AND cont.vendor_site_id = p_supplier_site_id;
935
936 l_exception_type number :=
937 wf_engine.GetItemAttrNumber( itemtype => itemtype,
938 itemkey => itemkey,
939 aname => 'EXCEPTION_TYPE_ID');
940
941 l_person_id number;
942 l_orig_system varchar2(48);
943 l_forward_to_username varchar2(100) := NULL;
944 l_display_username varchar2(240) := NULL;
945
946 BEGIN
947
948 if (funcmode = 'RUN') then
949
950 OPEN SUPPLIER_CONTACT_C(l_supplier_id, l_supplier_site_id);
951 LOOP
952 FETCH SUPPLIER_CONTACT_C INTO l_person_id, l_orig_system;
953 if (l_person_id is not null) then
954 exit;
955 end if;
956 EXIT WHEN SUPPLIER_CONTACT_C%NOTFOUND OR SUPPLIER_CONTACT_C%NOTFOUND IS NULL;
957 END LOOP;
958 CLOSE SUPPLIER_CONTACT_C;
959
960 wf_directory.GetRoleName(l_orig_system, to_char(l_person_id),
961 l_forward_to_username, l_display_username);
962
963 wf_engine.SetItemAttrText( itemtype => itemtype,
964 itemkey => itemkey,
965 aname => 'MESSAGE_NAME',
966 avalue => GetMessageName(l_exception_type,
967 1, 'SUPPLIERCNT'));
968
969 if (l_forward_to_username is not null) then
970
971 wf_engine.SetItemAttrText( itemtype => itemtype,
972 itemkey => itemkey,
973 aname => 'FORWARD_TO_USERNAME',
974 avalue => l_forward_to_username);
975
976 resultout := 'COMPLETE:FOUND';
977
978 else
979
980 resultout := 'COMPLETE:NOT_FOUND';
981
982 end if;
983
984 return;
985
986 end if;
987
988 if (funcmode = 'CANCEL') then
989
990 resultout := 'COMPLETE:';
991 return;
992
993 end if;
994
995 if (funcmode = 'TIMEOUT') then
996
997 resultout := 'COMPLETE:';
998 return;
999
1000 end if;
1001
1002 EXCEPTION
1003
1004 when others then
1005 wf_core.context('MRP_EXP_WF', 'SelectSupplierCnt', itemtype, itemkey, actid, funcmode);
1006 raise;
1007
1008 END SelectSupplierCnt;
1009
1010
1011
1012 PROCEDURE SelectSalesRep( itemtype in varchar2,
1013 itemkey in varchar2,
1014 actid in number,
1015 funcmode in varchar2,
1016 resultout out NOCOPY varchar2) is
1017
1018 CURSOR SALESREP_C(p_exception_id in number) IS
1019 SELECT rep.person_id
1020 FROM
1021 ra_salesreps_all rep,
1022 oe_order_headers_all soh,
1023 oe_order_types_v sot,
1024 mtl_sales_orders mso,
1025 mrp_schedule_dates dates,
1026 mrp_gross_requirements mgr,
1027 mrp_exception_details_v exp
1028 WHERE
1029 rep.org_id = soh.org_id
1030 AND rep.salesrep_id = soh.salesrep_id
1031 AND soh.order_number = to_number(mso.segment1)
1032 AND soh.order_type_id = sot.order_type_id
1033 AND sot.name = mso.segment2
1037 AND dates.mps_transaction_id = mgr.disposition_id
1034 AND mso.sales_order_id = dates.source_sales_order_id
1035 AND dates.supply_demand_type = 1
1036 AND dates.schedule_level = 3
1038 AND mgr.origination_type = 6
1039 AND mgr.demand_id = exp.demand_id
1040 AND exp.exception_id = p_exception_id;
1041
1042 l_exception_id number :=
1043 wf_engine.GetItemAttrNumber( itemtype => itemtype,
1044 itemkey => itemkey,
1045 aname => 'EXCEPTION_ID');
1046
1047 l_exception_type number :=
1048 wf_engine.GetitemAttrNumber( itemtype => itemtype,
1049 itemkey => itemkey,
1050 aname => 'EXCEPTION_TYPE_ID');
1051
1052 l_person_id number;
1053 l_forward_to_username varchar2(100) := NULL;
1054 l_display_username varchar2(240) := NULL;
1055
1056 BEGIN
1057
1058 if (funcmode = 'RUN') then
1059
1060 OPEN SALESREP_C(l_exception_id);
1061 LOOP
1062 FETCH SALESREP_C INTO l_person_id;
1063 EXIT WHEN SALESREP_C%NOTFOUND OR SALESREP_C%NOTFOUND IS NULL;
1064 END LOOP;
1065 CLOSE SALESREP_C;
1066
1067 wf_directory.GetRoleName('PER', to_char(l_person_id),
1068 l_forward_to_username, l_display_username);
1069
1070 wf_engine.SetItemAttrText( itemtype => itemtype,
1071 itemkey => itemkey,
1072 aname => 'MESSAGE_NAME',
1073 avalue => GetMessageName(l_exception_type,
1074 to_number(NULL),
1075 'SALESREP'));
1076
1077 if (l_forward_to_username is not null) then
1078
1079 wf_engine.SetItemAttrText( itemtype => itemtype,
1080 itemkey => itemkey,
1081 aname => 'FORWARD_TO_USERNAME',
1082 avalue => l_forward_to_username);
1083
1084 resultout := 'COMPLETE:FOUND';
1085
1086 else
1087
1088 resultout := 'COMPLETE:NOT_FOUND';
1089
1090 end if;
1091
1092 return;
1093
1094 end if;
1095
1096 if (funcmode = 'CANCEL') then
1097
1098 resultout := 'COMPLETE:';
1099 return;
1100
1101 end if;
1102
1103 if (funcmode = 'TIMEOUT') then
1104
1105 resultout := 'COMPLETE:';
1106 return;
1107
1108 end if;
1109
1110 EXCEPTION
1111
1112 when others then
1113 wf_core.context('MRP_EXP_WF', 'SelectSalesRep', itemtype, itemkey, actid, funcmode);
1114 raise;
1115
1116 END SelectSalesRep;
1117
1118
1119
1120 PROCEDURE SelectCustomerCnt( itemtype in varchar2,
1121 itemkey in varchar2,
1122 actid in number,
1123 funcmode in varchar2,
1124 resultout out NOCOPY varchar2) is
1125
1126 l_customer_id number :=
1127 wf_engine.GetItemAttrNumber( itemtype => itemtype,
1128 itemkey => itemkey,
1129 aname => 'CUSTOMER_ID');
1130
1131 CURSOR CUSTOMER_CONTACT_C1(p_customer_id in number) IS
1132 SELECT DECODE(fu.employee_id, NULL, fu.user_id, fu.employee_id),
1133 DECODE(fu.employee_id, NULL, 'FND_USR', 'PER')
1134 FROM fnd_user fu,
1135 hz_cust_account_roles cont
1136 WHERE fu.customer_id = cont.cust_account_role_id
1137 AND cont.cust_account_id = p_customer_id;
1138
1139 CURSOR CUSTOMER_CONTACT_C2(p_customer_id in number) IS
1140 SELECT cust_account_role_id
1141 FROM hz_cust_account_roles
1142 WHERE cust_account_id = p_customer_id;
1143
1144 l_person_id number;
1145 l_orig_system varchar2(48);
1146 l_forward_to_username varchar2(100) := NULL;
1147 l_display_username varchar2(240) := NULL;
1148
1149 BEGIN
1150
1151 if (funcmode = 'RUN') then
1152
1153 OPEN CUSTOMER_CONTACT_C1(l_customer_id);
1154 LOOP
1155 FETCH CUSTOMER_CONTACT_C1 INTO l_person_id, l_orig_system;
1156 EXIT WHEN CUSTOMER_CONTACT_C1%NOTFOUND OR CUSTOMER_CONTACT_C1%NOTFOUND IS NULL;
1157 END LOOP;
1158 CLOSE CUSTOMER_CONTACT_C1;
1159
1160 wf_directory.GetRoleName(l_orig_system, to_char(l_person_id),
1161 l_forward_to_username, l_display_username);
1162
1163 if (l_forward_to_username is null) then
1164
1165 OPEN CUSTOMER_CONTACT_C2(l_customer_id);
1166 LOOP
1167 FETCH CUSTOMER_CONTACT_C2 INTO l_person_id;
1168 EXIT WHEN CUSTOMER_CONTACT_C2%NOTFOUND OR CUSTOMER_CONTACT_C2%NOTFOUND IS NULL;
1169 END LOOP;
1170 CLOSE CUSTOMER_CONTACT_C2;
1171
1172 wf_directory.GetRoleName('CUST_CONT', to_char(l_person_id),
1173 l_forward_to_username, l_display_username);
1174
1175 end if;
1176
1177 if (l_forward_to_username is not null) then
1178
1179 wf_engine.SetItemAttrText( itemtype => itemtype,
1180 itemkey => itemkey,
1181 aname => 'FORWARD_TO_USERNAME',
1182 avalue => l_forward_to_username);
1183
1184 resultout := 'COMPLETE:FOUND';
1185
1186 else
1187
1188 resultout := 'COMPLETE:NOT_FOUND';
1189
1190 end if;
1191
1192 return;
1193
1194 end if;
1198 resultout := 'COMPLETE:';
1195
1196 if (funcmode = 'CANCEL') then
1197
1199 return;
1200
1201 end if;
1202
1203 if (funcmode = 'TIMEOUT') then
1204
1205 resultout := 'COMPLETE:';
1206 return;
1207
1208 end if;
1209
1210 EXCEPTION
1211
1212 when others then
1213 wf_core.context('MRP_EXP_WF', 'SelectCustomerCnt', itemtype, itemkey, actid, funcmode);
1214 raise;
1215
1216 END SelectCustomerCnt;
1217
1218
1219
1220
1221 PROCEDURE SelectTaskMgr( itemtype in varchar2,
1222 itemkey in varchar2,
1223 actid in number,
1224 funcmode in varchar2,
1225 resultout out NOCOPY varchar2) is
1226
1227 CURSOR TASK_MANAGER_C(p_project_number in varchar2,
1228 p_task_number in varchar2) IS
1229 SELECT tasks.task_manager_person_id
1230 FROM pa_tasks tasks,
1231 pa_projects_all proj
1232 WHERE tasks.task_number = p_task_number
1233 AND tasks.project_id = proj.project_id
1234 AND proj.segment1 = p_project_number;
1235
1236 CURSOR PROJECT_MANAGER_C(p_project_number in varchar2) IS
1237 SELECT ppp.person_id
1238 FROM pa_project_players ppp,
1239 pa_projects_all proj
1240 WHERE ppp.project_role_type = 'PROJECT MANAGER'
1241 AND ppp.project_id = proj.project_id
1242 AND proj.segment1 = p_project_number;
1243
1244 l_task_mgr_number number :=
1245 wf_engine.GetActivityAttrNumber( itemtype => itemtype,
1246 itemkey => itemkey,
1247 actid => actid,
1248 aname => 'WHICH_TASK_MANAGER');
1249
1250 l_project_number varchar2(4000) :=
1251 wf_engine.GetItemAttrText( itemtype => itemtype,
1252 itemkey => itemkey,
1253 aname => 'PROJECT_NUMBER');
1254
1255 l_to_project_number varchar2(4000) :=
1256 wf_engine.GetItemAttrText( itemtype => itemtype,
1257 itemkey => itemkey,
1258 aname => 'TO_PROJECT_NUMBER');
1259
1260 l_task_number varchar2(4000) :=
1261 wf_engine.GetItemAttrText( itemtype => itemtype,
1262 itemkey => itemkey,
1263 aname => 'TASK_NUMBER');
1264
1265 l_to_task_number varchar2(4000) :=
1266 wf_engine.GetItemAttrText( itemtype => itemtype,
1267 itemkey => itemkey,
1268 aname => 'TO_TASK_NUMBER');
1269
1270 l_person_id number;
1271 l_forward_to_username varchar2(100) := NULL;
1272 l_display_username varchar2(240) := NULL;
1273
1274 BEGIN
1275
1276 if (funcmode = 'RUN') then
1277
1278 if (l_task_mgr_number = 2) then
1279
1280 l_project_number := l_to_project_number;
1281 l_task_number := l_to_task_number;
1282
1283 end if;
1284
1285 OPEN TASK_MANAGER_C(l_project_number, l_task_number);
1286 LOOP
1287 FETCH TASK_MANAGER_C INTO l_person_id;
1288 EXIT WHEN TASK_MANAGER_C%NOTFOUND OR TASK_MANAGER_C%NOTFOUND IS NULL;
1289 END LOOP;
1290 CLOSE TASK_MANAGER_C;
1291
1292 wf_directory.GetRoleName('PER', to_char(l_person_id),
1293 l_forward_to_username, l_display_username);
1294
1295 if (l_forward_to_username is null) then
1296
1297 OPEN PROJECT_MANAGER_C(l_project_number);
1298 LOOP
1299 FETCH PROJECT_MANAGER_C INTO l_person_id;
1300 EXIT WHEN PROJECT_MANAGER_C%NOTFOUND OR PROJECT_MANAGER_C%NOTFOUND IS NULL;
1301 END LOOP;
1302 CLOSE PROJECT_MANAGER_C;
1303
1304 wf_directory.GetRoleName('PER', to_char(l_person_id),
1305 l_forward_to_username, l_display_username);
1306
1307 end if;
1308
1309 if (l_forward_to_username is not null) then
1310
1311 wf_engine.SetItemAttrText( itemtype => itemtype,
1312 itemkey => itemkey,
1313 aname => 'FORWARD_TO_USERNAME',
1314 avalue => l_forward_to_username);
1315
1316 resultout := 'COMPLETE:FOUND';
1317
1318 else
1319
1320 resultout := 'COMPLETE:NOT_FOUND';
1321
1322 end if;
1323
1324 return;
1325
1326 end if;
1327
1328 if (funcmode = 'CANCEL') then
1329
1330 resultout := 'COMPLETE:';
1331 return;
1332
1333 end if;
1334
1335 if (funcmode = 'TIMEOUT') then
1336
1337 resultout := 'COMPLETE:';
1338 return;
1339
1340 end if;
1341
1342 EXCEPTION
1343
1344 when others then
1345 wf_core.context('MRP_EXP_WF', 'SelectTaskMgr', itemtype, itemkey, actid, funcmode);
1346 raise;
1347
1348 END SelectTaskMgr;
1349
1350
1351
1352 FUNCTION GetMessageName(p_exception_type in number,
1353 p_order_type in number,
1354 p_recipient in varchar2) RETURN varchar2 IS
1355
1356 l_message_name varchar2(40);
1357
1358 BEGIN
1359
1360 if (p_recipient = 'PLANNER') then
1361
1362 if (p_exception_type = 1) then
1363 l_message_name := 'MSG_1';
1364
1368 elsif (p_exception_type in (6, 7)) then
1365 elsif (p_exception_type in (2, 3, 20)) then
1366 l_message_name := 'MSG_2_3_20';
1367
1369
1370 if (p_order_type = 1) then
1371 l_message_name := 'MSG_6_7_PO';
1372
1373 elsif (p_order_type = 2) then
1374 l_message_name := 'MSG_6_7_REQ';
1375
1376 elsif (p_order_type in (3, 5, 7, 18)) then
1377 l_message_name := 'MSG_RESCHEDULE_6_7_WORK';
1378
1379 end if;
1380
1381 elsif (p_exception_type in (8, 10)) then
1382
1383 if (p_order_type = 1) then
1384 l_message_name := 'MSG_8_10_PO';
1385
1386 elsif (p_order_type = 2) then
1387 l_message_name := 'MSG_8_10_REQ';
1388
1389 elsif (p_order_type in (3, 5, 7, 18)) then
1390 l_message_name := 'MSG_RESCHEDULE_8_10_WORK';
1391
1392 end if;
1393
1394 elsif (p_exception_type = 9) then
1395
1396 if (p_order_type = 1) then
1397 l_message_name := 'MSG_9_PO';
1398
1399 elsif (p_order_type = 2) then
1400 l_message_name := 'MSG_9_REQ';
1401
1402 elsif (p_order_type in (3, 5, 7, 18)) then
1403 l_message_name := 'MSG_RESCHEDULE_9_WORK';
1404
1405 end if;
1406
1407 elsif (p_exception_type = 12) then
1408 l_message_name := 'MSG_12';
1409
1410 elsif (p_exception_type = 13) then
1411 l_message_name := 'MSG_13';
1412
1413 elsif (p_exception_type = 14) then
1414 l_message_name := 'MSG_14';
1415
1416 elsif (p_exception_type = 15) then
1417 l_message_name := 'MSG_15';
1418
1419 elsif (p_exception_type = 16) then
1420 l_message_name := 'MSG_16';
1421
1422 elsif (p_exception_type in (17, 18)) then
1423 l_message_name := 'MSG_17_18';
1424
1425 elsif (p_exception_type = 19) then
1426 l_message_name := 'MSG_19';
1427
1428 end if;
1429
1430 elsif (p_recipient = 'BUYER') then
1431
1432 if (p_exception_type in (6, 7)) then
1433
1434 if (p_order_type = 1) then
1435 l_message_name := 'MSG_6_7_PO';
1436
1437 elsif (p_order_type = 2) then
1438 l_message_name := 'MSG_RESCHEDULE_6_7_REQ';
1439
1440 end if;
1441
1442 elsif (p_exception_type in (8, 10)) then
1443
1444 if (p_order_type = 1) then
1445 l_message_name := 'MSG_8_10_PO';
1446
1447 elsif (p_order_type = 2) then
1448 l_message_name := 'MSG_RESCHEDULE_8_10_REQ';
1449
1450 end if;
1451
1452 elsif (p_exception_type = 9) then
1453
1454 if (p_order_type = 1) then
1455 l_message_name := 'MSG_9_PO';
1456
1457 elsif (p_order_type = 2) then
1458 l_message_name := 'MSG_RESCHEDULE_9_REQ';
1459
1460 end if;
1461
1462 end if;
1463
1464 elsif (p_recipient = 'SUPPLIERCNT') then
1465
1466 if (p_exception_type in (6, 7)) then
1467 l_message_name := 'MSG_RESCHEDULE_6_7_PO';
1468
1469 elsif (p_exception_type in (8, 10)) then
1470 l_message_name := 'MSG_RESCHEDULE_8_10_PO';
1471
1472 elsif (p_exception_type = 9) then
1473 l_message_name := 'MSG_RESCHEDULE_9_PO';
1474
1475 end if;
1476
1477 elsif (p_recipient = 'SALESREP') then
1478 if (p_exception_type = 13) then
1479 l_message_name := 'MSG_13_FYI';
1480
1481 elsif (p_exception_type = 15) then
1482 l_message_name := 'MSG_15_FYI';
1483
1484 end if;
1485
1486 end if;
1487
1488 return l_message_name;
1489
1490 EXCEPTION
1491
1492 when others then
1493 wf_core.context('MRP_EXP_WF', 'GetMessageName', to_char(p_exception_type), to_char(p_order_type));
1494 raise;
1495
1496 END GetMessageName;
1497
1498
1499
1500 PROCEDURE DetermineExceptionType( itemtype in varchar2,
1501 itemkey in varchar2,
1502 actid in number,
1503 funcmode in varchar2,
1504 resultout out NOCOPY varchar2) is
1505
1506 l_exception_type number :=
1507 wf_engine.GetItemAttrNumber( itemtype => itemtype,
1508 itemkey => itemkey,
1509 aname => 'EXCEPTION_TYPE_ID');
1510
1511 BEGIN
1512
1513 if (funcmode = 'RUN') then
1514
1515 if (l_exception_type in (1, 2, 3, 20)) then
1516
1517 resultout := 'COMPLETE:1_2_3_20';
1518
1519 elsif (l_exception_type = 12) then
1520
1521 resultout := 'COMPLETE:12';
1522
1523 elsif (l_exception_type in (14, 16)) then
1524
1525 resultout := 'COMPLETE:14_16';
1526
1527 end if;
1528
1529 return;
1530
1531 end if;
1532
1533 if (funcmode = 'CANCEL') then
1534
1535 resultout := 'COMPLETE:';
1536 return;
1537
1538 end if;
1539
1540 if (funcmode = 'TIMEOUT') then
1541
1542 resultout := 'COMPLETE:';
1543 return;
1544
1545 end if;
1546
1547 EXCEPTION
1548
1549 when others then
1553 END DetermineExceptionType;
1550 wf_core.context('MRP_EXP_WF', 'DetermineExceptionType', itemtype, itemkey, actid, funcmode);
1551 raise;
1552
1554
1555
1556
1557
1558 PROCEDURE DetermineOrderType( itemtype in varchar2,
1559 itemkey in varchar2,
1560 actid in number,
1561 funcmode in varchar2,
1562 resultout out NOCOPY varchar2) is
1563
1564 l_exception_id number :=
1565 wf_engine.GetItemAttrNumber( itemtype => itemtype,
1566 itemkey => itemkey,
1567 aname => 'EXCEPTION_ID');
1568
1569 l_order_type number :=
1570 wf_engine.GetItemAttrNumber( itemtype => itemtype,
1571 itemkey => itemkey,
1572 aname => 'ORDER_TYPE_CODE');
1573
1574
1575 BEGIN
1576
1577 if (funcmode = 'RUN') then
1578
1579 -- Purchase Order
1580 if (l_order_type = 1) then
1581
1582 resultout := 'COMPLETE:PURCHASE_ORDER';
1583
1584 -- Purchase Requisition
1585 elsif (l_order_type = 2) then
1586
1587 resultout := 'COMPLETE:PURCHASE_REQUISITION';
1588
1589 -- Discrete Job, Planned Order, Non-standard Job, Flow Schedule
1590 elsif (l_order_type in (3, 5, 7, 18)) then
1591
1592 resultout := 'COMPLETE:WORK_ORDER';
1593
1594 else
1595
1596 resultout := 'COMPLETE:OTEHR_ORDER_TYPES';
1597
1598 end if;
1599
1600 return;
1601
1602 end if;
1603
1604 if (funcmode = 'CANCEL') then
1605
1606 resultout := 'COMPLETE:';
1607 return;
1608
1609 end if;
1610
1611 if (funcmode = 'TIMEOUT') then
1612
1613 resultout := 'COMPLETE:';
1614 return;
1615
1616 end if;
1617
1618 EXCEPTION
1619
1620 when others then
1621 wf_core.context('MRP_EXP_WF', 'DetermineOrderType', itemtype, itemkey, actid, funcmode);
1622 raise;
1623
1624 END DetermineOrderType;
1625
1626
1627
1628 PROCEDURE Reschedule( itemtype in varchar2,
1629 itemkey in varchar2,
1630 actid in number,
1631 funcmode in varchar2,
1632 resultout out NOCOPY varchar2) is
1633
1634 l_exception_id number :=
1635 wf_engine.GetItemAttrNumber( itemtype => itemtype,
1636 itemkey => itemkey,
1637 aname => 'EXCEPTION_ID');
1638
1639 l_compile_designator varchar2(10) :=
1640 wf_engine.GetItemAttrText( itemtype => itemtype,
1641 itemkey => itemkey,
1642 aname => 'PLAN_NAME');
1643
1644 l_organization_id number :=
1645 wf_engine.GetItemAttrNumber( itemtype => itemtype,
1646 itemkey => itemkey,
1647 aname => 'ORGANIZATION_ID');
1648
1649 l_exception_type number :=
1650 wf_engine.GetItemAttrNumber( itemtype => itemtype,
1651 itemkey => itemkey,
1652 aname => 'EXCEPTION_TYPE_ID');
1653
1654 l_order_type number :=
1655 wf_engine.GetItemAttrNumber( itemtype => itemtype,
1656 itemkey => itemkey,
1657 aname => 'ORDER_TYPE_CODE');
1658
1659 l_owning_org_id number;
1660 l_user_id number := fnd_global.user_id;
1661 l_po_group_by number := fnd_profile.value('MRP_LOAD_REQ_GROUP_BY');
1662 l_po_batch_number number;
1663 l_wip_group_id number;
1664 l_loaded_jobs number;
1665 l_loaded_reqs number;
1666 l_loaded_scheds number;
1667 l_resched_jobs number;
1668 l_resched_reqs number;
1669 l_wip_req_id number;
1670 l_req_load_id number;
1671 l_req_resched_id number;
1672 l_transaction_id number := to_number(NULL);
1673 l_location_id number;
1674
1675 l_reschedule_result boolean;
1676
1677 l_assigned_user varchar2(320);
1678 l_application_id number;
1679 l_responsibility_id number;
1680
1681 BEGIN
1682
1683 -- Based on whether it is a discrete job, repetitive_schedules,
1684 -- Flow schedules, Reqs or Purchase Orders we will call the appropriate
1685 -- APIs.
1686 -- DJ - wip_reschedule_interface
1687 -- RS - wip_reschedule_interface
1688 -- FS - FS api, however we do not do this currently
1689 -- Req - po_reschedule_interface
1690 -- PO - po api
1691 -- You need to account for cancel here as well
1692
1693 IF (funcmode = 'RUN') THEN
1694
1695 /** Bug 2226979 **/
1696 IF l_user_id < 1 THEN -- get a valid responsibility for this user
1697 l_assigned_user := wf_engine.GetItemAttrText( itemtype => itemtype,
1698 itemkey => itemkey,
1699 aname => 'FORWARD_TO_USERNAME');
1700
1701 SELECT g.responsibility_id, g.user_id, g.responsibility_application_id
1702 INTO l_responsibility_id, l_user_id, l_application_id
1703 FROM fnd_user u, fnd_user_resp_groups g
1704 WHERE u.user_name = l_assigned_user
1705 AND g.start_date <= SYSDATE
1706 AND NVL(g.end_date, SYSDATE + 1) >= SYSDATE
1707 AND g.user_id = u.user_id
1708 AND u.start_date <= SYSDATE
1709 AND NVL(u.end_date, SYSDATE + 1) >= SYSDATE
1710 AND ROWNUM = 1;
1711
1712 FND_GLOBAL.Apps_Initialize(l_user_id, l_responsibility_id, l_application_id);
1713
1717
1714 END IF;
1715
1716 /** End Bug 2226979 **/
1718 if (l_order_type in (1, 2, 3, 13)) then -- po, req, discrete, repetitve
1719
1720 SELECT organization_id
1721 INTO l_owning_org_id
1722 FROM mrp_plan_organizations_v
1723 WHERE planned_organization = l_organization_id
1724 AND compile_designator = l_compile_designator;
1725
1726 SELECT mrp_workbench_query_s.nextval,
1727 wip_job_schedule_interface_s.nextval
1728 INTO l_po_batch_number,
1729 l_wip_group_id
1730 FROM dual;
1731
1732 SELECT transaction_id
1733 INTO l_transaction_id
1734 FROM mrp_exception_details_v
1735 WHERE exception_id = l_exception_id;
1736
1737 if (l_order_type in (3, 13)) then
1738
1739 UPDATE mrp_recommendations
1740 SET implement_date = new_schedule_date,
1741 implement_quantity = DECODE(l_exception_type, 8, 0,
1742 new_order_quantity),
1743 implement_demand_class = demand_class,
1744 implement_status_code = decode(l_order_type, 3,
1745 decode(l_exception_type, 8, 7, implement_status_code),
1746 implement_status_code), /* Bug 2226979 **/
1747 implement_project_id = project_id,
1748 implement_task_id = task_id,
1749 implement_job_name = FND_PROFILE.VALUE('WIP_JOB_PREFIX')||to_char(wip_job_number_s.nextval),
1750 implement_line_id = line_id,
1751 implement_alternate_bom = alternate_bom_designator,
1752 implement_alternate_routing = alternate_routing_designator,
1753 implement_end_item_unit_number = end_item_unit_number
1754 WHERE transaction_id = l_transaction_id;
1755
1756 else
1757
1758 BEGIN
1759 SELECT loc.location_id
1760 INTO l_location_id
1761 FROM hr_locations loc,
1762 hr_organization_units unit
1763 WHERE unit.organization_id = l_organization_id
1764 AND unit.location_id = loc.location_id;
1765
1766 EXCEPTION
1767 WHEN NO_DATA_FOUND THEN
1768 l_location_id := NULL;
1769 END;
1770
1771 UPDATE mrp_recommendations supplies
1772 SET old_order_quantity = new_order_quantity,
1773 quantity_in_process = new_order_quantity,
1774 implement_date = new_schedule_date,
1775 implement_quantity = decode(disposition_status_type,2,0,new_order_quantity), /** Bug 2226979 **/
1776 implement_firm = firm_planned_type,
1777 implement_dock_date = new_dock_date,
1778 implement_location_id = l_location_id,
1779 implement_source_org_id = source_organization_id,
1780 implement_vendor_id = source_vendor_id,
1781 implement_vendor_site_id = source_vendor_site_id,
1782 implement_project_id = project_id,
1783 implement_task_id = task_id,
1784 implement_demand_class = NULL,
1785 implement_employee_id = ( SELECT
1786 decode(msi.planner_code,NULL,mplm.employee_id,mpl.employee_id)
1787 FROM mtl_planners mplm,
1788 mtl_planners mpl,
1789 mtl_parameters mparam,
1790 mtl_system_items master_msi,
1791 mtl_system_items msi
1792 WHERE msi.organization_id = supplies.organization_id
1793 AND msi.inventory_item_id = supplies.inventory_item_id
1794 AND master_msi.organization_id = mparam.master_organization_id
1795 AND master_msi.inventory_item_id = msi.inventory_item_id
1796 AND mpl.organization_id (+) = msi.organization_id
1797 AND mpl.planner_code (+) = NVL(msi.planner_code, 'A')
1798 AND mplm.organization_id (+)= master_msi.organization_id
1799 AND mplm.planner_code (+)= NVL(master_msi.planner_code, 'A')
1800 AND mparam.organization_id = msi.organization_id)
1801 WHERE transaction_id = l_transaction_id;
1802
1803 end if;
1804
1805 mrp_rel_plan_pub.mrp_release_plan_sc
1806 (l_organization_id, l_owning_org_id,
1807 l_compile_designator, l_user_id,
1808 l_po_group_by, l_po_batch_number, l_wip_group_id,
1809 l_loaded_jobs, l_loaded_reqs, l_loaded_scheds,
1810 l_resched_jobs, l_resched_reqs, l_wip_req_id,
1811 l_req_load_id, l_req_resched_id,
1812 'WF', l_transaction_id);
1813
1814 if nvl(l_wip_req_id,0) > 0 then
1815 wf_engine.additemattr(itemtype,
1816 itemkey,
1817 'WIP_REQ_ID',
1818 null,
1819 l_wip_req_id,
1820 null);
1821 end if;
1822 if nvl(l_req_load_id,0) > 0 then
1823 wf_engine.additemattr(itemtype,
1824 itemkey,
1825 'REQ_LOAD_REQ_ID',
1829 end if;
1826 null,
1827 l_req_load_id,
1828 null);
1830 if nvl(l_req_resched_id,0) > 0 then
1831 wf_engine.additemattr(itemtype,
1832 itemkey,
1833 'REQ_RESCHED_REQ_ID',
1834 null,
1835 l_req_resched_id,
1836 null);
1837 end if;
1838
1839 end if;
1840
1841 resultout := 'COMPLETE:';
1842 return;
1843
1844 end if;
1845
1846 if (funcmode = 'CANCEL') then
1847
1848 resultout := 'COMPLETE:';
1849 return;
1850
1851 end if;
1852
1853 if (funcmode = 'TIMEOUT') then
1854
1855 resultout := 'COMPLETE:';
1856 return;
1857
1858 end if;
1859
1860 EXCEPTION
1861
1862 when others then
1863 wf_core.context('MRP_EXP_WF', 'Reschedule', itemtype, itemkey, actid, funcmode);
1864 raise;
1865
1866 END Reschedule;
1867
1868
1869
1870 PROCEDURE IsType19( itemtype in varchar2,
1871 itemkey in varchar2,
1872 actid in number,
1873 funcmode in varchar2,
1874 resultout out NOCOPY varchar2) is
1875
1876 l_exception_type number :=
1877 wf_engine.GetItemAttrNumber( itemtype => itemtype,
1878 itemkey => itemkey,
1879 aname => 'EXCEPTION_TYPE_ID');
1880
1881 BEGIN
1882
1883 if (funcmode = 'RUN') then
1884
1885 if (l_exception_type = 19) then
1886
1887 resultout := 'COMPLETE:Y';
1888
1889 else
1890
1891 resultout := 'COMPLETE:N';
1892
1893 end if;
1894
1895 return;
1896
1897 end if;
1898
1899 if (funcmode = 'CANCEL') then
1900
1901 resultout := 'COMPLETE:';
1902 return;
1903
1904 end if;
1905
1906 if (funcmode = 'TIMEOUT') then
1907
1908 resultout := 'COMPLETE:';
1909 return;
1910
1911 end if;
1912
1913 EXCEPTION
1914
1915 when others then
1916 wf_core.context('MRP_EXP_WF', 'IsType19', itemtype, itemkey, actid, funcmode);
1917 raise;
1918
1919 END IsType19;
1920
1921 PROCEDURE PurgeActivities(l_item_type in varchar2,
1922 l_item_key in varchar2)
1923 IS
1924 BEGIN
1925
1926 UPDATE wf_notifications
1927 SET end_date = SYSDATE - 450
1928 WHERE group_id IN
1929 (SELECT notification_id
1930 FROM wf_item_activity_statuses
1931 WHERE item_type = l_item_type
1932 AND item_key = l_item_key
1933 UNION
1934 SELECT notification_id
1935 FROM wf_item_activity_statuses_h
1936 WHERE item_type = l_item_type
1937 AND item_key = l_item_key);
1938
1939 UPDATE wf_items
1940 SET end_date = SYSDATE - 450
1941 WHERE item_type = l_item_type
1942 AND item_key = l_item_key ;
1943
1944 UPDATE wf_item_activity_statuses
1945 SET end_date = SYSDATE - 450
1946 WHERE item_type = l_item_type
1947 AND item_key = l_item_key;
1948
1949 UPDATE wf_item_activity_statuses_h
1950 SET end_date = SYSDATE - 450
1951 WHERE item_type = l_item_type
1952 AND item_key = l_item_key;
1953
1954 wf_purge.items(l_item_type,l_item_key,sysdate - 450);
1955
1956 END PurgeActivities;
1957
1958
1959
1960 PROCEDURE DeleteActivities( arg_compile_desig in varchar2,
1961 arg_organization_id in number) IS
1962
1963 CURSOR DELETE_ACTIVITIES_C(p_min_exception_id in number,
1964 p_max_exception_id in number) IS
1965 SELECT item_key
1966 FROM wf_items
1967 WHERE item_type = 'MRPEXPWF'
1968 AND to_number(item_key) >= p_min_exception_id
1969 AND to_number(item_key) <= p_max_exception_id;
1970
1971 -- SELECT wfi.item_key
1972 -- FROM wf_items wfi,
1973 -- mrp_exception_details_v exp
1974 -- WHERE wfi.item_key = to_char(exp.exception_id)
1975 -- AND wfi.item_type = 'MRPEXPWF'
1976 -- AND exp.exception_type in (1,2,3,6,7,8,9,10,12,13,14,15,16,17,18,19,20)
1977 -- AND exp.compile_designator = p_compile_desig
1978 -- AND exp.organization_id in
1979 -- (SELECT planned_organization
1980 -- FROM mrp_last_plan_orgs_v
1981 -- WHERE organization_id = p_owning_org_id
1982 -- AND compile_designator = p_compile_desig);
1983
1984 CURSOR CANCEL_NOTIFICATIONS_C( p_item_type in varchar2,
1985 p_item_key in varchar2) IS
1986 SELECT wn.notification_id
1987 FROM wf_notifications wn,
1988 wf_item_activity_statuses wias
1989 WHERE wn.status = 'OPEN'
1990 AND wn.notification_id = wias.notification_id
1991 AND wias.item_key = p_item_key
1992 AND wias.item_type = p_item_type;
1993
1997 l_wf_result varchar2(30);
1994 l_item_key varchar2(240);
1995 l_activity_count number;
1996 l_wf_status varchar2(8);
1998 l_notification_id number;
1999 l_min_exception_id number;
2000 l_max_exception_id number;
2001
2002 BEGIN
2003
2004 SELECT min_wf_except_id,
2005 max_wf_except_id
2006 INTO l_min_exception_id,
2007 l_max_exception_id
2008 FROM mrp_plans
2009 WHERE organization_id = arg_organization_id
2010 AND compile_designator = arg_compile_desig;
2011
2012 if (l_min_exception_id is not null and l_max_exception_id is not null) then
2013
2014 OPEN DELETE_ACTIVITIES_C(l_min_exception_id, l_max_exception_id);
2015 LOOP
2016 FETCH DELETE_ACTIVITIES_C INTO l_item_key;
2017 EXIT WHEN DELETE_ACTIVITIES_C%NOTFOUND OR DELETE_ACTIVITIES_C%NOTFOUND IS NULL;
2018
2019 -- It might happen that WF process is defined in WF_ITEMS table
2020 -- but not in WF_ITEM_ACTIVITY_STATUSES. If so, error.
2021
2022 -- SELECT count(*)
2023 -- INTO l_activity_count
2024 -- FROM wf_item_activity_statuses
2025 -- WHERE item_type = 'MRPEXPWF'
2026 -- AND item_key = l_item_key;
2027
2028 -- if (l_activity_count > 0) then
2029
2030 wf_engine.ItemStatus('MRPEXPWF', l_item_key, l_wf_status, l_wf_result);
2031
2032 -- A process might be completed but there could be open notifications
2033
2034 if (l_wf_status = wf_engine.eng_completed) then
2035
2036 OPEN CANCEL_NOTIFICATIONS_C('MRPEXPWF', l_item_key);
2037 LOOP
2038 FETCH CANCEL_NOTIFICATIONS_C INTO l_notification_id;
2039 EXIT WHEN CANCEL_NOTIFICATIONS_C%NOTFOUND OR CANCEL_NOTIFICATIONS_C%NOTFOUND IS NULL;
2040 wf_notification.Cancel(l_notification_id);
2041 END LOOP;
2042 CLOSE CANCEL_NOTIFICATIONS_C;
2043
2044 -- Cancel all notifications within process and process itself
2045 else
2046 wf_engine.AbortProcess('MRPEXPWF', l_item_key);
2047
2048 end if;
2049
2050 PurgeActivities('MRPEXPWF', l_item_key);
2051
2052 -- end if;
2053
2054 END LOOP;
2055 CLOSE DELETE_ACTIVITIES_C;
2056
2057 end if;
2058
2059 EXCEPTION
2060
2061 when NO_DATA_FOUND then
2062 null;
2063
2064 when others then
2065 null;
2066
2067 END DeleteActivities;
2068
2069
2070 END mrp_exp_wf;