[Home] [Help]
PACKAGE BODY: APPS.WMS_RULE_9
Source
1 PACKAGE BODY WMS_RULE_9 AS
2
3 PROCEDURE open_curs (
4 p_cursor IN OUT NOCOPY WMS_RULE_PVT.cv_put_type,
5 p_organization_id IN NUMBER,
6 p_inventory_item_id IN NUMBER,
7 p_transaction_type_id IN NUMBER,
8 p_subinventory_code IN VARCHAR2,
9 p_locator_id IN NUMBER,
10 p_pp_transaction_temp_id IN NUMBER,
11 p_restrict_subs_code IN NUMBER,
12 p_restrict_locs_code IN NUMBER,
13 p_project_id IN NUMBER,
14 p_task_id IN NUMBER,
15 x_result OUT NOCOPY NUMBER) IS
16
17 g_organization_id NUMBER;
18 g_inventory_item_id NUMBER;
19 g_transaction_type_id NUMBER;
20 g_subinventory_code VARCHAR2(10);
21 g_locator_id NUMBER;
22 g_pp_transaction_temp_id NUMBER;
23 g_restrict_subs_code NUMBER;
24 g_restrict_locs_code NUMBER;
25 g_project_id NUMBER;
26 g_task_id NUMBER;
27
28 BEGIN
29 g_organization_id :=p_organization_id;
30 g_inventory_item_id := p_inventory_item_id;
31 g_transaction_type_id := p_transaction_type_id;
32 IF p_subinventory_code = '-9999' THEN
33 g_subinventory_code := NULL;
34 ELSE
35 g_subinventory_code := p_subinventory_code;
36 END IF;
37 IF p_locator_id = -9999 THEN
38 g_locator_id := NULL;
39 ELSE
40 g_locator_id := p_locator_id;
41 END IF;
42 g_pp_transaction_temp_id := p_pp_transaction_temp_id;
43 g_restrict_subs_code := p_restrict_subs_code;
44 g_restrict_locs_code := p_restrict_locs_code;
45 g_project_id := p_project_id;
46 g_task_id := p_task_id;
47
48 --if no restrictions
49 IF g_restrict_subs_code = 2 AND
50 g_restrict_locs_code = 2 THEN
51
52 If g_subinventory_code IS NULL Then
53 --if nothing passed, OPEN c_no_restrict_no_passed;
54
55 OPEN p_cursor FOR select base.SUBINVENTORY_CODE
56 ,base.LOCATOR_ID
57 ,base.PROJECT_ID
58 ,base.TASK_ID
59 from MTL_UNITS_OF_MEASURE bmuom
60 ,MTL_UNITS_OF_MEASURE muom
61 ,MTL_SYSTEM_ITEMS msi
62 ,WMS_TRX_DETAILS_TMP_V mptdtv
63 ,(
64 select msei.secondary_inventory_name subinventory_code --changed
65 ,mil.inventory_location_id locator_id
66 ,msei.organization_id organization_id --changed
67 ,mil.project_id project_id
68 ,mil.task_id task_id
69 ,g_inventory_item_id inventory_item_id
70 ,mil.location_current_units location_current_units
71 ,mil.inventory_item_id locator_inventory_item_id
72 ,mil.empty_flag empty_flag
73 ,mil.mixed_items_flag mixed_items_flag
74 ,mil.LAST_UPDATE_DATE
75 ,mil.LAST_UPDATED_BY
76 ,mil.CREATION_DATE
77 ,mil.CREATED_BY
78 ,mil.LAST_UPDATE_LOGIN
79 ,mil.DESCRIPTION
80 ,mil.DESCRIPTIVE_TEXT
81 ,mil.DISABLE_DATE
82 ,mil.INVENTORY_LOCATION_TYPE
83 ,mil.PICKING_ORDER
84 ,mil.PHYSICAL_LOCATION_CODE
85 ,mil.LOCATION_MAXIMUM_UNITS
86 ,mil.LOCATION_WEIGHT_UOM_CODE
87 ,mil.MAX_WEIGHT
88 ,mil.VOLUME_UOM_CODE
89 ,mil.MAX_CUBIC_AREA
90 ,mil.X_COORDINATE
91 ,mil.Y_COORDINATE
92 ,mil.Z_COORDINATE
93 ,mil.INVENTORY_ACCOUNT_ID
94 ,mil.SEGMENT1
95 ,mil.SEGMENT2
96 ,mil.SEGMENT3
97 ,mil.SEGMENT4
98 ,mil.SEGMENT5
99 ,mil.SEGMENT6
100 ,mil.SEGMENT7
101 ,mil.SEGMENT8
102 ,mil.SEGMENT9
103 ,mil.SEGMENT10
104 ,mil.SEGMENT11
105 ,mil.SEGMENT12
106 ,mil.SEGMENT13
107 ,mil.SEGMENT14
108 ,mil.SEGMENT15
109 ,mil.SEGMENT16
110 ,mil.SEGMENT17
111 ,mil.SEGMENT18
112 ,mil.SEGMENT19
113 ,mil.SEGMENT20
114 ,mil.SUMMARY_FLAG
115 ,mil.ENABLED_FLAG
116 ,mil.START_DATE_ACTIVE
117 ,mil.END_DATE_ACTIVE
118 ,mil.ATTRIBUTE_CATEGORY
119 ,mil.ATTRIBUTE1
120 ,mil.ATTRIBUTE2
121 ,mil.ATTRIBUTE3
122 ,mil.ATTRIBUTE4
123 ,mil.ATTRIBUTE5
124 ,mil.ATTRIBUTE6
125 ,mil.ATTRIBUTE7
126 ,mil.ATTRIBUTE8
127 ,mil.ATTRIBUTE9
128 ,mil.ATTRIBUTE10
129 ,mil.ATTRIBUTE11
130 ,mil.ATTRIBUTE12
131 ,mil.ATTRIBUTE13
132 ,mil.ATTRIBUTE14
133 ,mil.ATTRIBUTE15
134 ,mil.REQUEST_ID
135 ,mil.PROGRAM_APPLICATION_ID
136 ,mil.PROGRAM_ID
137 ,mil.PROGRAM_UPDATE_DATE
138 ,mil.PHYSICAL_LOCATION_ID
139 ,mil.PICK_UOM_CODE
140 ,mil.DIMENSION_UOM_CODE
141 ,mil.LENGTH
142 ,mil.WIDTH
143 ,mil.HEIGHT
144 ,mil.LOCATOR_STATUS
145 ,mil.STATUS_ID
146 ,mil.CURRENT_CUBIC_AREA
147 ,mil.AVAILABLE_CUBIC_AREA
148 ,mil.CURRENT_WEIGHT
149 ,mil.AVAILABLE_WEIGHT
150 ,mil.LOCATION_AVAILABLE_UNITS
151 ,mil.SUGGESTED_CUBIC_AREA
152 ,mil.SUGGESTED_WEIGHT
153 ,mil.LOCATION_SUGGESTED_UNITS
154 ,mil.rowid
155 from MTL_ITEM_LOCATIONS mil
156 ,MTL_SECONDARY_INVENTORIES msei
157 where mil.organization_id(+) = msei.organization_id
158 and mil.subinventory_code(+) = msei.secondary_inventory_name
159 and NVL(msei.disable_date, sysdate+1) > sysdate
160 and NVL(mil.disable_date, sysdate+1) > sysdate
161 )base
162 where base.ORGANIZATION_ID = g_organization_id
163 and base.INVENTORY_ITEM_ID = g_inventory_item_id
164 and (g_project_id = base.project_id OR base.project_id IS NULL)
165 and (g_task_id = base.task_id OR base.task_id IS NULL)
166 and mptdtv.PP_TRANSACTION_TEMP_ID = g_pp_transaction_temp_id
167 and msi.ORGANIZATION_ID = mptdtv.TO_ORGANIZATION_ID
168 and msi.INVENTORY_ITEM_ID = mptdtv.INVENTORY_ITEM_ID
169 and muom.UOM_CODE = mptdtv.TRANSACTION_UOM
170 and bmuom.UOM_CLASS = muom.UOM_CLASS
171 and bmuom.BASE_UOM_FLAG = 'Y'
172 and (
173 WMS_Parameter_PVT.GetMinimumAvailableUVWCapacity (
174 base.ORGANIZATION_ID
175 ,base.SUBINVENTORY_CODE
176 ,base.LOCATOR_ID
177 ,mptdtv.INVENTORY_ITEM_ID
178 ,msi.UNIT_VOLUME
179 ,msi.VOLUME_UOM_CODE
180 ,msi.UNIT_WEIGHT
181 ,msi.WEIGHT_UOM_CODE
182 ,msi.PRIMARY_UOM_CODE
183 ,mptdtv.TRANSACTION_UOM
184 ,bmuom.UOM_CODE
185 ,base.LOCATION_MAXIMUM_UNITS
186 ,base.LOCATION_CURRENT_UNITS
187 ,base.LOCATION_SUGGESTED_UNITS
188 ,base.LOCATION_AVAILABLE_UNITS
189 ,base.LOCATION_WEIGHT_UOM_CODE
190 ,base.MAX_WEIGHT
191 ,base.CURRENT_WEIGHT
192 ,base.SUGGESTED_WEIGHT
193 ,base.AVAILABLE_WEIGHT
194 ,base.VOLUME_UOM_CODE
195 ,base.MAX_CUBIC_AREA
196 ,base.CURRENT_CUBIC_AREA
197 ,base.SUGGESTED_CUBIC_AREA
198 ,base.AVAILABLE_CUBIC_AREA ) >= mptdtv.PRIMARY_QUANTITY
199 )
200 order by WMS_Parameter_PVT.GetItemOnHand(
201 base.ORGANIZATION_ID
202 ,mptdtv.INVENTORY_ITEM_ID
203 ,base.SUBINVENTORY_CODE
204 ,base.LOCATOR_ID
205 ,msi.PRIMARY_UOM_CODE
206 ,mptdtv.TRANSACTION_UOM ) desc
207 ,base.PROJECT_ID
208 ,base.TASK_ID
209 ;
210
211 Elsif g_locator_id IS NULL Then
212
213 --if only subinventory passed , OPEN c_no_restrict_sub_passed;
214
215 OPEN p_cursor FOR select base.SUBINVENTORY_CODE
216 ,base.LOCATOR_ID
217 ,base.PROJECT_ID
218 ,base.TASK_ID
219 from MTL_UNITS_OF_MEASURE bmuom
220 ,MTL_UNITS_OF_MEASURE muom
221 ,MTL_SYSTEM_ITEMS msi
222 ,WMS_TRX_DETAILS_TMP_V mptdtv
223 ,(
224 select msei.secondary_inventory_name subinventory_code --changed
225 ,mil.inventory_location_id locator_id
226 ,msei.organization_id organization_id --changed
227 ,mil.project_id project_id
228 ,mil.task_id task_id
229 ,g_inventory_item_id inventory_item_id
230 ,mil.location_current_units location_current_units
231 ,mil.inventory_item_id locator_inventory_item_id
232 ,mil.empty_flag empty_flag
233 ,mil.mixed_items_flag mixed_items_flag
234 ,mil.LAST_UPDATE_DATE
235 ,mil.LAST_UPDATED_BY
236 ,mil.CREATION_DATE
237 ,mil.CREATED_BY
238 ,mil.LAST_UPDATE_LOGIN
239 ,mil.DESCRIPTION
240 ,mil.DESCRIPTIVE_TEXT
241 ,mil.DISABLE_DATE
242 ,mil.INVENTORY_LOCATION_TYPE
243 ,mil.PICKING_ORDER
244 ,mil.PHYSICAL_LOCATION_CODE
245 ,mil.LOCATION_MAXIMUM_UNITS
246 ,mil.LOCATION_WEIGHT_UOM_CODE
247 ,mil.MAX_WEIGHT
248 ,mil.VOLUME_UOM_CODE
249 ,mil.MAX_CUBIC_AREA
250 ,mil.X_COORDINATE
251 ,mil.Y_COORDINATE
252 ,mil.Z_COORDINATE
253 ,mil.INVENTORY_ACCOUNT_ID
254 ,mil.SEGMENT1
255 ,mil.SEGMENT2
256 ,mil.SEGMENT3
257 ,mil.SEGMENT4
258 ,mil.SEGMENT5
259 ,mil.SEGMENT6
260 ,mil.SEGMENT7
261 ,mil.SEGMENT8
262 ,mil.SEGMENT9
263 ,mil.SEGMENT10
264 ,mil.SEGMENT11
265 ,mil.SEGMENT12
266 ,mil.SEGMENT13
267 ,mil.SEGMENT14
268 ,mil.SEGMENT15
269 ,mil.SEGMENT16
270 ,mil.SEGMENT17
271 ,mil.SEGMENT18
272 ,mil.SEGMENT19
273 ,mil.SEGMENT20
274 ,mil.SUMMARY_FLAG
275 ,mil.ENABLED_FLAG
276 ,mil.START_DATE_ACTIVE
277 ,mil.END_DATE_ACTIVE
278 ,mil.ATTRIBUTE_CATEGORY
279 ,mil.ATTRIBUTE1
280 ,mil.ATTRIBUTE2
281 ,mil.ATTRIBUTE3
282 ,mil.ATTRIBUTE4
283 ,mil.ATTRIBUTE5
284 ,mil.ATTRIBUTE6
285 ,mil.ATTRIBUTE7
286 ,mil.ATTRIBUTE8
287 ,mil.ATTRIBUTE9
288 ,mil.ATTRIBUTE10
289 ,mil.ATTRIBUTE11
290 ,mil.ATTRIBUTE12
291 ,mil.ATTRIBUTE13
292 ,mil.ATTRIBUTE14
293 ,mil.ATTRIBUTE15
294 ,mil.REQUEST_ID
295 ,mil.PROGRAM_APPLICATION_ID
296 ,mil.PROGRAM_ID
297 ,mil.PROGRAM_UPDATE_DATE
298 ,mil.PHYSICAL_LOCATION_ID
299 ,mil.PICK_UOM_CODE
300 ,mil.DIMENSION_UOM_CODE
301 ,mil.LENGTH
302 ,mil.WIDTH
303 ,mil.HEIGHT
304 ,mil.LOCATOR_STATUS
305 ,mil.STATUS_ID
306 ,mil.CURRENT_CUBIC_AREA
307 ,mil.AVAILABLE_CUBIC_AREA
308 ,mil.CURRENT_WEIGHT
309 ,mil.AVAILABLE_WEIGHT
310 ,mil.LOCATION_AVAILABLE_UNITS
311 ,mil.SUGGESTED_CUBIC_AREA
312 ,mil.SUGGESTED_WEIGHT
313 ,mil.LOCATION_SUGGESTED_UNITS
314 ,mil.rowid
315 from MTL_ITEM_LOCATIONS mil
316 ,MTL_SECONDARY_INVENTORIES msei
317 where mil.organization_id(+) = msei.organization_id
318 and mil.subinventory_code(+) = msei.secondary_inventory_name
319 and NVL(msei.disable_date, sysdate+1) > sysdate
320 and NVL(mil.disable_date, sysdate+1) > sysdate
321 )base
322 where base.ORGANIZATION_ID = g_organization_id
323 and base.INVENTORY_ITEM_ID = g_inventory_item_id
324 and (g_project_id = base.project_id OR base.project_id IS NULL)
325 and (g_task_id = base.task_id OR base.task_id IS NULL)
326 and mptdtv.PP_TRANSACTION_TEMP_ID = g_pp_transaction_temp_id
327 and msi.ORGANIZATION_ID = mptdtv.TO_ORGANIZATION_ID
328 and msi.INVENTORY_ITEM_ID = mptdtv.INVENTORY_ITEM_ID
329 and muom.UOM_CODE = mptdtv.TRANSACTION_UOM
330 and bmuom.UOM_CLASS = muom.UOM_CLASS
331 and bmuom.BASE_UOM_FLAG = 'Y'
332 and (
333 WMS_Parameter_PVT.GetMinimumAvailableUVWCapacity (
334 base.ORGANIZATION_ID
335 ,base.SUBINVENTORY_CODE
336 ,base.LOCATOR_ID
337 ,mptdtv.INVENTORY_ITEM_ID
338 ,msi.UNIT_VOLUME
339 ,msi.VOLUME_UOM_CODE
340 ,msi.UNIT_WEIGHT
341 ,msi.WEIGHT_UOM_CODE
342 ,msi.PRIMARY_UOM_CODE
343 ,mptdtv.TRANSACTION_UOM
344 ,bmuom.UOM_CODE
345 ,base.LOCATION_MAXIMUM_UNITS
346 ,base.LOCATION_CURRENT_UNITS
347 ,base.LOCATION_SUGGESTED_UNITS
348 ,base.LOCATION_AVAILABLE_UNITS
349 ,base.LOCATION_WEIGHT_UOM_CODE
350 ,base.MAX_WEIGHT
351 ,base.CURRENT_WEIGHT
352 ,base.SUGGESTED_WEIGHT
353 ,base.AVAILABLE_WEIGHT
354 ,base.VOLUME_UOM_CODE
355 ,base.MAX_CUBIC_AREA
356 ,base.CURRENT_CUBIC_AREA
357 ,base.SUGGESTED_CUBIC_AREA
358 ,base.AVAILABLE_CUBIC_AREA ) >= mptdtv.PRIMARY_QUANTITY
359 )
360 and base.subinventory_code = g_subinventory_code
361 order by WMS_Parameter_PVT.GetItemOnHand(
362 base.ORGANIZATION_ID
363 ,mptdtv.INVENTORY_ITEM_ID
364 ,base.SUBINVENTORY_CODE
365 ,base.LOCATOR_ID
366 ,msi.PRIMARY_UOM_CODE
367 ,mptdtv.TRANSACTION_UOM ) desc
368 ,base.PROJECT_ID
369 ,base.TASK_ID
370 ;
371
372 Else
373 --if subinventory and locator passed, OPEN c_no_restrict_loc_passed;
374 OPEN p_cursor FOR select base.SUBINVENTORY_CODE
375 ,base.LOCATOR_ID
376 ,base.PROJECT_ID
377 ,base.TASK_ID
378 from MTL_UNITS_OF_MEASURE bmuom
379 ,MTL_UNITS_OF_MEASURE muom
380 ,MTL_SYSTEM_ITEMS msi
381 ,WMS_TRX_DETAILS_TMP_V mptdtv
382 ,(
383 select msei.secondary_inventory_name subinventory_code --changed
384 ,mil.inventory_location_id locator_id
385 ,msei.organization_id organization_id --changed
386 ,mil.project_id project_id
387 ,mil.task_id task_id
388 ,g_inventory_item_id inventory_item_id
389 ,mil.location_current_units location_current_units
390 ,mil.inventory_item_id locator_inventory_item_id
391 ,mil.empty_flag empty_flag
392 ,mil.mixed_items_flag mixed_items_flag
393 ,mil.LAST_UPDATE_DATE
394 ,mil.LAST_UPDATED_BY
395 ,mil.CREATION_DATE
396 ,mil.CREATED_BY
397 ,mil.LAST_UPDATE_LOGIN
398 ,mil.DESCRIPTION
399 ,mil.DESCRIPTIVE_TEXT
400 ,mil.DISABLE_DATE
401 ,mil.INVENTORY_LOCATION_TYPE
402 ,mil.PICKING_ORDER
403 ,mil.PHYSICAL_LOCATION_CODE
404 ,mil.LOCATION_MAXIMUM_UNITS
405 ,mil.LOCATION_WEIGHT_UOM_CODE
406 ,mil.MAX_WEIGHT
407 ,mil.VOLUME_UOM_CODE
408 ,mil.MAX_CUBIC_AREA
409 ,mil.X_COORDINATE
410 ,mil.Y_COORDINATE
411 ,mil.Z_COORDINATE
412 ,mil.INVENTORY_ACCOUNT_ID
413 ,mil.SEGMENT1
414 ,mil.SEGMENT2
415 ,mil.SEGMENT3
416 ,mil.SEGMENT4
417 ,mil.SEGMENT5
418 ,mil.SEGMENT6
419 ,mil.SEGMENT7
420 ,mil.SEGMENT8
421 ,mil.SEGMENT9
422 ,mil.SEGMENT10
423 ,mil.SEGMENT11
424 ,mil.SEGMENT12
425 ,mil.SEGMENT13
426 ,mil.SEGMENT14
427 ,mil.SEGMENT15
428 ,mil.SEGMENT16
429 ,mil.SEGMENT17
430 ,mil.SEGMENT18
431 ,mil.SEGMENT19
432 ,mil.SEGMENT20
433 ,mil.SUMMARY_FLAG
434 ,mil.ENABLED_FLAG
435 ,mil.START_DATE_ACTIVE
436 ,mil.END_DATE_ACTIVE
437 ,mil.ATTRIBUTE_CATEGORY
438 ,mil.ATTRIBUTE1
439 ,mil.ATTRIBUTE2
440 ,mil.ATTRIBUTE3
441 ,mil.ATTRIBUTE4
442 ,mil.ATTRIBUTE5
443 ,mil.ATTRIBUTE6
444 ,mil.ATTRIBUTE7
445 ,mil.ATTRIBUTE8
446 ,mil.ATTRIBUTE9
447 ,mil.ATTRIBUTE10
448 ,mil.ATTRIBUTE11
449 ,mil.ATTRIBUTE12
450 ,mil.ATTRIBUTE13
451 ,mil.ATTRIBUTE14
452 ,mil.ATTRIBUTE15
453 ,mil.REQUEST_ID
454 ,mil.PROGRAM_APPLICATION_ID
455 ,mil.PROGRAM_ID
456 ,mil.PROGRAM_UPDATE_DATE
457 ,mil.PHYSICAL_LOCATION_ID
458 ,mil.PICK_UOM_CODE
459 ,mil.DIMENSION_UOM_CODE
460 ,mil.LENGTH
461 ,mil.WIDTH
462 ,mil.HEIGHT
463 ,mil.LOCATOR_STATUS
464 ,mil.STATUS_ID
465 ,mil.CURRENT_CUBIC_AREA
466 ,mil.AVAILABLE_CUBIC_AREA
467 ,mil.CURRENT_WEIGHT
468 ,mil.AVAILABLE_WEIGHT
469 ,mil.LOCATION_AVAILABLE_UNITS
470 ,mil.SUGGESTED_CUBIC_AREA
471 ,mil.SUGGESTED_WEIGHT
472 ,mil.LOCATION_SUGGESTED_UNITS
473 ,mil.rowid
474 from MTL_ITEM_LOCATIONS mil
475 ,MTL_SECONDARY_INVENTORIES msei
476 where mil.organization_id(+) = msei.organization_id
477 and mil.subinventory_code(+) = msei.secondary_inventory_name
478 and NVL(msei.disable_date, sysdate+1) > sysdate
479 and NVL(mil.disable_date, sysdate+1) > sysdate
480 )base
481 where base.ORGANIZATION_ID = g_organization_id
482 and base.INVENTORY_ITEM_ID = g_inventory_item_id
483 and (g_project_id = base.project_id OR base.project_id IS NULL)
484 and (g_task_id = base.task_id OR base.task_id IS NULL)
485 and mptdtv.PP_TRANSACTION_TEMP_ID = g_pp_transaction_temp_id
486 and msi.ORGANIZATION_ID = mptdtv.TO_ORGANIZATION_ID
487 and msi.INVENTORY_ITEM_ID = mptdtv.INVENTORY_ITEM_ID
488 and muom.UOM_CODE = mptdtv.TRANSACTION_UOM
489 and bmuom.UOM_CLASS = muom.UOM_CLASS
490 and bmuom.BASE_UOM_FLAG = 'Y'
491 and (
492 WMS_Parameter_PVT.GetMinimumAvailableUVWCapacity (
493 base.ORGANIZATION_ID
494 ,base.SUBINVENTORY_CODE
495 ,base.LOCATOR_ID
496 ,mptdtv.INVENTORY_ITEM_ID
497 ,msi.UNIT_VOLUME
498 ,msi.VOLUME_UOM_CODE
499 ,msi.UNIT_WEIGHT
500 ,msi.WEIGHT_UOM_CODE
501 ,msi.PRIMARY_UOM_CODE
502 ,mptdtv.TRANSACTION_UOM
503 ,bmuom.UOM_CODE
504 ,base.LOCATION_MAXIMUM_UNITS
505 ,base.LOCATION_CURRENT_UNITS
506 ,base.LOCATION_SUGGESTED_UNITS
507 ,base.LOCATION_AVAILABLE_UNITS
508 ,base.LOCATION_WEIGHT_UOM_CODE
509 ,base.MAX_WEIGHT
510 ,base.CURRENT_WEIGHT
511 ,base.SUGGESTED_WEIGHT
512 ,base.AVAILABLE_WEIGHT
513 ,base.VOLUME_UOM_CODE
514 ,base.MAX_CUBIC_AREA
515 ,base.CURRENT_CUBIC_AREA
516 ,base.SUGGESTED_CUBIC_AREA
517 ,base.AVAILABLE_CUBIC_AREA ) >= mptdtv.PRIMARY_QUANTITY
518 )
519 and base.subinventory_code = g_subinventory_code
520 and base.locator_id = g_locator_id
521 order by WMS_Parameter_PVT.GetItemOnHand(
522 base.ORGANIZATION_ID
523 ,mptdtv.INVENTORY_ITEM_ID
524 ,base.SUBINVENTORY_CODE
525 ,base.LOCATOR_ID
526 ,msi.PRIMARY_UOM_CODE
527 ,mptdtv.TRANSACTION_UOM ) desc
528 ,base.PROJECT_ID
529 ,base.TASK_ID
530 ;
531 End If;
532 ELSIF g_restrict_locs_code = 2 THEN
533 If g_subinventory_code IS NULL Then
534 --if nothing passed, OPEN c_sub_restrict_no_passed;
535 OPEN p_cursor FOR select base.SUBINVENTORY_CODE
536 ,base.LOCATOR_ID
537 ,base.PROJECT_ID
538 ,base.TASK_ID
539 from MTL_UNITS_OF_MEASURE bmuom
540 ,MTL_UNITS_OF_MEASURE muom
541 ,MTL_SYSTEM_ITEMS msi
542 ,WMS_TRX_DETAILS_TMP_V mptdtv
543 ,(
544 select msei.secondary_inventory_name subinventory_code -- changed
545 ,mil.inventory_location_id locator_id
546 ,msei.organization_id organization_id -- changed
547 ,mil.project_id project_id
548 ,mil.task_id task_id
549 ,g_inventory_item_id inventory_item_id
550 ,mil.location_current_units location_current_units
551 ,mil.inventory_item_id locator_inventory_item_id
552 ,mil.empty_flag empty_flag
553 ,mil.mixed_items_flag mixed_items_flag
554 ,mil.LAST_UPDATE_DATE
555 ,mil.LAST_UPDATED_BY
556 ,mil.CREATION_DATE
557 ,mil.CREATED_BY
558 ,mil.LAST_UPDATE_LOGIN
559 ,mil.DESCRIPTION
560 ,mil.DESCRIPTIVE_TEXT
561 ,mil.DISABLE_DATE
562 ,mil.INVENTORY_LOCATION_TYPE
563 ,mil.PICKING_ORDER
564 ,mil.PHYSICAL_LOCATION_CODE
565 ,mil.LOCATION_MAXIMUM_UNITS
566 ,mil.LOCATION_WEIGHT_UOM_CODE
567 ,mil.MAX_WEIGHT
568 ,mil.VOLUME_UOM_CODE
569 ,mil.MAX_CUBIC_AREA
570 ,mil.X_COORDINATE
571 ,mil.Y_COORDINATE
572 ,mil.Z_COORDINATE
573 ,mil.INVENTORY_ACCOUNT_ID
574 ,mil.SEGMENT1
575 ,mil.SEGMENT2
576 ,mil.SEGMENT3
577 ,mil.SEGMENT4
578 ,mil.SEGMENT5
579 ,mil.SEGMENT6
580 ,mil.SEGMENT7
581 ,mil.SEGMENT8
582 ,mil.SEGMENT9
583 ,mil.SEGMENT10
584 ,mil.SEGMENT11
585 ,mil.SEGMENT12
586 ,mil.SEGMENT13
587 ,mil.SEGMENT14
588 ,mil.SEGMENT15
589 ,mil.SEGMENT16
590 ,mil.SEGMENT17
591 ,mil.SEGMENT18
592 ,mil.SEGMENT19
593 ,mil.SEGMENT20
594 ,mil.SUMMARY_FLAG
595 ,mil.ENABLED_FLAG
596 ,mil.START_DATE_ACTIVE
597 ,mil.END_DATE_ACTIVE
598 ,mil.ATTRIBUTE_CATEGORY
599 ,mil.ATTRIBUTE1
600 ,mil.ATTRIBUTE2
601 ,mil.ATTRIBUTE3
602 ,mil.ATTRIBUTE4
603 ,mil.ATTRIBUTE5
604 ,mil.ATTRIBUTE6
605 ,mil.ATTRIBUTE7
606 ,mil.ATTRIBUTE8
607 ,mil.ATTRIBUTE9
608 ,mil.ATTRIBUTE10
609 ,mil.ATTRIBUTE11
610 ,mil.ATTRIBUTE12
611 ,mil.ATTRIBUTE13
612 ,mil.ATTRIBUTE14
613 ,mil.ATTRIBUTE15
614 ,mil.REQUEST_ID
615 ,mil.PROGRAM_APPLICATION_ID
616 ,mil.PROGRAM_ID
617 ,mil.PROGRAM_UPDATE_DATE
618 ,mil.PHYSICAL_LOCATION_ID
619 ,mil.PICK_UOM_CODE
620 ,mil.DIMENSION_UOM_CODE
621 ,mil.LENGTH
622 ,mil.WIDTH
623 ,mil.HEIGHT
624 ,mil.LOCATOR_STATUS
625 ,mil.STATUS_ID
626 ,mil.CURRENT_CUBIC_AREA
627 ,mil.AVAILABLE_CUBIC_AREA
628 ,mil.CURRENT_WEIGHT
629 ,mil.AVAILABLE_WEIGHT
630 ,mil.LOCATION_AVAILABLE_UNITS
631 ,mil.SUGGESTED_CUBIC_AREA
632 ,mil.SUGGESTED_WEIGHT
633 ,mil.LOCATION_SUGGESTED_UNITS
634 ,mil.rowid
635 from MTL_ITEM_LOCATIONS mil
636 ,MTL_SECONDARY_INVENTORIES msei
637 ,MTL_ITEM_SUB_INVENTORIES misi
638 where mil.organization_id(+) = msei.organization_id
639 and mil.subinventory_code(+) = msei.secondary_inventory_name
640 and NVL(msei.disable_date, sysdate+1) > sysdate
641 and NVL(mil.disable_date, sysdate+1) > sysdate
642 and mil.organization_id = misi.organization_id
643 and mil.subinventory_code = misi.secondary_inventory
644 and misi.inventory_item_id = g_inventory_item_id
645 )base
646 where base.ORGANIZATION_ID = g_organization_id
647 and base.INVENTORY_ITEM_ID = g_inventory_item_id
648 and (g_project_id = base.project_id OR base.project_id IS NULL)
649 and (g_task_id = base.task_id OR base.task_id IS NULL)
650 and mptdtv.PP_TRANSACTION_TEMP_ID = g_pp_transaction_temp_id
651 and msi.ORGANIZATION_ID = mptdtv.TO_ORGANIZATION_ID
652 and msi.INVENTORY_ITEM_ID = mptdtv.INVENTORY_ITEM_ID
653 and muom.UOM_CODE = mptdtv.TRANSACTION_UOM
654 and bmuom.UOM_CLASS = muom.UOM_CLASS
655 and bmuom.BASE_UOM_FLAG = 'Y'
656 and (
657 WMS_Parameter_PVT.GetMinimumAvailableUVWCapacity (
658 base.ORGANIZATION_ID
659 ,base.SUBINVENTORY_CODE
660 ,base.LOCATOR_ID
661 ,mptdtv.INVENTORY_ITEM_ID
662 ,msi.UNIT_VOLUME
663 ,msi.VOLUME_UOM_CODE
664 ,msi.UNIT_WEIGHT
665 ,msi.WEIGHT_UOM_CODE
666 ,msi.PRIMARY_UOM_CODE
667 ,mptdtv.TRANSACTION_UOM
668 ,bmuom.UOM_CODE
669 ,base.LOCATION_MAXIMUM_UNITS
670 ,base.LOCATION_CURRENT_UNITS
671 ,base.LOCATION_SUGGESTED_UNITS
672 ,base.LOCATION_AVAILABLE_UNITS
673 ,base.LOCATION_WEIGHT_UOM_CODE
674 ,base.MAX_WEIGHT
675 ,base.CURRENT_WEIGHT
676 ,base.SUGGESTED_WEIGHT
677 ,base.AVAILABLE_WEIGHT
678 ,base.VOLUME_UOM_CODE
679 ,base.MAX_CUBIC_AREA
680 ,base.CURRENT_CUBIC_AREA
681 ,base.SUGGESTED_CUBIC_AREA
682 ,base.AVAILABLE_CUBIC_AREA ) >= mptdtv.PRIMARY_QUANTITY
683 )
684 order by WMS_Parameter_PVT.GetItemOnHand(
685 base.ORGANIZATION_ID
686 ,mptdtv.INVENTORY_ITEM_ID
687 ,base.SUBINVENTORY_CODE
688 ,base.LOCATOR_ID
689 ,msi.PRIMARY_UOM_CODE
690 ,mptdtv.TRANSACTION_UOM ) desc
691 ,base.PROJECT_ID
692 ,base.TASK_ID
693 ;
694 Elsif g_locator_id IS NULL Then
695 --if only subinventory passed, OPEN c_sub_restrict_sub_passed;
696 OPEN p_cursor FOR select base.SUBINVENTORY_CODE
697 ,base.LOCATOR_ID
698 ,base.PROJECT_ID
699 ,base.TASK_ID
700 from MTL_UNITS_OF_MEASURE bmuom
701 ,MTL_UNITS_OF_MEASURE muom
702 ,MTL_SYSTEM_ITEMS msi
703 ,WMS_TRX_DETAILS_TMP_V mptdtv
704 ,(
705 select msei.secondary_inventory_name subinventory_code -- changed
706 ,mil.inventory_location_id locator_id
707 ,msei.organization_id organization_id -- changed
708 ,mil.project_id project_id
709 ,mil.task_id task_id
710 ,g_inventory_item_id inventory_item_id
711 ,mil.location_current_units location_current_units
712 ,mil.inventory_item_id locator_inventory_item_id
713 ,mil.empty_flag empty_flag
714 ,mil.mixed_items_flag mixed_items_flag
715 ,mil.LAST_UPDATE_DATE
716 ,mil.LAST_UPDATED_BY
717 ,mil.CREATION_DATE
718 ,mil.CREATED_BY
719 ,mil.LAST_UPDATE_LOGIN
720 ,mil.DESCRIPTION
721 ,mil.DESCRIPTIVE_TEXT
722 ,mil.DISABLE_DATE
723 ,mil.INVENTORY_LOCATION_TYPE
724 ,mil.PICKING_ORDER
725 ,mil.PHYSICAL_LOCATION_CODE
726 ,mil.LOCATION_MAXIMUM_UNITS
727 ,mil.LOCATION_WEIGHT_UOM_CODE
728 ,mil.MAX_WEIGHT
729 ,mil.VOLUME_UOM_CODE
730 ,mil.MAX_CUBIC_AREA
731 ,mil.X_COORDINATE
732 ,mil.Y_COORDINATE
733 ,mil.Z_COORDINATE
734 ,mil.INVENTORY_ACCOUNT_ID
735 ,mil.SEGMENT1
736 ,mil.SEGMENT2
737 ,mil.SEGMENT3
738 ,mil.SEGMENT4
739 ,mil.SEGMENT5
740 ,mil.SEGMENT6
741 ,mil.SEGMENT7
742 ,mil.SEGMENT8
743 ,mil.SEGMENT9
744 ,mil.SEGMENT10
745 ,mil.SEGMENT11
746 ,mil.SEGMENT12
747 ,mil.SEGMENT13
748 ,mil.SEGMENT14
749 ,mil.SEGMENT15
750 ,mil.SEGMENT16
751 ,mil.SEGMENT17
752 ,mil.SEGMENT18
753 ,mil.SEGMENT19
754 ,mil.SEGMENT20
755 ,mil.SUMMARY_FLAG
756 ,mil.ENABLED_FLAG
757 ,mil.START_DATE_ACTIVE
758 ,mil.END_DATE_ACTIVE
759 ,mil.ATTRIBUTE_CATEGORY
760 ,mil.ATTRIBUTE1
761 ,mil.ATTRIBUTE2
762 ,mil.ATTRIBUTE3
763 ,mil.ATTRIBUTE4
764 ,mil.ATTRIBUTE5
765 ,mil.ATTRIBUTE6
766 ,mil.ATTRIBUTE7
767 ,mil.ATTRIBUTE8
768 ,mil.ATTRIBUTE9
769 ,mil.ATTRIBUTE10
770 ,mil.ATTRIBUTE11
771 ,mil.ATTRIBUTE12
772 ,mil.ATTRIBUTE13
773 ,mil.ATTRIBUTE14
774 ,mil.ATTRIBUTE15
775 ,mil.REQUEST_ID
776 ,mil.PROGRAM_APPLICATION_ID
777 ,mil.PROGRAM_ID
778 ,mil.PROGRAM_UPDATE_DATE
779 ,mil.PHYSICAL_LOCATION_ID
780 ,mil.PICK_UOM_CODE
781 ,mil.DIMENSION_UOM_CODE
782 ,mil.LENGTH
783 ,mil.WIDTH
784 ,mil.HEIGHT
785 ,mil.LOCATOR_STATUS
786 ,mil.STATUS_ID
787 ,mil.CURRENT_CUBIC_AREA
788 ,mil.AVAILABLE_CUBIC_AREA
789 ,mil.CURRENT_WEIGHT
790 ,mil.AVAILABLE_WEIGHT
791 ,mil.LOCATION_AVAILABLE_UNITS
792 ,mil.SUGGESTED_CUBIC_AREA
793 ,mil.SUGGESTED_WEIGHT
794 ,mil.LOCATION_SUGGESTED_UNITS
795 ,mil.rowid
796 from MTL_ITEM_LOCATIONS mil
797 ,MTL_SECONDARY_INVENTORIES msei
798 ,MTL_ITEM_SUB_INVENTORIES misi
799 where mil.organization_id(+) = msei.organization_id
800 and mil.subinventory_code(+) = msei.secondary_inventory_name
801 and NVL(msei.disable_date, sysdate+1) > sysdate
802 and NVL(mil.disable_date, sysdate+1) > sysdate
803 and mil.organization_id = misi.organization_id
804 and mil.subinventory_code = misi.secondary_inventory
805 and misi.inventory_item_id = g_inventory_item_id
806 )base
807 where base.ORGANIZATION_ID = g_organization_id
808 and base.INVENTORY_ITEM_ID = g_inventory_item_id
809 and (g_project_id = base.project_id OR base.project_id IS NULL)
810 and (g_task_id = base.task_id OR base.task_id IS NULL)
811 and mptdtv.PP_TRANSACTION_TEMP_ID = g_pp_transaction_temp_id
812 and msi.ORGANIZATION_ID = mptdtv.TO_ORGANIZATION_ID
813 and msi.INVENTORY_ITEM_ID = mptdtv.INVENTORY_ITEM_ID
814 and muom.UOM_CODE = mptdtv.TRANSACTION_UOM
815 and bmuom.UOM_CLASS = muom.UOM_CLASS
816 and bmuom.BASE_UOM_FLAG = 'Y'
817 and (
818 WMS_Parameter_PVT.GetMinimumAvailableUVWCapacity (
819 base.ORGANIZATION_ID
820 ,base.SUBINVENTORY_CODE
821 ,base.LOCATOR_ID
822 ,mptdtv.INVENTORY_ITEM_ID
823 ,msi.UNIT_VOLUME
824 ,msi.VOLUME_UOM_CODE
825 ,msi.UNIT_WEIGHT
826 ,msi.WEIGHT_UOM_CODE
827 ,msi.PRIMARY_UOM_CODE
828 ,mptdtv.TRANSACTION_UOM
829 ,bmuom.UOM_CODE
830 ,base.LOCATION_MAXIMUM_UNITS
831 ,base.LOCATION_CURRENT_UNITS
832 ,base.LOCATION_SUGGESTED_UNITS
833 ,base.LOCATION_AVAILABLE_UNITS
834 ,base.LOCATION_WEIGHT_UOM_CODE
835 ,base.MAX_WEIGHT
836 ,base.CURRENT_WEIGHT
837 ,base.SUGGESTED_WEIGHT
838 ,base.AVAILABLE_WEIGHT
839 ,base.VOLUME_UOM_CODE
840 ,base.MAX_CUBIC_AREA
841 ,base.CURRENT_CUBIC_AREA
842 ,base.SUGGESTED_CUBIC_AREA
843 ,base.AVAILABLE_CUBIC_AREA ) >= mptdtv.PRIMARY_QUANTITY
844 )
845 and base.subinventory_code = g_subinventory_code
846 order by WMS_Parameter_PVT.GetItemOnHand(
847 base.ORGANIZATION_ID
848 ,mptdtv.INVENTORY_ITEM_ID
849 ,base.SUBINVENTORY_CODE
850 ,base.LOCATOR_ID
851 ,msi.PRIMARY_UOM_CODE
852 ,mptdtv.TRANSACTION_UOM ) desc
853 ,base.PROJECT_ID
854 ,base.TASK_ID
855 ;
856
857 Else
858 --if subinventory and locator passed, OPEN c_sub_restrict_loc_passed;
859 OPEN p_cursor FOR select base.SUBINVENTORY_CODE
860 ,base.LOCATOR_ID
861 ,base.PROJECT_ID
862 ,base.TASK_ID
863 from MTL_UNITS_OF_MEASURE bmuom
864 ,MTL_UNITS_OF_MEASURE muom
865 ,MTL_SYSTEM_ITEMS msi
866 ,WMS_TRX_DETAILS_TMP_V mptdtv
867 ,(
868 select msei.secondary_inventory_name subinventory_code -- changed
869 ,mil.inventory_location_id locator_id
870 ,msei.organization_id organization_id -- changed
871 ,mil.project_id project_id
872 ,mil.task_id task_id
873 ,g_inventory_item_id inventory_item_id
874 ,mil.location_current_units location_current_units
875 ,mil.inventory_item_id locator_inventory_item_id
876 ,mil.empty_flag empty_flag
877 ,mil.mixed_items_flag mixed_items_flag
878 ,mil.LAST_UPDATE_DATE
879 ,mil.LAST_UPDATED_BY
880 ,mil.CREATION_DATE
881 ,mil.CREATED_BY
882 ,mil.LAST_UPDATE_LOGIN
883 ,mil.DESCRIPTION
884 ,mil.DESCRIPTIVE_TEXT
885 ,mil.DISABLE_DATE
886 ,mil.INVENTORY_LOCATION_TYPE
887 ,mil.PICKING_ORDER
888 ,mil.PHYSICAL_LOCATION_CODE
889 ,mil.LOCATION_MAXIMUM_UNITS
890 ,mil.LOCATION_WEIGHT_UOM_CODE
891 ,mil.MAX_WEIGHT
892 ,mil.VOLUME_UOM_CODE
893 ,mil.MAX_CUBIC_AREA
894 ,mil.X_COORDINATE
895 ,mil.Y_COORDINATE
896 ,mil.Z_COORDINATE
897 ,mil.INVENTORY_ACCOUNT_ID
898 ,mil.SEGMENT1
899 ,mil.SEGMENT2
900 ,mil.SEGMENT3
901 ,mil.SEGMENT4
902 ,mil.SEGMENT5
903 ,mil.SEGMENT6
904 ,mil.SEGMENT7
905 ,mil.SEGMENT8
906 ,mil.SEGMENT9
907 ,mil.SEGMENT10
908 ,mil.SEGMENT11
909 ,mil.SEGMENT12
910 ,mil.SEGMENT13
911 ,mil.SEGMENT14
912 ,mil.SEGMENT15
913 ,mil.SEGMENT16
914 ,mil.SEGMENT17
915 ,mil.SEGMENT18
916 ,mil.SEGMENT19
917 ,mil.SEGMENT20
918 ,mil.SUMMARY_FLAG
919 ,mil.ENABLED_FLAG
920 ,mil.START_DATE_ACTIVE
921 ,mil.END_DATE_ACTIVE
922 ,mil.ATTRIBUTE_CATEGORY
923 ,mil.ATTRIBUTE1
924 ,mil.ATTRIBUTE2
925 ,mil.ATTRIBUTE3
926 ,mil.ATTRIBUTE4
927 ,mil.ATTRIBUTE5
928 ,mil.ATTRIBUTE6
929 ,mil.ATTRIBUTE7
930 ,mil.ATTRIBUTE8
931 ,mil.ATTRIBUTE9
932 ,mil.ATTRIBUTE10
933 ,mil.ATTRIBUTE11
934 ,mil.ATTRIBUTE12
935 ,mil.ATTRIBUTE13
936 ,mil.ATTRIBUTE14
937 ,mil.ATTRIBUTE15
938 ,mil.REQUEST_ID
939 ,mil.PROGRAM_APPLICATION_ID
940 ,mil.PROGRAM_ID
941 ,mil.PROGRAM_UPDATE_DATE
942 ,mil.PHYSICAL_LOCATION_ID
943 ,mil.PICK_UOM_CODE
944 ,mil.DIMENSION_UOM_CODE
945 ,mil.LENGTH
946 ,mil.WIDTH
947 ,mil.HEIGHT
948 ,mil.LOCATOR_STATUS
949 ,mil.STATUS_ID
950 ,mil.CURRENT_CUBIC_AREA
951 ,mil.AVAILABLE_CUBIC_AREA
952 ,mil.CURRENT_WEIGHT
953 ,mil.AVAILABLE_WEIGHT
954 ,mil.LOCATION_AVAILABLE_UNITS
955 ,mil.SUGGESTED_CUBIC_AREA
956 ,mil.SUGGESTED_WEIGHT
957 ,mil.LOCATION_SUGGESTED_UNITS
958 ,mil.rowid
959 from MTL_ITEM_LOCATIONS mil
960 ,MTL_SECONDARY_INVENTORIES msei
961 ,MTL_ITEM_SUB_INVENTORIES misi
962 where mil.organization_id(+) = msei.organization_id
963 and mil.subinventory_code(+) = msei.secondary_inventory_name
964 and NVL(msei.disable_date, sysdate+1) > sysdate
965 and NVL(mil.disable_date, sysdate+1) > sysdate
966 and mil.organization_id = misi.organization_id
967 and mil.subinventory_code = misi.secondary_inventory
968 and misi.inventory_item_id = g_inventory_item_id
969 )base
970 where base.ORGANIZATION_ID = g_organization_id
971 and base.INVENTORY_ITEM_ID = g_inventory_item_id
972 and (g_project_id = base.project_id OR base.project_id IS NULL)
973 and (g_task_id = base.task_id OR base.task_id IS NULL)
974 and mptdtv.PP_TRANSACTION_TEMP_ID = g_pp_transaction_temp_id
975 and msi.ORGANIZATION_ID = mptdtv.TO_ORGANIZATION_ID
976 and msi.INVENTORY_ITEM_ID = mptdtv.INVENTORY_ITEM_ID
977 and muom.UOM_CODE = mptdtv.TRANSACTION_UOM
978 and bmuom.UOM_CLASS = muom.UOM_CLASS
979 and bmuom.BASE_UOM_FLAG = 'Y'
980 and (
981 WMS_Parameter_PVT.GetMinimumAvailableUVWCapacity (
982 base.ORGANIZATION_ID
983 ,base.SUBINVENTORY_CODE
984 ,base.LOCATOR_ID
985 ,mptdtv.INVENTORY_ITEM_ID
986 ,msi.UNIT_VOLUME
987 ,msi.VOLUME_UOM_CODE
988 ,msi.UNIT_WEIGHT
989 ,msi.WEIGHT_UOM_CODE
990 ,msi.PRIMARY_UOM_CODE
991 ,mptdtv.TRANSACTION_UOM
992 ,bmuom.UOM_CODE
993 ,base.LOCATION_MAXIMUM_UNITS
994 ,base.LOCATION_CURRENT_UNITS
995 ,base.LOCATION_SUGGESTED_UNITS
996 ,base.LOCATION_AVAILABLE_UNITS
997 ,base.LOCATION_WEIGHT_UOM_CODE
998 ,base.MAX_WEIGHT
999 ,base.CURRENT_WEIGHT
1000 ,base.SUGGESTED_WEIGHT
1001 ,base.AVAILABLE_WEIGHT
1002 ,base.VOLUME_UOM_CODE
1003 ,base.MAX_CUBIC_AREA
1004 ,base.CURRENT_CUBIC_AREA
1005 ,base.SUGGESTED_CUBIC_AREA
1006 ,base.AVAILABLE_CUBIC_AREA ) >= mptdtv.PRIMARY_QUANTITY
1007 )
1008 and base.subinventory_code = g_subinventory_code
1009 and base.locator_id = g_locator_id
1010 order by WMS_Parameter_PVT.GetItemOnHand(
1011 base.ORGANIZATION_ID
1012 ,mptdtv.INVENTORY_ITEM_ID
1013 ,base.SUBINVENTORY_CODE
1014 ,base.LOCATOR_ID
1015 ,msi.PRIMARY_UOM_CODE
1016 ,mptdtv.TRANSACTION_UOM ) desc
1017 ,base.PROJECT_ID
1018 ,base.TASK_ID
1019 ;
1020 End If;
1021 ELSE
1022 If g_subinventory_code IS NULL Then
1023 --if nothing passed, OPEN c_loc_restrict_no_passed;
1024 OPEN p_cursor FOR select base.SUBINVENTORY_CODE
1025 ,base.LOCATOR_ID
1026 ,base.PROJECT_ID
1027 ,base.TASK_ID
1028 from MTL_UNITS_OF_MEASURE bmuom
1029 ,MTL_UNITS_OF_MEASURE muom
1030 ,MTL_SYSTEM_ITEMS msi
1031 ,WMS_TRX_DETAILS_TMP_V mptdtv
1032 ,(
1033 select mil.subinventory_code subinventory_code
1034 ,mil.inventory_location_id locator_id
1035 ,mil.organization_id organization_id
1036 ,mil.project_id project_id
1037 ,mil.task_id task_id
1038 ,g_inventory_item_id inventory_item_id
1039 ,mil.location_current_units location_current_units
1040 ,mil.inventory_item_id locator_inventory_item_id
1041 ,mil.empty_flag empty_flag
1042 ,mil.mixed_items_flag mixed_items_flag
1043 ,mil.LAST_UPDATE_DATE
1044 ,mil.LAST_UPDATED_BY
1045 ,mil.CREATION_DATE
1046 ,mil.CREATED_BY
1047 ,mil.LAST_UPDATE_LOGIN
1048 ,mil.DESCRIPTION
1049 ,mil.DESCRIPTIVE_TEXT
1050 ,mil.DISABLE_DATE
1051 ,mil.INVENTORY_LOCATION_TYPE
1052 ,mil.PICKING_ORDER
1053 ,mil.PHYSICAL_LOCATION_CODE
1054 ,mil.LOCATION_MAXIMUM_UNITS
1055 ,mil.LOCATION_WEIGHT_UOM_CODE
1056 ,mil.MAX_WEIGHT
1057 ,mil.VOLUME_UOM_CODE
1058 ,mil.MAX_CUBIC_AREA
1059 ,mil.X_COORDINATE
1060 ,mil.Y_COORDINATE
1061 ,mil.Z_COORDINATE
1062 ,mil.INVENTORY_ACCOUNT_ID
1063 ,mil.SEGMENT1
1064 ,mil.SEGMENT2
1065 ,mil.SEGMENT3
1066 ,mil.SEGMENT4
1067 ,mil.SEGMENT5
1068 ,mil.SEGMENT6
1069 ,mil.SEGMENT7
1070 ,mil.SEGMENT8
1071 ,mil.SEGMENT9
1072 ,mil.SEGMENT10
1073 ,mil.SEGMENT11
1074 ,mil.SEGMENT12
1075 ,mil.SEGMENT13
1076 ,mil.SEGMENT14
1077 ,mil.SEGMENT15
1078 ,mil.SEGMENT16
1079 ,mil.SEGMENT17
1080 ,mil.SEGMENT18
1081 ,mil.SEGMENT19
1082 ,mil.SEGMENT20
1083 ,mil.SUMMARY_FLAG
1084 ,mil.ENABLED_FLAG
1085 ,mil.START_DATE_ACTIVE
1086 ,mil.END_DATE_ACTIVE
1087 ,mil.ATTRIBUTE_CATEGORY
1088 ,mil.ATTRIBUTE1
1089 ,mil.ATTRIBUTE2
1090 ,mil.ATTRIBUTE3
1091 ,mil.ATTRIBUTE4
1092 ,mil.ATTRIBUTE5
1093 ,mil.ATTRIBUTE6
1094 ,mil.ATTRIBUTE7
1095 ,mil.ATTRIBUTE8
1096 ,mil.ATTRIBUTE9
1097 ,mil.ATTRIBUTE10
1098 ,mil.ATTRIBUTE11
1099 ,mil.ATTRIBUTE12
1100 ,mil.ATTRIBUTE13
1101 ,mil.ATTRIBUTE14
1102 ,mil.ATTRIBUTE15
1103 ,mil.REQUEST_ID
1104 ,mil.PROGRAM_APPLICATION_ID
1105 ,mil.PROGRAM_ID
1106 ,mil.PROGRAM_UPDATE_DATE
1107 ,mil.PHYSICAL_LOCATION_ID
1108 ,mil.PICK_UOM_CODE
1109 ,mil.DIMENSION_UOM_CODE
1110 ,mil.LENGTH
1111 ,mil.WIDTH
1112 ,mil.HEIGHT
1113 ,mil.LOCATOR_STATUS
1114 ,mil.STATUS_ID
1115 ,mil.CURRENT_CUBIC_AREA
1116 ,mil.AVAILABLE_CUBIC_AREA
1117 ,mil.CURRENT_WEIGHT
1118 ,mil.AVAILABLE_WEIGHT
1119 ,mil.LOCATION_AVAILABLE_UNITS
1120 ,mil.SUGGESTED_CUBIC_AREA
1121 ,mil.SUGGESTED_WEIGHT
1122 ,mil.LOCATION_SUGGESTED_UNITS
1123 ,mil.rowid
1124 from MTL_ITEM_LOCATIONS mil
1125 ,MTL_SECONDARY_INVENTORIES msei
1126 ,MTL_ITEM_SUB_INVENTORIES misi
1127 ,MTL_SECONDARY_LOCATORS msl
1128 where mil.organization_id = msei.organization_id
1129 and mil.subinventory_code = msei.secondary_inventory_name
1130 and NVL(msei.disable_date, sysdate+1) > sysdate
1131 and NVL(mil.disable_date, sysdate+1) > sysdate
1132 and mil.organization_id = misi.organization_id
1133 and mil.subinventory_code = misi.secondary_inventory
1134 and misi.inventory_item_id = g_inventory_item_id
1135 and mil.organization_id = msl.organization_id
1136 and mil.inventory_location_id = msl.secondary_locator
1137 and msl.inventory_item_Id = g_inventory_item_id
1138 )base
1139 where base.ORGANIZATION_ID = g_organization_id
1140 and base.INVENTORY_ITEM_ID = g_inventory_item_id
1141 and (g_project_id = base.project_id OR base.project_id IS NULL)
1142 and (g_task_id = base.task_id OR base.task_id IS NULL)
1143 and mptdtv.PP_TRANSACTION_TEMP_ID = g_pp_transaction_temp_id
1144 and msi.ORGANIZATION_ID = mptdtv.TO_ORGANIZATION_ID
1145 and msi.INVENTORY_ITEM_ID = mptdtv.INVENTORY_ITEM_ID
1146 and muom.UOM_CODE = mptdtv.TRANSACTION_UOM
1147 and bmuom.UOM_CLASS = muom.UOM_CLASS
1148 and bmuom.BASE_UOM_FLAG = 'Y'
1149 and (
1150 WMS_Parameter_PVT.GetMinimumAvailableUVWCapacity (
1151 base.ORGANIZATION_ID
1152 ,base.SUBINVENTORY_CODE
1153 ,base.LOCATOR_ID
1154 ,mptdtv.INVENTORY_ITEM_ID
1155 ,msi.UNIT_VOLUME
1156 ,msi.VOLUME_UOM_CODE
1157 ,msi.UNIT_WEIGHT
1158 ,msi.WEIGHT_UOM_CODE
1159 ,msi.PRIMARY_UOM_CODE
1160 ,mptdtv.TRANSACTION_UOM
1161 ,bmuom.UOM_CODE
1162 ,base.LOCATION_MAXIMUM_UNITS
1163 ,base.LOCATION_CURRENT_UNITS
1164 ,base.LOCATION_SUGGESTED_UNITS
1165 ,base.LOCATION_AVAILABLE_UNITS
1166 ,base.LOCATION_WEIGHT_UOM_CODE
1167 ,base.MAX_WEIGHT
1168 ,base.CURRENT_WEIGHT
1169 ,base.SUGGESTED_WEIGHT
1170 ,base.AVAILABLE_WEIGHT
1171 ,base.VOLUME_UOM_CODE
1172 ,base.MAX_CUBIC_AREA
1173 ,base.CURRENT_CUBIC_AREA
1174 ,base.SUGGESTED_CUBIC_AREA
1175 ,base.AVAILABLE_CUBIC_AREA ) >= mptdtv.PRIMARY_QUANTITY
1176 )
1177 order by WMS_Parameter_PVT.GetItemOnHand(
1178 base.ORGANIZATION_ID
1179 ,mptdtv.INVENTORY_ITEM_ID
1180 ,base.SUBINVENTORY_CODE
1181 ,base.LOCATOR_ID
1182 ,msi.PRIMARY_UOM_CODE
1183 ,mptdtv.TRANSACTION_UOM ) desc
1184 ,base.PROJECT_ID
1185 ,base.TASK_ID
1186 ;
1187
1188 Elsif g_locator_id IS NULL Then
1189 --if only subinventory passed,OPEN c_loc_restrict_sub_passed;
1190 OPEN p_cursor FOR select base.SUBINVENTORY_CODE
1191 ,base.LOCATOR_ID
1192 ,base.PROJECT_ID
1193 ,base.TASK_ID
1194 from MTL_UNITS_OF_MEASURE bmuom
1195 ,MTL_UNITS_OF_MEASURE muom
1196 ,MTL_SYSTEM_ITEMS msi
1197 ,WMS_TRX_DETAILS_TMP_V mptdtv
1198 ,(
1199 select mil.subinventory_code subinventory_code
1200 ,mil.inventory_location_id locator_id
1201 ,mil.organization_id organization_id
1202 ,mil.project_id project_id
1203 ,mil.task_id task_id
1204 ,g_inventory_item_id inventory_item_id
1205 ,mil.location_current_units location_current_units
1206 ,mil.inventory_item_id locator_inventory_item_id
1207 ,mil.empty_flag empty_flag
1208 ,mil.mixed_items_flag mixed_items_flag
1209 ,mil.LAST_UPDATE_DATE
1210 ,mil.LAST_UPDATED_BY
1211 ,mil.CREATION_DATE
1212 ,mil.CREATED_BY
1213 ,mil.LAST_UPDATE_LOGIN
1214 ,mil.DESCRIPTION
1215 ,mil.DESCRIPTIVE_TEXT
1216 ,mil.DISABLE_DATE
1217 ,mil.INVENTORY_LOCATION_TYPE
1218 ,mil.PICKING_ORDER
1219 ,mil.PHYSICAL_LOCATION_CODE
1220 ,mil.LOCATION_MAXIMUM_UNITS
1221 ,mil.LOCATION_WEIGHT_UOM_CODE
1222 ,mil.MAX_WEIGHT
1223 ,mil.VOLUME_UOM_CODE
1224 ,mil.MAX_CUBIC_AREA
1225 ,mil.X_COORDINATE
1226 ,mil.Y_COORDINATE
1227 ,mil.Z_COORDINATE
1228 ,mil.INVENTORY_ACCOUNT_ID
1229 ,mil.SEGMENT1
1230 ,mil.SEGMENT2
1231 ,mil.SEGMENT3
1232 ,mil.SEGMENT4
1233 ,mil.SEGMENT5
1234 ,mil.SEGMENT6
1235 ,mil.SEGMENT7
1236 ,mil.SEGMENT8
1237 ,mil.SEGMENT9
1238 ,mil.SEGMENT10
1239 ,mil.SEGMENT11
1240 ,mil.SEGMENT12
1241 ,mil.SEGMENT13
1242 ,mil.SEGMENT14
1243 ,mil.SEGMENT15
1244 ,mil.SEGMENT16
1245 ,mil.SEGMENT17
1246 ,mil.SEGMENT18
1247 ,mil.SEGMENT19
1248 ,mil.SEGMENT20
1249 ,mil.SUMMARY_FLAG
1250 ,mil.ENABLED_FLAG
1251 ,mil.START_DATE_ACTIVE
1252 ,mil.END_DATE_ACTIVE
1253 ,mil.ATTRIBUTE_CATEGORY
1254 ,mil.ATTRIBUTE1
1255 ,mil.ATTRIBUTE2
1256 ,mil.ATTRIBUTE3
1257 ,mil.ATTRIBUTE4
1258 ,mil.ATTRIBUTE5
1259 ,mil.ATTRIBUTE6
1260 ,mil.ATTRIBUTE7
1261 ,mil.ATTRIBUTE8
1262 ,mil.ATTRIBUTE9
1263 ,mil.ATTRIBUTE10
1264 ,mil.ATTRIBUTE11
1265 ,mil.ATTRIBUTE12
1266 ,mil.ATTRIBUTE13
1267 ,mil.ATTRIBUTE14
1268 ,mil.ATTRIBUTE15
1269 ,mil.REQUEST_ID
1270 ,mil.PROGRAM_APPLICATION_ID
1271 ,mil.PROGRAM_ID
1272 ,mil.PROGRAM_UPDATE_DATE
1273 ,mil.PHYSICAL_LOCATION_ID
1274 ,mil.PICK_UOM_CODE
1275 ,mil.DIMENSION_UOM_CODE
1276 ,mil.LENGTH
1277 ,mil.WIDTH
1278 ,mil.HEIGHT
1279 ,mil.LOCATOR_STATUS
1280 ,mil.STATUS_ID
1281 ,mil.CURRENT_CUBIC_AREA
1282 ,mil.AVAILABLE_CUBIC_AREA
1283 ,mil.CURRENT_WEIGHT
1284 ,mil.AVAILABLE_WEIGHT
1285 ,mil.LOCATION_AVAILABLE_UNITS
1286 ,mil.SUGGESTED_CUBIC_AREA
1287 ,mil.SUGGESTED_WEIGHT
1288 ,mil.LOCATION_SUGGESTED_UNITS
1289 ,mil.rowid
1290 from MTL_ITEM_LOCATIONS mil
1291 ,MTL_SECONDARY_INVENTORIES msei
1292 ,MTL_ITEM_SUB_INVENTORIES misi
1293 ,MTL_SECONDARY_LOCATORS msl
1294 where mil.organization_id = msei.organization_id
1295 and mil.subinventory_code = msei.secondary_inventory_name
1296 and NVL(msei.disable_date, sysdate+1) > sysdate
1297 and NVL(mil.disable_date, sysdate+1) > sysdate
1298 and mil.organization_id = misi.organization_id
1299 and mil.subinventory_code = misi.secondary_inventory
1300 and misi.inventory_item_id = g_inventory_item_id
1301 and mil.organization_id = msl.organization_id
1302 and mil.inventory_location_id = msl.secondary_locator
1303 and msl.inventory_item_Id = g_inventory_item_id
1304 )base
1305 where base.ORGANIZATION_ID = g_organization_id
1306 and base.INVENTORY_ITEM_ID = g_inventory_item_id
1307 and (g_project_id = base.project_id OR base.project_id IS NULL)
1308 and (g_task_id = base.task_id OR base.task_id IS NULL)
1309 and mptdtv.PP_TRANSACTION_TEMP_ID = g_pp_transaction_temp_id
1310 and msi.ORGANIZATION_ID = mptdtv.TO_ORGANIZATION_ID
1311 and msi.INVENTORY_ITEM_ID = mptdtv.INVENTORY_ITEM_ID
1312 and muom.UOM_CODE = mptdtv.TRANSACTION_UOM
1313 and bmuom.UOM_CLASS = muom.UOM_CLASS
1314 and bmuom.BASE_UOM_FLAG = 'Y'
1315 and (
1316 WMS_Parameter_PVT.GetMinimumAvailableUVWCapacity (
1317 base.ORGANIZATION_ID
1318 ,base.SUBINVENTORY_CODE
1319 ,base.LOCATOR_ID
1320 ,mptdtv.INVENTORY_ITEM_ID
1321 ,msi.UNIT_VOLUME
1322 ,msi.VOLUME_UOM_CODE
1323 ,msi.UNIT_WEIGHT
1324 ,msi.WEIGHT_UOM_CODE
1325 ,msi.PRIMARY_UOM_CODE
1326 ,mptdtv.TRANSACTION_UOM
1327 ,bmuom.UOM_CODE
1328 ,base.LOCATION_MAXIMUM_UNITS
1329 ,base.LOCATION_CURRENT_UNITS
1330 ,base.LOCATION_SUGGESTED_UNITS
1331 ,base.LOCATION_AVAILABLE_UNITS
1332 ,base.LOCATION_WEIGHT_UOM_CODE
1333 ,base.MAX_WEIGHT
1334 ,base.CURRENT_WEIGHT
1335 ,base.SUGGESTED_WEIGHT
1336 ,base.AVAILABLE_WEIGHT
1337 ,base.VOLUME_UOM_CODE
1338 ,base.MAX_CUBIC_AREA
1339 ,base.CURRENT_CUBIC_AREA
1340 ,base.SUGGESTED_CUBIC_AREA
1341 ,base.AVAILABLE_CUBIC_AREA ) >= mptdtv.PRIMARY_QUANTITY
1342 )
1343 and base.subinventory_code = g_subinventory_code
1344 order by WMS_Parameter_PVT.GetItemOnHand(
1345 base.ORGANIZATION_ID
1346 ,mptdtv.INVENTORY_ITEM_ID
1347 ,base.SUBINVENTORY_CODE
1348 ,base.LOCATOR_ID
1349 ,msi.PRIMARY_UOM_CODE
1350 ,mptdtv.TRANSACTION_UOM ) desc
1351 ,base.PROJECT_ID
1352 ,base.TASK_ID
1353 ;
1354
1355 Else
1356 --if subinventory and locator passed, OPEN c_loc_restrict_loc_passed;
1357 OPEN p_cursor FOR select base.SUBINVENTORY_CODE
1358 ,base.LOCATOR_ID
1359 ,base.PROJECT_ID
1360 ,base.TASK_ID
1361 from MTL_UNITS_OF_MEASURE bmuom
1362 ,MTL_UNITS_OF_MEASURE muom
1363 ,MTL_SYSTEM_ITEMS msi
1364 ,WMS_TRX_DETAILS_TMP_V mptdtv
1365 ,(
1366 select mil.subinventory_code subinventory_code
1367 ,mil.inventory_location_id locator_id
1368 ,mil.organization_id organization_id
1369 ,mil.project_id project_id
1370 ,mil.task_id task_id
1371 ,g_inventory_item_id inventory_item_id
1372 ,mil.location_current_units location_current_units
1373 ,mil.inventory_item_id locator_inventory_item_id
1374 ,mil.empty_flag empty_flag
1375 ,mil.mixed_items_flag mixed_items_flag
1376 ,mil.LAST_UPDATE_DATE
1377 ,mil.LAST_UPDATED_BY
1378 ,mil.CREATION_DATE
1379 ,mil.CREATED_BY
1380 ,mil.LAST_UPDATE_LOGIN
1381 ,mil.DESCRIPTION
1382 ,mil.DESCRIPTIVE_TEXT
1383 ,mil.DISABLE_DATE
1384 ,mil.INVENTORY_LOCATION_TYPE
1385 ,mil.PICKING_ORDER
1386 ,mil.PHYSICAL_LOCATION_CODE
1387 ,mil.LOCATION_MAXIMUM_UNITS
1388 ,mil.LOCATION_WEIGHT_UOM_CODE
1389 ,mil.MAX_WEIGHT
1390 ,mil.VOLUME_UOM_CODE
1391 ,mil.MAX_CUBIC_AREA
1392 ,mil.X_COORDINATE
1393 ,mil.Y_COORDINATE
1394 ,mil.Z_COORDINATE
1395 ,mil.INVENTORY_ACCOUNT_ID
1396 ,mil.SEGMENT1
1397 ,mil.SEGMENT2
1398 ,mil.SEGMENT3
1399 ,mil.SEGMENT4
1400 ,mil.SEGMENT5
1401 ,mil.SEGMENT6
1402 ,mil.SEGMENT7
1403 ,mil.SEGMENT8
1404 ,mil.SEGMENT9
1405 ,mil.SEGMENT10
1406 ,mil.SEGMENT11
1407 ,mil.SEGMENT12
1408 ,mil.SEGMENT13
1409 ,mil.SEGMENT14
1410 ,mil.SEGMENT15
1411 ,mil.SEGMENT16
1412 ,mil.SEGMENT17
1413 ,mil.SEGMENT18
1414 ,mil.SEGMENT19
1415 ,mil.SEGMENT20
1416 ,mil.SUMMARY_FLAG
1417 ,mil.ENABLED_FLAG
1418 ,mil.START_DATE_ACTIVE
1419 ,mil.END_DATE_ACTIVE
1420 ,mil.ATTRIBUTE_CATEGORY
1421 ,mil.ATTRIBUTE1
1422 ,mil.ATTRIBUTE2
1423 ,mil.ATTRIBUTE3
1424 ,mil.ATTRIBUTE4
1425 ,mil.ATTRIBUTE5
1426 ,mil.ATTRIBUTE6
1427 ,mil.ATTRIBUTE7
1428 ,mil.ATTRIBUTE8
1429 ,mil.ATTRIBUTE9
1430 ,mil.ATTRIBUTE10
1431 ,mil.ATTRIBUTE11
1432 ,mil.ATTRIBUTE12
1433 ,mil.ATTRIBUTE13
1434 ,mil.ATTRIBUTE14
1435 ,mil.ATTRIBUTE15
1436 ,mil.REQUEST_ID
1437 ,mil.PROGRAM_APPLICATION_ID
1438 ,mil.PROGRAM_ID
1439 ,mil.PROGRAM_UPDATE_DATE
1440 ,mil.PHYSICAL_LOCATION_ID
1441 ,mil.PICK_UOM_CODE
1442 ,mil.DIMENSION_UOM_CODE
1443 ,mil.LENGTH
1444 ,mil.WIDTH
1445 ,mil.HEIGHT
1446 ,mil.LOCATOR_STATUS
1447 ,mil.STATUS_ID
1448 ,mil.CURRENT_CUBIC_AREA
1449 ,mil.AVAILABLE_CUBIC_AREA
1450 ,mil.CURRENT_WEIGHT
1451 ,mil.AVAILABLE_WEIGHT
1452 ,mil.LOCATION_AVAILABLE_UNITS
1453 ,mil.SUGGESTED_CUBIC_AREA
1454 ,mil.SUGGESTED_WEIGHT
1455 ,mil.LOCATION_SUGGESTED_UNITS
1456 ,mil.rowid
1457 from MTL_ITEM_LOCATIONS mil
1458 ,MTL_SECONDARY_INVENTORIES msei
1459 ,MTL_ITEM_SUB_INVENTORIES misi
1460 ,MTL_SECONDARY_LOCATORS msl
1461 where mil.organization_id = msei.organization_id
1462 and mil.subinventory_code = msei.secondary_inventory_name
1463 and NVL(msei.disable_date, sysdate+1) > sysdate
1464 and NVL(mil.disable_date, sysdate+1) > sysdate
1465 and mil.organization_id = misi.organization_id
1466 and mil.subinventory_code = misi.secondary_inventory
1467 and misi.inventory_item_id = g_inventory_item_id
1468 and mil.organization_id = msl.organization_id
1469 and mil.inventory_location_id = msl.secondary_locator
1470 and msl.inventory_item_Id = g_inventory_item_id
1471 )base
1472 where base.ORGANIZATION_ID = g_organization_id
1473 and base.INVENTORY_ITEM_ID = g_inventory_item_id
1474 and (g_project_id = base.project_id OR base.project_id IS NULL)
1475 and (g_task_id = base.task_id OR base.task_id IS NULL)
1476 and mptdtv.PP_TRANSACTION_TEMP_ID = g_pp_transaction_temp_id
1477 and msi.ORGANIZATION_ID = mptdtv.TO_ORGANIZATION_ID
1478 and msi.INVENTORY_ITEM_ID = mptdtv.INVENTORY_ITEM_ID
1479 and muom.UOM_CODE = mptdtv.TRANSACTION_UOM
1480 and bmuom.UOM_CLASS = muom.UOM_CLASS
1481 and bmuom.BASE_UOM_FLAG = 'Y'
1482 and (
1483 WMS_Parameter_PVT.GetMinimumAvailableUVWCapacity (
1484 base.ORGANIZATION_ID
1485 ,base.SUBINVENTORY_CODE
1486 ,base.LOCATOR_ID
1487 ,mptdtv.INVENTORY_ITEM_ID
1488 ,msi.UNIT_VOLUME
1489 ,msi.VOLUME_UOM_CODE
1490 ,msi.UNIT_WEIGHT
1491 ,msi.WEIGHT_UOM_CODE
1492 ,msi.PRIMARY_UOM_CODE
1493 ,mptdtv.TRANSACTION_UOM
1494 ,bmuom.UOM_CODE
1495 ,base.LOCATION_MAXIMUM_UNITS
1496 ,base.LOCATION_CURRENT_UNITS
1497 ,base.LOCATION_SUGGESTED_UNITS
1498 ,base.LOCATION_AVAILABLE_UNITS
1499 ,base.LOCATION_WEIGHT_UOM_CODE
1500 ,base.MAX_WEIGHT
1501 ,base.CURRENT_WEIGHT
1502 ,base.SUGGESTED_WEIGHT
1503 ,base.AVAILABLE_WEIGHT
1504 ,base.VOLUME_UOM_CODE
1505 ,base.MAX_CUBIC_AREA
1506 ,base.CURRENT_CUBIC_AREA
1507 ,base.SUGGESTED_CUBIC_AREA
1508 ,base.AVAILABLE_CUBIC_AREA ) >= mptdtv.PRIMARY_QUANTITY
1509 )
1510 and base.subinventory_code = g_subinventory_code
1511 and base.locator_id = g_locator_id
1512 order by WMS_Parameter_PVT.GetItemOnHand(
1513 base.ORGANIZATION_ID
1514 ,mptdtv.INVENTORY_ITEM_ID
1515 ,base.SUBINVENTORY_CODE
1516 ,base.LOCATOR_ID
1517 ,msi.PRIMARY_UOM_CODE
1518 ,mptdtv.TRANSACTION_UOM ) desc
1519 ,base.PROJECT_ID
1520 ,base.TASK_ID
1521 ;
1522 End If;
1523 END IF;
1524
1525 x_result :=1;
1526
1527 END open_curs;
1528
1529 PROCEDURE fetch_one_row(
1530 p_cursor IN WMS_RULE_PVT.cv_put_type,
1531 x_subinventory_code OUT NOCOPY VARCHAR2,
1532 x_locator_id OUT NOCOPY NUMBER,
1533 x_project_id OUT NOCOPY NUMBER,
1534 x_task_id OUT NOCOPY NUMBER,
1535 x_return_status OUT NOCOPY NUMBER) IS
1536
1537
1538 BEGIN
1539 if p_cursor%ISOPEN then
1540 FETCH p_cursor INTO
1541 x_subinventory_code, x_locator_id, x_project_id, x_task_id;
1542 IF p_cursor%FOUND THEN
1543 x_return_status := 1;
1544 ELSE
1545 x_return_status := 0;
1546 END IF;
1547 else
1548 x_return_status := 0;
1549 end if;
1550
1551 END fetch_one_row;
1552
1553 PROCEDURE close_curs( p_cursor IN WMS_RULE_PVT.cv_put_type) IS
1554 BEGIN
1555 if p_cursor%ISOPEN then
1556 CLOSE p_cursor;
1557 end if;
1558 END close_curs;
1559
1560 END WMS_RULE_9;