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