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