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