[Home] [Help]
PACKAGE BODY: APPS.GME_MATERIAL_DETAILS_DBL
Source
1 PACKAGE BODY gme_material_details_dbl AS
2 /* $Header: GMEVGMDB.pls 120.3 2005/10/04 14:50:58 jsrivast noship $ */
3
4 /* Global Variables */
5 g_table_name VARCHAR2 (80) DEFAULT 'GME_MATERIAL_DETAILS';
6 g_pkg_name CONSTANT VARCHAR2 (30) := 'GME_MATERIAL_DETAILS_DBL';
7 g_debug VARCHAR2 (5) := fnd_profile.VALUE ('AFLOG_LEVEL');
8
9 /* ===========================================================================
10 | Copyright (c) 2001 Oracle Corporation |
11 | TVP, Reading, England |
12 | All rights reserved |
13 ===========================================================================
14 | FILENAME |
15 | GMEVGMDB.pls |
16 | |
17 | DESCRIPTION |
18 | |
19 | Body of package gme_material_details_dbl |
20 | |
21 | NOTES |
22 | HISTORY |
23 | |
24 | 13-Feb-01 Created |
25 | |
26 | - create_row |
27 | - fetch_row |
28 | - update_row |
29 | - delete_row |
30 | - fetch_tab |
31 | |
32 | |
33 ===========================================================================
34 */
35
36 /* Api start of comments
37 +==========================================================================+
38 | FUNCTION NAME |
39 | insert_row |
40 | |
41 | TYPE |
42 | Private |
43 | |
44 | USAGE |
45 | insert_Row will insert a row in gme_material_details |
46 | |
47 | |
48 | DESCRIPTION |
49 | insert_Row will insert a row in gme_material_details |
50 | |
51 | |
52 | PARAMETERS |
53 | p_material_detail IN gme_material_details%ROWTYPE |
54 | x_material_detail IN OUT NOCOPY gme_material_details%ROWTYPE |
55 | RETURNS |
56 | BOOLEAN |
57 | |
58 | HISTORY |
59 | 12-FEB-2001 fabdi Created |
60 | 15-OCT-2001 Thomas Daniel |
61 | Added who columns to be passed back. |
62 | 30-AUG-02 Chandrashekar Tiruvidula Bug 2526710 |
63 | Added byproduct_type in insert/update/fetch |
64 +==========================================================================+
65 Api end of comments
66 */
67 FUNCTION insert_row (
68 p_material_detail IN gme_material_details%ROWTYPE
69 ,x_material_detail IN OUT NOCOPY gme_material_details%ROWTYPE)
70 RETURN BOOLEAN
71 IS
72 l_api_name CONSTANT VARCHAR2 (30) := 'INSERT_ROW';
73 BEGIN
74 x_material_detail := p_material_detail;
75
76 INSERT INTO gme_material_details
77 (material_detail_id, batch_id
78 ,formulaline_id
79 ,line_no,
80 --item_id,
81 line_type
82 ,plan_qty,
83 --item_um,
84 item_um2
85 ,actual_qty
86 ,release_type
87 ,scrap_factor
88 ,scale_type
89 ,phantom_type
90 ,cost_alloc
91 ,alloc_ind, COST
92 ,text_code
93 ,phantom_id
94 ,created_by, creation_date
95 ,last_updated_by, last_update_date
96 ,attribute1
97 ,attribute2
98 ,attribute3
99 ,attribute4
100 ,attribute5
101 ,attribute6
102 ,attribute7
103 ,attribute8
104 ,attribute9
105 ,attribute10
106 ,attribute11
107 ,attribute12
108 ,attribute13
109 ,attribute14
110 ,attribute15
111 ,attribute16
112 ,attribute17
113 ,attribute18
114 ,attribute19
115 ,attribute20
116 ,attribute21
117 ,attribute22
118 ,attribute23
119 ,attribute24
120 ,attribute25
121 ,attribute26
122 ,attribute27
123 ,attribute28
124 ,attribute29
125 ,attribute30
126 ,attribute_category
127 ,last_update_login
128 ,scale_rounding_variance
129 ,scale_multiple
130 ,rounding_direction
131 ,contribute_yield_ind
132 ,contribute_step_qty_ind
133 ,wip_plan_qty
134 ,original_qty
135 ,by_product_type
136 ,organization_id
137 ,inventory_item_id
138 ,subinventory
139 ,locator_id, revision
140 ,backordered_qty
141 ,original_primary_qty
142 ,material_requirement_date
143 ,phantom_line_id
144 ,move_order_line_id
145 ,dtl_um
146 ,dispense_ind)
147 VALUES (gem5_line_id_s.NEXTVAL, x_material_detail.batch_id
148 ,x_material_detail.formulaline_id
149 ,x_material_detail.line_no,
150 --x_material_detail.item_id,
151 x_material_detail.line_type
152 ,x_material_detail.plan_qty,
153 --x_material_detail.item_um,
154 x_material_detail.item_um2
155 ,x_material_detail.actual_qty
156 ,x_material_detail.release_type
157 ,x_material_detail.scrap_factor
158 ,x_material_detail.scale_type
159 ,x_material_detail.phantom_type
160 ,x_material_detail.cost_alloc
161 ,x_material_detail.alloc_ind, x_material_detail.COST
162 ,x_material_detail.text_code
163 ,x_material_detail.phantom_id
164 ,gme_common_pvt.g_user_ident, gme_common_pvt.g_timestamp
165 ,gme_common_pvt.g_user_ident, gme_common_pvt.g_timestamp
166 ,x_material_detail.attribute1
167 ,x_material_detail.attribute2
168 ,x_material_detail.attribute3
169 ,x_material_detail.attribute4
170 ,x_material_detail.attribute5
171 ,x_material_detail.attribute6
172 ,x_material_detail.attribute7
173 ,x_material_detail.attribute8
174 ,x_material_detail.attribute9
175 ,x_material_detail.attribute10
176 ,x_material_detail.attribute11
177 ,x_material_detail.attribute12
178 ,x_material_detail.attribute13
179 ,x_material_detail.attribute14
180 ,x_material_detail.attribute15
181 ,x_material_detail.attribute16
182 ,x_material_detail.attribute17
183 ,x_material_detail.attribute18
184 ,x_material_detail.attribute19
185 ,x_material_detail.attribute20
186 ,x_material_detail.attribute21
187 ,x_material_detail.attribute22
188 ,x_material_detail.attribute23
189 ,x_material_detail.attribute24
190 ,x_material_detail.attribute25
191 ,x_material_detail.attribute26
192 ,x_material_detail.attribute27
193 ,x_material_detail.attribute28
194 ,x_material_detail.attribute29
195 ,x_material_detail.attribute30
196 ,x_material_detail.attribute_category
197 ,x_material_detail.last_update_login
198 ,x_material_detail.scale_rounding_variance
199 ,x_material_detail.scale_multiple
200 ,x_material_detail.rounding_direction
201 ,x_material_detail.contribute_yield_ind
202 ,x_material_detail.contribute_step_qty_ind
203 ,x_material_detail.wip_plan_qty
204 ,x_material_detail.original_qty
205 ,x_material_detail.by_product_type
206 ,x_material_detail.organization_id
207 ,x_material_detail.inventory_item_id
208 ,x_material_detail.subinventory
209 ,x_material_detail.locator_id, x_material_detail.revision
210 ,x_material_detail.backordered_qty
211 ,x_material_detail.original_primary_qty
212 ,x_material_detail.material_requirement_date
213 ,x_material_detail.phantom_line_id
214 ,x_material_detail.move_order_line_id
215 ,x_material_detail.dtl_um
216 ,x_material_detail.dispense_ind)
217 RETURNING material_detail_id
218 INTO x_material_detail.material_detail_id;
219
220 IF SQL%ROWCOUNT = 1 THEN
221 x_material_detail.created_by := gme_common_pvt.g_user_ident;
222 x_material_detail.creation_date := gme_common_pvt.g_timestamp;
223 x_material_detail.last_updated_by := gme_common_pvt.g_user_ident;
224 x_material_detail.last_update_date := gme_common_pvt.g_timestamp;
225 RETURN TRUE;
226 ELSE
227 RETURN FALSE;
228 END IF;
229
230 RETURN TRUE;
231 EXCEPTION
232 WHEN OTHERS THEN
233 IF g_debug <= gme_debug.g_log_unexpected THEN
234 gme_debug.put_line ( 'When others exception in '
235 || g_pkg_name
236 || '.'
237 || l_api_name
238 || ' Error is '
239 || SQLERRM);
240 END IF;
241
242 x_material_detail.material_detail_id := NULL;
243 gme_common_pvt.log_message ('GME_UNEXPECTED_ERROR', 'ERROR', SQLERRM);
244 RETURN FALSE;
245 END insert_row;
246
247 /* Api start of comments
248 +==========================================================================+
249 | FUNCTION NAME |
250 | fetch_row |
251 | |
252 | TYPE |
253 | Private |
254 | |
255 | USAGE |
256 | fetch_Row will fetch a row in gme_material_details |
257 | |
258 | |
259 | DESCRIPTION |
260 | fetch_row will fetch a row in gme_material_details |
261 | |
262 | |
263 | PARAMETERS |
264 | p_material_detail IN gme_material_details%ROWTYPE |
265 | x_material_detail IN OUT NOCOPY gme_material_details%ROWTYPE |
266 | RETURNS |
267 | BOOLEAN |
268 | |
269 | HISTORY |
270 | 12-FEB-2001 fabdi Created |
271 | |
272 +==========================================================================+
273 Api end of comments
274 */
275 FUNCTION fetch_row (
276 p_material_detail IN gme_material_details%ROWTYPE
277 ,x_material_detail IN OUT NOCOPY gme_material_details%ROWTYPE)
278 RETURN BOOLEAN
279 IS
280 l_api_name CONSTANT VARCHAR2 (30) := 'FETCH_ROW';
281 l_gme_material_details gme_material_details%ROWTYPE;
282 l_material_detail_id NUMBER;
283 l_batch_id NUMBER;
284 l_line_no NUMBER;
285 l_line_type NUMBER;
286 BEGIN
287 l_material_detail_id := p_material_detail.material_detail_id;
288 l_batch_id := p_material_detail.batch_id;
289 l_line_no := p_material_detail.line_no;
290 l_line_type := p_material_detail.line_type;
291
292 IF (l_material_detail_id IS NOT NULL) THEN
293 SELECT batch_id
294 ,material_detail_id
295 ,formulaline_id
296 ,line_no
297 ,
298 --item_id,
299 line_type
300 ,plan_qty
301 ,
302 --item_um,
303 item_um2
304 ,actual_qty
305 ,release_type
306 ,scrap_factor
307 ,scale_type
308 ,phantom_type
309 ,cost_alloc
310 ,alloc_ind
311 ,COST
312 ,text_code
313 ,phantom_id
314 ,created_by
315 ,creation_date
316 ,last_updated_by
317 ,last_update_date
318 ,attribute1
319 ,attribute2
320 ,attribute3
321 ,attribute4
322 ,attribute5
323 ,attribute6
324 ,attribute7
325 ,attribute8
326 ,attribute9
327 ,attribute10
328 ,attribute11
329 ,attribute12
330 ,attribute13
331 ,attribute14
332 ,attribute15
333 ,attribute16
334 ,attribute17
335 ,attribute18
336 ,attribute19
337 ,attribute20
338 ,attribute21
339 ,attribute22
340 ,attribute23
341 ,attribute24
342 ,attribute25
343 ,attribute26
344 ,attribute27
345 ,attribute28
346 ,attribute29
347 ,attribute30
348 ,attribute_category
349 ,last_update_login
350 ,scale_rounding_variance
351 ,scale_multiple
352 ,rounding_direction
353 ,contribute_yield_ind
354 ,contribute_step_qty_ind
355 ,wip_plan_qty
356 ,original_qty
357 ,by_product_type
358 ,organization_id
359 ,inventory_item_id
360 ,subinventory
361 ,locator_id
362 ,revision
363 ,backordered_qty
364 ,original_primary_qty
365 ,material_requirement_date
366 ,phantom_line_id
367 ,move_order_line_id
368 ,dtl_um
369 ,dispense_ind
370 INTO l_gme_material_details.batch_id
371 ,l_gme_material_details.material_detail_id
372 ,l_gme_material_details.formulaline_id
373 ,l_gme_material_details.line_no
374 ,
375 --l_gme_material_details.item_id,
376 l_gme_material_details.line_type
377 ,l_gme_material_details.plan_qty
378 ,
379 --l_gme_material_details.item_um,
380 l_gme_material_details.item_um2
381 ,l_gme_material_details.actual_qty
382 ,l_gme_material_details.release_type
383 ,l_gme_material_details.scrap_factor
384 ,l_gme_material_details.scale_type
385 ,l_gme_material_details.phantom_type
386 ,l_gme_material_details.cost_alloc
387 ,l_gme_material_details.alloc_ind
388 ,l_gme_material_details.COST
389 ,l_gme_material_details.text_code
390 ,l_gme_material_details.phantom_id
391 ,l_gme_material_details.created_by
392 ,l_gme_material_details.creation_date
393 ,l_gme_material_details.last_updated_by
394 ,l_gme_material_details.last_update_date
395 ,l_gme_material_details.attribute1
396 ,l_gme_material_details.attribute2
397 ,l_gme_material_details.attribute3
398 ,l_gme_material_details.attribute4
399 ,l_gme_material_details.attribute5
400 ,l_gme_material_details.attribute6
401 ,l_gme_material_details.attribute7
402 ,l_gme_material_details.attribute8
403 ,l_gme_material_details.attribute9
404 ,l_gme_material_details.attribute10
405 ,l_gme_material_details.attribute11
406 ,l_gme_material_details.attribute12
407 ,l_gme_material_details.attribute13
408 ,l_gme_material_details.attribute14
409 ,l_gme_material_details.attribute15
410 ,l_gme_material_details.attribute16
411 ,l_gme_material_details.attribute17
412 ,l_gme_material_details.attribute18
413 ,l_gme_material_details.attribute19
414 ,l_gme_material_details.attribute20
415 ,l_gme_material_details.attribute21
416 ,l_gme_material_details.attribute22
417 ,l_gme_material_details.attribute23
418 ,l_gme_material_details.attribute24
419 ,l_gme_material_details.attribute25
420 ,l_gme_material_details.attribute26
421 ,l_gme_material_details.attribute27
422 ,l_gme_material_details.attribute28
423 ,l_gme_material_details.attribute29
424 ,l_gme_material_details.attribute30
425 ,l_gme_material_details.attribute_category
426 ,l_gme_material_details.last_update_login
427 ,l_gme_material_details.scale_rounding_variance
428 ,l_gme_material_details.scale_multiple
429 ,l_gme_material_details.rounding_direction
430 ,l_gme_material_details.contribute_yield_ind
431 ,l_gme_material_details.contribute_step_qty_ind
432 ,l_gme_material_details.wip_plan_qty
433 ,l_gme_material_details.original_qty
434 ,l_gme_material_details.by_product_type
435 ,l_gme_material_details.organization_id
436 ,l_gme_material_details.inventory_item_id
437 ,l_gme_material_details.subinventory
438 ,l_gme_material_details.locator_id
439 ,l_gme_material_details.revision
440 ,l_gme_material_details.backordered_qty
441 ,l_gme_material_details.original_primary_qty
442 ,l_gme_material_details.material_requirement_date
443 ,l_gme_material_details.phantom_line_id
444 ,l_gme_material_details.move_order_line_id
445 ,l_gme_material_details.dtl_um
446 ,l_gme_material_details.dispense_ind
447 FROM gme_material_details
448 WHERE material_detail_id = l_material_detail_id;
449 ELSIF (l_batch_id IS NOT NULL)
450 AND (l_line_no IS NOT NULL)
451 AND (l_line_type IS NOT NULL) THEN
452 SELECT batch_id
453 ,material_detail_id
454 ,formulaline_id
455 ,line_no
456 ,
457 --item_id,
458 line_type
459 ,plan_qty
460 ,
461 --item_um,
462 item_um2
463 ,actual_qty
464 ,release_type
465 ,scrap_factor
466 ,scale_type
467 ,phantom_type
468 ,cost_alloc
469 ,alloc_ind
470 ,COST
471 ,text_code
472 ,phantom_id
473 ,created_by
474 ,creation_date
475 ,last_updated_by
476 ,last_update_date
477 ,attribute1
478 ,attribute2
479 ,attribute3
480 ,attribute4
481 ,attribute5
482 ,attribute6
483 ,attribute7
484 ,attribute8
485 ,attribute9
486 ,attribute10
487 ,attribute11
488 ,attribute12
489 ,attribute13
490 ,attribute14
491 ,attribute15
492 ,attribute16
493 ,attribute17
494 ,attribute18
495 ,attribute19
496 ,attribute20
497 ,attribute21
498 ,attribute22
499 ,attribute23
500 ,attribute24
501 ,attribute25
502 ,attribute26
503 ,attribute27
504 ,attribute28
505 ,attribute29
506 ,attribute30
507 ,attribute_category
508 ,last_update_login
509 ,scale_rounding_variance
510 ,scale_multiple
511 ,rounding_direction
512 ,contribute_yield_ind
513 ,contribute_step_qty_ind
514 ,wip_plan_qty
515 ,original_qty
516 ,by_product_type
517 ,organization_id
518 ,inventory_item_id
519 ,subinventory
520 ,locator_id
521 ,revision
522 ,backordered_qty
523 ,original_primary_qty
524 ,material_requirement_date
525 ,phantom_line_id
526 ,move_order_line_id
527 ,dtl_um
528 ,dispense_ind
529 INTO l_gme_material_details.batch_id
530 ,l_gme_material_details.material_detail_id
531 ,l_gme_material_details.formulaline_id
532 ,l_gme_material_details.line_no
533 ,
534 --l_gme_material_details.item_id,
535 l_gme_material_details.line_type
536 ,l_gme_material_details.plan_qty
537 ,
538 --l_gme_material_details.item_um,
539 l_gme_material_details.item_um2
540 ,l_gme_material_details.actual_qty
541 ,l_gme_material_details.release_type
542 ,l_gme_material_details.scrap_factor
543 ,l_gme_material_details.scale_type
544 ,l_gme_material_details.phantom_type
545 ,l_gme_material_details.cost_alloc
546 ,l_gme_material_details.alloc_ind
547 ,l_gme_material_details.COST
548 ,l_gme_material_details.text_code
549 ,l_gme_material_details.phantom_id
550 ,l_gme_material_details.created_by
551 ,l_gme_material_details.creation_date
552 ,l_gme_material_details.last_updated_by
553 ,l_gme_material_details.last_update_date
554 ,l_gme_material_details.attribute1
555 ,l_gme_material_details.attribute2
556 ,l_gme_material_details.attribute3
557 ,l_gme_material_details.attribute4
558 ,l_gme_material_details.attribute5
559 ,l_gme_material_details.attribute6
560 ,l_gme_material_details.attribute7
561 ,l_gme_material_details.attribute8
562 ,l_gme_material_details.attribute9
563 ,l_gme_material_details.attribute10
564 ,l_gme_material_details.attribute11
565 ,l_gme_material_details.attribute12
566 ,l_gme_material_details.attribute13
567 ,l_gme_material_details.attribute14
568 ,l_gme_material_details.attribute15
569 ,l_gme_material_details.attribute16
570 ,l_gme_material_details.attribute17
571 ,l_gme_material_details.attribute18
572 ,l_gme_material_details.attribute19
573 ,l_gme_material_details.attribute20
574 ,l_gme_material_details.attribute21
575 ,l_gme_material_details.attribute22
576 ,l_gme_material_details.attribute23
577 ,l_gme_material_details.attribute24
578 ,l_gme_material_details.attribute25
579 ,l_gme_material_details.attribute26
580 ,l_gme_material_details.attribute27
581 ,l_gme_material_details.attribute28
582 ,l_gme_material_details.attribute29
583 ,l_gme_material_details.attribute30
584 ,l_gme_material_details.attribute_category
585 ,l_gme_material_details.last_update_login
586 ,l_gme_material_details.scale_rounding_variance
587 ,l_gme_material_details.scale_multiple
588 ,l_gme_material_details.rounding_direction
589 ,l_gme_material_details.contribute_yield_ind
590 ,l_gme_material_details.contribute_step_qty_ind
591 ,l_gme_material_details.wip_plan_qty
592 ,l_gme_material_details.original_qty
593 ,l_gme_material_details.by_product_type
594 ,l_gme_material_details.organization_id
595 ,l_gme_material_details.inventory_item_id
596 ,l_gme_material_details.subinventory
597 ,l_gme_material_details.locator_id
598 ,l_gme_material_details.revision
599 ,l_gme_material_details.backordered_qty
600 ,l_gme_material_details.original_primary_qty
601 ,l_gme_material_details.material_requirement_date
602 ,l_gme_material_details.phantom_line_id
603 ,l_gme_material_details.move_order_line_id
604 ,l_gme_material_details.dtl_um
605 ,l_gme_material_details.dispense_ind
606
607 FROM gme_material_details
608 WHERE batch_id = l_batch_id
609 AND line_no = l_line_no
610 AND line_type = l_line_type;
611 ELSE
612 gme_common_pvt.log_message ('GME_NO_KEYS'
613 ,'TABLE_NAME'
614 ,g_table_name);
615 RETURN FALSE;
616 END IF;
617
618 IF (SQL%FOUND) THEN
619 x_material_detail := l_gme_material_details;
620 RETURN TRUE;
621 ELSE
622 gme_common_pvt.log_message ('GME_NO_DATA_FOUND'
623 ,'TABLE_NAME'
624 ,g_table_name);
625 x_material_detail := l_gme_material_details;
626 RETURN FALSE;
627 END IF;
628 EXCEPTION
629 WHEN NO_DATA_FOUND THEN
630 gme_common_pvt.log_message ('GME_NO_DATA_FOUND'
631 ,'TABLE_NAME'
632 ,g_table_name);
633 RETURN FALSE;
634 WHEN OTHERS THEN
635 IF g_debug <= gme_debug.g_log_unexpected THEN
636 gme_debug.put_line ( 'When others exception in '
637 || g_pkg_name
638 || '.'
639 || l_api_name
640 || ' Error is '
641 || SQLERRM);
642 END IF;
643
644 gme_common_pvt.log_message ('GME_UNEXPECTED_ERROR', 'ERROR', SQLERRM);
645 RETURN FALSE;
646 END fetch_row;
647
648 /* Api start of comments
649 +==========================================================================+
650 | FUNCTION NAME |
651 | delete_row |
652 | |
653 | TYPE |
654 | Private |
655 | |
656 | USAGE |
657 | delete_Row will delete a row in gme_material_details |
658 | |
659 | |
660 | DESCRIPTION |
661 | delete_row will delete a row in gme_material_details |
662 | |
663 | |
664 | PARAMETERS |
665 | p_material_detail IN gme_material_details%ROWTYPE |
666 | RETURNS |
667 | BOOLEAN |
668 | |
669 | HISTORY |
670 | 12-FEB-2001 fabdi Created |
671 | 26-AUG-2002 Bharati Satpute Bug2404126 |
672 | Added Error message 'GME_RECORD_CHANGED' |
673 +==========================================================================+
674 Api end of comments
675 */
676 FUNCTION delete_row (p_material_detail IN gme_material_details%ROWTYPE)
677 RETURN BOOLEAN
678 IS
679 l_material_detail_id NUMBER;
680 l_batch_id NUMBER;
681 l_line_no NUMBER;
682 l_line_type NUMBER;
683 l_dummy NUMBER := 0;
684 locked_by_other_user EXCEPTION;
685 PRAGMA EXCEPTION_INIT (locked_by_other_user, -54);
686 l_api_name CONSTANT VARCHAR2 (30) := 'DELETE_ROW';
687 BEGIN
688 l_material_detail_id := p_material_detail.material_detail_id;
689 l_batch_id := p_material_detail.batch_id;
690 l_line_no := p_material_detail.line_no;
691 l_line_type := p_material_detail.line_type;
692
693 IF l_material_detail_id IS NOT NULL THEN
694 SELECT 1
695 INTO l_dummy
696 FROM gme_material_details
697 WHERE material_detail_id = l_material_detail_id
698 FOR UPDATE NOWAIT;
699
700 DELETE FROM gme_material_details
701 WHERE material_detail_id = l_material_detail_id;
702 ELSIF (l_batch_id IS NOT NULL)
703 AND (l_line_no IS NOT NULL)
704 AND (l_line_type IS NOT NULL) THEN
705 SELECT 1
706 INTO l_dummy
707 FROM gme_material_details
708 WHERE batch_id = l_batch_id
709 AND line_no = l_line_no
710 AND line_type = l_line_type
711 FOR UPDATE NOWAIT;
712
713 DELETE FROM gme_material_details
714 WHERE batch_id = l_batch_id
715 AND line_no = l_line_no
716 AND line_type = l_line_type;
717 ELSE
718 gme_common_pvt.log_message ('GME_NO_KEYS'
719 ,'TABLE_NAME'
720 ,g_table_name);
721 RETURN FALSE;
722 END IF;
723
724 IF (SQL%FOUND) THEN
725 RETURN TRUE;
726 ELSE
727 IF l_dummy = 0 THEN
728 gme_common_pvt.log_message ('GME_NO_DATA_FOUND'
729 ,'TABLE_NAME'
730 ,g_table_name);
731 ELSE
732 gme_common_pvt.log_message ('GME_RECORD_CHANGED'
733 ,'TABLE_NAME'
734 ,g_table_name);
735 END IF;
736
737 RETURN FALSE;
738 END IF;
739
740 RETURN TRUE;
741 EXCEPTION
742 WHEN NO_DATA_FOUND THEN
743 IF l_dummy = 0 THEN
744 gme_common_pvt.log_message ('GME_NO_DATA_FOUND'
745 ,'TABLE_NAME'
746 ,g_table_name);
747 ELSE
748 gme_common_pvt.log_message ('GME_RECORD_CHANGED'
749 ,'TABLE_NAME'
750 ,g_table_name);
751 END IF;
752
753 RETURN FALSE;
754 WHEN locked_by_other_user THEN
755 gme_common_pvt.log_message ('GME_RECORD_LOCKED'
756 ,'TABLE_NAME'
757 ,g_table_name
758 ,'RECORD'
759 ,'Line No'
760 ,'KEY'
761 ,TO_CHAR (p_material_detail.line_no) );
762 RETURN FALSE;
763 WHEN OTHERS THEN
764 IF g_debug <= gme_debug.g_log_unexpected THEN
765 gme_debug.put_line ( 'When others exception in '
766 || g_pkg_name
767 || '.'
768 || l_api_name
769 || ' Error is '
770 || SQLERRM);
771 END IF;
772
773 gme_common_pvt.log_message ('GME_UNEXPECTED_ERROR', 'ERROR', SQLERRM);
774 RETURN FALSE;
775 END delete_row;
776
777 /* Api start of comments
778 +==========================================================================+
779 | FUNCTION NAME |
780 | update_row |
781 | |
782 | TYPE |
783 | Private |
784 | |
785 | USAGE |
786 | update_row will update a row in gme_material_details |
787 | |
788 | |
789 | DESCRIPTION |
790 | update_row will update a row in gme_material_details |
791 | |
792 | |
793 | PARAMETERS |
794 | p_material_detail IN gme_material_details%ROWTYPE |
795 | RETURNS |
796 | BOOLEAN | |
797 | HISTORY |
798 | 12-FEB-2001 fabdi Created
799 | 29-MAR-2002 bsatpute Added error message |
800 | 26-AUG-2002 Bharati Satpute Bug2404126 |
801 | Added Error message 'GME_RECORD_CHANGED'
802 | |
803 +==========================================================================+
804 Api end of comments
805 */
806 FUNCTION update_row (p_material_detail IN gme_material_details%ROWTYPE)
807 RETURN BOOLEAN
808 IS
809 l_material_detail_id NUMBER;
810 l_batch_id NUMBER;
811 l_line_no NUMBER;
812 l_line_type NUMBER;
813 l_dummy NUMBER := 0;
814 locked_by_other_user EXCEPTION;
815 PRAGMA EXCEPTION_INIT (locked_by_other_user, -54);
816 l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_ROW';
817 BEGIN
818 l_material_detail_id := p_material_detail.material_detail_id;
819 l_batch_id := p_material_detail.batch_id;
820 l_line_no := p_material_detail.line_no;
821 l_line_type := p_material_detail.line_type;
822
823 IF l_material_detail_id IS NOT NULL THEN
824 SELECT 1
825 INTO l_dummy
826 FROM gme_material_details
827 WHERE material_detail_id = l_material_detail_id
828 FOR UPDATE NOWAIT;
829
830 UPDATE gme_material_details
831 SET formulaline_id = p_material_detail.formulaline_id
832 ,line_no = p_material_detail.line_no
833 ,
834 --item_id = p_material_detail.item_id,
835 line_type = p_material_detail.line_type
836 ,plan_qty = p_material_detail.plan_qty
837 ,
838 --item_um = p_material_detail.item_um,
839 item_um2 = p_material_detail.item_um2
840 ,actual_qty = nvl(p_material_detail.actual_qty,0)
841 ,release_type = p_material_detail.release_type
842 ,scrap_factor = p_material_detail.scrap_factor
843 ,scale_type = p_material_detail.scale_type
844 ,phantom_type = p_material_detail.phantom_type
845 ,cost_alloc = p_material_detail.cost_alloc
846 ,alloc_ind = p_material_detail.alloc_ind
847 ,COST = p_material_detail.COST
848 ,text_code = p_material_detail.text_code
849 ,phantom_id = p_material_detail.phantom_id
850 ,last_updated_by = gme_common_pvt.g_user_ident
851 ,last_update_date = gme_common_pvt.g_timestamp
852 ,attribute1 = p_material_detail.attribute1
853 ,attribute2 = p_material_detail.attribute2
854 ,attribute3 = p_material_detail.attribute3
855 ,attribute4 = p_material_detail.attribute4
856 ,attribute5 = p_material_detail.attribute5
857 ,attribute6 = p_material_detail.attribute6
858 ,attribute7 = p_material_detail.attribute7
859 ,attribute8 = p_material_detail.attribute8
860 ,attribute9 = p_material_detail.attribute9
861 ,attribute10 = p_material_detail.attribute10
862 ,attribute11 = p_material_detail.attribute11
863 ,attribute12 = p_material_detail.attribute12
864 ,attribute13 = p_material_detail.attribute13
865 ,attribute14 = p_material_detail.attribute14
866 ,attribute15 = p_material_detail.attribute15
867 ,attribute16 = p_material_detail.attribute16
868 ,attribute17 = p_material_detail.attribute17
869 ,attribute18 = p_material_detail.attribute18
870 ,attribute19 = p_material_detail.attribute19
871 ,attribute20 = p_material_detail.attribute20
872 ,attribute21 = p_material_detail.attribute21
873 ,attribute22 = p_material_detail.attribute22
874 ,attribute23 = p_material_detail.attribute23
875 ,attribute24 = p_material_detail.attribute24
876 ,attribute25 = p_material_detail.attribute25
877 ,attribute26 = p_material_detail.attribute26
878 ,attribute27 = p_material_detail.attribute27
879 ,attribute28 = p_material_detail.attribute28
880 ,attribute29 = p_material_detail.attribute29
881 ,attribute30 = p_material_detail.attribute30
882 ,attribute_category = p_material_detail.attribute_category
883 ,last_update_login = p_material_detail.last_update_login
884 ,scale_rounding_variance =
885 p_material_detail.scale_rounding_variance
886 ,scale_multiple = p_material_detail.scale_multiple
887 ,rounding_direction = p_material_detail.rounding_direction
888 ,contribute_yield_ind = p_material_detail.contribute_yield_ind
889 ,contribute_step_qty_ind =
890 p_material_detail.contribute_step_qty_ind
891 ,wip_plan_qty = p_material_detail.wip_plan_qty
892 ,original_qty = p_material_detail.original_qty
893 ,by_product_type = p_material_detail.by_product_type
894 ,organization_id = p_material_detail.organization_id
895 ,inventory_item_id = p_material_detail.inventory_item_id
896 ,subinventory = p_material_detail.subinventory
897 ,locator_id = p_material_detail.locator_id
898 ,revision = p_material_detail.revision
899 ,backordered_qty = p_material_detail.backordered_qty
900 ,material_requirement_date =
901 p_material_detail.material_requirement_date
902 ,phantom_line_id = p_material_detail.phantom_line_id
903 ,move_order_line_id = p_material_detail.move_order_line_id
904 ,dtl_um = p_material_detail.dtl_um
905 ,dispense_ind = p_material_detail.dispense_ind
906
907 WHERE material_detail_id = p_material_detail.material_detail_id
908 AND last_update_date = p_material_detail.last_update_date;
909 ELSIF (l_batch_id IS NOT NULL)
910 AND (l_line_no IS NOT NULL)
911 AND (l_line_type IS NOT NULL) THEN
912 SELECT 1
913 INTO l_dummy
914 FROM gme_material_details
915 WHERE batch_id = l_batch_id
916 AND line_no = l_line_no
917 AND line_type = l_line_type
918 FOR UPDATE NOWAIT;
919
920 UPDATE gme_material_details
921 SET formulaline_id = p_material_detail.formulaline_id
922 ,line_no = p_material_detail.line_no
923 ,line_type = p_material_detail.line_type
924 ,plan_qty = p_material_detail.plan_qty
925 ,item_um2 = p_material_detail.item_um2
926 ,actual_qty = p_material_detail.actual_qty
927 ,release_type = p_material_detail.release_type
928 ,scrap_factor = p_material_detail.scrap_factor
929 ,scale_type = p_material_detail.scale_type
930 ,phantom_type = p_material_detail.phantom_type
931 ,cost_alloc = p_material_detail.cost_alloc
932 ,alloc_ind = p_material_detail.alloc_ind
933 ,COST = p_material_detail.COST
934 ,text_code = p_material_detail.text_code
935 ,phantom_id = p_material_detail.phantom_id
936 ,last_updated_by = gme_common_pvt.g_user_ident
937 ,last_update_date = gme_common_pvt.g_timestamp
938 ,attribute1 = p_material_detail.attribute1
939 ,attribute2 = p_material_detail.attribute2
940 ,attribute3 = p_material_detail.attribute3
941 ,attribute4 = p_material_detail.attribute4
942 ,attribute5 = p_material_detail.attribute5
943 ,attribute6 = p_material_detail.attribute6
944 ,attribute7 = p_material_detail.attribute7
945 ,attribute8 = p_material_detail.attribute8
946 ,attribute9 = p_material_detail.attribute9
947 ,attribute10 = p_material_detail.attribute10
948 ,attribute11 = p_material_detail.attribute11
949 ,attribute12 = p_material_detail.attribute12
950 ,attribute13 = p_material_detail.attribute13
951 ,attribute14 = p_material_detail.attribute14
952 ,attribute15 = p_material_detail.attribute15
953 ,attribute16 = p_material_detail.attribute16
954 ,attribute17 = p_material_detail.attribute17
955 ,attribute18 = p_material_detail.attribute18
956 ,attribute19 = p_material_detail.attribute19
957 ,attribute20 = p_material_detail.attribute20
958 ,attribute21 = p_material_detail.attribute21
959 ,attribute22 = p_material_detail.attribute22
960 ,attribute23 = p_material_detail.attribute23
961 ,attribute24 = p_material_detail.attribute24
962 ,attribute25 = p_material_detail.attribute25
963 ,attribute26 = p_material_detail.attribute26
964 ,attribute27 = p_material_detail.attribute27
965 ,attribute28 = p_material_detail.attribute28
966 ,attribute29 = p_material_detail.attribute29
967 ,attribute30 = p_material_detail.attribute30
968 ,attribute_category = p_material_detail.attribute_category
969 ,last_update_login = p_material_detail.last_update_login
970 ,scale_rounding_variance =
971 p_material_detail.scale_rounding_variance
972 ,scale_multiple = p_material_detail.scale_multiple
973 ,rounding_direction = p_material_detail.rounding_direction
974 ,contribute_yield_ind = p_material_detail.contribute_yield_ind
975 ,contribute_step_qty_ind =
976 p_material_detail.contribute_step_qty_ind
977 ,wip_plan_qty = p_material_detail.wip_plan_qty
978 ,by_product_type = p_material_detail.by_product_type
979 ,organization_id = p_material_detail.organization_id
980 ,inventory_item_id = p_material_detail.inventory_item_id
981 ,subinventory = p_material_detail.subinventory
982 ,locator_id = p_material_detail.locator_id
983 ,revision = p_material_detail.revision
984 ,backordered_qty = p_material_detail.backordered_qty
985 ,original_primary_qty = p_material_detail.original_primary_qty
986 ,material_requirement_date =
987 p_material_detail.material_requirement_date
988 ,phantom_line_id = p_material_detail.phantom_line_id
989 ,move_order_line_id = p_material_detail.move_order_line_id
990 ,dtl_um = p_material_detail.dtl_um
991 ,dispense_ind = p_material_detail.dispense_ind
992
993 WHERE batch_id = l_batch_id
994 AND line_no = l_line_no
995 AND line_type = l_line_type
996 AND last_update_date = p_material_detail.last_update_date;
997 ELSE
998 gme_common_pvt.log_message ('GME_NO_KEYS'
999 ,'TABLE_NAME'
1000 ,g_table_name);
1001 RETURN FALSE;
1002 END IF;
1003
1004 IF (SQL%FOUND) THEN
1005 RETURN TRUE;
1006 ELSE
1007 IF l_dummy = 0 THEN
1008 gme_common_pvt.log_message ('GME_NO_DATA_FOUND'
1009 ,'TABLE_NAME'
1010 ,g_table_name);
1011 ELSE
1012 gme_common_pvt.log_message ('GME_RECORD_CHANGED'
1013 ,'TABLE_NAME'
1014 ,g_table_name);
1015 END IF;
1016
1017 RETURN FALSE;
1018 END IF;
1019 EXCEPTION
1020 WHEN NO_DATA_FOUND THEN
1021 IF l_dummy = 0 THEN
1022 gme_common_pvt.log_message ('GME_NO_DATA_FOUND'
1023 ,'TABLE_NAME'
1024 ,g_table_name);
1025 ELSE
1026 gme_common_pvt.log_message ('GME_RECORD_CHANGED'
1027 ,'TABLE_NAME'
1028 ,g_table_name);
1029 END IF;
1030
1031 RETURN FALSE;
1032 WHEN locked_by_other_user THEN
1033 gme_common_pvt.log_message ('GME_RECORD_LOCKED'
1034 ,'TABLE_NAME'
1035 ,g_table_name
1036 ,'RECORD'
1037 ,'Line No'
1038 ,'KEY'
1039 ,TO_CHAR (p_material_detail.line_no) );
1040 RETURN FALSE;
1041 WHEN OTHERS THEN
1042 IF g_debug <= gme_debug.g_log_unexpected THEN
1043 gme_debug.put_line ( 'When others exception in '
1044 || g_pkg_name
1045 || '.'
1046 || l_api_name
1047 || ' Error is '
1048 || SQLERRM);
1049 END IF;
1050
1051 gme_common_pvt.log_message ('GME_UNEXPECTED_ERROR', 'ERROR', SQLERRM);
1052 RETURN FALSE;
1053 END update_row;
1054
1055 /*
1056 +==========================================================================+
1057 | FUNCTION NAME |
1058 | fetch_tab |
1059 | |
1060 | TYPE |
1061 | Private |
1062 | |
1063 | USAGE |
1064 | fetch_tab will fetch a tab in gme_material_details |
1065 | |
1066 | |
1067 | DESCRIPTION |
1068 | fetch_tab will fetch a tab in gme_material_details |
1069 | |
1070 | |
1071 | PARAMETERS |
1072 | p_material_detail IN gme_material_details%ROWTYPE |
1073 | x_material_detail IN OUT NOCOPY GME_API_GRP.material_details_tab |
1074 | |
1075 | RETURNS |
1076 | BOOLEAN |
1077 | |
1078 | HISTORY |
1079 | 08-May-2001 odaboval Created |
1080 | |
1081 +==========================================================================+
1082 */
1083 FUNCTION fetch_tab (
1084 p_material_detail IN gme_material_details%ROWTYPE
1085 ,x_material_detail IN OUT NOCOPY gme_common_pvt.material_details_tab)
1086 RETURN BOOLEAN
1087 IS
1088 i NUMBER := 0;
1089 l_api_name CONSTANT VARCHAR2 (30) := 'FETCH_TAB';
1090
1091 CURSOR c_material_dtl_0 (l_batch_id IN NUMBER)
1092 IS
1093 SELECT *
1094 FROM gme_material_details
1095 WHERE batch_id = l_batch_id;
1096
1097 CURSOR c_material_dtl_1 (l_mat_dtl_id IN NUMBER)
1098 IS
1099 SELECT *
1100 FROM gme_material_details
1101 WHERE material_detail_id = l_mat_dtl_id;
1102
1103 CURSOR c_material_dtl_2 (
1104 l_batch_id IN NUMBER
1105 ,l_line_no IN NUMBER
1106 ,l_line_type IN NUMBER)
1107 IS
1108 SELECT *
1109 FROM gme_material_details
1110 WHERE batch_id = l_batch_id
1111 AND line_no = l_line_no
1112 AND line_type = l_line_type;
1113 BEGIN
1114 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1115 gme_debug.put_line ( l_api_name
1116 || ' in fetch_tab. batch_id= '
1117 || p_material_detail.batch_id);
1118 END IF;
1119
1120 IF (p_material_detail.batch_id IS NOT NULL) THEN
1121 OPEN c_material_dtl_0 (p_material_detail.batch_id);
1122
1123 LOOP
1124 i := i + 1;
1125
1126 FETCH c_material_dtl_0
1127 INTO x_material_detail (i);
1128
1129 EXIT WHEN c_material_dtl_0%NOTFOUND;
1130 END LOOP;
1131
1132 IF (c_material_dtl_0%ROWCOUNT = 0) THEN
1133 CLOSE c_material_dtl_0;
1134
1135 RAISE NO_DATA_FOUND;
1136 END IF;
1137
1138 CLOSE c_material_dtl_0;
1139 ELSIF (p_material_detail.material_detail_id IS NOT NULL) THEN
1140 OPEN c_material_dtl_1 (p_material_detail.material_detail_id);
1141
1142 LOOP
1143 i := i + 1;
1144
1145 FETCH c_material_dtl_1
1146 INTO x_material_detail (i);
1147
1148 EXIT WHEN c_material_dtl_0%NOTFOUND;
1149 END LOOP;
1150
1151 IF (c_material_dtl_1%ROWCOUNT = 0) THEN
1152 CLOSE c_material_dtl_1;
1153
1154 RAISE NO_DATA_FOUND;
1155 END IF;
1156
1157 CLOSE c_material_dtl_1;
1158 ELSIF (p_material_detail.batch_id IS NOT NULL)
1159 AND (p_material_detail.line_no IS NOT NULL)
1160 AND (p_material_detail.line_type IS NOT NULL) THEN
1161 OPEN c_material_dtl_2 (p_material_detail.batch_id
1162 ,p_material_detail.line_no
1163 ,p_material_detail.line_type);
1164
1165 LOOP
1166 i := i + 1;
1167
1168 FETCH c_material_dtl_2
1169 INTO x_material_detail (i);
1170
1171 EXIT WHEN c_material_dtl_0%NOTFOUND;
1172 END LOOP;
1173
1174 IF (c_material_dtl_2%NOTFOUND) THEN
1175 CLOSE c_material_dtl_2;
1176
1177 RAISE NO_DATA_FOUND;
1178 END IF;
1179
1180 CLOSE c_material_dtl_2;
1181 ELSE
1182 gme_common_pvt.log_message ('GME_NO_KEYS'
1183 ,'TABLE_NAME'
1184 ,g_table_name);
1185 RETURN FALSE;
1186 END IF;
1187
1188 RETURN TRUE;
1189 EXCEPTION
1190 WHEN NO_DATA_FOUND THEN
1191 gme_common_pvt.log_message ('GME_NO_DATA_FOUND'
1192 ,'TABLE_NAME'
1193 ,g_table_name);
1194 RETURN FALSE;
1195 WHEN OTHERS THEN
1196 IF g_debug <= gme_debug.g_log_unexpected THEN
1197 gme_debug.put_line ( 'When others exception in '
1198 || g_pkg_name
1199 || '.'
1200 || l_api_name
1201 || ' Error is '
1202 || SQLERRM);
1203 END IF;
1204
1205 IF (c_material_dtl_0%ISOPEN) THEN
1206 CLOSE c_material_dtl_0;
1207 END IF;
1208
1209 IF (c_material_dtl_1%ISOPEN) THEN
1210 CLOSE c_material_dtl_1;
1211 END IF;
1212
1213 IF (c_material_dtl_2%ISOPEN) THEN
1214 CLOSE c_material_dtl_2;
1215 END IF;
1216
1217 gme_common_pvt.log_message ('GME_UNEXPECTED_ERROR', 'ERROR', SQLERRM);
1218 RETURN FALSE;
1219 END fetch_tab;
1220 END gme_material_details_dbl;