[Home] [Help]
PACKAGE BODY: APPS.IGI_SLS_IMP_EXP
Source
1 PACKAGE BODY IGI_SLS_IMP_EXP AS
2 --$Header: igislseb.pls 120.2.12010000.2 2008/08/04 13:07:19 sasukuma ship $
3
4 PROCEDURE create_sls_datafix (errbuf out nocopy varchar2,retcode out nocopy number,request_type in varchar2)
5 IS
6 -- retcode number(1);
7 -- errbuf varchar2(30);
8 already_exists EXCEPTION;
9 l_excep_level number(1);
10 l_sql_str varchar2(300);
11 l_sql_trun varchar2(300);
12 l_count_rec varchar2(300);
13 l_num_rec number;
14 l_sql_ins varchar2(300);
15 l_Start_Time varchar2(100);
16 l_End_Time varchar2(100);
17 session varchar2(5) := 'IGI';
18
19
20 CURSOR c_get_enab_sectab IS
21 SELECT owner,
22 table_name,
23 sls_table_name,
24 date_enabled,
25 date_disabled,
26 date_removed,
27 date_security_applied,
28 update_allowed
29 FROM igi_sls_secure_tables
30 WHERE date_removed IS NULL
31 AND date_object_created IS NOT NULL;
32
33
34 BEGIN
35
36 retcode := 0;
37 errbuf := 'Normal Completion';
38
39 -- select to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS') into l_Start_Time from dual;
40 -- dbms_output.put_line(l_Start_Time||' Start time');
41
42
43 -- Create addiotional column in SLS secured tables --
44 FOR rt_c_get_enab_sectab IN c_get_enab_sectab
45 LOOP
46 -- dbms_output.put_line(rt_c_get_enab_sectab.table_name||' Before count');
47 l_count_rec :=('select count(*) from '||rt_c_get_enab_sectab.sls_table_name);
48 EXECUTE IMMEDIATE l_count_rec into l_num_rec;
49 -- dbms_output.put_line(rt_c_get_enab_sectab.table_name||' After Count'||l_num_rec);
50
51 IF request_type = 'PRE-EXPORT' THEN
52 IF l_num_rec <> 0 THEN
53 -- dbms_output.put_line(rt_c_get_enab_sectab.table_name||' Inside Condition');
54 -- Call procedure to create additional column in to core tables
55 igi_sls_objects_pkg.create_sls_col
56 (sec_tab => rt_c_get_enab_sectab.table_name,
57 schema_name => rt_c_get_enab_sectab.owner,
58 errbuf => errbuf,
59 retcode => retcode);
60
61
62 -- Populate New column with SLS group from SLS Tables
63
64 l_sql_str :=('UPDATE '||
65 rt_c_get_enab_sectab.owner||'.'||rt_c_get_enab_sectab.table_name||' a '
66 ||'SET a.igi_sls_sec_group =' ||
67 '(SELECT b.sls_sec_grp from '|| rt_c_get_enab_sectab.sls_table_name||' b '||
68 'WHERE a.rowid = b.sls_rowid) WHERE EXISTS (SELECT '||'''x''' ||' from '
69 || rt_c_get_enab_sectab.sls_table_name||' c where c.sls_rowid = a.rowid)');
70
71 EXECUTE IMMEDIATE l_sql_str;
72 COMMIT;
73 END IF;
74 ELSIF request_type = 'PRE-EXPORT-UNDO' OR request_type ='POST-IMPORT' THEN
75
76 IF request_type ='POST-IMPORT' THEN
77 IF l_num_rec <> 0 THEN
78 -- Truncate SLS tables
79 l_sql_trun := 'BEGIN'||' IGI.apps_ddl.apps_ddl('||''''||'TRUNCATE TABLE '||session||'.'||rt_c_get_enab_sectab.sls_table_name||''''||');END;';
80
81
82 EXECUTE IMMEDIATE l_sql_trun;
83 END IF;
84 -- Copy data(new rowid and SLS group name) to SLS tables
85 IGI_SLS_IMP_EXP.insert_sls_data(rt_c_get_enab_sectab.sls_table_name,
86 rt_c_get_enab_sectab.owner,
87 rt_c_get_enab_sectab.table_name);
88
89 END IF;
90 -- Drop column in core tables
91
92 igi_sls_objects_pkg.drop_sls_col (sec_tab => rt_c_get_enab_sectab.table_name,
93 schema_name => rt_c_get_enab_sectab.owner,
94 errbuf => errbuf,
95 retcode => retcode);
96
97
98
99
100
101 END IF;
102
103 END LOOP;
104
105 -- select to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS') into l_End_Time from dual;
106 -- dbms_output.put_line(l_End_Time||' End time');
107
108 EXCEPTION
109
110 WHEN already_exists
111 THEN NULL;
112
113 WHEN OTHERS
114 THEN
115 FND_MESSAGE.SET_NAME('IGI', 'IGI_LOGGING_USER_ERROR'); -- Seeded Message
116 l_excep_level :=1;
117 retcode := 2;
118 errbuf := Fnd_message.get;
119 igi_sls_objects_pkg .write_to_log(l_excep_level, 'create_sls_col','END igi_sls_objects_pkg.create_sls_col - failed with error ' || SQLERRM );
120 -- dbms_output.put_line(SQLERRM);
121 RETURN;
122
123 END create_sls_datafix;
124
125
126 PROCEDURE insert_sls_data(sls_tab IN VARCHAR2,schema_name IN VARCHAR2,core_tab in varchar2)IS
127 l_sql_ins varchar2(300);
128 already_exists EXCEPTION;
129 PRAGMA EXCEPTION_INIT(already_exists, -00904);
130 BEGIN
131
132 l_sql_ins :=('Insert into '||sls_tab||'(SLS_ROWID,SLS_SEC_GRP) '||
133 '(select a.rowid,a.IGI_SLS_SEC_GROUP from '||schema_name||'.'||core_tab||' a'||' where a.IGI_SLS_SEC_GROUP is not null)');
134 EXECUTE IMMEDIATE l_sql_ins;
135 COMMIT;
136 EXCEPTION
137 WHEN already_exists
138 THEN null;
139 END insert_sls_data;
140
141 END IGI_SLS_IMP_EXP;