1 PACKAGE BODY WSH_PO_CMG_PVT as
2 /* $Header: WSHPPCMB.pls 120.0.12000000.2 2007/01/24 18:22:51 bsadri ship $ */
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_PO_CMG_PVT';
6 LIMITED_PRECISION NUMBER := 5;
7
8 /*========================================================================
9 -- PROCEDURE : Reapprove_PO
10 -- HISTORY : Created the API.
11 --========================================================================*/
12 -- Start of comments
13 -- API name : Reapprove_PO
14 -- Type : Public
15 -- Pre-reqs : None.
16 -- Function : This is the wrapper API which gets called when PO goes for Re-approval.
17 -- 1.For every Line_location_id passed by PO, first propagate
18 -- the attributes other than quantity on the Delivery details,
19 -- by calling Update_Attributes.
20 -- 2.Next, take care of the quantity increments and decrements,
21 -- by calling Update_Quantity.
22 -- 3.When ever delivery grouping attributes changes, the line
23 -- gets unassigned from the delivery.Collect the list of delivery details id's
24 -- that needs to be unassigned and at the end call the Unassign_Multiple_details
25 -- API for the same.
26 -- 4.If the inventory_item_id is updated on the delivery_detail or if the qty is
27 -- updated to any value other than zero, then those delivery_detail_id's are
28 -- Collected to l_wr_vol_dd_id list .Weight and volume gets recalculated
29 -- by calling WSH_WV_UTILS.Detail_Weight_Volume which makes use of this
30 -- l_wt_vol_dd_id list.
31 -- 5.Call WSH_DELIVERY_LEGS_ACTIONS.Mark_Reprice_Required API using the
32 -- l_wt_vol_dd_id list to reprice the delivery_details.
33 -- This also gets called from Correction by setting action code as
34 -- RECEIPT'.
35 --
36 -- Parameters :
37 -- IN OUT:
38 -- p_line_rec IN OUT OE_WSH_BULK_GRP.line_rec_type DEFAULT NULL
39 -- This record structure contains information about the lines to be updated.
40 -- The information may include header id,line id ,line location id and other po
41 -- attributes..
42 -- p_action_prms IN OUT NOCOPY WSH_BULK_TYPES_GRP.action_parameters_rectype
43 -- The record which specifies the caller, actio to be performed(REAPPROVE)
44 --IN :
45 -- p_dd_list IN dd_list_type
46 -- This is the record structure which contains the list of delivery details and the
47 -- associated po shipment line id that needs to be updated.This list gets populated
48 -- only when called from Cancel_ASN or Revert Matching Transaction.
49 -- OUT:
50 -- x_return_status OUT NOCOPY VARCHAR2
51 -- Version : 1.0
52 -- Previous version 1.0
53
54 PROCEDURE Reapprove_PO(
55 p_line_rec IN OUT NOCOPY OE_WSH_BULK_GRP.line_rec_type,
56 p_action_prms IN OUT NOCOPY WSH_BULK_TYPES_GRP.action_parameters_rectype,
57 p_dd_list IN dd_list_type,
58 x_return_status OUT NOCOPY VARCHAR2)
59 IS
60
61 l_return_status VARCHAR2(10);
62 l_num_warnings NUMBER := 0;
63 l_num_errors NUMBER := 0;
64 l_dd_id_unassigned wsh_util_core.id_tab_type;
65 l_wt_vol_dd_id wsh_util_core.id_tab_type;
66 l_init_msg_list VARCHAR2(30) := NULL;
67 l_msg_count NUMBER;
68 l_msg_data VARCHAR2(3000);
69 l_commit VARCHAR2(1);
70 l_validation_level NUMBER;
71 l_delivery_id NUMBER;
72 l_delivery_name VARCHAR2(150);
73 l_action_prms wsh_glbl_var_strct_grp.dd_action_parameters_rec_type;
74
75 --
76 l_debug_on BOOLEAN;
77 --
78 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'REAPPROVE_PO';
79 --
80 BEGIN
81
82 --
83 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
84 --
85 IF l_debug_on IS NULL
86 THEN
87 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
88 END IF;
89 --
90 --
91 -- Debug Statements
92 --
93 IF l_debug_on THEN
94 WSH_DEBUG_SV.push(l_module_name);
95 END IF;
96 --
97 SAVEPOINT Reapprove_PO_PVT;
98
99 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
100
101 IF p_action_prms.action_code = 'REAPPROVE_PO' OR
102 p_action_prms.action_code = 'CANCEL_ASN' THEN
103
104 -- If action code is 'CANCEL_ASN', only Update_Attributes API is called.
105 -- For action code 'REAPPROVE_PO', both Update_Quantity and Update_Attributes
106 -- are called.
107
108 IF l_debug_on THEN
109 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit Update_Attributes',WSH_DEBUG_SV.C_PROC_LEVEL);
110 END IF;
111
112 Update_Attributes(
113 p_line_rec => p_line_rec,
114 p_action_prms => p_action_prms,
115 p_dd_list => p_dd_list,
116 p_dd_id_unassigned => l_dd_id_unassigned,
117 p_wt_vol_dd_id => l_wt_vol_dd_id,
118 x_return_status => l_return_status);
119
120 --
121 -- Debug Statements
122 --
123 IF l_debug_on THEN
124 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
125 END IF;
126 --
127 wsh_util_core.api_post_call(
128 p_return_status => l_return_status,
129 x_num_warnings => l_num_warnings,
130 x_num_errors => l_num_errors);
131
132 END IF;
133
134 IF p_action_prms.action_code = 'REAPPROVE_PO' OR
135 p_action_prms.action_code = 'RECEIPT' OR
136 p_action_prms.action_code = 'ASN' THEN
137
138 -- If action code is 'RECEIPT' or 'ASN', only Update_quantity API is called.
139 -- For action code 'REAPPROVE_PO', both Update_Quantity and Update_Attributes
140 -- are called.
141
142
143 IF l_debug_on THEN
144 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit Update_Quantity',WSH_DEBUG_SV.C_PROC_LEVEL);
145 WSH_DEBUG_SV.log(l_module_name,'l_wt_vol_dd_id.COUNT is' ,l_wt_vol_dd_id.COUNT);
146 END IF;
147
148 Update_quantity(
149 p_line_rec => p_line_rec,
150 p_action_prms => p_action_prms,
151 p_dd_id_unassigned => l_dd_id_unassigned,
152 p_wt_vol_dd_id => l_wt_vol_dd_id,
153 x_return_status => l_return_status);
154
155 IF l_debug_on THEN
156 WSH_DEBUG_SV.log(l_module_name,'l_wt_vol_dd_id.COUNT is' ,l_wt_vol_dd_id.COUNT);
157 END IF;
158
159 --
160 -- Debug Statements
161 --
162 IF l_debug_on THEN
163 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
164 END IF;
165 --
166 wsh_util_core.api_post_call(
167 p_return_status => l_return_status,
168 x_num_warnings => l_num_warnings,
169 x_num_errors => l_num_errors);
170 END IF;
171
172 --
173 -- Debug Statements
174 --
175 IF l_dd_id_unassigned.count > 0 then
176 --Call GRP API to unassign the dd from delivery
177 IF l_debug_on THEN
178 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.UNASSIGN_MULTIPLE_DETAILS',WSH_DEBUG_SV.C_PROC_LEVEL);
179 END IF;
180
181 l_action_prms.caller := wsh_util_core.C_IB_PO_PREFIX;
182
183 WSH_DELIVERY_DETAILS_ACTIONS.unassign_multiple_details(
184 p_rec_of_detail_ids => l_dd_id_unassigned,
185 p_from_delivery => 'Y',
186 p_from_container => 'N',
187 x_return_status => l_return_status,
188 p_validate_flag => 'N',
189 p_action_prms => l_action_prms);
190
191 wsh_util_core.api_post_call(
192 p_return_status => l_return_status,
193 x_num_warnings => l_num_warnings,
194 x_num_errors => l_num_errors);
195
196 l_dd_id_unassigned.delete;
197
198 END IF;
199
200 IF l_debug_on THEN
201 WSH_DEBUG_SV.log(l_module_name,'l_wt_vol_dd_id.COUNT -3 is' ,l_wt_vol_dd_id.COUNT);
202 END IF;
203 IF l_wt_vol_dd_id.COUNT > 0 THEN
204 -- recalculate the wt/volume and reprice the delviery detail.
205
206 IF l_debug_on THEN
207 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WV_UTILS.Detail_Weight_Volume',WSH_DEBUG_SV.C_PROC_LEVEL);
208 WSH_DEBUG_SV.log(l_module_name,'l_wt_vol_dd_id.COUNT is' ,l_wt_vol_dd_id.COUNT);
209 END IF;
210
211 WSH_WV_UTILS.Detail_Weight_Volume(
212 p_detail_rows => l_wt_vol_dd_id,
213 p_override_flag => 'Y',
214 p_calc_wv_if_frozen => 'N',
215 x_return_status => l_return_status);
216
217 IF l_debug_on THEN
218 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
219 END IF;
220 --
221 wsh_util_core.api_post_call(
222 p_return_status => l_return_status,
223 x_num_warnings => l_num_warnings,
224 x_num_errors => l_num_errors);
225
226 IF l_debug_on THEN
227 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_LEGS_ACTIONS.MARK_REPRICE_REQUIRED',WSH_DEBUG_SV.C_PROC_LEVEL);
228 END IF;
229
230 WSH_DELIVERY_LEGS_ACTIONS.Mark_Reprice_Required(
231 p_entity_type => 'DELIVERY_DETAIL',
232 p_entity_ids => l_wt_vol_dd_id,
233 x_return_status => l_return_status);
234
235 --
236 -- Debug Statements
237 --
238 IF l_debug_on THEN
239 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
240 END IF;
241 --
242 wsh_util_core.api_post_call(
243 p_return_status => l_return_status,
244 x_num_warnings => l_num_warnings,
245 x_num_errors => l_num_errors);
246 --END IF;
247
248 l_wt_vol_dd_id.delete;
249 --l_dd_id_unassigned.delete;
250
251 END IF;
252
253 --
254 -- Debug Statements
255 --
256 IF l_debug_on THEN
257 WSH_DEBUG_SV.pop(l_module_name);
258 END IF;
259 --
260 IF l_num_errors > 0 THEN
261 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
262 ELSIF l_num_warnings > 0 THEN
263 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
264 END IF;
265
266
267 EXCEPTION
268 WHEN FND_API.G_EXC_ERROR THEN
269 ROLLBACK TO Reapprove_PO_PVT;
270 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
271 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status,l_module_name);
272
273 --
274 -- Debug Statements
275 --
276 IF l_debug_on THEN
277 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
278 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
279 END IF;
280 --
281 WHEN OTHERS THEN
282 ROLLBACK TO Reapprove_PO_PVT;
283 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
284 WSH_UTIL_CORE.DEFAULT_HANDLER('WSH_PO_CMG_PVT.Reapprove_PO',l_module_name);
285
286 --
287 -- Debug Statements
288 --
289 IF l_debug_on THEN
290 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
291 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
292 END IF;
293 --
294 END Reapprove_PO;
295
296
297 /*========================================================================
298 -- PROCEDURE : Update_Attributes
299 -- HISTORY : Created the API.
300 --========================================================================*/
301 -- Start of comments
302 -- API name : Update_Attributes
303 -- Type : Public
304 -- Pre-reqs : None.
305 -- Function : This API update all the non-quantity attributes in
306 -- wsh_delivery_details when PO goes for reapproval.
307 -- This API internally calls Update_dd_attribues API which does the
308 -- actual update on wsh_delivery_details.
309 -- In case of PO Reapproval scenario, the p_dd_list will
310 -- be just defined, but will not have any data in it.
311 -- In case of ASN Cancellation or Reverting the Matching
312 -- Transactions, the p_dd_list will have the set of
313 -- delivery_details that are getting cancelled/reverted.
314 -- Parameters :
315 -- IN OUT:
316 -- p_line_rec IN OE_WSH_BULK_GRP.line_rec_type DEFAULT NULL
317 -- This record structure contains information about the lines to be updated.
318 -- The information may include header id,line id ,line location id and other po
319 -- attributes..
320 -- p_dd_id_unassigned IN OUT NOCOPY wsh_util_core.id_tab_type
321 -- This contains the list of delviery details that needs to be unassigned from
322 -- the respective delivery.
323 -- p_wt_vol_dd_id IN OUT NOCOPY wsh_util_core.id_tab_type,
324 -- This contains the list of delviery details for which weight and volume needs to
325 -- be re-calculated.
326 -- p_action_prms IN OUT NOCOPY WSH_BULK_TYPES_GRP.action_parameters_rectype
327 -- The record which specifies the caller, actio to be performed(REAPPROVE,CANCEL_ASN,RECEIPT,ASN)
328 --IN :
329 -- p_dd_list IN dd_list_type
330 -- This is the record structure which contains the list of delivery details and the
331 -- associated po shipment line id that needs to be updated.This list gets populated
332 -- only when called from Cancel_ASN or Revert Matching Transaction.
333 -- OUT:
334 -- x_return_status OUT NOCOPY VARCHAR2
335 -- Version : 1.0
336 -- Previous version 1.0
337 -- Initial version 1.0
338 -- End of comments
339
340 Procedure Update_Attributes(
341 p_line_rec IN OUT NOCOPY OE_WSH_BULK_GRP.line_rec_type,
342 p_action_prms IN WSH_BULK_TYPES_GRP.action_parameters_rectype,
343 p_dd_list IN dd_list_type,
344 p_dd_id_unassigned IN OUT NOCOPY wsh_util_core.id_tab_type,
345 p_wt_vol_dd_id IN OUT NOCOPY wsh_util_core.id_tab_type,
346 x_return_status OUT NOCOPY VARCHAR2) IS
347
348 l_api_version CONSTANT NUMBER := 1.0;
349 l_msg_count NUMBER := 0;
350 l_msg_data VARCHAR2(1000) := NULL;
351 l_po_release_rec_type PO_FTE_INTEGRATION_GRP.po_release_rec_type;
352 l_po_shipment_line_id NUMBER;
353 l_return_status VARCHAR2(1);
354 l_num_warnings NUMBER := 0;
355 l_num_errors NUMBER := 0;
356 l_rcv_shipment_line_id NUMBER;
357
358 --
359 l_debug_on BOOLEAN;
360 --
361 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_ATTRIBUTES';
362 --
363 BEGIN
364
365 --If this routine is being called from the PO Reapproval Case
366 --the entire p_line_rec is always passed for which all
367 --the open delivery details are to be updated.
368
369 --
370 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
371 --
372 IF l_debug_on IS NULL
373 THEN
374 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
375 END IF;
376 --
377 --
378 -- Debug Statements
379 --
380 IF l_debug_on THEN
381 WSH_DEBUG_SV.push(l_module_name);
382 END IF;
383 --
384 SAVEPOINT Update_Attributes_PVT;
385
386 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
387
388 IF p_dd_list.delivery_detail_id.count = 0 THEN
389 -- If this routine is being called from the PO Reapproval Case
390 -- the p_dd_list is null.Only p_line_rec will be populated.
391 IF l_debug_on THEN
392 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit Update_dd_attributes',WSH_DEBUG_SV.C_PROC_LEVEL);
393 END IF;
394 --{
395 FOR i in p_line_rec.po_shipment_line_id.FIRST..p_line_rec.po_shipment_line_id.LAST
396 LOOP
397 -- Update_dd_attributes is called directly to update the non quantity attributes.
398 Update_dd_attributes(
399 p_line_rec => p_line_rec,
400 p_action_prms => p_action_prms,
401 p_dd_id => null,
402 p_dd_id_unassigned => p_dd_id_unassigned,
403 p_wt_vol_dd_id => p_wt_vol_dd_id,
404 p_line_rec_index => i,
405 x_return_status => l_return_status);
406
407 --
408 -- Debug Statements
409 --
410 IF l_debug_on THEN
411 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
412 END IF;
413 --
414 wsh_util_core.api_post_call(
415 p_return_status => l_return_status,
416 x_num_warnings => l_num_warnings,
417 x_num_errors => l_num_errors);
418
419 END LOOP;
420 ELSE
421
422 --If this routine is being called from the Cancel_asn or the
423 --revert Matching Transactions, entire list of delivery details
424 --will be passed which are being cancelled or reverted.
425 --Need to derive the p_line_rec information by calling get_po_rcv_attributes API.
426 l_po_shipment_line_id := 0;
427
428 For i in p_dd_list.delivery_detail_id.FIRST..p_dd_list.delivery_detail_id.LAST
429 LOOP
430 IF p_dd_list.po_shipment_line_id(i) <> l_po_shipment_line_id THEN
431 --{
432 --
433 -- Debug Statements
434 --
435 IF l_debug_on THEN
436 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_INBOUND_UTIL_PKG.GET_PO_RCV_ATTRIBUTES',WSH_DEBUG_SV.C_PROC_LEVEL);
437 END IF;
438 --
439 -- Deriving the p_line_rec information.
440 WSH_INBOUND_UTIL_PKG.get_po_rcv_attributes(
441 p_po_line_location_id => p_dd_list.po_shipment_line_id(i),
442 p_rcv_shipment_line_id => l_rcv_shipment_line_id,
443 x_line_rec => p_line_rec,
444 x_return_status => l_return_status);
445
446 --
447 -- Debug Statements
448 --
449 IF l_debug_on THEN
450 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
451 END IF;
452 --
453 wsh_util_core.api_post_call(
454 p_return_status => l_return_status,
455 x_num_warnings => l_num_warnings,
456 x_num_errors => l_num_errors);
457
458 l_po_shipment_line_id := p_dd_list.po_shipment_line_id(i);
459
460 --}
461 END IF;
462
463 IF l_debug_on THEN
464 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit Update_dd_attributes',WSH_DEBUG_SV.C_PROC_LEVEL);
465 END IF;
466 -- Once p_line_rec is derived, call update_dd_attributes to update wsh_delivery_details.
467
468 Update_dd_attributes(
469 p_line_rec => p_line_rec,
470 p_action_prms => p_action_prms,
471 p_dd_id => p_dd_list.delivery_detail_id(i),
472 p_dd_id_unassigned => p_dd_id_unassigned,
473 p_wt_vol_dd_id => p_wt_vol_dd_id,
474 p_line_rec_index => p_line_rec.header_id.COUNT,
475 x_return_status => l_return_status);
476
477 --
478 -- Debug Statements
479 --
480 IF l_debug_on THEN
481 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
482 END IF;
483 --
484 wsh_util_core.api_post_call(
485 p_return_status => l_return_status,
486 x_num_warnings => l_num_warnings,
487 x_num_errors => l_num_errors);
488
489 END LOOP;
490 --}
491 END IF;
492
493 --
494 -- Debug Statements
495 --
496 IF l_debug_on THEN
497 WSH_DEBUG_SV.pop(l_module_name);
498 END IF;
499 --
500 IF l_num_errors > 0 THEN
501 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
502 ELSIF l_num_warnings > 0 THEN
503 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
504 END IF;
505
506
507 EXCEPTION
508 WHEN FND_API.G_EXC_ERROR THEN
509 ROLLBACK TO Update_Attributes_PVT;
510 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
511 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status,l_module_name);
512
513 --
514 -- Debug Statements
515 --
516 IF l_debug_on THEN
517 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
518 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
519 END IF;
520 --
521 WHEN OTHERS THEN
522 ROLLBACK TO Update_Attributes_PVT;
523 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
524 WSH_UTIL_CORE.DEFAULT_HANDLER('WSH_PO_CMG_PVT.Update_Attributes',l_module_name);
525
526 --
527 -- Debug Statements
528 --
529 IF l_debug_on THEN
530 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
531 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
532 END IF;
533 --
534 END Update_Attributes;
535
536 /*========================================================================
537 -- PROCEDURE : Update_dd_Attributes
538 -- HISTORY : Created the API.
539 ========================================================================*/
540 -- Start of comments
541 -- API name : Update_dd_Attributes
542 -- Type : Public
543 -- Pre-reqs : None.
544 -- Function : This is the main API which actually synch up the
545 -- delivery detail attributes with the Purchase Order attribtues
546 -- in case of PO reapproval/Cancel ASN.
547 -- 1. Checks if any of the delviery grouping attribute is changed
548 -- on the PO or not.If the delivery grouping attribtue is changed, then
549 -- - Collect the delivery_detail_id into p_dd_id_unassigned list
550 -- which will be later used to unassign the delivery details from
551 -- the delivery.
552 -- 2. Check if the Shipping control is changed on the PO or not.
553 -- - If the Shipping control on the PO is changed from 'SUPPLIER' to
554 -- 'BUYER', then all the open delivery details are updated with the
555 -- new value.
556 -- - If the shipping Control is changed from 'BUYER' to 'SUPPLIER', then
557 -- then this change is updated in Shipping only if routing response
558 -- is not send.
559 -- If the associated delivery detail is assigned to delviery, then
560 -- it gets unassigned from the delivery. Also an exception gets logged
561 -- against the delivery.
562 -- 3. Check for the need by date/promise date change
563 -- If Trip is associated with the delivery detail then
564 -- - If the earliest delivery date is greater that planned arrival date
565 -- of the trip stop or latest delivery date is less that the
566 -- planned arrival date of the tirp stop then an exception gets logged
567 -- against the trip stop.
568 -- If the delivery detail has not trip, but delivery then
569 -- - If the earliest delivery date is greater that ultimate drop off date
570 -- of the delivery or latest delivery date is less that the
571 -- ultimate drop off date of the delivery then an exception gets logged
572 -- against the delivery.
573 -- The delivery id is collected to l_tp_del_id array which will be later
574 -- used to re-calculate the TP dates.
575 -- 4. Check for Freight (only if delivery is not getting unassigned and the
576 -- line is a standard PO line and not a blanket release)
577 -- - If all the details in a delivery has the same freight, then the freight on
578 -- the delivery will be updated to that value.If the details have different
579 -- freight terms, then the delviery freight is updated to NULL.
580 --
581 --{
582 --
583 -- Parameters :
584 -- IN OUT:
585 -- p_line_rec IN OE_WSH_BULK_GRP.line_rec_type DEFAULT NULL
586 -- This record structure contains information about the lines to be updated.
587 -- The information may include header id,line id ,line location id and other po
588 -- attributes..
589 -- p_dd_id_unassigned IN OUT NOCOPY wsh_util_core.id_tab_type
590 -- This contains the list of delviery details that needs to be unassigned from
591 -- the respective delivery.
592 -- p_wt_vol_dd_id IN OUT NOCOPY wsh_util_core.id_tab_type,
593 -- This contains the list of delviery details for which weight and volume needs to
594 -- be re-calculated.
595 -- p_action_prms IN OUT NOCOPY
596 -- WSH_BULK_TYPES_GRP.action_parameters_rectype
597 -- The record which specifies the caller, actio to be performed(REAPPROVE,CANCEL_ASN,RECEIPT,ASN)
598 --IN :
599 -- p_dd_id IN NUMBER
600 -- This parameter will have value only if the p_dd_list in the update_attributes is populated
601 -- (when called from Cancel_ASN or Revert Matching Transaction).
602 -- When p_dd_id is passed, only that particular delivery_detail_id is updated.If the p_dd_id
603 -- is null, then all the open delivery details corresponding to the input po_line_location_id
604 -- will be updated.
605 -- OUT:
606 -- x_return_status OUT NOCOPY VARCHAR2
607 -- Version : 1.0
608 -- Previous version 1.0
609 -- Initial version 1.0
610 -- End of comments
611
612 Procedure Update_dd_attributes(
613 p_line_rec IN OUT NOCOPY OE_WSH_BULK_GRP.line_rec_type,
614 p_action_prms IN WSH_BULK_TYPES_GRP.action_parameters_rectype,
615 p_dd_id IN NUMBER,
616 p_line_rec_index IN NUMBER,
617 p_dd_id_unassigned IN OUT NOCOPY wsh_util_core.id_tab_type,
618 p_wt_vol_dd_id IN OUT NOCOPY wsh_util_core.id_tab_type,
619 x_return_status OUT NOCOPY VARCHAR2) IS
620
621 -- Cursor to fetch the delivery grouping attributes and other critical attributes
622 -- like shipping control, freight term,fob,ultimate_drop_off_date of delivery.
623
624 Cursor C_dd_attr(p_line_location_id NUMBER, p_delivery_detail_id NUMBER,
625 p_header_id NUMBER,p_line_id NUMBER)
626 IS
627 SELECT
628 WDD.delivery_detail_id,
629 WDD.ship_to_location_id,
630 WDD.organization_id,
631 WDD.ship_from_location_id,
632 WDD.customer_id,
633 WDD.date_requested,
634 WDD.date_scheduled,
635 WDD.fob_code dd_fob,
636 WDD.freight_terms_code dd_fgt,
637 WND.routing_response_id,
638 WDD.shipping_control,
639 WND.delivery_id,
640 WND.freight_terms_code del_fgt,
641 WND.ultimate_dropoff_date del_date,
642 WND.ultimate_dropoff_location_id del_location,
643 /*J-IB-ANJ*/
644 WDD.earliest_dropoff_date edd,
645 WDD.latest_dropoff_date ldd,
646 WDD.last_update_Date
647 FROM
648 WSH_DELIVERY_DETAILS WDD,
649 wsh_delivery_assignments_v WDA,
650 WSH_NEW_DELIVERIES WND
651 WHERE
652 WDD.Delivery_Detail_id = WDA.Delivery_Detail_id AND
653 WDA.Delivery_id = WND.Delivery_id(+) AND
654 WDD.released_status = 'X' AND
655 WDD.PO_SHIPMENT_LINE_ID = p_line_location_id AND
656 WDD.source_code = 'PO' AND
657 WDD.source_header_id = nvl(p_header_id,WDD.source_header_id) AND
658 WDD.source_line_id = p_line_id AND
659 WDD.delivery_detail_id = NVL(p_delivery_detail_id, WDD.DELIVERY_DETAIL_ID);
660
661 -- Cursor to fetch all the attributes corresponding to the line location id, line id and header id
662 -- from wsh_delivery_details which is later used to check if any on the non-quantity attributes
663 -- have changed before doing the update on wdd.
664
665 Cursor c_dd_info(p_line_location_id NUMBER, p_delivery_detail_id NUMBER,
666 p_header_id NUMBER,p_line_id NUMBER)
667 IS
668 -- Changed for Bug# 3330869
669 -- select * from wsh_delivery_details
670 SELECT
671 inventory_item_id,
672 delivery_detail_id,
673 source_header_id,
674 source_blanket_reference_id,
675 source_line_id,
676 ship_from_site_id,
677 customer_item_id,
678 source_line_type_code,
679 sold_to_contact_id,
680 vendor_id,
681 item_description,
682 hazard_class_id,
683 country_of_origin,
684 ship_to_location_id,
685 ship_to_contact_id,
686 deliver_to_location_id,
687 deliver_to_contact_id,
688 intmed_ship_to_location_id,
689 intmed_ship_to_contact_id,
690 hold_code,
691 ship_tolerance_above,
692 ship_tolerance_below,
693 revision,
694 date_requested,
695 date_scheduled,
696 ship_method_code,
697 carrier_id,
698 freight_terms_code,
699 fob_code,
700 supplier_item_number,
701 customer_prod_seq,
702 customer_dock_code,
703 cust_model_serial_number,
704 customer_job,
705 customer_production_line,
706 organization_id,
707 ship_model_complete_flag,
708 top_model_line_id,
709 source_header_number,
710 source_header_type_name,
711 cust_po_number,
712 ato_line_id,
713 shipping_instructions,
714 packing_instructions,
715 org_id,
716 source_line_number,
717 unit_price,
718 currency_code,
719 preferred_grade,
720 po_shipment_line_number,
721 source_blanket_reference_num,
722 po_revision_number,
723 release_revision_number,
724 earliest_dropoff_date,
725 latest_dropoff_date
726 FROM wsh_delivery_details
727 WHERE source_code = 'PO'
728 AND po_shipment_line_id = p_line_location_id AND
729 source_header_id = p_header_id AND
730 source_line_id = p_line_id AND
731 delivery_detail_id = NVL(p_delivery_detail_id, DELIVERY_DETAIL_ID);
732
733 -- Cursor to get the planned arrival date of the trip stop
734
735 Cursor c_check_ts_date(p_dd_id NUMBER)
736 IS
737 SELECT
738 WTS.STOP_ID,
739 WTS.planned_arrival_date Stop_date ,
740 WTS.STOP_LOCATION_ID
741 FROM wsh_delivery_assignments_v WDA,
742 WSH_NEW_DELIVERIES WND,
743 WSH_DELIVERY_LEGS WDG,
744 WSH_TRIP_STOPS WTS
745 WHERE wda.delivery_detail_id = p_dd_id AND
746 WDA.DELIVERY_ID = WND.DELIVERY_ID AND
747 WDG.DELIVERY_ID = WND.DELIVERY_ID AND
748 WTS.STOP_ID = WDG.DROP_OFF_STOP_ID AND
749 WTS.STOP_LOCATION_ID = WND.ULTIMATE_DROPOFF_LOCATION_ID;
750
751 l_stop_loaction_id NUMBER;
752 l_del_unassigned VARCHAR2(1);
753 l_return_status VARCHAR2(1);
754 l_validate_flag VARCHAR2(1);
755 l_stop_id NUMBER;
756 l_stop_date DATE;
757 l_Earliest_delivery_Date DATE;
758 l_latest_delivery_Date DATE;
759
760 l_init_msg_list VARCHAR2(30) := NULL;
761 l_msg_count NUMBER;
762 l_msg_data VARCHAR2(3000);
763 l_commit VARCHAR2(1);
764 l_validation_level NUMBER;
765 l_delivery_id NUMBER;
766 l_tp_del_id wsh_util_core.id_tab_type;
767
768 l_delivery_name VARCHAR2(150);
769 l_num_warnings NUMBER := 0;
770 l_num_errors NUMBER := 0;
771 l_fgt_terms_code VARCHAR2(30);
772
773 l_del_fgt_terms_tbl wsh_util_core.id_tab_type;
774 l_del_fgt_terms_tbl_cache WSH_UTIL_CORE.key_value_tab_type;
775 l_del_fgt_terms_tbl_ext_cache WSH_UTIL_CORE.key_value_tab_type;
776 l_ind NUMBER;
777 l_req_res_id NUMBER;
778 l_msg varchar2(2000);
779 l_check_routing_res varchar2(1);
780 l_rr_status boolean;
781 l_old_value varchar2(100);
782 l_new_value varchar2(100);
783 --J-IB-ANJ
784 /*l_del_edd date; -- Earliest Drop Off Date Bug#: 3145863
785 l_del_ldd date; -- Latest Drop Off Date Bug#: 3145863*/
786
787 l_shippingControlTurnedOn BOOLEAN := FALSE;
788 l_action_prms WSH_BULK_TYPES_GRP.action_parameters_rectype;
789
790 e_wdd_locked EXCEPTION ;
791 PRAGMA EXCEPTION_INIT(e_wdd_locked,-54);
792 --
793 l_debug_on BOOLEAN;
794 --
795 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_DD_ATTRIBUTES';
796 --
797 BEGIN
798
799 --
800 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
801 --
802 IF l_debug_on IS NULL
803 THEN
804 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
805 END IF;
806 --
807 --
808 -- Debug Statements
809 --
810 IF l_debug_on THEN
811 WSH_DEBUG_SV.push(l_module_name);
812 --
813 WSH_DEBUG_SV.log(l_module_name,'P_DD_ID',P_DD_ID);
814 WSH_DEBUG_SV.log(l_module_name,'P_LINE_REC_INDEX',P_LINE_REC_INDEX);
815 END IF;
816 --
817 SAVEPOINT Update_dd_Attributes_PVT;
818
819 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
820
821 IF (p_action_prms.action_code ='REAPPROVE_PO'
822 OR p_action_prms.action_code ='CANCEL_ASN') THEN
823 --{
824
825 FOR v_dd_attr IN c_dd_attr(p_line_rec.po_shipment_line_id(p_line_rec_index),
826 p_dd_id, p_line_rec.header_id(p_line_rec_index),
827 p_line_rec.line_id(p_line_rec_index))
828 LOOP
829
830 /*Variable for avoiding unassignment from delivery for shipping control
831 check further down. Will be set to 'Y' when first unassignment happens,
832 and later can be just checked against.*/
833 IF l_debug_on THEN
834 WSH_DEBUG_SV.log(l_module_name,'v_dd_attr wdd id is ',v_dd_attr.delivery_detail_id);
835 WSH_DEBUG_SV.log(l_module_name,'v_dd_attr last_update_date is ',v_dd_attr.last_update_date);
836 END IF;
837
838
839 l_del_unassigned := 'N';
840 l_check_routing_res:=NULL;
841
842 --Check for Delivery Grouping Attributes - Starts
843 IF ((p_line_rec.ship_to_location_id(p_line_rec_index) IS NOT NULL) AND
844 p_line_rec.ship_to_location_id(p_line_rec_index) <> v_dd_attr.ship_to_location_id) OR
845 ((p_line_rec.organization_id(p_line_rec_index) IS NOT NULL) AND
846 p_line_rec.organization_id(p_line_rec_index) <> v_dd_attr.organization_id)
847 OR
848 ((p_line_rec.sold_to_org_id(p_line_rec_index) IS NOT NULL) AND
849 p_line_rec.sold_to_org_id(p_line_rec_index) <> v_dd_attr.customer_id)
850 THEN
851 --{
852 IF v_dd_attr.delivery_id IS NOT NULL THEN
853
854 -- When ever ship to location is changed on the PO, if the delivery details is
855 -- assigned to delviery, it gets unassigned from the delivery.This is done by
856 -- collecting the list of delviery details that needs to be unassigned to
857 -- p_dd_id_unassigned array.
858 -- Also an exception gets logged against the delivery.
859
860 --{
861 p_dd_id_unassigned(p_dd_id_unassigned.COUNT +1)
862 := v_dd_attr.delivery_detail_id;
863 IF l_debug_on THEN
864 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit Log_Exception',WSH_DEBUG_SV.C_PROC_LEVEL);
865 END IF;
866
867 -- Bug 3395410 : Setting the message that needs to be displayed while logging exception.
868
869 FND_MESSAGE.SET_NAME('WSH','WSH_IB_DELIVERY_CHANGE');
870 FND_MESSAGE.SET_TOKEN('DELIVERY',v_dd_attr.delivery_id);
871 l_msg := FND_MESSAGE.GET;
872
873 Log_Exception(
874 p_entity_id => V_dd_attr.delivery_id,
875 p_logging_entity_name => 'DELIVERY',
876 p_exception_name => 'WSH_IB_DELIVERY_CHANGE',
877 p_location_id => v_dd_attr.del_location,
878 p_message => l_msg,
879 x_return_status => l_return_status);
880
881 --
882 -- Debug Statements
883 --
884 IF l_debug_on THEN
885 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
886 END IF;
887 --
888 wsh_util_core.api_post_call(
889 p_return_status => l_return_status,
890 x_num_warnings => l_num_warnings,
891 x_num_errors => l_num_errors);
892
893
894 -- HACMS {
895 IF (p_line_rec.ship_to_location_id(p_line_rec_index) IS NOT NULL AND
896 p_line_rec.ship_to_location_id(p_line_rec_index) <> v_dd_attr.ship_to_location_id) THEN
897
898 IF (l_check_routing_res IS NULL) THEN
899 l_rr_status:=WSH_INBOUND_UTIL_PKG.Is_Routing_Response_Send(V_dd_attr.delivery_detail_id,l_req_res_id);
900 l_check_routing_res:='Y';
901 END IF;
902
903 IF (l_rr_status) THEN
904 -- If routing response in send and the ship to location is undergoing a change, then
905 -- an exception is logged against the delivery detail.
906
907 FND_MESSAGE.SET_NAME('WSH','WSH_IB_ST_LOC_CHANGE');
908 FND_MESSAGE.SET_TOKEN('DETAIL', V_dd_attr.delivery_detail_id);
909 FND_MESSAGE.SET_TOKEN('OLD_VALUE', v_dd_attr.ship_to_location_id);
910 FND_MESSAGE.SET_TOKEN('NEW_VALUE', p_line_rec.ship_to_location_id(p_line_rec_index));
911 l_msg := FND_MESSAGE.GET;
912
913 IF l_debug_on THEN
914 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit Log_Exception',WSH_DEBUG_SV.C_PROC_LEVEL);
915 END IF;
916
917 Log_Exception(
918 p_entity_id => V_dd_attr.delivery_detail_id,
919 p_logging_entity_name => 'DETAIL',
920 p_exception_name => 'WSH_IB_DETAIL_CHANGE',
921 p_location_id => v_dd_attr.ship_to_location_id,
922 p_message => l_msg,
923 x_return_status => l_return_status);
924
925 IF l_debug_on THEN
926 WSH_DEBUG_SV.log(l_module_name,'After Log_Exception l_return_status',l_return_status);
927 END IF;
928
929 wsh_util_core.api_post_call(
930 p_return_status => l_return_status,
931 x_num_warnings => l_num_warnings,
932 x_num_errors => l_num_errors);
933
934 END IF;
935 END IF;
936 -- HACMS }
937
938 l_del_unassigned := 'Y';
939
940 END IF;
941 --}
942 END IF;
943 --}
944
945 --Check for Delivery Grouping Attributes - Ends
946
947 --Check for Shipping Control - Starts
948
949 IF l_debug_on THEN
950 WSH_DEBUG_SV.log(l_module_name,'p_line_rec shipping_control is ',p_line_rec.shipping_control(p_line_rec_index));
951 WSH_DEBUG_SV.log(l_module_name,'v_dd_attr shipping_control is ',v_dd_attr.shipping_control);
952 WSH_DEBUG_SV.log(l_module_name,'v_dd_attr routing response id is ',v_dd_attr.routing_response_id);
953 END IF;
954
955
956 IF p_line_rec.shipping_control(p_line_rec_index) IS NOT NULL
957 AND v_dd_attr.shipping_control IS NULL
958 THEN
959 --{
960 l_shippingControlTurnedOn := TRUE;
961 --}
962 END IF;
963
964
965 IF ((p_line_rec.shipping_control(p_line_rec_index) IS NOT NULL) AND
966 p_line_rec.shipping_control(p_line_rec_index) <> v_dd_attr.shipping_control) THEN
967 --{
968 IF v_dd_attr.shipping_control ='BUYER' AND p_line_rec.shipping_control(p_line_rec_index) = 'SUPPLIER' THEN
969
970 -- If shipping control is changed from 'Buyer' to 'Supplier' on the PO, then this change is
971 -- updated in Shipping only if routing response is not send.
972 -- If the associated delivery detail is assigned to delviery, then it gets unassigned from the
973 --delivery. Also an exception gets logged against the delivery.
974
975 --{
976 IF v_dd_attr.routing_response_id IS NULL THEN
977 IF v_dd_attr.delivery_id is NOT NULL and l_del_unassigned = 'N' THEN
978 p_dd_id_unassigned(p_dd_id_unassigned.COUNT +1)
979 := v_dd_attr.delivery_detail_id;
980 l_del_unassigned := 'Y';
981
982 IF l_debug_on THEN
983 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit Log_Exception',WSH_DEBUG_SV.C_PROC_LEVEL);
984 END IF;
985
986 -- Bug 3395410 : Setting the message that needs to be displayed while logging exception.
987
988 FND_MESSAGE.SET_NAME('WSH','WSH_IB_DELIVERY_CHANGE');
989 FND_MESSAGE.SET_TOKEN('DELIVERY',v_dd_attr.delivery_id);
990 l_msg := FND_MESSAGE.GET;
991
992 Log_Exception(
993 p_entity_id => V_dd_attr.delivery_id,
994 p_logging_entity_name => 'DELIVERY',
995 p_exception_name => 'WSH_IB_DELIVERY_CHANGE',
996 p_location_id => v_dd_attr.del_location,
997 p_message => l_msg,
998 x_return_status => l_return_status);
999 --
1000 -- Debug Statements
1001 --
1002 IF l_debug_on THEN
1003 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
1004 END IF;
1005 --
1006 wsh_util_core.api_post_call(
1007 p_return_status => l_return_status,
1008 x_num_warnings => l_num_warnings,
1009 x_num_errors => l_num_errors);
1010
1011 END IF;
1012 update wsh_delivery_details
1013 set shipping_control = p_line_rec.shipping_control(p_line_rec_index),
1014 last_update_date = SYSDATE,
1015 last_updated_by = FND_GLOBAL.USER_ID,
1016 last_update_login = FND_GLOBAL.LOGIN_ID
1017 WHERE po_shipment_line_id = p_line_rec.po_shipment_line_id(p_line_rec_index)
1018 AND source_header_id = p_line_rec.header_id(p_line_rec_index) AND
1019 source_line_id = p_line_rec.line_id(p_line_rec_index) AND
1020 source_code = 'PO' AND
1021 ((p_line_rec.source_blanket_reference_id(p_line_rec_index) IS NULL AND
1022 source_blanket_reference_id IS NULL) OR
1023 (p_line_rec.source_blanket_reference_id(p_line_rec_index) IS NOT NULL
1024 AND source_blanket_reference_id = p_line_rec.source_blanket_reference_id(p_line_rec_index))) AND
1025 delivery_detail_id = v_dd_attr.delivery_detail_id AND
1026 released_status = 'X' AND
1027 container_flag = 'N' AND
1028 last_update_date = v_dd_attr.last_update_date;
1029
1030 IF SQL%ROWCOUNT <> 1 THEN
1031 IF l_debug_on THEN
1032 WSH_DEBUG_SV.log(l_module_name,'SQL%ROWCOUNT',SQL%ROWCOUNT);
1033 END IF;
1034 FND_MESSAGE.SET_NAME('WSH','WSH_DELIVERY_LINES_CHANGED');
1035 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
1036 RAISE FND_API.G_EXC_ERROR;
1037 END IF;
1038
1039 END IF;
1040 ELSE
1041 -- If shipping control is changed from 'Supplier' to 'Buyer' on the PO, then
1042 -- it is directly updated to wsh_delivery_details without checking for any contions
1043 -- other than line status which should be 'X'(OPEN).
1044
1045 update wsh_delivery_details
1046 set shipping_control = p_line_rec.shipping_control(p_line_rec_index),
1047 last_update_date = SYSDATE,
1048 last_updated_by = FND_GLOBAL.USER_ID,
1049 last_update_login = FND_GLOBAL.LOGIN_ID
1050 WHERE po_shipment_line_id = p_line_rec.po_shipment_line_id(p_line_rec_index)
1051 AND source_header_id = p_line_rec.header_id(p_line_rec_index) AND
1052 source_line_id = p_line_rec.line_id(p_line_rec_index) AND
1053 source_code = 'PO' AND
1054 ((p_line_rec.source_blanket_reference_id(p_line_rec_index) IS NULL AND
1055 source_blanket_reference_id IS NULL) OR
1056 (p_line_rec.source_blanket_reference_id(p_line_rec_index) IS NOT NULL
1057 AND source_blanket_reference_id = p_line_rec.source_blanket_reference_id(p_line_rec_index))) AND
1058 delivery_detail_id = v_dd_attr.delivery_detail_id AND
1059 released_status = 'X' AND
1060 container_flag = 'N' AND
1061 last_update_date = v_dd_attr.last_update_date;
1062
1063 IF SQL%ROWCOUNT <> 1 THEN
1064 IF l_debug_on THEN
1065 WSH_DEBUG_SV.log(l_module_name,'SQL%ROWCOUNT',SQL%ROWCOUNT);
1066 END IF;
1067 FND_MESSAGE.SET_NAME('WSH','WSH_DELIVERY_LINES_CHANGED');
1068 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
1069 RAISE FND_API.G_EXC_ERROR;
1070 END IF;
1071
1072 --}
1073 END IF;
1074 --}
1075 END IF;
1076
1077 --Check for Shipping Control - Ends
1078
1079 --Check for Need By Date Promise Date - Starts
1080 -- J-IB-ANJ
1081 /*IF (((p_line_rec.request_date(p_line_rec_index) IS NOT NULL) AND
1082 p_line_rec.request_date(p_line_rec_index) <> v_dd_attr.date_requested)) OR
1083 (((p_line_rec.schedule_ship_date(p_line_rec_index) IS NOT NULL) AND
1084 p_line_rec.schedule_ship_date(p_line_rec_index) <> v_dd_attr.date_scheduled))
1085 THEN
1086 --{*/
1087 l_Earliest_delivery_Date := (NVL(p_line_rec.schedule_ship_date(p_line_rec_index),
1088 p_line_rec.request_date(p_line_rec_index))-
1089 NVL(p_line_rec.Days_early_receipt_allowed(p_line_rec_index),0));
1090
1091 l_latest_delivery_date := (NVL(p_line_rec.schedule_ship_date(p_line_rec_index),
1092 p_line_rec.request_date(p_line_rec_index)) +
1093 NVL(p_line_rec.Days_late_receipt_allowed(p_line_rec_index),0));
1094
1095 --J-IB-ANJ
1096 IF l_debug_on THEN
1097 WSH_DEBUG_SV.log(l_module_name,'Early Receipt allowed',p_line_rec.Days_early_receipt_allowed(p_line_rec_index));
1098 WSH_DEBUG_SV.log(l_module_name,'Late Receipt Allowed',p_line_rec.Days_late_receipt_allowed(p_line_rec_index));
1099 WSH_DEBUG_SV.log(l_module_name,'l_Earliest_delivery_Date',l_Earliest_delivery_Date);
1100 WSH_DEBUG_SV.log(l_module_name,'l_latest_delivery_date',l_latest_delivery_date);
1101 END IF;
1102 -- J-IB-ANJ
1103
1104 IF v_dd_attr.edd <> l_Earliest_delivery_Date OR
1105 v_dd_attr.ldd <> l_latest_delivery_date THEN
1106 --{
1107 OPEN c_check_ts_date(v_dd_attr.delivery_detail_id);
1108 FETCH c_check_ts_date INTO l_stop_id, l_stop_date,l_stop_loaction_id;
1109
1110 IF c_check_ts_date%FOUND THEN
1111 --{
1112
1113 IF l_Earliest_delivery_Date > l_stop_date
1114 OR l_latest_delivery_date < l_stop_date THEN
1115 -- if the earliest delivery date is greater that planned arrival date of the trip stop
1116 -- or latest delivery date is less that the planned arrival date of the tirp stop then
1117 -- an exception gets logged against the trip stop.
1118 --{
1119 IF l_debug_on THEN
1120 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit Log_Exception',WSH_DEBUG_SV.C_PROC_LEVEL);
1121 END IF;
1122
1123 -- Bug 3395410 : Setting the message that needs to be displayed while logging exception.
1124
1125 FND_MESSAGE.SET_NAME('WSH','WSH_IB_TS_DATE_EXCP');
1126 FND_MESSAGE.SET_TOKEN('STOP',l_stop_id);
1127 l_msg := FND_MESSAGE.GET;
1128
1129 Log_Exception(
1130 p_entity_id => l_stop_id,
1131 p_logging_entity_name => 'STOP',
1132 p_exception_name => 'WSH_IB_TS_DATE_EXCP',
1133 p_location_id => l_stop_loaction_id,
1134 p_message => l_msg,
1135 x_return_status => l_return_status);
1136
1137 --
1138 -- Debug Statements
1139 --
1140 IF l_debug_on THEN
1141 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
1142 END IF;
1143 --
1144 wsh_util_core.api_post_call(
1145 p_return_status => l_return_status,
1146 x_num_warnings => l_num_warnings,
1147 x_num_errors => l_num_errors);
1148
1149 END IF;
1150 --}
1151 ELSE
1152 IF l_Earliest_delivery_Date > v_dd_attr.del_date
1153 OR l_latest_delivery_date < v_dd_attr.del_date THEN
1154 -- if the earliest delivery date is greater that ultimate drop off date of the delivery
1155 -- or latest delivery date is less that the ultimate drop off date of the delivery then
1156 -- an exception gets logged against the delivery.
1157 -- The delivery id is collected to l_tp_del_id array which will be later used to re-calculate
1158 -- the TP dates.
1159 --{
1160 IF l_debug_on THEN
1161 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit Log_Exception',WSH_DEBUG_SV.C_PROC_LEVEL);
1162 END IF;
1163
1164 -- Bug 3395410 : Setting the message that needs to be displayed while logging exception.
1165
1166 FND_MESSAGE.SET_NAME('WSH','WSH_IB_DEL_DATE_EXCP');
1167 FND_MESSAGE.SET_TOKEN('DELIVERY',v_dd_attr.delivery_id);
1168 l_msg := FND_MESSAGE.GET;
1169
1170 Log_Exception(
1171 p_entity_id => V_dd_attr.delivery_id,
1172 p_logging_entity_name => 'DELIVERY',
1173 p_exception_name => 'WSH_IB_DEL_DATE_EXCP',
1174 p_location_id => V_dd_attr.del_location,
1175 p_message => l_msg,
1176 x_return_status => l_return_status);
1177
1178 --
1179 -- Debug Statements
1180 --
1181 IF l_debug_on THEN
1182 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
1183 END IF;
1184 --
1185 wsh_util_core.api_post_call(
1186 p_return_status => l_return_status,
1187 x_num_warnings => l_num_warnings,
1188 x_num_errors => l_num_errors);
1189
1190 END IF;
1191 --}
1192 CLOSE c_check_ts_date;
1193
1194
1195 -- HACMS {
1196 IF (p_line_rec.request_date(p_line_rec_index) IS NOT NULL AND
1197 p_line_rec.request_date(p_line_rec_index) <> v_dd_attr.date_requested) THEN
1198
1199 IF (l_check_routing_res IS NULL) THEN
1200 l_rr_status:=WSH_INBOUND_UTIL_PKG.Is_Routing_Response_Send(V_dd_attr.delivery_detail_id,l_req_res_id);
1201 l_check_routing_res:='Y';
1202 END IF;
1203
1204 IF (l_rr_status) THEN
1205 -- If request date is changed on the PO and routing response is already send,
1206 -- then exception gets logged agains delivery and delivery detail.
1207
1208 l_old_value := to_char(v_dd_attr.date_requested);
1209 l_new_value := to_char(p_line_rec.request_date(p_line_rec_index));
1210
1211 FND_MESSAGE.SET_NAME('WSH','WSH_IB_NEED_DT_CHANGE');
1212 FND_MESSAGE.SET_TOKEN('DETAIL', V_dd_attr.delivery_detail_id);
1213 FND_MESSAGE.SET_TOKEN('OLD_VALUE', to_char(v_dd_attr.date_requested));
1214 FND_MESSAGE.SET_TOKEN('NEW_VALUE', to_char(p_line_rec.request_date(p_line_rec_index)));
1215 l_msg := FND_MESSAGE.GET;
1216
1217 IF l_debug_on THEN
1218 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit Log_Exception',WSH_DEBUG_SV.C_PROC_LEVEL);
1219 END IF;
1220
1221 Log_Exception(
1222 p_entity_id => V_dd_attr.delivery_detail_id,
1223 p_logging_entity_name => 'DETAIL',
1224 p_exception_name => 'WSH_IB_DETAIL_CHANGE',
1225 p_location_id => v_dd_attr.ship_to_location_id,
1226 p_message => l_msg,
1227 x_return_status => l_return_status);
1228
1229 IF l_debug_on THEN
1230 WSH_DEBUG_SV.log(l_module_name,'Log_Exception l_return_status',l_return_status);
1231 END IF;
1232
1233 wsh_util_core.api_post_call(
1234 p_return_status => l_return_status,
1235 x_num_warnings => l_num_warnings,
1236 x_num_errors => l_num_errors);
1237
1238 FND_MESSAGE.SET_NAME('WSH','WSH_IB_DEL_ATT_CHANGE');
1239 FND_MESSAGE.SET_TOKEN('DELIVERY', V_dd_attr.delivery_id);
1240 l_msg := FND_MESSAGE.GET;
1241
1242 Log_Exception(
1243 p_entity_id => V_dd_attr.delivery_id,
1244 p_logging_entity_name => 'DELIVERY',
1245 p_exception_name => 'WSH_IB_DEL_ATT_CHANGE',
1246 p_location_id => v_dd_attr.del_location,
1247 p_message => l_msg,
1248 x_return_status => l_return_status);
1249
1250 IF l_debug_on THEN
1251 WSH_DEBUG_SV.log(l_module_name,'Log_Exception l_return_status',l_return_status);
1252 END IF;
1253
1254 wsh_util_core.api_post_call(
1255 p_return_status => l_return_status,
1256 x_num_warnings => l_num_warnings,
1257 x_num_errors => l_num_errors);
1258
1259
1260 END IF;
1261 END IF;
1262 -- HACMS }
1263
1264 l_tp_del_id(l_tp_del_id.COUNT +1) := v_dd_attr.delivery_id;
1265 END IF;
1266 --}
1267 END IF;
1268 --}
1269
1270 --Check for Need By Date promised Date - Ends
1271
1272 --Check for Freight/FOB - Starts (only if delivery is not getting
1273 --unassigned and the line is a standard PO line and not a blanket release)
1274 -- If all the details in a delivery has the same freight, then the freight on
1275 -- the delivery will be updated to that value.If the details have different
1276 -- freight terms, then the delviery freight is updated to NULL.
1277
1278 IF l_del_unassigned = 'N' and v_dd_attr.delivery_id is NOT NULL and
1279 (p_line_rec.source_blanket_reference_id(p_line_rec_index) IS NULL)
1280 THEN
1281 --{
1282 IF nvl(v_dd_attr.del_fgt,'!!!') <> nvl(p_line_rec.freight_terms_code(p_line_rec_index),'!!!') THEN
1283 wsh_util_core.get_cached_value(
1284 p_cache_tbl => l_del_fgt_terms_tbl_cache,
1285 p_cache_ext_tbl => l_del_fgt_terms_tbl_ext_cache,
1286 p_value => v_dd_attr.delivery_id,
1287 p_key => v_dd_attr.delivery_id,
1288 p_action => 'PUT',
1289 x_return_status => l_return_status);
1290
1291 wsh_util_core.api_post_call(
1292 p_return_status => l_return_status,
1293 x_num_warnings => l_num_warnings,
1294 x_num_errors => l_num_errors);
1295 END IF;
1296 ELSIF p_line_rec.source_blanket_reference_id(p_line_rec_index) IS NOT NULL THEN
1297 p_line_rec.freight_terms_code(p_line_rec_index) := v_dd_attr.del_fgt;
1298 p_line_rec.fob_point_code(p_line_rec_index) := v_dd_attr.dd_fob;
1299 --}
1300 END IF;
1301 --Check for Freight Terms - Ends
1302
1303 END LOOP;--For c_dd_attr
1304
1305 FOR l_dd_info in c_dd_info(p_line_rec.po_shipment_line_id(p_line_rec_index),
1306 p_dd_id, p_line_rec.header_id(p_line_rec_index),
1307 p_line_rec.line_id(p_line_rec_index)) LOOP
1308 --J-IB-ANJ
1309 -- Calculating the Earliest and Latest Dropoff dates 3145863
1310 /*l_del_edd := (NVL(p_line_rec.schedule_ship_date(p_line_rec_index),p_line_rec.request_date(p_line_rec_index)) -
1311 NVL(p_line_rec.Days_early_receipt_allowed(p_line_rec_index),0));
1312 l_del_ldd := (NVL(p_line_rec.schedule_ship_date(p_line_rec_index),p_line_rec.request_date(p_line_rec_index)) +
1313 NVL(p_line_rec.Days_late_receipt_allowed(p_line_rec_index),0));
1314
1315 IF l_debug_on THEN
1316 WSH_DEBUG_SV.log(l_module_name,'Early Receipt allowed',p_line_rec.Days_early_receipt_allowed(p_line_rec_index));
1317 WSH_DEBUG_SV.log(l_module_name,'Late Receipt Allowed',p_line_rec.Days_late_receipt_allowed(p_line_rec_index));
1318 WSH_DEBUG_SV.log(l_module_name,'l_del_edd',l_del_edd);
1319 WSH_DEBUG_SV.log(l_module_name,'l_del_ldd',l_del_ldd);
1320 END IF;*/
1321
1322 IF ((l_dd_info.inventory_item_id is NULL and p_line_rec.inventory_item_id(p_line_rec_index) is NOT NULL )
1323 OR (l_dd_info.inventory_item_id <> p_line_rec.inventory_item_id(p_line_rec_index)) ) THEN
1324 -- Collecting the list of delivery_detail_ids for which inv item id has changed.
1325 -- This list is later used in Reapprove PO to re-calcualte the weight and volume.
1326 p_wt_vol_dd_id (p_wt_vol_dd_id.COUNT +1) := l_dd_info.delivery_detail_id;
1327 END IF;
1328
1329 IF nvl(l_dd_info.source_header_id,-99) <>
1330 nvl(p_line_rec.header_id(p_line_rec_index),-99)OR
1331 nvl(l_dd_info.source_blanket_reference_id,-99) <>
1332 nvl(p_line_rec.source_blanket_reference_id(p_line_rec_index),-99) OR
1333 nvl(l_dd_info.source_line_id,-99) <>
1334 nvl(p_line_rec.line_id(p_line_rec_index),-99) OR
1335 nvl(l_dd_info.ship_from_site_id,-99) <>
1336 nvl(p_line_rec.ship_from_site_id(p_line_rec_index),-99) OR
1337 nvl(l_dd_info.customer_item_id,-99) <>
1338 nvl(p_line_rec.customer_item_id(p_line_rec_index),-99) OR
1339 nvl(l_dd_info.source_line_type_code,'!!!') <>
1340 nvl(p_line_rec.source_line_type_code(p_line_rec_index),'!!!') OR
1341 nvl(l_dd_info.sold_to_contact_id,-99) <>
1342 nvl(p_line_rec.sold_to_contact_id(p_line_rec_index),-99)OR
1343 nvl(l_dd_info.vendor_id,-99) <>
1344 nvl(p_line_rec.vendor_id(p_line_rec_index),-99) OR
1345 nvl(l_dd_info.inventory_item_id,-99) <>
1346 nvl(p_line_rec.inventory_item_id(p_line_rec_index),-99) OR
1347 nvl(l_dd_info.item_description,'!!!') <>
1348 nvl(p_line_rec.item_description(p_line_rec_index),'!!!') OR
1349 nvl(l_dd_info.hazard_class_id,-99) <>
1350 nvl(p_line_rec.hazard_class_id(p_line_rec_index),-99) OR
1351 nvl(l_dd_info.country_of_origin,'!!!') <>
1352 nvl(p_line_rec.country_of_origin(p_line_rec_index),'!!!') OR
1353 nvl(l_dd_info.ship_to_location_id,-99) <>
1354 nvl(p_line_rec.ship_to_location_id(p_line_rec_index),-99) OR
1355 nvl(l_dd_info.ship_to_contact_id,-99) <>
1356 nvl(p_line_rec.ship_to_contact_id(p_line_rec_index),-99) OR
1357 nvl(l_dd_info.deliver_to_location_id,-99) <>
1358 nvl(p_line_rec.deliver_to_location_id(p_line_rec_index),-99) OR
1359 nvl(l_dd_info.deliver_to_contact_id,-99) <>
1360 nvl(p_line_rec.deliver_to_contact_id(p_line_rec_index),-99) OR
1361 nvl(l_dd_info.intmed_ship_to_location_id,-99) <>
1362 nvl(p_line_rec.intmed_ship_to_location_id(p_line_rec_index),-99) OR
1363 nvl(l_dd_info.intmed_ship_to_contact_id,-99) <>
1364 nvl(p_line_rec.intermed_ship_to_contact_id(p_line_rec_index),-99) OR
1365 nvl(l_dd_info.hold_code,'!!!') <>
1366 nvl(p_line_rec.hold_code(p_line_rec_index),'!!!') OR
1367 nvl(l_dd_info.ship_tolerance_above,-99) <>
1368 nvl(p_line_rec.ship_tolerance_above(p_line_rec_index),-99) OR
1369 nvl(l_dd_info.ship_tolerance_below,-99) <>
1370 nvl(p_line_rec.ship_tolerance_below(p_line_rec_index),-99) OR
1371 nvl(l_dd_info.revision,'!!!') <>
1372 nvl(p_line_rec.revision(p_line_rec_index),'!!!') OR
1373 nvl(l_dd_info.date_requested,FND_API.G_MISS_DATE) <>
1374 nvl(nvl(p_line_rec.request_date(p_line_rec_index),p_line_rec.schedule_ship_date(p_line_rec_index)),FND_API.G_MISS_DATE) OR
1375 nvl(l_dd_info.date_scheduled,FND_API.G_MISS_DATE) <>
1376 nvl(p_line_rec.schedule_ship_date(p_line_rec_index),FND_API.G_MISS_DATE) OR
1377 nvl(l_dd_info.ship_method_code,'!!!') <>
1378 nvl(p_line_rec.shipping_method_code(p_line_rec_index),'!!!') OR
1379 nvl(l_dd_info.carrier_id,-99) <>
1380 nvl(p_line_rec.carrier_id(p_line_rec_index),-99) OR
1381 nvl(l_dd_info.freight_terms_code,'!!!') <>
1382 nvl(p_line_rec.freight_terms_code(p_line_rec_index),'!!!') OR
1383 nvl(l_dd_info.fob_code,'!!!') <>
1384 nvl(p_line_rec.fob_point_code(p_line_rec_index),'!!!') OR
1385 nvl(l_dd_info.supplier_item_number,'!!!') <>
1386 nvl(p_line_rec.supplier_item_num(p_line_rec_index),'!!!') OR
1387 nvl(l_dd_info.customer_prod_seq,'!!!') <>
1388 nvl(p_line_rec.cust_production_seq_num(p_line_rec_index),'!!!') OR
1389 nvl(l_dd_info.customer_dock_code,'!!!') <>
1390 nvl(p_line_rec.customer_dock_code(p_line_rec_index),'!!!') OR
1391 nvl(l_dd_info.cust_model_serial_number,'!!!') <>
1392 nvl(p_line_rec.cust_model_serial_number(p_line_rec_index),'!!!') OR
1393 nvl(l_dd_info.customer_job,'!!!') <>
1394 nvl(p_line_rec.customer_job(p_line_rec_index),'!!!') OR
1395 nvl(l_dd_info.customer_production_line,'!!!') <>
1396 nvl(p_line_rec.customer_production_line(p_line_rec_index),'!!!') OR
1397 nvl(l_dd_info.organization_id,-99) <>
1398 nvl(p_line_rec.organization_id(p_line_rec_index),-99) OR
1399 nvl(l_dd_info.ship_model_complete_flag,'!!!') <>
1400 nvl(p_line_rec.ship_model_complete_flag(p_line_rec_index),'!!!') OR
1401 nvl(l_dd_info.top_model_line_id,-99) <>
1402 nvl(p_line_rec.top_model_line_id(p_line_rec_index),-99) OR
1403 nvl(l_dd_info.source_header_number,'!!!') <>
1404 nvl(p_line_rec.source_header_number(p_line_rec_index),'!!!') OR
1405 nvl(l_dd_info.source_header_type_name,'!!!') <>
1406 nvl(p_line_rec.source_header_type_name(p_line_rec_index),'!!!') OR
1407 nvl(l_dd_info.cust_po_number,'!!!') <>
1408 nvl(p_line_rec.cust_po_number(p_line_rec_index),'!!!') OR
1409 nvl(l_dd_info.ato_line_id,-99) <>
1410 nvl(p_line_rec.ato_line_id(p_line_rec_index),-99) OR
1411 --nvl(l_dd_info.tracking_number,'!!!') <>
1412 --nvl(p_line_rec.tracking_number(p_line_rec_index),'!!!') OR
1413 nvl(l_dd_info.shipping_instructions,'!!!') <>
1414 nvl(p_line_rec.shipping_instructions(p_line_rec_index),'!!!') OR
1415 nvl(l_dd_info.packing_instructions,'!!!') <>
1416 nvl(p_line_rec.packing_instructions(p_line_rec_index),'!!!') OR
1417 nvl(l_dd_info.org_id,-99) <>
1418 nvl(p_line_rec.org_id(p_line_rec_index),-99) OR
1419 nvl(l_dd_info.source_line_number,'!!!') <>
1420 nvl(p_line_rec.source_line_number(p_line_rec_index),'!!!') OR
1421 nvl(l_dd_info.unit_price,-99) <>
1422 nvl(p_line_rec.unit_list_price(p_line_rec_index),-99) OR
1423 nvl(l_dd_info.currency_code,'!!!') <>
1424 nvl(p_line_rec.currency_code(p_line_rec_index),'!!!') OR
1425 nvl(l_dd_info.preferred_grade,'!!!') <>
1426 nvl(p_line_rec.preferred_grade(p_line_rec_index),'!!!') OR
1427 nvl(l_dd_info.po_shipment_line_number,-99) <>
1428 nvl(p_line_rec.po_shipment_line_number(p_line_rec_index),-99) OR
1429 nvl(l_dd_info.source_blanket_reference_num,-99) <>
1430 nvl(p_line_rec.source_blanket_reference_num(p_line_rec_index),-99) OR
1431 nvl(p_line_rec.po_revision(p_line_rec_index),-99) <>
1432 nvl(l_dd_info.po_revision_number,-99) OR
1433 nvl(p_line_rec.release_revision(p_line_rec_index),-99) <>
1434 nvl(l_dd_info.release_revision_number,-99) OR
1435 -- Added for Bug#: 3145863
1436 --J-IB-ANJ
1437 /*nvl(l_dd_info.earliest_dropoff_date, sysdate) <> nvl(l_del_edd,sysdate) OR
1438 nvl(l_dd_info.latest_dropoff_date, sysdate) <> nvl(l_del_ldd,sysdate) THEN*/
1439 nvl(l_dd_info.earliest_dropoff_date, sysdate) <> nvl(l_Earliest_delivery_Date,sysdate) OR
1440 nvl(l_dd_info.latest_dropoff_date, sysdate) <> nvl(l_latest_delivery_date,sysdate) THEN
1441
1442 -- Only if any of the non-quantity attribtues are changed in the PO, the delivery details
1443 -- is updated.
1444 -- If only quantity is changed, then this is taken care in Update_quantity API.
1445
1446 UPDATE WSH_DELIVERY_DETAILS
1447 SET
1448 source_header_id = p_line_rec.header_id(p_line_rec_index),
1449 source_blanket_reference_id=p_line_rec.source_blanket_reference_id(p_line_rec_index),
1450 source_line_id=p_line_rec.line_id(p_line_rec_index),
1451 ship_from_site_id = p_line_rec.ship_from_site_id(p_line_rec_index),
1452 customer_item_id = p_line_rec.customer_item_id(p_line_rec_index),
1453 source_line_type_code = p_line_rec.source_line_type_code(p_line_rec_index),
1454 sold_to_contact_id=p_line_rec.sold_to_contact_id(p_line_rec_index),
1455 vendor_id=p_line_rec.vendor_id(p_line_rec_index),
1456 inventory_item_id=p_line_rec.inventory_item_id(p_line_rec_index),
1457 item_description=p_line_rec.item_description(p_line_rec_index),
1458 hazard_class_id=p_line_rec.hazard_class_id(p_line_rec_index),
1459 country_of_origin=p_line_rec.country_of_origin(p_line_rec_index),
1460 ship_to_location_id=p_line_rec.ship_to_location_id(p_line_rec_index),
1461 ship_to_contact_id=p_line_rec.ship_to_contact_id(p_line_rec_index),
1462 ship_to_site_use_id=p_line_rec.ship_to_org_id(p_line_rec_index),
1463 deliver_to_location_id=p_line_rec.deliver_to_location_id(p_line_rec_index),
1464 deliver_to_contact_id=p_line_rec.deliver_to_contact_id(p_line_rec_index),
1465 deliver_to_site_use_id=p_line_rec.deliver_to_org_id(p_line_rec_index),
1466 intmed_ship_to_location_id=p_line_rec.intmed_ship_to_location_id(p_line_rec_index),
1467 intmed_ship_to_contact_id=p_line_rec.intermed_ship_to_contact_id(p_line_rec_index),
1468 hold_code=p_line_rec.hold_code(p_line_rec_index),
1469 ship_tolerance_above=p_line_rec.ship_tolerance_above(p_line_rec_index),
1470 ship_tolerance_below=p_line_rec.ship_tolerance_below(p_line_rec_index),
1471 revision=p_line_rec.revision(p_line_rec_index),
1472 date_requested = p_line_rec.request_date(p_line_rec_index),
1473 date_scheduled= p_line_rec.schedule_ship_date(p_line_rec_index),
1474 /*J-IB-ANJ*/
1475 -- Start - Changed for Bug#: 3145863
1476 /* earliest_dropoff_date = l_del_edd,
1477 latest_dropoff_date = l_del_ldd, */
1478 -- End - Changed for Bug#: 3145863
1479 --J-IB-ANJ
1480 earliest_dropoff_date = l_Earliest_delivery_Date,
1481 latest_dropoff_date = l_latest_delivery_date,
1482 ship_method_code=p_line_rec.shipping_method_code(p_line_rec_index),
1483 carrier_id=p_line_rec.carrier_id(p_line_rec_index),
1484 freight_terms_code=p_line_rec.freight_terms_code(p_line_rec_index),
1485 fob_code=p_line_rec.fob_point_code(p_line_rec_index),
1486 supplier_item_number=p_line_rec.supplier_item_num(p_line_rec_index),
1487 customer_prod_seq=p_line_rec.cust_production_seq_num(p_line_rec_index),
1488 customer_dock_code=p_line_rec.customer_dock_code(p_line_rec_index),
1489 cust_model_serial_number=p_line_rec.cust_model_serial_number(p_line_rec_index),
1490 customer_job=p_line_rec.customer_job(p_line_rec_index),
1491 customer_production_line=p_line_rec.customer_production_line(p_line_rec_index),
1492 /*net_weight=p_line_rec.net_weight(p_line_rec_index),
1493 weight_uom_code=p_line_rec.weight_uom_code(p_line_rec_index),
1494 volume=p_line_rec.volume(p_line_rec_index),
1495 volume_uom_code=p_line_rec.volume_uom_code(p_line_rec_index),*/
1496 organization_id=p_line_rec.organization_id(p_line_rec_index),
1497 ship_model_complete_flag=p_line_rec.ship_model_complete_flag(p_line_rec_index),
1498 top_model_line_id=p_line_rec.top_model_line_id(p_line_rec_index),
1499 source_header_number=p_line_rec.source_header_number(p_line_rec_index),
1500 source_header_type_name=p_line_rec.source_header_type_name(p_line_rec_index),
1501 cust_po_number=p_line_rec.cust_po_number(p_line_rec_index),
1502 ato_line_id=p_line_rec.ato_line_id(p_line_rec_index),
1503 -- tracking_number=p_line_rec.tracking_number(p_line_rec_index),
1504 shipping_instructions=p_line_rec.shipping_instructions(p_line_rec_index),
1505 packing_instructions=p_line_rec.packing_instructions(p_line_rec_index),
1506 org_id=p_line_rec.org_id(p_line_rec_index),
1507 source_line_number=p_line_rec.source_line_number(p_line_rec_index),
1508 unit_price=p_line_rec.unit_list_price(p_line_rec_index),
1509 currency_code=p_line_rec.currency_code(p_line_rec_index),
1510 preferred_grade=p_line_rec.preferred_grade(p_line_rec_index),
1511 po_shipment_line_id=p_line_rec.po_shipment_line_id(p_line_rec_index),
1512 po_revision_number=p_line_rec.po_revision(p_line_rec_index),
1513 release_revision_number=p_line_rec.release_revision(p_line_rec_index),
1514 po_shipment_line_number=p_line_rec.po_shipment_line_number(p_line_rec_index),
1515 source_blanket_reference_num=p_line_rec.source_blanket_reference_num(p_line_rec_index),
1516 last_update_date = SYSDATE,
1517 last_updated_by = FND_GLOBAL.USER_ID,
1518 last_update_login = FND_GLOBAL.LOGIN_ID
1519 WHERE po_shipment_line_id = p_line_rec.po_shipment_line_id(p_line_rec_index) AND
1520 delivery_detail_id = nvl(p_dd_id,delivery_detail_id)
1521 AND source_header_id = p_line_rec.header_id(p_line_rec_index) AND
1522 source_line_id = p_line_rec.line_id(p_line_rec_index) ANd
1523 source_code = 'PO' AND
1524 ((p_line_rec.source_blanket_reference_id(p_line_rec_index) IS NULL AND
1525 source_blanket_reference_id IS NULL) OR
1526 (p_line_rec.source_blanket_reference_id(p_line_rec_index) IS NOT NULL
1527 AND source_blanket_reference_id = p_line_rec.source_blanket_reference_id(p_line_rec_index))) AND
1528 released_status = 'X' AND
1529 container_flag = 'N';
1530
1531 END IF;
1532
1533 END LOOP;
1534
1535 IF l_tp_del_id.COUNT > 0 THEN
1536 -- Call TP API for the list of delivery id's in l_tp_del_id.
1537 IF l_debug_on THEN
1538 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TP_RELEASE.calculate_cont_del_tpdates',WSH_DEBUG_SV.C_PROC_LEVEL);
1539 END IF;
1540
1541 WSH_TP_RELEASE.calculate_cont_del_tpdates(
1542 p_entity => 'DLVY',
1543 p_entity_ids => l_tp_del_id,
1544 x_return_status => l_return_status);
1545
1546 wsh_util_core.api_post_call(
1547 p_return_status =>l_return_status,
1548 x_num_warnings => l_num_warnings,
1549 x_num_errors => l_num_errors);
1550
1551 l_tp_del_id.delete;
1552
1553 END IF;
1554
1555 l_ind := l_del_fgt_terms_tbl_cache.FIRST;
1556
1557 WHILE l_ind IS NOT NULL
1558 LOOP
1559 --copying cached data to l_del_fgt_terms_tbl
1560 l_del_fgt_terms_tbl(l_del_fgt_terms_tbl.count + 1) := l_del_fgt_terms_tbl_cache(l_ind).value;
1561 l_ind := l_del_fgt_terms_tbl_cache.NEXT(l_ind);
1562 END LOOP;
1563 l_ind := l_del_fgt_terms_tbl_ext_cache.FIRST;
1564 WHILE l_ind IS NOT NULL
1565 LOOP
1566 l_del_fgt_terms_tbl(l_del_fgt_terms_tbl.count + 1) := l_del_fgt_terms_tbl_ext_cache(l_ind).value;
1567 l_ind := l_del_fgt_terms_tbl_ext_cache.NEXT(l_ind);
1568 END LOOP;
1569 --
1570
1571
1572
1573 IF l_debug_on THEN
1574 WSH_DEBUG_SV.log(l_module_name,'l_del_fgt_terms_tbl count is ' || l_del_fgt_terms_tbl.COUNT);
1575 END IF;
1576
1577 IF l_del_fgt_terms_tbl.COUNT > 0 THEN
1578 --update the freight terms of delivery
1579 IF l_debug_on THEN
1580 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERY_ACTIONS. update_freight_terms',WSH_DEBUG_SV.C_PROC_LEVEL);
1581 END IF;
1582
1583 FOR i in 1..l_del_fgt_terms_tbl.COUNT LOOP
1584 WSH_NEW_DELIVERY_ACTIONS. update_freight_terms
1585 ( p_delivery_id => l_del_fgt_terms_tbl(i),
1586 p_action_code => 'ASSIGN',
1587 x_freight_terms_Code => l_fgt_terms_code,
1588 x_return_status => l_return_status);
1589
1590 wsh_util_core.api_post_call(
1591 p_return_status => l_return_status,
1592 x_num_warnings => l_num_warnings,
1593 x_num_errors => l_num_errors);
1594
1595 END LOOP;
1596 END IF;
1597 l_del_fgt_terms_tbl.delete;
1598 --}
1599 END IF;
1600 --
1601 IF l_debug_on THEN
1602 WSH_DEBUG_SV.log(l_module_name,'l_shippingControlTurnedOn',l_shippingControlTurnedOn);
1603 END IF;
1604 --
1605 IF l_shippingControlTurnedOn THEN
1606 --{
1607 l_action_prms.caller := 'PO_INTG';
1608 /*
1609 --
1610 IF l_debug_on THEN
1611 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_IB_TXN_MATCH_PKG.handlePriorReceipts',WSH_DEBUG_SV.C_PROC_LEVEL);
1612 END IF;
1613 --
1614 --
1615 WSH_IB_TXN_MATCH_PKG.handlePriorReceipts
1616 (
1617 p_action_prms => l_action_prms,
1618 x_line_rec => p_line_rec,
1619 x_return_status => l_return_status
1620 );
1621 --
1622 --
1623 IF l_debug_on THEN
1624 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1625 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
1626 END IF;
1627 --
1628 wsh_util_core.api_post_call
1629 (
1630 p_return_status => l_return_status,
1631 x_num_warnings => l_num_warnings,
1632 x_num_errors => l_num_errors
1633 );
1634 */
1635
1636 --}
1637 END IF;
1638
1639 --
1640 -- Debug Statements
1641 --
1642 IF l_debug_on THEN
1643 WSH_DEBUG_SV.pop(l_module_name);
1644 END IF;
1645 --
1646 IF l_num_errors > 0 THEN
1647 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1648 ELSIF l_num_warnings > 0 THEN
1649 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1650 END IF;
1651
1652
1653 EXCEPTION
1654 WHEN e_wdd_locked THEN
1655 ROLLBACK TO Update_dd_Attributes_PVT;
1656 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1657 FND_MESSAGE.SET_NAME('WSH','WSH_DELIVERY_LINES_LOCKED');
1658 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status,l_module_name);
1659
1660 --
1661 -- Debug Statements
1662 --
1663 IF l_debug_on THEN
1664 WSH_DEBUG_SV.logmsg(l_module_name,'e_Wdd_locked exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1665 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:e_Wdd_locked');
1666 END IF;
1667 WHEN FND_API.G_EXC_ERROR THEN
1668 ROLLBACK TO Update_dd_Attributes_PVT;
1669 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1670 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status,l_module_name);
1671
1672 --
1673 -- Debug Statements
1674 --
1675 IF l_debug_on THEN
1676 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1677 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1678 END IF;
1679 --
1680 WHEN OTHERS THEN
1681 ROLLBACK TO Update_dd_Attributes_PVT;
1682 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1683 WSH_UTIL_CORE.DEFAULT_HANDLER('WSH_PO_CMG_PVT.Update_dd_Attributes',l_module_name);
1684
1685 --
1686 -- Debug Statements
1687 --
1688 IF l_debug_on THEN
1689 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1690 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1691 END IF;
1692 --
1693 END Update_dd_Attributes;
1694
1695
1696 /*========================================================================
1697 -- PROCEDURE : Update_Quantity
1698 -- HISTORY : Created the API.
1699 ========================================================================*/
1700 -- Start of comments
1701 -- API name : Update_quantity
1702 -- Type : Private
1703 -- Pre-reqs : None.
1704 -- Function : This API gets called from two places:
1705 -- 1. PO Reapproval
1706 -- 2.ASN/Receipt Integration
1707 --
1708 -- 1.When called from PO Reapproval,the execution is as follows:
1709 -- For every Line_location_id passed by PO, check the total
1710 -- source quantity against the passed quantity.
1711 -- - If no records found for the line_location_id, it is a
1712 -- case of new record to be inserted in the
1713 -- WSH_DELIVERY_DETAILS table.
1714 -- - If records are found, then check if it a case of
1715 -- Incrementing the quantity or decrementing the quantity.
1716 -- - If the case is INCREMENT, then the additional qty is updated on to
1717 -- the open delivery detail.If no open delivery detail is found, then
1718 -- a new record is inserted into wsh_delivery_details for the
1719 -- additional quantity.
1720 -- - In case of DECREMENT in the qty, the changes are first applied to
1721 -- lines with null routing request id.
1722 -- If no such lines are found, then the changes are applied to line
1723 -- with routing request id .
1724 -- The changes are applied to lines in the order of the latest earliest_pickup_date.
1725 -- Also checks if all the delivery details for the present line location id have
1726 -- the same ship from or not.If the lines have different ship from location id,
1727 -- then the ship_from_location_id is updated to -1.
1728 -- - This also takes care of the quantity change happening due to
1729 -- change in the ordered UOM.
1730 --
1731 -- 2.When called from ASN/Receipt Integration, the case is set to 'INCREMENT'.
1732 -- - The quantity that needs to be incremeted is specified by
1733 -- p_line_rec.consolidate_quantity.
1734 --
1735 -- Parameters :
1736 -- IN OUT:
1737 -- p_line_rec IN OE_WSH_BULK_GRP.line_rec_type DEFAULT NULL
1738 -- This record structure contains information about the lines to be updated.
1739 -- The information may include header id,line id ,line location id and other po
1740 -- attributes..
1741 -- p_dd_id_unassigned IN OUT NOCOPY wsh_util_core.id_tab_type
1742 -- This contains the list of delviery details that needs to be unassigned from
1743 -- the respective delivery.
1744 -- p_wt_vol_dd_id IN OUT NOCOPY wsh_util_core.id_tab_type,
1745 -- This contains the list of delviery details for which weight and volume needs to
1746 -- be re-calculated.
1747 -- p_action_prms IN OUT NOCOPY WSH_BULK_TYPES_GRP.action_parameters_rectype
1748 -- The record which specifies the caller, actio to be performed(REAPPROVE,CANCEL_ASN,RECEIPT,ASN)
1749 -- OUT:
1750 -- x_return_status OUT NOCOPY VARCHAR2
1751 -- Version : 1.0
1752 -- Previous version 1.0
1753 -- Initial version 1.0
1754 -- End of comments
1755
1756 PROCEDURE Update_Quantity(
1757 p_line_rec IN OUT NOCOPY OE_WSH_BULK_GRP.line_rec_type,
1758 p_action_prms IN OUT NOCOPY WSH_BULK_TYPES_GRP.action_parameters_rectype,
1759 p_dd_id_unassigned IN OUT NOCOPY wsh_util_core.id_tab_type,
1760 p_wt_vol_dd_id IN OUT NOCOPY wsh_util_core.id_tab_type,
1761 x_return_status OUT NOCOPY VARCHAR2) IS
1762
1763 -- Cursor to get the source requested quantity corresponding to the
1764 -- po_line_location_id.
1765
1766 Cursor c_src_qty(p_line_location_id NUMBER,p_header_id NUMBER,p_line_id NUMBER)
1767 is
1768 select src_requested_quantity,
1769 src_requested_quantity_uom,
1770 src_requested_quantity2,
1771 --HACMS {
1772 requested_quantity_uom,
1773 inventory_item_id
1774 --HACMS }
1775 from WSH_DELIVERY_DETAILS
1776 where po_shipment_line_id = p_line_location_id and
1777 source_code = 'PO' AND
1778 source_header_id = p_header_id AND
1779 source_line_id = p_line_id AND
1780 rownum = 1;
1781
1782 --Cursor to get the count of lines with null routing request id in wdd.
1783
1784 Cursor c_dd_count_with_null_rr(p_line_location_id NUMBER,p_header_id NUMBER,p_line_id NUMBER) is
1785 SELECT count(*)
1786 FROM wsh_delivery_details
1787 WHERE released_status = 'X' and
1788 source_code = 'PO' AND
1789 source_header_id = p_header_id AND
1790 source_line_id = p_line_id AND
1791 po_shipment_line_id = p_line_location_id and
1792 routing_req_id is null;
1793
1794 -- Cursor to find out if there are any open delivery detail with null
1795 -- routing request id which is later used in the 'INCREMENT' quantity case.
1796
1797 Cursor c_inc_qty(p_line_location_id NUMBER,p_header_id NUMBER,p_line_id NUMBER)is
1798 select
1799 delivery_detail_id,
1800 requested_quantity,
1801 --HACMS {
1802 requested_quantity_uom ,
1803 inventory_item_id,
1804 --HACMS }
1805 last_update_date
1806 from WSH_DELIVERY_DETAILS
1807 where released_status = 'X' and
1808 source_code = 'PO' AND
1809 source_header_id = p_header_id AND
1810 source_line_id = p_line_id AND
1811 po_shipment_line_id = p_line_location_id and
1812 routing_req_id IS NULL
1813 and rownum = 1;
1814
1815
1816 --HACMS {
1817 -- Cursor to fetch the requested_qty ,uom, picked_qty which is later used
1818 -- to take care of the UOM changes for one time items.
1819 cursor c_lines_all(p_line_location_id NUMBER,p_header_id NUMBER,p_line_id NUMBER) is
1820 select
1821 delivery_detail_id,
1822 requested_quantity qty,
1823 picked_quantity pick_qty,
1824 requested_quantity_uom qty_uom,
1825 inventory_item_id,
1826 last_update_date
1827 from
1828 wsh_delivery_details
1829 where released_status = 'X' and
1830 source_code = 'PO' AND
1831 source_header_id = p_header_id AND
1832 source_line_id = p_line_id AND
1833 po_shipment_line_id = p_line_location_id;
1834 --HACMS }
1835
1836 --Cursor to check if lines with null routing request id exists in wdd.
1837
1838 cursor c_lines_without_rr(p_line_location_id NUMBER,p_header_id NUMBER,p_line_id NUMBER) is
1839 select
1840 delivery_detail_id,
1841 requested_quantity qty,
1842 --HACMS {
1843 inventory_item_id,
1844 requested_quantity_uom qty_uom,
1845 last_update_date
1846 --HACMS }
1847 from
1848 wsh_delivery_details
1849 where released_status = 'X' and
1850 source_code = 'PO' AND
1851 source_header_id = p_header_id AND
1852 source_line_id = p_line_id AND
1853 po_shipment_line_id = p_line_location_id and
1854 routing_req_id is null
1855 order by qty asc;
1856
1857 --Cursor to check if lines with routing request id exists in wdd
1858 --The lines are fetched in the order of the latest earliest_pickup_date.
1859
1860 cursor c_lines_with_rr(p_line_location_id NUMBER,p_header_id NUMBER,p_line_id NUMBER) is
1861 select
1862 wdd.requested_quantity qty,
1863 wdd.delivery_detail_id,
1864 wnd.delivery_id,
1865 wdd.ship_from_location_id,
1866 wdd.routing_req_id,
1867 wnd.planned_flag,
1868 --wdd.ignore_for_planning,
1869 nvl(wdd.ignore_for_planning, 'N') ignore_for_planning,
1870 --HACMS {
1871 wdd.inventory_item_id,
1872 wdd.picked_quantity pick_qty,
1873 wdd.requested_quantity_uom qty_uom,
1874 wdd.ship_to_location_id,
1875 wnd.ultimate_dropoff_location_id del_location,
1876 --HACMS }
1877 wdd.last_update_date
1878 from
1879 wsh_delivery_details wdd,
1880 wsh_delivery_assignments_v wda,
1881 wsh_new_deliveries wnd
1882 where wdd.released_status = 'X' and
1883 WDD.source_code = 'PO' AND
1884 WDD.source_header_id = p_header_id AND
1885 WDD.source_line_id = p_line_id AND
1886 wdd.po_shipment_line_id = p_line_location_id and
1887 wda.delivery_detail_id = wdd.delivery_detail_id and
1888 wnd.delivery_id(+) = wda.delivery_id and
1889 wdd.routing_req_id is not null
1890 order by wdd.earliest_pickup_date desc;
1891
1892 -- Cursor to find out if the ship from location id is same or different on the delivery details
1893 -- belonging to the same po line location id.
1894
1895 CURSOR c_ship_from_count(p_line_location_id NUMBER,p_header_id NUMBER,p_line_id NUMBER) is
1896 SELECT count(distinct(ship_from_location_id))
1897 FROM wsh_delivery_details
1898 WHERE released_status = 'X' and
1899 source_code = 'PO' AND
1900 source_header_id = p_header_id AND
1901 source_line_id = p_line_id AND
1902 po_shipment_line_id = p_line_location_id and
1903 routing_req_id is not null;
1904
1905 l_case VARCHAR2(20);
1906 l_insert_new_dd VARCHAR2(1);
1907 l_remaining_qty NUMBER ;
1908 l_qty_to_change_ord_uom NUMBER ;
1909 l_qty_to_change NUMBER ;
1910 l_requested_qty NUMBER ;
1911 l_cancelled_qty NUMBER ;
1912 l_released_status VARCHAR2(1);
1913 l_ignore_for_planning VARCHAR2(1);
1914 l_sf_count NUMBER;
1915 l_sf_differs VARCHAR2(1);
1916 l_ship_from_location_id NUMBER;
1917 l_routing_request_id NUMBER;
1918 l_record_with_null_rr NUMBER;
1919 l_src_qty NUMBER ;
1920 l_src_qty2 NUMBER ;
1921 l_src_qty_uom VARCHAR2(3) ;
1922 temp_qty NUMBER ;
1923 l_last_update_date DATE;
1924 l_return_status VARCHAR2(1);
1925 l_delivery_detail_id NUMBER;
1926 l_validate_flag VARCHAR2(1);
1927 l_dff_attribute WSH_FLEXFIELD_UTILS.FlexfieldAttributeTabType ;
1928 l_dff_context VARCHAR2(150);
1929 l_dff_update_flag VARCHAR2(1);
1930 l_additional_line_info_rec WSH_BULK_PROCESS_PVT.additional_line_info_rec_type;
1931 l_delivery_details_info wsh_glbl_var_strct_grp.delivery_details_rec_type;
1932 l_new_delivery_detail_id NUMBER;
1933 detail_rowid VARCHAR2(30);
1934 x_rowid NUMBER;
1935 x_delivery_detail_id NUMBER;
1936 l_tab_count NUMBER := 0;
1937
1938 l_init_msg_list VARCHAR2(30) := NULL;
1939 l_msg_count NUMBER;
1940 l_msg_data VARCHAR2(3000);
1941 l_commit VARCHAR2(1);
1942 l_validation_level NUMBER;
1943 l_delivery_id NUMBER;
1944 l_delivery_name VARCHAR2(150);
1945 l_num_warnings NUMBER := 0;
1946 l_num_errors NUMBER := 0;
1947 l_dd_id NUMBER;
1948 v_item_info_rec wsh_util_validate.item_info_rec_type;
1949 l_ratio NUMBER;
1950 l_ratio_tbl WSH_UTIL_CORE.id_tab_type;
1951 l_sum_req_qty NUMBER;
1952 l_requested_qty2 NUMBER;
1953 temp_qty2 NUMBER;
1954 l_diff_qty2 NUMBER;
1955
1956 l_dd_ids_dd_ids_cache WSH_UTIL_CORE.key_value_tab_type;
1957 l_dd_ids_dd_ids_ext_cache WSH_UTIL_CORE.key_value_tab_type;
1958
1959 -- Bug 3395410 : Added the following variable.
1960 l_routing_response_send VARCHAR2(1);
1961
1962 l_action_prms WSH_BULK_TYPES_GRP.action_parameters_rectype;
1963
1964 l_ind NUMBER;
1965 j NUMBER;
1966
1967 --HACMS {
1968 l_requested_quantity_uom VARCHAR2(3);
1969 l_new_req_qty_uom VARCHAR2(3);
1970 l_requested_quantity NUMBER;
1971 l_inventory_item_id NUMBER;
1972 l_src_qty_to_change NUMBER;
1973 l_tmp_requested_quantity NUMBER;
1974
1975 l_conv_pick_qty NUMBER;
1976 l_qty_to_split NUMBER;
1977 l_qty_to_split2 NUMBER;
1978 l_pick_qty NUMBER;
1979 l_split_delivery_detail_id NUMBER;
1980 l_req_res_id NUMBER;
1981 l_msg varchar2(2000);
1982 l_update_flag BOOLEAN := FALSE;
1983 --HACMS }
1984 l_caller VARCHAR2(20);
1985 --
1986 e_wdd_locked EXCEPTION ;
1987 PRAGMA EXCEPTION_INIT(e_wdd_locked,-54);
1988
1989 l_detail_tab WSH_UTIL_CORE.id_tab_type; -- DBI Project
1990 l_dbi_rs VARCHAR2(1); -- Return Status from DBI API
1991 l_debug_on BOOLEAN;
1992 --
1993 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_QUANTITY';
1994 --
1995 BEGIN
1996
1997 --
1998 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1999 --
2000 IF l_debug_on IS NULL
2001 THEN
2002 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2003 END IF;
2004 --
2005 --
2006 -- Debug Statements
2007 --
2008 IF l_debug_on THEN
2009 WSH_DEBUG_SV.push(l_module_name);
2010 END IF;
2011 --
2012 SAVEPOINT Update_Quantity_PVT;
2013
2014 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2015
2016
2017
2018 --code to copy to cache tables
2019
2020
2021 l_ind := p_wt_vol_dd_id.FIRST;
2022 WHILE l_ind IS NOT NULL
2023 LOOP
2024 wsh_util_core.get_cached_value(
2025 p_cache_tbl => l_dd_ids_dd_ids_cache,
2026 p_cache_ext_tbl => l_dd_ids_dd_ids_ext_cache,
2027 p_value => p_wt_vol_dd_id(l_ind),
2028 p_key => p_wt_vol_dd_id(l_ind),
2029 p_action => 'PUT',
2030 x_return_status => l_return_status);
2031
2032 wsh_util_core.api_post_call(
2033 p_return_status => l_return_status,
2034 x_num_warnings => l_num_warnings,
2035 x_num_errors => l_num_errors);
2036
2037 l_ind := p_wt_vol_dd_id.next(l_ind);
2038 END LOOP;
2039
2040 -- Assigning the l_action_prms with the p_action_prms so that all the attributes
2041 -- and column values are still retained in l_action_prms.
2042 l_action_prms := p_action_prms;
2043 For i in p_line_rec.po_shipment_line_id.FIRST..p_line_rec.po_shipment_line_id.LAST
2044
2045 LOOP
2046 l_remaining_qty := 0;
2047 l_qty_to_change_ord_uom := 0;
2048 l_qty_to_change := 0;
2049 l_requested_qty := 0;
2050 l_cancelled_qty := 0;
2051 temp_qty := 0;
2052 l_insert_new_dd := NULL;
2053 l_case := NULL;
2054 l_new_req_qty_uom := NULL; --HACMS
2055 --initlizing c_src_qty cursor's fetch variables to null for each iteration
2056 l_src_qty := NULL;
2057 l_src_qty_uom := NULL;
2058 l_src_qty2 := NULL;
2059 l_requested_quantity_uom := NULL;
2060 l_inventory_item_id := NULL;
2061 --
2062
2063 IF l_debug_on THEN
2064 WSH_DEBUG_SV.log(l_module_name,'i',i);
2065 WSH_DEBUG_SV.log(l_module_name,'header_id',p_line_rec.header_id(i));
2066 WSH_DEBUG_SV.log(l_module_name,'line_id',p_line_rec.line_id(i));
2067 WSH_DEBUG_SV.log(l_module_name,'po_shipment_line_id',p_line_rec.po_shipment_line_id(i));
2068 WSH_DEBUG_SV.log(l_module_name,'ordered_quantity',p_line_rec.ordered_quantity(i));
2069 WSH_DEBUG_SV.log(l_module_name,'order_quantity_uom',p_line_rec.order_quantity_uom(i));
2070 WSH_DEBUG_SV.log(l_module_name,'ordered_quantity2',p_line_rec.ordered_quantity2(i));
2071 WSH_DEBUG_SV.log(l_module_name,'ordered_quantity_uom2',p_line_rec.ordered_quantity_uom2(i));
2072 END IF;
2073 -- get the source requested quantity for the line location.
2074
2075 OPEN c_src_qty(p_line_rec.po_shipment_line_id(i),p_line_rec.header_id(i),
2076 p_line_rec.line_id(i));
2077 FETCH c_src_qty INTO l_src_qty,l_src_qty_uom,l_src_qty2,
2078 l_requested_quantity_uom,l_inventory_item_id; --HACMS
2079 CLOSE c_src_qty;
2080 -- The l_ratio is calculated on the basis of the new ordered quantity2.
2081
2082 l_ratio := p_line_rec.ordered_quantity2(i)/p_line_rec.ordered_quantity(i); --HACMS
2083 l_ratio_tbl(i) := l_ratio;
2084
2085 IF l_debug_on THEN
2086 WSH_DEBUG_SV.log(l_module_name,'l_src_qty', l_src_qty);
2087 WSH_DEBUG_SV.log(l_module_name,'l_src_qty2', l_src_qty2);
2088 WSH_DEBUG_SV.log(l_module_name,'l_src_qty_uom',l_src_qty_uom);
2089 --HACMS {
2090 WSH_DEBUG_SV.log(l_module_name,'l_requested_quantity_uom',l_requested_quantity_uom);
2091 WSH_DEBUG_SV.log(l_module_name,'l_inventory_item_id',l_inventory_item_id);
2092 WSH_DEBUG_SV.log(l_module_name,'l_ratio',l_ratio);
2093 WSH_DEBUG_SV.log(l_module_name,'l_ratio_tbl(i)',l_ratio_tbl(i));
2094 --HACMS }
2095 WSH_DEBUG_SV.log(l_module_name,'p_action_prms.action_code', p_action_prms.action_code);
2096 END IF;
2097
2098
2099 IF l_src_qty IS NOT NULL AND p_action_prms.action_code = 'REAPPROVE_PO'
2100 THEN
2101 --{
2102 IF l_debug_on THEN
2103 WSH_DEBUG_SV.log(l_module_name,'p_line_rec.ordered_quantity(i)',p_line_rec.ordered_quantity(i));
2104 WSH_DEBUG_SV.log(l_module_name,'p_line_rec.order_quantity_uom(i)',p_line_rec.order_quantity_uom(i));
2105 END IF;
2106 -- Check if ordered_quantity is different from source_requested_quantity or
2107 -- ordered_quantity_uom is different from source_requested_quantity_uom.
2108 IF (p_line_rec.ordered_quantity(i) <> l_src_qty OR
2109 p_line_rec.order_quantity_uom(i) <> l_src_qty_uom) THEN
2110 --HACMS {
2111 IF (l_inventory_item_id IS NOT NULL) THEN
2112 -- If qyt is different from src qty or uom is different from src_uom ,then
2113 -- call the WSH_INBOUND_UTIL_PKG.convert_quantity API to find the actual qty
2114 -- that needs to be chagned in ordered uom.
2115 -- This is done only if inventory item id is not null.
2116 IF l_debug_on THEN
2117 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_INBOUND_UTIL_PKG.convert_quantity',WSH_DEBUG_SV.C_PROC_LEVEL);
2118 END IF;
2119
2120 WSH_INBOUND_UTIL_PKG.convert_quantity(
2121 p_inv_item_id => p_line_rec.inventory_item_id(i),
2122 p_organization_id => p_line_rec.organization_id(i),
2123 p_primary_uom_code => p_line_rec.order_quantity_uom(i),
2124 p_quantity => l_src_qty,
2125 p_qty_uom_code => l_src_qty_uom,
2126 x_conv_qty => l_src_qty_to_change,
2127 x_return_status => l_return_status);
2128
2129 IF l_debug_on THEN
2130 WSH_DEBUG_SV.log(l_module_name,'convert_quantity l_return_status',l_return_status);
2131 WSH_DEBUG_SV.log(l_module_name,'l_src_qty_to_change',l_src_qty_to_change);
2132 END IF;
2133
2134 wsh_util_core.api_post_call(
2135 p_return_status => l_return_status,
2136 x_num_warnings => l_num_warnings,
2137 x_num_errors => l_num_errors);
2138
2139
2140 l_qty_to_change_ord_uom := abs(p_line_rec.ordered_quantity(i) - l_src_qty_to_change);
2141 IF l_debug_on THEN
2142 WSH_DEBUG_SV.log(l_module_name,'l_qty_to_change_ord_uom',l_qty_to_change_ord_uom);
2143 END IF;
2144 -- Call WSH_INBOUND_UTIL_PKG.convert_quantity to convert the ordered qty to base uom .
2145
2146 IF l_debug_on THEN
2147 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_INBOUND_UTIL_PKG.convert_quantity',WSH_DEBUG_SV.C_PROC_LEVEL);
2148 END IF;
2149 WSH_INBOUND_UTIL_PKG.convert_quantity(
2150 p_inv_item_id => p_line_rec.inventory_item_id(i),
2151 p_organization_id => p_line_rec.organization_id(i),
2152 p_primary_uom_code => l_requested_quantity_uom,
2153 p_quantity => l_qty_to_change_ord_uom,
2154 p_qty_uom_code => p_line_rec.order_quantity_uom(i),
2155 x_conv_qty => l_qty_to_change,
2156 x_return_status => l_return_status);
2157
2158 IF l_debug_on THEN
2159 WSH_DEBUG_SV.log(l_module_name,'convert_quantity l_return_status,l_qty_to_change',l_return_status||','||l_qty_to_change);
2160 END IF;
2161 wsh_util_core.api_post_call(
2162 p_return_status => l_return_status,
2163 x_num_warnings => l_num_warnings,
2164 x_num_errors => l_num_errors);
2165
2166 -- if ordered_quantity is less that src_qty then the case is 'DECREMENT'
2167 -- if ordered_quantity is greater than src_qty then the case is 'INCREMENT'.
2168
2169 IF (p_line_rec.ordered_quantity(i) < l_src_qty_to_change) THEN
2170 l_case := 'DECREMENT';
2171 ELSIF (p_line_rec.ordered_quantity(i) > l_src_qty_to_change) then
2172 l_case := 'INCREMENT';
2173 END IF;--Finding Case of Increment/Decrement.
2174
2175 ELSE
2176 --One time item i.e if inventory_item_id is null
2177 IF l_debug_on THEN
2178 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_INBOUND_UTIL_PKG.convert_quantity',WSH_DEBUG_SV.C_PROC_LEVEL);
2179 END IF;
2180
2181 WSH_INBOUND_UTIL_PKG.convert_quantity(
2182 p_inv_item_id => p_line_rec.inventory_item_id(i),
2183 p_organization_id => p_line_rec.organization_id(i),
2184 p_primary_uom_code => p_line_rec.order_quantity_uom(i),
2185 p_quantity => l_src_qty,
2186 p_qty_uom_code => l_src_qty_uom,
2187 x_conv_qty => l_src_qty_to_change,
2188 x_return_status => l_return_status);
2189
2190 IF l_debug_on THEN
2191 WSH_DEBUG_SV.log(l_module_name,'convert_quantity l_return_status',l_return_status);
2192 WSH_DEBUG_SV.log(l_module_name,'l_src_qty_to_change',l_src_qty_to_change);
2193 END IF;
2194
2195 wsh_util_core.api_post_call(
2196 p_return_status => l_return_status,
2197 x_num_warnings => l_num_warnings,
2198 x_num_errors => l_num_errors);
2199
2200
2201 l_qty_to_change := abs(p_line_rec.ordered_quantity(i) - l_src_qty_to_change);
2202 l_new_req_qty_uom:= p_line_rec.order_quantity_uom(i);
2203
2204 IF (p_line_rec.ordered_quantity(i) < l_src_qty_to_change) THEN
2205 l_case := 'DECREMENT';
2206 ELSIF (p_line_rec.ordered_quantity(i) > l_src_qty_to_change) then
2207 l_case := 'INCREMENT';
2208 END IF;--Finding Case of Increment/Decrement.
2209
2210 IF l_debug_on THEN
2211 WSH_DEBUG_SV.log(l_module_name,'l_qty_to_change',l_qty_to_change);
2212 WSH_DEBUG_SV.log(l_module_name,'l_new_req_qty_uom',l_new_req_qty_uom);
2213 END IF;
2214 END IF;
2215 --HACMS }
2216
2217 ELSE
2218 --No qty change, then go to the next record in p_line_rec.
2219
2220 GOTO next_p_line_rec;
2221 END IF;--quantity change is there.
2222 --}
2223 ELSIF l_src_qty is NOT NULL AND (p_action_prms.action_code = 'ASN' OR
2224 p_action_prms.action_code = 'RECEIPT') THEN
2225 -- In case the Reapprove_PO api is called from ASN or RECEIPT, we
2226 -- set the case as INCREMENT.
2227 l_qty_to_change := nvl(p_line_rec.consolidate_quantity(i),0);
2228 l_case := 'INCREMENT';
2229
2230 ELSE--insert case
2231 -- No delivery detail is present for the corresponding po shipment line.
2232 -- This is an insert case.
2233 l_insert_new_dd := 'Y';
2234
2235 END IF;
2236 --}
2237
2238 IF l_debug_on THEN
2239 WSH_DEBUG_SV.log(l_module_name,'l_insert_new_dd', l_insert_new_dd);
2240 WSH_DEBUG_SV.log(l_module_name,'l_case', l_case);
2241 END IF;
2242
2243 IF l_case = 'DECREMENT' THEN
2244 --{
2245 -- First the decrement of qty is done on delivery details with null routing request id.
2246 l_remaining_qty := l_qty_to_change;
2247
2248 FOR v_lines_without_rr IN c_lines_without_rr(p_line_rec.po_shipment_line_id(i),p_line_rec.header_id(i),p_line_rec.line_id(i))
2249 LOOP
2250 IF l_debug_on THEN
2251 WSH_DEBUG_SV.log(l_module_name,'l_qty_to_change',l_qty_to_change);
2252 WSH_DEBUG_SV.log(l_module_name,' v_lines_without_rr.qty', v_lines_without_rr.qty);
2253 WSH_DEBUG_SV.log(l_module_name,' v_lines_without_rr.delivery_detail_id', v_lines_without_rr.delivery_detail_id);
2254 WSH_DEBUG_SV.log(l_module_name,' v_lines_without_rr.last_update_date', v_lines_without_rr.last_update_date);
2255 END IF;
2256
2257 --HACMS {
2258 l_requested_quantity:=v_lines_without_rr.qty;
2259
2260 IF (v_lines_without_rr.inventory_item_id IS NULL and
2261 v_lines_without_rr.qty_uom <> p_line_rec.order_quantity_uom(i)) THEN
2262
2263 l_new_req_qty_uom := p_line_rec.order_quantity_uom(i);
2264
2265 WSH_INBOUND_UTIL_PKG.convert_quantity(
2266 p_inv_item_id => p_line_rec.inventory_item_id(i),
2267 p_organization_id => p_line_rec.organization_id(i),
2268 p_primary_uom_code => p_line_rec.order_quantity_uom(i),
2269 p_quantity => v_lines_without_rr.qty,
2270 p_qty_uom_code => l_requested_quantity_uom,
2271 x_conv_qty => l_requested_quantity,
2272 x_return_status => l_return_status);
2273
2274 IF l_debug_on THEN
2275 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
2276 WSH_DEBUG_SV.log(l_module_name,'l_requested_quantity',l_requested_quantity);
2277 END IF;
2278 END IF;
2279 --HACMS }
2280
2281 IF l_qty_to_change < l_requested_quantity THEN --HACMS
2282 -- IF the qty to be changed is less that the requested quantity, then
2283 -- the open delivery detail is updated with the new qty.
2284 l_requested_qty := l_requested_quantity - l_qty_to_change; --HACMS
2285 l_cancelled_qty := l_qty_to_change;
2286 l_released_status := 'X';
2287 l_remaining_qty := 0;
2288 ELSIF l_qty_to_change = l_requested_quantity then --HACMS
2289 -- If the qty to be changed is equal to the requested quantity, then the
2290 -- delviery detail gets updatd to 'D'(Cancelled).
2291 l_requested_qty := 0;
2292 l_cancelled_qty := l_qty_to_change;
2293 l_released_status := 'D';
2294 l_remaining_qty := 0;
2295 ELSE
2296 -- If the qty to be changed is greater than the requested quantity, then
2297 -- the delivery detail is updated to 'D'( Cancelled').
2298 -- The new qyt that needs to be updated is determined and this is
2299 -- updated once again on to the next available open delivery detail.
2300 temp_qty := l_qty_to_change - l_requested_quantity; --HACMS
2301 l_requested_qty := 0;
2302 l_cancelled_qty := l_qty_to_change;
2303 l_released_status := 'D';
2304 l_qty_to_change := temp_qty;
2305 l_remaining_qty := temp_qty;
2306 END IF;
2307
2308 IF l_debug_on THEN
2309 WSH_DEBUG_SV.log(l_module_name,'l_requested_qty',l_requested_qty);
2310 WSH_DEBUG_SV.log(l_module_name,'l_requested_qty2',l_requested_qty2);
2311 WSH_DEBUG_SV.log(l_module_name,'l_cancelled_qty',l_cancelled_qty);
2312 WSH_DEBUG_SV.log(l_module_name,'l_released_status',l_released_status);
2313 WSH_DEBUG_SV.log(l_module_name,'l_remaining_qty',l_remaining_qty);
2314 END IF;
2315
2316 --Collecting the list of delviery_detail_ids if the req qty is not
2317 --updated to 0.
2318 IF l_requested_qty <> 0 THEN
2319 wsh_util_core.get_cached_value(
2320 p_cache_tbl => l_dd_ids_dd_ids_cache,
2321 p_cache_ext_tbl => l_dd_ids_dd_ids_ext_cache,
2322 p_value => v_lines_without_rr.delivery_detail_id,
2323 p_key => v_lines_without_rr.delivery_detail_id,
2324 p_action => 'PUT',
2325 x_return_status => l_return_status);
2326
2327 wsh_util_core.api_post_call(
2328 p_return_status => l_return_status,
2329 x_num_warnings => l_num_warnings,
2330 x_num_errors => l_num_errors);
2331
2332 END IF;
2333
2334 Update wsh_delivery_details
2335 set
2336 requested_quantity = l_requested_qty,
2337 requested_quantity_uom = decode(l_new_req_qty_uom,NULL,requested_quantity_uom,l_new_req_qty_uom), --HACMS
2338 cancelled_quantity = nvl(cancelled_quantity,0) + nvl(l_cancelled_qty,0), --HACMS
2339 released_status = l_released_status,
2340 last_update_date = SYSDATE,
2341 last_updated_by = FND_GLOBAL.USER_ID,
2342 last_update_login = FND_GLOBAL.LOGIN_ID
2343 WHERE
2344 delivery_detail_id = v_lines_without_rr.delivery_detail_id AND
2345 po_shipment_line_id = p_line_rec.po_shipment_line_id(i) AND
2346 source_header_id = p_line_rec.header_id(i) AND
2347 source_line_id = p_line_rec.line_id(i) ANd
2348 source_code = 'PO' AND
2349 ((p_line_rec.source_blanket_reference_id(i) IS NULL AND
2350 source_blanket_reference_id IS NULL) OR
2351 (p_line_rec.source_blanket_reference_id(i) IS NOT NULL AND
2352 source_blanket_reference_id = p_line_rec.source_blanket_reference_id(i))) AND
2353 released_status = 'X' AND
2354 last_update_date = v_lines_without_rr.last_update_date;
2355
2356
2357 IF l_debug_on THEN
2358 WSH_DEBUG_SV.log(l_module_name,'Line Updated in wsh_delivery_details',sql%rowcount);
2359 END IF;
2360
2361 IF SQL%ROWCOUNT <> 1 THEN
2362 FND_MESSAGE.SET_NAME('WSH','WSH_DELIVERY_LINES_CHANGED');
2363 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
2364 RAISE FND_API.G_EXC_ERROR;
2365 END IF;
2366
2367 --
2368 -- DBI Project
2369 -- Update of wsh_delivery_details where requested_quantity/released_status
2370 -- are changed, call DBI API after the update.
2371 -- This API will also check for DBI Installed or not
2372 IF l_debug_on THEN
2373 WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Detail id-',v_lines_without_rr.delivery_detail_id);
2374 END IF;
2375 l_detail_tab(1) := v_lines_without_rr.delivery_detail_id;
2376 WSH_INTEGRATION.DBI_Update_Detail_Log
2377 (p_delivery_detail_id_tab => l_detail_tab,
2378 p_dml_type => 'UPDATE',
2379 x_return_status => l_dbi_rs);
2380
2381 IF l_debug_on THEN
2382 WSH_DEBUG_SV.log(l_module_name,'Return Status after DBI Call-',l_dbi_rs);
2383 END IF;
2384 IF l_dbi_rs = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
2385 -- just pass this return status to caller API
2386 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2387 END IF;
2388 -- End of Code for DBI Project
2389 --
2390
2391 EXIT WHEN (l_remaining_qty = 0);
2392
2393 END LOOP;
2394
2395 IF l_remaining_qty <> 0 THEN
2396 -- if lines with null routing request is not available, then
2397 -- the qty change is applied to lines with routing request.
2398 --{
2399 IF l_debug_on THEN
2400 WSH_DEBUG_SV.logmsg(l_module_name,'For l_remaining_qty <> 0');
2401 END IF;
2402
2403 l_qty_to_change := l_remaining_qty;
2404
2405 --Check if all the delivery details for the present line location id have
2406 --the same ship from. if not, set the ship_from_location_id to -1.
2407 --Also collect the list of such delivery details to p_dd_id_unassigned
2408 --which will be later used to unassgin the delivery details from the
2409 --delviery.
2410 --l_sf_differs ='1' => ship_from is different.
2411
2412 OPEN c_ship_from_count(p_line_rec.po_shipment_line_id(i),
2413 p_line_rec.header_id(i),p_line_rec.line_id(i));
2414 FETCH c_ship_from_count into l_sf_count;
2415 CLOSE c_ship_from_count;
2416
2417 IF l_sf_count > 1 THEN
2418 l_sf_differs := '1';
2419 END IF;
2420
2421 IF l_debug_on THEN
2422 WSH_DEBUG_SV.log(l_module_name,'l_sf_differs',l_sf_differs);
2423 END IF;
2424 -- The cursor c_lines_with_rr fetches delivery_details in the order of
2425 -- latest earliest_pickup_date.
2426 FOR v_lines_with_rr IN c_lines_with_rr(p_line_rec.po_shipment_line_id(i),p_line_rec.header_id(i),p_line_rec.line_id(i))
2427 LOOP--Start loop for processing lines with Routing Request
2428
2429 IF l_debug_on THEN
2430 WSH_DEBUG_SV.logmsg(l_module_name,'Start loop for processing lines with Routing Request');
2431 WSH_DEBUG_SV.log(l_module_name,'l_qty_to_change',l_qty_to_change);
2432 WSH_DEBUG_SV.log(l_module_name,' v_lines_with_rr.qty', v_lines_with_rr.qty);
2433 WSH_DEBUG_SV.log(l_module_name,' v_lines_with_rr.delivery_detail_id', v_lines_with_rr.delivery_detail_id);
2434 WSH_DEBUG_SV.log(l_module_name,' v_lines_with_rr.last_update_date', v_lines_with_rr.last_update_date);
2435 END IF;
2436
2437 --HACMS {
2438 l_requested_quantity:=v_lines_with_rr.qty;
2439
2440 IF (v_lines_with_rr.inventory_item_id IS NULL and
2441 v_lines_with_rr.qty_uom <> p_line_rec.order_quantity_uom(i)) THEN
2442
2443 l_new_req_qty_uom := p_line_rec.order_quantity_uom(i);
2444
2445 WSH_INBOUND_UTIL_PKG.convert_quantity(
2446 p_inv_item_id => p_line_rec.inventory_item_id(i),
2447 p_organization_id => p_line_rec.organization_id(i),
2448 p_primary_uom_code => p_line_rec.order_quantity_uom(i),
2449 p_quantity => v_lines_with_rr.qty,
2450 p_qty_uom_code => l_requested_quantity_uom,
2451 x_conv_qty => l_requested_quantity,
2452 x_return_status => l_return_status);
2453
2454 IF l_debug_on THEN
2455 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
2456 WSH_DEBUG_SV.log(l_module_name,'l_requested_quantity',l_requested_quantity);
2457 END IF;
2458 END IF;
2459 --HACMS }
2460
2461 IF l_qty_to_change < l_requested_quantity then --HACMS
2462 -- IF the qty to be changed is less that the requested quantity, then
2463 -- the open delivery detail is updated with the new qty.
2464 l_requested_qty := l_requested_quantity - l_qty_to_change; --HACMS
2465 l_cancelled_qty := l_qty_to_change;
2466 l_released_status := 'X';
2467 l_remaining_qty := 0;
2468 ELSIF l_qty_to_change = l_requested_quantity then
2469 -- If the qty to be changed is equal to the requested quantity, then the
2470 -- delviery detail gets updatd to 'D'(Cancelled).
2471 l_requested_qty := 0;
2472 l_cancelled_qty := l_qty_to_change;
2473 l_released_status := 'D';
2474 l_remaining_qty := 0;
2475 ELSE
2476 -- If the qty to be changed is greater than the requested quantity, then
2477 -- the delivery detail is updated to 'D'( Cancelled').
2478 -- The new qyt that needs to be updated is determined and this is
2479 -- updated once again on to the next available open delivery detail.
2480 temp_qty := l_qty_to_change - l_requested_quantity;
2481 l_requested_qty := 0;
2482 l_cancelled_qty := l_qty_to_change;
2483 l_released_status := 'D';
2484 l_qty_to_change := temp_qty;
2485 l_remaining_qty := temp_qty;
2486 END IF;
2487
2488 IF l_debug_on THEN
2489 WSH_DEBUG_SV.log(l_module_name,'l_requested_qty',l_requested_qty);
2490 WSH_DEBUG_SV.log(l_module_name,'l_requested_qty2',l_requested_qty2);
2491 WSH_DEBUG_SV.log(l_module_name,'l_cancelled_qty',l_cancelled_qty);
2492 WSH_DEBUG_SV.log(l_module_name,'l_released_status',l_released_status);
2493 WSH_DEBUG_SV.log(l_module_name,'l_remaining_qty',l_remaining_qty);
2494 WSH_DEBUG_SV.log(l_module_name,'v_lines_with_rr.delivery_detail_id',v_lines_with_rr.delivery_detail_id);
2495 END IF;
2496
2497 IF l_sf_differs = '1' OR l_released_status = 'D' THEN
2498 --{
2499 l_ship_from_location_id := -1;
2500 l_ignore_for_planning := 'Y';
2501
2502 IF v_lines_with_rr.delivery_id IS NOT NULL THEN
2503 --{
2504 -- Collecting the delivery detail id for which ship from location ids are different
2505 -- to p_ddd_id_unassigned list which is used at the end of
2506 -- Reapproval action to unassign the details from the delivery.
2507 p_dd_id_unassigned(p_dd_id_unassigned.COUNT +1):= v_lines_with_rr.delivery_detail_id;
2508
2509 IF l_debug_on THEN
2510 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit Log_Exception',WSH_DEBUG_SV.C_PROC_LEVEL);
2511 END IF;
2512
2513 -- Bug 3395410 : Setting the message that needs to be displayed while logging exception.
2514
2515 FND_MESSAGE.SET_NAME('WSH','WSH_IB_DELIVERY_CHANGE');
2516 FND_MESSAGE.SET_TOKEN('DELIVERY', v_lines_with_rr.delivery_id);
2517 l_msg := FND_MESSAGE.GET;
2518
2519 Log_Exception(
2520 p_entity_id => v_lines_with_rr.delivery_id,
2521 p_logging_entity_name => 'DELIVERY',
2522 p_exception_name => 'WSH_IB_DELIVERY_CHANGE',
2523 p_location_id => v_lines_with_rr.del_location,
2524 x_return_status => l_return_status);
2525
2526 --
2527 -- Debug Statements
2528 --
2529 IF l_debug_on THEN
2530 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
2531 END IF;
2532 --
2533 wsh_util_core.api_post_call(
2534 p_return_status => x_return_status,
2535 x_num_warnings => l_num_warnings,
2536 x_num_errors => l_num_errors);
2537
2538 END IF;
2539
2540 --}
2541 ELSE
2542 l_ship_from_location_id := v_lines_with_rr.ship_from_location_id;
2543 l_ignore_for_planning := v_lines_with_rr.ignore_for_planning;
2544 l_routing_request_id := v_lines_with_rr.routing_req_id;
2545 END IF;
2546 --}
2547
2548 --HACMS {
2549 IF l_sf_differs = '1' THEN
2550 -- if delivery details have different ship from location and routing response is send against
2551 -- these delivery details, then log exception against the delivery detail.
2552
2553 IF (WSH_INBOUND_UTIL_PKG.Is_Routing_Response_Send( v_lines_with_rr.delivery_detail_id,l_req_res_id) ) THEN
2554
2555 FND_MESSAGE.SET_NAME('WSH','WSH_IB_ST_LOC_CHANGE');
2556 FND_MESSAGE.SET_TOKEN('DETAIL', v_lines_with_rr.delivery_detail_id);
2557 FND_MESSAGE.SET_TOKEN('OLD_VALUE', v_lines_with_rr.ship_from_location_id);
2558 FND_MESSAGE.SET_TOKEN('NEW_VALUE', '-1');
2559 l_msg := FND_MESSAGE.GET;
2560
2561 IF l_debug_on THEN
2562 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit Log_Exception',WSH_DEBUG_SV.C_PROC_LEVEL);
2563 END IF;
2564 -- Anjana : Changed the v_lines_with_rr.ship_to_location_id to v_lines_with_rr.ship_from_location_id
2565 -- in the log_exception.
2566 Log_Exception(
2567 p_entity_id => v_lines_with_rr.delivery_detail_id,
2568 p_logging_entity_name => 'DETAIL',
2569 p_exception_name => 'WSH_IB_DETAIL_CHANGE',
2570 p_location_id => v_lines_with_rr.ship_from_location_id,
2571 p_message => l_msg,
2572 x_return_status => l_return_status);
2573
2574 IF l_debug_on THEN
2575 WSH_DEBUG_SV.log(l_module_name,'Log_Exception l_return_status',l_return_status);
2576 END IF;
2577
2578 wsh_util_core.api_post_call(
2579 p_return_status => x_return_status,
2580 x_num_warnings => l_num_warnings,
2581 x_num_errors => l_num_errors);
2582 END IF;
2583 END IF;
2584 --HACMS }
2585
2586
2587
2588 IF l_requested_qty <> 0 THEN
2589 wsh_util_core.get_cached_value(
2590 p_cache_tbl => l_dd_ids_dd_ids_cache,
2591 p_cache_ext_tbl => l_dd_ids_dd_ids_ext_cache,
2592 p_value => v_lines_with_rr.delivery_detail_id,
2593 p_key => v_lines_with_rr.delivery_detail_id,
2594 p_action => 'PUT',
2595 x_return_status => l_return_status);
2596
2597 wsh_util_core.api_post_call(
2598 p_return_status => l_return_status,
2599 x_num_warnings => l_num_warnings,
2600 x_num_errors => l_num_errors);
2601
2602 END IF;
2603
2604 --HACMS {
2605 l_pick_qty:=null;
2606 l_conv_pick_qty:=null;
2607
2608 IF (v_lines_with_rr.inventory_item_id IS NULL) THEN
2609 -- for one time items
2610
2611 WSH_INBOUND_UTIL_PKG.convert_quantity(
2612 p_inv_item_id => NULL,
2613 p_organization_id => p_line_rec.organization_id(i),
2614 p_primary_uom_code => p_line_rec.requested_quantity_uom(i),
2615 p_quantity => v_lines_with_rr.pick_qty,
2616 p_qty_uom_code => v_lines_with_rr.qty_uom,
2617 x_conv_qty => l_conv_pick_qty,
2618 x_return_status => l_return_status);
2619
2620 IF l_debug_on THEN
2621 WSH_DEBUG_SV.log(l_module_name,'convert_quantity l_return_status',l_return_status);
2622 WSH_DEBUG_SV.log(l_module_name,'l_conv_pick_qty',l_conv_pick_qty);
2623 END IF;
2624 --
2625 wsh_util_core.api_post_call(
2626 p_return_status => x_return_status,
2627 x_num_warnings => l_num_warnings,
2628 x_num_errors => l_num_errors);
2629
2630 IF (l_conv_pick_qty < l_requested_qty) then
2631 l_qty_to_split := l_requested_qty - l_conv_pick_qty;
2632 l_pick_qty := l_requested_qty;
2633 ELSE
2634 l_pick_qty := l_conv_pick_qty;
2635 END IF;
2636
2637 END IF;
2638 --HACMS }
2639
2640 Update wsh_delivery_details
2641 set
2642 requested_quantity = l_requested_qty,
2643 --HACMS {
2644 requested_quantity_uom = decode(l_new_req_qty_uom,NULL,requested_quantity_uom,l_new_req_qty_uom),
2645 cancelled_quantity = nvl(cancelled_quantity,0)+nvl(l_cancelled_qty,0),
2646 picked_quantity = decode(l_pick_qty,NULL,picked_quantity,l_pick_qty),
2647 --HACMS }
2648 released_status = l_released_status,
2649 ignore_for_planning = l_ignore_for_planning,
2650 ship_from_location_id = l_ship_from_location_id,
2651 routing_req_id = l_routing_request_id,
2652 last_update_date = SYSDATE,
2653 last_updated_by = FND_GLOBAL.USER_ID,
2654 last_update_login = FND_GLOBAL.LOGIN_ID
2655 WHERE
2656 delivery_detail_id = v_lines_with_rr.delivery_detail_id AND
2657 po_shipment_line_id = p_line_rec.po_shipment_line_id(i) AND
2658 source_header_id = p_line_rec.header_id(i) AND
2659 source_line_id = p_line_rec.line_id(i) AND
2660 source_code = 'PO' AND
2661 ((p_line_rec.source_blanket_reference_id(i) IS NULL AND
2662 source_blanket_reference_id IS NULL) OR
2663 (p_line_rec.source_blanket_reference_id(i) IS NOT NULL AND
2664 source_blanket_reference_id = p_line_rec.source_blanket_reference_id(i))) AND
2665 released_status = 'X' AND
2666 last_update_date = v_lines_with_rr.last_update_date;
2667
2668 IF SQL%ROWCOUNT <> 1 THEN
2669 IF l_debug_on THEN
2670 WSH_DEBUG_SV.log(l_module_name,'SQL%ROWCOUNT',SQL%ROWCOUNT);
2671 END IF;
2672 FND_MESSAGE.SET_NAME('WSH','WSH_DELIVERY_LINES_CHANGED');
2673 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
2674 RAISE FND_API.G_EXC_ERROR;
2675 END IF;
2676
2677 --
2678 -- DBI Project
2679 -- Update of wsh_delivery_details where requested_quantity/released_status
2680 -- are changed, call DBI API after the update.
2681 -- This API will also check for DBI Installed or not
2682 IF l_debug_on THEN
2683 WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Detail id-',v_lines_with_rr.delivery_detail_id);
2684 END IF;
2685 l_detail_tab(1) := v_lines_with_rr.delivery_detail_id;
2686 WSH_INTEGRATION.DBI_Update_Detail_Log
2687 (p_delivery_detail_id_tab => l_detail_tab,
2688 p_dml_type => 'UPDATE',
2689 x_return_status => l_dbi_rs);
2690
2691 IF l_debug_on THEN
2692 WSH_DEBUG_SV.log(l_module_name,'Return Status after DBI Call-',l_dbi_rs);
2693 END IF;
2694 IF l_dbi_rs = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
2695 -- just pass this return status to caller API
2696 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2697 END IF;
2698 -- End of Code for DBI Project
2699 --
2700
2701 --HACMS {
2702
2703 -- Bug 3395410 : Exception needs to be logged if the qty is decreased and the associated delivery is
2704 -- Contents Firm.Checking for the planned_flag for the same.
2705
2706 IF (WSH_INBOUND_UTIL_PKG.Is_Routing_Response_Send( v_lines_with_rr.delivery_detail_id,l_req_res_id)) THEN
2707 l_routing_response_send := 'Y';
2708 ELSE
2709 l_routing_response_send := 'N';
2710 END IF;
2711
2712 IF l_debug_on THEN
2713 WSH_DEBUG_SV.log(l_module_name,'l_routing_response_send:',l_routing_response_send);
2714 END IF;
2715
2716 IF ((l_routing_response_send = 'Y')
2717 OR (v_lines_with_rr.planned_flag in ('Y','F'))) THEN
2718 -- If routing response is send then log excpetion against the delivery detail and delivery.
2719 -- If delivery is firm,then log excpetion against the delivery.
2720
2721 FND_MESSAGE.SET_NAME('WSH','WSH_IB_REQ_QTY_CHANGE');
2722 FND_MESSAGE.SET_TOKEN('DETAIL', v_lines_with_rr.delivery_detail_id);
2723 FND_MESSAGE.SET_TOKEN('OLD_VALUE', v_lines_with_rr.qty);
2724 FND_MESSAGE.SET_TOKEN('NEW_VALUE', l_requested_qty);
2725 l_msg := FND_MESSAGE.GET;
2726
2727 IF l_debug_on THEN
2728 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit Log_Exception',WSH_DEBUG_SV.C_PROC_LEVEL);
2729 END IF;
2730
2731 IF (l_routing_response_send = 'Y') THEN
2732 -- Routing response is send.
2733
2734 Log_Exception(
2735 p_entity_id => v_lines_with_rr.delivery_detail_id,
2736 p_logging_entity_name => 'DETAIL',
2737 p_exception_name => 'WSH_IB_DETAIL_CHANGE',
2738 p_location_id => v_lines_with_rr.ship_to_location_id,
2739 p_message => l_msg,
2740 x_return_status => l_return_status);
2741
2742 IF l_debug_on THEN
2743 WSH_DEBUG_SV.log(l_module_name,'Log_Exception l_return_status',l_return_status);
2744 END IF;
2745
2746 wsh_util_core.api_post_call(
2747 p_return_status => x_return_status,
2748 x_num_warnings => l_num_warnings,
2749 x_num_errors => l_num_errors);
2750
2751 ELSIF v_lines_with_rr.planned_flag in ('Y','F') THEN
2752 -- Delivery is Contents Firm.
2753 Log_Exception(
2754 p_entity_id => v_lines_with_rr.delivery_id,
2755 p_logging_entity_name => 'DELIVERY',
2756 p_exception_name => 'WSH_IB_DELIVERY_CHANGE',
2757 p_location_id => v_lines_with_rr.del_location,
2758 p_message => l_msg,
2759 x_return_status => l_return_status);
2760
2761 IF l_debug_on THEN
2762 WSH_DEBUG_SV.log(l_module_name,'Log_Exception l_return_status',l_return_status);
2763 END IF;
2764
2765 wsh_util_core.api_post_call(
2766 p_return_status => x_return_status,
2767 x_num_warnings => l_num_warnings,
2768 x_num_errors => l_num_errors);
2769
2770 END IF;
2771
2772
2773
2774 FND_MESSAGE.SET_NAME('WSH','WSH_IB_DEL_ATT_CHANGE');
2775 FND_MESSAGE.SET_TOKEN('DELIVERY',v_lines_with_rr.delivery_id);
2776 l_msg := FND_MESSAGE.GET;
2777
2778 IF l_debug_on THEN
2779 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit Log_Exception',WSH_DEBUG_SV.C_PROC_LEVEL);
2780 END IF;
2781
2782 Log_Exception(
2783 p_entity_id => v_lines_with_rr.delivery_id,
2784 p_logging_entity_name => 'DELIVERY',
2785 p_exception_name => 'WSH_IB_DEL_ATT_CHANGE',
2786 p_location_id => v_lines_with_rr.del_location,
2787 p_message => l_msg,
2788 x_return_status => l_return_status);
2789
2790 IF l_debug_on THEN
2791 WSH_DEBUG_SV.log(l_module_name,'Log_Exception l_return_status',l_return_status);
2792 END IF;
2793 wsh_util_core.api_post_call(
2794 p_return_status => x_return_status,
2795 x_num_warnings => l_num_warnings,
2796 x_num_errors => l_num_errors);
2797
2798 END IF;
2799
2800 --HACMS }
2801
2802 EXIT WHEN (l_remaining_qty = 0);
2803
2804
2805 END LOOP;--end loop for processing lines with Routing Request
2806
2807 END IF; -- l_requested_qty
2808 --}
2809
2810
2811 ELSE--For increment quantity case.
2812 IF l_debug_on THEN
2813 WSH_DEBUG_SV.log(l_module_name,'For increment quantity case l_qty_to_change',l_qty_to_change);
2814 END IF;
2815 IF l_qty_to_change > 0 THEN
2816
2817 --{
2818
2819 OPEN c_dd_count_with_null_rr(p_line_rec.po_shipment_line_id(i),
2820 p_line_rec.header_id(i),p_line_rec.line_id(i));
2821 FETCH c_dd_count_with_null_rr into l_record_with_null_rr;
2822 CLOSE c_dd_count_with_null_rr;
2823
2824 IF l_debug_on THEN
2825 WSH_DEBUG_SV.log(l_module_name,'l_record_with_null_rr', l_record_with_null_rr);
2826 END IF;
2827
2828 IF l_record_with_null_rr >= 1 THEN
2829 -- Increase in Qty is applied only to open delivery details with null
2830 -- routing request id.
2831 -- If no such line is present, then a new record is inserted into wdd
2832 -- for the additional qty.
2833 --{
2834
2835 OPEN c_inc_qty(p_line_rec.po_shipment_line_id(i),
2836 p_line_rec.header_id(i),p_line_rec.line_id(i));
2837 FETCH c_inc_qty INTO l_delivery_detail_id, l_requested_qty,
2838 l_requested_quantity_uom, l_inventory_item_id, l_last_update_date; --HACMS
2839 CLOSE c_inc_qty;
2840
2841 wsh_util_core.get_cached_value(
2842 p_cache_tbl => l_dd_ids_dd_ids_cache,
2843 p_cache_ext_tbl => l_dd_ids_dd_ids_ext_cache,
2844 p_value => l_delivery_detail_id,
2845 p_key => l_delivery_detail_id,
2846 p_action => 'PUT',
2847 x_return_status => l_return_status);
2848
2849 wsh_util_core.api_post_call(
2850 p_return_status => l_return_status,
2851 x_num_warnings => l_num_warnings,
2852 x_num_errors => l_num_errors);
2853
2854
2855 IF l_debug_on THEN
2856 WSH_DEBUG_SV.log(l_module_name,'l_delivery_detail_id',l_delivery_detail_id);
2857 WSH_DEBUG_SV.log(l_module_name,'l_requested_qty',l_requested_qty);
2858 WSH_DEBUG_SV.log(l_module_name,'l_requested_qty2',l_requested_qty2);
2859 WSH_DEBUG_SV.log(l_module_name,'l_last_update_date',l_last_update_date);
2860 END IF;
2861
2862 --HACMS {
2863 IF (l_inventory_item_id IS NULL and
2864 l_requested_quantity_uom<> p_line_rec.order_quantity_uom(i)) THEN
2865
2866 l_new_req_qty_uom := p_line_rec.order_quantity_uom(i);
2867
2868 WSH_INBOUND_UTIL_PKG.convert_quantity(
2869 p_inv_item_id => p_line_rec.inventory_item_id(i),
2870 p_organization_id => p_line_rec.organization_id(i),
2871 p_primary_uom_code => p_line_rec.order_quantity_uom(i),
2872 p_quantity => l_requested_qty,
2873 p_qty_uom_code => l_requested_quantity_uom,
2874 x_conv_qty => l_tmp_requested_quantity,
2875 x_return_status => l_return_status);
2876
2877 l_requested_qty:=l_tmp_requested_quantity;
2878
2879 IF l_debug_on THEN
2880 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
2881 WSH_DEBUG_SV.log(l_module_name,'After conversion l_requested_qty',l_requested_qty);
2882 END IF;
2883 END IF;
2884 --HACMS }
2885
2886 UPDATE WSH_DELIVERY_DETAILS
2887 SET
2888 requested_quantity = l_requested_qty + l_qty_to_change,
2889 requested_quantity_uom = decode(l_new_req_qty_uom,NULL,requested_quantity_uom,l_new_req_qty_uom), --HACMS
2890 last_update_date = SYSDATE,
2891 last_updated_by = FND_GLOBAL.USER_ID,
2892 last_update_login = FND_GLOBAL.LOGIN_ID
2893 WHERE delivery_detail_id = l_delivery_detail_id AND
2894 po_shipment_line_id = p_line_rec.po_shipment_line_id(i) AND
2895 source_header_id = p_line_rec.header_id(i) AND
2896 source_line_id = p_line_rec.line_id(i) ANd
2897 source_code = 'PO' AND
2898 ((p_line_rec.source_blanket_reference_id(i) IS NULL AND
2899 source_blanket_reference_id IS NULL) OR
2900 (p_line_rec.source_blanket_reference_id(i) IS NOT NULL AND
2901 source_blanket_reference_id = p_line_rec.source_blanket_reference_id(i))) AND
2902 released_status = 'X' AND
2903 last_update_date = l_last_update_date;
2904
2905 IF SQL%ROWCOUNT <> 1 THEN
2906 IF l_debug_on THEN
2907 WSH_DEBUG_SV.log(l_module_name,'SQL%ROWCOUNT',SQL%ROWCOUNT);
2908 END IF;
2909 FND_MESSAGE.SET_NAME('WSH','WSH_DELIVERY_LINES_CHANGED');
2910 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
2911 RAISE FND_API.G_EXC_ERROR;
2912 END IF;
2913 l_update_flag := TRUE;
2914
2915 --
2916 -- DBI Project
2917 -- Update of wsh_delivery_details where requested_quantity/released_status
2918 -- are changed, call DBI API after the update.
2919 -- This API will also check for DBI Installed or not
2920 IF l_debug_on THEN
2921 WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Detail id-',l_delivery_detail_id);
2922 END IF;
2923 l_detail_tab(1) := l_delivery_detail_id;
2924 WSH_INTEGRATION.DBI_Update_Detail_Log
2925 (p_delivery_detail_id_tab => l_detail_tab,
2926 p_dml_type => 'UPDATE',
2927 x_return_status => l_dbi_rs);
2928
2929 IF l_debug_on THEN
2930 WSH_DEBUG_SV.log(l_module_name,'Return Status after DBI Call-',l_dbi_rs);
2931 END IF;
2932 IF l_dbi_rs = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
2933 -- just pass this return status to caller API
2934 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2935 END IF;
2936 -- End of Code for DBI Project
2937 --
2938
2939 ELSIF l_record_with_null_rr = 0 THEN
2940 -- insert a new record for additional qty.
2941 IF p_action_prms.action_code = 'REAPPROVE_PO' THEN
2942 l_caller := 'WSH-PO-INT';
2943 END IF;
2944 p_line_rec.requested_quantity(i) := l_qty_to_change;
2945 p_line_rec.requested_quantity_uom2(i) := p_line_rec.ordered_quantity_uom2(i);
2946 p_line_rec.tracking_number(i) := null; --Bugfix 3711663
2947
2948 IF l_debug_on THEN
2949 WSH_DEBUG_SV.log(l_module_name,'l_qty_to_change', l_qty_to_change);
2950 END IF;
2951
2952 IF l_debug_on THEN
2953 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit populate_additional_line_info',WSH_DEBUG_SV.C_PROC_LEVEL);
2954 END IF;
2955
2956 populate_additional_line_info(
2957 p_line_rec => p_line_rec,
2958 p_index => i,
2959 p_caller => l_caller,
2960 p_additional_line_info_rec => l_additional_line_info_rec ,
2961 x_return_status => l_return_status);
2962
2963 --
2964 -- Debug Statements
2965 --
2966 IF l_debug_on THEN
2967 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
2968 END IF;
2969 --
2970 wsh_util_core.api_post_call(
2971 p_return_status => l_return_status,
2972 x_num_warnings => l_num_warnings,
2973 x_num_errors => l_num_errors);
2974
2975 IF l_debug_on THEN
2976 WSH_DEBUG_SV.log(l_module_name,'l_qty_to_change', l_qty_to_change);
2977 END IF;
2978
2979 -- This is very important. We should use a local action prms
2980 -- otherwise it will overwrite the existing value of action in
2981 -- in p_action_prms
2982 --p_action_prms.action_code := 'CREATE';
2983 l_action_prms.action_code := 'CREATE';
2984 l_action_prms.org_id := p_line_rec.org_id(i);
2985 --
2986 -- Debug Statements
2987 --
2988 IF l_debug_on THEN
2989 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_BULK_PROCESS_PVT.BULK_INSERT_DETAILS',WSH_DEBUG_SV.C_PROC_LEVEL);
2990 END IF;
2991 --
2992 WSH_BULK_PROCESS_PVT.bulk_insert_details (
2993 P_line_rec => P_line_rec,
2994 p_index => i,
2995 p_action_prms => l_action_prms,
2996 p_additional_line_info_rec => l_additional_line_info_rec,
2997 X_return_status => l_return_status
2998 );
2999
3000 --
3001 -- Debug Statements
3002 --
3003 IF l_debug_on THEN
3004 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
3005 END IF;
3006 --
3007 wsh_util_core.api_post_call(
3008 p_return_status => l_return_status,
3009 x_num_warnings => l_num_warnings,
3010 x_num_errors => l_num_errors);
3011
3012 wsh_util_core.get_cached_value(
3013 p_cache_tbl => l_dd_ids_dd_ids_cache,
3014 p_cache_ext_tbl => l_dd_ids_dd_ids_ext_cache,
3015 p_value => p_line_rec.delivery_detail_id(i),
3016 p_key => p_line_rec.delivery_detail_id(i),
3017 p_action => 'PUT',
3018 x_return_status => l_return_status);
3019
3020 wsh_util_core.api_post_call(
3021 p_return_status => l_return_status,
3022 x_num_warnings => l_num_warnings,
3023 x_num_errors => l_num_errors);
3024
3025
3026 END IF;
3027 --}
3028 END IF;
3029 --}
3030
3031 END IF;--end if for decrement/increment cases.
3032 --}
3033
3034 IF l_insert_new_dd = 'Y' THEN
3035 --{
3036 -- No delivery detail is present for the corresponding po shipment line id.
3037 -- Need to insert a new record for the line location id.
3038
3039 IF l_debug_on THEN
3040 WSH_DEBUG_SV.log(l_module_name,'p_line_rec.requested_quantity(i)', p_line_rec.requested_quantity(i));
3041 --WSH_DEBUG_SV.log(l_module_name,'p_line_rec.consolidate_quantity(i)', p_line_rec.consolidate_quantity(i));
3042 END IF;
3043 IF p_action_prms.action_code = 'REAPPROVE_PO' THEN
3044 l_caller := 'WSH-PO-INT';
3045 END IF;
3046
3047 IF l_debug_on THEN
3048 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit populate_additional_line_info',WSH_DEBUG_SV.C_PROC_LEVEL);
3049 END IF;
3050 -- populate p_additional_line_info_rec by calling populate_additional_line_info() API.
3051 populate_additional_line_info(
3052 p_line_rec => p_line_rec,
3053 p_index => i,
3054 p_caller => l_caller,
3055 p_additional_line_info_rec => l_additional_line_info_rec ,
3056 x_return_status => l_return_status);
3057 --
3058 -- Debug Statements
3059 --
3060 IF l_debug_on THEN
3061 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
3062 END IF;
3063 --
3064 wsh_util_core.api_post_call(
3065 p_return_status => l_return_status,
3066 x_num_warnings => l_num_warnings,
3067 x_num_errors => l_num_errors);
3068
3069 --
3070 -- Debug Statements
3071 --
3072 IF l_debug_on THEN
3073 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_BULK_PROCESS_PVT.BULK_INSERT_DETAILS',WSH_DEBUG_SV.C_PROC_LEVEL);
3074 END IF;
3075 --
3076 l_action_prms := p_action_prms;
3077 l_action_prms.org_id := p_line_rec.org_id(i);
3078 --
3079 IF l_debug_on THEN
3080 WSH_DEBUG_SV.log(l_module_name,' l_action_prms.org_id', l_action_prms.org_id);
3081 END IF;
3082 --
3083 -- Call WSH_BULK_PROCESS_PVT.bulk_insert_details() to insert the record into wsh_delivery_details.
3084 WSH_BULK_PROCESS_PVT.bulk_insert_details (
3085 P_line_rec => P_line_rec,
3086 p_index => i,
3087 p_action_prms => l_action_prms,
3088 p_additional_line_info_rec => l_additional_line_info_rec,
3089 X_return_status => l_return_status
3090 );
3091
3092 --
3093 -- Debug Statements
3094 --
3095 IF l_debug_on THEN
3096 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
3097 END IF;
3098 --
3099 wsh_util_core.api_post_call(
3100 p_return_status => l_return_status,
3101 x_num_warnings => l_num_warnings,
3102 x_num_errors => l_num_errors);
3103
3104 END IF;
3105 --}
3106
3107 --HACMS {
3108 --Take care of UOM for unchange UOM lines for one time items
3109 IF (l_inventory_item_id IS NULL) THEN
3110 FOR v_lines IN c_lines_all(p_line_rec.po_shipment_line_id(i),p_line_rec.header_id(i),p_line_rec.line_id(i) )
3111 LOOP
3112
3113 IF ( v_lines.qty_uom <> p_line_rec.requested_quantity_uom(i)) THEN --{
3114 WSH_INBOUND_UTIL_PKG.convert_quantity(
3115 p_inv_item_id => NULL,
3116 p_organization_id => p_line_rec.organization_id(i),
3117 p_primary_uom_code => p_line_rec.requested_quantity_uom(i),
3118 p_quantity => v_lines.qty,
3119 p_qty_uom_code => v_lines.qty_uom,
3120 x_conv_qty => l_qty_to_change,
3121 x_return_status => l_return_status);
3122
3123 IF l_debug_on THEN
3124 WSH_DEBUG_SV.log(l_module_name,'convert_quantity Req Qty l_return_status,l_qty_to_change',l_return_status||','||l_qty_to_change);
3125 END IF;
3126
3127 wsh_util_core.api_post_call(
3128 p_return_status => l_return_status,
3129 x_num_warnings => l_num_warnings,
3130 x_num_errors => l_num_errors);
3131
3132
3133 IF (v_lines.pick_qty IS NOT NULL or v_lines.pick_qty <> 0) THEN
3134 WSH_INBOUND_UTIL_PKG.convert_quantity(
3135 p_inv_item_id => NULL,
3136 p_organization_id => p_line_rec.organization_id(i),
3137 p_primary_uom_code => p_line_rec.requested_quantity_uom(i),
3138 p_quantity => v_lines.pick_qty,
3139 p_qty_uom_code => v_lines.qty_uom,
3140 x_conv_qty => l_pick_qty,
3141 x_return_status => l_return_status);
3142
3143 IF l_debug_on THEN
3144 WSH_DEBUG_SV.log(l_module_name,'convert_quantity Pick Qty l_return_status',l_return_status);
3145 WSH_DEBUG_SV.log(l_module_name,'l_pick_qty',l_pick_qty);
3146 END IF;
3147 --
3148 wsh_util_core.api_post_call(
3149 p_return_status => x_return_status,
3150 x_num_warnings => l_num_warnings,
3151 x_num_errors => l_num_errors);
3152
3153 END IF;
3154
3155 Update wsh_delivery_details
3156 set requested_quantity = l_qty_to_change,
3157 requested_quantity_uom = p_line_rec.requested_quantity_uom(i),
3158 picked_quantity = decode(l_pick_qty,null,picked_quantity,l_pick_qty) ,
3159 last_update_date = SYSDATE,
3160 last_updated_by = FND_GLOBAL.USER_ID,
3161 last_update_login = FND_GLOBAL.LOGIN_ID
3162 WHERE delivery_detail_id = v_lines.delivery_detail_id AND
3163 po_shipment_line_id = p_line_rec.po_shipment_line_id(i) AND
3164 source_header_id = p_line_rec.header_id(i) AND
3165 source_line_id = p_line_rec.line_id(i) ANd
3166 source_code = 'PO' AND
3167 ((p_line_rec.source_blanket_reference_id(i) IS NULL AND source_blanket_reference_id IS NULL) OR
3168 (p_line_rec.source_blanket_reference_id(i) IS NOT NULL AND source_blanket_reference_id = p_line_rec.source_blanket_reference_id(i))) AND
3169 released_status = 'X' AND
3170 last_update_date = v_lines.last_update_date;
3171
3172 IF SQL%ROWCOUNT <> 1 THEN
3173 IF l_debug_on THEN
3174 WSH_DEBUG_SV.log(l_module_name,'SQL%ROWCOUNT',SQL%ROWCOUNT);
3175 END IF;
3176 FND_MESSAGE.SET_NAME('WSH','WSH_DELIVERY_LINES_CHANGED');
3177 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
3178 RAISE FND_API.G_EXC_ERROR;
3179 END IF;
3180 --
3181 -- DBI Project
3182 -- Update of wsh_delivery_details where requested_quantity/released_status
3183 -- are changed, call DBI API after the update.
3184 -- This API will also check for DBI Installed or not
3185 IF l_debug_on THEN
3186 WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Detail id-',v_lines.delivery_detail_id);
3187 END IF;
3188 l_detail_tab(1) := v_lines.delivery_detail_id;
3189 WSH_INTEGRATION.DBI_Update_Detail_Log
3190 (p_delivery_detail_id_tab => l_detail_tab,
3191 p_dml_type => 'UPDATE',
3192 x_return_status => l_dbi_rs);
3193
3194 IF l_debug_on THEN
3195 WSH_DEBUG_SV.log(l_module_name,'Return Status after DBI Call-',l_dbi_rs);
3196 END IF;
3197 IF l_dbi_rs = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
3198 -- just pass this return status to caller API
3199 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3200 END IF;
3201 -- End of Code for DBI Project
3202 --
3203
3204
3205 END IF; --}
3206
3207 END LOOP;
3208 END IF;
3209 --HACMS }
3210
3211
3212 <<next_p_line_rec>>
3213 null;
3214 END LOOP;--For p_line_rec
3215
3216
3217 --Updating src_quantity on all lines whether 'OPEN'/'CLOSED'/'SHIPPED'.
3218 -- in case of increment, we still need to upd. sec. qty.
3219 --
3220 IF p_action_prms.action_code = 'REAPPROVE_PO'
3221 OR l_update_flag --- increment from asn/receipt
3222 THEN
3223 FORALL i in 1..p_line_rec.po_shipment_line_id.COUNT
3224
3225 Update wsh_delivery_details
3226 Set src_requested_quantity = nvl(p_line_rec.ordered_quantity(i),src_requested_quantity),
3227 src_requested_quantity_uom = nvl(p_line_rec.order_quantity_uom(i),src_requested_quantity_uom),
3228 --HACMS
3229 -- HW OPMCONV - No need to use OPM precision. Use current INV which is 5
3230 requested_quantity2 = decode(released_status,'X',ROUND(l_ratio_tbl(i) * requested_quantity ,WSH_UTIL_CORE.C_MAX_DECIMAL_DIGITS_INV),requested_quantity2),
3231 cancelled_quantity2 = decode(released_status,'X',ROUND(l_ratio_tbl(i) * cancelled_quantity ,WSH_UTIL_CORE.C_MAX_DECIMAL_DIGITS_INV),cancelled_quantity2),
3232 picked_quantity2 = decode(released_status,'X',ROUND(l_ratio_tbl(i) * picked_quantity ,WSH_UTIL_CORE.C_MAX_DECIMAL_DIGITS_INV),picked_quantity2),
3233 src_requested_quantity2 = nvl(p_line_rec.ordered_quantity2(i),src_requested_quantity2),
3234 src_requested_quantity_uom2 = nvl(p_line_rec.ordered_quantity_uom2(i),src_requested_quantity_uom2),
3235 --HACMS
3236 source_line_number = p_line_rec.source_line_number(i),
3237 po_shipment_line_number = p_line_rec.po_shipment_line_number(i),
3238 last_update_date = SYSDATE,
3239 last_updated_by = FND_GLOBAL.USER_ID,
3240 last_update_login = FND_GLOBAL.LOGIN_ID
3241 where source_code = 'PO'
3242 AND po_shipment_line_id = p_line_rec.po_shipment_line_id(i)
3243 AND source_header_id = p_line_rec.header_id(i)
3244 AND source_line_id = p_line_rec.line_id(i)
3245 AND source_code = 'PO' AND
3246 ((p_line_rec.source_blanket_reference_id(i) IS NULL AND source_blanket_reference_id IS NULL) OR
3247 (p_line_rec.source_blanket_reference_id(i) IS NOT NULL AND source_blanket_reference_id = p_line_rec.source_blanket_reference_id(i))) ;
3248
3249
3250 IF l_debug_on THEN
3251 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
3252 END IF;
3253 --
3254 END IF;
3255
3256 --copying back to the original table after deleting the contents from it
3257 p_wt_vol_dd_id.delete;
3258 l_ind := l_dd_ids_dd_ids_cache.FIRST;
3259
3260 IF l_debug_on THEN
3261 WSH_DEBUG_SV.log(l_module_name,'first-l_ind' ,l_ind);
3262 END IF;
3263
3264 WHILE l_ind IS NOT NULL
3265 LOOP
3266 p_wt_vol_dd_id(p_wt_vol_dd_id.COUNT + 1) := l_dd_ids_dd_ids_cache(l_ind).value;
3267 l_ind := l_dd_ids_dd_ids_cache.NEXT(l_ind);
3268 IF l_debug_on THEN
3269 WSH_DEBUG_SV.log(l_module_name,' p_wt_vol_dd_id.count' , p_wt_vol_dd_id.count);
3270 WSH_DEBUG_SV.log(l_module_name,' p_wt_vol_dd_id( p_wt_vol_dd_id.count)' ,p_wt_vol_dd_id( p_wt_vol_dd_id.count));
3271 WSH_DEBUG_SV.log(l_module_name,'l_ind' ,l_ind);
3272 END IF;
3273 END LOOP;
3274 l_ind := l_dd_ids_dd_ids_ext_cache.FIRST;
3275 IF l_debug_on THEN
3276 WSH_DEBUG_SV.log(l_module_name,'first-ext-l_ind' ,l_ind);
3277 END IF;
3278 WHILE l_ind IS NOT NULL
3279 LOOP
3280 p_wt_vol_dd_id(p_wt_vol_dd_id.COUNT + 1) := l_dd_ids_dd_ids_ext_cache(l_ind).value;
3281 l_ind := l_dd_ids_dd_ids_ext_cache.NEXT(l_ind);
3282 IF l_debug_on THEN
3283 WSH_DEBUG_SV.log(l_module_name,' p_wt_vol_dd_id.count' , p_wt_vol_dd_id.count);
3284 WSH_DEBUG_SV.log(l_module_name,' p_wt_vol_dd_id( p_wt_vol_dd_id.count)' ,p_wt_vol_dd_id( p_wt_vol_dd_id.count));
3285 WSH_DEBUG_SV.log(l_module_name,'l_ind' ,l_ind);
3286 END IF;
3287 END LOOP;
3288
3289
3290 --
3291 -- Debug Statements
3292 --
3293 IF l_debug_on THEN
3294 WSH_DEBUG_SV.pop(l_module_name);
3295 END IF;
3296 --
3297 IF l_num_errors > 0 THEN
3298 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3299 ELSIF l_num_warnings > 0 THEN
3300 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3301 END IF;
3302
3303
3304 EXCEPTION
3305 WHEN e_wdd_locked THEN
3306 ROLLBACK TO Update_quantity_PVT;
3307 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3308 FND_MESSAGE.SET_NAME('WSH','WSH_DELIVERY_LINES_LOCKED');
3309 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status,l_module_name);
3310
3311 --
3312 -- Debug Statements
3313 --
3314 IF l_debug_on THEN
3315 WSH_DEBUG_SV.logmsg(l_module_name,'e_Wdd_locked exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3316 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:e_Wdd_locked');
3317 END IF;
3318 WHEN FND_API.G_EXC_ERROR THEN
3319 ROLLBACK TO Update_quantity_PVT;
3320 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3321 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status,l_module_name);
3322 --
3323 -- Debug Statements
3324 --
3325 IF l_debug_on THEN
3326 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3327 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
3328 END IF;
3329 --
3330 -- Added for DBI Project to handle Unexpected error
3331 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3332 ROLLBACK TO Update_quantity_PVT;
3333 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
3334 --
3335 IF l_debug_on THEN
3336 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3337 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
3338 END IF;
3339 --
3340 -- End of Code Added for DBI Project to handle Unexpected error
3341 WHEN OTHERS THEN
3342 ROLLBACK TO Update_quantity_PVT;
3343 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
3344 WSH_UTIL_CORE.DEFAULT_HANDLER('WSH_PO_CMG_PVT.Update_quantity',l_module_name);
3345
3346 --
3347 -- Debug Statements
3348 --
3349 IF l_debug_on THEN
3350 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3351 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3352 END IF;
3353 --
3354 END Update_Quantity;
3355
3356 --Developed by Arun Raj
3357 --======================================================================
3358 -- PROCEDURE : cancel_close_po
3359 -- HISTORY : Created the API.
3360 --======================================================================
3361 -- Start of comments
3362 -- API name : cancel_close_po
3363 -- Type : Public
3364 -- Pre-reqs : None.
3365 -- Function : This API updates the status of lines to closed/cancelled depending
3366 -- on the action code and caller passed through the i/p parameter
3367 -- p_action_prms.
3368 -- Parameters :
3369 -- IN:
3370 -- p_line_rec IN OE_WSH_BULK_GRP.line_rec_type DEFAULT NULL
3371 -- This record structure contains information about the lines to be deleted.
3372 -- The information may include header id,line id ,line location id etc.
3373 -- p_action_prms IN OUT NOCOPY
3374 -- WSH_BULK_TYPES_GRP.action_parameters_rectype
3375 -- The record which specifies the caller, actio to be performed(Close/Cancel)
3376 -- IN OUT:
3377 -- OUT:
3378 -- x_return_status OUT NOCOPY VARCHAR2
3379 -- Version : 1.0
3380 -- Previous version 1.0
3381 -- Initial version 1.0
3382 -- End of comments
3383
3384
3385 PROCEDURE cancel_close_po(
3386 p_line_rec IN OE_WSH_BULK_GRP.line_rec_type DEFAULT NULL,
3387 p_action_prms IN OUT NOCOPY
3388 WSH_BULK_TYPES_GRP.action_parameters_rectype,
3389 x_return_status OUT NOCOPY VARCHAR2) IS
3390
3391 -- Cursor to fetch the open delivery detail ids that needs to be either cancelled of closed
3392 -- based on the action code that is being passed to the API.
3393
3394 cursor c_dd_ids(p_header_id NUMBER,p_line_id NUMBER,p_line_location_id NUMBER,p_release_id NUMBER) IS
3395 SELECT
3396 wdd.delivery_detail_id,
3397 wdd.requested_quantity,
3398 wdd.requested_quantity2,
3399 wda.delivery_id,
3400 wdd.last_update_date
3401 FROM
3402 wsh_delivery_details wdd,
3403 wsh_delivery_assignments_v wda
3404 WHERE
3405 wdd.source_header_id = NVL(p_header_id,wdd.source_header_id) AND
3406 wdd.delivery_detail_id = wda.delivery_detail_id AND
3407 wdd.source_line_id = p_line_id AND
3408 wdd.po_shipment_line_id = p_line_location_id AND
3409 ( (p_release_id IS NULL AND wdd.source_blanket_reference_id IS NULL)
3410 OR
3411 (p_release_id IS NOT NULL AND wdd.source_blanket_reference_id = p_release_id )
3412 ) AND
3413 wdd.source_code = 'PO' AND
3414 wdd.released_status = 'X'
3415 for update of wdd.released_status NOWAIT;
3416
3417 l_chk_pend NUMBER := 0;
3418 l_return_status VARCHAR2(1);
3419 l_num_warnings NUMBER := 0;
3420 l_num_errors NUMBER := 0;
3421 l_index NUMBER := 0;
3422 dd_ids_tab WSH_UTIL_CORE.ID_TAB_TYPE;
3423 dd_ids_tab_for_unassign WSH_UTIL_CORE.ID_TAB_TYPE;
3424 req_qty_tab WSH_UTIL_CORE.ID_TAB_TYPE;
3425 req_qty2_tab WSH_UTIL_CORE.ID_TAB_TYPE;
3426 last_update_date_tab WSH_UTIL_CORE.date_tab_type;
3427
3428 l_api_version NUMBER := 1.0;
3429 l_init_msg_list VARCHAR2(30) := NULL;
3430 l_msg_count NUMBER;
3431 l_msg_data VARCHAR2(3000);
3432 l_commit VARCHAR2(1);
3433 l_validation_level NUMBER;
3434 l_delivery_id NUMBER;
3435 l_action_prms wsh_glbl_var_strct_grp.dd_action_parameters_rec_type;
3436
3437 l_detail_tab WSH_UTIL_CORE.id_tab_type; -- DBI Project
3438 l_dbi_rs VARCHAR2(1); -- Return Status from DBI API
3439
3440 e_wdd_locked EXCEPTION ;
3441 PRAGMA EXCEPTION_INIT(e_wdd_locked,-54);
3442 --
3443 l_debug_on BOOLEAN;
3444 --
3445 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CANCEL_CLOSE_PO';
3446 --
3447 BEGIN
3448 --
3449 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3450 --
3451 IF l_debug_on IS NULL
3452 THEN
3453 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3454 END IF;
3455 --
3456 --
3457 -- Debug Statements
3458 --
3459 IF l_debug_on THEN
3460 WSH_DEBUG_SV.push(l_module_name);
3461 END IF;
3462 --
3463 SAVEPOINT cancel_close_PO_PVT;
3464 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS ;
3465
3466 l_index := p_line_rec.line_id.FIRST;
3467 IF l_debug_on THEN
3468 WSH_DEBUG_SV.log(l_module_name,'action code is :',p_action_prms.action_code);
3469 END IF;
3470
3471
3472 WHILE l_index IS NOT NULL
3473 LOOP
3474 --
3475 -- Debug Statements
3476 --
3477 IF l_debug_on THEN
3478 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_PO_CMG_PVT.CHECK_PENDING_TXNS',WSH_DEBUG_SV.C_PROC_LEVEL);
3479 END IF;
3480 --
3481
3482 --Call to check whether the line has any pending transactions
3483 --against it.
3484 --If l_chk_pend = 0,there are no pending(unmatched)transactions agaisnt the line.
3485 --IF l_chk_pend = 1, there are pending(unmatched) transactions against the line.
3486
3487 l_chk_pend:= WSH_PO_CMG_PVT.check_pending_txns(
3488 p_line_rec.header_id(l_index),
3489 p_line_rec.line_id(l_index),
3490 p_line_rec.po_shipment_line_id(l_index),
3491 p_line_rec.source_blanket_reference_id(l_index));
3492
3493 IF l_chk_pend = 0 THEN
3494 -- No pending transactions
3495 -- Collect the list of delivery detail ids that needs to be updated into dd_ids_tab array.
3496 -- Also collect the list of delivery details ids for which delivery id is not null into
3497 -- dd_ids_tab_for_unassign array which is later used for 'UNASSIGN' action.(by calling
3498 -- unassign_multiple_details grp API.
3499
3500 FOR v_dd_ids IN c_dd_ids(p_line_rec.header_id(l_index),
3501 p_line_rec.line_id(l_index),
3502 p_line_rec.po_shipment_line_id(l_index),
3503 p_line_rec.source_blanket_reference_id(l_index))
3504 LOOP
3505 dd_ids_tab(dd_ids_tab.COUNT + 1) := v_dd_ids.delivery_detail_id;
3506 req_qty_tab(req_qty_tab.COUNT+1) := v_dd_ids.requested_quantity;
3507 req_qty2_tab(req_qty2_tab.COUNT+1) := v_dd_ids.requested_quantity2;
3508 last_update_date_tab(last_update_date_tab.COUNT+1) := v_dd_ids.last_update_date;
3509
3510 IF v_dd_ids.delivery_id IS NOT NULL THEN
3511 dd_ids_tab_for_unassign(dd_ids_tab_for_unassign.COUNT + 1) := v_dd_ids.delivery_detail_id;
3512 END IF;
3513 END LOOP;
3514 END IF;
3515
3516 l_index := p_line_rec.line_id.NEXT(l_index);
3517 END LOOP;
3518
3519 IF dd_ids_tab_for_unassign.COUNT > 0 THEN
3520 --
3521 -- Debug Statements
3522 --
3523 IF l_debug_on THEN
3524 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.UNASSIGN_MULTIPLE_DETAILS',WSH_DEBUG_SV.C_PROC_LEVEL);
3525 END IF;
3526
3527 l_action_prms.caller := wsh_util_core.C_IB_PO_PREFIX;
3528
3529 WSH_DELIVERY_DETAILS_ACTIONS.unassign_multiple_details(
3530 p_rec_of_detail_ids => dd_ids_tab_for_unassign,
3531 p_from_delivery => 'Y',
3532 p_from_container => 'N',
3533 x_return_status => l_return_status,
3534 p_validate_flag => 'N',
3535 p_action_prms => l_action_prms);
3536
3537 wsh_util_core.api_post_call(
3538 p_return_status => l_return_status,
3539 x_num_warnings => l_num_warnings,
3540 x_num_errors => l_num_errors);
3541 END IF;
3542
3543
3544
3545 IF dd_ids_tab.COUNT > 0 THEN
3546 -- update the delivery details to the appropriate status based on the
3547 -- action code.
3548 IF p_action_prms.action_code = 'CANCEL_PO' THEN
3549 --update the status of the selected lines to 'D' (Cancel).
3550 FORALL i IN dd_ids_tab.FIRST..dd_ids_tab.LAST
3551 UPDATE
3552 wsh_delivery_details
3553 SET
3554 requested_quantity = 0,
3555 requested_quantity2 = 0,
3556 cancelled_quantity = req_qty_tab(i),
3557 cancelled_quantity2 = req_qty2_tab(i),
3558 released_status = 'D',
3559 last_update_date = SYSDATE,
3560 last_updated_by = FND_GLOBAL.USER_ID,
3561 last_update_login = FND_GLOBAL.LOGIN_ID
3562 WHERE
3563 delivery_detail_id = dd_ids_tab(i) AND
3564 released_status = 'X'
3565 RETURNING delivery_detail_id BULK COLLECT INTO l_detail_tab; -- Added for DBI Project
3566
3567 ELSIF p_action_prms.action_code = 'CLOSE_PO' OR
3568 --update the status of the selected lines to 'L' (Close).
3569 p_action_prms.action_code = 'FINAL_CLOSE' OR
3570 p_action_prms.action_code = 'CLOSE_PO_FOR_RECEIVING' THEN
3571 FORALL i IN dd_ids_tab.FIRST..dd_ids_tab.LAST
3572 UPDATE wsh_delivery_details
3573 SET released_status = 'L',
3574 last_update_date = SYSDATE,
3575 last_updated_by = FND_GLOBAL.USER_ID,
3576 last_update_login = FND_GLOBAL.LOGIN_ID
3577 WHERE delivery_detail_id = dd_ids_tab(i) AND
3578 released_status = 'X'
3579 RETURNING delivery_detail_id BULK COLLECT INTO l_detail_tab; -- Added for DBI Project
3580
3581 END IF;
3582 --
3583 -- Above 2 update conditions would populate the table of ids for DBI
3584 -- DBI Project
3585 -- Update of wsh_delivery_details where requested_quantity/released_status
3586 -- are changed, call DBI API after the update.
3587 -- DBI API will check if DBI is installed
3588 IF l_debug_on THEN
3589 WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Detail Count-',l_detail_tab.count);
3590 END IF;
3591 WSH_INTEGRATION.DBI_Update_Detail_Log
3592 (p_delivery_detail_id_tab => l_detail_tab,
3593 p_dml_type => 'UPDATE',
3594 x_return_status => l_dbi_rs);
3595
3596 IF l_debug_on THEN
3597 WSH_DEBUG_SV.log(l_module_name,'Return Status after DBI Call-',l_dbi_rs);
3598 END IF;
3599 -- Only Handle Unexpected error
3600 IF l_dbi_rs = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
3601 x_return_status := l_dbi_rs;
3602 --
3603 IF l_debug_on THEN
3604 WSH_DEBUG_SV.pop(l_module_name);
3605 END IF;
3606 --
3607 RETURN;
3608 END IF;
3609 -- End of Code for DBI Project
3610 --
3611
3612 END IF;
3613
3614 -- Debug Statements
3615 --
3616 IF l_debug_on THEN
3617 WSH_DEBUG_SV.pop(l_module_name);
3618 END IF;
3619 --
3620 IF l_num_errors > 0 THEN
3621 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3622 ELSIF l_num_warnings > 0 THEN
3623 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3624 END IF;
3625
3626
3627 EXCEPTION
3628 WHEN e_wdd_locked THEN
3629 ROLLBACK TO cancel_close_PO_PVT;
3630 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3631 FND_MESSAGE.SET_NAME('WSH','WSH_DELIVERY_LINES_LOCKED');
3632 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status,l_module_name);
3633
3634 --
3635 -- Debug Statements
3636 --
3637 IF l_debug_on THEN
3638 WSH_DEBUG_SV.logmsg(l_module_name,'e_Wdd_locked exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3639 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:e_Wdd_locked');
3640 END IF;
3641 WHEN FND_API.G_EXC_ERROR THEN
3642 ROLLBACK TO cancel_close_PO_PVT;
3643 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3644 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status,l_module_name);
3645
3646 --
3647 -- Debug Statements
3648 --
3649 IF l_debug_on THEN
3650 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3651 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
3652 END IF;
3653 --
3654 WHEN OTHERS THEN
3655 ROLLBACK TO cancel_close_PO_PVT;
3656 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
3657 WSH_UTIL_CORE.Default_Handler('WSH_PO_CMG_PVT.CANCEL_CLOSE_PO',l_module_name);
3658
3659 --
3660 -- Debug Statements
3661 --
3662 IF l_debug_on THEN
3663 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3664 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3665 END IF;
3666 --
3667 END cancel_close_po;
3668
3669 --========================================================================
3670 -- PROCEDURE : reopen_po
3671 -- HISTORY : Created the API.
3672 --========================================================================
3673
3674 -- Start of comments
3675 -- API name : REOPEN_PO
3676 -- Type : Public
3677 -- Pre-reqs : None.
3678 -- Function : This API, changes the status of all the shipment line IDs
3679 -- it receives, from Closed status (L) to Open status 'X'.
3680 -- Parameters :
3681 -- IN:
3682 -- p_line_rec IN OE_WSH_BULK_GRP.line_rec_type
3683 -- contains the information of the lines to be updated.
3684 -- IN OUT:
3685 --
3686 -- OUT:
3687 -- x_return_status OUT NOCOPY VARCHAR2
3688 -- Version : 1.0
3689 -- Previous version 1.0
3690 -- Initial version 1.0
3691 -- End of comments
3692
3693
3694 PROCEDURE reopen_po(
3695 p_line_rec IN OE_WSH_BULK_GRP.line_rec_type,
3696 x_return_status OUT NOCOPY VARCHAR2) IS
3697
3698
3699 l_num_warnings NUMBER;
3700 l_num_errors NUMBER;
3701
3702 l_detail_tab WSH_UTIL_CORE.id_tab_type; -- DBI Project
3703 l_dbi_rs VARCHAR2(1); -- Return Status from DBI API
3704 --
3705 l_debug_on BOOLEAN;
3706 --
3707 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'REOPEN_PO';
3708 --
3709 BEGIN
3710
3711 --
3712 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3713 --
3714 IF l_debug_on IS NULL
3715 THEN
3716 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3717 END IF;
3718 --
3719 --
3720 -- Debug Statements
3721 --
3722 IF l_debug_on THEN
3723 WSH_DEBUG_SV.push(l_module_name);
3724 END IF;
3725 --
3726 SAVEPOINT REOPEN_PO_PVT;
3727
3728 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3729
3730 FORALL i IN p_line_rec.header_id.FIRST..p_line_rec.header_id.LAST
3731 UPDATE wsh_delivery_details
3732 SET released_status = 'X'
3733 WHERE
3734 released_status = 'L' AND
3735 received_quantity IS NULL AND
3736 source_code = 'PO' AND
3737 source_header_id = NVL(p_line_rec.header_id(i),source_header_id) AND
3738 source_line_id = p_line_rec.line_id(i) AND
3739 po_shipment_line_id = p_line_rec.po_shipment_line_id(i) AND
3740 ((p_line_rec.source_blanket_reference_id(i) IS NULL AND source_blanket_reference_id IS NULL)
3741 OR
3742 (p_line_rec.source_blanket_reference_id(i) IS NOT NULL AND source_blanket_reference_id = p_line_rec.source_blanket_reference_id(i) )
3743 )
3744 RETURNING delivery_detail_id BULK COLLECT INTO l_detail_tab; -- Added for DBI Project
3745
3746 --
3747 -- DBI Project
3748 -- Update of wsh_delivery_details where requested_quantity/released_status
3749 -- are changed, call DBI API after the update.
3750 -- DBI API will check if DBI is installed
3751 IF l_debug_on THEN
3752 WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Detail Count-',l_detail_tab.count);
3753 END IF;
3754 WSH_INTEGRATION.DBI_Update_Detail_Log
3755 (p_delivery_detail_id_tab => l_detail_tab,
3756 p_dml_type => 'UPDATE',
3757 x_return_status => l_dbi_rs);
3758
3759 IF l_debug_on THEN
3760 WSH_DEBUG_SV.log(l_module_name,'Return Status after DBI Call-',l_dbi_rs);
3761 END IF;
3762 -- Only Handle Unexpected error
3763 IF l_dbi_rs = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
3764 x_return_status := l_dbi_rs;
3765 --
3766 ROLLBACK TO REOPEN_PO_PVT;
3767 IF l_debug_on THEN
3768 WSH_DEBUG_SV.pop(l_module_name);
3769 END IF;
3770 --
3771 RETURN; -- just pass this return status back to caller API
3772 END IF;
3773 -- End of Code for DBI Project
3774 --
3775 IF l_num_errors > 0 THEN
3776 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3777 ELSIF l_num_warnings > 0 THEN
3778 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3779 END IF;
3780
3781 --
3782 -- Debug Statements
3783 --
3784 IF l_debug_on THEN
3785 WSH_DEBUG_SV.pop(l_module_name);
3786 END IF;
3787 --
3788
3789 EXCEPTION
3790 WHEN OTHERS THEN
3791 ROLLBACK TO REOPEN_PO_PVT;
3792 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
3793 WSH_UTIL_CORE.Default_Handler('WSH_PO_CMG_PVT.Reopen_po',l_module_name);
3794
3795 --
3796 -- Debug Statements
3797 --
3798 IF l_debug_on THEN
3799 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3800 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3801 END IF;
3802 --
3803 END Reopen_po;
3804
3805 --========================================================================
3806 -- PROCEDURE : Log_Exception
3807 -- HISTORY : Created the API.
3808 --========================================================================
3809
3810 -- Start of comments
3811 -- API name : Log_Exception
3812 -- Type : Public
3813 -- Pre-reqs : None.
3814 -- Function : This API is used to log exception against delivery or
3815 -- trip stop.It will check if already any exception is logged
3816 -- against the delivery or the trip and if not, then it will
3817 -- log the exception.
3818 -- Parameters :
3819 -- IN:
3820 -- p_entity_id IN NUMBER
3821 -- p_logging_entity_name IN VARCHAR2
3822 -- p_exception_name IN VARCHAR2
3823 -- p_location_id IN NUMBER DEFAULT NULL
3824 -- p_message IN VARCHAR2 DEFAULT NULL
3825 -- IN OUT:
3826 --
3827 -- OUT:
3828 -- Version : 1.0
3829 -- Previous version 1.0
3830 -- Initial version 1.0
3831 -- End of comments
3832
3833 PROCEDURE Log_Exception(
3834 p_entity_id IN NUMBER,
3835 p_logging_entity_name IN VARCHAR2,
3836 p_exception_name IN VARCHAR2,
3837 p_location_id IN NUMBER DEFAULT NULL,
3838 p_message IN VARCHAR2 DEFAULT NULL,
3839 x_return_status OUT NOCOPY VARCHAR2) IS
3840
3841 l_exception_exists VARCHAR2(1) := 'N';
3842 l_return_status VARCHAR2(30);
3843 l_exception_msg_count NUMBER;
3844 l_dummy_exception_id NUMBER;
3845 l_exception_msg_data VARCHAR2(2000);
3846 l_init_msg_list VARCHAR2(100):= FND_API.G_FALSE;
3847 l_exceptions_tab WSH_EXCEPTIONS_PUB.XC_TAB_TYPE;
3848 l_msg VARCHAR2(2000):= NULL;
3849 l_exception_error_message VARCHAR2(2000) := NULL;
3850 l_logged_at_location_id NUMBER := FND_API.G_MISS_NUM;
3851 l_exception_location_id NUMBER := FND_API.G_MISS_NUM;
3852 l_num_warnings NUMBER := 0;
3853 l_num_errors NUMBER := 0;
3854
3855 --
3856 l_debug_on BOOLEAN;
3857 --
3858 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOG_EXCEPTION';
3859 --
3860 BEGIN
3861
3862 --
3863 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3864 --
3865 IF l_debug_on IS NULL
3866 THEN
3867 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3868 END IF;
3869 --
3870 --
3871 -- Debug Statements
3872 --
3873 IF l_debug_on THEN
3874 WSH_DEBUG_SV.push(l_module_name);
3875 --
3876 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_ID',P_ENTITY_ID);
3877 WSH_DEBUG_SV.log(l_module_name,'P_LOGGING_ENTITY_NAME',P_LOGGING_ENTITY_NAME);
3878 WSH_DEBUG_SV.log(l_module_name,'P_EXCEPTION_NAME',P_EXCEPTION_NAME);
3879 WSH_DEBUG_SV.log(l_module_name,'p_location_id',p_location_id);
3880 WSH_DEBUG_SV.log(l_module_name,'p_message',p_message);
3881 END IF;
3882 --
3883 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3884
3885 IF p_logging_entity_name = 'DELIVERY' THEN
3886 --
3887 -- Debug Statements
3888 --
3889 IF l_debug_on THEN
3890 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_EXCEPTIONS_GRP.GET_EXCEPTIONS',WSH_DEBUG_SV.C_PROC_LEVEL);
3891 END IF;
3892 --
3893 -- anviswan
3894 -- making use of WSH_EXCEPTIONS_GRP.Get_exceptions() instead of WSH_EXCEPTIONS_PUB.Get_exceptions()
3895 WSH_EXCEPTIONS_GRP.Get_exceptions(
3896 p_api_version => 1.0,
3897 p_init_msg_list => l_init_msg_list,
3898 x_return_status => l_return_status,
3899 x_msg_count => l_exception_msg_count,
3900 x_msg_data => l_exception_msg_data,
3901 p_logging_entity_id => p_entity_id,
3902 p_logging_entity_name => p_logging_entity_name,
3903 x_exceptions_tab => l_exceptions_tab
3904 );
3905
3906 wsh_util_core.api_post_call(
3907 p_return_status => l_return_status,
3908 x_num_warnings => l_num_warnings,
3909 x_num_errors => l_num_errors);
3910
3911 ELSIF p_logging_entity_name = 'STOP' THEN
3912 --
3913 -- Debug Statements
3914 --
3915 IF l_debug_on THEN
3916 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_EXCEPTIONS_GRP.GET_EXCEPTIONS',WSH_DEBUG_SV.C_PROC_LEVEL);
3917 END IF;
3918 --
3919 -- anviswan
3920 -- making use of WSH_EXCEPTIONS_GRP.Get_exceptions() instead of WSH_EXCEPTIONS_PUB.Get_exceptions()
3921 WSH_EXCEPTIONS_GRP.Get_exceptions(
3922 p_api_version => 1.0,
3923 x_return_status => l_return_status,
3924 x_msg_count => l_exception_msg_count,
3925 x_msg_data => l_exception_msg_data,
3926 p_logging_entity_id => p_entity_id,
3927 p_logging_entity_name => p_logging_entity_name,
3928 x_exceptions_tab => l_exceptions_tab
3929 );
3930
3931 wsh_util_core.api_post_call(
3932 p_return_status => l_return_status,
3933 x_num_warnings => l_num_warnings,
3934 x_num_errors => l_num_errors);
3935 END IF;
3936
3937
3938 IF p_logging_entity_name <> 'DETAIL' THEN
3939 FOR i IN 1..l_exceptions_tab.COUNT
3940 LOOP
3941 --HACMS {
3942 IF l_debug_on THEN
3943 WSH_DEBUG_SV.log(l_module_name,'exception_name',l_exceptions_tab(i).exception_name);
3944 WSH_DEBUG_SV.log(l_module_name,'status',l_exceptions_tab(i).status);
3945 END IF;
3946 --HACMS }
3947
3948 IF (l_exceptions_tab(i).exception_name = p_exception_name
3949 AND l_exceptions_tab(i).status='OPEN' ) THEN --HACMS
3950 l_exception_exists := 'Y';
3951 EXIT;
3952 END IF;
3953 END LOOP;
3954 END IF;
3955
3956
3957 IF l_debug_on THEN
3958 WSH_DEBUG_SV.log(l_module_name,'l_exception_exists',l_exception_exists);
3959 END IF;
3960
3961
3962 IF (l_exception_exists = 'N') THEN
3963 --{
3964 IF p_logging_entity_name = 'DELIVERY' THEN
3965
3966 -- Bug 3395410 : Commented the following code as p_message is getting populated with the
3967 -- message to be displayed.
3968
3969 /* IF (p_exception_name= 'WSH_IB_DEL_ATT_CHANGE') THEN
3970 l_msg := p_message;
3971 ELSE
3972 FND_MESSAGE.SET_NAME('WSH',p_exception_name);
3973 FND_MESSAGE.SET_TOKEN('DELIVERY', p_entity_id);
3974 l_msg := FND_MESSAGE.GET;
3975 END IF;
3976 */
3977 IF l_debug_on THEN
3978 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_XC_UTIL.LOG_EXCEPTION',WSH_DEBUG_SV.C_PROC_LEVEL);
3979 END IF;
3980 --
3981 wsh_xc_util.log_exception(
3982 p_api_version => 1.0,
3983 x_return_status => l_return_status,
3984 x_msg_count => l_exception_msg_count,
3985 x_msg_data => l_exception_msg_data,
3986 x_exception_id => l_dummy_exception_id ,
3987 p_logging_entity => 'SHIPPER',
3988 p_logging_entity_id => FND_GLOBAL.USER_ID,
3989 p_exception_name => p_exception_name,
3990 p_logged_at_location_id => p_location_id,
3991 p_exception_location_id => p_location_id,
3992 p_message => p_message,
3993 p_delivery_id => p_entity_id,
3994 p_error_message => l_exception_error_message);
3995
3996 wsh_util_core.api_post_call(
3997 p_return_status => l_return_status,
3998 x_num_warnings => l_num_warnings,
3999 x_num_errors => l_num_errors);
4000 ELSIF p_logging_entity_name = 'STOP' THEN
4001 -- Bug 3395410 : Commented the following code as p_message is getting populated with the
4002 -- message to be displayed.
4003 /*
4004 FND_MESSAGE.SET_NAME('WSH',p_exception_name);
4005 FND_MESSAGE.SET_TOKEN('STOP', p_entity_id);
4006 l_msg := FND_MESSAGE.GET;
4007 */
4008 --
4009 -- Debug Statements
4010 --
4011 IF l_debug_on THEN
4012 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_XC_UTIL.LOG_EXCEPTION',WSH_DEBUG_SV.C_PROC_LEVEL);
4013 END IF;
4014 --
4015 wsh_xc_util.log_exception(
4016 p_api_version => 1.0,
4017 x_return_status => l_return_status,
4018 x_msg_count => l_exception_msg_count,
4019 x_msg_data => l_exception_msg_data,
4020 x_exception_id => l_dummy_exception_id ,
4021 p_logging_entity => 'SHIPPER',
4022 p_logging_entity_id => FND_GLOBAL.USER_ID,
4023 p_exception_name => p_exception_name,
4024 p_logged_at_location_id => p_location_id,
4025 p_exception_location_id => p_location_id,
4026 p_message => p_message,
4027 p_trip_stop_id => p_entity_id,
4028 p_error_message => l_exception_error_message);
4029
4030 wsh_util_core.api_post_call(
4031 p_return_status => l_return_status,
4032 x_num_warnings => l_num_warnings,
4033 x_num_errors => l_num_errors);
4034
4035 END IF;--For logging entity name.
4036
4037 END IF;--For exception exists.
4038 --}
4039
4040
4041 --HACMS {
4042 IF p_logging_entity_name = 'DETAIL' THEN
4043 wsh_xc_util.log_exception(
4044 p_api_version => 1.0,
4045 x_return_status => l_return_status,
4046 x_msg_count => l_exception_msg_count,
4047 x_msg_data => l_exception_msg_data,
4048 x_exception_id => l_dummy_exception_id ,
4049 p_logging_entity => 'SHIPPER',
4050 p_logging_entity_id => FND_GLOBAL.USER_ID,
4051 p_exception_name => p_exception_name,
4052 p_logged_at_location_id => p_location_id,
4053 p_exception_location_id => p_location_id,
4054 p_message => p_message,
4055 p_delivery_detail_id => p_entity_id,
4056 p_error_message => l_exception_error_message);
4057
4058 IF l_debug_on THEN
4059 WSH_DEBUG_SV.log(l_module_name,'wsh_xc_util.log_exception l_return_status',l_return_status);
4060 END IF;
4061 wsh_util_core.api_post_call(
4062 p_return_status => l_return_status,
4063 x_num_warnings => l_num_warnings,
4064 x_num_errors => l_num_errors);
4065 IF l_debug_on THEN
4066 WSH_DEBUG_SV.logmsg(l_module_name,'after wsh_util_core.api_post_call');
4067 END IF;
4068 END IF;
4069 --HACMS }
4070
4071 IF l_debug_on THEN
4072 WSH_DEBUG_SV.pop(l_module_name);
4073 END IF;
4074 --
4075 IF l_num_errors > 0 THEN
4076 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4077 ELSIF l_num_warnings > 0 THEN
4078 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4079 END IF;
4080
4081 EXCEPTION
4082 WHEN FND_API.G_EXC_ERROR THEN
4083 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4084 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status,l_module_name);
4085
4086 --
4087 -- Debug Statements
4088 --
4089 IF l_debug_on THEN
4090 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4091 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
4092 END IF;
4093 --
4094 WHEN OTHERS THEN
4095 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
4096 WSH_UTIL_CORE.Default_Handler('WSH_PO_CMG_PVT.LOG_EXCEPTION',l_module_name);
4097
4098 --
4099 -- Debug Statements
4100 --
4101 IF l_debug_on THEN
4102 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4103 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4104 END IF;
4105 --
4106 END Log_Exception;
4107
4108 --========================================================================
4109 -- PROCEDURE : check_pending_txns
4110 -- HISTORY : Created the API.
4111 --========================================================================
4112
4113 -- Start of comments
4114 -- API name : CHECK_PENDING_TXNS
4115 -- Type : Public
4116 -- Pre-reqs : None.
4117 -- Function : This API is a utility API to check if there are pending
4118 -- transactions against the delivery detail line(s).Pending transactions
4119 -- imply
4120 -- 1. An ASN which conatins this delivery detail line is pending to be
4121 -- matched.
4122 -- (or)
4123 -- 2. A Receipt which conatins this delivery detail line is pending to be
4124 -- matched.
4125 -- Parameters :
4126 -- IN:
4127 -- p_header_id IN NUMBER
4128 -- The header Id of the delivery detail line to be checked for pending transactions.
4129 -- p_line_id IN NUMBER
4130 -- The line Id of the delivery detail line to be checked for pending transactions.
4131 -- p_line_location_id IN NUMBER
4132 -- The line location Id of the delivery detail line to be checked for pending transactions.
4133 -- p_release_id IN NUMBER
4134 -- The release Id of the delivery detail line to be checked for pending transactions.
4135 -- IN OUT:
4136 --
4137 -- OUT:
4138 -- Version : 1.0
4139 -- Previous version 1.0
4140 -- Initial version 1.0
4141 -- End of comments
4142
4143
4144 Function check_pending_txns(
4145 p_header_id IN NUMBER,
4146 p_line_id IN NUMBER,
4147 p_line_location_id IN NUMBER,
4148 p_release_id IN NUMBER
4149 ) RETURN NUMBER IS
4150
4151 -- Cursor to find out if there are any unmatched transactions
4152 -- present in WSH_INBOUND_TXN_HISTORY table.
4153
4154 cursor c_pending_txns(p_header_id NUMBER,p_line_id NUMBER,
4155 p_line_location_id NUMBER,p_release_id NUMBER) IS
4156 SELECT '1'
4157 FROM
4158 RCV_FTE_LINES_V RFLV,
4159 WSH_INBOUND_TXN_HISTORY WTH
4160 WHERE RFLV.PO_HEADER_ID = p_header_id AND
4161 RFLV.SHIPMENT_HEADER_ID = WTH.SHIPMENT_HEADER_ID AND
4162 RFLV.PO_LINE_ID = NVL(p_line_id,RFLV.PO_LINE_ID) AND
4163 RFLV.PO_LINE_LOCATION_ID= NVL(p_line_location_id,RFLV.PO_LINE_LOCATION_ID) AND
4164 (
4165 p_release_id IS NULL AND RFLV.po_release_id IS NULL
4166 OR
4167 RFLV.PO_RELEASE_ID = p_release_id
4168 )
4169 AND WTH.STATUS NOT IN ('MATCHED','CANCELLED')
4170 AND WTH.TRANSACTION_TYPE NOT IN ('ROUTING_REQUEST','ROUTING_RESPONSE');
4171
4172 l_pending_txns NUMBER := 0;
4173
4174 --
4175 l_debug_on BOOLEAN;
4176 --
4177 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CHECK_PENDING_TXNS';
4178 --
4179 BEGIN
4180
4181
4182 --
4183 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4184 --
4185 IF l_debug_on IS NULL
4186 THEN
4187 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4188 END IF;
4189 --
4190 --
4191 -- Debug Statements
4192 --
4193 IF l_debug_on THEN
4194 WSH_DEBUG_SV.push(l_module_name);
4195 --
4196 WSH_DEBUG_SV.log(l_module_name,'P_HEADER_ID',P_HEADER_ID);
4197 WSH_DEBUG_SV.log(l_module_name,'P_LINE_ID',P_LINE_ID);
4198 WSH_DEBUG_SV.log(l_module_name,'P_LINE_LOCATION_ID',P_LINE_LOCATION_ID);
4199 WSH_DEBUG_SV.log(l_module_name,'P_RELEASE_ID',P_RELEASE_ID);
4200 END IF;
4201 --
4202 OPEN c_pending_txns(p_header_id,p_line_id ,p_line_location_id ,p_release_id);
4203 FETCH c_pending_txns INTO l_pending_txns;
4204
4205 IF c_pending_txns%NOTFOUND THEN
4206
4207 --
4208 -- Debug Statements
4209 --
4210 IF l_debug_on THEN
4211 WSH_DEBUG_SV.pop(l_module_name);
4212 END IF;
4213 --
4214 RETURN 0;--No Pending Transactions.
4215
4216 ELSE
4217
4218 --
4219 -- Debug Statements
4220 --
4221 IF l_debug_on THEN
4222 WSH_DEBUG_SV.pop(l_module_name);
4223 END IF;
4224 --
4225 RETURN 1;--Transactions are Pending to be Matched.
4226
4227 END IF;
4228 CLOSE c_pending_txns;
4229
4230
4231 EXCEPTION
4232 WHEN OTHERS THEN
4233 WSH_UTIL_CORE.Default_Handler('WSH_PO_CMG_PVT.check_pending_txns',l_module_name);
4234 --
4235 -- Debug Statements
4236 --
4237 IF l_debug_on THEN
4238 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4239 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4240 END IF;
4241 --
4242 END check_pending_txns;
4243
4244 /*========================================================================
4245 -- PROCEDURE : populate_additional_line_info
4246 -- HISTORY : Created the API.
4247 --========================================================================*/
4248
4249 -- Start of comments
4250 -- API name : populate_additional_line_info
4251 -- Type : Private
4252 -- Pre-reqs : None.
4253 -- Function : This API is used to populate the p_additional_line_info record
4254 -- structure.Once this record structure is populated, during insert
4255 -- into wsh_delivery_details, the Bulk_insert API can me made use
4256 -- of .
4257 -- Parameters :
4258 -- IN OUT:
4259 -- p_line_rec IN OE_WSH_BULK_GRP.line_rec_type DEFAULT NULL
4260 -- This record structure contains information about the lines to be updated.
4261 -- The information may include header id,line id ,line location id and other po
4262 -- attributes..
4263 --IN:
4264 -- p_index IN NUMBER
4265 -- This stores the index of the p_line_rec that is being passed.
4266 -- p_caller IN VARCHAR2
4267 -- This variable specified if the call to populate_additional_line_info is being made from PO reapproval or ASN/Receipt.
4268 -- If the caller value is 'WSH-PO-INT', then the call is being made from PO Re-approval.
4269 -- OUT:
4270 -- p_additional_line_info_rec OUT NOCOPY WSH_BULK_PROCESS_PVT.additional_line_info_rec_type
4271 -- This record structure contains all the attributes which are derived in this API.
4272 -- x_return_status OUT NOCOPY VARCHAR2
4273 -- Version : 1.0
4274 -- Previous version 1.0
4275 -- Initial version 1.0
4276 -- End of comments
4277
4278 Procedure populate_additional_line_info(
4279 p_line_rec IN OUT NOCOPY OE_WSH_BULK_GRP.line_rec_type,
4280 p_index IN NUMBER,
4281 p_caller IN VARCHAR2,
4282 p_additional_line_info_rec OUT NOCOPY WSH_BULK_PROCESS_PVT.additional_line_info_rec_type ,
4283 x_return_status OUT NOCOPY VARCHAR2) IS
4284
4285 l_dff_attribute WSH_FLEXFIELD_UTILS.FlexfieldAttributeTabType ;
4286 l_dff_context VARCHAR2(150);
4287 l_dff_update_flag VARCHAR2(1);
4288 l_return_status VARCHAR2(1);
4289 i number := p_index;
4290 l_num_warnings NUMBER := 0;
4291 l_num_errors NUMBER := 0;
4292 v_item_info_rec wsh_util_validate.item_info_rec_type;
4293 l_vendor_party_exists VARCHAR2(1);
4294 l_index NUMBER;
4295 l_cache_tbl wsh_util_core.char500_tab_type;
4296 l_cache_ext_tbl wsh_util_core.char500_tab_type;
4297 --
4298 l_debug_on BOOLEAN;
4299 --
4300 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'POPULATE_ADDITIONAL_LINE_INFO';
4301 --
4302 BEGIN
4303
4304 --
4305 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4306 --
4307 IF l_debug_on IS NULL
4308 THEN
4309 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4310 END IF;
4311 --
4312 --
4313 -- Debug Statements
4314 --
4315 IF l_debug_on THEN
4316 WSH_DEBUG_SV.push(l_module_name);
4317 --
4318 WSH_DEBUG_SV.log(l_module_name,'P_INDEX',P_INDEX);
4319 END IF;
4320 --
4321 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4322
4323 --
4324 -- Debug Statements
4325 --
4326 IF l_debug_on THEN
4327 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_FLEXFIELD_UTILS.GET_DFF_DEFAULTS',WSH_DEBUG_SV.C_PROC_LEVEL);
4328 END IF;
4329 --
4330 WSH_FLEXFIELD_UTILS.Get_DFF_Defaults
4331 (p_flexfield_name => 'WSH_DELIVERY_DETAILS',
4332 p_default_values => l_dff_attribute,
4333 p_default_context => l_dff_context,
4334 p_update_flag => l_dff_update_flag,
4335 x_return_status => l_return_status);
4336
4337
4338 --
4339 -- Debug Statements
4340 --
4341 IF l_debug_on THEN
4342 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
4343 END IF;
4344 --
4345 Wsh_util_core.api_post_call(
4346 p_return_status => x_return_status,
4347 x_num_warnings => l_num_warnings,
4348 x_num_errors => l_num_errors);
4349
4350 l_index := p_index;
4351
4352 p_additional_line_info_rec.service_level.EXTEND(p_index);
4353 p_additional_line_info_rec.mode_of_transport.EXTEND(p_index);
4354 p_additional_line_info_rec.earliest_pickup_date.EXTEND(p_index);
4355 p_additional_line_info_rec.latest_pickup_date.EXTEND(p_index);
4356 p_additional_line_info_rec.earliest_dropoff_date.EXTEND(p_index);
4357 p_additional_line_info_rec.latest_dropoff_date.EXTEND(p_index);
4358 p_additional_line_info_rec.ignore_for_planning.EXTEND(p_index);
4359 p_additional_line_info_rec.cancelled_quantity2.EXTEND(p_index);
4360 p_additional_line_info_rec.released_status.EXTEND(p_index);
4361 p_additional_line_info_rec.inv_interfaced_flag.EXTEND(p_index);
4362 p_additional_line_info_rec.cancelled_quantity.EXTEND(p_index);
4363 p_additional_line_info_rec.master_container_item_id.EXTEND(p_index);
4364 p_additional_line_info_rec.detail_container_item_id.EXTEND(p_index);
4365 p_additional_line_info_rec.attribute_category.EXTEND(p_index);
4366 p_additional_line_info_rec.attribute1.EXTEND(p_index);
4367 p_additional_line_info_rec.attribute2.EXTEND(p_index);
4368 p_additional_line_info_rec.attribute3.EXTEND(p_index);
4369 p_additional_line_info_rec.attribute4.EXTEND(p_index);
4370 p_additional_line_info_rec.attribute5.EXTEND(p_index);
4371 p_additional_line_info_rec.attribute6.EXTEND(p_index);
4372 p_additional_line_info_rec.attribute7.EXTEND(p_index);
4373 p_additional_line_info_rec.attribute8.EXTEND(p_index);
4374 p_additional_line_info_rec.attribute9.EXTEND(p_index);
4375 p_additional_line_info_rec.attribute10.EXTEND(p_index);
4376 p_additional_line_info_rec.attribute11.EXTEND(p_index);
4377 p_additional_line_info_rec.attribute12.EXTEND(p_index);
4378 p_additional_line_info_rec.attribute13.EXTEND(p_index);
4379 p_additional_line_info_rec.attribute14.EXTEND(p_index);
4380 p_additional_line_info_rec.attribute15.EXTEND(p_index);
4381 p_line_rec.vendor_party_id.EXTEND(p_index);
4382
4383 p_additional_line_info_rec.source_code := 'PO';
4384 p_additional_line_info_rec.service_level(p_index):= NULL;
4385 p_additional_line_info_rec.mode_of_transport(p_index):= NULL;
4386 p_additional_line_info_rec.earliest_pickup_date(p_index):= NULL;
4387 p_additional_line_info_rec.latest_pickup_date(p_index):= NULL;
4388 p_additional_line_info_rec.earliest_dropoff_date(i):=
4389 (NVL(p_line_rec.schedule_ship_date(p_index),p_line_rec.request_date(p_index)) -
4390 NVL(p_line_rec.Days_early_receipt_allowed(p_index),0));
4391 p_additional_line_info_rec.latest_dropoff_date(p_index) :=
4392 (NVL(p_line_rec.schedule_ship_date(p_index),p_line_rec.request_date(p_index)) +
4393 NVL(p_line_rec.Days_late_receipt_allowed(p_index),0));
4394 p_additional_line_info_rec.ignore_for_planning(p_index):= 'Y';
4395 p_additional_line_info_rec.cancelled_quantity2(p_index):= NULL;
4396 p_additional_line_info_rec.released_status(p_index):= 'X';
4397 p_additional_line_info_rec.inv_interfaced_flag(p_index):= 'X';
4398 p_additional_line_info_rec.cancelled_quantity(p_index):= NULL;
4399 p_additional_line_info_rec.master_container_item_id(p_index):= NULL;
4400 p_additional_line_info_rec.detail_container_item_id(p_index):= NULL;
4401 p_line_rec.shipping_eligible_flag(p_index) := 'Y';
4402 p_line_rec.shipping_interfaced_flag(p_index) := 'Y';
4403
4404 p_line_rec.shipped_quantity(p_index) := NULL;
4405 p_line_rec.received_quantity(p_index) := NULL;
4406
4407 p_line_rec.shipped_quantity2(p_index) := NULL;
4408 p_line_rec.received_quantity2(p_index) := NULL;
4409
4410 IF l_debug_on THEN
4411 WSH_DEBUG_SV.log(l_module_name,'p_caller is :',p_caller);
4412 END IF;
4413
4414 IF nvl(p_caller,'!!!') <> 'WSH-PO-INT' THEN
4415 -- Need to calculate the wt and volume before inserting record into wdd.
4416 -- This is done only if the call is being made from ASN/Receipt.
4417
4418 IF l_debug_on THEN
4419 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_ASN_RECEIPT_PVT.Calculate_Wt_Vol',WSH_DEBUG_SV.C_PROC_LEVEL);
4420 END IF;
4421
4422 Calculate_Wt_Vol(p_line_rec => p_line_rec,
4423 p_index => l_index,
4424 x_return_status => l_return_status);
4425
4426 IF l_debug_on THEN
4427 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
4428 END IF;
4429
4430 wsh_util_core.api_post_call(
4431 p_return_status => l_return_status,
4432 x_num_warnings => l_num_warnings,
4433 x_num_errors => l_num_errors);
4434
4435 END IF;
4436
4437 -- Will Check it out with Nikhil once again and if not needed,can remove it.
4438 /*IF p_line_rec.request_date(p_index) IS NULL THEN
4439 P_line_rec.request_date(p_index) := p_line_rec.schedule_ship_date(p_index);
4440 END IF;*/
4441
4442 IF l_debug_on THEN
4443 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_SUPPLIER_PARTY.WSH_SUPPLIER_PARTY',WSH_DEBUG_SV.C_PROC_LEVEL);
4444 END IF;
4445 l_vendor_party_exists := WSH_SUPPLIER_PARTY.VENDOR_PARTY_EXISTS(
4446 p_vendor_id => p_line_rec.vendor_id(p_line_rec.vendor_id.FIRST),
4447 x_party_id => p_line_rec.vendor_party_id(p_index));
4448
4449 IF l_debug_on THEN
4450 WSH_DEBUG_SV.logmsg(l_module_name,'After calling VENDOR_PARTY_EXISTS');
4451 WSH_DEBUG_SV.log(l_module_name,'l_vendor_party_exists', l_vendor_party_exists);
4452 END IF;
4453
4454 IF l_vendor_party_exists = 'N' THEN
4455 -- { IB-Phase-2
4456 --If party not exists, throw an error message and return.
4457 IF l_debug_on THEN
4458 WSH_DEBUG_SV.log(l_module_name,'Party does not Exist for the Vendor ');
4459 END IF;
4460 raise fnd_api.g_exc_error;
4461 -- } IB-Phase-2
4462 END IF;
4463
4464 IF p_line_rec.drop_ship_flag(p_index) = 'Y' AND p_line_rec.shipping_method_code(p_index) is NOT NULL THEN
4465 --{
4466 -- Need to derive the carrier,service and mode from the ship method code for drop ship order.
4467 IF l_debug_on THEN
4468 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_BULK_PROCESS_PVT.calc_service_mode',WSH_DEBUG_SV.C_PROC_LEVEL);
4469 END IF;
4470 WSH_BULK_PROCESS_PVT.calc_service_mode(
4471 p_line_rec => p_line_rec,
4472 p_cache_tbl => l_cache_tbl,
4473 p_cache_ext_tbl => l_cache_ext_tbl,
4474 p_index => i,
4475 p_additional_line_info_rec => p_additional_line_info_rec,
4476 x_return_status => l_return_status);
4477
4478 wsh_util_core.api_post_call(
4479 p_return_status => l_return_status,
4480 x_num_warnings => l_num_warnings,
4481 x_num_errors => l_num_errors);
4482 --}
4483 END IF;
4484
4485 IF (l_dff_update_flag = 'Y') THEN
4486 --{
4487 --
4488 -- Debug Statements
4489 --
4490 IF l_debug_on THEN
4491 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_ACTIONS_LEVELS.G_VALIDATION_LEVEL_TAB',WSH_DEBUG_SV.C_PROC_LEVEL);
4492 END IF;
4493 --
4494 --IF WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_PO_DEFAULT_FLEX_LVL ) = 1
4495 --THEN
4496 --{
4497 p_additional_line_info_rec.attribute_category(p_index) := l_dff_context ;
4498 p_additional_line_info_rec.attribute1(p_index) :=l_dff_attribute(1);
4499 p_additional_line_info_rec.attribute2(p_index) :=l_dff_attribute(2);
4500 p_additional_line_info_rec.attribute3(p_index) :=l_dff_attribute(3);
4501 p_additional_line_info_rec.attribute4(p_index) :=l_dff_attribute(4);
4502 p_additional_line_info_rec.attribute5(p_index) :=l_dff_attribute(5);
4503 p_additional_line_info_rec.attribute6(p_index) :=l_dff_attribute(6);
4504 p_additional_line_info_rec.attribute7(p_index) :=l_dff_attribute(7);
4505 p_additional_line_info_rec.attribute8(p_index) :=l_dff_attribute(8);
4506 p_additional_line_info_rec.attribute9(p_index) :=l_dff_attribute(9);
4507 p_additional_line_info_rec.attribute10(p_index) :=l_dff_attribute(10);
4508 p_additional_line_info_rec.attribute11(p_index) := l_dff_attribute(11);
4509 p_additional_line_info_rec.attribute12(p_index) :=l_dff_attribute( 12);
4510 p_additional_line_info_rec.attribute13(p_index) :=l_dff_attribute(13);
4511 p_additional_line_info_rec.attribute14(p_index) :=l_dff_attribute(14);
4512 p_additional_line_info_rec.attribute15(p_index) :=l_dff_attribute(15);
4513
4514 ELSE --}{
4515 p_additional_line_info_rec.attribute_category(p_index) :=
4516 nvl(p_line_rec.context(i),l_dff_context ) ;
4517 p_additional_line_info_rec.attribute1(p_index) :=
4518 nvl(p_line_rec.attribute1(i),l_dff_attribute(1));
4519 p_additional_line_info_rec.attribute2(p_index) :=
4520 nvl(p_line_rec.attribute2(i),l_dff_attribute(2) );
4521 p_additional_line_info_rec.attribute3(p_index) :=
4522 nvl(p_line_rec.attribute3(i), l_dff_attribute( 3) );
4523 p_additional_line_info_rec.attribute4(p_index) :=
4524 nvl(p_line_rec.attribute4(i), l_dff_attribute( 4) );
4525 p_additional_line_info_rec.attribute5(p_index) :=
4526 nvl(p_line_rec.attribute5(i), l_dff_attribute( 5) );
4527 p_additional_line_info_rec.attribute6(p_index) :=
4528 nvl(p_line_rec.attribute6(i), l_dff_attribute( 6) );
4529 p_additional_line_info_rec.attribute7(p_index) :=
4530 nvl(p_line_rec.attribute7(i), l_dff_attribute( 7) );
4531 p_additional_line_info_rec.attribute8(p_index) :=
4532 nvl(p_line_rec.attribute8(i), l_dff_attribute( 8) );
4533 p_additional_line_info_rec.attribute9(p_index) :=
4534 nvl(p_line_rec.attribute9(i), l_dff_attribute( 9) );
4535 p_additional_line_info_rec.attribute10(p_index) :=
4536 nvl(p_line_rec.attribute10(i), l_dff_attribute( 10));
4537 p_additional_line_info_rec.attribute11(p_index) :=
4538 nvl(p_line_rec.attribute11(i), l_dff_attribute( 11));
4539 p_additional_line_info_rec.attribute12(p_index) :=
4540 nvl(p_line_rec.attribute12(i), l_dff_attribute( 12));
4541 p_additional_line_info_rec.attribute13(p_index) :=
4542 nvl(p_line_rec.attribute13(i), l_dff_attribute( 13));
4543 p_additional_line_info_rec.attribute14(p_index) :=
4544 nvl(p_line_rec.attribute14(i), l_dff_attribute( 14));
4545 p_additional_line_info_rec.attribute15(p_index) :=
4546 nvl(p_line_rec.attribute15(i), l_dff_attribute( 15));
4547
4548 --END IF; --}
4549 END IF; --}
4550
4551 --
4552 -- Debug Statements
4553 --
4554 IF l_debug_on THEN
4555 WSH_DEBUG_SV.pop(l_module_name);
4556 END IF;
4557 --
4558 IF l_num_errors > 0 THEN
4559 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4560 ELSIF l_num_warnings > 0 THEN
4561 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4562 END IF;
4563
4564
4565 EXCEPTION
4566 WHEN FND_API.G_EXC_ERROR THEN
4567 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4568 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status,l_module_name);
4569 --
4570 -- Debug Statements
4571 --
4572 IF l_debug_on THEN
4573 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4574 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
4575 END IF;
4576 --
4577 WHEN OTHERS THEN
4578 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
4579 WSH_UTIL_CORE.DEFAULT_HANDLER('WSH_PO_CMG_PVT.populate_additional_line_info',l_module_name);
4580
4581 --
4582 -- Debug Statements
4583 --
4584 IF l_debug_on THEN
4585 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4586 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4587 END IF;
4588 --
4589 END populate_additional_line_info;
4590
4591 --==============================================================================
4592 -- PROCEDURE : Calculate_Wt_Vol
4593 -- HISTORY : Created the API.
4594 --==============================================================================
4595 -- Start of comments
4596 -- API name : Calculate_Wt_Vol
4597 -- Type : Private
4598 -- Pre-reqs : None.
4599 -- Function : This API is used to dervie the wt/volume attributes of p_line_Rec
4600 -- while processing an ASN or RECEIPT
4601 -- Parameters :
4602 -- IN OUT:
4603 -- p_line_rec IN OE_WSH_BULK_GRP.line_rec_type DEFAULT NULL
4604 -- This record structure contains information about the lines to be updated.
4605 -- The information may include header id,line id ,line location id and other po
4606 -- attributes..
4607 -- p_index IN OUT NOCOPY NUMBER
4608 -- This stores the index of the p_line_rec that is being passed.
4609 -- OUT:
4610 -- x_return_status OUT NOCOPY VARCHAR2
4611 -- Version : 1.0
4612 -- Previous version 1.0
4613 -- Initial version 1.0
4614 -- End of comments
4615
4616 Procedure Calculate_Wt_Vol(p_line_rec IN OUT NOCOPY OE_WSH_BULK_GRP.line_rec_type,
4617 p_index IN OUT NOCOPY NUMBER,
4618 x_return_status OUT NOCOPY VARCHAR2) IS
4619
4620
4621 l_item_info_rec wsh_util_validate.item_info_rec_type;
4622 l_return_status VARCHAR2(1);
4623 l_num_warnings NUMBER := 0;
4624 l_num_errors NUMBER := 0;
4625 --
4626 l_debug_on BOOLEAN;
4627 --
4628 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Calculate_Wt_Vo';
4629 --
4630
4631 BEGIN
4632
4633 x_return_status := wsh_util_core.g_ret_sts_success;
4634 --
4635 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4636 --
4637 IF l_debug_on IS NULL
4638 THEN
4639 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4640 END IF;
4641 --
4642 --
4643 -- Debug Statements
4644 --
4645 IF l_debug_on THEN
4646 WSH_DEBUG_SV.push(l_module_name);
4647 END IF;
4648 --
4649
4650 IF (p_line_rec.inventory_item_id(p_index) is NOT NULL) THEN
4651 --{
4652 -- Need to calculate wt/volume only if inventroy item id is not null.
4653 IF p_line_rec.weight_uom_code(p_index) IS NULL OR
4654 p_line_rec.volume_uom_code(p_index) IS NULL
4655 THEN --{
4656 IF l_debug_on THEN
4657 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit wsh_util_validate.get_item_info',WSH_DEBUG_SV.C_PROC_LEVEL);
4658 END IF;
4659
4660 wsh_util_validate.get_item_info(
4661 p_organization_id => p_line_rec.organization_id(p_index),
4662 p_inventory_item_id => p_line_rec.inventory_item_id(p_index),
4663 x_Item_info_rec => l_item_info_rec,
4664 x_return_status => l_return_status);
4665
4666 wsh_util_core.api_post_call(
4667 p_return_status => l_return_status,
4668 x_num_warnings => l_num_warnings,
4669 x_num_errors => l_num_errors);
4670
4671 p_line_rec.weight_uom_code(p_index) := NVL(p_line_rec.weight_uom_code(p_index), l_item_info_rec.weight_uom_code);
4672 p_line_rec.volume_uom_code(p_index) := NVL(p_line_rec.volume_uom_code(p_index), l_item_info_rec.volume_uom_code);
4673 p_line_rec.mtl_unit_weight(p_index) := NVL(p_line_rec.mtl_unit_weight(p_index), l_item_info_rec.unit_weight);
4674 p_line_rec.mtl_unit_volume(p_index) := NVL(p_line_rec.mtl_unit_volume(p_index), l_item_info_rec.unit_volume);
4675
4676 p_line_rec.net_weight(p_index) := p_line_rec.mtl_unit_weight(p_index)* p_line_rec.requested_quantity(p_index);
4677 p_line_rec.volume(p_index) := p_line_rec.mtl_unit_volume(p_index) * p_line_rec.requested_quantity(p_index);
4678 p_line_rec.gross_weight(p_index) := p_line_rec.net_weight(p_index);
4679
4680 IF l_debug_on THEN
4681 WSH_DEBUG_SV.log(l_module_name,'p_line_rec wt uom code is :',p_line_rec.weight_uom_code(p_index));
4682 WSH_DEBUG_SV.log(l_module_name,'p_line_rec vol uom code is :',p_line_rec.volume_uom_code(p_index));
4683 WSH_DEBUG_SV.log(l_module_name,'p_line_rec unit wt is :',p_line_rec.mtl_unit_weight(p_index));
4684 WSH_DEBUG_SV.log(l_module_name,'p_line_rec unit vol is :',p_line_rec.mtl_unit_volume(p_index));
4685 WSH_DEBUG_SV.log(l_module_name,'p_line_rec net wt is : ',p_line_rec.net_weight(p_index));
4686 WSH_DEBUG_SV.log(l_module_name,'p_line_rec volume is :',p_line_rec.volume(p_index));
4687 WSH_DEBUG_SV.log(l_module_name,'p_line_rec gross wt is :',p_line_rec.gross_weight(p_index));
4688 END If;
4689 END IF;--}
4690 END IF;--}
4691
4692 EXCEPTION
4693 WHEN FND_API.G_EXC_ERROR THEN
4694 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4695 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status,l_module_name);
4696
4697 --
4698 -- Debug Statements
4699 --
4700 IF l_debug_on THEN
4701 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4702 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
4703 END IF;
4704 --
4705 WHEN OTHERS THEN
4706 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
4707 WSH_UTIL_CORE.Default_Handler('WSH_PO_CMG_PVT.Calculate_Wt_Vol');
4708
4709 --
4710 -- Debug Statements
4711 --
4712 IF l_debug_on THEN
4713 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4714 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4715 END IF;
4716 --
4717 END Calculate_Wt_Vol;
4718
4719 END WSH_PO_CMG_PVT;
[Home] [Help]