1: PACKAGE BODY PAY_FR_CPAM_PREPROCESSOR AS
2: /* $Header: pyfrcpam.pkb 120.0.12000000.2 2007/02/27 13:47:25 spendhar noship $ */
3: g_info_element_type_id pay_element_types_f.element_type_id%type;
4: g_info_pymt_frm_dt_iv_id pay_input_values_f.input_value_id%type;
5: g_info_pymt_to_dt_iv_id pay_input_values_f.input_value_id%type;
6: g_info_days_iv_id pay_input_values_f.input_value_id%type;
7: --g_info_subrogated_iv_id pay_input_values_f.input_value_id%type;
8: g_info_gross_amt_iv_id pay_input_values_f.input_value_id%type;
1: PACKAGE BODY PAY_FR_CPAM_PREPROCESSOR AS
2: /* $Header: pyfrcpam.pkb 120.0.12000000.2 2007/02/27 13:47:25 spendhar noship $ */
3: g_info_element_type_id pay_element_types_f.element_type_id%type;
4: g_info_pymt_frm_dt_iv_id pay_input_values_f.input_value_id%type;
5: g_info_pymt_to_dt_iv_id pay_input_values_f.input_value_id%type;
6: g_info_days_iv_id pay_input_values_f.input_value_id%type;
7: --g_info_subrogated_iv_id pay_input_values_f.input_value_id%type;
8: g_info_gross_amt_iv_id pay_input_values_f.input_value_id%type;
9: g_info_net_amt_iv_id pay_input_values_f.input_value_id%type;
2: /* $Header: pyfrcpam.pkb 120.0.12000000.2 2007/02/27 13:47:25 spendhar noship $ */
3: g_info_element_type_id pay_element_types_f.element_type_id%type;
4: g_info_pymt_frm_dt_iv_id pay_input_values_f.input_value_id%type;
5: g_info_pymt_to_dt_iv_id pay_input_values_f.input_value_id%type;
6: g_info_days_iv_id pay_input_values_f.input_value_id%type;
7: --g_info_subrogated_iv_id pay_input_values_f.input_value_id%type;
8: g_info_gross_amt_iv_id pay_input_values_f.input_value_id%type;
9: g_info_net_amt_iv_id pay_input_values_f.input_value_id%type;
10: g_info_gross_rt_iv_id pay_input_values_f.input_value_id%type;
3: g_info_element_type_id pay_element_types_f.element_type_id%type;
4: g_info_pymt_frm_dt_iv_id pay_input_values_f.input_value_id%type;
5: g_info_pymt_to_dt_iv_id pay_input_values_f.input_value_id%type;
6: g_info_days_iv_id pay_input_values_f.input_value_id%type;
7: --g_info_subrogated_iv_id pay_input_values_f.input_value_id%type;
8: g_info_gross_amt_iv_id pay_input_values_f.input_value_id%type;
9: g_info_net_amt_iv_id pay_input_values_f.input_value_id%type;
10: g_info_gross_rt_iv_id pay_input_values_f.input_value_id%type;
11: g_info_net_rt_iv_id pay_input_values_f.input_value_id%type;
4: g_info_pymt_frm_dt_iv_id pay_input_values_f.input_value_id%type;
5: g_info_pymt_to_dt_iv_id pay_input_values_f.input_value_id%type;
6: g_info_days_iv_id pay_input_values_f.input_value_id%type;
7: --g_info_subrogated_iv_id pay_input_values_f.input_value_id%type;
8: g_info_gross_amt_iv_id pay_input_values_f.input_value_id%type;
9: g_info_net_amt_iv_id pay_input_values_f.input_value_id%type;
10: g_info_gross_rt_iv_id pay_input_values_f.input_value_id%type;
11: g_info_net_rt_iv_id pay_input_values_f.input_value_id%type;
12:
5: g_info_pymt_to_dt_iv_id pay_input_values_f.input_value_id%type;
6: g_info_days_iv_id pay_input_values_f.input_value_id%type;
7: --g_info_subrogated_iv_id pay_input_values_f.input_value_id%type;
8: g_info_gross_amt_iv_id pay_input_values_f.input_value_id%type;
9: g_info_net_amt_iv_id pay_input_values_f.input_value_id%type;
10: g_info_gross_rt_iv_id pay_input_values_f.input_value_id%type;
11: g_info_net_rt_iv_id pay_input_values_f.input_value_id%type;
12:
13: g_proc_element_type_id pay_element_types_f.element_type_id%type;
6: g_info_days_iv_id pay_input_values_f.input_value_id%type;
7: --g_info_subrogated_iv_id pay_input_values_f.input_value_id%type;
8: g_info_gross_amt_iv_id pay_input_values_f.input_value_id%type;
9: g_info_net_amt_iv_id pay_input_values_f.input_value_id%type;
10: g_info_gross_rt_iv_id pay_input_values_f.input_value_id%type;
11: g_info_net_rt_iv_id pay_input_values_f.input_value_id%type;
12:
13: g_proc_element_type_id pay_element_types_f.element_type_id%type;
14: g_proc_pymt_frm_dt_iv_id pay_input_values_f.input_value_id%type;
7: --g_info_subrogated_iv_id pay_input_values_f.input_value_id%type;
8: g_info_gross_amt_iv_id pay_input_values_f.input_value_id%type;
9: g_info_net_amt_iv_id pay_input_values_f.input_value_id%type;
10: g_info_gross_rt_iv_id pay_input_values_f.input_value_id%type;
11: g_info_net_rt_iv_id pay_input_values_f.input_value_id%type;
12:
13: g_proc_element_type_id pay_element_types_f.element_type_id%type;
14: g_proc_pymt_frm_dt_iv_id pay_input_values_f.input_value_id%type;
15: g_proc_pymt_to_dt_iv_id pay_input_values_f.input_value_id%type;
10: g_info_gross_rt_iv_id pay_input_values_f.input_value_id%type;
11: g_info_net_rt_iv_id pay_input_values_f.input_value_id%type;
12:
13: g_proc_element_type_id pay_element_types_f.element_type_id%type;
14: g_proc_pymt_frm_dt_iv_id pay_input_values_f.input_value_id%type;
15: g_proc_pymt_to_dt_iv_id pay_input_values_f.input_value_id%type;
16: g_proc_days_iv_id pay_input_values_f.input_value_id%type;
17: --g_proc_subrogated_iv_id pay_input_values_f.input_value_id%type;
18: g_proc_gross_amt_iv_id pay_input_values_f.input_value_id%type;
11: g_info_net_rt_iv_id pay_input_values_f.input_value_id%type;
12:
13: g_proc_element_type_id pay_element_types_f.element_type_id%type;
14: g_proc_pymt_frm_dt_iv_id pay_input_values_f.input_value_id%type;
15: g_proc_pymt_to_dt_iv_id pay_input_values_f.input_value_id%type;
16: g_proc_days_iv_id pay_input_values_f.input_value_id%type;
17: --g_proc_subrogated_iv_id pay_input_values_f.input_value_id%type;
18: g_proc_gross_amt_iv_id pay_input_values_f.input_value_id%type;
19: g_proc_net_amt_iv_id pay_input_values_f.input_value_id%type;
12:
13: g_proc_element_type_id pay_element_types_f.element_type_id%type;
14: g_proc_pymt_frm_dt_iv_id pay_input_values_f.input_value_id%type;
15: g_proc_pymt_to_dt_iv_id pay_input_values_f.input_value_id%type;
16: g_proc_days_iv_id pay_input_values_f.input_value_id%type;
17: --g_proc_subrogated_iv_id pay_input_values_f.input_value_id%type;
18: g_proc_gross_amt_iv_id pay_input_values_f.input_value_id%type;
19: g_proc_net_amt_iv_id pay_input_values_f.input_value_id%type;
20: g_proc_gross_rt_iv_id pay_input_values_f.input_value_id%type;
13: g_proc_element_type_id pay_element_types_f.element_type_id%type;
14: g_proc_pymt_frm_dt_iv_id pay_input_values_f.input_value_id%type;
15: g_proc_pymt_to_dt_iv_id pay_input_values_f.input_value_id%type;
16: g_proc_days_iv_id pay_input_values_f.input_value_id%type;
17: --g_proc_subrogated_iv_id pay_input_values_f.input_value_id%type;
18: g_proc_gross_amt_iv_id pay_input_values_f.input_value_id%type;
19: g_proc_net_amt_iv_id pay_input_values_f.input_value_id%type;
20: g_proc_gross_rt_iv_id pay_input_values_f.input_value_id%type;
21: g_proc_net_rt_iv_id pay_input_values_f.input_value_id%type;
14: g_proc_pymt_frm_dt_iv_id pay_input_values_f.input_value_id%type;
15: g_proc_pymt_to_dt_iv_id pay_input_values_f.input_value_id%type;
16: g_proc_days_iv_id pay_input_values_f.input_value_id%type;
17: --g_proc_subrogated_iv_id pay_input_values_f.input_value_id%type;
18: g_proc_gross_amt_iv_id pay_input_values_f.input_value_id%type;
19: g_proc_net_amt_iv_id pay_input_values_f.input_value_id%type;
20: g_proc_gross_rt_iv_id pay_input_values_f.input_value_id%type;
21: g_proc_net_rt_iv_id pay_input_values_f.input_value_id%type;
22:
15: g_proc_pymt_to_dt_iv_id pay_input_values_f.input_value_id%type;
16: g_proc_days_iv_id pay_input_values_f.input_value_id%type;
17: --g_proc_subrogated_iv_id pay_input_values_f.input_value_id%type;
18: g_proc_gross_amt_iv_id pay_input_values_f.input_value_id%type;
19: g_proc_net_amt_iv_id pay_input_values_f.input_value_id%type;
20: g_proc_gross_rt_iv_id pay_input_values_f.input_value_id%type;
21: g_proc_net_rt_iv_id pay_input_values_f.input_value_id%type;
22:
23: CURSOR C_info_entry(p_element_entry_id IN NUMBER) IS
16: g_proc_days_iv_id pay_input_values_f.input_value_id%type;
17: --g_proc_subrogated_iv_id pay_input_values_f.input_value_id%type;
18: g_proc_gross_amt_iv_id pay_input_values_f.input_value_id%type;
19: g_proc_net_amt_iv_id pay_input_values_f.input_value_id%type;
20: g_proc_gross_rt_iv_id pay_input_values_f.input_value_id%type;
21: g_proc_net_rt_iv_id pay_input_values_f.input_value_id%type;
22:
23: CURSOR C_info_entry(p_element_entry_id IN NUMBER) IS
24: SELECT 'X' dum
17: --g_proc_subrogated_iv_id pay_input_values_f.input_value_id%type;
18: g_proc_gross_amt_iv_id pay_input_values_f.input_value_id%type;
19: g_proc_net_amt_iv_id pay_input_values_f.input_value_id%type;
20: g_proc_gross_rt_iv_id pay_input_values_f.input_value_id%type;
21: g_proc_net_rt_iv_id pay_input_values_f.input_value_id%type;
22:
23: CURSOR C_info_entry(p_element_entry_id IN NUMBER) IS
24: SELECT 'X' dum
25: FROM pay_element_entries_f pee, pay_element_links_f pel, pay_element_types_f pet
40: ,max(decode(i.name,'Net Amount',i.input_value_id,null))
41: ,max(decode(i.name,'Gross Daily Rate',i.input_value_id,null))
42: ,max(decode(i.name,'Net Daily Rate',i.input_value_id,null))
43: FROM pay_element_types_f e,
44: pay_input_values_f i
45: WHERE e.element_name = p_element_type
46: and e.legislation_code = 'FR'
47: and e.element_type_id = i.element_type_id
48: and p_effective_start_date between e.effective_start_date and e.effective_end_date
72: max(decode(eev.input_value_id,g_info_net_rt_iv_id
73: ,to_number(eev.screen_entry_value)
74: ,NULL)) Net_Daily_Rate
75: FROM pay_element_types_f pet, pay_element_entries_f pee,
76: pay_input_values_f piv, pay_element_entry_values_f eev
77: WHERE pee.element_entry_id = eev.element_entry_id
78: and pet.element_type_id = piv.element_type_id
79: and piv.input_value_id = eev.input_value_id
80: and pet.element_name = 'FR_SICKNESS_CPAM_INFO'
107:
108: CURSOR C_info_entries(p_assignment_id IN Number, p_start_date IN Date, p_end_date IN Date, p_curr_entry_id IN Number) IS
109: SELECT pev.effective_start_date
110: FROM pay_element_entries_f pee, pay_element_links_f pel, pay_element_types_f pet,
111: pay_input_values_f piv, pay_element_entry_values_f pev
112: WHERE pee.element_link_id = pel.element_link_id
113: AND pet.element_type_id = pel.element_type_id
114: AND pee.element_entry_id= pev.element_entry_id
115: AND pet.element_type_id = piv.element_type_id