1 PACKAGE BODY FTE_WORKFLOW_UTIL AS
2 /* $Header: FTEWKFUB.pls 120.3 2005/08/12 13:52:02 schennal noship $ */
3
4 --===================
5 -- PUBLIC VARIABLES
6 --===================
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'FTE_WORKFLOW_UTIL';
8
9 --========================================================================
10 -- CURSOR : Get_Trip_Deliveries
11 --
12 -- PARAMETERS: c_trip_id Trip Id
13 --
14 -- COMMENT : Gets the deliveries assigned to the trip.
15 --========================================================================
16 CURSOR Get_Trip_Deliveries(c_trip_id NUMBER)
17 IS
18 SELECT WDL.DELIVERY_ID
19 FROM WSH_TRIP_STOPS WTS, WSH_DELIVERY_LEGS WDL
20 WHERE WTS.STOP_ID = WDL.PICK_UP_STOP_ID
21 AND WTS.TRIP_ID = c_trip_id;
22
23 --========================================================================
24 -- CURSOR : Get_Dleg_Delivery
25 --
26 -- PARAMETERS: c_dleg_id Delivery Leg Id
27 --
28 -- COMMENT : Gets the delivery attached to the leg.
29 --========================================================================
30 CURSOR Get_Dleg_Delivery(c_dleg_id NUMBER)
31 IS
32 SELECT WDL.DELIVERY_ID
33 FROM WSH_DELIVERY_LEGS WDL
34 WHERE WDL.DELIVERY_LEG_ID = c_dleg_id;
35
36 --========================================================================
37 -- CURSOR : Get_Dleg_Trip
38 --
39 -- PARAMETERS: c_dleg_id Delivery Leg Id
40 --
41 -- COMMENT : Gets the trip to which the delivery leg is attached.
42 --========================================================================
43 CURSOR Get_Dleg_Trip(c_dleg_id NUMBER)
44 IS
45 SELECT WTS.TRIP_ID
46 FROM WSH_TRIP_STOPS WTS, WSH_DELIVERY_LEGS WDL
47 WHERE WTS.STOP_ID = WDL.PICK_UP_STOP_ID
48 AND WDL.DELIVERY_LEG_ID = c_dleg_id;
49
50 --========================================================================
51 -- CURSOR : Get_Delivery_Org
52 --
53 -- PARAMETERS: c_del_id Delivery Id
54 --
55 -- COMMENT : Gets the organization id of the delivery
56 --========================================================================
57 CURSOR Get_Delivery_Org(c_del_id NUMBER)
58 IS
59 SELECT ORGANIZATION_ID
60 FROM WSH_NEW_DELIVERIES
61 WHERE DELIVERY_ID = c_del_id;
62
63 --===================
64 -- PROCEDURES
65 --===================
66
67
68 --========================================================================
69 -- PROCEDURE : Single_Trip_Sel_Ser_Init
70 --
71 -- PARAMETERS: p_trip_id Trip Id
72 -- x_return_status Return status
73 --
74 -- COMMENT : This procedure accepts a trip id and raises the Select Service
75 -- Initiated event for the trip.
76 --========================================================================
77 PROCEDURE Single_Trip_Sel_Ser_Init(
78 p_trip_id IN NUMBER,
79 x_return_status OUT NOCOPY VARCHAR2)
80 IS
81 l_return_status VARCHAR2(1);
82 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || 'SINGLE_TRIP_SEL_SER_INIT';
83 l_debug_on BOOLEAN;
84 BEGIN
85 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
86
87 IF l_debug_on IS NULL
88 THEN
89 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
90 END IF;
91
92 IF l_debug_on
93 THEN
94 WSH_DEBUG_SV.push(l_module_name);
95 WSH_DEBUG_SV.log(l_module_name,'P_TRIP_ID',p_trip_id);
96 WSH_DEBUG_SV.logmsg(l_module_name,'Raising event oracle.apps.fte.trip.svc.serviceselectioninitiated for trip '||p_trip_id);
97 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WF_STD.RAISE_EVENT',WSH_DEBUG_SV.C_PROC_LEVEL);
98 END IF;
99
100 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
101 WSH_WF_STD.Raise_Event(
102 p_entity_type => 'TRIP',
103 p_entity_id => p_trip_id,
104 p_event => 'oracle.apps.fte.trip.svc.serviceselectioninitiated' ,
105 x_return_status => l_return_status);
106
107 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
108 OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
109 THEN
110 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
111 IF l_debug_on
112 THEN
113 WSH_DEBUG_SV.logmsg(l_module_name, 'Error occurred in WSH_WF_STD.RAISE_EVENT');
114 WSH_DEBUG_SV.logmsg(l_module_name, 'Unable to raise Select Service Init for trip '||p_trip_id);
115 END IF;
116 END IF;
117
118 IF l_debug_on
119 THEN
120 WSH_DEBUG_SV.pop(l_module_name);
121 END IF;
122
123 EXCEPTION
124 WHEN FND_API.G_EXC_ERROR
125 THEN
126
127 IF l_debug_on
128 THEN
129 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
130 END IF;
131 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
132 WHEN OTHERS
133 THEN
134 IF l_debug_on
135 THEN
136 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
137 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
138 END IF;
139 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
140 END Single_Trip_Sel_Ser_Init;
141
142
143 --========================================================================
144 -- PROCEDURE : Single_Del_Sel_Ser
145 --
146 -- PARAMETERS: p_del_id Delivery Id
147 -- p_org_id Organization Id
148 -- x_return_status Return status
149 --
150 -- COMMENT : This procedure accepts a delivery id and raises the Select Service
151 -- event for the delivery.
152 --========================================================================
153 PROCEDURE Single_Del_Sel_Ser(
154 p_del_id IN NUMBER,
155 p_org_id IN NUMBER,
156 x_return_status OUT NOCOPY VARCHAR2)
157 IS
158 l_return_status VARCHAR2(1);
159 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || 'SINGLE_DEL_SEL_SER';
160 l_debug_on BOOLEAN;
161 BEGIN
162 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
163
164 IF l_debug_on IS NULL
165 THEN
166 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
167 END IF;
168
169 IF l_debug_on
170 THEN
171 WSH_DEBUG_SV.push(l_module_name);
172 WSH_DEBUG_SV.log(l_module_name,'P_DEL_ID',p_del_id);
173 WSH_DEBUG_SV.log(l_module_name,'P_ORG_ID',p_org_id);
174 WSH_DEBUG_SV.logmsg(l_module_name,'Raising event oracle.apps.wsh.delivery.svc.serviceselected for delivery '||p_del_id||', org '||p_org_id);
175 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WF_STD.RAISE_EVENT',WSH_DEBUG_SV.C_PROC_LEVEL);
176 END IF;
177
178 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
179 WSH_WF_STD.Raise_Event(
180 p_entity_type => 'DELIVERY',
181 p_entity_id => p_del_id,
182 p_event => 'oracle.apps.fte.delivery.svc.serviceselected',
183 p_organization_id => p_org_id,
184 x_return_status => l_return_status);
185
186 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
187 OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
188 THEN
189 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
190 IF l_debug_on
191 THEN
192 WSH_DEBUG_SV.logmsg(l_module_name, 'Error occurred in WSH_WF_STD.RAISE_EVENT');
193 WSH_DEBUG_SV.logmsg(l_module_name, 'Unable to raise Select Service for delivery '||p_del_id||', org '||p_org_id);
194 END IF;
195 END IF;
196
197 IF l_debug_on
198 THEN
199 WSH_DEBUG_SV.pop(l_module_name);
200 END IF;
201
202 EXCEPTION
203 WHEN FND_API.G_EXC_ERROR
204 THEN
205
206 IF l_debug_on
207 THEN
208 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
209 END IF;
210 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
211 WHEN OTHERS
212 THEN
213 IF l_debug_on
214 THEN
215 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
216 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
217 END IF;
218 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
219
220 END Single_Del_Sel_Ser;
221
222
223
224 --========================================================================
225 -- PROCEDURE : Single_Del_Can_Ser
226 --
227 -- PARAMETERS: p_del_id Delivery Id
228 -- p_org_id Organization Id
229 -- x_return_status Return status
230 --
231 -- COMMENT : This procedure accepts a delivery id and raises the Cancel Service
232 -- event for the delivery.
233 --========================================================================
234 PROCEDURE Single_Del_Can_Ser(
235 p_del_id IN NUMBER,
236 p_org_id IN NUMBER,
237 x_return_status OUT NOCOPY VARCHAR2)
238 IS
239 l_return_status VARCHAR2(1);
240 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || 'SINGLE_DEL_CAN_SER';
241 l_debug_on BOOLEAN;
242 BEGIN
243 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
244
245 IF l_debug_on IS NULL
246 THEN
247 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
248 END IF;
249
250 IF l_debug_on
251 THEN
252 WSH_DEBUG_SV.push(l_module_name);
253 WSH_DEBUG_SV.log(l_module_name,'P_DEL_ID',p_del_id);
254 WSH_DEBUG_SV.log(l_module_name,'P_ORG_ID',p_org_id);
255 WSH_DEBUG_SV.logmsg(l_module_name,'Raising event oracle.apps.wsh.delivery.svc.servicecancelled for delivery '||p_del_id||', org '||p_org_id);
256 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WF_STD.RAISE_EVENT',WSH_DEBUG_SV.C_PROC_LEVEL);
257 END IF;
258
259 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
260 WSH_WF_STD.Raise_Event(
261 p_entity_type => 'DELIVERY',
262 p_entity_id => p_del_id,
263 p_event => 'oracle.apps.fte.delivery.svc.servicecancelled',
264 p_organization_id => p_org_id,
265 x_return_status => l_return_status);
266
267 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
268 OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
269 THEN
270 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
271 IF l_debug_on
272 THEN
273 WSH_DEBUG_SV.logmsg(l_module_name, 'Error occurred in WSH_WF_STD.RAISE_EVENT');
274 WSH_DEBUG_SV.logmsg(l_module_name, 'Unable to raise Cancel Service for delivery '||p_del_id||', org '||p_org_id);
275 END IF;
276 END IF;
277
278 IF l_debug_on
279 THEN
280 WSH_DEBUG_SV.pop(l_module_name);
281 END IF;
282
283 EXCEPTION
284 WHEN FND_API.G_EXC_ERROR
285 THEN
286
287 IF l_debug_on
288 THEN
289 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
290 END IF;
291 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
292 WHEN OTHERS
293 THEN
294 IF l_debug_on
295 THEN
296 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
297 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
301 END Single_Del_Can_Ser;
298 END IF;
299 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
300
302
303
304 --========================================================================
305 -- PROCEDURE : Trip_Select_Service_Init
306 --
307 -- PARAMETERS: p_trip_id Trip Id
308 -- x_return_status Return status
309 --
310 -- COMMENT : This procedure accepts a trip and calls the procedures which
311 -- raises the Service Selection Initiation for the trip and
312 -- Select Service event for all the deliveries assigned to the trip.
313 --========================================================================
314 PROCEDURE Trip_Select_Service_Init(
315 p_trip_id IN NUMBER,
316 x_return_status OUT NOCOPY VARCHAR2)
317 IS
318 l_del_id NUMBER;
319 l_org_id NUMBER;
320 l_return_status VARCHAR2(1);
321 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || 'TRIP_SELECT_SERVICE_INIT';
322 l_debug_on BOOLEAN;
323 BEGIN
324
325 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
326
327 IF l_debug_on IS NULL
328 THEN
329 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
330 END IF;
331
332 IF l_debug_on
333 THEN
334 WSH_DEBUG_SV.push(l_module_name);
335 WSH_DEBUG_SV.log(l_module_name,'P_TRIP_ID',p_trip_id);
336 END IF;
337
338 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
339 IF l_debug_on THEN
340 WSH_DEBUG_SV.logmsg(l_module_name,'Raising Service Selection Initiated for the trip '||p_trip_id);
341 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit FTE_WORKFLOW_UTIL.SINGLE_TRIP_SEL_SER_INIT',WSH_DEBUG_SV.C_PROC_LEVEL);
342 END IF;
343
344 Single_Trip_Sel_Ser_Init(
345 p_trip_id => p_trip_id,
346 x_return_status => l_return_status);
347
348 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
349 OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
350 THEN
351 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
352 IF l_debug_on
353 THEN
354 WSH_DEBUG_SV.logmsg(l_module_name, 'Error occurred in FTE_WORKFLOW_UTIL.SINGLE_TRIP_SEL_SER_INIT');
355 WSH_DEBUG_SV.logmsg(l_module_name, 'Raising Select Service Initiated for the trip '||p_trip_id||' failed');
356 END IF;
357 RAISE FND_API.G_EXC_ERROR;
358 END IF;
359
360 IF l_debug_on
361 THEN
362 WSH_DEBUG_SV.logmsg(l_module_name,'Fetching the deliveries assigned to the trip');
363 END IF;
364
365 OPEN Get_Trip_Deliveries(p_trip_id);
366 LOOP
367 FETCH Get_Trip_Deliveries INTO l_del_id;
368 EXIT WHEN Get_Trip_Deliveries%NOTFOUND;
369
370 OPEN Get_Delivery_Org(l_del_id);
371 FETCH Get_Delivery_Org INTO l_org_id;
372 CLOSE Get_Delivery_Org;
373
374 IF l_debug_on THEN
375 WSH_DEBUG_SV.logmsg(l_module_name,'Got delivery '||l_del_id);
376 WSH_DEBUG_SV.logmsg(l_module_name,'Raising Service Selection for the delivery '||l_del_id);
377 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit FTE_WORKFLOW_UTIL.SINGLE_DEL_SEL_SER',WSH_DEBUG_SV.C_PROC_LEVEL);
378 END IF;
379
380 Single_Del_Sel_Ser(
381 p_del_id => l_del_id,
382 p_org_id => l_org_id,
383 x_return_status => l_return_status);
384
385 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
386 OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
387 THEN
388 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
389 IF l_debug_on
390 THEN
391 WSH_DEBUG_SV.logmsg(l_module_name, 'Error occurred in FTE_WORKFLOW_UTIL.SINGLE_DEL_SEL_SER');
392 WSH_DEBUG_SV.logmsg(l_module_name, 'Raising Select Service for the delivery '||l_del_id||' failed');
393 END IF;
394 END IF;
395
396 END LOOP;
397 CLOSE Get_Trip_Deliveries;
398
399 IF l_debug_on
400 THEN
401 WSH_DEBUG_SV.pop(l_module_name);
402 END IF;
403
404 EXCEPTION
405 WHEN FND_API.G_EXC_ERROR
406 THEN
407
408 IF l_debug_on
409 THEN
410 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
411 END IF;
412 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
413 WHEN OTHERS
414 THEN
415 IF l_debug_on
416 THEN
417 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
418 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
419 END IF;
420 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
421
422 END Trip_Select_Service_Init;
423
424
425 --========================================================================
426 -- PROCEDURE : Trip_Cancel_Service
427 --
428 -- PARAMETERS: p_trip_id Trip Id
429 -- x_return_status Return status
430 --
431 -- COMMENT : This procedure accepts a trip and calls the procedures which
432 -- raises the Cancel Service event for all the deliveries
433 -- assigned to the trip.
434 --========================================================================
435 PROCEDURE Trip_Cancel_Service(
436 p_trip_id IN NUMBER,
437 x_return_status OUT NOCOPY VARCHAR2)
438
439 IS
440 l_del_id NUMBER;
441 l_org_id NUMBER;
442 l_return_status VARCHAR2(1);
443 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || 'TRIP_CANCEL_SERVICE';
444 l_debug_on BOOLEAN;
445 BEGIN
446
447 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
448
449 IF l_debug_on IS NULL
450 THEN
451 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
452 END IF;
453
454 IF l_debug_on
455 THEN
456 WSH_DEBUG_SV.push(l_module_name);
457 WSH_DEBUG_SV.log(l_module_name,'P_TRIP_ID',p_trip_id);
458 END IF;
459
460 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
461
462 IF l_debug_on
463 THEN
464 WSH_DEBUG_SV.logmsg(l_module_name,'Fetching the deliveries assigned to the trip');
465 END IF;
466
467 OPEN Get_Trip_Deliveries(p_trip_id);
468 LOOP
469 FETCH Get_Trip_Deliveries INTO l_del_id;
470 EXIT WHEN Get_Trip_Deliveries%NOTFOUND;
471
472 OPEN Get_Delivery_Org(l_del_id);
473 FETCH Get_Delivery_Org INTO l_org_id;
474 CLOSE Get_Delivery_Org;
475
476 IF l_debug_on THEN
477 WSH_DEBUG_SV.logmsg(l_module_name,'Got delivery '||l_del_id);
478 WSH_DEBUG_SV.logmsg(l_module_name,'Raising Service Cancellation for the delivery '||l_del_id);
482 Single_Del_Can_Ser(
479 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit FTE_WORKFLOW_UTIL.SINGLE_DEL_CAN_SER',WSH_DEBUG_SV.C_PROC_LEVEL);
480 END IF;
481
483 p_del_id => l_del_id,
484 p_org_id => l_org_id,
485 x_return_status => l_return_status);
486
487 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
488 OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
489 THEN
490 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
491 IF l_debug_on
492 THEN
493 WSH_DEBUG_SV.logmsg(l_module_name, 'Error occurred in FTE_WORKFLOW_UTIL.SINGLE_DEL_CAN_SER');
494 WSH_DEBUG_SV.logmsg(l_module_name, 'Raising Cancel Service for the delivery '||l_del_id||' failed');
495 END IF;
496 END IF;
497
498 END LOOP;
499 CLOSE Get_Trip_Deliveries;
500
501 IF l_debug_on
502 THEN
503 WSH_DEBUG_SV.pop(l_module_name);
504 END IF;
505
506 EXCEPTION
507 WHEN FND_API.G_EXC_ERROR
508 THEN
509
510 IF l_debug_on
511 THEN
512 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
513 END IF;
514 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
515 WHEN OTHERS
516 THEN
517 IF l_debug_on
518 THEN
519 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
520 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
521 END IF;
522 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
523
524 END Trip_Cancel_Service;
525
526
527 --========================================================================
528 -- PROCEDURE : Dleg_Select_Service
529 --
530 -- PARAMETERS: p_dleg_id Delivery Leg Id
531 -- x_return_status Return status
532 --
533 -- COMMENT : This procedure accepts a delivery leg and calls the procedures which
534 -- raises the Select Service Event for the delivery and Select Service
535 -- Initiation event for the trip to which the delivery is assigned.
536 --========================================================================
537 PROCEDURE Dleg_Select_Service(
538 p_dleg_id IN NUMBER,
539 x_return_status OUT NOCOPY VARCHAR2)
540 IS
541 l_trip_id NUMBER;
542 l_del_id NUMBER;
543 l_org_id NUMBER;
544 l_return_status VARCHAR2(1);
545 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || 'DLEG_SELECT_SERVICE';
546 l_debug_on BOOLEAN;
547 BEGIN
551 THEN
548 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
549
550 IF l_debug_on IS NULL
552 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
553 END IF;
554
555 IF l_debug_on
556 THEN
557 WSH_DEBUG_SV.push(l_module_name);
558 WSH_DEBUG_SV.log(l_module_name,'P_DLEG_ID',p_dleg_id);
559 END IF;
560
561 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
562
563 OPEN Get_Dleg_Delivery(p_dleg_id);
564 FETCH Get_Dleg_Delivery INTO l_del_id;
565 CLOSE Get_Dleg_Delivery;
566
567 IF l_del_id IS NULL
568 THEN
569 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
570 IF l_debug_on THEN
571 WSH_DEBUG_SV.logmsg(l_module_name,'Failed to fetch the delivery associated with the delivery leg');
572 END IF;
573 RAISE FND_API.G_EXC_ERROR;
574 END IF;
575
576 OPEN Get_Delivery_Org(l_del_id);
577 FETCH Get_Delivery_Org INTO l_org_id;
578 CLOSE Get_Delivery_Org;
579
580 IF l_debug_on THEN
581 WSH_DEBUG_SV.logmsg(l_module_name,'Raising Service Selection for the delivery '||l_del_id);
582 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit FTE_WORKFLOW_UTIL.SINGLE_DEL_SEL_SER',WSH_DEBUG_SV.C_PROC_LEVEL);
583 END IF;
584
585 Single_Del_Sel_Ser(
586 p_del_id => l_del_id,
587 p_org_id => l_org_id,
588 x_return_status => l_return_status);
589
590 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
591 OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
592 THEN
593 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
594 IF l_debug_on
595 THEN
596 WSH_DEBUG_SV.logmsg(l_module_name, 'Error occurred in FTE_WORKFLOW_UTIL.SINGLE_DEL_SEL_SER');
597 WSH_DEBUG_SV.logmsg(l_module_name, 'Raising Select Service for the delivery '||l_del_id||' failed');
598 END IF;
599 RAISE FND_API.G_EXC_ERROR;
600 END IF;
601
602 IF l_debug_on THEN
603 WSH_DEBUG_SV.logmsg(l_module_name,'Fetching trip to which the delivery '||l_del_id||' is assigned');
604 END IF;
605
606 OPEN Get_Dleg_Trip(p_dleg_id);
607 FETCH Get_Dleg_Trip INTO l_trip_id;
608 CLOSE Get_Dleg_Trip;
609
610 IF l_trip_id IS NOT NULL
611 THEN
612
613 IF l_debug_on THEN
614 WSH_DEBUG_SV.logmsg(l_module_name,'Found trip '||l_trip_id);
615 WSH_DEBUG_SV.logmsg(l_module_name,'Raising Service Selection Initiated for the trip '||l_trip_id);
616 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit FTE_WORKFLOW_UTIL.SINGLE_TRIP_SEL_SER_INIT',WSH_DEBUG_SV.C_PROC_LEVEL);
617 END IF;
618
619 Single_Trip_Sel_Ser_Init(
620 p_trip_id => l_trip_id,
621 x_return_status => l_return_status);
622
623 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
624 OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
625 THEN
626 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
627 IF l_debug_on
628 THEN
629 WSH_DEBUG_SV.logmsg(l_module_name, 'Error occurred in FTE_WORKFLOW_UTIL.SINGLE_TRIP_SEL_SER_INIT');
630 WSH_DEBUG_SV.logmsg(l_module_name, 'Raising Select Service for the trip '||l_trip_id||' failed');
631 END IF;
632 END IF;
633 ELSE
634 IF l_debug_on THEN
638
635 WSH_DEBUG_SV.logmsg(l_module_name,'No trip found trip');
636 END IF;
637 END IF;
639 IF l_debug_on
640 THEN
641 WSH_DEBUG_SV.pop(l_module_name);
642 END IF;
643
644 EXCEPTION
645 WHEN FND_API.G_EXC_ERROR
646 THEN
647
648 IF l_debug_on
649 THEN
650 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
651 END IF;
652 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
653 WHEN OTHERS
654 THEN
655 IF l_debug_on
656 THEN
657 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
658 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
659 END IF;
660 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
661
662 END Dleg_Select_Service;
663
664
665 --========================================================================
666 -- PROCEDURE : Dleg_Cancel_Service
667 --
668 -- PARAMETERS: p_dleg_id Delivery Leg Id
669 -- x_return_status Return status
670 --
671 -- COMMENT : This procedure accepts a delivery leg and calls the procedure which
672 -- raises the Cancel Service Event for the delivery.
673 --========================================================================
674 PROCEDURE Dleg_Cancel_Service(
675 p_dleg_id IN NUMBER,
676 x_return_status OUT NOCOPY VARCHAR2)
677 IS
678 l_del_id NUMBER;
679 l_org_id NUMBER;
680 l_return_status VARCHAR2(1);
681 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || 'DLEG_CANCEL_SERVICE';
682 l_debug_on BOOLEAN;
683 BEGIN
684 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
685
686 IF l_debug_on IS NULL
687 THEN
688 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
689 END IF;
690
691 IF l_debug_on
692 THEN
693 WSH_DEBUG_SV.push(l_module_name);
694 WSH_DEBUG_SV.log(l_module_name,'P_DLEG_ID',p_dleg_id);
695 END IF;
696
697 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
698
699 OPEN Get_Dleg_Delivery(p_dleg_id);
700 FETCH Get_Dleg_Delivery INTO l_del_id;
701 CLOSE Get_Dleg_Delivery;
702
703 IF l_del_id IS NULL
704 THEN
705 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
706 IF l_debug_on THEN
707 WSH_DEBUG_SV.logmsg(l_module_name,'Failed to fetch the delivery associated with the delivery leg');
708 END IF;
709 RAISE FND_API.G_EXC_ERROR;
710 END IF;
711
712 OPEN Get_Delivery_Org(l_del_id);
713 FETCH Get_Delivery_Org INTO l_org_id;
714 CLOSE Get_Delivery_Org;
715
716 IF l_debug_on THEN
717 WSH_DEBUG_SV.logmsg(l_module_name,'Raising Cancel Service for the delivery '||l_del_id);
718 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit FTE_WORKFLOW_UTIL.SINGLE_DEL_CAN_SER',WSH_DEBUG_SV.C_PROC_LEVEL);
719 END IF;
720
721 Single_Del_Can_Ser(
722 p_del_id => l_del_id,
723 p_org_id => l_org_id,
724 x_return_status => l_return_status);
725
726 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
727 OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
728 THEN
729 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
730 IF l_debug_on
731 THEN
732 WSH_DEBUG_SV.logmsg(l_module_name, 'Error occurred in FTE_WORKFLOW_UTIL.SINGLE_DEL_CAN_SER');
733 WSH_DEBUG_SV.logmsg(l_module_name, 'Raising Cancel Service for the delivery '||l_del_id||' failed');
734 END IF;
735 RAISE FND_API.G_EXC_ERROR;
736 END IF;
737
738 IF l_debug_on
739 THEN
740 WSH_DEBUG_SV.pop(l_module_name);
741 END IF;
742
743 EXCEPTION
744 WHEN FND_API.G_EXC_ERROR
745 THEN
746
747 IF l_debug_on
748 THEN
749 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
750 END IF;
751 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
752 WHEN OTHERS
753 THEN
754 IF l_debug_on
755 THEN
756 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
757 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
758 END IF;
759 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
760
761 END Dleg_Cancel_Service;
762
763
764 END FTE_WORKFLOW_UTIL;
[Home] [Help]