[Home] [Help]
PACKAGE BODY: APPS.WSH_BOLS_PVT
Source
1 PACKAGE BODY WSH_BOLS_PVT AS
2 -- $Header: WSHBLTHB.pls 120.0 2005/05/26 18:48:27 appldev noship $
3
4 --
5 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_BOLS_PVT';
6 --
7 PROCEDURE update_Row
8 ( p_api_version IN NUMBER
9 , p_init_msg_list IN VARCHAR2
10 , p_commit IN VARCHAR2
11 , p_validation_level IN NUMBER
12 , x_return_status OUT NOCOPY VARCHAR2
13 , x_msg_count OUT NOCOPY NUMBER
14 , x_msg_data OUT NOCOPY VARCHAR2
15 , p_entity_name IN VARCHAR2
16 , x_entity_id IN OUT NOCOPY NUMBER
17 , p_document_type IN VARCHAR2
18 /* Commented for shipping datamodel changes bug#1918342
19 , p_pod_flag IN VARCHAR2
20 , p_pod_by IN VARCHAR2
21 , p_pod_date IN DATE
22 , p_reason_of_transport IN VARCHAR2
23 , p_description IN VARCHAR2
24 , p_cod_amount IN NUMBER
25 , p_cod_currency_code IN VARCHAR2
26 , p_cod_remit_to IN VARCHAR2
27 , p_cod_charge_paid_by IN VARCHAR2
28 , p_problem_contact_reference IN VARCHAR2
29 , p_bill_freight_to IN VARCHAR2
30 , p_carried_by IN VARCHAR2
31 , p_port_of_loading IN VARCHAR2
32 , p_port_of_discharge IN VARCHAR2
33 , p_booking_office IN VARCHAR2
34 , p_booking_number IN VARCHAR2
35 , p_service_contract IN VARCHAR2
36 , p_shipper_export_ref IN VARCHAR2
37 , p_carrier_export_ref IN VARCHAR2
38 , p_bol_notify_party IN VARCHAR2
39 , p_supplier_code IN VARCHAR2
40 , p_aetc_number IN VARCHAR2
41 , p_shipper_signed_by IN VARCHAR2
42 , p_shipper_date IN DATE
43 , p_carrier_signed_by IN VARCHAR2
44 , p_carrier_date IN DATE
45 , p_bol_issue_office IN VARCHAR2
46 , p_bol_issued_by IN VARCHAR2
47 , p_bol_date_issued IN DATE
48 , p_shipper_hm_by IN VARCHAR2
49 , p_shipper_hm_date IN DATE
50 , p_carrier_hm_by IN VARCHAR2
51 , p_carrier_hm_date IN DATE
52 , p_ledger_id IN NUMBER */ -- LE Uptake
53 , p_consolidate_option IN VARCHAR2
54 , x_trip_id IN OUT NOCOPY NUMBER
55 , x_trip_name IN OUT NOCOPY VARCHAR2
56 , p_pick_up_location_id IN NUMBER
57 , p_drop_off_location_id IN NUMBER
58 , p_carrier_id IN NUMBER
59 , p_ship_method IN VARCHAR2
60 , p_delivery_id IN NUMBER
61 , x_document_number IN OUT NOCOPY VARCHAR2
62 )
63 IS
64 x_rowid varchar2(30);
65 l_trip_info wsh_trips_pvt.trip_rec_type;
66 l_leg_info_new wsh_delivery_legs_pvt.delivery_leg_rec_type;
67 l_leg_info_old wsh_delivery_legs_pvt.delivery_leg_rec_type;
68 l_old_leg_id NUMBER;
69 l_new_leg_id NUMBER;
70 l_ledger_id NUMBER; -- LE Uptake
71 l_del_rows wsh_util_core.id_tab_type;
72 l_leg_rows wsh_util_core.id_tab_type;
73 l_drop_off_stop_id NUMBER;
74 l_pick_up_stop_id NUMBER;
75 wsh_create_trip_error EXCEPTION;
76 wsh_populate_trip_error EXCEPTION;
77 wsh_update_trip_error EXCEPTION;
78 wsh_assign_delivery_error EXCEPTION;
79 wsh_populate_leg_error EXCEPTION;
80 wsh_delete_leg_error EXCEPTION;
81 wsh_update_leg_error EXCEPTION;
82 wsh_create_document_error EXCEPTION;
83 wsh_update_document_error EXCEPTION;
84
85 msg_buffer VARCHAR2(32);
86 tmp_buffer VARCHAR2(32);
87 tmp_out NUMBER;
88 --
89 l_debug_on BOOLEAN;
90 --
91 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_ROW';
92 --
93 BEGIN
94
95 --
96 -- Debug Statements
97 --
98 --
99 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
100 --
101 IF l_debug_on IS NULL
102 THEN
103 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
104 END IF;
105 --
106 IF l_debug_on THEN
107 WSH_DEBUG_SV.push(l_module_name);
108 --
109 WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION',P_API_VERSION);
110 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',P_INIT_MSG_LIST);
111 WSH_DEBUG_SV.log(l_module_name,'P_COMMIT',P_COMMIT);
112 WSH_DEBUG_SV.log(l_module_name,'P_VALIDATION_LEVEL',P_VALIDATION_LEVEL);
113 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_NAME',P_ENTITY_NAME);
114 WSH_DEBUG_SV.log(l_module_name,'X_ENTITY_ID',X_ENTITY_ID);
115 WSH_DEBUG_SV.log(l_module_name,'P_DOCUMENT_TYPE',P_DOCUMENT_TYPE);
116 -- WSH_DEBUG_SV.log(l_module_name,'P_LEDGER_ID',P_LEDGER_ID); -- LE Uptake
117 WSH_DEBUG_SV.log(l_module_name,'P_CONSOLIDATE_OPTION',P_CONSOLIDATE_OPTION);
118 WSH_DEBUG_SV.log(l_module_name,'X_TRIP_ID',X_TRIP_ID);
119 WSH_DEBUG_SV.log(l_module_name,'X_TRIP_NAME',X_TRIP_NAME);
120 WSH_DEBUG_SV.log(l_module_name,'P_PICK_UP_LOCATION_ID',P_PICK_UP_LOCATION_ID);
121 WSH_DEBUG_SV.log(l_module_name,'P_DROP_OFF_LOCATION_ID',P_DROP_OFF_LOCATION_ID);
122 WSH_DEBUG_SV.log(l_module_name,'P_CARRIER_ID',P_CARRIER_ID);
123 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_METHOD',P_SHIP_METHOD);
124 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_ID',P_DELIVERY_ID);
125 WSH_DEBUG_SV.log(l_module_name,'X_DOCUMENT_NUMBER',X_DOCUMENT_NUMBER);
126 END IF;
127 --
128
129 SELECT to_number(HOI.ORG_INFORMATION1) INTO l_ledger_id -- LE Uptake
130 FROM hr_organization_information hoi,
131 wsh_new_deliveries wnd
132 WHERE HOI.ORGANIZATION_ID = wnd.organization_id
133 AND HOI.ORG_INFORMATION_CONTEXT = 'Accounting Information'
134 AND wnd.delivery_id = p_delivery_id;
135
136 IF l_debug_on THEN
137 WSH_DEBUG_SV.log(l_module_name,'l_ledger_id',l_ledger_id); -- LE Uptake
138 END IF;
139
140
141 SAVEPOINT sp1;
142
143 -- if document is not yet created, create it here
144 IF x_document_number IS NULL THEN
145 --
146 -- Debug Statements
147 --
148 IF l_debug_on THEN
149 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_Document_PVT.CREATE_DOCUMENT',WSH_DEBUG_SV.C_PROC_LEVEL);
150 END IF;
151 --
152 WSH_Document_PVT.create_document
153 ( 1.0,
154 'T',
155 NULL,
156 NULL,
157 x_return_status,
158 x_msg_count,
159 x_msg_data,
160 p_entity_name,
161 l_old_leg_id,
162 665,
163 p_pick_up_location_id,
164 'BOL',
165 p_ship_method,
166 l_ledger_id, -- LE Uptake
167 'BOTH',
168 200,
169 x_document_number
170 );
171 END IF;
172
173 IF x_return_status <> 'S' THEN
174 RAISE wsh_create_document_error;
175 END IF;
176
177
178
179 -- update document
180 --
181 -- Debug Statements
182 --
183 IF l_debug_on THEN
184 WSH_DEBUG_SV.log(l_module_name,'x_document_number',x_document_number);
185 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_Document_PVT.UPDATE_DOCUMENT',WSH_DEBUG_SV.C_PROC_LEVEL);
186 END IF;
187 --
188 WSH_Document_PVT.update_document
189 ( p_api_version
190 , p_init_msg_list
191 , p_commit
192 , p_validation_level
193 , x_return_status
194 , x_msg_count
195 , x_msg_data
196 , p_entity_name
197 , x_entity_id
198 , p_document_type
199 , l_ledger_id -- LE Uptake
200 , p_consolidate_option
201 );
202
203 IF x_return_status <> 'S' THEN
204 RAISE wsh_update_document_error;
205 END IF;
206
207 --
208 -- Debug Statements
209 --
210 IF l_debug_on THEN
211 WSH_DEBUG_SV.pop(l_module_name);
212 END IF;
213 --
214 EXCEPTION
215 WHEN wsh_create_trip_error THEN
216 ROLLBACK TO sp1;
217 x_return_status := wsh_util_core.g_ret_sts_error;
218 IF l_debug_on THEN
219 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_CREATE_TRIP_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
220 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_CREATE_TRIP_ERROR');
221 END IF;
222 --
223 WHEN wsh_populate_trip_error THEN
224 ROLLBACK TO sp1;
225 x_return_status := wsh_util_core.g_ret_sts_error;
226 --
227 IF l_debug_on THEN
228 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_POPULATE_TRIP_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
229 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_POPULATE_TRIP_ERROR');
230 END IF;
231 --
232 WHEN wsh_update_trip_error THEN
233 ROLLBACK TO sp1;
234 x_return_status := wsh_util_core.g_ret_sts_error;
235 --
236 IF l_debug_on THEN
237 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UPDATE_TRIP_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
238 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UPDATE_TRIP_ERROR');
239 END IF;
240 --
241 WHEN wsh_assign_delivery_error THEN
242 ROLLBACK TO sp1;
243 x_return_status := wsh_util_core.g_ret_sts_error;
244 --
245 IF l_debug_on THEN
246 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_ASSIGN_DELIVERY_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
247 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_ASSIGN_DELIVERY_ERROR');
248 END IF;
249 --
250 WHEN wsh_populate_leg_error THEN
251 ROLLBACK TO sp1;
252 x_return_status := wsh_util_core.g_ret_sts_error;
253 --
254 IF l_debug_on THEN
255 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_POPULATE_LEG_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
256 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_POPULATE_LEG_ERROR');
257 END IF;
258 --
259 WHEN wsh_delete_leg_error THEN
260 ROLLBACK TO sp1;
261 x_return_status := wsh_util_core.g_ret_sts_error;
262 --
263 IF l_debug_on THEN
264 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_DELETE_LEG_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
265 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_DELETE_LEG_ERROR');
266 END IF;
267 --
268 WHEN wsh_update_leg_error THEN
269 ROLLBACK TO sp1;
270 x_return_status := wsh_util_core.g_ret_sts_error;
271 --
272 IF l_debug_on THEN
273 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UPDATE_LEG_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
274 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UPDATE_LEG_ERROR');
275 END IF;
276 --
277 WHEN wsh_create_document_error THEN
278 ROLLBACK TO sp1;
279 x_return_status := wsh_util_core.g_ret_sts_error;
280 --
281 IF l_debug_on THEN
282 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_CREATE_DOCUMENT_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
283 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_CREATE_DOCUMENT_ERROR');
284 END IF;
285 --
286 WHEN wsh_update_document_error THEN
287 ROLLBACK TO sp1;
288 x_return_status := wsh_util_core.g_ret_sts_error;
289 --
290 IF l_debug_on THEN
291 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UPDATE_DOCUMENT_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
292 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UPDATE_DOCUMENT_ERROR');
293 END IF;
294 --
295 WHEN OTHERS THEN
296 ROLLBACK TO sp1;
297 x_return_status := wsh_util_core.g_ret_sts_unexp_error;
298 --
299 IF l_debug_on THEN
300 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
301 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
302 END IF;
303 --
304 END Update_Row;
305
306
307 PROCEDURE insert_row
308 (x_return_status IN OUT NOCOPY VARCHAR2,
309 x_msg_count IN OUT NOCOPY VARCHAR2,
310 x_msg_data IN OUT NOCOPY VARCHAR2,
311 p_entity_name IN VARCHAR2,
312 x_entity_id IN OUT NOCOPY NUMBER,
313 p_application_id IN NUMBER,
314 p_location_id IN NUMBER,
315 p_document_type IN VARCHAR2,
316 p_document_sub_type IN VARCHAR2,
317 -- p_ledger_id IN NUMBER, -- LE Uptake
318 x_document_number IN OUT NOCOPY VARCHAR2,
319 x_trip_id IN OUT NOCOPY NUMBER,
320 x_trip_name IN OUT NOCOPY VARCHAR2,
321 x_delivery_id IN OUT NOCOPY NUMBER,
322 p_pick_up_location_id IN NUMBER,
323 p_drop_off_location_id IN NUMBER,
324 p_carrier_id IN NUMBER
325 )
326 IS
327 x_rowid varchar2(30);
328 l_trip_info wsh_trips_pvt.trip_rec_type;
329 l_del_rows wsh_util_core.id_tab_type;
330 l_leg_rows wsh_util_core.id_tab_type;
331 l_ledger_id NUMBER; -- LE Uptake
332
333 wsh_create_trip_error EXCEPTION;
334 wsh_populate_trip_error EXCEPTION;
335 wsh_update_trip_error EXCEPTION;
336 wsh_assign_delivery_error EXCEPTION;
337 wsh_create_document_error EXCEPTION;
338 --
339 l_debug_on BOOLEAN;
340 --
341 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'INSERT_ROW';
342 --
343 BEGIN
344 --
345 -- Debug Statements
346 --
347 --
348 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
349 --
350 IF l_debug_on IS NULL
351 THEN
352 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
353 END IF;
354 --
355 IF l_debug_on THEN
356 WSH_DEBUG_SV.push(l_module_name);
357 --
358 WSH_DEBUG_SV.log(l_module_name,'X_RETURN_STATUS',X_RETURN_STATUS);
359 WSH_DEBUG_SV.log(l_module_name,'X_MSG_COUNT',X_MSG_COUNT);
360 WSH_DEBUG_SV.log(l_module_name,'X_MSG_DATA',X_MSG_DATA);
361 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_NAME',P_ENTITY_NAME);
362 WSH_DEBUG_SV.log(l_module_name,'X_ENTITY_ID',X_ENTITY_ID);
363 WSH_DEBUG_SV.log(l_module_name,'P_APPLICATION_ID',P_APPLICATION_ID);
364 WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_ID',P_LOCATION_ID);
365 WSH_DEBUG_SV.log(l_module_name,'P_DOCUMENT_TYPE',P_DOCUMENT_TYPE);
366 WSH_DEBUG_SV.log(l_module_name,'P_DOCUMENT_SUB_TYPE',P_DOCUMENT_SUB_TYPE);
367 -- WSH_DEBUG_SV.log(l_module_name,'P_LEDGER_ID',P_LEDGER_ID); -- LE Uptake
368 WSH_DEBUG_SV.log(l_module_name,'X_DOCUMENT_NUMBER',X_DOCUMENT_NUMBER);
369 WSH_DEBUG_SV.log(l_module_name,'X_TRIP_ID',X_TRIP_ID);
370 WSH_DEBUG_SV.log(l_module_name,'X_TRIP_NAME',X_TRIP_NAME);
371 WSH_DEBUG_SV.log(l_module_name,'X_DELIVERY_ID',X_DELIVERY_ID);
372 WSH_DEBUG_SV.log(l_module_name,'P_PICK_UP_LOCATION_ID',P_PICK_UP_LOCATION_ID);
373 WSH_DEBUG_SV.log(l_module_name,'P_DROP_OFF_LOCATION_ID',P_DROP_OFF_LOCATION_ID);
374 WSH_DEBUG_SV.log(l_module_name,'P_CARRIER_ID',P_CARRIER_ID);
375 END IF;
376 --
377 SAVEPOINT sp1;
378
379 -- l_set_of_books_id := 1;
380
381 SELECT to_number(HOI.ORG_INFORMATION1) INTO l_ledger_id -- LE Uptake
382 FROM hr_organization_information hoi,
386 AND wnd.delivery_id = x_delivery_id;
383 wsh_new_deliveries wnd
384 WHERE HOI.ORGANIZATION_ID = wnd.organization_id
385 AND HOI.ORG_INFORMATION_CONTEXT = 'Accounting Information'
387
388 --
389 IF l_debug_on THEN
390 WSH_DEBUG_SV.log(l_module_name,'l_ledger_id',l_ledger_id); -- LE Uptake
391 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_Document_PVT.CREATE_DOCUMENT',WSH_DEBUG_SV.C_PROC_LEVEL);
392 END IF;
393 --
394 WSH_Document_PVT.create_document
395 ( 1.0,
396 'T',
397 NULL,
398 NULL,
399 x_return_status,
400 x_msg_count,
401 x_msg_data,
402 p_entity_name,
403 x_entity_id,
404 -- fnd_global.resp_appl_id,
405 p_application_id,
406 p_location_id,
407 p_document_type,
408 p_document_sub_type,
409 l_ledger_id, -- LE Uptake
410 'BOTH',
411 200,
412 x_document_number
413 );
414
415
416 IF x_return_status <> 'S' THEN
417 RAISE wsh_create_document_error;
418 END IF;
419
420 --
421 IF l_debug_on THEN
422 WSH_DEBUG_SV.log(l_module_name,'x_document_number',x_document_number);
423 WSH_DEBUG_SV.pop(l_module_name);
424 END IF;
425 --
426 EXCEPTION
427 WHEN wsh_create_trip_error THEN
428 ROLLBACK TO sp1;
429 x_return_status := wsh_util_core.g_ret_sts_error;
430 --
431 IF l_debug_on THEN
432 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_CREATE_TRIP_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
433 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_CREATE_TRIP_ERROR');
434 END IF;
435 --
436 WHEN wsh_populate_trip_error THEN
437 ROLLBACK TO sp1;
438 x_return_status := wsh_util_core.g_ret_sts_error;
439 --
440 IF l_debug_on THEN
441 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_POPULATE_TRIP_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
442 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_POPULATE_TRIP_ERROR');
443 END IF;
444 --
445 WHEN wsh_update_trip_error THEN
446 ROLLBACK TO sp1;
447 x_return_status := wsh_util_core.g_ret_sts_error;
448 --
449 IF l_debug_on THEN
450 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UPDATE_TRIP_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
451 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UPDATE_TRIP_ERROR');
452 END IF;
453 --
454 WHEN wsh_assign_delivery_error THEN
455 ROLLBACK TO sp1;
456 x_return_status := wsh_util_core.g_ret_sts_error;
457 --
458 IF l_debug_on THEN
459 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_ASSIGN_DELIVERY_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
460 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_ASSIGN_DELIVERY_ERROR');
461 END IF;
462 --
463 WHEN wsh_create_document_error THEN
464 ROLLBACK TO sp1;
465 x_return_status := wsh_util_core.g_ret_sts_error;
466 --
467 IF l_debug_on THEN
468 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_CREATE_DOCUMENT_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
469 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_CREATE_DOCUMENT_ERROR');
470 END IF;
471 --
472 WHEN OTHERS THEN
473 ROLLBACK TO sp1;
474 x_return_status := wsh_util_core.g_ret_sts_unexp_error;
475 --
476 IF l_debug_on THEN
477 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
478 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
479 END IF;
480 --
481 END Insert_Row;
482
483
484 PROCEDURE delete_row
485 ( p_api_version IN NUMBER
486 , p_init_msg_list IN VARCHAR2
487 , p_commit IN VARCHAR2
488 , p_validation_level IN NUMBER
489 , x_return_status OUT NOCOPY VARCHAR2
490 , x_msg_count OUT NOCOPY NUMBER
491 , x_msg_data OUT NOCOPY VARCHAR2
492 , p_entity_id IN NUMBER
493 , p_document_type IN VARCHAR2
494 , p_document_number IN VARCHAR2
495 )
496 IS
497 l_rowid VARCHAR2(30);
498
499 wsh_cancel_document_error EXCEPTION;
500 wsh_delete_leg_error EXCEPTION;
501 --
502 l_debug_on BOOLEAN;
503 --
504 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DELETE_ROW';
505 --
506 BEGIN
507 --
508 -- Debug Statements
509 --
510 --
511 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
512 --
513 IF l_debug_on IS NULL
514 THEN
515 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
516 END IF;
517 --
518 IF l_debug_on THEN
519 WSH_DEBUG_SV.push(l_module_name);
520 --
521 WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION',P_API_VERSION);
522 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',P_INIT_MSG_LIST);
526 WSH_DEBUG_SV.log(l_module_name,'P_DOCUMENT_TYPE',P_DOCUMENT_TYPE);
523 WSH_DEBUG_SV.log(l_module_name,'P_COMMIT',P_COMMIT);
524 WSH_DEBUG_SV.log(l_module_name,'P_VALIDATION_LEVEL',P_VALIDATION_LEVEL);
525 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_ID',P_ENTITY_ID);
527 WSH_DEBUG_SV.log(l_module_name,'P_DOCUMENT_NUMBER',P_DOCUMENT_NUMBER);
528 END IF;
529 --
530 SAVEPOINT sp1;
531
532 IF p_document_number <> NULL THEN
533 --
534 IF l_debug_on THEN
535 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_Document_PVT.CANCEL_DOCUMENT',WSH_DEBUG_SV.C_PROC_LEVEL);
536 END IF;
537 --
538 WSH_Document_PVT.cancel_document
539 (P_API_VERSION
540 , P_INIT_MSG_LIST
541 , P_COMMIT
542 , P_VALIDATION_LEVEL
543 , X_RETURN_STATUS
544 , X_MSG_COUNT
545 , X_MSG_DATA
546 , NULL
547 , p_ENTITY_ID
548 , P_DOCUMENT_TYPE
549 , 'BOTH'
550 );
551 IF x_return_status <> 'S' THEN
552 RAISE wsh_cancel_document_error;
553 END IF;
554 END IF;
555
556 IF l_debug_on THEN
557 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_LEGS_PVT.DELETE_DELIVERY_LEG',WSH_DEBUG_SV.C_PROC_LEVEL);
558 END IF;
559 --
560 wsh_delivery_legs_pvt.delete_delivery_leg
561 (l_rowid,
562 p_entity_id,
563 x_return_status
564 );
565
566 IF x_return_status <> 'S' THEN
567 RAISE wsh_delete_leg_error;
568 END IF;
569
570 --
571 -- Debug Statements
572 --
573 IF l_debug_on THEN
574 WSH_DEBUG_SV.pop(l_module_name);
575 END IF;
576 --
577 EXCEPTION
578 WHEN wsh_delete_leg_error THEN
579 ROLLBACK TO sp1;
580 x_return_status := wsh_util_core.g_ret_sts_error;
581 --
582 IF l_debug_on THEN
583 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_DELETE_LEG_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
584 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_DELETE_LEG_ERROR');
585 END IF;
586 --
587 WHEN wsh_cancel_document_error THEN
588 ROLLBACK TO sp1;
589 x_return_status := wsh_util_core.g_ret_sts_error;
590 --
591 IF l_debug_on THEN
592 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_CANCEL_DOCUMENT_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
593 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_CANCEL_DOCUMENT_ERROR');
594 END IF;
595 --
596 WHEN OTHERS THEN
597 ROLLBACK TO sp1;
598 x_return_status := wsh_util_core.g_ret_sts_unexp_error;
599 --
600 IF l_debug_on THEN
601 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
602 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
603 END IF;
604 --
605 END delete_row;
606
607
608 PROCEDURE cancel_bol
609 ( p_trip_id IN NUMBER
610 ,p_old_ship_method_code IN VARCHAR2
611 ,p_new_ship_method_code IN VARCHAR2
612 , x_return_status OUT NOCOPY VARCHAR2
613 )
614 IS
615 -- Two cursors are defined to have better performance.
616 -- bol_num_cur_1 is used when the new_ship_method_code is not null.
617 -- bol_num_cur_2 is used when new_ship_method_code is null. All the BOL Numbers associated to
618 -- trip must be cancelled.
619
620 cursor bol_num_cur_1 (p_trip_id number) is
621 select wdl.delivery_leg_id,
622 wdi.sequence_number,
623 wts1.stop_location_id,
624 wdi.document_instance_id,
625 wdl.delivery_id , --bugfix 3990683
626 wt.name
627 from wsh_delivery_legs wdl,
628 wsh_trip_stops wts1,
629 wsh_trips wt,
630 wsh_document_instances wdi,
631 wsh_doc_sequence_categories wdsc
632 where wdsc.doc_sequence_category_id = wdi.doc_sequence_category_id
633 AND NVL(wdsc.document_code, '-99') <> '-99'
634 AND wdi.entity_id = wdl.delivery_leg_id
635 AND wdi.entity_name = 'WSH_DELIVERY_LEGS'
636 AND wdi.document_type = 'BOL'
637 AND wdi.status <> 'CANCELLED'
638 AND wts1.trip_id = wt.trip_id
639 and wts1.stop_id =wdl.PICK_UP_STOP_ID
640 and wt.trip_id = p_trip_id;
641
642 cursor bol_num_cur_2 (p_trip_id number) is
643 select wdl.delivery_leg_id,
644 wdi.sequence_number,
645 wts1.stop_location_id,
646 wdi.document_instance_id,
647 wdl.delivery_id , --bugfix 3990683
648 wt.name
649 from wsh_delivery_legs wdl,
650 wsh_trip_stops wts1,
651 wsh_trip_stops wts2,
652 wsh_trips wt,
653 wsh_document_instances wdi
654 where wdi.entity_id = wdl.delivery_leg_id
655 AND wdi.entity_name = 'WSH_DELIVERY_LEGS'
656 AND wdi.document_type = 'BOL'
657 AND wdi.status <> 'CANCELLED'
658 AND wts1.trip_id = wt.trip_id
659 and wts2.trip_id = wt.trip_id
660 and wts1.stop_id =wdl.PICK_UP_STOP_ID
661 and wts2.stop_id = wdl.DROP_OFF_STOP_ID
662 and wt.trip_id = p_trip_id;
663 --
664 --
665 l_return_status VARCHAR2(1);
666 l_msg_count NUMBER;
667 l_msg_data VARCHAR2(2000);
671 --
668 l_okay BOOLEAN;
669 TYPE Tab_bol_num_Type IS TABLE OF bol_num_cur_1%ROWTYPE INDEX BY BINARY_INTEGER;
670 l_bol_num_tab Tab_bol_num_Type;
672 --
673 i NUMBER;
674 l_tmp NUMBER;
675 --
676 --
677 -- Variables for logging exceptions
678 l_exception_error_message VARCHAR2(2000) := NULL;
679 l_exception_msg_count NUMBER;
680 l_dummy_exception_id NUMBER;
681 l_exception_msg_data VARCHAR2(4000) := NULL;
682 l_msg VARCHAR2(2000);
683 l_entity VARCHAR2(30);
684 --
685 --
686 wsh_cancel_bol_error EXCEPTION;
687
688 record_locked EXCEPTION;
689 PRAGMA EXCEPTION_INIT(record_locked, -54);
690
691 --
692 --
693
694 --
695 l_debug_on BOOLEAN;
696 --
697 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CANCEL_BOL';
698 --
699 BEGIN
700 --
701 -- Debug Statements
702 --
703 --
704 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
705 --
706 IF l_debug_on IS NULL
707 THEN
708 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
709 END IF;
710 --
711 IF l_debug_on THEN
712 WSH_DEBUG_SV.push(l_module_name);
713 --
714 WSH_DEBUG_SV.log(l_module_name,'P_TRIP_ID',P_TRIP_ID);
715 WSH_DEBUG_SV.log(l_module_name,'P_OLD_SHIP_METHOD_CODE',P_OLD_SHIP_METHOD_CODE);
716 WSH_DEBUG_SV.log(l_module_name,'P_NEW_SHIP_METHOD_CODE',P_NEW_SHIP_METHOD_CODE);
717 END IF;
718 --
719 SAVEPOINT cancel_bol1;
720
721 FND_MESSAGE.SET_NAME('WSH','WSH_BOL_NUM_CANCELLED');
722 FND_MESSAGE.SET_TOKEN('TRIP_ID',p_trip_id);
723 l_msg := FND_MESSAGE.GET;
724
725 IF p_old_ship_method_code is not null then
726
727 IF p_new_ship_method_code is not null then
728
729 OPEN bol_num_cur_1(p_trip_id);
730 LOOP
731 FETCH bol_num_cur_1 INTO l_bol_num_tab(l_bol_num_tab.COUNT + 1);
732 EXIT WHEN bol_num_cur_1%NOTFOUND;
733 END LOOP;
734 close bol_num_cur_1;
735 ELSE
736 OPEN bol_num_cur_2(p_trip_id);
737 LOOP
738 FETCH bol_num_cur_2 INTO l_bol_num_tab(l_bol_num_tab.COUNT + 1);
739 EXIT WHEN bol_num_cur_2%NOTFOUND;
740 END LOOP;
741 close bol_num_cur_2;
742 END IF;
743
744 IF l_debug_on THEN
745 WSH_DEBUG_SV.log(l_module_name,'l_bol_num_tab.COUNT',l_bol_num_tab.COUNT);
746 END IF;
747
748 IF l_bol_num_tab.COUNT <> 0 THEN
749
750 -- Locking the document instances.
751
752 FOR i IN l_bol_num_tab.FIRST..l_bol_num_tab.LAST
753 LOOP
754 select 1 into l_tmp
755 from wsh_document_instances
756 where document_instance_id = l_bol_num_tab(i).document_instance_id
757 FOR UPDATE NOWAIT;
758 END LOOP;
759
760 FOR i IN l_bol_num_tab.FIRST..l_bol_num_tab.LAST
761 LOOP
762 --
763 -- Debug Statements
764 --
765 IF l_debug_on THEN
766 WSH_DEBUG_SV.log(l_module_name,'delivery_leg_id',
767 l_bol_num_tab(i).delivery_leg_id);
768 WSH_DEBUG_SV.log(l_module_name,'stop_location_id',
769 l_bol_num_tab(i).stop_location_id);
770 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_Document_PVT.CANCEL_DOCUMENT',WSH_DEBUG_SV.C_PROC_LEVEL);
771 END IF;
772 --
773 WSH_Document_PVT.cancel_document
774 (p_api_version => 1.0,
775 p_init_msg_list => fnd_api.g_false,
776 p_commit => fnd_api.g_false,
777 p_validation_level => 100,
778 x_return_status => l_return_status,
779 x_msg_count => l_msg_count,
780 x_msg_data => l_msg_data,
781 p_entity_name => NULL,
782 p_entity_id => l_bol_num_tab(i).delivery_leg_id,
783 p_document_type => 'BOL',
784 p_consolidate_option => 'BOTH');
785 IF (l_return_status not in (WSH_UTIL_CORE.G_RET_STS_SUCCESS, WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
786 raise wsh_cancel_bol_error;
787 END IF;
788
789 --
790 IF l_debug_on THEN
791 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_XC_UTIL.LOG_EXCEPTION',WSH_DEBUG_SV.C_PROC_LEVEL);
792 END IF;
793 --
794 l_dummy_exception_id := null; --Bugfix 3990683
795 wsh_xc_util.log_exception(
796 p_api_version => 1.0,
797 x_return_status => l_return_status,
798 x_msg_count => l_exception_msg_count,
799 x_msg_data => l_exception_msg_data,
800 x_exception_id => l_dummy_exception_id ,
801 p_exception_location_id => l_bol_num_tab(i).stop_location_id,
802 p_logged_at_location_id => l_bol_num_tab(i).stop_location_id,
803 p_logging_entity => 'SHIPPER',
807 p_trip_id => p_trip_id,
804 p_logging_entity_id => FND_GLOBAL.USER_ID,
805 p_exception_name => 'WSH_CHANGED_SHIP_METHOD',
806 p_message => l_msg,
808 p_trip_name => l_bol_num_tab(i).name, --bugfix 3990683
809 p_error_message => l_exception_error_message
810 );
811
812 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
813 --
814 IF l_debug_on THEN
815 WSH_DEBUG_SV.logmsg(l_module_name, 'WSH_XC_UTIL.LOG_EXCEPTION DID NOT RETURN SUCCESS' );
816 END IF;
817 --
818 raise wsh_cancel_bol_error;
819 END IF;
820 END LOOP;
821 END IF;
822 END IF;
823
824 IF l_return_status is not null then
825 x_return_status := l_return_status;
826 IF l_debug_on THEN
827 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
828 END IF;
829 ELSE
830 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
831 END IF;
832
833 IF l_debug_on THEN
834 WSH_DEBUG_SV.pop(l_module_name);
835 END IF;
836 --
837 EXCEPTION
838 WHEN wsh_cancel_bol_error THEN
839 ROLLBACK TO cancel_bol1;
840 x_return_status := wsh_util_core.g_ret_sts_error;
841 --
842 IF l_debug_on THEN
843 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_CANCEL_BOL_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
844 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_CANCEL_BOL_ERROR');
845 END IF;
846 --
847 WHEN record_locked THEN
848 x_return_status := wsh_util_core.g_ret_sts_error;
849 FND_MESSAGE.Set_Name('WSH', 'WSH_NO_LOCK');
850 WSH_UTIL_CORE.add_message (x_return_status, l_module_name);
851 --
852 IF l_debug_on THEN
853 WSH_DEBUG_SV.logmsg(l_module_name,'RECORD_LOCKED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
854 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:RECORD_LOCKED');
855 END IF;
856 --
857 WHEN OTHERS THEN
858 ROLLBACK TO cancel_bol1;
859 wsh_util_core.default_handler('WSH_BOLS_PVT.cancel_bol',l_module_name);
860 IF bol_num_cur_1%ISOPEN THEN
861 close bol_num_cur_1;
862 END IF;
863 IF bol_num_cur_2%ISOPEN THEN
864 close bol_num_cur_2;
865 END IF;
866 x_return_status := wsh_util_core.g_ret_sts_unexp_error;
867 --
868 IF l_debug_on THEN
869 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
870 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
871 END IF;
872 --
873 END cancel_bol;
874
875 END WSH_BOLS_PVT;