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