[Home] [Help]
PACKAGE BODY: APPS.AME_MIGRATE_PARALLEL_CONFIG
Source
1 package BODY ame_migrate_parallel_config as
2 /* $Header: amemigcfg.pkb 120.3 2006/12/26 13:03:53 avarri noship $ */
3 procedure log_message
4 (p_package varchar2
5 ,p_routine varchar2
6 ,p_message varchar2
7 ,p_errorcode integer default -20002
8 ) as
9 l_log_id integer;
10 begin
11 select ame_exceptions_log_s.nextval
12 into l_log_id
13 from dual;
14
15 insert into ame_exceptions_log
16 (log_id,package_name,routine_name,transaction_id,application_id,exception_number,exception_string)
17 values
18 (l_log_id,p_package,p_routine,'','',p_errorcode,to_char(sysdate, 'YYYY:MM:DD:HH24:MI:SS')|| p_message);
19 end log_message;
20
21 procedure migrate_approval_group_config
22 (errbuf out nocopy varchar2
23 ,retcode out nocopy number
24 ) as
25
26 cursor unused_group_config_cursor is
27 select aagc.application_id,
28 aagc.approval_group_id
29 from ame_approval_group_config aagc
30 where sysdate between aagc.start_date
31 and nvl(aagc.end_date - (1/86400),sysdate)
32 and not exists
33 (select null
34 from ame_action_usages aau,
35 ame_rule_usages aru,
36 ame_actions aa,
37 ame_action_types aat
38 where aa.action_id = aau.action_id
39 and aau.rule_id = aru.rule_id
40 and aru.item_id = aagc.application_id
41 and sysdate between aa.start_date
42 and nvl(aa.end_date - (1/86400),sysdate)
43 and sysdate between aat.start_date
44 and nvl(aat.end_date - (1/86400),sysdate)
45 and aa.parameter = to_char(aagc.approval_group_id)
46 and aat.action_type_id = aa.action_type_id
47 and aat.name in ('pre-chain-of-authority approvals'
48 ,'post-chain-of-authority approvals'
49 ,'approval-group chain of authority')
50 and rownum < 2)
51 order by aagc.application_id;
52
53 l_application_id integer;
54 l_application_name varchar2(240);
55 l_approval_group_id integer;
56 l_approval_group_name varchar2(50);
57 l_group_count integer;
58 l_string varchar2(300);
59 l_migration_date date;
60 begin
61 log_message('ame_migrate_parallel_config'
62 ,'migrate_approval_group_config'
63 ,'Approval Group Config Migration');
64 l_migration_date := sysdate;
65 log_message('ame_migrate_parallel_config'
66 ,'migrate_approval_group_config'
67 ,'Migration date:' || to_char(l_migration_date,'RRRR:MM:DD:HH24:MI:SS'));
68 log_message('ame_migrate_parallel_config'
69 ,'migrate_approval_group_config'
70 ,rpad('=',102,'='));
71 l_group_count := 0;
72 open unused_group_config_cursor;
73 loop
74 fetch unused_group_config_cursor
75 into l_application_id,
76 l_approval_group_id;
77
78 exit when unused_group_config_cursor%notfound;
79
80 update ame_approval_group_config master_cfg
81 set master_cfg.end_date = l_migration_date
82 where sysdate between master_cfg.start_date
83 and nvl(master_cfg.end_date - (1/86400),sysdate)
84 and master_cfg.application_id = l_application_id
85 and master_cfg.approval_group_id = l_approval_group_id;
86
87 if lengthb(to_char(l_application_id)) < 50 then
88 l_string := rpad(to_char(l_application_id),50,' ');
89 else
90 l_string := to_char(l_application_id);
91 end if;
92
93 l_string := l_string || '| ' || rpad(to_char(l_approval_group_id),50,' ');
94 log_message('ame_migrate_parallel_config'
95 ,'migrate_approval_group_config'
96 ,l_string);
97
98 l_group_count := l_group_count + 1;
99 end loop;
100 close unused_group_config_cursor;
101
102 log_message('ame_migrate_parallel_config'
103 ,'migrate_approval_group_config'
104 ,rpad('=',102,'='));
105 log_message('ame_migrate_parallel_config'
106 ,'migrate_approval_group_config'
107 ,'Migration Completed Successfully');
108 log_message('ame_migrate_parallel_config'
109 ,'migrate_approval_group_config'
110 ,'Total ' || l_group_count || ' configurations migrated');
111 log_message('ame_migrate_parallel_config'
112 ,'migrate_approval_group_config'
113 ,'Migration completion date:' || to_char(sysdate,'RRRR:MM:DD:HH24:MI:SS'));
114 commit;
115 retcode := 0;
116 errbuf := 'Approval Groups Migrated Successfully';
117 exception
118 when others then
119 rollback;
120 if unused_group_config_cursor%isopen then
121 close unused_group_config_cursor;
122 log_message('ame_migrate_parallel_config'
123 ,'migrate_approval_group_config'
124 ,'Failed during migration of ' ||
125 l_application_name ||
126 ',' ||
127 l_approval_group_name);
128 end if;
129 log_message('ame_migrate_parallel_config'
130 ,'migrate_approval_group_config'
131 ,'Approval Group Migration Failed');
132 log_message('ame_migrate_parallel_config'
133 ,'migrate_approval_group_config'
134 ,'Cause:' || sqlerrm);
135 retcode := 1;
136 errbuf := 'Approval Groups Migration Failed';
137 end migrate_approval_group_config;
138
139 procedure migrate_action_type_config
140 (errbuf out nocopy varchar2
141 ,retcode out nocopy number
142 ) as
143
144 cursor unused_atype_config_cursor is
145 select aatc.application_id,
146 aatc.action_type_id
147 from ame_action_type_config aatc
148 where sysdate between aatc.start_date
149 and nvl(aatc.end_date - (1/86400),sysdate)
150 and not exists
151 (select /*+ use_nl (aa aru) */ null
152 from ame_actions aa,
153 ame_action_usages aau,
154 ame_rule_usages aru
155 where sysdate between aa.start_date
156 and nvl(aa.end_date - (1/86400),sysdate)
157 and aa.action_id = aau.action_id
158 and aau.rule_id = aru.rule_id
159 and aru.item_id = aatc.application_id
160 and aa.action_type_id = aatc.action_type_id
161 and rownum < 2)
162 order by aatc.application_id;
163
164 l_application_id integer;
165 l_application_name varchar2(240);
166 l_action_type_id integer;
167 l_action_type_name varchar2(50);
168 l_atype_count integer;
169 l_string varchar2(300);
170 l_migration_date date;
171 begin
172 log_message('ame_migrate_parallel_config'
173 ,'migrate_action_type_config'
174 ,'Action Type Config Migration');
175 l_migration_date := sysdate;
176 log_message('ame_migrate_parallel_config'
177 ,'migrate_action_type_config'
178 ,'Migration date:' || to_char(l_migration_date,'RRRR:MM:DD:HH24:MI:SS'));
179 log_message('ame_migrate_parallel_config'
180 ,'migrate_action_type_config'
181 ,rpad('=',102,'='));
182
183 l_atype_count := 0;
184 open unused_atype_config_cursor;
185 loop
186 fetch unused_atype_config_cursor
187 into l_application_id,
188 l_action_type_id;
189
190 exit when unused_atype_config_cursor%notfound;
191
192 update ame_action_type_config master_cfg
193 set master_cfg.end_date = l_migration_date
194 where sysdate between master_cfg.start_date
195 and nvl(master_cfg.end_date - (1/86400),sysdate)
196 and master_cfg.application_id = l_application_id
197 and master_cfg.action_type_id = l_action_type_id;
198
199 if lengthb(to_char(l_application_id)) < 50 then
200 l_string := rpad(to_char(l_application_id),50,' ');
201 else
202 l_string := to_char(l_application_id);
203 end if;
204
205 l_string := l_string || '| ' || rpad(to_char(l_action_type_id),50,' ');
206 log_message('ame_migrate_parallel_config'
207 ,'migrate_action_type_config'
208 ,l_string);
209
210 l_atype_count := l_atype_count + 1;
211 end loop;
212 close unused_atype_config_cursor;
213
214 log_message('ame_migrate_parallel_config'
215 ,'migrate_action_type_config'
216 ,rpad('=',102,'='));
217 log_message('ame_migrate_parallel_config'
218 ,'migrate_action_type_config'
219 ,'Migration Completed Successfully');
220 log_message('ame_migrate_parallel_config'
221 ,'migrate_action_type_config'
222 ,'Total ' || l_atype_count || ' configurations migrated');
223 log_message('ame_migrate_parallel_config'
224 ,'migrate_action_type_config'
225 ,'Migration completion date:' || to_char(sysdate,'RRRR:MM:DD:HH24:MI:SS'));
226 -- Set the chain ordering mode for the action type configurations of
227 -- those action types for which it is not applicable.
228 update ame_action_type_config acf
229 set chain_ordering_mode = null
230 ,voting_regime = null
231 where (chain_ordering_mode is not null or
232 voting_regime is not null)
233 and action_type_id not in
234 (select action_type_id
235 from ame_action_type_usages
236 where rule_type = ame_util.authorityRuleType
237 and sysdate between start_date and nvl(end_date,sysdate)
238 )
239 and sysdate between start_date and nvl(end_date,sysdate);
240 commit;
241 retcode := 0;
242 errbuf := 'Action Types Migrated Successfully';
243 exception
244 when others then
245 rollback;
246 if unused_atype_config_cursor%isopen then
247 close unused_atype_config_cursor;
248 log_message('ame_migrate_parallel_config'
249 ,'migrate_action_type_config'
250 ,'Failed during migration of ' ||
251 l_application_name ||
252 ',' ||
253 l_action_type_name);
254 end if;
255 log_message('ame_migrate_parallel_config'
256 ,'migrate_action_type_config'
257 ,'Action Types Migration Failed');
258 log_message('ame_migrate_parallel_config'
259 ,'migrate_action_type_config'
260 ,'Cause:' || sqlerrm);
261 retcode := 1;
262 errbuf := 'Action Types Migration Failed';
263 end migrate_action_type_config;
264
265 procedure migrate_parallel_config
266 (errbuf out nocopy varchar2
267 ,retcode out nocopy number
268 ) as
269 actresult integer;
270 grpresult integer;
271 grperrbuf varchar2(100);
272 acterrbuf varchar2(100);
273 begin
274 migrate_approval_group_config
275 (retcode => grpresult
276 ,errbuf => grperrbuf
277 );
278
279 migrate_action_type_config
280 (retcode => actresult
281 ,errbuf => acterrbuf
282 );
283
284 if grpresult <> 0 and
285 actresult <> 0 then
286 retcode := 1;
287 errbuf := 'Parallelization Config Migration failed for both Groups and Action Types';
288 elsif grpresult <> 0 then
289 retcode := 1;
290 errbuf := 'Parallelization Config Migration failed for Groups';
291 elsif actresult <> 0 then
292 retcode := 1;
293 errbuf := 'Parallelization Config Migration failed for Action Types';
294 else
295 retcode := 0;
296 errbuf := 'Successfully Completed the Parallelization Config Migration';
297 end if;
298 end migrate_parallel_config;
299
300 end ame_migrate_parallel_config;