[Home] [Help]
PACKAGE BODY: APPS.WMS_RULE_18
Source
1 PACKAGE BODY WMS_RULE_18 AS
2
3 PROCEDURE open_curs
4 (
5 p_cursor IN OUT NOCOPY WMS_RULE_PVT.cv_pick_type,
6 p_organization_id IN NUMBER,
7 p_inventory_item_id IN NUMBER,
8 p_transaction_type_id IN NUMBER,
9 p_revision IN VARCHAR2,
10 p_lot_number IN VARCHAR2,
11 p_subinventory_code IN VARCHAR2,
12 p_locator_id IN NUMBER,
13 p_cost_group_id IN NUMBER,
14 p_pp_transaction_temp_id IN NUMBER,
15 p_serial_controlled IN NUMBER,
16 p_detail_serial IN NUMBER,
17 p_detail_any_serial IN NUMBER,
18 p_from_serial_number IN VARCHAR2,
19 p_to_serial_number IN VARCHAR2,
20 p_unit_number IN VARCHAR2,
21 p_lpn_id IN NUMBER,
22 p_project_id IN NUMBER,
23 p_task_id IN NUMBER,
24 x_result OUT NOCOPY NUMBER
25 ) IS
26 g_organization_id NUMBER;
27 g_inventory_item_id NUMBER;
28 g_transaction_type_id NUMBER;
29 g_revision VARCHAR2(3);
30 g_lot_number VARCHAR2(80);
31 g_subinventory_code VARCHAR2(10);
32 g_locator_id NUMBER;
33 g_cost_group_id NUMBER;
34 g_pp_transaction_temp_id NUMBER;
35 g_serial_control NUMBER;
36 g_detail_serial NUMBER;
37 g_detail_any_serial NUMBER;
38 g_from_serial_number VARCHAR2(30);
39 g_to_serial_number VARCHAR2(30);
40 g_unit_number VARCHAR2(30);
41 g_lpn_id NUMBER;
42 g_project_id NUMBER;
43 g_task_id NUMBER;
44
45
46 BEGIN
47 g_organization_id :=p_organization_id;
48 g_inventory_item_id := p_inventory_item_id;
49 g_transaction_type_id := p_transaction_type_id;
50 g_revision := p_revision;
51 g_lot_number := p_lot_number;
52 g_subinventory_code :=p_subinventory_code;
53 g_locator_id := p_locator_id;
54 g_cost_group_id := p_cost_group_id;
55 g_pp_transaction_temp_id := p_pp_transaction_temp_id;
56 g_serial_control:= p_serial_controlled;
57 g_detail_serial := p_detail_serial;
58 g_detail_any_serial := p_detail_any_serial;
59 g_from_serial_number := p_from_serial_number;
60 g_to_serial_number := p_to_serial_number;
61 g_unit_number := p_unit_number;
62 g_lpn_id := p_lpn_id;
63 g_project_id := p_project_id;
64 g_task_id := p_task_id;
65
66 IF (g_serial_control = 1) AND (g_detail_serial in (1,2)) THEN
67 OPEN p_cursor FOR select base.REVISION
68 ,base.LOT_NUMBER
69 ,base.LOT_EXPIRATION_DATE
70 ,base.SUBINVENTORY_CODE
71 ,base.LOCATOR_ID
72 ,base.COST_GROUP_ID
73 ,base.UOM_CODE
74 ,decode(g_lpn_id, -9999, NULL, g_lpn_id) LPN_ID
75 ,base.SERIAL_NUMBER
76 ,base.primary_quantity
77 ,base.secondary_quantity
78 ,base.grade_code
79 ,NULL consist_string
80 ,NULL order_by_string
81 from MTL_ITEM_LOCATIONS mil
82 ,WMS_TRX_DETAILS_TMP_V mptdtv
83 ,(
84 select msn.current_organization_id organization_id
85 ,msn.inventory_item_id
86 ,msn.revision
87 ,msn.lot_number
88 ,lot.expiration_date lot_expiration_date
89 ,msn.current_subinventory_code subinventory_code
90 ,msn.current_locator_id locator_id
91 ,msn.cost_group_id
92 ,msn.status_id --added status_id
93 ,msn.serial_number
94 ,msn.initialization_date date_received
95 ,1 primary_quantity
96 ,null secondary_quantity -- new
97 ,lot.grade_code grade_code -- new
98 ,sub.reservable_type
99 ,nvl(loc.reservable_type,1) locreservable -- Bug 6719290
100 ,nvl(lot.reservable_type,1) lotreservable -- Bug 6719290
101 ,nvl(loc.pick_uom_code, sub.pick_uom_code) uom_code
102 ,WMS_Rule_PVT.GetConversionRate(
103 nvl(loc.pick_uom_code, sub.pick_uom_code)
104 ,msn.current_organization_id
105 ,msn.inventory_item_id) conversion_rate
106 ,msn.lpn_id lpn_id
107 ,loc.project_id project_id
108 ,loc.task_id task_id
109 ,NULL locator_inventory_item_id
110 ,NULL empty_flag
111 ,NULL location_current_units
112 from mtl_serial_numbers msn
113 ,mtl_secondary_inventories sub
114 ,mtl_item_locations loc
115 ,mtl_lot_numbers lot
116 where msn.current_status = 3
117 and decode(g_unit_number, '-9999', 'a', '-7777', nvl(msn.end_item_unit_number, '-7777'), msn.end_item_unit_number) =
118 decode(g_unit_number, '-9999', 'a', g_unit_number)
119 and (msn.group_mark_id IS NULL or msn.group_mark_id = -1)
120 --and (g_detail_serial IN ( 1,2)
121 and ( g_detail_any_serial = 2 or (g_detail_any_serial = 1
122 and g_from_serial_number <= msn.serial_number
123 and lengthb(g_from_serial_number) = lengthb(msn.serial_number)
124 and g_to_serial_number >= msn.serial_number
125 and lengthb(g_to_serial_number) = lengthb(msn.serial_number))
126 or ( g_from_serial_number is null or g_to_serial_number is null)
127 )
128 and sub.organization_id = msn.current_organization_id
129 and sub.secondary_inventory_name = msn.current_subinventory_code
130 and loc.organization_id (+)= msn.current_organization_id
131 and loc.inventory_location_id (+)= msn.current_locator_id
132 and lot.organization_id (+)= msn.current_organization_id
133 and lot.inventory_Item_id (+)= msn.inventory_item_id
134 and lot.lot_number (+)= msn.lot_number
135 )base
136 where base.ORGANIZATION_ID = g_organization_id
137 and base.INVENTORY_ITEM_ID = g_inventory_item_id
138 and decode(g_subinventory_code, '-9999', 'a', base.SUBINVENTORY_CODE) = decode(g_subinventory_code, '-9999', 'a', g_subinventory_code)
139 and ((exists (select 1 from mtl_parameters where organization_id = g_organization_id and default_status_id is not null ) AND exists(select 1 from mtl_material_statuses where status_id = base.STATUS_ID AND RESERVABLE_TYPE = 1)) OR (NOT exists(select 1 from mtl_parameters where organization_id = g_organization_id and default_status_id is not NULL) and decode(g_subinventory_code, '-9999', base.RESERVABLE_TYPE, 1) = 1))
140 and decode(g_locator_id, -9999, 1, base.locator_id) = decode(g_locator_id,-9999, 1, g_locator_id)
141 and decode(g_revision, '-99', 'a', base.REVISION) = decode(g_revision, '-99', 'a', g_revision)
142 and decode(g_lot_number, '-9999', 'a', base.LOT_NUMBER) = decode(g_lot_number, '-9999', 'a', g_lot_number)
143 and decode(g_lpn_id, -9999, 1, base.lpn_id) = decode(g_lpn_id, -9999, 1, g_lpn_id)
144 and decode(g_cost_group_id, -9999, 1, base.cost_group_id) = decode(g_cost_group_id, -9999, 1, g_cost_group_id)
145 and (decode(g_project_id, -9999, -1, base.project_id) = decode(g_project_id, -9999, -1, g_project_id) OR ( g_project_id = -7777 and base.project_id IS NULL))
146 and (g_project_id = -9999 OR nvl(base.task_id, -9999) = g_task_id OR (g_task_id = -7777 and base.task_id IS NULL))
147 and mptdtv.PP_TRANSACTION_TEMP_ID = g_pp_transaction_temp_id
148 and mil.ORGANIZATION_ID (+) = base.ORGANIZATION_ID
149 and mil.INVENTORY_LOCATION_ID (+) = base.LOCATOR_ID
150 and (
151 mil.PROJECT_ID IS NULL
152 )
153 order by base.CONVERSION_RATE desc
154 ;
155 Elsif (g_serial_control = 1) AND (g_detail_serial = 3) THEN
156 OPEN p_cursor FOR select base.REVISION
157 ,base.LOT_NUMBER
158 ,base.LOT_EXPIRATION_DATE
159 ,base.SUBINVENTORY_CODE
160 ,base.LOCATOR_ID
161 ,base.COST_GROUP_ID
162 ,base.UOM_CODE
163 ,decode(g_lpn_id, -9999, NULL, g_lpn_id) LPN_ID
164 ,NULL SERIAL_NUMBER
165 ,sum(base.primary_quantity)
166 ,sum(base.secondary_quantity)
167 ,base.grade_code
168 ,NULL consist_string
169 ,NULL order_by_string
170 from MTL_ITEM_LOCATIONS mil
171 ,WMS_TRX_DETAILS_TMP_V mptdtv
172 ,(
173 select msn.current_organization_id organization_id
174 ,msn.inventory_item_id
175 ,msn.revision
176 ,msn.lot_number
177 ,lot.expiration_date lot_expiration_date
178 ,msn.current_subinventory_code subinventory_code
179 ,msn.current_locator_id locator_id
180 ,msn.cost_group_id
181 ,msn.status_id --added status_id
182 ,msn.serial_number
183 ,msn.initialization_date date_received
184 ,1 primary_quantity
185 ,null secondary_quantity -- new
186 ,lot.grade_code grade_code -- new
187 ,sub.reservable_type
188 ,nvl(loc.reservable_type,1) locreservable -- Bug 6719290
189 ,nvl(lot.reservable_type,1) lotreservable -- Bug 6719290
190 ,nvl(loc.pick_uom_code, sub.pick_uom_code) uom_code
191 ,WMS_Rule_PVT.GetConversionRate(
192 nvl(loc.pick_uom_code, sub.pick_uom_code)
193 ,msn.current_organization_id
194 ,msn.inventory_item_id) conversion_rate
195 ,msn.lpn_id lpn_id
196 ,loc.project_id project_id
197 ,loc.task_id task_id
198 ,NULL locator_inventory_item_id
199 ,NULL empty_flag
200 ,NULL location_current_units
201 from mtl_serial_numbers msn
202 ,mtl_secondary_inventories sub
203 ,mtl_item_locations loc
204 ,mtl_lot_numbers lot
205 where msn.current_status = 3
206 and decode(g_unit_number, '-9999', 'a', '-7777', nvl(msn.end_item_unit_number, '-7777'), msn.end_item_unit_number) =
207 decode(g_unit_number, '-9999', 'a', g_unit_number)
208 and (msn.group_mark_id IS NULL or msn.group_mark_id = -1)
209 and (g_detail_serial = 3
210 OR(g_detail_any_serial = 1
211 OR (g_from_serial_number <= msn.serial_number
212 AND lengthb(g_from_serial_number) = lengthb(msn.serial_number)
213 AND g_to_serial_number >= msn.serial_number
214 AND lengthb(g_to_serial_number) = lengthb(msn.serial_number)
215 )))
216 and sub.organization_id = msn.current_organization_id
217 and sub.secondary_inventory_name = msn.current_subinventory_code
218 and loc.organization_id (+)= msn.current_organization_id
219 and loc.inventory_location_id (+)= msn.current_locator_id
220 and lot.organization_id (+)= msn.current_organization_id
221 and lot.inventory_Item_id (+)= msn.inventory_item_id
222 and lot.lot_number (+)= msn.lot_number
223 and inv_detail_util_pvt.is_serial_trx_allowed(
224 g_transaction_type_id
225 ,msn.current_organization_id
226 ,msn.inventory_item_id
227 ,msn.status_id) = 'Y' )base
228 where base.ORGANIZATION_ID = g_organization_id
229 and base.INVENTORY_ITEM_ID = g_inventory_item_id
230 and decode(g_subinventory_code, '-9999', 'a', base.SUBINVENTORY_CODE) = decode(g_subinventory_code, '-9999', 'a', g_subinventory_code)
231 and ((exists (select 1 from mtl_parameters where organization_id = g_organization_id and default_status_id is not null ) AND exists(select 1 from mtl_material_statuses where status_id = base.STATUS_ID AND RESERVABLE_TYPE = 1)) OR (NOT exists(select 1 from mtl_parameters where organization_id = g_organization_id and default_status_id is not NULL) and decode(g_subinventory_code, '-9999', base.RESERVABLE_TYPE, 1) = 1))
232 and decode(g_locator_id, -9999, 1, base.locator_id) = decode(g_locator_id,-9999, 1, g_locator_id)
233 and decode(g_revision, '-99', 'a', base.REVISION) = decode(g_revision, '-99', 'a', g_revision)
234 and decode(g_lot_number, '-9999', 'a', base.LOT_NUMBER) = decode(g_lot_number, '-9999', 'a', g_lot_number)
235 and decode(g_lpn_id, -9999, 1, base.lpn_id) = decode(g_lpn_id, -9999, 1, g_lpn_id)
236 and decode(g_cost_group_id, -9999, 1, base.cost_group_id) = decode(g_cost_group_id, -9999, 1, g_cost_group_id)
237 and (decode(g_project_id, -9999, -1, base.project_id) = decode(g_project_id, -9999, -1, g_project_id) OR ( g_project_id = -7777 and base.project_id IS NULL))
238 and (g_project_id = -9999 OR nvl(base.task_id, -9999) = g_task_id OR (g_task_id = -7777 and base.task_id IS NULL))
239 and mptdtv.PP_TRANSACTION_TEMP_ID = g_pp_transaction_temp_id
240 and mil.ORGANIZATION_ID (+) = base.ORGANIZATION_ID
241 and mil.INVENTORY_LOCATION_ID (+) = base.LOCATOR_ID
242 and (
243 mil.PROJECT_ID IS NULL
244 )
245 group by base.ORGANIZATION_ID
246 ,base.INVENTORY_ITEM_ID
247 ,base.REVISION
248 ,base.LOT_NUMBER
249 ,base.LOT_EXPIRATION_DATE
250 ,base.SUBINVENTORY_CODE
251 ,base.LOCATOR_ID
252 ,base.COST_GROUP_ID
253 ,base.PROJECT_ID
254 ,base.TASK_ID
255 ,base.UOM_CODE
256 ,base.GRADE_CODE
257 ,base.CONVERSION_RATE
258 order by base.CONVERSION_RATE desc
259 ;
260 Elsif (g_serial_control = 1) AND (g_detail_serial = 4) THEN
261 OPEN p_cursor FOR select base.REVISION
262 ,base.LOT_NUMBER
263 ,base.LOT_EXPIRATION_DATE
264 ,base.SUBINVENTORY_CODE
265 ,base.LOCATOR_ID
266 ,base.COST_GROUP_ID
267 ,base.UOM_CODE
268 ,decode(g_lpn_id, -9999, NULL, g_lpn_id) LPN_ID
269 ,NULL SERIAL_NUMBER
270 ,sum(base.primary_quantity)
271 ,sum(base.secondary_quantity)
272 ,base.grade_code
273 ,NULL consist_string
274 ,NULL order_by_string
275 from MTL_ITEM_LOCATIONS mil
276 ,WMS_TRX_DETAILS_TMP_V mptdtv
277 ,(
278 select msn.current_organization_id organization_id
279 ,msn.inventory_item_id
280 ,msn.revision
281 ,msn.lot_number
282 ,lot.expiration_date lot_expiration_date
286 ,msn.status_id --added status_id
283 ,msn.current_subinventory_code subinventory_code
284 ,msn.current_locator_id locator_id
285 ,msn.cost_group_id
287 ,msn.serial_number
288 ,msn.initialization_date date_received
289 ,1 primary_quantity
290 ,null secondary_quantity -- new
291 ,lot.grade_code grade_code -- new
292 ,sub.reservable_type
293 ,nvl(loc.reservable_type,1) locreservable -- Bug 6719290
294 ,nvl(lot.reservable_type,1) lotreservable -- Bug 6719290
295 ,nvl(loc.pick_uom_code, sub.pick_uom_code) uom_code
296 ,WMS_Rule_PVT.GetConversionRate(
297 nvl(loc.pick_uom_code, sub.pick_uom_code)
298 ,msn.current_organization_id
299 ,msn.inventory_item_id) conversion_rate
300 ,msn.lpn_id lpn_id
301 ,loc.project_id project_id
302 ,loc.task_id task_id
303 ,NULL locator_inventory_item_id
304 ,NULL empty_flag
305 ,NULL location_current_units
306 from mtl_serial_numbers msn
307 ,mtl_secondary_inventories sub
308 ,mtl_item_locations loc
309 ,mtl_lot_numbers lot
310 where msn.current_status = 3
311 and decode(g_unit_number, '-9999', 'a', '-7777', nvl(msn.end_item_unit_number, '-7777'), msn.end_item_unit_number) =
312 decode(g_unit_number, '-9999', 'a', g_unit_number)
313 and (msn.group_mark_id IS NULL or msn.group_mark_id = -1)
314 and (g_detail_serial = 4
315 OR(g_detail_any_serial = 1
316 OR (g_from_serial_number <= msn.serial_number
317 AND lengthb(g_from_serial_number) = lengthb(msn.serial_number)
318 AND g_to_serial_number >= msn.serial_number
319 AND lengthb(g_to_serial_number) = lengthb(msn.serial_number)
320 )))
321 and sub.organization_id = msn.current_organization_id
322 and sub.secondary_inventory_name = msn.current_subinventory_code
323 and loc.organization_id (+)= msn.current_organization_id
324 and loc.inventory_location_id (+)= msn.current_locator_id
325 and lot.organization_id (+)= msn.current_organization_id
326 and lot.inventory_Item_id (+)= msn.inventory_item_id
327 and lot.lot_number (+)= msn.lot_number
328 )base
329 where base.ORGANIZATION_ID = g_organization_id
330 and base.INVENTORY_ITEM_ID = g_inventory_item_id
331 and decode(g_subinventory_code, '-9999', 'a', base.SUBINVENTORY_CODE) = decode(g_subinventory_code, '-9999', 'a', g_subinventory_code)
332 and ((exists (select 1 from mtl_parameters where organization_id = g_organization_id and default_status_id is not null ) AND exists(select 1 from mtl_material_statuses where status_id = base.STATUS_ID AND RESERVABLE_TYPE = 1)) OR (NOT exists(select 1 from mtl_parameters where organization_id = g_organization_id and default_status_id is not NULL) and decode(g_subinventory_code, '-9999', base.RESERVABLE_TYPE, 1) = 1))
333 and decode(g_locator_id, -9999, 1, base.locator_id) = decode(g_locator_id,-9999, 1, g_locator_id)
334 and decode(g_revision, '-99', 'a', base.REVISION) = decode(g_revision, '-99', 'a', g_revision)
335 and decode(g_lot_number, '-9999', 'a', base.LOT_NUMBER) = decode(g_lot_number, '-9999', 'a', g_lot_number)
336 and decode(g_lpn_id, -9999, 1, base.lpn_id) = decode(g_lpn_id, -9999, 1, g_lpn_id)
337 and decode(g_cost_group_id, -9999, 1, base.cost_group_id) = decode(g_cost_group_id, -9999, 1, g_cost_group_id)
338 and (decode(g_project_id, -9999, -1, base.project_id) = decode(g_project_id, -9999, -1, g_project_id) OR ( g_project_id = -7777 and base.project_id IS NULL))
339 and (g_project_id = -9999 OR nvl(base.task_id, -9999) = g_task_id OR (g_task_id = -7777 and base.task_id IS NULL))
340 and mptdtv.PP_TRANSACTION_TEMP_ID = g_pp_transaction_temp_id
341 and mil.ORGANIZATION_ID (+) = base.ORGANIZATION_ID
342 and mil.INVENTORY_LOCATION_ID (+) = base.LOCATOR_ID
343 and (
344 mil.PROJECT_ID IS NULL
345 )
346 group by base.ORGANIZATION_ID
347 ,base.INVENTORY_ITEM_ID
348 ,base.REVISION
349 ,base.LOT_NUMBER
350 ,base.LOT_EXPIRATION_DATE
351 ,base.SUBINVENTORY_CODE
352 ,base.LOCATOR_ID
353 ,base.COST_GROUP_ID
354 ,base.PROJECT_ID
355 ,base.TASK_ID
356 ,base.UOM_CODE
357 ,base.GRADE_CODE
358 ,base.CONVERSION_RATE
359 order by base.CONVERSION_RATE desc
360 ;
361
362 Elsif ((g_serial_control <> 1) OR (g_detail_serial = 0)) THEN
363 OPEN p_cursor FOR select base.REVISION
364 ,base.LOT_NUMBER
365 ,base.LOT_EXPIRATION_DATE
366 ,base.SUBINVENTORY_CODE
367 ,base.LOCATOR_ID
368 ,base.COST_GROUP_ID
369 ,base.UOM_CODE
370 ,decode(g_lpn_id, -9999, NULL, g_lpn_id) LPN_ID
371 ,NULL SERIAL_NUMBER
372 ,sum(base.primary_quantity)
373 ,sum(base.secondary_quantity)
374 ,base.grade_code
375 ,NULL consist_string
376 ,NULL order_by_string
377 from MTL_ITEM_LOCATIONS mil
378 ,WMS_TRX_DETAILS_TMP_V mptdtv
379 ,(
380 SELECT x.organization_id organization_id
381 ,x.inventory_item_id inventory_item_id
382 ,x.revision revision
383 ,x.lot_number lot_number
384 ,x.lot_expiration_date lot_expiration_date
385 ,x.subinventory_code subinventory_code
386 ,x.locator_id locator_id
387 ,x.cost_group_id cost_group_id
391 ,x.project_id project_id
388 ,x.status_id status_id
389 ,NULL serial_number
390 ,x.lpn_id lpn_id
392 ,x.task_id task_id
393 ,x.date_received date_received
394 ,x.primary_quantity primary_quantity
395 ,x.secondary_quantity secondary_quantity
396 ,x.grade_code grade_code
397 ,x.reservable_type reservable_type
398 ,x.locreservable locreservable
399 ,x.lotreservable lotreservable
400 ,NVL(loc.pick_uom_code,sub.pick_uom_code) uom_code
401 ,WMS_Rule_PVT.GetConversionRate(
402 NVL(loc.pick_uom_code, sub.pick_uom_code)
403 ,x.organization_id
404 ,x.inventory_item_id) conversion_rate
405 ,NULL locator_inventory_item_id
406 ,NULL empty_flag
407 ,NULL location_current_units
408 FROM (
409 select x.organization_id
410 ,x.inventory_item_id
411 ,x.revision
412 ,x.lot_number
413 ,lot.expiration_date lot_expiration_date
414 ,x.subinventory_code
415 ,sub.reservable_type
416 ,nvl(x.reservable_type,1) locreservable -- Bug 6719290
417 ,nvl(lot.reservable_type,1) lotreservable -- Bug 6719290
418 ,x.locator_id
419 ,x.cost_group_id
420 ,x.status_id --added status_id
421 ,x.date_received date_received
422 ,x.primary_quantity primary_quantity
423 ,x.secondary_quantity secondary_quantity -- new
424 ,lot.grade_code grade_code -- new
425 ,x.lpn_id lpn_id
426 ,x.project_id project_id
427 ,x.task_id task_id
428 from
429 (SELECT
430 moq.organization_id
431 ,moq.inventory_item_id
432 ,moq.revision
433 ,moq.lot_number
434 ,moq.subinventory_code
435 ,moq.locator_id
436 ,moq.cost_group_id
437 ,moq.status_id --added status_id
438 ,mils.reservable_type -- Bug 6719290
439 ,min(NVL(moq.orig_date_received,
440 moq.date_received)) date_received
441 ,sum(moq.primary_transaction_quantity) primary_quantity
442 ,sum(moq.secondary_transaction_quantity) secondary_quantity -- new
443 ,moq.lpn_id lpn_id
444 ,decode(mils.project_id, mils.project_id, moq.project_id) project_id
445 ,decode(mils.task_id, mils.task_id, moq.task_id) task_id
446 FROM
447 mtl_onhand_quantities_detail moq,mtl_item_locations mils
448 WHERE
449 moq.organization_id = g_organization_id
450 AND moq.inventory_item_id = g_inventory_item_id
451 AND moq.organization_id = mils.organization_id (+)
452 AND moq.subinventory_code = mils.subinventory_code (+)
453 AND moq.locator_id = mils.inventory_location_id (+)
454 GROUP BY
455 moq.organization_id, moq.inventory_item_id
456 ,moq.revision, moq.lot_number
457 ,moq.subinventory_code, moq.locator_id --added status_id
458 ,moq.cost_group_id,moq.status_id, mils.reservable_type, moq.lpn_id -- Bug 6719290
459 ,decode(mils.project_id, mils.project_id, moq.project_id)
460 ,decode(mils.task_id, mils.task_id, moq.task_id)
461 ) x
462 ,mtl_secondary_inventories sub
463 ,mtl_lot_numbers lot
464 where x.primary_quantity > 0
465 and x.organization_id = sub.organization_id
466 and x.subinventory_code = sub.secondary_inventory_name
467 and x.organization_id = lot.organization_id (+)
468 and x.inventory_item_id = lot.inventory_item_id (+)
469 and x.lot_number = lot.lot_number (+)
470 ) x
471 ,mtl_secondary_inventories sub
472 ,mtl_item_locations loc
473 WHERE x.organization_id = loc.organization_id (+)
474 AND x.locator_id = loc.inventory_location_id (+)
475 AND sub.organization_id = x.organization_id
476 AND sub.secondary_inventory_name = x.subinventory_code
477 ) base
478 where base.ORGANIZATION_ID = g_organization_id
479 and base.INVENTORY_ITEM_ID = g_inventory_item_id
480 and decode(g_subinventory_code, '-9999', 'a', base.SUBINVENTORY_CODE) = decode(g_subinventory_code, '-9999', 'a', g_subinventory_code)
481 and ((exists (select 1 from mtl_parameters where organization_id = g_organization_id and default_status_id is not null ) AND exists(select 1 from mtl_material_statuses where status_id = base.STATUS_ID AND RESERVABLE_TYPE = 1)) OR (NOT exists(select 1 from mtl_parameters where organization_id = g_organization_id and default_status_id is not NULL) and decode(g_subinventory_code, '-9999', base.RESERVABLE_TYPE, 1) = 1))
482 and decode(g_locator_id, -9999, 1, base.locator_id) = decode(g_locator_id,-9999, 1, g_locator_id)
483 and decode(g_revision, '-99', 'a', base.REVISION) = decode(g_revision, '-99', 'a', g_revision)
484 and decode(g_lot_number, '-9999', 'a', base.LOT_NUMBER) = decode(g_lot_number, '-9999', 'a', g_lot_number)
485 and decode(g_lpn_id, -9999, 1, base.lpn_id) = decode(g_lpn_id, -9999, 1, g_lpn_id)
486 and decode(g_cost_group_id, -9999, 1, base.cost_group_id) = decode(g_cost_group_id, -9999, 1, g_cost_group_id)
487 and (decode(g_project_id, -9999, -1, base.project_id) = decode(g_project_id, -9999, -1, g_project_id) OR ( g_project_id = -7777 and base.project_id IS NULL))
488 and (g_project_id = -9999 OR nvl(base.task_id, -9999) = g_task_id OR (g_task_id = -7777 and base.task_id IS NULL))
489 and mptdtv.PP_TRANSACTION_TEMP_ID = g_pp_transaction_temp_id
490 and mil.ORGANIZATION_ID (+) = base.ORGANIZATION_ID
491 and mil.INVENTORY_LOCATION_ID (+) = base.LOCATOR_ID
492 and (
493 mil.PROJECT_ID IS NULL
494 )
495 group by base.ORGANIZATION_ID
496 ,base.INVENTORY_ITEM_ID
497 ,base.REVISION
498 ,base.LOT_NUMBER
499 ,base.LOT_EXPIRATION_DATE
500 ,base.SUBINVENTORY_CODE
501 ,base.LOCATOR_ID
502 ,base.COST_GROUP_ID
503 ,base.PROJECT_ID
504 ,base.TASK_ID
505 ,base.UOM_CODE
506 ,base.GRADE_CODE
507 ,base.CONVERSION_RATE
508 order by base.CONVERSION_RATE desc
509 ;
510 END IF;
511
512 x_result :=1;
513
514 END open_curs;
515
516 PROCEDURE fetch_one_row(
520 x_lot_expiration_date OUT NOCOPY DATE,
517 p_cursor IN WMS_RULE_PVT.cv_pick_type,
518 x_revision OUT NOCOPY VARCHAR2,
519 x_lot_number OUT NOCOPY VARCHAR2,
521 x_subinventory_code OUT NOCOPY VARCHAR2,
522 x_locator_id OUT NOCOPY NUMBER,
523 x_cost_group_id OUT NOCOPY NUMBER,
524 x_uom_code OUT NOCOPY VARCHAR2,
525 x_lpn_id OUT NOCOPY NUMBER,
526 x_serial_number OUT NOCOPY VARCHAR2,
527 x_possible_quantity OUT NOCOPY NUMBER,
528 x_sec_possible_quantity OUT NOCOPY NUMBER,
529 x_grade_code OUT NOCOPY VARCHAR2,
530 x_consist_string OUT NOCOPY VARCHAR2,
531 x_order_by_string OUT NOCOPY VARCHAR2,
532 x_return_status OUT NOCOPY NUMBER) IS
533
534
535 BEGIN
536 IF (p_cursor%ISOPEN) THEN
537
538 FETCH p_cursor INTO
539 x_revision
540 , x_lot_number
541 , x_lot_expiration_date
542 , x_subinventory_code
543 , x_locator_id
544 , x_cost_group_id
545 , x_uom_code
546 , x_lpn_id
547 , x_serial_number
548 , x_possible_quantity
549 , x_sec_possible_quantity
550 , x_grade_code
551 , x_consist_string
552 , x_order_by_string;
553 IF p_cursor%FOUND THEN
554 x_return_status :=1;
555 ELSE
556 x_return_status :=0;
557 END IF;
558 ELSE
559 x_return_status:=0;
560 END IF;
561
562
563 END fetch_one_row;
564
565 PROCEDURE close_curs( p_cursor IN WMS_RULE_PVT.cv_pick_type) IS
566 BEGIN
567 if (p_cursor%ISOPEN) THEN
568 CLOSE p_cursor;
569 END IF;
570 END close_curs;
571
572 -- LG convergence new procedure for the new manual picking select screen
573 PROCEDURE fetch_available_rows(
574 p_cursor IN WMS_RULE_PVT.cv_pick_type,
575 x_return_status OUT NOCOPY NUMBER) IS
576
577
578 BEGIN
579 IF (p_cursor%ISOPEN) THEN
580
581 FETCH p_cursor bulk collect INTO
582 WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl;
583 IF p_cursor%FOUND THEN
584 x_return_status :=1;
585 ELSE
586 x_return_status :=0;
587 END IF;
588 ELSE
589 x_return_status:=0;
590 END IF;
591
592
593 END fetch_available_rows;
594
595 -- end LG convergence
596
597 END WMS_RULE_18;