[Home] [Help]
PACKAGE BODY: APPS.WSH_WF_STD
Source
1 PACKAGE BODY WSH_WF_STD AS
2 /* $Header: WSHWSTDB.pls 120.11.12000000.2 2007/01/23 18:40:15 rlanka 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 IF ( nvl(l_wh_type,'@') = 'TPW') THEN
816 -- l_tpw_org := 'Y';
817 x_wf_process := G_NO_WORKFLOW;
818 IF l_debug_on THEN
819 WSH_DEBUG_SV.pop(l_module_name);
820 END IF;
821 RETURN;
822 END IF;
823
824
825 -- CHECK IF PICKING REQUIRED. Check for these conditions:
826 -- 1. OKE lines - pickable flag is always 'N' (source_code checking not required)
827 -- 2. TPW lines - pickable flag is sometimes 'Y' hence the check for TPW
828 -- 3. Non transactable lines - pickable flag is not 'Y'
829 /*SR
830 OPEN get_non_pickable_count;
831 FETCH get_non_pickable_count into l_non_pick_eligible_count;
832 CLOSE get_non_pickable_count ;
833
834 IF (l_non_pick_eligible_count > 0 or l_tpw_org = 'Y') THEN
835 l_picking_req := 'N';
836 ELSE
837 l_picking_req := 'Y';
838 END IF;
839
840 IF (l_fte_installed = 'N' AND l_screening_req = 'N' AND l_tpw_org = 'N' AND l_picking_req = 'Y') THEN
841 x_wf_process := 'R_DEL_GEN';
842 ELSIF (l_fte_installed = 'N' AND l_screening_req = 'N' AND l_tpw_org = 'N' AND l_picking_req = 'N') THEN
843 x_wf_process:= 'R_DEL_SHP';
844 ELSIF (l_fte_installed = 'N' AND l_screening_req = 'Y' AND l_tpw_org = 'N' AND l_picking_req = 'Y') THEN
845 x_wf_process:= 'R_DEL_ITM_GEN';
846 ELSIF (l_fte_installed = 'N' AND l_screening_req = 'Y' AND l_tpw_org = 'N' AND l_picking_req = 'N') THEN
847 x_wf_process:= 'R_DEL_ITM_SHP';
848 ELSIF (l_fte_installed = 'Y' AND l_screening_req = 'N' AND l_tpw_org = 'N' AND l_picking_req = 'Y') THEN
849 x_wf_process:= 'R_DEL_FTE_GEN';
850 ELSIF (l_fte_installed = 'Y' AND l_screening_req = 'N' AND l_tpw_org = 'N' AND l_picking_req = 'N') THEN
851 x_wf_process:= 'R_DEL_FTE_SHP';
852 ELSIF (l_fte_installed = 'Y' AND l_screening_req = 'N' AND l_tpw_org = 'Y' AND l_picking_req = 'N') THEN
853 x_wf_process:= 'R_DEL_FTE_TPW';
854 ELSIF (l_fte_installed = 'Y' AND l_screening_req = 'Y' AND l_tpw_org = 'N' AND l_picking_req = 'Y') THEN
855 x_wf_process:= 'R_DEL_FTE_ITM_GEN';
856 ELSIF (l_fte_installed = 'Y' AND l_screening_req = 'Y' AND l_tpw_org = 'N' AND l_picking_req = 'N') THEN
857 x_wf_process:= 'R_DEL_FTE_ITM_SHP';
858 ELSIF (l_fte_installed = 'Y' AND l_screening_req = 'Y' AND l_tpw_org = 'Y' AND l_picking_req = 'N') THEN
859 x_wf_process:= 'R_DEL_FTE_ITM_TPW';
860 END IF;
861 SR*/
862
863 x_wf_process := 'R_DEL_GEN';
864 --Currenly not in use.
865 /*IF ( l_fte_installed = 'Y') THEN
866 x_wf_process := 'R_DEL_FTE_GEN';
867 ELSE
868 x_wf_process := 'R_DEL_GEN';
869 END IF;*/
870
871 END IF;
872
873 IF (x_wf_process is null) THEN
874 RAISE e_process_not_found;
875 END IF;
876
877 OPEN get_org_code;
878 FETCH get_org_code into l_organization_code;
879 CLOSE get_org_code ;
880
881 IF l_debug_on THEN
882 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WF_STD.GET_CUSTOM_WF_PROCESS',
883 WSH_DEBUG_SV.C_PROC_LEVEL);
884 END IF;
885
886 Get_Custom_Wf_Process(p_wf_process => x_wf_process,
887 p_org_code => l_organization_code,
888 x_wf_process => l_custom_process_name,
889 x_return_status => l_return_status);
890 IF l_debug_on THEN
891 WSH_DEBUG_SV.log(l_module_name,'l_custom_process_name',l_custom_process_name);
892 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
893 END IF;
894
895 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
896 x_return_status := l_return_status;
897 IF l_debug_on THEN
898 WSH_DEBUG_SV.pop(l_module_name);
899 END IF;
900 RETURN;
901 ELSE
902 x_wf_process:=l_custom_process_name;
903 END IF;
904 IF l_debug_on THEN
905 WSH_DEBUG_SV.pop(l_module_name);
906 END IF;
907
908
909 EXCEPTION
910 WHEN e_invalid_org THEN
911 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR ;
912 IF l_debug_on THEN
913 WSH_DEBUG_SV.logmsg(l_module_name,'Invalid Organization Id passed',WSH_DEBUG_SV.C_EXCEP_LEVEL);
914 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:E_INVALID_ORG');
915 END IF;
916 WHEN e_process_not_found 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,'Process not found for the current Setup parameters',
920 WSH_DEBUG_SV.C_EXCEP_LEVEL);
921 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:E_PROCESS_NOT_FOUND');
922 END IF;
923 WHEN others THEN
924 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
925 IF l_debug_on THEN
926 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
927 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
928 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
929 END IF;
930
931 END process_selector;
932
933 ---------------------------------------------------------------------------------------
934 --
935 -- Procedure: Raise_Event
936 -- Parameters: p_entity_type - 'TRIP','DELIVERY','LINE','STOP'
937 -- p_entity_id - TRIP_ID, DELIVERY_ID, DELIVERY_DETAIL_ID,STOP_ID
938 -- p_event - The Event to be raised
939 -- p_event_data - Optional Event data to be sent while raising the event
940 -- p_parameters - Optional Parameters to be sent while raising the event
941 -- p_send_date - Optional date to indicate when the event should
942 -- become available for subscription processing.
943 -- p_organization_id - The Organization Id
944 --
945 -- x_return_status - Returns WSH_UTIL_CORE.G_RET_STS_SUCCESS if Success
946 --
947 -- Description: This Procedure raises the event in the following scenario
948 -- 1) If a Process already exists for this Entity
949 -- 2) If no process exists, checks the Global and Shipping parameters
950 -- for raising events and raises accordingly
951 ---------------------------------------------------------------------------------------
952
953 PROCEDURE Raise_Event(
954 p_entity_type IN VARCHAR2,
955 p_entity_id IN VARCHAR2,
956 p_event IN VARCHAR2,
957 p_event_data IN CLOB DEFAULT NULL,
958 p_parameters IN wf_parameter_list_t DEFAULT NULL,
959 p_send_date IN DATE DEFAULT SYSDATE,
960 p_organization_id IN NUMBER DEFAULT NULL,
961 x_return_status OUT NOCOPY VARCHAR2) IS
962
963
964 l_param_info WSH_SHIPPING_PARAMS_PVT.Parameter_Rec_Typ;
965 l_global_parameters WSH_SHIPPING_PARAMS_PVT.Global_Parameters_Rec_Typ;
966 l_parameters WF_PARAMETER_LIST_T;
967 l_raise_event BOOLEAN;
968 l_wf_process_exists VARCHAR2(1);
969 l_return_status VARCHAR2(1);
970
971 e_org_required EXCEPTION;
972
973 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'RAISE_EVENT';
974 l_debug_on BOOLEAN;
975
976
977 BEGIN
978
979 -- Debug Statements
980 --
981 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
982 --
983 IF l_debug_on IS NULL
984 THEN
985 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
986 END IF;
987 --
988 IF l_debug_on THEN
989 WSH_DEBUG_SV.push(l_module_name);
990 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_TYPE',p_entity_type);
991 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_ID',p_entity_id);
992 WSH_DEBUG_SV.log(l_module_name,'P_EVENT',p_event);
993 WSH_DEBUG_SV.log(l_module_name,'P_SEND_DATE',p_send_date);
994 WSH_DEBUG_SV.log(l_module_name,'P_ORGANIZATION_ID',p_organization_id);
995 END IF;
996
997 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
998 l_raise_event := FALSE;
999
1000 IF (p_entity_type IN ('DELIVERY','LINE') AND p_organization_id IS NULL ) THEN
1001 raise e_org_required;
1002 END IF;
1003
1004 -- Raise event if a workflow process exists
1005 IF (p_entity_type = 'TRIP' or p_entity_type = 'DELIVERY') THEN
1006
1007 IF l_debug_on THEN
1008 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WF_STD.CHECK_WF_EXISTS',
1009 WSH_DEBUG_SV.C_PROC_LEVEL);
1010 END IF;
1011
1012 check_wf_exists(p_entity_type => p_entity_type,
1013 p_entity_id => p_entity_id,
1014 x_wf_process_exists => l_wf_process_exists,
1015 x_return_status => l_return_status);
1016
1017 IF l_debug_on THEN
1018 WSH_DEBUG_SV.log(l_module_name,'l_wf_process_exists',l_wf_process_exists);
1019 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1020 END IF;
1021
1022 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1023 x_return_status := l_return_status;
1024 IF l_debug_on THEN
1025 WSH_DEBUG_SV.pop(l_module_name);
1026 END IF;
1027 RETURN;
1028 END IF;
1029
1030 IF (l_wf_process_exists = 'Y') THEN
1031 l_raise_event := TRUE;
1032 END IF;
1033 END IF;
1034
1035 -- Get Global event parameters and raise event accordingly
1036 IF ( NOT l_raise_event) THEN
1037 IF l_debug_on THEN
1038 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_SHIPPING_PARAMS_PVT.GET_GLOBAL_PARAMETERS',
1039 WSH_DEBUG_SV.C_PROC_LEVEL);
1040 END IF;
1041
1042 WSH_SHIPPING_PARAMS_PVT.Get_Global_Parameters(x_Param_Info => l_global_parameters,
1043 x_return_status => l_return_status);
1044
1045 IF l_debug_on THEN
1046 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1047 END IF;
1048
1049 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1050 x_return_status := l_return_status;
1051 IF l_debug_on THEN
1052 WSH_DEBUG_SV.pop(l_module_name);
1053 END IF;
1054 RETURN;
1055 END IF;
1056
1057 IF ((p_entity_type = 'TRIP' OR p_entity_type = 'STOP')
1058 AND NVL(l_global_parameters.RAISE_BUSINESS_EVENTS,'N')='Y') THEN
1059 l_raise_event := TRUE;
1060 END IF;
1061 END IF;
1062
1063 -- Get Shipping event parameters and raise event accordingly
1064 IF ( NOT l_raise_event AND p_organization_id is not null) THEN
1065 IF l_debug_on THEN
1066 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_SHIPPING_PARAMS_PVT.GET',
1067 WSH_DEBUG_SV.C_PROC_LEVEL);
1068 END IF;
1069
1070 WSH_SHIPPING_PARAMS_PVT.Get(
1071 p_organization_id => p_organization_id,
1072 x_param_info => l_param_info,
1073 x_return_status => l_return_status);
1074
1075 IF l_debug_on THEN
1076 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1077 END IF;
1078
1079 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1080 x_return_status := l_return_status;
1081 IF l_debug_on THEN
1082 WSH_DEBUG_SV.pop(l_module_name);
1083 END IF;
1084 RETURN;
1085 END IF;
1086
1087 IF (NVL(l_param_info.RAISE_BUSINESS_EVENTS,'N')='Y' and NVL(l_global_parameters.RAISE_BUSINESS_EVENTS,'N')='Y')
1088 THEN
1089 l_raise_event := TRUE;
1090 END IF;
1091 END IF;
1092
1093 IF (l_raise_event) THEN
1094 IF (p_parameters IS NOT NULL) THEN
1095 l_parameters := p_parameters;
1096 END IF;
1097
1098 WF_EVENT.AddParameterToList (p_name => p_entity_type || '_ID',
1099 p_value => p_entity_id,
1100 p_parameterlist => l_parameters);
1101 WF_EVENT.AddParameterToList (p_name => 'SEND_DATE',
1102 p_value => p_send_date,
1103 p_parameterlist => l_parameters);
1104
1105 WF_EVENT.raise(
1106 p_event_name => p_event,
1107 p_event_key => p_entity_id,
1108 p_event_data => p_event_data,
1109 p_parameters => l_parameters,
1110 p_send_date => p_send_date);
1111
1112 /* This Procedure Call was just added to test business events.
1113
1114 Test_Events(p_entity_type => p_entity_type,
1115 p_entity_id => p_entity_id,
1116 p_send_date => p_send_date,
1117 p_event => p_event);
1118
1119 */
1120 END IF;
1121
1122 IF l_debug_on THEN
1123 WSH_DEBUG_SV.pop(l_module_name);
1124 END IF;
1125
1126 EXCEPTION
1127 WHEN e_org_required THEN
1128 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR ;
1129 IF l_debug_on THEN
1130 WSH_DEBUG_SV.logmsg(l_module_name,'Organization Id Required.Oracle error message is '||
1131 SQLERRM,WSH_DEBUG_SV.C_EXCEP_LEVEL);
1132 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:E_ORG_REQUIRED');
1133 END IF;
1134
1135 WHEN others THEN
1136 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1137 IF l_debug_on THEN
1138 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
1139 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1140 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1141 END IF;
1142
1143 END Raise_Event;
1144
1145 ---------------------------------------------------------------------------------------
1146 -- Procedure: confirm_start_wf_process
1147 -- Parameters: p_entity_type - 'TRIP','DELIVERY'
1148 -- p_organization_id - The Organization Id
1149 --
1150 -- x_start_wf_process - Returns 'Y' if process can be started, else 'N'
1151 -- x_return_status - Returns WSH_UTIL_CORE.G_RET_STS_SUCCESS if Success
1152 --
1153 -- Description: This Procedure obtains the Global and Shipping parameter
1154 -- values for 'Enable Tracking Workflows' and determines
1155 -- if a process can be started by,
1156 -- Global-TW Shipping-TW Eligible Workflow Entity
1157 -- --------- ----------- -------------------------
1158 -- None Delivery None
1159 -- None None None
1160 -- Trip Delivery Trip
1161 -- Trip None Trip
1162 -- Delivery Delivery Delivery
1163 -- Delivery None None
1164 -- Both Delivery Both
1165 -- Both None Trip
1166 ---------------------------------------------------------------------------------------
1167
1168 PROCEDURE confirm_start_wf_process(
1169 p_entity_type IN VARCHAR2,
1170 p_organization_id IN NUMBER,
1171 x_start_wf_process OUT NOCOPY VARCHAR2,
1172 x_return_status OUT NOCOPY VARCHAR2) IS
1173
1174 l_param_info WSH_SHIPPING_PARAMS_PVT.Parameter_Rec_Typ;
1175 l_global_parameters WSH_SHIPPING_PARAMS_PVT.Global_Parameters_Rec_Typ;
1176
1177 l_global_enable_tracking_wfs VARCHAR2(30);
1178 l_shipping_enable_tracking_wfs VARCHAR2(30);
1179
1180 l_return_status VARCHAR2(1);
1181
1182 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CONFIRM_START_WF_PROCESS';
1183 l_debug_on BOOLEAN;
1184
1185
1186 BEGIN
1187
1188 -- Debug Statements
1189 --
1190 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1191 --
1192 IF l_debug_on IS NULL
1193 THEN
1194 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1195 END IF;
1196 --
1197 IF l_debug_on THEN
1198 WSH_DEBUG_SV.push(l_module_name);
1199 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_TYPE',p_entity_type);
1200 WSH_DEBUG_SV.log(l_module_name,'P_ORGANIZATION_ID',p_organization_id);
1201 END IF;
1202
1203 x_start_wf_process := 'N';
1204 x_return_status:=WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1205
1206 -- Get Global and Shipping event parameters and raise event accordingly
1207
1208 IF (p_entity_type <> 'TRIP') THEN
1209
1210 IF l_debug_on THEN
1211 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_SHIPPING_PARAMS_PVT.GET',
1212 WSH_DEBUG_SV.C_PROC_LEVEL);
1213 END IF;
1214
1215 WSH_SHIPPING_PARAMS_PVT.Get(
1216 p_organization_id => p_organization_id,
1217 x_param_info => l_param_info,
1218 x_return_status => l_return_status);
1219 IF l_debug_on THEN
1220 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1221 END IF;
1222
1223 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1224 x_return_status := l_return_status;
1225 IF l_debug_on THEN
1226 WSH_DEBUG_SV.pop(l_module_name);
1227 END IF;
1228 RETURN;
1229 ELSE
1230 l_shipping_enable_tracking_wfs:=NVL(l_param_info.ENABLE_TRACKING_WFS,'NONE');
1231 END IF;
1232 END IF;
1233
1234 IF l_debug_on THEN
1235 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_SHIPPING_PARAMS_PVT.GET_GLOBAL_PARAMETERS',
1236 WSH_DEBUG_SV.C_PROC_LEVEL);
1237 END IF;
1238
1239 WSH_SHIPPING_PARAMS_PVT.Get_Global_Parameters(x_Param_Info => l_global_parameters,
1240 x_return_status => l_return_status);
1241
1242 IF l_debug_on THEN
1243 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1244 END IF;
1245
1246
1247 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1248 x_return_status := l_return_status;
1249 IF l_debug_on THEN
1250 WSH_DEBUG_SV.pop(l_module_name);
1251 END IF;
1252 RETURN;
1253 ELSE
1254 l_global_enable_tracking_wfs:= NVL(l_global_parameters.ENABLE_TRACKING_WFS,'NONE');
1255 END IF;
1256
1257
1258 IF (l_global_enable_tracking_wfs = 'NONE') THEN
1259 IF l_debug_on THEN
1260 WSH_DEBUG_SV.pop(l_module_name);
1261 END IF;
1262 RETURN;
1263 ELSIF ( (l_global_enable_tracking_wfs = 'TRIP' or l_global_enable_tracking_wfs = 'BOTH') and p_entity_type =
1264 'TRIP') THEN
1265 x_start_wf_process := 'Y';
1266 IF l_debug_on THEN
1267 WSH_DEBUG_SV.pop(l_module_name);
1268 END IF;
1269 RETURN;
1270 ELSIF ( (l_global_enable_tracking_wfs = 'DELIVERY' or l_global_enable_tracking_wfs = 'BOTH') and
1271 l_shipping_enable_tracking_wfs = 'DELIVERY' and p_entity_type = 'DELIVERY' ) THEN
1272 x_start_wf_process := 'Y';
1273 IF l_debug_on THEN
1274 WSH_DEBUG_SV.pop(l_module_name);
1275 END IF;
1276 RETURN;
1277 END IF;
1278 IF l_debug_on THEN
1279 WSH_DEBUG_SV.pop(l_module_name);
1280 END IF;
1281
1282 EXCEPTION
1283 WHEN others THEN
1284 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1285 IF l_debug_on THEN
1286 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
1287 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1288 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1289 END IF;
1290 END confirm_start_wf_process;
1291
1292 ---------------------------------------------------------------------------------------
1293 -- Procedure: Check_Wf_Exists
1294 -- Parameters: p_entity_type - 'TRIP','DELIVERY','DELIVERY_C'(controlling)
1295 -- p_entity_id - TRIP_ID or DELIVERY_ID
1296 --
1297 -- x_wf_process_exists - Returns 'Y' if Wf exists, else 'N'
1298 -- IF DELIVERY_C then returns 'Y' only if Cntll wf exists
1299 -- x_return_status - Returns WSH_UTIL_CORE.G_RET_STS_SUCCESS if Success
1300 --
1301 -- Description: This Procedure checks from WSH_NEW_DELIVERIES or WSH_TRIPS
1302 -- if a workflow process has been started for this entity.
1303 ---------------------------------------------------------------------------------------
1304
1305 PROCEDURE Check_Wf_Exists(
1306 p_entity_type IN VARCHAR2,
1307 p_entity_id IN NUMBER,
1308 x_wf_process_exists OUT NOCOPY VARCHAR2,
1309 x_return_status OUT NOCOPY VARCHAR2) IS
1310
1311 CURSOR get_trip_wf_name(l_trip_id IN NUMBER) IS
1312 select trip_wf_process
1313 from wsh_trips where trip_id=l_trip_id;
1314
1315 CURSOR get_delviery_wf_name(l_delivery_id IN NUMBER) IS
1316 select delivery_wf_process,delivery_scpod_wf_process
1317 from wsh_new_deliveries where delivery_id = l_delivery_id;
1318
1319 l_process_name VARCHAR2(30);
1320 l_scpod_process_name VARCHAR2(30);
1321 e_invalid_type EXCEPTION;
1322
1323 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CHECK_WF_EXISTS';
1324 l_debug_on BOOLEAN;
1325
1326
1327 BEGIN
1328
1329 -- Debug Statements
1330 --
1331 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1332 --
1333 IF l_debug_on IS NULL
1334 THEN
1335 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1336 END IF;
1337 --
1338 IF l_debug_on THEN
1339 WSH_DEBUG_SV.push(l_module_name);
1340 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_TYPE',p_entity_type);
1341 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_ID',p_entity_id);
1342 END IF;
1343
1344 x_return_status:=WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1345
1346 -- Get the Process name from Entity Tables
1347 IF (p_entity_type = 'TRIP') THEN
1348 OPEN get_trip_wf_name(p_entity_id);
1349 FETCH get_trip_wf_name into l_process_name;
1350 CLOSE get_trip_wf_name ;
1351 ELSIF (p_entity_type IN ('DELIVERY','DELIVERY_C')) THEN
1352 OPEN get_delviery_wf_name(p_entity_id);
1353 FETCH get_delviery_wf_name into l_process_name,l_scpod_process_name;
1354 CLOSE get_delviery_wf_name ;
1355 ELSE
1356 RAISE e_invalid_type;
1357 END IF;
1358
1359 IF (p_entity_type = 'DELIVERY_C') THEN
1360 IF (l_scpod_process_name is null) THEN
1361 x_wf_process_exists:='N';
1362 ELSE
1363 x_wf_process_exists:='Y';
1364 END IF;
1365 IF l_debug_on THEN
1366 WSH_DEBUG_SV.pop(l_module_name);
1367 END IF;
1368 RETURN;
1369 END IF;
1370
1371 IF (l_process_name is null AND l_scpod_process_name is null) THEN
1372 x_wf_process_exists:='N';
1373 ELSE
1374 x_wf_process_exists:='Y';
1375 END IF;
1376
1377 IF l_debug_on THEN
1378 WSH_DEBUG_SV.pop(l_module_name);
1379 END IF;
1380
1381 EXCEPTION
1382 WHEN e_invalid_type THEN
1383 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR ;
1384 IF l_debug_on THEN
1385 WSH_DEBUG_SV.logmsg(l_module_name,'Invalid Entity type passed.Oracle error message is '||
1386 SQLERRM,WSH_DEBUG_SV.C_EXCEP_LEVEL);
1387 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:E_INVALID_TYPE');
1388 END IF;
1389 WHEN no_data_found THEN
1390 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR ;
1391 IF l_debug_on THEN
1392 WSH_DEBUG_SV.logmsg(l_module_name,'No record found for the entity.Oracle error message is '||
1393 SQLERRM,WSH_DEBUG_SV.C_EXCEP_LEVEL);
1394 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:NO_DATA_FOUND');
1395 END IF;
1396 WHEN others THEN
1397 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1398 IF l_debug_on THEN
1399 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
1400 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1401 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1402 END IF;
1403
1404 END check_wf_exists;
1405 ---------------------------------------------------------------------------------------
1406
1407 FUNCTION Wf_Exists(p_entity_type IN VARCHAR2,
1408 p_entity_id IN NUMBER) RETURN
1409 BOOLEAN IS
1410 l_wf_process_exists VARCHAR2(1);
1411 l_return_status VARCHAR2(1);
1412 BEGIN
1413
1414 Check_Wf_Exists(p_entity_type => p_entity_type,
1415 p_entity_id => p_entity_id,
1416 x_wf_process_exists => l_wf_process_exists,
1417 x_return_status => l_return_status);
1418 IF (l_wf_process_exists = 'Y') THEN
1419 RETURN TRUE;
1420 ELSE
1421 RETURN FALSE;
1422 END IF;
1423 END Wf_Exists;
1424 ---------------------------------------------------------------------------------------
1425 -- Procedure: Get_Custom_Wf_Process
1426 -- Parameters: p_wf_process - The Process selected for the enity
1427 -- p_org_code - The organization code
1428 --
1429 -- x_wf_process - Returns the custom process name specified
1430 -- with the lookups else the orginial process
1431 -- x_return_status - Returns WSH_UTIL_CORE.G_RET_STS_SUCCESS if Success
1432 --
1433 -- Description: This Procedure queries from the WSH_LOOKUPS for any custom
1434 -- process name specified by the User through the lookups for
1435 -- a particular process else returns the original process
1436 ---------------------------------------------------------------------------------------
1437
1438 PROCEDURE Get_Custom_Wf_Process(
1439 p_wf_process IN VARCHAR2,
1440 p_org_code IN VARCHAR2,
1441 x_wf_process OUT NOCOPY VARCHAR2,
1442 x_return_status OUT NOCOPY VARCHAR2) IS
1443 l_custom_process VARCHAR2(80);
1444
1445 CURSOR get_custom_process IS
1446 select meaning
1447 from WSH_LOOKUPS where lookup_type = p_wf_process and lookup_code = p_org_code
1448 and sysdate between nvl(start_date_active,sysdate) and nvl(end_date_active,sysdate)
1449 and enabled_flag='Y';
1450
1451 CURSOR get_gen_custom_process IS
1452 select meaning
1453 from WSH_LOOKUPS where lookup_type = p_wf_process and lookup_code = 'ALL'
1454 and sysdate between nvl(start_date_active,sysdate) and nvl(end_date_active,sysdate)
1455 and enabled_flag='Y';
1456
1457 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_CUSTOM_WF_PROCESS';
1458 l_debug_on BOOLEAN;
1459
1460
1461 BEGIN
1462
1463 -- Debug Statements
1464 --
1465 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1466 --
1467 IF l_debug_on IS NULL
1468 THEN
1469 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1470 END IF;
1471 --
1472 IF l_debug_on THEN
1473 WSH_DEBUG_SV.push(l_module_name);
1474 WSH_DEBUG_SV.log(l_module_name,'P_WF_PROCESS',p_wf_process);
1475 WSH_DEBUG_SV.log(l_module_name,'P_ORG_CODE',p_org_code);
1476 END IF;
1477
1478 x_return_status:=WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1479
1480 OPEN get_custom_process;
1481 FETCH get_custom_process into l_custom_process;
1482
1483 IF get_custom_process%NOTFOUND THEN
1484 OPEN get_gen_custom_process;
1485 FETCH get_gen_custom_process INTO l_custom_process;
1486 IF get_gen_custom_process%NOTFOUND THEN
1487 x_wf_process := p_wf_process;
1488 RETURN;
1489 END IF;
1490 CLOSE get_gen_custom_process;
1491 END IF ;
1492 CLOSE get_custom_process ;
1493
1494 IF (l_custom_process is not null) THEN
1495 x_wf_process := l_custom_process;
1496 END IF;
1497
1498 IF l_debug_on THEN
1499 WSH_DEBUG_SV.pop(l_module_name);
1500 END IF;
1501
1502 EXCEPTION
1503 WHEN others THEN
1504 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1505 IF l_debug_on THEN
1506 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured.
1507 Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1508 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1509 END IF;
1510 END Get_Custom_Wf_Process;
1511 ---------------------------------------------------------------------------------------
1512 -- Procedure: Purge_Entity
1513 -- Parameters: p_entity_type - TRIP / DELIVERY
1514 -- p_entity_ids - Ids of entities to be purged
1515 -- p_action - CLOSE / PURGE
1516 -- p_docommit - Specify TRUE/FALSE to indicate whether
1517 -- to commit data while purging.
1518 --
1519 -- x_purged_count - No. of entities successfully purged/closed
1520 -- x_return_status - Returns WSH_UTIL_CORE.G_RET_STS_SUCCESS if Success
1521 --
1522 -- Description: This Procedure finds out the Item status for every entity id.
1523 -- If it is complete then purges it, if not complete aborts the process
1524 -- and then purges the item.
1525 -- If p_action is CLOSE alone then aborts the process if not completed.
1526 ---------------------------------------------------------------------------------------
1527 Procedure Purge_Entity(
1528 p_entity_type IN VARCHAR2,
1529 p_entity_ids IN WSH_UTIL_CORE.column_tab_type,
1530 p_action IN VARCHAR2 DEFAULT 'PURGE',
1531 p_docommit IN BOOLEAN DEFAULT FALSE,
1532 x_success_count OUT NOCOPY NUMBER,
1533 x_return_status OUT NOCOPY VARCHAR2) IS
1534
1535 l_wf_status VARCHAR2(30); -- COMPLETE/ERROR/SUSPENDED
1536 l_result VARCHAR2(30);
1537 l_itemtype VARCHAR2(30);
1538
1539 l_suc_entity_ids WSH_UTIL_CORE.column_tab_type;
1540
1541 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PURGE_ENTITY';
1542 l_debug_on BOOLEAN;
1543
1544 BEGIN
1545
1546 -- Debug Statements
1547 --
1548 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1549 --
1550 IF l_debug_on IS NULL
1551 THEN
1552 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1553 END IF;
1554 --
1555 IF l_debug_on THEN
1556 WSH_DEBUG_SV.push(l_module_name);
1557 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_TYPE',p_entity_type);
1558 WSH_DEBUG_SV.log(l_module_name,'P_ACTION',p_action);
1559 WSH_DEBUG_SV.log(l_module_name,'P_DOCOMMIT',p_docommit);
1560 END IF;
1561
1562 x_return_status:=WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1563 SAVEPOINT PURGE_ENTITY_UPDATE;
1564
1565 IF (p_entity_type = 'TRIP') THEN
1566 l_itemtype := 'WSHTRIP';
1567 ELSE
1568 l_itemtype := 'WSHDEL';
1569 END IF;
1570
1571 FOR i IN 1..p_entity_ids.COUNT LOOP
1572 IF ( Wf_Exists(p_entity_type, p_entity_ids(i))) THEN
1573 BEGIN
1574 --
1575 IF l_debug_on THEN
1576 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_IDS(I)',p_entity_ids(i));
1577 END IF;
1578 --
1579 WF_ENGINE.ItemStatus(itemtype => l_itemtype,
1580 itemkey => p_entity_ids(I),
1581 status => l_wf_status,
1582 result => l_result);
1583
1584 IF (l_wf_status <> 'COMPLETE') THEN
1585 WF_ENGINE.AbortProcess(itemtype => l_itemtype,
1586 itemkey => p_entity_ids(i)); -- ,l_wf_end_result);
1587 IF (p_action = 'PURGE') THEN
1588 WF_PURGE.Items(itemtype => l_itemtype,
1589 itemkey => p_entity_ids(i),
1590 docommit => p_docommit,
1591 force => TRUE);
1592 WF_PURGE.Total(itemtype => l_itemtype,
1593 itemkey => p_entity_ids(i),
1594 docommit => p_docommit);
1595 END IF;
1596 ELSE
1597 IF (p_action = 'PURGE') THEN
1598 WF_purge.Items(itemtype => l_itemtype,
1599 itemkey => p_entity_ids(i),
1600 docommit => p_docommit);
1601 END IF;
1602 END IF;
1603
1604 l_suc_entity_ids(l_suc_entity_ids.count + 1) := p_entity_ids(i);
1605 EXCEPTION
1606 WHEN OTHERS THEN
1607 IF l_debug_on THEN
1608 WSH_DEBUG_SV.logmsg(l_module_name,'Error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_EXCEP_LEVEL);
1609 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1610 END IF;
1611 END;
1612 END IF;
1613 END LOOP;
1614
1615 IF (p_entity_type = 'DELIVERY') THEN
1616 FORALL i IN 1..l_suc_entity_ids.count
1617 UPDATE WSH_NEW_DELIVERIES
1618 SET delivery_wf_process=NULL,
1619 delivery_scpod_wf_process=NULL,
1620 del_wf_intransit_attr=NULL,
1621 del_wf_interface_attr=NULL,
1622 del_wf_close_attr=NULL
1623 WHERE delivery_id = l_suc_entity_ids(i);
1624 ELSIF (p_entity_type = 'TRIP') THEN
1625 FORALL i IN 1..l_suc_entity_ids.count
1626 UPDATE WSH_TRIPS
1627 SET trip_wf_process=NULL
1628 WHERE trip_id=l_suc_entity_ids(i);
1629 END IF;
1630
1631 x_success_count := l_suc_entity_ids.count;
1632 /*
1633 IF (l_suc_entity_ids.count = 0) THEN
1634 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR ;
1635 ELSIF (p_entity_ids.count <> l_suc_entity_ids.count) THEN
1636 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING ;
1637 ELSIF (p_entity_ids.count = l_suc_entity_ids.count) THEN
1638 x_return_status:=WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1639 END IF;
1640 */
1641 IF l_debug_on THEN
1642 WSH_DEBUG_SV.pop(l_module_name);
1643 END IF;
1644
1645
1646 EXCEPTION
1647 WHEN others THEN
1648 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1649 IF l_debug_on THEN
1650 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1651 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1652 END IF;
1653
1654 END Purge_Entity;
1655
1656
1657 ---------------------------------------------------------------------------------------
1658 --
1659 -- Procedure: Log_Wf_Exception
1660 -- Parameters: p_entity_type - 'DELIVERY',
1661 -- p_entity_id - DELIVERY_ID
1662 -- p_ship_from_location_id - The Ship from Location Id
1663 -- p_exception_name WSH_LAUNCH_WF_FAILED
1664 -- WSH_DEL_SCPOD_PURGED
1665 --
1666 -- x_return_status - Returns WSH_UTIL_CORE.G_RET_STS_SUCCESS if Success
1667 --
1668 -- Description: This Procedure logs an exception against the specified Entity
1669 --
1670 --
1671 ---------------------------------------------------------------------------------------
1672
1673 PROCEDURE Log_Wf_Exception(p_entity_type IN VARCHAR2,
1674 p_entity_id IN NUMBER,
1675 p_ship_from_location_id in NUMBER DEFAULT NULL,
1676 p_logging_entity IN VARCHAR2,
1677 p_exception_name in VARCHAR2,
1678 x_return_status out nocopy VARCHAR2) IS
1679
1680 l_exception_name varchar2(30);
1681 l_msg varchar2(2000);
1682 l_exception_msg_count NUMBER;
1683 l_exception_msg_data varchar2(2000);
1684 l_exception_id NUMBER := NULL;
1685 l_ship_from_location_id NUMBER;
1686
1687 --
1688 l_debug_on BOOLEAN;
1689 --
1690 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOG_WF_EXCEPTION';
1691
1692 BEGIN
1693
1694 --
1695 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1696 --
1697 IF l_debug_on IS NULL THEN
1698 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1699 END IF;
1700 --
1701 IF l_debug_on THEN
1702 wsh_debug_sv.push(l_module_name);
1703 wsh_debug_sv.log(l_module_name, 'P_ENTITY_TYPE',p_entity_type);
1704 wsh_debug_sv.log(l_module_name, 'P_ENTITY_ID',p_entity_id);
1705 wsh_debug_sv.log(l_module_name, 'P_SHIP_FROM_LOCATION_ID',p_ship_from_location_id);
1706 wsh_debug_sv.log(l_module_name, 'P_LOGGING_ENTITY',p_logging_entity);
1707 wsh_debug_sv.log(l_module_name, 'P_EXCEPTION_NAME',p_exception_name);
1708 END IF;
1709
1710 IF (p_ship_from_location_id IS NULL AND p_entity_type = 'DELIVERY') THEN
1711 SELECT INITIAL_PICKUP_LOCATION_ID INTO l_ship_from_location_id
1712 FROM WSH_NEW_DELIVERIES
1713 WHERE delivery_id = p_entity_id;
1714 ELSE
1715 l_ship_from_location_id := p_ship_from_location_id;
1716 END IF;
1717
1718 IF (p_exception_name = 'WSH_DEL_SCPOD_PURGED') THEN
1719 l_msg := FND_MESSAGE.Get_String('WSH','WSH_DEL_SCPOD_TERMINATED');
1720 ELSIF (p_exception_name = 'WSH_LAUNCH_WF_FAILED') THEN
1721 l_msg := FND_MESSAGE.Get_String('WSH','WSH_LAUNCH_WF_FAILED');
1722 END IF;
1723
1724 IF l_debug_on THEN
1725 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_XC_UTIL.Log_Exception',
1726 WSH_DEBUG_SV.C_PROC_LEVEL);
1727 END IF;
1728
1729 IF (p_entity_type = 'DELIVERY') THEN
1730 wsh_xc_util.log_exception(
1731 p_api_version => 1.0,
1732 x_return_status => x_return_status,
1733 x_msg_count => l_exception_msg_count,
1734 x_msg_data => l_exception_msg_data,
1735 x_exception_id => l_exception_id ,
1736 p_logged_at_location_id => l_ship_from_location_id,
1737 p_exception_location_id => l_ship_from_location_id,
1738 p_logging_entity => p_logging_entity,
1739 p_logging_entity_id => FND_GLOBAL.USER_ID,
1740 p_exception_name => p_exception_name,
1741 p_message => l_msg,
1742 p_delivery_id => p_entity_id
1743 );
1744 END IF;
1745 IF l_debug_on THEN
1746 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_XC_UTIL.Log_Exception',x_return_status);
1747 WSH_DEBUG_SV.log(l_module_name,'L_EXCEPTION_MSG_COUNT',l_exception_msg_count);
1748 WSH_DEBUG_SV.log(l_module_name,'L_EXCEPTION_MSG_DATA',l_exception_msg_data);
1749 WSH_DEBUG_SV.log(l_module_name,'L_EXCEPTION_ID',l_exception_id);
1750 END IF;
1751
1752 IF l_debug_on THEN
1753 WSH_DEBUG_SV.pop(l_module_name);
1754 END IF;
1755
1756
1757 EXCEPTION
1758 WHEN others THEN
1759 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1760 IF l_debug_on THEN
1761 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1762 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1763 END IF;
1764 END Log_Wf_Exception;
1765
1766 ---------------------------------------------------------------------------------------
1767 FUNCTION Instance_Default_Rule (p_subscription_guid in raw,
1768 p_event in out nocopy WF_EVENT_T) return
1769 VARCHAR2 is
1770 --performance bug 5220516: make this API as efficient as possible
1771 -- by commenting the unnecessary assignment; this variable is
1772 -- used only in the code that is currently commented.
1773 --l_eventname VARCHAR2(240);
1774 l_eventkey VARCHAR2(240);
1775 l_entityid VARCHAR2(240);
1776 l_pik_status VARCHAR2(30);
1777 l_svc_status VARCHAR2(30);
1778
1779 CURSOR get_trip(l_wf_item_key IN VARCHAR2) IS
1780 SELECT trip_id
1781 FROM WSH_TRIPS
1782 WHERE wf_item_key = l_wf_item_key;
1783
1784 CURSOR get_trip_del(l_trip_id IN NUMBER) IS
1785 select delivery_id
1786 from WSH_DELIVERY_LEGS wdl,
1787 WSH_TRIP_STOPS wts
1788 where wdl.pick_up_stop_id = wts.stop_id and
1789 wts.trip_id = l_trip_id;
1790
1791 CURSOR get_delivery(l_event_key IN VARCHAR2) IS
1792 SELECT delivery_id
1793 FROM WSH_NEW_DELIVERIES WND,
1794 WSH_TRANSACTIONS_HISTORY WTH
1795 WHERE WTH.event_key = l_event_key and
1796 WTH.entity_type = 'DLVY' and
1797 WTH.entity_number = WND.name;
1798
1799 CURSOR get_act_status(l_eventkey IN VARCHAR2,l_act_name IN VARCHAR2) IS
1800 SELECT activity_status_code
1801 FROM WF_ITEM_ACTIVITY_STATUSES_V
1802 WHERE item_type = 'WSHDEL'
1803 AND item_key = l_eventkey
1804 AND activity_name = l_act_name;
1805
1806 l_return_status VARCHAR2(30);
1807 l_raise_exception BOOLEAN;
1808 e_entityid_notfound EXCEPTION;
1809 e_event_error EXCEPTION;
1810
1811
1812 l_debug_on BOOLEAN;
1813 --
1814 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Instance_Default_Rule';
1815
1816 BEGIN
1817 --l_eventname := p_event.getEventName;
1818 l_eventkey := p_event.getEventKey;
1819 l_raise_exception := FALSE;
1820
1821 /*
1822 IF (l_eventname = 'oracle.apps.wsh.sup.ssro') THEN
1823 OPEN get_delivery(l_eventkey);
1824 FETCH get_delivery into l_entityid;
1825 CLOSE get_delivery;
1826 ELSIF (l_eventname = 'oracle.apps.fte.lt.tenderrequest') THEN
1827 OPEN get_trip(l_eventkey);
1828 FETCH get_trip into l_entityid;
1829 CLOSE get_trip;
1830 ELSIF (l_eventname = 'oracle.apps.fte.inbound.routresp.send') THEN
1831 l_entityid := p_event.getValueForParameter('DELIVERY_ID');
1832 ELSE
1833 l_entityid := l_eventkey;
1834 END IF;
1835 */
1836 l_entityid := l_eventkey;
1837
1838 IF (l_entityid is not NULL) THEN
1839 p_event.SetEventKey(l_entityid);
1840 BEGIN
1841 l_return_status := wf_rule.Instance_Default_Rule(p_subscription_guid, p_event);
1842 EXCEPTION
1843 WHEN others THEN
1844 IF (Wf_Core.Error_Name = 'WFENG_EVENT_NOTFOUND') THEN
1845 Wf_Core.Clear;
1846 ELSE
1847 l_raise_exception:=TRUE;
1848 END IF;
1849 END;
1850
1851 /*CURRENTLY NOT IN USE
1852 IF (l_eventname = 'oracle.apps.fte.lt.tenderrequest') THEN
1853 FOR cur_rec in get_trip_del(l_entityid) LOOP
1854 p_event.setEventKey(cur_rec.delivery_id);
1855 BEGIN
1856 l_return_status := wf_rule.Instance_Default_Rule(p_subscription_guid, p_event);
1857 EXCEPTION
1858 WHEN others THEN
1859 IF (Wf_Core.Error_Name = 'WFENG_EVENT_NOTFOUND') THEN
1860 Wf_Core.Clear;
1861 ELSE
1862 l_raise_exception:=TRUE;
1863 END IF;
1864 END;
1865 END LOOP;
1866 END IF;
1867 p_event.SetEventKey(l_eventkey);
1868
1869 IF (l_eventname = 'oracle.apps.wsh.delivery.gen.carriercancelled') THEN
1870 OPEN get_act_status(l_eventkey,'DEL_PIK_INITIATED');
1871 FETCH get_act_status INTO l_pik_status;
1872 IF get_act_status%NOTFOUND THEN
1873 null;
1874 END IF;
1875 CLOSE get_act_status;
1876 OPEN get_act_status(l_eventkey,'DEL_SERV_SEL_INITIATED');
1877 FETCH get_act_status INTO l_svc_status;
1878 IF get_act_status%NOTFOUND THEN
1879 null;
1880 END IF;
1881 CLOSE get_act_status;
1882 WF_ENGINE.Startprocess(itemtype => 'WSHDEL',
1883 itemkey => l_eventkey );
1884 IF (l_pik_status = 'COMPLETE') THEN
1885 wf_engine.completeactivityinternalname('WSHDEL', l_eventkey,'DEL_PIK_INITIATED',null);
1886 END IF;
1887 IF (l_svc_status = 'COMPLETE') THEN
1888 wf_engine.completeactivityinternalname('WSHDEL', l_eventkey,'DEL_SERV_SEL_INITIATED',null);
1889 END IF;
1890 END IF;
1891 */
1892 ELSE
1893 raise e_entityid_notfound;
1894 END IF;
1895
1896 IF l_raise_exception THEN
1897 raise e_event_error;
1898 END IF;
1899
1900 IF l_debug_on THEN
1901 WSH_DEBUG_SV.pop(l_module_name);
1902 END IF;
1903
1904 RETURN 'SUCCESS';
1905
1906 EXCEPTION
1907 WHEN e_entityid_notfound THEN
1908 WF_CORE.CONTEXT('WSH_WF_STD', 'INSTANCE_DEFAULT_RULE',p_event.getEventName( ), p_subscription_guid);
1909 WF_EVENT.setErrorInfo(p_event, 'ERROR');
1910 IF l_debug_on THEN
1911 WSH_DEBUG_SV.logmsg(l_module_name,'Associated Entity Id not found. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1912 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:E_ENTITYID_NOTFOUND');
1913 END IF;
1914 RETURN 'ERROR';
1915
1916 WHEN e_event_error THEN
1917 WF_CORE.CONTEXT('WSH_WF_STD', 'INSTANCE_DEFAULT_RULE',p_event.getEventName( ), p_subscription_guid);
1918 WF_EVENT.setErrorInfo(p_event, 'ERROR');
1919 IF l_debug_on THEN
1920 WSH_DEBUG_SV.logmsg(l_module_name,'Some Entities had errors while raising events. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1921 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:E_EVENT_ERROR');
1922 END IF;
1923 RETURN 'ERROR';
1924
1925 WHEN others THEN
1926 WF_CORE.CONTEXT('WSH_WF_STD', 'INSTANCE_DEFAULT_RULE',p_event.getEventName( ), p_subscription_guid);
1927 WF_EVENT.setErrorInfo(p_event, 'ERROR');
1928 IF l_debug_on THEN
1929 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1930 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1931 END IF;
1932 RETURN 'ERROR';
1933 END;
1934 ---------------------------------------------------------------------------------------
1935 -- This procedure sets the global G_RESET_APPS_CONTEXT to TRUE
1936 ---------------------------------------------------------------------------------------
1937 PROCEDURE RESET_APPS_CONTEXT_ON IS
1938 --
1939 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'RESET_APPS_CONTEXT_ON';
1940 l_debug_on BOOLEAN;
1941 --
1942 BEGIN
1943
1944 WSH_WF_STD.G_RESET_APPS_CONTEXT := TRUE;
1945 IF l_debug_on THEN
1946 WSH_DEBUG_SV.push(l_module_name);
1947 WSH_DEBUG_SV.pop(l_module_name);
1948 END IF;
1949
1950 END RESET_APPS_CONTEXT_ON;
1951
1952 ---------------------------------------------------------------------------------------
1953 -- This procedure sets the global G_RESET_APPS_CONTEXT to FALSE
1954 ---------------------------------------------------------------------------------------
1955 PROCEDURE RESET_APPS_CONTEXT_OFF IS
1956 --
1957 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'RESET_APPS_CONTEXT_OFF';
1958 l_debug_on BOOLEAN;
1959 --
1960 BEGIN
1961
1962 WSH_WF_STD.G_RESET_APPS_CONTEXT := FALSE;
1963 IF l_debug_on THEN
1964 WSH_DEBUG_SV.push(l_module_name);
1965 WSH_DEBUG_SV.pop(l_module_name);
1966 END IF;
1967
1968 END RESET_APPS_CONTEXT_OFF;
1969
1970 /* CURRENTLY NOT IN USE
1971 PROCEDURE Get_Carrier(p_del_ids IN WSH_UTIL_CORE.ID_TAB_TYPE,
1972 x_del_old_carrier_ids OUT NOCOPY WSH_UTIL_CORE.ID_TAB_TYPE,
1973 x_return_status OUT NOCOPY VARCHAR2) IS
1974
1975 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_CARRIER';
1976 l_debug_on BOOLEAN;
1977
1978 CURSOR c_tripordel_carrier (p_delivery_id IN NUMBER) IS
1979 SELECT
1980 NVL(wt.carrier_id, wnd.carrier_id) carrier_id
1981 FROM
1982 wsh_trips wt,
1983 wsh_delivery_legs wdl,
1984 wsh_trip_stops wts,
1985 wsh_new_deliveries wnd
1986 WHERE wts.trip_id = wt.trip_id
1987 AND wdl.pick_up_stop_id = wts.stop_id
1988 AND wnd.initial_pickup_location_id = wts.stop_location_id
1989 AND wnd.delivery_id = wdl.delivery_id
1990 AND wnd.delivery_id = p_delivery_id;
1991
1992 CURSOR c_del_carrier (p_delivery_id IN NUMBER) IS
1993 SELECT
1994 carrier_id
1995 FROM
1996 wsh_new_deliveries
1997 WHERE delivery_id = p_delivery_id;
1998
1999 l_carrier_id NUMBER;
2000 l_return_status VARCHAR2(1);
2001
2002 BEGIN
2003
2004 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2005 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2006
2007 IF l_debug_on IS NULL THEN
2008 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2009 END IF;
2010
2011 FOR i IN p_del_ids.FIRST..p_del_ids.LAST
2012 LOOP
2013 --If Trip has a carrier pick it, otherwise get the delivery one.
2014 OPEN c_tripordel_carrier(p_delivery_id => p_del_ids(i));
2015 FETCH c_tripordel_carrier INTO l_carrier_id;
2016 IF c_tripordel_carrier%NOTFOUND THEN
2017 IF l_debug_on THEN
2018 WSH_DEBUG_SV.logmsg(l_module_name,'No Rows returned from Cursor c_tripordel_carrier', WSH_DEBUG_SV.C_STMT_LEVEL);
2019 END IF;
2020 OPEN c_del_carrier(p_delivery_id => p_del_ids(i));
2021 FETCH c_del_carrier INTO l_carrier_id;
2022 CLOSE c_del_carrier;
2023 END IF;
2024 CLOSE c_tripordel_carrier;
2025 x_del_old_carrier_ids(i) := l_carrier_id;
2026 IF l_debug_on THEN
2027 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);
2028 END IF;
2029 END LOOP;
2030
2031 EXCEPTION
2032 WHEN OTHERS THEN
2033 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
2034 wsh_util_core.default_handler('WSH_WF_STD.GET_CARRIER');
2035 IF l_debug_on THEN
2036 --{
2037 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2038 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2039 --}
2040 END IF;
2041 END Get_Carrier;
2042
2043 PROCEDURE Handle_Trip_Carriers(p_trip_id IN NUMBER,
2044 p_del_ids IN WSH_UTIL_CORE.ID_TAB_TYPE,
2045 p_del_old_carrier_ids IN WSH_UTIL_CORE.ID_TAB_TYPE,
2046 x_return_status OUT NOCOPY VARCHAR2) IS
2047
2048 l_del_new_carrier_ids WSH_UTIL_CORE.ID_TAB_TYPE;
2049 l_return_status VARCHAR2(1);
2050
2051 BEGIN
2052
2053 Get_Carrier(p_del_ids => p_del_ids,
2054 x_del_old_carrier_ids => l_del_new_carrier_ids,
2055 x_return_status => l_return_status);
2056
2057 FOR i IN p_del_ids.FIRST..p_del_ids.LAST
2058 LOOP
2059 Assign_Unassign_Carrier(p_delivery_id => p_del_ids(i),
2060 p_old_carrier_id => p_del_old_carrier_ids(i),
2061 p_new_carrier_id => l_del_new_carrier_ids(i),
2062 x_return_status => l_return_status);
2063 END LOOP;
2064 END Handle_Trip_Carriers;
2065
2066 PROCEDURE Assign_Unassign_Carrier(p_delivery_id IN NUMBER,
2067 p_old_carrier_id IN NUMBER,
2068 p_new_carrier_id IN NUMBER,
2069 x_return_status OUT NOCOPY VARCHAR2) IS
2070
2071 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Assign_Unassign_Carrier';
2072 l_debug_on BOOLEAN;
2073
2074 l_return_status VARCHAR2(1);
2075 l_wf_rs VARCHAR2(1);
2076
2077 CURSOR c_get_del_info (p_delivery_id IN NUMBER) IS
2078 SELECT
2079 organization_id,
2080 delivery_wf_process
2081 FROM
2082 wsh_new_deliveries
2083 WHERE delivery_id = p_delivery_id;
2084
2085 CURSOR c_get_carrier_name (p_carrier_id IN NUMBER) IS
2086 SELECT
2087 carrier_name
2088 FROM
2089 wsh_carriers_v
2090 WHERE carrier_id = p_carrier_id;
2091
2092 l_org_id NUMBER;
2093 l_del_wf VARCHAR2(30);
2094 l_carrier_name VARCHAR2(360);
2095
2096 BEGIN
2097
2098 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2099
2100 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2101
2102 IF l_debug_on IS NULL THEN
2103 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2104 END IF;
2105
2106 OPEN c_get_del_info(p_delivery_id => p_delivery_id);
2107 FETCH c_get_del_info INTO l_org_id, l_del_wf;
2108 CLOSE c_get_del_info;
2109
2110 IF (l_del_wf = 'R_DEL_FTE_GEN') THEN
2111
2112 IF ((p_old_carrier_id IS NULL) AND (p_new_carrier_id IS NOT NULL)) THEN
2113
2114 --Carrier on the delivery getting assigned for first time.
2115 WSH_WF_STD.RAISE_EVENT(p_entity_type => 'DELIVERY',
2116 p_entity_id => p_delivery_id,
2117 p_event => 'oracle.apps.wsh.delivery.gen.carrierselected',
2118 p_organization_id => l_org_id,
2119 x_return_status => l_wf_rs);
2120
2121 ELSIF ((p_old_carrier_id IS NOT NULL) AND (p_new_carrier_id IS NULL)) THEN
2122
2123 --Carrier on the Delivery getting nulled out/cancelled.
2124 WSH_WF_STD.RAISE_EVENT(p_entity_type => 'DELIVERY',
2125 p_entity_id => p_delivery_id,
2126 p_event => 'oracle.apps.wsh.delivery.gen.carriercancelled',
2127 p_organization_id => l_org_id,
2128 x_return_status => l_wf_rs);
2129
2130 ELSIF ((p_old_carrier_id IS NOT NULL) AND (p_new_carrier_id IS NOT NULL) AND
2131 (p_old_carrier_id <> p_new_carrier_id)) THEN
2132
2133 --Change in carrier from existing one to a new one.
2134 WSH_WF_STD.RAISE_EVENT(p_entity_type => 'DELIVERY',
2135 p_entity_id => p_delivery_id,
2136 p_event => 'oracle.apps.wsh.delivery.gen.carriercancelled',
2137 p_organization_id => l_org_id,
2138 x_return_status => l_wf_rs);
2139
2140 WSH_WF_STD.RAISE_EVENT(p_entity_type => 'DELIVERY',
2141 p_entity_id => p_delivery_id,
2142 p_event => 'oracle.apps.wsh.delivery.gen.carrierselected',
2143 p_organization_id => l_org_id,
2144 x_return_status => l_wf_rs);
2145 END IF;--Check for old and new Carrier_id values.
2146
2147 WF_ENGINE.SetItemAttrNumber(itemtype => 'WSHDEL',
2148 itemkey => p_delivery_id,
2149 aname => 'CARRIER_ID',
2150 avalue => p_new_carrier_id);
2151
2152 OPEN c_get_carrier_name(p_carrier_id => p_new_carrier_id);
2153 FETCH c_get_carrier_name INTO l_carrier_name;
2154 CLOSE c_get_carrier_name;
2155
2156 WF_ENGINE.SetItemAttrText(itemtype => 'WSHDEL',
2157 itemkey => p_delivery_id,
2158 aname => 'CARRIER_NAME',
2159 avalue => l_carrier_name);
2160
2161 END IF; --Check for Delivery Workflow Name.
2162
2163 EXCEPTION
2164 WHEN OTHERS THEN
2165 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
2166 wsh_util_core.default_handler('WSH_WF_STD.Assign_Unassign_Carrier');
2167 IF l_debug_on THEN
2168 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2169 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2170 END IF;
2171 END Assign_Unassign_Carrier;
2172
2173
2174 PROCEDURE Get_Deliveries(p_trip_id IN NUMBER,
2175 x_del_ids OUT NOCOPY WSH_UTIL_CORE.ID_TAB_TYPE,
2176 x_return_status OUT NOCOPY VARCHAR2) IS
2177
2178 CURSOR c_dels(p_trip_id IN NUMBER) IS
2179 SELECT
2180 wnd.delivery_id
2181 FROM
2182 wsh_new_deliveries wnd,
2183 wsh_delivery_legs wdl,
2184 wsh_trip_stops wts
2185 WHERE
2186 wts.trip_id = p_trip_id AND
2187 wts.stop_id = wdl.pick_up_stop_id AND
2188 wnd.initial_pickup_location_id = wts.stop_location_id AND
2189 wdl.delivery_id = wnd.delivery_id;
2190
2191 BEGIN
2192
2193 OPEN c_dels(p_trip_id => p_trip_id);
2194 FETCH c_dels BULK COLLECT INTO x_del_ids;
2195 CLOSE c_dels;
2196
2197 END Get_Deliveries;
2198 */
2199 END WSH_WF_STD;