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