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