[Home] [Help]
PACKAGE BODY: APPS.PAY_ES_RULES
Source
1 PACKAGE BODY pay_es_rules AS
2 /* $Header: pyesrule.pkb 120.3 2005/07/05 05:30:45 grchandr noship $ */
3 --
4 -------------------------------------------------------------------------------
5 -- get_main_tax_unit_id
6 -------------------------------------------------------------------------------
7 PROCEDURE get_main_tax_unit_id(p_assignment_id IN NUMBER
8 ,p_effective_date IN DATE
9 ,p_tax_unit_id IN OUT NOCOPY NUMBER) IS
10 --
11 CURSOR csr_get_wc_details IS
12 SELECT scl.segment2 work_center
13 FROM per_all_assignments_f paaf
14 ,hr_soft_coding_keyflex scl
15 WHERE paaf.assignment_id = p_assignment_id
16 AND paaf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
17 AND p_effective_date BETWEEN effective_start_date
18 AND effective_end_date;
19 --
20 CURSOR csr_get_le_details (p_wc_organization_id NUMBER) IS
21 SELECT hoi.organization_id le_id
22 FROM hr_organization_information hoi
23 WHERE hoi.org_information1 = p_wc_organization_id
24 AND hoi.org_information_context = 'ES_WORK_CENTER_REF';
25 --
26 l_wc_id hr_all_organization_units.organization_id%TYPE;
27 --
28 BEGIN
29 --
30 p_tax_unit_id := NULL;
31 l_wc_id := NULL;
32 --
33 OPEN csr_get_wc_details;
34 FETCH csr_get_wc_details INTO l_wc_id;
35 CLOSE csr_get_wc_details;
36 --
37 IF l_wc_id IS NOT NULL THEN
38 OPEN csr_get_le_details(l_wc_id);
39 FETCH csr_get_le_details INTO p_tax_unit_id;
40 CLOSE csr_get_le_details;
41 END IF;
42 --
43 END get_main_tax_unit_id;
44 -------------------------------------------------------------------------------
45 -- get_source_text_context
46 -------------------------------------------------------------------------------
47 PROCEDURE get_source_text_context(p_asg_act_id NUMBER
48 ,p_ee_id NUMBER
49 ,p_source_text IN OUT NOCOPY VARCHAR2) IS
50 --
51 CURSOR csr_get_payment_key(p_assignment_action_id NUMBER) IS
52 SELECT eev.screen_entry_value payment_key
53 FROM pay_element_entries_f pee
54 ,pay_element_entry_values_f eev
55 ,pay_input_values_f piv
56 ,pay_element_types_f pet
57 ,pay_assignment_actions paa
58 ,pay_payroll_actions ppa
59 WHERE paa.assignment_action_id = p_assignment_action_id
60 AND ppa.payroll_action_id = paa.payroll_action_id
61 AND pee.element_entry_id = eev.element_entry_id
62 AND eev.input_value_id + 0 = piv.input_value_id
63 AND piv.name = 'Payment Key'
64 AND piv.element_type_id = pet.element_type_id
65 AND ppa.effective_date BETWEEN piv.effective_start_date
66 AND piv.effective_end_date
67 AND pee.assignment_id = paa.assignment_id
68 AND pet.element_name = 'Tax Details'
69 AND pet.legislation_code = 'ES'
70 AND ppa.effective_date BETWEEN pee.effective_start_date
71 AND pee.effective_end_date
72 AND ppa.effective_date BETWEEN eev.effective_start_date
73 AND eev.effective_end_date
74 AND ppa.effective_date BETWEEN pet.effective_start_date
75 AND pet.effective_end_date;
76 --
77 l_payment_key VARCHAR2(1);
78 BEGIN
79 --
80 l_payment_key := 'X';
81 --
82 hr_utility.set_location('pay_es_rules.get_source_text_context',1);
83 --
84 OPEN csr_get_payment_key(p_asg_act_id);
85 FETCH csr_get_payment_key INTO l_payment_key;
86 CLOSE csr_get_payment_key;
87 --
88 p_source_text := NVL(l_payment_key,' ');
89 --
90 hr_utility.set_location('pay_es_rules.get_source_text_context='|| p_source_text,2);
91 --
92 END get_source_text_context;
93 -------------------------------------------------------------------------------
94 -- get_source_text_context
95 -------------------------------------------------------------------------------
96 PROCEDURE get_source_text2_context(p_asg_act_id NUMBER
97 ,p_ee_id NUMBER
98 ,p_source_text2 IN OUT NOCOPY VARCHAR2) IS
99 BEGIN
100 --
101 p_source_text2 := '0';
102 --
103 END get_source_text2_context;
104 -------------------------------------------------------------------------------
105 -- get_source_number_context
106 -------------------------------------------------------------------------------
107 PROCEDURE get_source_number_context(p_asg_act_id NUMBER
108 ,p_ee_id NUMBER
109 ,p_source_number IN OUT NOCOPY VARCHAR2) IS
110 --
111 CURSOR csr_get_epigraph_code(p_assignment_action_id NUMBER) IS
112 SELECT eev.screen_entry_value epigraph_code
113 FROM pay_element_entries_f pee
114 ,pay_element_entry_values_f eev
115 ,pay_input_values_f piv
116 ,pay_element_types_f pet
117 ,pay_assignment_actions paa
118 ,pay_payroll_actions ppa
119 ,per_time_periods ptp
120 WHERE paa.assignment_action_id = p_assignment_action_id
121 AND ppa.payroll_action_id = paa.payroll_action_id
122 AND ppa.payroll_id = ptp.payroll_id
123 AND ppa.time_period_id = ptp.time_period_id
124 AND pee.element_entry_id = eev.element_entry_id
125 AND eev.input_value_id + 0 = piv.input_value_id
126 AND piv.name = 'SS Epigraph Code'
127 AND piv.element_type_id = pet.element_type_id
128 AND ppa.effective_date BETWEEN piv.effective_start_date
129 AND piv.effective_end_date
130 AND pee.assignment_id = paa.assignment_id
131 AND pet.element_name = 'Social Security Details'
132 AND pet.legislation_code = 'ES'
133 AND pee.effective_end_date = eev.effective_end_date
134 AND eev.effective_end_date >= ptp.start_date
135 AND ppa.effective_date BETWEEN pet.effective_start_date
136 AND pet.effective_end_date
137 ORDER BY eev.effective_start_date;
138 --
139 BEGIN
140 --
141 p_source_number := '0';
142 OPEN csr_get_epigraph_code(p_asg_act_id);
143 FETCH csr_get_epigraph_code INTO p_source_number;
144 CLOSE csr_get_epigraph_code;
145 --p_source_number := '110';
146 --
147 END get_source_number_context;
148 -------------------------------------------------------------------------------
149 -- get_source_number2_context
150 -------------------------------------------------------------------------------
151 PROCEDURE get_source_number2_context(p_asg_act_id NUMBER
152 ,p_ee_id NUMBER
153 ,p_source_number2 IN OUT NOCOPY VARCHAR2) IS
154 --
155 CURSOR csr_get_cac(p_assignment_action_id NUMBER) IS
156 SELECT eev.screen_entry_value sec_cac
157 FROM pay_element_entries_f pee
158 ,pay_element_entry_values_f eev
159 ,pay_input_values_f piv
160 ,pay_element_types_f pet
161 ,pay_assignment_actions paa
162 ,pay_payroll_actions ppa
163 ,per_time_periods ptp
164 WHERE paa.assignment_action_id = p_assignment_action_id
165 AND ppa.payroll_action_id = paa.payroll_action_id
166 AND ppa.payroll_id = ptp.payroll_id
167 AND ppa.time_period_id = ptp.time_period_id
168 AND pee.element_entry_id = eev.element_entry_id
169 AND eev.input_value_id + 0 = piv.input_value_id
170 AND piv.name = 'Work Center CAC'
171 AND piv.element_type_id = pet.element_type_id
172 AND ppa.effective_date BETWEEN piv.effective_start_date
173 AND piv.effective_end_date
174 AND pee.assignment_id = paa.assignment_id
175 AND pet.element_name = 'Multiple Employment Details'
176 AND pet.legislation_code = 'ES'
177 AND pee.effective_end_date = eev.effective_end_date
178 AND eev.effective_end_date >= ptp.start_date
179 AND ppa.effective_date BETWEEN pet.effective_start_date
180 AND pet.effective_end_date
181 ORDER BY eev.effective_start_date;
182 --
183 BEGIN
184 --
185 p_source_number2 := '0';
186 OPEN csr_get_cac(p_asg_act_id);
187 FETCH csr_get_cac INTO p_source_number2;
188 CLOSE csr_get_cac;
189 --
190 END get_source_number2_context;
191
192 END pay_es_rules;