[Home] [Help]
PACKAGE BODY: APPS.WSH_INTERFACE_COMMON_ACTIONS
Source
1 PACKAGE BODY WSH_INTERFACE_COMMON_ACTIONS as
2 /* $Header: WSHINCAB.pls 120.9 2011/01/27 12:39:43 brana ship $ */
3
4 -- this cursor will be used by Process_Non_Splits and Process_Splits procedures
5
6 -- TPW - Distributed changes
7 CURSOR del_det_int_cur(l_header_number NUMBER, l_detail_id NUMBER, l_dlvy_interface_id NUMBER) IS
8 SELECT
9 wddi.FREIGHT_CLASS_CAT_CODE,
10 wddi.HAZARD_CLASS_CODE,
11 wddi.INTMED_SHIP_TO_LOCATION_CODE,
12 wddi.ITEM_NUMBER,
13 wddi.LOCATOR_CODE,
14 wddi.MASTER_CONTAINER_ITEM_NUMBER,
15 wddi.ORGANIZATION_CODE,
16 wddi.SHIP_FROM_LOCATION_CODE,
17 wddi.SHIP_TO_LOCATION_CODE,
18 wddi.PROJECT_ID,
19 wddi.SEAL_CODE,
20 wddi.SHIP_TO_SITE_USE_ID,
21 wddi.SHIPPING_INSTRUCTIONS,
22 wddi.SOURCE_LINE_NUMBER,
23 wddi.TO_SERIAL_NUMBER,
24 wddi.TRACKING_NUMBER,
25 wddi.UNIT_NUMBER,
26 wddi.FILL_PERCENT,
27 wddi.FREIGHT_CLASS_CAT_ID,
28 wddi.INSPECTION_FLAG,
29 wddi.LPN_CONTENT_ID,
30 wddi.LPN_ID,
31 wddi.MASTER_SERIAL_NUMBER,
32 wddi.MAXIMUM_LOAD_WEIGHT,
33 wddi.MAXIMUM_VOLUME,
34 wddi.MINIMUM_FILL_PERCENT,
35 wddi.UNIT_PRICE,
36 wddi.COMMODITY_CODE_CAT_ID,
37 wddi.TP_ATTRIBUTE9,
38 wddi.TP_ATTRIBUTE10,
39 wddi.TP_ATTRIBUTE11,
40 wddi.TP_ATTRIBUTE12,
41 wddi.TP_ATTRIBUTE13,
42 wddi.TP_ATTRIBUTE14,
43 wddi.TP_ATTRIBUTE15,
44 wddi.ATTRIBUTE_CATEGORY,
45 wddi.ATTRIBUTE1,
46 wddi.ATTRIBUTE2,
47 wddi.ATTRIBUTE3,
48 wddi.ATTRIBUTE4,
49 wddi.ATTRIBUTE5,
50 wddi.ATTRIBUTE6,
51 wddi.ATTRIBUTE7,
52 wddi.ATTRIBUTE8,
53 wddi.ATTRIBUTE9,
54 wddi.ATTRIBUTE10,
55 wddi.ATTRIBUTE11,
56 wddi.ATTRIBUTE12,
57 wddi.ATTRIBUTE13,
58 wddi.ATTRIBUTE14,
59 wddi.ATTRIBUTE15,
60 wddi.CREATION_DATE,
61 wddi.CREATED_BY,
62 wddi.LAST_UPDATE_DATE,
63 wddi.LAST_UPDATED_BY,
64 wddi.LAST_UPDATE_LOGIN,
65 wddi.PROGRAM_APPLICATION_ID,
66 wddi.PROGRAM_ID,
67 wddi.PROGRAM_UPDATE_DATE,
68 wddi.REQUEST_ID,
69 wddi.INTERFACE_ACTION_CODE,
70 wddi.LOCK_FLAG,
71 wddi.PROCESS_FLAG,
72 wddi.PROCESS_MODE,
73 wddi.DELETE_FLAG,
74 wddi.PROCESS_STATUS_FLAG,
75 wddi.SOURCE_HEADER_NUMBER,
76 wddi.SOURCE_HEADER_TYPE_ID,
77 wddi.SOURCE_HEADER_TYPE_NAME,
78 wddi.CUST_PO_NUMBER,
79 wddi.SHIP_SET_ID,
80 wddi.ARRIVAL_SET_ID,
81 wddi.TOP_MODEL_LINE_ID,
82 wddi.ATO_LINE_ID,
83 wddi.SHIP_MODEL_COMPLETE_FLAG,
84 wddi.HAZARD_CLASS_ID,
85 wddi.CLASSIFICATION,
86 wddi.ORGANIZATION_ID,
87 wddi.SRC_REQUESTED_QUANTITY,
88 wddi.SRC_REQUESTED_QUANTITY_UOM,
89 wddi.QUALITY_CONTROL_QUANTITY,
90 wddi.CYCLE_COUNT_QUANTITY,
91 wddi.MOVE_ORDER_LINE_ID,
92 wddi.LOCATOR_ID,
93 wddi.MVT_STAT_STATUS,
94 wddi.TRANSACTION_TEMP_ID,
95 wddi.PREFERRED_GRADE,
96 wddi.SRC_REQUESTED_QUANTITY2,
97 wddi.SRC_REQUESTED_QUANTITY_UOM2,
98 wddi.REQUESTED_QUANTITY2,
99 wddi.SHIPPED_QUANTITY2,
100 wddi.DELIVERED_QUANTITY2,
101 wddi.CANCELLED_QUANTITY2,
102 wddi.QUALITY_CONTROL_QUANTITY2,
103 wddi.CYCLE_COUNT_QUANTITY2,
104 wddi.REQUESTED_QUANTITY_UOM2,
105 -- HW OPMCONV - No need for sublot_number
106 --wddi.SUBLOT_NUMBER,
107 wddi.SPLIT_FROM_DELIVERY_DETAIL_ID,
108 wddi.CARRIER_CODE,
109 wddi.COMMODITY_CODE_CAT_CODE,
110 wddi.CUSTOMER_NUMBER,
111 wddi.CUSTOMER_ITEM_NUMBER,
112 wddi.DELIVER_TO_LOCATION_CODE,
113 wddi.CUSTOMER_PRODUCTION_LINE,
114 wddi.DELIVER_TO_SITE_USE_ID,
115 wddi.MOVEMENT_ID,
116 wddi.ORG_ID,
117 wddi.ORIGINAL_SUBINVENTORY,
118 wddi.PACKING_INSTRUCTIONS,
119 wddi.PICKED_QUANTITY,
120 wddi.PICKED_QUANTITY2,
121 wddi.DELIVERY_DETAIL_INTERFACE_ID,
122 wddi.DELIVERY_DETAIL_ID,
123 wddi.SOURCE_CODE,
124 wddi.SOURCE_HEADER_ID,
125 wddi.SOURCE_LINE_ID,
126 wddi.CUSTOMER_ID,
127 wddi.SOLD_TO_CONTACT_ID,
128 wddi.INVENTORY_ITEM_ID,
129 wddi.ITEM_DESCRIPTION,
130 wddi.COUNTRY_OF_ORIGIN,
131 wddi.SHIP_FROM_LOCATION_ID,
132 wddi.SHIP_TO_LOCATION_ID,
133 wddi.SHIP_TO_CONTACT_ID,
134 wddi.DELIVER_TO_LOCATION_ID,
135 wddi.DELIVER_TO_CONTACT_ID,
136 wddi.INTMED_SHIP_TO_LOCATION_ID,
137 wddi.INTMED_SHIP_TO_CONTACT_ID,
138 wddi.SHIP_TOLERANCE_ABOVE,
139 wddi.SHIP_TOLERANCE_BELOW,
140 wddi.REQUESTED_QUANTITY,
141 wddi.CANCELLED_QUANTITY,
142 wddi.SHIPPED_QUANTITY,
143 wddi.DELIVERED_QUANTITY,
144 wddi.REQUESTED_QUANTITY_UOM,
145 wddi.SHIPPING_QUANTITY_UOM,
146 wddi.SUBINVENTORY,
147 wddi.REVISION,
148 wddi.LOT_NUMBER,
149 wddi.CUSTOMER_REQUESTED_LOT_FLAG,
150 wddi.SERIAL_NUMBER,
151 wddi.DATE_REQUESTED,
152 wddi.DATE_SCHEDULED,
153 wddi.MASTER_CONTAINER_ITEM_ID,
154 wddi.DETAIL_CONTAINER_ITEM_ID,
155 wddi.LOAD_SEQ_NUMBER,
156 wddi.SHIP_METHOD_CODE,
157 wddi.CARRIER_ID,
158 wddi.FREIGHT_TERMS_CODE,
159 wddi.SHIPMENT_PRIORITY_CODE,
160 wddi.FOB_CODE,
161 wddi.CUSTOMER_ITEM_ID,
162 wddi.DEP_PLAN_REQUIRED_FLAG,
163 wddi.CUSTOMER_PROD_SEQ,
164 wddi.CUSTOMER_DOCK_CODE,
165 wddi.GROSS_WEIGHT,
166 wddi.NET_WEIGHT,
167 wddi.WEIGHT_UOM_CODE,
168 wddi.VOLUME,
169 wddi.VOLUME_UOM_CODE,
170 wddi.TP_ATTRIBUTE_CATEGORY,
171 wddi.TP_ATTRIBUTE1,
172 wddi.TP_ATTRIBUTE2,
173 wddi.TP_ATTRIBUTE3,
174 wddi.TP_ATTRIBUTE4,
175 wddi.TP_ATTRIBUTE5,
176 wddi.TP_ATTRIBUTE6,
177 wddi.TP_ATTRIBUTE7,
178 wddi.TP_ATTRIBUTE8,
179 wddi.DETAIL_CONTAINER_ITEM_CODE,
180 wddi.TASK_ID,
181 wddi.CUSTOMER_JOB,
182 wddi.CONTAINER_FLAG,
183 wddi.CONTAINER_NAME,
184 wddi.CONTAINER_TYPE_CODE,
185 wddi.CURRENCY_CODE,
186 wddi.CUST_MODEL_SERIAL_NUMBER,
187 -- J: W/V Changes
188 wddi.filled_volume,
189 wddi.wv_frozen_flag,
190 --Bug 3458160
191 wddi.LINE_DIRECTION,
192 wddi.REQUEST_DATE_TYPE_CODE,
193 wddi.EARLIEST_PICKUP_DATE ,
194 wddi.LATEST_PICKUP_DATE ,
195 wddi.EARLIEST_DROPOFF_DATE ,
196 wddi.LATEST_DROPOFF_DATE
197 FROM wsh_del_details_interface wddi,
198 wsh_del_assgn_interface wdai
199 WHERE wddi.delivery_detail_id= nvl(l_detail_id, wddi.delivery_detail_id)
200 -- TPW - Distributed changes
201 AND nvl(wddi.source_header_number,'-99') = nvl(l_header_number, nvl(wddi.source_header_number,'-99'))
202 AND wddi.delivery_detail_interface_id = wdai.delivery_detail_interface_id
203 AND wdai.delivery_interface_id= l_dlvy_interface_id
204 AND WDDI.INTERFACE_ACTION_CODE = '94X_INBOUND'
205 AND WDAI.INTERFACE_ACTION_CODE = '94X_INBOUND'
206 ORDER BY wddi.delivery_detail_id, wddi.source_line_id;
207
208
209 -- forward declaration
210 --
211 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_INTERFACE_COMMON_ACTIONS';
212 --
213 PROCEDURE Add_To_Update_Table(
214 p_del_det_int_rec IN del_det_int_cur%ROWTYPE,
215 p_update_mode IN VARCHAR2 DEFAULT 'UPDATE',
216 p_delivery_id IN NUMBER,
217 x_return_status OUT NOCOPY VARCHAR2);
218
219
220 PROCEDURE log_errors(
221 p_loc_interface_errors_rec IN WSH_INTERFACE_VALIDATIONS_PKG.interface_errors_rec_type,
222 p_msg_data IN VARCHAR2 DEFAULT NULL,
223 p_api_name IN VARCHAR2,
224 x_return_status OUT NOCOPY VARCHAR2);
225
226 PROCEDURE split_delivery_detail(
227 p_delivery_detail_id IN NUMBER,
228 p_qty_to_split IN NUMBER,
229 x_new_detail_id OUT NOCOPY NUMBER,
230 x_return_status OUT NOCOPY VARCHAR2
231 );
232
233 PROCEDURE add_to_serial_table(
234 p_serial_range_tab IN WSH_GLBL_VAR_STRCT_GRP.ddSerialRangeTabType);
235
236
237 PROCEDURE Update_Delivery_Details(
238 p_source_code IN VARCHAR2 DEFAULT 'OE',
239 p_delivery_interface_id IN NUMBER DEFAULT NULL,
240 p_action_code IN VARCHAR2,
241 x_return_status OUT NOCOPY VARCHAR2);
242
243 -- TPW - Distributed changes
244 PROCEDURE Lock_Delivery_Details(
245 p_delivery_interface_id IN NUMBER,
246 x_return_status OUT NOCOPY VARCHAR2);
247
248 /*
249 -----------------------------------------------------------------------------
250 PROCEDURE : Update_Contnr_Int_Assignments
251 PARAMETERS : p_parent_delivery_detail_id
252 p_parent_detail_interface_id
253 x_return_status - return status of API
254 DESCRIPTION :
255 - This procedure is called in the Inbound Map, to relate the SHIPITEM records
256 with the SHIPUNIT/CONTAINER records through the parent_detail_interface_id.
257 - This procedure updates the wsh_del_assgn_interface table.
258 - This takes the parent_delivery_detail_id and parent_detail_interface_id.
259 - For those records which have parent_delivery_detail_id is equal to the
260 parameter value, the parent_detail_interface_id is updated with the give
261 value.
262
263
264 ------------------------------------------------------------------------------
265 */
266
267
268 PROCEDURE Update_Contnr_Int_Assignments(
269 p_parent_delivery_detail_id IN NUMBER,
270 p_parent_detail_interface_id IN NUMBER,
271 x_return_status OUT NOCOPY VARCHAR2) IS
272
273 --
274 l_debug_on BOOLEAN;
275 --
276 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_CONTNR_INT_ASSIGNMENTS';
277 --
278 BEGIN
279 --
280 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
281 --
282 IF l_debug_on IS NULL
283 THEN
284 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
285 END IF;
286 --
287 IF l_debug_on THEN
288 wsh_debug_sv.push(l_module_name, 'Update_Contnr_Int_Assignments');
289 wsh_debug_sv.log (l_module_name,'parent_delivery_detail_id ',p_parent_delivery_detail_id);
290 wsh_debug_sv.log (l_module_name,'parent_detail_interface_id ',p_parent_detail_interface_id);
291 END IF;
292
293 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
294
295 UPDATE wsh_del_assgn_interface
296 SET parent_detail_interface_id = p_parent_detail_interface_id
297 WHERE parent_delivery_detail_id = p_parent_delivery_detail_id;
298
299 IF (SQL%NOTFOUND) THEN
300 NULL;
301 -- need to check with anil
302 END IF;
303
304 IF l_debug_on THEN
305 wsh_debug_sv.pop(l_module_name);
306 END IF;
307
308 EXCEPTION
309
310 WHEN Others THEN
311
312 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
313 IF l_debug_on THEN
314 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
315 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
316 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
317 END IF;
318 END Update_Contnr_Int_Assignments;
319
320 /*
321 -----------------------------------------------------------------------------
322 PROCEDURE : Process_Interfaced_Del_Details
323 PARAMETERS : p_del_detail_interface_id
324 p_del_detail_id
325 p_action_code - Action code 'CREATE' or 'UPDATE'
326 x_del_detail_id - Delivery_Detail_ID of the detail created
327 - using Create_Shipment_Lines api
328 x_return_status - return status of API
329 DESCRIPTION :
330 - This procedure is used to process the delivery details in the wsh_del_details_interface
331 table.
332 - If the action is CREATE, then we take the interface record columns and call
333 the 'Create_Shipment_Lines' api.
334 - If the action is UPDATE, then we do the following:
335 -- Do count(*) of the records for the given delivery_detail_id (p_del_detail_id)
336 -- If the count=1, then we take the interface record columns and call
337 Update_Shipping_Attributes
338 -- If the count>1, then we have multiple delivery detail records in the interface
339 table for one record in the base table.
340 -- Base records need to be split before the update. So we split the base
341 table record based on the quantities in the interface table records
342 -- After every split, we call Update_Shipping_Attributes to update the
343 newly created base record with the corresponding interface record values
344 -- If the interface delivery detail is packed, then we do the following:
345 -- create container instance in base tables using the container inv.item
346 -- pack the base records into the newly created container instances.
347 ------------------------------------------------------------------------------
348 */
349
350
351 PROCEDURE Process_Interfaced_Del_Details(
352 p_delivery_interface_id IN NUMBER,
353 p_delivery_id IN NUMBER,
354 p_new_delivery_id IN NUMBER,
355 p_action_code IN VARCHAR2,
356 x_return_status OUT NOCOPY VARCHAR2) IS
357
358 -- procedure specific variables
359 -- TPW - Distributed changes - Starts
360 l_detail_tab WSH_UTIL_CORE.id_tab_type;
361 l_organization_tab WSH_UTIL_CORE.id_tab_type;
362 l_wf_rs VARCHAR2(1);
363 l_dbi_rs VARCHAR2(1);
364 -- TPW - Distributed changes - Ends
365 l_return_status VARCHAR2(30);
366
367 -- exceptions
368 invalid_action_code exception;
369 invalid_input exception;
370 -- TPW - Distributed changes
371 others_dbi exception;
372
373 --
374 l_debug_on BOOLEAN;
375 --
376 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PROCESS_INTERFACED_DEL_DETAILS';
377 --
378 BEGIN
379 --
380 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
381 --
382 IF l_debug_on IS NULL
383 THEN
384 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
385 END IF;
386 --
387 IF l_debug_on THEN
388 wsh_debug_sv.push(l_module_name, 'Process_Interfaced_Del_Details');
389 wsh_debug_sv.log (l_module_name, 'Delivery Interface Id',p_delivery_interface_id);
390 wsh_debug_sv.log (l_module_name,'Delivery Id', p_delivery_id);
391 wsh_debug_sv.log (l_module_name,'New Delivery Id', p_new_delivery_id);
392 wsh_debug_sv.log (l_module_name,'Action Code ',p_action_code);
393 END IF;
394
395 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
396
397 IF(p_delivery_interface_id IS NULL) THEN
398 raise invalid_input;
399 END IF;
400
401 IF (p_action_code = 'UPDATE' and p_delivery_id IS NULL) THEN
402 raise invalid_input;
403 END IF;
404
405 IF (p_action_code = 'CREATE' and p_new_delivery_id IS NULL) THEN
406 raise invalid_input;
407 END IF;
408
409 Process_Non_Splits(
410 p_delivery_interface_id => p_delivery_interface_id,
411 p_delivery_id => p_delivery_id,
412 p_new_delivery_id => p_new_delivery_id,
413 p_action_code => p_action_code,
414 x_return_status => l_return_status);
415
416 IF l_debug_on THEN
417 wsh_debug_sv.log (l_module_name, 'Return Status from Process_Non_Splits', l_return_status);
418 END IF;
419
420 IF(l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
421 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
422 END IF;
423
424 IF(p_action_code = 'UPDATE') THEN
425 Process_Splits(
426 p_delivery_interface_id => p_delivery_interface_id,
427 p_delivery_id => p_delivery_id,
428 x_return_status => l_return_status);
429 END IF;
430
431 IF l_debug_on THEN
432 wsh_debug_sv.log (l_module_name, 'Return Status from Process Splits', l_return_status);
433 END IF;
434
435 IF(l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
436 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
437 END IF;
438
439 -- TPW - Distributed changes - Start
440 IF (nvl(WSH_INBOUND_SHIP_ADVICE_PKG.G_WAREHOUSE_TYPE, '!') in ( 'TPW', 'TW2')) THEN
441
442 UPDATE wsh_delivery_details
443 SET released_status = 'Y'
444 WHERE delivery_detail_id IN (
445 SELECT delivery_detail_id
446 FROM wsh_delivery_assignments
447 WHERE delivery_id = p_delivery_id)
448 AND released_status IN ('R', 'B', 'X')
449 AND container_flag = 'N'
450 RETURNING delivery_detail_id, organization_id BULK COLLECT INTO l_detail_tab, l_organization_tab; -- Added for TPW - Distributed changes;
451
452 -- TPW - Distributed changes - Starts
453 -- Moved above update statement from api WSH_INBOUND_SHIP_ADVICE_PKG.Process_Ship_Advice,
454 -- as part of TPW - Distribution changes, however missed including call to Raise Business
455 -- Event and DBI API in earlier version
456 --Raise Event : Pick To Pod Workflow
457 IF l_detail_tab.count > 0 THEN -- {
458 FOR i in l_detail_tab.first .. l_detail_tab.last LOOP
459 WSH_WF_STD.Raise_Event(
460 p_entity_type => 'Line',
461 p_entity_id => l_detail_tab(i) ,
462 p_event => 'oracle.apps.wsh.line.gen.staged' ,
463 --p_parameters IN wf_parameter_list_t DEFAULT NULL,
464 p_organization_id => l_organization_tab(i),
465 x_return_status => l_wf_rs ) ;
466 --Error Handling to be done in WSH_WF_STD.Raise_Event itself
467 IF l_debug_on THEN
468 WSH_DEBUG_SV.log(l_module_name,'Current Time is ',SYSDATE);
469 WSH_DEBUG_SV.log(l_module_name,'Delivery Detail Id is ',l_detail_tab(i) );
470 wsh_debug_sv.log(l_module_name,'Return Status After Calling WSH_WF_STD.Raise_Event',l_wf_rs);
471 END IF;
472 END LOOP;
473 END IF; -- }
474 --Done Raise Event: Pick To Pod Workflow
475 --
476 -- DBI Project
477 -- Update of wsh_delivery_details where requested_quantity/released_status
478 -- are changed, call DBI API after the update.
479 -- This API will also check for DBI Installed or not
480 IF l_debug_on THEN
481 WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Detail Count-',l_detail_tab.count);
482 END IF;
483 WSH_INTEGRATION.DBI_Update_Detail_Log(
484 p_delivery_detail_id_tab => l_detail_tab,
485 p_dml_type => 'UPDATE',
486 x_return_status => l_dbi_rs);
487 --
488 IF l_debug_on THEN
489 WSH_DEBUG_SV.log(l_module_name,'Return Status after DBI Call-',l_dbi_rs);
490 END IF;
491 IF l_dbi_rs = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
492 -- just pass this return status to caller API
493 -- this is a pre-defined exception handled in parent EXCEPTIONS block
494 -- x_return_status is set as Unexpected in exceptions handler
495 RAISE others_dbi;
496 END IF;
497 -- treat all other return status as Success
498 -- End of Code for DBI Project
499 --
500 -- TPW - Distributed changes - Ends
501 END IF;
502
503 IF (nvl(WSH_INBOUND_SHIP_ADVICE_PKG.G_WAREHOUSE_TYPE, '!') in ( 'TPW', 'TW2', 'CMS')) THEN
504 update wsh_new_deliveries
505 set status_code ='SA'
506 where delivery_id = p_delivery_id
507 and status_code IN ('OP','SR','SC');
508 END IF;
509 -- TPW - Distributed changes - End
510
511 IF(l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
512 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
513 END IF;
514
515 IF l_debug_on THEN
516 wsh_debug_sv.log (l_module_name, 'Packing Table Count', G_Packing_Detail_Tab.count);
517 END IF;
518 IF(G_Packing_Detail_Tab.count > 0) THEN
519 Pack_Lines(
520 x_return_status => l_return_status);
521
522 IF l_debug_on THEN
523 wsh_debug_sv.log (l_module_name, 'Return Status from Pack Lines', l_return_status);
524 END IF;
525
526 IF(l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
527 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
528 END IF;
529 END IF;
530
531 Update_Delivery_Details(
532 p_delivery_interface_id => p_delivery_interface_id,
533 p_action_code => p_action_code,
534 x_return_status => l_return_status);
535
536 IF l_debug_on THEN
537 wsh_debug_sv.log (l_module_name, 'Return Status from Update Del Details', l_return_status);
538 END IF;
539
540 -- TPW - Distributed changes - Inv. Rsv API Integration Changes
541 -- Identified during Inventory Integration testing
542 -- Handling Return status of API Update_Delivery_Details
543 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
544 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
545 END IF;
546
547 IF l_debug_on THEN
548 wsh_debug_sv.pop(l_module_name);
549 END IF;
550
551 EXCEPTION
552 WHEN invalid_action_code THEN
553 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
554 IF l_debug_on THEN
555 WSH_DEBUG_SV.logmsg(l_module_name,'invalid_action_code exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
556 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_action_code');
557 END IF;
558 WHEN invalid_input THEN
559 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
560 IF l_debug_on THEN
561 WSH_DEBUG_SV.logmsg(l_module_name,'invalid_input exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
562 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_input');
563 END IF;
564 -- TPW - Distributed changes
565 WHEN others_dbi THEN
566 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
567 IF l_debug_on THEN
568 WSH_DEBUG_SV.logmsg(l_module_name,'others_dbi exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
569 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:others_dbi');
570 END IF;
571 WHEN Others THEN
572 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
573 IF l_debug_on THEN
574 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
575 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
576 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
577 END IF;
578 END Process_Interfaced_Del_Details;
579
580
581 PROCEDURE Process_Non_Splits(
582 p_delivery_interface_id IN NUMBER,
583 p_delivery_id IN NUMBER,
584 p_new_delivery_id IN NUMBER,
585 p_action_code IN VARCHAR2,
586 x_return_status OUT NOCOPY VARCHAR2) IS
587 -- local variables
588 l_new_del_detail_id NUMBER;
589 l_new_detail_ids WSH_UTIL_CORE.Id_Tab_Type;
590 l_table_count NUMBER;
591 l_return_status VARCHAR2(30);
592
593 l_del_details_info WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Rec_Type;
594 -- Patchset I: Harmonization Project
595 l_detail_info_tab WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Attr_Tbl_Type;
596 l_api_version NUMBER := 1.0;
597 l_detail_in_rec WSH_GLBL_VAR_STRCT_GRP.detailInRecType;
598 l_detail_out_rec WSH_GLBL_VAR_STRCT_GRP.detailOutRecType;
599 l_index NUMBER;
600 -- End Patchset I: Harmonization Project
601
602
603 l_delivery_id NUMBER;
604 l_delivery_name NUMBER;
605
606 l_cont_inst_exists NUMBER;
607 l_cont_instance_id NUMBER;
608 l_cont_name VARCHAR2(30);
609 l_row_id VARCHAR2(30);
610 l_det_freight_costs NUMBER;
611
612 l_pickable_flag VARCHAR2(1);
613 -- J: W/V Changes
614 l_unit_weight NUMBER;
615 l_unit_volume NUMBER;
616
617 -- public api variables
618 l_init_msg_list VARCHAR2(30) := NULL;
619 l_msg_count NUMBER;
620 l_msg_data VARCHAR2(3000);
621 l_commit VARCHAR2(1);
622 l_validation_level NUMBER;
623
624 -- cursors
625 -- TPW - Distributed changes
626 CURSOR detail_ids_cur IS
627 SELECT count(*),
628 wddi.source_header_number,
629 wdai.delivery_detail_id,
630 decode(wddi.container_flag, 'Y', wdai.parent_delivery_detail_id,null) parent_delivery_detail_id,
631 wddi.container_flag
632 FROM wsh_del_assgn_interface wdai, wsh_del_details_interface wddi
633 WHERE wdai.delivery_interface_id = p_delivery_interface_id
634 AND wdai.delivery_detail_interface_id = wddi.delivery_detail_interface_id
635 AND WDAI.INTERFACE_ACTION_CODE = '94X_INBOUND'
636 AND WDDI.INTERFACE_ACTION_CODE = '94X_INBOUND'
637 GROUP BY wddi.source_header_number,
638 wdai.delivery_detail_id,
639 decode(wddi.container_flag, 'Y', wdai.parent_delivery_detail_id,null),
640 wddi.container_flag
641 HAVING count(*) = 1
642 ORDER BY wddi.container_flag desc,
643 decode(wddi.container_flag, 'Y', wdai.parent_delivery_detail_id,null) desc nulls first;
644
645 CURSOR cont_inst_exists(l_del_det_id NUMBER) IS
646 SELECT count(*)
647 FROM wsh_delivery_details wdd, wsh_delivery_assignments_v wda
648 WHERE wdd.source_line_id = l_del_det_id
649 AND wdd.source_code = 'WSH'
650 AND wdd.container_flag = 'Y'
651 AND wdd.delivery_detail_id = wda.delivery_detail_id
652 AND wda.delivery_id = p_delivery_id; -- check this
653
654 CURSOR c_specific_item_info( p_inventory_item_id NUMBER,
655 p_organization_id NUMBER)
656 IS
657 SELECT decode(mtl_transactions_enabled_flag,'Y','Y','N'),
658 -- J: W/V Changes
659 unit_weight,
660 unit_volume
661 FROM mtl_system_items m
662 WHERE m.inventory_item_id = p_inventory_item_id
663 AND m.organization_id = p_organization_id;
664
665 --Bug fix 3658492.
666 CURSOR c_org_oper_unit(p_organization_id IN NUMBER) IS
667 SELECT to_number(org_information3)
668 FROM hr_organization_information
669 WHERE organization_id = p_organization_id
670 AND org_information_context = 'Accounting Information';
671
672
673 l_dummy VARCHAR2(10);
674
675 del_det_int_rec del_det_int_cur%ROWTYPE;
676
677 --exceptions
678 invalid_input exception;
679 create_lines_failed exception;
680 create_cont_instance_failed exception;
681 add_to_update_failed exception;
682 new_assignment_failed exception;
683 freight_cost_processing_error exception;
684 l_msg_details varchar2(2000);
685 l_loc_interface_error_rec WSH_INTERFACE_VALIDATIONS_PKG.interface_errors_rec_type;
686
687 -- K LPN CONV. rv
688 l_cont_tab wsh_util_core.id_tab_type;
689 l_lpn_unit_weight NUMBER;
690 l_lpn_unit_volume NUMBER;
691 l_lpn_weight_uom_code VARCHAR2(100);
692 l_lpn_volume_uom_code VARCHAR2(100);
693 -- K LPN CONV. rv
694 l_delivery_rec WSH_NEW_DELIVERIES_PVT.Delivery_Rec_Type;
695 l_shipping_param_info WSH_SHIPPING_PARAMS_PVT.Parameter_Rec_Typ;
696
697 -- TPW - Distributed changes
698 l_new_split_detail_id number;
699 l_req_qty number;
700 l_pending_req_qty number;
701 l_pending_shp_qty number;
702 l_curr_index number;
703 l_detail_id_tab wsh_util_core.id_tab_type;
704 l_detail_qty_tab wsh_util_core.id_tab_type;
705 l_number_of_errors NUMBER := 0;
706 l_number_of_warnings NUMBER := 0;
707 l_serial_number_control NUMBER;
708 -- TPW - Distributed changes
709
710 --
711 l_debug_on BOOLEAN;
712 --
713 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PROCESS_NON_SPLITS';
714 --
715 BEGIN
716 --
717 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
718 --
719 IF l_debug_on IS NULL
720 THEN
721 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
722 END IF;
723 --
724 IF l_debug_on THEN
725 wsh_debug_sv.push(l_module_name, 'Process_Non_Splits');
726 wsh_debug_sv.log (l_module_name,'Delivery Interface Id',p_delivery_interface_id);
727 wsh_debug_sv.log (l_module_name,'Delivery Id', p_delivery_id);
728 wsh_debug_sv.log (l_module_name, 'New Delivery Id', p_new_delivery_id);
729 wsh_debug_sv.log (l_module_name,'Action Code ',p_action_code);
730 END IF;
731
732 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
733
734 IF(p_delivery_interface_id IS NULL) THEN
735 raise invalid_input;
736 END IF;
737
738 IF(p_action_code = 'UPDATE') THEN
739 IF (p_delivery_id IS NOT NULL) THEN
740 l_delivery_id := p_delivery_id;
741 ELSE
742 raise invalid_input;
743 END IF;
744 ELSIF(p_action_code = 'CREATE' AND p_new_delivery_id IS NULL) THEN
745 raise invalid_input;
746
747 END IF;
748
749 l_new_detail_ids.delete;
750
751 IF l_debug_on THEN
752 wsh_debug_sv.logmsg(l_module_name, 'Before Looping thru details');
753 END IF;
754
755 FOR det_id IN detail_ids_cur LOOP
756 /* Bug fix 2451920
757 Have to clear out the variable l_cont_name
758 After the first loop, the in-out parameter(x_cont_name) of create_container_instance
759 populates the variable l_cont_name
760 Since we don't want to use the created container name, we have to clear this variable */
761
762 l_cont_name := NULL;
763
764
765 IF l_debug_on THEN
766 wsh_debug_sv.log (l_module_name, 'Delivery Detail ID',det_id.delivery_detail_id);
767 END IF;
768
769 -- TPW - Distributed changes
770 OPEN del_det_int_cur(det_id.source_header_number, det_id.delivery_detail_id, p_delivery_interface_id);
771 FETCH del_det_int_cur INTO del_det_int_rec;
772 CLOSE del_det_int_cur;
773
774 IF l_debug_on THEN
775 wsh_debug_sv.log(l_module_name,'Delivery Detail Interf Id',del_det_int_rec.delivery_detail_interface_id);
776 END IF;
777
778 IF(p_action_code = 'CREATE') THEN --{
779
780 /*
781 Set l_del_details_info.released_status = X
782 Because picking at TPW is not supported for patchset H
783 */
784
785 l_del_details_info.released_status := 'X';
786
787 /*
788 Set l_del_details_info.oe_interfaced_flag = X
789 Because lines should not be interfaced with OM at the TPW
790 */
791
792 l_del_details_info.oe_interfaced_flag := 'X';
793
794
795 /* For 940 Inbound, the next step is to create delivery details
796 through the call to wsh_interface_pub.create_shipment_lines.
797 This public api expects a source_header_id for the delivery details.
798 We use the newly created delivery id as the source_header_id
799 for the delivery details that are to be created */
800
801 l_del_details_info.source_header_id := nvl(del_det_int_rec.source_header_id, p_new_delivery_id);
802
803 l_del_details_info.source_code := del_det_int_rec.source_code;
804 l_del_details_info.source_line_id := del_det_int_rec.source_line_id;
805 l_del_details_info.org_id := del_det_int_rec.org_id;
806
807 /* create_shipment_line api needs source line number
808 we use the supplier instance's delivery detail id as the source line number
809 Inbound mapping would have populated source_line_id column with the
810 supplier instance's delivery detail id */
811
812 l_del_details_info.source_line_number := nvl(del_det_int_rec.source_line_number, del_det_int_rec.source_line_id);
813
814
815 l_del_details_info.customer_id := del_det_int_rec.customer_id;
816 l_del_details_info.sold_to_contact_id := del_det_int_rec.sold_to_contact_id;
817 l_del_details_info.inventory_item_id := del_det_int_rec.inventory_item_id;
818 l_del_details_info.item_description := del_det_int_rec.item_description;
819 l_del_details_info.hazard_class_id := del_det_int_rec.hazard_class_id;
820 l_del_details_info.country_of_origin := del_det_int_rec.country_of_origin;
821 l_del_details_info.classification := del_det_int_rec.classification;
822 l_del_details_info.ship_from_location_id := del_det_int_rec.ship_from_location_id;
823 l_del_details_info.ship_to_location_id := del_det_int_rec.ship_to_location_id;
824 l_del_details_info.ship_to_contact_id := del_det_int_rec.ship_to_contact_id;
825 l_del_details_info.ship_to_site_use_id := del_det_int_rec.ship_to_site_use_id;
826 l_del_details_info.deliver_to_location_id := del_det_int_rec.deliver_to_location_id;
827 l_del_details_info.deliver_to_contact_id := del_det_int_rec.deliver_to_contact_id;
828 l_del_details_info.deliver_to_site_use_id := del_det_int_rec.deliver_to_site_use_id;
829 l_del_details_info.intmed_ship_to_location_id := del_det_int_rec.intmed_ship_to_location_id;
830 l_del_details_info.intmed_ship_to_contact_id := del_det_int_rec.intmed_ship_to_contact_id;
831 -- l_del_details_info.hold_code := del_det_int_rec.hold_code;
832 l_del_details_info.ship_tolerance_above := del_det_int_rec.ship_tolerance_above;
833 l_del_details_info.ship_tolerance_below := del_det_int_rec.ship_tolerance_below;
834 l_del_details_info.requested_quantity := del_det_int_rec.requested_quantity;
835 l_del_details_info.shipped_quantity := del_det_int_rec.shipped_quantity;
836 l_del_details_info.delivered_quantity := del_det_int_rec.delivered_quantity;
837 l_del_details_info.requested_quantity_uom := del_det_int_rec.requested_quantity_uom;
838 l_del_details_info.subinventory := del_det_int_rec.subinventory;
839 l_del_details_info.revision := del_det_int_rec.revision;
840 l_del_details_info.lot_number := del_det_int_rec.lot_number;
841 l_del_details_info.customer_requested_lot_flag := del_det_int_rec.customer_requested_lot_flag;
842 l_del_details_info.serial_number := del_det_int_rec.serial_number;
843 l_del_details_info.locator_id := del_det_int_rec.locator_id;
844 l_del_details_info.date_requested := del_det_int_rec.date_requested;
845 l_del_details_info.date_scheduled := del_det_int_rec.date_scheduled;
846 l_del_details_info.master_container_item_id := del_det_int_rec.master_container_item_id;
847 l_del_details_info.detail_container_item_id := del_det_int_rec.detail_container_item_id;
848 l_del_details_info.load_seq_number := del_det_int_rec.load_seq_number;
849 l_del_details_info.ship_method_code := del_det_int_rec.ship_method_code;
850 l_del_details_info.carrier_id := del_det_int_rec.carrier_id;
851 l_del_details_info.freight_terms_code := del_det_int_rec.freight_terms_code;
852 l_del_details_info.shipment_priority_code := del_det_int_rec.shipment_priority_code;
853 l_del_details_info.fob_code := del_det_int_rec.fob_code;
854 l_del_details_info.customer_item_id := del_det_int_rec.customer_item_id;
855 l_del_details_info.dep_plan_required_flag := del_det_int_rec.dep_plan_required_flag;
856 l_del_details_info.customer_prod_seq := del_det_int_rec.customer_prod_seq;
857 l_del_details_info.customer_dock_code := del_det_int_rec.customer_dock_code;
858 l_del_details_info.cust_model_serial_number := del_det_int_rec.cust_model_serial_number;
859 l_del_details_info.customer_job := del_det_int_rec.customer_job ;
860 l_del_details_info.customer_production_line := del_det_int_rec.customer_production_line;
861 l_del_details_info.net_weight := del_det_int_rec.net_weight;
862 l_del_details_info.weight_uom_code := del_det_int_rec.weight_uom_code;
863 l_del_details_info.volume := del_det_int_rec.volume;
864 l_del_details_info.volume_uom_code := del_det_int_rec.volume_uom_code;
865 l_del_details_info.tp_attribute_category := del_det_int_rec.tp_attribute_category;
866 l_del_details_info.tp_attribute1 := del_det_int_rec.tp_attribute1;
867 l_del_details_info.tp_attribute2 := del_det_int_rec.tp_attribute2;
868 l_del_details_info.tp_attribute3 := del_det_int_rec.tp_attribute3;
869 l_del_details_info.tp_attribute4 := del_det_int_rec.tp_attribute4;
870 l_del_details_info.tp_attribute5 := del_det_int_rec.tp_attribute5;
871 l_del_details_info.tp_attribute6 := del_det_int_rec.tp_attribute6;
872 l_del_details_info.tp_attribute7 := del_det_int_rec.tp_attribute7;
873 l_del_details_info.tp_attribute8 := del_det_int_rec.tp_attribute8;
874 l_del_details_info.tp_attribute9 := del_det_int_rec.tp_attribute9;
875 l_del_details_info.tp_attribute10 := del_det_int_rec.tp_attribute10;
876 l_del_details_info.tp_attribute11 := del_det_int_rec.tp_attribute11;
877 l_del_details_info.tp_attribute12 := del_det_int_rec.tp_attribute12;
878 l_del_details_info.tp_attribute13 := del_det_int_rec.tp_attribute13;
879 l_del_details_info.tp_attribute14 := del_det_int_rec.tp_attribute14;
880 l_del_details_info.tp_attribute15 := del_det_int_rec.tp_attribute15;
881 l_del_details_info.attribute_category := del_det_int_rec.attribute_category;
882 l_del_details_info.attribute1 := del_det_int_rec.attribute1;
883 l_del_details_info.attribute2 := del_det_int_rec.attribute2;
884 l_del_details_info.attribute3 := del_det_int_rec.attribute3;
885 l_del_details_info.attribute4 := del_det_int_rec.attribute4;
886 l_del_details_info.attribute5 := del_det_int_rec.attribute5;
887 l_del_details_info.attribute6 := del_det_int_rec.attribute6;
888 l_del_details_info.attribute7 := del_det_int_rec.attribute7;
889 l_del_details_info.attribute8 := del_det_int_rec.attribute8;
890 l_del_details_info.attribute9 := del_det_int_rec.attribute9;
891 l_del_details_info.attribute10 := del_det_int_rec.attribute10;
892 l_del_details_info.attribute11 := del_det_int_rec.attribute11;
893 l_del_details_info.attribute12 := del_det_int_rec.attribute12;
894 l_del_details_info.attribute13 := del_det_int_rec.attribute13;
895 l_del_details_info.attribute14 := del_det_int_rec.attribute14;
896 l_del_details_info.attribute15 := del_det_int_rec.attribute15;
897
898 /* do we need to send the who columns for create/update ??
899 l_del_details_info.created_by := del_det_int_rec.created_by;
900 l_del_details_info.creation_date := del_det_int_rec.creation_date;
901 l_del_details_info.last_update_date := del_det_int_rec.last_update_date;
902 l_del_details_info.last_update_login := del_det_int_rec.last_update_login;
903 -- l_del_details_info.last_updated_by := del_det_int_rec.last_updated_by;
904 l_del_details_info.program_application_id := del_det_int_rec.program_application_id;
905 l_del_details_info.program_id := del_det_int_rec.program_id;
906 l_del_details_info.program_update_date := del_det_int_rec.program_update_date;
907 l_del_details_info.request_id := del_det_int_rec.request_id;
908 */
909 l_del_details_info.mvt_stat_status := del_det_int_rec.mvt_stat_status;
910
911 l_del_details_info.organization_id := del_det_int_rec.organization_id;
912 l_del_details_info.transaction_temp_id := del_det_int_rec.transaction_temp_id;
913 l_del_details_info.ship_set_id := del_det_int_rec.ship_set_id;
914 l_del_details_info.arrival_set_id := del_det_int_rec.arrival_set_id;
915 l_del_details_info.ship_model_complete_flag := del_det_int_rec.ship_model_complete_flag;
916
917 l_del_details_info.top_model_line_id := del_det_int_rec.top_model_line_id;
918 l_del_details_info.source_header_number := del_det_int_rec.source_header_number;
919 l_del_details_info.source_header_type_id := del_det_int_rec.source_header_type_id;
920 l_del_details_info.source_header_type_name := del_det_int_rec.source_header_type_name;
921 l_del_details_info.cust_po_number := del_det_int_rec.cust_po_number;
922 l_del_details_info.ato_line_id := del_det_int_rec.ato_line_id;
923 l_del_details_info.src_requested_quantity := del_det_int_rec.src_requested_quantity;
924 l_del_details_info.src_requested_quantity_uom := del_det_int_rec.src_requested_quantity_uom;
925 l_del_details_info.move_order_line_id := del_det_int_rec.move_order_line_id;
926 l_del_details_info.cancelled_quantity := del_det_int_rec.cancelled_quantity;
927 l_del_details_info.quality_control_quantity := del_det_int_rec.quality_control_quantity;
928 l_del_details_info.cycle_count_quantity := del_det_int_rec.cycle_count_quantity;
929 l_del_details_info.tracking_number := del_det_int_rec.tracking_number;
930 l_del_details_info.movement_id := del_det_int_rec.movement_id;
931 l_del_details_info.shipping_instructions := del_det_int_rec.shipping_instructions;
932 l_del_details_info.packing_instructions := del_det_int_rec.packing_instructions;
933 l_del_details_info.project_id := del_det_int_rec.project_id;
934 l_del_details_info.task_id := del_det_int_rec.task_id;
935
936 l_del_details_info.inspection_flag := del_det_int_rec.inspection_flag;
937 l_del_details_info.container_flag := del_det_int_rec.container_flag;
938 l_del_details_info.container_type_code := del_det_int_rec.container_type_code ;
939
940 l_del_details_info.container_name := del_det_int_rec.container_name;
941 l_del_details_info.fill_percent := del_det_int_rec.fill_percent;
942 l_del_details_info.gross_weight := del_det_int_rec.gross_weight;
943
944 l_del_details_info.master_serial_number := del_det_int_rec.master_serial_number;
945 l_del_details_info.maximum_load_weight := del_det_int_rec.maximum_load_weight;
946 l_del_details_info.maximum_volume := del_det_int_rec.maximum_volume;
947 l_del_details_info.minimum_fill_percent := del_det_int_rec.minimum_fill_percent;
948 l_del_details_info.seal_code := del_det_int_rec.seal_code;
949 l_del_details_info.unit_number := del_det_int_rec.unit_number ;
950 l_del_details_info.unit_price := del_det_int_rec.unit_price;
951 l_del_details_info.currency_code := del_det_int_rec.currency_code;
952 l_del_details_info.freight_class_cat_id := del_det_int_rec.freight_class_cat_id;
953 l_del_details_info.commodity_code_cat_id := del_det_int_rec.commodity_code_cat_id;
954 l_del_details_info.preferred_grade := del_det_int_rec.preferred_grade;
955 l_del_details_info.src_requested_quantity2 := del_det_int_rec.src_requested_quantity2;
956 l_del_details_info.src_requested_quantity_uom2 := del_det_int_rec.src_requested_quantity_uom2 ;
957 l_del_details_info.requested_quantity2 := del_det_int_rec.requested_quantity2;
958 l_del_details_info.shipped_quantity2 := del_det_int_rec.shipped_quantity2;
959 l_del_details_info.delivered_quantity2 := del_det_int_rec.delivered_quantity2 ;
960 l_del_details_info.cancelled_quantity2 := del_det_int_rec.cancelled_quantity2;
961 l_del_details_info.quality_control_quantity2 := del_det_int_rec.quality_control_quantity2;
962 l_del_details_info.cycle_count_quantity2 := del_det_int_rec.cycle_count_quantity2;
963 l_del_details_info.requested_quantity_uom2 := del_det_int_rec.requested_quantity_uom2;
964 -- HW OPMCONV - No need for sublot_number
965 -- l_del_details_info.sublot_number := del_det_int_rec.sublot_number ;
966 l_del_details_info.lpn_id := del_det_int_rec.lpn_id ;
967 -- bug 2399705
968 -- We need to make this fix as WSHDDSHB.pls and WSHDDSPB.pls need to
969 -- set inv_interfaced_flag to 'X' if pickable_flag is 'N'or NULL for WSH line.
970 -- And if we do not set the pickable_flag according the item defn, inventory interface
971 -- will not be run even for standard items the third party warehouse instance.
972 open c_specific_item_info(p_inventory_item_id => del_det_int_rec.inventory_item_id,
973 p_organization_id => del_det_int_rec.organization_id);
974 fetch c_specific_item_info into l_pickable_flag,
975 -- J: W/V Changes
976 l_unit_weight,l_unit_volume;
977 close c_specific_item_info;
978 l_del_details_info.pickable_flag := l_pickable_flag;
979 -- J: W/V Changes
980 l_del_details_info.unit_weight := l_unit_weight;
981 l_del_details_info.unit_volume := l_unit_volume;
982 l_del_details_info.wv_frozen_flag := del_det_int_rec.wv_frozen_flag;
983 -- bug 2399705
984 -- l_del_details_info.pickable_flag := del_det_int_rec.pickable_flag ;
985 l_del_details_info.original_subinventory := del_det_int_rec.original_subinventory ;
986 l_del_details_info.to_serial_number := del_det_int_rec.to_serial_number;
987 l_del_details_info.picked_quantity := del_det_int_rec.picked_quantity;
988 l_del_details_info.picked_quantity2 := del_det_int_rec.picked_quantity2;
989 --Bug 3458160
990 l_del_details_info.LINE_DIRECTION := del_det_int_rec.LINE_DIRECTION;
991 l_del_details_info.REQUEST_DATE_TYPE_CODE:= del_det_int_rec.REQUEST_DATE_TYPE_CODE;
992 l_del_details_info.EARLIEST_PICKUP_DATE := del_det_int_rec.EARLIEST_PICKUP_DATE;
993 l_del_details_info.LATEST_PICKUP_DATE := del_det_int_rec.LATEST_PICKUP_DATE;
994 l_del_details_info.EARLIEST_DROPOFF_DATE:= del_det_int_rec.EARLIEST_DROPOFF_DATE;
995 l_del_details_info.LATEST_DROPOFF_DATE := del_det_int_rec.LATEST_DROPOFF_DATE;
996 IF (WSH_UTIL_CORE.GC3_IS_INSTALLED = 'N'
997 AND wsh_util_core.tp_is_installed = 'Y')
998 THEN
999 l_del_details_info.ignore_for_planning := 'Y';
1000 END IF;
1001
1002 --{Bug 8539281
1003 IF l_debug_on THEN
1004 wsh_debug_sv.log (l_module_name, 'Organization id',l_del_details_info.organization_id);
1005 wsh_debug_sv.logmsg(l_module_name,'Calling program unit WSH_SHIPPING_PARAMS_PVT.GET',WSH_DEBUG_SV.C_PROC_LEVEL);
1006 END IF;
1007
1008 WSH_SHIPPING_PARAMS_PVT.GET( p_organization_id => l_del_details_info.organization_id,
1009 x_param_info => l_shipping_param_info,
1010 x_return_status => l_return_status);
1011
1012 IF l_debug_on THEN
1013 wsh_debug_sv.log (l_module_name, 'Return Status from WSH_SHIPPING_PARAMS_PVT.GET', l_return_status);
1014 END IF;
1015
1016 IF(l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1017 raise FND_API.G_EXC_ERROR ;
1018 END IF;
1019
1020 --If any of the delivery grouping attribute is enabled copy the grouping attributes from delivery to delivery line
1021 IF (l_shipping_param_info.GROUP_BY_SHIP_METHOD_FLAG = 'Y' or
1022 l_shipping_param_info.GROUP_BY_CUSTOMER_FLAG = 'Y' or
1023 l_shipping_param_info.GROUP_BY_FREIGHT_TERMS_FLAG = 'Y' or
1024 l_shipping_param_info.GROUP_BY_FOB_FLAG = 'Y' or
1025 l_shipping_param_info.GROUP_BY_INTMED_SHIP_TO_FLAG = 'Y') THEN
1026 --{
1027 IF l_debug_on THEN
1028 wsh_debug_sv.log (l_module_name, 'Getting the new delivery attributes-Delivery ID:',p_new_delivery_id);
1029 wsh_debug_sv.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.TABLE_TO_RECORD',WSH_DEBUG_SV.C_PROC_LEVEL);
1030 END IF;
1031
1032 WSH_NEW_DELIVERIES_PVT.TABLE_TO_RECORD(p_delivery_id=>p_new_delivery_id,
1033 x_delivery_rec=>l_delivery_rec,
1034 x_return_status=>l_return_status);
1035
1036 IF l_debug_on THEN
1037 wsh_debug_sv.log (l_module_name, 'Return Status from WSH_NEW_DELIVERIES_PVT.TABLE_TO_RECORD', l_return_status);
1038 END IF;
1039
1040 IF(l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1041 raise FND_API.G_EXC_ERROR ;
1042 END IF;
1043
1044 IF l_shipping_param_info.GROUP_BY_SHIP_METHOD_FLAG = 'Y' THEN
1045 --{
1046 IF l_debug_on THEN
1047 wsh_debug_sv.logmsg(l_module_name, 'Copying Ship Method attributes from delivery to delivery detail:'||
1048 ' Carrier_id - '||to_char(l_delivery_rec.carrier_id)||' ,'||
1049 ' service_level - '||l_delivery_rec.service_level||' ,'||
1050 ' mode_of_transport - '||l_delivery_rec.mode_of_transport||' ,'||
1051 ' ship_method_code - '||l_delivery_rec.ship_method_code,WSH_DEBUG_SV.C_STMT_LEVEL);
1052 END IF;
1053 l_del_details_info.carrier_id := l_delivery_rec.carrier_id ;
1054 l_del_details_info.service_level := l_delivery_rec.service_level ;
1055 l_del_details_info.mode_of_transport := l_delivery_rec.mode_of_transport ;
1056 l_del_details_info.ship_method_code := l_delivery_rec.ship_method_code ;
1057 --}
1058 END IF;
1059
1060 IF l_shipping_param_info.GROUP_BY_CUSTOMER_FLAG = 'Y' THEN
1061
1062 IF l_debug_on THEN
1063 wsh_debug_sv.log (l_module_name, 'Copying customer_id value from delivery to delivery detail',l_delivery_rec.customer_id);
1064 END IF;
1065 l_del_details_info.customer_id := l_delivery_rec.customer_id ;
1066
1067 END IF;
1068
1069 IF l_shipping_param_info.GROUP_BY_FREIGHT_TERMS_FLAG = 'Y' THEN
1070
1071 IF l_debug_on THEN
1072 wsh_debug_sv.log (l_module_name, 'Copying freight_terms_code value from delivery to delivery detail',l_delivery_rec.freight_terms_code);
1073 END IF;
1074 l_del_details_info.freight_terms_code := l_delivery_rec.freight_terms_code ;
1075
1076 END IF;
1077
1078 IF l_shipping_param_info.GROUP_BY_FOB_FLAG = 'Y' THEN
1079
1080 IF l_debug_on THEN
1081 wsh_debug_sv.log (l_module_name, 'Copying fob_code value from delivery to delivery detail',l_delivery_rec.fob_code);
1082 END IF;
1083 l_del_details_info.fob_code := l_delivery_rec.fob_code ;
1084
1085 END IF;
1086
1087 IF l_shipping_param_info.GROUP_BY_INTMED_SHIP_TO_FLAG = 'Y' THEN
1088
1089 IF l_debug_on THEN
1090 wsh_debug_sv.log (l_module_name, 'Copying intmed_ship_to_location_id value from delivery to delivery detail',l_delivery_rec.intmed_ship_to_location_id);
1091 END IF;
1092 l_del_details_info.intmed_ship_to_location_id := l_delivery_rec.intmed_ship_to_location_id ;
1093
1094 END IF;
1095 --}
1096 END IF; --If any of the delivery grouping attribute is enabled
1097 --}Bug 8539281
1098
1099 IF l_debug_on THEN
1100 wsh_debug_sv.log (l_module_name, 'calling create shipment lines');
1101 wsh_debug_sv.log (l_module_name, 'the mandatory attributes for create shipment lines are:');
1102 wsh_debug_sv.log (l_module_name, 'source_code',l_del_details_info.source_code);
1103 wsh_debug_sv.log (l_module_name, 'Source header id',l_del_details_info.source_header_id);
1104 wsh_debug_sv.log (l_module_name, 'Source header number',l_del_details_info.source_header_number);
1105 wsh_debug_sv.log (l_module_name, 'Source line id',l_del_details_info.source_line_id);
1106 wsh_debug_sv.log (l_module_name, 'Source line number',l_del_details_info.source_line_number);
1107 wsh_debug_sv.log (l_module_name, 'Organization id',l_del_details_info.organization_id);
1108 wsh_debug_sv.log (l_module_name, 'Org Id', l_del_details_info.org_id);
1109 wsh_debug_sv.log (l_module_name, 'Requested qty', l_del_details_info.requested_quantity);
1110 wsh_debug_sv.log (l_module_name, 'Requested qty uom',l_del_details_info.requested_quantity_uom );
1111 wsh_debug_sv.log (l_module_name, 'Src req qty',l_del_details_info. src_requested_quantity);
1112 wsh_debug_sv.log (l_module_name, 'Src req qty uom', l_del_details_info.src_requested_quantity_uom);
1113 wsh_debug_sv.log (l_module_name, 'inventory item id',l_del_details_info.inventory_item_id);
1114 wsh_debug_sv.log (l_module_name, 'ship from loc id',l_del_details_info.ship_from_location_id);
1115 wsh_debug_sv.log (l_module_name, 'ship to loc id', l_del_details_info.ship_to_location_id);
1116 wsh_debug_sv.log (l_module_name, 'LINE_DIRECTION', l_del_details_info.LINE_DIRECTION);
1117 wsh_debug_sv.log (l_module_name, 'REQUEST_DATE_TYPE_CODE', l_del_details_info.REQUEST_DATE_TYPE_CODE);
1118 wsh_debug_sv.log (l_module_name, 'EARLIEST_PICKUP_DATE', l_del_details_info.EARLIEST_PICKUP_DATE);
1119 wsh_debug_sv.log (l_module_name, 'LATEST_PICKUP_DATE', l_del_details_info.LATEST_PICKUP_DATE);
1120 wsh_debug_sv.log (l_module_name, 'EARLIEST_DROPOFF_DATE', l_del_details_info.EARLIEST_DROPOFF_DATE);
1121 wsh_debug_sv.log (l_module_name, 'LATEST_DROPOFF_DATE', l_del_details_info.LATEST_DROPOFF_DATE);
1122 --}
1123 END IF;
1124
1125
1126 /* Patchset I : Harmonization Project.
1127 Calling Wrapper Group API in Interface Package */
1128
1129 l_detail_info_tab(1) := l_del_details_info;
1130 l_detail_in_rec.caller := 'WSH_TPW_INBOUND';
1131 l_detail_in_rec.action_code := 'CREATE';
1132
1133 WSH_INTERFACE_GRP.Create_Update_Delivery_Detail(
1134 p_api_version_number => l_api_version,
1135 p_init_msg_list => FND_API.G_FALSE,
1136 p_commit => FND_API.G_FALSE,
1137 x_return_status => l_return_status,
1138 x_msg_count => l_msg_count,
1139 x_msg_data => l_msg_data,
1140 p_detail_info_tab => l_detail_info_tab,
1141 p_IN_rec => l_detail_in_rec,
1142 x_OUT_rec => l_detail_out_rec);
1143
1144 l_index := l_detail_out_rec.detail_ids.first;
1145 l_new_del_detail_id := l_detail_out_rec.detail_ids(l_index);
1146
1147
1148 IF l_debug_on THEN
1149 wsh_debug_sv.log (l_module_name, 'Create Shipment Lines l_new_del_detail_id,l_return_status',
1150 l_new_del_detail_id||','||l_return_status);
1151 END IF;
1152
1153 IF(l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1154
1155 l_loc_interface_error_rec.p_interface_table_name := 'WSH_NEW_DEL_INTERFACE';
1156 l_loc_interface_error_rec.p_interface_id := p_delivery_interface_id;
1157
1158 IF l_debug_on THEN
1159 wsh_debug_sv.log (l_module_name, 'Delivery Interface Id', p_delivery_interface_id);
1160 END IF;
1161
1162 Log_Errors(
1163 p_loc_interface_errors_rec => l_loc_interface_error_rec,
1164 p_msg_data => l_msg_data,
1165 p_api_name => 'WSH_INTERFACE_PUB.Create_Shipment_lines' ,
1166 x_return_status => l_return_status);
1167
1168 IF l_debug_on THEN
1169 wsh_debug_sv.log (l_module_name, 'Return status after log_errors', l_return_status);
1170 END IF;
1171 raise create_lines_failed;
1172 END IF;
1173
1174 l_table_count := l_new_detail_ids.count;
1175
1176 IF l_new_del_detail_id IS NOT NULL THEN
1177 l_new_detail_ids(l_table_count+1) := l_new_del_detail_id;
1178 ELSE
1179 raise create_lines_failed;
1180 END IF;
1181 ELSIF(p_action_code = 'UPDATE') THEN --}{
1182 IF l_debug_on THEN
1183 wsh_debug_sv.logmsg(l_module_name,'Starting Update Action');
1184 END IF;
1185 IF(nvl(del_det_int_rec.container_flag, 'N') = 'Y') THEN
1186 -- container record
1187 -- check if a container instance exists
1188 -- if it does not exist, then create a container instance using the item_id
1189
1190 OPEN cont_inst_exists(del_det_int_rec.delivery_detail_id);
1191 FETCH cont_inst_exists INTO l_cont_inst_exists;
1192 CLOSE cont_inst_exists;
1193
1194 IF l_debug_on THEN
1195 wsh_debug_sv.log (l_module_name, 'Container Instance Check', l_cont_inst_exists);
1196 END IF;
1197
1198 IF(nvl(l_cont_inst_exists, -9999) = 1) THEN
1199 -- Just add to the global update table.
1200 null;
1201 ELSE
1202 -- need to create the container instance
1203 --
1204 -- K LPN CONV. rv
1205 --
1206 WSH_CONTAINER_ACTIONS.Create_Cont_Instance_Multi(
1207 x_cont_name => l_cont_name,
1208 p_cont_item_id => del_det_int_rec.inventory_item_id,
1209 x_cont_instance_id => l_cont_instance_id,
1210 p_par_detail_id => NULL,
1211 p_organization_id => del_det_int_rec.organization_id,
1212 p_container_type_code => del_det_int_rec.container_type_code,
1213 p_num_of_containers => 1,
1214 x_row_id => l_row_id,
1215 x_return_status => l_return_status,
1216 x_cont_tab => l_cont_tab,
1217 x_unit_weight => l_lpn_unit_weight,
1218 x_unit_volume => l_lpn_unit_volume,
1219 x_weight_uom_code => l_lpn_weight_uom_code,
1220 x_volume_uom_code => l_lpn_volume_uom_code,
1221 p_lpn_id => NULL,
1222 p_ignore_for_planning => NULL,
1223 p_caller => 'WSH');
1224 --
1225 --
1226 IF l_debug_on THEN
1227 WSH_DEBUG_SV.log(l_module_name,'return status',l_return_status);
1228 WSH_DEBUG_SV.log(l_module_name,'count of l_cont_tab',l_cont_tab.count);
1229 END IF;
1230 l_cont_instance_id := l_cont_tab(1);
1231 -- K LPN CONV. rv
1232 --
1233 /*
1234 WSH_CONTAINER_ACTIONS.Create_Container_Instance(
1235 x_cont_name => l_cont_name,
1236 p_cont_item_id => del_det_int_rec.inventory_item_id,
1237 x_cont_instance_id => l_cont_instance_id,
1238 p_par_detail_id => NULL,
1239 p_organization_id => del_det_int_rec.organization_id,
1240 p_container_type_code => del_det_int_rec.container_type_code,
1241 x_row_id => l_row_id,
1242 x_return_status => l_return_status);
1243
1244 IF l_debug_on THEN
1245 wsh_debug_sv.log (l_module_name, 'Create_Container_Instance l_cont_instance_id,
1246 l_return_status',l_cont_instance_id||','||l_return_status);
1247 END IF;
1248 */
1249
1250 IF(l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1251 raise create_cont_instance_failed;
1252 END IF;
1253
1254
1255
1256 -- need to update the source_line_id of the newly created container instance
1257 UPDATE wsh_delivery_details
1258 SET source_line_id = del_det_int_rec.delivery_detail_id
1259 WHERE delivery_detail_id = l_cont_instance_id;
1260
1261 IF(SQL%NOTFOUND) THEN
1262 null;
1263 --need to check
1264 END IF;
1265
1266
1267 -- Need to update the record's delivery_detail id with the newly created
1268 -- delivery_detail_id. Because this record will be sent to USA for updating
1269 -- the newly created container instance with the data from the interface table record.
1270 del_det_int_rec.delivery_detail_id := l_cont_instance_id;
1271
1272 -- TPW - Distributed changes
1273 --Add the container instance id to the list of detail ids that need assignment
1274 IF (nvl(WSH_INBOUND_SHIP_ADVICE_PKG.G_WAREHOUSE_TYPE, '!') = 'TW2') THEN
1275 l_new_detail_ids(l_new_detail_ids.COUNT+1) := l_cont_instance_id;
1276 END IF;
1277
1278 END IF; -- if l_cont_inst_exists
1279
1280 Add_To_Update_Table(
1281 p_del_det_int_rec => del_det_int_rec,
1282 p_update_mode => 'UPDATE',
1283 p_delivery_id => l_delivery_id,
1284 x_return_status => l_return_status);
1285
1286 IF l_debug_on THEN
1287 wsh_debug_sv.log (l_module_name, 'Add_To_Update_Table l_return_status',l_return_status);
1288 END IF;
1289
1290 IF(l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1291 raise add_to_update_failed;
1292 END IF;
1293
1294
1295 ELSE -- not a container, plain update
1296 -- TPW - Distributed changes
1297 IF l_debug_on THEN
1298 wsh_debug_sv.log (l_module_name, 'in Else');
1299 wsh_debug_sv.log (l_module_name, 'del_det_int_rec.source_line_id', del_det_int_rec.source_line_id);
1300 wsh_debug_sv.log (l_module_name, 'del_det_int_rec.source_header_number', del_det_int_rec.source_header_number);
1301 END IF;
1302
1303 IF (nvl(WSH_INBOUND_SHIP_ADVICE_PKG.G_WAREHOUSE_TYPE, '!') = 'TW2') THEN --{
1304
1305 IF l_debug_on THEN
1306 wsh_debug_sv.logmsg(l_module_name, 'Line Direction '||del_det_int_rec.line_direction||
1307 ' SHN '||del_det_int_rec.source_header_number||
1308 ' Intf DDID '||del_det_int_rec.delivery_detail_id);
1309 END IF;
1310
1311 IF (del_det_int_rec.line_direction = 'IO') THEN
1312 select wdd.delivery_detail_id, wdd.requested_quantity BULK COLLECT
1313 into l_detail_id_tab, l_detail_qty_tab
1314 from wsh_delivery_details wdd,
1315 oe_order_lines_all ol,
1316 po_requisition_lines_all pl,
1317 po_requisition_headers_all ph
1318 where wdd.source_code = 'OE'
1319 and wdd.released_status in ('R','B','X')
1320 and wdd.source_line_id = ol.line_id
1321 and ol.source_document_line_id = pl.requisition_line_id
1322 and ol.source_document_id = pl.requisition_header_id
1323 and pl.requisition_header_id = ph.requisition_header_id
1324 and pl.line_num = del_det_int_rec.delivery_detail_id
1325 and ph.segment1 = del_det_int_rec.source_header_number;
1326 ELSE
1327 select wdd.delivery_detail_id, wdd.requested_quantity BULK COLLECT
1328 into l_detail_id_tab, l_detail_qty_tab
1329 from wsh_delivery_details wdd,
1330 wsh_shipment_batches wsb,
1331 wsh_transactions_history wth
1332 where wdd.source_code = 'OE'
1333 and wdd.shipment_batch_id = wsb.batch_id
1334 and wdd.shipment_line_number = del_det_int_rec.delivery_detail_id
1335 and wsb.name = wth.entity_number
1336 and wth.entity_type = 'BATCH'
1337 and wth.document_direction = 'O'
1338 and wth.document_type = 'SR'
1339 and wth.document_number = del_det_int_rec.source_header_number
1340 and wdd.released_status in ('R','B','X');
1341 END IF;
1342
1343 IF l_debug_on THEN
1344 FOR i in 1..l_detail_id_tab.COUNT LOOP
1345 wsh_debug_sv.logmsg(l_module_name,'DD-id '||l_detail_id_tab(i)||' Req Qty '||l_detail_qty_tab(i));
1346 END LOOP;
1347 END IF;
1348
1349 IF (del_det_int_rec.serial_number is not null) and (l_detail_id_tab.COUNT > 1) THEN
1350 -- Not Supported
1351 IF l_debug_on THEN
1352 wsh_debug_sv.logmsg(l_module_name,'Serial types not supported for remnant models');
1353 END IF;
1354 raise fnd_api.g_exc_error;
1355 -- Serial Number is NOT NULL
1356 ELSIF del_det_int_rec.serial_number IS NOT NULL THEN
1357 IF l_debug_on THEN
1358 wsh_debug_sv.log(l_module_name,'PNS: del_det_int_rec.inventory_item_id', del_det_int_rec.inventory_item_id);
1359 wsh_debug_sv.log(l_module_name,'PNS: del_det_int_rec.organization_id', del_det_int_rec.organization_id);
1360 wsh_debug_sv.log(l_module_name,'PNS: del_det_int_rec.serial_number', del_det_int_rec.serial_number);
1361 wsh_debug_sv.log(l_module_name,'PNS: del_det_int_rec.to_serial_number', del_det_int_rec.to_serial_number);
1362 END IF;
1363
1364 BEGIN
1365 SELECT serial_number_control_code
1366 INTO l_serial_number_control
1367 FROM mtl_system_items
1368 WHERE inventory_item_id = del_det_int_rec.inventory_item_id
1369 AND organization_id = del_det_int_rec.organization_id;
1370
1371 IF l_debug_on THEN
1372 wsh_debug_sv.log(l_module_name,'PNS: l_serial_number_control', l_serial_number_control);
1373 END IF;
1374
1375 EXCEPTION
1376 WHEN NO_DATA_FOUND THEN
1377 IF l_debug_on THEN
1378 wsh_debug_sv.logmsg(l_module_name,'PNS: Inside No-Data-Found');
1379 END IF;
1380 END;
1381
1382 -- If item is not serial controlled then NULL out serial
1383 -- information obtained from WDDI interface table.
1384 IF nvl(l_serial_number_control, 0) = 1 THEN
1385 del_det_int_rec.serial_number := NULL;
1386 del_det_int_rec.to_serial_number := NULL;
1387 END IF;
1388 END IF;
1389
1390 l_pending_req_qty := del_det_int_rec.requested_quantity;
1391 l_pending_shp_qty := del_det_int_rec.shipped_quantity;
1392 l_curr_index := 1;
1393
1394 while (l_pending_req_qty > 0) loop --{
1395
1396 if l_debug_on then
1397 wsh_debug_sv.logmsg(l_module_name, 'l_pending_req_qty '||l_pending_req_qty||
1398 ' l_pending_shp_qty '||l_pending_shp_qty);
1399 wsh_debug_sv.logmsg(l_module_name, ' l_curr_index '||l_curr_index||
1400 ' l_detail_id_tab(l_curr_index) '||l_detail_id_tab(l_curr_index));
1401 end if;
1402
1403 IF (l_pending_req_qty <= l_detail_qty_tab(l_curr_index)) THEN --{
1404 split_delivery_detail(
1405 p_delivery_detail_id => l_detail_id_tab(l_curr_index),
1406 p_qty_to_split => l_pending_req_qty,
1407 x_new_detail_id => l_new_split_detail_id,
1408 x_return_status => l_return_status);
1409
1410 wsh_util_core.api_post_call(p_return_status => l_return_status,
1411 x_num_warnings => l_number_of_warnings,
1412 x_num_errors => l_number_of_errors);
1413
1414 -- For the newly created base delivery detail, make updates
1415 -- Add the record with new detail id to the global table
1416 if l_debug_on then
1417 wsh_debug_sv.log(l_module_name, 'l_new_split_detail_id', l_new_split_detail_id);
1418 end if;
1419
1420 IF (l_new_split_detail_id is not null) THEN
1421 del_det_int_rec.delivery_detail_id := l_new_split_detail_id;
1422 ELSE
1423 del_det_int_rec.delivery_detail_id := l_detail_id_tab(l_curr_index);
1424 END IF;
1425 del_det_int_rec.requested_quantity := l_pending_req_qty;
1426 del_det_int_rec.shipped_quantity := l_pending_shp_qty;
1427 del_det_int_rec.cycle_count_quantity := null;
1428
1429 l_pending_req_qty := 0;
1430 l_pending_shp_qty := 0;
1431
1432 ELSE
1433 del_det_int_rec.delivery_detail_id := l_detail_id_tab(l_curr_index);
1434 del_det_int_rec.requested_quantity := l_detail_qty_tab(l_curr_index);
1435 del_det_int_rec.shipped_quantity := l_detail_qty_tab(l_curr_index);
1436 del_det_int_rec.cycle_count_quantity := null;
1437 l_pending_req_qty := l_pending_req_qty - l_detail_qty_tab(l_curr_index);
1438 l_pending_shp_qty := l_pending_shp_qty - l_detail_qty_tab(l_curr_index);
1439
1440 l_curr_index := l_curr_index + 1;
1441
1442 END IF; --}
1443
1444 Add_To_Update_Table(
1445 p_del_det_int_rec => del_det_int_rec,
1446 p_update_mode => 'UPDATE',
1447 p_delivery_id => l_delivery_id,
1448 x_return_status => l_return_status);
1449
1450 IF l_debug_on THEN
1451 wsh_debug_sv.log (l_module_name, 'Add_To_Update_Table l_return_status',l_return_status);
1452 END IF;
1453
1454 IF(l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1455 raise add_to_update_failed;
1456 END IF;
1457
1458 l_new_detail_ids(l_new_detail_ids.COUNT+1) := del_det_int_rec.delivery_detail_id;
1459 end loop; --}
1460 ELSE
1461
1462 -- Add to the global table here. Because we need to call USA for all three update cases viz.
1463 -- 1. newly created container instance
1464 -- 2. existing container instance
1465 -- 3. existing non-container delivery details
1466
1467 Add_To_Update_Table(
1468 p_del_det_int_rec => del_det_int_rec,
1469 p_update_mode => 'UPDATE',
1470 p_delivery_id => l_delivery_id,
1471 x_return_status => l_return_status);
1472
1473 IF l_debug_on THEN
1474 wsh_debug_sv.log (l_module_name, 'Add_To_Update_Table l_return_status',l_return_status);
1475 END IF;
1476
1477 IF(l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1478 raise add_to_update_failed;
1479 END IF;
1480 END IF; --}
1481
1482 END IF; -- if nvl(del_det container flag)
1483
1484
1485 -- Process Interface freight costs
1486 SELECT count(*) INTO l_det_freight_costs
1487 FROM wsh_freight_costs_interface
1488 WHERE delivery_detail_interface_id = del_det_int_rec.delivery_detail_interface_id
1489 AND INTERFACE_ACTION_CODE = '94X_INBOUND';
1490
1491 IF l_debug_on THEN
1492 wsh_debug_sv.log (l_module_name, 'Interface freight count', l_det_freight_costs);
1493 END IF;
1494
1495 IF(l_det_freight_costs > 0 ) THEN
1496 Process_Int_Freight_Costs(
1497 p_del_detail_interface_id => del_det_int_rec.delivery_detail_interface_id,
1498 -- TPW - Distributed changes
1499 p_delivery_detail_id => del_det_int_rec.delivery_detail_id,
1500 x_return_status => l_return_status);
1501
1502 IF l_debug_on THEN
1503 wsh_debug_sv.log (l_module_name,'Return status from Process Int Freight Costs',l_return_status);
1504 END IF;
1505
1506 IF( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1507 raise freight_cost_processing_error;
1508 END IF;
1509
1510 END IF;
1511 END IF; -- if action code = create }
1512
1513
1514 END LOOP; -- for det_id
1515
1516 -- For the newly created delivery details, call detail_to_delivery to assign them to delivery
1517 IF l_debug_on THEN
1518 wsh_debug_sv.log (l_module_name, 'Assign table count', l_new_detail_ids.count);
1519 END IF;
1520
1521 IF(l_new_detail_ids.count > 0) THEN
1522 WSH_DELIVERY_DETAILS_GRP.Detail_To_Delivery(
1523 p_api_version =>1.0,
1524 p_init_msg_list => l_init_msg_list,
1525 p_validation_level => l_validation_level,
1526 p_commit => l_commit,
1527 x_return_status => l_return_status,
1528 x_msg_count => l_msg_count,
1529 x_msg_data => l_msg_data,
1530 p_TabOfDelDets => l_new_detail_ids,
1531 p_action => 'ASSIGN',
1532 p_delivery_id => p_new_delivery_id,
1533 p_delivery_name => l_delivery_name
1534 );
1535
1536 IF l_debug_on THEN
1537 wsh_debug_sv.log (l_module_name, 'Return status from Detail to delivery', l_return_status);
1538 wsh_debug_sv.log (l_module_name, 'Detail to Delivery api msg count', l_msg_count);
1539 wsh_debug_sv.log (l_module_name, 'Detail to Delivery api msg data', l_msg_data);
1540 END IF;
1541
1542 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1543 raise new_assignment_failed;
1544 END IF;
1545 END IF;
1546
1547 IF l_debug_on THEN
1548 wsh_debug_sv.pop(l_module_name);
1549 END IF;
1550 EXCEPTION
1551 WHEN FND_API.G_EXC_ERROR THEN
1552 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1553 IF l_debug_on THEN
1554 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1555 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FND_API.G_EXC_ERROR');
1556 END IF;
1557 WHEN create_cont_instance_failed THEN
1558 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1559 IF l_debug_on THEN
1560 WSH_DEBUG_SV.logmsg(l_module_name,'create_cont_instance_failed exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1561 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:create_cont_instance_failed');
1562 END IF;
1563 WHEN invalid_input THEN
1564 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1565 IF l_debug_on THEN
1566 WSH_DEBUG_SV.logmsg(l_module_name,'invalid_input exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1567 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_input');
1568 END IF;
1569 WHEN create_lines_failed THEN
1570 FND_MESSAGE.SET_NAME('WSH', 'WSH_CREATE_LINES_FAILED');
1571 FND_MESSAGE.SET_TOKEN('DET_INT',del_det_int_rec.delivery_detail_interface_id);
1572 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1573 IF l_debug_on THEN
1574 WSH_DEBUG_SV.logmsg(l_module_name,'create_lines_failed exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1575 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:create_lines_failed');
1576 END IF;
1577 WHEN new_assignment_failed THEN
1578 FND_MESSAGE.SET_NAME('WSH','WSH_DEL_ASSGN_ERROR');
1579 FND_MESSAGE.SET_TOKEN('DLVY', p_new_delivery_id);
1580 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1581 IF l_debug_on THEN
1582 WSH_DEBUG_SV.logmsg(l_module_name,'new_assignment_failed exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1583 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:new_assignment_failed');
1584 END IF;
1585 WHEN freight_cost_processing_error THEN
1586 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1587 IF l_debug_on THEN
1588 WSH_DEBUG_SV.logmsg(l_module_name,'freight_cost_processing_error exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1589 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:freight_cost_processing_error');
1590 END IF;
1591 WHEN Others THEN
1592 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1593 IF l_debug_on THEN
1594 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
1595 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1596 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1597 END IF;
1598 END Process_Non_Splits;
1599
1600 PROCEDURE process_splits(
1601 p_delivery_interface_id IN NUMBER,
1602 p_delivery_id IN NUMBER,
1603 x_return_status OUT NOCOPY VARCHAR2
1604 ) IS
1605 -- local variables
1606 l_new_split_detail_id NUMBER;
1607 l_base_req_qty NUMBER;
1608 l_return_status VARCHAR2(30);
1609 l_delivery_id NUMBER;
1610 l_det_freight_costs NUMBER;
1611 l_ser_count NUMBER := 0;
1612 l_det_index NUMBER := 0;
1613 l_add_flag VARCHAR2(1) := 'T';
1614 l_total_req_qty NUMBER := 0;
1615 l_total_shp_qty NUMBER := 0;
1616 l_total_cc_qty NUMBER := 0;
1617 l_dd_count NUMBER := 0;
1618 l_src_line_count NUMBER := 0;
1619 l_prev_int_rec del_det_int_cur%ROWTYPE;
1620 l_number_of_errors NUMBER := 0;
1621 l_number_of_warnings NUMBER := 0;
1622 l_num_of_dtl NUMBER := 0;
1623 l_serial_range_tab WSH_GLBL_VAR_STRCT_GRP.ddSerialRangeTabType;
1624 l_index NUMBER;
1625 -- TPW - Distributed changes
1626 l_detail_id NUMBER;
1627
1628 --Cursors
1629 --TPW - Distributed changes
1630 CURSOR c_det_count_cur(p_header_number NUMBER, p_detail_id NUMBER, p_dlvy_int_id NUMBER) IS
1631 SELECT COUNT(*)
1632 FROM wsh_del_details_interface wddi, wsh_del_assgn_interface wdai
1633 WHERE wddi.delivery_Detail_interface_id = wdai.delivery_detail_interface_id
1634 AND wdai.delivery_interface_id = p_dlvy_int_id
1635 AND wddi.delivery_detail_id=p_detail_id
1636 AND nvl(wddi.source_header_number,'-99') = nvl(p_header_number,'-99')
1637 AND WDAI.INTERFACE_ACTION_CODE = '94X_INBOUND'
1638 AND WDDI.INTERFACE_ACTION_CODE = '94X_INBOUND';
1639
1640 -- TPW - Distributed changes
1641 -- public api variables
1642 l_init_msg_list VARCHAR2(30) := NULL;
1643 l_msg_count NUMBER;
1644 l_msg_data VARCHAR2(3000);
1645 l_commit VARCHAR2(1);
1646 l_validation_level NUMBER;
1647 l_pending_req_qty NUMBER;
1648 l_pending_shp_qty NUMBER;
1649 l_curr_index NUMBER;
1650
1651 l_serial_number_control NUMBER;
1652 l_detail_id_tab wsh_util_core.id_tab_type;
1653 l_detail_qty_tab wsh_util_core.id_tab_type;
1654 l_new_detail_ids wsh_util_core.id_tab_type;
1655 l_frt_detail_intf_tab wsh_util_core.id_tab_type;
1656 l_frt_detail_tab wsh_util_core.id_tab_type;
1657 new_assignment_failed exception;
1658 -- TPW - Distributed changes
1659
1660 --exceptions
1661 invalid_input EXCEPTION;
1662 freight_cost_processing_error EXCEPTION;
1663 --
1664 l_debug_on BOOLEAN;
1665 --
1666 l_module_name CONSTANT VARCHAR2(100)
1667 := 'wsh.plsql.' || g_pkg_name || '.' || 'PROCESS_SPLITS';
1668 --
1669 BEGIN
1670 --
1671 l_debug_on := wsh_debug_interface.g_debug;
1672
1673 --
1674 IF l_debug_on IS NULL THEN
1675 l_debug_on := wsh_debug_sv.is_debug_enabled;
1676 END IF;
1677
1678 --
1679 IF l_debug_on THEN
1680 wsh_debug_sv.push(l_module_name, 'Process_Splits');
1681 wsh_debug_sv.LOG(l_module_name, 'Delivery Interface Id',
1682 p_delivery_interface_id);
1683 wsh_debug_sv.LOG(l_module_name, 'Delivery Id', p_delivery_id);
1684 END IF;
1685
1686 x_return_status := wsh_util_core.g_ret_sts_success;
1687
1688 IF (p_delivery_interface_id IS NULL) THEN
1689 RAISE invalid_input;
1690 END IF;
1691
1692 IF (p_delivery_id IS NOT NULL) THEN
1693 l_delivery_id := p_delivery_id;
1694 ELSE
1695 RAISE invalid_input;
1696 END IF;
1697
1698 /* New Logic for splitting at supplier instance 945 inbound. kvenkate.
1699 For a given delivery_detail_id ,
1700 if more than one record exists in wsh_del_details_interface, then
1701 if those records have the same not null source_line_id, then do not split.
1702 if those records have distinct source_line_id, then split. */
1703
1704 -- TPW - Distributed changes
1705 FOR del_det_int_rec IN del_det_int_cur(NULL, NULL, p_delivery_interface_id) LOOP --{
1706 IF l_debug_on THEN
1707 wsh_debug_sv.log(l_module_name, '*** Current Delivery_Detail_Id', del_det_int_rec.delivery_detail_id);
1708 wsh_debug_sv.log(l_module_name, 'Current del.detail_int_id', del_det_int_rec.delivery_detail_interface_id);
1709 wsh_debug_sv.log(l_module_name, 'Req Qty '||del_det_int_rec.requested_quantity||' Shp Qty '||
1710 del_det_int_rec.shipped_quantity||' Cyc Qty '||del_det_int_rec.cycle_count_quantity);
1711 wsh_debug_sv.log(l_module_name, 'Previous Delivery_detail_id', l_prev_int_rec.delivery_detail_id);
1712 wsh_debug_sv.log(l_module_name, 'Previous del.detail_int_id', l_prev_int_rec.delivery_detail_interface_id);
1713 wsh_debug_sv.logmsg(l_module_name, 'l_src_line_count '||l_src_line_count||' l_dd_count '||l_dd_count);
1714 wsh_debug_sv.logmsg(l_module_name, 'Total req qty '|| l_total_req_qty ||' Total shp qty '||l_total_shp_qty||
1715 ' Total cc qty '||l_total_cc_qty);
1716 wsh_debug_sv.log(l_module_name, 'l_detail_id_tab.COUNT', l_detail_id_tab.COUNT);
1717 END IF;
1718
1719 -- TPW - Distributed changes
1720 IF ((NVL(l_prev_int_rec.source_header_number, '-9999') = NVL(del_det_int_rec.source_header_number,'-9999')) AND
1721 (NVL(l_prev_int_rec.delivery_detail_id, '-9999') = del_det_int_rec.delivery_detail_id)) THEN --{
1722 if l_debug_on then
1723 wsh_debug_sv.logmsg(l_module_name, 'Same Delivery Detail');
1724 end if;
1725 l_dd_count := l_dd_count + 1;
1726
1727 IF NVL(l_prev_int_rec.source_line_id, '-9999') =
1728 del_det_int_rec.source_line_id THEN
1729 if l_debug_on then
1730 wsh_debug_sv.logmsg(l_module_name, 'Same Source Line');
1731 end if;
1732 ELSE --} {
1733
1734 -- TPW - Distributed changes
1735 l_pending_req_qty := l_total_req_qty;
1736 l_pending_shp_qty := l_total_shp_qty;
1737
1738 WHILE (l_pending_req_qty > 0) LOOP --{
1739
1740 if l_debug_on then
1741 wsh_debug_sv.logmsg(l_module_name, '*** 1 **** l_pending_req_qty '||l_pending_req_qty||' l_pending_shp_qty '||l_pending_shp_qty);
1742 wsh_debug_sv.logmsg(l_module_name, ' l_curr_index '||l_curr_index||' l_detail_id_tab(l_curr_index) '||l_detail_id_tab(l_curr_index));
1743 end if;
1744
1745 IF (l_pending_req_qty <= l_detail_qty_tab(l_curr_index)) THEN --{
1746
1747
1748 l_detail_qty_tab(l_curr_index) := l_detail_qty_tab(l_curr_index) - l_pending_req_qty;
1749
1750 -- split dd
1751 split_delivery_detail(
1752 p_delivery_detail_id => l_detail_id_tab(l_curr_index),
1753 p_qty_to_split => l_pending_req_qty,
1754 x_new_detail_id => l_new_split_detail_id,
1755 x_return_status => l_return_status);
1756
1757 wsh_util_core.api_post_call(p_return_status => l_return_status,
1758 x_num_warnings => l_number_of_warnings,
1759 x_num_errors => l_number_of_errors);
1760
1761 -- For the newly created base delivery detail, make updates
1762 -- Add the record with new detail id to the global table
1763 if l_debug_on then
1764 wsh_debug_sv.log(l_module_name, 'Splitted '||l_new_split_detail_id ||' from '|| l_detail_id_tab(l_curr_index) || ' for quantity '||l_pending_req_qty);
1765 end if;
1766
1767 If l_new_split_detail_id IS NOT NULL THEN
1768 l_prev_int_rec.delivery_detail_id := l_new_split_detail_id;
1769 l_index := l_serial_range_tab.first;
1770 while l_index is not null loop
1771 l_serial_range_tab(l_index).delivery_detail_id := l_new_split_detail_id;
1772 l_index := l_serial_range_tab.next(l_index);
1773 end loop;
1774 Else
1775 l_prev_int_rec.delivery_detail_id := l_detail_id_tab(l_curr_index);
1776 end if;
1777
1778 --
1779 l_prev_int_rec.requested_quantity := l_pending_req_qty;
1780 l_prev_int_rec.shipped_quantity := l_pending_shp_qty;
1781 l_pending_req_qty := 0;
1782
1783 ELSE --} {
1784
1785 l_prev_int_rec.delivery_detail_id := l_detail_id_tab(l_curr_index);
1786 l_prev_int_rec.requested_quantity := l_detail_qty_tab(l_curr_index);
1787 IF l_pending_shp_qty is not null THEN
1788 l_prev_int_rec.shipped_quantity := l_detail_qty_tab(l_curr_index);
1789 END IF;
1790 l_pending_req_qty := l_pending_req_qty - l_detail_qty_tab(l_curr_index);
1791 l_pending_shp_qty := l_pending_shp_qty - l_detail_qty_tab(l_curr_index);
1792
1793 l_curr_index := l_curr_index + 1;
1794
1795 END IF; --}
1796
1797 IF (nvl(WSH_INBOUND_SHIP_ADVICE_PKG.G_WAREHOUSE_TYPE, '!') = 'TPW') THEN
1798 l_prev_int_rec.cycle_count_quantity := l_total_cc_qty;
1799 ELSE
1800 l_prev_int_rec.cycle_count_quantity := null;
1801 END IF;
1802 --
1803
1804 add_to_update_table(
1805 p_del_det_int_rec => l_prev_int_rec,
1806 p_update_mode => 'UPDATE',
1807 p_delivery_id => l_delivery_id,
1808 x_return_status => l_return_status);
1809
1810 IF (l_return_status <> wsh_util_core.g_ret_sts_success) THEN
1811 RAISE fnd_api.g_exc_error;
1812 END IF;
1813
1814
1815 IF (nvl(WSH_INBOUND_SHIP_ADVICE_PKG.G_WAREHOUSE_TYPE, '!') = 'TW2') THEN
1816 l_new_detail_ids(l_new_detail_ids.COUNT+1) := l_prev_int_rec.delivery_detail_id;
1817 END IF;
1818
1819 END LOOP; --}
1820 --
1821 -- g_serial_range_tab := g_serial_range_tab + l_serial_range_tab;
1822 add_to_serial_table(p_serial_range_tab => l_serial_range_tab);
1823 l_serial_range_tab.delete;
1824 l_total_req_qty := 0;
1825 l_total_shp_qty := 0;
1826 l_total_cc_qty := 0;
1827 l_src_line_count := l_src_line_count + 1;
1828
1829 l_frt_detail_intf_tab (l_frt_detail_intf_tab.COUNT+1) := l_prev_int_rec.delivery_detail_interface_id;
1830 l_frt_detail_tab (l_frt_detail_tab.COUNT+1) := l_prev_int_rec.delivery_detail_id;
1831 END IF; --}
1832
1833 ELSE --} {
1834
1835
1836 IF (l_prev_int_rec.container_flag = 'N') THEN --{
1837
1838 IF l_src_line_count > 1 --count(distinct sn) for prev. dd id)
1839 THEN
1840 -- TPW - Distributed changes
1841 l_pending_req_qty := l_total_req_qty;
1842 l_pending_shp_qty := l_total_shp_qty;
1843
1844 WHILE (l_pending_req_qty > 0) LOOP --{
1845
1846 if l_debug_on then
1847 wsh_debug_sv.logmsg(l_module_name, '*** 2 **** l_pending_req_qty '||l_pending_req_qty||' l_pending_shp_qty '||l_pending_shp_qty);
1848 wsh_debug_sv.logmsg(l_module_name, ' l_curr_index '||l_curr_index||' l_detail_id_tab(l_curr_index) '||l_detail_id_tab(l_curr_index));
1849 end if;
1850
1851 IF (l_pending_req_qty <= l_detail_qty_tab(l_curr_index)) THEN --{
1852
1853
1854 l_detail_qty_tab(l_curr_index) := l_detail_qty_tab(l_curr_index) - l_pending_req_qty;
1855
1856 -- split dd
1857 --split_delivery_detail(p_delivery_detail_id => del_det_int_rec.delivery_detail_id,
1858 split_delivery_detail(
1859 p_delivery_detail_id => l_detail_id_tab(l_curr_index),
1860 p_qty_to_split => l_pending_req_qty,
1861 x_new_detail_id => l_new_split_detail_id,
1862 x_return_status => l_return_status);
1863
1864 wsh_util_core.api_post_call(p_return_status => l_return_status,
1865 x_num_warnings => l_number_of_warnings,
1866 x_num_errors => l_number_of_errors);
1867
1868 -- For the newly created base delivery detail, make updates
1869 -- Add the record with new detail id to the global table
1870 if l_debug_on then
1871 wsh_debug_sv.log(l_module_name, 'Splitted '||l_new_split_detail_id ||' from '|| l_detail_id_tab(l_curr_index) || ' for quantity '||l_pending_req_qty);
1872 end if;
1873
1874 If l_new_split_detail_id IS NOT NULL THEN
1875 l_prev_int_rec.delivery_detail_id := l_new_split_detail_id;
1876 l_index := l_serial_range_tab.first;
1877 while l_index is not null loop
1878 l_serial_range_tab(l_index).delivery_detail_id := l_new_split_detail_id;
1879 l_index := l_serial_range_tab.next(l_index);
1880 end loop;
1881 Else
1882 l_prev_int_rec.delivery_detail_id := l_detail_id_tab(l_curr_index);
1883 end if;
1884
1885 --
1886 l_prev_int_rec.requested_quantity := l_pending_req_qty;
1887 l_prev_int_rec.shipped_quantity := l_pending_shp_qty;
1888 l_pending_req_qty := 0;
1889
1890 ELSE
1891
1892 l_prev_int_rec.delivery_detail_id := l_detail_id_tab(l_curr_index);
1893 l_prev_int_rec.requested_quantity := l_detail_qty_tab(l_curr_index);
1894 IF l_pending_shp_qty is not null THEN
1895 l_prev_int_rec.shipped_quantity := l_detail_qty_tab(l_curr_index);
1896 END IF;
1897 l_pending_req_qty := l_pending_req_qty - l_detail_qty_tab(l_curr_index);
1898 l_pending_shp_qty := l_pending_shp_qty - l_detail_qty_tab(l_curr_index);
1899
1900 l_curr_index := l_curr_index + 1;
1901
1902 END IF; --}
1903
1904 IF (nvl(WSH_INBOUND_SHIP_ADVICE_PKG.G_WAREHOUSE_TYPE, '!') = 'TPW') THEN
1905 l_prev_int_rec.cycle_count_quantity := l_total_cc_qty;
1906 ELSE
1907 l_prev_int_rec.cycle_count_quantity := null;
1908 END IF;
1909 --
1910
1911 add_to_update_table(
1912 p_del_det_int_rec => l_prev_int_rec,
1913 p_update_mode => 'UPDATE',
1914 p_delivery_id => l_delivery_id,
1915 x_return_status => l_return_status);
1916
1917 IF (l_return_status <> wsh_util_core.g_ret_sts_success) THEN
1918 RAISE fnd_api.g_exc_error;
1919 END IF;
1920
1921
1922 IF (nvl(WSH_INBOUND_SHIP_ADVICE_PKG.G_WAREHOUSE_TYPE, '!') = 'TW2') THEN
1923 l_new_detail_ids(l_new_detail_ids.COUNT+1) := l_prev_int_rec.delivery_detail_id;
1924 END IF;
1925
1926 END LOOP; --}
1927
1928 add_to_serial_table(p_serial_range_tab => l_serial_range_tab);
1929 l_serial_range_tab.delete;
1930
1931 l_frt_detail_intf_tab (l_frt_detail_intf_tab.COUNT+1) := l_prev_int_rec.delivery_detail_interface_id;
1932 l_frt_detail_tab (l_frt_detail_tab.COUNT+1) := l_prev_int_rec.delivery_detail_id;
1933
1934 l_detail_id_tab.DELETE;
1935
1936 ELSIF l_dd_count > 1 -- count(prev dd id) > 1
1937 THEN
1938 l_pending_req_qty := l_total_req_qty;
1939 l_pending_shp_qty := l_total_shp_qty;
1940
1941 WHILE (l_pending_req_qty > 0) LOOP --{
1942
1943 if l_debug_on then
1944 wsh_debug_sv.logmsg(l_module_name, '*** 3 **** l_pending_req_qty '||l_pending_req_qty||' l_pending_shp_qty '||l_pending_shp_qty);
1945 wsh_debug_sv.logmsg(l_module_name, ' l_curr_index '||l_curr_index||' l_detail_id_tab(l_curr_index) '||l_detail_id_tab(l_curr_index));
1946 end if;
1947
1948 IF (l_pending_req_qty <= l_detail_qty_tab(l_curr_index)) THEN --{
1949
1950
1951 l_detail_qty_tab(l_curr_index) := l_detail_qty_tab(l_curr_index) - l_pending_req_qty;
1952
1953 -- split dd
1954 --split_delivery_detail(p_delivery_detail_id => del_det_int_rec.delivery_detail_id,
1955 split_delivery_detail(
1956 p_delivery_detail_id => l_detail_id_tab(l_curr_index),
1957 p_qty_to_split => l_pending_req_qty,
1958 x_new_detail_id => l_new_split_detail_id,
1959 x_return_status => l_return_status);
1960
1961 wsh_util_core.api_post_call(p_return_status => l_return_status,
1962 x_num_warnings => l_number_of_warnings,
1963 x_num_errors => l_number_of_errors);
1964
1965 -- For the newly created base delivery detail, make updates
1966 -- Add the record with new detail id to the global table
1967 if l_debug_on then
1968 wsh_debug_sv.log(l_module_name, 'Splitted '||l_new_split_detail_id ||' from '|| l_detail_id_tab(l_curr_index) || ' for quantity '||l_pending_req_qty);
1969 end if;
1970
1971 If l_new_split_detail_id IS NOT NULL THEN
1972 l_prev_int_rec.delivery_detail_id := l_new_split_detail_id;
1973 l_index := l_serial_range_tab.first;
1974 while l_index is not null loop
1975 l_serial_range_tab(l_index).delivery_detail_id := l_new_split_detail_id;
1976 l_index := l_serial_range_tab.next(l_index);
1977 end loop;
1978 Else
1979 l_prev_int_rec.delivery_detail_id := l_detail_id_tab(l_curr_index);
1980 end if;
1981
1982 --
1983 l_prev_int_rec.requested_quantity := l_pending_req_qty;
1984 l_prev_int_rec.shipped_quantity := l_pending_shp_qty;
1985 l_pending_req_qty := 0;
1986
1987 ELSE
1988
1989 l_prev_int_rec.delivery_detail_id := l_detail_id_tab(l_curr_index);
1990 l_prev_int_rec.requested_quantity := l_detail_qty_tab(l_curr_index);
1991 IF l_pending_shp_qty is not null THEN
1992 l_prev_int_rec.shipped_quantity := l_detail_qty_tab(l_curr_index);
1993 END IF;
1994 l_pending_req_qty := l_pending_req_qty - l_detail_qty_tab(l_curr_index);
1995 l_pending_shp_qty := l_pending_shp_qty - l_detail_qty_tab(l_curr_index);
1996
1997 l_curr_index := l_curr_index + 1;
1998
1999 END IF; --}
2000
2001 IF (nvl(WSH_INBOUND_SHIP_ADVICE_PKG.G_WAREHOUSE_TYPE, '!') = 'TPW') THEN
2002 l_prev_int_rec.cycle_count_quantity := l_total_cc_qty;
2003 ELSE
2004 l_prev_int_rec.cycle_count_quantity := null;
2005 END IF;
2006 --
2007
2008 add_to_update_table(
2009 p_del_det_int_rec => l_prev_int_rec,
2010 p_update_mode => 'UPDATE',
2011 p_delivery_id => l_delivery_id,
2012 x_return_status => l_return_status);
2013
2014 IF (l_return_status <> wsh_util_core.g_ret_sts_success) THEN
2015 RAISE fnd_api.g_exc_error;
2016 END IF;
2017
2018
2019 IF (nvl(WSH_INBOUND_SHIP_ADVICE_PKG.G_WAREHOUSE_TYPE, '!') = 'TW2') THEN
2020 l_new_detail_ids(l_new_detail_ids.COUNT+1) := l_prev_int_rec.delivery_detail_id;
2021 END IF;
2022
2023 END LOOP; --}
2024 --
2025 -- g_serial_range_tab := g_serial_range_tab + l_serial_range_tab;
2026 add_to_serial_table(p_serial_range_tab => l_serial_range_tab);
2027 l_serial_range_tab.delete;
2028
2029 -- TPW - Distributed changes
2030 l_frt_detail_intf_tab (l_frt_detail_intf_tab.COUNT+1) := l_prev_int_rec.delivery_detail_interface_id;
2031 l_frt_detail_tab (l_frt_detail_tab.COUNT+1) := l_prev_int_rec.delivery_detail_id;
2032
2033 l_detail_id_tab.DELETE;
2034
2035 END IF;
2036 END IF; --}
2037
2038 l_total_req_qty := 0;
2039 l_total_shp_qty := 0;
2040 l_total_cc_qty := 0;
2041 --
2042 l_dd_count := 1;
2043 l_src_line_count := 1;
2044
2045 -- TPW - Distributed changes
2046 OPEN c_det_count_cur(del_det_int_rec.source_header_number, del_det_int_rec.delivery_detail_id, p_delivery_interface_id);
2047 FETCH c_det_count_cur INTO l_num_of_dtl;
2048 CLOSE c_det_count_cur;
2049 if l_debug_on THEN
2050 wsh_debug_sv.log(l_module_name, 'l_num_of_dtl', l_num_of_dtl);
2051 end if;
2052
2053 -- TPW - Distributed changes
2054 if (del_det_int_rec.container_flag = 'N') THEN
2055
2056 IF (nvl(WSH_INBOUND_SHIP_ADVICE_PKG.G_WAREHOUSE_TYPE, '!') = 'TW2') THEN
2057
2058 IF l_debug_on THEN
2059 wsh_debug_sv.logmsg(l_module_name, 'Line Direction '||del_det_int_rec.line_direction||
2060 ' SHN '||del_det_int_rec.source_header_number||
2061 ' Intf DDID '||del_det_int_rec.delivery_detail_id);
2062 END IF;
2063
2064 IF (del_det_int_rec.line_direction = 'IO') THEN
2065 select wdd.delivery_detail_id, wdd.requested_quantity BULK COLLECT
2066 into l_detail_id_tab, l_detail_qty_tab
2067 from wsh_delivery_details wdd,
2068 oe_order_lines_all ol,
2069 po_requisition_lines_all pl,
2070 po_requisition_headers_all ph
2071 where wdd.source_code = 'OE'
2072 and wdd.released_status in ('R','B','X')
2073 and wdd.source_line_id = ol.line_id
2074 and ol.source_document_line_id = pl.requisition_line_id
2075 and ol.source_document_id = pl.requisition_header_id
2076 and pl.requisition_header_id = ph.requisition_header_id
2077 and pl.line_num = del_det_int_rec.delivery_detail_id
2078 and ph.segment1 = del_det_int_rec.source_header_number;
2079 ELSE
2080 select wdd.delivery_detail_id, wdd.requested_quantity BULK COLLECT
2081 into l_detail_id_tab, l_detail_qty_tab
2082 from wsh_delivery_details wdd,
2083 wsh_shipment_batches wsb,
2084 wsh_transactions_history wth
2085 where wdd.source_code = 'OE'
2086 and wdd.released_status in ('R','B','X')
2087 and wdd.shipment_batch_id = wsb.batch_id
2088 and wdd.shipment_line_number = del_det_int_rec.delivery_detail_id
2089 and wsb.name = wth.entity_number
2090 and wth.entity_type = 'BATCH'
2091 and wth.document_direction = 'O'
2092 and wth.document_type = 'SR'
2093 and wth.document_number = del_det_int_rec.source_header_number;
2094 END IF;
2095
2096 IF l_debug_on THEN
2097 FOR i in 1..l_detail_id_tab.COUNT LOOP
2098 wsh_debug_sv.logmsg(l_module_name,'DD-id '||l_detail_id_tab(i)||' Req Qty '||l_detail_qty_tab(i));
2099 END LOOP;
2100 END IF;
2101
2102 ELSE
2103 l_detail_id_tab(1) := del_det_int_rec.delivery_detail_id;
2104
2105 select requested_quantity
2106 into l_detail_qty_tab(1)
2107 from wsh_delivery_details
2108 where delivery_detail_id = del_det_int_rec.delivery_detail_id;
2109 END IF;
2110
2111 l_curr_index := 1;
2112 else
2113 l_num_of_dtl := 1;
2114 end if;
2115
2116 IF l_num_of_dtl = 1 THEN
2117 l_serial_range_tab.delete;
2118 goto detail_loop_end;
2119 END IF;
2120
2121 END IF; --}
2122
2123 l_total_req_qty := l_total_req_qty + del_det_int_rec.requested_quantity;
2124 l_total_shp_qty := l_total_shp_qty + del_det_int_rec.shipped_quantity;
2125 IF (nvl(WSH_INBOUND_SHIP_ADVICE_PKG.G_WAREHOUSE_TYPE, '!') = 'TPW') THEN
2126 l_total_cc_qty := l_total_cc_qty + del_det_int_rec.cycle_count_quantity;
2127 ELSE
2128 l_total_cc_qty := null;
2129 END IF;
2130
2131 --
2132 IF del_det_int_rec.serial_number IS NOT NULL THEN
2133 -- { If Organization is TW2 then check if item is serial controlled - Start
2134 IF (nvl(WSH_INBOUND_SHIP_ADVICE_PKG.G_WAREHOUSE_TYPE, '!') = 'TW2') THEN
2135 IF l_debug_on THEN
2136 wsh_debug_sv.log(l_module_name,'PS: del_det_int_rec.inventory_item_id', del_det_int_rec.inventory_item_id);
2137 wsh_debug_sv.log(l_module_name,'PS: del_det_int_rec.organization_id', del_det_int_rec.organization_id);
2138 wsh_debug_sv.log(l_module_name,'PS: del_det_int_rec.serial_number', del_det_int_rec.serial_number);
2139 wsh_debug_sv.log(l_module_name,'PS: del_det_int_rec.to_serial_number', del_det_int_rec.to_serial_number);
2140 END IF;
2141
2142 BEGIN
2143 SELECT serial_number_control_code
2144 INTO l_serial_number_control
2145 FROM mtl_system_items
2146 WHERE inventory_item_id = del_det_int_rec.inventory_item_id
2147 AND organization_id = del_det_int_rec.organization_id;
2148
2149 IF l_debug_on THEN
2150 wsh_debug_sv.log(l_module_name,'PS: l_serial_number_control', l_serial_number_control);
2151 END IF;
2152
2153 EXCEPTION
2154 WHEN NO_DATA_FOUND THEN
2155 IF l_debug_on THEN
2156 wsh_debug_sv.logmsg(l_module_name,'PS: Inside No-Data-Found');
2157 END IF;
2158 END;
2159 ELSE -- Organization is TPW Enabled
2160 l_serial_number_control := 2;
2161 END IF;
2162 -- } If Organization is TW2 then check if item is serial controlled - End
2163
2164 -- TPW - Distributed changes
2165 IF (l_detail_id_tab.COUNT = 1) THEN --{
2166 IF l_serial_number_control <> 1 THEN
2167 IF l_debug_on THEN
2168 wsh_debug_sv.logmsg(l_module_name, 'Adding serial number to serial table');
2169 END IF;
2170
2171 l_ser_count := l_serial_range_tab.COUNT;
2172 l_serial_range_tab(l_ser_count + 1).delivery_detail_id :=
2173 l_detail_id_tab(1);
2174 l_serial_range_tab(l_ser_count + 1).from_serial_number :=
2175 del_det_int_rec.serial_number;
2176 l_serial_range_tab(l_ser_count + 1).to_serial_number :=
2177 del_det_int_rec.to_serial_number;
2178 l_serial_range_tab(l_ser_count + 1).quantity :=
2179 del_det_int_rec.shipped_quantity;
2180 END IF;
2181
2182 -- Since serial numbers added to serial table, should not be added in the update record
2183 del_det_int_rec.serial_number := NULL;
2184 del_det_int_rec.to_serial_number := NULL;
2185 ELSE
2186 -- Not Supported
2187 IF l_debug_on THEN
2188 wsh_debug_sv.logmsg(l_module_name,'Serial types not supported for remnant models');
2189 END IF;
2190 raise fnd_api.g_exc_error;
2191 END IF; --}
2192 END IF;
2193
2194 <<detail_loop_end>>
2195 l_prev_int_rec := del_det_int_rec;
2196
2197 END LOOP; -- for del_det_int_rec in cursor }
2198
2199 IF l_debug_on THEN
2200 wsh_debug_sv.logmsg(l_module_name, '*** Outside the Loop ***');
2201 wsh_debug_sv.logmsg(l_module_name, 'l_src_line_count '|| l_src_line_count||' l_dd_count '||l_dd_count);
2202 wsh_debug_sv.logmsg(l_module_name, 'Total req qty '|| l_total_req_qty||' Total shp qty '||l_total_shp_qty||' Total cc qty '||l_total_cc_qty);
2203 END IF;
2204
2205 IF (l_prev_int_rec.container_flag = 'N') THEN --{
2206 IF l_src_line_count > 1 THEN
2207
2208 -- TPW - Distributed changes
2209 l_pending_req_qty := l_total_req_qty;
2210 l_pending_shp_qty := l_total_shp_qty;
2211
2212 WHILE (l_pending_req_qty > 0) LOOP --{
2213
2214 if l_debug_on then
2215 wsh_debug_sv.logmsg(l_module_name, '*** 4 **** l_pending_req_qty '||l_pending_req_qty||' l_pending_shp_qty '||l_pending_shp_qty);
2216 wsh_debug_sv.logmsg(l_module_name, ' l_curr_index '||l_curr_index||' l_detail_id_tab(l_curr_index) '||l_detail_id_tab(l_curr_index));
2217 end if;
2218
2219 IF (l_pending_req_qty <= l_detail_qty_tab(l_curr_index)) THEN --{
2220
2221
2222 l_detail_qty_tab(l_curr_index) := l_detail_qty_tab(l_curr_index) - l_pending_req_qty;
2223
2224 -- split dd
2225 --split_delivery_detail(p_delivery_detail_id => del_det_int_rec.delivery_detail_id,
2226 split_delivery_detail(
2227 p_delivery_detail_id => l_detail_id_tab(l_curr_index),
2228 p_qty_to_split => l_pending_req_qty,
2229 x_new_detail_id => l_new_split_detail_id,
2230 x_return_status => l_return_status);
2231
2232 wsh_util_core.api_post_call(p_return_status => l_return_status,
2233 x_num_warnings => l_number_of_warnings,
2234 x_num_errors => l_number_of_errors);
2235
2236 -- For the newly created base delivery detail, make updates
2237 -- Add the record with new detail id to the global table
2238 if l_debug_on then
2239 wsh_debug_sv.log(l_module_name, 'Splitted '||l_new_split_detail_id ||' from '|| l_detail_id_tab(l_curr_index) || ' for quantity '||l_pending_req_qty);
2240 end if;
2241
2242 If l_new_split_detail_id IS NOT NULL THEN
2243 l_prev_int_rec.delivery_detail_id := l_new_split_detail_id;
2244 l_index := l_serial_range_tab.first;
2245 while l_index is not null loop
2246 l_serial_range_tab(l_index).delivery_detail_id := l_new_split_detail_id;
2247 l_index := l_serial_range_tab.next(l_index);
2248 end loop;
2249 Else
2250 l_prev_int_rec.delivery_detail_id := l_detail_id_tab(l_curr_index);
2251 end if;
2252
2253 --
2254 l_prev_int_rec.requested_quantity := l_pending_req_qty;
2255 l_prev_int_rec.shipped_quantity := l_pending_shp_qty;
2256 l_pending_req_qty := 0;
2257
2258 ELSE --} {
2259
2260 l_prev_int_rec.delivery_detail_id := l_detail_id_tab(l_curr_index);
2261 l_prev_int_rec.requested_quantity := l_detail_qty_tab(l_curr_index);
2262 IF l_pending_shp_qty is not null THEN
2263 l_prev_int_rec.shipped_quantity := l_detail_qty_tab(l_curr_index);
2264 END IF;
2265 l_pending_req_qty := l_pending_req_qty - l_detail_qty_tab(l_curr_index);
2266 l_pending_shp_qty := l_pending_shp_qty - l_detail_qty_tab(l_curr_index);
2267
2268 l_curr_index := l_curr_index + 1;
2269
2270 END IF; --}
2271
2272 IF (nvl(WSH_INBOUND_SHIP_ADVICE_PKG.G_WAREHOUSE_TYPE, '!') = 'TPW') THEN
2273 l_prev_int_rec.cycle_count_quantity := l_total_cc_qty;
2274 ELSE
2275 l_prev_int_rec.cycle_count_quantity := null;
2276 END IF;
2277 --
2278
2279 add_to_update_table(
2280 p_del_det_int_rec => l_prev_int_rec,
2281 p_update_mode => 'UPDATE',
2282 p_delivery_id => l_delivery_id,
2283 x_return_status => l_return_status);
2284
2285 IF (l_return_status <> wsh_util_core.g_ret_sts_success) THEN
2286 RAISE fnd_api.g_exc_error;
2287 END IF;
2288
2289
2290 IF (nvl(WSH_INBOUND_SHIP_ADVICE_PKG.G_WAREHOUSE_TYPE, '!') = 'TW2') THEN
2291 l_new_detail_ids(l_new_detail_ids.COUNT+1) := l_prev_int_rec.delivery_detail_id;
2292 END IF;
2293
2294 END LOOP; --}
2295
2296 add_to_serial_table(p_serial_range_tab => l_serial_range_tab);
2297 l_serial_range_tab.delete;
2298
2299 l_frt_detail_intf_tab (l_frt_detail_intf_tab.COUNT+1) := l_prev_int_rec.delivery_detail_interface_id;
2300 l_frt_detail_tab (l_frt_detail_tab.COUNT+1) := l_prev_int_rec.delivery_detail_id;
2301
2302 l_detail_id_tab.DELETE;
2303
2304 ELSIF l_dd_count > 1 THEN
2305
2306 l_pending_req_qty := l_total_req_qty;
2307 l_pending_shp_qty := l_total_shp_qty;
2308
2309 WHILE (l_pending_req_qty > 0) LOOP --{
2310
2311 if l_debug_on then
2312 wsh_debug_sv.logmsg(l_module_name, '*** 5 **** l_pending_req_qty '||l_pending_req_qty||' l_pending_shp_qty '||l_pending_shp_qty);
2313 wsh_debug_sv.logmsg(l_module_name, ' l_curr_index '||l_curr_index||' l_detail_id_tab(l_curr_index) '||l_detail_id_tab(l_curr_index));
2314 end if;
2315
2316 IF (l_pending_req_qty <= l_detail_qty_tab(l_curr_index)) THEN --{
2317
2318
2319 l_detail_qty_tab(l_curr_index) := l_detail_qty_tab(l_curr_index) - l_pending_req_qty;
2320
2321 -- split dd
2322 --split_delivery_detail(p_delivery_detail_id => del_det_int_rec.delivery_detail_id,
2323 split_delivery_detail(
2324 p_delivery_detail_id => l_detail_id_tab(l_curr_index),
2325 p_qty_to_split => l_pending_req_qty,
2326 x_new_detail_id => l_new_split_detail_id,
2327 x_return_status => l_return_status);
2328
2329 wsh_util_core.api_post_call(p_return_status => l_return_status,
2330 x_num_warnings => l_number_of_warnings,
2331 x_num_errors => l_number_of_errors);
2332
2333 -- For the newly created base delivery detail, make updates
2334 -- Add the record with new detail id to the global table
2335 if l_debug_on then
2336 wsh_debug_sv.log(l_module_name, 'Splitted '||l_new_split_detail_id ||' from '|| l_detail_id_tab(l_curr_index) || ' for quantity '||l_pending_req_qty);
2337 end if;
2338
2339 If l_new_split_detail_id IS NOT NULL THEN
2340 l_prev_int_rec.delivery_detail_id := l_new_split_detail_id;
2341 l_index := l_serial_range_tab.first;
2342 while l_index is not null loop
2343 l_serial_range_tab(l_index).delivery_detail_id := l_new_split_detail_id;
2344 l_index := l_serial_range_tab.next(l_index);
2345 end loop;
2346 Else
2347 l_prev_int_rec.delivery_detail_id := l_detail_id_tab(l_curr_index);
2348 end if;
2349
2350 --
2351 l_prev_int_rec.requested_quantity := l_pending_req_qty;
2352 l_prev_int_rec.shipped_quantity := l_pending_shp_qty;
2353 l_pending_req_qty := 0;
2354
2355 ELSE --} {
2356
2357 l_prev_int_rec.delivery_detail_id := l_detail_id_tab(l_curr_index);
2358 l_prev_int_rec.requested_quantity := l_detail_qty_tab(l_curr_index);
2359 IF l_pending_shp_qty is not null THEN
2360 l_prev_int_rec.shipped_quantity := l_detail_qty_tab(l_curr_index);
2361 END IF;
2362 l_pending_req_qty := l_pending_req_qty - l_detail_qty_tab(l_curr_index);
2363 l_pending_shp_qty := l_pending_shp_qty - l_detail_qty_tab(l_curr_index);
2364
2365 l_curr_index := l_curr_index + 1;
2366
2367 END IF; --}
2368
2369 IF (nvl(WSH_INBOUND_SHIP_ADVICE_PKG.G_WAREHOUSE_TYPE, '!') = 'TPW') THEN
2370 l_prev_int_rec.cycle_count_quantity := l_total_cc_qty;
2371 ELSE
2372 l_prev_int_rec.cycle_count_quantity := null;
2373 END IF;
2374 --
2375
2376 add_to_update_table(
2377 p_del_det_int_rec => l_prev_int_rec,
2378 p_update_mode => 'UPDATE',
2379 p_delivery_id => l_delivery_id,
2380 x_return_status => l_return_status);
2381
2382 IF (l_return_status <> wsh_util_core.g_ret_sts_success) THEN
2383 RAISE fnd_api.g_exc_error;
2384 END IF;
2385
2386
2387 IF (nvl(WSH_INBOUND_SHIP_ADVICE_PKG.G_WAREHOUSE_TYPE, '!') = 'TW2') THEN
2388 l_new_detail_ids(l_new_detail_ids.COUNT+1) := l_prev_int_rec.delivery_detail_id;
2389 END IF;
2390
2391 END LOOP; --}
2392 --
2393 -- g_serial_range_tab := g_serial_range_tab + l_serial_range_tab;
2394 add_to_serial_table(p_serial_range_tab => l_serial_range_tab);
2395 l_serial_range_tab.delete;
2396
2397 -- TPW - Distributed changes
2398 l_frt_detail_intf_tab (l_frt_detail_intf_tab.COUNT+1) := l_prev_int_rec.delivery_detail_interface_id;
2399 l_frt_detail_tab (l_frt_detail_tab.COUNT+1) := l_prev_int_rec.delivery_detail_id;
2400
2401 l_detail_id_tab.DELETE;
2402
2403 END IF;
2404 END IF; --}
2405
2406 -- TPW - Distributed changes
2407 IF l_debug_on THEN
2408 wsh_debug_sv.log (l_module_name, 'Freight Table Count', l_frt_detail_intf_tab.COUNT);
2409 END IF;
2410
2411 IF (l_frt_detail_intf_tab.COUNT > 0) THEN
2412 FOR i in 1..l_frt_detail_intf_tab.COUNT LOOP
2413 IF l_debug_on THEN
2414 wsh_debug_sv.log (l_module_name, 'i', i);
2415 wsh_debug_sv.log (l_module_name, 'l_frt_detail_intf_tab',l_frt_detail_intf_tab(i));
2416 wsh_debug_sv.log (l_module_name, 'l_frt_detail_tab',l_frt_detail_tab(i));
2417 END IF;
2418 -- Process interface freight costs
2419 SELECT COUNT(*)
2420 INTO l_det_freight_costs
2421 FROM wsh_freight_costs_interface
2422 WHERE delivery_detail_interface_id = l_frt_detail_intf_tab(i)
2423 AND INTERFACE_ACTION_CODE = '94X_INBOUND';
2424
2425 IF l_debug_on THEN
2426 wsh_debug_sv.LOG(l_module_name, 'Interface freight count', l_det_freight_costs);
2427 END IF;
2428
2429 IF (l_det_freight_costs > 0) THEN
2430 IF l_debug_on THEN
2431 wsh_debug_sv.LOG(l_module_name, 'Processing freight costs for interface detail', l_frt_detail_intf_tab(i));
2432 END IF;
2433
2434 process_int_freight_costs(
2435 p_del_detail_interface_id => l_frt_detail_intf_tab(i),
2436 p_delivery_detail_id => l_frt_detail_tab(i),
2437 x_return_status => l_return_status);
2438
2439 IF l_debug_on THEN
2440 wsh_debug_sv.LOG(l_module_name, 'Process_Int_Freight_Costs l_return_status', l_return_status);
2441 END IF;
2442
2443 IF (l_return_status <> wsh_util_core.g_ret_sts_success) THEN
2444 RAISE freight_cost_processing_error;
2445 END IF;
2446 END IF;
2447 END LOOP;
2448 END IF;
2449
2450 -- TPW - Distributed changes
2451 -- For the newly created delivery details, call detail_to_delivery to assign them to delivery
2452 IF l_debug_on THEN
2453 wsh_debug_sv.log (l_module_name, 'Assign table count', l_new_detail_ids.count);
2454 END IF;
2455
2456 IF (l_new_detail_ids.count > 0) THEN
2457 WSH_DELIVERY_DETAILS_GRP.Detail_To_Delivery(
2458 p_api_version =>1.0,
2459 p_init_msg_list => l_init_msg_list,
2460 p_validation_level => l_validation_level,
2461 p_commit => l_commit,
2462 x_return_status => l_return_status,
2463 x_msg_count => l_msg_count,
2464 x_msg_data => l_msg_data,
2465 p_TabOfDelDets => l_new_detail_ids,
2466 p_action => 'ASSIGN',
2467 p_delivery_id => l_delivery_id
2468 );
2469
2470 IF l_debug_on THEN
2471 wsh_debug_sv.log (l_module_name, 'Return status from Detail to delivery', l_return_status);
2472 wsh_debug_sv.log (l_module_name, 'Detail to Delivery api msg count', l_msg_count);
2473 wsh_debug_sv.log (l_module_name, 'Detail to Delivery api msg data', l_msg_data);
2474 END IF;
2475
2476 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
2477 raise new_assignment_failed;
2478 END IF;
2479
2480 END IF;
2481
2482 IF l_debug_on THEN
2483 wsh_debug_sv.pop(l_module_name);
2484 END IF;
2485
2486 EXCEPTION
2487 WHEN fnd_api.g_exc_error THEN
2488 x_return_status := fnd_api.g_ret_sts_error;
2489 --
2490 IF l_debug_on THEN
2491 wsh_debug_sv.logmsg(l_module_name,
2492 'FND_API.G_EXC_ERROR exception has occured.',
2493 wsh_debug_sv.c_excep_level);
2494 wsh_debug_sv.pop(l_module_name, 'EXCEPTION:FND_API.G_EXC_ERROR');
2495 END IF;
2496 --
2497 --
2498 WHEN fnd_api.g_exc_unexpected_error THEN
2499 x_return_status := fnd_api.g_ret_sts_unexp_error;
2500 --
2501 IF l_debug_on THEN
2502 wsh_debug_sv.logmsg(l_module_name,
2503 'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',
2504 wsh_debug_sv.c_excep_level);
2505 wsh_debug_sv.pop(l_module_name,
2506 'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2507 END IF;
2508 --
2509 WHEN wsh_util_core.g_exc_warning THEN
2510 x_return_status := wsh_util_core.g_ret_sts_warning;
2511 --
2512 IF l_debug_on THEN
2513 wsh_debug_sv.logmsg(l_module_name,
2514 'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',
2515 wsh_debug_sv.c_excep_level);
2516 wsh_debug_sv.pop(l_module_name,
2517 'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
2518 END IF;
2519 --
2520 WHEN invalid_input THEN
2521 x_return_status := wsh_util_core.g_ret_sts_error;
2522
2523 IF l_debug_on THEN
2524 wsh_debug_sv.logmsg(l_module_name,
2525 'invalid_input exception has occured.',
2526 wsh_debug_sv.c_excep_level);
2527 wsh_debug_sv.pop(l_module_name, 'EXCEPTION:invalid_input');
2528 END IF;
2529 WHEN freight_cost_processing_error THEN
2530 x_return_status := wsh_util_core.g_ret_sts_error;
2531
2532 IF l_debug_on THEN
2533 wsh_debug_sv.logmsg(l_module_name,
2534 'freight_cost_processing_error exception has occured.',
2535 wsh_debug_sv.c_excep_level);
2536 wsh_debug_sv.pop(l_module_name,
2537 'EXCEPTION:freight_cost_processing_error');
2538 END IF;
2539 -- TPW - Distributed changes
2540 WHEN new_assignment_failed THEN
2541 FND_MESSAGE.SET_NAME('WSH','WSH_DEL_ASSGN_ERROR');
2542 FND_MESSAGE.SET_TOKEN('DLVY', l_delivery_id);
2543 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2544 IF l_debug_on THEN
2545 WSH_DEBUG_SV.logmsg(l_module_name,'new_assignment_failed exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2546 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:new_assignment_failed');
2547 END IF;
2548 WHEN OTHERS THEN
2549 x_return_status := wsh_util_core.g_ret_sts_unexp_error;
2550
2551 IF l_debug_on THEN
2552 wsh_debug_sv.logmsg(l_module_name,
2553 'Unexpected error has occured. Oracle error message is '
2554 || SQLERRM,
2555 wsh_debug_sv.c_unexpec_err_level);
2556 wsh_debug_sv.pop(l_module_name, 'EXCEPTION:OTHERS');
2557 END IF;
2558 END process_splits;
2559
2560
2561 PROCEDURE Pack_Lines(
2562 x_return_status OUT NOCOPY VARCHAR2) IS
2563 -- variables
2564 l_return_status VARCHAR2(30);
2565 l_pack_status VARCHAR2(30);
2566 l_del_detail_tab WSH_UTIL_CORE.id_tab_type;
2567
2568 --exceptions
2569 packing_failed exception;
2570
2571 --
2572 l_debug_on BOOLEAN;
2573 --
2574 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PACK_LINES';
2575 --
2576 BEGIN
2577 --
2578 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2579 --
2580 IF l_debug_on IS NULL
2581 THEN
2582 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2583 END IF;
2584 --
2585 IF l_debug_on THEN
2586 wsh_debug_sv.push(l_module_name, 'Pack_Lines');
2587 wsh_debug_sv.log (l_module_name, 'Packing Table Count', G_Packing_Detail_Tab.count);
2588 END IF;
2589
2590 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2591
2592 FOR i in 1..G_Packing_Detail_Tab.count LOOP
2593 -- TPW - Distributed changes
2594 IF (G_Packing_Detail_Tab(i).src_container_flag = 'N') THEN --{
2595 -- Need to delete the table because we pass only one delivery detail per call
2596 l_del_detail_tab.delete;
2597 l_del_detail_tab(1) := G_Packing_Detail_Tab(i).Delivery_Detail_Id;
2598 -- call packing api
2599
2600 IF l_debug_on THEN
2601 wsh_debug_sv.logmsg(l_module_name, 'calling container_actions.assign_detail');
2602 wsh_debug_sv.log (l_module_name, 'Delivery detail id', G_Packing_Detail_Tab(i).Delivery_Detail_Id);
2603 wsh_debug_sv.log (l_module_name, 'Container instance id', G_Packing_Detail_Tab(i).Parent_Delivery_Detail_Id);
2604 END IF;
2605
2606 WSH_CONTAINER_ACTIONS.Assign_Detail(
2607 p_container_instance_id => G_Packing_Detail_Tab(i).Parent_Delivery_Detail_Id,
2608 p_del_detail_tab => l_del_detail_tab,
2609 x_pack_status => l_pack_status,
2610 x_return_status => l_return_status);
2611
2612 ELSE --} {
2613 IF l_debug_on THEN
2614 wsh_debug_sv.logmsg(l_module_name, 'calling container_actions.assign_to_container');
2615 wsh_debug_sv.log (l_module_name, 'Det Container instance id', G_Packing_Detail_Tab(i).Delivery_Detail_Id);
2616 wsh_debug_sv.log (l_module_name, 'Master Container instance id', G_Packing_Detail_Tab(i).Parent_Delivery_Detail_Id);
2617 END IF;
2618
2619 WSH_CONTAINER_ACTIONS.Assign_To_Container (
2620 p_det_cont_inst_id => G_Packing_Detail_Tab(i).Delivery_Detail_Id,
2621 p_par_cont_inst_id => G_Packing_Detail_Tab(i).Parent_Delivery_Detail_Id,
2622 x_return_status => l_return_status);
2623
2624 END IF; --}
2625
2626 IF l_debug_on THEN
2627 wsh_debug_sv.log (l_module_name,'Return Status', l_return_status);
2628 END IF;
2629
2630 IF(l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
2631 FND_MESSAGE.SET_NAME('WSH','WSH_PACKING_ERROR');
2632 FND_MESSAGE.SET_TOKEN('DEL_DETAIL', G_Packing_Detail_Tab(i).Delivery_Detail_Id);
2633 raise packing_failed;
2634 END IF;
2635 END LOOP; -- for i in 1..G_Packing
2636
2637 IF l_debug_on THEN
2638 wsh_debug_sv.pop(l_module_name);
2639 END IF;
2640
2641 EXCEPTION
2642 WHEN packing_failed THEN
2643 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2644 IF l_debug_on THEN
2645 WSH_DEBUG_SV.logmsg(l_module_name,'packing_failed exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2646 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:packing_failed');
2647 END IF;
2648 WHEN Others THEN
2649 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2650 IF l_debug_on THEN
2651 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
2652 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2653 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2654 END IF;
2655
2656 END Pack_Lines;
2657
2658
2659 /*
2660 -----------------------------------------------------------------------------
2661 PROCEDURE : Process_Interfaced_Deliveries
2662 PARAMETERS : p_delivery_interface_id IN NUMBER,
2663 p_action_code CREATE or UPDATE
2664 x_dlvy_id The delivery id that is created
2665 x_return_status OUT VARCHAR2)
2666
2667 DESCRIPTION :
2668 -- This procedure is called by the wrapper, to process the interfaced deliveries
2669 -- If the action is CREATE, then the interface record is fetched and a base
2670 record is created by calling the public api
2671 -- If the action is UPDATE, then , using the interface record values, the
2672 base record is updated by calling the public api.
2673
2674 ------------------------------------------------------------------------------
2675 */
2676
2677 PROCEDURE Process_Interfaced_Deliveries(
2678 p_delivery_interface_id IN NUMBER,
2679 p_action_code IN VARCHAR2,
2680 x_dlvy_id OUT NOCOPY NUMBER,
2681 x_return_status OUT NOCOPY VARCHAR2) IS
2682
2683 -- Bug 2753330
2684 l_in_rec WSH_DELIVERIES_GRP.Del_In_Rec_Type;
2685 l_dlvy_attr_tab WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;
2686 l_dlvy_out_rec_tab WSH_DELIVERIES_GRP.Del_Out_Tbl_Type;
2687 l_index NUMBER;
2688 l_number_of_errors NUMBER := 0;
2689 l_number_of_warnings NUMBER := 0;
2690 l_api_version_number NUMBER := 1.0;
2691 --
2692 l_init_msg_list VARCHAR2(30) := NULL;
2693 l_return_status VARCHAR2(30);
2694 l_msg_count NUMBER;
2695 l_msg_data VARCHAR2(3000);
2696
2697 l_del_freight_costs NUMBER;
2698 l_delivery_id NUMBER;
2699
2700 l_delivery_name VARCHAR2(150);
2701 x_delivery_name VARCHAR2(150);
2702
2703 l_curr_ship_method VARCHAR2(30);
2704
2705 l_loc_interface_error_rec WSH_INTERFACE_VALIDATIONS_PKG.interface_errors_rec_type;
2706
2707 CURSOR del_int_cur IS
2708 SELECT
2709 POOLED_SHIP_TO_LOCATION_CODE,
2710 ULTIMATE_DROPOFF_LOCATION_CODE,
2711 CUSTOMER_NUMBER,
2712 FOB_LOCATION_CODE,
2713 INITIAL_PICKUP_LOCATION_CODE,
2714 INTMED_SHIP_TO_LOCATION_CODE,
2715 ORGANIZATION_CODE,
2716 BATCH_ID,
2717 BILL_FREIGHT_TO,
2718 BOOKING_NUMBER
2719 CARRIED_BY,
2720 COD_AMOUNT,
2721 COD_CHARGE_PAID_BY,
2722 COD_CURRENCY_CODE,
2723 COD_REMIT_TO,
2724 DESCRIPTION,
2725 ENTRY_NUMBER,
2726 FTZ_NUMBER,
2727 HASH_VALUE,
2728 IN_BOND_CODE,
2729 LOADING_ORDER_FLAG,
2730 LOADING_SEQUENCE,
2731 NUMBER_OF_LPN,
2732 PORT_OF_DISCHARGE,
2733 PORT_OF_LOADING,
2734 PROBLEM_CONTACT_REFERENCE,
2735 REASON_OF_TRANSPORT,
2736 ROUTED_EXPORT_TXN,
2737 ROUTING_INSTRUCTIONS,
2738 SERVICE_CONTRACT,
2739 SHIPPING_MARKS,
2740 SOURCE_HEADER_ID,
2741 CARRIER_CODE,
2742 NAME,
2743 PLANNED_FLAG,
2744 STATUS_CODE,
2745 INITIAL_PICKUP_DATE,
2746 INITIAL_PICKUP_LOCATION_ID,
2747 ULTIMATE_DROPOFF_LOCATION_ID,
2748 ULTIMATE_DROPOFF_DATE,
2749 CUSTOMER_ID,
2750 INTMED_SHIP_TO_LOCATION_ID,
2751 POOLED_SHIP_TO_LOCATION_ID,
2752 FREIGHT_TERMS_CODE,
2753 FOB_CODE,
2754 FOB_LOCATION_ID,
2755 WAYBILL,
2756 LOAD_TENDER_FLAG,
2757 ACCEPTANCE_FLAG,
2758 ACCEPTED_BY,
2759 ACCEPTED_DATE,
2760 ACKNOWLEDGED_BY,
2761 CONFIRMED_BY,
2762 ASN_DATE_SENT,
2763 ASN_STATUS_CODE,
2764 ASN_SEQ_NUMBER,
2765 GROSS_WEIGHT,
2766 NET_WEIGHT,
2767 WEIGHT_UOM_CODE,
2768 VOLUME,
2769 VOLUME_UOM_CODE,
2770 ADDITIONAL_SHIPMENT_INFO,
2771 ATTRIBUTE_CATEGORY,
2772 ATTRIBUTE1,
2773 ATTRIBUTE2,
2774 ATTRIBUTE3,
2775 ATTRIBUTE4,
2776 ATTRIBUTE5,
2777 ATTRIBUTE6,
2778 ATTRIBUTE7,
2779 ATTRIBUTE8,
2780 ATTRIBUTE9,
2781 ATTRIBUTE10,
2782 ATTRIBUTE11,
2783 ATTRIBUTE12,
2784 ATTRIBUTE13,
2785 ATTRIBUTE14,
2786 ATTRIBUTE15,
2787 TP_ATTRIBUTE_CATEGORY,
2788 TP_ATTRIBUTE1,
2789 TP_ATTRIBUTE2,
2790 TP_ATTRIBUTE3,
2791 TP_ATTRIBUTE4,
2792 TP_ATTRIBUTE5,
2793 TP_ATTRIBUTE6,
2794 TP_ATTRIBUTE7,
2795 TP_ATTRIBUTE8,
2796 TP_ATTRIBUTE9,
2797 TP_ATTRIBUTE10,
2798 TP_ATTRIBUTE11,
2799 TP_ATTRIBUTE12,
2800 TP_ATTRIBUTE13,
2801 TP_ATTRIBUTE14,
2802 TP_ATTRIBUTE15,
2803 CREATION_DATE,
2804 CREATED_BY,
2805 LAST_UPDATE_DATE,
2806 LAST_UPDATED_BY,
2807 LAST_UPDATE_LOGIN,
2808 PROGRAM_APPLICATION_ID,
2809 PROGRAM_ID,
2810 PROGRAM_UPDATE_DATE,
2811 REQUEST_ID,
2812 GLOBAL_ATTRIBUTE_CATEGORY,
2813 GLOBAL_ATTRIBUTE1,
2814 GLOBAL_ATTRIBUTE2,
2815 GLOBAL_ATTRIBUTE3,
2816 GLOBAL_ATTRIBUTE4,
2817 GLOBAL_ATTRIBUTE5,
2818 GLOBAL_ATTRIBUTE6,
2819 GLOBAL_ATTRIBUTE7,
2820 GLOBAL_ATTRIBUTE8,
2821 GLOBAL_ATTRIBUTE9,
2822 GLOBAL_ATTRIBUTE10,
2823 GLOBAL_ATTRIBUTE11,
2824 GLOBAL_ATTRIBUTE12,
2825 GLOBAL_ATTRIBUTE13,
2826 GLOBAL_ATTRIBUTE14,
2827 GLOBAL_ATTRIBUTE15,
2828 GLOBAL_ATTRIBUTE16,
2829 GLOBAL_ATTRIBUTE17,
2830 GLOBAL_ATTRIBUTE18,
2831 GLOBAL_ATTRIBUTE19,
2832 GLOBAL_ATTRIBUTE20,
2833 INTERFACE_ACTION_CODE,
2834 LOCK_FLAG,
2835 PROCESS_FLAG,
2836 PROCESS_MODE,
2837 DELETE_FLAG,
2838 PROCESS_STATUS_FLAG,
2839 CURRENCY_CODE,
2840 DELIVERY_TYPE,
2841 ORGANIZATION_ID,
2842 CARRIER_ID,
2843 SHIP_METHOD_CODE,
2844 DOCK_CODE,
2845 CONFIRM_DATE,
2846 DELIVERY_INTERFACE_ID,
2847 DELIVERY_ID,
2848 SERVICE_LEVEL,
2849 MODE_OF_TRANSPORT,
2850 -- J: W/V Changes
2851 WV_FROZEN_FLAG,
2852 --Bug 3458160
2853 SHIPMENT_DIRECTION,
2854 DELIVERED_DATE
2855 FROM WSH_NEW_DEL_INTERFACE
2856 WHERE delivery_interface_id = p_delivery_interface_id
2857 AND INTERFACE_ACTION_CODE = '94X_INBOUND';
2858
2859 cursor get_carrier ( p_carrier_name in varchar2 ) is
2860 select distinct carrier_id , manifesting_enabled_flag
2861 from wsh_carriers_v
2862 where carrier_name = p_carrier_name;
2863
2864 cursor l_get_enforce_shp_method_csr is
2865 select enforce_ship_method
2866 from wsh_global_parameters;
2867
2868 cursor l_shp_method_org_csr(p_ship_method_code IN VARCHAR2,
2869 p_organization_id IN NUMBER) is
2870 select 'Y'
2871 from wsh_carrier_services wcs,
2872 wsh_org_carrier_services wocs
2873 where wcs.carrier_service_id = wocs.carrier_service_id
2874 and wcs.ship_method_code = p_ship_method_code
2875 and wocs.organization_id = p_organization_id;
2876
2877 --exceptions
2878 invalid_ship_method exception;
2879 freight_cost_processing_error exception;
2880 --
2881 l_carrier_id NUMBER;
2882 l_manifesting_enabled_flag VARCHAR2(100);
2883 l_enforce_ship_method VARCHAR2(100);
2884 l_log_error_flag VARCHAR2(100);
2885 l_valid_shp_method_flag VARCHAR2(100);
2886 l_txn_type VARCHAR2(100);
2887
2888 -- TPW - Distributed changes
2889 l_ship_to_site_use_id number;
2890
2891 --
2892 l_debug_on BOOLEAN;
2893 --
2894 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PROCESS_INTERFACED_DELIVERIES';
2895 --
2896 BEGIN
2897 --
2898 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2899 --
2900 IF l_debug_on IS NULL
2901 THEN
2902 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2903 END IF;
2904 --
2905 IF l_debug_on THEN
2906 wsh_debug_sv.push(l_module_name,'Process_Interfaced_Deliveries');
2907 wsh_debug_sv.log (l_module_name,'Delivery interface Id', p_delivery_interface_id);
2908 wsh_debug_sv.log (l_module_name,'Action Code', p_action_code);
2909 wsh_debug_sv.log (l_module_name, 'Warehouse type', WSH_INBOUND_SHIP_ADVICE_PKG.G_WAREHOUSE_TYPE);
2910 END IF;
2911
2912 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2913
2914 IF(p_delivery_interface_id IS NULL)
2915 THEN
2916 raise fnd_api.g_exc_error;
2917 END IF;
2918
2919 IF p_action_code NOT IN ('CREATE', 'UPDATE')
2920 THEN
2921 FND_MESSAGE.SET_NAME('WSH', 'WSH_INVALID_ACTION_CODE');
2922 FND_MESSAGE.SET_TOKEN('ACT_CODE',p_action_code );
2923 RAISE fnd_api.g_exc_error;
2924 END IF;
2925
2926 l_index := 1;
2927
2928 -- get the delivery interface record
2929 --initialize the l_delivery_info record with the interface record values
2930 FOR l_del_int_rec IN del_int_cur
2931 LOOP
2932 -- {
2933
2934 l_enforce_ship_method := NULL;
2935 open l_get_enforce_shp_method_csr;
2936 fetch l_get_enforce_shp_method_csr into l_enforce_ship_method;
2937 close l_get_enforce_shp_method_csr;
2938
2939 IF l_debug_on THEN
2940 wsh_debug_sv.log (l_module_name, ' Ship Method ', l_del_int_rec.ship_method_code);
2941 END IF;
2942 -- need to send delivery id and name only for update
2943 --Bug Bug 3458160
2944 IF(p_action_code = 'UPDATE') THEN
2945 l_dlvy_attr_tab(l_index).DELIVERY_ID := l_del_int_rec.delivery_id;
2946 l_dlvy_attr_tab(l_index).NAME := l_del_int_rec.name;
2947 l_dlvy_attr_tab(l_index).delivered_date :=
2948 l_del_int_rec.delivered_date;
2949 ELSIF p_action_code = 'CREATE' THEN
2950 l_dlvy_attr_tab(l_index).shipment_direction :=
2951 l_del_int_rec.shipment_direction;
2952 IF (WSH_UTIL_CORE.GC3_IS_INSTALLED = 'N'
2953 AND wsh_util_core.tp_is_installed = 'Y')
2954 THEN
2955 l_dlvy_attr_tab(l_index).ignore_for_planning := 'Y';
2956 END IF;
2957 END IF; -- if p_action_code=update
2958
2959 -- This is to pass the carrier information to the Third Party Instance.
2960 if ( nvl(l_del_int_rec.carrier_code, fnd_api.g_miss_char) <> fnd_api.g_miss_char )
2961 THEN
2962 -- {
2963 l_carrier_id := NULL;
2964 l_manifesting_enabled_flag := NULL;
2965 open get_carrier( l_del_int_rec.carrier_code );
2966 fetch get_carrier into l_carrier_id, l_manifesting_enabled_flag;
2967 close get_carrier;
2968 IF l_debug_on THEN
2969 wsh_debug_sv.log (l_module_name, ' l_carrier_id', l_carrier_id);
2970 wsh_debug_sv.log (l_module_name, ' l_manifesting_enabled_flag', l_manifesting_enabled_flag);
2971 END IF;
2972
2973 l_del_int_rec.carrier_id := l_carrier_id;
2974 -- }
2975 end if;
2976 --
2977 IF(nvl(WSH_INBOUND_SHIP_ADVICE_PKG.G_WAREHOUSE_TYPE, FND_API.G_MISS_CHAR) = 'CMS')
2978 THEN
2979 -- {
2980 -- For updates from CMS, need to check if the ship method code
2981 -- is the same. Error out if different.
2982 IF(p_action_code = 'UPDATE')
2983 THEN
2984 -- {
2985 -- These changes are made to allow the manifesting system to send a changed
2986 -- combo of carrier, service level, and mode of transport.
2987 IF ( nvl(l_del_int_rec.carrier_code, fnd_api.g_miss_char) = fnd_api.g_miss_char) THEN
2988 --{
2989 IF l_debug_on THEN
2990 wsh_debug_sv.logmsg(l_module_name, 'Carrier is null');
2991 END IF;
2992 l_loc_interface_error_rec.p_message_name := 'WSH_NULL_INB_CARRIER';
2993 l_log_error_flag := 'Y';
2994 --}
2995 ELSIF (l_carrier_id IS NULL) THEN
2996 --{
2997 IF l_debug_on THEN
2998 wsh_debug_sv.logmsg(l_module_name, 'Carrier Id is null');
2999 END IF;
3000 l_loc_interface_error_rec.p_message_name := 'WSH_INVALID_INB_CARRIER';
3001 l_log_error_flag := 'Y';
3002 --}
3003 ELSIF (nvl(l_manifesting_enabled_flag, 'N') = 'N' ) THEN
3004 --{
3005 IF l_debug_on THEN
3006 wsh_debug_sv.logmsg(l_module_name, 'Carrier is not Manifesting Enabled');
3007 END IF;
3008 l_loc_interface_error_rec.p_message_name := 'WSH_INB_CAR_NOT_MFST_ENABLED';
3009 l_log_error_flag := 'Y';
3010 --}
3011 END IF;
3012 IF (
3013 nvl(l_enforce_ship_method,'N') = 'Y'
3014 AND nvl(l_del_int_rec.ship_method_code, FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
3015 ) THEN
3016 --{
3017 IF l_debug_on THEN
3018 wsh_debug_sv.logmsg(l_module_name, 'Ship Method is enforced for the org and inbound Ship Method is NULL');
3019 END IF;
3020 l_loc_interface_error_rec.p_message_name := 'WSH_NULL_INB_SHIP_METHOD';
3021 l_log_error_flag := 'Y';
3022 --}
3023 ELSIF nvl(l_del_int_rec.ship_method_code, FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR
3024 THEN
3025 --{
3026 l_valid_shp_method_flag := NULL;
3027 open l_shp_method_org_csr(l_del_int_rec.ship_method_code, l_del_int_rec.organization_id);
3028 fetch l_shp_method_org_csr into l_valid_shp_method_flag;
3029 close l_shp_method_org_csr;
3030 IF l_debug_on THEN
3031 wsh_debug_sv.log (l_module_name, ' l_valid_shp_method_flag ', l_valid_shp_method_flag);
3032 END IF;
3033
3034 IF (nvl(l_valid_shp_method_flag,'N') = 'N') THEN
3035 --{
3036 IF l_debug_on THEN
3037 wsh_debug_sv.logmsg(l_module_name, 'Ship Method is not valid');
3038 END IF;
3039 l_loc_interface_error_rec.p_message_name := 'WSH_OI_INVALID_SHIP_METHOD';
3040 l_log_error_flag := 'Y';
3041 --}
3042 END IF;
3043
3044 --}
3045 END IF;
3046 IF ( l_log_error_flag = 'Y') THEN
3047 --{
3048 l_loc_interface_error_rec.p_interface_table_name := 'WSH_NEW_DEL_INTERFACE';
3049 l_loc_interface_error_rec.p_interface_id := p_delivery_interface_id;
3050 Log_Errors(
3051 p_loc_interface_errors_rec => l_loc_interface_error_rec,
3052 p_api_name =>'Process_Interfaced_Deliveries, Action=UPDATE' ,
3053 x_return_status => l_return_status);
3054
3055 l_log_error_flag := 'N';
3056 RAISE FND_API.G_EXC_ERROR;
3057 --}
3058 END IF;
3059 /*
3060 -- Commented this part of the code as we allow the
3061 -- manifesting system to change the carrier, srv lvl, and mot.
3062 SELECT ship_method_code INTO l_curr_ship_method
3063 FROM wsh_new_deliveries
3064 WHERE delivery_id = l_del_int_rec.delivery_id;
3065
3066 IF l_debug_on THEN
3067 wsh_debug_sv.log (l_module_name,'Current ship method', l_curr_ship_method);
3068 wsh_debug_sv.log (l_module_name,'Incoming ship method', l_del_int_rec.ship_method_code);
3069 END IF;
3070
3071 IF nvl(l_curr_ship_method,FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR
3072 THEN
3073 -- {
3074 IF(l_curr_ship_method <> nvl(l_del_int_rec.ship_method_code, FND_API.G_MISS_CHAR)) THEN
3075 raise invalid_ship_method;
3076 END IF;
3077 -- }
3078 END IF;
3079 -- Bug 2753330
3080 -- Since ship_method_code should not be updateable for CMS inbound,
3081 -- Need to send G_MISS_CHAR for code and name
3082 -- so that the database value will be used for ship method code
3083 l_dlvy_attr_tab(l_index).ship_method_code := FND_API.G_MISS_CHAR;
3084 */
3085 -- }
3086 END IF;
3087 l_dlvy_attr_tab(l_index).ship_method_name := FND_API.G_MISS_CHAR;
3088 l_dlvy_attr_tab(l_index).SHIP_METHOD_CODE := nvl(l_del_int_rec.SHIP_METHOD_CODE, FND_API.G_MISS_CHAR);
3089 ELSE
3090 -- Need to send ship method code only for non-cms cases.
3091 l_dlvy_attr_tab(l_index).SHIP_METHOD_CODE := nvl(l_del_int_rec.SHIP_METHOD_CODE, FND_API.G_MISS_CHAR);
3092 -- }
3093 END IF;
3094
3095
3096 l_dlvy_attr_tab(l_index).DELIVERY_TYPE := nvl(l_del_int_rec.delivery_type, FND_API.G_MISS_CHAR);
3097 l_dlvy_attr_tab(l_index).LOADING_SEQUENCE := nvl(l_del_int_rec.loading_sequence, FND_API.G_MISS_NUM);
3098 l_dlvy_attr_tab(l_index).LOADING_ORDER_FLAG := nvl(l_del_int_rec.loading_order_flag, FND_API.G_MISS_CHAR);
3099 l_dlvy_attr_tab(l_index).INITIAL_PICKUP_DATE := nvl(l_del_int_rec.initial_pickup_date, FND_API.G_MISS_DATE);
3100 l_dlvy_attr_tab(l_index).INITIAL_PICKUP_LOCATION_ID := nvl(l_del_int_rec.initial_pickup_location_id, FND_API.G_MISS_NUM);
3101 l_dlvy_attr_tab(l_index).INITIAL_PICKUP_LOCATION_CODE := nvl(l_del_int_rec.initial_pickup_location_code, FND_API.G_MISS_CHAR);
3102 l_dlvy_attr_tab(l_index).ORGANIZATION_ID := nvl(l_del_int_rec.organization_id, FND_API.G_MISS_NUM);
3103 l_dlvy_attr_tab(l_index).ORGANIZATION_CODE := nvl(l_del_int_rec.organization_code, FND_API.G_MISS_CHAR);
3104 l_dlvy_attr_tab(l_index).ULTIMATE_DROPOFF_LOCATION_ID := nvl(l_del_int_rec.ultimate_dropoff_location_id, FND_API.G_MISS_NUM);
3105 -- Since the location id has been derived already, we should send the code only when the id is null
3106 IF(l_del_int_rec.ultimate_dropoff_location_id IS NULL)
3107 THEN
3108 l_dlvy_attr_tab(l_index).ULTIMATE_DROPOFF_LOCATION_CODE := nvl(l_del_int_rec.ULTIMATE_DROPOFF_LOCATION_CODE, FND_API.G_MISS_CHAR);
3109 END IF;
3110 l_dlvy_attr_tab(l_index).ULTIMATE_DROPOFF_DATE := nvl(l_del_int_rec.ULTIMATE_DROPOFF_DATE, FND_API.G_MISS_DATE);
3111 l_dlvy_attr_tab(l_index).CUSTOMER_ID := nvl(l_del_int_rec.CUSTOMER_ID, FND_API.G_MISS_NUM);
3112 l_dlvy_attr_tab(l_index).CUSTOMER_NUMBER := nvl(l_del_int_rec.customer_number, FND_API.G_MISS_CHAR);
3113 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);
3114
3115 -- Since the location id has been derived already, we should send the code only when the id is null
3116 IF(l_del_int_rec.INTMED_SHIP_TO_LOCATION_ID IS NULL)
3117 THEN
3118 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);
3119 END IF;
3120
3121 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);
3122 -- Send the code only when the id is null
3123 IF(l_del_int_rec.POOLED_SHIP_TO_LOCATION_ID IS NULL) THEN
3124 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);
3125 END IF;
3126
3127 l_dlvy_attr_tab(l_index).FREIGHT_TERMS_CODE := nvl(l_del_int_rec.FREIGHT_TERMS_CODE, FND_API.G_MISS_CHAR);
3128 l_dlvy_attr_tab(l_index).FOB_CODE := nvl(l_del_int_rec.FOB_CODE, FND_API.G_MISS_CHAR);
3129 l_dlvy_attr_tab(l_index).FOB_LOCATION_ID := nvl(l_del_int_rec.FOB_LOCATION_ID, FND_API.G_MISS_NUM);
3130 l_dlvy_attr_tab(l_index).FOB_LOCATION_CODE := nvl(l_del_int_rec.FOB_LOCATION_CODE, FND_API.G_MISS_CHAR);
3131 l_dlvy_attr_tab(l_index).WAYBILL := nvl(l_del_int_rec.waybill, FND_API.G_MISS_CHAR);
3132 l_dlvy_attr_tab(l_index).DOCK_CODE := nvl(l_del_int_rec.dock_code, FND_API.G_MISS_CHAR);
3133 l_dlvy_attr_tab(l_index).ACCEPTANCE_FLAG := nvl(l_del_int_rec.acceptance_flag, FND_API.G_MISS_CHAR);
3134 l_dlvy_attr_tab(l_index).ACCEPTED_BY := nvl(l_del_int_rec.accepted_by, FND_API.G_MISS_CHAR);
3135 l_dlvy_attr_tab(l_index).ACCEPTED_DATE := nvl(l_del_int_rec.accepted_date, FND_API.G_MISS_DATE);
3136 l_dlvy_attr_tab(l_index).ACKNOWLEDGED_BY := nvl(l_del_int_rec.acknowledged_by, FND_API.G_MISS_CHAR);
3137 l_dlvy_attr_tab(l_index).CONFIRMED_BY := nvl(l_del_int_rec.CONFIRMED_BY, FND_API.G_MISS_CHAR);
3138 l_dlvy_attr_tab(l_index).CONFIRM_DATE := nvl(l_del_int_rec.CONFIRM_DATE, FND_API.G_MISS_DATE);
3139 l_dlvy_attr_tab(l_index).ASN_DATE_SENT := nvl(l_del_int_rec.ASN_DATE_SENT, FND_API.G_MISS_DATE);
3140 l_dlvy_attr_tab(l_index).ASN_STATUS_CODE := nvl(l_del_int_rec.ASN_STATUS_CODE, FND_API.G_MISS_CHAR);
3141 l_dlvy_attr_tab(l_index).ASN_SEQ_NUMBER := nvl(l_del_int_rec.ASN_SEQ_NUMBER, FND_API.G_MISS_NUM);
3142 l_dlvy_attr_tab(l_index).GROSS_WEIGHT := nvl(l_del_int_rec.GROSS_WEIGHT, FND_API.G_MISS_NUM);
3143 l_dlvy_attr_tab(l_index).NET_WEIGHT := nvl(l_del_int_rec.NET_WEIGHT, FND_API.G_MISS_NUM);
3144 l_dlvy_attr_tab(l_index).WEIGHT_UOM_CODE := nvl(l_del_int_rec.WEIGHT_UOM_CODE, FND_API.G_MISS_CHAR);
3145 l_dlvy_attr_tab(l_index).VOLUME := nvl(l_del_int_rec.VOLUME , FND_API.G_MISS_NUM);
3146 l_dlvy_attr_tab(l_index).VOLUME_UOM_CODE := nvl(l_del_int_rec.VOLUME_UOM_CODE, FND_API.G_MISS_CHAR);
3147 -- J: W/V Changes
3148 l_dlvy_attr_tab(l_index).WV_FROZEN_FLAG := l_del_int_rec.WV_FROZEN_FLAG;
3149 l_dlvy_attr_tab(l_index).ADDITIONAL_SHIPMENT_INFO := nvl(l_del_int_rec.ADDITIONAL_SHIPMENT_INFO , FND_API.G_MISS_CHAR);
3150 l_dlvy_attr_tab(l_index).CURRENCY_CODE := nvl(l_del_int_rec.CURRENCY_CODE, FND_API.G_MISS_CHAR);
3151 l_dlvy_attr_tab(l_index).ATTRIBUTE_CATEGORY := nvl(l_del_int_rec.ATTRIBUTE_CATEGORY , FND_API.G_MISS_CHAR);
3152 l_dlvy_attr_tab(l_index).ATTRIBUTE1 := nvl(l_del_int_rec.ATTRIBUTE1, FND_API.G_MISS_CHAR);
3153 l_dlvy_attr_tab(l_index).ATTRIBUTE2 := nvl(l_del_int_rec.ATTRIBUTE2, FND_API.G_MISS_CHAR);
3154 l_dlvy_attr_tab(l_index).ATTRIBUTE3 := nvl(l_del_int_rec.ATTRIBUTE3, FND_API.G_MISS_CHAR);
3155 l_dlvy_attr_tab(l_index).ATTRIBUTE4 := nvl(l_del_int_rec.ATTRIBUTE4, FND_API.G_MISS_CHAR);
3156 l_dlvy_attr_tab(l_index).ATTRIBUTE5 := nvl(l_del_int_rec.ATTRIBUTE5, FND_API.G_MISS_CHAR);
3157 l_dlvy_attr_tab(l_index).ATTRIBUTE6 := nvl(l_del_int_rec.ATTRIBUTE6, FND_API.G_MISS_CHAR);
3158 l_dlvy_attr_tab(l_index).ATTRIBUTE7 := nvl(l_del_int_rec.ATTRIBUTE7, FND_API.G_MISS_CHAR);
3159 l_dlvy_attr_tab(l_index).ATTRIBUTE8 := nvl(l_del_int_rec.ATTRIBUTE8, FND_API.G_MISS_CHAR);
3160 l_dlvy_attr_tab(l_index).ATTRIBUTE9 := nvl(l_del_int_rec.ATTRIBUTE9, FND_API.G_MISS_CHAR);
3161 l_dlvy_attr_tab(l_index).ATTRIBUTE10 := nvl(l_del_int_rec.ATTRIBUTE10, FND_API.G_MISS_CHAR);
3162 l_dlvy_attr_tab(l_index).ATTRIBUTE11 := nvl(l_del_int_rec.ATTRIBUTE11, FND_API.G_MISS_CHAR);
3163 l_dlvy_attr_tab(l_index).ATTRIBUTE12 := nvl(l_del_int_rec.ATTRIBUTE12, FND_API.G_MISS_CHAR);
3164 l_dlvy_attr_tab(l_index).ATTRIBUTE13 := nvl(l_del_int_rec.ATTRIBUTE13, FND_API.G_MISS_CHAR);
3165 l_dlvy_attr_tab(l_index).ATTRIBUTE14 := nvl(l_del_int_rec.ATTRIBUTE14, FND_API.G_MISS_CHAR);
3166 l_dlvy_attr_tab(l_index).ATTRIBUTE15 := nvl(l_del_int_rec.ATTRIBUTE15, FND_API.G_MISS_CHAR);
3167 l_dlvy_attr_tab(l_index).TP_ATTRIBUTE_CATEGORY := nvl(l_del_int_rec.TP_ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR);
3168 l_dlvy_attr_tab(l_index).TP_ATTRIBUTE1 := nvl(l_del_int_rec.TP_ATTRIBUTE1, FND_API.G_MISS_CHAR);
3169 l_dlvy_attr_tab(l_index).TP_ATTRIBUTE2 := nvl(l_del_int_rec.TP_ATTRIBUTE2, FND_API.G_MISS_CHAR);
3170 l_dlvy_attr_tab(l_index).TP_ATTRIBUTE3 := nvl(l_del_int_rec.TP_ATTRIBUTE3, FND_API.G_MISS_CHAR);
3171 l_dlvy_attr_tab(l_index).TP_ATTRIBUTE4 := nvl(l_del_int_rec.TP_ATTRIBUTE4, FND_API.G_MISS_CHAR);
3172 l_dlvy_attr_tab(l_index).TP_ATTRIBUTE5 := nvl(l_del_int_rec.TP_ATTRIBUTE5, FND_API.G_MISS_CHAR);
3173 l_dlvy_attr_tab(l_index).TP_ATTRIBUTE6 := nvl(l_del_int_rec.TP_ATTRIBUTE6, FND_API.G_MISS_CHAR);
3174 l_dlvy_attr_tab(l_index).TP_ATTRIBUTE7 := nvl(l_del_int_rec.TP_ATTRIBUTE7, FND_API.G_MISS_CHAR);
3175 l_dlvy_attr_tab(l_index).TP_ATTRIBUTE8 := nvl(l_del_int_rec.TP_ATTRIBUTE8, FND_API.G_MISS_CHAR);
3176 l_dlvy_attr_tab(l_index).TP_ATTRIBUTE9 := nvl(l_del_int_rec.TP_ATTRIBUTE9, FND_API.G_MISS_CHAR);
3177 l_dlvy_attr_tab(l_index).TP_ATTRIBUTE10 := nvl(l_del_int_rec.TP_ATTRIBUTE10, FND_API.G_MISS_CHAR);
3178 l_dlvy_attr_tab(l_index).TP_ATTRIBUTE11 := nvl(l_del_int_rec.TP_ATTRIBUTE11, FND_API.G_MISS_CHAR);
3179 l_dlvy_attr_tab(l_index).TP_ATTRIBUTE12 := nvl(l_del_int_rec.TP_ATTRIBUTE12, FND_API.G_MISS_CHAR);
3180 l_dlvy_attr_tab(l_index).TP_ATTRIBUTE13 := nvl(l_del_int_rec.TP_ATTRIBUTE13, FND_API.G_MISS_CHAR);
3181 l_dlvy_attr_tab(l_index).TP_ATTRIBUTE14 := nvl(l_del_int_rec.TP_ATTRIBUTE14, FND_API.G_MISS_CHAR);
3182 l_dlvy_attr_tab(l_index).TP_ATTRIBUTE15 := nvl(l_del_int_rec.TP_ATTRIBUTE15, FND_API.G_MISS_CHAR);
3183 l_dlvy_attr_tab(l_index).GLOBAL_ATTRIBUTE_CATEGORY := nvl(l_del_int_rec.GLOBAL_ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR);
3184 l_dlvy_attr_tab(l_index).GLOBAL_ATTRIBUTE1 := nvl(l_del_int_rec.GLOBAL_ATTRIBUTE1 , FND_API.G_MISS_CHAR);
3185 l_dlvy_attr_tab(l_index).GLOBAL_ATTRIBUTE2 := nvl(l_del_int_rec.GLOBAL_ATTRIBUTE2 , FND_API.G_MISS_CHAR);
3186 l_dlvy_attr_tab(l_index).GLOBAL_ATTRIBUTE3 := nvl(l_del_int_rec.GLOBAL_ATTRIBUTE3 , FND_API.G_MISS_CHAR);
3187 l_dlvy_attr_tab(l_index).GLOBAL_ATTRIBUTE4 := nvl(l_del_int_rec.GLOBAL_ATTRIBUTE4 , FND_API.G_MISS_CHAR);
3188 l_dlvy_attr_tab(l_index).GLOBAL_ATTRIBUTE5 := nvl(l_del_int_rec.GLOBAL_ATTRIBUTE5 , FND_API.G_MISS_CHAR);
3189 l_dlvy_attr_tab(l_index).GLOBAL_ATTRIBUTE6 := nvl(l_del_int_rec.GLOBAL_ATTRIBUTE6 , FND_API.G_MISS_CHAR);
3190 l_dlvy_attr_tab(l_index).GLOBAL_ATTRIBUTE7 := nvl(l_del_int_rec.GLOBAL_ATTRIBUTE7 , FND_API.G_MISS_CHAR);
3191 l_dlvy_attr_tab(l_index).GLOBAL_ATTRIBUTE8 := nvl(l_del_int_rec.GLOBAL_ATTRIBUTE8 , FND_API.G_MISS_CHAR);
3192 l_dlvy_attr_tab(l_index).GLOBAL_ATTRIBUTE9 := nvl(l_del_int_rec.GLOBAL_ATTRIBUTE9 , FND_API.G_MISS_CHAR);
3193 l_dlvy_attr_tab(l_index).GLOBAL_ATTRIBUTE10 := nvl(l_del_int_rec.GLOBAL_ATTRIBUTE10, FND_API.G_MISS_CHAR);
3194 l_dlvy_attr_tab(l_index).GLOBAL_ATTRIBUTE11 := nvl(l_del_int_rec.GLOBAL_ATTRIBUTE11, FND_API.G_MISS_CHAR);
3195 l_dlvy_attr_tab(l_index).GLOBAL_ATTRIBUTE12 := nvl(l_del_int_rec.GLOBAL_ATTRIBUTE12, FND_API.G_MISS_CHAR);
3196 l_dlvy_attr_tab(l_index).GLOBAL_ATTRIBUTE13 := nvl(l_del_int_rec.GLOBAL_ATTRIBUTE13, FND_API.G_MISS_CHAR);
3197 l_dlvy_attr_tab(l_index).GLOBAL_ATTRIBUTE14 := nvl(l_del_int_rec.GLOBAL_ATTRIBUTE14, FND_API.G_MISS_CHAR);
3198 l_dlvy_attr_tab(l_index).GLOBAL_ATTRIBUTE15 := nvl(l_del_int_rec.GLOBAL_ATTRIBUTE15, FND_API.G_MISS_CHAR);
3199 l_dlvy_attr_tab(l_index).GLOBAL_ATTRIBUTE16 := nvl(l_del_int_rec.GLOBAL_ATTRIBUTE16, FND_API.G_MISS_CHAR);
3200 l_dlvy_attr_tab(l_index).GLOBAL_ATTRIBUTE17 := nvl(l_del_int_rec.GLOBAL_ATTRIBUTE17, FND_API.G_MISS_CHAR);
3201 l_dlvy_attr_tab(l_index).GLOBAL_ATTRIBUTE18 := nvl(l_del_int_rec.GLOBAL_ATTRIBUTE18, FND_API.G_MISS_CHAR);
3202 l_dlvy_attr_tab(l_index).GLOBAL_ATTRIBUTE19 := nvl(l_del_int_rec.GLOBAL_ATTRIBUTE19, FND_API.G_MISS_CHAR);
3203 l_dlvy_attr_tab(l_index).GLOBAL_ATTRIBUTE20 := nvl(l_del_int_rec.GLOBAL_ATTRIBUTE20, FND_API.G_MISS_CHAR);
3204 l_dlvy_attr_tab(l_index).PROBLEM_CONTACT_REFERENCE := nvl(l_del_int_rec.PROBLEM_CONTACT_REFERENCE, FND_API.G_MISS_CHAR);
3205 l_dlvy_attr_tab(l_index).COD_AMOUNT := nvl(l_del_int_rec.COD_AMOUNT, FND_API.G_MISS_NUM);
3206 l_dlvy_attr_tab(l_index).COD_CURRENCY_CODE := nvl(l_del_int_rec.COD_CURRENCY_CODE , FND_API.G_MISS_CHAR);
3207 l_dlvy_attr_tab(l_index).COD_REMIT_TO := nvl(l_del_int_rec.COD_REMIT_TO, FND_API.G_MISS_CHAR);
3208 l_dlvy_attr_tab(l_index).COD_CHARGE_PAID_BY := nvl(l_del_int_rec.COD_CHARGE_PAID_BY , FND_API.G_MISS_CHAR);
3209 l_dlvy_attr_tab(l_index).PORT_OF_LOADING := nvl(l_del_int_rec.PORT_OF_LOADING , FND_API.G_MISS_CHAR);
3210 l_dlvy_attr_tab(l_index).PORT_OF_DISCHARGE := nvl(l_del_int_rec.PORT_OF_DISCHARGE , FND_API.G_MISS_CHAR);
3211 l_dlvy_attr_tab(l_index).FTZ_NUMBER := nvl(l_del_int_rec.FTZ_NUMBER, FND_API.G_MISS_CHAR);
3212 l_dlvy_attr_tab(l_index).ROUTED_EXPORT_TXN := nvl(l_del_int_rec.ROUTED_EXPORT_TXN, FND_API.G_MISS_CHAR);
3213 l_dlvy_attr_tab(l_index).ENTRY_NUMBER := nvl(l_del_int_rec.ENTRY_NUMBER, FND_API.G_MISS_CHAR);
3214 l_dlvy_attr_tab(l_index).ROUTING_INSTRUCTIONS := nvl(l_del_int_rec.ROUTING_INSTRUCTIONS , FND_API.G_MISS_CHAR);
3215 l_dlvy_attr_tab(l_index).IN_BOND_CODE := nvl(l_del_int_rec.IN_BOND_CODE , FND_API.G_MISS_CHAR);
3216 l_dlvy_attr_tab(l_index).SHIPPING_MARKS := nvl(l_del_int_rec.SHIPPING_MARKS, FND_API.G_MISS_CHAR);
3217 l_txn_type := l_del_int_rec.INTERFACE_ACTION_CODE;
3218 IF (nvl(l_txn_type,'!!!!') = '94X_INBOUND') THEN
3219 l_dlvy_attr_tab(l_index).CARRIER_ID := l_del_int_rec.carrier_id;
3220 l_dlvy_attr_tab(l_index).CARRIER_CODE := l_del_int_rec.carrier_code;
3221 l_dlvy_attr_tab(l_index).SERVICE_LEVEL := l_del_int_rec.SERVICE_LEVEL;
3222 l_dlvy_attr_tab(l_index).MODE_OF_TRANSPORT := l_del_int_rec.MODE_OF_TRANSPORT;
3223 ELSE
3224 l_dlvy_attr_tab(l_index).CARRIER_ID := nvl(l_del_int_rec.carrier_id, FND_API.G_MISS_NUM);
3225 l_dlvy_attr_tab(l_index).CARRIER_CODE := nvl(l_del_int_rec.carrier_code, FND_API.G_MISS_CHAR);
3226 l_dlvy_attr_tab(l_index).SERVICE_LEVEL := nvl(l_del_int_rec.SERVICE_LEVEL, FND_API.G_MISS_CHAR);
3227 l_dlvy_attr_tab(l_index).MODE_OF_TRANSPORT := nvl(l_del_int_rec.MODE_OF_TRANSPORT, FND_API.G_MISS_CHAR);
3228 END IF;
3229 l_txn_type := null;
3230
3231 IF l_debug_on THEN
3232 wsh_debug_sv.log (l_module_name, ' Service Level ', l_del_int_rec.SERVICE_LEVEL);
3233 wsh_debug_sv.log (l_module_name, ' Transportation Method ', l_del_int_rec.MODE_OF_TRANSPORT);
3234 wsh_debug_sv.logmsg (l_module_name, '--------------------------------------------------------------------------------------------');
3235 wsh_debug_sv.logmsg (l_module_name, 'The following are the values of Carrier, Service Level and MOT being passed to the Group API');
3236 wsh_debug_sv.logmsg (l_module_name, '--------------------------------------------------------------------------------------------');
3237 wsh_debug_sv.logmsg (l_module_name, '********************************************************************************************');
3238 wsh_debug_sv.logmsg (l_module_name, '--------------------------------------------------------------------------------------------');
3239 wsh_debug_sv.log (l_module_name, 'l_dlvy_attr_tab(l_index).CARRIER_ID',l_dlvy_attr_tab(l_index).CARRIER_ID);
3240 wsh_debug_sv.log (l_module_name, 'l_dlvy_attr_tab(l_index).CARRIER_CODE',l_dlvy_attr_tab(l_index).CARRIER_CODE);
3241 wsh_debug_sv.log (l_module_name, 'l_dlvy_attr_tab(l_index).SERVICE_LEVEL',l_dlvy_attr_tab(l_index).SERVICE_LEVEL);
3242 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);
3243 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);
3244 wsh_debug_sv.logmsg (l_module_name, '--------------------------------------------------------------------------------------------');
3245 wsh_debug_sv.logmsg (l_module_name, '********************************************************************************************');
3246 wsh_debug_sv.logmsg (l_module_name, '--------------------------------------------------------------------------------------------');
3247 END IF;
3248
3249 l_index := l_index + 1;
3250 -- }
3251 END LOOP; -- for l_del_int_rec
3252
3253 --Bug 3458160
3254 -- TPW - Distributed changes
3255 IF (p_action_code = 'CREATE') AND (nvl(WSH_INBOUND_SHIP_ADVICE_PKG.G_WAREHOUSE_TYPE, '!') <> 'TW2') THEN
3256 l_in_rec.caller := 'WSH_TPW_INBOUND';
3257 ELSE
3258 l_in_rec.caller := 'WSH_INBOUND';
3259 END IF;
3260 l_in_rec.action_code := p_action_code;
3261
3262 wsh_interface_grp.Create_Update_Delivery(
3263 p_api_version_number => l_api_version_number,
3264 p_init_msg_list => FND_API.G_FALSE,
3265 p_commit => FND_API.G_FALSE,
3266 p_in_rec => l_in_rec,
3267 p_rec_attr_tab => l_dlvy_attr_tab,
3268 x_del_out_rec_tab => l_dlvy_out_rec_tab,
3269 x_return_status => l_return_status,
3270 x_msg_count => l_msg_count,
3271 x_msg_data => l_msg_data);
3272
3273 IF l_debug_on THEN
3274 wsh_debug_sv.log (l_module_name, 'Return status from create_Update_delivery',l_return_status);
3275 wsh_debug_sv.log (l_module_name, 'Create Update Delivery api msg count', l_msg_count);
3276 wsh_debug_sv.log (l_module_name, 'Create Update Delivery api msg', l_msg_data);
3277 END IF;
3278
3279 IF(l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS)
3280 THEN
3281 -- {
3282 l_loc_interface_error_rec.p_interface_table_name := 'WSH_NEW_DEL_INTERFACE';
3283 l_loc_interface_error_rec.p_interface_id := p_delivery_interface_id;
3284 --
3285 Log_Errors(
3286 p_loc_interface_errors_rec => l_loc_interface_error_rec,
3287 p_msg_data => l_msg_data,
3288 p_api_name => 'WSH_INTERFACE_GRP.Create_Update_Delivery' ,
3289 x_return_status => l_return_status);
3290 --
3291 IF l_debug_on THEN
3292 wsh_debug_sv.log (l_module_name,'Log_Errors l_return_status',l_return_status);
3293 END IF;
3294
3295 IF p_action_code = 'CREATE'
3296 THEN
3297 -- {
3298 FND_MESSAGE.SET_NAME('WSH', 'WSH_CREATE_DLVY_ERROR');
3299 FND_MESSAGE.SET_TOKEN('DEL_INT',p_delivery_interface_id);
3300 ELSIF p_action_code = 'UPDATE'
3301 THEN
3302 FND_MESSAGE.SET_NAME('WSH', 'WSH_UPDATE_DLVY_ERROR');
3303 FND_MESSAGE.SET_TOKEN('DEL_INT', p_delivery_interface_id);
3304 -- }
3305 END IF;
3306 --
3307 RAISE fnd_api.g_exc_error;
3308 -- }
3309 END IF;
3310
3311 -- send the newly created delivery in the out parameter
3312 IF p_action_code = 'CREATE'
3313 THEN
3314 -- {
3315 IF l_debug_on THEN
3316 wsh_debug_sv.log (l_module_name, 'New delivery Id created', l_dlvy_out_rec_tab(l_dlvy_out_rec_tab.first).delivery_id);
3317 END IF;
3318 x_dlvy_id := l_dlvy_out_rec_tab(l_dlvy_out_rec_tab.first).delivery_id;
3319
3320 -- TPW - Distributed changes
3321 IF (nvl(WSH_INBOUND_SHIP_ADVICE_PKG.G_WAREHOUSE_TYPE, '!') = 'TW2') THEN
3322 update wsh_new_del_interface
3323 set delivery_id = x_dlvy_id
3324 where delivery_interface_id = p_delivery_interface_id;
3325
3326 UPDATE wsh_del_legs_interface
3327 SET delivery_id = x_dlvy_id
3328 WHERE delivery_interface_id = p_delivery_interface_id;
3329 END IF;
3330 -- }
3331 END IF;
3332
3333 -- TPW - Distributed changes
3334 SELECT count(*) INTO l_del_freight_costs
3335 FROM wsh_freight_costs_interface
3336 WHERE delivery_interface_id = p_delivery_interface_id
3337 AND INTERFACE_ACTION_CODE = '94X_INBOUND';
3338
3339 IF l_debug_on THEN
3340 wsh_debug_sv.log (l_module_name, 'Delivery Freight record count', l_del_freight_costs);
3341 END IF;
3342
3343 IF(l_del_freight_costs > 0)
3344 THEN
3345 -- {
3346 IF l_debug_on THEN
3347 wsh_debug_sv.log (l_module_name, 'calling process freight for delivery interface', p_delivery_interface_id);
3348 END IF;
3349
3350 Process_Int_Freight_Costs(
3351 p_delivery_interface_id => p_delivery_interface_id,
3352 x_return_status => l_return_status);
3353
3354 IF l_debug_on THEN
3355 wsh_debug_sv.log (l_module_name, 'Return status from process int freight costs', l_return_status);
3356 END IF;
3357 --
3358 IF(l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
3359 raise freight_cost_processing_error;
3360 END IF;
3361 -- }
3362 END IF;
3363
3364 IF l_debug_on THEN
3365 wsh_debug_sv.pop(l_module_name);
3366 END IF;
3367
3368 EXCEPTION
3369 WHEN fnd_api.g_exc_error THEN
3370 x_return_status := fnd_api.g_ret_sts_error;
3371 --
3372 IF l_debug_on THEN
3373 wsh_debug_sv.logmsg(l_module_name, 'FND_API.G_EXC_ERROR exception has occured.', wsh_debug_sv.c_excep_level);
3374 wsh_debug_sv.pop(l_module_name, 'EXCEPTION:FND_API.G_EXC_ERROR');
3375 END IF;
3376 --
3377 WHEN fnd_api.g_exc_unexpected_error THEN
3378 x_return_status := fnd_api.g_ret_sts_unexp_error;
3379 --
3380 IF l_debug_on THEN
3381 wsh_debug_sv.logmsg(l_module_name, 'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.', wsh_debug_sv.c_excep_level);
3382 wsh_debug_sv.pop(l_module_name, 'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
3383 END IF;
3384 --
3385 WHEN wsh_util_core.g_exc_warning THEN
3386 x_return_status := wsh_util_core.g_ret_sts_warning;
3387 --
3388 IF l_debug_on THEN
3389 wsh_debug_sv.logmsg(l_module_name, 'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ', wsh_debug_sv.c_excep_level);
3390 wsh_debug_sv.pop(l_module_name, 'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
3391 END IF;
3392 --
3393 WHEN invalid_ship_method THEN
3394 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3395 IF l_debug_on THEN
3396 WSH_DEBUG_SV.logmsg(l_module_name,'invalid_ship_method exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3397 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_ship_method');
3398 END IF;
3399 WHEN freight_cost_processing_error THEN
3400 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3401 IF l_debug_on THEN
3402 WSH_DEBUG_SV.logmsg(l_module_name,'freight_cost_processing_error exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3403 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:freight_cost_processing_error');
3404 END IF;
3405 WHEN OTHERS THEN
3406 x_return_status := wsh_util_core.g_ret_sts_unexp_error;
3407 wsh_util_core.default_handler('WSH_INTERFACE_COMMON_ACTIONS.PROCESS_INTERFACED_DELIVERIES');
3408
3409 IF l_debug_on THEN
3410 wsh_debug_sv.logmsg(l_module_name, 'Unexpected error has occured. Oracle error message is ' || SQLERRM, wsh_debug_sv.c_unexpec_err_level);
3411 wsh_debug_sv.pop(l_module_name, 'EXCEPTION:OTHERS');
3412 END IF;
3413
3414 END Process_Interfaced_Deliveries;
3415
3416 /*
3417 -----------------------------------------------------------------------------
3418 PROCEDURE : Delivery_Interface_Wrapper
3419 PARAMETERS : p_delivery_interface_id IN NUMBER,
3420 p_action_code CREATE or UPDATE or CANCEL
3421 x_return_status OUT VARCHAR2)
3422
3423 DESCRIPTION :
3424 -- This is the wrapper procedure that will be called by the Process_Inbound
3425 for Shipment_Advice or Shipment_Request.
3426 -- This takes in a delivery_interface_id and the action code
3427 -- If the action code is CREATE, then the delivery is created in the base
3428 tables based on the data in the delivery-interface tables
3429 - Then for each of the delivery details in the interface tables, a corresponding
3430 delivery detail is created in the base tables.
3431 -- Then the newly created base delivery details are assigned to the newly
3432 created base delivery.
3433 -- If the action code is UPDATE, then the base delivery is updated first
3434 -- Followed by updates of base delivery details
3435
3436 ------------------------------------------------------------------------------
3437 */
3438
3439 PROCEDURE Delivery_Interface_Wrapper(
3440 p_delivery_interface_id IN NUMBER,
3441 p_action_code IN VARCHAR2,
3442 x_delivery_id IN OUT NOCOPY NUMBER,
3443 x_return_status OUT NOCOPY VARCHAR2) IS
3444
3445 -- variables
3446 l_return_status VARCHAR2(30);
3447 l_delivery_id NUMBER;
3448
3449 l_created_delivery_id NUMBER;
3450 l_new_del_detail_id NUMBER;
3451 l_action_code VARCHAR2(30);
3452
3453 l_loc_interface_error_rec WSH_INTERFACE_VALIDATIONS_PKG.interface_errors_rec_type;
3454 -- cursors
3455 CURSOR del_id IS
3456 SELECT delivery_id ,
3457 -- J: W/V Changes
3458 gross_weight,
3459 net_weight,
3460 volume,
3461 wv_frozen_flag
3462 FROM wsh_new_del_interface
3463 WHERE delivery_interface_id = p_delivery_interface_id
3464 AND INTERFACE_ACTION_CODE = '94X_INBOUND';
3465
3466 cursor org_del_id(c_del_id IN NUMBER) IS
3467 SELECT NVL(gross_weight,0),
3468 NVL(net_weight,0),
3469 NVL(volume,0)
3470 FROM wsh_new_deliveries
3471 WHERE delivery_id = c_del_id;
3472
3473
3474 -- J: W/V changes
3475 l_api_version NUMBER := 1.0;
3476 l_msg_count NUMBER;
3477 l_msg_data VARCHAR2(3000);
3478 l_gross_weight NUMBER;
3479 l_net_weight NUMBER;
3480 l_volume NUMBER;
3481 l_wv_frozen_flag VARCHAR2(1);
3482 l_tmp_del_id NUMBER;
3483 l_org_gross_weight NUMBER;
3484 l_org_net_weight NUMBER;
3485 l_org_volume NUMBER;
3486
3487 -- exceptions
3488 invalid_action_code exception;
3489 process_del_details_failed exception;
3490 process_delivery_failed exception;
3491 process_delivery_wv_failed exception;
3492 cancel_lines_failed exception;
3493 invalid_input exception;
3494 no_lock_found exception;
3495 --
3496 l_debug_on BOOLEAN;
3497 --
3498 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DELIVERY_INTERFACE_WRAPPER';
3499 --
3500 BEGIN
3501 --
3502 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3503 --
3504 IF l_debug_on IS NULL
3505 THEN
3506 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3507 END IF;
3508 --
3509 IF l_debug_on THEN
3510 wsh_debug_sv.push(l_module_name,'Delivery_Interface_Wrapper');
3511 wsh_debug_sv.log (l_module_name,'Delivery interface Id', p_delivery_interface_id);
3512 wsh_debug_sv.log (l_module_name,'Action Code', p_action_code);
3513 END IF;
3514
3515 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3516
3517 l_action_code := p_action_code;
3518
3519 -- First process the delivery
3520 IF(l_action_code IN ('CREATE', 'UPDATE')) THEN
3521 IF(p_delivery_interface_id IS NULL) THEN
3522 raise invalid_input;
3523 END IF;
3524
3525 OPEN del_id;
3526 FETCH del_id INTO l_delivery_id,
3527 -- J: W/V Changes
3528 l_gross_weight,
3529 l_net_weight,
3530 l_volume,
3531 l_wv_frozen_flag;
3532 CLOSE del_id;
3533
3534 IF(l_action_code = 'UPDATE') THEN
3535
3536 --Lock the records
3537 Lock_Delivery_And_Details(
3538 p_delivery_id => l_delivery_id,
3539 x_return_status => l_return_status);
3540
3541 IF l_debug_on THEN
3542 wsh_debug_sv.log (l_module_name,'Return status from lock delivery and details', l_return_status);
3543 END IF;
3544
3545 IF(l_return_status <> wsh_util_core.g_ret_sts_success) THEN
3546 l_loc_interface_error_rec.p_interface_table_name := 'WSH_NEW_DEL_INTERFACE';
3547 l_loc_interface_error_rec.p_interface_id := p_delivery_interface_id;
3548 l_loc_interface_error_rec.p_message_name := 'WSH_NO_LOCK';
3549
3550 Log_Errors(
3551 p_loc_interface_errors_rec => l_loc_interface_error_rec,
3552 p_api_name =>'Delivery_Interface_Wrapper, Action=UPDATE' ,
3553 x_return_status => l_return_status);
3554
3555 IF l_debug_on THEN
3556 wsh_debug_sv.log (l_module_name,'Log_Errors l_return_status', l_return_status);
3557 END IF;
3558 raise no_lock_found;
3559 END IF;
3560
3561 -- TPW - Distributed changes
3562 ELSIF (l_action_code = 'CREATE') AND (nvl(WSH_INBOUND_SHIP_ADVICE_PKG.G_WAREHOUSE_TYPE, '!') = 'TW2') THEN
3563
3564 --Lock the records
3565 Lock_Delivery_Details(
3566 p_delivery_interface_id => p_delivery_interface_id,
3567 x_return_status => l_return_status);
3568
3569 IF l_debug_on THEN
3570 wsh_debug_sv.log (l_module_name,'Return status from Lock_Delivery_Details', l_return_status);
3571 END IF;
3572
3573 IF(l_return_status <> wsh_util_core.g_ret_sts_success) THEN
3574 l_loc_interface_error_rec.p_interface_table_name := 'WSH_NEW_DEL_INTERFACE';
3575 l_loc_interface_error_rec.p_interface_id := p_delivery_interface_id;
3576 l_loc_interface_error_rec.p_message_name := 'WSH_NO_LOCK';
3577
3578 Log_Errors(
3579 p_loc_interface_errors_rec => l_loc_interface_error_rec,
3580 p_api_name =>'Delivery_Interface_Wrapper, Action=CREATE' ,
3581 x_return_status => l_return_status);
3582
3583 IF l_debug_on THEN
3584 wsh_debug_sv.log (l_module_name,'Log_Errors l_return_status', l_return_status);
3585 END IF;
3586 raise no_lock_found;
3587 END IF;
3588
3589 END IF;
3590
3591 Process_Interfaced_Deliveries(
3592 p_delivery_interface_id => p_delivery_interface_id,
3593 p_action_code => l_action_code,
3594 x_dlvy_id => l_created_delivery_id,
3595 x_return_status => l_return_status);
3596
3597 IF l_debug_on THEN
3598 wsh_debug_sv.log (l_module_name, 'Return Status from Process Deliveries', l_return_status);
3599 END IF;
3600
3601 IF(l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
3602 raise process_delivery_failed;
3603 END IF;
3604
3605 IF(l_action_code = 'CREATE' AND l_created_delivery_id is NULL ) THEN
3606 raise process_delivery_failed;
3607 END IF;
3608
3609 -- set the out parameter
3610 x_delivery_id := l_created_delivery_id;
3611
3612 -- Now process the delivery details
3613 -- TPW - Distributed changes
3614 IF (nvl(WSH_INBOUND_SHIP_ADVICE_PKG.G_WAREHOUSE_TYPE, '!') = 'TW2') THEN
3615 Process_Interfaced_Del_Details(
3616 p_delivery_interface_id => p_delivery_interface_id,
3617 p_delivery_id => l_created_delivery_id,
3618 p_new_delivery_id => l_created_delivery_id,
3619 p_action_code => 'UPDATE',
3620 x_return_status => l_return_status);
3621
3622 ELSE
3623 Process_Interfaced_Del_Details(
3624 p_delivery_interface_id => p_delivery_interface_id,
3625 p_delivery_id => l_delivery_id,
3626 p_new_delivery_id => l_created_delivery_id,
3627 p_action_code => l_action_code,
3628 x_return_status => l_return_status);
3629 END IF;
3630
3631 IF l_debug_on THEN
3632 wsh_debug_sv.log (l_module_name, 'Return Status from Process Details', l_return_status);
3633 END IF;
3634
3635 IF(l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
3636 raise process_del_details_failed;
3637 END IF;
3638
3639 -- J: W/V Changes
3640 -- Need to update W/V for del here
3641 IF(l_action_code = 'UPDATE') THEN
3642 l_tmp_del_id := l_delivery_id;
3643 ELSE
3644 l_tmp_del_id := l_created_delivery_id;
3645 END IF;
3646
3647 /* Adjust the W/V on delivery
3648 as the dd assignment would have bumped(if del W/V is not frozen)
3649 up the W/V on del */
3650
3651 OPEN org_del_id(l_tmp_del_id);
3652 FETCH org_del_id
3653 INTO l_org_gross_weight,
3654 l_org_net_weight,
3655 l_org_volume;
3656 CLOSE org_del_id;
3657
3658 UPDATE wsh_new_deliveries
3659 SET gross_weight = l_gross_weight,
3660 net_weight = l_net_weight,
3661 volume = l_volume,
3662 wv_frozen_flag = l_wv_frozen_flag
3663 WHERE delivery_id = l_tmp_del_id;
3664
3665 WSH_WV_UTILS.Del_WV_Post_Process(
3666 p_delivery_id => l_tmp_del_id,
3667 p_diff_gross_wt => NVL(l_gross_weight,0) - l_org_gross_weight,
3668 p_diff_net_wt => NVL(l_net_weight,0) - l_org_net_weight,
3669 p_diff_volume => NVL(l_volume,0) - l_org_volume,
3670 x_return_status => l_return_status);
3671
3672 IF l_debug_on THEN
3673 wsh_debug_sv.log (l_module_name, 'Return status from WSH_WV_UTILS.Del_WV_Post_Process ',l_return_status);
3674 END IF;
3675
3676 IF(l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
3677
3678 l_loc_interface_error_rec.p_interface_table_name := 'WSH_NEW_DEL_INTERFACE';
3679 l_loc_interface_error_rec.p_interface_id := p_delivery_interface_id;
3680
3681 IF l_debug_on THEN
3682 wsh_debug_sv.log (l_module_name, 'Delivery Interface Id', p_delivery_interface_id);
3683 END IF;
3684
3685 Log_Errors(
3686 p_loc_interface_errors_rec => l_loc_interface_error_rec,
3687 p_msg_data => l_msg_data,
3688 p_api_name => 'WSH_INTERFACE_PUB.Delivery_Interface_Wrapper' ,
3689 x_return_status => l_return_status);
3690
3691 IF l_debug_on THEN
3692 wsh_debug_sv.log (l_module_name, 'Return status after log_errors', l_return_status);
3693 END IF;
3694 raise process_delivery_wv_failed;
3695 END IF;
3696
3697 ELSIF (l_action_code = 'CANCEL') THEN
3698
3699 -- for cancel case, we need a base delivery id
3700 -- hence raise error if it is null
3701 IF(x_delivery_id IS NULL) THEN
3702 raise invalid_input;
3703 ELSE
3704 l_delivery_id := x_delivery_id;
3705 END IF;
3706
3707 Process_Cancel(
3708 p_delivery_id => l_delivery_id,
3709 x_return_status => l_return_status);
3710
3711 IF l_debug_on THEN
3712 wsh_debug_sv.log (l_module_name, 'Return Status from Process Cancel', l_return_status);
3713 END IF;
3714
3715 IF(l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
3716 raise cancel_lines_failed;
3717 END IF;
3718
3719 ELSE
3720 raise invalid_action_code;
3721 END IF; -- if l_action_code
3722
3723 IF l_debug_on THEN
3724 wsh_debug_sv.pop(l_module_name);
3725 END IF;
3726
3727 EXCEPTION
3728 WHEN invalid_input THEN
3729 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3730 IF l_debug_on THEN
3731 WSH_DEBUG_SV.logmsg(l_module_name,'invalid_input exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3732 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_input');
3733 END IF;
3734 WHEN no_lock_found THEN
3735 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3736 IF l_debug_on THEN
3737 WSH_DEBUG_SV.logmsg(l_module_name,'no_lock_found exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3738 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:no_lock_found');
3739 END IF;
3740 WHEN invalid_action_code THEN
3741 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3742 IF l_debug_on THEN
3743 WSH_DEBUG_SV.logmsg(l_module_name,'invalid_action_code exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3744 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_action_code');
3745 END IF;
3746 WHEN process_delivery_failed THEN
3747 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3748 IF l_debug_on THEN
3749 WSH_DEBUG_SV.logmsg(l_module_name,'process_delivery_failed exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3750 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:process_delivery_failed');
3751 END IF;
3752 -- J: W/V Changes
3753 WHEN process_delivery_wv_failed THEN
3754 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3755 IF l_debug_on THEN
3756 WSH_DEBUG_SV.logmsg(l_module_name,'process_delivery_wv_failed exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3757 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:process_delivery_wv_failed');
3758 END IF;
3759 WHEN process_del_details_failed THEN
3760 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3761 IF l_debug_on THEN
3762 WSH_DEBUG_SV.logmsg(l_module_name,'process_del_details_failed exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3763 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:process_del_details_failed');
3764 END IF;
3765 WHEN cancel_lines_failed THEN
3766 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3767 IF l_debug_on THEN
3768 WSH_DEBUG_SV.logmsg(l_module_name,'cancel_lines_failed exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3769 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:cancel_lines_failed');
3770 END IF;
3771 WHEN Others THEN
3772 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3773 IF l_debug_on THEN
3774 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
3775 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3776 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3777 END IF;
3778
3779 END Delivery_Interface_Wrapper;
3780
3781
3782 /*
3783 -----------------------------------------------------------------------------
3784 PROCEDURE : Process_Int_Freight_Costs
3785 PARAMETERS : p_delivery_interface_id IN NUMBER DEFAULT NULL,
3786 p_del_detail_interface_id IN NUMBER DEFAULT NULL,
3787 p_delivery_detail_id IN NUMBER DEFAULT NULL,
3788 p_stop_interface_id IN NUMBER DEFAULT NULL,
3789 p_trip_interface_id IN NUMBER DEFAULT NULL,
3790 x_return_status - return status of API
3791 DESCRIPTION :
3792 -- This procedure takes the freight cost record from freight interface table
3793 and inserts into the base wsh_freight_costs table.
3794 -- This takes in as input the interface_id for a delivery or detail or stop or trip
3795 -- This will be called by the procedures for processing delivery and delivery details
3796
3797 ------------------------------------------------------------------------------
3798 */
3799
3800
3801 PROCEDURE Process_Int_Freight_Costs(
3802 p_delivery_interface_id IN NUMBER, -- DEFAULT NULL in spec,
3803 p_del_detail_interface_id IN NUMBER, -- DEFAULT NULL in spec
3804 -- TPW - Distributed changes
3805 p_delivery_detail_id IN NUMBER, -- DEFAULT NULL in spec
3806 p_stop_interface_id IN NUMBER, -- DEFAULT NULL in spec
3807 p_trip_interface_id IN NUMBER, -- DEFAULT NULL in spec
3808 x_return_status OUT NOCOPY VARCHAR2) IS
3809
3810 -- variables
3811 l_freight_costs_info WSH_FREIGHT_COSTS_PUB.PubFreightCostRecType;
3812 l_freight_cost_id NUMBER;
3813 l_fc_type_id NUMBER;
3814 l_freight_cost_int_id NUMBER;
3815
3816 l_msg_count NUMBER;
3817 l_msg_data VARCHAR2(3000);
3818 l_init_msg_list VARCHAR2(30) := NULL;
3819 l_commit VARCHAR2(1);
3820 l_return_status VARCHAR2(30);
3821
3822 l_loc_interface_error_rec WSH_INTERFACE_VALIDATIONS_PKG.interface_errors_rec_type;
3823 l_entity_interface_id NUMBER;
3824 l_delivery_id NUMBER;
3825 l_del_detail_id NUMBER;
3826 -- cursors
3827 CURSOR freight_int_cur IS
3828 SELECT FREIGHT_COST_INTERFACE_ID,
3829 FREIGHT_COST_ID,
3830 FREIGHT_COST_TYPE_ID,
3831 FREIGHT_COST_TYPE_CODE,
3832 UNIT_AMOUNT,
3833 CALCULATION_METHOD,
3834 UOM,
3835 QUANTITY,
3836 TOTAL_AMOUNT,
3837 CURRENCY_CODE,
3838 CONVERSION_DATE,
3839 CONVERSION_RATE,
3840 CONVERSION_TYPE_CODE,
3841 TRIP_INTERFACE_ID,
3842 STOP_INTERFACE_ID,
3843 DELIVERY_INTERFACE_ID,
3844 DELIVERY_LEG_INTERFACE_ID,
3845 DELIVERY_DETAIL_INTERFACE_ID,
3846 TRIP_ID,
3847 STOP_ID,
3848 DELIVERY_ID,
3849 DELIVERY_LEG_ID,
3850 ATTRIBUTE9,
3851 ATTRIBUTE10,
3852 ATTRIBUTE11,
3853 ATTRIBUTE12,
3854 ATTRIBUTE13,
3855 ATTRIBUTE6,
3856 ATTRIBUTE7,
3857 ATTRIBUTE8,
3858 ATTRIBUTE_CATEGORY,
3859 ATTRIBUTE1,
3860 ATTRIBUTE2,
3861 ATTRIBUTE3,
3862 ATTRIBUTE4,
3863 ATTRIBUTE5,
3864 DELIVERY_DETAIL_ID,
3865 ATTRIBUTE14,
3866 ATTRIBUTE15,
3867 CREATION_DATE,
3868 CREATED_BY,
3869 LAST_UPDATE_DATE,
3870 LAST_UPDATED_BY,
3871 LAST_UPDATE_LOGIN,
3872 PROGRAM_APPLICATION_ID,
3873 PROGRAM_ID,
3874 PROGRAM_UPDATE_DATE,
3875 REQUEST_ID,
3876 FREIGHT_CODE,
3877 INTERFACE_ACTION_CODE
3878 FROM wsh_freight_costs_interface
3879 WHERE (delivery_detail_interface_id = NVL(p_del_detail_interface_id, -99999))
3880 OR (delivery_interface_id = NVL(p_delivery_interface_id, -99999))
3881 OR (stop_interface_id = NVL(p_stop_interface_id, -99999))
3882 OR (trip_interface_id = NVL(p_trip_interface_id, -99999))
3883 AND INTERFACE_ACTION_CODE = '94X_INBOUND';
3884
3885 CURSOR fc_type_id(l_fc_type_code VARCHAR2) IS
3886 SELECT freight_cost_type_id
3887 FROM wsh_freight_cost_types
3888 WHERE name = l_fc_type_code;
3889
3890 --exceptions
3891 no_freight_record exception;
3892 invalid_input exception;
3893 invalid_freight_cost_type exception;
3894
3895 --
3896 l_debug_on BOOLEAN;
3897 --
3898 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PROCESS_INT_FREIGHT_COSTS';
3899 --
3900 BEGIN
3901 --
3902 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3903 --
3904 IF l_debug_on IS NULL
3905 THEN
3906 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3907 END IF;
3908 --
3909 IF l_debug_on THEN
3910 wsh_debug_sv.push(l_module_name,'Process_Int_Freight_Costs');
3911 wsh_debug_sv.log (l_module_name,'Delivery interface Id', p_delivery_interface_id);
3912 wsh_debug_sv.log (l_module_name,'del_detail_interface_id', p_del_detail_interface_id);
3913 wsh_debug_sv.log (l_module_name,'delivery detail id', p_delivery_detail_id);
3914 END IF;
3915
3916 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3917
3918
3919 IF(p_del_detail_interface_id IS NOT NULL) THEN
3920 IF(p_delivery_detail_id IS NULL) THEN
3921 IF l_debug_on THEN
3922 wsh_debug_sv.logmsg(l_module_name, 'Null Delivery Detail Id');
3923 END IF;
3924 raise invalid_input;
3925 END IF;
3926 ELSIF(p_delivery_interface_id IS NOT NULL) THEN
3927 l_entity_interface_id := p_delivery_interface_id;
3928 SELECT delivery_id INTO l_delivery_id
3929 FROM wsh_new_del_interface
3930 WHERE delivery_interface_id = p_delivery_interface_id
3931 AND INTERFACE_ACTION_CODE = '94X_INBOUND';
3932
3933 IF l_debug_on THEN
3934 wsh_debug_sv.log (l_module_name, 'Delivery Id', l_delivery_id);
3935 END IF;
3936 IF(l_delivery_id IS NULL) THEN
3937 IF l_debug_on THEN
3938 wsh_debug_sv.logmsg(l_module_name, 'Null Delivery Id');
3939 END IF;
3940 raise invalid_input;
3941 END IF;
3942
3943 ELSIF(p_stop_interface_id IS NOT NULL) THEN
3944 l_entity_interface_id := p_stop_interface_id;
3945 ELSIF(p_trip_interface_id IS NOT NULL) THEN
3946 l_entity_interface_id := p_trip_interface_id;
3947 ELSE
3948 IF l_debug_on THEN
3949 wsh_debug_sv.logmsg(l_module_name, 'Entity IDs are Null');
3950 END IF;
3951 raise invalid_input;
3952 END IF;
3953
3954 -- call the public api for creating freight costs
3955
3956 FOR l_freight_int_rec in freight_int_cur LOOP
3957
3958 IF l_debug_on THEN
3959 wsh_debug_sv.log (l_module_name, 'Freight Cost Type Code', l_freight_int_rec.freight_cost_type_code);
3960 END IF;
3961
3962 IF(l_freight_int_rec.freight_cost_type_code IS NOT NULL) THEN
3963 OPEN fc_type_id(l_freight_int_rec.freight_cost_type_code);
3964 FETCH fc_type_id INTO l_fc_type_id;
3965 CLOSE fc_type_id;
3966 ELSE
3967 raise invalid_freight_cost_type;
3968 END IF;
3969
3970 IF l_debug_on THEN
3971 wsh_debug_sv.log (l_module_name, 'Freight Cost Type Id', l_fc_type_id);
3972 END IF;
3973 IF(l_fc_type_id IS NULL) THEN
3974 IF l_debug_on THEN
3975 wsh_debug_sv.logmsg(l_module_name, 'Invalid Freight Cost Type');
3976 END IF;
3977
3978 l_loc_interface_error_rec.p_interface_table_name := 'WSH_FREIGHT_COSTS_INTERFACE';
3979 l_loc_interface_error_rec.p_interface_id := l_entity_interface_id;
3980
3981 Log_Errors(
3982 p_loc_interface_errors_rec => l_loc_interface_error_rec,
3983 p_msg_data => 'Invalid Freight Cost Type',
3984 p_api_name => 'WSH_FREIGHT_COSTS_PUB.Create_Update_Freight_Costs',
3985 x_return_status => l_return_status);
3986 IF l_debug_on THEN
3987 wsh_debug_sv.log (l_module_name, 'Log_Errors l_return_status',l_return_status);
3988 END IF;
3989 raise invalid_freight_cost_type;
3990 ELSE
3991 l_freight_costs_info.freight_cost_type_id := l_fc_type_id;
3992 END IF;
3993
3994 l_freight_costs_info.freight_cost_id := l_freight_int_rec.freight_cost_id;
3995
3996 l_freight_costs_info.unit_amount := l_freight_int_rec.unit_amount;
3997 l_freight_costs_info.currency_code := l_freight_int_rec.currency_code;
3998 l_freight_costs_info.conversion_date := l_freight_int_rec.conversion_date;
3999 l_freight_costs_info.conversion_rate := l_freight_int_rec.conversion_rate;
4000 l_freight_costs_info.conversion_type_code := l_freight_int_rec.conversion_type_code;
4001 l_freight_costs_info.trip_id := l_freight_int_rec.trip_id;
4002 l_freight_costs_info.stop_id := l_freight_int_rec.stop_id;
4003
4004 l_freight_costs_info.delivery_id := l_delivery_id;
4005 l_freight_costs_info.delivery_leg_id := l_freight_int_rec.delivery_leg_id;
4006 l_freight_costs_info.delivery_detail_id := p_delivery_detail_id;
4007
4008 l_freight_costs_info.attribute_category := l_freight_int_rec.attribute_category;
4009 l_freight_costs_info.attribute1 := l_freight_int_rec.attribute1;
4010 l_freight_costs_info.attribute2 := l_freight_int_rec.attribute2;
4011 l_freight_costs_info.attribute3 := l_freight_int_rec.attribute3;
4012 l_freight_costs_info.attribute4 := l_freight_int_rec.attribute4;
4013 l_freight_costs_info.attribute5 := l_freight_int_rec.attribute5;
4014 l_freight_costs_info.attribute6 := l_freight_int_rec.attribute6;
4015 l_freight_costs_info.attribute7 := l_freight_int_rec.attribute7;
4016 l_freight_costs_info.attribute8 := l_freight_int_rec.attribute8;
4017 l_freight_costs_info.attribute9 := l_freight_int_rec.attribute9;
4018 l_freight_costs_info.attribute10 := l_freight_int_rec.attribute10;
4019 l_freight_costs_info.attribute11 := l_freight_int_rec.attribute11;
4020 l_freight_costs_info.attribute12 := l_freight_int_rec.attribute12;
4021 l_freight_costs_info.attribute13 := l_freight_int_rec.attribute13;
4022 l_freight_costs_info.attribute14 := l_freight_int_rec.attribute14;
4023 l_freight_costs_info.attribute15 := l_freight_int_rec.attribute15;
4024
4025 IF l_debug_on THEN
4026 wsh_debug_sv.logmsg(l_module_name,'Calling freight public api');
4027 wsh_debug_sv.log (l_module_name, 'Unit amount', l_freight_costs_info.unit_amount);
4028 wsh_debug_sv.log (l_module_name, 'Currency code', l_freight_costs_info.currency_code);
4029 wsh_debug_sv.log (l_module_name, 'Delivery Id', l_freight_costs_info.delivery_id);
4030 wsh_debug_sv.log (l_module_name, 'Delivery Detail Id',l_freight_costs_info.delivery_detail_id);
4031 wsh_debug_sv.log (l_module_name, 'Trip Id', l_freight_costs_info.trip_id);
4032 wsh_debug_sv.log (l_module_name, 'Trip Name', l_freight_costs_info.trip_name);
4033 wsh_debug_sv.log (l_module_name, 'Stop Id', l_freight_costs_info.stop_id);
4034 END IF;
4035
4036 WSH_FREIGHT_COSTS_PUB.Create_Update_Freight_Costs (
4037 p_api_version_number => 1.0,
4038 p_init_msg_list => l_init_msg_list,
4039 p_commit => l_commit,
4040 x_return_status => l_return_status,
4041 x_msg_count => l_msg_count,
4042 x_msg_data => l_msg_data,
4043 p_pub_freight_costs => l_freight_costs_info,
4044 p_action_code => 'CREATE',
4045 x_freight_cost_id => l_freight_cost_id);
4046
4047 IF l_debug_on THEN
4048 wsh_debug_sv.log (l_module_name, 'return status from freight public api', l_return_status);
4049 wsh_debug_sv.log (l_module_name, 'Create Update Freight Costs api msg count', l_msg_count);
4050 wsh_debug_sv.log (l_module_name, 'Create Update Freight Costs api msg', l_msg_data);
4051 wsh_debug_sv.log (l_module_name, 'freight_cost id created', l_freight_cost_id);
4052 END IF;
4053
4054 IF(l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
4055 l_loc_interface_error_rec.p_interface_table_name := 'WSH_FREIGHT_COSTS_INTERFACE';
4056 l_loc_interface_error_rec.p_interface_id := l_entity_interface_id;
4057
4058 Log_Errors(
4059 p_loc_interface_errors_rec => l_loc_interface_error_rec,
4060 p_msg_data => l_msg_data,
4061 p_api_name => 'WSH_FREIGHT_COSTS_PUB.Create_Update_Freight_Costs',
4062 x_return_status => l_return_status);
4063 IF l_debug_on THEN
4064 wsh_debug_sv.log (l_module_name, 'Log_Errors l_return_status',l_return_status);
4065 END IF;
4066 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4067 END IF;
4068
4069 END LOOP;
4070
4071 IF l_debug_on THEN
4072 wsh_debug_sv.pop(l_module_name);
4073 END IF;
4074
4075 EXCEPTION
4076 WHEN invalid_input THEN
4077 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4078 IF l_debug_on THEN
4079 WSH_DEBUG_SV.logmsg(l_module_name,'invalid_input exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4080 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_input');
4081 END IF;
4082 WHEN invalid_freight_cost_type THEN
4083 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4084 IF l_debug_on THEN
4085 WSH_DEBUG_SV.logmsg(l_module_name,'invalid_freight_cost_type exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4086 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_freight_cost_type');
4087 END IF;
4088 WHEN no_freight_record THEN
4089 null;
4090 IF l_debug_on THEN
4091 WSH_DEBUG_SV.logmsg(l_module_name,'no_freight_record exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4092 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:no_freight_record');
4093 END IF;
4094 WHEN Others THEN
4095 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4096 IF l_debug_on THEN
4097 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
4098 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4099 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4100 END IF;
4101
4102 END Process_Int_Freight_Costs;
4103
4104
4105 /*
4106 -----------------------------------------------------------------------------
4107 PROCEDURE : Update_Delivery_Details
4108 PARAMETERS : p_changed_det_attributes IN WSH_INTERFACE.ChangedAttributeTabType
4109 x_return_status - return status of API
4110 DESCRIPTION :
4111 -- This is an internal procedure, used by Process_Interfaced_Del_Details
4112 -- This will be called for any updates of delivery details
4113
4114 -- history: 1/13/03 jckwok added a parameter for action_code to distinguish
4115 -- between UPDATE and CANCEL actions.
4116 ------------------------------------------------------------------------------
4117 */
4118
4119
4120 PROCEDURE Update_Delivery_Details(
4121 p_source_code IN VARCHAR2, -- DEFAULT 'OE' in spec
4122 p_delivery_interface_id IN NUMBER,
4123 p_action_code IN VARCHAR2, -- jckwok
4124 x_return_status OUT NOCOPY VARCHAR2
4125 ) IS
4126
4127 -- public api variables
4128 l_msg_count NUMBER;
4129 l_msg_data VARCHAR2(3000);
4130 l_init_msg_list VARCHAR2(30) := NULL;
4131 l_commit VARCHAR2(1) ;
4132 l_return_status VARCHAR2(30);
4133
4134 l_loc_interface_error_rec WSH_INTERFACE_VALIDATIONS_PKG.interface_errors_rec_type;
4135 l_DETAIL_INFO_TAB WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Attr_Tbl_Type;
4136 l_IN_REC WSH_GLBL_VAR_STRCT_GRP.detailInRecType;
4137 l_OUT_REC WSH_GLBL_VAR_STRCT_GRP.detailOutRecType;
4138 -- TPW - Distribution Organization Changes - Inv. Rsv API Integration Changes - Starts
4139 l_header_id NUMBER;
4140 l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
4141 l_serial_number_tbl inv_reservation_global.serial_number_tbl_type;
4142 -- TPW - Distribution Organization Changes - Inv. Rsv API Integration Changes - Ends
4143 --exceptions
4144 update_shipping_att_failed exception;
4145 cont_upd_ship_att_failed exception;
4146 --
4147 l_debug_on BOOLEAN;
4148 --
4149 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_DELIVERY_DETAILS';
4150 --
4151 BEGIN
4152 --
4153 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4154 --
4155 IF l_debug_on IS NULL
4156 THEN
4157 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4158 END IF;
4159 --
4160 IF l_debug_on THEN
4161 wsh_debug_sv.push(l_module_name,'Update_Delivery_Details');
4162 wsh_debug_sv.log (l_module_name, 'Source Code', p_source_code);
4163 wsh_debug_sv.log (l_module_name, 'Delivery Interface Id', p_delivery_interface_id);
4164 wsh_debug_sv.log (l_module_name, 'Update Table Count', G_Update_Attributes_Tab.count);
4165 wsh_debug_sv.log(l_module_name, 'Serial Range Tab count',G_SERIAL_RANGE_TAB.count);
4166 END IF;
4167
4168 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4169 -- Use the global table to call create_update Group API
4170
4171 IF(G_Update_Attributes_Tab.count > 0 ) THEN
4172 l_in_rec.caller := 'WSH_INBOUND';
4173 --jckwok: set UPDATE or CANCEL action codes
4174 l_in_rec.action_code := p_action_code;
4175 l_in_rec.phase := 1;
4176
4177 wsh_delivery_Details_grp.create_update_delivery_detail(
4178 P_API_VERSION_NUMBER => 1.0,
4179 P_INIT_MSG_LIST => FND_API.G_FALSE,
4180 P_COMMIT => FND_API.G_FALSE,
4181 x_RETURN_STATUS => l_RETURN_STATUS,
4182 X_MSG_COUNT => l_MSG_COUNT,
4183 X_MSG_DATA => l_MSG_DATA,
4184 P_DETAIL_INFO_TAB => G_Update_Attributes_Tab,
4185 P_IN_REC => l_IN_REC,
4186 X_OUT_REC => l_OUT_REC,
4187 P_SERIAL_RANGE_TAB => G_SERIAL_RANGE_TAB
4188 );
4189
4190 IF l_debug_on THEN
4191 wsh_debug_sv.log (l_module_name,'Return Status from create_update group api', l_return_status);
4192 wsh_debug_sv.log (l_module_name, 'Update Ship Attr api msg count', l_msg_count);
4193 wsh_debug_sv.log (l_module_name, 'Update Ship Attr api msg data', l_msg_data);
4194 END IF;
4195
4196 IF(l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
4197 -- Need to insert record in interface errors table only
4198 -- for 'OE' source code, i.e during 945 inbound
4199 -- For 940 inbound - cancel case, there may not be any
4200 -- data in interface tables.
4201
4202 IF(p_delivery_interface_id IS NOT NULL) THEN
4203 l_loc_interface_error_rec.p_interface_table_name := 'WSH_NEW_DEL_INTERFACE';
4204 l_loc_interface_error_rec.p_interface_id := p_delivery_interface_id;
4205
4206 Log_Errors(
4207 p_loc_interface_errors_rec => l_loc_interface_error_rec,
4208 p_msg_data => l_msg_data,
4209 p_api_name =>'WSH_DELIVERY_DETAILS_GRP.Create_Update_Delivery_Detail',
4210 x_return_status => l_return_status);
4211
4212 IF l_debug_on THEN
4213 wsh_debug_sv.log (l_module_name,'Log_Errors l_return_status',l_return_status);
4214 END IF;
4215
4216 END IF;
4217 raise update_shipping_att_failed;
4218 END IF;
4219
4220 -- TPW - Distribution Organization Changes - Inv. Rsv API Integration Changes - Starts
4221 IF ( nvl(WSH_INBOUND_SHIP_ADVICE_PKG.G_WAREHOUSE_TYPE, '!') = 'TW2' )
4222 THEN --{
4223 FOR i in G_Update_Attributes_Tab.first..G_Update_Attributes_Tab.last
4224 LOOP --{
4225 --
4226 IF l_debug_on THEN
4227 wsh_debug_sv.log (l_module_name, 'Container Flag for delivery detail: ' || G_Update_Attributes_Tab(i).delivery_detail_id, G_Update_Attributes_Tab(i).Container_Flag);
4228 END IF;
4229 --
4230 IF ( G_Update_Attributes_Tab(i).Container_Flag = 'N' )
4231 THEN -- {
4232 SELECT source_header_id,
4233 source_line_id,
4234 inventory_item_id,
4235 organization_id,
4236 requested_quantity_uom,
4237 DECODE(line_direction, 'IO', 8, 2),
4238 shipped_quantity,
4239 revision,
4240 subinventory,
4241 lot_number,
4242 locator_id
4243 INTO l_header_id,
4244 l_rsv_rec.demand_source_line_id,
4245 l_rsv_rec.inventory_item_id,
4246 l_rsv_rec.organization_id,
4247 l_rsv_rec.primary_uom_code,
4248 l_rsv_rec.demand_source_type_id,
4249 l_rsv_rec.primary_reservation_quantity,
4250 l_rsv_rec.revision,
4251 l_rsv_rec.subinventory_code,
4252 l_rsv_rec.lot_number,
4253 l_rsv_rec.locator_id
4254 FROM wsh_delivery_details
4255 WHERE delivery_detail_id = G_Update_Attributes_Tab(i).delivery_detail_id;
4256 --
4257 IF l_debug_on THEN
4258 wsh_debug_sv.logmsg (l_module_name,'Calling INV_SALESORDER.Get_Salesorder_For_Oeheader', WSH_DEBUG_SV.C_PROC_LEVEL );
4259 END IF;
4260 --
4261 l_rsv_rec.demand_source_header_id := INV_SALESORDER.Get_Salesorder_For_Oeheader(P_OE_HEADER_ID => l_header_id);
4262 --
4263 IF l_debug_on THEN
4264 wsh_debug_sv.logmsg (l_module_name,'Calling INV_RSV_DETAIL_STAGE_PVT.Process_Reservation', WSH_DEBUG_SV.C_PROC_LEVEL );
4265 END IF;
4266 --
4267 -- After discussing with Inventory team, its been decided that
4268 -- 1. Serial Numbers will NOT be passed in p_serial_number parameter,
4269 -- even if the item is Serial Controlled.
4270 -- 2. Return Status of Process_Reservation api will NOT be handled
4271 -- Documented the same in MED under Closed Issues
4272 INV_RSV_DETAIL_STAGE_PVT.Process_Reservation(
4273 p_api_version_number => 1.0,
4274 p_init_msg_lst => FND_API.G_TRUE,
4275 p_rsv_rec => l_rsv_rec,
4276 p_serial_number => l_serial_number_tbl,
4277 p_rsv_status => 'STAGE',
4278 x_return_status => l_return_status,
4279 x_msg_count => l_msg_count,
4280 x_msg_data => l_msg_data );
4281 --
4282 -- Bug 8579149 - Fixing the GSCC error introduced in previous fix
4283 IF l_debug_on THEN
4284 wsh_debug_sv.log (l_module_name, 'Return Status from INV_RSV_DETAIL_STAGE_PVT.Process_Reservation', l_return_status);
4285 wsh_debug_sv.log (l_module_name, 'Process_Reservation api msg count', l_msg_count);
4286 wsh_debug_sv.log (l_module_name, 'Process_Reservation api msg data', l_msg_data);
4287 END IF;
4288 --
4289 -- Return Status of Process_Reservation api will NOT be handled
4290 END IF; --} Container Flag
4291 END LOOP; --}
4292 END IF; --}
4293 -- TPW - Distribution Organization Changes - Inv. Rsv API Integration Changes - Ends
4294
4295 END IF; -- if G_Update_Attributes_Tab.count
4296
4297 IF l_debug_on THEN
4298 wsh_debug_sv.pop(l_module_name);
4299 END IF;
4300
4301 EXCEPTION
4302 WHEN update_shipping_att_failed THEN
4303 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4304 IF l_debug_on THEN
4305 WSH_DEBUG_SV.logmsg(l_module_name,'update_shipping_att_failed exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4306 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:update_shipping_att_failed');
4307 END IF;
4308 WHEN cont_upd_ship_att_failed THEN
4309 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4310 IF l_debug_on THEN
4311 WSH_DEBUG_SV.logmsg(l_module_name,'cont_upd_ship_att_failed exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4312 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:cont_upd_ship_att_failed');
4313 END IF;
4314 WHEN Others THEN
4315 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4316 IF l_debug_on THEN
4317 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
4318 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4319 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4320 END IF;
4321 END Update_Delivery_Details;
4322
4323
4324 /*
4325 -----------------------------------------------------------------------------
4326 PROCEDURE : Create_Update_Trip_For_Dlvy
4327 PARAMETERS : p_delivery_id
4328 x_return_status - return status of API
4329 DESCRIPTION :
4330 - This procedure is called to create/update the trip for the delivery
4331 which has been updated with the inbound 945 transaction data
4332 - If a trip already exists for the delivery in the base tables, then
4333 this procedure just updates the trip and trip_stop tables based on the
4334 values in the trip interface table and trip_stop interface table.
4335 - If a trip does not already exist, then this procedure first calls
4336 autocreate_trip to create a trip for the delivery.
4337 -- Then it updates the newly created trip and trip_stops with the
4338 values from the interface table data
4339
4340 ------------------------------------------------------------------------------
4341 */
4342
4343 PROCEDURE Create_Update_Trip_For_Dlvy(
4344 p_delivery_id IN NUMBER,
4345 x_pickup_stop_id OUT NOCOPY NUMBER,
4346 x_dropoff_stop_id OUT NOCOPY NUMBER,
4347 x_trip_id OUT NOCOPY NUMBER,
4348 x_return_status OUT NOCOPY VARCHAR2) IS
4349
4350 -- variables
4351 l_del_rows wsh_util_core.id_tab_type;
4352
4353 l_pickup_stop_id NUMBER ;
4354 l_dropoff_stop_id NUMBER ;
4355 l_delivery_leg_id NUMBER ;
4356
4357 l_trip_id NUMBER;
4358 l_trip_name VARCHAR2(30);
4359
4360 l_trip_interface_id NUMBER;
4361 l_stop_interface_id NUMBER;
4362
4363 l_pickup_stop_int_id NUMBER;
4364 l_dropoff_stop_int_id NUMBER;
4365
4366 l_return_status VARCHAR2(30);
4367
4368 l_stop_tab WSH_UTIL_CORE.id_tab_type; -- DBI Project
4369 l_dbi_rs VARCHAR2(1); -- DBI Project
4370
4371 -- cursors
4372 CURSOR del_trip_stops IS
4373 SELECT wdg.pick_up_stop_id, wdg.drop_off_stop_id, wts.trip_id
4374 FROM wsh_delivery_legs wdg, wsh_trip_stops wts
4375 WHERE wdg.delivery_id = p_delivery_id
4376 AND wdg.pick_up_stop_id = wts.stop_id;
4377
4378 CURSOR int_del_trip_stops IS
4379 SELECT wdli.pick_up_stop_interface_id, wdli.drop_off_stop_interface_id, wtsi.trip_interface_id
4380 FROM wsh_del_legs_interface wdli, wsh_trip_stops_interface wtsi
4381 WHERE wdli.delivery_id = p_delivery_id
4382 AND wdli.pick_up_stop_interface_id = wtsi.stop_interface_id
4383 AND WDLI.INTERFACE_ACTION_CODE = '94X_INBOUND'
4384 AND WTSI.INTERFACE_ACTION_CODE = '94X_INBOUND';
4385
4386
4387 CURSOR int_pickup_stop_cur(l_stop_interface_id NUMBER) IS
4388 SELECT actual_departure_date, departure_seal_code
4389 FROM wsh_trip_stops_interface
4390 WHERE stop_interface_id = l_stop_interface_id
4391 AND INTERFACE_ACTION_CODE = '94X_INBOUND';
4392
4393 CURSOR int_dropoff_stop_cur(l_stop_interface_id NUMBER) IS
4394 SELECT actual_arrival_date
4395 FROM wsh_trip_stops_interface
4396 WHERE stop_interface_id = l_stop_interface_id
4397 AND INTERFACE_ACTION_CODE = '94X_INBOUND';
4398
4399 CURSOR int_trip_cur(l_trip_interface_id NUMBER) IS
4400 SELECT vehicle_number, vehicle_num_prefix, route_id, routing_instructions,
4401 --Bug 3458160
4402 operator
4403 FROM wsh_trips_interface
4404 WHERE trip_interface_id = l_trip_interface_id
4405 AND INTERFACE_ACTION_CODE = '94X_INBOUND';
4406
4407 -- cursor records
4408 int_trip_rec int_trip_cur%ROWTYPE;
4409 int_dropoff_stop_rec int_dropoff_stop_cur%ROWTYPE;
4410 int_pickup_stop_rec int_pickup_stop_cur%ROWTYPE;
4411
4412 --exceptions
4413 trip_creation_failed exception;
4414 invalid_input exception;
4415 --
4416 l_debug_on BOOLEAN;
4417 --
4418 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_UPDATE_TRIP_FOR_DLVY';
4419 --
4420 BEGIN
4421 --
4422 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4423 --
4424 IF l_debug_on IS NULL
4425 THEN
4426 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4427 END IF;
4428 --
4429 IF l_debug_on THEN
4430 wsh_debug_sv.push(l_module_name,'Create_Update_Trip_For_Dlvy');
4431 wsh_debug_sv.log (l_module_name, 'Delivery id', p_delivery_id);
4432 END IF;
4433
4434 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4435
4436 IF(p_delivery_id IS NULL) THEN
4437 raise invalid_input;
4438 END IF;
4439
4440 -- check if a trip exists
4441 OPEN del_trip_stops;
4442 FETCH del_trip_stops INTO l_pickup_stop_id, l_dropoff_stop_id, l_trip_id;
4443 CLOSE del_trip_stops;
4444
4445 IF l_debug_on THEN
4446 wsh_debug_sv.log (l_module_name, 'Pickup Stop Id', l_pickup_stop_id);
4447 wsh_debug_sv.log (l_module_name, 'Dropoff Stop Id', l_dropoff_stop_id);
4448 wsh_debug_sv.log (l_module_name, 'Trip Id', l_trip_id);
4449 END IF;
4450 IF (l_pickup_stop_id IS NULL) THEN
4451
4452 -- trip does not exist. so do autocreate_trip
4453
4454 l_del_rows(1) := p_delivery_id;
4455
4456 wsh_trips_actions.autocreate_trip(
4457 p_del_rows => l_del_rows,
4458 x_trip_id => l_trip_id,
4459 x_trip_name => l_trip_name,
4460 x_return_status => l_return_status);
4461
4462 IF l_debug_on THEN
4463 wsh_debug_sv.log (l_module_name, 'Return status from autocreate trip', l_return_status);
4464 wsh_debug_sv.log (l_module_name, 'Trip created', l_trip_id);
4465 END IF;
4466
4467 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
4468 x_return_status := l_return_status;
4469 raise trip_creation_failed;
4470 END IF;
4471
4472 -- Now that a trip is created, get the stop and trip info
4473 OPEN del_trip_stops;
4474 FETCH del_trip_stops INTO l_pickup_stop_id, l_dropoff_stop_id, l_trip_id;
4475 CLOSE del_trip_stops;
4476
4477 IF l_debug_on THEN
4478 wsh_debug_sv.log (l_module_name, 'After doing autocreate trip');
4479 wsh_debug_sv.log (l_module_name, 'Pickup Stop Id', l_pickup_stop_id);
4480 wsh_debug_sv.log (l_module_name, 'Dropoff Stop Id', l_dropoff_stop_id);
4481 wsh_debug_sv.log (l_module_name, 'Trip Id', l_trip_id);
4482 END IF;
4483
4484 END IF; -- if l_pickup_stop_id is null
4485
4486 -- get the interface trip_stop_ids and trip_id
4487 OPEN int_del_trip_stops;
4488 FETCH int_del_trip_stops
4489 INTO l_pickup_stop_int_id, l_dropoff_stop_int_id, l_trip_interface_id;
4490 CLOSE int_del_trip_stops;
4491
4492 IF l_debug_on THEN
4493 wsh_debug_sv.log (l_module_name, 'Pickup Stop Interface Id', l_pickup_stop_int_id);
4494 wsh_debug_sv.log (l_module_name, 'Dropoff Stop Interface Id', l_dropoff_stop_int_id);
4495 wsh_debug_sv.log (l_module_name, 'Trip Interface Id', l_trip_interface_id);
4496 END IF;
4497
4498 -- get the interface trip_stop info
4499 OPEN int_pickup_stop_cur(l_pickup_stop_int_id);
4500 FETCH int_pickup_stop_cur INTO int_pickup_stop_rec;
4501
4502 IF(int_pickup_stop_cur%NOTFOUND) THEN
4503 NULL;
4504 -- need to decide what should be done
4505 END IF;
4506 IF l_debug_on THEN
4507 wsh_debug_sv.log (l_module_name, 'Updating Stop Id', l_pickup_stop_id);
4508 END IF;
4509
4510 -- update the base trip_stops
4511 IF l_pickup_stop_id IS NOT NULL THEN
4512
4513 UPDATE wsh_trip_stops
4514 SET actual_departure_date = int_pickup_stop_rec.actual_departure_date,
4515 departure_seal_code = int_pickup_stop_rec.departure_seal_code
4516 WHERE stop_id = l_pickup_stop_id;
4517
4518 --
4519 -- DBI Project
4520 -- Updating WSH_TRIP_STOPS.
4521 -- Call DBI API after the Update.
4522 -- This API will also check for DBI Installed or not
4523 IF l_debug_on THEN
4524 WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Stop id -',l_pickup_stop_id);
4525 END IF;
4526 l_stop_tab(1) := l_pickup_stop_id;
4527 WSH_INTEGRATION.DBI_Update_Trip_Stop_Log
4528 (p_stop_id_tab => l_stop_tab,
4529 p_dml_type => 'UPDATE',
4530 x_return_status => l_dbi_rs);
4531
4532 IF l_debug_on THEN
4533 WSH_DEBUG_SV.log(l_module_name,'Return Status after DBI Call-',l_dbi_rs);
4534 END IF;
4535 IF l_dbi_rs = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
4536 x_return_status := l_dbi_rs;
4537 -- just pass this return status to caller API
4538 IF l_debug_on THEN
4539 WSH_DEBUG_SV.log(l_module_name,'DBI API Returned Unexpected error '||x_return_status);
4540 WSH_DEBUG_SV.pop(l_module_name);
4541 END IF;
4542 return;
4543 END IF;
4544 -- End of Code for DBI Project
4545 --
4546
4547 x_pickup_stop_id := l_pickup_stop_id;
4548
4549 END IF;
4550
4551 IF(int_pickup_stop_cur%ISOPEN) THEN
4552 CLOSE int_pickup_stop_cur;
4553 END IF;
4554
4555 OPEN int_dropoff_stop_cur(l_dropoff_stop_int_id);
4556 FETCH int_dropoff_stop_cur INTO int_dropoff_stop_rec;
4557
4558 IF(int_dropoff_stop_cur%NOTFOUND) THEN
4559 NULL;
4560 -- need to decide what should be done
4561 END IF;
4562 IF l_debug_on THEN
4563 wsh_debug_sv.log (l_module_name, 'Updating Drop off Stop Id', l_dropoff_stop_id);
4564 END IF;
4565
4566 IF l_dropoff_stop_id IS NOT NULL THEN
4567 UPDATE wsh_trip_stops
4568 SET actual_arrival_date = int_dropoff_stop_rec.actual_arrival_date
4569 WHERE stop_id = l_dropoff_stop_id;
4570
4571 --
4572 -- DBI Project
4573 -- Updating WSH_TRIP_STOPS.
4574 -- Call DBI API after the Update.
4575 -- This API will also check for DBI Installed or not
4576 IF l_debug_on THEN
4577 WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Stop id -',l_dropoff_stop_id);
4578 END IF;
4579 l_stop_tab(1) := l_dropoff_stop_id;
4580 WSH_INTEGRATION.DBI_Update_Trip_Stop_Log
4581 (p_stop_id_tab => l_stop_tab,
4582 p_dml_type => 'UPDATE',
4583 x_return_status => l_dbi_rs);
4584
4585 IF l_debug_on THEN
4586 WSH_DEBUG_SV.log(l_module_name,'Return Status after DBI Call-',l_dbi_rs);
4587 END IF;
4588 IF l_dbi_rs = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
4589 x_return_status := l_dbi_rs;
4590 -- just pass this return status to caller API
4591 IF l_debug_on THEN
4592 WSH_DEBUG_SV.log(l_module_name,'DBI API Returned Unexpected error '||x_return_status);
4593 WSH_DEBUG_SV.pop(l_module_name);
4594 END IF;
4595 return;
4596 END IF;
4597 -- End of Code for DBI Project
4598 --
4599
4600 x_dropoff_stop_id := l_dropoff_stop_id;
4601 END IF;
4602
4603 IF(int_dropoff_stop_cur%ISOPEN) THEN
4604 CLOSE int_dropoff_stop_cur;
4605 END IF;
4606
4607 -- get the interface trip info
4608 OPEN int_trip_cur(l_trip_interface_id);
4609 FETCH int_trip_cur INTO int_trip_rec;
4610
4611 IF(int_trip_cur%NOTFOUND) THEN
4612 NULL;
4613 -- need to decide what should be done
4614 END IF;
4615
4616 IF l_debug_on THEN
4617 wsh_debug_sv.log (l_module_name, 'Updating Trip Id', l_trip_id);
4618 END IF;
4619 -- update the base trip
4620 IF l_trip_id IS NOT NULL THEN
4621
4622 UPDATE wsh_trips
4623 SET vehicle_num_prefix = int_trip_rec.vehicle_num_prefix,
4624 vehicle_number = int_trip_rec.vehicle_number,
4625 route_id = int_trip_rec.route_id,
4626 routing_instructions = int_trip_rec.routing_instructions,
4627 --Bug 3458160
4628 operator = int_trip_rec.operator
4629 WHERE trip_id = l_trip_id;
4630
4631 x_trip_id := l_trip_id;
4632
4633 END IF; -- if l_trip_id is not null
4634
4635 IF(int_trip_cur%ISOPEN) THEN
4636 CLOSE int_trip_cur;
4637 END IF;
4638
4639 IF l_debug_on THEN
4640 wsh_debug_sv.pop(l_module_name);
4641 END IF;
4642 EXCEPTION
4643 WHEN trip_creation_failed THEN
4644 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4645 IF l_debug_on THEN
4646 WSH_DEBUG_SV.logmsg(l_module_name,'trip_creation_failed exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4647 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:trip_creation_failed');
4648 END IF;
4649 WHEN invalid_input THEN
4650 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4651 IF l_debug_on THEN
4652 WSH_DEBUG_SV.logmsg(l_module_name,'invalid_input exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4653 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_input');
4654 END IF;
4655 WHEN Others THEN
4656 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4657 IF l_debug_on THEN
4658 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
4659 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4660 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4661 END IF;
4662
4663 END Create_Update_Trip_For_Dlvy;
4664
4665
4666 PROCEDURE Int_Trip_Stop_Info(
4667 p_delivery_interface_id IN NUMBER,
4668 p_act_dep_date IN DATE,
4669 p_dep_seal_code IN VARCHAR2,
4670 p_act_arr_date IN DATE,
4671 p_trip_vehicle_num IN VARCHAR2,
4672 p_trip_veh_num_pfx IN VARCHAR2,
4673 p_trip_route_id IN NUMBER,
4674 p_trip_routing_ins IN VARCHAR2,
4675 --Bug 3458160
4676 p_operator IN VARCHAR2,
4677 x_return_status OUT NOCOPY VARCHAR2) IS
4678
4679 -- variables
4680 l_del_leg_interface_id NUMBER;
4681 l_pickup_stop_interface_id NUMBER;
4682 l_dropoff_stop_interface_id NUMBER;
4683 l_trip_interface_id NUMBER;
4684
4685 --
4686 l_debug_on BOOLEAN;
4687 --
4688 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'INT_TRIP_STOP_INFO';
4689 --
4690 BEGIN
4691 --
4692 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4693 --
4694 IF l_debug_on IS NULL
4695 THEN
4696 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4697 END IF;
4698 --
4699 IF l_debug_on THEN
4700 wsh_debug_sv.push(l_module_name,'Int_Trip_Stop_Info');
4701 wsh_debug_sv.log (l_module_name, 'Delivery Interface id', p_delivery_interface_id);
4702 wsh_debug_sv.log (l_module_name, 'Act Departure Date', p_act_dep_date);
4703 wsh_debug_sv.log (l_module_name, 'Dep Seal Code', p_dep_seal_code);
4704 wsh_debug_sv.log (l_module_name, 'Act Arrival Date', p_act_arr_date);
4705 wsh_debug_sv.log (l_module_name, 'Vehicle Num', p_trip_vehicle_num);
4706 wsh_debug_sv.log (l_module_name, 'Vehicle Num Prefix', p_trip_veh_num_pfx);
4707 wsh_debug_sv.log (l_module_name, 'Route Id', p_trip_route_id);
4708 wsh_debug_sv.log (l_module_name, 'Routing Ins', p_trip_routing_ins);
4709 wsh_debug_sv.log (l_module_name, 'p_operator', p_operator);
4710 END IF;
4711
4712 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4713 -- get delivery_leg_interface_id, stop_interface_id, trip_interface_id
4714
4715 SELECT
4716 WSH_DEL_LEGS_INTERFACE_S.nextval,
4717 WSH_TRIP_STOPS_INTERFACE_S.nextval,
4718 WSH_TRIPS_INTERFACE_S.nextval
4719 INTO l_del_leg_interface_id,
4720 l_pickup_stop_interface_id,
4721 l_trip_interface_id
4722 FROM dual;
4723
4724 SELECT
4725 WSH_TRIP_STOPS_INTERFACE_S.nextval
4726 INTO l_dropoff_stop_interface_id
4727 FROM dual;
4728
4729 -- insert record into wsh_del_legs_interface
4730
4731 INSERT into wsh_del_legs_interface(
4732 DELIVERY_LEG_INTERFACE_ID,
4733 DELIVERY_INTERFACE_ID,
4734 PICK_UP_STOP_INTERFACE_ID,
4735 DROP_OFF_STOP_INTERFACE_ID,
4736 CREATION_DATE,
4737 CREATED_BY,
4738 LAST_UPDATE_DATE,
4739 LAST_UPDATED_BY,
4740 INTERFACE_ACTION_CODE)
4741 VALUES(
4742 l_del_leg_interface_id,
4743 p_delivery_interface_id,
4744 l_pickup_stop_interface_id,
4745 l_dropoff_stop_interface_id,
4746 SYSDATE,
4747 FND_GLOBAL.USER_ID,
4748 SYSDATE,
4749 FND_GLOBAL.USER_ID,
4750 '94X_INBOUND');
4751
4752 -- insert records into wsh_trip_stops_interface
4753 -- first the pickup stop
4754 INSERT INTO wsh_trip_stops_interface(
4755 STOP_INTERFACE_ID,
4756 TRIP_INTERFACE_ID,
4757 ACTUAL_DEPARTURE_DATE,
4758 DEPARTURE_SEAL_CODE,
4759 CREATION_DATE,
4760 CREATED_BY,
4761 LAST_UPDATE_DATE,
4762 LAST_UPDATED_BY,
4763 INTERFACE_ACTION_CODE)
4764 VALUES(
4765 l_pickup_stop_interface_id,
4766 l_trip_interface_id,
4767 p_act_dep_date,
4768 p_dep_seal_code,
4769 SYSDATE,
4770 FND_GLOBAL.USER_ID,
4771 SYSDATE,
4772 FND_GLOBAL.USER_ID,
4773 '94X_INBOUND');
4774
4775 -- then the dropoff stop
4776 INSERT INTO wsh_trip_stops_interface(
4777 STOP_INTERFACE_ID,
4778 TRIP_INTERFACE_ID,
4779 ACTUAL_ARRIVAL_DATE,
4780 CREATION_DATE,
4781 CREATED_BY,
4782 LAST_UPDATE_DATE,
4783 LAST_UPDATED_BY,
4784 INTERFACE_ACTION_CODE)
4785 VALUES(
4786 l_dropoff_stop_interface_id,
4787 l_trip_interface_id,
4788 p_act_arr_date,
4789 SYSDATE,
4790 FND_GLOBAL.USER_ID,
4791 SYSDATE,
4792 FND_GLOBAL.USER_ID,
4793 '94X_INBOUND');
4794
4795 -- insert records into wsh_trips_interface
4796 INSERT INTO wsh_trips_interface(
4797 TRIP_INTERFACE_ID,
4798 VEHICLE_NUM_PREFIX,
4799 VEHICLE_NUMBER,
4800 ROUTE_ID,
4801 ROUTING_INSTRUCTIONS,
4802 CREATION_DATE,
4803 CREATED_BY,
4804 LAST_UPDATE_DATE,
4805 LAST_UPDATED_BY,
4806 INTERFACE_ACTION_CODE,
4807 --Bug 3458160
4808 operator)
4809 VALUES (
4810 l_trip_interface_id,
4811 p_trip_veh_num_pfx,
4812 p_trip_vehicle_num,
4813 p_trip_route_id,
4814 p_trip_routing_ins,
4815 SYSDATE,
4816 FND_GLOBAL.USER_ID,
4817 SYSDATE,
4818 FND_GLOBAL.USER_ID,
4819 '94X_INBOUND',
4820 p_operator);
4821
4822 IF l_debug_on THEN
4823 wsh_debug_sv.pop(l_module_name);
4824 END IF;
4825 EXCEPTION
4826
4827 WHEN Others THEN
4828 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4829 IF l_debug_on THEN
4830 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
4831 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4832 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4833 END IF;
4834 END Int_Trip_Stop_Info;
4835
4836
4837 PROCEDURE Add_To_Update_Table
4838 (p_del_det_int_rec IN del_det_int_cur%ROWTYPE,
4839 p_update_mode IN VARCHAR2 DEFAULT 'UPDATE',
4840 p_delivery_id IN NUMBER,
4841 x_return_status OUT NOCOPY VARCHAR2) IS
4842
4843 -- variables
4844 --l_changed_attributes WSH_INTERFACE.ChangedAttributeRecType;
4845 l_changed_attributes WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Rec_Type;
4846 l_packing_detail_rec WSH_INTERFACE_COMMON_ACTIONS.PackingDetailRecType;
4847 l_intf_parent_det_id NUMBER;
4848
4849 -- TPW - Distributed changes
4850 l_container_flag VARCHAR2(1);
4851
4852 l_delivery_detail_id NUMBER;
4853 l_subinventory wsh_delivery_details.subinventory%type;
4854 l_locator_id NUMBER;
4855 l_wms_installed varchar2(1);
4856
4857 -- cursors
4858 CURSOR base_detail_cur(l_cont_inst_id NUMBER) IS
4859 SELECT wdd.delivery_detail_id
4860 FROM wsh_delivery_details wdd
4861 WHERE source_line_id = l_cont_inst_id
4862 AND wdd.source_code = 'WSH'
4863 AND wdd.container_flag = 'Y'
4864 AND wdd.organization_id = p_del_det_int_rec.organization_id;
4865
4866 CURSOR intf_parent_det_cur IS
4867 SELECT wdai.parent_delivery_detail_id, wddi.container_flag
4868 FROM wsh_del_assgn_interface wdai, wsh_del_details_interface wddi
4869 WHERE wdai.delivery_detail_interface_id = p_del_det_int_rec.delivery_detail_interface_id
4870 AND wdai.delivery_detail_interface_id = wddi.delivery_detail_interface_id
4871 AND wddi.INTERFACE_ACTION_CODE = '94X_INBOUND'
4872 AND WDAI.INTERFACE_ACTION_CODE = '94X_INBOUND';
4873
4874 --bugfix 8841528 added cursor
4875 CURSOR inv_detail_cur IS
4876 SELECT wdd.delivery_detail_id,wdd.subinventory, wdd.locator_id
4877 FROM wsh_delivery_details wdd
4878 WHERE source_line_id = p_del_det_int_rec.delivery_detail_id
4879 AND wdd.source_code = 'WSH'
4880 AND wdd.container_flag = 'Y'
4881 AND wdd.organization_id = p_del_det_int_rec.organization_id;
4882
4883
4884 --exceptions
4885 packing_error exception;
4886
4887 --
4888 l_debug_on BOOLEAN;
4889 --
4890 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'ADD_TO_UPDATE_TABLE';
4891 --
4892 BEGIN
4893 --
4894 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4895 --
4896 IF l_debug_on IS NULL
4897 THEN
4898 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4899 END IF;
4900 --
4901 IF l_debug_on THEN
4902 wsh_debug_sv.push(l_module_name, 'Add_To_Update_Table');
4903 wsh_debug_sv.log (l_module_name, 'Update Mode' , p_update_mode);
4904 wsh_debug_sv.log (l_module_name, 'p_delivery_id', p_delivery_id);
4905 wsh_debug_sv.log(l_module_name, 'Delivery Detail Id', p_del_det_int_rec.delivery_detail_id);
4906 END IF;
4907
4908 -- Add To Packing Table, only for update cases
4909 IF(p_update_mode = 'UPDATE') THEN
4910 OPEN intf_parent_det_cur;
4911 FETCH intf_parent_det_cur INTO l_intf_parent_det_id, l_container_flag;
4912 CLOSE intf_parent_det_cur;
4913
4914 IF l_debug_on THEN
4915 wsh_debug_sv.log (l_module_name, 'l_container_flag', l_container_flag);
4916 wsh_debug_sv.log (l_module_name, 'Parent del.detail in intef assgn', l_intf_parent_det_id);
4917 wsh_debug_sv.log(l_module_name, 'Organization id of detail', p_del_det_int_rec.organization_id);
4918 END IF;
4919
4920 IF(l_intf_parent_det_id IS NOT NULL) THEN
4921
4922 l_packing_detail_rec.delivery_detail_id := p_del_det_int_rec.delivery_detail_id;
4923 l_packing_detail_rec.src_container_flag := l_container_flag;
4924
4925 OPEN base_detail_cur(l_intf_parent_det_id);
4926 FETCH base_detail_cur INTO l_packing_detail_rec.parent_delivery_detail_id;
4927
4928 IF l_debug_on THEN
4929 wsh_debug_sv.log (l_module_name, 'Base table detail id', l_packing_detail_rec.parent_delivery_detail_id);
4930 END IF;
4931
4932 IF( base_detail_cur%NOTFOUND) THEN
4933 raise packing_error;
4934 END IF;
4935
4936 CLOSE base_detail_cur;
4937
4938 G_Packing_Detail_Tab((G_Packing_Detail_Tab.count) +1) := l_packing_detail_rec;
4939
4940 END IF; -- if l_intf_parent_det_id
4941 END IF; -- if p_udpate_mode is UPDATE
4942
4943 l_changed_attributes.source_header_id := p_del_det_int_rec.source_header_id;
4944 l_changed_attributes.source_line_id := p_del_det_int_rec.source_line_id;
4945 l_changed_attributes.source_code := p_del_det_int_rec.source_code;
4946 -- l_changed_attributes.sold_to_org_id := p_del_det_int_rec.sold_to_org_id;
4947 -- l_changed_attributes.customer_number := p_del_det_int_rec.customer_number;
4948 l_changed_attributes.sold_to_contact_id := p_del_det_int_rec.sold_to_contact_id;
4949 -- l_changed_attributes.ship_from_org_id := p_del_det_int_rec.ship_from_org_id;
4950 -- l_changed_attributes.ship_to_org_id := p_del_det_int_rec.ship_to_location_id;
4951 l_changed_attributes.ship_to_contact_id := p_del_det_int_rec.ship_to_contact_id;
4952 -- l_changed_attributes.deliver_to_org_id := p_del_det_int_rec.deliver_to_org_id;
4953 l_changed_attributes.deliver_to_contact_id := p_del_det_int_rec.deliver_to_contact_id;
4954 -- l_changed_attributes.intmed_ship_to_org_id := p_del_det_int_rec.intmed_ship_to_org_id;
4955 l_changed_attributes.intmed_ship_to_contact_id := p_del_det_int_rec.intmed_ship_to_contact_id;
4956 l_changed_attributes.preferred_grade := p_del_det_int_rec.preferred_grade;
4957
4958 --bugfix 8841528
4959
4960 IF(nvl(WSH_INBOUND_SHIP_ADVICE_PKG.G_WAREHOUSE_TYPE, FND_API.G_MISS_CHAR) = 'CMS') AND (p_del_det_int_rec.container_flag = 'Y') THEN
4961
4962 OPEN inv_detail_cur;
4963 FETCH inv_detail_cur INTO l_delivery_detail_id,l_subinventory,l_locator_id;
4964 CLOSE inv_detail_cur;
4965 --
4966 IF l_debug_on THEN
4967 wsh_debug_sv.log (l_module_name, 'Base table delivery detail id', l_delivery_detail_id);
4968 wsh_debug_sv.log (l_module_name, 'Base table subinventory', l_subinventory);
4969 wsh_debug_sv.log (l_module_name, 'Base table locator ', l_locator_id);
4970 END IF;
4971 --
4972 l_changed_attributes.subinventory := nvl(p_del_det_int_rec.subinventory,l_subinventory);
4973
4974 l_wms_installed := wsh_util_validate.Check_Wms_Org(p_del_det_int_rec.organization_id);
4975
4976 --
4977 IF l_wms_installed = 'Y' THEN
4978
4979 l_changed_attributes.locator_id := nvl(p_del_det_int_rec.locator_id,l_locator_id);
4980
4981 ELSE
4982 l_changed_attributes.locator_id := p_del_det_int_rec.locator_id;
4983
4984 END IF ;
4985 --
4986 ELSE
4987
4988 l_changed_attributes.subinventory := p_del_det_int_rec.subinventory;
4989 l_changed_attributes.locator_id := p_del_det_int_rec.locator_id;
4990 END IF;
4991
4992 l_changed_attributes.revision := p_del_det_int_rec.revision;
4993 l_changed_attributes.lot_number := p_del_det_int_rec.lot_number;
4994 -- HW OPMCONV - No need for sublot_number
4995 -- l_changed_attributes.sublot_number := p_del_det_int_rec.sublot_number;
4996 l_changed_attributes.customer_requested_lot_flag := p_del_det_int_rec.customer_requested_lot_flag;
4997 /* --kvenkate commenting the following line in patchset I
4998 l_changed_attributes.serial_number := nvl(p_del_det_int_rec.serial_number, FND_API.G_MISS_CHAR);
4999 */
5000 l_changed_attributes.serial_number := p_del_det_int_rec.serial_number;
5001 l_changed_attributes.master_container_item_id := p_del_det_int_rec.master_container_item_id;
5002 l_changed_attributes.detail_container_item_id := p_del_det_int_rec.detail_container_item_id;
5003
5004
5005 -- No need to pass ship method code because when a delivery is present, the ship method code of
5006 -- delivery detail should not be updated.
5007 -- l_changed_attributes.shipping_method_code := p_del_det_int_rec.ship_method_code;
5008 l_changed_attributes.carrier_id := p_del_det_int_rec.carrier_id;
5009
5010 -- l_changed_attributes.freight_terms_code := p_del_det_int_rec.freight_terms_code;
5011 l_changed_attributes.shipment_priority_code := p_del_det_int_rec.shipment_priority_code;
5012 -- l_changed_attributes.fob_code := p_del_det_int_rec.fob_code;
5013 l_changed_attributes.dep_plan_required_flag := p_del_det_int_rec.dep_plan_required_flag;
5014 l_changed_attributes.customer_prod_seq := p_del_det_int_rec.customer_prod_seq;
5015 l_changed_attributes.customer_dock_code := p_del_det_int_rec.customer_dock_code;
5016 l_changed_attributes.gross_weight := p_del_det_int_rec.gross_weight;
5017 l_changed_attributes.net_weight := p_del_det_int_rec.net_weight;
5018 l_changed_attributes.weight_uom_code := p_del_det_int_rec.weight_uom_code;
5019 l_changed_attributes.volume := p_del_det_int_rec.volume;
5020 l_changed_attributes.volume_uom_code := p_del_det_int_rec.volume_uom_code;
5021 -- J: W/V Changes
5022 l_changed_attributes.filled_volume := p_del_det_int_rec.filled_volume;
5023 l_changed_attributes.fill_percent := p_del_det_int_rec.fill_percent;
5024 l_changed_attributes.wv_frozen_flag := p_del_det_int_rec.wv_frozen_flag;
5025
5026 l_changed_attributes.top_model_line_id := p_del_det_int_rec.top_model_line_id;
5027 l_changed_attributes.ato_line_id := p_del_det_int_rec.ato_line_id;
5028 l_changed_attributes.arrival_set_id := p_del_det_int_rec.arrival_set_id;
5029 l_changed_attributes.ship_model_complete_flag := p_del_det_int_rec.ship_model_complete_flag;
5030 l_changed_attributes.cust_po_number := p_del_det_int_rec.cust_po_number;
5031 l_changed_attributes.packing_instructions := p_del_det_int_rec.packing_instructions;
5032 l_changed_attributes.shipping_instructions := p_del_det_int_rec.shipping_instructions;
5033 IF l_debug_on THEN
5034 wsh_debug_sv.log(l_module_name,'Orig Container Name',
5035 p_del_det_int_rec.container_name);
5036 wsh_debug_sv.log(l_module_name,'delivery detail id',
5037 p_del_det_int_rec.delivery_detail_id);
5038 wsh_debug_sv.log(l_module_name,'container flag',
5039 p_del_det_int_rec.container_flag);
5040 END IF;
5041
5042 --Bugfix 11070825 removed concatenation of dd to container name in case of CMS org
5043 IF (p_del_det_int_rec.container_flag = 'Y' )
5044 AND (NVL(p_del_det_int_rec.container_name ,FND_API.G_MISS_CHAR) <>
5045 FND_API.G_MISS_CHAR)
5046 AND (NVL(p_del_det_int_rec.delivery_detail_id,FND_API.G_MISS_NUM) <>
5047 FND_API.G_MISS_NUM)
5048 AND (nvl(WSH_INBOUND_SHIP_ADVICE_PKG.G_WAREHOUSE_TYPE, FND_API.G_MISS_CHAR) <> 'CMS')
5049 THEN
5050 l_changed_attributes.container_name :=
5051 SUBSTRB(p_del_det_int_rec.container_name ,1,30);
5052 ELSE
5053 l_changed_attributes.container_name :=
5054 nvl(p_del_det_int_rec.container_name,FND_API.G_MISS_CHAR);
5055 END IF;
5056
5057 IF l_debug_on THEN
5058 wsh_debug_sv.log(l_module_name,'New container name',
5059 l_changed_attributes.container_name);
5060 END IF;
5061
5062 l_changed_attributes.container_flag := p_del_det_int_rec.container_flag ;
5063 l_changed_attributes.delivery_detail_id := p_del_det_int_rec.delivery_detail_id;
5064
5065 IF l_debug_on THEN
5066 wsh_debug_sv.log (l_module_name, 'Shipped Quantity ', p_del_det_int_rec.shipped_quantity);
5067 END IF;
5068
5069 IF nvl(p_del_det_int_rec.shipped_quantity,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM THEN
5070 l_changed_attributes.shipped_quantity := 0;
5071 ELSE
5072 l_changed_attributes.shipped_quantity := p_del_det_int_rec.shipped_quantity;
5073 END IF;
5074 l_changed_attributes.cycle_count_quantity := p_del_det_int_rec.cycle_count_quantity;
5075 l_changed_attributes.tracking_number := p_del_det_int_rec.tracking_number ;
5076 l_changed_attributes.attribute1 := p_del_det_int_rec.attribute1;
5077 l_changed_attributes.attribute2 := p_del_det_int_rec.attribute2;
5078 l_changed_attributes.attribute3 := p_del_det_int_rec.attribute3;
5079 l_changed_attributes.attribute4 := p_del_det_int_rec.attribute4;
5080 l_changed_attributes.attribute5 := p_del_det_int_rec.attribute5;
5081 l_changed_attributes.attribute6 := p_del_det_int_rec.attribute6;
5082 l_changed_attributes.attribute7 := p_del_det_int_rec.attribute7;
5083 l_changed_attributes.attribute8 := p_del_det_int_rec.attribute8;
5084 l_changed_attributes.attribute9 := p_del_det_int_rec.attribute9;
5085 l_changed_attributes.attribute10 := p_del_det_int_rec.attribute10;
5086 l_changed_attributes.attribute11 := p_del_det_int_rec.attribute11;
5087 l_changed_attributes.attribute12 := p_del_det_int_rec.attribute12;
5088 l_changed_attributes.attribute13 := p_del_det_int_rec.attribute13;
5089 l_changed_attributes.attribute14 := p_del_det_int_rec.attribute14;
5090 l_changed_attributes.attribute15 := p_del_det_int_rec.attribute15;
5091 l_changed_attributes.to_serial_number := p_del_det_int_rec.to_serial_number;
5092 l_changed_attributes.requested_quantity_uom := p_del_det_int_rec.requested_quantity_uom;
5093
5094 if l_debug_on then
5095 wsh_debug_sv.log(l_module_name, 'subinventory:', l_changed_attributes.subinventory);
5096 wsh_debug_sv.log(l_module_name, 'Container Name:', l_changed_attributes.container_name);
5097 wsh_debug_sv.log(l_module_name, 'Tracking Number', l_changed_attributes.tracking_number);
5098 end if;
5099
5100
5101
5102 G_Update_Attributes_Tab((G_Update_Attributes_Tab.count)+1) := l_changed_attributes;
5103
5104
5105 /* Patchset I: passing serial numbers to group api. so no need for the direct update of to_serial_number
5106 -- kvenkate. Removed the code for direct update.
5107 */
5108
5109 IF l_debug_on THEN
5110 wsh_debug_sv.pop(l_module_name);
5111 END IF;
5112
5113 EXCEPTION
5114 WHEN packing_error THEN
5115 IF(base_detail_cur%ISOPEN) THEN
5116 CLOSE base_detail_cur;
5117 END IF;
5118 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5119 IF l_debug_on THEN
5120 WSH_DEBUG_SV.logmsg(l_module_name,'packing_error exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
5121 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:packing_error');
5122 END IF;
5123
5124 WHEN Others THEN
5125 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
5126 IF l_debug_on THEN
5127 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
5128 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
5129 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
5130 END IF;
5131
5132 END Add_To_Update_Table;
5133
5134
5135 PROCEDURE Process_Cancel(
5136 p_delivery_id IN NUMBER,
5137 x_return_status OUT NOCOPY VARCHAR2) IS
5138
5139 CURSOR del_details_cur IS
5140 SELECT wdd.delivery_detail_id,
5141 wdd.source_line_id,
5142 wdd.source_code,
5143 wdd.container_flag,
5144 wdd.requested_quantity_uom
5145 FROM wsh_delivery_details wdd,
5146 wsh_delivery_assignments_v wda
5147 WHERE wdd.delivery_detail_id = wda.delivery_detail_id
5148 AND wda.delivery_id = p_delivery_id;
5149
5150 l_del_det_int_rec del_det_int_cur%ROWTYPE;
5151 l_return_status VARCHAR2(30);
5152
5153 --
5154 l_debug_on BOOLEAN;
5155 --
5156 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PROCESS_CANCEL';
5157 --
5158 BEGIN
5159 --
5160 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
5161 --
5162 IF l_debug_on IS NULL
5163 THEN
5164 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
5165 END IF;
5166 --
5167 IF l_debug_on THEN
5168 wsh_debug_sv.push(l_module_name, 'Process_Cancel');
5169 wsh_debug_sv.log (l_module_name, 'Delivery Id', p_delivery_id);
5170 END IF;
5171
5172 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
5173
5174 -- select the delivery lines
5175 -- Add to the global update table
5176
5177 FOR del_details_rec IN del_details_cur LOOP
5178
5179 l_del_det_int_rec.delivery_detail_id := del_details_rec.delivery_detail_id;
5180 l_del_det_int_rec.source_line_id := del_details_rec.source_line_id;
5181 l_del_det_int_rec.source_code := del_details_rec.source_code;
5182 l_del_det_int_rec.container_flag := del_details_rec.container_flag;
5183 l_del_det_int_rec.requested_quantity_uom := del_details_rec.requested_quantity_uom;
5184 IF l_debug_on THEN
5185 wsh_debug_sv.log (l_module_name, 'For cancel, delivery detail id',l_del_det_int_rec.delivery_detail_id);
5186 END IF;
5187
5188 Add_To_Update_Table(
5189 l_del_det_int_rec,
5190 'CANCEL',
5191 p_delivery_id,
5192 l_return_status);
5193
5194 IF l_debug_on THEN
5195 wsh_debug_sv.log (l_module_name, 'return status from add_to_update_tbl', l_return_status);
5196 END IF;
5197
5198 END LOOP; -- for del_details_rec
5199
5200
5201 -- call update_delivery_details
5202 Update_Delivery_Details(
5203 p_source_code => 'WSH',
5204 p_action_code => 'CANCEL',
5205 x_return_status => l_return_status
5206 );
5207
5208 IF l_debug_on THEN
5209 wsh_debug_sv.log (l_module_name, 'Update_Delivery_Details l_return_status',l_return_status);
5210 END IF;
5211
5212 IF l_debug_on THEN
5213 wsh_debug_sv.pop(l_module_name);
5214 END IF;
5215
5216 EXCEPTION
5217 WHEN Others THEN
5218 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
5219 IF l_debug_on THEN
5220 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
5221 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
5222 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
5223 END IF;
5224
5225 END Process_Cancel;
5226
5227 PROCEDURE Lock_Delivery_And_Details(
5228 p_delivery_id IN NUMBER,
5229 x_return_status OUT NOCOPY VARCHAR2) IS
5230
5231 l_dummy_id NUMBER;
5232
5233 CURSOR lock_delivery_details IS
5234 SELECT wdd.delivery_detail_id
5235 FROM wsh_delivery_details wdd, wsh_delivery_assignments_v wda
5236 WHERE wdd.delivery_detail_id = wda.delivery_detail_id
5237 AND wda.delivery_id = p_delivery_id
5238 FOR UPDATE NOWAIT;
5239
5240 det_ids lock_delivery_details%ROWTYPE;
5241 RECORD_LOCKED EXCEPTION;
5242 PRAGMA EXCEPTION_INIT(RECORD_LOCKED, -54);
5243
5244 --
5245 l_debug_on BOOLEAN;
5246 --
5247 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOCK_DELIVERY_AND_DETAILS';
5248 --
5249 BEGIN
5250 --
5251 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
5252 --
5253 IF l_debug_on IS NULL
5254 THEN
5255 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
5256 END IF;
5257 --
5258 IF l_debug_on THEN
5259 wsh_debug_sv.push(l_module_name, 'Lock_Delivery_And_Details');
5260 wsh_debug_sv.log (l_module_name, 'Delivery Id', p_delivery_id);
5261 END IF;
5262
5263 x_return_status := wsh_util_core.g_ret_sts_success;
5264
5265 IF l_debug_on THEN
5266 wsh_debug_sv.logmsg(l_module_name, 'Locking the Delivery');
5267 END IF;
5268 SELECT delivery_id
5269 INTO l_dummy_id
5270 FROM wsh_new_deliveries
5271 WHERE delivery_id = p_delivery_id
5272 FOR UPDATE NOWAIT;
5273
5274 IF l_debug_on THEN
5275 wsh_debug_sv.logmsg(l_module_name, 'Locking the delivery details');
5276 END IF;
5277 OPEN lock_delivery_details;
5278 FETCH lock_delivery_details INTO det_ids;
5279 IF lock_delivery_details%NOTFOUND THEN
5280 IF l_debug_on THEN
5281 wsh_debug_sv.logmsg(l_module_name, 'No details found');
5282 END IF;
5283
5284 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5285 CLOSE lock_delivery_details;
5286 END IF;
5287 IF (lock_delivery_details%ISOPEN) THEN
5288 CLOSE lock_delivery_details;
5289 END IF;
5290 IF l_debug_on THEN
5291 wsh_debug_sv.pop(l_module_name);
5292 END IF;
5293
5294 EXCEPTION
5295 WHEN RECORD_LOCKED THEN
5296 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5297
5298 IF l_debug_on THEN
5299 wsh_debug_sv.logmsg(l_module_name, 'Could not obtain lock');
5300 END IF;
5301
5302 FND_MESSAGE.Set_Name('WSH', 'WSH_NO_LOCK');
5303 WSH_UTIL_CORE.add_message (x_return_status,l_module_name);
5304 IF l_debug_on THEN
5305 WSH_DEBUG_SV.logmsg(l_module_name,'RECORD_LOCKED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
5306 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:RECORD_LOCKED');
5307 END IF;
5308
5309 WHEN others THEN
5310 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
5311 IF l_debug_on THEN
5312 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
5313 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
5314 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
5315 END IF;
5316 END Lock_Delivery_And_Details;
5317
5318
5319 -- TPW - Distributed changes
5320 PROCEDURE Lock_Delivery_Details(
5321 p_delivery_interface_id IN NUMBER,
5322 x_return_status OUT NOCOPY VARCHAR2) IS
5323
5324 l_dummy_id NUMBER;
5325
5326 CURSOR lock_delivery_details IS
5327 SELECT wdd.delivery_detail_id
5328 FROM wsh_delivery_details wdd,
5329 wsh_delivery_assignments wda
5330 WHERE wdd.source_code = 'OE'
5331 AND wdd.delivery_detail_id = wda.delivery_detail_id
5332 AND wdd.released_status in ('R','B','X')
5333 AND wdd.delivery_detail_id in (
5334 select wdd1.delivery_detail_id
5335 from wsh_del_details_interface wddi,
5336 wsh_del_assgn_interface wdai,
5337 wsh_shipment_batches wsb,
5338 wsh_transactions_history wth,
5339 wsh_delivery_details wdd1
5340 where wdd1.source_code = 'OE'
5341 and wdd1.released_status in ('R','B','X')
5342 and wdd1.shipment_line_number = wddi.delivery_detail_id
5343 and wddi.delivery_detail_interface_id = wdai.delivery_detail_interface_id
5344 and wdai.delivery_interface_id = p_delivery_interface_id
5345 and wddi.line_direction = 'O'
5346 AND wdd1.shipment_batch_id = wsb.batch_id
5347 AND wsb.name = wth.entity_number
5348 AND wth.entity_type = 'BATCH'
5349 AND wth.document_number = wddi.source_header_number
5350 AND wth.document_type = 'SR'
5351 AND wth.document_direction = 'O'
5352 UNION
5353 SELECT wdd1.delivery_detail_id
5354 FROM wsh_del_details_interface wddi,
5355 wsh_del_assgn_interface wdai,
5356 wsh_delivery_details wdd1,
5357 oe_order_lines_all ol,
5358 po_requisition_lines_all pl,
5359 po_requisition_headers_all ph
5360 where wdd1.source_code = 'OE'
5361 and wdd1.released_status in ('R','B','X')
5362 and wddi.delivery_detail_interface_id = wdai.delivery_detail_interface_id
5363 and wdai.delivery_interface_id = p_delivery_interface_id
5364 and wddi.line_direction = 'IO'
5365 and wdd1.source_line_id = ol.line_id
5366 and ol.source_document_line_id = pl.requisition_line_id
5367 and ol.source_document_id = pl.requisition_header_id
5368 and pl.requisition_header_id = ph.requisition_header_id
5369 and pl.line_num = wddi.delivery_detail_id
5370 and ph.segment1 = wddi.source_header_number)
5371 FOR UPDATE OF wdd.attribute1, wda.parent_delivery_id NOWAIT;
5372
5373
5374 det_ids lock_delivery_details%ROWTYPE;
5375 RECORD_LOCKED EXCEPTION;
5376 PRAGMA EXCEPTION_INIT(RECORD_LOCKED, -54);
5377
5378 --
5379 l_debug_on BOOLEAN;
5380 --
5381 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOCK_DELIVERY_DETAILS';
5382 --
5383 BEGIN
5384 --
5385 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
5386 --
5387 IF l_debug_on IS NULL
5388 THEN
5389 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
5390 END IF;
5391 --
5392 IF l_debug_on THEN
5393 wsh_debug_sv.push(l_module_name, 'Lock_Delivery_Details');
5394 wsh_debug_sv.log (l_module_name, 'Delivery Interface Id', p_delivery_interface_id);
5395 END IF;
5396
5397 x_return_status := wsh_util_core.g_ret_sts_success;
5398
5399 IF l_debug_on THEN
5400 wsh_debug_sv.logmsg(l_module_name, 'Locking the delivery details');
5401 END IF;
5402 OPEN lock_delivery_details;
5403 FETCH lock_delivery_details INTO det_ids;
5404 IF lock_delivery_details%NOTFOUND THEN
5405 IF l_debug_on THEN
5406 wsh_debug_sv.logmsg(l_module_name, 'No details found');
5407 END IF;
5408
5409 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5410 CLOSE lock_delivery_details;
5411 END IF;
5412 IF (lock_delivery_details%ISOPEN) THEN
5413 CLOSE lock_delivery_details;
5414 END IF;
5415 IF l_debug_on THEN
5416 wsh_debug_sv.pop(l_module_name);
5417 END IF;
5418
5419 EXCEPTION
5420 WHEN RECORD_LOCKED THEN
5421 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5422
5423 IF l_debug_on THEN
5424 wsh_debug_sv.logmsg(l_module_name, 'Could not obtain lock');
5425 END IF;
5426
5427 FND_MESSAGE.Set_Name('WSH', 'WSH_NO_LOCK');
5428 WSH_UTIL_CORE.add_message (x_return_status,l_module_name);
5429 IF l_debug_on THEN
5430 WSH_DEBUG_SV.logmsg(l_module_name,'RECORD_LOCKED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
5431 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:RECORD_LOCKED');
5432 END IF;
5433
5434 WHEN others THEN
5435 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
5436 IF l_debug_on THEN
5437 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
5438 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
5439 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
5440 END IF;
5441 END Lock_Delivery_Details;
5442
5443 PROCEDURE log_errors(
5444 p_loc_interface_errors_rec IN WSH_INTERFACE_VALIDATIONS_PKG.interface_errors_rec_type,
5445 p_msg_data IN VARCHAR2 DEFAULT NULL,
5446 p_api_name IN VARCHAR2,
5447 x_return_status OUT NOCOPY VARCHAR2
5448 ) IS
5449 --
5450 l_debug_on BOOLEAN;
5451 --
5452 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOG_ERRORS';
5453 --
5454 BEGIN
5455 --
5456 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
5457 --
5458 IF l_debug_on IS NULL
5459 THEN
5460 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
5461 END IF;
5462 --
5463 IF l_debug_on THEN
5464 wsh_debug_sv.push(l_module_name, 'log_errors');
5465 wsh_debug_sv.log (l_module_name, 'Interface table name', p_loc_interface_errors_rec.p_interface_table_name);
5466 wsh_debug_sv.log (l_module_name, 'Interface Id', p_loc_interface_errors_rec.p_interface_id);
5467 wsh_debug_sv.log (l_module_name, 'Message Name', p_loc_interface_errors_rec.p_message_name);
5468 END IF;
5469
5470 WSH_INTERFACE_VALIDATIONS_PKG.Log_Interface_Errors(
5471 p_interface_errors_rec => p_loc_interface_errors_rec,
5472 p_msg_data => p_msg_data,
5473 p_api_name => p_api_name,
5474 x_return_status => x_return_status);
5475
5476 IF l_debug_on THEN
5477 wsh_debug_sv.log (l_module_name, 'Return status from Log_Interface_Errors', x_return_status);
5478 wsh_debug_sv.pop(l_module_name);
5479 END IF;
5480 EXCEPTION
5481 WHEN others THEN
5482 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
5483 IF l_debug_on THEN
5484 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
5485 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
5486 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
5487 END IF;
5488
5489 END Log_Errors;
5490
5491 PROCEDURE split_delivery_detail(
5492 p_delivery_detail_id IN NUMBER,
5493 p_qty_to_split IN NUMBER,
5494 x_new_detail_id OUT NOCOPY NUMBER,
5495 x_return_status OUT NOCOPY VARCHAR2
5496 ) IS
5497
5498 l_base_req_qty NUMBER ;
5499 l_qty_to_split NUMBER;
5500 l_return_status VARCHAR2(30);
5501 l_number_of_errors NUMBER := 0;
5502 l_number_of_warnings NUMBER := 0;
5503 --
5504 CURSOR base_detail_qty(l_del_detail_id NUMBER) IS
5505 SELECT requested_quantity
5506 FROM wsh_delivery_details
5507 WHERE delivery_detail_id =l_del_detail_id;
5508 --
5509 l_debug_on BOOLEAN;
5510 --
5511 l_module_name CONSTANT VARCHAR2(100):= 'wsh.plsql.' || g_pkg_name || '.' || 'SPLIT_DELIVERY_DETAIL';
5512 BEGIN
5513 --
5514 l_debug_on := wsh_debug_interface.g_debug;
5515
5516 --
5517 IF l_debug_on IS NULL THEN
5518 l_debug_on := wsh_debug_sv.is_debug_enabled;
5519 END IF;
5520
5521 --
5522 IF l_debug_on THEN
5523 wsh_debug_sv.push(l_module_name, 'split_delivery_detail');
5524 wsh_debug_sv.LOG(l_module_name, 'p_delivery_detail_id',p_delivery_detail_id);
5525 wsh_debug_sv.LOG(l_module_name, 'p_qty_to_split', p_qty_to_split);
5526 END IF;
5527
5528 OPEN base_detail_qty(p_delivery_detail_id);
5529 FETCH base_detail_qty INTO l_base_req_qty;
5530 CLOSE base_detail_qty;
5531
5532 IF l_debug_on THEN
5533 wsh_debug_sv.LOG(l_module_name, 'Base req qty', l_base_req_qty);
5534 END IF;
5535
5536 IF (p_qty_to_split > l_base_req_qty) THEN
5537 -- Quantity has exceeded
5538 -- so exit
5539 NULL;
5540 ELSIF(p_qty_to_split = l_base_req_qty) THEN
5541 NULL;
5542 ELSE
5543 -- call split_delivery_details
5544 IF l_debug_on THEN
5545 wsh_debug_sv.LOG(l_module_name, 'qty to split', p_qty_to_split);
5546 END IF;
5547
5548 IF p_qty_to_split IS NULL THEN
5549 fnd_message.set_name('WSH', 'WSH_REQUIRED_FIELD_NULL');
5550 fnd_message.set_token('FIELD_NAME', 'REQUESTED_QTY');
5551 wsh_util_core.add_message(wsh_util_core.g_ret_sts_error);
5552 RAISE FND_API.G_EXC_ERROR;
5553 END IF;
5554
5555 l_qty_to_split := p_qty_to_split;
5556 wsh_delivery_details_actions.split_delivery_details(
5557 p_from_detail_id => p_delivery_detail_id,
5558 p_req_quantity => l_qty_to_split,
5559 x_new_detail_id => x_new_detail_id,
5560 x_return_status => l_return_status);
5561
5562 IF l_debug_on THEN
5563 wsh_debug_sv.LOG(l_module_name, 'Split_Delivery_Details x_new_detail_id,l_return_status',
5564 x_new_detail_id || ',' || l_return_status);
5565 END IF;
5566
5567 wsh_util_core.api_post_call(
5568 p_return_status =>l_return_status,
5569 x_num_warnings =>l_number_of_warnings,
5570 x_num_errors =>l_number_of_errors);
5571 END IF;
5572
5573 IF l_number_of_warnings > 0 THEN
5574 IF l_debug_on THEN
5575 wsh_debug_sv.logmsg (l_module_name,'Number of warnings', l_number_of_warnings);
5576 END IF;
5577 RAISE WSH_UTIL_CORE.G_EXC_WARNING;
5578 END IF;
5579
5580 x_return_status := wsh_util_core.g_ret_sts_success;
5581
5582 IF l_debug_on THEN
5583 wsh_debug_sv.pop(l_module_name);
5584 END IF;
5585 EXCEPTION
5586 WHEN fnd_api.g_exc_error THEN
5587 x_return_status := fnd_api.g_ret_sts_error;
5588 --
5589 IF l_debug_on THEN
5590 wsh_debug_sv.logmsg(l_module_name,
5591 'FND_API.G_EXC_ERROR exception has occured.',
5592 wsh_debug_sv.c_excep_level);
5593 wsh_debug_sv.pop(l_module_name, 'EXCEPTION:FND_API.G_EXC_ERROR');
5594 END IF;
5595 --
5596 WHEN fnd_api.g_exc_unexpected_error THEN
5597 x_return_status := fnd_api.g_ret_sts_unexp_error;
5598 --
5599 IF l_debug_on THEN
5600 wsh_debug_sv.logmsg(l_module_name,
5601 'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',
5602 wsh_debug_sv.c_excep_level);
5603 wsh_debug_sv.pop(l_module_name,
5604 'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
5605 END IF;
5606 --
5607 WHEN wsh_util_core.g_exc_warning THEN
5608 x_return_status := wsh_util_core.g_ret_sts_warning;
5609 --
5610 IF l_debug_on THEN
5611 wsh_debug_sv.logmsg(l_module_name,
5612 'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',
5613 wsh_debug_sv.c_excep_level);
5614 wsh_debug_sv.pop(l_module_name,
5615 'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
5616 END IF;
5617 WHEN OTHERS THEN
5618 x_return_status := wsh_util_core.g_ret_sts_unexp_error;
5619 wsh_util_core.default_handler('WSH_INTERFACE_COMMON_ACTIONS.split_delivery_detail');
5620 --
5621 IF l_debug_on THEN
5622 wsh_debug_sv.logmsg(l_module_name,
5623 'Unexpected error has occured. Oracle error message is '
5624 || SQLERRM,
5625 wsh_debug_sv.c_unexpec_err_level);
5626 wsh_debug_sv.pop(l_module_name, 'EXCEPTION:OTHERS');
5627 END IF;
5628 END split_delivery_detail;
5629
5630
5631 PROCEDURE add_to_serial_table(
5632 p_serial_range_tab IN WSH_GLBL_VAR_STRCT_GRP.ddSerialRangeTabType)
5633 IS
5634 l_index NUMBER;
5635 l_g_count NUMBER;
5636 BEGIN
5637 l_index := p_serial_range_tab.first;
5638 while l_index is not null loop
5639 l_g_count := g_serial_range_tab.count;
5640 g_serial_range_tab(l_g_count + 1).delivery_detail_id := p_serial_range_tab(l_index).delivery_detail_id;
5641 g_serial_range_tab(l_g_count + 1).from_serial_number := p_serial_range_tab(l_index).from_serial_number;
5642 g_serial_range_tab(l_g_count + 1).to_serial_number := p_serial_range_tab(l_index).to_serial_number;
5643 g_serial_range_tab(l_g_count + 1).quantity := p_serial_range_tab(l_index).quantity;
5644 l_index := p_serial_range_tab.next(l_index);
5645 end loop;
5646 EXCEPTION
5647 WHEN OTHERS THEN
5648 raise fnd_api.g_exc_error;
5649 END add_to_serial_table;
5650
5651 END WSH_INTERFACE_COMMON_ACTIONS;