[Home] [Help]
PACKAGE BODY: APPS.WSH_TRANSACTIONS_UTIL
Source
1 PACKAGE BODY WSH_TRANSACTIONS_UTIL
2 -- $Header: WSHXUTLB.pls 120.9 2011/12/21 10:05:23 skanduku 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 l_event_code VARCHAR2(30);
680 l_event_key VARCHAR2(30);
681
682 l_temp NUMBER;
683 l_wh_type VARCHAR2(10);
684 l_delivery_id NUMBER;
685 wsh_invalid_event_name EXCEPTION;
686 wsh_invalid_delivery_name EXCEPTION;
687 wsh_invalid_batch_name EXCEPTION; --Fulfillment Batch XML Project
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 -- R12.1.1 STANDALONE PROJECT
714 -- LSP PROJECT : Consider LSP mode also.
715 IF ( WMS_DEPLOY.WMS_DEPLOYMENT_MODE IN ('D','L') AND (l_event_code = 'SSAO')) THEN
716 select wsh_transaction_s.nextval into l_temp from dual;
717 x_event_key := to_char(l_temp);
718 ELSE
719 open get_key;
720 Fetch get_key into x_event_key;
721 close get_key;
722 END IF;
723 ELSIF l_event_code = 'SSRO' THEN
724 IF p_delivery_name IS NOT NULL THEN
725 open del_cur;
726 fetch del_cur into l_delivery_id;
727 close del_cur;
728 select wsh_transaction_s.nextval into l_temp from dual;
729 l_wh_type := WSH_EXTERNAL_INTERFACE_SV.Get_Warehouse_Type( p_organization_id => p_organization_id,
730 x_return_status => x_return_status,
731 p_delivery_id => l_delivery_id
732 );
733 IF l_debug_on THEN
734 wsh_debug_sv.log (l_module_name,'Get_Warehouse_Type l_wh_type,x_return_status',l_wh_type||','||x_return_status );
735 END IF;
736 ELSE
737 raise wsh_invalid_delivery_name;
738 END IF;
739 x_event_key := l_wh_type || to_char(l_temp);
740 ELSIF l_event_code = 'BSRO' THEN --Fulfillment Batch XML Project
741 IF p_delivery_name IS NOT NULL THEN
742 select wsh_transaction_s.nextval into l_temp from dual;
743 l_wh_type := WSH_EXTERNAL_INTERFACE_SV.Get_Warehouse_Type( p_organization_id => p_organization_id,
744 x_return_status => x_return_status);
745 IF l_debug_on THEN
746 wsh_debug_sv.log (l_module_name,'Get_Warehouse_Type l_wh_type,x_return_status',l_wh_type||','||x_return_status );
747 END IF;
748 ELSE
749 raise wsh_invalid_batch_name;
750 END IF;
751 x_event_key := l_wh_type || to_char(l_temp);
752 ELSIF l_event_code IN ('SSRI','SCBOD') THEN
753 select wsh_transaction_s.nextval into l_temp from dual;
754 x_event_key := to_char(l_temp);
755 --Fulfillment Batch XML Project
756 --Event key will be generated for new business event added for shipment Advice Inbound Procesing for Shipment Batches.
757 ELSIF l_event_code in ('BSAI') THEN
758 select wsh_transaction_s.nextval into l_temp from dual;
759 l_wh_type := WSH_EXTERNAL_INTERFACE_SV.Get_Warehouse_Type(p_organization_id => p_organization_id,
760 x_return_status => x_return_status,
761 p_delivery_id => l_delivery_id);
762 IF l_debug_on THEN
763 wsh_debug_sv.log (l_module_name,'Get_Warehouse_Type l_wh_type,x_return_status',l_wh_type||','||x_return_status );
764 END IF;
765 x_event_key := l_wh_type || to_char(l_temp);
766 ELSE
767 raise wsh_invalid_event_name;
768 END IF;
769
770 IF l_debug_on THEN
771 WSH_DEBUG_SV.log(l_module_name, 'Event Key'|| x_event_key);
772 wsh_debug_sv.pop(l_module_name);
773 END IF;
774 EXCEPTION
775
776 WHEN wsh_invalid_event_name THEN
777 x_return_status := wsh_util_core.g_ret_sts_error;
778 IF l_debug_on THEN
779 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_invalid_event_name exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
780 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_invalid_event_name');
781 END IF;
782 WHEN wsh_invalid_delivery_name THEN
783 x_return_status := wsh_util_core.g_ret_sts_error;
784 IF l_debug_on THEN
785 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_invalid_delivery_name exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
786 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_invalid_delivery_name');
787 END IF;
788 WHEN wsh_invalid_batch_name THEN--Fulfillment Batch XML Project
789 x_return_status := wsh_util_core.g_ret_sts_error;
790 IF l_debug_on THEN
791 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_invalid_batch_name exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
792 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_invalid_batch_name ');
793 END IF;
794 WHEN others THEN
795 x_return_status := wsh_util_core.g_ret_sts_unexp_error;
796 IF l_debug_on THEN
797 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
798 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
799 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
800 END IF;
801 END Get_Event_Key;
802
803
804 PROCEDURE Unlock_Delivery_WF( item_type IN VARCHAR2,
805 item_key IN VARCHAR2,
806 actid IN NUMBER,
807 funcmode IN VARCHAR2,
808 resultout OUT NOCOPY VARCHAR2
809 )
810 IS
811
812 CURSOR c_delId_cur IS
813 SELECT wnd.delivery_id
814 from wsh_new_deliveries wnd,
815 wsh_transactions_history wth
816 where wnd.name = wth.entity_number
817 and entity_type='DLVY'
818 and wth.event_key = item_key
819 and wth.item_type = item_type
820 and wth.document_direction='O';
821
822 l_return_status VARCHAR2(1);
823 l_delivery_id NUMBER;
824 wsh_unlock_error EXCEPTION;
825 wsh_del_not_found EXCEPTION;
826 --
827 l_debug_on BOOLEAN;
828 --
829 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UNLOCK_DELIVERY_WF';
830 --
831 BEGIN
832 --
833 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
834 --
835 IF l_debug_on IS NULL
836 THEN
837 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
838 END IF;
839 --
840 IF l_debug_on THEN
841 wsh_debug_sv.push(l_module_name, 'Unlock_Delivery_WF');
842 wsh_debug_sv.log(l_module_name, 'item_type',item_type);
843 wsh_debug_sv.log(l_module_name, 'item_key',item_key);
844 wsh_debug_sv.log(l_module_name, 'actid',actid);
845 wsh_debug_sv.log(l_module_name, 'funcmode',funcmode);
846 END IF;
847
848
849 IF ( funcmode = 'RUN' ) THEN
850
851 open c_delId_cur;
852 Fetch c_delId_cur into l_delivery_id;
853 IF ( c_delId_cur%NOTFOUND ) THEN
854 CLOSE c_delId_cur;
855 resultout := 'COMPLETE:FAILURE';
856 raise wsh_del_not_found;
857 END IF;
858
859 WSH_DELIVERY_UTIL.Update_Dlvy_Status(l_delivery_id,
860 NULL,
861 NULL,
862 l_return_status);
863 IF l_debug_on THEN
864 wsh_debug_sv.log(l_module_name, 'l_return_status',l_return_status);
865 END IF;
866
867 IF ( l_return_status not in (WSH_UTIL_CORE.G_RET_STS_SUCCESS,WSH_UTIL_CORE.G_RET_STS_WARNING) ) THEN
868 raise wsh_unlock_error;
869 ELSE
870 resultout := 'COMPLETE:SUCCESS';
871 IF l_debug_on THEN
872 wsh_debug_sv.log(l_module_name, 'resultout',resultout);
873 wsh_debug_sv.pop (l_module_name,'RETURN');
874 END IF;
875 RETURN;
876 END IF;
877
878 END IF;
879
880 IF l_debug_on THEN
881 wsh_debug_sv.log(l_module_name, 'resultout',resultout);
882 wsh_debug_sv.pop (l_module_name);
883 END IF;
884 EXCEPTION
885 WHEN wsh_del_not_found THEN
886 resultout := 'COMPLETE:FAILURE';
887 IF l_debug_on THEN
888 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_del_not_found exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
889 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_del_not_found');
890 END IF;
891 raise;
892 WHEN wsh_unlock_error THEN
893 resultout := 'COMPLETE:FAILURE';
894 IF l_debug_on THEN
895 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_unlock_error exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
896 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_unlock_error');
897 END IF;
898 raise;
899 WHEN OTHERS THEN
900 resultout := 'COMPLETE:FAILURE';
901 IF l_debug_on THEN
902 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
903 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
904 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
905 END IF;
906 raise;
907 END Unlock_Delivery_WF;
908
909 PROCEDURE update_atnms( p_transaction_id IN number)
910
911 IS
912
913 pragma AUTONOMOUS_TRANSACTION;
914 l_debug_on BOOLEAN;
915
916 --
917 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.'
918 || 'UPDATE_ATNMS';
919
920 BEGIN
921 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
922 --
923 IF l_debug_on IS NULL
924 THEN
925 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
926 END IF;
927 --
928 IF l_debug_on THEN
929 wsh_debug_sv.push(l_module_name);
930 wsh_debug_sv.log(l_module_name, 'p_transaction_id',p_transaction_id);
931 END IF;
932
933 UPDATE wsh_transactions_history
934 SET transaction_status = 'ER'
935 WHERE transaction_id = p_transaction_id;
936
937 IF l_debug_on THEN
938 wsh_debug_sv.log(l_module_name, 'ROWCOUNT', SQL%ROWCOUNT);
939 END IF;
940
941 COMMIT;
942
943 IF l_debug_on THEN
944 wsh_debug_sv.pop (l_module_name);
945 END IF;
946
947 EXCEPTION
948 WHEN OTHERS THEN
949 IF l_debug_on THEN
950 WSH_DEBUG_SV.logmsg(l_module_name,
951 'Unexpected error has occured. Oracle error message is '
952 || SQLERRM, WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
953 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
954 END IF;
955 raise;
956 END update_atnms;
957
958
959 PROCEDURE Process_Inbound_Delivery_WF( Item_type IN VARCHAR2,
960 Item_key IN VARCHAR2,
961 Actid IN NUMBER,
962 Funcmode IN VARCHAR2,
963 Resultout OUT NOCOPY VARCHAR2
964 )
965
966 IS
967
968
969 l_txns_history_rec WSH_TRANSACTIONS_HISTORY_PKG.Txns_History_Record_Type;
970 l_return_status VARCHAR2(1);
971 l_document_type VARCHAR2(30);
972 --k proj
973 l_cancellation_in_progress BOOLEAN := FALSE;
974 l_num_warn number := 0;
975 l_num_err number := 0;
976 l_sr_trx_id NUMBER;
977 l_enity_number wsh_transactions_history.entity_number%TYPE;
978
979 CURSOR c_get_entity_number (v_trx_id NUMBER) IS --bmso
980 SELECT wth1.entity_number , wth1.transaction_id
981 FROM wsh_transactions_history wth1,
982 wsh_transactions_history wth2
983 WHERE wth1.action_type = 'A'
984 AND wth1.entity_type = 'DLVY'
985 AND wth1.document_type = 'SR'
986 AND wth1.document_direction = 'O'
987 AND wth1.document_number = wth2.ORIG_DOCUMENT_NUMBER
988 AND wth1.event_key = wth2.event_key
989 AND wth2.document_direction = 'I'
990 AND wth2.transaction_id = v_trx_id
991 AND wth2.document_type = 'SA'
992 ORDER BY wth1.transaction_id desc;
993
994 CURSOR c_get_cancel_record (v_sr_trx_id number) IS
995 SELECT wth2.transaction_id ,
996 wth2.document_type ,
997 wth2.document_direction ,
998 wth2.document_number ,
999 wth2.orig_document_number ,
1000 wth2.entity_number ,
1001 wth2.entity_type ,
1002 wth2.trading_partner_id ,
1003 wth2.action_type ,
1004 wth2.transaction_status ,
1005 wth2.ecx_message_id ,
1006 wth2.event_name ,
1007 wth2.event_key ,
1008 wth2.item_type ,
1009 wth2.internal_control_number ,
1010 -- R12.1.1 STANDALONE PROJECT
1011 wth2.document_revision,
1012 wth2.attribute_category ,
1013 wth2.attribute1 ,
1014 wth2.attribute2 ,
1015 wth2.attribute3 ,
1016 wth2.attribute4 ,
1017 wth2.attribute5 ,
1018 wth2.attribute6 ,
1019 wth2.attribute7 ,
1020 wth2.attribute8 ,
1021 wth2.attribute9 ,
1022 wth2.attribute10 ,
1023 wth2.attribute11 ,
1024 wth2.attribute12 ,
1025 wth2.attribute13 ,
1026 wth2.attribute14 ,
1027 wth2.attribute15,
1028 NULL -- LSP PROJECT : just added for dependency for client_id
1029 FROM wsh_transactions_history wth1,
1030 wsh_transactions_history wth2
1031 WHERE wth1.transaction_id = v_sr_trx_id
1032 AND wth2.entity_number = wth1.entity_number
1033 AND wth2.document_direction = 'O'
1034 AND wth2.document_type = 'SR'
1035 AND wth2.action_type = 'D'
1036 ORDER BY wth2.transaction_id desc;
1037
1038 l_cancel_history_rec WSH_TRANSACTIONS_HISTORY_PKG.Txns_History_Record_Type;
1039
1040 wsh_process_inbound EXCEPTION;
1041
1042 --Fulfillment Batch XML Project
1043 wsh_batch_process_inbound EXCEPTION;
1044 --
1045 l_debug_on BOOLEAN;
1046 --
1047 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PROCESS_INBOUND_DELIVERY_WF';
1048 --
1049 BEGIN
1050 --
1051 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1052 --
1053 IF l_debug_on IS NULL
1054 THEN
1055 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1056 END IF;
1057 --
1058 IF l_debug_on THEN
1059 wsh_debug_sv.push(l_module_name, 'Process_Inbound_Delivery_WF');
1060 wsh_debug_sv.log(l_module_name, 'item_type',item_type);
1061 wsh_debug_sv.log(l_module_name, 'item_key',item_key);
1062 wsh_debug_sv.log(l_module_name, 'actid',actid);
1063 wsh_debug_sv.log(l_module_name, 'funcmode',funcmode);
1064 END IF;
1065
1066
1067 IF ( funcmode = 'RUN' ) THEN
1068
1069 IF ( item_type in ('WSHSUPI','WSHBATI')) THEN
1070 l_document_type := 'SA';
1071 ELSE
1072 l_document_type := 'SR';
1073 END IF;
1074 WSH_TRANSACTIONS_HISTORY_PKG.Get_Txns_History(
1075 Item_type,
1076 Item_key,
1077 'I',
1078 l_document_type,
1079 l_txns_history_rec,
1080 l_return_status
1081 );
1082 IF l_debug_on THEN
1083 wsh_debug_sv.log(l_module_name, 'Get_Txns_History l_return_status ',l_return_status);
1084 END IF;
1085 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
1086 raise wsh_process_inbound;
1087 END IF;
1088 --k proj
1089 --Modified if condition for Fulfillment Batch XML Project
1090 IF item_type <> 'WSHBATI' AND l_document_type = 'SA' AND branch_cms_tpw_flow(p_event_key => item_key)
1091 THEN --{
1092
1093 OPEN c_get_entity_number(l_txns_history_rec.transaction_id);
1094 FETCH c_get_entity_number INTO l_enity_number, l_sr_trx_id;
1095 CLOSE c_get_entity_number;
1096
1097 IF l_debug_on THEN
1098 wsh_debug_sv.log(l_module_name, 'l_enity_number ',l_enity_number);
1099 wsh_debug_sv.log(l_module_name, 'l_sr_trx_id ',l_sr_trx_id);
1100 END IF;
1101
1102 IF l_txns_history_rec.transaction_status IN ('IP','ER') THEN --{
1103
1104
1105 Check_cancellation_inprogress (
1106 p_delivery_name => l_enity_number,
1107 --=> l_txns_history_rec.entity_number,
1108 x_cancellation_in_progress =>
1109 l_cancellation_in_progress,
1110 x_return_status => l_return_status
1111 );
1112 IF l_debug_on THEN
1113 wsh_debug_sv.log(l_module_name, 'l_return_status ',
1114 l_return_status);
1115 wsh_debug_sv.log(l_module_name, 'l_cancellation_in_progress',
1116 l_cancellation_in_progress);
1117 END IF;
1118
1119 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1120 RAISE fnd_api.g_exc_error;
1121 END IF;
1122
1123 IF l_cancellation_in_progress THEN
1124
1125 update_atnms(l_txns_history_rec.transaction_id);
1126 RAISE fnd_api.g_exc_error;
1127
1128 END IF;
1129
1130 END IF; --}
1131 END IF; --}
1132
1133 WSH_PROCESS_INTERFACED_PKG.Process_Inbound(l_txns_history_rec,
1134 l_return_status);
1135 IF l_debug_on THEN
1136 wsh_debug_sv.log(l_module_name, 'Process_Inbound l_return_status ',l_return_status);
1137 END IF;
1138
1139 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
1140 --Fulfillment Batch XML Project
1141 IF l_txns_history_rec.event_name = 'oracle.apps.wsh.batch.bsai' AND l_txns_history_rec.item_type='WSHBATI' THEN
1142 RAISE wsh_batch_process_inbound;
1143 ELSE
1144 raise wsh_process_inbound;
1145 END IF;
1146 ELSE
1147 -- send a CBOD to close the cancellation workflow instance bmso k proj
1148
1149 IF branch_cms_tpw_flow(p_event_key => item_key)
1150 AND l_document_type = 'SA'
1151 THEN --{
1152 OPEN c_get_cancel_record(l_sr_trx_id);
1153 FETCH c_get_cancel_record INTO l_cancel_history_rec;
1154
1155 IF c_get_cancel_record%FOUND THEN --{
1156 l_cancel_history_rec.Event_Name := 'ORACLE.APPS.FTE.SSNO.CONFIRM';
1157
1158 WSH_EXTERNAL_INTERFACE_SV.Raise_Event (
1159 l_cancel_history_rec,
1160 '99',
1161 l_Return_Status );
1162 wsh_util_core.api_post_call(
1163 p_return_status => l_return_status,
1164 x_num_warnings => l_num_warn,
1165 x_num_errors => l_num_err);
1166
1167 END IF; --}
1168 CLOSE c_get_cancel_record;
1169 END IF; --}
1170
1171 resultout := 'COMPLETE:SUCCESS';
1172 IF l_debug_on THEN
1173 wsh_debug_sv.log(l_module_name, 'resultout',resultout);
1174 wsh_debug_sv.pop (l_module_name,'RETURN');
1175 END IF;
1176 RETURN;
1177 END IF;
1178
1179 END IF;
1180
1181 IF l_debug_on THEN
1182 wsh_debug_sv.log(l_module_name, 'resultout',resultout);
1183 wsh_debug_sv.pop (l_module_name);
1184 END IF;
1185 EXCEPTION
1186 --Fulfillment Batch XML Project
1187 WHEN wsh_batch_process_inbound THEN
1188 resultout := 'COMPLETE:FAILURE';
1189 IF l_debug_on THEN
1190 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_batch_process_inbound exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1191 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_batch_process_inbound');
1192 END IF;
1193 WHEN wsh_process_inbound THEN
1194 resultout := 'COMPLETE:FAILURE';
1195 IF l_debug_on THEN
1196 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_process_inbound exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1197 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_process_inbound');
1198 END IF;
1199 raise;
1200 WHEN fnd_api.g_exc_error THEN
1201 resultout := 'COMPLETE:FAILURE';
1202 IF l_debug_on THEN
1203 WSH_DEBUG_SV.logmsg(l_module_name,'fnd_api.g_exc_error exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1204 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:fnd_api.g_exc_error');
1205 END IF;
1206 raise;
1207 WHEN OTHERS THEN
1208 resultout := 'COMPLETE:FAILURE';
1209 IF l_debug_on THEN
1210 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
1211 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1212 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1213 END IF;
1214 raise;
1215 END Process_Inbound_Delivery_WF;
1216
1217 -- ------------------------------------------------------------------
1218 -- Procedure: Process_Inbound_SR_WF
1219 --
1220 -- Parameters: Item_Type IN VARCHAR2
1221 -- Item_Key IN VARCHAR2
1222 -- Actid IN NUMBER
1223 -- Funcmode IN VARCHAR2
1224 -- Resultout OUT VARCHAR2
1225 --
1226 -- Description: This procedure is called from Inbound workflow (WSHSTNDI) to process
1227 -- the Inbound Shipment Request information sent by Host ERP system
1228 -- Created: Standalone WMS Project
1229 -- -----------------------------------------------------------------------
1230 PROCEDURE Process_Inbound_SR_WF( Item_type IN VARCHAR2,
1231 Item_key IN VARCHAR2,
1232 Actid IN NUMBER,
1233 Funcmode IN VARCHAR2,
1234 Resultout OUT NOCOPY VARCHAR2
1235 )
1236
1237 IS
1238
1239
1240 l_txns_history_rec WSH_TRANSACTIONS_HISTORY_PKG.Txns_History_Record_Type;
1241 l_return_status VARCHAR2(1);
1242 l_document_type VARCHAR2(30);
1243
1244 wsh_process_inbound EXCEPTION;
1245 --
1246 l_debug_on BOOLEAN;
1247 --
1248 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Process_Inbound_SR_WF';
1249 --
1250 BEGIN
1251 --
1252 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1253 --
1254 IF l_debug_on IS NULL
1255 THEN
1256 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1257 END IF;
1258 --
1259 IF l_debug_on THEN
1260 wsh_debug_sv.push(l_module_name, 'Process_Inbound_SR_WF');
1261 wsh_debug_sv.log(l_module_name, 'item_type',item_type);
1262 wsh_debug_sv.log(l_module_name, 'item_key',item_key);
1263 wsh_debug_sv.log(l_module_name, 'actid',actid);
1264 wsh_debug_sv.log(l_module_name, 'funcmode',funcmode);
1265 END IF;
1266
1267
1268 IF ( funcmode = 'RUN' ) THEN
1269
1270 l_document_type := 'SR';
1271 WSH_TRANSACTIONS_HISTORY_PKG.Get_Txns_History(
1272 Item_type,
1273 Item_key,
1274 'I',
1275 l_document_type,
1276 l_txns_history_rec,
1277 l_return_status
1278 );
1279 IF l_debug_on THEN
1280 wsh_debug_sv.log(l_module_name, 'Get_Txns_History l_return_status ',l_return_status);
1281 END IF;
1282 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
1283 raise wsh_process_inbound;
1284 END IF;
1285
1286 WSH_PROCESS_INTERFACED_PKG.Process_Inbound(l_txns_history_rec,
1287 l_return_status);
1288 IF l_debug_on THEN
1289 wsh_debug_sv.log(l_module_name, 'Process_Inbound l_return_status ',l_return_status);
1290 END IF;
1291
1292 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
1293 raise wsh_process_inbound;
1294 ELSE
1295 resultout := 'COMPLETE:SUCCESS';
1296 IF l_debug_on THEN
1297 wsh_debug_sv.log(l_module_name, 'resultout',resultout);
1298 wsh_debug_sv.pop (l_module_name,'RETURN');
1299 END IF;
1300 RETURN;
1301 END IF;
1302
1303 END IF;
1304
1305 IF l_debug_on THEN
1306 wsh_debug_sv.log(l_module_name, 'resultout',resultout);
1307 wsh_debug_sv.pop (l_module_name);
1308 END IF;
1309 EXCEPTION
1310 WHEN wsh_process_inbound THEN
1311 resultout := 'COMPLETE:ERROR';
1312 IF l_debug_on THEN
1313 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_process_inbound exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1314 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_process_inbound');
1315 END IF;
1316 -- raise;
1317 WHEN fnd_api.g_exc_error THEN
1318 resultout := 'COMPLETE:ERROR';
1319 IF l_debug_on THEN
1320 WSH_DEBUG_SV.logmsg(l_module_name,'fnd_api.g_exc_error exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1321 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:fnd_api.g_exc_error');
1322 END IF;
1323 -- raise;
1324 WHEN OTHERS THEN
1325 resultout := 'COMPLETE:ERROR';
1326 IF l_debug_on THEN
1327 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
1328 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1329 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1330 END IF;
1331 -- raise;
1332 END Process_Inbound_SR_WF;
1333
1334 PROCEDURE Update_Txn_Hist_Err_WF( Item_type IN VARCHAR2,
1335 Item_key IN VARCHAR2,
1336 Actid IN NUMBER,
1337 Funcmode IN VARCHAR2,
1338 Resultout OUT NOCOPY VARCHAR2
1339 )
1340
1341 IS
1342 l_return_status VARCHAR2(1);
1343 wsh_update_history EXCEPTION;
1344 --
1345 l_debug_on BOOLEAN;
1346 --
1347 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_TXN_HIST_ERR_WF';
1348 --
1349 BEGIN
1350 --
1351 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1352 --
1353 IF l_debug_on IS NULL
1354 THEN
1355 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1356 END IF;
1357 --
1358 IF l_debug_on THEN
1359 wsh_debug_sv.push(l_module_name, 'Update_Txn_Hist_Err_WF');
1360 wsh_debug_sv.log(l_module_name, 'item_type',item_type);
1361 wsh_debug_sv.log(l_module_name, 'item_key',item_key);
1362 wsh_debug_sv.log(l_module_name, 'actid',actid);
1363 wsh_debug_sv.log(l_module_name, 'funcmode',funcmode);
1364 END IF;
1365
1366
1367 IF ( funcmode = 'RUN' ) THEN
1368 Update_Txn_History ( Item_type,
1369 Item_key,
1370 'ER',
1371 l_return_status
1372 );
1373 IF l_debug_on THEN
1374 wsh_debug_sv.log(l_module_name, 'Update_Txn_History l_return_status ',l_return_status);
1375 END IF;
1376
1377 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
1378 raise wsh_update_history;
1379 ELSE
1380 resultout := 'COMPLETE:SUCCESS';
1381 IF l_debug_on THEN
1382 wsh_debug_sv.log(l_module_name, 'resultout',resultout);
1383 wsh_debug_sv.pop(l_module_name, 'RETURN');
1384 END IF;
1385 RETURN;
1386 END IF;
1387
1388 END IF;
1389
1390 IF l_debug_on THEN
1391 wsh_debug_sv.pop (l_module_name);
1392 END IF;
1393 EXCEPTION
1394 WHEN wsh_update_history THEN
1395 resultout := 'COMPLETE:FAILURE';
1396 IF l_debug_on THEN
1397 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_update_history exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1398 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_update_history');
1399 END IF;
1400 raise;
1401 WHEN OTHERS THEN
1402 resultout := 'COMPLETE:FAILURE';
1403 IF l_debug_on THEN
1404 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
1405 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1406 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1407 END IF;
1408 raise;
1409 END Update_Txn_Hist_Err_WF;
1410
1411 PROCEDURE Update_Txn_Hist_Success_WF( Item_type IN VARCHAR2,
1412 Item_key IN VARCHAR2,
1413 Actid IN NUMBER,
1414 Funcmode IN VARCHAR2,
1415 Resultout OUT NOCOPY VARCHAR2
1416 )
1417
1418 IS
1419 l_return_status VARCHAR2(1);
1420 wsh_update_history EXCEPTION;
1421 --
1422 l_debug_on BOOLEAN;
1423 --
1424 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_TXN_HIST_SUCCESS_WF';
1425 --
1426 BEGIN
1427 --
1428 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1429 --
1430 IF l_debug_on IS NULL
1431 THEN
1432 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1433 END IF;
1434 --
1435 IF l_debug_on THEN
1436 wsh_debug_sv.push(l_module_name, 'Update_Txn_Hist_Err_WF');
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, 'actid',actid);
1440 wsh_debug_sv.log(l_module_name, 'funcmode',funcmode);
1441 END IF;
1442
1443 IF ( funcmode = 'RUN' ) THEN
1444 Update_Txn_History ( Item_type,
1445 Item_key,
1446 'ST',
1447 l_return_status
1448 );
1449 IF l_debug_on THEN
1450 wsh_debug_sv.log(l_module_name, 'Update_Txn_History l_return_status ',l_return_status);
1451 END IF;
1452 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
1453 raise wsh_update_history;
1454 ELSE
1455 resultout := 'COMPLETE:SUCCESS';
1456 IF l_debug_on THEN
1457 wsh_debug_sv.log(l_module_name, 'resultout',resultout);
1458 wsh_debug_sv.pop(l_module_name, 'RETURN');
1459 END IF;
1460 RETURN;
1461 END IF;
1462 END IF;
1463 IF l_debug_on THEN
1464 wsh_debug_sv.pop (l_module_name);
1465 END IF;
1466 EXCEPTION
1467 WHEN wsh_update_history THEN
1468 resultout := 'COMPLETE:FAILURE';
1469 IF l_debug_on THEN
1470 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_update_history exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1471 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_update_history');
1472 END IF;
1473 raise;
1474 WHEN OTHERS THEN
1475 resultout := 'COMPLETE:FAILURE';
1476 IF l_debug_on THEN
1477 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
1478 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1479 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1480 END IF;
1481 raise;
1482 END Update_Txn_Hist_Success_WF;
1483
1484 ---------------------------------------------------------------------
1485 -- Procedure: Update_Txn_Hist_Closed_WF
1486 --
1487 -- Parameters: Item_Type IN VARCHAR2
1488 -- Item_Key IN VARCHAR2
1489 -- Actid IN NUMBER
1490 -- Funcmode IN VARCHAR2
1491 -- Resultout OUT VARCHAR2
1492 --
1493 -- Description: This procedure is called from Inbound Workflow (WSHSTNDI) to Close
1494 -- all the previous error out Shipment Request revision of the workflow
1495 -- Created: Standalone WMS Project
1496 -- -----------------------------------------------------------------------
1497 PROCEDURE Update_Txn_Hist_Closed_WF( Item_type IN VARCHAR2,
1498 Item_key IN VARCHAR2,
1499 Actid IN NUMBER,
1500 Funcmode IN VARCHAR2,
1501 Resultout OUT NOCOPY VARCHAR2
1502 )
1503
1504 IS
1505 l_return_status VARCHAR2(1);
1506 wsh_update_history EXCEPTION;
1507 --
1508 l_debug_on BOOLEAN;
1509 --
1510 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_TXN_HIST_CLOSED_WF';
1511 --
1512 BEGIN
1513 --
1514 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1515 --
1516 IF l_debug_on IS NULL
1517 THEN
1518 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1519 END IF;
1520 --
1521 IF l_debug_on THEN
1522 wsh_debug_sv.push(l_module_name, 'Update_Txn_Hist_Closed_WF');
1523 wsh_debug_sv.log(l_module_name, 'item_type',item_type);
1524 wsh_debug_sv.log(l_module_name, 'item_key',item_key);
1525 wsh_debug_sv.log(l_module_name, 'actid',actid);
1526 wsh_debug_sv.log(l_module_name, 'funcmode',funcmode);
1527 END IF;
1528
1529 IF ( funcmode = 'RUN' ) THEN
1530 Update_Txn_History ( Item_type,
1531 Item_key,
1532 'SC',
1533 l_return_status
1534 );
1535 IF l_debug_on THEN
1536 wsh_debug_sv.log(l_module_name, 'Update_Txn_History l_return_status ',l_return_status);
1537 END IF;
1538 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
1539 raise wsh_update_history;
1540 ELSE
1541 resultout := 'COMPLETE:SUCCESS';
1542 IF l_debug_on THEN
1543 wsh_debug_sv.log(l_module_name, 'resultout',resultout);
1544 wsh_debug_sv.pop(l_module_name, 'RETURN');
1545 END IF;
1546 RETURN;
1547 END IF;
1548 END IF;
1549 IF l_debug_on THEN
1550 wsh_debug_sv.pop (l_module_name);
1551 END IF;
1552 EXCEPTION
1553 WHEN wsh_update_history THEN
1554 resultout := 'COMPLETE:FAILURE';
1555 IF l_debug_on THEN
1556 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_update_history exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1557 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_update_history');
1558 END IF;
1559 raise;
1560 WHEN OTHERS THEN
1561 resultout := 'COMPLETE:FAILURE';
1562 IF l_debug_on THEN
1563 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
1564 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1565 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1566 END IF;
1567 raise;
1568 END Update_Txn_Hist_Closed_WF;
1569
1570 PROCEDURE Update_Txn_History ( p_item_type IN VARCHAR2,
1571 p_item_key IN VARCHAR2,
1572 p_transaction_status IN VARCHAR2,
1573 x_return_status OUT NOCOPY VARCHAR2
1574 )
1575 IS
1576
1577
1578 pragma AUTONOMOUS_TRANSACTION;
1579
1580 l_txns_history_rec WSH_TRANSACTIONS_HISTORY_PKG.Txns_History_Record_Type;
1581 l_document_type VARCHAR2(2);
1582 l_txn_direction VARCHAR2(1);
1583 l_txn_id NUMBER;
1584
1585 wsh_update_history EXCEPTION;
1586
1587 --
1588 l_debug_on BOOLEAN;
1589 --
1590 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_TXN_HISTORY';
1591 --
1592 BEGIN
1593 --
1594 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1595 --
1596 IF l_debug_on IS NULL
1597 THEN
1598 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1599 END IF;
1600 --
1601 IF l_debug_on THEN
1602 wsh_debug_sv.push(l_module_name);
1603 wsh_debug_sv.log(l_module_name, 'p_item_type',p_item_type);
1604 wsh_debug_sv.log(l_module_name, 'p_item_key',p_item_key);
1605 wsh_debug_sv.log(l_module_name, 'p_transaction_status',p_transaction_status);
1606 END IF;
1607
1608 IF ( p_item_type in ('WSHSUPI','WSHBATO')) THEN --Fulfillment Batch XML Project (Added WSHBATO)
1609 l_document_type := 'SR';
1610 l_txn_direction := 'O';
1611 -- R12.1.1 STANDALONE PROJECT
1612 ELSIF (( p_item_type = 'WSHTPWI' AND p_transaction_status = 'ER' ) OR (p_item_type = 'WSHSTNDI')) THEN
1613 l_txn_direction := 'I';
1614 l_document_type := 'SR';
1615 --Fulfillment Batch XML Project
1616 ELSIF (p_item_type='WSHBATI' ) THEN
1617 l_txn_direction := 'I';
1618 l_document_type := 'SA';
1619 ELSE
1620 l_document_type := 'SA';
1621 l_txn_direction := 'O';
1622 END IF;
1623 WSH_TRANSACTIONS_HISTORY_PKG.Get_Txns_History( p_item_type,
1624 p_item_key,
1625 l_txn_direction,
1626 l_document_type,
1627 l_txns_history_rec,
1628 x_return_status );
1629 IF l_debug_on THEN
1630 wsh_debug_sv.log(l_module_name, 'Get_Txns_History x_return_status',x_return_status);
1631 END IF;
1632
1633 l_txns_history_rec.transaction_status := p_transaction_status;
1634 IF ( x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
1635 raise wsh_update_history;
1636 END IF;
1637
1638 WSH_TRANSACTIONS_HISTORY_PKG.Create_Update_Txns_History ( l_txns_history_rec,
1639 l_txn_id,
1640 x_return_status );
1641 IF l_debug_on THEN
1642 wsh_debug_sv.log(l_module_name, 'Create_Update_Txns_History x_return_status',x_return_status);
1643 END IF;
1644
1645 IF ( x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
1646 raise wsh_update_history;
1647 ELSE
1648 IF p_item_type = 'WSHBATO' THEN --Fulfillment Batch XML Project
1649 UPDATE wsh_shipment_batches
1650 SET pending_request_flag = NULL
1651 WHERE batch_id = (SELECT entity_number FROM WSH_TRANSACTIONS_HISTORY WHERE event_key = p_item_key AND item_type = p_item_type)
1652 AND pending_request_flag = 'Y';
1653 END IF;
1654 COMMIT;
1655 END IF;
1656
1657 IF l_debug_on THEN
1658 wsh_debug_sv.pop (l_module_name);
1659 END IF;
1660 EXCEPTION
1661 WHEN wsh_update_history 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_update_history exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1665 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_update_history');
1666 END IF;
1667 WHEN OTHERS THEN
1668 x_return_status := wsh_util_core.g_ret_sts_error;
1669 IF l_debug_on THEN
1670 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
1671 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1672 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1673 END IF;
1674 END Update_Txn_History;
1675
1676 PROCEDURE WSHSUPI_SELECTOR( Item_type IN VARCHAR2,
1677 Item_key IN VARCHAR2,
1678 Actid IN NUMBER,
1679 Funcmode IN VARCHAR2,
1680 Resultout IN OUT NOCOPY VARCHAR2
1681 ) IS
1682 l_user_id NUMBER;
1683 l_resp_id NUMBER;
1684 l_resp_appl_id NUMBER;
1685 l_org_id NUMBER;
1686 l_current_org_id NUMBER;
1687 l_client_org_id NUMBER;
1688 --
1689 l_debug_on BOOLEAN;
1690 --
1691 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'WSHSUPI_SELECTOR';
1692 --
1693 BEGIN
1694 --
1695 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1696 --
1697 IF l_debug_on IS NULL
1698 THEN
1699 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1700 END IF;
1701 --
1702 wsh_debug_sv.start_debug('wf_context');
1703 IF l_debug_on THEN
1704 wsh_debug_sv.push(l_module_name, 'WSHSUPI_SELECTOR');
1705 wsh_debug_sv.log (l_module_name, 'Item Type', Item_type);
1706 wsh_debug_sv.log (l_module_name, 'Item_Key', Item_key);
1707 wsh_debug_sv.log (l_module_name, 'Funcmode', Funcmode);
1708 END IF;
1709
1710 IF(funcmode = 'RUN') THEN
1711 Resultout := 'COMPLETE';
1712 ELSIF(funcmode = 'SET_CTX') THEN
1713 l_user_id := wf_engine.GetItemAttrNumber(
1714 'WSHSUPI',
1715 Item_key,
1716 'USER_ID');
1717 l_resp_appl_id := wf_engine.GetItemAttrNumber(
1718 'WSHSUPI',
1719 Item_key,
1720 'APPLICATION_ID');
1721
1722 l_resp_id := wf_engine.GetItemAttrNumber(
1723 'WSHSUPI',
1724 Item_key,
1725 'RESPONSIBILITY_ID');
1726
1727 IF(l_resp_appl_id IS NULL OR l_resp_id IS NULL) THEN
1728 RAISE no_data_found;
1729 ELSE
1730 FND_GLOBAL.Apps_Initialize(l_user_id, l_resp_id, l_resp_appl_id);
1731 END IF;
1732 Resultout := 'COMPLETE';
1733 ELSIF(funcmode = 'TEST_CTX') THEN
1734 -- Bugfix: 8501373
1735 -- Check to verify if current session user is same as user who initiated WSHSUPI workflow
1736 -- This is done since shipping is not dependent on ORG_ID (Oper Unit)
1737 l_user_id := wf_engine.GetItemAttrNumber( 'WSHSUPI',
1738 Item_key,
1739 'USER_ID'
1740 );
1741
1742 IF l_debug_on THEN
1743 wsh_debug_sv.log (l_module_name, 'l_user_id', l_user_id);
1744 wsh_debug_sv.log (l_module_name, 'FND_GLOBAL.USER_ID', FND_GLOBAL.USER_ID);
1745 END IF;
1746
1747 IF l_user_id <> FND_GLOBAL.USER_ID THEN
1748 Resultout := 'FALSE';
1749 ELSE
1750 Resultout := 'TRUE';
1751 END IF;
1752 END IF; -- if funcmode = run
1753
1754 IF l_debug_on THEN
1755 wsh_debug_sv.log (l_module_name, 'Resultout', Resultout);
1756 wsh_debug_sv.pop(l_module_name);
1757 END IF;
1758 wsh_debug_sv.stop_debug;
1759 EXCEPTION
1760 WHEN OTHERS THEN
1761 resultout := 'COMPLETE:FAILURE';
1762 IF l_debug_on THEN
1763 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
1764 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1765 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1766 END IF;
1767 wsh_debug_sv.stop_debug;
1768 RAISE;
1769 END WSHSUPI_SELECTOR;
1770
1771 --k proj
1772
1773 /*---------------------------------------------------------------------
1774
1775 FUNCTION : branch_cms_tpw_flow
1776 Parameter: p_event_key
1777
1778 Comments :
1779
1780 This function is used to branch the flow of the cancellation for TPW
1781 and CMS. If the cancellation is done for CMS, this function will return
1782 TRUE. In future (if it is decided that CMS and TPW have same flow for
1783 cancellation) this function returns always TRUE;
1784
1785 ---------------------------------------------------------------------*/
1786
1787 FUNCTION branch_cms_tpw_flow (p_event_key IN VARCHAR2)
1788 RETURN BOOLEAN IS
1789
1790 l_cms_flow BOOLEAN := FALSE;
1791 l_debug_on BOOLEAN;
1792 --
1793 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' ||
1794 'BRANCH_CMS_TPW_FLOW';
1795 --
1796 BEGIN
1797
1798 --
1799 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1800 --
1801 IF l_debug_on IS NULL
1802 THEN
1803 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1804 END IF;
1805 --
1806 IF l_debug_on THEN
1807 wsh_debug_sv.push(l_module_name);
1808 wsh_debug_sv.log(l_module_name, 'p_event_key',p_event_key);
1809 END IF;
1810
1811 IF p_event_key LIKE 'CMS%' THEN
1812 l_cms_flow := TRUE;
1813 END IF;
1814
1815 IF l_debug_on THEN
1816 wsh_debug_sv.log(l_module_name, 'l_cms_flow',l_cms_flow);
1817 wsh_debug_sv.pop(l_module_name);
1818 END IF;
1819
1820 RETURN l_cms_flow;
1821
1822 EXCEPTION
1823
1824 WHEN OTHERS THEN
1825 wsh_util_core.default_handler('WSH_TRANSACTIONS_UTIL.branch_cms_tpw_flow');
1826 IF l_debug_on THEN
1827 WSH_DEBUG_SV.logmsg(l_module_name,
1828 'Unexpected error has occured. Oracle error message is '||
1829 substr(SQLERRM,1,200), WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1830 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1831 END IF;
1832
1833 END branch_cms_tpw_flow;
1834
1835
1836 /*---------------------------------------------------------------------
1837
1838 PROCEDURE : Check_cancellation_inprogress
1839 Parameters: p_delivery_id
1840 x_cancellation_in_progress
1841 x_return_status
1842
1843 Comments :
1844
1845 This procedure is used to determine if there is a cancellation in progress
1846 for CMS flow. A cancellation is in progress if the supplier has sent a
1847 cancel message, but no CBOD confirmation/rejection has arrived yet.
1848
1849 ---------------------------------------------------------------------*/
1850
1851 PROCEDURE Check_cancellation_inprogress
1852 (
1853 p_delivery_name IN VARCHAR2,
1854 x_cancellation_in_progress OUT NOCOPY BOOLEAN ,
1855 x_return_status OUT NOCOPY VARCHAR2
1856 )
1857 IS
1858
1859 l_debug_on BOOLEAN;
1860 --
1861 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' ||
1862 'CHECK_CANCELLATION_INPROGRESS';
1863 --
1864 l_status VARCHAR2(5);
1865 l_event_key VARCHAR2(250);
1866
1867 CURSOR c_del_status (v_delivery_name varchar2) IS
1868 SELECT status_code
1869 FROM wsh_new_deliveries
1870 WHERE name = v_delivery_name;
1871
1872 CURSOR c_get_event_key(v_delivery_name NUMBER) IS
1873 SELECT event_key
1874 FROM wsh_transactions_history
1875 WHERE ENTITY_NUMBER = v_delivery_name
1876 AND ENTITY_TYPE = 'DLVY'
1877 AND ACTION_TYPE = 'D'
1878 AND document_direction = 'O'
1879 ORDER BY transaction_id DESC;
1880 --bmso
1881 l_wf_status VARCHAR2(30);
1882 l_result VARCHAR2(30);
1883 e_success EXCEPTION;
1884 BEGIN
1885
1886 --
1887 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1888 --
1889 IF l_debug_on IS NULL
1890 THEN
1891 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1892 END IF;
1893 --
1894 IF l_debug_on THEN
1895 wsh_debug_sv.push(l_module_name);
1896 wsh_debug_sv.log(l_module_name, 'p_delivery_name',p_delivery_name);
1897 END IF;
1898
1899 x_cancellation_in_progress := FALSE;
1900
1901 OPEN c_del_status(p_delivery_name);
1902 FETCH c_del_status INTO l_status;
1903 IF c_del_status%NOTFOUND THEN
1904 IF l_debug_on THEN
1905 wsh_debug_sv.logmsg(l_module_name, 'ERROR Invalid delivery');
1906 END IF;
1907 FND_MESSAGE.Set_Name('WSH', 'WSH_DEL_NOT_FOUND');
1908 WSH_UTIL_CORE.add_message (wsh_util_core.g_ret_sts_error,l_module_name);
1909 CLOSE c_del_status;
1910 RAISE fnd_api.g_exc_error;
1911 END IF;
1912 IF l_debug_on THEN
1913 wsh_debug_sv.log(l_module_name, 'l_status',l_status);
1914 END IF;
1915
1916 IF l_status <> 'SC' THEN
1917 x_cancellation_in_progress := FALSE;
1918 RAISE e_success;
1919 END IF;
1920 CLOSE c_del_status;
1921
1922 -- IF cancellation rejection comes and the status of the delivery is
1923 -- SC then we need to see if the second workflow instance
1924 -- (cancellation workflow) is still active
1925
1926 OPEN c_get_event_key(p_delivery_name);
1927 FETCH c_get_event_key INTO l_event_key;
1928 IF c_get_event_key%NOTFOUND THEN
1929
1930 IF l_debug_on THEN
1931 wsh_debug_sv.logmsg(l_module_name, 'Cannot find the transaction history record');
1932 END IF;
1933
1934 x_cancellation_in_progress := FALSE;
1935 CLOSE c_get_event_key;
1936
1937 ELSE --{
1938
1939 CLOSE c_get_event_key;
1940
1941 IF l_debug_on THEN
1942 wsh_debug_sv.log(l_module_name, 'l_event_key',l_event_key);
1943 wsh_debug_sv.logmsg(l_module_name, 'calling program WF_ENGINE.ItemStatus',WSH_DEBUG_SV.C_PROC_LEVEL);
1944 END IF;
1945 WF_ENGINE.ItemStatus(
1946 itemtype => 'WSHSUPI',
1947 itemkey => l_event_key,
1948 status => l_wf_status,
1949 result => l_result
1950 );
1951
1952 -- values COMPLETE,SUSPENDED,ACTIVE,ERROR
1953
1954 IF l_wf_status IN ('COMPLETE','SUSPENDED') THEN
1955 x_cancellation_in_progress := FALSE;
1956 ELSE
1957 x_cancellation_in_progress := TRUE;
1958 END IF;
1959
1960
1961 END IF; --}
1962
1963
1964 x_return_status := wsh_util_core.g_ret_sts_success;
1965
1966 IF l_debug_on THEN
1967 wsh_debug_sv.log(l_module_name, 'x_cancellation_in_progress',
1968 x_cancellation_in_progress);
1969 wsh_debug_sv.pop(l_module_name);
1970 END IF;
1971
1972
1973 EXCEPTION
1974
1975 WHEN e_success THEN
1976 x_return_status := wsh_util_core.g_ret_sts_success;
1977 --
1978 IF l_debug_on THEN
1979 wsh_debug_sv.log(l_module_name, 'x_cancellation_in_progress',
1980 x_cancellation_in_progress);
1981 wsh_debug_sv.pop(l_module_name);
1982 END IF;
1983
1984 WHEN fnd_api.g_exc_error THEN
1985 x_return_status := fnd_api.g_ret_sts_error;
1986 --
1987 IF l_debug_on THEN
1988 wsh_debug_sv.logmsg(l_module_name, 'FND_API.G_EXC_ERROR exception has occured.', wsh_debug_sv.c_excep_level);
1989 wsh_debug_sv.pop(l_module_name, 'EXCEPTION:FND_API.G_EXC_ERROR');
1990 END IF;
1991 WHEN OTHERS THEN
1992 x_return_status := wsh_util_core.g_ret_sts_unexp_error;
1993 wsh_util_core.default_handler('WSH_TRANSACTIONS_UTIL.check_cancellation_inprogress',l_module_name);
1994 IF l_debug_on THEN
1995 WSH_DEBUG_SV.logmsg(l_module_name,
1996 'Unexpected error has occured. Oracle error message is '||
1997 substr(SQLERRM,1,200), WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1998 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1999 END IF;
2000
2001 END Check_cancellation_inprogress;
2002
2003
2004 /*---------------------------------------------------------------------
2005
2006 PROCEDURE : Check_cancellation_wf
2007 Parameters: item_type
2008 item_key
2009 actid
2010 funcmode
2011 resultout
2012
2013 Comments :
2014
2015 This procedure is called from workflow and will determine if there is a
2016 Cancellation in process for CMS system.
2017
2018 ---------------------------------------------------------------------*/
2019
2020 PROCEDURE Check_cancellation_wf (
2021 item_type IN VARCHAR2,
2022 item_key IN VARCHAR2,
2023 actid IN NUMBER,
2024 funcmode IN VARCHAR2,
2025 resultout OUT NOCOPY VARCHAR2
2026 )
2027 IS
2028
2029 l_debug_on BOOLEAN;
2030 --
2031 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' ||
2032 'CHECK_CANCELLATION_WF';
2033 --
2034 l_delivery_name varchar2(30);
2035 l_cancellation_in_progress BOOLEAN := FALSE;
2036 l_return_status VARCHAR2(1);
2037 l_num_warn NUMBER := 0;
2038 l_num_err NUMBER := 0;
2039 l_transaction_id NUMBER;
2040
2041 --
2042 CURSOR c_del_name_cur (v_item_key varchar2, v_item_type VARCHAR2) IS --bmso
2043 SELECT wth2.entity_number , wth1.transaction_id
2044 FROM wsh_transactions_history wth1,
2045 wsh_transactions_history wth2
2046 where wth1.entity_type='DLVY_INT'
2047 and wth1.event_key = v_item_key
2048 and wth1.item_type = v_item_type
2049 and wth1.document_type = 'SA'
2050 and wth1.document_direction='I'
2051 and wth1.action_type = 'A'
2052 AND wth2.entity_type = 'DLVY'
2053 AND wth2.document_type = 'SR'
2054 AND wth2.action_type = 'A'
2055 AND wth2.document_direction = 'O'
2056 AND wth2.item_type = v_item_type
2057 AND wth2.event_key = v_item_key
2058 ORDER BY wth1.transaction_id desc;
2059
2060
2061
2062 e_send_no EXCEPTION;
2063 e_send_yes EXCEPTION;
2064 BEGIN
2065
2066 --
2067 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2068 --
2069 IF l_debug_on IS NULL
2070 THEN
2071 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2072 END IF;
2073 --
2074 SAVEPOINT s_Check_cancellation_wf ;
2075
2076 IF l_debug_on THEN
2077 --bmso do we need to start the debugger
2078 wsh_debug_sv.push(l_module_name);
2079 wsh_debug_sv.log(l_module_name, 'item_type',item_type);
2080 wsh_debug_sv.log(l_module_name, 'item_key',item_key);
2081 wsh_debug_sv.log(l_module_name, 'actid',actid);
2082 wsh_debug_sv.log(l_module_name, 'funcmode',funcmode);
2083 END IF;
2084
2085 IF ( funcmode = 'RUN' ) THEN --{
2086 IF branch_cms_tpw_flow(p_event_key => item_key) THEN --{
2087 --get the delivery name
2088 OPEN c_del_name_cur(item_key, item_type);
2089 FETCH c_del_name_cur INTO l_delivery_name, l_transaction_id;
2090 IF c_del_name_cur%NOTFOUND THEN
2091 IF l_debug_on THEN
2092 wsh_debug_sv.logmsg(l_module_name, 'Error, invalid interface delivery');
2093 END IF;
2094 CLOSE c_del_name_cur;
2095 RAISE fnd_api.g_exc_error;
2096 END IF;
2097 CLOSE c_del_name_cur;
2098
2099 IF l_debug_on THEN
2100 wsh_debug_sv.log(l_module_name, 'l_delivery_name',l_delivery_name);
2101 wsh_debug_sv.log(l_module_name, 'l_transaction_id',l_transaction_id);
2102 END IF;
2103 -- see if there is any cancelation pending
2104 Check_cancellation_inprogress (
2105 p_delivery_name => l_delivery_name,
2106 x_cancellation_in_progress =>
2107 l_cancellation_in_progress,
2108 x_return_status => l_return_status
2109 );
2110
2111 wsh_util_core.api_post_call(
2112 p_return_status => l_return_status,
2113 x_num_warnings => l_num_warn,
2114 x_num_errors => l_num_err);
2115
2116 IF l_cancellation_in_progress THEN --{
2117
2118 UPDATE wsh_transactions_history
2119 SET TRANSACTION_STATUS = 'AP'
2120 WHERE transaction_id = l_transaction_id;
2121
2122 IF SQL%ROWCOUNT <> 1 THEN
2123
2124 IF l_debug_on THEN
2125 wsh_debug_sv.log(l_module_name, 'Error in updating the transaction hsitory record to status AP',SQL%ROWCOUNT);
2126 END IF;
2127 RAISE FND_API.g_exc_error;
2128 END IF;
2129 RAISE e_send_yes;
2130
2131 ELSE --}{
2132
2133 RAISE e_send_no;
2134
2135 END IF; --}
2136
2137 ELSE --}{
2138 RAISE e_send_no;
2139 END IF; --}
2140
2141 END IF; --}
2142
2143 IF l_debug_on THEN
2144 wsh_debug_sv.pop(l_module_name);
2145 END IF;
2146
2147
2148 EXCEPTION
2149
2150
2151 WHEN e_send_no THEN
2152 resultout := 'COMPLETE:N';
2153 IF l_debug_on THEN
2154 wsh_debug_sv.log(l_module_name, 'resultout', resultout);
2155 wsh_debug_sv.pop(l_module_name);
2156 END IF;
2157
2158 WHEN e_send_yes THEN
2159 resultout := 'COMPLETE:Y';
2160 IF l_debug_on THEN
2161 wsh_debug_sv.log(l_module_name, 'resultout', resultout);
2162 wsh_debug_sv.pop(l_module_name);
2163 END IF;
2164
2165 WHEN fnd_api.g_exc_unexpected_error THEN
2166 --bmso how do we rollback;
2167 --also COMPLETE:FAILURE does not exist for this
2168 rollback to s_Check_cancellation_wf;
2169 resultout := 'COMPLETE:FAILURE';
2170 --
2171 IF l_debug_on THEN
2172 wsh_debug_sv.logmsg(l_module_name, 'fnd_api.g_exc_unexpected_error exception has occured.', wsh_debug_sv.c_excep_level);
2173 wsh_debug_sv.pop(l_module_name, 'EXCEPTION:fnd_api.g_exc_unexpected_error');
2174 END IF;
2175 RAISE;
2176
2177 WHEN fnd_api.g_exc_error THEN
2178 rollback to s_Check_cancellation_wf;
2179 resultout := 'COMPLETE:FAILURE';
2180 --
2181 IF l_debug_on THEN
2182 wsh_debug_sv.logmsg(l_module_name, 'FND_API.G_EXC_ERROR exception has occured.', wsh_debug_sv.c_excep_level);
2183 wsh_debug_sv.pop(l_module_name, 'EXCEPTION:FND_API.G_EXC_ERROR');
2184 END IF;
2185
2186 RAISE;
2187 WHEN OTHERS THEN
2188 resultout := 'COMPLETE:FAILURE';
2189 wsh_util_core.default_handler('WSH_TRANSACTIONS_UTIL.check_cancellation_wf');
2190 IF l_debug_on THEN
2191 WSH_DEBUG_SV.logmsg(l_module_name,
2192 'Unexpected error has occured. Oracle error message is '||
2193 substr(SQLERRM,1,200), WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2194 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2195 END IF;
2196 RAISE;
2197
2198 END check_cancellation_wf;
2199
2200
2201
2202 /*---------------------------------------------------------------------
2203
2204 PROCEDURE : process_cbod_wf
2205 Parameters: item_type
2206 item_key
2207 actid
2208 funcmode
2209 resultout
2210
2211 Comments :
2212
2213 This procedure is called from workflow and will determine if there is a
2214 Cancellation in process for CMS system.
2215
2216 ---------------------------------------------------------------------*/
2217
2218 PROCEDURE process_cbod_wf (
2219 item_type IN VARCHAR2,
2220 item_key IN VARCHAR2,
2221 actid IN NUMBER,
2222 funcmode IN VARCHAR2,
2223 resultout OUT NOCOPY VARCHAR2
2224 )
2225 IS
2226
2227 l_debug_on BOOLEAN;
2228 --
2229 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' ||
2230 'PROCESS_CBOD_WF';
2231 --
2232 l_cbod_status VARCHAR2(5);
2233 l_sr_hist_record WSH_TRANSACTIONS_HISTORY_PKG.Txns_History_Record_Type;
2234 l_entity_number WSH_TRANSACTIONS_HISTORY.entity_number%TYPE;
2235 l_result_code VARCHAR2(50);
2236 l_transaction_status VARCHAR2(5);
2237 l_trx_id NUMBER;
2238 l_return_status VARCHAR2(2);
2239 l_sa_exist BOOLEAN := FALSE;
2240 l_param_list wf_parameter_list_t;
2241
2242 --
2243
2244 l_del_interface_id NUMBER;
2245
2246 CURSOR c_get_trx_id (v_name varchar2) IS --bmso
2247 SELECT wth2.transaction_id, wth2.transaction_status,
2248 to_number(wth2.entity_number)
2249 FROM wsh_transactions_history wth1,
2250 wsh_transactions_history wth2
2251 WHERE wth1.entity_number = v_name
2252 AND wth1.action_type = 'A'
2253 AND wth1.entity_type = 'DLVY'
2254 AND wth1.document_type = 'SR'
2255 AND wth1.document_direction = 'O'
2256 AND wth1.document_number = wth2.ORIG_DOCUMENT_NUMBER
2257 AND wth1.event_key = wth2.event_key
2258 AND wth2.document_direction = 'I'
2259 AND wth2.document_type = 'SA';
2260
2261
2262 CURSOR c_get_sr_record (v_entity_number varchar2) IS --bmso
2263 SELECT transaction_id,
2264 document_type,
2265 document_direction,
2266 document_number,
2267 orig_document_number,
2268 entity_number,
2269 entity_type,
2270 trading_partner_id,
2271 action_type,
2272 transaction_status,
2273 ecx_message_id,
2274 event_name,
2275 event_key ,
2276 item_type,
2277 internal_control_number,
2278 -- R12.1.1 STANDALONE PROJECT
2279 document_revision,
2280 attribute_category,
2281 attribute1,
2282 attribute2,
2283 attribute3,
2284 attribute4,
2285 attribute5,
2286 attribute6,
2287 attribute7,
2288 attribute8,
2289 attribute9,
2290 attribute10,
2291 attribute11,
2292 attribute12,
2293 attribute13,
2294 attribute14,
2295 attribute15,
2296 NULL -- LSP PROJECT : just added for dependency for client_id
2297 FROM wsh_transactions_history
2298 WHERE
2299 entity_number = v_entity_number
2300 AND action_type = 'A'
2301 AND entity_type = 'DLVY'
2302 and document_direction = 'O'
2303 and document_type = 'SR'
2304 ORDER BY transaction_id DESC;
2305
2306 CURSOR c_get_del_status (v_delivery_name VARCHAR2) IS
2307 SELECT status_code
2308 FROM wsh_new_deliveries
2309 WHERE name = v_delivery_name;
2310
2311 l_status wsh_new_deliveries.status_code%TYPE;
2312
2313 cursor c_get_cancel_rec (v_item_type VARCHAR2, v_item_key VARCHAR2)
2314 IS
2315 SELECT entity_number
2316 FROM wsh_transactions_history
2317 WHERE item_type = v_item_type
2318 AND event_key = v_item_key
2319 AND document_direction = 'O'
2320 AND document_type = 'SR'
2321 AND ACTION_TYPE = 'D'
2322 ORDER BY transaction_id desc;
2323
2324 cursor c_sr_instance (v_item_type VARCHAR2, v_item_key VARCHAR2)
2325 IS
2326 SELECT 1
2327 FROM wsh_transactions_history
2328 WHERE item_type = v_item_type
2329 AND event_key = v_item_key
2330 AND document_direction = 'O'
2331 AND document_type = 'SR'
2332 AND ACTION_TYPE = 'A'
2333 ORDER BY transaction_id desc;
2334
2335 l_dummy NUMBER;
2336
2337 e_send_true EXCEPTION;
2338 e_send_false EXCEPTION;
2339 BEGIN
2340
2341 --
2342 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2343 --
2344 IF l_debug_on IS NULL
2345 THEN
2346 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2347 END IF;
2348 --
2349 SAVEPOINT s_process_cbod ;
2350
2351 IF l_debug_on THEN
2352 wsh_debug_sv.push(l_module_name);
2353 wsh_debug_sv.log(l_module_name, 'item_type',item_type);
2354 wsh_debug_sv.log(l_module_name, 'item_key',item_key);
2355 wsh_debug_sv.log(l_module_name, 'actid',actid);
2356 wsh_debug_sv.log(l_module_name, 'funcmode',funcmode);
2357 END IF;
2358
2359 IF ( funcmode = 'RUN' ) THEN --{
2360 IF branch_cms_tpw_flow(p_event_key => item_key) THEN --{
2361
2362 -- get the history record for cancellation
2363
2364 OPEN c_get_cancel_rec(item_type, item_key);
2365 FETCH c_get_cancel_rec INTO l_entity_number;
2366
2367 IF c_get_cancel_rec%NOTFOUND THEN --{
2368
2369 --If the cancel record is not found then it could be the
2370 -- case the the first work flow istance is being closed
2371 -- by the cancellation workflow. This case happens when a
2372 -- CBOD confirmation arriaves at the cancellation workflow.
2373
2374 CLOSE c_get_cancel_rec;
2375
2376 OPEN c_sr_instance(item_type, item_key);
2377 FETCH c_sr_instance INTO l_dummy;
2378 IF c_sr_instance%NOTFOUND THEN --{
2379 IF l_debug_on THEN
2380 wsh_debug_sv.logmsg(l_module_name,
2381 'Error: Could not find record in transaction history');
2382 END IF;
2383 CLOSE c_sr_instance;
2384 RAISE e_send_false;
2385 END IF; --}
2386
2387 CLOSE c_sr_instance;
2388
2389 -- close the workflow
2390 RAISE e_send_true;
2391
2392 END IF; --}
2393 CLOSE c_get_cancel_rec;
2394
2395 -- If for some reason the shipment advice has been processed through
2396 -- the message correction form and the cancellation workflow was
2397 -- not closed then close the cancellation workflow as a CBOD reject.
2398
2399 OPEN c_get_del_status (l_entity_number);
2400 FETCH c_get_del_status INTO l_status;
2401 IF c_get_del_status%NOTFOUND THEN
2402 FND_MESSAGE.Set_Name('WSH', 'WSH_DEL_NOT_FOUND');
2403 WSH_UTIL_CORE.add_message (wsh_util_core.g_ret_sts_error,l_module_name);
2404 CLOSE c_get_del_status;
2405 RAISE e_send_false;
2406 END IF;
2407 CLOSE c_get_del_status;
2408
2409 IF l_status IN ('CO','IT','CL','SA') THEN --{
2410 IF l_debug_on THEN
2411 wsh_debug_sv.logmsg(l_module_name, 'Calling wf_engine.setItemAttribute');
2412 END IF;
2413 wf_engine.setItemAttrText
2414 (
2415 itemType => item_type,
2416 itemKey => item_key,
2417 aname => 'PARAMETER6',
2418 avalue => '99'
2419 );
2420 RAISE e_send_true;
2421
2422 END IF; --}
2423
2424 --get the Shipment Request record
2425 OPEN c_get_sr_record(l_entity_number);
2426 FETCH c_get_sr_record INTO l_sr_hist_record;
2427 IF c_get_sr_record%NOTFOUND THEN
2428 IF l_debug_on THEN
2429 wsh_debug_sv.log(l_module_name,
2430 'Error: Could not find Shipment Request record for ',
2431 l_entity_number);
2432 END IF;
2433 CLOSE c_get_sr_record;
2434 RAISE e_send_false;
2435 END IF;
2436 CLOSE c_get_sr_record;
2437
2438 --get the transaction_id for the Shipment Advice record
2439
2440 OPEN c_get_trx_id(l_entity_number);
2441 FETCH c_get_trx_id INTO l_trx_id , l_transaction_status,
2442 l_del_interface_id;
2443 IF c_get_trx_id%NOTFOUND THEN
2444 l_sa_exist := FALSE;
2445 IF l_debug_on THEN
2446 wsh_debug_sv.log(l_module_name,
2447 'No SA record exist for ',
2448 l_entity_number);
2449 END IF;
2450 ELSE
2451 l_sa_exist := TRUE;
2452 END IF;
2453 CLOSE c_get_trx_id;
2454
2455 IF l_debug_on THEN
2456 wsh_debug_sv.log(l_module_name,'l_sa_exist',l_sa_exist);
2457 wsh_debug_sv.log(l_module_name,'l_transaction_status',
2458 l_transaction_status);
2459 wsh_debug_sv.log(l_module_name,'l_trx_id',l_trx_id);
2460 wsh_debug_sv.log(l_module_name,'l_del_interface_id',
2461 l_del_interface_id);
2462 END IF;
2463
2464 l_cbod_status := wf_engine.GetItemAttrText(
2465 'WSHSUPI',
2466 Item_key,
2467 'PARAMETER6');
2468 IF l_debug_on THEN
2469 wsh_debug_sv.log(l_module_name,'l_cbod_status',l_cbod_status);
2470 END IF;
2471 IF l_cbod_status = '00' THEN --{ confirmation
2472
2473
2474 --IF Shipment Advice record exist then change the status to 'SX'
2475
2476 IF l_sa_exist THEN --{
2477
2478 -- Delete the interface record
2479
2480 WSH_PROCESS_INTERFACED_PKG.delete_interface_records (
2481 p_delivery_interface_id => l_del_interface_id,
2482 x_return_status => l_Return_Status
2483 ) ;
2484
2485 IF (l_Return_Status NOT IN (WSH_UTIL_CORE.g_ret_sts_success,
2486 WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
2487 RAISE e_send_false;
2488 END IF;
2489
2490 --Update the status_code for shipment advice record to 'SX'
2491
2492 UPDATE wsh_transactions_history
2493 SET transaction_status= 'SX',
2494 entity_number = l_entity_number,
2495 entity_type = 'DLVY'
2496 WHERE
2497 transaction_id = l_trx_id; --bmso
2498
2499 END IF; --}
2500
2501
2502 -- Raise the event for the first workflow instance to finish.
2503
2504 l_sr_hist_record.Event_Name := 'ORACLE.APPS.FTE.SSNO.CONFIRM';
2505
2506 WSH_EXTERNAL_INTERFACE_SV.Raise_Event ( l_sr_hist_record, '99',
2507 l_Return_Status );
2508
2509 IF l_debug_on THEN
2510 wsh_debug_sv.log (l_module_name, 'Return status after Raise_Event ', l_Return_Status);
2511 END IF;
2512
2513 IF (l_Return_Status <> WSH_UTIL_CORE.g_ret_sts_success ) THEN
2514 RAISE e_send_false;
2515 END IF;
2516
2517
2518 ELSE --}{ rejection
2519 IF l_sa_exist THEN --{
2520 IF l_transaction_status = 'ER' THEN --{
2521
2522 wf_engine.handleError(
2523 itemType => l_sr_hist_record.item_type,
2524 itemKey => l_sr_hist_record.event_key,
2525 activity => 'WSH_SUPPLIER_WF:WSH_PROCESS_DELIVERY',
2526 command => 'RETRY',
2527 result => NULL
2528 );
2529
2530 ELSIF l_transaction_status = 'AP' THEN --}{
2531
2532 wf_engine.completeActivity (
2533 itemtype => l_sr_hist_record.item_type,
2534 itemkey => l_sr_hist_record.event_key,
2535 activity => 'WSH_SUPPLIER_WF:CONTINUE_SHIPMENT_ADVICE',
2536 result => l_result_code);
2537
2538 END IF; --}
2539 END IF; --}
2540 END IF; --}
2541 END IF; --}
2542
2543 RAISE e_send_true;
2544
2545 END IF; --}
2546
2547 IF l_debug_on THEN
2548 wsh_debug_sv.pop(l_module_name);
2549 END IF;
2550
2551
2552 EXCEPTION
2553
2554
2555 WHEN e_send_true THEN
2556 resultout := 'COMPLETE:T';
2557 IF l_debug_on THEN
2558 wsh_debug_sv.log(l_module_name, 'resultout', resultout);
2559 wsh_debug_sv.pop(l_module_name);
2560 END IF;
2561
2562 WHEN e_send_false THEN
2563 resultout := 'COMPLETE:F';
2564 IF l_debug_on THEN
2565 wsh_debug_sv.log(l_module_name, 'resultout', resultout);
2566 wsh_debug_sv.pop(l_module_name);
2567 END IF;
2568
2569 WHEN OTHERS THEN
2570 resultout := 'COMPLETE:FAILURE';
2571 wsh_util_core.default_handler('WSH_TRANSACTIONS_UTIL.process_cbod_wf');
2572 IF l_debug_on THEN
2573 WSH_DEBUG_SV.logmsg(l_module_name,
2574 'Unexpected error has occured. Oracle error message is '||
2575 substr(SQLERRM,1,200), WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2576 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2577 END IF;
2578 RAISE;
2579 END process_cbod_wf;
2580
2581 --Fulfillment Batch XML Project
2582 --Thie procedure sets the name of the event based on the document type and
2583 --the value of profile option 'WSH: Distributed Source Entity '
2584 --This procedure will be invoked from WSHSSNI.xgm
2585 PROCEDURE get_event_name (p_document_type IN VARCHAR2,
2586 x_event_name OUT NOCOPY VARCHAR2,
2587 x_item_type OUT NOCOPY VARCHAR2) IS
2588
2589 BEGIN
2590 IF P_document_type ='SA' THEN
2591 IF nvl(fnd_profile.Value('WSH_SR_SOURCE'),'D') = 'B' THEN
2592 x_event_name:= 'oracle.apps.wsh.batch.bsai';
2593 x_item_type:='WSHBATI';
2594 ELSE
2595 x_event_name:= 'oracle.apps.wsh.sup.ssai';
2596 x_item_type:='WSHSUPI';
2597 END IF;
2598 ELSIF p_document_type = 'SR' THEN
2599 x_event_name:= 'oracle.apps.wsh.tpw.ssri';
2600 x_item_type:='WSHTPWI';
2601 END IF;
2602 END get_event_name;
2603
2604 --Fulfillment Batch XML Project
2605 --This procedure is wrapper for ECX_STANDARD.isDeliveryRequired.
2606 --This API raises EXCEPTION when the result out from the ECX API is 'COMPLETE:F'
2607 --In this case,it keeps the workflow activity in 'Error' status,so that it may be retried.
2608 procedure isDeliveryRequired (itemtype in varchar2,
2609 itemkey in varchar2,
2610 actid in number,
2611 funcmode in varchar2,
2612 resultout in out NOCOPY varchar2) IS
2613 --
2614 l_resultout VARCHAR2(1000);
2615 COMPLETE_F EXCEPTION;
2616 --
2617 l_debug_on BOOLEAN;
2618 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'ISDELIVERYREQUIRED';
2619 --
2620 BEGIN
2621 --
2622 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2623 --
2624 IF l_debug_on IS NULL THEN
2625 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2626 END IF;
2627 l_resultout := wf_engine.eng_null;
2628 --
2629 IF l_debug_on THEN
2630 wsh_debug_sv.push(l_module_name);
2631 wsh_debug_sv.log(l_module_name, 'itemtype',itemtype);
2632 wsh_debug_sv.log(l_module_name, 'itemkey',itemkey);
2633 wsh_debug_sv.log(l_module_name, 'actid',actid);
2634 wsh_debug_sv.log(l_module_name, 'funcmode',funcmode);
2635 wsh_debug_sv.log(l_module_name, 'l_resultout',l_resultout);
2636 END IF;
2637 ecx_standard.isDeliveryRequired(itemtype,itemkey,actid,funcmode,l_resultout);
2638 resultout := l_resultout;
2639 IF l_debug_on THEN
2640 wsh_debug_sv.log(l_module_name, 'resultout',resultout);
2641 END IF;
2642 IF nvl(resultout,'COMPLETE:F') <> 'COMPLETE:T' THEN
2643 RAISE COMPLETE_F;
2644 END IF;
2645 IF l_debug_on THEN
2646 wsh_debug_sv.pop(l_module_name);
2647 END IF;
2648
2649 EXCEPTION
2650 WHEN COMPLETE_F THEN
2651 IF l_debug_on THEN
2652 wsh_debug_sv.pop(l_module_name,'EXCEPTION:COMPLETE:F');
2653 END IF;
2654 RAISE;
2655 WHEN OTHERS THEN
2656 IF l_debug_on THEN
2657 wsh_debug_sv.log(l_module_name, 'resultout',resultout);
2658 WSH_DEBUG_SV.logmsg(l_module_name,
2659 'Unexpected error has occured. Oracle error message is '||
2660 substr(SQLERRM,1,200), WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2661 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2662 END IF;
2663 RAISE;
2664 END isDeliveryRequired;
2665
2666 END WSH_TRANSACTIONS_UTIL;