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