1 package body BEN_EXT_UTIL as
2 /* $Header: benxutil.pkb 120.13.12010000.2 2008/08/05 15:01:54 ubhat ship $ */
3 --------------------------------------------------------------------------------
4 /*
5 +==============================================================================+
6 | Copyright (c) 1997 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +==============================================================================+
10 Name:
11 Extract Utility.
12 Purpose:
13 This is used for utility style processes for the Benefits Extract System.
14 History:
15 Date Who Version What?
16 ---- --- ------- -----
17 24 Oct 98 Ty Hayden 115.0 Created.
18 26 Oct 98 Ty Hayden 115.1 Added request_id as input.
19 04 Feb 99 Pulak Das 115.2 Added procedure
20 get_rec_nam_num,
21 get_rec_statistics,
22 get_per_statistics,
23 get_err_warn_statitics.
24 08 Feb 99 Pulak Das 115.3 Added procedure
25 get_statistics_text
26 Added function
27 get_value (from benxsttl.pkb).
28 10 Feb 99 Pulak Das 115.4 Modified procedure
29 get_statistics_text
30 10 Feb 99 Pulak Das 115.5 Modified procedure
31 get_per_statistics
32 15 Feb 99 Ty Hayden 115.6 Added ff function
33 get_extract_value
34 19 Feb 99 Pulak Das 115.7 Modified get_value procedure
35 03 Mar 99 Siok Tee 115.8 Removed dbms_output.put_line.
36 09 Mar 99 Ty Hayden 115.9 Removed CHR statements.
37 13 May 99 I Sen 115.10 Added calc_ext_date function
38 (earlier in benxthrd)
39 16 Jun 99 I Sen 115.11 Added foreign key ref ext_rslt_id
40 01 Jul 99 Ty Hayden 115.12 Added coverage amt to get_extract_value
41 06 Aug 99 Asen 115.13 Added messages : Entering, Exiting.
42 27 Aug 99 Ty Hayden 115.14 Changed get_extract_val substr nums.
43 02 Sep 99 Ty Hayden 115.15 Added get_chg_dates.
44 03 Sep 99 I Sen 115.16 Changed user entered date to MM/DD/YYYY
45 13 Sep 99 Ty Hayden 115.17 Added get_cm_dates.
46 10 Oct 99 Ty Hayden 115.18 Changed positions for get extract val.
47 12 Oct 99 Ty Hayden 115.19 Changed positions for get extract val.
48 i3 Nov 99 Ty Hayden 115.20 Added new comm and chg date codes.
49 11 Nov 99 Ty Hayden 115.21 Added get_ext_dates.
50 30 Dec 99 Ty Hayden 115.22 Remove get_extract_value.
51 12 Feb 00 Ty Hayden 115.23 Added 18MA.
52 24 Feb 00 Ty Hayden 115.24 Change default of person and benefits date
53 01 Mar 00 P Clark 115.25 Changed line length of l_text in
54 get_statistics_text. ref bug 1209782.
55 06 Mar 00 P Clark 115.26 Changed procedure get_rec_nam_num
56 to order by record number and return
57 names with no rslt_dtl_id's.
58 Ref bug 1219126.
59 27 Sep 00 Ty Hayden 115.28 Change DAED and DARD logic.
60 29 Sep 00 Tilak 115.29 New hr Lookup code addeed bug 1409185
61 02 oct 00 tilak 115.30 new hr lookup added - next-curr-prev 16th bug 1380732
62 30 jan 01 tilak 115.31 1579767 error message changed
63 09 mar 01 tilak 115.32 bug : 1550072 date codes added
64 24 mar 01 tilak 115.33 error message substr for set_location
65 14 jun 01 tilak 115.34 current swmi month satrt date and end date
66 calcualtion added 1831651
67 04 jul 01 tilak 115.53 PM15 - 15 of previous month date code added
68 13 jul 01 tilak 115.54 CM15 Corrected
69 23 jul 01 tilak 115.55 whne error log created , global person id is defaulted
70 13 Mar 02 ikasire 115.38 UTF8 Changes for BEN
71 14-mar-02 ikasire 115.39 dbdrv
72 16-may-02 tjesumi 115.40 date override criteria TDRASG added for full profile
73 2376285
74 28-Sug-02 tjesumic 115.41 ANSI Extract , form is not supporting more then
75 2000 so get_statistics_text return only 2000 char
76 24-Dec-02 bmanyam 115.42 NOCOPY Changes
77 17-May-04 hmani 115.43 Added assignment_type = 'E' condition - Bug 3629576
78 19-Oct-04 tjesumic 115.44 FDO2PM , LDO2PM added for dt calcaultion
79 15-Dec-04 tjesumic 115.45 pl_pl_id added to calc_Ext_dates
80 22-MAr-05 tjesumic 115.45 CWB (CW) date determination added
81 20-Oct-2005 tjesumic 115.47 warning validates numer and message for the uniquness
82 the same error with element name could appear for a person
83 this fix dispalys all the warnings
84 01-Feb-06 tjesumic 115.48 date override criteria TDPRASG added for full profile
85 06-Feb-06 tjesumic 115.47 messages uniqness validated for warnings , new extract status code
86 'W' added
87 31-Oct-06 tjesumic 115.48 for performance person_dt and benefit dt code are cached in benxtrct pkg
88 pre-req benxrct.pkh/pkb 32/57
89 Entries_affected procedure moved from pqp to ben. Need pkh 115.15
90 10-Nov-06 tjesumic 115.49 Performance fix for Entries_affected. the values are cached
91 12-Feb-07 tjesumic 115.50 DBED and DBRD added for previous extract date
92 02-Mar-07 tjesumic 115.53 Date code calcualtion chnaged. TDRASG and TDPRASG calcualted for all type
93 15-Mar-07 tjesumic 115.55 the lenght is creating issue with japan customer so get_stat lengh changed
94 to lengthb
95 30-Apr-08 vkodedal 115.60 entries_affected - added one parameter for penserver
96 --------------------------------------------------------------------------------
97 */
98 -- package locak globals
99 -- globals used for entries_affected
100 TYPE t_number IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
101 TYPE t_varchar2 IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
102
103 TYPE g_r_element_entries IS RECORD
104 (
105 element_entry_id t_number
106 ,datetracked_event_id t_number
107 );
108
109 TYPE t_event_element IS table of varchar2(1) INDEX BY BINARY_INTEGER;
110 TYPE t_tab_of_collection IS TABLE OF t_number INDEX BY BINARY_INTEGER;
111
112 g_t_event_element t_event_element ;
113 g_eg_has_purge_dte t_number;
114 g_ele_set_ids_on_eg t_tab_of_collection;
115 g_datetraced_event_ids t_tab_of_collection;
116
117
118
119 ---
120
121 PROCEDURE write_err
122 (p_err_num in number default null,
123 p_err_name in varchar2 default null,
124 p_typ_cd in varchar2 default null,
125 p_person_id in number default null,
126 p_request_id in number default null,
127 p_business_group_id in number default null,
128 p_ext_rslt_id in number default null)
129 IS
130 --
131 l_ext_rslt_err_id number;
132 l_object_version_number number;
133 l_dummy varchar2(1);
134 l_proc varchar2(72) := g_package||'.write_err';
135 --
136 cursor c_xre is
137 select 'x'
138 from ben_ext_rslt_err xre
139 where xre.ext_rslt_id = p_ext_rslt_id --xre.request_id = fnd_global.conc_request_id
140 and xre.person_id = p_person_id
141 and xre.err_num = p_err_num
142 and ( /*p_typ_cd <> 'W'
143 or*/ p_err_name is null
144 or p_err_name = xre.err_txt
145 ) ;
146
147 --
148 BEGIN
149 --
150 hr_utility.set_location('Entering'||l_proc, 5);
151 --
152 open c_xre;
153 fetch c_xre into l_dummy;
154 hr_utility.set_location('error msg '||substr(p_err_name,1,100), 99.96 );
155 if c_xre%notfound then -- only write once.
156 ben_ext_rslt_err_api.create_ext_rslt_err
157 (p_validate => FALSE,
158 p_ext_rslt_err_id => l_ext_rslt_err_id,
159 p_err_num => p_err_num,
160 p_err_txt => p_err_name,
161 p_typ_cd => p_typ_cd,
162 p_person_id => nvl(p_person_id,ben_ext_person.g_person_id),
163 p_business_group_id => p_business_group_id,
164 p_ext_rslt_id => p_ext_rslt_id,
165 p_object_version_number => l_object_version_number,
166 p_request_id => nvl(p_request_id,fnd_global.conc_request_id),
167 p_program_application_id => fnd_global.prog_appl_id,
168 p_program_id => fnd_global.conc_program_id,
169 p_program_update_date => sysdate,
170 p_effective_date => sysdate
171 );
172 end if;
173 --
174 hr_utility.set_location('Exiting'||l_proc, 15);
175 --
176 END WRITE_ERR;
177 --
178 --
179 -- This procedure will return a data structure containing the name and number
180 -- of all extracted records corresponding to a ext_rslt_id or request_id or both.
181 -- If no records are found then one record with value null, 0 will be returned.
182 -- If both ext_rslt_id and request_id are passed and if they do not correspond
183 -- to each other then one record with value null, 0 will be returned.
184 --
185 Procedure get_rec_nam_num
186 (p_ext_rslt_id in number default null
187 ,p_request_id in number default null
188 ,p_rec_tab out nocopy g_rec_nam_num_tab_typ
189 ) is
190 --
191 --Modified cursor version 115.26
192 CURSOR csr_get_rec_tot_rslt IS
193 SELECT r.name name,
194 count(d.ext_rslt_dtl_id) count
195 FROM ben_ext_rslt_dtl d,
196 ben_ext_rcd r,
197 ben_ext_rcd_in_file f,
198 ben_ext_rslt rs,
199 ben_ext_dfn df
200 WHERE d.ext_rslt_id(+) = p_ext_rslt_id
201 AND f.ext_rcd_id = r.ext_rcd_id
202 AND r.ext_rcd_id = d.ext_rcd_id (+)
203 AND f.ext_file_id = df.ext_file_id
204 AND df.ext_dfn_id = rs.ext_dfn_id
205 AND rs.ext_rslt_id = p_ext_rslt_id
206 GROUP BY r.name, f.seq_num, f.ext_rcd_id
207 ORDER BY f.seq_num;
208 --
209 --Modified cursor version 115.26
210 CURSOR csr_get_rec_tot_both IS
211 SELECT r.name name,
212 count(d.ext_rslt_dtl_id) count
213 FROM ben_ext_rslt_dtl d,
214 ben_ext_rcd r,
215 ben_ext_rcd_in_file f,
216 ben_ext_rslt rs,
217 ben_ext_dfn df
218 WHERE d.ext_rslt_id(+) = p_ext_rslt_id
219 AND d.request_id(+) = p_request_id
220 AND f.ext_rcd_id = r.ext_rcd_id
221 AND r.ext_rcd_id = d.ext_rcd_id (+)
222 AND f.ext_file_id = df.ext_file_id
223 AND df.ext_dfn_id = rs.ext_dfn_id
224 AND rs.ext_rslt_id = p_ext_rslt_id
225 GROUP BY r.name, f.seq_num, f.ext_rcd_id
226 ORDER BY f.seq_num;
227 --
228 cursor csr_get_rec_tot_req is
229 SELECT b.name rec_name
230 ,count(ext_rslt_dtl_id)
231 FROM ben_ext_rslt_dtl a
232 ,ben_ext_rcd b
233 WHERE a.ext_rcd_id = b.ext_rcd_id
234 AND a.ext_rslt_id = p_ext_rslt_id --a.request_id = p_request_id
235 GROUP BY b.name
236 ORDER BY upper(b.name);
237 --
238 --New cursor version 115.26
239 CURSOR csr_get_count(p_name in varchar2)IS
240 SELECT count(r.name) count
241 FROM ben_ext_rcd r,
242 ben_ext_rcd_in_file f,
243 ben_ext_rslt rs,
244 ben_ext_dfn df
245 WHERE f.ext_rcd_id = r.ext_rcd_id
246 AND f.ext_file_id = df.ext_file_id
247 AND df.ext_dfn_id = rs.ext_dfn_id
248 AND rs.ext_rslt_id = p_ext_rslt_id
249 AND r.name = p_name
250 GROUP BY r.name;
251 --
252 l_counter number := 0;
253 l_count number;
254 l_name ben_ext_rcd.name%type;
255 l_num number;
256 l_proc varchar2(72) := g_package||'.get_rec_nam_num';
257 --
258 begin
259 --
260 hr_utility.set_location('Entering'||l_proc, 5);
261 --
262 if p_ext_rslt_id is null and p_request_id is null then
263 p_rec_tab(1).name := null;
264 p_rec_tab(1).num := 0;
265 elsif p_ext_rslt_id is not null and p_request_id is null then
266 open csr_get_rec_tot_rslt;
267 loop
268 fetch csr_get_rec_tot_rslt into l_name, l_num;
269 exit when csr_get_rec_tot_rslt%notfound;
270 open csr_get_count(p_name => l_name);
271 fetch csr_get_count into l_count;
272 close csr_get_count;
273 l_counter := l_counter + 1;
274 p_rec_tab(l_counter).name := l_name;
275 p_rec_tab(l_counter).num := l_num/l_count;
276 end loop;
277 close csr_get_rec_tot_rslt;
278 if l_counter = 0 then
279 p_rec_tab(1).name := null;
280 p_rec_tab(1).num := 0;
281 end if;
282 elsif p_ext_rslt_id is null and p_request_id is not null then
283 open csr_get_rec_tot_req;
284 loop
285 fetch csr_get_rec_tot_req into l_name, l_num;
286 exit when csr_get_rec_tot_req%notfound;
287 l_counter := l_counter + 1;
288 p_rec_tab(l_counter).name := l_name;
289 p_rec_tab(l_counter).num := l_num;
290 end loop;
291 close csr_get_rec_tot_req;
292 if l_counter = 0 then
293 p_rec_tab(1).name := null;
294 p_rec_tab(1).num := 0;
295 end if;
296 elsif p_ext_rslt_id is not null and p_request_id is not null then
297 open csr_get_rec_tot_both;
298 loop
299 fetch csr_get_rec_tot_both into l_name, l_num;
300 exit when csr_get_rec_tot_both%notfound;
301 open csr_get_count(p_name => l_name);
302 fetch csr_get_count into l_count;
303 close csr_get_count;
304 l_counter := l_counter + 1;
305 p_rec_tab(l_counter).name := l_name;
306 p_rec_tab(l_counter).num := l_num/l_count;
307 end loop;
308 close csr_get_rec_tot_both;
309 if l_counter = 0 then
310 p_rec_tab(1).name := null;
311 p_rec_tab(1).num := 0;
312 end if;
313 end if;
314 --
315 hr_utility.set_location('Exiting'||l_proc, 15);
316 --
317 --
318 end get_rec_nam_num;
319 --
320 --
321 -- This procedure will return total header records, total detail records,
322 -- total trailer records corresponding to a ext_rslt_id or request_id or both.
323 -- If both ext_rslt_id and request_id are passed and if they do not correspond
324 -- to each other then 0, 0, 0 will be returned.
325 --
326 procedure get_rec_statistics
327 (p_ext_rslt_id in number default null
328 ,p_request_id in number default null
329 ,p_header_rec out nocopy number
330 ,p_detail_rec out nocopy number
331 ,p_trailer_rec out nocopy number
332 ) is
333 --
334 l_proc varchar2(72) := g_package||'.get_rec_statistics';
335 --
336 cursor csr_get_rec_statistics_rslt is
337 SELECT count(decode(b.rcd_type_cd, 'H', b.rcd_type_cd))
338 ,count(decode(b.rcd_type_cd, 'D', b.rcd_type_cd))
339 ,count(decode(b.rcd_type_cd, 'T', b.rcd_type_cd))
340 FROM ben_ext_rslt_dtl a
341 ,ben_ext_rcd b
342 WHERE a.ext_rcd_id = b.ext_rcd_id
343 AND a.ext_rslt_id = p_ext_rslt_id;
344 --
345 cursor csr_get_rec_statistics_req is
346 SELECT count(decode(b.rcd_type_cd, 'H', b.rcd_type_cd))
347 ,count(decode(b.rcd_type_cd, 'D', b.rcd_type_cd))
348 ,count(decode(b.rcd_type_cd, 'T', b.rcd_type_cd))
349 FROM ben_ext_rslt_dtl a
350 ,ben_ext_rcd b
351 WHERE a.ext_rcd_id = b.ext_rcd_id
352 AND a.ext_rslt_id = p_ext_rslt_id; --a.request_id = p_request_id;
353 --
354 cursor csr_get_rec_statistics_both is
355 SELECT count(decode(b.rcd_type_cd, 'H', b.rcd_type_cd))
356 ,count(decode(b.rcd_type_cd, 'D', b.rcd_type_cd))
357 ,count(decode(b.rcd_type_cd, 'T', b.rcd_type_cd))
358 FROM ben_ext_rslt_dtl a
359 ,ben_ext_rcd b
360 WHERE a.ext_rcd_id = b.ext_rcd_id
361 AND a.request_id = p_request_id
362 AND a.ext_rslt_id = p_ext_rslt_id;
363 --
364 begin
365 --
366 hr_utility.set_location('Entering'||l_proc, 5);
367 --
368 if p_ext_rslt_id is null and p_request_id is null then
369 p_header_rec := 0;
370 p_detail_rec := 0;
371 p_trailer_rec := 0;
372 elsif p_ext_rslt_id is not null and p_request_id is null then
373 open csr_get_rec_statistics_rslt;
374 fetch csr_get_rec_statistics_rslt into p_header_rec,
375 p_detail_rec,
376 p_trailer_rec;
377 close csr_get_rec_statistics_rslt;
378 elsif p_ext_rslt_id is null and p_request_id is not null then
379 open csr_get_rec_statistics_req;
380 fetch csr_get_rec_statistics_req into p_header_rec,
381 p_detail_rec,
382 p_trailer_rec;
383 close csr_get_rec_statistics_req;
384 elsif p_ext_rslt_id is not null and p_request_id is not null then
385 open csr_get_rec_statistics_both;
386 fetch csr_get_rec_statistics_both into p_header_rec,
387 p_detail_rec,
388 p_trailer_rec;
389 close csr_get_rec_statistics_both;
390 end if;
391 --
392 hr_utility.set_location('Exiting'||l_proc, 15);
393 --
394 end get_rec_statistics;
395 --
396 --
397 -- This procedure will return total people extracted, total people not
398 -- extracted due to error corresponding to a ext_rslt_id or request_id or both.
399 -- If both ext_rslt_id and request_id are passed and if they do not correspond
400 -- to each other then 0, 0, 0 will be returned.
401 --
402 procedure get_per_statistics
403 (p_ext_rslt_id in number default null
404 ,p_request_id in number default null
405 ,p_per_xtrctd out nocopy number
406 ,p_per_not_xtrctd out nocopy number
407 ) is
408 --
409 l_request_id number;
410 l_proc varchar2(72) := g_package||'.get_per_statistics';
411 --
412 cursor csr_get_per_xtrctd_rslt is
413 SELECT count(distinct person_id)
414 FROM ben_ext_rslt_dtl
415 WHERE ext_rslt_id = p_ext_rslt_id
416 AND person_id not in (0, 999999999999);
417
418 --
419 cursor csr_get_per_xtrctd_req is
420 SELECT count(distinct person_id)
421 FROM ben_ext_rslt_dtl
422 WHERE ext_rslt_id = p_ext_rslt_id --request_id = p_request_id
423 AND person_id not in (0, 999999999999);
424 --
425 cursor csr_get_per_not_xtrctd_req is
426 SELECT count(distinct person_id)
427 FROM ben_ext_rslt_err
428 WHERE ext_rslt_id = p_ext_rslt_id --request_id = l_request_id
429 AND person_id not in (0, 999999999999)
430 AND typ_cd <> 'W';
431 --
432 cursor csr_get_req_id is
433 SELECT request_id
434 FROM ben_ext_rslt
435 WHERE ext_rslt_id = p_ext_rslt_id;
436 --
437 begin
438 --
439 --
440 hr_utility.set_location('Entering'||l_proc, 5);
441 --
442 if p_ext_rslt_id is null and p_request_id is null then
443 p_per_xtrctd := 0;
444 p_per_not_xtrctd := 0;
445 elsif p_ext_rslt_id is not null and p_request_id is null then
446 open csr_get_per_xtrctd_rslt;
447 fetch csr_get_per_xtrctd_rslt into p_per_xtrctd;
448 close csr_get_per_xtrctd_rslt;
449 --
450 open csr_get_req_id;
451 fetch csr_get_req_id into l_request_id;
452 close csr_get_req_id;
453 --
454 if l_request_id is null then
455 p_per_not_xtrctd := 0;
456 else
457 open csr_get_per_not_xtrctd_req;
458 fetch csr_get_per_not_xtrctd_req into p_per_not_xtrctd;
459 close csr_get_per_not_xtrctd_req;
460 end if;
461 --
462 elsif p_ext_rslt_id is null and p_request_id is not null then
463 open csr_get_per_xtrctd_req;
464 fetch csr_get_per_xtrctd_req into p_per_xtrctd;
465 close csr_get_per_xtrctd_req;
466 --
467 l_request_id := p_request_id;
468 open csr_get_per_not_xtrctd_req;
469 fetch csr_get_per_not_xtrctd_req into p_per_not_xtrctd;
470 close csr_get_per_not_xtrctd_req;
471 --
472 elsif p_ext_rslt_id is not null and p_request_id is not null then
473 open csr_get_req_id;
474 fetch csr_get_req_id into l_request_id;
475 close csr_get_req_id;
476 --
477 if l_request_id <> p_request_id then
478 p_per_xtrctd := 0;
479 p_per_not_xtrctd := 0;
480 else
481 open csr_get_per_xtrctd_req;
482 fetch csr_get_per_xtrctd_req into p_per_xtrctd;
483 close csr_get_per_xtrctd_req;
484 --
485 open csr_get_per_not_xtrctd_req;
486 fetch csr_get_per_not_xtrctd_req into p_per_not_xtrctd;
487 close csr_get_per_not_xtrctd_req;
488 end if;
489 --
490 end if;
491 --
492 hr_utility.set_location('Exiting'||l_proc, 15);
493 --
494 end get_per_statistics;
495 --
496 --
497 -- This procedure will return total job failures, total errors,
498 -- total warnings corresponding to a ext_rslt_id or request_id or both.
499 -- If both ext_rslt_id and request_id are passed and if they do not correspond
500 -- to each other then 0, 0, 0 will be returned.
501 --
502 procedure get_err_warn_statistics
503 (p_ext_rslt_id in number default null
504 ,p_request_id in number default null
505 ,p_job_failure out nocopy number
506 ,p_error out nocopy number
507 ,p_warning out nocopy number
508 ) is
509 --
510 l_request_id number;
511 --
512 cursor csr_get_err_warn_stat_req is
513 SELECT count(decode(typ_cd, 'F', typ_cd))
514 ,count(decode(typ_cd, 'E', typ_cd))
515 ,count(decode(typ_cd, 'W', typ_cd))
516 FROM ben_ext_rslt_err
517 WHERE ext_rslt_id = p_ext_rslt_id; --request_id = l_request_id;
518 --
519 cursor csr_get_req_id is
520 SELECT request_id
521 FROM ben_ext_rslt
522 WHERE ext_rslt_id = p_ext_rslt_id;
523 --
524 l_proc varchar2(72) := g_package||'.get_err_warn_statistics';
525 --
526 begin
527 --
528 --
529 hr_utility.set_location('Entering'||l_proc, 5);
530 --
531 if p_ext_rslt_id is null and p_request_id is null then
532 p_job_failure := 0;
533 p_error := 0;
534 p_warning := 0;
535 elsif p_ext_rslt_id is not null and p_request_id is null then
536 open csr_get_req_id;
537 fetch csr_get_req_id into l_request_id;
538 close csr_get_req_id;
539 --
540 if l_request_id is null then
541 p_job_failure := 0;
542 p_error := 0;
543 p_warning := 0;
544 else
545 open csr_get_err_warn_stat_req;
546 fetch csr_get_err_warn_stat_req into p_job_failure
547 ,p_error
548 ,p_warning;
549 close csr_get_err_warn_stat_req;
550 end if;
551 --
552 elsif p_ext_rslt_id is null and p_request_id is not null then
553 --
554 l_request_id := p_request_id;
555 open csr_get_err_warn_stat_req;
556 fetch csr_get_err_warn_stat_req into p_job_failure
557 ,p_error
558 ,p_warning;
559 close csr_get_err_warn_stat_req;
560 elsif p_ext_rslt_id is not null and p_request_id is not null then
561 open csr_get_req_id;
562 fetch csr_get_req_id into l_request_id;
563 close csr_get_req_id;
564 --
565 if l_request_id <> p_request_id then
566 p_job_failure := 0;
567 p_error := 0;
568 p_warning := 0;
569 else
570 open csr_get_err_warn_stat_req;
571 fetch csr_get_err_warn_stat_req into p_job_failure
572 ,p_error
573 ,p_warning;
574 close csr_get_err_warn_stat_req;
575 end if;
576 --
577 end if;
578 --
579 hr_utility.set_location('Exiting'||l_proc, 15);
580 --
581 end get_err_warn_statistics;
582 --
583 --
584 -- This procedure will return a text containing the statistics of the extract
585 -- run.
586 --
587 procedure get_statistics_text
588 (p_ext_rslt_id in number default null
589 ,p_request_id in number default null
590 ,p_text out nocopy varchar2
591 ) is
592 --
593 l_proc varchar2(72) := g_package||'.get_statistics_text';
594 --
595 -- One DB hit for two lookup_type
596 --
597 cursor get_prompt is
598 SELECT decode(lookup_type, 'BEN_EXT_ERR_TYP',
599 '1' || lookup_code,
600 'BEN_EXT_PROMPT',
601 '2' || lookup_code) lookup_code,
602 meaning
603 FROM hr_lookups
604 WHERE lookup_type in ('BEN_EXT_ERR_TYP', 'BEN_EXT_PROMPT');
605 --
606 Type lookup_rec_typ is Record
607 (lookup_code hr_lookups.lookup_code%type
608 ,meaning hr_lookups.meaning%type
609 );
610 --
611 Type lookup_tab_typ is table
612 of lookup_rec_typ
613 Index By Binary_Integer;
614 --
615 l_lookup_tab lookup_tab_typ;
616 --
617 l_lookup_code hr_lookups.lookup_code%type;
618 l_meaning hr_lookups.meaning%type;
619 l_counter integer := 0;
620 l_text varchar2(4000) := NULL;
621 l_rec_tab g_rec_nam_num_tab_typ;
622 l_tot_rec number := 0;
623 l_tot_per number := 0;
624 l_tot_err number := 0;
625 l_per_xtrctd number := 0;
626 l_per_not_xtrctd number := 0;
627 l_job_failure number := 0;
628 l_error number := 0;
629 l_warning number := 0;
630 l_lengthb number := 3880; -- 4000 - line of japan
631 --
632 -- Private function can be called from this procedure only
633 --
634 function get_index
635 (p_array in lookup_tab_typ
636 ,p_key in hr_lookups.lookup_code%type
637 ) return binary_integer is
638 begin
639 --
640 for i in 1..p_array.count
641 loop
642 if p_array(i).lookup_code = p_key then
643 return i;
644 end if;
645 end loop;
646 --
647 return 0;
648 end;
649 --
650 begin
651 --
652 --
653 hr_utility.set_location('Entering'||l_proc, 5);
654 --
655 open get_prompt;
656 loop
657 fetch get_prompt into l_lookup_code, l_meaning;
658 exit when get_prompt%notfound;
659 l_counter := l_counter + 1;
660 l_lookup_tab(l_counter).lookup_code := l_lookup_code;
661 l_lookup_tab(l_counter).meaning := l_meaning;
662 end loop;
663 close get_prompt;
664 --
665
666 hr_utility.set_location('p_ext_rslt_id'||p_ext_rslt_id, 5);
667 hr_utility.set_location('p_request_id'||p_request_id, 5);
668 get_rec_nam_num(p_ext_rslt_id => p_ext_rslt_id
669 ,p_request_id => p_request_id
670 ,p_rec_tab => l_rec_tab
671 );
672 --
673 if l_rec_tab.first = l_rec_tab.last
674 and l_rec_tab(1).name is null
675 and l_rec_tab(1).num = 0 then
676 l_text := rpad(l_lookup_tab(get_index(p_array => l_lookup_tab
677 ,p_key => '2E')).meaning, 34) || ' ' || lpad('0', 6) || ' ';
678 else
679 for i in 1..l_rec_tab.count
680 loop
681 hr_utility.set_location ( l_rec_tab(i).name || ' ' || l_rec_tab(i).num , 60);
682 l_tot_rec := l_tot_rec + l_rec_tab(i).num;
683 end loop;
684 --
685 l_text := rpad(l_lookup_tab(get_index(p_array => l_lookup_tab
686 ,p_key => '2E')).meaning, 34)
687 || ' ' || lpad(to_char(l_tot_rec), 6) || ' ';
688 --
689 for i in 1..l_rec_tab.count
690 loop
691 -- validate the lenght before joining ;
692
693 if lengthb(l_text || '-' || rpad(nvl(l_rec_tab(i).name, 'No Name'), 33)
694 || ' ' || lpad(to_char(l_rec_tab(i).num), 6) || ' ' ) < l_lengthb then
695
696 l_text := l_text || '-' || rpad(nvl(l_rec_tab(i).name, 'No Name'), 33)
697 || ' ' || lpad(to_char(l_rec_tab(i).num), 6) || ' ';
698 end if ;
699
700 end loop;
701 end if;
702 --
703 l_text := l_text || '-----------------------------------------';
704 --
705 get_per_statistics(p_ext_rslt_id => p_ext_rslt_id
706 ,p_request_id => p_request_id
707 ,p_per_xtrctd => l_per_xtrctd
708 ,p_per_not_xtrctd => l_per_not_xtrctd
709 );
710 l_tot_per := l_per_xtrctd + l_per_not_xtrctd;
711 -- we validate 120 character to make sure the unicode
712 if lengthb(l_text) < l_lengthb then
713 l_text := l_text || rpad(l_lookup_tab(get_index(p_array => l_lookup_tab
714 ,p_key => '2P')).meaning
715 , 34) || ' ' || lpad(to_char(l_tot_per), 6) || ' ';
716 end if ;
717 --
718 if l_per_xtrctd <> 0 then
719 if lengthb(l_text) < l_lengthb then
720 l_text := l_text || '-' || rpad(l_lookup_tab(get_index(p_array => l_lookup_tab
721 ,p_key => '2PE')).meaning ,33)
722 || ' ' || lpad(to_char(l_per_xtrctd), 6) || ' ';
723 end if ;
724 end if;
725 if l_per_not_xtrctd <> 0 then
726 if lengthb(l_text) < l_lengthb then
727 l_text := l_text || '-' || rpad(l_lookup_tab(get_index(p_array => l_lookup_tab
728 ,p_key => '2PNE')).meaning ,33)
729 || ' ' || lpad(to_char(l_per_not_xtrctd), 6) || ' ';
730 end if ;
731 end if;
732 --
733 l_text := l_text || '-----------------------------------------';
734 --
735 get_err_warn_statistics(p_ext_rslt_id => p_ext_rslt_id
736 ,p_request_id => p_request_id
737 ,p_job_failure => l_job_failure
738 ,p_error => l_error
739 ,p_warning => l_warning
740 );
741 --
742 l_tot_err := l_job_failure + l_error + l_warning;
743 --
744 if lengthb(l_text) < l_lengthb then
745 l_text := l_text || rpad(l_lookup_tab(get_index(p_array => l_lookup_tab
746 ,p_key => '2EW')).meaning
747 , 34) || ' ' || lpad(to_char(l_tot_err), 6) || ' ';
748 end if ;
749 --
750 if l_job_failure <> 0 then
751 if lengthb(l_text) < l_lengthb then
752 l_text := l_text || '-' || rpad(l_lookup_tab(get_index(p_array => l_lookup_tab
753 ,p_key => '1F')).meaning ,33)
754 || ' ' || lpad(to_char(l_job_failure), 6) || ' ';
755 end if ;
756 end if;
757 if l_error <> 0 then
758 if lengthb(l_text) < l_lengthb then
759 l_text := l_text || '-' || rpad(l_lookup_tab(get_index(p_array => l_lookup_tab
760 ,p_key => '1E')).meaning ,33)
761 || ' ' || lpad(to_char(l_error), 6) || ' ';
762 end if;
763 end if;
764 if l_warning <> 0 then
765 if lengthb(l_text) < l_lengthb then
766 l_text := l_text || '-' || rpad(l_lookup_tab(get_index(p_array => l_lookup_tab
767 ,p_key => '1W')).meaning ,33)
768 || ' ' || lpad(to_char(l_warning), 6) || ' ';
769 end if ;
770 end if;
771 -- Currently theform si nopt supporting more then 2000 cahr
772 -- if the l_text_more then 2000 , truncate
773 if lengthb(l_text) > 2000 then
774 l_text := substrb(l_text,1,2000) ;
775 end if ;
776 p_text := l_text;
777 --
778 --
779 hr_utility.set_location('Exiting'||l_proc, 15);
780 --
781 end get_statistics_text;
782 -----------------------------------------------------------------------------------
783 ----------------------------< Get_Value >------------------------------------------
784 -----------------------------------------------------------------------------------
785 Function get_value(p_ext_rcd_id number,
786 p_ext_rslt_dtl_id number,
787 p_seq_num number)
788 RETURN varchar2 IS
789 --
790 l_char_seq_num varchar2(3);
791 l_cid integer;
792 l_res integer;
793 l_string varchar2(2000);
794 l_value varchar2(200);
795 l_proc varchar2(72) := g_package||'.get_value';
796 --
797 BEGIN
798 --
799 --
800 hr_utility.set_location('Entering'||l_proc, 5);
801 --
802 if p_seq_num < 10 then
803 l_char_seq_num := '0' || to_char(p_seq_num);
804 else
805 l_char_seq_num := to_char(p_seq_num);
806 end if;
807 --
808 l_string := 'SELECT val_' || l_char_seq_num ||
809 ' FROM ben_ext_rslt_dtl ' ||
810 'WHERE ext_rslt_dtl_id = ' ||
811 to_char(p_ext_rslt_dtl_id);
812 --
813 l_cid := DBMS_SQL.OPEN_CURSOR;
814 DBMS_SQL.PARSE(l_cid, l_string, DBMS_SQL.NATIVE);
815 DBMS_SQL.DEFINE_COLUMN(l_cid, 1, l_value, 200);
816 l_res := DBMS_SQL.EXECUTE(l_cid);
817 l_res := DBMS_SQL.FETCH_ROWS(l_cid);
818 DBMS_SQL.COLUMN_VALUE(l_cid, 1, l_value);
819 --
820 DBMS_SQL.CLOSE_CURSOR(l_cid);
821 --
822 --
823 hr_utility.set_location('Exiting'||l_proc, 5);
824 --
825 return(l_value);
826 --
827 END get_value;
828 --
829 -----------------------------------------------------------------------------------
830 --------------------------------< Get_chg_dates >----------------------------------
831 -----------------------------------------------------------------------------------
832 --
833 procedure get_chg_dates
834 (p_ext_dfn_id in number,
835 p_effective_date in date,
836 p_chg_actl_strt_dt out nocopy date,
837 p_chg_actl_end_dt out nocopy date,
838 p_chg_eff_strt_dt out nocopy date,
839 p_chg_eff_end_dt out nocopy date) is
840 --
841 cursor c_chg_actl_dt(p_ext_dfn_id in number) is
842 select xcv.val_1, xcv.val_2, xct.excld_flag
843 from ben_ext_crit_val xcv,
844 ben_ext_crit_typ xct,
845 ben_ext_dfn xdf
846 where xdf.ext_dfn_id = p_ext_dfn_id
847 and xdf.ext_crit_prfl_id = xct.ext_crit_prfl_id
848 and xct.ext_crit_typ_id = xcv.ext_crit_typ_id
849 and xct.crit_typ_cd = 'CAD'; -- change actual date
850 l_chg_actl_dt c_chg_actl_dt%rowtype;
851
852 --
853 cursor c_chg_eff_dt(p_ext_dfn_id in number) is
854 select xcv.val_1, xcv.val_2, xct.excld_flag
855 from ben_ext_crit_val xcv,
856 ben_ext_crit_typ xct,
857 ben_ext_dfn xdf
858 where xdf.ext_dfn_id = p_ext_dfn_id
859 and xdf.ext_crit_prfl_id = xct.ext_crit_prfl_id
860 and xct.ext_crit_typ_id = xcv.ext_crit_typ_id
861 and xct.crit_typ_cd = 'CED'; -- change effective date
862 l_chg_eff_dt c_chg_eff_dt%rowtype;
863 --
864 l_proc varchar2(72) := g_package||'.get_chg_dates';
865 --
866 Begin
867 --
868 hr_utility.set_location('Entering'||l_proc, 5);
869 --
870 -- Note about this logic: If the exclude flag is on for these ranges,
871 -- then we will set the range from bot to eot, and let the evaluate
872 -- inclusion program handle it.
873 --
874 open c_chg_actl_dt(p_ext_dfn_id);
875 fetch c_chg_actl_dt into l_chg_actl_dt;
876 close c_chg_actl_dt;
877 --
878 if nvl(l_chg_actl_dt.excld_flag,'N') = 'Y' or l_chg_actl_dt.val_1 is null or
879 l_chg_actl_dt.val_1 in ('CHAD','CHED') then
880 p_chg_actl_strt_dt := hr_api.g_sot;
881 else
882 p_chg_actl_strt_dt := ben_ext_util.calc_ext_date
883 (p_ext_date_cd => l_chg_actl_dt.val_1,
884 p_abs_date => p_effective_date,
885 p_ext_dfn_id => p_ext_dfn_id
886 );
887 end if;
888
889 --
890 if nvl(l_chg_actl_dt.excld_flag,'N') = 'Y' or l_chg_actl_dt.val_2 is null or
891 l_chg_actl_dt.val_2 in ('CHAD','CHED'/*,'CTBSD','CESD','CLEOD','CDBLEOD'*/ ) then
892 p_chg_actl_end_dt := hr_api.g_eot;
893 else
894 p_chg_actl_end_dt := ben_ext_util.calc_ext_date
895 (p_ext_date_cd => l_chg_actl_dt.val_2,
896 p_abs_date => p_effective_date,
897 p_ext_dfn_id => p_ext_dfn_id
898 );
899 end if;
900 --
901 open c_chg_eff_dt(p_ext_dfn_id);
902 fetch c_chg_eff_dt into l_chg_eff_dt;
903 close c_chg_eff_dt;
904 --
905 if nvl(l_chg_eff_dt.excld_flag,'N') = 'Y' or l_chg_eff_dt.val_1 is null or
906 l_chg_actl_dt.val_2 in ('CHAD','CHED'/*,'CTBSD','CESD','CLEOD','CDBLEOD'*/ ) then
907 p_chg_eff_strt_dt := hr_api.g_sot;
908 else
909 p_chg_eff_strt_dt := ben_ext_util.calc_ext_date
910 (p_ext_date_cd => l_chg_eff_dt.val_1,
911 p_abs_date => p_effective_date,
912 p_ext_dfn_id => p_ext_dfn_id
913 );
914 end if;
915 --
916 if nvl(l_chg_eff_dt.excld_flag,'N') = 'Y' or l_chg_eff_dt.val_2 is null or
917 l_chg_actl_dt.val_2 in ('CHAD','CHED'/*,'CTBSD','CESD','CLEOD','CDBLEOD'*/ ) then
918 p_chg_eff_end_dt := hr_api.g_eot;
919 else
920 p_chg_eff_end_dt := ben_ext_util.calc_ext_date
921 (p_ext_date_cd => l_chg_eff_dt.val_2,
922 p_abs_date => p_effective_date,
923 p_ext_dfn_id => p_ext_dfn_id
924 );
925 end if;
926 --
927 hr_utility.set_location('chg start date ' || p_chg_eff_strt_dt , 9185);
928 hr_utility.set_location('chg End date ' || p_chg_eff_end_dt , 9185);
929 hr_utility.set_location('Exiting'||l_proc, 5);
930 --
931 End get_chg_dates;
932 --
933 -----------------------------------------------------------------------------------
934 --------------------------------< Get_cm_dates >----------------------------------
935 -----------------------------------------------------------------------------------
936 --
937 procedure get_cm_dates
938 (p_ext_dfn_id in number,
939 p_effective_date in date,
940 p_to_be_sent_strt_dt out nocopy date,
941 p_to_be_sent_end_dt out nocopy date) is
942 --
943 cursor c_to_be_sent_dt(p_ext_dfn_id in number) is
944 select xcv.val_1, xcv.val_2, xct.excld_flag
945 from ben_ext_crit_val xcv,
946 ben_ext_crit_typ xct,
947 ben_ext_dfn xdf
948 where xdf.ext_dfn_id = p_ext_dfn_id
949 and xdf.ext_crit_prfl_id = xct.ext_crit_prfl_id
950 and xct.ext_crit_typ_id = xcv.ext_crit_typ_id
951 and xct.crit_typ_cd = 'MTBSDT'; -- comm to be sent date
952 l_to_be_sent_dt c_to_be_sent_dt%rowtype;
953
954 l_proc varchar2(72) := g_package||'.get_cm_dates';
955 --
956 Begin
957 --
958 hr_utility.set_location('Entering'||l_proc, 5);
959 --
960 -- Note about this logic: If the exclude flag is on for these ranges,
961 -- then we will set the range from bot to eot, and let the evaluate
962 -- inclusion program handle it.
963 --
964 open c_to_be_sent_dt(p_ext_dfn_id);
965 fetch c_to_be_sent_dt into l_to_be_sent_dt;
966 close c_to_be_sent_dt;
967 --
968 if nvl(l_to_be_sent_dt.excld_flag,'N') = 'Y' or l_to_be_sent_dt.val_1 is null then
969 p_to_be_sent_strt_dt := hr_api.g_sot;
970 else
971
972 hr_utility.set_location(' 514 error cm ' , 514);
973 p_to_be_sent_strt_dt := ben_ext_util.calc_ext_date
974 (p_ext_date_cd => l_to_be_sent_dt.val_1,
975 p_abs_date => p_effective_date,
976 p_ext_dfn_id => p_ext_dfn_id
977 );
978 end if;
979 --
980 if nvl(l_to_be_sent_dt.excld_flag,'N') = 'Y' or l_to_be_sent_dt.val_2 is null then
981 p_to_be_sent_end_dt := hr_api.g_eot;
982 else
983 hr_utility.set_location(' 514 error cmm ' , 514);
984 p_to_be_sent_end_dt := ben_ext_util.calc_ext_date
985 (p_ext_date_cd => l_to_be_sent_dt.val_2,
986 p_abs_date => p_effective_date,
987 p_ext_dfn_id => p_ext_dfn_id
988 );
989 end if;
990 --
991 hr_utility.set_location('cm start date ' || p_to_be_sent_strt_dt , 9185);
992 hr_utility.set_location('cm End date ' || p_to_be_sent_end_dt , 9185);
993
994 hr_utility.set_location('Exiting'||l_proc, 5);
995 --
996 End get_cm_dates;
997 --
998 -----------------------------------------------------------------------------------
999 --------------------------------< get_ext_dates >----------------------------------
1000 -----------------------------------------------------------------------------------
1001 --
1002 -- Full profile extracts always use the Extract Effective Date for extracting
1003 -- Datetrack and dated fields. This is the date passed in Conc Mgr at runtime.
1004 -- Communication Extracts use the effective date passed in
1005 -- unless overriden via the
1006 -- criteria profile Datetrack Override options. Changes Only Extracts use The
1007 -- effective date passed in unless overriden
1008 -- via the criteria profile Datetrack Override options.
1009 -- Also it is worth mentioning here that the user can extract person related
1010 -- data as of one date, and benefits related data as of another.
1011 --
1012 procedure get_ext_dates
1013 (p_ext_dfn_id in number,
1014 p_data_typ_cd in varchar2,
1015 p_effective_date in date,
1016 p_person_ext_dt out nocopy date,
1017 p_benefits_ext_dt out nocopy date) is
1018 --
1019 /*
1020 cursor c_person_dt_cd(p_ext_dfn_id in number) is
1021 select xcv.val_1
1022 from ben_ext_crit_val xcv,
1023 ben_ext_crit_typ xct,
1024 ben_ext_dfn xdf
1025 where xdf.ext_dfn_id = p_ext_dfn_id
1026 and xdf.ext_crit_prfl_id = xct.ext_crit_prfl_id
1027 and xct.ext_crit_typ_id = xcv.ext_crit_typ_id
1028 and xct.crit_typ_cd = 'PASOR'; -- person datetrack override date
1029 */
1030 l_person_dt_cd ben_ext_crit_val.val_1%TYPE; -- UTF8 varchar2(30);
1031 l_person_dt date;
1032 --
1033 /*
1034 cursor c_benefits_dt_cd(p_ext_dfn_id in number) is
1035 select xcv.val_1
1036 from ben_ext_crit_val xcv,
1037 ben_ext_crit_typ xct,
1038 ben_ext_dfn xdf
1039 where xdf.ext_dfn_id = p_ext_dfn_id
1040 and xdf.ext_crit_prfl_id = xct.ext_crit_prfl_id
1041 and xct.ext_crit_typ_id = xcv.ext_crit_typ_id
1042 and xct.crit_typ_cd = 'BDTOR'; -- benefits datetrack override date
1043 */
1044
1045 l_dummy varchar2(1) ;
1046 l_benefits_dt_cd ben_ext_crit_val.val_1%TYPE; -- UTF8 varchar2(30);
1047 l_benefits_dt date;
1048 ---
1049 cursor c_asg_exist is
1050 select 'x' from
1051 per_all_assignments_f
1052 where person_id = ben_ext_person.g_person_id
1053 and primary_flag = 'Y'
1054 and p_effective_Date between effective_start_date
1055 and effective_end_date ;
1056
1057
1058 cursor c_e_asg_exist is
1059 select 'x' from
1060 per_all_assignments_f
1061 where person_id = ben_ext_person.g_person_id
1062 and primary_flag = 'Y'
1063 and assignment_type = 'E' -- added by hmani bug 3629576
1064 and p_effective_Date between effective_start_date
1065 and effective_end_date ;
1066
1067
1068 cursor c_A_asg_exist is
1069 select 'x' from
1070 per_all_assignments_f
1071 where person_id = ben_ext_person.g_person_id
1072 and assignment_type = 'A' -- applicatn does not have any primary
1073 and p_effective_Date between effective_start_date
1074 and effective_end_date ;
1075
1076
1077 cursor c_asg_term is
1078 select effective_end_date from
1079 per_all_assignments_f
1080 where person_id = ben_ext_person.g_person_id
1081 and primary_flag = 'Y'
1082 and assignment_type = 'E' -- added by hmani bug 3629576
1083 and effective_start_date < p_effective_Date
1084 order by effective_end_Date desc ;
1085
1086
1087 l_proc varchar2(72) := g_package||'.get_ext_dates';
1088 --
1089 Begin
1090 --
1091 hr_utility.set_location('Entering'||l_proc, 5);
1092
1093 --
1094 if ben_extract.g_pasor_dt_cd is not null then
1095 l_person_dt_cd := ben_extract.g_pasor_dt_cd ;
1096 end if ;
1097
1098 hr_utility.set_location('pasor_dt_cd '||l_person_dt_cd, 5);
1099 hr_utility.set_location('effective date '||p_effective_date, 5);
1100
1101 if l_person_dt_cd in ( 'TDRASG','TDPRASG') then
1102 l_person_dt := p_effective_date;
1103 l_benefits_dt := p_effective_date;
1104
1105 -- if employee exist dont do anything, else check for criteria
1106 open c_e_asg_exist ;
1107 fetch c_e_asg_exist into l_dummy ;
1108 if c_e_asg_exist%notfound then
1109 --- if there is no employee assignment get the termianted employee assignment
1110 if l_person_dt_cd = 'TDRASG' then
1111 open c_asg_term ;
1112 fetch c_asg_term into l_person_dt ;
1113 close c_asg_term ;
1114 l_benefits_dt := l_person_dt ;
1115 hr_utility.set_location('terminated asg eff date'||l_person_dt, 5);
1116 end if ;
1117 -- if employee assignment not there
1118 -- check for any other primary asg , if not
1119 -- check for any applicant assg , if not
1120 -- check for ex employee asg
1121 if l_person_dt_cd = 'TDPRASG' then
1122 --- if any primary assignment exist
1123 open c_asg_exist ;
1124 fetch c_asg_exist into l_dummy ;
1125 if c_asg_exist%notfound then
1126
1127 hr_utility.set_location(' pr asg not found '||l_person_dt_cd , 6);
1128 -- get applicant assignment
1129 open c_a_asg_exist ;
1130 fetch c_a_asg_exist into l_dummy ;
1131 if c_a_asg_exist%notfound then
1132 -- get terminated assignment
1133 open c_asg_term ;
1134 fetch c_asg_term into l_person_dt ;
1135 close c_asg_term ;
1136 l_benefits_dt := l_person_dt ;
1137 hr_utility.set_location('termianted asg eff date '||l_person_dt, 7) ;
1138 end if ;
1139 close c_a_asg_exist ;
1140 end if ;
1141 close c_asg_exist ;
1142 end if ;
1143 end if ;
1144 close c_e_asg_exist;
1145 end if ;
1146 --- Change and communication specifc
1147 if p_data_typ_cd in ( 'C', 'CM') then
1148
1149 if ben_extract.g_pasor_dt_cd is not null then
1150 l_person_dt_cd := ben_extract.g_pasor_dt_cd ;
1151
1152
1153 if l_person_dt_cd = 'CLEOD' then -- life event occured
1154 l_person_dt := ben_ext_person.g_cm_lf_evt_ocrd_dt;
1155 elsif l_person_dt_cd = 'CDBLEOD' then -- day before life event occured
1156 l_person_dt := ben_ext_person.g_cm_lf_evt_ocrd_dt - 1;
1157 elsif l_person_dt_cd = 'CESD' then -- per_cm_f effective start date
1158 l_person_dt := ben_ext_person.g_cm_eff_dt;
1159 elsif l_person_dt_cd = 'CTBSD' then -- communication to be sent date
1160 l_person_dt := ben_ext_person.g_cm_to_be_sent_dt;
1161 elsif l_person_dt_cd = 'CHAD' then -- change actual date
1162 l_person_dt := ben_ext_person.g_chg_actl_dt;
1163 elsif l_person_dt_cd = 'CHED' then -- change effective date
1164 l_person_dt := ben_ext_person.g_chg_eff_dt;
1165 elsif l_person_dt_cd = 'TD' then -- today (conc mgr effective dt)
1166 l_person_dt := p_effective_date;
1167 end if;
1168 end if; --found
1169 --l_benefits_dt := null;
1170 -- Benefit override code setup
1171 if ben_extract.g_bdtor_dt_cd is not null then
1172 l_benefits_dt_cd := ben_extract.g_bdtor_dt_cd ;
1173
1174 if l_benefits_dt_cd = 'CLEOD' then -- life event occured
1175 l_benefits_dt := ben_ext_person.g_cm_lf_evt_ocrd_dt;
1176 elsif l_benefits_dt_cd = 'CDBLEOD' then -- day before life event occured
1177 l_benefits_dt := ben_ext_person.g_cm_lf_evt_ocrd_dt - 1;
1178 elsif l_benefits_dt_cd = 'CESD' then -- per_cm_f effective start date
1179 l_benefits_dt := ben_ext_person.g_cm_eff_dt;
1180 elsif l_benefits_dt_cd = 'CTBSD' then -- communication to be sent date
1181 l_benefits_dt := ben_ext_person.g_cm_to_be_sent_dt;
1182 elsif l_benefits_dt_cd = 'CHAD' then -- change actual date
1183 l_benefits_dt := ben_ext_person.g_chg_actl_dt;
1184 elsif l_benefits_dt_cd = 'CHED' then -- change effective date
1185 l_benefits_dt := ben_ext_person.g_chg_eff_dt;
1186 elsif l_benefits_dt_cd = 'TD' then -- today (conc mgr effective dt)
1187 l_benefits_dt := p_effective_date;
1188 end if;
1189 end if; --found
1190 --
1191 elsif p_data_typ_cd = 'CW' then
1192
1193 if ben_extract.g_pasor_dt_cd is not null then
1194 l_person_dt_cd := ben_extract.g_pasor_dt_cd ;
1195
1196 if l_person_dt_cd = 'CWBEDT' then -- effective date
1197 l_person_dt := ben_ext_person.g_CWB_LE_DT;
1198 elsif l_person_dt_cd = 'CWBFDT' then -- life evt ocrd dt
1199 l_person_dt := ben_ext_person.g_CWB_EFFECTIVE_DATE ;
1200 elsif l_person_dt_cd = 'TD' then -- today (conc mgr effective dt)
1201 l_person_dt := p_effective_date;
1202 end if;
1203 end if; --found
1204 --- close c_person_dt_cd;
1205 -- apply defaults when not null;
1206 if l_person_dt is null then
1207 l_person_dt := p_effective_date;
1208 end if;
1209 end if; --data type
1210
1211 p_person_ext_dt := nvl(l_person_dt , p_effective_date );
1212 p_benefits_ext_dt := nvl(l_benefits_dt, p_effective_date);
1213 --
1214 hr_utility.set_location('l_person_dt_cd '||l_person_dt_cd||' / '||p_person_ext_dt , 5);
1215 hr_utility.set_location('l_benefits_dt_cd '||l_benefits_dt_cd||' / '|| p_benefits_ext_dt ,5);
1216 hr_utility.set_location('Exiting'||l_proc, 5);
1217 --
1218 End get_ext_dates;
1219 --
1220 Function calc_ext_date(p_ext_date_cd in varchar2,
1221 p_abs_date in date,
1222 p_ext_dfn_id in number,
1223 p_pl_id in number default null )
1224 Return Date Is
1225 --
1226 l_proc varchar2(72) := g_package||'.calc_ext_date';
1227 l_rslt_dt date := null;
1228 --
1229 l_run_dt date;
1230 l_eff_dt date;
1231 --
1232 cursor prior_ext_run_c is
1233 SELECT max(run_end_dt)
1234 FROM ben_ext_rslt
1235 WHERE ext_dfn_id = p_ext_dfn_id
1236 AND ext_stat_cd IN ('S', 'E', 'A','W');
1237 --
1238 cursor prior_ext_eff_c is
1239 SELECT max(eff_dt)
1240 FROM ben_ext_rslt
1241 WHERE ext_dfn_id = p_ext_dfn_id
1242 AND ext_stat_cd IN ('S', 'E', 'A','W');
1243 --
1244 cursor c_pln_yr is
1245 select start_date , end_date
1246 from ben_popl_yr_perd cpy ,
1247 ben_yr_perd yrp
1248 where
1249 cpy.yr_perd_id = yrp.yr_perd_id
1250 and cpy.pl_id = p_pl_id
1251 and p_abs_date
1252 between yrp.start_date and yrp.end_date ;
1253
1254
1255 l_yr_strt_date date ;
1256 l_yr_end_date date ;
1257
1258 --
1259 Begin
1260 --
1261 hr_utility.set_location('Entering:'||l_proc, 5);
1262 hr_utility.set_location('date '||p_abs_date, 5);
1263 --
1264 -- User entered date
1265 --
1266 if substr(p_ext_date_cd, 3, 1) IN ('-', '/') then
1267 --
1268 l_rslt_dt := to_date(p_ext_date_cd, 'MM/DD/YYYY');
1269 --
1270 -- Y - Yesterday
1271 --
1272 elsif p_ext_date_cd = 'Y' then
1273 --
1274 l_rslt_dt := p_abs_date - 1;
1275 --
1276 -- TD - Today
1277 --
1278 elsif p_ext_date_cd = 'TD' then
1279 --
1280 l_rslt_dt := p_abs_date;
1281 --
1282 -- TM - Tomorrow
1283 --
1284 elsif p_ext_date_cd = 'TM' then
1285 --
1286 l_rslt_dt := p_abs_date + 1;
1287 --
1288 -- FDOCM - First Day Of Current Month
1289 --
1290 elsif p_ext_date_cd = 'FDOCM' then
1291 --
1292 l_rslt_dt := trunc(p_abs_date, 'month');
1293 --
1294 -- LDOCM - Last Day Of Current Month
1295 --
1296 elsif p_ext_date_cd = 'LDOCM' then
1297 --
1298 l_rslt_dt := last_day(p_abs_date);
1299 --
1300 -- FDOCY - First Day Of Current Year
1301 --
1302 elsif p_ext_date_cd = 'FDOCY' then
1303 --
1304 l_rslt_dt := trunc(p_abs_date, 'YYYY');
1305 --
1306 -- LDOCY - Last Day Of Current Year
1307 --
1308 elsif p_ext_date_cd = 'LDOCY' then
1309 --
1310 l_rslt_dt := trunc(add_months(p_abs_date, 12), 'YYYY') - 1;
1311 --
1312 -- FDOCW - First Day Of Current Week
1313 --
1314 elsif p_ext_date_cd = 'FDOCW' then
1315 --
1316 l_rslt_dt := trunc(p_abs_date, 'DAY');
1317 --
1318 -- LDOCW - Last Day Of Current Week
1319 --
1320 elsif p_ext_date_cd = 'LDOCW' then
1321 --
1322 l_rslt_dt := trunc(p_abs_date + 7, 'DAY') - 1;
1323 --
1324 -- FDOCQ - First Day Of Current Quarter
1325 --
1326 elsif p_ext_date_cd = 'FDOCQ' then
1327 --
1328 l_rslt_dt := trunc(p_abs_date, 'Q');
1329 --
1330 -- LDOCQ - Last Day Of Current Quarter
1331 --
1332 elsif p_ext_date_cd = 'LDOCQ' then
1333 --
1334 l_rslt_dt := trunc(add_months(p_abs_date, 3), 'Q') - 1;
1335 --
1336 -- FDOPY - First Day Of Previous Year
1337 --
1338 elsif p_ext_date_cd = 'FDOPY' then
1339 --
1340 l_rslt_dt := trunc((trunc(p_abs_date, 'YYYY') - 1), 'YYYY');
1341 --
1342 -- LDOPY - Last Day Of Previous Year
1343 --
1344 elsif p_ext_date_cd = 'LDOPY' then
1345 --
1346 l_rslt_dt := trunc(p_abs_date, 'YYYY') - 1;
1347 --
1348 -- FDOPM - First Day Of Previous Month
1349 --
1350 elsif p_ext_date_cd = 'FDOPM' then
1351 --
1352 l_rslt_dt := trunc((trunc(p_abs_date, 'month') - 1), 'month');
1353 --
1354
1355
1356
1357 -- LDOPM - Last Day Of Previous Month
1358 --
1359 elsif p_ext_date_cd = 'LDOPM' then
1360 --
1361 l_rslt_dt := trunc(p_abs_date, 'month') - 1;
1362
1363 -- FD2OPM - First Day Of two Previous Month
1364 --
1365 elsif p_ext_date_cd = 'FDO2PM' then
1366 --
1367 l_rslt_dt := trunc( add_months(p_abs_date, -2) , 'Month');
1368 --
1369 -- LDO2PM - Last Day Of Previous Month
1370 --
1371 elsif p_ext_date_cd = 'LDO2PM' then
1372 --
1373 l_rslt_dt := trunc( add_months(p_abs_date, -1) , 'Month') - 1;
1374 --
1375 -- FDOPQ - First Day Of Previous Quarter
1376 --
1377 elsif p_ext_date_cd = 'FDOPQ' then
1378 --
1379 l_rslt_dt := trunc((trunc(p_abs_date, 'Q') - 1), 'Q');
1380 --
1381 -- LDOPQ - Last Day Of Previous Quarter
1382 --
1383 elsif p_ext_date_cd = 'LDOPQ' then
1384 --
1385 l_rslt_dt := trunc(p_abs_date, 'Q') - 1;
1386 --
1387 -- FDOPW - First Day Of Previous Week
1388 --
1389 elsif p_ext_date_cd = 'FDOPW' then
1390 --
1391 l_rslt_dt := trunc((trunc(p_abs_date, 'DAY') - 1), 'DAY');
1392 --
1393 -- LDOPW - Last Day Of Previous Week
1394 --
1395 elsif p_ext_date_cd = 'LDOPW' then
1396 --
1397 l_rslt_dt := trunc(p_abs_date, 'DAY') - 1;
1398 --
1399 -- FDONM - First Day Of Next Month
1400 --
1401 elsif p_ext_date_cd = 'FDONM' then
1402 --
1403 l_rslt_dt := trunc(add_months(p_abs_date, 1), 'month');
1404 --
1405 -- LDONM - Last Day Of Next Month
1406 --
1407 elsif p_ext_date_cd = 'LDONM' then
1408 --
1409 l_rslt_dt := last_day(trunc(add_months(p_abs_date, 1), 'month'));
1410 --
1411 -- FDOMAN - First Day Of Month After Next
1412 --
1413 elsif p_ext_date_cd = 'FDOMAN' then
1414 --
1415 l_rslt_dt := trunc(add_months(p_abs_date, 2), 'month');
1416 --
1417 -- LDOMAN - Last Day Of Month After Next
1418 --
1419 elsif p_ext_date_cd = 'LDOMAN' then
1420 --
1421 l_rslt_dt := last_day(trunc(add_months(p_abs_date, 2), 'month'));
1422 --
1423 -- BOT - Begginning of Time
1424 --
1425 elsif p_ext_date_cd = 'BOT' then
1426 --
1427 l_rslt_dt := to_date('01/01/0001', 'DD/MM/YYYY');
1428 --
1429 -- EOT - End of Time
1430 --
1431 elsif p_ext_date_cd = 'EOT' then
1432 --
1433 l_rslt_dt := to_date('31/12/4712', 'DD/MM/YYYY');
1434 --
1435 elsif p_ext_date_cd = '18MA' then
1436 --
1437 l_rslt_dt := trunc(add_months(p_abs_date, -18));
1438 --
1439 elsif p_ext_date_cd IN ('CTBSD','CESD','CLEOD','CDBLEOD') then
1440 --
1441 if ben_ext_person.g_cm_type_id is null then
1442 hr_utility.set_location(' 514 error '|| p_ext_date_cd , 514);
1443
1444 ben_ext_thread.g_err_num := 92451;
1445 ben_ext_thread.g_err_name := 'BEN_92451_EXT_INV_CM_DT';
1446 raise ben_ext_thread.g_job_failure_error;
1447
1448 end if;
1449
1450 if p_ext_date_cd = 'CTBSD' then --communication to be sent date
1451
1452 if ben_ext_person.g_cm_to_be_sent_dt is null then
1453
1454 ben_ext_thread.g_err_num := 92454;
1455 ben_ext_thread.g_err_name := 'BEN_92454_EXT_INV_TO_BE_SNT_DT';
1456 raise ben_ext_person.detail_error;
1457
1458 else
1459
1460 l_rslt_dt := trunc(ben_ext_person.g_cm_to_be_sent_dt);
1461
1462 end if;
1463
1464 elsif p_ext_date_cd = 'CESD' then -- communication effective start date
1465
1466 l_rslt_dt := trunc(ben_ext_person.g_cm_eff_dt);
1467
1468 elsif p_ext_date_cd = 'CLEOD' then -- communication life event occurred date
1469
1470 if ben_ext_person.g_cm_lf_evt_ocrd_dt is null then
1471
1472 ben_ext_thread.g_err_num := 92450;
1473 ben_ext_thread.g_err_name := 'BEN_92450_EXT_INV_LER_DT';
1474 raise ben_ext_person.detail_error;
1475
1476 else
1477
1478 l_rslt_dt := trunc(ben_ext_person.g_cm_lf_evt_ocrd_dt);
1479
1480 end if;
1481
1482 else -- CDBLEOD communication day before life event occured date
1483
1484 if ben_ext_person.g_cm_lf_evt_ocrd_dt is null then
1485
1486 ben_ext_thread.g_err_num := 92450;
1487 ben_ext_thread.g_err_name := 'BEN_92450_EXT_INV_LER_DT';
1488 raise ben_ext_person.detail_error;
1489
1490 else
1491
1492 l_rslt_dt := trunc(ben_ext_person.g_cm_lf_evt_ocrd_dt) - 1;
1493
1494 end if;
1495
1496 end if;
1497 --
1498 elsif p_ext_date_cd = 'CHAD' then
1499
1500 if ben_ext_person.g_chg_actl_dt is null then
1501 hr_utility.set_location(' 514 error 1' , 514);
1502 ben_ext_thread.g_err_num := 92455;
1503 ben_ext_thread.g_err_name := 'BEN_92455_EXT_INV_CHG_DT';
1504 raise ben_ext_thread.g_job_failure_error;
1505
1506 else
1507
1508 l_rslt_dt := trunc(ben_ext_person.g_chg_actl_dt);
1509
1510 end if;
1511 --
1512 elsif p_ext_date_cd = 'CHED' then
1513
1514 if ben_ext_person.g_chg_eff_dt is null then
1515
1516 hr_utility.set_location(' 514 error 2' , 514);
1517 ben_ext_thread.g_err_num := 92455;
1518 ben_ext_thread.g_err_name := 'BEN_92455_EXT_INV_CHG_DT';
1519 raise ben_ext_thread.g_job_failure_error;
1520
1521 else
1522
1523 l_rslt_dt := trunc(ben_ext_person.g_chg_eff_dt);
1524
1525 end if;
1526 --
1527 -- day after last run date, day of last run date
1528 elsif p_ext_date_cd IN ('DARD', 'DORD','DBRD') then
1529 --
1530 l_run_dt := null;
1531 open prior_ext_run_c;
1532 fetch prior_ext_run_c into l_run_dt;
1533 close prior_ext_run_c;
1534 --
1535 if nvl(to_char(l_run_dt),'x') = 'x' then -- this extract has never been run before
1536 --
1537 l_rslt_dt := to_date('01/01/0001', 'DD/MM/YYYY');
1538 --
1539 elsif p_ext_date_cd = 'DARD' then
1540 --
1541 l_rslt_dt := trunc(l_run_dt+1);
1542 --
1543 elsif p_ext_date_cd = 'DORD' then
1544 --
1545 l_rslt_dt := trunc(l_run_dt);
1546 --
1547 elsif p_ext_date_cd = 'DBRD' then
1548 --
1549 l_rslt_dt := trunc(l_run_dt-1);
1550
1551 --
1552 end if;
1553 --
1554 -- day of last effective date, day after last effective date.
1555 elsif p_ext_date_cd IN ('DAED','DOED','DBED') then
1556 --
1557 l_eff_dt := null;
1558 open prior_ext_eff_c;
1559 fetch prior_ext_eff_c into l_eff_dt;
1560 close prior_ext_eff_c;
1561 --
1562 if nvl(to_char(l_eff_dt),'x') = 'x' then --it has never been run
1563 --
1564 l_rslt_dt := to_date('01/01/0001', 'DD/MM/YYYY');
1565 --
1566 elsif p_ext_date_cd = 'DAED' then
1567 --
1568 l_rslt_dt := trunc(l_eff_dt+1);
1569 --
1570 elsif p_ext_date_cd = 'DOED' then
1571 --
1572 l_rslt_dt := trunc(l_eff_dt);
1573 --
1574 elsif p_ext_date_cd = 'DBED' then
1575 --
1576 l_rslt_dt := trunc(l_eff_dt-1);
1577
1578 --
1579 end if;
1580
1581
1582 -- for bug 1409185 the folowing date code are added
1583 -- The curent date deducted by the day No of current date and the requred day no (sun -1,sat-7)
1584 -- so that will get the date of the day in the current week
1585 -- if return date is current date or more then a week (7) deducted from that so it will retunr to last
1586 -- week. 0.99 used to find the maximum because the current date will return 0 and that is to be decutedw-- with 7 so .99 is validated
1587 --
1588 elsif p_ext_date_cd = 'PM' then
1589 -- Perivious Monday
1590 select trunc(p_abs_date-(to_number(to_char((p_abs_date),'D')) -2)
1591 -decode(greatest((to_number(to_char(p_abs_date,'D')) -2),0.99) ,0.99,7,0))
1592 into l_rslt_dt from dual ;
1593
1594 elsif p_ext_date_cd = 'PT' then
1595 -- Perious Tuesday
1596 select trunc(p_abs_date-(to_number(to_char((p_abs_date),'D')) -3)
1597 -decode(greatest((to_number(to_char(p_abs_date,'D')) -3),0.99) ,0.99,7,0))
1598 into l_rslt_dt from dual ;
1599 elsif p_ext_date_cd = 'PW' then
1600 -- Perivious Wednesday
1601 select trunc(p_abs_date-(to_number(to_char((p_abs_date),'D')) -4)
1602 -decode(greatest((to_number(to_char(p_abs_date,'D')) -4),0.99) ,0.99,7,0))
1603 into l_rslt_dt from dual ;
1604
1605 elsif p_ext_date_cd = 'PTH' then
1606 -- Perivious THURSDAY
1607 select trunc(p_abs_date-(to_number(to_char((p_abs_date),'D')) -5)
1608 -decode(greatest((to_number(to_char(p_abs_date,'D')) -5),0.99) ,0.99,7,0))
1609 into l_rslt_dt from dual ;
1610
1611 elsif p_ext_date_cd = 'PF' then
1612 -- Perivious FRIDAY
1613 select trunc(p_abs_date-(to_number(to_char((p_abs_date),'D')) -6)
1614 -decode(greatest((to_number(to_char(p_abs_date,'D')) -6),0.99) ,0.99,7,0))
1615 into l_rslt_dt from dual ;
1616
1617 elsif p_ext_date_cd = 'PSA' then
1618 -- Perivious SATURDAY
1619 select trunc(p_abs_date-(to_number(to_char((p_abs_date),'D')) -7)
1620 -decode(greatest((to_number(to_char(p_abs_date,'D')) -7),0.99) ,0.99,7,0))
1621 into l_rslt_dt from dual ;
1622
1623 elsif p_ext_date_cd = 'PSU' then
1624 -- Perivious SUNDAY
1625 select trunc(p_abs_date-(to_number(to_char((p_abs_date),'D')) -8)
1626 -decode(greatest((to_number(to_char(p_abs_date,'D')) -8),0.99) ,0.99,7,0))
1627 into l_rslt_dt from dual ;
1628 --RCHASE - Bug 1550072 - Add new date calc codes
1629 -- tilak changed the sysdate to p_abs_date
1630 --added Next day - NM, NT, NW, NTH, NF, NSA, NSU
1631 --added Day of next week - MONW, TONW, WONW, THONW, FONW, SAONW, SUONW
1632 --added Day of current week - MOCW, TOCW, WOCW, THOCW, FOCW, SAOCW, SUOCW
1633 --added LPSME15RL, FPSMS1R16
1634 --added LCSME15RL, FCSMS1R16
1635 elsif p_ext_date_cd = 'NM' then
1636 -- Next Monday
1637 select trunc(p_abs_date-(to_number(to_char((p_abs_date),'D')) -2)
1638 +decode(greatest(2-(to_number(to_char(p_abs_date,'D'))),0.99) ,0.99,7,0))
1639 into l_rslt_dt from dual;
1640 elsif p_ext_date_cd = 'NT' then
1641 -- Next Tuesday
1642 select trunc(p_abs_date-(to_number(to_char((p_abs_date),'D')) -3)
1643 +decode(greatest(3-(to_number(to_char(p_abs_date,'D'))),0.99) ,0.99,7,0))
1644 into l_rslt_dt from dual;
1645 elsif p_ext_date_cd = 'NW' then
1646 -- Next Wednesday
1647 select trunc(p_abs_date-(to_number(to_char((p_abs_date),'D')) -4)
1648 +decode(greatest(4-(to_number(to_char(p_abs_date,'D'))),0.99) ,0.99,7,0))
1649 into l_rslt_dt from dual;
1650 elsif p_ext_date_cd = 'NTH' then
1651 -- Next THURSDAY
1652 select trunc(p_abs_date-(to_number(to_char((p_abs_date),'D')) -5)
1653 +decode(greatest(5-(to_number(to_char(p_abs_date,'D'))),0.99) ,0.99,7,0))
1654 into l_rslt_dt from dual;
1655 elsif p_ext_date_cd = 'NF' then
1656 -- Next FRIDAY
1657 select trunc(p_abs_date-(to_number(to_char((p_abs_date),'D')) -6)
1658 +decode(greatest(6-(to_number(to_char(p_abs_date,'D'))),0.99) ,0.99,7,0))
1659 into l_rslt_dt from dual;
1660 elsif p_ext_date_cd = 'NSA' then
1661 -- Next SATURDAY
1662 select trunc(p_abs_date-(to_number(to_char((p_abs_date),'D')) -7)
1663 +decode(greatest(7-(to_number(to_char(p_abs_date,'D'))),0.99) ,0.99,7,0))
1664 into l_rslt_dt from dual;
1665 elsif p_ext_date_cd = 'NSU' then
1666 -- Next SUNDAY
1667 select trunc(p_abs_date-(to_number(to_char((p_abs_date),'D')) -1)
1668 +decode(greatest(1-(to_number(to_char(p_abs_date,'D'))),0.99) ,0.99,7,0))
1669 into l_rslt_dt from dual;
1670 elsif p_ext_date_cd = 'MONW' then
1671 -- Monday of next week
1672 l_rslt_dt := trunc(p_abs_date+(2 -to_number(to_char(p_abs_date,'D'))+7));
1673 elsif p_ext_date_cd = 'TONW' then
1674 -- Tuesday of next week
1675 l_rslt_dt := trunc(p_abs_date+(3 -to_number(to_char(p_abs_date,'D'))+7));
1676 elsif p_ext_date_cd = 'WONW' then
1677 -- Wednesday of next week
1678 l_rslt_dt := trunc(p_abs_date+(4 -to_number(to_char(p_abs_date,'D'))+7));
1679 elsif p_ext_date_cd = 'THONW' then
1680 -- Thursday of next week
1681 l_rslt_dt := trunc(p_abs_date+(5 -to_number(to_char(p_abs_date,'D'))+7));
1682 elsif p_ext_date_cd = 'FONW' then
1683 -- Friday of next week
1684 l_rslt_dt := trunc(p_abs_date+(6 -to_number(to_char(p_abs_date,'D'))+7));
1685 elsif p_ext_date_cd = 'SAONW' then
1686 -- Saturday of next week
1687 l_rslt_dt := trunc(p_abs_date+(7 -to_number(to_char(p_abs_date,'D'))+7));
1688 elsif p_ext_date_cd = 'SUONW' then
1689 -- Sunday of next week
1690 l_rslt_dt := trunc(p_abs_date+(1 -to_number(to_char(p_abs_date,'D'))+7));
1691 elsif p_ext_date_cd = 'MOCW' then
1692 -- Monday of Current Week
1693 l_rslt_dt := trunc(p_abs_date+(2-to_number(to_char(p_abs_date,'D'))));
1694 elsif p_ext_date_cd = 'TOCW' then
1695 -- Tuesday of Current Week
1696 l_rslt_dt := trunc(p_abs_date+(3-to_number(to_char(p_abs_date,'D'))));
1697 elsif p_ext_date_cd = 'WOCW' then
1698 -- Wednesday of Current Week
1699 l_rslt_dt := trunc(p_abs_date+(4-to_number(to_char(p_abs_date,'D'))));
1700 elsif p_ext_date_cd = 'THOCW' then
1701 -- Thursday of Current Week
1702 l_rslt_dt := trunc(p_abs_date+(5-to_number(to_char(p_abs_date,'D'))));
1703 elsif p_ext_date_cd = 'FOCW' then
1704 -- Friday of Current Week
1705 l_rslt_dt := trunc(p_abs_date+(6-to_number(to_char(p_abs_date,'D'))));
1706 elsif p_ext_date_cd = 'SAOCW' then
1707 -- Saturday of Current Week
1708 l_rslt_dt := trunc(p_abs_date+(7-to_number(to_char(p_abs_date,'D'))));
1709 elsif p_ext_date_cd = 'SUOCW' then
1710 -- Sunday of Current Week
1711 l_rslt_dt := trunc(p_abs_date+(1-to_number(to_char(p_abs_date,'D'))));
1712 elsif p_ext_date_cd = 'CM15' then
1713 -- 15th of current month
1714 l_rslt_dt := trunc(p_abs_date,'MM')+14;
1715 elsif p_ext_date_cd = 'PM15' then
1716 -- 15 of Previous Month
1717 l_rslt_dt := trunc(add_months(p_abs_date,-1),'MM')+14;
1718 elsif p_ext_date_cd = 'NM15' then
1719 -- 15th of next month
1720 l_rslt_dt := add_months(trunc(p_abs_date,'MM')+14,1);
1721 elsif p_ext_date_cd = 'FPSMS1R16' then
1722 -- First of Prior Semi Month Starting 1st or 16th of Month (Previous 1st or 16th of Month)
1723 -- Tilak :is should go to the previous semi period and pick up the firs date of the period
1724 -- for eg. if i ma in 1 of mar , the perious sem period is feb 16-29 so retunr feb 16
1725 -- if i am on feb 29 the perious period is feb 1- 15 o return feb 1
1726 select trunc(trunc(p_abs_date-15,'MM')
1727 +decode(greatest(to_number(to_char(p_abs_date,'DD')),15.9) , 15.9 , 15,0 )
1728 )
1729 into l_rslt_dt from dual;
1730
1731 elsif p_ext_date_cd = 'FCSMS1R16' then
1732 -- First of current Semi Month Starting 1st or 16th of Month
1733 -- Tilak :is should go to the current semi period and pick up the firs date of the period
1734 select trunc(trunc(p_abs_date,'MM')
1735 +decode(greatest(to_number(to_char(p_abs_date,'DD')),15.9) , 15.9 , 0,15 )
1736 )
1737 into l_rslt_dt from dual;
1738
1739 elsif p_ext_date_cd = 'LPSME15RL' then
1740 -- Last of Prior Semi Month Ending 15th or Last of Month (Previous 15th or Last Day of Month)
1741 -- like FPSMS1R16 it has to pikcup the last date of the previous semi month
1742 select trunc(p_abs_date,'MM')+decode(greatest(to_number(to_char(p_abs_date,'DD')),15),15, -1, 14)
1743 into l_rslt_dt from dual;
1744
1745 elsif p_ext_date_cd = 'LCSME15RL' then
1746 -- Last of current Semi Month Ending 15th or Last of Month
1747 -- like FCSMS1R16 it has to pikcup the last date of the previous semi month
1748 select decode(greatest(to_number(to_char(p_abs_date,'DD')),15),15,
1749 trunc(p_abs_date,'MM') +14 , trunc(add_months(p_abs_date,1),'MM') -1)
1750 into l_rslt_dt from dual;
1751
1752
1753 elsif p_ext_date_cd = 'CM16' then
1754 -- 16th of current month
1755 l_rslt_dt := trunc(trunc(p_abs_date,'MM')+15);
1756 elsif p_ext_date_cd = 'PM16' then
1757 -- 16th of previous month
1758 l_rslt_dt := trunc(add_months(trunc(p_abs_date,'MM')+15,-1));
1759 elsif p_ext_date_cd = 'NM16' then
1760 -- 16th of next month
1761 l_rslt_dt := trunc(add_months(trunc(p_abs_date,'MM')+15,1));
1762
1763
1764 elsif p_ext_date_cd = 'MOPW' then
1765 -- Perivious monday previous
1766 select trunc(p_abs_date-(to_number(to_char((p_abs_date),'D')) -2)
1767 -decode(greatest((to_number(to_char(p_abs_date,'D')) -2),0.99) ,0.99,14,7))
1768 into l_rslt_dt from dual ;
1769 elsif p_ext_date_cd = 'TOPW' then
1770 -- Perivious TUE previous
1771 select trunc(p_abs_date-(to_number(to_char((p_abs_date),'D')) -3)
1772 -decode(greatest((to_number(to_char(p_abs_date,'D')) -3),0.99) ,0.99,14,7))
1773 into l_rslt_dt from dual ;
1774 elsif p_ext_date_cd = 'WOPW' then
1775 -- Perivious wednesday previous
1776 select trunc(p_abs_date-(to_number(to_char((p_abs_date),'D')) -4)
1777 -decode(greatest((to_number(to_char(p_abs_date,'D')) -4),0.99) ,0.99,14,7))
1778 into l_rslt_dt from dual ;
1779 elsif p_ext_date_cd = 'THOPW' then
1780 -- Perivious thursday of previous
1781 select trunc(p_abs_date-(to_number(to_char((p_abs_date),'D')) -5)
1782 -decode(greatest((to_number(to_char(p_abs_date,'D')) -5),0.99) ,0.99,14,7))
1783 into l_rslt_dt from dual ;
1784 elsif p_ext_date_cd = 'FOPW' then
1785 -- Perivious friday of previous
1786 select trunc(p_abs_date-(to_number(to_char((p_abs_date),'D')) -6)
1787 -decode(greatest((to_number(to_char(p_abs_date,'D')) -6),0.99) ,0.99,14,7))
1788 into l_rslt_dt from dual ;
1789 elsif p_ext_date_cd = 'SAOPW' then
1790 -- Perivious saturday of previous
1791 select trunc(p_abs_date-(to_number(to_char((p_abs_date),'D')) -7)
1792 -decode(greatest((to_number(to_char(p_abs_date,'D')) -7),0.99) ,0.99,14,7))
1793 into l_rslt_dt from dual ;
1794 elsif p_ext_date_cd = 'SUOPW' then
1795 -- Perivious sunday of previous
1796 select trunc(p_abs_date-(to_number(to_char((p_abs_date),'D')) -1)
1797 -decode(greatest((to_number(to_char(p_abs_date,'D')) -1),0.99) ,0.99,14,7))
1798 into l_rslt_dt from dual ;
1799
1800 elsif p_ext_date_cd = 'CM16' then
1801 -- 16th of current month
1802 l_rslt_dt := trunc(trunc(p_abs_date,'MM')+15);
1803 elsif p_ext_date_cd = 'PM16' then
1804 -- 16th of previous month
1805 l_rslt_dt := trunc(add_months(trunc(p_abs_date,'MM')+15,-1));
1806 elsif p_ext_date_cd = 'NM16' then
1807 -- 16th of next month
1808 l_rslt_dt := trunc(add_months(trunc(p_abs_date,'MM')+15,1));
1809
1810 elsif p_ext_date_cd = 'PYSCYSDT' then
1811 -- Plan Year Start date or Calendar Year Start date
1812 open c_pln_yr ;
1813 fetch c_pln_yr into
1814 l_yr_strt_date,
1815 l_yr_end_date ;
1816 close c_pln_yr ;
1817
1818 if l_yr_strt_date is not null then
1819 l_rslt_dt := l_yr_strt_date;
1820 hr_utility.set_location ( ' plan year start date ' , 99 );
1821 else
1822 l_rslt_dt := trunc(p_abs_date, 'YYYY');
1823 end if ;
1824
1825 elsif p_ext_date_cd = 'PYECYEDT' then
1826 -- Plan Year End date or Calendar Year End date
1827 open c_pln_yr ;
1828 fetch c_pln_yr into
1829 l_yr_strt_date,
1830 l_yr_end_date ;
1831 close c_pln_yr ;
1832
1833 if l_yr_end_date is not null then
1834 l_rslt_dt := l_yr_end_date;
1835 hr_utility.set_location ( ' plan year End date ' , 99 );
1836 else
1837 l_rslt_dt := trunc(add_months(p_abs_date, 12), 'YYYY') - 1 ;
1838 end if ;
1839
1840
1841 else
1842 --
1843 ben_ext_thread.g_err_num := 91628;
1844 ben_ext_thread.g_err_name := 'BEN_91628_LOOKUP_TYPE_GENERIC';
1845 raise ben_ext_thread.g_job_failure_error;
1846 --
1847 end if;
1848 --
1849
1850 hr_utility.set_location(' ext_date : '||p_ext_date_cd ||l_rslt_dt, 185);
1851 hr_utility.set_location(' Exiting:'||l_proc, 15);
1852
1853 return (l_rslt_dt);
1854 --
1855
1856 --
1857 --
1858 End calc_ext_date;
1859 --
1860 /*---------------------------------------------------------------/
1861 ==========================================================
1862 --- Descriptio: This function returns a collection of cartesian
1863 --- product of element_entry_id and datetracked_event_id
1864 ---
1865 --- ***********Algorithm************************
1866 --- procedure get_element_entries_for_eg
1867 ---
1868 --- for each element_set_id attahced to the event group
1869 --- fetch all the element_entry_id X dte_id combinations
1870 ---
1871 --- IF there are any purge dte on eg
1872 --- fetch the ele_entry_id X dte_id
1873 --- (using noted_value which is
1874 --- element_type_id of deleted ele entries
1875 --- surrogate_key will be element_entry_id)
1876 --- End if;
1877 ---
1878 --- combine with the above creatd collection checking for uniqueness
1879 ---
1880 --- return the collection
1881 --- end loop;
1882 ---
1883 ==========================================================
1884 */
1885
1886 FUNCTION get_element_entries_for_eg
1887 (p_event_group_id IN NUMBER
1888 ,p_assignment_id IN NUMBER
1889 ,p_start_date IN DATE
1890 ,p_end_date IN DATE
1891 ,p_element_entries_tab OUT NOCOPY g_r_element_entries
1892 ) RETURN NUMBER -- number of element entries in the out param table
1893 IS
1894 l_proc VARCHAR2(70) := g_package||'.get_element_entries_for_eg';
1895 l_purge_dte_id NUMBER;
1896 l_purge_ee_ids t_number;
1897 l_dte_ids t_number;
1898 l_next NUMBER;
1899 l_element_entries_tab t_number;
1900 l_dte_ids_tab t_number;
1901 l_global_env_collection g_r_element_entries;
1902 l_element_set_ids_tab t_number;
1903 l_index NUMBER;
1904 l_match_exists VARCHAR2(10);
1905
1906
1907 CURSOR csr_element_entries
1908 (p_element_set_id IN NUMBER
1909 ,p_event_group_id IN NUMBER
1910 ,p_assignment_id IN NUMBER
1911 ,p_start_date IN DATE
1912 ,p_end_date IN DATE
1913 )
1914 IS
1915 SELECT distinct pee.element_entry_id
1916 FROM pay_element_type_rules petr
1917 ,pay_element_entries_f pee
1918 WHERE petr.element_set_id = p_element_set_id
1919 AND pee.element_type_id = petr.element_type_id
1920 AND pee.assignment_id = p_assignment_id
1921 AND (
1922 pee.effective_start_date <= p_end_date
1923 AND
1924 pee.effective_end_date >= p_start_date
1925 );
1926
1927 -- this is used to check for any datetracked events for purge events on
1928 -- element entries in the event group.
1929 CURSOR csr_get_purge_events_on_eg
1930 IS
1931 SELECT datetracked_event_id
1932 FROM pay_datetracked_events pde
1933 ,pay_dated_tables pdt
1934 WHERE event_group_id = p_event_group_id
1935 AND pde.dated_table_id = pdt.dated_table_id
1936 AND pde.update_type = 'P'
1937 AND pdt.table_name = 'PAY_ELEMENT_ENTRIES_F';
1938
1939
1940 -- this is used to fetch the element entry ids of the
1941 -- puged element entries.
1942 -- the element tntry ids are fetched by comparing the
1943 -- element type id in the element set attached to the
1944 -- event group and the element type id stored in the
1945 -- column 'NOTED_VALUE' of pay_process_events fro purged
1946 -- element entry events.
1947 CURSOR csr_get_purged_ee_ids (p_element_set_id IN NUMBER)
1948 IS
1949 SELECT distinct ppe.surrogate_key
1950 FROM pay_element_type_rules petr
1951 ,pay_process_events ppe
1952 ,pay_event_updates peu
1953 WHERE petr.element_set_id = p_element_set_id
1954 AND ppe.assignment_id = p_assignment_id
1955 AND ppe.noted_value = petr.element_type_id
1956 AND peu.event_update_id = ppe.event_update_id
1957 AND peu.event_type = 'ZAP'
1958 AND ppe.effective_date BETWEEN p_start_date AND p_end_date;
1959
1960
1961 BEGIN
1962 hr_utility.trace('Entering: '||l_proc);
1963 hr_utility.trace('Entered get_element_entries_for_eg: EG_Id:'||to_char(p_event_group_id));
1964 hr_utility.trace('Assignment Id:'||to_char(p_assignment_id));
1965 hr_utility.trace('Start Date:'||to_char(p_start_date, 'DD/MM/YYYY'));
1966 hr_utility.trace('End Date:'||to_char(p_end_date, 'DD/MM/YYYY'));
1967
1968 p_element_entries_tab.element_entry_id.DELETE;
1969 p_element_entries_tab.datetracked_event_id.DELETE;
1970
1971 --- get the ids from cache
1972 IF g_eg_has_purge_dte.EXISTS(p_event_group_id) THEN
1973 hr_utility.trace('Obtained the value from cache: '||g_eg_has_purge_dte(p_event_group_id));
1974 l_purge_dte_id := g_eg_has_purge_dte(p_event_group_id);
1975 END IF;
1976
1977
1978 -- get the element set ids attached to the event group
1979 IF g_ele_set_ids_on_eg.EXISTS(p_event_group_id) THEN
1980 -- found ion the cache
1981 hr_utility.trace('Obtained element set ids from cache');
1982 l_element_set_ids_tab := g_ele_set_ids_on_eg(p_event_group_id);
1983 END IF;
1984
1985 -- get the dte ids of the current event group
1986 IF g_datetraced_event_ids.EXISTS(p_event_group_id) THEN
1987 -- found ion the cache
1988 hr_utility.trace('Obtained element set ids from cache');
1989 l_dte_ids_tab := g_datetraced_event_ids(p_event_group_id);
1990 END IF;
1991 ---
1992
1993 FOR i IN 1..l_element_set_ids_tab.COUNT LOOP
1994 OPEN csr_element_entries
1995 (p_element_set_id => l_element_set_ids_tab(i)
1996 ,p_event_group_id => p_event_group_id
1997 ,p_assignment_id => p_assignment_id
1998 ,p_start_date => p_start_date
1999 ,p_end_date => p_end_date
2000 );
2001 -- nullify the collection
2002 l_element_entries_tab.delete ;
2003 --
2004 FETCH csr_element_entries BULK COLLECT INTO l_element_entries_tab;
2005 CLOSE csr_element_entries;
2006 hr_utility.trace('Count:'||to_char(l_element_entries_tab.COUNT));
2007
2008 -- prepare l_global_env_collection with the ee_ids and dte_ids collections
2009 FOR i IN 1 .. l_dte_ids_tab.count
2010 LOOP
2011
2012 FOR j IN 1 ..l_element_entries_tab.count
2013 LOOP
2014
2015 l_next := nvl(l_global_env_collection.element_entry_id.LAST,0) + 1;
2016
2017 l_global_env_collection.element_entry_id(l_next)
2018 := l_element_entries_tab(j);
2019 l_global_env_collection.datetracked_event_id(l_next)
2020 := l_dte_ids_tab(i);
2021
2022 END LOOP; -- end j loop
2023
2024 END LOOP; -- end i loop
2025
2026
2027
2028 IF nvl(l_purge_dte_id,-1) <> -1 THEN
2029 -- if there are purge events in the event group
2030 hr_utility.trace('There are puge events on element entries table in the eg.');
2031 OPEN csr_get_purged_ee_ids(l_element_set_ids_tab(i));
2032 FETCH csr_get_purged_ee_ids BULK COLLECT INTO l_purge_ee_ids;
2033 CLOSE csr_get_purged_ee_ids;
2034
2035 hr_utility.trace('Fill the values in the element entries collection.');
2036 FOR i IN 1..l_purge_ee_ids.COUNT
2037 LOOP
2038 hr_utility.trace('l_purge_ee_ids(i): '||l_purge_ee_ids(i));
2039 -- bug fix 5368066. nvl is added for this bug fix.
2040 l_next := nvl(l_global_env_collection.element_entry_id.LAST,0) + 1;
2041 l_global_env_collection.element_entry_id(l_next) := fnd_number.canonical_to_number(l_purge_ee_ids(i));
2042 l_global_env_collection.datetracked_event_id(l_next) := l_purge_dte_id;
2043 END LOOP;
2044 END IF;
2045
2046 FOR i IN 1..l_global_env_collection.element_entry_id.COUNT LOOP
2047 IF p_element_entries_tab.element_entry_id.COUNT = 0 THEN
2048 p_element_entries_tab := l_global_env_collection;
2049 EXIT;
2050 ELSE -- count is non zero
2051 l_index := p_element_entries_tab.element_entry_id.LAST;
2052 l_match_exists := 'N';
2053 FOR j IN 1..p_element_entries_tab.element_entry_id.COUNT LOOP
2054 IF p_element_entries_tab.element_entry_id(j) = l_global_env_collection.element_entry_id(i) AND
2055 p_element_entries_tab.datetracked_event_id(j) = l_global_env_collection.datetracked_event_id(i)
2056 THEN
2057 -- Combination exist so do nothing
2058 l_match_exists := 'Y';
2059 EXIT;
2060 END IF; -- End if of match exists check ...
2061 END LOOP; -- j loop
2062 IF l_match_exists = 'N' THEN
2063 -- store the information
2064 l_index := l_index + 1;
2065 p_element_entries_tab.element_entry_id(l_index) := l_global_env_collection.element_entry_id(i);
2066 p_element_entries_tab.datetracked_event_id(l_index) := l_global_env_collection.datetracked_event_id(i);
2067 END IF; -- End if of match does not exist ...
2068 END IF; -- End if of return collection count is zero check ...
2069 END LOOP; -- i loop
2070
2071 END LOOP; -- element set loop ...
2072
2073 hr_utility.trace('Count:'||to_char(p_element_entries_tab.element_entry_id.COUNT));
2074
2075 hr_utility.trace('Leaving: '||l_proc);
2076 RETURN p_element_entries_tab.element_entry_id.COUNT;
2077
2078 EXCEPTION
2079 WHEN OTHERS THEN
2080 -- NOCOPY
2081 p_element_entries_tab.element_entry_id.DELETE;
2082 p_element_entries_tab.datetracked_event_id.DELETE;
2083 RAISE;
2084 END get_element_entries_for_eg;
2085
2086
2087 /*
2088 ==========================================================
2089 --- Description: This procedure is used to
2090 --- 1. Check if there are any element entry related
2091 --- datetracekd events on the event group.
2092 --- 2. If there any such datetracked events
2093 --- a) set a flag in the global g_t_event_element
2094 --- and return the value.
2095 --- b) set the event group level globals:
2096 --- . g_ele_set_ids_on_eg
2097 --- . g_datetraced_event_ids
2098 --- . g_eg_has_purge_dte
2099 ==========================================================
2100 */
2101 Function event_element_exists(p_event_group_id IN NUMBER
2102 ) return varchar2 is
2103 l_return varchar2(1) ;
2104 l_proc VARCHAR2(70);
2105
2106 CURSOR csr_chk_eg_for_ee_tab IS
2107 SELECT 'Y'
2108 FROM pay_datetracked_events pde
2109 ,pay_dated_tables pdt
2110 WHERE event_group_id = p_event_group_id
2111 AND pde.dated_table_id = pdt.dated_table_id
2112 AND (pdt.table_name = 'PAY_ELEMENT_ENTRIES_F'
2113 OR
2114 pdt.table_name = 'PAY_ELEMENT_ENTRY_VALUES_F'
2115 )
2116 AND ROWNUM < 2;
2117
2118
2119 CURSOR csr_get_element_set IS
2120 SELECT element_set_id
2121 FROM pay_event_group_usages
2122 WHERE event_group_id = p_event_group_id;
2123
2124 CURSOR csr_get_dte_ids IS
2125 SELECT datetracked_event_id
2126 FROM pay_datetracked_events pde
2127 WHERE pde.event_group_id = p_event_group_id;
2128
2129
2130 CURSOR csr_get_purge_events_ids IS
2131 SELECT datetracked_event_id
2132 FROM pay_datetracked_events pde
2133 ,pay_dated_tables pdt
2134 WHERE event_group_id = p_event_group_id
2135 AND pde.dated_table_id = pdt.dated_table_id
2136 AND pde.update_type = 'P'
2137 AND pdt.table_name = 'PAY_ELEMENT_ENTRIES_F';
2138
2139
2140
2141 l_element_set_ids_tab t_number;
2142 l_dte_ids_tab t_number;
2143 l_purge_dte_id number;
2144
2145 Begin
2146 l_proc := g_package||'.event_element_exists';
2147 l_return := 'N' ;
2148 hr_utility.set_location('Entering'||l_proc, 5);
2149
2150 -- for every person the event are executed
2151 -- instead of getting the value from cursor
2152 -- the values are cached so one event executes once for a theread
2153
2154 if g_t_event_element.EXISTS(p_event_group_id) then
2155 l_return := g_t_event_element(p_event_group_id) ;
2156 else
2157 open csr_chk_eg_for_ee_tab ;
2158 fetch csr_chk_eg_for_ee_tab into l_return ;
2159 if csr_chk_eg_for_ee_tab%notfound then
2160 l_return := 'N' ;
2161 end if ;
2162 close csr_chk_eg_for_ee_tab ;
2163 g_t_event_element(p_event_group_id) := l_return ;
2164
2165 -- when the element exists
2166 if l_return = 'Y' then
2167
2168 hr_utility.trace('element set ids in cache');
2169 OPEN csr_get_element_set ;
2170 FETCH csr_get_element_set BULK COLLECT INTO l_element_set_ids_tab;
2171 CLOSE csr_get_element_set;
2172
2173 --put the element set ids in the cache
2174
2175 g_ele_set_ids_on_eg(p_event_group_id) := l_element_set_ids_tab;
2176 hr_utility.trace('Count:'||to_char(l_element_set_ids_tab.COUNT));
2177
2178 hr_utility.trace('date treac event ids in cache');
2179 OPEN csr_get_dte_ids ;
2180 FETCH csr_get_dte_ids BULK COLLECT INTO l_dte_ids_tab;
2181 CLOSE csr_get_dte_ids;
2182 --put the element set ids in the cache
2183 g_datetraced_event_ids(p_event_group_id) := l_dte_ids_tab;
2184
2185
2186 hr_utility.trace('date treack purge event ids in cache');
2187 OPEN csr_get_purge_events_ids;
2188 FETCH csr_get_purge_events_ids into l_purge_dte_id;
2189 CLOSE csr_get_purge_events_ids;
2190 g_eg_has_purge_dte(p_event_group_id) := nvl(l_purge_dte_id,-1);
2191
2192
2193 end if ;
2194 end if ;
2195
2196 hr_utility.set_location('Exiting '|| l_return ||l_proc, 10);
2197 Return l_return ;
2198
2199 End event_element_exists ;
2200
2201 /*
2202 ==========================================================
2203 --- Description: This is a wrapper procedure on pay_interpreter_pkg.entries_affected
2204 --- pay_interpreter_pkg.entry_affected.
2205 --- Depending upon the elements entries on the assignment
2206 --- which are of type of elements which are attached to
2207 --- the element set which are attached to the event group
2208 --- usages, this procedure calls entries_affected or entry_affected
2209 --- and returns the table of events for the event group during the
2210 --- date range specified
2211 ---
2212 --- ***********Algorithm************************
2213 --- procedure entries_affected
2214 ---
2215 --- Check the event group for datetracked events on element entries
2216 ---
2217 --- IF there are DTE on element entries THEN
2218 --- get ee_id X de_id from get_element_entries_for_eg;
2219 --- populate global_env using the above collection
2220 --- call entreis_affected using global_env
2221 --- ELSE
2222 --- call entry_affected (normal procedure)
2223 --- END;
2224 ---
2225 ==========================================================
2226 */
2227 PROCEDURE entries_affected
2228 (p_assignment_id IN NUMBER DEFAULT NULL
2229 ,p_event_group_id IN NUMBER DEFAULT NULL
2230 ,p_mode IN VARCHAR2 DEFAULT NULL
2231 ,p_start_date IN DATE DEFAULT hr_api.g_sot
2232 ,p_end_date IN DATE DEFAULT hr_api.g_eot
2233 ,p_business_group_id IN NUMBER
2234 ,p_detailed_output OUT NOCOPY pay_interpreter_pkg.t_detailed_output_table_type
2235 ,p_process_mode IN VARCHAR2 DEFAULT 'ENTRY_CREATION_DATE'
2236 ,p_penserv_mode IN VARCHAR2 DEFAULT 'N' --vkodedal changes for penserver - 30-apr-2008
2237 )
2238 IS
2239
2240 l_proc VARCHAR2(70) := g_package||'.entries_affected';
2241 l_datetrack_ee_tab g_r_element_entries;
2242 l_count NUMBER := 0;
2243 l_global_env pay_interpreter_pkg.t_global_env_rec;
2244 l_proration_dates pay_interpreter_pkg.t_proration_dates_table_type;
2245 l_proration_changes pay_interpreter_pkg.t_proration_type_table_type;
2246 l_pro_type_tab pay_interpreter_pkg.t_proration_type_table_type;
2247 l_eg_has_ee_tab VARCHAR2(1);
2248
2249
2250 BEGIN --entries_effected
2251 hr_utility.trace('Entering: '||l_proc);
2252 hr_utility.trace('Get the element entries for the assignment id');
2253
2254 -- Bugfix 4739067: Performance enhancement
2255 -- Checking if the event group has element entries or
2256 -- element entry values table before trying to fetch events
2257 -- If the EG does not have EE tables, we use the entry_affected call
2258 -- further the cursor cached to get a better performance
2259
2260 IF event_element_exists(p_event_group_id) = 'Y' THEN
2261 l_count := get_element_entries_for_eg
2262 (p_event_group_id => p_event_group_id
2263 ,p_assignment_id => p_assignment_id
2264 ,p_start_date => p_start_date
2265 ,p_end_date => p_end_date
2266 ,p_element_entries_tab => l_datetrack_ee_tab
2267 );
2268 ELSE
2269 l_count := 0;
2270 END IF;
2271
2272 -----
2273 -- This line can be removed after fix from pay for missing events on mix of calls to
2274 -- entry_affected and entries_affected - kkarri
2275 pay_interpreter_pkg.t_distinct_tab := pay_interpreter_pkg.glo_monitored_events;
2276 -----
2277 IF l_count > 0 THEN
2278 hr_utility.trace('Our procedure');
2279 hr_utility.trace('Setup the global area');
2280 pay_interpreter_pkg.initialise_global(l_global_env);
2281 pay_interpreter_pkg.event_group_tables
2282 (p_event_group_id => p_event_group_id
2283 ,p_distinct_tab => pay_interpreter_pkg.glo_monitored_events
2284 );
2285 --The start and end pointers can be just for the event group.
2286 -- So, commenting out these lines. - kkarri
2287 /*l_global_env.monitor_start_ptr := 1;
2288 l_global_env.monitor_end_ptr := pay_interpreter_pkg.glo_monitored_events.count;*/
2289 l_global_env.monitor_start_ptr
2290 := pay_interpreter_pkg.t_proration_group_tab(p_event_group_id).range_start;
2291 l_global_env.monitor_end_ptr
2292 := pay_interpreter_pkg.t_proration_group_tab(p_event_group_id).range_end;
2293 ---
2294 l_global_env.datetrack_ee_tab_use := TRUE;
2295 l_global_env.validate_run_actions := FALSE;
2296 hr_utility.trace(' call add_datetrack_event_to_entry for collection ');
2297
2298 FOR i IN l_datetrack_ee_tab.element_entry_id.FIRST..l_datetrack_ee_tab.element_entry_id.LAST
2299 LOOP
2300 hr_utility.trace('----------------------------------');
2301 hr_utility.trace('i: '||i);
2302 hr_utility.trace('datetracked_event_id: '||l_datetrack_ee_tab.datetracked_event_id(i));
2303 hr_utility.trace('element_entry_id: '||l_datetrack_ee_tab.element_entry_id(i));
2304 pay_interpreter_pkg.add_datetrack_event_to_entry
2305 (p_datetracked_evt_id => l_datetrack_ee_tab.datetracked_event_id(i)
2306 ,p_element_entry_id => l_datetrack_ee_tab.element_entry_id(i)
2307 ,p_global_env => l_global_env
2308 );
2309 END LOOP;
2310 hr_utility.trace('Entered all the dte_id X ee_ids');
2311
2312 BEGIN
2313 --call entries_effected
2314 hr_utility.trace('element call to entries_effected');
2315 pay_interpreter_pkg.entries_affected
2316 (p_assignment_id => p_assignment_id
2317 ,p_mode => p_mode
2318 ,p_start_date => p_start_date
2319 ,p_end_date => p_end_date
2320 ,p_business_group_id => p_business_group_id
2321 ,p_global_env => l_global_env
2322 ,t_detailed_output => p_detailed_output
2323 ,p_process_mode => p_process_mode
2324 ,p_penserv_mode => p_penserv_mode --vkodedal changes for penserver - 30-apr-2008
2325 );
2326 EXCEPTION
2327 WHEN NO_DATA_FOUND THEN
2328 hr_utility.trace('No payroll run for the assignment');
2329 hr_utility.set_message(8303,'BEN_94629_NO_ASG_ACTION_ID');
2330 hr_utility.raise_error;
2331 END;
2332 -- reset l_global_env
2333 pay_interpreter_pkg.clear_dt_event_for_entry
2334 (p_global_env => l_global_env);
2335 ELSE
2336 hr_utility.trace('Normal call to entries_effected');
2337 --call entry_affected
2338 pay_interpreter_pkg.entry_affected(
2339 p_element_entry_id => NULL
2340 ,p_assignment_action_id => NULL
2341 ,p_assignment_id => p_assignment_id
2342 ,p_mode => p_mode
2343 ,p_process => NULL -- 'U' --
2344 ,p_event_group_id => p_event_group_id
2345 ,p_process_mode => p_process_mode
2346 ,p_start_date => p_start_date
2347 ,p_end_date => p_end_date
2348 ,t_detailed_output => p_detailed_output -- OUT
2349 ,t_proration_dates => l_proration_dates -- OUT
2350 ,t_proration_change_type => l_proration_changes -- OUT
2351 ,t_proration_type => l_pro_type_tab -- OUT
2352 ,p_penserv_mode => p_penserv_mode --vkodedal changes for penserver - 30-apr-2008
2353 );
2354 END IF;
2355 hr_utility.trace('Leaving: '||l_proc);
2356 END entries_affected;
2357
2358 --
2359 END BEN_EXT_UTIL;
[Home] [Help]