[Home] [Help]
PACKAGE BODY: APPS.GMD_LAB_ORGN_MIGRATION
Source
1 PACKAGE BODY GMD_LAB_ORGN_MIGRATION AS
2 /* $Header: GMDLOMGB.pls 120.1 2005/09/29 11:19:40 srsriran noship $ pxkumar*/
3
4 PROCEDURE INSERT_LAB_ORGN IS
5
6 error_msg VARCHAR2(240);
7
8 CURSOR Cur_get_text(ptext_code NUMBER) IS
9 SELECT * FROM lm_text_tbl
10 WHERE text_code = ptext_code;
11
12 l_count NUMBER;
13 l_row_id VARCHAR2(80);
14 l_temp_rec Cur_get_text%ROWTYPE;
15 l_text_code NUMBER DEFAULT NULL;
16
17 BEGIN
18
19 FOR cur_lab_tmp IN (SELECT * FROM lm_ltyp_mst l
20 where not exists ( select 1 from sy_orgn_mst
21 where orgn_code = l.lab_type)) LOOP
22 Begin
23
24 /* Get the text code */
25 IF (Cur_lab_tmp.text_code IS NOT NULL) THEN
26 SELECT Gem5_text_code_s.nextval INTO l_text_code FROM sys.dual;
27
28 OPEN Cur_get_text(Cur_lab_tmp.text_code);
29 LOOP
30 FETCH Cur_get_text INTO l_temp_rec;
31 EXIT WHEN Cur_get_text%NOTFOUND;
32
33 gma_sy_text_tbl_pkg.insert_row(x_rowid => l_row_id,
34 x_text_code => l_text_code,
35 x_paragraph_code => l_temp_rec.paragraph_code,
36 x_sub_paracode => l_temp_rec.sub_paracode,
37 x_line_no => l_temp_rec.line_no,
38 x_lang_code => l_temp_rec.lang_code,
39 x_text => l_temp_rec.text,
40 x_creation_date => l_temp_rec.creation_date,
41 x_created_by => l_temp_rec.created_by,
42 x_last_update_date => l_temp_rec.last_update_date,
43 x_last_updated_by => l_temp_rec.last_updated_by,
44 x_last_update_login => l_temp_rec.last_update_login);
45 END LOOP;/* for cur_get_text */
46 CLOSE Cur_get_text;
47 ELSE
48 l_text_code := NULL;
49 END IF; /* End of get text code logic */
50
51 INSERT INTO sy_orgn_mst (orgn_code,
52 parent_orgn_code,
53 co_code,
54 orgn_name,
55 plant_ind,
56 poc_ind,
57 text_code,
58 delete_mark,
59 trans_cnt,
60 creation_date,
61 last_update_date,
62 created_by,
63 last_updated_by,
64 last_update_login)
65 VALUES(Cur_lab_tmp.lab_type,
66 Cur_lab_tmp.lab_type,
67 Cur_lab_tmp.lab_type,
68 Cur_lab_tmp.lab_description,
69 2,
70 0,
71 l_text_code,
72 decode(Cur_lab_tmp.active_ind,1,0,1),
73 Cur_lab_tmp.trans_cnt,
74 Cur_lab_tmp.creation_date,
75 Cur_lab_tmp.last_update_date,
76 Cur_lab_tmp.created_by,
77 Cur_lab_tmp.last_updated_by,
78 Cur_lab_tmp.last_update_login);
79
80 EXCEPTION
81 WHEN OTHERS THEN
82 error_msg := SQLERRM;
83 INSERT INTO GMD_MIGRATION ( migration_id,
84 source_table,
85 target_table,
86 source_id ,
87 target_id ,
88 message_text )
89 SELECT GMD_REQUEST_ID_S.nextval,
90 'LM_LTYP_MST',
91 'SY_ORGN_MST',
92 Cur_lab_tmp.lab_type,
93 Cur_lab_tmp.lab_type,
94 error_msg
95 FROM DUAL ;
96 End;
97
98 END LOOP;
99
100 END INSERT_LAB_ORGN;
101
102 END GMD_LAB_ORGN_MIGRATION;