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