[Home] [Help]
PACKAGE BODY: APPS.FND_AUDIT_UPDATE_PKG
Source
1 package body FND_AUDIT_UPDATE_PKG as
2 /* $Header: fdaaddrk.pls 120.9.12020000.2 2012/07/30 20:48:50 jwsmith ship $ */
3
4 procedure FND_AUDIT_ROW_KEY(errbuf IN OUT NOCOPY varchar2, rc IN OUT NOCOPY varchar2, p_snm varchar2,p_taplid number,p_tabid number)
5 is
6
7 cursor l_is_there_row_key_csr(c_tabname varchar2, c_owner varchar2) is
8 select column_name from
9 dba_tab_columns where
10 table_name = c_tabname and
11 owner = c_owner and
12 column_name = 'ROW_KEY';
13
14 cursor l_get_owner_csr(c_tabname varchar2) is
15 select owner from
16 dba_tables where
17 table_name = c_tabname and
18 owner in (select oracle_username from fnd_oracle_userid);
19
20 cursor l_get_tab_columns_csr(c_tabname varchar2, c_owner varchar2) is
21 select column_name from dba_tab_columns where
22 column_name <> 'ROW_KEY' and column_id > 7 and
23 table_name = c_tabname and
24 owner = c_owner
25 order by column_id;
26
27 cursor l_get_base_table_name_csr(c_appid number, c_tabid number) is
28 select table_name from fnd_tables where
29 application_id=c_appid and
30 table_id = c_tabid;
31
32
33 v_CursorID NUMBER;
34 v_dummy NUMBER;
35 v_rowkey varchar2(30);
36 v_longflag varchar2(1) := 'N';
37 v_altertable varchar2(400);
38 v_tname varchar2(30);
39 v_oldtname varchar2(30);
40 v_shadname varchar2(30);
41 v_viewname varchar2(30);
42 v_owner varchar2(30);
43 v_createtable varchar2(2000);
44 v_updatetable varchar2(2000);
45 tcount number;
46 toldcount number;
47
48 v_fnd_schema fnd_oracle_userid.oracle_username%TYPE;
49 v_ddl_sql varchar2(240);
50 app_short_name varchar2(51);
51
52
53 begin
54 v_longflag := 'N';
55 v_shadname := p_snm;
56 v_oldtname := v_shadname || '_O';
57
58 open l_get_base_table_name_csr(p_taplid, p_tabid);
59 fetch l_get_base_table_name_csr into v_tname;
60 close l_get_base_table_name_csr;
61
62 open l_get_owner_csr(v_tname);
63 fetch l_get_owner_csr into v_owner;
64 close l_get_owner_csr;
65
66 open l_is_there_row_key_csr(p_snm,v_owner);
67 fetch l_is_there_row_key_csr into v_rowkey;
68 if l_is_there_row_key_csr%FOUND then
69 close l_is_there_row_key_csr;
70 else
71 /* if long tablename that needs to be truncated */
72 if (length(v_tname) > 24) then
73 v_longflag := 'Y';
74 /* truncate and append _A to get new shadow table name */
75 v_tname := substr(rtrim(v_shadname,'_A'),0,24) || '_A';
76 else
77 v_tname := v_shadname;
78 end if;
79
80 -- First get the FND schema name
81 SELECT fou.oracle_username into v_fnd_schema
82 FROM fnd_product_installations fpi,
83 fnd_oracle_userid fou,
84 fnd_application fa
85 WHERE fpi.application_id = fa.application_id
86 AND fpi.oracle_id = fou.oracle_id
87 AND fa.application_short_name = 'FND';
88
89
90 -- Get Application Short Name
91 SELECT application_short_name into app_short_name
92 FROM fnd_application
93 WHERE application_id = p_taplid;
94
95 -- Copy the Shadow Table over to saved copy
96 v_ddl_sql := 'alter table ' || v_owner ||'.' || v_shadname || ' rename to ' || v_oldtname;
97 ad_ddl.do_ddl( v_fnd_schema, app_short_name, ad_ddl.alter_table, v_ddl_sql, v_shadname);
98
99 -- Drop Associated Synonyms
100 v_ddl_sql := 'drop synonym ' || v_shadname;
101 ad_ddl.do_ddl( v_fnd_schema, app_short_name, ad_ddl.drop_synonym, v_ddl_sql, v_shadname);
102
103 -- Create Associated Synonyms
104 v_ddl_sql := 'create synonym '|| v_oldtname || ' for ' || v_owner ||'.' || v_oldtname;
105 ad_ddl.do_ddl( v_fnd_schema, app_short_name, ad_ddl.create_synonym, v_ddl_sql, v_shadname);
106
107 -- Alter Table and add the row_key
108 v_ddl_sql := 'alter table '|| v_owner ||'.'|| v_oldtname || ' add (row_key number)';
109 ad_ddl.do_ddl( v_fnd_schema, app_short_name, ad_ddl.alter_table, v_ddl_sql, v_shadname);
110
111 -- Update Table for row_key
112 v_updatetable := 'update '|| v_owner ||'.'|| v_oldtname || ' set row_key = ' ||
113 '(TO_NUMBER(TO_CHAR(AUDIT_TIMESTAMP,''YYYYMMDDHH24MISS''))'
114 || '* 100000 + MOD(AUDIT_SEQUENCE_ID,100000)) * 100000 + AUDIT_SESSION_ID';
115 execute immediate (v_updatetable);
116
117 -- Create shadow table new so row_key is in the proper place
118 v_createtable := 'create table ' || v_owner ||'.'|| v_tname || ' as (select audit_timestamp, audit_transaction_type, audit_user_name, audit_true_nulls, audit_session_id, audit_sequence_id, audit_commit_id, row_key';
119 for l_get_col_rec in l_get_tab_columns_csr(v_oldtname, v_owner) loop
120 v_createtable := v_createtable || ', ' || l_get_col_rec.column_name;
121 end loop;
122 v_createtable := v_createtable || ' from '|| v_owner ||'.'|| v_oldtname || ')';
123
124 ad_ddl.do_ddl( v_fnd_schema, app_short_name, ad_ddl.create_table, v_createtable, v_tname);
125
126 -- Error checking
127 execute immediate ('select count(*) from '|| v_owner ||'.'|| v_tname) into tcount;
128 execute immediate ('select count(*) from '|| v_owner ||'.'|| v_oldtname) into toldcount;
129
130 if (tcount = toldcount) then
131
132 -- Drop the copy of shadow table
133 v_ddl_sql := 'drop table '|| v_owner ||'.'|| v_oldtname;
134 ad_ddl.do_ddl( v_fnd_schema, app_short_name, ad_ddl.drop_table, v_ddl_sql, v_oldtname);
135
136 -- Drop the synonym to the copy of shadow table
137 v_ddl_sql := 'drop synonym '|| v_oldtname;
138 ad_ddl.do_ddl( v_fnd_schema, app_short_name, ad_ddl.drop_synonym, v_ddl_sql, v_oldtname);
139
140 if (v_longflag = 'Y') then
141 -- Drop the procedurea, triggers, views from renamed shadow table
142 v_ddl_sql := 'drop procedure '|| v_shadname || 'IP';
143 ad_ddl.do_ddl( v_fnd_schema, app_short_name, ad_ddl.drop_trigger, v_ddl_sql, v_shadname);
144
145 v_ddl_sql := 'drop procedure '|| v_shadname || 'UP';
146 ad_ddl.do_ddl( v_fnd_schema, app_short_name, ad_ddl.drop_trigger, v_ddl_sql, v_shadname);
147
148 v_ddl_sql := 'drop procedure '|| v_shadname || 'DP';
149 ad_ddl.do_ddl( v_fnd_schema, app_short_name, ad_ddl.drop_trigger, v_ddl_sql, v_shadname);
150
151 v_ddl_sql := 'drop trigger ' || v_shadname || 'D';
152 ad_ddl.do_ddl( v_fnd_schema, app_short_name, ad_ddl.drop_trigger, v_ddl_sql, v_shadname);
153
154 v_ddl_sql := 'drop trigger ' || v_shadname || 'I';
155 ad_ddl.do_ddl( v_fnd_schema, app_short_name, ad_ddl.drop_trigger, v_ddl_sql, v_shadname);
156
157 v_ddl_sql := 'drop trigger ' || v_shadname || 'U';
158 ad_ddl.do_ddl( v_fnd_schema, app_short_name, ad_ddl.drop_trigger, v_ddl_sql, v_shadname);
159
160 v_ddl_sql := 'drop trigger ' || v_shadname || 'H';
161 ad_ddl.do_ddl( v_fnd_schema, app_short_name, ad_ddl.drop_trigger, v_ddl_sql, v_shadname);
162
163 v_ddl_sql := 'drop trigger ' || v_shadname || 'T';
164 ad_ddl.do_ddl( v_fnd_schema, app_short_name, ad_ddl.drop_trigger, v_ddl_sql, v_shadname);
165
166 v_ddl_sql := 'drop trigger ' || v_shadname || 'C';
167 ad_ddl.do_ddl( v_fnd_schema, app_short_name, ad_ddl.drop_trigger, v_ddl_sql, v_shadname);
168
169 v_ddl_sql := 'drop view ' || v_shadname || 'C1';
170 v_viewname := v_shadname || 'C1';
171 ad_ddl.do_ddl( v_fnd_schema, app_short_name, ad_ddl.drop_view, v_ddl_sql, v_viewname);
172
173 v_ddl_sql := 'drop view ' || v_shadname || 'V1';
174 v_viewname := v_shadname || 'V1';
175 ad_ddl.do_ddl( v_fnd_schema, app_short_name, ad_ddl.drop_view, v_ddl_sql, v_viewname);
176
177 end if;
178 else
179 null;
180 /* add error message */
181 end if;
182 rc := '0';
183 close l_is_there_row_key_csr;
184 end if;
185
186 exception
187 when others then
188 fnd_message.set_name('FND', 'SQL-GENERIC ERROR');
189 fnd_message.set_token('ERRNO', sqlcode, FALSE);
190 fnd_message.set_token('ROUTINE', 'fnd_audit_row_key', FALSE);
191 fnd_message.set_token('REASON', sqlerrm, FALSE);
192 fnd_message.set_token('ERRFILE', 'fdaaddrk.pls', FALSE);
193 fnd_message.set_token('SQLSTMT', ad_ddl.error_buf, FALSE);
194 app_exception.raise_exception;
195
196 end FND_AUDIT_ROW_KEY;
197
198 end FND_AUDIT_UPDATE_PKG;