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