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