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