[Home] [Help]
PACKAGE BODY: APPS.GME_MOBILE_LOVS
Source
1 PACKAGE BODY GME_MOBILE_LOVS AS
2 /* $Header: GMEMLOVB.pls 120.41.12020000.2 2012/07/26 19:34:26 gmurator ship $ */
3 /*===========================================================================+
4 | Copyright (c) 2005 Oracle Corporation, Redwood Shores, CA, USA |
5 | All rights reserved. |
6 |===========================================================================|
7 | |
8 | PL/SQL Package to support the (Java) GME Mobile Application. |
9 | Contains PL/SQL cursors used to fetch data in the mobile LOVs. |
10 | |
11 +===========================================================================+
12 | HISTORY |
13 | |
14 | Date Who What |
15 | ==== === ==== |
16 | 26-Apr-05 Eddie Oumerretane First version |
17 | 31-May-06 Eddie Oumerretane Bug 5236930 Removed character '%' in where|
18 | clause of all LOVs |
19 | 09-Jun-06 Namit Singhi. Bug#5236906. Defined 2 new procs - |
20 | Step_Material_Lot_Line_LoV and Material_Lot_LoV. Show only|
21 | lot controlled items for Create/Pending Lots. |
22 | 21-Jun-06 Shrikant Nene Bug#5263908. Defined 1 new procs - |
23 | Revision_LoV. Also added the revision field in 3 procs |
24 | Subinventory_LoV_Dispense |
25 | Locator_LoV_Dispense |
26 | Lot_LoV_Dispense |
27 | 21-Aug-06 Shrikant Nene Bug#5263908. Defined 1 new procs - |
28 | Pending_Parent_Lot_LoV |
29 | 15-Sep-06 SivakumarG Bug#5261131 |
30 | Added procedure Lot_OnHand_Exp_LoV to filter based on |
31 | expiration date | |
32 |29-JUL-08 Swapna K Bug#7253370 |
33 | Added NVL for the secondary_reservation_quantity in the |
34 | Rsrv_Lov procedure.
35 |26-OCT-09 Swapna K Bug#8937312
36 | Added parameter,p_transaction_type to the
37 | procedure,Lot_OnHand_Exp_LoV and changed the where clause to|
38 | consider the lot status |
39 |16-Apr-10 APMISHRA |
40 | Modified the cussors in the procedures rsrv_lov and |
41 | Dispense_LoV to select LPN number |
42
43 |17-Jun-11 Apeksha Mishra Bug 12562057 |
44 | Remove explicit parameter names as it does |
45 | not compile on versions below 11g |
46 +===========================================================================*/
47
48 --- For GTIN support
49 g_gtin_cross_ref_type VARCHAR2(25) := fnd_profile.value('INV:GTIN_CROSS_REFERENCE_TYPE');
50 g_gtin_code_length NUMBER := 14;
51
52 g_debug VARCHAR2 (5) := fnd_profile.VALUE ('AFLOG_LEVEL');
53
54 /* Bug#5663458 Begin
55 * Created the following procedure. This procedure is to get all reservations
56 * from material line. Used for Use Rsrv field in mobile
57 */
58 PROCEDURE Rsrv_LoV
59 ( x_batch_cursor OUT NOCOPY t_genref
60 , p_org_id IN NUMBER
61 , p_batch_id IN NUMBER
62 , p_material_detail_id IN NUMBER
63 )
64 IS
65 l_date_format VARCHAR2(100);
66 BEGIN
67 FND_PROFILE.GET('MWA_DATE_FORMAT_MASK',l_date_format);
68
69 IF l_date_format IS NULL THEN
70 FND_PROFILE.GET('ICX_DATE_FORMAT_MASK',l_date_format);
71 END IF;
72
73 --
74 -- bug 9483781
75 -- Modified the cursor definition to also
76 -- select the LPN number to be displayed in
77 -- in the LOV
78 --
79 OPEN x_batch_cursor FOR
80 SELECT NVL(mr.revision,' '),
81 NVL(mr.lot_number,' '),
82 NVL(mr.subinventory_code,' '),
83 NVL(lo.concatenated_segments,' ') locator,
84 mr.reservation_quantity,
85 mr.reservation_uom_code,
86 TO_CHAR(mr.requirement_date,l_date_format),
87 NVL(RTRIM(lpn.LICENSE_PLATE_NUMBER),' '), /*Bug9483781*/
88 nvl(mr.secondary_reservation_quantity,0), /*Bug7041074*/
89 mr.reservation_id,
90 lo.inventory_location_id
91 FROM mtl_reservations mr,
92 wms_item_locations_kfv lo,
93 wms_license_plate_numbers lpn
94 WHERE lo.inventory_location_id(+) = mr.locator_id
95 AND mr.organization_id = p_org_id
96 AND mr.demand_source_type_id = 5
97 AND mr.demand_source_header_id = p_batch_id
98 AND mr.demand_source_line_id = p_material_detail_id
99 AND LPN.lpn_id(+) = mr.lpn_id
100 AND NOT EXISTS (SELECT 1
101 FROM mtl_material_transactions_temp
102 WHERE reservation_id = mr.reservation_id)
103 ORDER BY mr.requirement_date, mr.reservation_id;
104
105 END Rsrv_LoV;
106
107 /*
108 * Created the following procedure. This procedure is to get all dispensing records
109 * from material line. Used for Use Rsrv field in mobile
110 */
111 PROCEDURE Dispense_LoV
112 ( x_batch_cursor OUT NOCOPY t_genref
113 )IS
114 l_date_format VARCHAR2(100);
115 BEGIN
116 FND_PROFILE.GET('MWA_DATE_FORMAT_MASK',l_date_format);
117
118 IF l_date_format IS NULL THEN
119 FND_PROFILE.GET('ICX_DATE_FORMAT_MASK',l_date_format);
120 END IF;
121
122 --
123 -- bug 9483781
124 -- Modified the cursor definition to select
125 -- a NULL LPN number. This is done to keep
126 -- this and the rsrv_lov in sync
127 --
128 OPEN x_batch_cursor FOR
129 SELECT NVL(revision,' '),
130 NVL(d.lot_number,' '),
131 NVL(d.subinventory_code,' '),
132 NVL(lo.concatenated_segments,' ') locator,
133 d.dispensed_qty,
134 d.dispense_uom,
135 TO_CHAR(SYSDATE,l_date_format),
136 NULL, /*Bug9483781*/
137 d.secondary_dispensed_qty,
138 d.dispense_id,
139 lo.inventory_location_id
140 FROM gme_material_dispensing_gtmp d,
141 wms_item_locations_kfv lo
142 WHERE lo.inventory_location_id(+) = d.locator_id
143 AND lo.subinventory_code(+) = d.subinventory_code
144 ORDER BY d.dispense_id;
145
146 END Dispense_LoV;
147
148 /*+========================================================================+
149 | PROCEDURE NAME
150 | Batch_LoV
151 |
152 | USAGE
153 |
154 | ARGUMENTS
155 | p_org_id
156 | p_statuses
157 | p_batch_no
158 |
159 | RETURNS
160 | REF cursor x_batch_cursor
161 |
162 | HISTORY
163 | Created 26-Apr-05 Eddie Oumerretane
164 |
165 +========================================================================+*/
166 PROCEDURE Batch_LoV
167 ( x_batch_cursor OUT NOCOPY t_genref
168 , p_org_id IN NUMBER
169 , p_statuses IN VARCHAR2
170 , p_batch_no IN VARCHAR2
171 ) IS
172
173 BEGIN
174 IF p_statuses= 'PENDING_WIP'
175 THEN
176 OPEN x_batch_cursor FOR
177 SELECT batch_no
178 ,meaning
179 ,batch_status
180 ,batch_id
181 ,NVL(formula_id,0)
182 ,NVL(routing_id,0)
183 FROM gme_batch_header b,
184 gem_lookups lkup
185 WHERE organization_id = p_org_id
186 AND batch_type= 0
187 AND batch_status in (1,2)
188 --- Bug 5236930 AND batch_no LIKE LTRIM(RTRIM('%'||p_batch_no||'%'))
189 AND batch_no LIKE p_batch_no
190 AND delete_mark = 0
191 AND lkup.lookup_type = 'GME_BATCH_STATUS'
192 AND lkup.lookup_code = batch_status
193 AND sysdate BETWEEN NVL(lkup.start_date_active, SYSDATE)
194 AND NVL(lkup.end_date_active, sysdate)
195 AND lkup.enabled_flag = 'Y'
196 AND NVL(b.migrated_batch_ind, 'N') <> 'Y'
197 AND NVL(b.migrated_batch_ind, 'N') <> 'Y'
198 AND NVL(b.update_inventory_ind,'N') = 'Y' --Bug#5763793
199 ORDER BY batch_no DESC;
200
201 ELSIF p_statuses = 'WIP' THEN
202
203 OPEN x_batch_cursor FOR
204 SELECT batch_no
205 ,meaning
206 ,batch_status
207 ,batch_id
208 ,NVL(formula_id,0)
209 ,NVL(routing_id,0)
210 FROM gme_batch_header b,
211 gem_lookups lkup
212 WHERE organization_id = p_org_id
213 AND batch_type= 0
214 AND batch_status = 2
215 --- Bug 5236930 AND batch_no LIKE LTRIM(RTRIM('%'||p_batch_no||'%'))
216 AND batch_no LIKE p_batch_no
217 AND delete_mark = 0
218 AND lkup.lookup_type = 'GME_BATCH_STATUS'
219 AND lkup.lookup_code = batch_status
220 AND sysdate BETWEEN NVL(lkup.start_date_active, SYSDATE)
221 AND NVL(lkup.end_date_active, sysdate)
222 AND lkup.enabled_flag = 'Y'
223 AND NVL(b.migrated_batch_ind, 'N') <> 'Y'
224 AND NVL(b.update_inventory_ind,'N') = 'Y' --Bug#5763793
225 ORDER BY batch_no DESC;
226
227 ELSIF p_statuses = 'WIP_COMPLETED' THEN
228
229 OPEN x_batch_cursor FOR
230 SELECT batch_no
231 ,meaning
232 ,batch_status
233 ,batch_id
234 ,NVL(formula_id,0)
235 ,NVL(routing_id,0)
236 FROM gme_batch_header b,
237 gem_lookups lkup
238 WHERE organization_id = p_org_id
239 AND batch_type= 0
240 AND batch_status in (2,3)
241 --- Bug 5236930 AND batch_no LIKE LTRIM(RTRIM('%'||p_batch_no||'%'))
242 AND batch_no LIKE p_batch_no
243 AND delete_mark = 0
244 AND lkup.lookup_type = 'GME_BATCH_STATUS'
245 AND lkup.lookup_code = batch_status
246 AND sysdate BETWEEN NVL(lkup.start_date_active, SYSDATE)
247 AND NVL(lkup.end_date_active, sysdate)
248 AND lkup.enabled_flag = 'Y'
249 AND NVL(b.migrated_batch_ind, 'N') <> 'Y'
250 AND NVL(b.update_inventory_ind,'N') = 'Y' --Bug#5763793
251 ORDER BY batch_no DESC;
252
253 ELSIF p_statuses = 'WIP_COMPLETED_LPN' THEN
254
255 OPEN x_batch_cursor FOR
256 SELECT batch_no
257 ,meaning
258 ,batch_status
259 ,batch_id
260 ,NVL(formula_id,0)
261 ,NVL(routing_id,0)
262 FROM gme_batch_header b,
263 gem_lookups lkup
264 WHERE organization_id = p_org_id
265 AND batch_type= 0
266 AND batch_status in (2,3)
267 --- Bug 5236930 AND batch_no LIKE LTRIM(RTRIM('%'||p_batch_no||'%'))
268 AND batch_no LIKE p_batch_no
269 AND delete_mark = 0
270 AND parentline_id IS NULL
271 AND lkup.lookup_type = 'GME_BATCH_STATUS'
272 AND lkup.lookup_code = batch_status
273 AND sysdate BETWEEN NVL(lkup.start_date_active, SYSDATE)
274 AND NVL(lkup.end_date_active, sysdate)
275 AND lkup.enabled_flag = 'Y'
276 AND NVL(b.migrated_batch_ind, 'N') <> 'Y'
277 AND NVL(b.update_inventory_ind,'N') = 'Y' --Bug#5763793
278 ORDER BY batch_no DESC;
279
280 ELSIF p_statuses = 'UPDATE_RSRC_USAGE' THEN
281
282 OPEN x_batch_cursor FOR
283 SELECT batch_no
284 ,meaning
285 ,batch_status
286 ,batch_id
287 ,NVL(formula_id,0)
288 ,NVL(routing_id,0)
289 FROM gme_batch_header b,
290 gem_lookups lkup
291 WHERE organization_id = p_org_id
292 AND batch_type= 0
293 AND batch_status in (2,3)
294 --- Bug 5236930 AND batch_no LIKE LTRIM(RTRIM('%'||p_batch_no||'%'))
295 AND batch_no LIKE p_batch_no
296 AND delete_mark = 0
297 AND routing_id IS NOT NULL
298 AND routing_id > 0
299 AND automatic_step_calculation = 0
300 AND automatic_step_calculation IN (0,1)
301 AND lkup.lookup_type = 'GME_BATCH_STATUS'
302 AND lkup.lookup_code = batch_status
303 AND sysdate BETWEEN NVL(lkup.start_date_active, SYSDATE)
304 AND NVL(lkup.end_date_active, sysdate)
305 AND lkup.enabled_flag = 'Y'
306 AND NVL(b.migrated_batch_ind, 'N') <> 'Y'
307 AND NVL(b.update_inventory_ind,'N') = 'Y' --Bug#5763793
308 AND EXISTS
309 ( SELECT 1
310 FROM gme_batch_steps
311 WHERE batch_id = b.batch_id
312 AND step_status in (2,3)
313 )
314 UNION
315 SELECT batch_no
316 ,meaning
317 ,batch_status
318 ,batch_id
319 ,NVL(formula_id,0)
320 ,NVL(routing_id,0)
321 FROM gme_batch_header h,
322 gem_lookups lkup
323 WHERE organization_id = p_org_id
324 AND batch_type= 0
325 AND batch_status = 3
326 --- Bug 5236930 AND batch_no LIKE LTRIM(RTRIM('%'||p_batch_no||'%'))
327 AND batch_no LIKE p_batch_no
328 AND routing_id IS NOT NULL
329 AND routing_id > 0
330 AND automatic_step_calculation = 1
331 AND lkup.lookup_type = 'BATCH_STATUS'
332 AND lkup.lookup_code = batch_status
333 AND sysdate BETWEEN NVL(lkup.start_date_active, SYSDATE)
334 AND NVL(lkup.end_date_active, sysdate)
335 AND lkup.enabled_flag = 'Y'
336 AND NVL(h.update_inventory_ind,'N') = 'Y' --Bug#5763793
337 AND EXISTS
338 ( SELECT 1
339 FROM gme_batch_steps
340 WHERE batch_id = h.batch_id
341 AND step_status = 3
342 )
343 ORDER BY batch_no DESC;
344
345 ELSIF p_statuses= 'PENDING_WIP_WITH_ROUTE'
346 THEN
347 OPEN x_batch_cursor FOR
348 SELECT batch_no
349 ,meaning
350 ,batch_status
351 ,batch_id
352 ,NVL(formula_id,0)
353 ,NVL(routing_id,0)
354 FROM gme_batch_header b,
355 gem_lookups lkup
356 WHERE organization_id = p_org_id
357 AND batch_type= 0
358 AND batch_status in (1,2)
359 --- Bug 5236930 AND batch_no LIKE LTRIM(RTRIM('%'||p_batch_no||'%'))
360 AND batch_no LIKE p_batch_no
361 AND delete_mark = 0
362 AND routing_id IS NOT NULL
363 AND lkup.lookup_type = 'GME_BATCH_STATUS'
364 AND lkup.lookup_code = batch_status
365 AND sysdate BETWEEN NVL(lkup.start_date_active, SYSDATE)
366 AND NVL(lkup.end_date_active, sysdate)
367 AND lkup.enabled_flag = 'Y'
368 AND NVL(b.migrated_batch_ind, 'N') <> 'Y'
369 AND NVL(b.update_inventory_ind,'N') = 'Y' --Bug#5763793
370 ORDER BY batch_no DESC;
371
372 END IF;
373
374 EXCEPTION
375 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
376 THEN
377 NULL;
378
379 END Batch_Lov;
380
381 /*+========================================================================+
382 | PROCEDURE NAME
383 | Ingredient_Line_Rsrv_LoV
384 |
385 | USAGE
386 |
387 | ARGUMENTS
388 | p_batch_id
389 | p_line_no
390 | p_item_no
391 |
392 | RETURNS
393 | REF cursor x_line_cursor
394 |
395 | HISTORY
396 | Created 26-Apr-05 Eddie Oumerretane
397 |
398 +========================================================================+*/
399 PROCEDURE Ingredient_Line_Rsrv_LoV
400 ( x_line_cursor OUT NOCOPY t_genref
401 , p_batch_id IN NUMBER
402 , p_line_no IN VARCHAR2
403 , p_item_no IN VARCHAR2
404 )
405 IS
406 BEGIN
407
408 OPEN x_line_cursor FOR
409 SELECT d.line_no,
410 i.concatenated_segments,
411 i.inventory_item_id,
412 d.material_detail_id
413 FROM gme_material_details d,
414 mtl_system_items_kfv i
415 WHERE
416 d.batch_id = p_batch_id
417 AND d.line_type = -1
418 AND d.phantom_type = 0
419 AND i.concatenated_segments = NVL(p_item_no, i.concatenated_segments)
420 AND i.inventory_item_id = d.inventory_item_id
421 AND i.organization_id = d.organization_id
422 AND i.reservable_type = 1
423 AND d.line_no LIKE (p_line_no)
424 AND NOT EXISTS
425 (SELECT 1
426 FROM gme_batch_step_items i2,
427 gme_batch_steps s
428 WHERE i2.batch_id = d.batch_id
429 AND s.batch_id = d.batch_id
430 AND i2.material_detail_id = d.material_detail_id
431 AND i2.batchstep_id = s.batchstep_id
432 AND s.step_status IN (3,4,5))
433 ORDER BY d.line_no;
434
435 END Ingredient_Line_Rsrv_LoV;
436
437 /*+========================================================================+
438 | PROCEDURE NAME
439 | Step_Ingredient_Line_LoV
440 |
441 | USAGE
442 |
443 | ARGUMENTS
444 | p_batch_id
445 | p_line_no
446 | p_step_no
447 |
448 | RETURNS
449 | REF cursor x_line_cursor
450 |
451 | HISTORY
452 | Created 26-Apr-05 Eddie Oumerretane
453 |
454 +========================================================================+*/
455 PROCEDURE Step_Ingredient_Line_LoV
456 ( x_line_cursor OUT NOCOPY t_genref
457 , p_batch_id IN NUMBER
458 , p_line_no IN VARCHAR2
459 , p_step_no IN VARCHAR2
460 ) IS
461 BEGIN
462 Step_Material_Line_LoV(x_line_cursor => x_line_cursor
463 , p_batch_id => p_batch_id
464 , p_line_no => p_line_no
465 , p_step_no => p_step_no
466 , p_line_type => -1);
467
468
469 END Step_Ingredient_Line_LoV;
470
471
472 PROCEDURE Line_Step_LoV
473 ( x_item_cursor OUT NOCOPY t_genref
474 , p_batch_id IN NUMBER
475 , p_line_no IN VARCHAR2
476 , p_item_no IN VARCHAR2
477 , p_step_no IN VARCHAR2
478 , p_line_type IN NUMBER
479 )
480 IS
481 l_cross_ref varchar2(204);
482 BEGIN
483
484 IF (g_debug IS NOT NULL) THEN
485 gme_debug.log_initialize ('MobileLineStepLoV');
486 END IF;
487
488 l_cross_ref := lpad(Rtrim(p_item_no, '%'), g_gtin_code_length,'00000000000000');
489
490
491 gme_debug.put_line('Cross ref type = '||g_gtin_cross_ref_type);
492 gme_debug.put_line('Cross ref = '||l_cross_ref);
493
494 IF p_step_no IS NULL THEN
495
496 OPEN x_item_cursor FOR
497
498 SELECT DISTINCT
499 d.line_no,
500 i.concatenated_segments,
501 i.description,
502 i.inventory_item_id,
503 d.material_detail_id,
504 NVL(i.lot_control_code, 1),
505 NVL(i.location_control_code, 1),
506 'N', --- gtin_entered_ind
507 NULL, --- GTIN UOM code
508 NVL(i.restrict_locators_code, 2),
509 NVL(i.grade_control_flag, 'N'),
510 NVL(i.lot_status_enabled, 'N'),
511 NVL(i.lot_divisible_flag, 'N')
512 FROM gme_material_details d,
513 mtl_system_items_kfv i
514 WHERE d.batch_id = p_batch_id
515 AND d.inventory_item_id = i.inventory_item_id
516 AND d.organization_id = i.organization_id
517 AND d.line_type = p_line_type
518 AND i.mtl_transactions_enabled_flag = 'Y'
519 AND d.line_no LIKE (p_line_no)
520 AND i.concatenated_segments = NVL(p_item_no,i.concatenated_segments)
521
522 --- For GTIN support
523 UNION
524
525 SELECT DISTINCT
526 d.line_no,
527 i.concatenated_segments,
528 i.description,
529 i.inventory_item_id,
530 d.material_detail_id,
531 NVL(i.lot_control_code, 1),
532 NVL(i.location_control_code, 1),
533 'Y', --- gtin_entered_ind
534 NVL(uom_code, ' '), --- GTIN UOM code
535 NVL(i.restrict_locators_code, 2),
536 NVL(i.grade_control_flag, 'N'),
537 NVL(i.lot_status_enabled, 'N'),
538 NVL(i.lot_divisible_flag, 'N')
539 FROM
540 gme_material_details d,
541 mtl_cross_references mcr,
542 mtl_system_items_kfv i
543 WHERE d.batch_id = p_batch_id
544 AND d.inventory_item_id = i.inventory_item_id
545 AND d.organization_id = i.organization_id
546 AND d.line_type = p_line_type
547 AND i.inventory_item_id = mcr.inventory_item_id
548 AND i.mtl_transactions_enabled_flag = 'Y'
549 AND d.line_no LIKE (p_line_no)
550 AND mcr.cross_reference_type = g_gtin_cross_ref_type
551 AND mcr.cross_reference LIKE l_cross_ref
552 AND (mcr.organization_id = i.organization_id OR
553 mcr.org_independent_flag = 'Y')
554 ORDER BY line_no;
555
556 ELSE
557
558 OPEN x_item_cursor FOR
559
560 SELECT DISTINCT
561 d.line_no,
562 i.concatenated_segments,
563 i.description,
564 i.inventory_item_id,
565 d.material_detail_id,
566 NVL(i.lot_control_code, 1),
567 NVL(i.location_control_code, 1),
568 'N', --- gtin_entered_ind
569 NULL, --- GTIN UOM code
570 NVL(i.restrict_locators_code, 2),
571 NVL(i.grade_control_flag, 'N'),
572 NVL(i.lot_status_enabled, 'N'),
573 NVL(i.lot_divisible_flag, 'N')
574 FROM gme_material_details d,
575 gme_batch_step_items si,
576 gme_batch_steps s,
577 mtl_system_items_kfv i
578 WHERE d.batch_id = p_batch_id
579 AND d.batch_id = si.batch_id
580 AND si.material_detail_id = d.material_detail_id
581 AND s.batchstep_no = p_step_no
582 AND si.batchstep_id = s.batchstep_id
583 AND d.inventory_item_id = i.inventory_item_id
584 AND d.organization_id = i.organization_id
585 AND d.line_type = p_line_type
586 AND i.mtl_transactions_enabled_flag = 'Y'
587 AND d.line_no LIKE (p_line_no)
588 AND i.concatenated_segments = NVL(p_item_no,i.concatenated_segments)
589
590 --- For GTIN support
591 UNION
592
593 SELECT DISTINCT
594 d.line_no,
595 i.concatenated_segments,
596 i.description,
597 i.inventory_item_id,
598 d.material_detail_id,
599 NVL(i.lot_control_code, 1),
600 NVL(i.location_control_code, 1),
601 'Y', --- gtin_entered_ind
602 NVL(uom_code, ' '), --- GTIN UOM code
603 NVL(i.restrict_locators_code, 2),
604 NVL(i.grade_control_flag, 'N'),
605 NVL(i.lot_status_enabled, 'N'),
606 NVL(i.lot_divisible_flag, 'N')
607 FROM
608 gme_material_details d,
609 mtl_cross_references mcr,
610 gme_batch_step_items si,
611 gme_batch_steps s,
612 mtl_system_items_kfv i
613 WHERE d.batch_id = p_batch_id
614 AND d.batch_id = si.batch_id
615 AND si.material_detail_id = d.material_detail_id
616 AND s.batchstep_no = p_step_no
617 AND si.batchstep_id = s.batchstep_id
618 AND d.inventory_item_id = i.inventory_item_id
619 AND d.organization_id = i.organization_id
620 AND d.line_type = p_line_type
621 AND i.inventory_item_id = mcr.inventory_item_id
622 AND i.mtl_transactions_enabled_flag = 'Y'
623 AND d.line_no LIKE (p_line_no)
624 AND mcr.cross_reference_type = g_gtin_cross_ref_type
625 AND mcr.cross_reference LIKE l_cross_ref
626 AND (mcr.organization_id = i.organization_id OR mcr.org_independent_flag = 'Y')
627 ORDER BY line_no;
628 END IF;
629
630 EXCEPTION
631 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
632 THEN
633 NULL;
634 END Line_Step_LoV;
635
636 /*+========================================================================+
637 | PROCEDURE NAME
638 | Step_Material_Line_LoV
639 |
640 | USAGE
641 |
642 | ARGUMENTS
643 | p_batch_id
644 | p_line_no
645 | p_item_no
646 | p_step_no
647 | p_line_type
648 |
649 | RETURNS
650 | REF cursor x_line_cursor
651 |
652 | HISTORY
653 | Created 26-Apr-05 Eddie Oumerretane
654 |
655 +========================================================================+*/
656 PROCEDURE Step_Material_Line_LoV
657 ( x_line_cursor OUT NOCOPY t_genref
658 , p_batch_id IN NUMBER
659 , p_line_no IN VARCHAR2
660 , p_step_no IN VARCHAR2
661 , p_line_type IN NUMBER
662 )
663 IS
664 BEGIN
665
666 IF p_step_no IS NOT NULL THEN
667
668 OPEN x_line_cursor FOR
669 SELECT d.line_no,
670 i.concatenated_segments,
671 i.inventory_item_id,
672 d.material_detail_id
673 FROM gme_material_details d,
674 gme_batch_step_items si,
675 gme_batch_steps s,
676 mtl_system_items_kfv i
677 WHERE
678 d.batch_id = p_batch_id
679 AND d.batch_id = si.batch_id
680 AND si.material_detail_id = d.material_detail_id
681 AND s.batchstep_no = p_step_no
682 AND si.batchstep_id = s.batchstep_id
683 AND d.line_type = p_line_type
684 AND i.inventory_item_id = d.inventory_item_id
685 AND i.organization_id = d.organization_id
686 AND i.mtl_transactions_enabled_flag = 'Y'
687 AND d.line_no LIKE (p_line_no)
688 ORDER BY d.line_no;
689
690 ELSE
691
692 OPEN x_line_cursor FOR
693 SELECT d.line_no,
694 i.concatenated_segments,
695 i.inventory_item_id,
696 d.material_detail_id
697 FROM gme_material_details d,
698 mtl_system_items_kfv i
699 WHERE
700 d.batch_id = p_batch_id
701 AND d.line_type = p_line_type
702 AND i.inventory_item_id = d.inventory_item_id
703 AND i.organization_id = d.organization_id
704 AND i.mtl_transactions_enabled_flag = 'Y'
705 AND d.line_no LIKE (p_line_no)
706 ORDER BY d.line_no;
707
708 END IF;
709
710 END Step_Material_Line_LoV;
711
712 -- nsinghi bug#5236906. Added this procedure to show only lot controlled items.
713 /*+========================================================================+
714 | PROCEDURE NAME
715 | Step_Material_Lot_Line_LoV
716 |
717 | USAGE
718 |
719 | ARGUMENTS
720 | p_batch_id
721 | p_line_no
722 | p_item_no
723 | p_step_no
724 | p_line_type
725 |
726 | RETURNS
727 | REF cursor x_line_cursor
728 |
729 | HISTORY
730 | Created 26-Apr-05 Namit Singhi
731 |
732 +========================================================================+*/
733 PROCEDURE Step_Material_Lot_Line_LoV
734 ( x_line_cursor OUT NOCOPY t_genref
735 , p_batch_id IN NUMBER
736 , p_line_no IN VARCHAR2
737 , p_step_no IN VARCHAR2
738 , p_line_type IN NUMBER
739 )
740 IS
741 BEGIN
742
743 IF p_step_no IS NOT NULL THEN
744
745 OPEN x_line_cursor FOR
746 SELECT d.line_no,
747 i.concatenated_segments,
748 i.inventory_item_id,
749 d.material_detail_id
750 FROM gme_material_details d,
751 gme_batch_step_items si,
752 gme_batch_steps s,
753 mtl_system_items_kfv i
754 WHERE
755 d.batch_id = p_batch_id
756 AND d.batch_id = si.batch_id
757 AND si.material_detail_id = d.material_detail_id
758 AND s.batchstep_no = p_step_no
759 AND si.batchstep_id = s.batchstep_id
760 AND d.line_type = p_line_type
761 AND i.inventory_item_id = d.inventory_item_id
762 AND i.organization_id = d.organization_id
763 AND i.lot_control_code = 2
764 AND i.mtl_transactions_enabled_flag = 'Y'
765 AND d.line_no LIKE (p_line_no)
766 ORDER BY d.line_no;
767
768 ELSE
769
770 OPEN x_line_cursor FOR
771 SELECT d.line_no,
772 i.concatenated_segments,
773 i.inventory_item_id,
774 d.material_detail_id
775 FROM gme_material_details d,
776 mtl_system_items_kfv i
777 WHERE
778 d.batch_id = p_batch_id
779 AND d.line_type = p_line_type
780 AND i.inventory_item_id = d.inventory_item_id
781 AND i.organization_id = d.organization_id
782 AND i.lot_control_code = 2
783 AND i.mtl_transactions_enabled_flag = 'Y'
784 AND d.line_no LIKE (p_line_no)
785 ORDER BY d.line_no;
786
787 END IF;
788
789 END Step_Material_Lot_Line_LoV;
790
791 /*+========================================================================+
792 | PROCEDURE NAME
793 | Step_Material_Line_For_IB_LoV
794 |
795 | USAGE
796 |
797 | ARGUMENTS
798 | p_batch_id
799 | p_line_no
800 | p_item_no
801 | p_step_no
802 | p_line_type
803 |
804 | RETURNS
805 | REF cursor x_line_cursor
806 |
807 | HISTORY
808 | Created 06-Oct-05 Eddie Oumerretane
809 |
810 +========================================================================+*/
811 PROCEDURE Step_Material_Line_For_IB_LoV
812 ( x_line_cursor OUT NOCOPY t_genref
813 , p_batch_id IN NUMBER
814 , p_line_no IN VARCHAR2
815 , p_step_no IN VARCHAR2
816 , p_line_type IN NUMBER
817 )
818 IS
819 BEGIN
820
821 IF p_step_no IS NOT NULL THEN
822
823 OPEN x_line_cursor FOR
824 SELECT d.line_no,
825 i.concatenated_segments,
826 i.inventory_item_id,
827 d.material_detail_id
828 FROM gme_material_details d,
829 gme_batch_step_items si,
830 gme_batch_steps s,
831 mtl_system_items_kfv i
832 WHERE
833 d.batch_id = p_batch_id
834 AND d.batch_id = si.batch_id
835 AND si.material_detail_id = d.material_detail_id
836 AND s.batchstep_no = p_step_no
837 AND si.batchstep_id = s.batchstep_id
838 AND d.line_type = p_line_type
839 AND i.inventory_item_id = d.inventory_item_id
840 AND i.organization_id = d.organization_id
841 AND i.mtl_transactions_enabled_flag = 'Y'
842 AND d.release_type IN (1,2) --- Manual/Incremental
843 AND d.line_no LIKE (p_line_no)
844 ORDER BY d.line_no;
845
846 ELSE
847
848 OPEN x_line_cursor FOR
849 SELECT d.line_no,
850 i.concatenated_segments,
851 i.inventory_item_id,
852 d.material_detail_id
853 FROM gme_material_details d,
854 mtl_system_items_kfv i
855 WHERE
856 d.batch_id = p_batch_id
857 AND d.line_type = p_line_type
858 AND i.inventory_item_id = d.inventory_item_id
859 AND i.organization_id = d.organization_id
860 AND i.mtl_transactions_enabled_flag = 'Y'
861 AND d.release_type IN (1,2) --- Manual/Incremental
862 AND d.line_no LIKE (p_line_no)
863 ORDER BY d.line_no;
864
865 END IF;
866
867 END Step_Material_Line_For_IB_LoV;
868
869 /*+========================================================================+
870 | PROCEDURE NAME
871 | Step_Ingredient_LoV
872 |
873 | USAGE
874 |
875 | ARGUMENTS
876 | p_batch_id
877 | p_step_no
878 |
879 | RETURNS
880 | REF cursor x_step_cursor
881 |
882 | HISTORY
883 | Created 26-Apr-05 Eddie Oumerretane
884 |
885 +========================================================================+*/
886 PROCEDURE Step_Ingredient_Lov
887 ( x_step_cursor OUT NOCOPY t_genref
888 , p_batch_id IN NUMBER
889 , p_step_no IN VARCHAR2
890 )
891 IS
892 BEGIN
893
894 OPEN x_step_cursor FOR
895 SELECT DISTINCT
896 s.batchstep_no,
897 o.oprn_desc,
898 s.batchstep_id
899 FROM gme_batch_steps s,
900 gme_batch_step_items i,
901 gmd_operations o
902 WHERE
903 i.batch_id = p_batch_id
904 AND s.batch_id = i.batch_id
905 AND i.batchstep_id = s.batchstep_id
906 AND s.step_status <> 4
907 AND s.batchstep_no LIKE (p_step_no)
908 AND s.oprn_id = o.oprn_id
909 ORDER BY 1;
910
911 EXCEPTION
912 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
913 THEN
914 NULL;
915 END Step_Ingredient_Lov;
916
917 /*+========================================================================+
918 | PROCEDURE NAME
919 | Step_Prod_ByProd_LoV
920 |
921 | USAGE
922 |
923 | ARGUMENTS
924 | p_batch_id
925 | p_step_no
926 |
927 | RETURNS
928 | REF cursor x_step_cursor
929 |
930 | HISTORY
931 | Created 26-Apr-05 Eddie Oumerretane
932 |
933 +========================================================================+*/
934 PROCEDURE Step_Prod_ByProd_LoV
935 ( x_step_cursor OUT NOCOPY t_genref
936 , p_batch_id IN NUMBER
937 , p_step_no IN VARCHAR2
938 )
939 IS
940 BEGIN
941
942 OPEN x_step_cursor FOR
943 SELECT DISTINCT
944 s.batchstep_no,
945 o.oprn_desc,
946 s.batchstep_id
947 FROM gme_batch_steps s,
948 gme_batch_step_items i,
949 gmd_operations o,
950 gme_material_details d
951 WHERE
952 i.batch_id = p_batch_id
953 AND s.batch_id = i.batch_id
954 AND i.batchstep_id = s.batchstep_id
955 AND i.material_detail_id = d.material_detail_id
956 AND d.line_type IN (1,2)
957 AND s.step_status <> 4
958 AND s.batchstep_no LIKE (p_step_no)
959 AND s.oprn_id = o.oprn_id
960 ORDER BY 1;
961
962 EXCEPTION
963 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
964 THEN
965 NULL;
966 END Step_Prod_ByProd_LoV;
967
968 /*+========================================================================+
969 | PROCEDURE NAME
970 | Step_Pend_Lot_LoV
971 |
972 | USAGE
973 |
974 | ARGUMENTS
975 | p_batch_id
976 | p_step_no
977 |
978 | RETURNS
979 | REF cursor x_step_cursor
980 |
981 | HISTORY
982 | Created 03-Oct-05 Eddie Oumerretane
983 |
984 +========================================================================+*/
985 PROCEDURE Step_Pend_Lot_LoV
986 ( x_step_cursor OUT NOCOPY t_genref
987 , p_batch_id IN NUMBER
988 , p_step_no IN VARCHAR2
989 )
990 IS
991 BEGIN
992
993 OPEN x_step_cursor FOR
994 SELECT DISTINCT
995 s.batchstep_no,
996 o.oprn_desc,
997 s.batchstep_id
998 FROM gme_batch_steps s,
999 gme_batch_step_items i,
1000 gmd_operations o,
1001 gme_material_details d
1002 WHERE
1003 i.batch_id = p_batch_id
1004 AND s.batch_id = i.batch_id
1005 AND i.batchstep_id = s.batchstep_id
1006 AND i.material_detail_id = d.material_detail_id
1007 AND d.line_type IN (1,2)
1008 AND s.step_status IN (1,2)
1009 AND s.batchstep_no LIKE (p_step_no)
1010 AND s.oprn_id = o.oprn_id
1011 ORDER BY 1;
1012
1013 EXCEPTION
1014 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
1015 THEN
1016 NULL;
1017 END Step_Pend_Lot_LoV;
1018
1019 /*+========================================================================+
1020 | PROCEDURE NAME
1021 | Step_All_Items_LoV
1022 |
1023 | USAGE
1024 |
1025 | ARGUMENTS
1026 | p_batch_id
1027 | p_step_no
1028 |
1029 | RETURNS
1030 | REF cursor x_step__cursor
1031 |
1032 | HISTORY
1033 | Created 26-Apr-05 Eddie Oumerretane
1034 |
1035 +========================================================================+*/
1036 PROCEDURE Step_All_Items_LoV
1037 ( x_step_cursor OUT NOCOPY t_genref
1038 , p_batch_id IN NUMBER
1039 , p_step_no IN VARCHAR2
1040 )
1041 IS
1042 BEGIN
1043
1044 OPEN x_step_cursor FOR
1045 SELECT DISTINCT
1046 s.batchstep_no,
1047 o.oprn_desc,
1048 s.batchstep_id
1049 FROM gme_batch_steps s,
1050 gme_batch_step_items i,
1051 gmd_operations o,
1052 gme_material_details d
1053 WHERE
1054 i.batch_id = p_batch_id
1055 AND s.batch_id = i.batch_id
1056 AND i.batchstep_id = s.batchstep_id
1057 AND i.material_detail_id = d.material_detail_id
1058 AND s.step_status IN (1,2,3)
1059 AND s.batchstep_no LIKE (p_step_no)
1060 AND s.oprn_id = o.oprn_id
1061 ORDER BY 1;
1062
1063 EXCEPTION
1064 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
1065 THEN
1066 NULL;
1067 END Step_All_Items_LoV;
1068
1069 /*+========================================================================+
1070 | PROCEDURE NAME
1071 | Reason_LoV
1072 |
1073 | USAGE
1074 |
1075 | ARGUMENTS
1076 | p_reason_name
1077 |
1078 | RETURNS
1079 | REF cursor x_reason_cursor
1080 |
1081 | HISTORY
1082 | Created 26-Apr-05 Eddie Oumerretane
1083 |
1084 +========================================================================+*/
1085 PROCEDURE Reason_LoV
1086 ( x_reason_cursor OUT NOCOPY t_genref
1087 , p_reason_name IN VARCHAR2
1088 )
1089 IS
1090 BEGIN
1091
1092 OPEN x_reason_cursor FOR
1093 SELECT reason_name, description, reason_id
1094 FROM mtl_transaction_reasons
1095 WHERE reason_name like (p_reason_name)
1096 AND NVL(disable_date, SYSDATE+1) > SYSDATE
1097 ORDER BY reason_name;
1098
1099 END Reason_LoV;
1100
1101 /*+========================================================================+
1102 | PROCEDURE NAME
1103 | Activity_LoV
1104 |
1105 | USAGE
1106 |
1107 | ARGUMENTS
1108 | p_organization_id
1109 | p_batch_id
1110 | p_step_no
1111 | p_activity
1112 |
1113 | RETURNS
1114 | REF cursor x_activity_cursor
1115 |
1116 | HISTORY
1117 | Created 26-Apr-05 Eddie Oumerretane
1118 |
1119 +========================================================================+*/
1120 PROCEDURE Activity_LoV
1121 ( x_activity_cursor OUT NOCOPY t_genref
1122 , p_organization_id IN NUMBER
1123 , p_batch_id IN NUMBER
1124 , p_step_no IN VARCHAR2
1125 , p_activity IN VARCHAR2
1126 )
1127 IS
1128 BEGIN
1129 OPEN x_activity_cursor FOR
1130 SELECT a.activity,
1131 a.batchstep_activity_id
1132 FROM gme_batch_header h,
1133 gme_batch_steps s,
1134 gme_batch_step_activities a
1135 WHERE h.organization_id = p_organization_id
1136 AND h.batch_id = p_batch_id
1137 AND h.batch_type = 0
1138 AND h.batch_id = s.batch_id
1139 AND s.batchstep_no = p_step_no
1140 AND s.batchstep_id = a.batchstep_id
1141 AND h.batch_id = a.batch_id
1142 --- Bug 5236930 AND a.activity LIKE LTRIM(RTRIM(p_activity||'%'))
1143 AND a.activity LIKE p_activity
1144 ORDER BY a.activity;
1145
1146 END Activity_LoV;
1147
1148 /*+========================================================================+
1149 | PROCEDURE NAME
1150 | Resource_LoV
1151 |
1152 | USAGE
1153 |
1154 | ARGUMENTS
1155 | p_org_id
1156 | p_batch_id
1157 | p_step_no
1158 | p_activity_id
1159 | p_resource
1160 |
1161 | RETURNS
1162 | REF cursor x_resource_cursor
1163 |
1164 | HISTORY
1165 | Created 26-Apr-05 Eddie Oumerretane
1166 |
1167 +========================================================================+*/
1168 PROCEDURE Resource_LoV
1169 ( x_resource_cursor OUT NOCOPY t_genref
1170 , p_org_id IN NUMBER
1171 , p_batch_id IN NUMBER
1172 , p_step_no IN NUMBER
1173 , p_activity_id IN NUMBER
1174 , p_resource IN VARCHAR2
1175 )
1176 IS
1177 BEGIN
1178
1179 OPEN x_resource_cursor FOR
1180 SELECT r.resources,
1181 r.batchstep_resource_id
1182 FROM gme_batch_header h,
1183 gme_batch_steps s,
1184 gme_batch_step_activities a,
1185 gme_batch_step_resources r
1186 WHERE h.organization_id = p_org_id
1187 AND h.batch_id = p_batch_id
1188 AND h.batch_type = 0
1189 AND h.batch_id = s.batch_id
1190 AND s.batchstep_no = p_step_no
1191 AND s.batchstep_id = a.batchstep_id
1192 AND h.batch_id = a.batch_id
1193 AND r.batch_id = h.batch_id
1194 AND r.batchstep_id = s.batchstep_id
1195 AND r.batchstep_activity_id = a.batchstep_activity_id
1196 AND r.batchstep_activity_id = p_activity_id
1197 --- Bug 5236930 AND r.resources LIKE LTRIM(RTRIM(p_resource||'%'))
1198 AND r.resources LIKE p_resource
1199 ORDER BY r.resources;
1200
1201 END Resource_LoV;
1202
1203 /*+========================================================================+
1204 | PROCEDURE NAME
1205 | Ingredient_LoV
1206 |
1207 | USAGE
1208 |
1209 | ARGUMENTS
1210 | p_batch_id
1211 | p_line_no
1212 | p_item_no
1213 |
1214 | RETURNS
1215 | REF cursor x_item_cursor
1216 |
1217 | HISTORY
1218 | Created 26-Apr-05 Eddie Oumerretane
1219 |
1220 +========================================================================+*/
1221 PROCEDURE Ingredient_LoV
1222 ( x_item_cursor OUT NOCOPY t_genref
1223 , p_batch_id IN NUMBER
1224 , p_line_no IN VARCHAR2
1225 , p_item_no IN VARCHAR2
1226 )
1227 IS
1228
1229 l_cross_ref varchar2(204);
1230
1231 BEGIN
1232
1233 l_cross_ref := lpad(Rtrim(p_item_no, '%'), g_gtin_code_length,'00000000000000');
1234
1235 OPEN x_item_cursor FOR
1236
1237 SELECT DISTINCT
1238 i.concatenated_segments,
1239 i.description,
1240 d.line_no,
1241 i.inventory_item_id,
1242 d.material_detail_id,
1243 NVL(i.lot_control_code, 1),
1244 NVL(i.location_control_code, 1),
1245 'N', --- gtin_entered_ind
1246 NULL, --- GTIN UOM code
1247 NVL(i.restrict_locators_code, 2),
1248 NVL(i.grade_control_flag, 'N'),
1249 NVL(i.lot_status_enabled, 'N'),
1250 NVL(i.lot_divisible_flag, 'N')
1251 FROM gme_material_details d,
1252 mtl_system_items_kfv i
1253 WHERE d.batch_id = p_batch_id
1254 AND d.inventory_item_id = i.inventory_item_id
1255 AND d.organization_id = i.organization_id
1256 AND d.line_no = NVL(p_line_no, d.line_no)
1257 AND d.line_type = -1
1258 AND i.reservable_type = 1
1259 AND i.concatenated_segments LIKE (p_item_no)
1260
1261 --- For GTIN support
1262 UNION
1263
1264 SELECT DISTINCT
1265 i.concatenated_segments,
1266 i.description,
1267 d.line_no,
1268 i.inventory_item_id,
1269 d.material_detail_id,
1270 NVL(i.lot_control_code, 1),
1271 NVL(i.location_control_code, 1),
1272 'Y', --- gtin_entered_ind
1273 NVL(uom_code, ' '), --- GTIN UOM code
1274 NVL(i.restrict_locators_code, 2),
1275 NVL(i.grade_control_flag, 'N'),
1276 NVL(i.lot_status_enabled, 'N'),
1277 NVL(i.lot_divisible_flag, 'N')
1278 FROM
1279 gme_material_details d,
1280 mtl_cross_references mcr,
1281 mtl_system_items_kfv i
1282 WHERE d.batch_id = p_batch_id
1283 AND d.inventory_item_id = i.inventory_item_id
1284 AND d.organization_id = i.organization_id
1285 AND d.line_no = NVL(p_line_no, d.line_no)
1286 AND d.line_type = -1
1287 AND i.inventory_item_id = mcr.inventory_item_id
1288 AND i.reservable_type = 1
1289 AND mcr.cross_reference_type = g_gtin_cross_ref_type
1290 AND mcr.cross_reference LIKE l_cross_ref
1291 AND (mcr.organization_id = i.organization_id
1292 OR
1293 mcr.org_independent_flag = 'Y');
1294
1295 EXCEPTION
1296 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
1297 THEN
1298 NULL;
1299 END Ingredient_LoV;
1300
1301 /*+========================================================================+
1302 | PROCEDURE NAME
1303 | Ingredient_Rsrv_LoV
1304 |
1305 | USAGE
1306 |
1307 | ARGUMENTS
1308 | p_batch_id
1309 | p_line_no
1310 | p_item_no
1311 |
1312 | RETURNS
1313 | REF cursor x_item_cursor
1314 |
1315 | HISTORY
1316 | Created 23-Sep-05 Eddie Oumerretane
1317 |
1318 +========================================================================+*/
1319 PROCEDURE Ingredient_Rsrv_LoV
1320 ( x_item_cursor OUT NOCOPY t_genref
1321 , p_batch_id IN NUMBER
1322 , p_line_no IN VARCHAR2
1323 , p_item_no IN VARCHAR2
1324 )
1325 IS
1326
1327 l_cross_ref varchar2(204);
1328
1329 BEGIN
1330
1331 l_cross_ref := lpad(Rtrim(p_item_no, '%'), g_gtin_code_length,'00000000000000');
1332
1333 OPEN x_item_cursor FOR
1334
1335 SELECT DISTINCT
1336 i.concatenated_segments,
1337 i.description,
1338 d.line_no,
1339 i.inventory_item_id,
1340 d.material_detail_id,
1341 NVL(i.lot_control_code, 1),
1342 NVL(i.location_control_code, 1),
1343 'N', --- gtin_entered_ind
1344 NULL, --- GTIN UOM code
1345 NVL(i.restrict_locators_code, 2),
1346 NVL(i.grade_control_flag, 'N'),
1347 NVL(i.lot_status_enabled, 'N'),
1348 NVL(i.lot_divisible_flag, 'N')
1349 FROM gme_material_details d,
1350 mtl_system_items_kfv i
1351 WHERE d.batch_id = p_batch_id
1352 AND d.inventory_item_id = i.inventory_item_id
1353 AND d.organization_id = i.organization_id
1354 AND d.line_no = NVL(p_line_no, d.line_no)
1355 AND d.line_type = -1
1356 AND d.phantom_type = 0
1357 AND i.reservable_type = 1
1358 AND i.concatenated_segments LIKE (p_item_no)
1359
1360 --- For GTIN support
1361 UNION
1362
1363 SELECT DISTINCT
1364 i.concatenated_segments,
1365 i.description,
1366 d.line_no,
1367 i.inventory_item_id,
1368 d.material_detail_id,
1369 NVL(i.lot_control_code, 1),
1370 NVL(i.location_control_code, 1),
1371 'Y', --- gtin_entered_ind
1372 NVL(uom_code, ' '), --- GTIN UOM code
1373 NVL(i.restrict_locators_code, 2),
1374 NVL(i.grade_control_flag, 'N'),
1375 NVL(i.lot_status_enabled, 'N'),
1376 NVL(i.lot_divisible_flag, 'N')
1377 FROM
1378 gme_material_details d,
1379 mtl_cross_references mcr,
1380 mtl_system_items_kfv i
1381 WHERE d.batch_id = p_batch_id
1382 AND d.inventory_item_id = i.inventory_item_id
1383 AND d.organization_id = i.organization_id
1384 AND d.line_no = NVL(p_line_no, d.line_no)
1385 AND d.line_type = -1
1386 AND d.phantom_type = 0
1387 AND i.inventory_item_id = mcr.inventory_item_id
1388 AND i.reservable_type = 1
1389 AND mcr.cross_reference_type = g_gtin_cross_ref_type
1390 AND mcr.cross_reference LIKE l_cross_ref
1391 AND (mcr.organization_id = i.organization_id
1392 OR
1393 mcr.org_independent_flag = 'Y');
1394
1395 EXCEPTION
1396 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
1397 THEN
1398 NULL;
1399 END Ingredient_Rsrv_LoV;
1400
1401 /*+========================================================================+
1402 | PROCEDURE NAME
1403 | Ingredient_Step_LoV
1404 |
1405 | USAGE
1406 |
1407 | ARGUMENTS
1408 | p_batch_id
1409 | p_line_no
1410 | p_item_no
1411 | p_step_no
1412 |
1413 | RETURNS
1414 | REF cursor x_item_cursor
1415 |
1416 | HISTORY
1417 | Created 26-Apr-05 Eddie Oumerretane
1418 |
1419 +========================================================================+*/
1420 PROCEDURE Ingredient_Step_LoV
1421 ( x_item_cursor OUT NOCOPY t_genref
1422 , p_batch_id IN NUMBER
1423 , p_line_no IN VARCHAR2
1424 , p_item_no IN VARCHAR2
1425 , p_step_no IN VARCHAR2
1426 )
1427 IS
1428
1429 l_cross_ref varchar2(204);
1430
1431 BEGIN
1432
1433 IF (g_debug IS NOT NULL) THEN
1434 gme_debug.log_initialize ('MobileIngStepLoV');
1435 END IF;
1436
1437 l_cross_ref := lpad(Rtrim(p_item_no, '%'), g_gtin_code_length,'00000000000000');
1438
1439 gme_debug.put_line('Cross ref type = '||g_gtin_cross_ref_type);
1440 gme_debug.put_line('Cross ref = '||l_cross_ref);
1441
1442 IF p_step_no IS NULL THEN
1443
1444 OPEN x_item_cursor FOR
1445
1446 SELECT DISTINCT
1447 i.concatenated_segments,
1448 i.description,
1449 d.line_no,
1450 i.inventory_item_id,
1451 d.material_detail_id,
1452 NVL(i.lot_control_code, 1),
1453 NVL(i.location_control_code, 1),
1454 'N', --- gtin_entered_ind
1455 NULL, --- GTIN UOM code
1456 NVL(i.restrict_locators_code, 2),
1457 NVL(i.grade_control_flag, 'N'),
1458 NVL(i.lot_status_enabled, 'N'),
1459 NVL(i.lot_divisible_flag, 'N')
1460 FROM gme_material_details d,
1461 mtl_system_items_kfv i
1462 WHERE d.batch_id = p_batch_id
1463 AND d.inventory_item_id = i.inventory_item_id
1464 AND d.organization_id = i.organization_id
1465 AND d.line_no = NVL(p_line_no, d.line_no)
1466 AND d.line_type = -1
1467 AND i.mtl_transactions_enabled_flag = 'Y'
1468 AND i.concatenated_segments LIKE (p_item_no)
1469
1470 --- For GTIN support
1471 UNION
1472
1473 SELECT DISTINCT
1474 i.concatenated_segments,
1475 i.description,
1476 d.line_no,
1477 i.inventory_item_id,
1478 d.material_detail_id,
1479 NVL(i.lot_control_code, 1),
1480 NVL(i.location_control_code, 1),
1481 'Y', --- gtin_entered_ind
1482 NVL(uom_code, ' '), --- GTIN UOM code
1483 NVL(i.restrict_locators_code, 2),
1484 NVL(i.grade_control_flag, 'N'),
1485 NVL(i.lot_status_enabled, 'N'),
1486 NVL(i.lot_divisible_flag, 'N')
1487 FROM
1488 gme_material_details d,
1489 mtl_cross_references mcr,
1490 mtl_system_items_kfv i
1491 WHERE d.batch_id = p_batch_id
1492 AND d.inventory_item_id = i.inventory_item_id
1493 AND d.organization_id = i.organization_id
1494 AND d.line_no = NVL(p_line_no, d.line_no)
1495 AND d.line_type = -1
1496 AND i.inventory_item_id = mcr.inventory_item_id
1497 AND i.mtl_transactions_enabled_flag = 'Y'
1498 AND mcr.cross_reference_type = g_gtin_cross_ref_type
1499 AND mcr.cross_reference LIKE l_cross_ref
1500 AND (mcr.organization_id = i.organization_id
1501 OR
1502 mcr.org_independent_flag = 'Y');
1503
1504 ELSE
1505
1506 OPEN x_item_cursor FOR
1507
1508 SELECT DISTINCT
1509 i.concatenated_segments,
1510 i.description,
1511 d.line_no,
1512 i.inventory_item_id,
1513 d.material_detail_id,
1514 NVL(i.lot_control_code, 1),
1515 NVL(i.location_control_code, 1),
1516 'N', --- gtin_entered_ind
1517 NULL, --- GTIN UOM code
1518 NVL(i.restrict_locators_code, 2),
1519 NVL(i.grade_control_flag, 'N'),
1520 NVL(i.lot_status_enabled, 'N'),
1521 NVL(i.lot_divisible_flag, 'N')
1522 FROM gme_material_details d,
1523 gme_batch_step_items si,
1524 gme_batch_steps s,
1525 mtl_system_items_kfv i
1526 WHERE d.batch_id = p_batch_id
1527 AND d.batch_id = si.batch_id
1528 AND si.material_detail_id = d.material_detail_id
1529 AND s.batchstep_no = p_step_no
1530 AND si.batchstep_id = s.batchstep_id
1531 AND d.inventory_item_id = i.inventory_item_id
1532 AND d.organization_id = i.organization_id
1533 AND d.line_no = NVL(p_line_no, d.line_no)
1534 AND d.line_type = -1
1535 AND i.mtl_transactions_enabled_flag = 'Y'
1536 AND i.concatenated_segments LIKE (p_item_no)
1537
1538 --- For GTIN support
1539 UNION
1540
1541 SELECT DISTINCT
1542 i.concatenated_segments,
1543 i.description,
1544 d.line_no,
1545 i.inventory_item_id,
1546 d.material_detail_id,
1547 NVL(i.lot_control_code, 1),
1548 NVL(i.location_control_code, 1),
1549 'Y', --- gtin_entered_ind
1550 NVL(uom_code, ' '), --- GTIN UOM code
1551 NVL(i.restrict_locators_code, 2),
1552 NVL(i.grade_control_flag, 'N'),
1553 NVL(i.lot_status_enabled, 'N'),
1554 NVL(i.lot_divisible_flag, 'N')
1555 FROM
1556 gme_material_details d,
1557 mtl_cross_references mcr,
1558 gme_batch_step_items si,
1559 gme_batch_steps s,
1560 mtl_system_items_kfv i
1561 WHERE d.batch_id = p_batch_id
1562 AND d.batch_id = si.batch_id
1563 AND si.material_detail_id = d.material_detail_id
1564 AND s.batchstep_no = p_step_no
1565 AND si.batchstep_id = s.batchstep_id
1566 AND d.inventory_item_id = i.inventory_item_id
1567 AND d.organization_id = i.organization_id
1568 AND d.line_no = NVL(p_line_no, d.line_no)
1569 AND d.line_type = -1
1570 AND i.inventory_item_id = mcr.inventory_item_id
1571 AND i.mtl_transactions_enabled_flag = 'Y'
1572 AND mcr.cross_reference_type = g_gtin_cross_ref_type
1573 AND mcr.cross_reference LIKE l_cross_ref
1574 AND (mcr.organization_id = i.organization_id
1575 OR
1576 mcr.org_independent_flag = 'Y');
1577 END IF;
1578
1579 EXCEPTION
1580 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
1581 THEN
1582 NULL;
1583 END Ingredient_Step_LoV;
1584
1585 /*+========================================================================+
1586 | PROCEDURE NAME
1587 | Material_Step_LoV
1588 |
1589 | USAGE
1590 |
1591 | ARGUMENTS
1592 | p_batch_id
1593 | p_line_no
1594 | p_item_no
1595 | p_step_no
1596 | p_line_type
1597 |
1598 | RETURNS
1599 | REF cursor x_item_cursor
1600 |
1601 | HISTORY
1602 | Created 26-Apr-05 Eddie Oumerretane
1603 |
1604 +========================================================================+*/
1605 PROCEDURE Material_Step_LoV
1606 ( x_item_cursor OUT NOCOPY t_genref
1607 , p_batch_id IN NUMBER
1608 , p_line_no IN VARCHAR2
1609 , p_item_no IN VARCHAR2
1610 , p_step_no IN VARCHAR2
1611 , p_line_type IN NUMBER
1612 )
1613 IS
1614
1615 l_cross_ref varchar2(204);
1616
1617 BEGIN
1618
1619 IF (g_debug IS NOT NULL) THEN
1620 gme_debug.log_initialize ('MobileMtlStepLoV');
1621 END IF;
1622
1623 l_cross_ref := lpad(Rtrim(p_item_no, '%'), g_gtin_code_length,'00000000000000');
1624
1625
1626 gme_debug.put_line('Cross ref type = '||g_gtin_cross_ref_type);
1627 gme_debug.put_line('Cross ref = '||l_cross_ref);
1628
1629 IF p_step_no IS NULL THEN
1630
1631 OPEN x_item_cursor FOR
1632
1633 SELECT DISTINCT
1634 i.concatenated_segments,
1635 i.description,
1636 d.line_no,
1637 i.inventory_item_id,
1638 d.material_detail_id,
1639 NVL(i.lot_control_code, 1),
1640 NVL(i.location_control_code, 1),
1641 'N', --- gtin_entered_ind
1642 NULL, --- GTIN UOM code
1643 NVL(i.restrict_locators_code, 2),
1644 NVL(i.grade_control_flag, 'N'),
1645 NVL(i.lot_status_enabled, 'N'),
1646 NVL(i.lot_divisible_flag, 'N')
1647 FROM gme_material_details d,
1648 mtl_system_items_kfv i
1649 WHERE d.batch_id = p_batch_id
1650 AND d.inventory_item_id = i.inventory_item_id
1651 AND d.organization_id = i.organization_id
1652 AND d.line_no = NVL(p_line_no, d.line_no)
1653 AND d.line_type = p_line_type
1654 AND i.mtl_transactions_enabled_flag = 'Y'
1655 AND i.concatenated_segments LIKE (p_item_no)
1656
1657 --- For GTIN support
1658 UNION
1659
1660 SELECT DISTINCT
1661 i.concatenated_segments,
1662 i.description,
1663 d.line_no,
1664 i.inventory_item_id,
1665 d.material_detail_id,
1666 NVL(i.lot_control_code, 1),
1667 NVL(i.location_control_code, 1),
1668 'Y', --- gtin_entered_ind
1669 NVL(uom_code, ' '), --- GTIN UOM code
1670 NVL(i.restrict_locators_code, 2),
1671 NVL(i.grade_control_flag, 'N'),
1672 NVL(i.lot_status_enabled, 'N'),
1673 NVL(i.lot_divisible_flag, 'N')
1674 FROM
1675 gme_material_details d,
1676 mtl_cross_references mcr,
1677 mtl_system_items_kfv i
1678 WHERE d.batch_id = p_batch_id
1679 AND d.inventory_item_id = i.inventory_item_id
1680 AND d.organization_id = i.organization_id
1681 AND d.line_no = NVL(p_line_no, d.line_no)
1682 AND d.line_type = p_line_type
1683 AND i.inventory_item_id = mcr.inventory_item_id
1684 AND i.mtl_transactions_enabled_flag = 'Y'
1685 AND mcr.cross_reference_type = g_gtin_cross_ref_type
1686 AND mcr.cross_reference LIKE l_cross_ref
1687 AND (mcr.organization_id = i.organization_id
1688 OR
1689 mcr.org_independent_flag = 'Y')
1690 ORDER BY line_no;
1691
1692 ELSE
1693
1694 OPEN x_item_cursor FOR
1695
1696 SELECT DISTINCT
1697 i.concatenated_segments,
1698 i.description,
1699 d.line_no,
1700 i.inventory_item_id,
1701 d.material_detail_id,
1702 NVL(i.lot_control_code, 1),
1703 NVL(i.location_control_code, 1),
1704 'N', --- gtin_entered_ind
1705 NULL, --- GTIN UOM code
1706 NVL(i.restrict_locators_code, 2),
1707 NVL(i.grade_control_flag, 'N'),
1708 NVL(i.lot_status_enabled, 'N'),
1709 NVL(i.lot_divisible_flag, 'N')
1710 FROM gme_material_details d,
1711 gme_batch_step_items si,
1712 gme_batch_steps s,
1713 mtl_system_items_kfv i
1714 WHERE d.batch_id = p_batch_id
1715 AND d.batch_id = si.batch_id
1716 AND si.material_detail_id = d.material_detail_id
1717 AND s.batchstep_no = p_step_no
1718 AND si.batchstep_id = s.batchstep_id
1719 AND d.inventory_item_id = i.inventory_item_id
1720 AND d.organization_id = i.organization_id
1721 AND d.line_no = NVL(p_line_no, d.line_no)
1722 AND d.line_type = p_line_type
1723 AND i.mtl_transactions_enabled_flag = 'Y'
1724 AND i.concatenated_segments LIKE (p_item_no)
1725
1726 --- For GTIN support
1727 UNION
1728
1729 SELECT DISTINCT
1730 i.concatenated_segments,
1731 i.description,
1732 d.line_no,
1733 i.inventory_item_id,
1734 d.material_detail_id,
1735 NVL(i.lot_control_code, 1),
1736 NVL(i.location_control_code, 1),
1737 'Y', --- gtin_entered_ind
1738 NVL(uom_code, ' '), --- GTIN UOM code
1739 NVL(i.restrict_locators_code, 2),
1740 NVL(i.grade_control_flag, 'N'),
1741 NVL(i.lot_status_enabled, 'N'),
1742 NVL(i.lot_divisible_flag, 'N')
1743 FROM
1744 gme_material_details d,
1745 mtl_cross_references mcr,
1746 gme_batch_step_items si,
1747 gme_batch_steps s,
1748 mtl_system_items_kfv i
1749 WHERE d.batch_id = p_batch_id
1750 AND d.batch_id = si.batch_id
1751 AND si.material_detail_id = d.material_detail_id
1752 AND s.batchstep_no = p_step_no
1753 AND si.batchstep_id = s.batchstep_id
1754 AND d.inventory_item_id = i.inventory_item_id
1755 AND d.organization_id = i.organization_id
1756 AND d.line_no = NVL(p_line_no, d.line_no)
1757 AND d.line_type = p_line_type
1758 AND i.inventory_item_id = mcr.inventory_item_id
1759 AND i.mtl_transactions_enabled_flag = 'Y'
1760 AND mcr.cross_reference_type = g_gtin_cross_ref_type
1761 AND mcr.cross_reference LIKE l_cross_ref
1762 AND (mcr.organization_id = i.organization_id
1763 OR
1764 mcr.org_independent_flag = 'Y')
1765 ORDER BY line_no;
1766 END IF;
1767
1768 EXCEPTION
1769 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
1770 THEN
1771 NULL;
1772 END Material_Step_LoV;
1773
1774 -- nsinghi bug#5236906. Added this procedure.
1775 /*+========================================================================+
1776 | PROCEDURE NAME
1777 | Material_Lot_LoV
1778 |
1779 | USAGE
1780 |
1781 | ARGUMENTS
1782 | p_batch_id
1783 | p_line_no
1784 | p_item_no
1785 | p_step_no
1786 | p_line_type
1787 |
1788 | RETURNS
1789 | REF cursor x_item_cursor
1790 |
1791 | HISTORY
1792 | Created 26-Apr-05 Namit Singhi
1793 |
1794 +========================================================================+*/
1795 PROCEDURE Material_Lot_LoV
1796 ( x_item_cursor OUT NOCOPY t_genref
1797 , p_batch_id IN NUMBER
1798 , p_line_no IN VARCHAR2
1799 , p_item_no IN VARCHAR2
1800 , p_step_no IN VARCHAR2
1801 , p_line_type IN NUMBER
1802 )
1803 IS
1804
1805 l_cross_ref varchar2(204);
1806
1807 BEGIN
1808
1809 IF (g_debug IS NOT NULL) THEN
1810 gme_debug.log_initialize ('MobileLotProdStepLoV');
1811 END IF;
1812
1813 l_cross_ref := lpad(Rtrim(p_item_no, '%'), g_gtin_code_length,'00000000000000');
1814
1815
1816 gme_debug.put_line('Cross ref type = '||g_gtin_cross_ref_type);
1817 gme_debug.put_line('Cross ref = '||l_cross_ref);
1818
1819 IF p_step_no IS NULL THEN
1820
1821 OPEN x_item_cursor FOR
1822
1823 SELECT DISTINCT
1824 i.concatenated_segments,
1825 i.description,
1826 d.line_no,
1827 i.inventory_item_id,
1828 d.material_detail_id,
1829 NVL(i.lot_control_code, 2),
1830 NVL(i.location_control_code, 1),
1831 'N', --- gtin_entered_ind
1832 NULL, --- GTIN UOM code
1833 NVL(i.restrict_locators_code, 2),
1834 NVL(i.grade_control_flag, 'N'),
1835 NVL(i.lot_status_enabled, 'N'),
1836 NVL(i.lot_divisible_flag, 'N')
1837 FROM gme_material_details d,
1838 mtl_system_items_kfv i
1839 WHERE d.batch_id = p_batch_id
1840 AND d.inventory_item_id = i.inventory_item_id
1841 AND d.organization_id = i.organization_id
1842 AND d.line_no = NVL(p_line_no, d.line_no)
1843 AND d.line_type = p_line_type
1844 AND i.mtl_transactions_enabled_flag = 'Y'
1845 AND i.lot_control_code = 2
1846 AND i.concatenated_segments LIKE (p_item_no)
1847
1848 --- For GTIN support
1849 UNION
1850
1851 SELECT DISTINCT
1852 i.concatenated_segments,
1853 i.description,
1854 d.line_no,
1855 i.inventory_item_id,
1856 d.material_detail_id,
1857 NVL(i.lot_control_code, 2),
1858 NVL(i.location_control_code, 1),
1859 'Y', --- gtin_entered_ind
1860 NVL(uom_code, ' '), --- GTIN UOM code
1861 NVL(i.restrict_locators_code, 2),
1862 NVL(i.grade_control_flag, 'N'),
1863 NVL(i.lot_status_enabled, 'N'),
1864 NVL(i.lot_divisible_flag, 'N')
1865 FROM
1866 gme_material_details d,
1867 mtl_cross_references mcr,
1868 mtl_system_items_kfv i
1869 WHERE d.batch_id = p_batch_id
1870 AND d.inventory_item_id = i.inventory_item_id
1871 AND d.organization_id = i.organization_id
1872 AND d.line_no = NVL(p_line_no, d.line_no)
1873 AND d.line_type = p_line_type
1874 AND i.lot_control_code = 2
1875 AND i.inventory_item_id = mcr.inventory_item_id
1876 AND i.mtl_transactions_enabled_flag = 'Y'
1877 AND mcr.cross_reference_type = g_gtin_cross_ref_type
1878 AND mcr.cross_reference LIKE l_cross_ref
1879 AND (mcr.organization_id = i.organization_id
1880 OR
1881 mcr.org_independent_flag = 'Y');
1882
1883 ELSE
1884
1885 OPEN x_item_cursor FOR
1886
1887 SELECT DISTINCT
1888 i.concatenated_segments,
1889 i.description,
1890 d.line_no,
1891 i.inventory_item_id,
1892 d.material_detail_id,
1893 NVL(i.lot_control_code, 2),
1894 NVL(i.location_control_code, 1),
1895 'N', --- gtin_entered_ind
1896 NULL, --- GTIN UOM code
1897 NVL(i.restrict_locators_code, 2),
1898 NVL(i.grade_control_flag, 'N'),
1899 NVL(i.lot_status_enabled, 'N'),
1900 NVL(i.lot_divisible_flag, 'N')
1901 FROM gme_material_details d,
1902 gme_batch_step_items si,
1903 gme_batch_steps s,
1904 mtl_system_items_kfv i
1905 WHERE d.batch_id = p_batch_id
1906 AND d.batch_id = si.batch_id
1907 AND si.material_detail_id = d.material_detail_id
1908 AND s.batchstep_no = p_step_no
1909 AND si.batchstep_id = s.batchstep_id
1910 AND d.inventory_item_id = i.inventory_item_id
1911 AND d.organization_id = i.organization_id
1912 AND d.line_no = NVL(p_line_no, d.line_no)
1913 AND d.line_type = p_line_type
1914 AND i.lot_control_code = 2
1915 AND i.mtl_transactions_enabled_flag = 'Y'
1916 AND i.concatenated_segments LIKE (p_item_no)
1917
1918 --- For GTIN support
1919 UNION
1920
1921 SELECT DISTINCT
1922 i.concatenated_segments,
1923 i.description,
1924 d.line_no,
1925 i.inventory_item_id,
1926 d.material_detail_id,
1927 NVL(i.lot_control_code, 2),
1928 NVL(i.location_control_code, 1),
1929 'Y', --- gtin_entered_ind
1930 NVL(uom_code, ' '), --- GTIN UOM code
1931 NVL(i.restrict_locators_code, 2),
1932 NVL(i.grade_control_flag, 'N'),
1933 NVL(i.lot_status_enabled, 'N'),
1934 NVL(i.lot_divisible_flag, 'N')
1935 FROM
1936 gme_material_details d,
1937 mtl_cross_references mcr,
1938 gme_batch_step_items si,
1939 gme_batch_steps s,
1940 mtl_system_items_kfv i
1941 WHERE d.batch_id = p_batch_id
1942 AND d.batch_id = si.batch_id
1943 AND si.material_detail_id = d.material_detail_id
1944 AND s.batchstep_no = p_step_no
1945 AND si.batchstep_id = s.batchstep_id
1946 AND d.inventory_item_id = i.inventory_item_id
1947 AND d.organization_id = i.organization_id
1948 AND d.line_no = NVL(p_line_no, d.line_no)
1949 AND d.line_type = p_line_type
1950 AND i.lot_control_code = 2
1951 AND i.inventory_item_id = mcr.inventory_item_id
1952 AND i.mtl_transactions_enabled_flag = 'Y'
1953 AND mcr.cross_reference_type = g_gtin_cross_ref_type
1954 AND mcr.cross_reference LIKE l_cross_ref
1955 AND (mcr.organization_id = i.organization_id
1956 OR
1957 mcr.org_independent_flag = 'Y');
1958 END IF;
1959
1960 EXCEPTION
1961 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
1962 THEN
1963 NULL;
1964 END Material_Lot_LoV;
1965
1966 /*+========================================================================+
1967 | PROCEDURE NAME
1968 | Material_Step_For_IB_LoV
1969 |
1970 | USAGE
1971 |
1972 | ARGUMENTS
1973 | p_batch_id
1974 | p_line_no
1975 | p_item_no
1976 | p_step_no
1977 | p_line_type
1978 |
1979 | RETURNS
1980 | REF cursor x_item_cursor
1981 |
1982 | HISTORY
1983 | Created 06-Oct-05 Eddie Oumerretane
1984 |
1985 +========================================================================+*/
1986 PROCEDURE Material_Step_For_IB_LoV
1987 ( x_item_cursor OUT NOCOPY t_genref
1988 , p_batch_id IN NUMBER
1989 , p_line_no IN VARCHAR2
1990 , p_item_no IN VARCHAR2
1991 , p_step_no IN VARCHAR2
1992 , p_line_type IN NUMBER
1993 )
1994 IS
1995
1996 l_cross_ref varchar2(204);
1997
1998 BEGIN
1999
2000 IF (g_debug IS NOT NULL) THEN
2001 gme_debug.log_initialize ('MobileMtlStepLoV');
2002 END IF;
2003
2004 l_cross_ref := lpad(Rtrim(p_item_no, '%'), g_gtin_code_length,'00000000000000');
2005
2006
2007 gme_debug.put_line('Cross ref type = '||g_gtin_cross_ref_type);
2008 gme_debug.put_line('Cross ref = '||l_cross_ref);
2009
2010 IF p_step_no IS NULL THEN
2011
2012 OPEN x_item_cursor FOR
2013
2014 SELECT DISTINCT
2015 i.concatenated_segments,
2016 i.description,
2017 d.line_no,
2018 i.inventory_item_id,
2019 d.material_detail_id,
2020 NVL(i.lot_control_code, 1),
2021 NVL(i.location_control_code, 1),
2022 'N', --- gtin_entered_ind
2023 NULL, --- GTIN UOM code
2024 NVL(i.restrict_locators_code, 2),
2025 NVL(i.grade_control_flag, 'N'),
2026 NVL(i.lot_status_enabled, 'N'),
2027 NVL(i.lot_divisible_flag, 'N')
2028 FROM gme_material_details d,
2029 mtl_system_items_kfv i
2030 WHERE d.batch_id = p_batch_id
2031 AND d.inventory_item_id = i.inventory_item_id
2032 AND d.organization_id = i.organization_id
2033 AND d.line_no = NVL(p_line_no, d.line_no)
2034 AND d.line_type = p_line_type
2035 AND i.mtl_transactions_enabled_flag = 'Y'
2036 AND d.release_type IN (1,2) --- Manual/Incremental
2037 AND i.concatenated_segments LIKE (p_item_no)
2038 --- For GTIN support
2039 UNION
2040
2041 SELECT DISTINCT
2042 i.concatenated_segments,
2043 i.description,
2044 d.line_no,
2045 i.inventory_item_id,
2046 d.material_detail_id,
2047 NVL(i.lot_control_code, 1),
2048 NVL(i.location_control_code, 1),
2049 'Y', --- gtin_entered_ind
2050 NVL(uom_code, ' '), --- GTIN UOM code
2051 NVL(i.restrict_locators_code, 2),
2052 NVL(i.grade_control_flag, 'N'),
2053 NVL(i.lot_status_enabled, 'N'),
2054 NVL(i.lot_divisible_flag, 'N')
2055 FROM
2056 gme_material_details d,
2057 mtl_cross_references mcr,
2058 mtl_system_items_kfv i
2059 WHERE d.batch_id = p_batch_id
2060 AND d.inventory_item_id = i.inventory_item_id
2061 AND d.organization_id = i.organization_id
2062 AND d.line_no = NVL(p_line_no, d.line_no)
2063 AND d.line_type = p_line_type
2064 AND i.inventory_item_id = mcr.inventory_item_id
2065 AND i.mtl_transactions_enabled_flag = 'Y'
2066 AND d.release_type IN (1,2) --- Manual/Incremental
2067 AND mcr.cross_reference_type = g_gtin_cross_ref_type
2068 AND mcr.cross_reference LIKE l_cross_ref
2069 AND (mcr.organization_id = i.organization_id
2070 OR
2071 mcr.org_independent_flag = 'Y');
2072
2073 ELSE
2074
2075 OPEN x_item_cursor FOR
2076
2077 SELECT DISTINCT
2078 i.concatenated_segments,
2079 i.description,
2080 d.line_no,
2081 i.inventory_item_id,
2082 d.material_detail_id,
2083 NVL(i.lot_control_code, 1),
2084 NVL(i.location_control_code, 1),
2085 'N', --- gtin_entered_ind
2086 NULL, --- GTIN UOM code
2087 NVL(i.restrict_locators_code, 2),
2088 NVL(i.grade_control_flag, 'N'),
2089 NVL(i.lot_status_enabled, 'N'),
2090 NVL(i.lot_divisible_flag, 'N')
2091 FROM gme_material_details d,
2092 gme_batch_step_items si,
2093 gme_batch_steps s,
2094 mtl_system_items_kfv i
2095 WHERE d.batch_id = p_batch_id
2096 AND d.batch_id = si.batch_id
2097 AND si.material_detail_id = d.material_detail_id
2098 AND s.batchstep_no = p_step_no
2099 AND si.batchstep_id = s.batchstep_id
2100 AND d.inventory_item_id = i.inventory_item_id
2101 AND d.organization_id = i.organization_id
2102 AND d.line_no = NVL(p_line_no, d.line_no)
2103 AND d.line_type = p_line_type
2104 AND d.release_type IN (1,2) --- Manual/Incremental
2105 AND i.mtl_transactions_enabled_flag = 'Y'
2106 AND i.concatenated_segments LIKE (p_item_no)
2107
2108 --- For GTIN support
2109 UNION
2110
2111 SELECT DISTINCT
2112 i.concatenated_segments,
2113 i.description,
2114 d.line_no,
2115 i.inventory_item_id,
2116 d.material_detail_id,
2117 NVL(i.lot_control_code, 1),
2118 NVL(i.location_control_code, 1),
2119 'Y', --- gtin_entered_ind
2120 NVL(uom_code, ' '), --- GTIN UOM code
2121 NVL(i.restrict_locators_code, 2),
2122 NVL(i.grade_control_flag, 'N'),
2123 NVL(i.lot_status_enabled, 'N'),
2124 NVL(i.lot_divisible_flag, 'N')
2125 FROM
2126 gme_material_details d,
2127 mtl_cross_references mcr,
2128 gme_batch_step_items si,
2129 gme_batch_steps s,
2130 mtl_system_items_kfv i
2131 WHERE d.batch_id = p_batch_id
2132 AND d.batch_id = si.batch_id
2133 AND si.material_detail_id = d.material_detail_id
2134 AND s.batchstep_no = p_step_no
2135 AND si.batchstep_id = s.batchstep_id
2136 AND d.inventory_item_id = i.inventory_item_id
2137 AND d.organization_id = i.organization_id
2138 AND d.line_no = NVL(p_line_no, d.line_no)
2139 AND d.line_type = p_line_type
2140 AND i.inventory_item_id = mcr.inventory_item_id
2141 AND d.release_type IN (1,2) --- Manual/Incremental
2142 AND i.mtl_transactions_enabled_flag = 'Y'
2143 AND mcr.cross_reference_type = g_gtin_cross_ref_type
2144 AND mcr.cross_reference LIKE l_cross_ref
2145 AND (mcr.organization_id = i.organization_id
2146 OR
2147 mcr.org_independent_flag = 'Y');
2148 END IF;
2149
2150 EXCEPTION
2151 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
2152 THEN
2153 NULL;
2154 END Material_Step_For_IB_LoV;
2155
2156 /*+========================================================================+
2157 | PROCEDURE NAME
2158 | Step_For_Resource_LoV
2159 |
2160 | USAGE
2161 |
2162 | ARGUMENTS
2163 | p_batch_id
2164 | p_step_no
2165 |
2166 | RETURNS
2167 | REF cursor x_step_cursor
2168 |
2169 | HISTORY
2170 | Created 26-Apr-05 Eddie Oumerretane
2171 |
2172 +========================================================================+*/
2173 PROCEDURE Step_For_Resource_LoV
2174 ( x_step_cursor OUT NOCOPY t_genref
2175 , p_batch_id IN NUMBER
2176 , p_step_no IN VARCHAR2
2177 )
2178 IS
2179 BEGIN
2180 OPEN x_step_cursor FOR
2181 SELECT s.batchstep_no,
2182 o.oprn_desc,
2183 s.batchstep_id
2184 FROM gme_batch_header h,
2185 gme_batch_steps s,
2186 gmd_operations o
2187 WHERE h.batch_id = p_batch_id
2188 AND h.batch_type = 0
2189 AND h.batch_id = s.batch_id
2190 AND automatic_step_calculation = 0
2191 --- Bug 5236930 AND to_char(s.batchstep_no) LIKE LTRIM(RTRIM(p_step_no||'%'))
2192 AND to_char(s.batchstep_no) LIKE p_step_no
2193 AND s.oprn_id = o.oprn_id
2194 AND s.step_status in (2,3)
2195 UNION
2196 SELECT s.batchstep_no,
2197 o.oprn_desc,
2198 s.batchstep_id
2199 FROM gme_batch_header h,
2200 gme_batch_steps s,
2201 gmd_operations o
2202 WHERE h.batch_id = p_batch_id
2203 AND h.batch_type = 0
2204 AND h.batch_id = s.batch_id
2205 AND automatic_step_calculation = 1
2206 --- Bug 5236930 AND to_char(s.batchstep_no) LIKE LTRIM(RTRIM(p_step_no||'%'))
2207 AND to_char(s.batchstep_no) LIKE p_step_no
2208 AND s.oprn_id = o.oprn_id
2209 AND s.step_status = 3
2210 ORDER BY 1;
2211
2212 END Step_For_Resource_LoV;
2213
2214 /*+========================================================================+
2215 | PROCEDURE NAME
2216 | Step_Material_Line_LoV
2217 |
2218 | USAGE
2219 |
2220 | ARGUMENTS
2221 | p_organization_id
2222 | p_subinv_code
2223 |
2224 | RETURNS
2225 | REF cursor x_sub_lov
2226 |
2227 | HISTORY
2228 | Created 26-Apr-05 Eddie Oumerretane
2229 |
2230 +========================================================================+*/
2231 PROCEDURE Subinventory_Lov(x_sub_lov OUT NOCOPY t_genref,
2232 p_organization_id IN NUMBER,
2233 p_subinv_code IN VARCHAR,
2234 p_item_id IN NUMBER,
2235 p_restrict_subinv IN NUMBER) IS
2236 BEGIN
2237 --Bug#5867209 added the following IF condition
2238 IF p_restrict_subinv = 1 THEN
2239 OPEN x_sub_lov FOR
2240 SELECT s.secondary_inventory_name
2241 , s.description
2242 , NVL(s.locator_type, 1)
2243 FROM mtl_secondary_inventories s,
2244 mtl_item_sub_inventories i
2245 WHERE s.secondary_inventory_name = i.secondary_inventory
2246 AND s.organization_id = i.organization_id
2247 AND s.organization_id = p_organization_id
2248 AND i.inventory_item_id = p_item_id
2249 AND NVL(s.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
2250 AND s.secondary_inventory_name LIKE (p_subinv_code)
2251 AND s.quantity_tracked = 1
2252 ORDER BY s.secondary_inventory_name;
2253 ELSE
2254 OPEN x_sub_lov FOR
2255 SELECT secondary_inventory_name
2256 , description
2257 , NVL(locator_type, 1)
2258 FROM mtl_secondary_inventories
2259 WHERE organization_id = p_organization_id
2260 AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
2261 AND secondary_inventory_name LIKE (p_subinv_code)
2262 AND quantity_tracked = 1
2263 ORDER BY secondary_inventory_name;
2264 END IF;
2265
2266 END Subinventory_Lov;
2267
2268
2269 /* Bug#5663458
2270 * Created the following procedure. This procedure is to get subinventoried that have
2271 * on hand. If the GME parameters is not set we call master LoV directly
2272 */
2273 PROCEDURE Subinventory_OnHand_LoV(
2274 x_sub_lov OUT NOCOPY t_genref
2275 , p_organization_id IN NUMBER
2276 , p_subinventory_code IN VARCHAR2
2277 , p_inventory_item_id IN NUMBER
2278 , p_revision IN VARCHAR2
2279 , p_restrict_subinv IN NUMBER
2280 )
2281 IS
2282 CURSOR c_get_locind IS
2283 SELECT subinv_loc_ind
2284 FROM gme_parameters
2285 WHERE organization_id = p_organization_id;
2286
2287 l_ind NUMBER(1) := 0;
2288 BEGIN
2289
2290 OPEN c_get_locind;
2291 FETCH c_get_locind INTO l_ind;
2292 CLOSE c_get_locind;
2293
2294 IF l_ind = 1 THEN
2295 OPEN x_sub_lov FOR
2296 SELECT DISTINCT sub.secondary_inventory_name, sub.description,
2297 NVL(sub.locator_type, 1)
2298 FROM mtl_secondary_inventories sub, mtl_onhand_sub_v onh
2299 WHERE sub.organization_id = onh.organization_id
2300 AND sub.secondary_inventory_name = onh.subinventory_code
2301 AND NVL(sub.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
2302 AND sub.quantity_tracked = 1
2303 AND sub.organization_id = p_organization_id
2304 AND onh.inventory_item_id = p_inventory_item_id
2305 AND (p_revision IS NULL OR onh.revision = p_revision)
2306 AND secondary_inventory_name LIKE (p_subinventory_code)
2307 AND total_qoh > 0;
2308 ELSE
2309 Subinventory_Lov(x_sub_lov => x_sub_lov,
2310 p_organization_id => p_organization_id,
2311 p_subinv_code => p_subinventory_code,
2312 p_item_id => p_inventory_item_id,
2313 p_restrict_subinv => p_restrict_subinv);
2314 END IF;
2315 END Subinventory_OnHand_LoV;
2316
2317
2318 /*+========================================================================+
2319 | PROCEDURE NAME
2320 | Subinventory_LoV_Dispense
2321 |
2322 | USAGE
2323 |
2324 | ARGUMENTS
2325 | p_organization_id
2326 | p_subinv_code
2327 |
2328 | RETURNS
2329 | REF cursor x_sub_lov
2330 |
2331 | HISTORY
2332 | Created 26-Apr-05 Eddie Oumerretane
2333 |
2334 +========================================================================+*/
2335 PROCEDURE Subinventory_LoV_Dispense(x_sub_lov OUT NOCOPY t_genref,
2336 p_organization_id IN NUMBER,
2337 p_subinv_code IN VARCHAR) IS
2338 BEGIN
2339
2340 OPEN x_sub_lov FOR
2341 SELECT DISTINCT s.secondary_inventory_name
2342 , s.description
2343 , NVL(s.locator_type, 1)
2344 , NVL (b.concatenated_segments, ' ')
2345 ---,rtrim(substr(INV_UTILITIES.get_conc_segments(p_organization_id, d.locator_id),1,255)) locator
2346 ,d.lot_number
2347 ,d.dispensed_qty
2348 ,NVL(d.secondary_dispensed_qty,0)
2349 ,d.dispense_uom
2350 , dispense_id
2351 ,d.revision
2352 FROM mtl_secondary_inventories s,
2353 wms_item_locations_kfv b,
2354 gme_material_dispensing_gtmp d
2355 WHERE
2356 d.subinventory_code LIKE (p_subinv_code)
2357 AND s.secondary_inventory_name = d.subinventory_code
2358 AND s.organization_id = p_organization_id
2359 AND d.locator_id = b.inventory_location_id(+)
2360 AND b.organization_id (+) = p_organization_id
2361 ORDER BY s.secondary_inventory_name;
2362
2363 END Subinventory_LoV_Dispense;
2364
2365 /*+========================================================================+
2366 | PROCEDURE NAME
2367 | Subinventory_LoV_Rsrv
2368 |
2369 | USAGE
2370 |
2371 | ARGUMENTS
2372 | p_organization_id
2373 | p_batch_id
2374 | p_material_detail_id
2375 | p_item_id
2376 | p_subinventory_code
2377 | p_locator_id
2378 | p_lot_number
2379 |
2380 | RETURNS
2381 | REF cursor x_sub_lov
2382 |
2383 | HISTORY
2384 | Created 26-Apr-05 Eddie Oumerretane
2385 |
2386 +========================================================================+*/
2387 PROCEDURE Subinventory_LoV_Rsrv(
2388 x_sub_lov OUT NOCOPY t_genref
2389 , p_organization_id IN NUMBER
2390 , p_batch_id IN NUMBER
2391 , p_material_detail_id IN NUMBER
2392 , p_item_id IN NUMBER
2393 , p_subinventory_code IN VARCHAR2
2394 , p_locator_id IN NUMBER
2395 , p_lot_number IN VARCHAR2
2396 ) IS
2397
2398 BEGIN
2399
2400 OPEN x_sub_lov FOR
2401 SELECT DISTINCT msi.secondary_inventory_name
2402 , msi.description
2403 , NVL(msi.locator_type, 1)
2404 FROM mtl_secondary_inventories msi,
2405 mtl_reservations mr
2406 WHERE mr.organization_id = p_organization_id
2407 AND mr.inventory_item_id = p_item_id
2408 AND NVL(mr.lot_number,0) = NVL(NVL(p_lot_number, mr.lot_number),0)
2409 AND demand_source_type_id = gme_common_pvt.g_txn_source_type
2410 AND demand_source_header_id = p_batch_id
2411 AND NVL(demand_source_line_id, -1) = NVL(p_material_detail_id, -1)
2412 AND subinventory_code LIKE (p_subinventory_code)
2413 AND NVL(mr.locator_id, -1) = NVL(NVL(p_locator_id, mr.locator_id), -1)
2414 AND subinventory_code = msi.secondary_inventory_name
2415 AND msi.organization_id = p_organization_id;
2416
2417 END Subinventory_LoV_Rsrv;
2418
2419
2420
2421 /* Bug#5663458
2422 * Created the following procedure. This procedure is to get subinventories from
2423 * DLR of material line.
2424 */
2425 PROCEDURE Ing_Subinventory_LoV_Rsrv(
2426 x_sub_lov OUT NOCOPY t_genref
2427 , p_organization_id IN NUMBER
2428 , p_batch_id IN NUMBER
2429 , p_material_detail_id IN NUMBER
2430 , p_item_id IN NUMBER
2431 , p_subinventory_code IN VARCHAR2
2432 ) IS
2433
2434 BEGIN
2435
2436 OPEN x_sub_lov FOR
2437 SELECT DISTINCT msi.secondary_inventory_name
2438 , msi.description
2439 , NVL(msi.locator_type, 1)
2440 , mr.revision
2441 FROM mtl_secondary_inventories msi,
2442 mtl_reservations mr
2443 WHERE mr.subinventory_code = msi.secondary_inventory_name
2444 AND mr.organization_id = p_organization_id
2445 AND mr.inventory_item_id = p_item_id
2446 AND demand_source_header_id = p_batch_id
2447 AND demand_source_type_id = gme_common_pvt.g_txn_source_type
2448 AND demand_source_line_id = p_material_detail_id
2449 AND subinventory_code LIKE (p_subinventory_code)
2450 AND msi.organization_id = p_organization_id;
2451
2452 END Ing_Subinventory_LoV_Rsrv;
2453
2454
2455 /*+========================================================================+
2456 | PROCEDURE NAME
2457 | Subinventory_Lov_Temp_Rsrv
2458 |
2459 | USAGE
2460 |
2461 | ARGUMENTS
2462 | p_organization_id
2463 | p_item_id
2464 | p_revision
2465 | p_lot_number
2466 | p_subinv_code
2467 |
2468 | RETURNS
2469 | REF cursor x_sub_lov
2470 |
2471 | HISTORY
2472 | Created 26-Apr-05 Eddie Oumerretane
2473 |
2474 +========================================================================+*/
2475 PROCEDURE Subinventory_Lov_Temp_Rsrv(x_sub_lov OUT NOCOPY t_genref,
2476 p_organization_id IN NUMBER,
2477 p_item_id IN NUMBER,
2478 p_revision IN VARCHAR2,
2479 p_lot_number IN VARCHAR2,
2480 p_subinv_code IN VARCHAR) IS
2481 BEGIN
2482
2483 OPEN x_sub_lov FOR
2484
2485 SELECT t.subinventory_code,
2486 msi.description,
2487 NVL(msi.locator_type, 1),
2488 t.atr
2489 FROM mtl_secondary_inventories msi,
2490 mtl_rsv_quantities_temp t
2491 WHERE node_level = 4
2492 AND t.organization_id = p_organization_id
2493 AND t.inventory_item_id = p_item_id
2494 AND nvl(t.revision,1) = nvl(p_revision, 1)
2495 AND nvl(t.lot_number,0) = nvl(p_lot_number, 0)
2496 AND t.subinventory_code LIKE (p_subinv_code)
2497 AND t.subinventory_code = msi.secondary_inventory_name
2498 AND msi.organization_id = p_organization_id
2499 AND msi.quantity_tracked = 1
2500 AND msi.reservable_type=1
2501 ORDER BY t.subinventory_code, msi.description;
2502
2503 END Subinventory_Lov_Temp_Rsrv;
2504
2505 /*+========================================================================+
2506 | PROCEDURE NAME
2507 | Locator_LoV
2508 |
2509 | USAGE
2510 |
2511 | ARGUMENTS
2512 | p_organization_id
2513 | p_subinventory_code
2514 | p_subinventory_item_id
2515 | p_concatenated_segments
2516 | p_restrict_locators
2517 |
2518 | RETURNS
2519 | REF cursor x_locators
2520 |
2521 | HISTORY
2522 | Created 26-Apr-05 Eddie Oumerretane
2523 | Namit S. Bug4917215 01Feb06. Changed queries to dynamic sql.
2524 |
2525 +========================================================================+*/
2526 PROCEDURE Locator_LoV(
2527 x_locators OUT NOCOPY t_genref
2528 , p_organization_id IN NUMBER
2529 , p_subinventory_code IN VARCHAR2
2530 , p_inventory_item_id IN NUMBER
2531 , p_concatenated_segments IN VARCHAR2
2532 , p_restrict_locators IN NUMBER
2533 ) IS
2534
2535 sqlstmt VARCHAR2(1000);
2536
2537 BEGIN
2538
2539 -- Namit S. Bug4917215 01Feb06. Changed both queries to dynamic SQL to improve perf.
2540
2541 IF p_restrict_locators = 1 THEN
2542
2543 sqlstmt := ' SELECT a.concatenated_segments, '
2544 ||' a.description, '
2545 ||' a.inventory_location_id '
2546 ||' FROM wms_item_locations_kfv a, '
2547 ||' mtl_secondary_locators b '
2548 ||' WHERE b.organization_id = :p_organization_Id '
2549 ||' AND b.inventory_item_id = :p_Inventory_Item_Id '
2550 ||' AND b.subinventory_code = :p_Subinventory_Code '
2551 ||' AND a.inventory_location_id = b.secondary_locator '
2552 ||' and nvl(a.disable_date, trunc(SYSDATE+1)) > trunc(SYSDATE) '
2553 ||' AND a.concatenated_segments LIKE (:p_concatenated_segments ) '
2554 ||' ORDER BY 1 ';
2555 OPEN x_Locators FOR sqlstmt USING p_organization_id, p_Inventory_Item_Id, p_Subinventory_Code, p_concatenated_segments;
2556
2557 ELSE
2558
2559 sqlstmt := ' SELECT concatenated_segments, '
2560 ||' description, '
2561 ||' inventory_location_id '
2562 ||' FROM wms_item_locations_kfv '
2563 ||' WHERE organization_id = :p_organization_id '
2564 ||' AND subinventory_code = :p_subinventory_code '
2565 ||' AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE) '
2566 ||' AND concatenated_segments LIKE (:p_concatenated_segments) '
2567 ||' ORDER BY 1 ';
2568
2569 OPEN x_Locators FOR sqlstmt USING p_organization_id, p_Subinventory_Code, p_concatenated_segments;
2570
2571 END IF;
2572
2573 END Locator_LoV;
2574
2575 /* Bug#5663458
2576 * Created the following procedure. This procedure is to get locators that have
2577 * on hand. If the GME parameters is not set we call master LoV directly
2578 */
2579 PROCEDURE Locator_OnHand_LoV(
2580 x_locators OUT NOCOPY t_genref
2581 , p_organization_id IN NUMBER
2582 , p_subinventory_code IN VARCHAR2
2583 , p_inventory_item_id IN NUMBER
2584 , p_locator IN VARCHAR2
2585 , p_revision IN VARCHAR2
2586 , p_restrict_locators IN NUMBER
2587 )
2588 IS
2589 CURSOR c_get_locind IS
2590 SELECT subinv_loc_ind
2591 FROM gme_parameters
2592 WHERE organization_id = p_organization_id;
2593
2594 l_ind NUMBER(1) := 0;
2595 BEGIN
2596 OPEN c_get_locind;
2597 FETCH c_get_locind INTO l_ind;
2598 CLOSE c_get_locind;
2599
2600 IF l_ind = 1 THEN
2601 OPEN x_locators FOR
2602 SELECT DISTINCT loc.concatenated_segments locator, loc.description,
2603 onh.locator_id
2604 FROM wms_item_locations_kfv loc, mtl_onhand_locator_v onh
2605 WHERE loc.organization_id = onh.organization_id
2606 AND loc.inventory_location_id = onh.locator_id
2607 AND NVL(loc.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
2608 AND loc.organization_id = p_organization_id
2609 AND onh.inventory_item_id = p_inventory_item_id
2610 AND (p_revision IS NULL OR onh.revision = p_revision)
2611 AND onh.subinventory_code = p_subinventory_code
2612 AND concatenated_segments LIKE (p_locator)
2613 AND onh.total_qoh > 0 ;
2614 ELSE
2615 Locator_LoV(
2616 x_locators => x_locators
2617 , p_organization_id => p_organization_id
2618 , p_subinventory_code => p_subinventory_code
2619 , p_inventory_item_id => p_inventory_item_id
2620 , p_concatenated_segments => p_locator
2621 , p_restrict_locators => p_restrict_locators
2622 );
2623 END IF;
2624 END Locator_OnHand_LoV;
2625
2626 /*+========================================================================+
2627 | PROCEDURE NAME
2628 | Locator_LoV_Rsrv
2629 |
2630 | USAGE
2631 |
2632 | ARGUMENTS
2633 | p_organization_id
2634 | p_batch_id
2635 | p_material_detail_id
2636 | p_item_id
2637 | p_subinventory_code
2638 | p_locator
2639 | p_lot_number
2640 |
2641 | RETURNS
2642 | REF cursor x_locators
2643 |
2644 | HISTORY
2645 | Created 26-Apr-05 Eddie Oumerretane
2646 |
2647 +========================================================================+*/
2648 PROCEDURE Locator_LoV_Rsrv(
2649 x_locators OUT NOCOPY t_genref
2650 , p_organization_id IN NUMBER
2651 , p_batch_id IN NUMBER
2652 , p_material_detail_id IN NUMBER
2653 , p_item_id IN NUMBER
2654 , p_subinventory_code IN VARCHAR2
2655 , p_locator IN VARCHAR2
2656 , p_lot_number IN VARCHAR2
2657 ) IS
2658 BEGIN
2659
2660 OPEN x_Locators FOR
2661 SELECT a.concatenated_segments,
2662 a.description,
2663 a.inventory_location_id
2664 FROM wms_item_locations_kfv a,
2665 mtl_reservations mr
2666 WHERE mr.organization_id = p_organization_id
2667 AND mr.inventory_item_id = p_item_id
2668 AND NVL(mr.lot_number,0) = NVL(NVL(p_lot_number, mr.lot_number),0)
2669 AND mr.demand_source_type_id = gme_common_pvt.g_txn_source_type
2670 AND mr.demand_source_header_id = p_batch_id
2671 AND NVL(demand_source_line_id, -1) = NVL(p_material_detail_id, -1)
2672 AND mr.subinventory_code = p_subinventory_code
2673 AND a.concatenated_segments LIKE (p_locator)
2674 AND mr.locator_id = a.inventory_location_id
2675 ORDER BY 1;
2676
2677 END Locator_LoV_Rsrv;
2678
2679
2680
2681 /* Bug#5663458
2682 * Created the following procedure. This procedure is to get the locators
2683 * from DLR of selected material line.
2684 */
2685 PROCEDURE Ing_Locator_LoV_Rsrv(
2686 x_locators OUT NOCOPY t_genref
2687 , p_organization_id IN NUMBER
2688 , p_batch_id IN NUMBER
2689 , p_material_detail_id IN NUMBER
2690 , p_item_id IN NUMBER
2691 , p_subinventory_code IN VARCHAR2
2692 , p_locator IN VARCHAR2
2693 ) IS
2694 BEGIN
2695
2696 OPEN x_Locators FOR
2697 SELECT a.concatenated_segments,
2698 a.description,
2699 a.inventory_location_id
2700 FROM wms_item_locations_kfv a,
2701 mtl_reservations mr
2702 WHERE mr.locator_id = a.inventory_location_id
2703 AND mr.organization_id = p_organization_id
2704 AND mr.inventory_item_id = p_item_id
2705 AND mr.demand_source_type_id = gme_common_pvt.g_txn_source_type
2706 AND mr.demand_source_header_id = p_batch_id
2707 AND demand_source_line_id = p_material_detail_id
2708 AND mr.subinventory_code = p_subinventory_code
2709 AND a.concatenated_segments LIKE (p_locator)
2710 ORDER BY 1;
2711
2712 END Ing_Locator_LoV_Rsrv;
2713
2714 /*+========================================================================+
2715 | PROCEDURE NAME
2716 | Locator_LoV_Dispense
2717 |
2718 | USAGE
2719 |
2720 | ARGUMENTS
2721 | p_subinv_code
2722 | p_locator_code
2723 |
2724 | RETURNS
2725 | REF cursor x_locator_lov
2726 |
2727 | HISTORY
2728 | Created 26-Apr-05 Eddie Oumerretane
2729 | Namit S. Bug4917215 01Feb06. Changed queries to dynamic sql.
2730 |
2731 +========================================================================+*/
2732 PROCEDURE Locator_LoV_Dispense(
2733 x_locator_lov OUT NOCOPY t_genref
2734 , p_subinv_code IN VARCHAR2
2735 , p_locator_code IN VARCHAR2
2736 ) IS
2737 sqlstmt VARCHAR2(1000);
2738
2739 BEGIN
2740
2741 -- Namit S. Bug4917215 01Feb06. Changed to dynamic SQL to improve perf.
2742 sqlstmt := ' SELECT a.concatenated_segments '
2743 ||' ,a.description '
2744 ||' ,a.inventory_location_id '
2745 ||' ,t.lot_number '
2746 ||' ,t.dispensed_qty '
2747 ||' ,NVL(t.secondary_dispensed_qty, 0) '
2748 ||' ,dispense_uom '
2749 ||' ,dispense_id '
2750 ||' ,t.revision '
2751 ||' FROM wms_item_locations_kfv a, '
2752 ||' gme_material_dispensing_gtmp t '
2753 ||' WHERE '
2754 ||' t.subinventory_code = :p_subinv_code '
2755 ||' AND a.subinventory_code = t.subinventory_code '
2756 ||' AND a.concatenated_segments LIKE (:p_locator_code) '
2757 ||' AND a.inventory_location_id = t.locator_id ';
2758
2759 OPEN x_locator_lov FOR sqlstmt USING p_subinv_code, p_locator_code;
2760
2761 END Locator_LoV_Dispense;
2762
2763 /*+========================================================================+
2764 | PROCEDURE NAME
2765 | Locator_LoV_Temp_Rsrv
2766 |
2767 | USAGE
2768 |
2769 | ARGUMENTS
2770 | p_lot_number
2771 | p_organization_id
2772 | p_item_id
2773 | p_revision
2774 | p_subinv_code
2775 | p_locator_code
2776 |
2777 | RETURNS
2778 | REF cursor x_locator_lov
2779 |
2780 | HISTORY
2781 | Created 26-Apr-05 Eddie Oumerretane
2782 |
2783 +========================================================================+*/
2784 PROCEDURE Locator_LoV_Temp_Rsrv(
2785 x_locator_lov OUT NOCOPY t_genref
2786 , p_lot_number IN VARCHAR2
2787 , p_organization_id IN NUMBER
2788 , p_item_id IN NUMBER
2789 , p_revision IN VARCHAR2
2790 , p_subinv_code IN VARCHAR2
2791 , p_locator_code IN VARCHAR2
2792 ) IS
2793
2794
2795 BEGIN
2796
2797 OPEN x_locator_lov FOR
2798 SELECT a.concatenated_segments,
2799 a.description,
2800 a.inventory_location_id,
2801 ROUND(SUM(atr),4) atr
2802 FROM wms_item_locations_kfv a,
2803 mtl_rsv_quantities_temp t
2804 WHERE ((t.node_level = 4
2805 AND t.subinventory_code not in (select subinventory_code from
2806 mtl_rsv_quantities_temp where node_level = 5)) OR
2807 (t.node_level = 5))
2808 AND t.organization_id = p_organization_id
2809 AND t.inventory_item_id = p_item_id
2810 AND nvl(t.revision, 1) = nvl(p_revision, 1)
2811 AND t.subinventory_code = p_subinv_code
2812 AND a.concatenated_segments LIKE (p_locator_code)
2813 AND a.inventory_location_id = locator_id
2814 AND nvl(t.lot_number,0) = nvl(p_lot_number, 0)
2815 GROUP BY a.concatenated_segments,
2816 a.description,
2817 a.inventory_location_id;
2818
2819 END Locator_LoV_Temp_Rsrv;
2820
2821 /*+========================================================================+
2822 | PROCEDURE NAME
2823 | Lot_LoV_Rsrv
2824 |
2825 | USAGE
2826 |
2827 | ARGUMENTS
2828 | p_lot_number
2829 | p_organization_id
2830 | p_batch_id
2831 | p_material_detail_id
2832 | p_item_id
2833 | p_subinventory_code
2834 | p_locator_id
2835 |
2836 | RETURNS
2837 | REF cursor x_lot_num_lov
2838 |
2839 | HISTORY
2840 | Created 26-Apr-05 Eddie Oumerretane
2841 |
2842 +========================================================================+*/
2843 PROCEDURE Lot_LoV_Rsrv(
2844 x_lot_num_lov OUT NOCOPY t_genref
2845 , p_lot_number IN VARCHAR2
2846 , p_organization_id IN NUMBER
2847 , p_batch_id IN NUMBER
2848 , p_material_detail_id IN NUMBER
2849 , p_item_id IN NUMBER
2850 , p_subinventory_code IN VARCHAR2
2851 , p_locator_id IN NUMBER
2852 ) IS
2853
2854 l_date_format VARCHAR2(100);
2855
2856 BEGIN
2857
2858 FND_PROFILE.GET('MWA_DATE_FORMAT_MASK',l_date_format);
2859
2860 IF l_date_format IS NULL THEN
2861 FND_PROFILE.GET('ICX_DATE_FORMAT_MASK',l_date_format);
2862 END IF;
2863
2864 IF p_subinventory_code IS NOT NULL THEN
2865
2866 OPEN x_lot_num_lov FOR
2867 SELECT DISTINCT mln.lot_number
2868 i, TO_CHAR(mln.expiration_date, l_date_format)
2869 , mln.grade_code
2870 , mln.parent_lot_number
2871 FROM mtl_reservations mr,
2872 mtl_lot_numbers mln
2873 WHERE mr.organization_id = p_organization_id
2874 AND mr.organization_id = mln.organization_id
2875 AND mr.inventory_item_id = p_item_id
2876 AND mr.inventory_item_id = mln.inventory_item_id
2877 AND mr.lot_number LIKE (p_lot_number)
2878 AND mr.lot_number = mln.lot_number
2879 AND demand_source_type_id = gme_common_pvt.g_txn_source_type
2880 AND demand_source_header_id = p_batch_id
2881 AND NVL(demand_source_line_id, -1) = NVL(p_material_detail_id, -1)
2882 AND subinventory_code = p_subinventory_code
2883 AND NVL(mr.locator_id, -1) = NVL(NVL(p_locator_id, mr.locator_id), -1)
2884 AND NOT EXISTS (SELECT 1
2885 FROM mtl_material_transactions_temp
2886 WHERE reservation_id = mr.reservation_id);
2887 ELSE
2888
2889 OPEN x_lot_num_lov FOR
2890 SELECT DISTINCT mln.lot_number
2891 i, TO_CHAR(mln.expiration_date, l_date_format)
2892 , mln.grade_code
2893 , mln.parent_lot_number
2894 FROM mtl_reservations mr,
2895 mtl_lot_numbers mln
2896 WHERE mr.organization_id = p_organization_id
2897 AND mr.organization_id = mln.organization_id
2898 AND mr.inventory_item_id = p_item_id
2899 AND mr.inventory_item_id = mln.inventory_item_id
2900 AND mr.lot_number LIKE (p_lot_number)
2901 AND mr.lot_number = mln.lot_number
2902 AND demand_source_type_id = gme_common_pvt.g_txn_source_type
2903 AND demand_source_header_id = p_batch_id
2904 AND NVL(demand_source_line_id, -1) = NVL(p_material_detail_id, -1)
2905 AND NOT EXISTS (SELECT 1
2906 FROM mtl_material_transactions_temp
2907 WHERE reservation_id = mr.reservation_id);
2908 END IF;
2909
2910 END Lot_LoV_Rsrv;
2911
2912 /*+========================================================================+
2913 | PROCEDURE NAME
2914 | Lot_LoV_Txn
2915 |
2916 | USAGE
2917 |
2918 | ARGUMENTS
2919 | p_lot_number
2920 | p_organization_id
2921 | p_batch_id
2922 | p_material_detail_id
2923 |
2924 | RETURNS
2925 | REF cursor x_lot_num_lov
2926 |
2927 | HISTORY
2928 | Created 26-Apr-05 Eddie Oumerretane
2929 |
2930 +========================================================================+*/
2931 PROCEDURE Lot_LoV_Txn(
2932 x_lot_num_lov OUT NOCOPY t_genref
2933 , p_lot_number IN VARCHAR2
2934 , p_organization_id IN NUMBER
2935 , p_batch_id IN NUMBER
2936 , p_material_detail_id IN NUMBER
2937 , p_revision IN VARCHAR2
2938 , p_line_type IN NUMBER
2939 ) IS
2940 BEGIN
2941
2942 OPEN x_lot_num_lov FOR
2943 SELECT l.lot_num, ABS(l.txn_qty),ABS(l.txn_sec_qty)
2944 FROM
2945 (SELECT m2.revision,
2946 l2.lot_number lot_num,
2947 SUM(l2.primary_quantity) txn_qty,
2948 SUM(NVL(l2.secondary_transaction_quantity,0)) txn_sec_qty
2949 FROM mtl_material_transactions m2,
2950 mtl_transaction_lot_numbers l2
2951 WHERE l2.transaction_id = m2.transaction_id
2952 AND l2.lot_number LIKE (p_lot_number)
2953 AND m2.organization_id = p_organization_id
2954 AND m2.transaction_source_id = p_batch_id
2955 AND m2.trx_source_line_id = p_material_detail_id
2956 AND m2.transaction_source_type_id = gme_common_pvt.g_txn_source_type
2957 AND (p_revision IS NULL OR m2.revision = p_revision)
2958 GROUP BY m2.revision, l2.lot_number) l
2959 WHERE (p_line_type = -1 and l.txn_qty < 0) OR
2960 (p_line_type IN (1,2) and l.txn_qty > 0);
2961
2962 END Lot_Lov_Txn;
2963
2964 /*+========================================================================+
2965 | PROCEDURE NAME
2966 | Lot_Onhand_LoV
2967 |
2968 | USAGE
2969 |
2970 | ARGUMENTS
2971 | p_lot_number
2972 | p_organization_id
2973 | p_item_id
2974 | p_subinventory_code
2975 | p_locator_id
2976 |
2977 | RETURNS
2978 | REF cursor x_lot_num_lov
2979 |
2980 | HISTORY
2981 | Created 26-Apr-05 Eddie Oumerretane
2982 |
2983 +========================================================================+*/
2984 PROCEDURE Lot_OnHand_LoV(
2985 x_lot_num_lov OUT NOCOPY t_genref
2986 , p_lot_number IN VARCHAR2
2987 , p_organization_id IN NUMBER
2988 , p_item_id IN NUMBER
2989 , p_subinventory_code IN VARCHAR2
2990 , p_locator_id IN NUMBER
2991 ) IS
2992
2993 l_date_format VARCHAR2(100);
2994
2995 BEGIN
2996
2997 FND_PROFILE.GET('MWA_DATE_FORMAT_MASK',l_date_format);
2998
2999 IF l_date_format IS NULL THEN
3000 FND_PROFILE.GET('ICX_DATE_FORMAT_MASK',l_date_format);
3001 END IF;
3002
3003 IF p_subinventory_code IS NULL THEN
3004
3005 OPEN x_lot_num_lov FOR
3006 SELECT DISTINCT mln.lot_number
3007 , TO_CHAR(mln.expiration_date, l_date_format)
3008 , mln.grade_code
3009 , mln.parent_lot_number
3010 FROM mtl_lot_numbers mln
3011 WHERE mln.organization_id = p_organization_id
3012 AND mln.inventory_item_id = p_item_id
3013 AND mln.lot_number LIKE (p_lot_number)
3014 AND exists (SELECT '1' FROM mtl_onhand_quantities_detail moqd
3015 WHERE moqd.lot_number = mln.lot_number
3016 AND moqd.inventory_item_id = mln.inventory_item_id
3017 AND moqd.organization_id = mln.organization_id);
3018
3019 ELSE
3020
3021 OPEN x_lot_num_lov FOR
3022 SELECT DISTINCT mln.lot_number
3023 , TO_CHAR(mln.expiration_date, l_date_format)
3024 , mln.grade_code
3025 , mln.parent_lot_number
3026 FROM mtl_lot_numbers mln
3027 WHERE mln.organization_id = p_organization_id
3028 AND mln.inventory_item_id = p_item_id
3029 AND mln.lot_number LIKE (p_lot_number)
3030 AND exists (SELECT '1' FROM mtl_onhand_quantities_detail moqd
3031 WHERE moqd.lot_number = mln.lot_number
3032 AND moqd.inventory_item_id = mln.inventory_item_id
3033 AND moqd.organization_id = mln.organization_id
3034 AND moqd.subinventory_code = p_subinventory_code
3035 AND NVL(moqd.locator_id, -1) = NVL(NVL(p_locator_id, moqd.locator_id), -1));
3036 END IF;
3037
3038 END Lot_OnHand_LoV;
3039
3040
3041 /*+========================================================================+
3042 | PROCEDURE NAME
3043 | Lot_Onhand_Exp_LoV
3044 |
3045 | USAGE
3046 |
3047 | ARGUMENTS
3048 | p_lot_number
3049 | p_organization_id
3050 | p_item_id
3051 | p_subinventory_code
3052 | p_locator_id
3053 |
3054 | RETURNS
3055 | REF cursor x_lot_num_lov
3056 |
3057 | HISTORY
3058 | Created Bug#5261131 15-Sep-06 SivakumarG
3059
3060 | 17-Jun-11 Apeksha Mishra Bug 12562057
3061 | Remove explicit parameter names as it does not compile on versions below 11g.
3062 +========================================================================+*/
3063 PROCEDURE Lot_OnHand_Exp_LoV(
3064 x_lot_num_lov OUT NOCOPY t_genref
3065 , p_lot_number IN VARCHAR2
3066 , p_organization_id IN NUMBER
3067 , p_item_id IN NUMBER
3068 , p_subinventory_code IN VARCHAR2
3069 , p_locator_id IN NUMBER
3070 , p_revision IN VARCHAR2 --Bug#5867209
3071 , p_transaction_type IN NUMBER --Bug#8937132
3072 ) IS
3073
3074 l_date_format VARCHAR2(100);
3075
3076 BEGIN
3077
3078 FND_PROFILE.GET('MWA_DATE_FORMAT_MASK',l_date_format);
3079
3080 IF l_date_format IS NULL THEN
3081 FND_PROFILE.GET('ICX_DATE_FORMAT_MASK',l_date_format);
3082 END IF;
3083
3084 IF p_subinventory_code IS NULL THEN
3085 OPEN x_lot_num_lov FOR
3086 SELECT DISTINCT mln.lot_number
3087 , TO_CHAR(mln.expiration_date, l_date_format)
3088 , mln.grade_code
3089 , mln.parent_lot_number
3090 FROM mtl_lot_numbers mln
3091 WHERE mln.organization_id = p_organization_id
3092 AND mln.inventory_item_id = p_item_id
3093 AND mln.lot_number LIKE (p_lot_number)
3094 AND NVL(mln.expiration_date,SYSDATE+1) > SYSDATE --Bug#5092198
3095 /*Bug#8937132 including the check for the lot status allows the transaction or not*/
3096 AND (inv_material_status_grp.is_status_applicable(
3097 NULL
3098 ,NULL
3099 ,p_transaction_type
3100 ,NULL
3101 ,NULL
3102 ,p_organization_id
3103 ,p_item_id
3104 ,NULL
3105 ,NULL
3106 ,mln.lot_number
3107 ,NULL
3108 ,'O') = 'Y' )
3109 AND EXISTS (SELECT '1'
3110 FROM mtl_onhand_quantities_detail moqd
3111 WHERE moqd.lot_number = mln.lot_number
3112 AND moqd.inventory_item_id = mln.inventory_item_id
3113 AND moqd.organization_id = mln.organization_id
3114 AND (p_revision IS NULL OR revision = p_revision)); --Bug#5867209
3115
3116 ELSE
3117 OPEN x_lot_num_lov FOR
3118 SELECT DISTINCT mln.lot_number
3119 , TO_CHAR(mln.expiration_date, l_date_format)
3120 , mln.grade_code
3121 , mln.parent_lot_number
3122 FROM mtl_lot_numbers mln
3123 WHERE mln.organization_id = p_organization_id
3124 AND mln.inventory_item_id = p_item_id
3125 AND mln.lot_number LIKE (p_lot_number)
3126 AND NVL(mln.expiration_date,SYSDATE+1) > SYSDATE --Bug#5092198
3127 AND (inv_material_status_grp.is_status_applicable(
3128 NULL
3129 ,NULL
3130 ,p_transaction_type
3131 ,NULL
3132 ,NULL
3133 ,p_organization_id
3134 ,p_item_id
3135 ,p_subinventory_code
3136 ,p_locator_id
3137 ,mln.lot_number
3138 ,NULL
3139 ,'A') = 'Y')
3140 AND EXISTS (SELECT '1'
3141 FROM mtl_onhand_quantities_detail moqd
3142 WHERE moqd.lot_number = mln.lot_number
3143 AND moqd.inventory_item_id = mln.inventory_item_id
3144 AND moqd.organization_id = mln.organization_id
3145 AND moqd.subinventory_code = p_subinventory_code
3146 AND NVL(moqd.locator_id, -1) = NVL(NVL(p_locator_id, moqd.locator_id), -1)
3147 AND (p_revision IS NULL OR revision = p_revision)); --Bug#5867209
3148
3149 -- Bug 12670639 - Moved the following comment block which was causing an issue at customer site.
3150 /*Bug#8937132 including the check for the lot status allows the transaction or not*/
3151
3152 -- Bug 10178907 - Pass in granular level data (subinventory, locator and 'A') so that logic
3153 -- is accurate for status check. Also mapped parameters to explicit value.
3154 -- Bug 12562057 - Remove explicit parameter names as it does not compile on versions below 11g.
3155
3156
3157 /*
3158 AND (inv_material_status_grp.is_status_applicable(
3159 p_wms_installed => NULL
3160 ,p_trx_status_enabled => NULL
3161 ,p_trx_type_id => p_transaction_type
3162 ,p_lot_status_enabled => NULL
3163 ,p_serial_status_enabled => NULL
3164 ,p_organization_id => p_organization_id
3165 ,p_inventory_item_id => p_item_id
3166 ,p_sub_code => p_subinventory_code
3167 ,p_locator_id => p_locator_id
3168 ,p_lot_number => mln.lot_number
3169 ,p_serial_number => NULL
3170 ,p_object_type => 'A') = 'Y')
3171 */
3172
3173 END IF; /*p_subinventory_code IS NULL*/
3174 END Lot_OnHand_Exp_LoV;
3175
3176 /*+========================================================================+
3177 | PROCEDURE NAME
3178 | Lot_LoV
3179 |
3180 | USAGE
3181 |
3182 | ARGUMENTS
3183 | p_lot_number
3184 | p_organization_id
3185 | p_item_id
3186 |
3187 | RETURNS
3188 | REF cursor x_lot_num_lov
3189 |
3190 | HISTORY
3191 | Created 26-Apr-05 Eddie Oumerretane
3192 | Bug#5261131 entered the expired lots check
3193 +========================================================================+*/
3194 PROCEDURE Lot_LoV(
3195 x_lot_num_lov OUT NOCOPY t_genref
3196 , p_lot_number IN VARCHAR2
3197 , p_organization_id IN NUMBER
3198 , p_item_id IN NUMBER
3199 ) IS
3200
3201 l_date_format VARCHAR2(100);
3202
3203 BEGIN
3204
3205 FND_PROFILE.GET('MWA_DATE_FORMAT_MASK',l_date_format);
3206
3207 IF l_date_format IS NULL THEN
3208 FND_PROFILE.GET('ICX_DATE_FORMAT_MASK',l_date_format);
3209 END IF;
3210
3211 OPEN x_lot_num_lov FOR
3212 SELECT DISTINCT mln.lot_number
3213 , TO_CHAR(mln.expiration_date, l_date_format)
3214 , mln.grade_code
3215 , mln.parent_lot_number
3216 FROM mtl_lot_numbers mln
3217 WHERE mln.organization_id = p_organization_id
3218 AND mln.inventory_item_id = p_item_id
3219 AND mln.lot_number LIKE (p_lot_number)
3220 AND NVl(mln.expiration_date,SYSDATE+1) > SYSDATE; --Bug#5092198
3221
3222 END Lot_LoV;
3223
3224 /*+========================================================================+
3225 | PROCEDURE NAME
3226 | Lot_LoV_Dispense
3227 |
3228 | USAGE
3229 |
3230 | ARGUMENTS
3231 | p_lot_number
3232 | p_subinv_code
3233 | p_locator_id
3234 |
3235 | RETURNS
3236 | REF cursor x_lot_num_lov
3237 |
3238 | HISTORY
3239 | Created 26-Apr-05 Eddie Oumerretane
3240 |
3241 +========================================================================+*/
3242 PROCEDURE Lot_LoV_Dispense(
3243 x_lot_num_lov OUT NOCOPY t_genref
3244 , p_lot_number IN VARCHAR2
3245 , p_subinv_code IN VARCHAR2
3246 , p_locator_id IN NUMBER
3247 ) IS
3248
3249
3250 BEGIN
3251
3252 OPEN x_lot_num_lov FOR
3253 SELECT lot_number,
3254 dispensed_qty,
3255 NVL(secondary_dispensed_qty,0),
3256 dispense_uom,
3257 dispense_id,
3258 revision
3259 FROM
3260 GME_MATERIAL_DISPENSING_GTMP
3261 WHERE subinventory_code = NVL(p_subinv_code, subinventory_code)
3262 AND NVL(locator_id,-1) = NVL(p_locator_id, -1)
3263 AND lot_number LIKE (p_lot_number)
3264 ORDER BY lot_number;
3265
3266 END Lot_LoV_Dispense;
3267
3268 /*+========================================================================+
3269 | PROCEDURE NAME
3270 | Lot_LoV_Dispense
3271 |
3272 | USAGE
3273 |
3274 | ARGUMENTS
3275 | p_lot_number
3276 | p_organization_id
3277 | p_item_id
3278 | p_revision
3279 | p_subinv_code
3280 | p_locator_id
3281 |
3282 | RETURNS
3283 | REF cursor x_lot_num_lov
3284 |
3285 | HISTORY
3286 | Created 26-Apr-05 Eddie Oumerretane
3287 |
3288 +========================================================================+*/
3289 PROCEDURE Lot_LoV_Temp_Rsrv(
3290 x_lot_num_lov OUT NOCOPY t_genref
3291 , p_lot_number IN VARCHAR2
3292 , p_organization_id IN NUMBER
3293 , p_item_id IN NUMBER
3294 , p_revision IN VARCHAR2
3295 , p_subinv_code IN VARCHAR2
3296 , p_locator_id IN NUMBER
3297 ) IS
3298
3299 l_date_format VARCHAR2(100);
3300
3301 BEGIN
3302
3303 FND_PROFILE.GET('MWA_DATE_FORMAT_MASK',l_date_format);
3304
3305 IF l_date_format IS NULL THEN
3306 FND_PROFILE.GET('ICX_DATE_FORMAT_MASK',l_date_format);
3307 END IF;
3308
3309 OPEN x_lot_num_lov FOR
3310 SELECT lot_number,
3311 ---subinventory_code,
3312 ---decode(t.node_level,4,'',5,rtrim(substr(INV_UTILITIES.get_conc_segments(organization_id, locator_id),1,255)),'') locator,
3313 ROUND(SUM(atr),4) atr,
3314 grade_code
3315 ---locator_id
3316 FROM mtl_rsv_quantities_temp t
3317 WHERE ((t.node_level = 4
3318 AND subinventory_code not in (select subinventory_code from
3319 mtl_rsv_quantities_temp where node_level = 5)) OR
3320 (t.node_level = 5))
3321 AND t.organization_id = p_organization_id
3322 AND t.inventory_item_id = p_item_id
3323 AND nvl(t.revision, 1) = nvl(p_revision, 1)
3324 ---AND t.revision = nvl(p_revision, t.revision)
3325 ---AND subinventory_code = NVL(p_subinv_code, subinventory_code)
3326 ---AND locator_id = NVL(p_locator_id, locator_id)
3327 AND lot_number LIKE (p_lot_number)
3328 GROUP BY lot_number, grade_code
3329 ORDER BY lot_number;
3330
3331 END Lot_LoV_Temp_Rsrv;
3332
3333
3334 /* Bug#5663458
3335 * Created the following procedure. This procedure is to get all pending product lots
3336 * for a selected material line. Used for Use Pnd Lot field in mobile
3337 */
3338 PROCEDURE PndLot_LoV(
3339 x_lot_num_lov OUT NOCOPY t_genref
3340 , p_org_id IN NUMBER
3341 , p_batch_id IN NUMBER
3342 , p_material_detail_id IN NUMBER
3343 ) IS
3344 BEGIN
3345
3346 OPEN x_lot_num_lov FOR
3347 SELECT lo.sequence,
3348 NVL(lo.revision,' '),
3349 NVL(m.subinventory,' '),
3350 NVl(loc.concatenated_segments,' ') locator,
3351 NVL(mln.parent_lot_number,' '),
3352 lo.lot_number,
3353 NVL(quantity, 0),
3354 lo.pending_product_lot_id,
3355 NVL(m.locator_id,-1)
3356 FROM gme_material_details m,
3357 gme_pending_product_lots lo,
3358 wms_item_locations_kfv loc,
3359 mtl_lot_numbers mln
3360 WHERE m.batch_id = lo.batch_id
3361 AND m.material_detail_id = lo.material_detail_id
3362 AND m.locator_id = loc.inventory_location_id(+)
3363 AND m.inventory_item_id = mln.inventory_item_id
3364 AND lo.lot_number = mln.lot_number
3365 AND mln.organization_id = p_org_id
3366 AND m.batch_id = p_batch_id
3367 AND m.material_detail_id = p_material_detail_id
3368 ORDER BY sequence;
3369 END PndLot_LoV;
3370
3371 /*+========================================================================+
3372 | PROCEDURE NAME
3373 | Pending_Lot_LoV
3374 |
3375 | USAGE
3376 |
3377 | ARGUMENTS
3378 | p_lot_number
3379 | p_batch_id
3380 | p_material_detail_id
3381 |
3382 | RETURNS
3383 | REF cursor x_lot_num_lov
3384 |
3385 | HISTORY
3386 | Created 26-Apr-05 Eddie Oumerretane
3387 |
3388 +========================================================================+*/
3389 PROCEDURE Pending_Lot_LoV(
3390 x_lot_num_lov OUT NOCOPY t_genref
3391 , p_lot_number IN VARCHAR2
3392 , p_batch_id IN NUMBER
3393 , p_material_detail_id IN NUMBER
3394 ) IS
3395 BEGIN
3396 OPEN x_lot_num_lov FOR
3397 SELECT DISTINCT lot_number
3398 FROM
3399 GME_PENDING_PRODUCT_LOTS
3400 WHERE
3401 batch_id = p_batch_id AND
3402 material_detail_id = NVL(p_material_detail_id, material_detail_id) AND
3403 lot_number LIKE (p_lot_number)
3404 ORDER BY 1;
3405
3406 END Pending_Lot_LoV;
3407
3408 /* Bug#5663458
3409 * Created the following procedure to fetch the pending lots with revision
3410 */
3411 PROCEDURE Pending_Rev_Lot_LoV(
3412 x_lot_num_lov OUT NOCOPY t_genref
3413 , p_lot_number IN VARCHAR2
3414 , p_batch_id IN NUMBER
3415 , p_material_detail_id IN NUMBER
3416 , p_revision IN VARCHAR2
3417 ) IS
3418 BEGIN
3419 OPEN x_lot_num_lov FOR
3420 SELECT DISTINCT lot_number, quantity, NVL(secondary_quantity,0),
3421 pending_product_lot_id
3422 FROM
3423 GME_PENDING_PRODUCT_LOTS
3424 WHERE
3425 batch_id = p_batch_id AND
3426 material_detail_id = NVL(p_material_detail_id, material_detail_id) AND
3427 lot_number LIKE (p_lot_number) AND
3428 (p_revision IS NULL OR revision = p_revision)
3429 ORDER BY 1;
3430
3431 END Pending_Rev_Lot_LoV;
3432
3433 /*+========================================================================+
3434 | PROCEDURE NAME
3435 | Pending_Parent_Lot_LoV
3436 |
3437 | USAGE
3438 |
3439 | ARGUMENTS
3440 | p_lot_number
3441 | p_batch_id
3442 | p_material_detail_id
3443 |
3444 | RETURNS
3445 | REF cursor x_lot_num_lov
3446 |
3447 | HISTORY
3448 | Created 21-Aug-06 Shrikant Nene
3449 |
3450 +========================================================================+*/
3451 PROCEDURE Pending_Parent_Lot_LoV(
3452 x_lot_num_lov OUT NOCOPY t_genref
3453 , p_lot_number IN VARCHAR2
3454 , p_batch_id IN NUMBER
3455 , p_material_detail_id IN NUMBER
3456 ) IS
3457 BEGIN
3458
3459 OPEN x_lot_num_lov FOR
3460 SELECT lot_number
3461 FROM mtl_lot_numbers l, gme_material_details d
3462 WHERE l.inventory_item_id = d.inventory_item_id
3463 AND l.organization_id = d.organization_id
3464 AND d.material_detail_id = p_material_detail_id
3465 AND l.lot_number LIKE (p_lot_number)
3466 UNION
3467 SELECT parent_lot_number
3468 FROM mtl_lot_numbers l, gme_material_details d
3469 WHERE l.inventory_item_id = d.inventory_item_id
3470 AND l.organization_id = d.organization_id
3471 AND d.material_detail_id = p_material_detail_id
3472 AND l.parent_lot_number LIKE (p_lot_number)
3473 ORDER BY 1;
3474
3475 END Pending_Parent_Lot_LoV;
3476 /*+========================================================================+
3477 | PROCEDURE NAME
3478 | UoM_LoV
3479 |
3480 | USAGE
3481 |
3482 | ARGUMENTS
3483 | p_uom_code
3484 | p_organization_id
3485 | p_item_id
3486 |
3487 | RETURNS
3488 | REF cursor x_uom_lov
3489 |
3490 | HISTORY
3491 | Created 26-Apr-05 Eddie Oumerretane
3492 | Namit S. Bug4917215 01Feb06. Changed queries to dynamic sql.
3493 |
3494 +========================================================================+*/
3495 PROCEDURE UoM_LoV(
3496 x_uom_lov OUT NOCOPY t_genref
3497 , p_uom_code IN VARCHAR2
3498 , p_organization_id IN NUMBER
3499 , p_item_id IN NUMBER
3500 ) IS
3501
3502 sqlstmt VARCHAR2(2000);
3503
3504 BEGIN
3505
3506 -- Namit S. Bug4917215 01Feb06. Used the mtl_item_uoms_view definition query with dynamic sql to improve perf.
3507
3508 sqlstmt := ' SELECT DISTINCT mtluom2.uom_code, '
3509 ||' mtluom2.unit_of_measure '
3510 ||' FROM mtl_system_items_b mtlitm1, '
3511 ||' mtl_units_of_measure_tl mtluom2, '
3512 ||' mtl_uom_conversions mtlucv '
3513 ||' WHERE mtlitm1.inventory_item_id = :p_item_id '
3514 ||' AND mtlitm1.organization_id = :p_org_id '
3515 ||' AND mtluom2.uom_code = mtlucv.uom_code '
3516 ||' AND ( mtlucv.inventory_item_id = :p_item_id OR mtlucv.inventory_item_id = 0) '
3517 ||' AND mtluom2.language = USERENV('||''''||'LANG'||''''||') '
3518 ||' AND ( ( mtlitm1.allowed_units_lookup_code IN (1, 3) '
3519 ||' AND mtlucv.inventory_item_id = mtlitm1.inventory_item_id '
3520 ||' OR ( mtlucv.inventory_item_id = 0 '
3521 ||' AND mtluom2.base_uom_flag = '||''''||'Y'||''''
3522 ||' AND mtluom2.uom_class = mtlucv.uom_class '
3523 ||' AND mtlucv.uom_class IN ( '
3524 ||' SELECT mtlpri1.uom_class '
3525 ||' FROM mtl_units_of_measure mtlpri1 '
3526 ||' WHERE mtlpri1.uom_code = mtlitm1.primary_uom_code) '
3527 ||' ) '
3528 ||' OR ( mtlucv.inventory_item_id = 0 '
3529 ||' AND mtlucv.uom_code IN ( '
3530 ||' SELECT mtlucc1.to_uom_code '
3531 ||' FROM mtl_uom_class_conversions mtlucc1 '
3532 ||' WHERE mtlucc1.inventory_item_id = mtlitm1.inventory_item_id '
3533 ||' AND NVL (mtlucc1.disable_date, TRUNC (SYSDATE) + 1) > TRUNC (SYSDATE)) '
3534 ||' ) '
3535 ||' ) '
3536 ||' OR ( mtlitm1.allowed_units_lookup_code IN (2, 3) '
3537 ||' AND mtlucv.inventory_item_id = 0 '
3538 ||' AND ( mtlucv.uom_class IN ( '
3539 ||' SELECT mtlucc.to_uom_class '
3540 ||' FROM mtl_uom_class_conversions mtlucc '
3541 ||' WHERE mtlucc.inventory_item_id = mtlitm1.inventory_item_id '
3542 ||' AND NVL (mtlucc.disable_date, TRUNC (SYSDATE) + 1) > TRUNC (SYSDATE)) '
3543 ||' OR mtlucv.uom_class = '
3544 ||' (SELECT mtlpri.uom_class '
3545 ||' FROM mtl_units_of_measure mtlpri '
3546 ||' WHERE mtlpri.uom_code = mtlitm1.primary_uom_code) '
3547 ||' ) '
3548 ||' ) '
3549 ||' ) '
3550 ||' AND NVL (mtlucv.disable_date, TRUNC (SYSDATE) + 1) > TRUNC (SYSDATE) '
3551 ||' AND NVL (mtluom2.disable_date, TRUNC (SYSDATE) + 1) > TRUNC (SYSDATE) '
3552 ||' AND mtluom2.uom_code like (:p_uom_code) '
3553 ||' ORDER BY 1 ';
3554 OPEN x_uom_lov FOR sqlstmt USING p_item_id, p_organization_id, p_item_id, p_uom_code;
3555
3556 END UoM_LoV;
3557
3558
3559 /*+========================================================================+
3560 | PROCEDURE NAME
3561 | All_Line_Type_LoV
3562 |
3563 | USAGE
3564 |
3565 | ARGUMENTS
3566 | p_organization_id
3567 | p_batch_id
3568 | p_line_type
3569 | p_step_no
3570 |
3571 | RETURNS
3572 | REF cursor x_line_type_cursor
3573 |
3574 | HISTORY
3575 | Created 26-Apr-05 Eddie Oumerretane
3576 |
3577 +========================================================================+*/
3578 PROCEDURE All_Line_Type_LoV
3579 ( x_line_type_cursor OUT NOCOPY t_genref
3580 , p_organization_id IN NUMBER
3581 , p_batch_id IN NUMBER
3582 , p_line_type IN VARCHAR2
3583 , p_step_no IN VARCHAR2
3584 )
3585 IS
3586
3587 l_line_type VARCHAR2(2);
3588
3589 BEGIN
3590
3591 IF p_line_type = '1' OR p_line_type = '1%' THEN
3592 l_line_type := -1;
3593 ELSIF p_line_type = '2' OR p_line_type = '2%' THEN
3594 l_line_type := 1;
3595 ELSIF p_line_type = '3' OR p_line_type = '3%' THEN
3596 l_line_type := 2;
3597 ELSE
3598 l_line_type := p_line_type;
3599 END IF;
3600
3601 IF p_step_no IS NOT NULL THEN
3602
3603 OPEN x_line_type_cursor FOR
3604 SELECT distinct decode(d.line_type,-1,'1',+1,'2',+2,'3'),
3605 meaning,
3606 d.line_type
3607 FROM gme_batch_header h,
3608 gme_material_details d,
3609 gme_batch_step_items si,
3610 gme_batch_steps s,
3611 gem_lookups lkup
3612 WHERE h.organization_id = p_organization_id
3613 AND h.batch_id = p_batch_id
3614 AND h.batch_id = d.batch_id
3615 AND d.batch_id = si.batch_id
3616 AND si.material_detail_id = d.material_detail_id
3617 AND s.batchstep_no = p_step_no
3618 AND si.batchstep_id = s.batchstep_id
3619 AND h.batch_type = 0
3620 AND lkup.lookup_type = 'GMD_FORMULA_ITEM_TYPE'
3621 AND lkup.lookup_code = d.line_type
3622 AND sysdate BETWEEN NVL(lkup.start_date_active, SYSDATE)
3623 AND NVL(lkup.end_date_active, sysdate)
3624 AND lkup.enabled_flag = 'Y'
3625 AND d.line_type LIKE (l_line_type)
3626 ORDER BY 1;
3627
3628 ELSE
3629
3630 OPEN x_line_type_cursor FOR
3631 SELECT distinct decode(d.line_type,-1,'1',+1,'2',+2,'3'),
3632 meaning,
3633 d.line_type
3634 FROM gme_batch_header h,
3635 gme_material_details d,
3636 gem_lookups lkup
3637 WHERE h.organization_id = p_organization_id
3638 AND h.batch_id = p_batch_id
3639 AND h.batch_id = d.batch_id
3640 AND h.batch_type = 0
3641 AND lkup.lookup_type = 'GMD_FORMULA_ITEM_TYPE'
3642 AND lkup.lookup_code = d.line_type
3643 AND sysdate BETWEEN NVL(lkup.start_date_active, SYSDATE)
3644 AND NVL(lkup.end_date_active, sysdate)
3645 AND lkup.enabled_flag = 'Y'
3646 AND d.line_type LIKE (l_line_type)
3647 ORDER BY 1;
3648
3649 END IF;
3650
3651 END All_Line_Type_LoV;
3652
3653 /*+========================================================================+
3654 | PROCEDURE NAME
3655 | Prod_Line_Type_LoV
3656 |
3657 | USAGE
3658 |
3659 | ARGUMENTS
3660 | p_organization_id
3661 | p_batch_id
3662 | p_line_type
3663 | p_step_no
3664 |
3665 | RETURNS
3666 | REF cursor x_line_type_cursor
3667 |
3668 | HISTORY
3669 | Created 26-Apr-05 Eddie Oumerretane
3670 |
3671 +========================================================================+*/
3672 PROCEDURE Prod_Line_Type_LoV
3673 ( x_line_type_cursor OUT NOCOPY t_genref
3674 , p_organization_id IN NUMBER
3675 , p_batch_id IN NUMBER
3676 , p_line_type IN VARCHAR2
3677 , p_step_no IN VARCHAR2
3678 )
3679 IS
3680
3681 l_line_type VARCHAR2(2);
3682
3683 BEGIN
3684
3685 IF p_line_type = '1' OR p_line_type = '1%' THEN
3686 l_line_type := -1;
3687 ELSIF p_line_type = '2' OR p_line_type = '2%' THEN
3688 l_line_type := 1;
3689 ELSIF p_line_type = '3' OR p_line_type = '3%' THEN
3690 l_line_type := 2;
3691 ELSE
3692 l_line_type := p_line_type;
3693 END IF;
3694
3695 IF p_step_no IS NOT NULL THEN
3696
3697 OPEN x_line_type_cursor FOR
3698 SELECT distinct decode(d.line_type,-1,'1',+1,'2',+2,'3'),
3699 meaning,
3700 d.line_type
3701 FROM gme_batch_header h,
3702 gme_material_details d,
3703 gme_batch_step_items si,
3704 gme_batch_steps s,
3705 gem_lookups lkup
3706 WHERE h.organization_id = p_organization_id
3707 AND h.batch_id = p_batch_id
3708 AND h.batch_id = d.batch_id
3709 AND d.batch_id = si.batch_id
3710 AND si.material_detail_id = d.material_detail_id
3711 AND s.batchstep_no = p_step_no
3712 AND si.batchstep_id = s.batchstep_id
3713 AND h.batch_type = 0
3714 AND lkup.lookup_type = 'GMD_FORMULA_ITEM_TYPE'
3715 AND lkup.lookup_code = d.line_type
3716 AND d.line_type IN (1,2)
3717 AND sysdate BETWEEN NVL(lkup.start_date_active, SYSDATE)
3718 AND NVL(lkup.end_date_active, sysdate)
3719 AND lkup.enabled_flag = 'Y'
3720 AND d.line_type LIKE (l_line_type)
3721 ORDER BY 1;
3722
3723 ELSE
3724
3725 OPEN x_line_type_cursor FOR
3726 SELECT distinct decode(d.line_type,-1,'1',+1,'2',+2,'3'),
3727 meaning,
3728 d.line_type
3729 FROM gme_batch_header h,
3730 gme_material_details d,
3731 gem_lookups lkup
3732 WHERE h.organization_id = p_organization_id
3733 AND h.batch_id = p_batch_id
3734 AND h.batch_id = d.batch_id
3735 AND h.batch_type = 0
3736 AND lkup.lookup_type = 'GMD_FORMULA_ITEM_TYPE'
3737 AND lkup.lookup_code = d.line_type
3738 AND d.line_type IN (1,2)
3739 AND sysdate BETWEEN NVL(lkup.start_date_active, SYSDATE)
3740 AND NVL(lkup.end_date_active, sysdate)
3741 AND lkup.enabled_flag = 'Y'
3742 AND d.line_type LIKE (l_line_type)
3743 ORDER BY 1;
3744
3745 END IF;
3746
3747 END Prod_Line_Type_Lov;
3748
3749 /*+========================================================================+
3750 | PROCEDURE NAME
3751 | revision_LoV
3752 |
3753 | USAGE
3754 |
3755 | ARGUMENTS
3756 | p_org_id
3757 | p_inventory_item_id
3758 | p_revision
3759 |
3760 | RETURNS
3761 | REF cursor x_revision_cursor
3762 |
3763 | HISTORY
3764 | Created 19-Jun-06 Shrikant Nene
3765 |
3766 +========================================================================+*/
3767 PROCEDURE Revision_LoV
3768 ( x_revision_cursor OUT NOCOPY t_genref
3769 , p_org_id IN NUMBER
3770 , p_inventory_item_id IN NUMBER
3771 , p_revision IN VARCHAR2
3772 )
3773 IS
3774 BEGIN
3775
3776 OPEN x_revision_cursor FOR
3777 SELECT revision
3778 FROM mtl_item_revisions
3779 WHERE organization_id = p_org_id
3780 AND inventory_item_id = p_inventory_item_id
3781 AND implementation_date IS NOT NULL
3782 AND revision like p_revision
3783 ORDER BY revision;
3784
3785 END Revision_LoV;
3786
3787
3788
3789 /* Bug#5663458 Begin
3790 * Created the following procedure. This procedure is to get the revisions for a particualr
3791 * material line. Used in return ingredient and product pages
3792 */
3793 PROCEDURE Revision_Txn_Lov
3794 ( x_revision_cursor OUT NOCOPY t_genref
3795 , p_org_id IN NUMBER
3796 , p_batch_id IN NUMBER
3797 , p_material_detail_id IN NUMBER
3798 , p_revision IN VARCHAR2
3799 , p_line_type IN NUMBER
3800 )
3801 IS
3802 BEGIN
3803 OPEN x_revision_cursor FOR
3804 SELECT DISTINCT revision
3805 FROM mtl_material_transactions
3806 WHERE organization_id = p_org_id
3807 AND transaction_source_id = p_batch_id
3808 AND trx_source_line_id = p_material_detail_id
3809 AND transaction_source_type_id = gme_common_pvt.g_txn_source_type
3810 AND revision LIKE (p_revision)
3811 GROUP BY revision
3812 HAVING (p_line_type = -1 AND SUM(transaction_quantity) < 0) OR
3813 (p_line_type IN (1,2) AND SUM(transaction_quantity) > 0)--rework
3814 ORDER BY revision;
3815
3816 END Revision_Txn_Lov;
3817
3818 /*
3819 * Created the following procedure. This procedure is to get the revisions from
3820 * DLR of material line.
3821 */
3822 PROCEDURE Revision_Rsrv_Lov
3823 ( x_revision_cursor OUT NOCOPY t_genref
3824 , p_org_id IN NUMBER
3825 , p_batch_id IN NUMBER
3826 , p_material_detail_id IN NUMBER
3827 , p_revision IN VARCHAR2
3828 )
3829 IS
3830 BEGIN
3831 OPEN x_revision_cursor FOR
3832 SELECT DISTINCT revision
3833 FROM mtl_reservations
3834 WHERE organization_id = p_org_id
3835 AND demand_source_header_id = p_batch_id
3836 AND demand_source_line_id = p_material_detail_id
3837 AND demand_source_type_id = 5
3838 AND revision LIKE (p_revision)
3839 ORDER BY revision;
3840 END Revision_Rsrv_Lov;
3841
3842 /*
3843 * Created the following procedure. This procedure is to get the revisions from
3844 * pending product lots of material line.
3845 */
3846 PROCEDURE Revision_PndLot_Lov
3847 ( x_revision_cursor OUT NOCOPY t_genref
3848 , p_batch_id IN NUMBER
3849 , p_material_detail_id IN NUMBER
3850 , p_revision IN VARCHAR2
3851 )
3852 IS
3853 BEGIN
3854 OPEN x_revision_cursor FOR
3855 SELECT DISTINCT revision
3856 FROM gme_pending_product_lots
3857 WHERE batch_id = p_batch_id
3858 AND material_detail_id = p_material_detail_id
3859 AND revision LIKE (p_revision)
3860 ORDER BY revision;
3861 END Revision_PndLot_Lov;
3862 /* Bug#5663458 End */
3863
3864 /*###############################################################
3865 # DESCRIPTION
3866 # Bug 4962372 Resource Instance LOV used in the Start resource transaction
3867 ###############################################################*/
3868 PROCEDURE Resource_Instance_MsT_LoV
3869 ( x_resource_cursor OUT NOCOPY t_genref
3870 , p_organization_id IN NUMBER
3871 , p_resource IN VARCHAR2
3872 , p_instance IN VARCHAR2 ) IS
3873 BEGIN
3874 OPEN x_resource_cursor FOR
3875 SELECT i.instance_number, i.instance_id
3876 FROM gmp_resource_instances i, cr_rsrc_dtl r
3877 WHERE r.resource_id = i.resource_id
3878 AND r.resources = p_resource
3879 AND r.organization_id = p_organization_id
3880 AND i.instance_number LIKE (p_instance);
3881 EXCEPTION
3882 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
3883 THEN
3884 NULL;
3885 END Resource_Instance_Mst_LoV;
3886
3887 /*###############################################################
3888 # DESCRIPTION
3889 # Bug 4962372 Resource Instance LOV used in the End resource transaction
3890 ###############################################################*/
3891 PROCEDURE Resource_Instance_Txn_LoV
3892 ( x_resource_cursor OUT NOCOPY t_genref
3893 , p_batch_id IN NUMBER
3894 , p_batchstep_resource_id IN NUMBER
3895 , p_instance IN VARCHAR2) IS
3896 BEGIN
3897 OPEN x_resource_cursor FOR
3898 SELECT i.instance_number, i.instance_id
3899 FROM gmp_resource_instances i, gme_resource_txns_gtmp t
3900 WHERE t.doc_id = p_batch_id
3901 AND t.line_id = p_batchstep_resource_id
3902 AND t.start_date = t.end_date
3903 AND action_code NOT IN ('REVS', 'REVL')
3904 AND t.resource_usage = 0
3905 AND t.completed_ind = 1
3906 AND t.delete_mark = 0
3907 AND t.instance_id = i.instance_id
3908 AND i.instance_number LIKE (p_instance);
3909 End Resource_Instance_Txn_LoV;
3910
3911 /*###############################################################
3912 # DESCRIPTION
3913 # This procedure is for pending steps
3914 ###############################################################*/
3915 PROCEDURE step_pending_lov
3916 ( x_step_cursor OUT NOCOPY t_genref
3917 , p_batch_id IN NUMBER
3918 , p_step_no IN VARCHAR2
3919 , p_date_format IN VARCHAR2) IS
3920 BEGIN
3921 OPEN x_step_cursor FOR
3922 SELECT gbs.batchstep_no, glk.meaning,gbs.batchstep_id, gmo.oprn_no, gmo.oprn_vers, gbs.plan_step_qty,
3923 TO_NUMBER(NULL) act_step_qty, gbs.step_qty_um,
3924 TO_CHAR(gbs.plan_start_date, p_date_format||gme_mobile_txn.HOUR_MIN_SEC_FORMAT_STRING),
3925 ' ' act_start_date,
3926 TO_CHAR(gbs.plan_start_date, p_date_format||gme_mobile_txn.HOUR_MIN_SEC_FORMAT_STRING) plan_comlt_date
3927 FROM gme_batch_header gbh, gme_batch_steps gbs, gmd_operations gmo, gem_lookups glk
3928 WHERE gbh.batch_id = p_batch_id
3929 AND gbh.batch_type = 0
3930 AND gbh.batch_id = gbs.batch_id
3931 AND gbs.step_status = 1
3932 AND glk.lookup_type = 'GME_STEP_STATUS'
3933 AND glk.lookup_code = TO_CHAR(gbs.step_status)
3934 AND TO_CHAR(gbs.batchstep_no) LIKE LTRIM(RTRIM(p_step_no||'%'))
3935 AND gbs.oprn_id = gmo.oprn_id
3936 ORDER BY 1;
3937 EXCEPTION
3938 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3939 NULL;
3940 END step_pending_lov;
3941
3942 /*###############################################################
3943 # DESCRIPTION
3944 # This procedure is for pending/wip steps
3945 ###############################################################*/
3946 PROCEDURE step_pending_wip_lov
3947 ( x_step_cursor OUT NOCOPY t_genref
3948 , p_batch_id IN NUMBER
3949 , p_step_no IN VARCHAR2
3950 , p_date_format IN VARCHAR2) IS
3951 BEGIN
3952 OPEN x_step_cursor FOR
3953 SELECT gbs.batchstep_no, glk.meaning,gbs.batchstep_id, gmo.oprn_no, gmo.oprn_vers, gbs.plan_step_qty,
3954 TO_NUMBER(NULL) act_step_qty, gbs.step_qty_um,
3955 TO_CHAR(gbs.plan_start_date, p_date_format||gme_mobile_txn.HOUR_MIN_SEC_FORMAT_STRING),
3956 DECODE(gbs.actual_start_date,NULL,' ',TO_CHAR(gbs.actual_start_date, p_date_format||gme_mobile_txn.HOUR_MIN_SEC_FORMAT_STRING)) act_start_date,
3957 TO_CHAR(gbs.plan_start_date, p_date_format||gme_mobile_txn.HOUR_MIN_SEC_FORMAT_STRING) plan_comlt_date
3958 FROM gme_batch_header gbh, gme_batch_steps gbs, gmd_operations gmo, gem_lookups glk
3959 WHERE gbh.batch_id = p_batch_id
3960 AND gbh.batch_type = 0
3961 AND gbh.batch_id = gbs.batch_id
3962 AND gbs.step_status IN (1,2)
3963 AND glk.lookup_type = 'GME_STEP_STATUS'
3964 AND glk.lookup_code = TO_CHAR(gbs.step_status)
3965 AND TO_CHAR(gbs.batchstep_no) LIKE LTRIM(RTRIM(p_step_no||'%'))
3966 AND gbs.oprn_id = gmo.oprn_id
3967 ORDER BY 1;
3968 EXCEPTION
3969 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3970 NULL;
3971 END step_pending_wip_lov;
3972
3973 /*###############################################################
3974 # DESCRIPTION
3975 # Bug 4962372 Resource LOV used in the Start resource transaction
3976 ###############################################################*/
3977 PROCEDURE Resource_Mst_LoV
3978 ( x_resource_cursor OUT NOCOPY t_genref
3979 , p_batch_id IN NUMBER
3980 , p_step_no IN VARCHAR2
3981 , p_activity_id IN NUMBER
3982 , p_resource IN VARCHAR2
3983 , p_date_format IN VARCHAR2) IS
3984 BEGIN
3985 OPEN x_resource_cursor FOR
3986 SELECT
3987 r.resources
3988 , r.batchstep_resource_id
3989 , DECODE(i.instance_number, null, ' ')
3990 , NVL (i.instance_id,-1)
3991 , TO_CHAR(r.actual_start_date,p_date_format)
3992 , TO_CHAR(r.actual_cmplt_date,p_date_format)
3993 , TO_CHAR(DECODE(s.step_status, 2, r.plan_start_date, 3, r.actual_start_date), p_date_format)
3994 , u.user_name
3995 , ROUND(DECODE(s.step_status, 2, r.plan_rsrc_qty, 3, r.actual_rsrc_qty),2)
3996 , r.resource_qty_um
3997 , ROUND(DECODE(s.step_status, 2, r.plan_rsrc_usage, 3, r.actual_rsrc_usage),2)
3998 , r.usage_um
3999 FROM gme_batch_header h,
4000 gme_batch_steps s,
4001 gme_batch_step_activities a,
4002 gme_batch_step_resources r,
4003 gmp_resource_instances i,
4004 fnd_user u
4005 WHERE h.batch_id = p_batch_id
4006 AND h.batch_id = s.batch_id
4007 AND s.batchstep_no = p_step_no
4008 AND s.batchstep_id = a.batchstep_id
4009 AND h.batch_id = a.batch_id
4010 AND r.batch_id = h.batch_id
4011 AND r.batchstep_id = s.batchstep_id
4012 AND r.batchstep_activity_id = a.batchstep_activity_id
4013 AND r.batchstep_activity_id = p_activity_id
4014 AND r.resources LIKE LTRIM(RTRIM(p_resource||'%'))
4015 AND r.batchstep_resource_id = i.resource_id(+)
4016 AND u.user_id = r.last_updated_by
4017 ORDER BY r.resources;
4018
4019 EXCEPTION
4020 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
4021 THEN
4022 NULL;
4023 END Resource_MsT_LoV;
4024
4025 /*###############################################################
4026 # DESCRIPTION
4027 # Bug 4962372 Resource LOV used in the End resource transaction
4028 ###############################################################*/
4029 PROCEDURE Resource_Txn_LoV
4030 ( x_resource_cursor OUT NOCOPY t_genref
4031 , p_batch_id IN NUMBER
4032 , p_step_no IN VARCHAR2
4033 , p_activity_id IN NUMBER
4034 , p_resource IN VARCHAR2
4035 , p_date_format IN VARCHAR2) IS
4036 BEGIN
4037
4038 OPEN x_resource_cursor FOR
4039 SELECT
4040 r.resources
4041 , r.batchstep_resource_id
4042 , DECODE(t.instance_id, NULL, ' ', i.instance_number)
4043 , NVL (t.instance_id,-1)
4044 , TO_CHAR(r.actual_start_date,p_date_format)
4045 , TO_CHAR(r.actual_cmplt_date,p_date_format)
4046 , TO_CHAR(t.start_date, p_date_format)
4047 , u.user_name
4048 , ROUND(DECODE(s.step_status, 2, r.plan_rsrc_qty, 3, r.actual_rsrc_qty),2)
4049 , r.resource_qty_um
4050 , ROUND(DECODE(s.step_status, 2, r.plan_rsrc_usage, 3, r.actual_rsrc_usage),2)
4051 , r.usage_um
4052 FROM gme_batch_steps s,
4053 gme_batch_step_activities a,
4054 gme_batch_step_resources r,
4055 gmp_resource_instances i,
4056 gme_resource_txns_gtmp t,
4057 gme_resource_txns rt,
4058 fnd_user u
4059 WHERE
4060 t.doc_id = p_batch_id
4061 AND t.line_id = r.batchstep_resource_id
4062 AND t.start_date = t.end_date
4063 AND t.resource_usage = 0
4064 AND t.completed_ind = 1
4065 AND t.action_code NOT IN ('REVS', 'REVL')
4066 AND t.delete_mark = 0
4067 AND t.instance_id = i.instance_id(+)
4068 AND s.batchstep_no = p_step_no
4069 AND s.batchstep_id = a.batchstep_id
4070 AND a.batch_id = t.doc_id
4071 AND r.batch_id = a.batch_id
4072 AND r.batchstep_id = s.batchstep_id
4073 AND r.batchstep_activity_id = a.batchstep_activity_id
4074 AND r.batchstep_activity_id = p_activity_id
4075 AND r.resources LIKE LTRIM(RTRIM(p_resource||'%'))
4076 AND rt.poc_trans_id = t.poc_trans_id
4077 AND u.user_id = rt.last_updated_by
4078 ORDER BY r.resources;
4079
4080 EXCEPTION
4081 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
4082 THEN
4083 NULL;
4084 END Resource_Txn_LoV;
4085
4086
4087 END GME_MOBILE_LOVS;