[Home] [Help]
PACKAGE BODY: APPS.WSH_MBOLS_PVT
Source
1 PACKAGE BODY WSH_MBOLS_PVT AS
2 -- $Header: WSHMBTHB.pls 120.10 2010/10/14 12:19:05 selsubra ship $
3 --
4 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_MBOLS_PVT';
5 --
6 --========================================================================
7 -- PROCEDURE : Generate_MBOL
8 --
9 -- PARAMETERS: p_trip_id trip id
10 -- x_sequence_number MBOL number
11 -- x_return_status return status
12 --
13 --========================================================================
14 PROCEDURE Generate_MBOL(
15 p_trip_id IN NUMBER,
16 x_sequence_number OUT NOCOPY VARCHAR2,
17 x_return_status OUT NOCOPY VARCHAR2
18 ) IS
19
20 CURSOR c_get_trip_info(l_trip_id NUMBER) IS
21 SELECT
22 wt.name,
23 wc.freight_code
24 FROM
25 wsh_trips wt,
26 wsh_carrier_services wcs,
27 wsh_carriers wc
28 WHERE
29 wt.ship_method_code = wcs.ship_method_code(+) AND
30 wcs.carrier_id = wc.carrier_id(+) AND
31 trip_id = l_trip_id;
32
33 CURSOR c_get_seq_num (l_trip_id NUMBER) IS
34 SELECT sequence_number
35 FROM wsh_document_instances
36 WHERE entity_name = 'WSH_TRIPS'
37 AND entity_id = l_trip_id
38 AND status <> 'CANCELLED';
39
40 l_trip_name WSH_TRIPS.NAME%TYPE;
41 l_freight_code WSH_CARRIERS.FREIGHT_CODE%TYPE;
42 l_document_number WSH_DOCUMENT_INSTANCES.SEQUENCE_NUMBER%TYPE;
43 l_ledger_id NUMBER; -- LE Uptake
44 l_msg_count NUMBER;
45 l_msg_data VARCHAR2(2000);
46 l_return_status VARCHAR2(1);
47 -- COMMENTED
48 l_func_currency VARCHAR2(15); --GL_LEDGERS_PUBLIC_V.currency_code%type; -- LE Uptake
49
50 l_org_id NUMBER;
51 l_organization_id NUMBER;
52
53 wsh_create_document_error EXCEPTION;
54
55 --
56 l_debug_on BOOLEAN;
57 --
58 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GENERATE_MBOL';
59 --
60
61 BEGIN
62 --
63 -- Debug Statements
64 --
65 --
66 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
67 --
68 IF l_debug_on IS NULL THEN
69 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
70 END IF;
71 --
72 IF l_debug_on THEN
73 --
74 WSH_DEBUG_SV.push(l_module_name);
75 WSH_DEBUG_SV.log(l_module_name,'P_TRIP_ID',P_TRIP_ID);
76 --
77 END IF;
78 --
79 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
80 --
81
82 OPEN c_get_seq_num(p_trip_id);
83 FETCH c_get_seq_num INTO x_sequence_number;
84 CLOSE c_get_seq_num;
85
86 IF x_sequence_number IS NOT NULL THEN
87 RETURN;
88 END IF;
89
90 --Get Trip Info.
91 OPEN c_get_trip_info(p_trip_id);
92 FETCH c_get_trip_info INTO l_trip_name, l_freight_code;
93 CLOSE c_get_trip_info;
94
95 SAVEPOINT Print_Mbol_Pvt;
96
97 IF l_freight_code IS NULL THEN
98 --
99 IF l_debug_on THEN
100 --
101 WSH_DEBUG_SV.logmsg(l_module_name, 'Trip does not have a valid Freight Code, so MBOL cannot be Created.');
102 --
103 END IF;
104
105 FND_MESSAGE.SET_NAME('WSH','WSH_MBOL_NULL_FREIGHT_CODE');
106 FND_MESSAGE.SET_TOKEN('TRIP_NAME', l_trip_name);
107 x_return_status := wsh_util_core.g_ret_sts_error;
108 wsh_util_core.add_message(x_return_status);
109 RAISE wsh_create_document_error;
110 END IF;
111
112 WSH_FTE_INTEGRATION.GET_ORG_ORGANIZATION_INFO(
113 p_init_msg_list => FND_API.G_FALSE,
114 x_return_status => l_return_status,
115 x_msg_count => l_msg_count,
116 x_msg_data => l_msg_data,
117 x_organization_id => l_organization_id,
118 x_org_id => l_org_id,
119 p_entity_id => p_trip_id,
120 p_entity_type => WSH_FTE_INTEGRATION.C_ORG_INFO_TRIP,
121 p_org_id_flag => FND_API.G_TRUE);
122 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR OR
123 l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
124 --
125 IF l_debug_on THEN
126 WSH_DEBUG_SV.logmsg(l_module_name, 'ORG_ID not found, so MBOL cannot be Created.');
127 END IF;
128 RAISE wsh_create_document_error;
129 END IF;
130
131 IF l_org_id IS NULL THEN
132 -- ECO:bug 4500358
133 -- if operating unit/organization cannot be associated with the trip,
134 -- let the user know what to do in order to generate MBOL.
135 FND_MESSAGE.SET_NAME('WSH', 'WSH_TRIP_NO_ORG_FOR_MBOL');
136 FND_MESSAGE.SET_TOKEN('TRIP_NAME', l_trip_name);
137 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
138 wsh_util_core.add_message(x_return_status);
139 RAISE wsh_create_document_error;
140 END IF;
141
142
143 -- LE Uptake
144 --R12: MOAC passing ORG_ID
145 --Get Ledger ID into l_ledger_id.
146 WSH_UTIL_CORE.Get_Ledger_id_Func_Currency(
147 p_org_id => l_org_id,
148 x_ledger_id => l_ledger_id,
149 x_func_currency => l_func_currency,
150 x_return_status => l_return_status);
151
152 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR OR
153 l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
154 --
155 IF l_debug_on THEN
156 WSH_DEBUG_SV.logmsg(l_module_name, 'Ledger ID not found, so MBOL cannot be Created.');
157 END IF;
158 RAISE wsh_create_document_error;
159 END IF;
160
161 --
162 -- Debug Statements
163 --
164 IF l_debug_on THEN
165 WSH_DEBUG_SV.log(l_module_name, 'l_freight_code', l_freight_code);
166 WSH_DEBUG_SV.log(l_module_name, 'l_trip_name', l_trip_name);
167 WSH_DEBUG_SV.log(l_module_name, 'l_ledger_id', l_ledger_id);
168 END IF;
169 --
170 --
171 IF l_debug_on THEN
172 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DOCUMENT_PVT.CREATE_DOCUMENT',WSH_DEBUG_SV.C_PROC_LEVEL);
173 END IF;
174 --
175
176 WSH_Document_PVT.Create_Document
177 ( p_api_version => 1.0
178 , p_init_msg_list => 'F'
179 , p_commit => NULL
180 , p_validation_level => NULL
181 , x_return_status => l_return_status
182 , x_msg_count => l_msg_count
183 , x_msg_data => l_msg_data
184 , p_entity_name => 'WSH_TRIPS'
185 , p_entity_id => p_trip_id
186 , p_application_id => 665
187 , p_location_id => NULL
188 , p_document_type => 'MBOL'
189 , p_document_sub_type => l_freight_code
190 , p_ledger_id => l_ledger_id
191 , p_consolidate_option => 'BOTH'
192 , p_manual_sequence_number => 200
193 , x_document_number => l_document_number);
194
195 x_sequence_number := l_document_number;
196
197 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
198 RAISE wsh_create_document_error;
199 END IF;
200
201 Generate_Bols( p_trip_id => p_trip_id,
202 x_return_status => l_return_status );
203
204 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
205 RAISE wsh_create_document_error;
206 END IF;
207 --
208 --
209 -- Debug Statements
210 --
211 IF l_debug_on THEN
212 WSH_DEBUG_SV.pop(l_module_name);
213 END IF;
214 --
215 EXCEPTION
216 WHEN wsh_create_document_error THEN
217 ROLLBACK TO Print_Mbol_Pvt;
218 x_return_status := wsh_util_core.g_ret_sts_error;
219 --
220 -- Debug Statements
221 --
222 IF l_debug_on THEN
223 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_CREATE_DOCUMENT_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
224 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_CREATE_DOCUMENT_ERROR');
225 END IF;
226 --
227 WHEN OTHERS THEN
228 wsh_util_core.default_handler('WSH_MBOLS_PVT.Generate_MBOL',l_module_name);
229 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
230 IF l_debug_on THEN
231 WSH_DEBUG_SV.pop(l_module_name,'When Others');
232 END IF;
233
234 END Generate_MBOL;
235
236 --========================================================================
237 -- PROCEDURE : Generate_BOLs
238 --
239 -- PARAMETERS: p_trip_id trip id
240 -- x_return_status return status
241 --
242 --========================================================================
243 PROCEDURE Generate_BOLs(
244 p_trip_id IN NUMBER,
245 x_return_status OUT NOCOPY VARCHAR2
246 ) IS
247
248 CURSOR c_get_delivery_info(l_trip_id IN NUMBER) IS
249 SELECT del.delivery_id,
250 dlg.delivery_leg_id,
251 wt.ship_method_code,
252 del.initial_pickup_location_id,
253 wt.name
254 FROM wsh_new_deliveries del,
255 wsh_delivery_legs dlg,
256 wsh_trip_stops st,
257 wsh_trips wt
258 WHERE del.delivery_id = dlg.delivery_id
259 AND dlg.pick_up_stop_id = st.stop_id
260 AND dlg.parent_delivery_leg_id IS NULL
261 AND st.trip_id = wt.trip_id
262 AND del.initial_pickup_location_id = st.stop_location_id
263 AND wt.trip_id = l_trip_id;
264
265 CURSOR c_get_ledger_id(p_delivery_id IN NUMBER) IS
266 SELECT ood.set_of_books_id
267 FROM org_organization_definitions ood,
268 wsh_new_deliveries del
269 WHERE ood.organization_id = del.organization_id
270 AND del.delivery_id = p_delivery_id;
271
272 l_delivery_id NUMBER;
273 l_delivery_leg_id NUMBER;
274 l_ship_method_code VARCHAR2(30);
275 l_pickup_location_id NUMBER;
276 l_document_number VARCHAR2(50);
277 l_trip_name VARCHAR2(50);
278 l_ledger_id NUMBER;
279 l_msg_count NUMBER;
280 l_msg_data VARCHAR2(2000);
281 l_return_status VARCHAR2(1);
282 l_bol_count NUMBER;
283
284 wsh_create_document_error EXCEPTION;
285
286 --
287 l_debug_on BOOLEAN;
288 --
289 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GENERATE_BOLS';
290 --
291 BEGIN
292 --
293 -- Debug Statements
294 --
295 --
296 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
297 --
298 IF l_debug_on IS NULL THEN
299 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
300 END IF;
301 --
302 IF l_debug_on THEN
303 --
304 WSH_DEBUG_SV.push(l_module_name);
305 WSH_DEBUG_SV.log(l_module_name,'P_TRIP_ID',P_TRIP_ID);
306 --
307 END IF;
308 --
309 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
310 --
311
312 OPEN c_get_delivery_info(p_trip_id);
313 LOOP
314 FETCH c_get_delivery_info INTO l_delivery_id,
315 l_delivery_leg_id,
316 l_ship_method_code,
317 l_pickup_location_id,
318 l_trip_name;
319 EXIT WHEN c_get_delivery_info%NOTFOUND;
320
321 SELECT count(*)
322 INTO l_bol_count
323 FROM wsh_document_instances
324 WHERE entity_name = 'WSH_DELIVERY_LEGS'
325 AND entity_id = l_delivery_leg_id
326 AND status <> 'CANCELLED';
327
328 IF l_bol_count = 0 THEN
329
330 IF l_ship_method_code IS NULL THEN
331 FND_MESSAGE.SET_NAME('WSH','WSH_BOL_NULL_SHIP_METHOD_ERROR');
332 FND_MESSAGE.SET_TOKEN('TRIP_NAME', l_trip_name);
333 x_return_status := wsh_util_core.g_ret_sts_error;
334 wsh_util_core.add_message(x_return_status);
335 CLOSE c_get_delivery_info;
336 RAISE wsh_create_document_error;
337 END IF;
338
339 OPEN c_get_ledger_id(l_delivery_id);
340 FETCH c_get_ledger_id INTO l_ledger_id;
341 IF c_get_ledger_id%NOTFOUND THEN
342 FND_MESSAGE.SET_NAME('WSH','WSH_LEDGER_ID_NOT_FOUND');
343 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
344 wsh_util_core.add_message(x_return_status);
345 CLOSE c_get_delivery_info;
346 RAISE wsh_create_document_error;
347 END IF;
348 IF c_get_ledger_id%ISOPEN THEN
349 CLOSE c_get_ledger_id;
350 END IF;
351
352 WSH_Document_PVT.Create_Document
353 ( p_api_version => 1.0
354 , p_init_msg_list => 'F'
355 , p_commit => NULL
356 , p_validation_level => NULL
357 , x_return_status => l_return_status
358 , x_msg_count => l_msg_count
359 , x_msg_data => l_msg_data
360 , p_entity_name => 'WSH_DELIVERY_LEGS'
361 , p_entity_id => l_delivery_leg_id
362 , p_application_id => 665
363 , p_location_id => l_pickup_location_id
364 , p_document_type => 'BOL'
365 , p_document_sub_type => l_ship_method_code
366 , p_ledger_id => l_ledger_id
367 , p_consolidate_option => 'BOTH'
368 , p_manual_sequence_number => 200
369 , x_document_number => l_document_number);
370
371 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
372 CLOSE c_get_delivery_info;
373 RAISE wsh_create_document_error;
374 END IF;
375 --
376 END IF;
377 END LOOP;
378 IF c_get_delivery_info%ISOPEN THEN
379 CLOSE c_get_delivery_info;
380 END IF;
381
382 --
383 -- Debug Statements
384 --
385 IF l_debug_on THEN
386 WSH_DEBUG_SV.pop(l_module_name);
387 END IF;
388 --
389 EXCEPTION
390 WHEN wsh_create_document_error THEN
391 x_return_status := wsh_util_core.g_ret_sts_error;
392 --
393 -- Debug Statements
394 --
395 IF l_debug_on THEN
396 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_CREATE_DOCUMENT_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
397 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_CREATE_DOCUMENT_ERROR');
398 END IF;
399 --
400 WHEN OTHERS THEN
401 wsh_util_core.default_handler('WSH_MBOLS_PVT.Generate_BOLs',l_module_name);
402 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
403 IF l_debug_on THEN
404 WSH_DEBUG_SV.pop(l_module_name,'When Others');
405 END IF;
406
407 END Generate_BOLs;
408
409 --========================================================================
410 -- PROCEDURE : Print_MBOL
411 --
412 -- PARAMETERS: p_trip_id trip id
413 -- p_generate_bols generate related BOLs if 'Y'
414 -- x_return_status return status
415 --
416 --========================================================================
417 PROCEDURE Print_MBOL(
418 p_trip_id IN NUMBER,
419 p_generate_bols IN VARCHAR2,
420 x_return_status OUT NOCOPY VARCHAR2
421 )IS
422
423 l_conc_program_id NUMBER;
424 l_cp_printer_name VARCHAR2(30);
425 l_print_style VARCHAR2(60);
426 l_save_output_flag VARCHAR2(30);
427 l_print_flag VARCHAR2(1);
428 l_return_status VARCHAR2(1);
429 l_application_id NUMBER;
430 l_printer_name VARCHAR2(32000);
431 l_error_message VARCHAR2(32000);
432 l_copies NUMBER := 0;
433 l_save_output BOOLEAN;
434 l_printer_setup BOOLEAN;
435 l_request_id NUMBER;
436 x_organization_id NUMBER;
437 l_org_id NUMBER;
438 l_report_name VARCHAR2(10);
439 l_msg_count NUMBER;
440 l_msg_data VARCHAR2(2000);
441
442 CURSOR c_conc_prog_csr(p_conc_prog_name VARCHAR2) IS
443 SELECT concurrent_program_id,
444 output_print_style,
445 save_output_flag,
446 print_flag,
447 printer_name
448 FROM fnd_concurrent_programs_vl
449 WHERE application_id = 665
450 AND concurrent_program_name = p_conc_prog_name;
451
452 err_mbol_submission EXCEPTION;
453
454 --
455 l_debug_on BOOLEAN;
456 --
457 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PRINT_MBOL';
458 --
459 BEGIN
460 --
461 -- Debug Statements
462 --
463 --
464 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
465 --
466 IF l_debug_on IS NULL THEN
467 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
468 END IF;
469 --
470 IF l_debug_on THEN
471 --
472 WSH_DEBUG_SV.push(l_module_name);
473 WSH_DEBUG_SV.log(l_module_name,'P_TRIP_ID',P_TRIP_ID);
474 --
475 END IF;
476 --
477 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
478 --
479
480
481
482 OPEN c_conc_prog_csr('WSHRDMBL');
483 FETCH c_conc_prog_csr INTO l_conc_program_id,
484 l_print_style,
485 l_save_output_flag,
486 l_print_flag,
487 l_cp_printer_name;
488 CLOSE c_conc_prog_csr;
489
490
491
492
493
494 IF l_print_flag = 'Y' THEN
495
496 WSH_REPORT_PRINTERS_PVT.Get_Printer(
497 p_concurrent_program_id => l_conc_program_id,
498 p_organization_id => null,
499 p_equipment_type_id => null,
500 p_equipment_instance => null,
501 p_user_id => fnd_global.user_id,
502 p_zone => null,
503 p_department_id => null,
504 p_responsibility_id => fnd_global.resp_id,
505 p_application_id => 665,
506 p_site_id => 0,
507 x_printer => l_printer_name,
508 x_api_status => l_return_status,
509 x_error_message => l_error_message);
510
511 IF l_debug_on THEN
512 wsh_debug_sv.log(l_module_name, 'x_printer', l_printer_name);
513 wsh_debug_sv.log(l_module_name, 'x_api_status', l_return_status);
514 IF l_error_message IS NOT NULL THEN
515 WSH_DEBUG_SV.logmsg(l_module_name, 'GET_PRINTER: ' || l_error_message);
516 END IF;
517 END IF;
518
519 -- Set Print Options
520 l_copies := to_number(NVL(FND_PROFILE.VALUE('CONC_COPIES'),'0')) ; --Bug 9733286
521
522 IF l_debug_on THEN
523 wsh_debug_sv.log(l_module_name, 'l_copies', l_copies);
524 wsh_debug_sv.log(l_module_name, 'l_print_style', l_print_style);
525 wsh_debug_sv.log(l_module_name, 'l_save_output_flag', l_save_output_flag);
526 END IF;
527
528 IF l_printer_name IS NULL OR l_printer_name = 'No Printer' THEN
529
530 l_printer_name := l_cp_printer_name;
531
532 IF l_debug_on THEN
533 WSH_DEBUG_SV.logmsg(l_module_name, 'PRINTER NAME IS NULL AND THE DEFAULT PRINTER IS '||L_PRINTER_NAME );
534 END IF;
535 END IF;
536
537 IF l_save_output_flag = 'Y' THEN
538 l_save_output := TRUE;
539 ELSE
540 l_save_output := FALSE;
541 END IF;
542
543 l_printer_setup := fnd_request.set_print_options
544 (l_printer_name,
545 l_print_style,
546 l_copies,
547 l_save_output,
548 'N');
549
550 IF l_debug_on THEN
551 wsh_debug_sv.log(l_module_name, 'Set_Print_Options', l_printer_setup);
552 END IF;
553 END IF; -- if print_flag = Y
554
555 -- Call Submit Request
556 l_request_id := FND_REQUEST.SUBMIT_REQUEST
557 ( 'WSH'
558 , 'WSHRDMBL'
559 , ''
560 , ''
561 , FALSE
562 , p_trip_id
563 , nvl(p_generate_bols, 'N')
564 , ''
565 , ''
566 , ''
567 , ''
568 , ''
569 , ''
570 , ''
571 , ''
572 , '', '', '', '', '', '', '', '', '', ''
573 , '', '', '', '', '', '', '', '', '', ''
574 , '', '', '', '', '', '', '', '', '', ''
575 , '', '', '', '', '', '', '', '', '', ''
576 , '', '', '', '', '', '', '', '', '', ''
577 , '', '', '', '', '', '', '', '', '', ''
578 , '', '', '', '', '', '', '', '', '', ''
579 , '', '', '', '', '', '', '', '', '', ''
580 , '', '', '', '', '', '', '', '', '', '');
581
582 IF l_debug_on THEN
583 wsh_debug_sv.log(l_module_name, 'l_request_id', l_request_id);
584 END IF;
585
586 IF (l_request_id = 0) THEN
587 raise err_mbol_submission;
588 ELSE
589 FND_MESSAGE.SET_NAME('WSH', 'WSH_MBOL_SUBMITTED');
590 FND_MESSAGE.SET_TOKEN('REQ_ID', to_char(l_request_id));
591 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status,l_module_name);
592 END if;
593
594 --
595 -- Debug Statements
596 --
597 IF l_debug_on THEN
598 WSH_DEBUG_SV.pop(l_module_name);
599 END IF;
600 --
601
602 EXCEPTION
603 WHEN err_mbol_submission THEN
604 x_return_status := wsh_util_core.g_ret_sts_error;
605 fnd_message.set_name('WSH', 'WSH_MBOL_FAILED');
606 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status,l_module_name);
607 IF l_debug_on THEN
608 WSH_DEBUG_SV.logmsg(l_module_name,'err_mbol_submission exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
609 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:err_mbol_submission');
610 END IF;
611 --
612 WHEN OTHERS THEN
613 wsh_util_core.default_handler('WSH_MBOLS_PVT.Print_MBOL',l_module_name);
614 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
615 IF l_debug_on THEN
616 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
617 END IF;
618
619 END Print_MBOL;
620
621 --========================================================================
622 -- PROCEDURE : Print_BOLs
623 --
624 -- PARAMETERS: p_trip_id trip id
625 -- p_conc_request_id Concurrent Request Id (Added for XDO Integration)
626 -- x_return_status return status
627 --
628 --========================================================================
629 PROCEDURE Print_BOLs(
630 p_trip_id IN NUMBER,
631 p_conc_request_id IN NUMBER,
632 x_return_status OUT NOCOPY VARCHAR2
633 )IS
634
635 l_conc_program_id NUMBER;
636 l_cp_printer_name VARCHAR2(30);
637 l_print_style VARCHAR2(60);
638 l_save_output_flag VARCHAR2(30);
639 l_print_flag VARCHAR2(1);
640 l_return_status VARCHAR2(1);
641 l_application_id NUMBER;
642 l_printer_name VARCHAR2(32000);
643 l_error_message VARCHAR2(32000);
644 l_copies NUMBER := 0;
645 l_save_output BOOLEAN;
646 l_printer_setup BOOLEAN;
647 l_request_id NUMBER;
648 l_output_file_type VARCHAR2(10);
649 l_report_name VARCHAR2(10);
650 x_organization_id NUMBER;
651 l_org_id NUMBER;
652 l_msg_count NUMBER;
653 l_msg_data VARCHAR2(2000);
654
655 CURSOR c_conc_prog_csr(p_conc_prog_name NUMBER) IS
656 SELECT concurrent_program_id,
657 output_print_style,
658 save_output_flag,
659 print_flag,
660 printer_name
661 FROM fnd_concurrent_programs_vl
662 WHERE application_id = 665
663 AND concurrent_program_name = p_conc_prog_name;
664
665
666
667
668 err_bol_submission EXCEPTION;
669
670 --
671 l_debug_on BOOLEAN;
672 --
673 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PRINT_BOLs';
674 --
675 BEGIN
676 --
677 -- Debug Statements
678 --
679 --
680 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
681 --
682 IF l_debug_on IS NULL THEN
683 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
684 END IF;
685 --
686 IF l_debug_on THEN
687 --
688 WSH_DEBUG_SV.push(l_module_name);
689 WSH_DEBUG_SV.log(l_module_name,'P_TRIP_ID',P_TRIP_ID);
690 wsh_debug_sv.log(l_module_name, 'p_conc_request_id', p_conc_request_id );
691 --
692 END IF;
693 --
694 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
695 --
696
697
698 OPEN c_conc_prog_csr('WSHRDBOL');
699 FETCH c_conc_prog_csr INTO l_conc_program_id,
700 l_print_style,
701 l_save_output_flag,
702 l_print_flag,
703 l_cp_printer_name;
704
705 CLOSE c_conc_prog_csr;
706
707
708 IF l_print_flag = 'Y' THEN
709
710 WSH_REPORT_PRINTERS_PVT.Get_Printer(
711 p_concurrent_program_id => l_conc_program_id,
712 p_organization_id => null,
713 p_equipment_type_id => null,
714 p_equipment_instance => null,
715 p_user_id => fnd_global.user_id,
716 p_zone => null,
717 p_department_id => null,
718 p_responsibility_id => fnd_global.resp_id,
719 p_application_id => 665,
720 p_site_id => 0,
721 x_printer => l_printer_name,
722 x_api_status => l_return_status,
723 x_error_message => l_error_message);
724
725 IF l_debug_on THEN
726 wsh_debug_sv.log(l_module_name, 'x_printer', l_printer_name);
727 wsh_debug_sv.log(l_module_name, 'x_api_status', l_return_status);
728 IF l_error_message IS NOT NULL THEN
729 WSH_DEBUG_SV.logmsg(l_module_name, 'GET_PRINTER: ' || l_error_message);
730 END IF;
731 END IF;
732
733 -- Set Print Options
734 l_copies := to_number(NVL(FND_PROFILE.VALUE('CONC_COPIES'),'0')) ; --Bug 9733286
735
736 IF l_debug_on THEN
737 wsh_debug_sv.log(l_module_name, 'l_copies', l_copies);
738 wsh_debug_sv.log(l_module_name, 'l_print_style', l_print_style);
739 wsh_debug_sv.log(l_module_name, 'l_save_output_flag', l_save_output_flag);
740 END IF;
741
742 IF l_printer_name IS NULL OR l_printer_name = 'No Printer' THEN
743
744 l_printer_name := l_cp_printer_name;
745
746 IF l_debug_on THEN
747 WSH_DEBUG_SV.logmsg(l_module_name, 'PRINTER NAME IS NULL AND THE DEFAULT PRINTER IS '||L_PRINTER_NAME );
748 END IF;
749 END IF;
750
751 IF l_save_output_flag = 'Y' THEN
752 l_save_output := TRUE;
753 ELSE
754 l_save_output := FALSE;
755 END IF;
756
757 l_printer_setup := fnd_request.set_print_options
758 (l_printer_name,
759 l_print_style,
760 l_copies,
761 l_save_output,
762 'N');
763
764 IF l_debug_on THEN
765 wsh_debug_sv.log(l_module_name, 'Set_Print_Options', l_printer_setup);
766 END IF;
767 END IF; -- if print_flag = Y
768
769 -- Call Submit Request
770 l_request_id := FND_REQUEST.SUBMIT_REQUEST
771 ( 'WSH'
772 , 'WSHRDBOL'
773 , ''
774 , ''
775 , FALSE
776 , ''
777 , ''
778 , ''
779 , ''
780 , ''
781 , p_trip_id
782 , ''
783 , ''
784 , ''
785 , ''
786 , '', '', '', '', '', '', '', '', '', ''
787 , '', '', '', '', '', '', '', '', '', ''
788 , '', '', '', '', '', '', '', '', '', ''
789 , '', '', '', '', '', '', '', '', '', ''
790 , '', '', '', '', '', '', '', '', '', ''
791 , '', '', '', '', '', '', '', '', '', ''
792 , '', '', '', '', '', '', '', '', '', ''
793 , '', '', '', '', '', '', '', '', '', ''
794 , '', '', '', '', '', '', '', '', '', '');
795
796 IF l_debug_on THEN
797 wsh_debug_sv.log(l_module_name, 'l_request_id', l_request_id);
798 END IF;
799
800 IF (l_request_id = 0) THEN
801 raise err_bol_submission;
802 ELSE
803 WSH_UTIL_CORE.PrintMsg('Bill of Lading concurrent request submitted for request ID:' || to_char(l_request_id) );
804 END IF;
805
806 --
807 -- Debug Statements
808 --
809 IF l_debug_on THEN
810 WSH_DEBUG_SV.pop(l_module_name);
811 END IF;
812 --
813 EXCEPTION
814 WHEN err_bol_submission THEN
815 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
816 WSH_UTIL_CORE.PrintMsg('ERROR: Failed to submit Bill of Lading concurrent request');
817 IF l_debug_on THEN
818 WSH_DEBUG_SV.logmsg(l_module_name,'err_bol_submission exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
819 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:err_bol_submission');
820 END IF;
821 --
822 WHEN OTHERS THEN
823 wsh_util_core.default_handler('WSH_MBOLS_PVT.Print_MBOL',l_module_name);
824 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
825 IF l_debug_on THEN
826 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
827 END IF;
828 END Print_BOLs;
829
830 --========================================================================
831 -- PROCEDURE : Cancel_MBOL
832 --
833 -- PARAMETERS: p_trip_id trip id
834 -- x_return_status return status
835 --
836 --========================================================================
837
838 PROCEDURE cancel_mbol
839 ( p_trip_id IN NUMBER
840 , x_return_status OUT NOCOPY VARCHAR2
841 )
842 IS
843 --
844 cursor mbol_num_cur (p_trip_id number) is
845 select document_instance_id
846 from wsh_document_instances
847 where entity_id = p_trip_id
848 AND document_type = 'MBOL'
849 AND status in ('OPEN', 'PLANNED');
850
851
852 TYPE Tab_mbol_num_Type IS TABLE OF mbol_num_cur%ROWTYPE INDEX BY BINARY_INTEGER;
853 l_mbol_num_tab Tab_mbol_num_Type;
854
855 --
856 --
857 l_return_status VARCHAR2(1);
858 l_msg_count NUMBER;
859 l_msg_data VARCHAR2(2000);
860 l_okay BOOLEAN;
861 --
862 --
863 wsh_cancel_mbol_error EXCEPTION;
864 record_locked EXCEPTION;
865 PRAGMA EXCEPTION_INIT(record_locked, -54);
866 l_tmp NUMBER;
867 --
868 l_debug_on BOOLEAN;
869 --
870 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CANCEL_MBOL';
871 --
872 BEGIN
873 --
874 -- Debug Statements
875 --
876 --
877 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
878 --
879 IF l_debug_on IS NULL
880 THEN
881 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
882 END IF;
883 --
884 IF l_debug_on THEN
885 WSH_DEBUG_SV.push(l_module_name);
886 --
887 WSH_DEBUG_SV.log(l_module_name,'P_TRIP_ID',P_TRIP_ID);
888 END IF;
889 --
890 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
891
892 SAVEPOINT cancel_mbol1;
893
894 OPEN mbol_num_cur(p_trip_id);
895 LOOP
896 FETCH mbol_num_cur INTO l_mbol_num_tab(l_mbol_num_tab.COUNT + 1);
897 EXIT WHEN mbol_num_cur%NOTFOUND;
898 END LOOP;
899 close mbol_num_cur;
900
901 IF l_mbol_num_tab.COUNT <> 0 THEN
902
903 --- Locking the document instance
904
905 FOR i IN l_mbol_num_tab.FIRST..l_mbol_num_tab.LAST
906 LOOP
907 select 1 into l_tmp
908 from wsh_document_instances
909 where document_instance_id = l_mbol_num_tab(i).document_instance_id
910 FOR UPDATE NOWAIT;
911 END LOOP;
912
913 --- Cancelling the document here
914 FOR i IN l_mbol_num_tab.FIRST..l_mbol_num_tab.LAST
915 LOOP
916
917 --
918 wsh_document_pvt.cancel_document
919 (p_api_version => 1.0,
920 p_init_msg_list => fnd_api.g_false,
921 p_commit => fnd_api.g_false,
922 p_validation_level => 100,
923 x_return_status => l_return_status,
924 x_msg_count => l_msg_count,
925 x_msg_data => l_msg_data,
926 p_entity_name => NULL,
927 p_entity_id => p_trip_id,
928 p_document_type => 'MBOL'
929 );
930
931 IF (l_return_status not in (WSH_UTIL_CORE.G_RET_STS_SUCCESS, WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
932 raise wsh_cancel_mbol_error;
933 END IF;
934 END LOOP;
935 END IF;
936
937 IF l_return_status is not null then
938 x_return_status := l_return_status;
939 IF l_debug_on THEN
940 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
941 END IF;
942 ELSE
943 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
944 END IF;
945
946 IF l_debug_on THEN
947 WSH_DEBUG_SV.pop(l_module_name);
948 END IF;
949 --
950 EXCEPTION
951 WHEN wsh_cancel_mbol_error THEN
952 ROLLBACK TO cancel_mbol1;
953 x_return_status := wsh_util_core.g_ret_sts_error;
954 --
955 IF l_debug_on THEN
956 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_CANCEL_MBOL_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
957 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_CANCEL_MBOL_ERROR');
958 END IF;
959 --
960 WHEN record_locked THEN
961 x_return_status := wsh_util_core.g_ret_sts_error;
962 FND_MESSAGE.Set_Name('WSH', 'WSH_NO_LOCK');
963 WSH_UTIL_CORE.add_message (x_return_status, l_module_name);
964 --
965 IF l_debug_on THEN
966 WSH_DEBUG_SV.logmsg(l_module_name,'RECORD_LOCKED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
967 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:RECORD_LOCKED');
968 END IF;
969 --
970 WHEN OTHERS THEN
971 ROLLBACK TO cancel_mbol1;
972 wsh_util_core.default_handler('WSH_MBOLS_PVT.cancel_mbol',l_module_name);
973 x_return_status := wsh_util_core.g_ret_sts_unexp_error;
974 --
975 IF l_debug_on THEN
976 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
977 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
978 END IF;
979 --
980 END cancel_mbol;
981
982 --========================================================================
983 -- PROCEDURE : Get_Organization_of_MBOL
984 --
985 -- PARAMETERS: p_trip_id trip id
986 -- x_return_status return status
987 --
988 --========================================================================
989 PROCEDURE Get_Organization_of_MBOL(
990 p_trip_id IN NUMBER,
991 x_organization_id OUT NOCOPY NUMBER,
992 x_return_status OUT NOCOPY VARCHAR2
993 ) IS
994
995 l_shipments_type_flag VARCHAR2(1);
996 l_location_id NUMBER;
997 l_organization_id NUMBER;
998 l_first_delivery_id NUMBER;
999 cursor get_shipments_type_flag is
1000 select shipments_type_flag
1001 from wsh_trips
1002 where trip_id = p_trip_id;
1003
1004 cursor locations_csr is
1005 select stop_location_id into l_location_id
1006 from
1007 wsh_trip_stops
1008 where trip_id = p_trip_id
1009 order by stop_sequence_number desc;
1010
1011 cursor organizations_csr(p_location_id NUMBER) is
1012 select organization_id
1013 from hr_all_organization_units
1014 where location_id = p_location_id;
1015
1016 l_debug_on BOOLEAN;
1017 --
1018 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Get_Organization_of_MBOL';
1019 --
1020 BEGIN
1021 --
1022 -- Debug Statements
1023 --
1024 --
1025 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1026 --
1027 IF l_debug_on IS NULL
1028 THEN
1029 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1030 END IF;
1031 --
1032 IF l_debug_on THEN
1033 WSH_DEBUG_SV.push(l_module_name);
1034 --
1035 WSH_DEBUG_SV.log(l_module_name,'P_TRIP_ID',P_TRIP_ID);
1036 END IF;
1037 --
1038 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1039
1040 open get_shipments_type_flag;
1041 fetch get_shipments_type_flag into l_shipments_type_flag;
1042 close get_shipments_type_flag;
1043 IF l_debug_on THEN
1044 WSH_DEBUG_SV.log(l_module_name,'l_shipments_type_flag',l_shipments_type_flag);
1045 END IF;
1046 open locations_csr;
1047 LOOP
1048 fetch locations_csr into l_location_id;
1049 IF l_debug_on THEN
1050 WSH_DEBUG_SV.log(l_module_name,'l_location_id', l_location_id);
1051 END IF;
1052 if ( l_shipments_type_flag = 'I') then
1053 exit;
1054 end if;
1055 EXIT WHEN locations_csr%NOTFOUND;
1056 END LOOP;
1057 open organizations_csr(l_location_id);
1058 fetch organizations_csr into l_organization_id;
1059 close organizations_csr;
1060
1061 IF l_debug_on THEN
1062 WSH_DEBUG_SV.log(l_module_name,'l_organization_id', l_organization_id);
1063 END IF;
1064
1065 If l_organization_id is null then
1066 --{
1067 select min(delivery_id)
1068 into l_first_delivery_id
1069 from wsh_trip_stops wts, wsh_delivery_legs wdl
1070 where wts.trip_id = p_trip_id
1071 and wts.stop_id = wdl.pick_up_stop_id;
1072 IF l_debug_on THEN
1073 WSH_DEBUG_SV.log(l_module_name,'l_first_delivery_id', l_first_delivery_id);
1074 END IF;
1075
1076 select organization_id
1077 into l_organization_id
1078 from wsh_new_deliveries
1079 where delivery_id = l_first_delivery_id;
1080 IF l_debug_on THEN
1081 WSH_DEBUG_SV.log(l_module_name,'l_organization_id', l_organization_id);
1082 END IF;
1083 --}
1084 end if;
1085 x_organization_id := l_organization_id;
1086
1087 IF l_debug_on THEN
1088 WSH_DEBUG_SV.pop(l_module_name);
1089 END IF;
1090
1091 EXCEPTION
1092 WHEN OTHERS THEN
1093
1094 wsh_util_core.default_handler('WSH_MBOLS_PVT.Get_Organization_of_MBOL',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,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1099 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1100 END IF;
1101
1102 END Get_Organization_of_MBOL;
1103
1104
1105 END wsh_mbols_pvt;