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