1 PACKAGE BODY WSH_WF_STD AS
2 /* $Header: WSHWSTDB.pls 120.12.12010000.4 2010/03/11 07:33:06 sankarun ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):='WSH_WF_STD';
5 G_NO_WORKFLOW CONSTANT VARCHAR2(30):='NO_WORLFLOW_98';
6
7 /*PROCEDURE Test_Events(p_entity_type IN VARCHAR2,
8 p_entity_id IN NUMBER,
9 p_send_date IN DATE,
10 p_event IN VARCHAR2) IS
11
12 l_return_status VARCHAR2(1);
13 l_exception_msg_count NUMBER;
14 l_exception_msg_data varchar2(2000);
15 l_exception_id NUMBER := NULL;
16 l_msg varchar2(2000);
17 l_ship_from_location_id NUMBER := 207;--For testing purposes, change if not valid.
18
19 BEGIN
20
21 l_msg := 'Event '||p_event||' raised on date '||p_send_date||' for entity '||p_entity_type||' with ID '||p_entity_id;
22
23 IF p_entity_type = 'DELIVERY' THEN
24
25 wsh_xc_util.log_exception(
26 p_api_version => 1.0,
27 x_return_status => l_return_status,
28 x_msg_count => l_exception_msg_count,
29 x_msg_data => l_exception_msg_data,
30 x_exception_id => l_exception_id ,
31 p_logged_at_location_id => l_ship_from_location_id,
32 p_exception_location_id => l_ship_from_location_id,
33 p_logging_entity => 'SHIPPER',
34 p_logging_entity_id => FND_GLOBAL.USER_ID,
35 p_exception_name => 'WSH_WF_BES',
36 p_message => l_msg,
37 p_delivery_id => p_entity_id
38 );
39
40 ELSIF p_entity_type = 'TRIP' THEN
41
42 wsh_xc_util.log_exception(
43 p_api_version => 1.0,
44 x_return_status => l_return_status,
45 x_msg_count => l_exception_msg_count,
46 x_msg_data => l_exception_msg_data,
47 x_exception_id => l_exception_id ,
48 p_logged_at_location_id => l_ship_from_location_id,
49 p_exception_location_id => l_ship_from_location_id,
50 p_logging_entity => 'SHIPPER',
51 p_logging_entity_id => FND_GLOBAL.USER_ID,
52 p_exception_name => 'WSH_WF_BES',
53 p_message => l_msg,
54 p_trip_id => p_entity_id
55 );
56
57
58 ELSIF p_entity_type = 'STOP' THEN
59
60 wsh_xc_util.log_exception(
61 p_api_version => 1.0,
62 x_return_status => l_return_status,
63 x_msg_count => l_exception_msg_count,
64 x_msg_data => l_exception_msg_data,
65 x_exception_id => l_exception_id ,
66 p_logged_at_location_id => l_ship_from_location_id,
67 p_exception_location_id => l_ship_from_location_id,
68 p_logging_entity => 'SHIPPER',
69 p_logging_entity_id => FND_GLOBAL.USER_ID,
70 p_exception_name => 'WSH_WF_BES',
71 p_message => l_msg,
72 p_trip_stop_id => p_entity_id
73 );
74
75
76 ELSIF p_entity_type = 'LINE' THEN
77
78 wsh_xc_util.log_exception(
79 p_api_version => 1.0,
80 x_return_status => l_return_status,
81 x_msg_count => l_exception_msg_count,
82 x_msg_data => l_exception_msg_data,
83 x_exception_id => l_exception_id ,
84 p_logged_at_location_id => l_ship_from_location_id,
85 p_exception_location_id => l_ship_from_location_id,
86 p_logging_entity => 'SHIPPER',
87 p_logging_entity_id => FND_GLOBAL.USER_ID,
88 p_exception_name => 'WSH_WF_BES',
89 p_message => l_msg,
90 p_delivery_detail_id => p_entity_id
91 );
92
93 END IF;
94
95 END Test_Events;
96 */
97 ---------------------------------------------------------------------------------------
98 --
99 -- Procedure: Start_Wf_Process
100 -- Parameters: p_entity_type - 'TRIP','DELIVERY'
101 -- p_entity_id - TRIP_ID or DELIVERY_ID
102 -- p_organization_id - The Organization Id
103 --
104 -- x_process_started - 'Y' Process started;
105 -- 'E' Process already exists
106 -- 'N' Process not started
107 -- x_return_status - Returns WSH_UTIL_CORE.G_RET_STS_SUCCESS if Success
108
109 -- Description: This Procedure selects and starts a Tracking Workflow process
110 -- for an entity - Trip/Delivery after checking if it is eligible.
111 -- i.e.1) No Process exists already for the Entity
112 -- 2) Global and Shipping parameters for the entity admits
113 -- Finally updates the WSH_NEW_DELIVERIES or WSH_TRIPS with the
114 -- Process name that was launched.
115 --
116 ---------------------------------------------------------------------------------------
117
118 PROCEDURE Start_Wf_Process(
119 p_entity_type IN VARCHAR2,
120 p_entity_id IN NUMBER,
121 p_organization_id IN NUMBER DEFAULT NULL,
122 x_process_started OUT NOCOPY VARCHAR2,
123 x_return_status OUT NOCOPY VARCHAR2) IS
124
125
126 l_itemtype VARCHAR2(30);
127 l_itemkey VARCHAR2(30);
128 l_process_name VARCHAR2(30);
129
130 CURSOR get_customer_name(p_delivery_id IN NUMBER) IS
131 SELECT substrb(party.party_name,1,50)
132 FROM
133 hz_parties party,
134 hz_cust_accounts cust_acct,
135 wsh_new_deliveries wnd
136 WHERE
137 cust_acct.cust_account_id = wnd.customer_id and
138 cust_acct.party_id = party.party_id and
139 wnd.delivery_id = p_delivery_id;
140
141 l_wf_process_exists VARCHAR2(1);
142 l_start_wf_process VARCHAR2(1);
143 l_return_status VARCHAR2(1);
144
145 l_customer_name VARCHAR2(50);
146 l_aname_num wf_engine.nametabtyp;
147 l_avalue_num wf_engine.numtabtyp;
148 l_aname_text wf_engine.nametabtyp;
149 l_avalue_text wf_engine.texttabtyp;
150
151 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'START_WF_PROCESS';
152 l_debug_on BOOLEAN;
153
154 BEGIN
155
156 -- Debug Statements
157 --
158 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
159 --
160 IF l_debug_on IS NULL
161 THEN
162 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
163 END IF;
164 --
165 IF l_debug_on THEN
166 WSH_DEBUG_SV.push(l_module_name);
167 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_TYPE',p_entity_type);
168 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_ID',p_entity_id);
169 WSH_DEBUG_SV.log(l_module_name,'P_ORGANIZATION_ID',p_organization_id);
170 END IF;
171
172 x_process_started := 'N';
173 x_return_status:=WSH_UTIL_CORE.G_RET_STS_SUCCESS;
174 SAVEPOINT START_WF_PROCESS_UPDATE;
175
176 IF l_debug_on THEN
177 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WF_STD.CHECK_WF_EXISTS',
178 WSH_DEBUG_SV.C_PROC_LEVEL);
179 END IF;
180
181 Check_Wf_Exists(p_entity_type => p_entity_type,
182 p_entity_id => p_entity_id,
183 x_wf_process_exists => l_wf_process_exists,
184 x_return_status => l_return_status);
185
186 IF l_debug_on THEN
187 WSH_DEBUG_SV.log(l_module_name,'l_wf_process_exists',l_wf_process_exists);
188 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
189 END IF;
190
191 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
192 x_return_status := l_return_status;
193 IF l_debug_on THEN
194 WSH_DEBUG_SV.pop(l_module_name);
195 END IF;
196 RETURN;
197 END IF;
198 IF (l_wf_process_exists = 'Y') THEN
199 x_process_started := 'E';
200 IF l_debug_on THEN
201 WSH_DEBUG_SV.pop(l_module_name);
202 END IF;
203 RETURN;
204 END IF;
205
206 IF l_debug_on THEN
207 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WF_STD.CONFIRM_START_WF_PROCESS',
208 WSH_DEBUG_SV.C_PROC_LEVEL);
209 END IF;
210
211 Confirm_Start_Wf_Process(p_entity_type => p_entity_type,
212 p_organization_id => p_organization_id,
213 x_start_wf_process => l_start_wf_process,
214 x_return_status => l_return_status);
215
216 IF l_debug_on THEN
217 WSH_DEBUG_SV.log(l_module_name,'l_start_wf_process',l_start_wf_process);
218 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
219 END IF;
220
221 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
222 x_return_status := l_return_status;
223 IF l_debug_on THEN
224 WSH_DEBUG_SV.pop(l_module_name);
225 END IF;
226 RETURN;
227 END IF;
228 IF (l_start_wf_process = 'N') THEN
229 IF l_debug_on THEN
230 WSH_DEBUG_SV.pop(l_module_name);
231 END IF;
232 RETURN;
233 end if;
234
235 -- invoke the workflow
236 l_itemkey:=p_entity_id;
237
238 IF l_debug_on THEN
239 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WF_STD.PROCESS_SELECTOR',
240 WSH_DEBUG_SV.C_PROC_LEVEL);
241 END IF;
242
243 Process_Selector(p_entity_type => p_entity_type,
244 p_entity_id => p_entity_id,
245 p_organization_id => p_organization_id,
246 x_wf_process => l_process_name,
247 x_return_status => l_return_status);
248
249 IF l_debug_on THEN
250 WSH_DEBUG_SV.log(l_module_name,'l_process_name',l_process_name);
251 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
252 END IF;
253
254 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
255 x_return_status := l_return_status;
256 IF l_debug_on THEN
257 WSH_DEBUG_SV.pop(l_module_name);
258 END IF;
259 RETURN;
260 END IF;
261
262 IF (l_process_name = G_NO_WORKFLOW) THEN
263 IF l_debug_on THEN
264 WSH_DEBUG_SV.pop(l_module_name);
265 END IF;
266 RETURN;
267 END IF;
268
269 IF (p_entity_type = 'DELIVERY') THEN
270 l_itemtype:='WSHDEL';
271 ELSE
272 l_itemtype:='WSHTRIP';
273 END IF;
274
275 WF_ENGINE.Createprocess(itemtype => l_itemtype,
276 itemkey => l_itemkey,
277 process => l_process_name);
278
279 l_aname_num(1) := 'USER_ID';
280 l_avalue_num(1) := FND_GLOBAL.USER_ID;
281 l_aname_num(2) := 'APPLICATION_ID';
282 l_avalue_num(2) := FND_GLOBAL.RESP_APPL_ID;
283 l_aname_num(3) := 'RESPONSIBILITY_ID';
284 l_avalue_num(3) := FND_GLOBAL.RESP_ID;
285
286 WF_ENGINE.SetItemAttrNumberArray(
287 itemtype => l_itemtype,
288 itemkey => l_itemkey,
289 aname => l_aname_num,
290 avalue => l_avalue_num);
291
292 IF (p_entity_type = 'DELIVERY') THEN
293
294 OPEN get_customer_name(p_entity_id);
295 FETCH get_customer_name INTO l_customer_name;
296 IF get_customer_name%NOTFOUND THEN
297 l_customer_name := null; -- do not set CUSTOMER_NAME then
298 END IF;
299 CLOSE get_customer_name;
300
301 l_aname_text(1) := 'DELIVERY_ID';
302 l_avalue_text(1) := to_char(p_entity_id);
303 l_aname_text(2) := 'ORG_ID';
304 l_avalue_text(2) := to_char(p_organization_id);
305 l_aname_text(3) := 'DELIVERY_NAME';
306 l_avalue_text(3) := to_char(p_entity_id);
307 l_aname_text(4) := 'NOTIFICATION_FROM_ROLE';
308 l_avalue_text(4) := 'WFADMIN';
309 l_aname_text(5) := 'NOTIFICATION_TO_ROLE';
310 l_avalue_text(5) := FND_GLOBAL.USER_NAME;
311 l_aname_text(6) := 'CUSTOMER_NAME';
312 l_avalue_text(6) := l_customer_name;
313
314 WF_ENGINE.SetItemAttrTextArray(
315 itemtype => l_itemtype,
316 itemkey => l_itemkey,
317 aname => l_aname_text,
318 avalue => l_avalue_text);
319
320 ELSIF(p_entity_type = 'TRIP') THEN
321
322 WF_ENGINE.SetItemAttrText(
323 itemtype => l_itemtype,
324 itemkey => l_itemkey,
325 aname => 'TRIP_ID',
326 avalue => to_char(p_entity_id));
327 END IF;
328
329 WF_ENGINE.Startprocess(itemtype => l_itemtype,
330 itemkey => l_itemkey);
331
332 IF (p_entity_type = 'DELIVERY') THEN
333 UPDATE WSH_NEW_DELIVERIES
334 SET DELIVERY_WF_PROCESS=l_process_name
335 WHERE delivery_id=p_entity_id;
336 ELSE
337 UPDATE WSH_TRIPS
338 SET TRIP_WF_PROCESS=l_process_name
339 WHERE trip_id=p_entity_id;
340 END IF;
341 x_process_started:= 'Y';
342 IF l_debug_on THEN
343 WSH_DEBUG_SV.pop(l_module_name);
344 END IF;
345
346
347
348 EXCEPTION
349 WHEN no_data_found THEN
350 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR ;
351 IF l_debug_on THEN
352 WSH_DEBUG_SV.logmsg(l_module_name,'No record found for the entity.Oracle error message is '||
353 SQLERRM,WSH_DEBUG_SV.C_EXCEP_LEVEL);
354 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:NO_DATA_FOUND');
355 END IF;
356 WHEN others THEN
357 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
358 IF l_debug_on THEN
359 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
360 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
361 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
362 END IF;
363 END Start_Wf_Process;
364
365 ---------------------------------------------------------------------------------------
366 --
367 -- Procedure: Start_Scpod_C_Process
368 -- Parameters: p_entity_id - DELIVERY_ID (Entity is always Delivery)
369 -- p_organization_id - The Organization Id
370 --
371 -- x_process_started - 'Y' Process started;
372 -- 'E' Process already exists
373 -- 'N' Process not started
374 -- x_return_status - Returns WSH_UTIL_CORE.G_RET_STS_SUCCESS if Success
375 --
376 -- Description: This Procedure starts the 'Ship to Deliver' controlling
377 -- Workflow process for the Delivery after checking
378 -- if it is eligible.
379 -- i.e.1) No Process exists already for the Delivery
380 -- 2) Global and Shipping parameters for the entity admits
381 --
382 --
383 ---------------------------------------------------------------------------------------
384
385 PROCEDURE Start_Scpod_C_Process(
386 p_entity_id IN NUMBER,
387 p_organization_id IN NUMBER,
388 x_process_started OUT NOCOPY VARCHAR2,
389 x_return_status OUT NOCOPY VARCHAR2) IS
390
391
392 l_param_info WSH_SHIPPING_PARAMS_PVT.Parameter_Rec_Typ;
393 l_global_parameters WSH_SHIPPING_PARAMS_PVT.Global_Parameters_Rec_Typ;
394
395 l_itemtype VARCHAR2(30);
396 l_itemkey VARCHAR2(30);
397
398 CURSOR get_org_code IS
399 SELECT organization_code
400 FROM MTL_PARAMETERS
401 WHERE organization_id = p_organization_id;
402
403 CURSOR get_customer_name(p_delivery_id IN NUMBER) IS
404 SELECT substrb(party.party_name,1,50)
405 FROM
406 hz_parties party,
407 hz_cust_accounts cust_acct,
408 wsh_new_deliveries wnd
409 WHERE
410 cust_acct.cust_account_id = wnd.customer_id and
411 cust_acct.party_id = party.party_id and
412 wnd.delivery_id = p_delivery_id;
413
414 l_customer_name VARCHAR2(50);
415 l_organization_code VARCHAR2(3);
416 l_custom_process_name VARCHAR2(30);
417
418 l_scpod_wf_process_exists VARCHAR2(1);
419 l_return_status VARCHAR2(1);
420
421 l_aname_num wf_engine.nametabtyp;
422 l_avalue_num wf_engine.numtabtyp;
423 l_aname_text wf_engine.nametabtyp;
424 l_avalue_text wf_engine.texttabtyp;
425
426 l_override_wf VARCHAR2(1);
427 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'START_SCPOD_C_PROCESS';
428 l_debug_on BOOLEAN;
429
430
431 BEGIN
432
433 -- Debug Statements
434 --
435 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
436 --
437 IF l_debug_on IS NULL
438 THEN
439 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
440 END IF;
441 --
442 IF l_debug_on THEN
443 WSH_DEBUG_SV.push(l_module_name);
444 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_ID',p_entity_id);
445 WSH_DEBUG_SV.log(l_module_name,'P_ORGANIZATION_ID',p_organization_id);
446 END IF;
447 SAVEPOINT START_SCPOD_C_PROCESS_UPDATE;
448
449 l_itemtype := 'WSHDEL';
450 l_itemkey := p_entity_id;
451 x_process_started := 'N';
452 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
453
454 l_override_wf:= fnd_profile.value('WSH_OVERRIDE_SCPOD_WF');
455 IF l_debug_on THEN
456 WSH_DEBUG_SV.log(l_module_name,'L_OVERRIDE_WF',l_override_wf);
457 END IF;
458 IF (nvl(l_override_wf,'N') = 'Y') THEN
459 IF l_debug_on THEN
460 WSH_DEBUG_SV.pop(l_module_name);
461 END IF;
462 RETURN;
463 END IF;
464
465 IF l_debug_on THEN
466 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WF_STD.CHECK_WF_EXISTS',
467 WSH_DEBUG_SV.C_PROC_LEVEL);
468 END IF;
469
470 check_wf_exists(p_entity_type => 'DELIVERY_C',
471 p_entity_id => p_entity_id,
472 x_wf_process_exists => l_scpod_wf_process_exists,
473 x_return_status => l_return_status);
474
475 IF l_debug_on THEN
476 WSH_DEBUG_SV.log(l_module_name,'L_SCPOD_WF_PROCESS_EXISTS',l_scpod_wf_process_exists);
477 WSH_DEBUG_SV.log(l_module_name,'L_RETURN_STATUS',l_return_status);
478 END IF;
479
480 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
481 x_return_status := l_return_status;
482 IF l_debug_on THEN
483 WSH_DEBUG_SV.pop(l_module_name);
484 END IF;
485 RETURN;
486 END IF;
487
488 IF (l_scpod_wf_process_exists = 'Y') THEN
489 x_process_started := 'E';
490 IF l_debug_on THEN
491 WSH_DEBUG_SV.pop(l_module_name);
492 END IF;
493 RETURN;
494 ELSE
495
496 IF l_debug_on THEN
497 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_SHIPPING_PARAMS_PVT.GET',
498 WSH_DEBUG_SV.C_PROC_LEVEL);
499 END IF;
500
501 WSH_SHIPPING_PARAMS_PVT.Get(
502 p_organization_id => p_organization_id,
503 x_param_info => l_param_info,
504 x_return_status => l_return_status);
505 IF l_debug_on THEN
506 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
507 END IF;
508
509 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
510 x_return_status := l_return_status;
511 IF l_debug_on THEN
512 WSH_DEBUG_SV.pop(l_module_name);
513 END IF;
514 RETURN;
515 END IF;
516
517 IF l_debug_on THEN
518 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_SHIPPING_PARAMS_PVT.GET_GLOBAL_PARAMETERS',
519 WSH_DEBUG_SV.C_PROC_LEVEL);
520 END IF;
521
522 WSH_SHIPPING_PARAMS_PVT.Get_Global_Parameters(x_Param_Info => l_global_parameters,
523 x_return_status => l_return_status);
524
525 IF l_debug_on THEN
526 WSH_DEBUG_SV.log(l_module_name,'L_RETURN_STATUS',l_return_status);
527 END IF;
528
529 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
530 x_return_status := l_return_status;
531 IF l_debug_on THEN
532 WSH_DEBUG_SV.pop(l_module_name);
533 END IF;
534 RETURN;
535 END IF;
536
537 IF (NVL(l_global_parameters.ENABLE_SC_WF,'N') = 'Y' and NVL(l_param_info.ENABLE_SC_WF,'N') = 'Y') THEN
538 -- Get custom process if any
539 OPEN get_org_code;
540 FETCH get_org_code into l_organization_code;
541 CLOSE get_org_code ;
542
543 IF l_debug_on THEN
544 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WF_STD.GET_CUSTOM_WF_PROCESS',
545 WSH_DEBUG_SV.C_PROC_LEVEL);
546 END IF;
547
548 Get_Custom_Wf_Process(p_wf_process => 'R_SCPOD_C',
549 p_org_code => l_organization_code,
550 x_wf_process => l_custom_process_name,
551 x_return_status => l_return_status);
552 IF l_debug_on THEN
553 WSH_DEBUG_SV.log(l_module_name,'l_custom_process_name',l_custom_process_name);
554 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
555 END IF;
556
557 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
558 x_return_status := l_return_status;
559 IF l_debug_on THEN
560 WSH_DEBUG_SV.pop(l_module_name);
561 END IF;
562 RETURN;
563 END IF;
564
565 WF_ENGINE.CreateProcess(itemtype => 'WSHDEL',
566 itemkey => p_entity_id,
567 process => l_custom_process_name);
568
569 l_aname_num(1) := 'USER_ID';
570 l_avalue_num(1) := FND_GLOBAL.USER_ID;
571 l_aname_num(2) := 'APPLICATION_ID';
572 l_avalue_num(2) := FND_GLOBAL.RESP_APPL_ID;
573 l_aname_num(3) := 'RESPONSIBILITY_ID';
574 l_avalue_num(3) := FND_GLOBAL.RESP_ID;
575
576 WF_ENGINE.SetItemAttrNumberArray(
577 itemtype => l_itemtype,
578 itemkey => l_itemkey,
579 aname => l_aname_num,
580 avalue => l_avalue_num);
581
582 OPEN get_customer_name(p_entity_id);
583 FETCH get_customer_name INTO l_customer_name;
584 IF get_customer_name%NOTFOUND THEN
585 l_customer_name := null; -- do not set CUSTOMER_NAME then
586 END IF;
587 CLOSE get_customer_name;
588
589 l_aname_text(1) := 'DELIVERY_ID';
590 l_avalue_text(1) := to_char(p_entity_id);
591 l_aname_text(2) := 'ORG_ID';
592 l_avalue_text(2) := to_char(p_organization_id);
593 l_aname_text(3) := 'DELIVERY_NAME';
594 l_avalue_text(3) := to_char(p_entity_id);
595 l_aname_text(4) := 'NOTIFICATION_FROM_ROLE';
596 l_avalue_text(4) := 'WFADMIN';
597 l_aname_text(5) := 'NOTIFICATION_TO_ROLE';
598 l_avalue_text(5) := FND_GLOBAL.USER_NAME;
599 l_aname_text(6) := 'CUSTOMER_NAME';
600 l_avalue_text(6) := l_customer_name;
601
602 WF_ENGINE.SetItemAttrTextArray(
603 itemtype => l_itemtype,
604 itemkey => l_itemkey,
605 aname => l_aname_text,
606 avalue => l_avalue_text);
607 WF_ENGINE.StartProcess(itemtype => 'WSHDEL',
608 itemkey => p_entity_id);
609 -- Add attributes
610 UPDATE WSH_NEW_DELIVERIES
611 SET delivery_scpod_wf_process=l_custom_process_name
612 WHERE delivery_id=p_entity_id;
613 x_process_started := 'Y';
614 END IF;
615 END IF;
616
617 IF l_debug_on THEN
618 WSH_DEBUG_SV.pop(l_module_name);
619 END IF;
620
621 EXCEPTION
622 WHEN others THEN
623 WSH_WF_STD.Log_Wf_Exception(p_entity_type => 'DELIVERY',
624 p_entity_id => p_entity_id,
625 p_logging_entity => 'SHIPPER',
626 p_exception_name => 'WSH_LAUNCH_WF_FAILED',
627 x_return_status => l_return_status);
628
629 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
630 IF l_debug_on THEN
631 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
632 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
633 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
634 END IF;
635
636 END Start_Scpod_C_Process;
637 ---------------------------------------------------------------------------------------
638 --
639 -- Procedure: process_selector
640 -- Parameters: p_entity_type - 'TRIP','DELIVERY'
641 -- p_entity_id - TRIP_ID or DELIVERY_ID
642 -- p_organization_id - The Organization Id
643 --
644 -- x_wf_process - Returns the process selected for the Entity
645 -- x_return_status - Returns WSH_UTIL_CORE.G_RET_STS_SUCCESS if Success
646 --
647 -- Description: This Procedure selects the process for the entities based on the
648 -- various criteria
649 -- Delivery:
650 -- 1) FTE Installed
651 -- Criteria currently not used: 1) Picking Required
652 -- 2) Export Screening Required
653 -- Return G_NO_WORKFLOW in the following cases:
654 -- 1) TPW enabled organization
655 -- 2) Shipment direction (Inbound/Outbound)
656 -- Trip:
657 -- 1) FTE Installed
658 ---------------------------------------------------------------------------------------
659
660 PROCEDURE process_selector(
661 p_entity_type IN VARCHAR2,
662 p_entity_id IN NUMBER,
663 p_organization_id IN NUMBER,
664 x_wf_process OUT NOCOPY VARCHAR2,
665 x_return_status OUT NOCOPY VARCHAR2) IS
666
667 CURSOR get_ship_dir_control IS
668 SELECT shipment_direction,shipping_control
669 FROM WSH_NEW_DELIVERIES WHERE delivery_id=p_entity_id;
670
671 l_param_info WSH_SHIPPING_PARAMS_PVT.Parameter_Rec_Typ;
672
673 CURSOR get_org_code IS
674 SELECT organization_code
675 FROM MTL_PARAMETERS
676 WHERE organization_id = p_organization_id;
677
678 CURSOR get_non_pickable_count IS
679 SELECT count(*)
680 FROM wsh_delivery_assignments_v wda,
681 wsh_delivery_details wdd
682 WHERE
683 wda.delivery_id = p_entity_id
684 AND wda.delivery_detail_id = wdd.delivery_detail_id
685 AND wdd.container_flag = 'N'
686 AND wdd.pickable_flag <> 'Y' ;
687
688 l_organization_code VARCHAR2(3);
689
690 -- l_screening_flag VARCHAR2(1);
691 l_wh_type VARCHAR2(30);
692 l_process_identifier NUMBER;
693 -- l_non_pick_eligible_count NUMBER;
694 l_shipment_direction VARCHAR2(30);
695 l_shipping_control VARCHAR2(30);
696 l_custom_process_name VARCHAR2(30);
697
698 l_fte_installed VARCHAR2(1);
699 -- l_screening_req VARCHAR2(1);
700 -- l_tpw_org VARCHAR2(1);
701 -- l_picking_req VARCHAR2(1);
702
703
704 l_return_status VARCHAR2(30);
705
706 e_invalid_org EXCEPTION;
707 e_process_not_found EXCEPTION;
708
709 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PROCESS_SELECTOR';
710 l_debug_on BOOLEAN;
711
712
713 BEGIN
714
715 -- Debug Statements
716 --
717 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
718 --
719 IF l_debug_on IS NULL
720 THEN
721 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
722 END IF;
723 --
724 IF l_debug_on THEN
725 WSH_DEBUG_SV.push(l_module_name);
726 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_TYPE',p_entity_type);
727 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_ID',p_entity_id);
728 WSH_DEBUG_SV.log(l_module_name,'P_ORGANIZATION_ID',p_organization_id);
729 END IF;
730
731 l_fte_installed := 'N';
732 -- l_screening_req := 'N';
733 -- l_tpw_org := 'N';
734 -- l_picking_req := 'N';
735
736 x_return_status:=WSH_UTIL_CORE.G_RET_STS_SUCCESS;
737 -- CHECK IF FTE INSTALLED
738 IF (WSH_UTIL_CORE.FTE_IS_INSTALLED = 'Y') THEN
739 l_fte_installed := 'Y';
740 END IF;
741
742 IF (p_entity_type = 'TRIP') THEN
743 x_wf_process := 'R_TRIP_GEN';
744 --Currently not in use
745 /*IF ( l_fte_installed = 'Y') THEN
746 x_wf_process := 'R_TRIP_FTE_GEN';
747 ELSE
748 x_wf_process := 'R_TRIP_GEN';
749 END IF;*/
750 ELSIF (p_entity_type = 'DELIVERY') THEN
751
752 -- SHIPMENT DIRECTION AND CONTROL.
753 OPEN get_ship_dir_control;
754 FETCH get_ship_dir_control into l_shipment_direction,l_shipping_control;
755 CLOSE get_ship_dir_control ;
756 IF (l_shipment_direction = 'I') THEN
757 /*SR IF (l_shipping_control = 'SUPPLIER') THEN
758 x_wf_process := 'R_DEL_FTE_SUPPLIER';
759 ELSE
760 x_wf_process := 'R_DEL_FTE_BUYER';
761 END IF; SR*/
762 x_wf_process := G_NO_WORKFLOW;
763 IF l_debug_on THEN
764 WSH_DEBUG_SV.pop(l_module_name);
765 END IF;
766 RETURN;
767 END IF;
768
769 -- CHECK IF EXPORT SCREENING REQUIRED
770 /*SR
771 IF l_debug_on THEN
772 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_SHIPPING_PARAMS_PVT.GET', WSH_DEBUG_SV.C_PROC_LEVEL);
773 END IF;
774
775 WSH_SHIPPING_PARAMS_PVT.Get(
776 p_organization_id => p_organization_id,
777 x_param_info => l_param_info,
778 x_return_status => l_return_status);
779 IF l_debug_on THEN
780 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
781 END IF;
782
783 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
784 x_return_status := l_return_status;
785 IF l_debug_on THEN
786 WSH_DEBUG_SV.pop(l_module_name);
787 END IF;
788 RETURN; --Double check it
789 ELSE
790 l_screening_flag := NVL(l_param_info.EXPORT_SCREENING_FLAG,'N');
791 END IF;
792
793 IF (l_screening_flag <> 'N') THEN -- assuming only N,A,C,S
794 l_screening_req := 'Y';
795 END IF;
796 SR*/
797
798
799 -- CHECK IF ORGANIZATION IS TPW ENABLED
800 IF l_debug_on THEN
801 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_EXTERNAL_INTERFACE_SV.GET_WAREHOUSE_TYPE',
802 WSH_DEBUG_SV.C_PROC_LEVEL);
803 END IF;
804
805 l_wh_type:=WSH_EXTERNAL_INTERFACE_SV.Get_Warehouse_Type(p_organization_id => p_organization_id,
806 p_delivery_id => p_entity_id,
807 x_return_status => l_return_status);
808 IF l_debug_on THEN
809 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
810 END IF;
811
812 IF ( l_return_status <> WSH_UTIL_CORE.g_ret_sts_success) THEN
813 raise e_invalid_org; -- the api returns error when no org id is sent; unexp error
814 END IF;
815 -- TPW - Distributed Organization Changes
816 -- Arcsing the file in R12.0 branchline even though this project is done in 12.1 branch
817 -- due to dual-maintenance. Changes will NOT have any significane in R12.0 branchline
818 -- until the 'TPW - Distributed Organization Changes' are backported to R120.
819 -- Workflow should be disalbed for TW2 enabled Organizaiton
820 --IF ( nvl(l_wh_type,'@') = 'TPW') THEN
821 IF ( nvl(l_wh_type,'@') IN ('TPW', 'TW2')) THEN
822 -- l_tpw_org := 'Y';
823 x_wf_process := G_NO_WORKFLOW;
824 IF l_debug_on THEN
825 WSH_DEBUG_SV.pop(l_module_name);
826 END IF;
827 RETURN;
828 END IF;
829
830
831 -- CHECK IF PICKING REQUIRED. Check for these conditions:
832 -- 1. OKE lines - pickable flag is always 'N' (source_code checking not required)
833 -- 2. TPW lines - pickable flag is sometimes 'Y' hence the check for TPW
834 -- 3. Non transactable lines - pickable flag is not 'Y'
835 /*SR
836 OPEN get_non_pickable_count;
837 FETCH get_non_pickable_count into l_non_pick_eligible_count;
838 CLOSE get_non_pickable_count ;
839
840 IF (l_non_pick_eligible_count > 0 or l_tpw_org = 'Y') THEN
841 l_picking_req := 'N';
842 ELSE
843 l_picking_req := 'Y';
844 END IF;
845
846 IF (l_fte_installed = 'N' AND l_screening_req = 'N' AND l_tpw_org = 'N' AND l_picking_req = 'Y') THEN
847 x_wf_process := 'R_DEL_GEN';
848 ELSIF (l_fte_installed = 'N' AND l_screening_req = 'N' AND l_tpw_org = 'N' AND l_picking_req = 'N') THEN
849 x_wf_process:= 'R_DEL_SHP';
850 ELSIF (l_fte_installed = 'N' AND l_screening_req = 'Y' AND l_tpw_org = 'N' AND l_picking_req = 'Y') THEN
851 x_wf_process:= 'R_DEL_ITM_GEN';
852 ELSIF (l_fte_installed = 'N' AND l_screening_req = 'Y' AND l_tpw_org = 'N' AND l_picking_req = 'N') THEN
853 x_wf_process:= 'R_DEL_ITM_SHP';
854 ELSIF (l_fte_installed = 'Y' AND l_screening_req = 'N' AND l_tpw_org = 'N' AND l_picking_req = 'Y') THEN
855 x_wf_process:= 'R_DEL_FTE_GEN';
856 ELSIF (l_fte_installed = 'Y' AND l_screening_req = 'N' AND l_tpw_org = 'N' AND l_picking_req = 'N') THEN
857 x_wf_process:= 'R_DEL_FTE_SHP';
858 ELSIF (l_fte_installed = 'Y' AND l_screening_req = 'N' AND l_tpw_org = 'Y' AND l_picking_req = 'N') THEN
859 x_wf_process:= 'R_DEL_FTE_TPW';
860 ELSIF (l_fte_installed = 'Y' AND l_screening_req = 'Y' AND l_tpw_org = 'N' AND l_picking_req = 'Y') THEN
861 x_wf_process:= 'R_DEL_FTE_ITM_GEN';
862 ELSIF (l_fte_installed = 'Y' AND l_screening_req = 'Y' AND l_tpw_org = 'N' AND l_picking_req = 'N') THEN
863 x_wf_process:= 'R_DEL_FTE_ITM_SHP';
864 ELSIF (l_fte_installed = 'Y' AND l_screening_req = 'Y' AND l_tpw_org = 'Y' AND l_picking_req = 'N') THEN
865 x_wf_process:= 'R_DEL_FTE_ITM_TPW';
866 END IF;
867 SR*/
868
869 x_wf_process := 'R_DEL_GEN';
870 --Currenly not in use.
871 /*IF ( l_fte_installed = 'Y') THEN
872 x_wf_process := 'R_DEL_FTE_GEN';
873 ELSE
874 x_wf_process := 'R_DEL_GEN';
875 END IF;*/
876
877 END IF;
878
879 IF (x_wf_process is null) THEN
880 RAISE e_process_not_found;
881 END IF;
882
883 OPEN get_org_code;
884 FETCH get_org_code into l_organization_code;
885 CLOSE get_org_code ;
886
887 IF l_debug_on THEN
888 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WF_STD.GET_CUSTOM_WF_PROCESS',
889 WSH_DEBUG_SV.C_PROC_LEVEL);
890 END IF;
891
892 Get_Custom_Wf_Process(p_wf_process => x_wf_process,
893 p_org_code => l_organization_code,
894 x_wf_process => l_custom_process_name,
895 x_return_status => l_return_status);
896 IF l_debug_on THEN
897 WSH_DEBUG_SV.log(l_module_name,'l_custom_process_name',l_custom_process_name);
898 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
899 END IF;
900
901 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
902 x_return_status := l_return_status;
903 IF l_debug_on THEN
904 WSH_DEBUG_SV.pop(l_module_name);
905 END IF;
906 RETURN;
907 ELSE
908 x_wf_process:=l_custom_process_name;
909 END IF;
910 IF l_debug_on THEN
911 WSH_DEBUG_SV.pop(l_module_name);
912 END IF;
913
914
915 EXCEPTION
916 WHEN e_invalid_org THEN
917 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR ;
918 IF l_debug_on THEN
919 WSH_DEBUG_SV.logmsg(l_module_name,'Invalid Organization Id passed',WSH_DEBUG_SV.C_EXCEP_LEVEL);
920 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:E_INVALID_ORG');
921 END IF;
922 WHEN e_process_not_found THEN
923 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR ;
924 IF l_debug_on THEN
925 WSH_DEBUG_SV.logmsg(l_module_name,'Process not found for the current Setup parameters',
926 WSH_DEBUG_SV.C_EXCEP_LEVEL);
927 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:E_PROCESS_NOT_FOUND');
928 END IF;
929 WHEN others THEN
930 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
931 IF l_debug_on THEN
932 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
933 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
934 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
935 END IF;
936
937 END process_selector;
938
939 ---------------------------------------------------------------------------------------
940 --
941 -- Procedure: Raise_Event
942 -- Parameters: p_entity_type - 'TRIP','DELIVERY','LINE','STOP'
943 -- p_entity_id - TRIP_ID, DELIVERY_ID, DELIVERY_DETAIL_ID,STOP_ID
944 -- p_event - The Event to be raised
945 -- p_event_data - Optional Event data to be sent while raising the event
946 -- p_parameters - Optional Parameters to be sent while raising the event
947 -- p_send_date - Optional date to indicate when the event should
948 -- become available for subscription processing.
949 -- p_organization_id - The Organization Id
950 --
951 -- x_return_status - Returns WSH_UTIL_CORE.G_RET_STS_SUCCESS if Success
952 --
953 -- Description: This Procedure raises the event in the following scenario
954 -- 1) If a Process already exists for this Entity
955 -- 2) If no process exists, checks the Global and Shipping parameters
956 -- for raising events and raises accordingly
957 ---------------------------------------------------------------------------------------
958
959 PROCEDURE Raise_Event(
960 p_entity_type IN VARCHAR2,
961 p_entity_id IN VARCHAR2,
962 p_event IN VARCHAR2,
963 p_event_data IN CLOB DEFAULT NULL,
964 p_parameters IN wf_parameter_list_t DEFAULT NULL,
965 p_send_date IN DATE DEFAULT SYSDATE,
966 p_organization_id IN NUMBER DEFAULT NULL,
967 x_return_status OUT NOCOPY VARCHAR2) IS
968
969
970 l_param_info WSH_SHIPPING_PARAMS_PVT.Parameter_Rec_Typ;
971 l_global_parameters WSH_SHIPPING_PARAMS_PVT.Global_Parameters_Rec_Typ;
972 l_parameters WF_PARAMETER_LIST_T;
973 l_raise_event BOOLEAN;
974 l_wf_process_exists VARCHAR2(1);
975 l_return_status VARCHAR2(1);
976
977 e_org_required EXCEPTION;
978
979 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'RAISE_EVENT';
980 l_debug_on BOOLEAN;
981
982
983 BEGIN
984
985 -- Debug Statements
986 --
987 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
988 --
989 IF l_debug_on IS NULL
990 THEN
991 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
992 END IF;
993 --
994 IF l_debug_on THEN
995 WSH_DEBUG_SV.push(l_module_name);
996 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_TYPE',p_entity_type);
997 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_ID',p_entity_id);
998 WSH_DEBUG_SV.log(l_module_name,'P_EVENT',p_event);
999 WSH_DEBUG_SV.log(l_module_name,'P_SEND_DATE',p_send_date);
1000 WSH_DEBUG_SV.log(l_module_name,'P_ORGANIZATION_ID',p_organization_id);
1001 END IF;
1002
1003 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1004 l_raise_event := FALSE;
1005
1006 IF (p_entity_type IN ('DELIVERY','LINE') AND p_organization_id IS NULL ) THEN
1007 raise e_org_required;
1008 END IF;
1009
1010 -- Raise event if a workflow process exists
1011 IF (p_entity_type = 'TRIP' or p_entity_type = 'DELIVERY') THEN
1012
1013 IF l_debug_on THEN
1014 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WF_STD.CHECK_WF_EXISTS',
1015 WSH_DEBUG_SV.C_PROC_LEVEL);
1016 END IF;
1017
1018 check_wf_exists(p_entity_type => p_entity_type,
1019 p_entity_id => p_entity_id,
1020 x_wf_process_exists => l_wf_process_exists,
1021 x_return_status => l_return_status);
1022
1023 IF l_debug_on THEN
1024 WSH_DEBUG_SV.log(l_module_name,'l_wf_process_exists',l_wf_process_exists);
1025 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1026 END IF;
1027
1028 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1029 x_return_status := l_return_status;
1030 IF l_debug_on THEN
1031 WSH_DEBUG_SV.pop(l_module_name);
1032 END IF;
1033 RETURN;
1034 END IF;
1035
1036 IF (l_wf_process_exists = 'Y') THEN
1037 l_raise_event := TRUE;
1038 END IF;
1039 END IF;
1040
1041 -- Get Global event parameters and raise event accordingly
1042 IF ( NOT l_raise_event) THEN
1043 IF l_debug_on THEN
1044 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_SHIPPING_PARAMS_PVT.GET_GLOBAL_PARAMETERS',
1045 WSH_DEBUG_SV.C_PROC_LEVEL);
1046 END IF;
1047
1048 WSH_SHIPPING_PARAMS_PVT.Get_Global_Parameters(x_Param_Info => l_global_parameters,
1049 x_return_status => l_return_status);
1050
1051 IF l_debug_on THEN
1052 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1053 END IF;
1054
1055 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1056 x_return_status := l_return_status;
1057 IF l_debug_on THEN
1058 WSH_DEBUG_SV.pop(l_module_name);
1059 END IF;
1060 RETURN;
1061 END IF;
1062
1063 IF ((p_entity_type = 'TRIP' OR p_entity_type = 'STOP')
1064 AND NVL(l_global_parameters.RAISE_BUSINESS_EVENTS,'N')='Y') THEN
1065 l_raise_event := TRUE;
1066 END IF;
1067 END IF;
1068
1069 -- Get Shipping event parameters and raise event accordingly
1070 IF ( NOT l_raise_event AND p_organization_id is not null) THEN
1071 IF l_debug_on THEN
1072 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_SHIPPING_PARAMS_PVT.GET',
1073 WSH_DEBUG_SV.C_PROC_LEVEL);
1074 END IF;
1075
1076 WSH_SHIPPING_PARAMS_PVT.Get(
1077 p_organization_id => p_organization_id,
1078 x_param_info => l_param_info,
1079 x_return_status => l_return_status);
1080
1081 IF l_debug_on THEN
1082 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1083 END IF;
1084
1085 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1086 x_return_status := l_return_status;
1087 IF l_debug_on THEN
1088 WSH_DEBUG_SV.pop(l_module_name);
1089 END IF;
1090 RETURN;
1091 END IF;
1092
1093 IF (NVL(l_param_info.RAISE_BUSINESS_EVENTS,'N')='Y' and NVL(l_global_parameters.RAISE_BUSINESS_EVENTS,'N')='Y')
1094 THEN
1095 l_raise_event := TRUE;
1096 END IF;
1097 END IF;
1098
1099 IF (l_raise_event) THEN
1100 IF (p_parameters IS NOT NULL) THEN
1101 l_parameters := p_parameters;
1102 END IF;
1103
1104 WF_EVENT.AddParameterToList (p_name => p_entity_type || '_ID',
1105 p_value => p_entity_id,
1106 p_parameterlist => l_parameters);
1107 WF_EVENT.AddParameterToList (p_name => 'SEND_DATE',
1108 p_value => p_send_date,
1109 p_parameterlist => l_parameters);
1110
1111 WF_EVENT.raise(
1112 p_event_name => p_event,
1113 p_event_key => p_entity_id,
1114 p_event_data => p_event_data,
1115 p_parameters => l_parameters,
1116 p_send_date => p_send_date);
1117
1118 /* This Procedure Call was just added to test business events.
1119
1120 Test_Events(p_entity_type => p_entity_type,
1121 p_entity_id => p_entity_id,
1122 p_send_date => p_send_date,
1123 p_event => p_event);
1124
1125 */
1126 END IF;
1127
1128 IF l_debug_on THEN
1129 WSH_DEBUG_SV.pop(l_module_name);
1130 END IF;
1131
1132 EXCEPTION
1133 WHEN e_org_required THEN
1134 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR ;
1135 IF l_debug_on THEN
1136 WSH_DEBUG_SV.logmsg(l_module_name,'Organization Id Required.Oracle error message is '||
1137 SQLERRM,WSH_DEBUG_SV.C_EXCEP_LEVEL);
1138 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:E_ORG_REQUIRED');
1139 END IF;
1140
1141 WHEN others THEN
1142 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1143 IF l_debug_on THEN
1144 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
1145 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1146 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1147 END IF;
1148
1149 END Raise_Event;
1150
1151 ---------------------------------------------------------------------------------------
1152 -- Procedure: confirm_start_wf_process
1153 -- Parameters: p_entity_type - 'TRIP','DELIVERY'
1154 -- p_organization_id - The Organization Id
1155 --
1156 -- x_start_wf_process - Returns 'Y' if process can be started, else 'N'
1157 -- x_return_status - Returns WSH_UTIL_CORE.G_RET_STS_SUCCESS if Success
1158 --
1159 -- Description: This Procedure obtains the Global and Shipping parameter
1160 -- values for 'Enable Tracking Workflows' and determines
1161 -- if a process can be started by,
1162 -- Global-TW Shipping-TW Eligible Workflow Entity
1163 -- --------- ----------- -------------------------
1164 -- None Delivery None
1165 -- None None None
1166 -- Trip Delivery Trip
1167 -- Trip None Trip
1168 -- Delivery Delivery Delivery
1169 -- Delivery None None
1170 -- Both Delivery Both
1171 -- Both None Trip
1172 ---------------------------------------------------------------------------------------
1173
1174 PROCEDURE confirm_start_wf_process(
1175 p_entity_type IN VARCHAR2,
1176 p_organization_id IN NUMBER,
1177 x_start_wf_process OUT NOCOPY VARCHAR2,
1178 x_return_status OUT NOCOPY VARCHAR2) IS
1179
1180 l_param_info WSH_SHIPPING_PARAMS_PVT.Parameter_Rec_Typ;
1181 l_global_parameters WSH_SHIPPING_PARAMS_PVT.Global_Parameters_Rec_Typ;
1182
1183 l_global_enable_tracking_wfs VARCHAR2(30);
1184 l_shipping_enable_tracking_wfs VARCHAR2(30);
1185
1186 l_return_status VARCHAR2(1);
1187
1188 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CONFIRM_START_WF_PROCESS';
1189 l_debug_on BOOLEAN;
1190
1191
1192 BEGIN
1193
1194 -- Debug Statements
1195 --
1196 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1197 --
1198 IF l_debug_on IS NULL
1199 THEN
1200 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1201 END IF;
1202 --
1203 IF l_debug_on THEN
1204 WSH_DEBUG_SV.push(l_module_name);
1205 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_TYPE',p_entity_type);
1206 WSH_DEBUG_SV.log(l_module_name,'P_ORGANIZATION_ID',p_organization_id);
1207 END IF;
1208
1209 x_start_wf_process := 'N';
1210 x_return_status:=WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1211
1212 -- Get Global and Shipping event parameters and raise event accordingly
1213
1214 IF (p_entity_type <> 'TRIP') THEN
1215
1216 IF l_debug_on THEN
1217 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_SHIPPING_PARAMS_PVT.GET',
1218 WSH_DEBUG_SV.C_PROC_LEVEL);
1219 END IF;
1220
1221 WSH_SHIPPING_PARAMS_PVT.Get(
1222 p_organization_id => p_organization_id,
1223 x_param_info => l_param_info,
1224 x_return_status => l_return_status);
1225 IF l_debug_on THEN
1226 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1227 END IF;
1228
1229 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1230 x_return_status := l_return_status;
1231 IF l_debug_on THEN
1232 WSH_DEBUG_SV.pop(l_module_name);
1233 END IF;
1234 RETURN;
1235 ELSE
1236 l_shipping_enable_tracking_wfs:=NVL(l_param_info.ENABLE_TRACKING_WFS,'NONE');
1237 END IF;
1238 END IF;
1239
1240 IF l_debug_on THEN
1241 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_SHIPPING_PARAMS_PVT.GET_GLOBAL_PARAMETERS',
1242 WSH_DEBUG_SV.C_PROC_LEVEL);
1243 END IF;
1244
1245 WSH_SHIPPING_PARAMS_PVT.Get_Global_Parameters(x_Param_Info => l_global_parameters,
1246 x_return_status => l_return_status);
1247
1248 IF l_debug_on THEN
1249 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1250 END IF;
1251
1252
1253 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1254 x_return_status := l_return_status;
1255 IF l_debug_on THEN
1256 WSH_DEBUG_SV.pop(l_module_name);
1257 END IF;
1258 RETURN;
1259 ELSE
1260 l_global_enable_tracking_wfs:= NVL(l_global_parameters.ENABLE_TRACKING_WFS,'NONE');
1261 END IF;
1262
1263
1264 IF (l_global_enable_tracking_wfs = 'NONE') THEN
1265 IF l_debug_on THEN
1266 WSH_DEBUG_SV.pop(l_module_name);
1267 END IF;
1268 RETURN;
1269 ELSIF ( (l_global_enable_tracking_wfs = 'TRIP' or l_global_enable_tracking_wfs = 'BOTH') and p_entity_type =
1270 'TRIP') THEN
1271 x_start_wf_process := 'Y';
1272 IF l_debug_on THEN
1273 WSH_DEBUG_SV.pop(l_module_name);
1274 END IF;
1275 RETURN;
1276 ELSIF ( (l_global_enable_tracking_wfs = 'DELIVERY' or l_global_enable_tracking_wfs = 'BOTH') and
1277 l_shipping_enable_tracking_wfs = 'DELIVERY' and p_entity_type = 'DELIVERY' ) THEN
1278 x_start_wf_process := 'Y';
1279 IF l_debug_on THEN
1280 WSH_DEBUG_SV.pop(l_module_name);
1281 END IF;
1282 RETURN;
1283 END IF;
1284 IF l_debug_on THEN
1285 WSH_DEBUG_SV.pop(l_module_name);
1286 END IF;
1287
1288 EXCEPTION
1289 WHEN others THEN
1290 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1291 IF l_debug_on THEN
1292 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
1293 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1294 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1295 END IF;
1296 END confirm_start_wf_process;
1297
1298 ---------------------------------------------------------------------------------------
1299 -- Procedure: Check_Wf_Exists
1300 -- Parameters: p_entity_type - 'TRIP','DELIVERY','DELIVERY_C'(controlling)
1301 -- p_entity_id - TRIP_ID or DELIVERY_ID
1302 --
1303 -- x_wf_process_exists - Returns 'Y' if Wf exists, else 'N'
1304 -- IF DELIVERY_C then returns 'Y' only if Cntll wf exists
1305 -- x_return_status - Returns WSH_UTIL_CORE.G_RET_STS_SUCCESS if Success
1306 --
1307 -- Description: This Procedure checks from WSH_NEW_DELIVERIES or WSH_TRIPS
1308 -- if a workflow process has been started for this entity.
1309 ---------------------------------------------------------------------------------------
1310
1311 PROCEDURE Check_Wf_Exists(
1312 p_entity_type IN VARCHAR2,
1313 p_entity_id IN NUMBER,
1314 x_wf_process_exists OUT NOCOPY VARCHAR2,
1315 x_return_status OUT NOCOPY VARCHAR2) IS
1316
1317 CURSOR get_trip_wf_name(l_trip_id IN NUMBER) IS
1318 select trip_wf_process
1319 from wsh_trips where trip_id=l_trip_id;
1320
1321 CURSOR get_delivery_wf_name(l_delivery_id IN NUMBER) IS
1322 select delivery_wf_process,delivery_scpod_wf_process
1323 from wsh_new_deliveries where delivery_id = l_delivery_id;
1324
1325 l_process_name VARCHAR2(30);
1326 l_scpod_process_name VARCHAR2(30);
1327 e_invalid_type EXCEPTION;
1328
1329 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CHECK_WF_EXISTS';
1330 l_debug_on BOOLEAN;
1331
1332
1333 BEGIN
1334
1335 -- Debug Statements
1336 --
1337 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1338 --
1339 IF l_debug_on IS NULL
1340 THEN
1341 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1342 END IF;
1343 --
1344 IF l_debug_on THEN
1345 WSH_DEBUG_SV.push(l_module_name);
1346 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_TYPE',p_entity_type);
1347 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_ID',p_entity_id);
1348 END IF;
1349
1350 x_return_status:=WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1351
1352 -- Get the Process name from Entity Tables
1353 IF (p_entity_type = 'TRIP') THEN
1354 OPEN get_trip_wf_name(p_entity_id);
1355 FETCH get_trip_wf_name into l_process_name;
1356 CLOSE get_trip_wf_name ;
1357 ELSIF (p_entity_type IN ('DELIVERY','DELIVERY_C')) THEN
1358 OPEN get_delivery_wf_name(p_entity_id);
1359 FETCH get_delivery_wf_name into l_process_name,l_scpod_process_name;
1360 CLOSE get_delivery_wf_name ;
1361 ELSE
1362 RAISE e_invalid_type;
1363 END IF;
1364
1365 IF (p_entity_type = 'DELIVERY_C') THEN
1366 IF (l_scpod_process_name is null) THEN
1367 x_wf_process_exists:='N';
1368 ELSE
1369 x_wf_process_exists:='Y';
1370 END IF;
1371 IF l_debug_on THEN
1372 WSH_DEBUG_SV.pop(l_module_name);
1373 END IF;
1374 RETURN;
1375 END IF;
1376
1377 IF (l_process_name is null AND l_scpod_process_name is null) THEN
1378 x_wf_process_exists:='N';
1379 ELSE
1380 x_wf_process_exists:='Y';
1381 END IF;
1382
1383 IF l_debug_on THEN
1384 WSH_DEBUG_SV.pop(l_module_name);
1385 END IF;
1386
1387 EXCEPTION
1388 WHEN e_invalid_type THEN
1389 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR ;
1390 IF l_debug_on THEN
1391 WSH_DEBUG_SV.logmsg(l_module_name,'Invalid Entity type passed.Oracle error message is '||
1392 SQLERRM,WSH_DEBUG_SV.C_EXCEP_LEVEL);
1393 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:E_INVALID_TYPE');
1394 END IF;
1395 WHEN no_data_found THEN
1396 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR ;
1397 IF l_debug_on THEN
1398 WSH_DEBUG_SV.logmsg(l_module_name,'No record found for the entity.Oracle error message is '||
1399 SQLERRM,WSH_DEBUG_SV.C_EXCEP_LEVEL);
1400 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:NO_DATA_FOUND');
1401 END IF;
1402 WHEN others THEN
1403 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1404 IF l_debug_on THEN
1405 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
1406 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1407 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1408 END IF;
1409
1410 END check_wf_exists;
1411 ---------------------------------------------------------------------------------------
1412
1413 FUNCTION Wf_Exists(p_entity_type IN VARCHAR2,
1414 p_entity_id IN NUMBER) RETURN
1415 BOOLEAN IS
1416 l_wf_process_exists VARCHAR2(1);
1417 l_return_status VARCHAR2(1);
1418 BEGIN
1419
1420 Check_Wf_Exists(p_entity_type => p_entity_type,
1421 p_entity_id => p_entity_id,
1422 x_wf_process_exists => l_wf_process_exists,
1423 x_return_status => l_return_status);
1424 IF (l_wf_process_exists = 'Y') THEN
1425 RETURN TRUE;
1426 ELSE
1427 RETURN FALSE;
1428 END IF;
1429 END Wf_Exists;
1430 ---------------------------------------------------------------------------------------
1431 -- Procedure: Get_Custom_Wf_Process
1432 -- Parameters: p_wf_process - The Process selected for the enity
1433 -- p_org_code - The organization code
1434 --
1435 -- x_wf_process - Returns the custom process name specified
1436 -- with the lookups else the orginial process
1437 -- x_return_status - Returns WSH_UTIL_CORE.G_RET_STS_SUCCESS if Success
1438 --
1439 -- Description: This Procedure queries from the WSH_LOOKUPS for any custom
1440 -- process name specified by the User through the lookups for
1441 -- a particular process else returns the original process
1442 ---------------------------------------------------------------------------------------
1443
1444 PROCEDURE Get_Custom_Wf_Process(
1445 p_wf_process IN VARCHAR2,
1446 p_org_code IN VARCHAR2,
1447 x_wf_process OUT NOCOPY VARCHAR2,
1448 x_return_status OUT NOCOPY VARCHAR2) IS
1449 l_custom_process VARCHAR2(80);
1450
1451 CURSOR get_custom_process IS
1452 select meaning
1453 from WSH_LOOKUPS where lookup_type = p_wf_process and lookup_code = p_org_code
1454 and sysdate between nvl(start_date_active,sysdate) and nvl(end_date_active,sysdate)
1455 and enabled_flag='Y';
1456
1457 CURSOR get_gen_custom_process IS
1458 select meaning
1459 from WSH_LOOKUPS where lookup_type = p_wf_process and lookup_code = 'ALL'
1460 and sysdate between nvl(start_date_active,sysdate) and nvl(end_date_active,sysdate)
1461 and enabled_flag='Y';
1462
1463 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_CUSTOM_WF_PROCESS';
1464 l_debug_on BOOLEAN;
1465
1466
1467 BEGIN
1468
1469 -- Debug Statements
1470 --
1471 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1472 --
1473 IF l_debug_on IS NULL
1474 THEN
1475 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1476 END IF;
1477 --
1478 IF l_debug_on THEN
1479 WSH_DEBUG_SV.push(l_module_name);
1480 WSH_DEBUG_SV.log(l_module_name,'P_WF_PROCESS',p_wf_process);
1481 WSH_DEBUG_SV.log(l_module_name,'P_ORG_CODE',p_org_code);
1482 END IF;
1483
1484 x_return_status:=WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1485
1486 OPEN get_custom_process;
1487 FETCH get_custom_process into l_custom_process;
1488
1489 IF get_custom_process%NOTFOUND THEN
1490 OPEN get_gen_custom_process;
1491 FETCH get_gen_custom_process INTO l_custom_process;
1492 IF get_gen_custom_process%NOTFOUND THEN
1493 x_wf_process := p_wf_process;
1494 RETURN;
1495 END IF;
1496 CLOSE get_gen_custom_process;
1497 END IF ;
1498 CLOSE get_custom_process ;
1499
1500 IF (l_custom_process is not null) THEN
1501 x_wf_process := l_custom_process;
1502 END IF;
1503
1504 IF l_debug_on THEN
1505 WSH_DEBUG_SV.pop(l_module_name);
1506 END IF;
1507
1508 EXCEPTION
1509 WHEN others THEN
1510 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1511 IF l_debug_on THEN
1512 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured.
1513 Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1514 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1515 END IF;
1516 END Get_Custom_Wf_Process;
1517 ---------------------------------------------------------------------------------------
1518 -- Procedure: Purge_Entity
1519 -- Parameters: p_entity_type - TRIP / DELIVERY
1520 -- p_entity_ids - Ids of entities to be purged
1521 -- p_action - CLOSE / PURGE
1522 -- p_docommit - Specify TRUE/FALSE to indicate whether
1523 -- to commit data while purging.
1524 --
1525 -- x_purged_count - No. of entities successfully purged/closed
1526 -- x_return_status - Returns WSH_UTIL_CORE.G_RET_STS_SUCCESS if Success
1527 --
1528 -- Description: This Procedure finds out the Item status for every entity id.
1529 -- If it is complete then purges it, if not complete aborts the process
1530 -- and then purges the item.
1531 -- If p_action is CLOSE alone then aborts the process if not completed.
1532 ---------------------------------------------------------------------------------------
1533 Procedure Purge_Entity(
1534 p_entity_type IN VARCHAR2,
1535 p_entity_ids IN WSH_UTIL_CORE.column_tab_type,
1536 p_action IN VARCHAR2 DEFAULT 'PURGE',
1537 p_docommit IN BOOLEAN DEFAULT FALSE,
1538 x_success_count OUT NOCOPY NUMBER,
1539 x_return_status OUT NOCOPY VARCHAR2) IS
1540
1541 l_wf_status VARCHAR2(30); -- COMPLETE/ERROR/SUSPENDED
1542 l_result VARCHAR2(30);
1543 l_itemtype VARCHAR2(30);
1544
1545 l_suc_entity_ids WSH_UTIL_CORE.column_tab_type;
1546
1547 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PURGE_ENTITY';
1548 l_debug_on BOOLEAN;
1549
1550 BEGIN
1551
1552 -- Debug Statements
1553 --
1554 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1555 --
1556 IF l_debug_on IS NULL
1557 THEN
1558 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1559 END IF;
1560 --
1561 IF l_debug_on THEN
1562 WSH_DEBUG_SV.push(l_module_name);
1563 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_TYPE',p_entity_type);
1564 WSH_DEBUG_SV.log(l_module_name,'P_ACTION',p_action);
1565 WSH_DEBUG_SV.log(l_module_name,'P_DOCOMMIT',p_docommit);
1566 END IF;
1567
1568 x_return_status:=WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1569 SAVEPOINT PURGE_ENTITY_UPDATE;
1570
1571 IF (p_entity_type = 'TRIP') THEN
1572 l_itemtype := 'WSHTRIP';
1573 ELSE
1574 l_itemtype := 'WSHDEL';
1575 END IF;
1576
1577 FOR i IN 1..p_entity_ids.COUNT LOOP
1578 IF ( Wf_Exists(p_entity_type, p_entity_ids(i))) THEN
1579 BEGIN
1580 --
1581 IF l_debug_on THEN
1582 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_IDS(I)',p_entity_ids(i));
1583 END IF;
1584 --
1585 WF_ENGINE.ItemStatus(itemtype => l_itemtype,
1586 itemkey => p_entity_ids(I),
1587 status => l_wf_status,
1588 result => l_result);
1589
1590 IF (l_wf_status <> 'COMPLETE') THEN
1591 WF_ENGINE.AbortProcess(itemtype => l_itemtype,
1592 itemkey => p_entity_ids(i)); -- ,l_wf_end_result);
1593 IF (p_action = 'PURGE') THEN
1594 WF_PURGE.Items(itemtype => l_itemtype,
1595 itemkey => p_entity_ids(i),
1596 docommit => p_docommit,
1597 force => TRUE);
1598 WF_PURGE.Total(itemtype => l_itemtype,
1599 itemkey => p_entity_ids(i),
1600 docommit => p_docommit);
1601 END IF;
1602 ELSE
1603 IF (p_action = 'PURGE') THEN
1604 WF_purge.Items(itemtype => l_itemtype,
1605 itemkey => p_entity_ids(i),
1606 docommit => p_docommit);
1607 END IF;
1608 END IF;
1609
1610 l_suc_entity_ids(l_suc_entity_ids.count + 1) := p_entity_ids(i);
1611 EXCEPTION
1612 WHEN OTHERS THEN
1613 IF l_debug_on THEN
1614 WSH_DEBUG_SV.logmsg(l_module_name,'Error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_EXCEP_LEVEL);
1615 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1616 END IF;
1617 END;
1618 END IF;
1619 END LOOP;
1620
1621 IF (p_entity_type = 'DELIVERY') THEN
1622 FORALL i IN 1..l_suc_entity_ids.count
1623 UPDATE WSH_NEW_DELIVERIES
1624 SET delivery_wf_process=NULL,
1625 delivery_scpod_wf_process=NULL,
1626 del_wf_intransit_attr=NULL,
1627 del_wf_interface_attr=NULL,
1628 del_wf_close_attr=NULL
1629 WHERE delivery_id = l_suc_entity_ids(i);
1630 ELSIF (p_entity_type = 'TRIP') THEN
1631 FORALL i IN 1..l_suc_entity_ids.count
1632 UPDATE WSH_TRIPS
1633 SET trip_wf_process=NULL
1634 WHERE trip_id=l_suc_entity_ids(i);
1635 END IF;
1636
1637 x_success_count := l_suc_entity_ids.count;
1638 /*
1639 IF (l_suc_entity_ids.count = 0) THEN
1640 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR ;
1641 ELSIF (p_entity_ids.count <> l_suc_entity_ids.count) THEN
1642 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING ;
1643 ELSIF (p_entity_ids.count = l_suc_entity_ids.count) THEN
1644 x_return_status:=WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1645 END IF;
1646 */
1647 IF l_debug_on THEN
1648 WSH_DEBUG_SV.pop(l_module_name);
1649 END IF;
1650
1651
1652 EXCEPTION
1653 WHEN others THEN
1654 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1655 IF l_debug_on THEN
1656 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1657 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1658 END IF;
1659
1660 END Purge_Entity;
1661
1662
1663 ---------------------------------------------------------------------------------------
1664 --
1665 -- Procedure: Log_Wf_Exception
1666 -- Parameters: p_entity_type - 'DELIVERY',
1667 -- p_entity_id - DELIVERY_ID
1668 -- p_ship_from_location_id - The Ship from Location Id
1669 -- p_exception_name WSH_LAUNCH_WF_FAILED
1670 -- WSH_DEL_SCPOD_PURGED
1671 --
1672 -- x_return_status - Returns WSH_UTIL_CORE.G_RET_STS_SUCCESS if Success
1673 --
1674 -- Description: This Procedure logs an exception against the specified Entity
1675 --
1676 --
1677 ---------------------------------------------------------------------------------------
1678
1679 PROCEDURE Log_Wf_Exception(p_entity_type IN VARCHAR2,
1680 p_entity_id IN NUMBER,
1681 p_ship_from_location_id in NUMBER DEFAULT NULL,
1682 p_logging_entity IN VARCHAR2,
1683 p_exception_name in VARCHAR2,
1684 x_return_status out nocopy VARCHAR2) IS
1685
1686 l_exception_name varchar2(30);
1687 l_msg varchar2(2000);
1688 l_exception_msg_count NUMBER;
1689 l_exception_msg_data varchar2(2000);
1690 l_exception_id NUMBER := NULL;
1691 l_ship_from_location_id NUMBER;
1692
1693 --
1694 l_debug_on BOOLEAN;
1695 --
1696 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOG_WF_EXCEPTION';
1697
1698 BEGIN
1699
1700 --
1701 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1702 --
1703 IF l_debug_on IS NULL THEN
1704 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1705 END IF;
1706 --
1707 IF l_debug_on THEN
1708 wsh_debug_sv.push(l_module_name);
1709 wsh_debug_sv.log(l_module_name, 'P_ENTITY_TYPE',p_entity_type);
1710 wsh_debug_sv.log(l_module_name, 'P_ENTITY_ID',p_entity_id);
1711 wsh_debug_sv.log(l_module_name, 'P_SHIP_FROM_LOCATION_ID',p_ship_from_location_id);
1712 wsh_debug_sv.log(l_module_name, 'P_LOGGING_ENTITY',p_logging_entity);
1713 wsh_debug_sv.log(l_module_name, 'P_EXCEPTION_NAME',p_exception_name);
1714 END IF;
1715
1716 IF (p_ship_from_location_id IS NULL AND p_entity_type = 'DELIVERY') THEN
1717 SELECT INITIAL_PICKUP_LOCATION_ID INTO l_ship_from_location_id
1718 FROM WSH_NEW_DELIVERIES
1719 WHERE delivery_id = p_entity_id;
1720 ELSE
1721 l_ship_from_location_id := p_ship_from_location_id;
1722 END IF;
1723
1724 IF (p_exception_name = 'WSH_DEL_SCPOD_PURGED') THEN
1725 l_msg := FND_MESSAGE.Get_String('WSH','WSH_DEL_SCPOD_TERMINATED');
1726 ELSIF (p_exception_name = 'WSH_LAUNCH_WF_FAILED') THEN
1727 l_msg := FND_MESSAGE.Get_String('WSH','WSH_LAUNCH_WF_FAILED');
1728 END IF;
1729
1730 IF l_debug_on THEN
1731 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_XC_UTIL.Log_Exception',
1732 WSH_DEBUG_SV.C_PROC_LEVEL);
1733 END IF;
1734
1735 IF (p_entity_type = 'DELIVERY') THEN
1736 wsh_xc_util.log_exception(
1737 p_api_version => 1.0,
1738 x_return_status => x_return_status,
1739 x_msg_count => l_exception_msg_count,
1740 x_msg_data => l_exception_msg_data,
1741 x_exception_id => l_exception_id ,
1742 p_logged_at_location_id => l_ship_from_location_id,
1743 p_exception_location_id => l_ship_from_location_id,
1744 p_logging_entity => p_logging_entity,
1745 p_logging_entity_id => FND_GLOBAL.USER_ID,
1746 p_exception_name => p_exception_name,
1747 p_message => l_msg,
1748 p_delivery_id => p_entity_id
1749 );
1750 END IF;
1751 IF l_debug_on THEN
1752 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_XC_UTIL.Log_Exception',x_return_status);
1753 WSH_DEBUG_SV.log(l_module_name,'L_EXCEPTION_MSG_COUNT',l_exception_msg_count);
1754 WSH_DEBUG_SV.log(l_module_name,'L_EXCEPTION_MSG_DATA',l_exception_msg_data);
1755 WSH_DEBUG_SV.log(l_module_name,'L_EXCEPTION_ID',l_exception_id);
1756 END IF;
1757
1758 IF l_debug_on THEN
1759 WSH_DEBUG_SV.pop(l_module_name);
1760 END IF;
1761
1762
1763 EXCEPTION
1764 WHEN others THEN
1765 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1766 IF l_debug_on THEN
1767 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1768 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1769 END IF;
1770 END Log_Wf_Exception;
1771
1772 --========================================================================================
1773 -- FUNCTION : GET_WF_NAME
1774 -- This function will return the workflow process name associated with the entity
1775 -- with respect to p_entity_type
1776 --
1777 -- If encountered any exception/error , NULL will be returned.
1778 --
1779 -- SCOPE : PRIVATE
1780 -- PARAMETERS: p_entity_type Entity for which proceess_name is being looked.
1781 -- DELIVERY / Delivery Flow - Generic
1782 -- DELIVERY_C / Ship To Deliver subprocess
1783 -- TRIP / Trip Flow - Generic
1784 --
1785 -- p_entity_id Unique identifier for the entity i.e., Delivery_id / Trip_id.
1786 --
1787 -- COMMENT : This Function will fetch and return the process_name associated with the entity
1788 -- and the type which were passed as parameters.
1789 --
1790 -- This function is introduced as part of bugfix 8706771 .
1791 --========================================================================================
1792 FUNCTION GET_WF_NAME(p_entity_type IN VARCHAR2,
1793 p_entity_id IN NUMBER) RETURN
1794 VARCHAR2 IS
1795 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_WF_NAME';
1796 l_debug_on BOOLEAN;
1797 l_process_name VARCHAR2(30);
1798 l_scpod_process_name VARCHAR2(30);
1799
1800 CURSOR get_trip_wf_name(l_trip_id IN NUMBER) IS
1801 select trip_wf_process
1802 from wsh_trips where trip_id=l_trip_id;
1803
1804 CURSOR get_delivery_wf_name(l_delivery_id IN NUMBER) IS
1805 select delivery_wf_process,delivery_scpod_wf_process
1806 from wsh_new_deliveries where delivery_id = l_delivery_id;
1807
1808 ENTITY_NOT_FOUND exception;
1809 INVALID_ENTITY_TYPE exception;
1810
1811 BEGIN
1812
1813 -- Debug Statements
1814 --
1815 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1816 --
1817 IF l_debug_on IS NULL
1818 THEN
1819 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1820 END IF;
1821 --
1822 IF l_debug_on THEN
1823 WSH_DEBUG_SV.push(l_module_name);
1824 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_TYPE',p_entity_type);
1825 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_ID',p_entity_id);
1826 END IF;
1827
1828 -- Get the Process name from Entity Tables
1829 IF (p_entity_type = 'TRIP') THEN
1830 OPEN get_trip_wf_name(p_entity_id);
1831 FETCH get_trip_wf_name into l_process_name;
1832
1833 IF get_trip_wf_name%NOTFOUND THEN
1834 CLOSE get_trip_wf_name;
1835 RAISE ENTITY_NOT_FOUND;
1836 END IF;
1837
1838 CLOSE get_trip_wf_name ;
1839
1840 IF l_debug_on THEN
1841 WSH_DEBUG_SV.log(l_module_name,'l_process_name',l_process_name);
1842 WSH_DEBUG_SV.pop(l_module_name);
1843 END IF;
1844 RETURN l_process_name;
1845 ELSIF (p_entity_type = 'DELIVERY') THEN
1846 OPEN get_delivery_wf_name(p_entity_id);
1847 FETCH get_delivery_wf_name into l_process_name,l_scpod_process_name;
1848
1849 IF get_delivery_wf_name%NOTFOUND THEN
1850 CLOSE get_delivery_wf_name;
1851 RAISE ENTITY_NOT_FOUND;
1852 END IF;
1853
1854 CLOSE get_delivery_wf_name ;
1855
1856 IF l_debug_on THEN
1857 WSH_DEBUG_SV.log(l_module_name,'l_process_name',l_process_name);
1858 WSH_DEBUG_SV.pop(l_module_name);
1859 END IF;
1860 RETURN l_process_name;
1861 ELSIF (p_entity_type = 'DELIVERY_C') THEN
1862 OPEN get_delivery_wf_name(p_entity_id);
1863 FETCH get_delivery_wf_name into l_process_name,l_scpod_process_name;
1864
1865 IF get_delivery_wf_name%NOTFOUND THEN
1866 CLOSE get_delivery_wf_name;
1867 RAISE ENTITY_NOT_FOUND;
1868 END IF;
1869
1870 CLOSE get_delivery_wf_name ;
1871
1872 IF l_debug_on THEN
1873 WSH_DEBUG_SV.log(l_module_name,'l_process_name',l_scpod_process_name);
1874 WSH_DEBUG_SV.pop(l_module_name);
1875 END IF;
1876 RETURN l_scpod_process_name;
1877 ELSE
1878 RAISE INVALID_ENTITY_TYPE;
1879 END IF;
1880
1881 IF l_debug_on THEN
1882 WSH_DEBUG_SV.pop(l_module_name);
1883 END IF;
1884
1885 EXCEPTION
1886 WHEN ENTITY_NOT_FOUND THEN
1887 IF get_trip_wf_name%ISOPEN THEN
1888 close get_trip_wf_name;
1889 END IF;
1890
1891 IF get_delivery_wf_name%ISOPEN THEN
1892 close get_delivery_wf_name;
1893 END IF;
1894
1895 IF l_debug_on THEN
1896 WSH_DEBUG_SV.logmsg(l_module_name,'Expected error has occured. Oracle error message is '||p_entity_type||' entity '||p_entity_id||' does not exists',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1897 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:ENTITY_NOT_FOUND');
1898 END IF;
1899
1900 RETURN NULL;
1901
1902 WHEN INVALID_ENTITY_TYPE THEN
1903 IF get_trip_wf_name%ISOPEN THEN
1904 close get_trip_wf_name;
1905 END IF;
1906
1907 IF get_delivery_wf_name%ISOPEN THEN
1908 close get_delivery_wf_name;
1909 END IF;
1910
1911 IF l_debug_on THEN
1912 WSH_DEBUG_SV.logmsg(l_module_name,'Expected error has occured. Oracle error message is INVALID_ENTITY_TYPE passed: '||p_entity_type,WSH_DEBUG_SV.C_EXCEP_LEVEL);
1913 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:INVALID_ENTITY_TYPE');
1914 END IF;
1915
1916 RETURN NULL;
1917
1918 WHEN OTHERS THEN
1919 IF get_trip_wf_name%ISOPEN THEN
1920 close get_trip_wf_name;
1921 END IF;
1922
1923 IF get_delivery_wf_name%ISOPEN THEN
1924 close get_delivery_wf_name;
1925 END IF;
1926
1927 IF l_debug_on THEN
1928 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1929 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1930 END IF;
1931
1932 RETURN NULL;
1933
1934 END GET_WF_NAME;
1935
1936 --========================================================================================
1937 -- FUNCTION : WF_ENGINE_EVENT
1938 -- This function will derive the workflow process name for the trip/delivery
1939 -- and pass the event to those processes which are waiting to receive the
1940 -- corresponding event by calling WF API wf_engine.event.
1941 --
1942 -- The return status could be SUCCESS/ERROR
1943 -- This API must be called only from wsh_wf_std.instance_default_rule
1944 -- SCOPE : PRIVATE
1945 -- PARAMETERS: p_subscription_guid Unique identifier of the subscription for B.Event.
1946 --
1947 -- p_event Event info containing even name,event id , etc.,
1948 --
1949 -- COMMENT : This Function is used to derive the delivery generic/ship to deliver/trip
1950 -- workflow process name and then pass the event information to those processes
1951 -- to receive this event passed.
1952 -- The return status could be SUCCESS/ERROR which will be reported to business
1953 -- event system via api wsh_wf_std.instance_default_rule
1954 --
1955 -- This function is introduced as part of bugfix 8706771 and this is replacement
1956 -- for function wsh_wf_std.instance_default_rule
1957 --========================================================================================
1958 FUNCTION WF_ENGINE_EVENT (p_subscription_guid in raw,
1959 p_event in out nocopy WF_EVENT_T) return
1960 VARCHAR2 is
1961 l_eventname VARCHAR2(240);
1962 l_wf_pr_name VARCHAR2(30);
1963 l_eventkey VARCHAR2(240);
1964 l_entityid VARCHAR2(240);
1965
1966 l_raise_exception BOOLEAN;
1967 e_entityid_notfound EXCEPTION;
1968 e_event_error EXCEPTION;
1969
1970
1971 l_debug_on BOOLEAN;
1972 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'WF_ENGINE_EVENT';
1973
1974 BEGIN
1975
1976 --Get the EventName and EventKey from p_event to local variable
1977 l_eventname := p_event.getEventName;
1978 l_eventkey := p_event.getEventKey;
1979 l_raise_exception := FALSE;
1980
1981 -- Debug Statements
1982 --
1983 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1984 --
1985 IF l_debug_on IS NULL
1986 THEN
1987 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1988 END IF;
1989 --
1990 IF l_debug_on THEN
1991 WSH_DEBUG_SV.push(l_module_name);
1992 WSH_DEBUG_SV.log(l_module_name,'l_eventname',l_eventname);
1993 WSH_DEBUG_SV.log(l_module_name,'l_eventkey',l_eventkey);
1994 END IF;
1995
1996 l_entityid := l_eventkey;
1997
1998 -- Out Agent/To Agent/Priority/Workflow Type/Workflow Process
1999 -- We dont have any of the above specified in our business event subscriptions which are responsible for triggering the shipping workflow.
2000 -- Due to this we are not doing anything w.r.t input parameter p_subscription_guid and it disappears in this function without doing any processing.
2001 -- If in future we specify any value for these fileds.Then we need to add code here so that those things will be communicated/Set before calling wf_engine.event.
2002 -- Need to refer API WF_RULE.INSTANCE_DEFAULT_RULE for the code to be added.
2003
2004 IF (l_entityid is not NULL) THEN --{ If l_entityid is not null
2005 p_event.SetEventKey(l_entityid);
2006 BEGIN
2007
2008 IF (l_eventname = 'oracle.apps.wsh.delivery.pik.pickinitiated' OR
2009 l_eventname = 'oracle.apps.wsh.delivery.gen.shipconfirmed' OR
2010 l_eventname = 'oracle.apps.wsh.delivery.gen.open' OR
2011 l_eventname = 'oracle.apps.wsh.delivery.gen.setintransit' OR
2012 l_eventname = 'oracle.apps.wsh.delivery.gen.closed' OR
2013 l_eventname = 'oracle.apps.wsh.delivery.gen.interfaced') THEN -- Events specific to delivery workflow.
2014 --{
2015 IF l_debug_on THEN
2016 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WF_STD.GET_WF_NAME', WSH_DEBUG_SV.C_PROC_LEVEL);
2017 END IF;
2018
2019 l_wf_pr_name := GET_WF_NAME('DELIVERY',l_entityid);
2020
2021 IF l_debug_on THEN
2022 WSH_DEBUG_SV.log(l_module_name,'DELIVERY: l_wf_pr_name',l_wf_pr_name);
2023 END IF;
2024
2025 --Call wf_engine.event only if any process is associated to the entity
2026 IF l_wf_pr_name IS NOT NULL THEN
2027 --{
2028 IF l_debug_on THEN
2029 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WF_ENGINE.EVENT', WSH_DEBUG_SV.C_PROC_LEVEL);
2030 END IF;
2031
2032 WF_ENGINE.EVENT(itemtype => 'WSHDEL',
2033 itemkey => l_entityid,
2034 process_name => l_wf_pr_name,
2035 event_message => p_event);
2036 ELSE
2037 IF l_debug_on THEN
2038 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WF_STD.GET_WF_NAME', WSH_DEBUG_SV.C_PROC_LEVEL);
2039 END IF;
2040
2041 l_wf_pr_name := GET_WF_NAME('DELIVERY_C',l_entityid);
2042
2043 IF l_debug_on THEN
2044 WSH_DEBUG_SV.log(l_module_name,'DELIVERY_C: l_wf_pr_name',l_wf_pr_name);
2045 END IF;
2046
2047 IF l_wf_pr_name IS NOT NULL THEN
2048 IF l_debug_on THEN
2049 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WF_ENGINE.EVENT', WSH_DEBUG_SV.C_PROC_LEVEL);
2050 END IF;
2051
2052 WF_ENGINE.EVENT(itemtype => 'WSHDEL',
2053 itemkey => l_entityid,
2054 process_name => l_wf_pr_name,
2055 event_message => p_event);
2056 END IF;
2057 --}
2058 END IF;
2059 ELSIF (l_eventname = 'oracle.apps.wsh.trip.gen.initialpickupstopclosed' OR
2060 l_eventname = 'oracle.apps.wsh.trip.gen.ultimatedropoffstopclosed') THEN -- Events specific to Trip workflow
2061
2062 IF l_debug_on THEN
2063 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WF_STD.GET_WF_NAME', WSH_DEBUG_SV.C_PROC_LEVEL);
2064 END IF;
2065
2066 l_wf_pr_name := GET_WF_NAME('TRIP',l_entityid);
2067
2068 IF l_debug_on THEN
2069 WSH_DEBUG_SV.log(l_module_name,'TRIP: l_wf_pr_name',l_wf_pr_name);
2070 END IF;
2071
2072 IF l_wf_pr_name IS NOT NULL THEN
2073 IF l_debug_on THEN
2074 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WF_ENGINE.EVENT', WSH_DEBUG_SV.C_PROC_LEVEL);
2075 END IF;
2076
2077 wf_engine.Event(itemtype => 'WSHTRIP',
2078 itemkey => l_entityid,
2079 process_name => l_wf_pr_name,
2080 event_message => p_event);
2081 END IF;
2082
2083 --}
2084 END IF;
2085
2086 EXCEPTION
2087 WHEN others THEN
2088 IF l_debug_on THEN
2089 WSH_DEBUG_SV.logmsg(l_module_name,'Exception :'||Wf_Core.Error_Name||' Occured', WSH_DEBUG_SV.C_EXCEP_LEVEL);
2090 END IF;
2091
2092 IF (Wf_Core.Error_Name = 'WFENG_EVENT_NOTFOUND') THEN
2093 Wf_Core.Clear;
2094 ELSE
2095 l_raise_exception:=TRUE;
2096 END IF;
2097 END;
2098
2099 ELSE
2100 raise e_entityid_notfound;
2101 END IF; --{ If l_entityid is not nulls
2102
2103 IF l_raise_exception THEN
2104 raise e_event_error;
2105 END IF;
2106
2107 IF l_debug_on THEN
2108 WSH_DEBUG_SV.pop(l_module_name);
2109 END IF;
2110
2111 RETURN 'SUCCESS';
2112
2113 EXCEPTION
2114 WHEN e_entityid_notfound THEN
2115 WF_CORE.CONTEXT('WSH_WF_STD', 'WF_ENGINE_EVENT',p_event.getEventName( ), p_subscription_guid);
2116 WF_EVENT.setErrorInfo(p_event, 'ERROR');
2117
2118 IF l_debug_on THEN
2119 WSH_DEBUG_SV.logmsg(l_module_name,'Associated Entity Id not found. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_EXCEP_LEVEL);
2120 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:E_ENTITYID_NOTFOUND');
2121 END IF;
2122
2123 RETURN 'ERROR';
2124
2125 WHEN e_event_error THEN
2126 WF_CORE.CONTEXT('WSH_WF_STD', 'WF_ENGINE_EVENT',p_event.getEventName( ), p_subscription_guid);
2127 WF_EVENT.setErrorInfo(p_event, 'ERROR');
2128
2129 IF l_debug_on THEN
2130 WSH_DEBUG_SV.logmsg(l_module_name,'Some Entities had errors while raising events. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_EXCEP_LEVEL);
2131 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:E_EVENT_ERROR');
2132 END IF;
2133
2134 RETURN 'ERROR';
2135
2136 WHEN others THEN
2137 WF_CORE.CONTEXT('WSH_WF_STD', 'WF_ENGINE_EVENT',p_event.getEventName( ), p_subscription_guid);
2138 WF_EVENT.setErrorInfo(p_event, 'ERROR');
2139
2140 IF l_debug_on THEN
2141 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2142 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2143 END IF;
2144
2145 RETURN 'ERROR';
2146 END WF_ENGINE_EVENT;
2147
2148 ---------------------------------------------------------------------------------------
2149 FUNCTION Instance_Default_Rule (p_subscription_guid in raw,
2150 p_event in out nocopy WF_EVENT_T) return
2151 VARCHAR2 is
2152 --performance bug 5220516: make this API as efficient as possible
2153 -- by commenting the unnecessary assignment; this variable is
2154 -- used only in the code that is currently commented.
2155 --l_eventname VARCHAR2(240);
2156 /* bugfix 8706771
2157 l_eventkey VARCHAR2(240);
2158 l_entityid VARCHAR2(240);
2159 l_pik_status VARCHAR2(30);
2160 l_svc_status VARCHAR2(30);
2161
2162 CURSOR get_trip(l_wf_item_key IN VARCHAR2) IS
2163 SELECT trip_id
2164 FROM WSH_TRIPS
2165 WHERE wf_item_key = l_wf_item_key;
2166
2167 CURSOR get_trip_del(l_trip_id IN NUMBER) IS
2168 select delivery_id
2169 from WSH_DELIVERY_LEGS wdl,
2170 WSH_TRIP_STOPS wts
2171 where wdl.pick_up_stop_id = wts.stop_id and
2172 wts.trip_id = l_trip_id;
2173
2174 CURSOR get_delivery(l_event_key IN VARCHAR2) IS
2175 SELECT delivery_id
2176 FROM WSH_NEW_DELIVERIES WND,
2177 WSH_TRANSACTIONS_HISTORY WTH
2178 WHERE WTH.event_key = l_event_key and
2179 WTH.entity_type = 'DLVY' and
2180 WTH.entity_number = WND.name;
2181
2182 CURSOR get_act_status(l_eventkey IN VARCHAR2,l_act_name IN VARCHAR2) IS
2183 SELECT activity_status_code
2184 FROM WF_ITEM_ACTIVITY_STATUSES_V
2185 WHERE item_type = 'WSHDEL'
2186 AND item_key = l_eventkey
2187 AND activity_name = l_act_name;
2188
2189 l_return_status VARCHAR2(30);
2190 l_raise_exception BOOLEAN;
2191 e_entityid_notfound EXCEPTION;
2192 e_event_error EXCEPTION;
2193
2194 */ -- Bugfix 8706771
2195 l_debug_on BOOLEAN;
2196 --
2197 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Instance_Default_Rule';
2198 l_return_status VARCHAR2(30);
2199
2200 BEGIN
2201 -- De-supporting the usage of this API code logic due to bug 8706771.
2202 -- Removing all the code inside the begin block and commenting almost all local variables in the declaration part.
2203 -- From now on this API will just redirect the call to the newly created API WSH_WF_STD.WF_ENGINE_EVENT.
2204 -- This API is not removed since the subscription of business event has this one specified.SO instead of making changes there,
2205 -- we are still keeping the skeleton of this API as it is,but redirecting the code logic to new API.
2206 -- For more details refer bug 8706771.
2207
2208 -- Debug Statements
2209 --
2210 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2211 --
2212 IF l_debug_on IS NULL
2213 THEN
2214 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2215 END IF;
2216 --
2217 IF l_debug_on THEN
2218 WSH_DEBUG_SV.push(l_module_name);
2219 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WF_STD.WF_ENGINE_EVENT', WSH_DEBUG_SV.C_PROC_LEVEL);
2220 END IF;
2221
2222 l_return_status := WF_ENGINE_EVENT( p_subscription_guid => p_subscription_guid,
2223 p_event => p_event);
2224 IF l_debug_on THEN
2225 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
2226 WSH_DEBUG_SV.pop(l_module_name);
2227 END IF;
2228
2229 RETURN l_return_status;
2230
2231 EXCEPTION
2232 WHEN OTHERS THEN
2233 IF l_debug_on THEN
2234 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2235 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2236 END IF;
2237 RETURN 'ERROR';
2238 END;
2239 ---------------------------------------------------------------------------------------
2240 -- This procedure sets the global G_RESET_APPS_CONTEXT to TRUE
2241 ---------------------------------------------------------------------------------------
2242 PROCEDURE RESET_APPS_CONTEXT_ON IS
2243 --
2244 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'RESET_APPS_CONTEXT_ON';
2245 l_debug_on BOOLEAN;
2246 --
2247 BEGIN
2248
2249 WSH_WF_STD.G_RESET_APPS_CONTEXT := TRUE;
2250 IF l_debug_on THEN
2251 WSH_DEBUG_SV.push(l_module_name);
2252 WSH_DEBUG_SV.pop(l_module_name);
2253 END IF;
2254
2255 END RESET_APPS_CONTEXT_ON;
2256
2257 ---------------------------------------------------------------------------------------
2258 -- This procedure sets the global G_RESET_APPS_CONTEXT to FALSE
2259 ---------------------------------------------------------------------------------------
2260 PROCEDURE RESET_APPS_CONTEXT_OFF IS
2261 --
2262 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'RESET_APPS_CONTEXT_OFF';
2263 l_debug_on BOOLEAN;
2264 --
2265 BEGIN
2266
2267 WSH_WF_STD.G_RESET_APPS_CONTEXT := FALSE;
2268 IF l_debug_on THEN
2269 WSH_DEBUG_SV.push(l_module_name);
2270 WSH_DEBUG_SV.pop(l_module_name);
2271 END IF;
2272
2273 END RESET_APPS_CONTEXT_OFF;
2274
2275 /* CURRENTLY NOT IN USE
2276 PROCEDURE Get_Carrier(p_del_ids IN WSH_UTIL_CORE.ID_TAB_TYPE,
2277 x_del_old_carrier_ids OUT NOCOPY WSH_UTIL_CORE.ID_TAB_TYPE,
2278 x_return_status OUT NOCOPY VARCHAR2) IS
2279
2280 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_CARRIER';
2281 l_debug_on BOOLEAN;
2282
2283 CURSOR c_tripordel_carrier (p_delivery_id IN NUMBER) IS
2284 SELECT
2285 NVL(wt.carrier_id, wnd.carrier_id) carrier_id
2286 FROM
2287 wsh_trips wt,
2288 wsh_delivery_legs wdl,
2289 wsh_trip_stops wts,
2290 wsh_new_deliveries wnd
2291 WHERE wts.trip_id = wt.trip_id
2292 AND wdl.pick_up_stop_id = wts.stop_id
2293 AND wnd.initial_pickup_location_id = wts.stop_location_id
2294 AND wnd.delivery_id = wdl.delivery_id
2295 AND wnd.delivery_id = p_delivery_id;
2296
2297 CURSOR c_del_carrier (p_delivery_id IN NUMBER) IS
2298 SELECT
2299 carrier_id
2300 FROM
2301 wsh_new_deliveries
2302 WHERE delivery_id = p_delivery_id;
2303
2304 l_carrier_id NUMBER;
2305 l_return_status VARCHAR2(1);
2306
2307 BEGIN
2308
2309 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2310 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2311
2312 IF l_debug_on IS NULL THEN
2313 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2314 END IF;
2315
2316 FOR i IN p_del_ids.FIRST..p_del_ids.LAST
2317 LOOP
2318 --If Trip has a carrier pick it, otherwise get the delivery one.
2319 OPEN c_tripordel_carrier(p_delivery_id => p_del_ids(i));
2320 FETCH c_tripordel_carrier INTO l_carrier_id;
2321 IF c_tripordel_carrier%NOTFOUND THEN
2322 IF l_debug_on THEN
2323 WSH_DEBUG_SV.logmsg(l_module_name,'No Rows returned from Cursor c_tripordel_carrier', WSH_DEBUG_SV.C_STMT_LEVEL);
2324 END IF;
2325 OPEN c_del_carrier(p_delivery_id => p_del_ids(i));
2326 FETCH c_del_carrier INTO l_carrier_id;
2327 CLOSE c_del_carrier;
2328 END IF;
2329 CLOSE c_tripordel_carrier;
2330 x_del_old_carrier_ids(i) := l_carrier_id;
2331 IF l_debug_on THEN
2332 WSH_DEBUG_SV.logmsg(l_module_name,'Delivery ID '||p_del_ids(i)||'has Trip/Delivery carrier '||l_carrier_id, WSH_DEBUG_SV.C_STMT_LEVEL);
2333 END IF;
2334 END LOOP;
2335
2336 EXCEPTION
2337 WHEN OTHERS THEN
2338 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
2339 wsh_util_core.default_handler('WSH_WF_STD.GET_CARRIER');
2340 IF l_debug_on THEN
2341 --{
2342 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2343 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2344 --}
2345 END IF;
2346 END Get_Carrier;
2347
2348 PROCEDURE Handle_Trip_Carriers(p_trip_id IN NUMBER,
2349 p_del_ids IN WSH_UTIL_CORE.ID_TAB_TYPE,
2350 p_del_old_carrier_ids IN WSH_UTIL_CORE.ID_TAB_TYPE,
2351 x_return_status OUT NOCOPY VARCHAR2) IS
2352
2353 l_del_new_carrier_ids WSH_UTIL_CORE.ID_TAB_TYPE;
2354 l_return_status VARCHAR2(1);
2355
2356 BEGIN
2357
2358 Get_Carrier(p_del_ids => p_del_ids,
2359 x_del_old_carrier_ids => l_del_new_carrier_ids,
2360 x_return_status => l_return_status);
2361
2362 FOR i IN p_del_ids.FIRST..p_del_ids.LAST
2363 LOOP
2364 Assign_Unassign_Carrier(p_delivery_id => p_del_ids(i),
2365 p_old_carrier_id => p_del_old_carrier_ids(i),
2366 p_new_carrier_id => l_del_new_carrier_ids(i),
2367 x_return_status => l_return_status);
2368 END LOOP;
2369 END Handle_Trip_Carriers;
2370
2371 PROCEDURE Assign_Unassign_Carrier(p_delivery_id IN NUMBER,
2372 p_old_carrier_id IN NUMBER,
2373 p_new_carrier_id IN NUMBER,
2374 x_return_status OUT NOCOPY VARCHAR2) IS
2375
2376 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Assign_Unassign_Carrier';
2377 l_debug_on BOOLEAN;
2378
2379 l_return_status VARCHAR2(1);
2380 l_wf_rs VARCHAR2(1);
2381
2382 CURSOR c_get_del_info (p_delivery_id IN NUMBER) IS
2383 SELECT
2384 organization_id,
2385 delivery_wf_process
2386 FROM
2387 wsh_new_deliveries
2388 WHERE delivery_id = p_delivery_id;
2389
2390 CURSOR c_get_carrier_name (p_carrier_id IN NUMBER) IS
2391 SELECT
2392 carrier_name
2393 FROM
2394 wsh_carriers_v
2395 WHERE carrier_id = p_carrier_id;
2396
2397 l_org_id NUMBER;
2398 l_del_wf VARCHAR2(30);
2399 l_carrier_name VARCHAR2(360);
2400
2401 BEGIN
2402
2403 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2404
2405 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2406
2407 IF l_debug_on IS NULL THEN
2408 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2409 END IF;
2410
2411 OPEN c_get_del_info(p_delivery_id => p_delivery_id);
2412 FETCH c_get_del_info INTO l_org_id, l_del_wf;
2413 CLOSE c_get_del_info;
2414
2415 IF (l_del_wf = 'R_DEL_FTE_GEN') THEN
2416
2417 IF ((p_old_carrier_id IS NULL) AND (p_new_carrier_id IS NOT NULL)) THEN
2418
2419 --Carrier on the delivery getting assigned for first time.
2420 WSH_WF_STD.RAISE_EVENT(p_entity_type => 'DELIVERY',
2421 p_entity_id => p_delivery_id,
2422 p_event => 'oracle.apps.wsh.delivery.gen.carrierselected',
2423 p_organization_id => l_org_id,
2424 x_return_status => l_wf_rs);
2425
2426 ELSIF ((p_old_carrier_id IS NOT NULL) AND (p_new_carrier_id IS NULL)) THEN
2427
2428 --Carrier on the Delivery getting nulled out/cancelled.
2429 WSH_WF_STD.RAISE_EVENT(p_entity_type => 'DELIVERY',
2430 p_entity_id => p_delivery_id,
2431 p_event => 'oracle.apps.wsh.delivery.gen.carriercancelled',
2432 p_organization_id => l_org_id,
2433 x_return_status => l_wf_rs);
2434
2435 ELSIF ((p_old_carrier_id IS NOT NULL) AND (p_new_carrier_id IS NOT NULL) AND
2436 (p_old_carrier_id <> p_new_carrier_id)) THEN
2437
2438 --Change in carrier from existing one to a new one.
2439 WSH_WF_STD.RAISE_EVENT(p_entity_type => 'DELIVERY',
2440 p_entity_id => p_delivery_id,
2441 p_event => 'oracle.apps.wsh.delivery.gen.carriercancelled',
2442 p_organization_id => l_org_id,
2443 x_return_status => l_wf_rs);
2444
2445 WSH_WF_STD.RAISE_EVENT(p_entity_type => 'DELIVERY',
2446 p_entity_id => p_delivery_id,
2447 p_event => 'oracle.apps.wsh.delivery.gen.carrierselected',
2448 p_organization_id => l_org_id,
2449 x_return_status => l_wf_rs);
2450 END IF;--Check for old and new Carrier_id values.
2451
2452 WF_ENGINE.SetItemAttrNumber(itemtype => 'WSHDEL',
2453 itemkey => p_delivery_id,
2454 aname => 'CARRIER_ID',
2455 avalue => p_new_carrier_id);
2456
2457 OPEN c_get_carrier_name(p_carrier_id => p_new_carrier_id);
2458 FETCH c_get_carrier_name INTO l_carrier_name;
2459 CLOSE c_get_carrier_name;
2460
2461 WF_ENGINE.SetItemAttrText(itemtype => 'WSHDEL',
2462 itemkey => p_delivery_id,
2463 aname => 'CARRIER_NAME',
2464 avalue => l_carrier_name);
2465
2466 END IF; --Check for Delivery Workflow Name.
2467
2468 EXCEPTION
2469 WHEN OTHERS THEN
2470 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
2471 wsh_util_core.default_handler('WSH_WF_STD.Assign_Unassign_Carrier');
2472 IF l_debug_on THEN
2473 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2474 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2475 END IF;
2476 END Assign_Unassign_Carrier;
2477
2478
2479 PROCEDURE Get_Deliveries(p_trip_id IN NUMBER,
2480 x_del_ids OUT NOCOPY WSH_UTIL_CORE.ID_TAB_TYPE,
2481 x_return_status OUT NOCOPY VARCHAR2) IS
2482
2483 CURSOR c_dels(p_trip_id IN NUMBER) IS
2484 SELECT
2485 wnd.delivery_id
2486 FROM
2487 wsh_new_deliveries wnd,
2488 wsh_delivery_legs wdl,
2489 wsh_trip_stops wts
2490 WHERE
2491 wts.trip_id = p_trip_id AND
2492 wts.stop_id = wdl.pick_up_stop_id AND
2493 wnd.initial_pickup_location_id = wts.stop_location_id AND
2494 wdl.delivery_id = wnd.delivery_id;
2495
2496 BEGIN
2497
2498 OPEN c_dels(p_trip_id => p_trip_id);
2499 FETCH c_dels BULK COLLECT INTO x_del_ids;
2500 CLOSE c_dels;
2501
2502 END Get_Deliveries;
2503 */
2504
2505 END WSH_WF_STD;
[Home] [Help]