[Home] [Help]
PACKAGE BODY: APPS.MSC_WF_ALLOC_ATP
Source
1 PACKAGE BODY MSC_WF_ALLOC_ATP AS -- package body
2 /* $Header: MSCWFATB.pls 120.3.12010000.2 2008/05/26 10:29:47 arrsubra ship $ */
3
4 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('MSC_ATP_DEBUG'), 'N');
5
6 -- ================= Procedures ====================
7
8 --ATP Debug Workflow
9 PROCEDURE DEBUG_WF(
10 p_session_id IN NUMBER,
11 p_login_user IN VARCHAR2,
12 p_session_loc_des IN VARCHAR2,
13 p_trace_loc_des IN VARCHAR2,
14 p_spid_des IN NUMBER
15 ) IS
16 PG_DEBUG_SRC varchar2(1) := NVL(FND_PROFILE.value('MSC_ATP_DEBUG'), 'N');
17 DEBUG_LOC_SRC varchar2(100);
18 SPID_SRC NUMBER;
19 l_process VARCHAR2(30) := 'ATP_DEBUG_NOTIFY';
20 l_itemtype VARCHAR2(30) := 'MSCDBG';
21 l_itemkey VARCHAR2(30);
22 TRACE_LOC_SRC varchar2(4000);
23
24 BEGIN
25
26 IF PG_DEBUG in ('Y', 'C') THEN
27 msc_sch_wb.atp_debug('debug_wf: ' || 'Debug WF started'||' p_session_id '|| p_session_id || ' p_login_user '|| p_login_user );
28 END IF;
29
30 select ltrim(rtrim(substr(value, instr(value,',',-1,1)+1)))
31 into DEBUG_LOC_SRC
32 from v$parameter where name= 'utl_file_dir';
33
34 IF PG_DEBUG in ('Y', 'C') THEN
35 msc_sch_wb.atp_debug('debug_wf: ' || DEBUG_LOC_SRC );
36 END IF;
37
38 IF (PG_DEBUG_SRC = 'C') THEN
39 SELECT spid
40 INTO SPID_SRC
41 FROM v$process
42 WHERE addr = (SELECT paddr FROM v$session
43 WHERE audsid=userenv('SESSIONID'));
44 SELECT value
45 INTO TRACE_LOC_SRC
46 FROM v$parameter
47 WHERE name = 'user_dump_dest';
48 END IF;
49
50
51 IF PG_DEBUG in ('Y', 'C') THEN
52 msc_sch_wb.atp_debug('debug_wf: ' || SPID_SRC );
53 END IF;
54
55 --Create work_flow process
56
57 select p_session_id ||':'||mrp_atp_schedule_temp_s.nextval
58 into l_itemkey
59 from mrp_atp_details_temp
60 where rownum = 1;
61
62 IF PG_DEBUG in ('Y', 'C') THEN
63 msc_sch_wb.atp_debug('debug_wf: ' || l_itemkey );
64 END IF;
65
66 IF PG_DEBUG in ('Y', 'C') THEN
67 msc_sch_wb.atp_debug('debug_wf: ' || 'CreateProcess' );
68 END IF;
69 wf_engine.CreateProcess(
70 itemtype => l_itemtype,
71 itemkey => l_itemkey,
72 process => l_process);
73
74 --setting parameters
75
76 IF PG_DEBUG in ('Y', 'C') THEN
77 msc_sch_wb.atp_debug('debug_wf: ' || 'Setting Param' );
78 END IF;
79
80 wf_engine.SetItemAttrNumber(
81 itemtype => l_itemtype,
82 itemkey => l_itemkey,
83 aname => 'SESSION_ID',
84 avalue => p_session_id);
85
86 IF PG_DEBUG in ('Y', 'C') THEN
87 msc_sch_wb.atp_debug('debug_wf: p_session_id ' || p_session_id );
88 END IF;
89
90 wf_engine.SetItemAttrText(
91 itemtype => l_itemtype,
92 itemkey => l_itemkey,
93 aname => 'DEBUG_LOC_SRC',
94 avalue => DEBUG_LOC_SRC);
95
96 IF PG_DEBUG in ('Y', 'C') THEN
97 msc_sch_wb.atp_debug('debug_wf: DEBUG_LOC_SRC ' || DEBUG_LOC_SRC );
98 END IF;
99
100 wf_engine.SetItemAttrNumber(
101 itemtype => l_itemtype,
102 itemkey => l_itemkey,
103 aname => 'SPID_SRC',
104 avalue => SPID_SRC);
105
106 IF PG_DEBUG in ('Y', 'C') THEN
107 msc_sch_wb.atp_debug('debug_wf: SPID_SRC ' || SPID_SRC );
108 END IF;
109
110 wf_engine.SetItemAttrText(
111 itemtype => l_itemtype,
112 itemkey => l_itemkey,
113 aname => 'TRACE_LOC_SRC',
114 avalue => TRACE_LOC_SRC);
115
116 IF PG_DEBUG in ('Y', 'C') THEN
117 msc_sch_wb.atp_debug('debug_wf: TRACE_LOC_SRC ' || TRACE_LOC_SRC );
118 END IF;
119
120 wf_engine.SetItemAttrText(
121 itemtype => l_itemtype,
122 itemkey => l_itemkey,
123 aname => 'DEBUG_LOC_DES',
124 avalue => p_session_loc_des);
125
126 IF PG_DEBUG in ('Y', 'C') THEN
127 msc_sch_wb.atp_debug('debug_wf: DEBUG_LOC_DES ' || p_session_loc_des );
128 END IF;
129
130 wf_engine.SetItemAttrNumber(
131 itemtype => l_itemtype,
132 itemkey => l_itemkey,
133 aname => 'SPID_DES',
134 avalue => p_spid_des);
135
136 IF PG_DEBUG in ('Y', 'C') THEN
137 msc_sch_wb.atp_debug('debug_wf: SPID_DES ' || p_spid_des );
138 END IF;
139
140 wf_engine.SetItemAttrText(
141 itemtype => l_itemtype,
142 itemkey => l_itemkey,
143 aname => 'TRACE_LOC_DES',
144 avalue => p_trace_loc_des);
145
146 IF PG_DEBUG in ('Y', 'C') THEN
147 msc_sch_wb.atp_debug('debug_wf: TRACE_LOC_DES ' || p_trace_loc_des );
148 END IF;
149
150 wf_engine.SetItemAttrText(
151 itemtype => l_itemtype,
152 itemkey => l_itemkey,
153 aname => 'LOGIN_USER',
154 avalue => p_login_user);
155
156 IF PG_DEBUG in ('Y', 'C') THEN
157 msc_sch_wb.atp_debug('debug_wf: LOGIN_USER ' || p_login_user );
158 END IF;
159
160 IF PG_DEBUG in ('Y', 'C') THEN
161 msc_sch_wb.atp_debug('debug_wf: ' || 'starting process' );
162 END IF;
163
164 wf_engine.StartProcess(
165 itemtype => l_itemtype,
166 itemkey => l_itemkey);
167
168 IF PG_DEBUG in ('Y', 'C') THEN
169 msc_sch_wb.atp_debug('debug_wf: ' || 'Debug WF done' );
170 END IF;
171
172 END DEBUG_WF;
173 --ATP Debug Workflow
174
175 PROCEDURE start_mscalloc_wf(
176 p_itemkey IN VARCHAR2,
177 p_inventory_item_id IN NUMBER,
178 p_inventory_item_name IN VARCHAR2,
179 p_plan_id IN NUMBER,
180 p_organization_id IN NUMBER,
181 p_organization_code IN VARCHAR2,
182 p_instance_id IN NUMBER,
183 p_demand_class IN VARCHAR2,
184 p_requested_qty IN NUMBER,
185 p_request_date IN DATE,
186 p_request_date_qty IN NUMBER,
187 p_available_qty IN NUMBER,
188 p_available_date IN DATE,
189 p_stolen_qty IN NUMBER,
190 p_customer_id IN NUMBER,
191 p_customer_site_id IN NUMBER,
192 p_order_number IN NUMBER)
193 IS
194 -- wf variables
195 l_itemtype VARCHAR2(10) := 'MSCALLOC';
196 l_profile VARCHAR2(1);
197 l_username VARCHAR2(100);
198 l_plan_name VARCHAR2(10);
199 l_customer_class VARCHAR2(100);
200 l_customer VARCHAR2(100);
201 l_location VARCHAR2(40);
202 l_address VARCHAR2(1600);
203
204 -- Bug 1757371, username and plan name was not being picked up in case
205 -- planner belongs to a different org than item.
206 CURSOR PLANNER_C( p_plan_id in number, p_inventory_item_id in number,
207 p_organization_id in number, p_instance_id in number)
208 IS
209 SELECT distinct pl.user_name, p.compile_designator
210 FROM msc_planners pl,
211 msc_system_items sys,
212 msc_plans p
213 WHERE sys.plan_id = p_plan_id
214 AND sys.organization_id = p_organization_id
215 AND sys.sr_instance_id = p_instance_id
216 AND sys.sr_inventory_item_id = p_inventory_item_id
217 AND sys.plan_id = p.plan_id
218 --AND pl.organization_id = sys.organization_id
219 AND pl.sr_instance_id = sys.sr_instance_id
220 AND pl.planner_code = sys.planner_code;
221 BEGIN
222 -- Check if the profile has been set for workflow to be installed
223 -- and activated.
224 l_profile := fnd_profile.value('MSC_ALLOCATED_ATP_WORKFLOW');
225 IF PG_DEBUG in ('Y', 'C') THEN
226 msc_sch_wb.atp_debug('start_mscalloc_wf: ' || 'WF Profile:' || l_profile);
227 END IF;
228
229 IF NVL(l_profile, 'N') = 'N' THEN
230 IF PG_DEBUG in ('Y', 'C') THEN
231 msc_sch_wb.atp_debug('start_mscalloc_wf: ' || '***Allocated ATP Workflow Profile = No***');
232 END IF;
233 RETURN;
234 END IF;
235
236 wf_engine.CreateProcess(
237 itemtype => l_itemtype,
238 itemkey => p_itemkey,
239 process => 'ALLOCATEDATPNOTIFY');
240
241 IF PG_DEBUG in ('Y', 'C') THEN
242 msc_sch_wb.atp_debug('start_mscalloc_wf: ' || 'Starting wf:' || p_itemkey);
243 msc_sch_wb.atp_debug('start_mscalloc_wf: ' || 'Starting wf:' || to_char(p_inventory_item_id));
244 END IF;
245
246 wf_engine.SetItemAttrNumber(
247 itemtype => l_itemtype,
248 itemkey => p_itemkey,
249 aname => 'INVENTORY_ITEM_ID',
250 avalue => p_inventory_item_id);
251
252 wf_engine.SetItemAttrText(
253 itemtype => l_itemtype,
254 itemkey => p_itemkey,
255 aname => 'INVENTORY_ITEM_NAME',
256 avalue => p_inventory_item_name);
257
258 wf_engine.SetItemAttrNumber(
259 itemtype => l_itemtype,
260 itemkey => p_itemkey,
261 aname => 'PLAN_ID',
262 avalue => p_plan_id);
263
264 wf_engine.SetItemAttrNumber(
265 itemtype => l_itemtype,
266 itemkey => p_itemkey,
267 aname => 'ORGANIZATION_ID',
268 avalue => p_organization_id);
269
270 wf_engine.SetItemAttrText(
271 itemtype => l_itemtype,
272 itemkey => p_itemkey,
273 aname => 'ORGANIZATION_CODE',
274 avalue => p_organization_code);
275
276 wf_engine.SetItemAttrNumber(
277 itemtype => l_itemtype,
278 itemkey => p_itemkey,
279 aname => 'INSTANCE_ID',
280 avalue => p_instance_id);
281
282 wf_engine.SetItemAttrText(
283 itemtype => l_itemtype,
284 itemkey => p_itemkey,
285 aname => 'DEMAND_CLASS',
286 avalue => p_demand_class);
287
288 wf_engine.SetItemAttrNumber(
289 itemtype => l_itemtype,
290 itemkey => p_itemkey,
291 aname => 'REQUESTED_QTY',
292 avalue => p_requested_qty);
293
294 wf_engine.SetItemAttrDate(
295 itemtype => l_itemtype,
296 itemkey => p_itemkey,
297 aname => 'REQUESTED_DATE',
298 avalue => p_request_date);
299
300 wf_engine.SetItemAttrNumber(
301 itemtype => l_itemtype,
302 itemkey => p_itemkey,
303 aname => 'REQUEST_DATE_QTY',
304 avalue => p_request_date_qty);
305
306 wf_engine.SetItemAttrNumber(
307 itemtype => l_itemtype,
308 itemkey => p_itemkey,
309 aname => 'AVAILABLE_QTY',
310 avalue => p_available_qty);
311
312 wf_engine.SetItemAttrDate(
313 itemtype => l_itemtype,
314 itemkey => p_itemkey,
315 aname => 'AVAILABLE_DATE',
316 avalue => p_available_date);
317
318 wf_engine.SetItemAttrNumber(
319 itemtype => l_itemtype,
320 itemkey => p_itemkey,
321 aname => 'STOLEN_QTY',
322 avalue => p_stolen_qty);
323
324 wf_engine.SetItemAttrNumber(
325 itemtype => l_itemtype,
326 itemkey => p_itemkey,
327 aname => 'ALLOCATED_QTY',
328 avalue => LEAST(p_requested_qty, NVL(p_request_date_qty,0)) -
329 NVL(p_stolen_qty, 0));
330
331 -- bug 1831563. instead of using msc_trading_partners and
332 -- msc_trading_partner_sites directly,we need to join to id_lid
333
334 SELECT tp.customer_class_code, tp.partner_name,
335 tps.location, tps.partner_address
336 INTO l_customer_class, l_customer, l_location, l_address
337 FROM msc_tp_id_lid tplid,
338 msc_trading_partners tp,
339 msc_tp_site_id_lid tpslid,
340 msc_trading_partner_sites tps
341 WHERE tplid.sr_tp_id = p_customer_id
342 AND tplid.sr_instance_id = p_instance_id
343 AND tplid.partner_type = 2
344 AND tpslid.sr_tp_site_id = p_customer_site_id
345 AND tpslid.sr_instance_id = p_instance_id
346 AND tpslid.partner_type = 2
347 AND tp.partner_id = tplid.tp_id
348 AND tps.partner_site_id = tpslid.tp_site_id ;
349
350 wf_engine.SetItemAttrText(
351 itemtype => l_itemtype,
352 itemkey => p_itemkey,
353 aname => 'CUST_CLASS',
354 avalue => l_customer_class);
355
356 wf_engine.SetItemAttrText(
357 itemtype => l_itemtype,
358 itemkey => p_itemkey,
359 aname => 'CUSTOMER',
360 avalue => l_customer);
361
362 wf_engine.SetItemAttrText(
363 itemtype => l_itemtype,
364 itemkey => p_itemkey,
365 aname => 'CUST_LOCATION',
366 avalue => l_location);
367
368 wf_engine.SetItemAttrText(
369 itemtype => l_itemtype,
370 itemkey => p_itemkey,
371 aname => 'CUST_ADDRESS',
372 avalue => l_address);
373
374 wf_engine.SetItemAttrNumber(
375 itemtype => l_itemtype,
376 itemkey => p_itemkey,
377 aname => 'ORDER_NUMBER',
378 avalue => p_order_number);
379
380 -- Get username (planner name) for forwarding the notifications.
381 OPEN PLANNER_C(p_plan_id, p_inventory_item_id,
382 p_organization_id, p_instance_id);
383 FETCH PLANNER_C INTO l_username, l_plan_name;
384 CLOSE PLANNER_C;
385
386 wf_engine.SetItemAttrText(
387 itemtype => l_itemtype,
388 itemkey => p_itemkey,
389 aname => 'PLAN_NAME',
390 avalue => l_plan_name);
391
392 wf_engine.SetItemAttrText(
393 itemtype => l_itemtype,
394 itemkey => p_itemkey,
395 aname => 'USERNAME',
396 avalue => l_username);
397
398 wf_engine.StartProcess(
399 itemtype => l_itemtype,
400 itemkey => p_itemkey);
401 END start_mscalloc_wf;
402
403
404 PROCEDURE Within_Allocation(
405 itemtype in varchar2,
406 itemkey in varchar2,
407 actid in number,
408 funcmode in varchar2,
409 resultout out NoCopy varchar2)
410 IS
411 l_allocated_qty NUMBER;
412 l_requested_qty NUMBER;
413 BEGIN
414
415 l_allocated_qty := wf_engine.GetItemAttrNumber(
416 itemtype => itemtype,
417 itemkey => itemkey,
418 aname => 'ALLOCATED_QTY');
419
420 l_requested_qty := wf_engine.GetItemAttrNumber(
421 itemtype => itemtype,
422 itemkey => itemkey,
423 aname => 'REQUESTED_QTY');
424
425 IF PG_DEBUG in ('Y', 'C') THEN
426 msc_sch_wb.atp_debug('Within_Allocation: ' || 'Inside Shortage:' || l_allocated_qty ||
427 ' : '||l_requested_qty);
428 END IF;
429
430 if (NVL(l_allocated_qty, 0) >= NVL(l_requested_qty, 0)) then
431 resultout := 'COMPLETE:Y';
432 else
433 resultout := 'COMPLETE:N';
434 end if;
435
436 return;
437 END Within_Allocation;
438
439 PROCEDURE Qty_Stolen(
440 itemtype in varchar2,
441 itemkey in varchar2,
442 actid in number,
443 funcmode in varchar2,
444 resultout out NoCopy varchar2)
445 IS
446 l_stolen_qty NUMBER;
447 BEGIN
448
449 l_stolen_qty := wf_engine.GetItemAttrNumber(
450 itemtype => itemtype,
451 itemkey => itemkey,
452 aname => 'STOLEN_QTY');
453
454 IF PG_DEBUG in ('Y', 'C') THEN
458 if NVL(l_stolen_qty, 0) > 0 then
455 msc_sch_wb.atp_debug('Qty_Stolen: ' || 'Inside Stolen:' || l_stolen_qty);
456 END IF;
457
459 resultout := 'COMPLETE:Y';
460 else
461 resultout := 'COMPLETE:N';
462 end if;
463
464 return;
465 END Qty_Stolen;
466
467 PROCEDURE ATP_Satisfy(
468 itemtype in varchar2,
469 itemkey in varchar2,
470 actid in number,
471 funcmode in varchar2,
472 resultout out NoCopy varchar2)
473 IS
474 l_request_date_qty NUMBER;
475 l_requested_qty NUMBER;
476 BEGIN
477
478 l_request_date_qty := wf_engine.GetItemAttrNumber(
479 itemtype => itemtype,
480 itemkey => itemkey,
481 aname => 'REQUEST_DATE_QTY');
482
483 l_requested_qty := wf_engine.GetItemAttrNumber(
484 itemtype => itemtype,
485 itemkey => itemkey,
486 aname => 'REQUESTED_QTY');
487
488 IF PG_DEBUG in ('Y', 'C') THEN
489 msc_sch_wb.atp_debug('ATP_Satisfy: ' || 'Inside ATP Satisfy:' || l_request_date_qty);
490 END IF;
491
492 if (NVL(l_request_date_qty, 0) >= NVL(l_requested_qty, 0)) then
493 resultout := 'COMPLETE:Y';
494 else
495 resultout := 'COMPLETE:N';
496 end if;
497
498 return;
499 END ATP_Satisfy;
500
501
502 PROCEDURE start_mscatp_wf(
503 p_itemkey IN VARCHAR2,
504 p_inventory_item_id IN NUMBER,
505 p_inventory_item_name IN VARCHAR2,
506 p_plan_id IN NUMBER,
507 p_organization_id IN NUMBER,
508 p_organization_code IN VARCHAR2,
509 p_instance_id IN NUMBER,
510 p_demand_class IN VARCHAR2,
511 p_requested_qty IN NUMBER,
512 p_request_date IN DATE,
513 p_request_date_qty IN NUMBER,
514 p_available_qty IN NUMBER,
515 p_available_date IN DATE,
516 p_customer_id IN NUMBER,
517 p_customer_site_id IN NUMBER,
518 p_order_number IN NUMBER,
519 p_line_number IN NUMBER)
520 IS
521 -- wf variables
522 l_itemtype VARCHAR2(10) := 'MSCATP';
523 l_profile VARCHAR2(1);
524 l_username VARCHAR2(100);
525 l_plan_name VARCHAR2(10);
526 l_customer_class VARCHAR2(100);
527 l_customer VARCHAR2(100);
528 l_location VARCHAR2(40);
529 l_address VARCHAR2(1600);
530
531 -- Bug 1757371, username and plan name was not being picked up in case
532 -- planner belongs to a different org than item.
533 CURSOR PLANNER_C
534 IS
535 SELECT distinct pl.user_name, p.compile_designator
536 FROM msc_planners pl,
537 msc_system_items sys,
538 msc_plans p
539 WHERE sys.plan_id = p_plan_id
540 AND sys.organization_id = p_organization_id
541 AND sys.sr_instance_id = p_instance_id
542 AND sys.sr_inventory_item_id = p_inventory_item_id
543 AND sys.plan_id = p.plan_id
544 --AND pl.organization_id = sys.organization_id
545 AND pl.sr_instance_id = sys.sr_instance_id
546 AND pl.planner_code = sys.planner_code;
547 BEGIN
548 -- Check if the profile has been set for workflow to be installed
549 -- and activated.
550 l_profile := fnd_profile.value('MSC_ALLOCATED_ATP_WORKFLOW');
551 IF PG_DEBUG in ('Y', 'C') THEN
552 msc_sch_wb.atp_debug('start_mscatp_wf: ' || 'WF Profile:' || l_profile);
553 END IF;
554
555 IF NVL(l_profile, 'N') = 'N' THEN
556 IF PG_DEBUG in ('Y', 'C') THEN
557 msc_sch_wb.atp_debug('start_mscatp_wf: ' || '***Allocated ATP Workflow Profile = No***');
558 END IF;
559 RETURN;
560 END IF;
561
562 wf_engine.CreateProcess(
563 itemtype => l_itemtype,
564 itemkey => p_itemkey,
565 process => 'ATP_OVERRIDE_NOTIFY');
566
567 IF PG_DEBUG in ('Y', 'C') THEN
568 msc_sch_wb.atp_debug('start_mscatp_wf: ' || 'Starting wf:' || p_itemkey);
569 msc_sch_wb.atp_debug('start_mscatp_wf: ' || 'Starting wf:' || to_char(p_inventory_item_id));
570 END IF;
571
572 wf_engine.SetItemAttrText(
573 itemtype => l_itemtype,
574 itemkey => p_itemkey,
575 aname => 'INVENTORY_ITEM_NAME',
576 avalue => p_inventory_item_name);
577
578 wf_engine.SetItemAttrText(
579 itemtype => l_itemtype,
580 itemkey => p_itemkey,
581 aname => 'ORGANIZATION_CODE',
582 avalue => p_organization_code);
583
584 wf_engine.SetItemAttrText(
585 itemtype => l_itemtype,
586 itemkey => p_itemkey,
587 aname => 'DEMAND_CLASS',
588 avalue => p_demand_class);
589
590 wf_engine.SetItemAttrNumber(
591 itemtype => l_itemtype,
592 itemkey => p_itemkey,
593 aname => 'REQUESTED_QTY',
594 avalue => p_requested_qty);
595
596 wf_engine.SetItemAttrDate(
597 itemtype => l_itemtype,
598 itemkey => p_itemkey,
599 aname => 'REQUESTED_DATE',
600 avalue => p_request_date);
601
602 wf_engine.SetItemAttrNumber(
603 itemtype => l_itemtype,
604 itemkey => p_itemkey,
605 aname => 'REQUEST_DATE_QTY',
606 avalue => p_request_date_qty);
607
608 wf_engine.SetItemAttrNumber(
609 itemtype => l_itemtype,
610 itemkey => p_itemkey,
611 aname => 'AVAILABLE_QTY',
612 avalue => p_available_qty);
613
614 wf_engine.SetItemAttrDate(
615 itemtype => l_itemtype,
616 itemkey => p_itemkey,
617 aname => 'AVAILABLE_DATE',
618 avalue => p_available_date);
619
620
621 -- bug 1831563. instead of using msc_trading_partners and
622 -- msc_trading_partner_sites directly,we need to join to id_lid
623
624 SELECT tp.customer_class_code, tp.partner_name,
625 tps.location, tps.partner_address
626 INTO l_customer_class, l_customer, l_location, l_address
627 FROM msc_tp_id_lid tplid,
628 msc_trading_partners tp,
629 msc_tp_site_id_lid tpslid,
630 msc_trading_partner_sites tps
631 WHERE tplid.sr_tp_id = p_customer_id
632 AND tplid.sr_instance_id = p_instance_id
633 AND tplid.partner_type = 2
634 AND tpslid.sr_tp_site_id = p_customer_site_id
635 AND tpslid.sr_instance_id = p_instance_id
636 AND tpslid.partner_type = 2
637 AND tp.partner_id = tplid.tp_id
638 AND tps.partner_site_id = tpslid.tp_site_id ;
639
640 wf_engine.SetItemAttrText(
641 itemtype => l_itemtype,
642 itemkey => p_itemkey,
643 aname => 'CUST_CLASS',
644 avalue => l_customer_class);
645
646 wf_engine.SetItemAttrText(
647 itemtype => l_itemtype,
648 itemkey => p_itemkey,
649 aname => 'CUSTOMER',
650 avalue => l_customer);
651
652 wf_engine.SetItemAttrText(
653 itemtype => l_itemtype,
654 itemkey => p_itemkey,
655 aname => 'CUST_LOCATION',
656 avalue => l_location);
657
658 wf_engine.SetItemAttrText(
659 itemtype => l_itemtype,
660 itemkey => p_itemkey,
661 aname => 'CUST_ADDRESS',
662 avalue => l_address);
663
664 wf_engine.SetItemAttrNumber(
665 itemtype => l_itemtype,
666 itemkey => p_itemkey,
667 aname => 'ORDER_NUMBER',
668 avalue => p_order_number);
669
670 wf_engine.SetItemAttrNumber(
671 itemtype => l_itemtype,
672 itemkey => p_itemkey,
673 aname => 'LINE_NUMBER',
674 avalue => p_line_number);
675
676 -- Get username (planner name) for forwarding the notifications.
677 OPEN PLANNER_C;
678 FETCH PLANNER_C INTO l_username, l_plan_name;
679 CLOSE PLANNER_C;
680
681 wf_engine.SetItemAttrText(
682 itemtype => l_itemtype,
683 itemkey => p_itemkey,
684 aname => 'PLAN_NAME',
685 avalue => l_plan_name);
686
687 wf_engine.SetItemAttrText(
688 itemtype => l_itemtype,
689 itemkey => p_itemkey,
690 aname => 'USERNAME',
691 avalue => l_username);
692
693 wf_engine.StartProcess(
694 itemtype => l_itemtype,
695 itemkey => p_itemkey);
696 END start_mscatp_wf;
697
698
699
700 END MSC_WF_ALLOC_ATP;