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