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