[Home] [Help]
PACKAGE BODY: APPS.FND_AUDIT_UPDATE_PKG
Source
1 package body FND_AUDIT_UPDATE_PKG as
2 /* $Header: fdaaddrk.pls 120.5 2007/01/25 17:32:26 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_owner varchar2(30);
42 v_createtable varchar2(2000);
43 v_updatetable varchar2(2000);
44 tcount number;
45 toldcount number;
46
47 begin
48 v_longflag := 'N';
49 v_shadname := p_snm;
50 v_oldtname := v_shadname || '_O';
51
52 open l_get_base_table_name_csr(p_taplid, p_tabid);
53 fetch l_get_base_table_name_csr into v_tname;
54 close l_get_base_table_name_csr;
55
56 open l_get_owner_csr(v_tname);
57 fetch l_get_owner_csr into v_owner;
58 close l_get_owner_csr;
59
60 open l_is_there_row_key_csr(p_snm,v_owner);
61 fetch l_is_there_row_key_csr into v_rowkey;
62 if l_is_there_row_key_csr%FOUND then
63 close l_is_there_row_key_csr;
64 else
65 /* if long tablename that needs to be truncated */
66 if (length(v_tname) > 24) then
67 v_longflag := 'Y';
68 /* truncate and append _A to get new shadow table name */
69 v_tname := substr(rtrim(v_shadname,'_A'),0,24) || '_A';
70 else
71 v_tname := v_shadname;
72 end if;
73 execute immediate('alter table ' || v_owner ||'.' || v_shadname || ' rename to ' || v_oldtname);
74 execute immediate('drop synonym ' || v_shadname);
75 execute immediate('create synonym '|| v_oldtname || ' for ' || v_owner ||'.' || v_oldtname);
76 execute immediate('alter table '|| v_owner ||'.'|| v_oldtname || ' add (row_key number)');
77 v_updatetable := 'update '|| v_owner ||'.'|| v_oldtname || ' set row_key = ' ||
78 '(TO_NUMBER(TO_CHAR(AUDIT_TIMESTAMP,''YYYYMMDDHH24MISS''))'
79 || '* 100000 + MOD(AUDIT_SEQUENCE_ID,100000)) * 100000 + AUDIT_SESSION_ID';
80 execute immediate (v_updatetable);
81 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';
82 for l_get_col_rec in l_get_tab_columns_csr(v_oldtname, v_owner) loop
83 v_createtable := v_createtable || ', ' || l_get_col_rec.column_name;
84 end loop;
85 v_createtable := v_createtable || ' from '|| v_owner ||'.'|| v_oldtname || ')';
86 execute immediate (v_createtable);
87 execute immediate ('select count(*) from '|| v_owner ||'.'|| v_tname) into tcount;
88 execute immediate ('select count(*) from '|| v_owner ||'.'|| v_oldtname) into toldcount;
89 if (tcount = toldcount) then
90 execute immediate ('drop table '|| v_owner ||'.'|| v_oldtname);
91 execute immediate ('drop synonym '|| v_oldtname);
92 execute immediate ('create synonym '|| v_tname || ' for ' || v_owner ||'.' || v_tname);
93 if (v_longflag = 'Y') then
94 execute immediate ('drop procedure ' || v_shadname || 'IP');
95 execute immediate ('drop procedure ' || v_shadname || 'UP');
96 execute immediate ('drop procedure ' || v_shadname || 'DP');
97 execute immediate ('drop trigger ' || v_shadname || 'D');
98 execute immediate ('drop trigger ' || v_shadname || 'I');
99 execute immediate ('drop trigger ' || v_shadname || 'U');
100 execute immediate ('drop view ' || v_shadname || 'C1');
101 execute immediate ('drop view ' || v_shadname || 'V1');
102 end if;
103 else
104 null;
105 /* add error message */
106 end if;
107 rc := '0';
108 close l_is_there_row_key_csr;
109 end if;
110 end FND_AUDIT_ROW_KEY;
111
112 end FND_AUDIT_UPDATE_PKG;