[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 l_allow_expired_lot_txn NUMBER := 0;
45
46
47 BEGIN
48 g_organization_id :=p_organization_id;
49 g_inventory_item_id := p_inventory_item_id;
50 g_transaction_type_id := p_transaction_type_id;
51 g_revision := p_revision;
52 g_lot_number := p_lot_number;
53 g_subinventory_code :=p_subinventory_code;
54 g_locator_id := p_locator_id;
55 g_cost_group_id := p_cost_group_id;
56 g_pp_transaction_temp_id := p_pp_transaction_temp_id;
57 g_serial_control:= p_serial_controlled;
58 g_detail_serial := p_detail_serial;
59 g_detail_any_serial := p_detail_any_serial;
60 g_from_serial_number := p_from_serial_number;
61 g_to_serial_number := p_to_serial_number;
62 g_unit_number := p_unit_number;
63 g_lpn_id := p_lpn_id;
64 g_project_id := p_project_id;
65 g_task_id := p_task_id;
66
67 IF (g_serial_control = 1) AND (g_detail_serial in (1,2)) THEN
68 OPEN p_cursor FOR select base.REVISION
69 ,base.LOT_NUMBER
70 ,base.LOT_EXPIRATION_DATE
71 ,base.SUBINVENTORY_CODE
75 ,decode(g_lpn_id, -9999, NULL, g_lpn_id) LPN_ID
72 ,base.LOCATOR_ID
73 ,base.COST_GROUP_ID
74 ,base.UOM_CODE
76 ,base.SERIAL_NUMBER
77 ,base.primary_quantity
78 ,base.secondary_quantity
79 ,base.grade_code
80 ,NULL consist_string
81 ,NULL order_by_string
82 from
83 MTL_LOT_NUMBERS mlna ,
84 WMS_TRX_DETAILS_TMP_V mptdtv
85 ,(
86 select msn.current_organization_id organization_id
87 ,msn.inventory_item_id
88 ,msn.revision
89 ,msn.lot_number
90 ,lot.expiration_date lot_expiration_date
91 ,msn.current_subinventory_code subinventory_code
92 ,msn.current_locator_id locator_id
93 ,msn.cost_group_id
94 ,msn.status_id --added status_id
95 ,msn.serial_number
96 ,msn.initialization_date date_received
97 ,1 primary_quantity
98 ,null secondary_quantity -- new
99 ,lot.grade_code grade_code -- new
100 ,sub.reservable_type
101 ,nvl(loc.reservable_type,1) locreservable -- Bug 6719290
102 ,nvl(lot.reservable_type,1) lotreservable -- Bug 6719290
103 ,nvl(loc.pick_uom_code, sub.pick_uom_code) uom_code
104 ,WMS_Rule_PVT.GetConversionRate(
105 nvl(loc.pick_uom_code, sub.pick_uom_code)
106 ,msn.current_organization_id
107 ,msn.inventory_item_id) conversion_rate
108 ,msn.lpn_id lpn_id
109 ,loc.project_id project_id
110 ,loc.task_id task_id
111 ,NULL locator_inventory_item_id
112 ,NULL empty_flag
113 ,NULL location_current_units
114 from mtl_serial_numbers msn
115 ,mtl_secondary_inventories sub
116 ,mtl_item_locations loc
117 ,mtl_lot_numbers lot
118 where msn.current_status = 3
119 and decode(g_unit_number, '-9999', 'a', '-7777', nvl(msn.end_item_unit_number, '-7777'), msn.end_item_unit_number) =
120 decode(g_unit_number, '-9999', 'a', g_unit_number)
121 and (msn.group_mark_id IS NULL or msn.group_mark_id = -1)
122 --and (g_detail_serial IN ( 1,2)
123 and ( g_detail_any_serial = 2 or (g_detail_any_serial = 1
124 and g_from_serial_number <= msn.serial_number
125 and lengthb(g_from_serial_number) = lengthb(msn.serial_number)
126 and g_to_serial_number >= msn.serial_number
127 and lengthb(g_to_serial_number) = lengthb(msn.serial_number))
128 or ( g_from_serial_number is null or g_to_serial_number is null)
129 )
130 and sub.organization_id = msn.current_organization_id
131 and sub.secondary_inventory_name = msn.current_subinventory_code
132 and loc.organization_id (+)= msn.current_organization_id
133 and loc.inventory_location_id (+)= msn.current_locator_id
134 and lot.organization_id (+)= msn.current_organization_id
135 and lot.inventory_Item_id (+)= msn.inventory_item_id
136 and lot.lot_number (+)= msn.lot_number
137 )base
138 where base.ORGANIZATION_ID = g_organization_id
139 and base.INVENTORY_ITEM_ID = g_inventory_item_id
140 and decode(g_subinventory_code, '-9999', 'a', base.SUBINVENTORY_CODE) = decode(g_subinventory_code, '-9999', 'a', g_subinventory_code)
141 and decode(g_subinventory_code, '-9999', base.RESERVABLE_TYPE, 1) = 1
142 and decode(g_locator_id, -9999, 1, base.locator_id) = decode(g_locator_id,-9999, 1, g_locator_id)
143 and decode(g_revision, '-99', 'a', base.REVISION) = decode(g_revision, '-99', 'a', g_revision)
144 and decode(g_lot_number, '-9999', 'a', base.LOT_NUMBER) = decode(g_lot_number, '-9999', 'a', g_lot_number)
145 and decode(g_lpn_id, -9999, 1, base.lpn_id) = decode(g_lpn_id, -9999, 1, g_lpn_id)
146 and decode(g_cost_group_id, -9999, 1, base.cost_group_id) = decode(g_cost_group_id, -9999, 1, g_cost_group_id)
147 and mptdtv.PP_TRANSACTION_TEMP_ID = g_pp_transaction_temp_id
148 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
149 and mlna.ORGANIZATION_ID (+) = base.ORGANIZATION_ID
150 and mlna.INVENTORY_ITEM_ID (+) = base.INVENTORY_ITEM_ID
151 and mlna.LOT_NUMBER (+) = base.LOT_NUMBER
152 and ( mlna.EXPIRATION_DATE is NULL OR mlna.EXPIRATION_DATE > sysdate OR wms_rule_pvt.g_allow_expired_lot_txn = 'Y' ) order by decode(base.project_id,g_project_id,1,NULL,2,3) asc,base.SERIAL_NUMBER asc,base.CONVERSION_RATE desc
153 ;
154 Elsif (g_serial_control = 1) AND (g_detail_serial = 3) THEN
155 OPEN p_cursor FOR select base.REVISION
156 ,base.LOT_NUMBER
157 ,base.LOT_EXPIRATION_DATE
158 ,base.SUBINVENTORY_CODE
159 ,base.LOCATOR_ID
160 ,base.COST_GROUP_ID
161 ,base.UOM_CODE
162 ,decode(g_lpn_id, -9999, NULL, g_lpn_id) LPN_ID
163 ,NULL SERIAL_NUMBER
164 ,sum(base.primary_quantity)
165 ,sum(base.secondary_quantity)
166 ,base.grade_code
167 ,NULL consist_string
168 ,NULL order_by_string
169 from
170 MTL_LOT_NUMBERS mlna ,
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
191 ,WMS_Rule_PVT.GetConversionRate(
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
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 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 mptdtv.PP_TRANSACTION_TEMP_ID = g_pp_transaction_temp_id
238 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
239 and mlna.ORGANIZATION_ID (+) = base.ORGANIZATION_ID
240 and mlna.INVENTORY_ITEM_ID (+) = base.INVENTORY_ITEM_ID
241 and mlna.LOT_NUMBER (+) = base.LOT_NUMBER
242 and ( mlna.EXPIRATION_DATE is NULL OR mlna.EXPIRATION_DATE > sysdate OR wms_rule_pvt.g_allow_expired_lot_txn = 'Y' ) group by base.ORGANIZATION_ID
243 ,base.INVENTORY_ITEM_ID
244 ,base.REVISION
245 ,base.LOT_NUMBER
246 ,base.LOT_EXPIRATION_DATE
247 ,base.SUBINVENTORY_CODE
248 ,base.LOCATOR_ID
249 ,base.COST_GROUP_ID
250 ,base.PROJECT_ID
251 ,base.TASK_ID
252 ,base.UOM_CODE
253 ,base.GRADE_CODE
254 ,base.SERIAL_NUMBER,base.CONVERSION_RATE
255 order by decode(base.project_id,g_project_id,1,NULL,2,3) asc,base.SERIAL_NUMBER asc,base.CONVERSION_RATE desc
256 ;
257 Elsif (g_serial_control = 1) AND (g_detail_serial = 4) THEN
258 OPEN p_cursor FOR select base.REVISION
259 ,base.LOT_NUMBER
260 ,base.LOT_EXPIRATION_DATE
261 ,base.SUBINVENTORY_CODE
262 ,base.LOCATOR_ID
263 ,base.COST_GROUP_ID
264 ,base.UOM_CODE
265 ,decode(g_lpn_id, -9999, NULL, g_lpn_id) LPN_ID
266 ,NULL SERIAL_NUMBER
267 ,sum(base.primary_quantity)
268 ,sum(base.secondary_quantity)
269 ,base.grade_code
270 ,NULL consist_string
271 ,NULL order_by_string
272 from
273 MTL_LOT_NUMBERS mlna ,
274 WMS_TRX_DETAILS_TMP_V mptdtv
275 ,(
276 select msn.current_organization_id organization_id
277 ,msn.inventory_item_id
278 ,msn.revision
279 ,msn.lot_number
280 ,lot.expiration_date lot_expiration_date
281 ,msn.current_subinventory_code subinventory_code
282 ,msn.current_locator_id locator_id
283 ,msn.cost_group_id
284 ,msn.status_id --added status_id
285 ,msn.serial_number
286 ,msn.initialization_date date_received
287 ,1 primary_quantity
288 ,null secondary_quantity -- new
289 ,lot.grade_code grade_code -- new
290 ,sub.reservable_type
291 ,nvl(loc.reservable_type,1) locreservable -- Bug 6719290
292 ,nvl(lot.reservable_type,1) lotreservable -- Bug 6719290
293 ,nvl(loc.pick_uom_code, sub.pick_uom_code) uom_code
294 ,WMS_Rule_PVT.GetConversionRate(
295 nvl(loc.pick_uom_code, sub.pick_uom_code)
296 ,msn.current_organization_id
300 ,loc.task_id task_id
297 ,msn.inventory_item_id) conversion_rate
298 ,msn.lpn_id lpn_id
299 ,loc.project_id project_id
301 ,NULL locator_inventory_item_id
302 ,NULL empty_flag
303 ,NULL location_current_units
304 from mtl_serial_numbers msn
305 ,mtl_secondary_inventories sub
306 ,mtl_item_locations loc
307 ,mtl_lot_numbers lot
308 where msn.current_status = 3
309 and decode(g_unit_number, '-9999', 'a', '-7777', nvl(msn.end_item_unit_number, '-7777'), msn.end_item_unit_number) =
310 decode(g_unit_number, '-9999', 'a', g_unit_number)
311 and (msn.group_mark_id IS NULL or msn.group_mark_id = -1)
312 and (g_detail_serial = 4
313 OR(g_detail_any_serial = 1
314 OR (g_from_serial_number <= msn.serial_number
315 AND lengthb(g_from_serial_number) = lengthb(msn.serial_number)
316 AND g_to_serial_number >= msn.serial_number
317 AND lengthb(g_to_serial_number) = lengthb(msn.serial_number)
318 )))
319 and sub.organization_id = msn.current_organization_id
320 and sub.secondary_inventory_name = msn.current_subinventory_code
321 and loc.organization_id (+)= msn.current_organization_id
322 and loc.inventory_location_id (+)= msn.current_locator_id
323 and lot.organization_id (+)= msn.current_organization_id
324 and lot.inventory_Item_id (+)= msn.inventory_item_id
325 and lot.lot_number (+)= msn.lot_number
326 )base
327 where base.ORGANIZATION_ID = g_organization_id
328 and base.INVENTORY_ITEM_ID = g_inventory_item_id
329 and decode(g_subinventory_code, '-9999', 'a', base.SUBINVENTORY_CODE) = decode(g_subinventory_code, '-9999', 'a', g_subinventory_code)
330 and decode(g_subinventory_code, '-9999', base.RESERVABLE_TYPE, 1) = 1
331 and decode(g_locator_id, -9999, 1, base.locator_id) = decode(g_locator_id,-9999, 1, g_locator_id)
332 and decode(g_revision, '-99', 'a', base.REVISION) = decode(g_revision, '-99', 'a', g_revision)
333 and decode(g_lot_number, '-9999', 'a', base.LOT_NUMBER) = decode(g_lot_number, '-9999', 'a', g_lot_number)
334 and decode(g_lpn_id, -9999, 1, base.lpn_id) = decode(g_lpn_id, -9999, 1, g_lpn_id)
335 and decode(g_cost_group_id, -9999, 1, base.cost_group_id) = decode(g_cost_group_id, -9999, 1, g_cost_group_id)
336 and mptdtv.PP_TRANSACTION_TEMP_ID = g_pp_transaction_temp_id
337 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
338 and mlna.ORGANIZATION_ID (+) = base.ORGANIZATION_ID
339 and mlna.INVENTORY_ITEM_ID (+) = base.INVENTORY_ITEM_ID
340 and mlna.LOT_NUMBER (+) = base.LOT_NUMBER
341 and ( mlna.EXPIRATION_DATE is NULL OR mlna.EXPIRATION_DATE > sysdate OR wms_rule_pvt.g_allow_expired_lot_txn = 'Y' ) group by base.ORGANIZATION_ID
342 ,base.INVENTORY_ITEM_ID
343 ,base.REVISION
344 ,base.LOT_NUMBER
345 ,base.LOT_EXPIRATION_DATE
346 ,base.SUBINVENTORY_CODE
347 ,base.LOCATOR_ID
348 ,base.COST_GROUP_ID
349 ,base.PROJECT_ID
350 ,base.TASK_ID
351 ,base.UOM_CODE
352 ,base.GRADE_CODE
353 ,base.SERIAL_NUMBER,base.CONVERSION_RATE
354 order by decode(base.project_id,g_project_id,1,NULL,2,3) asc,base.SERIAL_NUMBER asc,base.CONVERSION_RATE desc
355 ;
356
357 Elsif ((g_serial_control <> 1) OR (g_detail_serial = 0)) THEN
358 OPEN p_cursor FOR select base.REVISION
359 ,base.LOT_NUMBER
360 ,base.LOT_EXPIRATION_DATE
361 ,base.SUBINVENTORY_CODE
362 ,base.LOCATOR_ID
363 ,base.COST_GROUP_ID
364 ,base.UOM_CODE
365 ,decode(g_lpn_id, -9999, NULL, g_lpn_id) LPN_ID
366 ,NULL SERIAL_NUMBER
367 ,sum(base.primary_quantity)
368 ,sum(base.secondary_quantity)
369 ,base.grade_code
370 ,NULL consist_string
371 ,NULL order_by_string
372 from
373 MTL_LOT_NUMBERS mlna ,
374 WMS_TRX_DETAILS_TMP_V mptdtv
375 ,(
376 SELECT x.organization_id organization_id
377 ,x.inventory_item_id inventory_item_id
378 ,x.revision revision
379 ,x.lot_number lot_number
380 ,x.lot_expiration_date lot_expiration_date
381 ,x.subinventory_code subinventory_code
382 ,x.locator_id locator_id
383 ,x.cost_group_id cost_group_id
384 ,x.status_id status_id
385 ,NULL serial_number
386 ,x.lpn_id lpn_id
387 ,x.project_id project_id
388 ,x.task_id task_id
389 ,x.date_received date_received
390 ,x.primary_quantity primary_quantity
391 ,x.secondary_quantity secondary_quantity
392 ,x.grade_code grade_code
393 ,x.reservable_type reservable_type
394 ,x.locreservable locreservable
395 ,x.lotreservable lotreservable
396 ,NVL(loc.pick_uom_code,sub.pick_uom_code) uom_code
397 ,WMS_Rule_PVT.GetConversionRate(
398 NVL(loc.pick_uom_code, sub.pick_uom_code)
399 ,x.organization_id
400 ,x.inventory_item_id) conversion_rate
401 ,NULL locator_inventory_item_id
402 ,NULL empty_flag
403 ,NULL location_current_units
404 FROM (
405 select x.organization_id
409 ,lot.expiration_date lot_expiration_date
406 ,x.inventory_item_id
407 ,x.revision
408 ,x.lot_number
410 ,x.subinventory_code
411 ,sub.reservable_type
412 ,nvl(x.reservable_type,1) locreservable -- Bug 6719290
413 ,nvl(lot.reservable_type,1) lotreservable -- Bug 6719290
414 ,x.locator_id
415 ,x.cost_group_id
416 ,x.status_id --added status_id
417 ,x.date_received date_received
418 ,x.primary_quantity primary_quantity
419 ,x.secondary_quantity secondary_quantity -- new
420 ,lot.grade_code grade_code -- new
421 ,x.lpn_id lpn_id
422 ,x.project_id project_id
423 ,x.task_id task_id
424 from
425 (SELECT
426 moq.organization_id
427 ,moq.inventory_item_id
428 ,moq.revision
429 ,moq.lot_number
430 ,moq.subinventory_code
431 ,moq.locator_id
432 ,moq.cost_group_id
433 ,moq.status_id --added status_id
434 ,mils.reservable_type -- Bug 6719290
435 ,min(NVL(moq.orig_date_received,
436 moq.date_received)) date_received
437 ,sum(moq.primary_transaction_quantity) primary_quantity
438 ,sum(moq.secondary_transaction_quantity) secondary_quantity -- new
439 ,moq.lpn_id lpn_id
440 ,decode(mils.project_id, mils.project_id, moq.project_id) project_id
441 ,decode(mils.task_id, mils.task_id, moq.task_id) task_id
442 FROM
443 mtl_onhand_quantities_detail moq,mtl_item_locations mils
444 WHERE
445 moq.organization_id = g_organization_id
446 AND moq.inventory_item_id = g_inventory_item_id
447 AND moq.organization_id = mils.organization_id (+)
448 AND moq.subinventory_code = mils.subinventory_code (+)
449 AND moq.locator_id = mils.inventory_location_id (+)
450 GROUP BY
451 moq.organization_id, moq.inventory_item_id
452 ,moq.date_received --bug 6648984
453 ,moq.revision, moq.lot_number
454 ,moq.subinventory_code, moq.locator_id --added status_id
455 ,moq.cost_group_id,moq.status_id, mils.reservable_type, moq.lpn_id -- Bug 6719290
456 ,decode(mils.project_id, mils.project_id, moq.project_id)
457 ,decode(mils.task_id, mils.task_id, moq.task_id)
458 HAVING
459 sum(moq.primary_transaction_quantity) > 0 -- high volume project 8546026
460 ) x
461 ,mtl_secondary_inventories sub
462 ,mtl_lot_numbers lot
463 where
464 -- x.primary_quantity > 0 and -- high volume project 8546026
465 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 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 mptdtv.PP_TRANSACTION_TEMP_ID = g_pp_transaction_temp_id
488 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
489 and mlna.ORGANIZATION_ID (+) = base.ORGANIZATION_ID
490 and mlna.INVENTORY_ITEM_ID (+) = base.INVENTORY_ITEM_ID
491 and mlna.LOT_NUMBER (+) = base.LOT_NUMBER
492 and ( mlna.EXPIRATION_DATE is NULL OR mlna.EXPIRATION_DATE > sysdate OR wms_rule_pvt.g_allow_expired_lot_txn = 'Y' ) group by base.ORGANIZATION_ID
493 ,base.INVENTORY_ITEM_ID
494 ,base.REVISION
495 ,base.LOT_NUMBER
496 ,base.LOT_EXPIRATION_DATE
497 ,base.SUBINVENTORY_CODE
498 ,base.LOCATOR_ID
499 ,base.COST_GROUP_ID
500 ,base.PROJECT_ID
501 ,base.TASK_ID
502 ,base.UOM_CODE
503 ,base.GRADE_CODE
504 ,base.SERIAL_NUMBER,base.CONVERSION_RATE
505 order by decode(base.project_id,g_project_id,1,NULL,2,3) asc,base.SERIAL_NUMBER asc,base.CONVERSION_RATE desc
506 ;
507 END IF;
508
509 x_result :=1;
510
511 END open_curs;
512
513 PROCEDURE fetch_one_row(
514 p_cursor IN WMS_RULE_PVT.cv_pick_type,
515 x_revision OUT NOCOPY VARCHAR2,
516 x_lot_number OUT NOCOPY VARCHAR2,
517 x_lot_expiration_date OUT NOCOPY DATE,
521 x_uom_code OUT NOCOPY VARCHAR2,
518 x_subinventory_code OUT NOCOPY VARCHAR2,
519 x_locator_id OUT NOCOPY NUMBER,
520 x_cost_group_id OUT NOCOPY NUMBER,
522 x_lpn_id OUT NOCOPY NUMBER,
523 x_serial_number OUT NOCOPY VARCHAR2,
524 x_possible_quantity OUT NOCOPY NUMBER,
525 x_sec_possible_quantity OUT NOCOPY NUMBER,
526 x_grade_code OUT NOCOPY VARCHAR2,
527 x_consist_string OUT NOCOPY VARCHAR2,
528 x_order_by_string OUT NOCOPY VARCHAR2,
529 x_return_status OUT NOCOPY NUMBER) IS
530
531
532 BEGIN
533 IF (p_cursor%ISOPEN) THEN
534
535 FETCH p_cursor INTO
536 x_revision
537 , x_lot_number
538 , x_lot_expiration_date
539 , x_subinventory_code
540 , x_locator_id
541 , x_cost_group_id
542 , x_uom_code
543 , x_lpn_id
544 , x_serial_number
545 , x_possible_quantity
546 , x_sec_possible_quantity
547 , x_grade_code
548 , x_consist_string
549 , x_order_by_string;
550 IF p_cursor%FOUND THEN
551 x_return_status :=1;
552 ELSE
553 x_return_status :=0;
554 END IF;
555 ELSE
556 x_return_status:=0;
557 END IF;
558
559
560 END fetch_one_row;
561
562 PROCEDURE close_curs( p_cursor IN WMS_RULE_PVT.cv_pick_type) IS
563 BEGIN
564 if (p_cursor%ISOPEN) THEN
565 CLOSE p_cursor;
566 END IF;
567 END close_curs;
568
569 -- LG convergence new procedure for the new manual picking select screen
570 PROCEDURE fetch_available_rows(
571 p_cursor IN WMS_RULE_PVT.cv_pick_type,
572 x_return_status OUT NOCOPY NUMBER) IS
573
574 /* Fix for Bug#8360804 . Added temp variable of type available_inventory_tbl */
575
576 l_available_inv_tbl WMS_SEARCH_ORDER_GLOBALS_PVT.available_inventory_tbl;
577 l_count number ;
578
579
580 BEGIN
581 IF (p_cursor%ISOPEN) THEN
582
583 /* Fix for bug#8360804. Collect into temp variable and then add it to g_available_inv_tbl */
584
585 FETCH p_cursor bulk collect INTO l_available_inv_tbl;
586
587 IF p_cursor%FOUND THEN
588 x_return_status :=1;
589 ELSE
590 x_return_status :=0;
591 END IF;
592
593 IF (WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl.exists(1)) THEN
594
595 l_count := WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl.LAST ;
596
597 FOR i in l_available_inv_tbl.FIRST..l_available_inv_tbl.LAST LOOP
598 l_count := l_count + 1 ;
599 WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl(l_count) := l_available_inv_tbl(i) ;
600 END LOOP ;
601
602 ELSE
603 WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl := l_available_inv_tbl ;
604 END IF ;
605 ELSE
606 x_return_status:=0;
607 END IF;
608
609
610 END fetch_available_rows;
611
612 -- end LG convergence
613
614 END WMS_RULE_14;