[Home] [Help]
PACKAGE BODY: APPS.WSH_WF
Source
1 PACKAGE BODY WSH_WF as
2 /* $Header: WSHUTWFB.pls 120.0 2005/05/26 18:47:56 appldev noship $ */
3
4 TYPE wfRecTyp IS RECORD (
5 source_header_id NUMBER,
6 source_code VARCHAR2(10),
7 contact_type VARCHAR2(10),
8 contact_id NUMBER,
9 wf_started BOOLEAN);
10
11 TYPE wfRecTabTyp IS TABLE OF wfRecTyp INDEX BY BINARY_INTEGER;
12 g_wf_table wfRecTabTyp;
13
14
15
16 --
17 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_WF';
18 --
19 PROCEDURE Start_Process(
20 p_source_header_id in number,
21 p_source_code in varchar2,
22 p_order_number in number,
23 p_contact_type in varchar2,
24 p_contact_name in varchar2,
25 p_contact_id in number,
26 p_contact_last_name in varchar2,
27 p_shipped_lines in varchar2,
28 p_backordered_lines in varchar2,
29 p_ship_notif_date in date,
30 p_bo_notif_date in date,
31 p_workflow_process in varchar2 default null,
32 p_item_type in varchar2 default null) is
33
34 l_workflow_process varchar2(30) := nvl(p_workflow_process,'WSHNOTIF');
35 l_item_type varchar2(30) := nvl(p_item_type,'WSHNOTIF');
36 l_item_key varchar2(150) := to_char(p_source_header_id)||'-'||p_source_code||'-'||p_contact_name ;
37 l_item_userkey varchar2(30) := to_char(p_source_header_id) ;
38
39 --
40 l_debug_on BOOLEAN;
41 --
42 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'START_PROCESS';
43 --
44 begin
45 --
46 -- Debug Statements
47 --
48 --
49 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
50 --
51 IF l_debug_on IS NULL
52 THEN
53 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
54 END IF;
55 --
56 IF l_debug_on THEN
57 WSH_DEBUG_SV.push(l_module_name);
58 --
59 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_HEADER_ID',P_SOURCE_HEADER_ID);
60 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_CODE',P_SOURCE_CODE);
61 WSH_DEBUG_SV.log(l_module_name,'P_ORDER_NUMBER',P_ORDER_NUMBER);
62 WSH_DEBUG_SV.log(l_module_name,'P_CONTACT_TYPE',P_CONTACT_TYPE);
63 WSH_DEBUG_SV.log(l_module_name,'P_CONTACT_NAME',P_CONTACT_NAME);
64 WSH_DEBUG_SV.log(l_module_name,'P_CONTACT_ID',P_CONTACT_ID);
65 WSH_DEBUG_SV.log(l_module_name,'P_CONTACT_LAST_NAME',P_CONTACT_LAST_NAME);
66 WSH_DEBUG_SV.log(l_module_name,'P_SHIPPED_LINES',P_SHIPPED_LINES);
67 WSH_DEBUG_SV.log(l_module_name,'P_BACKORDERED_LINES',P_BACKORDERED_LINES);
68 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_NOTIF_DATE',P_SHIP_NOTIF_DATE);
69 WSH_DEBUG_SV.log(l_module_name,'P_BO_NOTIF_DATE',P_BO_NOTIF_DATE);
70 WSH_DEBUG_SV.log(l_module_name,'P_WORKFLOW_PROCESS',P_WORKFLOW_PROCESS);
71 WSH_DEBUG_SV.log(l_module_name,'P_ITEM_TYPE',P_ITEM_TYPE);
72 END IF;
73 --
74 --
75 -- Debug Statements
76 --
77 IF l_debug_on THEN
78 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WF_ENGINE.CREATEPROCESS',WSH_DEBUG_SV.C_PROC_LEVEL);
79 END IF;
80 --
81 wf_engine.CreateProcess(
82 ItemType => l_item_type,
83 ItemKey => l_item_key,
84 process => l_workflow_process );
85
86 --
87 -- Debug Statements
88 --
89 IF l_debug_on THEN
90 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WF_ENGINE.SETITEMATTRNUMBER',WSH_DEBUG_SV.C_PROC_LEVEL);
91 END IF;
92 --
93 wf_engine.SetItemAttrNumber(
94 ItemType => l_item_type,
95 ItemKey => l_item_key,
96 aname => 'SOURCE_HEADER_ID',
97 avalue => p_source_header_id );
98
99 --
100 -- Debug Statements
101 --
102 IF l_debug_on THEN
103 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WF_ENGINE.SETITEMATTRTEXT',WSH_DEBUG_SV.C_PROC_LEVEL);
104 END IF;
105 --
106 wf_engine.SetItemAttrText(
107 ItemType => l_item_type,
108 ItemKey => l_item_key,
109 aname => 'SOURCE_CODE',
110 avalue => p_source_code);
111
112 --
113 -- Debug Statements
114 --
115 IF l_debug_on THEN
116 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WF_ENGINE.SETITEMATTRNUMBER',WSH_DEBUG_SV.C_PROC_LEVEL);
117 END IF;
118 --
119 wf_engine.SetItemAttrNumber(
120 ItemType => l_item_type,
121 ItemKey => l_item_key,
122 aname => 'ORDER_NUMBER',
123 avalue => p_order_number );
124
125 --
126 -- Debug Statements
127 --
128 IF l_debug_on THEN
129 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WF_ENGINE.SETITEMATTRTEXT',WSH_DEBUG_SV.C_PROC_LEVEL);
130 END IF;
131 --
132 wf_engine.SetItemAttrText(
133 ItemType => l_item_type,
134 ItemKey => l_item_key,
135 aname => 'CONTACT_TYPE',
136 avalue => p_contact_type);
137
138 --
139 -- Debug Statements
140 --
141 IF l_debug_on THEN
142 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WF_ENGINE.SETITEMATTRTEXT',WSH_DEBUG_SV.C_PROC_LEVEL);
143 END IF;
144 --
145 wf_engine.SetItemAttrText(
146 ItemType => l_item_type,
147 ItemKey => l_item_key,
148 aname => 'CONTACT_NAME',
149 avalue => p_contact_name);
150
151 --
152 -- Debug Statements
153 --
154 IF l_debug_on THEN
155 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WF_ENGINE.SETITEMATTRNUMBER',WSH_DEBUG_SV.C_PROC_LEVEL);
156 END IF;
157 --
158 wf_engine.SetItemAttrNumber(
159 ItemType => l_item_type,
160 ItemKey => l_item_key,
161 aname => 'CONTACT_ID',
162 avalue => p_contact_id);
163
164 --
165 -- Debug Statements
166 --
167 IF l_debug_on THEN
168 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WF_ENGINE.SETITEMATTRTEXT',WSH_DEBUG_SV.C_PROC_LEVEL);
169 END IF;
170 --
171 wf_engine.SetItemAttrText(
172 ItemType => l_item_type,
173 ItemKey => l_item_key,
174 aname => 'CONTACT_LAST_NAME',
175 avalue => p_contact_last_name);
176
177 --
178 -- Debug Statements
179 --
180 IF l_debug_on THEN
181 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WF_ENGINE.SETITEMATTRDATE',WSH_DEBUG_SV.C_PROC_LEVEL);
182 END IF;
183 --
184 wf_engine.SetItemAttrDate(
185 ItemType => l_item_type,
186 ItemKey => l_item_key,
187 aname => 'LAST_SHIP_NOTIF_DATE',
188 avalue => p_ship_notif_date);
189
190 --
191 -- Debug Statements
192 --
193 IF l_debug_on THEN
194 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WF_ENGINE.SETITEMATTRDATE',WSH_DEBUG_SV.C_PROC_LEVEL);
195 END IF;
196 --
197 wf_engine.SetItemAttrDate(
198 ItemType => l_item_type,
199 ItemKey => l_item_key,
200 aname => 'LAST_BO_NOTIF_DATE',
201 avalue => p_bo_notif_date);
202
203 --
204 -- Debug Statements
205 --
206 IF l_debug_on THEN
207 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WF_ENGINE.SETITEMUSERKEY',WSH_DEBUG_SV.C_PROC_LEVEL);
208 END IF;
209 --
210 wf_engine.SetItemUserKey(
211 ItemType => l_item_type,
212 ItemKey => l_item_key,
213 UserKey => l_item_userkey );
214
215 --
216 -- Debug Statements
217 --
218 IF l_debug_on THEN
219 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WF_ENGINE.STARTPROCESS',WSH_DEBUG_SV.C_PROC_LEVEL);
220 END IF;
221 --
222 wf_engine.StartProcess(
223 ItemType => l_item_type,
224 ItemKey => l_item_key);
225 --
226 -- Debug Statements
227 --
228 IF l_debug_on THEN
229 WSH_DEBUG_SV.pop(l_module_name);
230 END IF;
231 --
232 exception
233 when others then
234 --
235 -- Debug Statements
236 --
237 IF l_debug_on THEN
238 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WF_CORE.CONTEXT',WSH_DEBUG_SV.C_PROC_LEVEL);
239 END IF;
240 --
241 wf_core.context('WSHNOTIF','StartProcess',
242 to_char(p_source_header_id),
243 p_source_code,
244 to_char(p_order_number),
245 p_contact_type,
246 to_char(p_contact_id));
247 --
248 -- Debug Statements
249 --
250 IF l_debug_on THEN
251 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
252 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
253 END IF;
254 --
255 raise;
256 END Start_Process;
257
258
259 PROCEDURE Order_Status(
260 itemtype in varchar2,
261 itemkey in varchar2,
262 actid in number,
263 funcmode in varchar2,
264 resultout in out NOCOPY varchar2) is
265
266 CURSOR c_shipped_lines(
267 p_source_header_id in number,
268 p_source_code in varchar2,
269 p_contact_type in varchar2,
270 p_contact_id in number,
271 p_last_notif_date in date) is
272 SELECT
273 wdd.delivery_detail_id
274 FROM
275 wsh_delivery_details wdd,
276 wsh_delivery_assignments_v wda,
277 wsh_new_deliveries wnd,
278 mtl_system_items msi
279 WHERE wdd.delivery_detail_id = wda.delivery_detail_id
280 AND wda.delivery_id = wnd.delivery_id
281 AND wnd.status_code in ('IT','CL')
282 AND wnd.initial_pickup_date > p_last_notif_date
283 AND nvl(wdd.shipped_quantity,0) > 0
284 AND wdd.inventory_item_id = msi.inventory_item_id
285 AND wdd.organization_id = msi.organization_id
286 AND wdd.source_header_id = p_source_header_id
287 AND wdd.source_code = p_source_code
288 AND nvl(wdd.LINE_DIRECTION , 'O') IN ('O', 'IO') -- J Inbound Logistics jckwok
289 AND decode(p_contact_type,
290 'SHIP_TO',wdd.ship_to_contact_id,
291 'SOLD_TO',wdd.sold_to_contact_id,
292 wdd.customer_id) = p_contact_id;
293
294 CURSOR c_backordered_lines(
295 p_source_header_id in number,
296 p_source_code in varchar2,
297 p_contact_type in varchar2,
298 p_contact_id in number,
299 p_last_notif_date in date) is
300 SELECT
301 wdd.delivery_detail_id
302 FROM
303 wsh_delivery_details wdd,
304 wsh_delivery_assignments_v wda,
305 wsh_new_deliveries wnd,
306 mtl_system_items msi
307 WHERE wdd.delivery_detail_id = wda.delivery_detail_id
308 AND wdd.date_scheduled < sysdate
309 --AND wdd.date_scheduled > p_last_notif_date
310 AND nvl(wdd.picked_quantity, wdd.requested_quantity) > 0
311 AND wdd.released_status NOT IN ('C', 'D')
312 AND wda.delivery_id = wnd.delivery_id (+)
313 AND nvl(wnd.status_code,'XX') not in ('IT','CL')
314 AND wdd.inventory_item_id = msi.inventory_item_id
315 AND wdd.organization_id = msi.organization_id
316 AND wdd.source_header_id = p_source_header_id
317 AND wdd.source_code = p_source_code
318 AND nvl(wdd.LINE_DIRECTION , 'O') IN ('O', 'IO') -- J Inbound Logistics jckwok
319 AND decode(p_contact_type,
320 'SHIP_TO',wdd.ship_to_contact_id,
321 'SOLD_TO',wdd.sold_to_contact_id,
322 wdd.customer_id) = p_contact_id;
323
324 l_source_header_id number;
325 l_source_code varchar2(30);
326 l_contact_type varchar2(10);
327 l_contact_id number;
328 l_ship_notif_date date;
329 l_bo_notif_date date;
330
331 l_delivery_detail_id number;
332 l_shipped BOOLEAN;
333 l_backordered BOOLEAN;
334
335 --
336 l_debug_on BOOLEAN;
337 --
338 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'ORDER_STATUS';
339 --
340 begin
341 --
342 -- Debug Statements
343 --
344 --
345 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
346 --
347 IF l_debug_on IS NULL
348 THEN
349 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
350 END IF;
351 --
352 IF l_debug_on THEN
353 WSH_DEBUG_SV.push(l_module_name);
354 --
355 WSH_DEBUG_SV.log(l_module_name,'ITEMTYPE',ITEMTYPE);
356 WSH_DEBUG_SV.log(l_module_name,'ITEMKEY',ITEMKEY);
357 WSH_DEBUG_SV.log(l_module_name,'ACTID',ACTID);
358 WSH_DEBUG_SV.log(l_module_name,'FUNCMODE',FUNCMODE);
359 WSH_DEBUG_SV.log(l_module_name,'RESULTOUT',RESULTOUT);
360 END IF;
361 --
362 if (funcmode = 'RUN') then
363 --
364 -- Debug Statements
365 --
366 IF l_debug_on THEN
367 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WF_ENGINE.GETITEMATTRNUMBER',WSH_DEBUG_SV.C_PROC_LEVEL);
368 END IF;
369 --
370 l_source_header_id := wf_engine.GetItemAttrNumber(
371 ItemType => itemtype,
372 ItemKey => itemkey,
373 aname => 'SOURCE_HEADER_ID');
374
375 --
376 -- Debug Statements
377 --
378 IF l_debug_on THEN
379 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WF_ENGINE.GETITEMATTRTEXT',WSH_DEBUG_SV.C_PROC_LEVEL);
380 END IF;
381 --
382 l_source_code := wf_engine.GetItemAttrText(
383 ItemType => itemtype,
384 ItemKey => itemkey,
385 aname => 'SOURCE_CODE');
386
387 --
388 -- Debug Statements
389 --
390 IF l_debug_on THEN
391 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WF_ENGINE.GETITEMATTRTEXT',WSH_DEBUG_SV.C_PROC_LEVEL);
392 END IF;
393 --
394 l_contact_type := wf_engine.GetItemAttrText(
395 ItemType => itemtype,
396 ItemKey => itemkey,
397 aname => 'CONTACT_TYPE');
398
399 --
400 -- Debug Statements
401 --
402 IF l_debug_on THEN
403 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WF_ENGINE.GETITEMATTRNUMBER',WSH_DEBUG_SV.C_PROC_LEVEL);
404 END IF;
405 --
406 l_contact_id := wf_engine.GetItemAttrNumber(
407 ItemType => itemtype,
408 ItemKey => itemkey,
409 aname => 'CONTACT_ID');
410
411 --
412 -- Debug Statements
413 --
414 IF l_debug_on THEN
415 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WF_ENGINE.GETITEMATTRDATE',WSH_DEBUG_SV.C_PROC_LEVEL);
416 END IF;
417 --
418 l_bo_notif_date := wf_engine.GetItemAttrDate(
419 ItemType => itemtype,
420 ItemKey => itemkey,
421 aname => 'LAST_BO_NOTIF_DATE');
422
423 --
424 -- Debug Statements
425 --
426 IF l_debug_on THEN
427 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WF_ENGINE.GETITEMATTRDATE',WSH_DEBUG_SV.C_PROC_LEVEL);
428 END IF;
429 --
430 l_ship_notif_date := wf_engine.GetItemAttrDate(
431 ItemType => itemtype,
432 ItemKey => itemkey,
433 aname => 'LAST_SHIP_NOTIF_DATE');
434
435 l_delivery_detail_id := 0;
436 open c_shipped_lines(l_source_header_id, l_source_code, l_contact_type, l_contact_id, l_ship_notif_date);
437 fetch c_shipped_lines
438 into l_delivery_detail_id;
439 close c_shipped_lines;
440
441 if (l_delivery_detail_id <> 0) then
442 l_shipped := TRUE;
443 else
444 l_shipped := FALSE;
445 end if;
446
447 l_delivery_detail_id := 0;
448 open c_backordered_lines(l_source_header_id, l_source_code, l_contact_type, l_contact_id, l_bo_notif_date);
449 fetch c_backordered_lines
450 into l_delivery_detail_id;
451 close c_backordered_lines;
452
453 if (l_delivery_detail_id <> 0) then
454 l_backordered := TRUE;
455 else
456 l_backordered := FALSE;
457 end if;
458
459 if (l_shipped and l_backordered) then
460 resultout := 'COMPLETE:SHIPPED_BACKORDERD';
461 elsif (l_shipped) then
462 resultout := 'COMPLETE:SHIPPED';
463 elsif (l_backordered) then
464 resultout := 'COMPLETE:BACKORDERED';
465 else
466 resultout := 'COMPLETE:NO_STATUS';
467 end if;
468
469 elsif (funcmode = 'CANCEL') then
470 resultout := 'COMPLETE';
471 end if;
472
473 --
474 -- Debug Statements
475 --
476 IF l_debug_on THEN
477 WSH_DEBUG_SV.log(l_module_name,'resultout',resultout);
478 WSH_DEBUG_SV.pop(l_module_name);
479 END IF;
480 --
481 return;
482 END Order_Status;
483
484
485 PROCEDURE Order_fulfilled(
486 itemtype in varchar2,
487 itemkey in varchar2,
488 actid in number,
489 funcmode in varchar2,
490 resultout in out NOCOPY varchar2) is
491
492 cursor c_order_fulfilled(
493 p_source_header_id in number,
494 p_source_code in varchar2,
495 p_contact_type in varchar2,
496 p_contact_id in number) is
497 SELECT wdd.delivery_detail_id
498 FROM
499 wsh_delivery_details wdd,
500 wsh_delivery_assignments_v wda,
501 wsh_new_deliveries wnd
502 WHERE wdd.delivery_detail_id = wda.delivery_detail_id
503 AND wda.delivery_id = wnd.delivery_id (+)
504 AND nvl(wnd.status_code,'XX') not in ('IT','CL')
505 AND wdd.source_header_id = p_source_header_id
506 AND wdd.source_code = p_source_code
507 AND nvl(wdd.LINE_DIRECTION , 'O') IN ('O', 'IO') -- J Inbound Logistics jckwok
508 AND decode(p_contact_type,
509 'SHIP_TO',wdd.ship_to_contact_id,
510 'SOLD_TO',wdd.sold_to_contact_id,
511 wdd.customer_id) = p_contact_id;
512
513 l_source_header_id number;
514 l_source_code varchar2(30);
515 l_contact_type varchar2(10);
516 l_contact_id number;
517 l_delivery_detail_id number := 0;
518
519 --
520 l_debug_on BOOLEAN;
521 --
522 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'ORDER_FULFILLED';
523 --
524 begin
525 --
526 -- Debug Statements
527 --
528 --
529 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
530 --
531 IF l_debug_on IS NULL
532 THEN
533 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
534 END IF;
535 --
536 IF l_debug_on THEN
537 WSH_DEBUG_SV.push(l_module_name);
538 --
539 WSH_DEBUG_SV.log(l_module_name,'ITEMTYPE',ITEMTYPE);
540 WSH_DEBUG_SV.log(l_module_name,'ITEMKEY',ITEMKEY);
541 WSH_DEBUG_SV.log(l_module_name,'ACTID',ACTID);
542 WSH_DEBUG_SV.log(l_module_name,'FUNCMODE',FUNCMODE);
543 WSH_DEBUG_SV.log(l_module_name,'RESULTOUT',RESULTOUT);
544 END IF;
545 --
546 if (funcmode = 'RUN') then
547 --
548 -- Debug Statements
549 --
550 IF l_debug_on THEN
551 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WF_ENGINE.GETITEMATTRNUMBER',WSH_DEBUG_SV.C_PROC_LEVEL);
552 END IF;
553 --
554 l_source_header_id := wf_engine.GetItemAttrNumber(
555 ItemType => itemtype,
556 ItemKey => itemkey,
557 aname => 'SOURCE_HEADER_ID');
558
559 --
560 -- Debug Statements
561 --
562 IF l_debug_on THEN
563 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WF_ENGINE.GETITEMATTRTEXT',WSH_DEBUG_SV.C_PROC_LEVEL);
564 END IF;
565 --
566 l_source_code := wf_engine.GetItemAttrText(
567 ItemType => itemtype,
568 ItemKey => itemkey,
569 aname => 'SOURCE_CODE');
570
571 --
572 -- Debug Statements
573 --
574 IF l_debug_on THEN
575 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WF_ENGINE.GETITEMATTRTEXT',WSH_DEBUG_SV.C_PROC_LEVEL);
576 END IF;
577 --
578 l_contact_type := wf_engine.GetItemAttrText(
579 ItemType => itemtype,
580 ItemKey => itemkey,
581 aname => 'CONTACT_TYPE');
582
583 --
584 -- Debug Statements
585 --
586 IF l_debug_on THEN
587 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WF_ENGINE.GETITEMATTRNUMBER',WSH_DEBUG_SV.C_PROC_LEVEL);
588 END IF;
589 --
590 l_contact_id := wf_engine.GetItemAttrNumber(
591 ItemType => itemtype,
592 ItemKey => itemkey,
593 aname => 'CONTACT_ID');
594
595 open c_order_fulfilled(l_source_header_id,l_source_code,l_contact_type,l_contact_id);
596 fetch c_order_fulfilled
597 into l_delivery_detail_id;
598 close c_order_fulfilled;
599
600 if (l_delivery_detail_id = 0) then
601 resultout := 'COMPLETE:YES';
602 else
603 resultout := 'COMPLETE:NO';
604 end if;
605
606 elsif (funcmode = 'CANCEL') then
607 resultout := 'COMPLETE';
608 end if;
609
610 --
611 -- Debug Statements
612 --
613 IF l_debug_on THEN
614 WSH_DEBUG_SV.log(l_module_name,'resultout',resultout);
615 WSH_DEBUG_SV.pop(l_module_name);
616 END IF;
617 --
618 return;
619 END Order_Fulfilled;
620
621 PROCEDURE Shipped_Lines(
622 document_id in varchar2,
623 display_type in varchar2,
624 document in out NOCOPY varchar2,
625 document_type in out NOCOPY varchar2) IS
626
627 CURSOR c_get_itemkey(c_document_id in varchar2) is
628 SELECT item_key
629 FROM wf_item_activity_statuses
630 WHERE notification_id = to_number(c_document_id);
631
632 l_item_type varchar2(30) := 'WSHNOTIF';
633 l_item_key varchar2(150);
634 l_source_header_id number;
635 l_source_code varchar2(30);
636 l_contact_type varchar2(10);
637 l_contact_id number;
638 l_last_notif_date date;
639 l_shipped boolean := FALSE;
640 l_shipped_lines varchar2(32750) := '';
641
642 --
643 l_debug_on BOOLEAN;
644 --
645 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'SHIPPED_LINES';
646 --
647 BEGIN
648 --
649 -- Debug Statements
650 --
651 --
652 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
653 --
654 IF l_debug_on IS NULL
655 THEN
656 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
657 END IF;
658 --
659 IF l_debug_on THEN
660 WSH_DEBUG_SV.push(l_module_name);
661 --
662 WSH_DEBUG_SV.log(l_module_name,'DOCUMENT_ID',DOCUMENT_ID);
663 WSH_DEBUG_SV.log(l_module_name,'DISPLAY_TYPE',DISPLAY_TYPE);
664 WSH_DEBUG_SV.log(l_module_name,'DOCUMENT',DOCUMENT);
665 WSH_DEBUG_SV.log(l_module_name,'DOCUMENT_TYPE',DOCUMENT_TYPE);
666 END IF;
667 --
668 open c_get_itemkey(document_id);
669 fetch c_get_itemkey
670 into l_item_key;
671 close c_get_itemkey;
672
673 --
674 -- Debug Statements
675 --
676 IF l_debug_on THEN
677 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WF_ENGINE.GETITEMATTRNUMBER',WSH_DEBUG_SV.C_PROC_LEVEL);
678 END IF;
679 --
680 l_source_header_id := wf_engine.GetItemAttrNumber(
681 ItemType => l_item_type,
682 ItemKey => l_item_key,
683 aname => 'SOURCE_HEADER_ID');
684
685 --
686 -- Debug Statements
687 --
688 IF l_debug_on THEN
689 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WF_ENGINE.GETITEMATTRTEXT',WSH_DEBUG_SV.C_PROC_LEVEL);
690 END IF;
691 --
692 l_source_code := wf_engine.GetItemAttrText(
693 ItemType => l_item_type,
694 ItemKey => l_item_key,
695 aname => 'SOURCE_CODE');
696
697 --
698 -- Debug Statements
699 --
700 IF l_debug_on THEN
701 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WF_ENGINE.GETITEMATTRTEXT',WSH_DEBUG_SV.C_PROC_LEVEL);
702 END IF;
703 --
704 l_contact_type := wf_engine.GetItemAttrText(
705 ItemType => l_item_type,
706 ItemKey => l_item_key,
707 aname => 'CONTACT_TYPE');
708
709 --
710 -- Debug Statements
711 --
712 IF l_debug_on THEN
713 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WF_ENGINE.GETITEMATTRNUMBER',WSH_DEBUG_SV.C_PROC_LEVEL);
714 END IF;
715 --
716 l_contact_id := wf_engine.GetItemAttrNumber(
717 ItemType => l_item_type,
718 ItemKey => l_item_key,
719 aname => 'CONTACT_ID');
720
721 --
722 -- Debug Statements
723 --
724 IF l_debug_on THEN
725 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WF_ENGINE.GETITEMATTRDATE',WSH_DEBUG_SV.C_PROC_LEVEL);
726 END IF;
727 --
728 l_last_notif_date := wf_engine.GetItemAttrDate(
729 ItemType => l_item_type,
730 ItemKey => l_item_key,
731 aname => 'LAST_SHIP_NOTIF_DATE');
732
733 /* Put Customizations in WSH_CUSTOM_PUB.Shipped_Lines Procedure */
734 WSH_CUSTOM_PUB.Shipped_Lines(
735 l_source_header_id,
736 l_source_code,
737 l_contact_type,
738 l_contact_id,
739 l_last_notif_date,
740 l_shipped,
741 l_shipped_lines);
742
743 IF l_debug_on THEN
744 WSH_DEBUG_SV.log(l_module_name,'l_shipped',l_shipped);
745 WSH_DEBUG_SV.log(l_module_name,'l_shipped_lines',l_shipped_lines);
746 END IF;
747
748 document_type := 'text/plain';
749 document := l_shipped_lines;
750
751 if (l_shipped = TRUE) THEN
752 --
753 -- Debug Statements
754 --
755 IF l_debug_on THEN
756 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WF_ENGINE.SETITEMATTRDATE',WSH_DEBUG_SV.C_PROC_LEVEL);
757 END IF;
758 --
759 wf_engine.SetItemAttrDate(
760 ItemType => l_item_type,
761 ItemKey => l_item_key,
762 aname => 'LAST_SHIP_NOTIF_DATE',
763 avalue => sysdate);
764 end if;
765
766 --
767 -- Debug Statements
768 --
769 IF l_debug_on THEN
770 WSH_DEBUG_SV.pop(l_module_name);
771 END IF;
772 --
773 return;
774 END Shipped_Lines;
775
776 PROCEDURE Backordered_Lines(
777 document_id in varchar2,
778 display_type in varchar2,
779 document in out NOCOPY varchar2,
780 document_type in out NOCOPY varchar2) IS
781
782 CURSOR c_get_itemkey (c_document_id in number) is
783 SELECT item_key
784 FROM wf_item_activity_statuses
785 WHERE notification_id = to_number(c_document_id);
786
787 l_item_type varchar2(30) := 'WSHNOTIF';
788 l_item_key varchar2(150);
789 l_source_header_id number;
790 l_source_code varchar2(30);
791 l_contact_type varchar2(10);
792 l_contact_id number;
793 l_last_notif_date date;
794
795 l_backordered boolean := FALSE;
796 l_backordered_lines varchar2(32750) := '';
797
798 --
799 l_debug_on BOOLEAN;
800 --
801 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'BACKORDERED_LINES';
802 --
803 BEGIN
804 --
805 -- Debug Statements
806 --
807 --
808 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
809 --
810 IF l_debug_on IS NULL
811 THEN
812 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
813 END IF;
814 --
815 IF l_debug_on THEN
816 WSH_DEBUG_SV.push(l_module_name);
817 --
818 WSH_DEBUG_SV.log(l_module_name,'DOCUMENT_ID',DOCUMENT_ID);
819 WSH_DEBUG_SV.log(l_module_name,'DISPLAY_TYPE',DISPLAY_TYPE);
820 WSH_DEBUG_SV.log(l_module_name,'DOCUMENT',DOCUMENT);
821 WSH_DEBUG_SV.log(l_module_name,'DOCUMENT_TYPE',DOCUMENT_TYPE);
822 END IF;
823 --
824 open c_get_itemkey(document_id);
825 fetch c_get_itemkey
826 into l_item_key;
827 close c_get_itemkey;
828
829 --
830 -- Debug Statements
831 --
832 IF l_debug_on THEN
833 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WF_ENGINE.GETITEMATTRNUMBER',WSH_DEBUG_SV.C_PROC_LEVEL);
834 END IF;
835 --
836 l_source_header_id := wf_engine.GetItemAttrNumber(
837 ItemType => l_item_type,
838 ItemKey => l_item_key,
839 aname => 'SOURCE_HEADER_ID');
840
841 --
842 -- Debug Statements
843 --
844 IF l_debug_on THEN
845 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WF_ENGINE.GETITEMATTRTEXT',WSH_DEBUG_SV.C_PROC_LEVEL);
846 END IF;
847 --
848 l_source_code := wf_engine.GetItemAttrText(
849 ItemType => l_item_type,
850 ItemKey => l_item_key,
851 aname => 'SOURCE_CODE');
852
853 --
854 -- Debug Statements
855 --
856 IF l_debug_on THEN
857 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WF_ENGINE.GETITEMATTRTEXT',WSH_DEBUG_SV.C_PROC_LEVEL);
858 END IF;
859 --
860 l_contact_type := wf_engine.GetItemAttrText(
861 ItemType => l_item_type,
862 ItemKey => l_item_key,
863 aname => 'CONTACT_TYPE');
864
865 --
866 -- Debug Statements
867 --
868 IF l_debug_on THEN
869 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WF_ENGINE.GETITEMATTRNUMBER',WSH_DEBUG_SV.C_PROC_LEVEL);
870 END IF;
871 --
872 l_contact_id := wf_engine.GetItemAttrNumber(
873 ItemType => l_item_type,
874 ItemKey => l_item_key,
875 aname => 'CONTACT_ID');
876
877 --
878 -- Debug Statements
879 --
880 IF l_debug_on THEN
881 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WF_ENGINE.GETITEMATTRDATE',WSH_DEBUG_SV.C_PROC_LEVEL);
882 END IF;
883 --
884 l_last_notif_date := wf_engine.GetItemAttrDate(
885 ItemType => l_item_type,
886 ItemKey => l_item_key,
887 aname => 'LAST_BO_NOTIF_DATE');
888
889
890 /* Put Customizations in WSH_CUSTOM_PUB.Backordered_Lines Procedure */
891 WSH_CUSTOM_PUB.Backordered_Lines(
892 l_source_header_id,
893 l_source_code,
894 l_contact_type,
895 l_contact_id,
896 l_last_notif_date,
897 l_backordered,
898 l_backordered_lines);
899
900 IF l_debug_on THEN
901 WSH_DEBUG_SV.log(l_module_name,'l_backordered',l_backordered);
902 WSH_DEBUG_SV.log(l_module_name,'l_backordered_lines',l_backordered_lines);
903 END IF;
904
905 document_type := 'text/plain';
906 document := l_backordered_lines;
907
908 if (l_backordered = TRUE) then
909 --
910 -- Debug Statements
911 --
912 IF l_debug_on THEN
913 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WF_ENGINE.SETITEMATTRDATE',WSH_DEBUG_SV.C_PROC_LEVEL);
914 END IF;
915 --
916 wf_engine.SetItemAttrDate(
917 ItemType => l_item_type,
918 ItemKey => l_item_key,
919 aname => 'LAST_BO_NOTIF_DATE',
920 avalue => sysdate);
921 end if;
922
923 --
924 -- Debug Statements
925 --
926 IF l_debug_on THEN
927 WSH_DEBUG_SV.pop(l_module_name);
928 END IF;
929 --
930 return;
931 --
932 -- Debug Statements
933 --
934 IF l_debug_on THEN
935 WSH_DEBUG_SV.pop(l_module_name);
936 END IF;
937 --
938 END Backordered_Lines;
939
940 PROCEDURE Update_Workflow(
941 p_delivery_id in number) is
942
943 CURSOR c_get_item_key(p_delivery_id in number) IS
944 SELECT
945 DISTINCT wdd.source_header_id,
946 wdd.source_code,
947 decode(NVL(wdd.ship_to_contact_id,-99),
948 -99,decode(nvl(wdd.sold_to_contact_id,-99),
949 -99, 'CUSTOMER',
950 'SOLD_TO'),
951 'SHIP_TO') contact_type,
952 nvl(wdd.ship_to_contact_id,nvl(wdd.sold_to_contact_id,wdd.customer_id)) contact_id
953 FROM
954 wsh_delivery_details wdd,
955 wsh_delivery_assignments_v wda
956 WHERE wdd.delivery_detail_id = wda.delivery_detail_id
957 AND wda.delivery_id = p_delivery_id
958 AND wda.delivery_id IS NOT NULL
959 AND wdd.source_code = 'OE';
960
961 l_wf_contact_name varchar2(100) := NULL;
962 l_wf_contact_last_name varchar2(240) := NULL;
963 l_result BOOLEAN := FALSE;
964 --
965 l_debug_on BOOLEAN;
966 --
967 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_WORKFLOW';
968 --
969 BEGIN
970 --
971 -- Debug Statements
972 --
973 --
974 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
975 --
976 IF l_debug_on IS NULL
977 THEN
978 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
979 END IF;
980 --
981 IF l_debug_on THEN
982 WSH_DEBUG_SV.push(l_module_name);
983 --
984 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_ID',P_DELIVERY_ID);
985 END IF;
986 --
987 --
988 -- Debug Statements
989 --
990 IF l_debug_on THEN
991 WSH_DEBUG_SV.logmsg(l_module_name, 'IN PROCEDURE WSH_WF.UPDATE_WORKFLOW FOR DELIVERY '||P_DELIVERY_ID );
992 END IF;
993 --
994 for crec in c_get_item_key(p_delivery_id)
995 loop
996 begin
997 --
998 -- Debug Statements
999 --
1000 IF l_debug_on THEN
1001 WSH_DEBUG_SV.logmsg(l_module_name, 'CALLING WORKFLOW WAIT_FOR_SHIP_CONFIRM_EVENT WITH '|| TO_CHAR ( CREC.SOURCE_HEADER_ID ) ||'-'|| CREC.SOURCE_CODE||'-'||CREC.CONTACT_TYPE||'-'||TO_CHAR ( CREC.CONTACT_ID ) );
1002 END IF;
1003 --
1004
1005 l_wf_contact_name := NULL;
1006 l_wf_contact_last_name := NULL;
1007 Get_Wf_User(
1008 crec.contact_type,
1009 crec.contact_id,
1010 l_wf_contact_last_name,
1011 l_wf_contact_name);
1012 IF (l_wf_contact_name is NULL) THEN
1013 --
1014 -- Debug Statements
1015 --
1016 IF l_debug_on THEN
1017 WSH_DEBUG_SV.logmsg(l_module_name, 'UNABLE TO FIND THE CONTACT IN WF_USERS' );
1018 END IF;
1019 --
1020 ELSE
1021 -- See if the workflow instance is exisiting
1022 Check_Item_Instance(
1023 crec.source_header_id,
1024 crec.source_code,
1025 l_wf_contact_name,
1026 l_result);
1027
1028 IF (l_result) THEN
1029 --
1030 -- Debug Statements
1031 --
1032 IF l_debug_on THEN
1033 WSH_DEBUG_SV.logmsg(l_module_name, 'WORKFLOW INSTANCE EXISTS' );
1034 END IF;
1035 --
1036 --
1037 -- Debug Statements
1038 --
1039 IF l_debug_on THEN
1040 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WF_ENGINE.COMPLETEACTIVITY',WSH_DEBUG_SV.C_PROC_LEVEL);
1041 END IF;
1042 --
1043 wf_engine.completeactivity(
1044 'WSHNOTIF',
1045 to_char(crec.source_header_id)||'-'|| crec.source_code||'-'||l_wf_contact_name,
1046 'WAIT_FOR_SHIP_CONFIRM_EVENT',
1047 null);
1048 END IF;
1049
1050 --
1051 -- Debug Statements
1052 --
1053 IF l_debug_on THEN
1054 WSH_DEBUG_SV.logmsg(l_module_name, 'WORKFLOW BLOCK REMOVED SUCCESSFULLY' );
1055 END IF;
1056 --
1057 END IF;
1058 exception
1059 when others then
1060 --
1061 -- Debug Statements
1062 --
1063 IF l_debug_on THEN
1064 WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR WHILE UNBLOCKING THE WORKFLOW BLOCK WAIT_FOR_SHIP_CONFIRM_EVENT' );
1065 END IF;
1066 --
1067 end;
1068 end loop;
1069 --
1070 -- Debug Statements
1071 --
1072 IF l_debug_on THEN
1073 WSH_DEBUG_SV.pop(l_module_name);
1074 END IF;
1075 --
1076 EXCEPTION
1077 WHEN OTHERS THEN
1078 --
1079 -- Debug Statements
1080 --
1081 IF l_debug_on THEN
1082 WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR IN WSH_WF.UPDATE_WORKFLOW' );
1083 END IF;
1084 --
1085 --
1086 -- Debug Statements
1087 --
1088 IF l_debug_on THEN
1089 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1090 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1091 END IF;
1092 --
1093 END Update_Workflow;
1094
1095 PROCEDURE Check_Item_Instance(
1096 p_source_header_id in number,
1097 p_source_code in varchar2,
1098 p_contact_name in varchar2,
1099 p_result out NOCOPY BOOLEAN) IS
1100 l_status varchar2(20);
1101 l_result varchar2(20);
1102 --
1103 l_debug_on BOOLEAN;
1104 --
1105 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CHECK_ITEM_INSTANCE';
1106 --
1107 BEGIN
1108 --
1109 -- Debug Statements
1110 --
1111 --
1112 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1113 --
1114 IF l_debug_on IS NULL
1115 THEN
1116 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1117 END IF;
1118 --
1119 IF l_debug_on THEN
1120 WSH_DEBUG_SV.push(l_module_name);
1121 --
1122 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_HEADER_ID',P_SOURCE_HEADER_ID);
1123 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_CODE',P_SOURCE_CODE);
1124 WSH_DEBUG_SV.log(l_module_name,'P_CONTACT_NAME',P_CONTACT_NAME);
1125 END IF;
1126 --
1127 --
1128 -- Debug Statements
1129 --
1130 IF l_debug_on THEN
1131 WSH_DEBUG_SV.logmsg(l_module_name, 'WSH_WF.CHECK_ITEM_INSTANCE : CALLING WITH ITEM KEY '|| TO_CHAR ( P_SOURCE_HEADER_ID ) ||'-'||P_SOURCE_CODE||'-'||P_CONTACT_NAME );
1132 END IF;
1133 --
1134
1135 --
1136 -- Debug Statements
1137 --
1138 IF l_debug_on THEN
1139 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WF_ENGINE.ITEMSTATUS',WSH_DEBUG_SV.C_PROC_LEVEL);
1140 END IF;
1141 --
1142 Wf_Engine.ItemStatus(
1143 'WSHNOTIF',
1144 to_char(p_source_header_id)||'-'||p_source_code||'-'||p_contact_name,
1145 l_status,
1146 l_result);
1147
1148 IF (l_status = 'ACTIVE') THEN
1149 p_result := TRUE;
1150 --
1151 -- Debug Statements
1152 --
1153 IF l_debug_on THEN
1154 WSH_DEBUG_SV.logmsg(l_module_name, 'WORKFLOW ITEM INSTANCE IS '||L_STATUS );
1155 END IF;
1156 --
1157 ELSE
1158 p_result := FALSE;
1159 --
1160 -- Debug Statements
1161 --
1162 IF l_debug_on THEN
1163 WSH_DEBUG_SV.logmsg(l_module_name, 'WORKFLOW ITEM INSTANCE IS '||L_STATUS );
1164 END IF;
1165 --
1166 END IF;
1167
1168 --
1169 -- Debug Statements
1170 --
1171 IF l_debug_on THEN
1172 WSH_DEBUG_SV.pop(l_module_name);
1173 END IF;
1174 --
1175 return;
1176 --
1177 -- Debug Statements
1178 --
1179 IF l_debug_on THEN
1180 WSH_DEBUG_SV.pop(l_module_name);
1181 END IF;
1182 --
1183 EXCEPTION
1184 WHEN OTHERS THEN
1185 p_result := FALSE;
1186 --
1187 -- Debug Statements
1188 --
1189 IF l_debug_on THEN
1190 WSH_DEBUG_SV.logmsg(l_module_name, 'WORKFLOW ITEM INSTANCE DOESN''T EXIST' );
1191 END IF;
1192 --
1193 --
1194 -- Debug Statements
1195 --
1196 IF l_debug_on THEN
1197 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1198 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1199 END IF;
1200 --
1201 END Check_Item_Instance;
1202
1203 PROCEDURE Start_Workflow(
1204 p_source_header_id in number,
1205 p_source_code in varchar2,
1206 p_order_number in number,
1207 p_contact_type in varchar2,
1208 p_contact_id in number,
1209 p_result out NOCOPY BOOLEAN) IS
1210
1211 l_result BOOLEAN := FALSE;
1212 l_cached_result BOOLEAN := FALSE;
1213 l_wf_contact_name VARCHAR2(100) := NULL;
1214 l_wf_contact_last_name VARCHAR2(240) := NULL;
1215 l_count NUMBER := 0;
1216 l_wf_active BOOLEAN := FALSE;
1217 --
1218 l_debug_on BOOLEAN;
1219 --
1220 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'START_WORKFLOW';
1221 --
1222 BEGIN
1223
1224 --
1225 -- Debug Statements
1226 --
1227 --
1228 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1229 --
1230 IF l_debug_on IS NULL
1231 THEN
1232 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1233 END IF;
1234 --
1235 IF l_debug_on THEN
1236 WSH_DEBUG_SV.push(l_module_name);
1237 --
1238 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_HEADER_ID',P_SOURCE_HEADER_ID);
1239 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_CODE',P_SOURCE_CODE);
1240 WSH_DEBUG_SV.log(l_module_name,'P_ORDER_NUMBER',P_ORDER_NUMBER);
1241 WSH_DEBUG_SV.log(l_module_name,'P_CONTACT_TYPE',P_CONTACT_TYPE);
1242 WSH_DEBUG_SV.log(l_module_name,'P_CONTACT_ID',P_CONTACT_ID);
1243 END IF;
1244 --
1245 l_result := FALSE;
1246 --
1247 -- Debug Statements
1248 --
1249 IF l_debug_on THEN
1250 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CUSTOM_PUB.START_WORKFLOW',WSH_DEBUG_SV.C_PROC_LEVEL);
1251 END IF;
1252 --
1253 WSH_CUSTOM_PUB.Start_Workflow(
1254 p_source_header_id,
1255 p_source_code,
1256 p_contact_type,
1257 p_contact_id,
1258 l_result);
1259
1260 IF (l_result = TRUE) THEN
1261 l_cached_result := FALSE;
1262 FOR i in 1..g_wf_table.COUNT LOOP
1263 IF ((g_wf_table(i).source_header_id = p_source_header_id) AND
1264 (g_wf_table(i).source_code = p_source_code) AND
1265 (g_wf_table(i).contact_type = p_contact_type) AND
1266 (g_wf_table(i).contact_id = p_contact_id)) THEN
1267 IF (g_wf_table(i).wf_started) THEN
1268 --
1269 -- Debug Statements
1270 --
1271 IF l_debug_on THEN
1272 WSH_DEBUG_SV.logmsg(l_module_name, 'WORKFLOW INSTANCE ALREADY EXISTING FOR THE COMBINATION' );
1273 END IF;
1274 --
1275 ELSE
1276 --
1277 -- Debug Statements
1278 --
1279 IF l_debug_on THEN
1280 WSH_DEBUG_SV.logmsg(l_module_name, 'WORKFLOW INSTANCE NOT STARTED DUE TO A PREVIOUS ERROR FOR THE COMBINATION' );
1281 END IF;
1282 --
1283 END IF;
1284 l_cached_result := TRUE;
1285 END IF;
1286 END LOOP;
1287
1288 IF (NOT l_cached_result) THEN
1289 --
1290 -- Debug Statements
1291 --
1292 IF l_debug_on THEN
1293 WSH_DEBUG_SV.logmsg(l_module_name, 'WORKFLOW INSTANCE NOT FOUND IN THE CACHED TABLE' );
1294 END IF;
1295 --
1296 l_count := g_wf_table.COUNT + 1;
1297
1298 g_wf_table(l_count).source_header_id := p_source_header_id;
1299 g_wf_table(l_count).source_code := p_source_code;
1300 g_wf_table(l_count).contact_type := p_contact_type;
1301 g_wf_table(l_count).contact_id := p_contact_id;
1302
1303 Get_Wf_User(
1304 g_wf_table(l_count).contact_type,
1305 g_wf_table(l_count).contact_id,
1306 l_wf_contact_last_name,
1307 l_wf_contact_name);
1308 IF (l_wf_contact_name is NULL) THEN
1309 -- Cannot Start Wf if we cannot get a valid wf_user from contact
1310 g_wf_table(l_count).wf_started := FALSE;
1311 ELSE
1312 -- Check if the workflow instance is already running
1313 -- This can happen if we happened to import the line in one of the previous
1314 -- Import Delivery runs.
1315 Check_Item_Instance(
1316 g_wf_table(l_count).source_header_id,
1317 g_wf_table(l_count).source_code,
1318 l_wf_contact_name,
1319 l_wf_active);
1320
1321 IF (l_wf_active) THEN
1322 g_wf_table(l_count).wf_started := TRUE;
1323 ELSE
1324 -- Need to Start workflow now
1325 Start_Process(
1326 p_source_header_id => p_source_header_id,
1327 p_source_code => p_source_code,
1328 p_order_number => p_order_number,
1329 p_contact_type => p_contact_type,
1330 p_contact_name => l_wf_contact_name,
1331 p_contact_id => p_contact_id,
1332 p_contact_last_name => l_wf_contact_last_name,
1333 p_shipped_lines => NULL,
1334 p_backordered_lines => NULL,
1335 p_ship_notif_date => sysdate,
1336 p_bo_notif_date => sysdate,
1337 p_workflow_process => 'WSHNOTIF',
1338 p_item_type => 'WSHNOTIF');
1339 --
1340 -- Debug Statements
1341 --
1342 IF l_debug_on THEN
1343 WSH_DEBUG_SV.logmsg(l_module_name, 'STARTED WORKFLOW FOR '||P_SOURCE_HEADER_ID||'-'||P_SOURCE_CODE||'-'||L_WF_CONTACT_NAME );
1344 END IF;
1345 --
1346 g_wf_table(l_count).wf_started := TRUE;
1347 END IF; -- l_wf_active
1348 END IF; -- l_wf_contact_name is NULL
1349 p_result := g_wf_table(l_count).wf_started;
1350 ELSE
1351 p_result := FALSE;
1352 END IF; -- l_cached_result
1353 ELSE
1354 p_result := FALSE;
1355 --
1356 -- Debug Statements
1357 --
1358 IF l_debug_on THEN
1359 WSH_DEBUG_SV.logmsg(l_module_name, 'WSH_CUSTOM_PUB.START_WORKFLOW RETURNED FALSE' );
1360 END IF;
1361 --
1362 END IF; -- l_result = TRUE;
1363 --
1364 -- Debug Statements
1365 --
1366 IF l_debug_on THEN
1367 WSH_DEBUG_SV.pop(l_module_name);
1368 END IF;
1369 --
1370 return;
1371 --
1372 EXCEPTION
1373 WHEN OTHERS THEN
1374 p_result := FALSE;
1375 --
1376 -- Debug Statements
1377 --
1378 IF l_debug_on THEN
1379 WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR IN WSH_WF.START_WORKFLOW' );
1380 END IF;
1381 --
1382 --
1383 -- Debug Statements
1384 --
1385 IF l_debug_on THEN
1386 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1387 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1388 END IF;
1389 --
1390 END Start_Workflow;
1391
1392 PROCEDURE Get_Wf_User(
1393 p_contact_type in varchar2,
1394 p_contact_id in number,
1395 p_wf_contact_last_name out NOCOPY varchar2,
1396 p_wf_contact_name out NOCOPY varchar2) IS
1397
1398 CURSOR get_party(c_contact_type in varchar2, c_contact_id in number) IS --TCA view Removal Starts
1399 SELECT
1400 party_id
1401 FROM
1402 hz_cust_accounts
1403 WHERE
1404 cust_account_id/*customer_id*/ = c_contact_id
1405 AND c_contact_type = 'CUSTOMER'
1406 UNION
1407 SELECT
1408 rel.subject_id /*contact_party_id*/
1409 FROM
1410 hz_cust_account_roles Acct_role,
1411 hz_relationships Rel,
1412 hz_org_contacts Org_cont,
1413 hz_cust_accounts Role_acct
1414 WHERE
1415 acct_role.cust_account_role_id/*contact id*/ = c_contact_id
1416 AND acct_role.party_id = rel.party_id
1417 AND acct_role.Role_type = 'CONTACT'
1418 AND org_cont.party_relationship_id = rel.relationship_id
1419 AND rel.party_id is not null
1420 AND rel.subject_table_name = 'HZ_PARTIES'
1421 AND rel.object_table_name = 'HZ_PARTIES'
1422 And acct_role.cust_account_id = role_acct.cust_account_id
1423 AND role_acct.party_id = rel.Object_id
1424 AND c_contact_type in ('SHIP_TO','SOLD_TO'); --TCA view Removal Ends
1425
1426
1427 l_party_id NUMBER := 0;
1428 l_wf_contact_last_name VARCHAR2(240) := NULL;
1429 l_email_address VARCHAR2(2000) := NULL;
1430 l_notification_preference VARCHAR2(4000) := NULL;
1431 l_language VARCHAR2(4000) := NULL;
1432 l_territory VARCHAR2(4000) := NULL;
1433
1434 --
1435 l_debug_on BOOLEAN;
1436 --
1437 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_WF_USER';
1438 --
1439 BEGIN
1440
1441 -- Get the party_id for the contact
1442 --
1443 -- Debug Statements
1444 --
1445 --
1446 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1447 --
1448 IF l_debug_on IS NULL
1449 THEN
1450 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1451 END IF;
1452 --
1453 IF l_debug_on THEN
1454 WSH_DEBUG_SV.push(l_module_name);
1455 --
1456 WSH_DEBUG_SV.log(l_module_name,'P_CONTACT_TYPE',P_CONTACT_TYPE);
1457 WSH_DEBUG_SV.log(l_module_name,'P_CONTACT_ID',P_CONTACT_ID);
1458 END IF;
1459 --
1460 OPEN get_party(p_contact_type,p_contact_id);
1461 FETCH get_party
1462 INTO l_party_id;
1463 CLOSE get_party;
1464
1465 IF (NVL(l_party_id,0) = 0) THEN
1466 p_wf_contact_name := NULL;
1467 ELSE
1468 -- parties will be with the following format in wf_users view
1469 -- Made HZ_PARTY change as per 1391687
1470 p_wf_contact_name := 'HZ_PARTY:'||to_char(l_party_id);
1471
1472 -- Check if the party is in wf_users view
1473 --
1474 -- Debug Statements
1475 --
1476 IF l_debug_on THEN
1477 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WF_DIRECTORY.GETROLEINFO',WSH_DEBUG_SV.C_PROC_LEVEL);
1478 END IF;
1479 --
1480 wf_directory.getroleinfo(
1481 p_wf_contact_name,
1482 l_wf_contact_last_name,
1483 l_email_address,
1484 l_notification_preference,
1485 l_language,
1486 l_territory);
1487
1488 IF l_debug_on THEN
1489 WSH_DEBUG_SV.log(l_module_name,'l_wf_contact_last_name',l_wf_contact_last_name);
1490 WSH_DEBUG_SV.log(l_module_name,'l_email_address',l_email_address);
1491 WSH_DEBUG_SV.log(l_module_name,'l_notification_preference',l_notification_preference);
1492 WSH_DEBUG_SV.log(l_module_name,'l_language',l_language);
1493 WSH_DEBUG_SV.log(l_module_name,'l_territory',l_territory);
1494 END IF;
1495
1496 IF (l_wf_contact_last_name IS NULL) THEN
1497 -- If party is not in wf_users views the we cannot send notification
1498 -- So return null which will not trigger workflow
1499 p_wf_contact_name := NULL;
1500 p_wf_contact_last_name := NULL;
1501 ELSE
1502 p_wf_contact_last_name := l_wf_contact_last_name;
1503 END IF;
1504
1505 END IF;
1506
1507 --
1508 -- Debug Statements
1509 --
1510 IF l_debug_on THEN
1511 WSH_DEBUG_SV.pop(l_module_name);
1512 END IF;
1513 --
1514 return;
1515 EXCEPTION
1516 WHEN OTHERS THEN
1517 p_wf_contact_name := NULL;
1518 --
1519 -- Debug Statements
1520 --
1521 IF l_debug_on THEN
1522 WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR IN WSH_WF.GET_WF_USER' );
1523 END IF;
1524 --
1525 --
1526 -- Debug Statements
1527 --
1528 IF l_debug_on THEN
1529 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1530 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1531 END IF;
1532 --
1533 END Get_Wf_User;
1534
1535 END WSH_WF;