[Home] [Help]
PACKAGE BODY: APPS.WSH_TRANSACTIONS_UTIL
Source
1 PACKAGE BODY WSH_TRANSACTIONS_UTIL
2 -- $Header: WSHXUTLB.pls 120.5 2006/10/18 19:05:18 bsadri ship $
3 AS
4
5
6
7 --
8 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_TRANSACTIONS_UTIL';
9 --
10 PROCEDURE Send_Document( p_entity_id IN NUMBER,
11 p_entity_type IN VARCHAR2,
12 p_action_type IN VARCHAR2,
13 p_document_type IN VARCHAR2,
14 p_organization_id IN NUMBER,
15 x_return_status OUT NOCOPY VARCHAR2)
16
17 IS
18
19 wsh_invalid_doc_type EXCEPTION;
20
21 --
22 l_debug_on BOOLEAN;
23 --
24 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'SEND_DOCUMENT';
25 --
26 BEGIN
27
28 --
29 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
30 --
31 IF l_debug_on IS NULL
32 THEN
33 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
34 END IF;
35 --
36 WSH_DEBUG_SV.start_debug (p_entity_id);
37 IF l_debug_on THEN
38 wsh_debug_sv.push(l_module_name, 'Send_Document');
39 wsh_debug_sv.log(l_module_name, 'p_entity_id',p_entity_id);
40 wsh_debug_sv.log(l_module_name, 'p_entity_type',p_entity_type);
41 wsh_debug_sv.log(l_module_name, 'p_action_type',p_action_type);
42 wsh_debug_sv.log(l_module_name, 'p_document_type',p_document_type);
43 wsh_debug_sv.log(l_module_name, 'p_organization_id',p_organization_id);
44 END IF;
45
46 IF ( p_document_type = 'SR' ) THEN
47
48
49 Send_Shipment_Request ( p_entity_id,
50 p_entity_type,
51 p_action_type,
52 p_document_type,
53 p_organization_id,
54 x_return_status
55 );
56 ELSIF ( p_document_type = 'SA' ) THEN
57 WSH_TRANSACTIONS_TPW_UTIL.Send_Shipment_Advice( p_entity_id,
58 p_entity_type,
59 p_action_type,
60 p_document_type,
61 p_organization_id,
62 x_return_status
63 );
64 ELSE
65 raise wsh_invalid_doc_type;
66 END IF;
67
68 IF l_debug_on THEN
69 wsh_debug_sv.pop (l_module_name);
70 END IF;
71 WSH_DEBUG_SV.stop_debug;
72
73 EXCEPTION
74
75 WHEN wsh_invalid_doc_type THEN
76 x_return_status := wsh_util_core.g_ret_sts_error;
77 IF l_debug_on THEN
78 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_invalid_doc_type exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
79 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_invalid_doc_type');
80 END IF;
81 WHEN OTHERS THEN
82 x_return_status := wsh_util_core.g_ret_sts_unexp_error;
83 IF l_debug_on THEN
84 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
85 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
86 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
87 END IF;
88 END Send_Document;
89
90 PROCEDURE Send_Shipment_Request ( p_entity_id IN NUMBER,
91 p_entity_type IN VARCHAR2,
92 p_action_type IN VARCHAR2,
93 p_document_type IN VARCHAR2,
94 p_organization_id IN NUMBER,
95 x_return_status OUT NOCOPY VARCHAR2
96 )
97 IS
98
99 l_wh_type VARCHAR2(30);
100 l_org_code VARCHAR2(3);
101 l_rel_stat_tmp VARCHAR2(1);
102 l_txn_id NUMBER;
103 l_entity_number VARCHAR2(30);
104 l_tmp NUMBER;
105 l_valid_del_tmp VARCHAR2(1);
106 l_packed_det_tmp VARCHAR2(1);
107 l_orig_txn_status VARCHAR2(2);
108 l_manifest_enabled_flag VARCHAR2(1);
109 x_valid_flag BOOLEAN;
110 l_assigned_to_trip VARCHAR2(1);
111 l_customer_id NUMBER;
112
113 l_txns_history_rec WSH_TRANSACTIONS_HISTORY_PKG.Txns_History_Record_Type;
114
115 cursor del_name_cur is
116 select name, customer_id from wsh_new_deliveries
117 where delivery_id = p_entity_id;
118
119 -- This cursor checks if the delivery contains all the delivery details associated their corresponding source_line_ids.
120 cursor valid_del_cur(p_delivery_id IN NUMBER) is
121 select distinct 'X'
122 from wsh_delivery_details wdd1,
123 wsh_delivery_assignments_v wda1,
124 wsh_delivery_details wdd2,
125 wsh_delivery_assignments_v wda2
126 where
127 wdd1.source_line_id = wdd2.source_line_id
128 and wdd1.delivery_detail_id = wda1.delivery_detail_id
129 and wdd1.container_flag='N'
130 and wda1.delivery_id = p_delivery_id
131 and wdd2.delivery_detail_id = wda2.delivery_detail_id
132 and wdd2.container_flag='N'
133 and (wda2.delivery_id <> p_delivery_id
134 or wda2.delivery_id is null);
135
136 -- The cursors rel_status_tpw_cur, rel_status_cms_cur are used to ensure that the delivery details
137 -- associated to the correnponding delivery are eligible to be to sent out.
138 cursor rel_status_tpw_cur(p_delivery_id IN NUMBER) is
139 select distinct 'X'
140 from wsh_delivery_details wdd,
141 wsh_delivery_assignments_v wda
142 where wdd.delivery_detail_id = wda.delivery_detail_id
143 and wdd.container_flag = 'N'
144 and wdd.released_status not in ('X', 'R', 'B')
145 and wda.delivery_id =p_delivery_id;
146
147 cursor rel_status_cms_cur(p_delivery_id IN NUMBER) is
148 select distinct 'X'
149 from wsh_delivery_details wdd,
150 wsh_delivery_assignments_v wda
151 where wdd.delivery_detail_id = wda.delivery_detail_id
152 and wdd.container_flag = 'N'
153 and wdd.released_status not in ('X','Y')
154 and wda.delivery_id =p_delivery_id;
155
156 cursor orig_txn_hist_cur(p_entity_number IN VARCHAR2,
157 p_tp_id IN NUMBER
158 ) is
159 select document_number,transaction_status
160 from wsh_transactions_history
161 where transaction_id = (
162 select max(transaction_id)
163 from wsh_transactions_history
164 where entity_number = p_entity_number
165 and trading_partner_id = p_tp_id
166 and document_direction = 'O'
167 --and transaction_status = 'ST'
168 and action_type = 'A'
169 );
170
171 cursor det_pack_cms_cur(p_delivery_id IN NUMBER) is
172 select distinct 'X'
173 from wsh_delivery_details wdd,
174 wsh_delivery_assignments_v wda
175 where wda.delivery_id = p_delivery_id
176 and wda.parent_delivery_detail_id is null
177 and wda.delivery_detail_id = wdd.delivery_detail_id
178 and wdd.container_flag='N';
179
180
181 --bug 2399697 and 2399687 fixed
182 cursor get_delivery_details(p_delivery_id IN NUMBER) is
183 select wdd.delivery_detail_id,
184 wdd.source_header_id,
185 wdd.source_line_id
186 from wsh_delivery_details wdd,
187 wsh_delivery_assignments_v wda
188 where wda.delivery_id = p_delivery_id
189 and wda.delivery_detail_id = wdd.delivery_detail_id
190 and wdd.container_flag = 'N'
191 and wda.delivery_id IS NOT NULL;
192
193
194 wsh_incorrect_org EXCEPTION;
195 wsh_invalid_delivery EXCEPTION;
196 wsh_tpw_del_det_rel_stat EXCEPTION;
197 wsh_cms_del_det_rel_stat EXCEPTION;
198 wsh_details_not_packed EXCEPTION;
199 wsh_insert_history_error EXCEPTION;
200 wsh_raise_event_error EXCEPTION;
201 wsh_delivery_locked EXCEPTION;
202 wsh_cancel_disallowed EXCEPTION;
203 wsh_del_assign_to_trip EXCEPTION;
204
205 --bug 2399697 and 2399687 fixed
206 wsh_details_exceptions EXCEPTION;
207 wsh_details_credit_hold EXCEPTION;
208
209 -- 2444821
210 wsh_invalid_customer EXCEPTION;
211 -- 2444821
212
213 --wsh_document_in_ip_er EXCEPTION;
214
215 x_exception_exist Varchar2(1):='N';
216 x_severity_present Varchar2(1):=NULL;
217 l_delivery_detail_id NUMBER;
218
219 l_warning_count NUMBER:=0;
220
221 --
222 l_debug_on BOOLEAN;
223 --
224 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'SEND_SHIPMENT_REQUEST';
225 --
226 BEGIN
227 --
228 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
229 --
230 IF l_debug_on IS NULL
231 THEN
232 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
233 END IF;
234 --
235 IF l_debug_on THEN
236 wsh_debug_sv.push(l_module_name);
237 wsh_debug_sv.log(l_module_name, 'p_entity_id',p_entity_id);
238 wsh_debug_sv.log(l_module_name, 'p_entity_type',p_entity_type);
239 wsh_debug_sv.log(l_module_name, 'p_action_type',p_action_type);
240 wsh_debug_sv.log(l_module_name, 'p_document_type',p_document_type);
241 wsh_debug_sv.log(l_module_name, 'p_organization_id',p_organization_id);
242 END IF;
243
244
245 l_wh_type := WSH_EXTERNAL_INTERFACE_SV.Get_Warehouse_Type(p_organization_id => p_organization_id,
246 x_return_status => x_return_status,
247 p_delivery_id => p_entity_id);
248
249 IF l_debug_on THEN
250 wsh_debug_sv.log(l_module_name, 'l_wh_type,x_return_status',l_wh_type||','||x_return_status);
251 END IF;
252
253 IF ( l_wh_type IS NULL ) THEN
254 select organization_code
255 into l_org_code
256 from mtl_parameters
257 where organization_id = p_organization_id;
258 raise wsh_incorrect_org;
259 END IF;
260
261 IF l_debug_on THEN
262 wsh_debug_sv.log(l_module_name, 'l_org_code',l_org_code);
263 END IF;
264
265 open del_name_cur;
266 Fetch del_name_cur into l_entity_number, l_customer_id;
267 close del_name_cur;
268
269 IF l_debug_on THEN
270 wsh_debug_sv.log(l_module_name, 'l_entity_number,l_customer_id',l_entity_number||','||l_customer_id);
271 END IF;
272 IF l_customer_id IS NULL THEN
273 raise wsh_invalid_customer;
274 END IF;
275 -- Initializing the Transactions_History Record.
276
277 l_txns_history_rec.entity_number := l_entity_number;
278 l_txns_history_rec.document_type := p_document_type;
279 l_txns_history_rec.transaction_status := 'IP';
280 l_txns_history_rec.entity_type := p_entity_type;
281 l_txns_history_rec.action_type := p_action_type;
282 l_txns_history_rec.trading_partner_id := p_organization_id;
283 l_txns_history_rec.document_direction := 'O';
284 l_txns_history_rec.item_type := 'WSHSUPI';
285
286 select to_char(WSH_DOCUMENT_NUMBER_S.nextval) into l_txns_history_rec.document_number from dual;
287 IF l_debug_on THEN
288 wsh_debug_sv.log(l_module_name, 'DOCUMENT_NUMBER',l_txns_history_rec.document_number);
289 END IF;
290
291 /* K proj
292 IF ( l_wh_type = 'CMS' and p_action_type = 'D' ) THEN
293 l_txns_history_rec.event_name := 'ORACLE.APPS.FTE.SSNO.CONFIRM';
294 ELSE
295 */
296 l_txns_history_rec.event_name := 'oracle.apps.wsh.sup.ssro';
297 --END IF;
298 IF l_debug_on THEN
299 wsh_debug_sv.log (l_module_name, 'Action Type ' , p_action_type);
300 wsh_debug_sv.log (l_module_name, 'Delivery Id ' , p_entity_id);
301 wsh_debug_sv.log (l_module_name, 'Document Type ' , p_document_type);
302 END IF;
303 IF ( p_action_type = 'A' ) THEN
304
305 IF ( WSH_DELIVERY_UTIL.Is_SendDoc_Allowed( p_entity_id,
306 p_action_type,
307 x_return_status)
308 ) THEN
309
310 --bug 2399697 and 2399687 fixed
311 IF (p_entity_type = 'DLVY' ) THEN
312 FOR detail_info IN get_delivery_details(p_entity_id) LOOP
313 l_delivery_detail_id:= detail_info.delivery_detail_id;
314
315 WSH_SHIP_CONFIRM_ACTIONS2.check_exception(
316 p_delivery_detail_id => detail_info.delivery_detail_id,
317 x_exception_exist => x_exception_exist,
318 x_severity_present => x_severity_present,
319 x_return_status => x_return_status);
320 IF l_debug_on THEN
321 wsh_debug_sv.log(l_module_name, 'check_exception x_exception_exist,x_severity_present,x_return_status',x_exception_exist||','||x_severity_present||','||x_return_status);
322 END IF;
323
324 IF (x_exception_exist = 'Y') THEN
325 raise wsh_details_exceptions;
326 END IF;
327
328 wsh_details_validations.check_credit_holds(
329 p_detail_id => detail_info.delivery_detail_id,
330 p_activity_type => 'SHIP',
331 p_source_line_id => detail_info.source_line_id,
332 p_source_header_id => detail_info.source_header_id,
333 p_source_code => 'OE',
334 p_init_flag => 'Y',
335 x_return_status => x_return_status);
336 IF l_debug_on THEN
337 wsh_debug_sv.log(l_module_name, 'check_credit_holds x_return_status',x_return_status);
338 END IF;
339
340 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
341 raise wsh_details_credit_hold;
342 END IF;
343
344 END LOOP;
345 END IF;
346 --bug 2399697 and 2399687 fixed
347
348 /* heali
349 -- bug fix 2312168
350 open valid_del_cur(p_entity_id);
351 Fetch valid_del_cur into l_valid_del_tmp;
352 close valid_del_cur;
353
354 IF ( nvl(l_valid_del_tmp,FND_API.G_MISS_CHAR) = 'X' ) THEN
355 raise wsh_invalid_delivery;
356 END IF;
357 */
358
359 --heali bug 2399671
360 IF (p_entity_type = 'DLVY' ) THEN
361 l_assigned_to_trip := WSH_Delivery_Validations.Del_Assigned_To_Trip
362 (p_delivery_id => p_entity_id,
363 x_return_status => x_return_status);
364
365 IF l_debug_on THEN
366 wsh_debug_sv.log(l_module_name, 'Del_Assigned_To_Trip x_return_status',x_return_status);
367 END IF;
368 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
369 IF l_debug_on THEN
370 wsh_debug_sv.pop(l_module_name, 'RETURN');
371 END IF;
372 RETURN;
373 ELSIF l_assigned_to_trip = 'Y' THEN
374 raise wsh_del_assign_to_trip;
375 END IF;
376 END IF;
377 --heali
378
379 IF ( l_wh_type = 'TPW' ) THEN
380 open rel_status_tpw_cur(p_entity_id);
381 Fetch rel_status_tpw_cur into l_rel_stat_tmp;
382 close rel_status_tpw_cur;
383 IF l_rel_stat_tmp = 'X' THEN
384 raise wsh_tpw_del_det_rel_stat;
385 END IF;
386 ELSIF ( l_wh_type = 'CMS' ) THEN
387 open rel_status_cms_cur(p_entity_id);
388 Fetch rel_status_cms_cur into l_rel_stat_tmp;
389 close rel_status_cms_cur;
390 IF l_rel_stat_tmp = 'X' THEN
391 raise wsh_cms_del_det_rel_stat;
392 END IF;
393
394 open det_pack_cms_cur(p_entity_id);
395 Fetch det_pack_cms_cur into l_packed_det_tmp;
396 close det_pack_cms_cur;
397
398 IF l_packed_det_tmp = 'X' THEN
399 raise wsh_details_not_packed;
400 END IF;
401
402
403 END IF;
404
405
406 WSH_DELIVERY_UTIL.Update_Dlvy_Status (p_entity_id,
407 p_action_type,
408 p_document_type,
409 x_return_status);
410 --commit;
411 select 1 into l_tmp
412 from wsh_new_deliveries
413 where delivery_id = p_entity_id
414 for update nowait;
415
416 IF l_debug_on THEN
417 wsh_debug_sv.log (l_module_name,'Update_Dlvy_Status-> x_return_status: ',x_return_status);
418 END IF;
419
420 IF ( x_return_status not in (WSH_UTIL_CORE.G_RET_STS_SUCCESS,WSH_UTIL_CORE.G_RET_STS_WARNING) ) THEN
421 raise wsh_insert_history_error;
422 ELSE
423 IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
424 l_warning_count := l_warning_count + 1;
425 END IF;
426 END IF;
427 WSH_EXTERNAL_INTERFACE_SV.RAISE_EVENT(l_txns_history_rec,
428 NULL,
429 x_return_status);
430 IF l_debug_on THEN
431 wsh_debug_sv.log (l_module_name,'RAISE_EVENT x_return_status: ',x_return_status);
432 END IF;
433 IF ( x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
434 raise wsh_raise_event_error;
435 END IF;
436 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
437 ELSE
438 raise wsh_delivery_locked;
439 END IF;
440
441 ELSIF ( p_action_type = 'D' ) THEN --{
442
443
444 IF ( WSH_DELIVERY_UTIL.Is_SendDoc_Allowed(p_entity_id,
445 p_action_type,
446 x_return_status)
447 ) THEN --{
448
449 -- K proj removed the condition on if l_wh_type = 'TPW'
450 -- also removed the else part this condition.
451
452 open orig_txn_hist_cur( l_entity_number, p_organization_id);
453 Fetch orig_txn_hist_cur into l_txns_history_rec.orig_document_number, l_orig_txn_status;
454 close orig_txn_hist_cur;
455 --l_wh_type := 'CMS';
456
457 /* Bug 2399483
458 IF (l_orig_txn_status in ('IP','ER')) THEN
459 raise wsh_document_in_ip_er;
460 END IF;
461 Bug 2399483
462 */
463
464 select 1 into l_tmp
465 from wsh_new_deliveries
466 where delivery_id = p_entity_id
467 for update nowait;
468
469 WSH_DELIVERY_UTIL.Update_Dlvy_Status (p_entity_id,
470 p_action_type,
471 p_document_type,
472 x_return_status);
473
474 --commit;
475 IF l_debug_on THEN
476 wsh_debug_sv.log (l_module_name,'Update_Dlvy_Status-> x_return_status: ',x_return_status);
477 END IF;
478
479 WSH_EXTERNAL_INTERFACE_SV.RAISE_EVENT(l_txns_history_rec,
480 NULL,
481 x_return_status);
482 IF l_debug_on THEN
483 wsh_debug_sv.log (l_module_name,'RAISE_EVENT x_return_status: ',x_return_status);
484 END IF;
485 IF ( x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
486 raise wsh_raise_event_error;
487 END IF;
488 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
489
490 --bmso l_txns_history_rec.transaction_status := 'ST';
491 ELSE --}{
492 raise wsh_cancel_disallowed;
493 END IF; --}
494
495 END IF; --}
496
497 IF (l_warning_count > 0 ) THEN
498 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
499 END IF;
500
501 IF l_debug_on THEN
502 wsh_debug_sv.pop (l_module_name);
503 END IF;
504 EXCEPTION
505 WHEN wsh_incorrect_org THEN
506 x_return_status := wsh_util_core.g_ret_sts_error;
507 FND_MESSAGE.Set_Name('WSH', 'WSH_INCORRECT_ORG');
508 FND_MESSAGE.Set_Token('ORG_CODE', l_org_code);
509 WSH_UTIL_CORE.add_message (x_return_status,l_module_name);
510 IF l_debug_on THEN
511 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_incorrect_org exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
512 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_incorrect_org');
513 END IF;
514 WHEN wsh_invalid_delivery THEN
515 x_return_status := wsh_util_core.g_ret_sts_error;
516 FND_MESSAGE.Set_Name('WSH', 'WSH_INCOMPLETE_DELIVERY');
517 FND_MESSAGE.Set_Token('DEL_NAME', l_entity_number);
518 WSH_UTIL_CORE.add_message (x_return_status,l_module_name);
519 IF l_debug_on THEN
520 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_invalid_delivery exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
521 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_invalid_delivery');
522 END IF;
523 WHEN wsh_details_not_packed THEN
524 x_return_status := wsh_util_core.g_ret_sts_error;
525 FND_MESSAGE.Set_Name('WSH', 'WSH_DEL_PACK_ITEMS_UNPACKED');
526 FND_MESSAGE.Set_Token('DEL_NAME', l_entity_number);
527 WSH_UTIL_CORE.add_message (x_return_status,l_module_name);
528 IF l_debug_on THEN
529 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_details_not_packed exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
530 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_details_not_packed');
531 END IF;
532 WHEN wsh_tpw_del_det_rel_stat THEN
533 x_return_status := wsh_util_core.g_ret_sts_error;
534 FND_MESSAGE.Set_Name('WSH', 'WSH_TPW_DEL_DET_REL_STAT');
535 FND_MESSAGE.Set_Token('DEL_NAME', l_entity_number);
536 WSH_UTIL_CORE.add_message (x_return_status,l_module_name);
537 IF l_debug_on THEN
538 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_tpw_del_det_rel_stat exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
539 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_tpw_del_det_rel_stat');
540 END IF;
541 WHEN wsh_cms_del_det_rel_stat THEN
542 x_return_status := wsh_util_core.g_ret_sts_error;
543 FND_MESSAGE.Set_Name('WSH', 'WSH_CMS_DEL_DET_REL_STAT');
544 FND_MESSAGE.Set_Token('DEL_NAME', l_entity_number);
545 WSH_UTIL_CORE.add_message (x_return_status,l_module_name);
546 IF l_debug_on THEN
547 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_cms_del_det_rel_stat exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
548 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_cms_del_det_rel_stat');
549 END IF;
550 WHEN wsh_insert_history_error THEN
551 IF ( p_action_type = 'A' ) THEN
552 WSH_DELIVERY_UTIL.Update_Dlvy_Status (p_entity_id,
553 NULL,
554 p_document_type,
555 x_return_status);
556 --commit;
557 x_return_status := wsh_util_core.g_ret_sts_error;
558 ELSIF ( p_action_type = 'D' ) THEN
559 WSH_DELIVERY_UTIL.Update_Dlvy_Status (p_entity_id,
560 'A',
561 p_document_type,
562 x_return_status);
563 --commit;
564 x_return_status := wsh_util_core.g_ret_sts_error;
565 END IF;
566 IF l_debug_on THEN
567 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_insert_history_error exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
568 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_insert_history_error');
569 END IF;
570 WHEN wsh_raise_event_error THEN
571 IF ( p_action_type = 'A' ) THEN
572 WSH_DELIVERY_UTIL.Update_Dlvy_Status (p_entity_id,
573 NULL,
574 p_document_type,
575 x_return_status);
576 --commit;
577 x_return_status := wsh_util_core.g_ret_sts_error;
578 ELSIF ( p_action_type = 'D' ) THEN
579 WSH_DELIVERY_UTIL.Update_Dlvy_Status (p_entity_id,
580 'A',
581 p_document_type,
582 x_return_status);
583 --commit;
584 x_return_status := wsh_util_core.g_ret_sts_error;
585 ELSE
586 x_return_status := wsh_util_core.g_ret_sts_error;
587 END IF;
588 IF l_debug_on THEN
589 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_raise_event_error exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
590 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_raise_event_error');
591 END IF;
592 WHEN wsh_delivery_locked THEN
593 x_return_status := wsh_util_core.g_ret_sts_error;
594 FND_MESSAGE.Set_Name('WSH', 'WSH_DELIVERY_LOCKED');
595 FND_MESSAGE.Set_Token('DEL_NAME', l_entity_number);
596 WSH_UTIL_CORE.add_message (x_return_status,l_module_name);
597 IF l_debug_on THEN
598 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_delivery_locked exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
599 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_delivery_locked');
600 END IF;
601 WHEN wsh_cancel_disallowed THEN
602 x_return_status := wsh_util_core.g_ret_sts_error;
603 FND_MESSAGE.Set_Name('WSH', 'WSH_CANCEL_DISALLOWED');
604 FND_MESSAGE.Set_Token('DEL_NAME', l_entity_number);
605 WSH_UTIL_CORE.add_message (x_return_status,l_module_name);
606 IF l_debug_on THEN
607 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_cancel_disallowed exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
608 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_cancel_disallowed');
609 END IF;
610 WHEN wsh_del_assign_to_trip THEN
611 FND_MESSAGE.SET_NAME('WSH','WSH_DEL_OUTBOUND_FAILED_TRIP');
612 FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_entity_id));
613 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
614 wsh_util_core.add_message(x_return_status,l_module_name);
615 IF l_debug_on THEN
616 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_del_assign_to_trip exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
617 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_del_assign_to_trip');
618 END IF;
619 WHEN wsh_details_exceptions THEN
620 FND_MESSAGE.SET_NAME('WSH','WSH_XC_EXIST_DET');
621 FND_MESSAGE.SET_TOKEN('DEL_DET_ID', to_char(l_delivery_detail_id));
622 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
623 wsh_util_core.add_message(x_return_status,l_module_name);
624 IF l_debug_on THEN
625 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_details_exceptions exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
626 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_details_exceptions');
627 END IF;
628 WHEN wsh_details_credit_hold THEN
629 FND_MESSAGE.SET_NAME('WSH','WSH_SR_CREDIT_HOLD_ERR');
630 FND_MESSAGE.SET_TOKEN('DEL_NAME',l_entity_number);
631 FND_MESSAGE.SET_TOKEN('DET_NAME',to_char(l_delivery_detail_id));
632 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
633 wsh_util_core.add_message(x_return_status,l_module_name);
634 IF l_debug_on THEN
635 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_details_credit_hold exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
636 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_details_credit_hold ');
637 END IF;
638 WHEN wsh_invalid_customer THEN
639 FND_MESSAGE.SET_NAME('WSH','WSH_NO_CUST_DEF_ERROR');
640 FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_entity_id));
641 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
642 wsh_util_core.add_message(x_return_status,l_module_name);
643 IF l_debug_on THEN
644 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_invalid_customer exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
645 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_invalid_customer');
646 END IF;
647 WHEN others THEN
648 x_return_status := wsh_util_core.g_ret_sts_unexp_error;
649 IF l_debug_on THEN
650 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
651 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
652 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
653 END IF;
654 END Send_Shipment_Request;
655
656 PROCEDURE Get_Event_Key(p_item_type IN VARCHAR2 DEFAULT NULL,
657 p_orig_doc_number IN VARCHAR2 DEFAULT NULL,
658 p_organization_id IN NUMBER,
659 p_event_name IN VARCHAR2,
660 p_delivery_name IN VARCHAR2 DEFAULT NULL,
661 x_event_key OUT NOCOPY VARCHAR2,
662 x_return_status OUT NOCOPY VARCHAR2
663 )
664 IS
665
666 cursor get_key is
667 select event_key
668 from wsh_transactions_history
669 where item_type = p_item_type
670 and document_number = p_orig_doc_number
671 and trading_partner_id = p_organization_id;
672
673 cursor del_cur is
674 select delivery_id
675 from wsh_new_deliveries
676 where name = p_delivery_name
677 and organization_id = p_organization_id;
678
679
680 l_event_code VARCHAR2(30);
681 l_event_key VARCHAR2(30);
682
683 l_temp NUMBER;
684 l_wh_type VARCHAR2(10);
685 l_delivery_id NUMBER;
686 wsh_invalid_event_name EXCEPTION;
687 wsh_invalid_delivery_name EXCEPTION;
688 --
689 l_debug_on BOOLEAN;
690 --
691 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_EVENT_KEY';
692 --
693 BEGIN
694 --
695 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
696 --
697 IF l_debug_on IS NULL
698 THEN
699 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
700 END IF;
701 --
702 IF l_debug_on THEN
703 wsh_debug_sv.push(l_module_name, 'Get_Event_Key');
704 wsh_debug_sv.log (l_module_name, 'Event Name ' , p_event_name);
705 wsh_debug_sv.log (l_module_name, 'Organization Id ' , p_organization_id);
706 wsh_debug_sv.log (l_module_name, 'Delivery Name ' , p_delivery_name);
707 wsh_debug_sv.log (l_module_name, 'Event Code ' , l_event_code);
708 END IF;
709
710 x_return_status := wsh_util_core.g_ret_sts_success;
711 l_event_code := UPPER(SUBSTRB (p_event_name, INSTRB(p_event_name, '.', -1) + 1));
712 IF l_event_code IN ('SPWF','SSAI','SSAO','CONFIRM') THEN
713 open get_key;
714 Fetch get_key into x_event_key;
715 close get_key;
716 ELSIF l_event_code = 'SSRO' THEN
717 IF p_delivery_name IS NOT NULL THEN
718 open del_cur;
719 fetch del_cur into l_delivery_id;
720 close del_cur;
721 select wsh_transaction_s.nextval into l_temp from dual;
722 l_wh_type := WSH_EXTERNAL_INTERFACE_SV.Get_Warehouse_Type( p_organization_id => p_organization_id,
723 x_return_status => x_return_status,
724 p_delivery_id => l_delivery_id
725 );
726 IF l_debug_on THEN
727 wsh_debug_sv.log (l_module_name,'Get_Warehouse_Type l_wh_type,x_return_status',l_wh_type||','||x_return_status );
728 END IF;
729 ELSE
730 raise wsh_invalid_delivery_name;
731 END IF;
732 x_event_key := l_wh_type || to_char(l_temp);
733 ELSIF l_event_code IN ('SSRI','SCBOD') THEN
734 select wsh_transaction_s.nextval into l_temp from dual;
735 x_event_key := to_char(l_temp);
736 ELSE
737 raise wsh_invalid_event_name;
738 END IF;
739
740 IF l_debug_on THEN
741 WSH_DEBUG_SV.log(l_module_name, 'Event Key'|| x_event_key);
742 wsh_debug_sv.pop(l_module_name);
743 END IF;
744 EXCEPTION
745
746 WHEN wsh_invalid_event_name THEN
747 x_return_status := wsh_util_core.g_ret_sts_error;
748 IF l_debug_on THEN
749 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_invalid_event_name exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
750 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_invalid_event_name');
751 END IF;
752 WHEN wsh_invalid_delivery_name THEN
753 x_return_status := wsh_util_core.g_ret_sts_error;
754 IF l_debug_on THEN
755 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_invalid_delivery_name exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
756 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_invalid_delivery_name');
757 END IF;
758 WHEN others THEN
759 x_return_status := wsh_util_core.g_ret_sts_unexp_error;
760 IF l_debug_on THEN
761 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
762 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
763 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
764 END IF;
765 END Get_Event_Key;
766
767
768 PROCEDURE Unlock_Delivery_WF( item_type IN VARCHAR2,
769 item_key IN VARCHAR2,
770 actid IN NUMBER,
771 funcmode IN VARCHAR2,
772 resultout OUT NOCOPY VARCHAR2
773 )
774 IS
775
776 CURSOR c_delId_cur IS
777 SELECT wnd.delivery_id
778 from wsh_new_deliveries wnd,
779 wsh_transactions_history wth
780 where wnd.name = wth.entity_number
781 and entity_type='DLVY'
782 and wth.event_key = item_key
783 and wth.item_type = item_type
784 and wth.document_direction='O';
785
786 l_return_status VARCHAR2(1);
787 l_delivery_id NUMBER;
788 wsh_unlock_error EXCEPTION;
789 wsh_del_not_found EXCEPTION;
790 --
791 l_debug_on BOOLEAN;
792 --
793 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UNLOCK_DELIVERY_WF';
794 --
795 BEGIN
796 --
797 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
798 --
799 IF l_debug_on IS NULL
800 THEN
801 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
802 END IF;
803 --
804 IF l_debug_on THEN
805 wsh_debug_sv.push(l_module_name, 'Unlock_Delivery_WF');
806 wsh_debug_sv.log(l_module_name, 'item_type',item_type);
807 wsh_debug_sv.log(l_module_name, 'item_key',item_key);
808 wsh_debug_sv.log(l_module_name, 'actid',actid);
809 wsh_debug_sv.log(l_module_name, 'funcmode',funcmode);
810 END IF;
811
812
813 IF ( funcmode = 'RUN' ) THEN
814
815 open c_delId_cur;
816 Fetch c_delId_cur into l_delivery_id;
817 IF ( c_delId_cur%NOTFOUND ) THEN
818 CLOSE c_delId_cur;
819 resultout := 'COMPLETE:FAILURE';
820 raise wsh_del_not_found;
821 END IF;
822
823 WSH_DELIVERY_UTIL.Update_Dlvy_Status(l_delivery_id,
824 NULL,
825 NULL,
826 l_return_status);
827 IF l_debug_on THEN
828 wsh_debug_sv.log(l_module_name, 'l_return_status',l_return_status);
829 END IF;
830
831 IF ( l_return_status not in (WSH_UTIL_CORE.G_RET_STS_SUCCESS,WSH_UTIL_CORE.G_RET_STS_WARNING) ) THEN
832 raise wsh_unlock_error;
833 ELSE
834 resultout := 'COMPLETE:SUCCESS';
835 IF l_debug_on THEN
836 wsh_debug_sv.log(l_module_name, 'resultout',resultout);
837 wsh_debug_sv.pop (l_module_name,'RETURN');
838 END IF;
839 RETURN;
840 END IF;
841
842 END IF;
843
844 IF l_debug_on THEN
845 wsh_debug_sv.log(l_module_name, 'resultout',resultout);
846 wsh_debug_sv.pop (l_module_name);
847 END IF;
848 EXCEPTION
849 WHEN wsh_del_not_found THEN
850 resultout := 'COMPLETE:FAILURE';
851 IF l_debug_on THEN
852 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_del_not_found exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
853 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_del_not_found');
854 END IF;
855 raise;
856 WHEN wsh_unlock_error THEN
857 resultout := 'COMPLETE:FAILURE';
858 IF l_debug_on THEN
859 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_unlock_error exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
860 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_unlock_error');
861 END IF;
862 raise;
863 WHEN OTHERS THEN
864 resultout := 'COMPLETE:FAILURE';
865 IF l_debug_on THEN
866 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
867 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
868 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
869 END IF;
870 raise;
871 END Unlock_Delivery_WF;
872
873 PROCEDURE update_atnms( p_transaction_id IN number)
874
875 IS
876
877 pragma AUTONOMOUS_TRANSACTION;
878 l_debug_on BOOLEAN;
879
880 --
881 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.'
882 || 'UPDATE_ATNMS';
883
884 BEGIN
885 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
886 --
887 IF l_debug_on IS NULL
888 THEN
889 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
890 END IF;
891 --
892 IF l_debug_on THEN
893 wsh_debug_sv.push(l_module_name);
894 wsh_debug_sv.log(l_module_name, 'p_transaction_id',p_transaction_id);
895 END IF;
896
897 UPDATE wsh_transactions_history
898 SET transaction_status = 'ER'
899 WHERE transaction_id = p_transaction_id;
900
901 IF l_debug_on THEN
902 wsh_debug_sv.log(l_module_name, 'ROWCOUNT', SQL%ROWCOUNT);
903 END IF;
904
905 COMMIT;
906
907 IF l_debug_on THEN
908 wsh_debug_sv.pop (l_module_name);
909 END IF;
910
911 EXCEPTION
912 WHEN OTHERS THEN
913 IF l_debug_on THEN
914 WSH_DEBUG_SV.logmsg(l_module_name,
915 'Unexpected error has occured. Oracle error message is '
916 || SQLERRM, WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
917 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
918 END IF;
919 raise;
920 END update_atnms;
921
922
923 PROCEDURE Process_Inbound_Delivery_WF( Item_type IN VARCHAR2,
924 Item_key IN VARCHAR2,
925 Actid IN NUMBER,
926 Funcmode IN VARCHAR2,
927 Resultout OUT NOCOPY VARCHAR2
928 )
929
930 IS
931
932
933 l_txns_history_rec WSH_TRANSACTIONS_HISTORY_PKG.Txns_History_Record_Type;
934 l_return_status VARCHAR2(1);
935 l_document_type VARCHAR2(30);
936 --k proj
937 l_cancellation_in_progress BOOLEAN := FALSE;
938 l_num_warn number := 0;
939 l_num_err number := 0;
940 l_sr_trx_id NUMBER;
941 l_enity_number wsh_transactions_history.entity_number%TYPE;
942
943 CURSOR c_get_entity_number (v_trx_id NUMBER) IS --bmso
944 SELECT wth1.entity_number , wth1.transaction_id
945 FROM wsh_transactions_history wth1,
946 wsh_transactions_history wth2
947 WHERE wth1.action_type = 'A'
948 AND wth1.entity_type = 'DLVY'
949 AND wth1.document_type = 'SR'
950 AND wth1.document_direction = 'O'
951 AND wth1.document_number = wth2.ORIG_DOCUMENT_NUMBER
952 AND wth1.event_key = wth2.event_key
953 AND wth2.document_direction = 'I'
954 AND wth2.transaction_id = v_trx_id
955 AND wth2.document_type = 'SA'
956 ORDER BY wth1.transaction_id desc;
957
958 CURSOR c_get_cancel_record (v_sr_trx_id number) IS
959 SELECT wth2.transaction_id ,
960 wth2.document_type ,
961 wth2.document_direction ,
962 wth2.document_number ,
963 wth2.orig_document_number ,
964 wth2.entity_number ,
965 wth2.entity_type ,
966 wth2.trading_partner_id ,
967 wth2.action_type ,
968 wth2.transaction_status ,
969 wth2.ecx_message_id ,
970 wth2.event_name ,
971 wth2.event_key ,
972 wth2.item_type ,
973 wth2.internal_control_number ,
974 wth2.attribute_category ,
975 wth2.attribute1 ,
976 wth2.attribute2 ,
977 wth2.attribute3 ,
978 wth2.attribute4 ,
979 wth2.attribute5 ,
980 wth2.attribute6 ,
981 wth2.attribute7 ,
982 wth2.attribute8 ,
983 wth2.attribute9 ,
984 wth2.attribute10 ,
985 wth2.attribute11 ,
986 wth2.attribute12 ,
987 wth2.attribute13 ,
988 wth2.attribute14 ,
989 wth2.attribute15
990 FROM wsh_transactions_history wth1,
991 wsh_transactions_history wth2
992 WHERE wth1.transaction_id = v_sr_trx_id
993 AND wth2.entity_number = wth1.entity_number
994 AND wth2.document_direction = 'O'
995 AND wth2.document_type = 'SR'
996 AND wth2.action_type = 'D'
997 ORDER BY wth2.transaction_id desc;
998
999 l_cancel_history_rec WSH_TRANSACTIONS_HISTORY_PKG.Txns_History_Record_Type;
1000
1001 wsh_process_inbound EXCEPTION;
1002 --
1003 l_debug_on BOOLEAN;
1004 --
1005 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PROCESS_INBOUND_DELIVERY_WF';
1006 --
1007 BEGIN
1008 --
1009 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1010 --
1011 IF l_debug_on IS NULL
1012 THEN
1013 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1014 END IF;
1015 --
1016 IF l_debug_on THEN
1017 wsh_debug_sv.push(l_module_name, 'Process_Inbound_Delivery_WF');
1018 wsh_debug_sv.log(l_module_name, 'item_type',item_type);
1019 wsh_debug_sv.log(l_module_name, 'item_key',item_key);
1020 wsh_debug_sv.log(l_module_name, 'actid',actid);
1021 wsh_debug_sv.log(l_module_name, 'funcmode',funcmode);
1022 END IF;
1023
1024
1025 IF ( funcmode = 'RUN' ) THEN
1026
1027 IF ( item_type = 'WSHSUPI') THEN
1028 l_document_type := 'SA';
1029 ELSE
1030 l_document_type := 'SR';
1031 END IF;
1032 WSH_TRANSACTIONS_HISTORY_PKG.Get_Txns_History(
1033 Item_type,
1034 Item_key,
1035 'I',
1036 l_document_type,
1037 l_txns_history_rec,
1038 l_return_status
1039 );
1040 IF l_debug_on THEN
1041 wsh_debug_sv.log(l_module_name, 'Get_Txns_History l_return_status ',l_return_status);
1042 END IF;
1043 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
1044 raise wsh_process_inbound;
1045 END IF;
1046 --k proj
1047 IF l_document_type = 'SA' AND branch_cms_tpw_flow(p_event_key => item_key)
1048 THEN --{
1049
1050 OPEN c_get_entity_number(l_txns_history_rec.transaction_id);
1051 FETCH c_get_entity_number INTO l_enity_number, l_sr_trx_id;
1052 CLOSE c_get_entity_number;
1053
1054 IF l_debug_on THEN
1055 wsh_debug_sv.log(l_module_name, 'l_enity_number ',l_enity_number);
1056 wsh_debug_sv.log(l_module_name, 'l_sr_trx_id ',l_sr_trx_id);
1057 END IF;
1058
1059 IF l_txns_history_rec.transaction_status IN ('IP','ER') THEN --{
1060
1061
1062 Check_cancellation_inprogress (
1063 p_delivery_name => l_enity_number,
1064 --=> l_txns_history_rec.entity_number,
1065 x_cancellation_in_progress =>
1066 l_cancellation_in_progress,
1067 x_return_status => l_return_status
1068 );
1069 IF l_debug_on THEN
1070 wsh_debug_sv.log(l_module_name, 'l_return_status ',
1071 l_return_status);
1072 wsh_debug_sv.log(l_module_name, 'l_cancellation_in_progress',
1073 l_cancellation_in_progress);
1074 END IF;
1075
1076 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1077 RAISE fnd_api.g_exc_error;
1078 END IF;
1079
1080 IF l_cancellation_in_progress THEN
1081
1082 update_atnms(l_txns_history_rec.transaction_id);
1083 RAISE fnd_api.g_exc_error;
1084
1085 END IF;
1086
1087 END IF; --}
1088 END IF; --}
1089
1090 WSH_PROCESS_INTERFACED_PKG.Process_Inbound(l_txns_history_rec,
1091 l_return_status);
1092 IF l_debug_on THEN
1093 wsh_debug_sv.log(l_module_name, 'Process_Inbound l_return_status ',l_return_status);
1094 END IF;
1095
1096 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
1097 raise wsh_process_inbound;
1098 ELSE
1099 -- send a CBOD to close the cancellation workflow instance bmso k proj
1100
1101 IF branch_cms_tpw_flow(p_event_key => item_key)
1102 AND l_document_type = 'SA'
1103 THEN --{
1104 OPEN c_get_cancel_record(l_sr_trx_id);
1105 FETCH c_get_cancel_record INTO l_cancel_history_rec;
1106
1107 IF c_get_cancel_record%FOUND THEN --{
1108 l_cancel_history_rec.Event_Name := 'ORACLE.APPS.FTE.SSNO.CONFIRM';
1109
1110 WSH_EXTERNAL_INTERFACE_SV.Raise_Event (
1111 l_cancel_history_rec,
1112 '99',
1113 l_Return_Status );
1114 wsh_util_core.api_post_call(
1115 p_return_status => l_return_status,
1116 x_num_warnings => l_num_warn,
1117 x_num_errors => l_num_err);
1118
1119 END IF; --}
1120 CLOSE c_get_cancel_record;
1121 END IF; --}
1122
1123 resultout := 'COMPLETE:SUCCESS';
1124 IF l_debug_on THEN
1125 wsh_debug_sv.log(l_module_name, 'resultout',resultout);
1126 wsh_debug_sv.pop (l_module_name,'RETURN');
1127 END IF;
1128 RETURN;
1129 END IF;
1130
1131 END IF;
1132
1133 IF l_debug_on THEN
1134 wsh_debug_sv.log(l_module_name, 'resultout',resultout);
1135 wsh_debug_sv.pop (l_module_name);
1136 END IF;
1137 EXCEPTION
1138 WHEN wsh_process_inbound THEN
1139 resultout := 'COMPLETE:FAILURE';
1140 IF l_debug_on THEN
1141 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_process_inbound exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1142 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_process_inbound');
1143 END IF;
1144 raise;
1145 WHEN fnd_api.g_exc_error THEN
1146 resultout := 'COMPLETE:FAILURE';
1147 IF l_debug_on THEN
1148 WSH_DEBUG_SV.logmsg(l_module_name,'fnd_api.g_exc_error exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1149 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:fnd_api.g_exc_error');
1150 END IF;
1151 raise;
1152 WHEN OTHERS THEN
1153 resultout := 'COMPLETE:FAILURE';
1154 IF l_debug_on THEN
1155 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
1156 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1157 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1158 END IF;
1159 raise;
1160 END Process_Inbound_Delivery_WF;
1161
1162
1163 PROCEDURE Update_Txn_Hist_Err_WF( Item_type IN VARCHAR2,
1164 Item_key IN VARCHAR2,
1165 Actid IN NUMBER,
1166 Funcmode IN VARCHAR2,
1167 Resultout OUT NOCOPY VARCHAR2
1168 )
1169
1170 IS
1171 l_return_status VARCHAR2(1);
1172 wsh_update_history EXCEPTION;
1173 --
1174 l_debug_on BOOLEAN;
1175 --
1176 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_TXN_HIST_ERR_WF';
1177 --
1178 BEGIN
1179 --
1180 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1181 --
1182 IF l_debug_on IS NULL
1183 THEN
1184 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1185 END IF;
1186 --
1187 IF l_debug_on THEN
1188 wsh_debug_sv.push(l_module_name, 'Update_Txn_Hist_Err_WF');
1189 wsh_debug_sv.log(l_module_name, 'item_type',item_type);
1190 wsh_debug_sv.log(l_module_name, 'item_key',item_key);
1191 wsh_debug_sv.log(l_module_name, 'actid',actid);
1192 wsh_debug_sv.log(l_module_name, 'funcmode',funcmode);
1193 END IF;
1194
1195
1196 IF ( funcmode = 'RUN' ) THEN
1197 Update_Txn_History ( Item_type,
1198 Item_key,
1199 'ER',
1200 l_return_status
1201 );
1202 IF l_debug_on THEN
1203 wsh_debug_sv.log(l_module_name, 'Update_Txn_History l_return_status ',l_return_status);
1204 END IF;
1205
1206 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
1207 raise wsh_update_history;
1208 ELSE
1209 resultout := 'COMPLETE:SUCCESS';
1210 IF l_debug_on THEN
1211 wsh_debug_sv.log(l_module_name, 'resultout',resultout);
1212 wsh_debug_sv.pop(l_module_name, 'RETURN');
1213 END IF;
1214 RETURN;
1215 END IF;
1216
1217 END IF;
1218
1219 IF l_debug_on THEN
1220 wsh_debug_sv.pop (l_module_name);
1221 END IF;
1222 EXCEPTION
1223 WHEN wsh_update_history THEN
1224 resultout := 'COMPLETE:FAILURE';
1225 IF l_debug_on THEN
1226 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_update_history exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1227 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_update_history');
1228 END IF;
1229 raise;
1230 WHEN OTHERS THEN
1231 resultout := 'COMPLETE:FAILURE';
1232 IF l_debug_on THEN
1233 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
1234 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1235 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1236 END IF;
1237 raise;
1238 END Update_Txn_Hist_Err_WF;
1239
1240 PROCEDURE Update_Txn_Hist_Success_WF( Item_type IN VARCHAR2,
1241 Item_key IN VARCHAR2,
1242 Actid IN NUMBER,
1243 Funcmode IN VARCHAR2,
1244 Resultout OUT NOCOPY VARCHAR2
1245 )
1246
1247 IS
1248 l_return_status VARCHAR2(1);
1249 wsh_update_history EXCEPTION;
1250 --
1251 l_debug_on BOOLEAN;
1252 --
1253 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_TXN_HIST_SUCCESS_WF';
1254 --
1255 BEGIN
1256 --
1257 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1258 --
1259 IF l_debug_on IS NULL
1260 THEN
1261 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1262 END IF;
1263 --
1264 IF l_debug_on THEN
1265 wsh_debug_sv.push(l_module_name, 'Update_Txn_Hist_Err_WF');
1266 wsh_debug_sv.log(l_module_name, 'item_type',item_type);
1267 wsh_debug_sv.log(l_module_name, 'item_key',item_key);
1268 wsh_debug_sv.log(l_module_name, 'actid',actid);
1269 wsh_debug_sv.log(l_module_name, 'funcmode',funcmode);
1270 END IF;
1271
1272 IF ( funcmode = 'RUN' ) THEN
1273 Update_Txn_History ( Item_type,
1274 Item_key,
1275 'ST',
1276 l_return_status
1277 );
1278 IF l_debug_on THEN
1279 wsh_debug_sv.log(l_module_name, 'Update_Txn_History l_return_status ',l_return_status);
1280 END IF;
1281 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
1282 raise wsh_update_history;
1283 ELSE
1284 resultout := 'COMPLETE:SUCCESS';
1285 IF l_debug_on THEN
1286 wsh_debug_sv.log(l_module_name, 'resultout',resultout);
1287 wsh_debug_sv.pop(l_module_name, 'RETURN');
1288 END IF;
1289 RETURN;
1290 END IF;
1291 END IF;
1292 IF l_debug_on THEN
1293 wsh_debug_sv.pop (l_module_name);
1294 END IF;
1295 EXCEPTION
1296 WHEN wsh_update_history THEN
1297 resultout := 'COMPLETE:FAILURE';
1298 IF l_debug_on THEN
1299 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_update_history exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1300 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_update_history');
1301 END IF;
1302 raise;
1303 WHEN OTHERS THEN
1304 resultout := 'COMPLETE:FAILURE';
1305 IF l_debug_on THEN
1306 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
1307 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1308 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1309 END IF;
1310 raise;
1311 END Update_Txn_Hist_Success_WF;
1312
1313 PROCEDURE Update_Txn_History ( p_item_type IN VARCHAR2,
1314 p_item_key IN VARCHAR2,
1315 p_transaction_status IN VARCHAR2,
1316 x_return_status OUT NOCOPY VARCHAR2
1317 )
1318 IS
1319
1320
1321 pragma AUTONOMOUS_TRANSACTION;
1322
1323 l_txns_history_rec WSH_TRANSACTIONS_HISTORY_PKG.Txns_History_Record_Type;
1324 l_document_type VARCHAR2(2);
1325 l_txn_direction VARCHAR2(1);
1326 l_txn_id NUMBER;
1327
1328 wsh_update_history EXCEPTION;
1329
1330 --
1331 l_debug_on BOOLEAN;
1332 --
1333 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_TXN_HISTORY';
1334 --
1335 BEGIN
1336 --
1337 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1338 --
1339 IF l_debug_on IS NULL
1340 THEN
1341 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1342 END IF;
1343 --
1344 IF l_debug_on THEN
1345 wsh_debug_sv.push(l_module_name);
1346 wsh_debug_sv.log(l_module_name, 'p_item_type',p_item_type);
1347 wsh_debug_sv.log(l_module_name, 'p_item_key',p_item_key);
1348 wsh_debug_sv.log(l_module_name, 'p_transaction_status',p_transaction_status);
1349 END IF;
1350
1351 IF ( p_item_type = 'WSHSUPI' ) THEN
1352 l_document_type := 'SR';
1353 l_txn_direction := 'O';
1354 ELSIF ( p_item_type = 'WSHTPWI' AND p_transaction_status = 'ER' ) THEN
1355 l_txn_direction := 'I';
1356 l_document_type := 'SR';
1357 ELSE
1358 l_document_type := 'SA';
1359 l_txn_direction := 'O';
1360 END IF;
1361 WSH_TRANSACTIONS_HISTORY_PKG.Get_Txns_History( p_item_type,
1362 p_item_key,
1363 l_txn_direction,
1364 l_document_type,
1365 l_txns_history_rec,
1366 x_return_status );
1367 IF l_debug_on THEN
1368 wsh_debug_sv.log(l_module_name, 'Get_Txns_History x_return_status',x_return_status);
1369 END IF;
1370
1371 l_txns_history_rec.transaction_status := p_transaction_status;
1372 IF ( x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
1373 raise wsh_update_history;
1374 END IF;
1375
1376 WSH_TRANSACTIONS_HISTORY_PKG.Create_Update_Txns_History ( l_txns_history_rec,
1377 l_txn_id,
1378 x_return_status );
1379 IF l_debug_on THEN
1380 wsh_debug_sv.log(l_module_name, 'Create_Update_Txns_History x_return_status',x_return_status);
1381 END IF;
1382
1383 IF ( x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
1384 raise wsh_update_history;
1385 ELSE
1386 COMMIT;
1387 END IF;
1388
1389 IF l_debug_on THEN
1390 wsh_debug_sv.pop (l_module_name);
1391 END IF;
1392 EXCEPTION
1393 WHEN wsh_update_history THEN
1394 x_return_status := wsh_util_core.g_ret_sts_error;
1395 IF l_debug_on THEN
1396 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_update_history exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1397 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_update_history');
1398 END IF;
1399 WHEN OTHERS THEN
1400 x_return_status := wsh_util_core.g_ret_sts_error;
1401 IF l_debug_on THEN
1402 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
1403 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1404 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1405 END IF;
1406 END Update_Txn_History;
1407
1408 PROCEDURE WSHSUPI_SELECTOR( Item_type IN VARCHAR2,
1409 Item_key IN VARCHAR2,
1410 Actid IN NUMBER,
1411 Funcmode IN VARCHAR2,
1412 Resultout IN OUT NOCOPY VARCHAR2
1413 ) IS
1414 l_user_id NUMBER;
1415 l_resp_id NUMBER;
1416 l_resp_appl_id NUMBER;
1417 l_org_id NUMBER;
1418 l_current_org_id NUMBER;
1419 l_client_org_id NUMBER;
1420 --
1421 l_debug_on BOOLEAN;
1422 --
1423 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'WSHSUPI_SELECTOR';
1424 --
1425 BEGIN
1426 --
1427 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1428 --
1429 IF l_debug_on IS NULL
1430 THEN
1431 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1432 END IF;
1433 --
1434 wsh_debug_sv.start_debug('wf_context');
1435 IF l_debug_on THEN
1436 wsh_debug_sv.push(l_module_name, 'WSHSUPI_SELECTOR');
1437 wsh_debug_sv.log (l_module_name, 'Item Type', Item_type);
1438 wsh_debug_sv.log (l_module_name, 'Item_Key', Item_key);
1439 wsh_debug_sv.log (l_module_name, 'Funcmode', Funcmode);
1440 END IF;
1441
1442 IF(funcmode = 'RUN') THEN
1443 Resultout := 'COMPLETE';
1444 ELSIF(funcmode = 'SET_CTX') THEN
1445 l_user_id := wf_engine.GetItemAttrNumber(
1446 'WSHSUPI',
1447 Item_key,
1448 'USER_ID');
1449 l_resp_appl_id := wf_engine.GetItemAttrNumber(
1450 'WSHSUPI',
1451 Item_key,
1452 'APPLICATION_ID');
1453
1454 l_resp_id := wf_engine.GetItemAttrNumber(
1455 'WSHSUPI',
1456 Item_key,
1457 'RESPONSIBILITY_ID');
1458
1459 IF(l_resp_appl_id IS NULL OR l_resp_id IS NULL) THEN
1460 RAISE no_data_found;
1461 ELSE
1462 FND_GLOBAL.Apps_Initialize(l_user_id, l_resp_id, l_resp_appl_id);
1463 END IF;
1464 Resultout := 'COMPLETE';
1465 ELSIF(funcmode = 'TEST_CTX') THEN
1466
1467
1468 Resultout := 'TRUE';
1469
1470 END IF; -- if funcmode = run
1471
1472 IF l_debug_on THEN
1473 wsh_debug_sv.log (l_module_name, 'Resultout', Resultout);
1474 wsh_debug_sv.pop(l_module_name);
1475 END IF;
1476 wsh_debug_sv.stop_debug;
1477 EXCEPTION
1478 WHEN OTHERS THEN
1479 resultout := 'COMPLETE:FAILURE';
1480 IF l_debug_on THEN
1481 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
1482 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1483 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1484 END IF;
1485 wsh_debug_sv.stop_debug;
1486 RAISE;
1487 END WSHSUPI_SELECTOR;
1488
1489 --k proj
1490
1491 /*---------------------------------------------------------------------
1492
1493 FUNCTION : branch_cms_tpw_flow
1494 Parameter: p_event_key
1495
1496 Comments :
1497
1498 This function is used to branch the flow of the cancellation for TPW
1499 and CMS. If the cancellation is done for CMS, this function will return
1500 TRUE. In future (if it is decided that CMS and TPW have same flow for
1501 cancellation) this function returns always TRUE;
1502
1503 ---------------------------------------------------------------------*/
1504
1505 FUNCTION branch_cms_tpw_flow (p_event_key IN VARCHAR2)
1506 RETURN BOOLEAN IS
1507
1508 l_cms_flow BOOLEAN := FALSE;
1509 l_debug_on BOOLEAN;
1510 --
1511 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' ||
1512 'BRANCH_CMS_TPW_FLOW';
1513 --
1514 BEGIN
1515
1516 --
1517 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1518 --
1519 IF l_debug_on IS NULL
1520 THEN
1521 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1522 END IF;
1523 --
1524 IF l_debug_on THEN
1525 wsh_debug_sv.push(l_module_name);
1526 wsh_debug_sv.log(l_module_name, 'p_event_key',p_event_key);
1527 END IF;
1528
1529 IF p_event_key LIKE 'CMS%' THEN
1530 l_cms_flow := TRUE;
1531 END IF;
1532
1533 IF l_debug_on THEN
1534 wsh_debug_sv.log(l_module_name, 'l_cms_flow',l_cms_flow);
1535 wsh_debug_sv.pop(l_module_name);
1536 END IF;
1537
1538 RETURN l_cms_flow;
1539
1540 EXCEPTION
1541
1542 WHEN OTHERS THEN
1543 wsh_util_core.default_handler('WSH_TRANSACTIONS_UTIL.branch_cms_tpw_flow');
1544 IF l_debug_on THEN
1545 WSH_DEBUG_SV.logmsg(l_module_name,
1546 'Unexpected error has occured. Oracle error message is '||
1547 substr(SQLERRM,1,200), WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1548 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1549 END IF;
1550
1551 END branch_cms_tpw_flow;
1552
1553
1554 /*---------------------------------------------------------------------
1555
1556 PROCEDURE : Check_cancellation_inprogress
1557 Parameters: p_delivery_id
1558 x_cancellation_in_progress
1559 x_return_status
1560
1561 Comments :
1562
1563 This procedure is used to determine if there is a cancellation in progress
1564 for CMS flow. A cancellation is in progress if the supplier has sent a
1565 cancel message, but no CBOD confirmation/rejection has arrived yet.
1566
1567 ---------------------------------------------------------------------*/
1568
1569 PROCEDURE Check_cancellation_inprogress
1570 (
1571 p_delivery_name IN VARCHAR2,
1572 x_cancellation_in_progress OUT NOCOPY BOOLEAN ,
1573 x_return_status OUT NOCOPY VARCHAR2
1574 )
1575 IS
1576
1577 l_debug_on BOOLEAN;
1578 --
1579 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' ||
1580 'CHECK_CANCELLATION_INPROGRESS';
1581 --
1582 l_status VARCHAR2(5);
1583 l_event_key VARCHAR2(250);
1584
1585 CURSOR c_del_status (v_delivery_name varchar2) IS
1586 SELECT status_code
1587 FROM wsh_new_deliveries
1588 WHERE name = v_delivery_name;
1589
1590 CURSOR c_get_event_key(v_delivery_name NUMBER) IS
1591 SELECT event_key
1592 FROM wsh_transactions_history
1593 WHERE ENTITY_NUMBER = v_delivery_name
1594 AND ENTITY_TYPE = 'DLVY'
1595 AND ACTION_TYPE = 'D'
1596 AND document_direction = 'O'
1597 ORDER BY transaction_id DESC;
1598 --bmso
1599 l_wf_status VARCHAR2(30);
1600 l_result VARCHAR2(30);
1601 e_success EXCEPTION;
1602 BEGIN
1603
1604 --
1605 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1606 --
1607 IF l_debug_on IS NULL
1608 THEN
1609 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1610 END IF;
1611 --
1612 IF l_debug_on THEN
1613 wsh_debug_sv.push(l_module_name);
1614 wsh_debug_sv.log(l_module_name, 'p_delivery_name',p_delivery_name);
1615 END IF;
1616
1617 x_cancellation_in_progress := FALSE;
1618
1619 OPEN c_del_status(p_delivery_name);
1620 FETCH c_del_status INTO l_status;
1621 IF c_del_status%NOTFOUND THEN
1622 IF l_debug_on THEN
1623 wsh_debug_sv.logmsg(l_module_name, 'ERROR Invalid delivery');
1624 END IF;
1625 FND_MESSAGE.Set_Name('WSH', 'WSH_DEL_NOT_FOUND');
1626 WSH_UTIL_CORE.add_message (wsh_util_core.g_ret_sts_error,l_module_name);
1627 CLOSE c_del_status;
1628 RAISE fnd_api.g_exc_error;
1629 END IF;
1630 IF l_debug_on THEN
1631 wsh_debug_sv.log(l_module_name, 'l_status',l_status);
1632 END IF;
1633
1634 IF l_status <> 'SC' THEN
1635 x_cancellation_in_progress := FALSE;
1636 RAISE e_success;
1637 END IF;
1638 CLOSE c_del_status;
1639
1640 -- IF cancellation rejection comes and the status of the delivery is
1641 -- SC then we need to see if the second workflow instance
1642 -- (cancellation workflow) is still active
1643
1644 OPEN c_get_event_key(p_delivery_name);
1645 FETCH c_get_event_key INTO l_event_key;
1646 IF c_get_event_key%NOTFOUND THEN
1647
1648 IF l_debug_on THEN
1649 wsh_debug_sv.logmsg(l_module_name, 'Cannot find the transaction history record');
1650 END IF;
1651
1652 x_cancellation_in_progress := FALSE;
1653 CLOSE c_get_event_key;
1654
1655 ELSE --{
1656
1657 CLOSE c_get_event_key;
1658
1659 IF l_debug_on THEN
1660 wsh_debug_sv.log(l_module_name, 'l_event_key',l_event_key);
1661 wsh_debug_sv.logmsg(l_module_name, 'calling program WF_ENGINE.ItemStatus',WSH_DEBUG_SV.C_PROC_LEVEL);
1662 END IF;
1663 WF_ENGINE.ItemStatus(
1664 itemtype => 'WSHSUPI',
1665 itemkey => l_event_key,
1666 status => l_wf_status,
1667 result => l_result
1668 );
1669
1670 -- values COMPLETE,SUSPENDED,ACTIVE,ERROR
1671
1672 IF l_wf_status IN ('COMPLETE','SUSPENDED') THEN
1673 x_cancellation_in_progress := FALSE;
1674 ELSE
1675 x_cancellation_in_progress := TRUE;
1676 END IF;
1677
1678
1679 END IF; --}
1680
1681
1682 x_return_status := wsh_util_core.g_ret_sts_success;
1683
1684 IF l_debug_on THEN
1685 wsh_debug_sv.log(l_module_name, 'x_cancellation_in_progress',
1686 x_cancellation_in_progress);
1687 wsh_debug_sv.pop(l_module_name);
1688 END IF;
1689
1690
1691 EXCEPTION
1692
1693 WHEN e_success THEN
1694 x_return_status := wsh_util_core.g_ret_sts_success;
1695 --
1696 IF l_debug_on THEN
1697 wsh_debug_sv.log(l_module_name, 'x_cancellation_in_progress',
1698 x_cancellation_in_progress);
1699 wsh_debug_sv.pop(l_module_name);
1700 END IF;
1701
1702 WHEN fnd_api.g_exc_error THEN
1703 x_return_status := fnd_api.g_ret_sts_error;
1704 --
1705 IF l_debug_on THEN
1706 wsh_debug_sv.logmsg(l_module_name, 'FND_API.G_EXC_ERROR exception has occured.', wsh_debug_sv.c_excep_level);
1707 wsh_debug_sv.pop(l_module_name, 'EXCEPTION:FND_API.G_EXC_ERROR');
1708 END IF;
1709 WHEN OTHERS THEN
1710 x_return_status := wsh_util_core.g_ret_sts_unexp_error;
1711 wsh_util_core.default_handler('WSH_TRANSACTIONS_UTIL.check_cancellation_inprogress',l_module_name);
1712 IF l_debug_on THEN
1713 WSH_DEBUG_SV.logmsg(l_module_name,
1714 'Unexpected error has occured. Oracle error message is '||
1715 substr(SQLERRM,1,200), WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1716 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1717 END IF;
1718
1719 END Check_cancellation_inprogress;
1720
1721
1722 /*---------------------------------------------------------------------
1723
1724 PROCEDURE : Check_cancellation_wf
1725 Parameters: item_type
1726 item_key
1727 actid
1728 funcmode
1729 resultout
1730
1731 Comments :
1732
1733 This procedure is called from workflow and will determine if there is a
1734 Cancellation in process for CMS system.
1735
1736 ---------------------------------------------------------------------*/
1737
1738 PROCEDURE Check_cancellation_wf (
1739 item_type IN VARCHAR2,
1740 item_key IN VARCHAR2,
1741 actid IN NUMBER,
1742 funcmode IN VARCHAR2,
1743 resultout OUT NOCOPY VARCHAR2
1744 )
1745 IS
1746
1747 l_debug_on BOOLEAN;
1748 --
1749 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' ||
1750 'CHECK_CANCELLATION_WF';
1751 --
1752 l_delivery_name varchar2(30);
1753 l_cancellation_in_progress BOOLEAN := FALSE;
1754 l_return_status VARCHAR2(1);
1755 l_num_warn NUMBER := 0;
1756 l_num_err NUMBER := 0;
1757 l_transaction_id NUMBER;
1758
1759 --
1760 CURSOR c_del_name_cur (v_item_key varchar2, v_item_type VARCHAR2) IS --bmso
1761 SELECT wth2.entity_number , wth1.transaction_id
1762 FROM wsh_transactions_history wth1,
1763 wsh_transactions_history wth2
1764 where wth1.entity_type='DLVY_INT'
1765 and wth1.event_key = v_item_key
1766 and wth1.item_type = v_item_type
1767 and wth1.document_type = 'SA'
1768 and wth1.document_direction='I'
1769 and wth1.action_type = 'A'
1770 AND wth2.entity_type = 'DLVY'
1771 AND wth2.document_type = 'SR'
1772 AND wth2.action_type = 'A'
1773 AND wth2.document_direction = 'O'
1774 AND wth2.item_type = v_item_type
1775 AND wth2.event_key = v_item_key
1776 ORDER BY wth1.transaction_id desc;
1777
1778
1779
1780 e_send_no EXCEPTION;
1781 e_send_yes EXCEPTION;
1782 BEGIN
1783
1784 --
1785 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1786 --
1787 IF l_debug_on IS NULL
1788 THEN
1789 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1790 END IF;
1791 --
1792 SAVEPOINT s_Check_cancellation_wf ;
1793
1794 IF l_debug_on THEN
1795 --bmso do we need to start the debugger
1796 wsh_debug_sv.push(l_module_name);
1797 wsh_debug_sv.log(l_module_name, 'item_type',item_type);
1798 wsh_debug_sv.log(l_module_name, 'item_key',item_key);
1799 wsh_debug_sv.log(l_module_name, 'actid',actid);
1800 wsh_debug_sv.log(l_module_name, 'funcmode',funcmode);
1801 END IF;
1802
1803 IF ( funcmode = 'RUN' ) THEN --{
1804 IF branch_cms_tpw_flow(p_event_key => item_key) THEN --{
1805 --get the delivery name
1806 OPEN c_del_name_cur(item_key, item_type);
1807 FETCH c_del_name_cur INTO l_delivery_name, l_transaction_id;
1808 IF c_del_name_cur%NOTFOUND THEN
1809 IF l_debug_on THEN
1810 wsh_debug_sv.logmsg(l_module_name, 'Error, invalid interface delivery');
1811 END IF;
1812 CLOSE c_del_name_cur;
1813 RAISE fnd_api.g_exc_error;
1814 END IF;
1815 CLOSE c_del_name_cur;
1816
1817 IF l_debug_on THEN
1818 wsh_debug_sv.log(l_module_name, 'l_delivery_name',l_delivery_name);
1819 wsh_debug_sv.log(l_module_name, 'l_transaction_id',l_transaction_id);
1820 END IF;
1821 -- see if there is any cancelation pending
1822 Check_cancellation_inprogress (
1823 p_delivery_name => l_delivery_name,
1824 x_cancellation_in_progress =>
1825 l_cancellation_in_progress,
1826 x_return_status => l_return_status
1827 );
1828
1829 wsh_util_core.api_post_call(
1830 p_return_status => l_return_status,
1831 x_num_warnings => l_num_warn,
1832 x_num_errors => l_num_err);
1833
1834 IF l_cancellation_in_progress THEN --{
1835
1836 UPDATE wsh_transactions_history
1837 SET TRANSACTION_STATUS = 'AP'
1838 WHERE transaction_id = l_transaction_id;
1839
1840 IF SQL%ROWCOUNT <> 1 THEN
1841
1842 IF l_debug_on THEN
1843 wsh_debug_sv.log(l_module_name, 'Error in updating the transaction hsitory record to status AP',SQL%ROWCOUNT);
1844 END IF;
1845 RAISE FND_API.g_exc_error;
1846 END IF;
1847 RAISE e_send_yes;
1848
1849 ELSE --}{
1850
1851 RAISE e_send_no;
1852
1853 END IF; --}
1854
1855 ELSE --}{
1856 RAISE e_send_no;
1857 END IF; --}
1858
1859 END IF; --}
1860
1861 IF l_debug_on THEN
1862 wsh_debug_sv.pop(l_module_name);
1863 END IF;
1864
1865
1866 EXCEPTION
1867
1868
1869 WHEN e_send_no THEN
1870 resultout := 'COMPLETE:N';
1871 IF l_debug_on THEN
1872 wsh_debug_sv.log(l_module_name, 'resultout', resultout);
1873 wsh_debug_sv.pop(l_module_name);
1874 END IF;
1875
1876 WHEN e_send_yes THEN
1877 resultout := 'COMPLETE:Y';
1878 IF l_debug_on THEN
1879 wsh_debug_sv.log(l_module_name, 'resultout', resultout);
1880 wsh_debug_sv.pop(l_module_name);
1881 END IF;
1882
1883 WHEN fnd_api.g_exc_unexpected_error THEN
1884 --bmso how do we rollback;
1885 --also COMPLETE:FAILURE does not exist for this
1886 rollback to s_Check_cancellation_wf;
1887 resultout := 'COMPLETE:FAILURE';
1888 --
1889 IF l_debug_on THEN
1890 wsh_debug_sv.logmsg(l_module_name, 'fnd_api.g_exc_unexpected_error exception has occured.', wsh_debug_sv.c_excep_level);
1891 wsh_debug_sv.pop(l_module_name, 'EXCEPTION:fnd_api.g_exc_unexpected_error');
1892 END IF;
1893 RAISE;
1894
1895 WHEN fnd_api.g_exc_error THEN
1896 rollback to s_Check_cancellation_wf;
1897 resultout := 'COMPLETE:FAILURE';
1898 --
1899 IF l_debug_on THEN
1900 wsh_debug_sv.logmsg(l_module_name, 'FND_API.G_EXC_ERROR exception has occured.', wsh_debug_sv.c_excep_level);
1901 wsh_debug_sv.pop(l_module_name, 'EXCEPTION:FND_API.G_EXC_ERROR');
1902 END IF;
1903
1904 RAISE;
1905 WHEN OTHERS THEN
1906 resultout := 'COMPLETE:FAILURE';
1907 wsh_util_core.default_handler('WSH_TRANSACTIONS_UTIL.check_cancellation_wf');
1908 IF l_debug_on THEN
1909 WSH_DEBUG_SV.logmsg(l_module_name,
1910 'Unexpected error has occured. Oracle error message is '||
1911 substr(SQLERRM,1,200), WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1912 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1913 END IF;
1914 RAISE;
1915
1916 END check_cancellation_wf;
1917
1918
1919
1920 /*---------------------------------------------------------------------
1921
1922 PROCEDURE : process_cbod_wf
1923 Parameters: item_type
1924 item_key
1925 actid
1926 funcmode
1927 resultout
1928
1929 Comments :
1930
1931 This procedure is called from workflow and will determine if there is a
1932 Cancellation in process for CMS system.
1933
1934 ---------------------------------------------------------------------*/
1935
1936 PROCEDURE process_cbod_wf (
1937 item_type IN VARCHAR2,
1938 item_key IN VARCHAR2,
1939 actid IN NUMBER,
1940 funcmode IN VARCHAR2,
1941 resultout OUT NOCOPY VARCHAR2
1942 )
1943 IS
1944
1945 l_debug_on BOOLEAN;
1946 --
1947 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' ||
1948 'PROCESS_CBOD_WF';
1949 --
1950 l_cbod_status VARCHAR2(5);
1951 l_sr_hist_record WSH_TRANSACTIONS_HISTORY_PKG.Txns_History_Record_Type;
1952 l_entity_number WSH_TRANSACTIONS_HISTORY.entity_number%TYPE;
1953 l_result_code VARCHAR2(50);
1954 l_transaction_status VARCHAR2(5);
1955 l_trx_id NUMBER;
1956 l_return_status VARCHAR2(2);
1957 l_sa_exist BOOLEAN := FALSE;
1958 l_param_list wf_parameter_list_t;
1959
1960 --
1961
1962 l_del_interface_id NUMBER;
1963
1964 CURSOR c_get_trx_id (v_name varchar2) IS --bmso
1965 SELECT wth2.transaction_id, wth2.transaction_status,
1966 to_number(wth2.entity_number)
1967 FROM wsh_transactions_history wth1,
1968 wsh_transactions_history wth2
1969 WHERE wth1.entity_number = v_name
1970 AND wth1.action_type = 'A'
1971 AND wth1.entity_type = 'DLVY'
1972 AND wth1.document_type = 'SR'
1973 AND wth1.document_direction = 'O'
1974 AND wth1.document_number = wth2.ORIG_DOCUMENT_NUMBER
1975 AND wth1.event_key = wth2.event_key
1976 AND wth2.document_direction = 'I'
1977 AND wth2.document_type = 'SA';
1978
1979
1980 CURSOR c_get_sr_record (v_entity_number varchar2) IS --bmso
1981 SELECT transaction_id,
1982 document_type,
1983 document_direction,
1984 document_number,
1985 orig_document_number,
1986 entity_number,
1987 entity_type,
1988 trading_partner_id,
1989 action_type,
1990 transaction_status,
1991 ecx_message_id,
1992 event_name,
1993 event_key ,
1994 item_type,
1995 internal_control_number,
1996 attribute_category,
1997 attribute1,
1998 attribute2,
1999 attribute3,
2000 attribute4,
2001 attribute5,
2002 attribute6,
2003 attribute7,
2004 attribute8,
2005 attribute9,
2006 attribute10,
2007 attribute11,
2008 attribute12,
2009 attribute13,
2010 attribute14,
2011 attribute15
2012 FROM wsh_transactions_history
2013 WHERE
2014 entity_number = v_entity_number
2015 AND action_type = 'A'
2016 AND entity_type = 'DLVY'
2017 and document_direction = 'O'
2018 and document_type = 'SR'
2019 ORDER BY transaction_id DESC;
2020
2021 CURSOR c_get_del_status (v_delivery_name VARCHAR2) IS
2022 SELECT status_code
2023 FROM wsh_new_deliveries
2024 WHERE name = v_delivery_name;
2025
2026 l_status wsh_new_deliveries.status_code%TYPE;
2027
2028 cursor c_get_cancel_rec (v_item_type VARCHAR2, v_item_key VARCHAR2)
2029 IS
2030 SELECT entity_number
2031 FROM wsh_transactions_history
2032 WHERE item_type = v_item_type
2033 AND event_key = v_item_key
2034 AND document_direction = 'O'
2035 AND document_type = 'SR'
2036 AND ACTION_TYPE = 'D'
2037 ORDER BY transaction_id desc;
2038
2039 cursor c_sr_instance (v_item_type VARCHAR2, v_item_key VARCHAR2)
2040 IS
2041 SELECT 1
2042 FROM wsh_transactions_history
2043 WHERE item_type = v_item_type
2044 AND event_key = v_item_key
2045 AND document_direction = 'O'
2046 AND document_type = 'SR'
2047 AND ACTION_TYPE = 'A'
2048 ORDER BY transaction_id desc;
2049
2050 l_dummy NUMBER;
2051
2052 e_send_true EXCEPTION;
2053 e_send_false EXCEPTION;
2054 BEGIN
2055
2056 --
2057 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2058 --
2059 IF l_debug_on IS NULL
2060 THEN
2061 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2062 END IF;
2063 --
2064 SAVEPOINT s_process_cbod ;
2065
2066 IF l_debug_on THEN
2067 wsh_debug_sv.push(l_module_name);
2068 wsh_debug_sv.log(l_module_name, 'item_type',item_type);
2069 wsh_debug_sv.log(l_module_name, 'item_key',item_key);
2070 wsh_debug_sv.log(l_module_name, 'actid',actid);
2071 wsh_debug_sv.log(l_module_name, 'funcmode',funcmode);
2072 END IF;
2073
2074 IF ( funcmode = 'RUN' ) THEN --{
2075 IF branch_cms_tpw_flow(p_event_key => item_key) THEN --{
2076
2077 -- get the history record for cancellation
2078
2079 OPEN c_get_cancel_rec(item_type, item_key);
2080 FETCH c_get_cancel_rec INTO l_entity_number;
2081
2082 IF c_get_cancel_rec%NOTFOUND THEN --{
2083
2084 --If the cancel record is not found then it could be the
2085 -- case the the first work flow istance is being closed
2086 -- by the cancellation workflow. This case happens when a
2087 -- CBOD confirmation arriaves at the cancellation workflow.
2088
2089 CLOSE c_get_cancel_rec;
2090
2091 OPEN c_sr_instance(item_type, item_key);
2092 FETCH c_sr_instance INTO l_dummy;
2093 IF c_sr_instance%NOTFOUND THEN --{
2094 IF l_debug_on THEN
2095 wsh_debug_sv.logmsg(l_module_name,
2096 'Error: Could not find record in transaction history');
2097 END IF;
2098 CLOSE c_sr_instance;
2099 RAISE e_send_false;
2100 END IF; --}
2101
2102 CLOSE c_sr_instance;
2103
2104 -- close the workflow
2105 RAISE e_send_true;
2106
2107 END IF; --}
2108 CLOSE c_get_cancel_rec;
2109
2110 -- If for some reason the shipment advice has been processed through
2111 -- the message correction form and the cancellation workflow was
2112 -- not closed then close the cancellation workflow as a CBOD reject.
2113
2114 OPEN c_get_del_status (l_entity_number);
2115 FETCH c_get_del_status INTO l_status;
2116 IF c_get_del_status%NOTFOUND THEN
2117 FND_MESSAGE.Set_Name('WSH', 'WSH_DEL_NOT_FOUND');
2118 WSH_UTIL_CORE.add_message (wsh_util_core.g_ret_sts_error,l_module_name);
2119 CLOSE c_get_del_status;
2120 RAISE e_send_false;
2121 END IF;
2122 CLOSE c_get_del_status;
2123
2124 IF l_status IN ('CO','IT','CL','SA') THEN --{
2125 IF l_debug_on THEN
2126 wsh_debug_sv.logmsg(l_module_name, 'Calling wf_engine.setItemAttribute');
2127 END IF;
2128 wf_engine.setItemAttrText
2129 (
2130 itemType => item_type,
2131 itemKey => item_key,
2132 aname => 'PARAMETER6',
2133 avalue => '99'
2134 );
2135 RAISE e_send_true;
2136
2137 END IF; --}
2138
2139 --get the Shipment Request record
2140 OPEN c_get_sr_record(l_entity_number);
2141 FETCH c_get_sr_record INTO l_sr_hist_record;
2142 IF c_get_sr_record%NOTFOUND THEN
2143 IF l_debug_on THEN
2144 wsh_debug_sv.log(l_module_name,
2145 'Error: Could not find Shipment Request record for ',
2146 l_entity_number);
2147 END IF;
2148 CLOSE c_get_sr_record;
2149 RAISE e_send_false;
2150 END IF;
2151 CLOSE c_get_sr_record;
2152
2153 --get the transaction_id for the Shipment Advice record
2154
2155 OPEN c_get_trx_id(l_entity_number);
2156 FETCH c_get_trx_id INTO l_trx_id , l_transaction_status,
2157 l_del_interface_id;
2158 IF c_get_trx_id%NOTFOUND THEN
2159 l_sa_exist := FALSE;
2160 IF l_debug_on THEN
2161 wsh_debug_sv.log(l_module_name,
2162 'No SA record exist for ',
2163 l_entity_number);
2164 END IF;
2165 ELSE
2166 l_sa_exist := TRUE;
2167 END IF;
2168 CLOSE c_get_trx_id;
2169
2170 IF l_debug_on THEN
2171 wsh_debug_sv.log(l_module_name,'l_sa_exist',l_sa_exist);
2172 wsh_debug_sv.log(l_module_name,'l_transaction_status',
2173 l_transaction_status);
2174 wsh_debug_sv.log(l_module_name,'l_trx_id',l_trx_id);
2175 wsh_debug_sv.log(l_module_name,'l_del_interface_id',
2176 l_del_interface_id);
2177 END IF;
2178
2179 l_cbod_status := wf_engine.GetItemAttrText(
2180 'WSHSUPI',
2181 Item_key,
2182 'PARAMETER6');
2183 IF l_debug_on THEN
2184 wsh_debug_sv.log(l_module_name,'l_cbod_status',l_cbod_status);
2185 END IF;
2186 IF l_cbod_status = '00' THEN --{ confirmation
2187
2188
2189 --IF Shipment Advice record exist then change the status to 'SX'
2190
2191 IF l_sa_exist THEN --{
2192
2193 -- Delete the interface record
2194
2195 WSH_PROCESS_INTERFACED_PKG.delete_interface_records (
2196 p_delivery_interface_id => l_del_interface_id,
2197 x_return_status => l_Return_Status
2198 ) ;
2199
2200 IF (l_Return_Status NOT IN (WSH_UTIL_CORE.g_ret_sts_success,
2201 WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
2202 RAISE e_send_false;
2203 END IF;
2204
2205 --Update the status_code for shipment advice record to 'SX'
2206
2207 UPDATE wsh_transactions_history
2208 SET transaction_status= 'SX',
2209 entity_number = l_entity_number,
2210 entity_type = 'DLVY'
2211 WHERE
2212 transaction_id = l_trx_id; --bmso
2213
2214 END IF; --}
2215
2216
2217 -- Raise the event for the first workflow instance to finish.
2218
2219 l_sr_hist_record.Event_Name := 'ORACLE.APPS.FTE.SSNO.CONFIRM';
2220
2221 WSH_EXTERNAL_INTERFACE_SV.Raise_Event ( l_sr_hist_record, '99',
2222 l_Return_Status );
2223
2224 IF l_debug_on THEN
2225 wsh_debug_sv.log (l_module_name, 'Return status after Raise_Event ', l_Return_Status);
2226 END IF;
2227
2228 IF (l_Return_Status <> WSH_UTIL_CORE.g_ret_sts_success ) THEN
2229 RAISE e_send_false;
2230 END IF;
2231
2232
2233 ELSE --}{ rejection
2234 IF l_sa_exist THEN --{
2235 IF l_transaction_status = 'ER' THEN --{
2236
2237 wf_engine.handleError(
2238 itemType => l_sr_hist_record.item_type,
2239 itemKey => l_sr_hist_record.event_key,
2240 activity => 'WSH_SUPPLIER_WF:WSH_PROCESS_DELIVERY',
2241 command => 'RETRY',
2242 result => NULL
2243 );
2244
2245 ELSIF l_transaction_status = 'AP' THEN --}{
2246
2247 wf_engine.completeActivity (
2248 itemtype => l_sr_hist_record.item_type,
2249 itemkey => l_sr_hist_record.event_key,
2250 activity => 'WSH_SUPPLIER_WF:CONTINUE_SHIPMENT_ADVICE',
2251 result => l_result_code);
2252
2253 END IF; --}
2254 END IF; --}
2255 END IF; --}
2256 END IF; --}
2257
2258 RAISE e_send_true;
2259
2260 END IF; --}
2261
2262 IF l_debug_on THEN
2263 wsh_debug_sv.pop(l_module_name);
2264 END IF;
2265
2266
2267 EXCEPTION
2268
2269
2270 WHEN e_send_true THEN
2271 resultout := 'COMPLETE:T';
2272 IF l_debug_on THEN
2273 wsh_debug_sv.log(l_module_name, 'resultout', resultout);
2274 wsh_debug_sv.pop(l_module_name);
2275 END IF;
2276
2277 WHEN e_send_false THEN
2278 resultout := 'COMPLETE:F';
2279 IF l_debug_on THEN
2280 wsh_debug_sv.log(l_module_name, 'resultout', resultout);
2281 wsh_debug_sv.pop(l_module_name);
2282 END IF;
2283
2284 WHEN OTHERS THEN
2285 resultout := 'COMPLETE:FAILURE';
2286 wsh_util_core.default_handler('WSH_TRANSACTIONS_UTIL.process_cbod_wf');
2287 IF l_debug_on THEN
2288 WSH_DEBUG_SV.logmsg(l_module_name,
2289 'Unexpected error has occured. Oracle error message is '||
2290 substr(SQLERRM,1,200), WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2291 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2292 END IF;
2293 RAISE;
2294 END process_cbod_wf;
2295
2296 END WSH_TRANSACTIONS_UTIL;