1 PACKAGE BODY WSH_TRANSACTIONS_TPW_UTIL AS
2 /* $Header: WSHTXTPB.pls 120.0.12000000.2 2007/01/23 18:31:14 rlanka 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 /*===========================================================================
313 | |
314 | PROCEDURE NAME Send_Cbod_Success |
315 | |
316 | DESCRIPTION This procedure sends a Conformation BOD to the supplier, |
317 | if the cancellation requiest is successfully completed. |
318 | |
319 | MODIFICATION HISTORY |
320 | |
321 | 02/18/02 Vijay Nandula Created |
322 | |
323 ============================================================================*/
324
325 PROCEDURE Send_Cbod_Success ( P_item_type IN VARCHAR2,
326 P_item_key IN VARCHAR2,
327 X_Return_Status OUT NOCOPY VARCHAR2 )
328 IS
329 l_txn_hist_record WSH_TRANSACTIONS_HISTORY_PKG.Txns_History_Record_Type;
330 l_Return_Status VARCHAR2 (1);
331 l_Cbod_Status VARCHAR2 (5) := '00';
332
333 wsh_get_txns_hist_error EXCEPTION;
334 wsh_raise_event_error EXCEPTION;
335 --
336 l_debug_on BOOLEAN;
337 --
338 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'SEND_CBOD_SUCCESS';
339 --
340 BEGIN
341 --
342 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
343 --
344 IF l_debug_on IS NULL
345 THEN
346 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
347 END IF;
348 --
349 IF l_debug_on THEN
350 wsh_debug_sv.push(l_module_name, 'Send_Cbod_Success');
351 wsh_debug_sv.log(l_module_name, 'P_item_type',P_item_type);
352 wsh_debug_sv.log(l_module_name, 'P_item_key',P_item_key);
353 END IF;
354
355 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_success;
356
357 WSH_TRANSACTIONS_HISTORY_PKG.Get_Txns_History ( P_item_type,
358 P_item_key,
359 'I',
360 'SR',
361 l_txn_hist_record,
362 l_Return_Status );
363 IF l_debug_on THEN
364 wsh_debug_sv.log (l_module_name, 'Return status after Get_Txns_History ', l_Return_Status);
365 END IF;
366
367 IF (l_Return_Status <> WSH_UTIL_CORE.g_ret_sts_success ) THEN
368 RAISE wsh_get_txns_hist_error;
369 END IF;
370
371 IF l_debug_on THEN
372 wsh_debug_sv.log (l_module_name, 'txn_history.Transaction Status ', l_txn_hist_record.transaction_status);
373 END IF;
374 -- Change the Event Name before calling Raise Event
375 l_txn_hist_record.Event_Name := 'oracle.apps.wsh.tpw.scbod';
376 l_txn_hist_record.Event_Key := NULL;
377
378 WSH_EXTERNAL_INTERFACE_SV.Raise_Event ( l_txn_hist_record, l_Cbod_Status, l_Return_Status );
379
380 IF l_debug_on THEN
381 wsh_debug_sv.log (l_module_name, 'Return status after Raise_Event ', l_Return_Status);
382 END IF;
383
384 IF (l_Return_Status <> WSH_UTIL_CORE.g_ret_sts_success ) THEN
385 RAISE wsh_raise_event_error;
386 END IF;
387
388 IF l_debug_on THEN
389 wsh_debug_sv.pop(l_module_name);
390 END IF;
391 EXCEPTION
392 WHEN wsh_get_txns_hist_error THEN
393 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_error;
394 IF l_debug_on THEN
395 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_get_txns_hist_error has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
396 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_get_txns_hist_error');
397 END IF;
398
399 WHEN wsh_raise_event_error THEN
400 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_error;
401 IF l_debug_on THEN
402 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_raise_event_error exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
403 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_raise_event_error');
404 END IF;
405
406 WHEN OTHERS THEN
407 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
408 IF l_debug_on THEN
409 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
410 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
411 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
412 END IF;
413 END Send_Cbod_Success;
414
415
416 /*===========================================================================
417 | |
418 | PROCEDURE NAME Send_Cbod_Failure |
419 | |
420 | DESCRIPTION This procedure sends a failure Conformation BOD to the |
421 | supplier if (a) a cancellation is not processed or (b) |
422 | if the cancellation is processed, then failure to the |
423 | work flow process which sent the original transaction. |
424 | MODIFICATION HISTORY |
425 | |
426 | 02/18/02 Vijay Nandula Created |
427 | |
428 ============================================================================*/
429
430 PROCEDURE Send_Cbod_Failure ( P_item_type IN VARCHAR2,
431 P_item_key IN VARCHAR2,
432 X_Return_Status OUT NOCOPY VARCHAR2 )
433 IS
434 l_txn_hist_record WSH_TRANSACTIONS_HISTORY_PKG.Txns_History_Record_Type;
435 l_Return_Status VARCHAR2 (1);
436 l_Cbod_Status VARCHAR2 (5) := '99';
437
438 l_Event_Name VARCHAR2 (120);
439 l_Event_Key VARCHAR2 (30);
440
441 CURSOR l_txn_hist_cur (c_Orig_Document_Number VARCHAR2,
442 c_Trading_Partner_ID NUMBER)
443 IS
444 SELECT Event_Key,
445 Item_Type,
446 Internal_Control_Number
447 FROM wsh_transactions_history
448 WHERE Transaction_ID = (SELECT MAX (Transaction_ID)
449 FROM wsh_transactions_history
450 WHERE Document_Number = c_Orig_Document_Number
451 AND Trading_Partner_ID = c_Trading_Partner_ID
452 AND Document_Direction = 'I'
453 AND Action_Type = 'A');
454
455 wsh_get_txns_hist_error EXCEPTION;
456 wsh_raise_event_error EXCEPTION;
457 wsh_orig_txns_hist_error EXCEPTION;
458 --
459 l_debug_on BOOLEAN;
460 --
461 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'SEND_CBOD_FAILURE';
462 --
463 BEGIN
464 --
465 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
466 --
467 IF l_debug_on IS NULL
468 THEN
469 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
470 END IF;
471 --
472 IF l_debug_on THEN
473 wsh_debug_sv.push(l_module_name, 'Send_Cbod_Failure');
474 wsh_debug_sv.log(l_module_name, 'P_item_type',P_item_type);
475 wsh_debug_sv.log(l_module_name, 'P_item_key',P_item_key);
476 END IF;
477
478 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_success;
479
480 WSH_TRANSACTIONS_HISTORY_PKG.Get_Txns_History ( P_item_type,
481 P_item_key,
482 'I',
483 'SR',
484 l_txn_hist_record,
485 l_Return_Status );
486
487 IF l_debug_on THEN
488 wsh_debug_sv.log (l_module_name, 'Return status after Get_Txns_History ', l_Return_Status);
489 END IF;
490
491 IF (l_Return_Status <> WSH_UTIL_CORE.g_ret_sts_success ) THEN
492 RAISE wsh_get_txns_hist_error;
493 END IF;
494
495 -- Check if the error message is for the current WF or the Original WF
496 -- If the Status is populated with 'success' then it is for original instance
497 -- else it is for the current WF instance.
498 IF l_debug_on THEN
499 wsh_debug_sv.log (l_module_name, 'Transaction Status ', l_txn_hist_record.Transaction_Status);
500 wsh_debug_sv.log (l_module_name, 'Action Type ', l_txn_hist_record.Action_Type);
501 END IF;
502
503 IF (l_txn_hist_record.Transaction_Status = 'SC' AND
504 l_txn_hist_record.Action_Type = 'D') THEN
505
506 -- Get the original transaction record from the Transaction History table.
507 OPEN l_txn_hist_cur (l_txn_hist_record.Orig_Document_Number, l_txn_hist_record.Trading_Partner_ID);
508 FETCH l_txn_hist_cur
509 INTO l_txn_hist_record.Event_Key,
510 l_txn_hist_record.Item_Type,
511 l_txn_hist_record.Internal_Control_Number;
512 IF ( l_txn_hist_cur % NOTFOUND )THEN
513 CLOSE l_txn_hist_cur;
514 IF l_debug_on THEN
515 wsh_debug_sv.log (l_module_name, 'No data found ');
516 END IF;
517 RAISE wsh_orig_txns_hist_error;
518 END IF;
519 CLOSE l_txn_hist_cur;
520 END IF;
521
522 -- Before calling the Raise Event Update the l_txn_hist_record.Event_Name
523 -- with appropriate value. Is this 'oracle.apps.wsh.tpw.scbod' ??
524
525 IF l_debug_on THEN
526 wsh_debug_sv.log (l_module_name, 'Before Calling the Raise_Event ', l_Cbod_Status);
527 END IF;
528 -- Change the Event Name before calling Raise Event
529 l_txn_hist_record.Event_Name := 'oracle.apps.wsh.tpw.scbod';
530 l_txn_hist_record.Event_Key := NULL;
531
532 -- Call Raise Event procedure to raise the appropriate event.
533 WSH_EXTERNAL_INTERFACE_SV.Raise_Event ( l_txn_hist_record, l_Cbod_Status, l_Return_Status );
534
535 IF l_debug_on THEN
536 wsh_debug_sv.log (l_module_name, 'After calling the Raise_Event, Status ', l_Return_Status);
537 END IF;
538
539 IF (l_Return_Status <> WSH_UTIL_CORE.g_ret_sts_success ) THEN
540 RAISE wsh_raise_event_error;
541 END IF;
542
543 IF l_debug_on THEN
544 wsh_debug_sv.pop(l_module_name);
545 END IF;
546 EXCEPTION
547 WHEN wsh_get_txns_hist_error THEN
548 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_error;
549 IF l_debug_on THEN
550 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_get_txns_hist_error exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
551 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_get_txns_hist_error');
552 END IF;
553
554 WHEN wsh_orig_txns_hist_error THEN
555 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_error;
556 IF l_debug_on THEN
557 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_orig_txns_hist_error exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
558 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_orig_txns_hist_error');
559 END IF;
560
561 WHEN wsh_raise_event_error THEN
562 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_error;
563 IF l_debug_on THEN
564 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_raise_event_error exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
565 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_raise_event_error');
566 END IF;
567
568 WHEN OTHERS THEN
569 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
570 IF l_debug_on THEN
571 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
572 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
573 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
574 END IF;
575 END Send_Cbod_Failure;
576
577
578 /*===========================================================================
579 | |
580 | PROCEDURE NAME Check_Cancel_Allowed |
581 | |
582 | DESCRIPTION This procedure checks if the cancellation request for a |
583 | transaction can be processed or not. If allowed then |
584 | cancellation will be completed. |
585 | MODIFICATION HISTORY |
586 | |
587 | 02/18/02 Vijay Nandula Created |
588 | |
589 ============================================================================*/
590
591 PROCEDURE Check_Cancel_Allowed ( P_item_type IN VARCHAR2,
592 P_item_key IN VARCHAR2,
593 X_Return_Status OUT NOCOPY VARCHAR2 )
594 IS
595 l_txn_hist_record WSH_TRANSACTIONS_HISTORY_PKG.Txns_History_Record_Type;
596 l_Return_Status VARCHAR2 (1);
597 l_new_del_status VARCHAR2 (2);
598 l_Event_Name VARCHAR2 (120);
599 l_Event_Key VARCHAR2 (30);
600 l_Delivery_ID NUMBER;
601
602 l_Orig_Entity_Number VARCHAR2 (30);
603 l_Orig_Trading_Partner_ID NUMBER;
604 l_Orig_Transaction_Status VARCHAR2 (2);
605
606 l_New_Del_Interface_ID NUMBER;
607
608 CURSOR l_orig_txn_hist_cur (c_Orig_Document_Number VARCHAR2,
609 c_Trading_Partner_ID NUMBER)
610 IS
611 SELECT Entity_Number,
612 Trading_Partner_ID,
613 Transaction_Status
614 FROM wsh_transactions_history
615 WHERE Transaction_ID = (SELECT MAX (Transaction_ID)
616 FROM wsh_transactions_history
617 WHERE Document_Number = c_Orig_Document_Number
618 AND Trading_Partner_ID = c_Trading_Partner_ID
619 AND Document_Direction = 'I'
620 AND Action_Type = 'A');
621
622 CURSOR l_new_del_status_cur (c_Delivery_Name VARCHAR2,
623 c_Organization_ID NUMBER) IS
624 SELECT status_code, Delivery_ID
625 FROM wsh_new_deliveries
626 WHERE Organization_id = c_Organization_ID
627 AND Name = c_Delivery_Name;
628 /*
629
630 CURSOR l_new_del_interface_cur ( c_Name VARCHAR2,
631 c_Org_ID NUMBER ) IS
632 SELECT Delivery_Interface_id
633 FROM wsh_new_del_interface
634 WHERE Name = c_Name
635 AND Organization_ID = c_Org_ID;
636 */
637
638 wsh_invalid_item_key EXCEPTION;
639 wsh_get_txns_hist_error EXCEPTION;
640 wsh_orig_txns_hist_error EXCEPTION;
641 wsh_update_history EXCEPTION;
642 wsh_del_interface_rec EXCEPTION;
643 wsh_invalid_delivery_no EXCEPTION;
644 wsh_del_interface_wrapper EXCEPTION;
645
646 --
647 l_debug_on BOOLEAN;
648 --
649 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CHECK_CANCEL_ALLOWED';
650 --
651 --Bugfix 4070732
652 l_api_session_name CONSTANT VARCHAR2(150) := G_PKG_NAME ||'.' || l_module_name;
653 BEGIN
654 --Bugfix 4070732
655 IF WSH_UTIL_CORE.G_START_OF_SESSION_API is null THEN
656 WSH_UTIL_CORE.G_START_OF_SESSION_API := l_api_session_name;
657 WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API := FALSE;
658 END IF;
659 --
660 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
661 --
662 IF l_debug_on IS NULL
663 THEN
664 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
665 END IF;
666 --
667 IF l_debug_on THEN
668 wsh_debug_sv.push(l_module_name, 'Check_Cancel_Allowed');
669 wsh_debug_sv.log(l_module_name, 'P_item_type',P_item_type);
670 wsh_debug_sv.log(l_module_name, 'P_item_key',P_item_key);
671 END IF;
672
673
674 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_success;
675
676 IF ( P_item_type IS NULL OR
677 P_item_key IS NULL ) THEN
678 RAISE wsh_invalid_item_key;
679 END IF;
680
681 -- Get the record from the Transaction History Table.
682 WSH_TRANSACTIONS_HISTORY_PKG.Get_Txns_History ( P_item_type,
683 P_item_key,
684 'I',
685 'SR',
686 l_txn_hist_record,
687 l_Return_Status );
688
689 IF l_debug_on THEN
690 wsh_debug_sv.log (l_module_name, 'Return status after Get_Txns_History ', l_Return_Status);
691 END IF;
692
693 IF (l_Return_Status <> WSH_UTIL_CORE.g_ret_sts_success ) THEN
694 RAISE wsh_get_txns_hist_error;
695 END IF;
696
697 -- Get the Delivery Interface ID from wsh_new_del_interface table for the given record
698 -- in the transaction history table.
699 /*
700 OPEN l_new_del_interface_cur ( l_txn_hist_record.Entity_Number,
701 l_txn_hist_record.Trading_Partner_ID );
702 FETCH l_new_del_interface_cur INTO l_New_Del_Interface_ID;
703 CLOSE l_new_del_interface_cur;
704 */
705
706 l_New_Del_Interface_ID := to_number(l_txn_hist_record.Entity_Number);
707
708
709 IF l_debug_on THEN
710 wsh_debug_sv.log (l_module_name, 'Delivery Interface ID' , l_New_Del_Interface_ID);
711 END IF;
712
713 -- Get the Transaction Record for the Original transaction (which has addition)
714 OPEN l_orig_txn_hist_cur (l_txn_hist_record.Orig_Document_Number, l_txn_hist_record.Trading_Partner_ID);
715 FETCH l_orig_txn_hist_cur
716 INTO l_Orig_Entity_Number, l_Orig_Trading_Partner_ID, l_Orig_Transaction_Status;
717
718 IF ( l_orig_txn_hist_cur % NOTFOUND ) THEN
719 CLOSE l_orig_txn_hist_cur;
720 IF l_debug_on THEN
721 wsh_debug_sv.log (l_module_name, 'Original Record Not Found ');
722 END IF;
723 RAISE wsh_orig_txns_hist_error;
724 END IF;
725 CLOSE l_orig_txn_hist_cur;
726
727 IF l_debug_on THEN
728 wsh_debug_sv.log (l_module_name, 'Txn Status for original record ', l_Orig_Transaction_Status);
729 END IF;
730
731 IF ( l_Orig_Transaction_Status = 'IP' ) THEN
732 l_txn_hist_record.transaction_status := 'ER';
733 WSH_TRANSACTIONS_HISTORY_PKG.Create_Update_Txns_History ( l_txn_hist_record,
734 l_txn_hist_record.transaction_id,
735 l_Return_Status );
736 IF l_debug_on THEN
737 wsh_debug_sv.log (l_module_name, 'Return status after Create_Update_Txns_History ', l_Return_Status);
738 END IF;
739 IF ( l_Return_Status <> WSH_UTIL_CORE.g_ret_sts_success ) THEN
740 RAISE wsh_update_history;
741 END IF;
742
743 WSH_PROCESS_INTERFACED_PKG.delete_interface_records ( l_New_Del_Interface_ID,
744 l_Return_Status );
745 IF l_debug_on THEN
746 wsh_debug_sv.log (l_module_name, 'Return status after delete_interface_records ', l_Return_Status);
747 END IF;
748
749 IF ( l_Return_Status <> WSH_UTIL_CORE.g_ret_sts_success ) THEN
750 RAISE wsh_del_interface_rec;
751 END IF;
752
753 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_error;
754
755 --Bugfix 4070732 {
756 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
757 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
758 IF l_debug_on THEN
759 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
760 END IF;
761
762 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
763 x_return_status => l_return_status);
764
765 IF l_debug_on THEN
766 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
767 END IF;
768
769 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
770 x_return_status := l_return_status;
771 END IF;
772
773 END IF;
774 END IF;
775 --}
776 -- End of bug 4070732
777
778 IF l_debug_on THEN
779 wsh_debug_sv.pop(l_module_name);
780 END IF;
781 RETURN;
782 ELSIF ( l_Orig_Transaction_Status = 'ER' ) THEN
783
784 WSH_PROCESS_INTERFACED_PKG.delete_interface_records ( l_New_Del_Interface_ID,
785 l_Return_Status );
786 IF l_debug_on THEN
787 wsh_debug_sv.log (l_module_name, 'Return status after delete_interface_records ', l_Return_Status);
788 END IF;
789
790 IF ( l_Return_Status <> WSH_UTIL_CORE.g_ret_sts_success ) THEN
791 RAISE wsh_del_interface_rec;
792 END IF;
793
794 l_txn_hist_record.transaction_status := 'SC';
795 WSH_TRANSACTIONS_HISTORY_PKG.Create_Update_Txns_History ( l_txn_hist_record,
796 l_txn_hist_record.transaction_id,
797 l_Return_Status );
798 IF l_debug_on THEN
799 wsh_debug_sv.log (l_module_name, 'Return status after Create_Update_Txns_History ', l_Return_Status);
800 END IF;
801 IF ( l_Return_Status <> WSH_UTIL_CORE.g_ret_sts_success ) THEN
802 RAISE wsh_update_history;
803 END IF;
804
805 --Bugfix 4070732 {
806 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
807 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
808 IF l_debug_on THEN
809 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
810 END IF;
811
812 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
813 x_return_status => l_return_status);
814
815 IF l_debug_on THEN
816 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
817 END IF;
818
819 IF (
820 ( l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR )
821 OR ( l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR
822 AND x_return_status <> WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR )
823 )
824 THEN
825 --{
826 x_return_status := l_return_status;
827 --}
828 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING
829 AND x_return_status <> WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
830 THEN
831 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
832 END IF;
833
834 END IF;
835 END IF;
836 --}
837 -- End of bug 4070732
838
839 IF l_debug_on THEN
840 wsh_debug_sv.pop(l_module_name);
841 END IF;
842 RETURN;
843
844 END IF;
845
846 OPEN l_new_del_status_cur (l_Orig_Entity_Number, l_Orig_Trading_Partner_ID);
847 FETCH l_new_del_status_cur INTO l_new_del_status, l_Delivery_ID;
848 IF (l_new_del_status_cur % NOTFOUND) THEN
849 IF l_debug_on THEN
850 wsh_debug_sv.log (l_module_name, 'No data found for l_new_del_status_cur');
851 END IF;
852 CLOSE l_new_del_status_cur;
853 RAISE wsh_invalid_delivery_no;
854 END IF;
855 CLOSE l_new_del_status_cur;
856
857 IF l_debug_on THEN
858 wsh_debug_sv.log (l_module_name, 'Delivery Name' , l_txn_hist_record.entity_number);
859 wsh_debug_sv.log (l_module_name, 'Delivery Status' , l_new_del_status);
860 END IF;
861
862 IF ( l_new_del_status IN ('CL', 'IT', 'CO') ) THEN
863 l_txn_hist_record.transaction_status := 'ER';
864 WSH_TRANSACTIONS_HISTORY_PKG.Create_Update_Txns_History ( l_txn_hist_record,
865 l_txn_hist_record.transaction_id,
866 l_Return_Status );
867 IF l_debug_on THEN
868 wsh_debug_sv.log (l_module_name, 'Return status after Create_Update_Txns_History ', l_Return_Status);
869 END IF;
870 IF ( l_Return_Status <> WSH_UTIL_CORE.g_ret_sts_success ) THEN
871 RAISE wsh_update_history;
872 END IF;
873
874
875 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_error;
876
877 --Bugfix 4070732 {
878 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
879 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
880 IF l_debug_on THEN
881 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
882 END IF;
883
884 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
885 x_return_status => l_return_status);
886
887
888 IF l_debug_on THEN
889 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
890 END IF;
891
892 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
893 x_return_status := l_return_status;
894 END IF;
895
896 END IF;
897 END IF;
898 --}
899 -- End of bug 4070732
900
901 IF l_debug_on THEN
902 wsh_debug_sv.pop(l_module_name);
903 END IF;
904 RETURN;
905 END IF;
906
907 IF l_debug_on THEN
908 wsh_debug_sv.log (l_module_name, 'New Delivery Interface ID' , l_new_del_interface_id);
909 wsh_debug_sv.log (l_module_name, 'Delivery ID' , l_delivery_id);
910 END IF;
911
912 WSH_INTERFACE_COMMON_ACTIONS.Delivery_Interface_Wrapper (l_New_Del_Interface_ID,
913 'CANCEL',
914 l_Delivery_ID,
915 l_Return_Status );
916
917 IF l_debug_on THEN
918 wsh_debug_sv.log (l_module_name, 'Return status after Delivery_Interface_Wrapper ', l_Return_Status);
919 END IF;
920
921 IF ( l_Return_Status <> WSH_UTIL_CORE.g_ret_sts_success ) THEN
922 RAISE wsh_del_interface_wrapper;
923 ELSE
924 l_txn_hist_record.transaction_status := 'SC';
925
926 WSH_TRANSACTIONS_HISTORY_PKG.Create_Update_Txns_History ( l_txn_hist_record,
927 l_txn_hist_record.transaction_id,
928 l_Return_Status );
929
930 IF l_debug_on THEN
931 wsh_debug_sv.log (l_module_name, 'Return status after Create_Update_Txns_History ', l_Return_Status);
932 END IF;
933
934 IF (l_Return_Status <> WSH_UTIL_CORE.g_ret_sts_success ) THEN
935 RAISE wsh_update_history;
936 END IF;
937
938 WSH_PROCESS_INTERFACED_PKG.delete_interface_records ( l_New_Del_Interface_ID,
939 l_Return_Status );
940
941 IF l_debug_on THEN
942 wsh_debug_sv.log (l_module_name, 'Return status after delete_interface_records ', l_Return_Status);
943 END IF;
944
945 IF ( l_Return_Status <> WSH_UTIL_CORE.g_ret_sts_success ) THEN
946 RAISE wsh_del_interface_rec;
947 END IF;
948 END IF;
949
950 --bug 4070732
951 --End of the API handling of calls to process_stops_for_load_tender
952 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name)
953 THEN
954 --{
955 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
956 --{
957
958 IF l_debug_on THEN
959 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Process_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
960 END IF;
961
962 WSH_UTIL_CORE.Process_stops_for_load_tender(p_reset_flags => TRUE,
963 x_return_status => l_return_status);
964
965 IF l_debug_on THEN
966 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
967 END IF;
968
969 IF (
970 ( l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR )
971 OR ( l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR
972 AND x_return_status <> WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR )
973 )
974 THEN
975 --{
976 x_return_status := l_return_status;
977 --}
978 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING
979 AND x_return_status <> WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
980 THEN
981 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
982 END IF;
983
984
985 --}
986 END IF;
987 --}
988 END IF;
989 --bug 4070732
990
991 IF l_debug_on THEN
992 wsh_debug_sv.pop(l_module_name);
993 END IF;
994
995 EXCEPTION
996 WHEN wsh_invalid_item_key THEN
997 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_error;
998
999 --Bugfix 4070732 {
1000 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
1001 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1002 IF l_debug_on THEN
1003 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1004 END IF;
1005
1006 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
1007 x_return_status => l_return_status);
1008
1009 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1010 x_return_status := l_return_status;
1011 END IF;
1012
1013
1014 IF l_debug_on THEN
1015 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1016 END IF;
1017
1018 END IF;
1019 END IF;
1020 --}
1021 -- End of bug 383969
1022
1023 IF l_debug_on THEN
1024 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_invalid_item_key exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1025 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_invalid_item_key');
1026 END IF;
1027
1028 WHEN wsh_get_txns_hist_error THEN
1029 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_error;
1030 --Bugfix 4070732 {
1031 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
1032 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1033 IF l_debug_on THEN
1034 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1035 END IF;
1036
1037 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
1038 x_return_status => l_return_status);
1039
1040
1041 IF l_debug_on THEN
1042 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1043 END IF;
1044
1045 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1046 x_return_status := l_return_status;
1047 END IF;
1048
1049 END IF;
1050 END IF;
1051 --}
1052 -- End of bug 383969
1053
1054 IF l_debug_on THEN
1055 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_get_txns_hist_error exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1056 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_get_txns_hist_error');
1057 END IF;
1058
1059 WHEN wsh_orig_txns_hist_error THEN
1060 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_error;
1061 --Bugfix 4070732 {
1062 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
1063 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1064 IF l_debug_on THEN
1065 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1066 END IF;
1067
1068 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
1069 x_return_status => l_return_status);
1070
1071
1072 IF l_debug_on THEN
1073 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1074 END IF;
1075
1076 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1077 x_return_status := l_return_status;
1078 END IF;
1079 END IF;
1080 END IF;
1081 --}
1082 -- End of bug 383969
1083
1084 IF l_debug_on THEN
1085 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_orig_txns_hist_error exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1086 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_orig_txns_hist_error');
1087 END IF;
1088
1089 WHEN wsh_update_history THEN
1090 X_return_status := WSH_UTIL_CORE.g_ret_sts_error;
1091
1092 --Bugfix 4070732 {
1093 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
1094 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1095 IF l_debug_on THEN
1096 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1097 END IF;
1098
1099 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
1100 x_return_status => l_return_status);
1101
1102
1103 IF l_debug_on THEN
1104 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1105 END IF;
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 END IF;
1112 END IF;
1113 --}
1114 -- End of bug 383969
1115
1116 IF l_debug_on THEN
1117 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_update_history exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1118 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_update_history');
1119 END IF;
1120
1121 WHEN wsh_del_interface_rec THEN
1122 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_error;
1123
1124 --Bugfix 4070732 {
1125 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
1126 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1127 IF l_debug_on THEN
1128 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1129 END IF;
1130
1131 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
1132 x_return_status => l_return_status);
1133
1134 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1135 x_return_status := l_return_status;
1136 END IF;
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 END IF;
1144 END IF;
1145 --}
1146 -- End of bug 383969
1147
1148 IF l_debug_on THEN
1149 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_del_interface_rec exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1150 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_del_interface_rec');
1151 END IF;
1152
1153 WHEN wsh_invalid_delivery_no THEN
1154 X_return_status := WSH_UTIL_CORE.g_ret_sts_error;
1155
1156 --Bugfix 4070732 {
1157 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
1158 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1159 IF l_debug_on THEN
1160 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1161 END IF;
1162
1163 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
1164 x_return_status => l_return_status);
1165
1166
1167 IF l_debug_on THEN
1168 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1169 END IF;
1170
1171 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1172 x_return_status := l_return_status;
1173 END IF;
1174
1175 END IF;
1176 END IF;
1177 --}
1178 -- End of bug 383969
1179
1180 IF l_debug_on THEN
1181 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_invalid_delivery_no exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1182 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_invalid_delivery_no');
1183 END IF;
1184
1185 WHEN wsh_del_interface_wrapper THEN
1186 l_txn_hist_record.transaction_status := 'ER';
1187 WSH_TRANSACTIONS_HISTORY_PKG.Create_Update_Txns_History ( l_txn_hist_record,
1188 l_txn_hist_record.transaction_id,
1189 l_Return_Status );
1190 IF ( l_Return_Status <> WSH_UTIL_CORE.g_ret_sts_success ) THEN
1191 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
1192 END IF;
1193
1194 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_error;
1195
1196 --Bugfix 4070732 {
1197 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
1198 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1199 IF l_debug_on THEN
1200 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1201 END IF;
1202
1203 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
1204 x_return_status => l_return_status);
1205
1206
1207 IF l_debug_on THEN
1208 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1209 END IF;
1210
1211 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1212 x_return_status := l_return_status;
1213 END IF;
1214
1215 END IF;
1216 END IF;
1217 --}
1218 -- End of bug 383969
1219
1220 IF l_debug_on THEN
1221 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_del_interface_wrapper exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1222 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_del_interface_wrapper');
1223 END IF;
1224
1225 WHEN OTHERS THEN
1226 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
1227
1228 --Bugfix 4070732 {
1229 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
1230 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1231 IF l_debug_on THEN
1232 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1233 END IF;
1234
1235 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
1236 x_return_status => l_return_status);
1237
1238
1239 IF l_debug_on THEN
1240 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1241 END IF;
1242
1243 END IF;
1244 END IF;
1245 --}
1246 -- End of bug 383969
1247
1248 IF l_debug_on THEN
1249 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
1250 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1251 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1252 END IF;
1253 END Check_Cancel_Allowed;
1254
1255
1256 /*===========================================================================
1257 | |
1258 | PROCEDURE NAME Raise_Cancel_Event |
1259 | |
1260 | DESCRIPTION This procedure raises a WF event for cancelling the |
1261 | previous WF instance. |
1262 | MODIFICATION HISTORY |
1263 | |
1264 | 02/18/02 Vijay Nandula Created |
1265 | |
1266 ============================================================================*/
1267
1268 PROCEDURE Raise_Cancel_Event ( P_item_type IN VARCHAR2,
1269 P_item_key IN VARCHAR2,
1270 X_return_Status OUT NOCOPY VARCHAR2 )
1271 IS
1272 l_txn_hist_record WSH_TRANSACTIONS_HISTORY_PKG.Txns_History_Record_Type;
1273 l_orig_txn_hist_record WSH_TRANSACTIONS_HISTORY_PKG.Txns_History_Record_Type;
1274 l_Return_Status VARCHAR2 (1);
1275 l_Event_Name VARCHAR2 (120);
1276 l_Event_Key VARCHAR2 (30);
1277
1278 CURSOR l_txn_hist_cur (c_Item_Type VARCHAR2,
1279 c_Item_Key VARCHAR2)
1280 IS
1281 SELECT wth1.Transaction_ID,
1282 wth1.Document_Type,
1283 wth1.Document_Direction,
1284 wth1.Document_Number,
1285 wth1.Orig_Document_Number,
1286 wth1.Entity_Number,
1287 wth1.Entity_Type,
1288 wth1.Trading_Partner_ID,
1289 wth1.Action_Type,
1290 wth1.Transaction_Status,
1291 wth1.ECX_Message_ID,
1292 wth1.Event_Name,
1293 wth1.Event_Key,
1294 wth1.Item_Type,
1295 wth1.Internal_Control_Number,
1296 wth1.Attribute_Category,
1297 wth1.Attribute1,
1298 wth1.Attribute2,
1299 wth1.Attribute3,
1300 wth1.Attribute4,
1301 wth1.Attribute5,
1302 wth1.Attribute6,
1303 wth1.Attribute7,
1304 wth1.Attribute8,
1305 wth1.Attribute9,
1306 wth1.Attribute10,
1307 wth1.Attribute11,
1308 wth1.Attribute12,
1309 wth1.Attribute13,
1310 wth1.Attribute14,
1311 wth1.Attribute15
1312 FROM wsh_transactions_history wth1,
1313 wsh_transactions_history wth2
1314 WHERE wth2.Item_Type = c_Item_Type
1315 AND wth2.Event_Key = c_Item_Key
1316 AND wth1.Document_Number = wth2.Orig_Document_Number
1317 AND wth1.Trading_Partner_ID = wth2.Trading_Partner_ID
1318 AND wth1.Document_Direction = 'I'
1319 AND wth1.Action_Type = 'A';
1320
1321 wsh_orig_txns_hist_error EXCEPTION;
1322 wsh_raise_event_error EXCEPTION;
1323 --
1324 l_debug_on BOOLEAN;
1325 --
1326 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'RAISE_CANCEL_EVENT';
1327 --
1328 BEGIN
1329 --
1330 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1331 --
1332 IF l_debug_on IS NULL
1333 THEN
1334 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1335 END IF;
1336 --
1337 IF l_debug_on THEN
1338 wsh_debug_sv.push(l_module_name, 'Raise_Cancel_Event');
1339 wsh_debug_sv.log(l_module_name, 'P_item_type',P_item_type);
1340 wsh_debug_sv.log(l_module_name, 'P_item_key',P_item_key);
1341 END IF;
1342
1343 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_success;
1344
1345 OPEN l_txn_hist_cur (P_Item_Type, P_Item_Key);
1346 FETCH l_txn_hist_cur
1347 INTO l_orig_txn_hist_record;
1348 IF ( l_txn_hist_cur % NOTFOUND ) THEN
1349 IF l_debug_on THEN
1350 wsh_debug_sv.log (l_module_name, 'No data found for l_txn_hist_cur ');
1351 END IF;
1352
1353 CLOSE l_txn_hist_cur;
1354 RAISE wsh_orig_txns_hist_error;
1355 END IF;
1356 CLOSE l_txn_hist_cur;
1357
1358 l_orig_txn_hist_record.Event_Name := 'oracle.apps.wsh.tpw.spwf';
1359 IF l_debug_on THEN
1360 wsh_debug_sv.log (l_module_name, 'Before Raise_Event : Event Name ' , l_orig_txn_hist_record.Event_Name);
1361 wsh_debug_sv.log (l_module_name, 'Event key ' , l_orig_txn_hist_record.Event_Key);
1362 END IF;
1363 WSH_EXTERNAL_INTERFACE_SV.Raise_Event ( l_orig_txn_hist_record,
1364 NULL,
1365 l_Return_Status );
1366
1367 IF l_debug_on THEN
1368 wsh_debug_sv.log (l_module_name, 'Return status after Raise_Event ', l_Return_Status);
1369 END IF;
1370
1371 IF (l_Return_Status <> WSH_UTIL_CORE.g_ret_sts_success ) THEN
1372 RAISE wsh_raise_event_error;
1373 END IF;
1374
1375 IF l_debug_on THEN
1376 wsh_debug_sv.pop(l_module_name);
1377 END IF;
1378 EXCEPTION
1379 WHEN wsh_orig_txns_hist_error THEN
1380 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_error;
1381 IF l_debug_on THEN
1382 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_orig_txns_hist_error exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1383 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_orig_txns_hist_error');
1384 END IF;
1385
1386 WHEN wsh_raise_event_error THEN
1387 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_error;
1388 IF l_debug_on THEN
1389 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_raise_event_error exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1390 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_raise_event_error');
1391 END IF;
1392
1393 WHEN OTHERS THEN
1394 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
1395 IF l_debug_on THEN
1396 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
1397 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1398 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1399 END IF;
1400 END Raise_Cancel_Event;
1401
1402
1403 /*===========================================================================
1404 | |
1405 | PROCEDURE NAME Send_Shipment_Advice |
1406 | |
1407 | DESCRIPTION This procedure checks if Warehouse Shipping Advice needs |
1408 | to be sent to the Supplier instance or not. If it is |
1409 | required, an event is raised to send the transaction. |
1410 | MODIFICATION HISTORY |
1411 | |
1412 | 02/18/02 Vijay Nandula Created |
1413 | |
1414 ============================================================================*/
1415
1416 PROCEDURE Send_Shipment_Advice ( P_Entity_ID IN NUMBER,
1417 P_Entity_Type IN VARCHAR2,
1418 P_Action_Type IN VARCHAR2,
1419 P_Document_Type IN VARCHAR2,
1420 P_Org_ID IN NUMBER,
1421 X_Return_Status OUT NOCOPY VARCHAR2 )
1422 IS
1423 CURSOR l_orig_txn_hist_cur (c_Entity_Number VARCHAR2,
1424 c_Org_ID NUMBER)
1425 IS
1426 SELECT wth1.Document_Number,
1427 wth1.Transaction_Status,
1428 wth1.Event_Key
1429 FROM wsh_transactions_history wth1
1430 WHERE Transaction_ID = (SELECT MAX (Transaction_ID)
1431 FROM wsh_transactions_history
1432 WHERE Entity_Number = c_Entity_Number
1433 AND Trading_Partner_ID = c_Org_ID
1434 AND Entity_Type = 'DLVY'
1435 AND Action_Type = 'A'
1436 AND Document_Direction = 'I'
1437 AND Document_Type = 'SR');
1438
1439 CURSOR l_new_del_status_cur ( c_Delivery_ID VARCHAR2, c_Organization_ID NUMBER ) IS
1440 SELECT Name, status_code
1441 FROM wsh_new_deliveries
1442 WHERE Organization_id = c_Organization_ID
1443 AND Delivery_ID = c_Delivery_ID;
1444
1445 CURSOR l_del_details_cur (c_Delivery_ID NUMBER, c_Org_ID NUMBER) IS
1446 SELECT 'X'
1447 FROM wsh_delivery_details wdd,
1448 wsh_delivery_assignments_v wda,
1449 wsh_new_deliveries wnd
1450 WHERE wdd.container_flag = 'N'
1451 AND wdd.source_code = 'WSH'
1452 AND wdd.Delivery_Detail_ID = wda.Delivery_Detail_ID
1453 AND wda.Delivery_ID = wnd.Delivery_ID
1454 AND wnd.Delivery_ID = c_Delivery_ID
1455 AND wnd.Organization_ID = c_Org_ID
1456 AND rownum = 1;
1457
1458 cursor l_check_resend_cur(p_entity_number VARCHAR2 ) is
1459 select transaction_status from wsh_transactions_history
1460 where transaction_id =(select max(transaction_id) from wsh_transactions_history
1461 where entity_number= p_entity_number
1462 and trading_partner_id=p_org_id
1463 and document_direction = 'O'
1464 and document_type ='SA'
1465 and entity_type ='DLVY'
1466 and action_type = 'A');
1467
1468 CURSOR get_delivery_details(cp_entity_id NUMBER) IS
1469 SELECT 'X'
1470 FROM wsh_delivery_details wdd,
1471 wsh_delivery_assignments_v wda,
1472 mtl_system_items msi
1473 WHERE wdd.container_flag = 'N'
1474 AND wdd.released_status <> 'D'
1475 AND nvl(wdd.inv_interfaced_flag,'N') <> 'Y'
1476 AND wdd.Delivery_Detail_ID = wda.Delivery_Detail_ID
1477 AND msi.inventory_item_id = wdd.inventory_item_id
1478 AND msi.organization_id = wdd.organization_id
1479 AND msi.serial_number_control_code IN (2,5,6)
1480 AND wda.Delivery_ID = cp_entity_id
1481 AND rownum = 1;
1482
1483 l_orig_document_number VARCHAR2 (120);
1484 l_orig_Transaction_Status VARCHAR2 (2);
1485 l_orig_Event_Key VARCHAR2 (240);
1486 l_curr_txn_hist_record WSH_TRANSACTIONS_HISTORY_PKG.Txns_History_Record_Type;
1487 l_Return_Status VARCHAR2 (1);
1488 l_new_del_status VARCHAR2 (2);
1489 l_Del_Name VARCHAR2 (30);
1490 l_txn_Status VARCHAR2 (2);
1491 l_del_detail_x VARCHAR2 (1);
1492 l_delivery_details VARCHAR2(1);
1493
1494 wsh_invalid_delivery_id EXCEPTION;
1495 wsh_invalid_entity_type EXCEPTION;
1496 wsh_invalid_action_type EXCEPTION;
1497 wsh_invalid_doc_type EXCEPTION;
1498 wsh_orig_txns_hist_error EXCEPTION;
1499 wsh_orig_txn_hist_error_status EXCEPTION;
1500 wsh_invalid_del_status EXCEPTION;
1501 wsh_del_details_error EXCEPTION;
1502 wsh_raise_event_error EXCEPTION;
1503 wsh_sa_resend_error EXCEPTION;
1504 wsh_serial_no_inv_interface EXCEPTION;
1505 --
1506 l_debug_on BOOLEAN;
1507 --
1508 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'SEND_SHIPMENT_ADVICE';
1509 --
1510 BEGIN
1511 --
1512 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1513 --
1514 IF l_debug_on IS NULL
1515 THEN
1516 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1517 END IF;
1518 --
1519 IF l_debug_on THEN
1520 wsh_debug_sv.push(l_module_name, 'Send_Shipment_Advice');
1521 wsh_debug_sv.log (l_module_name, 'P_Entity_ID ', P_Entity_ID);
1522 wsh_debug_sv.log (l_module_name, 'P_Entity_Type ', P_Entity_Type);
1523 wsh_debug_sv.log (l_module_name, 'P_Action_Type ', P_Action_Type);
1524 wsh_debug_sv.log (l_module_name, 'P_Document_Type ', P_Document_Type);
1525 wsh_debug_sv.log (l_module_name, 'P_Org_ID ', P_Org_ID);
1526 END IF;
1527
1528 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_success;
1529
1530 IF ( P_Entity_TYPE <> 'DLVY' ) THEN
1531 RAISE wsh_invalid_entity_type;
1532 ELSIF ( P_Action_TYPE <> 'A' ) THEN
1533 RAISE wsh_invalid_action_type;
1534 ELSIF ( P_Document_TYPE <> 'SA' ) THEN
1535 RAISE wsh_invalid_doc_type;
1536 END IF;
1537
1538 -- Get the Original Delivery Number and status from the delivery table.
1539 OPEN l_new_del_status_cur (P_Entity_ID,
1540 P_Org_ID);
1541 FETCH l_new_del_status_cur INTO l_Del_Name, l_new_del_status;
1542
1543 IF l_debug_on THEN
1544 wsh_debug_sv.log (l_module_name, 'Delivery Name' , l_Del_Name);
1545 wsh_debug_sv.log (l_module_name, 'Delivery Status' , l_new_del_status);
1546 END IF;
1547
1548 IF (l_new_del_status_cur % NOTFOUND) THEN
1549 IF l_debug_on THEN
1550 wsh_debug_sv.log (l_module_name, 'No Data found for l_new_del_status_cur');
1551 END IF;
1552 CLOSE l_new_del_status_cur;
1553 RAISE wsh_invalid_delivery_id;
1554 END IF;
1555 CLOSE l_new_del_status_cur;
1556
1557 -- If the Delivery status is open then we cannot send the Shipping Advice
1558 IF ( l_new_del_status NOT IN ('CL', 'IT', 'CO') ) THEN
1559 RAISE wsh_invalid_del_status;
1560 ELSE
1561 OPEN l_del_details_cur (P_Entity_ID, P_Org_ID);
1562 FETCH l_del_details_cur INTO l_del_detail_x;
1563 CLOSE l_del_details_cur;
1564 IF l_debug_on THEN
1565 wsh_debug_sv.log (l_module_name, 'Value of l_del_details_cur ' , l_del_detail_x);
1566 END IF;
1567 IF (NVL (l_del_detail_x, '-') <> 'X') THEN
1568 RAISE wsh_del_details_error;
1569 END IF;
1570
1571 OPEN get_delivery_details(p_entity_id);
1572 FETCH get_delivery_details INTO l_delivery_details;
1573 IF l_debug_on THEN
1574 wsh_debug_sv.log (l_module_name, 'Value of l_delivery_details' ,l_delivery_details);
1575 END IF;
1576 CLOSE get_delivery_details;
1577
1578 IF (NVL (l_delivery_details, '-') = 'X') THEN
1579 RAISE wsh_serial_no_inv_interface;
1580 END IF;
1581
1582 END IF;
1583
1584 -- Get the Original record from the Transaction History Table for the Delivery.
1585 OPEN l_orig_txn_hist_cur (l_Del_Name, P_Org_ID);
1586 FETCH l_orig_txn_hist_cur
1587 INTO l_orig_document_number, l_orig_Transaction_Status, l_orig_Event_Key;
1588 IF (l_orig_txn_hist_cur % NOTFOUND) THEN
1589 IF l_debug_on THEN
1590 wsh_debug_sv.log (l_module_name, 'No Data found for l_orig_txn_hist_cur');
1591 END IF;
1592 CLOSE l_orig_txn_hist_cur;
1593 RAISE wsh_orig_txns_hist_error;
1594 END IF;
1595 CLOSE l_orig_txn_hist_cur;
1596
1597 -- If the Original transaction status is Error or In process then we cannot send a
1598 -- Shipping Advice back to the Supplier.
1599 IF l_debug_on THEN
1600 wsh_debug_sv.log (l_module_name, 'Orig Transaction Status' , l_orig_Transaction_Status);
1601 END IF;
1602 IF ( l_orig_Transaction_Status IN ('ER', 'IP') ) THEN
1603 RAISE wsh_orig_txn_hist_error_status;
1604 END IF;
1605
1606 open l_check_resend_cur( l_Del_Name );
1607 fetch l_check_resend_cur into l_txn_Status;
1608 close l_check_resend_cur;
1609
1610 IF l_debug_on THEN
1611 wsh_debug_sv.log (l_module_name, 'Outbound Transaction Status' , l_txn_Status);
1612 END IF;
1613 if ( nvl(l_txn_Status, 'ER') <> 'ER' ) THEN
1614 raise wsh_sa_resend_error;
1615 end if;
1616
1617
1618 -- Build a Transaction Record for the current transaction
1619 l_curr_txn_hist_record.Document_Type := P_Document_Type;
1620 l_curr_txn_hist_record.Document_Direction := 'O';
1621 l_curr_txn_hist_record.Orig_Document_Number := l_orig_Document_Number;
1622 l_curr_txn_hist_record.Entity_Number := l_Del_Name;
1623 l_curr_txn_hist_record.Entity_Type := P_Entity_TYPE;
1624 l_curr_txn_hist_record.Trading_Partner_ID := P_Org_ID;
1625 l_curr_txn_hist_record.Action_Type := P_Action_Type;
1626 l_curr_txn_hist_record.Transaction_Status := 'IP';
1627 l_curr_txn_hist_record.Event_Name := 'oracle.apps.wsh.tpw.ssao';
1628 l_curr_txn_hist_record.Item_Type := 'WSHTPWI';
1629 l_curr_txn_hist_record.Event_Key := l_orig_Event_Key;
1630
1631 -- Raise event will insert the record into the transaction history table
1632 -- for the current transaction.
1633 IF l_debug_on THEN
1634 wsh_debug_sv.log (l_module_name, 'Before Raise_Event');
1635 END IF;
1636
1637 WSH_EXTERNAL_INTERFACE_SV.Raise_Event ( l_curr_txn_hist_record,
1638 NULL,
1639 l_Return_Status );
1640
1641 IF l_debug_on THEN
1642 wsh_debug_sv.log (l_module_name, 'After Raise_Event , return status ' , l_Return_Status);
1643 END IF;
1644
1645 IF (l_Return_Status <> WSH_UTIL_CORE.g_ret_sts_success ) THEN
1646 RAISE wsh_raise_event_error;
1647 END IF;
1648
1649 IF l_debug_on THEN
1650 wsh_debug_sv.pop(l_module_name);
1651 END IF;
1652
1653 EXCEPTION
1654 WHEN wsh_invalid_delivery_id THEN
1655 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_error;
1656 IF l_debug_on THEN
1657 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_invalid_delivery_id exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1658 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_invalid_delivery_id');
1659 END IF;
1660
1661 WHEN wsh_invalid_entity_type THEN
1662 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_error;
1663 IF l_debug_on THEN
1664 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_invalid_entity_type exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1665 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_invalid_entity_type');
1666 END IF;
1667
1668 WHEN wsh_invalid_action_type THEN
1669 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_error;
1670 IF l_debug_on THEN
1671 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_invalid_action_type exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1672 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_invalid_action_type');
1673 END IF;
1674
1675 WHEN wsh_invalid_doc_type THEN
1676 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_error;
1677 IF l_debug_on THEN
1678 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_invalid_doc_type exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1679 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_invalid_doc_type');
1680 END IF;
1681
1682 WHEN wsh_orig_txns_hist_error THEN
1683 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_error;
1684 IF l_debug_on THEN
1685 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_orig_txns_hist_error exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1686 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_orig_txns_hist_error');
1687 END IF;
1688
1689 WHEN wsh_orig_txn_hist_error_status THEN
1690 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_error;
1691 IF l_debug_on THEN
1692 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_orig_txns_hist_error exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1693 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_orig_txns_hist_error');
1694 END IF;
1695
1696 WHEN wsh_invalid_del_status THEN
1697 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_error;
1698 FND_MESSAGE.Set_Name ('WSH', 'WSH_INVALID_DELIVERY_STATUS');
1699 WSH_UTIL_CORE.Add_Message (x_return_status,l_module_name);
1700 IF l_debug_on THEN
1701 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_invalid_del_status exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1702 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_invalid_del_status');
1703 END IF;
1704
1705 WHEN wsh_del_details_error THEN
1706 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_error;
1707 FND_MESSAGE.Set_Name ('WSH', 'WSH_DEL_DETAIL_ERROR');
1708 WSH_UTIL_CORE.Add_Message (x_return_status,l_module_name);
1709 IF l_debug_on THEN
1710 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_del_details_error exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1711 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_del_details_error');
1712 END IF;
1713
1714 WHEN wsh_raise_event_error THEN
1715 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_error;
1716 IF l_debug_on THEN
1717 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_raise_event_error exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1718 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_raise_event_error');
1719 END IF;
1720 WHEN wsh_sa_resend_error THEN
1721 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_error;
1722 FND_MESSAGE.Set_Name ('WSH', 'WSH_SA_RESEND_ERROR');
1723 WSH_UTIL_CORE.Add_Message (x_return_status,l_module_name);
1724 IF l_debug_on THEN
1725 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_sa_resend_error exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1726 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_sa_resend_error');
1727 END IF;
1728
1729 WHEN wsh_serial_no_inv_interface THEN
1730 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_error;
1731 FND_MESSAGE.Set_Name ('WSH', 'WSH_SERIAL_NO_INV_INTERFACE');
1732 FND_MESSAGE.SET_TOKEN('DEL_NAME',l_del_name);
1733 WSH_UTIL_CORE.Add_Message (x_return_status,l_module_name);
1734 IF l_debug_on THEN
1735 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_serial_no_inv_interface exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1736 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_serial_no_inv_interface');
1737 END IF;
1738
1739 WHEN OTHERS THEN
1740 X_Return_Status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
1741 IF l_debug_on THEN
1742 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
1743 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1744 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1745 END IF;
1746 END Send_Shipment_Advice;
1747
1748
1749 END WSH_TRANSACTIONS_TPW_UTIL;
1750
[Home] [Help]