[Home] [Help]
PACKAGE BODY: APPS.BEN_MAINTAIN_BENEFIT_ACTIONS
Source
1 package body ben_maintain_benefit_actions as
2 /* $Header: benbmbft.pkb 120.1 2006/05/18 11:11:19 nhunur noship $ */
3 --
4 g_package varchar2(50) := 'ben_maintain_benefit_actions.';
5 --
6 PROCEDURE grab_next_batch_range
7 (p_benefit_action_id in number
8 --
9 ,p_start_person_action_id out nocopy number
10 ,p_end_person_action_id out nocopy number
11 ,p_rows_found out nocopy boolean
12 )
13 IS
14 --
15 cursor c_range_thread
16 (c_bft_id number
17 )
18 is
19 select /*+ index(ran BEN_BATCH_RANGES_CK) */
20 ran.rowid,
21 ran.starting_person_action_id,
22 ran.ending_person_action_id
23 from ben_batch_ranges ran
24 where ran.range_status_cd = 'U'
25 and ran.benefit_action_id = c_bft_id
26 and rownum < 2
27 for update of ran.range_status_cd;
28 --
29 l_rowid rowid;
30 --
31 BEGIN
32 --
33 p_rows_found := false;
34 --
35 open c_range_thread
36 (c_bft_id => p_benefit_action_id
37 );
38 fetch c_range_thread into l_rowid,
39 p_start_person_action_id,
40 p_end_person_action_id;
41 if c_range_thread%found then
42 --
43 p_rows_found := true;
44 --
45 update ben_batch_ranges ran
46 set ran.range_status_cd = 'P'
47 where ran.rowid = l_rowid;
48 --
49 end if;
50 close c_range_thread;
51 --
52 END grab_next_batch_range;
53 --
54 procedure start_slaves
55 (p_threads in number
56 ,p_num_ranges in number
57 ,p_validate in varchar2
58 ,p_benefit_action_id in number
59 ,p_effective_date in varchar2
60 ,p_pgm_id in number
61 ,p_business_group_id in number
62 ,p_pl_id in number
63 ,p_no_programs in varchar2
64 ,p_no_plans in varchar2
65 ,p_rptg_grp_id in number
66 ,p_pl_typ_id in number
67 ,p_opt_id in number
68 ,p_eligy_prfl_id in number
69 ,p_vrbl_rt_prfl_id in number
70 ,p_mode in varchar2
71 ,p_person_selection_rule_id in number
72 ,p_comp_selection_rule_id in number
73 ,p_derivable_factors in varchar2
74 ,p_cbr_tmprl_evt_flag in varchar2
75 ,p_lf_evt_ocrd_dt in varchar2
76 ,p_lmt_prpnip_by_org_flag in varchar2
77 ,p_gsp_eval_elig_flag in varchar2 default null -- GSP Rate Sync : Evaluate Eligibility
78 ,p_lf_evt_oper_cd in varchar2 default null -- GSP Rate Sync : Life Event Operation code
79 )
80 is
81 --
82 l_package varchar2(80) := g_package||'.start_slaves';
83 l_request_id number;
84 --
85 begin
86 --
87 hr_utility.set_location ('Entering '||l_package,10);
88 --
89 if p_threads > 0 then
90 --
91 for l_count in 1..least(p_threads,p_num_ranges) loop
92 --
93 hr_utility.set_location ('Submitting request '||l_package,10);
94 --
95 l_request_id := fnd_request.submit_request
96 (application => 'BEN',
97 program => 'BENTHREAD',
98 description => NULL,
99 sub_request => FALSE,
100 argument1 => p_validate,
101 argument2 => p_benefit_action_id,
102 argument3 => p_effective_date,
103 argument4 => p_pgm_id,
104 argument5 => p_business_group_id,
105 argument6 => p_pl_id,
106 -- PB : 5422 :
107 -- Temporarily uncommented.
108 argument7 => null, -- p_popl_enrt_typ_cycl_id,
109 argument8 => p_no_programs,
110 argument9 => p_no_plans,
111 argument10 => p_rptg_grp_id,
112 argument11 => p_pl_typ_id,
113 argument12 => p_opt_id,
114 argument13 => p_eligy_prfl_id,
115 argument14 => p_vrbl_rt_prfl_id,
116 argument15 => p_mode,
117 argument16 => p_person_selection_rule_id,
118 argument17 => p_comp_selection_rule_id,
119 argument18 => p_derivable_factors,
120 argument19 => l_count,
121 argument20 => p_lf_evt_ocrd_dt,
122 argument21 => p_cbr_tmprl_evt_flag,
123 argument22 => p_lmt_prpnip_by_org_flag,
124 argument23 => p_gsp_eval_elig_flag, -- GSP Rate Sync : Evaluate Eligibility
125 argument24 => p_lf_evt_oper_cd -- GSP Rate Sync : Life Event Operation code
126 );
127 --
128 -- Store the request id of the concurrent request
129 --
130 ben_maintain_benefit_actions.g_num_processes := ben_maintain_benefit_actions.g_num_processes + 1;
131 ben_maintain_benefit_actions.g_processes_rec(g_num_processes) := l_request_id;
132 --
133 hr_utility.set_location ('Submitted request '||l_package,10);
134 end loop;
135 --
136 end if;
137 --
138 hr_utility.set_location ('Leaving '||l_package,10);
139 --
140 commit;
141 --
142 end start_slaves;
143 --
144 procedure check_slaves_status
145 (p_num_processes in number
146 ,p_processes_rec in ben_maintain_benefit_actions.g_processes_table
147 ,p_master in varchar2
148 ,p_slave_errored out nocopy boolean
149 )
150 is
151 --
152 l_package varchar2(80) := g_package||'.check_slaves_status';
153 --
154 l_no_slaves boolean;
155 l_poll_loops pls_integer;
156 l_slave_errored boolean;
157 --
158 cursor c_slaves
159 (c_request_id number
160 )
161 is
162 select phase_code,
163 status_code
164 from fnd_concurrent_requests fnd
165 where fnd.request_id = c_request_id;
166 --
167 l_slaves c_slaves%rowtype;
168 --
169 begin
170 --
171 hr_utility.set_location ('Entering '||l_package,10);
172 --
173 if p_num_processes <> 0 and p_master = 'Y'
174 then
175 --
176 -- 2237993 : threads are not synchronised as without initialization
177 -- code is not being executed.
178 --
179 l_no_slaves := true;
180 while l_no_slaves loop
181 --
182 l_no_slaves := false;
183 --
184 for elenum in 1..p_num_processes
185 loop
186 --
187 open c_slaves
188 (p_processes_rec(elenum)
189 );
190 fetch c_slaves into l_slaves;
191 if l_slaves.phase_code <> 'C'
192 then
193 --
194 l_no_slaves := true;
195 --
196 end if;
197 --
198 if l_slaves.status_code = 'E' then
199 --
200 l_slave_errored := true;
201 --
202 end if;
203 --
204 close c_slaves;
205 --
206 -- Loop to avoid over polling of fnd_concurrent_requests
207 --
208 -- l_poll_loops := 100000;
209 dbms_lock.sleep(4);
210 --
211 -- for i in 1..l_poll_loops
212 -- loop
213 --
214 -- null;
215 --
216 -- end loop;
217 --
218 end loop;
219 --
220 end loop;
221 --
222 end if;
223 --
224 hr_utility.set_location ('Leaving '||l_package,10);
225 --
226 commit;
227 --
228 end check_slaves_status;
229 --
230 procedure check_all_slaves_finished
231 (p_benefit_action_id in number
232 ,p_business_group_id in number
233 ,p_slave_errored out nocopy boolean
234 )
235 is
236 --
237 l_package varchar2(80) := g_package||'.check_all_slaves_finished';
238 l_no_slaves boolean := true;
239 l_dummy varchar2(1);
240 l_master varchar2(1) := 'N';
241 l_param_rec benutils.g_batch_param_rec;
242 l_slave_errored boolean := false;
243 --
244 cursor c_master is
245 select 'Y'
246 from ben_benefit_actions bft
247 where bft.benefit_action_id = p_benefit_action_id
248 and bft.request_id = fnd_global.conc_request_id;
249 --
250 cursor c_person_actions(p_status_cd varchar2) is
251 select count(*)
252 from ben_person_actions pac
253 where pac.benefit_action_id = p_benefit_action_id
254 and pac.action_status_cd = nvl(p_status_cd,pac.action_status_cd);
255 --
256 begin
257 --
258 hr_utility.set_location ('Entering '||l_package,10);
259 --
260 -- Work out if process is master
261 --
262 open c_master;
263 --
264 fetch c_master into l_master;
265 --
266 close c_master;
267 --
268 benutils.get_batch_parameters
269 (p_benefit_action_id => p_benefit_action_id,
270 p_rec => l_param_rec);
271 --
272 -- Check slave status
273 --
274 ben_maintain_benefit_actions.check_slaves_status
275 (p_num_processes => ben_maintain_benefit_actions.g_num_processes
276 ,p_processes_rec => ben_maintain_benefit_actions.g_processes_rec
277 ,p_master => l_master
278 --
279 ,p_slave_errored => l_slave_errored
280 );
281 --
282 hr_utility.set_location (l_package||' OUT NOCOPY slave loop ',20);
283 --
284 -- Log process information
285 -- This is master specific only
286 --
287 if l_master = 'Y' then
288 --
289 ben_manage_life_events.write_bft_statistics
290 (p_business_group_id => p_business_group_id
291 ,p_benefit_action_id => p_benefit_action_id
292 );
293 --
294 end if;
295 hr_utility.set_location (l_package||' Write to file ',35);
296 --
297 benutils.write_table_and_file(p_table => true,
298 p_file => false);
299 commit;
300 --
301 -- Fire off Reports for BENMNGLE run.
302 --
303 if l_master = 'Y' then
304 --
305 -- Process Log
306 -- Activity Summary
307 -- Error by Error Type
308 -- Error by Person
309 --
310 -- Don't do this code if we are running from SQL Plus
311 --
312 -- GLOBALCWB
313 if fnd_global.conc_request_id <> -1
314 then
315 --
316 if l_param_rec.mode_cd<>'W' then
317 hr_utility.set_location (l_package||' Fire Reports ',40);
318 ben_batch_reporting.batch_reports
319 (p_concurrent_request_id => fnd_global.conc_request_id,
320 p_report_type => 'GENERIC_LOG');
321 if l_param_rec.mode_cd<>'R' then
322 ben_batch_reporting.batch_reports
323 (p_concurrent_request_id => fnd_global.conc_request_id,
324 p_mode => l_param_rec.mode_cd,
325 p_report_type => 'ACTIVITY_SUMMARY');
326 end if;
327 ben_batch_reporting.batch_reports
328 (p_concurrent_request_id => fnd_global.conc_request_id,
329 p_report_type => 'ERROR_BY_ERROR_TYPE');
330 ben_batch_reporting.batch_reports
331 (p_concurrent_request_id => fnd_global.conc_request_id,
332 p_report_type => 'ERROR_BY_PERSON');
333 hr_utility.set_location (l_package||' Dn Fire Reports ',40);
334 --
335 end if;
336 commit;
337 --
338 end if;
339 --
340 end if;
341 --
342 p_slave_errored := l_slave_errored;
343 --
344 hr_utility.set_location ('Leaving '||l_package,50);
345 --
346 end check_all_slaves_finished;
347 --
348 PROCEDURE get_peractionrange_persondets
349 (p_benefit_action_id in number
350 ,p_start_person_action_id in number
351 ,p_end_person_action_id in number
352 --
353 ,p_personid_va in out nocopy benutils.g_number_table
354 ,p_pactid_va in out nocopy benutils.g_number_table
355 ,p_pactovn_va in out nocopy benutils.g_number_table
356 ,p_lerid_va in out nocopy benutils.g_number_table
357 )
358 IS
359 --
360 l_personid_va benutils.g_number_table := benutils.g_number_table();
361 l_pactid_va benutils.g_number_table := benutils.g_number_table();
362 l_pactovn_va benutils.g_number_table := benutils.g_number_table();
363 l_lerid_va benutils.g_number_table := benutils.g_number_table();
364 --
365 cursor c_person_details
366 (c_bft_id number
367 ,c_stpact_id number
368 ,c_endpact_id number
369 )
370 is
371 select pact.person_id,
372 pact.person_action_id,
373 pact.object_version_number,
374 pact.ler_id
375 from ben_person_actions pact
376 where pact.benefit_action_id = c_bft_id
377 and pact.action_status_cd = 'U'
378 and pact.person_action_id
379 between c_stpact_id and c_endpact_id;
380 --
381 BEGIN
382 --
383 open c_person_details
384 (c_bft_id => p_benefit_action_id
385 ,c_stpact_id => p_start_person_action_id
386 ,c_endpact_id => p_end_person_action_id
387 );
388 fetch c_person_details BULK COLLECT INTO l_personid_va,
389 l_pactid_va,
390 l_pactovn_va,
391 l_lerid_va;
392 close c_person_details;
393 --
394 p_personid_va := l_personid_va;
395 p_pactid_va := l_pactid_va;
396 p_pactovn_va := l_pactovn_va;
397 p_lerid_va := l_lerid_va;
398 --
399 END get_peractionrange_persondets;
400 --
401 end ben_maintain_benefit_actions;