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