1 package body WMS_RE_Custom_PUB as
2 /* $Header: WMSPPPUB.pls 120.5.12020000.2 2013/01/31 22:02:27 sahmahes ship $ */
3
4 -- File : WMSPPPUB.pls
5 -- Content : WMS_RE_Custom_PUB package body
6 -- Description : Customizable stub procedures and functions called during rules
7 -- engine run.
8 -- Notes :
9 -- Modified : 02/08/99 mzeckzer created
10
11 g_pkg_name CONSTANT VARCHAR2(30) := 'WMS_RE_Custom_PUB';
12
13 -- Start of comments
14 -- API name : GetTotalLocationCapacity
15 -- Type : Public
16 -- Function : Calculates and returns the total capacity of a location
17 -- ( sub or sub/locator ) in a customer-specific manner
18 -- This function is made available to be used within put away
19 -- rule setup.
20 -- Pre-reqs : none
21 -- Parameters :
22 -- p_organization_id in number required default = fnd_api.g_miss_num
23 -- p_subinventory_code in varchar2 required default = fnd_api.g_miss_char
24 -- p_locator_id in number optional default = null
25 -- p_inventory_item_id in number required default = fnd_api.g_miss_num
26 -- p_transaction_uom in varchar2 required default = fnd_api.g_miss_char
27 -- return value out number
28 -- Version : not tracked
29 -- Notes : capacity should be returned as measured in txn UOM
30 -- End of comments
31
32 function GetTotalLocationCapacity (
33 p_organization_id number := g_miss_num
34 ,p_subinventory_code varchar2 := g_miss_char
35 ,p_locator_id number := null
36 ,p_inventory_item_id number := g_miss_num
37 ,p_transaction_uom varchar2 := g_miss_char
38 ) return number is
39 l_total_capacity number := 0;
40
41 begin
42 -- validate input parameters
43 if p_organization_id is null
44 or p_organization_id = g_miss_num
45 or p_subinventory_code is null
46 or p_subinventory_code = g_miss_char
47 or p_inventory_item_id is null
48 or p_inventory_item_id = g_miss_num
49 or p_transaction_uom is null
50 or p_transaction_uom = g_miss_char
51 then
52 return(null);
53 end if;
54
55 -- customer-specific logic
56
57 -- return calculated total capacity
58 return(l_total_capacity);
59
60 end GetTotalLocationCapacity;
61
62 -- Start of comments
63 -- API name : GetOccupiedLocationCapacity
64 -- Type : Public
65 -- Function : Calculates and returns the occupied capacity of a location
66 -- ( sub or sub/locator ) in a customer-specific manner
67 -- This function is made available to be used within put away
68 -- rule setup.
69 -- Pre-reqs : none
70 -- Parameters :
71 -- p_organization_id in number required default = fnd_api.g_miss_num
72 -- p_subinventory_code in varchar2 required default = fnd_api.g_miss_char
73 -- p_locator_id in number optional default = null
74 -- p_inventory_item_id in number required default = fnd_api.g_miss_num
75 -- p_transaction_uom in varchar2 required default = fnd_api.g_miss_char
76 -- return value out number
77 -- Version : not tracked
78 -- Notes : capacity should be returned as measured in txn UOM
79 -- End of comments
80
81 function GetOccupiedLocationCapacity (
82 p_organization_id number := g_miss_num
83 ,p_subinventory_code varchar2 := g_miss_char
84 ,p_locator_id number := null
85 ,p_inventory_item_id number := g_miss_num
86 ,p_transaction_uom varchar2 := g_miss_char
87 ) return number is
88 l_occupied_capacity number := 0;
89
90 begin
91 -- validate input parameters
92 if p_organization_id is null
93 or p_organization_id = g_miss_num
94 or p_subinventory_code is null
95 or p_subinventory_code = g_miss_char
96 or p_inventory_item_id is null
97 or p_inventory_item_id = g_miss_num
98 or p_transaction_uom is null
99 or p_transaction_uom = g_miss_char
100 then
101 return(null);
102 end if;
103
104 -- customer-specific logic
105
106 -- return calculated occupied capacity
107 return(l_occupied_capacity);
108
109 end GetOccupiedLocationCapacity;
110
111 -- Start of comments
112 -- API name : GetAvailableLocationCapacity
113 -- Type : Public
114 -- Function : Calculates and returns the available capacity of a location
115 -- ( sub or sub/locator ) in a customer-specific manner
116 -- This function is made available to be used within put away
117 -- rule setup.
118 -- Pre-reqs : none
119 -- Parameters :
120 -- p_organization_id in number required default = fnd_api.g_miss_num
121 -- p_subinventory_code in varchar2 required default = fnd_api.g_miss_char
122 -- p_locator_id in number optional default = null
123 -- p_inventory_item_id in number required default = fnd_api.g_miss_num
124 -- p_transaction_quantity in number required default = fnd_api.g_miss_num
125 -- p_transaction_uom in varchar2 required default = fnd_api.g_miss_char
126 -- return value out number
127 -- Version : not tracked
128 -- Notes : capacity must be returned as measured in txn UOM
129 -- End of comments
130
131 function GetAvailableLocationCapacity (
132 p_organization_id number := g_miss_num
133 ,p_subinventory_code varchar2 := g_miss_char
134 ,p_locator_id number := null
135 ,p_inventory_item_id number := g_miss_num
136 ,p_transaction_quantity number := g_miss_num
137 ,p_transaction_uom varchar2 := g_miss_char
138 ) return number is
139 l_available_capacity number := 0;
140
141 begin
142 -- validate input parameters
143 if p_organization_id is null
144 or p_organization_id = g_miss_num
145 or p_subinventory_code is null
146 or p_subinventory_code = g_miss_char
147 or p_inventory_item_id is null
148 or p_inventory_item_id = g_miss_num
149 or p_transaction_quantity is null
150 or p_transaction_quantity = g_miss_num
151 or p_transaction_uom is null
152 or p_transaction_uom = g_miss_char
153 then
154 return(null);
155 end if;
156
157 -- customer-specific logic
158
159 -- return calculated available capacity
160 return(l_available_capacity);
161
162 end GetAvailableLocationCapacity;
163
164 -- Start of comments
165 -- API name : GetRemainingLocationCapacity
166 -- Type : Public
167 -- Function : Calculates and returns the occupied capacity of a location
168 -- ( sub or sub/locator ) in a customer-specific manner
169 -- This function is made available to be used within put away
170 -- rule setup.
171 -- Pre-reqs : none
172 -- Parameters :
173 -- p_organization_id in number required default = fnd_api.g_miss_num
174 -- p_subinventory_code in varchar2 required default = fnd_api.g_miss_char
175 -- p_locator_id in number optional default = null
176 -- p_inventory_item_id in number required default = fnd_api.g_miss_num
177 -- p_transaction_quantity in number required default = fnd_api.g_miss_num
178 -- p_transaction_uom in varchar2 required default = fnd_api.g_miss_char
179 -- return value out number
180 -- Version : not tracked
181 -- Notes : capacity should be returned as measured in txn UOM
182 -- End of comments
183
184 function GetRemainingLocationCapacity (
185 p_organization_id number := g_miss_num
186 ,p_subinventory_code varchar2 := g_miss_char
187 ,p_locator_id number := null
188 ,p_inventory_item_id number := g_miss_num
189 ,p_transaction_quantity number := g_miss_num
190 ,p_transaction_uom varchar2 := g_miss_char
191 ) return number is
192 l_remaining_capacity number := 0;
193
194 begin
195 -- validate input parameters
196 if p_organization_id is null
197 or p_organization_id = g_miss_num
198 or p_subinventory_code is null
199 or p_subinventory_code = g_miss_char
200 or p_inventory_item_id is null
201 or p_inventory_item_id = g_miss_num
202 or p_transaction_quantity is null
203 or p_transaction_quantity = g_miss_num
204 or p_transaction_uom is null
205 or p_transaction_uom = g_miss_char
206 then
207 return(null);
208 end if;
209
210 -- customer-specific logic
211
212 -- return calculated remaining capacity
213 return(l_remaining_capacity);
214
215 end GetRemainingLocationCapacity;
216
217 -- Start of comments
218 -- API name : SearchForStrategy
219 -- Type : Public
220 -- Function : Searches for a wms strategy assignment to a
221 -- customer-defined business object in a customer-specific
222 -- manner.
223 -- This procedure gets called just before the standard algorithm
224 -- which searches for strategy assignments to system-defined
225 -- business objects.
226 -- Pre-reqs : transaction record in WMS_STRATEGY_MAT_TXN_TMP_V uniquely
227 -- identified by parameters p_transaction_temp_id and
228 -- p_type_code ( already validated by calling procedure )
229 -- set up strategy assignment in WMS_STRATEGY_ASSIGNMENTS
230 -- Parameters :
231 -- p_init_msg_list in varchar2 optional default = fnd_api.g_false
232 -- x_return_status out varchar2(1)
233 -- x_msg_count out number
234 -- x_msg_data out varchar2(2000)
235 -- p_transaction_temp_id in number required default = fnd_api.g_miss_num
236 -- p_type_code in number required default = fnd_api.g_miss_num
237 -- x_strategy_id out number
238 -- Version : not tracked
239 -- Notes : type code of returned strategy has to match type code
240 -- parameter
241 -- End of comments
242
243 procedure SearchForStrategy (
244 p_init_msg_list in varchar2 := fnd_api.g_false
245 ,x_return_status out NOCOPY varchar2
246 ,x_msg_count out NOCOPY number
247 ,x_msg_data out NOCOPY varchar2
248 ,p_transaction_temp_id in number := fnd_api.g_miss_num
249 ,p_type_code in number := fnd_api.g_miss_num
250 ,x_strategy_id out NOCOPY number
251 ) is
252
253 -- API standard variables
254 l_api_name constant varchar2(30) := 'SearchForStrategy';
255
256 begin
257
258 -- Initialize message list if p_init_msg_list is set to TRUE
259 if fnd_api.to_boolean( p_init_msg_list ) then
260 fnd_msg_pub.initialize;
261 end if;
262
263 -- Initialize API return status to success
264 x_return_status := fnd_api.g_ret_sts_success;
265
266 -- Search for Strategy in a custom-specific manner, using
267 -- View WMS_STRATEGY_MAT_TXN_TMP_V ( Actual transaction values )
268 -- Table WMS_STRATEGY_ASSIGNMENTS ( Setup data )
269 -- ...
270 -- ...
271 -- ...
272 -- By default, no Strategy is found using custom-specific procedure
273 x_strategy_id := null;
274
275 if x_strategy_id is null then
276 -- Message: No strategy found using custom-specific stub procedure
277 raise fnd_api.g_exc_error;
278 end if;
279
280 -- Standard call to get message count and if count is 1, get message info
281 fnd_msg_pub.count_and_get( p_count => x_msg_count
282 ,p_data => x_msg_data );
283
284 exception
285 when fnd_api.g_exc_error then
286 x_return_status := fnd_api.g_ret_sts_error;
287 fnd_msg_pub.count_and_get( p_count => x_msg_count
288 ,p_data => x_msg_data );
289
290 when fnd_api.g_exc_unexpected_error then
291 x_return_status := fnd_api.g_ret_sts_unexp_error;
292 fnd_msg_pub.count_and_get( p_count => x_msg_count
293 ,p_data => x_msg_data );
294
295 when others then
296 x_return_status := fnd_api.g_ret_sts_unexp_error;
297 if fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) then
298 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
299 end if;
300 fnd_msg_pub.count_and_get( p_count => x_msg_count
301 ,p_data => x_msg_data );
302
303 end SearchForStrategy;
304
305
306
307 --*************************************************************************
308 /**
309
310 API name : SearchForStrategy
311 Type : Public
312 Function : Searches for a wms strategy/rule/value assignment to a
313 customer-defined business object in a customer-specific
314 manner.
315 Pre-reqs : transaction record in WMS_STRATEGY_MAT_TXN_TMP_V uniquely
316 identified by parameters p_transaction_temp_id and
317 p_type_code ( already validated by calling procedure )
318 set up strategy assignment in WMS_STRATEGY_ASSIGNMENTS
319 Parameters :
320 p_init_msg_list in varchar2 optional default = fnd_api.g_false
321 x_return_status out varchar2(1)
322 x_msg_count out number
323 x_msg_data out varchar2(2000)
324 p_transaction_temp_id in number required default = fnd_api.g_miss_num
325 p_type_code in number required default = fnd_api.g_miss_num
326 x_return_type out varchar2 'V' for Value , 'R' for Rule , 'S' for strategy
327 ,x_return_type_id out number
328 Notes : type code of returned strategy has to match type code
329 parameter
330 */
331 procedure SearchForStrategy (
332 p_init_msg_list in varchar2 := fnd_api.g_false
333 ,x_return_status out NOCOPY varchar2
334 ,x_msg_count out NOCOPY number
335 ,x_msg_data out NOCOPY varchar2
336 ,p_transaction_temp_id in number := fnd_api.g_miss_num
337 ,p_type_code in number := fnd_api.g_miss_num
338 ,x_return_type out NOCOPY varchar2 -- 'V' for Value , 'R' for Rule , 'S' for strategy
339 ,x_return_type_id out NOCOPY number
340 ) is
341
342 -- API standard variables
343 l_api_name constant varchar2(30) := 'SearchForStrategy';
344
345 begin
346
347 -- Initialize message list if p_init_msg_list is set to TRUE
348 if fnd_api.to_boolean( p_init_msg_list ) then
349 fnd_msg_pub.initialize;
350 end if;
351
352 -- Initialize API return status to success
353 x_return_status := fnd_api.g_ret_sts_success;
354
355 -- Search for Strategy in a custom-specific manner, using
356 -- View WMS_STRATEGY_MAT_TXN_TMP_V ( Actual transaction values )
357 -- Table WMS_STRATEGY_ASSIGNMENTS ( Setup data )
358 -- ...
359 -- ...
360 -- ...
361 -- By default, no Strategy/Rule/Value is found using custom-specific procedure
362 x_return_type :=NULL;
363 x_return_type_id := null;
364
365 if x_return_type_id is null then
366 -- Message: No strategy/Rule/Value found using custom-specific stub procedure
367 raise fnd_api.g_exc_error;
368 end if;
369
370 -- Standard call to get message count and if count is 1, get message info
371 fnd_msg_pub.count_and_get( p_count => x_msg_count
372 ,p_data => x_msg_data );
373
374 exception
375 when fnd_api.g_exc_error then
376 x_return_status := fnd_api.g_ret_sts_error;
377 fnd_msg_pub.count_and_get( p_count => x_msg_count
378 ,p_data => x_msg_data );
379
380 when fnd_api.g_exc_unexpected_error then
381 x_return_status := fnd_api.g_ret_sts_unexp_error;
382 fnd_msg_pub.count_and_get( p_count => x_msg_count
383 ,p_data => x_msg_data );
384
385 when others then
386 x_return_status := fnd_api.g_ret_sts_unexp_error;
387 if fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) then
388 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
389 end if;
390 fnd_msg_pub.count_and_get( p_count => x_msg_count
391 ,p_data => x_msg_data );
392
393 end SearchForStrategy;
394
395 --BUG12681832 Begin
396 --Refer spec for implementation details of the function
397 FUNCTION PutawayPartialDetailedLPN(
398 p_lpn_id in number
399 ) RETURN BOOLEAN IS
400
401 BEGIN
402 RETURN FALSE;
403
404 EXCEPTION WHEN OTHERS THEN
405 RETURN FALSE;
406
407 END PutawayPartialDetailedLPN;
408 --BUG12681832 End
409
410
411 -- Start of comments
412 -- API name : set_dualuom_alloc_prefs
413 -- Type : Public
414 -- Function : Allows customers to set preferences for material allocation
415 -- of dual UOM items. This API is called once for each
416 -- move order line being allocated.
417 -- Pre-reqs : none
418 --
419 -- Parameters :
420 -- p_organization_id - Organization ID
421 -- p_inventory_item_id - Item ID
422 -- p_move_order_type - Numeric code indicating move order type
423 -- (see lookup type MOVE_ORDER_TYPE)
424 -- p_transaction_type_id - Transaction type ID
425 -- (see table MTL_TRANSACTION_TYPES)
426 -- p_move_order_line_id - Move Order Line ID
427 --
428 -- Return parameters:
429 -- x_sec_qty_round_mode - Ensure that integer secondary quantities are
430 -- allocated. (Can be used only if OM indivisible
431 -- flag on the item is unchecked - 'N')
432 --
433 -- Indicate preference for how rounding should
434 -- occur, using one of the following:
435 --
436 -- 'F': Allocate the largest integer secondary quantity possible without exceeding
437 -- the primary requested quantity (uses the FLOOR() function)
438 --
439 -- 'C': Allocate the smallest integer secondary quantity that meets or
440 -- exceeds the primary requested quantity (uses the CEIL() function)
441 --
442 -- 'R': Round secondary quantity to nearest integer and minimize deviation
443 -- from the primary requested quantity (uses the ROUND() function)
444 --
445 -- Note: for allocation based on secondary, only 'F' (floor) is supported
446 --
447 --
448 -- x_target_uom - Whether to allocate based on primary requested quantity (P)
449 -- (the default), or secondary requested quantity (S)
450 --
451 -- Allocation based on secondary is typically used when
452 -- the demand document was created in secondary UOM
453 -- and deviation is allowed between primary and secondary
454 -- quantities.
455 --
456 -- In such cases the primary quantity specified on the
457 -- move order line would have been derived based on the
458 -- standard UOM conversion from the secondary requested
459 -- quantity, and if used as the basis for allocation,
460 -- would result in either over-allocation or
461 -- under-allocation of the secondary requested quantity
462 -- (as a result of quantity deviation).
463 --
464 -- By setting the fulfillment basis (x_target_uom) parameter
465 -- one can ensure that the requested secondary quantity is
466 -- correctly allocated.
467 --
468 --
469 -- Version : not tracked
470 -- End of comments
471
472 PROCEDURE set_dualuom_alloc_prefs(
473 p_organization_id IN NUMBER
474 , p_inventory_item_id IN NUMBER
475 , p_move_order_type IN NUMBER
476 , p_transaction_type_id IN NUMBER
477 , p_move_order_line_id IN NUMBER
478 , x_return_status OUT NOCOPY VARCHAR2
479 , x_msg_count OUT NOCOPY NUMBER
480 , x_msg_data OUT NOCOPY VARCHAR
481 , x_sec_qty_round_mode OUT NOCOPY VARCHAR2
482 , x_target_uom OUT NOCOPY VARCHAR2
483 ) IS
484 BEGIN
485 x_return_status := fnd_api.g_ret_sts_success;
486
487 x_sec_qty_round_mode := NULL;
488 x_target_uom := 'P';
489
490 EXCEPTION
491 WHEN OTHERS THEN
492 x_return_status := fnd_api.g_ret_sts_unexp_error;
493 x_msg_data := SQLERRM;
494 END set_dualuom_alloc_prefs;
495
496
497 /*
498 Custom Hook API to validate the SKU being picked for the picking task. Bug#13361151
499 */
500 -- Start of comments
501 -- API name : custom_validate_picking_sku
502 -- Type : Public
503 -- Function : Allows customers do further validation (beyind picking rule) on the LPN being allocated
504 -- with the visibility of each partial allocation , if any .
505 -- Pre-reqs : none
506 --
507 -- Parameters :
508 -- p_organization_id - Organization ID
509 -- p_inventory_item_id - Item ID
510 -- p_revision - Item revision
511 -- p_subinventory_code - Subinventory name where the allocation is being done.
512 -- p_locator_id - Locator ID from where the allocation is being done.
513 -- p_lot_number - Lot number which is being allocated.
514 -- p_cost_group_id - Cost Group ID of onhand that is being allocated.
515 -- p_lpn_id - LPN ID from where the allocation is being done.
516 -- p_rule_id - WMS Picking Rule ID used for allocation.
517 -- p_move_order_line_id - Move Order Line ID
518 -- p_needed_primary_quantity - Quantity left to be allocated at the moment.
519 -- ( MO quantity - sum(quantity already allocated) ) .
520 -- p_needed_secondary_quantity - Secondary Quantity left to be allocated at the moment.
521 -- p_picking_primary_quantity - Quantity thats is being allocated from this LPN/SKU
522 -- p_picking_secondary_quantity - Secondary Quantity thats is being allocated from this LPN/SKU
523 -- p_lpn_available_primary_qty - Total Quantity availble in this LPN thats is being allocated.
524 -- p_lpn_available_secondary_qty - Total Secondary Quantity availble in this LPN thats is being allocated.
525 -- p_already_allocated_pri_qty - Total primary quantity that is already allocated for this move order line in this current execution
526 -- p_already_allocated_sec_qty - Total secondary quantity that is already allocated for this move order line in this current execution
527 --
528 -- Return parameters:
529 -- The function returns boolean TRUE or FALSE
530 --
531 -- 'TRUE' : Allocation Engine will proceed and create a picking suggestion
532 -- 'FALSE': Allocation Engine will not pick this SKU . It will skip this LPN and proceed to find the next one
533 --
534 -- Version : not tracked
535 -- End of comments
536
537
538 FUNCTION custom_validate_picking_sku (
539 p_organization_id number,
540 p_inventory_item_id number,
541 p_revision varchar2,
542 p_subinventory_code varchar2,
543 p_locator_id number,
544 p_lot_number varchar2,
545 p_cost_group_id number,
546 p_lpn_id number,
547 p_rule_id number,
548 p_move_order_line_id number,
549 p_needed_primary_quantity number,
550 p_needed_secondary_quantity number,
551 p_picking_primary_quantity number,
552 p_picking_secondary_quantity number,
553 p_lpn_available_primary_qty number,
554 p_lpn_available_secondary_qty number,
555 p_already_allocated_pri_qty number,
556 p_already_allocated_sec_qty number )
557 return boolean IS
558 BEGIN
559 return true;
560 EXCEPTION
561 WHEN OTHERS THEN
562 RETURN TRUE;
563 END custom_validate_picking_sku;
564
565
566
567 end WMS_RE_Custom_PUB;
[Home] [Help]