1 Package Body PQP_Pension_Extracts As
2 /* $Header: pqglpext.pkb 120.2 2007/03/26 19:26:52 tjesumic noship $ */
3
4 g_proc_name CONSTANT VARCHAR2(200) :='PQP_Pension_Extracts.';
5 g_debug BOOLEAN := FALSE;
6 -- =============================================================================
7 -- Cursor to get all the element type ids from an element set
8 -- =============================================================================
9 CURSOR csr_ele_id (c_element_set_id IN NUMBER) IS
10 SELECT DISTINCT petr.element_type_id
11 FROM pay_element_type_rules petr
12 WHERE petr.element_set_id = c_element_set_id
13 AND petr.include_or_exclude = 'I'
14 UNION ALL
15 SELECT DISTINCT pet1.element_type_id
16 FROM pay_element_types_f pet1
17 WHERE pet1.classification_id IN
18 (SELECT classification_id
19 FROM pay_ele_classification_rules
20 WHERE element_set_id = c_element_set_id)
21 MINUS
22 SELECT DISTINCT petr.element_type_id
23 FROM pay_element_type_rules petr
24 WHERE petr.element_set_id = c_element_set_id
25 AND petr.include_or_exclude = 'E';
26
27 -- =============================================================================
28 -- Cursor to get input value id for a give element type id and input name
29 -- =============================================================================
30 CURSOR csr_inv (c_input_name IN VARCHAR2
31 ,c_element_type_id IN NUMBER
32 ,c_effective_date IN DATE
33 ,c_business_group_id IN NUMBER
34 ,c_legislation_code IN VARCHAR2 ) IS
35 SELECT piv.input_value_id
36 FROM pay_input_values_f piv
37 WHERE piv.NAME = c_input_name
38 AND piv.element_type_id = c_element_type_id
39 AND (piv.business_group_id = c_business_group_id OR
40 piv.legislation_code = c_legislation_code)
41 AND c_effective_date BETWEEN piv.effective_start_date
42 AND piv.effective_end_date;
43 -- =============================================================================
44 -- Cursor to get assignment details
45 -- =============================================================================
46 CURSOR csr_assig (c_assignment_id IN NUMBER
47 ,c_effective_date IN DATE
48 ,c_business_group_id IN NUMBER) IS
49 SELECT paf.person_id
50 ,paf.organization_id
51 ,paf.assignment_type
52 ,paf.effective_start_date
53 ,paf.effective_end_date
54 ,'NO'
55 ,ast.user_status
56 ,hr_general.decode_lookup
57 ('EMP_CAT',
58 paf.employment_category) employment_category
59 ,paf.normal_hours
60 ,pps.date_start
61 ,pps.actual_termination_date
62 FROM per_all_assignments_f paf,
63 per_periods_of_service pps,
64 per_assignment_status_types ast
65 WHERE paf.assignment_id = c_assignment_id
66 AND pps.period_of_service_id(+) = paf.period_of_service_id
67 AND ast.assignment_status_type_id = paf.assignment_status_type_id
68 AND c_effective_date BETWEEN paf.effective_start_date
69 AND paf.effective_end_date
70 AND paf.business_group_id = c_business_group_id;
71 -- =============================================================================
72 -- Cursor to get the Asg_Run defined balance id for a given balance name
73 -- =============================================================================
74 CURSOR csr_asg_balid (c_balance_type_id IN NUMBER
75 ,c_balance_dimension_id IN NUMBER
76 ,c_business_group_id IN NUMBER) IS
77 SELECT db.defined_balance_id
78 FROM pay_defined_balances db
79 WHERE db.balance_type_id = c_balance_type_id
80 AND db.balance_dimension_id = c_balance_dimension_id
81 AND (db.business_group_id = c_business_group_id OR
82 db.legislation_code = g_legislation_code);
83 -- =============================================================================
84 -- Cursor to get all assig.actions for a given assig. within a data range
85 -- =============================================================================
86 CURSOR csr_asg_act (c_assignment_id IN NUMBER
87 ,c_payroll_id IN NUMBER
88 ,c_con_set_id IN NUMBER
89 ,c_start_date IN DATE
90 ,c_end_date IN DATE
91 ) IS
92 SELECT paa.assignment_action_id
93 ,ppa.effective_date
94 ,ppa.action_type
95 ,paa.tax_unit_id
96 FROM pay_assignment_actions paa
97 ,pay_payroll_actions ppa
98 WHERE paa.assignment_id = c_assignment_id
99 AND ppa.action_status = 'C'
100 AND paa.action_status = 'C'
101 AND paa.payroll_action_id = ppa.payroll_action_id
102 AND ppa.payroll_id = NVL(c_payroll_id,ppa.payroll_id)
103 AND ppa.consolidation_set_id = NVL(c_con_set_id,ppa.consolidation_set_id)
104 AND ppa.effective_date BETWEEN c_start_date
105 AND c_end_date
106 ORDER BY ppa.effective_date;
107 -- =============================================================================
108 -- MX: Cursor to get all assig.actions for a given assig. within a data range
109 -- =============================================================================
110 CURSOR csr_gre_act (c_assignment_id IN NUMBER
111 ,c_payroll_id IN NUMBER
112 ,c_con_set_id IN NUMBER
113 ,c_gre_id IN NUMBER
114 ,c_start_date IN DATE
115 ,c_end_date IN DATE
116 ) IS
117 SELECT paa.assignment_action_id
118 ,ppa.effective_date
119 ,ppa.action_type
120 ,paa.tax_unit_id
121 FROM pay_assignment_actions paa,
122 pay_payroll_actions ppa,
123 pay_action_classifications pac
124 WHERE paa.assignment_id = c_assignment_id
125 AND (c_gre_id is null or
126 paa.tax_unit_id = nvl(c_gre_id,paa.tax_unit_id)
127 )
128 AND paa.payroll_action_id = ppa.payroll_action_id
129 AND ppa.payroll_id = Nvl(c_payroll_id,ppa.payroll_id)
130 AND ppa.consolidation_set_id = Nvl(c_con_set_id,ppa.consolidation_set_id)
131 AND ppa.action_type = pac.action_type
132 AND pac.classification_name = 'SEQUENCED'
133 AND ppa.effective_date BETWEEN c_start_date
134 AND c_end_date
135 AND (
136 ( nvl(paa.run_type_id,
137 ppa.run_type_id) IS NULL
138 AND paa.source_action_id IS NULL
139 )
140 OR
141 ( nvl(paa.run_type_id,
142 ppa.run_type_id) IS NOT NULL
143 AND paa.source_action_id IS NOT NULL
144 )
145 OR
146 ( ppa.action_type = 'V'
147 AND ppa.run_type_id IS NULL
148 AND paa.run_type_id IS NOT NULL
149 AND paa.source_action_id IS NULL
150 )
151 )
152 ORDER BY paa.action_sequence desc;
153 -- =============================================================================
154 -- Cursor to get all assig.actions for a given assig. within a data range. This
155 -- is same as above and was created to avioded to cursor already open error.
156 -- =============================================================================
157
158 CURSOR csr_bal_act (c_assignment_id IN NUMBER
159 ,c_payroll_id IN NUMBER
160 ,c_con_set_id IN NUMBER
161 ,c_gre_id IN NUMBER
162 ,c_start_date IN DATE
163 ,c_end_date IN DATE
164 ) IS
165 SELECT paa.assignment_action_id
166 ,ppa.effective_date
167 ,ppa.action_type
168 ,paa.tax_unit_id
169 FROM pay_assignment_actions paa,
170 pay_payroll_actions ppa,
171 pay_action_classifications pac
172 WHERE paa.assignment_id = c_assignment_id
173 AND (c_gre_id is null or
174 paa.tax_unit_id = nvl(c_gre_id,paa.tax_unit_id)
175 )
176 AND paa.payroll_action_id = ppa.payroll_action_id
177 AND ppa.payroll_id = Nvl(c_payroll_id,ppa.payroll_id)
178 AND ppa.consolidation_set_id = Nvl(c_con_set_id,ppa.consolidation_set_id)
179 AND ppa.action_type = pac.action_type
180 AND pac.classification_name = 'SEQUENCED'
181 AND ppa.effective_date BETWEEN c_start_date
182 AND c_end_date
183 AND (
184 ( nvl(paa.run_type_id,
185 ppa.run_type_id) IS NULL
186 AND paa.source_action_id IS NULL
187 )
188 OR
189 ( nvl(paa.run_type_id,
190 ppa.run_type_id) IS NOT NULL
191 AND paa.source_action_id IS NOT NULL
192 )
193 OR
194 ( ppa.action_type = 'V'
195 AND ppa.run_type_id IS NULL
196 AND paa.run_type_id IS NOT NULL
197 AND paa.source_action_id IS NULL
198 )
199 )
200 ORDER BY paa.action_sequence desc;
201
202 -- =============================================================================
203 -- Cursor to check if an element has been processed in an assign. action.
204 -- =============================================================================
205 CURSOR csr_ele_run (c_asg_action_id IN NUMBER
206 ,c_element_type_id IN NUMBER
207 ) IS
208 SELECT 'X'
209 FROM pay_run_results prr
210 WHERE prr.assignment_action_id = c_asg_action_id
211 AND prr.element_type_id = c_element_type_id;
212 --AND prr.entry_type IN ('E','V','B')
213 --AND prr.status IN ('P','PA');
214 -- =============================================================================
215 -- Cursor to get the screen entry value of an input value id and element type id
216 -- =============================================================================
217 CURSOR csr_entry (c_effective_date IN DATE
218 ,c_element_type_id IN NUMBER
219 ,c_assignment_id IN NUMBER
220 ,c_input_value_id IN NUMBER) IS
221 SELECT pev.screen_entry_value
222 FROM pay_input_values_f piv
223 ,pay_element_entry_values_f pev
224 ,pay_element_entries_f pee
225 ,pay_element_links_f pel
226 WHERE c_effective_date BETWEEN piv.effective_start_date
227 AND piv.effective_end_date
228 AND c_effective_date BETWEEN pev.effective_start_date
229 AND pev.effective_end_date
230 AND c_effective_date BETWEEN pee.effective_start_date
231 AND pee.effective_end_date
232 AND c_effective_date BETWEEN pel.effective_start_date
233 AND pel.effective_end_date
234 AND pev.input_value_id = piv.input_value_id
235 AND pev.element_entry_id = pee.element_entry_id
236 AND pee.element_link_id = pel.element_link_id
237 AND piv.element_type_id = pel.element_type_id
238 AND pel.element_type_id = c_element_type_id
239 AND pee.assignment_id = c_assignment_id
240 AND piv.input_value_id = c_input_value_id;
241 -- =============================================================================
242 -- Cursor to get the run result value for given input_value_id and
243 -- element_type_id along with assignment_action_id ( for (P)rocessed actions.
244 -- =============================================================================
245 CURSOR csr_run (c_asg_action_id IN NUMBER
246 ,c_element_type_id IN NUMBER
247 ,c_input_value_id IN NUMBER) IS
248 SELECT prv.result_value
249 FROM pay_run_results prr
250 ,pay_run_result_values prv
251 WHERE prr.assignment_action_id = c_asg_action_id
252 AND prr.element_type_id = c_element_type_id
253 AND prv.input_value_id = c_input_value_id
254 AND prv.run_result_id = prr.run_result_id;
255 --AND prr.entry_type IN ('E','V','B')
256 --AND prr.status IN ('P','PA');
257 -- =============================================================================
258 -- Cursor to get the extract record id
259 -- =============================================================================
260 CURSOR csr_ext_rcd_id(c_hide_flag IN VARCHAR2
261 ,c_rcd_type_cd IN VARCHAR2
262 ) IS
263 SELECT rcd.ext_rcd_id
264 FROM ben_ext_rcd rcd
265 ,ben_ext_rcd_in_file rin
266 ,ben_ext_dfn dfn
267 WHERE dfn.ext_dfn_id = ben_ext_thread.g_ext_dfn_id -- The extract executing currently
268 AND rin.ext_file_id = dfn.ext_file_id
269 AND rin.hide_flag = c_hide_flag -- Y=Hidden, N=Not Hidden
270 AND rin.ext_rcd_id = rcd.ext_rcd_id
271 AND rcd.rcd_type_cd = c_rcd_type_cd; -- D=Detail,H=Header,F=Footer
272 -- =============================================================================
273 -- Cursor to get the extract result dtl record for a person id
274 -- =============================================================================
275 CURSOR csr_rslt_dtl(c_person_id IN NUMBER
276 ,c_ext_rslt_id IN NUMBER
277 ,c_ext_dtl_rcd_id IN NUMBER ) IS
278 SELECT *
279 FROM ben_ext_rslt_dtl dtl
280 WHERE dtl.ext_rslt_id = c_ext_rslt_id
281 AND dtl.person_id = c_person_id
282 AND dtl.ext_rcd_id = c_ext_dtl_rcd_id;
283 -- =============================================================================
284 -- Cursor to get the balance type id for a given name
285 -- =============================================================================
286 CURSOR csr_bal_typid (c_balance_name IN VARCHAR2
287 ,c_business_group_id IN NUMBER
288 ,c_legislation_code IN VARCHAR2) IS
289 SELECT pbt.balance_type_id
290 FROM pay_balance_types pbt
291 WHERE pbt.balance_name = c_balance_name
292 AND (pbt.business_group_id = c_business_group_id
293 OR
294 pbt.legislation_code = c_legislation_code);
295 -- =============================================================================
296 -- Cursor to ids for a given assignment_id
297 -- =============================================================================
298 CURSOR csr_asg (c_assignment_id IN NUMBER
299 ,c_effective_date IN DATE) IS
300 SELECT paf.person_id
301 ,paf.grade_id
302 ,paf.job_id
303 ,paf.location_id
304 ,paf.assignment_id
305 FROM per_all_assignments_f paf
306 WHERE paf.assignment_id = c_assignment_id
307 AND paf.business_group_id = g_business_group_id
308 AND c_effective_date BETWEEN paf.effective_start_date
309 AND paf.effective_end_date;
310 -- =============================================================================
311 -- Cursor to employement dates and status for a given person_id
312 -- =============================================================================
313 CURSOR csr_per_dates (c_effective_date IN DATE
314 ,c_person_id IN NUMBER) IS
315 SELECT paf.person_type_id
316 ,ppt.system_person_type
317 ,pps.actual_termination_date
318 ,pps.date_start
319 ,paf.original_date_of_hire
320 FROM per_all_people_f paf
321 ,per_person_types ppt
322 ,per_periods_of_service pps
323 WHERE paf.person_id = c_person_id
324 AND ppt.person_type_id = paf.person_type_id
325 AND pps.business_group_id = g_business_group_id
326 AND paf.business_group_id = g_business_group_id
327 AND pps.person_id = paf.person_id
328 AND c_effective_date BETWEEN paf.effective_start_date
329 AND paf.effective_end_date
330 AND c_effective_date BETWEEN pps.date_start
331 AND Nvl(pps.actual_termination_date,
332 To_Date('31/12/4712','DD/MM/YYYY'));
333 -- =============================================================================
334 -- Cursor to Get the element_type_id and input_value_id for a given elemeny
335 -- name and input name along with bus. group id and Leg. Code.
336 -- =============================================================================
337 CURSOR csr_ele_ipv (c_element_name IN VARCHAR2
338 ,c_input_name IN VARCHAR2
339 ,c_effective_date IN DATE
340 ,c_business_group_id IN NUMBER
341 ,c_legislation_code IN VARCHAR2) IS
342 SELECT pet.element_type_id
343 ,piv.input_value_id
344 FROM pay_element_types_f pet
345 ,pay_input_values_f piv
346 WHERE pet.element_name = c_element_name
347 AND piv.NAME = c_input_name
348 AND (pet.business_group_id = c_business_group_id OR
349 pet.legislation_code = c_legislation_code)
350 AND (piv.business_group_id = c_business_group_id OR
351 piv.legislation_code = c_legislation_code)
352 AND piv.element_type_id = pet.element_type_id
353 AND c_effective_date BETWEEN pet.effective_start_date
354 AND pet.effective_end_date
355 AND c_effective_date BETWEEN piv.effective_start_date
356 AND piv.effective_end_date;
357 -- =============================================================================
358 -- Cursor to chk for other primary assig. within the extract date range. This
359 -- cursor fetchs the secondary as well as terminated assigs within the extract
360 -- date range.
361 -- =============================================================================
362 CURSOR csr_sec_assg
363 (c_primary_assignment_id IN per_all_assignments_f.assignment_id%TYPE
364 ,c_person_id IN per_all_people_f.person_id%TYPE
365 ,c_effective_date IN DATE
366 ,c_extract_start_date IN DATE
367 ,c_extract_end_date IN DATE ) IS
368 SELECT asg.person_id
369 ,asg.organization_id
370 ,asg.assignment_type
371 ,asg.effective_start_date
372 ,asg.effective_end_date
373 ,asg.primary_flag
374 ,asg.assignment_id
375 FROM per_all_assignments_f asg
376 WHERE asg.person_id = c_person_id
377 AND asg.assignment_id <> c_primary_assignment_id
378 AND asg.assignment_type ='E'
379 AND (( c_effective_date BETWEEN asg.effective_start_date
380 AND asg.effective_end_date
381 )
382 OR
383 ( asg.effective_end_date =
384 (SELECT MAX(asx.effective_end_date)
385 FROM per_all_assignments_f asx
386 WHERE asx.assignment_id = asg.assignment_id
387 AND asx.person_id = c_person_id
388 AND asx.assignment_type = 'E'
389 AND ((asx.effective_end_date BETWEEN c_extract_start_date
390 AND c_extract_end_date)
391 OR
392 (asx.effective_start_date BETWEEN c_extract_start_date
393 AND c_extract_end_date)
394 )
395 )
396 )
397 )
398 ORDER BY asg.effective_start_date ASC;
399 -- =============================================================================
400 -- Cursor to check if there are any change events within the given date range
401 -- =============================================================================
402 CURSOR csr_chk_log (c_person_id IN NUMBER
403 ,c_ext_start_date IN DATE
404 ,c_ext_end_date IN DATE ) IS
405 SELECT 'x'
406 FROM ben_ext_chg_evt_log
407 WHERE person_id = c_person_id
408 AND business_group_id = g_business_group_id
409 AND (chg_eff_dt BETWEEN c_ext_start_date
410 AND c_ext_end_date
411 OR
412 chg_actl_dt BETWEEN c_ext_start_date
413 AND c_ext_end_date);
414 -- =============================================================================
415 -- Based on result id and Ext. Dfn Id, get the con. request id
416 -- =============================================================================
417
418 CURSOR csr_req_id
419 (c_ext_rslt_id IN ben_ext_rslt.ext_rslt_id%TYPE
420 ,c_ext_dfn_id IN ben_ext_rslt.ext_dfn_id%TYPE
421 ,c_business_group_id IN ben_ext_rslt.business_group_id%TYPE) IS
422 SELECT request_id
423 FROM ben_ext_rslt
424 WHERE ext_rslt_id = c_ext_rslt_id
425 AND ext_dfn_id = c_ext_dfn_id
426 AND business_group_id = c_business_group_id;
427
428 -- =============================================================================
429 -- Get the benefit action details
430 -- =============================================================================
431 CURSOR csr_ben (c_ext_dfn_id IN NUMBER
432 ,c_ext_rslt_id IN NUMBER
433 ,c_business_group_id IN NUMBER) IS
434 SELECT ben.pgm_id
435 ,ben.pl_id
436 ,ben.benefit_action_id
437 ,ben.business_group_id
438 ,ben.process_date
439 ,ben.request_id
440 FROM ben_benefit_actions ben
441 WHERE ben.pl_id = c_ext_rslt_id
442 AND ben.pgm_id = c_ext_dfn_id
443 AND ben.business_group_id = c_business_group_id;
444
445 -- =============================================================================
446 -- ~ Get_Leg_Code:
447 -- =============================================================================
448 FUNCTION Get_Leg_Code
449 (p_business_group_id IN NUMBER) RETURN VARCHAR2 IS
450
451 CURSOR csr_bg_code (c_business_group_id IN NUMBER)IS
452 SELECT legislation_code
453 FROM per_business_groups_perf
454 WHERE business_group_id = c_business_group_id;
455 l_proc_name CONSTANT VARCHAR2(150) := g_proc_name ||'Get_Leg_Code';
456
457 BEGIN
458 hr_utility.set_location('Entering: '||l_proc_name, 5);
459
460 OPEN csr_bg_code (p_business_group_id);
461 FETCH csr_bg_code INTO g_legislation_code;
462 CLOSE csr_bg_code;
463
464 hr_utility.set_location('Leaving: '||l_proc_name, 80);
465 RETURN g_legislation_code;
466
467 END Get_Leg_Code;
468 -- =============================================================================
469 -- Chk_IfAsg_Valid:
470 -- =============================================================================
471 FUNCTION Chk_IfAsg_Valid
472 (p_assignment_id IN NUMBER
473 ,p_business_group_id IN NUMBER
474 ,p_person_id IN NUMBER
475 ,p_effective_date IN DATE
476
477 ) RETURN VARCHAR2 IS
478
479 i NUMBER(15);
480 l_ele_type_id NUMBER(15);
481 l_prev_ele_type_id NUMBER(15);
482
483 l_return_value VARCHAR2(2);
484 l_valid_action VARCHAR2(2);
485 l_valid_person_chk VARCHAR2(2);
486
487 l_proc_name CONSTANT VARCHAR2(150) := g_proc_name ||'Chk_IfAsg_Valid';
488 BEGIN
489 i := p_business_group_id;
490 l_return_value := 'N';
491 hr_utility.set_location('Entering: '||l_proc_name, 5);
492 --
493 -- Check the person id is eligible for passed orgid ,persontype id,
494 -- GRE Org Id and location id.
495 --
496 l_valid_person_chk :=
497 Chk_Person_Asg
498 (p_assignment_id => p_assignment_id
499 ,p_person_id => p_person_id
500 ,p_bus_grp_id => p_business_group_id
501 ,p_org_id => g_extract_params(i).org_id
502 ,p_gre_org_id => g_extract_params(i).gre_org_id
503 ,p_person_type_id => g_extract_params(i).person_type_id
504 ,p_location_id => g_extract_params(i).location_id
505 ,p_effective_date => least(g_extract_params(i).extract_end_date,
506 p_effective_date)
507 );
508 hr_utility.set_location(' l_valid_person_chk: '||l_valid_person_chk, 6);
509 --
510 -- Now check for each assig action within the date-range
511 --
512 << Asg_Action >>
513 FOR act_rec IN csr_gre_act
514 (c_assignment_id => p_assignment_id
515 ,c_payroll_id => g_extract_params(i).payroll_id
516 ,c_con_set_id => g_extract_params(i).con_set_id
517 ,c_gre_id => g_extract_params(i).gre_org_id
518 ,c_start_date => g_extract_params(i).extract_start_date
519 ,c_end_date => g_extract_params(i).extract_end_date
520 )
521 LOOP
522 -- If not valid then exit, i.e. person should not be extracted
523 IF l_valid_person_chk = 'N' THEN
524 EXIT Asg_Action;
525 END IF;
526 --hr_utility.set_location(' assignment_action_id: '||act_rec.assignment_action_id, 6);
527 -- Check for each assignment action within the date range if the element(s)
528 -- have a run result in any of the assignment actions in the date-range.
529 l_ele_type_id := g_element.first;
530 WHILE l_ele_type_id IS NOT NULL
531 LOOP
532 OPEN csr_ele_run (c_asg_action_id => act_rec.assignment_action_id
533 ,c_element_type_id => l_ele_type_id);
534 FETCH csr_ele_run INTO l_valid_action;
535 --hr_utility.set_location(' l_ele_type_id: '||l_ele_type_id, 6);
536 IF csr_ele_run%FOUND THEN
537 CLOSE csr_ele_run;
538 l_return_value := 'Y';
539 --
540 -- If there was atleast one element which was processed in
541 -- the asg action for the given extract date range then
542 -- the assig. id needs to extracted.
543 --
544 EXIT Asg_Action;
545 ELSE
546 CLOSE csr_ele_run;
547 l_prev_ele_type_id := l_ele_type_id;
548 l_ele_type_id := g_element.next(l_prev_ele_type_id);
549 END IF;
550 END LOOP; -- WHILE l_ele_type_id
551
552 END LOOP Asg_Action;
553
554 hr_utility.set_location('..l_return_value: '||l_return_value, 80);
555 hr_utility.set_location('Leaving: '||l_proc_name, 80);
556 RETURN l_return_value;
557 EXCEPTION
558 WHEN OTHERS THEN
559 hr_utility.set_location('SQLERRM: '||sqlerrm, 90);
560 hr_utility.set_location('Leaving: '||l_proc_name, 90);
561 RETURN l_return_value;
562
563 END Chk_IfAsg_Valid;
564
565 -- =============================================================================
566 -- ~ Chk_IfSecAsg_Valid:
567 -- =============================================================================
568 FUNCTION Chk_IfSecAsg_Valid
569 (p_pri_assignment_id IN NUMBER
570 ,p_business_group_id IN NUMBER
571 ,p_person_id IN NUMBER
572 ) RETURN VARCHAR2 IS
573
574 i NUMBER(15);
575 l_effective_date DATE;
576 l_return_value VARCHAR2(2);
577 l_warning_message VARCHAR2(2000);
578 l_error_message VARCHAR2(2000);
579 l_sec_assg_rec csr_sec_assg%ROWTYPE;
580 l_proc_name CONSTANT VARCHAR2(150) := g_proc_name ||'Chk_IfSecAsg_Valid';
581
582 BEGIN
583
584 hr_utility.set_location('Entering: '||l_proc_name, 5);
585 l_return_value := 'N';
586 IF g_processing_addl_asgs THEN
587 RETURN l_return_value;
588 END IF;
589 i := p_business_group_id;
590 FOR sec_asg_rec IN
591 csr_sec_assg
592 (c_primary_assignment_id => p_pri_assignment_id
593 ,c_person_id => p_person_id
594 ,c_effective_date => g_extract_params(i).extract_end_date
595 ,c_extract_start_date => g_extract_params(i).extract_start_date
596 ,c_extract_end_date => g_extract_params(i).extract_end_date)
597 LOOP
598 l_sec_assg_rec := sec_asg_rec;
599 --
600 -- The assignment might have been end dated so need to take the least of
601 -- the extract end and assignment end date.
602 --
603 l_effective_date := least(g_extract_params(i).extract_end_date,
604 l_sec_assg_rec.effective_end_date);
605 IF g_debug THEN
606 hr_utility.set_location('..Checking for assignment : '||
607 l_sec_assg_rec.assignment_id, 7);
608 hr_utility.set_location('..p_effective_date : '||
609 l_effective_date, 7);
610 END IF;
611 -- Call the main criteria function for this assignment to check if its a valid
612 -- assignment that can be reported based on the criteria specified.
613 IF NOT g_primary_assig.EXISTS(l_sec_assg_rec.assignment_id) THEN
614 l_return_value := Chk_IfAsg_Valid
615 (p_assignment_id => l_sec_assg_rec.assignment_id
616 ,p_business_group_id => p_business_group_id
617 ,p_person_id => p_person_id
618 ,p_effective_date => l_effective_date
619 );
620 END IF;
621 EXIT WHEN l_return_value = 'Y';
622
623 END LOOP;
624 hr_utility.set_location('Leaving: '||l_proc_name, 80);
625 RETURN l_return_value;
626
627 EXCEPTION
628 WHEN others THEN
629 hr_utility.set_location('Leaving: '||l_proc_name, 90);
630 RETURN l_return_value;
631
632 END Chk_IfSecAsg_Valid;
633
634 -- =============================================================================
635 -- ~ Pension_Extract_Process: This is called by the conc. program as is a
636 -- ~ wrapper around the benefits conc. program Extract Process.
637 -- =============================================================================
638 PROCEDURE Pension_Extract_Process
639 (errbuf OUT NOCOPY VARCHAR2
640 ,retcode OUT NOCOPY VARCHAR2
641 ,p_benefit_action_id IN NUMBER
642 ,p_ext_dfn_id IN NUMBER
643 ,p_ext_dfn_data_typ IN VARCHAR2
644 ,p_reporting_dimension IN VARCHAR2
645 ,p_is_fullprofile_data_typ IN VARCHAR2
646 ,p_selection_criteria IN VARCHAR2
647 ,p_is_element_set IN VARCHAR2
648 ,p_element_set_id IN NUMBER
649 ,p_is_element IN VARCHAR2
650 ,p_element_type_id IN NUMBER
651 ,p_report_dfn_typ_id IN VARCHAR2
652 ,p_start_date IN VARCHAR2
653 ,p_end_date IN VARCHAR2
654 ,p_gre_id IN NUMBER
655 ,p_payroll_id IN NUMBER
656 ,p_con_ext_dfn_typ_id IN VARCHAR2
657 ,p_con_is_fullprofile_data_typ IN VARCHAR2
658 ,p_con_set IN NUMBER
659 ,p_business_group_id IN NUMBER
660 ,p_org_id IN NUMBER
661 ,p_person_type_id IN NUMBER
662 ,p_location_id IN NUMBER
663 ,p_ext_rslt_id IN NUMBER DEFAULT NULL ) IS
664
665 l_errbuff VARCHAR2(3000);
666 l_retcode NUMBER;
667 l_session_id NUMBER;
668 l_proc_name VARCHAR2(150) := g_proc_name ||'Pension_Extract_Process';
669
670 BEGIN
671
672 g_debug := hr_utility.debug_enabled;
673
674 hr_utility.set_location('Entering: '||l_proc_name, 5);
675 g_conc_request_id := fnd_global.conc_request_id;
676 SELECT USERENV('SESSIONID') INTO l_session_id FROM dual;
677 DELETE FROM pay_us_rpt_totals
678 WHERE organization_name = 'Global Pension Extracts'
679 AND attribute30 = 'EXTRACT_COMPLETED'
680 AND business_group_id = p_business_group_id
681 AND organization_id = p_business_group_id
682 AND location_id = p_ext_dfn_id;
683
684 -- Insert into pay_us_rpt_totals so that we can refer to these parameters
685 -- when we call the criteria formula for the pension extract.
686 INSERT INTO pay_us_rpt_totals
687 (session_id -- session id
688 ,organization_name -- Conc. Program Name
689 ,business_group_id -- business group id
690 ,organization_id -- -do-
691 ,location_id -- used for perf.
692 ,tax_unit_id -- concurrent request id
693 ,value1 -- extract def. id
694 ,value2 -- element set id
695 ,value3 -- element type id
696 ,value4 -- Payroll Id
697 ,value5 -- GRE Org Id
698 ,value6 -- Consolidation set id
699 ,attribute1 -- Selection Criteria
700 ,attribute2 -- Reporting dimension
701 ,attribute3 -- Extract Start Date
702 ,attribute4 -- Extract End Date
703 ,attribute5 -- Organization Name
704 ,attribute6 -- Person Type
705 ,attribute7 -- Location
706 ,attribute30 -- Status
707 )
708 VALUES
709 (l_session_id
710 ,'Global Pension Extracts'
711 ,p_business_group_id
712 ,p_business_group_id
713 ,p_ext_dfn_id
714 ,g_conc_request_id
715 ,p_ext_dfn_id
716 ,p_element_set_id
717 ,p_element_type_id
718 ,p_payroll_id
719 ,p_gre_id
720 ,p_con_set
721 ,p_selection_criteria
722 ,p_reporting_dimension
723 ,p_start_date
724 ,p_end_date
725 ,p_org_id
726 ,p_person_type_id
727 ,p_location_id
728 ,'EXTRACT_RUNNING'
729 );
730 COMMIT;
731 --
732 -- Call the actual benefit extract process with the effective date as the
733 -- extract end date along with the ext def. id and business group id.
734 --
735 hr_utility.set_location('..Calling Benefit Ext Process'||l_proc_name, 6);
736 ben_ext_thread.process
737 (errbuf => l_errbuff,
738 retcode => l_retcode,
739 p_benefit_action_id => NULL,
740 p_ext_dfn_id => p_ext_dfn_id,
741 p_effective_date => p_end_date,
742 p_business_group_id => p_business_group_id);
743
744 UPDATE pay_us_rpt_totals
745 SET attribute30 = 'EXTRACT_COMPLETED'
746 WHERE organization_name = 'Global Pension Extracts'
747 AND tax_unit_id = g_conc_request_id
748 AND business_group_id = p_business_group_id
749 AND organization_id = p_business_group_id
750 AND location_id = p_ext_dfn_id;
751
752 hr_utility.set_location('Leaving: '||l_proc_name, 80);
753
754 EXCEPTION
755 WHEN Others THEN
756 hr_utility.set_location('Leaving: '||l_proc_name, 90);
757 UPDATE pay_us_rpt_totals
758 SET attribute30 = 'EXTRACT_COMPLETED'
759 WHERE organization_name = 'Global Pension Extracts'
760 AND tax_unit_id = g_conc_request_id
761 AND business_group_id = p_business_group_id
762 AND organization_id = p_business_group_id
763 AND location_id = p_ext_dfn_id;
764
765 RAISE;
766 END Pension_Extract_Process;
767
768 -- =============================================================================
769 -- Get_Element_Info:
770 -- =============================================================================
771 PROCEDURE Get_Element_Info
772 (p_element_type_id IN NUMBER
773 ,p_business_group_id IN NUMBER
774 ,p_effective_date IN DATE) IS
775
776 -- Cursor to get the ele info based on ele type id
777 CURSOR csr_ele_info (c_element_type_id IN NUMBER
778 ,c_effective_date IN DATE
779 ,c_business_group_id IN NUMBER
780 ,c_legislation_code IN VARCHAR2) IS
781 SELECT pet.element_information_category -- Information Category
782 ,pet.element_information1 -- PreTax Category
783 ,pet.element_information10 -- Primary Balance Id
784 ,pet.element_name -- Element Name
785 FROM pay_element_types_f pet
786 WHERE pet.element_type_id = c_element_type_id
787 AND (pet.business_group_id = c_business_group_id OR
788 pet.legislation_code = c_legislation_code)
789 AND c_effective_date BETWEEN pet.effective_start_date
790 AND pet.effective_end_date ;
791
792 -- Cursor to get the balance_type_id for a given balance name
793 CURSOR csr_bal_name (c_balance_type_id IN NUMBER) IS
794 SELECT pbt.balance_name
795 FROM pay_balance_types pbt
796 WHERE pbt.balance_type_id = c_balance_type_id;
797
798 l_input_name pay_input_values_f.NAME%TYPE :='Pay Value';
799 l_input_value_id pay_input_values_f.input_value_id%TYPE;
800 l_element_name pay_element_types_f.element_name%TYPE;
801 l_ele_type_id pay_element_types_f.element_type_id%TYPE;
802 l_balance_name pay_balance_types.balance_name%TYPE;
803 l_balance_type_id pay_balance_types.balance_type_id%TYPE;
804 l_legislation_code per_business_groups.legislation_code%TYPE;
805 l_proc_name VARCHAR2(150) := g_proc_name ||'Get_Element_Info';
806 l_ele_info_rec csr_ele_info%ROWTYPE;
807
808 BEGIN
809 hr_utility.set_location('Entering: '||l_proc_name, 5);
810 l_legislation_code := g_extract_params(p_business_group_id).legislation_code;
811 -- Get the element information details based on the element_type_id,
812 -- effective_date and bus. group id or Leg. Code.
813 OPEN csr_ele_info (c_element_type_id => p_element_type_id
814 ,c_effective_date => p_effective_date
815 ,c_business_group_id => p_business_group_id
816 ,c_legislation_code => l_legislation_code);
817 FETCH csr_ele_info INTO l_ele_info_rec;
818 hr_utility.set_location('p_element_type_id: '||p_element_type_id, 5);
819 IF csr_ele_info%FOUND THEN
820 CLOSE csr_ele_info;
821 -- =======================================================================
822 -- ~ Get Info for the Element Type Id Passed
823 -- =======================================================================
824 g_element(p_element_type_id).Information_category := l_ele_info_rec.element_information_category;
825 g_element(p_element_type_id).PreTax_category := l_ele_info_rec.element_information1;
826 g_element(p_element_type_id).Primary_balance_id := l_ele_info_rec.element_information10;
827 -- =======================================================================
828 -- Get the contribution type i.e. percentage or Amount
829 -- and the input value id for the element.
830 -- =======================================================================
831 OPEN csr_inv (c_input_name => l_input_name
832 ,c_element_type_id => p_element_type_id
833 ,c_effective_date => p_effective_date
834 ,c_business_group_id => p_business_group_id
835 ,c_legislation_code => l_legislation_code);
836 FETCH csr_inv INTO l_input_value_id;
837 IF csr_inv%FOUND THEN
838 g_element(p_element_type_id).input_name := l_input_name;
839 g_element(p_element_type_id).input_value_id := l_input_value_id;
840 END IF;
841 CLOSE csr_inv;
842
843 ELSE
844 -- Cursor failed to get any matching record for the ele type id passed.
845 hr_utility.set_location('..Failed to get details for element_type_id : '
846 ||p_element_type_id, 60);
847 CLOSE csr_ele_info;
848 END IF;-- If csr_ele_info%FOUND
849
850 hr_utility.set_location('Leaving: '||l_proc_name, 80);
851
852 END Get_Element_Info;
853 -- ================================================================================
854 -- ~ Update_Record_Values :
855 -- ================================================================================
856 PROCEDURE Update_Record_Values
857 (p_ext_rcd_id IN ben_ext_rcd.ext_rcd_id%TYPE
858 ,p_ext_data_element_name IN ben_ext_data_elmt.NAME%TYPE
859 ,p_data_element_value IN ben_ext_rslt_dtl.val_01%TYPE
860 ,p_data_ele_seqnum IN NUMBER
861 ,p_ext_dtl_rec IN OUT NOCOPY ben_ext_rslt_dtl%ROWTYPE
862 ) IS
863
864 CURSOR csr_seqnum
865 (c_ext_rcd_id IN ben_ext_rcd.ext_rcd_id%TYPE
866 ,c_ext_data_element_name IN ben_ext_data_elmt.NAME%TYPE
867 ) IS
868 SELECT der.ext_data_elmt_id,
869 der.seq_num,
870 ede.NAME
871 FROM ben_ext_data_elmt_in_rcd der
872 ,ben_ext_data_elmt ede
873 WHERE der.ext_rcd_id = c_ext_rcd_id
874 AND ede.ext_data_elmt_id = der.ext_data_elmt_id
875 AND ede.NAME LIKE '%'|| c_ext_data_element_name
876 ORDER BY seq_num;
877
878 l_seqnum_rec csr_seqnum%ROWTYPE;
879 l_proc_name VARCHAR2(150):= g_proc_name||'Update_Record_Values';
880 l_ext_dtl_rec_nc ben_ext_rslt_dtl%ROWTYPE;
881
882 BEGIN
883 hr_utility.set_location('Entering :'||l_proc_name, 5);
884 l_ext_dtl_rec_nc := p_ext_dtl_rec;
885
886 IF p_data_ele_seqnum IS NULL THEN
887 OPEN csr_seqnum ( c_ext_rcd_id => p_ext_rcd_id
888 ,c_ext_data_element_name => p_ext_data_element_name);
889 FETCH csr_seqnum INTO l_seqnum_rec;
890 IF csr_seqnum%NOTFOUND THEN
891 CLOSE csr_seqnum;
892 ELSE
893 CLOSE csr_seqnum;
894 END IF;
895 ELSE
896 l_seqnum_rec.seq_num := p_data_ele_seqnum;
897 END IF;
898 --
899 -- Based on the seq. num passed update the record value based on the
900 -- seq. num
901 IF l_seqnum_rec.seq_num = 1 THEN
902 p_ext_dtl_rec.val_01 := p_data_element_value;
903 ELSIF l_seqnum_rec.seq_num = 2 THEN
904 p_ext_dtl_rec.val_02 := p_data_element_value;
905 ELSIF l_seqnum_rec.seq_num = 3 THEN
906 p_ext_dtl_rec.val_03 := p_data_element_value;
907 ELSIF l_seqnum_rec.seq_num = 4 THEN
908 p_ext_dtl_rec.val_04 := p_data_element_value;
909 ELSIF l_seqnum_rec.seq_num = 5 THEN
910 p_ext_dtl_rec.val_05 := p_data_element_value;
911 ELSIF l_seqnum_rec.seq_num = 6 THEN
912 p_ext_dtl_rec.val_06 := p_data_element_value;
913 ELSIF l_seqnum_rec.seq_num = 7 THEN
914 p_ext_dtl_rec.val_07 := p_data_element_value;
915 ELSIF l_seqnum_rec.seq_num = 8 THEN
916 p_ext_dtl_rec.val_08 := p_data_element_value;
917 ELSIF l_seqnum_rec.seq_num = 9 THEN
918 p_ext_dtl_rec.val_09 := p_data_element_value;
919 ELSIF l_seqnum_rec.seq_num = 10 THEN
920 p_ext_dtl_rec.val_10 := p_data_element_value;
921 ELSIF l_seqnum_rec.seq_num = 11 THEN
922 p_ext_dtl_rec.val_11 := p_data_element_value;
923 ELSIF l_seqnum_rec.seq_num = 12 THEN
924 p_ext_dtl_rec.val_12 := p_data_element_value;
925 ELSIF l_seqnum_rec.seq_num = 13 THEN
926 p_ext_dtl_rec.val_13 := p_data_element_value;
927 ELSIF l_seqnum_rec.seq_num = 14 THEN
928 p_ext_dtl_rec.val_14 := p_data_element_value;
929 ELSIF l_seqnum_rec.seq_num = 15 THEN
930 p_ext_dtl_rec.val_15 := p_data_element_value;
931 ELSIF l_seqnum_rec.seq_num = 16 THEN
932 p_ext_dtl_rec.val_16 := p_data_element_value;
933 ELSIF l_seqnum_rec.seq_num = 17 THEN
934 p_ext_dtl_rec.val_17 := p_data_element_value;
935 ELSIF l_seqnum_rec.seq_num = 18 THEN
936 p_ext_dtl_rec.val_18 := p_data_element_value;
937 ELSIF l_seqnum_rec.seq_num = 19 THEN
938 p_ext_dtl_rec.val_19 := p_data_element_value;
939 ELSIF l_seqnum_rec.seq_num = 20 THEN
940 p_ext_dtl_rec.val_20 := p_data_element_value;
941 ELSIF l_seqnum_rec.seq_num = 21 THEN
942 p_ext_dtl_rec.val_21 := p_data_element_value;
943 ELSIF l_seqnum_rec.seq_num = 22 THEN
944 p_ext_dtl_rec.val_22 := p_data_element_value;
945 ELSIF l_seqnum_rec.seq_num = 23THEN
946 p_ext_dtl_rec.val_23 := p_data_element_value;
947 ELSIF l_seqnum_rec.seq_num = 24 THEN
948 p_ext_dtl_rec.val_24 := p_data_element_value;
949 ELSIF l_seqnum_rec.seq_num = 25 THEN
950 p_ext_dtl_rec.val_25 := p_data_element_value;
951 ELSIF l_seqnum_rec.seq_num = 26 THEN
952 p_ext_dtl_rec.val_26 := p_data_element_value;
953 ELSIF l_seqnum_rec.seq_num = 27 THEN
954 p_ext_dtl_rec.val_27 := p_data_element_value;
955 ELSIF l_seqnum_rec.seq_num = 28 THEN
956 p_ext_dtl_rec.val_28 := p_data_element_value;
957 ELSIF l_seqnum_rec.seq_num = 29 THEN
958 p_ext_dtl_rec.val_29 := p_data_element_value;
959 ELSIF l_seqnum_rec.seq_num = 30 THEN
960 p_ext_dtl_rec.val_30 := p_data_element_value;
961 ELSIF l_seqnum_rec.seq_num = 31 THEN
962 p_ext_dtl_rec.val_31 := p_data_element_value;
963 ELSIF l_seqnum_rec.seq_num = 32 THEN
964 p_ext_dtl_rec.val_32 := p_data_element_value;
965 ELSIF l_seqnum_rec.seq_num = 33 THEN
966 p_ext_dtl_rec.val_33 := p_data_element_value;
967 ELSIF l_seqnum_rec.seq_num = 34 THEN
968 p_ext_dtl_rec.val_34 := p_data_element_value;
969 ELSIF l_seqnum_rec.seq_num = 35 THEN
970 p_ext_dtl_rec.val_35 := p_data_element_value;
971 ELSIF l_seqnum_rec.seq_num = 36 THEN
972 p_ext_dtl_rec.val_36 := p_data_element_value;
973 ELSIF l_seqnum_rec.seq_num = 37 THEN
974 p_ext_dtl_rec.val_37 := p_data_element_value;
975 ELSIF l_seqnum_rec.seq_num = 38 THEN
976 p_ext_dtl_rec.val_38 := p_data_element_value;
977 ELSIF l_seqnum_rec.seq_num = 39 THEN
978 p_ext_dtl_rec.val_39 := p_data_element_value;
979 ELSIF l_seqnum_rec.seq_num = 40 THEN
980 p_ext_dtl_rec.val_40 := p_data_element_value;
981 ELSIF l_seqnum_rec.seq_num = 41 THEN
982 p_ext_dtl_rec.val_41 := p_data_element_value;
983 ELSIF l_seqnum_rec.seq_num = 42 THEN
984 p_ext_dtl_rec.val_42 := p_data_element_value;
985 ELSIF l_seqnum_rec.seq_num = 43 THEN
986 p_ext_dtl_rec.val_43 := p_data_element_value;
987 ELSIF l_seqnum_rec.seq_num = 44 THEN
988 p_ext_dtl_rec.val_44 := p_data_element_value;
989 ELSIF l_seqnum_rec.seq_num = 45 THEN
990 p_ext_dtl_rec.val_45 := p_data_element_value;
991 ELSIF l_seqnum_rec.seq_num = 46 THEN
992 p_ext_dtl_rec.val_46 := p_data_element_value;
993 ELSIF l_seqnum_rec.seq_num = 47 THEN
994 p_ext_dtl_rec.val_47 := p_data_element_value;
995 ELSIF l_seqnum_rec.seq_num = 48 THEN
996 p_ext_dtl_rec.val_48 := p_data_element_value;
997 ELSIF l_seqnum_rec.seq_num = 49 THEN
998 p_ext_dtl_rec.val_49 := p_data_element_value;
999 ELSIF l_seqnum_rec.seq_num = 50 THEN
1000 p_ext_dtl_rec.val_50 := p_data_element_value;
1001 ELSIF l_seqnum_rec.seq_num = 51 THEN
1002 p_ext_dtl_rec.val_51 := p_data_element_value;
1003 ELSIF l_seqnum_rec.seq_num = 52 THEN
1004 p_ext_dtl_rec.val_52 := p_data_element_value;
1005 ELSIF l_seqnum_rec.seq_num = 53 THEN
1006 p_ext_dtl_rec.val_53 := p_data_element_value;
1007 ELSIF l_seqnum_rec.seq_num = 54 THEN
1008 p_ext_dtl_rec.val_54 := p_data_element_value;
1009 ELSIF l_seqnum_rec.seq_num = 55 THEN
1010 p_ext_dtl_rec.val_55 := p_data_element_value;
1011 ELSIF l_seqnum_rec.seq_num = 56 THEN
1012 p_ext_dtl_rec.val_56 := p_data_element_value;
1013 ELSIF l_seqnum_rec.seq_num = 57 THEN
1014 p_ext_dtl_rec.val_57 := p_data_element_value;
1015 ELSIF l_seqnum_rec.seq_num = 58 THEN
1016 p_ext_dtl_rec.val_58 := p_data_element_value;
1017 ELSIF l_seqnum_rec.seq_num = 58 THEN
1018 p_ext_dtl_rec.val_58 := p_data_element_value;
1019 ELSIF l_seqnum_rec.seq_num = 59 THEN
1020 p_ext_dtl_rec.val_59 := p_data_element_value;
1021 ELSIF l_seqnum_rec.seq_num = 60 THEN
1022 p_ext_dtl_rec.val_60 := p_data_element_value;
1023 ELSIF l_seqnum_rec.seq_num = 61 THEN
1024 p_ext_dtl_rec.val_61 := p_data_element_value;
1025 ELSIF l_seqnum_rec.seq_num = 62 THEN
1026 p_ext_dtl_rec.val_62 := p_data_element_value;
1027 ELSIF l_seqnum_rec.seq_num = 63 THEN
1028 p_ext_dtl_rec.val_63 := p_data_element_value;
1029 ELSIF l_seqnum_rec.seq_num = 64 THEN
1030 p_ext_dtl_rec.val_64 := p_data_element_value;
1031 ELSIF l_seqnum_rec.seq_num = 65 THEN
1032 p_ext_dtl_rec.val_65 := p_data_element_value;
1033 ELSIF l_seqnum_rec.seq_num = 66 THEN
1034 p_ext_dtl_rec.val_66 := p_data_element_value;
1035 ELSIF l_seqnum_rec.seq_num = 67 THEN
1036 p_ext_dtl_rec.val_67 := p_data_element_value;
1037 ELSIF l_seqnum_rec.seq_num = 68 THEN
1038 p_ext_dtl_rec.val_68 := p_data_element_value;
1039 ELSIF l_seqnum_rec.seq_num = 69 THEN
1040 p_ext_dtl_rec.val_69 := p_data_element_value;
1041 ELSIF l_seqnum_rec.seq_num = 70 THEN
1042 p_ext_dtl_rec.val_70 := p_data_element_value;
1043 ELSIF l_seqnum_rec.seq_num = 71 THEN
1044 p_ext_dtl_rec.val_71 := p_data_element_value;
1045 ELSIF l_seqnum_rec.seq_num = 72 THEN
1046 p_ext_dtl_rec.val_72 := p_data_element_value;
1047 ELSIF l_seqnum_rec.seq_num = 73 THEN
1048 p_ext_dtl_rec.val_73 := p_data_element_value;
1049 ELSIF l_seqnum_rec.seq_num = 74 THEN
1050 p_ext_dtl_rec.val_74 := p_data_element_value;
1051 ELSIF l_seqnum_rec.seq_num = 75 THEN
1052 p_ext_dtl_rec.val_75 := p_data_element_value;
1053 END IF;
1054 hr_utility.set_location('Leaving :'||l_proc_name, 25);
1055 RETURN;
1056 EXCEPTION
1057 WHEN Others THEN
1058 p_ext_dtl_rec := l_ext_dtl_rec_nc;
1059 RAISE;
1060
1061 END Update_Record_Values;
1062
1063 -- =============================================================================
1064 -- ~ Ins_Rslt_Dtl : Inserts a record into the results detail record.
1065 -- =============================================================================
1066 PROCEDURE Ins_Rslt_Dtl
1067 (p_dtl_rec IN OUT NOCOPY ben_ext_rslt_dtl%ROWTYPE
1068 ,p_val_tab IN ValTabTyp
1069 ,p_rslt_dtl_id OUT NOCOPY NUMBER
1070 ) IS
1071
1072 l_proc_name VARCHAR2(150) := g_proc_name||'Ins_Rslt_Dtl';
1073 l_dtl_rec_nc ben_ext_rslt_dtl%ROWTYPE;
1074
1075 BEGIN
1076 Hr_Utility.set_location('Entering :'||l_proc_name, 5);
1077 l_dtl_rec_nc := p_dtl_rec;
1078
1079 -- Get the next sequence NUMBER to insert a record into the table
1080 SELECT ben_ext_rslt_dtl_s.NEXTVAL INTO p_dtl_rec.ext_rslt_dtl_id FROM dual;
1081 INSERT INTO ben_ext_rslt_dtl
1082 (ext_rslt_dtl_id
1083 ,ext_rslt_id
1084 ,business_group_id
1085 ,ext_rcd_id
1086 ,person_id
1087 ,val_01
1088 ,val_02
1089 ,val_03
1090 ,val_04
1091 ,val_05
1092 ,val_06
1093 ,val_07
1094 ,val_08
1095 ,val_09
1096 ,val_10
1097 ,val_11
1098 ,val_12
1099 ,val_13
1100 ,val_14
1101 ,val_15
1102 ,val_16
1103 ,val_17
1104 ,val_19
1105 ,val_18
1106 ,val_20
1107 ,val_21
1108 ,val_22
1109 ,val_23
1110 ,val_24
1111 ,val_25
1112 ,val_26
1113 ,val_27
1114 ,val_28
1115 ,val_29
1116 ,val_30
1117 ,val_31
1118 ,val_32
1119 ,val_33
1120 ,val_34
1121 ,val_35
1122 ,val_36
1123 ,val_37
1124 ,val_38
1125 ,val_39
1126 ,val_40
1127 ,val_41
1128 ,val_42
1129 ,val_43
1130 ,val_44
1131 ,val_45
1132 ,val_46
1133 ,val_47
1134 ,val_48
1135 ,val_49
1136 ,val_50
1137 ,val_51
1138 ,val_52
1139 ,val_53
1140 ,val_54
1141 ,val_55
1142 ,val_56
1143 ,val_57
1144 ,val_58
1145 ,val_59
1146 ,val_60
1147 ,val_61
1148 ,val_62
1149 ,val_63
1150 ,val_64
1151 ,val_65
1152 ,val_66
1153 ,val_67
1154 ,val_68
1155 ,val_69
1156 ,val_70
1157 ,val_71
1158 ,val_72
1159 ,val_73
1160 ,val_74
1161 ,val_75
1162 ,created_by
1163 ,creation_date
1164 ,last_update_date
1165 ,last_updated_by
1166 ,last_update_login
1167 ,program_application_id
1168 ,program_id
1169 ,program_update_date
1170 ,request_id
1171 ,object_version_number
1172 ,prmy_sort_val
1173 ,scnd_sort_val
1174 ,thrd_sort_val
1175 ,trans_seq_num
1176 ,rcrd_seq_num
1177 )
1178 VALUES
1179 (p_dtl_rec.ext_rslt_dtl_id
1180 ,p_dtl_rec.ext_rslt_id
1181 ,p_dtl_rec.business_group_id
1182 ,p_dtl_rec.ext_rcd_id
1183 ,p_dtl_rec.person_id
1184 ,p_val_tab(1)
1185 ,p_val_tab(2)
1186 ,p_val_tab(3)
1187 ,p_val_tab(4)
1188 ,p_val_tab(5)
1189 ,p_val_tab(6)
1190 ,p_val_tab(7)
1191 ,p_val_tab(8)
1192 ,p_val_tab(9)
1193 ,p_val_tab(10)
1194 ,p_val_tab(11)
1195 ,p_val_tab(12)
1196 ,p_val_tab(13)
1197 ,p_val_tab(14)
1198 ,p_val_tab(15)
1199 ,p_val_tab(16)
1200 ,p_val_tab(17)
1201 ,p_val_tab(19)
1202 ,p_val_tab(18)
1203 ,p_val_tab(20)
1204 ,p_val_tab(21)
1205 ,p_val_tab(22)
1206 ,p_val_tab(23)
1207 ,p_val_tab(24)
1208 ,p_val_tab(25)
1209 ,p_val_tab(26)
1210 ,p_val_tab(27)
1211 ,p_val_tab(28)
1212 ,p_val_tab(29)
1213 ,p_val_tab(30)
1214 ,p_val_tab(31)
1215 ,p_val_tab(32)
1216 ,p_val_tab(33)
1217 ,p_val_tab(34)
1218 ,p_val_tab(35)
1219 ,p_val_tab(36)
1220 ,p_val_tab(37)
1221 ,p_val_tab(38)
1222 ,p_val_tab(39)
1223 ,p_val_tab(40)
1224 ,p_val_tab(41)
1225 ,p_val_tab(42)
1226 ,p_val_tab(43)
1227 ,p_val_tab(44)
1228 ,p_val_tab(45)
1229 ,p_val_tab(46)
1230 ,p_val_tab(47)
1231 ,p_val_tab(48)
1232 ,p_val_tab(49)
1233 ,p_val_tab(50)
1234 ,p_val_tab(51)
1235 ,p_val_tab(52)
1236 ,p_val_tab(53)
1237 ,p_val_tab(54)
1238 ,p_val_tab(55)
1239 ,p_val_tab(56)
1240 ,p_val_tab(57)
1241 ,p_val_tab(58)
1242 ,p_val_tab(59)
1243 ,p_val_tab(60)
1244 ,p_val_tab(61)
1245 ,p_val_tab(62)
1246 ,p_val_tab(63)
1247 ,p_val_tab(64)
1248 ,p_val_tab(65)
1249 ,p_val_tab(66)
1250 ,p_val_tab(67)
1251 ,p_val_tab(68)
1252 ,p_val_tab(69)
1253 ,p_val_tab(70)
1254 ,p_val_tab(71)
1255 ,p_val_tab(72)
1256 ,p_val_tab(73)
1257 ,p_val_tab(74)
1258 ,p_val_tab(75)
1259 ,p_dtl_rec.created_by
1260 ,p_dtl_rec.creation_date
1261 ,p_dtl_rec.last_update_date
1262 ,p_dtl_rec.last_updated_by
1263 ,p_dtl_rec.last_update_login
1264 ,p_dtl_rec.program_application_id
1265 ,p_dtl_rec.program_id
1266 ,p_dtl_rec.program_update_date
1267 ,p_dtl_rec.request_id
1268 ,p_dtl_rec.object_version_number
1269 ,p_dtl_rec.prmy_sort_val
1270 ,p_dtl_rec.scnd_sort_val
1271 ,p_dtl_rec.thrd_sort_val
1272 ,p_dtl_rec.trans_seq_num
1273 ,p_dtl_rec.rcrd_seq_num
1274 );
1275 Hr_Utility.set_location('Leaving :'||l_proc_name, 25);
1276 RETURN;
1277
1278 EXCEPTION
1279 WHEN Others THEN
1280 Hr_Utility.set_location('Leaving :'||l_proc_name, 25);
1281 p_dtl_rec := l_dtl_rec_nc;
1282 RAISE;
1283 END Ins_Rslt_Dtl;
1284
1285 -- =============================================================================
1286 -- ~Upd_Rslt_Dtl : Updates the primary assignment record in results detail table
1287 -- =============================================================================
1288 PROCEDURE Upd_Rslt_Dtl
1289 (p_dtl_rec IN ben_ext_rslt_dtl%ROWTYPE
1290 ,p_val_tab IN ValTabTyp ) IS
1291
1292 l_proc_name VARCHAR2(150):= g_proc_name||'upd_rslt_dtl';
1293
1294 BEGIN
1295 UPDATE ben_ext_rslt_dtl
1296 set val_01 = p_val_tab(1)
1297 ,val_02 = p_val_tab(2)
1298 ,val_03 = p_val_tab(3)
1299 ,val_04 = p_val_tab(4)
1300 ,val_05 = p_val_tab(5)
1301 ,val_06 = p_val_tab(6)
1302 ,val_07 = p_val_tab(7)
1303 ,val_08 = p_val_tab(8)
1304 ,val_09 = p_val_tab(9)
1305 ,val_10 = p_val_tab(10)
1306 ,val_11 = p_val_tab(11)
1307 ,val_12 = p_val_tab(12)
1308 ,val_13 = p_val_tab(13)
1309 ,val_14 = p_val_tab(14)
1310 ,val_15 = p_val_tab(15)
1311 ,val_16 = p_val_tab(16)
1312 ,val_17 = p_val_tab(17)
1313 ,val_19 = p_val_tab(19)
1314 ,val_18 = p_val_tab(18)
1315 ,val_20 = p_val_tab(20)
1316 ,val_21 = p_val_tab(21)
1317 ,val_22 = p_val_tab(22)
1318 ,val_23 = p_val_tab(23)
1319 ,val_24 = p_val_tab(24)
1320 ,val_25 = p_val_tab(25)
1321 ,val_26 = p_val_tab(26)
1322 ,val_27 = p_val_tab(27)
1323 ,val_28 = p_val_tab(28)
1324 ,val_29 = p_val_tab(29)
1325 ,val_30 = p_val_tab(30)
1326 ,val_31 = p_val_tab(31)
1327 ,val_32 = p_val_tab(32)
1328 ,val_33 = p_val_tab(33)
1329 ,val_34 = p_val_tab(34)
1330 ,val_35 = p_val_tab(35)
1331 ,val_36 = p_val_tab(36)
1332 ,val_37 = p_val_tab(37)
1333 ,val_38 = p_val_tab(38)
1334 ,val_39 = p_val_tab(39)
1335 ,val_40 = p_val_tab(40)
1336 ,val_41 = p_val_tab(41)
1337 ,val_42 = p_val_tab(42)
1338 ,val_43 = p_val_tab(43)
1339 ,val_44 = p_val_tab(44)
1340 ,val_45 = p_val_tab(45)
1341 ,val_46 = p_val_tab(46)
1342 ,val_47 = p_val_tab(47)
1343 ,val_48 = p_val_tab(48)
1344 ,val_49 = p_val_tab(49)
1345 ,val_50 = p_val_tab(50)
1346 ,val_51 = p_val_tab(51)
1347 ,val_52 = p_val_tab(52)
1348 ,val_53 = p_val_tab(53)
1349 ,val_54 = p_val_tab(54)
1350 ,val_55 = p_val_tab(55)
1351 ,val_56 = p_val_tab(56)
1352 ,val_57 = p_val_tab(57)
1353 ,val_58 = p_val_tab(58)
1354 ,val_59 = p_val_tab(59)
1355 ,val_60 = p_val_tab(60)
1356 ,val_61 = p_val_tab(61)
1357 ,val_62 = p_val_tab(62)
1358 ,val_63 = p_val_tab(63)
1359 ,val_64 = p_val_tab(64)
1360 ,val_65 = p_val_tab(65)
1361 ,val_66 = p_val_tab(66)
1362 ,val_67 = p_val_tab(67)
1363 ,val_68 = p_val_tab(68)
1364 ,val_69 = p_val_tab(69)
1365 ,val_70 = p_val_tab(70)
1366 ,val_71 = p_val_tab(71)
1367 ,val_72 = p_val_tab(72)
1368 ,val_73 = p_val_tab(73)
1369 ,val_74 = p_val_tab(74)
1370 ,val_75 = p_val_tab(75)
1371 ,object_version_number = p_dtl_rec.object_version_number
1372 ,thrd_sort_val = p_dtl_rec.thrd_sort_val
1373 WHERE ext_rslt_dtl_id = p_dtl_rec.ext_rslt_dtl_id;
1374
1375 RETURN;
1376
1377 EXCEPTION
1378 WHEN Others THEN
1379 RAISE;
1380 END Upd_Rslt_Dtl;
1381
1382 -- =============================================================================
1383 -- Write_Warning:
1384 -- =============================================================================
1385 PROCEDURE Write_Warning
1386 (p_err_name IN VARCHAR2,
1387 p_err_no IN NUMBER DEFAULT NULL,
1388 p_element IN VARCHAR2 DEFAULT NULL ) IS
1389
1390 l_proc VARCHAR2(72) := g_proc_name||'write_warning';
1391 l_err_name VARCHAR2(2000) := p_err_name ;
1392 l_err_no NUMBER := p_err_no ;
1393
1394 BEGIN
1395
1396 Hr_Utility.set_location('Entering'||l_proc, 5);
1397 IF p_err_no IS NULL THEN
1398 -- Assumed the name is Error Name
1399 l_err_no := To_Number(Substr(p_err_name,5,5)) ;
1400 l_err_name := NULL ;
1401 END IF ;
1402 -- If element name is sent get the message to write
1403 IF p_err_no IS NOT NULL AND p_element IS NOT NULL THEN
1404 l_err_name := Ben_Ext_Fmt.get_error_msg(p_err_no,
1405 p_err_name,
1406 p_element ) ;
1407 END IF ;
1408
1409 IF g_business_group_id IS NOT NULL THEN
1410 Ben_Ext_Util.write_err
1411 (p_err_num => l_err_no,
1412 p_err_name => l_err_name,
1413 p_typ_cd => 'W',
1414 p_person_id => g_person_id,
1415 p_business_group_id => g_business_group_id,
1416 p_ext_rslt_id => Ben_Extract.g_ext_rslt_id);
1417 END IF;
1418 --
1419 Hr_Utility.set_location('Exiting'||l_proc, 15);
1420 --
1421 --
1422 END Write_Warning;
1423 -- =============================================================================
1424 -- Write_Error:
1425 -- =============================================================================
1426 PROCEDURE Write_Error
1427 (p_err_name IN VARCHAR2,
1428 p_err_no IN NUMBER DEFAULT NULL,
1429 p_element IN VARCHAR2 DEFAULT NULL ) IS
1430 --
1431 l_proc VARCHAR2(72) := g_proc_name||'write_error';
1432 l_err_name VARCHAR2(2000) := p_err_name ;
1433 l_err_no NUMBER := p_err_no ;
1434 l_err_num NUMBER(15);
1435 --
1436 CURSOR err_cnt_c IS
1437 SELECT count(*) FROM ben_ext_rslt_err
1438 WHERE ext_rslt_id = ben_extract.g_ext_rslt_id
1439 AND typ_cd <> 'W';
1440 --
1441 BEGIN
1442 --
1443 Hr_Utility.set_location('Entering'||l_proc, 5);
1444 IF p_err_no IS NULL THEN
1445 -- Assumed the name is Error Name
1446 l_err_no := To_Number(Substr(p_err_name,5,5)) ;
1447 l_err_name := NULL ;
1448 END IF ;
1449 -- If element name is sent get the message to write
1450 IF p_err_no IS NOT NULL AND p_element IS NOT NULL THEN
1451 l_err_name := Ben_Ext_Fmt.get_error_msg(p_err_no,
1452 p_err_name,
1453 p_element ) ;
1454 END IF ;
1455
1456 OPEN err_cnt_c;
1457 FETCH err_cnt_c INTO l_err_num;
1458 CLOSE err_cnt_c;
1459 --
1460 IF l_err_num >= ben_ext_thread.g_max_errors_allowed THEN
1461 --
1462 ben_ext_thread.g_err_num := 91947;
1463 ben_ext_thread.g_err_name := 'BEN_91947_EXT_MX_ERR_NUM';
1464 RAISE ben_ext_thread.g_job_failure_error;
1465 --
1466 END IF;
1467
1468 IF g_business_group_id IS NOT NULL THEN
1469 Ben_Ext_Util.write_err
1470 (p_err_num => l_err_no,
1471 p_err_name => l_err_name,
1472 p_typ_cd => 'E',
1473 p_person_id => g_person_id,
1474 p_business_group_id => g_business_group_id,
1475 p_ext_rslt_id => Ben_Extract.g_ext_rslt_id);
1476 END IF;
1477 --
1478 Hr_Utility.set_location('Exiting'||l_proc, 15);
1479 --
1480 --
1481 END Write_Error;
1482
1483 -- =============================================================================
1484 -- Rcd_In_File:
1485 -- =============================================================================
1486 PROCEDURE Rcd_In_File
1487 (p_ext_rcd_in_file_id IN NUMBER
1488 ,p_sprs_cd IN VARCHAR2
1489 ,p_val_tab IN OUT NOCOPY ValTabTyp
1490 ,p_exclude_this_rcd_flag OUT NOCOPY BOOLEAN
1491 ,p_raise_warning OUT NOCOPY BOOLEAN
1492 ,p_rollback_person OUT NOCOPY BOOLEAN) IS
1493
1494 CURSOR c_xwc(p_ext_rcd_in_file_id IN NUMBER) IS
1495 SELECT xwc.oper_cd,
1496 xwc.val,
1497 xwc.and_or_cd,
1498 xer.seq_num,
1499 xrc.NAME,
1500 Substr(xel.frmt_mask_cd,1,1) xel_frmt_mask_cd,
1501 xel.data_elmt_typ_cd,
1502 xel.data_elmt_rl,
1503 xel.ext_fld_id,
1504 fld.frmt_mask_typ_cd
1505
1506 FROM ben_ext_where_clause xwc,
1507 ben_ext_data_elmt_in_rcd xer,
1508 ben_ext_rcd xrc,
1509 ben_ext_data_elmt xel,
1510 ben_ext_fld fld
1511 WHERE xwc.ext_rcd_in_file_id = p_ext_rcd_in_file_id
1512 AND xwc.cond_ext_data_elmt_in_rcd_id = xer.ext_data_elmt_in_rcd_id
1513 AND xer.ext_rcd_id = xrc.ext_rcd_id
1514 AND xel.ext_data_elmt_id = xer.ext_data_elmt_id
1515 AND xel.ext_fld_id = fld.ext_fld_id(+)
1516 ORDER BY xwc.seq_num;
1517 --
1518 l_proc VARCHAR2(72) := g_proc_name||'Rcd_In_File';
1519 l_condition VARCHAR2(1);
1520 l_cnt NUMBER;
1521 l_value_without_quotes VARCHAR2(500);
1522 l_dynamic_condition VARCHAR2(9999);
1523 l_rcd_name ben_ext_rcd.NAME%TYPE ;
1524 --
1525 --
1526 BEGIN
1527 --
1528 Hr_Utility.set_location('Entering'||l_proc, 5);
1529 --
1530 p_exclude_this_rcd_flag := FALSE;
1531 p_raise_warning := FALSE;
1532 p_rollback_person := FALSE;
1533 IF p_sprs_cd = NULL THEN
1534 RETURN;
1535 END IF;
1536 --
1537 l_cnt := 0;
1538 l_dynamic_condition := 'begin If ';
1539 FOR xwc IN c_xwc(p_ext_rcd_in_file_id) LOOP
1540 l_cnt := l_cnt +1;
1541 -- Strip all quotes out of any values.
1542 l_value_without_quotes := REPLACE(p_val_tab(xwc.seq_num),'''');
1543 --
1544 IF (xwc.frmt_mask_typ_cd = 'N' OR
1545 xwc.xel_frmt_mask_cd = 'N' OR
1546 xwc.data_elmt_typ_cd = 'R')
1547 AND
1548 l_value_without_quotes IS NOT NULL
1549 THEN
1550 BEGIN
1551 -- Test for numeric value
1552 IF xwc.oper_cd = 'IN' THEN
1553 l_dynamic_condition := l_dynamic_condition ||''''||
1554 l_value_without_quotes||'''';
1555 ELSE
1556 l_dynamic_condition := l_dynamic_condition ||
1557 To_Number(l_value_without_quotes);
1558 END IF;
1559
1560 EXCEPTION WHEN Others THEN
1561 -- Quotes needed, not numeric value
1562 l_dynamic_condition := l_dynamic_condition || '''' ||
1563 l_value_without_quotes|| '''';
1564 END;
1565 ELSE
1566 -- Quotes needed, not Numeric value
1567 l_dynamic_condition := l_dynamic_condition || '''' ||
1568 l_value_without_quotes|| '''';
1569 END IF;
1570
1571 l_dynamic_condition := l_dynamic_condition || ' ' || xwc.oper_cd ||
1572 ' ' || xwc.val ||
1573 ' ' || xwc.and_or_cd ||
1574 ' ';
1575
1576 l_rcd_name := xwc.NAME ;
1577 END LOOP;
1578 -- if there is no data for advanced conditions, exit this program.
1579 IF l_cnt = 0 THEN
1580 RETURN;
1581 END IF;
1582 l_dynamic_condition := l_dynamic_condition ||
1583 ' then :l_condition := ''T''; else :l_condition := ''F''; end if; end;';
1584 BEGIN
1585 EXECUTE IMMEDIATE l_dynamic_condition Using OUT l_condition;
1586 EXCEPTION
1587 WHEN Others THEN
1588 Fnd_File.put_line(Fnd_File.Log,
1589 'Error in Advanced Conditions while processing this dynamic sql statement: ');
1590 Fnd_File.put_line(Fnd_File.Log, l_dynamic_condition);
1591 RAISE; -- such that the error processing in ben_ext_thread occurs.
1592 END;
1593 --
1594 IF l_condition = 'T' THEN
1595 IF p_sprs_cd = 'A' THEN
1596 -- Rollback Record
1597 p_exclude_this_rcd_flag := TRUE;
1598 ELSIF p_sprs_cd = 'B' THEN
1599 -- Rollback Person
1600 p_exclude_this_rcd_flag := TRUE;
1601 p_rollback_person := TRUE;
1602 ELSIF p_sprs_cd = 'C' THEN
1603 -- Rollback Person and Error
1604 p_exclude_this_rcd_flag := TRUE;
1605 p_rollback_person := TRUE;
1606
1607 Write_Error
1608 (p_err_name => 'BEN_92679_EXT_USER_DEFINED_ERR'
1609 ,p_err_no => 92679
1610 ,p_element => l_rcd_name);
1611
1612 ELSIF p_sprs_cd = 'H' THEN
1613 -- Signal Warning
1614 p_raise_warning := TRUE;
1615
1616 Write_Warning ('BEN_92678_EXT_USER_DEFINED_WRN'
1617 ,92678
1618 ,l_rcd_name);
1619
1620 ELSIF p_sprs_cd = 'M' THEN
1621 -- Rollback Record and Signal Warning
1622 p_raise_warning := TRUE;
1623
1624 Write_Warning ('BEN_92678_EXT_USER_DEFINED_WRN'
1625 ,92678
1626 ,l_rcd_name);
1627
1628 p_exclude_this_rcd_flag := TRUE;
1629 END IF; -- IF p_sprs_cd = 'A'
1630
1631 ELSE -- l_condition = 'F'
1632
1633 IF p_sprs_cd = 'D' THEN
1634 -- Rollback Record
1635 p_exclude_this_rcd_flag := TRUE;
1636 ELSIF p_sprs_cd = 'E' THEN
1637 -- Rollback Person
1638 p_exclude_this_rcd_flag := TRUE;
1639 p_rollback_person := TRUE;
1640 ELSIF p_sprs_cd = 'F' THEN
1641 -- Rollback Person and Error
1642 p_exclude_this_rcd_flag := TRUE;
1643 p_rollback_person := TRUE;
1644
1645 Write_Error
1646 (p_err_name => 'BEN_92679_EXT_USER_DEFINED_ERR'
1647 ,p_err_no => 92679
1648 ,p_element => l_rcd_name);
1649
1650 ELSIF p_sprs_cd = 'K' THEN
1651 -- Signal Warning
1652 p_raise_warning := TRUE;
1653 Write_Warning ('BEN_92678_EXT_USER_DEFINED_WRN'
1654 ,92678
1655 ,l_rcd_name);
1656 ELSIF p_sprs_cd = 'N' THEN
1657 -- Rollback Record and Signal warning
1658 Write_Warning ('BEN_92678_EXT_USER_DEFINED_WRN'
1659 ,92678
1660 ,l_rcd_name);
1661 p_raise_warning := TRUE;
1662 p_exclude_this_rcd_flag := TRUE;
1663 END IF; -- IF p_sprs_cd = 'D'
1664 --
1665 END IF; -- IF l_condition = 'T'
1666 --
1667 Hr_Utility.set_location('Exiting'||l_proc, 15);
1668 --
1669 END Rcd_In_File;
1670
1671 -- =============================================================================
1672 -- Data_Elmt_In_Rcd:
1673 -- =============================================================================
1674 PROCEDURE Data_Elmt_In_Rcd
1675 (p_ext_rcd_id IN NUMBER
1676 ,p_val_tab IN OUT NOCOPY ValTabTyp
1677 ,p_exclude_this_rcd_flag OUT NOCOPY BOOLEAN
1678 ,p_raise_warning OUT NOCOPY BOOLEAN
1679 ,p_rollback_person OUT NOCOPY BOOLEAN) IS
1680 --
1681 CURSOR c_xer(p_ext_rcd_id IN NUMBER) IS
1682 SELECT xer.seq_num,
1683 xer.sprs_cd,
1684 xer.ext_data_elmt_in_rcd_id,
1685 xdm.NAME
1686 FROM ben_ext_data_elmt_in_rcd xer,
1687 ben_ext_data_elmt xdm
1688 WHERE ext_rcd_id = p_ext_rcd_id
1689 AND xer.sprs_cd IS NOT NULL
1690 AND xer.ext_data_elmt_id = xdm.ext_data_elmt_id ;
1691 --
1692 CURSOR c_xwc(p_ext_data_elmt_in_rcd_id IN NUMBER) IS
1693 SELECT xwc.oper_cd,
1694 xwc.val,
1695 xwc.and_or_cd,
1696 xer.seq_num
1697 FROM ben_ext_where_clause xwc,
1698 ben_ext_data_elmt_in_rcd xer
1699 WHERE xwc.ext_data_elmt_in_rcd_id = p_ext_data_elmt_in_rcd_id
1700 AND xwc.cond_ext_data_elmt_in_rcd_id = xer.ext_data_elmt_in_rcd_id
1701 ORDER BY xwc.seq_num;
1702 --
1703 l_proc VARCHAR2(72) := g_proc_name||'Data_Elmt_In_Rcd';
1704 l_condition VARCHAR2(1);
1705 l_cnt NUMBER;
1706 l_value_without_quotes VARCHAR2(500);
1707 l_dynamic_condition VARCHAR2(9999);
1708 --
1709 l_val_tab_mirror ValTabTyp;
1710 BEGIN
1711 Hr_Utility.set_location('Entering'||l_proc, 5);
1712 p_exclude_this_rcd_flag := FALSE;
1713 p_raise_warning := FALSE;
1714 p_rollback_person := FALSE;
1715 -- Make mirror image of table for evaluation, since values in
1716 -- the real table are changing (being nullified).
1717 l_val_tab_mirror := p_val_tab;
1718 --
1719 FOR xer IN c_xer(p_ext_rcd_id) LOOP
1720 --
1721 l_cnt := 0;
1722 l_dynamic_condition := 'begin If ';
1723 FOR xwc IN c_xwc(xer.ext_data_elmt_in_rcd_id) LOOP
1724 l_cnt := l_cnt +1;
1725 -- strip all quotes out of any values.
1726 l_value_without_quotes := REPLACE(l_val_tab_mirror(xwc.seq_num),'''');
1727 l_dynamic_condition := l_dynamic_condition || '''' ||
1728 l_value_without_quotes || '''' || ' ' ||
1729 xwc.oper_cd || ' ' ||
1730 xwc.val || ' ' ||
1731 xwc.and_or_cd || ' ';
1732 END LOOP;-- FOR xwc IN c_xwc
1733
1734 -- If there is no data for advanced conditions, bypass rest of this program.
1735 IF l_cnt > 0 THEN
1736 l_dynamic_condition := l_dynamic_condition ||
1737 ' then :l_condition := ''T''; else :l_condition := ''F''; end if; end;';
1738 BEGIN
1739 EXECUTE IMMEDIATE l_dynamic_condition Using OUT l_condition;
1740 EXCEPTION
1741 WHEN Others THEN
1742 -- this needs replaced with a message for translation.
1743 Fnd_File.put_line(Fnd_File.Log,
1744 'Error in Advanced Conditions while processing this dynamic sql statement: ');
1745 Fnd_File.put_line(Fnd_File.Log, l_dynamic_condition);
1746 RAISE; -- such that the error processing in ben_ext_thread occurs.
1747 END;
1748 --
1749 --
1750 IF l_condition = 'T' THEN
1751 IF xer.sprs_cd = 'A' THEN
1752 -- Rollback Record
1753 p_exclude_this_rcd_flag := TRUE;
1754 EXIT;
1755 ELSIF xer.sprs_cd = 'B' THEN
1756 -- Rollback Person
1757 p_exclude_this_rcd_flag := TRUE;
1758 p_rollback_person := TRUE;
1759 ELSIF xer.sprs_cd = 'C' THEN
1760 -- Rollback person and error
1761 p_exclude_this_rcd_flag := TRUE;
1762 p_rollback_person := TRUE;
1763 ELSIF xer.sprs_cd = 'G' THEN
1764 -- Nullify Data Element
1765 p_val_tab(xer.seq_num) := NULL;
1766 ELSIF xer.sprs_cd = 'H' THEN
1767 -- Signal Warning
1768 p_raise_warning := FALSE;
1769 Write_Warning ('BEN_92313_EXT_USER_DEFINED_WRN'
1770 ,92313
1771 ,xer.NAME);
1772 ELSIF xer.sprs_cd = 'I' THEN
1773 -- Nullify Data Element and Signal Warning
1774 p_val_tab(xer.seq_num) := NULL;
1775 p_raise_warning := FALSE;
1776 Write_Warning ('BEN_92313_EXT_USER_DEFINED_WRN'
1777 ,92313
1778 ,xer.NAME);
1779 END IF; --IF xer.sprs_cd = 'A'
1780
1781 ELSE -- l_condition = 'F'
1782 IF xer.sprs_cd = 'D' THEN
1783 -- Rollback record
1784 p_exclude_this_rcd_flag := TRUE;
1785 EXIT;
1786 ELSIF xer.sprs_cd = 'E' THEN
1787 -- Rollback person
1788 p_exclude_this_rcd_flag := TRUE;
1789 p_rollback_person := TRUE;
1790 ELSIF xer.sprs_cd = 'F' THEN
1791 -- Rollback person and error
1792 p_exclude_this_rcd_flag := TRUE;
1793 p_rollback_person := TRUE;
1794 ELSIF xer.sprs_cd = 'J' THEN
1795 -- Nullify data element
1796 p_val_tab(xer.seq_num) := NULL;
1797 ELSIF xer.sprs_cd = 'K' THEN
1798 -- Signal warning
1799 p_raise_warning := FALSE;
1800 Write_Warning ('BEN_92313_EXT_USER_DEFINED_WRN'
1801 ,92313
1802 ,xer.NAME);
1803 ELSIF xer.sprs_cd = 'L' THEN
1804 -- Nullify data element and signal warning
1805 p_val_tab(xer.seq_num) := NULL;
1806 p_raise_warning := FALSE;
1807 Write_Warning ('BEN_92313_EXT_USER_DEFINED_WRN'
1808 ,92313
1809 ,xer.NAME);
1810 END IF; --IF xer.sprs_cd = 'D'
1811 --
1812 END IF; -- IF l_condition = 'T'
1813 --
1814 END IF;-- IF l_cnt > 0 THEN
1815 --
1816 END LOOP; -- FOR xer IN c_xer
1817 --
1818 Hr_Utility.set_location('Exiting'||l_proc, 15);
1819 --
1820 END Data_Elmt_In_Rcd;
1821
1822 -- =============================================================================
1823 -- Copy_Rec_Values :
1824 -- =============================================================================
1825 PROCEDURE Copy_Rec_Values
1826 (p_rslt_rec IN ben_ext_rslt_dtl%ROWTYPE
1827 ,p_val_tab IN OUT NOCOPY ValTabTyp) IS
1828
1829 l_proc_name VARCHAR2(150) := g_proc_name ||'Copy_Rec_Values ';
1830 BEGIN
1831 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
1832
1833 p_val_tab(1) := p_rslt_rec.val_01;
1834 p_val_tab(2) := p_rslt_rec.val_02;
1835 p_val_tab(3) := p_rslt_rec.val_03;
1836 p_val_tab(4) := p_rslt_rec.val_04;
1837 p_val_tab(5) := p_rslt_rec.val_05;
1838 p_val_tab(6) := p_rslt_rec.val_06;
1839 p_val_tab(7) := p_rslt_rec.val_07;
1840 p_val_tab(8) := p_rslt_rec.val_08;
1841 p_val_tab(9) := p_rslt_rec.val_09;
1842
1843 p_val_tab(10) := p_rslt_rec.val_10;
1844 p_val_tab(11) := p_rslt_rec.val_11;
1845 p_val_tab(12) := p_rslt_rec.val_12;
1846 p_val_tab(13) := p_rslt_rec.val_13;
1847 p_val_tab(14) := p_rslt_rec.val_14;
1848 p_val_tab(15) := p_rslt_rec.val_15;
1849 p_val_tab(16) := p_rslt_rec.val_16;
1850 p_val_tab(17) := p_rslt_rec.val_17;
1851 p_val_tab(18) := p_rslt_rec.val_18;
1852 p_val_tab(19) := p_rslt_rec.val_19;
1853
1854 p_val_tab(20) := p_rslt_rec.val_20;
1855 p_val_tab(21) := p_rslt_rec.val_21;
1856 p_val_tab(22) := p_rslt_rec.val_22;
1857 p_val_tab(23) := p_rslt_rec.val_23;
1858 p_val_tab(24) := p_rslt_rec.val_24;
1859 p_val_tab(25) := p_rslt_rec.val_25;
1860 p_val_tab(26) := p_rslt_rec.val_26;
1861 p_val_tab(27) := p_rslt_rec.val_27;
1862 p_val_tab(28) := p_rslt_rec.val_28;
1863 p_val_tab(29) := p_rslt_rec.val_29;
1864
1865 p_val_tab(30) := p_rslt_rec.val_30;
1866 p_val_tab(31) := p_rslt_rec.val_31;
1867 p_val_tab(32) := p_rslt_rec.val_32;
1868 p_val_tab(33) := p_rslt_rec.val_33;
1869 p_val_tab(34) := p_rslt_rec.val_34;
1870 p_val_tab(35) := p_rslt_rec.val_35;
1871 p_val_tab(36) := p_rslt_rec.val_36;
1872 p_val_tab(37) := p_rslt_rec.val_37;
1873 p_val_tab(38) := p_rslt_rec.val_38;
1874 p_val_tab(39) := p_rslt_rec.val_39;
1875
1876 p_val_tab(40) := p_rslt_rec.val_40;
1877 p_val_tab(41) := p_rslt_rec.val_41;
1878 p_val_tab(42) := p_rslt_rec.val_42;
1879 p_val_tab(43) := p_rslt_rec.val_43;
1880 p_val_tab(44) := p_rslt_rec.val_44;
1881 p_val_tab(45) := p_rslt_rec.val_45;
1882 p_val_tab(46) := p_rslt_rec.val_46;
1883 p_val_tab(47) := p_rslt_rec.val_47;
1884 p_val_tab(48) := p_rslt_rec.val_48;
1885 p_val_tab(49) := p_rslt_rec.val_49;
1886
1887 p_val_tab(50) := p_rslt_rec.val_50;
1888 p_val_tab(51) := p_rslt_rec.val_51;
1889 p_val_tab(52) := p_rslt_rec.val_52;
1890 p_val_tab(53) := p_rslt_rec.val_53;
1891 p_val_tab(54) := p_rslt_rec.val_54;
1892 p_val_tab(55) := p_rslt_rec.val_55;
1893 p_val_tab(56) := p_rslt_rec.val_56;
1894 p_val_tab(57) := p_rslt_rec.val_57;
1895 p_val_tab(58) := p_rslt_rec.val_58;
1896 p_val_tab(59) := p_rslt_rec.val_59;
1897
1898 p_val_tab(60) := p_rslt_rec.val_60;
1899 p_val_tab(61) := p_rslt_rec.val_61;
1900 p_val_tab(62) := p_rslt_rec.val_62;
1901 p_val_tab(63) := p_rslt_rec.val_63;
1902 p_val_tab(64) := p_rslt_rec.val_64;
1903 p_val_tab(65) := p_rslt_rec.val_65;
1904 p_val_tab(66) := p_rslt_rec.val_66;
1905 p_val_tab(67) := p_rslt_rec.val_67;
1906 p_val_tab(68) := p_rslt_rec.val_68;
1907 p_val_tab(69) := p_rslt_rec.val_69;
1908
1909 p_val_tab(70) := p_rslt_rec.val_70;
1910 p_val_tab(71) := p_rslt_rec.val_71;
1911 p_val_tab(72) := p_rslt_rec.val_72;
1912 p_val_tab(73) := p_rslt_rec.val_73;
1913 p_val_tab(74) := p_rslt_rec.val_74;
1914 p_val_tab(75) := p_rslt_rec.val_75;
1915 Hr_Utility.set_location('Leaving: '||l_proc_name, 15);
1916
1917 END Copy_Rec_Values;
1918
1919 -- =============================================================================
1920 -- Exclude_Person:
1921 -- =============================================================================
1922 PROCEDURE Exclude_Person
1923 (p_person_id IN NUMBER
1924 ,p_business_group_id IN NUMBER
1925 ,p_benefit_action_id IN NUMBER
1926 ,p_flag_thread IN VARCHAR2) IS
1927
1928 CURSOR csr_ben_per (c_person_id IN NUMBER
1929 ,c_benefit_action_id IN NUMBER) IS
1930 SELECT *
1931 FROM ben_person_actions bpa
1932 WHERE bpa.benefit_action_id = c_benefit_action_id
1933 AND bpa.person_id = c_person_id;
1934
1935 l_ben_per csr_ben_per%ROWTYPE;
1936
1937 CURSOR csr_rng (c_benefit_action_id IN NUMBER
1938 ,c_person_action_id IN NUMBER) IS
1939 SELECT 'x'
1940 FROM ben_batch_ranges
1941 WHERE benefit_action_id = c_benefit_action_id
1942 AND c_person_action_id BETWEEN starting_person_action_id
1943 AND ending_person_action_id;
1944 l_conc_reqest_id NUMBER(20);
1945 l_exists VARCHAR2(2);
1946 l_proc_name CONSTANT VARCHAR2(150) := g_proc_name ||'Exclude_Person';
1947 BEGIN
1948 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
1949 OPEN csr_ben_per (c_person_id => p_person_id
1950 ,c_benefit_action_id => p_benefit_action_id);
1951 FETCH csr_ben_per INTO l_ben_per;
1952 CLOSE csr_ben_per;
1953
1954 UPDATE ben_person_actions bpa
1955 SET bpa.action_status_cd = 'U'
1956 WHERE bpa.benefit_action_id = p_benefit_action_id
1957 AND bpa.person_id = p_person_id;
1958 IF p_flag_thread = 'Y' THEN
1959 OPEN csr_rng (c_benefit_action_id => p_benefit_action_id
1960 ,c_person_action_id => l_ben_per.person_action_id);
1961 FETCH csr_rng INTO l_exists;
1962 CLOSE csr_rng;
1963 UPDATE ben_batch_ranges bbr
1964 SET bbr.range_status_cd = 'E'
1965 WHERE bbr.benefit_action_id = p_benefit_action_id
1966 AND l_ben_per.person_action_id
1967 BETWEEN bbr.starting_person_action_id
1968 AND bbr.ending_person_action_id;
1969 END IF;
1970 Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
1971
1972 END Exclude_Person;
1973 -- =============================================================================
1974 -- Process_Ext_Rslt_Dtl_Rec:
1975 -- =============================================================================
1976 PROCEDURE Process_Ext_Rslt_Dtl_Rec
1977 (p_assignment_id IN per_all_assignments.assignment_id%TYPE
1978 ,p_organization_id IN per_all_assignments.organization_id%TYPE
1979 ,p_effective_date IN DATE
1980 ,p_ext_dtl_rcd_id IN ben_ext_rcd.ext_rcd_id%TYPE
1981 ,p_rslt_rec IN OUT NOCOPY ben_ext_rslt_dtl%ROWTYPE
1982 ,p_asgaction_no IN NUMBER
1983 ,p_error_message OUT NOCOPY VARCHAR2) IS
1984
1985 CURSOR csr_rule_ele
1986 (c_ext_rcd_id IN ben_ext_data_elmt_in_rcd.ext_rcd_id%TYPE) IS
1987 SELECT a.ext_data_elmt_in_rcd_id
1988 ,a.seq_num
1989 ,a.sprs_cd
1990 ,a.strt_pos
1991 ,a.dlmtr_val
1992 ,a.rqd_flag
1993 ,b.ext_data_elmt_id
1994 ,b.data_elmt_typ_cd
1995 ,b.data_elmt_rl
1996 ,b.NAME
1997 ,Hr_General.decode_lookup('BEN_EXT_FRMT_MASK',
1998 b.frmt_mask_cd) frmt_mask_cd
1999 ,b.frmt_mask_cd frmt_mask_lookup_cd
2000 ,b.string_val
2001 ,b.dflt_val
2002 ,b.max_length_num
2003 ,b.just_cd
2004 FROM ben_ext_data_elmt b,
2005 ben_ext_data_elmt_in_rcd a
2006 WHERE a.ext_data_elmt_id = b.ext_data_elmt_id
2007 AND b.data_elmt_typ_cd = 'R'
2008 AND a.ext_rcd_id = c_ext_rcd_id
2009 ORDER BY a.seq_num;
2010
2011 CURSOR csr_ff_type ( c_formula_type_id IN ff_formulas_f.formula_id%TYPE
2012 ,c_effective_date IN DATE) IS
2013 SELECT formula_type_id
2014 FROM ff_formulas_f
2015 WHERE formula_id = c_formula_type_id
2016 AND c_effective_date BETWEEN effective_start_date
2017 AND effective_end_date;
2018 --
2019 CURSOR csr_xrif (c_rcd_id IN NUMBER
2020 ,c_ext_dfn_id IN NUMBER ) IS
2021
2022 SELECT rif.ext_rcd_in_file_id
2023 ,rif.any_or_all_cd
2024 ,rif.seq_num
2025 ,rif.sprs_cd
2026 ,rif.rqd_flag
2027 FROM ben_ext_rcd_in_file rif
2028 ,ben_ext_dfn dfn
2029 WHERE rif.ext_file_id = dfn.ext_file_id
2030 AND rif.ext_rcd_id = c_rcd_id
2031 AND dfn.ext_dfn_id = c_ext_dfn_id;
2032 --
2033 l_ben_params csr_ben%ROWTYPE;
2034 l_proc_name CONSTANT VARCHAR2(150) := g_proc_name ||'Process_Ext_Rslt_Dtl_Rec';
2035 l_foumula_type_id ff_formulas_f.formula_id%TYPE;
2036 l_outputs Ff_Exec.outputs_t;
2037 l_ff_value ben_ext_rslt_dtl.val_01%TYPE;
2038 l_ff_value_fmt ben_ext_rslt_dtl.val_01%TYPE;
2039 l_max_len NUMBER;
2040 l_rqd_elmt_is_present VARCHAR2(2) := 'Y';
2041 l_person_id per_all_people_f.person_id%TYPE;
2042 --
2043 l_val_tab ValTabTyp;
2044 l_exclude_this_rcd_flag BOOLEAN;
2045 l_raise_warning BOOLEAN;
2046 l_rollback_person BOOLEAN;
2047 l_rslt_dtl_id NUMBER;
2048 --
2049 BEGIN
2050 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
2051 FOR i IN 1..75
2052 LOOP
2053 l_val_tab(i) := NULL;
2054 END LOOP;
2055
2056 FOR i IN csr_rule_ele( c_ext_rcd_id => p_ext_dtl_rcd_id)
2057 LOOP
2058 OPEN csr_ff_type(c_formula_type_id => i.data_elmt_rl
2059 ,c_effective_date => p_effective_date);
2060 FETCH csr_ff_type INTO l_foumula_type_id;
2061 CLOSE csr_ff_type;
2062 IF l_foumula_type_id = -413 THEN -- person level rule
2063 l_outputs := Benutils.formula
2064 (p_formula_id => i.data_elmt_rl
2065 ,p_effective_date => p_effective_date
2066 ,p_assignment_id => p_assignment_id
2067 ,p_organization_id => p_organization_id
2068 ,p_business_group_id => g_business_group_id
2069 ,p_jurisdiction_code => NULL
2070 ,p_param1 => 'EXT_DFN_ID'
2071 ,p_param1_value => To_Char(Nvl(Ben_Ext_Thread.g_ext_dfn_id, -1))
2072 ,p_param2 => 'EXT_RSLT_ID'
2073 ,p_param2_value => To_Char(Nvl(Ben_Ext_Thread.g_ext_rslt_id, -1))
2074 );
2075 l_ff_value := l_outputs(l_outputs.FIRST).VALUE;
2076 IF l_ff_value IS NULL THEN
2077 l_ff_value := i.dflt_val;
2078 END IF;
2079 BEGIN
2080 IF i.frmt_mask_lookup_cd IS NOT NULL AND
2081 l_ff_value IS NOT NULL THEN
2082 IF Substr(i.frmt_mask_lookup_cd,1,1) = 'N' THEN
2083 Hr_Utility.set_location('..Applying NUMBER format mask :ben_ext_fmt.apply_format_mask',50);
2084 l_ff_value_fmt := Ben_Ext_Fmt.apply_format_mask(To_Number(l_ff_value), i.frmt_mask_cd);
2085 l_ff_value := l_ff_value_fmt;
2086 ELSIF Substr(i.frmt_mask_lookup_cd,1,1) = 'D' THEN
2087 Hr_Utility.set_location('..Applying Date format mask :ben_ext_fmt.apply_format_mask',55);
2088 l_ff_value_fmt := Ben_Ext_Fmt.apply_format_mask(Fnd_Date.canonical_to_date(l_ff_value),
2089 i.frmt_mask_cd);
2090 l_ff_value := l_ff_value_fmt;
2091 END IF;
2092 END IF;
2093 EXCEPTION -- incase l_ff_value is not valid for formatting, just don't format it.
2094 WHEN Others THEN
2095 p_error_message := SQLERRM;
2096 END;
2097 -- Truncate data element if the max. length is given
2098 IF i.max_length_num IS NOT NULL THEN
2099 l_max_len := Least (Length(l_ff_value),i.max_length_num) ;
2100 -- numbers should always trunc from the left
2101 IF Substr(i.frmt_mask_lookup_cd,1,1) = 'N' THEN
2102 l_ff_value := Substr(l_ff_value, -l_max_len);
2103 ELSE -- everything else truncs from the right.
2104 l_ff_value := Substr(l_ff_value, 1, i.max_length_num);
2105 END IF;
2106 Hr_Utility.set_location('..After Max Length : '|| l_ff_value,56 );
2107 END IF;
2108 -- If the data element is required, and null then exit
2109 -- no need to re-execute the other data-elements in the record.
2110 IF i.rqd_flag = 'Y' AND (l_ff_value IS NULL) THEN
2111 l_rqd_elmt_is_present := 'N' ;
2112 EXIT ;
2113 END IF;
2114 -- Update the data-element value at the right seq. num within the
2115 -- record.
2116 Update_Record_Values
2117 (p_ext_rcd_id => p_ext_dtl_rcd_id
2118 ,p_ext_data_element_name => NULL
2119 ,p_data_element_value => l_ff_value
2120 ,p_data_ele_seqnum => i.seq_num
2121 ,p_ext_dtl_rec => p_rslt_rec);
2122 END IF;
2123 END LOOP; --For i in csr_rule_ele
2124 -- Copy the data-element values into a PL/SQL table
2125 Copy_Rec_Values
2126 (p_rslt_rec => p_rslt_rec
2127 ,p_val_tab => l_val_tab);
2128
2129 -- Check the Adv. Conditions for data elements in record
2130 Data_Elmt_In_Rcd
2131 (p_ext_rcd_id => p_rslt_rec.ext_rcd_id
2132 ,p_val_tab => l_val_tab
2133 ,p_exclude_this_rcd_flag => l_exclude_this_rcd_flag
2134 ,p_raise_warning => l_raise_warning
2135 ,p_rollback_person => l_rollback_person);
2136
2137 -- Need to remove all the detail records for the person
2138 IF l_rollback_person THEN
2139 g_total_dtl_lines := 0;
2140 END IF;
2141
2142 -- Check the Adv. Conditions for records in file
2143 FOR rif IN csr_xrif
2144 (c_rcd_id => p_rslt_rec.ext_rcd_id
2145 ,c_ext_dfn_id => Ben_Ext_Thread.g_ext_dfn_id )
2146 LOOP
2147 Rcd_In_File
2148 (p_ext_rcd_in_file_id => rif.ext_rcd_in_file_id
2149 ,p_sprs_cd => rif.sprs_cd
2150 ,p_val_tab => l_val_tab
2151 ,p_exclude_this_rcd_flag => l_exclude_this_rcd_flag
2152 ,p_raise_warning => l_raise_warning
2153 ,p_rollback_person => l_rollback_person);
2154 END LOOP;
2155
2156 -- Need to remove all the detail records for the person
2157 IF l_rollback_person THEN
2158 g_total_dtl_lines := 0;
2159 END IF;
2160
2161 -- If exclude record is not true, then insert or update record
2162 IF NOT l_exclude_this_rcd_flag AND
2163 l_rqd_elmt_is_present <> 'N' THEN
2164 g_total_dtl_lines := g_total_dtl_lines + 1;
2165 IF g_total_dtl_lines > 1 THEN
2166 Ins_Rslt_Dtl
2167 (p_dtl_rec => p_rslt_rec
2168 ,p_val_tab => l_val_tab
2169 ,p_rslt_dtl_id => l_rslt_dtl_id );
2170 ELSE
2171 Upd_Rslt_Dtl
2172 (p_dtl_rec => p_rslt_rec
2173 ,p_val_tab => l_val_tab);
2174 END IF; --IF g_total_dtl_lines
2175
2176 ELSIF l_exclude_this_rcd_flag THEN
2177
2178 OPEN csr_ben (c_ext_dfn_id => Ben_Ext_Thread.g_ext_dfn_id
2179 ,c_ext_rslt_id => Ben_Ext_Thread.g_ext_rslt_id
2180 ,c_business_group_id => g_business_group_id);
2181 FETCH csr_ben INTO l_ben_params;
2182 CLOSE csr_ben;
2183
2184 Exclude_Person
2185 (p_person_id => g_person_id
2186 ,p_business_group_id => g_business_group_id
2187 ,p_benefit_action_id => l_ben_params.benefit_action_id
2188 ,p_flag_thread => 'N'
2189 );
2190 END IF;
2191
2192 Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
2193
2194 EXCEPTION
2195 WHEN Others THEN
2196 p_error_message :='SQL-ERRM :'||SQLERRM;
2197 Hr_Utility.set_location('..'||p_error_message,85);
2198 Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
2199
2200 END Process_Ext_Rslt_Dtl_Rec;
2201
2202 -- =============================================================================
2203 -- Create_AsgAction_Lines: This procedure executes all the person based formulas
2204 -- for the ben_ext_rslt_dtl when inserting or updating the record.
2205 -- =============================================================================
2206 PROCEDURE Create_AsgAction_Lines
2207 (p_assignment_id IN NUMBER
2208 ,p_business_group_id IN NUMBER
2209 ,p_person_id IN NUMBER
2210 ,p_asgaction_no IN NUMBER
2211 ,p_error_message OUT NOCOPY VARCHAR2) IS
2212 l_proc_name VARCHAR2(150) := g_proc_name ||'Create_AsgAction_Lines';
2213 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
2214 l_organization_id per_all_assignments_f.organization_id%TYPE;
2215 l_business_group_id per_all_assignments_f.business_group_id%TYPE;
2216 l_main_rec csr_rslt_dtl%ROWTYPE;
2217 l_new_rec csr_rslt_dtl%ROWTYPE;
2218 l_effective_date DATE;
2219 BEGIN
2220 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
2221 FOR csr_rcd_rec IN csr_ext_rcd_id(c_hide_flag => 'N' -- N=No Y=Yes
2222 ,c_rcd_type_cd => 'D')-- D=Detail, T=Total, H-Header
2223 LOOP
2224 g_ext_dtl_rcd_id := csr_rcd_rec.ext_rcd_id;
2225 OPEN csr_rslt_dtl
2226 (c_person_id => p_person_id
2227 ,c_ext_rslt_id => Ben_Ext_Thread.g_ext_rslt_id
2228 ,c_ext_dtl_rcd_id => g_ext_dtl_rcd_id
2229 );
2230 FETCH csr_rslt_dtl INTO l_main_rec;
2231 CLOSE csr_rslt_dtl;
2232
2233 l_main_rec.object_version_number
2234 := Nvl(l_main_rec.object_version_NUMBER,0) + 1;
2235 l_new_rec := l_main_rec;
2236 l_assignment_id := p_assignment_id;
2237 l_organization_id := g_primary_assig(p_assignment_id).organization_id;
2238 l_business_group_id := p_business_group_id;
2239 l_effective_date := g_action_effective_date;
2240 -- Re-Process the person level rule based data-element for the record
2241 -- along with appropiate effective date and assignment id.
2242 Process_Ext_Rslt_Dtl_Rec
2243 (p_assignment_id => l_assignment_id
2244 ,p_organization_id => l_organization_id
2245 ,p_effective_date => l_effective_date
2246 ,p_ext_dtl_rcd_id => g_ext_dtl_rcd_id
2247 ,p_rslt_rec => l_main_rec
2248 ,p_asgaction_no => p_asgaction_no
2249 ,p_error_message => p_error_message
2250 );
2251 END LOOP;
2252
2253 Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
2254
2255 EXCEPTION
2256
2257 WHEN Others THEN
2258 p_error_message :='SQL-ERRM :'||SQLERRM;
2259 Hr_Utility.set_location('..'||p_error_message,85);
2260 Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
2261
2262 END Create_AsgAction_Lines;
2263
2264 -- =============================================================================
2265 -- Get_Element_Details: Get the details of the element(s) in a element set or
2266 -- a single a element.
2267 -- =============================================================================
2268 PROCEDURE Get_Element_Details
2269 (p_element_type_id IN NUMBER
2270 ,p_element_set_id IN NUMBER
2271 ,p_effective_date IN DATE
2272 ,p_business_group_id IN NUMBER) IS
2273 l_proc_name VARCHAR2(150) := g_proc_name ||'Get_Element_Details';
2274 l_ele_type_id pay_element_types_f.element_type_id%TYPE;
2275 l_prev_ele_type_id pay_element_types_f.element_type_id%TYPE;
2276 l_CatchUp_ele_type_id pay_element_types_f.element_type_id%TYPE;
2277 l_AT_ele_type_id pay_element_types_f.element_type_id%TYPE;
2278 BEGIN
2279 hr_utility.set_location('Entering: '||l_proc_name, 5);
2280 -- If element set was selected
2281 FOR ele_rec IN csr_ele_id (c_element_set_id => p_element_set_id)
2282 LOOP
2283 Get_Element_Info
2284 (p_element_type_id => ele_rec.element_type_id
2285 ,p_effective_date => p_effective_date
2286 ,p_business_group_id => p_business_group_id);
2287 END LOOP;
2288 -- If a single element was selected
2289 IF p_element_type_id IS NOT NULL THEN
2290 Get_Element_Info
2291 (p_element_type_id => p_element_type_id
2292 ,p_effective_date => p_effective_date
2293 ,p_business_group_id => p_business_group_id);
2294 END IF;
2295 hr_utility.set_location('Leaving: '||l_proc_name, 80);
2296 END Get_Element_Details;
2297 -- =============================================================================
2298 -- Process_Assignments: This procedure creates a row in ben_ext_rslt_dtl table
2299 -- for each valid assig action when the reporting dimension is ASG_RUN.
2300 -- =============================================================================
2301 PROCEDURE Process_Assignments
2302 (p_assignment_id IN NUMBER
2303 ,p_business_group_id IN NUMBER
2304 ,p_return_value IN OUT NOCOPY VARCHAR2
2305 ,p_no_asg_action IN OUT NOCOPY NUMBER
2306 ,p_error_message OUT NOCOPY VARCHAR2
2307 ) IS
2308
2309 l_ele_type_id pay_element_types_f.element_type_id%TYPE;
2310 l_prev_ele_type_id pay_element_types_f.element_type_id%TYPE;
2311 l_valid_action VARCHAR2(2);
2312 i per_all_assignments_f.business_group_id%TYPE;
2313 l_proc_name CONSTANT VARCHAR2(150) := g_proc_name ||'Process_Assignments';
2314 l_valid_person_chk VARCHAR2(2);
2315
2316 BEGIN
2317 hr_utility.set_location('Entering: '||l_proc_name, 5);
2318
2319 i := p_business_group_id;
2320 IF g_extract_params(i).reporting_dimension = 'ASG_RUN' THEN
2321 -- Reporting Dimension is ASG_RUN
2322 FOR act_rec IN csr_gre_act
2323 (c_assignment_id => p_assignment_id
2324 ,c_payroll_id => g_extract_params(i).payroll_id
2325 ,c_con_set_id => g_extract_params(i).con_set_id
2326 ,c_gre_id => g_extract_params(i).gre_org_id
2327 ,c_start_date => g_extract_params(i).extract_start_date
2328 ,c_end_date => g_extract_params(i).extract_end_date
2329 )
2330 LOOP
2331 --
2332 -- Re-set these values for the next asg. action, for each Asg Action
2333 --
2334 p_return_value := 'NOTFOUND';
2335 g_AfterTax.Count := 0;
2336 g_CatchUp.Count := 0;
2337 g_PreTax.Count := 0;
2338 --
2339 -- Now check for each element if its processed in the assignment action
2340 --
2341 l_ele_type_id := g_element.first;
2342 WHILE l_ele_type_id IS NOT NULL
2343 LOOP
2344
2345 OPEN csr_ele_run (c_asg_action_id => act_rec.assignment_action_id
2346 ,c_element_type_id => l_ele_type_id);
2347 FETCH csr_ele_run INTO l_valid_action;
2348 IF csr_ele_run%FOUND AND
2349 p_return_value <> 'FOUND' THEN
2350 p_return_value := 'FOUND';
2351 g_asg_action_id := act_rec.assignment_action_id;
2352 g_action_effective_date := act_rec.effective_date;
2353 g_gre_org_id := act_rec.tax_unit_id;
2354 g_action_type := act_rec.action_type;
2355 p_no_asg_action := p_no_asg_action + 1;
2356 END IF;
2357 CLOSE csr_ele_run;
2358 l_prev_ele_type_id := l_ele_type_id;
2359 l_ele_type_id := g_element.next(l_prev_ele_type_id);
2360
2361 END LOOP; -- While Loop
2362
2363 IF p_return_value = 'FOUND' THEN
2364
2365 g_primary_assig(p_assignment_id).Calculate_Amount := 'YES';
2366 Create_AsgAction_Lines
2367 (p_assignment_id => p_assignment_id
2368 ,p_business_group_id => p_business_group_id
2369 ,p_person_id => g_primary_assig(p_assignment_id).person_id
2370 ,p_asgaction_no => p_no_asg_action
2371 ,p_error_message => p_error_message
2372 );
2373
2374 END IF;
2375 g_gre_org_id := NULL;
2376 g_action_effective_date := NULL;
2377 END LOOP; -- FOR act_rec IN csr_asg_act
2378
2379 ELSE
2380 -- For all other reporting dimensions we on have one extract record for
2381 -- an assignment.
2382 hr_utility.set_location(' Summary Reporting Dimension', 5);
2383 g_gre_org_id := NULL;
2384 g_action_effective_date := NULL;
2385
2386 g_primary_assig(p_assignment_id).Calculate_Amount := 'YES';
2387 g_action_effective_date :=
2388 least(g_primary_assig(p_assignment_id).effective_end_date,
2389 g_extract_params(i).extract_end_date);
2390 IF g_legislation_code ='MX' THEN
2391
2392 FOR act_rec IN csr_gre_act
2393 (c_assignment_id => p_assignment_id
2394 ,c_payroll_id => g_extract_params(i).payroll_id
2395 ,c_con_set_id => g_extract_params(i).con_set_id
2396 ,c_gre_id => g_extract_params(i).gre_org_id
2397 ,c_start_date => g_extract_params(i).extract_start_date
2398 ,c_end_date => g_extract_params(i).extract_end_date
2399 )
2400 LOOP
2401 IF (g_gre_org_id IS NULL) OR
2402 (act_rec.tax_unit_id IS NOT NULL AND
2403 g_gre_org_id <> act_rec.tax_unit_id) THEN
2404
2405 g_gre_org_id := act_rec.tax_unit_id;
2406 g_action_effective_date := act_rec.effective_date;
2407 hr_utility.set_location(' g_gre_org_id: '||g_gre_org_id, 5);
2408 Create_AsgAction_Lines
2409 (p_assignment_id => p_assignment_id
2410 ,p_business_group_id => p_business_group_id
2411 ,p_person_id => g_primary_assig(p_assignment_id).person_id
2412 ,p_asgaction_no => p_no_asg_action + 1
2413 ,p_error_message => p_error_message
2414 );
2415
2416 END IF;
2417 END LOOP;
2418
2419 ELSE
2420 Create_AsgAction_Lines
2421 (p_assignment_id => p_assignment_id
2422 ,p_business_group_id => p_business_group_id
2423 ,p_person_id => g_primary_assig(p_assignment_id).person_id
2424 ,p_asgaction_no => p_no_asg_action
2425 ,p_error_message => p_error_message
2426 );
2427 END IF;
2428 END IF; -- If reporting_dimension = 'ASG_RUN'
2429
2430 hr_utility.set_location('Leaving: '||l_proc_name, 80);
2431
2432 END Process_Assignments;
2433
2434 -- =============================================================================
2435 -- Check_Asg_Actions: This function is called by the hidden record to check for
2436 -- the terminated and secondary assigs records with the extract date-range.
2437 -- =============================================================================
2438 FUNCTION Check_Asg_Actions
2439 (p_assignment_id IN NUMBER
2440 ,p_business_group_id IN NUMBER
2441 ,p_effective_date IN DATE
2442 ,p_error_message OUT NOCOPY VARCHAR2
2443 ) RETURN VARCHAR2 IS
2444
2445 l_return_value VARCHAR2(50);
2446 i per_all_assignments_f.business_group_id%TYPE;
2447 l_ele_type_id pay_element_types_f.element_type_id%TYPE;
2448 l_prev_ele_type_id pay_element_types_f.element_type_id%TYPE;
2449 l_valid_action VARCHAR2(2);
2450 l_no_asg_action NUMBER(5) := 0;
2451 l_proc_name VARCHAR2(150) := g_proc_name ||'Check_Asg_Actions';
2452 l_sec_assg_rec csr_sec_assg%ROWTYPE;
2453 l_effective_date DATE;
2454 l_criteria_value VARCHAR2(2);
2455 l_warning_message VARCHAR2(2000);
2456 l_error_message VARCHAR2(2000);
2457 l_asg_type per_all_assignments_f.assignment_type%TYPE;
2458 l_main_rec csr_rslt_dtl%ROWTYPE;
2459 l_person_id per_all_people_f.person_id%TYPE;
2460 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
2461 BEGIN
2462
2463 hr_utility.set_location('Entering: '||l_proc_name, 5);
2464 i := p_business_group_id;
2465
2466 IF NOT g_primary_assig.EXISTS(p_assignment_id) THEN
2467 l_return_value := 'NOTFOUND';
2468 hr_utility.set_location('..Not a Valid assignment: '||p_assignment_id, 6);
2469 RETURN l_return_value;
2470
2471 ELSIF g_primary_assig(p_assignment_id).assignment_type IN ('B','E') THEN
2472
2473 l_person_id := g_primary_assig(p_assignment_id).person_id;
2474 l_asg_type := g_primary_assig(p_assignment_id).assignment_type;
2475 g_processing_addl_asgs:= TRUE;
2476
2477 IF g_debug THEN
2478 hr_utility.set_location('..l_asg_type : '||l_asg_type, 6);
2479 hr_utility.set_location('..p_assignment_id : '||p_assignment_id, 6);
2480 hr_utility.set_location('..l_person_id : '||l_person_id, 6);
2481 END IF;
2482 -- Check if there are any other assignments which might be active within the
2483 -- specified extract date range.
2484 FOR sec_asg_rec IN
2485 csr_sec_assg
2486 (c_primary_assignment_id => p_assignment_id
2487 ,c_person_id => g_primary_assig(p_assignment_id).person_id
2488 ,c_effective_date => g_extract_params(i).extract_end_date
2489 ,c_extract_start_date => g_extract_params(i).extract_start_date
2490 ,c_extract_end_date => g_extract_params(i).extract_end_date)
2491 LOOP
2492 l_sec_assg_rec := sec_asg_rec;
2493 l_criteria_value := 'N';
2494 l_effective_date := least(g_extract_params(i).extract_end_date,
2495 l_sec_assg_rec.effective_end_date);
2496 hr_utility.set_location('..Checking for assignment : '||l_sec_assg_rec.assignment_id, 7);
2497 hr_utility.set_location('..p_effective_date : '||l_effective_date, 7);
2498 -- Call the main criteria function for this assignment to check if its a valid
2499 -- assignment that can be reported based on the criteria specified.
2500 l_criteria_value := Pension_Criteria_Full_Profile
2501 (p_assignment_id => l_sec_assg_rec.assignment_id
2502 ,p_effective_date => l_effective_date
2503 ,p_business_group_id => p_business_group_id
2504 ,p_warning_message => l_warning_message
2505 ,p_error_message => l_error_message
2506 );
2507 END LOOP;
2508 g_processing_addl_asgs:= FALSE;
2509 END IF;
2510 hr_utility.set_location('..Assignment Count : '||g_primary_assig.COUNT, 7);
2511 hr_utility.set_location('..l_person_id : '||l_person_id, 7);
2512
2513 -- For each assignment for this person id check if additional rows need to be
2514 -- created and re-calculate the person level based fast-formulas.
2515 g_total_dtl_lines := 0;
2516 l_assignment_id := g_primary_assig.first;
2517
2518 WHILE l_assignment_id IS NOT NULL
2519 LOOP
2520 hr_utility.set_location('..Checking for Assignment : '||l_assignment_id, 7);
2521 IF g_primary_assig(l_assignment_id).person_id = l_person_id AND
2522 g_primary_assig(l_assignment_id).Assignment_Type = 'E' THEN
2523
2524 g_primary_assig(l_assignment_id).Calculate_Amount := 'YES';
2525 IF g_debug THEN
2526 hr_utility.set_location('..Valid Assignment : '||l_assignment_id, 8);
2527 hr_utility.set_location('..l_no_asg_action : '||l_no_asg_action, 8);
2528 END IF;
2529 Process_Assignments
2530 (p_assignment_id => l_assignment_id
2531 ,p_business_group_id => p_business_group_id
2532 ,p_return_value => l_return_value
2533 ,p_no_asg_action => l_no_asg_action
2534 ,p_error_message => l_error_message
2535 );
2536 l_no_asg_action := l_no_asg_action + 1;
2537 END IF;
2538 l_assignment_id := g_primary_assig.NEXT(l_assignment_id);
2539 l_return_value := 'NOTFOUND';
2540
2541 END LOOP;
2542
2543 IF l_asg_type = 'B' AND l_no_asg_action = 0 THEN
2544 -- This mean that the extract created a row for the benefit's assig.
2545 -- record and that person does not have any employee assig. record
2546 -- within the extract date range specified. So we need to get rid of
2547 -- this record from the extract results details.
2548 OPEN csr_ext_rcd_id(c_hide_flag => 'N' -- N=No record is not hidden one
2549 ,c_rcd_type_cd => 'D' ); -- D=Detail, T=Total, H-Header Record types
2550 FETCH csr_ext_rcd_id INTO g_ext_dtl_rcd_id;
2551 CLOSE csr_ext_rcd_id;
2552 --
2553 OPEN csr_rslt_dtl
2554 (c_person_id => l_person_id
2555 ,c_ext_rslt_id => ben_ext_thread.g_ext_rslt_id
2556 ,c_ext_dtl_rcd_id => g_ext_dtl_rcd_id
2557 );
2558 FETCH csr_rslt_dtl INTO l_main_rec;
2559 CLOSE csr_rslt_dtl;
2560 --
2561 DELETE ben_ext_rslt_dtl
2562 WHERE ext_rslt_dtl_id = l_main_rec.ext_rslt_dtl_id
2563 AND person_id = l_person_id;
2564 END IF;
2565 g_AfterTax.Count := 0;
2566 g_CatchUp.Count := 0;
2567 g_PreTax.Count := 0;
2568 -- Once the sec. record has been taken care of all the asg actions remove it
2569 -- from the PL/SQL table.
2570 l_assignment_id := g_primary_assig.first;
2571 WHILE l_assignment_id IS NOT NULL
2572 LOOP
2573 IF g_primary_assig(l_assignment_id).person_id = l_person_id THEN
2574 g_primary_assig.DELETE(l_assignment_id);
2575 END IF;
2576 l_assignment_id := g_primary_assig.NEXT(l_assignment_id);
2577 END LOOP;
2578 hr_utility.set_location('Leaving: '||l_proc_name, 80);
2579
2580 l_assignment_id := g_mx_rules.first;
2581 WHILE l_assignment_id IS NOT NULL
2582 LOOP
2583 IF g_mx_rules(l_assignment_id).person_id <> l_person_id THEN
2584 g_mx_rules.DELETE(l_assignment_id);
2585 END IF;
2586 l_assignment_id := g_mx_rules.NEXT(l_assignment_id);
2587 END LOOP;
2588 RETURN l_return_value;
2589
2590 EXCEPTION
2591 WHEN Others THEN
2592 p_error_message :='SQL-ERRM :'||SQLERRM;
2593 hr_utility.set_location('..'||p_error_message,85);
2594 hr_utility.set_location('Leaving: '||l_proc_name, 90);
2595 RETURN l_return_value;
2596 END Check_Asg_Actions;
2597
2598 -- =============================================================================
2599 -- Get_SIT_Segment:
2600 -- =============================================================================
2601 FUNCTION Get_SIT_Segment
2602 (p_business_group_id IN NUMBER
2603 ,p_assignment_id IN NUMBER
2604 ,p_effective_date IN DATE
2605 ,p_structure_code IN VARCHAR2
2606 ,p_segment_name IN VARCHAR2
2607 ,p_error_code OUT NOCOPY VARCHAR2
2608 ,p_err_message OUT NOCOPY VARCHAR2
2609 ) RETURN VARCHAR2 AS
2610
2611 CURSOR csr_flex_num (c_structure_code IN VARCHAR2) IS
2612 SELECT id_flex_structure_code
2613 ,id_flex_num
2614 FROM fnd_id_flex_structures_vl
2615 WHERE application_id = 800
2616 AND id_flex_code = 'PEA'
2617 AND id_flex_structure_code = c_structure_code;
2618 l_flex csr_flex_num%ROWTYPE;
2619
2620 CURSOR csr_pe (c_business_group_id IN NUMBER
2621 ,c_person_id IN NUMBER
2622 ,c_id_flex_num IN NUMBER
2623 ,c_effective_date IN DATE) IS
2624 SELECT *
2625 FROM per_person_analyses ppa
2626 WHERE ppa.business_group_id = c_business_group_id
2627 AND ppa.person_id = c_person_id
2628 AND ppa.id_flex_num = c_id_flex_num
2629 AND c_effective_date BETWEEN nvl(ppa.date_from,c_effective_date)
2630 AND nvl(ppa.date_to,c_effective_date);
2631 l_per_analysis_rec per_person_analyses%ROWTYPE;
2632
2633 CURSOR csr_kff_seg (c_anal_criteria_id IN NUMBER
2634 ,c_flex_num IN NUMBER
2635 ,c_effective_date IN DATE) IS
2636 SELECT *
2637 FROM per_analysis_criteria
2638 WHERE analysis_criteria_id = c_anal_criteria_id
2639 AND id_flex_num = c_flex_num
2640 AND c_effective_date BETWEEN NVL(start_date_active,c_effective_date)
2641 AND NVL(end_date_active,c_effective_date);
2642
2643 l_analysis_criteria_rec per_analysis_criteria%ROWTYPE;
2644
2645 l_asg_rec csr_asg%ROWTYPE;
2646 Invaild_kff_flex EXCEPTION;
2647 l_return_value VARCHAR2(150);
2648 l_proc_name VARCHAR2(150) := g_proc_name ||'Get_SIT_Segment';
2649 BEGIN
2650 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
2651 p_error_code := '0';
2652 g_business_group_id := p_business_group_id;
2653 OPEN csr_asg (c_assignment_id => p_assignment_id
2654 ,c_effective_date => p_effective_date);
2655 FETCH csr_asg INTO l_asg_rec;
2656 IF csr_asg%NOTFOUND THEN
2657 p_error_code := '-20001';
2658 p_err_message := 'Invalid assignment_id :'||p_assignment_id||
2659 ' for effective date :'||p_effective_date;
2660 CLOSE csr_asg;
2661 l_return_value := 'EXT_ERR_WARNING';
2662 RETURN l_return_value;
2663 END IF;
2664 CLOSE csr_asg;
2665 -- Get the Key Flex Number for given Structure code
2666 OPEN csr_flex_num (c_structure_code => p_structure_code);
2667 FETCH csr_flex_num INTO l_flex;
2668 CLOSE csr_flex_num;
2669 -- Get the Key Flex for the person if present for the person
2670 OPEN csr_pe (c_business_group_id => p_business_group_id
2671 ,c_person_id => l_asg_rec.person_id
2672 ,c_id_flex_num => l_flex.id_flex_num
2673 ,c_effective_date => p_effective_date);
2674 FETCH csr_pe INTO l_per_analysis_rec;
2675 IF csr_pe%NOTFOUND THEN
2676 CLOSE csr_pe;
2677 RETURN l_return_value;
2678 END IF;
2679 CLOSE csr_pe;
2680 -- Get the KFF segments
2681 OPEN csr_kff_seg
2682 (c_anal_criteria_id => l_per_analysis_rec.analysis_criteria_id
2683 ,c_flex_num => l_flex.id_flex_num
2684 ,c_effective_date => p_effective_date);
2685 FETCH csr_kff_seg INTO l_analysis_criteria_rec;
2686 CLOSE csr_kff_seg;
2687
2688 IF p_segment_name = 'SEGMENT1' THEN
2689 l_return_value := l_analysis_criteria_rec.segment1;
2690 ELSIF p_segment_name = 'SEGMENT2' THEN
2691 l_return_value := l_analysis_criteria_rec.segment2;
2692 ELSIF p_segment_name = 'SEGMENT3' THEN
2693 l_return_value := l_analysis_criteria_rec.segment3;
2694 ELSIF p_segment_name = 'SEGMENT4' THEN
2695 l_return_value := l_analysis_criteria_rec.segment4;
2696 ELSIF p_segment_name = 'SEGMENT5' THEN
2697 l_return_value := l_analysis_criteria_rec.segment5;
2698 ELSIF p_segment_name = 'SEGMENT6' THEN
2699 l_return_value := l_analysis_criteria_rec.segment6;
2700 ELSIF p_segment_name = 'SEGMENT7' THEN
2701 l_return_value := l_analysis_criteria_rec.segment7;
2702 ELSIF p_segment_name = 'SEGMENT8' THEN
2703 l_return_value := l_analysis_criteria_rec.segment8;
2704 ELSIF p_segment_name = 'SEGMENT9' THEN
2705 l_return_value := l_analysis_criteria_rec.segment9;
2706 ELSIF p_segment_name = 'SEGMENT10' THEN
2707 l_return_value := l_analysis_criteria_rec.segment10;
2708 ELSIF p_segment_name = 'SEGMENT11' THEN
2709 l_return_value := l_analysis_criteria_rec.segment11;
2710 ELSIF p_segment_name = 'SEGMENT12' THEN
2711 l_return_value := l_analysis_criteria_rec.segment12;
2712 ELSIF p_segment_name = 'SEGMENT13' THEN
2713 l_return_value := l_analysis_criteria_rec.segment13;
2714 ELSIF p_segment_name = 'SEGMENT14' THEN
2715 l_return_value := l_analysis_criteria_rec.segment14;
2716 ELSIF p_segment_name = 'SEGMENT15' THEN
2717 l_return_value := l_analysis_criteria_rec.segment15;
2718 ELSIF p_segment_name = 'SEGMENT16' THEN
2719 l_return_value := l_analysis_criteria_rec.segment16;
2720 ELSIF p_segment_name = 'SEGMENT17' THEN
2721 l_return_value := l_analysis_criteria_rec.segment17;
2722 ELSIF p_segment_name = 'SEGMENT18' THEN
2723 l_return_value := l_analysis_criteria_rec.segment18;
2724 ELSIF p_segment_name = 'SEGMENT19' THEN
2725 l_return_value := l_analysis_criteria_rec.segment19;
2726 ELSIF p_segment_name = 'SEGMENT20' THEN
2727 l_return_value := l_analysis_criteria_rec.segment20;
2728 ELSIF p_segment_name = 'SEGMENT21' THEN
2729 l_return_value := l_analysis_criteria_rec.segment21;
2730 ELSIF p_segment_name = 'SEGMENT22' THEN
2731 l_return_value := l_analysis_criteria_rec.segment22;
2732 ELSIF p_segment_name = 'SEGMENT23' THEN
2733 l_return_value := l_analysis_criteria_rec.segment23;
2734 ELSIF p_segment_name = 'SEGMENT24' THEN
2735 l_return_value := l_analysis_criteria_rec.segment24;
2736 ELSIF p_segment_name = 'SEGMENT25' THEN
2737 l_return_value := l_analysis_criteria_rec.segment25;
2738 ELSIF p_segment_name = 'SEGMENT26' THEN
2739 l_return_value := l_analysis_criteria_rec.segment26;
2740 ELSIF p_segment_name = 'SEGMENT27' THEN
2741 l_return_value := l_analysis_criteria_rec.segment27;
2742 ELSIF p_segment_name = 'SEGMENT28' THEN
2743 l_return_value := l_analysis_criteria_rec.segment28;
2744 ELSIF p_segment_name = 'SEGMENT29' THEN
2745 l_return_value := l_analysis_criteria_rec.segment29;
2746 ELSIF p_segment_name = 'SEGMENT30' THEN
2747 l_return_value := l_analysis_criteria_rec.segment30;
2748 END IF;
2749
2750 l_return_value := Nvl(l_return_value,'EXT_NULL_VALUE');
2751 Hr_Utility.set_location('Leaving: '||l_proc_name,80);
2752 RETURN l_return_value;
2753
2754 EXCEPTION
2755 WHEN Invaild_kff_flex THEN
2756 p_error_code := '-20001';
2757 p_err_message := 'Invalid Key Flex structure code.';
2758 l_return_value := 'EXT_ERR_WARNING';
2759 Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
2760 RAISE;
2761 -- RETURN l_return_value;
2762
2763 WHEN Others THEN
2764 l_return_value := 'EXT_ERR_WARNING';
2765 p_error_code := '-20001';
2766 p_err_message :='SQL-ERRM :'||SQLERRM;
2767 Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
2768 RETURN l_return_value;
2769
2770 END Get_SIT_Segment;
2771
2772 -- =============================================================================
2773 -- Get_DDF_Value:
2774 -- =============================================================================
2775 FUNCTION Get_DDF_DF_Value
2776 (p_business_group_id IN NUMBER
2777 ,p_assignment_id IN NUMBER
2778 ,p_effective_date IN DATE
2779 ,p_flex_name IN VARCHAR2
2780 ,p_flex_context IN VARCHAR2
2781 ,p_flex_field_title IN VARCHAR2
2782 ,p_error_code OUT NOCOPY VARCHAR2
2783 ,p_err_message OUT NOCOPY VARCHAR2
2784 ) RETURN VARCHAR2 AS
2785
2786 CURSOR csr_pei (c_person_id IN NUMBER
2787 ,c_information_type IN VARCHAR2) IS
2788 SELECT pei.person_extra_info_id
2789 FROM per_people_extra_info pei
2790 WHERE pei.person_id = c_person_id
2791 AND pei.information_type = c_information_type;
2792
2793 CURSOR csr_aei (c_assignment_id IN NUMBER
2794 ,c_information_type IN VARCHAR2) IS
2795 SELECT aei.assignment_extra_info_id
2796 FROM per_assignment_extra_info aei
2797 WHERE aei.assignment_id = c_assignment_id
2798 AND aei.information_type = c_information_type;
2799
2800 CURSOR csr_asg_mult_occur(c_information_type IN VARCHAR2) IS
2801 SELECT multiple_occurences_flag
2802 FROM per_assignment_info_types
2803 WHERE information_type = c_information_type
2804 AND active_inactive_flag = 'Y';
2805
2806 CURSOR csr_per_mult_occur(c_information_type IN VARCHAR2) IS
2807 SELECT multiple_occurences_flag
2808 FROM per_people_info_types
2809 WHERE information_type = c_information_type
2810 AND active_inactive_flag = 'Y';
2811 --
2812 -- Get the GRE Id for the assignment passed
2813 --
2814 CURSOR csr_gre_id (c_assignment_id IN NUMBER
2815 ,c_business_group_id IN NUMBER
2816 ,c_effective_date IN DATE) IS
2817 SELECT to_number(nvl(segment1,0))
2818 FROM per_all_assignments_f paf
2819 ,hr_soft_coding_keyflex hfc
2820 WHERE paf.assignment_id = c_assignment_id
2821 AND paf.business_group_id = c_business_group_id
2822 AND c_effective_date BETWEEN paf.effective_start_date
2823 AND paf.effective_end_date
2824 AND paf.soft_coding_keyflex_id = hfc.soft_coding_keyflex_id;
2825
2826 CURSOR csr_info_id (c_organization_id IN NUMBER
2827 ,c_flex_context IN VARCHAR2) IS
2828 SELECT org_information_id
2829 FROM hr_organization_information
2830 WHERE organization_id = c_organization_id
2831 AND org_information_context = c_flex_context;
2832
2833 --
2834 -- To get the person id for the given assignment id
2835 --
2836 CURSOR csr_per_id(c_assignment_id IN NUMBER
2837 ,c_business_group_id IN NUMBER
2838 ,c_effective_date IN DATE) IS
2839 SELECT paf.person_id
2840 FROM per_all_assignments_f paf
2841 WHERE paf.assignment_id = c_assignment_id
2842 AND paf.business_group_id = c_business_group_id
2843 AND c_effective_date BETWEEN paf.effective_start_date
2844 AND paf.effective_end_date;
2845
2846
2847 l_assignment_extra_info_id csr_aei%ROWTYPE;
2848 l_person_extra_info_id csr_pei%ROWTYPE;
2849 l_asg_rec csr_asg%ROWTYPE;
2850
2851 l_organization_id hr_all_organization_units.organization_id%TYPE;
2852 l_proc_name VARCHAR2(150) := g_proc_name ||'Get_DDF_DF_Value';
2853 l_key_val NUMBER;
2854 l_key_col VARCHAR2(150);
2855 l_df_key_val VARCHAR2(150);
2856 l_tab_name VARCHAR2(150);
2857 l_ddf_seg_value VARCHAR2(150);
2858 l_df_seg_value VARCHAR2(150);
2859 l_return_value VARCHAR2(150);
2860 l_mult_occur VARCHAR2(2);
2861 Invaild_DDF_or_DF EXCEPTION;
2862 /*
2863 +=============================+=========================+============================+
2864 |DDF/DF Title | p_flex_name |TABLE |
2865 +=============================+=========================+============================+
2866 |Extra Assignment Information |Assignment Developer DF |PER_ASSIGNMENT_EXTRA_INFO |
2867 |Assignment Extra Information |PER_ASSIGNMENT_EXTRA_INFO| -DO- |
2868 |Extra Person Information |Extra Person Info DDF |PER_PEOPLE_EXTRA_INFO |
2869 |Extra Person Info. Details |PER_PEOPLE_EXTRA_INFO | -DO- |
2870 |Org Developer DF |Org Developer DF |HR_ORGANIZATION_INFORMATION |
2871 |Further Person Information |Person Developer DF |PER_ALL_PEOPLE_F |
2872 | | | |
2873 +=============================+=========================+============================+
2874 */
2875
2876 BEGIN
2877 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
2878 -- Get the legisaltion code if null
2879 IF g_legislation_code IS NULL THEN
2880 g_legislation_code := Get_Leg_Code(p_business_group_id);
2881 END IF;
2882 -- Get the person id
2883 OPEN csr_per_id(c_assignment_id => p_assignment_id
2884 ,c_business_group_id => p_business_group_id
2885 ,c_effective_date => p_effective_date);
2886 FETCH csr_per_id INTO g_person_id;
2887 CLOSE csr_per_id;
2888
2889 p_error_code := '0';
2890 OPEN csr_asg (c_assignment_id => p_assignment_id
2891 ,c_effective_date => p_effective_date);
2892 FETCH csr_asg INTO l_asg_rec;
2893 IF csr_asg%NOTFOUND THEN
2894 p_error_code := '-20001';
2895 p_err_message := 'Invalid assignment_id :'||p_assignment_id||
2896 ' for effective date :'||p_effective_date;
2897 CLOSE csr_asg;
2898 l_return_value := 'EXT_ERR_WARNING';
2899 RETURN l_return_value;
2900 END IF;
2901 CLOSE csr_asg;
2902
2903 IF p_flex_name IN('Extra Person Info DDF',
2904 'PER_PEOPLE_EXTRA_INFO') THEN
2905 Hr_Utility.set_location('..p_flex_name = '||p_flex_name, 6);
2906 OPEN csr_per_mult_occur(c_information_type => p_flex_context);
2907 FETCH csr_per_mult_occur INTO l_mult_occur;
2908 CLOSE csr_per_mult_occur;
2909 IF l_mult_occur <> 'Y' THEN
2910 Hr_Utility.set_location('..l_mult_occur = '||l_mult_occur, 6);
2911 OPEN csr_pei (c_person_id => l_asg_rec.person_id
2912 ,c_information_type => p_flex_context);
2913 FETCH csr_pei INTO l_key_val;
2914 CLOSE csr_pei;
2915 l_key_col := 'PERSON_EXTRA_INFO_ID';
2916 l_tab_name := 'PER_PEOPLE_EXTRA_INFO';
2917 ELSE
2918 Hr_Utility.set_location('..l_mult_occur = '||l_mult_occur, 6);
2919 p_error_code := '-20001';
2920 p_err_message := 'Contexts :'||p_flex_context ||
2921 ' can have multiple occurances';
2922 l_return_value := 'EXT_ERR_WARNING';
2923 RETURN l_return_value;
2924 END IF;
2925
2926 ELSIF p_flex_name IN('Assignment Developer DF',
2927 'PER_ASSIGNMENT_EXTRA_INFO') THEN
2928 Hr_Utility.set_location('..p_flex_name = '||p_flex_name, 7);
2929 OPEN csr_asg_mult_occur(c_information_type => p_flex_context);
2930 FETCH csr_asg_mult_occur INTO l_mult_occur;
2931 CLOSE csr_asg_mult_occur;
2932 IF l_mult_occur <> 'Y' THEN
2933 Hr_Utility.set_location('..l_mult_occur = '||l_mult_occur, 7);
2934 Hr_Utility.set_location('..assignment_id = '||l_asg_rec.assignment_id, 7);
2935 Hr_Utility.set_location('..p_flex_context = '||p_flex_context, 7);
2936 OPEN csr_aei (c_assignment_id => l_asg_rec.assignment_id
2937 ,c_information_type => p_flex_context);
2938 FETCH csr_aei INTO l_key_val;
2939 CLOSE csr_aei;
2940
2941 Hr_Utility.set_location('..p_flex_name = '||p_flex_name, 7);
2942 Hr_Utility.set_location('..l_key_val = '||l_key_val, 7);
2943 l_key_col := 'ASSIGNMENT_EXTRA_INFO_ID';
2944 l_tab_name := 'PER_ASSIGNMENT_EXTRA_INFO';
2945 ELSE
2946 p_error_code := '-20001';
2947 p_err_message := 'Contexts :'||p_flex_context ||
2948 ' can have multiple occurances';
2949 l_return_value := 'EXT_ERR_WARNING';
2950 RETURN l_return_value;
2951 END IF;
2952
2953 ELSIF p_flex_name IN('Org Developer DF',
2954 'HR_ORGANIZATION_INFORMATION') THEN
2955 IF g_legislation_code = 'MX' THEN
2956
2957 IF p_flex_context IN ('MX_SOC_SEC_DETAILS') THEN
2958
2959 IF g_gre_org_id IS NULL THEN
2960 PAY_MX_Rules.Get_Main_Tax_Unit_ID
2961 (p_assignment_id => p_assignment_id
2962 ,p_effective_date => p_effective_date
2963 ,p_tax_unit_id => l_organization_id
2964 );
2965 ELSE
2966 l_organization_id := g_gre_org_id;
2967 END IF;
2968
2969 OPEN csr_info_id (l_organization_id
2970 ,p_flex_context);
2971 FETCH csr_info_id INTO l_key_val;
2972 CLOSE csr_info_id;
2973
2974 l_key_col := 'ORG_INFORMATION_ID';
2975 l_tab_name := 'HR_ORGANIZATION_INFORMATION';
2976 Hr_Utility.set_location('..g_gre_org_id = '||g_gre_org_id, 7);
2977 Hr_Utility.set_location('..p_flex_context = '||p_flex_context, 7);
2978 Hr_Utility.set_location('..l_key_val = '||l_key_val, 7);
2979 END IF;
2980
2981 END IF;
2982
2983 ELSIF p_flex_name IN('Further Person Information ',
2984 'Person Developer DF') THEN
2985
2986 IF p_flex_context <> g_legislation_code THEN
2987 Hr_Utility.set_location('..Invalid p_flex_name = '||p_flex_name, 8);
2988 RAISE Invaild_DDF_or_DF;
2989 END IF;
2990 l_key_val := g_person_id;
2991 l_key_col := 'PERSON_ID';
2992 l_tab_name := 'PER_ALL_PEOPLE_F';
2993 ELSE
2994 Hr_Utility.set_location('..Invalid p_flex_name = '||p_flex_name, 8);
2995 RAISE Invaild_DDF_or_DF;
2996 END IF;
2997 --
2998 -- Now call the generic function to get the segment value for the given
2999 -- context and flex field title name
3000 --
3001 IF p_flex_name IN ('Extra Person Info DDF'
3002 ,'Org Developer DF'
3003 ,'Person Developer DF'
3004 ,'Assignment Developer DF') THEN
3005
3006 Hr_Utility.set_location('..Calling pqp_utilities.get_ddf_value', 9);
3007 Hr_Utility.set_location('..l_key_val = '||l_key_val, 7);
3008
3009 l_ddf_seg_value := Pqp_Utilities.get_ddf_value
3010 (p_flex_name => p_flex_name
3011 ,p_flex_context => p_flex_context
3012 ,p_flex_field_title => p_flex_field_title
3013 ,p_key_col => l_key_col
3014 ,p_key_val => l_key_val
3015 ,p_effective_date => NULL
3016 ,p_eff_date_req => 'N'
3017 ,p_business_group_id => NULL
3018 ,p_bus_group_id_req => 'N'
3019 ,p_error_code => p_error_code
3020 ,p_message => p_err_message
3021 );
3022 l_return_value := l_ddf_seg_value;
3023
3024 Hr_Utility.set_location('..get_ddf_value ='||l_return_value, 10);
3025
3026 ELSIF p_flex_name IN ('PER_ASSIGNMENT_EXTRA_INFO',
3027 'PER_PEOPLE_EXTRA_INFO') THEN
3028 Hr_Utility.set_location('..Calling pqp_utilities.get_df_value', 9);
3029 l_df_seg_value:= Pqp_Utilities.get_df_value
3030 (p_flex_name => p_flex_name
3031 ,p_flex_context => p_flex_context
3032 ,p_flex_field_title => p_flex_field_title
3033 ,p_key_col => l_key_col
3034 ,p_key_val => l_df_key_val
3035 ,p_tab_name => l_tab_name
3036 ,p_effective_date => NULL
3037 ,p_eff_date_req => 'N'
3038 ,p_business_group_id => NULL
3039 ,p_bus_group_id_req => 'N'
3040 ,p_error_code => p_error_code
3041 ,p_message => p_err_message
3042 );
3043 l_return_value := l_df_seg_value;
3044 Hr_Utility.set_location('..get_df_value ='||l_return_value, 10);
3045 ELSE
3046 RAISE Invaild_DDF_or_DF;
3047 END IF;
3048 l_return_value := Nvl(l_return_value,'EXT_NULL_VALUE');
3049 Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
3050 RETURN l_return_value;
3051
3052 EXCEPTION
3053 WHEN Invaild_DDF_or_DF THEN
3054 p_error_code := '-20001';
3055 p_err_message := 'Currently Supported DDF/DFs :Assignment Developer DF,'||
3056 'Extra Person Info DDF,
3057 PER_ASSIGNMENT_EXTRA_INFO,
3058 PER_ASSIGNMENT_EXTRA_INFO';
3059
3060 l_return_value := 'EXT_ERR_WARNING';
3061 IF csr_pei%ISOPEN THEN CLOSE csr_pei; END IF;
3062 IF csr_aei%ISOPEN THEN CLOSE csr_aei; END IF;
3063 Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
3064 RETURN l_return_value;
3065
3066 WHEN Others THEN
3067 l_return_value := 'EXT_ERR_WARNING';
3068 p_error_code := '-20001';
3069 p_err_message :='SQL-ERRM :'||SQLERRM;
3070 IF csr_pei%ISOPEN THEN CLOSE csr_pei; END IF;
3071 IF csr_aei%ISOPEN THEN CLOSE csr_aei; END IF;
3072 Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
3073 RETURN l_return_value;
3074
3075 END Get_DDF_DF_Value;
3076 -- =============================================================================
3077 -- Get_Participant_Status_Code:
3078 -- =============================================================================
3079 FUNCTION Get_Participant_Status_Code
3080 (p_business_group_id IN NUMBER
3081 ,p_assignment_id IN NUMBER
3082 ,p_effective_date IN DATE
3083 ,p_original_hire_date OUT NOCOPY DATE
3084 ,p_recent_hire_date OUT NOCOPY DATE
3085 ,p_actual_termination_date OUT NOCOPY DATE
3086 ,p_extract_date OUT NOCOPY DATE
3087 ,p_person_type OUT NOCOPY VARCHAR2
3088 ,p_401k_entry_value OUT NOCOPY VARCHAR2
3089 ,p_entry_eff_date OUT NOCOPY DATE
3090 ,p_error_code OUT NOCOPY VARCHAR2
3091 ,p_err_message OUT NOCOPY VARCHAR2
3092 ) RETURN NUMBER AS
3093 CURSOR csr_entry_dtls (c_effective_date IN DATE
3094 ,c_element_type_id IN NUMBER
3095 ,c_assignment_id IN NUMBER
3096 ,c_input_value_id IN NUMBER) IS
3097 SELECT pev.screen_entry_value
3098 ,pee.effective_start_date
3099 FROM pay_input_values_f piv
3100 ,pay_element_entry_values_f pev
3101 ,pay_element_entries_f pee
3102 ,pay_element_links_f pel
3103 WHERE c_effective_date BETWEEN piv.effective_start_date
3104 AND piv.effective_end_date
3105 AND c_effective_date BETWEEN pev.effective_start_date
3106 AND pev.effective_end_date
3107 AND c_effective_date BETWEEN pee.effective_start_date
3108 AND pee.effective_end_date
3109 AND c_effective_date BETWEEN pel.effective_start_date
3110 AND pel.effective_end_date
3111 AND pev.input_value_id = piv.input_value_id
3112 AND pev.element_entry_id = pee.element_entry_id
3113 AND pee.element_link_id = pel.element_link_id
3114 AND piv.element_type_id = pel.element_type_id
3115 AND pel.element_type_id = c_element_type_id
3116 AND pee.assignment_id = c_assignment_id
3117 AND piv.input_value_id = c_input_value_id;
3118
3119 l_proc_name VARCHAR2(150) := g_proc_name ||'Get_Participant_Status_Code';
3120 l_per_dates csr_per_dates%ROWTYPE;
3121 l_asg_rec csr_asg%ROWTYPE;
3122 l_entry_dtls csr_entry_dtls%ROWTYPE;
3123 l_ele_type_id pay_element_types_f.element_type_id%TYPE;
3124 l_prev_ele_type_id pay_element_types_f.element_type_id%TYPE;
3125 l_input_value_id pay_input_values_f.input_value_id%TYPE;
3126 l_return_value NUMBER(2) :=0;
3127 l_df_st_date DATE := To_Date('1900/01/01','YYYY/MM/DD');
3128 BEGIN
3129 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
3130 OPEN csr_asg (c_assignment_id => p_assignment_id
3131 ,c_effective_date => p_effective_date);
3132 FETCH csr_asg INTO l_asg_rec;
3133 IF csr_asg%NOTFOUND THEN
3134 p_error_code := '-20001';
3135 p_err_message := 'Invalid assignment_id :'||p_assignment_id||
3136 ' for effective date :'||p_effective_date;
3137 CLOSE csr_asg;
3138 l_return_value := -1;
3139 RETURN l_return_value;
3140 END IF;
3141 Hr_Utility.set_location('..Valid Assignment Id '||p_assignment_id, 6);
3142 CLOSE csr_asg;
3143 OPEN csr_per_dates (c_effective_date => p_effective_date
3144 ,c_person_id => l_asg_rec.person_id);
3145 FETCH csr_per_dates INTO l_per_dates;
3146 IF csr_per_dates%NOTFOUND THEN
3147 p_error_code := '-20001';
3148 p_err_message := 'Could not find person details based on assignment_id :'
3149 ||p_assignment_id||' for effective date :'
3150 ||p_effective_date;
3151 CLOSE csr_per_dates;
3152 l_return_value := -1;
3153 RETURN l_return_value;
3154 ELSE
3155 Hr_Utility.set_location('..Person Details found Id: '||l_asg_rec.person_id, 7);
3156 p_original_hire_date := l_per_dates.original_date_of_hire;
3157 p_recent_hire_date := l_per_dates.date_start;
3158 p_actual_termination_date := Nvl(l_per_dates.actual_termination_date,
3159 l_df_st_date);
3160 p_person_type := l_per_dates.system_person_type;
3161 p_extract_date := p_effective_date;
3162 END IF;
3163 CLOSE csr_per_dates;
3164
3165 Hr_Utility.set_location('..Getting the screen entry value', 7);
3166 l_ele_type_id := g_element.FIRST;
3167 WHILE l_ele_type_id IS NOT NULL
3168 LOOP
3169 l_input_value_id := g_element(l_ele_type_id).input_value_id;
3170
3171 OPEN csr_entry_dtls (c_effective_date => p_effective_date
3172 ,c_element_type_id => l_ele_type_id
3173 ,c_assignment_id => p_assignment_id
3174 ,c_input_value_id => l_input_value_id);
3175 FETCH csr_entry_dtls INTO l_entry_dtls;
3176 IF csr_entry_dtls%FOUND THEN
3177 CLOSE csr_entry_dtls;
3178 p_401k_entry_value := Nvl(l_entry_dtls.screen_entry_value,'0');
3179 p_entry_eff_date := Nvl(l_entry_dtls.effective_start_date,
3180 l_df_st_date);
3181 EXIT;
3182 END IF;
3183 CLOSE csr_entry_dtls;
3184 l_prev_ele_type_id := l_ele_type_id;
3185 l_ele_type_id := g_element.NEXT(l_prev_ele_type_id);
3186 END LOOP; -- While Loop
3187 Hr_Utility.set_location('Leaving: '||l_proc_name, 60);
3188 RETURN l_return_value;
3189
3190 EXCEPTION
3191 WHEN Others THEN
3192 p_err_message :='SQL-ERRM :'||SQLERRM;
3193 Hr_Utility.set_location('..'||p_err_message,85);
3194 Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
3195 RETURN l_return_value;
3196 END Get_Participant_Status_Code;
3197
3198 -- =============================================================================
3199 -- Get_Data_Elements:
3200 -- =============================================================================
3201 FUNCTION Get_Data_Elements
3202 (p_assignment_id IN NUMBER
3203 ,p_business_group_id IN NUMBER
3204 ,p_effective_date IN DATE
3205 ,p_data_ele_name IN VARCHAR2
3206 ,p_error_message OUT NOCOPY VARCHAR2
3207 ) RETURN VARCHAR2 IS
3208 -- Get the Annualization factor
3209 CURSOR csr_pay_basis (c_assignment_id IN NUMBER
3210 ,c_effective_date IN DATE
3211 ,c_business_group_id IN NUMBER) IS
3212 SELECT ppb.pay_annualization_factor
3213 FROM per_all_assignments_f paf
3214 ,per_pay_bases ppb
3215 WHERE assignment_id = c_assignment_id
3216 AND paf.pay_basis_id = ppb.pay_basis_id
3217 AND ppb.business_group_id = c_business_group_id
3218 AND paf.business_group_id = ppb.business_group_id
3219 AND p_effective_date BETWEEN effective_start_date
3220 AND effective_end_date;
3221
3222 -- Get the most recent salary change based on the eff. date passed
3223 CURSOR csr_base_sal (c_assignment_id IN NUMBER
3224 ,c_effective_date IN DATE
3225 ,c_business_group_id IN NUMBER) IS
3226
3227 SELECT ppp.proposed_salary_n
3228 FROM per_pay_proposals ppp
3229 WHERE ppp.assignment_id = c_assignment_id
3230 AND ppp.business_group_id = c_business_group_id
3231 AND ppp.change_date = (SELECT MAX(ppx.change_date)
3232 FROM per_pay_proposals ppx
3233 WHERE ppx.assignment_id = ppp.assignment_id
3234 AND ppx.business_group_id = ppp.business_group_id
3235 AND ppx.change_date <= c_effective_date
3236 AND ppx.approved = 'Y');
3237
3238 l_proc_name VARCHAR2(150) := g_proc_name ||'Get_Data_Elements';
3239 l_return_value VARCHAR2(250);
3240 l_base_salary NUMBER(15,2);
3241 l_annualization_factor per_pay_bases.pay_annualization_factor%TYPE;
3242 l_pay_basis_id per_all_assignments_f.pay_basis_id%TYPE;
3243 BEGIN
3244 hr_utility.set_location('Entering: '||l_proc_name, 5);
3245 IF g_primary_assig.EXISTS(p_assignment_id) THEN
3246 IF p_data_ele_name = 'EMPLOYMENT_CATEGORY' THEN
3247 l_return_value := g_primary_assig(p_assignment_id).employment_category;
3248 ELSIF p_data_ele_name = 'EMPLOYEMENT_STATUS' THEN
3249 l_return_value := g_primary_assig(p_assignment_id).assignment_status;
3250 ELSIF p_data_ele_name = 'TERMINATION_DATE' THEN
3251 l_return_value := g_primary_assig(p_assignment_id).termination_date;
3252 ELSIF p_data_ele_name = 'NORMAL_HOURS' THEN
3253 l_return_value := g_primary_assig(p_assignment_id).normal_hours;
3254 ELSIF p_data_ele_name = 'ANNUAL_COMPENSATION' THEN
3255 OPEN csr_pay_basis (c_assignment_id => p_assignment_id
3256 ,c_effective_date => p_effective_date
3257 ,c_business_group_id => g_business_group_id);
3258 FETCH csr_pay_basis INTO l_annualization_factor;
3259 CLOSE csr_pay_basis;
3260 OPEN csr_base_sal (c_assignment_id => p_assignment_id
3261 ,c_effective_date => p_effective_date
3262 ,c_business_group_id => g_business_group_id);
3263 FETCH csr_base_sal INTO l_base_salary;
3264 CLOSE csr_base_sal;
3265 l_return_value := ROUND(nvl(l_base_salary,0) *
3266 nvl(l_annualization_factor,0)
3267 ,2);
3268 END IF;
3269 END IF;
3270 IF g_debug THEN
3271 hr_utility.set_location(' ..p_data_ele_name : '||p_data_ele_name, 80);
3272 hr_utility.set_location(' ..l_return_value : '||l_return_value, 80);
3273 hr_utility.set_location('Leaving: '||l_proc_name, 80);
3274 END IF;
3275 RETURN l_return_value;
3276 EXCEPTION
3277 WHEN Others THEN
3278 p_error_message :='SQL-ERRM :'||SQLERRM;
3279 hr_utility.set_location('..'||p_error_message,85);
3280 hr_utility.set_location('Leaving: '||l_proc_name, 90);
3281 RETURN l_return_value;
3282 END Get_Data_Elements;
3283
3284 -- =============================================================================
3285 -- Get_Payroll_Date:
3286 -- =============================================================================
3287 FUNCTION Get_Payroll_Date
3288 (p_assignment_id IN NUMBER
3289 ,p_business_group_id IN NUMBER
3290 ,p_effective_date IN DATE
3291 ,p_error_message OUT NOCOPY VARCHAR2
3292 ) RETURN VARCHAR2 IS
3293 l_proc_name VARCHAR2(150) := g_proc_name ||'Get_Payroll_Date';
3294 l_return_value VARCHAR2(150);
3295 BEGIN
3296
3297 hr_utility.set_location('Entering: '||l_proc_name, 5);
3298 IF g_extract_params(p_business_group_id).reporting_dimension <> 'ASG_RUN' THEN
3299 l_return_value := FND_DATE.date_to_canonical
3300 (g_extract_params(p_business_group_id).extract_end_date);
3301 ELSE
3302 l_return_value := g_action_effective_date;
3303 END IF;
3304 RETURN l_return_value;
3305 hr_utility.set_location('Leaving: '||l_proc_name, 80);
3306 EXCEPTION
3307 WHEN Others THEN
3308 p_error_message :='SQL-ERRM :'||SQLERRM;
3309 IF g_debug THEN
3310 hr_utility.set_location('..'||p_error_message,85);
3311 hr_utility.set_location('Leaving: '||l_proc_name, 90);
3312 END IF;
3313 RETURN l_return_value;
3314 END Get_Payroll_Date;
3315 -- =============================================================================
3316 -- ~ Get_ConcProg_Information: Common function to get the conc.prg parameters
3317 -- =============================================================================
3318 FUNCTION Get_ConcProg_Information
3319 (p_header_type IN VARCHAR2
3320 ,p_error_message OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
3321
3322 l_proc_name VARCHAR2(150) := g_proc_name ||'.Get_ConcProg_Information';
3323 l_return_value VARCHAR2(1000);
3324 BEGIN
3325 hr_utility.set_location('Entering: '||l_proc_name, 5);
3326 IF p_header_type = 'EXTRACT_NAME' THEN
3327 l_return_value := g_conc_prog_details(0).extract_name;
3328 ELSIF p_header_type = 'REPORT_OPTION' THEN
3329 l_return_value := g_conc_prog_details(0).reporting_options;
3330 ELSIF p_header_type = 'SELECTION_CRITERIA' THEN
3331 l_return_value := g_conc_prog_details(0).selection_criteria;
3332 ELSIF p_header_type = 'ELE_SET' THEN
3333 l_return_value := g_conc_prog_details(0).elementset;
3334 ELSIF p_header_type = 'ELE_NAME' THEN
3335 l_return_value := g_conc_prog_details(0).elementname;
3336 ELSIF p_header_type = 'BGN_DT_PAID' THEN
3337 l_return_value := g_conc_prog_details(0).beginningdt;
3338 ELSIF p_header_type = 'END_DT_PAID' THEN
3339 l_return_value := g_conc_prog_details(0).endingdt;
3340 ELSIF p_header_type = 'PAYROLL_NAME' THEN
3341 hr_utility.set_location('PAYROLL_NAME: '||g_conc_prog_details(0).payrollname, 5);
3342 l_return_value := g_conc_prog_details(0).payrollname;
3343 ELSIF p_header_type = 'CON_SET' THEN
3344 l_return_value := g_conc_prog_details(0).consolset;
3345 hr_utility.set_location('CON_SET: '||l_return_value, 5);
3346 ELSIF p_header_type = 'ORG_NAME' THEN
3347 l_return_value := g_conc_prog_details(0).org_name;
3348 hr_utility.set_location('ORG_NAME: '||l_return_value, 5);
3349 ELSIF p_header_type = 'PERSON_TYPE' THEN
3350 l_return_value := g_conc_prog_details(0).person_type;
3351 hr_utility.set_location('PERSON_TYPE: '||l_return_value, 5);
3352 ELSIF p_header_type = 'LOCATION' THEN
3353 l_return_value := g_conc_prog_details(0).location;
3354 hr_utility.set_location('LOCATION: '||l_return_value, 5);
3355 END IF;
3356 hr_utility.set_location('Leaving: '||l_proc_name, 45);
3357
3358 RETURN l_return_value;
3359 EXCEPTION
3360 WHEN Others THEN
3361 p_error_message :='SQL-ERRM :'||SQLERRM;
3362 hr_utility.set_location('..Exception Others Raised at Get_ConcProg_Information'||p_error_message,40);
3363 hr_utility.set_location('Leaving: '||l_proc_name, 45);
3364 RETURN l_return_value;
3365 END Get_ConcProg_Information;
3366 -- =============================================================================
3367 -- ~ Get_Element_Entry_Value: Gets the elements entry value from run-results in
3368 -- ~ in case the reporting dimension is Assig. Run level and for other dimension
3369 -- ~ fetchs the screen entry value based on the extract end-date.
3370 -- =============================================================================
3371 FUNCTION Get_Element_Entry_Value
3372 (p_assignment_id IN NUMBER
3373 ,p_business_group_id IN NUMBER
3374 ,p_element_name IN VARCHAR2
3375 ,p_input_name IN VARCHAR2
3376 ,p_error_message OUT NOCOPY VARCHAR2
3377 ) RETURN VARCHAR2 IS
3378
3379 l_element_type_id pay_element_types_f.element_type_id%TYPE;
3380 l_input_value_id pay_input_values_f.input_value_id%TYPE;
3381 l_result_value pay_run_result_values.result_value%TYPE;
3382 l_screen_entry_value pay_element_entry_values_f.screen_entry_value%TYPE;
3383 l_effective_date DATE;
3384 l_return_value VARCHAR2(50) := '0';
3385 l_asg_action_id pay_assignment_actions.assignment_action_id%TYPE;
3386 l_error_message VARCHAR2(3000);
3387 l_legislation_code per_business_groups.legislation_code%TYPE;
3388 l_index NUMBER :=0;
3389 l_proc_name VARCHAR2(150) := g_proc_name ||'Get_Element_Entry_Value';
3390 BEGIN
3391 hr_utility.set_location('Entering: '||l_proc_name, 5);
3392 l_legislation_code := g_extract_params(p_business_group_id).legislation_code;
3393
3394 IF g_extract_params(p_business_group_id).reporting_dimension = 'ASG_RUN' THEN
3395 l_effective_date := g_action_effective_date;
3396 l_asg_action_id := g_asg_action_id;
3397 ELSE
3398 l_effective_date := g_extract_params(p_business_group_id).extract_end_date;
3399 END IF;
3400 -- Check this Element Name is already exist in record
3401 -- if it is then get the element type id
3402 FOR num IN 1..g_element_input_dets.COUNT LOOP
3403 IF g_element_input_dets(num).element_name = p_element_name AND
3404 g_element_input_dets(num).input_name = p_input_name THEN
3405 l_element_type_id := g_element_input_dets(num).element_type_id;
3406 l_input_value_id := g_element_input_dets(num).input_value_id;
3407 EXIT;
3408 END IF;
3409 END LOOP;
3410 IF l_element_type_id IS NULL THEN
3411 --Get the ele type id and Input Value Id
3412 OPEN csr_ele_ipv (c_element_name => p_element_name
3413 ,c_input_name => p_input_name
3414 ,c_effective_date => l_effective_date
3415 ,c_business_group_id => p_business_group_id
3416 ,c_legislation_code => l_legislation_code);
3417 FETCH csr_ele_ipv INTO l_element_type_id,l_input_value_id;
3418 IF csr_ele_ipv%NOTFOUND THEN
3419 CLOSE csr_ele_ipv;
3420 RETURN l_return_value;
3421 END IF;
3422 CLOSE csr_ele_ipv;
3423 --Put the element Type id and the input value id into record
3424 --Increment the index count by one for next record insert
3425 l_index := g_element_input_dets.COUNT+1;
3426 g_element_input_dets(l_index).element_name := p_element_name;
3427 g_element_input_dets(l_index).element_type_id := l_element_type_id;
3428 g_element_input_dets(l_index).input_value_id := l_input_value_id;
3429 g_element_input_dets(l_index).input_name := p_input_name;
3430 END IF;
3431
3432 IF g_extract_params(p_business_group_id).reporting_dimension = 'ASG_RUN' THEN
3433 -- To get the run results
3434 OPEN csr_run (c_asg_action_id => l_asg_action_id
3435 ,c_element_type_id => l_element_type_id
3436 ,c_input_value_id => l_input_value_id);
3437 FETCH csr_run INTO l_result_value;
3438 CLOSE csr_run;
3439 l_return_value := l_result_value;
3440 ELSE --If it is YTD, get the Screen entry values
3441 OPEN csr_entry (c_effective_date => l_effective_date
3442 ,c_element_type_id => l_element_type_id
3443 ,c_assignment_id => p_assignment_id
3444 ,c_input_value_id => l_input_value_id);
3445 FETCH csr_entry INTO l_screen_entry_value;
3446 CLOSE csr_entry;
3447 l_return_value := l_screen_entry_value;
3448 END IF;
3449 hr_utility.set_location('Leaving: '||l_proc_name, 10);
3450 RETURN l_return_value;
3451 EXCEPTION
3452 WHEN Others THEN
3453 l_error_message := ' Error:'||SQLERRM;
3454 p_error_message := l_error_message;
3455 IF g_debug THEN
3456 hr_utility.set_location('..'||p_error_message,10);
3457 hr_utility.set_location('Leaving: '||l_proc_name, 11);
3458 END IF;
3459 RETURN l_return_value;
3460
3461 END Get_Element_Entry_Value;
3462 -- =============================================================================
3463 -- ~ Get_Balance_Value: Gets the balance value for a given balance name for that
3464 -- ~ Assign.Id.
3465 -- =============================================================================
3466 FUNCTION Get_Balance_Value
3467 (p_assignment_id IN NUMBER
3468 ,p_business_group_id IN NUMBER
3469 ,p_balance_name IN VARCHAR2
3470 ,p_error_message OUT NOCOPY VARCHAR2
3471 ) RETURN NUMBER IS
3472
3473 l_defined_balance_id pay_defined_balances.defined_balance_id%TYPE;
3474 l_balance_amount NUMBER :=0;
3475 l_bal_total_amt NUMBER :=0;
3476 l_dimension_name VARCHAR2(100);
3477 i per_all_assignments_f.business_group_id%TYPE;
3478 l_legislation_code per_business_groups.legislation_code%TYPE;
3479 l_balance_type_id pay_balance_types.balance_type_id%TYPE;
3480 l_index NUMBER;
3481 l_valid_person_chk VARCHAR2(2);
3482 l_organization_id NUMBER(15);
3483 l_gre_org_id NUMBER(15);
3484 BEGIN
3485 i := p_business_group_id;
3486 hr_utility.set_location('Entering: Get_Balance_Value ', 5);
3487 -- Check this balance Name is already exist in record
3488 -- if it is then get the balance type id
3489 FOR num IN 1..g_balance_detls.count LOOP
3490 IF g_balance_detls(num).balance_name = p_balance_name THEN
3491 l_balance_type_id := g_balance_detls(num).balance_type_id;
3492 l_defined_balance_id := g_balance_detls(num).defined_balance_id;
3493 EXIT;
3494 END IF;
3495 END LOOP;
3496 -- Get the balance type id for given balance name ,if it is not exist in record
3497 IF l_balance_type_id IS NULL THEN
3498 OPEN csr_bal_typid (c_balance_name => p_balance_name
3499 ,c_business_group_id => p_business_group_id
3500 ,c_legislation_code => g_legislation_code);
3501 FETCH csr_bal_typid INTO l_balance_type_id;
3502 CLOSE csr_bal_typid;
3503 -- Get the def. balance id for a given balance type id
3504 IF l_balance_type_id IS NOT NULL THEN
3505 OPEN csr_asg_balid
3506 (c_balance_type_id => l_balance_type_id
3507 ,c_balance_dimension_id => g_asgrun_dim_id
3508 ,c_business_group_id => p_business_group_id);
3509 FETCH csr_asg_balid INTO l_defined_balance_id;
3510 CLOSE csr_asg_balid;
3511 END IF;
3512 l_index := g_balance_detls.COUNT + 1;
3513 g_balance_detls(l_index).balance_name := p_balance_name;
3514 g_balance_detls(l_index).balance_type_id := l_balance_type_id;
3515 g_balance_detls(l_index).defined_balance_id := l_defined_balance_id;
3516 END IF;
3517
3518 IF g_debug THEN
3519 hr_utility.set_location(' p_balance_name: '||p_balance_name, 5);
3520 hr_utility.set_location(' l_balance_type_id: '||l_balance_type_id, 5);
3521 hr_utility.set_location(' g_asgrun_dim_id: '||g_asgrun_dim_id, 5);
3522 hr_utility.set_location(' l_defined_balance_id: '||l_defined_balance_id, 5);
3523
3524 END IF;
3525 --
3526 -- Mexico Specific Changes
3527 --
3528 IF g_legislation_code = 'MX' AND
3529 g_extract_params(i).reporting_dimension = 'ASG_RUN' THEN
3530 -- Set the Balance Context
3531 pay_balance_pkg.set_context('tax_unit_id', g_gre_org_id);
3532 pay_balance_pkg.set_context('date_earned', g_action_effective_date);
3533 IF g_debug THEN
3534 hr_utility.set_location(' tax_unit_id: '||g_gre_org_id, 5);
3535 hr_utility.set_location(' date_earned: '||g_action_effective_date, 5);
3536 END IF;
3537 END IF;
3538 --
3539 --If Reporting dimension is ASG_RUN
3540 --
3541 IF g_extract_params(i).reporting_dimension = 'ASG_RUN' THEN
3542 --Get the balance amount
3543 IF l_defined_balance_id IS NOT NULL THEN
3544 l_balance_amount := pay_balance_pkg.get_value
3545 (p_defined_balance_id => l_defined_balance_id,
3546 p_assignment_action_id => g_asg_action_id );
3547 l_bal_total_amt := l_bal_total_amt + NVL(l_balance_amount,0);
3548 END IF;
3549 ELSE
3550 IF l_defined_balance_id IS NOT NULL THEN
3551 --Get the Assignment action ids for assignment Id
3552 IF g_legislation_code = 'MX' THEN
3553 l_gre_org_id := nvl(g_gre_org_id,
3554 g_extract_params(i).gre_org_id);
3555 ELSE
3556 l_gre_org_id := NULL;
3557 END IF;
3558 FOR asgact_rec IN
3559 csr_bal_act
3560 (c_assignment_id => p_assignment_id
3561 ,c_payroll_id => g_extract_params(i).payroll_id
3562 ,c_con_set_id => g_extract_params(i).con_set_id
3563 ,c_gre_id => l_gre_org_id
3564 ,c_start_date => g_extract_params(i).extract_start_date
3565 ,c_end_date => g_extract_params(i).extract_end_date
3566 )
3567 LOOP
3568 IF g_legislation_code = 'MX' THEN
3569 -- Set the Balance Context
3570 pay_balance_pkg.set_context('tax_unit_id', asgact_rec.tax_unit_id);
3571 pay_balance_pkg.set_context('date_earned', asgact_rec.effective_date);
3572 IF g_debug THEN
3573 hr_utility.set_location(' tax_unit_id: '||asgact_rec.tax_unit_id, 5);
3574 hr_utility.set_location(' date_earned: '||asgact_rec.effective_date, 5);
3575 END IF;
3576 END IF;
3577 IF g_legislation_code = 'MX' AND
3578 g_gre_org_id IS NOT NULL AND
3579 asgact_rec.tax_unit_id = g_gre_org_id THEN
3580 l_balance_amount :=
3581 pay_balance_pkg.get_value
3582 (p_defined_balance_id => l_defined_balance_id,
3583 p_assignment_action_id => asgact_rec.assignment_action_id );
3584
3585 ELSIF g_legislation_code <> 'MX' THEN
3586
3587 l_balance_amount :=
3588 pay_balance_pkg.get_value
3589 (p_defined_balance_id => l_defined_balance_id,
3590 p_assignment_action_id => asgact_rec.assignment_action_id );
3591 END IF;
3592
3593 l_bal_total_amt := l_bal_total_amt + NVL(l_balance_amount,0);
3594
3595 END LOOP; -- For Loop
3596 END IF; -- If l_defined_balance_id
3597 END IF; --final end if
3598
3599 hr_utility.set_location(' l_bal_total_amt: '||l_bal_total_amt, 50);
3600 hr_utility.set_location('Leaving: Get_Balance_Value ', 50);
3601 RETURN l_bal_total_amt;
3602
3603 EXCEPTION
3604
3605 WHEN Others THEN
3606 p_error_message :='SQL-ERRM :'||SQLERRM;
3607 IF g_debug THEN
3608 hr_utility.set_location('..'||p_error_message,85);
3609 hr_utility.set_location('Leaving Get_Balance_Value function:', 90);
3610 END IF;
3611
3612 RETURN l_bal_total_amt;
3613
3614 END Get_Balance_Value;
3615
3616 -- =============================================================================
3617 -- ~ Set_ConcProg_Parameter_Values : Used to get the conc program parameters
3618 -- ~ values for passed ids and also setting the values into the global records
3619 -- =============================================================================
3620 PROCEDURE Set_ConcProg_Parameter_Values
3621 (p_ext_dfn_id IN NUMBER
3622 ,p_reporting_dimension IN VARCHAR2
3623 ,p_selection_criteria IN VARCHAR2
3624 ,p_element_set_id IN NUMBER
3625 ,p_element_type_id IN NUMBER
3626 ,p_start_date IN VARCHAR2
3627 ,p_end_date IN VARCHAR2
3628 ,p_gre_id IN NUMBER
3629 ,p_payroll_id IN NUMBER
3630 ,p_con_set IN NUMBER
3631 ,p_org_id IN NUMBER
3632 ,p_person_type_id IN NUMBER
3633 ,p_location_id IN NUMBER
3634 ) IS
3635
3636
3637 CURSOR csr_ext_name(c_ext_dfn_id IN NUMBER
3638 )IS
3639 SELECT SUBSTR(ed.NAME,1,240)
3640 FROM ben_ext_dfn ed
3641 WHERE ed.ext_dfn_id = p_ext_dfn_id;
3642
3643 CURSOR csr_ele_set_name(c_element_set_id IN NUMBER
3644 )IS
3645 SELECT element_set_name
3646 FROM pay_element_sets
3647 WHERE element_set_id = c_element_set_id
3648 AND element_set_type = 'C';
3649
3650 CURSOR csr_ele_name( c_element_type_id IN NUMBER
3651 ,c_end_date IN DATE
3652 )IS
3653 SELECT element_name
3654 FROM pay_element_types_f
3655 WHERE element_type_id = c_element_type_id
3656 AND c_end_date BETWEEN effective_start_date
3657 AND effective_end_date;
3658
3659 CURSOR csr_gre_name(c_gre_id IN NUMBER
3660 )IS
3661 SELECT hou.NAME
3662 FROM hr_organization_units hou
3663 WHERE hou.organization_id = c_gre_id;
3664
3665 CURSOR csr_pay_name(c_payroll_id IN NUMBER
3666 ,c_end_date IN DATE
3667 )IS
3668 SELECT pay.payroll_name
3669 FROM pay_payrolls_f pay
3670 WHERE pay.payroll_id = c_payroll_id
3671 AND c_end_date BETWEEN pay.effective_start_date
3672 AND pay.effective_end_date;
3673
3674 CURSOR csr_con_set (c_con_set IN NUMBER
3675 )IS
3676 SELECT con.consolidation_set_name
3677 FROM pay_consolidation_sets con
3678 WHERE con.consolidation_set_id = c_con_set;
3679
3680 CURSOR csr_org_name (c_org_id IN NUMBER
3681 )IS
3682 SELECT NAME
3683 FROM hr_all_organization_units
3684 WHERE organization_id =c_org_id;
3685
3686 CURSOR csr_person_type (c_person_type_id IN NUMBER
3687 )IS
3688 SELECT user_person_type
3689 FROM per_person_types
3690 WHERE person_type_id = c_person_type_id;
3691
3692 CURSOR csr_location_code (c_location_id IN NUMBER
3693 )IS
3694 SELECT location_code
3695 FROM hr_locations_all
3696 WHERE location_id = c_location_id;
3697
3698 l_proc_name VARCHAR2(150) := g_proc_name ||'Set_ConcProg_Parameter_Values';
3699 l_extract_name ben_ext_dfn.NAME%TYPE;
3700 l_element_set pay_element_sets.element_set_name%TYPE;
3701 l_element_name pay_element_types_f.element_name%TYPE;
3702 l_gre_name hr_organization_units.NAME%TYPE ;
3703 l_payroll_name pay_payrolls_f.payroll_name%TYPE ;
3704 l_con_set_name pay_consolidation_sets.consolidation_set_name%TYPE;
3705 l_org_name hr_all_organization_units.NAME%TYPE;
3706 l_person_type per_person_types.user_person_type%TYPE;
3707 l_location hr_locations_all.location_code%TYPE;
3708 BEGIN
3709 hr_utility.set_location('Entering: '||l_proc_name, 5);
3710 OPEN csr_ext_name( c_ext_dfn_id => p_ext_dfn_id);
3711 FETCH csr_ext_name INTO l_extract_name;
3712 CLOSE csr_ext_name;
3713 IF p_element_set_id IS NOT NULL THEN
3714 OPEN csr_ele_set_name( c_element_set_id => p_element_set_id);
3715 FETCH csr_ele_set_name INTO l_element_set;
3716 CLOSE csr_ele_set_name;
3717 END IF;
3718 IF p_element_type_id IS NOT NULL THEN
3719 OPEN csr_ele_name( c_element_type_id => p_element_type_id
3720 ,c_end_date => p_end_date);
3721 FETCH csr_ele_name INTO l_element_name;
3722 CLOSE csr_ele_name;
3723 END IF;
3724 IF p_payroll_id IS NOT NULL THEN
3725 OPEN csr_pay_name( c_payroll_id => p_payroll_id
3726 ,c_end_date => p_end_date);
3727 FETCH csr_pay_name INTO l_payroll_name;
3728 CLOSE csr_pay_name;
3729 END IF;
3730 IF p_con_set IS NOT NULL THEN
3731 OPEN csr_con_set( c_con_set => p_con_set);
3732 FETCH csr_con_set INTO l_con_set_name;
3733 CLOSE csr_con_set;
3734 END IF;
3735 IF p_org_id IS NOT NULL THEN
3736 hr_utility.set_location('p_org_id: '||p_org_id, 5);
3737 OPEN csr_org_name( c_org_id => p_org_id);
3738 FETCH csr_org_name INTO l_org_name;
3739 CLOSE csr_org_name;
3740 END IF;
3741 IF p_person_type_id IS NOT NULL THEN
3742 hr_utility.set_location('p_person_type_id: '||p_person_type_id, 5);
3743 OPEN csr_person_type( c_person_type_id => p_person_type_id);
3744 FETCH csr_person_type INTO l_person_type;
3745 CLOSE csr_person_type;
3746 END IF;
3747 IF p_location_id IS NOT NULL THEN
3748 hr_utility.set_location('p_location_id: '||p_location_id, 5);
3749 OPEN csr_location_code( c_location_id => p_location_id);
3750 FETCH csr_location_code INTO l_location;
3751 CLOSE csr_location_code;
3752 END IF;
3753 IF g_debug THEN
3754 hr_utility.set_location('l_gre_name : '||l_gre_name, 5);
3755 hr_utility.set_location('l_extract_name : '||l_extract_name, 5);
3756 hr_utility.set_location('l_element_name : '||l_element_name, 5);
3757 hr_utility.set_location('p_reporting_dimension : '||p_reporting_dimension, 5);
3758 hr_utility.set_location('p_selection_criteria : '||p_selection_criteria, 5);
3759 hr_utility.set_location('l_payroll_name: '||l_payroll_name, 5);
3760 hr_utility.set_location('l_con_set_name: '||l_con_set_name, 5);
3761 hr_utility.set_location('l_org_name : '||l_org_name, 5);
3762 hr_utility.set_location('l_person_type : '||l_person_type, 5);
3763 hr_utility.set_location('l_location : '||l_location, 5);
3764 END IF;
3765
3766 g_conc_prog_details(0).extract_name := l_extract_name;
3767 g_conc_prog_details(0).reporting_options := HR_GENERAL.DECODE_LOOKUP
3768 ('PQP_EXT_RPT_DIMENSION',
3769 p_reporting_dimension);
3770 g_conc_prog_details(0).selection_criteria := HR_GENERAL.DECODE_LOOKUP
3771 ('REPORT_SELECT_SORT_CODE',
3772 p_selection_criteria);
3773 g_conc_prog_details(0).elementset := l_element_set;
3774 g_conc_prog_details(0).elementname := l_element_name;
3775 g_conc_prog_details(0).beginningdt := p_start_date;
3776 g_conc_prog_details(0).endingdt := p_end_date;
3777 g_conc_prog_details(0).grename := l_gre_name;
3778 g_conc_prog_details(0).payrollname := l_payroll_name;
3779 g_conc_prog_details(0).consolset := l_con_set_name;
3780 g_conc_prog_details(0).org_name := l_org_name;
3781 g_conc_prog_details(0).person_type := l_person_type;
3782 g_conc_prog_details(0).location := l_location;
3783
3784 hr_utility.set_location('Leaving: '||l_proc_name, 80);
3785 EXCEPTION
3786 WHEN Others THEN
3787 hr_utility.set_location('ERROR: '||l_proc_name, 90);
3788 END Set_ConcProg_Parameter_Values;
3789 --=============================================================================
3790 -- Chk_Person_Asg:This is used to check the person id is valid for passed orgId,
3791 -- locationId and person type id.
3792 -- =============================================================================
3793 FUNCTION Chk_Person_Asg
3794 (p_assignment_id IN NUMBER
3795 ,p_person_id IN NUMBER
3796 ,p_bus_grp_id IN NUMBER
3797 ,p_gre_org_id IN NUMBER
3798 ,p_org_id IN NUMBER
3799 ,p_person_type_id IN NUMBER
3800 ,p_location_id IN NUMBER
3801 ,p_effective_date IN DATE
3802 ) RETURN VARCHAR2 IS
3803
3804 CURSOR csr_per (c_person_id IN NUMBER
3805 ,c_effective_date IN DATE
3806 ,c_bus_grp_id IN NUMBER) IS
3807 SELECT paf.person_type_id
3808 ,paa.location_id
3809 ,paa.organization_id
3810
3811 FROM per_all_people_f paf
3812 ,per_all_assignments_f paa
3813 WHERE paf.person_id = c_person_id
3814 AND paa.person_id = paf.person_id
3815 AND paa.assignment_type = 'E'
3816 AND paa.business_group_id = paf.business_group_id
3817 AND c_effective_date BETWEEN paf.effective_start_date
3818 AND paf.effective_end_date
3819 AND c_effective_date BETWEEN paa.effective_start_date
3820 AND paa.effective_end_date;
3821
3822 l_per_rec csr_per%ROWTYPE;
3823 l_tax_unit_id NUMBER(15);
3824 l_return_value VARCHAR2(2);
3825 l_proc_name VARCHAR2(150) := g_proc_name ||'Chk_Person_Asg';
3826
3827 BEGIN
3828 l_return_value := 'Y';
3829 hr_utility.set_location('Entering: '||l_proc_name, 5);
3830 IF p_person_type_id IS NULL AND
3831 p_location_id IS NULL AND
3832 p_org_id IS NULL AND
3833 p_gre_org_id IS NULL THEN
3834 hr_utility.set_location('Leaving: '||l_proc_name, 76);
3835 RETURN l_return_value;
3836 END IF;
3837 OPEN csr_per (c_person_id => p_person_id
3838 ,c_effective_date => p_effective_date
3839 ,c_bus_grp_id => p_bus_grp_id);
3840 FETCH csr_per INTO l_per_rec;
3841 CLOSE csr_per;
3842 /*
3843 IF g_legislation_code = 'MX' AND
3844 p_gre_org_id IS NOT NULL THEN
3845
3846 PAY_MX_Rules.Get_Main_Tax_Unit_ID
3847 (p_assignment_id => p_assignment_id
3848 ,p_effective_date => p_effective_date
3849 ,p_tax_unit_id => l_tax_unit_id
3850 );
3851 IF p_gre_org_id <> l_tax_unit_id THEN
3852 l_return_value := 'N';
3853 hr_utility.set_location('Leaving: '||l_proc_name, 77);
3854 RETURN l_return_value;
3855 END IF;
3856
3857 END IF;
3858 */
3859 -- Checking the person type id
3860 IF (p_person_type_id IS NOT NULL AND
3861 l_per_rec.person_type_id <> p_person_type_id) THEN
3862 l_return_value := 'N';
3863 hr_utility.set_location('Leaving: '||l_proc_name, 78);
3864 RETURN l_return_value;
3865 END IF;
3866
3867 IF (p_location_id IS NOT NULL AND
3868 l_per_rec.location_id <> p_location_id)
3869 OR
3870 (p_org_id IS NOT NULL AND
3871 l_per_rec.organization_id <> p_org_id )
3872 OR
3873 (p_location_id IS NOT NULL AND
3874 l_per_rec.organization_id <> p_org_id )
3875 OR
3876 (p_org_id IS NOT NULL AND
3877 l_per_rec.location_id <> p_location_id ) THEN
3878 l_return_value := 'N';
3879 hr_utility.set_location('Leaving: '||l_proc_name, 79);
3880 RETURN l_return_value;
3881 END IF;
3882 hr_utility.set_location('Leaving: '||l_proc_name, 80);
3883 RETURN l_return_value;
3884
3885 EXCEPTION
3886 WHEN Others THEN
3887 hr_utility.set_location('Error: '||l_proc_name, 90);
3888 l_return_value:='N';
3889 RETURN l_return_value;
3890
3891 END Chk_Person_Asg;
3892 -- =============================================================================
3893 -- Get_Pay_value:
3894 -- =============================================================================
3895 FUNCTION Get_Pay_value
3896 (p_assignment_id IN NUMBER
3897 ,p_business_group_id IN NUMBER
3898 ,p_effective_date IN DATE
3899 ,p_error_message OUT NOCOPY VARCHAR2
3900 ) RETURN NUMBER IS
3901 l_bal_total_amt NUMBER := 0;
3902 l_balance_amount NUMBER := 0;
3903 l_ele_type_id pay_element_types_f.element_type_id%TYPE;
3904 l_prev_ele_type_id pay_element_types_f.element_type_id%TYPE;
3905 l_legislation_code per_business_groups.legislation_code%TYPE;
3906 i per_all_assignments_f.business_group_id%TYPE;
3907 l_input_value_id pay_input_values_f.input_value_id%TYPE;
3908 l_valid_action VARCHAR2(2);
3909 l_proc_name VARCHAR2(150) := g_proc_name ||'Get_Pay_value';
3910 l_valid_person_chk VARCHAR2(2);
3911 BEGIN
3912 hr_utility.set_location('Entering: '||l_proc_name, 5);
3913 -- Check if this assignment was process in the criteria func. else return
3914 i := p_business_group_id;
3915
3916 IF g_primary_assig.EXISTS(p_assignment_id) THEN
3917 IF g_primary_assig(p_assignment_id).Calculate_Amount <> 'YES' THEN
3918 RETURN l_bal_total_amt;
3919 END IF;
3920 ELSE
3921 RETURN l_bal_total_amt;
3922 END IF;
3923 l_legislation_code := g_extract_params(i).legislation_code;
3924 -- If its for a single Assig. action, then take global variable
3925 IF g_extract_params(i).reporting_dimension = 'ASG_RUN' THEN
3926 l_ele_type_id := g_element.FIRST;
3927 WHILE l_ele_type_id IS NOT NULL
3928 LOOP
3929 --Check the element is processed for asg action
3930 OPEN csr_ele_run (c_asg_action_id => g_asg_action_id
3931 ,c_element_type_id => l_ele_type_id);
3932 FETCH csr_ele_run INTO l_valid_action;
3933 IF csr_ele_run%FOUND THEN
3934 -- If processed then get the input value id
3935 l_input_value_id:= g_element(l_ele_type_id).input_value_id;
3936 -- To get the run results
3937 OPEN csr_run (c_asg_action_id => g_asg_action_id
3938 ,c_element_type_id => l_ele_type_id
3939 ,c_input_value_id => l_input_value_id);
3940 FETCH csr_run INTO l_balance_amount;
3941 CLOSE csr_run;
3942 l_bal_total_amt := l_bal_total_amt + NVL(l_balance_amount,0);
3943 END IF;
3944 CLOSE csr_ele_run;
3945 l_prev_ele_type_id := l_ele_type_id;
3946 l_ele_type_id := g_element.NEXT(l_prev_ele_type_id);
3947 END LOOP; -- While Loop
3948 ELSE
3949 -- We are reporting a single row for each person
3950 FOR act_rec IN csr_asg_act
3951 (c_assignment_id => p_assignment_id
3952 ,c_payroll_id => g_extract_params(i).payroll_id
3953 ,c_con_set_id => g_extract_params(i).con_set_id
3954 ,c_start_date => g_extract_params(i).extract_start_date
3955 ,c_end_date => g_extract_params(i).extract_end_date
3956 )
3957 LOOP
3958 l_ele_type_id := g_element.FIRST;
3959 WHILE l_ele_type_id IS NOT NULL
3960 LOOP
3961 OPEN csr_ele_run (c_asg_action_id => act_rec.assignment_action_id
3962 ,c_element_type_id => l_ele_type_id);
3963 FETCH csr_ele_run INTO l_valid_action;
3964 IF csr_ele_run%FOUND THEN
3965 -- If processed then get the input value id
3966 l_input_value_id:= g_element(l_ele_type_id).input_value_id;
3967 -- get the un results for the Pay Value input
3968 OPEN csr_run (c_asg_action_id => act_rec.assignment_action_id
3969 ,c_element_type_id => l_ele_type_id
3970 ,c_input_value_id => l_input_value_id);
3971 FETCH csr_run INTO l_balance_amount;
3972 CLOSE csr_run;
3973 l_bal_total_amt := l_bal_total_amt + NVL(l_balance_amount,0);
3974 END IF; -- If csr_ele_run%FOUND
3975 CLOSE csr_ele_run;
3976 l_prev_ele_type_id := l_ele_type_id;
3977 l_ele_type_id := g_element.NEXT(l_prev_ele_type_id);
3978 END LOOP; -- While Loop
3979 END LOOP; -- For Loop
3980 END IF;
3981 hr_utility.set_location('Leaving: '||l_proc_name, 80);
3982 RETURN l_bal_total_amt;
3983 EXCEPTION
3984 WHEN Others THEN
3985 p_error_message :='SQL-ERRM :'||SQLERRM;
3986 IF g_debug THEN
3987 hr_utility.set_location('..'||p_error_message,85);
3988 hr_utility.set_location('Leaving: '||l_proc_name, 90);
3989 END IF;
3990 RETURN l_bal_total_amt;
3991 END Get_Pay_value;
3992 -- =============================================================================
3993 -- Pension_Criteria_Full_Profile: The Main extract criteria that would be used
3994 -- for the pension extract.
3995 -- =============================================================================
3996 FUNCTION Pension_Criteria_Full_Profile
3997 (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
3998 ,p_effective_date IN DATE
3999 ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
4000 ,p_warning_message OUT NOCOPY VARCHAR2
4001 ,p_error_message OUT NOCOPY VARCHAR2
4002 ) RETURN VARCHAR2 IS
4003 -- =========================================
4004 -- ~ Cursor variables
4005 -- =========================================
4006 CURSOR csr_ext_attr (c_ext_dfn_id IN ben_ext_rslt.ext_dfn_id%TYPE) IS
4007 SELECT ext_dfn_type
4008 FROM pqp_extract_attributes
4009 WHERE ext_dfn_id = c_ext_dfn_id;
4010 -- Get the Conc. requests params based on the request id fetched
4011 CURSOR csr_ext_params (c_request_id IN NUMBER
4012 ,c_ext_dfn_id IN NUMBER
4013 ,c_business_group_id IN NUMBER) IS
4014 SELECT session_id -- session id
4015 ,business_group_id -- business group id
4016 ,tax_unit_id -- concurrent request id
4017 ,value1 -- extract def. id
4018 ,value2 -- element set id
4019 ,value3 -- element type id
4020 ,value4 -- Payroll Id
4021 ,value5 -- GRE Org Id
4022 ,value6 -- Consolidation set id
4023 ,attribute1 -- Selection Criteria
4024 ,attribute2 -- Reporting dimension
4025 ,attribute3 -- Extract Start Date
4026 ,attribute4 -- Extract End Date
4027 ,attribute5 -- Organization Name
4028 ,attribute6 -- Person Type
4029 ,attribute7 -- Location
4030
4031 FROM pay_us_rpt_totals
4032 WHERE tax_unit_id = c_request_id
4033 AND value1 = c_ext_dfn_id
4034 AND business_group_id = c_business_group_id
4035 AND organization_id = c_business_group_id
4036 AND location_id = c_ext_dfn_id;
4037
4038 -- Get the Assignment Run level dimension id
4039 CURSOR csr_asg_dimId(c_legislation_code IN VARCHAR2
4040 ,c_dimension_name IN VARCHAR2 ) IS
4041 SELECT balance_dimension_id
4042 FROM pay_balance_dimensions
4043 WHERE legislation_code = c_legislation_code
4044 AND dimension_name = c_dimension_name;
4045
4046 -- Get the Legislation Code and Curreny Code
4047 CURSOR csr_leg_code (c_business_group_id IN NUMBER) IS
4048 SELECT pbg.legislation_code
4049 ,pbg.currency_code
4050 FROM per_business_groups_perf pbg
4051 WHERE pbg.business_group_id = c_business_group_id;
4052
4053 -- Check the eligibility to the person enrolled for pension for passed values.
4054 CURSOR csr_chk_asg
4055 (c_start_date IN DATE
4056 ,c_end_date IN DATE
4057 ,c_assignment_id IN NUMBER
4058 ,c_business_group_id IN NUMBER
4059 ,c_payroll_id IN NUMBER
4060 ,c_org_id IN NUMBER) IS
4061
4062 SELECT 'X'
4063
4064 FROM per_all_assignments_f paf
4065
4066 WHERE paf.assignment_id = c_assignment_id
4067 AND paf.business_group_id = c_business_group_id
4068 AND (c_org_id IS NULL OR
4069 paf.organization_id = c_org_id)
4070 AND (c_payroll_id IS NULL OR
4071 paf.payroll_id = c_payroll_id)
4072 AND (c_end_date BETWEEN paf.effective_start_date
4073 AND paf.effective_end_date
4074 OR
4075 paf.effective_end_date BETWEEN c_start_date
4076 AND c_end_date);
4077 -- Check if element exists in assignment element entry
4078 CURSOR csr_chk_ele
4079 (c_start_date IN DATE
4080 ,c_end_date IN DATE
4081 ,c_assignment_id IN NUMBER
4082 ,c_ele_type_id IN NUMBER
4083 ,c_payroll_id IN NUMBER
4084 ,c_business_group_id IN NUMBER
4085 ,c_org_id IN NUMBER) IS
4086
4087 SELECT 'X'
4088 FROM pay_element_entries_f pee
4089 ,pay_element_links_f pel
4090
4091 WHERE (c_end_date BETWEEN pee.effective_start_date
4092 AND pee.effective_end_date
4093 OR
4094 pee.effective_end_date BETWEEN c_start_date
4095 AND c_end_date
4096 )
4097 AND pee.effective_end_date BETWEEN pel.effective_start_date
4098 AND pel.effective_end_date
4099 AND pee.element_link_id = pel.element_link_id
4100 AND pel.element_type_id = c_ele_type_id
4101 AND pee.assignment_id = c_assignment_id
4102 AND pel.business_group_id = c_business_group_id;
4103
4104 -- Cursor to Check if the payroll id if passed was valid for the assignment
4105 CURSOR csr_val_assg(c_assignment_id IN NUMBER
4106 ,c_payroll_id IN NUMBER
4107 ,c_start_date IN DATE
4108 ,c_end_date IN DATE
4109 ) IS
4110 SELECT 'x'
4111 FROM per_all_assignments_f paf
4112 WHERE paf.assignment_id = c_assignment_id
4113 AND (c_payroll_id IS NULL OR
4114 paf.payroll_id = NVL(c_payroll_id,paf.payroll_id)
4115 )
4116 AND (c_end_date BETWEEN paf.effective_start_date
4117 AND paf.effective_end_date
4118 OR
4119 paf.effective_end_date BETWEEN c_start_date
4120 AND c_end_date
4121 );
4122
4123 l_ben_params csr_ben%ROWTYPE;
4124
4125 -- =========================================
4126 -- ~ Local variables
4127 -- =========================================
4128 l_ext_params csr_ext_params%ROWTYPE;
4129 l_conc_reqest_id ben_ext_rslt.request_id%TYPE;
4130 l_ext_dfn_type pqp_extract_attributes.ext_dfn_type%TYPE;
4131 i per_all_assignments_f.business_group_id%TYPE;
4132 l_ext_rslt_id ben_ext_rslt.ext_rslt_id%TYPE;
4133 l_ext_dfn_id ben_ext_dfn.ext_dfn_id%TYPE;
4134 l_return_value VARCHAR2(2) :='N';
4135 l_valid_action VARCHAR2(2);
4136 l_ele_type_id pay_element_types_f.element_type_id%TYPE;
4137 l_prev_ele_type_id pay_element_types_f.element_type_id%TYPE;
4138 l_proc_name VARCHAR2(150) := g_proc_name ||'Pension_Criteria_Full_Profile';
4139 l_assig_rec csr_assig%ROWTYPE;
4140 l_valid_person_chk VARCHAR2(2);
4141 l_Chg_Evt_Exists VARCHAR2(2);
4142 l_dimension_name pay_balance_dimensions.dimension_name%TYPE;
4143 BEGIN
4144
4145 hr_utility.set_location('Entering: '||l_proc_name, 5);
4146 i := p_business_group_id;
4147 l_ext_rslt_id := ben_ext_thread.g_ext_rslt_id;
4148 l_ext_dfn_id := ben_ext_thread.g_ext_dfn_id;
4149
4150 IF NOT g_extract_params.EXISTS(i) THEN
4151 hr_utility.set_location('..Exract Params. PL/SQL not populated ', 6);
4152 -- Get the extract type, Changes extract or Full Profile
4153 OPEN csr_ext_attr(c_ext_dfn_id=> l_ext_dfn_id);
4154 FETCH csr_ext_attr INTO l_ext_dfn_type;
4155 CLOSE csr_ext_attr;
4156 hr_utility.set_location('..After cursor csr_ext_attr',7);
4157 -- Get the Conc. request id to get the params
4158 OPEN csr_req_id(c_ext_rslt_id => l_ext_rslt_id
4159 ,c_ext_dfn_id => l_ext_dfn_id
4160 ,c_business_group_id => p_business_group_id);
4161 FETCH csr_req_id INTO l_conc_reqest_id;
4162 CLOSE csr_req_id;
4163 hr_utility.set_location('..After Conc. Request id cursor csr_req_id',8);
4164 -- Get the params. based on the conc. request id.
4165 OPEN csr_ext_params (c_request_id => l_conc_reqest_id
4166 ,c_ext_dfn_id => l_ext_dfn_id
4167 ,c_business_group_id => p_business_group_id);
4168 FETCH csr_ext_params INTO l_ext_params;
4169 IF csr_ext_params%NOTFOUND THEN
4170 hr_utility.set_location('..Failed to get extract params',9);
4171 END IF;
4172 CLOSE csr_ext_params;
4173
4174 -- Get the benefit action id.
4175 OPEN csr_ben (c_ext_dfn_id => l_ext_dfn_id
4176 ,c_ext_rslt_id => l_ext_rslt_id
4177 ,c_business_group_id => p_business_group_id);
4178 FETCH csr_ben INTO l_ben_params;
4179 IF csr_ben%NOTFOUND THEN
4180 hr_utility.set_location('..Failed to get benefit action id',9);
4181 END IF;
4182 CLOSE csr_ben;
4183
4184 -- Store the params. in a PL/SQL table record
4185 g_extract_params(i).session_id := l_ext_params.session_id;
4186 g_extract_params(i).ext_dfn_type := l_ext_dfn_type;
4187 g_extract_params(i).business_group_id := l_ext_params.business_group_id;
4188 g_extract_params(i).concurrent_req_id := l_ext_params.tax_unit_id;
4189 g_extract_params(i).ext_dfn_id := l_ext_params.value1;
4190 g_extract_params(i).element_set_id := l_ext_params.value2;
4191 g_extract_params(i).element_type_id := l_ext_params.value3;
4192 g_extract_params(i).payroll_id := l_ext_params.value4;
4193 g_extract_params(i).gre_org_id := l_ext_params.value5;
4194 g_extract_params(i).con_set_id := l_ext_params.value6;
4195 g_extract_params(i).selection_criteria := l_ext_params.attribute1;
4196 g_extract_params(i).reporting_dimension := l_ext_params.attribute2;
4197
4198 g_extract_params(i).extract_start_date :=
4199 fnd_date.canonical_to_date(l_ext_params.attribute3);
4200
4201 g_extract_params(i).extract_end_date :=
4202 fnd_date.canonical_to_date(l_ext_params.attribute4);
4203 g_extract_params(i).benefit_action_id := l_ben_params.benefit_action_id;
4204
4205 g_extract_params(i).org_id := l_ext_params.attribute5;
4206 g_extract_params(i).person_type_id := l_ext_params.attribute6;
4207 g_extract_params(i).location_id := l_ext_params.attribute7;
4208 hr_utility.set_location('..Stored the params. in a PL/SQL table record',9);
4209 -- Get the Legislation Code, Currency Code
4210 OPEN csr_leg_code (c_business_group_id => p_business_group_id);
4211 FETCH csr_leg_code INTO g_extract_params(i).legislation_code,
4212 g_extract_params(i).currency_code;
4213 CLOSE csr_leg_code;
4214 g_legislation_code := g_extract_params(i).legislation_code;
4215 g_business_group_id := p_business_group_id;
4216 hr_utility.set_location('..Stored bg grp id, leg code in PL/SQL table', 10);
4217 --
4218 -- Get Assignment Run dimension Id as we will be using for calculating
4219 -- the amount.
4220 IF g_legislation_code ='MX' THEN
4221 l_dimension_name := 'Assignment Within GRE Run';
4222 ELSIF g_legislation_code ='US' THEN
4223 l_dimension_name := 'Assignment-Level Current Run';
4224 END IF;
4225
4226 OPEN csr_asg_dimId(g_legislation_code, l_dimension_name);
4227 FETCH csr_asg_dimId INTO g_asgrun_dim_id;
4228 CLOSE csr_asg_dimId;
4229 hr_utility.set_location('..Got the dim id for _ASG_RUN dimension: '||g_asgrun_dim_id, 11);
4230 --
4231 -- Get the element details based on the element set or element type id
4232 -- and store in a PL/SQL table.
4233 --
4234 Get_Element_Details
4235 (p_element_type_id => g_extract_params(i).element_type_id
4236 ,p_element_set_id => g_extract_params(i).element_set_id
4237 ,p_effective_date => g_extract_params(i).extract_end_date
4238 ,p_business_group_id => p_business_group_id
4239 );
4240 hr_utility.set_location('..Stored the Element Ids in PL/SQL table', 12);
4241 --
4242 -- Pass the conc program id's to store the values into PLSQL table.
4243 --
4244 Set_ConcProg_Parameter_Values
4245 (p_ext_dfn_id => g_extract_params(i).ext_dfn_id
4246 ,p_reporting_dimension => g_extract_params(i).reporting_dimension
4247 ,p_selection_criteria => g_extract_params(i).selection_criteria
4248 ,p_element_set_id => g_extract_params(i).element_set_id
4249 ,p_element_type_id => g_extract_params(i).element_type_id
4250 ,p_start_date => g_extract_params(i).extract_start_date
4251 ,p_end_date => g_extract_params(i).extract_end_date
4252 ,p_gre_id => g_extract_params(i).gre_org_id
4253 ,p_payroll_id => g_extract_params(i).payroll_id
4254 ,p_con_set => g_extract_params(i).con_set_id
4255 ,p_org_id => g_extract_params(i).org_id
4256 ,p_person_type_id => g_extract_params(i).person_type_id
4257 ,p_location_id => g_extract_params(i).location_id
4258 );
4259 hr_utility.set_location('..Stored the Conc. Program parameters', 17);
4260
4261 END IF;
4262 --
4263 -- Check if for this assignment id there are assign. action(s) which have
4264 -- processed the element(s). If any then return return Y i.e. assign needs
4265 -- to be extracted.
4266
4267 g_person_id:= NVL(ben_ext_person.g_person_id
4268 ,get_current_extract_person(p_assignment_id)
4269 );
4270 IF g_debug THEN
4271 hr_utility.set_location('..business_group_id : '||p_business_group_id, 17);
4272 hr_utility.set_location('..Processing Assig Id : '||p_assignment_id, 17);
4273 hr_utility.set_location('..Processing Person Id : '||g_person_id, 17);
4274 hr_utility.set_location('..Processing Eff.Date : '||p_effective_date, 17);
4275 END IF;
4276
4277 OPEN csr_assig (c_assignment_id => p_assignment_id
4278 ,c_effective_date => LEAST(g_extract_params(i).extract_end_date,
4279 p_effective_date)
4280 ,c_business_group_id => p_business_group_id);
4281 FETCH csr_assig INTO l_assig_rec;
4282 CLOSE csr_assig;
4283 --
4284 -- Check if the assignment is benefits one, if yes then return true and then
4285 -- later on we can check if there are any employee assignments within
4286 -- the extarct date-range.
4287 --
4288
4289 IF l_assig_rec.assignment_type = 'B' AND
4290 g_extract_params(i).ext_dfn_type <> 'GBL_MUL_CHG' THEN
4291
4292 hr_utility.set_location('..Assignment is of type Benefits:(B)', 17);
4293 l_return_value := 'Y';
4294 g_primary_assig(p_assignment_id) := l_assig_rec;
4295
4296 ELSIF g_extract_params(i).ext_dfn_type = 'GBL_FPR' THEN
4297
4298 hr_utility.set_location('..Reporting Dimension : GBL_FPR', 17);
4299
4300 l_return_value := Chk_IfAsg_Valid
4301 (p_assignment_id => p_assignment_id
4302 ,p_business_group_id => p_business_group_id
4303 ,p_person_id => g_person_id
4304 ,p_effective_date => p_effective_date
4305 );
4306 -- Check if any secondary or terminated assignments are valid
4307 IF g_legislation_code = 'MX' AND
4308 l_return_value <> 'Y' THEN
4309 l_return_value := Chk_IfSecAsg_Valid
4310 (p_pri_assignment_id => p_assignment_id
4311 ,p_business_group_id => p_business_group_id
4312 ,p_person_id => g_person_id
4313 );
4314 hr_utility.set_location(' Chk_IfSecAsg_Valid:l_return_value: '||l_return_value, 19);
4315 END IF;
4316
4317 IF l_return_value = 'Y' THEN
4318 OPEN csr_assig (c_assignment_id => p_assignment_id
4319 ,c_effective_date => least(g_extract_params(i).extract_end_date,
4320 p_effective_date)
4321 ,c_business_group_id => p_business_group_id);
4322 FETCH csr_assig INTO g_primary_assig(p_assignment_id);
4323 CLOSE csr_assig;
4324 hr_utility.set_location('..Valid Assig Id : '||p_assignment_id, 20);
4325 END IF;
4326
4327 ELSIF g_extract_params(i).ext_dfn_type IN ('GBL_CHG' -- Global Single Change
4328 ,'GBL_MUL_CHG' -- Multi Change
4329 ) THEN
4330 hr_utility.set_location('..Reporting Dimension : GBL_MUL_CHG or GBL_CHG', 17);
4331 -- The Extract is a Change Extract, check if there are any events for this
4332 -- this person id within the given extract date-range.
4333 --
4334 OPEN csr_chk_log (c_person_id => g_person_id
4335 ,c_ext_start_date => g_extract_params(i).extract_start_date
4336 ,c_ext_end_date => g_extract_params(i).extract_end_date);
4337 FETCH csr_chk_log INTO l_Chg_Evt_Exists;
4338 IF csr_chk_log%NOTFOUND THEN
4339 CLOSE csr_chk_log;
4340 l_return_value := 'N';
4341 RETURN l_return_value;
4342 END IF;
4343 CLOSE csr_chk_log;
4344 --
4345 -- Check if the person needs to be extracted based on the org, person type id,
4346 -- location id, if not then return N, i.e. not to be extracted.
4347 l_valid_person_chk := Chk_Person_Asg
4348 (p_assignment_id => p_assignment_id
4349 ,p_person_id => g_person_id
4350 ,p_bus_grp_id => p_business_group_id
4351 ,p_gre_org_id => g_extract_params(i).gre_org_id
4352 ,p_org_id => g_extract_params(i).org_id
4353 ,p_person_type_id => g_extract_params(i).person_type_id
4354 ,p_location_id => g_extract_params(i).location_id
4355 ,p_effective_date => LEAST(g_extract_params(i).extract_end_date,
4356 p_effective_date)
4357 );
4358 IF l_valid_person_chk = 'Y' THEN
4359 l_return_value := 'Y';
4360 hr_utility.set_location('..Chk_Person_Asg = Y : '||p_assignment_id, 79);
4361 ELSE
4362 l_return_value := 'N';
4363 RETURN l_return_value;
4364 END IF; -- IF l_valid_person_chk = 'Y'
4365
4366 IF g_extract_params(i).reporting_dimension = 'CHG_ALL' THEN
4367 -- Check if the reporting dimension is All Employees who have changes
4368 -- recorded within the extract date-range.
4369 hr_utility.set_location('..Reporting Dimension : CHG_ALL', 79);
4370 OPEN csr_val_assg (c_assignment_id => p_assignment_id
4371 ,c_payroll_id => g_extract_params(i).payroll_id
4372 ,c_start_date => g_extract_params(i).extract_start_date
4373 ,c_end_date => g_extract_params(i).extract_end_date
4374 );
4375 FETCH csr_val_assg INTO l_valid_action;
4376 IF csr_val_assg%NOTFOUND THEN
4377 hr_utility.set_location('..CHG_ALL l_valid_action: '||l_valid_action, 79);
4378 -- Check the person id is eligible for passed orgid ,persontype id and location id.
4379 l_return_value := 'N';
4380 END IF; -- IF csr_val_assg%NOTFOUND
4381 CLOSE csr_val_assg;
4382
4383 ELSIF g_extract_params(i).reporting_dimension = 'CHG_PEN' THEN
4384 -- Check if the reporting dimension is Employees Enrolled in a plan
4385 -- who have changes recorded within the extract date-range.
4386 hr_utility.set_location('..Reporting Dimension : CHG_PEN', 79);
4387 l_ele_type_id := g_element.FIRST;
4388 << Chg_Action >>
4389 WHILE l_ele_type_id IS NOT NULL
4390 LOOP
4391 hr_utility.set_location('..Ele_Type_ID'||l_ele_type_id, 17);
4392 hr_utility.set_location('..l_return_value'||l_return_value, 17);
4393 OPEN csr_chk_ele (c_start_date => g_extract_params(i).extract_start_date
4394 ,c_end_date => g_extract_params(i).extract_end_date
4395 ,c_assignment_id => p_assignment_id
4396 ,c_business_group_id => g_business_group_id
4397 ,c_ele_type_id => l_ele_type_id
4398 ,c_payroll_id => g_extract_params(i).payroll_id
4399 ,c_org_id => g_extract_params(i).org_id
4400 );
4401 FETCH csr_chk_ele INTO l_valid_action;
4402 IF csr_chk_ele%FOUND THEN
4403 CLOSE csr_chk_ele;
4404 hr_utility.set_location('..g_person_id :'||g_person_id, 17);
4405 -- If valid then set return the value to Y, it means that this assignment
4406 -- should be extracted.
4407 OPEN csr_chk_asg(c_start_date => g_extract_params(i).extract_start_date
4408 ,c_end_date => g_extract_params(i).extract_end_date
4409 ,c_assignment_id => p_assignment_id
4410 ,c_business_group_id => g_business_group_id
4411 ,c_payroll_id => g_extract_params(i).payroll_id
4412 ,c_org_id => g_extract_params(i).org_id);
4413 FETCH csr_chk_asg INTO l_valid_action;
4414 IF csr_chk_asg%FOUND THEN
4415 CLOSE csr_chk_asg;
4416 l_return_value := 'Y';
4417 EXIT Chg_Action;
4418 END IF;
4419 CLOSE csr_chk_asg;
4420 ELSE
4421 l_prev_ele_type_id := l_ele_type_id;
4422 l_ele_type_id := g_element.NEXT(l_prev_ele_type_id);
4423 l_return_value := 'N';
4424 END IF;
4425 CLOSE csr_chk_ele;
4426 END LOOP Chg_Action;
4427 END IF; -- IF g_extract_params(i).reporting_dimension = 'CHG_ALL'
4428
4429 -- If the assignment is valid then store the details in PL/SQL table
4430 IF l_return_value = 'Y' THEN
4431 OPEN csr_assig (c_assignment_id => p_assignment_id
4432 ,c_effective_date => LEAST(g_extract_params(i).extract_end_date,
4433 p_effective_date)
4434 ,c_business_group_id => p_business_group_id);
4435 FETCH csr_assig INTO g_primary_assig(p_assignment_id);
4436 CLOSE csr_assig;
4437 g_primary_assig(p_assignment_id).Calculate_Amount := 'YES';
4438 END IF; -- l_return_value = 'Y'
4439 END IF; -- IF l_assig_rec.assignment_type = 'B'
4440 hr_utility.set_location('..l_return_value : '||l_return_value, 79);
4441 hr_utility.set_location('Leaving: '||l_proc_name, 80);
4442
4443 IF csr_chk_ele%ISOPEN THEN
4444 CLOSE csr_chk_ele;
4445 END IF;
4446
4447 RETURN l_return_value;
4448
4449 EXCEPTION
4450 WHEN Others THEN
4451 p_error_message :='SQL-ERRM :'||SQLERRM;
4452 hr_utility.set_location('..'||p_error_message,85);
4453 hr_utility.set_location('Leaving: '||l_proc_name, 90);
4454 IF csr_chk_ele%ISOPEN THEN
4455 CLOSE csr_chk_ele;
4456 END IF;
4457 RETURN l_return_value;
4458
4459 END Pension_Criteria_Full_Profile;
4460
4461 -- ========================================================================
4462 -- ~ Del_Service_Detail_Recs : Delete all the records created as part
4463 -- ~ of hidden record as they are not required.
4464 -- ========================================================================
4465 FUNCTION Del_Service_Detail_Recs
4466 (p_business_group_id ben_ext_rslt_dtl.business_group_id%TYPE
4467 )RETURN NUMBER IS
4468 CURSOR csr_err (c_bg_id IN NUMBER
4469 ,c_ext_rslt_id IN NUMBER) IS
4470 SELECT err.person_id
4471 ,err.typ_cd
4472 ,err.ext_rslt_id
4473 FROM ben_ext_rslt_err err
4474 WHERE err.business_group_id = c_bg_id
4475 AND err.typ_cd = 'E'
4476 AND err.ext_rslt_id = c_ext_rslt_id;
4477
4478 l_ext_dtl_rcd_id ben_ext_rcd.ext_rcd_id%TYPE;
4479 l_ext_main_rcd_id ben_ext_rcd.ext_rcd_id%TYPE;
4480 l_proc_name VARCHAR2(150):= g_proc_name||'Del_Service_Detail_Recs';
4481 l_return_value NUMBER := 0; --0= Sucess, -1=Error
4482 l_ben_params csr_ben%ROWTYPE;
4483 BEGIN
4484 hr_utility.set_location('Entering :'||l_proc_name, 5);
4485 -- Get the record id for the Hidden Detail record
4486 hr_utility.set_location('..Get the hidden record for extract running..',10);
4487 FOR csr_rcd_rec IN csr_ext_rcd_id
4488 (c_hide_flag => 'Y' -- Y=Record is hidden one
4489 ,c_rcd_type_cd => 'D')-- D=Detail, T=Total, H-Header
4490 -- Loop through each detail record for the extract
4491 LOOP
4492 -- Delete all hidden detail records for the all persons
4493 DELETE
4494 FROM ben_ext_rslt_dtl
4495 WHERE ext_rcd_id = csr_rcd_rec.ext_rcd_id
4496 AND ext_rslt_id = Ben_Ext_Thread.g_ext_rslt_id
4497 AND business_group_id = p_business_group_id;
4498 END LOOP;
4499
4500 -- Get the benefit action id for the extract
4501 OPEN csr_ben (c_ext_dfn_id => Ben_Ext_Thread.g_ext_dfn_id
4502 ,c_ext_rslt_id => Ben_Ext_Thread.g_ext_rslt_id
4503 ,c_business_group_id => p_business_group_id);
4504 FETCH csr_ben INTO l_ben_params;
4505 CLOSE csr_ben;
4506
4507 -- Flag the person in ben_person_actions and ben_batch_ranges
4508 -- as Unporcessed and errored.
4509 FOR err_rec IN csr_err(c_bg_id => p_business_group_id
4510 ,c_ext_rslt_id => Ben_Ext_Thread.g_ext_rslt_id)
4511 LOOP
4512 Exclude_Person
4513 (p_person_id => err_rec.person_id
4514 ,p_business_group_id => p_business_group_id
4515 ,p_benefit_action_id => l_ben_params.benefit_action_id
4516 ,p_flag_thread => 'Y');
4517 DELETE
4518 FROM ben_ext_rslt_dtl dtl
4519 WHERE dtl.ext_rslt_id = Ben_Ext_Thread.g_ext_rslt_id
4520 AND dtl.person_id = err_rec.person_id
4521 AND dtl.business_group_id = p_business_group_id;
4522
4523 END LOOP;
4524 hr_utility.set_location('Leaving :'||l_proc_name, 25);
4525
4526 RETURN l_return_value;
4527
4528 EXCEPTION
4529 WHEN Others THEN
4530 hr_utility.set_location('.....Exception when others raised..', 20);
4531 hr_utility.set_location('Leaving :'||l_proc_name, 25);
4532 RETURN -1;
4533
4534 END Del_Service_Detail_Recs;
4535
4536 -- =============================================================================
4537 -- Raise_Extract_Warning:
4538 -- When called from the Rule of a extract detail data element
4539 -- it logs a warning in the ben_ext_rslt_err table against
4540 -- the person being processed (or as specified by context of
4541 -- assignment id ). It prefixes all warning messages with a
4542 -- string "Warning raised in data element "||element_name
4543 -- This allows the same Rule to be called from different data
4544 -- elements. Usage example.
4545 -- RAISE_EXTRACT_WARNING("No initials were found.")
4546 -- RRTURNCODE MEANING
4547 -- -1 Cannot raise warning against a header/trailer
4548 -- record. System Extract does not allow it.
4549 -- -2 No current extract process was found.
4550 -- -3 No person was found.A Warning in System Extract
4551 -- is always raised against a person.
4552 -- =============================================================================
4553 FUNCTION Raise_Extract_Warning
4554 (p_assignment_id IN NUMBER -- context
4555 ,p_error_text IN VARCHAR2
4556 ,p_error_NUMBER IN NUMBER DEFAULT NULL
4557 ) RETURN NUMBER IS
4558
4559 l_ext_rslt_id NUMBER;
4560 l_person_id NUMBER;
4561 l_error_text VARCHAR2(2000);
4562 l_return_value NUMBER:= 0;
4563
4564 BEGIN
4565 --
4566 IF p_assignment_id <> -1 THEN
4567 l_ext_rslt_id:= get_current_extract_result;
4568 IF l_ext_rslt_id <> -1 THEN
4569 IF p_error_NUMBER IS NULL THEN
4570 l_error_text:= 'Warning raised in data element '||
4571 NVL(ben_ext_fmt.g_elmt_name
4572 ,ben_ext_person.g_elmt_name)||'. '||
4573 p_error_text;
4574 ELSE
4575 ben_ext_thread.g_err_num := p_error_NUMBER;
4576 ben_ext_thread.g_err_name := p_error_text;
4577 l_error_text :=
4578 ben_ext_fmt.get_error_msg(TO_NUMBER(SUBSTR(p_error_text, 5, 5)),
4579 p_error_text,NVL(ben_ext_fmt.g_elmt_name,ben_ext_person.g_elmt_name) );
4580
4581 END IF;
4582 l_person_id:= NVL(get_current_extract_person(p_assignment_id)
4583 ,ben_ext_person.g_person_id);
4584
4585 IF l_person_id IS NOT NULL THEN
4586 --
4587 ben_ext_util.write_err
4588 (p_err_num => p_error_NUMBER
4589 ,p_err_name => l_error_text
4590 ,p_typ_cd => 'W'
4591 ,p_person_id => l_person_id
4592 ,p_request_id => fnd_global.conc_request_id
4593 ,p_business_group_id => fnd_global.per_business_group_id
4594 ,p_ext_rslt_id => get_current_extract_result
4595 );
4596 l_return_value:= 0;
4597 ELSE
4598 l_return_value:= -3;
4599 END IF;
4600 ELSE
4601 --
4602 l_return_value:= -2;
4603 --
4604 END IF;
4605 --
4606 ELSE
4607 --
4608 l_return_value := -1;
4609 --
4610 END IF;
4611 --
4612 RETURN l_return_value;
4613
4614 END Raise_Extract_Warning;
4615
4616 -- =============================================================================
4617 -- Get_Current_Extract_Result:
4618 -- Returns the person id associated with the given assignment.
4619 -- If none is found,it returns NULL. This may arise if the
4620 -- user calls this from a header/trailer record, where
4621 -- a dummy context of assignment_id = -1 is passed.
4622 -- =============================================================================
4623 FUNCTION Get_Current_Extract_Result
4624 RETURN NUMBER IS
4625
4626 e_extract_process_not_running EXCEPTION;
4627 PRAGMA EXCEPTION_INIT(e_extract_process_not_running,-8002);
4628 l_ext_rslt_id NUMBER;
4629
4630 BEGIN
4631
4632 l_ext_rslt_id := ben_ext_thread.g_ext_rslt_id;
4633 RETURN l_ext_rslt_id;
4634
4635 EXCEPTION
4636
4637 WHEN e_extract_process_not_running THEN
4638 RETURN -1;
4639
4640 END Get_Current_Extract_Result;
4641
4642 -- =============================================================================
4643 -- Get_Current_Extract_Person:
4644 -- Returns the ext_rslt_id for the current extract process
4645 -- if one is running, else returns -1
4646 -- =============================================================================
4647 FUNCTION Get_Current_Extract_Person
4648 (p_assignment_id IN NUMBER )
4649 RETURN NUMBER IS
4650 l_person_id NUMBER;
4651 BEGIN
4652 SELECT person_id
4653 INTO l_person_id
4654 FROM per_all_assignments_f
4655 WHERE assignment_id = p_assignment_id
4656 AND ROWNUM < 2;
4657 RETURN l_person_id;
4658 EXCEPTION
4659 WHEN NO_DATA_FOUND THEN
4660 RETURN NULL;
4661 END Get_Current_Extract_Person;
4662
4663 END PQP_Pension_Extracts;
[Home] [Help]