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