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