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