1 PACKAGE BODY GMA_PURGE_COPY AS
2 /* $Header: GMAPRGCB.pls 120.2.12010000.2 2008/11/11 20:55:18 srpuri ship $ */
3
4 g_copycursor INTEGER;
5
6 FUNCTION archiveengine(p_purge_id sy_purg_mst.purge_id%TYPE,
7 p_owner user_users.username%TYPE,
8 p_appl_short_name fnd_application.application_short_name%TYPE,
9 p_user NUMBER,
10 p_arcrowtablename user_tables.table_name%TYPE,
11 p_tablecount INTEGER,
12 p_tablename_tab GMA_PURGE_DDL.g_tablename_tab_type,
13 p_tableaction_tab GMA_PURGE_DDL.g_tableaction_tab_type,
14 p_debug_flag BOOLEAN,
15 p_commitfrequency INTEGER)
16 RETURN BOOLEAN IS
17 -- This FUNCTION logs, copies, deletes, commits. You name it.
18
19 l_arctablename user_tables.table_name%TYPE;
20 -- name of current archive targe table
21 l_detailcursor INTEGER; -- cursor for detail row statement
22
23 l_detailrowid ROWID; -- current detail row rowid
24
25 l_return INTEGER; -- holds number of rows returned
26
27 l_sourcetable user_tables.table_name%TYPE;
28
29 l_sqlstatement sy_purg_def.sqlstatement%TYPE;
30
31 l_transcount PLS_INTEGER; -- transaction commit counter
32
33 t_arctables_tab GMA_PURGE_DDL.g_tablename_tab_type;
34 t_arcactions_tab GMA_PURGE_DDL.g_tableaction_tab_type;
35 t_tablecount INTEGER;
36 t_idx_tablespaces_tab GMA_PURGE_DDL.g_tablespace_name_tab_type;
37 t_idx_tablespaces_count INTEGER;
38
39 pm_matl_dtl_flag boolean :=false;
40
41 BEGIN
42
43 l_transcount := 0; -- Nothin's happened yet.
44
45 -- we will be reusing these...
46 l_detailcursor := DBMS_SQL.OPEN_CURSOR;
47 g_copycursor := DBMS_SQL.OPEN_CURSOR;
48
49 -- deal with the tables, starting with the most detail and moving towards
50 -- the document master table
51
52 GMA_PURGE_UTILITIES.printlong(p_purge_id,
53 'Starting copy... ' ||
54 GMA_PURGE_UTILITIES.chartime);
55
56 -- do each table
57 FOR l_arctableno IN REVERSE 0 .. p_tablecount LOOP
58
59 l_sourcetable := p_tablename_tab(l_arctableno);
60 l_arctablename := GMA_PURGE_UTILITIES.makearcname(p_purge_id,
61 p_tablename_tab(l_arctableno));
62
63 -- create and open cursor for detail table rowids
64 -- Made by Khaja
65
66 -- No literals to change to bind variables as per coding standard,
67 -- because none of the variables are entered by user.
68
69 l_sqlstatement := 'SELECT UNIQUE rowidtochar(' ||
70 l_sourcetable ||
71 ') FROM ' ||
72 p_owner||'.'||p_arcrowtablename || --Bug#6681753
73 ' WHERE ' ||
74 l_sourcetable ||
75 ' != CHARTOROWID(' ||
76 '''' ||
77 '0' ||
78 '''' ||
79 ')';
80
81 GMA_PURGE_UTILITIES.printdebug(p_purge_id,l_sqlstatement,p_debug_flag);
82
83 DBMS_SQL.PARSE(l_detailcursor,l_sqlstatement,DBMS_SQL.NATIVE);
84 DBMS_SQL.DEFINE_COLUMN_ROWID(l_detailcursor,1,l_detailrowid);
85 l_return := DBMS_SQL.EXECUTE(l_detailcursor);
86
87 -- The following is long and hard to follow, but runs faster
88 -- than breaking it out into functions. Sorry.
89
90 -- do we want to delete rows?
91 IF p_tableaction_tab(l_arctableno) = 'D' THEN
92
93 -- This disables the constriant for only PM_MATL_DTL
94 if upper(l_sourcetable)='PM_MATL_DTL' then
95 GMA_PURGE_DDL.alterconstraints(p_purge_id,
96 t_arctables_tab,
97 t_arcactions_tab,
98 t_tablecount,
99 t_idx_tablespaces_tab,
100 t_idx_tablespaces_count,
101 p_owner,
102 'KHG',
103 'DISABLE',
104 p_debug_flag);
105 pm_matl_dtl_flag:=true;
106 end if;
107
108 -- repeat the following for each unique detail row in each detail table
109 LOOP
110
111 -- get the next value or exit loop if there isn't one
112 IF DBMS_SQL.FETCH_ROWS(l_detailcursor) <= 0 THEN
113 exit;
114 END IF;
115 DBMS_SQL.COLUMN_VALUE(l_detailcursor,1,l_detailrowid);
116
117 -- do copy
118 -- Changing literals to bind variables as per coding standard.
119 l_sqlstatement := 'INSERT INTO '|| p_owner || '.' || l_arctablename --Bug#6681753
120 || ' SELECT * FROM ' ||
121 l_sourcetable || ' WHERE ROWID = :b_detailrowid ';
122 -- l_sourcetable || ' WHERE ROWID = ' || '''' ||
123 -- ':b_detailrowid '|| '''';
124 DBMS_SQL.PARSE(g_copycursor,l_sqlstatement,DBMS_SQL.NATIVE);
125
126 -- Using bind variable, added by Khaja
127 dbms_sql.bind_variable(g_copycursor, 'b_detailrowid',l_detailrowid);
128
129 l_return := DBMS_SQL.EXECUTE(g_copycursor);
130
131 -- do delete
132 -- Changing literals to bind variables as per coding standard.
133 l_sqlstatement := 'DELETE FROM ' ||l_sourcetable ||
134 ' WHERE ROWID = :b_detailrowid ';
135 DBMS_SQL.PARSE(g_copycursor,l_sqlstatement,DBMS_SQL.NATIVE);
136
137 -- Using bind variable, added by Khaja
138 dbms_sql.bind_variable(g_copycursor, 'b_detailrowid',l_detailrowid);
139
140 l_return := DBMS_SQL.EXECUTE(g_copycursor);
141
142 -- check to see if we need to do commit
143 l_transcount := l_transcount + 1;
144 IF (l_transcount >= p_commitfrequency) THEN
145 GMA_PURGE_COPY.docommit(p_purge_id,l_transcount);
146 END IF;
147
148 END LOOP; -- detail rows
149
150
151 ELSE
152 -- repeat the following for each unique detail row in each detail table
153 LOOP
154
155 -- get the next value or exit loop if there isn't one
156 IF DBMS_SQL.FETCH_ROWS(l_detailcursor) <= 0 THEN
157 exit;
158 END IF;
159 DBMS_SQL.COLUMN_VALUE(l_detailcursor,1,l_detailrowid);
160
161 -- do copy
162 -- Changing literals to bind variables as per coding standard.
163 l_sqlstatement := 'INSERT INTO ' || p_owner || '.' || l_arctablename || --Bug#6681753
164 ' SELECT * FROM ' ||
165 l_sourcetable || ' WHERE ROWID =:b_detailrowid ';
166 DBMS_SQL.PARSE(g_copycursor,l_sqlstatement,DBMS_SQL.NATIVE);
167
168 -- Using bind variable, added by Khaja
169 dbms_sql.bind_variable(g_copycursor, 'b_detailrowid',l_detailrowid);
170
171 l_return := DBMS_SQL.EXECUTE(g_copycursor);
172
173 -- check to see if we need to do commit
174 l_transcount := l_transcount + 1;
175 IF (l_transcount >= p_commitfrequency) THEN
176 GMA_PURGE_COPY.docommit(p_purge_id,l_transcount);
177 END IF;
178
179 END LOOP; -- detail rows
180
181 END IF;
182
183 -- logical unit of work commit
184 GMA_PURGE_COPY.docommit(p_purge_id,l_transcount);
185
186 END LOOP; -- table loop
187
188 -- close 'em up, turn off the lights
189 DBMS_SQL.CLOSE_CURSOR(l_detailcursor);
190 DBMS_SQL.CLOSE_CURSOR(g_copycursor);
191
192 -- This enables the constriant for only PM_MATL_DTL
193 -- if upper(l_sourcetable)='PM_MATL_DTL' then
194 if pm_matl_dtl_flag then
195 GMA_PURGE_DDL.alterconstraints(p_purge_id,
196 t_arctables_tab,
197 t_arcactions_tab,
198 t_tablecount,
199 t_idx_tablespaces_tab,
200 t_idx_tablespaces_count,
201 p_owner,
202 'KHG',
203 'ENABLE',
204 p_debug_flag);
205 end if;
206 RETURN TRUE;
207
208 EXCEPTION
209
210 WHEN OTHERS THEN
211 GMA_PURGE_UTILITIES.printlong(p_purge_id,
212 'Problem raised in GMA_PURGE_COPY.archiveengine.');
213 GMA_PURGE_UTILITIES.printlong(p_purge_id,
214 'Unhandled EXCEPTION - ' || sqlerrm);
215 RAISE;
216
217 END archiveengine;
218
219 /***********************************************************/
220
221 PROCEDURE docommit(p_purge_id sy_purg_mst.purge_id%TYPE,
222 p_transcount IN OUT NOCOPY INTEGER) IS
223 -- commit changes to database, restart commit counter
224 BEGIN
225
226 COMMIT;
227
228 p_transcount := 0;
229
230 EXCEPTION
231
232 WHEN OTHERS THEN
233 GMA_PURGE_UTILITIES.printlong(p_purge_id,
234 'Problem raised in GMA_PURGE_COPY.docommit.');
235 GMA_PURGE_UTILITIES.printlong(p_purge_id,
236 'Unhandled EXCEPTION - ' || sqlerrm);
237 RAISE;
238
239 END docommit;
240
241 END GMA_PURGE_COPY;
[Home] [Help]