[Home] [Help]
PACKAGE BODY: APPS.WSH_DELIVERY_UTIL
Source
1 PACKAGE BODY WSH_DELIVERY_UTIL
2 -- $Header: WSHDLUTB.pls 120.0.12000000.2 2007/01/23 19:12:49 rvishnuv ship $
3 AS
4
5 /*===========================================================================
6 | |
7 | PROCEDURE NAME Update_Dlvy_Status |
8 | |
9 | DESCRIPTION This procedure is used to update the delivery status |
10 | appropriately depending on the parameters passed. |
11 | |
12 | MODIFICATION HISTORY |
13 | |
14 | 02/20/02 Ravikiran Vishnuvajhala Created |
15 | |
16 ============================================================================*/
17 --
18 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_DELIVERY_UTIL';
19 --
20 procedure Update_Dlvy_Status( p_delivery_id IN NUMBER,
21 p_action_code IN VARCHAR2 ,
22 p_document_type IN VARCHAR2 ,
23 x_return_status OUT NOCOPY VARCHAR2
24 )
25 IS
26
27 --pragma AUTONOMOUS_TRANSACTION;
28
29 l_del_rows wsh_util_core.id_tab_type;
30 l_planned_flag varchar2(1);
31
32 wsh_plan_error EXCEPTION;
33 wsh_unplan_error EXCEPTION;
34 wsh_invalid_doc_type EXCEPTION;
35 wsh_invalid_action_type EXCEPTION;
36 l_return_status VARCHAR2(1);
37 l_valid_flag BOOLEAN;
38 l_warning_count NUMBER :=0;
39 --
40 l_debug_on BOOLEAN;
41 --
42 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_DLVY_STATUS';
43 --
44 BEGIN
45 --
46 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
47 --
48 IF l_debug_on IS NULL
49 THEN
50 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
51 END IF;
52 --
53 IF l_debug_on THEN
54 wsh_debug_sv.push(l_module_name, 'Update_Dlvy_Status');
55 wsh_debug_sv.log(l_module_name, 'p_delivery_id',p_delivery_id);
56 wsh_debug_sv.log(l_module_name, 'p_action_code',p_action_code);
57 wsh_debug_sv.log(l_module_name, 'p_document_type',p_document_type);
58 END IF;
59
60 l_return_status :=wsh_util_core.g_ret_sts_success;
61
62 -- It unlocks the delivery and sets it to 'OP' status if p_action_code is null
63 -- Plan and Unplan APIs do not recognize the new statuses, therefore we have
64 -- to call them only after the delivery status is set to 'OP'
65
66 IF p_action_code IS NULL THEN
67
68 update wsh_new_deliveries
69 set status_code ='OP'
70 where delivery_id = p_delivery_id
71 and status_code IN ('SR','SC');
72
73 l_del_rows(l_del_rows.COUNT + 1) := p_delivery_id;
74 WSH_NEW_DELIVERY_ACTIONS.Unplan(p_del_rows => l_del_rows,
75 x_return_status => l_return_status);
76
77 IF l_debug_on THEN
78 wsh_debug_sv.log (l_module_name,'Unplan->x_return_status: ',l_return_status);
79 END IF;
80
81 IF ( l_return_status not in (wsh_util_core.g_ret_sts_success,wsh_util_core.g_ret_sts_warning) ) THEN
82 raise wsh_unplan_error;
83 ELSE
84 IF (l_return_status = wsh_util_core.g_ret_sts_warning) THEN
85 WSH_DELIVERY_VALIDATIONS.check_smc(p_delivery_id => p_delivery_id,
86 x_valid_flag => l_valid_flag,
87 x_return_status => l_return_status);
88 IF l_debug_on THEN
89 wsh_debug_sv.log (l_module_name,'check_smc->x_return_status: ',l_return_status);
90 END IF;
91
92 IF (l_return_status <> wsh_util_core.g_ret_sts_success) THEN
93 raise wsh_plan_error;
94 ELSE
95 l_warning_count:=l_warning_count + 1;
96 END IF;
97 END IF;
98 END IF;
99 ELSIF ( p_action_code = 'A' ) THEN
100 IF p_document_type IS NULL THEN
101 raise wsh_invalid_doc_type;
102 ELSIF (p_document_type = 'SR' ) THEN
103
104 select planned_flag into l_planned_flag
105 from wsh_new_deliveries
106 where delivery_id = p_delivery_id;
107
108 IF l_planned_flag = 'N' THEN
109
110 l_del_rows(l_del_rows.COUNT + 1) := p_delivery_id;
111 WSH_NEW_DELIVERY_ACTIONS.Plan( p_del_rows => l_del_rows,
112 x_return_status => l_return_status);
113
114 IF l_debug_on THEN
115 wsh_debug_sv.log (l_module_name,'plan->x_return_status: ',l_return_status);
116 END IF;
117
118 IF ( l_return_status not in (wsh_util_core.g_ret_sts_success,wsh_util_core.g_ret_sts_warning) ) THEN
119 raise wsh_plan_error;
120 ELSE
121 IF (l_return_status = wsh_util_core.g_ret_sts_warning) THEN
122 WSH_DELIVERY_VALIDATIONS.check_smc(p_delivery_id => p_delivery_id,
123 x_valid_flag => l_valid_flag,
124 x_return_status => l_return_status);
125 IF l_debug_on THEN
126 wsh_debug_sv.log (l_module_name,'check_smc->x_return_status: ',l_return_status);
127 END IF;
128
129 IF (l_return_status <> wsh_util_core.g_ret_sts_success) THEN
130 raise wsh_plan_error;
131 ELSE
132 l_warning_count:=l_warning_count + 1;
133 END IF;
134 END IF;
135 END IF;
136 END IF;
137 update wsh_new_deliveries
138 set status_code ='SR'
139 where delivery_id = p_delivery_id
140 and status_code = 'OP';
141 ELSIF ( p_document_type = 'SA' ) THEN
142 update wsh_new_deliveries
143 set status_code ='SA'
144 where delivery_id = p_delivery_id
145 and status_code IN ('SR','SC');
146 ELSE
147 raise wsh_invalid_doc_type;
148 END IF;
149 ELSIF (p_action_code = 'D' ) THEN
150 update wsh_new_deliveries
151 set status_code ='SC'
152 where delivery_id = p_delivery_id
153 and status_code = 'SR';
154 ELSE
155 raise wsh_invalid_action_type;
156 END IF;
157
158 IF l_debug_on THEN
159 wsh_debug_sv.log (l_module_name,'l_return_status: ',l_return_status);
160 END IF;
161
162 IF (l_warning_count > 0 ) THEN
163 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING ;
164 ELSE
165 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
166 END IF;
167
168 IF l_debug_on THEN
169 wsh_debug_sv.pop (l_module_name);
170 END IF;
171 --commit;
172
173 EXCEPTION
174 WHEN wsh_plan_error THEN
175 x_return_status := l_return_status;
176 IF l_debug_on THEN
177 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_plan_error exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
178 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_plan_error');
179 END IF;
180 WHEN wsh_unplan_error THEN
181 x_return_status := l_return_status;
182 IF l_debug_on THEN
183 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_unplan_error exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
184 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_unplan_error');
185 END IF;
186 WHEN wsh_invalid_doc_type THEN
187 x_return_status := wsh_util_core.g_ret_sts_error;
188 IF l_debug_on THEN
189 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_invalid_doc_type exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
190 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_invalid_doc_type');
191 END IF;
192 WHEN wsh_invalid_action_type THEN
193 x_return_status := wsh_util_core.g_ret_sts_error;
194 IF l_debug_on THEN
195 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_invalid_action_type exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
196 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_invalid_action_type');
197 END IF;
198 WHEN others THEN
199 x_return_status := wsh_util_core.g_ret_sts_unexp_error;
200 IF l_debug_on THEN
201 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
202 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
203 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
204 END IF;
205 END Update_Dlvy_Status;
206
207
208 FUNCTION Is_SendDoc_Allowed( p_delivery_id IN NUMBER,
209 p_action_code IN VARCHAR2 DEFAULT 'A',
210 x_return_status OUT NOCOPY VARCHAR2)
211 RETURN BOOLEAN
212 IS
213
214 cursor del_status_cur is
215 select status_code, planned_flag
216 from wsh_new_deliveries
217 where delivery_id =p_delivery_id;
218
219 cursor det_tpw_cur is
220 select 'X'
221 from wsh_delivery_details wdd,
222 wsh_delivery_assignments_v wda
223 where wdd.delivery_detail_id = wda.delivery_detail_id
224 and wdd.container_flag='N'
225 and wdd.source_code ='WSH'
226 and wda.delivery_id=p_delivery_id
227 and rownum = 1;
228
229 l_status_code VARCHAR2(2);
230 l_planned_flag VARCHAR2(1);
231 l_det_temp VARCHAR2(1);
232 --k proj bmso
233
234 CURSOR c_get_event_key (v_delivery_id number) is
235 select wth.event_key, wth.item_type
236 from wsh_new_deliveries wnd,
237 wsh_transactions_history wth
238 where delivery_id =v_delivery_id
239 and wnd.name = wth.entity_number
240 and wth.document_direction = 'O'
241 and wth.document_type = 'SR'
242 and wth.action_type = 'A'
243 and wth.entity_type = 'DLVY'
244 order by transaction_id desc;
245
246 cursor c_inbound_txn_csr (v_event_key VARCHAR2, v_item_type VARCHAR2) is
247 select 'X'
248 from wsh_transactions_history
249 where event_key = v_event_key
250 and item_type = v_item_type
251 and document_direction = 'I'
252 and action_type = 'A'
253 and document_type = 'SA'
254 order by transaction_id desc;
255
256 l_sa_exist VARCHAR2(1);
257 l_event_key wsh_transactions_history.event_key%TYPE;
258 l_item_type wsh_transactions_history.item_type%TYPE;
259
260 wsh_del_not_found EXCEPTION;
261 wsh_invalid_action_code EXCEPTION;
262 --
263 l_debug_on BOOLEAN;
264 --
265 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'IS_SENDDOC_ALLOWED';
266 --
267 BEGIN
268 --
269 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
270 --
271 IF l_debug_on IS NULL
272 THEN
273 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
274 END IF;
275 --
276 IF l_debug_on THEN
277 wsh_debug_sv.push(l_module_name, 'Is_SendDoc_Allowed');
278 wsh_debug_sv.log (l_module_name, 'p_delivery_id',p_delivery_id);
279 wsh_debug_sv.log (l_module_name, 'p_action_code',p_action_code);
280 END IF;
281
282 open del_status_cur;
283 Fetch del_status_cur into l_status_code, l_planned_flag;
284 IF del_status_cur%NOTFOUND THEN
285 close del_status_cur;
286 raise wsh_del_not_found;
287 END IF;
288 IF del_status_cur%ISOPEN THEN
289 close del_status_cur;
290 END IF;
291 IF l_debug_on THEN
292 wsh_debug_sv.log (l_module_name, ' Status Code',l_status_code);
293 wsh_debug_sv.log (l_module_name, ' Planned Flag', l_planned_flag);
294 END IF;
295
296 IF ( p_action_code = 'A' ) THEN
297
298 IF ( l_status_code = 'OP') THEN
299 open det_tpw_cur;
300 fetch det_tpw_cur into l_det_temp;
301 close det_tpw_cur;
302 IF l_det_temp is null THEN
303 IF l_debug_on THEN
304 wsh_debug_sv.pop (l_module_name,'RETURN-TRUE');
305 END IF;
306 RETURN TRUE;
307 ELSE
308 IF l_debug_on THEN
309 wsh_debug_sv.pop (l_module_name,'RETURN-FALSE');
310 END IF;
311 RETURN FALSE;
312 END IF;
313 ELSE
314 IF l_debug_on THEN
315 wsh_debug_sv.pop (l_module_name,'RETURN-FALSE');
316 END IF;
317 RETURN FALSE;
318 END IF;
319 ELSIF ( p_action_code = 'D' ) THEN
320 IF ( l_status_code = 'SR' and l_planned_flag IN ('Y', 'F') ) THEN
321
322 --k proj bmso
323 -- if for cancellation the Shipment Advice record exist and
324 -- delivery status is in SR then do not allow the cancellation
325 -- to be sent.
326
327 OPEN c_get_event_key(p_delivery_id);
328 FETCH c_get_event_key INTO l_event_key, l_item_type;
329 CLOSE c_get_event_key;
330
331 IF l_debug_on THEN
332 wsh_debug_sv.log (l_module_name, 'l_event_key',l_event_key);
333 wsh_debug_sv.log (l_module_name, 'l_item_type',l_item_type);
334 END IF;
335
336 OPEN c_inbound_txn_csr(l_event_key,l_item_type);
337 FETCH c_inbound_txn_csr INTO l_sa_exist;
338 CLOSE c_inbound_txn_csr;
339
340 IF l_debug_on THEN
341 wsh_debug_sv.log (l_module_name, 'l_sa_exist',l_sa_exist);
342 END IF;
343
344 IF l_sa_exist = 'X' THEN
345 IF l_debug_on THEN
346 wsh_debug_sv.pop (l_module_name,'RETURN-FALSE');
347 END IF;
348 RETURN FALSE;
349 ELSE
350 IF l_debug_on THEN
351 wsh_debug_sv.pop (l_module_name,'RETURN-TRUE');
352 END IF;
353 RETURN TRUE;
354 END IF;
355
356 ELSE
357 IF l_debug_on THEN
358 wsh_debug_sv.pop (l_module_name,'RETURN-FALSE');
359 END IF;
360 RETURN FALSE;
361 END IF;
362 ELSE
363 raise wsh_invalid_action_code;
364 END IF;
365 x_return_status := wsh_util_core.g_ret_sts_success;
366
367 IF l_debug_on THEN
368 wsh_debug_sv.pop (l_module_name);
369 END IF;
370 EXCEPTION
371 WHEN wsh_del_not_found THEN
372 x_return_status := wsh_util_core.g_ret_sts_error;
373 FND_MESSAGE.Set_Name('WSH', 'WSH_DEL_NOT_FOUND');
374 WSH_UTIL_CORE.add_message (x_return_status,l_module_name);
375 IF l_debug_on THEN
376 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_del_not_found exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
377 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_del_not_found');
378 END IF;
379 RETURN FALSE;
380 WHEN wsh_invalid_action_code THEN
381 x_return_status := wsh_util_core.g_ret_sts_error;
382 IF l_debug_on THEN
383 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_invalid_action_code exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
384 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_invalid_action_code');
385 END IF;
386 RETURN FALSE;
387 WHEN others THEN
388 x_return_status := wsh_util_core.g_ret_sts_unexp_error;
389 IF l_debug_on THEN
390 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
391 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
392 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
393 END IF;
394 RETURN FALSE;
395 END Is_SendDoc_Allowed;
396
397 PROCEDURE Check_Updates_Allowed( p_changed_attributes IN WSH_INTERFACE.ChangedAttributeTabType,
398 p_source_code IN VARCHAR2,
399 x_update_allowed OUT NOCOPY VARCHAR2,
400 x_return_status OUT NOCOPY VARCHAR2)
401 IS
402
403 cursor del_cur ( p_source_line_id IN NUMBER ) is
404 select count(*)
405 from wsh_new_deliveries wnd,
406 wsh_delivery_assignments_v wda,
407 wsh_delivery_details wdd
408 where wnd.status_code in ('SR','SC')
409 --and wnd.planned_flag = 'Y'
410 and wda.delivery_id = wnd.delivery_id
411 and wda.delivery_detail_id = wdd.delivery_detail_id
412 and wdd.container_flag ='N'
413 and wdd.source_line_id = p_source_line_id
414 and wdd.source_code = p_source_code;
415
416 cursor det_org_cur ( p_source_line_id IN NUMBER ) is
417 select organization_id,
418 delivery_detail_id
419 from wsh_delivery_details
420 where source_code= p_source_code
421 and source_line_id = p_source_line_id
422 and rownum = 1;
423
424 l_counter NUMBER;
425 l_dCount NUMBER;
426 l_organization_id NUMBER;
427 l_delivery_detail_id NUMBER;
428 l_wh_type VARCHAR2(30);
429
430 l_del_rows wsh_util_core.id_tab_type;
431 l_status_code_tab wsh_util_core.id_tab_type;
432 l_planned_flag_tab wsh_util_core.id_tab_type;
433
434 l_char_temp VARCHAR2(1);
435 l_return_status VARCHAR2(1);
436 l_source_line_id NUMBER;
437
438 wsh_update_not_allowed EXCEPTION;
439 wsh_invalid_org EXCEPTION;
440 --
441 l_debug_on BOOLEAN;
442 --
443 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CHECK_UPDATES_ALLOWED';
444 --
445 BEGIN
446 --
447 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
448 --
449 IF l_debug_on IS NULL
450 THEN
451 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
452 END IF;
453 --
454 IF l_debug_on THEN
455 wsh_debug_sv.push(l_module_name, 'Check_Updates_Allowed');
456 wsh_debug_sv.log (l_module_name, 'p_source_code',p_source_code);
457 END IF;
458
459 l_return_status := wsh_util_core.g_ret_sts_success;
460
461 IF not WSH_DELIVERY_UTIL.G_INBOUND_FLAG THEN
462
463 FOR l_counter IN p_changed_attributes.FIRST ..p_changed_attributes.LAST LOOP
464
465 IF l_debug_on THEN
466 wsh_debug_sv.log(l_module_name,'original_source_line_id:',p_changed_attributes(l_counter).original_source_line_id);
467 wsh_debug_sv.log (l_module_name, 'source_line_id: ',p_changed_attributes(l_counter).source_line_id);
468 wsh_debug_sv.log (l_module_name, 'action_flag',p_changed_attributes(l_counter).action_flag);
469 END IF;
470
471 IF (p_changed_attributes(l_counter).action_flag <> 'I' ) THEN
472 --bug 2320115 fixed
473 IF (p_changed_attributes(l_counter).action_flag = 'S' ) THEN
474 l_source_line_id := nvl(p_changed_attributes(l_counter).original_source_line_id,p_changed_attributes(l_counter).source_line_id);
475 ELSE
476 l_source_line_id := p_changed_attributes(l_counter).source_line_id;
477 END IF;
478
479 open det_org_cur( l_source_line_id);
480 --bug 2320115 fixed
481
482 Fetch det_org_cur into l_organization_id, l_delivery_detail_id;
483
484 IF ( det_org_cur%FOUND ) THEN
485 close det_org_cur;
486 l_wh_type := WSH_EXTERNAL_INTERFACE_SV.Get_Warehouse_Type(p_organization_id => l_organization_id,
487 x_return_status => l_return_status,
488 p_delivery_detail_id => l_delivery_detail_id);
489
490 IF l_debug_on THEN
491 wsh_debug_sv.log(l_module_name,'l_wh_type,l_return_status',l_wh_type||','||l_return_status);
492 END IF;
493
494 IF ( l_return_status <> wsh_util_core.g_ret_sts_success ) THEN
495 raise wsh_invalid_org;
496 END IF;
497 -- if the warehouse is either a Third Party Warehouse or a Carrier Manifesting System.
498 IF ( nvl(l_wh_type, FND_API.G_MISS_CHAR) IN ( 'TPW', 'CMS' )) THEN
499 open del_cur(l_source_line_id);
500 FETCH del_cur into l_dCount;
501 close del_cur;
502 IF l_dCount > 0 THEN
503 raise wsh_update_not_allowed;
504 END IF;
505 END IF;
506 END IF;
507 IF det_org_cur%ISOPEN THEN
508 close det_org_cur;
509 END IF;
510 END IF;
511 END LOOP;
512 END IF;
513 IF l_return_status = wsh_util_core.g_ret_sts_success THEN
514 x_update_allowed := 'Y';
515 END IF;
516 x_return_status := l_return_status;
517 IF l_debug_on THEN
518 wsh_debug_sv.pop (l_module_name);
519 END IF;
520 EXCEPTION
521 WHEN wsh_update_not_allowed THEN
522 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
523 FND_MESSAGE.SET_NAME('WSH', 'WSH_UPDATE_NOT_ALLOWED');
524 WSH_UTIL_CORE.Add_Message(x_return_status,l_module_name);
525 x_update_allowed := 'N';
526 IF l_debug_on THEN
527 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_update_not_allowed exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
528 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_update_not_allowed');
529 END IF;
530 WHEN wsh_invalid_org THEN
531 x_return_status := l_return_status;
532 x_update_allowed := 'N';
533 IF l_debug_on THEN
534 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_invalid_org exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
535 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_invalid_org');
536 END IF;
537 WHEN OTHERS THEN
538 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
539 x_update_allowed := 'N';
540 WSH_UTIL_CORE.DEFAULT_HANDLER('WSH_DELIVERY_UTIL.Check_Updates_Allowed',l_module_name);
541 IF l_debug_on THEN
542 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
543 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
544 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
545 END IF;
546 END Check_Updates_Allowed;
547
548 PROCEDURE Check_Actions_Allowed(x_entity_ids IN OUT NOCOPY WSH_UTIL_CORE.Id_Tab_Type,
549 p_entity_name IN VARCHAR2,
550 p_action IN VARCHAR2,
551 p_delivery_id IN NUMBER,
552 x_err_entity_ids OUT NOCOPY WSH_UTIL_CORE.Id_Tab_Type,
553 x_return_status OUT NOCOPY VARCHAR2
554 )
555 IS
556
557 -- Cursors get the delivery status based on the entity_ids
558
559 cursor trip_to_del_cur(p_trip_id IN NUMBER) is
560 select wnd.organization_id,
561 wnd.status_code,
562 wnd.planned_flag,
563 wnd.delivery_id
564 from wsh_new_deliveries wnd,
565 wsh_delivery_legs wdl,
566 wsh_trip_stops wts1,
567 wsh_trip_stops wts2,
568 wsh_trips wt
569 where wnd.delivery_id = wdl.delivery_id
570 and wts1.stop_id = wdl.PICK_UP_STOP_ID
571 and wts2.stop_id = wdl.DROP_OFF_STOP_ID
572 and wts1.trip_id = wt.trip_id
573 and wts2.trip_id = wt.trip_id
574 and wt.trip_id = p_trip_id
575 and rownum = 1;
576
577 cursor stop_to_del_cur( p_stop_id IN NUMBER ) is
578 select wnd.organization_id,
579 wnd.status_code,
580 wnd.planned_flag,
581 wnd.delivery_id
582 from wsh_new_deliveries wnd,
583 wsh_delivery_legs wdl
584 where wnd.delivery_id = wdl.delivery_id
585 and wdl.pick_up_stop_id = p_stop_id
586 and rownum = 1;
587
588
589 cursor det_to_del_cur( p_del_det_id IN NUMBER ) is
590 select wnd.organization_id,
591 wnd.status_code,
592 wnd.planned_flag,
593 wnd.delivery_id
594 from wsh_new_deliveries wnd,
595 wsh_delivery_assignments_v wda
596 where wnd.delivery_id = wda.delivery_id
597 and wda.delivery_detail_id = p_del_det_id;
598
599 cursor del_cur( p_delivery_id IN NUMBER ) is
600 select organization_id,
601 status_code,
602 planned_flag
603 from wsh_new_deliveries
604 where delivery_id=p_delivery_id;
605
606
607 -- Cursor to get the org_id for delivery_detail_id
608 -- Cursor to check if the Instance is a Third Party Warehouse Instance
609 cursor det_cur(p_del_det_id IN NUMBER ) is
610 select organization_id,
611 source_code,
612 container_flag
613 from wsh_delivery_details
614 where delivery_detail_id = p_del_det_id;
615
616
617 cursor del_to_det_cur( p_delivery_id IN NUMBER ) is
618 select distinct 'X'
619 from wsh_delivery_details wdd,
620 wsh_delivery_assignments_v wda
621 where wda.delivery_id = p_delivery_id
622 and wdd.delivery_detail_id = wda.delivery_detail_id
623 and wdd.source_code = 'WSH'
624 and wdd.container_flag = 'N';
625
626 --performance fix - no need to join to wnd
627 cursor stop_to_det_cur( p_stop_id IN NUMBER ) is
628 select 'X'
629 from wsh_delivery_details wdd,
630 wsh_delivery_assignments_v wda,
631 wsh_delivery_legs wdl
632 where wdl.pick_up_stop_id = p_stop_id
633 and wda.delivery_id is not null
634 and wda.delivery_id = wdl.delivery_id
635 and wdd.delivery_detail_id = wda.delivery_detail_id
636 and wdd.source_code = 'WSH'
637 and wdd.container_flag = 'N'
638 and rownum=1;
639
640 cursor trip_to_det_cur( p_trip_id IN NUMBER ) is
641 select distinct 'X'
642 from wsh_delivery_details wdd,
643 wsh_new_deliveries wnd,
644 wsh_delivery_assignments_v wda,
645 wsh_delivery_legs wdl,
646 wsh_trip_stops wts1,
647 wsh_trip_stops wts2,
648 wsh_trips wt
649 where wt.trip_id = p_trip_id
650 and wts1.trip_id = wt.trip_id
651 and wts2.trip_id = wt.trip_id
652 and wts1.stop_id = wdl.pick_up_stop_id
653 and wts2.stop_id = wdl.drop_off_stop_id
654 and wnd.delivery_id = wdl.delivery_id
655 and wda.delivery_id = wnd.delivery_id
656 and wdd.delivery_detail_id = wda.delivery_detail_id
657 and wdd.source_code = 'WSH'
658 and wdd.container_flag = 'N';
659
660 cursor det_stat_cur( p_delivery_id IN NUMBER) is
661 select distinct 'X'
662 from wsh_delivery_details wdd,
663 wsh_delivery_assignments_v wda
664 where wdd.source_code = 'WSH'
665 and wdd.container_flag = 'N'
666 and wdd.delivery_detail_id = wda.delivery_detail_id
667 and wda.delivery_id = p_delivery_id;
668
669 cursor valid_shpmnt_advice_cur(p_delivery_id IN NUMBER,
670 p_tp_id IN NUMBER
671 ) is
672 select 'X'
673 from wsh_transactions_history
674 where transaction_id = (
675 select max(transaction_id)
676 from wsh_transactions_history wth,
677 wsh_new_deliveries wnd
678 where wth.entity_number = wnd.name
679 and wth.trading_partner_id = p_tp_id
680 and wnd.delivery_id = p_delivery_id
681 )
682 and document_direction='I'
683 and action_type = 'A';
684
685
686 l_entity_ids WSH_UTIL_CORE.Id_Tab_Type;
687 l_err_entity_ids WSH_UTIL_CORE.Id_Tab_Type;
688
689 l_organization_id NUMBER;
690 l_delivery_id NUMBER;
691 l_delivery_detail_id NUMBER;
692 l_planned_flag VARCHAR2(1);
693 l_status_code VARCHAR2(2);
694 l_source_code VARCHAR2(30);
695 l_cnt_flag VARCHAR2(2);
696 l_counter NUMBER;
697 l_wh_type VARCHAR2(30);
698
699 l_tpw_temp VARCHAR2(1);
700 l_atd_tpw_temp VARCHAR2(1);
701 l_valid_shpt_advc_tmp VARCHAR2(1);
702 l_return_status VARCHAR2(1);
703
704 wsh_delivery_locked EXCEPTION;
705 --
706 l_debug_on BOOLEAN;
707 --
708 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CHECK_ACTIONS_ALLOWED';
709 --
710 BEGIN
711 --
712 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
713 --
714 IF l_debug_on IS NULL
715 THEN
716 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
717 END IF;
718 --
719 IF l_debug_on THEN
720 wsh_debug_sv.push(l_module_name, 'Check_Actions_Allowed');
721 wsh_debug_sv.log (l_module_name, 'Entity Name',p_entity_name);
722 wsh_debug_sv.log (l_module_name, 'Action Code',p_action);
723 END IF;
724
725 IF ( p_action = 'ASSIGN_TO_DELIVERY') THEN
726 open det_stat_cur(p_delivery_id);
727 Fetch det_stat_cur into l_atd_tpw_temp;
728 close det_stat_cur;
729 IF l_atd_tpw_temp is not null THEN
730 raise wsh_delivery_locked;
731 ELSE
732 open del_cur(p_delivery_id);
733 Fetch del_cur into l_organization_id, l_status_code, l_planned_flag;
734 close del_cur;
735 IF l_debug_on THEN
736 wsh_debug_sv.log (l_module_name, 'Organization_id',l_organization_id);
737 wsh_debug_sv.log (l_module_name, 'Status Code',l_status_code);
738 END IF;
739
740 IF ( l_status_code IN ('SR','SC') and l_planned_flag IN ('Y','F') ) THEN
741 raise wsh_delivery_locked;
742 ELSE
743 l_entity_ids := x_entity_ids;
744 END IF;
745 END IF;
746 ELSIF (p_entity_name='DLVB' and p_action IN ('FREIGHT_COSTS','CREATE_CONTAINERS', 'CALC_WT_VOL',
747 'RATE_WITH_UPS', 'UPS_TIME_IN_TRANSIT','UPS_ADDRESS_VALIDATION', 'UPS_TRACKING')) THEN
748 l_entity_ids := x_entity_ids;
749 ELSIF (p_entity_name='DLVY' and p_action IN ('FREIGHT_COSTS', 'CALC_WT_VOL', 'RATE_WITH_UPS',
750 'UPS_TIME_IN_TRANSIT','UPS_ADDRESS_VALIDATION', 'UPS_TRACKING', 'UNASSIGN_FROM_TRIP',
751 'PRINT_DOC_SET','CLOSE') ) THEN
752 l_entity_ids := x_entity_ids;
753 ELSIF (p_entity_name='STOP' and p_action IN ('FREIGHT_COSTS','CALC_WT_VOL','PRINT_DOC_SET',
754 'UPDATE_STATUS')) THEN
755 l_entity_ids := x_entity_ids;
756 ELSIF (p_entity_name='TRIP' and p_action IN ('PLAN', 'UNPLAN', 'FREIGHT_COSTS','CALC_WT_VOL',
757 'PRINT_DOC_SET')) THEN
758 l_entity_ids := x_entity_ids;
759 ELSE
760 FOR l_counter IN x_entity_ids.FIRST..x_entity_ids.LAST LOOP
761 IF ( p_entity_name = 'TRIP') THEN
762 IF ( p_action = 'LAUNCH_PICK_RELEASE') THEN
763
764 open trip_to_del_cur(x_entity_ids(l_counter));
765 fetch trip_to_del_cur into l_organization_id, l_status_code, l_planned_flag,l_delivery_id;
766 close trip_to_del_cur;
767
768 IF l_debug_on THEN
769 wsh_debug_sv.log (l_module_name, 'Organization Id',l_organization_id);
770 wsh_debug_sv.log (l_module_name, 'Status Code',l_status_code);
771 wsh_debug_sv.log (l_module_name, 'Planned Flag',l_planned_flag);
772 wsh_debug_sv.log (l_module_name, 'Delivery Id',l_delivery_id);
773 END IF;
774
775 IF ( l_status_code IN ('SR', 'SC') AND l_planned_flag IN ('Y', 'F') ) THEN
776 l_err_entity_ids(l_err_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
777 ELSE
778 l_wh_type := WSH_EXTERNAL_INTERFACE_SV.Get_Warehouse_Type(p_organization_id => l_organization_id,
779 x_return_status => l_return_status,
780 p_delivery_id => l_delivery_id,
781 p_msg_display => 'N');
782 IF l_debug_on THEN
783 wsh_debug_sv.log (l_module_name, 'l_wh_type,l_return_status',l_wh_type||','||l_return_status);
784 END IF;
785
786 IF ( nvl(l_wh_type, FND_API.G_MISS_CHAR) = 'TPW' ) THEN
787 open trip_to_det_cur(x_entity_ids(l_counter));
788 Fetch trip_to_det_cur into l_tpw_temp;
789 close trip_to_det_cur;
790 IF ( l_tpw_temp is null ) THEN
791 l_err_entity_ids(l_err_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
792 ELSE
793 l_entity_ids(l_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
794 END IF;
795 ELSE
796 l_entity_ids(l_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
797 END IF;
798 END IF;
799 ELSE
800 l_entity_ids(l_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
801 END IF;
802
803 ELSIF ( p_entity_name = 'STOP' ) THEN
804
805 IF ( p_action ='LAUNCH_PICK_RELEASE') THEN
806
807 open stop_to_del_cur(x_entity_ids(l_counter));
808 fetch stop_to_del_cur into l_organization_id, l_status_code, l_planned_flag, l_delivery_id;
809 close stop_to_del_cur;
810
811 IF l_debug_on THEN
812 wsh_debug_sv.log (l_module_name, 'Organization Id',l_organization_id);
813 wsh_debug_sv.log (l_module_name, 'Status Code',l_status_code);
814 wsh_debug_sv.log (l_module_name, 'Planned Flag',l_planned_flag);
815 wsh_debug_sv.log (l_module_name, 'Delivery Id',l_delivery_id);
816 END IF;
817
818 IF ( l_status_code IN ('SR', 'SC') AND l_planned_flag IN ('Y', 'F') ) THEN
819 l_err_entity_ids(l_err_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
820 ELSE
821 l_wh_type := WSH_EXTERNAL_INTERFACE_SV.Get_Warehouse_Type(p_organization_id => l_organization_id,
822 x_return_status => l_return_status,
823 p_delivery_id => l_delivery_id,
824 p_msg_display => 'N');
825
826 IF l_debug_on THEN
827 wsh_debug_sv.log (l_module_name, 'l_wh_type,l_return_status',l_wh_type||','||l_return_status);
828 END IF;
829
830 IF ( nvl(l_wh_type, FND_API.G_MISS_CHAR) = 'TPW' ) THEN
831 open stop_to_det_cur(x_entity_ids(l_counter));
832 Fetch stop_to_det_cur into l_tpw_temp;
833 close stop_to_det_cur;
834 IF ( l_tpw_temp is null ) THEN
835 l_err_entity_ids(l_err_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
836 ELSE
837 l_entity_ids(l_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
838 END IF;
839 ELSE
840 l_entity_ids(l_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
841 END IF;
842 END IF;
843 ELSIF ( p_action IN ('PLAN', 'UNPLAN' )) THEN
844 open stop_to_det_cur(x_entity_ids(l_counter));
845 Fetch stop_to_det_cur into l_tpw_temp;
846 close stop_to_det_cur;
847 IF ( l_tpw_temp is not null ) THEN
848 l_entity_ids(l_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
849 ELSE
850 open stop_to_del_cur(x_entity_ids(l_counter));
851 fetch stop_to_del_cur into l_organization_id, l_status_code, l_planned_flag, l_delivery_id;
852 close stop_to_del_cur;
853
854 IF l_debug_on THEN
855 wsh_debug_sv.log (l_module_name, 'Organization Id',l_organization_id);
856 wsh_debug_sv.log (l_module_name, 'Status Code',l_status_code);
857 wsh_debug_sv.log (l_module_name, 'Planned Flag',l_planned_flag);
858 wsh_debug_sv.log (l_module_name, 'Delivery Id',l_delivery_id);
859 END IF;
860
861 IF ( l_status_code IN ('SR', 'SC') AND l_planned_flag IN ('Y','F') ) THEN
862 l_err_entity_ids(l_err_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
863 ELSE
864 l_wh_type := WSH_EXTERNAL_INTERFACE_SV.Get_Warehouse_Type(p_organization_id => l_organization_id,
865 x_return_status => l_return_status,
866 p_delivery_id => l_delivery_id);
867 IF l_debug_on THEN
868 wsh_debug_sv.log (l_module_name, 'l_wh_type,l_return_status',l_wh_type||','||l_return_status);
869 END IF;
870
871 IF ( nvl(l_wh_type, FND_API.G_MISS_CHAR) IN ('TPW','CMS') ) THEN
872 l_err_entity_ids(l_err_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
873 ELSE
874 l_entity_ids(l_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
875 END IF;
876 END IF;
877 END IF;
878 ELSE
879 l_entity_ids(l_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
880 END IF;
881
882
883 ELSIF ( p_entity_name = 'DLVY' ) THEN
884
885 open del_cur(x_entity_ids(l_counter));
886 Fetch del_cur into l_organization_id, l_status_code, l_planned_flag;
887 close del_cur;
888
889 IF l_debug_on THEN
890 wsh_debug_sv.log (l_module_name, 'Organization Id',l_organization_id);
891 wsh_debug_sv.log (l_module_name, 'Status Code',l_status_code);
892 wsh_debug_sv.log (l_module_name, 'Planned Flag',l_planned_flag);
893 END IF;
894
895 IF ( p_action IN ('LAUNCH_PICK_RELEASE','AUTO_PACK','AUTO_PACK_MASTER','PACK') ) THEN
896
897 IF ( l_status_code IN ('SR', 'SC') AND l_planned_flag IN ('Y','F') ) THEN
898 l_err_entity_ids(l_err_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
899 ELSE
900 l_wh_type := WSH_EXTERNAL_INTERFACE_SV.Get_Warehouse_Type(p_organization_id => l_organization_id,
901 x_return_status => l_return_status,
902 p_delivery_id => x_entity_ids(l_counter),
903 p_msg_display => 'N');
904 IF l_debug_on THEN
905 wsh_debug_sv.log (l_module_name, 'l_wh_type,l_return_status',l_wh_type||','||l_return_status);
906 END IF;
907 IF ( nvl(l_wh_type, FND_API.G_MISS_CHAR) = 'TPW' ) THEN
908 open del_to_det_cur(x_entity_ids(l_counter));
909 Fetch del_to_det_cur into l_tpw_temp;
910 close del_to_det_cur;
911 IF ( l_tpw_temp is null ) THEN
912 l_err_entity_ids(l_err_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
913 ELSE
914 l_entity_ids(l_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
915 END IF;
916 ELSE
917 l_entity_ids(l_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
918 END IF;
919 END IF;
920 ELSIF ( p_action IN ('PLAN','UNPLAN') ) THEN
921 IF ( l_status_code IN ('SR', 'SC') AND l_planned_flag IN ('Y','F') ) THEN
922 l_err_entity_ids(l_err_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
923 ELSE
924 l_entity_ids(l_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
925 END IF;
926 ELSIF ( p_action ='GEN_LOAD_SEQ' ) THEN
927 IF ( l_status_code IN ('SR', 'SC') AND l_planned_flag IN ('Y','F') ) THEN
928 l_wh_type := WSH_EXTERNAL_INTERFACE_SV.Get_Warehouse_Type(p_organization_id => l_organization_id,
929 x_return_status => l_return_status,
930 p_delivery_id => x_entity_ids(l_counter),
931 p_msg_display => 'N');
932 IF l_debug_on THEN
933 wsh_debug_sv.log (l_module_name, 'l_wh_type,l_return_status',l_wh_type||','||l_return_status);
934 END IF;
935 IF ( nvl(l_wh_type, FND_API.G_MISS_CHAR) = 'TPW' ) THEN
936 l_err_entity_ids(l_err_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
937 ELSE
938 l_entity_ids(l_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
939 END IF;
940 ELSE
941 l_entity_ids(l_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
942 END IF;
943 ELSIF ( p_action = 'REOPEN' ) THEN
944 IF ( l_status_code = 'CO' ) THEN
945 open del_to_det_cur(x_entity_ids(l_counter));
946 Fetch del_to_det_cur into l_tpw_temp;
947 close del_to_det_cur;
948 IF ( l_tpw_temp is null ) THEN
949 l_wh_type := WSH_EXTERNAL_INTERFACE_SV.Get_Warehouse_Type(p_organization_id => l_organization_id,
950 x_return_status => l_return_status,
951 p_delivery_id => x_entity_ids(l_counter),
952 p_msg_display => 'N');
953 IF l_debug_on THEN
954 wsh_debug_sv.log (l_module_name, 'l_wh_type,l_return_status',l_wh_type||','||l_return_status);
955 END IF;
956 IF ( nvl(l_wh_type, FND_API.G_MISS_CHAR) = 'CMS' ) THEN
957 l_err_entity_ids(l_err_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
958 ELSE
959 l_entity_ids(l_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
960 END IF;
961 l_entity_ids(l_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
962 ELSE
963 l_err_entity_ids(l_err_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
964 END IF;
965 ELSE
966 l_wh_type := WSH_EXTERNAL_INTERFACE_SV.Get_Warehouse_Type(p_organization_id => l_organization_id,
967 x_return_status => l_return_status,
968 p_delivery_id => x_entity_ids(l_counter),
969 p_msg_display => 'N');
970 IF l_debug_on THEN
971 wsh_debug_sv.log (l_module_name, 'l_wh_type,l_return_status',l_wh_type||','||l_return_status);
972 END IF;
973 IF ( nvl(l_wh_type, FND_API.G_MISS_CHAR) = 'CMS' ) THEN
974 l_err_entity_ids(l_err_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
975 ELSE
976 l_entity_ids(l_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
977 END IF;
978 END IF;
979 ELSIF ( p_action ='SHIP_CONFIRM' ) THEN
980 IF ( l_status_code IN ( 'SR', 'SC' )) THEN
981 l_err_entity_ids(l_err_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
982 ELSE
983 l_wh_type := WSH_EXTERNAL_INTERFACE_SV.Get_Warehouse_Type(p_organization_id => l_organization_id,
984 x_return_status => l_return_status,
985 p_delivery_id => x_entity_ids(l_counter),
986 p_msg_display => 'N');
987 IF l_debug_on THEN
988 wsh_debug_sv.log (l_module_name, 'l_wh_type,l_return_status',l_wh_type||','||l_return_status);
989 END IF;
990 IF ( nvl(l_wh_type, FND_API.G_MISS_CHAR) = 'CMS' ) THEN
991 open del_to_det_cur(x_entity_ids(l_counter));
992 Fetch del_to_det_cur into l_tpw_temp;
993 close del_to_det_cur;
994 IF ( l_tpw_temp IS NULL ) THEN
995 IF ( l_status_code = 'OP' ) THEN
996 open valid_shpmnt_advice_cur(x_entity_ids(l_counter), l_organization_id);
997 fetch valid_shpmnt_advice_cur into l_valid_shpt_advc_tmp;
998 close valid_shpmnt_advice_cur;
999 IF ( l_valid_shpt_advc_tmp IS NOT NULL ) THEN
1000 l_entity_ids(l_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
1001 ELSE
1002 l_err_entity_ids(l_err_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
1003 END IF;
1004 ELSE
1005 l_entity_ids(l_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
1006 END IF;
1007 ELSE
1008 l_entity_ids(l_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
1009 END IF;
1010 ELSE
1011 l_entity_ids(l_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
1012 END IF;
1013 END IF;
1014 ELSIF ( p_action IN ('OUTBOUND_DOCUMENT','TRANSACTION_HISTORY') ) THEN
1015 l_wh_type := WSH_EXTERNAL_INTERFACE_SV.Get_Warehouse_Type(p_organization_id => l_organization_id,
1016 x_return_status => l_return_status,
1017 p_delivery_id => x_entity_ids(l_counter));
1018 IF l_debug_on THEN
1019 wsh_debug_sv.log (l_module_name, 'l_wh_type,l_return_status',l_wh_type||','||l_return_status);
1020 END IF;
1021 IF ( nvl(l_wh_type, FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR ) THEN
1022 open det_stat_cur(x_entity_ids(l_counter));
1023 fetch det_stat_cur into l_atd_tpw_temp;
1024 close det_stat_cur;
1025 IF ( l_atd_tpw_temp IS NULL ) THEN
1026 l_err_entity_ids(l_err_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
1027 ELSE
1028 l_entity_ids(l_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
1029 END IF;
1030 ELSIF ( nvl(l_wh_type, FND_API.G_MISS_CHAR) IN ('CMS','TPW') ) THEN
1031 IF ( l_status_code = 'OP' ) THEN
1032 open valid_shpmnt_advice_cur(x_entity_ids(l_counter), l_organization_id);
1033 fetch valid_shpmnt_advice_cur into l_valid_shpt_advc_tmp;
1034 close valid_shpmnt_advice_cur;
1035 IF ( l_valid_shpt_advc_tmp IS NULL ) THEN
1036 l_entity_ids(l_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
1037 ELSE
1038 l_err_entity_ids(l_err_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
1039 END IF;
1040 ELSE
1041 l_entity_ids(l_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
1042 END IF;
1043 ELSE
1044 l_err_entity_ids(l_err_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
1045 END IF;
1046 ELSIF ( p_action IN ('AUTO_CREATE_TRIP','ASSIGN_TO_TRIP')) THEN
1047 IF ( l_status_code = 'SA' ) THEN
1048 l_entity_ids(l_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
1049 ELSE
1050 l_wh_type := WSH_EXTERNAL_INTERFACE_SV.Get_Warehouse_Type(p_organization_id => l_organization_id,
1051 x_return_status => l_return_status,
1052 p_delivery_id => x_entity_ids(l_counter),
1053 p_msg_display => 'N');
1054
1055 IF l_debug_on THEN
1056 wsh_debug_sv.log (l_module_name, 'l_wh_type,l_return_status',l_wh_type||','||l_return_status);
1057 END IF;
1058 IF ( nvl(l_wh_type, FND_API.G_MISS_CHAR) in ( 'CMS','TPW') ) THEN
1059 open del_to_det_cur(x_entity_ids(l_counter));
1060 fetch del_to_det_cur into l_tpw_temp;
1061 close del_to_det_cur;
1062 IF ( l_tpw_temp IS NULL ) THEN
1063 l_err_entity_ids(l_err_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
1064 ELSE
1065 l_entity_ids(l_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
1066 END IF;
1067 ELSE
1068 l_entity_ids(l_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
1069 END IF;
1070 END IF;
1071 ELSIF ( p_action IN ('GENERATE_BOL','GENERATE_PS')) THEN
1072 open det_stat_cur(x_entity_ids(l_counter));
1073 Fetch det_stat_cur into l_atd_tpw_temp;
1074 close det_stat_cur;
1075 IF ( l_atd_tpw_temp is not null ) THEN
1076 l_entity_ids(l_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
1077 --raise wsh_delivery_locked;
1078 ELSE
1079 open del_cur(p_delivery_id);
1080 Fetch del_cur into l_organization_id, l_status_code, l_planned_flag;
1081 close del_cur;
1082 IF l_debug_on THEN
1083 wsh_debug_sv.log (l_module_name, 'Organization_id',l_organization_id);
1084 wsh_debug_sv.log (l_module_name, 'Status Code',l_status_code);
1085 END IF;
1086 IF ( l_status_code IN ('SR', 'SC') AND l_planned_flag IN ('Y','F') ) THEN
1087 raise wsh_delivery_locked;
1088 ELSE
1089 l_wh_type := WSH_EXTERNAL_INTERFACE_SV.Get_Warehouse_Type(p_organization_id => l_organization_id,
1090 x_return_status => l_return_status,
1091 p_delivery_id => x_entity_ids(l_counter),
1092 p_msg_display => 'N');
1093 IF l_debug_on THEN
1094 wsh_debug_sv.log (l_module_name, 'l_wh_type,l_return_status',l_wh_type||','||l_return_status);
1095 END IF;
1096 IF ( nvl(l_wh_type, FND_API.G_MISS_CHAR) IN ('TPW','CMS') ) THEN
1097 raise wsh_delivery_locked;
1098 ELSE
1099 l_entity_ids := x_entity_ids;
1100 END IF;
1101 END IF;
1102 END IF;
1103 ELSE
1104 l_entity_ids(l_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
1105 END IF;
1106
1107 ELSIF ( p_entity_name = 'DLVB' ) THEN
1108
1109 open det_to_del_cur(x_entity_ids(l_counter));
1110 Fetch det_to_del_cur into l_organization_id, l_status_code, l_planned_flag, l_delivery_id;
1111
1112 IF l_debug_on THEN
1113 wsh_debug_sv.log (l_module_name, 'Organization Id',l_organization_id);
1114 wsh_debug_sv.log (l_module_name, 'Status Code',l_status_code);
1115 wsh_debug_sv.log (l_module_name, 'Planned Flag',l_planned_flag);
1116 wsh_debug_sv.log (l_module_name, 'Delivery Id',l_delivery_id);
1117 END IF;
1118
1119 IF ( p_action IN ('CYCLE_COUNT','LAUNCH_PICK_RELEASE','AUTO_PACK','AUTO_PACK_MASTER',
1120 'PACK','UNPACK','PACKING_WORKBENCH') ) THEN
1121 IF ( det_to_del_cur%NOTFOUND ) THEN
1122 close det_to_del_cur;
1123 open det_cur(x_entity_ids(l_counter));
1124 Fetch det_cur into l_organization_id, l_source_code, l_cnt_flag;
1125 close det_cur;
1126 l_wh_type := WSH_EXTERNAL_INTERFACE_SV.Get_Warehouse_Type(p_organization_id => l_organization_id,
1127 x_return_status => l_return_status,
1128 p_delivery_id => l_delivery_id,
1129 p_delivery_detail_id => x_entity_ids(l_counter),
1130 p_msg_display => 'N');
1131 IF l_debug_on THEN
1132 wsh_debug_sv.log (l_module_name, 'l_wh_type,l_return_status',l_wh_type||','||l_return_status);
1133 END IF;
1134 IF ( nvl(l_wh_type, FND_API.G_MISS_CHAR) = 'TPW' ) THEN
1135 IF ( l_source_code = 'WSH' and l_cnt_flag = 'N' ) THEN
1136 l_entity_ids(l_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
1137 ELSE
1138 l_err_entity_ids(l_err_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
1139 END IF;
1140 ELSE
1141 l_entity_ids(l_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
1142 END IF;
1143 ELSE
1144 IF ( l_status_code IN ('SR', 'SC') AND l_planned_flag IN ('Y','F') ) THEN
1145 l_err_entity_ids(l_err_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
1146 ELSE
1147 l_wh_type := WSH_EXTERNAL_INTERFACE_SV.Get_Warehouse_Type(p_organization_id => l_organization_id,
1148 x_return_status => l_return_status,
1149 p_delivery_id => l_delivery_id,
1150 p_delivery_detail_id => x_entity_ids(l_counter),
1151 p_msg_display => 'N');
1152 IF l_debug_on THEN
1153 wsh_debug_sv.log (l_module_name, 'l_wh_type,l_return_status',l_wh_type||','||l_return_status);
1154 END IF;
1155 IF ( nvl(l_wh_type, FND_API.G_MISS_CHAR) = 'TPW' ) THEN
1156 open det_cur(x_entity_ids(l_counter));
1157 Fetch det_cur into l_organization_id, l_source_code, l_cnt_flag;
1158 close det_cur;
1159 IF ( l_source_code = 'WSH' and l_cnt_flag = 'N' ) THEN
1160 l_entity_ids(l_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
1161 ELSE
1162 l_err_entity_ids(l_err_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
1163 END IF;
1164 ELSE
1165 l_entity_ids(l_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
1166 END IF;
1167 END IF;
1168 END IF;
1169
1170 ELSIF ( p_action = 'SPLIT_LINE') THEN
1171 IF ( det_to_del_cur%NOTFOUND ) THEN
1172 close det_to_del_cur;
1173 l_entity_ids(l_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
1174 ELSIF ( nvl(l_status_code, FND_API.G_MISS_CHAR) IN ('SR', 'SC') AND nvl(l_planned_flag,FND_API.G_MISS_CHAR) IN ('Y','F') ) THEN
1175 l_err_entity_ids(l_err_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
1176 ELSE
1177 l_entity_ids(l_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
1178 END IF;
1179 ELSIF ( p_action = 'UNASSIGN_FROM_DELIVERY') THEN
1180 IF ( det_to_del_cur%NOTFOUND ) THEN
1181 close det_to_del_cur;
1182 l_entity_ids(l_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
1183 ELSIF ( nvl(l_status_code, FND_API.G_MISS_CHAR) IN ('SR', 'SC', 'SA') AND nvl(l_planned_flag,FND_API.G_MISS_CHAR) IN ('Y','F') ) THEN
1184 l_err_entity_ids(l_err_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
1185 ELSE
1186 open det_cur(x_entity_ids(l_counter));
1187 Fetch det_cur into l_organization_id, l_source_code, l_cnt_flag;
1188 close det_cur;
1189 IF ( l_source_code = 'WSH' and l_cnt_flag = 'N' ) THEN
1190 l_err_entity_ids(l_err_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
1191 ELSE
1192 l_entity_ids(l_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
1193 END IF;
1194 END IF;
1195 ELSIF ( p_action = 'AUTO_CREATE_TRIP') THEN
1196 IF ( det_to_del_cur%NOTFOUND ) THEN
1197 close det_to_del_cur;
1198 open det_cur(x_entity_ids(l_counter));
1199 Fetch det_cur into l_organization_id, l_source_code, l_cnt_flag;
1200 close det_cur;
1201 l_wh_type := WSH_EXTERNAL_INTERFACE_SV.Get_Warehouse_Type(p_organization_id => l_organization_id,
1202 x_return_status => l_return_status,
1203 p_delivery_id => l_delivery_id,
1204 p_delivery_detail_id => x_entity_ids(l_counter),
1205 p_msg_display => 'N');
1206 IF l_debug_on THEN
1207 wsh_debug_sv.log (l_module_name, 'l_wh_type,l_return_status',l_wh_type||','||l_return_status);
1208 END IF;
1209 IF ( nvl(l_wh_type, FND_API.G_MISS_CHAR) in ('CMS','TPW')) THEN
1210 open det_cur(x_entity_ids(l_counter));
1211 Fetch det_cur into l_organization_id, l_source_code, l_cnt_flag;
1212 close det_cur;
1213 IF ( l_source_code = 'WSH' and l_cnt_flag = 'N' ) THEN
1214 l_entity_ids(l_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
1215 ELSE
1216 l_err_entity_ids(l_err_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
1217 END IF;
1218 ELSE
1219 l_entity_ids(l_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
1220 END IF;
1221 ELSE
1222 l_err_entity_ids(l_err_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
1223 END IF;
1224 ELSE
1225 l_entity_ids(l_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
1226 END IF;
1227 IF ( det_to_del_cur%ISOPEN ) THEN
1228 close det_to_del_cur;
1229 END IF;
1230 ELSE
1231 l_entity_ids(l_entity_ids.COUNT + 1) := x_entity_ids(l_counter);
1232 END IF;
1233
1234 END LOOP;
1235 END IF;
1236
1237 IF ( l_entity_ids.COUNT = x_entity_ids.COUNT ) THEN
1238 l_return_status := wsh_util_core.g_ret_sts_success;
1239 ELSIF ( l_entity_ids.COUNT = 0 ) THEN
1240 raise wsh_delivery_locked;
1241 ELSIF ( l_entity_ids.COUNT < x_entity_ids.COUNT ) THEN
1242 x_entity_ids := l_entity_ids;
1243 x_err_entity_ids := l_err_entity_ids;
1244 l_return_status := wsh_util_core.g_ret_sts_warning;
1245 END IF;
1246 x_return_status := l_return_status;
1247 IF l_debug_on THEN
1248 wsh_debug_sv.pop (l_module_name);
1249 END IF;
1250 EXCEPTION
1251 WHEN wsh_delivery_locked THEN
1252 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1253 IF l_debug_on THEN
1254 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_delivery_locked exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1255 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_delivery_locked');
1256 END IF;
1257 WHEN others THEN
1258 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1259 IF l_debug_on THEN
1260 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
1261 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1262 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1263 END IF;
1264 END Check_Actions_Allowed;
1265
1266 END WSH_DELIVERY_UTIL;