[Home] [Help]
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;
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;
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;
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
26: WHERE pee.element_link_id = pel.element_link_id
27: AND pet.element_type_id = pel.element_type_id
28: AND pet.element_name = 'FR_SICKNESS_CPAM_INFO'
29: AND pee.element_entry_id = p_element_entry_id;
39: ,max(decode(i.name,'Gross Amount',i.input_value_id,null))
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
71: ,NULL)) Gross_Daily_Rate,
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
81: and pee.element_entry_id = p_element_entry_id;
82: --
83: CURSOR C_element_link(p_element_type_id IN NUMBER,p_source_element_link_id IN NUMBER, p_effective_start_date IN DATE) IS
84: SELECT pel.element_link_id
85: FROM pay_element_types_f pet, pay_element_links_f pel, pay_element_links_f pel1
86: WHERE pet.element_type_id = pel.element_type_id
87: AND pel.business_group_id = pel1.business_group_id
88: AND pet.element_type_id = p_element_type_id
89: AND p_effective_start_date BETWEEN pel.effective_start_date AND pel.effective_end_date
106: rec_element_link c_element_link%ROWTYPE;
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
160: AND pasg.assignment_id = p_assignment_id;
161:
162: CURSOR C_proc_entry(p_element_entry_id IN NUMBER) IS
163: SELECT 'X' dum
164: FROM pay_element_entries_f pee, pay_element_links_f pel, pay_element_types_f pet
165: WHERE pee.element_link_id = pel.element_link_id
166: AND pet.element_type_id = pel.element_type_id
167: AND pet.element_name = 'FR_SICKNESS_CPAM_PROCESS'
168: AND pee.creator_type IN ('F','H')