[Home] [Help]
PACKAGE BODY: APPS.GMA_AUDIT_PKG
Source
1 PACKAGE BODY GMA_AUDIT_PKG AS
2 /* $Header: GMAAUDTB.pls 115.2 2001/12/20 01:49:29 pkm ship $ */
3 PROCEDURE RETRIEVE_RESULT_TABLES(AUDIT_WHERE_CLAUSE VARCHAR2,
4 TABLE_WHERE_CLAUSE VARCHAR2,
5 QUERY_TABLES IN OUT AUDIT_REQUIRED_TABLES_TYPE) AS
6 TYPE RETRIEVE_DATA IS REF CURSOR;
7 AUDIT_TABLE RETRIEVE_DATA; -- declare cursor variable
8 TABLE_DATA RETRIEVE_DATA; -- declare cursor variable
9 audit_sql_stmt VARCHAR2(4000);
10 table_sql_stmt VARCHAR2(4000);
11 AUDIT_TABLE_REC AUDIT_REQUIRED_TABLE;
12 i INTEGER := 1;
13 j INTEGER := 1;
14 DATA_EXISTS_FLAG INTEGER := null;
15 shadow_table_exists number;
16 BEGIN
17 audit_sql_stmt := ' select distinct FND_TABLES.TABLE_NAME TABLE_NAME ,
18 FND_TABLES.USER_TABLE_NAME USER_TABLE_NAME ,
19 FND_TABLES.TABLE_ID TABLE_ID ,
20 FND_TABLES.APPLICATION_ID TABLE_APPLICATION_ID
21 from FND_AUDIT_GROUPS ,
22 GMA_AUDIT_TMPLT_DTL ,
23 FND_AUDIT_TABLES ,
24 FND_TABLES
25 WHERE GMA_AUDIT_TMPLT_DTL.AUDIT_GROUP_ID = FND_AUDIT_GROUPS.AUDIT_GROUP_ID
26 AND GMA_AUDIT_TMPLT_DTL.APPLICATION_ID = FND_AUDIT_GROUPS.APPLICATION_ID
27 AND FND_AUDIT_GROUPS.APPLICATION_ID = FND_AUDIT_TABLES.AUDIT_GROUP_APP_ID
28 AND FND_AUDIT_GROUPS.AUDIT_GROUP_ID = FND_AUDIT_TABLES.AUDIT_GROUP_ID
29 AND FND_AUDIT_TABLES.TABLE_ID = FND_TABLES.TABLE_ID
30 AND FND_AUDIT_TABLES.TABLE_APP_ID = FND_TABLES.APPLICATION_ID
31 AND FND_AUDIT_GROUPS.STATE in (''E'',''G'',''N'')
32 AND FND_AUDIT_TABLES.STATE in (''E'',''G'',''N'')
33 AND '|| AUDIT_WHERE_CLAUSE;
34 OPEN AUDIT_TABLE FOR audit_sql_stmt;
35 LOOP
36 FETCH AUDIT_TABLE INTO AUDIT_TABLE_REC;
37 EXIT WHEN AUDIT_TABLE%NOTFOUND;
38 BEGIN
39 SELECT 1 into shadow_table_exists
40 FROM tab
41 where tname like substrb(AUDIT_TABLE_REC.TABLE_NAME,1,26)||'_AC1';
42 IF TABLE_WHERE_CLAUSE IS NULL THEN
43 QUERY_TABLES(i).TABLE_NAME := AUDIT_TABLE_REC.TABLE_NAME;
44 QUERY_TABLES(i).USER_TABLE_NAME := AUDIT_TABLE_REC.USER_TABLE_NAME;
45 QUERY_TABLES(i).TABLE_ID := AUDIT_TABLE_REC.TABLE_ID;
46 QUERY_TABLES(i).TABLE_APPLICATION_ID := AUDIT_TABLE_REC.TABLE_APPLICATION_ID;
47 i:=i+1;
48 ELSE
49 table_sql_stmt := ' SELECT 1 DATA_EXISTS_FLAG '||
50 ' FROM '||substrb(AUDIT_TABLE_REC.TABLE_NAME,1,26)||'_A ' ||
51 ' WHERE ' || TABLE_WHERE_CLAUSE;
52 OPEN TABLE_DATA FOR table_sql_stmt;
53 LOOP
54 FETCH TABLE_DATA INTO DATA_EXISTS_FLAG;
55 IF TABLE_DATA%FOUND THEN
56 QUERY_TABLES(i).TABLE_NAME := AUDIT_TABLE_REC.TABLE_NAME;
57 QUERY_TABLES(i).USER_TABLE_NAME := AUDIT_TABLE_REC.USER_TABLE_NAME;
58 QUERY_TABLES(i).TABLE_ID := AUDIT_TABLE_REC.TABLE_ID;
59 QUERY_TABLES(i).TABLE_APPLICATION_ID := AUDIT_TABLE_REC.TABLE_APPLICATION_ID;
60 i:=i+1;
61 END IF;
62 EXIT;
63 END LOOP;
64 CLOSE TABLE_DATA;
65 END IF;
66 EXCEPTION WHEN NO_DATA_FOUND THEN
67 null;
68 END;
69 END LOOP;
70 CLOSE AUDIT_TABLE;
71 END RETRIEVE_RESULT_TABLES;
72
73
74 /*********************************************************************************************
75 ******* *******
76 ******* *******
77 **********************************************************************************************/
78
79
80 PROCEDURE POPULATE_TAB_REP_DATA(P_SELECT_CLAUSE VARCHAR2,
81 P_TABLE_NAME VARCHAR2,
82 P_USER_TABLE_NAME VARCHAR2,
83 P_WHERE_CLAUSE VARCHAR2,
84 P_APPLICATION_ID NUMBER,
85 P_TABLE_ID NUMBER,
86 P_REP_ID NUMBER) IS
87
88 -- Local Variables
89
90 TYPE RETRIEVE_DATA IS REF CURSOR;
91 DISP_COL_VALUE RETRIEVE_DATA; -- declare cursor variable
92 AUDIT_DATA RETRIEVE_DATA; -- declare cursor variable
93 AUDIT_DATA_REC REPORT_TABLE;
94 PREV_AUDIT_KEY VARCHAR2(240);
95 user_column_list VARCHAR2(4000) := null;
96 pk_column_list VARCHAR2(4000) := null;
97 l_where_clause VARCHAR2(4000) := null;
98 col_flag number :=0;
99 j NUMBER:=1;
100 i NUMBER:=1;
101 l_tab_position number;
102 l_comma_position number;
103 table_alias VARCHAR2(40);
104 parse_text1 VARCHAR2(80);
105 parse_text2 VARCHAR2(80);
106 parsed_text VARCHAR2(80);
107 remaining_text VARCHAR2(2000);
108 missing_base_table_flag number := 0;
109 l_cursor_id INTEGER;
110 l_dummy INTEGER;
111 id VARCHAR2(2000);
112 disp_col_val VARCHAR2(2000);
113 disp_val VARCHAR2(2000);
114 data_select_stmt VARCHAR2(4000) := null;
115 v_select_stmt VARCHAR2(4000) := null;
116 v_select_stmt1 VARCHAR2(4000) := null;
117
118
119 -- cursor Variables
120
121 CURSOR GET_USER_KEY_COLUMNS IS
122 SELECT COL_DISP_IND ,
123 SELECT_CLAUSE,
124 FROM_CLAUSE,
125 WHERE_CLAUSE
126 FROM GMA_AUDIT_DISP_COLS
127 WHERE APPLICATION_ID = P_APPLICATION_ID
128 AND TABLE_ID = P_TABLE_ID;
129
130 User_key_columns_rec GET_USER_KEY_COLUMNS%ROWTYPE;
131
132 CURSOR GET_SYSTEM_KEY_COLS IS
133 SELECT fnd_cols.column_name column_name
134 FROM FND_COLUMNS fnd_cols,
135 FND_PRIMARY_KEYS pks,
136 FND_PRIMARY_KEY_COLUMNS keycols
137 WHERE pks.application_id = keycols.application_id
138 AND pks.table_id = keycols.table_id
139 AND pks.primary_key_id = keycols.primary_key_id
140 AND keycols.application_id = fnd_cols.application_id
141 AND keycols.table_id = fnd_cols.table_id
142 AND keycols.column_id = fnd_cols.column_id
143 AND pks.table_id = P_TABLE_ID
144 AND pks.application_id = P_APPLICATION_ID
145 AND pks.audit_key_flag = 'Y';
146
147 SYSTEM_KEY_COLS_REC GET_SYSTEM_KEY_COLS%ROWTYPE;
148
149 BEGIN
150 OPEN GET_USER_KEY_COLUMNS;
151 FETCH GET_USER_KEY_COLUMNS INTO User_key_columns_rec;
152 CLOSE GET_USER_KEY_COLUMNS;
153 remaining_text := ltrim(rtrim(User_key_columns_rec.FROM_CLAUSE));
154 i:= 0;
155 IF remaining_text IS NOT NULL THEN
156 LOOP
157 l_comma_position := instrb(remaining_text,',',1,1);
158 IF l_comma_position <> 0 THEN
159 parse_text1 := rtrim(ltrim(substrb(remaining_text,1,l_comma_position -1 )));
160 remaining_text := rtrim(ltrim(substrb(remaining_text,l_comma_position + 1)));
161 ELSE
162 parse_text1 := rtrim(ltrim(remaining_text));
163 remaining_text := null;
164 END IF;
165 parse_text2 := rtrim(ltrim(substrb(parse_text1,1,instrb(parse_text1,' ',1,1))));
166 IF parse_text2 is null THEN
167 IF upper(parse_text1) = upper(p_table_name) THEN
168 table_alias := parse_text1;
169 EXIT;
170 END IF;
171 ELSE
172 IF upper(parse_text2) = upper(p_table_name) THEN
173 table_alias := rtrim(ltrim(substrb(parse_text1,instrb(parse_text1,' ',1,1))));
174 EXIT;
175 END IF;
176 END IF ;
177 IF remaining_text IS NULL THEN
178 missing_base_table_flag := 1;
179 table_alias := null;
180 EXIT;
181 END IF;
182 END LOOP;
183 if table_alias IS NOT NULL THEN
184 table_alias := table_alias || '.';
185 ELSE
186 missing_base_table_flag := 1;
187 table_alias := p_table_name || '.';
188 end if;
189 ELSE
190 -- missing_base_table_flag := 1;
191 table_alias := p_table_name || '.';
192 END IF;
193 OPEN GET_SYSTEM_KEY_COLS;
194 LOOP
195 FETCH GET_SYSTEM_KEY_COLS into SYSTEM_KEY_COLS_REC;
196 EXIT WHEN GET_SYSTEM_KEY_COLS%NOTFOUND;
197 IF pk_column_list IS NULL THEN
198 pk_column_list := table_alias||SYSTEM_KEY_COLS_REC.column_name;
199 l_where_clause := SYSTEM_KEY_COLS_REC.column_name;
200 ELSE
201 pk_column_list := pk_column_list||','||table_alias||SYSTEM_KEY_COLS_REC.column_name;
202 l_where_clause := l_where_clause ||','||SYSTEM_KEY_COLS_REC.column_name;
203 END IF;
204 END LOOP;
205 pk_column_list := ltrim(replace(pk_column_list,',','||'||''''||','||''''||'||'));
206 l_where_clause := ltrim(replace(l_where_clause,',','||'||''''||','||''''||'||'));
207 IF missing_base_table_flag = 0 AND User_key_columns_rec.select_clause IS NOT NULL THEN
208 user_column_list := replace(User_key_columns_rec.select_clause,',','||'||''''||','||''''||'||');
209 v_select_stmt := ' SELECT '||pk_column_list || ' ID, '||
210 user_column_list || ' disp_val ' ;
211 IF User_key_columns_rec.from_clause is not null THEN
212 v_select_stmt := v_select_stmt || ' FROM ' ||User_key_columns_rec.FROM_CLAUSE;
213 ELSE
214 v_select_stmt := v_select_stmt || ' FROM '|| p_table_name;
215 END IF;
216 IF User_key_columns_rec.where_clause IS not NULL THEN
217 v_select_stmt := v_select_stmt ||
218 ' WHERE '||User_key_columns_rec.where_clause;
219 ELSE
220 v_select_stmt := v_select_stmt ||
221 ' WHERE 1=1 ';
222 END IF;
223 ELSE
224 v_select_stmt := ' SELECT '||pk_column_list || ' ID, '||
225 pk_column_list || ' disp_val ' ||
226 ' FROM '|| p_table_name ||
227 ' WHERE 1=1 ';
228 END IF;
229 /******
230 Retrieve Data from AC1 View then lookup for display column value
231 ******/
232
233 data_select_stmt := ' SELECT '||l_where_clause || ' AUDIT_KEY,AUDIT_TIMESTAMP,AUDIT_TRANSACTION_TYPE,AUDIT_USER_NAME,'
234 || P_SELECT_CLAUSE
235 || ' FROM '||substrb(P_TABLE_NAME,1,26)||'_AC1'
236 || ' WHERE '|| P_WHERE_CLAUSE
237 || ' order by '||l_where_clause;
238 OPEN AUDIT_DATA FOR data_select_stmt;
239 LOOP
240 FETCH AUDIT_DATA INTO AUDIT_DATA_REC;
241 EXIT WHEN AUDIT_DATA%NOTFOUND;
242 IF PREV_AUDIT_KEY IS NULL OR
243 PREV_AUDIT_KEY <> AUDIT_DATA_REC.AUDIT_KEY THEN
244 v_select_stmt1 := v_select_stmt || ' AND '||pk_column_list ||' = '||''''||AUDIT_DATA_REC.AUDIT_KEY||'''';
245 OPEN DISP_COL_VALUE for v_select_stmt1;
246 FETCH DISP_COL_VALUE INTO id,disp_val;
247 IF DISP_COL_VALUE%NOTFOUND THEN
248 disp_val := AUDIT_DATA_REC.AUDIT_KEY;
249 END IF;
250 CLOSE DISP_COL_VALUE;
251 PREV_AUDIT_KEY := AUDIT_DATA_REC.AUDIT_KEY ;
252 END IF;
253 INSERT INTO GMA_AUDIT_REP_DTL(REP_ID
254 ,TABLE_NAME
255 ,AUDIT_KEY
256 ,AUDIT_TIMESTAMP
257 ,AUDIT_TRANSACTION_TYPE
258 ,AUDIT_USER_NAME
259 ,COLUMN1_VALUE
260 ,COLUMN2_VALUE
261 ,COLUMN3_VALUE
262 ,COLUMN4_VALUE
263 ,COLUMN5_VALUE
264 ,ROW_DISP_COL)
265 VALUES
266 (P_REP_ID
267 ,P_TABLE_NAME
268 ,substrb(AUDIT_DATA_REC.AUDIT_KEY,1,240)
269 ,AUDIT_DATA_REC.AUDIT_TIMESTAMP
270 ,AUDIT_DATA_REC.AUDIT_TRANSACTION_TYPE
271 ,AUDIT_DATA_REC.AUDIT_USER_NAME
272 ,substrb(AUDIT_DATA_REC.COLUMN1_VALUE,1,240)
273 ,substrb(AUDIT_DATA_REC.COLUMN2_VALUE,1,240)
274 ,substrb(AUDIT_DATA_REC.COLUMN3_VALUE,1,240)
275 ,substrb(AUDIT_DATA_REC.COLUMN4_VALUE,1,240)
276 ,substrb(AUDIT_DATA_REC.COLUMN5_VALUE,1,240)
277 ,substrb(DISP_VAL,1,240));
278 END LOOP;
279 CLOSE AUDIT_DATA;
280 END;
281 END;
282
283