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