[Home] [Help]
PACKAGE BODY: APPS.WSH_INTERFACE_COMMON_ACTIONS
Source
1 PACKAGE BODY WSH_INTERFACE_COMMON_ACTIONS as
2 /* $Header: WSHINCAB.pls 120.1 2006/10/18 18:45:52 bsadri noship $ */
3
4 -- this cursor will be used by Process_Non_Splits and Process_Splits procedures
5
6 CURSOR del_det_int_cur(l_detail_id NUMBER, l_dlvy_interface_id NUMBER) IS
7 SELECT
8 wddi.FREIGHT_CLASS_CAT_CODE,
9 wddi.HAZARD_CLASS_CODE,
10 wddi.INTMED_SHIP_TO_LOCATION_CODE,
11 wddi.ITEM_NUMBER,
12 wddi.LOCATOR_CODE,
13 wddi.MASTER_CONTAINER_ITEM_NUMBER,
14 wddi.ORGANIZATION_CODE,
15 wddi.SHIP_FROM_LOCATION_CODE,
16 wddi.SHIP_TO_LOCATION_CODE,
17 wddi.PROJECT_ID,
18 wddi.SEAL_CODE,
19 wddi.SHIP_TO_SITE_USE_ID,
20 wddi.SHIPPING_INSTRUCTIONS,
21 wddi.SOURCE_LINE_NUMBER,
22 wddi.TO_SERIAL_NUMBER,
23 wddi.TRACKING_NUMBER,
24 wddi.UNIT_NUMBER,
25 wddi.FILL_PERCENT,
26 wddi.FREIGHT_CLASS_CAT_ID,
27 wddi.INSPECTION_FLAG,
28 wddi.LPN_CONTENT_ID,
29 wddi.LPN_ID,
30 wddi.MASTER_SERIAL_NUMBER,
31 wddi.MAXIMUM_LOAD_WEIGHT,
32 wddi.MAXIMUM_VOLUME,
33 wddi.MINIMUM_FILL_PERCENT,
34 wddi.UNIT_PRICE,
35 wddi.COMMODITY_CODE_CAT_ID,
36 wddi.TP_ATTRIBUTE9,
37 wddi.TP_ATTRIBUTE10,
38 wddi.TP_ATTRIBUTE11,
39 wddi.TP_ATTRIBUTE12,
40 wddi.TP_ATTRIBUTE13,
41 wddi.TP_ATTRIBUTE14,
42 wddi.TP_ATTRIBUTE15,
43 wddi.ATTRIBUTE_CATEGORY,
44 wddi.ATTRIBUTE1,
45 wddi.ATTRIBUTE2,
46 wddi.ATTRIBUTE3,
47 wddi.ATTRIBUTE4,
48 wddi.ATTRIBUTE5,
49 wddi.ATTRIBUTE6,
50 wddi.ATTRIBUTE7,
51 wddi.ATTRIBUTE8,
52 wddi.ATTRIBUTE9,
53 wddi.ATTRIBUTE10,
54 wddi.ATTRIBUTE11,
55 wddi.ATTRIBUTE12,
56 wddi.ATTRIBUTE13,
57 wddi.ATTRIBUTE14,
58 wddi.ATTRIBUTE15,
59 wddi.CREATION_DATE,
60 wddi.CREATED_BY,
61 wddi.LAST_UPDATE_DATE,
62 wddi.LAST_UPDATED_BY,
63 wddi.LAST_UPDATE_LOGIN,
64 wddi.PROGRAM_APPLICATION_ID,
65 wddi.PROGRAM_ID,
66 wddi.PROGRAM_UPDATE_DATE,
67 wddi.REQUEST_ID,
68 wddi.INTERFACE_ACTION_CODE,
69 wddi.LOCK_FLAG,
70 wddi.PROCESS_FLAG,
71 wddi.PROCESS_MODE,
72 wddi.DELETE_FLAG,
73 wddi.PROCESS_STATUS_FLAG,
74 wddi.SOURCE_HEADER_NUMBER,
75 wddi.SOURCE_HEADER_TYPE_ID,
76 wddi.SOURCE_HEADER_TYPE_NAME,
77 wddi.CUST_PO_NUMBER,
78 wddi.SHIP_SET_ID,
79 wddi.ARRIVAL_SET_ID,
80 wddi.TOP_MODEL_LINE_ID,
81 wddi.ATO_LINE_ID,
82 wddi.SHIP_MODEL_COMPLETE_FLAG,
83 wddi.HAZARD_CLASS_ID,
84 wddi.CLASSIFICATION,
85 wddi.ORGANIZATION_ID,
86 wddi.SRC_REQUESTED_QUANTITY,
87 wddi.SRC_REQUESTED_QUANTITY_UOM,
88 wddi.QUALITY_CONTROL_QUANTITY,
89 wddi.CYCLE_COUNT_QUANTITY,
90 wddi.MOVE_ORDER_LINE_ID,
91 wddi.LOCATOR_ID,
92 wddi.MVT_STAT_STATUS,
93 wddi.TRANSACTION_TEMP_ID,
94 wddi.PREFERRED_GRADE,
95 wddi.SRC_REQUESTED_QUANTITY2,
96 wddi.SRC_REQUESTED_QUANTITY_UOM2,
97 wddi.REQUESTED_QUANTITY2,
98 wddi.SHIPPED_QUANTITY2,
99 wddi.DELIVERED_QUANTITY2,
100 wddi.CANCELLED_QUANTITY2,
101 wddi.QUALITY_CONTROL_QUANTITY2,
102 wddi.CYCLE_COUNT_QUANTITY2,
103 wddi.REQUESTED_QUANTITY_UOM2,
104 -- HW OPMCONV - No need for sublot_number
105 --wddi.SUBLOT_NUMBER,
106 wddi.SPLIT_FROM_DELIVERY_DETAIL_ID,
107 wddi.CARRIER_CODE,
108 wddi.COMMODITY_CODE_CAT_CODE,
109 wddi.CUSTOMER_NUMBER,
110 wddi.CUSTOMER_ITEM_NUMBER,
111 wddi.DELIVER_TO_LOCATION_CODE,
112 wddi.CUSTOMER_PRODUCTION_LINE,
113 wddi.DELIVER_TO_SITE_USE_ID,
114 wddi.MOVEMENT_ID,
115 wddi.ORG_ID,
116 wddi.ORIGINAL_SUBINVENTORY,
117 wddi.PACKING_INSTRUCTIONS,
118 wddi.PICKED_QUANTITY,
119 wddi.PICKED_QUANTITY2,
120 wddi.DELIVERY_DETAIL_INTERFACE_ID,
121 wddi.DELIVERY_DETAIL_ID,
122 wddi.SOURCE_CODE,
123 wddi.SOURCE_HEADER_ID,
124 wddi.SOURCE_LINE_ID,
125 wddi.CUSTOMER_ID,
126 wddi.SOLD_TO_CONTACT_ID,
127 wddi.INVENTORY_ITEM_ID,
128 wddi.ITEM_DESCRIPTION,
129 wddi.COUNTRY_OF_ORIGIN,
130 wddi.SHIP_FROM_LOCATION_ID,
131 wddi.SHIP_TO_LOCATION_ID,
132 wddi.SHIP_TO_CONTACT_ID,
133 wddi.DELIVER_TO_LOCATION_ID,
134 wddi.DELIVER_TO_CONTACT_ID,
135 wddi.INTMED_SHIP_TO_LOCATION_ID,
136 wddi.INTMED_SHIP_TO_CONTACT_ID,
137 wddi.SHIP_TOLERANCE_ABOVE,
138 wddi.SHIP_TOLERANCE_BELOW,
139 wddi.REQUESTED_QUANTITY,
140 wddi.CANCELLED_QUANTITY,
141 wddi.SHIPPED_QUANTITY,
142 wddi.DELIVERED_QUANTITY,
143 wddi.REQUESTED_QUANTITY_UOM,
144 wddi.SHIPPING_QUANTITY_UOM,
145 wddi.SUBINVENTORY,
146 wddi.REVISION,
147 wddi.LOT_NUMBER,
148 wddi.CUSTOMER_REQUESTED_LOT_FLAG,
149 wddi.SERIAL_NUMBER,
150 wddi.DATE_REQUESTED,
151 wddi.DATE_SCHEDULED,
152 wddi.MASTER_CONTAINER_ITEM_ID,
153 wddi.DETAIL_CONTAINER_ITEM_ID,
154 wddi.LOAD_SEQ_NUMBER,
155 wddi.SHIP_METHOD_CODE,
156 wddi.CARRIER_ID,
157 wddi.FREIGHT_TERMS_CODE,
158 wddi.SHIPMENT_PRIORITY_CODE,
159 wddi.FOB_CODE,
160 wddi.CUSTOMER_ITEM_ID,
161 wddi.DEP_PLAN_REQUIRED_FLAG,
162 wddi.CUSTOMER_PROD_SEQ,
163 wddi.CUSTOMER_DOCK_CODE,
164 wddi.GROSS_WEIGHT,
165 wddi.NET_WEIGHT,
166 wddi.WEIGHT_UOM_CODE,
167 wddi.VOLUME,
168 wddi.VOLUME_UOM_CODE,
169 wddi.TP_ATTRIBUTE_CATEGORY,
170 wddi.TP_ATTRIBUTE1,
171 wddi.TP_ATTRIBUTE2,
172 wddi.TP_ATTRIBUTE3,
173 wddi.TP_ATTRIBUTE4,
174 wddi.TP_ATTRIBUTE5,
175 wddi.TP_ATTRIBUTE6,
176 wddi.TP_ATTRIBUTE7,
177 wddi.TP_ATTRIBUTE8,
178 wddi.DETAIL_CONTAINER_ITEM_CODE,
179 wddi.TASK_ID,
180 wddi.CUSTOMER_JOB,
181 wddi.CONTAINER_FLAG,
182 wddi.CONTAINER_NAME,
183 wddi.CONTAINER_TYPE_CODE,
184 wddi.CURRENCY_CODE,
185 wddi.CUST_MODEL_SERIAL_NUMBER,
186 -- J: W/V Changes
187 wddi.filled_volume,
188 wddi.wv_frozen_flag,
189 --Bug 3458160
190 wddi.LINE_DIRECTION,
191 wddi.REQUEST_DATE_TYPE_CODE,
192 wddi.EARLIEST_PICKUP_DATE ,
193 wddi.LATEST_PICKUP_DATE ,
194 wddi.EARLIEST_DROPOFF_DATE ,
195 wddi.LATEST_DROPOFF_DATE
196 FROM wsh_del_details_interface wddi,
197 wsh_del_assgn_interface wdai
198 WHERE wddi.delivery_detail_id= nvl(l_detail_id, wddi.delivery_detail_id)
199 AND wddi.delivery_detail_interface_id = wdai.delivery_detail_interface_id
200 AND wdai.delivery_interface_id= l_dlvy_interface_id
201 AND WDDI.INTERFACE_ACTION_CODE = '94X_INBOUND'
202 AND WDAI.INTERFACE_ACTION_CODE = '94X_INBOUND'
203 ORDER BY wddi.delivery_detail_id, wddi.source_line_id;
204
205
206 -- forward declaration
207 --
208 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_INTERFACE_COMMON_ACTIONS';
209 --
210 PROCEDURE Add_To_Update_Table(
211 p_del_det_int_rec IN del_det_int_cur%ROWTYPE,
212 p_update_mode IN VARCHAR2 DEFAULT 'UPDATE',
213 p_delivery_id IN NUMBER,
214 x_return_status OUT NOCOPY VARCHAR2);
215
216
217 PROCEDURE log_errors(
218 p_loc_interface_errors_rec IN WSH_INTERFACE_VALIDATIONS_PKG.interface_errors_rec_type,
219 p_msg_data IN VARCHAR2 DEFAULT NULL,
220 p_api_name IN VARCHAR2,
221 x_return_status OUT NOCOPY VARCHAR2);
222
223 PROCEDURE split_delivery_detail(
224 p_delivery_detail_id IN NUMBER,
225 p_qty_to_split IN NUMBER,
226 x_new_detail_id OUT NOCOPY NUMBER,
227 x_return_status OUT NOCOPY VARCHAR2
228 );
229
230 PROCEDURE add_to_serial_table(
231 p_serial_range_tab IN WSH_GLBL_VAR_STRCT_GRP.ddSerialRangeTabType);
232
233
234 PROCEDURE Update_Delivery_Details(
235 p_source_code IN VARCHAR2 DEFAULT 'OE',
236 p_delivery_interface_id IN NUMBER DEFAULT NULL,
237 p_action_code IN VARCHAR2,
238 x_return_status OUT NOCOPY VARCHAR2);
239
240 /*
241 -----------------------------------------------------------------------------
242 PROCEDURE : Update_Contnr_Int_Assignments
243 PARAMETERS : p_parent_delivery_detail_id
244 p_parent_detail_interface_id
245 x_return_status - return status of API
246 DESCRIPTION :
247 - This procedure is called in the Inbound Map, to relate the SHIPITEM records
248 with the SHIPUNIT/CONTAINER records through the parent_detail_interface_id.
249 - This procedure updates the wsh_del_assgn_interface table.
250 - This takes the parent_delivery_detail_id and parent_detail_interface_id.
251 - For those records which have parent_delivery_detail_id is equal to the
252 parameter value, the parent_detail_interface_id is updated with the give
253 value.
254
255
256 ------------------------------------------------------------------------------
257 */
258
259
260 PROCEDURE Update_Contnr_Int_Assignments(
261 p_parent_delivery_detail_id IN NUMBER,
262 p_parent_detail_interface_id IN NUMBER,
263 x_return_status OUT NOCOPY VARCHAR2) IS
264
265 --
266 l_debug_on BOOLEAN;
267 --
268 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_CONTNR_INT_ASSIGNMENTS';
269 --
270 BEGIN
271 --
272 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
273 --
274 IF l_debug_on IS NULL
275 THEN
276 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
277 END IF;
278 --
279 IF l_debug_on THEN
280 wsh_debug_sv.push(l_module_name, 'Update_Contnr_Int_Assignments');
281 wsh_debug_sv.log (l_module_name,'parent_delivery_detail_id ',p_parent_delivery_detail_id);
282 wsh_debug_sv.log (l_module_name,'parent_detail_interface_id ',p_parent_detail_interface_id);
283 END IF;
284
285 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
286
287 UPDATE wsh_del_assgn_interface
288 SET parent_detail_interface_id = p_parent_detail_interface_id
289 WHERE parent_delivery_detail_id = p_parent_delivery_detail_id;
290
291 IF (SQL%NOTFOUND) THEN
292 NULL;
293 -- need to check with anil
294 END IF;
295
296 IF l_debug_on THEN
297 wsh_debug_sv.pop(l_module_name);
298 END IF;
299
300 EXCEPTION
301
302 WHEN Others THEN
303
304 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
305 IF l_debug_on THEN
306 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
307 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
308 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
309 END IF;
310 END Update_Contnr_Int_Assignments;
311
312 /*
313 -----------------------------------------------------------------------------
314 PROCEDURE : Process_Interfaced_Del_Details
315 PARAMETERS : p_del_detail_interface_id
316 p_del_detail_id
317 p_action_code - Action code 'CREATE' or 'UPDATE'
318 x_del_detail_id - Delivery_Detail_ID of the detail created
319 - using Create_Shipment_Lines api
320 x_return_status - return status of API
321 DESCRIPTION :
322 - This procedure is used to process the delivery details in the wsh_del_details_interface
323 table.
324 - If the action is CREATE, then we take the interface record columns and call
325 the 'Create_Shipment_Lines' api.
326 - If the action is UPDATE, then we do the following:
327 -- Do count(*) of the records for the given delivery_detail_id (p_del_detail_id)
328 -- If the count=1, then we take the interface record columns and call
329 Update_Shipping_Attributes
330 -- If the count>1, then we have multiple delivery detail records in the interface
331 table for one record in the base table.
332 -- Base records need to be split before the update. So we split the base
333 table record based on the quantities in the interface table records
334 -- After every split, we call Update_Shipping_Attributes to update the
335 newly created base record with the corresponding interface record values
336 -- If the interface delivery detail is packed, then we do the following:
337 -- create container instance in base tables using the container inv.item
338 -- pack the base records into the newly created container instances.
339 ------------------------------------------------------------------------------
340 */
341
342
343 PROCEDURE Process_Interfaced_Del_Details(
344 p_delivery_interface_id IN NUMBER,
345 p_delivery_id IN NUMBER,
346 p_new_delivery_id IN NUMBER,
347 p_action_code IN VARCHAR2,
348 x_return_status OUT NOCOPY VARCHAR2) IS
349
350 -- procedure specific variables
351 l_return_status VARCHAR2(30);
352
353 -- exceptions
354 invalid_action_code exception;
355 invalid_input exception;
356
357 --
358 l_debug_on BOOLEAN;
359 --
360 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PROCESS_INTERFACED_DEL_DETAILS';
361 --
362 BEGIN
363 --
364 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
365 --
366 IF l_debug_on IS NULL
367 THEN
368 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
369 END IF;
370 --
371 IF l_debug_on THEN
372 wsh_debug_sv.push(l_module_name, 'Process_Interfaced_Del_Details');
373 wsh_debug_sv.log (l_module_name, 'Delivery Interface Id',p_delivery_interface_id);
374 wsh_debug_sv.log (l_module_name,'Delivery Id', p_delivery_id);
375 wsh_debug_sv.log (l_module_name,'New Delivery Id', p_new_delivery_id);
376 wsh_debug_sv.log (l_module_name,'Action Code ',p_action_code);
377 END IF;
378
379 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
380
381 IF(p_delivery_interface_id IS NULL) THEN
382 raise invalid_input;
383 END IF;
384
385 IF (p_action_code = 'UPDATE' and p_delivery_id IS NULL) THEN
386 raise invalid_input;
387 END IF;
388
389 IF (p_action_code = 'CREATE' and p_new_delivery_id IS NULL) THEN
390 raise invalid_input;
391 END IF;
392
393 Process_Non_Splits(
394 p_delivery_interface_id => p_delivery_interface_id,
395 p_delivery_id => p_delivery_id,
396 p_new_delivery_id => p_new_delivery_id,
397 p_action_code => p_action_code,
398 x_return_status => l_return_status);
399
400 IF l_debug_on THEN
401 wsh_debug_sv.log (l_module_name, 'Return Status from Process_Non_Splits', l_return_status);
402 END IF;
403
404 IF(l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
405 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
406 END IF;
407
408 IF(p_action_code = 'UPDATE') THEN
409 Process_Splits(
410 p_delivery_interface_id => p_delivery_interface_id,
411 p_delivery_id => p_delivery_id,
412 x_return_status => l_return_status);
413 END IF;
414
415 IF l_debug_on THEN
416 wsh_debug_sv.log (l_module_name, 'Return Status from Process Splits', l_return_status);
417 END IF;
418
419 IF(l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
420 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
421 END IF;
422
423 IF l_debug_on THEN
424 wsh_debug_sv.log (l_module_name, 'Packing Table Count', G_Packing_Detail_Tab.count);
425 END IF;
426 IF(G_Packing_Detail_Tab.count > 0) THEN
427 Pack_Lines(
428 x_return_status => l_return_status);
429
430 IF l_debug_on THEN
431 wsh_debug_sv.log (l_module_name, 'Return Status from Pack Lines', l_return_status);
432 END IF;
433
434 IF(l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
435 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
436 END IF;
437 END IF;
438
439 Update_Delivery_Details(
440 p_delivery_interface_id => p_delivery_interface_id,
441 p_action_code => p_action_code,
442 x_return_status => l_return_status);
443
444 IF l_debug_on THEN
445 wsh_debug_sv.log (l_module_name, 'Return Status from Update Del Details', l_return_status);
446 END IF;
447
448 IF(l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
449 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
450 END IF;
451
452
453 IF l_debug_on THEN
454 wsh_debug_sv.pop(l_module_name);
455 END IF;
456
457 EXCEPTION
458 WHEN invalid_action_code THEN
459 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
460 IF l_debug_on THEN
461 WSH_DEBUG_SV.logmsg(l_module_name,'invalid_action_code exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
462 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_action_code');
463 END IF;
464 WHEN invalid_input THEN
465 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
466 IF l_debug_on THEN
467 WSH_DEBUG_SV.logmsg(l_module_name,'invalid_input exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
468 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_input');
469 END IF;
470 WHEN Others THEN
471 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
472 IF l_debug_on THEN
473 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
474 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
475 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
476 END IF;
477 END Process_Interfaced_Del_Details;
478
479
480 PROCEDURE Process_Non_Splits(
481 p_delivery_interface_id IN NUMBER,
482 p_delivery_id IN NUMBER,
483 p_new_delivery_id IN NUMBER,
484 p_action_code IN VARCHAR2,
485 x_return_status OUT NOCOPY VARCHAR2) IS
486 -- local variables
487 l_new_del_detail_id NUMBER;
488 l_new_detail_ids WSH_UTIL_CORE.Id_Tab_Type;
489 l_table_count NUMBER;
490 l_return_status VARCHAR2(30);
491
492 l_del_details_info WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Rec_Type;
493 -- Patchset I: Harmonization Project
494 l_detail_info_tab WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Attr_Tbl_Type;
495 l_api_version NUMBER := 1.0;
496 l_detail_in_rec WSH_GLBL_VAR_STRCT_GRP.detailInRecType;
497 l_detail_out_rec WSH_GLBL_VAR_STRCT_GRP.detailOutRecType;
498 l_index NUMBER;
499 -- End Patchset I: Harmonization Project
500
501
502 l_delivery_id NUMBER;
503 l_delivery_name NUMBER;
504
505 l_cont_inst_exists NUMBER;
506 l_cont_instance_id NUMBER;
507 l_cont_name VARCHAR2(30);
508 l_row_id VARCHAR2(30);
509 l_det_freight_costs NUMBER;
510
511 l_pickable_flag VARCHAR2(1);
512 -- J: W/V Changes
513 l_unit_weight NUMBER;
514 l_unit_volume NUMBER;
515
516 -- public api variables
517 l_init_msg_list VARCHAR2(30) := NULL;
518 l_msg_count NUMBER;
519 l_msg_data VARCHAR2(3000);
520 l_commit VARCHAR2(1);
521 l_validation_level NUMBER;
522 -- cursors
523 CURSOR detail_ids_cur IS
524 SELECT count(*), wdai.delivery_detail_id, wddi.container_flag
525 FROM wsh_del_assgn_interface wdai, wsh_del_details_interface wddi
526 WHERE wdai.delivery_interface_id = p_delivery_interface_id
527 AND wdai.delivery_detail_interface_id = wddi.delivery_detail_interface_id
528 AND WDAI.INTERFACE_ACTION_CODE = '94X_INBOUND'
529 AND WDDI.INTERFACE_ACTION_CODE = '94X_INBOUND'
530 GROUP BY wdai.delivery_detail_id, wddi.container_flag
531 HAVING count(*) = 1
532 ORDER BY wddi.container_flag desc;
533
534 CURSOR cont_inst_exists(l_del_det_id NUMBER) IS
535 SELECT count(*)
536 FROM wsh_delivery_details wdd, wsh_delivery_assignments_v wda
537 WHERE wdd.source_line_id = l_del_det_id
538 AND wdd.source_code = 'WSH'
539 AND wdd.container_flag = 'Y'
540 AND wdd.delivery_detail_id = wda.delivery_detail_id
541 AND wda.delivery_id = p_delivery_id; -- check this
542
543 CURSOR c_specific_item_info( p_inventory_item_id NUMBER,
544 p_organization_id NUMBER)
545 IS
546 SELECT decode(mtl_transactions_enabled_flag,'Y','Y','N'),
547 -- J: W/V Changes
548 unit_weight,
549 unit_volume
550 FROM mtl_system_items m
551 WHERE m.inventory_item_id = p_inventory_item_id
552 AND m.organization_id = p_organization_id;
553
554 --Bug fix 3658492.
555 CURSOR c_org_oper_unit(p_organization_id IN NUMBER) IS
556 SELECT to_number(org_information3)
557 FROM hr_organization_information
558 WHERE organization_id = p_organization_id
559 AND org_information_context = 'Accounting Information';
560
561
562 l_dummy VARCHAR2(10);
563
564 del_det_int_rec del_det_int_cur%ROWTYPE;
565
566 --exceptions
567 invalid_input exception;
568 create_lines_failed exception;
569 create_cont_instance_failed exception;
570 add_to_update_failed exception;
571 new_assignment_failed exception;
572 freight_cost_processing_error exception;
573 l_msg_details varchar2(2000);
574 l_loc_interface_error_rec WSH_INTERFACE_VALIDATIONS_PKG.interface_errors_rec_type;
575
576 -- K LPN CONV. rv
577 l_cont_tab wsh_util_core.id_tab_type;
578 l_lpn_unit_weight NUMBER;
579 l_lpn_unit_volume NUMBER;
580 l_lpn_weight_uom_code VARCHAR2(100);
581 l_lpn_volume_uom_code VARCHAR2(100);
582 -- K LPN CONV. rv
583
584 --
585 l_debug_on BOOLEAN;
586 --
587 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PROCESS_NON_SPLITS';
588 --
589 BEGIN
590 --
591 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
592 --
593 IF l_debug_on IS NULL
594 THEN
595 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
596 END IF;
597 --
598 IF l_debug_on THEN
599 wsh_debug_sv.push(l_module_name, 'Process_Non_Splits');
600 wsh_debug_sv.log (l_module_name,'Delivery Interface Id',p_delivery_interface_id);
601 wsh_debug_sv.log (l_module_name,'Delivery Id', p_delivery_id);
602 wsh_debug_sv.log (l_module_name, 'New Delivery Id', p_new_delivery_id);
603 wsh_debug_sv.log (l_module_name,'Action Code ',p_action_code);
604 END IF;
605
606 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
607
608 IF(p_delivery_interface_id IS NULL) THEN
609 raise invalid_input;
610 END IF;
611
612 IF(p_action_code = 'UPDATE') THEN
613 IF (p_delivery_id IS NOT NULL) THEN
614 l_delivery_id := p_delivery_id;
615 ELSE
616 raise invalid_input;
617 END IF;
618 ELSIF(p_action_code = 'CREATE' AND p_new_delivery_id IS NULL) THEN
619 raise invalid_input;
620
621 END IF;
622
623 l_new_detail_ids.delete;
624
625 IF l_debug_on THEN
626 wsh_debug_sv.logmsg(l_module_name, 'Before Looping thru details');
627 END IF;
628
629 FOR det_id IN detail_ids_cur LOOP
630 /* Bug fix 2451920
631 Have to clear out the variable l_cont_name
632 After the first loop, the in-out parameter(x_cont_name) of create_container_instance
633 populates the variable l_cont_name
634 Since we don't want to use the created container name, we have to clear this variable */
635
636 l_cont_name := NULL;
637
638
639 IF l_debug_on THEN
640 wsh_debug_sv.log (l_module_name, 'Delivery Detail ID',det_id.delivery_detail_id);
641 END IF;
642
643 OPEN del_det_int_cur(det_id.delivery_detail_id, p_delivery_interface_id);
644 FETCH del_det_int_cur INTO del_det_int_rec;
645 CLOSE del_det_int_cur;
646
647 IF l_debug_on THEN
648 wsh_debug_sv.log(l_module_name,'Delivery Detail Interf Id',del_det_int_rec.delivery_detail_interface_id);
649 END IF;
650
651 IF(p_action_code = 'CREATE') THEN --{
652
653 /*
654 Set l_del_details_info.released_status = X
655 Because picking at TPW is not supported for patchset H
656 */
657
658 l_del_details_info.released_status := 'X';
659
660 /*
661 Set l_del_details_info.oe_interfaced_flag = X
662 Because lines should not be interfaced with OM at the TPW
663 */
664
665 l_del_details_info.oe_interfaced_flag := 'X';
666
667
668 /* For 940 Inbound, the next step is to create delivery details
669 through the call to wsh_interface_pub.create_shipment_lines.
670 This public api expects a source_header_id for the delivery details.
671 We use the newly created delivery id as the source_header_id
672 for the delivery details that are to be created */
673
674 l_del_details_info.source_header_id := nvl(del_det_int_rec.source_header_id, p_new_delivery_id);
675
676 l_del_details_info.source_code := del_det_int_rec.source_code;
677 l_del_details_info.source_line_id := del_det_int_rec.source_line_id;
678 l_del_details_info.org_id := del_det_int_rec.org_id;
679
680 /* create_shipment_line api needs source line number
681 we use the supplier instance's delivery detail id as the source line number
682 Inbound mapping would have populated source_line_id column with the
683 supplier instance's delivery detail id */
684
685 l_del_details_info.source_line_number := nvl(del_det_int_rec.source_line_number, del_det_int_rec.source_line_id);
686
687
688 l_del_details_info.customer_id := del_det_int_rec.customer_id;
689 l_del_details_info.sold_to_contact_id := del_det_int_rec.sold_to_contact_id;
690 l_del_details_info.inventory_item_id := del_det_int_rec.inventory_item_id;
691 l_del_details_info.item_description := del_det_int_rec.item_description;
692 l_del_details_info.hazard_class_id := del_det_int_rec.hazard_class_id;
693 l_del_details_info.country_of_origin := del_det_int_rec.country_of_origin;
694 l_del_details_info.classification := del_det_int_rec.classification;
695 l_del_details_info.ship_from_location_id := del_det_int_rec.ship_from_location_id;
696 l_del_details_info.ship_to_location_id := del_det_int_rec.ship_to_location_id;
697 l_del_details_info.ship_to_contact_id := del_det_int_rec.ship_to_contact_id;
698 l_del_details_info.ship_to_site_use_id := del_det_int_rec.ship_to_site_use_id;
699 l_del_details_info.deliver_to_location_id := del_det_int_rec.deliver_to_location_id;
700 l_del_details_info.deliver_to_contact_id := del_det_int_rec.deliver_to_contact_id;
701 l_del_details_info.deliver_to_site_use_id := del_det_int_rec.deliver_to_site_use_id;
702 l_del_details_info.intmed_ship_to_location_id := del_det_int_rec.intmed_ship_to_location_id;
703 l_del_details_info.intmed_ship_to_contact_id := del_det_int_rec.intmed_ship_to_contact_id;
704 -- l_del_details_info.hold_code := del_det_int_rec.hold_code;
705 l_del_details_info.ship_tolerance_above := del_det_int_rec.ship_tolerance_above;
706 l_del_details_info.ship_tolerance_below := del_det_int_rec.ship_tolerance_below;
707 l_del_details_info.requested_quantity := del_det_int_rec.requested_quantity;
708 l_del_details_info.shipped_quantity := del_det_int_rec.shipped_quantity;
709 l_del_details_info.delivered_quantity := del_det_int_rec.delivered_quantity;
710 l_del_details_info.requested_quantity_uom := del_det_int_rec.requested_quantity_uom;
711 l_del_details_info.subinventory := del_det_int_rec.subinventory;
712 l_del_details_info.revision := del_det_int_rec.revision;
713 l_del_details_info.lot_number := del_det_int_rec.lot_number;
714 l_del_details_info.customer_requested_lot_flag := del_det_int_rec.customer_requested_lot_flag;
715 l_del_details_info.serial_number := del_det_int_rec.serial_number;
716 l_del_details_info.locator_id := del_det_int_rec.locator_id;
717 l_del_details_info.date_requested := del_det_int_rec.date_requested;
718 l_del_details_info.date_scheduled := del_det_int_rec.date_scheduled;
719 l_del_details_info.master_container_item_id := del_det_int_rec.master_container_item_id;
720 l_del_details_info.detail_container_item_id := del_det_int_rec.detail_container_item_id;
721 l_del_details_info.load_seq_number := del_det_int_rec.load_seq_number;
722 l_del_details_info.ship_method_code := del_det_int_rec.ship_method_code;
723 l_del_details_info.carrier_id := del_det_int_rec.carrier_id;
724 l_del_details_info.freight_terms_code := del_det_int_rec.freight_terms_code;
725 l_del_details_info.shipment_priority_code := del_det_int_rec.shipment_priority_code;
726 l_del_details_info.fob_code := del_det_int_rec.fob_code;
727 l_del_details_info.customer_item_id := del_det_int_rec.customer_item_id;
728 l_del_details_info.dep_plan_required_flag := del_det_int_rec.dep_plan_required_flag;
729 l_del_details_info.customer_prod_seq := del_det_int_rec.customer_prod_seq;
730 l_del_details_info.customer_dock_code := del_det_int_rec.customer_dock_code;
731 l_del_details_info.cust_model_serial_number := del_det_int_rec.cust_model_serial_number;
732 l_del_details_info.customer_job := del_det_int_rec.customer_job ;
733 l_del_details_info.customer_production_line := del_det_int_rec.customer_production_line;
734 l_del_details_info.net_weight := del_det_int_rec.net_weight;
735 l_del_details_info.weight_uom_code := del_det_int_rec.weight_uom_code;
736 l_del_details_info.volume := del_det_int_rec.volume;
737 l_del_details_info.volume_uom_code := del_det_int_rec.volume_uom_code;
738 l_del_details_info.tp_attribute_category := del_det_int_rec.tp_attribute_category;
739 l_del_details_info.tp_attribute1 := del_det_int_rec.tp_attribute1;
740 l_del_details_info.tp_attribute2 := del_det_int_rec.tp_attribute2;
741 l_del_details_info.tp_attribute3 := del_det_int_rec.tp_attribute3;
742 l_del_details_info.tp_attribute4 := del_det_int_rec.tp_attribute4;
743 l_del_details_info.tp_attribute5 := del_det_int_rec.tp_attribute5;
744 l_del_details_info.tp_attribute6 := del_det_int_rec.tp_attribute6;
745 l_del_details_info.tp_attribute7 := del_det_int_rec.tp_attribute7;
746 l_del_details_info.tp_attribute8 := del_det_int_rec.tp_attribute8;
747 l_del_details_info.tp_attribute9 := del_det_int_rec.tp_attribute9;
748 l_del_details_info.tp_attribute10 := del_det_int_rec.tp_attribute10;
749 l_del_details_info.tp_attribute11 := del_det_int_rec.tp_attribute11;
750 l_del_details_info.tp_attribute12 := del_det_int_rec.tp_attribute12;
751 l_del_details_info.tp_attribute13 := del_det_int_rec.tp_attribute13;
752 l_del_details_info.tp_attribute14 := del_det_int_rec.tp_attribute14;
753 l_del_details_info.tp_attribute15 := del_det_int_rec.tp_attribute15;
754 l_del_details_info.attribute_category := del_det_int_rec.attribute_category;
755 l_del_details_info.attribute1 := del_det_int_rec.attribute1;
756 l_del_details_info.attribute2 := del_det_int_rec.attribute2;
757 l_del_details_info.attribute3 := del_det_int_rec.attribute3;
758 l_del_details_info.attribute4 := del_det_int_rec.attribute4;
759 l_del_details_info.attribute5 := del_det_int_rec.attribute5;
760 l_del_details_info.attribute6 := del_det_int_rec.attribute6;
761 l_del_details_info.attribute7 := del_det_int_rec.attribute7;
762 l_del_details_info.attribute8 := del_det_int_rec.attribute8;
763 l_del_details_info.attribute9 := del_det_int_rec.attribute9;
764 l_del_details_info.attribute10 := del_det_int_rec.attribute10;
765 l_del_details_info.attribute11 := del_det_int_rec.attribute11;
766 l_del_details_info.attribute12 := del_det_int_rec.attribute12;
767 l_del_details_info.attribute13 := del_det_int_rec.attribute13;
768 l_del_details_info.attribute14 := del_det_int_rec.attribute14;
769 l_del_details_info.attribute15 := del_det_int_rec.attribute15;
770
771 /* do we need to send the who columns for create/update ??
772 l_del_details_info.created_by := del_det_int_rec.created_by;
773 l_del_details_info.creation_date := del_det_int_rec.creation_date;
774 l_del_details_info.last_update_date := del_det_int_rec.last_update_date;
775 l_del_details_info.last_update_login := del_det_int_rec.last_update_login;
776 -- l_del_details_info.last_updated_by := del_det_int_rec.last_updated_by;
777 l_del_details_info.program_application_id := del_det_int_rec.program_application_id;
778 l_del_details_info.program_id := del_det_int_rec.program_id;
779 l_del_details_info.program_update_date := del_det_int_rec.program_update_date;
780 l_del_details_info.request_id := del_det_int_rec.request_id;
781 */
782 l_del_details_info.mvt_stat_status := del_det_int_rec.mvt_stat_status;
783
784 l_del_details_info.organization_id := del_det_int_rec.organization_id;
785 l_del_details_info.transaction_temp_id := del_det_int_rec.transaction_temp_id;
786 l_del_details_info.ship_set_id := del_det_int_rec.ship_set_id;
787 l_del_details_info.arrival_set_id := del_det_int_rec.arrival_set_id;
788 l_del_details_info.ship_model_complete_flag := del_det_int_rec.ship_model_complete_flag;
789
790 l_del_details_info.top_model_line_id := del_det_int_rec.top_model_line_id;
791 l_del_details_info.source_header_number := del_det_int_rec.source_header_number;
792 l_del_details_info.source_header_type_id := del_det_int_rec.source_header_type_id;
793 l_del_details_info.source_header_type_name := del_det_int_rec.source_header_type_name;
794 l_del_details_info.cust_po_number := del_det_int_rec.cust_po_number;
795 l_del_details_info.ato_line_id := del_det_int_rec.ato_line_id;
796 l_del_details_info.src_requested_quantity := del_det_int_rec.src_requested_quantity;
797 l_del_details_info.src_requested_quantity_uom := del_det_int_rec.src_requested_quantity_uom;
798 l_del_details_info.move_order_line_id := del_det_int_rec.move_order_line_id;
799 l_del_details_info.cancelled_quantity := del_det_int_rec.cancelled_quantity;
800 l_del_details_info.quality_control_quantity := del_det_int_rec.quality_control_quantity;
801 l_del_details_info.cycle_count_quantity := del_det_int_rec.cycle_count_quantity;
802 l_del_details_info.tracking_number := del_det_int_rec.tracking_number;
803 l_del_details_info.movement_id := del_det_int_rec.movement_id;
804 l_del_details_info.shipping_instructions := del_det_int_rec.shipping_instructions;
805 l_del_details_info.packing_instructions := del_det_int_rec.packing_instructions;
806 l_del_details_info.project_id := del_det_int_rec.project_id;
807 l_del_details_info.task_id := del_det_int_rec.task_id;
808
809 l_del_details_info.inspection_flag := del_det_int_rec.inspection_flag;
810 l_del_details_info.container_flag := del_det_int_rec.container_flag;
811 l_del_details_info.container_type_code := del_det_int_rec.container_type_code ;
812
813 l_del_details_info.container_name := del_det_int_rec.container_name;
814 l_del_details_info.fill_percent := del_det_int_rec.fill_percent;
815 l_del_details_info.gross_weight := del_det_int_rec.gross_weight;
816
817 l_del_details_info.master_serial_number := del_det_int_rec.master_serial_number;
818 l_del_details_info.maximum_load_weight := del_det_int_rec.maximum_load_weight;
819 l_del_details_info.maximum_volume := del_det_int_rec.maximum_volume;
820 l_del_details_info.minimum_fill_percent := del_det_int_rec.minimum_fill_percent;
821 l_del_details_info.seal_code := del_det_int_rec.seal_code;
822 l_del_details_info.unit_number := del_det_int_rec.unit_number ;
823 l_del_details_info.unit_price := del_det_int_rec.unit_price;
824 l_del_details_info.currency_code := del_det_int_rec.currency_code;
825 l_del_details_info.freight_class_cat_id := del_det_int_rec.freight_class_cat_id;
826 l_del_details_info.commodity_code_cat_id := del_det_int_rec.commodity_code_cat_id;
827 l_del_details_info.preferred_grade := del_det_int_rec.preferred_grade;
828 l_del_details_info.src_requested_quantity2 := del_det_int_rec.src_requested_quantity2;
829 l_del_details_info.src_requested_quantity_uom2 := del_det_int_rec.src_requested_quantity_uom2 ;
830 l_del_details_info.requested_quantity2 := del_det_int_rec.requested_quantity2;
831 l_del_details_info.shipped_quantity2 := del_det_int_rec.shipped_quantity2;
832 l_del_details_info.delivered_quantity2 := del_det_int_rec.delivered_quantity2 ;
833 l_del_details_info.cancelled_quantity2 := del_det_int_rec.cancelled_quantity2;
834 l_del_details_info.quality_control_quantity2 := del_det_int_rec.quality_control_quantity2;
835 l_del_details_info.cycle_count_quantity2 := del_det_int_rec.cycle_count_quantity2;
836 l_del_details_info.requested_quantity_uom2 := del_det_int_rec.requested_quantity_uom2;
837 -- HW OPMCONV - No need for sublot_number
838 -- l_del_details_info.sublot_number := del_det_int_rec.sublot_number ;
839 l_del_details_info.lpn_id := del_det_int_rec.lpn_id ;
840 -- bug 2399705
841 -- We need to make this fix as WSHDDSHB.pls and WSHDDSPB.pls need to
842 -- set inv_interfaced_flag to 'X' if pickable_flag is 'N'or NULL for WSH line.
843 -- And if we do not set the pickable_flag according the item defn, inventory interface
844 -- will not be run even for standard items the third party warehouse instance.
845 open c_specific_item_info(p_inventory_item_id => del_det_int_rec.inventory_item_id,
846 p_organization_id => del_det_int_rec.organization_id);
847 fetch c_specific_item_info into l_pickable_flag,
848 -- J: W/V Changes
849 l_unit_weight,l_unit_volume;
850 close c_specific_item_info;
851 l_del_details_info.pickable_flag := l_pickable_flag;
852 -- J: W/V Changes
853 l_del_details_info.unit_weight := l_unit_weight;
854 l_del_details_info.unit_volume := l_unit_volume;
855 l_del_details_info.wv_frozen_flag := del_det_int_rec.wv_frozen_flag;
856 -- bug 2399705
857 -- l_del_details_info.pickable_flag := del_det_int_rec.pickable_flag ;
858 l_del_details_info.original_subinventory := del_det_int_rec.original_subinventory ;
859 l_del_details_info.to_serial_number := del_det_int_rec.to_serial_number;
860 l_del_details_info.picked_quantity := del_det_int_rec.picked_quantity;
861 l_del_details_info.picked_quantity2 := del_det_int_rec.picked_quantity2;
862 --Bug 3458160
863 l_del_details_info.LINE_DIRECTION := del_det_int_rec.LINE_DIRECTION;
864 l_del_details_info.REQUEST_DATE_TYPE_CODE:= del_det_int_rec.REQUEST_DATE_TYPE_CODE;
865 l_del_details_info.EARLIEST_PICKUP_DATE := del_det_int_rec.EARLIEST_PICKUP_DATE;
866 l_del_details_info.LATEST_PICKUP_DATE := del_det_int_rec.LATEST_PICKUP_DATE;
867 l_del_details_info.EARLIEST_DROPOFF_DATE:= del_det_int_rec.EARLIEST_DROPOFF_DATE;
868 l_del_details_info.LATEST_DROPOFF_DATE := del_det_int_rec.LATEST_DROPOFF_DATE;
869 IF (WSH_UTIL_CORE.GC3_IS_INSTALLED = 'N'
870 AND wsh_util_core.tp_is_installed = 'Y')
871 THEN
872 l_del_details_info.ignore_for_planning := 'Y';
873 END IF;
874
875 IF l_debug_on THEN
876 wsh_debug_sv.log (l_module_name, 'calling create shipment lines');
877 wsh_debug_sv.log (l_module_name, 'the mandatory attributes for create shipment lines are:');
878 wsh_debug_sv.log (l_module_name, 'source_code',l_del_details_info.source_code);
879 wsh_debug_sv.log (l_module_name, 'Source header id',l_del_details_info.source_header_id);
880 wsh_debug_sv.log (l_module_name, 'Source header number',l_del_details_info.source_header_number);
881 wsh_debug_sv.log (l_module_name, 'Source line id',l_del_details_info.source_line_id);
882 wsh_debug_sv.log (l_module_name, 'Source line number',l_del_details_info.source_line_number);
883 wsh_debug_sv.log (l_module_name, 'Organization id',l_del_details_info.organization_id);
884 wsh_debug_sv.log (l_module_name, 'Org Id', l_del_details_info.org_id);
885 wsh_debug_sv.log (l_module_name, 'Requested qty', l_del_details_info.requested_quantity);
886 wsh_debug_sv.log (l_module_name, 'Requested qty uom',l_del_details_info.requested_quantity_uom );
887 wsh_debug_sv.log (l_module_name, 'Src req qty',l_del_details_info. src_requested_quantity);
888 wsh_debug_sv.log (l_module_name, 'Src req qty uom', l_del_details_info.src_requested_quantity_uom);
889 wsh_debug_sv.log (l_module_name, 'inventory item id',l_del_details_info.inventory_item_id);
890 wsh_debug_sv.log (l_module_name, 'ship from loc id',l_del_details_info.ship_from_location_id);
891 wsh_debug_sv.log (l_module_name, 'ship to loc id', l_del_details_info.ship_to_location_id);
892 wsh_debug_sv.log (l_module_name, 'LINE_DIRECTION', l_del_details_info.LINE_DIRECTION);
893 wsh_debug_sv.log (l_module_name, 'REQUEST_DATE_TYPE_CODE', l_del_details_info.REQUEST_DATE_TYPE_CODE);
894 wsh_debug_sv.log (l_module_name, 'EARLIEST_PICKUP_DATE', l_del_details_info.EARLIEST_PICKUP_DATE);
895 wsh_debug_sv.log (l_module_name, 'LATEST_PICKUP_DATE', l_del_details_info.LATEST_PICKUP_DATE);
896 wsh_debug_sv.log (l_module_name, 'EARLIEST_DROPOFF_DATE', l_del_details_info.EARLIEST_DROPOFF_DATE);
897 wsh_debug_sv.log (l_module_name, 'LATEST_DROPOFF_DATE', l_del_details_info.LATEST_DROPOFF_DATE);
898 --}
899 END IF;
900
901
902 /* Patchset I : Harmonization Project.
903 Calling Wrapper Group API in Interface Package */
904
905 l_detail_info_tab(1) := l_del_details_info;
906 l_detail_in_rec.caller := 'WSH_TPW_INBOUND';
907 l_detail_in_rec.action_code := 'CREATE';
908
909 WSH_INTERFACE_GRP.Create_Update_Delivery_Detail(
910 p_api_version_number => l_api_version,
911 p_init_msg_list => FND_API.G_FALSE,
912 p_commit => FND_API.G_FALSE,
913 x_return_status => l_return_status,
914 x_msg_count => l_msg_count,
915 x_msg_data => l_msg_data,
916 p_detail_info_tab => l_detail_info_tab,
917 p_IN_rec => l_detail_in_rec,
918 x_OUT_rec => l_detail_out_rec);
919
920 l_index := l_detail_out_rec.detail_ids.first;
921 l_new_del_detail_id := l_detail_out_rec.detail_ids(l_index);
922
923
924 IF l_debug_on THEN
925 wsh_debug_sv.log (l_module_name, 'Create Shipment Lines l_new_del_detail_id,l_return_status',
926 l_new_del_detail_id||','||l_return_status);
927 END IF;
928
929 IF(l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
930
931 l_loc_interface_error_rec.p_interface_table_name := 'WSH_NEW_DEL_INTERFACE';
932 l_loc_interface_error_rec.p_interface_id := p_delivery_interface_id;
933
934 IF l_debug_on THEN
935 wsh_debug_sv.log (l_module_name, 'Delivery Interface Id', p_delivery_interface_id);
936 END IF;
937
938 Log_Errors(
939 p_loc_interface_errors_rec => l_loc_interface_error_rec,
940 p_msg_data => l_msg_data,
941 p_api_name => 'WSH_INTERFACE_PUB.Create_Shipment_lines' ,
942 x_return_status => l_return_status);
943
944 IF l_debug_on THEN
945 wsh_debug_sv.log (l_module_name, 'Return status after log_errors', l_return_status);
946 END IF;
947 raise create_lines_failed;
948 END IF;
949
950 l_table_count := l_new_detail_ids.count;
951
952 IF l_new_del_detail_id IS NOT NULL THEN
953 l_new_detail_ids(l_table_count+1) := l_new_del_detail_id;
954 ELSE
955 raise create_lines_failed;
956 END IF;
957 ELSIF(p_action_code = 'UPDATE') THEN --}{
958 IF l_debug_on THEN
959 wsh_debug_sv.logmsg(l_module_name,'Starting Update Action');
960 END IF;
961 IF(nvl(del_det_int_rec.container_flag, 'N') = 'Y') THEN
962 -- container record
963 -- check if a container instance exists
964 -- if it does not exist, then create a container instance using the item_id
965
966 OPEN cont_inst_exists(del_det_int_rec.delivery_detail_id);
967 FETCH cont_inst_exists INTO l_cont_inst_exists;
968 CLOSE cont_inst_exists;
969
970 IF l_debug_on THEN
971 wsh_debug_sv.log (l_module_name, 'Container Instance Check', l_cont_inst_exists);
972 END IF;
973
974 IF(nvl(l_cont_inst_exists, -9999) = 1) THEN
975 -- Just add to the global update table.
976 null;
977 ELSE
978 -- need to create the container instance
979 --
980 -- K LPN CONV. rv
981 --
982 WSH_CONTAINER_ACTIONS.Create_Cont_Instance_Multi(
983 x_cont_name => l_cont_name,
984 p_cont_item_id => del_det_int_rec.inventory_item_id,
985 x_cont_instance_id => l_cont_instance_id,
986 p_par_detail_id => NULL,
987 p_organization_id => del_det_int_rec.organization_id,
988 p_container_type_code => del_det_int_rec.container_type_code,
989 p_num_of_containers => 1,
990 x_row_id => l_row_id,
991 x_return_status => l_return_status,
992 x_cont_tab => l_cont_tab,
993 x_unit_weight => l_lpn_unit_weight,
994 x_unit_volume => l_lpn_unit_volume,
995 x_weight_uom_code => l_lpn_weight_uom_code,
996 x_volume_uom_code => l_lpn_volume_uom_code,
997 p_lpn_id => NULL,
998 p_ignore_for_planning => NULL,
999 p_caller => 'WSH');
1000 --
1001 --
1002 IF l_debug_on THEN
1003 WSH_DEBUG_SV.log(l_module_name,'return status',l_return_status);
1004 WSH_DEBUG_SV.log(l_module_name,'count of l_cont_tab',l_cont_tab.count);
1005 END IF;
1006 l_cont_instance_id := l_cont_tab(1);
1007 -- K LPN CONV. rv
1008 --
1009 /*
1010 WSH_CONTAINER_ACTIONS.Create_Container_Instance(
1011 x_cont_name => l_cont_name,
1012 p_cont_item_id => del_det_int_rec.inventory_item_id,
1013 x_cont_instance_id => l_cont_instance_id,
1014 p_par_detail_id => NULL,
1015 p_organization_id => del_det_int_rec.organization_id,
1016 p_container_type_code => del_det_int_rec.container_type_code,
1017 x_row_id => l_row_id,
1018 x_return_status => l_return_status);
1019
1020 IF l_debug_on THEN
1021 wsh_debug_sv.log (l_module_name, 'Create_Container_Instance l_cont_instance_id,
1022 l_return_status',l_cont_instance_id||','||l_return_status);
1023 END IF;
1024 */
1025
1026 IF(l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1027 raise create_cont_instance_failed;
1028 END IF;
1029
1030
1031
1032 -- need to update the source_line_id of the newly created container instance
1033 UPDATE wsh_delivery_details
1034 SET source_line_id = del_det_int_rec.delivery_detail_id
1035 WHERE delivery_detail_id = l_cont_instance_id;
1036
1037 IF(SQL%NOTFOUND) THEN
1038 null;
1039 --need to check
1040 END IF;
1041
1042
1043 -- Need to update the record's delivery_detail id with the newly created
1044 -- delivery_detail_id. Because this record will be sent to USA for updating
1045 -- the newly created container instance with the data from the interface table record.
1046 del_det_int_rec.delivery_detail_id := l_cont_instance_id;
1047
1048 --Add the container instance id to the list of detail ids that need assignment
1049
1050 END IF; -- if l_cont_inst_exists
1051
1052
1053 ELSE -- not a container, plain update
1054 null;
1055 END IF; -- if nvl(del_det container flag)
1056
1057 -- Add to the global table here. Because we need to call USA for all three update cases viz.
1058 -- 1. newly created container instance
1059 -- 2. existing container instance
1060 -- 3. existing non-container delivery details
1061
1062 Add_To_Update_Table(
1063 p_del_det_int_rec => del_det_int_rec,
1064 p_update_mode => 'UPDATE',
1065 p_delivery_id => l_delivery_id,
1066 x_return_status => l_return_status);
1067
1068 IF l_debug_on THEN
1069 wsh_debug_sv.log (l_module_name, 'Add_To_Update_Table l_return_status',l_return_status);
1070 END IF;
1071
1072 IF(l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1073 raise add_to_update_failed;
1074 END IF;
1075
1076 -- Process Interface freight costs
1077 SELECT count(*) INTO l_det_freight_costs
1078 FROM wsh_freight_costs_interface
1079 WHERE delivery_detail_interface_id = del_det_int_rec.delivery_detail_interface_id
1080 AND INTERFACE_ACTION_CODE = '94X_INBOUND';
1081
1082 IF l_debug_on THEN
1083 wsh_debug_sv.log (l_module_name, 'Interface freight count', l_det_freight_costs);
1084 END IF;
1085
1086 IF(l_det_freight_costs > 0 ) THEN
1087 Process_Int_Freight_Costs(
1088 p_del_detail_interface_id => del_det_int_rec.delivery_detail_interface_id,
1089 x_return_status => l_return_status);
1090
1091 IF l_debug_on THEN
1092 wsh_debug_sv.log (l_module_name,'Return status from Process Int Freight Costs',l_return_status);
1093 END IF;
1094
1095 IF( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1096 raise freight_cost_processing_error;
1097 END IF;
1098
1099 END IF;
1100 END IF; -- if action code = create }
1101
1102
1103 END LOOP; -- for det_id
1104
1105 -- For the newly created delivery details, call detail_to_delivery to assign them to delivery
1106 IF l_debug_on THEN
1107 wsh_debug_sv.log (l_module_name, 'Assign table count', l_new_detail_ids.count);
1108 END IF;
1109
1110 IF(l_new_detail_ids.count > 0) THEN
1111 WSH_DELIVERY_DETAILS_GRP.Detail_To_Delivery(
1112 p_api_version =>1.0,
1113 p_init_msg_list => l_init_msg_list,
1114 p_validation_level => l_validation_level,
1115 p_commit => l_commit,
1116 x_return_status => l_return_status,
1117 x_msg_count => l_msg_count,
1118 x_msg_data => l_msg_data,
1119 p_TabOfDelDets => l_new_detail_ids,
1120 p_action => 'ASSIGN',
1121 p_delivery_id => p_new_delivery_id,
1122 p_delivery_name => l_delivery_name
1123 );
1124
1125 IF l_debug_on THEN
1126 wsh_debug_sv.log (l_module_name, 'Return status from Detail to delivery', l_return_status);
1127 wsh_debug_sv.log (l_module_name, 'Detail to Delivery api msg count', l_msg_count);
1128 wsh_debug_sv.log (l_module_name, 'Detail to Delivery api msg data', l_msg_data);
1129 END IF;
1130
1131 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1132 raise new_assignment_failed;
1133 END IF;
1134 END IF;
1135
1136 IF l_debug_on THEN
1137 wsh_debug_sv.pop(l_module_name);
1138 END IF;
1139 EXCEPTION
1140 WHEN FND_API.G_EXC_ERROR THEN
1141 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1142 IF l_debug_on THEN
1143 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1144 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FND_API.G_EXC_ERROR');
1145 END IF;
1146 WHEN create_cont_instance_failed THEN
1147 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1148 IF l_debug_on THEN
1149 WSH_DEBUG_SV.logmsg(l_module_name,'create_cont_instance_failed exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1150 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:create_cont_instance_failed');
1151 END IF;
1152 WHEN invalid_input THEN
1153 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1154 IF l_debug_on THEN
1155 WSH_DEBUG_SV.logmsg(l_module_name,'invalid_input exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1156 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_input');
1157 END IF;
1158 WHEN create_lines_failed THEN
1159 FND_MESSAGE.SET_NAME('WSH', 'WSH_CREATE_LINES_FAILED');
1160 FND_MESSAGE.SET_TOKEN('DET_INT',del_det_int_rec.delivery_detail_interface_id);
1161 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1162 IF l_debug_on THEN
1163 WSH_DEBUG_SV.logmsg(l_module_name,'create_lines_failed exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1164 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:create_lines_failed');
1165 END IF;
1166 WHEN new_assignment_failed THEN
1167 FND_MESSAGE.SET_NAME('WSH','WSH_DEL_ASSGN_ERROR');
1168 FND_MESSAGE.SET_TOKEN('DLVY', p_new_delivery_id);
1169 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1170 IF l_debug_on THEN
1171 WSH_DEBUG_SV.logmsg(l_module_name,'new_assignment_failed exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1172 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:new_assignment_failed');
1173 END IF;
1174 WHEN freight_cost_processing_error THEN
1175 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1176 IF l_debug_on THEN
1177 WSH_DEBUG_SV.logmsg(l_module_name,'freight_cost_processing_error exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1178 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:freight_cost_processing_error');
1179 END IF;
1180 WHEN Others THEN
1181 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1182 IF l_debug_on THEN
1183 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
1184 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1185 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1186 END IF;
1187 END Process_Non_Splits;
1188
1189 PROCEDURE process_splits(
1190 p_delivery_interface_id IN NUMBER,
1191 p_delivery_id IN NUMBER,
1192 x_return_status OUT NOCOPY VARCHAR2
1193 ) IS
1194 -- local variables
1195 l_new_split_detail_id NUMBER;
1196 l_base_req_qty NUMBER;
1197 l_return_status VARCHAR2(30);
1198 l_delivery_id NUMBER;
1199 l_det_freight_costs NUMBER;
1200 l_ser_count NUMBER := 0;
1201 l_det_index NUMBER := 0;
1202 l_add_flag VARCHAR2(1) := 'T';
1203 l_total_req_qty NUMBER := 0;
1204 l_total_shp_qty NUMBER := 0;
1205 l_total_cc_qty NUMBER := 0;
1206 l_dd_count NUMBER := 0;
1207 l_src_line_count NUMBER := 0;
1208 l_prev_int_rec del_det_int_cur%ROWTYPE;
1209 l_number_of_errors NUMBER := 0;
1210 l_number_of_warnings NUMBER := 0;
1211 l_num_of_dtl NUMBER := 0;
1212 l_serial_range_tab WSH_GLBL_VAR_STRCT_GRP.ddSerialRangeTabType;
1213 l_index NUMBER;
1214 --Cursors
1215 CURSOR c_det_count_cur(p_detail_id NUMBER, p_dlvy_int_id NUMBER) IS
1216 SELECT COUNT(*)
1217 FROM wsh_del_details_interface wddi, wsh_del_assgn_interface wdai
1218 WHERE wddi.delivery_Detail_interface_id = wdai.delivery_detail_interface_id
1219 AND wdai.delivery_interface_id = p_dlvy_int_id
1220 AND wddi.delivery_detail_id=p_detail_id
1221 AND WDAI.INTERFACE_ACTION_CODE = '94X_INBOUND'
1222 AND WDDI.INTERFACE_ACTION_CODE = '94X_INBOUND';
1223
1224
1225 --exceptions
1226 invalid_input EXCEPTION;
1227 freight_cost_processing_error EXCEPTION;
1228 --
1229 l_debug_on BOOLEAN;
1230 --
1231 l_module_name CONSTANT VARCHAR2(100)
1232 := 'wsh.plsql.' || g_pkg_name || '.' || 'PROCESS_SPLITS';
1233 --
1234 BEGIN
1235 --
1236 l_debug_on := wsh_debug_interface.g_debug;
1237
1238 --
1239 IF l_debug_on IS NULL THEN
1240 l_debug_on := wsh_debug_sv.is_debug_enabled;
1241 END IF;
1242
1243 --
1244 IF l_debug_on THEN
1245 wsh_debug_sv.push(l_module_name, 'Process_Splits');
1246 wsh_debug_sv.LOG(l_module_name, 'Delivery Interface Id',
1247 p_delivery_interface_id);
1248 wsh_debug_sv.LOG(l_module_name, 'Delivery Id', p_delivery_id);
1249 END IF;
1250
1251 x_return_status := wsh_util_core.g_ret_sts_success;
1252
1253 IF (p_delivery_interface_id IS NULL) THEN
1254 RAISE invalid_input;
1255 END IF;
1256
1257 IF (p_delivery_id IS NOT NULL) THEN
1258 l_delivery_id := p_delivery_id;
1259 ELSE
1260 RAISE invalid_input;
1261 END IF;
1262
1263 /* New Logic for splitting at supplier instance 945 inbound. kvenkate.
1264 For a given delivery_detail_id ,
1265 if more than one record exists in wsh_del_details_interface, then
1266 if those records have the same not null source_line_id, then do not split.
1267 if those records have distinct source_line_id, then split. */
1268
1269 FOR del_det_int_rec IN del_det_int_cur(NULL, p_delivery_interface_id) LOOP
1270 IF l_debug_on THEN
1271 wsh_debug_sv.LOG(l_module_name, 'l_src_line_count',
1272 l_src_line_count);
1273 wsh_debug_sv.LOG(l_module_name, 'l_dd_count', l_dd_count);
1274 wsh_debug_sv.log(l_module_name, 'Current Delivery_Detail_Id', del_det_int_rec.delivery_detail_id);
1275 wsh_debug_sv.log(l_module_name, 'Current del.detail_int_id', del_det_int_rec.delivery_detail_interface_id);
1276 wsh_debug_sv.log(l_module_name, 'Previous Delivery_detail_id', l_prev_int_rec.delivery_detail_id);
1277 wsh_debug_sv.log(l_module_name, 'Previous del.detail_int_id', l_prev_int_rec.delivery_detail_interface_id);
1278 wsh_debug_sv.log(l_module_name, 'Total req qty', l_total_req_qty);
1279 wsh_debug_sv.log(l_module_name, 'Total shp qty', l_total_shp_qty);
1280 wsh_debug_sv.log(l_module_name, 'total cc qty', l_total_cc_qty);
1281 END IF;
1282
1283 IF NVL(l_prev_int_rec.delivery_detail_id, '-9999') =
1284 del_det_int_rec.delivery_detail_id THEN
1285 if l_debug_on then
1286 wsh_debug_sv.logmsg(l_module_name, 'Same Delivery Detail');
1287 end if;
1288 l_dd_count := l_dd_count + 1;
1289
1290 IF NVL(l_prev_int_rec.source_line_id, '-9999') =
1291 del_det_int_rec.source_line_id THEN
1292 if l_debug_on then
1293 wsh_debug_sv.logmsg(l_module_name, 'Same Source Line');
1294 end if;
1295 NULL;
1296 ELSE
1297 -- split dd
1298 split_delivery_detail(p_delivery_detail_id => del_det_int_rec.delivery_detail_id,
1299 p_qty_to_split => l_total_req_qty,
1300 x_new_detail_id => l_new_split_detail_id,
1301 x_return_status => l_return_status);
1302
1303 wsh_util_core.api_post_call(p_return_status => l_return_status,
1304 x_num_warnings => l_number_of_warnings,
1305 x_num_errors => l_number_of_errors);
1306
1307 -- For the newly created base delivery detail, make updates
1308 -- Add the record with new detail id to the global table
1309 if l_debug_on then
1310 wsh_debug_sv.log(l_module_name, 'l_new_split_detail_id', l_new_split_detail_id);
1311 end if;
1312
1313 If l_new_split_detail_id IS NOT NULL THEN
1314 l_prev_int_rec.delivery_detail_id := l_new_split_detail_id;
1315 l_index := l_serial_range_tab.first;
1316 while l_index is not null loop
1317 l_serial_range_tab(l_index).delivery_detail_id := l_new_split_detail_id;
1318 l_index := l_serial_range_tab.next(l_index);
1319 end loop;
1320 end if;
1321
1322 --
1323 l_prev_int_rec.requested_quantity := l_total_req_qty;
1324 l_prev_int_rec.shipped_quantity := l_total_shp_qty;
1325 l_prev_int_rec.cycle_count_quantity := l_total_cc_qty;
1326 --
1327
1328 add_to_update_table(p_del_det_int_rec => l_prev_int_rec,
1329 p_update_mode => 'UPDATE', p_delivery_id => l_delivery_id,
1330 x_return_status => l_return_status);
1331
1332 IF (l_return_status <> wsh_util_core.g_ret_sts_success) THEN
1333 RAISE fnd_api.g_exc_error;
1334 END IF;
1335
1336 --
1337 -- g_serial_range_tab := g_serial_range_tab + l_serial_range_tab;
1338 add_to_serial_table(p_serial_range_tab => l_serial_range_tab);
1339 l_serial_range_tab.delete;
1340 l_total_req_qty := 0;
1341 l_total_shp_qty := 0;
1342 l_total_cc_qty := 0;
1343 l_src_line_count := l_src_line_count + 1;
1344 END IF;
1345 ELSE
1346
1347
1348 IF l_src_line_count > 1 --count(distinct sn) for prev. dd id)
1349 THEN
1350 -- split dd
1351 split_delivery_detail(p_delivery_detail_id => l_prev_int_rec.delivery_detail_id,
1352 p_qty_to_split => l_total_req_qty,
1353 x_new_detail_id => l_new_split_detail_id,
1354 x_return_status => l_return_status);
1355 wsh_util_core.api_post_call(p_return_status => l_return_status,
1356 x_num_warnings => l_number_of_warnings,
1357 x_num_errors => l_number_of_errors);
1358
1359 -- For the newly created base delivery detail, make updates
1360 -- Add the record with new detail id to the global table
1361 if l_debug_on then
1362 wsh_debug_sv.log(l_module_name, 'l_new_split_detail_id', l_new_split_detail_id);
1363 end if;
1364
1365 If l_new_split_detail_id IS NOT NULL THEN
1366 l_prev_int_rec.delivery_detail_id := l_new_split_detail_id;
1367 l_index := l_serial_range_tab.first;
1368 while l_index is not null loop
1369 l_serial_range_tab(l_index).delivery_detail_id := l_new_split_detail_id;
1370 l_index := l_serial_range_tab.next(l_index);
1371 end loop;
1372 end if;
1373
1374 l_prev_int_rec.requested_quantity := l_total_req_qty;
1375 l_prev_int_rec.shipped_quantity := l_total_shp_qty;
1376 l_prev_int_rec.cycle_count_quantity := l_total_cc_qty;
1377 --
1378 add_to_update_table(p_del_det_int_rec => l_prev_int_rec,
1379 p_update_mode => 'UPDATE', p_delivery_id => l_delivery_id,
1380 x_return_status => l_return_status);
1381
1382 IF (l_return_status <> wsh_util_core.g_ret_sts_success) THEN
1383 RAISE fnd_api.g_exc_error;
1384 END IF;
1385
1386 add_to_serial_table(p_serial_range_tab => l_serial_range_tab);
1387 l_serial_range_tab.delete;
1388
1389
1390 ELSIF l_dd_count > 1 -- count(prev dd id) > 1
1391 THEN
1392 -- do not split
1393 --
1394 l_prev_int_rec.requested_quantity := l_total_req_qty;
1395 l_prev_int_rec.shipped_quantity := l_total_shp_qty;
1396 l_prev_int_rec.cycle_count_quantity := l_total_cc_qty;
1397 --
1398 add_to_update_table(p_del_det_int_rec => l_prev_int_rec,
1399 p_update_mode => 'UPDATE', p_delivery_id => l_delivery_id,
1400 x_return_status => l_return_status);
1401
1402 IF (l_return_status <> wsh_util_core.g_ret_sts_success) THEN
1403 RAISE fnd_api.g_exc_error;
1404 END IF;
1405
1406 add_to_serial_table(p_serial_range_tab => l_serial_range_tab);
1407 l_serial_range_tab.delete;
1408 ELSE
1409 OPEN c_det_count_cur(del_det_int_rec.delivery_detail_id, p_delivery_interface_id);
1410 FETCH c_det_count_cur INTO l_num_of_dtl;
1411 CLOSE c_det_count_cur;
1412 if l_debug_on THEN
1413 wsh_debug_sv.log(l_module_name, 'l_num_of_dtl', l_num_of_dtl);
1414 end if;
1415 IF l_num_of_dtl = 1 THEN
1416 goto detail_loop_end;
1417 END IF;
1418 END IF;
1419
1420 l_total_req_qty := 0;
1421 l_total_shp_qty := 0;
1422 l_total_cc_qty := 0;
1423 --
1424 l_dd_count := 1;
1425 l_src_line_count := 1;
1426 END IF;
1427
1428 l_total_req_qty := l_total_req_qty + del_det_int_rec.requested_quantity;
1429 l_total_shp_qty := l_total_shp_qty + del_det_int_rec.shipped_quantity;
1430 l_total_cc_qty := l_total_cc_qty + del_det_int_rec.cycle_count_quantity;
1431
1432 --
1433 IF del_det_int_rec.serial_number IS NOT NULL THEN
1434 IF l_debug_on THEN
1435 wsh_debug_sv.logmsg(l_module_name,
1436 'Adding serial number to serial table');
1437 END IF;
1438
1439 l_ser_count := l_serial_range_tab.COUNT;
1440 l_serial_range_tab(l_ser_count + 1).delivery_detail_id :=
1441 del_det_int_rec.delivery_detail_id;
1442 l_serial_range_tab(l_ser_count + 1).from_serial_number :=
1443 del_det_int_rec.serial_number;
1444 l_serial_range_tab(l_ser_count + 1).to_serial_number :=
1445 del_det_int_rec.to_serial_number;
1446 l_serial_range_tab(l_ser_count + 1).quantity :=
1447 del_det_int_rec.shipped_quantity;
1448 -- Since serial numbers added to serial table, should not be added in the update record
1449 del_det_int_rec.serial_number := NULL;
1450 del_det_int_rec.to_serial_number := NULL;
1451 END IF;
1452
1453 -- Process interface freight costs
1454 SELECT COUNT(*)
1455 INTO l_det_freight_costs
1456 FROM wsh_freight_costs_interface
1457 WHERE delivery_detail_interface_id =
1458 del_det_int_rec.delivery_detail_interface_id
1459 AND INTERFACE_ACTION_CODE = '94X_INBOUND';
1460
1461 IF l_debug_on THEN
1462 wsh_debug_sv.LOG(l_module_name, 'Interface freight count',
1463 l_det_freight_costs);
1464 END IF;
1465
1466 IF (l_det_freight_costs > 0) THEN
1467 IF l_debug_on THEN
1468 wsh_debug_sv.LOG(l_module_name,
1469 'Processing freight costs for interface detail',
1470 del_det_int_rec.delivery_detail_interface_id);
1471 END IF;
1472
1473 process_int_freight_costs(p_del_detail_interface_id => del_det_int_rec.delivery_detail_interface_id,
1474 x_return_status => l_return_status);
1475
1476 IF l_debug_on THEN
1477 wsh_debug_sv.LOG(l_module_name,
1478 'Process_Int_Freight_Costs l_return_status', l_return_status);
1479 END IF;
1480
1481 IF (l_return_status <> wsh_util_core.g_ret_sts_success) THEN
1482 RAISE freight_cost_processing_error;
1483 END IF;
1484 END IF;
1485
1486 <<detail_loop_end>>
1487 l_prev_int_rec := del_det_int_rec;
1488
1489 END LOOP; -- for del_det_int_rec in cursor
1490
1491 IF l_debug_on THEN
1492 wsh_debug_sv.logmsg(l_module_name, 'Outside the Loop');
1493 wsh_debug_sv.LOG(l_module_name, 'l_src_line_count', l_src_line_count);
1494 wsh_debug_sv.LOG(l_module_name, 'l_dd_count', l_dd_count);
1495 wsh_debug_sv.log(l_module_name, 'Total req qty', l_total_req_qty);
1496 wsh_debug_sv.log(l_module_name, 'Total shp qty', l_total_shp_qty);
1497 wsh_debug_sv.log(l_module_name, 'total cc qty', l_total_cc_qty);
1498 END IF;
1499
1500 IF l_src_line_count > 1 THEN
1501 split_delivery_detail(
1502 p_delivery_detail_id => l_prev_int_rec.delivery_detail_id,
1503 p_qty_to_split => l_total_req_qty,
1504 x_new_detail_id => l_new_split_detail_id,
1505 x_return_status => l_return_status);
1506 --
1507 wsh_util_core.api_post_call(
1508 p_return_status => l_return_status,
1509 x_num_warnings => l_number_of_warnings,
1510 x_num_errors => l_number_of_errors);
1511
1512 -- For the newly created base delivery detail, make updates
1513 -- Add the record with new detail id to the global table
1514 if l_debug_on then
1515 wsh_debug_sv.log(l_module_name, 'l_new_split_detail_id', l_new_split_detail_id);
1516 end if;
1517 If l_new_split_detail_id IS NOT NULL THEN
1518 l_prev_int_rec.delivery_detail_id := l_new_split_detail_id;
1519 end if;
1520 --
1521 l_prev_int_rec.requested_quantity := l_total_req_qty;
1522 l_prev_int_rec.shipped_quantity := l_total_shp_qty;
1523 l_prev_int_rec.cycle_count_quantity := l_total_cc_qty;
1524
1525 add_to_update_table(
1526 p_del_det_int_rec => l_prev_int_rec,
1527 p_update_mode => 'UPDATE',
1528 p_delivery_id => l_delivery_id,
1529 x_return_status => l_return_status);
1530
1531 IF (l_return_status <> wsh_util_core.g_ret_sts_success) THEN
1532 RAISE fnd_api.g_exc_error;
1533 END IF;
1534 --
1535 add_to_serial_table(p_serial_range_tab => l_serial_range_tab);
1536 l_serial_range_tab.delete;
1537 ELSIF l_dd_count > 1 THEN
1538 l_prev_int_rec.requested_quantity := l_total_req_qty;
1539 l_prev_int_rec.shipped_quantity := l_total_shp_qty;
1540 l_prev_int_rec.cycle_count_quantity := l_total_cc_qty;
1541
1542 add_to_update_table(p_del_det_int_rec => l_prev_int_rec,
1543 p_update_mode => 'UPDATE', p_delivery_id => l_delivery_id,
1544 x_return_status => l_return_status);
1545
1546 IF (l_return_status <> wsh_util_core.g_ret_sts_success) THEN
1547 RAISE fnd_api.g_exc_error;
1548 END IF;
1549 --
1550 add_to_serial_table(p_serial_range_tab => l_serial_range_tab);
1551 l_serial_range_tab.delete;
1552 END IF;
1553
1554 IF l_debug_on THEN
1555 wsh_debug_sv.pop(l_module_name);
1556 END IF;
1557
1558 EXCEPTION
1559 WHEN fnd_api.g_exc_error THEN
1560 x_return_status := fnd_api.g_ret_sts_error;
1561 --
1562 IF l_debug_on THEN
1563 wsh_debug_sv.logmsg(l_module_name,
1564 'FND_API.G_EXC_ERROR exception has occured.',
1565 wsh_debug_sv.c_excep_level);
1566 wsh_debug_sv.pop(l_module_name, 'EXCEPTION:FND_API.G_EXC_ERROR');
1567 END IF;
1568 --
1569 --
1570 WHEN fnd_api.g_exc_unexpected_error THEN
1571 x_return_status := fnd_api.g_ret_sts_unexp_error;
1572 --
1573 IF l_debug_on THEN
1574 wsh_debug_sv.logmsg(l_module_name,
1575 'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',
1576 wsh_debug_sv.c_excep_level);
1577 wsh_debug_sv.pop(l_module_name,
1578 'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1579 END IF;
1580 --
1581 WHEN wsh_util_core.g_exc_warning THEN
1582 x_return_status := wsh_util_core.g_ret_sts_warning;
1583 --
1584 IF l_debug_on THEN
1585 wsh_debug_sv.logmsg(l_module_name,
1586 'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',
1587 wsh_debug_sv.c_excep_level);
1588 wsh_debug_sv.pop(l_module_name,
1589 'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
1590 END IF;
1591 --
1592 WHEN invalid_input THEN
1593 x_return_status := wsh_util_core.g_ret_sts_error;
1594
1595 IF l_debug_on THEN
1596 wsh_debug_sv.logmsg(l_module_name,
1597 'invalid_input exception has occured.',
1598 wsh_debug_sv.c_excep_level);
1599 wsh_debug_sv.pop(l_module_name, 'EXCEPTION:invalid_input');
1600 END IF;
1601 WHEN freight_cost_processing_error THEN
1602 x_return_status := wsh_util_core.g_ret_sts_error;
1603
1604 IF l_debug_on THEN
1605 wsh_debug_sv.logmsg(l_module_name,
1606 'freight_cost_processing_error exception has occured.',
1607 wsh_debug_sv.c_excep_level);
1608 wsh_debug_sv.pop(l_module_name,
1609 'EXCEPTION:freight_cost_processing_error');
1610 END IF;
1611 WHEN OTHERS THEN
1612 x_return_status := wsh_util_core.g_ret_sts_unexp_error;
1613
1614 IF l_debug_on THEN
1615 wsh_debug_sv.logmsg(l_module_name,
1616 'Unexpected error has occured. Oracle error message is '
1617 || SQLERRM,
1618 wsh_debug_sv.c_unexpec_err_level);
1619 wsh_debug_sv.pop(l_module_name, 'EXCEPTION:OTHERS');
1620 END IF;
1621 END process_splits;
1622
1623
1624 PROCEDURE Pack_Lines(
1625 x_return_status OUT NOCOPY VARCHAR2) IS
1626 -- variables
1627 l_return_status VARCHAR2(30);
1628 l_pack_status VARCHAR2(30);
1629 l_del_detail_tab WSH_UTIL_CORE.id_tab_type;
1630
1631 --exceptions
1632 packing_failed exception;
1633
1634 --
1635 l_debug_on BOOLEAN;
1636 --
1637 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PACK_LINES';
1638 --
1639 BEGIN
1640 --
1641 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1642 --
1643 IF l_debug_on IS NULL
1644 THEN
1645 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1646 END IF;
1647 --
1648 IF l_debug_on THEN
1649 wsh_debug_sv.push(l_module_name, 'Pack_Lines');
1650 wsh_debug_sv.log (l_module_name, 'Packing Table Count', G_Packing_Detail_Tab.count);
1651 END IF;
1652
1653 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1654
1655 FOR i in 1..G_Packing_Detail_Tab.count LOOP
1656 -- Need to delete the table because we pass only one delivery detail per call
1657 l_del_detail_tab.delete;
1658 l_del_detail_tab(1) := G_Packing_Detail_Tab(i).Delivery_Detail_Id;
1659 -- call packing api
1660
1661 IF l_debug_on THEN
1662 wsh_debug_sv.logmsg(l_module_name, 'calling container_actions.assign_detail');
1663 wsh_debug_sv.log (l_module_name, 'Delivery detail id', G_Packing_Detail_Tab(i).Delivery_Detail_Id);
1664 wsh_debug_sv.log (l_module_name, 'Container instance id', G_Packing_Detail_Tab(i).Parent_Delivery_Detail_Id);
1665 END IF;
1666
1667 WSH_CONTAINER_ACTIONS.Assign_Detail(
1668 p_container_instance_id => G_Packing_Detail_Tab(i).Parent_Delivery_Detail_Id,
1669 p_del_detail_tab => l_del_detail_tab,
1670 x_pack_status => l_pack_status,
1671 x_return_status => l_return_status);
1672
1673 IF l_debug_on THEN
1674 wsh_debug_sv.log (l_module_name,'Return Status from Assign_Detail', l_return_status);
1675 END IF;
1676
1677 IF(l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1678 FND_MESSAGE.SET_NAME('WSH','WSH_PACKING_ERROR');
1679 FND_MESSAGE.SET_TOKEN('DEL_DETAIL', G_Packing_Detail_Tab(i).Delivery_Detail_Id);
1680 raise packing_failed;
1681 END IF;
1682 END LOOP; -- for i in 1..G_Packing
1683
1684 IF l_debug_on THEN
1685 wsh_debug_sv.pop(l_module_name);
1686 END IF;
1687
1688 EXCEPTION
1689 WHEN packing_failed THEN
1690 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1691 IF l_debug_on THEN
1692 WSH_DEBUG_SV.logmsg(l_module_name,'packing_failed exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1693 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:packing_failed');
1694 END IF;
1695 WHEN Others THEN
1696 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1697 IF l_debug_on THEN
1698 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
1699 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1700 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1701 END IF;
1702
1703 END Pack_Lines;
1704
1705
1706 /*
1707 -----------------------------------------------------------------------------
1708 PROCEDURE : Process_Interfaced_Deliveries
1709 PARAMETERS : p_delivery_interface_id IN NUMBER,
1710 p_action_code CREATE or UPDATE
1711 x_dlvy_id The delivery id that is created
1712 x_return_status OUT VARCHAR2)
1713
1714 DESCRIPTION :
1715 -- This procedure is called by the wrapper, to process the interfaced deliveries
1716 -- If the action is CREATE, then the interface record is fetched and a base
1717 record is created by calling the public api
1718 -- If the action is UPDATE, then , using the interface record values, the
1719 base record is updated by calling the public api.
1720
1721 ------------------------------------------------------------------------------
1722 */
1723
1724 PROCEDURE Process_Interfaced_Deliveries(
1725 p_delivery_interface_id IN NUMBER,
1726 p_action_code IN VARCHAR2,
1727 x_dlvy_id OUT NOCOPY NUMBER,
1728 x_return_status OUT NOCOPY VARCHAR2) IS
1729
1730 -- Bug 2753330
1731 l_in_rec WSH_DELIVERIES_GRP.Del_In_Rec_Type;
1732 l_dlvy_attr_tab WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;
1733 l_dlvy_out_rec_tab WSH_DELIVERIES_GRP.Del_Out_Tbl_Type;
1734 l_index NUMBER;
1735 l_number_of_errors NUMBER := 0;
1736 l_number_of_warnings NUMBER := 0;
1737 l_api_version_number NUMBER := 1.0;
1738 --
1739 l_init_msg_list VARCHAR2(30) := NULL;
1740 l_return_status VARCHAR2(30);
1741 l_msg_count NUMBER;
1742 l_msg_data VARCHAR2(3000);
1743
1744 l_del_freight_costs NUMBER;
1745 l_delivery_id NUMBER;
1746
1747 l_delivery_name VARCHAR2(150);
1748 x_delivery_name VARCHAR2(150);
1749
1750 l_curr_ship_method VARCHAR2(30);
1751
1752 l_loc_interface_error_rec WSH_INTERFACE_VALIDATIONS_PKG.interface_errors_rec_type;
1753
1754 CURSOR del_int_cur IS
1755 SELECT
1756 POOLED_SHIP_TO_LOCATION_CODE,
1757 ULTIMATE_DROPOFF_LOCATION_CODE,
1758 CUSTOMER_NUMBER,
1759 FOB_LOCATION_CODE,
1760 INITIAL_PICKUP_LOCATION_CODE,
1761 INTMED_SHIP_TO_LOCATION_CODE,
1762 ORGANIZATION_CODE,
1763 BATCH_ID,
1764 BILL_FREIGHT_TO,
1765 BOOKING_NUMBER
1766 CARRIED_BY,
1767 COD_AMOUNT,
1768 COD_CHARGE_PAID_BY,
1769 COD_CURRENCY_CODE,
1770 COD_REMIT_TO,
1771 DESCRIPTION,
1772 ENTRY_NUMBER,
1773 FTZ_NUMBER,
1774 HASH_VALUE,
1775 IN_BOND_CODE,
1776 LOADING_ORDER_FLAG,
1777 LOADING_SEQUENCE,
1778 NUMBER_OF_LPN,
1779 PORT_OF_DISCHARGE,
1780 PORT_OF_LOADING,
1781 PROBLEM_CONTACT_REFERENCE,
1782 REASON_OF_TRANSPORT,
1783 ROUTED_EXPORT_TXN,
1784 ROUTING_INSTRUCTIONS,
1785 SERVICE_CONTRACT,
1786 SHIPPING_MARKS,
1787 SOURCE_HEADER_ID,
1788 CARRIER_CODE,
1789 NAME,
1790 PLANNED_FLAG,
1791 STATUS_CODE,
1792 INITIAL_PICKUP_DATE,
1793 INITIAL_PICKUP_LOCATION_ID,
1794 ULTIMATE_DROPOFF_LOCATION_ID,
1795 ULTIMATE_DROPOFF_DATE,
1796 CUSTOMER_ID,
1797 INTMED_SHIP_TO_LOCATION_ID,
1798 POOLED_SHIP_TO_LOCATION_ID,
1799 FREIGHT_TERMS_CODE,
1800 FOB_CODE,
1801 FOB_LOCATION_ID,
1802 WAYBILL,
1803 LOAD_TENDER_FLAG,
1804 ACCEPTANCE_FLAG,
1805 ACCEPTED_BY,
1806 ACCEPTED_DATE,
1807 ACKNOWLEDGED_BY,
1808 CONFIRMED_BY,
1809 ASN_DATE_SENT,
1810 ASN_STATUS_CODE,
1811 ASN_SEQ_NUMBER,
1812 GROSS_WEIGHT,
1813 NET_WEIGHT,
1814 WEIGHT_UOM_CODE,
1815 VOLUME,
1816 VOLUME_UOM_CODE,
1817 ADDITIONAL_SHIPMENT_INFO,
1818 ATTRIBUTE_CATEGORY,
1819 ATTRIBUTE1,
1820 ATTRIBUTE2,
1821 ATTRIBUTE3,
1822 ATTRIBUTE4,
1823 ATTRIBUTE5,
1824 ATTRIBUTE6,
1825 ATTRIBUTE7,
1826 ATTRIBUTE8,
1827 ATTRIBUTE9,
1828 ATTRIBUTE10,
1829 ATTRIBUTE11,
1830 ATTRIBUTE12,
1831 ATTRIBUTE13,
1832 ATTRIBUTE14,
1833 ATTRIBUTE15,
1834 TP_ATTRIBUTE_CATEGORY,
1835 TP_ATTRIBUTE1,
1836 TP_ATTRIBUTE2,
1837 TP_ATTRIBUTE3,
1838 TP_ATTRIBUTE4,
1839 TP_ATTRIBUTE5,
1840 TP_ATTRIBUTE6,
1841 TP_ATTRIBUTE7,
1842 TP_ATTRIBUTE8,
1843 TP_ATTRIBUTE9,
1844 TP_ATTRIBUTE10,
1845 TP_ATTRIBUTE11,
1846 TP_ATTRIBUTE12,
1847 TP_ATTRIBUTE13,
1848 TP_ATTRIBUTE14,
1849 TP_ATTRIBUTE15,
1850 CREATION_DATE,
1851 CREATED_BY,
1852 LAST_UPDATE_DATE,
1853 LAST_UPDATED_BY,
1854 LAST_UPDATE_LOGIN,
1855 PROGRAM_APPLICATION_ID,
1856 PROGRAM_ID,
1857 PROGRAM_UPDATE_DATE,
1858 REQUEST_ID,
1859 GLOBAL_ATTRIBUTE_CATEGORY,
1860 GLOBAL_ATTRIBUTE1,
1861 GLOBAL_ATTRIBUTE2,
1862 GLOBAL_ATTRIBUTE3,
1863 GLOBAL_ATTRIBUTE4,
1864 GLOBAL_ATTRIBUTE5,
1865 GLOBAL_ATTRIBUTE6,
1866 GLOBAL_ATTRIBUTE7,
1867 GLOBAL_ATTRIBUTE8,
1868 GLOBAL_ATTRIBUTE9,
1869 GLOBAL_ATTRIBUTE10,
1870 GLOBAL_ATTRIBUTE11,
1871 GLOBAL_ATTRIBUTE12,
1872 GLOBAL_ATTRIBUTE13,
1873 GLOBAL_ATTRIBUTE14,
1874 GLOBAL_ATTRIBUTE15,
1875 GLOBAL_ATTRIBUTE16,
1876 GLOBAL_ATTRIBUTE17,
1877 GLOBAL_ATTRIBUTE18,
1878 GLOBAL_ATTRIBUTE19,
1879 GLOBAL_ATTRIBUTE20,
1880 INTERFACE_ACTION_CODE,
1881 LOCK_FLAG,
1882 PROCESS_FLAG,
1883 PROCESS_MODE,
1884 DELETE_FLAG,
1885 PROCESS_STATUS_FLAG,
1886 CURRENCY_CODE,
1887 DELIVERY_TYPE,
1888 ORGANIZATION_ID,
1889 CARRIER_ID,
1890 SHIP_METHOD_CODE,
1891 DOCK_CODE,
1892 CONFIRM_DATE,
1893 DELIVERY_INTERFACE_ID,
1894 DELIVERY_ID,
1895 SERVICE_LEVEL,
1896 MODE_OF_TRANSPORT,
1897 -- J: W/V Changes
1898 WV_FROZEN_FLAG,
1899 --Bug 3458160
1900 SHIPMENT_DIRECTION,
1901 DELIVERED_DATE
1902 FROM WSH_NEW_DEL_INTERFACE
1903 WHERE delivery_interface_id = p_delivery_interface_id
1904 AND INTERFACE_ACTION_CODE = '94X_INBOUND';
1905
1906 cursor get_carrier ( p_carrier_name in varchar2 ) is
1907 select distinct carrier_id , manifesting_enabled_flag
1908 from wsh_carriers_v
1909 where carrier_name = p_carrier_name;
1910
1911 cursor l_get_enforce_shp_method_csr is
1912 select enforce_ship_method
1913 from wsh_global_parameters;
1914
1915 cursor l_shp_method_org_csr(p_ship_method_code IN VARCHAR2,
1916 p_organization_id IN NUMBER) is
1917 select 'Y'
1918 from wsh_carrier_services wcs,
1919 wsh_org_carrier_services wocs
1920 where wcs.carrier_service_id = wocs.carrier_service_id
1921 and wcs.ship_method_code = p_ship_method_code
1922 and wocs.organization_id = p_organization_id;
1923
1924 --exceptions
1925 invalid_ship_method exception;
1926 freight_cost_processing_error exception;
1927 --
1928 l_carrier_id NUMBER;
1929 l_manifesting_enabled_flag VARCHAR2(100);
1930 l_enforce_ship_method VARCHAR2(100);
1931 l_log_error_flag VARCHAR2(100);
1932 l_valid_shp_method_flag VARCHAR2(100);
1933 l_txn_type VARCHAR2(100);
1934
1935 --
1936 l_debug_on BOOLEAN;
1937 --
1938 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PROCESS_INTERFACED_DELIVERIES';
1939 --
1940 BEGIN
1941 --
1942 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1943 --
1944 IF l_debug_on IS NULL
1945 THEN
1946 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1947 END IF;
1948 --
1949 IF l_debug_on THEN
1950 wsh_debug_sv.push(l_module_name,'Process_Interfaced_Deliveries');
1951 wsh_debug_sv.log (l_module_name,'Delivery interface Id', p_delivery_interface_id);
1952 wsh_debug_sv.log (l_module_name,'Action Code', p_action_code);
1953 wsh_debug_sv.log (l_module_name, 'Warehouse type', WSH_INBOUND_SHIP_ADVICE_PKG.G_WAREHOUSE_TYPE);
1954 END IF;
1955
1956 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1957
1958 IF(p_delivery_interface_id IS NULL)
1959 THEN
1960 raise fnd_api.g_exc_error;
1961 END IF;
1962
1963 IF p_action_code NOT IN ('CREATE', 'UPDATE')
1964 THEN
1965 FND_MESSAGE.SET_NAME('WSH', 'WSH_INVALID_ACTION_CODE');
1966 FND_MESSAGE.SET_TOKEN('ACT_CODE',p_action_code );
1967 RAISE fnd_api.g_exc_error;
1968 END IF;
1969
1970 l_index := 1;
1971
1972 -- get the delivery interface record
1973 --initialize the l_delivery_info record with the interface record values
1974 FOR l_del_int_rec IN del_int_cur
1975 LOOP
1976 -- {
1977
1978 l_enforce_ship_method := NULL;
1979 open l_get_enforce_shp_method_csr;
1980 fetch l_get_enforce_shp_method_csr into l_enforce_ship_method;
1981 close l_get_enforce_shp_method_csr;
1982
1983 IF l_debug_on THEN
1984 wsh_debug_sv.log (l_module_name, ' Ship Method ', l_del_int_rec.ship_method_code);
1985 END IF;
1986 -- need to send delivery id and name only for update
1987 --Bug Bug 3458160
1988 IF(p_action_code = 'UPDATE') THEN
1989 l_dlvy_attr_tab(l_index).DELIVERY_ID := l_del_int_rec.delivery_id;
1990 l_dlvy_attr_tab(l_index).NAME := l_del_int_rec.name;
1991 l_dlvy_attr_tab(l_index).delivered_date :=
1992 l_del_int_rec.delivered_date;
1993 ELSIF p_action_code = 'CREATE' THEN
1994 l_dlvy_attr_tab(l_index).shipment_direction :=
1995 l_del_int_rec.shipment_direction;
1996 IF (WSH_UTIL_CORE.GC3_IS_INSTALLED = 'N'
1997 AND wsh_util_core.tp_is_installed = 'Y')
1998 THEN
1999 l_dlvy_attr_tab(l_index).ignore_for_planning := 'Y';
2000 END IF;
2001 END IF; -- if p_action_code=update
2002
2003 -- This is to pass the carrier information to the Third Party Instance.
2004 if ( nvl(l_del_int_rec.carrier_code, fnd_api.g_miss_char) <> fnd_api.g_miss_char )
2005 THEN
2006 -- {
2007 l_carrier_id := NULL;
2008 l_manifesting_enabled_flag := NULL;
2009 open get_carrier( l_del_int_rec.carrier_code );
2010 fetch get_carrier into l_carrier_id, l_manifesting_enabled_flag;
2011 close get_carrier;
2012 IF l_debug_on THEN
2013 wsh_debug_sv.log (l_module_name, ' l_carrier_id', l_carrier_id);
2014 wsh_debug_sv.log (l_module_name, ' l_manifesting_enabled_flag', l_manifesting_enabled_flag);
2015 END IF;
2016
2017 l_del_int_rec.carrier_id := l_carrier_id;
2018 -- }
2019 end if;
2020 --
2021 IF(nvl(WSH_INBOUND_SHIP_ADVICE_PKG.G_WAREHOUSE_TYPE, FND_API.G_MISS_CHAR) = 'CMS')
2022 THEN
2023 -- {
2024 -- For updates from CMS, need to check if the ship method code
2025 -- is the same. Error out if different.
2026 IF(p_action_code = 'UPDATE')
2027 THEN
2028 -- {
2029 -- These changes are made to allow the manifesting system to send a changed
2030 -- combo of carrier, service level, and mode of transport.
2031 IF ( nvl(l_del_int_rec.carrier_code, fnd_api.g_miss_char) = fnd_api.g_miss_char) THEN
2032 --{
2033 IF l_debug_on THEN
2034 wsh_debug_sv.logmsg(l_module_name, 'Carrier is null');
2035 END IF;
2036 l_loc_interface_error_rec.p_message_name := 'WSH_NULL_INB_CARRIER';
2037 l_log_error_flag := 'Y';
2038 --}
2039 ELSIF (l_carrier_id IS NULL) THEN
2040 --{
2041 IF l_debug_on THEN
2042 wsh_debug_sv.logmsg(l_module_name, 'Carrier Id is null');
2043 END IF;
2044 l_loc_interface_error_rec.p_message_name := 'WSH_INVALID_INB_CARRIER';
2045 l_log_error_flag := 'Y';
2046 --}
2047 ELSIF (nvl(l_manifesting_enabled_flag, 'N') = 'N' ) THEN
2048 --{
2049 IF l_debug_on THEN
2050 wsh_debug_sv.logmsg(l_module_name, 'Carrier is not Manifesting Enabled');
2051 END IF;
2052 l_loc_interface_error_rec.p_message_name := 'WSH_INB_CAR_NOT_MFST_ENABLED';
2053 l_log_error_flag := 'Y';
2054 --}
2055 END IF;
2056 IF (
2057 nvl(l_enforce_ship_method,'N') = 'Y'
2058 AND nvl(l_del_int_rec.ship_method_code, FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
2059 ) THEN
2060 --{
2061 IF l_debug_on THEN
2062 wsh_debug_sv.logmsg(l_module_name, 'Ship Method is enforced for the org and inbound Ship Method is NULL');
2063 END IF;
2064 l_loc_interface_error_rec.p_message_name := 'WSH_NULL_INB_SHIP_METHOD';
2065 l_log_error_flag := 'Y';
2066 --}
2067 ELSIF nvl(l_del_int_rec.ship_method_code, FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR
2068 THEN
2069 --{
2070 l_valid_shp_method_flag := NULL;
2071 open l_shp_method_org_csr(l_del_int_rec.ship_method_code, l_del_int_rec.organization_id);
2072 fetch l_shp_method_org_csr into l_valid_shp_method_flag;
2073 close l_shp_method_org_csr;
2074 IF l_debug_on THEN
2075 wsh_debug_sv.log (l_module_name, ' l_valid_shp_method_flag ', l_valid_shp_method_flag);
2076 END IF;
2077
2078 IF (nvl(l_valid_shp_method_flag,'N') = 'N') THEN
2079 --{
2080 IF l_debug_on THEN
2081 wsh_debug_sv.logmsg(l_module_name, 'Ship Method is not valid');
2082 END IF;
2083 l_loc_interface_error_rec.p_message_name := 'WSH_OI_INVALID_SHIP_METHOD';
2084 l_log_error_flag := 'Y';
2085 --}
2086 END IF;
2087
2088 --}
2089 END IF;
2090 IF ( l_log_error_flag = 'Y') THEN
2091 --{
2092 l_loc_interface_error_rec.p_interface_table_name := 'WSH_NEW_DEL_INTERFACE';
2093 l_loc_interface_error_rec.p_interface_id := p_delivery_interface_id;
2094 Log_Errors(
2095 p_loc_interface_errors_rec => l_loc_interface_error_rec,
2096 p_api_name =>'Process_Interfaced_Deliveries, Action=UPDATE' ,
2097 x_return_status => l_return_status);
2098
2099 l_log_error_flag := 'N';
2100 RAISE FND_API.G_EXC_ERROR;
2101 --}
2102 END IF;
2103 /*
2104 -- Commented this part of the code as we allow the
2105 -- manifesting system to change the carrier, srv lvl, and mot.
2106 SELECT ship_method_code INTO l_curr_ship_method
2107 FROM wsh_new_deliveries
2108 WHERE delivery_id = l_del_int_rec.delivery_id;
2109
2110 IF l_debug_on THEN
2111 wsh_debug_sv.log (l_module_name,'Current ship method', l_curr_ship_method);
2112 wsh_debug_sv.log (l_module_name,'Incoming ship method', l_del_int_rec.ship_method_code);
2113 END IF;
2114
2115 IF nvl(l_curr_ship_method,FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR
2116 THEN
2117 -- {
2118 IF(l_curr_ship_method <> nvl(l_del_int_rec.ship_method_code, FND_API.G_MISS_CHAR)) THEN
2119 raise invalid_ship_method;
2120 END IF;
2121 -- }
2122 END IF;
2123 -- Bug 2753330
2124 -- Since ship_method_code should not be updateable for CMS inbound,
2125 -- Need to send G_MISS_CHAR for code and name
2126 -- so that the database value will be used for ship method code
2127 l_dlvy_attr_tab(l_index).ship_method_code := FND_API.G_MISS_CHAR;
2128 */
2129 -- }
2130 END IF;
2131 l_dlvy_attr_tab(l_index).ship_method_name := FND_API.G_MISS_CHAR;
2132 l_dlvy_attr_tab(l_index).SHIP_METHOD_CODE := nvl(l_del_int_rec.SHIP_METHOD_CODE, FND_API.G_MISS_CHAR);
2133 ELSE
2134 -- Need to send ship method code only for non-cms cases.
2135 l_dlvy_attr_tab(l_index).SHIP_METHOD_CODE := nvl(l_del_int_rec.SHIP_METHOD_CODE, FND_API.G_MISS_CHAR);
2136 -- }
2137 END IF;
2138
2139
2140 l_dlvy_attr_tab(l_index).DELIVERY_TYPE := nvl(l_del_int_rec.delivery_type, FND_API.G_MISS_CHAR);
2141 l_dlvy_attr_tab(l_index).LOADING_SEQUENCE := nvl(l_del_int_rec.loading_sequence, FND_API.G_MISS_NUM);
2142 l_dlvy_attr_tab(l_index).LOADING_ORDER_FLAG := nvl(l_del_int_rec.loading_order_flag, FND_API.G_MISS_CHAR);
2143 l_dlvy_attr_tab(l_index).INITIAL_PICKUP_DATE := nvl(l_del_int_rec.initial_pickup_date, FND_API.G_MISS_DATE);
2144 l_dlvy_attr_tab(l_index).INITIAL_PICKUP_LOCATION_ID := nvl(l_del_int_rec.initial_pickup_location_id, FND_API.G_MISS_NUM);
2145 l_dlvy_attr_tab(l_index).INITIAL_PICKUP_LOCATION_CODE := nvl(l_del_int_rec.initial_pickup_location_code, FND_API.G_MISS_CHAR);
2146 l_dlvy_attr_tab(l_index).ORGANIZATION_ID := nvl(l_del_int_rec.organization_id, FND_API.G_MISS_NUM);
2147 l_dlvy_attr_tab(l_index).ORGANIZATION_CODE := nvl(l_del_int_rec.organization_code, FND_API.G_MISS_CHAR);
2148 l_dlvy_attr_tab(l_index).ULTIMATE_DROPOFF_LOCATION_ID := nvl(l_del_int_rec.ultimate_dropoff_location_id, FND_API.G_MISS_NUM);
2149 -- Since the location id has been derived already, we should send the code only when the id is null
2150 IF(l_del_int_rec.ultimate_dropoff_location_id IS NULL)
2151 THEN
2152 l_dlvy_attr_tab(l_index).ULTIMATE_DROPOFF_LOCATION_CODE := nvl(l_del_int_rec.ULTIMATE_DROPOFF_LOCATION_CODE, FND_API.G_MISS_CHAR);
2153 END IF;
2154 l_dlvy_attr_tab(l_index).ULTIMATE_DROPOFF_DATE := nvl(l_del_int_rec.ULTIMATE_DROPOFF_DATE, FND_API.G_MISS_DATE);
2155 l_dlvy_attr_tab(l_index).CUSTOMER_ID := nvl(l_del_int_rec.CUSTOMER_ID, FND_API.G_MISS_NUM);
2156 l_dlvy_attr_tab(l_index).CUSTOMER_NUMBER := nvl(l_del_int_rec.customer_number, FND_API.G_MISS_CHAR);
2157 l_dlvy_attr_tab(l_index).INTMED_SHIP_TO_LOCATION_ID := nvl(l_del_int_rec.INTMED_SHIP_TO_LOCATION_ID, FND_API.G_MISS_NUM);
2158
2159 -- Since the location id has been derived already, we should send the code only when the id is null
2160 IF(l_del_int_rec.INTMED_SHIP_TO_LOCATION_ID IS NULL)
2161 THEN
2162 l_dlvy_attr_tab(l_index).INTMED_SHIP_TO_LOCATION_CODE := nvl(l_del_int_rec.INTMED_SHIP_TO_LOCATION_CODE, FND_API.G_MISS_CHAR);
2163 END IF;
2164
2165 l_dlvy_attr_tab(l_index).POOLED_SHIP_TO_LOCATION_ID := nvl(l_del_int_rec.POOLED_SHIP_TO_LOCATION_ID, FND_API.G_MISS_NUM);
2166 -- Send the code only when the id is null
2167 IF(l_del_int_rec.POOLED_SHIP_TO_LOCATION_ID IS NULL) THEN
2168 l_dlvy_attr_tab(l_index).POOLED_SHIP_TO_LOCATION_CODE := nvl(l_del_int_rec.POOLED_SHIP_TO_LOCATION_CODE, FND_API.G_MISS_CHAR);
2169 END IF;
2170
2171 l_dlvy_attr_tab(l_index).FREIGHT_TERMS_CODE := nvl(l_del_int_rec.FREIGHT_TERMS_CODE, FND_API.G_MISS_CHAR);
2172 l_dlvy_attr_tab(l_index).FOB_CODE := nvl(l_del_int_rec.FOB_CODE, FND_API.G_MISS_CHAR);
2173 l_dlvy_attr_tab(l_index).FOB_LOCATION_ID := nvl(l_del_int_rec.FOB_LOCATION_ID, FND_API.G_MISS_NUM);
2174 l_dlvy_attr_tab(l_index).FOB_LOCATION_CODE := nvl(l_del_int_rec.FOB_LOCATION_CODE, FND_API.G_MISS_CHAR);
2175 l_dlvy_attr_tab(l_index).WAYBILL := nvl(l_del_int_rec.waybill, FND_API.G_MISS_CHAR);
2176 l_dlvy_attr_tab(l_index).DOCK_CODE := nvl(l_del_int_rec.dock_code, FND_API.G_MISS_CHAR);
2177 l_dlvy_attr_tab(l_index).ACCEPTANCE_FLAG := nvl(l_del_int_rec.acceptance_flag, FND_API.G_MISS_CHAR);
2178 l_dlvy_attr_tab(l_index).ACCEPTED_BY := nvl(l_del_int_rec.accepted_by, FND_API.G_MISS_CHAR);
2179 l_dlvy_attr_tab(l_index).ACCEPTED_DATE := nvl(l_del_int_rec.accepted_date, FND_API.G_MISS_DATE);
2180 l_dlvy_attr_tab(l_index).ACKNOWLEDGED_BY := nvl(l_del_int_rec.acknowledged_by, FND_API.G_MISS_CHAR);
2181 l_dlvy_attr_tab(l_index).CONFIRMED_BY := nvl(l_del_int_rec.CONFIRMED_BY, FND_API.G_MISS_CHAR);
2182 l_dlvy_attr_tab(l_index).CONFIRM_DATE := nvl(l_del_int_rec.CONFIRM_DATE, FND_API.G_MISS_DATE);
2183 l_dlvy_attr_tab(l_index).ASN_DATE_SENT := nvl(l_del_int_rec.ASN_DATE_SENT, FND_API.G_MISS_DATE);
2184 l_dlvy_attr_tab(l_index).ASN_STATUS_CODE := nvl(l_del_int_rec.ASN_STATUS_CODE, FND_API.G_MISS_CHAR);
2185 l_dlvy_attr_tab(l_index).ASN_SEQ_NUMBER := nvl(l_del_int_rec.ASN_SEQ_NUMBER, FND_API.G_MISS_NUM);
2186 l_dlvy_attr_tab(l_index).GROSS_WEIGHT := nvl(l_del_int_rec.GROSS_WEIGHT, FND_API.G_MISS_NUM);
2187 l_dlvy_attr_tab(l_index).NET_WEIGHT := nvl(l_del_int_rec.NET_WEIGHT, FND_API.G_MISS_NUM);
2188 l_dlvy_attr_tab(l_index).WEIGHT_UOM_CODE := nvl(l_del_int_rec.WEIGHT_UOM_CODE, FND_API.G_MISS_CHAR);
2189 l_dlvy_attr_tab(l_index).VOLUME := nvl(l_del_int_rec.VOLUME , FND_API.G_MISS_NUM);
2190 l_dlvy_attr_tab(l_index).VOLUME_UOM_CODE := nvl(l_del_int_rec.VOLUME_UOM_CODE, FND_API.G_MISS_CHAR);
2191 -- J: W/V Changes
2192 l_dlvy_attr_tab(l_index).WV_FROZEN_FLAG := l_del_int_rec.WV_FROZEN_FLAG;
2193 l_dlvy_attr_tab(l_index).ADDITIONAL_SHIPMENT_INFO := nvl(l_del_int_rec.ADDITIONAL_SHIPMENT_INFO , FND_API.G_MISS_CHAR);
2194 l_dlvy_attr_tab(l_index).CURRENCY_CODE := nvl(l_del_int_rec.CURRENCY_CODE, FND_API.G_MISS_CHAR);
2195 l_dlvy_attr_tab(l_index).ATTRIBUTE_CATEGORY := nvl(l_del_int_rec.ATTRIBUTE_CATEGORY , FND_API.G_MISS_CHAR);
2196 l_dlvy_attr_tab(l_index).ATTRIBUTE1 := nvl(l_del_int_rec.ATTRIBUTE1, FND_API.G_MISS_CHAR);
2197 l_dlvy_attr_tab(l_index).ATTRIBUTE2 := nvl(l_del_int_rec.ATTRIBUTE2, FND_API.G_MISS_CHAR);
2198 l_dlvy_attr_tab(l_index).ATTRIBUTE3 := nvl(l_del_int_rec.ATTRIBUTE3, FND_API.G_MISS_CHAR);
2199 l_dlvy_attr_tab(l_index).ATTRIBUTE4 := nvl(l_del_int_rec.ATTRIBUTE4, FND_API.G_MISS_CHAR);
2200 l_dlvy_attr_tab(l_index).ATTRIBUTE5 := nvl(l_del_int_rec.ATTRIBUTE5, FND_API.G_MISS_CHAR);
2201 l_dlvy_attr_tab(l_index).ATTRIBUTE6 := nvl(l_del_int_rec.ATTRIBUTE6, FND_API.G_MISS_CHAR);
2202 l_dlvy_attr_tab(l_index).ATTRIBUTE7 := nvl(l_del_int_rec.ATTRIBUTE7, FND_API.G_MISS_CHAR);
2203 l_dlvy_attr_tab(l_index).ATTRIBUTE8 := nvl(l_del_int_rec.ATTRIBUTE8, FND_API.G_MISS_CHAR);
2204 l_dlvy_attr_tab(l_index).ATTRIBUTE9 := nvl(l_del_int_rec.ATTRIBUTE9, FND_API.G_MISS_CHAR);
2205 l_dlvy_attr_tab(l_index).ATTRIBUTE10 := nvl(l_del_int_rec.ATTRIBUTE10, FND_API.G_MISS_CHAR);
2206 l_dlvy_attr_tab(l_index).ATTRIBUTE11 := nvl(l_del_int_rec.ATTRIBUTE11, FND_API.G_MISS_CHAR);
2207 l_dlvy_attr_tab(l_index).ATTRIBUTE12 := nvl(l_del_int_rec.ATTRIBUTE12, FND_API.G_MISS_CHAR);
2208 l_dlvy_attr_tab(l_index).ATTRIBUTE13 := nvl(l_del_int_rec.ATTRIBUTE13, FND_API.G_MISS_CHAR);
2209 l_dlvy_attr_tab(l_index).ATTRIBUTE14 := nvl(l_del_int_rec.ATTRIBUTE14, FND_API.G_MISS_CHAR);
2210 l_dlvy_attr_tab(l_index).ATTRIBUTE15 := nvl(l_del_int_rec.ATTRIBUTE15, FND_API.G_MISS_CHAR);
2211 l_dlvy_attr_tab(l_index).TP_ATTRIBUTE_CATEGORY := nvl(l_del_int_rec.TP_ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR);
2212 l_dlvy_attr_tab(l_index).TP_ATTRIBUTE1 := nvl(l_del_int_rec.TP_ATTRIBUTE1, FND_API.G_MISS_CHAR);
2213 l_dlvy_attr_tab(l_index).TP_ATTRIBUTE2 := nvl(l_del_int_rec.TP_ATTRIBUTE2, FND_API.G_MISS_CHAR);
2214 l_dlvy_attr_tab(l_index).TP_ATTRIBUTE3 := nvl(l_del_int_rec.TP_ATTRIBUTE3, FND_API.G_MISS_CHAR);
2215 l_dlvy_attr_tab(l_index).TP_ATTRIBUTE4 := nvl(l_del_int_rec.TP_ATTRIBUTE4, FND_API.G_MISS_CHAR);
2216 l_dlvy_attr_tab(l_index).TP_ATTRIBUTE5 := nvl(l_del_int_rec.TP_ATTRIBUTE5, FND_API.G_MISS_CHAR);
2217 l_dlvy_attr_tab(l_index).TP_ATTRIBUTE6 := nvl(l_del_int_rec.TP_ATTRIBUTE6, FND_API.G_MISS_CHAR);
2218 l_dlvy_attr_tab(l_index).TP_ATTRIBUTE7 := nvl(l_del_int_rec.TP_ATTRIBUTE7, FND_API.G_MISS_CHAR);
2219 l_dlvy_attr_tab(l_index).TP_ATTRIBUTE8 := nvl(l_del_int_rec.TP_ATTRIBUTE8, FND_API.G_MISS_CHAR);
2220 l_dlvy_attr_tab(l_index).TP_ATTRIBUTE9 := nvl(l_del_int_rec.TP_ATTRIBUTE9, FND_API.G_MISS_CHAR);
2221 l_dlvy_attr_tab(l_index).TP_ATTRIBUTE10 := nvl(l_del_int_rec.TP_ATTRIBUTE10, FND_API.G_MISS_CHAR);
2222 l_dlvy_attr_tab(l_index).TP_ATTRIBUTE11 := nvl(l_del_int_rec.TP_ATTRIBUTE11, FND_API.G_MISS_CHAR);
2223 l_dlvy_attr_tab(l_index).TP_ATTRIBUTE12 := nvl(l_del_int_rec.TP_ATTRIBUTE12, FND_API.G_MISS_CHAR);
2224 l_dlvy_attr_tab(l_index).TP_ATTRIBUTE13 := nvl(l_del_int_rec.TP_ATTRIBUTE13, FND_API.G_MISS_CHAR);
2225 l_dlvy_attr_tab(l_index).TP_ATTRIBUTE14 := nvl(l_del_int_rec.TP_ATTRIBUTE14, FND_API.G_MISS_CHAR);
2226 l_dlvy_attr_tab(l_index).TP_ATTRIBUTE15 := nvl(l_del_int_rec.TP_ATTRIBUTE15, FND_API.G_MISS_CHAR);
2227 l_dlvy_attr_tab(l_index).GLOBAL_ATTRIBUTE_CATEGORY := nvl(l_del_int_rec.GLOBAL_ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR);
2228 l_dlvy_attr_tab(l_index).GLOBAL_ATTRIBUTE1 := nvl(l_del_int_rec.GLOBAL_ATTRIBUTE1 , FND_API.G_MISS_CHAR);
2229 l_dlvy_attr_tab(l_index).GLOBAL_ATTRIBUTE2 := nvl(l_del_int_rec.GLOBAL_ATTRIBUTE2 , FND_API.G_MISS_CHAR);
2230 l_dlvy_attr_tab(l_index).GLOBAL_ATTRIBUTE3 := nvl(l_del_int_rec.GLOBAL_ATTRIBUTE3 , FND_API.G_MISS_CHAR);
2231 l_dlvy_attr_tab(l_index).GLOBAL_ATTRIBUTE4 := nvl(l_del_int_rec.GLOBAL_ATTRIBUTE4 , FND_API.G_MISS_CHAR);
2232 l_dlvy_attr_tab(l_index).GLOBAL_ATTRIBUTE5 := nvl(l_del_int_rec.GLOBAL_ATTRIBUTE5 , FND_API.G_MISS_CHAR);
2233 l_dlvy_attr_tab(l_index).GLOBAL_ATTRIBUTE6 := nvl(l_del_int_rec.GLOBAL_ATTRIBUTE6 , FND_API.G_MISS_CHAR);
2234 l_dlvy_attr_tab(l_index).GLOBAL_ATTRIBUTE7 := nvl(l_del_int_rec.GLOBAL_ATTRIBUTE7 , FND_API.G_MISS_CHAR);
2235 l_dlvy_attr_tab(l_index).GLOBAL_ATTRIBUTE8 := nvl(l_del_int_rec.GLOBAL_ATTRIBUTE8 , FND_API.G_MISS_CHAR);
2236 l_dlvy_attr_tab(l_index).GLOBAL_ATTRIBUTE9 := nvl(l_del_int_rec.GLOBAL_ATTRIBUTE9 , FND_API.G_MISS_CHAR);
2237 l_dlvy_attr_tab(l_index).GLOBAL_ATTRIBUTE10 := nvl(l_del_int_rec.GLOBAL_ATTRIBUTE10, FND_API.G_MISS_CHAR);
2238 l_dlvy_attr_tab(l_index).GLOBAL_ATTRIBUTE11 := nvl(l_del_int_rec.GLOBAL_ATTRIBUTE11, FND_API.G_MISS_CHAR);
2239 l_dlvy_attr_tab(l_index).GLOBAL_ATTRIBUTE12 := nvl(l_del_int_rec.GLOBAL_ATTRIBUTE12, FND_API.G_MISS_CHAR);
2240 l_dlvy_attr_tab(l_index).GLOBAL_ATTRIBUTE13 := nvl(l_del_int_rec.GLOBAL_ATTRIBUTE13, FND_API.G_MISS_CHAR);
2241 l_dlvy_attr_tab(l_index).GLOBAL_ATTRIBUTE14 := nvl(l_del_int_rec.GLOBAL_ATTRIBUTE14, FND_API.G_MISS_CHAR);
2242 l_dlvy_attr_tab(l_index).GLOBAL_ATTRIBUTE15 := nvl(l_del_int_rec.GLOBAL_ATTRIBUTE15, FND_API.G_MISS_CHAR);
2243 l_dlvy_attr_tab(l_index).GLOBAL_ATTRIBUTE16 := nvl(l_del_int_rec.GLOBAL_ATTRIBUTE16, FND_API.G_MISS_CHAR);
2244 l_dlvy_attr_tab(l_index).GLOBAL_ATTRIBUTE17 := nvl(l_del_int_rec.GLOBAL_ATTRIBUTE17, FND_API.G_MISS_CHAR);
2245 l_dlvy_attr_tab(l_index).GLOBAL_ATTRIBUTE18 := nvl(l_del_int_rec.GLOBAL_ATTRIBUTE18, FND_API.G_MISS_CHAR);
2246 l_dlvy_attr_tab(l_index).GLOBAL_ATTRIBUTE19 := nvl(l_del_int_rec.GLOBAL_ATTRIBUTE19, FND_API.G_MISS_CHAR);
2247 l_dlvy_attr_tab(l_index).GLOBAL_ATTRIBUTE20 := nvl(l_del_int_rec.GLOBAL_ATTRIBUTE20, FND_API.G_MISS_CHAR);
2248 l_dlvy_attr_tab(l_index).PROBLEM_CONTACT_REFERENCE := nvl(l_del_int_rec.PROBLEM_CONTACT_REFERENCE, FND_API.G_MISS_CHAR);
2249 l_dlvy_attr_tab(l_index).COD_AMOUNT := nvl(l_del_int_rec.COD_AMOUNT, FND_API.G_MISS_NUM);
2250 l_dlvy_attr_tab(l_index).COD_CURRENCY_CODE := nvl(l_del_int_rec.COD_CURRENCY_CODE , FND_API.G_MISS_CHAR);
2251 l_dlvy_attr_tab(l_index).COD_REMIT_TO := nvl(l_del_int_rec.COD_REMIT_TO, FND_API.G_MISS_CHAR);
2252 l_dlvy_attr_tab(l_index).COD_CHARGE_PAID_BY := nvl(l_del_int_rec.COD_CHARGE_PAID_BY , FND_API.G_MISS_CHAR);
2253 l_dlvy_attr_tab(l_index).PORT_OF_LOADING := nvl(l_del_int_rec.PORT_OF_LOADING , FND_API.G_MISS_CHAR);
2254 l_dlvy_attr_tab(l_index).PORT_OF_DISCHARGE := nvl(l_del_int_rec.PORT_OF_DISCHARGE , FND_API.G_MISS_CHAR);
2255 l_dlvy_attr_tab(l_index).FTZ_NUMBER := nvl(l_del_int_rec.FTZ_NUMBER, FND_API.G_MISS_CHAR);
2256 l_dlvy_attr_tab(l_index).ROUTED_EXPORT_TXN := nvl(l_del_int_rec.ROUTED_EXPORT_TXN, FND_API.G_MISS_CHAR);
2257 l_dlvy_attr_tab(l_index).ENTRY_NUMBER := nvl(l_del_int_rec.ENTRY_NUMBER, FND_API.G_MISS_CHAR);
2258 l_dlvy_attr_tab(l_index).ROUTING_INSTRUCTIONS := nvl(l_del_int_rec.ROUTING_INSTRUCTIONS , FND_API.G_MISS_CHAR);
2259 l_dlvy_attr_tab(l_index).IN_BOND_CODE := nvl(l_del_int_rec.IN_BOND_CODE , FND_API.G_MISS_CHAR);
2260 l_dlvy_attr_tab(l_index).SHIPPING_MARKS := nvl(l_del_int_rec.SHIPPING_MARKS, FND_API.G_MISS_CHAR);
2261 l_txn_type := l_del_int_rec.INTERFACE_ACTION_CODE;
2262 IF (nvl(l_txn_type,'!!!!') = '94X_INBOUND') THEN
2263 l_dlvy_attr_tab(l_index).CARRIER_ID := l_del_int_rec.carrier_id;
2264 l_dlvy_attr_tab(l_index).CARRIER_CODE := l_del_int_rec.carrier_code;
2265 l_dlvy_attr_tab(l_index).SERVICE_LEVEL := l_del_int_rec.SERVICE_LEVEL;
2266 l_dlvy_attr_tab(l_index).MODE_OF_TRANSPORT := l_del_int_rec.MODE_OF_TRANSPORT;
2267 ELSE
2268 l_dlvy_attr_tab(l_index).CARRIER_ID := nvl(l_del_int_rec.carrier_id, FND_API.G_MISS_NUM);
2269 l_dlvy_attr_tab(l_index).CARRIER_CODE := nvl(l_del_int_rec.carrier_code, FND_API.G_MISS_CHAR);
2270 l_dlvy_attr_tab(l_index).SERVICE_LEVEL := nvl(l_del_int_rec.SERVICE_LEVEL, FND_API.G_MISS_CHAR);
2271 l_dlvy_attr_tab(l_index).MODE_OF_TRANSPORT := nvl(l_del_int_rec.MODE_OF_TRANSPORT, FND_API.G_MISS_CHAR);
2272 END IF;
2273 l_txn_type := null;
2274
2275 IF l_debug_on THEN
2276 wsh_debug_sv.log (l_module_name, ' Service Level ', l_del_int_rec.SERVICE_LEVEL);
2277 wsh_debug_sv.log (l_module_name, ' Transportation Method ', l_del_int_rec.MODE_OF_TRANSPORT);
2278 wsh_debug_sv.logmsg (l_module_name, '--------------------------------------------------------------------------------------------');
2279 wsh_debug_sv.logmsg (l_module_name, 'The following are the values of Carrier, Service Level and MOT being passed to the Group API');
2280 wsh_debug_sv.logmsg (l_module_name, '--------------------------------------------------------------------------------------------');
2281 wsh_debug_sv.logmsg (l_module_name, '********************************************************************************************');
2282 wsh_debug_sv.logmsg (l_module_name, '--------------------------------------------------------------------------------------------');
2283 wsh_debug_sv.log (l_module_name, 'l_dlvy_attr_tab(l_index).CARRIER_ID',l_dlvy_attr_tab(l_index).CARRIER_ID);
2284 wsh_debug_sv.log (l_module_name, 'l_dlvy_attr_tab(l_index).CARRIER_CODE',l_dlvy_attr_tab(l_index).CARRIER_CODE);
2285 wsh_debug_sv.log (l_module_name, 'l_dlvy_attr_tab(l_index).SERVICE_LEVEL',l_dlvy_attr_tab(l_index).SERVICE_LEVEL);
2286 wsh_debug_sv.log (l_module_name, 'l_dlvy_attr_tab(l_index).MODE_OF_TRANSPORT',l_dlvy_attr_tab(l_index).MODE_OF_TRANSPORT);
2287 wsh_debug_sv.log (l_module_name, 'l_dlvy_attr_tab(l_index).SHIP_METHOD_CODE',l_dlvy_attr_tab(l_index).SHIP_METHOD_CODE);
2288 wsh_debug_sv.logmsg (l_module_name, '--------------------------------------------------------------------------------------------');
2289 wsh_debug_sv.logmsg (l_module_name, '********************************************************************************************');
2290 wsh_debug_sv.logmsg (l_module_name, '--------------------------------------------------------------------------------------------');
2291 END IF;
2292
2293 SELECT count(*) INTO l_del_freight_costs
2294 FROM wsh_freight_costs_interface
2295 WHERE delivery_interface_id = p_delivery_interface_id
2296 AND INTERFACE_ACTION_CODE = '94X_INBOUND';
2297
2298 IF l_debug_on THEN
2299 wsh_debug_sv.log (l_module_name, 'Delivery Freight record count', l_del_freight_costs);
2300 END IF;
2301
2302 IF(l_del_freight_costs > 0)
2303 THEN
2304 -- {
2305 IF l_debug_on THEN
2306 wsh_debug_sv.log (l_module_name, 'calling process freight for delivery interface', p_delivery_interface_id);
2307 END IF;
2308
2309 Process_Int_Freight_Costs(
2310 p_delivery_interface_id => p_delivery_interface_id,
2311 x_return_status => l_return_status);
2312
2313 IF l_debug_on THEN
2314 wsh_debug_sv.log (l_module_name, 'Return status from process int freight costs', l_return_status);
2315 END IF;
2316 --
2317 IF(l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
2318 raise freight_cost_processing_error;
2319 END IF;
2320 -- }
2321 END IF;
2322
2323 l_index := l_index + 1;
2324 -- }
2325 END LOOP; -- for l_del_int_rec
2326
2327 --Bug 3458160
2328 IF p_action_code = 'CREATE' THEN
2329 l_in_rec.caller := 'WSH_TPW_INBOUND';
2330 ELSE
2331 l_in_rec.caller := 'WSH_INBOUND';
2332 END IF;
2333 l_in_rec.action_code := p_action_code;
2334
2335 wsh_interface_grp.Create_Update_Delivery(
2336 p_api_version_number => l_api_version_number,
2337 p_init_msg_list => FND_API.G_FALSE,
2338 p_commit => FND_API.G_FALSE,
2339 p_in_rec => l_in_rec,
2340 p_rec_attr_tab => l_dlvy_attr_tab,
2341 x_del_out_rec_tab => l_dlvy_out_rec_tab,
2342 x_return_status => l_return_status,
2343 x_msg_count => l_msg_count,
2344 x_msg_data => l_msg_data);
2345
2346 IF l_debug_on THEN
2347 wsh_debug_sv.log (l_module_name, 'Return status from create_Update_delivery',l_return_status);
2348 wsh_debug_sv.log (l_module_name, 'Create Update Delivery api msg count', l_msg_count);
2349 wsh_debug_sv.log (l_module_name, 'Create Update Delivery api msg', l_msg_data);
2350 END IF;
2351
2352 IF(l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS)
2353 THEN
2354 -- {
2355 l_loc_interface_error_rec.p_interface_table_name := 'WSH_NEW_DEL_INTERFACE';
2356 l_loc_interface_error_rec.p_interface_id := p_delivery_interface_id;
2357 --
2358 Log_Errors(
2359 p_loc_interface_errors_rec => l_loc_interface_error_rec,
2360 p_msg_data => l_msg_data,
2361 p_api_name => 'WSH_INTERFACE_GRP.Create_Update_Delivery' ,
2362 x_return_status => l_return_status);
2363 --
2364 IF l_debug_on THEN
2365 wsh_debug_sv.log (l_module_name,'Log_Errors l_return_status',l_return_status);
2366 END IF;
2367
2368 IF p_action_code = 'CREATE'
2369 THEN
2370 -- {
2371 FND_MESSAGE.SET_NAME('WSH', 'WSH_CREATE_DLVY_ERROR');
2372 FND_MESSAGE.SET_TOKEN('DEL_INT',p_delivery_interface_id);
2373 ELSIF p_action_code = 'UPDATE'
2374 THEN
2375 FND_MESSAGE.SET_NAME('WSH', 'WSH_UPDATE_DLVY_ERROR');
2376 FND_MESSAGE.SET_TOKEN('DEL_INT', p_delivery_interface_id);
2377 -- }
2378 END IF;
2379 --
2380 RAISE fnd_api.g_exc_error;
2381 -- }
2382 END IF;
2383
2384 -- send the newly created delivery in the out parameter
2385 IF p_action_code = 'CREATE'
2386 THEN
2387 -- {
2388 IF l_debug_on THEN
2389 wsh_debug_sv.log (l_module_name, 'New delivery Id created', l_dlvy_out_rec_tab(l_dlvy_out_rec_tab.first).delivery_id);
2390 END IF;
2391 x_dlvy_id := l_dlvy_out_rec_tab(l_dlvy_out_rec_tab.first).delivery_id;
2392 -- }
2393 END IF;
2394
2395 IF l_debug_on THEN
2396 wsh_debug_sv.pop(l_module_name);
2397 END IF;
2398
2399 EXCEPTION
2400 WHEN fnd_api.g_exc_error THEN
2401 x_return_status := fnd_api.g_ret_sts_error;
2402 --
2403 IF l_debug_on THEN
2404 wsh_debug_sv.logmsg(l_module_name, 'FND_API.G_EXC_ERROR exception has occured.', wsh_debug_sv.c_excep_level);
2405 wsh_debug_sv.pop(l_module_name, 'EXCEPTION:FND_API.G_EXC_ERROR');
2406 END IF;
2407 --
2408 WHEN fnd_api.g_exc_unexpected_error THEN
2409 x_return_status := fnd_api.g_ret_sts_unexp_error;
2410 --
2411 IF l_debug_on THEN
2412 wsh_debug_sv.logmsg(l_module_name, 'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.', wsh_debug_sv.c_excep_level);
2413 wsh_debug_sv.pop(l_module_name, 'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2414 END IF;
2415 --
2416 WHEN wsh_util_core.g_exc_warning THEN
2417 x_return_status := wsh_util_core.g_ret_sts_warning;
2418 --
2419 IF l_debug_on THEN
2420 wsh_debug_sv.logmsg(l_module_name, 'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ', wsh_debug_sv.c_excep_level);
2421 wsh_debug_sv.pop(l_module_name, 'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
2422 END IF;
2423 --
2424 WHEN invalid_ship_method THEN
2425 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2426 IF l_debug_on THEN
2427 WSH_DEBUG_SV.logmsg(l_module_name,'invalid_ship_method exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2428 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_ship_method');
2429 END IF;
2430 WHEN freight_cost_processing_error THEN
2431 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2432 IF l_debug_on THEN
2433 WSH_DEBUG_SV.logmsg(l_module_name,'freight_cost_processing_error exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2434 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:freight_cost_processing_error');
2435 END IF;
2436 WHEN OTHERS THEN
2437 x_return_status := wsh_util_core.g_ret_sts_unexp_error;
2438 wsh_util_core.default_handler('WSH_INTERFACE_COMMON_ACTIONS.PROCESS_INTERFACED_DELIVERIES');
2439
2440 IF l_debug_on THEN
2441 wsh_debug_sv.logmsg(l_module_name, 'Unexpected error has occured. Oracle error message is ' || SQLERRM, wsh_debug_sv.c_unexpec_err_level);
2442 wsh_debug_sv.pop(l_module_name, 'EXCEPTION:OTHERS');
2443 END IF;
2444
2445 END Process_Interfaced_Deliveries;
2446
2447 /*
2448 -----------------------------------------------------------------------------
2449 PROCEDURE : Delivery_Interface_Wrapper
2450 PARAMETERS : p_delivery_interface_id IN NUMBER,
2451 p_action_code CREATE or UPDATE or CANCEL
2452 x_return_status OUT VARCHAR2)
2453
2454 DESCRIPTION :
2455 -- This is the wrapper procedure that will be called by the Process_Inbound
2456 for Shipment_Advice or Shipment_Request.
2457 -- This takes in a delivery_interface_id and the action code
2458 -- If the action code is CREATE, then the delivery is created in the base
2459 tables based on the data in the delivery-interface tables
2460 - Then for each of the delivery details in the interface tables, a corresponding
2461 delivery detail is created in the base tables.
2462 -- Then the newly created base delivery details are assigned to the newly
2463 created base delivery.
2464 -- If the action code is UPDATE, then the base delivery is updated first
2465 -- Followed by updates of base delivery details
2466
2467 ------------------------------------------------------------------------------
2468 */
2469
2470 PROCEDURE Delivery_Interface_Wrapper(
2471 p_delivery_interface_id IN NUMBER,
2472 p_action_code IN VARCHAR2,
2473 x_delivery_id IN OUT NOCOPY NUMBER,
2474 x_return_status OUT NOCOPY VARCHAR2) IS
2475
2476 -- variables
2477 l_return_status VARCHAR2(30);
2478 l_delivery_id NUMBER;
2479
2480 l_created_delivery_id NUMBER;
2481 l_new_del_detail_id NUMBER;
2482 l_action_code VARCHAR2(30);
2483
2484 l_loc_interface_error_rec WSH_INTERFACE_VALIDATIONS_PKG.interface_errors_rec_type;
2485 -- cursors
2486 CURSOR del_id IS
2487 SELECT delivery_id ,
2488 -- J: W/V Changes
2489 gross_weight,
2490 net_weight,
2491 volume,
2492 wv_frozen_flag
2493 FROM wsh_new_del_interface
2494 WHERE delivery_interface_id = p_delivery_interface_id
2495 AND INTERFACE_ACTION_CODE = '94X_INBOUND';
2496
2497 cursor org_del_id(c_del_id IN NUMBER) IS
2498 SELECT NVL(gross_weight,0),
2499 NVL(net_weight,0),
2500 NVL(volume,0)
2501 FROM wsh_new_deliveries
2502 WHERE delivery_id = c_del_id;
2503
2504
2505 -- J: W/V changes
2506 l_api_version NUMBER := 1.0;
2507 l_msg_count NUMBER;
2508 l_msg_data VARCHAR2(3000);
2509 l_gross_weight NUMBER;
2510 l_net_weight NUMBER;
2511 l_volume NUMBER;
2512 l_wv_frozen_flag VARCHAR2(1);
2513 l_tmp_del_id NUMBER;
2514 l_org_gross_weight NUMBER;
2515 l_org_net_weight NUMBER;
2516 l_org_volume NUMBER;
2517
2518 -- exceptions
2519 invalid_action_code exception;
2520 process_del_details_failed exception;
2521 process_delivery_failed exception;
2522 process_delivery_wv_failed exception;
2523 cancel_lines_failed exception;
2524 invalid_input exception;
2525 no_lock_found exception;
2526 --
2527 l_debug_on BOOLEAN;
2528 --
2529 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DELIVERY_INTERFACE_WRAPPER';
2530 --
2531 BEGIN
2532 --
2533 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2534 --
2535 IF l_debug_on IS NULL
2536 THEN
2537 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2538 END IF;
2539 --
2540 IF l_debug_on THEN
2541 wsh_debug_sv.push(l_module_name,'Delivery_Interface_Wrapper');
2542 wsh_debug_sv.log (l_module_name,'Delivery interface Id', p_delivery_interface_id);
2543 wsh_debug_sv.log (l_module_name,'Action Code', p_action_code);
2544 END IF;
2545
2546 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2547
2548 l_action_code := p_action_code;
2549
2550 -- First process the delivery
2551 IF(l_action_code IN ('CREATE', 'UPDATE')) THEN
2552 IF(p_delivery_interface_id IS NULL) THEN
2553 raise invalid_input;
2554 END IF;
2555
2556 OPEN del_id;
2557 FETCH del_id INTO l_delivery_id,
2558 -- J: W/V Changes
2559 l_gross_weight,
2560 l_net_weight,
2561 l_volume,
2562 l_wv_frozen_flag;
2563 CLOSE del_id;
2564
2565 IF(l_action_code = 'UPDATE') THEN
2566
2567 --Lock the records
2568 Lock_Delivery_And_Details(
2569 p_delivery_id => l_delivery_id,
2570 x_return_status => l_return_status);
2571
2572 IF l_debug_on THEN
2573 wsh_debug_sv.log (l_module_name,'Return status from lock delivery and details', l_return_status);
2574 END IF;
2575
2576 IF(l_return_status <> wsh_util_core.g_ret_sts_success) THEN
2577 l_loc_interface_error_rec.p_interface_table_name := 'WSH_NEW_DEL_INTERFACE';
2578 l_loc_interface_error_rec.p_interface_id := p_delivery_interface_id;
2579 l_loc_interface_error_rec.p_message_name := 'WSH_NO_LOCK';
2580
2581 Log_Errors(
2582 p_loc_interface_errors_rec => l_loc_interface_error_rec,
2583 p_api_name =>'Delivery_Interface_Wrapper, Action=UPDATE' ,
2584 x_return_status => l_return_status);
2585
2586 IF l_debug_on THEN
2587 wsh_debug_sv.log (l_module_name,'Log_Errors l_return_status', l_return_status);
2588 END IF;
2589 raise no_lock_found;
2590 END IF;
2591
2592
2593 END IF;
2594
2595 Process_Interfaced_Deliveries(
2596 p_delivery_interface_id => p_delivery_interface_id,
2597 p_action_code => l_action_code,
2598 x_dlvy_id => l_created_delivery_id,
2599 x_return_status => l_return_status);
2600
2601 IF l_debug_on THEN
2602 wsh_debug_sv.log (l_module_name, 'Return Status from Process Deliveries', l_return_status);
2603 END IF;
2604
2605 IF(l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
2606 raise process_delivery_failed;
2607 END IF;
2608
2609 IF(l_action_code = 'CREATE' AND l_created_delivery_id is NULL ) THEN
2610 raise process_delivery_failed;
2611 END IF;
2612
2613 -- set the out parameter
2614 x_delivery_id := l_created_delivery_id;
2615
2616 -- Now process the delivery details
2617 Process_Interfaced_Del_Details(
2618 p_delivery_interface_id => p_delivery_interface_id,
2619 p_delivery_id => l_delivery_id,
2620 p_new_delivery_id => l_created_delivery_id,
2621 p_action_code => l_action_code,
2622 x_return_status => l_return_status);
2623
2624 IF l_debug_on THEN
2625 wsh_debug_sv.log (l_module_name, 'Return Status from Process Details', l_return_status);
2626 END IF;
2627
2628 IF(l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
2629 raise process_del_details_failed;
2630 END IF;
2631
2632 -- J: W/V Changes
2633 -- Need to update W/V for del here
2634 IF(l_action_code = 'UPDATE') THEN
2635 l_tmp_del_id := l_delivery_id;
2636 ELSE
2637 l_tmp_del_id := l_created_delivery_id;
2638 END IF;
2639
2640 /* Adjust the W/V on delivery
2641 as the dd assignment would have bumped(if del W/V is not frozen)
2642 up the W/V on del */
2643
2644 OPEN org_del_id(l_tmp_del_id);
2645 FETCH org_del_id
2646 INTO l_org_gross_weight,
2647 l_org_net_weight,
2648 l_org_volume;
2649 CLOSE org_del_id;
2650
2651 UPDATE wsh_new_deliveries
2652 SET gross_weight = l_gross_weight,
2653 net_weight = l_net_weight,
2654 volume = l_volume,
2655 wv_frozen_flag = l_wv_frozen_flag
2656 WHERE delivery_id = l_tmp_del_id;
2657
2658 WSH_WV_UTILS.Del_WV_Post_Process(
2659 p_delivery_id => l_tmp_del_id,
2660 p_diff_gross_wt => NVL(l_gross_weight,0) - l_org_gross_weight,
2661 p_diff_net_wt => NVL(l_net_weight,0) - l_org_net_weight,
2662 p_diff_volume => NVL(l_volume,0) - l_org_volume,
2663 x_return_status => l_return_status);
2664
2665 IF l_debug_on THEN
2666 wsh_debug_sv.log (l_module_name, 'Return status from WSH_WV_UTILS.Del_WV_Post_Process ',l_return_status);
2667 END IF;
2668
2669 IF(l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
2670
2671 l_loc_interface_error_rec.p_interface_table_name := 'WSH_NEW_DEL_INTERFACE';
2672 l_loc_interface_error_rec.p_interface_id := p_delivery_interface_id;
2673
2674 IF l_debug_on THEN
2675 wsh_debug_sv.log (l_module_name, 'Delivery Interface Id', p_delivery_interface_id);
2676 END IF;
2677
2678 Log_Errors(
2679 p_loc_interface_errors_rec => l_loc_interface_error_rec,
2680 p_msg_data => l_msg_data,
2681 p_api_name => 'WSH_INTERFACE_PUB.Delivery_Interface_Wrapper' ,
2682 x_return_status => l_return_status);
2683
2684 IF l_debug_on THEN
2685 wsh_debug_sv.log (l_module_name, 'Return status after log_errors', l_return_status);
2686 END IF;
2687 raise process_delivery_wv_failed;
2688 END IF;
2689
2690 ELSIF (l_action_code = 'CANCEL') THEN
2691
2692 -- for cancel case, we need a base delivery id
2693 -- hence raise error if it is null
2694 IF(x_delivery_id IS NULL) THEN
2695 raise invalid_input;
2696 ELSE
2697 l_delivery_id := x_delivery_id;
2698 END IF;
2699
2700 Process_Cancel(
2701 p_delivery_id => l_delivery_id,
2702 x_return_status => l_return_status);
2703
2704 IF l_debug_on THEN
2705 wsh_debug_sv.log (l_module_name, 'Return Status from Process Cancel', l_return_status);
2706 END IF;
2707
2708 IF(l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
2709 raise cancel_lines_failed;
2710 END IF;
2711
2712 ELSE
2713 raise invalid_action_code;
2714 END IF; -- if l_action_code
2715
2716 IF l_debug_on THEN
2717 wsh_debug_sv.pop(l_module_name);
2718 END IF;
2719
2720 EXCEPTION
2721 WHEN invalid_input THEN
2722 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2723 IF l_debug_on THEN
2724 WSH_DEBUG_SV.logmsg(l_module_name,'invalid_input exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2725 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_input');
2726 END IF;
2727 WHEN no_lock_found THEN
2728 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2729 IF l_debug_on THEN
2730 WSH_DEBUG_SV.logmsg(l_module_name,'no_lock_found exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2731 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:no_lock_found');
2732 END IF;
2733 WHEN invalid_action_code THEN
2734 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2735 IF l_debug_on THEN
2736 WSH_DEBUG_SV.logmsg(l_module_name,'invalid_action_code exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2737 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_action_code');
2738 END IF;
2739 WHEN process_delivery_failed THEN
2740 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2741 IF l_debug_on THEN
2742 WSH_DEBUG_SV.logmsg(l_module_name,'process_delivery_failed exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2743 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:process_delivery_failed');
2744 END IF;
2745 -- J: W/V Changes
2746 WHEN process_delivery_wv_failed THEN
2747 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2748 IF l_debug_on THEN
2749 WSH_DEBUG_SV.logmsg(l_module_name,'process_delivery_wv_failed exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2750 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:process_delivery_wv_failed');
2751 END IF;
2752 WHEN process_del_details_failed THEN
2753 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2754 IF l_debug_on THEN
2755 WSH_DEBUG_SV.logmsg(l_module_name,'process_del_details_failed exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2756 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:process_del_details_failed');
2757 END IF;
2758 WHEN cancel_lines_failed THEN
2759 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2760 IF l_debug_on THEN
2761 WSH_DEBUG_SV.logmsg(l_module_name,'cancel_lines_failed exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2762 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:cancel_lines_failed');
2763 END IF;
2764 WHEN Others THEN
2765 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2766 IF l_debug_on THEN
2767 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
2768 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2769 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2770 END IF;
2771
2772 END Delivery_Interface_Wrapper;
2773
2774
2775 /*
2776 -----------------------------------------------------------------------------
2777 PROCEDURE : Process_Int_Freight_Costs
2778 PARAMETERS : p_delivery_interface_id IN NUMBER DEFAULT NULL,
2779 p_del_detail_interface_id IN NUMBER DEFAULT NULL
2780 p_stop_interface_id IN NUMBER DEFAULT NULL,
2781 p_trip_interface_id IN NUMBER DEFAULT NULL,
2782 x_return_status - return status of API
2783 DESCRIPTION :
2784 -- This procedure takes the freight cost record from freight interface table
2785 and inserts into the base wsh_freight_costs table.
2786 -- This takes in as input the interface_id for a delivery or detail or stop or trip
2787 -- This will be called by the procedures for processing delivery and delivery details
2788
2789 ------------------------------------------------------------------------------
2790 */
2791
2792
2793 PROCEDURE Process_Int_Freight_Costs(
2794 p_delivery_interface_id IN NUMBER, -- DEFAULT NULL in spec,
2795 p_del_detail_interface_id IN NUMBER, -- DEFAULT NULL in spec
2796 p_stop_interface_id IN NUMBER, -- DEFAULT NULL in spec
2797 p_trip_interface_id IN NUMBER, -- DEFAULT NULL in spec
2798 x_return_status OUT NOCOPY VARCHAR2) IS
2799
2800 -- variables
2801 l_freight_costs_info WSH_FREIGHT_COSTS_PUB.PubFreightCostRecType;
2802 l_freight_cost_id NUMBER;
2803 l_fc_type_id NUMBER;
2804 l_freight_cost_int_id NUMBER;
2805
2806 l_msg_count NUMBER;
2807 l_msg_data VARCHAR2(3000);
2808 l_init_msg_list VARCHAR2(30) := NULL;
2809 l_commit VARCHAR2(1);
2810 l_return_status VARCHAR2(30);
2811
2812 l_loc_interface_error_rec WSH_INTERFACE_VALIDATIONS_PKG.interface_errors_rec_type;
2813 l_entity_interface_id NUMBER;
2814 l_delivery_id NUMBER;
2815 l_del_detail_id NUMBER;
2816 -- cursors
2817 CURSOR freight_int_cur IS
2818 SELECT FREIGHT_COST_INTERFACE_ID,
2819 FREIGHT_COST_ID,
2820 FREIGHT_COST_TYPE_ID,
2821 FREIGHT_COST_TYPE_CODE,
2822 UNIT_AMOUNT,
2823 CALCULATION_METHOD,
2824 UOM,
2825 QUANTITY,
2826 TOTAL_AMOUNT,
2827 CURRENCY_CODE,
2828 CONVERSION_DATE,
2829 CONVERSION_RATE,
2830 CONVERSION_TYPE_CODE,
2831 TRIP_INTERFACE_ID,
2832 STOP_INTERFACE_ID,
2833 DELIVERY_INTERFACE_ID,
2834 DELIVERY_LEG_INTERFACE_ID,
2835 DELIVERY_DETAIL_INTERFACE_ID,
2836 TRIP_ID,
2837 STOP_ID,
2838 DELIVERY_ID,
2839 DELIVERY_LEG_ID,
2840 ATTRIBUTE9,
2841 ATTRIBUTE10,
2842 ATTRIBUTE11,
2843 ATTRIBUTE12,
2844 ATTRIBUTE13,
2845 ATTRIBUTE6,
2846 ATTRIBUTE7,
2847 ATTRIBUTE8,
2848 ATTRIBUTE_CATEGORY,
2849 ATTRIBUTE1,
2850 ATTRIBUTE2,
2851 ATTRIBUTE3,
2852 ATTRIBUTE4,
2853 ATTRIBUTE5,
2854 DELIVERY_DETAIL_ID,
2855 ATTRIBUTE14,
2856 ATTRIBUTE15,
2857 CREATION_DATE,
2858 CREATED_BY,
2859 LAST_UPDATE_DATE,
2860 LAST_UPDATED_BY,
2861 LAST_UPDATE_LOGIN,
2862 PROGRAM_APPLICATION_ID,
2863 PROGRAM_ID,
2864 PROGRAM_UPDATE_DATE,
2865 REQUEST_ID,
2866 FREIGHT_CODE,
2867 INTERFACE_ACTION_CODE
2868 FROM wsh_freight_costs_interface
2869 WHERE (delivery_detail_interface_id = NVL(p_del_detail_interface_id, -99999))
2870 OR (delivery_interface_id = NVL(p_delivery_interface_id, -99999))
2871 OR (stop_interface_id = NVL(p_stop_interface_id, -99999))
2872 OR (trip_interface_id = NVL(p_trip_interface_id, -99999))
2873 AND INTERFACE_ACTION_CODE = '94X_INBOUND';
2874
2875 CURSOR fc_type_id(l_fc_type_code VARCHAR2) IS
2876 SELECT freight_cost_type_id
2877 FROM wsh_freight_cost_types
2878 WHERE name = l_fc_type_code;
2879
2880 --exceptions
2881 no_freight_record exception;
2882 invalid_input exception;
2883 invalid_freight_cost_type exception;
2884
2885 --
2886 l_debug_on BOOLEAN;
2887 --
2888 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PROCESS_INT_FREIGHT_COSTS';
2889 --
2890 BEGIN
2891 --
2892 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2893 --
2894 IF l_debug_on IS NULL
2895 THEN
2896 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2897 END IF;
2898 --
2899 IF l_debug_on THEN
2900 wsh_debug_sv.push(l_module_name,'Process_Int_Freight_Costs');
2901 wsh_debug_sv.log (l_module_name,'Delivery interface Id', p_delivery_interface_id);
2902 wsh_debug_sv.log (l_module_name,'del_detail_interface_id', p_del_detail_interface_id);
2903 END IF;
2904
2905 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2906
2907
2908 IF(p_del_detail_interface_id IS NOT NULL) THEN
2909 l_entity_interface_id := p_del_detail_interface_id;
2910 SELECT delivery_detail_id INTO l_del_detail_id
2911 FROM wsh_del_details_interface
2912 WHERE delivery_detail_interface_id = p_del_detail_interface_id
2913 AND INTERFACE_ACTION_CODE = '94X_INBOUND';
2914 IF l_debug_on THEN
2915 wsh_debug_sv.log (l_module_name, 'Delivery Detail Id', l_del_detail_id);
2916 END IF;
2917 IF(l_del_detail_id IS NULL) THEN
2918 IF l_debug_on THEN
2919 wsh_debug_sv.logmsg(l_module_name, 'Null Delivery Detail Id');
2920 END IF;
2921 raise invalid_input;
2922 END IF;
2923 ELSIF(p_delivery_interface_id IS NOT NULL) THEN
2924 l_entity_interface_id := p_delivery_interface_id;
2925 SELECT delivery_id INTO l_delivery_id
2926 FROM wsh_new_del_interface
2927 WHERE delivery_interface_id = p_delivery_interface_id
2928 AND INTERFACE_ACTION_CODE = '94X_INBOUND';
2929
2930 IF l_debug_on THEN
2931 wsh_debug_sv.log (l_module_name, 'Delivery Id', l_delivery_id);
2932 END IF;
2933 IF(l_delivery_id IS NULL) THEN
2934 IF l_debug_on THEN
2935 wsh_debug_sv.logmsg(l_module_name, 'Null Delivery Id');
2936 END IF;
2937 raise invalid_input;
2938 END IF;
2939
2940 ELSIF(p_stop_interface_id IS NOT NULL) THEN
2941 l_entity_interface_id := p_stop_interface_id;
2942 ELSIF(p_trip_interface_id IS NOT NULL) THEN
2943 l_entity_interface_id := p_trip_interface_id;
2944 ELSE
2945 IF l_debug_on THEN
2946 wsh_debug_sv.logmsg(l_module_name, 'Entity IDs are Null');
2947 END IF;
2948 raise invalid_input;
2949 END IF;
2950
2951 -- call the public api for creating freight costs
2952
2953 FOR l_freight_int_rec in freight_int_cur LOOP
2954
2955 IF l_debug_on THEN
2956 wsh_debug_sv.log (l_module_name, 'Freight Cost Type Code', l_freight_int_rec.freight_cost_type_code);
2957 END IF;
2958
2959 IF(l_freight_int_rec.freight_cost_type_code IS NOT NULL) THEN
2960 OPEN fc_type_id(l_freight_int_rec.freight_cost_type_code);
2961 FETCH fc_type_id INTO l_fc_type_id;
2962 CLOSE fc_type_id;
2963 ELSE
2964 raise invalid_freight_cost_type;
2965 END IF;
2966
2967 IF l_debug_on THEN
2968 wsh_debug_sv.log (l_module_name, 'Freight Cost Type Id', l_fc_type_id);
2969 END IF;
2970 IF(l_fc_type_id IS NULL) THEN
2971 IF l_debug_on THEN
2972 wsh_debug_sv.logmsg(l_module_name, 'Invalid Freight Cost Type');
2973 END IF;
2974
2975 l_loc_interface_error_rec.p_interface_table_name := 'WSH_FREIGHT_COSTS_INTERFACE';
2976 l_loc_interface_error_rec.p_interface_id := l_entity_interface_id;
2977
2978 Log_Errors(
2979 p_loc_interface_errors_rec => l_loc_interface_error_rec,
2980 p_msg_data => 'Invalid Freight Cost Type',
2981 p_api_name => 'WSH_FREIGHT_COSTS_PUB.Create_Update_Freight_Costs',
2982 x_return_status => l_return_status);
2983 IF l_debug_on THEN
2984 wsh_debug_sv.log (l_module_name, 'Log_Errors l_return_status',l_return_status);
2985 END IF;
2986 raise invalid_freight_cost_type;
2987 ELSE
2988 l_freight_costs_info.freight_cost_type_id := l_fc_type_id;
2989 END IF;
2990
2991 l_freight_costs_info.freight_cost_id := l_freight_int_rec.freight_cost_id;
2992
2993 l_freight_costs_info.unit_amount := l_freight_int_rec.unit_amount;
2994 l_freight_costs_info.currency_code := l_freight_int_rec.currency_code;
2995 l_freight_costs_info.conversion_date := l_freight_int_rec.conversion_date;
2996 l_freight_costs_info.conversion_rate := l_freight_int_rec.conversion_rate;
2997 l_freight_costs_info.conversion_type_code := l_freight_int_rec.conversion_type_code;
2998 l_freight_costs_info.trip_id := l_freight_int_rec.trip_id;
2999 l_freight_costs_info.stop_id := l_freight_int_rec.stop_id;
3000
3001 l_freight_costs_info.delivery_id := l_delivery_id;
3002 l_freight_costs_info.delivery_leg_id := l_freight_int_rec.delivery_leg_id;
3003 l_freight_costs_info.delivery_detail_id := l_del_detail_id;
3004
3005 l_freight_costs_info.attribute_category := l_freight_int_rec.attribute_category;
3006 l_freight_costs_info.attribute1 := l_freight_int_rec.attribute1;
3007 l_freight_costs_info.attribute2 := l_freight_int_rec.attribute2;
3008 l_freight_costs_info.attribute3 := l_freight_int_rec.attribute3;
3009 l_freight_costs_info.attribute4 := l_freight_int_rec.attribute4;
3010 l_freight_costs_info.attribute5 := l_freight_int_rec.attribute5;
3011 l_freight_costs_info.attribute6 := l_freight_int_rec.attribute6;
3012 l_freight_costs_info.attribute7 := l_freight_int_rec.attribute7;
3013 l_freight_costs_info.attribute8 := l_freight_int_rec.attribute8;
3014 l_freight_costs_info.attribute9 := l_freight_int_rec.attribute9;
3015 l_freight_costs_info.attribute10 := l_freight_int_rec.attribute10;
3016 l_freight_costs_info.attribute11 := l_freight_int_rec.attribute11;
3017 l_freight_costs_info.attribute12 := l_freight_int_rec.attribute12;
3018 l_freight_costs_info.attribute13 := l_freight_int_rec.attribute13;
3019 l_freight_costs_info.attribute14 := l_freight_int_rec.attribute14;
3020 l_freight_costs_info.attribute15 := l_freight_int_rec.attribute15;
3021
3022 IF l_debug_on THEN
3023 wsh_debug_sv.logmsg(l_module_name,'Calling freight public api');
3024 wsh_debug_sv.log (l_module_name, 'Unit amount', l_freight_costs_info.unit_amount);
3025 wsh_debug_sv.log (l_module_name, 'Currency code', l_freight_costs_info.currency_code);
3026 wsh_debug_sv.log (l_module_name, 'Delivery Id', l_freight_costs_info.delivery_id);
3027 wsh_debug_sv.log (l_module_name, 'Delivery Detail Id',l_freight_costs_info.delivery_detail_id);
3028 wsh_debug_sv.log (l_module_name, 'Trip Id', l_freight_costs_info.trip_id);
3029 wsh_debug_sv.log (l_module_name, 'Trip Name', l_freight_costs_info.trip_name);
3030 wsh_debug_sv.log (l_module_name, 'Stop Id', l_freight_costs_info.stop_id);
3031 END IF;
3032
3033 WSH_FREIGHT_COSTS_PUB.Create_Update_Freight_Costs (
3034 p_api_version_number => 1.0,
3035 p_init_msg_list => l_init_msg_list,
3036 p_commit => l_commit,
3037 x_return_status => l_return_status,
3038 x_msg_count => l_msg_count,
3039 x_msg_data => l_msg_data,
3040 p_pub_freight_costs => l_freight_costs_info,
3041 p_action_code => 'CREATE',
3042 x_freight_cost_id => l_freight_cost_id);
3043
3044 IF l_debug_on THEN
3045 wsh_debug_sv.log (l_module_name, 'return status from freight public api', l_return_status);
3046 wsh_debug_sv.log (l_module_name, 'Create Update Freight Costs api msg count', l_msg_count);
3047 wsh_debug_sv.log (l_module_name, 'Create Update Freight Costs api msg', l_msg_data);
3048 wsh_debug_sv.log (l_module_name, 'freight_cost id created', l_freight_cost_id);
3049 END IF;
3050
3051 IF(l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
3052 l_loc_interface_error_rec.p_interface_table_name := 'WSH_FREIGHT_COSTS_INTERFACE';
3053 l_loc_interface_error_rec.p_interface_id := l_entity_interface_id;
3054
3055 Log_Errors(
3056 p_loc_interface_errors_rec => l_loc_interface_error_rec,
3057 p_msg_data => l_msg_data,
3058 p_api_name => 'WSH_FREIGHT_COSTS_PUB.Create_Update_Freight_Costs',
3059 x_return_status => l_return_status);
3060 IF l_debug_on THEN
3061 wsh_debug_sv.log (l_module_name, 'Log_Errors l_return_status',l_return_status);
3062 END IF;
3063 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3064 END IF;
3065
3066 END LOOP;
3067
3068 IF l_debug_on THEN
3069 wsh_debug_sv.pop(l_module_name);
3070 END IF;
3071
3072 EXCEPTION
3073 WHEN invalid_input THEN
3074 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3075 IF l_debug_on THEN
3076 WSH_DEBUG_SV.logmsg(l_module_name,'invalid_input exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3077 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_input');
3078 END IF;
3079 WHEN invalid_freight_cost_type THEN
3080 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3081 IF l_debug_on THEN
3082 WSH_DEBUG_SV.logmsg(l_module_name,'invalid_freight_cost_type exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3083 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_freight_cost_type');
3084 END IF;
3085 WHEN no_freight_record THEN
3086 null;
3087 IF l_debug_on THEN
3088 WSH_DEBUG_SV.logmsg(l_module_name,'no_freight_record exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3089 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:no_freight_record');
3090 END IF;
3091 WHEN Others THEN
3092 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3093 IF l_debug_on THEN
3094 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
3095 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3096 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3097 END IF;
3098
3099 END Process_Int_Freight_Costs;
3100
3101
3102 /*
3103 -----------------------------------------------------------------------------
3104 PROCEDURE : Update_Delivery_Details
3105 PARAMETERS : p_changed_det_attributes IN WSH_INTERFACE.ChangedAttributeTabType
3106 x_return_status - return status of API
3107 DESCRIPTION :
3108 -- This is an internal procedure, used by Process_Interfaced_Del_Details
3109 -- This will be called for any updates of delivery details
3110
3111 -- history: 1/13/03 jckwok added a parameter for action_code to distinguish
3112 -- between UPDATE and CANCEL actions.
3113 ------------------------------------------------------------------------------
3114 */
3115
3116
3117 PROCEDURE Update_Delivery_Details(
3118 p_source_code IN VARCHAR2, -- DEFAULT 'OE' in spec
3119 p_delivery_interface_id IN NUMBER,
3120 p_action_code IN VARCHAR2, -- jckwok
3121 x_return_status OUT NOCOPY VARCHAR2
3122 ) IS
3123
3124 -- public api variables
3125 l_msg_count NUMBER;
3126 l_msg_data VARCHAR2(3000);
3127 l_init_msg_list VARCHAR2(30) := NULL;
3128 l_commit VARCHAR2(1) ;
3129 l_return_status VARCHAR2(30);
3130
3131 l_loc_interface_error_rec WSH_INTERFACE_VALIDATIONS_PKG.interface_errors_rec_type;
3132 l_DETAIL_INFO_TAB WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Attr_Tbl_Type;
3133 l_IN_REC WSH_GLBL_VAR_STRCT_GRP.detailInRecType;
3134 l_OUT_REC WSH_GLBL_VAR_STRCT_GRP.detailOutRecType;
3135 --exceptions
3136 update_shipping_att_failed exception;
3137 cont_upd_ship_att_failed exception;
3138 --
3139 l_debug_on BOOLEAN;
3140 --
3141 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_DELIVERY_DETAILS';
3142 --
3143 BEGIN
3144 --
3145 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3146 --
3147 IF l_debug_on IS NULL
3148 THEN
3149 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3150 END IF;
3151 --
3152 IF l_debug_on THEN
3153 wsh_debug_sv.push(l_module_name,'Update_Delivery_Details');
3154 wsh_debug_sv.log (l_module_name, 'Source Code', p_source_code);
3155 wsh_debug_sv.log (l_module_name, 'Delivery Interface Id', p_delivery_interface_id);
3156 wsh_debug_sv.log (l_module_name, 'Update Table Count', G_Update_Attributes_Tab.count);
3157 wsh_debug_sv.log(l_module_name, 'Serial Range Tab count',G_SERIAL_RANGE_TAB.count);
3158 END IF;
3159
3160 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3161 -- Use the global table to call create_update Group API
3162
3163 IF(G_Update_Attributes_Tab.count > 0 ) THEN
3164 l_in_rec.caller := 'WSH_INBOUND';
3165 --jckwok: set UPDATE or CANCEL action codes
3166 l_in_rec.action_code := p_action_code;
3167 l_in_rec.phase := 1;
3168
3169 wsh_delivery_Details_grp.create_update_delivery_detail(
3170 P_API_VERSION_NUMBER => 1.0,
3171 P_INIT_MSG_LIST => FND_API.G_FALSE,
3172 P_COMMIT => FND_API.G_FALSE,
3173 x_RETURN_STATUS => l_RETURN_STATUS,
3174 X_MSG_COUNT => l_MSG_COUNT,
3175 X_MSG_DATA => l_MSG_DATA,
3176 P_DETAIL_INFO_TAB => G_Update_Attributes_Tab,
3177 P_IN_REC => l_IN_REC,
3178 X_OUT_REC => l_OUT_REC,
3179 P_SERIAL_RANGE_TAB => G_SERIAL_RANGE_TAB
3180 );
3181
3182 IF l_debug_on THEN
3183 wsh_debug_sv.log (l_module_name,'Return Status from create_update group api', l_return_status);
3184 wsh_debug_sv.log (l_module_name, 'Update Ship Attr api msg count', l_msg_count);
3185 wsh_debug_sv.log (l_module_name, 'Update Ship Attr api msg data', l_msg_data);
3186 END IF;
3187
3188 IF(l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
3189 -- Need to insert record in interface errors table only
3190 -- for 'OE' source code, i.e during 945 inbound
3191 -- For 940 inbound - cancel case, there may not be any
3192 -- data in interface tables.
3193
3194 IF(p_delivery_interface_id IS NOT NULL) THEN
3195 l_loc_interface_error_rec.p_interface_table_name := 'WSH_NEW_DEL_INTERFACE';
3196 l_loc_interface_error_rec.p_interface_id := p_delivery_interface_id;
3197
3198 Log_Errors(
3199 p_loc_interface_errors_rec => l_loc_interface_error_rec,
3200 p_msg_data => l_msg_data,
3201 p_api_name =>'WSH_DELIVERY_DETAILS_GRP.Create_Update_Delivery_Detail',
3202 x_return_status => l_return_status);
3203
3204 IF l_debug_on THEN
3205 wsh_debug_sv.log (l_module_name,'Log_Errors l_return_status',l_return_status);
3206 END IF;
3207
3208 END IF;
3209 raise update_shipping_att_failed;
3210 END IF;
3211 END IF; -- if G_Update_Attributes_Tab.count
3212
3213 IF l_debug_on THEN
3214 wsh_debug_sv.pop(l_module_name);
3215 END IF;
3216
3217 EXCEPTION
3218 WHEN update_shipping_att_failed THEN
3219 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3220 IF l_debug_on THEN
3221 WSH_DEBUG_SV.logmsg(l_module_name,'update_shipping_att_failed exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3222 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:update_shipping_att_failed');
3223 END IF;
3224 WHEN cont_upd_ship_att_failed THEN
3225 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3226 IF l_debug_on THEN
3227 WSH_DEBUG_SV.logmsg(l_module_name,'cont_upd_ship_att_failed exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3228 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:cont_upd_ship_att_failed');
3229 END IF;
3230 WHEN Others THEN
3231 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3232 IF l_debug_on THEN
3233 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
3234 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3235 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3236 END IF;
3237 END Update_Delivery_Details;
3238
3239
3240 /*
3241 -----------------------------------------------------------------------------
3242 PROCEDURE : Create_Update_Trip_For_Dlvy
3243 PARAMETERS : p_delivery_id
3244 x_return_status - return status of API
3245 DESCRIPTION :
3246 - This procedure is called to create/update the trip for the delivery
3247 which has been updated with the inbound 945 transaction data
3248 - If a trip already exists for the delivery in the base tables, then
3249 this procedure just updates the trip and trip_stop tables based on the
3250 values in the trip interface table and trip_stop interface table.
3251 - If a trip does not already exist, then this procedure first calls
3252 autocreate_trip to create a trip for the delivery.
3253 -- Then it updates the newly created trip and trip_stops with the
3254 values from the interface table data
3255
3256 ------------------------------------------------------------------------------
3257 */
3258
3259 PROCEDURE Create_Update_Trip_For_Dlvy(
3260 p_delivery_id IN NUMBER,
3261 x_pickup_stop_id OUT NOCOPY NUMBER,
3262 x_dropoff_stop_id OUT NOCOPY NUMBER,
3263 x_trip_id OUT NOCOPY NUMBER,
3264 x_return_status OUT NOCOPY VARCHAR2) IS
3265
3266 -- variables
3267 l_del_rows wsh_util_core.id_tab_type;
3268
3269 l_pickup_stop_id NUMBER ;
3270 l_dropoff_stop_id NUMBER ;
3271 l_delivery_leg_id NUMBER ;
3272
3273 l_trip_id NUMBER;
3274 l_trip_name VARCHAR2(30);
3275
3276 l_trip_interface_id NUMBER;
3277 l_stop_interface_id NUMBER;
3278
3279 l_pickup_stop_int_id NUMBER;
3280 l_dropoff_stop_int_id NUMBER;
3281
3282 l_return_status VARCHAR2(30);
3283
3284 l_stop_tab WSH_UTIL_CORE.id_tab_type; -- DBI Project
3285 l_dbi_rs VARCHAR2(1); -- DBI Project
3286
3287 -- cursors
3288 CURSOR del_trip_stops IS
3289 SELECT wdg.pick_up_stop_id, wdg.drop_off_stop_id, wts.trip_id
3290 FROM wsh_delivery_legs wdg, wsh_trip_stops wts
3291 WHERE wdg.delivery_id = p_delivery_id
3292 AND wdg.pick_up_stop_id = wts.stop_id;
3293
3294 CURSOR int_del_trip_stops IS
3295 SELECT wdli.pick_up_stop_interface_id, wdli.drop_off_stop_interface_id, wtsi.trip_interface_id
3296 FROM wsh_del_legs_interface wdli, wsh_trip_stops_interface wtsi
3297 WHERE wdli.delivery_id = p_delivery_id
3298 AND wdli.pick_up_stop_interface_id = wtsi.stop_interface_id
3299 AND WDLI.INTERFACE_ACTION_CODE = '94X_INBOUND'
3300 AND WTSI.INTERFACE_ACTION_CODE = '94X_INBOUND';
3301
3302
3303 CURSOR int_pickup_stop_cur(l_stop_interface_id NUMBER) IS
3304 SELECT actual_departure_date, departure_seal_code
3305 FROM wsh_trip_stops_interface
3306 WHERE stop_interface_id = l_stop_interface_id
3307 AND INTERFACE_ACTION_CODE = '94X_INBOUND';
3308
3309 CURSOR int_dropoff_stop_cur(l_stop_interface_id NUMBER) IS
3310 SELECT actual_arrival_date
3311 FROM wsh_trip_stops_interface
3312 WHERE stop_interface_id = l_stop_interface_id
3313 AND INTERFACE_ACTION_CODE = '94X_INBOUND';
3314
3315 CURSOR int_trip_cur(l_trip_interface_id NUMBER) IS
3316 SELECT vehicle_number, vehicle_num_prefix, route_id, routing_instructions,
3317 --Bug 3458160
3318 operator
3319 FROM wsh_trips_interface
3320 WHERE trip_interface_id = l_trip_interface_id
3321 AND INTERFACE_ACTION_CODE = '94X_INBOUND';
3322
3323 -- cursor records
3324 int_trip_rec int_trip_cur%ROWTYPE;
3325 int_dropoff_stop_rec int_dropoff_stop_cur%ROWTYPE;
3326 int_pickup_stop_rec int_pickup_stop_cur%ROWTYPE;
3327
3328 --exceptions
3329 trip_creation_failed exception;
3330 invalid_input exception;
3331 --
3332 l_debug_on BOOLEAN;
3333 --
3334 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_UPDATE_TRIP_FOR_DLVY';
3335 --
3336 BEGIN
3337 --
3338 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3339 --
3340 IF l_debug_on IS NULL
3341 THEN
3342 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3343 END IF;
3344 --
3345 IF l_debug_on THEN
3346 wsh_debug_sv.push(l_module_name,'Create_Update_Trip_For_Dlvy');
3347 wsh_debug_sv.log (l_module_name, 'Delivery id', p_delivery_id);
3348 END IF;
3349
3350 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3351
3352 IF(p_delivery_id IS NULL) THEN
3353 raise invalid_input;
3354 END IF;
3355
3356 -- check if a trip exists
3357 OPEN del_trip_stops;
3358 FETCH del_trip_stops INTO l_pickup_stop_id, l_dropoff_stop_id, l_trip_id;
3359 CLOSE del_trip_stops;
3360
3361 IF l_debug_on THEN
3362 wsh_debug_sv.log (l_module_name, 'Pickup Stop Id', l_pickup_stop_id);
3363 wsh_debug_sv.log (l_module_name, 'Dropoff Stop Id', l_dropoff_stop_id);
3364 wsh_debug_sv.log (l_module_name, 'Trip Id', l_trip_id);
3365 END IF;
3366 IF (l_pickup_stop_id IS NULL) THEN
3367
3368 -- trip does not exist. so do autocreate_trip
3369
3370 l_del_rows(1) := p_delivery_id;
3371
3372 wsh_trips_actions.autocreate_trip(
3373 p_del_rows => l_del_rows,
3374 x_trip_id => l_trip_id,
3375 x_trip_name => l_trip_name,
3376 x_return_status => l_return_status);
3377
3378 IF l_debug_on THEN
3379 wsh_debug_sv.log (l_module_name, 'Return status from autocreate trip', l_return_status);
3380 wsh_debug_sv.log (l_module_name, 'Trip created', l_trip_id);
3381 END IF;
3382
3383 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
3384 x_return_status := l_return_status;
3385 raise trip_creation_failed;
3386 END IF;
3387
3388 -- Now that a trip is created, get the stop and trip info
3389 OPEN del_trip_stops;
3390 FETCH del_trip_stops INTO l_pickup_stop_id, l_dropoff_stop_id, l_trip_id;
3391 CLOSE del_trip_stops;
3392
3393 IF l_debug_on THEN
3394 wsh_debug_sv.log (l_module_name, 'After doing autocreate trip');
3395 wsh_debug_sv.log (l_module_name, 'Pickup Stop Id', l_pickup_stop_id);
3396 wsh_debug_sv.log (l_module_name, 'Dropoff Stop Id', l_dropoff_stop_id);
3397 wsh_debug_sv.log (l_module_name, 'Trip Id', l_trip_id);
3398 END IF;
3399
3400 END IF; -- if l_pickup_stop_id is null
3401
3402 -- get the interface trip_stop_ids and trip_id
3403 OPEN int_del_trip_stops;
3404 FETCH int_del_trip_stops
3405 INTO l_pickup_stop_int_id, l_dropoff_stop_int_id, l_trip_interface_id;
3406 CLOSE int_del_trip_stops;
3407
3408 IF l_debug_on THEN
3409 wsh_debug_sv.log (l_module_name, 'Pickup Stop Interface Id', l_pickup_stop_int_id);
3410 wsh_debug_sv.log (l_module_name, 'Dropoff Stop Interface Id', l_dropoff_stop_int_id);
3411 wsh_debug_sv.log (l_module_name, 'Trip Interface Id', l_trip_interface_id);
3412 END IF;
3413
3414 -- get the interface trip_stop info
3415 OPEN int_pickup_stop_cur(l_pickup_stop_int_id);
3416 FETCH int_pickup_stop_cur INTO int_pickup_stop_rec;
3417
3418 IF(int_pickup_stop_cur%NOTFOUND) THEN
3419 NULL;
3420 -- need to decide what should be done
3421 END IF;
3422 IF l_debug_on THEN
3423 wsh_debug_sv.log (l_module_name, 'Updating Stop Id', l_pickup_stop_id);
3424 END IF;
3425
3426 -- update the base trip_stops
3427 IF l_pickup_stop_id IS NOT NULL THEN
3428
3429 UPDATE wsh_trip_stops
3430 SET actual_departure_date = int_pickup_stop_rec.actual_departure_date,
3431 departure_seal_code = int_pickup_stop_rec.departure_seal_code
3432 WHERE stop_id = l_pickup_stop_id;
3433
3434 --
3435 -- DBI Project
3436 -- Updating WSH_TRIP_STOPS.
3437 -- Call DBI API after the Update.
3438 -- This API will also check for DBI Installed or not
3439 IF l_debug_on THEN
3440 WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Stop id -',l_pickup_stop_id);
3441 END IF;
3442 l_stop_tab(1) := l_pickup_stop_id;
3443 WSH_INTEGRATION.DBI_Update_Trip_Stop_Log
3444 (p_stop_id_tab => l_stop_tab,
3445 p_dml_type => 'UPDATE',
3446 x_return_status => l_dbi_rs);
3447
3448 IF l_debug_on THEN
3449 WSH_DEBUG_SV.log(l_module_name,'Return Status after DBI Call-',l_dbi_rs);
3450 END IF;
3451 IF l_dbi_rs = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
3452 x_return_status := l_dbi_rs;
3453 -- just pass this return status to caller API
3454 IF l_debug_on THEN
3455 WSH_DEBUG_SV.log(l_module_name,'DBI API Returned Unexpected error '||x_return_status);
3456 WSH_DEBUG_SV.pop(l_module_name);
3457 END IF;
3458 return;
3459 END IF;
3460 -- End of Code for DBI Project
3461 --
3462
3463 x_pickup_stop_id := l_pickup_stop_id;
3464
3465 END IF;
3466
3467 IF(int_pickup_stop_cur%ISOPEN) THEN
3468 CLOSE int_pickup_stop_cur;
3469 END IF;
3470
3471 OPEN int_dropoff_stop_cur(l_dropoff_stop_int_id);
3472 FETCH int_dropoff_stop_cur INTO int_dropoff_stop_rec;
3473
3474 IF(int_dropoff_stop_cur%NOTFOUND) THEN
3475 NULL;
3476 -- need to decide what should be done
3477 END IF;
3478 IF l_debug_on THEN
3479 wsh_debug_sv.log (l_module_name, 'Updating Drop off Stop Id', l_dropoff_stop_id);
3480 END IF;
3481
3482 IF l_dropoff_stop_id IS NOT NULL THEN
3483 UPDATE wsh_trip_stops
3484 SET actual_arrival_date = int_dropoff_stop_rec.actual_arrival_date
3485 WHERE stop_id = l_dropoff_stop_id;
3486
3487 --
3488 -- DBI Project
3489 -- Updating WSH_TRIP_STOPS.
3490 -- Call DBI API after the Update.
3491 -- This API will also check for DBI Installed or not
3492 IF l_debug_on THEN
3493 WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Stop id -',l_dropoff_stop_id);
3494 END IF;
3495 l_stop_tab(1) := l_dropoff_stop_id;
3496 WSH_INTEGRATION.DBI_Update_Trip_Stop_Log
3497 (p_stop_id_tab => l_stop_tab,
3498 p_dml_type => 'UPDATE',
3499 x_return_status => l_dbi_rs);
3500
3501 IF l_debug_on THEN
3502 WSH_DEBUG_SV.log(l_module_name,'Return Status after DBI Call-',l_dbi_rs);
3503 END IF;
3504 IF l_dbi_rs = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
3505 x_return_status := l_dbi_rs;
3506 -- just pass this return status to caller API
3507 IF l_debug_on THEN
3508 WSH_DEBUG_SV.log(l_module_name,'DBI API Returned Unexpected error '||x_return_status);
3509 WSH_DEBUG_SV.pop(l_module_name);
3510 END IF;
3511 return;
3512 END IF;
3513 -- End of Code for DBI Project
3514 --
3515
3516 x_dropoff_stop_id := l_dropoff_stop_id;
3517 END IF;
3518
3519 IF(int_dropoff_stop_cur%ISOPEN) THEN
3520 CLOSE int_dropoff_stop_cur;
3521 END IF;
3522
3523 -- get the interface trip info
3524 OPEN int_trip_cur(l_trip_interface_id);
3525 FETCH int_trip_cur INTO int_trip_rec;
3526
3527 IF(int_trip_cur%NOTFOUND) THEN
3528 NULL;
3529 -- need to decide what should be done
3530 END IF;
3531
3532 IF l_debug_on THEN
3533 wsh_debug_sv.log (l_module_name, 'Updating Trip Id', l_trip_id);
3534 END IF;
3535 -- update the base trip
3536 IF l_trip_id IS NOT NULL THEN
3537
3538 UPDATE wsh_trips
3539 SET vehicle_num_prefix = int_trip_rec.vehicle_num_prefix,
3540 vehicle_number = int_trip_rec.vehicle_number,
3541 route_id = int_trip_rec.route_id,
3542 routing_instructions = int_trip_rec.routing_instructions,
3543 --Bug 3458160
3544 operator = int_trip_rec.operator
3545 WHERE trip_id = l_trip_id;
3546
3547 x_trip_id := l_trip_id;
3548
3549 END IF; -- if l_trip_id is not null
3550
3551 IF(int_trip_cur%ISOPEN) THEN
3552 CLOSE int_trip_cur;
3553 END IF;
3554
3555 IF l_debug_on THEN
3556 wsh_debug_sv.pop(l_module_name);
3557 END IF;
3558 EXCEPTION
3559 WHEN trip_creation_failed THEN
3560 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3561 IF l_debug_on THEN
3562 WSH_DEBUG_SV.logmsg(l_module_name,'trip_creation_failed exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3563 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:trip_creation_failed');
3564 END IF;
3565 WHEN invalid_input THEN
3566 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3567 IF l_debug_on THEN
3568 WSH_DEBUG_SV.logmsg(l_module_name,'invalid_input exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3569 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_input');
3570 END IF;
3571 WHEN Others THEN
3572 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3573 IF l_debug_on THEN
3574 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
3575 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3576 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3577 END IF;
3578
3579 END Create_Update_Trip_For_Dlvy;
3580
3581
3582 PROCEDURE Int_Trip_Stop_Info(
3583 p_delivery_interface_id IN NUMBER,
3584 p_act_dep_date IN DATE,
3585 p_dep_seal_code IN VARCHAR2,
3586 p_act_arr_date IN DATE,
3587 p_trip_vehicle_num IN VARCHAR2,
3588 p_trip_veh_num_pfx IN VARCHAR2,
3589 p_trip_route_id IN NUMBER,
3590 p_trip_routing_ins IN VARCHAR2,
3591 --Bug 3458160
3592 p_operator IN VARCHAR2,
3593 x_return_status OUT NOCOPY VARCHAR2) IS
3594
3595 -- variables
3596 l_del_leg_interface_id NUMBER;
3597 l_pickup_stop_interface_id NUMBER;
3598 l_dropoff_stop_interface_id NUMBER;
3599 l_trip_interface_id NUMBER;
3600
3601 --
3602 l_debug_on BOOLEAN;
3603 --
3604 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'INT_TRIP_STOP_INFO';
3605 --
3606 BEGIN
3607 --
3608 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3609 --
3610 IF l_debug_on IS NULL
3611 THEN
3612 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3613 END IF;
3614 --
3615 IF l_debug_on THEN
3616 wsh_debug_sv.push(l_module_name,'Int_Trip_Stop_Info');
3617 wsh_debug_sv.log (l_module_name, 'Delivery Interface id', p_delivery_interface_id);
3618 wsh_debug_sv.log (l_module_name, 'Act Departure Date', p_act_dep_date);
3619 wsh_debug_sv.log (l_module_name, 'Dep Seal Code', p_dep_seal_code);
3620 wsh_debug_sv.log (l_module_name, 'Act Arrival Date', p_act_arr_date);
3621 wsh_debug_sv.log (l_module_name, 'Vehicle Num', p_trip_vehicle_num);
3622 wsh_debug_sv.log (l_module_name, 'Vehicle Num Prefix', p_trip_veh_num_pfx);
3623 wsh_debug_sv.log (l_module_name, 'Route Id', p_trip_route_id);
3624 wsh_debug_sv.log (l_module_name, 'Routing Ins', p_trip_routing_ins);
3625 wsh_debug_sv.log (l_module_name, 'p_operator', p_operator);
3626 END IF;
3627
3628 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3629 -- get delivery_leg_interface_id, stop_interface_id, trip_interface_id
3630
3631 SELECT
3632 WSH_DEL_LEGS_INTERFACE_S.nextval,
3633 WSH_TRIP_STOPS_INTERFACE_S.nextval,
3634 WSH_TRIPS_INTERFACE_S.nextval
3635 INTO l_del_leg_interface_id,
3636 l_pickup_stop_interface_id,
3637 l_trip_interface_id
3638 FROM dual;
3639
3640 SELECT
3641 WSH_TRIP_STOPS_INTERFACE_S.nextval
3642 INTO l_dropoff_stop_interface_id
3643 FROM dual;
3644
3645 -- insert record into wsh_del_legs_interface
3646
3647 INSERT into wsh_del_legs_interface(
3648 DELIVERY_LEG_INTERFACE_ID,
3649 DELIVERY_INTERFACE_ID,
3650 PICK_UP_STOP_INTERFACE_ID,
3651 DROP_OFF_STOP_INTERFACE_ID,
3652 CREATION_DATE,
3653 CREATED_BY,
3654 LAST_UPDATE_DATE,
3655 LAST_UPDATED_BY,
3656 INTERFACE_ACTION_CODE)
3657 VALUES(
3658 l_del_leg_interface_id,
3659 p_delivery_interface_id,
3660 l_pickup_stop_interface_id,
3661 l_dropoff_stop_interface_id,
3662 SYSDATE,
3663 FND_GLOBAL.USER_ID,
3664 SYSDATE,
3665 FND_GLOBAL.USER_ID,
3666 '94X_INBOUND');
3667
3668 -- insert records into wsh_trip_stops_interface
3669 -- first the pickup stop
3670 INSERT INTO wsh_trip_stops_interface(
3671 STOP_INTERFACE_ID,
3672 TRIP_INTERFACE_ID,
3673 ACTUAL_DEPARTURE_DATE,
3674 DEPARTURE_SEAL_CODE,
3675 CREATION_DATE,
3676 CREATED_BY,
3677 LAST_UPDATE_DATE,
3678 LAST_UPDATED_BY,
3679 INTERFACE_ACTION_CODE)
3680 VALUES(
3681 l_pickup_stop_interface_id,
3682 l_trip_interface_id,
3683 p_act_dep_date,
3684 p_dep_seal_code,
3685 SYSDATE,
3686 FND_GLOBAL.USER_ID,
3687 SYSDATE,
3688 FND_GLOBAL.USER_ID,
3689 '94X_INBOUND');
3690
3691 -- then the dropoff stop
3692 INSERT INTO wsh_trip_stops_interface(
3693 STOP_INTERFACE_ID,
3694 TRIP_INTERFACE_ID,
3695 ACTUAL_ARRIVAL_DATE,
3696 CREATION_DATE,
3697 CREATED_BY,
3698 LAST_UPDATE_DATE,
3699 LAST_UPDATED_BY,
3700 INTERFACE_ACTION_CODE)
3701 VALUES(
3702 l_dropoff_stop_interface_id,
3703 l_trip_interface_id,
3704 p_act_arr_date,
3705 SYSDATE,
3706 FND_GLOBAL.USER_ID,
3707 SYSDATE,
3708 FND_GLOBAL.USER_ID,
3709 '94X_INBOUND');
3710
3711 -- insert records into wsh_trips_interface
3712 INSERT INTO wsh_trips_interface(
3713 TRIP_INTERFACE_ID,
3714 VEHICLE_NUM_PREFIX,
3715 VEHICLE_NUMBER,
3716 ROUTE_ID,
3717 ROUTING_INSTRUCTIONS,
3718 CREATION_DATE,
3719 CREATED_BY,
3720 LAST_UPDATE_DATE,
3721 LAST_UPDATED_BY,
3722 INTERFACE_ACTION_CODE,
3723 --Bug 3458160
3724 operator)
3725 VALUES (
3726 l_trip_interface_id,
3727 p_trip_veh_num_pfx,
3728 p_trip_vehicle_num,
3729 p_trip_route_id,
3730 p_trip_routing_ins,
3731 SYSDATE,
3732 FND_GLOBAL.USER_ID,
3733 SYSDATE,
3734 FND_GLOBAL.USER_ID,
3735 '94X_INBOUND',
3736 p_operator);
3737
3738 IF l_debug_on THEN
3739 wsh_debug_sv.pop(l_module_name);
3740 END IF;
3741 EXCEPTION
3742
3743 WHEN Others THEN
3744 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3745 IF l_debug_on THEN
3746 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
3747 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3748 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3749 END IF;
3750 END Int_Trip_Stop_Info;
3751
3752
3753 PROCEDURE Add_To_Update_Table
3754 (p_del_det_int_rec IN del_det_int_cur%ROWTYPE,
3755 p_update_mode IN VARCHAR2 DEFAULT 'UPDATE',
3756 p_delivery_id IN NUMBER,
3757 x_return_status OUT NOCOPY VARCHAR2) IS
3758
3759 -- variables
3760 --l_changed_attributes WSH_INTERFACE.ChangedAttributeRecType;
3761 l_changed_attributes WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Rec_Type;
3762 l_packing_detail_rec WSH_INTERFACE_COMMON_ACTIONS.PackingDetailRecType;
3763 l_intf_parent_det_id NUMBER;
3764
3765 -- cursors
3766 CURSOR base_detail_cur(l_cont_inst_id NUMBER) IS
3767 SELECT wdd.delivery_detail_id
3768 FROM wsh_delivery_details wdd
3769 WHERE source_line_id = l_cont_inst_id
3770 AND wdd.source_code = 'WSH'
3771 AND wdd.container_flag = 'Y'
3772 AND wdd.organization_id = p_del_det_int_rec.organization_id;
3773
3774 CURSOR intf_parent_det_cur IS
3775 SELECT wdai.parent_delivery_detail_id
3776 FROM wsh_del_assgn_interface wdai
3777 WHERE wdai.delivery_detail_interface_id = p_del_det_int_rec.delivery_detail_interface_id
3778 AND WDAI.INTERFACE_ACTION_CODE = '94X_INBOUND';
3779
3780 --exceptions
3781 packing_error exception;
3782
3783 --
3784 l_debug_on BOOLEAN;
3785 --
3786 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'ADD_TO_UPDATE_TABLE';
3787 --
3788 BEGIN
3789 --
3790 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3791 --
3792 IF l_debug_on IS NULL
3793 THEN
3794 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3795 END IF;
3796 --
3797 IF l_debug_on THEN
3798 wsh_debug_sv.push(l_module_name, 'Add_To_Update_Table');
3799 wsh_debug_sv.log (l_module_name, 'Update Mode' , p_update_mode);
3800 wsh_debug_sv.log (l_module_name, 'p_delivery_id', p_delivery_id);
3801 wsh_debug_sv.log(l_module_name, 'Delivery Detail Id', p_del_det_int_rec.delivery_detail_id);
3802 END IF;
3803
3804 -- Add To Packing Table, only for update cases
3805 IF(p_update_mode = 'UPDATE') THEN
3806 OPEN intf_parent_det_cur;
3807 FETCH intf_parent_det_cur INTO l_intf_parent_det_id;
3808 CLOSE intf_parent_det_cur;
3809
3810 IF l_debug_on THEN
3811 wsh_debug_sv.log (l_module_name, 'Parent del.detail in intef assgn', l_intf_parent_det_id);
3812 wsh_debug_sv.log(l_module_name, 'Organization id of detail', p_del_det_int_rec.organization_id);
3813 END IF;
3814
3815 IF(l_intf_parent_det_id IS NOT NULL) THEN
3816
3817 l_packing_detail_rec.delivery_detail_id := p_del_det_int_rec.delivery_detail_id;
3818
3819 OPEN base_detail_cur(l_intf_parent_det_id);
3820 FETCH base_detail_cur INTO l_packing_detail_rec.parent_delivery_detail_id;
3821
3822 IF l_debug_on THEN
3823 wsh_debug_sv.log (l_module_name, 'Base table detail id', l_packing_detail_rec.parent_delivery_detail_id);
3824 END IF;
3825
3826 IF( base_detail_cur%NOTFOUND) THEN
3827 raise packing_error;
3828 END IF;
3829
3830 CLOSE base_detail_cur;
3831
3832 G_Packing_Detail_Tab((G_Packing_Detail_Tab.count) +1) := l_packing_detail_rec;
3833
3834 END IF; -- if l_intf_parent_det_id
3835 END IF; -- if p_udpate_mode is UPDATE
3836
3837 l_changed_attributes.source_header_id := p_del_det_int_rec.source_header_id;
3838 l_changed_attributes.source_line_id := p_del_det_int_rec.source_line_id;
3839 l_changed_attributes.source_code := p_del_det_int_rec.source_code;
3840 -- l_changed_attributes.sold_to_org_id := p_del_det_int_rec.sold_to_org_id;
3841 -- l_changed_attributes.customer_number := p_del_det_int_rec.customer_number;
3842 l_changed_attributes.sold_to_contact_id := p_del_det_int_rec.sold_to_contact_id;
3843 -- l_changed_attributes.ship_from_org_id := p_del_det_int_rec.ship_from_org_id;
3844 -- l_changed_attributes.ship_to_org_id := p_del_det_int_rec.ship_to_location_id;
3845 l_changed_attributes.ship_to_contact_id := p_del_det_int_rec.ship_to_contact_id;
3846 -- l_changed_attributes.deliver_to_org_id := p_del_det_int_rec.deliver_to_org_id;
3847 l_changed_attributes.deliver_to_contact_id := p_del_det_int_rec.deliver_to_contact_id;
3848 -- l_changed_attributes.intmed_ship_to_org_id := p_del_det_int_rec.intmed_ship_to_org_id;
3849 l_changed_attributes.intmed_ship_to_contact_id := p_del_det_int_rec.intmed_ship_to_contact_id;
3850 l_changed_attributes.preferred_grade := p_del_det_int_rec.preferred_grade;
3851 l_changed_attributes.subinventory := p_del_det_int_rec.subinventory;
3852 l_changed_attributes.revision := p_del_det_int_rec.revision;
3853 l_changed_attributes.lot_number := p_del_det_int_rec.lot_number;
3854 -- HW OPMCONV - No need for sublot_number
3855 -- l_changed_attributes.sublot_number := p_del_det_int_rec.sublot_number;
3856 l_changed_attributes.customer_requested_lot_flag := p_del_det_int_rec.customer_requested_lot_flag;
3857 /* --kvenkate commenting the following line in patchset I
3858 l_changed_attributes.serial_number := nvl(p_del_det_int_rec.serial_number, FND_API.G_MISS_CHAR);
3859 */
3860 l_changed_attributes.serial_number := p_del_det_int_rec.serial_number;
3861 l_changed_attributes.locator_id := p_del_det_int_rec.locator_id;
3862 l_changed_attributes.master_container_item_id := p_del_det_int_rec.master_container_item_id;
3863 l_changed_attributes.detail_container_item_id := p_del_det_int_rec.detail_container_item_id;
3864
3865 -- No need to pass ship method code because when a delivery is present, the ship method code of
3866 -- delivery detail should not be updated.
3867 -- l_changed_attributes.shipping_method_code := p_del_det_int_rec.ship_method_code;
3868 l_changed_attributes.carrier_id := p_del_det_int_rec.carrier_id;
3869
3870 -- l_changed_attributes.freight_terms_code := p_del_det_int_rec.freight_terms_code;
3871 l_changed_attributes.shipment_priority_code := p_del_det_int_rec.shipment_priority_code;
3872 -- l_changed_attributes.fob_code := p_del_det_int_rec.fob_code;
3873 l_changed_attributes.dep_plan_required_flag := p_del_det_int_rec.dep_plan_required_flag;
3874 l_changed_attributes.customer_prod_seq := p_del_det_int_rec.customer_prod_seq;
3875 l_changed_attributes.customer_dock_code := p_del_det_int_rec.customer_dock_code;
3876 l_changed_attributes.gross_weight := p_del_det_int_rec.gross_weight;
3877 l_changed_attributes.net_weight := p_del_det_int_rec.net_weight;
3878 l_changed_attributes.weight_uom_code := p_del_det_int_rec.weight_uom_code;
3879 l_changed_attributes.volume := p_del_det_int_rec.volume;
3880 l_changed_attributes.volume_uom_code := p_del_det_int_rec.volume_uom_code;
3881 -- J: W/V Changes
3882 l_changed_attributes.filled_volume := p_del_det_int_rec.filled_volume;
3883 l_changed_attributes.fill_percent := p_del_det_int_rec.fill_percent;
3884 l_changed_attributes.wv_frozen_flag := p_del_det_int_rec.wv_frozen_flag;
3885
3886 l_changed_attributes.top_model_line_id := p_del_det_int_rec.top_model_line_id;
3887 l_changed_attributes.ato_line_id := p_del_det_int_rec.ato_line_id;
3888 l_changed_attributes.arrival_set_id := p_del_det_int_rec.arrival_set_id;
3889 l_changed_attributes.ship_model_complete_flag := p_del_det_int_rec.ship_model_complete_flag;
3890 l_changed_attributes.cust_po_number := p_del_det_int_rec.cust_po_number;
3891 l_changed_attributes.packing_instructions := p_del_det_int_rec.packing_instructions;
3892 l_changed_attributes.shipping_instructions := p_del_det_int_rec.shipping_instructions;
3893 IF l_debug_on THEN
3894 wsh_debug_sv.log(l_module_name,'Orig Container Name',
3895 p_del_det_int_rec.container_name);
3896 wsh_debug_sv.log(l_module_name,'delivery detail id',
3897 p_del_det_int_rec.delivery_detail_id);
3898 wsh_debug_sv.log(l_module_name,'container flag',
3899 p_del_det_int_rec.container_flag);
3900 END IF;
3901 IF (p_del_det_int_rec.container_flag = 'Y' )
3902 AND (NVL(p_del_det_int_rec.container_name ,FND_API.G_MISS_CHAR) <>
3903 FND_API.G_MISS_CHAR)
3904 AND (NVL(p_del_det_int_rec.delivery_detail_id,FND_API.G_MISS_NUM) <>
3905 FND_API.G_MISS_NUM)
3906 THEN
3907 l_changed_attributes.container_name :=
3908 SUBSTRB(TO_CHAR(p_del_det_int_rec.delivery_detail_id)||
3909 '-' || p_del_det_int_rec.container_name ,1,30);
3910 ELSE
3911 l_changed_attributes.container_name :=
3912 nvl(p_del_det_int_rec.container_name,FND_API.G_MISS_CHAR);
3913 END IF;
3914
3915 IF l_debug_on THEN
3916 wsh_debug_sv.log(l_module_name,'New container name',
3917 l_changed_attributes.container_name);
3918 END IF;
3919
3920 l_changed_attributes.container_flag := p_del_det_int_rec.container_flag ;
3921 l_changed_attributes.delivery_detail_id := p_del_det_int_rec.delivery_detail_id;
3922
3923 IF l_debug_on THEN
3924 wsh_debug_sv.log (l_module_name, 'Shipped Quantity ', p_del_det_int_rec.shipped_quantity);
3925 END IF;
3926
3927 IF nvl(p_del_det_int_rec.shipped_quantity,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM THEN
3928 l_changed_attributes.shipped_quantity := 0;
3929 ELSE
3930 l_changed_attributes.shipped_quantity := p_del_det_int_rec.shipped_quantity;
3931 END IF;
3932 l_changed_attributes.cycle_count_quantity := p_del_det_int_rec.cycle_count_quantity;
3933 l_changed_attributes.tracking_number := p_del_det_int_rec.tracking_number ;
3934 l_changed_attributes.attribute1 := p_del_det_int_rec.attribute1;
3935 l_changed_attributes.attribute2 := p_del_det_int_rec.attribute2;
3936 l_changed_attributes.attribute3 := p_del_det_int_rec.attribute3;
3937 l_changed_attributes.attribute4 := p_del_det_int_rec.attribute4;
3938 l_changed_attributes.attribute5 := p_del_det_int_rec.attribute5;
3939 l_changed_attributes.attribute6 := p_del_det_int_rec.attribute6;
3940 l_changed_attributes.attribute7 := p_del_det_int_rec.attribute7;
3941 l_changed_attributes.attribute8 := p_del_det_int_rec.attribute8;
3942 l_changed_attributes.attribute9 := p_del_det_int_rec.attribute9;
3943 l_changed_attributes.attribute10 := p_del_det_int_rec.attribute10;
3944 l_changed_attributes.attribute11 := p_del_det_int_rec.attribute11;
3945 l_changed_attributes.attribute12 := p_del_det_int_rec.attribute12;
3946 l_changed_attributes.attribute13 := p_del_det_int_rec.attribute13;
3947 l_changed_attributes.attribute14 := p_del_det_int_rec.attribute14;
3948 l_changed_attributes.attribute15 := p_del_det_int_rec.attribute15;
3949 l_changed_attributes.to_serial_number := p_del_det_int_rec.to_serial_number;
3950 l_changed_attributes.requested_quantity_uom := p_del_det_int_rec.requested_quantity_uom;
3951
3952 if l_debug_on then
3953 wsh_debug_sv.log(l_module_name, 'subinventory:', l_changed_attributes.subinventory);
3954 wsh_debug_sv.log(l_module_name, 'Container Name:', l_changed_attributes.container_name);
3955 wsh_debug_sv.log(l_module_name, 'Tracking Number', l_changed_attributes.tracking_number);
3956 end if;
3957
3958
3959
3960 G_Update_Attributes_Tab((G_Update_Attributes_Tab.count)+1) := l_changed_attributes;
3961
3962
3963 /* Patchset I: passing serial numbers to group api. so no need for the direct update of to_serial_number
3964 -- kvenkate. Removed the code for direct update.
3965 */
3966
3967 IF l_debug_on THEN
3968 wsh_debug_sv.pop(l_module_name);
3969 END IF;
3970
3971 EXCEPTION
3972 WHEN packing_error THEN
3973 IF(base_detail_cur%ISOPEN) THEN
3974 CLOSE base_detail_cur;
3975 END IF;
3976 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3977 IF l_debug_on THEN
3978 WSH_DEBUG_SV.logmsg(l_module_name,'packing_error exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3979 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:packing_error');
3980 END IF;
3981
3982 WHEN Others THEN
3983 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3984 IF l_debug_on THEN
3985 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
3986 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3987 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3988 END IF;
3989
3990 END Add_To_Update_Table;
3991
3992
3993 PROCEDURE Process_Cancel(
3994 p_delivery_id IN NUMBER,
3995 x_return_status OUT NOCOPY VARCHAR2) IS
3996
3997 CURSOR del_details_cur IS
3998 SELECT wdd.delivery_detail_id,
3999 wdd.source_line_id,
4000 wdd.source_code,
4001 wdd.container_flag,
4002 wdd.requested_quantity_uom
4003 FROM wsh_delivery_details wdd,
4004 wsh_delivery_assignments_v wda
4005 WHERE wdd.delivery_detail_id = wda.delivery_detail_id
4006 AND wda.delivery_id = p_delivery_id;
4007
4008 l_del_det_int_rec del_det_int_cur%ROWTYPE;
4009 l_return_status VARCHAR2(30);
4010
4011 --
4012 l_debug_on BOOLEAN;
4013 --
4014 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PROCESS_CANCEL';
4015 --
4016 BEGIN
4017 --
4018 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4019 --
4020 IF l_debug_on IS NULL
4021 THEN
4022 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4023 END IF;
4024 --
4025 IF l_debug_on THEN
4026 wsh_debug_sv.push(l_module_name, 'Process_Cancel');
4027 wsh_debug_sv.log (l_module_name, 'Delivery Id', p_delivery_id);
4028 END IF;
4029
4030 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4031
4032 -- select the delivery lines
4033 -- Add to the global update table
4034
4035 FOR del_details_rec IN del_details_cur LOOP
4036
4037 l_del_det_int_rec.delivery_detail_id := del_details_rec.delivery_detail_id;
4038 l_del_det_int_rec.source_line_id := del_details_rec.source_line_id;
4039 l_del_det_int_rec.source_code := del_details_rec.source_code;
4040 l_del_det_int_rec.container_flag := del_details_rec.container_flag;
4041 l_del_det_int_rec.requested_quantity_uom := del_details_rec.requested_quantity_uom;
4042 IF l_debug_on THEN
4043 wsh_debug_sv.log (l_module_name, 'For cancel, delivery detail id',l_del_det_int_rec.delivery_detail_id);
4044 END IF;
4045
4046 Add_To_Update_Table(
4047 l_del_det_int_rec,
4048 'CANCEL',
4049 p_delivery_id,
4050 l_return_status);
4051
4052 IF l_debug_on THEN
4053 wsh_debug_sv.log (l_module_name, 'return status from add_to_update_tbl', l_return_status);
4054 END IF;
4055
4056 END LOOP; -- for del_details_rec
4057
4058
4059 -- call update_delivery_details
4060 Update_Delivery_Details(
4061 p_source_code => 'WSH',
4062 p_action_code => 'CANCEL',
4063 x_return_status => l_return_status
4064 );
4065
4066 IF l_debug_on THEN
4067 wsh_debug_sv.log (l_module_name, 'Update_Delivery_Details l_return_status',l_return_status);
4068 END IF;
4069
4070 IF l_debug_on THEN
4071 wsh_debug_sv.pop(l_module_name);
4072 END IF;
4073
4074 EXCEPTION
4075 WHEN Others THEN
4076 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4077 IF l_debug_on THEN
4078 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
4079 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4080 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4081 END IF;
4082
4083 END Process_Cancel;
4084
4085 PROCEDURE Lock_Delivery_And_Details(
4086 p_delivery_id IN NUMBER,
4087 x_return_status OUT NOCOPY VARCHAR2) IS
4088
4089 l_dummy_id NUMBER;
4090
4091 CURSOR lock_delivery_details IS
4092 SELECT wdd.delivery_detail_id
4093 FROM wsh_delivery_details wdd, wsh_delivery_assignments_v wda
4094 WHERE wdd.delivery_detail_id = wda.delivery_detail_id
4095 AND wda.delivery_id = p_delivery_id
4096 FOR UPDATE NOWAIT;
4097
4098 det_ids lock_delivery_details%ROWTYPE;
4099 RECORD_LOCKED EXCEPTION;
4100 PRAGMA EXCEPTION_INIT(RECORD_LOCKED, -54);
4101
4102 --
4103 l_debug_on BOOLEAN;
4104 --
4105 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOCK_DELIVERY_AND_DETAILS';
4106 --
4107 BEGIN
4108 --
4109 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4110 --
4111 IF l_debug_on IS NULL
4112 THEN
4113 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4114 END IF;
4115 --
4116 IF l_debug_on THEN
4117 wsh_debug_sv.push(l_module_name, 'Lock_Delivery_And_Details');
4118 wsh_debug_sv.log (l_module_name, 'Delivery Id', p_delivery_id);
4119 END IF;
4120
4121 x_return_status := wsh_util_core.g_ret_sts_success;
4122
4123 IF l_debug_on THEN
4124 wsh_debug_sv.logmsg(l_module_name, 'Locking the Delivery');
4125 END IF;
4126 SELECT delivery_id
4127 INTO l_dummy_id
4128 FROM wsh_new_deliveries
4129 WHERE delivery_id = p_delivery_id
4130 FOR UPDATE NOWAIT;
4131
4132 IF l_debug_on THEN
4133 wsh_debug_sv.logmsg(l_module_name, 'Locking the delivery details');
4134 END IF;
4135 OPEN lock_delivery_details;
4136 FETCH lock_delivery_details INTO det_ids;
4137 IF lock_delivery_details%NOTFOUND THEN
4138 IF l_debug_on THEN
4139 wsh_debug_sv.logmsg(l_module_name, 'No details found');
4140 END IF;
4141
4142 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4143 CLOSE lock_delivery_details;
4144 END IF;
4145 IF (lock_delivery_details%ISOPEN) THEN
4146 CLOSE lock_delivery_details;
4147 END IF;
4148 IF l_debug_on THEN
4149 wsh_debug_sv.pop(l_module_name);
4150 END IF;
4151
4152 EXCEPTION
4153 WHEN RECORD_LOCKED THEN
4154 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4155
4156 IF l_debug_on THEN
4157 wsh_debug_sv.logmsg(l_module_name, 'Could not obtain lock');
4158 END IF;
4159
4160 FND_MESSAGE.Set_Name('WSH', 'WSH_NO_LOCK');
4161 WSH_UTIL_CORE.add_message (x_return_status,l_module_name);
4162 IF l_debug_on THEN
4163 WSH_DEBUG_SV.logmsg(l_module_name,'RECORD_LOCKED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4164 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:RECORD_LOCKED');
4165 END IF;
4166
4167 WHEN others THEN
4168 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4169 IF l_debug_on THEN
4170 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
4171 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4172 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4173 END IF;
4174 END Lock_Delivery_And_Details;
4175
4176 PROCEDURE log_errors(
4177 p_loc_interface_errors_rec IN WSH_INTERFACE_VALIDATIONS_PKG.interface_errors_rec_type,
4178 p_msg_data IN VARCHAR2 DEFAULT NULL,
4179 p_api_name IN VARCHAR2,
4180 x_return_status OUT NOCOPY VARCHAR2
4181 ) IS
4182 --
4183 l_debug_on BOOLEAN;
4184 --
4185 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOG_ERRORS';
4186 --
4187 BEGIN
4188 --
4189 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4190 --
4191 IF l_debug_on IS NULL
4192 THEN
4193 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4194 END IF;
4195 --
4196 IF l_debug_on THEN
4197 wsh_debug_sv.push(l_module_name, 'log_errors');
4198 wsh_debug_sv.log (l_module_name, 'Interface table name', p_loc_interface_errors_rec.p_interface_table_name);
4199 wsh_debug_sv.log (l_module_name, 'Interface Id', p_loc_interface_errors_rec.p_interface_id);
4200 wsh_debug_sv.log (l_module_name, 'Message Name', p_loc_interface_errors_rec.p_message_name);
4201 END IF;
4202
4203 WSH_INTERFACE_VALIDATIONS_PKG.Log_Interface_Errors(
4204 p_interface_errors_rec => p_loc_interface_errors_rec,
4205 p_msg_data => p_msg_data,
4206 p_api_name => p_api_name,
4207 x_return_status => x_return_status);
4208
4209 IF l_debug_on THEN
4210 wsh_debug_sv.log (l_module_name, 'Return status from Log_Interface_Errors', x_return_status);
4211 wsh_debug_sv.pop(l_module_name);
4212 END IF;
4213 EXCEPTION
4214 WHEN others THEN
4215 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4216 IF l_debug_on THEN
4217 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
4218 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4219 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4220 END IF;
4221
4222 END Log_Errors;
4223
4224 PROCEDURE split_delivery_detail(
4225 p_delivery_detail_id IN NUMBER,
4226 p_qty_to_split IN NUMBER,
4227 x_new_detail_id OUT NOCOPY NUMBER,
4228 x_return_status OUT NOCOPY VARCHAR2
4229 ) IS
4230
4231 l_base_req_qty NUMBER ;
4232 l_qty_to_split NUMBER;
4233 l_return_status VARCHAR2(30);
4234 l_number_of_errors NUMBER := 0;
4235 l_number_of_warnings NUMBER := 0;
4236 --
4237 CURSOR base_detail_qty(l_del_detail_id NUMBER) IS
4238 SELECT requested_quantity
4239 FROM wsh_delivery_details
4240 WHERE delivery_detail_id =l_del_detail_id;
4241 --
4242 l_debug_on BOOLEAN;
4243 --
4244 l_module_name CONSTANT VARCHAR2(100):= 'wsh.plsql.' || g_pkg_name || '.' || 'SPLIT_DELIVERY_DETAIL';
4245 BEGIN
4246 --
4247 l_debug_on := wsh_debug_interface.g_debug;
4248
4249 --
4250 IF l_debug_on IS NULL THEN
4251 l_debug_on := wsh_debug_sv.is_debug_enabled;
4252 END IF;
4253
4254 --
4255 IF l_debug_on THEN
4256 wsh_debug_sv.push(l_module_name, 'split_delivery_detail');
4257 wsh_debug_sv.LOG(l_module_name, 'p_delivery_detail_id',p_delivery_detail_id);
4258 wsh_debug_sv.LOG(l_module_name, 'p_qty_to_split', p_qty_to_split);
4259 END IF;
4260
4261 OPEN base_detail_qty(p_delivery_detail_id);
4262 FETCH base_detail_qty INTO l_base_req_qty;
4263 CLOSE base_detail_qty;
4264
4265 IF l_debug_on THEN
4266 wsh_debug_sv.LOG(l_module_name, 'Base req qty', l_base_req_qty);
4267 END IF;
4268
4269 IF (p_qty_to_split > l_base_req_qty) THEN
4270 -- Quantity has exceeded
4271 -- so exit
4272 NULL;
4273 ELSIF(p_qty_to_split = l_base_req_qty) THEN
4274 NULL;
4275 ELSE
4276 -- call split_delivery_details
4277 IF l_debug_on THEN
4278 wsh_debug_sv.LOG(l_module_name, 'qty to split', p_qty_to_split);
4279 END IF;
4280
4281 IF p_qty_to_split IS NULL THEN
4282 fnd_message.set_name('WSH', 'WSH_REQUIRED_FIELD_NULL');
4283 fnd_message.set_token('FIELD_NAME', 'REQUESTED_QTY');
4284 wsh_util_core.add_message(wsh_util_core.g_ret_sts_error);
4285 RAISE FND_API.G_EXC_ERROR;
4286 END IF;
4287
4288 l_qty_to_split := p_qty_to_split;
4289 wsh_delivery_details_actions.split_delivery_details(
4290 p_from_detail_id => p_delivery_detail_id,
4291 p_req_quantity => l_qty_to_split,
4292 x_new_detail_id => x_new_detail_id,
4293 x_return_status => l_return_status);
4294
4295 IF l_debug_on THEN
4296 wsh_debug_sv.LOG(l_module_name, 'Split_Delivery_Details x_new_detail_id,l_return_status',
4297 x_new_detail_id || ',' || l_return_status);
4298 END IF;
4299
4300 wsh_util_core.api_post_call(
4301 p_return_status =>l_return_status,
4302 x_num_warnings =>l_number_of_warnings,
4303 x_num_errors =>l_number_of_errors);
4304 END IF;
4305
4306 IF l_number_of_warnings > 0 THEN
4307 IF l_debug_on THEN
4308 wsh_debug_sv.logmsg (l_module_name,'Number of warnings', l_number_of_warnings);
4309 END IF;
4310 RAISE WSH_UTIL_CORE.G_EXC_WARNING;
4311 END IF;
4312
4313 x_return_status := wsh_util_core.g_ret_sts_success;
4314
4315 IF l_debug_on THEN
4316 wsh_debug_sv.pop(l_module_name);
4317 END IF;
4318 EXCEPTION
4319 WHEN fnd_api.g_exc_error THEN
4320 x_return_status := fnd_api.g_ret_sts_error;
4321 --
4322 IF l_debug_on THEN
4323 wsh_debug_sv.logmsg(l_module_name,
4324 'FND_API.G_EXC_ERROR exception has occured.',
4325 wsh_debug_sv.c_excep_level);
4326 wsh_debug_sv.pop(l_module_name, 'EXCEPTION:FND_API.G_EXC_ERROR');
4327 END IF;
4328 --
4329 WHEN fnd_api.g_exc_unexpected_error THEN
4330 x_return_status := fnd_api.g_ret_sts_unexp_error;
4331 --
4332 IF l_debug_on THEN
4333 wsh_debug_sv.logmsg(l_module_name,
4334 'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',
4335 wsh_debug_sv.c_excep_level);
4336 wsh_debug_sv.pop(l_module_name,
4337 'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
4338 END IF;
4339 --
4340 WHEN wsh_util_core.g_exc_warning THEN
4341 x_return_status := wsh_util_core.g_ret_sts_warning;
4342 --
4343 IF l_debug_on THEN
4344 wsh_debug_sv.logmsg(l_module_name,
4345 'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',
4346 wsh_debug_sv.c_excep_level);
4347 wsh_debug_sv.pop(l_module_name,
4348 'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
4349 END IF;
4350 WHEN OTHERS THEN
4351 x_return_status := wsh_util_core.g_ret_sts_unexp_error;
4352 wsh_util_core.default_handler('WSH_INTERFACE_COMMON_ACTIONS.split_delivery_detail');
4353 --
4354 IF l_debug_on THEN
4355 wsh_debug_sv.logmsg(l_module_name,
4356 'Unexpected error has occured. Oracle error message is '
4357 || SQLERRM,
4358 wsh_debug_sv.c_unexpec_err_level);
4359 wsh_debug_sv.pop(l_module_name, 'EXCEPTION:OTHERS');
4360 END IF;
4361 END split_delivery_detail;
4362
4363
4364 PROCEDURE add_to_serial_table(
4365 p_serial_range_tab IN WSH_GLBL_VAR_STRCT_GRP.ddSerialRangeTabType)
4366 IS
4367 l_index NUMBER;
4368 l_g_count NUMBER;
4369 BEGIN
4370 l_index := p_serial_range_tab.first;
4371 while l_index is not null loop
4372 l_g_count := g_serial_range_tab.count;
4373 g_serial_range_tab(l_g_count + 1).delivery_detail_id := p_serial_range_tab(l_index).delivery_detail_id;
4374 g_serial_range_tab(l_g_count + 1).from_serial_number := p_serial_range_tab(l_index).from_serial_number;
4375 g_serial_range_tab(l_g_count + 1).to_serial_number := p_serial_range_tab(l_index).to_serial_number;
4376 g_serial_range_tab(l_g_count + 1).quantity := p_serial_range_tab(l_index).quantity;
4377 l_index := p_serial_range_tab.next(l_index);
4378 end loop;
4379 EXCEPTION
4380 WHEN OTHERS THEN
4381 raise fnd_api.g_exc_error;
4382 END add_to_serial_table;
4383
4384 END WSH_INTERFACE_COMMON_ACTIONS;