[Home] [Help]
PACKAGE BODY: APPS.WF_HA_MIGRATION
Source
1 PACKAGE BODY WF_HA_MIGRATION AS
2 /* $Header: WFHAMIGB.pls 120.2 2005/10/18 12:45:52 mfisher ship $ */
3
4 -- Cached value
5 P_ha_maint_mode varchar2(30) := Null;
6
7 --
8 -- Procedure
9 -- RESET_HA_FLAGS
10 --
11 -- Purpose
12 -- Resets the Migration Flags on WF_ITEMS. Performs Commit.
13 --
14 -- Arguments: None
15 --
16 Procedure RESET_HA_FLAGS(errbuf out nocopy varchar2, retcode out nocopy number)
17
18 is
19
20 Begin
21 errbuf := '';
22 retcode := 0;
23
24 Update WF_ITEMS
25 Set HA_MIGRATION_FLAG = NULL
26 where HA_MIGRATION_FLAG is not null;
27
28 Commit;
29
30 exception
31 when others then
32 errbuf := sqlerrm;
33 retcode := '2';
34 FND_FILE.put_line(FND_FILE.log, errbuf);
35 end;
36
37
38 --
39 -- Procedure
40 -- SET_HA_FLAG
41 --
42 -- Purpose
43 -- Sets the Migration Flag on WF_ITEMS for a particular item.
44 --
45 -- Arguments:
46 -- Item_Type, Item_Key
47 --
48 Procedure SET_HA_FLAG(x_item_type in varchar2, x_item_key in varchar2)
49
50 is
51
52 Begin
53 Update WF_ITEMS
54 Set HA_MIGRATION_FLAG = 'Y'
55 where HA_MIGRATION_FLAG is null
56 and ITEM_TYPE = x_ITEM_TYPE
57 and ITEM_KEY = x_ITEM_KEY;
58 exception
59 when others then
60 Wf_Core.Context('Wf_Ha_Migration', 'Set_Ha_Flag',
61 x_item_type, x_item_key);
62 raise;
63 end;
64
65 --
66 -- Function
67 -- GET_HA_MAINT_MODE
68 --
69 -- Purpose
70 -- Returns the Current High Availability Maintenance Mode.
71 --
72 -- Arguments: None
73 --
74 FUNCTION GET_HA_MAINT_MODE return Varchar2 is
75
76 Begin
77 P_ha_maint_mode := nvl(FND_PROFILE.VALUE_SPECIFIC('APPS_MAINTENANCE_MODE'),
78 'NORMAL');
79 return P_ha_maint_mode;
80 end;
81
82 --
83 -- Function
84 -- GET_CACHED_HA_MAINT_MODE
85 --
86 -- Purpose
87 -- Returns the Cacched High Availability Maintenance Mode if available,
88 -- other wise the current one.
89 --
90 -- Arguments: None
91 --
92 FUNCTION GET_CACHED_HA_MAINT_MODE return Varchar2 is
93
94 Begin
95 if (P_ha_maint_mode is null) then return GET_HA_MAINT_MODE;
96 else return P_ha_maint_mode;
97 end if;
98 end;
99
100
101 --
102 -- Procedure
103 -- Export Items
104 --
105 -- Purpose
106 -- Shipped updated items from WF_ITEMS and associated tables to the
107 -- maintanence system...continues until no more txns being processed on old
108 -- system, and no more backlog to process.
109 --
110 -- Arguments: None
111 --
112 PROCEDURE EXPORT_ITEMS(errbuf out nocopy varchar2, retcode out nocopy number) is
113 Done BOOLEAN := FALSE;
114 My_Mode Varchar2(30) := Null;
115 itype Varchar2(8);
116 ikey Varchar2(240);
117 kount number := 0;
118 ekount number := 0;
119 myparams wf_parameter_list_t;
120
121 Begin
122
123 errbuf := '';
124 retcode := '0';
125
126 -- Notice that these loops are constructed so that in order to fall through
127 -- the following MUST OCCUR IN ORDER: 1) use get_ha_maint mode to discover
128 -- that we are in fuzzy phase; 2) re-open intem cursor; 3) find no rows.
129 -- This is to ensure that we don't miss a last moment txn.
130
131 While (Done = FALSE) LOOP
132 My_Mode := get_ha_maint_mode;
133
134
135 -- rumor has it that there is a new db feature to only grab rows
136 -- that are not locked....we may want to use two seperate selects
137 -- here: one using the feature for pre-fuzzy fast processing and then
138 -- the one we currently use for completeness during fuzzy time.
139
140 -- We aren't using a cursor fetch loop here as we are more
141 -- worried about not holding locks than about local efficiency
142 begin
143 select ITEM_TYPE, ITEM_KEY
144 into itype, ikey
145 from WF_ITEMS
146 where HA_MIGRATION_FLAG = 'Y'
147 and rownum <2
148 for update of HA_MIGRATION_FLAG;
149
150 kount := 1;
151
152 exception
153 when no_data_found then kount := 0;
154 end;
155
156 if (kount = 1) then
157 /* clear the flag */
158 update WF_ITEMS
159 Set HA_MIGRATION_FLAG = NULL
160 where ITEM_TYPE = itype
161 and ITEM_KEY = ikey;
162
163 /* See if we are going to truncate a business event */
164 select count(*)
165 into ekount
166 from wf_item_attribute_values
167 where ITEM_TYPE = itype
168 and ITEM_KEY = ikey
169 and EVENT_VALUE is not null;
170
171 /* push the data */
172 myparams := wf_parameter_list_t();
173 wf_event.AddParameterToList(p_name =>'ECX_PARAMETER1',
174 p_value => itype,
175 p_parameterlist => myparams);
176
177 wf_event.AddParameterToList(p_name =>'ECX_PARAMETER2',
178 p_value => ikey,
179 p_parameterlist => myparams);
180
181 WF_Event.Raise(p_event_name => 'oracle.apps.wf.replay.wf.item',
182 p_event_key => SUBSTRB(itype || ':' || ikey, 1, 240),
183 p_parameters => myparams);
184
185 myparams.DELETE;
186 end if;
187
188 -- unlock the rows
189 commit;
190
191 -- Print error if necessary
192 if (ekount > 0) then
193 FND_FILE.put_line(FND_FILE.log,
194 'Warning: This version of HA doesn''t support migration of business events');
195 FND_FILE.put_line(FND_FILE.log,
196 ' WF_ITEM [' || itype || ':' || ikey || '] has been truncated.');
197 end if;
198
199 if ((kount = 0) and (My_Mode = 'DISABLED')) then
200 Done := TRUE;
201 end if;
202
203 end LOOP outer;
204
205 exception
206 when others then
207 errbuf := sqlerrm;
208 retcode := '2';
209 FND_FILE.put_line(FND_FILE.log, errbuf);
210
211 end;
212
213 --
214 -- Procedure
215 -- FixSubscriptions
216 --
217 -- Purpose
218 -- Shipped updated items from WF_ITEMS and associated tables to the
219 -- maintanence system...continues until no more txns being processed on old
220 -- system, and no more backlog to process.
221 --
222 -- Arguments:
223 -- WF_Schema in varchar2 - Schema for FND.
224 -- Clone_DBLink in varchar2 - DBLink for cloned DB.
225 --
226 PROCEDURE FixSubscriptions(WF_Schema in varchar2 default 'APPLSYS',
227 Clone_DBLink in varchar2) is
228
229 myagent sys.aq$_agent;
230 address_string varchar2(1024) := 'WF_REPLAY_IN';
231 kount number;
232 WF_Schema2 varchar2(1024);
233
234 sql_stmt varchar2(2000);
235
236 pragma AUTONOMOUS_TRANSACTION;
237
238 begin
239 /* prevent (unlikely) possibility of sql injection */
240 select count(*)
241 into kount
242 from sys.user$ t
243 where t.name = WF_Schema;
244
245 if (kount < 1) then
246 WF_Schema2 := 'INVALID_SCHEMA_THROW_ERROR';
247 else
248 WF_Schema2 := WF_Schema;
249 end if;
250
251 /* insert dummy subscriber to make sure view works */
252 myagent := sys.aq$_agent('MyDummyAgent',NULL, NULL);
253
254 DBMS_AQADM.ADD_SUBSCRIBER(queue_name =>WF_Schema2 || '.WF_REPLAY_OUT',
255 subscriber=>myagent);
256
257 /* Remove all subscribers */
258 sql_stmt := 'declare ';
259
260 sql_stmt := sql_stmt || 'CURSOR C1 is select NAME, ADDRESS, PROTOCOL ';
261 sql_stmt := sql_stmt || ' from '||WF_Schema2||'.aq$WF_REPLAY_OUT_S ';
262 sql_stmt := sql_stmt || ' where QUEUE=''WF_REPLAY_OUT'';';
263
264 sql_stmt := sql_stmt || ' begin ';
265 sql_stmt := sql_stmt || ' for c1rec in c1 loop ';
266
267 sql_stmt := sql_stmt || 'DBMS_AQADM.REMOVE_SUBSCRIBER(queue_name => ''';
268 sql_stmt := sql_stmt || WF_Schema2 || '.WF_REPLAY_OUT'', ';
269 sql_stmt := sql_stmt || 'subscriber=>sys.aq$_agent(';
270 sql_stmt := sql_stmt || 'c1rec.NAME, c1rec.ADDRESS, c1rec.PROTOCOL));';
271
272 /* turn off propogation for old subscriber if active */
273 sql_stmt := sql_stmt || ' begin ';
274 sql_stmt := sql_stmt || ' if instr(c1rec.ADDRESS,''@'') > 0 then ';
275 sql_stmt := sql_stmt || ' dbms_aqadm.unschedule_propagation (';
276 sql_stmt := sql_stmt || ' queue_name => ''' ||
277 WF_Schema2||'.WF_REPLAY_OUT'', ';
278 sql_stmt := sql_stmt || ' destination => ' ||
279 'substr(c1rec.ADDRESS, instr(c1rec.ADDRESS,''@'') + 1) ); ';
280 sql_stmt := sql_stmt || ' end if; ';
281 sql_stmt := sql_stmt || ' exception when others then null; ';
282 sql_stmt := sql_stmt || ' end; ';
283
284
285 sql_stmt := sql_stmt || ' end loop; ';
286 sql_stmt := sql_stmt || ' end;';
287
288 EXECUTE IMMEDIATE sql_stmt ;
289
290 /* just to be safe turn off local prop */
291 begin
292 dbms_aqadm.unschedule_propagation (
293 queue_name => WF_Schema2||'.WF_REPLAY_OUT',
294 destination =>null
295 );
296 exception
297 when others then
298 null;
299 end;
300
301 /* just to be safe turn off prop to new destination */
302 begin
303 if (Clone_DBLink is not null) then
304 dbms_aqadm.unschedule_propagation (
305 queue_name => WF_Schema2||'.WF_REPLAY_OUT',
306 destination =>Clone_DBLink
307 );
308 end if;
309 exception
310 when others then
311 null;
312 end;
313
314 /* turn on prop to new clone */
315 dbms_aqadm.schedule_propagation (
316 queue_name => WF_Schema2||'.WF_REPLAY_OUT',
317 destination => 'Clone_DBLink', duration => 60,
318 next_time => 'SYSDATE + 1/24/60/6');
319
320 address_string := WF_Schema2 || '.' || address_string;
321
322 if (Clone_DBLink is not null) then
323 address_string := address_string || '@' || Clone_DBLink;
324 end if;
325
326 myagent := sys.aq$_agent('WF_REPLAY_IN', address_string, 0);
327 dbms_aqadm.add_subscriber(queue_name =>WF_Schema2||'.WF_REPLAY_OUT',
328 subscriber=>myagent);
329
330 commit;
331 end;
332
333
334 END WF_HA_MIGRATION;