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