1 PACKAGE BODY wms_parameter_pvt AS
2 /* $Header: WMSVPPPB.pls 120.7 2008/01/15 08:47:55 kkesavar ship $ */
3 -- File : WMSVPPPS.pls
4 -- Content : WMS_Parameter_PVT package specification
5 -- Description : WMS parameters private APIs
6 -- This package contains two types of functions:
7 -- 1. functions related to flexfield
8 -- 2. functions related to various quantity functions
9 --
10 -- Notes :
11 -- Modified : 02/08/99 mzeckzer created
12 -- 05/01/99 bitang changed the code to call AOL APIs for
13 -- flexfield functions
14 -- 11/08/99 bitang moved to wms and added comment
15 --
16 -- Package global variable to store the package name
17 g_pkg_name CONSTANT VARCHAR2(30) := 'WMS_Parameter_PVT';
18
19 --
20 TYPE t_num_empty_loc_rec IS RECORD(
21 subinventory_code VARCHAR2(10)
22 , num_empty_locators NUMBER);
23
24 TYPE t_num_empty_loc_table IS TABLE OF t_num_empty_loc_rec
25 INDEX BY BINARY_INTEGER;
26
27 --used in GetNumEmptyLocators
28 g_num_empty_locators t_num_empty_loc_table;
29 --used in GetPOHeaderLineID
30 g_po_header_id NUMBER;
31 g_po_line_id NUMBER;
32 g_po_reference_id NUMBER; -- Bug #4554344
33
34 -- To improve performance in the functions within this package the last set of
35 -- in parameters and the return value is cached. In most cases these functions
36 -- are called in a Where clause and many times the same parameters are passed
37 -- repeatidly. This caching will avoid redoing the work.
38 -- Function getsoheader_line
39 g_gsohl_header_id NUMBER;
40 g_gsohl_line_id NUMBER;
41 g_gsohl_mo_line_id NUMBER;
42 g_gsohl_reference_id NUMBER;
43
44
45 g_project_name VARCHAR2(30); -- Cached project name
46 g_project_number VARCHAR2(30); -- Cached project number
47 g_planning_group VARCHAR2(30); -- Cached project group
48 g_inventory_organization_id number ;
49 g_project_id number ;
50
51 PROCEDURE log_mesg(
52 p_api_name VARCHAR2,
53 p_label VARCHAR2,
54 p_message VARCHAR2) IS
55
56 l_module VARCHAR2(255);
57
58 BEGIN
59
60 l_module := 'wms.plsql.' || g_pkg_name || '.' || p_api_name || '.' || p_label
61 ;
62 inv_log_util.trace(p_message, l_module, 9);
63 END log_mesg;
64
65
66 -- API name : ClearCache
67 -- Type : Private
68 -- Function : Clears the global cache used in the parameters file.
69 -- This will be called from the WMS_RULE_PVT package.
70 PROCEDURE clearcache IS
71 BEGIN
72 g_num_empty_locators.DELETE;
73 g_po_header_id := NULL;
74 g_po_line_id := NULL;
75
76 END clearcache;
77
78 /*LPN Status Project*/
79 FUNCTION GET_MATERIAL_STATUS(
80 p_status_id IN NUMBER DEFAULT NULL)
81 RETURN VARCHAR2 IS
82
83 l_material_status VARCHAR2(30) := NULL;
84
85 BEGIN
86
87 SELECT status_code into l_material_status
88 FROM mtl_material_statuses_vl
89 WHERE status_id= p_status_id ;
90
91 RETURN l_material_status;
92
93 EXCEPTION
94
95 WHEN OTHERS THEN
96 RETURN NULL;
97
98 END GET_MATERIAL_STATUS;
99 /*LPN Status Project*/
100
101
102 -- Functions Related To Flexfield
103 --
104 -- Description
105 -- Procedure that returns information about key flexfield
106 PROCEDURE get_key_flex_info(
107 p_application_short_name IN VARCHAR2
108 , p_flexfield_name IN VARCHAR2
109 , p_column_name IN VARCHAR2
110 , x_if_flex_then_available OUT NOCOPY VARCHAR2
111 , x_flex_data_type_code OUT NOCOPY NUMBER
112 , x_flex_name OUT NOCOPY VARCHAR2
113 , x_flex_description OUT NOCOPY VARCHAR2
114 ) IS
115 l_flexfield fnd_flex_key_api.flexfield_type;
116 l_segment fnd_flex_key_api.segment_type;
117 l_nstructures NUMBER;
118 l_structure fnd_flex_key_api.structure_type;
119 l_structure_list fnd_flex_key_api.structure_list;
120 l_valueset_dr fnd_vset.valueset_dr;
121 l_valueset_r fnd_vset.valueset_r;
122 l_segment_found BOOLEAN := FALSE;
123 l_nsegment NUMBER;
124 l_segment_list fnd_flex_key_api.segment_list;
125 BEGIN
126 -- initialize
127 x_if_flex_then_available := 'Y';
128 x_flex_data_type_code := NULL;
129 x_flex_name := NULL;
130 x_flex_description := NULL;
131 --
132 fnd_flex_key_api.set_session_mode('seed_data');
133 --
134 l_flexfield := fnd_flex_key_api.find_flexfield(p_application_short_name, p_flexfield_name);
135
136 --
137 IF l_flexfield.instantiated = 'N' THEN
138 RETURN;
139 END IF;
140
141 -- since we have no info about the structure we would look at
142 -- all structures
143 fnd_flex_key_api.get_structures(
144 flexfield => l_flexfield
145 , enabled_only => TRUE
146 , nstructures => l_nstructures
147 , structures => l_structure_list
148 );
149
150 FOR l_index IN 1 .. l_nstructures LOOP
151 -- find the structure
152 l_structure := fnd_flex_key_api.find_structure(flexfield => l_flexfield, structure_number => l_structure_list(l_index));
153
154 -- find the segment. only consider the ones freezed
155 IF l_structure.instantiated = 'Y'
156 AND l_structure.freeze_flag = 'Y' THEN
157 fnd_flex_key_api.get_segments(l_flexfield, l_structure, TRUE, l_nsegment, l_segment_list);
158
159 FOR l_seg_index IN 1 .. l_nsegment LOOP
160 l_segment := fnd_flex_key_api.find_segment(l_flexfield, l_structure, l_segment_list(l_seg_index));
161
162 IF l_segment.column_name = p_column_name THEN
163 l_segment_found := TRUE;
164 EXIT;
165 END IF;
166 END LOOP;
167
168 IF l_segment_found
169 AND l_segment.instantiated = 'Y' THEN
170 EXIT;
171 END IF;
172 END IF;
173 END LOOP;
174
175 --
176 -- segment not found
177 IF l_segment_found = FALSE
178 OR l_segment.instantiated = 'N' THEN
179 x_if_flex_then_available := 'N';
180 RETURN;
181 END IF;
182
183 -- segment found
184 -- if the segment does not use a value set, no type info
185 IF l_segment.value_set_id IS NULL THEN
186 x_flex_data_type_code := NULL;
187 ELSE
188 fnd_vset.get_valueset(valueset_id => l_segment.value_set_id, valueset => l_valueset_r, format => l_valueset_dr);
189
190 --
191 IF l_valueset_dr.format_type = 'N' THEN -- number type
192 x_flex_data_type_code := 1;
193 ELSIF l_valueset_dr.format_type = 'C' THEN -- character type
194 x_flex_data_type_code := 2;
195 ELSIF l_valueset_dr.format_type = 'D' THEN -- date type
196 x_flex_data_type_code := 3;
197 ELSE
198 x_flex_data_type_code := NULL; /* should consider more type later */
199 END IF;
200 END IF;
201
202 --
203 x_flex_name := l_segment.window_prompt;
204 x_flex_description := l_segment.description;
205 --
206 RETURN;
207 --
208 EXCEPTION
209 WHEN NO_DATA_FOUND THEN
210 x_if_flex_then_available := 'Y';
211 x_flex_data_type_code := NULL;
212 x_flex_name := NULL;
213 x_flex_description := NULL;
214 RETURN;
215 END get_key_flex_info;
216
217 --
218 -- Description
219 -- Procedure that returns information about descriptive flexfield
220 PROCEDURE get_desc_flex_info(
221 p_application_short_name IN VARCHAR2
222 , p_flexfield_name IN VARCHAR2
223 , p_column_name IN VARCHAR2
224 , x_if_flex_then_available OUT NOCOPY VARCHAR2
225 , x_flex_data_type_code OUT NOCOPY NUMBER
226 , x_flex_name OUT NOCOPY VARCHAR2
227 , x_flex_description OUT NOCOPY VARCHAR2
228 ) IS
229 l_flexfield fnd_dflex.dflex_r;
230 l_flexinfo fnd_dflex.dflex_dr;
231 l_contexts fnd_dflex.contexts_dr;
232 l_context fnd_dflex.context_r;
233 l_segments fnd_dflex.segments_dr;
234 l_segment_index BINARY_INTEGER;
235 l_context_index BINARY_INTEGER;
236 l_global_context_idx BINARY_INTEGER;
237 l_valueset_dr fnd_vset.valueset_dr;
238 l_valueset_r fnd_vset.valueset_r;
239 BEGIN
240 -- initialize
241 x_flex_data_type_code := NULL;
242 x_flex_name := NULL;
243 x_flex_description := NULL;
244
245 IF inv_pp_debug.is_debug_mode THEN
246 inv_pp_debug.send_message_to_pipe('Get_desc_flex_info(): ');
247 inv_pp_debug.send_message_to_pipe('p_flexfield_name: '|| p_flexfield_name);
248 inv_pp_debug.send_message_to_pipe('p_column_name: '|| p_column_name);
249 inv_pp_debug.send_message_to_pipe('x_if_flex_then_available: '|| x_if_flex_then_available);
250 END IF;
251
252 --
253 fnd_dflex.get_flexfield(
254 appl_short_name => p_application_short_name
255 , flexfield_name => p_flexfield_name
256 , flexfield => l_flexfield
257 , flexinfo => l_flexinfo
258 );
259 --
260 fnd_dflex.get_contexts(flexfield => l_flexfield, contexts => l_contexts);
261 --
262 -- we only want the global and enabled context
263 l_global_context_idx := l_contexts.global_context;
264
265 -- global context not found
266 IF l_global_context_idx = 0
267 OR l_global_context_idx IS NULL THEN
268 RETURN;
269 END IF;
270
271 --
272 x_if_flex_then_available := 'Y';
273 --
274 -- assemble the context_r record
275 l_context.context_code := l_contexts.context_code(l_global_context_idx);
276 l_context.flexfield := l_flexfield;
277 --
278 -- get segments
279 fnd_dflex.get_segments(l_context, l_segments, TRUE);
280 --
281 l_segment_index := 0;
282
283 FOR l_index IN 1 .. l_segments.nsegments LOOP
284 IF l_segments.application_column_name(l_index) = p_column_name THEN
285 l_segment_index := l_index;
286 EXIT;
287 END IF;
288 END LOOP;
289
290 --
291 IF l_segment_index = 0 THEN
292 IF inv_pp_debug.is_debug_mode THEN
293 inv_pp_debug.send_message_to_pipe('No segment found in Global context. ncontexts enabled : '|| l_contexts.ncontexts);
294 END IF;
295
296 --segment not found
297 /*Start of New proposed fix*/
298 FOR l_seg IN 1 .. l_contexts.ncontexts LOOP
299 inv_pp_debug.send_message_to_pipe('l_seg : '|| l_seg);
300
301 IF l_contexts.is_enabled(l_seg) = TRUE
302 AND l_seg <> l_contexts.global_context THEN
303 inv_pp_debug.send_message_to_pipe('context ENABLED and not GLOBAL');
304 l_context.context_code := l_contexts.context_code(l_seg);
305 l_context.flexfield := l_flexfield;
306 fnd_dflex.get_segments(l_context, l_segments, TRUE);
307
308 FOR l_index IN 1 .. l_segments.nsegments LOOP
309 IF l_segments.application_column_name(l_index) = p_column_name THEN
310 l_segment_index := l_index;
311
312 IF inv_pp_debug.is_debug_mode THEN
313 inv_pp_debug.send_message_to_pipe('Found context code:'|| l_context.context_code);
314 END IF;
315
316 RETURN;
317 END IF;
318 END LOOP;
319 END IF;
320
321 inv_pp_debug.send_message_to_pipe('AFTER LOOP: l_segment_index :'|| l_segment_index);
322 END LOOP;
323 END IF;
324
325 IF l_segment_index = 0 THEN
326 x_if_flex_then_available := 'N';
327 RETURN;
328 END IF;
329
330 /*End of Proposed fix*/
331 inv_pp_debug.send_message_to_pipe('segment found .');
332
333 --
334 -- segment found
335 -- if the segment does not use a value set, no type info
336 IF l_segments.value_set(l_segment_index) IS NULL THEN
337 x_flex_data_type_code := NULL;
338 ELSE
339 fnd_vset.get_valueset(valueset_id => l_segments.value_set(l_segment_index), valueset => l_valueset_r, format => l_valueset_dr);
340
341 IF l_valueset_dr.format_type = 'N' THEN -- number type
342 x_flex_data_type_code := 1;
343 ELSIF l_valueset_dr.format_type = 'C' THEN -- character type
344 x_flex_data_type_code := 2;
345 ELSIF l_valueset_dr.format_type = 'D' THEN -- date type
346 x_flex_data_type_code := 3;
347 ELSE
348 x_flex_data_type_code := NULL; /* should consider more type later */
349 END IF;
350 END IF;
351
352 --
353 x_flex_name := l_segments.row_prompt(l_segment_index);
354 x_flex_description := l_segments.description(l_segment_index);
355 --
356 RETURN;
357 EXCEPTION
358 WHEN NO_DATA_FOUND THEN
359 x_if_flex_then_available := 'Y';
360 x_flex_data_type_code := NULL;
361 x_flex_name := NULL;
362 x_flex_description := NULL;
363
364 IF inv_pp_debug.is_debug_mode THEN
365 inv_pp_debug.send_message_to_pipe('EXCEPTION: x_if_flex_then_available: '|| x_if_flex_then_available);
366 END IF;
367
368 RETURN;
369 END get_desc_flex_info;
370
371 --
372 -- Description
373 -- Returns the application short name for the given application id
374 FUNCTION get_application_short_name(p_application_id IN NUMBER)
375 RETURN VARCHAR2 IS
376 l_appl_short_name VARCHAR2(50);
377
378 --
379 CURSOR l_cursor IS
380 SELECT application_short_name
381 FROM fnd_application
382 WHERE application_id = p_application_id;
383 BEGIN
384 OPEN l_cursor;
385 FETCH l_cursor INTO l_appl_short_name;
386
387 IF l_cursor%NOTFOUND THEN
388 l_appl_short_name := NULL;
389 END IF;
390
391 RETURN l_appl_short_name;
392 END get_application_short_name;
393
394 --
395 -- Description
396 -- Returns flexfield information
397 -- This procedure calls either get_key_flex_info or get_desc_flex_info
398 -- based on p_flexfield_usage_code.
399 PROCEDURE get_flex_info(
400 p_db_object_ref_type_code IN NUMBER
401 , p_parameter_type_code IN NUMBER
402 , p_flexfield_usage_code IN VARCHAR2
403 , p_flexfield_application_id IN NUMBER
404 , p_flexfield_name IN VARCHAR2
405 , p_column_name IN VARCHAR2
406 , x_if_flex_then_available OUT NOCOPY VARCHAR2
407 , x_flex_data_type_code OUT NOCOPY NUMBER
408 , x_flex_name OUT NOCOPY VARCHAR2
409 , x_flex_description OUT NOCOPY VARCHAR2
410 ) IS
411 l_appl_short_name VARCHAR2(3);
412 l_msg_data VARCHAR2(256);
413 l_msg_count NUMBER;
414 BEGIN
415 -- initialize
416 x_if_flex_then_available := NULL;
417 x_flex_data_type_code := NULL;
418 x_flex_name := NULL;
419 x_flex_description := NULL;
420
421 --
422 -- validate input
423
424 IF inv_pp_debug.is_debug_mode THEN
425 inv_pp_debug.send_message_to_pipe('Get_flex_info(): ');
426 inv_pp_debug.send_message_to_pipe('p_db_object_ref_type_code: '|| p_db_object_ref_type_code);
427 inv_pp_debug.send_message_to_pipe('p_parameter_type_code: '|| p_parameter_type_code);
428 inv_pp_debug.send_message_to_pipe('p_flexfield_usage_code: '|| p_flexfield_usage_code);
429 inv_pp_debug.send_message_to_pipe('p_flexfield_name: '|| p_flexfield_name);
430 inv_pp_debug.send_message_to_pipe('p_column_name: '|| p_column_name);
431 inv_pp_debug.send_message_to_pipe('x_if_flex_then_available: '|| x_if_flex_then_available);
432 END IF;
433
434 IF p_db_object_ref_type_code = g_miss_num
435 OR p_db_object_ref_type_code IS NULL
436 OR p_parameter_type_code = g_miss_num
437 OR p_parameter_type_code IS NULL
438 OR p_flexfield_usage_code = g_miss_char
439 OR p_flexfield_application_id = g_miss_num
440 OR p_flexfield_name = g_miss_char
441 OR p_column_name = g_miss_char THEN
442 inv_pp_debug.send_message_to_pipe('Failed input validation .RETURN');
443 RETURN;
444 END IF;
445
446 --
447 x_if_flex_then_available := 'Y';
448
449 IF p_db_object_ref_type_code <> 1 THEN /* not a single referenced object */
450 RETURN;
451 END IF;
452
453 --
454 IF p_parameter_type_code <> 1 THEN /* self-defined sql expression */
455 RETURN;
456 END IF;
457
458 --
459 IF p_flexfield_application_id IS NULL THEN
460 inv_pp_debug.send_message_to_pipe('p_flexfield_application_id IS NULL.RETURN');
461
462 IF (p_flexfield_usage_code IS NOT NULL) THEN
463 x_if_flex_then_available := 'N';
464 END IF;
465
466 RETURN;
467 END IF;
468
469 --
470 -- everything else should be a column of a table or view, so the
471 -- usage code decides whether or not the column could be setup
472 -- as flexfield segment.
473 --
474 l_appl_short_name := get_application_short_name(p_flexfield_application_id);
475 --
476 inv_pp_debug.send_message_to_pipe('l_appl_short_name : '|| l_appl_short_name);
477
478 IF l_appl_short_name IS NULL THEN
479 fnd_message.set_name('INV', 'INV_INVALID_APPLICATION_ID');
480 fnd_message.set_token('APPLICATION_ID', p_flexfield_application_id);
481 RAISE fnd_api.g_exc_unexpected_error;
482 END IF;
483
484 --
485 IF p_flexfield_usage_code = 'K' THEN
486 get_key_flex_info(
487 p_application_short_name => l_appl_short_name
488 , p_flexfield_name => p_flexfield_name
489 , p_column_name => p_column_name
490 , x_if_flex_then_available => x_if_flex_then_available
491 , x_flex_data_type_code => x_flex_data_type_code
492 , x_flex_name => x_flex_name
493 , x_flex_description => x_flex_description
494 );
495 ELSIF p_flexfield_usage_code = 'D' THEN
496 get_desc_flex_info(
497 p_application_short_name => l_appl_short_name
498 , p_flexfield_name => p_flexfield_name
499 , p_column_name => p_column_name
500 , x_if_flex_then_available => x_if_flex_then_available
501 , x_flex_data_type_code => x_flex_data_type_code
502 , x_flex_name => x_flex_name
503 , x_flex_description => x_flex_description
504 );
505 ELSE
506 -- column can serve as neither key or descriptive flex field
507 -- segment so we should just return the following values
508 x_if_flex_then_available := 'Y';
509 x_flex_data_type_code := NULL;
510 x_flex_name := NULL;
511 x_flex_description := NULL;
512 inv_pp_debug.send_message_to_pipe('flexfield usage code NULL: '|| ' x_if_flex_then_available: ' || x_if_flex_then_available);
513 END IF;
514 EXCEPTION
515 WHEN fnd_api.g_exc_unexpected_error THEN
516 inv_pp_debug.send_message_to_pipe('EXCEPTION ');
517 fnd_msg_pub.count_and_get(p_count => l_msg_count, p_data => l_msg_data);
518 END get_flex_info;
519
520 -- API name : IfFlexThenAvailable
521 -- Type : Private
522 -- Function : Returns 'N' if actual parameter is a key or descriptive
523 -- flexfield segment and not configured yet, returns 'Y' in
524 -- any other case.
525 -- ( Needed for all forms base views and LOV's regarding
526 -- parameters like rules, restrictions and sort criteria )
527 --
528 -- Input Parameters :
529 -- See the definition of corresponding column in WMS_PARAMETERS for
530 -- update-to-date information about the following input parameters.
531 --
532 -- p_db_object_ref_type_code:
533 -- 1 - single referenced ; 2 - multiple referenced
534 -- p_parameter_type_code:
535 -- 1 - column ; 2 - expression
536 -- p_flexfield_usage_code:
537 -- 'K' - key flexfield; 'D' - descriptive flexfield ;
538 -- null - not used in flexfield
539 -- p_flexfield_application_id:
540 -- id of the application in which the flexfield is defined
541 -- p_flexfield_name:
542 -- code of the key flexfield or name of the descriptive flexfield
543 -- p_column_name:
544 -- column name if the parameter is based on a table/view column
545 --
546 -- Notes : works for global segments only, not for context segments
547 --
548 FUNCTION ifflexthenavailable(
549 p_db_object_ref_type_code IN NUMBER DEFAULT g_miss_num
550 , p_parameter_type_code IN NUMBER DEFAULT g_miss_num
551 , p_flexfield_usage_code IN VARCHAR2 DEFAULT g_miss_char
552 , p_flexfield_application_id IN NUMBER DEFAULT g_miss_num
553 , p_flexfield_name IN VARCHAR2 DEFAULT g_miss_char
554 , p_column_name IN VARCHAR2 DEFAULT g_miss_char
555 )
556 RETURN VARCHAR2 IS
557 l_if_flex_then_available VARCHAR2(1);
558 l_flex_data_type_code wms_parameters_b.data_type_code%TYPE;
559 l_flex_name wms_parameters_vl.NAME%TYPE;
560 l_flex_description wms_parameters_vl.description%TYPE;
561 BEGIN
562 get_flex_info(
563 p_db_object_ref_type_code => p_db_object_ref_type_code
564 , p_parameter_type_code => p_parameter_type_code
565 , p_flexfield_usage_code => p_flexfield_usage_code
566 , p_flexfield_application_id => p_flexfield_application_id
567 , p_flexfield_name => p_flexfield_name
568 , p_column_name => p_column_name
569 , x_if_flex_then_available => l_if_flex_then_available
570 , x_flex_data_type_code => l_flex_data_type_code
571 , x_flex_name => l_flex_name
572 , x_flex_description => l_flex_description
573 );
574 --
575 RETURN l_if_flex_then_available;
576 END ifflexthenavailable;
577
578 --
579 --
580 -- API name : GetFlexDataTypeCode
581 -- Type : Private
582 -- Function : Returns user-defined segment data type if actual parameter is
583 -- a key or descriptive flexfield segment and configured,
584 -- returns original data type in any other case.
585 -- ( Needed for all forms base views and LOV's regarding
586 -- parameters like rules, restrictions and sort criteria )
587 -- Input Parameters :
588 -- p_data_type_code:
589 -- data type of the flexfield segment
590 -- 1 - number ; 2 - character; 3 - date ; null - not given
591 --
592 -- See the comment in function IfFlexThenAvailable for the
593 -- meaning of the following input parameters.
594 --
595 -- p_db_object_ref_type_code
596 -- p_parameter_type_code
597 -- p_flexfield_usage_code
598 -- p_flexfield_application_id
599 -- p_flexfield_name
600 -- p_column_name
601 --
602 -- Notes : works for global segments only, not for context segments
603 FUNCTION getflexdatatypecode(
604 p_data_type_code IN NUMBER DEFAULT g_miss_num
605 , p_db_object_ref_type_code IN NUMBER DEFAULT g_miss_num
606 , p_parameter_type_code IN NUMBER DEFAULT g_miss_num
607 , p_flexfield_usage_code IN VARCHAR2 DEFAULT g_miss_char
608 , p_flexfield_application_id IN NUMBER DEFAULT g_miss_num
609 , p_flexfield_name IN VARCHAR2 DEFAULT g_miss_char
610 , p_column_name IN VARCHAR2 DEFAULT g_miss_char
611 )
612 RETURN NUMBER IS
613 l_if_flex_then_available VARCHAR2(1);
614 l_flex_data_type_code wms_parameters_b.data_type_code%TYPE;
615 l_flex_name wms_parameters_vl.NAME%TYPE;
616 l_flex_description wms_parameters_vl.description%TYPE;
617 l_appl_short_name VARCHAR2(3);
618 BEGIN
619 get_flex_info(
620 p_db_object_ref_type_code => p_db_object_ref_type_code
621 , p_parameter_type_code => p_parameter_type_code
622 , p_flexfield_usage_code => p_flexfield_usage_code
623 , p_flexfield_application_id => p_flexfield_application_id
624 , p_flexfield_name => p_flexfield_name
625 , p_column_name => p_column_name
626 , x_if_flex_then_available => l_if_flex_then_available
627 , x_flex_data_type_code => l_flex_data_type_code
628 , x_flex_name => l_flex_name
629 , x_flex_description => l_flex_description
630 );
631
632 --
633 IF l_flex_data_type_code IS NOT NULL THEN
634 RETURN l_flex_data_type_code;
635 ELSE
636 RETURN p_data_type_code;
637 END IF;
638 END getflexdatatypecode;
639
640 --
641 -- API name : GetFlexName
642 -- Type : Private
643 -- Function : Returns user-defined segment name if actual parameter is
644 -- a key or descriptive flexfield segment and configured,
645 -- returns original name in any other case.
646 -- ( Needed for all forms base views and LOV's regarding
647 -- parameters like rules, restrictions and sort criteria )
648 --
649 -- Input Parameters :
650 -- p_name:
651 -- name of the flexfield segment
652 --
653 -- See the comment in function IfFlexThenAvailable for the
654 -- meaning of the following input parameters.
655 --
656 -- p_db_object_ref_type_code
657 -- p_parameter_type_code
658 -- p_flexfield_usage_code
659 -- p_flexfield_application_id
660 -- p_flexfield_name
661 -- p_column_name
662 --
663 -- Notes : works for global segments only, not for context segments
664 --
665 FUNCTION getflexname(
666 p_name IN VARCHAR2 DEFAULT g_miss_char
667 , p_db_object_ref_type_code IN NUMBER DEFAULT g_miss_num
668 , p_parameter_type_code IN NUMBER DEFAULT g_miss_num
669 , p_flexfield_usage_code IN VARCHAR2 DEFAULT g_miss_char
670 , p_flexfield_application_id IN NUMBER DEFAULT g_miss_num
671 , p_flexfield_name IN VARCHAR2 DEFAULT g_miss_char
672 , p_column_name IN VARCHAR2 DEFAULT g_miss_char
673 )
674 RETURN VARCHAR2 IS
675 l_if_flex_then_available VARCHAR2(1);
676 l_flex_data_type_code wms_parameters_b.data_type_code%TYPE;
677 l_flex_name wms_parameters_vl.NAME%TYPE;
678 l_flex_description wms_parameters_vl.description%TYPE;
679 l_appl_short_name VARCHAR2(3);
680 BEGIN
681 get_flex_info(
682 p_db_object_ref_type_code => p_db_object_ref_type_code
683 , p_parameter_type_code => p_parameter_type_code
684 , p_flexfield_usage_code => p_flexfield_usage_code
685 , p_flexfield_application_id => p_flexfield_application_id
686 , p_flexfield_name => p_flexfield_name
687 , p_column_name => p_column_name
688 , x_if_flex_then_available => l_if_flex_then_available
689 , x_flex_data_type_code => l_flex_data_type_code
690 , x_flex_name => l_flex_name
691 , x_flex_description => l_flex_description
692 );
693
694 --
695 IF l_flex_name IS NOT NULL THEN
696 RETURN l_flex_name;
697 ELSE
698 RETURN p_name;
699 END IF;
700 END getflexname;
701
702 --
703 -- API name : GetFlexDescription
704 -- Type : Private
705 -- Function : Returns user-defined segment description if actual parameter
706 -- is a key or descriptive flexfield segment and configured,
707 -- returns original description in any other case.
708 -- ( Needed for all forms base views and LOV's regarding
709 -- parameters like rules, restrictions and sort criteria )
710 --
711 -- Input Parameters:
712 -- p_description:
713 -- description of the flexfield segment
714 -- p_db_object_ref_type_code
715 -- p_parameter_type_code
716 -- p_flexfield_usage_code
717 -- p_flexfield_application_id
718 -- p_flexfield_name
719 -- p_column_name
720 --
721 -- Notes : works for global segments only, not for context segments
722 FUNCTION getflexdescription(
723 p_description IN VARCHAR2 DEFAULT g_miss_char
724 , p_db_object_ref_type_code IN NUMBER DEFAULT g_miss_num
725 , p_parameter_type_code IN NUMBER DEFAULT g_miss_num
726 , p_flexfield_usage_code IN VARCHAR2 DEFAULT g_miss_char
727 , p_flexfield_application_id IN NUMBER DEFAULT g_miss_num
728 , p_flexfield_name IN VARCHAR2 DEFAULT g_miss_char
729 , p_column_name IN VARCHAR2 DEFAULT g_miss_char
730 )
731 RETURN VARCHAR2 IS
732 l_if_flex_then_available VARCHAR2(1);
733 l_flex_data_type_code wms_parameters_b.data_type_code%TYPE;
734 l_flex_name wms_parameters_vl.NAME%TYPE;
735 l_flex_description wms_parameters_vl.description%TYPE;
736 l_appl_short_name VARCHAR2(3);
737 BEGIN
738 get_flex_info(
739 p_db_object_ref_type_code => p_db_object_ref_type_code
740 , p_parameter_type_code => p_parameter_type_code
741 , p_flexfield_usage_code => p_flexfield_usage_code
742 , p_flexfield_application_id => p_flexfield_application_id
743 , p_flexfield_name => p_flexfield_name
744 , p_column_name => p_column_name
745 , x_if_flex_then_available => l_if_flex_then_available
746 , x_flex_data_type_code => l_flex_data_type_code
747 , x_flex_name => l_flex_name
748 , x_flex_description => l_flex_description
749 );
750
751 --
752 IF l_flex_description IS NOT NULL THEN
753 RETURN l_flex_description;
754 ELSE
755 RETURN p_description;
756 END IF;
757 END getflexdescription;
758
759 --
760 -- Description
761 -- testing procedure
762 PROCEDURE test IS
763 l_flex_data_type_code wms_parameters_b.data_type_code%TYPE;
764 l_if_flex_then_available VARCHAR2(1);
765 l_flex_name wms_parameters_tl.NAME%TYPE;
766 l_flex_description wms_parameters_tl.description%TYPE;
767 BEGIN
768 get_flex_info(
769 p_db_object_ref_type_code => 2
770 , p_parameter_type_code => 1
771 , p_flexfield_usage_code => 'D'
772 , p_flexfield_application_id => 401
773 , p_flexfield_name => 'MTL_GENERIC_DISPOSITIONS'
774 , p_column_name => 'ATTRIBUTE1'
775 , x_if_flex_then_available => l_if_flex_then_available
776 , x_flex_data_type_code => l_flex_data_type_code
777 , x_flex_name => l_flex_name
778 , x_flex_description => l_flex_description
779 );
780 get_flex_info(
781 p_db_object_ref_type_code => TO_NUMBER(1)
782 , p_flexfield_usage_code => 'K'
783 , p_flexfield_application_id => 401
784 , p_parameter_type_code => 1
785 , p_flexfield_name => 'MSTK'
786 , p_column_name => 'Item Number Value'
787 , x_if_flex_then_available => l_if_flex_then_available
788 , x_flex_data_type_code => l_flex_data_type_code
789 , x_flex_name => l_flex_name
790 , x_flex_description => l_flex_description
791 );
792 END test;
793
794 --
795 -- API name : RoundUp
796 -- Type : Private
797 -- Function : Returns quantity, rounded up according actual and base units
798 -- of measure and the conversion defined between them.
799 -- ( Used for capacity and on-hand calculation parameters )
800 FUNCTION roundup(
801 p_quantity IN NUMBER DEFAULT g_miss_num
802 , p_transaction_uom IN VARCHAR2 DEFAULT g_miss_char
803 , p_inventory_item_id IN NUMBER DEFAULT g_miss_num
804 , p_base_uom IN VARCHAR2 DEFAULT g_miss_char
805 )
806 RETURN NUMBER IS
807 l_quantity NUMBER;
808 BEGIN
809 -- validate input parameters
810 IF p_quantity = g_miss_num
811 OR p_quantity IS NULL
812 OR p_transaction_uom = g_miss_char
813 OR p_transaction_uom IS NULL
814 OR p_inventory_item_id = g_miss_num
815 OR p_inventory_item_id IS NULL
816 OR p_base_uom = g_miss_char
817 OR p_base_uom IS NULL THEN
818 RETURN NULL;
819 END IF;
820
821 --
822 -- standard way of rounding capacity:
823 -- convert capacity to base uom
824 -- round up
825 -- ( convert back to transaction uom )
826 IF p_transaction_uom <> p_base_uom THEN
827 l_quantity := inv_convert.inv_um_convert(
828 p_inventory_item_id
829 , NULL
830 , CEIL(inv_convert.inv_um_convert(p_inventory_item_id, NULL, p_quantity, p_transaction_uom, p_base_uom, NULL, NULL))
831 , p_base_uom
832 , p_transaction_uom
833 , NULL
834 , NULL
835 );
836 ELSE
837 l_quantity := CEIL(p_quantity);
838 END IF;
839
840 --
841 IF l_quantity <= 0 THEN -- can happen, if conversion isn't defined
842 RETURN 0;
843 END IF;
844
845 --
846 RETURN l_quantity;
847 --
848 END roundup;
849
850 --
851 -- API name : RoundDown
852 -- Type : Private
853 -- Function : Returns quantity, rounded down according actual and base
854 -- units of measure and the conversion defined between them.
855 -- ( Used for capacity and on-hand calculation parameters )
856 FUNCTION rounddown(
857 p_quantity IN NUMBER DEFAULT g_miss_num
858 , p_transaction_uom IN VARCHAR2 DEFAULT g_miss_char
859 , p_inventory_item_id IN NUMBER DEFAULT g_miss_num
860 , p_base_uom IN VARCHAR2 DEFAULT g_miss_char
861 )
862 RETURN NUMBER IS
863 l_quantity NUMBER;
864 BEGIN
865 -- validate input parameters
866 IF p_quantity = g_miss_num
867 OR p_quantity IS NULL
868 OR p_transaction_uom = g_miss_char
869 OR p_transaction_uom IS NULL
870 OR p_inventory_item_id = g_miss_num
871 OR p_inventory_item_id IS NULL
872 OR p_base_uom = g_miss_char
873 OR p_base_uom IS NULL THEN
874 RETURN NULL;
875 END IF;
876
877 --
878 -- standard way of rounding capacity:
879 -- convert capacity to base uom
880 -- cut decimals
881 -- ( convert back to transaction uom )
882 IF p_transaction_uom <> p_base_uom THEN
883 l_quantity := inv_convert.inv_um_convert(
884 p_inventory_item_id
885 , NULL
886 , FLOOR(inv_convert.inv_um_convert(p_inventory_item_id, NULL, p_quantity, p_transaction_uom, p_base_uom, NULL, NULL))
887 , p_base_uom
888 , p_transaction_uom
889 , NULL
890 , NULL
891 );
892 ELSE
893 l_quantity := FLOOR(p_quantity);
894 END IF;
895
896 --
897 IF l_quantity <= 0 THEN -- can happen, if conversion isn't defined
898 RETURN 0;
899 END IF;
900
901 --
902 RETURN l_quantity;
903 END rounddown;
904
905 --
906 -- API name : GetTotalUnitCapacity
907 -- Type : Private
908 -- Function : Returns total unit capacity of a location regardless any unit
909 -- of measure.
910 -- ( Used for capacity calculation parameters )
911 -- Notes : Since there are several situations, where unit capacity can
912 -- not be calculated, the following definitions are made:
913 -- - in case of subinventories w/o locators, capacity is
914 -- infinite
915 -- - in case of missing setup data at the locator, capacity is
916 -- infinite
917 FUNCTION gettotalunitcapacity(
918 p_organization_id IN NUMBER DEFAULT g_miss_num
919 , p_subinventory_code IN VARCHAR2 DEFAULT g_miss_char
920 , p_locator_id IN NUMBER DEFAULT g_miss_num
921 )
922 RETURN NUMBER IS
923 l_capacity NUMBER;
924 l_return_status VARCHAR2(1);
925 l_msg_count NUMBER;
926 l_msg_data VARCHAR2(240);
927 l_loc_max_units NUMBER;
928 l_loc_cur_units NUMBER;
929 l_loc_sug_units NUMBER;
930 l_loc_avail_units NUMBER;
931 BEGIN
932 -- missing input parameters, something is wrong -> null capacity
933 IF p_organization_id = g_miss_num
934 OR p_subinventory_code = g_miss_char
935 OR p_locator_id = g_miss_num THEN
936 l_capacity := NULL;
937 -- if no sub specified, something is wrong -> null capacity
938 ELSIF p_organization_id IS NULL
939 OR p_subinventory_code IS NULL THEN
940 l_capacity := NULL;
941 -- if no locator specified -> sub w/o loc -> infinite capacity
942 ELSIF p_locator_id IS NULL THEN
943 l_capacity := 1e125;
944 -- otherwise return maximum units ( no uom conversions possible )
945 ELSE
946 inv_loc_wms_utils.get_locator_unit_capacity(
947 x_return_status => l_return_status
948 , x_msg_count => l_msg_count
949 , x_msg_data => l_msg_data
950 , x_location_maximum_units => l_loc_max_units
951 , x_location_current_units => l_loc_cur_units
952 , x_location_suggested_units => l_loc_sug_units
953 , x_location_available_units => l_loc_avail_units
954 , p_organization_id => p_organization_id
955 , p_inventory_location_id => p_locator_id
956 );
957 l_capacity := l_loc_max_units;
958
959 IF l_capacity IS NULL THEN
960 l_capacity := 1e125;
961 END IF;
962 END IF;
963
964 RETURN l_capacity;
965 END gettotalunitcapacity;
966
967 --
968 -- API name : GetOccupiedUnitCapacity
969 -- Type : Private
970 -- Function : Returns occupied unit capacity of a location regardless any
971 -- unit of measure.
972 -- ( Used for capacity calculation parameters )
973 FUNCTION getoccupiedunitcapacity(
974 p_organization_id IN NUMBER DEFAULT g_miss_num
975 , p_subinventory_code IN VARCHAR2 DEFAULT g_miss_char
976 , p_locator_id IN NUMBER DEFAULT g_miss_num
977 )
978 RETURN NUMBER IS
979 l_capacity NUMBER;
980 l_return_status VARCHAR2(1);
981 l_msg_count NUMBER;
982 l_msg_data VARCHAR2(240);
983 l_loc_max_units NUMBER;
984 l_loc_cur_units NUMBER;
985 l_loc_sug_units NUMBER;
986 l_loc_avail_units NUMBER;
987 BEGIN
988 -- missing input parameters, something is wrong -> null capacity
989 IF p_organization_id = g_miss_num
990 OR p_subinventory_code = g_miss_char
991 OR p_locator_id = g_miss_num THEN
992 l_capacity := NULL;
993 -- if no sub specified, something is wrong -> null capacity
994 ELSIF p_organization_id IS NULL
995 OR p_subinventory_code IS NULL THEN
996 l_capacity := NULL;
997 -- get on-hand regardless UOM
998 ELSIF p_locator_id IS NULL THEN
999 l_capacity := 0;
1000 ELSE
1001 inv_loc_wms_utils.get_locator_unit_capacity(
1002 x_return_status => l_return_status
1003 , x_msg_count => l_msg_count
1004 , x_msg_data => l_msg_data
1005 , x_location_maximum_units => l_loc_max_units
1006 , x_location_current_units => l_loc_cur_units
1007 , x_location_suggested_units => l_loc_sug_units
1008 , x_location_available_units => l_loc_avail_units
1009 , p_organization_id => p_organization_id
1010 , p_inventory_location_id => p_locator_id
1011 );
1012
1013
1014 IF l_loc_cur_units IS NULL THEN
1015 l_loc_cur_units := 0;
1016 END IF;
1017 IF l_loc_sug_units IS NULL THEN
1018 l_loc_sug_units := 0;
1019 END IF;
1020 l_capacity := l_loc_cur_units + l_loc_sug_units;
1021 END IF;
1022
1023 --
1024 RETURN l_capacity;
1025 --
1026 END getoccupiedunitcapacity;
1027
1028 --
1029 -- API name : GetAvailableUnitCapacity
1030 -- Type : Private
1031 -- Function : Returns available unit capacity of a location considering
1032 -- on-hand stock regardless any unit of measure.
1033 -- ( Used for capacity calculation parameters )
1034 -- Notes : Since there are several situations, where unit capacity can
1035 -- not be calculated, the following definitions are made:
1036 -- - in case of subinventories w/o locators, capacity is
1037 -- infinite
1038 -- - in case of missing setup data at the locator, capacity is
1039 -- infinite
1040 FUNCTION getavailableunitcapacity(
1041 p_organization_id IN NUMBER DEFAULT g_miss_num
1042 , p_subinventory_code IN VARCHAR2 DEFAULT g_miss_char
1043 , p_locator_id IN NUMBER DEFAULT g_miss_num
1044 )
1045 RETURN NUMBER IS
1046 l_capacity NUMBER;
1047 l_return_status VARCHAR2(1);
1048 l_msg_count NUMBER;
1049 l_msg_data VARCHAR2(240);
1050 l_loc_max_units NUMBER;
1051 l_loc_cur_units NUMBER;
1052 l_loc_sug_units NUMBER;
1053 l_loc_avail_units NUMBER;
1054 BEGIN
1055 -- missing input parameters, something is wrong -> null capacity
1056 IF p_organization_id = g_miss_num
1057 OR p_subinventory_code = g_miss_char
1058 OR p_locator_id = g_miss_num THEN
1059 l_capacity := NULL;
1060 -- if no sub specified, something is wrong -> null capacity
1061 ELSIF p_organization_id IS NULL
1062 OR p_subinventory_code IS NULL THEN
1063 l_capacity := NULL;
1064 -- get on-hand regardless UOM
1065 ELSIF p_locator_id IS NULL THEN
1066 l_capacity := 1e125;
1067 ELSE
1068 inv_loc_wms_utils.get_locator_unit_capacity(
1069 x_return_status => l_return_status
1070 , x_msg_count => l_msg_count
1071 , x_msg_data => l_msg_data
1072 , x_location_maximum_units => l_loc_max_units
1073 , x_location_current_units => l_loc_cur_units
1074 , x_location_suggested_units => l_loc_sug_units
1075 , x_location_available_units => l_loc_avail_units
1076 , p_organization_id => p_organization_id
1077 , p_inventory_location_id => p_locator_id
1078 );
1079 l_capacity := l_loc_avail_units;
1080
1081 IF l_capacity IS NULL THEN
1082 l_capacity := 1e125;
1083 END IF;
1084 END IF;
1085
1086 RETURN l_capacity;
1087 END getavailableunitcapacity;
1088
1089 --
1090 -- API name : GetRemainingUnitCapacity
1091 -- Type : Private
1092 -- Function : Returns remaining unit capacity of a location, assuming the
1093 -- actual receipt would have been performed already, regardless
1094 -- any unit of measure.
1095 -- ( Used for capacity calculation parameters )
1096 -- Notes : Since there are several situations, where unit capacity can
1097 -- not be calculated, the following definitions are made:
1098 -- - in case of subinventories w/o locators, capacity is
1099 -- infinite
1100 -- - in case of missing setup data at the locator, capacity is
1101 -- infinite
1102 FUNCTION getremainingunitcapacity(
1103 p_organization_id IN NUMBER DEFAULT g_miss_num
1104 , p_subinventory_code IN VARCHAR2 DEFAULT g_miss_char
1105 , p_locator_id IN NUMBER DEFAULT g_miss_num
1106 , p_transaction_quantity IN NUMBER DEFAULT g_miss_num
1107 )
1108 RETURN NUMBER IS
1109 l_available_capacity NUMBER;
1110 l_capacity NUMBER;
1111 BEGIN
1112 -- if transaction quantity is null or missing -> something is wrong -> abort
1113 IF p_transaction_quantity = g_miss_num
1114 OR p_transaction_quantity IS NULL THEN
1115 RETURN NULL;
1116 END IF;
1117
1118 -- get available capacity
1119 l_available_capacity := getavailableunitcapacity(p_organization_id, p_subinventory_code, p_locator_id);
1120
1121 --
1122 -- if available capacity is null -> something is wrong -> abort
1123 IF l_available_capacity IS NULL THEN
1124 RETURN l_available_capacity;
1125 -- if available capacity is infinite -> no need to subtract anything
1126 ELSIF l_available_capacity = 1e125 THEN
1127 RETURN l_available_capacity;
1128 -- if total capacity is zero or less -> no need to subtract anything
1129 ELSIF l_available_capacity <= 0 THEN
1130 RETURN 0;
1131 ELSE
1132 -- otherwise remaining = available - txn quantity
1133 l_capacity := l_available_capacity - p_transaction_quantity;
1134 END IF;
1135
1136 --
1137 -- we don't return any negative capacity
1138 IF l_capacity <= 0 THEN
1139 RETURN 0;
1140 END IF;
1141
1142 --
1143 RETURN l_capacity;
1144 --
1145 END getremainingunitcapacity;
1146
1147 --
1148 -- API name : GetTotalVolumeCapacity
1149 -- Type : Private
1150 -- Function : Returns total volume or weight capacity of a location
1151 -- measured in transaction UOM of the actual item.
1152 -- Meaning: The function determines, how many items, measured in
1153 -- transaction UOM, will fit into the location
1154 -- according to their volume or weight.
1155 -- ( Used for capacity calculation parameters )
1156 -- Notes : Since there are several situations, where volume or weight
1157 -- capacity can not be calculated, the following definitions are
1158 -- made:
1159 -- - in case of subinventories w/o locators, capacity is
1160 -- infinite
1161 -- - in case of missing setup data at the locator, capacity is
1162 -- infinite
1163 -- - in case of missing setup data at the item, capacity is
1164 -- infinite ( meaning: item then has no volume or weight )
1165 FUNCTION gettotalvolumecapacity(
1166 p_organization_id IN NUMBER DEFAULT g_miss_num
1167 , p_subinventory_code IN VARCHAR2 DEFAULT g_miss_char
1168 , p_locator_id IN NUMBER DEFAULT g_miss_num
1169 , p_inventory_item_id IN NUMBER DEFAULT g_miss_num
1170 , p_unit_volume IN NUMBER DEFAULT g_miss_num
1171 , p_unit_volume_uom_code IN VARCHAR2 DEFAULT g_miss_char
1172 , p_primary_uom IN VARCHAR2 DEFAULT g_miss_char
1173 , p_transaction_uom IN VARCHAR2 DEFAULT g_miss_char
1174 , p_base_uom IN VARCHAR2 DEFAULT g_miss_char
1175 )
1176 RETURN NUMBER IS
1177 l_capacity NUMBER;
1178 l_return_status VARCHAR2(1);
1179 l_msg_count NUMBER;
1180 l_msg_data VARCHAR2(1);
1181 l_volume_uom_code VARCHAR2(3);
1182 l_max_cubic_area NUMBER;
1183 l_cur_cubic_area NUMBER;
1184 l_sug_cubic_area NUMBER;
1185 l_avail_cubic_area NUMBER;
1186 BEGIN
1187 -- missing input parameters, something is wrong -> null capacity
1188 IF p_organization_id = g_miss_num
1189 OR p_subinventory_code = g_miss_char
1190 OR p_locator_id = g_miss_num
1191 OR p_inventory_item_id = g_miss_num
1192 OR p_unit_volume = g_miss_num
1193 OR p_unit_volume_uom_code = g_miss_char
1194 OR p_primary_uom = g_miss_char
1195 OR p_transaction_uom = g_miss_char
1196 OR p_base_uom = g_miss_char THEN
1197 RETURN NULL;
1198 -- if no sub specified, something is wrong -> null capacity
1199 ELSIF p_organization_id IS NULL
1200 OR p_subinventory_code IS NULL
1201 OR p_inventory_item_id IS NULL THEN
1202 RETURN NULL;
1203 ELSIF p_primary_uom IS NULL
1204 OR p_transaction_uom IS NULL
1205 OR p_base_uom IS NULL THEN
1206 RETURN 1e125;
1207 -- Mising item setup data regarding volume
1208 -- -> assumption: item has no volume -> used up capacity
1209 -- If setup data is missing, return infinite
1210 ELSIF p_unit_volume IS NULL
1211 OR p_unit_volume_uom_code IS NULL THEN
1212 RETURN 1e125;
1213 -- if unit volume is zero or negative -> same as above
1214 -- If setup data is missing or wrong, return infinite
1215 ELSIF p_unit_volume <= 0 THEN
1216 RETURN 1e125;
1217 -- if no locator specified -> sub w/o loc -> infinite capacity
1218 ELSIF p_locator_id IS NULL THEN
1219 RETURN 1e125;
1220 ELSE
1221 inv_loc_wms_utils.get_locator_volume_capacity(
1222 x_return_status => l_return_status
1223 , x_msg_count => l_msg_count
1224 , x_msg_data => l_msg_data
1225 , x_volume_uom_code => l_volume_uom_code
1226 , x_max_cubic_area => l_max_cubic_area
1227 , x_current_cubic_area => l_cur_cubic_area
1228 , x_suggested_cubic_area => l_sug_cubic_area
1229 , x_available_cubic_area => l_avail_cubic_area
1230 , p_organization_id => p_organization_id
1231 , p_inventory_location_id => p_locator_id
1232 );
1233
1234 -- max cubic area is NULL if setup data is missing
1235 -- If setup data is missing or wrong, return infinite
1236 IF l_max_cubic_area IS NULL THEN
1237 RETURN 1e125;
1238 END IF;
1239
1240 --if zero, no need to convert
1241 IF l_max_cubic_area <= 0 THEN
1242 RETURN 0;
1243 END IF;
1244
1245
1246 IF p_unit_volume_uom_code <> l_volume_uom_code THEN
1247 l_capacity := inv_convert.inv_um_convert(
1248 p_inventory_item_id
1249 , NULL
1250 , l_max_cubic_area
1251 , l_volume_uom_code
1252 , p_unit_volume_uom_code
1253 , NULL
1254 , NULL
1255 )
1256 / p_unit_volume;
1257 -- l_capacity is negative if setup data is missing or wrong
1258 -- If setup data is missing or wrong, return infinite
1259 IF l_capacity < 0 THEN
1260 RETURN 1e125;
1261 END IF;
1262 ELSE
1263 l_capacity := l_max_cubic_area / p_unit_volume;
1264 END IF;
1265
1266 IF p_primary_uom <> p_transaction_uom THEN
1267 l_capacity := inv_convert.inv_um_convert(p_inventory_item_id, NULL, l_capacity, p_primary_uom, p_transaction_uom, NULL, NULL);
1268 END IF;
1269
1270 -- l_capacity is negative if setup data is missing or wrong
1271 -- If setup data is missing or wrong, return infinite
1272 IF l_capacity < 0 THEN
1273 RETURN 1e125;
1274 END IF;
1275
1276 l_capacity := rounddown(l_capacity, p_transaction_uom, p_inventory_item_id, p_base_uom);
1277
1278 -- l_capacity is negative if setup data is missing or wrong
1279 -- If setup data is missing or wrong, return infinite
1280 IF l_capacity < 0 THEN
1281 RETURN 1e125;
1282 END IF;
1283 END IF;
1284
1285 RETURN l_capacity;
1286 END gettotalvolumecapacity;
1287
1288 --
1289 -- API name : GetTotalWeightCapacity
1290 -- Type : Private
1291 -- Function : Returns total weight capacity of a location
1292 -- measured in transaction UOM of the actual item.
1293 -- Meaning: The function determines, how many items, measured in
1294 -- transaction UOM, will fit into the location
1295 -- according to their volume or weight.
1296 -- ( Used for capacity calculation parameters )
1297 -- Notes : Since there are several situations, where weight
1298 -- capacity can not be calculated, the following definitions are
1299 -- made:
1300 -- - in case of subinventories w/o locators, capacity is
1301 -- infinite
1302 -- - in case of missing setup data at the locator, capacity is
1303 -- infinite
1304 -- - in case of missing setup data at the item, capacity is
1305 -- infinite ( meaning: item then has no volume or weight )
1306 FUNCTION gettotalweightcapacity(
1307 p_organization_id IN NUMBER DEFAULT g_miss_num
1308 , p_subinventory_code IN VARCHAR2 DEFAULT g_miss_char
1309 , p_locator_id IN NUMBER DEFAULT g_miss_num
1310 , p_inventory_item_id IN NUMBER DEFAULT g_miss_num
1311 , p_unit_weight IN NUMBER DEFAULT g_miss_num
1312 , p_unit_weight_uom_code IN VARCHAR2 DEFAULT g_miss_char
1313 , p_primary_uom IN VARCHAR2 DEFAULT g_miss_char
1314 , p_transaction_uom IN VARCHAR2 DEFAULT g_miss_char
1315 , p_base_uom IN VARCHAR2 DEFAULT g_miss_char
1316 )
1317 RETURN NUMBER IS
1318 l_capacity NUMBER;
1319 l_return_status VARCHAR2(1);
1320 l_msg_count NUMBER;
1321 l_msg_data VARCHAR2(1);
1322 l_weight_uom_code VARCHAR2(3);
1323 l_max_weight NUMBER;
1324 l_cur_weight NUMBER;
1325 l_sug_weight NUMBER;
1326 l_avail_weight NUMBER;
1327 BEGIN
1328 -- missing input parameters, something is wrong -> null capacity
1329 IF p_organization_id = g_miss_num
1330 OR p_subinventory_code = g_miss_char
1331 OR p_locator_id = g_miss_num
1332 OR p_inventory_item_id = g_miss_num
1333 OR p_unit_weight = g_miss_num
1334 OR p_unit_weight_uom_code = g_miss_char
1335 OR p_primary_uom = g_miss_char
1336 OR p_transaction_uom = g_miss_char
1337 OR p_base_uom = g_miss_char THEN
1338 RETURN NULL;
1339 -- if no sub specified, something is wrong -> null capacity
1340 ELSIF p_organization_id IS NULL
1341 OR p_subinventory_code IS NULL
1342 OR p_inventory_item_id IS NULL THEN
1343 RETURN NULL;
1344 ELSIF p_primary_uom IS NULL
1345 OR p_transaction_uom IS NULL
1346 OR p_base_uom IS NULL THEN
1347 RETURN 1e125;
1348 -- Mising item setup data regarding volume
1349 -- -> assumption: item has no volume -> used up capacity
1350 -- If setup data is missing, return infinite
1351 ELSIF p_unit_weight IS NULL
1352 OR p_unit_weight_uom_code IS NULL THEN
1353 RETURN 1e125;
1354 -- if unit volume is zero or negative -> same as above
1355 -- If setup data is missing or wrong, return infinite
1356 ELSIF p_unit_weight <= 0 THEN
1357 RETURN 1e125;
1358 -- if no locator specified -> sub w/o loc -> infinite capacity
1359 ELSIF p_locator_id IS NULL THEN
1360 RETURN 1e125;
1361 ELSE
1362 inv_loc_wms_utils.get_locator_weight_capacity(
1363 x_return_status => l_return_status
1364 , x_msg_count => l_msg_count
1365 , x_msg_data => l_msg_data
1366 , x_location_weight_uom_code => l_weight_uom_code
1367 , x_max_weight => l_max_weight
1368 , x_current_weight => l_cur_weight
1369 , x_suggested_weight => l_sug_weight
1370 , x_available_weight => l_avail_weight
1371 , p_organization_id => p_organization_id
1372 , p_inventory_location_id => p_locator_id
1373 );
1374
1375 -- max weight is NULL if setup data is missing
1376 -- If setup data is missing or wrong, return infinite
1377 IF l_max_weight IS NULL THEN
1378 RETURN 1e125;
1379 END IF;
1380
1381 IF l_max_weight <=0 THEN
1382 RETURN 0;
1383 END IF;
1384
1385 IF p_unit_weight_uom_code <> l_weight_uom_code THEN
1386 l_capacity := inv_convert.inv_um_convert_new(
1387 p_inventory_item_id
1388 , NULL
1389 , l_max_weight
1390 , l_weight_uom_code
1391 , p_unit_weight_uom_code
1392 , NULL
1393 , NULL, 'W'
1394 )
1395 / p_unit_weight;
1396 -- l_capacity is negative if setup data is missing or wrong
1397 -- If setup data is missing or wrong, return infinite
1398 IF l_capacity < 0 THEN
1399 RETURN 1e125;
1400 END IF;
1401 ELSE
1402 l_capacity := l_max_weight / p_unit_weight;
1403 END IF;
1404
1405 IF p_primary_uom <> p_transaction_uom THEN
1406 l_capacity := inv_convert.inv_um_convert(p_inventory_item_id, NULL, l_capacity, p_primary_uom, p_transaction_uom, NULL, NULL);
1407 END IF;
1408
1409 -- l_capacity is negative if setup data is missing or wrong
1410 -- If setup data is missing or wrong, return infinite
1411 IF l_capacity < 0 THEN
1412 RETURN 1e125;
1413 END IF;
1414
1415 l_capacity := rounddown(l_capacity, p_transaction_uom, p_inventory_item_id, p_base_uom);
1416
1417 -- l_capacity is negative if setup data is missing or wrong
1418 -- If setup data is missing or wrong, return infinite
1419 IF l_capacity < 0 THEN
1420 RETURN 1e125;
1421 END IF;
1422 END IF;
1423
1424 RETURN l_capacity;
1425 END gettotalweightcapacity;
1426
1427 --
1428 -- API name : GetOccupiedVolumeCapacity
1429 -- Type : Private
1430 -- Function : Returns occupied volume capacity of a location measured in
1431 -- transaction UOM of the actual item.
1432 -- Meaning: The function determines, how many items, measured in
1433 -- transaction UOM, virtually occupy the location
1434 -- already according to their volume.
1435 -- ( Used for capacity calculation parameters )
1436 -- Notes : Since there are several situations, where volume capacity can
1437 -- not be calculated, the following definitions are made:
1438 -- - in case of missing setup data at the item, occupied
1439 -- capacity is zero ( meaning: item then has no volume )
1440 FUNCTION getoccupiedvolumecapacity(
1441 p_organization_id IN NUMBER DEFAULT g_miss_num
1442 , p_subinventory_code IN VARCHAR2 DEFAULT g_miss_char
1443 , p_locator_id IN NUMBER DEFAULT g_miss_num
1444 , p_inventory_item_id IN NUMBER DEFAULT g_miss_num
1445 , p_unit_volume IN NUMBER DEFAULT g_miss_num
1446 , p_unit_volume_uom_code IN VARCHAR2 DEFAULT g_miss_char
1447 , p_primary_uom IN VARCHAR2 DEFAULT g_miss_char
1448 , p_transaction_uom IN VARCHAR2 DEFAULT g_miss_char
1449 , p_base_uom IN VARCHAR2 DEFAULT g_miss_char
1450 )
1451 RETURN NUMBER IS
1452 l_capacity NUMBER;
1453 l_volume NUMBER;
1454 l_return_status VARCHAR2(1);
1455 l_msg_count NUMBER;
1456 l_msg_data VARCHAR2(1);
1457 l_volume_uom_code VARCHAR2(3);
1458 l_max_cubic_area NUMBER;
1459 l_cur_cubic_area NUMBER;
1460 l_sug_cubic_area NUMBER;
1461 l_avail_cubic_area NUMBER;
1462 BEGIN
1463 -- missing input parameters, something is wrong -> null capacity
1464 IF p_organization_id = g_miss_num
1465 OR p_subinventory_code = g_miss_char
1466 OR p_locator_id = g_miss_num
1467 OR p_inventory_item_id = g_miss_num
1468 OR p_unit_volume = g_miss_num
1469 OR p_unit_volume_uom_code = g_miss_char
1470 OR p_primary_uom = g_miss_char
1471 OR p_transaction_uom = g_miss_char
1472 OR p_base_uom = g_miss_char THEN
1473 RETURN NULL;
1474 -- if no sub specified, something is wrong -> null capacity
1475 ELSIF p_organization_id IS NULL
1476 OR p_subinventory_code IS NULL
1477 OR p_inventory_item_id IS NULL THEN
1478 RETURN NULL;
1479 ELSIF p_primary_uom IS NULL
1480 OR p_transaction_uom IS NULL
1481 OR p_base_uom IS NULL THEN
1482 RETURN 0;
1483 -- Mising item setup data regarding volume
1484 -- -> assumption: item has no volume -> used up capacity
1485 -- If setup data is missing, return zero
1486 ELSIF p_unit_volume IS NULL
1487 OR p_unit_volume_uom_code IS NULL THEN
1488 RETURN 0;
1489 -- if unit volume is zero or negative -> same as above
1490 -- If setup data is missing or wrong, return zero
1491 ELSIF p_unit_volume <= 0 THEN
1492 RETURN 0;
1493 -- if no locator specified -> sub w/o loc -> infinite capacity
1494 ELSIF p_locator_id IS NULL THEN
1495 RETURN 0;
1496 ELSE
1497 inv_loc_wms_utils.get_locator_volume_capacity(
1498 x_return_status => l_return_status
1499 , x_msg_count => l_msg_count
1500 , x_msg_data => l_msg_data
1501 , x_volume_uom_code => l_volume_uom_code
1502 , x_max_cubic_area => l_max_cubic_area
1503 , x_current_cubic_area => l_cur_cubic_area
1504 , x_suggested_cubic_area => l_sug_cubic_area
1505 , x_available_cubic_area => l_avail_cubic_area
1506 , p_organization_id => p_organization_id
1507 , p_inventory_location_id => p_locator_id
1508 );
1509 -- if cur or sug volume is NULL, set to 0
1510 -- If setup data is missing or wrong, return infinite
1511 l_capacity := NVL(l_cur_cubic_area, 0) + NVL(l_sug_cubic_area, 0);
1512
1513 IF l_capacity <= 0 THEN
1514 RETURN 0;
1515 END IF;
1516
1517 IF p_unit_volume_uom_code <> l_volume_uom_code THEN
1518 l_capacity := inv_convert.inv_um_convert_new(
1519 p_inventory_item_id
1520 , NULL
1521 , l_capacity
1522 , l_volume_uom_code
1523 , p_unit_volume_uom_code
1524 , NULL
1525 , NULL, 'V'
1526 )
1527 / p_unit_volume;
1528 IF l_capacity <= 0 THEN
1529 RETURN 0;
1530 END IF;
1531 ELSE
1532 l_capacity := l_capacity / p_unit_volume;
1533 END IF;
1534
1535 IF p_primary_uom <> p_transaction_uom THEN
1536 l_capacity :=
1537 inv_convert.inv_um_convert(
1538 p_inventory_item_id,
1539 NULL,
1540 l_capacity,
1541 p_primary_uom,
1542 p_transaction_uom,
1543 NULL,
1544 NULL);
1545 IF l_capacity <= 0 THEN
1546 RETURN 0;
1547 END IF;
1548 END IF;
1549
1550 -- l_capacity is negative if setup data is missing or wrong
1551 -- If setup data is missing or wrong, return zero
1552 IF l_capacity <= 0 THEN
1553 RETURN 0;
1554 END IF;
1555
1556 l_capacity := rounddown(l_capacity,
1557 p_transaction_uom,
1558 p_inventory_item_id,
1559 p_base_uom);
1560
1561 -- l_capacity is negative if setup data is missing or wrong
1562 -- If setup data is missing or wrong, return infinite
1563 IF l_capacity < 0 THEN
1564 RETURN 0;
1565 END IF;
1566 END IF;
1567
1568 RETURN l_capacity;
1569 END getoccupiedvolumecapacity;
1570
1571 --
1572 --
1573 -- API name : GetOccupiedWeightCapacity
1574 -- Type : Private
1575 -- Function : Returns occupied weight capacity of a location measured in
1576 -- transaction UOM of the actual item.
1577 -- Meaning: The function determines, how many items, measured in
1578 -- transaction UOM, virtually occupy the location
1579 -- already according to their weight.
1580 -- ( Used for capacity calculation parameters )
1581 -- Notes : Since there are several situations, where weight capacity can
1582 -- not be calculated, the following definitions are made:
1583 -- - in case of missing setup data at the item, occupied
1584 -- capacity is zero ( meaning: item then has no weight )
1585 FUNCTION getoccupiedweightcapacity(
1586 p_organization_id IN NUMBER DEFAULT g_miss_num
1587 , p_subinventory_code IN VARCHAR2 DEFAULT g_miss_char
1588 , p_locator_id IN NUMBER DEFAULT g_miss_num
1589 , p_inventory_item_id IN NUMBER DEFAULT g_miss_num
1590 , p_unit_weight IN NUMBER DEFAULT g_miss_num
1591 , p_unit_weight_uom_code IN VARCHAR2 DEFAULT g_miss_char
1592 , p_primary_uom IN VARCHAR2 DEFAULT g_miss_char
1593 , p_transaction_uom IN VARCHAR2 DEFAULT g_miss_char
1594 , p_base_uom IN VARCHAR2 DEFAULT g_miss_char
1595 )
1596 RETURN NUMBER IS
1597 l_capacity NUMBER;
1598 l_weight NUMBER;
1599 --
1600 l_return_status VARCHAR2(1);
1601 l_msg_count NUMBER;
1602 l_msg_data VARCHAR2(1);
1603 l_weight_uom_code VARCHAR2(3);
1604 l_max_weight NUMBER;
1605 l_cur_weight NUMBER;
1606 l_sug_weight NUMBER;
1607 l_avail_weight NUMBER;
1608 BEGIN
1609 -- missing input parameters, something is wrong -> null capacity
1610 IF p_organization_id = g_miss_num
1611 OR p_subinventory_code = g_miss_char
1612 OR p_locator_id = g_miss_num
1613 OR p_inventory_item_id = g_miss_num
1614 OR p_unit_weight = g_miss_num
1615 OR p_unit_weight_uom_code = g_miss_char
1616 OR p_primary_uom = g_miss_char
1617 OR p_transaction_uom = g_miss_char
1618 OR p_base_uom = g_miss_char THEN
1619 RETURN NULL;
1620 -- if no sub specified, something is wrong -> null capacity
1621 ELSIF p_organization_id IS NULL
1622 OR p_subinventory_code IS NULL
1623 OR p_inventory_item_id IS NULL THEN
1624 RETURN NULL;
1625 ELSIF p_primary_uom IS NULL
1626 OR p_transaction_uom IS NULL
1627 OR p_base_uom IS NULL THEN
1628 RETURN 0;
1629 -- Mising item setup data regarding volume
1630 -- -> assumption: item has no volume -> used up capacity
1631 -- If setup data is missing, return 0
1632 ELSIF p_unit_weight IS NULL
1633 OR p_unit_weight_uom_code IS NULL THEN
1634 RETURN 0;
1635 -- if unit volume is zero or negative -> same as above
1636 -- If setup data is missing or wrong, return 0
1637 ELSIF p_unit_weight <= 0 THEN
1638 RETURN 0;
1639 -- if no locator specified -> sub w/o loc -> infinite capacity
1640 ELSIF p_locator_id IS NULL THEN
1641 RETURN 0;
1642 ELSE
1643 inv_loc_wms_utils.get_locator_weight_capacity(
1644 x_return_status => l_return_status
1645 , x_msg_count => l_msg_count
1646 , x_msg_data => l_msg_data
1647 , x_location_weight_uom_code => l_weight_uom_code
1648 , x_max_weight => l_max_weight
1649 , x_current_weight => l_cur_weight
1650 , x_suggested_weight => l_sug_weight
1651 , x_available_weight => l_avail_weight
1652 , p_organization_id => p_organization_id
1653 , p_inventory_location_id => p_locator_id
1654 );
1655 -- if cur and sug weight are NULL, set them to 0
1656 -- If setup data is missing or wrong, return infinite
1657 l_capacity := NVL(l_cur_weight, 0) + NVL(l_sug_weight, 0);
1658
1659 IF l_capacity <= 0 THEN
1660 RETURN 0;
1661 END IF;
1662
1663 IF p_unit_weight_uom_code <> l_weight_uom_code THEN
1664 l_capacity := inv_convert.inv_um_convert_new(
1665 p_inventory_item_id
1666 , NULL
1667 , l_capacity
1668 , l_weight_uom_code
1669 , p_unit_weight_uom_code
1670 , NULL
1671 , NULL, 'W'
1672 )
1673 / p_unit_weight;
1674 IF l_capacity <= 0 THEN
1675 RETURN 0;
1676 END IF;
1677 ELSE
1678 l_capacity := l_capacity / p_unit_weight;
1679 END IF;
1680
1681 IF p_primary_uom <> p_transaction_uom THEN
1682 l_capacity := inv_convert.inv_um_convert(p_inventory_item_id, NULL, l_capacity, p_primary_uom, p_transaction_uom, NULL, NULL);
1683 IF l_capacity <= 0 THEN
1684 RETURN 0;
1685 END IF;
1686 END IF;
1687
1688 -- l_capacity is negative if setup data is missing or wrong
1689 -- If setup data is missing or wrong, return infinite
1690 IF l_capacity < 0 THEN
1691 RETURN 0;
1692 END IF;
1693
1694 l_capacity := rounddown(l_capacity, p_transaction_uom, p_inventory_item_id, p_base_uom);
1695
1696 END IF;
1697
1698 RETURN l_capacity;
1699 END getoccupiedweightcapacity;
1700
1701 --
1702 -- API name : GetAvailableVolumeCapacity
1703 -- Type : Private
1704 -- Function : Returns available volume capacity of a location measured in
1705 -- transaction UOM of the actual item.
1706 -- Meaning: The function determines, how many items, measured in
1707 -- transaction UOM, will fit into the location
1708 -- according to their volume considering the capacity
1709 -- already occupied by on-hand stock.
1710 -- ( Used for capacity calculation parameters )
1711 -- Notes : Since there are several situations, where volume capacity can
1712 -- not be calculated, the following definitions are made:
1713 -- - in case of subinventories w/o locators, capacity is
1714 -- infinite
1715 -- - in case of missing setup data at the locator, capacity is
1716 -- infinite
1717 -- - in case of missing setup data at the item, capacity is
1718 -- infinite ( meaning: item then has no volume )
1719 FUNCTION getavailablevolumecapacity(
1720 p_organization_id IN NUMBER DEFAULT g_miss_num
1721 , p_subinventory_code IN VARCHAR2 DEFAULT g_miss_char
1722 , p_locator_id IN NUMBER DEFAULT g_miss_num
1723 , p_inventory_item_id IN NUMBER DEFAULT g_miss_num
1724 , p_unit_volume IN NUMBER DEFAULT g_miss_num
1725 , p_unit_volume_uom_code IN VARCHAR2 DEFAULT g_miss_char
1726 , p_primary_uom IN VARCHAR2 DEFAULT g_miss_char
1727 , p_transaction_uom IN VARCHAR2 DEFAULT g_miss_char
1728 , p_base_uom IN VARCHAR2 DEFAULT g_miss_char
1729 )
1730 RETURN NUMBER IS
1731 l_total_capacity NUMBER;
1732 l_occupied_capacity NUMBER;
1733 l_capacity NUMBER;
1734 l_return_status VARCHAR2(1);
1735 l_msg_count NUMBER;
1736 l_msg_data VARCHAR2(1);
1737 l_volume_uom_code VARCHAR2(3);
1738 l_max_cubic_area NUMBER;
1739 l_cur_cubic_area NUMBER;
1740 l_sug_cubic_area NUMBER;
1741 l_avail_cubic_area NUMBER;
1742 BEGIN
1743 -- missing input parameters, something is wrong -> null capacity
1744 IF p_organization_id = g_miss_num
1745 OR p_subinventory_code = g_miss_char
1746 OR p_locator_id = g_miss_num
1747 OR p_inventory_item_id = g_miss_num
1748 OR p_unit_volume = g_miss_num
1749 OR p_unit_volume_uom_code = g_miss_char
1750 OR p_primary_uom = g_miss_char
1751 OR p_transaction_uom = g_miss_char
1752 OR p_base_uom = g_miss_char THEN
1753 RETURN NULL;
1754 -- if no sub specified, something is wrong -> null capacity
1755 ELSIF p_organization_id IS NULL
1756 OR p_subinventory_code IS NULL
1757 OR p_inventory_item_id IS NULL THEN
1758 RETURN NULL;
1759 ELSIF p_primary_uom IS NULL
1760 OR p_transaction_uom IS NULL
1761 OR p_base_uom IS NULL THEN
1762 RETURN 1e125;
1763 -- Mising item setup data regarding volume
1764 -- -> assumption: item has no volume -> used up capacity
1765 -- If setup data is missing, return infinite
1766 ELSIF p_unit_volume IS NULL
1767 OR p_unit_volume_uom_code IS NULL THEN
1768 RETURN 1e125;
1769 -- if unit volume is zero or negative -> same as above
1770 -- If setup data is missing or wrong, return infinite
1771 ELSIF p_unit_volume <= 0 THEN
1772 RETURN 1e125;
1773 -- if no locator specified -> sub w/o loc -> infinite capacity
1774 ELSIF p_locator_id IS NULL THEN
1775 RETURN 1e125;
1776 ELSE
1777 inv_loc_wms_utils.get_locator_volume_capacity(
1778 x_return_status => l_return_status
1779 , x_msg_count => l_msg_count
1780 , x_msg_data => l_msg_data
1781 , x_volume_uom_code => l_volume_uom_code
1782 , x_max_cubic_area => l_max_cubic_area
1783 , x_current_cubic_area => l_cur_cubic_area
1784 , x_suggested_cubic_area => l_sug_cubic_area
1785 , x_available_cubic_area => l_avail_cubic_area
1786 , p_organization_id => p_organization_id
1787 , p_inventory_location_id => p_locator_id
1788 );
1789
1790 -- avail cubic area is NULL if setup data is missing
1791 -- If setup data is missing or wrong, return infinite
1792 IF l_avail_cubic_area IS NULL THEN
1793 RETURN 1e125;
1794 END IF;
1795
1796 IF l_avail_cubic_area <= 0 THEN
1797 RETURN 0;
1798 END IF;
1799
1800 IF p_unit_volume_uom_code <> l_volume_uom_code THEN
1801 l_capacity := inv_convert.inv_um_convert_new(
1802 p_inventory_item_id
1803 , NULL
1804 , l_avail_cubic_area
1805 , l_volume_uom_code
1806 , p_unit_volume_uom_code
1807 , NULL
1808 , NULL, 'V'
1809 )
1810 / p_unit_volume;
1811 IF l_capacity < 0 THEN
1812 RETURN 1e125;
1813 END IF;
1814 ELSE
1815 l_capacity := l_avail_cubic_area / p_unit_volume;
1816 END IF;
1817
1818 IF p_primary_uom <> p_transaction_uom THEN
1819 l_capacity := inv_convert.inv_um_convert(p_inventory_item_id, NULL, l_capacity, p_primary_uom, p_transaction_uom, NULL, NULL);
1820 IF l_capacity < 0 THEN
1821 RETURN 1e125;
1822 END IF;
1823 END IF;
1824
1825 -- l_capacity is negative if setup data is missing or wrong
1826 -- If setup data is missing or wrong, return infinite
1827 IF l_capacity < 0 THEN
1828 RETURN 1e125;
1829 END IF;
1830
1831 l_capacity := rounddown(l_capacity, p_transaction_uom, p_inventory_item_id, p_base_uom);
1832
1833 -- l_capacity is negative if setup data is missing or wrong
1834 -- If setup data is missing or wrong, return infinite
1835 IF l_capacity < 0 THEN
1836 RETURN 1e125;
1837 END IF;
1838 END IF;
1839
1840 RETURN l_capacity;
1841 END getavailablevolumecapacity;
1842
1843 --
1844 -- API name : GetAvailableWeightCapacity
1845 -- Type : Private
1846 -- Function : Returns available weight capacity of a location measured in
1847 -- transaction UOM of the actual item.
1848 -- Meaning: The function determines, how many items, measured in
1849 -- transaction UOM, will fit into the location
1850 -- according to their weight considering the capacity
1851 -- already occupied by on-hand stock.
1852 -- ( Used for capacity calculation parameters )
1853 -- Notes : Since there are several situations, where weight capacity can
1854 -- not be calculated, the following definitions are made:
1855 -- - in case of subinventories w/o locators, capacity is
1856 -- infinite
1857 -- - in case of missing setup data at the locator, capacity is
1858 -- infinite
1859 -- - in case of missing setup data at the item, capacity is
1860 -- infinite ( meaning: item then has no weight )
1861 FUNCTION getavailableweightcapacity(
1862 p_organization_id IN NUMBER DEFAULT g_miss_num
1863 , p_subinventory_code IN VARCHAR2 DEFAULT g_miss_char
1864 , p_locator_id IN NUMBER DEFAULT g_miss_num
1865 , p_inventory_item_id IN NUMBER DEFAULT g_miss_num
1866 , p_unit_weight IN NUMBER DEFAULT g_miss_num
1867 , p_unit_weight_uom_code IN VARCHAR2 DEFAULT g_miss_char
1868 , p_primary_uom IN VARCHAR2 DEFAULT g_miss_char
1869 , p_transaction_uom IN VARCHAR2 DEFAULT g_miss_char
1870 , p_base_uom IN VARCHAR2 DEFAULT g_miss_char
1871 )
1872 RETURN NUMBER IS
1873 l_total_capacity NUMBER;
1874 l_occupied_capacity NUMBER;
1875 l_capacity NUMBER;
1876 l_return_status VARCHAR2(1);
1877 l_msg_count NUMBER;
1878 l_msg_data VARCHAR2(1);
1879 l_weight_uom_code VARCHAR2(3);
1880 l_max_weight NUMBER;
1881 l_cur_weight NUMBER;
1882 l_sug_weight NUMBER;
1883 l_avail_weight NUMBER;
1884 BEGIN
1885 -- missing input parameters, something is wrong -> null capacity
1886 IF p_organization_id = g_miss_num
1887 OR p_subinventory_code = g_miss_char
1888 OR p_locator_id = g_miss_num
1889 OR p_inventory_item_id = g_miss_num
1890 OR p_unit_weight = g_miss_num
1891 OR p_unit_weight_uom_code = g_miss_char
1892 OR p_primary_uom = g_miss_char
1893 OR p_transaction_uom = g_miss_char
1894 OR p_base_uom = g_miss_char THEN
1895 RETURN NULL;
1896 -- if no sub specified, something is wrong -> null capacity
1897 ELSIF p_organization_id IS NULL
1898 OR p_subinventory_code IS NULL
1899 OR p_inventory_item_id IS NULL THEN
1900 RETURN NULL;
1901 ELSIF p_primary_uom IS NULL
1902 OR p_transaction_uom IS NULL
1903 OR p_base_uom IS NULL THEN
1904 RETURN 1e125;
1905 -- Mising item setup data regarding volume
1906 -- -> assumption: item has no volume -> used up capacity
1907 -- If setup data is missing, return infinite
1908 ELSIF p_unit_weight IS NULL
1909 OR p_unit_weight_uom_code IS NULL THEN
1910 RETURN 1e125;
1911 -- if unit volume is zero or negative -> same as above
1912 -- If setup data is missing or wrong, return infinite
1913 ELSIF p_unit_weight <= 0 THEN
1914 RETURN 1e125;
1915 -- if no locator specified -> sub w/o loc -> infinite capacity
1916 ELSIF p_locator_id IS NULL THEN
1917 RETURN 1e125;
1918 ELSE
1919 inv_loc_wms_utils.get_locator_weight_capacity(
1920 x_return_status => l_return_status
1921 , x_msg_count => l_msg_count
1922 , x_msg_data => l_msg_data
1923 , x_location_weight_uom_code => l_weight_uom_code
1924 , x_max_weight => l_max_weight
1925 , x_current_weight => l_cur_weight
1926 , x_suggested_weight => l_sug_weight
1927 , x_available_weight => l_avail_weight
1928 , p_organization_id => p_organization_id
1929 , p_inventory_location_id => p_locator_id
1930 );
1931
1932 -- avail weight is NULL if setup data is missing
1933 -- If setup data is missing or wrong, return infinite
1934 IF l_avail_weight IS NULL THEN
1935 RETURN 1e125;
1936 END IF;
1937
1938 IF l_avail_weight <= 0 THEN
1939 RETURN 0;
1940 END IF;
1941
1942 IF p_unit_weight_uom_code <> l_weight_uom_code THEN
1943 l_capacity := inv_convert.inv_um_convert_new(
1944 p_inventory_item_id
1945 , NULL
1946 , l_avail_weight
1947 , l_weight_uom_code
1948 , p_unit_weight_uom_code
1949 , NULL
1950 , NULL, 'W'
1951 )
1952 / p_unit_weight;
1953 -- l_capacity is negative if setup data is missing or wrong
1954 -- If setup data is missing or wrong, return infinite
1955 IF l_capacity < 0 THEN
1956 RETURN 1e125;
1957 END IF;
1958 ELSE
1959 l_capacity := l_avail_weight / p_unit_weight;
1960 END IF;
1961
1962 IF p_primary_uom <> p_transaction_uom THEN
1963 l_capacity := inv_convert.inv_um_convert(p_inventory_item_id, NULL, l_capacity, p_primary_uom, p_transaction_uom, NULL, NULL);
1964 -- l_capacity is negative if setup data is missing or wrong
1965 -- If setup data is missing or wrong, return infinite
1966 IF l_capacity < 0 THEN
1967 RETURN 1e125;
1968 END IF;
1969 END IF;
1970
1971 -- l_capacity is negative if setup data is missing or wrong
1972 -- If setup data is missing or wrong, return infinite
1973 IF l_capacity < 0 THEN
1974 RETURN 1e125;
1975 END IF;
1976
1977 l_capacity := rounddown(l_capacity, p_transaction_uom, p_inventory_item_id, p_base_uom);
1978
1979 -- l_capacity is negative if setup data is missing or wrong
1980 -- If setup data is missing or wrong, return infinite
1981 IF l_capacity < 0 THEN
1982 RETURN 1e125;
1983 END IF;
1984 END IF;
1985
1986 RETURN l_capacity;
1987 END getavailableweightcapacity;
1988
1989 --
1990 -- API name : GetRemainingVolumeCapacity
1991 -- Type : Private
1992 -- Function : Returns remaining available volume capacity of a location
1993 -- measured in transaction UOM of the actual item.
1994 -- Meaning: The function determines, how many items, measured in
1995 -- transaction UOM, will fit into the location
1996 -- according to their volume considering the capacity
1997 -- already occupied by on-hand stock and assuming the
1998 -- actual receipt would have been performed already.
1999 -- ( Used for capacity calculation parameters )
2000 -- Notes : Since there are several situations, where volume capacity can
2001 -- not be calculated, the following definitions are made:
2002 -- - in case of subinventories w/o locators, capacity is
2003 -- infinite
2004 -- - in case of missing setup data at the locator, capacity is
2005 -- infinite
2006 -- - in case of missing setup data at the item, capacity is
2007 -- infinite ( meaning: item then has no volume )
2008 FUNCTION getremainingvolumecapacity(
2009 p_organization_id IN NUMBER DEFAULT g_miss_num
2010 , p_subinventory_code IN VARCHAR2 DEFAULT g_miss_char
2011 , p_locator_id IN NUMBER DEFAULT g_miss_num
2012 , p_inventory_item_id IN NUMBER DEFAULT g_miss_num
2013 , p_unit_volume IN NUMBER DEFAULT g_miss_num
2014 , p_unit_volume_uom_code IN VARCHAR2 DEFAULT g_miss_char
2015 , p_primary_uom IN VARCHAR2 DEFAULT g_miss_char
2016 , p_transaction_uom IN VARCHAR2 DEFAULT g_miss_char
2017 , p_base_uom IN VARCHAR2 DEFAULT g_miss_char
2018 , p_transaction_quantity IN NUMBER DEFAULT g_miss_num
2019 )
2020 RETURN NUMBER IS
2021 l_available_capacity NUMBER;
2022 l_capacity NUMBER;
2023 BEGIN
2024 --
2025 -- if transaction quantity is null or missing -> something is wrong -> abort
2026 IF p_transaction_quantity = g_miss_num
2027 OR p_transaction_quantity IS NULL THEN
2028 RETURN NULL;
2029 END IF;
2030
2031 --
2032 -- get available capacity
2033 l_available_capacity := getavailablevolumecapacity(
2034 p_organization_id
2035 , p_subinventory_code
2036 , p_locator_id
2037 , p_inventory_item_id
2038 , p_unit_volume
2039 , p_unit_volume_uom_code
2040 , p_primary_uom
2041 , p_transaction_uom
2042 , p_base_uom
2043 );
2044
2045 --
2046 -- if available capacity is null -> something is wrong -> abort
2047 IF l_available_capacity IS NULL THEN
2048 RETURN l_available_capacity;
2049 --
2050 -- if available capacity is infinite -> no need to subtract anything
2051 ELSIF l_available_capacity = 1e125 THEN
2052 RETURN l_available_capacity;
2053 --
2054 -- if total capacity is zero or less -> no need to subtract anything
2055 ELSIF l_available_capacity <= 0 THEN
2056 RETURN 0;
2057 ELSE
2058 -- otherwise remaining = available - txn quantity
2059 l_capacity := l_available_capacity - p_transaction_quantity;
2060 END IF;
2061
2062 --
2063 -- we don't return any negative capacity
2064 IF l_capacity <= 0 THEN
2065 RETURN 0;
2066 END IF;
2067
2068 -- No need to round - we did it in GetAvailable...
2069 -- round it reasonably
2070 --l_capacity := rounddown(l_capacity, p_transaction_uom, p_inventory_item_id, p_base_uom);
2071
2072 --
2073 IF l_capacity <= 0 THEN
2074 RETURN 0;
2075 END IF;
2076
2077 --
2078 RETURN l_capacity;
2079 END getremainingvolumecapacity;
2080
2081 --
2082 -- API name : GetRemainingWeightCapacity
2083 -- Type : Private
2084 -- Function : Returns remaining available weight capacity of a location
2085 -- measured in transaction UOM of the actual item.
2086 -- Meaning: The function determines, how many items, measured in
2087 -- transaction UOM, will fit into the location
2088 -- according to their weight considering the capacity
2089 -- already occupied by on-hand stock and assuming the
2090 -- actual receipt would have been performed already.
2091 -- ( Used for capacity calculation parameters )
2092 -- Notes : Since there are several situations, where weight capacity can
2093 -- not be calculated, the following definitions are made:
2094 -- - in case of subinventories w/o locators, capacity is
2095 -- infinite
2096 -- - in case of missing setup data at the locator, capacity is
2097 -- infinite
2098 -- - in case of missing setup data at the item, capacity is
2099 -- infinite ( meaning: item then has no weight )
2100 FUNCTION getremainingweightcapacity(
2101 p_organization_id IN NUMBER DEFAULT g_miss_num
2102 , p_subinventory_code IN VARCHAR2 DEFAULT g_miss_char
2103 , p_locator_id IN NUMBER DEFAULT g_miss_num
2104 , p_inventory_item_id IN NUMBER DEFAULT g_miss_num
2105 , p_unit_weight IN NUMBER DEFAULT g_miss_num
2106 , p_unit_weight_uom_code IN VARCHAR2 DEFAULT g_miss_char
2107 , p_primary_uom IN VARCHAR2 DEFAULT g_miss_char
2108 , p_transaction_uom IN VARCHAR2 DEFAULT g_miss_char
2109 , p_base_uom IN VARCHAR2 DEFAULT g_miss_char
2110 , p_transaction_quantity IN NUMBER DEFAULT g_miss_num
2111 )
2112 RETURN NUMBER IS
2113 l_available_capacity NUMBER;
2114 l_capacity NUMBER;
2115 BEGIN
2116 --
2117 -- if transaction quantity is null or missing -> something is wrong -> abort
2118 IF p_transaction_quantity = g_miss_num
2119 OR p_transaction_quantity IS NULL THEN
2120 RETURN NULL;
2121 END IF;
2122
2123 --
2124 -- get available capacity
2125 l_available_capacity := getavailableweightcapacity(
2126 p_organization_id
2127 , p_subinventory_code
2128 , p_locator_id
2129 , p_inventory_item_id
2130 , p_unit_weight
2131 , p_unit_weight_uom_code
2132 , p_primary_uom
2133 , p_transaction_uom
2134 , p_base_uom
2135 );
2136
2137 --
2138 -- if available capacity is null -> something is wrong -> abort
2139 IF l_available_capacity IS NULL THEN
2140 RETURN l_available_capacity;
2141 --
2142 -- if available capacity is infinite -> no need to subtract anything
2143 ELSIF l_available_capacity = 1e125 THEN
2144 RETURN l_available_capacity;
2145 --
2146 -- if total capacity is zero or less -> no need to subtract anything
2147 ELSIF l_available_capacity <= 0 THEN
2148 RETURN 0;
2149 ELSE
2150 -- otherwise remaining = available - txn quantity
2151 l_capacity := l_available_capacity - p_transaction_quantity;
2152 END IF;
2153
2154 --
2155 -- we don't return any negative capacity
2156 IF l_capacity <= 0 THEN
2157 RETURN 0;
2158 END IF;
2159
2160 -- No need to round - we did it in GetAvailable...
2161 -- round it reasonably
2162 --l_capacity := rounddown(l_capacity, p_transaction_uom, p_inventory_item_id, p_base_uom);
2163
2164 --
2165 IF l_capacity <= 0 THEN
2166 RETURN 0;
2167 END IF;
2168
2169 --
2170 RETURN l_capacity;
2171 END getremainingweightcapacity;
2172
2173
2174 --
2175 -- API name : GetMinimumTotalVWCapacity
2176 -- Type : Private
2177 -- Function : Returns the minimum of total volume and total weight capacity
2178 -- of a location measured in transaction UOM of the actual item.
2179 -- ( Used for capacity calculation parameters )
2180 -- Notes : refer to notes regarding the corresponding separate volume
2181 -- and weight capacity functions.
2182 --
2183 FUNCTION GetMinimumTotalVWCapacity
2184 ( p_organization_id IN NUMBER DEFAULT g_miss_num
2185 ,p_subinventory_code IN VARCHAR2 DEFAULT g_miss_char
2186 ,p_locator_id IN NUMBER DEFAULT g_miss_num
2187 ,p_inventory_item_id IN NUMBER DEFAULT g_miss_num
2188 ,p_unit_volume IN NUMBER DEFAULT g_miss_num
2189 ,p_unit_volume_uom_code IN VARCHAR2 DEFAULT g_miss_char
2190 ,p_unit_weight IN NUMBER DEFAULT g_miss_num
2191 ,p_unit_weight_uom_code IN VARCHAR2 DEFAULT g_miss_char
2192 ,p_primary_uom IN VARCHAR2 DEFAULT g_miss_char
2193 ,p_transaction_uom IN VARCHAR2 DEFAULT g_miss_char
2194 ,p_base_uom IN VARCHAR2 DEFAULT g_miss_char
2195 ,p_location_maximum_units IN NUMBER
2196 ,p_location_current_units IN NUMBER
2197 ,p_location_suggested_units IN NUMBER
2198 ,p_location_available_units IN NUMBER
2199 ,p_weight_uom_code IN VARCHAR2
2200 ,p_max_weight IN NUMBER
2201 ,p_current_weight IN NUMBER
2202 ,p_suggested_weight IN NUMBER
2203 ,p_available_weight IN NUMBER
2204 ,p_volume_uom_code IN VARCHAR2
2205 ,p_max_cubic_area IN NUMBER
2206 ,p_current_cubic_area IN NUMBER
2207 ,p_suggested_cubic_area IN NUMBER
2208 ,p_available_cubic_area IN NUMBER
2209
2210 ) RETURN NUMBER
2211 IS
2212
2213 l_weight_capacity NUMBER;
2214 l_volume_capacity NUMBER;
2215 l_capacity NUMBER;
2216
2217 BEGIN
2218
2219 -- if necessary parameters are present, convert max weight into
2220 -- txn UOM
2221 IF p_max_weight IS NULL OR
2222 p_unit_weight IS NULL OR
2223 p_unit_weight_uom_code IS NULL OR
2224 p_unit_weight <= 0 THEN
2225
2226 l_weight_capacity := 1e125;
2227 ELSE
2228 IF p_unit_weight_uom_code <> p_weight_uom_code THEN
2229 l_weight_capacity := inv_convert.inv_um_convert_new
2230 (p_inventory_item_id,
2231 NULL,
2232 p_max_weight,
2233 p_weight_uom_code,
2234 p_unit_weight_uom_code,
2235 NULL,
2236 NULL, 'W') / p_unit_weight;
2237 ELSE
2238 l_weight_capacity := p_max_weight / p_unit_weight;
2239 END IF;
2240
2241 -- l_capacity is negative if setup data is missing or wrong
2242 -- If setup data is missing or wrong, return infinite
2243 IF l_weight_capacity < 0 THEN
2244 l_weight_capacity := 1e125;
2245 END IF;
2246 END IF;
2247
2248 -- if necessary parameters are present, convert max volume into
2249 -- txn UOM
2250 IF p_max_cubic_area IS NULL OR
2251 p_unit_volume IS NULL OR
2252 p_unit_volume_uom_code IS NULL OR
2253 p_unit_volume <= 0 THEN
2254
2255 l_volume_capacity := 1e125;
2256 ELSE
2257 IF p_unit_volume_uom_code <> p_volume_uom_code THEN
2258 l_volume_capacity := inv_convert.inv_um_convert_new
2259 (p_inventory_item_id,
2260 NULL,
2261 p_max_cubic_area,
2262 p_volume_uom_code,
2263 p_unit_volume_uom_code,
2264 NULL,
2265 NULL, 'V') / p_unit_volume;
2266 ELSE
2267 l_volume_capacity := p_max_cubic_area / p_unit_volume;
2268 END IF;
2269
2270 -- l_capacity is negative if setup data is missing or wrong
2271 -- If setup data is missing or wrong, return infinite
2272 IF l_volume_capacity < 0 THEN
2273 l_volume_capacity := 1e125;
2274 END IF;
2275 END IF;
2276
2277 -- Find minimum value
2278 IF l_weight_capacity < l_volume_capacity THEN
2279 l_capacity := l_weight_capacity;
2280 ELSE
2281 l_capacity := l_volume_capacity;
2282 END IF;
2283
2284 --no need to convert or round if capacity is zero or infinite
2285 IF l_capacity NOT IN (1e125,0) THEN
2286 IF p_primary_uom <> p_transaction_uom THEN
2287 l_capacity:= inv_convert.inv_um_convert
2288 (p_inventory_item_id,
2289 NULL,
2290 l_capacity,
2291 p_primary_uom,
2292 p_transaction_uom,
2293 NULL,
2294 NULL);
2295 END IF;
2296
2297 -- Round Down
2298 l_capacity := RoundDown(l_capacity,
2299 p_transaction_uom,
2300 p_inventory_item_id,
2301 p_base_uom);
2302 END IF;
2303 -- l_capacity is negative if setup data is missing or wrong
2304 -- If setup data is missing or wrong, return infinite
2305 IF l_capacity < 0 THEN
2306 RETURN 1e125;
2307 END IF;
2308
2309 RETURN l_capacity;
2310
2311 END GetMinimumTotalVWCapacity;
2312
2313 --
2314 -- API name : GetMinimumTotalVWCapacity
2315 -- Type : Private
2316 -- Function : Returns the minimum of total volume and total weight capacity
2317 -- of a location measured in transaction UOM of the actual item.
2318 -- ( Used for capacity calculation parameters )
2319 -- Notes : refer to notes regarding the corresponding separate volume
2320 -- and weight capacity functions.
2321 --
2322 FUNCTION GetMinimumTotalVWCapacity
2323 ( p_organization_id IN NUMBER DEFAULT g_miss_num
2324 ,p_subinventory_code IN VARCHAR2 DEFAULT g_miss_char
2325 ,p_locator_id IN NUMBER DEFAULT g_miss_num
2326 ,p_inventory_item_id IN NUMBER DEFAULT g_miss_num
2327 ,p_unit_volume IN NUMBER DEFAULT g_miss_num
2328 ,p_unit_volume_uom_code IN VARCHAR2 DEFAULT g_miss_char
2329 ,p_unit_weight IN NUMBER DEFAULT g_miss_num
2330 ,p_unit_weight_uom_code IN VARCHAR2 DEFAULT g_miss_char
2331 ,p_primary_uom IN VARCHAR2 DEFAULT g_miss_char
2332 ,p_transaction_uom IN VARCHAR2 DEFAULT g_miss_char
2333 ,p_base_uom IN VARCHAR2 DEFAULT g_miss_char
2334 ) RETURN NUMBER
2335 IS
2336
2337 l_capacity NUMBER;
2338
2339 l_return_status VARCHAR2(1);
2340 l_msg_count NUMBER;
2341 l_msg_data VARCHAR2(1);
2342
2343 l_loc_max_units NUMBER;
2344 l_loc_cur_units NUMBER;
2345 l_loc_sug_units NUMBER;
2346 l_loc_avail_units NUMBER;
2347
2348
2349 l_volume_uom_code VARCHAR2(3);
2350 l_max_cubic_area NUMBER;
2351 l_cur_cubic_area NUMBER;
2352 l_sug_cubic_area NUMBER;
2353 l_avail_cubic_area NUMBER;
2354
2355 l_weight_uom_code VARCHAR2(3);
2356 l_max_weight NUMBER;
2357 l_cur_weight NUMBER;
2358 l_sug_weight NUMBER;
2359 l_avail_weight NUMBER;
2360
2361
2362 BEGIN
2363 --
2364 /*
2365 l_capacity :=
2366 GetTotalVolumeCapacity ( p_organization_id
2367 ,p_subinventory_code
2368 ,p_locator_id
2369 ,p_inventory_item_id
2370 ,p_unit_volume
2371 ,p_unit_volume_uom_code
2372 ,p_primary_uom
2373 ,p_transaction_uom
2374 ,p_base_uom );
2375 --
2376 l_tempcapa :=
2377 GetTotalWeightCapacity ( p_organization_id
2378 ,p_subinventory_code
2379 ,p_locator_id
2380 ,p_inventory_item_id
2381 ,p_unit_weight
2382 ,p_unit_weight_uom_code
2383 ,p_primary_uom
2384 ,p_transaction_uom
2385 ,p_base_uom );
2386 --
2387 */
2388
2389 -- missing input parameters, something is wrong -> null capacity
2390 IF p_organization_id = g_miss_num
2391 OR p_subinventory_code = g_miss_char
2392 OR p_locator_id = g_miss_num
2393 OR p_inventory_item_id = g_miss_num
2394 OR p_unit_volume = g_miss_num
2395 OR p_unit_volume_uom_code = g_miss_char
2396 OR p_unit_weight = g_miss_num
2397 OR p_unit_weight_uom_code = g_miss_char
2398 OR p_primary_uom = g_miss_char
2399 OR p_transaction_uom = g_miss_char
2400 OR p_base_uom = g_miss_char
2401 THEN
2402 RETURN NULL;
2403 -- if no sub specified, something is wrong -> null capacity
2404 ELSIF p_organization_id IS NULL
2405 OR p_subinventory_code IS NULL
2406 OR p_inventory_item_id IS NULL
2407 THEN
2408 RETURN NULL;
2409 ELSIF p_primary_uom IS NULL
2410 OR p_transaction_uom IS NULL
2411 OR p_base_uom IS NULL
2412 THEN
2413 RETURN 1e125;
2414 -- Mising item setup data regarding volume
2415 -- -> assumption: item has no volume -> used up capacity
2416 -- If setup data is missing, return infinite
2417 ELSIF (p_unit_volume IS NULL
2418 OR p_unit_volume_uom_code IS NULL) AND
2419 (p_unit_weight IS NULL
2420 OR p_unit_weight_uom_code IS NULL)
2421 THEN
2422 RETURN 1e125;
2423 -- if unit volume is zero or negative -> same as above
2424 -- If setup data is missing or wrong, return infinite
2425 ELSIF p_unit_volume <= 0 AND
2426 p_unit_weight <= 0
2427 THEN
2428 RETURN 1e125;
2429 -- if no locator specified -> sub w/o loc -> infinite capacity
2430 ELSIF p_locator_id IS NULL THEN
2431 RETURN 1e125;
2432 END IF;
2433
2434 inv_loc_wms_utils.get_locator_capacity
2435 (x_return_status => l_return_status
2436 ,x_msg_count => l_msg_count
2437 ,x_msg_data => l_msg_data
2438 ,x_location_maximum_units => l_loc_max_units
2439 ,x_location_current_units => l_loc_cur_units
2440 ,x_location_suggested_units => l_loc_sug_units
2441 ,x_location_available_units => l_loc_avail_units
2442 ,x_location_weight_uom_code => l_weight_uom_code
2443 ,x_max_weight => l_max_weight
2444 ,x_current_weight => l_cur_weight
2445 ,x_suggested_weight => l_sug_weight
2446 ,x_available_weight => l_avail_weight
2447 ,x_volume_uom_code => l_volume_uom_code
2448 ,x_max_cubic_area => l_max_cubic_area
2449 ,x_current_cubic_area => l_cur_cubic_area
2450 ,x_suggested_cubic_area => l_sug_cubic_area
2451 ,x_available_cubic_area => l_avail_cubic_area
2452 ,p_organization_id => p_organization_id
2453 ,p_inventory_location_id => p_locator_id
2454 );
2455
2456 l_capacity := GetMinimumTotalVWCapacity
2457 ( p_organization_id
2458 ,p_subinventory_code
2459 ,p_locator_id
2460 ,p_inventory_item_id
2461 ,p_unit_volume
2462 ,p_unit_volume_uom_code
2463 ,p_unit_weight
2464 ,p_unit_weight_uom_code
2465 ,p_primary_uom
2466 ,p_transaction_uom
2467 ,p_base_uom
2468 ,l_loc_max_units
2469 ,l_loc_cur_units
2470 ,l_loc_sug_units
2471 ,l_loc_avail_units
2472 ,l_weight_uom_code
2473 ,l_max_weight
2474 ,l_cur_weight
2475 ,l_sug_weight
2476 ,l_avail_weight
2477 ,l_volume_uom_code
2478 ,l_max_cubic_area
2479 ,l_cur_cubic_area
2480 ,l_sug_cubic_area
2481 ,l_avail_cubic_area);
2482
2483 RETURN l_capacity;
2484
2485
2486 END GetMinimumTotalVWCapacity;
2487
2488 --
2489 -- API name : GetMaximumOccupiedVWCapacity
2490 -- Type : Private
2491 -- Function : Returns the maximum of occupied volume and occupied weight
2492 -- capacity of a location measured in transaction UOM of the
2493 -- actual item.
2494 -- ( Used for capacity calculation parameters )
2495 -- Notes : refer to notes regarding the corresponding separate volume
2496 -- and weight capacity functions.
2497 FUNCTION GetMaximumOccupiedVWCapacity
2498 ( p_organization_id IN NUMBER DEFAULT g_miss_num
2499 ,p_subinventory_code IN VARCHAR2 DEFAULT g_miss_char
2500 ,p_locator_id IN NUMBER DEFAULT g_miss_num
2501 ,p_inventory_item_id IN NUMBER DEFAULT g_miss_num
2502 ,p_unit_volume IN NUMBER DEFAULT g_miss_num
2503 ,p_unit_volume_uom_code IN VARCHAR2 DEFAULT g_miss_char
2504 ,p_unit_weight IN NUMBER DEFAULT g_miss_num
2505 ,p_unit_weight_uom_code IN VARCHAR2 DEFAULT g_miss_char
2506 ,p_primary_uom IN VARCHAR2 DEFAULT g_miss_char
2507 ,p_transaction_uom IN VARCHAR2 DEFAULT g_miss_char
2508 ,p_base_uom IN VARCHAR2 DEFAULT g_miss_char
2509 ,p_location_maximum_units IN NUMBER
2510 ,p_location_current_units IN NUMBER
2511 ,p_location_suggested_units IN NUMBER
2512 ,p_location_available_units IN NUMBER
2513 ,p_weight_uom_code IN VARCHAR2
2514 ,p_max_weight IN NUMBER
2515 ,p_current_weight IN NUMBER
2516 ,p_suggested_weight IN NUMBER
2517 ,p_available_weight IN NUMBER
2518 ,p_volume_uom_code IN VARCHAR2
2519 ,p_max_cubic_area IN NUMBER
2520 ,p_current_cubic_area IN NUMBER
2521 ,p_suggested_cubic_area IN NUMBER
2522 ,p_available_cubic_area IN NUMBER
2523
2524 ) RETURN NUMBER
2525 IS
2526
2527 l_weight_capacity NUMBER;
2528 l_volume_capacity NUMBER;
2529 l_capacity NUMBER;
2530 BEGIN
2531
2532 -- if necessary parameters are present, convert max weight into
2533 -- txn UOM
2534 IF p_unit_weight IS NULL OR
2535 p_unit_weight_uom_code IS NULL OR
2536 p_unit_weight <= 0 THEN
2537
2538 l_weight_capacity := 0;
2539 ELSE
2540 l_weight_capacity := NVL(p_current_weight, 0) + NVL(p_suggested_weight, 0);
2541 IF p_unit_weight_uom_code <> p_weight_uom_code THEN
2542 l_weight_capacity := inv_convert.inv_um_convert_new
2543 (p_inventory_item_id,
2544 NULL,
2545 l_weight_capacity,
2546 p_weight_uom_code,
2547 p_unit_weight_uom_code,
2548 NULL,
2549 NULL, 'W') / p_unit_weight;
2550 ELSE
2551 l_weight_capacity := l_weight_capacity / p_unit_weight;
2552 END IF;
2553
2554 -- l_capacity is negative if setup data is missing or wrong
2555 -- If setup data is missing or wrong, return zero
2556 IF l_weight_capacity < 0 THEN
2557 l_weight_capacity := 0;
2558 END IF;
2559 END IF;
2560
2561 -- if necessary parameters are present, convert max volume into
2562 -- txn UOM
2563 IF p_unit_volume IS NULL OR
2564 p_unit_volume_uom_code IS NULL OR
2565 p_unit_volume <= 0 THEN
2566
2567 l_volume_capacity := 0;
2568 ELSE
2569 l_volume_capacity := NVL(p_current_cubic_area,0) + NVL(p_suggested_cubic_area,0);
2570 IF p_unit_volume_uom_code <> p_volume_uom_code THEN
2571 l_volume_capacity := inv_convert.inv_um_convert_new
2572 (p_inventory_item_id,
2573 NULL,
2574 l_volume_capacity,
2575 p_volume_uom_code,
2576 p_unit_volume_uom_code,
2577 NULL,
2578 NULL, 'V') / p_unit_volume;
2579 ELSE
2580 l_volume_capacity := l_volume_capacity / p_unit_volume;
2581 END IF;
2582
2583 -- l_capacity is negative if setup data is missing or wrong
2584 -- If setup data is missing or wrong, return infinite
2585 IF l_volume_capacity < 0 THEN
2586 l_volume_capacity := 0;
2587 END IF;
2588 END IF;
2589
2590 -- Find minimum value
2591 IF l_weight_capacity > l_volume_capacity THEN
2592 l_capacity := l_weight_capacity;
2593 ELSE
2594 l_capacity := l_volume_capacity;
2595 END IF;
2596
2597 -- if capacity is zero, no need to convert or round
2598 IF l_capacity > 0 THEN
2599 IF p_primary_uom <> p_transaction_uom THEN
2600 l_capacity:= inv_convert.inv_um_convert
2601 (p_inventory_item_id,
2602 NULL,
2603 l_capacity,
2604 p_primary_uom,
2605 p_transaction_uom,
2606 NULL,
2607 NULL);
2608 END IF;
2609
2610 -- Round Down
2611 l_capacity := RoundDown(l_capacity,
2612 p_transaction_uom,
2613 p_inventory_item_id,
2614 p_base_uom);
2615 END IF;
2616
2617 -- l_capacity is negative if setup data is missing or wrong
2618 -- If setup data is missing or wrong, return zero
2619 IF l_capacity < 0 THEN
2620 RETURN 0;
2621 END IF;
2622
2623 return l_capacity;
2624
2625 END;
2626
2627
2628 --
2629 -- API name : GetMaximumOccupiedVWCapacity
2630 -- Type : Private
2631 -- Function : Returns the maximum of occupied volume and occupied weight
2632 -- capacity of a location measured in transaction UOM of the
2633 -- actual item.
2634 -- ( Used for capacity calculation parameters )
2635 -- Notes : refer to notes regarding the corresponding separate volume
2636 -- and weight capacity functions.
2637 FUNCTION GetMaximumOccupiedVWCapacity
2638 ( p_organization_id IN NUMBER DEFAULT g_miss_num
2639 ,p_subinventory_code IN VARCHAR2 DEFAULT g_miss_char
2640 ,p_locator_id IN NUMBER DEFAULT g_miss_num
2641 ,p_inventory_item_id IN NUMBER DEFAULT g_miss_num
2642 ,p_unit_volume IN NUMBER DEFAULT g_miss_num
2643 ,p_unit_volume_uom_code IN VARCHAR2 DEFAULT g_miss_char
2644 ,p_unit_weight IN NUMBER DEFAULT g_miss_num
2645 ,p_unit_weight_uom_code IN VARCHAR2 DEFAULT g_miss_char
2646 ,p_primary_uom IN VARCHAR2 DEFAULT g_miss_char
2647 ,p_transaction_uom IN VARCHAR2 DEFAULT g_miss_char
2648 ,p_base_uom IN VARCHAR2 DEFAULT g_miss_char
2649 ) RETURN NUMBER
2650 IS
2651
2652 l_weight_capacity NUMBER;
2653 l_volume_capacity NUMBER;
2654 l_capacity NUMBER;
2655
2656 l_return_status VARCHAR2(1);
2657 l_msg_count NUMBER;
2658 l_msg_data VARCHAR2(1);
2659
2660 l_loc_max_units NUMBER;
2661 l_loc_cur_units NUMBER;
2662 l_loc_sug_units NUMBER;
2663 l_loc_avail_units NUMBER;
2664
2665
2666 l_volume_uom_code VARCHAR2(3);
2667 l_max_cubic_area NUMBER;
2668 l_cur_cubic_area NUMBER;
2669 l_sug_cubic_area NUMBER;
2670 l_avail_cubic_area NUMBER;
2671
2672 l_weight_uom_code VARCHAR2(3);
2673 l_max_weight NUMBER;
2674 l_cur_weight NUMBER;
2675 l_sug_weight NUMBER;
2676 l_avail_weight NUMBER;
2677
2678 BEGIN
2679 /*
2680 --
2681 l_capacity :=
2682 GetOccupiedVolumeCapacity ( p_organization_id
2683 ,p_subinventory_code
2684 ,p_locator_id
2685 ,p_inventory_item_id
2686 ,p_unit_volume
2687 ,p_unit_volume_uom_code
2688 ,p_primary_uom
2689 ,p_transaction_uom
2690 ,p_base_uom );
2691 --
2692 l_tempcapa :=
2693 GetOccupiedWeightCapacity (p_organization_id
2694 ,p_subinventory_code
2695 ,p_locator_id
2696 ,p_inventory_item_id
2697 ,p_unit_weight
2698 ,p_unit_weight_uom_code
2699 ,p_primary_uom
2700 ,p_transaction_uom
2701 ,p_base_uom );
2702 --
2703 IF l_tempcapa IS NOT NULL THEN
2704 IF l_capacity IS NULL THEN
2705 l_capacity := l_tempcapa;
2706 ELSIF l_capacity < l_tempcapa THEN
2707 l_capacity := l_tempcapa;
2708 END IF;
2709 END IF;
2710 --
2711 RETURN l_capacity;
2712 */
2713
2714 -- missing input parameters, something is wrong -> null capacity
2715 IF p_organization_id = g_miss_num
2716 OR p_subinventory_code = g_miss_char
2717 OR p_locator_id = g_miss_num
2718 OR p_inventory_item_id = g_miss_num
2719 OR p_unit_volume = g_miss_num
2720 OR p_unit_volume_uom_code = g_miss_char
2721 OR p_unit_weight = g_miss_num
2722 OR p_unit_weight_uom_code = g_miss_char
2723 OR p_primary_uom = g_miss_char
2724 OR p_transaction_uom = g_miss_char
2725 OR p_base_uom = g_miss_char
2726 THEN
2727 RETURN NULL;
2728 -- if no sub specified, something is wrong -> null capacity
2729 ELSIF p_organization_id IS NULL
2730 OR p_subinventory_code IS NULL
2731 OR p_inventory_item_id IS NULL
2732 THEN
2733 RETURN NULL;
2734 ELSIF p_primary_uom IS NULL
2735 OR p_transaction_uom IS NULL
2736 OR p_base_uom IS NULL
2737 THEN
2738 RETURN 0;
2739 -- Mising item setup data regarding volume
2740 -- -> assumption: item has no volume -> used up capacity
2741 -- If setup data is missing, return zero
2742 ELSIF (p_unit_volume IS NULL
2743 OR p_unit_volume_uom_code IS NULL) AND
2744 (p_unit_weight IS NULL
2745 OR p_unit_weight_uom_code IS NULL)
2746 THEN
2747 RETURN 0;
2748 -- if unit volume is zero or negative -> same as above
2749 -- If setup data is missing or wrong, return zero
2750 ELSIF p_unit_volume <= 0 AND
2751 p_unit_weight <= 0
2752 THEN
2753 RETURN 0;
2754 -- if no locator specified -> sub w/o loc -> zero occupied
2755 ELSIF p_locator_id IS NULL THEN
2756 RETURN 0;
2757 END IF;
2758
2759 inv_loc_wms_utils.get_locator_capacity
2760 (x_return_status => l_return_status
2761 ,x_msg_count => l_msg_count
2762 ,x_msg_data => l_msg_data
2763 ,x_location_maximum_units => l_loc_max_units
2764 ,x_location_current_units => l_loc_cur_units
2765 ,x_location_suggested_units => l_loc_sug_units
2766 ,x_location_available_units => l_loc_avail_units
2767 ,x_location_weight_uom_code => l_weight_uom_code
2768 ,x_max_weight => l_max_weight
2769 ,x_current_weight => l_cur_weight
2770 ,x_suggested_weight => l_sug_weight
2771 ,x_available_weight => l_avail_weight
2772 ,x_volume_uom_code => l_volume_uom_code
2773 ,x_max_cubic_area => l_max_cubic_area
2774 ,x_current_cubic_area => l_cur_cubic_area
2775 ,x_suggested_cubic_area => l_sug_cubic_area
2776 ,x_available_cubic_area => l_avail_cubic_area
2777 ,p_organization_id => p_organization_id
2778 ,p_inventory_location_id => p_locator_id
2779 );
2780
2781 l_capacity := GetMaximumOccupiedVWCapacity
2782 ( p_organization_id
2783 ,p_subinventory_code
2784 ,p_locator_id
2785 ,p_inventory_item_id
2786 ,p_unit_volume
2787 ,p_unit_volume_uom_code
2788 ,p_unit_weight
2789 ,p_unit_weight_uom_code
2790 ,p_primary_uom
2791 ,p_transaction_uom
2792 ,p_base_uom
2793 ,l_loc_max_units
2794 ,l_loc_cur_units
2795 ,l_loc_sug_units
2796 ,l_loc_avail_units
2797 ,l_weight_uom_code
2798 ,l_max_weight
2799 ,l_cur_weight
2800 ,l_sug_weight
2801 ,l_avail_weight
2802 ,l_volume_uom_code
2803 ,l_max_cubic_area
2804 ,l_cur_cubic_area
2805 ,l_sug_cubic_area
2806 ,l_avail_cubic_area);
2807
2808 RETURN l_capacity;
2809
2810 END GetMaximumOccupiedVWCapacity;
2811 --
2812 -- API name : GetMinimumAvailableVWCapacity
2813 -- Type : Private
2814 -- Function : Returns the minimum of available volume and available weight
2815 -- capacity of a location measured in transaction UOM of the
2816 -- actual item.
2817 -- ( Used for capacity calculation parameters )
2818 -- Notes : refer to notes regarding the corresponding separate volume
2819 -- and weight capacity functions.
2820 FUNCTION GetMinimumAvailableVWCapacity
2821 ( p_organization_id IN NUMBER DEFAULT g_miss_num
2822 ,p_subinventory_code IN VARCHAR2 DEFAULT g_miss_char
2823 ,p_locator_id IN NUMBER DEFAULT g_miss_num
2824 ,p_inventory_item_id IN NUMBER DEFAULT g_miss_num
2825 ,p_unit_volume IN NUMBER DEFAULT g_miss_num
2826 ,p_unit_volume_uom_code IN VARCHAR2 DEFAULT g_miss_char
2827 ,p_unit_weight IN NUMBER DEFAULT g_miss_num
2828 ,p_unit_weight_uom_code IN VARCHAR2 DEFAULT g_miss_char
2829 ,p_primary_uom IN VARCHAR2 DEFAULT g_miss_char
2830 ,p_transaction_uom IN VARCHAR2 DEFAULT g_miss_char
2831 ,p_base_uom IN VARCHAR2 DEFAULT g_miss_char
2832 ,p_location_maximum_units IN NUMBER
2833 ,p_location_current_units IN NUMBER
2834 ,p_location_suggested_units IN NUMBER
2835 ,p_location_available_units IN NUMBER
2836 ,p_weight_uom_code IN VARCHAR2
2837 ,p_max_weight IN NUMBER
2838 ,p_current_weight IN NUMBER
2839 ,p_suggested_weight IN NUMBER
2840 ,p_available_weight IN NUMBER
2841 ,p_volume_uom_code IN VARCHAR2
2842 ,p_max_cubic_area IN NUMBER
2843 ,p_current_cubic_area IN NUMBER
2844 ,p_suggested_cubic_area IN NUMBER
2845 ,p_available_cubic_area IN NUMBER
2846 ) RETURN NUMBER
2847 IS
2848
2849 l_capacity NUMBER;
2850 l_weight_capacity NUMBER;
2851 l_volume_capacity NUMBER;
2852
2853 BEGIN
2854
2855
2856 -- if necessary parameters are present, convert max weight into
2857 -- txn UOM
2858 IF p_unit_weight IS NULL OR
2859 p_unit_weight_uom_code IS NULL OR
2860 p_unit_weight <= 0 OR
2861 p_available_weight IS NULL THEN
2862
2863 l_weight_capacity := 1e125;
2864 ELSE
2865 l_weight_capacity := p_available_weight;
2866 IF p_unit_weight_uom_code <> p_weight_uom_code THEN
2867 l_weight_capacity := inv_convert.inv_um_convert_new
2868 (p_inventory_item_id,
2869 NULL,
2870 l_weight_capacity,
2871 p_weight_uom_code,
2872 p_unit_weight_uom_code,
2873 NULL,
2874 NULL, 'W') / p_unit_weight;
2875 ELSE
2876 l_weight_capacity := l_weight_capacity / p_unit_weight;
2877 END IF;
2878
2879 -- l_capacity is negative if setup data is missing or wrong
2880 -- If setup data is missing or wrong, return infinite
2881 IF l_weight_capacity < 0 THEN
2882 l_weight_capacity := 1e125;
2883 END IF;
2884 END IF;
2885
2886 -- if necessary parameters are present, convert max volume into
2887 -- txn UOM
2888 IF p_unit_volume IS NULL OR
2889 p_unit_volume_uom_code IS NULL OR
2890 p_unit_volume <= 0 OR
2891 p_available_cubic_area IS NULL THEN
2892
2893 l_volume_capacity := 1e125;
2894 ELSE
2895 l_volume_capacity :=p_available_cubic_area;
2896 IF p_unit_volume_uom_code <> p_volume_uom_code THEN
2897 l_volume_capacity := inv_convert.inv_um_convert_new
2898 (p_inventory_item_id,
2899 NULL,
2900 l_volume_capacity,
2901 p_volume_uom_code,
2902 p_unit_volume_uom_code,
2903 NULL,
2904 NULL, 'V') / p_unit_volume;
2905 ELSE
2906 l_volume_capacity := l_volume_capacity / p_unit_volume;
2907 END IF;
2908
2909 -- l_capacity is negative if setup data is missing or wrong
2910 -- If setup data is missing or wrong, return infinite
2911 IF l_volume_capacity < 0 THEN
2912 l_volume_capacity := 1e125;
2913 END IF;
2914 END IF;
2915
2916 -- Find minimum value
2917 IF l_weight_capacity < l_volume_capacity THEN
2918 l_capacity := l_weight_capacity;
2919 ELSE
2920 l_capacity := l_volume_capacity;
2921 END IF;
2922
2923 -- no need to convert or round if capacity is zero or infinite
2924 IF l_capacity NOT IN (1e125,0) THEN
2925 IF p_primary_uom <> p_transaction_uom THEN
2926 l_capacity:= inv_convert.inv_um_convert
2927 (p_inventory_item_id,
2928 NULL,
2929 l_capacity,
2930 p_primary_uom,
2931 p_transaction_uom,
2932 NULL,
2933 NULL);
2934 END IF;
2935
2936 -- Round Down
2937 l_capacity := RoundDown(l_capacity,
2938 p_transaction_uom,
2939 p_inventory_item_id,
2940 p_base_uom);
2941 END IF;
2942 -- l_capacity is negative if setup data is missing or wrong
2943 -- If setup data is missing or wrong, return infinite
2944 IF l_capacity < 0 THEN
2945 RETURN 1e125;
2946 END IF;
2947
2948 return l_capacity;
2949
2950 END;
2951
2952
2953 FUNCTION GetMinimumAvailableVWCapacity
2954 ( p_organization_id IN NUMBER DEFAULT g_miss_num
2955 ,p_subinventory_code IN VARCHAR2 DEFAULT g_miss_char
2956 ,p_locator_id IN NUMBER DEFAULT g_miss_num
2957 ,p_inventory_item_id IN NUMBER DEFAULT g_miss_num
2958 ,p_unit_volume IN NUMBER DEFAULT g_miss_num
2959 ,p_unit_volume_uom_code IN VARCHAR2 DEFAULT g_miss_char
2960 ,p_unit_weight IN NUMBER DEFAULT g_miss_num
2961 ,p_unit_weight_uom_code IN VARCHAR2 DEFAULT g_miss_char
2962 ,p_primary_uom IN VARCHAR2 DEFAULT g_miss_char
2963 ,p_transaction_uom IN VARCHAR2 DEFAULT g_miss_char
2964 ,p_base_uom IN VARCHAR2 DEFAULT g_miss_char
2965 ) RETURN NUMBER
2966 IS
2967
2968 l_capacity NUMBER;
2969
2970 l_return_status VARCHAR2(1);
2971 l_msg_count NUMBER;
2972 l_msg_data VARCHAR2(1);
2973
2974 l_loc_max_units NUMBER;
2975 l_loc_cur_units NUMBER;
2976 l_loc_sug_units NUMBER;
2977 l_loc_avail_units NUMBER;
2978
2979
2980 l_volume_uom_code VARCHAR2(3);
2981 l_max_cubic_area NUMBER;
2982 l_cur_cubic_area NUMBER;
2983 l_sug_cubic_area NUMBER;
2984 l_avail_cubic_area NUMBER;
2985
2986 l_weight_uom_code VARCHAR2(3);
2987 l_max_weight NUMBER;
2988 l_cur_weight NUMBER;
2989 l_sug_weight NUMBER;
2990 l_avail_weight NUMBER;
2991
2992 BEGIN
2993 --
2994 /*
2995 l_capacity :=
2996 GetAvailableVolumeCapacity ( p_organization_id
2997 ,p_subinventory_code
2998 ,p_locator_id
2999 ,p_inventory_item_id
3000 ,p_unit_volume
3001 ,p_unit_volume_uom_code
3002 ,p_primary_uom
3003 ,p_transaction_uom
3004 ,p_base_uom );
3005 --
3006 l_tempcapa :=
3007 GetAvailableWeightCapacity ( p_organization_id
3008 ,p_subinventory_code
3009 ,p_locator_id
3010 ,p_inventory_item_id
3011 ,p_unit_weight
3012 ,p_unit_weight_uom_code
3013 ,p_primary_uom
3014 ,p_transaction_uom
3015 ,p_base_uom );
3016 --
3017 IF l_tempcapa IS NOT NULL THEN
3018 IF l_capacity IS NULL THEN
3019 l_capacity := l_tempcapa;
3020 ELSIF l_capacity > l_tempcapa THEN
3021 l_capacity := l_tempcapa;
3022 END IF;
3023 END IF;
3024
3025 RETURN l_capacity;
3026 */
3027
3028 -- missing input parameters, something is wrong -> null capacity
3029 IF p_organization_id = g_miss_num
3030 OR p_subinventory_code = g_miss_char
3031 OR p_locator_id = g_miss_num
3032 OR p_inventory_item_id = g_miss_num
3033 OR p_unit_volume = g_miss_num
3034 OR p_unit_volume_uom_code = g_miss_char
3035 OR p_unit_weight = g_miss_num
3036 OR p_unit_weight_uom_code = g_miss_char
3037 OR p_primary_uom = g_miss_char
3038 OR p_transaction_uom = g_miss_char
3039 OR p_base_uom = g_miss_char
3040 THEN
3041 RETURN NULL;
3042 -- if no sub specified, something is wrong -> null capacity
3043 ELSIF p_organization_id IS NULL
3044 OR p_subinventory_code IS NULL
3045 OR p_inventory_item_id IS NULL
3046 THEN
3047 RETURN NULL;
3048 ELSIF p_primary_uom IS NULL
3049 OR p_transaction_uom IS NULL
3050 OR p_base_uom IS NULL
3051 THEN
3052 RETURN 1e125;
3053 -- Mising item setup data regarding volume
3054 -- -> assumption: item has no volume -> used up capacity
3055 -- If setup data is missing, return infinite
3056 ELSIF (p_unit_volume IS NULL
3057 OR p_unit_volume_uom_code IS NULL) AND
3058 (p_unit_weight IS NULL
3059 OR p_unit_weight_uom_code IS NULL)
3060 THEN
3061 RETURN 1e125;
3062 -- if unit volume is zero or negative -> same as above
3063 -- If setup data is missing or wrong, return infinite
3064 ELSIF p_unit_volume <= 0 AND
3065 p_unit_weight <= 0
3066 THEN
3067 RETURN 1e125;
3068 -- if no locator specified -> sub w/o loc -> infinite capacity
3069 ELSIF p_locator_id IS NULL THEN
3070 RETURN 1e125;
3071 END IF;
3072
3073 inv_loc_wms_utils.get_locator_capacity
3074 (x_return_status => l_return_status
3075 ,x_msg_count => l_msg_count
3076 ,x_msg_data => l_msg_data
3077 ,x_location_maximum_units => l_loc_max_units
3078 ,x_location_current_units => l_loc_cur_units
3079 ,x_location_suggested_units => l_loc_sug_units
3080 ,x_location_available_units => l_loc_avail_units
3081 ,x_location_weight_uom_code => l_weight_uom_code
3082 ,x_max_weight => l_max_weight
3083 ,x_current_weight => l_cur_weight
3084 ,x_suggested_weight => l_sug_weight
3085 ,x_available_weight => l_avail_weight
3086 ,x_volume_uom_code => l_volume_uom_code
3087 ,x_max_cubic_area => l_max_cubic_area
3088 ,x_current_cubic_area => l_cur_cubic_area
3089 ,x_suggested_cubic_area => l_sug_cubic_area
3090 ,x_available_cubic_area => l_avail_cubic_area
3091 ,p_organization_id => p_organization_id
3092 ,p_inventory_location_id => p_locator_id
3093 );
3094
3095 l_capacity := GetMinimumAvailableVWCapacity
3096 ( p_organization_id
3097 ,p_subinventory_code
3098 ,p_locator_id
3099 ,p_inventory_item_id
3100 ,p_unit_volume
3101 ,p_unit_volume_uom_code
3102 ,p_unit_weight
3103 ,p_unit_weight_uom_code
3104 ,p_primary_uom
3105 ,p_transaction_uom
3106 ,p_base_uom
3107 ,l_loc_max_units
3108 ,l_loc_cur_units
3109 ,l_loc_sug_units
3110 ,l_loc_avail_units
3111 ,l_weight_uom_code
3112 ,l_max_weight
3113 ,l_cur_weight
3114 ,l_sug_weight
3115 ,l_avail_weight
3116 ,l_volume_uom_code
3117 ,l_max_cubic_area
3118 ,l_cur_cubic_area
3119 ,l_sug_cubic_area
3120 ,l_avail_cubic_area);
3121
3122 RETURN l_capacity;
3123
3124 END GetMinimumAvailableVWCapacity;
3125 --
3126 -- API name : GetMinimumRemainingVWCapacity
3127 -- Type : Private
3128 -- Function : Returns the minimum of remaining available volume and
3129 -- remaining available weight capacity of a location measured in
3130 -- transaction UOM of the actual item.
3131 -- ( Used for capacity calculation parameters )
3132 -- Notes : refer to notes regarding the corresponding separate volume
3133 -- and weight capacity functions.
3134 FUNCTION GetMinimumRemainingVWCapacity
3135 ( p_organization_id IN NUMBER DEFAULT g_miss_num
3136 ,p_subinventory_code IN VARCHAR2 DEFAULT g_miss_char
3137 ,p_locator_id IN NUMBER DEFAULT g_miss_num
3138 ,p_inventory_item_id IN NUMBER DEFAULT g_miss_num
3139 ,p_unit_volume IN NUMBER DEFAULT g_miss_num
3140 ,p_unit_volume_uom_code IN VARCHAR2 DEFAULT g_miss_char
3141 ,p_unit_weight IN NUMBER DEFAULT g_miss_num
3142 ,p_unit_weight_uom_code IN VARCHAR2 DEFAULT g_miss_char
3143 ,p_primary_uom IN VARCHAR2 DEFAULT g_miss_char
3144 ,p_transaction_uom IN VARCHAR2 DEFAULT g_miss_char
3145 ,p_base_uom IN VARCHAR2 DEFAULT g_miss_char
3146 ,p_transaction_quantity IN NUMBER DEFAULT g_miss_num
3147 ,p_location_maximum_units IN NUMBER
3148 ,p_location_current_units IN NUMBER
3149 ,p_location_suggested_units IN NUMBER
3150 ,p_location_available_units IN NUMBER
3151 ,p_weight_uom_code IN VARCHAR2
3152 ,p_max_weight IN NUMBER
3153 ,p_current_weight IN NUMBER
3154 ,p_suggested_weight IN NUMBER
3155 ,p_available_weight IN NUMBER
3156 ,p_volume_uom_code IN VARCHAR2
3157 ,p_max_cubic_area IN NUMBER
3158 ,p_current_cubic_area IN NUMBER
3159 ,p_suggested_cubic_area IN NUMBER
3160 ,p_available_cubic_area IN NUMBER
3161 ) RETURN NUMBER
3162 IS
3163 l_available_capacity NUMBER;
3164 l_capacity NUMBER;
3165
3166 BEGIN
3167 -- if transaction quantity is null or missing -> something is wrong -> abort
3168 IF p_transaction_quantity = g_miss_num
3169 OR p_transaction_quantity IS NULL
3170 THEN
3171 RETURN NULL ;
3172 END IF;
3173 --
3174 -- get available capacity
3175 l_available_capacity :=
3176 GetMinimumAvailableVWCapacity ( p_organization_id
3177 ,p_subinventory_code
3178 ,p_locator_id
3179 ,p_inventory_item_id
3180 ,p_unit_volume
3181 ,p_unit_volume_uom_code
3182 ,p_unit_weight
3183 ,p_unit_weight_uom_code
3184 ,p_primary_uom
3185 ,p_transaction_uom
3186 ,p_base_uom
3187 ,p_location_maximum_units
3188 ,p_location_current_units
3189 ,p_location_suggested_units
3190 ,p_location_available_units
3191 ,p_weight_uom_code
3192 ,p_max_weight
3193 ,p_current_weight
3194 ,p_suggested_weight
3195 ,p_available_weight
3196 ,p_volume_uom_code
3197 ,p_max_cubic_area
3198 ,p_current_cubic_area
3199 ,p_suggested_cubic_area
3200 ,p_available_cubic_area);
3201 --
3202 -- if available capacity is null -> something is wrong -> abort
3203 IF l_available_capacity IS NULL THEN
3204 RETURN l_available_capacity;
3205 --
3206 -- if available capacity is infinite -> no need to subtract anything
3207 ELSIF l_available_capacity = 1e125 THEN
3208 RETURN l_available_capacity;
3209 --
3210 -- if total capacity is zero or less -> no need to subtract anything
3211 ELSIF l_available_capacity <= 0 THEN
3212 RETURN 0;
3213 ELSE
3214 -- otherwise remaining = available - txn quantity
3215 l_capacity := l_available_capacity - p_transaction_quantity;
3216 END IF;
3217 --
3218 -- we don't return any negative capacity
3219 IF l_capacity <= 0 THEN
3220 RETURN 0;
3221 END IF;
3222 --
3223
3224 RETURN l_capacity;
3225 END;
3226
3227
3228 FUNCTION GetMinimumRemainingVWCapacity
3229 ( p_organization_id IN NUMBER DEFAULT g_miss_num
3230 ,p_subinventory_code IN VARCHAR2 DEFAULT g_miss_char
3231 ,p_locator_id IN NUMBER DEFAULT g_miss_num
3232 ,p_inventory_item_id IN NUMBER DEFAULT g_miss_num
3233 ,p_unit_volume IN NUMBER DEFAULT g_miss_num
3234 ,p_unit_volume_uom_code IN VARCHAR2 DEFAULT g_miss_char
3235 ,p_unit_weight IN NUMBER DEFAULT g_miss_num
3236 ,p_unit_weight_uom_code IN VARCHAR2 DEFAULT g_miss_char
3237 ,p_primary_uom IN VARCHAR2 DEFAULT g_miss_char
3238 ,p_transaction_uom IN VARCHAR2 DEFAULT g_miss_char
3239 ,p_base_uom IN VARCHAR2 DEFAULT g_miss_char
3240 ,p_transaction_quantity IN NUMBER DEFAULT g_miss_num
3241 ) RETURN NUMBER
3242 IS
3243 l_available_capacity NUMBER;
3244 l_capacity NUMBER;
3245 BEGIN
3246 -- if transaction quantity is null or missing -> something is wrong -> abort
3247 IF p_transaction_quantity = g_miss_num
3248 OR p_transaction_quantity IS NULL
3249 THEN
3250 RETURN NULL ;
3251 END IF;
3252 --
3253 -- get available capacity
3254 l_available_capacity :=
3255 GetMinimumAvailableVWCapacity ( p_organization_id
3256 ,p_subinventory_code
3257 ,p_locator_id
3258 ,p_inventory_item_id
3259 ,p_unit_volume
3260 ,p_unit_volume_uom_code
3261 ,p_unit_weight
3262 ,p_unit_weight_uom_code
3263 ,p_primary_uom
3264 ,p_transaction_uom
3265 ,p_base_uom );
3266 --
3267 -- if available capacity is null -> something is wrong -> abort
3268 IF l_available_capacity IS NULL THEN
3269 RETURN l_available_capacity;
3270 --
3271 -- if available capacity is infinite -> no need to subtract anything
3272 ELSIF l_available_capacity = 1e125 THEN
3273 RETURN l_available_capacity;
3274 --
3275 -- if total capacity is zero or less -> no need to subtract anything
3276 ELSIF l_available_capacity <= 0 THEN
3277 RETURN 0;
3278 ELSE
3279 -- otherwise remaining = available - txn quantity
3280 l_capacity := l_available_capacity - p_transaction_quantity;
3281 END IF;
3282 --
3283 -- we don't return any negative capacity
3284 IF l_capacity <= 0 THEN
3285 RETURN 0;
3286 END IF;
3287 --
3288 /* No need to round - we did it in Get Available
3289 *-- round it reasonably
3290 *l_capacity := RoundDown ( l_capacity
3291 * ,p_transaction_uom
3292 * ,p_inventory_item_id
3293 * ,p_base_uom );
3294 *
3295 *--
3296 *IF l_capacity <= 0 THEN
3297 * RETURN 0;
3298 *END IF;
3299 */
3300
3301 RETURN l_capacity;
3302 END GetMinimumRemainingVWCapacity;
3303 --
3304 -- API name : GetMinimumTotalUVWCapacity
3305 -- Type : Private
3306 -- Function : Returns the minimum of total unit, total volume and total
3307 -- weight capacity of a location measured in transaction UOM of
3308 -- the actual item.
3309 -- ( Used for capacity calculation parameters )
3310 -- Notes : refer to notes regarding the corresponding separate unit,
3311 -- volume and weight capacity functions.
3312 FUNCTION GetMinimumTotalUVWCapacity
3313 ( p_organization_id IN NUMBER DEFAULT g_miss_num
3314 ,p_subinventory_code IN VARCHAR2 DEFAULT g_miss_char
3315 ,p_locator_id IN NUMBER DEFAULT g_miss_num
3316 ,p_inventory_item_id IN NUMBER DEFAULT g_miss_num
3317 ,p_unit_volume IN NUMBER DEFAULT g_miss_num
3318 ,p_unit_volume_uom_code IN VARCHAR2 DEFAULT g_miss_char
3319 ,p_unit_weight IN NUMBER DEFAULT g_miss_num
3320 ,p_unit_weight_uom_code IN VARCHAR2 DEFAULT g_miss_char
3321 ,p_primary_uom IN VARCHAR2 DEFAULT g_miss_char
3322 ,p_transaction_uom IN VARCHAR2 DEFAULT g_miss_char
3323 ,p_base_uom IN VARCHAR2 DEFAULT g_miss_char
3324 ,p_location_maximum_units IN NUMBER
3325 ,p_location_current_units IN NUMBER
3326 ,p_location_suggested_units IN NUMBER
3327 ,p_location_available_units IN NUMBER
3328 ,p_weight_uom_code IN VARCHAR2
3329 ,p_max_weight IN NUMBER
3330 ,p_current_weight IN NUMBER
3331 ,p_suggested_weight IN NUMBER
3332 ,p_available_weight IN NUMBER
3333 ,p_volume_uom_code IN VARCHAR2
3334 ,p_max_cubic_area IN NUMBER
3335 ,p_current_cubic_area IN NUMBER
3336 ,p_suggested_cubic_area IN NUMBER
3337 ,p_available_cubic_area IN NUMBER
3338 ) RETURN NUMBER
3339 IS
3340
3341 l_weight_capacity NUMBER;
3342 l_volume_capacity NUMBER;
3343 l_capacity NUMBER;
3344 l_loc_max_units NUMBER;
3345
3346 BEGIN
3347
3348
3349 -- if necessary parameters are present, convert max weight into
3350 -- txn UOM
3351 IF p_max_weight IS NULL OR
3352 p_unit_weight IS NULL OR
3353 p_unit_weight_uom_code IS NULL OR
3354 p_unit_weight <= 0 THEN
3355
3356 l_weight_capacity := 1e125;
3357 ELSE
3358 IF p_unit_weight_uom_code <> p_weight_uom_code THEN
3359 l_weight_capacity := inv_convert.inv_um_convert_new
3360 (p_inventory_item_id,
3361 NULL,
3362 p_max_weight,
3363 p_weight_uom_code,
3364 p_unit_weight_uom_code,
3365 NULL,
3366 NULL, 'W') / p_unit_weight;
3367 ELSE
3368 l_weight_capacity := p_max_weight / p_unit_weight;
3369 END IF;
3370
3371 -- l_capacity is negative if setup data is missing or wrong
3372 -- If setup data is missing or wrong, return infinite
3373 IF l_weight_capacity < 0 THEN
3374 l_weight_capacity := 1e125;
3375 END IF;
3376 END IF;
3377
3378 -- if necessary parameters are present, convert max volume into
3379 -- txn UOM
3380 IF p_max_cubic_area IS NULL OR
3381 p_unit_volume IS NULL OR
3382 p_unit_volume_uom_code IS NULL OR
3383 p_unit_volume <= 0 THEN
3384
3385 l_volume_capacity := 1e125;
3386 ELSE
3387 IF p_unit_volume_uom_code <> p_volume_uom_code THEN
3388 l_volume_capacity := inv_convert.inv_um_convert_new
3389 (p_inventory_item_id,
3390 NULL,
3391 p_max_cubic_area,
3392 p_volume_uom_code,
3393 p_unit_volume_uom_code,
3394 NULL,
3395 NULL, 'V') / p_unit_volume;
3396 ELSE
3397 l_volume_capacity := p_max_cubic_area / p_unit_volume;
3398 END IF;
3399
3400 -- l_capacity is negative if setup data is missing or wrong
3401 -- If setup data is missing or wrong, return infinite
3402 IF l_volume_capacity < 0 THEN
3403 l_volume_capacity := 1e125;
3404 END IF;
3405 END IF;
3406
3407 l_loc_max_units := p_location_maximum_units;
3408 IF l_loc_max_units IS NULL THEN
3409 l_loc_max_units := 1e125;
3410 END IF;
3411
3412 -- Find minimum value
3413 IF l_weight_capacity < l_volume_capacity THEN
3414 l_capacity := l_weight_capacity;
3415 ELSE
3416 l_capacity := l_volume_capacity;
3417 END IF;
3418 IF l_capacity > l_loc_max_units THEN
3419 l_capacity := l_loc_max_units;
3420 END IF;
3421
3422 --no need to convert or round if capacity is 0 or infinite
3423 IF l_capacity NOT IN (1e125,0) THEN
3424 IF p_primary_uom <> p_transaction_uom THEN
3425 l_capacity:= inv_convert.inv_um_convert
3426 (p_inventory_item_id,
3427 NULL,
3428 l_capacity,
3429 p_primary_uom,
3430 p_transaction_uom,
3431 NULL,
3432 NULL);
3433 END IF;
3434
3435 -- Round Down
3436 l_capacity := RoundDown(l_capacity,
3437 p_transaction_uom,
3438 p_inventory_item_id,
3439 p_base_uom);
3440 END IF;
3441 -- l_capacity is negative if setup data is missing or wrong
3442 -- If setup data is missing or wrong, return infinite
3443 IF l_capacity < 0 THEN
3444 RETURN 1e125;
3445 END IF;
3446
3447 RETURN l_capacity;
3448
3449 END GetMinimumTotalUVWCapacity;
3450
3451
3452 FUNCTION GetMinimumTotalUVWCapacity
3453 ( p_organization_id IN NUMBER DEFAULT g_miss_num
3454 ,p_subinventory_code IN VARCHAR2 DEFAULT g_miss_char
3455 ,p_locator_id IN NUMBER DEFAULT g_miss_num
3456 ,p_inventory_item_id IN NUMBER DEFAULT g_miss_num
3457 ,p_unit_volume IN NUMBER DEFAULT g_miss_num
3458 ,p_unit_volume_uom_code IN VARCHAR2 DEFAULT g_miss_char
3459 ,p_unit_weight IN NUMBER DEFAULT g_miss_num
3460 ,p_unit_weight_uom_code IN VARCHAR2 DEFAULT g_miss_char
3461 ,p_primary_uom IN VARCHAR2 DEFAULT g_miss_char
3462 ,p_transaction_uom IN VARCHAR2 DEFAULT g_miss_char
3463 ,p_base_uom IN VARCHAR2 DEFAULT g_miss_char
3464 ) RETURN NUMBER
3465 IS
3466
3467 l_capacity NUMBER;
3468
3469 l_return_status VARCHAR2(1);
3470 l_msg_count NUMBER;
3471 l_msg_data VARCHAR2(1);
3472
3473 l_loc_max_units NUMBER;
3474 l_loc_cur_units NUMBER;
3475 l_loc_sug_units NUMBER;
3476 l_loc_avail_units NUMBER;
3477
3478
3479 l_volume_uom_code VARCHAR2(3);
3480 l_max_cubic_area NUMBER;
3481 l_cur_cubic_area NUMBER;
3482 l_sug_cubic_area NUMBER;
3483 l_avail_cubic_area NUMBER;
3484
3485 l_weight_uom_code VARCHAR2(3);
3486 l_max_weight NUMBER;
3487 l_cur_weight NUMBER;
3488 l_sug_weight NUMBER;
3489 l_avail_weight NUMBER;
3490
3491 BEGIN
3492 --
3493 /*
3494 l_capacity :=
3495 GetMinimumTotalVWCapacity ( p_organization_id
3496 ,p_subinventory_code
3497 ,p_locator_id
3498 ,p_inventory_item_id
3499 ,p_unit_volume
3500 ,p_unit_volume_uom_code
3501 ,p_unit_weight
3502 ,p_unit_weight_uom_code
3503 ,p_primary_uom
3504 ,p_transaction_uom
3505 ,p_base_uom );
3506 --
3507 l_tempcapa :=
3508 GetTotalUnitCapacity ( p_organization_id
3509 ,p_subinventory_code
3510 ,p_locator_id);
3511 --
3512 IF l_tempcapa IS NOT NULL THEN
3513 IF l_capacity IS NULL THEN
3514 l_capacity := l_tempcapa;
3515 ELSIF l_capacity > l_tempcapa THEN
3516 l_capacity := l_tempcapa;
3517 END IF;
3518 END IF;
3519
3520 */
3521 -- missing input parameters, something is wrong -> null capacity
3522 IF p_organization_id = g_miss_num
3523 OR p_subinventory_code = g_miss_char
3524 OR p_locator_id = g_miss_num
3525 OR p_inventory_item_id = g_miss_num
3526 OR p_unit_volume = g_miss_num
3527 OR p_unit_volume_uom_code = g_miss_char
3528 OR p_unit_weight = g_miss_num
3529 OR p_unit_weight_uom_code = g_miss_char
3530 OR p_primary_uom = g_miss_char
3531 OR p_transaction_uom = g_miss_char
3532 OR p_base_uom = g_miss_char
3533 THEN
3534 RETURN NULL;
3535 -- if no sub specified, something is wrong -> null capacity
3536 ELSIF p_organization_id IS NULL
3537 OR p_subinventory_code IS NULL
3538 OR p_inventory_item_id IS NULL
3539 THEN
3540 RETURN NULL;
3541 ELSIF p_primary_uom IS NULL
3542 OR p_transaction_uom IS NULL
3543 OR p_base_uom IS NULL
3544 THEN
3545 RETURN 1e125;
3546 -- Mising item setup data regarding volume
3547 -- -> assumption: item has no volume -> used up capacity
3548 -- If setup data is missing, return infinite
3549 ELSIF (p_unit_volume IS NULL
3550 OR p_unit_volume_uom_code IS NULL) AND
3551 (p_unit_weight IS NULL
3552 OR p_unit_weight_uom_code IS NULL)
3553 THEN
3554 RETURN 1e125;
3555 -- if unit volume is zero or negative -> same as above
3556 -- If setup data is missing or wrong, return infinite
3557 ELSIF p_unit_volume <= 0 AND
3558 p_unit_weight <= 0
3559 THEN
3560 RETURN 1e125;
3561 -- if no locator specified -> sub w/o loc -> infinite capacity
3562 ELSIF p_locator_id IS NULL THEN
3563 RETURN 1e125;
3564 END IF;
3565
3566 inv_loc_wms_utils.get_locator_capacity
3567 (x_return_status => l_return_status
3568 ,x_msg_count => l_msg_count
3569 ,x_msg_data => l_msg_data
3570 ,x_location_maximum_units => l_loc_max_units
3571 ,x_location_current_units => l_loc_cur_units
3572 ,x_location_suggested_units => l_loc_sug_units
3573 ,x_location_available_units => l_loc_avail_units
3574 ,x_location_weight_uom_code => l_weight_uom_code
3575 ,x_max_weight => l_max_weight
3576 ,x_current_weight => l_cur_weight
3577 ,x_suggested_weight => l_sug_weight
3578 ,x_available_weight => l_avail_weight
3579 ,x_volume_uom_code => l_volume_uom_code
3580 ,x_max_cubic_area => l_max_cubic_area
3581 ,x_current_cubic_area => l_cur_cubic_area
3582 ,x_suggested_cubic_area => l_sug_cubic_area
3583 ,x_available_cubic_area => l_avail_cubic_area
3584 ,p_organization_id => p_organization_id
3585 ,p_inventory_location_id => p_locator_id
3586 );
3587
3588 l_capacity := GetMinimumTotalUVWCapacity
3589 ( p_organization_id
3590 ,p_subinventory_code
3591 ,p_locator_id
3592 ,p_inventory_item_id
3593 ,p_unit_volume
3594 ,p_unit_volume_uom_code
3595 ,p_unit_weight
3596 ,p_unit_weight_uom_code
3597 ,p_primary_uom
3598 ,p_transaction_uom
3599 ,p_base_uom
3600 ,l_loc_max_units
3601 ,l_loc_cur_units
3602 ,l_loc_sug_units
3603 ,l_loc_avail_units
3604 ,l_weight_uom_code
3605 ,l_max_weight
3606 ,l_cur_weight
3607 ,l_sug_weight
3608 ,l_avail_weight
3609 ,l_volume_uom_code
3610 ,l_max_cubic_area
3611 ,l_cur_cubic_area
3612 ,l_sug_cubic_area
3613 ,l_avail_cubic_area);
3614
3615 Return l_capacity;
3616
3617 END GetMinimumTotalUVWCapacity;
3618
3619
3620 --
3621 -- API name : GetMaximumOccupiedUVWCapacity
3622 -- Type : Private
3623 -- Function : Returns the maximum of occupied unit, occupied volume and
3624 -- occupied weight capacity of a location measured in
3625 -- transaction UOM of the actual item.
3626 -- ( Used for capacity calculation parameters )
3627 -- Notes : refer to notes regarding the corresponding separate unit,
3628 -- volume and weight capacity functions.
3629 FUNCTION GetMaximumOccupiedUVWCapacity
3630 ( p_organization_id IN NUMBER DEFAULT g_miss_num
3631 ,p_subinventory_code IN VARCHAR2 DEFAULT g_miss_char
3632 ,p_locator_id IN NUMBER DEFAULT g_miss_num
3633 ,p_inventory_item_id IN NUMBER DEFAULT g_miss_num
3634 ,p_unit_volume IN NUMBER DEFAULT g_miss_num
3635 ,p_unit_volume_uom_code IN VARCHAR2 DEFAULT g_miss_char
3636 ,p_unit_weight IN NUMBER DEFAULT g_miss_num
3637 ,p_unit_weight_uom_code IN VARCHAR2 DEFAULT g_miss_char
3638 ,p_primary_uom IN VARCHAR2 DEFAULT g_miss_char
3639 ,p_transaction_uom IN VARCHAR2 DEFAULT g_miss_char
3640 ,p_base_uom IN VARCHAR2 DEFAULT g_miss_char
3641 ,p_location_maximum_units IN NUMBER
3642 ,p_location_current_units IN NUMBER
3643 ,p_location_suggested_units IN NUMBER
3644 ,p_location_available_units IN NUMBER
3645 ,p_weight_uom_code IN VARCHAR2
3646 ,p_max_weight IN NUMBER
3647 ,p_current_weight IN NUMBER
3648 ,p_suggested_weight IN NUMBER
3649 ,p_available_weight IN NUMBER
3650 ,p_volume_uom_code IN VARCHAR2
3651 ,p_max_cubic_area IN NUMBER
3652 ,p_current_cubic_area IN NUMBER
3653 ,p_suggested_cubic_area IN NUMBER
3654 ,p_available_cubic_area IN NUMBER
3655 ) RETURN NUMBER
3656 IS
3657 l_weight_capacity NUMBER;
3658 l_volume_capacity NUMBER;
3659 l_capacity NUMBER;
3660 l_loc_cur_units NUMBER;
3661 l_loc_sug_units NUMBER;
3662
3663 BEGIN
3664
3665 -- if necessary parameters are present, convert max weight into
3666 -- txn UOM
3667 IF p_unit_weight IS NULL OR
3668 p_unit_weight_uom_code IS NULL OR
3669 p_unit_weight <= 0 THEN
3670
3671 l_weight_capacity := 0;
3672 ELSE
3673 l_weight_capacity := NVL(p_current_weight, 0) + NVL(p_suggested_weight, 0);
3674 IF p_unit_weight_uom_code <> p_weight_uom_code THEN
3675 l_weight_capacity := inv_convert.inv_um_convert_new
3676 (p_inventory_item_id,
3677 NULL,
3678 l_weight_capacity,
3679 p_weight_uom_code,
3680 p_unit_weight_uom_code,
3681 NULL,
3682 NULL, 'W') / p_unit_weight;
3683 ELSE
3684 l_weight_capacity := l_weight_capacity / p_unit_weight;
3685 END IF;
3686
3687 -- l_capacity is negative if setup data is missing or wrong
3688 -- If setup data is missing or wrong, return zero
3689 IF l_weight_capacity < 0 THEN
3690 l_weight_capacity := 0;
3691 END IF;
3692 END IF;
3693
3694 -- if necessary parameters are present, convert max volume into
3695 -- txn UOM
3696 IF p_unit_volume IS NULL OR
3697 p_unit_volume_uom_code IS NULL OR
3698 p_unit_volume <= 0 THEN
3699
3700 l_volume_capacity := 0;
3701 ELSE
3702 l_volume_capacity := NVL(p_current_cubic_area,0) + NVL(p_suggested_cubic_area,0);
3703 IF p_unit_volume_uom_code <> p_volume_uom_code THEN
3704 l_volume_capacity := inv_convert.inv_um_convert_new
3705 (p_inventory_item_id,
3706 NULL,
3707 l_volume_capacity,
3708 p_volume_uom_code,
3709 p_unit_volume_uom_code,
3710 NULL,
3711 NULL, 'V') / p_unit_volume;
3712 ELSE
3713 l_volume_capacity := l_volume_capacity / p_unit_volume;
3714 END IF;
3715
3716 -- l_capacity is negative if setup data is missing or wrong
3717 -- If setup data is missing or wrong, return infinite
3718 IF l_volume_capacity < 0 THEN
3719 l_volume_capacity := 0;
3720 END IF;
3721 END IF;
3722
3723 l_loc_cur_units := p_location_current_units;
3724 l_loc_sug_units := p_location_suggested_units;
3725 IF l_loc_cur_units IS NULL THEN
3726 l_loc_cur_units := 0;
3727 END IF;
3728 IF l_loc_sug_units IS NULL THEN
3729 l_loc_sug_units := 0;
3730 END IF;
3731
3732
3733 -- Find minimum value
3734 IF l_weight_capacity > l_volume_capacity THEN
3735 l_capacity := l_weight_capacity;
3736 ELSE
3737 l_capacity := l_volume_capacity;
3738 END IF;
3739 IF l_capacity < l_loc_cur_units + l_loc_sug_units THEN
3740 l_capacity := l_loc_cur_units + l_loc_sug_units;
3741 END IF;
3742
3743 -- no need to round if capacity is 0
3744 IF l_capacity > 0 THEN
3745 IF p_primary_uom <> p_transaction_uom THEN
3746 l_capacity:= inv_convert.inv_um_convert
3747 (p_inventory_item_id,
3748 NULL,
3749 l_capacity,
3750 p_primary_uom,
3751 p_transaction_uom,
3752 NULL,
3753 NULL);
3754 END IF;
3755
3756 -- Round Down
3757 l_capacity := RoundDown(l_capacity,
3758 p_transaction_uom,
3759 p_inventory_item_id,
3760 p_base_uom);
3761 END IF;
3762 -- l_capacity is negative if setup data is missing or wrong
3763 -- If setup data is missing or wrong, return infinite
3764 IF l_capacity < 0 THEN
3765 RETURN 0;
3766 END IF;
3767
3768 RETURN l_capacity;
3769
3770 END GetMaximumOccupiedUVWCapacity;
3771
3772
3773 FUNCTION GetMaximumOccupiedUVWCapacity
3774 ( p_organization_id IN NUMBER DEFAULT g_miss_num
3775 ,p_subinventory_code IN VARCHAR2 DEFAULT g_miss_char
3776 ,p_locator_id IN NUMBER DEFAULT g_miss_num
3777 ,p_inventory_item_id IN NUMBER DEFAULT g_miss_num
3778 ,p_unit_volume IN NUMBER DEFAULT g_miss_num
3779 ,p_unit_volume_uom_code IN VARCHAR2 DEFAULT g_miss_char
3780 ,p_unit_weight IN NUMBER DEFAULT g_miss_num
3781 ,p_unit_weight_uom_code IN VARCHAR2 DEFAULT g_miss_char
3782 ,p_primary_uom IN VARCHAR2 DEFAULT g_miss_char
3783 ,p_transaction_uom IN VARCHAR2 DEFAULT g_miss_char
3784 ,p_base_uom IN VARCHAR2 DEFAULT g_miss_char
3785 ) RETURN NUMBER
3786 IS
3787
3788 l_capacity NUMBER;
3789
3790 l_return_status VARCHAR2(1);
3791 l_msg_count NUMBER;
3792 l_msg_data VARCHAR2(1);
3793
3794 l_loc_max_units NUMBER;
3795 l_loc_cur_units NUMBER;
3796 l_loc_sug_units NUMBER;
3797 l_loc_avail_units NUMBER;
3798
3799
3800 l_volume_uom_code VARCHAR2(3);
3801 l_max_cubic_area NUMBER;
3802 l_cur_cubic_area NUMBER;
3803 l_sug_cubic_area NUMBER;
3804 l_avail_cubic_area NUMBER;
3805
3806 l_weight_uom_code VARCHAR2(3);
3807 l_max_weight NUMBER;
3808 l_cur_weight NUMBER;
3809 l_sug_weight NUMBER;
3810 l_avail_weight NUMBER;
3811
3812 BEGIN
3813 --
3814 /*
3815 l_capacity :=
3816 GetMaximumOccupiedVWCapacity ( p_organization_id
3817 ,p_subinventory_code
3818 ,p_locator_id
3819 ,p_inventory_item_id
3820 ,p_unit_volume
3821 ,p_unit_volume_uom_code
3822 ,p_unit_weight
3823 ,p_unit_weight_uom_code
3824 ,p_primary_uom
3825 ,p_transaction_uom
3826 ,p_base_uom );
3827 --
3828 l_tempcapa :=
3829 GetOccupiedUnitCapacity ( p_organization_id
3830 ,p_subinventory_code
3831 ,p_locator_id );
3832 --
3833 IF l_tempcapa IS NOT NULL THEN
3834 IF l_capacity IS NULL THEN
3835 l_capacity := l_tempcapa;
3836 ELSIF l_capacity < l_tempcapa THEN
3837 l_capacity := l_tempcapa;
3838 END IF;
3839 END IF;
3840 */
3841 -- missing input parameters, something is wrong -> null capacity
3842 IF p_organization_id = g_miss_num
3843 OR p_subinventory_code = g_miss_char
3844 OR p_locator_id = g_miss_num
3845 OR p_inventory_item_id = g_miss_num
3846 OR p_unit_volume = g_miss_num
3847 OR p_unit_volume_uom_code = g_miss_char
3848 OR p_unit_weight = g_miss_num
3849 OR p_unit_weight_uom_code = g_miss_char
3850 OR p_primary_uom = g_miss_char
3851 OR p_transaction_uom = g_miss_char
3852 OR p_base_uom = g_miss_char
3853 THEN
3854 RETURN NULL;
3855 -- if no sub specified, something is wrong -> null capacity
3856 ELSIF p_organization_id IS NULL
3857 OR p_subinventory_code IS NULL
3858 OR p_inventory_item_id IS NULL
3859 THEN
3860 RETURN NULL;
3861 ELSIF p_primary_uom IS NULL
3862 OR p_transaction_uom IS NULL
3863 OR p_base_uom IS NULL
3864 THEN
3865 RETURN 0;
3866 -- Mising item setup data regarding volume
3867 -- -> assumption: item has no volume -> used up capacity
3868 -- If setup data is missing, return zero occuppied
3869 ELSIF (p_unit_volume IS NULL
3870 OR p_unit_volume_uom_code IS NULL) AND
3871 (p_unit_weight IS NULL
3872 OR p_unit_weight_uom_code IS NULL)
3873 THEN
3874 RETURN 0;
3875 -- if unit volume is zero or negative -> same as above
3876 -- If setup data is missing or wrong, return infinite
3877 ELSIF p_unit_volume <= 0 AND
3878 p_unit_weight <= 0
3879 THEN
3880 RETURN 0;
3881 -- if no locator specified -> sub w/o loc -> no occuppied capacity
3882 ELSIF p_locator_id IS NULL THEN
3883 RETURN 0;
3884 END IF;
3885
3886 inv_loc_wms_utils.get_locator_capacity
3887 (x_return_status => l_return_status
3888 ,x_msg_count => l_msg_count
3889 ,x_msg_data => l_msg_data
3890 ,x_location_maximum_units => l_loc_max_units
3891 ,x_location_current_units => l_loc_cur_units
3892 ,x_location_suggested_units => l_loc_sug_units
3893 ,x_location_available_units => l_loc_avail_units
3894 ,x_location_weight_uom_code => l_weight_uom_code
3895 ,x_max_weight => l_max_weight
3896 ,x_current_weight => l_cur_weight
3897 ,x_suggested_weight => l_sug_weight
3898 ,x_available_weight => l_avail_weight
3899 ,x_volume_uom_code => l_volume_uom_code
3900 ,x_max_cubic_area => l_max_cubic_area
3901 ,x_current_cubic_area => l_cur_cubic_area
3902 ,x_suggested_cubic_area => l_sug_cubic_area
3903 ,x_available_cubic_area => l_avail_cubic_area
3904 ,p_organization_id => p_organization_id
3905 ,p_inventory_location_id => p_locator_id
3906 );
3907
3908 l_capacity := GetMaximumOccupiedUVWCapacity
3909 ( p_organization_id
3910 ,p_subinventory_code
3911 ,p_locator_id
3912 ,p_inventory_item_id
3913 ,p_unit_volume
3914 ,p_unit_volume_uom_code
3915 ,p_unit_weight
3916 ,p_unit_weight_uom_code
3917 ,p_primary_uom
3918 ,p_transaction_uom
3919 ,p_base_uom
3920 ,l_loc_max_units
3921 ,l_loc_cur_units
3922 ,l_loc_sug_units
3923 ,l_loc_avail_units
3924 ,l_weight_uom_code
3925 ,l_max_weight
3926 ,l_cur_weight
3927 ,l_sug_weight
3928 ,l_avail_weight
3929 ,l_volume_uom_code
3930 ,l_max_cubic_area
3931 ,l_cur_cubic_area
3932 ,l_sug_cubic_area
3933 ,l_avail_cubic_area);
3934
3935 Return l_capacity;
3936
3937 END GetMaximumOccupiedUVWCapacity;
3938 --
3939 -- API name : GetMinimumAvailableUVWCapacity
3940 -- Type : Private
3941 -- Function : Returns the minimum of available unit, available volume and
3942 -- available weight capacity of a location measured in
3943 -- transaction UOM of the actual item.
3944 -- ( Used for capacity calculation parameters )
3945 -- Notes : refer to notes regarding the corresponding separate unit,
3946 -- volume and weight capacity functions.
3947 FUNCTION GetMinimumAvailableUVWCapacity
3948 ( p_organization_id IN NUMBER DEFAULT g_miss_num
3949 ,p_subinventory_code IN VARCHAR2 DEFAULT g_miss_char
3950 ,p_locator_id IN NUMBER DEFAULT g_miss_num
3951 ,p_inventory_item_id IN NUMBER DEFAULT g_miss_num
3952 ,p_unit_volume IN NUMBER DEFAULT g_miss_num
3953 ,p_unit_volume_uom_code IN VARCHAR2 DEFAULT g_miss_char
3954 ,p_unit_weight IN NUMBER DEFAULT g_miss_num
3955 ,p_unit_weight_uom_code IN VARCHAR2 DEFAULT g_miss_char
3956 ,p_primary_uom IN VARCHAR2 DEFAULT g_miss_char
3957 ,p_transaction_uom IN VARCHAR2 DEFAULT g_miss_char
3958 ,p_base_uom IN VARCHAR2 DEFAULT g_miss_char
3959 ,p_location_maximum_units IN NUMBER
3960 ,p_location_current_units IN NUMBER
3961 ,p_location_suggested_units IN NUMBER
3962 ,p_location_available_units IN NUMBER
3963 ,p_weight_uom_code IN VARCHAR2
3964 ,p_max_weight IN NUMBER
3965 ,p_current_weight IN NUMBER
3966 ,p_suggested_weight IN NUMBER
3967 ,p_available_weight IN NUMBER
3968 ,p_volume_uom_code IN VARCHAR2
3969 ,p_max_cubic_area IN NUMBER
3970 ,p_current_cubic_area IN NUMBER
3971 ,p_suggested_cubic_area IN NUMBER
3972 ,p_available_cubic_area IN NUMBER
3973 ) RETURN NUMBER
3974 IS
3975 l_weight_capacity NUMBER;
3976 l_volume_capacity NUMBER;
3977 l_capacity NUMBER;
3978 l_loc_avail_units NUMBER;
3979
3980 BEGIN
3981
3982 -- if necessary parameters are present, convert max weight into
3983 -- txn UOM
3984 IF p_unit_weight IS NULL OR
3985 p_unit_weight_uom_code IS NULL OR
3986 p_unit_weight <= 0 OR
3987 p_available_weight IS NULL THEN
3988
3989 l_weight_capacity := 1e125;
3990 ELSE
3991 l_weight_capacity := p_available_weight;
3992 IF p_unit_weight_uom_code <> p_weight_uom_code THEN
3993 l_weight_capacity := inv_convert.inv_um_convert_new -- INV_UM_CONVERT_NEW
3994 (p_inventory_item_id,
3995 NULL,
3996 l_weight_capacity,
3997 p_weight_uom_code,
3998 p_unit_weight_uom_code,
3999 NULL,
4000 NULL, 'W') / p_unit_weight;
4001 ELSE
4002 l_weight_capacity := l_weight_capacity / p_unit_weight;
4003 END IF;
4004
4005 -- l_capacity is negative if setup data is missing or wrong
4006 -- If setup data is missing or wrong, return infinite
4007 IF l_weight_capacity < 0 THEN
4008 l_weight_capacity := 1e125;
4009 END IF;
4010 END IF;
4011
4012 -- if necessary parameters are present, convert max volume into
4013 -- txn UOM
4014 IF p_unit_volume IS NULL OR
4015 p_unit_volume_uom_code IS NULL OR
4016 p_unit_volume <= 0 OR
4017 p_available_cubic_area IS NULL THEN
4018
4019 l_volume_capacity := 1e125;
4020 ELSE
4021 l_volume_capacity := p_available_cubic_area;
4022 IF p_unit_volume_uom_code <> p_volume_uom_code THEN
4023 l_volume_capacity := inv_convert.inv_um_convert_new -- INV_UM_CONVERT_NEW
4024 (p_inventory_item_id,
4025 NULL,
4026 l_volume_capacity,
4027 p_volume_uom_code,
4028 p_unit_volume_uom_code,
4029 NULL,
4030 NULL, 'V') / p_unit_volume;
4031 ELSE
4032 l_volume_capacity := l_volume_capacity / p_unit_volume;
4033 END IF;
4034
4035 -- l_capacity is negative if setup data is missing or wrong
4036 -- If setup data is missing or wrong, return infinite
4037 IF l_volume_capacity < 0 THEN
4038 l_volume_capacity := 1e125;
4039 END IF;
4040 END IF;
4041
4042 l_loc_avail_units := p_location_available_units;
4043
4044 IF l_loc_avail_units IS NULL THEN
4045 l_loc_avail_units := 1e125;
4046 END IF;
4047
4048 -- Find minimum value
4049 IF l_weight_capacity < l_volume_capacity THEN
4050 l_capacity := l_weight_capacity;
4051 ELSE
4052 l_capacity := l_volume_capacity;
4053 END IF;
4054 IF l_capacity > l_loc_avail_units THEN
4055 l_capacity := l_loc_avail_units;
4056 END IF;
4057
4058 --no need to round or convert if capacity is zero or infinite
4059 IF l_capacity NOT IN (1e125,0) THEN
4060 IF p_primary_uom <> p_transaction_uom THEN
4061 l_capacity:= inv_convert.inv_um_convert -- INV_UM_CONVERT_NEW
4062 (p_inventory_item_id,
4063 NULL,
4064 l_capacity,
4065 p_primary_uom,
4066 p_transaction_uom,
4067 NULL,
4068 NULL);
4069 END IF;
4070
4071 -- Round Down
4072 l_capacity := RoundDown(l_capacity,
4073 p_transaction_uom,
4074 p_inventory_item_id,
4075 p_base_uom);
4076 END IF;
4077 -- l_capacity is negative if setup data is missing or wrong
4078 -- If setup data is missing or wrong, return infinite
4079 IF l_capacity < 0 THEN
4080 RETURN 1e125;
4081 END IF;
4082
4083 RETURN l_capacity;
4084
4085 END GetMinimumAvailableUVWCapacity;
4086
4087
4088 FUNCTION GetMinimumAvailableUVWCapacity
4089 ( p_organization_id IN NUMBER DEFAULT g_miss_num
4090 ,p_subinventory_code IN VARCHAR2 DEFAULT g_miss_char
4091 ,p_locator_id IN NUMBER DEFAULT g_miss_num
4092 ,p_inventory_item_id IN NUMBER DEFAULT g_miss_num
4093 ,p_unit_volume IN NUMBER DEFAULT g_miss_num
4094 ,p_unit_volume_uom_code IN VARCHAR2 DEFAULT g_miss_char
4095 ,p_unit_weight IN NUMBER DEFAULT g_miss_num
4096 ,p_unit_weight_uom_code IN VARCHAR2 DEFAULT g_miss_char
4097 ,p_primary_uom IN VARCHAR2 DEFAULT g_miss_char
4098 ,p_transaction_uom IN VARCHAR2 DEFAULT g_miss_char
4099 ,p_base_uom IN VARCHAR2 DEFAULT g_miss_char
4100 ) RETURN NUMBER
4101 IS
4102
4103 l_capacity NUMBER;
4104
4105 l_return_status VARCHAR2(1);
4106 l_msg_count NUMBER;
4107 l_msg_data VARCHAR2(1);
4108
4109 l_loc_max_units NUMBER;
4110 l_loc_cur_units NUMBER;
4111 l_loc_sug_units NUMBER;
4112 l_loc_avail_units NUMBER;
4113
4114
4115 l_volume_uom_code VARCHAR2(3);
4116 l_max_cubic_area NUMBER;
4117 l_cur_cubic_area NUMBER;
4118 l_sug_cubic_area NUMBER;
4119 l_avail_cubic_area NUMBER;
4120
4121 l_weight_uom_code VARCHAR2(3);
4122 l_max_weight NUMBER;
4123 l_cur_weight NUMBER;
4124 l_sug_weight NUMBER;
4125 l_avail_weight NUMBER;
4126
4127 BEGIN
4128 --
4129 /*
4130 l_capacity :=
4131 GetAvailableUnitCapacity ( p_organization_id
4132 ,p_subinventory_code
4133 ,p_locator_id);
4134 --
4135 l_tempcapa :=
4136 GetMinimumAvailableVWCapacity ( p_organization_id
4137 ,p_subinventory_code
4138 ,p_locator_id
4139 ,p_inventory_item_id
4140 ,p_unit_volume
4141 ,p_unit_volume_uom_code
4142 ,p_unit_weight
4143 ,p_unit_weight_uom_code
4144 ,p_primary_uom
4145 ,p_transaction_uom
4146 ,p_base_uom );
4147 --
4148 IF l_tempcapa IS NOT NULL THEN
4149 IF l_capacity IS NULL THEN
4150 l_capacity := l_tempcapa;
4151 ELSIF l_capacity > l_tempcapa THEN
4152 l_capacity := l_tempcapa;
4153 END IF;
4154 END IF;
4155 --
4156 */
4157 -- missing input parameters, something is wrong -> null capacity
4158 IF p_organization_id = g_miss_num
4159 OR p_subinventory_code = g_miss_char
4160 OR p_locator_id = g_miss_num
4161 OR p_inventory_item_id = g_miss_num
4162 OR p_unit_volume = g_miss_num
4163 OR p_unit_volume_uom_code = g_miss_char
4164 OR p_unit_weight = g_miss_num
4165 OR p_unit_weight_uom_code = g_miss_char
4166 OR p_primary_uom = g_miss_char
4167 OR p_transaction_uom = g_miss_char
4168 OR p_base_uom = g_miss_char
4169 THEN
4170 RETURN NULL;
4171 -- if no sub specified, something is wrong -> null capacity
4172 ELSIF p_organization_id IS NULL
4173 OR p_subinventory_code IS NULL
4174 OR p_inventory_item_id IS NULL
4175 THEN
4176 RETURN NULL;
4177 ELSIF p_primary_uom IS NULL
4178 OR p_transaction_uom IS NULL
4179 OR p_base_uom IS NULL
4180 THEN
4181 RETURN 1e125;
4182 -- Mising item setup data regarding volume
4183 -- -> assumption: item has no volume -> used up capacity
4184 -- If setup data is missing, return infinite
4185 ELSIF (p_unit_volume IS NULL
4186 OR p_unit_volume_uom_code IS NULL) AND
4187 (p_unit_weight IS NULL
4188 OR p_unit_weight_uom_code IS NULL)
4189 THEN
4190 RETURN 1e125;
4191 -- if unit volume is zero or negative -> same as above
4192 -- If setup data is missing or wrong, return infinite
4193 ELSIF p_unit_volume <= 0 AND
4194 p_unit_weight <= 0
4195 THEN
4196 RETURN 1e125;
4197 -- if no locator specified -> sub w/o loc -> infinite capacity
4198 ELSIF p_locator_id IS NULL THEN
4199 RETURN 1e125;
4200 END IF;
4201
4202 inv_loc_wms_utils.get_locator_capacity
4203 (x_return_status => l_return_status
4204 ,x_msg_count => l_msg_count
4205 ,x_msg_data => l_msg_data
4206 ,x_location_maximum_units => l_loc_max_units
4207 ,x_location_current_units => l_loc_cur_units
4208 ,x_location_suggested_units => l_loc_sug_units
4209 ,x_location_available_units => l_loc_avail_units
4210 ,x_location_weight_uom_code => l_weight_uom_code
4211 ,x_max_weight => l_max_weight
4212 ,x_current_weight => l_cur_weight
4213 ,x_suggested_weight => l_sug_weight
4214 ,x_available_weight => l_avail_weight
4215 ,x_volume_uom_code => l_volume_uom_code
4216 ,x_max_cubic_area => l_max_cubic_area
4217 ,x_current_cubic_area => l_cur_cubic_area
4218 ,x_suggested_cubic_area => l_sug_cubic_area
4219 ,x_available_cubic_area => l_avail_cubic_area
4220 ,p_organization_id => p_organization_id
4221 ,p_inventory_location_id => p_locator_id
4222 );
4223
4224 l_capacity := GetMinimumAvailableUVWCapacity
4225 ( p_organization_id
4226 ,p_subinventory_code
4227 ,p_locator_id
4228 ,p_inventory_item_id
4229 ,p_unit_volume
4230 ,p_unit_volume_uom_code
4231 ,p_unit_weight
4232 ,p_unit_weight_uom_code
4233 ,p_primary_uom
4234 ,p_transaction_uom
4235 ,p_base_uom
4236 ,l_loc_max_units
4237 ,l_loc_cur_units
4238 ,l_loc_sug_units
4239 ,l_loc_avail_units
4240 ,l_weight_uom_code
4241 ,l_max_weight
4242 ,l_cur_weight
4243 ,l_sug_weight
4244 ,l_avail_weight
4245 ,l_volume_uom_code
4246 ,l_max_cubic_area
4247 ,l_cur_cubic_area
4248 ,l_sug_cubic_area
4249 ,l_avail_cubic_area);
4250
4251 Return l_capacity;
4252
4253 END GetMinimumAvailableUVWCapacity;
4254 --
4255 -- API name : GetMinimumRemainingUVWCapacity
4256 -- Type : Private
4257 -- Function : Returns the minimum of remaining available unit, remaining
4258 -- available volume and remaining available weight capacity of
4259 -- a location measured in transaction UOM of the actual item.
4260 -- ( Used for capacity calculation parameters )
4261 -- Notes : refer to notes regarding the corresponding separate unit,
4262 -- volume and weight capacity functions.
4263 FUNCTION GetMinimumRemainingUVWCapacity
4264 ( p_organization_id IN NUMBER DEFAULT g_miss_num
4265 ,p_subinventory_code IN VARCHAR2 DEFAULT g_miss_char
4266 ,p_locator_id IN NUMBER DEFAULT g_miss_num
4267 ,p_inventory_item_id IN NUMBER DEFAULT g_miss_num
4268 ,p_unit_volume IN NUMBER DEFAULT g_miss_num
4269 ,p_unit_volume_uom_code IN VARCHAR2 DEFAULT g_miss_char
4270 ,p_unit_weight IN NUMBER DEFAULT g_miss_num
4271 ,p_unit_weight_uom_code IN VARCHAR2 DEFAULT g_miss_char
4272 ,p_primary_uom IN VARCHAR2 DEFAULT g_miss_char
4273 ,p_transaction_uom IN VARCHAR2 DEFAULT g_miss_char
4274 ,p_base_uom IN VARCHAR2 DEFAULT g_miss_char
4275 ,p_transaction_quantity IN NUMBER DEFAULT g_miss_num
4276 ,p_location_maximum_units IN NUMBER
4277 ,p_location_current_units IN NUMBER
4278 ,p_location_suggested_units IN NUMBER
4279 ,p_location_available_units IN NUMBER
4280 ,p_weight_uom_code IN VARCHAR2
4281 ,p_max_weight IN NUMBER
4282 ,p_current_weight IN NUMBER
4283 ,p_suggested_weight IN NUMBER
4284 ,p_available_weight IN NUMBER
4285 ,p_volume_uom_code IN VARCHAR2
4286 ,p_max_cubic_area IN NUMBER
4287 ,p_current_cubic_area IN NUMBER
4288 ,p_suggested_cubic_area IN NUMBER
4289 ,p_available_cubic_area IN NUMBER
4290 ) RETURN NUMBER
4291 IS
4292 l_available_capacity NUMBER;
4293 l_capacity NUMBER;
4294
4295 BEGIN
4296
4297 --
4298 -- if transaction quantity is null or missing-> something is wrong -> abort
4299 IF p_transaction_quantity = g_miss_num
4300 OR p_transaction_quantity IS NULL
4301 THEN
4302 RETURN NULL;
4303 END IF;
4304 --
4305 -- get available capacity
4306 l_available_capacity :=
4307 GetMinimumAvailableUVWCapacity ( p_organization_id
4308 ,p_subinventory_code
4309 ,p_locator_id
4310 ,p_inventory_item_id
4311 ,p_unit_volume
4312 ,p_unit_volume_uom_code
4313 ,p_unit_weight
4314 ,p_unit_weight_uom_code
4315 ,p_primary_uom
4316 ,p_transaction_uom
4317 ,p_base_uom
4318 ,p_location_maximum_units
4319 ,p_location_current_units
4320 ,p_location_suggested_units
4321 ,p_location_available_units
4322 ,p_weight_uom_code
4323 ,p_max_weight
4324 ,p_current_weight
4325 ,p_suggested_weight
4326 ,p_available_weight
4327 ,p_volume_uom_code
4328 ,p_max_cubic_area
4329 ,p_current_cubic_area
4330 ,p_suggested_cubic_area
4331 ,p_available_cubic_area);
4332 --
4333 -- if available capacity is null -> something is wrong -> abort
4334 IF l_available_capacity IS NULL THEN
4335 RETURN l_available_capacity;
4336 --
4337 -- if available capacity is infinite -> no need to subtract anything
4338 ELSIF l_available_capacity = 1e125 THEN
4339 RETURN l_available_capacity;
4340 --
4341 -- if total capacity is zero or less -> no need to subtract anything
4342 ELSIF l_available_capacity <= 0 THEN
4343 RETURN 0;
4344 ELSE
4345 -- otherwise remaining = available - txn quantity
4346 l_capacity := l_available_capacity - p_transaction_quantity;
4347 END IF;
4348 --
4349 -- we don't return any negative capacity
4350 IF l_capacity <= 0 THEN
4351 RETURN 0;
4352 END IF;
4353 --
4354 RETURN l_capacity;
4355 END GetMinimumRemainingUVWCapacity;
4356
4357
4358 FUNCTION GetMinimumRemainingUVWCapacity
4359 ( p_organization_id IN NUMBER DEFAULT g_miss_num
4360 ,p_subinventory_code IN VARCHAR2 DEFAULT g_miss_char
4361 ,p_locator_id IN NUMBER DEFAULT g_miss_num
4362 ,p_inventory_item_id IN NUMBER DEFAULT g_miss_num
4363 ,p_unit_volume IN NUMBER DEFAULT g_miss_num
4364 ,p_unit_volume_uom_code IN VARCHAR2 DEFAULT g_miss_char
4365 ,p_unit_weight IN NUMBER DEFAULT g_miss_num
4366 ,p_unit_weight_uom_code IN VARCHAR2 DEFAULT g_miss_char
4367 ,p_primary_uom IN VARCHAR2 DEFAULT g_miss_char
4368 ,p_transaction_uom IN VARCHAR2 DEFAULT g_miss_char
4369 ,p_base_uom IN VARCHAR2 DEFAULT g_miss_char
4370 ,p_transaction_quantity IN NUMBER DEFAULT g_miss_num
4371 ) RETURN NUMBER
4372 IS
4373 l_available_capacity NUMBER;
4374 l_capacity NUMBER;
4375 BEGIN
4376 --
4377 -- if transaction quantity is null or missing-> something is wrong -> abort
4378 IF p_transaction_quantity = g_miss_num
4379 OR p_transaction_quantity IS NULL
4380 THEN
4381 RETURN NULL;
4382 END IF;
4383 --
4384 -- get available capacity
4385 l_available_capacity :=
4386 GetMinimumAvailableUVWCapacity ( p_organization_id
4387 ,p_subinventory_code
4388 ,p_locator_id
4389 ,p_inventory_item_id
4390 ,p_unit_volume
4391 ,p_unit_volume_uom_code
4392 ,p_unit_weight
4393 ,p_unit_weight_uom_code
4394 ,p_primary_uom
4395 ,p_transaction_uom
4396 ,p_base_uom );
4397 --
4398 -- if available capacity is null -> something is wrong -> abort
4399 IF l_available_capacity IS NULL THEN
4400 RETURN l_available_capacity;
4401 --
4402 -- if available capacity is infinite -> no need to subtract anything
4403 ELSIF l_available_capacity = 1e125 THEN
4404 RETURN l_available_capacity;
4405 --
4406 -- if total capacity is zero or less -> no need to subtract anything
4407 ELSIF l_available_capacity <= 0 THEN
4408 RETURN 0;
4409 ELSE
4410 -- otherwise remaining = available - txn quantity
4411 l_capacity := l_available_capacity - p_transaction_quantity;
4412 END IF;
4413 --
4414 -- we don't return any negative capacity
4415 IF l_capacity <= 0 THEN
4416 RETURN 0;
4417 END IF;
4418 --
4419 /* No need to round, as we did it in GetAvailableUVW...
4420 *-- round it reasonably
4421 *l_capacity := RoundDown ( l_capacity
4422 * ,p_transaction_uom
4423 * ,p_inventory_item_id
4424 * ,p_base_uom );
4425 *--
4426 *(IF l_capacity <= 0 THEN
4427 * RETURN 0;
4428 *END IF;
4429 */
4430 --
4431 RETURN l_capacity;
4432 END GetMinimumRemainingUVWCapacity;
4433
4434
4435 --bug 2200812
4436 -- Change from one cursor to 3 - one that assumes only item is passed,
4437 -- one that assumes that only item and sub is passed, and one that
4438 -- assumes that item, sub, and locator are passed
4439 --bug 2259821
4440 -- Added 2 arguments, loc_Inventory_item_id and loc_current_units. These
4441 -- parameters allow the procedure to operate more effeciently. In some
4442 -- situations, there is no need to call the costly SQL statement to get
4443 -- the value.
4444 --
4445 -- Made p_transaction_uom and p_primary_uom optional - the procedure
4446 -- no longer errors out if these values aren't passed. This change
4447 -- is necessary to support a new parameter which returns the
4448 -- Item OnHand in the primary UOM of the item.
4449 FUNCTION getitemonhand(
4450 p_organization_id IN NUMBER DEFAULT g_miss_num
4451 , p_inventory_item_id IN NUMBER DEFAULT g_miss_num
4452 , p_subinventory_code IN VARCHAR2 DEFAULT g_miss_char
4453 , p_locator_id IN NUMBER DEFAULT g_miss_num
4454 , p_primary_uom IN VARCHAR2 DEFAULT g_miss_char
4455 , p_transaction_uom IN VARCHAR2 DEFAULT g_miss_char
4456 , p_locator_inventory_item_id IN NUMBER DEFAULT NULL
4457 , p_location_current_units IN NUMBER DEFAULT NULL
4458 )
4459 RETURN NUMBER IS
4460 l_primary_quantity NUMBER;
4461 l_transaction_quantity NUMBER;
4462
4463 -- Bug #3413372
4464 -- Added locator_id to the cursor l_item_onhand
4465 -- Only the cursor l_item_onhand is used and rest of the two cursors
4466 -- ignored
4467
4468
4469 CURSOR l_item_onhand IS
4470 SELECT NVL(SUM(onhand.oh_quantity), 0)
4471 FROM (-- on-hand
4472 SELECT moq.organization_id organization_id
4473 , moq.inventory_item_id inventory_item_id
4474 , moq.subinventory_code subinventory_code
4475 , moq.locator_id locator_id
4476 , moq.primary_transaction_quantity oh_quantity
4477 FROM mtl_onhand_quantities_detail moq
4478 -- to be more conservative ( or simply realistic ) we don't add
4479 -- negative on-hand to the capacity
4480 WHERE moq.transaction_quantity > 0
4481 UNION ALL
4482 -- pending issues/receipts and issues in transfers
4483 SELECT DECODE(mmtt.transaction_action_id, 3, mmtt.transfer_organization, mmtt.organization_id)
4484 , mmtt.inventory_item_id
4485 , mmtt.subinventory_code
4486 , mmtt.locator_id
4487 , DECODE(
4488 mmtt.transaction_action_id
4489 , 2, -ABS(mmtt.primary_quantity)
4490 , 3, -ABS(mmtt.primary_quantity)
4491 , mmtt.primary_quantity
4492 )
4493 FROM mtl_material_transactions_temp mmtt
4494 WHERE mmtt.inventory_item_id > 0 -- Index !!!
4495 AND mmtt.posting_flag = 'Y' -- pending txn
4496 AND NVL(transaction_status, -1) <> 2 -- not suggestions
4497 UNION ALL
4498 -- receiving side in transfers
4499 SELECT DECODE(mmtt.transaction_action_id, 3, mmtt.transfer_organization, mmtt.organization_id)
4500 , mmtt.inventory_item_id
4501 , mmtt.transfer_subinventory
4502 , mmtt.transfer_to_location
4503 , mmtt.primary_quantity
4504 FROM mtl_material_transactions_temp mmtt
4505 WHERE mmtt.inventory_item_id > 0 -- Index !!!
4506 AND mmtt.posting_flag = 'Y' -- pending txn
4507 AND NVL(mmtt.transaction_status, -1) <> 2 -- not suggestions
4508 AND mmtt.transaction_action_id IN (2, 3) -- transfers
4509 UNION ALL
4510 -- note: we don't add pick suggestions to capacity
4511 --
4512 -- put away suggestions (including transfers)
4513 SELECT DECODE(mmtt.transaction_action_id, 3, mmtt.transfer_organization, mmtt.organization_id)
4514 , mmtt.inventory_item_id
4515 , DECODE(
4516 mmtt.transaction_action_id
4517 , 2, mmtt.transfer_subinventory
4518 , 3, mmtt.transfer_subinventory
4519 , mmtt.subinventory_code
4520 )
4521 , DECODE(mmtt.transaction_action_id, 2, mmtt.transfer_to_location, 3, mmtt.transfer_to_location, mmtt.locator_id)
4522 , ABS(mmtt.primary_quantity)
4523 FROM mtl_material_transactions_temp mmtt
4524 WHERE mmtt.posting_flag = 'Y'
4525 AND mmtt.transaction_status = 2 -- suggestions
4526 AND mmtt.transaction_action_id IN -- put away
4527 (2, 3, 12, 27, 31, 33) -- only receipts and transfer
4528 UNION ALL
4529 -- put away suggestions still sitting in internal temp table
4530 SELECT DECODE(mtt.transaction_action_id, 3, mtrl.to_organization_id, mtrl.organization_id)
4531 , mtrl.inventory_item_id
4532 , wtt.to_subinventory_code subinventory_code
4533 , wtt.to_locator_id locator_id
4534 , wtt.primary_quantity
4535 FROM mtl_txn_request_lines mtrl, wms_transactions_temp wtt, mtl_transaction_types mtt
4536 WHERE wtt.type_code = 1 -- put away
4537 AND wtt.line_type_code = 2 -- output
4538 AND mtrl.line_id = wtt.transaction_temp_id
4539 AND mtrl.transaction_type_id = mtt.transaction_type_id) onhand
4540 WHERE onhand.organization_id = p_organization_id
4541 AND onhand.inventory_item_id = p_inventory_item_id
4542 GROUP BY onhand.inventory_item_id;
4543
4544 CURSOR l_sub_onhand IS
4545 SELECT NVL(SUM(onhand.oh_quantity), 0)
4546 FROM (-- on-hand
4547 SELECT moq.organization_id organization_id
4548 , moq.inventory_item_id inventory_item_id
4549 , moq.subinventory_code subinventory_code
4550 , moq.locator_id locator_id
4551 , moq.primary_transaction_quantity oh_quantity
4552 FROM mtl_onhand_quantities_detail moq
4553 -- to be more conservative ( or simply realistic ) we don't add
4554 -- negative on-hand to the capacity
4555 WHERE moq.transaction_quantity > 0
4556 UNION ALL
4557 -- pending issues/receipts and issues in transfers
4558 SELECT DECODE(mmtt.transaction_action_id, 3, mmtt.transfer_organization, mmtt.organization_id)
4559 , mmtt.inventory_item_id
4560 , mmtt.subinventory_code
4561 , mmtt.locator_id
4562 , DECODE(
4563 mmtt.transaction_action_id
4564 , 2, -ABS(mmtt.primary_quantity)
4565 , 3, -ABS(mmtt.primary_quantity)
4566 , mmtt.primary_quantity
4567 )
4568 FROM mtl_material_transactions_temp mmtt
4569 WHERE mmtt.inventory_item_id > 0 -- Index !!!
4570 AND mmtt.posting_flag = 'Y' -- pending txn
4571 AND NVL(transaction_status, -1) <> 2 -- not suggestions
4572 UNION ALL
4573 -- receiving side in transfers
4574 SELECT DECODE(mmtt.transaction_action_id, 3, mmtt.transfer_organization, mmtt.organization_id)
4575 , mmtt.inventory_item_id
4576 , mmtt.transfer_subinventory
4577 , mmtt.transfer_to_location
4578 , mmtt.primary_quantity
4579 FROM mtl_material_transactions_temp mmtt
4580 WHERE mmtt.inventory_item_id > 0 -- Index !!!
4581 AND mmtt.posting_flag = 'Y' -- pending txn
4582 AND NVL(mmtt.transaction_status, -1) <> 2 -- not suggestions
4583 AND mmtt.transaction_action_id IN (2, 3) -- transfers
4584 UNION ALL
4585 -- note: we don't add pick suggestions to capacity
4586 --
4587 -- put away suggestions (including transfers)
4588 SELECT DECODE(mmtt.transaction_action_id, 3, mmtt.transfer_organization, mmtt.organization_id)
4589 , mmtt.inventory_item_id
4590 , DECODE(
4591 mmtt.transaction_action_id
4592 , 2, mmtt.transfer_subinventory
4593 , 3, mmtt.transfer_subinventory
4594 , mmtt.subinventory_code
4595 )
4596 , DECODE(mmtt.transaction_action_id, 2, mmtt.transfer_to_location, 3, mmtt.transfer_to_location, mmtt.locator_id)
4597 , ABS(mmtt.primary_quantity)
4598 FROM mtl_material_transactions_temp mmtt
4599 WHERE mmtt.posting_flag = 'Y'
4600 AND mmtt.transaction_status = 2 -- suggestions
4601 AND mmtt.transaction_action_id IN -- put away
4602 (2, 3, 12, 27, 31, 33) -- only receipts and transfer
4603 UNION ALL
4604 -- put away suggestions still sitting in internal temp table
4605 SELECT DECODE(mtt.transaction_action_id, 3, mtrl.to_organization_id, mtrl.organization_id)
4606 , mtrl.inventory_item_id
4607 , wtt.to_subinventory_code subinventory_code
4608 , wtt.to_locator_id locator_id
4609 , wtt.primary_quantity
4610 FROM mtl_txn_request_lines mtrl, wms_transactions_temp wtt, mtl_transaction_types mtt
4611 WHERE wtt.type_code = 1 -- put away
4612 AND wtt.line_type_code = 2 -- output
4613 AND mtrl.line_id = wtt.transaction_temp_id
4614 AND mtrl.transaction_type_id = mtt.transaction_type_id) onhand
4615 WHERE onhand.organization_id = p_organization_id
4616 AND onhand.inventory_item_id = p_inventory_item_id
4617 AND onhand.subinventory_code = p_subinventory_code
4618 GROUP BY onhand.inventory_item_id;
4619
4620 CURSOR l_loc_onhand IS
4621 SELECT onhand.locator_id, NVL(SUM(onhand.oh_quantity), 0)
4622 FROM (-- on-hand
4623 SELECT moq.organization_id organization_id
4624 , moq.inventory_item_id inventory_item_id
4625 , moq.subinventory_code subinventory_code
4626 , moq.locator_id locator_id
4627 , moq.primary_transaction_quantity oh_quantity
4628 FROM mtl_onhand_quantities_detail moq
4629 -- to be more conservative ( or simply realistic ) we don't add
4630 -- negative on-hand to the capacity
4631 WHERE moq.transaction_quantity > 0
4632 UNION ALL
4633 -- pending issues/receipts and issues in transfers
4634 SELECT DECODE(mmtt.transaction_action_id, 3, mmtt.transfer_organization, mmtt.organization_id)
4635 , mmtt.inventory_item_id
4636 , mmtt.subinventory_code
4637 , mmtt.locator_id
4638 , DECODE(
4639 mmtt.transaction_action_id
4640 , 2, -ABS(mmtt.primary_quantity)
4641 , 3, -ABS(mmtt.primary_quantity)
4642 , mmtt.primary_quantity
4643 )
4644 FROM mtl_material_transactions_temp mmtt
4645 WHERE mmtt.inventory_item_id > 0 -- Index !!!
4646 AND nvl(mmtt.locator_id, 0) > 0 -- Added for bug # 4493640
4647 AND mmtt.posting_flag = 'Y' -- pending txn
4648 AND NVL(transaction_status, -1) <> 2 -- not suggestions
4649 UNION ALL
4650 -- receiving side in transfers
4651 SELECT DECODE(mmtt.transaction_action_id, 3, mmtt.transfer_organization, mmtt.organization_id)
4652 , mmtt.inventory_item_id
4653 , mmtt.transfer_subinventory
4654 , mmtt.transfer_to_location
4655 , mmtt.primary_quantity
4656 FROM mtl_material_transactions_temp mmtt
4657 WHERE mmtt.inventory_item_id > 0 -- Index !!!
4658 AND nvl(mmtt.locator_id, 0) > 0 -- Added for bug # 4493640
4659 AND mmtt.posting_flag = 'Y' -- pending txn
4660 AND NVL(mmtt.transaction_status, -1) <> 2 -- not suggestions
4661 AND mmtt.transaction_action_id IN (2, 3) -- transfers
4662 UNION ALL
4663 -- note: we don't add pick suggestions to capacity
4664 --
4665 -- put away suggestions (including transfers)
4666 SELECT DECODE(mmtt.transaction_action_id, 3, mmtt.transfer_organization, mmtt.organization_id)
4667 , mmtt.inventory_item_id
4668 , DECODE(
4669 mmtt.transaction_action_id
4670 , 2, mmtt.transfer_subinventory
4671 , 3, mmtt.transfer_subinventory
4672 , mmtt.subinventory_code
4673 )
4674 , DECODE(mmtt.transaction_action_id, 2, mmtt.transfer_to_location, 3, mmtt.transfer_to_location, mmtt.locator_id)
4675 , ABS(mmtt.primary_quantity)
4676 FROM mtl_material_transactions_temp mmtt
4677 WHERE mmtt.posting_flag = 'Y'
4678 AND nvl(mmtt.locator_id, 0) > 0 -- Added for bug # 4493640
4679 AND mmtt.transaction_status = 2 -- suggestions
4680 AND mmtt.transaction_action_id IN -- put away
4681 (2, 3, 12, 27, 31, 33) -- only receipts and transfer
4682 UNION ALL
4683 -- put away suggestions still sitting in internal temp table
4684 SELECT DECODE(mtt.transaction_action_id, 3, mtrl.to_organization_id, mtrl.organization_id)
4685 , mtrl.inventory_item_id
4686 , wtt.to_subinventory_code subinventory_code
4687 , wtt.to_locator_id locator_id
4688 , wtt.primary_quantity
4689 FROM mtl_txn_request_lines mtrl, wms_transactions_temp wtt, mtl_transaction_types mtt
4690 WHERE wtt.type_code = 1 -- put away
4691 AND wtt.line_type_code = 2 -- output
4692 AND mtrl.line_id = wtt.transaction_temp_id
4693 AND mtrl.transaction_type_id = mtt.transaction_type_id) onhand
4694 WHERE onhand.organization_id = p_organization_id
4695 AND onhand.inventory_item_id = p_inventory_item_id
4696 --AND onhand.subinventory_code = p_subinventory_code
4697 --AND onhand.locator_id = p_locator_id
4698 GROUP BY onhand.locator_id, onhand.inventory_item_id;
4699
4700 BEGIN
4701 -- log_mesg('GetItemOnhand','Start', '()' );
4702 -- log_mesg('GetItemOnhand','p_organization_id :', p_organization_id );
4703 -- log_mesg('GetItemOnhand','p_inventory_item_id :', p_inventory_item_id );
4704 -- log_mesg('GetItemOnhand','p_locator_id :', p_locator_id );
4705 -- log_mesg('GetItemOnhand','p_subinventory_code :', p_subinventory_code );
4706 -- log_mesg('GetItemOnhand','p_primary_uom :', p_primary_uom );
4707 -- log_mesg('GetItemOnhand','p_transaction_uom :', p_transaction_uom );
4708 IF (p_organization_id = g_miss_num OR --check for missing org, item, or sub
4709 p_inventory_item_id = g_miss_num OR
4710 p_organization_id IS NULL OR
4711 p_inventory_item_id IS NULL ) THEN
4712
4713 RETURN NULL;
4714 END IF;
4715
4716 -- Bug 3413372 / 3573819 Performance fix
4717
4718 /* Currently the cursor - l_loc_onhand is executed once per every pre-suggested row by the rules engine.
4719 The new code will execute the cursor once and bulk fetch the values ( Locator_id, on_hand_qty) into two
4720 internal tables - g_bulkCollect_Locator and g_bulkCollect_quantity. Since the 'Bulk fetch' organizing
4721 the data sequentially, The data is transferred into a third table g_locator_item_quantity which is
4722 binary indexed.
4723 First time, after a rule being called, the value of the g_GetItemOnhq_IsRuleCached will be 'N' and the
4724 cursor 'l_item_onhand' will be executed to populate the table - g_locator_item_quantity.
4725 For subsequent calls, qty for a given locator_id will be fetched from the internal table instead of
4726 calling the expensive query. The internal tables and the global variables will be initialized after
4727 the rule being executed.
4728 */
4729
4730 IF p_locator_id IS NOT NULL THEN
4731 --if the current item is the only item in the locator, the primary
4732 -- quantity is equal to the location current units.
4733 /* Since the l_loc_onhand is executed only once, chages made for the bug 2259821 was commented out
4734
4735 IF p_locator_inventory_item_id IS NOT NULL
4736 AND p_locator_inventory_item_id = p_inventory_item_id THEN
4737 l_primary_quantity := p_location_current_units;
4738 --if there is only one item in the locator, and that item is not
4739 -- equal to the current item, we know that no quantity of the current
4740 -- item resides in the locator. Return 0.
4741 -- log_mesg('GetItemonhand', 'locator_id IS NOT NULL:', '' );
4742 ELSIF p_locator_inventory_item_id IS NOT NULL THEN
4743 -- log_mesg('GetItemonhand', 'p_locator_inventory_item_id IS NOT NULL:', '' );
4744 RETURN 0;
4745 --locator has multiple items
4746 ELSE
4747
4748 OPEN l_loc_onhand;
4749 FETCH l_loc_onhand INTO l_primary_quantity;
4750
4751 IF l_loc_onhand%NOTFOUND
4752 OR l_primary_quantity IS NULL
4753 OR l_primary_quantity <= 0 THEN
4754 CLOSE l_loc_onhand;
4755 RETURN 0;
4756 END IF;
4757 CLOSE l_loc_onhand;
4758 */
4759 -- log_mesg('GetItemonhand', 'g_GetItemOnhq_IsRuleCached :', g_GetItemOnhq_IsRuleCached );
4760 IF ( NVL(g_GetItemOnhq_IsRuleCached, 'N') = 'N' ) then
4761 -- log_mesg('GetItemonhand', 'Inside IsRuleCached Check:', g_GetItemOnhq_IsRuleCached );
4762 g_GetItemOnhq_IsRuleCached := 'Y' ;
4763 g_locator_item_quantity.DELETE; -- Re-initialize the tables before re-using it
4764 g_bulkCollect_Locator.DELETE;
4765 g_bulkCollect_quantity.DELETE;
4766
4767 OPEN l_loc_onhand; -- Execute the SQL and create the cache
4768 FETCH l_loc_onhand bulk collect into g_bulkCollect_Locator, g_bulkCollect_quantity;
4769 IF l_loc_onhand%ROWCOUNT = 0 THEN
4770 CLOSE l_loc_onhand;
4771 RETURN 0;
4772 END IF;
4773 -- Copy the g_bulkCollect_Locator and g_bulkCollect_quantity tables
4774 -- into g_locator_item_quantity
4775 FOR i IN g_bulkCollect_Locator.FIRST..g_bulkCollect_Locator.LAST LOOP
4776 g_locator_item_quantity( g_bulkCollect_Locator(i) ) := g_bulkCollect_quantity(i);
4777 -- log_mesg('GetItemonhand', 'g_locator_item_quantity:'|| to_char( g_bulkCollect_Locator(i))|| ': ', g_bulkCollect_quantity(i) );
4778 END LOOP;
4779 CLOSE l_loc_onhand;
4780 -- log_mesg('GetItemOnhand', 'l_loc_onhand Checking :', 'End' );
4781 END IF;
4782 -- log_mesg('GetItemOnhand', 'nvl(g_locator_item_quantity(p_locator_id), 0) :', nvl(g_locator_item_quantity(p_locator_id), 0) );
4783 IF g_locator_item_quantity.count() <> 0 then
4784 l_primary_quantity:= g_locator_item_quantity(p_locator_id);
4785 -- log_mesg('GetItemOnhand', 'g_locator_item_quantity(p_locator_id) :', g_locator_item_quantity(p_locator_id) );
4786 ELSE
4787 l_primary_quantity:= 0;
4788 -- log_mesg('GetItemonhand', 'l_primary_quantity:', l_primary_quantity );
4789 RETURN 0;
4790 END IF;
4791 -- log_mesg('GetItemonhand', 'Inise If - l_primary_quantity:', l_primary_quantity );
4792 --END IF;
4793 ELSIF p_subinventory_code IS NOT NULL THEN
4794 OPEN l_sub_onhand;
4795 FETCH l_sub_onhand INTO l_primary_quantity;
4796 -- log_mesg('GetItemonhand', 'Inside Subinv - l_primary_quantity:', l_primary_quantity );
4797 IF l_sub_onhand%NOTFOUND
4798 OR l_primary_quantity IS NULL
4799 OR l_primary_quantity <= 0 THEN
4800 CLOSE l_sub_onhand;
4801 RETURN 0;
4802 END IF;
4803
4804 CLOSE l_sub_onhand;
4805 ELSE
4806 OPEN l_item_onhand;
4807 FETCH l_item_onhand INTO l_primary_quantity;
4808 -- log_mesg('GetItemonhand', 'Inside not loc/sub - l_primary_quantity:', l_primary_quantity );
4809 IF l_item_onhand%NOTFOUND
4810 OR l_primary_quantity IS NULL
4811 OR l_primary_quantity <= 0 THEN
4812 CLOSE l_item_onhand;
4813 RETURN 0;
4814 END IF;
4815
4816 CLOSE l_item_onhand;
4817 END IF;
4818
4819 IF p_primary_uom IS NOT NULL
4820 AND p_transaction_uom IS NOT NULL
4821 AND p_primary_uom <> p_transaction_uom THEN
4822 l_transaction_quantity :=
4823 inv_convert.inv_um_convert(p_inventory_item_id, NULL, l_primary_quantity, p_primary_uom, p_transaction_uom, NULL, NULL);
4824 ELSE
4825 l_transaction_quantity := l_primary_quantity;
4826 END IF;
4827 -- log_mesg('GetItemonhand', 'l_transaction_quantity:', l_transaction_quantity );
4828 IF l_transaction_quantity <= 0 THEN
4829 -- log_mesg('GetItemonhand', 'l_transaction_quantity <= 0:', l_transaction_quantity );
4830 RETURN 0;
4831 END IF;
4832 -- log_mesg('GetItemonhand', 'Return Value - l_transaction_quantity:', l_transaction_quantity );
4833 RETURN l_transaction_quantity;
4834 EXCEPTION
4835 WHEN OTHERS THEN
4836 --log_mesg('GetItemonhand', 'Exception - Others :','' );
4837 return 0;
4838 END getitemonhand;
4839
4840
4841 -- API name : GetTotalOnHand
4842 -- Type : Private
4843 -- Function : Returns on hand stock of a given locator
4844 -- (all items) in the transaction UOM
4845 -- ( Used for capacity calculation parameters )
4846 FUNCTION gettotalonhand(
4847 p_organization_id IN NUMBER DEFAULT g_miss_num
4848 , p_subinventory_code IN VARCHAR2 DEFAULT g_miss_char
4849 , p_locator_id IN NUMBER DEFAULT g_miss_num
4850 , p_transaction_uom IN VARCHAR2 DEFAULT g_miss_char
4851 , p_locator_inventory_item_id IN NUMBER DEFAULT NULL
4852 , p_location_current_units IN NUMBER DEFAULT NULL
4853 , p_empty_flag IN VARCHAR2 DEFAULT NULL
4854 )
4855 RETURN NUMBER IS
4856 l_primary_quantity NUMBER;
4857 l_transaction_quantity NUMBER;
4858 l_primary_uom_code VARCHAR(3);
4859 l_total_quantity NUMBER;
4860 l_current_item_id NUMBER;
4861 l_current_quantity NUMBER;
4862
4863 CURSOR c_primary_uom IS
4864 SELECT primary_uom_code
4865 FROM mtl_system_items
4866 WHERE organization_id = p_organization_id
4867 AND inventory_item_id = p_locator_inventory_item_id;
4868
4869 --bug 2200812: change where clause so that we always assume p_locator_id is
4870 -- not null. This way, the correct index on mtl_onhand_quantities_detail will
4871 -- be used.
4872 CURSOR l_tot_onhand IS
4873 SELECT onhand.inventory_item_id
4874 , NVL(SUM(onhand.oh_quantity), 0)
4875 , msi.primary_uom_code
4876 FROM mtl_system_items msi
4877 , (-- on-hand
4878 SELECT moq.organization_id organization_id
4879 , moq.inventory_item_id inventory_item_id
4880 , moq.subinventory_code subinventory_code
4881 , moq.locator_id locator_id
4882 , moq.primary_transaction_quantity oh_quantity
4883 FROM mtl_onhand_quantities_detail moq
4884 -- to be more conservative ( or simply realistic ) we don't add
4885 -- negative on-hand to the capacity
4886 WHERE moq.transaction_quantity > 0
4887 UNION ALL
4888 -- pending issues/receipts and issues in transfers
4889 SELECT DECODE(mmtt.transaction_action_id, 3, mmtt.transfer_organization, mmtt.organization_id)
4890 , mmtt.inventory_item_id
4891 , mmtt.subinventory_code
4892 , mmtt.locator_id
4893 , DECODE(
4894 mmtt.transaction_action_id
4895 , 2, -ABS(mmtt.primary_quantity)
4896 , 3, -ABS(mmtt.primary_quantity)
4897 , mmtt.primary_quantity
4898 )
4899 FROM mtl_material_transactions_temp mmtt
4900 WHERE mmtt.inventory_item_id > 0 -- Index !!!
4901 AND mmtt.posting_flag = 'Y' -- pending txn
4902 AND NVL(transaction_status, -1) <> 2 -- not suggestions
4903 UNION ALL
4904 -- receiving side in transfers
4905 SELECT DECODE(mmtt.transaction_action_id, 3, mmtt.transfer_organization, mmtt.organization_id)
4906 , mmtt.inventory_item_id
4907 , mmtt.transfer_subinventory
4908 , mmtt.transfer_to_location
4909 , mmtt.primary_quantity
4910 FROM mtl_material_transactions_temp mmtt
4911 WHERE mmtt.inventory_item_id > 0 -- Index !!!
4912 AND mmtt.posting_flag = 'Y' -- pending txn
4913 AND NVL(mmtt.transaction_status, -1) <> 2 -- not suggestions
4914 AND mmtt.transaction_action_id IN (2, 3) -- transfers
4915 UNION ALL
4916 -- note: we don't add pick suggestions to capacity
4917 --
4918 -- put away suggestions (including transfers)
4919 SELECT DECODE(mmtt.transaction_action_id, 3, mmtt.transfer_organization, mmtt.organization_id)
4920 , mmtt.inventory_item_id
4921 , DECODE(
4922 mmtt.transaction_action_id
4923 , 2, mmtt.transfer_subinventory
4924 , 3, mmtt.transfer_subinventory
4925 , mmtt.subinventory_code
4926 )
4927 , DECODE(mmtt.transaction_action_id, 2, mmtt.transfer_to_location, 3, mmtt.transfer_to_location, mmtt.locator_id)
4928 , ABS(mmtt.primary_quantity)
4929 FROM mtl_material_transactions_temp mmtt
4930 WHERE mmtt.transaction_status = 2 -- suggestions
4931 -- AND mmtt.posting_flag = 'Y' /* 3446963 */
4932 AND mmtt.transaction_action_id IN -- put away
4933 (2, 3, 12, 27, 31, 33) -- only receipts and transfer
4934 UNION ALL
4935 -- put away suggestions still sitting in internal temp table
4936 SELECT DECODE(mtt.transaction_action_id, 3, mtrl.to_organization_id, mtrl.organization_id)
4937 , mtrl.inventory_item_id
4938 , wtt.to_subinventory_code subinventory_code
4939 , wtt.to_locator_id locator_id
4940 , wtt.primary_quantity
4941 FROM mtl_txn_request_lines mtrl, wms_transactions_temp wtt, mtl_transaction_types mtt
4942 WHERE wtt.type_code = 1 -- put away
4943 AND wtt.line_type_code = 2 -- output
4944 AND mtrl.line_id = wtt.transaction_temp_id
4945 AND mtrl.transaction_type_id = mtt.transaction_type_id) onhand
4946 WHERE onhand.organization_id = p_organization_id
4947 AND onhand.subinventory_code = p_subinventory_code
4948 AND onhand.locator_id = p_locator_id
4949 AND msi.inventory_item_id = onhand.inventory_item_id
4950 AND msi.organization_id = p_organization_id
4951 GROUP BY onhand.inventory_item_id, msi.primary_uom_code;
4952 BEGIN
4953 --check for missing org, item, or sub
4954 IF p_organization_id = g_miss_num
4955 OR p_subinventory_code = g_miss_char
4956 OR p_locator_id = g_miss_num
4957 OR p_transaction_uom = g_miss_char THEN
4958 RETURN NULL;
4959 ELSIF p_organization_id IS NULL
4960 OR p_subinventory_code IS NULL
4961 OR p_locator_id IS NULL
4962 OR p_transaction_uom IS NULL THEN
4963 RETURN NULL;
4964 END IF;
4965
4966 l_total_quantity := 0;
4967
4968 IF NVL(p_empty_flag, 'N') = 'Y' THEN
4969 l_total_quantity := 0;
4970 ELSIF p_locator_inventory_item_id IS NOT NULL THEN
4971 OPEN c_primary_uom;
4972 FETCH c_primary_uom INTO l_primary_uom_code;
4973
4974 IF c_primary_uom%NOTFOUND
4975 OR l_primary_uom_code IS NULL
4976 OR p_location_current_units <= 0 THEN
4977 l_primary_quantity := 0;
4978 ELSE
4979 l_primary_quantity := p_location_current_units;
4980 END IF;
4981
4982 IF l_primary_quantity > 0
4983 AND l_primary_uom_code <> p_transaction_uom THEN
4984 l_total_quantity := inv_convert.inv_um_convert(
4985 p_locator_inventory_item_id
4986 , NULL
4987 , l_primary_quantity
4988 , l_primary_uom_code
4989 , p_transaction_uom
4990 , NULL
4991 , NULL
4992 );
4993 ELSE
4994 l_total_quantity := l_primary_quantity;
4995 END IF;
4996 ELSE
4997 OPEN l_tot_onhand;
4998
4999 LOOP
5000 FETCH l_tot_onhand INTO l_current_item_id, l_primary_quantity, l_primary_uom_code;
5001 EXIT WHEN l_tot_onhand%NOTFOUND;
5002
5003 IF l_primary_uom_code <> p_transaction_uom THEN
5004 l_current_quantity :=
5005 inv_convert.inv_um_convert(l_current_item_id, NULL, l_primary_quantity, l_primary_uom_code, p_transaction_uom, NULL, NULL);
5006 ELSE
5007 l_current_quantity := l_primary_quantity;
5008 END IF;
5009
5010 IF l_current_quantity <= 0 THEN
5011 l_current_quantity := 0;
5012 END IF;
5013
5014 l_total_quantity := l_total_quantity + l_current_quantity;
5015 END LOOP;
5016 END IF;
5017
5018 RETURN l_total_quantity;
5019 END gettotalonhand;
5020
5021 -- API name : IsItemInLocator
5022 -- Type : Private
5023 -- Function : Returns 'Y' if the given item resides in the given
5024 -- locator, 'N' otherwise
5025 FUNCTION isiteminlocator(
5026 p_organization_id IN NUMBER
5027 , p_inventory_item_id IN NUMBER
5028 , p_subinventory_code IN VARCHAR2
5029 , p_locator_id IN NUMBER
5030 )
5031 RETURN VARCHAR2 IS
5032 l_return_value VARCHAR2(1);
5033 BEGIN
5034 SELECT 'Y'
5035 INTO l_return_value
5036 FROM DUAL
5037 WHERE EXISTS( SELECT 'Y'
5038 FROM mtl_onhand_quantities_detail
5039 WHERE organization_id = p_organization_id
5040 AND inventory_item_id = p_inventory_item_id
5041 AND subinventory_code = p_subinventory_code
5042 AND locator_id = p_locator_id);
5043
5044 IF l_return_value = 'Y' THEN
5045 RETURN 'Y';
5046 ELSE
5047 RETURN 'N';
5048 END IF;
5049 EXCEPTION
5050 WHEN OTHERS THEN
5051 RETURN 'N';
5052 END isiteminlocator;
5053
5054 -- API name : GetOuterLpnQuantityRevLot
5055 -- Type : Private
5056 -- Function : Returns quantity of the given item, revision, and lot
5057 -- in the outermost LPN containing the given LPN
5058 FUNCTION getouterlpnquantityrevlot(p_lpn_id IN NUMBER, p_inventory_item_id IN NUMBER, p_revision IN VARCHAR2, p_lot_number IN VARCHAR2)
5059 RETURN NUMBER IS
5060 l_return_value NUMBER;
5061 BEGIN
5062 IF p_lpn_id IS NULL
5063 OR p_inventory_item_id IS NULL THEN
5064 RETURN -1;
5065 END IF;
5066
5067 SELECT SUM(moqdx.primary_transaction_quantity)
5068 INTO l_return_value
5069 FROM mtl_onhand_quantities_detail moqdx
5070 WHERE moqdx.lpn_id IN (SELECT wlpn1.lpn_id
5071 FROM wms_license_plate_numbers wlpn1
5072 WHERE wlpn1.outermost_lpn_id = p_lpn_id)
5073 AND moqdx.inventory_item_id = p_inventory_item_id
5074 AND NVL(moqdx.revision, '-99') = NVL(p_revision, '-99')
5075 AND NVL(moqdx.lot_number, '-9999') = NVL(p_lot_number, '-9999');
5076
5077 RETURN l_return_value;
5078 EXCEPTION
5079 WHEN OTHERS THEN
5080 RETURN -1;
5081 END getouterlpnquantityrevlot;
5082
5083 -- API name : GetOuterLpnQuantity
5084 -- Type : Private
5085 -- Function : Returns quantity of the given item
5086 -- in the outermost LPN containing the given LPN
5087 FUNCTION getouterlpnquantity(p_lpn_id IN NUMBER, p_inventory_item_id IN NUMBER)
5088 RETURN NUMBER IS
5089 l_return_value NUMBER;
5090 BEGIN
5091 IF p_lpn_id IS NULL
5092 OR p_inventory_item_id IS NULL THEN
5093 RETURN -1;
5094 END IF;
5095
5096 SELECT SUM(moqdx.primary_transaction_quantity)
5097 INTO l_return_value
5098 FROM mtl_onhand_quantities_detail moqdx
5099 WHERE moqdx.lpn_id IN (SELECT wlpn1.lpn_id
5100 FROM wms_license_plate_numbers wlpn1
5101 WHERE wlpn1.outermost_lpn_id = p_lpn_id)
5102 AND moqdx.inventory_item_id = p_inventory_item_id;
5103
5104 RETURN l_return_value;
5105 EXCEPTION
5106 WHEN OTHERS THEN
5107 RETURN -1;
5108 END getouterlpnquantity;
5109
5110 -- API name : GetOuterLpnNumOtherItems
5111 -- Type : Private
5112 -- Function : Returns number of items - 1
5113 -- in the outermost LPN containing the given LPN
5114 FUNCTION getouterlpnnumotheritems(p_lpn_id IN NUMBER)
5115 RETURN NUMBER IS
5116 l_return_value NUMBER;
5117 BEGIN
5118 IF p_lpn_id IS NULL THEN
5119 RETURN -1;
5120 END IF;
5121
5122 SELECT COUNT(DISTINCT (moqdx.inventory_item_id)) - 1
5123 INTO l_return_value
5124 FROM mtl_onhand_quantities_detail moqdx
5125 WHERE moqdx.lpn_id IN (SELECT wlpn1.lpn_id
5126 FROM wms_license_plate_numbers wlpn1
5127 WHERE wlpn1.outermost_lpn_id = p_lpn_id);
5128
5129 RETURN l_return_value;
5130 EXCEPTION
5131 WHEN OTHERS THEN
5132 RETURN -1;
5133 END getouterlpnnumotheritems;
5134
5135 -- API name : GetOuterLpnNumOtherRevs
5136 -- Type : Private
5137 -- Function : Returns number of revisions of this item - 1
5138 -- in the outermost LPN containing the given LPN
5139 FUNCTION getouterlpnnumotherrevs(p_lpn_id IN NUMBER, p_inventory_item_id IN NUMBER)
5140 RETURN NUMBER IS
5141 l_return_value NUMBER;
5142 BEGIN
5143 IF p_lpn_id IS NULL
5144 OR p_inventory_item_id IS NULL THEN
5145 RETURN -1;
5146 END IF;
5147
5148 SELECT COUNT(DISTINCT (moqdx.revision)) - 1
5149 INTO l_return_value
5150 FROM mtl_onhand_quantities_detail moqdx
5151 WHERE moqdx.lpn_id IN (SELECT wlpn1.lpn_id
5152 FROM wms_license_plate_numbers wlpn1
5153 WHERE wlpn1.outermost_lpn_id = p_lpn_id)
5154 AND moqdx.inventory_item_id = p_inventory_item_id;
5155
5156 RETURN l_return_value;
5157 EXCEPTION
5158 WHEN OTHERS THEN
5159 RETURN -1;
5160 END getouterlpnnumotherrevs;
5161
5162 -- API name : GetOuterLpnNumOtherLots
5163 -- Type : Private
5164 -- Function : Returns number of lots of this item - 1
5165 -- in the outermost LPN containing the given LPN
5166 FUNCTION getouterlpnnumotherlots(p_lpn_id IN NUMBER, p_inventory_item_id IN NUMBER)
5167 RETURN NUMBER IS
5168 l_return_value NUMBER;
5169 BEGIN
5170 IF p_lpn_id IS NULL
5171 OR p_inventory_item_id IS NULL THEN
5172 RETURN -1;
5173 END IF;
5174
5175 SELECT COUNT(DISTINCT (moqdx.lot_number)) - 1
5176 INTO l_return_value
5177 FROM mtl_onhand_quantities_detail moqdx
5178 WHERE moqdx.lpn_id IN (SELECT wlpn1.lpn_id
5179 FROM wms_license_plate_numbers wlpn1
5180 WHERE wlpn1.outermost_lpn_id = p_lpn_id)
5181 AND moqdx.inventory_item_id = p_inventory_item_id;
5182
5183 RETURN l_return_value;
5184 EXCEPTION
5185 WHEN OTHERS THEN
5186 RETURN -1;
5187 END getouterlpnnumotherlots;
5188
5189 -- API name : GetLpnQuantityRevLot
5190 -- Type : Private
5191 -- Function : Returns quantity of the given item, revision, and lot
5192 -- in the given LPN
5193 FUNCTION getlpnquantityrevlot(p_lpn_id IN NUMBER, p_inventory_item_id IN NUMBER, p_revision IN VARCHAR2, p_lot_number IN VARCHAR2)
5194 RETURN NUMBER IS
5195 l_return_value NUMBER;
5196 BEGIN
5197 IF p_lpn_id IS NULL
5198 OR p_inventory_item_id IS NULL THEN
5199 RETURN -1;
5200 END IF;
5201
5202 SELECT SUM(moqdx.primary_transaction_quantity)
5203 INTO l_return_value
5204 FROM mtl_onhand_quantities_detail moqdx
5205 WHERE moqdx.lpn_id = p_lpn_id
5206 AND moqdx.inventory_item_id = p_inventory_item_id
5207 AND NVL(moqdx.revision, '-99') = NVL(p_revision, '-99')
5208 AND NVL(moqdx.lot_number, '-9999') = NVL(p_lot_number, '-9999');
5209
5210 RETURN l_return_value;
5211 EXCEPTION
5212 WHEN OTHERS THEN
5213 RETURN 0;
5214 END getlpnquantityrevlot;
5215
5216 -- API name : GetLpnQuantity
5217 -- Type : Private
5218 -- Function : Returns quantity of the given item
5219 -- in the given LPN
5220 FUNCTION getlpnquantity(p_lpn_id IN NUMBER, p_inventory_item_id IN NUMBER)
5221 RETURN NUMBER IS
5222 l_return_value NUMBER;
5223 BEGIN
5224 IF p_lpn_id IS NULL
5225 OR p_inventory_item_id IS NULL THEN
5226 RETURN -1;
5227 END IF;
5228
5229 SELECT SUM(moqdx.primary_transaction_quantity)
5230 INTO l_return_value
5231 FROM mtl_onhand_quantities_detail moqdx
5232 WHERE moqdx.lpn_id = p_lpn_id
5233 AND moqdx.inventory_item_id = p_inventory_item_id;
5234
5235 RETURN l_return_value;
5236 EXCEPTION
5237 WHEN OTHERS THEN
5238 RETURN 0;
5239 END getlpnquantity;
5240
5241 -- API name : GetLpnNumOtherItems
5242 -- Type : Private
5243 -- Function : Returns number of items - 1
5244 -- in the the given LPN
5245 FUNCTION getlpnnumotheritems(p_lpn_id IN NUMBER)
5246 RETURN NUMBER IS
5247 l_return_value NUMBER;
5248 BEGIN
5249 IF p_lpn_id IS NULL THEN
5250 RETURN -1;
5251 END IF;
5252
5253 SELECT COUNT(DISTINCT (moqdx.inventory_item_id))
5254 INTO l_return_value
5255 FROM mtl_onhand_quantities_detail moqdx
5256 WHERE moqdx.lpn_id = p_lpn_id;
5257
5258 RETURN l_return_value;
5259 EXCEPTION
5260 WHEN OTHERS THEN
5261 RETURN -1;
5262 END getlpnnumotheritems;
5263
5264 -- API name : GetLpnNumOtherRevs
5265 -- Type : Private
5266 -- Function : Returns number of revisions of this item - 1
5267 -- in the given LPN
5268 FUNCTION getlpnnumotherrevs(p_lpn_id IN NUMBER, p_inventory_item_id IN NUMBER)
5269 RETURN NUMBER IS
5270 l_return_value NUMBER;
5271 BEGIN
5272 IF p_lpn_id IS NULL
5273 OR p_inventory_item_id IS NULL THEN
5274 RETURN -1;
5275 END IF;
5276
5277 SELECT COUNT(DISTINCT (moqdx.revision))
5278 INTO l_return_value
5279 FROM mtl_onhand_quantities_detail moqdx
5280 WHERE moqdx.lpn_id = p_lpn_id
5281 AND moqdx.inventory_item_id = p_inventory_item_id;
5282
5283 RETURN l_return_value;
5284 EXCEPTION
5285 WHEN OTHERS THEN
5286 RETURN -1;
5287 END getlpnnumotherrevs;
5288
5289 -- API name : GetLpnNumOtherLots
5290 -- Type : Private
5291 -- Function : Returns number of lots of this item - 1
5292 -- in the the given LPN
5293 FUNCTION getlpnnumotherlots(p_lpn_id IN NUMBER, p_inventory_item_id IN NUMBER)
5294 RETURN NUMBER IS
5295 l_return_value NUMBER;
5296 BEGIN
5297 IF p_lpn_id IS NULL
5298 OR p_inventory_item_id IS NULL THEN
5299 RETURN -1;
5300 END IF;
5301
5302 SELECT COUNT(DISTINCT (moqdx.lot_number))
5303 INTO l_return_value
5304 FROM mtl_onhand_quantities_detail moqdx
5305 WHERE moqdx.lpn_id = p_lpn_id
5306 AND moqdx.inventory_item_id = p_inventory_item_id;
5307
5308 RETURN l_return_value;
5309 EXCEPTION
5310 WHEN OTHERS THEN
5311 RETURN -1;
5312 END getlpnnumotherlots;
5313
5314 -- API name : GetLpnNumNestedLevels
5315 -- Type : Private
5316 -- Function : Returns number of LPNs between this LPN and the outermost
5317 -- LPN containing this LPN. 1 means that the given LPN
5318 -- is the outermost LPN.
5319 FUNCTION getlpnnumnestedlevels(p_lpn_id IN NUMBER)
5320 RETURN NUMBER IS
5321 l_return_value NUMBER;
5322 BEGIN
5323 IF p_lpn_id IS NULL THEN
5324 RETURN -1;
5325 END IF;
5326
5327 SELECT COUNT(wlpnx.lpn_id)
5328 INTO l_return_value
5329 FROM wms_license_plate_numbers wlpnx
5330 START WITH wlpnx.lpn_id = p_lpn_id
5331 CONNECT BY wlpnx.lpn_id = PRIOR parent_lpn_id;
5332
5333 RETURN l_return_value;
5334 EXCEPTION
5335 WHEN OTHERS THEN
5336 RETURN -1;
5337 END getlpnnumnestedlevels;
5338
5339 --==============================================================
5340 -- API name : GetPOHeaderLineID
5341 -- Type : Private
5342 -- Function : Returns PO Header ID or Line ID based on Move Order Line
5343 -- Reference and Reference ID and header or line flag.
5344 -- ( Used for join condition in seed data )
5345
5346 FUNCTION getpoheaderlineid(
5347 p_transaction_source_type_id IN NUMBER
5348 , p_reference IN VARCHAR2 DEFAULT g_miss_char
5349 , p_reference_id IN NUMBER DEFAULT g_miss_num
5350 , p_header_flag IN VARCHAR2 DEFAULT 'N'
5351 , p_line_flag IN VARCHAR2 DEFAULT 'N'
5352 )
5353 RETURN NUMBER IS
5354 l_po_header_id NUMBER := -1;
5355 l_po_line_id NUMBER := -1;
5356 l_return_val NUMBER := -1;
5357
5358 CURSOR l_po_distributions_curs IS
5359 SELECT po_header_id
5360 , po_line_id
5361 FROM po_distributions_all
5362 WHERE po_distribution_id = p_reference_id;
5363
5364 CURSOR l_po_line_locations_curs IS
5365 SELECT po_header_id
5366 , po_line_id
5367 FROM po_line_locations_all
5368 WHERE line_location_id = p_reference_id;
5369
5370 CURSOR l_rcv_transactions_curs IS
5371 SELECT po_header_id
5372 , po_line_id
5373 FROM rcv_transactions
5374 WHERE transaction_id = p_reference_id;
5375 BEGIN
5376 IF inv_pp_debug.is_debug_mode THEN
5377 inv_pp_debug.send_message_to_pipe('GetPOHeaderLineID(): ');
5378 inv_pp_debug.send_message_to_pipe('p_transaction_source_type_id: '|| p_transaction_source_type_id);
5379 inv_pp_debug.send_message_to_pipe('p_reference: '|| p_reference);
5380 inv_pp_debug.send_message_to_pipe('p_reference_id: '|| p_reference_id);
5381 inv_pp_debug.send_message_to_pipe('p_header_flag: '|| p_header_flag);
5382 inv_pp_debug.send_message_to_pipe('p_line_flag: '|| p_line_flag);
5383 END IF;
5384
5385 -- validate input parameters
5386 IF p_transaction_source_type_id IS NULL
5387 OR p_reference_id = g_miss_num
5388 OR p_reference_id IS NULL THEN
5389 RETURN NULL;
5390 END IF;
5391
5392 --bug 2983185 - use cached values
5393 IF (g_po_header_Id IS NULL) or (nvl(g_po_reference_id,-1) <> p_reference_id) THEN
5394
5395 IF (p_reference = 'PO_DISTRIBUTION_ID') THEN
5396 OPEN l_po_distributions_curs;
5397 FETCH l_po_distributions_curs INTO l_po_header_id, l_po_line_id;
5398 CLOSE l_po_distributions_curs;
5399 ELSIF (p_reference = 'PO_LINE_LOCATION_ID') THEN
5400 OPEN l_po_line_locations_curs;
5401 FETCH l_po_line_locations_curs INTO l_po_header_id, l_po_line_id;
5402 CLOSE l_po_line_locations_curs;
5403 -- =========================================================
5404 -- transaction_source_type_id =1 indicates that mmtt record
5405 -- has reference to PO. This case, p_reference_id is
5406 -- rcv_transaction_id
5407 -- ==========================================================
5408 ELSIF (p_transaction_source_type_id = 1
5409 AND p_reference_id IS NOT NULL
5410 ) THEN
5411 IF inv_pp_debug.is_debug_mode THEN
5412 inv_pp_debug.send_message_to_pipe('Open cursor rcv_transactions ');
5413 END IF;
5414
5415 OPEN l_rcv_transactions_curs;
5416 FETCH l_rcv_transactions_curs INTO l_po_header_id, l_po_line_id;
5417 CLOSE l_rcv_transactions_curs;
5418
5419 IF inv_pp_debug.is_debug_mode THEN
5420 inv_pp_debug.send_message_to_pipe('l_po_header_id : '|| l_po_header_id);
5421 inv_pp_debug.send_message_to_pipe('l_po_line_id : '|| l_po_line_id);
5422 END IF;
5423 END IF;
5424 --bug 2983185 - PO information constant for move order line, so
5425 -- we don't need to constantly requery this information for every
5426 -- locator. Instead, store these values in global variables.
5427 g_po_header_id := l_po_header_id;
5428 g_po_line_id := l_po_line_id;
5429 g_po_reference_id := p_reference_id;
5430 END IF;
5431
5432 IF (p_header_flag = 'Y') THEN
5433 l_return_val := g_po_header_id;
5434 ELSIF (p_line_flag = 'Y') THEN
5435 l_return_val := g_po_line_id;
5436 END IF;
5437
5438 RETURN l_return_val;
5439 END getpoheaderlineid;
5440
5441 -- API name : GetProxPickOrder
5442 -- Type : Private
5443 -- Function : Returns the minimum distance between this locator
5444 -- and the nearest locator containing the item,
5445 -- as calculated using the locator's picking order
5446 -- ( Used for building rules)
5447 FUNCTION getproxpickorder(
5448 p_organization_id IN NUMBER
5449 , p_inventory_item_id IN NUMBER
5450 , p_subinventory_code IN VARCHAR2
5451 , p_locator_id IN NUMBER
5452 )
5453 RETURN NUMBER IS
5454 l_pick_order NUMBER;
5455
5456 CURSOR c_pick_order IS
5457 SELECT MIN(ABS(NVL(milx.picking_order, -9999) - NVL(mil.picking_order, 9999)))
5458 FROM mtl_item_locations mil, mtl_item_locations milx, mtl_onhand_quantities_detail moq
5459 WHERE mil.inventory_location_id = p_locator_id
5460 AND mil.organization_id = p_organization_id
5461 AND moq.organization_id = p_organization_id
5462 AND moq.inventory_item_id = p_inventory_item_id
5463 AND moq.subinventory_code = p_subinventory_code
5464 AND milx.organization_id = moq.organization_id
5465 AND milx.inventory_location_id = moq.locator_id;
5466 BEGIN
5467 IF p_organization_id IS NULL
5468 OR p_inventory_item_id IS NULL
5469 OR p_subinventory_code IS NULL
5470 OR p_locator_id IS NULL THEN
5471 RETURN 999999999;
5472 END IF;
5473
5474 OPEN c_pick_order;
5475 FETCH c_pick_order INTO l_pick_order;
5476
5477 IF c_pick_order%NOTFOUND
5478 OR l_pick_order IS NULL THEN
5479 l_pick_order := 999999999;
5480 END IF;
5481
5482 CLOSE c_pick_order;
5483 RETURN l_pick_order;
5484 END getproxpickorder;
5485
5486 -- API name : GetProxCoordinates
5487 -- Type : Private
5488 -- Function : Returns the minimum distance between this locator
5489 -- and the nearest locator containing the item,
5490 -- as calculated using xyz coordinates
5491 -- ( Used for building rules)
5492 FUNCTION getproxcoordinates(
5493 p_organization_id IN NUMBER
5494 , p_inventory_item_id IN NUMBER
5495 , p_subinventory_code IN VARCHAR2
5496 , p_locator_id IN NUMBER
5497 )
5498 RETURN NUMBER IS
5499 l_pick_order NUMBER;
5500
5501 CURSOR c_pick_order IS
5502 SELECT MIN(
5503 ((NVL(milx.x_coordinate, -9999) - NVL(mil.x_coordinate, 9999)) * (NVL(milx.x_coordinate, -9999) - NVL(
5504 mil.x_coordinate
5505 , 9999
5506 )
5507 )
5508 )
5509 + ((NVL(milx.y_coordinate, -9999) - NVL(mil.y_coordinate, 9999)) * (NVL(milx.y_coordinate, -9999) - NVL(
5510 mil.y_coordinate
5511 , 9999
5512 )
5513 )
5514 )
5515 + ((NVL(milx.z_coordinate, -9999) - NVL(mil.z_coordinate, 9999)) * (NVL(milx.z_coordinate, -9999) - NVL(
5516 mil.z_coordinate
5517 , 9999
5518 )
5519 )
5520 )
5521 )
5522 FROM mtl_item_locations mil, mtl_item_locations milx, mtl_onhand_quantities_detail moq
5523 WHERE mil.inventory_location_id = p_locator_id
5524 AND mil.organization_id = p_organization_id
5525 AND moq.organization_id = p_organization_id
5526 AND moq.inventory_item_id = p_inventory_item_id
5527 AND moq.subinventory_code = p_subinventory_code
5528 AND milx.organization_id = moq.organization_id
5529 AND milx.inventory_location_id = moq.locator_id;
5530 BEGIN
5531 IF p_organization_id IS NULL
5532 OR p_inventory_item_id IS NULL
5533 OR p_subinventory_code IS NULL
5534 OR p_locator_id IS NULL THEN
5535 RETURN 999999999;
5536 END IF;
5537
5538 OPEN c_pick_order;
5539 FETCH c_pick_order INTO l_pick_order;
5540
5541 IF c_pick_order%NOTFOUND
5542 OR l_pick_order IS NULL THEN
5543 l_pick_order := 999999999;
5544 END IF;
5545
5546 CLOSE c_pick_order;
5547 RETURN l_pick_order;
5548 END getproxcoordinates;
5549
5550 -- API name : GetNumOtherItems
5551 -- Type : Private
5552 -- Function : Returns the number of items within the locator
5553 -- other than the item passed in as a parameter
5554 -- ( Used for building rules)
5555 FUNCTION getnumotheritems(
5556 p_organization_id IN NUMBER
5557 , p_inventory_item_id IN NUMBER
5558 , p_subinventory_code IN VARCHAR2
5559 , p_locator_id IN NUMBER
5560 , p_locator_inventory_item_id IN NUMBER DEFAULT NULL
5561 )
5562 RETURN NUMBER IS
5563 l_num_items NUMBER;
5564
5565 CURSOR c_items IS
5566 SELECT COUNT(inventory_item_id)
5567 FROM (SELECT inventory_item_id
5568 FROM (--current onhand
5569 SELECT inventory_item_id
5570 FROM mtl_onhand_quantities_detail
5571 WHERE organization_id = p_organization_id
5572 AND subinventory_code = p_subinventory_code
5573 AND locator_id = p_locator_id
5574 AND inventory_item_id <> p_inventory_item_id
5575 UNION ALL
5576 --pending receipts and putaway suggestions
5577 SELECT inventory_item_id
5578 FROM mtl_material_transactions_temp
5579 WHERE organization_id = p_organization_id
5580 AND subinventory_code = p_subinventory_code
5581 AND locator_id = p_locator_id
5582 AND inventory_item_id <> p_inventory_item_id
5583 AND transaction_action_id IN (12, 27, 31)
5584 UNION ALL
5585 --pending transfers and suggestions
5586 SELECT inventory_item_id
5587 FROM mtl_material_transactions_temp
5588 WHERE organization_id = p_organization_id
5589 AND transfer_subinventory = p_subinventory_code
5590 AND transfer_to_location = p_locator_id
5591 AND inventory_item_id <> p_inventory_item_id
5592 AND transaction_action_id IN (2, 3, 28))
5593 GROUP BY inventory_item_id);
5594 BEGIN
5595 IF p_organization_id IS NULL
5596 OR p_inventory_item_id IS NULL
5597 OR p_subinventory_code IS NULL
5598 OR p_locator_id IS NULL THEN
5599 RETURN 0;
5600 END IF;
5601
5602 --if current item is the only item in the locator, return 0
5603 IF p_locator_inventory_item_id IS NOT NULL
5604 AND p_locator_inventory_item_id = p_inventory_item_id THEN
5605 l_num_items := 0;
5606 --if only one item in the locator, then return 1
5607 ELSIF p_locator_inventory_item_id IS NOT NULL THEN
5608 l_num_items := 1;
5609 ELSE
5610 OPEN c_items;
5611 FETCH c_items INTO l_num_items;
5612
5613 IF c_items%NOTFOUND
5614 OR l_num_items IS NULL THEN
5615 l_num_items := 0;
5616 END IF;
5617
5618 CLOSE c_items;
5619 END IF;
5620
5621 RETURN l_num_items;
5622 EXCEPTION
5623 WHEN OTHERS THEN
5624 RETURN 0;
5625 END getnumotheritems;
5626
5627 -- API name : GetNumOtherLots
5628 -- Type : Private
5629 -- Function : Returns the number of lots for the given item
5630 -- within the locator other than the given lot
5631 -- ( Used for building rules)
5632 FUNCTION getnumotherlots(
5633 p_organization_id IN NUMBER
5634 , p_inventory_item_id IN NUMBER
5635 , p_subinventory_code IN VARCHAR2
5636 , p_locator_id IN NUMBER
5637 , p_lot_number IN VARCHAR2
5638 )
5639 RETURN NUMBER IS
5640 l_num_lots NUMBER;
5641
5642 CURSOR c_lots IS
5643 SELECT COUNT(lot_number)
5644 FROM (SELECT lot_number
5645 FROM (--current onhand
5646 SELECT lot_number
5647 FROM mtl_onhand_quantities_detail
5648 WHERE organization_id = p_organization_id
5649 AND inventory_item_id = p_inventory_item_id
5650 AND subinventory_code = p_subinventory_code
5651 AND locator_id = p_locator_id
5652 AND lot_number IS NOT NULL
5653 AND lot_number <> p_lot_number
5654 UNION ALL
5655 --pending receipts and putaway suggestions (lot in MMTT)
5656 SELECT lot_number
5657 FROM mtl_material_transactions_temp
5658 WHERE organization_id = p_organization_id
5659 AND inventory_item_id = p_inventory_item_id
5660 AND subinventory_code = p_subinventory_code
5661 AND locator_id = p_locator_id
5662 AND lot_number IS NOT NULL
5663 AND lot_number <> p_lot_number
5664 AND transaction_action_id IN (12, 27, 31)
5665 UNION ALL
5666 --pending transfers and suggestions (lot in MMTT)
5667 SELECT lot_number
5668 FROM mtl_material_transactions_temp
5669 WHERE organization_id = p_organization_id
5670 AND inventory_item_id = p_inventory_item_id
5671 AND transfer_subinventory = p_subinventory_code
5672 AND transfer_to_location = p_locator_id
5673 AND lot_number IS NOT NULL
5674 AND lot_number <> p_lot_number
5675 AND transaction_action_id IN (2, 3, 28)
5676 UNION ALL
5677 --pending receipts and putaway suggestions (lot in MTLT)
5678 SELECT mtlt.lot_number
5679 FROM mtl_material_transactions_temp mmtt, mtl_transaction_lots_temp mtlt
5680 WHERE mmtt.organization_id = p_organization_id
5681 AND mmtt.inventory_item_id = p_inventory_item_id
5682 AND mmtt.subinventory_code = p_subinventory_code
5683 AND mmtt.locator_id = p_locator_id
5684 AND mmtt.lot_number IS NULL
5685 AND mmtt.transaction_action_id IN (12, 27, 31)
5686 AND mtlt.transaction_temp_id = mmtt.transaction_temp_id
5687 AND mtlt.lot_number <> p_lot_number
5688 UNION ALL
5689 --pending transfers and suggestions (lot in MTLT)
5690 SELECT mtlt.lot_number
5691 FROM mtl_material_transactions_temp mmtt, mtl_transaction_lots_temp mtlt
5692 WHERE mmtt.organization_id = p_organization_id
5693 AND mmtt.inventory_item_id = p_inventory_item_id
5694 AND mmtt.transfer_subinventory = p_subinventory_code
5695 AND mmtt.transfer_to_location = p_locator_id
5696 AND mmtt.lot_number IS NULL
5697 AND mmtt.transaction_action_id IN (2, 3, 28)
5698 AND mtlt.transaction_temp_id = mmtt.transaction_temp_id
5699 AND mtlt.lot_number <> p_lot_number
5700 UNION ALL
5701 --putaway suggestions already created for this move order
5702 SELECT lot_number
5703 FROM wms_transactions_temp
5704 WHERE type_code = 1
5705 AND line_type_code = 2
5706 AND to_subinventory_code = p_subinventory_code
5707 AND to_locator_id = p_locator_id
5708 AND lot_number IS NOT NULL
5709 AND lot_number <> p_lot_number)
5710 GROUP BY lot_number);
5711 BEGIN
5712 IF p_organization_id IS NULL
5713 OR p_inventory_item_id IS NULL
5714 OR p_subinventory_code IS NULL
5715 OR p_locator_id IS NULL
5716 OR p_lot_number IS NULL THEN
5717 RETURN 0;
5718 END IF;
5719
5720 OPEN c_lots;
5721 FETCH c_lots INTO l_num_lots;
5722
5723 IF c_lots%NOTFOUND
5724 OR l_num_lots IS NULL THEN
5725 l_num_lots := 0;
5726 END IF;
5727
5728 CLOSE c_lots;
5729 RETURN l_num_lots;
5730 EXCEPTION
5731 WHEN OTHERS THEN
5732 RETURN 0;
5733 END getnumotherlots;
5734
5735 -- API name : GetNumOtherRevisions
5736 -- Type : Private
5737 -- Function : Returns the number of revisions for the given item
5738 -- within the locator other than the given revision
5739 -- ( Used for building rules)
5740 FUNCTION getnumotherrevisions(
5741 p_organization_id IN NUMBER
5742 , p_inventory_item_id IN NUMBER
5743 , p_subinventory_code IN VARCHAR2
5744 , p_locator_id IN NUMBER
5745 , p_revision IN VARCHAR2
5746 )
5747 RETURN NUMBER IS
5748 l_num_revisions NUMBER;
5749
5750 CURSOR c_revisions IS
5751 SELECT COUNT(revision)
5752 FROM (SELECT revision
5753 FROM (--current onhand
5754 SELECT revision
5755 FROM mtl_onhand_quantities_detail
5756 WHERE organization_id = p_organization_id
5757 AND subinventory_code = p_subinventory_code
5758 AND locator_id = p_locator_id
5759 AND inventory_item_id = p_inventory_item_id
5760 AND revision IS NOT NULL
5761 AND revision <> p_revision
5762 UNION ALL
5763 --pending receipts and putaway suggestions
5764 SELECT revision
5765 FROM mtl_material_transactions_temp
5766 WHERE organization_id = p_organization_id
5767 AND subinventory_code = p_subinventory_code
5768 AND locator_id = p_locator_id
5769 AND inventory_item_id = p_inventory_item_id
5770 AND revision IS NOT NULL
5771 AND revision <> p_revision
5772 AND transaction_action_id IN (12, 27, 31)
5773 UNION ALL
5774 --pending transfer txns and suggestions
5775 SELECT revision
5776 FROM mtl_material_transactions_temp
5777 WHERE organization_id = p_organization_id
5778 AND transfer_subinventory = p_subinventory_code
5779 AND transfer_to_location = p_locator_id
5780 AND inventory_item_id = p_inventory_item_id
5781 AND revision IS NOT NULL
5782 AND revision <> p_revision
5783 AND transaction_action_id IN (2, 3, 28)
5784 UNION ALL
5785 --suggestions already created for this move order line
5786 SELECT revision
5787 FROM wms_transactions_temp
5788 WHERE type_code = 1
5789 AND line_type_code = 2
5790 AND to_subinventory_code = p_subinventory_code
5791 AND to_locator_id = p_locator_id
5792 AND revision IS NOT NULL
5793 AND revision <> p_revision)
5794 GROUP BY revision);
5795 BEGIN
5796 IF p_organization_id IS NULL
5797 OR p_inventory_item_id IS NULL
5798 OR p_subinventory_code IS NULL
5799 OR p_locator_id IS NULL
5800 OR p_revision IS NULL THEN
5801 RETURN 0;
5802 END IF;
5803
5804 OPEN c_revisions;
5805 FETCH c_revisions INTO l_num_revisions;
5806
5807 IF c_revisions%NOTFOUND
5808 OR l_num_revisions IS NULL THEN
5809 l_num_revisions := 0;
5810 END IF;
5811
5812 CLOSE c_revisions;
5813 RETURN l_num_revisions;
5814 EXCEPTION
5815 WHEN OTHERS THEN
5816 RETURN 0;
5817 END getnumotherrevisions;
5818
5819 FUNCTION getnumemptylocators(p_organization_id IN NUMBER, p_subinventory_code IN VARCHAR2)
5820 RETURN NUMBER IS
5821 CURSOR c_locations IS
5822 SELECT COUNT(inventory_location_id)
5823 FROM mtl_item_locations
5824 WHERE organization_id = p_organization_id
5825 AND subinventory_code = p_subinventory_code
5826 AND empty_flag = 'Y';
5827
5828 l_hash_size NUMBER;
5829 l_hash_base NUMBER;
5830 l_hash_index NUMBER;
5831 l_empty_locs NUMBER;
5832 BEGIN
5833 IF p_organization_id IS NULL
5834 OR p_subinventory_code IS NULL THEN
5835 RETURN 0;
5836 END IF;
5837
5838 l_hash_base := 1;
5839 l_hash_size := POWER(2, 15);
5840 l_hash_index := DBMS_UTILITY.get_hash_value(NAME => p_subinventory_code, base => l_hash_base, hash_size => l_hash_size);
5841
5842 LOOP
5843 EXIT WHEN NOT g_num_empty_locators.EXISTS(l_hash_index);
5844 EXIT WHEN p_subinventory_code = g_num_empty_locators(l_hash_index).subinventory_code;
5845 l_hash_index := l_hash_index + 1;
5846 END LOOP;
5847
5848 IF g_num_empty_locators.EXISTS(l_hash_index) THEN
5849 RETURN g_num_empty_locators(l_hash_index).num_empty_locators;
5850 ELSE
5851 OPEN c_locations;
5852 FETCH c_locations INTO l_empty_locs;
5853
5854 IF c_locations%NOTFOUND
5855 OR l_empty_locs IS NULL THEN
5856 l_empty_locs := 0;
5857 END IF;
5858
5859 CLOSE c_locations;
5860 g_num_empty_locators(l_hash_index).subinventory_code := p_subinventory_code;
5861 g_num_empty_locators(l_hash_index).num_empty_locators := l_empty_locs;
5862 END IF;
5863
5864 RETURN l_empty_locs;
5865 END getnumemptylocators;
5866
5867 --==============================================================
5868 -- API name : GetSOHeaderLineID
5869 -- Type : Private
5870 -- Function : Returns Sale Order Header ID or Line ID based
5871 -- on Move Order Line reference and Reference ID
5872 -- and header or line flag.
5873 -- ( Used for join condition in seed data )
5874
5875 FUNCTION getsoheaderlineid(
5876 p_line_id IN NUMBER
5877 , p_transaction_source_type_id IN NUMBER DEFAULT g_miss_num
5878 , p_reference IN VARCHAR2 DEFAULT g_miss_char
5879 , p_reference_id IN NUMBER DEFAULT g_miss_num
5880 , p_header_flag IN VARCHAR2 DEFAULT 'N'
5881 , p_line_flag IN VARCHAR2 DEFAULT 'N'
5882 )
5883 RETURN NUMBER IS
5884 l_header_id NUMBER := -1;
5885 l_line_id NUMBER := -1;
5886 l_return_val NUMBER := -1;
5887
5888 CURSOR l_oe_lines_curs IS
5889 SELECT header_id
5890 , line_id
5891 FROM oe_order_lines_all
5892 WHERE line_id = p_reference_id;
5893
5894 CURSOR l_wsh_delivery_details_curs IS
5895 SELECT source_header_id
5896 , source_line_id
5897 FROM wsh_delivery_details
5898 WHERE move_order_line_id = p_line_id;
5899 --Begin bug 4505225
5900 CURSOR l_oe_rcv_curs IS
5901 SELECT oe_order_header_id
5902 , oe_order_line_id
5903 FROM rcv_transactions
5904 WHERE transaction_id = p_reference_id
5905 AND routing_header_id = 3 ;
5906 --End bug 4505225
5907 BEGIN
5908 --inv_pp_debug.set_debug_mode(inv_pp_debug.g_debug_mode_yes);
5909 --inv_pp_debug.set_debug_pipe_name('htnguyen');
5910
5911 IF inv_pp_debug.is_debug_mode THEN
5912 inv_pp_debug.send_message_to_pipe('GetSOHeaderLineID(): ');
5913 inv_pp_debug.send_message_to_pipe('p_line_id: '|| p_line_id);
5914 inv_pp_debug.send_message_to_pipe('p_transaction_source_type_id: '|| p_transaction_source_type_id);
5915 inv_pp_debug.send_message_to_pipe('p_reference: '|| p_reference);
5916 inv_pp_debug.send_message_to_pipe('p_reference_id: '|| p_reference_id);
5917 inv_pp_debug.send_message_to_pipe('p_header_flag: '|| p_header_flag);
5918 inv_pp_debug.send_message_to_pipe('p_line_flag: '|| p_line_flag);
5919 END IF;
5920
5921 -- RMA = 12
5922 -- BUG 3205362 - For performance reasons cache the in parameters and results of this function
5923 IF (p_transaction_source_type_id = 12) and
5924 (p_reference = 'ORDER_LINE_ID') THEN
5925 If (p_reference_id <> nvl(g_gsohl_reference_id, -1)) OR
5926 (g_gsohl_header_id IS NULL) OR
5927 (g_gsohl_line_id IS NULL) THEN
5928 OPEN l_oe_lines_curs;
5929 FETCH l_oe_lines_curs INTO g_gsohl_header_id,g_gsohl_line_id;
5930 CLOSE l_oe_lines_curs;
5931 g_gsohl_reference_id := p_reference_id;
5932 g_gsohl_mo_line_id := NULL;
5933 END IF;
5934 --Begin bug 4505225
5935 ELSIF (p_transaction_source_type_id = 12) and (p_reference IS NULL) THEN
5936 If (p_reference_id <> nvl(g_gsohl_reference_id, -1)) OR
5937 (g_gsohl_header_id IS NULL) OR
5938 (g_gsohl_line_id IS NULL) THEN
5939 OPEN l_oe_rcv_curs;
5940 FETCH l_oe_rcv_curs INTO g_gsohl_header_id,g_gsohl_line_id;
5941 CLOSE l_oe_rcv_curs;
5942 g_gsohl_reference_id := p_reference_id;
5943 g_gsohl_mo_line_id := NULL;
5944 END IF;
5945 --End bug 4505225
5946 --Begin bug 5671641
5947 ELSIF (p_transaction_source_type_id = 2)
5948 And (p_reference = 'ORDER_LINE_ID_RSV') THEN
5949 OPEN l_oe_lines_curs;
5950 FETCH l_oe_lines_curs INTO g_gsohl_header_id,g_gsohl_line_id;
5951 CLOSE l_oe_lines_curs;
5952 --End bug 5671641
5953 ELSE
5954 If (p_line_id <> nvl(g_gsohl_mo_line_id, -1)) OR
5955 (g_gsohl_header_id IS NULL) OR
5956 (g_gsohl_line_id IS NULL) THEN
5957 -- Retrieve Sale information from shipping delivery detail
5958 OPEN l_wsh_delivery_details_curs;
5959 FETCH l_wsh_delivery_details_curs INTO g_gsohl_header_id,g_gsohl_line_id;
5960 CLOSE l_wsh_delivery_details_curs;
5961 g_gsohl_reference_id := NULL;
5962 g_gsohl_mo_line_id := p_line_id;
5963 END IF;
5964 END IF;
5965 l_line_id := g_gsohl_line_id;
5966 l_header_id := g_gsohl_header_id;
5967
5968 IF inv_pp_debug.is_debug_mode THEN
5969 inv_pp_debug.send_message_to_pipe('l_header_id : '|| l_header_id);
5970 inv_pp_debug.send_message_to_pipe('l_line_id : '|| l_line_id);
5971 END IF;
5972
5973 IF (p_header_flag = 'Y') THEN
5974 l_return_val := l_header_id;
5975 ELSIF (p_line_flag = 'Y') THEN
5976 l_return_val := l_line_id;
5977 END IF;
5978
5979 RETURN l_return_val;
5980 END getsoheaderlineid;
5981
5982 FUNCTION cart_lpn_contains_entire_del
5983 (p_lpn_id IN NUMBER,
5984 p_delivery_id IN NUMBER,
5985 p_business_flow_code IN NUMBER)
5986 RETURN VARCHAR2 IS
5987 l_ret VARCHAR2(1) := 'Y';
5988 multiple_lpns VARCHAR2(1) := 'X';
5989 l_delivery_id NUMBER := -1;
5990 BEGIN
5991 -- Bug 2631051 fix - this functions is intended to work only
5992 -- for the business flow of cartonization
5993 IF Nvl(p_business_flow_code,-1) <> 22 THEN
5994 l_ret := 'N';
5995 ELSE
5996 -- Bug 2631051 fix - if the delivery information is passed
5997 -- use that otherwise query for the delivery
5998 IF p_delivery_id IS NOT NULL THEN
5999 l_delivery_id := p_delivery_id;
6000 ELSE
6001
6002 BEGIN
6003 SELECT wda.delivery_id
6004 INTO l_delivery_id
6005 FROM
6006 wsh_delivery_assignments_v wda,
6007 wsh_delivery_details wdd,
6008 mtl_material_transactions_temp mmtt
6009 WHERE
6010 mmtt.cartonization_id = p_lpn_id
6011 AND mmtt.move_order_line_id = wdd.move_order_line_id -- kkoothan Removed the NVL as part of Bug Fix:2631051
6012 AND wdd.delivery_detail_id = wda.delivery_detail_id
6013 GROUP BY wda.delivery_id;
6014 EXCEPTION
6015 WHEN NO_DATA_FOUND THEN
6016 l_ret := 'N';
6017 WHEN TOO_MANY_ROWS THEN
6018 l_ret := 'N';
6019 END;
6020
6021
6022 IF ((l_delivery_id IS NULL)
6023 OR (l_delivery_id = -1)
6024 ) THEN
6025 l_ret := 'N';
6026 END IF;
6027 END IF;--else of IF p_delivery_id IS NOT NULL
6028
6029 IF (l_ret = 'Y') THEN
6030 BEGIN
6031 SELECT 'Y'
6032 INTO multiple_lpns
6033 FROM DUAL
6034 WHERE EXISTS( SELECT mmtt.transaction_temp_id
6035 FROM mtl_material_transactions_temp mmtt, wsh_delivery_details wdd, wsh_delivery_assignments_v wda
6036 WHERE NVL(mmtt.cartonization_id, -1) <> p_lpn_id
6037 AND mmtt.move_order_line_id = wdd.move_order_line_id
6038 AND wdd.delivery_detail_id = wda.delivery_detail_id
6039 AND wda.delivery_id = l_delivery_id);
6040 EXCEPTION
6041 WHEN NO_DATA_FOUND THEN
6042 multiple_lpns := 'N';
6043 WHEN OTHERS THEN
6044 multiple_lpns := 'Y';
6045 END;
6046
6047 IF multiple_lpns = 'Y' THEN
6048 l_ret := 'N';
6049 END IF;
6050 -- multiple lpns N => contains entire delivery
6051
6052 END IF;
6053 END IF;--else of IF Nvl(p_business_flow_code,-1) <> 22 THEN
6054
6055 RETURN l_ret;
6056 END cart_lpn_contains_entire_del;
6057
6058 FUNCTION getearliestreceiptdate(
6059 p_org_id IN NUMBER
6060 , p_item_id IN NUMBER
6061 , p_sub IN VARCHAR2
6062 , p_loc_id IN NUMBER DEFAULT NULL
6063 , p_lot IN VARCHAR2 DEFAULT NULL
6064 , p_rev IN VARCHAR2 DEFAULT NULL
6065 )
6066 RETURN DATE IS
6067 l_ret_date DATE := SYSDATE;
6068
6069 -- This cursor assumes that date_recieved is never null
6070 CURSOR rec_dates IS
6071 SELECT MIN(DECODE(orig_date_received, NULL, date_received, LEAST(date_received, orig_date_received)))
6072 FROM mtl_onhand_quantities_detail
6073 WHERE organization_id = p_org_id
6074 AND inventory_item_id = p_item_id
6075 AND subinventory_code = p_sub
6076 AND NVL(locator_id, -1) = NVL(p_loc_id, NVL(locator_id, -1))
6077 AND NVL(revision, '-1') = NVL(p_rev, NVL(revision, '-1'))
6078 AND NVL(lot_number, '-1') = NVL(p_lot, NVL(lot_number, '-1'));
6079 BEGIN
6080 OPEN rec_dates;
6081 FETCH rec_dates INTO l_ret_date;
6082
6083 IF (rec_dates%NOTFOUND
6084 OR l_ret_date IS NULL
6085 ) THEN
6086 l_ret_date := SYSDATE;
6087 END IF;
6088
6089 CLOSE rec_dates;
6090 RETURN l_ret_date;
6091 END getearliestreceiptdate;
6092
6093 FUNCTION is_wip_transaction(p_transaction_temp_id IN NUMBER)
6094 RETURN VARCHAR2 IS
6095 l_ret VARCHAR2(1);
6096 l_hdr_type NUMBER;
6097 BEGIN
6098 l_ret := 'N';
6099
6100 BEGIN
6101 SELECT mtrh.move_order_type
6102 INTO l_hdr_type
6103 FROM mtl_material_transactions_temp mmtt, mtl_txn_request_lines mtrl, mtl_txn_request_headers mtrh
6104 WHERE mmtt.transaction_temp_id = p_transaction_temp_id
6105 AND mtrl.line_id = mmtt.move_order_line_id
6106 AND mtrh.header_id = mtrl.header_id;
6107
6108 -- Bug 2666620: BackFlush MO Type Removed
6109 IF (l_hdr_type = inv_globals.g_move_order_mfg_pick) THEN
6110 RETURN 'Y';
6111 ELSE
6112 RETURN 'N';
6113 END IF;
6114 EXCEPTION
6115 WHEN NO_DATA_FOUND THEN
6116 l_ret := 'N';
6117 END;
6118
6119 SELECT 'Y'
6120 INTO l_ret
6121 FROM DUAL
6122 WHERE EXISTS( SELECT transaction_temp_id
6123 FROM mtl_material_transactions_temp
6124 WHERE transaction_temp_id = p_transaction_temp_id
6125 AND transaction_type_id IN (inv_globals.g_type_xfer_order_wip_issue, inv_globals.g_type_xfer_order_repl_subxfr));
6126
6127 RETURN l_ret;
6128 EXCEPTION
6129 WHEN OTHERS THEN
6130 RETURN 'N';
6131 END;
6132
6133 FUNCTION is_wip_move_order(p_header_id IN NUMBER)
6134 RETURN VARCHAR2 IS
6135 l_hdr_type NUMBER;
6136 BEGIN
6137 SELECT move_order_type
6138 INTO l_hdr_type
6139 FROM mtl_txn_request_headers mtrh
6140 WHERE mtrh.header_id = p_header_id;
6141
6142 -- Bug 2666620: BackFlush MO Type Removed
6143 IF (l_hdr_type = inv_globals.g_move_order_mfg_pick) THEN
6144 RETURN 'Y';
6145 ELSE
6146 RETURN 'N';
6147 END IF;
6148 EXCEPTION
6149 WHEN OTHERS THEN
6150 RETURN 'N';
6151 END is_wip_move_order;
6152
6153 --
6154 --
6155 FUNCTION GET_PROJECT_ATTRIBUTE(
6156 P_ATTRIBUTE_TYPE IN VARCHAR2 DEFAULT g_miss_char,
6157 P_INVENTORY_ORGANIZATION_ID IN NUMBER DEFAULT g_miss_num,
6158 P_PROJECT_ID IN NUMBER DEFAULT g_miss_num)
6159 RETURN VARCHAR2 IS
6160 l_project_name VARCHAR2(30);
6161 l_project_number VARCHAR2(30);
6162 l_planning_group VARCHAR2(30);
6163 l_rule_id NUMBER;
6164
6165
6166 BEGIN
6167 -- log_mesg('get_project_attribute','Start', '()');
6168 -- log_mesg('get_project_attribute','p_attribute_type :', p_attribute_type);
6169 -- log_mesg('get_project_attribute','p_inventory_organization_id :',p_inventory_organization_id );
6170 -- log_mesg('get_project_attribute','p_project_id :', p_project_id);
6171
6172 -- log_mesg('get_project_attribute','g_inventory_organization_id :',g_inventory_organization_id );
6173 -- log_mesg('get_project_attribute','g_project_id :', g_project_id);
6174
6175 IF (p_attribute_type = g_miss_char
6176 or p_inventory_organization_id = g_miss_num
6177 or p_project_id = g_miss_num
6178 or p_project_id is null
6179 or p_inventory_organization_id is null) THEN
6180
6181 RETURN NULL;
6182 END IF;
6183
6184 /*
6185 The query is executed once and the values of project_name, number and Group are stored in
6186 the local variable which are used for subsequent pre-suggested rows to be processed by the
6187 rules engine. These cached values are initialized for every rule.
6188
6189 */
6190 -- log_mesg('Get_project_attribute',' Before checking IsRuleCached :', g_GetProjAttr_IsRuleCached);
6191
6192
6193 IF (( NVL(g_GetProjAttr_IsRuleCached, 'N') = 'N') or
6194 ( g_inventory_organization_id <> p_inventory_organization_id) or
6195 ( g_project_id <> p_project_id )) then
6196
6197 g_GetProjAttr_IsRuleCached := 'Y';
6198 g_inventory_organization_id := p_inventory_organization_id;
6199 g_project_id := p_project_id;
6200
6201
6202 --- bug fix to improve performance
6203 IF P_ATTRIBUTE_TYPE = 'GROUP' THEN
6204 SELECT distinct planning_group
6205 INTO l_planning_group
6206 FROM pjm_project_parameters ppov
6207 WHERE project_id = nvl(p_project_id, 0)
6208 AND organization_id = p_inventory_organization_id;
6209
6210 ELSE
6211
6212 SELECT ppov.project_name, ppov.project_number, ppov.planning_group
6213 INTO l_project_name, l_project_number, l_planning_group
6214 FROM PJM_PROJECTS_ORG_V ppov
6215 WHERE ppov.project_id = nvl(p_project_id, 0)
6216 AND ppov.inventory_organization_id = p_inventory_organization_id;
6217 END IF;
6218
6219 g_project_name := l_project_name;
6220 g_project_number := l_project_number;
6221 g_planning_group := l_planning_group;
6222
6223 -- log_mesg('get_project_attribute','Inside the If ', ' ---' );
6224 -- log_mesg('get_project_attribute','l_project_name :', l_project_name);
6225 -- log_mesg('get_project_attribute','l_project_number :', l_project_number);
6226 -- log_mesg('get_project_attribute','l_planning_group :', l_planning_group);
6227
6228 END IF;
6229
6230 -- log_mesg('get_project_attribute','g_project_name :', g_project_name);
6231 -- log_mesg('get_project_attribute','g_project_number :', g_project_number);
6232 -- log_mesg('get_project_attribute','g_planning_group :', g_planning_group);
6233
6234 IF P_ATTRIBUTE_TYPE = 'NAME' THEN
6235 RETURN g_project_name;
6236 ELSIF P_ATTRIBUTE_TYPE = 'NUMBER' THEN
6237 RETURN g_project_number;
6238 ELSIF P_ATTRIBUTE_TYPE = 'GROUP' THEN
6239 RETURN g_planning_group;
6240 END IF;
6241
6242
6243 EXCEPTION
6244 WHEN OTHERS THEN
6245 RETURN NULL;
6246 END GET_PROJECT_ATTRIBUTE;
6247
6248 END wms_parameter_pvt;
[Home] [Help]