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