[Home] [Help]
PACKAGE BODY: APPS.WSH_AUTO_MANIFEST
Source
1 PACKAGE BODY WSH_AUTO_MANIFEST as
2 /* $Header: WSHAUMNB.pls 120.3 2006/10/18 18:20:19 bsadri noship $ */
3
4 --
5 -- PROCEDURE: Submit
6 -- Purpose: Submit Automated Carrier Manifesting based on given criteria.
7 -- Description: This procedure is called by Concurrent Program to submit request for Automated
8 -- Carrier Manifesting. This works as a wrapper to the main procedure
9 -- Process_Auto_Manifest for Automated Carrier Manifesting.
10 --
11 --
12 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_AUTO_MANIFEST';
13 --
14 PROCEDURE Submit (
15 errbuf OUT NOCOPY VARCHAR2,
16 retcode OUT NOCOPY VARCHAR2,
17 -- K proj
18 p_doctype IN VARCHAR2,
19 p_shipment_type IN VARCHAR2,
20 p_set_org IN NUMBER,
21 p_organization_id IN NUMBER,
22 -- K proj
23 p_src_header_num_from IN VARCHAR2,
24 p_src_header_num_to IN VARCHAR2,
25 p_carrier_id IN NUMBER,
26 p_customer_id IN NUMBER,
27 p_customer_ship_to_id IN NUMBER,
28 p_scheduled_from_date IN VARCHAR2,
29 p_scheduled_to_date IN VARCHAR2,
30 p_set_auto_pack IN NUMBER,
31 p_autopack IN VARCHAR2,
32 p_log_level IN NUMBER
33 ) IS
34
35 l_return_status VARCHAR2(1);
36 l_temp BOOLEAN;
37 l_message_level NUMBER;
38 --K proj
39 l_warning_count NUMBER := 0;
40 l_error_count NUMBER := 0;
41 l_otm_installed VARCHAR2(1) ;
42
43 --
44 l_debug_on BOOLEAN;
45 --
46 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'SUBMIT';
47 --
48 BEGIN
49
50 --
51 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
52 --
53 IF l_debug_on IS NULL
54 THEN
55 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
56 END IF;
57 --
58 IF (p_doctype = 'SA') --{ K proj
59 OR
60 ((p_doctype = 'SR')
61 AND ((p_shipment_type IS NULL) OR (p_shipment_type = 'TPW') OR (p_shipment_type = 'BOTH'))) THEN
62 Process_Auto_Manifest (
63 p_organization_id => p_organization_id,
64 p_carrier_id => p_carrier_id,
65 p_customer_id => p_customer_id,
66 p_customer_ship_to_id => p_customer_ship_to_id,
67 p_scheduled_from_date => FND_DATE.canonical_to_date(p_scheduled_from_date),
68 p_scheduled_to_date => FND_DATE.canonical_to_date(p_scheduled_to_date),
69 p_autopack => p_autopack,
70 p_log_level => p_log_level,
71 x_return_status => l_return_status,
72 p_shipment_type => 'TPW',
73 p_doctype => p_doctype,
74 p_src_header_num_from => p_src_header_num_from,
75 p_src_header_num_to => p_src_header_num_to );
76
77 wsh_util_core.api_post_call
78 (
79 p_return_status => l_return_status,
80 x_num_warnings => l_warning_count,
81 x_num_errors => l_error_count,
82 p_raise_error_flag => FALSE
83 );
84
85 END IF; --}
86 IF ((p_doctype = 'SR')
87 AND ((p_shipment_type IS NULL) OR (p_shipment_type = 'CMS') OR
88 (p_shipment_type = 'BOTH'))) THEN --{
89 l_otm_installed := WSH_UTIL_CORE.GC3_Is_Installed;
90 IF l_otm_installed = 'N' THEN --{
91 Process_Auto_Manifest (
92 p_organization_id => p_organization_id,
93 p_carrier_id => p_carrier_id,
94 p_customer_id => p_customer_id,
95 p_customer_ship_to_id => p_customer_ship_to_id,
96 p_scheduled_from_date =>
97 FND_DATE.canonical_to_date(p_scheduled_from_date),
98 p_scheduled_to_date =>
99 FND_DATE.canonical_to_date(p_scheduled_to_date),
100 p_autopack => p_autopack,
101 p_log_level => p_log_level,
102 x_return_status => l_return_status,
103 p_shipment_type => 'CMS',
104 p_doctype => p_doctype,
105 p_src_header_num_from => p_src_header_num_from,
106 p_src_header_num_to => p_src_header_num_to );
107
108 wsh_util_core.api_post_call
109 (
110 p_return_status => l_return_status,
111 x_num_warnings => l_warning_count,
112 x_num_errors => l_error_count,
113 p_raise_error_flag => FALSE
114 );
115 END IF; --}
116 END IF; --}
117
118 IF l_error_count > 0
119 THEN
120 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
121 ELSIF l_warning_count > 0
122 THEN
123 l_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
124 ELSE
125 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
126 END IF;
127
128
129 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
130 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL', '');
131 --errbuf := 'Automated Carrier Manifesting is completed Successfully';
132 FND_MESSAGE.SET_NAME('WSH', 'WSH_MANIFEST_NORMAL');
133 errbuf := FND_MESSAGE.GET;
134 retcode := '0';
135 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
136 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS( 'WARNING', '');
137 --errbuf := 'Automated Carrier Manifesting is completed with Warning';
138 FND_MESSAGE.SET_NAME('WSH', 'WSH_MANIFEST_WARNING');
139 errbuf := FND_MESSAGE.GET;
140 retcode := '1';
141 ELSE
142 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', '');
143 --errbuf := 'Automated Carrier Manifesting submission is completed with Error';
144 FND_MESSAGE.SET_NAME('WSH', 'WSH_MANIFEST_ERROR');
145 errbuf := FND_MESSAGE.GET;
146 retcode := '2';
147 END IF;
148
149 EXCEPTION
150
151 WHEN others THEN
152 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', '');
153 --errbuf := 'Automated Carrier Manifesting submission is completed with Unexpected Error: '||sqlerrm;
154 FND_MESSAGE.SET_NAME('WSH', 'WSH_MANIFEST_UNEXP');
155 FND_MESSAGE.SET_TOKEN('ERR_MSG', sqlerrm);
156 errbuf := FND_MESSAGE.GET;
157 retcode := '2';
158
159 END Submit;
160
161
162 --
163 -- PROCEDURE : Process_Auto_Manifest
164 -- Description: This is the main procedure for Automated Carrier Manifesting System,
165 -- which is called by procedure submit.
166 --
167 PROCEDURE Process_Auto_Manifest (
168 p_organization_id IN NUMBER,
169 p_carrier_id IN NUMBER,
170 p_customer_id IN NUMBER,
171 p_customer_ship_to_id IN NUMBER,
172 p_scheduled_from_date IN DATE,
173 p_scheduled_to_date IN DATE,
174 p_autopack IN VARCHAR2 DEFAULT 'N',
175 p_log_level IN NUMBER DEFAULT 0,
176 x_return_status OUT NOCOPY VARCHAR2,
177 p_shipment_type IN VARCHAR2,
178 p_doctype IN VARCHAR2,
179 p_src_header_num_from IN VARCHAR2,
180 p_src_header_num_to IN VARCHAR2) IS
181
182 --k proj
183 CURSOR c_shipment_advice_del IS
184 SELECT distinct wnd.organization_id
185 ,wnd.delivery_id
186 ,wnd.carrier_id
187 ,wnd.customer_id
188 ,wnd.ultimate_dropoff_location_id ship_to_id
189 FROM wsh_new_deliveries wnd
190 , wsh_transactions_history wth
191 , wsh_delivery_assignments_v wdav
192 , wsh_delivery_details wdd
193 WHERE nvl(wnd.SHIPMENT_DIRECTION , 'O') IN ('O', 'IO')
194 AND wdav.delivery_id = wnd.delivery_id
195 AND wdd.delivery_detail_id = wdav.delivery_detail_id
196 AND ((p_src_header_num_from IS NOT NULL
197 AND wdd.source_header_number >= p_src_header_num_from)
198 OR (p_src_header_num_from IS NULL))
199 AND ((p_src_header_num_to IS NOT NULL
200 AND wdd.source_header_number <= p_src_header_num_to)
201 OR (p_src_header_num_to IS NULL))
202 AND wnd.status_code in ('CL', 'IT', 'CO')
203 AND wnd.DELIVERY_TYPE <> 'CONSOLIDATION'
204 AND ( ( p_organization_id is not null
205 AND wnd.organization_id = p_organization_id)
206 OR (P_organization_id is null))
207 AND (( p_carrier_id is null )
208 OR ( p_carrier_id is not null AND wnd.carrier_id = p_carrier_id))
209 AND ((p_customer_id IS NULL)
210 OR ((p_customer_id IS NOT NULL) AND (wnd.customer_id = p_customer_id)))
211 AND wnd.ultimate_dropoff_location_id =
212 nvl(p_customer_ship_to_id,ultimate_dropoff_location_id)
213 AND ((p_scheduled_from_date IS NULL )
214 OR (wnd.confirm_date >= p_scheduled_from_date))
215 AND ((p_scheduled_to_date IS NULL)
216 OR (wnd.confirm_date <= p_scheduled_to_date))
217 AND wth.entity_number = wnd.name
218 AND wth.document_direction = 'I'
219 AND wth.document_type = 'SR'
220 AND wth.entity_type = 'DLVY'
221 AND NOT EXISTS (
222 SELECT entity_number
223 from wsh_transactions_history wth2
224 WHERE wth2.entity_number = wnd.name
225 AND wth2.document_type = 'SA'
226 ) ;
227
228 CURSOR c_sr_cms_del IS
229 SELECT distinct wnd.organization_id
230 ,wnd.delivery_id
231 ,wnd.carrier_id
232 ,wnd.customer_id
233 ,wnd.ultimate_dropoff_location_id ship_to_id
234 FROM wsh_delivery_details wdd,
235 wsh_delivery_assignments_v wda,
236 wsh_new_deliveries wnd,
237 mtl_parameters mtl,
238 wsh_carriers wc
239 WHERE wdd.delivery_detail_id = wda.delivery_detail_id
240 AND wda.delivery_id = wnd.delivery_id
241 AND wda.delivery_detail_id = wdd.delivery_detail_id
242 AND ((p_src_header_num_from IS NOT NULL
243 AND wdd.source_header_number >= p_src_header_num_from)
244 OR (p_src_header_num_from IS NULL))
245 AND ((p_src_header_num_to IS NOT NULL
246 AND wdd.source_header_number <= p_src_header_num_to)
247 OR (p_src_header_num_to IS NULL))
248 AND nvl(wnd.SHIPMENT_DIRECTION , 'O') IN ('O', 'IO')
249 AND wdd.container_flag = 'N'
250 AND wnd.organization_id = NVL(p_organization_id , wnd.organization_id)
251 AND wnd.organization_id= mtl.organization_id
252 AND wnd.DELIVERY_TYPE <> 'CONSOLIDATION'
253 AND mtl.CARRIER_MANIFESTING_FLAG = 'Y'
254 AND wdd.released_status in ('X','Y')
255 AND wnd.status_code='OP'
256 AND wc.carrier_id = wnd.carrier_id
257 AND wc.MANIFESTING_ENABLED_FLAG = 'Y'
258 AND NVL(p_carrier_id, wnd.carrier_id ) = wnd.carrier_id
259 AND wnd.customer_id = nvl(p_customer_id,wnd.customer_id)
260 AND wnd.ultimate_dropoff_location_id =
261 nvl(p_customer_ship_to_id,ultimate_dropoff_location_id)
262 AND wda.delivery_id IS NOT NULL
263 AND ((p_scheduled_from_date IS NULL )
264 OR (wnd.initial_pickup_date >= p_scheduled_from_date))
265 AND ((p_scheduled_to_date IS NULL)
266 OR ( wnd.initial_pickup_date <= p_scheduled_to_date)) ;
267
268 CURSOR c_sr_tpw_del IS
269 SELECT distinct wnd.organization_id,
270 wnd.delivery_id
271 ,wnd.carrier_id
272 ,wnd.customer_id
273 ,wnd.ultimate_dropoff_location_id ship_to_id
274 FROM wsh_delivery_details wdd,
275 wsh_delivery_assignments_v wda,
276 wsh_new_deliveries wnd,
277 mtl_parameters mtl
278 WHERE wdd.delivery_detail_id = wda.delivery_detail_id
279 AND wda.delivery_id = wnd.delivery_id
280 AND nvl(wnd.SHIPMENT_DIRECTION , 'O') IN ('O', 'IO')
281 AND wdd.container_flag = 'N'
282 AND ((p_src_header_num_from IS NOT NULL
283 AND wdd.source_header_number >= p_src_header_num_from)
284 OR (p_src_header_num_from IS NULL))
285 AND ((p_src_header_num_to IS NOT NULL
286 AND wdd.source_header_number <= p_src_header_num_to)
287 OR (p_src_header_num_to IS NULL))
288 AND wnd.organization_id = NVL(p_organization_id , wnd.organization_id)
289 AND wnd.organization_id= mtl.organization_id
290 AND mtl.DISTRIBUTED_ORGANIZATION_FLAG ='Y'
291 AND wdd.released_status in ('X','R','B')
292 AND wnd.status_code='OP'
293 AND wnd.DELIVERY_TYPE <> 'CONSOLIDATION'
294 AND ( ( p_carrier_id is null)
295 OR (p_carrier_id IS NOT NULL AND wnd.carrier_id = p_carrier_id))
296 AND wnd.customer_id = nvl(p_customer_id,wnd.customer_id)
297 AND wnd.ultimate_dropoff_location_id =
298 nvl(p_customer_ship_to_id,ultimate_dropoff_location_id)
299 AND wda.delivery_id IS NOT NULL
300 AND ((p_scheduled_from_date IS NULL )
301 OR (wnd.initial_pickup_date >= p_scheduled_from_date))
302 AND ((p_scheduled_to_date IS NULL)
303 OR ( wnd.initial_pickup_date <= p_scheduled_to_date));
304
305 CURSOR get_carrier_name(pc_carrier_id NUMBER) IS
306 SELECT party_name
307 FROM wsh_carriers, hz_parties
308 WHERE carrier_id =party_id (+)
309 AND carrier_id= pc_carrier_id;
310
311 CURSOR get_customer_name(pc_customer_id NUMBER) IS
312 SELECT HP.PARTY_NAME
313 FROM HZ_CUST_ACCOUNTS HCA, HZ_PARTIES HP
314 WHERE HP.PARTY_ID = HCA.PARTY_ID
315 AND HP.PARTY_ID = pc_customer_id;
316
317 /*Patchset I: Locations Project. Use ui_location_code from wsh_customer_locations_v */
318
319 CURSOR get_location(pc_location_id NUMBER) IS
323 WHERE wclv.wsh_location_id = pc_location_id
320 SELECT wclv.ui_location_code
321 FROM
322 wsh_customer_locations_v wclv
324 AND wclv.customer_status = 'A'
325 AND wclv.cust_acct_site_status = 'A'
326 AND wclv.site_use_status = 'A'
327 AND wclv.site_use_code = 'SHIP_TO';
328
329 l_carrier_name VARCHAR2(80);
330 l_customer_name VARCHAR2(80);
331 l_location VARCHAR2(200);
332 l_autopack VARCHAR2(5);
333
334 l_entity_ids WSH_UTIL_CORE.id_tab_type;
335 l_con_ids WSH_UTIL_CORE.id_tab_type;
336 l_err_entity_ids WSH_UTIL_CORE.id_tab_type;
337 l_cont_inst_tab WSH_UTIL_CORE.id_tab_type;
338 l_group_id_tab WSH_UTIL_CORE.id_tab_type;
339 l_success_delivery WSH_AUTO_MANIFEST.tab_delivery_msg;
340 l_success_count NUMBER := 0;
341 l_error_delivery WSH_AUTO_MANIFEST.tab_delivery_msg;
342 l_error_count NUMBER := 0;
343 l_warning_delivery WSH_AUTO_MANIFEST.tab_delivery_msg;
344 l_warning_count NUMBER := 0;
345 l_warning_index NUMBER;
346 l_delivery_count NUMBER := 0;
347
348 l_calling_api VARCHAR2(500);
349
350 l_delivery_status VARCHAR2(10):='SUCCESS';
351 l_validate VARCHAR2(1);
352 l_return_status VARCHAR2(1);
353 l_msg_summary VARCHAR2(3000);
354 l_msg_details VARCHAR2(3000);
355 l_msg_data VARCHAR2(3000);
356 l_msg_count NUMBER;
357
358 --
359 l_debug_on BOOLEAN;
360 --
361 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PROCESS_AUTO_MANIFEST';
362 --
363 --Bugfix 4070732
364 l_api_session_name CONSTANT VARCHAR2(150) := G_PKG_NAME ||'.' || l_module_name;
365 l_reset_flags BOOLEAN;
366 --k proj
367 l_cur_rec WSH_AUTO_MANIFEST.t_shipment_rec;
368
369 BEGIN
370 --
371 -- Bug 4070732
372 IF WSH_UTIL_CORE.G_START_OF_SESSION_API is null THEN
373 WSH_UTIL_CORE.G_START_OF_SESSION_API := l_api_session_name;
374 WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API := FALSE;
375 END IF;
376
377 --
378 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
379 --
380 IF l_debug_on IS NULL
381 THEN
382 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
383 END IF;
384 --
385 wsh_debug_sv.start_debug;
386
387 IF l_debug_on THEN
388 wsh_debug_sv.push (l_module_name);
389 wsh_debug_sv.log(l_module_name,'Parameters');
390 wsh_debug_sv.log(l_module_name,'==========');
391 wsh_debug_sv.log (l_module_name,'p_organization_id',to_char(p_organization_id));
392 wsh_debug_sv.log (l_module_name,'p_carrier_id',to_char(p_carrier_id));
393 wsh_debug_sv.log (l_module_name,'p_customer_id',to_char(p_customer_id));
394 wsh_debug_sv.log (l_module_name,'p_customer_ship_to_id',to_char(p_customer_ship_to_id));
395 wsh_debug_sv.log (l_module_name,'p_scheduled_from_date',p_scheduled_from_date);
396 wsh_debug_sv.log (l_module_name,'p_scheduled_to_date',p_scheduled_to_date);
397 wsh_debug_sv.log (l_module_name,'p_autopack',p_autopack);
398 wsh_debug_sv.log (l_module_name,'p_log_level',p_log_level);
399 wsh_debug_sv.log (l_module_name,'p_shipment_type',p_shipment_type);
400 wsh_debug_sv.log (l_module_name,'p_doctype',p_doctype);
401 wsh_debug_sv.log (l_module_name,'p_src_header_num_from'
402 ,p_src_header_num_from);
403 wsh_debug_sv.log (l_module_name,'p_src_header_num_to'
404 ,p_src_header_num_to);
405 END IF;
406
407
408
409 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
410
411 IF l_debug_on THEN
412 wsh_debug_sv.log (l_module_name, 'Begining of FOR LOOP');
413 END IF;
414 --Open the cursor based on p_shipment_type and p_doctype
415 IF p_doctype = 'SA' THEN
416 OPEN c_shipment_advice_del;
417 ELSIF p_shipment_type = 'TPW' THEN
418 OPEN c_sr_tpw_del;
419 ELSE
420 OPEN c_sr_cms_del;
421 END IF;
422 LOOP --Begin of the loop {
423 IF c_shipment_advice_del%ISOPEN THEN
424 FETCH c_shipment_advice_del INTO l_cur_rec;
425 IF c_shipment_advice_del%NOTFOUND THEN
426 CLOSE c_shipment_advice_del;
427 EXIT;
428 END IF;
429 ELSIF c_sr_tpw_del%ISOPEN THEN
430 FETCH c_sr_tpw_del INTO l_cur_rec;
431 IF c_sr_tpw_del%NOTFOUND THEN
432 CLOSE c_sr_tpw_del;
433 EXIT;
434 END IF;
435 ELSIF c_sr_cms_del%ISOPEN THEN
436 FETCH c_sr_cms_del INTO l_cur_rec;
437 IF c_sr_cms_del%NOTFOUND THEN
438 CLOSE c_sr_cms_del;
439 EXIT;
440 END IF;
441 END IF;
442 IF l_debug_on THEN
443 wsh_debug_sv.log (l_module_name,' ');
444 wsh_debug_sv.log (l_module_name,'Delivery_id',l_cur_rec.delivery_id);
445 wsh_debug_sv.log (l_module_name,'organization_id',l_cur_rec.organization_id);
446 wsh_debug_sv.log (l_module_name,'Carrier_Id',l_cur_rec.carrier_id);
447 wsh_debug_sv.log (l_module_name,'Customer_Id',l_cur_rec.customer_id);
451 SAVEPOINT start_process_delivery;
448 wsh_debug_sv.log (l_module_name,'Ship_To',l_cur_rec.ultimate_dropoff_location_id);
449 END IF;
450
452
453 l_delivery_status :='SUCCESS';
454 l_warning_index := 0;
455 l_delivery_count := l_delivery_count + 1;
456 l_entity_ids(1) := l_cur_rec.delivery_id;
457
458
459 l_calling_api := 'Calling API wsh_new_deliveries_pvt.Lock_Dlvy_No_Compare';
460 BEGIN
461 wsh_new_deliveries_pvt.Lock_Dlvy_No_Compare(
462 p_delivery_id =>l_entity_ids(1));
463
464 -- set the return status to success if no exception raised, as the
465 -- API Lock_Dlvy_No_Compare does not have a x_return_status
466
467 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
468
469 EXCEPTION
470 WHEN app_exception.application_exception
471 OR app_exception.record_lock_exception THEN
472 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
473 END ;
474
475 IF l_debug_on THEN
476 wsh_debug_sv.log (l_module_name,'wsh_new_deliveries_pvt.Lock_Dlvy_No_Compare return status: ',l_return_status);
477 END IF;
478
479 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN --1.IF {
480
481 IF p_shipment_type = 'CMS' AND p_doctype = 'SR' THEN --{
482
483 l_calling_api := 'Calling WSH_DELIVERY_VALIDATIONS.Check_Pack';
484
485 WSH_DELIVERY_VALIDATIONS.Check_Pack(
486 p_delivery_id => l_entity_ids(1),
487 x_return_status => l_return_status);
488
489 IF l_debug_on THEN
490 wsh_debug_sv.log (l_module_name,'WSH_DELIVERY_VALIDATIONS.Check_Pack Return Status',l_return_status);
491 END IF;
492
493 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR) THEN --3.IF {
494
495 IF ( nvl(p_autopack,'N') = 'Y' ) THEN -- {
496 l_calling_api := 'Calling WSH_CONTAINER_ACTIONS.Auto_Pack_Delivery';
497
498 WSH_CONTAINER_ACTIONS.Auto_Pack_Delivery(
499 p_delivery_tab => l_entity_ids,
500 p_pack_cont_flag => 'N',
501 x_cont_instance_tab => l_con_ids,
502 x_return_status => l_return_status);
503
504 IF l_debug_on THEN
505 wsh_debug_sv.log (l_module_name,'WSH_CONTAINER_ACTIONS.Auto_Pack_Delivery Return Status',
506 l_return_status);
507 END IF;
508
509 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) )THEN --5.IF
510 l_delivery_status := 'ERROR';
511 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN --5.IF
512 l_warning_index := l_warning_index +1;
513 END IF; --5.IF
514
515 ELSE -- }{
516 l_delivery_status := 'ERROR';
517 END IF; --}
518
519 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN --}{
520 l_warning_index := l_warning_index +1;
521 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN --}{
522 l_delivery_status := 'ERROR';
523 END IF; --}
524 END IF; --}
525
526
527 IF (l_delivery_status = 'SUCCESS' ) THEN --{
528 l_calling_api := 'Calling WSH_TRANSACTIONS_UTIL.Send_Shipment_Request';
529
530 WSH_TRANSACTIONS_UTIL.Send_Document (
531 p_entity_id => l_entity_ids(1) ,
532 p_entity_type => 'DLVY',
533 p_action_type => 'A' ,
534 p_document_type => p_doctype ,
535 p_organization_id => l_cur_rec.organization_id,
536 x_return_status => l_return_status);
537
538 IF l_debug_on THEN
539 wsh_debug_sv.log (l_module_name,'WSH_TRANSACTIONS_UTIL.Send_Document Return Status',l_return_status);
540 END IF;
541
542 IF ( l_return_status not in (WSH_UTIL_CORE.G_RET_STS_SUCCESS,WSH_UTIL_CORE.G_RET_STS_WARNING )) THEN --{
543 l_delivery_status := 'ERROR';
544 ELSE --}{
545 IF ( l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING ) THEN
546 l_warning_index := l_warning_index +1;
547 END IF;
548 END IF; --}
549 END IF; --}
550
551 ELSE --}{
552 l_delivery_status := 'ERROR';
553 END IF; --}
554
555
556 WSH_UTIL_CORE.get_messages('Y', l_msg_summary, l_msg_details, l_msg_count);
557 IF (l_msg_count < 2 ) THEN
558 l_msg_details := NULL;
559 END IF;
560
561 IF l_debug_on THEN
562 wsh_debug_sv.log (l_module_name,'l_msg_summary: ',l_msg_summary);
563 wsh_debug_sv.log (l_module_name,'l_msg_details: ',l_msg_details);
564 wsh_debug_sv.log (l_module_name,'l_msg_count: ',l_msg_count);
565 END IF;
566 FND_MSG_PUB.initialize;
570 IF l_debug_on THEN
567
568 IF (l_delivery_status = 'SUCCESS' ) THEN --{
569
571 wsh_debug_sv.log (l_module_name,'Success l_warning_index: ',l_warning_index);
572 END IF;
573
574 --Start of bug 4070732
575 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
576
577 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN --{
578
579 IF l_debug_on THEN
580 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Process_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
581 END IF;
582
583 WSH_UTIL_CORE.Process_stops_for_load_tender(p_reset_flags => FALSE,
584 x_return_status => l_return_status);
585
586 IF l_debug_on THEN
587 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
588 END IF;
589 wsh_util_core.api_post_call
590 (
591 p_return_status => l_return_status,
592 x_num_warnings => l_warning_count,
593 x_num_errors => l_error_count,
594 p_raise_error_flag => FALSE
595 );
596 END IF; --}
597 -- End of bug 4070732
598 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_SUCCESS,
599 WSH_UTIL_CORE.G_RET_STS_WARNING) THEN --{
600 IF ( l_warning_index > 0 ) THEN --{
601 IF l_debug_on THEN
602 wsh_debug_sv.log(l_module_name,'Delivery Status: SUCCESS with WARNING');
603 END IF;
604 l_warning_count := l_warning_count+1;
605 l_warning_delivery(l_warning_count).delivery_name := wsh_new_deliveries_pvt.get_name(l_entity_ids(1));
606 l_warning_delivery(l_warning_count).msg_summary := l_msg_summary;
607 l_warning_delivery(l_warning_count).msg_details := l_msg_details;
608 ELSE --}{
609 IF l_debug_on THEN
610 wsh_debug_sv.log(l_module_name,'Delivery Status: SUCCESS');
611 END IF;
612 l_success_count := l_success_count+1;
613 l_success_delivery(l_success_count).delivery_name := wsh_new_deliveries_pvt.get_name(l_entity_ids(1));
614 l_success_delivery(l_success_count).msg_summary := l_msg_summary;
615 l_success_delivery(l_success_count).msg_details := l_msg_details;
616 END IF; --}
617 END IF; --}
618
619 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_SUCCESS,
620 WSH_UTIL_CORE.G_RET_STS_WARNING) THEN --{
621 COMMIT;
622 ELSE --}{
623 IF l_debug_on THEN
624 wsh_debug_sv.log(l_module_name,'Delivery Status: ERROR');
625 END IF;
626 l_error_count := l_error_count+1;
627 l_error_delivery(l_error_count).delivery_name := wsh_new_deliveries_pvt.get_name(l_entity_ids(1));
628 l_error_delivery(l_error_count).msg_summary := l_msg_summary;
629 l_error_delivery(l_error_count).msg_details := l_msg_details;
630 ROLLBACK TO SAVEPOINT start_process_delivery;
631 END IF; --}
632 ELSE --}{
633 IF l_debug_on THEN
634 wsh_debug_sv.log(l_module_name,'Delivery Status: ERROR');
635 END IF;
636 l_error_count := l_error_count+1;
637 l_error_delivery(l_error_count).delivery_name := wsh_new_deliveries_pvt.get_name(l_entity_ids(1));
638 l_error_delivery(l_error_count).msg_summary := l_msg_summary;
639 l_error_delivery(l_error_count).msg_details := l_msg_details;
640 ROLLBACK TO SAVEPOINT start_process_delivery;
641 END IF; --}
642
643 END LOOP; --}
644
645
646 IF l_error_count > 0 THEN
647 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
648 IF l_delivery_count = l_error_count THEN
649 IF l_debug_on THEN
650 wsh_debug_sv.log(l_module_name,'All Deliveries selected are having error');
651 END IF;
652 END IF;
653 END IF;
654
655 ----** Output to Concurrent Output File **---
656 OPEN get_carrier_name(p_carrier_id);
657 FETCH get_carrier_name INTO l_carrier_name;
658 CLOSE get_carrier_name;
659
660 OPEN get_customer_name(p_customer_id);
661 FETCH get_customer_name INTO l_customer_name;
662 CLOSE get_customer_name;
663
664 OPEN get_location(p_customer_ship_to_id);
665 FETCH get_location INTO l_location;
666 CLOSE get_location;
667
668 IF (NVL(p_autopack,'N') ='N') THEN
669 l_autopack :='No';
670 ELSE
671 l_autopack :='Yes';
672 END IF;
673
674 FND_MESSAGE.SET_NAME('WSH', 'WSH_MANIFEST_PARM');
675 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
676 FND_FILE.put_line(FND_FILE.output,'==========');
677
678 FND_MESSAGE.SET_NAME('WSH', 'WSH_DOC_TYPE');
679 FND_MESSAGE.SET_TOKEN('DOC_TYPE', wsh_util_core.get_lookup_meaning('WSH_TXN_DOCUMENT_TYPE',p_doctype));
680 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
681
682 FND_MESSAGE.SET_NAME('WSH', 'WSH_SHIP_TYPE');
683 FND_MESSAGE.SET_TOKEN('SHIP_TYPE', wsh_util_core.get_lookup_meaning('WSH_SHIPMENT_TYPE',p_shipment_type));
684 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
685
686 FND_MESSAGE.SET_NAME('WSH', 'WSH_MANIFEST_ORG');
690 FND_MESSAGE.SET_NAME('WSH', 'WSH_ORDER_FROM');
687 FND_MESSAGE.SET_TOKEN('ORG_NAME', WSH_UTIL_CORE.Get_Org_Name(p_organization_id));
688 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
689
691 FND_MESSAGE.SET_TOKEN('ORDER',p_src_header_num_from );
692 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
693
694 FND_MESSAGE.SET_NAME('WSH', 'WSH_ORDER_TO');
695 FND_MESSAGE.SET_TOKEN('ORDER',p_src_header_num_to );
696 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
697
698 FND_MESSAGE.SET_NAME('WSH', 'WSH_MANIFEST_CARRIER');
699 FND_MESSAGE.SET_TOKEN('CARRIER_NAME', l_carrier_name);
700 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
701
702 FND_MESSAGE.SET_NAME('WSH', 'WSH_MANIFEST_CUSTOMER');
703 FND_MESSAGE.SET_TOKEN('CUSTOMER_NAME', l_customer_name);
704 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
705
706 FND_MESSAGE.SET_NAME('WSH', 'WSH_MANIFEST_SHIP_TO');
707 FND_MESSAGE.SET_TOKEN('SHIP_TO', l_location);
708 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
709
710 FND_MESSAGE.SET_NAME('WSH', 'WSH_MANIFEST_SCH_FROM');
711 FND_MESSAGE.SET_TOKEN('SCH_FROM_DATE', p_scheduled_from_date);
712 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
713
714 FND_MESSAGE.SET_NAME('WSH', 'WSH_MANIFEST_SCH_TO');
715 FND_MESSAGE.SET_TOKEN('SCH_TO_DATE', p_scheduled_to_date);
716 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
717
718 FND_MESSAGE.SET_NAME('WSH', 'WSH_MANIFEST_AUTOPACK');
719 FND_MESSAGE.SET_TOKEN('AUTOPACK', l_autopack);
720 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
721
722 FND_MESSAGE.SET_NAME('WSH', 'WSH_MANIFEST_LOG_LEVEL');
723 FND_MESSAGE.SET_TOKEN('LOG_LEVEL', to_char(p_log_level));
724 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
725
726 IF (l_delivery_count < 1 ) THEN
727 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
728 FND_FILE.put_line(FND_FILE.output,' ');
729 FND_MESSAGE.SET_NAME('WSH', 'WSH_NO_DEL_FOR_PARAMETERS');
730 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
731 IF l_debug_on THEN
732 wsh_debug_sv.log(l_module_name,l_msg_summary);
733 END IF;
734
735 ELSE
736 FND_FILE.put_line(FND_FILE.output,' ');
737 FND_MESSAGE.SET_NAME('WSH', 'WSH_MANIFEST_SUMMARY');
738 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
739
740 FND_FILE.put_line(FND_FILE.output,'==============');
741 IF l_debug_on THEN
742 wsh_debug_sv.log (l_module_name,'All Deliveries: ',l_delivery_count);
743 END IF;
744 FND_MESSAGE.SET_NAME('WSH', 'WSH_MANIFEST_ALL_DLVY');
745 FND_MESSAGE.SET_TOKEN('ALL_DLVY', to_char(l_delivery_count));
746 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
747
748 IF l_debug_on THEN
749 wsh_debug_sv.log (l_module_name,'Success Deliveries: ',l_success_count);
750 END IF;
751 FND_MESSAGE.SET_NAME('WSH', 'WSH_MANIFEST_SUC_DLVY');
752 FND_MESSAGE.SET_TOKEN('SUC_DLVY', to_char(l_success_count));
753 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
754
755 IF l_debug_on THEN
756 wsh_debug_sv.log (l_module_name,'Warning Deliveries: ',l_warning_count);
757 END IF;
758 FND_MESSAGE.SET_NAME('WSH', 'WSH_MANIFEST_WAR_DLVY');
759 FND_MESSAGE.SET_TOKEN('WAR_DLVY', to_char(l_warning_count));
760 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
761
762 IF l_debug_on THEN
763 wsh_debug_sv.log (l_module_name,'Error Deliveries: ',l_error_count);
764 END IF;
765 FND_MESSAGE.SET_NAME('WSH', 'WSH_MANIFEST_ERR_DLVY');
766 FND_MESSAGE.SET_TOKEN('ERR_DLVY', to_char(l_error_count));
767 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
768
769 FND_FILE.put_line(FND_FILE.output,' ');
770 FND_MESSAGE.SET_NAME('WSH', 'WSH_MANIFEST_HS_DLVY');
771 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
772
773 IF l_debug_on THEN
774 wsh_debug_sv.log(l_module_name,'Success Deliveries');
775 END IF;
776 FOR s_count IN 1..l_success_count LOOP
777 IF (s_count <> 1) THEN
778 FND_FILE.put(FND_FILE.output,',');
779 IF l_debug_on THEN
780 wsh_debug_sv.log(l_module_name,',');
781 END IF;
782 END IF;
783 FND_FILE.put(FND_FILE.output,l_success_delivery(s_count).delivery_name);
784 IF l_debug_on THEN
785 wsh_debug_sv.log (l_module_name,' ',l_success_delivery(s_count).delivery_name);
786 END IF;
787 END LOOP;
788
789 IF l_debug_on THEN
790 wsh_debug_sv.log(l_module_name,'Warning Deliveries');
791 END IF;
792 FND_FILE.put_line(FND_FILE.output,' ');
793 FND_FILE.put_line(FND_FILE.output,' ');
794 FND_MESSAGE.SET_NAME('WSH', 'WSH_MANIFEST_HW_DLVY');
795 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
796
797 FOR w_count IN 1..l_warning_count LOOP
798 IF l_debug_on THEN
799 wsh_debug_sv.log(l_module_name,l_warning_delivery(w_count).delivery_name||': '||
800 l_warning_delivery(w_count).msg_summary);
801 END IF;
802 FND_FILE.put_line(FND_FILE.output,l_warning_delivery(w_count).delivery_name||': '
806 IF l_debug_on THEN
803 ||l_warning_delivery(w_count).msg_summary);
804
805 IF (l_warning_delivery(w_count).msg_details IS NOT NULL ) THEN
807 wsh_debug_sv.log(l_module_name,l_warning_delivery(w_count).msg_details);
808 END IF;
809 FND_FILE.put_line(FND_FILE.output,' '||l_warning_delivery(w_count).msg_details);
810 END IF;
811 END LOOP;
812
813 IF l_debug_on THEN
814 wsh_debug_sv.log(l_module_name,'Error Deliveries');
815 END IF;
816 FND_FILE.put_line(FND_FILE.output,' ');
817 FND_FILE.put_line(FND_FILE.output,' ');
818 FND_MESSAGE.SET_NAME('WSH', 'WSH_MANIFEST_HE_DLVY');
819 FND_FILE.put_line(FND_FILE.output,FND_MESSAGE.GET);
820
821 FOR e_count IN 1..l_error_count LOOP
822 IF l_debug_on THEN
823 wsh_debug_sv.log(l_module_name,l_error_delivery(e_count).delivery_name||': '||
824 l_error_delivery(e_count).msg_summary);
825 END IF;
826 FND_FILE.put_line(FND_FILE.output,l_error_delivery(e_count).delivery_name||': '
827 ||l_error_delivery(e_count).msg_summary);
828
829 IF (l_error_delivery(e_count).msg_details IS NOT NULL) THEN
830 IF l_debug_on THEN
831 wsh_debug_sv.log(l_module_name,l_error_delivery(e_count).msg_details);
832 END IF;
833 FND_FILE.put_line(FND_FILE.output,' '||l_error_delivery(e_count).msg_details);
834 END IF;
835 END LOOP;
836
837 END IF;
838 ----** Output to Concurrent Output File **---
839
840 --Bugfix 4070732 {
841 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name)
842 THEN --{
843 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
844 IF l_debug_on THEN
845 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Process_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
846 END IF;
847 WSH_UTIL_CORE.Process_stops_for_load_tender(
848 p_reset_flags => TRUE,
849 x_return_status => l_return_status);
850 IF l_debug_on THEN
851 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
852 END IF;
853
854
855 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
856 OR (x_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
857 THEN --{
858 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
859 ELSIF x_return_status <> WSH_UTIL_CORE.G_RET_STS_ERROR
860 THEN
861 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
862 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
863 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING
864 THEN
865 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
866 END IF;
867 END IF; --}
868 END IF;
869 END IF; --}
870
871 --}
872 --End of bug 4070732
873 IF l_debug_on THEN
874 wsh_debug_sv.pop(l_module_name);
875 END IF;
876 wsh_debug_sv.stop_debug;
877
878 EXCEPTION
879
880 WHEN others THEN
881 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
882 wsh_util_core.default_handler('WSH_AUTO_MANIFEST.Process_Auto_Manifest',l_module_name);
883 WSH_UTIL_CORE.get_messages('Y', l_msg_summary, l_msg_details, l_msg_count);
884
885 --Start of bug 4070732
886
887 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
888 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
889 IF l_debug_on THEN
890 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
891 END IF;
892
893 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
894 x_return_status => l_return_status);
895 IF l_debug_on THEN
896 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
897 END IF;
898
899 END IF;
900 END IF;
901 --End of bug 4070732
902
903 IF l_debug_on THEN
904 wsh_debug_sv.log(l_module_name,l_msg_summary);
905 wsh_debug_sv.log(l_module_name,'Calling API :'||l_calling_api);
906 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
907 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
908 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
909 END IF;
910
911 END Process_Auto_Manifest;
912
913
914 --
915 -- PROCEDURE : Lock_Manifest_Delivery
916 -- Description: This procedure lock the delivery and its assigned lines
917 --
918 PROCEDURE Lock_Manifest_Delivery(
919 p_delivery_id IN NUMBER,
920 x_return_status OUT NOCOPY VARCHAR2) IS
921
922 RECORD_LOCKED EXCEPTION;
923 PRAGMA EXCEPTION_INIT(RECORD_LOCKED, -54);
924 l_delivery_id NUMBER;
925 l_status_code VARCHAR2(10);
926
927 CURSOR c_lock_delivery IS
928 SELECT wnd.delivery_id, wnd.status_code
929 FROM wsh_delivery_details wdd,
930 wsh_delivery_assignments_v wda,
931 wsh_new_deliveries wnd
935 AND wdd.released_status in ('X','Y')
932 WHERE wdd.delivery_detail_id = wda.delivery_detail_id
933 AND wda.delivery_id = wnd.delivery_id
934 AND wdd.container_flag = 'N'
936 AND wnd.status_code='OP'
937 AND wnd.delivery_id=p_delivery_id
938 AND wda.delivery_id IS NOT NULL
939 FOR UPDATE NOWAIT;
940 --
941 l_debug_on BOOLEAN;
942 --
943 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOCK_MANIFEST_DELIVERY';
944 --
945 BEGIN
946 --
947 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
948 --
949 IF l_debug_on IS NULL
950 THEN
951 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
952 END IF;
953 --
954 IF l_debug_on THEN
955 wsh_debug_sv.push (l_module_name);
956 wsh_debug_sv.log (l_module_name,'DELIVERY_ID',p_delivery_id);
957 END IF;
958
959 OPEN c_lock_delivery;
960 FETCH c_lock_delivery INTO l_delivery_id,l_status_code;
961 CLOSE c_lock_delivery;
962
963 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
964 IF l_debug_on THEN
965 wsh_debug_sv.pop(l_module_name);
966 END IF;
967 EXCEPTION
968 WHEN RECORD_LOCKED THEN
969 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
970 FND_MESSAGE.Set_Name('WSH', 'WSH_NO_LOCK');
971 WSH_UTIL_CORE.add_message (x_return_status,l_module_name);
972 IF l_debug_on THEN
973 WSH_DEBUG_SV.logmsg(l_module_name,'RECORD_LOCKED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
974 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:RECORD_LOCKED');
975 END IF;
976
977 WHEN others THEN
978 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
979 wsh_util_core.default_handler('WSH_AUTO_MANIFEST.Lock_Manifest_Delivery',l_module_name);
980
981 IF l_debug_on THEN
982 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
983 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
984 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
985 END IF;
986
987 END Lock_Manifest_Delivery;
988
989
990 --
991 -- PROCEDURE : Validate_Scheduled_Ship_Date
992 -- Description: This procedure check if scheduled _date of lines assign to delivery fall in the range
993 -- of input scheduled_ship_date
994 --
995 PROCEDURE Validate_Scheduled_Ship_Date(
996 p_delivery_id IN NUMBER,
997 p_scheduled_from_date IN DATE,
998 p_scheduled_to_date IN DATE,
999 x_validate OUT NOCOPY VARCHAR2,
1000 x_return_status OUT NOCOPY VARCHAR2) IS
1001
1002 l_count NUMBER;
1003 CURSOR c1 IS
1004 SELECT count(*)
1005 FROM wsh_delivery_details wdd,
1006 wsh_delivery_assignments_v wda
1007 WHERE wdd.delivery_detail_id = wda.delivery_detail_id
1008 AND wda.delivery_id = p_delivery_id
1009 AND wdd.container_flag = 'N'
1010 AND wdd.released_status in ('X','Y')
1011 AND wda.delivery_id IS NOT NULL
1012 AND trunc(wdd.DATE_SCHEDULED) between trunc(p_scheduled_from_date) AND trunc(p_scheduled_to_date);
1013
1014 CURSOR c2 IS
1015 SELECT count(*)
1016 FROM wsh_delivery_details wdd,
1017 wsh_delivery_assignments_v wda
1018 WHERE wdd.delivery_detail_id = wda.delivery_detail_id
1019 AND wda.delivery_id = p_delivery_id
1020 AND wdd.container_flag = 'N'
1021 AND wdd.released_status in ('X','Y')
1022 AND wda.delivery_id IS NOT NULL
1023 AND trunc(wdd.DATE_SCHEDULED) >= trunc(p_scheduled_from_date);
1024
1025 CURSOR c3 IS
1026 SELECT count(*)
1027 FROM wsh_delivery_details wdd,
1028 wsh_delivery_assignments_v wda
1029 WHERE wdd.delivery_detail_id = wda.delivery_detail_id
1030 AND wda.delivery_id = p_delivery_id
1031 AND wdd.container_flag = 'N'
1032 AND wdd.released_status in ('X','Y')
1033 AND wda.delivery_id IS NOT NULL
1034 AND trunc(wdd.DATE_SCHEDULED) <= trunc(p_scheduled_to_date);
1035
1036 --
1037 l_debug_on BOOLEAN;
1038 --
1039 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_SCHEDULED_SHIP_DATE';
1040 --
1041 BEGIN
1042 --
1043 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1044 --
1045 IF l_debug_on IS NULL
1046 THEN
1047 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1048 END IF;
1049 --
1050 IF l_debug_on THEN
1051 wsh_debug_sv.push (l_module_name);
1052 wsh_debug_sv.log (l_module_name,'DELIVERY_ID',p_delivery_id);
1053 wsh_debug_sv.log (l_module_name,'P_SCHEDULED_FROM_DATE',p_scheduled_from_date);
1054 wsh_debug_sv.log (l_module_name,'P_SCHEDULED_TO_DATE',p_scheduled_to_date);
1055 END IF;
1056
1057 x_validate := 'Y';
1058 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1059
1060 IF (p_scheduled_from_date IS NULL AND p_scheduled_to_date IS NULL ) THEN
1061 IF l_debug_on THEN
1062 wsh_debug_sv.pop(l_module_name,'RETURN');
1063 END IF;
1064 RETURN;
1065 END IF;
1066
1067 IF (p_scheduled_from_date IS NOT NULL AND p_scheduled_to_date IS NOT NULL ) THEN
1068 OPEN c1;
1069 FETCH c1 INTO l_count;
1070 CLOSE c1;
1071 ELSIF (p_scheduled_from_date IS NOT NULL AND p_scheduled_to_date IS NULL ) THEN
1072 OPEN c2;
1073 FETCH c2 INTO l_count;
1074 CLOSE c2;
1075 ELSIF (p_scheduled_from_date IS NULL AND p_scheduled_to_date IS NOT NULL ) THEN
1076 OPEN c3;
1077 FETCH c3 INTO l_count;
1078 CLOSE c3;
1079 END IF;
1080
1081 IF (l_count < 1 ) THEN
1082 x_validate := 'N';
1083 FND_MESSAGE.SET_NAME('WSH','WSH_SCH_DATE_NOT_IN_RANGED');
1084 FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_delivery_id));
1085 WSH_UTIL_CORE.add_message (x_return_status,l_module_name);
1086 END IF;
1087
1088 IF l_debug_on THEN
1089 wsh_debug_sv.pop(l_module_name);
1090 END IF;
1091 EXCEPTION
1092 WHEN others THEN
1093 x_validate := 'N';
1094 wsh_util_core.default_handler('WSH_AUTO_MANIFEST.Validate_Scheduled_Ship_Date',l_module_name);
1095 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1096
1097 IF l_debug_on THEN
1098 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
1099 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1100 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1101 END IF;
1102
1103 END Validate_Scheduled_Ship_Date;
1104
1105 FUNCTION set_auto_pack (
1106 p_doc_type IN VARCHAR2,
1107 p_shipment_type IN VARCHAR2
1108 ) RETURN NUMBER
1109 IS
1110 l_debug_on BOOLEAN;
1111 --
1112 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' ||
1113 'SET_AUTO_PACK';
1114 BEGIN
1115
1116 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1117 --
1118 IF l_debug_on IS NULL
1119 THEN
1120 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1121 END IF;
1122 --
1123 IF l_debug_on THEN
1124 wsh_debug_sv.push (l_module_name);
1125 WSH_DEBUG_SV.log(l_module_name,'p_doc_type', p_doc_type);
1126 WSH_DEBUG_SV.log(l_module_name,'p_shipment_type', p_shipment_type);
1127 END IF;
1128
1129 IF p_doc_type = 'SR' AND p_shipment_type = 'CMS' THEN
1130 IF l_debug_on THEN
1131 WSH_DEBUG_SV.logmsg(l_module_name,'return 1');
1132 wsh_debug_sv.pop(l_module_name);
1133 END IF;
1134 RETURN 1;
1135 END IF;
1136
1137 IF l_debug_on THEN
1138 wsh_debug_sv.pop(l_module_name);
1139 END IF;
1140
1141 RETURN NULL;
1142
1143 END set_auto_pack;
1144
1145 END WSH_AUTO_MANIFEST;