[Home] [Help]
PACKAGE BODY: APPS.GME_MATERIAL_DETAILS_DBL
Source
1 PACKAGE BODY gme_material_details_dbl AS
2 /* $Header: GMEVGMDB.pls 120.3.12010000.2 2009/02/27 20:19:17 gmurator ship $ */
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 G. Muratore 26-Feb-2009 Bug 7710435
804 Added Called by parameter to avoid timestamp failures during batch
805 creation. Also, corrected checking of l_dummy by adding NVL and
806 left additional debug messages in here for future use.
807 +==========================================================================+
808 Api end of comments
809 */
810 FUNCTION update_row (p_material_detail IN gme_material_details%ROWTYPE
811 ,p_called_by IN VARCHAR2 DEFAULT 'U')
812 RETURN BOOLEAN
813 IS
814 l_material_detail_id NUMBER;
815 l_batch_id NUMBER;
816 l_line_no NUMBER;
817 l_line_type NUMBER;
818 l_upd DATE;
819 l_dummy NUMBER := 0;
820 locked_by_other_user EXCEPTION;
821 PRAGMA EXCEPTION_INIT (locked_by_other_user, -54);
822 l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_ROW';
823 BEGIN
824 l_material_detail_id := p_material_detail.material_detail_id;
825 l_batch_id := p_material_detail.batch_id;
826 l_line_no := p_material_detail.line_no;
827 l_line_type := p_material_detail.line_type;
828
829 IF g_debug <= gme_debug.g_log_statement THEN
830 gme_debug.put_line ('Entering '||l_api_name);
831 gme_debug.put_line ('Lets see what is really happening in db layer upon update.');
832 gme_debug.put_line ('material detail id is '||l_material_detail_id);
833 gme_debug.put_line ('batch detail id is '||l_batch_id);
834 gme_debug.put_line ('line no is '||l_line_no);
835 gme_debug.put_line ('l_line_type is '||l_line_type);
836 gme_debug.put_line ('last_update_date coming in is '||TO_CHAR(p_material_detail.last_update_date,'DD-MON-YYYY HH24:MI:SS'));
837 gme_debug.put_line ('timestamp is '||TO_CHAR(gme_common_pvt.g_timestamp,'DD-MON-YYYY HH24:MI:SS'));
838 END IF;
839
840 IF l_material_detail_id IS NOT NULL THEN
841 SELECT 1, last_update_date
842 INTO l_dummy, l_upd
843 FROM gme_material_details
844 WHERE material_detail_id = l_material_detail_id
845 FOR UPDATE NOWAIT;
846
847 UPDATE gme_material_details
848 SET formulaline_id = p_material_detail.formulaline_id
849 ,line_no = p_material_detail.line_no
850 ,
851 --item_id = p_material_detail.item_id,
852 line_type = p_material_detail.line_type
853 ,plan_qty = p_material_detail.plan_qty
854 ,
855 --item_um = p_material_detail.item_um,
856 item_um2 = p_material_detail.item_um2
857 ,actual_qty = nvl(p_material_detail.actual_qty,0)
858 ,release_type = p_material_detail.release_type
859 ,scrap_factor = p_material_detail.scrap_factor
860 ,scale_type = p_material_detail.scale_type
861 ,phantom_type = p_material_detail.phantom_type
862 ,cost_alloc = p_material_detail.cost_alloc
863 ,alloc_ind = p_material_detail.alloc_ind
864 ,COST = p_material_detail.COST
865 ,text_code = p_material_detail.text_code
866 ,phantom_id = p_material_detail.phantom_id
867 ,last_updated_by = gme_common_pvt.g_user_ident
868 ,last_update_date = gme_common_pvt.g_timestamp
869 ,attribute1 = p_material_detail.attribute1
870 ,attribute2 = p_material_detail.attribute2
871 ,attribute3 = p_material_detail.attribute3
872 ,attribute4 = p_material_detail.attribute4
873 ,attribute5 = p_material_detail.attribute5
874 ,attribute6 = p_material_detail.attribute6
875 ,attribute7 = p_material_detail.attribute7
876 ,attribute8 = p_material_detail.attribute8
877 ,attribute9 = p_material_detail.attribute9
878 ,attribute10 = p_material_detail.attribute10
879 ,attribute11 = p_material_detail.attribute11
880 ,attribute12 = p_material_detail.attribute12
881 ,attribute13 = p_material_detail.attribute13
882 ,attribute14 = p_material_detail.attribute14
883 ,attribute15 = p_material_detail.attribute15
884 ,attribute16 = p_material_detail.attribute16
885 ,attribute17 = p_material_detail.attribute17
886 ,attribute18 = p_material_detail.attribute18
887 ,attribute19 = p_material_detail.attribute19
888 ,attribute20 = p_material_detail.attribute20
889 ,attribute21 = p_material_detail.attribute21
890 ,attribute22 = p_material_detail.attribute22
891 ,attribute23 = p_material_detail.attribute23
892 ,attribute24 = p_material_detail.attribute24
893 ,attribute25 = p_material_detail.attribute25
894 ,attribute26 = p_material_detail.attribute26
895 ,attribute27 = p_material_detail.attribute27
896 ,attribute28 = p_material_detail.attribute28
897 ,attribute29 = p_material_detail.attribute29
898 ,attribute30 = p_material_detail.attribute30
899 ,attribute_category = p_material_detail.attribute_category
900 ,last_update_login = p_material_detail.last_update_login
901 ,scale_rounding_variance =
902 p_material_detail.scale_rounding_variance
903 ,scale_multiple = p_material_detail.scale_multiple
904 ,rounding_direction = p_material_detail.rounding_direction
905 ,contribute_yield_ind = p_material_detail.contribute_yield_ind
906 ,contribute_step_qty_ind =
907 p_material_detail.contribute_step_qty_ind
908 ,wip_plan_qty = p_material_detail.wip_plan_qty
909 ,original_qty = p_material_detail.original_qty
910 ,by_product_type = p_material_detail.by_product_type
911 ,organization_id = p_material_detail.organization_id
912 ,inventory_item_id = p_material_detail.inventory_item_id
913 ,subinventory = p_material_detail.subinventory
914 ,locator_id = p_material_detail.locator_id
915 ,revision = p_material_detail.revision
916 ,backordered_qty = p_material_detail.backordered_qty
917 ,material_requirement_date =
918 p_material_detail.material_requirement_date
919 ,phantom_line_id = p_material_detail.phantom_line_id
920 ,move_order_line_id = p_material_detail.move_order_line_id
921 ,dtl_um = p_material_detail.dtl_um
922 ,dispense_ind = p_material_detail.dispense_ind
923
924 WHERE material_detail_id = p_material_detail.material_detail_id
925 -- AND last_update_date = p_material_detail.last_update_date;
926 -- Bug 7710435 Put decode in there to avoid timestamp failures during batch creation.
927 AND last_update_date = DECODE(p_called_by, 'U', p_material_detail.last_update_date, last_update_date);
928 ELSIF (l_batch_id IS NOT NULL)
929 AND (l_line_no IS NOT NULL)
930 AND (l_line_type IS NOT NULL) THEN
931 SELECT 1
932 INTO l_dummy
933 FROM gme_material_details
934 WHERE batch_id = l_batch_id
935 AND line_no = l_line_no
936 AND line_type = l_line_type
937 FOR UPDATE NOWAIT;
938
939 UPDATE gme_material_details
940 SET formulaline_id = p_material_detail.formulaline_id
941 ,line_no = p_material_detail.line_no
942 ,line_type = p_material_detail.line_type
943 ,plan_qty = p_material_detail.plan_qty
944 ,item_um2 = p_material_detail.item_um2
945 ,actual_qty = p_material_detail.actual_qty
946 ,release_type = p_material_detail.release_type
947 ,scrap_factor = p_material_detail.scrap_factor
948 ,scale_type = p_material_detail.scale_type
949 ,phantom_type = p_material_detail.phantom_type
950 ,cost_alloc = p_material_detail.cost_alloc
951 ,alloc_ind = p_material_detail.alloc_ind
952 ,COST = p_material_detail.COST
953 ,text_code = p_material_detail.text_code
954 ,phantom_id = p_material_detail.phantom_id
955 ,last_updated_by = gme_common_pvt.g_user_ident
956 ,last_update_date = gme_common_pvt.g_timestamp
957 ,attribute1 = p_material_detail.attribute1
958 ,attribute2 = p_material_detail.attribute2
959 ,attribute3 = p_material_detail.attribute3
960 ,attribute4 = p_material_detail.attribute4
961 ,attribute5 = p_material_detail.attribute5
962 ,attribute6 = p_material_detail.attribute6
963 ,attribute7 = p_material_detail.attribute7
964 ,attribute8 = p_material_detail.attribute8
965 ,attribute9 = p_material_detail.attribute9
966 ,attribute10 = p_material_detail.attribute10
967 ,attribute11 = p_material_detail.attribute11
968 ,attribute12 = p_material_detail.attribute12
969 ,attribute13 = p_material_detail.attribute13
970 ,attribute14 = p_material_detail.attribute14
971 ,attribute15 = p_material_detail.attribute15
972 ,attribute16 = p_material_detail.attribute16
973 ,attribute17 = p_material_detail.attribute17
974 ,attribute18 = p_material_detail.attribute18
975 ,attribute19 = p_material_detail.attribute19
976 ,attribute20 = p_material_detail.attribute20
977 ,attribute21 = p_material_detail.attribute21
978 ,attribute22 = p_material_detail.attribute22
979 ,attribute23 = p_material_detail.attribute23
980 ,attribute24 = p_material_detail.attribute24
981 ,attribute25 = p_material_detail.attribute25
982 ,attribute26 = p_material_detail.attribute26
983 ,attribute27 = p_material_detail.attribute27
984 ,attribute28 = p_material_detail.attribute28
985 ,attribute29 = p_material_detail.attribute29
986 ,attribute30 = p_material_detail.attribute30
987 ,attribute_category = p_material_detail.attribute_category
988 ,last_update_login = p_material_detail.last_update_login
989 ,scale_rounding_variance =
990 p_material_detail.scale_rounding_variance
991 ,scale_multiple = p_material_detail.scale_multiple
992 ,rounding_direction = p_material_detail.rounding_direction
993 ,contribute_yield_ind = p_material_detail.contribute_yield_ind
994 ,contribute_step_qty_ind =
995 p_material_detail.contribute_step_qty_ind
996 ,wip_plan_qty = p_material_detail.wip_plan_qty
997 ,by_product_type = p_material_detail.by_product_type
998 ,organization_id = p_material_detail.organization_id
999 ,inventory_item_id = p_material_detail.inventory_item_id
1000 ,subinventory = p_material_detail.subinventory
1001 ,locator_id = p_material_detail.locator_id
1002 ,revision = p_material_detail.revision
1003 ,backordered_qty = p_material_detail.backordered_qty
1004 ,original_primary_qty = p_material_detail.original_primary_qty
1005 ,material_requirement_date =
1006 p_material_detail.material_requirement_date
1007 ,phantom_line_id = p_material_detail.phantom_line_id
1008 ,move_order_line_id = p_material_detail.move_order_line_id
1009 ,dtl_um = p_material_detail.dtl_um
1010 ,dispense_ind = p_material_detail.dispense_ind
1011
1012 WHERE batch_id = l_batch_id
1013 AND line_no = l_line_no
1014 AND line_type = l_line_type
1015 -- AND last_update_date = p_material_detail.last_update_date;
1016 -- Bug 7710435 Put decode in there to avoid timestamp failures during batch creation.
1017 AND last_update_date = DECODE(p_called_by, 'U', p_material_detail.last_update_date, last_update_date);
1018 ELSE
1019 gme_common_pvt.log_message ('GME_NO_KEYS'
1020 ,'TABLE_NAME'
1021 ,g_table_name);
1022 RETURN FALSE;
1023 END IF;
1024
1025 IF (SQL%FOUND) THEN
1026 RETURN TRUE;
1027 ELSE
1028
1029 IF g_debug <= gme_debug.g_log_statement THEN
1030 gme_debug.put_line ('Lets see why update is failing. POINT 1');
1031 IF (l_dummy IS NULL) THEN
1032 gme_debug.put_line ('l_dummy is NULL');
1033 ELSE
1034 gme_debug.put_line ('l_dummy is '||l_dummy);
1035 END IF;
1036 gme_debug.put_line ('DB LUP date is '||TO_CHAR(l_upd,'DD-MON-YYYY HH24:MI:SS'));
1037 END IF;
1038
1039 IF NVL(l_dummy,0) = 0 THEN
1040 gme_common_pvt.log_message ('GME_NO_DATA_FOUND'
1041 ,'TABLE_NAME'
1042 ,g_table_name);
1043 ELSE
1044 gme_common_pvt.log_message ('GME_RECORD_CHANGED'
1045 ,'TABLE_NAME'
1046 ,g_table_name);
1047 END IF;
1048
1049 RETURN FALSE;
1050 END IF;
1051 EXCEPTION
1052 WHEN NO_DATA_FOUND THEN
1053 IF g_debug <= gme_debug.g_log_statement THEN
1054 gme_debug.put_line ('Lets see why update is failing. POINT 2');
1055 IF (l_dummy IS NULL) THEN
1056 gme_debug.put_line ('l_dummy is NULL');
1057 ELSE
1058 gme_debug.put_line ('l_dummy is '||l_dummy);
1059 END IF;
1060 END IF;
1061
1062 IF NVL(l_dummy,0) = 0 THEN
1063 gme_common_pvt.log_message ('GME_NO_DATA_FOUND'
1064 ,'TABLE_NAME'
1065 ,g_table_name);
1066 ELSE
1067 gme_common_pvt.log_message ('GME_RECORD_CHANGED'
1068 ,'TABLE_NAME'
1069 ,g_table_name);
1070 END IF;
1071
1072 RETURN FALSE;
1073 WHEN locked_by_other_user THEN
1074 gme_common_pvt.log_message ('GME_RECORD_LOCKED'
1075 ,'TABLE_NAME'
1076 ,g_table_name
1077 ,'RECORD'
1078 ,'Line No'
1079 ,'KEY'
1080 ,TO_CHAR (p_material_detail.line_no) );
1081 RETURN FALSE;
1082 WHEN OTHERS THEN
1083 IF g_debug <= gme_debug.g_log_unexpected THEN
1084 gme_debug.put_line ( 'When others exception in '
1085 || g_pkg_name
1086 || '.'
1087 || l_api_name
1088 || ' Error is '
1089 || SQLERRM);
1090 END IF;
1091
1092 gme_common_pvt.log_message ('GME_UNEXPECTED_ERROR', 'ERROR', SQLERRM);
1093 RETURN FALSE;
1094 END update_row;
1095
1096 /*
1097 +==========================================================================+
1098 | FUNCTION NAME |
1099 | fetch_tab |
1100 | |
1101 | TYPE |
1102 | Private |
1103 | |
1104 | USAGE |
1105 | fetch_tab will fetch a tab in gme_material_details |
1106 | |
1107 | |
1108 | DESCRIPTION |
1109 | fetch_tab will fetch a tab in gme_material_details |
1110 | |
1111 | |
1112 | PARAMETERS |
1113 | p_material_detail IN gme_material_details%ROWTYPE |
1114 | x_material_detail IN OUT NOCOPY GME_API_GRP.material_details_tab |
1115 | |
1116 | RETURNS |
1117 | BOOLEAN |
1118 | |
1119 | HISTORY |
1120 | 08-May-2001 odaboval Created |
1121 | |
1122 +==========================================================================+
1123 */
1124 FUNCTION fetch_tab (
1125 p_material_detail IN gme_material_details%ROWTYPE
1126 ,x_material_detail IN OUT NOCOPY gme_common_pvt.material_details_tab)
1127 RETURN BOOLEAN
1128 IS
1129 i NUMBER := 0;
1130 l_api_name CONSTANT VARCHAR2 (30) := 'FETCH_TAB';
1131
1132 CURSOR c_material_dtl_0 (l_batch_id IN NUMBER)
1133 IS
1134 SELECT *
1135 FROM gme_material_details
1136 WHERE batch_id = l_batch_id;
1137
1138 CURSOR c_material_dtl_1 (l_mat_dtl_id IN NUMBER)
1139 IS
1140 SELECT *
1141 FROM gme_material_details
1142 WHERE material_detail_id = l_mat_dtl_id;
1143
1144 CURSOR c_material_dtl_2 (
1145 l_batch_id IN NUMBER
1146 ,l_line_no IN NUMBER
1147 ,l_line_type IN NUMBER)
1148 IS
1149 SELECT *
1150 FROM gme_material_details
1151 WHERE batch_id = l_batch_id
1152 AND line_no = l_line_no
1153 AND line_type = l_line_type;
1154 BEGIN
1155 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1156 gme_debug.put_line ( l_api_name
1157 || ' in fetch_tab. batch_id= '
1158 || p_material_detail.batch_id);
1159 END IF;
1160
1161 IF (p_material_detail.batch_id IS NOT NULL) THEN
1162 OPEN c_material_dtl_0 (p_material_detail.batch_id);
1163
1164 LOOP
1165 i := i + 1;
1166
1167 FETCH c_material_dtl_0
1168 INTO x_material_detail (i);
1169
1170 EXIT WHEN c_material_dtl_0%NOTFOUND;
1171 END LOOP;
1172
1173 IF (c_material_dtl_0%ROWCOUNT = 0) THEN
1174 CLOSE c_material_dtl_0;
1175
1176 RAISE NO_DATA_FOUND;
1177 END IF;
1178
1179 CLOSE c_material_dtl_0;
1180 ELSIF (p_material_detail.material_detail_id IS NOT NULL) THEN
1181 OPEN c_material_dtl_1 (p_material_detail.material_detail_id);
1182
1183 LOOP
1184 i := i + 1;
1185
1186 FETCH c_material_dtl_1
1187 INTO x_material_detail (i);
1188
1189 EXIT WHEN c_material_dtl_0%NOTFOUND;
1190 END LOOP;
1191
1192 IF (c_material_dtl_1%ROWCOUNT = 0) THEN
1193 CLOSE c_material_dtl_1;
1194
1195 RAISE NO_DATA_FOUND;
1196 END IF;
1197
1198 CLOSE c_material_dtl_1;
1199 ELSIF (p_material_detail.batch_id IS NOT NULL)
1200 AND (p_material_detail.line_no IS NOT NULL)
1201 AND (p_material_detail.line_type IS NOT NULL) THEN
1202 OPEN c_material_dtl_2 (p_material_detail.batch_id
1203 ,p_material_detail.line_no
1204 ,p_material_detail.line_type);
1205
1206 LOOP
1207 i := i + 1;
1208
1209 FETCH c_material_dtl_2
1210 INTO x_material_detail (i);
1211
1212 EXIT WHEN c_material_dtl_0%NOTFOUND;
1213 END LOOP;
1214
1215 IF (c_material_dtl_2%NOTFOUND) THEN
1216 CLOSE c_material_dtl_2;
1217
1218 RAISE NO_DATA_FOUND;
1219 END IF;
1220
1221 CLOSE c_material_dtl_2;
1222 ELSE
1223 gme_common_pvt.log_message ('GME_NO_KEYS'
1224 ,'TABLE_NAME'
1225 ,g_table_name);
1226 RETURN FALSE;
1227 END IF;
1228
1229 RETURN TRUE;
1230 EXCEPTION
1231 WHEN NO_DATA_FOUND THEN
1232 gme_common_pvt.log_message ('GME_NO_DATA_FOUND'
1233 ,'TABLE_NAME'
1234 ,g_table_name);
1235 RETURN FALSE;
1236 WHEN OTHERS THEN
1237 IF g_debug <= gme_debug.g_log_unexpected THEN
1238 gme_debug.put_line ( 'When others exception in '
1239 || g_pkg_name
1240 || '.'
1241 || l_api_name
1242 || ' Error is '
1243 || SQLERRM);
1244 END IF;
1245
1246 IF (c_material_dtl_0%ISOPEN) THEN
1247 CLOSE c_material_dtl_0;
1248 END IF;
1249
1250 IF (c_material_dtl_1%ISOPEN) THEN
1251 CLOSE c_material_dtl_1;
1252 END IF;
1253
1254 IF (c_material_dtl_2%ISOPEN) THEN
1255 CLOSE c_material_dtl_2;
1256 END IF;
1257
1258 gme_common_pvt.log_message ('GME_UNEXPECTED_ERROR', 'ERROR', SQLERRM);
1259 RETURN FALSE;
1260 END fetch_tab;
1261 END gme_material_details_dbl;