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