1 PACKAGE BODY WSH_INTEGRATION as
2 /* $Header: WSHINTGB.pls 120.5.12010000.2 2008/08/04 12:31:18 suppal 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 --
1062 l_debug_on BOOLEAN;
1063 --
1064 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DBI_INSTALLED';
1065 BEGIN
1066 --
1067 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1068 --
1069 IF l_debug_on IS NULL THEN
1070 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1071 END IF;
1072 --
1073 IF l_debug_on THEN
1074 WSH_DEBUG_SV.push(l_module_name);
1075 END IF;
1076 --
1077 IF G_DBI_IS_INSTALLED IS NULL THEN
1078 G_DBI_IS_INSTALLED := (NVL(FND_PROFILE.VALUE('ISC_WSH_FTE_DBI_INSTALLED'), 'N'));
1079 END IF;
1080 IF l_debug_on THEN
1081 WSH_DEBUG_SV.log(l_module_name,'G_DBI_IS_INSTALLED',G_DBI_IS_INSTALLED);
1082 WSH_DEBUG_SV.pop(l_module_name);
1083 END IF;
1084 RETURN G_DBI_IS_INSTALLED ;
1085
1086 EXCEPTION
1087 WHEN OTHERS THEN
1088 FND_MESSAGE.Set_Name('WSH','WSH_UNEXPECTED_ERROR');
1089 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR,l_module_name);
1090 IF l_debug_on THEN
1091 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
1092 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1093 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1094 END IF;
1095 END DBI_Installed;
1096
1097 --========================================================
1098 -- DataType Conversion APIs , DBI Project
1099 -- 1. Convert WSH table of ids to DBI Table of ids
1100 --=========================================================
1101
1102 -- 1.Convert WSH Table of ids to DBI Table of ids
1103 PROCEDURE WSH_ID_TAB_TO_DBI_ID_TAB
1104 (p_wsh_id_tab IN WSH_UTIL_CORE.id_tab_type,
1105 x_dbi_id_tab OUT NOCOPY ISC_DBI_CHANGE_LOG_PKG.log_tab_type,
1106 x_return_status OUT NOCOPY VARCHAR2
1107 ) IS
1108
1109 --
1110 l_debug_on BOOLEAN;
1111 --
1112 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.'||G_PKG_NAME||'.'||'WSH_ID_TAB_TO_DBI_ID_TAB';
1113 BEGIN
1114 --
1115 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1116 --
1117 IF l_debug_on IS NULL THEN
1118 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1119 END IF;
1120 --
1121 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1122
1123 IF l_debug_on THEN
1124 WSH_DEBUG_SV.push(l_module_name);
1125 WSH_DEBUG_SV.log(l_module_name,'Count Input records',p_wsh_id_tab.count);
1126 END IF;
1127
1128 -- Conversion is required from WSH datatype to DBI datatype
1129 -- Use the same counter while adding records in x_dbi_tab
1130 FOR i in p_wsh_id_tab.FIRST..p_wsh_id_tab.LAST
1131 LOOP
1132 x_dbi_id_tab(i) := p_wsh_id_tab(i);
1133 IF l_debug_on THEN
1134 WSH_DEBUG_SV.log(l_module_name,'Id being converted-',p_wsh_id_tab(i));
1135 END IF;
1136 END LOOP;
1137
1138 IF l_debug_on THEN
1139 WSH_DEBUG_SV.log(l_module_name,'Count Output records',x_dbi_id_tab.count);
1140 WSH_DEBUG_SV.pop(l_module_name);
1141 END IF;
1142
1143 EXCEPTION
1144 WHEN OTHERS THEN
1145 FND_MESSAGE.Set_Name('WSH','WSH_UNEXPECTED_ERROR');
1146 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1147 wsh_util_core.add_message(x_return_status,l_module_name);
1148 IF l_debug_on THEN
1149 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
1150 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1151 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1152 END IF;
1153 END WSH_ID_TAB_TO_DBI_ID_TAB;
1154
1155 --===============================================
1156 -- Name : DBI_Update_Detail_Log
1157 -- Purpose: Call DBI for update in wsh_delivery_details table
1158 -- History: Added in 11i10+. Actions covered are
1159 -- 1. Update of Requested Quantity
1160 -- 2. Update of Requested Quantity UOM
1161 -- 3. Update of Released Status
1162 --
1163 -- Input Arguments:
1164 -- p_delivery_detail_tab - Table of Delivery Detail ids
1165 -- p_dml_type - DML type (INSERT/UPDATE/DELETE)
1166 -- Output Arguments:
1167 -- x_return_status - Return Status
1168 --
1169 --===============================================
1170 PROCEDURE DBI_Update_Detail_Log
1171 (p_delivery_detail_id_tab IN WSH_UTIL_CORE.id_tab_type,
1172 p_dml_type IN VARCHAR2,
1173 x_return_status OUT NOCOPY VARCHAR2) IS
1174
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';BEGIN
1180 --
1181 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1182 --
1183 IF l_debug_on IS NULL THEN
1184 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1185 END IF;
1186 --
1187 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1188 IF l_debug_on THEN
1189 WSH_DEBUG_SV.push(l_module_name);
1190 WSH_DEBUG_SV.log(l_module_name,'Before Calling DBI API-',x_return_status);
1191 WSH_DEBUG_SV.log(l_module_name,'Count Detail records-',p_delivery_detail_id_tab.count);
1192 WSH_DEBUG_SV.log(l_module_name,'DML Type-',p_dml_type);
1193 END IF;
1194 --
1195 -- Check if DBI is installed, possible values are Y or N only
1196 -- If not installed, then do not proceed , return Success
1197 -- Also, atleast 1 record should be populated in the Input table
1198 IF (WSH_INTEGRATION.DBI_Installed = 'N' OR
1199 p_delivery_detail_id_tab.count < 1)
1200 THEN
1201 IF l_debug_on THEN
1202 WSH_DEBUG_SV.log(l_module_name,'DBI Installed flag-',WSH_INTEGRATION.DBI_Installed);
1203 WSH_DEBUG_SV.pop(l_module_name);
1204 END IF;
1205 RETURN;
1206 END IF;
1207 --
1208 -- Conversion is required from WSH datatype to DBI datatype
1209 WSH_ID_TAB_TO_DBI_ID_TAB
1210 (p_wsh_id_tab => p_delivery_detail_id_tab,
1211 x_dbi_id_tab => l_dbi_detail_list,
1212 x_return_status => x_return_status);
1213
1214 IF x_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1215 IF l_debug_on THEN
1216 WSH_DEBUG_SV.log(l_module_name,'Count Detail records-',l_dbi_detail_list.count);
1217 WSH_DEBUG_SV.log(l_module_name,'Before Calling DBI API-',x_return_status);
1218 END IF;
1219 --
1220 ISC_DBI_CHANGE_LOG_PKG.Update_Del_Detail_Log
1221 (p_detail_list => l_dbi_detail_list,
1222 p_dml_type => p_dml_type,
1223 x_return_status => x_return_status
1224 );
1225 --
1226 -- Only Unexpected error can be raised from DBI API,
1227 -- all others have to be treated as success and code flow
1228 -- will continue and not rollback
1229 IF x_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
1230 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1231 FND_MESSAGE.SET_NAME('WSH','WSH_INCOMPLETE_TRANSACTION');
1232 WSH_UTIL_CORE.Add_Message(x_return_status);
1233 ELSE -- all other Statuses are equivalent to Success
1234 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1235 END IF;
1236 --
1237 END IF;
1238
1239 IF l_debug_on THEN
1240 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
1241 WSH_DEBUG_SV.pop(l_module_name);
1242 END IF;
1243
1244 EXCEPTION
1245 WHEN OTHERS THEN
1246 FND_MESSAGE.Set_Name('WSH','WSH_UNEXPECTED_ERROR');
1247 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1248 wsh_util_core.add_message(x_return_status,l_module_name);
1249
1250 IF l_debug_on THEN
1251 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
1252 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1253 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1254 END IF;
1255 END DBI_Update_Detail_Log;
1256
1257
1258 --===============================================
1259 -- Name : DBI_Update_Trip_Stop_Log
1260 -- Purpose: Call DBI for creation/deletion/update of Stop,Delivery Leg
1261 -- History: Added in 11i10+. Actions covered are
1262 -- 1. Create Trip Stop
1263 -- 2. Delete Trip Stop
1264 -- 3. Update of Stop Actual Departure Date
1265 -- 4. Update of Stop Actual Arrival Date
1266 -- 5. Update of Stop Status
1267 -- 6. Update of Planned Arrival Date
1268 -- 7. Assign Delivery to Trip(create delivery leg)
1269 -- 8. Unassign Delivery from Trip(delete delivery leg)
1270 -- 9. Update of Freight Cost of delivery leg
1271 -- 10. Change in Trip Status (corresponds to change in Stop status)
1272 --
1273 -- Input Arguments:
1274 -- p_delivery_detail_tab - Table of Delivery Detail ids
1275 -- p_dml_type - DML type (INSERT/UPDATE/DELETE)
1276 -- Output Arguments:
1277 -- x_return_status - Return Status
1278 --
1279 --===============================================
1280 PROCEDURE DBI_Update_Trip_Stop_Log
1281 (p_stop_id_tab IN WSH_UTIL_CORE.id_tab_type,
1282 p_dml_type IN VARCHAR2,
1283 x_return_status OUT NOCOPY VARCHAR2) IS
1284
1285 l_dbi_stop_list ISC_DBI_CHANGE_LOG_PKG.log_tab_type;
1286 --
1287 l_debug_on BOOLEAN;
1288 --
1289 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.'||G_PKG_NAME||'.'||'DBI_UPDATE_TRIP_STOP_LOG';
1290 BEGIN
1291 --
1292 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1293 --
1294 IF l_debug_on IS NULL THEN
1295 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1296 END IF;
1297 --
1298 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1299 IF l_debug_on THEN
1300 WSH_DEBUG_SV.push(l_module_name);
1301 WSH_DEBUG_SV.log(l_module_name,'Count Stop records-',p_stop_id_tab.count);
1302 WSH_DEBUG_SV.log(l_module_name,'DML Type-',p_dml_type);
1303 END IF;
1304 --
1305 -- Check if DBI is installed, possible values are Y or N only
1306 -- If not installed, then do not proceed , return Success
1307 -- Also, atleast 1 record should be populated in the Input table
1308 IF (WSH_INTEGRATION.DBI_Installed = 'N' OR
1309 p_stop_id_tab.count < 1)
1310 THEN
1311 IF l_debug_on THEN
1312 WSH_DEBUG_SV.log(l_module_name,'DBI Installed flag-',WSH_INTEGRATION.DBI_Installed);
1313 WSH_DEBUG_SV.pop(l_module_name);
1314 END IF;
1315 RETURN;
1316 END IF;
1317 --
1318 -- Conversion is required from WSH datatype to DBI datatype
1319 WSH_ID_TAB_TO_DBI_ID_TAB
1320 (p_wsh_id_tab => p_stop_id_tab,
1321 x_dbi_id_tab => l_dbi_stop_list,
1322 x_return_status => x_return_status);
1323
1324 IF x_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1325
1326 IF l_debug_on THEN
1327 WSH_DEBUG_SV.log(l_module_name,'Count Stop records-',l_dbi_stop_list.count);
1328 WSH_DEBUG_SV.log(l_module_name,'Before Calling DBI API-',x_return_status);
1329 END IF;
1330 --
1331 ISC_DBI_CHANGE_LOG_PKG.Update_Trip_Stop_Log
1332 (p_stop_list => l_dbi_stop_list,
1333 p_dml_type => p_dml_type,
1334 x_return_status => x_return_status
1335 );
1336 --
1337 -- Only Unexpected error can be raised from DBI API,
1338 -- all others have to be treated as success and code flow
1339 -- will continue and not rollback
1340 IF x_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
1341 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1342 FND_MESSAGE.SET_NAME('WSH','WSH_INCOMPLETE_TRANSACTION');
1343 WSH_UTIL_CORE.Add_Message(x_return_status);
1344 ELSE -- all other Statuses are equivalent to Success
1345 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1346 END IF;
1347 --
1348 END IF;
1349
1350 IF l_debug_on THEN
1351 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
1352 WSH_DEBUG_SV.pop(l_module_name);
1353 END IF;
1354
1355 EXCEPTION
1356 WHEN OTHERS THEN
1357 FND_MESSAGE.Set_Name('WSH','WSH_UNEXPECTED_ERROR');
1358 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1359 wsh_util_core.add_message(x_return_status,l_module_name);
1360
1361 IF l_debug_on THEN
1362 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
1363 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1364 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1365 END IF;
1366
1367 END DBI_Update_Trip_Stop_Log;
1368
1369 -- X-dock
1370 -- Procedure Name : Find_Matching_Groups
1371 -- Description : This API will find entities (deliveries/containers) that
1372 -- match the grouping criteria of the input table of entities.
1373 -- p_attr_tab : Table of entities or record of grouping criteria that need to be matched.
1374 -- p_action_rec : Record of specific actions and their corresponding parameters.
1375 -- check_single_grp_only: ('Y', 'N') will check only of the records can be
1376 -- grouped together.
1377 -- output_entity_type: ('DLVY', 'CONT') the entity type that the input records
1378 -- need to be matched with.
1379 -- output_format_type: Format of the output.
1380 -- 'ID_TAB': table of id's of the matched entities
1381 -- 'TEMP_TAB': The output will be inserted into wsh_temp (wsh_temp
1382 -- needs to be cleared after this API has been used).
1383 -- 'SQL_STRING': Will return a SQL query to find the matching entities
1384 -- as a string and values of the variables that will
1385 -- need to be bound to the string.
1386 -- p_target_rec : Entity or grouping attributes that need to be matched with (if necessary)
1387 -- x_matched_entities: table of ids of the matched entities
1388 -- x_out_rec : Record of output values based on the actions and output format.
1389 -- query_string: String to query for matching entities.
1390 -- x_return_status : 'S', 'E', 'U'.
1391
1392 procedure Find_Matching_Groups
1393 (p_attr_tab IN OUT NOCOPY GRP_ATTR_TAB_TYPE,
1394 p_action_rec IN ACTION_REC_TYPE,
1395 p_target_rec IN GRP_ATTR_REC_TYPE,
1396 p_group_tab IN OUT NOCOPY GRP_ATTR_TAB_TYPE,
1397 x_matched_entities OUT NOCOPY WSH_UTIL_CORE.ID_TAB_TYPE,
1398 x_out_rec OUT NOCOPY OUT_REC_TYPE,
1399 x_return_status OUT NOCOPY VARCHAR2) IS
1400
1401 -- Local variables to move information from or to the variables
1402 l_attr_tab WSH_DELIVERY_AUTOCREATE.grp_attr_tab_type;
1403 l_action_rec WSH_DELIVERY_AUTOCREATE.action_rec_type;
1404 l_target_rec WSH_DELIVERY_AUTOCREATE.grp_attr_rec_type;
1405 l_group_tab WSH_DELIVERY_AUTOCREATE.grp_attr_tab_type;
1406 l_out_rec WSH_DELIVERY_AUTOCREATE.out_rec_type;
1407 l_del_det_id WSH_DELIVERY_DETAILS.delivery_detail_id%type;
1408 l_org_id WSH_DELIVERY_DETAILS.organization_id%type;
1409 l_param_info WSH_SHIPPING_PARAMS_PVT.Parameter_Rec_Typ;
1410
1411 l_debug_on BOOLEAN;
1412 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Find_Matching_Groups';
1413
1414 BEGIN
1415
1416 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1417 --
1418 IF l_debug_on IS NULL
1419 THEN
1420 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1421 END IF;
1422 --
1423 IF l_debug_on THEN
1424 WSH_DEBUG_SV.push(l_module_name);
1425 WSH_DEBUG_SV.log(l_module_name, 'p_action_rec.action', p_action_rec.action);
1426 WSH_DEBUG_SV.log(l_module_name, 'p_action_rec.output_format_type', p_action_rec.output_format_type);
1427 WSH_DEBUG_SV.log(l_module_name, 'p_attr_tab count' , p_attr_tab.count);
1428 WSH_DEBUG_SV.log(l_module_name, 'p_group_tab count', p_group_tab.count);
1429 WSH_DEBUG_SV.log(l_module_name, 'p_target_rec.entity_type', p_target_rec.entity_type);
1430 END IF;
1431
1432 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1433
1434 --
1435 -- Wrapper on top of WSHDEAUB.find_matching_groups
1436 --
1437 l_attr_tab.delete;
1438 l_group_tab.delete;
1439
1440 -- Move information from p_attr_tab to l_attr_tab
1441 l_attr_tab := p_attr_tab;
1442
1443 -- Move information from p_action_rec to l_p_action_rec
1444 l_action_rec := p_action_rec;
1445
1446 -- Move information from p_target_rec to l_p_target_rec
1447 l_target_rec := p_target_rec;
1448
1449 -- Move information from p_group_tab to l_group_tab
1450 IF p_group_tab.COUNT > 0 THEN
1451 l_group_tab := p_group_tab;
1452 END IF;
1453
1454 --Bug : 6911078 : Check for Appending_limit to get the matching deliveries : Start
1455 IF l_action_rec.action = 'MATCH_GROUPS' AND l_target_rec.entity_type = 'DELIVERY' THEN
1456 --{
1457 IF l_debug_on THEN
1458 --{
1459 WSH_DEBUG_SV.log(l_module_name, 'p_attr_tab.count for ''MATCH_GROUPS'' is ', l_attr_tab.COUNT);
1460 --}
1461 END IF;
1462
1463 IF l_attr_tab.COUNT > 1 THEN
1464 --{
1465 x_return_status := FND_API.G_RET_STS_ERROR;
1466 IF l_debug_on THEN
1467 --{
1468 WSH_DEBUG_SV.log(l_module_name, 'p_attr_tab.count for ''MATCH_GROUPS'' > 1 and hence exiting..');
1469 WSH_DEBUG_SV.pop(l_module_name);
1470 --}
1471 END IF;
1472 RETURN;
1473 --}
1474 END IF;
1475
1476 l_del_det_id := l_attr_tab(l_attr_tab.FIRST).entity_id ;
1477
1478 -- Check for delivery_detail_id > 0
1479 IF NVL(l_del_det_id,0) > 0 THEN
1480 --{
1481 -- Delivery appending limit 'N' check goes in this block.
1482 BEGIN
1483
1484 SELECT organization_id INTO l_org_id FROM wsh_delivery_details WHERE delivery_detail_id = l_del_det_id;
1485 -- Call wsh_shipping_parameters.Get for appending_limit
1486 IF l_debug_on THEN
1487 --{
1488 WSH_DEBUG_SV.log(l_module_name, 'l_org_id', l_org_id);
1489 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_SHIPPING_PARAMS_PVT.GET',WSH_DEBUG_SV.C_PROC_LEVEL);
1490 --}
1491 END IF;
1492
1493 WSH_SHIPPING_PARAMS_PVT.GET(p_organization_id => l_org_id,
1494 x_param_info => l_param_info,
1495 x_return_status => x_return_status);
1496
1497 IF x_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ) THEN
1498 --{
1499 IF l_debug_on THEN
1500 --{
1501 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
1502 WSH_DEBUG_SV.pop(l_module_name);
1503 --}
1504 END IF;
1505 RETURN;
1506 --}
1507 END IF;
1508
1509 IF l_debug_on THEN
1510 --{
1511 WSH_DEBUG_SV.log(l_module_name, 'Appending Limit', l_param_info.APPENDING_LIMIT );
1512 --}
1513 END IF;
1514
1515 IF l_param_info.APPENDING_LIMIT = 'N' THEN
1516 --{
1517 IF l_debug_on THEN
1518 --{
1519 WSH_DEBUG_SV.log(l_module_name, 'Append Limit is ''Do not append'' and hence exiting..');
1520 WSH_DEBUG_SV.pop(l_module_name);
1521 --}
1522 END IF;
1523 RETURN;
1524 --}
1525 END IF;
1526
1527 EXCEPTION
1528 WHEN NO_DATA_FOUND THEN
1529 x_return_status := FND_API.G_RET_STS_ERROR;
1530 IF l_debug_on THEN
1531 --{
1532 WSH_DEBUG_SV.log(l_module_name, 'Delivery Detail doesnt exists in WDD', l_del_det_id);
1533 WSH_DEBUG_SV.pop(l_module_name);
1534 --}
1535 END IF;
1536 RETURN;
1537
1538 WHEN OTHERS THEN
1539 x_return_status := FND_API.G_RET_STS_ERROR;
1540 IF l_debug_on THEN
1541 --{
1542 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1543 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1544 --}
1545 END IF;
1546 RETURN;
1547 END;
1548
1549 ELSE -- Delivery Detail is Invalid
1550 x_return_status := FND_API.G_RET_STS_ERROR;
1551 IF l_debug_on THEN
1552 --{
1553 WSH_DEBUG_SV.logmsg(l_module_name,'Invalid Delivery_detail_id',l_del_det_id);
1554 WSH_DEBUG_SV.pop(l_module_name);
1555 --}
1556 END IF;
1557 RETURN;
1558 --}
1559 END IF;
1560 -- Check for delivery_detail_id > 0
1561 END IF;
1562 --Bug : 6911078 : Check for Appending_limit to get the matching deliveries : End
1563
1564 WSH_DELIVERY_AUTOCREATE.FIND_MATCHING_GROUPS
1565 (p_attr_tab => l_attr_tab,
1566 p_action_rec => l_action_rec,
1567 p_target_rec => l_target_rec,
1568 p_group_tab => l_group_tab,
1569 x_matched_entities => x_matched_entities,
1570 x_out_rec => l_out_rec,
1571 x_return_status => x_return_status);
1572
1573 IF x_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ) THEN
1574 IF l_debug_on THEN
1575 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
1576 WSH_DEBUG_SV.pop(l_module_name);
1577 END IF;
1578 RETURN;
1579 END IF;
1580
1581 -- Move information from l_attr_tab back to p_attr_tab, IN OUT variable
1582 p_attr_tab := l_attr_tab;
1583 -- Move informaiton from l_group_tab back to p_group_tab, IN OUT variable
1584 p_group_tab := l_group_tab;
1585
1586 -- Move information from l_out_rec to x_out_rec
1587 x_out_rec := l_out_rec;
1588
1589 IF l_debug_on THEN
1590 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
1591 wsh_debug_sv.pop(l_module_name);
1592 END IF;
1593
1594 EXCEPTION
1595 WHEN OTHERS THEN
1596 x_return_status := wsh_util_core.g_ret_sts_unexp_error;
1597 wsh_util_core.default_handler('WSH_INTEGRATION.FIND_MATCHING_GROUPS',l_module_name);
1598 IF l_debug_on THEN
1599 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
1600 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1601 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1602 END IF;
1603
1604 END find_matching_groups;
1605
1606 -- end of X-dock changes
1607
1608 -- 5870774
1609 -- Procedure Get_Cancel_Qty_Allowed is to get the Unshipped Qty. on any Source Line id that
1610 -- is allowed to be Cancelled. The Unshipped Qty. is from all the underlying Delivery Details
1611 -- that do not belong to Any Delivery that is either in 'CO'nfirmed, 'CL'osed or 'InTransit' status
1612 -- Input :
1613 -- p_source_code -- Source Code of the Application Calling this Procedure
1614 -- e.g. 'OKE'
1615 -- p_source_line_id -- Source Line id of Source Line for which the Qty. cancellable is to be
1616 -- determined
1617 --
1618 -- Output :
1619 -- x_cancel_qty_allowed -- Qty. that is allowed to be Cancelled in Source Ordered Qty. UOM
1620 -- If there is No Qty. that can be cancelled, then this parameter will return 0 (Zero)
1621 --
1622 -- x_return_status -- Return Status [ Fnd api- Return Status: Sucess, Error , UnExpected Error ]
1623 -- x_msg_count,data -- Number of Messages, Message Data Stored
1624 PROCEDURE Get_Cancel_Qty_Allowed
1625 ( p_source_code IN VARCHAR2,
1626 p_source_line_id IN NUMBER,
1627 x_cancel_qty_allowed OUT NOCOPY NUMBER,
1628 x_return_status OUT NOCOPY VARCHAR2,
1629 x_msg_count OUT NOCOPY NUMBER,
1630 x_msg_data OUT NOCOPY VARCHAR2
1631 )
1632 IS
1633 --
1634 l_cancel_qty_allowed number;
1635 l_src_cancel_qty_allowed number;
1636 l_requested_quantity_uom wsh_delivery_details.src_requested_quantity_uom%type;
1637 l_src_requested_quantity_uom wsh_delivery_details.src_requested_quantity_uom%type;
1638 l_cancel_allowed_quantity number;
1639 l_inventory_item_id number;
1640 --
1641 l_debug_on BOOLEAN;
1642 --
1643 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_CANCEL_QTY_ALLOWED';
1644 --
1645 BEGIN
1646 --
1647 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1648 --
1649 IF l_debug_on IS NULL
1650 THEN
1651 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1652 END IF;
1653 --
1654 IF l_debug_on THEN
1655 WSH_DEBUG_SV.push(l_module_name);
1656 WSH_DEBUG_SV.log(l_module_name,'source_code ',p_source_code);
1657 WSH_DEBUG_SV.log(l_module_name,'src line id ',p_source_line_id);
1658 END IF;
1659
1660 IF ( p_source_code IS NULL )
1661 THEN
1662 x_msg_count := 1;
1663 x_msg_data := 'INVALID SOURCE_CODE';
1664 x_return_status := FND_API.G_RET_STS_ERROR;
1665 IF l_debug_on THEN
1666 WSH_DEBUG_SV.log(l_module_name,'Invalid source code: ', p_source_code);
1667 WSH_DEBUG_SV.pop(l_module_name);
1668 END IF;
1669 return;
1670 END IF;
1671
1672 x_return_status := FND_API.G_RET_STS_SUCCESS;
1673
1674 BEGIN
1675 -- select stmt.
1676
1677 select sum(wdd.requested_quantity) , requested_quantity_uom, src_requested_quantity_uom, inventory_item_id
1678 into l_cancel_qty_allowed , l_requested_quantity_uom, l_src_requested_quantity_uom, l_inventory_item_id
1679 from wsh_delivery_details wdd
1680 where
1681 wdd.source_line_id = p_source_line_id
1682 and wdd.source_code = p_source_code
1683 and not exists (select 'x' from
1684 wsh_delivery_assignments wda,
1685 wsh_new_deliveries wnd
1686 where
1687 wda.delivery_detail_id = wdd.delivery_detail_id
1688 and wda.delivery_id = wnd.delivery_id
1689 and wnd.status_code in ('CL','CO', 'IT') )
1690 group by requested_quantity_uom, src_requested_quantity_uom, inventory_item_id;
1691 --
1692 EXCEPTION
1693 WHEN NO_DATA_FOUND THEN
1694 x_cancel_qty_allowed := 0;
1695 IF l_debug_on THEN
1696 WSH_DEBUG_SV.log(l_module_name, 'No Data Found. Req. Qty. allowed to be cancelled'||to_char(x_cancel_qty_allowed) );
1697 WSH_DEBUG_SV.pop(l_module_name);
1698 END IF;
1699 FND_MESSAGE.SET_NAME('WSH', 'WSH_LINE_CANCEL_NOT_ALLOWED');
1700 FND_MSG_PUB.ADD;
1701 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1702 , p_data => x_msg_data );
1703 x_return_status := fnd_api.g_ret_sts_error;
1704 return;
1705 END;
1706 --
1707 x_cancel_qty_allowed := l_cancel_qty_allowed;
1708 --
1709 IF l_debug_on THEN
1710 WSH_DEBUG_SV.log(l_module_name, 'l_cancel_qty_allowed: '||to_char(l_cancel_qty_allowed) );
1711 end if;
1712 --
1713 -- Need to do UOM conversion of the Quantity if SRc and REq. differ.
1714 -- and if there are any Cancellable Qtys.
1715 --
1716 IF ( (nvl(l_cancel_qty_allowed,0) > 0) and (l_src_requested_quantity_uom <> l_requested_quantity_uom) ) then
1717 --{
1718 l_src_cancel_qty_allowed := WSH_WV_UTILS.convert_uom(from_uom => l_requested_quantity_uom,
1719 to_uom => l_src_requested_quantity_uom,
1720 quantity => l_cancel_qty_allowed ,
1721 item_id => l_inventory_item_id );
1722 --
1723 x_cancel_qty_allowed := l_src_cancel_qty_allowed;
1724 --
1725 IF l_debug_on THEN
1726 WSH_DEBUG_SV.log(l_module_name, 'SRC Req. Qty. UOM: '||l_src_requested_quantity_uom );
1727 WSH_DEBUG_SV.log(l_module_name, 'Req. Qty. UOM: '||l_requested_quantity_uom );
1728 WSH_DEBUG_SV.log(l_module_name, 'SRC Req. Qty. allowed to be cancelled: '||l_src_cancel_qty_allowed);
1729 end if;
1730 END IF; --}
1731 --
1732
1733 IF l_debug_on THEN
1734 WSH_DEBUG_SV.log(l_module_name, 'Req. Qty. allowed to be cancelled'||to_char(x_cancel_qty_allowed) );
1735 WSH_DEBUG_SV.log(l_module_name, 'x_return_status '||x_return_status);
1736 WSH_DEBUG_SV.pop(l_module_name);
1737 END IF;
1738
1739 EXCEPTION
1740
1741 WHEN OTHERS THEN
1742 FND_MESSAGE.Set_Name('WSH','WSH_UNEXPECTED_ERROR');
1743 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1744
1745 FND_MSG_PUB.Count_And_Get
1746 ( p_count => x_msg_count
1747 , p_data => x_msg_data
1748 ,p_encoded => FND_API.G_FALSE
1749 );
1750
1751 IF l_debug_on THEN
1752 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
1753 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1754 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1755 END IF;
1756
1757 END Get_Cancel_Qty_Allowed;
1758 --
1759
1760 END WSH_INTEGRATION;
[Home] [Help]