1 PACKAGE BODY WSH_INTEGRATION as
2 /* $Header: WSHINTGB.pls 120.14 2011/03/23 10:52:11 ashimalh ship $ */
3
4 -- Global constant holding the package name
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'WSH_INTEGRATION';
6
7 -- Global variable holding transaction_id
8 --Bug#5104847:Assigning default value FND_API.G_MISS_NUM to trx_id and trx_temp_id as
9 -- WMS is not calling the API WSH_INTEGRATION.Set_Inv_PC_Attributes to set trx ids.
10 TYPE InvPCRecType IS RECORD
11 ( transaction_id NUMBER DEFAULT FND_API.G_MISS_NUM,
12 transaction_temp_id NUMBER DEFAULT FND_API.G_MISS_NUM);
13
14
15 G_InvPCRec InvPCRecType;
16
17 -- DBI Project,11.5.10+
18 -- Global Variable indicating if DBI is installed or not
19 G_DBI_IS_INSTALLED VARCHAR2(1) := NULL;
20
21
22 PROCEDURE Get_Min_Max_Tolerance_Quantity
23 ( p_in_attributes IN MinMaxInRecType,
24 p_out_attributes OUT NOCOPY MinMaxOutRecType,
25 p_inout_attributes IN OUT NOCOPY MinMaxInOutRecType,
26 x_return_status OUT NOCOPY VARCHAR2,
27 x_msg_count OUT NOCOPY NUMBER,
28 x_msg_data OUT NOCOPY VARCHAR2
29 )
30 IS
31 l_minmaxinrectype WSH_DETAILS_VALIDATIONS.MinMaxInRecType;
32 l_minmaxinoutrectype WSH_DETAILS_VALIDATIONS.MinMaxInOutRecType;
33 l_minmaxoutrectype WSH_DETAILS_VALIDATIONS.MinMaxOutRecType;
34
35 --
36 l_debug_on BOOLEAN;
37 --
38 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_MIN_MAX_TOLERANCE_QUANTITY';
39 --
40 BEGIN
41 --
42 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
43 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
44 --
45 IF l_debug_on IS NULL
46 THEN
47 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
48 END IF;
49 --
50 IF l_debug_on THEN
51 WSH_DEBUG_SV.push(l_module_name);
52 WSH_DEBUG_SV.log(l_module_name,'api_version_number',p_in_attributes.api_version_number);
53 WSH_DEBUG_SV.log(l_module_name,'source_code',p_in_attributes.source_code);
54 WSH_DEBUG_SV.log(l_module_name,'line_id',p_in_attributes.line_id);
55 WSH_DEBUG_SV.log(l_module_name,'dummy_quantity',p_inout_attributes.dummy_quantity);
56 END IF;
57
58 IF ( p_in_attributes.source_code IS NULL )
59 THEN
60 x_msg_count := 1;
61 x_msg_data := 'INVALID SOURCE_CODE';
62 x_return_status := FND_API.G_RET_STS_ERROR;
63 END IF;
64
65 IF l_debug_on THEN
66 WSH_DEBUG_SV.logmsg(l_module_name, 'Calling WSH_DETAILS_VALIDATIONS.Get_Min_Max_Tolerance_Quantity');
67 END IF;
68
69 l_minmaxinrectype.api_version_number := NVL(p_in_attributes.api_version_number, 1.0);
70 l_minmaxinrectype.source_code := p_in_attributes.source_code;
71 l_minmaxinrectype.line_id := p_in_attributes.line_id;
72 l_minmaxinrectype.action_flag := 'C';
73 l_minmaxinoutrectype.dummy_quantity := p_inout_attributes.dummy_quantity;
74
75 WSH_DETAILS_VALIDATIONS.get_min_max_tolerance_quantity
76 (p_in_attributes => l_minmaxinrectype,
77 x_out_attributes => l_minmaxoutrectype,
78 p_inout_attributes => l_minmaxinoutrectype,
79 x_return_status => x_return_status,
80 x_msg_count => x_msg_count,
81 x_msg_data => x_msg_data
82 );
83
84 IF l_debug_on THEN
85 WSH_DEBUG_SV.log(l_module_name,'Return status from WSH_DETAILS_VALIDATIONS.get_min_max_tolerance_quantity',x_return_status);
86 END IF;
87
88 IF x_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ) THEN
89 IF l_debug_on THEN
90 WSH_DEBUG_SV.pop(l_module_name);
91 END IF;
92 return;
93 END IF;
94
95 p_inout_attributes.dummy_quantity := l_minmaxinoutrectype.dummy_quantity;
96 p_out_attributes.quantity_uom := l_minmaxoutrectype.quantity_uom;
97 p_out_attributes.min_remaining_quantity := l_minmaxoutrectype.min_remaining_quantity;
98 p_out_attributes.max_remaining_quantity := l_minmaxoutrectype.max_remaining_quantity;
99 p_out_attributes.quantity2_uom := l_minmaxoutrectype.quantity2_uom;
100 p_out_attributes.min_remaining_quantity2 := l_minmaxoutrectype.min_remaining_quantity2;
101 p_out_attributes.max_remaining_quantity2 := l_minmaxoutrectype.max_remaining_quantity2;
102
103 IF l_debug_on THEN
104 WSH_DEBUG_SV.log(l_module_name,'p_inout_attributes.dummy_quantity', p_inout_attributes.dummy_quantity);
105 WSH_DEBUG_SV.log(l_module_name,'p_out_attributes.quantity_uom',p_out_attributes.quantity_uom);
106 WSH_DEBUG_SV.log(l_module_name,'p_out_attributes.min_remaining_quantity',p_out_attributes.min_remaining_quantity);
107 WSH_DEBUG_SV.log(l_module_name,'p_out_attributes.max_remaining_quantity',p_out_attributes.max_remaining_quantity);
108 WSH_DEBUG_SV.log(l_module_name,'p_out_attributes.quantity2_uom',p_out_attributes.quantity2_uom);
109 WSH_DEBUG_SV.log(l_module_name,'p_out_attributes.min_remaining_quantity2',p_out_attributes.min_remaining_quantity2);
110 WSH_DEBUG_SV.log(l_module_name,'p_out_attributes.max_remaining_quantity2',p_out_attributes.max_remaining_quantity2);
111 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
112 WSH_DEBUG_SV.log(l_module_name,'x_msg_count',x_msg_count);
113 WSH_DEBUG_SV.log(l_module_name,'x_msg_data',x_msg_data);
114 END IF;
115
116 IF l_debug_on THEN
117 WSH_DEBUG_SV.pop(l_module_name);
118 END IF;
119
120 EXCEPTION
121 WHEN OTHERS THEN
122 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
123
124 FND_MSG_PUB.Count_And_Get
125 ( p_count => x_msg_count
126 , p_data => x_msg_data
127 ,p_encoded => FND_API.G_FALSE
128 );
129
130 IF l_debug_on THEN
131 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
132 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
133 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
134 END IF;
135
136 END Get_Min_Max_Tolerance_Quantity;
137
138
139 -- PROCEDURE GET_UNTRXD_SHPG_LINES_COUNT
140 --
141 -- Purpose : To Get the total Number of Untransacted Shipping Lines remaining ( which
142 -- are Shipped, and Delivery is in CLOSED or IN-TRANSIT status) for a
143 -- given set of dates (From Dt. and To Dt.).
144 -- Untransacted implies all delivery details in Shpg. which are in Shipped Status,
145 -- but are either not Inventory Interfaced or are pending Inv. Interface.
146 -- This is intended for a given Organization_id
147 --
148 -- Input : p_in_attributes.closing_fm_date -> (usuall INV) PERIOD Closing From date;
149 -- p_in_attributes.closing_to_date -> (usuall INV) PERIOD Closing To date;
150 -- p_in_attributes.organization_id -> Inventory Warehouse/Organization id;
151 --
152 -- Output : p_out_attributes.untrxd_rec_count -> Total Number of Untransacted (Shipped) Delivery Dtls.
153 -- p_out_attributes.receiving_rec_count -> Total Number of Untransacted (Shipped) Delivery Dtls
154 -- that are Receiving (incoming) - Direct Shipment or Intransit to Expense destinations.
155
156
157 PROCEDURE Get_Untrxd_Shpg_Lines_Count
158 ( p_in_attributes IN ShpgUnTrxdInRecType,
159 p_out_attributes OUT NOCOPY ShpgUnTrxdOutRecType,
160 p_inout_attributes IN OUT NOCOPY ShpgUnTrxdInOutRecType,
161 x_return_status OUT NOCOPY VARCHAR2,
162 x_msg_count OUT NOCOPY NUMBER,
163 x_msg_data OUT NOCOPY VARCHAR2
164 )
165 IS
166 l_untrxd_rec_count NUMBER;
167 l_rec_exp_count NUMBER;
168 l_rec_direct_count NUMBER;
169 l_closing_fm_date DATE;
170 l_closing_to_date DATE;
171 l_organization_id NUMBER;
172 --
173 l_debug_on BOOLEAN;
174 --
175 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_UNTRXD_SHPG_LINES_COUNT';
176 --
177 BEGIN
178 --
179 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
180 --
181 IF l_debug_on IS NULL
182 THEN
183 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
184 END IF;
185 --
186 IF l_debug_on THEN
187 WSH_DEBUG_SV.push(l_module_name);
188 WSH_DEBUG_SV.log(l_module_name,'api_version_number',p_in_attributes.api_version_number);
189 WSH_DEBUG_SV.log(l_module_name,'source_code',p_in_attributes.source_code);
190 WSH_DEBUG_SV.log(l_module_name,'closing_fm_date',p_in_attributes.closing_fm_date);
191 WSH_DEBUG_SV.log(l_module_name,'closing_to_date',p_in_attributes.closing_to_date);
192 WSH_DEBUG_SV.log(l_module_name,'organization_id',p_in_attributes.organization_id);
193 WSH_DEBUG_SV.log(l_module_name,'dummy_count',p_inout_attributes.dummy_count);
194 END IF;
195
196 IF ( p_in_attributes.source_code IS NULL )
197 THEN
198 x_msg_count := 1;
199 x_msg_data := 'INVALID SOURCE_CODE';
200 x_return_status := FND_API.G_RET_STS_ERROR;
201 END IF;
202
203 x_return_status := FND_API.G_RET_STS_SUCCESS;
204
205 l_closing_fm_date := p_in_attributes.closing_fm_date;
206 l_closing_to_date := p_in_attributes.closing_to_date;
207 l_organization_id := p_in_attributes.organization_id;
208
209 BEGIN
210 select count(*)
211 into l_untrxd_rec_count
212 from wsh_delivery_details wdd, wsh_delivery_assignments_v wda,
213 wsh_new_deliveries wnd, wsh_delivery_legs wdl, wsh_trip_stops wts
214 where
215 wdd.source_code = 'OE'
216 and wdd.released_status = 'C'
217 and wdd.inv_interfaced_flag in ('N' ,'P')
218 and wdd.organization_id = l_organization_id
219 and wda.delivery_detail_id = wdd.delivery_detail_id
220 and wnd.delivery_id = wda.delivery_id
221 and wnd.status_code in ('CL','IT')
222 and wdl.delivery_id = wnd.delivery_id
223 and wts.pending_interface_flag in ('Y', 'P')
224 and trunc(wts.actual_departure_date) between l_closing_fm_date and l_closing_to_date
225 and wdl.pick_up_stop_id = wts.stop_id;
226
227 IF l_debug_on THEN
228 WSH_DEBUG_SV.log(l_module_name,'l_untrxd_rec_count',l_untrxd_rec_count);
229 END IF;
230
231 EXCEPTION
232 WHEN NO_DATA_FOUND THEN
233 l_untrxd_rec_count := 0;
234
235 WHEN OTHERS THEN
236 l_untrxd_rec_count := null;
237 x_msg_count := 1;
238 FND_MESSAGE.Set_Name('WSH','WSH_UNEXPECTED_ERROR');
239 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
240
241 END;
242 BEGIN
243 -- Check for Expense Destination Type Code Receiving Transactions
244 select count(*)
245 into l_rec_exp_count
246 from wsh_delivery_details wdd, wsh_delivery_assignments wda,
247 wsh_new_deliveries wnd, wsh_delivery_legs wdl, wsh_trip_stops wts,
248 oe_order_lines_all oel, po_requisition_lines_all pl
249 where
250 wdd.source_code = 'OE'
251 and wdd.released_status = 'C'
252 and wdd.inv_interfaced_flag in ('N' ,'P')
253 and wda.delivery_detail_id = wdd.delivery_detail_id
254 and wnd.delivery_id = wda.delivery_id
255 and wnd.status_code in ('CL','IT')
256 and wdl.delivery_id = wnd.delivery_id
257 and wts.pending_interface_flag in ('Y', 'P')
258 and trunc(wts.actual_departure_date) between l_closing_fm_date and l_closing_to_date
259 and wdd.source_line_id = oel.line_id
260 and wdd.source_document_type_id = 10
261 and oel.source_document_line_id = pl.requisition_line_id
262 and pl.destination_organization_id = l_organization_id
263 and pl.destination_organization_id <> pl.source_organization_id
264 and pl.destination_type_code = 'EXPENSE'
265 and wdl.pick_up_stop_id = wts.stop_id
266 and wts.stop_location_id = wnd.initial_pickup_location_id;
267
268 IF l_debug_on THEN
269 WSH_DEBUG_SV.log(l_module_name,'l_rec_exp_count',l_rec_exp_count);
270 END IF;
271 EXCEPTION
272 WHEN NO_DATA_FOUND THEN
273 l_rec_exp_count := 0;
274 WHEN OTHERS THEN
275 l_rec_exp_count := null;
276 x_msg_count := 1;
277 FND_MESSAGE.Set_Name('WSH','WSH_UNEXPECTED_ERROR');
278 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
279 END;
280
281 BEGIN
282 -- Check for Direct Shipment which are not Expense Destination Type Code Related
283 select count(*)
284 into l_rec_direct_count
285 from wsh_delivery_details wdd, wsh_delivery_assignments wda,
286 wsh_new_deliveries wnd, wsh_delivery_legs wdl, wsh_trip_stops wts,
287 oe_order_lines_all oel, po_requisition_lines_all pl,
288 mtl_interorg_parameters mip
289 where
290 wdd.source_code = 'OE'
291 and wdd.released_status = 'C'
292 and wdd.inv_interfaced_flag in ('N' ,'P')
293 and wda.delivery_detail_id = wdd.delivery_detail_id
294 and wnd.delivery_id = wda.delivery_id
295 and wnd.status_code in ('CL','IT')
296 and wdl.delivery_id = wnd.delivery_id
297 and wts.pending_interface_flag in ('Y', 'P')
298 and trunc(wts.actual_departure_date) between l_closing_fm_date and l_closing_to_date
299 and wdd.source_line_id = oel.line_id
300 and wdd.source_document_type_id = 10
301 and oel.source_document_line_id = pl.requisition_line_id
302 and pl.destination_organization_id = l_organization_id
303 and pl.destination_organization_id <> pl.source_organization_id
304 and pl.destination_organization_id = mip.to_organization_id
305 and pl.source_organization_id = mip.from_organization_id
306 and mip.intransit_type = 1
307 and pl.destination_type_code <> 'EXPENSE'
308 and wdl.pick_up_stop_id = wts.stop_id
309 and wts.stop_location_id = wnd.initial_pickup_location_id;
310
311 IF l_debug_on THEN
312 WSH_DEBUG_SV.log(l_module_name,'l_rec_direct_count',l_rec_direct_count);
313 END IF;
314 EXCEPTION
315 WHEN NO_DATA_FOUND THEN
316 l_rec_direct_count := 0;
317 WHEN OTHERS THEN
318 l_rec_direct_count := null;
319 x_msg_count := 1;
320 FND_MESSAGE.Set_Name('WSH','WSH_UNEXPECTED_ERROR');
321 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
322 END;
323
324 p_out_attributes.untrxd_rec_count := l_untrxd_rec_count;
325 p_out_attributes.receiving_rec_count := NVL(l_rec_exp_count,0) + NVL(l_rec_direct_count,0);
326
327 IF l_debug_on THEN
328 WSH_DEBUG_SV.pop(l_module_name);
329 END IF;
330
331 EXCEPTION
332 WHEN OTHERS THEN
333 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
334
335 FND_MSG_PUB.Count_And_Get
336 ( p_count => x_msg_count
337 , p_data => x_msg_data
338 ,p_encoded => FND_API.G_FALSE
339 );
340
341 IF l_debug_on THEN
342 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
343 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
344 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
345 END IF;
346
347 END Get_Untrxd_Shpg_Lines_Count;
348
349 --2465199
350 -- PROCEDURE GET_NONINTF_SHPG_LINE_QTY
351 --
352 -- Purpose : To Get the total order line quantity that is not yet interfaced to Inventory
353 --
354 -- Input : p_in_attributes.line_id -> Order line id
355 -- : p_in_attributes.source_code -> 'OE'
356 --
357 -- Output : p_out_attributes.nonintf_line_qty : Total Order line Qty. not yet Interfaced to INV
358
359 PROCEDURE Get_NonIntf_Shpg_Line_Qty
360 ( p_in_attributes IN LineIntfInRecType,
361 p_out_attributes OUT NOCOPY LineIntfOutRecType,
362 p_inout_attributes IN OUT NOCOPY LineIntfInOutRecType,
363 x_return_status OUT NOCOPY VARCHAR2,
364 x_msg_count OUT NOCOPY NUMBER,
365 x_msg_data OUT NOCOPY VARCHAR2
366 )
367 IS
368 l_nonintf_line_qty NUMBER;
369 l_line_id NUMBER;
370 BEGIN
371
372 --
373 --
374 IF ( p_in_attributes.source_code IS NULL )
375 THEN
376 x_msg_count := 1;
377 x_msg_data := 'INVALID SOURCE_CODE';
378 x_return_status := FND_API.G_RET_STS_ERROR;
379 END IF;
380
381 x_return_status := FND_API.G_RET_STS_SUCCESS;
382
383 l_line_id := p_in_attributes.line_id;
384
385 BEGIN
386 select sum( nvl(shipped_quantity, nvl(picked_quantity, requested_quantity)) )
387 into l_nonintf_line_qty
388 from wsh_delivery_details wdd
389 where
390 wdd.source_code = 'OE'
391 and wdd.inv_interfaced_flag in ('N' ,'P')
392 and wdd.source_line_id = l_line_id;
393
394 EXCEPTION
395 WHEN NO_DATA_FOUND THEN
396 l_nonintf_line_qty := 0;
397
398 WHEN OTHERS THEN
399 l_nonintf_line_qty := null;
400 x_msg_count := 1;
401 FND_MESSAGE.Set_Name('WSH','WSH_UNEXPECTED_ERROR');
402 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
403
404 END;
405
406 p_out_attributes.nonintf_line_qty := l_nonintf_line_qty;
407
408 EXCEPTION
409 WHEN OTHERS THEN
410 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
411
412 FND_MSG_PUB.Count_And_Get
413 ( p_count => x_msg_count
414 , p_data => x_msg_data
415 ,p_encoded => FND_API.G_FALSE
416 );
417
418 END Get_Nonintf_Shpg_Line_Qty;
419
420 -- PROCEDURE Ins_Backorder_SS_SMC_Rec
421 --
422 -- Purpose : To insert the Ship Set or Ship Model line which has insufficient available quantity
423 -- and causes the backorder of the line. This will lead to other lines in Ship Set / SMC
424 -- to also become backordered, if the Enforce Ship Set / SMC is set in Shipping Parameters
425 -- Inventory will call this in such a scenario and will have 1 record for each Ship Set / SMC
426 --
427 -- Input : p_api_version_number -> Standard Version Number 1.0
428 -- p_source_code -> Source code = 'INV'
429 -- p_init_msg_list -> Should be initialized to TRUE by the caller
430 -- p_backorder_rec -> Record containing Line information for Backordering
431 --
432 -- Output : x_return_status -> Return Status
433 -- x_msg_count -> Error / Warning Message Count
434 -- x_msg_data -> Error / Warning Message
435
436 PROCEDURE Ins_Backorder_SS_SMC_Rec (
437 p_api_version_number IN NUMBER,
438 p_source_code IN VARCHAR2,
439 p_init_msg_list IN VARCHAR2,
440 p_backorder_rec IN BackorderRecType,
441 x_return_status OUT NOCOPY VARCHAR2,
442 x_msg_count OUT NOCOPY NUMBER,
443 x_msg_data OUT NOCOPY VARCHAR2
444 )
445
446 IS
447
448 l_cnt NUMBER;
449 l_api_version_number CONSTANT NUMBER := 1.0;
450 l_api_name CONSTANT VARCHAR2(30):= 'Ins_Backorder_SS_SMC_Rec';
451
452 --
453 l_debug_on BOOLEAN;
454 --
455 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'INS_BACKORDER_SS_SMC_REC';
456 --
457 BEGIN
458 --
459 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
460 --
461 IF l_debug_on IS NULL
462 THEN
463 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
464 END IF;
465 --
466 IF l_debug_on THEN
467 WSH_DEBUG_SV.push(l_module_name);
468 WSH_DEBUG_SV.log(l_module_name,'p_api_version_number',p_api_version_number);
469 WSH_DEBUG_SV.log(l_module_name,'p_source_code',p_source_code);
470 WSH_DEBUG_SV.log(l_module_name,'p_init_msg_list',p_init_msg_list);
471 WSH_DEBUG_SV.log(l_module_name,'move_order_line_id',p_backorder_rec.move_order_line_id);
472 WSH_DEBUG_SV.log(l_module_name,'delivery_detail_id',p_backorder_rec.delivery_detail_id);
473 WSH_DEBUG_SV.log(l_module_name,'ship_set_id',p_backorder_rec.ship_set_id);
474 WSH_DEBUG_SV.log(l_module_name,'ship_model_id',p_backorder_rec.ship_model_id);
475 END IF;
476
477
478 x_return_status := FND_API.G_RET_STS_SUCCESS;
479
480 -- Standard call to check for call compatibility
481 IF NOT FND_API.Compatible_API_Call
482 ( l_api_version_number
483 , p_api_version_number
484 , l_api_name
485 , G_PKG_NAME
486 )
487 THEN
488 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
489 END IF;
490
491 -- Initialize message list.
492
493 IF FND_API.to_Boolean(p_init_msg_list) THEN
494 FND_MSG_PUB.initialize;
495 END IF;
496
497
498 IF ( p_source_code IS NULL ) OR (p_source_code <> 'INV' ) THEN
499
500 x_msg_count := 1;
501 x_msg_data := 'INVALID SOURCE CODE';
502 x_return_status := FND_API.G_RET_STS_ERROR;
503
504 ELSIF ( p_backorder_rec.move_order_line_id IS NULL AND p_backorder_rec.delivery_detail_id IS NULL )
505 OR ( p_backorder_rec.ship_set_id IS NULL AND p_backorder_rec.ship_model_id IS NULL ) THEN
506
507 x_msg_count := 1;
508 x_msg_data := 'INVALID RECORD';
509 x_return_status := FND_API.G_RET_STS_ERROR;
510
511 ELSE
512
513 l_cnt := G_BackorderRec_Tbl.LAST;
514
515 IF l_cnt IS NULL THEN
516 l_cnt := 1;
517 ELSE
518 l_cnt := l_cnt + 1;
519 END IF;
520
521 G_BackorderRec_Tbl(l_cnt).move_order_line_id := p_backorder_rec.move_order_line_id;
522 G_BackorderRec_Tbl(l_cnt).delivery_detail_id := p_backorder_rec.delivery_detail_id;
523 G_BackorderRec_Tbl(l_cnt).ship_set_id := p_backorder_rec.ship_set_id;
524 G_BackorderRec_Tbl(l_cnt).ship_model_id := p_backorder_rec.ship_model_id;
525
526 END IF;
527
528 IF l_debug_on THEN
529 WSH_DEBUG_SV.pop(l_module_name);
530 END IF;
531 EXCEPTION
532
533 WHEN OTHERS THEN
534
535 FND_MESSAGE.Set_Name('WSH','WSH_UNEXPECTED_ERROR');
536 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
537
538 -- Get message count and data
539
540 FND_MSG_PUB.Count_And_Get
541 ( p_count => x_msg_count
542 , p_data => x_msg_data
543 );
544
545 IF l_debug_on THEN
546 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
547 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
548 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
549 END IF;
550
551 END Ins_Backorder_SS_SMC_Rec;
552
553 /*
554 ** -- The below API has been copied from WMS.
555 */
556 -- NOTES
557 --
558 -- HISTORY
559 --
560 -- 05-June-2002 Created By Johnson Abraham (joabraha@us)
561
562 -- This is the final version.
563 --
564
565 PROCEDURE trace(p_message IN VARCHAR2) iS
566 --
567 l_debug_on BOOLEAN;
568 --
569 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'TRACE';
570 --
571 BEGIN
572 -- Please replace this call to the one used by shipping.
573 --INV_LOG_UTIL.trace(p_message, 'WSH_PRINTER_ASSG', 1);
574
575 -- replaced as required
576 --
577 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
578 --
579 IF l_debug_on IS NULL
580 THEN
581 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
582 END IF;
583 --
584 IF l_debug_on THEN
585 wsh_debug_sv.log (l_module_name,'p_message');
586 END IF;
587 END trace;
588
589 PROCEDURE update_printer_assignment(
590 x_msg_count OUT NOCOPY NUMBER
591 , x_msg_data OUT NOCOPY VARCHAR2
592 , x_return_status OUT NOCOPY VARCHAR2
593 , p_application_id IN NUMBER
594 , p_conc_program_id IN NUMBER
595 , p_level_type_id IN NUMBER
596 , p_level_value_id IN NUMBER
597 , p_organization_id IN NUMBER
598 , p_printer_name IN VARCHAR2
599 , p_enabled_flag IN VARCHAR2
600 ) IS
601
602 l_api_name CONSTANT VARCHAR2(100) := 'update_printer_assignment';
603
604 l_enabled_flag WSH_REPORT_PRINTERS.enabled_flag%TYPE := null;
605 l_default_printer_flag WSH_REPORT_PRINTERS.default_printer_flag%TYPE := null;
606
607 l_min_label_type_id NUMBER := 1;
608 l_max_label_type_id NUMBER := 0;
609 loop_counter NUMBER := 0;
610
611 l_label_type_id NUMBER := 0;
612 l_application_id NUMBER := null;
613
614 l_printer_signed_on NUMBER := 0;
615
616 CURSOR c_printer_enabled(v_application_id NUMBER, v_label_type_id NUMBER) IS
617 SELECT enabled_flag, default_printer_flag
618 FROM WSH_REPORT_PRINTERS
619 WHERE application_id = v_application_id
620 AND concurrent_program_id = v_label_type_id
621 AND level_type_id = p_level_type_id
622 AND level_value_id = p_level_value_id
623 AND printer_name = p_printer_name;
624
625 CURSOR c_get_max_label_type_id IS
626 SELECT max(lookup_code)
627 FROM mfg_lookups
628 WHERE lookup_type = 'WMS_LABEL_TYPE';
629
630 --
631 l_debug_on BOOLEAN;
632 --
633 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_PRINTER_ASSIGNMENT';
634 --
635 BEGIN
636 --
637 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
638 --
639 IF l_debug_on IS NULL
640 THEN
641 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
642 END IF;
643 --
644 WSH_DEBUG_SV.start_debug();
645 IF l_debug_on THEN
646 wsh_debug_sv.push(l_module_name);
647 WSH_DEBUG_SV.log(l_module_name,'p_application_id',p_application_id);
648 WSH_DEBUG_SV.log(l_module_name,'p_conc_program_id',p_conc_program_id);
649 WSH_DEBUG_SV.log(l_module_name,'p_level_type_id',p_level_type_id);
650 WSH_DEBUG_SV.log(l_module_name,'p_level_value_id',p_level_value_id);
651 WSH_DEBUG_SV.log(l_module_name,'p_organization_id',p_organization_id);
652 WSH_DEBUG_SV.log(l_module_name,'p_printer_name',p_printer_name);
653 WSH_DEBUG_SV.log(l_module_name,'p_enabled_flag',p_enabled_flag);
654 END IF;
655
656 x_return_status := FND_API.G_RET_STS_SUCCESS;
657 l_application_id := p_application_id;
658
659 trace('******** Mobile Printer Sign On **********');
660
661 trace('p_application_id passed in = ' || p_application_id);
662 trace('p_conc_program_id passed in = ' || p_conc_program_id);
663 trace('p_level_type_id passed in = ' || p_level_type_id);
664 trace('p_level_value_id passed in = ' || p_level_value_id);
665 trace('p_organization_id passed in = ' || p_organization_id);
666 trace('p_printer_name passed in = ' || p_printer_name);
667 trace('p_enabled_flag passed in = ' || p_enabled_flag);
668
669 IF (p_enabled_flag = 'Y') AND (p_printer_name IS NULL) THEN
670 trace('Printer name is required for enabling');
671 FND_MESSAGE.SET_NAME('WSH', 'WSH_PRINTER_NAME_REQUIRED');
672 FND_MSG_PUB.ADD;
673 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
674 , p_data => x_msg_data );
675 x_return_status := fnd_api.g_ret_sts_error;
676 IF l_debug_on THEN
677 wsh_debug_sv.pop(l_module_name,'RETURN');
678 END IF;
679
680 RETURN;
681 END IF;
682
683 -- When signing off a printer, check to see whether this printer has been signed on
684 -- If this printer is not signed on for any type, given error message and return.
685 -- This SELECT checks to see if there is atleast a single label type enabled for this printer.
686 -- In case the user is signing off this printer for all label types, the "p_conc_program_id"
687 -- is null and so the "concurrent_program_id = nvl(p_conc_program_id, concurrent_program_id)"
688 -- results in a true.
689 -- The advantage of having this check outside of the loop is that the message pops up only for
690 -- cases where a printer name is specified.
691 -- A good test case for this would be to enable a printer for multiple label types and test.
692 -- In cases of signing off all printers for all label types, this part of the code is not executed
693 -- and so the messages don't pop up.
694 IF (p_printer_name IS NOT NULL) AND
695 (nvl(p_enabled_flag,'N') = 'N') THEN
696 trace('When signing off a specific printer, check whether this printer has been signed on');
697
698 BEGIN
699 SELECT 1 INTO l_printer_signed_on FROM dual
700 WHERE EXISTS
701 (SELECT 1
702 FROM wsh_report_printers
703 WHERE enabled_flag ='Y'
704 AND nvl(default_printer_flag,'N') ='Y'
705 AND concurrent_program_id = nvl(p_conc_program_id, concurrent_program_id)
706 AND level_type_id = p_level_type_id
707 AND level_value_id = p_level_value_id
708 AND printer_name = p_printer_name);
709 EXCEPTION
710 WHEN NO_DATA_FOUND THEN
711 trace('Printer is not signed on');
712 wsh_debug_sv.pop(l_module_name);
713 FND_MESSAGE.SET_NAME('WSH', 'WSH_PRINTER_NOT_SIGNON');
714 FND_MSG_PUB.ADD;
715 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
716 , p_data => x_msg_data );
717 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
718 IF l_debug_on THEN
719 wsh_debug_sv.pop(l_module_name,'RETURN');
720 END IF;
721 RETURN;
722 END;
723 trace('printer has been signed on, continue signing off');
724 END IF;
725
726
727 IF p_conc_program_id IS NULL THEN
728 -- Check to see if the concurrent program ID(Label Type ID) is null.
729 -- This means for all label types.
730 trace('Enabling/Disabling printer(s) for all label types');
731 l_min_label_type_id := 1;
732
733 OPEN c_get_max_label_type_id;
734 FETCH c_get_max_label_type_id
735 INTO l_max_label_type_id;
736
737 CLOSE c_get_max_label_type_id;
738 ELSE
739 l_min_label_type_id := p_conc_program_id;
740 l_max_label_type_id := p_conc_program_id;
741 END IF;
742
743 trace('Got min_label_type_id = '|| l_min_label_type_id);
744 trace('Got max_label_type_id = '|| l_max_label_type_id);
745 loop_counter := 1;
746 FOR l_label_type_id IN l_min_label_type_id..l_max_label_type_id LOOP
747 -- Looping for every label type id retrieved
748 trace('p_conc_program_id = ' || l_label_type_id);
749 trace('In Loop ' || loop_counter);
750
751 trace('Deriving Application ID');
752 IF l_label_type_id in (3,4,5,9) THEN
753 l_application_id := 385;
754 trace('label_type_id = '|| l_label_type_id || ' Application ID Derived = ' || l_application_id);
755 ELSE
756 l_application_id := 401;
757 trace('label_type_id = '|| l_label_type_id || ' Application ID Derived = ' || l_application_id);
758 END IF;
759
760 trace('p_application_id = ' || l_application_id);
761 trace('p_conc_program_id = ' || l_label_type_id);
762
763 IF (p_enabled_flag = 'Y') THEN
764 -- Trying to enable a printer/user/doc relationship
765 -- Check for existing records that match the App_id, conc_prog_id, level_type_id, level_value_id,
766 -- printer name in the WSH_REPORT_PRINTERS.
767
768 trace('Inside p_enabled_flag = Y Enabling Printer ');
769
770 OPEN c_printer_enabled(l_application_id, l_label_type_id);
771 FETCH c_printer_enabled INTO l_enabled_flag, l_default_printer_flag;
772
773 trace(' l_enabled_flag is ' || l_enabled_flag);
774 trace(' l_default_printer_flag ' || l_default_printer_flag);
775
776 IF c_printer_enabled%NOTFOUND THEN
777 --IF (l_enabled_flag <> 'Y') OR (nvl(l_default_printer_flag, 'N') <> 'Y') THEN
778 -- Check to see if the printer is already enabled.
779
780 trace('No Records exist for this combination');
781 trace('Inserting new record for the combination');
782
783 -- Insert the new relation into the table.
784 INSERT INTO WSH_REPORT_PRINTERS
785 ( application_id
786 , concurrent_program_id
787 , level_type_id
788 , level_value_id
789 , printer_name
790 , description
791 , enabled_flag
792 , attribute_category
793 , attribute1
794 , attribute2
795 , attribute3
796 , attribute4
797 , attribute5
798 , attribute6
799 , attribute7
800 , attribute8
801 , attribute9
802 , attribute10
803 , attribute11
804 , attribute12
805 , attribute13
806 , attribute14
807 , attribute15
808 , creation_date
809 , created_by
810 , last_update_date
811 , last_updated_by
812 , last_update_login
813 , request_id
814 , program_application_id
815 , program_id
816 , program_update_date
817 , label_id
818 , format_type
819 , equipment_instance
820 , organization_id
821 , subinventory
822 , default_printer_flag)
823 VALUES(
824 l_application_id
825 , l_label_type_id
826 , p_level_type_id
827 , p_level_value_id
828 , p_printer_name
829 , null
830 , p_enabled_flag
831 , null
832 , null
833 , null
834 , null
835 , null
836 , null
837 , null
838 , null
839 , null
840 , null
841 , null
842 , null
843 , null
844 , null
845 , null
846 , null
847 , sysdate
848 , FND_GLOBAL.user_id
849 , sysdate
850 , FND_GLOBAL.user_id
851 , FND_GLOBAL.user_id
852 , null
853 , null
854 , null
855 , null
856 , null
857 , null
858 , null
859 , null
860 , null
861 , 'Y');
862 trace('Record inserted');
863 ELSE
864 trace('Record already exists');
865 IF (l_enabled_flag <> 'Y') OR (nvl(l_default_printer_flag, 'N') <> 'Y') THEN
866 -- Check to see if the printer is already enabled.
867
868 trace('Updating existing record for the combination');
869 -- Relationship exists already.
870 -- Now the printer is also marked as the default printer for the user.
871
872 UPDATE WSH_REPORT_PRINTERS
873 SET enabled_flag = 'Y',
874 default_printer_flag = 'Y'
875 WHERE application_id = l_application_id
876 AND concurrent_program_id = l_label_type_id
877 AND level_type_id = p_level_type_id
878 AND level_value_id = p_level_value_id
879 AND printer_name = p_printer_name;
880 trace('Record updated');
881 ELSE
882 trace('Printer ' ||p_printer_name||' is already enabled');
883 END IF;
884 END IF;
885
886 trace('Update printers other than the current one to be not default');
887 -- Reset the current default_printer for this user since the newly enabled printer
888 -- will also be the default printer for this user.
889
890 -- In this update, set the default_printer_flag to 'N' so that any printer
891 -- with the enabled_flag earlier is left untouched. The currently enabled printer
892 -- is a combination of the "enabled_flag" and the "default_printer_flag" set to 'Y'.
893 UPDATE WSH_REPORT_PRINTERS
894 SET enabled_flag = 'N',
895 default_printer_flag = null
896 WHERE application_id = l_application_id
897 AND concurrent_program_id = l_label_type_id
898 AND level_type_id = p_level_type_id
899 AND level_value_id = p_level_value_id
900 AND printer_name <> p_printer_name;
901 CLOSE c_printer_enabled;
902 trace('Record updated');
903
904 ELSIF (p_enabled_flag = 'N') THEN
905 -- Trying to disable all printer/user/doc relationship for that user
906 -- Trying to disable a specific printer/user/doc relationship
907 -- This is for the case where printers are being disabled for multiple label types,
908 -- (label type id null passed in), application id.
909
910 trace('Disabling all printers or the specific printer');
911
912 -- Check to see if a printer name is passed in.
913 -- If yes, then check to see if a record exists for the printer for the user
914 -- and enable only if the printer has already been disabled.
915
916 trace('Inside p_enabled_flag = N');
917 UPDATE WSH_REPORT_PRINTERS
918 SET enabled_flag = 'N',
919 default_printer_flag = null
920 WHERE application_id = l_application_id
921 AND concurrent_program_id = l_label_type_id
922 AND level_type_id = p_level_type_id
923 AND level_value_id = p_level_value_id
924 AND printer_name = nvl(p_printer_name, printer_name);
925 trace('Record updated');
926
927 END IF;
928 loop_counter := loop_counter + 1;
929
930 END LOOP;
931 COMMIT;
932 IF l_debug_on THEN
933 wsh_debug_sv.pop(l_module_name);
934 wsh_debug_sv.stop_debug;
935 END IF;
936 EXCEPTION
937 WHEN fnd_api.g_exc_error THEN
938 trace(' Expected Error In '|| G_PKG_NAME||'.' || l_api_name);
939 trace('ERROR CODE = ' || SQLCODE);
940 trace('ERROR MESSAGE = ' || SQLERRM);
941 IF l_debug_on THEN
942 wsh_debug_sv.pop(l_module_name);
943 wsh_debug_sv.stop_debug;
944 END IF;
945 ROLLBACK;
946
947 WHEN fnd_api.g_exc_unexpected_error THEN
948 trace(' Unexpected Error In '|| G_PKG_NAME||'.' || l_api_name);
949 trace('ERROR CODE = ' || SQLCODE);
950 trace('ERROR MESSAGE = ' || SQLERRM);
951 IF l_debug_on THEN
952 wsh_debug_sv.pop(l_module_name);
953 wsh_debug_sv.stop_debug;
954 END IF;
955 ROLLBACK;
956
957 WHEN others THEN
958 trace(' Other Error In '|| G_PKG_NAME||'.' || l_api_name);
959 trace('ERROR CODE = ' || SQLCODE);
960 trace('ERROR MESSAGE = ' || SQLERRM);
961 IF l_debug_on THEN
962 wsh_debug_sv.pop(l_module_name);
963 wsh_debug_sv.stop_debug;
964 END IF;
965 ROLLBACK;
966
967 END update_printer_assignment;
968
969 -- For the issue in bug 2678601 porting to Pack I
970
971 PROCEDURE Set_Inv_PC_Attributes
972 ( p_in_attributes IN InvPCInRecType,
973 x_return_status OUT NOCOPY VARCHAR2,
974 x_msg_count OUT NOCOPY NUMBER,
975 x_msg_data OUT NOCOPY VARCHAR2
976 )
977 IS
978
979 l_api_version_number CONSTANT NUMBER := 1.0;
980 l_api_name CONSTANT VARCHAR2(30):= 'Set_Inv_PC_Attributes';
981
982 BEGIN
983 x_return_status := FND_API.G_RET_STS_SUCCESS;
984
985 -- Standard call to check for call compatibility
986 IF NOT FND_API.Compatible_API_Call
987 ( l_api_version_number
988 , p_in_attributes.api_version_number
989 , l_api_name
990 , G_PKG_NAME
991 )
992 THEN
993 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
994 END IF;
995 IF ( p_in_attributes.source_code IS NULL ) OR (p_in_attributes.source_code
996 <> 'INV' ) THEN
997 x_msg_count := 1;
998 x_msg_data := 'INVALID SOURCE CODE';
999 x_return_status := FND_API.G_RET_STS_ERROR;
1000 ELSE
1001 G_InvPCRec.transaction_id := p_in_attributes.transaction_id;
1002 G_InvPCRec.transaction_temp_id := p_in_attributes.transaction_temp_id;
1003 END IF;
1004
1005 EXCEPTION
1006
1007 WHEN OTHERS THEN
1008
1009 FND_MESSAGE.Set_Name('WSH','WSH_UNEXPECTED_ERROR');
1010 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1011
1012 -- Get message count and data
1013
1014 FND_MSG_PUB.Count_And_Get
1015 ( p_count => x_msg_count
1016 , p_data => x_msg_data
1017 );
1018
1019 END Set_Inv_PC_Attributes;
1020
1021 PROCEDURE Get_Inv_PC_Attributes
1022 ( p_out_attributes OUT NOCOPY InvPCOutRecType,
1023 x_return_status OUT NOCOPY VARCHAR2,
1024 x_msg_count OUT NOCOPY NUMBER,
1025 x_msg_data OUT NOCOPY VARCHAR2
1026 ) IS
1027 BEGIN
1028 x_return_status := FND_API.G_RET_STS_SUCCESS;
1029
1030 p_out_attributes.transaction_id := G_InvPCRec.transaction_id;
1031 p_out_attributes.transaction_temp_id := G_InvPCRec.transaction_temp_id;
1032
1033 EXCEPTION
1034
1035 WHEN OTHERS THEN
1036
1037 FND_MESSAGE.Set_Name('WSH','WSH_UNEXPECTED_ERROR');
1038 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1039
1040 -- Get message count and data
1041
1042 FND_MSG_PUB.Count_And_Get
1043 ( p_count => x_msg_count
1044 , p_data => x_msg_data
1045 );
1046
1047 END Get_Inv_PC_Attributes;
1048
1049 -- DBI Project, Added in 11.5.10+
1050 --
1051 --===============================================
1052 -- Name : DBI_Installed
1053 -- Purpose: To check if DBI is installed,
1054 -- History: Added in 11i10+
1055 --
1056 -- Input Arguments: None
1057 -- Output Arguments: Varchar2(1) indicating Y or N
1058 --
1059 --===============================================
1060 Function DBI_Installed return VARCHAR2 is
1061 l_debug_on BOOLEAN;
1062 --
1063 --l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DBI_INSTALLED';
1064 BEGIN
1065 --Commenting the code for Obsoletion of DBI APIs in 12.2
1066 /*l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1067 --
1068 IF l_debug_on IS NULL THEN
1069 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1070 END IF;
1071 --
1072 IF l_debug_on THEN
1073 WSH_DEBUG_SV.push(l_module_name);
1074 END IF;
1075 --
1076 IF G_DBI_IS_INSTALLED IS NULL THEN
1077 G_DBI_IS_INSTALLED := (NVL(FND_PROFILE.VALUE('ISC_WSH_FTE_DBI_INSTALLED'), 'N'));
1078 END IF;
1079 IF l_debug_on THEN
1080 WSH_DEBUG_SV.log(l_module_name,'G_DBI_IS_INSTALLED',G_DBI_IS_INSTALLED);
1081 WSH_DEBUG_SV.pop(l_module_name);
1082 END IF;*/
1083 RETURN 'N';
1084
1085 /*EXCEPTION
1086 WHEN OTHERS THEN
1087 FND_MESSAGE.Set_Name('WSH','WSH_UNEXPECTED_ERROR');
1088 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR,l_module_name);
1089 IF l_debug_on THEN
1090 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
1091 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1092 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1093 END IF;*/
1094 END DBI_Installed;
1095
1096 --========================================================
1097 -- DataType Conversion APIs , DBI Project
1098 -- 1. Convert WSH table of ids to DBI Table of ids
1099 --=========================================================
1100 --Commenting the code for Obsoletion of DBI APIs in 12.2
1101 -- 1.Convert WSH Table of ids to DBI Table of ids
1102 /*PROCEDURE WSH_ID_TAB_TO_DBI_ID_TAB
1103 (p_wsh_id_tab IN WSH_UTIL_CORE.id_tab_type,
1104 x_dbi_id_tab OUT NOCOPY ISC_DBI_CHANGE_LOG_PKG.log_tab_type,
1105 x_return_status OUT NOCOPY VARCHAR2
1106 ) IS
1107
1108 --
1109 l_debug_on BOOLEAN;
1110 --
1111 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.'||G_PKG_NAME||'.'||'WSH_ID_TAB_TO_DBI_ID_TAB';
1112 BEGIN
1113 --
1114 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1115 --
1116 IF l_debug_on IS NULL THEN
1117 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1118 END IF;
1119 --
1120 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1121
1122 IF l_debug_on THEN
1123 WSH_DEBUG_SV.push(l_module_name);
1124 WSH_DEBUG_SV.log(l_module_name,'Count Input records',p_wsh_id_tab.count);
1125 END IF;
1126
1127 -- Conversion is required from WSH datatype to DBI datatype
1128 -- Use the same counter while adding records in x_dbi_tab
1129 FOR i in p_wsh_id_tab.FIRST..p_wsh_id_tab.LAST
1130 LOOP
1131 x_dbi_id_tab(i) := p_wsh_id_tab(i);
1132 IF l_debug_on THEN
1133 WSH_DEBUG_SV.log(l_module_name,'Id being converted-',p_wsh_id_tab(i));
1134 END IF;
1135 END LOOP;
1136
1137 IF l_debug_on THEN
1138 WSH_DEBUG_SV.log(l_module_name,'Count Output records',x_dbi_id_tab.count);
1139 WSH_DEBUG_SV.pop(l_module_name);
1140 END IF;
1141
1142 EXCEPTION
1143 WHEN OTHERS THEN
1144 FND_MESSAGE.Set_Name('WSH','WSH_UNEXPECTED_ERROR');
1145 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1146 wsh_util_core.add_message(x_return_status,l_module_name);
1147 IF l_debug_on THEN
1148 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
1149 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1150 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1151 END IF;
1152 END WSH_ID_TAB_TO_DBI_ID_TAB;*/
1153
1154 --===============================================
1155 -- Name : DBI_Update_Detail_Log
1156 -- Purpose: Call DBI for update in wsh_delivery_details table
1157 -- History: Added in 11i10+. Actions covered are
1158 -- 1. Update of Requested Quantity
1159 -- 2. Update of Requested Quantity UOM
1160 -- 3. Update of Released Status
1161 --
1162 -- Input Arguments:
1163 -- p_delivery_detail_tab - Table of Delivery Detail ids
1164 -- p_dml_type - DML type (INSERT/UPDATE/DELETE)
1165 -- Output Arguments:
1166 -- x_return_status - Return Status
1167 --
1168 --===============================================
1169 PROCEDURE DBI_Update_Detail_Log
1170 (p_delivery_detail_id_tab IN WSH_UTIL_CORE.id_tab_type,
1171 p_dml_type IN VARCHAR2,
1172 x_return_status OUT NOCOPY VARCHAR2) IS
1173
1174 --Commenting the code for Obsoletion of DBI APIs in 12.2
1175 -- l_dbi_detail_list ISC_DBI_CHANGE_LOG_PKG.log_tab_type;
1176 --
1177 -- l_debug_on BOOLEAN;
1178 --
1179 -- l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DBI_UPDATE_DETAIL_LOG';
1180 BEGIN
1181 --
1182 /* l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1183 --
1184 IF l_debug_on IS NULL THEN
1185 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1186 END IF;*/
1187 --
1188 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1189 /*IF l_debug_on THEN
1190 WSH_DEBUG_SV.push(l_module_name);
1191 WSH_DEBUG_SV.log(l_module_name,'Before Calling DBI API-',x_return_status);
1192 WSH_DEBUG_SV.log(l_module_name,'Count Detail records-',p_delivery_detail_id_tab.count);
1193 WSH_DEBUG_SV.log(l_module_name,'DML Type-',p_dml_type);
1194 END IF;
1195 --
1196 -- Check if DBI is installed, possible values are Y or N only
1197 -- If not installed, then do not proceed , return Success
1198 -- Also, atleast 1 record should be populated in the Input table
1199 --IF (WSH_INTEGRATION.DBI_Installed = 'N' OR BUG#11852550
1200 IF p_delivery_detail_id_tab.count < 1
1201 THEN
1202 IF l_debug_on THEN
1203 -- WSH_DEBUG_SV.log(l_module_name,'DBI Installed flag-',WSH_INTEGRATION.DBI_Installed);
1204 WSH_DEBUG_SV.pop(l_module_name);
1205 END IF;
1206 RETURN;
1207 END IF;
1208 --
1209 -- Conversion is required from WSH datatype to DBI datatype
1210 WSH_ID_TAB_TO_DBI_ID_TAB BUG#11852550
1211 (p_wsh_id_tab => p_delivery_detail_id_tab,
1212 x_dbi_id_tab => l_dbi_detail_list,
1213 x_return_status => x_return_status);
1214
1215 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1216
1217 IF x_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1218 IF l_debug_on THEN
1219 WSH_DEBUG_SV.log(l_module_name,'Count Detail records-',l_dbi_detail_list.count);
1220 WSH_DEBUG_SV.log(l_module_name,'Before Calling DBI API-',x_return_status);
1221 END IF;
1222 --
1223 ISC_DBI_CHANGE_LOG_PKG.Update_Del_Detail_Log
1224 (p_detail_list => l_dbi_detail_list,
1225 p_dml_type => p_dml_type,
1226 x_return_status => x_return_status
1227 );
1228 --
1229 -- Only Unexpected error can be raised from DBI API,
1230 -- all others have to be treated as success and code flow
1231 -- will continue and not rollback
1232 IF x_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
1233 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1234 FND_MESSAGE.SET_NAME('WSH','WSH_INCOMPLETE_TRANSACTION');
1235 WSH_UTIL_CORE.Add_Message(x_return_status);
1236 ELSE -- all other Statuses are equivalent to Success
1237 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1238 END IF;
1239 --
1240 END IF;
1241
1242 IF l_debug_on THEN
1243 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
1244 WSH_DEBUG_SV.pop(l_module_name);
1245 END IF;
1246
1247 EXCEPTION
1248 WHEN OTHERS THEN
1249 FND_MESSAGE.Set_Name('WSH','WSH_UNEXPECTED_ERROR');
1250 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1251 wsh_util_core.add_message(x_return_status,l_module_name);
1252
1253 IF l_debug_on THEN
1254 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
1255 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1256 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1257 END IF;*/
1258 END DBI_Update_Detail_Log;
1259
1260
1261 --===============================================
1262 -- Name : DBI_Update_Trip_Stop_Log
1263 -- Purpose: Call DBI for creation/deletion/update of Stop,Delivery Leg
1264 -- History: Added in 11i10+. Actions covered are
1265 -- 1. Create Trip Stop
1266 -- 2. Delete Trip Stop
1267 -- 3. Update of Stop Actual Departure Date
1268 -- 4. Update of Stop Actual Arrival Date
1269 -- 5. Update of Stop Status
1270 -- 6. Update of Planned Arrival Date
1271 -- 7. Assign Delivery to Trip(create delivery leg)
1272 -- 8. Unassign Delivery from Trip(delete delivery leg)
1273 -- 9. Update of Freight Cost of delivery leg
1274 -- 10. Change in Trip Status (corresponds to change in Stop status)
1275 --
1276 -- Input Arguments:
1277 -- p_delivery_detail_tab - Table of Delivery Detail ids
1278 -- p_dml_type - DML type (INSERT/UPDATE/DELETE)
1279 -- Output Arguments:
1280 -- x_return_status - Return Status
1281 --
1282 --===============================================
1283 PROCEDURE DBI_Update_Trip_Stop_Log
1284 (p_stop_id_tab IN WSH_UTIL_CORE.id_tab_type,
1285 p_dml_type IN VARCHAR2,
1286 x_return_status OUT NOCOPY VARCHAR2) IS
1287
1288 --Commenting the code for Obsoletion of DBI APIs in 12.2
1289 -- l_dbi_stop_list ISC_DBI_CHANGE_LOG_PKG.log_tab_type;
1290 --
1291 -- l_debug_on BOOLEAN;
1292 --
1293 -- l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.'||G_PKG_NAME||'.'||'DBI_UPDATE_TRIP_STOP_LOG';
1294 BEGIN
1295 --
1296 /* l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1297 --
1298 IF l_debug_on IS NULL THEN
1299 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1300 END IF;*/
1301 --
1302 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1303 /* IF l_debug_on THEN
1304 WSH_DEBUG_SV.push(l_module_name);
1305 WSH_DEBUG_SV.log(l_module_name,'Count Stop records-',p_stop_id_tab.count);
1306 WSH_DEBUG_SV.log(l_module_name,'DML Type-',p_dml_type);
1307 END IF;
1308 --
1309 -- Check if DBI is installed, possible values are Y or N only
1310 -- If not installed, then do not proceed , return Success
1311 -- Also, atleast 1 record should be populated in the Input table
1312 --IF (WSH_INTEGRATION.DBI_Installed = 'N' OR BUG#11852550
1313 IF p_stop_id_tab.count < 1
1314 THEN
1315 IF l_debug_on THEN
1316 -- WSH_DEBUG_SV.log(l_module_name,'DBI Installed flag-',WSH_INTEGRATION.DBI_Installed);
1317 WSH_DEBUG_SV.pop(l_module_name);
1318 END IF;
1319 RETURN;
1320 END IF;
1321 --
1322 -- Conversion is required from WSH datatype to DBI datatype
1323 WSH_ID_TAB_TO_DBI_ID_TAB BUG#11852550
1324 (p_wsh_id_tab => p_stop_id_tab,
1325 x_dbi_id_tab => l_dbi_stop_list,
1326 x_return_status => x_return_status);
1327
1328 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1329
1330 IF x_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1331
1332 IF l_debug_on THEN
1333 WSH_DEBUG_SV.log(l_module_name,'Count Stop records-',l_dbi_stop_list.count);
1334 WSH_DEBUG_SV.log(l_module_name,'Before Calling DBI API-',x_return_status);
1335 END IF;
1336 --
1337 ISC_DBI_CHANGE_LOG_PKG.Update_Trip_Stop_Log
1338 (p_stop_list => l_dbi_stop_list,
1339 p_dml_type => p_dml_type,
1340 x_return_status => x_return_status
1341 );
1342 --
1343 -- Only Unexpected error can be raised from DBI API,
1344 -- all others have to be treated as success and code flow
1345 -- will continue and not rollback
1346 IF x_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
1347 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1348 FND_MESSAGE.SET_NAME('WSH','WSH_INCOMPLETE_TRANSACTION');
1349 WSH_UTIL_CORE.Add_Message(x_return_status);
1350 ELSE -- all other Statuses are equivalent to Success
1351 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1352 END IF;
1353 --
1354 END IF;
1355
1356 IF l_debug_on THEN
1357 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
1358 WSH_DEBUG_SV.pop(l_module_name);
1359 END IF;
1360
1361 EXCEPTION
1362 WHEN OTHERS THEN
1363 FND_MESSAGE.Set_Name('WSH','WSH_UNEXPECTED_ERROR');
1364 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1365 wsh_util_core.add_message(x_return_status,l_module_name);
1366
1367 IF l_debug_on THEN
1368 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
1369 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1370 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1371 END IF;*/
1372
1373 END DBI_Update_Trip_Stop_Log;
1374
1375 -- X-dock
1376 -- Procedure Name : Find_Matching_Groups
1377 -- Description : This API will find entities (deliveries/containers) that
1378 -- match the grouping criteria of the input table of entities.
1379 -- p_attr_tab : Table of entities or record of grouping criteria that need to be matched.
1380 -- p_action_rec : Record of specific actions and their corresponding parameters.
1381 -- check_single_grp_only: ('Y', 'N') will check only of the records can be
1382 -- grouped together.
1383 -- output_entity_type: ('DLVY', 'CONT') the entity type that the input records
1384 -- need to be matched with.
1385 -- output_format_type: Format of the output.
1386 -- 'ID_TAB': table of id's of the matched entities
1387 -- 'TEMP_TAB': The output will be inserted into wsh_temp (wsh_temp
1388 -- needs to be cleared after this API has been used).
1389 -- 'SQL_STRING': Will return a SQL query to find the matching entities
1390 -- as a string and values of the variables that will
1391 -- need to be bound to the string.
1392 -- p_target_rec : Entity or grouping attributes that need to be matched with (if necessary)
1393 -- x_matched_entities: table of ids of the matched entities
1394 -- x_out_rec : Record of output values based on the actions and output format.
1395 -- query_string: String to query for matching entities.
1396 -- x_return_status : 'S', 'E', 'U'.
1397
1398 procedure Find_Matching_Groups
1399 (p_attr_tab IN OUT NOCOPY GRP_ATTR_TAB_TYPE,
1400 p_action_rec IN ACTION_REC_TYPE,
1401 p_target_rec IN GRP_ATTR_REC_TYPE,
1402 p_group_tab IN OUT NOCOPY GRP_ATTR_TAB_TYPE,
1403 x_matched_entities OUT NOCOPY WSH_UTIL_CORE.ID_TAB_TYPE,
1404 x_out_rec OUT NOCOPY OUT_REC_TYPE,
1405 x_return_status OUT NOCOPY VARCHAR2) IS
1406
1407 -- Local variables to move information from or to the variables
1408 l_attr_tab WSH_DELIVERY_AUTOCREATE.grp_attr_tab_type;
1409 l_action_rec WSH_DELIVERY_AUTOCREATE.action_rec_type;
1410 l_target_rec WSH_DELIVERY_AUTOCREATE.grp_attr_rec_type;
1411 l_group_tab WSH_DELIVERY_AUTOCREATE.grp_attr_tab_type;
1412 l_out_rec WSH_DELIVERY_AUTOCREATE.out_rec_type;
1413 l_del_det_id WSH_DELIVERY_DETAILS.delivery_detail_id%type;
1414 l_org_id WSH_DELIVERY_DETAILS.organization_id%type;
1415 l_param_info WSH_SHIPPING_PARAMS_PVT.Parameter_Rec_Typ;
1416
1417 l_debug_on BOOLEAN;
1418 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Find_Matching_Groups';
1419
1420 BEGIN
1421
1422 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1423 --
1424 IF l_debug_on IS NULL
1425 THEN
1426 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1427 END IF;
1428 --
1429 IF l_debug_on THEN
1430 WSH_DEBUG_SV.push(l_module_name);
1431 WSH_DEBUG_SV.log(l_module_name, 'p_action_rec.action', p_action_rec.action);
1432 WSH_DEBUG_SV.log(l_module_name, 'p_action_rec.output_format_type', p_action_rec.output_format_type);
1433 WSH_DEBUG_SV.log(l_module_name, 'p_attr_tab count' , p_attr_tab.count);
1434 WSH_DEBUG_SV.log(l_module_name, 'p_group_tab count', p_group_tab.count);
1435 WSH_DEBUG_SV.log(l_module_name, 'p_target_rec.entity_type', p_target_rec.entity_type);
1436 END IF;
1437
1438 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1439
1440 --
1441 -- Wrapper on top of WSHDEAUB.find_matching_groups
1442 --
1443 l_attr_tab.delete;
1444 l_group_tab.delete;
1445
1446 -- Move information from p_attr_tab to l_attr_tab
1447 l_attr_tab := p_attr_tab;
1448
1449 -- Move information from p_action_rec to l_p_action_rec
1450 l_action_rec := p_action_rec;
1451
1452 -- Move information from p_target_rec to l_p_target_rec
1453 l_target_rec := p_target_rec;
1454
1455 -- Move information from p_group_tab to l_group_tab
1456 IF p_group_tab.COUNT > 0 THEN
1457 l_group_tab := p_group_tab;
1458 END IF;
1459
1460 --Bug : 6911078 : Check for Appending_limit to get the matching deliveries : Start
1461 IF l_action_rec.action = 'MATCH_GROUPS' AND l_target_rec.entity_type = 'DELIVERY' THEN
1462 --{
1463 IF l_debug_on THEN
1464 --{
1465 WSH_DEBUG_SV.log(l_module_name, 'p_attr_tab.count for ''MATCH_GROUPS'' is ', l_attr_tab.COUNT);
1466 --}
1467 END IF;
1468
1469 IF l_attr_tab.COUNT > 1 THEN
1470 --{
1471 x_return_status := FND_API.G_RET_STS_ERROR;
1472 IF l_debug_on THEN
1473 --{
1474 WSH_DEBUG_SV.log(l_module_name, 'p_attr_tab.count for ''MATCH_GROUPS'' > 1 and hence exiting..');
1475 WSH_DEBUG_SV.pop(l_module_name);
1476 --}
1477 END IF;
1478 RETURN;
1479 --}
1480 END IF;
1481
1482 l_del_det_id := l_attr_tab(l_attr_tab.FIRST).entity_id ;
1483
1484 -- Check for delivery_detail_id > 0
1485 IF NVL(l_del_det_id,0) > 0 THEN
1486 --{
1487 -- Delivery appending limit 'N' check goes in this block.
1488 BEGIN
1489
1490 SELECT organization_id INTO l_org_id FROM wsh_delivery_details WHERE delivery_detail_id = l_del_det_id;
1491 -- Call wsh_shipping_parameters.Get for appending_limit
1492 IF l_debug_on THEN
1493 --{
1494 WSH_DEBUG_SV.log(l_module_name, 'l_org_id', l_org_id);
1495 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_SHIPPING_PARAMS_PVT.GET',WSH_DEBUG_SV.C_PROC_LEVEL);
1496 --}
1497 END IF;
1498
1499 WSH_SHIPPING_PARAMS_PVT.GET(p_organization_id => l_org_id,
1500 x_param_info => l_param_info,
1501 x_return_status => x_return_status);
1502
1503 IF x_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ) THEN
1504 --{
1505 IF l_debug_on THEN
1506 --{
1507 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
1508 WSH_DEBUG_SV.pop(l_module_name);
1509 --}
1510 END IF;
1511 RETURN;
1512 --}
1513 END IF;
1514
1515 IF l_debug_on THEN
1516 --{
1517 WSH_DEBUG_SV.log(l_module_name, 'Appending Limit', l_param_info.APPENDING_LIMIT );
1518 --}
1519 END IF;
1520
1521 IF l_param_info.APPENDING_LIMIT = 'N' THEN
1522 --{
1523 IF l_debug_on THEN
1524 --{
1525 WSH_DEBUG_SV.log(l_module_name, 'Append Limit is ''Do not append'' and hence exiting..');
1526 WSH_DEBUG_SV.pop(l_module_name);
1527 --}
1528 END IF;
1529 RETURN;
1530 --}
1531 END IF;
1532
1533 EXCEPTION
1534 WHEN NO_DATA_FOUND THEN
1535 x_return_status := FND_API.G_RET_STS_ERROR;
1536 IF l_debug_on THEN
1537 --{
1538 WSH_DEBUG_SV.log(l_module_name, 'Delivery Detail doesnt exists in WDD', l_del_det_id);
1539 WSH_DEBUG_SV.pop(l_module_name);
1540 --}
1541 END IF;
1542 RETURN;
1543
1544 WHEN OTHERS THEN
1545 x_return_status := FND_API.G_RET_STS_ERROR;
1546 IF l_debug_on THEN
1547 --{
1548 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1549 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1550 --}
1551 END IF;
1552 RETURN;
1553 END;
1554
1555 ELSE -- Delivery Detail is Invalid
1556 x_return_status := FND_API.G_RET_STS_ERROR;
1557 IF l_debug_on THEN
1558 --{
1559 WSH_DEBUG_SV.logmsg(l_module_name,'Invalid Delivery_detail_id',l_del_det_id);
1560 WSH_DEBUG_SV.pop(l_module_name);
1561 --}
1562 END IF;
1563 RETURN;
1564 --}
1565 END IF;
1566 -- Check for delivery_detail_id > 0
1567 END IF;
1568 --Bug : 6911078 : Check for Appending_limit to get the matching deliveries : End
1569
1570 WSH_DELIVERY_AUTOCREATE.FIND_MATCHING_GROUPS
1571 (p_attr_tab => l_attr_tab,
1572 p_action_rec => l_action_rec,
1573 p_target_rec => l_target_rec,
1574 p_group_tab => l_group_tab,
1575 x_matched_entities => x_matched_entities,
1576 x_out_rec => l_out_rec,
1577 x_return_status => x_return_status);
1578
1579 IF x_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ) THEN
1580 IF l_debug_on THEN
1581 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
1582 WSH_DEBUG_SV.pop(l_module_name);
1583 END IF;
1584 RETURN;
1585 END IF;
1586
1587 -- Move information from l_attr_tab back to p_attr_tab, IN OUT variable
1588 p_attr_tab := l_attr_tab;
1589 -- Move informaiton from l_group_tab back to p_group_tab, IN OUT variable
1590 p_group_tab := l_group_tab;
1591
1592 -- Move information from l_out_rec to x_out_rec
1593 x_out_rec := l_out_rec;
1594
1595 IF l_debug_on THEN
1596 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
1597 wsh_debug_sv.pop(l_module_name);
1598 END IF;
1599
1600 EXCEPTION
1601 WHEN OTHERS THEN
1602 x_return_status := wsh_util_core.g_ret_sts_unexp_error;
1603 wsh_util_core.default_handler('WSH_INTEGRATION.FIND_MATCHING_GROUPS',l_module_name);
1604 IF l_debug_on THEN
1605 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
1606 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1607 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1608 END IF;
1609
1610 END find_matching_groups;
1611
1612 -- end of X-dock changes
1613
1614 -- 5870774
1615 -- Procedure Get_Cancel_Qty_Allowed is to get the Unshipped Qty. on any Source Line id that
1616 -- is allowed to be Cancelled. The Unshipped Qty. is from all the underlying Delivery Details
1617 -- that do not belong to Any Delivery that is either in 'CO'nfirmed, 'CL'osed or 'InTransit' status
1618 -- Input :
1619 -- p_source_code -- Source Code of the Application Calling this Procedure
1620 -- e.g. 'OKE'
1621 -- p_source_line_id -- Source Line id of Source Line for which the Qty. cancellable is to be
1622 -- determined
1623 --
1624 -- Output :
1625 -- x_cancel_qty_allowed -- Qty. that is allowed to be Cancelled in Source Ordered Qty. UOM
1626 -- If there is No Qty. that can be cancelled, then this parameter will return 0 (Zero)
1627 --
1628 -- x_return_status -- Return Status [ Fnd api- Return Status: Sucess, Error , UnExpected Error ]
1629 -- x_msg_count,data -- Number of Messages, Message Data Stored
1630 PROCEDURE Get_Cancel_Qty_Allowed
1631 ( p_source_code IN VARCHAR2,
1632 p_source_line_id IN NUMBER,
1633 x_cancel_qty_allowed OUT NOCOPY NUMBER,
1634 x_return_status OUT NOCOPY VARCHAR2,
1635 x_msg_count OUT NOCOPY NUMBER,
1636 x_msg_data OUT NOCOPY VARCHAR2
1637 )
1638 IS
1639 --
1640 l_cancel_qty_allowed number;
1641 l_src_cancel_qty_allowed number;
1642 l_requested_quantity_uom wsh_delivery_details.src_requested_quantity_uom%type;
1643 l_src_requested_quantity_uom wsh_delivery_details.src_requested_quantity_uom%type;
1644 l_cancel_allowed_quantity number;
1645 l_inventory_item_id number;
1646 --
1647 l_debug_on BOOLEAN;
1648 --
1649 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_CANCEL_QTY_ALLOWED';
1650 --
1651 BEGIN
1652 --
1653 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1654 --
1655 IF l_debug_on IS NULL
1656 THEN
1657 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1658 END IF;
1659 --
1660 IF l_debug_on THEN
1661 WSH_DEBUG_SV.push(l_module_name);
1662 WSH_DEBUG_SV.log(l_module_name,'source_code ',p_source_code);
1663 WSH_DEBUG_SV.log(l_module_name,'src line id ',p_source_line_id);
1664 END IF;
1665
1666 IF ( p_source_code IS NULL )
1667 THEN
1668 x_msg_count := 1;
1669 x_msg_data := 'INVALID SOURCE_CODE';
1670 x_return_status := FND_API.G_RET_STS_ERROR;
1671 IF l_debug_on THEN
1672 WSH_DEBUG_SV.log(l_module_name,'Invalid source code: ', p_source_code);
1673 WSH_DEBUG_SV.pop(l_module_name);
1674 END IF;
1675 return;
1676 END IF;
1677
1678 x_return_status := FND_API.G_RET_STS_SUCCESS;
1679
1680 BEGIN
1681 -- select stmt.
1682
1683 select sum(wdd.requested_quantity) , requested_quantity_uom, src_requested_quantity_uom, inventory_item_id
1684 into l_cancel_qty_allowed , l_requested_quantity_uom, l_src_requested_quantity_uom, l_inventory_item_id
1685 from wsh_delivery_details wdd
1686 where
1687 wdd.source_line_id = p_source_line_id
1688 and wdd.source_code = p_source_code
1689 and not exists (select 'x' from
1690 wsh_delivery_assignments wda,
1691 wsh_new_deliveries wnd
1692 where
1693 wda.delivery_detail_id = wdd.delivery_detail_id
1694 and wda.delivery_id = wnd.delivery_id
1695 and wnd.status_code in ('CL','CO', 'IT') )
1696 group by requested_quantity_uom, src_requested_quantity_uom, inventory_item_id;
1697 --
1698 EXCEPTION
1699 WHEN NO_DATA_FOUND THEN
1700 x_cancel_qty_allowed := 0;
1701 IF l_debug_on THEN
1702 WSH_DEBUG_SV.log(l_module_name, 'No Data Found. Req. Qty. allowed to be cancelled'||to_char(x_cancel_qty_allowed) );
1703 WSH_DEBUG_SV.pop(l_module_name);
1704 END IF;
1705 FND_MESSAGE.SET_NAME('WSH', 'WSH_LINE_CANCEL_NOT_ALLOWED');
1706 FND_MSG_PUB.ADD;
1707 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1708 , p_data => x_msg_data );
1709 x_return_status := fnd_api.g_ret_sts_error;
1710 return;
1711 END;
1712 --
1713 x_cancel_qty_allowed := l_cancel_qty_allowed;
1714 --
1715 IF l_debug_on THEN
1716 WSH_DEBUG_SV.log(l_module_name, 'l_cancel_qty_allowed: '||to_char(l_cancel_qty_allowed) );
1717 end if;
1718 --
1719 -- Need to do UOM conversion of the Quantity if SRc and REq. differ.
1720 -- and if there are any Cancellable Qtys.
1721 --
1722 IF ( (nvl(l_cancel_qty_allowed,0) > 0) and (l_src_requested_quantity_uom <> l_requested_quantity_uom) ) then
1723 --{
1724 l_src_cancel_qty_allowed := WSH_WV_UTILS.convert_uom(from_uom => l_requested_quantity_uom,
1725 to_uom => l_src_requested_quantity_uom,
1726 quantity => l_cancel_qty_allowed ,
1727 item_id => l_inventory_item_id );
1728 --
1729 x_cancel_qty_allowed := l_src_cancel_qty_allowed;
1730 --
1731 IF l_debug_on THEN
1732 WSH_DEBUG_SV.log(l_module_name, 'SRC Req. Qty. UOM: '||l_src_requested_quantity_uom );
1733 WSH_DEBUG_SV.log(l_module_name, 'Req. Qty. UOM: '||l_requested_quantity_uom );
1734 WSH_DEBUG_SV.log(l_module_name, 'SRC Req. Qty. allowed to be cancelled: '||l_src_cancel_qty_allowed);
1735 end if;
1736 END IF; --}
1737 --
1738
1739 IF l_debug_on THEN
1740 WSH_DEBUG_SV.log(l_module_name, 'Req. Qty. allowed to be cancelled'||to_char(x_cancel_qty_allowed) );
1741 WSH_DEBUG_SV.log(l_module_name, 'x_return_status '||x_return_status);
1742 WSH_DEBUG_SV.pop(l_module_name);
1743 END IF;
1744
1745 EXCEPTION
1746
1747 WHEN OTHERS THEN
1748 FND_MESSAGE.Set_Name('WSH','WSH_UNEXPECTED_ERROR');
1749 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1750
1751 FND_MSG_PUB.Count_And_Get
1752 ( p_count => x_msg_count
1753 , p_data => x_msg_data
1754 ,p_encoded => FND_API.G_FALSE
1755 );
1756
1757 IF l_debug_on THEN
1758 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
1759 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1760 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1761 END IF;
1762
1763 END Get_Cancel_Qty_Allowed;
1764 --
1765 --bug #8590113 :
1766 --===================================================================================================
1767 -- Start of comments
1768 --
1769 -- API Name : Get_Delivery_Detail_attributes
1770 -- Type : Public
1771 -- Purpose : To fetch all the delivery details attributes along with parent_delivery_Detail_id(container)
1772 -- Pre-reqs : None
1773 -- Function : This API can be used to get all the attributes of delivery details along with container details
1774 --
1775 --
1776 -- PARAMETERS : p_header_id header_id of the Sales Order
1777 -- p_line_id line_id of the Sales Order
1778 -- x_rec_tab Return all the delivery details attributes in following format
1779 -- x_rec_tab.detail_rec_type - dellivery details attributes record
1780 -- x_rec_tab.parent_delivery_detail_id - container delivery_detail_id
1781 --
1782 -- x_return_status return status
1783 -- VERSION : current version 1.0
1784 -- initial version 1.0
1785 -- End of comments
1786 --===================================================================================================
1787
1788
1789 PROCEDURE Get_Delivery_Detail_attributes ( p_header_id IN NUMBER,
1790 p_line_id IN NUMBER,
1791 x_rec_tab OUT NOCOPY WSH_INTEGRATION.detail_lpn_rec_type_tab_type,
1792 x_return_status OUT NOCOPY VARCHAR2)IS
1793
1794 l_debug_on BOOLEAN;
1795 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.'|| G_PKG_NAME || '.'|| ' Get_Delivery_Detail_attributes';
1796 l_return_status VARCHAR2(1);
1797 l_wdd_lpn_tab detail_lpn_rec_type_tab_type;
1798 ind NUMBER := 0;
1799 l_actual_departure_date DATE;
1800 l_ship_method_meaning VARCHAR2(240);
1801 l_carrier_name VARCHAR2(360);
1802 l_temp_line_id NUMBER :=0 ;
1803
1804 --Get the delivery details and associated container delivery details records for the order line id passed
1805 CURSOR c_wdd_lpn_for_line (l_line_id NUMBER ) IS
1806 SELECT wdd.*
1807 FROM wsh_delivery_assignments wda,wsh_delivery_details wdd
1808 WHERE wdd.delivery_detail_id = wda.delivery_detail_id
1809 START WITH wda.delivery_detail_id in
1810 ( SELECT delivery_detail_id
1811 FROM wsh_delivery_details
1812 WHERE source_line_id = l_line_id
1813 AND source_code = 'OE')
1814 CONNECT BY PRIOR wda.parent_delivery_detail_id = wda.DELIVERY_DETAIL_ID ;
1815
1816 l_line_ids wsh_util_core.id_tab_type; --for storing line_ids if only header_id is passed in package.
1817
1818 --Get the line_id assoicated to the header_id
1819 CURSOR c_line_id(l_header_id NUMBER ) IS
1820 SELECT DISTINCT source_line_id
1821 FROM wsh_delivery_details
1822 WHERE source_header_id = l_header_id
1823 AND source_code = 'OE';
1824
1825 --Get the parent_delivery_detail_id for delivery detail
1826 CURSOR c_assignment(l_delivery_detail_id NUMBER ) IS
1827 SELECT parent_delivery_detail_id
1828 FROM wsh_delivery_assignments
1829 WHERE delivery_detail_id =l_delivery_detail_id ;
1830
1831 ----Get the actual departure date
1832 CURSOR c_actual_ship_date(l_line_id NUMBER ) IS
1833 SELECT wts.actual_departure_date,wcs.ship_method_meaning,hp.party_name
1834 FROM wsh_delivery_details wdd ,
1835 wsh_delivery_assignments wda ,
1836 wsh_new_deliveries wnd ,
1837 wsh_delivery_legs wdl ,
1838 wsh_trip_stops wts,
1839 wsh_trips wt,
1840 wsh_carrier_services wcs,
1841 hz_parties hp
1842 WHERE wdd.source_line_id = l_line_id
1843 AND wdd.delivery_detail_id = wda.delivery_detail_id
1844 AND wda.delivery_id = wnd.delivery_id
1845 AND wda.delivery_id = wdl.delivery_id
1846 AND wdl.pick_up_stop_id = wts.stop_id
1847 AND wnd.initial_pickup_location_id = wts.stop_location_id
1848 AND wts.trip_id = wt.trip_id
1849 AND wt.carrier_id = hp.party_id (+)
1850 AND wt.ship_method_code = wcs.ship_method_code (+)
1851 AND ROWNUM = 1;
1852
1853 NO_VALUE_PASSED exception;
1854
1855 BEGIN
1856
1857 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1858 IF l_debug_on IS NULL THEN
1859 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1860 END IF;
1861
1862 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1863
1864 IF l_debug_on THEN
1865 WSH_DEBUG_SV.push(l_module_name);
1866 WSH_DEBUG_SV.log(l_module_name,'header_id ',p_header_id);
1867 WSH_DEBUG_SV.log(l_module_name,'line_id ',p_line_id);
1868 END IF;
1869
1870 --If line_id is passed as i/p
1871 IF p_line_id IS NOT NULL THEN
1872 --{
1873 l_line_ids(1) := p_line_id ;
1874 --}
1875 --If only header_id is passed as i/p
1876 ELSIF p_header_id IS NOT NULL THEN
1877 --{
1878 OPEN c_line_id(p_header_id);
1879 FETCH c_line_id BULK COLLECT INTO l_line_ids;
1880 CLOSE c_line_id;
1881 --}
1882 ELSE
1883 --{
1884 RAISE NO_VALUE_PASSED;
1885 --}
1886 END IF;
1887
1888 FOR i in 1..l_line_ids.COUNT LOOP
1889 --{
1890 --Get the actual departure date
1891 --bugfix 9883511 - assigned null value to variables
1892 l_actual_departure_date := NULL;
1893 l_ship_method_meaning := NULL ;
1894 l_carrier_name := NULL;
1895
1896 OPEN c_actual_ship_date(l_line_ids(i)) ;
1897 FETCH c_actual_ship_date INTO l_actual_departure_date,l_ship_method_meaning,l_carrier_name;
1898 CLOSE c_actual_ship_date;
1899
1900 IF l_debug_on THEN
1901 WSH_DEBUG_SV.log(l_module_name,'********** Line_id loop for line_id ',l_line_ids(i));
1902 WSH_DEBUG_SV.log(l_module_name,'actual ship date',l_actual_departure_date);
1903 WSH_DEBUG_SV.log(l_module_name,'Ship Method',l_ship_method_meaning);
1904 WSH_DEBUG_SV.log(l_module_name,'Carrier Name',l_carrier_name);
1905 END IF;
1906
1907 --Get the delivery detail and associated container records
1908 FOR wdd_lpn_rec IN c_wdd_lpn_for_line(l_line_ids(i)) loop
1909 --{
1910 x_rec_tab(ind).detail_rec_type:= wdd_lpn_rec;
1911
1912 --Assign the actual departure date, ship method and carrier name
1913 x_rec_tab(ind).actual_ship_date := l_actual_departure_date;
1914 x_rec_tab(ind).Ship_method := l_ship_method_meaning;
1915 x_rec_tab(ind).carrier_name := l_carrier_name;
1916
1917 --Get the parent_delivery_details_id for the delivery detail
1918 OPEN c_assignment(x_rec_tab(ind).detail_rec_type.delivery_detail_id) ;
1919 FETCH c_assignment INTO x_rec_tab(ind).parent_delivery_detail_id ;
1920 CLOSE c_assignment;
1921
1922
1923 IF l_debug_on THEN
1924 WSH_DEBUG_SV.log(l_module_name,'wdd_lpn_rec loop count ',ind + 1);
1925 WSH_DEBUG_SV.log(l_module_name,'delivery_detail_id ',x_rec_tab(ind).detail_rec_type.delivery_detail_id);
1926 WSH_DEBUG_SV.log(l_module_name,'parent_delivery_detail_id ',x_rec_tab(ind).parent_delivery_detail_id);
1927 END IF;
1928 ind := ind+1;
1929 --}
1930 END LOOP;
1931 --}
1932 END LOOP ;
1933
1934
1935 IF l_debug_on THEN
1936 WSH_DEBUG_SV.log(l_module_name,'Total records count ',x_rec_tab.count);
1937 WSH_DEBUG_SV.pop(l_module_name);
1938 END IF;
1939
1940 EXCEPTION
1941 WHEN NO_VALUE_PASSED THEN
1942 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1943 wsh_util_core.printMsg('line_id and header_id both are not passed');
1944
1945 IF c_line_id%ISOPEN THEN
1946 close c_line_id;
1947 END IF;
1948
1949 IF c_wdd_lpn_for_line%ISOPEN THEN
1950 close c_wdd_lpn_for_line;
1951 END IF;
1952
1953 IF c_assignment%ISOPEN THEN
1954 close c_assignment;
1955 END IF;
1956
1957 IF c_actual_ship_date%ISOPEN THEN
1958 close c_actual_ship_date;
1959 END IF;
1960
1961 IF l_debug_on THEN
1962 WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR IN Get_Delivery_Detail_attributes :line_id and header_id both are not passed' );
1963 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:NO_VALUE_PASSED');
1964 END IF;
1965
1966
1967 WHEN OTHERS THEN
1968 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1969 wsh_util_core.printMsg('API Get_Delivery_Detail_attributes failed with an unexpected error');
1970 WSH_UTIL_CORE.PrintMsg('The unexpected error is '|| sqlerrm);
1971
1972 IF c_line_id%ISOPEN THEN
1973 close c_line_id;
1974 END IF;
1975
1976 IF c_wdd_lpn_for_line%ISOPEN THEN
1977 close c_wdd_lpn_for_line;
1978 END IF;
1979
1980 IF c_assignment%ISOPEN THEN
1981 close c_assignment;
1982 END IF;
1983
1984 IF c_actual_ship_date%ISOPEN THEN
1985 close c_actual_ship_date;
1986 END IF;
1987
1988 IF l_debug_on THEN
1989 WSH_DEBUG_SV.logmsg(l_module_name, 'UNEXPECTED ERROR IN Get_Delivery_Detail_attributes' );
1990 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1991 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1992 END IF;
1993
1994 END Get_Delivery_Detail_attributes;
1995 --
1996 --
1997 -- LSP project : new API
1998 --
1999 --===================================================================================================
2000 -- Start of comments
2001 --
2002 -- API Name : Validate_Oe_Attributes
2003 -- Type : Private
2004 -- Purpose : To determine whether the validation of Sales order/order line
2005 -- attribute is required or not.
2006 -- Pre-reqs : None
2007 -- Function : This API returns 'N' when the
2008 -- a) Deployment mode is Distributed
2009 -- b) Deployment Mode is LSP and Order Source is equal to any of the valid client code
2010 -- For all other cases this API returns 'Y'
2011 --
2012 --
2013 -- PARAMETERS : p_order_source_id Order number of the Sales Order
2014 -- x_return_status 'Y' : OM should validate attributes, 'N': OM can ignore the validation.
2015 -- VERSION : current version 1.0
2016 -- initial version 1.0
2017 -- End of comments
2018 --===================================================================================================
2019 FUNCTION Validate_Oe_Attributes (p_order_source_id IN NUMBER) RETURN VARCHAR2 IS
2020 CURSOR c_check_client IS
2021 SELECT
2022 'N'
2023 FROM
2024 oe_order_sources oos,
2025 mtl_client_parameters mcp
2026 WHERE oos.order_source_id = p_order_source_id
2027 AND mcp.client_code = oos.name;
2028
2029 l_return_status VARCHAR2(1) := 'Y';
2030 --
2031 l_debug_on BOOLEAN;
2032 --
2033 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Validate_Oe_Attributes';
2034 BEGIN
2035 --
2036 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2037 --
2038 IF l_debug_on IS NULL THEN
2039 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2040 END IF;
2041 --
2042 IF l_debug_on THEN
2043 WSH_DEBUG_SV.push(l_module_name);
2044 WSH_DEBUG_SV.log(l_module_name,'p_order_source_id',p_order_source_id);
2045 END IF;
2046 IF WMS_DEPLOY.WMS_DEPLOYMENT_MODE = 'D' THEN
2047 --{
2048 l_return_status := 'N';
2049 ELSIF ( WMS_DEPLOY.WMS_DEPLOYMENT_MODE = 'L' AND p_order_source_id is NOT NULL ) THEN
2050 OPEN c_check_client;
2051 FETCH c_check_client INTO l_return_status;
2052 CLOSE c_check_client;
2053 l_return_status := nvl(l_return_status,'Y');
2054 --}
2055 END IF;
2056 IF l_debug_on THEN
2057 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
2058 WSH_DEBUG_SV.pop(l_module_name);
2059 END IF;
2060 RETURN l_return_status;
2061 EXCEPTION
2062 WHEN OTHERS THEN
2063 FND_MESSAGE.Set_Name('WSH','WSH_UNEXPECTED_ERROR');
2064 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR,l_module_name);
2065 IF l_debug_on THEN
2066 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
2067 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2068 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2069 END IF;
2070 END Validate_Oe_Attributes;
2071 --
2072 -- LSP project : end
2073 --
2074 --RTV changes
2075 --
2076 -- Procedure: Update_Delivery_Line
2077 -- Parameters: list of Delivery Lines that need to be updated
2078 -- Description: This procedure will update inv_interface_flag of
2079 -- a delivery line to 'Y'
2080 --
2081
2082 PROCEDURE Update_Delivery_Details(
2083 p_detail_rows IN wsh_util_core.id_tab_type,
2084 x_return_status OUT NOCOPY VARCHAR2) IS
2085
2086 others EXCEPTION;
2087 l_debug_on BOOLEAN;
2088 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_DELIVERY_DETAILS';
2089 --
2090 BEGIN
2091 --
2092 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2093 --
2094 IF l_debug_on IS NULL
2095 THEN
2096 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2097 END IF;
2098 --
2099
2100 IF l_debug_on THEN
2101 WSH_DEBUG_SV.push(l_module_name);
2102 END IF;
2103 --
2104 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2105
2106 IF (p_detail_rows.count = 0) THEN
2107 raise others;
2108 END IF;
2109
2110 FOR i IN 1..p_detail_rows.count LOOP
2111
2112 IF l_debug_on THEN
2113 wsh_debug_sv.log(l_module_name,'delivery_detail_id', p_detail_rows(i));
2114 END IF;
2115
2116 update wsh_delivery_details dd
2117 set inv_interfaced_flag = 'Y',
2118 program_application_id = FND_GLOBAL.Prog_Appl_Id,
2119 program_id = FND_GLOBAL.Conc_Program_Id,
2120 request_id = FND_GLOBAL.Conc_Request_Id,
2121 program_update_date = sysdate,
2122 last_update_date = sysdate,
2123 last_updated_by = fnd_global.user_id,
2124 last_update_login = fnd_global.login_id
2125 where delivery_detail_id = p_detail_rows(i)
2126 and source_code NOT IN ('OE','OKE', 'WSH');
2127
2128 END LOOP;
2129
2130 IF l_debug_on THEN
2131 WSH_DEBUG_SV.pop(l_module_name);
2132 END IF;
2133
2134 EXCEPTION
2135
2136 WHEN others THEN
2137 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2138 wsh_util_core.default_handler('WSH_INTERFACE.UPDATE_DELIVERY_DETAILS',l_module_name);
2139 --
2140 IF l_debug_on THEN
2141 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2142 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2143 END IF;
2144 --
2145 END Update_Delivery_Details;
2146 --RTV changes
2147
2148 END WSH_INTEGRATION;
[Home] [Help]