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