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