[Home] [Help]
PACKAGE BODY: APPS.WSH_DELIVERY_DETAILS_PUB
Source
1 PACKAGE BODY WSH_DELIVERY_DETAILS_PUB as
2 /* $Header: WSHDDPBB.pls 120.5.12020000.2 2013/05/17 05:42:54 skanduku ship $ */
3
4 -- standard global constants
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'WSH_DELIVERY_DETAILS_PUB';
6 p_message_type CONSTANT VARCHAR2(1) := 'E';
7
8
9 PROCEDURE Copy_Attributes(
10 x_detail_info_tab OUT NOCOPY WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Attr_Tbl_Type
11 , x_changed_attributes IN WSH_DELIVERY_DETAILS_PUB.ChangedAttributeTabType);
12
13
14 -- -----------------------------------------------------------------------
15 -- Procedure: detail_to_delivery
16 --
17 -- Parameters: 1) table of delivery_detail_ids
18 -- 2) action: assign/unassign
19 -- 3) delivery_id: need to specify delivery id or delivery name
20 -- if the action is 'ASSIGN'
21 -- 4) delivery_name: need to specify delivery id or delivery
22 -- name if the action is 'ASSIGN'
23 -- 5) other standard parameters
24 --
25 --
26 -- Description: This procedure assign/unassign delivery_details to
27 -- a delivery.
28 -- Parameters: p_TabOfDelDets: required
29 -- p_action: required
30 -- p_delivery_id: required if action='ASSIGN'
31 -- -----------------------------------------------------------------------
32
33 PROCEDURE detail_to_delivery(
34 -- Standard parameters
35 p_api_version IN NUMBER,
36 p_init_msg_list IN VARCHAR2,
37 p_commit IN VARCHAR2,
38 p_validation_level IN NUMBER,
39 x_return_status OUT NOCOPY VARCHAR2,
40 x_msg_count OUT NOCOPY NUMBER,
41 x_msg_data OUT NOCOPY VARCHAR2,
42
43 -- procedure specific parameters
44 p_TabOfDelDets IN ID_TAB_TYPE,
45 p_action IN VARCHAR2,
46 p_delivery_id IN NUMBER,
47 p_delivery_name IN VARCHAR2
48 ) IS
49
50 -- Standard call to check for call compatibility
51 l_api_version CONSTANT NUMBER := 1.0;
52 l_api_name CONSTANT VARCHAR2(30):= 'delivery_detail_to_delivery';
53
54 l_return_status VARCHAR2(30) := NULL;
55 l_delivery_id number := NULL;
56 l_cont_ins_id number := NULL;
57 l_TabOfDelDets WSH_UTIL_CORE.ID_TAB_TYPE;
58 l_msg_summary varchar2(2000) := NULL;
59 l_msg_details varchar2(4000) := NULL;
60
61 -- Harmonization Project
62 l_action_prms WSH_GLBL_VAR_STRCT_GRP.dd_action_parameters_rec_type;
63 l_action_out_rec WSH_GLBL_VAR_STRCT_GRP.dd_action_out_rec_type;
64 l_dummy_qty NUMBER;
65 l_dummy_qty2 NUMBER;
66 l_dummy_ids wsh_util_core.id_tab_type;
67
68 l_msg_count NUMBER;
69 l_msg_data VARCHAR2(32767);
70 l_number_of_errors NUMBER := 0;
71 l_number_of_warnings NUMBER := 0;
72
73 -- Harmonization Project
74
75 WSH_NO_DEL_DET_TBL exception;
76 --
77 l_debug_on BOOLEAN;
78 --
79 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DETAIL_TO_DELIVERY';
80 --
81 begin
82
83 -- Standard begin of API savepoint
84 --
85 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
86 --
87 IF l_debug_on IS NULL
88 THEN
89 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
90 END IF;
91 --
92 SAVEPOINT DETAIL_TO_DELIVERY_PUB;
93 IF l_debug_on THEN
94 WSH_DEBUG_SV.push(l_module_name);
95 --
96 WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION',P_API_VERSION);
97 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',P_INIT_MSG_LIST);
98 WSH_DEBUG_SV.log(l_module_name,'P_COMMIT',P_COMMIT);
99 WSH_DEBUG_SV.log(l_module_name,'P_VALIDATION_LEVEL',P_VALIDATION_LEVEL);
100 WSH_DEBUG_SV.log(l_module_name,'P_ACTION',P_ACTION);
101 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_ID',P_DELIVERY_ID);
102 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_NAME',P_DELIVERY_NAME);
103 END IF;
104 --
105
106 IF NOT FND_API.compatible_api_call( l_api_version,
107 p_api_version,
108 l_api_name,
109 G_PKG_NAME) THEN
110 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
111 END IF;
112
113 -- Check p_init_msg_list
114 IF FND_API.to_boolean(p_init_msg_list) THEN
115 FND_MSG_PUB.initialize;
116 END IF;
117
118 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
119
120 if (p_TabOfDelDets.count = 0) then
121 raise WSH_NO_DEL_DET_TBL;
122 end if;
123
124
125 FOR I IN p_TabOfDelDets.first .. p_TabOfDelDets.last
126 LOOP
127
128 l_TabOfDelDets(i) := p_TabOfDelDets(i);
129 -- dbms_output.put_line(l_TabOfDelDets(i));
130 END LOOP;
131 l_action_prms.caller := 'WSH_PUB';
132 l_action_prms.action_code := p_action;
133 l_action_prms.delivery_id := p_delivery_id;
134 l_action_prms.delivery_name := p_delivery_name;
135
136 /* Patchset I: Harmonization Project. Call group API
137 All validations done by Group API */
138
139 IF l_debug_on THEN
140 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_INTERFACE_GRP.DELIVERY_DETAIL_ACTION',WSH_DEBUG_SV.C_PROC_LEVEL);
141 END IF;
142 --
143
144 wsh_interface_grp.delivery_detail_action(
145 p_api_version_number => p_api_version,
146 p_init_msg_list => FND_API.G_FALSE,
147 p_commit => FND_API.G_FALSE,
148 x_return_status => l_return_status,
149 x_msg_count => l_msg_count,
150 x_msg_data => l_msg_data,
151 p_detail_id_tab => l_TabOfDelDets,
152 p_action_prms => l_action_prms ,
153 x_action_out_rec => l_action_out_rec);
154
155 IF l_debug_on THEN
156 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
157 END IF;
158 --
159 wsh_util_core.api_post_call(
160 p_return_status => l_return_status,
161 x_num_warnings => l_number_of_warnings,
162 x_num_errors => l_number_of_errors,
163 p_msg_data => l_msg_data
164 );
165
166 IF l_number_of_warnings > 0 THEN
167 x_return_status := wsh_util_core.g_ret_sts_warning;
168 END IF;
169
170
171 IF FND_API.TO_BOOLEAN(p_commit) THEN
172 -- dbms_output.put_line('commit');
173 COMMIT WORK;
174 END IF;
175
176 FND_MSG_PUB.Count_And_Get
177 (
178 p_count => x_msg_count,
179 p_data => x_msg_data,
180 p_encoded => FND_API.G_FALSE
181 );
182
183 IF l_debug_on THEN
184 WSH_DEBUG_SV.pop(l_module_name);
185 END IF;
186 --
187 exception
188 WHEN FND_API.G_EXC_ERROR THEN
189 ROLLBACK TO DETAIL_TO_DELIVERY_PUB;
190 x_return_status := FND_API.G_RET_STS_ERROR ;
191 wsh_util_core.add_message(x_return_status);
192 FND_MSG_PUB.Count_And_Get
193 (
194 p_count => x_msg_count,
195 p_data => x_msg_data,
196 p_encoded => FND_API.G_FALSE
197 );
198
199 IF l_debug_on THEN
200 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
201 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
202 END IF;
203 --
204 WHEN WSH_NO_DEL_DET_TBL then
205 ROLLBACK TO DETAIL_TO_DELIVERY_PUB;
206 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
207 fnd_message.set_name('WSH', 'WSH_PUB_NO_DEL_DET_TBL');
208 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
209 FND_MSG_PUB.Count_And_Get
210 (
211 p_count => x_msg_count,
212 p_data => x_msg_data,
213 p_encoded => FND_API.G_FALSE
214 );
215
216 IF l_debug_on THEN
217 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_NO_DEL_DET_TBL exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
218 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_NO_DEL_DET_TBL');
219 END IF;
220 --
221 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
222 ROLLBACK TO DETAIL_TO_DELIVERY_PUB;
223 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
224 wsh_util_core.add_message(x_return_status, l_module_name);
225 FND_MSG_PUB.Count_And_Get
226 (
227 p_count => x_msg_count,
228 p_data => x_msg_data,
229 p_encoded => FND_API.G_FALSE
230 );
231 --
232
233 IF l_debug_on THEN
234 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
235 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
236 END IF;
237 WHEN others then
238 ROLLBACK TO DETAIL_TO_DELIVERY_PUB;
239 wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_PUB.DETAIL_TO_DELIVERY');
240 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
241 FND_MSG_PUB.Count_And_Get
242 (
243 p_count => x_msg_count,
244 p_data => x_msg_data,
245 p_encoded => FND_API.G_FALSE
246 );
247
248 IF l_debug_on THEN
249 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
250 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
251 END IF;
252 --
253 END detail_to_delivery;
254
255 -- ----------------------------------------------------------------------
256 -- Procedure: split_line
257 -- Parameters: p_from_detail_id: The delivery detail ID to be split
258 -- x_new_detail_id: The new delivery detail ID
259 -- p_source_quantity:
260 -- split_quantity:
261 --
262 -- Description: This procedure split a delivery_deatil line
263 -- 03/19/01 OPM changes. Added split_quantity2
264 -- ----------------------------------------------------------------------
265
266 PROCEDURE split_line
267 (
268 -- Standard parameters
269 p_api_version IN NUMBER,
270 p_init_msg_list IN VARCHAR2,
271 p_commit IN VARCHAR2,
272 p_validation_level IN NUMBER,
273 x_return_status OUT NOCOPY VARCHAR2,
274 x_msg_count OUT NOCOPY NUMBER,
275 x_msg_data OUT NOCOPY VARCHAR2,
276
277 -- Procedure specific parameters
278 p_from_detail_id IN NUMBER,
279 x_new_detail_id OUT NOCOPY NUMBER,
280 x_split_quantity IN OUT NOCOPY NUMBER,
281 x_split_quantity2 IN OUT NOCOPY NUMBER
282 ) is
283
284 l_msg_summary varchar2(2000);
285 l_msg_details varchar2(4000);
286
287 -- Harmonization Project
288 l_action_prms WSH_GLBL_VAR_STRCT_GRP.dd_action_parameters_rec_type;
289 l_action_out_rec WSH_GLBL_VAR_STRCT_GRP.dd_action_out_rec_type;
290 l_dummy_qty NUMBER;
291 l_dummy_qty2 NUMBER;
292 l_dummy_ids wsh_util_core.id_tab_type;
293 l_detail_ids wsh_util_core.id_tab_type;
294 l_msg_count NUMBER;
295 l_msg_data VARCHAR2(32767);
296 l_number_of_errors NUMBER := 0;
297 l_number_of_warnings NUMBER := 0;
298 l_return_status VARCHAR2(30);
299 l_index NUMBER;
300 --MUOM Bug 16806520 : Start of Changes
301 l_organization_id NUMBER;
302 l_inventory_item_id NUMBER;
303 l_source_line_id NUMBER;
304 l_lot_number VARCHAR2(100);
305 l_requested_quantity_uom VARCHAR2(100);
306 l_requested_quantity_uom2 VARCHAR2(100);
307 l_requested_quantity NUMBER;
308 l_requested_quantity2 NUMBER;
309 l_released_status VARCHAR2(1);
310 l_return NUMBER;
311 l_fulfillment_base VARCHAR2(1);
312
313 cursor wdd_details IS
314 select organization_id,inventory_item_id,source_line_id,
315 lot_number,requested_quantity_uom,requested_quantity_uom2,
316 requested_quantity,requested_quantity2,released_status
317 from wsh_delivery_details
318 where delivery_detail_id = P_FROM_DETAIL_ID;
319
320 --MUOM Bug 16806520 :End of changes
321
322 -- Standard call to check for call compatibility.
323 l_api_version CONSTANT NUMBER := 1.0;
324 l_api_name CONSTANT VARCHAR2(30):= 'delivery_detail_to_delivery';
325
326 l_debug_on BOOLEAN;
327 --
328 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'SPLIT_LINE';
329 --
330 BEGIN
331
332 -- Standard begin of API savepoint
333 --
334 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
335 --
336 IF l_debug_on IS NULL
337 THEN
338 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
339 END IF;
340 --
341 SAVEPOINT SPLIT_LINE_PUB;
342
343 IF l_debug_on THEN
344 WSH_DEBUG_SV.push(l_module_name);
345 --
346 WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION',P_API_VERSION);
347 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',P_INIT_MSG_LIST);
348 WSH_DEBUG_SV.log(l_module_name,'P_COMMIT',P_COMMIT);
349 WSH_DEBUG_SV.log(l_module_name,'P_VALIDATION_LEVEL',P_VALIDATION_LEVEL);
350 WSH_DEBUG_SV.log(l_module_name,'P_FROM_DETAIL_ID',P_FROM_DETAIL_ID);
351 WSH_DEBUG_SV.log(l_module_name,'X_SPLIT_QUANTITY',X_SPLIT_QUANTITY);
352 WSH_DEBUG_SV.log(l_module_name,'X_SPLIT_QUANTITY2',X_SPLIT_QUANTITY2);
353 END IF;
354 --
355
356 IF NOT FND_API.compatible_api_call(l_api_version,
357 p_api_version,
358 l_api_name,
359 G_PKG_NAME) THEN
360 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
361 END IF;
362 -- Check p_init_msg_list
363 IF FND_API.to_boolean(p_init_msg_list) THEN
364 FND_MSG_PUB.initialize;
365 END IF;
366 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
367
368
369 l_detail_ids(1) := p_from_detail_id;
370 l_action_prms.caller := 'WSH_PUB';
371 l_action_prms.action_code := 'SPLIT-LINE';
372 l_action_prms.split_quantity := x_split_quantity;
373 l_action_prms.split_quantity2 := x_split_quantity2;
374
375 /* Patchset I: Harmonization Project. Call group API
376 All validations done by Group API */
377 --MUOM Bug : 16806520 : Start of Changes
378 OPEN wdd_details ;
379 FETCH wdd_details INTO l_organization_id,l_inventory_item_id,l_source_line_id,l_lot_number,l_requested_quantity_uom,l_requested_quantity_uom2,l_requested_quantity,l_requested_quantity2,l_released_status;
380 CLOSE wdd_details;
381
382 l_fulfillment_base := WSH_UTIL_CORE.Get_Line_Fulfillment_Base('OE', l_source_line_id);
383
384 IF l_fulfillment_base = 'S' AND l_released_status = 'Y' THEN
385 l_return := WSH_WV_UTILS.within_deviation
386 (
387 p_organization_id => l_organization_id,
388 p_inventory_item_id => l_inventory_item_id,
389 p_lot_number => l_lot_number,
390 p_quantity => x_split_quantity,
391 p_uom1 => l_requested_quantity_uom,
392 p_quantity2 => x_split_quantity2,
393 p_uom2 => l_requested_quantity_uom2);
394
395 IF l_return = 1 THEN
396 l_return := WSH_WV_UTILS.within_deviation
397 (
398 p_organization_id => l_organization_id,
399 p_inventory_item_id => l_inventory_item_id,
400 p_lot_number => l_lot_number,
401 p_quantity => l_requested_quantity-x_split_quantity,
402 p_uom1 => l_requested_quantity_uom,
403 p_quantity2 => l_requested_quantity2-x_split_quantity2,
404 p_uom2 => l_requested_quantity_uom2);
405
406 END IF;
407
408 IF l_return <> 1 THEN
409 RAISE FND_API.G_EXC_ERROR;
410 END IF;
411 END IF;
412 --MUOM : BUG : 16806520 : End of Changes
413
414
415 IF l_debug_on THEN
416 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_INTERFACE_GRP.DELIVERY_DETAIL_ACTION',WSH_DEBUG_SV.C_PROC_LEVEL);
417 END IF;
418
419 wsh_interface_grp.delivery_detail_action(
420 p_api_version_number => p_api_version,
421 p_init_msg_list => FND_API.G_FALSE,
422 p_commit => FND_API.G_FALSE,
423 x_return_status => l_return_status,
424 x_msg_count => l_msg_count,
425 x_msg_data => l_msg_data,
426 p_detail_id_tab => l_detail_ids,
427 p_action_prms => l_action_prms ,
428 x_action_out_rec => l_action_out_rec);
429
430 IF l_debug_on THEN
431 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
432 END IF;
433 --
434 wsh_util_core.api_post_call(
435 p_return_status => l_return_status,
436 x_num_warnings => l_number_of_warnings,
437 x_num_errors => l_number_of_errors,
438 p_msg_data => l_msg_data
439 );
440
441 x_split_quantity := l_action_out_rec.split_quantity;
442 x_split_quantity2 := l_action_out_rec.split_quantity2;
443 l_index := l_action_out_rec.result_id_tab.first;
444 x_new_detail_id := l_action_out_rec.result_id_tab(l_index);
445
446 IF l_number_of_warnings > 0 THEN
447 x_return_status := wsh_util_core.g_ret_sts_warning;
448 END IF;
449
450 IF FND_API.TO_BOOLEAN(p_commit) THEN
451 -- dbms_output.put_line('commit');
452 COMMIT WORK;
453 END IF;
454
455 FND_MSG_PUB.Count_And_Get
456 (
457 p_count => x_msg_count,
458 p_data => x_msg_data,
459 p_encoded => FND_API.G_FALSE
460 );
461
462 IF l_debug_on THEN
463 WSH_DEBUG_SV.pop(l_module_name);
464 END IF;
465 --
466 exception
467 WHEN FND_API.G_EXC_ERROR THEN
468 ROLLBACK TO SPLIT_LINE_PUB;
469 x_return_status := FND_API.G_RET_STS_ERROR ;
470 wsh_util_core.add_message(x_return_status);
471 FND_MSG_PUB.Count_And_Get
472 (
473 p_count => x_msg_count,
474 p_data => x_msg_data,
475 p_encoded => FND_API.G_FALSE
476 );
477
478 IF l_debug_on THEN
479 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
480 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
481 END IF;
482 --
483 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
484 ROLLBACK TO SPLIT_LINE_PUB;
485 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
486 wsh_util_core.add_message(x_return_status, l_module_name);
487 FND_MSG_PUB.Count_And_Get
488 (
489 p_count => x_msg_count,
490 p_data => x_msg_data,
491 p_encoded => FND_API.G_FALSE
492 );
493 --
494
495 IF l_debug_on THEN
496 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
497 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
498 END IF;
499 WHEN OTHERS THEN
500 ROLLBACK TO SPLIT_LINE_PUB;
501 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
502 wsh_util_core.add_message(x_return_status, l_module_name);
503 WSH_UTIL_CORE.default_handler('WSH_DELIVERY_DETAILS_PUB.Split_Line');
504 FND_MSG_PUB.Count_And_Get
505 (
506 p_count => x_msg_count,
507 p_data => x_msg_data,
508 p_encoded => FND_API.G_FALSE
509 );
510 --
511 IF l_debug_on THEN
512 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
513 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
514 END IF;
515 --
516 END split_line;
517 --===================
518 -- PROCEDURES
519 --===================
520
521 -- Procedure Init_Changed_Attribute_Rec
522 -- Parameter p_init_rec record that needs to be initialized.
523 -- This procedure takes in a record of WSH_DELIVERY_DETAILS_PUB.ChangedAttributeRecType and
524 -- initializes its attributes to the default FND_API_G values.
525
526 Procedure Init_Changed_Attribute_Rec(p_init_rec IN OUT NOCOPY WSH_DELIVERY_DETAILS_PUB.ChangedAttributeRecType,
527 x_return_status OUT NOCOPY VARCHAR2) IS
528 --
529 l_debug_on BOOLEAN;
530 --
531 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'INIT_CHANGED_ATTRIBUTE_REC';
532 --
533 BEGIN
534 --
535 -- Debug Statements
536 --
537 --
538 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
539 --
540 IF l_debug_on IS NULL
541 THEN
542 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
543 END IF;
544 --
545 IF l_debug_on THEN
546 WSH_DEBUG_SV.push(l_module_name);
547 END IF;
548 --
549 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
550 p_init_rec.source_header_id := FND_API.G_MISS_NUM;
551 p_init_rec.source_line_id := FND_API.G_MISS_NUM;
552 p_init_rec.sold_to_org_id := FND_API.G_MISS_NUM;
553 p_init_rec.customer_number := FND_API.G_MISS_NUM;
554 p_init_rec.sold_to_contact_id := FND_API.G_MISS_NUM;
555 p_init_rec.ship_from_org_id := FND_API.G_MISS_NUM;
556 p_init_rec.ship_from_org_code := FND_API.G_MISS_CHAR;
557 p_init_rec.ship_to_org_id := FND_API.G_MISS_NUM;
558 p_init_rec.ship_to_org_code := FND_API.G_MISS_CHAR;
559 p_init_rec.ship_to_contact_id := FND_API.G_MISS_NUM;
560 p_init_rec.deliver_to_org_id := FND_API.G_MISS_NUM;
561 p_init_rec.deliver_to_org_code := FND_API.G_MISS_CHAR;
562 p_init_rec.deliver_to_contact_id := FND_API.G_MISS_NUM;
563 p_init_rec.intmed_ship_to_org_id := FND_API.G_MISS_NUM;
564 p_init_rec.intmed_ship_to_org_code := FND_API.G_MISS_CHAR;
565 p_init_rec.intmed_ship_to_contact_id := FND_API.G_MISS_NUM;
566 p_init_rec.ship_tolerance_above := FND_API.G_MISS_NUM;
567 p_init_rec.ship_tolerance_below := FND_API.G_MISS_NUM;
568 p_init_rec.ordered_quantity := FND_API.G_MISS_NUM;
569 p_init_rec.ordered_quantity2 := FND_API.G_MISS_NUM;
570 p_init_rec.order_quantity_uom := FND_API.G_MISS_CHAR;
571 p_init_rec.ordered_quantity_uom2 := FND_API.G_MISS_CHAR;
572 p_init_rec.preferred_grade := FND_API.G_MISS_CHAR;
573 p_init_rec.ordered_qty_unit_of_measure := FND_API.G_MISS_CHAR;
574 p_init_rec.ordered_qty_unit_of_measure2 := FND_API.G_MISS_CHAR;
575 p_init_rec.subinventory := FND_API.G_MISS_CHAR;
576 p_init_rec.revision := FND_API.G_MISS_CHAR;
577 p_init_rec.lot_number := FND_API.G_MISS_CHAR;
578 -- HW OPMCONV - No need for sublot_number
579 -- p_init_rec.sublot_number := FND_API.G_MISS_CHAR;
580 p_init_rec.customer_requested_lot_flag := FND_API.G_MISS_CHAR;
581 p_init_rec.serial_number := FND_API.G_MISS_CHAR;
582 p_init_rec.locator_id := FND_API.G_MISS_NUM;
583 p_init_rec.date_requested := FND_API.G_MISS_DATE;
584 p_init_rec.date_scheduled := FND_API.G_MISS_DATE;
585 p_init_rec.master_container_item_id := FND_API.G_MISS_NUM;
586 p_init_rec.detail_container_item_id := FND_API.G_MISS_NUM;
587 p_init_rec.shipping_method_code := FND_API.G_MISS_CHAR;
588 p_init_rec.carrier_id := FND_API.G_MISS_NUM;
589 p_init_rec.freight_terms_code := FND_API.G_MISS_CHAR;
590 p_init_rec.freight_terms_name := FND_API.G_MISS_CHAR;
591 p_init_rec.freight_carrier_code := FND_API.G_MISS_CHAR;
592 p_init_rec.shipment_priority_code := FND_API.G_MISS_CHAR;
593 p_init_rec.fob_code := FND_API.G_MISS_CHAR;
594 p_init_rec.fob_name := FND_API.G_MISS_CHAR;
595 p_init_rec.dep_plan_required_flag := FND_API.G_MISS_CHAR;
596 p_init_rec.customer_prod_seq := FND_API.G_MISS_CHAR;
597 p_init_rec.customer_dock_code := FND_API.G_MISS_CHAR;
598 p_init_rec.gross_weight := FND_API.G_MISS_NUM;
599 p_init_rec.net_weight := FND_API.G_MISS_NUM;
600 p_init_rec.weight_uom_code := FND_API.G_MISS_CHAR;
601 p_init_rec.weight_uom_desc := FND_API.G_MISS_CHAR;
602 p_init_rec.volume := FND_API.G_MISS_NUM;
603 p_init_rec.volume_uom_code := FND_API.G_MISS_CHAR;
604 p_init_rec.volume_uom_desc := FND_API.G_MISS_CHAR;
605 p_init_rec.top_model_line_id := FND_API.G_MISS_NUM;
606 p_init_rec.ship_set_id := FND_API.G_MISS_NUM;
607 p_init_rec.ato_line_id := FND_API.G_MISS_NUM;
608 p_init_rec.arrival_set_id := FND_API.G_MISS_NUM;
609 p_init_rec.ship_model_complete_flag := FND_API.G_MISS_CHAR;
610 p_init_rec.cust_po_number := FND_API.G_MISS_CHAR;
611 p_init_rec.released_status := FND_API.G_MISS_CHAR;
612 p_init_rec.packing_instructions := FND_API.G_MISS_CHAR;
613 p_init_rec.shipping_instructions := FND_API.G_MISS_CHAR;
614 p_init_rec.container_name := FND_API.G_MISS_CHAR;
615 p_init_rec.container_flag := FND_API.G_MISS_CHAR;
616 p_init_rec.delivery_detail_id := FND_API.G_MISS_NUM;
617 p_init_rec.shipped_quantity := FND_API.G_MISS_NUM;
618 p_init_rec.cycle_count_quantity := FND_API.G_MISS_NUM;
619 -- HW OPMCONV - Added Qty2
620 p_init_rec.shipped_quantity2 := FND_API.G_MISS_NUM;
621 p_init_rec.cycle_count_quantity2 := FND_API.G_MISS_NUM;
622 p_init_rec.tracking_number := FND_API.G_MISS_CHAR;
623 p_init_rec.attribute1 := FND_API.G_MISS_CHAR;
624 p_init_rec.attribute2 := FND_API.G_MISS_CHAR;
625 p_init_rec.attribute3 := FND_API.G_MISS_CHAR;
626 p_init_rec.attribute4 := FND_API.G_MISS_CHAR;
627 p_init_rec.attribute5 := FND_API.G_MISS_CHAR;
628 p_init_rec.attribute6 := FND_API.G_MISS_CHAR;
629 p_init_rec.attribute7 := FND_API.G_MISS_CHAR;
630 p_init_rec.attribute8 := FND_API.G_MISS_CHAR;
631 p_init_rec.attribute9 := FND_API.G_MISS_CHAR;
632 p_init_rec.attribute10 := FND_API.G_MISS_CHAR;
633 p_init_rec.attribute11 := FND_API.G_MISS_CHAR;
634 p_init_rec.attribute12 := FND_API.G_MISS_CHAR;
635 p_init_rec.attribute13 := FND_API.G_MISS_CHAR;
636 p_init_rec.attribute14 := FND_API.G_MISS_CHAR;
637 p_init_rec.attribute15 := FND_API.G_MISS_CHAR;
638 -- J: W/V Changes
639 p_init_rec.filled_volume := FND_API.G_MISS_NUM;
640
641 --
642 -- Debug Statements
643 --
644 IF l_debug_on THEN
645 WSH_DEBUG_SV.pop(l_module_name);
646 END IF;
647 --
648 EXCEPTION
649 WHEN Others THEN
650 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
651 WSH_UTIL_CORE.add_message (x_return_status);
652 WSH_UTIL_CORE.default_handler('WSH_DELIVERY_DETAILS_PUB.Init_Changed_Attribute_Rec');
653
654
655 --
656 -- Debug Statements
657 --
658 IF l_debug_on THEN
659 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
660 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
661 END IF;
662 --
663 END Init_Changed_Attribute_Rec;
664
665 --========================================================================
666 -- PROCEDURE : Update_Shipping_Attributes
667 --
668 -- PARAMETERS: p_api_version_number known api versionerror buffer
669 -- p_init_msg_list initialize message stack
670 -- x_return_status return status
671 -- x_msg_count number of messages in the list
672 -- x_msg_data text of messages
673 -- p_changed_attributes changed attributes for delivery details
674 -- p_source_code source system
675 --
676 --
677 -- COMMENT : Validates Organization_id and Organization_code against view
678 -- org_organization_definitions. If both values are
679 -- specified then only Org_Id is used
680 --
681 --DESCRIPTION: This overloaded version of Update_Shipping_Attributes is created
682 -- to enable entry of multiple serial ranges for a given delivery
683 -- detail
684 --
685 --CREATED: During patchset I
686 --========================================================================
687
688 PROCEDURE Update_Shipping_Attributes (
689 -- Standard parameters
690 p_api_version_number IN NUMBER
691 , p_init_msg_list IN VARCHAR2
692 , p_commit IN VARCHAR2
693 , x_return_status OUT NOCOPY VARCHAR2
694 , x_msg_count OUT NOCOPY NUMBER
695 , x_msg_data OUT NOCOPY VARCHAR2
696
697 -- Procedure specific parameters
698 , p_changed_attributes IN WSH_DELIVERY_DETAILS_PUB.ChangedAttributeTabType
699 , p_source_code IN VARCHAR2
700 , p_container_flag IN VARCHAR2
701 )
702 IS
703
704 l_valid_index_tab WSH_UTIL_CORE.Id_Tab_Type;
705 l_valid_ids_tab WSH_UTIL_CORE.Id_Tab_Type;
706 l_msg_summary varchar2(2000) := NULL;
707 l_msg_details varchar2(4000) := NULL;
708 l_return_status VARCHAR2(30);
709 l_counter NUMBER;
710 l_index NUMBER;
711 l_api_version_number NUMBER := 1.0;
712 l_api_name VARCHAR2(30) := 'Update_Shipping_Attributes';
713 l_ship_to_location_id NUMBER;
714 invalid_source_code EXCEPTION;
715
716 m NUMBER := 0;
717
718
719 -- Harmonization Project
720 l_detail_info_tab WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Attr_Tbl_Type;
721 l_in_rec WSH_GLBL_VAR_STRCT_GRP.detailInRecType;
722 l_lpn_ids wsh_util_core.id_Tab_type;
723 l_out_Rec WSH_GLBL_VAR_STRCT_GRP.detailOutRecType;
724 l_msg_count NUMBER;
725 l_msg_data VARCHAR2(32767);
726 l_number_of_errors NUMBER := 0;
727 l_number_of_warnings NUMBER := 0;
728
729 --RTV changes
730 CURSOR c_valid_source_code(c_source_code varchar2) IS
731 SELECT lookup_code
732 FROM wsh_lookups
733 WHERE lookup_code = c_source_code
734 AND lookup_type = 'SOURCE_SYSTEM'
735 AND lookup_code not in ('OKE','PO')
736 AND enabled_flag = 'Y';
737
738 l_source_code VARCHAR2(30);
739 --RTV changes
740 --
741 l_debug_on BOOLEAN;
742 --
743 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_SHIPPING_ATTRIBUTES';
744 --
745 BEGIN
746 --
747 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
748 --
749 IF l_debug_on IS NULL
750 THEN
751 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
752 END IF;
753 --
754 SAVEPOINT UPDATE_SHIPPING_ATTR_PUB;
755
756 IF l_debug_on THEN
757 WSH_DEBUG_SV.push(l_module_name);
758 --
759 WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION_NUMBER',P_API_VERSION_NUMBER);
760 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',P_INIT_MSG_LIST);
761 WSH_DEBUG_SV.log(l_module_name,'P_COMMIT',P_COMMIT);
762 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_CODE',P_SOURCE_CODE);
763 WSH_DEBUG_SV.log(l_module_name,'P_CONTAINER_FLAG',P_CONTAINER_FLAG);
764 END IF;
765 --
766
767 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
768
769 -- Standard call to check for call compatibility
770 IF NOT FND_API.Compatible_API_Call(
771 l_api_version_number,
772 p_api_version_number,
773 l_api_name,
774 G_PKG_NAME) THEN
775 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
776 END IF;
777
778 -- Initialize message stack if required
779 IF FND_API.to_Boolean(p_init_msg_list) THEN
780 FND_MSG_PUB.initialize;
781 END IF;
782
783 -- <start of API logic>
784 -- sperera source_code has to be 'OE' or 'WSH'
785 --RTV changes
786 OPEN c_valid_source_code (p_source_code);
787 FETCH c_valid_source_code INTO l_source_code;
788 IF c_valid_source_code%NOTFOUND THEN
789 IF l_debug_on THEN
790 WSH_DEBUG_SV.log(l_module_name,'Invalid Source Code', p_source_code);
791 END IF;
792 CLOSE c_valid_source_code;
793 RAISE invalid_source_code;
794 END IF;
795 --RTV changes
796
797 -- call the copy procedure here
798
799 Copy_Attributes(x_detail_info_tab => l_detail_info_tab,
800 x_changed_attributes=>p_changed_attributes);
801
802 l_in_rec.caller := 'WSH_PUB';
803 l_in_rec.action_code := 'UPDATE';
804 --Bug 8900333: Locking the delivery details before calling the group API for further processing
805 BEGIN
806 savepoint before_lock;
807 l_index := l_detail_info_tab.FIRST;
808 WHILE l_index IS NOT NULL LOOP
809 l_valid_index_tab(l_index) :=l_index;
810 l_index := l_detail_info_tab.NEXT(l_index);
811 END LOOP;
812 IF l_valid_index_tab.count >0 THEN
813 IF l_debug_on THEN
814 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_PKG.LOCK_DELIVERY_DETAILS',WSH_DEBUG_SV.C_PROC_LEVEL);
815 END IF;
816 wsh_delivery_details_pkg.Lock_Delivery_Details(
817 p_rec_attr_tab => l_detail_info_tab,
818 p_caller => l_In_rec.caller,
819 p_valid_index_tab => l_valid_index_tab,
820 x_valid_ids_tab => l_valid_ids_tab,
821 x_return_status => l_return_status
822 );
823
824 wsh_util_core.api_post_call(
825 p_return_status => l_return_status,
826 x_num_warnings => l_number_of_warnings,
827 x_num_errors => l_number_of_errors,
828 p_msg_data => l_msg_data,
829 p_raise_error_flag => FALSE
830 );
831 IF l_return_status <> wsh_util_core.g_ret_sts_success THEN
832 wsh_util_core.add_message(wsh_util_core.g_ret_sts_error, l_module_name);
833 RAISE FND_API.G_EXC_ERROR;
834 END IF;
835
836 END IF;
837 EXCEPTION
838 WHEN FND_API.G_EXC_ERROR THEN
839 ROLLBACK TO before_lock;
840 IF l_debug_on THEN
841 wsh_debug_sv.logmsg(l_module_name, 'FND_API.G_EXC_ERROR exception has occured',WSH_DEBUG_SV.C_EXCEP_LEVEL);
842 END IF;
843 RAISE FND_API.G_EXC_ERROR ;
844 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
845 ROLLBACK TO before_lock;
846 IF l_debug_on THEN
847 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
848 END IF;
849 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
850 WHEN OTHERS THEN
851 ROLLBACK TO before_lock;
852 IF l_debug_on THEN
853 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
854 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
855 END IF;
856 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
857 END;
858
859 /* Patchset I: Harmonization Project. Call group API
860 All validations done by Group API */
861
862 IF l_debug_on THEN
863 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_GRP.CREATE_UPDATE_DELIVERY_DETAIL',WSH_DEBUG_SV.C_PROC_LEVEL);
864 END IF;
865 wsh_interface_grp.create_update_delivery_detail(
866 p_api_version_number => p_api_version_number,
867 p_init_msg_list => FND_API.G_FALSE,
868 p_commit => FND_API.G_FALSE,
869 x_return_status => l_return_status,
870 x_msg_count => l_msg_count,
871 x_msg_data => l_msg_data,
872 p_detail_info_tab => l_detail_info_tab,
873 p_IN_rec => l_in_rec,
874 x_OUT_rec => l_out_rec);
875
876 IF l_debug_on THEN
877 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
878 END IF;
879 --
880 wsh_util_core.api_post_call(
881 p_return_status => l_return_status,
882 x_num_warnings => l_number_of_warnings,
883 x_num_errors => l_number_of_errors,
884 p_msg_data => l_msg_data
885 );
886 --
887
888 IF l_number_of_warnings > 0 THEN
889 x_return_status := wsh_util_core.g_ret_sts_warning;
890 END IF;
891
892 -- report success
893 IF FND_API.TO_BOOLEAN(p_commit) THEN
894 COMMIT WORK;
895 END IF;
896
897 FND_MSG_PUB.Count_And_Get (
898 p_count => x_msg_count,
899 p_data => x_msg_data,
900 p_encoded => FND_API.G_FALSE
901 );
902
903 IF l_debug_on THEN
904 WSH_DEBUG_SV.pop(l_module_name);
905 END IF;
906 --
907 EXCEPTION
908 WHEN FND_API.G_EXC_ERROR THEN
909 ROLLBACK TO UPDATE_SHIPPING_ATTR_PUB;
910 x_return_status := FND_API.G_RET_STS_ERROR ;
911 wsh_util_core.add_message(x_return_status);
912 FND_MSG_PUB.Count_And_Get
913 (
914 p_count => x_msg_count,
915 p_data => x_msg_data,
916 p_encoded => FND_API.G_FALSE
917 );
918
919 IF l_debug_on THEN
920 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
921 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
922 END IF;
923 --
924 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
925 ROLLBACK TO UPDATE_SHIPPING_ATTR_PUB;
926 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
927 wsh_util_core.add_message(x_return_status, l_module_name);
928 FND_MSG_PUB.Count_And_Get
929 (
930 p_count => x_msg_count,
931 p_data => x_msg_data,
932 p_encoded => FND_API.G_FALSE
933 );
934 --
935
936 IF l_debug_on THEN
937 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
938 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
939 END IF;
940 --
941 WHEN invalid_source_code THEN
942 ROLLBACK TO UPDATE_SHIPPING_ATTR_PUB;
943 x_return_status := FND_API.G_RET_STS_ERROR;
944 FND_MESSAGE.Set_Name('WSH', 'WSH_INVALID_SOURCE_CODE');
945 FND_MESSAGE.SET_TOKEN('SOURCE_CODE',p_source_code );
946 WSH_UTIL_CORE.Add_Message(x_return_status);
947 --RTV changes
948 IF c_valid_source_code%ISOPEN THEN
949 CLOSE c_valid_source_code;
950 END IF;
951 --RTV changes
952 FND_MSG_PUB.Count_And_Get
953 (
954 p_count => x_msg_count,
955 p_data => x_msg_data,
956 p_encoded => FND_API.G_FALSE
957 );
958
959 IF l_debug_on THEN
960 WSH_DEBUG_SV.logmsg(l_module_name,'INVALID_SOURCE_CODE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
961 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:INVALID_SOURCE_CODE');
962 END IF;
963 --
964 WHEN Others THEN
965 ROLLBACK TO UPDATE_SHIPPING_ATTR_PUB;
966 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
967 WSH_UTIL_CORE.add_message (x_return_status);
968 WSH_UTIL_CORE.default_handler('WSH_DELIVERY_DETAILS_PUB.Update_Shipping_Attributes')
969 ;
970
971 FND_MSG_PUB.Count_And_Get
972 (
973 p_count => x_msg_count,
974 p_data => x_msg_data,
975 p_encoded => FND_API.G_FALSE
976 );
977
978 IF l_debug_on THEN
979 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
980 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
981 END IF;
982 --
983 END Update_Shipping_Attributes;
984
985
986 --Overloaded Update_Shipping_Attributes
987
988
989 --========================================================================
990 -- PROCEDURE : Update_Shipping_Attributes (overloaded)
991 --
992 -- PARAMETERS: p_api_version_number known api versionerror buffer
993 -- p_init_msg_list initialize message stack
994 -- x_return_status return status
995 -- x_msg_count number of messages in the list
996 -- x_msg_data text of messages
997 -- p_changed_attributes changed attributes for delivery details
998 -- p_source_code source system
999 -- p_serial_range_tab serial number range
1000 --
1001 --
1002 -- COMMENT : Validates Organization_id and Organization_code against view
1003 -- org_organization_definitions. If both values are
1004 -- specified then only Org_Id is used
1005 --========================================================================
1006
1007 PROCEDURE Update_Shipping_Attributes (
1008 -- Standard parameters
1009 p_api_version_number IN NUMBER
1010 , p_init_msg_list IN VARCHAR2
1011 , p_commit IN VARCHAR2
1012 , x_return_status OUT NOCOPY VARCHAR2
1013 , x_msg_count OUT NOCOPY NUMBER
1014 , x_msg_data OUT NOCOPY VARCHAR2
1015
1016 -- Procedure specific parameters
1017 , p_changed_attributes IN WSH_DELIVERY_DETAILS_PUB.ChangedAttributeTabType
1018 , p_source_code IN VARCHAR2
1019 , p_container_flag IN VARCHAR2
1020 , p_serial_range_tab IN WSH_GLBL_VAR_STRCT_GRP.ddSerialRangeTabType
1021 )
1022 IS
1023
1024 l_valid_index_tab WSH_UTIL_CORE.Id_Tab_Type;
1025 l_valid_ids_tab WSH_UTIL_CORE.Id_Tab_Type;
1026 l_msg_summary varchar2(2000) := NULL;
1027 l_msg_details varchar2(4000) := NULL;
1028 l_return_status VARCHAR2(30);
1029 l_counter NUMBER;
1030 l_index NUMBER;
1031 l_api_version_number NUMBER := 1.0;
1032 l_api_name VARCHAR2(30) := 'Update_Shipping_Attributes2';
1033 l_ship_to_location_id NUMBER;
1034 invalid_source_code EXCEPTION;
1035
1036 l_serial_range_tab WSH_GLBL_VAR_STRCT_GRP.ddSerialRangeTabType;
1037
1038 m NUMBER := 0;
1039
1040
1041 -- Harmonization Project
1042 l_detail_info_tab WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Attr_Tbl_Type;
1043 l_in_rec WSH_GLBL_VAR_STRCT_GRP.detailInRecType;
1044 l_lpn_ids wsh_util_core.id_Tab_type;
1045 l_out_Rec WSH_GLBL_VAR_STRCT_GRP.detailOutRecType;
1046 l_msg_count NUMBER;
1047 l_msg_data VARCHAR2(32767);
1048 l_number_of_errors NUMBER := 0;
1049 l_number_of_warnings NUMBER := 0;
1050
1051 --RTV changes
1052 CURSOR c_valid_source_code(c_source_code varchar2) IS
1053 SELECT lookup_code
1054 FROM wsh_lookups
1055 WHERE lookup_code = c_source_code
1056 AND lookup_type = 'SOURCE_SYSTEM'
1057 AND lookup_code not in ('OKE','PO')
1058 AND enabled_flag = 'Y';
1059
1060 l_source_code VARCHAR2(30);
1061 --RTV changes
1062 --
1063 l_debug_on BOOLEAN;
1064 --
1065 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_SHIPPING_ATTRIBUTES';
1066 --
1067 BEGIN
1068 --
1069 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1070 --
1071 IF l_debug_on IS NULL
1072 THEN
1073 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1074 END IF;
1075 --
1076 SAVEPOINT UPDATE_SHIPPING_ATTR_PUB2;
1077
1078 IF l_debug_on THEN
1079 WSH_DEBUG_SV.push(l_module_name);
1080 --
1081 WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION_NUMBER',P_API_VERSION_NUMBER);
1082 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',P_INIT_MSG_LIST);
1083 WSH_DEBUG_SV.log(l_module_name,'P_COMMIT',P_COMMIT);
1084 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_CODE',P_SOURCE_CODE);
1085 WSH_DEBUG_SV.log(l_module_name,'P_CONTAINER_FLAG',P_CONTAINER_FLAG);
1086 WSH_DEBUG_SV.log(l_module_name,'P_SERIAL_RANGE_TAB.COUNT', P_SERIAL_RANGE_TAB.COUNT);
1087
1088
1089 END IF;
1090 --
1091
1092 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1093
1094 -- Standard call to check for call compatibility
1095 IF NOT FND_API.Compatible_API_Call(
1096 l_api_version_number,
1097 p_api_version_number,
1098 l_api_name,
1099 G_PKG_NAME) THEN
1100 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1101 END IF;
1102
1103 -- Initialize message stack if required
1104 IF FND_API.to_Boolean(p_init_msg_list) THEN
1105 FND_MSG_PUB.initialize;
1106 END IF;
1107
1108 -- <start of API logic>
1109 -- sperera source_code has to be 'OE' or 'WSH'
1110 --RTV changes
1111 OPEN c_valid_source_code (p_source_code);
1112 FETCH c_valid_source_code INTO l_source_code;
1113 IF c_valid_source_code%NOTFOUND THEN
1114 IF l_debug_on THEN
1115 WSH_DEBUG_SV.log(l_module_name,'Invalid Source Code', p_source_code);
1116 END IF;
1117 CLOSE c_valid_source_code;
1118 RAISE invalid_source_code;
1119 END IF;
1120 --RTV changes
1121
1122 -- call the copy procedure here
1123
1124 Copy_Attributes(x_detail_info_tab => l_detail_info_tab,
1125 x_changed_attributes=>p_changed_attributes);
1126
1127 -- frontport bug 5049214 - Trim leading spaces from serial number
1128 l_serial_range_tab := p_serial_range_tab;
1129 l_index := l_serial_range_tab.FIRST;
1130 WHILE l_index IS NOT NULL LOOP
1131 l_serial_range_tab(l_index).from_serial_number :=
1132 LTRIM(RTRIM(l_serial_range_tab(l_index).from_serial_number));
1133 l_serial_range_tab(l_index).to_serial_number :=
1134 LTRIM(RTRIM(l_serial_range_tab(l_index).to_serial_number));
1135 l_index := l_serial_range_tab.NEXT(l_index);
1136 END LOOP;
1137
1138 l_in_rec.caller := 'WSH_PUB';
1139 l_in_rec.action_code := 'UPDATE';
1140 --Bug 8900333: Locking the delivery details before calling the group API for further processing
1141 BEGIN
1142 savepoint before_lock;
1143 l_index := l_detail_info_tab.FIRST;
1144 WHILE l_index IS NOT NULL LOOP
1145 l_valid_index_tab(l_index) :=l_index;
1146 l_index := l_detail_info_tab.NEXT(l_index);
1147 END LOOP;
1148 IF l_valid_index_tab.count >0 THEN
1149 IF l_debug_on THEN
1150 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_PKG.LOCK_DELIVERY_DETAILS',WSH_DEBUG_SV.C_PROC_LEVEL);
1151 END IF;
1152 wsh_delivery_details_pkg.Lock_Delivery_Details(
1153 p_rec_attr_tab => l_detail_info_tab,
1154 p_caller => l_In_rec.caller,
1155 p_valid_index_tab => l_valid_index_tab,
1156 x_valid_ids_tab => l_valid_ids_tab,
1157 x_return_status => l_return_status
1158 );
1159
1160 wsh_util_core.api_post_call(
1161 p_return_status => l_return_status,
1162 x_num_warnings => l_number_of_warnings,
1163 x_num_errors => l_number_of_errors,
1164 p_msg_data => l_msg_data,
1165 p_raise_error_flag => FALSE
1166 );
1167 IF l_return_status <> wsh_util_core.g_ret_sts_success THEN
1168 wsh_util_core.add_message(wsh_util_core.g_ret_sts_error, l_module_name);
1169 RAISE FND_API.G_EXC_ERROR;
1170 END IF;
1171
1172 END IF;
1173 EXCEPTION
1174 WHEN FND_API.G_EXC_ERROR THEN
1175 ROLLBACK TO before_lock;
1176 IF l_debug_on THEN
1177 wsh_debug_sv.logmsg(l_module_name, 'FND_API.G_EXC_ERROR exception has occured',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1178 END IF;
1179 RAISE FND_API.G_EXC_ERROR ;
1180 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1181 ROLLBACK TO before_lock;
1182 IF l_debug_on THEN
1183 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1184 END IF;
1185 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1186 WHEN OTHERS THEN
1187 ROLLBACK TO before_lock;
1188 IF l_debug_on THEN
1189 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1190 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1191 END IF;
1192 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1193 END;
1194
1195 /* Patchset I: Harmonization Project. Call group API
1196 All validations done by Group API */
1197
1198 IF l_debug_on THEN
1199 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_GRP.CREATE_UPDATE_DELIVERY_DETAIL',WSH_DEBUG_SV.C_PROC_LEVEL);
1200 END IF;
1201
1202 wsh_delivery_details_grp.create_update_delivery_detail(
1203 p_api_version_number => p_api_version_number,
1204 p_init_msg_list => FND_API.G_FALSE,
1205 p_commit => FND_API.G_FALSE,
1206 x_return_status => l_return_status,
1207 x_msg_count => l_msg_count,
1208 x_msg_data => l_msg_data,
1209 p_detail_info_tab => l_detail_info_tab,
1210 p_IN_rec => l_in_rec,
1211 x_OUT_rec => l_out_rec,
1212 p_serial_range_tab => l_serial_range_tab
1213 );
1214
1215 IF l_debug_on THEN
1216 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
1217 END IF;
1218 --
1219 wsh_util_core.api_post_call(
1220 p_return_status => l_return_status,
1221 x_num_warnings => l_number_of_warnings,
1222 x_num_errors => l_number_of_errors,
1223 p_msg_data => l_msg_data
1224 );
1225 --
1226
1227 IF l_number_of_warnings > 0 THEN
1228 x_return_status := wsh_util_core.g_ret_sts_warning;
1229 END IF;
1230 -- report success
1231 IF FND_API.TO_BOOLEAN(p_commit) THEN
1232 COMMIT WORK;
1233 END IF;
1234
1235 FND_MSG_PUB.Count_And_Get (
1236 p_count => x_msg_count,
1237 p_data => x_msg_data,
1238 p_encoded => FND_API.G_FALSE
1239 );
1240
1241 IF l_debug_on THEN
1242 WSH_DEBUG_SV.pop(l_module_name);
1243 END IF;
1244 --
1245 EXCEPTION
1246 WHEN FND_API.G_EXC_ERROR THEN
1247 ROLLBACK TO UPDATE_SHIPPING_ATTR_PUB2;
1248 x_return_status := FND_API.G_RET_STS_ERROR ;
1249 wsh_util_core.add_message(x_return_status);
1250 FND_MSG_PUB.Count_And_Get
1251 (
1252 p_count => x_msg_count,
1253 p_data => x_msg_data,
1254 p_encoded => FND_API.G_FALSE
1255 );
1256
1257 IF l_debug_on THEN
1258 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1259 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1260 END IF;
1261 --
1262 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1263 ROLLBACK TO UPDATE_SHIPPING_ATTR_PUB2;
1264 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1265 wsh_util_core.add_message(x_return_status, l_module_name);
1266 FND_MSG_PUB.Count_And_Get
1267 (
1268 p_count => x_msg_count,
1269 p_data => x_msg_data,
1270 p_encoded => FND_API.G_FALSE
1271 );
1272 --
1273
1274 IF l_debug_on THEN
1275 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1276 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1277 END IF;
1278 --
1279 WHEN invalid_source_code THEN
1280 ROLLBACK TO UPDATE_SHIPPING_ATTR_PUB2;
1281 x_return_status := FND_API.G_RET_STS_ERROR;
1282 FND_MESSAGE.Set_Name('WSH', 'WSH_INVALID_SOURCE_CODE');
1283 FND_MESSAGE.SET_TOKEN('SOURCE_CODE',p_source_code );
1284 WSH_UTIL_CORE.Add_Message(x_return_status);
1285 --RTV changes
1286 IF c_valid_source_code%ISOPEN THEN
1287 CLOSE c_valid_source_code;
1288 END IF;
1289 --RTV changes
1290
1291 FND_MSG_PUB.Count_And_Get
1292 (
1293 p_count => x_msg_count,
1294 p_data => x_msg_data,
1295 p_encoded => FND_API.G_FALSE
1296 );
1297
1298 IF l_debug_on THEN
1299 WSH_DEBUG_SV.logmsg(l_module_name,'INVALID_SOURCE_CODE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1300 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:INVALID_SOURCE_CODE');
1301 END IF;
1302 --
1303 WHEN Others THEN
1304 ROLLBACK TO UPDATE_SHIPPING_ATTR_PUB2;
1305 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1306 WSH_UTIL_CORE.add_message (x_return_status);
1307 WSH_UTIL_CORE.default_handler('WSH_DELIVERY_DETAILS_PUB.Update_Shipping_Attributes')
1308 ;
1309
1310 FND_MSG_PUB.Count_And_Get
1311 (
1312 p_count => x_msg_count,
1313 p_data => x_msg_data,
1314 p_encoded => FND_API.G_FALSE
1315 );
1316 IF l_debug_on THEN
1317 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1318 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1319 END IF;
1320 --
1321 END Update_Shipping_Attributes;
1322
1323
1324 PROCEDURE Get_Detail_Status(
1325 p_delivery_detail_id IN NUMBER
1326 , x_line_status OUT NOCOPY VARCHAR2
1327 , x_return_status OUT NOCOPY VARCHAR2
1328 )
1329 IS
1330 CURSOR del_assign IS
1331 SELECT delivery_id, parent_delivery_detail_id
1332 FROM wsh_delivery_assignments_v
1333 WHERE delivery_detail_id = p_delivery_detail_id;
1334 l_assign_rec del_assign%ROWTYPE;
1335
1336 CURSOR del_status(c_del_id NUMBER) IS
1337 SELECT status_code
1338 FROM wsh_new_deliveries
1339 WHERE delivery_id = c_del_id;
1340
1341 l_del_status VARCHAR2(100) := NULL;
1342 l_msg_summary VARCHAR2(3000);
1343 x_msg_count NUMBER;
1344 --
1345 l_debug_on BOOLEAN;
1346 --
1347 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_DETAIL_STATUS';
1348 --
1349 BEGIN
1350 --
1351 -- Debug Statements
1352 --
1353 --
1354 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1355 --
1356 IF l_debug_on IS NULL
1357 THEN
1358 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1359 END IF;
1360 --
1361 IF l_debug_on THEN
1362 WSH_DEBUG_SV.push(l_module_name);
1363 --
1364 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_DETAIL_ID',P_DELIVERY_DETAIL_ID);
1365 END IF;
1366 --
1367 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1368
1369 -- get line status for delivery detail
1370 OPEN del_assign;
1371 FETCH del_assign INTO l_assign_rec;
1372 IF (l_assign_rec.delivery_id IS NOT NULL) THEN
1373 OPEN del_status(l_assign_rec.delivery_id);
1374 FETCH del_status INTO l_del_status;
1375 CLOSE del_status;
1376 END IF;
1377 CLOSE del_assign;
1378
1379 IF (l_del_status IN ('CO', 'IT', 'CL')) THEN
1380 x_line_status := 'SIC';
1381 ELSIF (l_assign_rec.parent_delivery_detail_id IS NOT NULL) THEN
1382 x_line_status := 'PK';
1383 ELSIF( l_assign_rec.parent_delivery_detail_id IS NULL) THEN
1384 x_line_status := 'OK';
1385 ELSE
1386 NULL;
1387 END IF;
1388
1389 /*
1390 EXCEPTION
1391 when others then
1392 wsh_util_core.default_handler('WSH_SHIP_CONFRIM_ACTIONS.SHIP_CONFIRM_A_TRIP_STOP');
1393 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1394 WSH_UTIL_CORE.get_messages('Y', l_msg_summary, l_msg_details, x_msg_count);
1395 if x_msg_count > 1 then
1396 x_msg_data := l_msg_summary || l_msg_details;
1397 else
1398 x_msg_data := l_msg_summary;
1399 end if;
1400 */
1401 --
1402 -- Debug Statements
1403 --
1404 IF l_debug_on THEN
1405 WSH_DEBUG_SV.pop(l_module_name);
1406 END IF;
1407 --
1408 END get_detail_status;
1409
1410 PROCEDURE Autocreate_Deliveries(
1411 -- Standard parameters
1412 p_api_version_number IN NUMBER
1413 , p_init_msg_list IN VARCHAR2
1414 , p_commit IN VARCHAR2
1415 , x_return_status OUT NOCOPY VARCHAR2
1416 , x_msg_count OUT NOCOPY NUMBER
1417 , x_msg_data OUT NOCOPY VARCHAR2
1418 , p_line_rows IN WSH_UTIL_CORE.id_tab_type
1419 , x_del_rows OUT NOCOPY wsh_util_core.id_tab_type
1420 )
1421 IS
1422 l_dummy_rows WSH_UTIL_CORE.id_tab_type;
1423 l_api_version_number NUMBER := 1.0;
1424 l_api_name CONSTANT VARCHAR2(30):= 'Autocreate_Deliveries';
1425 l_return_status VARCHAR2(30);
1426 autocreate_delivery_failed EXCEPTION;
1427 l_msg_summary varchar2(2000) := NULL;
1428 l_msg_details varchar2(4000) := NULL;
1429
1430 -- Harmonization Project
1431 l_action_prms WSH_GLBL_VAR_STRCT_GRP.dd_action_parameters_rec_type;
1432 l_action_out_rec WSH_GLBL_VAR_STRCT_GRP.dd_action_out_rec_type;
1433 l_dummy_qty NUMBER;
1434 l_dummy_qty2 NUMBER;
1435 l_dummy_ids wsh_util_core.id_tab_type;
1436 l_msg_count NUMBER;
1437 l_msg_data VARCHAR2(32767);
1438 l_number_of_errors NUMBER := 0;
1439 l_number_of_warnings NUMBER := 0;
1440 --
1441 l_debug_on BOOLEAN;
1442 --
1443 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'AUTOCREATE_DELIVERIES';
1444 --
1445 BEGIN
1446 -- Standard begin of API savepoint
1447 --
1448 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1449 --
1450 IF l_debug_on IS NULL
1451 THEN
1452 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1453 END IF;
1454 --
1455 SAVEPOINT AUTOCREATE_DEL_PUB;
1456 --
1457 IF l_debug_on THEN
1458 WSH_DEBUG_SV.push(l_module_name);
1459 --
1460 WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION_NUMBER',P_API_VERSION_NUMBER);
1461 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',P_INIT_MSG_LIST);
1462 WSH_DEBUG_SV.log(l_module_name,'P_COMMIT',P_COMMIT);
1463 END IF;
1464 --
1465
1466
1467 IF NOT FND_API.compatible_api_call(
1468 l_api_version_number,
1469 p_api_version_number,
1470 l_api_name,
1471 G_PKG_NAME) THEN
1472 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1473 END IF;
1474
1475 -- Check p_init_msg_list
1476 IF FND_API.to_boolean(p_init_msg_list) THEN
1477 FND_MSG_PUB.initialize;
1478 END IF;
1479 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1480
1481 l_action_prms.caller := 'WSH_PUB';
1482 l_action_prms.action_code := 'AUTOCREATE-DEL';
1483
1484 /* Patchset I: Harmonization Project. Call group API
1485 All validations done by Group API */
1486
1487 IF l_debug_on THEN
1488 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_INTERFACE_GRP.DELIVERY_DETAIL_ACTION',WSH_DEBUG_SV.C_PROC_LEVEL);
1489 END IF;
1490
1491 wsh_interface_grp.delivery_detail_action(
1492 p_api_version_number => p_api_version_number,
1493 p_init_msg_list => FND_API.G_FALSE,
1494 p_commit => FND_API.G_FALSE,
1495 x_return_status => l_return_status,
1496 x_msg_count => l_msg_count,
1497 x_msg_data => l_msg_data,
1498 p_detail_id_tab => p_line_rows,
1499 p_action_prms => l_action_prms ,
1500 x_action_out_rec => l_action_out_rec);
1501
1502 IF l_debug_on THEN
1503 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
1504 END IF;
1505 --
1506 wsh_util_core.api_post_call(
1507 p_return_status => l_return_status,
1508 x_num_warnings => l_number_of_warnings,
1509 x_num_errors => l_number_of_errors,
1510 p_msg_data => l_msg_data
1511 );
1512
1513 x_del_rows := l_action_out_rec.delivery_id_tab;
1514
1515 IF l_number_of_warnings > 0 THEN
1516 x_return_status := wsh_util_core.g_ret_sts_warning;
1517 END IF;
1518
1519
1520 FND_MSG_PUB.Count_And_Get
1521 (
1522 p_count => x_msg_count,
1523 p_data => x_msg_data,
1524 p_encoded => FND_API.G_FALSE
1525 );
1526
1527
1528 IF FND_API.TO_BOOLEAN(p_commit) THEN
1529 COMMIT WORK;
1530 END IF;
1531
1532
1533 IF l_debug_on THEN
1534 WSH_DEBUG_SV.pop(l_module_name);
1535 END IF;
1536 --
1537 EXCEPTION
1538 WHEN FND_API.G_EXC_ERROR THEN
1539 ROLLBACK TO AUTOCREATE_DEL_PUB;
1540 x_return_status := FND_API.G_RET_STS_ERROR ;
1541 wsh_util_core.add_message(x_return_status);
1542 FND_MSG_PUB.Count_And_Get
1543 (
1544 p_count => x_msg_count,
1545 p_data => x_msg_data,
1546 p_encoded => FND_API.G_FALSE
1547 );
1548
1549 IF l_debug_on THEN
1550 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1551 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1552 END IF;
1553
1554 WHEN autocreate_delivery_failed THEN
1555 ROLLBACK TO AUTOCREATE_DEL_PUB;
1556 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1557 FND_MSG_PUB.Count_And_Get
1558 (
1559 p_count => x_msg_count,
1560 p_data => x_msg_data,
1561 p_encoded => FND_API.G_FALSE
1562 );
1563
1564 IF l_debug_on THEN
1565 WSH_DEBUG_SV.logmsg(l_module_name,'AUTOCREATE_DELIVERY_FAILED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1566 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:AUTOCREATE_DELIVERY_FAILED');
1567 END IF;
1568
1569 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1570 ROLLBACK TO AUTOCREATE_DEL_PUB;
1571 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1572 wsh_util_core.add_message(x_return_status, l_module_name);
1573 FND_MSG_PUB.Count_And_Get
1574 (
1575 p_count => x_msg_count,
1576 p_data => x_msg_data,
1577 p_encoded => FND_API.G_FALSE
1578 );
1579 --
1580
1581 IF l_debug_on THEN
1582 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1583 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1584 END IF;
1585 WHEN OTHERS THEN
1586 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1587 ROLLBACK TO AUTOCREATE_DEL_PUB;
1588 wsh_util_core.add_message(x_return_status, l_module_name);
1589 WSH_UTIL_CORE.default_handler('WSH_DELIVERY_DETAILS_PUB.Autocreate_Deliveries');
1590 FND_MSG_PUB.Count_And_Get
1591 (
1592 p_count => x_msg_count,
1593 p_data => x_msg_data,
1594 p_encoded => FND_API.G_FALSE
1595 );
1596 --
1597 IF l_debug_on THEN
1598 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1599 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1600 END IF;
1601 --
1602 END Autocreate_Deliveries;
1603
1604 PROCEDURE Autocreate_del_trip(
1605 -- Standard parameters
1606 p_api_version_number IN NUMBER
1607 , p_init_msg_list IN VARCHAR2
1608 , p_commit IN VARCHAR2
1609 , x_return_status OUT NOCOPY VARCHAR2
1610 , x_msg_count OUT NOCOPY NUMBER
1611 , x_msg_data OUT NOCOPY VARCHAR2
1612 , p_line_rows IN WSH_UTIL_CORE.id_tab_type
1613 , x_del_rows OUT NOCOPY WSH_UTIL_CORE.id_tab_type
1614 , x_trip_id OUT NOCOPY NUMBER
1615 , x_trip_name OUT NOCOPY VARCHAR2
1616 )
1617 IS
1618
1619 l_trip_rows WSH_UTIL_CORE.id_tab_type;
1620 l_first NUMBER := 0;
1621 l_debug_on BOOLEAN;
1622 --
1623 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'AUTOCREATE_DEL_TRIP - single trip';
1624
1625 BEGIN
1626 -- Standard begin of API savepoint
1627 --
1628 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1629 --
1630 IF l_debug_on IS NULL THEN
1631 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1632 END IF;
1633 --
1634 SAVEPOINT AUTOCREATE_TRIP_PUB;
1635 --
1636 IF l_debug_on THEN
1637 WSH_DEBUG_SV.push(l_module_name);
1638 END IF;
1639 --
1640
1641
1642 IF l_debug_on THEN
1643 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_PUB.Autocreate_del_trip - multiple trips',WSH_DEBUG_SV.C_PROC_LEVEL);
1644 END IF;
1645
1646
1647 WSH_DELIVERY_DETAILS_PUB.Autocreate_del_trip(
1648 -- Standard parameters
1649 p_api_version_number => p_api_version_number,
1650 p_init_msg_list => p_init_msg_list,
1651 p_commit => p_commit,
1652 x_return_status => x_return_status,
1653 x_msg_count => x_msg_count,
1654 x_msg_data => x_msg_data,
1655 p_line_rows => p_line_rows,
1656 x_del_rows => x_del_rows,
1657 x_trip_rows => l_trip_rows );
1658
1659 IF l_trip_rows.count > 0 THEN
1660 l_first := l_trip_rows.first;
1661 x_trip_id := l_trip_rows(l_first);
1662 IF x_trip_id IS NOT NULL THEN
1663 x_trip_name := wsh_trips_pvt.get_name(x_trip_id);
1664 ELSE
1665 x_trip_name := NULL;
1666 END IF;
1667 ELSE
1668 x_trip_id := NULL;
1669 x_trip_name := NULL;
1670 END IF;
1671
1672 IF l_debug_on THEN
1673 WSH_DEBUG_SV.pop(l_module_name);
1674 END IF;
1675
1676 EXCEPTION
1677
1678
1679 WHEN OTHERS THEN
1680
1681 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1682 ROLLBACK TO AUTOCREATE_TRIP_PUB;
1683 wsh_util_core.add_message(x_return_status, l_module_name);
1684 WSH_UTIL_CORE.default_handler('WSH_DELIVERY_DETAILS_PUB.Autocreate_Del_Trip');
1685 FND_MSG_PUB.Count_And_Get
1686 (
1687 p_count => x_msg_count,
1688 p_data => x_msg_data,
1689 p_encoded => FND_API.G_FALSE
1690 );
1691
1692 IF l_debug_on THEN
1693 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1694 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1695 END IF;
1696
1697 END Autocreate_del_trip;
1698
1699
1700 PROCEDURE Autocreate_del_trip(
1701 -- Standard parameters
1702 p_api_version_number IN NUMBER
1703 , p_init_msg_list IN VARCHAR2
1704 , p_commit IN VARCHAR2
1705 , x_return_status OUT NOCOPY VARCHAR2
1706 , x_msg_count OUT NOCOPY NUMBER
1707 , x_msg_data OUT NOCOPY VARCHAR2
1708 , p_line_rows IN WSH_UTIL_CORE.id_tab_type
1709 , x_del_rows OUT NOCOPY WSH_UTIL_CORE.id_tab_type
1710 , x_trip_rows OUT NOCOPY WSH_UTIL_CORE.id_tab_type
1711 )
1712 IS
1713 l_api_name CONSTANT VARCHAR2(30):= 'Autocreate_del_trip';
1714 l_api_version_number CONSTANT NUMBER := 1.0;
1715 l_return_status VARCHAR2(30);
1716 l_msg_summary varchar2(2000) := NULL;
1717 l_msg_details varchar2(4000) := NULL;
1718 l_org_rows wsh_util_core.id_tab_type; -- bug 1668578
1719 autocreate_trip_failed EXCEPTION;
1720
1721 -- Harmonization Project
1722 l_action_prms WSH_GLBL_VAR_STRCT_GRP.dd_action_parameters_rec_type;
1723 l_action_out_rec WSH_GLBL_VAR_STRCT_GRP.dd_action_out_rec_type;
1724 l_dummy_qty NUMBER;
1725 l_dummy_qty2 NUMBER;
1726 l_del_rows wsh_util_core.id_tab_type;
1727 l_msg_count NUMBER;
1728 l_msg_data VARCHAR2(32767);
1729 l_number_of_errors NUMBER := 0;
1730 l_number_of_warnings NUMBER := 0;
1731 --
1732 l_debug_on BOOLEAN;
1733 --
1734 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'AUTOCREATE_DEL_TRIP - multiple trips';
1735 --
1736 BEGIN
1737 -- Standard begin of API savepoint
1738 --
1739 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1740 --
1741 IF l_debug_on IS NULL
1742 THEN
1743 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1744 END IF;
1745 --
1746 SAVEPOINT AUTOCREATE_TRIP_PUB;
1747 --
1748 IF l_debug_on THEN
1749 WSH_DEBUG_SV.push(l_module_name);
1750 --
1751 WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION_NUMBER',P_API_VERSION_NUMBER);
1752 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',P_INIT_MSG_LIST);
1753 WSH_DEBUG_SV.log(l_module_name,'P_COMMIT',P_COMMIT);
1754 END IF;
1755 --
1756
1757 IF NOT FND_API.compatible_api_call(
1758 l_api_version_number,
1759 p_api_version_number,
1760 l_api_name,
1761 G_PKG_NAME) THEN
1762 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1763 END IF;
1764
1765 -- Check p_init_msg_list
1766 IF FND_API.to_boolean(p_init_msg_list) THEN
1767 FND_MSG_PUB.initialize;
1768 END IF;
1769 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1770
1771 l_action_prms.caller := 'WSH_PUB';
1772 l_action_prms.action_code := 'AUTOCREATE-TRIP';
1773
1774 /* Patchset I: Harmonization Project. Call group API
1775 All validations done by Group API */
1776
1777 IF l_debug_on THEN
1778 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_INTERFACE_GRP.DELIVERY_DETAIL_ACTION',WSH_DEBUG_SV.C_PROC_LEVEL);
1779 END IF;
1780
1781 wsh_interface_grp.delivery_detail_action(
1782 p_api_version_number => p_api_version_number,
1783 p_init_msg_list => FND_API.G_FALSE,
1784 p_commit => FND_API.G_FALSE,
1785 x_return_status => l_return_status,
1786 x_msg_count => l_msg_count,
1787 x_msg_data => l_msg_data,
1788 p_detail_id_tab => p_line_rows,
1789 p_action_prms => l_action_prms ,
1790 x_action_out_rec => l_action_out_rec);
1791
1792 IF l_debug_on THEN
1793 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
1794 END IF;
1795 --
1796 wsh_util_core.api_post_call(
1797 p_return_status => l_return_status,
1798 x_num_warnings => l_number_of_warnings,
1799 x_num_errors => l_number_of_errors,
1800 p_msg_data => l_msg_data
1801 );
1802
1803 x_del_rows := l_action_out_rec.delivery_id_tab;
1804 x_trip_rows:= l_action_out_rec.result_id_tab;
1805
1806 IF l_number_of_warnings > 0 THEN
1807 x_return_status := wsh_util_core.g_ret_sts_warning;
1808 END IF;
1809
1810 IF FND_API.TO_BOOLEAN(p_commit) THEN
1811 COMMIT WORK;
1812 END IF;
1813
1814 FND_MSG_PUB.Count_And_Get
1815 (
1816 p_count => x_msg_count,
1817 p_data => x_msg_data,
1818 p_encoded => FND_API.G_FALSE
1819 );
1820
1821
1822 IF l_debug_on THEN
1823 WSH_DEBUG_SV.pop(l_module_name);
1824 END IF;
1825
1826 EXCEPTION
1827 WHEN FND_API.G_EXC_ERROR THEN
1828 ROLLBACK TO AUTOCREATE_TRIP_PUB;
1829 x_return_status := FND_API.G_RET_STS_ERROR ;
1830 wsh_util_core.add_message(x_return_status);
1831 FND_MSG_PUB.Count_And_Get
1832 (
1833 p_count => x_msg_count,
1834 p_data => x_msg_data,
1835 p_encoded => FND_API.G_FALSE
1836 );
1837
1838 IF l_debug_on THEN
1839 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1840 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1841 END IF;
1842
1843 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1844 ROLLBACK TO AUTOCREATE_TRIP_PUB;
1845 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1846 wsh_util_core.add_message(x_return_status, l_module_name);
1847 FND_MSG_PUB.Count_And_Get
1848 (
1849 p_count => x_msg_count,
1850 p_data => x_msg_data,
1851 p_encoded => FND_API.G_FALSE
1852 );
1853 --
1854
1855 IF l_debug_on THEN
1856 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1857 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1858 END IF;
1859
1860
1861 WHEN autocreate_trip_failed THEN
1862 ROLLBACK TO AUTOCREATE_TRIP_PUB;
1863 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1864 FND_MSG_PUB.Count_And_Get
1865 (
1866 p_count => x_msg_count,
1867 p_data => x_msg_data,
1868 p_encoded => FND_API.G_FALSE
1869 );
1870
1871 IF l_debug_on THEN
1872 WSH_DEBUG_SV.logmsg(l_module_name,'AUTOCREATE_TRIP_FAILED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1873 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:AUTOCREATE_TRIP_FAILED');
1874 END IF;
1875
1876 WHEN OTHERS THEN
1877
1878 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1879 ROLLBACK TO AUTOCREATE_TRIP_PUB;
1880 wsh_util_core.add_message(x_return_status, l_module_name);
1881 WSH_UTIL_CORE.default_handler('WSH_DELIVERY_DETAILS_PUB.Autocreate_Del_Trip');
1882 FND_MSG_PUB.Count_And_Get
1883 (
1884 p_count => x_msg_count,
1885 p_data => x_msg_data,
1886 p_encoded => FND_API.G_FALSE
1887 );
1888 --
1889 IF l_debug_on THEN
1890 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1891 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1892 END IF;
1893 --
1894 END Autocreate_del_trip;
1895
1896
1897 PROCEDURE Copy_Attributes(
1898 x_detail_info_tab OUT NOCOPY WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Attr_Tbl_Type
1899 , x_changed_attributes IN WSH_DELIVERY_DETAILS_PUB.ChangedAttributeTabType)
1900
1901 IS
1902
1903 l_index NUMBER;
1904 l_debug_on BOOLEAN;
1905 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'COPY_ATTRIBUTES';
1906
1907 BEGIN
1908 --
1909 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1910 --
1911 IF l_debug_on IS NULL
1912 THEN
1913 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1914 END IF;
1915 --
1916 IF l_debug_on THEN
1917
1918 WSH_DEBUG_SV.push(l_module_name);
1919 WSH_DEBUG_SV.log(l_module_name,'X_CHANGED_ATTRIBUTES.COUNT', X_CHANGED_ATTRIBUTES.COUNT);
1920
1921 END IF;
1922
1923 l_index := x_changed_attributes.FIRST;
1924
1925
1926 WHILE l_index IS NOT NULL
1927 LOOP
1928
1929 x_detail_info_tab(l_index).source_header_id := x_changed_attributes(l_index).source_header_id;
1930 x_detail_info_tab(l_index).source_line_id := x_changed_attributes(l_index).source_line_id;
1931 x_detail_info_tab(l_index).sold_to_contact_id := x_changed_attributes(l_index).sold_to_contact_id;
1932 x_detail_info_tab(l_index).ship_to_contact_id := x_changed_attributes(l_index).ship_to_contact_id;
1933 x_detail_info_tab(l_index).deliver_to_contact_id := x_changed_attributes(l_index).deliver_to_contact_id;
1934 x_detail_info_tab(l_index).intmed_ship_to_contact_id := x_changed_attributes(l_index).intmed_ship_to_contact_id;
1935 x_detail_info_tab(l_index).ship_tolerance_above := x_changed_attributes(l_index).ship_tolerance_above;
1936 x_detail_info_tab(l_index).ship_tolerance_below := x_changed_attributes(l_index).ship_tolerance_below;
1937 x_detail_info_tab(l_index).preferred_grade := x_changed_attributes(l_index).preferred_grade;
1938 x_detail_info_tab(l_index).subinventory := x_changed_attributes(l_index).subinventory;
1939 x_detail_info_tab(l_index).revision := x_changed_attributes(l_index).revision;
1940 x_detail_info_tab(l_index).lot_number := x_changed_attributes(l_index).lot_number;
1941 -- HW OPMCONV - No need for sublot_number
1942 -- x_detail_info_tab(l_index).sublot_number := x_changed_attributes(l_index).sublot_number;
1943 x_detail_info_tab(l_index).customer_requested_lot_flag := x_changed_attributes(l_index).customer_requested_lot_flag;
1944 x_detail_info_tab(l_index).serial_number := x_changed_attributes(l_index).serial_number;
1945 x_detail_info_tab(l_index).to_serial_number := x_changed_attributes(l_index).to_serial_number;
1946 x_detail_info_tab(l_index).locator_id := x_changed_attributes(l_index).locator_id;
1947 x_detail_info_tab(l_index).date_requested := x_changed_attributes(l_index).date_requested;
1948 x_detail_info_tab(l_index).date_scheduled := x_changed_attributes(l_index).date_scheduled;
1949 x_detail_info_tab(l_index).master_container_item_id := x_changed_attributes(l_index).master_container_item_id;
1950 x_detail_info_tab(l_index).detail_container_item_id := x_changed_attributes(l_index).detail_container_item_id;
1951 x_detail_info_tab(l_index).ship_method_code := x_changed_attributes(l_index).shipping_method_code;
1952 x_detail_info_tab(l_index).carrier_id := x_changed_attributes(l_index).carrier_id;
1953 x_detail_info_tab(l_index).freight_terms_code := x_changed_attributes(l_index).freight_terms_code;
1954 x_detail_info_tab(l_index).shipment_priority_code := x_changed_attributes(l_index).shipment_priority_code;
1955 x_detail_info_tab(l_index).fob_code := x_changed_attributes(l_index).fob_code;
1956 x_detail_info_tab(l_index).dep_plan_required_flag := x_changed_attributes(l_index).dep_plan_required_flag;
1957 x_detail_info_tab(l_index).customer_prod_seq := x_changed_attributes(l_index).customer_prod_seq;
1958 x_detail_info_tab(l_index).customer_dock_code := x_changed_attributes(l_index).customer_dock_code;
1959 x_detail_info_tab(l_index).gross_weight := x_changed_attributes(l_index).gross_weight;
1960 x_detail_info_tab(l_index).net_weight := x_changed_attributes(l_index).net_weight;
1961 x_detail_info_tab(l_index).weight_uom_code := x_changed_attributes(l_index).weight_uom_code;
1962 x_detail_info_tab(l_index).volume := x_changed_attributes(l_index).volume;
1963 x_detail_info_tab(l_index).volume_uom_code := x_changed_attributes(l_index).volume_uom_code;
1964 x_detail_info_tab(l_index).top_model_line_id := x_changed_attributes(l_index).top_model_line_id;
1965 x_detail_info_tab(l_index).ship_set_id := x_changed_attributes(l_index).ship_set_id;
1966 x_detail_info_tab(l_index).ato_line_id := x_changed_attributes(l_index).ato_line_id;
1967 x_detail_info_tab(l_index).arrival_set_id := x_changed_attributes(l_index).arrival_set_id;
1968 x_detail_info_tab(l_index).ship_model_complete_flag := x_changed_attributes(l_index).ship_model_complete_flag;
1969 x_detail_info_tab(l_index).cust_po_number := x_changed_attributes(l_index).cust_po_number;
1970 x_detail_info_tab(l_index).released_status := x_changed_attributes(l_index).released_status;
1971 x_detail_info_tab(l_index).packing_instructions := x_changed_attributes(l_index).packing_instructions;
1972 x_detail_info_tab(l_index).shipping_instructions := x_changed_attributes(l_index).shipping_instructions;
1973 x_detail_info_tab(l_index).container_name := x_changed_attributes(l_index).container_name;
1974 x_detail_info_tab(l_index).container_flag := x_changed_attributes(l_index).container_flag ;
1975 x_detail_info_tab(l_index).delivery_detail_id := x_changed_attributes(l_index).delivery_detail_id;
1976 x_detail_info_tab(l_index).shipped_quantity := x_changed_attributes(l_index).shipped_quantity ;
1977 x_detail_info_tab(l_index).cycle_count_quantity := x_changed_attributes(l_index).cycle_count_quantity ;
1978 /* Bug 3055126 added shipped_quantity 2 and cycle_count_quantity2 and removed from defaulting below */
1979 x_detail_info_tab(l_index).shipped_quantity2 := x_changed_attributes(l_index).shipped_quantity2 ;
1980 x_detail_info_tab(l_index).cycle_count_quantity2 := x_changed_attributes(l_index).cycle_count_quantity2 ;x_detail_info_tab(l_index).tracking_number := x_changed_attributes(l_index).tracking_number ;
1981 x_detail_info_tab(l_index).attribute_category := x_changed_attributes(l_index).attribute_category;
1982 x_detail_info_tab(l_index).attribute1 := x_changed_attributes(l_index).attribute1;
1983 x_detail_info_tab(l_index).attribute2 := x_changed_attributes(l_index).attribute2;
1984 x_detail_info_tab(l_index).attribute3 := x_changed_attributes(l_index).attribute3;
1985 x_detail_info_tab(l_index).attribute4 := x_changed_attributes(l_index).attribute4;
1986 x_detail_info_tab(l_index).attribute5 := x_changed_attributes(l_index).attribute5;
1987 x_detail_info_tab(l_index).attribute6 := x_changed_attributes(l_index).attribute6;
1988 x_detail_info_tab(l_index).attribute7 := x_changed_attributes(l_index).attribute7;
1989 x_detail_info_tab(l_index).attribute8 := x_changed_attributes(l_index).attribute8;
1990 x_detail_info_tab(l_index).attribute9 := x_changed_attributes(l_index).attribute9;
1991 x_detail_info_tab(l_index).attribute10 := x_changed_attributes(l_index).attribute10;
1992 x_detail_info_tab(l_index).attribute11 := x_changed_attributes(l_index).attribute11;
1993 x_detail_info_tab(l_index).attribute12 := x_changed_attributes(l_index).attribute12;
1994 x_detail_info_tab(l_index).attribute13 := x_changed_attributes(l_index).attribute13;
1995 x_detail_info_tab(l_index).attribute14 := x_changed_attributes(l_index).attribute14;
1996 x_detail_info_tab(l_index).attribute15 := x_changed_attributes(l_index).attribute15;
1997 -- Bug 3723831 :tp attributes also part of the public API update_shipping_attributes
1998 x_detail_info_tab(l_index).tp_attribute_category := x_changed_attributes(l_index).tp_attribute_category;
1999 x_detail_info_tab(l_index).tp_attribute1 := x_changed_attributes(l_index).tp_attribute1;
2000 x_detail_info_tab(l_index).tp_attribute2 := x_changed_attributes(l_index).tp_attribute2;
2001 x_detail_info_tab(l_index).tp_attribute3 := x_changed_attributes(l_index).tp_attribute3;
2002 x_detail_info_tab(l_index).tp_attribute4 := x_changed_attributes(l_index).tp_attribute4;
2003 x_detail_info_tab(l_index).tp_attribute5 := x_changed_attributes(l_index).tp_attribute5;
2004 x_detail_info_tab(l_index).tp_attribute6 := x_changed_attributes(l_index).tp_attribute6;
2005 x_detail_info_tab(l_index).tp_attribute7 := x_changed_attributes(l_index).tp_attribute7;
2006 x_detail_info_tab(l_index).tp_attribute8 := x_changed_attributes(l_index).tp_attribute8;
2007 x_detail_info_tab(l_index).tp_attribute9 := x_changed_attributes(l_index).tp_attribute9;
2008 x_detail_info_tab(l_index).tp_attribute10 := x_changed_attributes(l_index).tp_attribute10;
2009 x_detail_info_tab(l_index).tp_attribute11 := x_changed_attributes(l_index).tp_attribute11;
2010 x_detail_info_tab(l_index).tp_attribute12 := x_changed_attributes(l_index).tp_attribute12;
2011 x_detail_info_tab(l_index).tp_attribute13 := x_changed_attributes(l_index).tp_attribute13;
2012 x_detail_info_tab(l_index).tp_attribute14 := x_changed_attributes(l_index).tp_attribute14;
2013 x_detail_info_tab(l_index).tp_attribute15 := x_changed_attributes(l_index).tp_attribute15;
2014
2015 -- J: W/V Changes
2016 x_detail_info_tab(l_index).filled_volume := x_changed_attributes(l_index).filled_volume;
2017 -- Bug 4146352 : Added seal_code and load_seq_number.
2018 --
2019 x_detail_info_tab(l_index).seal_code := x_changed_attributes(l_index).seal_code;
2020 x_detail_info_tab(l_index).load_seq_number := x_changed_attributes(l_index).load_seq_number;
2021 /* Start of fix for Bug 2766446
2022 For those attributes that are not available in public api record,
2023 need to send G_MISS values so that the database values are used for such attributes */
2024
2025 x_detail_info_tab(l_index).batch_id := FND_API.G_MISS_NUM;
2026 x_detail_info_tab(l_index).cancelled_quantity := FND_API.G_MISS_NUM;
2027 x_detail_info_tab(l_index).cancelled_quantity2 := FND_API.G_MISS_NUM;
2028 x_detail_info_tab(l_index).classification := FND_API.G_MISS_CHAR;
2029 x_detail_info_tab(l_index).commodity_code_cat_id := FND_API.G_MISS_NUM;
2030 x_detail_info_tab(l_index).container_type_code := FND_API.G_MISS_CHAR;
2031 x_detail_info_tab(l_index).country_of_origin := FND_API.G_MISS_CHAR;
2032 x_detail_info_tab(l_index).currency_code := FND_API.G_MISS_CHAR;
2033 x_detail_info_tab(l_index).cust_model_serial_number := FND_API.G_MISS_CHAR;
2034 x_detail_info_tab(l_index).customer_id := FND_API.G_MISS_NUM;
2035 x_detail_info_tab(l_index).customer_item_id := FND_API.G_MISS_NUM;
2036 x_detail_info_tab(l_index).customer_job := FND_API.G_MISS_CHAR;
2037 x_detail_info_tab(l_index).customer_production_line := FND_API.G_MISS_CHAR;
2038 x_detail_info_tab(l_index).deliver_to_location_id := FND_API.G_MISS_NUM;
2039 x_detail_info_tab(l_index).deliver_to_site_use_id := FND_API.G_MISS_NUM;
2040 x_detail_info_tab(l_index).delivered_quantity := FND_API.G_MISS_NUM;
2041 x_detail_info_tab(l_index).delivered_quantity2 := FND_API.G_MISS_NUM;
2042 x_detail_info_tab(l_index).freight_class_cat_id := FND_API.G_MISS_NUM;
2043 x_detail_info_tab(l_index).fill_percent := FND_API.G_MISS_NUM;
2044 x_detail_info_tab(l_index).hazard_class_id := FND_API.G_MISS_NUM;
2045 x_detail_info_tab(l_index).hold_code := FND_API.G_MISS_CHAR;
2046 x_detail_info_tab(l_index).inspection_flag := FND_API.G_MISS_CHAR;
2047 x_detail_info_tab(l_index).intmed_ship_to_location_id := FND_API.G_MISS_NUM;
2048 x_detail_info_tab(l_index).inv_interfaced_flag := FND_API.G_MISS_CHAR;
2049 x_detail_info_tab(l_index).inventory_item_id := FND_API.G_MISS_NUM;
2050 x_detail_info_tab(l_index).item_description := FND_API.G_MISS_CHAR;
2051 -- Bug 4146352 : Need to comment seal_code as this is now added to
2052 -- the public api record structure.
2053 -- x_detail_info_tab(l_index).load_seq_number := FND_API.G_MISS_NUM;
2054 x_detail_info_tab(l_index).lpn_id := FND_API.G_MISS_NUM;
2055 x_detail_info_tab(l_index).master_serial_number := FND_API.G_MISS_CHAR;
2056 x_detail_info_tab(l_index).maximum_load_weight := FND_API.G_MISS_NUM;
2057 x_detail_info_tab(l_index).maximum_volume := FND_API.G_MISS_NUM;
2058 x_detail_info_tab(l_index).minimum_fill_percent := FND_API.G_MISS_NUM;
2059 x_detail_info_tab(l_index).move_order_line_id := FND_API.G_MISS_NUM;
2060 x_detail_info_tab(l_index).movement_id := FND_API.G_MISS_NUM;
2061 x_detail_info_tab(l_index).mvt_stat_status := FND_API.G_MISS_CHAR;
2062 x_detail_info_tab(l_index).oe_interfaced_flag := FND_API.G_MISS_CHAR;
2063 x_detail_info_tab(l_index).org_id := FND_API.G_MISS_NUM;
2064 x_detail_info_tab(l_index).organization_id := FND_API.G_MISS_NUM;
2065 x_detail_info_tab(l_index).original_subinventory := FND_API.G_MISS_CHAR;
2066 x_detail_info_tab(l_index).pickable_flag := FND_API.G_MISS_CHAR;
2067 x_detail_info_tab(l_index).picked_quantity := FND_API.G_MISS_NUM;
2068 x_detail_info_tab(l_index).picked_quantity2 := FND_API.G_MISS_NUM;
2069 x_detail_info_tab(l_index).project_id := FND_API.G_MISS_NUM;
2070 x_detail_info_tab(l_index).quality_control_quantity := FND_API.G_MISS_NUM;
2071 x_detail_info_tab(l_index).quality_control_quantity2 := FND_API.G_MISS_NUM;
2072 x_detail_info_tab(l_index).received_quantity := FND_API.G_MISS_NUM;
2073 x_detail_info_tab(l_index).received_quantity2 := FND_API.G_MISS_NUM;
2074 x_detail_info_tab(l_index).released_flag := FND_API.G_MISS_CHAR;
2075 x_detail_info_tab(l_index).request_id := FND_API.G_MISS_NUM;
2076 x_detail_info_tab(l_index).requested_quantity := FND_API.G_MISS_NUM;
2077 x_detail_info_tab(l_index).requested_quantity_uom := FND_API.G_MISS_CHAR;
2078 x_detail_info_tab(l_index).requested_quantity_uom2 := FND_API.G_MISS_CHAR;
2079 x_detail_info_tab(l_index).requested_quantity2 := FND_API.G_MISS_NUM;
2080 -- Bug 4146352 : Need to comment seal_code as this is now added to
2081 -- the public api record structure.
2082 -- x_detail_info_tab(l_index).seal_code := FND_API.G_MISS_CHAR;
2083 x_detail_info_tab(l_index).ship_from_location_id := FND_API.G_MISS_NUM;
2084 x_detail_info_tab(l_index).ship_to_location_id := FND_API.G_MISS_NUM;
2085 x_detail_info_tab(l_index).ship_to_site_use_id := FND_API.G_MISS_NUM;
2086 x_detail_info_tab(l_index).source_code := FND_API.G_MISS_CHAR;
2087 x_detail_info_tab(l_index).source_header_number := FND_API.G_MISS_CHAR;
2088 x_detail_info_tab(l_index).source_header_type_id := FND_API.G_MISS_NUM;
2089 x_detail_info_tab(l_index).source_header_type_name := FND_API.G_MISS_CHAR;
2090 x_detail_info_tab(l_index).source_line_number := FND_API.G_MISS_CHAR;
2091 x_detail_info_tab(l_index).source_line_set_id := FND_API.G_MISS_NUM;
2092 x_detail_info_tab(l_index).split_from_detail_id := FND_API.G_MISS_NUM;
2093 x_detail_info_tab(l_index).src_requested_quantity := FND_API.G_MISS_NUM;
2094 x_detail_info_tab(l_index).src_requested_quantity_uom := FND_API.G_MISS_CHAR;
2095 x_detail_info_tab(l_index).src_requested_quantity_uom2 := FND_API.G_MISS_CHAR;
2096 x_detail_info_tab(l_index).src_requested_quantity2 := FND_API.G_MISS_NUM;
2097 x_detail_info_tab(l_index).task_id := FND_API.G_MISS_NUM;
2098 x_detail_info_tab(l_index).transaction_temp_id := FND_API.G_MISS_NUM;
2099 x_detail_info_tab(l_index).unit_number := FND_API.G_MISS_CHAR;
2100 x_detail_info_tab(l_index).unit_price := FND_API.G_MISS_NUM;
2101 -- J: W/V Changes
2102 x_detail_info_tab(l_index).unit_weight := FND_API.G_MISS_NUM;
2103 x_detail_info_tab(l_index).unit_volume := FND_API.G_MISS_NUM;
2104
2105 /* End of fix for Bug 2766446 */
2106
2107 l_index := x_changed_attributes.NEXT(l_index);
2108 END LOOP;
2109
2110 IF l_debug_on THEN
2111 WSH_DEBUG_SV.pop(l_module_name);
2112 END IF;
2113
2114 EXCEPTION
2115
2116 WHEN FND_API.G_EXC_ERROR THEN
2117
2118 IF l_debug_on THEN
2119 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2120 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
2121 END IF;
2122 raise;
2123
2124
2125 WHEN OTHERS THEN
2126
2127 wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_PUB.UPDATE_ATTRIBUTES');
2128 IF l_debug_on THEN
2129 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2130 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2131 END IF;
2132 raise;
2133
2134 END Copy_Attributes;
2135
2136
2137 END WSH_DELIVERY_DETAILS_PUB;