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