[Home] [Help]
PACKAGE BODY: APPS.WSH_TRANSACTIONS_TPW_UTIL
Source
1 PACKAGE BODY WSH_TRANSACTIONS_TPW_UTIL AS
2 /* $Header: WSHTXTPB.pls 120.1.12010000.3 2009/12/03 16:14:59 mvudugul ship $ */
3
4
5 /*===========================================================================
6 | |
7 | PROCEDURE NAME Check_Cancel_Allowed_WF |
8 | |
9 | DESCRIPTION This procedure is called from the work flow to trigger |
10 | the procedure Check_Cancel_Allowed, which checks if the |
11 | cancellation for a transaction is allowed or not. |
12 | |
13 | MODIFICATION HISTORY |
14 | |
15 | 02/18/02 Vijay Nandula Created |
16 | |
17 ============================================================================*/
18
19 --
20 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_TRANSACTIONS_TPW_UTIL';
21 --
22 PROCEDURE Check_Cancel_Allowed_WF ( P_item_type IN VARCHAR2,
23 P_item_key IN VARCHAR2,
24 P_actid IN NUMBER,
25 P_funcmode IN VARCHAR2,
26 X_resultout OUT NOCOPY VARCHAR2 )
27 IS
28 l_Return_Status VARCHAR2(1);
29 --
30 l_debug_on BOOLEAN;
31 --
32 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CHECK_CANCEL_ALLOWED_WF';
33 --
34 BEGIN
35 --
36 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
37 --
38 IF l_debug_on IS NULL
39 THEN
40 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
41 END IF;
42 --
43 IF l_debug_on THEN
44 wsh_debug_sv.push(l_module_name, 'Check_Cancel_Allowed_WF');
45 wsh_debug_sv.log(l_module_name, 'P_item_type',P_item_type);
46 wsh_debug_sv.log(l_module_name, 'P_item_key',P_item_key);
47 wsh_debug_sv.log(l_module_name, 'P_actid',P_actid);
48 wsh_debug_sv.log(l_module_name, 'P_funcmode',P_funcmode);
49 END IF;
50
51 IF ( P_funcmode = 'RUN' ) THEN
52
53 l_Return_Status := WSH_UTIL_CORE.g_ret_sts_success;
54
55 WSH_TRANSACTIONS_TPW_UTIL.Check_Cancel_Allowed (P_item_type, P_item_key, l_Return_Status);
56 IF l_debug_on THEN
57 wsh_debug_sv.log (l_module_name, 'Return status after check_Cancel_Allowed ', l_Return_Status);
58 END IF;
59
60 IF ( l_Return_Status = WSH_UTIL_CORE.g_ret_sts_success ) THEN
61 X_resultout := 'COMPLETE:SUCCESS';
62 ELSE
63 X_resultout := 'COMPLETE:ERROR';
64 END IF;
65 END IF;
66
67 IF l_debug_on THEN
68 wsh_debug_sv.log(l_module_name, 'X_resultout',X_resultout);
69 wsh_debug_sv.pop(l_module_name);
70 END IF;
71
72 EXCEPTION
73 WHEN OTHERS THEN
74 X_resultout := 'COMPLETE:ERROR';
75 IF l_debug_on THEN
76 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
77 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
78 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
79 END IF;
80 RAISE;
81 END Check_Cancel_Allowed_WF;
82
83
84 /*===========================================================================
85 | |
86 | PROCEDURE NAME Send_Cbod_Success_WF |
87 | |
88 | DESCRIPTION This procedure is called from the work flow to trigger |
89 | the procedure Send_Cbod_Success, which sends a CBOD to |
90 | the supplier, if cancellation is successful. |
91 | |
92 | MODIFICATION HISTORY |
93 | |
94 | 02/18/02 Vijay Nandula Created |
95 | |
96 ============================================================================*/
97
98 PROCEDURE Send_Cbod_Success_WF ( P_item_type IN VARCHAR2,
99 P_item_key IN VARCHAR2,
100 P_actid IN NUMBER,
101 P_funcmode IN VARCHAR2,
102 X_resultout OUT NOCOPY VARCHAR2 )
103 IS
104 l_Return_Status VARCHAR2(1);
105 --
106 l_debug_on BOOLEAN;
107 --
108 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'SEND_CBOD_SUCCESS_WF';
109 --
110 BEGIN
111 --
112 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
113 --
114 IF l_debug_on IS NULL
115 THEN
116 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
117 END IF;
118 --
119 IF l_debug_on THEN
120 wsh_debug_sv.push(l_module_name, 'Send_Cbod_Success_WF');
121 wsh_debug_sv.log(l_module_name, 'P_item_type',P_item_type);
122 wsh_debug_sv.log(l_module_name, 'P_item_key',P_item_key);
123 wsh_debug_sv.log(l_module_name, 'P_actid',P_actid);
124 wsh_debug_sv.log(l_module_name, 'P_funcmode',P_funcmode);
125 END IF;
126
127 IF ( P_funcmode = 'RUN' ) THEN
128
129 l_Return_Status := WSH_UTIL_CORE.g_ret_sts_success;
130
131 Send_Cbod_Success (P_item_type, P_item_key, l_Return_Status);
132 IF l_debug_on THEN
133 wsh_debug_sv.log (l_module_name, 'Return status after Send_Cbod_Success ', l_Return_Status);
134 END IF;
135
136 IF ( l_Return_Status = WSH_UTIL_CORE.g_ret_sts_success ) THEN
137 X_resultout := 'COMPLETE:SUCCESS';
138 ELSE
139 X_resultout := 'COMPLETE:ERROR';
140 END IF;
141 END IF;
142
143 IF l_debug_on THEN
144 wsh_debug_sv.log(l_module_name, 'X_resultout',X_resultout);
145 wsh_debug_sv.pop(l_module_name);
146 END IF;
147
148 EXCEPTION
149
150 WHEN OTHERS THEN
151 X_resultout := 'COMPLETE:ERROR';
152 IF l_debug_on THEN
153 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
154 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
155 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
156 END IF;
157 RAISE;
158 END Send_Cbod_Success_WF;
159
160
161 /*===========================================================================
162 | |
163 | PROCEDURE NAME Send_Cbod_Failure_WF |
164 | |
165 | DESCRIPTION This procedure is called from the work flow to trigger |
166 | the procedure Send_Cbod_Failure, which sends a COBD to |
167 | the supplier indicating the transaction or cancellation |
168 | is not processed successfully. |
169 | MODIFICATION HISTORY |
170 | |
171 | 02/18/02 Vijay Nandula Created |
172 | |
173 ============================================================================*/
174
175 PROCEDURE Send_Cbod_Failure_WF ( P_item_type IN VARCHAR2,
176 P_item_key IN VARCHAR2,
177 P_actid IN NUMBER,
178 P_funcmode IN VARCHAR2,
179 X_resultout OUT NOCOPY VARCHAR2 )
180 IS
181 l_Return_Status VARCHAR2(1);
182 --
183 l_debug_on BOOLEAN;
184 --
185 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'SEND_CBOD_FAILURE_WF';
186 --
187 BEGIN
188 --
189 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
190 --
191 IF l_debug_on IS NULL
192 THEN
193 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
194 END IF;
195 --
196 IF l_debug_on THEN
197 wsh_debug_sv.push(l_module_name, 'Send_Cbod_FailureWF');
198 wsh_debug_sv.log(l_module_name, 'P_item_type',P_item_type);
199 wsh_debug_sv.log(l_module_name, 'P_item_key',P_item_key);
200 wsh_debug_sv.log(l_module_name, 'P_actid',P_actid);
201 wsh_debug_sv.log(l_module_name, 'P_funcmode',P_funcmode);
202 END IF;
203
204 IF ( P_funcmode = 'RUN' ) THEN
205
206 l_Return_Status := WSH_UTIL_CORE.g_ret_sts_success;
207
208 Send_Cbod_Failure (P_item_type, P_item_key, l_Return_Status);
209 IF l_debug_on THEN
210 wsh_debug_sv.log (l_module_name, 'Return status after Send_Cbod_Failure ', l_Return_Status);
211 END IF;
212
213 IF ( l_Return_Status = WSH_UTIL_CORE.g_ret_sts_success ) THEN
214 X_resultout := 'COMPLETE:SUCCESS';
215 ELSE
216 X_resultout := 'COMPLETE:ERROR';
217 END IF;
218 END IF;
219 IF l_debug_on THEN
220 wsh_debug_sv.log(l_module_name, 'X_resultout',X_resultout);
221 wsh_debug_sv.pop(l_module_name);
222 END IF;
223
224 EXCEPTION
225
226 WHEN OTHERS THEN
227 X_resultout := 'COMPLETE:ERROR';
228 IF l_debug_on THEN
229 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
230 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
231 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
232 END IF;
233 RAISE;
234 END Send_Cbod_Failure_WF;
235
236
237 /*===========================================================================
238 | |
239 | PROCEDURE NAME Raise_Cancel_Event_WF |
240 | |
241 | DESCRIPTION This procedure is called from the work flow to trigger |
242 | the procedure Raise_Cancel_Event, which will raise an |
243 | event for cancelling the previous WF instance. |
244 | |
245 | MODIFICATION HISTORY |
246 | |
247 | 02/18/02 Vijay Nandula Created |
248 | |
249 ============================================================================*/
250
251 PROCEDURE Raise_Cancel_Event_WF ( P_item_type IN VARCHAR2,
252 P_item_key IN VARCHAR2,
253 P_actid IN NUMBER,
254 P_funcmode IN VARCHAR2,
255 X_resultout OUT NOCOPY VARCHAR2 )
256 IS
257 l_Return_Status VARCHAR2(1);
258 --
259 l_debug_on BOOLEAN;
260 --
261 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'RAISE_CANCEL_EVENT_WF';
262 --
263 BEGIN
264 --
265 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
266 --
267 IF l_debug_on IS NULL
268 THEN
269 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
270 END IF;
271 --
272 IF l_debug_on THEN
273 wsh_debug_sv.push(l_module_name, 'Raise_Cancel_Event_WF');
274 wsh_debug_sv.log(l_module_name, 'P_item_type',P_item_type);
275 wsh_debug_sv.log(l_module_name, 'P_item_key',P_item_key);
276 wsh_debug_sv.log(l_module_name, 'P_actid',P_actid);
277 wsh_debug_sv.log(l_module_name, 'P_funcmode',P_funcmode);
278 END IF;
279
280 IF ( P_funcmode = 'RUN' ) THEN
281
282 l_Return_Status := WSH_UTIL_CORE.g_ret_sts_success;
283 Raise_Cancel_Event (P_item_type, P_item_key, l_Return_Status);
284 IF l_debug_on THEN
285 wsh_debug_sv.log (l_module_name, 'Return status after Raise_Cancel_Event ', l_Return_Status);
286 END IF;
287
288 IF ( l_Return_Status = WSH_UTIL_CORE.g_ret_sts_success ) THEN
289 X_resultout := 'COMPLETE:SUCCESS';
290 ELSE
291 X_resultout := 'COMPLETE:ERROR';
292 END IF;
293 END IF;
294
295 IF l_debug_on THEN
296 wsh_debug_sv.log(l_module_name, 'X_resultout',X_resultout);
297 wsh_debug_sv.pop(l_module_name);
298 END IF;
299 EXCEPTION
300
301 WHEN OTHERS THEN
302 X_resultout := 'COMPLETE:ERROR';
303 IF l_debug_on THEN
304 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
305 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
306 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
307 END IF;
308 RAISE;
309 END Raise_Cancel_Event_WF;
310
311 -- ---------------------------------------------------------------------
312 -- Procedure: Raise_Close_Event_WF
313 --
314 -- Parameters: Item_Type IN VARCHAR2
315 -- Item_Key IN VARCHAR2
316 -- Actid IN NUMBER
317 -- Funcmode IN VARCHAR2
318 -- Resultout OUT VARCHAR2
319 --
320 -- Description: This procedure is called from Inbound workflow (WSHSTNDI) to
321 -- trigger the API Raise_Close_Event that intern calls the business
322 -- event oracle.apps.wsh.standalone.spwf to close all the previous
323 -- error out revision of Shipment Request
324 -- Created: Standalone WMS Project
325 -- -----------------------------------------------------------------------
326 PROCEDURE Raise_Close_Event_WF ( P_item_type IN VARCHAR2,
327 P_item_key IN VARCHAR2,
328 P_actid IN NUMBER,
329 P_funcmode IN VARCHAR2,
330 X_resultout OUT NOCOPY VARCHAR2 )
331 IS
332 l_Return_Status VARCHAR2(1);
333 --
334 l_debug_on BOOLEAN;
335 --
336 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'RAISE_CLOSE_EVENT_WF';
337 --
338 BEGIN
339 --
340 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
341 --
342 IF l_debug_on IS NULL
343 THEN
344 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
345 END IF;
346 --
347 IF l_debug_on THEN
348 wsh_debug_sv.push(l_module_name, 'Raise_Close_Event_WF');
349 wsh_debug_sv.log(l_module_name, 'P_item_type',P_item_type);
350 wsh_debug_sv.log(l_module_name, 'P_item_key',P_item_key);
351 wsh_debug_sv.log(l_module_name, 'P_actid',P_actid);
352 wsh_debug_sv.log(l_module_name, 'P_funcmode',P_funcmode);
353 END IF;
354
355 IF ( P_funcmode = 'RUN' ) THEN
356
357 l_Return_Status := WSH_UTIL_CORE.g_ret_sts_success;
358 Raise_Close_Event (P_item_type, P_item_key, l_Return_Status);
359 IF l_debug_on THEN
360 wsh_debug_sv.log (l_module_name, 'Return status after Raise_Close_Event ', l_Return_Status);
361 END IF;
362
363 IF ( l_Return_Status = WSH_UTIL_CORE.g_ret_sts_success ) THEN
364 X_resultout := 'COMPLETE:SUCCESS';
365 ELSE
366 X_resultout := 'COMPLETE:ERROR';
367 END IF;
368 END IF;
369
370 IF l_debug_on THEN
371 wsh_debug_sv.log(l_module_name, 'X_resultout',X_resultout);
372 wsh_debug_sv.pop(l_module_name);
373 END IF;
374 EXCEPTION
375
376 WHEN OTHERS THEN
377 X_resultout := 'COMPLETE:ERROR';
378 IF l_debug_on THEN
379 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
380 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
381 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
382 END IF;
383 RAISE;
384 END Raise_Close_Event_WF;
385
386
387 /*===========================================================================
388 | |
389 | PROCEDURE NAME Send_Cbod_Success |
390 | |
391 | DESCRIPTION This procedure sends a Conformation BOD to the supplier, |
392 | if the cancellation requiest is successfully completed. |
393 | |
394 | MODIFICATION HISTORY |
395 | |
396 | 02/18/02 Vijay Nandula Created |
397 | |
398 ============================================================================*/
399
400 PROCEDURE Send_Cbod_Success ( P_item_type IN VARCHAR2,
401 P_item_key IN VARCHAR2,
402 X_Return_Status OUT NOCOPY VARCHAR2 )
403 IS
404 l_txn_hist_record WSH_TRANSACTIONS_HISTORY_PKG.Txns_History_Record_Type;
405 l_Return_Status VARCHAR2 (1);
406 l_Cbod_Status VARCHAR2 (5) := '00';
407
408 wsh_get_txns_hist_error EXCEPTION;
409 wsh_raise_event_error EXCEPTION;
410 --
411 l_debug_on BOOLEAN;
412 --
413 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'SEND_CBOD_SUCCESS';
414 --
415 BEGIN
416 --
417 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
418 --
419 IF l_debug_on IS NULL
420 THEN
421 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
422 END IF;
423 --
424 IF l_debug_on THEN
425 wsh_debug_sv.push(l_module_name, 'Send_Cbod_Success');
426 wsh_debug_sv.log(l_module_name, 'P_item_type',P_item_type);
427 wsh_debug_sv.log(l_module_name, 'P_item_key',P_item_key);
428 END IF;
429
430 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_success;
431
432 WSH_TRANSACTIONS_HISTORY_PKG.Get_Txns_History ( P_item_type,
433 P_item_key,
434 'I',
435 'SR',
436 l_txn_hist_record,
437 l_Return_Status);
438 IF l_debug_on THEN
439 wsh_debug_sv.log (l_module_name, 'Return status after Get_Txns_History ', l_Return_Status);
440 END IF;
441
442 IF (l_Return_Status <> WSH_UTIL_CORE.g_ret_sts_success ) THEN
443 RAISE wsh_get_txns_hist_error;
444 END IF;
445
446 IF l_debug_on THEN
447 wsh_debug_sv.log (l_module_name, 'txn_history.Transaction Status ', l_txn_hist_record.transaction_status);
448 END IF;
449 -- Change the Event Name before calling Raise Event
450 -- R12.1.1 STANDALONE PROJECT
451 -- LSP PROJECT : consider LSP mode also.
452 IF ( WMS_DEPLOY.WMS_DEPLOYMENT_MODE = 'D' OR (WMS_DEPLOY.WMS_DEPLOYMENT_MODE = 'L' AND l_txn_hist_record.client_code IS NOT NULL ) ) THEN
453 l_txn_hist_record.Event_Name := 'oracle.apps.wsh.standalone.scbod';
454 ELSE
455 l_txn_hist_record.Event_Name := 'oracle.apps.wsh.tpw.scbod';
456 END IF;
457
458 l_txn_hist_record.Event_Key := NULL;
459
460 WSH_EXTERNAL_INTERFACE_SV.Raise_Event ( l_txn_hist_record, l_Cbod_Status, l_Return_Status );
461
462 IF l_debug_on THEN
463 wsh_debug_sv.log (l_module_name, 'Return status after Raise_Event ', l_Return_Status);
464 END IF;
465
466 IF (l_Return_Status <> WSH_UTIL_CORE.g_ret_sts_success ) THEN
467 RAISE wsh_raise_event_error;
468 END IF;
469
470 IF l_debug_on THEN
471 wsh_debug_sv.pop(l_module_name);
472 END IF;
473 EXCEPTION
474 WHEN wsh_get_txns_hist_error THEN
475 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_error;
476 IF l_debug_on THEN
477 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_get_txns_hist_error has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
478 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_get_txns_hist_error');
479 END IF;
480
481 WHEN wsh_raise_event_error THEN
482 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_error;
483 IF l_debug_on THEN
484 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_raise_event_error exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
485 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_raise_event_error');
486 END IF;
487
488 WHEN OTHERS THEN
489 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
490 IF l_debug_on THEN
491 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
492 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
493 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
494 END IF;
495 END Send_Cbod_Success;
496
497
498 /*===========================================================================
499 | |
500 | PROCEDURE NAME Send_Cbod_Failure |
501 | |
502 | DESCRIPTION This procedure sends a failure Conformation BOD to the |
503 | supplier if (a) a cancellation is not processed or (b) |
504 | if the cancellation is processed, then failure to the |
505 | work flow process which sent the original transaction. |
506 | MODIFICATION HISTORY |
507 | |
508 | 02/18/02 Vijay Nandula Created |
509 | |
510 ============================================================================*/
511
512 PROCEDURE Send_Cbod_Failure ( P_item_type IN VARCHAR2,
513 P_item_key IN VARCHAR2,
514 X_Return_Status OUT NOCOPY VARCHAR2 )
515 IS
516 l_txn_hist_record WSH_TRANSACTIONS_HISTORY_PKG.Txns_History_Record_Type;
517 l_Return_Status VARCHAR2 (1);
518 l_Cbod_Status VARCHAR2 (5) := '99';
519
520 l_Event_Name VARCHAR2 (120);
521 l_Event_Key VARCHAR2 (30);
522
523 -- LSP PROJECT : added wndi table to get client_code from wndi.
524 CURSOR l_txn_hist_cur (c_Orig_Document_Number VARCHAR2,
525 c_Trading_Partner_ID NUMBER)
526 IS
527 SELECT wth.Event_Key,
528 wth.Item_Type,
529 wth.Internal_Control_Number
530 FROM wsh_transactions_history wth
531 WHERE wth.Transaction_ID = (SELECT MAX (Transaction_ID)
532 FROM wsh_transactions_history
533 WHERE Document_Number = c_Orig_Document_Number
534 AND Trading_Partner_ID = c_Trading_Partner_ID
535 AND Document_Direction = 'I'
536 AND Action_Type = 'A');
537
538 wsh_get_txns_hist_error EXCEPTION;
539 wsh_raise_event_error EXCEPTION;
540 wsh_orig_txns_hist_error EXCEPTION;
541 --
542 l_debug_on BOOLEAN;
543 --
544 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'SEND_CBOD_FAILURE';
545 --
546 BEGIN
547 --
548 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
549 --
550 IF l_debug_on IS NULL
551 THEN
552 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
553 END IF;
554 --
555 IF l_debug_on THEN
556 wsh_debug_sv.push(l_module_name, 'Send_Cbod_Failure');
557 wsh_debug_sv.log(l_module_name, 'P_item_type',P_item_type);
558 wsh_debug_sv.log(l_module_name, 'P_item_key',P_item_key);
559 END IF;
560
561 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_success;
562
563 WSH_TRANSACTIONS_HISTORY_PKG.Get_Txns_History ( P_item_type,
564 P_item_key,
565 'I',
566 'SR',
567 l_txn_hist_record,
568 l_Return_Status );
569
570 IF l_debug_on THEN
571 wsh_debug_sv.log (l_module_name, 'Return status after Get_Txns_History ', l_Return_Status);
572 END IF;
573
574 IF (l_Return_Status <> WSH_UTIL_CORE.g_ret_sts_success ) THEN
575 RAISE wsh_get_txns_hist_error;
576 END IF;
577
578 -- Check if the error message is for the current WF or the Original WF
579 -- If the Status is populated with 'success' then it is for original instance
580 -- else it is for the current WF instance.
581 IF l_debug_on THEN
582 wsh_debug_sv.log (l_module_name, 'Transaction Status ', l_txn_hist_record.Transaction_Status);
583 wsh_debug_sv.log (l_module_name, 'Action Type ', l_txn_hist_record.Action_Type);
584 END IF;
585
586 -- Before calling the Raise Event Update the l_txn_hist_record.Event_Name
587 -- with appropriate value. Is this 'oracle.apps.wsh.tpw.scbod' ??
588
589 IF l_debug_on THEN
590 wsh_debug_sv.log (l_module_name, 'Before Calling the Raise_Event ', l_Cbod_Status);
591 END IF;
592 -- Change the Event Name before calling Raise Event
593 -- R12.1.1 STANDALONE PROJECT
594 -- LSP PROJECT : consider LSP mode also.
595 IF ( WMS_DEPLOY.WMS_DEPLOYMENT_MODE = 'D' OR (WMS_DEPLOY.WMS_DEPLOYMENT_MODE = 'L' AND l_txn_hist_record.client_code IS NOT NULL)) THEN
596 l_txn_hist_record.Event_Name := 'oracle.apps.wsh.standalone.scbod';
597 ELSE
598
599 IF (l_txn_hist_record.Transaction_Status = 'SC' AND
600 l_txn_hist_record.Action_Type = 'D') THEN
601
602 -- Get the original transaction record from the Transaction History table.
603 OPEN l_txn_hist_cur (l_txn_hist_record.Orig_Document_Number, l_txn_hist_record.Trading_Partner_ID);
604 FETCH l_txn_hist_cur
605 INTO l_txn_hist_record.Event_Key,
606 l_txn_hist_record.Item_Type,
607 l_txn_hist_record.Internal_Control_Number;
608 IF ( l_txn_hist_cur % NOTFOUND )THEN
609 CLOSE l_txn_hist_cur;
610 IF l_debug_on THEN
611 wsh_debug_sv.log (l_module_name, 'No data found ');
612 END IF;
613 RAISE wsh_orig_txns_hist_error;
614 END IF;
615 CLOSE l_txn_hist_cur;
616 END IF;
617
618 l_txn_hist_record.Event_Name := 'oracle.apps.wsh.tpw.scbod';
619 END IF;
620
621 l_txn_hist_record.Event_Key := NULL;
622
623 -- Call Raise Event procedure to raise the appropriate event.
624 WSH_EXTERNAL_INTERFACE_SV.Raise_Event ( l_txn_hist_record, l_Cbod_Status, l_Return_Status );
625
626 IF l_debug_on THEN
627 wsh_debug_sv.log (l_module_name, 'After calling the Raise_Event, Status ', l_Return_Status);
628 END IF;
629
630 IF (l_Return_Status <> WSH_UTIL_CORE.g_ret_sts_success ) THEN
631 RAISE wsh_raise_event_error;
632 END IF;
633
634 IF l_debug_on THEN
635 wsh_debug_sv.pop(l_module_name);
636 END IF;
637 EXCEPTION
638 WHEN wsh_get_txns_hist_error THEN
639 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_error;
640 IF l_debug_on THEN
641 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_get_txns_hist_error exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
642 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_get_txns_hist_error');
643 END IF;
644
645 WHEN wsh_orig_txns_hist_error THEN
646 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_error;
647 IF l_debug_on THEN
648 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_orig_txns_hist_error exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
649 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_orig_txns_hist_error');
650 END IF;
651
652 WHEN wsh_raise_event_error THEN
653 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_error;
654 IF l_debug_on THEN
655 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_raise_event_error exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
656 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_raise_event_error');
657 END IF;
658
659 WHEN OTHERS THEN
660 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
661 IF l_debug_on THEN
662 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
663 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
664 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
665 END IF;
666 END Send_Cbod_Failure;
667
668
669 /*===========================================================================
670 | |
671 | PROCEDURE NAME Check_Cancel_Allowed |
672 | |
673 | DESCRIPTION This procedure checks if the cancellation request for a |
674 | transaction can be processed or not. If allowed then |
675 | cancellation will be completed. |
676 | MODIFICATION HISTORY |
677 | |
678 | 02/18/02 Vijay Nandula Created |
679 | |
680 ============================================================================*/
681
682 PROCEDURE Check_Cancel_Allowed ( P_item_type IN VARCHAR2,
683 P_item_key IN VARCHAR2,
684 X_Return_Status OUT NOCOPY VARCHAR2 )
685 IS
686 l_txn_hist_record WSH_TRANSACTIONS_HISTORY_PKG.Txns_History_Record_Type;
687 l_Return_Status VARCHAR2 (1);
688 l_new_del_status VARCHAR2 (2);
689 l_Event_Name VARCHAR2 (120);
690 l_Event_Key VARCHAR2 (30);
691 l_Delivery_ID NUMBER;
692
693 l_Orig_Entity_Number VARCHAR2 (30);
694 l_Orig_Trading_Partner_ID NUMBER;
695 l_Orig_Transaction_Status VARCHAR2 (2);
696
697 l_New_Del_Interface_ID NUMBER;
698 -- LSP PROJECT : added wndi table to get client_code from wndi.
699 CURSOR l_orig_txn_hist_cur (c_Orig_Document_Number VARCHAR2,
700 c_Trading_Partner_ID NUMBER)
701 IS
702 SELECT wth.Entity_Number,
703 wth.Trading_Partner_ID,
704 wth.Transaction_Status,
705 wndi.client_code
706 FROM wsh_transactions_history wth,
707 wsh_new_del_interface wndi
708 WHERE wth.Transaction_ID = (SELECT MAX (Transaction_ID)
709 FROM wsh_transactions_history
710 WHERE Document_Number = c_Orig_Document_Number
711 AND Trading_Partner_ID = c_Trading_Partner_ID
712 AND Document_Direction = 'I'
713 AND Action_Type = 'A')
714 AND wth.entity_number = wndi.delivery_interface_id (+);
715
716 CURSOR l_new_del_status_cur (c_Delivery_Name VARCHAR2,
717 c_Organization_ID NUMBER) IS
718 SELECT status_code, Delivery_ID
719 FROM wsh_new_deliveries
720 WHERE Organization_id = c_Organization_ID
721 AND Name = c_Delivery_Name;
722 /*
723
724 CURSOR l_new_del_interface_cur ( c_Name VARCHAR2,
725 c_Org_ID NUMBER ) IS
726 SELECT Delivery_Interface_id
727 FROM wsh_new_del_interface
728 WHERE Name = c_Name
729 AND Organization_ID = c_Org_ID;
730 */
731
732 l_client_code VARCHAR2(200); -- LSP PROJECT
733 --
734 wsh_invalid_item_key EXCEPTION;
735 wsh_get_txns_hist_error EXCEPTION;
736 wsh_orig_txns_hist_error EXCEPTION;
737 wsh_update_history EXCEPTION;
738 wsh_del_interface_rec EXCEPTION;
739 wsh_invalid_delivery_no EXCEPTION;
740 wsh_del_interface_wrapper EXCEPTION;
741
742 --
743 l_debug_on BOOLEAN;
744 --
745 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CHECK_CANCEL_ALLOWED';
746 --
747 --Bugfix 4070732
748 l_api_session_name CONSTANT VARCHAR2(150) := G_PKG_NAME ||'.' || l_module_name;
749 BEGIN
750 --Bugfix 4070732
751 IF WSH_UTIL_CORE.G_START_OF_SESSION_API is null THEN
752 WSH_UTIL_CORE.G_START_OF_SESSION_API := l_api_session_name;
753 WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API := FALSE;
754 END IF;
755 --
756 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
757 --
758 IF l_debug_on IS NULL
759 THEN
760 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
761 END IF;
762 --
763 IF l_debug_on THEN
764 wsh_debug_sv.push(l_module_name, 'Check_Cancel_Allowed');
765 wsh_debug_sv.log(l_module_name, 'P_item_type',P_item_type);
766 wsh_debug_sv.log(l_module_name, 'P_item_key',P_item_key);
767 END IF;
768
769
770 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_success;
771
772 IF ( P_item_type IS NULL OR
773 P_item_key IS NULL ) THEN
774 RAISE wsh_invalid_item_key;
775 END IF;
776
777 -- Get the record from the Transaction History Table.
778 WSH_TRANSACTIONS_HISTORY_PKG.Get_Txns_History ( P_item_type,
779 P_item_key,
780 'I',
781 'SR',
782 l_txn_hist_record,
783 l_Return_Status );
784
785 IF l_debug_on THEN
786 wsh_debug_sv.log (l_module_name, 'Return status after Get_Txns_History ', l_Return_Status);
787 END IF;
788
789 IF (l_Return_Status <> WSH_UTIL_CORE.g_ret_sts_success ) THEN
790 RAISE wsh_get_txns_hist_error;
791 END IF;
792
793 -- Get the Delivery Interface ID from wsh_new_del_interface table for the given record
794 -- in the transaction history table.
795 /*
796 OPEN l_new_del_interface_cur ( l_txn_hist_record.Entity_Number,
797 l_txn_hist_record.Trading_Partner_ID );
798 FETCH l_new_del_interface_cur INTO l_New_Del_Interface_ID;
799 CLOSE l_new_del_interface_cur;
800 */
801
802 l_New_Del_Interface_ID := to_number(l_txn_hist_record.Entity_Number);
803
804
805 IF l_debug_on THEN
806 wsh_debug_sv.log (l_module_name, 'Delivery Interface ID' , l_New_Del_Interface_ID);
807 END IF;
808
809 -- Get the Transaction Record for the Original transaction (which has addition)
810 OPEN l_orig_txn_hist_cur (l_txn_hist_record.Orig_Document_Number, l_txn_hist_record.Trading_Partner_ID);
811 FETCH l_orig_txn_hist_cur
812 INTO l_Orig_Entity_Number, l_Orig_Trading_Partner_ID, l_Orig_Transaction_Status,l_client_code; -- LSP PROJECT.
813
814 IF ( l_orig_txn_hist_cur % NOTFOUND ) THEN
815 CLOSE l_orig_txn_hist_cur;
816 IF l_debug_on THEN
817 wsh_debug_sv.log (l_module_name, 'Original Record Not Found ');
818 END IF;
819 RAISE wsh_orig_txns_hist_error;
820 END IF;
821 CLOSE l_orig_txn_hist_cur;
822
823 IF l_debug_on THEN
824 wsh_debug_sv.log (l_module_name, 'Txn Status for original record ', l_Orig_Transaction_Status);
825 END IF;
826
827 -- R12.1.1 STANDALONE PROJECT
828 -- LSP PROJECT : consider LSP mode also.
829 IF (WMS_DEPLOY.WMS_DEPLOYMENT_MODE = 'D' OR (WMS_DEPLOY.WMS_DEPLOYMENT_MODE = 'L' AND l_client_code IS NOT NULL)) OR ( l_Orig_Transaction_Status = 'IP' ) THEN
830 l_txn_hist_record.transaction_status := 'ER';
831 WSH_TRANSACTIONS_HISTORY_PKG.Create_Update_Txns_History ( l_txn_hist_record,
832 l_txn_hist_record.transaction_id,
833 l_Return_Status );
834 IF l_debug_on THEN
835 wsh_debug_sv.log (l_module_name, 'Return status after Create_Update_Txns_History ', l_Return_Status);
836 END IF;
837 IF ( l_Return_Status <> WSH_UTIL_CORE.g_ret_sts_success ) THEN
838 RAISE wsh_update_history;
839 END IF;
840
841 WSH_PROCESS_INTERFACED_PKG.delete_interface_records ( l_New_Del_Interface_ID,
842 l_Return_Status );
843 IF l_debug_on THEN
844 wsh_debug_sv.log (l_module_name, 'Return status after delete_interface_records ', l_Return_Status);
845 END IF;
846
847 IF ( l_Return_Status <> WSH_UTIL_CORE.g_ret_sts_success ) THEN
848 RAISE wsh_del_interface_rec;
849 END IF;
850
851 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_error;
852
853 --Bugfix 4070732 {
854 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
855 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
856 IF l_debug_on THEN
857 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
858 END IF;
859
860 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
861 x_return_status => l_return_status);
862
863 IF l_debug_on THEN
864 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
865 END IF;
866
867 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
868 x_return_status := l_return_status;
869 END IF;
870
871 END IF;
872 END IF;
873 --}
874 -- End of bug 4070732
875
876 IF l_debug_on THEN
877 wsh_debug_sv.pop(l_module_name);
878 END IF;
879 RETURN;
880 ELSIF ( l_Orig_Transaction_Status = 'ER' ) THEN
881
882 WSH_PROCESS_INTERFACED_PKG.delete_interface_records ( l_New_Del_Interface_ID,
883 l_Return_Status );
884 IF l_debug_on THEN
885 wsh_debug_sv.log (l_module_name, 'Return status after delete_interface_records ', l_Return_Status);
886 END IF;
887
888 IF ( l_Return_Status <> WSH_UTIL_CORE.g_ret_sts_success ) THEN
889 RAISE wsh_del_interface_rec;
890 END IF;
891
892 l_txn_hist_record.transaction_status := 'SC';
893 WSH_TRANSACTIONS_HISTORY_PKG.Create_Update_Txns_History ( l_txn_hist_record,
894 l_txn_hist_record.transaction_id,
895 l_Return_Status );
896 IF l_debug_on THEN
897 wsh_debug_sv.log (l_module_name, 'Return status after Create_Update_Txns_History ', l_Return_Status);
898 END IF;
899 IF ( l_Return_Status <> WSH_UTIL_CORE.g_ret_sts_success ) THEN
900 RAISE wsh_update_history;
901 END IF;
902
903 --Bugfix 4070732 {
904 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
905 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
906 IF l_debug_on THEN
907 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
908 END IF;
909
910 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
911 x_return_status => l_return_status);
912
913 IF l_debug_on THEN
914 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
915 END IF;
916
917 IF (
918 ( l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR )
919 OR ( l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR
920 AND x_return_status <> WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR )
921 )
922 THEN
923 --{
924 x_return_status := l_return_status;
925 --}
926 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING
927 AND x_return_status <> WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
928 THEN
929 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
930 END IF;
931
932 END IF;
933 END IF;
934 --}
935 -- End of bug 4070732
936
937 IF l_debug_on THEN
938 wsh_debug_sv.pop(l_module_name);
939 END IF;
940 RETURN;
941
942 END IF;
943
944 OPEN l_new_del_status_cur (l_Orig_Entity_Number, l_Orig_Trading_Partner_ID);
945 FETCH l_new_del_status_cur INTO l_new_del_status, l_Delivery_ID;
946 IF (l_new_del_status_cur % NOTFOUND) THEN
947 IF l_debug_on THEN
948 wsh_debug_sv.log (l_module_name, 'No data found for l_new_del_status_cur');
949 END IF;
950 CLOSE l_new_del_status_cur;
951 RAISE wsh_invalid_delivery_no;
952 END IF;
953 CLOSE l_new_del_status_cur;
954
955 IF l_debug_on THEN
956 wsh_debug_sv.log (l_module_name, 'Delivery Name' , l_txn_hist_record.entity_number);
957 wsh_debug_sv.log (l_module_name, 'Delivery Status' , l_new_del_status);
958 END IF;
959
960 IF ( l_new_del_status IN ('CL', 'IT', 'CO') ) THEN
961 l_txn_hist_record.transaction_status := 'ER';
962 WSH_TRANSACTIONS_HISTORY_PKG.Create_Update_Txns_History ( l_txn_hist_record,
963 l_txn_hist_record.transaction_id,
964 l_Return_Status );
965 IF l_debug_on THEN
966 wsh_debug_sv.log (l_module_name, 'Return status after Create_Update_Txns_History ', l_Return_Status);
967 END IF;
968 IF ( l_Return_Status <> WSH_UTIL_CORE.g_ret_sts_success ) THEN
969 RAISE wsh_update_history;
970 END IF;
971
972
973 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_error;
974
975 --Bugfix 4070732 {
976 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
977 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
978 IF l_debug_on THEN
979 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
980 END IF;
981
982 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
983 x_return_status => l_return_status);
984
985
986 IF l_debug_on THEN
987 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
988 END IF;
989
990 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
991 x_return_status := l_return_status;
992 END IF;
993
994 END IF;
995 END IF;
996 --}
997 -- End of bug 4070732
998
999 IF l_debug_on THEN
1000 wsh_debug_sv.pop(l_module_name);
1001 END IF;
1002 RETURN;
1003 END IF;
1004
1005 IF l_debug_on THEN
1006 wsh_debug_sv.log (l_module_name, 'New Delivery Interface ID' , l_new_del_interface_id);
1007 wsh_debug_sv.log (l_module_name, 'Delivery ID' , l_delivery_id);
1008 END IF;
1009
1010 WSH_INTERFACE_COMMON_ACTIONS.Delivery_Interface_Wrapper (l_New_Del_Interface_ID,
1011 'CANCEL',
1012 l_Delivery_ID,
1013 l_Return_Status );
1014
1015 IF l_debug_on THEN
1016 wsh_debug_sv.log (l_module_name, 'Return status after Delivery_Interface_Wrapper ', l_Return_Status);
1017 END IF;
1018
1019 IF ( l_Return_Status <> WSH_UTIL_CORE.g_ret_sts_success ) THEN
1020 RAISE wsh_del_interface_wrapper;
1021 ELSE
1022 l_txn_hist_record.transaction_status := 'SC';
1023
1024 WSH_TRANSACTIONS_HISTORY_PKG.Create_Update_Txns_History ( l_txn_hist_record,
1025 l_txn_hist_record.transaction_id,
1026 l_Return_Status );
1027
1028 IF l_debug_on THEN
1029 wsh_debug_sv.log (l_module_name, 'Return status after Create_Update_Txns_History ', l_Return_Status);
1030 END IF;
1031
1032 IF (l_Return_Status <> WSH_UTIL_CORE.g_ret_sts_success ) THEN
1033 RAISE wsh_update_history;
1034 END IF;
1035
1036 WSH_PROCESS_INTERFACED_PKG.delete_interface_records ( l_New_Del_Interface_ID,
1037 l_Return_Status );
1038
1039 IF l_debug_on THEN
1040 wsh_debug_sv.log (l_module_name, 'Return status after delete_interface_records ', l_Return_Status);
1041 END IF;
1042
1043 IF ( l_Return_Status <> WSH_UTIL_CORE.g_ret_sts_success ) THEN
1044 RAISE wsh_del_interface_rec;
1045 END IF;
1046 END IF;
1047
1048 --bug 4070732
1049 --End of the API handling of calls to process_stops_for_load_tender
1050 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name)
1051 THEN
1052 --{
1053 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1054 --{
1055
1056 IF l_debug_on THEN
1057 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Process_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1058 END IF;
1059
1060 WSH_UTIL_CORE.Process_stops_for_load_tender(p_reset_flags => TRUE,
1061 x_return_status => l_return_status);
1062
1063 IF l_debug_on THEN
1064 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1065 END IF;
1066
1067 IF (
1068 ( l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR )
1069 OR ( l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR
1070 AND x_return_status <> WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR )
1071 )
1072 THEN
1073 --{
1074 x_return_status := l_return_status;
1075 --}
1076 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING
1077 AND x_return_status <> WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
1078 THEN
1079 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1080 END IF;
1081
1082
1083 --}
1084 END IF;
1085 --}
1086 END IF;
1087 --bug 4070732
1088
1089 IF l_debug_on THEN
1090 wsh_debug_sv.pop(l_module_name);
1091 END IF;
1092
1093 EXCEPTION
1094 WHEN wsh_invalid_item_key THEN
1095 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_error;
1096
1097 --Bugfix 4070732 {
1098 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
1099 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1100 IF l_debug_on THEN
1101 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1102 END IF;
1103
1104 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
1105 x_return_status => l_return_status);
1106
1107 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1108 x_return_status := l_return_status;
1109 END IF;
1110
1111
1112 IF l_debug_on THEN
1113 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1114 END IF;
1115
1116 END IF;
1117 END IF;
1118 --}
1119 -- End of bug 383969
1120
1121 IF l_debug_on THEN
1122 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_invalid_item_key exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1123 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_invalid_item_key');
1124 END IF;
1125
1126 WHEN wsh_get_txns_hist_error THEN
1127 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_error;
1128 --Bugfix 4070732 {
1129 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
1130 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1131 IF l_debug_on THEN
1132 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1133 END IF;
1134
1135 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
1136 x_return_status => l_return_status);
1137
1138
1139 IF l_debug_on THEN
1140 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1141 END IF;
1142
1143 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1144 x_return_status := l_return_status;
1145 END IF;
1146
1147 END IF;
1148 END IF;
1149 --}
1150 -- End of bug 383969
1151
1152 IF l_debug_on THEN
1153 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_get_txns_hist_error exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1154 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_get_txns_hist_error');
1155 END IF;
1156
1157 WHEN wsh_orig_txns_hist_error THEN
1158 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_error;
1159 --Bugfix 4070732 {
1160 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
1161 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1162 IF l_debug_on THEN
1163 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1164 END IF;
1165
1166 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
1167 x_return_status => l_return_status);
1168
1169
1170 IF l_debug_on THEN
1171 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1172 END IF;
1173
1174 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1175 x_return_status := l_return_status;
1176 END IF;
1177 END IF;
1178 END IF;
1179 --}
1180 -- End of bug 383969
1181
1182 IF l_debug_on THEN
1183 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_orig_txns_hist_error exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1184 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_orig_txns_hist_error');
1185 END IF;
1186
1187 WHEN wsh_update_history THEN
1188 X_return_status := WSH_UTIL_CORE.g_ret_sts_error;
1189
1190 --Bugfix 4070732 {
1191 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
1192 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1193 IF l_debug_on THEN
1194 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1195 END IF;
1196
1197 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
1198 x_return_status => l_return_status);
1199
1200
1201 IF l_debug_on THEN
1202 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1203 END IF;
1204
1205 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1206 x_return_status := l_return_status;
1207 END IF;
1208
1209 END IF;
1210 END IF;
1211 --}
1212 -- End of bug 383969
1213
1214 IF l_debug_on THEN
1215 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_update_history exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1216 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_update_history');
1217 END IF;
1218
1219 WHEN wsh_del_interface_rec THEN
1220 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_error;
1221
1222 --Bugfix 4070732 {
1223 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
1224 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1225 IF l_debug_on THEN
1226 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1227 END IF;
1228
1229 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
1230 x_return_status => l_return_status);
1231
1232 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1233 x_return_status := l_return_status;
1234 END IF;
1235
1236
1237 IF l_debug_on THEN
1238 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1239 END IF;
1240
1241 END IF;
1242 END IF;
1243 --}
1244 -- End of bug 383969
1245
1246 IF l_debug_on THEN
1247 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_del_interface_rec exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1248 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_del_interface_rec');
1249 END IF;
1250
1251 WHEN wsh_invalid_delivery_no THEN
1252 X_return_status := WSH_UTIL_CORE.g_ret_sts_error;
1253
1254 --Bugfix 4070732 {
1255 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
1256 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1257 IF l_debug_on THEN
1258 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1259 END IF;
1260
1261 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
1262 x_return_status => l_return_status);
1263
1264
1265 IF l_debug_on THEN
1266 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1267 END IF;
1268
1269 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1270 x_return_status := l_return_status;
1271 END IF;
1272
1273 END IF;
1274 END IF;
1275 --}
1276 -- End of bug 383969
1277
1278 IF l_debug_on THEN
1279 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_invalid_delivery_no exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1280 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_invalid_delivery_no');
1281 END IF;
1282
1283 WHEN wsh_del_interface_wrapper THEN
1284 l_txn_hist_record.transaction_status := 'ER';
1285 WSH_TRANSACTIONS_HISTORY_PKG.Create_Update_Txns_History ( l_txn_hist_record,
1286 l_txn_hist_record.transaction_id,
1287 l_Return_Status );
1288 IF ( l_Return_Status <> WSH_UTIL_CORE.g_ret_sts_success ) THEN
1289 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
1290 END IF;
1291
1292 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_error;
1293
1294 --Bugfix 4070732 {
1295 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
1296 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1297 IF l_debug_on THEN
1298 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1299 END IF;
1300
1301 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
1302 x_return_status => l_return_status);
1303
1304
1305 IF l_debug_on THEN
1306 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1307 END IF;
1308
1309 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1310 x_return_status := l_return_status;
1311 END IF;
1312
1313 END IF;
1314 END IF;
1315 --}
1316 -- End of bug 383969
1317
1318 IF l_debug_on THEN
1319 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_del_interface_wrapper exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1320 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_del_interface_wrapper');
1321 END IF;
1322
1323 WHEN OTHERS THEN
1324 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
1325
1326 --Bugfix 4070732 {
1327 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
1328 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1329 IF l_debug_on THEN
1330 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1331 END IF;
1332
1333 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
1334 x_return_status => l_return_status);
1335
1336
1337 IF l_debug_on THEN
1338 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1339 END IF;
1340
1341 END IF;
1342 END IF;
1343 --}
1344 -- End of bug 383969
1345
1346 IF l_debug_on THEN
1347 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
1348 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1349 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1350 END IF;
1351 END Check_Cancel_Allowed;
1352
1353
1354 /*===========================================================================
1355 | |
1356 | PROCEDURE NAME Raise_Cancel_Event |
1357 | |
1358 | DESCRIPTION This procedure raises a WF event for cancelling the |
1359 | previous WF instance. |
1360 | MODIFICATION HISTORY |
1361 | |
1362 | 02/18/02 Vijay Nandula Created |
1363 | |
1364 ============================================================================*/
1365
1366 PROCEDURE Raise_Cancel_Event ( P_item_type IN VARCHAR2,
1367 P_item_key IN VARCHAR2,
1368 X_return_Status OUT NOCOPY VARCHAR2 )
1369 IS
1370 l_txn_hist_record WSH_TRANSACTIONS_HISTORY_PKG.Txns_History_Record_Type;
1371 l_orig_txn_hist_record WSH_TRANSACTIONS_HISTORY_PKG.Txns_History_Record_Type;
1372 l_Return_Status VARCHAR2 (1);
1373 l_Event_Name VARCHAR2 (120);
1374 l_Event_Key VARCHAR2 (30);
1375
1376 CURSOR l_txn_hist_cur (c_Item_Type VARCHAR2,
1377 c_Item_Key VARCHAR2)
1378 IS
1379 SELECT wth1.Transaction_ID,
1380 wth1.Document_Type,
1381 wth1.Document_Direction,
1382 wth1.Document_Number,
1383 wth1.Orig_Document_Number,
1384 wth1.Entity_Number,
1385 wth1.Entity_Type,
1386 wth1.Trading_Partner_ID,
1387 wth1.Action_Type,
1388 wth1.Transaction_Status,
1389 wth1.ECX_Message_ID,
1390 wth1.Event_Name,
1391 wth1.Event_Key,
1392 wth1.Item_Type,
1393 wth1.Internal_Control_Number,
1394 -- R12.1.1 STANDALONE PROJECT
1395 wth1.document_revision,
1396 wth1.Attribute_Category,
1397 wth1.Attribute1,
1398 wth1.Attribute2,
1399 wth1.Attribute3,
1400 wth1.Attribute4,
1401 wth1.Attribute5,
1402 wth1.Attribute6,
1403 wth1.Attribute7,
1404 wth1.Attribute8,
1405 wth1.Attribute9,
1406 wth1.Attribute10,
1407 wth1.Attribute11,
1408 wth1.Attribute12,
1409 wth1.Attribute13,
1410 wth1.Attribute14,
1411 wth1.Attribute15,
1412 NULL -- LSP PROJECT
1413 FROM wsh_transactions_history wth1,
1414 wsh_transactions_history wth2
1415 WHERE wth2.Item_Type = c_Item_Type
1416 AND wth2.Event_Key = c_Item_Key
1417 AND wth1.Document_Number = wth2.Orig_Document_Number
1418 AND wth1.Trading_Partner_ID = wth2.Trading_Partner_ID
1419 AND wth1.Document_Direction = 'I'
1420 AND wth1.Action_Type = 'A';
1421
1422 wsh_orig_txns_hist_error EXCEPTION;
1423 wsh_raise_event_error EXCEPTION;
1424 --
1425 l_debug_on BOOLEAN;
1426 --
1427 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'RAISE_CANCEL_EVENT';
1428 --
1429 BEGIN
1430 --
1431 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1432 --
1433 IF l_debug_on IS NULL
1434 THEN
1435 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1436 END IF;
1437 --
1438 IF l_debug_on THEN
1439 wsh_debug_sv.push(l_module_name, 'Raise_Cancel_Event');
1440 wsh_debug_sv.log(l_module_name, 'P_item_type',P_item_type);
1441 wsh_debug_sv.log(l_module_name, 'P_item_key',P_item_key);
1442 END IF;
1443
1444 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_success;
1445
1446 OPEN l_txn_hist_cur (P_Item_Type, P_Item_Key);
1447 FETCH l_txn_hist_cur
1448 INTO l_orig_txn_hist_record;
1449 IF ( l_txn_hist_cur % NOTFOUND ) THEN
1450 IF l_debug_on THEN
1451 wsh_debug_sv.log (l_module_name, 'No data found for l_txn_hist_cur ');
1452 END IF;
1453
1454 CLOSE l_txn_hist_cur;
1455 RAISE wsh_orig_txns_hist_error;
1456 END IF;
1457 CLOSE l_txn_hist_cur;
1458
1459 l_orig_txn_hist_record.Event_Name := 'oracle.apps.wsh.tpw.spwf';
1460 IF l_debug_on THEN
1461 wsh_debug_sv.log (l_module_name, 'Before Raise_Event : Event Name ' , l_orig_txn_hist_record.Event_Name);
1462 wsh_debug_sv.log (l_module_name, 'Event key ' , l_orig_txn_hist_record.Event_Key);
1463 END IF;
1464 WSH_EXTERNAL_INTERFACE_SV.Raise_Event ( l_orig_txn_hist_record,
1465 NULL,
1466 l_Return_Status );
1467
1468 IF l_debug_on THEN
1469 wsh_debug_sv.log (l_module_name, 'Return status after Raise_Event ', l_Return_Status);
1470 END IF;
1471
1472 IF (l_Return_Status <> WSH_UTIL_CORE.g_ret_sts_success ) THEN
1473 RAISE wsh_raise_event_error;
1474 END IF;
1475
1476 IF l_debug_on THEN
1477 wsh_debug_sv.pop(l_module_name);
1478 END IF;
1479 EXCEPTION
1480 WHEN wsh_orig_txns_hist_error THEN
1481 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_error;
1482 IF l_debug_on THEN
1483 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_orig_txns_hist_error exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1484 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_orig_txns_hist_error');
1485 END IF;
1486
1487 WHEN wsh_raise_event_error THEN
1488 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_error;
1489 IF l_debug_on THEN
1490 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_raise_event_error exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1491 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_raise_event_error');
1492 END IF;
1493
1494 WHEN OTHERS THEN
1495 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
1496 IF l_debug_on THEN
1497 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
1498 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1499 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1500 END IF;
1501 END Raise_Cancel_Event;
1502
1503 -- R12.1.1 STANDALONE PROJECT
1504 -- ---------------------------------------------------------------------
1505 -- Procedure: Raise_Close_Event
1506 --
1507 -- Parameters: P_Item_Type IN VARCHAR2
1508 -- P_Item_Key IN VARCHAR2
1509 -- X_return_Status VARCHAR2
1510 --
1511 -- Description: This procedure is called from Raise_Close_Event_WF API to
1512 -- trigger the business event oracle.apps.wsh.standalone.spwf and
1513 --- close all the previous error out revision of Shipment Request
1514 -- Created: Standalone WMS Project
1515 -- -----------------------------------------------------------------------
1516 PROCEDURE Raise_Close_Event ( P_item_type IN VARCHAR2,
1517 P_item_key IN VARCHAR2,
1518 X_return_Status OUT NOCOPY VARCHAR2 )
1519 IS
1520
1521 pragma AUTONOMOUS_TRANSACTION;
1522
1523 l_txn_hist_record WSH_TRANSACTIONS_HISTORY_PKG.Txns_History_Record_Type;
1524 l_orig_txn_hist_record WSH_TRANSACTIONS_HISTORY_PKG.Txns_History_Record_Type;
1525 l_Return_Status VARCHAR2 (1);
1526 l_Event_Name VARCHAR2 (120);
1527 l_Event_Key VARCHAR2 (30);
1528
1529 CURSOR l_stnd_txn_hist_cur (c_Item_Type VARCHAR2,
1530 c_Item_Key VARCHAR2)
1531 IS
1532 SELECT wth1.Transaction_ID,
1533 wth1.Document_Type,
1534 wth1.Document_Direction,
1535 wth1.Document_Number,
1536 wth1.Document_Number Orig_Document_Number,
1537 wth1.Entity_Number,
1538 wth1.Entity_Type,
1539 wth1.Trading_Partner_ID,
1540 wth1.Action_Type,
1541 wth1.Transaction_Status,
1542 wth1.ECX_Message_ID,
1543 wth1.Event_Name,
1544 wth1.Event_Key,
1545 wth1.Item_Type,
1546 wth1.Internal_Control_Number,
1547 wth1.document_revision,
1548 wth1.Attribute_Category,
1549 wth1.Attribute1,
1550 wth1.Attribute2,
1551 wth1.Attribute3,
1552 wth1.Attribute4,
1553 wth1.Attribute5,
1554 wth1.Attribute6,
1555 wth1.Attribute7,
1556 wth1.Attribute8,
1557 wth1.Attribute9,
1558 wth1.Attribute10,
1559 wth1.Attribute11,
1560 wth1.Attribute12,
1561 wth1.Attribute13,
1562 wth1.Attribute14,
1563 wth1.Attribute15,
1564 NULL -- LSP PROJECT
1565 FROM wsh_transactions_history wth1,
1566 wsh_transactions_history wth2
1567 WHERE wth2.Item_Type = c_Item_Type
1568 AND wth2.Event_Key = c_Item_Key
1569 AND wth1.Document_Number = wth2.Document_Number
1570 AND wth1.Trading_Partner_ID = wth2.Trading_Partner_ID
1571 AND wth1.Document_Revision < wth2.Document_Revision
1572 AND wth1.Document_Direction = 'I'
1573 AND wth2.Document_Direction = 'I'
1574 AND wth1.Transaction_Status in ('AP', 'ER')
1575 AND wth1.Event_Name is not null
1576 AND wth1.Event_Key is not null;
1577
1578 wsh_orig_txns_hist_error EXCEPTION;
1579 wsh_raise_event_error EXCEPTION;
1580 --
1581 l_debug_on BOOLEAN;
1582 --
1583 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'RAISE_CLOSE_EVENT';
1584 --
1585 BEGIN
1586 --
1587 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1588 --
1589 IF l_debug_on IS NULL
1590 THEN
1591 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1592 END IF;
1593 --
1594 IF l_debug_on THEN
1595 wsh_debug_sv.push(l_module_name, 'Raise_Close_Event');
1596 wsh_debug_sv.log(l_module_name, 'P_item_type',P_item_type);
1597 wsh_debug_sv.log(l_module_name, 'P_item_key',P_item_key);
1598 END IF;
1599
1600 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_success;
1601
1602 FOR l_orig_txn_hist_record IN l_stnd_txn_hist_cur(P_Item_Type, P_Item_Key) LOOP
1603
1604 l_orig_txn_hist_record.Event_Name := 'oracle.apps.wsh.standalone.spwf';
1605
1606 IF l_debug_on THEN
1607 wsh_debug_sv.log (l_module_name, 'Before Raise_Event : Event Name ' , l_orig_txn_hist_record.Event_Name);
1608 wsh_debug_sv.log (l_module_name, 'Event key ' , l_orig_txn_hist_record.Event_Key);
1609 END IF;
1610
1611 WSH_EXTERNAL_INTERFACE_SV.Raise_Event ( l_orig_txn_hist_record,
1612 NULL,
1613 l_Return_Status );
1614
1615 IF l_debug_on THEN
1616 wsh_debug_sv.log (l_module_name, 'Return status after Raise_Event ', l_Return_Status);
1617 END IF;
1618
1619 IF (l_Return_Status <> WSH_UTIL_CORE.g_ret_sts_success ) THEN
1620 RAISE wsh_raise_event_error;
1621 END IF;
1622
1623 END LOOP;
1624
1625 COMMIT;
1626
1627 IF l_debug_on THEN
1628 wsh_debug_sv.pop(l_module_name);
1629 END IF;
1630 EXCEPTION
1631 WHEN wsh_orig_txns_hist_error THEN
1632 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_error;
1633 IF l_debug_on THEN
1634 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_orig_txns_hist_error exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1635 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_orig_txns_hist_error');
1636 END IF;
1637
1638 WHEN wsh_raise_event_error THEN
1639 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_error;
1640 IF l_debug_on THEN
1641 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_raise_event_error exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1642 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_raise_event_error');
1643 END IF;
1644
1645 WHEN OTHERS THEN
1646 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
1647 IF l_debug_on THEN
1648 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
1649 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1650 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1651 END IF;
1652 END Raise_Close_Event;
1653
1654 /*===========================================================================
1655 | |
1656 | PROCEDURE NAME Send_Shipment_Advice |
1657 | |
1658 | DESCRIPTION This procedure checks if Warehouse Shipping Advice needs |
1659 | to be sent to the Supplier instance or not. If it is |
1660 | required, an event is raised to send the transaction. |
1661 | MODIFICATION HISTORY |
1662 | |
1663 | 02/18/02 Vijay Nandula Created |
1664 | |
1665 ============================================================================*/
1666
1667 PROCEDURE Send_Shipment_Advice ( P_Entity_ID IN NUMBER,
1668 P_Entity_Type IN VARCHAR2,
1669 P_Action_Type IN VARCHAR2,
1670 P_Document_Type IN VARCHAR2,
1671 P_Org_ID IN NUMBER,
1672 X_Return_Status OUT NOCOPY VARCHAR2 )
1673 IS
1674 CURSOR l_orig_txn_hist_cur (c_Entity_Number VARCHAR2,
1675 c_Org_ID NUMBER)
1676 IS
1677 SELECT wth1.Document_Number,
1678 wth1.Transaction_Status,
1679 wth1.Event_Key
1680 FROM wsh_transactions_history wth1
1681 WHERE Transaction_ID = (SELECT MAX (Transaction_ID)
1682 FROM wsh_transactions_history
1683 WHERE Entity_Number = c_Entity_Number
1684 AND Trading_Partner_ID = c_Org_ID
1685 AND Entity_Type = 'DLVY'
1686 AND Action_Type = 'A'
1687 AND Document_Direction = 'I'
1688 AND Document_Type = 'SR');
1689
1690 -- LSP PROJECT : Added client_id
1691 CURSOR l_new_del_status_cur ( c_Delivery_ID VARCHAR2, c_Organization_ID NUMBER ) IS
1692 SELECT Name, status_code,client_id
1693 FROM wsh_new_deliveries
1694 WHERE Organization_id = c_Organization_ID
1695 AND Delivery_ID = c_Delivery_ID;
1696
1697 CURSOR l_del_details_cur (c_Delivery_ID NUMBER, c_Org_ID NUMBER) IS
1698 SELECT 'X'
1699 FROM wsh_delivery_details wdd,
1700 wsh_delivery_assignments_v wda,
1701 wsh_new_deliveries wnd
1702 WHERE wdd.container_flag = 'N'
1703 AND wdd.source_code = 'WSH'
1704 AND wdd.Delivery_Detail_ID = wda.Delivery_Detail_ID
1705 AND wda.Delivery_ID = wnd.Delivery_ID
1706 AND wnd.Delivery_ID = c_Delivery_ID
1707 AND wnd.Organization_ID = c_Org_ID
1708 AND rownum = 1;
1709
1710 cursor l_check_resend_cur(p_entity_number VARCHAR2 ) is
1711 select transaction_status from wsh_transactions_history
1712 where transaction_id =(select max(transaction_id) from wsh_transactions_history
1713 where entity_number= p_entity_number
1714 and trading_partner_id=p_org_id
1715 and document_direction = 'O'
1716 and document_type ='SA'
1717 and entity_type ='DLVY'
1718 and action_type = 'A');
1719
1720 CURSOR get_delivery_details(cp_entity_id NUMBER) IS
1721 SELECT 'X'
1722 FROM wsh_delivery_details wdd,
1723 wsh_delivery_assignments_v wda,
1724 mtl_system_items msi
1725 WHERE wdd.container_flag = 'N'
1726 AND wdd.released_status <> 'D'
1727 AND nvl(wdd.inv_interfaced_flag,'N') <> 'Y'
1728 AND wdd.Delivery_Detail_ID = wda.Delivery_Detail_ID
1729 AND msi.inventory_item_id = wdd.inventory_item_id
1730 AND msi.organization_id = wdd.organization_id
1731 AND msi.serial_number_control_code IN (2,5,6)
1732 AND wda.Delivery_ID = cp_entity_id
1733 AND rownum = 1;
1734
1735 l_orig_document_number VARCHAR2 (120);
1736 l_orig_Transaction_Status VARCHAR2 (2);
1737 l_orig_Event_Key VARCHAR2 (240);
1738 l_curr_txn_hist_record WSH_TRANSACTIONS_HISTORY_PKG.Txns_History_Record_Type;
1739 l_Return_Status VARCHAR2 (1);
1740 l_new_del_status VARCHAR2 (2);
1741 l_Del_Name VARCHAR2 (30);
1742 l_txn_Status VARCHAR2 (2);
1743 l_del_detail_x VARCHAR2 (1);
1744 l_delivery_details VARCHAR2(1);
1745 -- R12.1.1 STANDALONE PROJECT
1746 l_wms_deployment_mode VARCHAR2(1);
1747 l_client_id NUMBER; -- LSP PROJECT
1748
1749 wsh_invalid_delivery_id EXCEPTION;
1750 wsh_invalid_entity_type EXCEPTION;
1751 wsh_invalid_action_type EXCEPTION;
1752 wsh_invalid_doc_type EXCEPTION;
1753 wsh_orig_txns_hist_error EXCEPTION;
1754 wsh_orig_txn_hist_error_status EXCEPTION;
1755 wsh_invalid_del_status EXCEPTION;
1756 wsh_del_details_error EXCEPTION;
1757 wsh_raise_event_error EXCEPTION;
1758 wsh_sa_resend_error EXCEPTION;
1759 wsh_serial_no_inv_interface EXCEPTION;
1760 -- R12.1.1 STANDALONE PROJECT
1761 wsh_interface_to_om_failed EXCEPTION;
1762 --
1763 l_debug_on BOOLEAN;
1764 --
1765 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'SEND_SHIPMENT_ADVICE';
1766 --
1767 BEGIN
1768 --
1769 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1770 --
1771 IF l_debug_on IS NULL
1772 THEN
1773 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1774 END IF;
1775 --
1776 IF l_debug_on THEN
1777 wsh_debug_sv.push(l_module_name, 'Send_Shipment_Advice');
1778 wsh_debug_sv.log (l_module_name, 'P_Entity_ID ', P_Entity_ID);
1779 wsh_debug_sv.log (l_module_name, 'P_Entity_Type ', P_Entity_Type);
1780 wsh_debug_sv.log (l_module_name, 'P_Action_Type ', P_Action_Type);
1781 wsh_debug_sv.log (l_module_name, 'P_Document_Type ', P_Document_Type);
1782 wsh_debug_sv.log (l_module_name, 'P_Org_ID ', P_Org_ID);
1783 END IF;
1784
1785 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_success;
1786
1787 IF ( P_Entity_TYPE <> 'DLVY' ) THEN
1788 RAISE wsh_invalid_entity_type;
1789 ELSIF ( P_Action_TYPE <> 'A' ) THEN
1790 RAISE wsh_invalid_action_type;
1791 ELSIF ( P_Document_TYPE <> 'SA' ) THEN
1792 RAISE wsh_invalid_doc_type;
1793 END IF;
1794
1795 -- Get the Original Delivery Number and status from the delivery table.
1796 OPEN l_new_del_status_cur (P_Entity_ID,
1797 P_Org_ID);
1798 FETCH l_new_del_status_cur INTO l_Del_Name, l_new_del_status,l_client_id; -- LSP PROJECT
1799
1800 IF l_debug_on THEN
1801 wsh_debug_sv.log (l_module_name, 'Delivery Name' , l_Del_Name);
1802 wsh_debug_sv.log (l_module_name, 'Delivery Status' , l_new_del_status);
1803 wsh_debug_sv.log (l_module_name, 'Client Id' , l_client_id); -- LSP PROJECT
1804 END IF;
1805
1806 IF (l_new_del_status_cur % NOTFOUND) THEN
1807 IF l_debug_on THEN
1808 wsh_debug_sv.log (l_module_name, 'No Data found for l_new_del_status_cur');
1809 END IF;
1810 CLOSE l_new_del_status_cur;
1811 RAISE wsh_invalid_delivery_id;
1812 END IF;
1813 CLOSE l_new_del_status_cur;
1814
1815 -- R12.1.1 STANDALONE PROJECT
1816 l_wms_deployment_mode := WMS_DEPLOY.WMS_DEPLOYMENT_MODE;
1817 -- LSP PROJECT : consider LSP mode also.
1818 IF (l_wms_deployment_mode = 'D' OR (l_wms_deployment_mode = 'L' AND l_client_id IS NOT NULL )) THEN
1819 IF (l_new_del_status NOT IN ('IT', 'CL')) THEN
1820 RAISE wsh_invalid_del_status;
1821 END IF;
1822
1823 ELSIF ( l_new_del_status NOT IN ('CL', 'IT', 'CO') ) THEN
1824 RAISE wsh_invalid_del_status;
1825
1826 ELSE
1827 OPEN l_del_details_cur (P_Entity_ID, P_Org_ID);
1828 FETCH l_del_details_cur INTO l_del_detail_x;
1829 CLOSE l_del_details_cur;
1830 IF l_debug_on THEN
1831 wsh_debug_sv.log (l_module_name, 'Value of l_del_details_cur ' , l_del_detail_x);
1832 END IF;
1833 IF (NVL (l_del_detail_x, '-') <> 'X') THEN
1834 RAISE wsh_del_details_error;
1835 END IF;
1836
1837 OPEN get_delivery_details(p_entity_id);
1838 FETCH get_delivery_details INTO l_delivery_details;
1839 IF l_debug_on THEN
1840 wsh_debug_sv.log (l_module_name, 'Value of l_delivery_details' ,l_delivery_details);
1841 END IF;
1842 CLOSE get_delivery_details;
1843
1844 IF (NVL (l_delivery_details, '-') = 'X') THEN
1845 RAISE wsh_serial_no_inv_interface;
1846 END IF;
1847
1848 END IF;
1849
1850 open l_check_resend_cur( l_Del_Name );
1851 fetch l_check_resend_cur into l_txn_Status;
1852 close l_check_resend_cur;
1853
1854 IF l_debug_on THEN
1855 wsh_debug_sv.log (l_module_name, 'Outbound Transaction Status' , l_txn_Status);
1856 END IF;
1857 if ( nvl(l_txn_Status, 'ER') <> 'ER' ) THEN
1858 raise wsh_sa_resend_error;
1859 end if;
1860
1861 -- R12.1.1 STANDALONE PROJECT
1862 -- LSP PROJECT : consider LSP mode also.
1863 IF (l_wms_deployment_mode = 'D' OR (l_wms_deployment_mode = 'L' AND l_client_id IS NOT NULL )) THEN --{
1864 IF l_debug_on THEN
1865 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_SHIP_CONFIRM_ACTIONS.Process_Delivery_To_OM',WSH_DEBUG_SV.C_PROC_LEVEL);
1866 END IF;
1867
1868 WSH_SHIP_CONFIRM_ACTIONS.Process_Delivery_To_OM (
1869 p_delivery_id => p_entity_id,
1870 x_return_status => l_return_status);
1871
1872 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1873 RAISE wsh_interface_to_om_failed;
1874 END IF;
1875 ELSE
1876 -- Get the Original record from the Transaction History Table for the Delivery.
1877 OPEN l_orig_txn_hist_cur (l_Del_Name, P_Org_ID);
1878 FETCH l_orig_txn_hist_cur
1879 INTO l_orig_document_number, l_orig_Transaction_Status, l_orig_Event_Key;
1880 IF (l_orig_txn_hist_cur % NOTFOUND) THEN
1881 IF l_debug_on THEN
1882 wsh_debug_sv.log (l_module_name, 'No Data found for l_orig_txn_hist_cur');
1883 END IF;
1884 CLOSE l_orig_txn_hist_cur;
1885 RAISE wsh_orig_txns_hist_error;
1886 END IF;
1887 CLOSE l_orig_txn_hist_cur;
1888
1889 -- If the Original transaction status is Error or In process then we cannot send a
1890 -- Shipping Advice back to the Supplier.
1891 IF l_debug_on THEN
1892 wsh_debug_sv.log (l_module_name, 'Orig Transaction Status' , l_orig_Transaction_Status);
1893 END IF;
1894 IF ( l_orig_Transaction_Status IN ('ER', 'IP') ) THEN
1895 RAISE wsh_orig_txn_hist_error_status;
1896 END IF;
1897 END IF; --}
1898 -- R12.1.1 STANDALONE PROJECT
1899 IF l_debug_on THEN
1900 wsh_debug_sv.logmsg (l_module_name, 'Updating Delivery Pending_Advice_Flag Status to Null');
1901 END IF;
1902 UPDATE wsh_new_deliveries
1903 SET pending_advice_flag = NULL,
1904 last_update_date = SYSDATE,
1905 last_updated_by = fnd_global.user_id,
1906 program_application_id = fnd_global.prog_appl_id,
1907 program_id = fnd_global.conc_program_id,
1908 program_update_date = SYSDATE,
1909 request_id = fnd_global.conc_request_id
1910 WHERE delivery_id = P_Entity_ID;
1911
1912 -- Build a Transaction Record for the current transaction
1913 l_curr_txn_hist_record.Document_Type := P_Document_Type;
1914 l_curr_txn_hist_record.Document_Direction := 'O';
1915 l_curr_txn_hist_record.Entity_Number := l_Del_Name;
1916 l_curr_txn_hist_record.Entity_Type := P_Entity_TYPE;
1917 -- LSP PROJECT.
1918 IF (l_wms_deployment_mode = 'L' AND l_client_id IS NOT NULL ) THEN
1919 --{
1920 SELECT party_id
1921 INTO l_curr_txn_hist_record.Trading_Partner_ID
1922 FROM hz_cust_accounts_all
1923 WHERE cust_account_id = l_client_id;
1924 ELSE
1925 l_curr_txn_hist_record.Trading_Partner_ID := P_Org_ID;
1926 --}
1927 END IF;
1928 IF l_debug_on THEN
1929 wsh_debug_sv.log (l_module_name, 'Trading Partner Id ' , l_curr_txn_hist_record.Trading_Partner_ID);
1930 END IF;
1931 -- LSP PROJECT : End
1932 l_curr_txn_hist_record.Action_Type := P_Action_Type;
1933 l_curr_txn_hist_record.Transaction_Status := 'IP';
1934 -- R12.1.1 STANDALONE PROJECT
1935 -- LSP PROJECT : consider LSP mode also.
1936 IF (l_wms_deployment_mode = 'D' OR (l_wms_deployment_mode = 'L' AND l_client_id IS NOT NULL )) THEN
1937 l_curr_txn_hist_record.Event_Name := 'oracle.apps.wsh.standalone.ssao';
1938 l_curr_txn_hist_record.Item_Type := 'WSHSTNDO';
1939 ELSE
1940 l_curr_txn_hist_record.Orig_Document_Number := l_orig_Document_Number;
1941 l_curr_txn_hist_record.Event_Name := 'oracle.apps.wsh.tpw.ssao';
1942 l_curr_txn_hist_record.Item_Type := 'WSHTPWI';
1943 END IF;
1944 l_curr_txn_hist_record.Event_Key := l_orig_Event_Key;
1945
1946 -- Raise event will insert the record into the transaction history table
1947 -- for the current transaction.
1948 IF l_debug_on THEN
1949 wsh_debug_sv.log (l_module_name, 'Before Raise_Event');
1950 END IF;
1951
1952 WSH_EXTERNAL_INTERFACE_SV.Raise_Event ( l_curr_txn_hist_record,
1953 NULL,
1954 l_Return_Status );
1955
1956 IF l_debug_on THEN
1957 wsh_debug_sv.log (l_module_name, 'After Raise_Event , return status ' , l_Return_Status);
1958 END IF;
1959
1960 IF (l_Return_Status <> WSH_UTIL_CORE.g_ret_sts_success ) THEN
1961 RAISE wsh_raise_event_error;
1962 END IF;
1963
1964 IF l_debug_on THEN
1965 wsh_debug_sv.pop(l_module_name);
1966 END IF;
1967
1968 EXCEPTION
1969 WHEN wsh_invalid_delivery_id THEN
1970 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_error;
1971 IF l_debug_on THEN
1972 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_invalid_delivery_id exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1973 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_invalid_delivery_id');
1974 END IF;
1975
1976 WHEN wsh_invalid_entity_type THEN
1977 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_error;
1978 IF l_debug_on THEN
1979 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_invalid_entity_type exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1980 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_invalid_entity_type');
1981 END IF;
1982
1983 WHEN wsh_invalid_action_type THEN
1984 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_error;
1985 IF l_debug_on THEN
1986 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_invalid_action_type exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1987 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_invalid_action_type');
1988 END IF;
1989
1990 WHEN wsh_invalid_doc_type THEN
1991 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_error;
1992 IF l_debug_on THEN
1993 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_invalid_doc_type exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1994 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_invalid_doc_type');
1995 END IF;
1996
1997 WHEN wsh_orig_txns_hist_error THEN
1998 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_error;
1999 IF l_debug_on THEN
2000 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_orig_txns_hist_error exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2001 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_orig_txns_hist_error');
2002 END IF;
2003
2004 WHEN wsh_orig_txn_hist_error_status THEN
2005 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_error;
2006 IF l_debug_on THEN
2007 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_orig_txns_hist_error exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2008 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_orig_txns_hist_error');
2009 END IF;
2010 -- R12.1.1 STANDALONE PROJECT
2011 WHEN wsh_interface_to_om_failed THEN
2012 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_error;
2013 IF l_debug_on THEN
2014 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_interface_to_om_failed exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2015 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_interface_to_om_failed');
2016 END IF;
2017
2018 WHEN wsh_invalid_del_status THEN
2019 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_error;
2020 FND_MESSAGE.Set_Name ('WSH', 'WSH_INVALID_DELIVERY_STATUS');
2021 WSH_UTIL_CORE.Add_Message (x_return_status,l_module_name);
2022 IF l_debug_on THEN
2023 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_invalid_del_status exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2024 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_invalid_del_status');
2025 END IF;
2026
2027 WHEN wsh_del_details_error THEN
2028 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_error;
2029 FND_MESSAGE.Set_Name ('WSH', 'WSH_DEL_DETAIL_ERROR');
2030 WSH_UTIL_CORE.Add_Message (x_return_status,l_module_name);
2031 IF l_debug_on THEN
2032 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_del_details_error exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2033 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_del_details_error');
2034 END IF;
2035
2036 WHEN wsh_raise_event_error THEN
2037 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_error;
2038 IF l_debug_on THEN
2039 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_raise_event_error exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2040 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_raise_event_error');
2041 END IF;
2042 WHEN wsh_sa_resend_error THEN
2043 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_error;
2044 FND_MESSAGE.Set_Name ('WSH', 'WSH_SA_RESEND_ERROR');
2045 FND_MESSAGE.SET_TOKEN('DEL_NAME',l_del_name);
2046 WSH_UTIL_CORE.Add_Message (x_return_status,l_module_name);
2047 IF l_debug_on THEN
2048 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_sa_resend_error exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2049 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_sa_resend_error');
2050 END IF;
2051
2052 WHEN wsh_serial_no_inv_interface THEN
2053 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_error;
2054 FND_MESSAGE.Set_Name ('WSH', 'WSH_SERIAL_NO_INV_INTERFACE');
2055 FND_MESSAGE.SET_TOKEN('DEL_NAME',l_del_name);
2056 WSH_UTIL_CORE.Add_Message (x_return_status,l_module_name);
2057 IF l_debug_on THEN
2058 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_serial_no_inv_interface exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2059 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_serial_no_inv_interface');
2060 END IF;
2061
2062 WHEN OTHERS THEN
2063 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
2064 IF l_debug_on THEN
2065 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
2066 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2067 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2068 END IF;
2069 END Send_Shipment_Advice;
2070
2071
2072 END WSH_TRANSACTIONS_TPW_UTIL;
2073