[Home] [Help]
PACKAGE BODY: APPS.WMS_RULE_10
Source
1 PACKAGE BODY WMS_RULE_10 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_SYSTEM_ITEMS msi
60 ,WMS_TRX_DETAILS_TMP_V mptdtv
61 ,(
62 select msei.secondary_inventory_name subinventory_code --changed
63 ,mil.inventory_location_id locator_id
64 ,msei.organization_id organization_id --changed
65 ,mil.project_id project_id
66 ,mil.task_id task_id
67 ,g_inventory_item_id inventory_item_id
68 ,mil.location_current_units location_current_units
69 ,mil.inventory_item_id locator_inventory_item_id
70 ,mil.empty_flag empty_flag
71 ,mil.mixed_items_flag mixed_items_flag
72 ,mil.LAST_UPDATE_DATE
73 ,mil.LAST_UPDATED_BY
74 ,mil.CREATION_DATE
75 ,mil.CREATED_BY
76 ,mil.LAST_UPDATE_LOGIN
77 ,mil.DESCRIPTION
78 ,mil.DESCRIPTIVE_TEXT
79 ,mil.DISABLE_DATE
80 ,mil.INVENTORY_LOCATION_TYPE
81 ,mil.PICKING_ORDER
82 ,mil.PHYSICAL_LOCATION_CODE
83 ,mil.LOCATION_MAXIMUM_UNITS
84 ,mil.LOCATION_WEIGHT_UOM_CODE
85 ,mil.MAX_WEIGHT
86 ,mil.VOLUME_UOM_CODE
87 ,mil.MAX_CUBIC_AREA
88 ,mil.X_COORDINATE
89 ,mil.Y_COORDINATE
90 ,mil.Z_COORDINATE
91 ,mil.INVENTORY_ACCOUNT_ID
92 ,mil.SEGMENT1
93 ,mil.SEGMENT2
94 ,mil.SEGMENT3
95 ,mil.SEGMENT4
96 ,mil.SEGMENT5
97 ,mil.SEGMENT6
98 ,mil.SEGMENT7
99 ,mil.SEGMENT8
100 ,mil.SEGMENT9
101 ,mil.SEGMENT10
102 ,mil.SEGMENT11
103 ,mil.SEGMENT12
104 ,mil.SEGMENT13
105 ,mil.SEGMENT14
106 ,mil.SEGMENT15
107 ,mil.SEGMENT16
108 ,mil.SEGMENT17
109 ,mil.SEGMENT18
110 ,mil.SEGMENT19
111 ,mil.SEGMENT20
112 ,mil.SUMMARY_FLAG
113 ,mil.ENABLED_FLAG
114 ,mil.START_DATE_ACTIVE
115 ,mil.END_DATE_ACTIVE
116 ,mil.ATTRIBUTE_CATEGORY
117 ,mil.ATTRIBUTE1
118 ,mil.ATTRIBUTE2
119 ,mil.ATTRIBUTE3
120 ,mil.ATTRIBUTE4
121 ,mil.ATTRIBUTE5
122 ,mil.ATTRIBUTE6
123 ,mil.ATTRIBUTE7
124 ,mil.ATTRIBUTE8
125 ,mil.ATTRIBUTE9
126 ,mil.ATTRIBUTE10
127 ,mil.ATTRIBUTE11
128 ,mil.ATTRIBUTE12
129 ,mil.ATTRIBUTE13
130 ,mil.ATTRIBUTE14
131 ,mil.ATTRIBUTE15
132 ,mil.REQUEST_ID
133 ,mil.PROGRAM_APPLICATION_ID
134 ,mil.PROGRAM_ID
135 ,mil.PROGRAM_UPDATE_DATE
136 ,mil.PHYSICAL_LOCATION_ID
137 ,mil.PICK_UOM_CODE
138 ,mil.DIMENSION_UOM_CODE
139 ,mil.LENGTH
140 ,mil.WIDTH
141 ,mil.HEIGHT
142 ,mil.LOCATOR_STATUS
143 ,mil.STATUS_ID
144 ,mil.CURRENT_CUBIC_AREA
145 ,mil.AVAILABLE_CUBIC_AREA
146 ,mil.CURRENT_WEIGHT
147 ,mil.AVAILABLE_WEIGHT
148 ,mil.LOCATION_AVAILABLE_UNITS
149 ,mil.SUGGESTED_CUBIC_AREA
150 ,mil.SUGGESTED_WEIGHT
151 ,mil.LOCATION_SUGGESTED_UNITS
152 ,mil.rowid
153 from MTL_ITEM_LOCATIONS mil
154 ,MTL_SECONDARY_INVENTORIES msei
155 where mil.organization_id(+) = msei.organization_id
156 and mil.subinventory_code(+) = msei.secondary_inventory_name
157 and NVL(msei.disable_date, sysdate+1) > sysdate
158 and NVL(mil.disable_date, sysdate+1) > sysdate
159 )base
160 where base.ORGANIZATION_ID = g_organization_id
161 and base.INVENTORY_ITEM_ID = g_inventory_item_id
162 and (g_project_id = base.project_id OR base.project_id IS NULL)
163 and (g_task_id = base.task_id OR base.task_id IS NULL)
164 and mptdtv.PP_TRANSACTION_TEMP_ID = g_pp_transaction_temp_id
165 and msi.ORGANIZATION_ID = mptdtv.TO_ORGANIZATION_ID
166 and msi.INVENTORY_ITEM_ID = mptdtv.INVENTORY_ITEM_ID
167 order by WMS_Parameter_PVT.GetItemOnHand(
168 base.ORGANIZATION_ID
169 ,mptdtv.INVENTORY_ITEM_ID
170 ,base.SUBINVENTORY_CODE
171 ,base.LOCATOR_ID
172 ,msi.PRIMARY_UOM_CODE
173 ,mptdtv.TRANSACTION_UOM ) desc
174 ,base.PROJECT_ID
175 ,base.TASK_ID
176 ;
177
178 Elsif g_locator_id IS NULL Then
179
180 --if only subinventory passed , OPEN c_no_restrict_sub_passed;
181
182 OPEN p_cursor FOR select base.SUBINVENTORY_CODE
183 ,base.LOCATOR_ID
184 ,base.PROJECT_ID
185 ,base.TASK_ID
186 from MTL_SYSTEM_ITEMS msi
187 ,WMS_TRX_DETAILS_TMP_V mptdtv
188 ,(
189 select msei.secondary_inventory_name subinventory_code --changed
190 ,mil.inventory_location_id locator_id
191 ,msei.organization_id organization_id --changed
192 ,mil.project_id project_id
193 ,mil.task_id task_id
194 ,g_inventory_item_id inventory_item_id
195 ,mil.location_current_units location_current_units
196 ,mil.inventory_item_id locator_inventory_item_id
197 ,mil.empty_flag empty_flag
198 ,mil.mixed_items_flag mixed_items_flag
199 ,mil.LAST_UPDATE_DATE
200 ,mil.LAST_UPDATED_BY
201 ,mil.CREATION_DATE
202 ,mil.CREATED_BY
203 ,mil.LAST_UPDATE_LOGIN
204 ,mil.DESCRIPTION
205 ,mil.DESCRIPTIVE_TEXT
206 ,mil.DISABLE_DATE
207 ,mil.INVENTORY_LOCATION_TYPE
208 ,mil.PICKING_ORDER
209 ,mil.PHYSICAL_LOCATION_CODE
210 ,mil.LOCATION_MAXIMUM_UNITS
211 ,mil.LOCATION_WEIGHT_UOM_CODE
212 ,mil.MAX_WEIGHT
213 ,mil.VOLUME_UOM_CODE
214 ,mil.MAX_CUBIC_AREA
215 ,mil.X_COORDINATE
216 ,mil.Y_COORDINATE
217 ,mil.Z_COORDINATE
218 ,mil.INVENTORY_ACCOUNT_ID
219 ,mil.SEGMENT1
220 ,mil.SEGMENT2
221 ,mil.SEGMENT3
222 ,mil.SEGMENT4
223 ,mil.SEGMENT5
224 ,mil.SEGMENT6
225 ,mil.SEGMENT7
226 ,mil.SEGMENT8
227 ,mil.SEGMENT9
228 ,mil.SEGMENT10
229 ,mil.SEGMENT11
230 ,mil.SEGMENT12
231 ,mil.SEGMENT13
232 ,mil.SEGMENT14
233 ,mil.SEGMENT15
234 ,mil.SEGMENT16
235 ,mil.SEGMENT17
236 ,mil.SEGMENT18
237 ,mil.SEGMENT19
238 ,mil.SEGMENT20
239 ,mil.SUMMARY_FLAG
240 ,mil.ENABLED_FLAG
241 ,mil.START_DATE_ACTIVE
242 ,mil.END_DATE_ACTIVE
243 ,mil.ATTRIBUTE_CATEGORY
244 ,mil.ATTRIBUTE1
245 ,mil.ATTRIBUTE2
246 ,mil.ATTRIBUTE3
247 ,mil.ATTRIBUTE4
248 ,mil.ATTRIBUTE5
249 ,mil.ATTRIBUTE6
250 ,mil.ATTRIBUTE7
251 ,mil.ATTRIBUTE8
252 ,mil.ATTRIBUTE9
253 ,mil.ATTRIBUTE10
254 ,mil.ATTRIBUTE11
255 ,mil.ATTRIBUTE12
256 ,mil.ATTRIBUTE13
257 ,mil.ATTRIBUTE14
258 ,mil.ATTRIBUTE15
259 ,mil.REQUEST_ID
260 ,mil.PROGRAM_APPLICATION_ID
261 ,mil.PROGRAM_ID
262 ,mil.PROGRAM_UPDATE_DATE
263 ,mil.PHYSICAL_LOCATION_ID
264 ,mil.PICK_UOM_CODE
265 ,mil.DIMENSION_UOM_CODE
266 ,mil.LENGTH
267 ,mil.WIDTH
268 ,mil.HEIGHT
269 ,mil.LOCATOR_STATUS
270 ,mil.STATUS_ID
271 ,mil.CURRENT_CUBIC_AREA
272 ,mil.AVAILABLE_CUBIC_AREA
273 ,mil.CURRENT_WEIGHT
274 ,mil.AVAILABLE_WEIGHT
275 ,mil.LOCATION_AVAILABLE_UNITS
276 ,mil.SUGGESTED_CUBIC_AREA
277 ,mil.SUGGESTED_WEIGHT
278 ,mil.LOCATION_SUGGESTED_UNITS
279 ,mil.rowid
280 from MTL_ITEM_LOCATIONS mil
281 ,MTL_SECONDARY_INVENTORIES msei
282 where mil.organization_id(+) = msei.organization_id
283 and mil.subinventory_code(+) = msei.secondary_inventory_name
284 and NVL(msei.disable_date, sysdate+1) > sysdate
285 and NVL(mil.disable_date, sysdate+1) > sysdate
286 )base
287 where base.ORGANIZATION_ID = g_organization_id
288 and base.INVENTORY_ITEM_ID = g_inventory_item_id
289 and (g_project_id = base.project_id OR base.project_id IS NULL)
290 and (g_task_id = base.task_id OR base.task_id IS NULL)
291 and mptdtv.PP_TRANSACTION_TEMP_ID = g_pp_transaction_temp_id
292 and msi.ORGANIZATION_ID = mptdtv.TO_ORGANIZATION_ID
293 and msi.INVENTORY_ITEM_ID = mptdtv.INVENTORY_ITEM_ID
294 and base.subinventory_code = g_subinventory_code
295 order by WMS_Parameter_PVT.GetItemOnHand(
296 base.ORGANIZATION_ID
297 ,mptdtv.INVENTORY_ITEM_ID
298 ,base.SUBINVENTORY_CODE
299 ,base.LOCATOR_ID
300 ,msi.PRIMARY_UOM_CODE
301 ,mptdtv.TRANSACTION_UOM ) desc
302 ,base.PROJECT_ID
303 ,base.TASK_ID
304 ;
305
306 Else
307 --if subinventory and locator passed, OPEN c_no_restrict_loc_passed;
308 OPEN p_cursor FOR select base.SUBINVENTORY_CODE
309 ,base.LOCATOR_ID
310 ,base.PROJECT_ID
311 ,base.TASK_ID
312 from MTL_SYSTEM_ITEMS msi
313 ,WMS_TRX_DETAILS_TMP_V mptdtv
314 ,(
315 select msei.secondary_inventory_name subinventory_code --changed
316 ,mil.inventory_location_id locator_id
317 ,msei.organization_id organization_id --changed
318 ,mil.project_id project_id
319 ,mil.task_id task_id
320 ,g_inventory_item_id inventory_item_id
321 ,mil.location_current_units location_current_units
322 ,mil.inventory_item_id locator_inventory_item_id
323 ,mil.empty_flag empty_flag
324 ,mil.mixed_items_flag mixed_items_flag
325 ,mil.LAST_UPDATE_DATE
326 ,mil.LAST_UPDATED_BY
327 ,mil.CREATION_DATE
328 ,mil.CREATED_BY
329 ,mil.LAST_UPDATE_LOGIN
330 ,mil.DESCRIPTION
331 ,mil.DESCRIPTIVE_TEXT
332 ,mil.DISABLE_DATE
333 ,mil.INVENTORY_LOCATION_TYPE
334 ,mil.PICKING_ORDER
335 ,mil.PHYSICAL_LOCATION_CODE
336 ,mil.LOCATION_MAXIMUM_UNITS
337 ,mil.LOCATION_WEIGHT_UOM_CODE
338 ,mil.MAX_WEIGHT
339 ,mil.VOLUME_UOM_CODE
340 ,mil.MAX_CUBIC_AREA
341 ,mil.X_COORDINATE
342 ,mil.Y_COORDINATE
343 ,mil.Z_COORDINATE
344 ,mil.INVENTORY_ACCOUNT_ID
345 ,mil.SEGMENT1
346 ,mil.SEGMENT2
347 ,mil.SEGMENT3
348 ,mil.SEGMENT4
349 ,mil.SEGMENT5
350 ,mil.SEGMENT6
351 ,mil.SEGMENT7
352 ,mil.SEGMENT8
353 ,mil.SEGMENT9
354 ,mil.SEGMENT10
355 ,mil.SEGMENT11
356 ,mil.SEGMENT12
357 ,mil.SEGMENT13
358 ,mil.SEGMENT14
359 ,mil.SEGMENT15
360 ,mil.SEGMENT16
361 ,mil.SEGMENT17
362 ,mil.SEGMENT18
363 ,mil.SEGMENT19
364 ,mil.SEGMENT20
365 ,mil.SUMMARY_FLAG
366 ,mil.ENABLED_FLAG
367 ,mil.START_DATE_ACTIVE
368 ,mil.END_DATE_ACTIVE
369 ,mil.ATTRIBUTE_CATEGORY
370 ,mil.ATTRIBUTE1
371 ,mil.ATTRIBUTE2
372 ,mil.ATTRIBUTE3
373 ,mil.ATTRIBUTE4
374 ,mil.ATTRIBUTE5
375 ,mil.ATTRIBUTE6
376 ,mil.ATTRIBUTE7
377 ,mil.ATTRIBUTE8
378 ,mil.ATTRIBUTE9
379 ,mil.ATTRIBUTE10
380 ,mil.ATTRIBUTE11
381 ,mil.ATTRIBUTE12
382 ,mil.ATTRIBUTE13
383 ,mil.ATTRIBUTE14
384 ,mil.ATTRIBUTE15
385 ,mil.REQUEST_ID
386 ,mil.PROGRAM_APPLICATION_ID
387 ,mil.PROGRAM_ID
388 ,mil.PROGRAM_UPDATE_DATE
389 ,mil.PHYSICAL_LOCATION_ID
390 ,mil.PICK_UOM_CODE
391 ,mil.DIMENSION_UOM_CODE
392 ,mil.LENGTH
393 ,mil.WIDTH
394 ,mil.HEIGHT
395 ,mil.LOCATOR_STATUS
396 ,mil.STATUS_ID
397 ,mil.CURRENT_CUBIC_AREA
398 ,mil.AVAILABLE_CUBIC_AREA
399 ,mil.CURRENT_WEIGHT
400 ,mil.AVAILABLE_WEIGHT
401 ,mil.LOCATION_AVAILABLE_UNITS
402 ,mil.SUGGESTED_CUBIC_AREA
403 ,mil.SUGGESTED_WEIGHT
404 ,mil.LOCATION_SUGGESTED_UNITS
405 ,mil.rowid
406 from MTL_ITEM_LOCATIONS mil
407 ,MTL_SECONDARY_INVENTORIES msei
408 where mil.organization_id(+) = msei.organization_id
409 and mil.subinventory_code(+) = msei.secondary_inventory_name
410 and NVL(msei.disable_date, sysdate+1) > sysdate
411 and NVL(mil.disable_date, sysdate+1) > sysdate
412 )base
413 where base.ORGANIZATION_ID = g_organization_id
414 and base.INVENTORY_ITEM_ID = g_inventory_item_id
415 and (g_project_id = base.project_id OR base.project_id IS NULL)
416 and (g_task_id = base.task_id OR base.task_id IS NULL)
417 and mptdtv.PP_TRANSACTION_TEMP_ID = g_pp_transaction_temp_id
418 and msi.ORGANIZATION_ID = mptdtv.TO_ORGANIZATION_ID
419 and msi.INVENTORY_ITEM_ID = mptdtv.INVENTORY_ITEM_ID
420 and base.subinventory_code = g_subinventory_code
421 and base.locator_id = g_locator_id
422 order by WMS_Parameter_PVT.GetItemOnHand(
423 base.ORGANIZATION_ID
424 ,mptdtv.INVENTORY_ITEM_ID
425 ,base.SUBINVENTORY_CODE
426 ,base.LOCATOR_ID
427 ,msi.PRIMARY_UOM_CODE
428 ,mptdtv.TRANSACTION_UOM ) desc
429 ,base.PROJECT_ID
430 ,base.TASK_ID
431 ;
432 End If;
433 ELSIF g_restrict_locs_code = 2 THEN
434 If g_subinventory_code IS NULL Then
438 ,base.PROJECT_ID
435 --if nothing passed, OPEN c_sub_restrict_no_passed;
436 OPEN p_cursor FOR select base.SUBINVENTORY_CODE
437 ,base.LOCATOR_ID
439 ,base.TASK_ID
440 from MTL_SYSTEM_ITEMS msi
441 ,WMS_TRX_DETAILS_TMP_V mptdtv
442 ,(
443 select msei.secondary_inventory_name subinventory_code -- changed
444 ,mil.inventory_location_id locator_id
445 ,msei.organization_id organization_id -- changed
446 ,mil.project_id project_id
447 ,mil.task_id task_id
448 ,g_inventory_item_id inventory_item_id
449 ,mil.location_current_units location_current_units
450 ,mil.inventory_item_id locator_inventory_item_id
451 ,mil.empty_flag empty_flag
452 ,mil.mixed_items_flag mixed_items_flag
453 ,mil.LAST_UPDATE_DATE
454 ,mil.LAST_UPDATED_BY
455 ,mil.CREATION_DATE
456 ,mil.CREATED_BY
457 ,mil.LAST_UPDATE_LOGIN
458 ,mil.DESCRIPTION
459 ,mil.DESCRIPTIVE_TEXT
460 ,mil.DISABLE_DATE
461 ,mil.INVENTORY_LOCATION_TYPE
462 ,mil.PICKING_ORDER
463 ,mil.PHYSICAL_LOCATION_CODE
464 ,mil.LOCATION_MAXIMUM_UNITS
465 ,mil.LOCATION_WEIGHT_UOM_CODE
466 ,mil.MAX_WEIGHT
467 ,mil.VOLUME_UOM_CODE
468 ,mil.MAX_CUBIC_AREA
469 ,mil.X_COORDINATE
470 ,mil.Y_COORDINATE
471 ,mil.Z_COORDINATE
472 ,mil.INVENTORY_ACCOUNT_ID
473 ,mil.SEGMENT1
474 ,mil.SEGMENT2
475 ,mil.SEGMENT3
476 ,mil.SEGMENT4
477 ,mil.SEGMENT5
478 ,mil.SEGMENT6
479 ,mil.SEGMENT7
480 ,mil.SEGMENT8
481 ,mil.SEGMENT9
482 ,mil.SEGMENT10
483 ,mil.SEGMENT11
484 ,mil.SEGMENT12
485 ,mil.SEGMENT13
486 ,mil.SEGMENT14
487 ,mil.SEGMENT15
488 ,mil.SEGMENT16
489 ,mil.SEGMENT17
490 ,mil.SEGMENT18
491 ,mil.SEGMENT19
492 ,mil.SEGMENT20
493 ,mil.SUMMARY_FLAG
494 ,mil.ENABLED_FLAG
495 ,mil.START_DATE_ACTIVE
496 ,mil.END_DATE_ACTIVE
497 ,mil.ATTRIBUTE_CATEGORY
498 ,mil.ATTRIBUTE1
499 ,mil.ATTRIBUTE2
500 ,mil.ATTRIBUTE3
501 ,mil.ATTRIBUTE4
502 ,mil.ATTRIBUTE5
503 ,mil.ATTRIBUTE6
504 ,mil.ATTRIBUTE7
505 ,mil.ATTRIBUTE8
506 ,mil.ATTRIBUTE9
507 ,mil.ATTRIBUTE10
508 ,mil.ATTRIBUTE11
509 ,mil.ATTRIBUTE12
510 ,mil.ATTRIBUTE13
511 ,mil.ATTRIBUTE14
512 ,mil.ATTRIBUTE15
513 ,mil.REQUEST_ID
514 ,mil.PROGRAM_APPLICATION_ID
515 ,mil.PROGRAM_ID
516 ,mil.PROGRAM_UPDATE_DATE
517 ,mil.PHYSICAL_LOCATION_ID
518 ,mil.PICK_UOM_CODE
519 ,mil.DIMENSION_UOM_CODE
520 ,mil.LENGTH
521 ,mil.WIDTH
522 ,mil.HEIGHT
523 ,mil.LOCATOR_STATUS
524 ,mil.STATUS_ID
525 ,mil.CURRENT_CUBIC_AREA
526 ,mil.AVAILABLE_CUBIC_AREA
527 ,mil.CURRENT_WEIGHT
528 ,mil.AVAILABLE_WEIGHT
529 ,mil.LOCATION_AVAILABLE_UNITS
530 ,mil.SUGGESTED_CUBIC_AREA
531 ,mil.SUGGESTED_WEIGHT
532 ,mil.LOCATION_SUGGESTED_UNITS
533 ,mil.rowid
534 from MTL_ITEM_LOCATIONS mil
535 ,MTL_SECONDARY_INVENTORIES msei
536 ,MTL_ITEM_SUB_INVENTORIES misi
537 where mil.organization_id(+) = msei.organization_id
538 and mil.subinventory_code(+) = msei.secondary_inventory_name
539 and NVL(msei.disable_date, sysdate+1) > sysdate
540 and NVL(mil.disable_date, sysdate+1) > sysdate
541 and mil.organization_id = misi.organization_id
542 and mil.subinventory_code = misi.secondary_inventory
543 and misi.inventory_item_id = g_inventory_item_id
544 )base
545 where base.ORGANIZATION_ID = g_organization_id
546 and base.INVENTORY_ITEM_ID = g_inventory_item_id
547 and (g_project_id = base.project_id OR base.project_id IS NULL)
548 and (g_task_id = base.task_id OR base.task_id IS NULL)
549 and mptdtv.PP_TRANSACTION_TEMP_ID = g_pp_transaction_temp_id
550 and msi.ORGANIZATION_ID = mptdtv.TO_ORGANIZATION_ID
551 and msi.INVENTORY_ITEM_ID = mptdtv.INVENTORY_ITEM_ID
552 order by WMS_Parameter_PVT.GetItemOnHand(
553 base.ORGANIZATION_ID
554 ,mptdtv.INVENTORY_ITEM_ID
555 ,base.SUBINVENTORY_CODE
556 ,base.LOCATOR_ID
557 ,msi.PRIMARY_UOM_CODE
558 ,mptdtv.TRANSACTION_UOM ) desc
559 ,base.PROJECT_ID
560 ,base.TASK_ID
561 ;
562 Elsif g_locator_id IS NULL Then
563 --if only subinventory passed, OPEN c_sub_restrict_sub_passed;
564 OPEN p_cursor FOR select base.SUBINVENTORY_CODE
565 ,base.LOCATOR_ID
566 ,base.PROJECT_ID
567 ,base.TASK_ID
568 from MTL_SYSTEM_ITEMS msi
569 ,WMS_TRX_DETAILS_TMP_V mptdtv
570 ,(
571 select msei.secondary_inventory_name subinventory_code -- changed
572 ,mil.inventory_location_id locator_id
573 ,msei.organization_id organization_id -- changed
574 ,mil.project_id project_id
575 ,mil.task_id task_id
579 ,mil.empty_flag empty_flag
576 ,g_inventory_item_id inventory_item_id
577 ,mil.location_current_units location_current_units
578 ,mil.inventory_item_id locator_inventory_item_id
580 ,mil.mixed_items_flag mixed_items_flag
581 ,mil.LAST_UPDATE_DATE
582 ,mil.LAST_UPDATED_BY
583 ,mil.CREATION_DATE
584 ,mil.CREATED_BY
585 ,mil.LAST_UPDATE_LOGIN
586 ,mil.DESCRIPTION
587 ,mil.DESCRIPTIVE_TEXT
588 ,mil.DISABLE_DATE
589 ,mil.INVENTORY_LOCATION_TYPE
590 ,mil.PICKING_ORDER
591 ,mil.PHYSICAL_LOCATION_CODE
592 ,mil.LOCATION_MAXIMUM_UNITS
593 ,mil.LOCATION_WEIGHT_UOM_CODE
594 ,mil.MAX_WEIGHT
595 ,mil.VOLUME_UOM_CODE
596 ,mil.MAX_CUBIC_AREA
597 ,mil.X_COORDINATE
598 ,mil.Y_COORDINATE
599 ,mil.Z_COORDINATE
600 ,mil.INVENTORY_ACCOUNT_ID
601 ,mil.SEGMENT1
602 ,mil.SEGMENT2
603 ,mil.SEGMENT3
604 ,mil.SEGMENT4
605 ,mil.SEGMENT5
606 ,mil.SEGMENT6
607 ,mil.SEGMENT7
608 ,mil.SEGMENT8
609 ,mil.SEGMENT9
610 ,mil.SEGMENT10
611 ,mil.SEGMENT11
612 ,mil.SEGMENT12
613 ,mil.SEGMENT13
614 ,mil.SEGMENT14
615 ,mil.SEGMENT15
616 ,mil.SEGMENT16
617 ,mil.SEGMENT17
618 ,mil.SEGMENT18
619 ,mil.SEGMENT19
620 ,mil.SEGMENT20
621 ,mil.SUMMARY_FLAG
622 ,mil.ENABLED_FLAG
623 ,mil.START_DATE_ACTIVE
624 ,mil.END_DATE_ACTIVE
625 ,mil.ATTRIBUTE_CATEGORY
626 ,mil.ATTRIBUTE1
627 ,mil.ATTRIBUTE2
628 ,mil.ATTRIBUTE3
629 ,mil.ATTRIBUTE4
630 ,mil.ATTRIBUTE5
631 ,mil.ATTRIBUTE6
632 ,mil.ATTRIBUTE7
633 ,mil.ATTRIBUTE8
634 ,mil.ATTRIBUTE9
635 ,mil.ATTRIBUTE10
636 ,mil.ATTRIBUTE11
637 ,mil.ATTRIBUTE12
638 ,mil.ATTRIBUTE13
639 ,mil.ATTRIBUTE14
640 ,mil.ATTRIBUTE15
641 ,mil.REQUEST_ID
642 ,mil.PROGRAM_APPLICATION_ID
643 ,mil.PROGRAM_ID
644 ,mil.PROGRAM_UPDATE_DATE
645 ,mil.PHYSICAL_LOCATION_ID
646 ,mil.PICK_UOM_CODE
647 ,mil.DIMENSION_UOM_CODE
648 ,mil.LENGTH
649 ,mil.WIDTH
650 ,mil.HEIGHT
651 ,mil.LOCATOR_STATUS
652 ,mil.STATUS_ID
653 ,mil.CURRENT_CUBIC_AREA
654 ,mil.AVAILABLE_CUBIC_AREA
655 ,mil.CURRENT_WEIGHT
656 ,mil.AVAILABLE_WEIGHT
657 ,mil.LOCATION_AVAILABLE_UNITS
658 ,mil.SUGGESTED_CUBIC_AREA
659 ,mil.SUGGESTED_WEIGHT
660 ,mil.LOCATION_SUGGESTED_UNITS
661 ,mil.rowid
662 from MTL_ITEM_LOCATIONS mil
663 ,MTL_SECONDARY_INVENTORIES msei
664 ,MTL_ITEM_SUB_INVENTORIES misi
665 where mil.organization_id(+) = msei.organization_id
666 and mil.subinventory_code(+) = msei.secondary_inventory_name
667 and NVL(msei.disable_date, sysdate+1) > sysdate
668 and NVL(mil.disable_date, sysdate+1) > sysdate
669 and mil.organization_id = misi.organization_id
670 and mil.subinventory_code = misi.secondary_inventory
671 and misi.inventory_item_id = g_inventory_item_id
672 )base
673 where base.ORGANIZATION_ID = g_organization_id
674 and base.INVENTORY_ITEM_ID = g_inventory_item_id
675 and (g_project_id = base.project_id OR base.project_id IS NULL)
676 and (g_task_id = base.task_id OR base.task_id IS NULL)
677 and mptdtv.PP_TRANSACTION_TEMP_ID = g_pp_transaction_temp_id
678 and msi.ORGANIZATION_ID = mptdtv.TO_ORGANIZATION_ID
679 and msi.INVENTORY_ITEM_ID = mptdtv.INVENTORY_ITEM_ID
680 and base.subinventory_code = g_subinventory_code
681 order by WMS_Parameter_PVT.GetItemOnHand(
682 base.ORGANIZATION_ID
683 ,mptdtv.INVENTORY_ITEM_ID
684 ,base.SUBINVENTORY_CODE
685 ,base.LOCATOR_ID
686 ,msi.PRIMARY_UOM_CODE
687 ,mptdtv.TRANSACTION_UOM ) desc
688 ,base.PROJECT_ID
689 ,base.TASK_ID
690 ;
691
692 Else
693 --if subinventory and locator passed, OPEN c_sub_restrict_loc_passed;
694 OPEN p_cursor FOR select base.SUBINVENTORY_CODE
695 ,base.LOCATOR_ID
696 ,base.PROJECT_ID
697 ,base.TASK_ID
698 from MTL_SYSTEM_ITEMS msi
699 ,WMS_TRX_DETAILS_TMP_V mptdtv
700 ,(
701 select msei.secondary_inventory_name subinventory_code -- changed
702 ,mil.inventory_location_id locator_id
703 ,msei.organization_id organization_id -- changed
704 ,mil.project_id project_id
705 ,mil.task_id task_id
706 ,g_inventory_item_id inventory_item_id
707 ,mil.location_current_units location_current_units
708 ,mil.inventory_item_id locator_inventory_item_id
709 ,mil.empty_flag empty_flag
710 ,mil.mixed_items_flag mixed_items_flag
711 ,mil.LAST_UPDATE_DATE
712 ,mil.LAST_UPDATED_BY
713 ,mil.CREATION_DATE
714 ,mil.CREATED_BY
715 ,mil.LAST_UPDATE_LOGIN
716 ,mil.DESCRIPTION
717 ,mil.DESCRIPTIVE_TEXT
718 ,mil.DISABLE_DATE
722 ,mil.LOCATION_MAXIMUM_UNITS
719 ,mil.INVENTORY_LOCATION_TYPE
720 ,mil.PICKING_ORDER
721 ,mil.PHYSICAL_LOCATION_CODE
723 ,mil.LOCATION_WEIGHT_UOM_CODE
724 ,mil.MAX_WEIGHT
725 ,mil.VOLUME_UOM_CODE
726 ,mil.MAX_CUBIC_AREA
727 ,mil.X_COORDINATE
728 ,mil.Y_COORDINATE
729 ,mil.Z_COORDINATE
730 ,mil.INVENTORY_ACCOUNT_ID
731 ,mil.SEGMENT1
732 ,mil.SEGMENT2
733 ,mil.SEGMENT3
734 ,mil.SEGMENT4
735 ,mil.SEGMENT5
736 ,mil.SEGMENT6
737 ,mil.SEGMENT7
738 ,mil.SEGMENT8
739 ,mil.SEGMENT9
740 ,mil.SEGMENT10
741 ,mil.SEGMENT11
742 ,mil.SEGMENT12
743 ,mil.SEGMENT13
744 ,mil.SEGMENT14
745 ,mil.SEGMENT15
746 ,mil.SEGMENT16
747 ,mil.SEGMENT17
748 ,mil.SEGMENT18
749 ,mil.SEGMENT19
750 ,mil.SEGMENT20
751 ,mil.SUMMARY_FLAG
752 ,mil.ENABLED_FLAG
753 ,mil.START_DATE_ACTIVE
754 ,mil.END_DATE_ACTIVE
755 ,mil.ATTRIBUTE_CATEGORY
756 ,mil.ATTRIBUTE1
757 ,mil.ATTRIBUTE2
758 ,mil.ATTRIBUTE3
759 ,mil.ATTRIBUTE4
760 ,mil.ATTRIBUTE5
761 ,mil.ATTRIBUTE6
762 ,mil.ATTRIBUTE7
763 ,mil.ATTRIBUTE8
764 ,mil.ATTRIBUTE9
765 ,mil.ATTRIBUTE10
766 ,mil.ATTRIBUTE11
767 ,mil.ATTRIBUTE12
768 ,mil.ATTRIBUTE13
769 ,mil.ATTRIBUTE14
770 ,mil.ATTRIBUTE15
771 ,mil.REQUEST_ID
772 ,mil.PROGRAM_APPLICATION_ID
773 ,mil.PROGRAM_ID
774 ,mil.PROGRAM_UPDATE_DATE
775 ,mil.PHYSICAL_LOCATION_ID
776 ,mil.PICK_UOM_CODE
777 ,mil.DIMENSION_UOM_CODE
778 ,mil.LENGTH
779 ,mil.WIDTH
780 ,mil.HEIGHT
781 ,mil.LOCATOR_STATUS
782 ,mil.STATUS_ID
783 ,mil.CURRENT_CUBIC_AREA
784 ,mil.AVAILABLE_CUBIC_AREA
785 ,mil.CURRENT_WEIGHT
786 ,mil.AVAILABLE_WEIGHT
787 ,mil.LOCATION_AVAILABLE_UNITS
788 ,mil.SUGGESTED_CUBIC_AREA
789 ,mil.SUGGESTED_WEIGHT
790 ,mil.LOCATION_SUGGESTED_UNITS
791 ,mil.rowid
792 from MTL_ITEM_LOCATIONS mil
793 ,MTL_SECONDARY_INVENTORIES msei
794 ,MTL_ITEM_SUB_INVENTORIES misi
795 where mil.organization_id(+) = msei.organization_id
796 and mil.subinventory_code(+) = msei.secondary_inventory_name
797 and NVL(msei.disable_date, sysdate+1) > sysdate
798 and NVL(mil.disable_date, sysdate+1) > sysdate
799 and mil.organization_id = misi.organization_id
800 and mil.subinventory_code = misi.secondary_inventory
801 and misi.inventory_item_id = g_inventory_item_id
802 )base
803 where base.ORGANIZATION_ID = g_organization_id
804 and base.INVENTORY_ITEM_ID = g_inventory_item_id
805 and (g_project_id = base.project_id OR base.project_id IS NULL)
806 and (g_task_id = base.task_id OR base.task_id IS NULL)
807 and mptdtv.PP_TRANSACTION_TEMP_ID = g_pp_transaction_temp_id
808 and msi.ORGANIZATION_ID = mptdtv.TO_ORGANIZATION_ID
809 and msi.INVENTORY_ITEM_ID = mptdtv.INVENTORY_ITEM_ID
810 and base.subinventory_code = g_subinventory_code
811 and base.locator_id = g_locator_id
812 order by WMS_Parameter_PVT.GetItemOnHand(
813 base.ORGANIZATION_ID
814 ,mptdtv.INVENTORY_ITEM_ID
815 ,base.SUBINVENTORY_CODE
816 ,base.LOCATOR_ID
817 ,msi.PRIMARY_UOM_CODE
818 ,mptdtv.TRANSACTION_UOM ) desc
819 ,base.PROJECT_ID
820 ,base.TASK_ID
821 ;
822 End If;
823 ELSE
824 If g_subinventory_code IS NULL Then
825 --if nothing passed, OPEN c_loc_restrict_no_passed;
826 OPEN p_cursor FOR select base.SUBINVENTORY_CODE
827 ,base.LOCATOR_ID
828 ,base.PROJECT_ID
829 ,base.TASK_ID
830 from MTL_SYSTEM_ITEMS msi
831 ,WMS_TRX_DETAILS_TMP_V mptdtv
832 ,(
833 select mil.subinventory_code subinventory_code
834 ,mil.inventory_location_id locator_id
835 ,mil.organization_id organization_id
836 ,mil.project_id project_id
837 ,mil.task_id task_id
838 ,g_inventory_item_id inventory_item_id
839 ,mil.location_current_units location_current_units
840 ,mil.inventory_item_id locator_inventory_item_id
841 ,mil.empty_flag empty_flag
842 ,mil.mixed_items_flag mixed_items_flag
843 ,mil.LAST_UPDATE_DATE
844 ,mil.LAST_UPDATED_BY
845 ,mil.CREATION_DATE
846 ,mil.CREATED_BY
847 ,mil.LAST_UPDATE_LOGIN
848 ,mil.DESCRIPTION
849 ,mil.DESCRIPTIVE_TEXT
850 ,mil.DISABLE_DATE
851 ,mil.INVENTORY_LOCATION_TYPE
852 ,mil.PICKING_ORDER
853 ,mil.PHYSICAL_LOCATION_CODE
854 ,mil.LOCATION_MAXIMUM_UNITS
855 ,mil.LOCATION_WEIGHT_UOM_CODE
856 ,mil.MAX_WEIGHT
857 ,mil.VOLUME_UOM_CODE
858 ,mil.MAX_CUBIC_AREA
859 ,mil.X_COORDINATE
860 ,mil.Y_COORDINATE
861 ,mil.Z_COORDINATE
862 ,mil.INVENTORY_ACCOUNT_ID
863 ,mil.SEGMENT1
867 ,mil.SEGMENT5
864 ,mil.SEGMENT2
865 ,mil.SEGMENT3
866 ,mil.SEGMENT4
868 ,mil.SEGMENT6
869 ,mil.SEGMENT7
870 ,mil.SEGMENT8
871 ,mil.SEGMENT9
872 ,mil.SEGMENT10
873 ,mil.SEGMENT11
874 ,mil.SEGMENT12
875 ,mil.SEGMENT13
876 ,mil.SEGMENT14
877 ,mil.SEGMENT15
878 ,mil.SEGMENT16
879 ,mil.SEGMENT17
880 ,mil.SEGMENT18
881 ,mil.SEGMENT19
882 ,mil.SEGMENT20
883 ,mil.SUMMARY_FLAG
884 ,mil.ENABLED_FLAG
885 ,mil.START_DATE_ACTIVE
886 ,mil.END_DATE_ACTIVE
887 ,mil.ATTRIBUTE_CATEGORY
888 ,mil.ATTRIBUTE1
889 ,mil.ATTRIBUTE2
890 ,mil.ATTRIBUTE3
891 ,mil.ATTRIBUTE4
892 ,mil.ATTRIBUTE5
893 ,mil.ATTRIBUTE6
894 ,mil.ATTRIBUTE7
895 ,mil.ATTRIBUTE8
896 ,mil.ATTRIBUTE9
897 ,mil.ATTRIBUTE10
898 ,mil.ATTRIBUTE11
899 ,mil.ATTRIBUTE12
900 ,mil.ATTRIBUTE13
901 ,mil.ATTRIBUTE14
902 ,mil.ATTRIBUTE15
903 ,mil.REQUEST_ID
904 ,mil.PROGRAM_APPLICATION_ID
905 ,mil.PROGRAM_ID
906 ,mil.PROGRAM_UPDATE_DATE
907 ,mil.PHYSICAL_LOCATION_ID
908 ,mil.PICK_UOM_CODE
909 ,mil.DIMENSION_UOM_CODE
910 ,mil.LENGTH
911 ,mil.WIDTH
912 ,mil.HEIGHT
913 ,mil.LOCATOR_STATUS
914 ,mil.STATUS_ID
915 ,mil.CURRENT_CUBIC_AREA
916 ,mil.AVAILABLE_CUBIC_AREA
917 ,mil.CURRENT_WEIGHT
918 ,mil.AVAILABLE_WEIGHT
919 ,mil.LOCATION_AVAILABLE_UNITS
920 ,mil.SUGGESTED_CUBIC_AREA
921 ,mil.SUGGESTED_WEIGHT
922 ,mil.LOCATION_SUGGESTED_UNITS
923 ,mil.rowid
924 from MTL_ITEM_LOCATIONS mil
925 ,MTL_SECONDARY_INVENTORIES msei
926 ,MTL_ITEM_SUB_INVENTORIES misi
927 ,MTL_SECONDARY_LOCATORS msl
928 where mil.organization_id = msei.organization_id
929 and mil.subinventory_code = msei.secondary_inventory_name
930 and NVL(msei.disable_date, sysdate+1) > sysdate
931 and NVL(mil.disable_date, sysdate+1) > sysdate
932 and mil.organization_id = misi.organization_id
933 and mil.subinventory_code = misi.secondary_inventory
934 and misi.inventory_item_id = g_inventory_item_id
935 and mil.organization_id = msl.organization_id
936 and mil.inventory_location_id = msl.secondary_locator
937 and msl.inventory_item_Id = g_inventory_item_id
938 )base
939 where base.ORGANIZATION_ID = g_organization_id
940 and base.INVENTORY_ITEM_ID = g_inventory_item_id
941 and (g_project_id = base.project_id OR base.project_id IS NULL)
942 and (g_task_id = base.task_id OR base.task_id IS NULL)
943 and mptdtv.PP_TRANSACTION_TEMP_ID = g_pp_transaction_temp_id
944 and msi.ORGANIZATION_ID = mptdtv.TO_ORGANIZATION_ID
945 and msi.INVENTORY_ITEM_ID = mptdtv.INVENTORY_ITEM_ID
946 order by WMS_Parameter_PVT.GetItemOnHand(
947 base.ORGANIZATION_ID
948 ,mptdtv.INVENTORY_ITEM_ID
949 ,base.SUBINVENTORY_CODE
950 ,base.LOCATOR_ID
951 ,msi.PRIMARY_UOM_CODE
952 ,mptdtv.TRANSACTION_UOM ) desc
953 ,base.PROJECT_ID
954 ,base.TASK_ID
955 ;
956
957 Elsif g_locator_id IS NULL Then
958 --if only subinventory passed,OPEN c_loc_restrict_sub_passed;
959 OPEN p_cursor FOR select base.SUBINVENTORY_CODE
960 ,base.LOCATOR_ID
961 ,base.PROJECT_ID
962 ,base.TASK_ID
963 from MTL_SYSTEM_ITEMS msi
964 ,WMS_TRX_DETAILS_TMP_V mptdtv
965 ,(
966 select mil.subinventory_code subinventory_code
967 ,mil.inventory_location_id locator_id
968 ,mil.organization_id organization_id
969 ,mil.project_id project_id
970 ,mil.task_id task_id
971 ,g_inventory_item_id inventory_item_id
972 ,mil.location_current_units location_current_units
973 ,mil.inventory_item_id locator_inventory_item_id
974 ,mil.empty_flag empty_flag
975 ,mil.mixed_items_flag mixed_items_flag
976 ,mil.LAST_UPDATE_DATE
977 ,mil.LAST_UPDATED_BY
978 ,mil.CREATION_DATE
979 ,mil.CREATED_BY
980 ,mil.LAST_UPDATE_LOGIN
981 ,mil.DESCRIPTION
982 ,mil.DESCRIPTIVE_TEXT
983 ,mil.DISABLE_DATE
984 ,mil.INVENTORY_LOCATION_TYPE
985 ,mil.PICKING_ORDER
986 ,mil.PHYSICAL_LOCATION_CODE
987 ,mil.LOCATION_MAXIMUM_UNITS
988 ,mil.LOCATION_WEIGHT_UOM_CODE
989 ,mil.MAX_WEIGHT
990 ,mil.VOLUME_UOM_CODE
991 ,mil.MAX_CUBIC_AREA
992 ,mil.X_COORDINATE
993 ,mil.Y_COORDINATE
994 ,mil.Z_COORDINATE
995 ,mil.INVENTORY_ACCOUNT_ID
996 ,mil.SEGMENT1
997 ,mil.SEGMENT2
998 ,mil.SEGMENT3
999 ,mil.SEGMENT4
1000 ,mil.SEGMENT5
1001 ,mil.SEGMENT6
1002 ,mil.SEGMENT7
1003 ,mil.SEGMENT8
1004 ,mil.SEGMENT9
1005 ,mil.SEGMENT10
1006 ,mil.SEGMENT11
1007 ,mil.SEGMENT12
1008 ,mil.SEGMENT13
1012 ,mil.SEGMENT17
1009 ,mil.SEGMENT14
1010 ,mil.SEGMENT15
1011 ,mil.SEGMENT16
1013 ,mil.SEGMENT18
1014 ,mil.SEGMENT19
1015 ,mil.SEGMENT20
1016 ,mil.SUMMARY_FLAG
1017 ,mil.ENABLED_FLAG
1018 ,mil.START_DATE_ACTIVE
1019 ,mil.END_DATE_ACTIVE
1020 ,mil.ATTRIBUTE_CATEGORY
1021 ,mil.ATTRIBUTE1
1022 ,mil.ATTRIBUTE2
1023 ,mil.ATTRIBUTE3
1024 ,mil.ATTRIBUTE4
1025 ,mil.ATTRIBUTE5
1026 ,mil.ATTRIBUTE6
1027 ,mil.ATTRIBUTE7
1028 ,mil.ATTRIBUTE8
1029 ,mil.ATTRIBUTE9
1030 ,mil.ATTRIBUTE10
1031 ,mil.ATTRIBUTE11
1032 ,mil.ATTRIBUTE12
1033 ,mil.ATTRIBUTE13
1034 ,mil.ATTRIBUTE14
1035 ,mil.ATTRIBUTE15
1036 ,mil.REQUEST_ID
1037 ,mil.PROGRAM_APPLICATION_ID
1038 ,mil.PROGRAM_ID
1039 ,mil.PROGRAM_UPDATE_DATE
1040 ,mil.PHYSICAL_LOCATION_ID
1041 ,mil.PICK_UOM_CODE
1042 ,mil.DIMENSION_UOM_CODE
1043 ,mil.LENGTH
1044 ,mil.WIDTH
1045 ,mil.HEIGHT
1046 ,mil.LOCATOR_STATUS
1047 ,mil.STATUS_ID
1048 ,mil.CURRENT_CUBIC_AREA
1049 ,mil.AVAILABLE_CUBIC_AREA
1050 ,mil.CURRENT_WEIGHT
1051 ,mil.AVAILABLE_WEIGHT
1052 ,mil.LOCATION_AVAILABLE_UNITS
1053 ,mil.SUGGESTED_CUBIC_AREA
1054 ,mil.SUGGESTED_WEIGHT
1055 ,mil.LOCATION_SUGGESTED_UNITS
1056 ,mil.rowid
1057 from MTL_ITEM_LOCATIONS mil
1058 ,MTL_SECONDARY_INVENTORIES msei
1059 ,MTL_ITEM_SUB_INVENTORIES misi
1060 ,MTL_SECONDARY_LOCATORS msl
1061 where mil.organization_id = msei.organization_id
1062 and mil.subinventory_code = msei.secondary_inventory_name
1063 and NVL(msei.disable_date, sysdate+1) > sysdate
1064 and NVL(mil.disable_date, sysdate+1) > sysdate
1065 and mil.organization_id = misi.organization_id
1066 and mil.subinventory_code = misi.secondary_inventory
1067 and misi.inventory_item_id = g_inventory_item_id
1068 and mil.organization_id = msl.organization_id
1069 and mil.inventory_location_id = msl.secondary_locator
1070 and msl.inventory_item_Id = g_inventory_item_id
1071 )base
1072 where base.ORGANIZATION_ID = g_organization_id
1073 and base.INVENTORY_ITEM_ID = g_inventory_item_id
1074 and (g_project_id = base.project_id OR base.project_id IS NULL)
1075 and (g_task_id = base.task_id OR base.task_id IS NULL)
1076 and mptdtv.PP_TRANSACTION_TEMP_ID = g_pp_transaction_temp_id
1077 and msi.ORGANIZATION_ID = mptdtv.TO_ORGANIZATION_ID
1078 and msi.INVENTORY_ITEM_ID = mptdtv.INVENTORY_ITEM_ID
1079 and base.subinventory_code = g_subinventory_code
1080 order by WMS_Parameter_PVT.GetItemOnHand(
1081 base.ORGANIZATION_ID
1082 ,mptdtv.INVENTORY_ITEM_ID
1083 ,base.SUBINVENTORY_CODE
1084 ,base.LOCATOR_ID
1085 ,msi.PRIMARY_UOM_CODE
1086 ,mptdtv.TRANSACTION_UOM ) desc
1087 ,base.PROJECT_ID
1088 ,base.TASK_ID
1089 ;
1090
1091 Else
1092 --if subinventory and locator passed, OPEN c_loc_restrict_loc_passed;
1093 OPEN p_cursor FOR select base.SUBINVENTORY_CODE
1094 ,base.LOCATOR_ID
1095 ,base.PROJECT_ID
1096 ,base.TASK_ID
1097 from MTL_SYSTEM_ITEMS msi
1098 ,WMS_TRX_DETAILS_TMP_V mptdtv
1099 ,(
1100 select mil.subinventory_code subinventory_code
1101 ,mil.inventory_location_id locator_id
1102 ,mil.organization_id organization_id
1103 ,mil.project_id project_id
1104 ,mil.task_id task_id
1105 ,g_inventory_item_id inventory_item_id
1106 ,mil.location_current_units location_current_units
1107 ,mil.inventory_item_id locator_inventory_item_id
1108 ,mil.empty_flag empty_flag
1109 ,mil.mixed_items_flag mixed_items_flag
1110 ,mil.LAST_UPDATE_DATE
1111 ,mil.LAST_UPDATED_BY
1112 ,mil.CREATION_DATE
1113 ,mil.CREATED_BY
1114 ,mil.LAST_UPDATE_LOGIN
1115 ,mil.DESCRIPTION
1116 ,mil.DESCRIPTIVE_TEXT
1117 ,mil.DISABLE_DATE
1118 ,mil.INVENTORY_LOCATION_TYPE
1119 ,mil.PICKING_ORDER
1120 ,mil.PHYSICAL_LOCATION_CODE
1121 ,mil.LOCATION_MAXIMUM_UNITS
1122 ,mil.LOCATION_WEIGHT_UOM_CODE
1123 ,mil.MAX_WEIGHT
1124 ,mil.VOLUME_UOM_CODE
1125 ,mil.MAX_CUBIC_AREA
1126 ,mil.X_COORDINATE
1127 ,mil.Y_COORDINATE
1128 ,mil.Z_COORDINATE
1129 ,mil.INVENTORY_ACCOUNT_ID
1130 ,mil.SEGMENT1
1131 ,mil.SEGMENT2
1132 ,mil.SEGMENT3
1133 ,mil.SEGMENT4
1134 ,mil.SEGMENT5
1135 ,mil.SEGMENT6
1136 ,mil.SEGMENT7
1137 ,mil.SEGMENT8
1138 ,mil.SEGMENT9
1139 ,mil.SEGMENT10
1140 ,mil.SEGMENT11
1141 ,mil.SEGMENT12
1142 ,mil.SEGMENT13
1143 ,mil.SEGMENT14
1144 ,mil.SEGMENT15
1145 ,mil.SEGMENT16
1146 ,mil.SEGMENT17
1147 ,mil.SEGMENT18
1148 ,mil.SEGMENT19
1149 ,mil.SEGMENT20
1150 ,mil.SUMMARY_FLAG
1151 ,mil.ENABLED_FLAG
1152 ,mil.START_DATE_ACTIVE
1156 ,mil.ATTRIBUTE2
1153 ,mil.END_DATE_ACTIVE
1154 ,mil.ATTRIBUTE_CATEGORY
1155 ,mil.ATTRIBUTE1
1157 ,mil.ATTRIBUTE3
1158 ,mil.ATTRIBUTE4
1159 ,mil.ATTRIBUTE5
1160 ,mil.ATTRIBUTE6
1161 ,mil.ATTRIBUTE7
1162 ,mil.ATTRIBUTE8
1163 ,mil.ATTRIBUTE9
1164 ,mil.ATTRIBUTE10
1165 ,mil.ATTRIBUTE11
1166 ,mil.ATTRIBUTE12
1167 ,mil.ATTRIBUTE13
1168 ,mil.ATTRIBUTE14
1169 ,mil.ATTRIBUTE15
1170 ,mil.REQUEST_ID
1171 ,mil.PROGRAM_APPLICATION_ID
1172 ,mil.PROGRAM_ID
1173 ,mil.PROGRAM_UPDATE_DATE
1174 ,mil.PHYSICAL_LOCATION_ID
1175 ,mil.PICK_UOM_CODE
1176 ,mil.DIMENSION_UOM_CODE
1177 ,mil.LENGTH
1178 ,mil.WIDTH
1179 ,mil.HEIGHT
1180 ,mil.LOCATOR_STATUS
1181 ,mil.STATUS_ID
1182 ,mil.CURRENT_CUBIC_AREA
1183 ,mil.AVAILABLE_CUBIC_AREA
1184 ,mil.CURRENT_WEIGHT
1185 ,mil.AVAILABLE_WEIGHT
1186 ,mil.LOCATION_AVAILABLE_UNITS
1187 ,mil.SUGGESTED_CUBIC_AREA
1188 ,mil.SUGGESTED_WEIGHT
1189 ,mil.LOCATION_SUGGESTED_UNITS
1190 ,mil.rowid
1191 from MTL_ITEM_LOCATIONS mil
1192 ,MTL_SECONDARY_INVENTORIES msei
1193 ,MTL_ITEM_SUB_INVENTORIES misi
1194 ,MTL_SECONDARY_LOCATORS msl
1195 where mil.organization_id = msei.organization_id
1196 and mil.subinventory_code = msei.secondary_inventory_name
1197 and NVL(msei.disable_date, sysdate+1) > sysdate
1198 and NVL(mil.disable_date, sysdate+1) > sysdate
1199 and mil.organization_id = misi.organization_id
1200 and mil.subinventory_code = misi.secondary_inventory
1201 and misi.inventory_item_id = g_inventory_item_id
1202 and mil.organization_id = msl.organization_id
1203 and mil.inventory_location_id = msl.secondary_locator
1204 and msl.inventory_item_Id = g_inventory_item_id
1205 )base
1206 where base.ORGANIZATION_ID = g_organization_id
1207 and base.INVENTORY_ITEM_ID = g_inventory_item_id
1208 and (g_project_id = base.project_id OR base.project_id IS NULL)
1209 and (g_task_id = base.task_id OR base.task_id IS NULL)
1210 and mptdtv.PP_TRANSACTION_TEMP_ID = g_pp_transaction_temp_id
1211 and msi.ORGANIZATION_ID = mptdtv.TO_ORGANIZATION_ID
1212 and msi.INVENTORY_ITEM_ID = mptdtv.INVENTORY_ITEM_ID
1213 and base.subinventory_code = g_subinventory_code
1214 and base.locator_id = g_locator_id
1215 order by WMS_Parameter_PVT.GetItemOnHand(
1216 base.ORGANIZATION_ID
1217 ,mptdtv.INVENTORY_ITEM_ID
1218 ,base.SUBINVENTORY_CODE
1219 ,base.LOCATOR_ID
1220 ,msi.PRIMARY_UOM_CODE
1221 ,mptdtv.TRANSACTION_UOM ) desc
1222 ,base.PROJECT_ID
1223 ,base.TASK_ID
1224 ;
1225 End If;
1226 END IF;
1227
1228 x_result :=1;
1229
1230 END open_curs;
1231
1232 PROCEDURE fetch_one_row(
1233 p_cursor IN WMS_RULE_PVT.cv_put_type,
1234 x_subinventory_code OUT NOCOPY VARCHAR2,
1235 x_locator_id OUT NOCOPY NUMBER,
1236 x_project_id OUT NOCOPY NUMBER,
1237 x_task_id OUT NOCOPY NUMBER,
1238 x_return_status OUT NOCOPY NUMBER) IS
1239
1240
1241 BEGIN
1242 if p_cursor%ISOPEN then
1243 FETCH p_cursor INTO
1244 x_subinventory_code, x_locator_id, x_project_id, x_task_id;
1245 IF p_cursor%FOUND THEN
1246 x_return_status := 1;
1247 ELSE
1248 x_return_status := 0;
1249 END IF;
1250 else
1251 x_return_status := 0;
1252 end if;
1253
1254 END fetch_one_row;
1255
1256 PROCEDURE close_curs( p_cursor IN WMS_RULE_PVT.cv_put_type) IS
1257 BEGIN
1258 if p_cursor%ISOPEN then
1259 CLOSE p_cursor;
1260 end if;
1261 END close_curs;
1262
1263 END WMS_RULE_10;