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