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