1 PACKAGE BODY PAY_IE_ARCHIVE_DETAIL_PKG as
2 /* $Header: pyieelin.pkb 120.1 2006/12/11 13:44:37 sgajula noship $ */
3
4 /*
5 NAME
6 pyieelin.pkb -- procedure IE Tax Details
7 --
8 DESCRIPTION
9 this procedure is used by to retrieve Element Information.
10
11 MODIFIED (DD-MON-YYYY)
12 ILeath 10-NOV-2001 - Initial Version.
13 aashokan 17-DEC-2004 Bug 4069789
14 aashokan 23-DEC-2004 Bug 4083856
15 sgajula 11-DEC-2006 Bug 5696117
16 */
17
18 -------------------------------------------------------------------------
19 --
20 FUNCTION get_tax_details(p_run_assignment_action_id number,
21 p_input_value_id number,
22 p_date_earned varchar2)
23 RETURN varchar2
24 IS
25 --
26 -- Retrieve the details via the element entry values table
27 --
28 cursor element_type_value is
29 SELECT peev.screen_entry_value,
30 pee.updating_action_id
31 FROM pay_element_entry_values_f peev,
32 pay_element_entries_f pee,
33 pay_assignment_actions paa
34 WHERE pee.element_entry_id = peev.element_entry_id
35 AND pee.assignment_id = paa.assignment_id
36 AND paa.assignment_action_id = p_run_assignment_action_id
37 AND peev.input_value_id +0 = p_input_value_id
38 AND to_date(p_date_earned, 'YYYY/MM/DD')
39 BETWEEN
40 pee.effective_start_date
41 AND pee.effective_end_date
42 AND to_date(p_date_earned, 'YYYY/MM/DD')
43 BETWEEN
44 peev.effective_start_date
45 AND peev.effective_end_date;
46 --
47 -- Retrieve the details via the run result
48 --
49 cursor result_type_value is
50 SELECT result_value
51 FROM pay_run_result_values prr,
52 pay_run_results pr,
53 pay_element_types_f pet,
54 pay_input_values_f piv
55 WHERE pr.assignment_action_id = p_run_assignment_action_id
56 and pr.element_type_id = pet.element_type_id
57 and pr.run_result_id = prr.run_result_id
58 and prr.input_value_id = piv.input_value_id
59 and pet.element_type_id = piv.element_type_id
60 and piv.input_value_id = p_input_value_id
61 and piv.business_group_id IS NULL
62 and piv.legislation_code = 'IE'
63 and to_date(p_date_earned, 'YYYY/MM/DD')
64 between piv.effective_start_date
65 and piv.effective_end_date
66 and to_date(p_date_earned, 'YYYY/MM/DD')
67 between pet.effective_start_date
68 and pet.effective_end_date;
69 --
70 l_legislation_code varchar2(30) := 'IE';
71 pay_result_value varchar2 (60);
72 error_string varchar2 (60);
73 l_el_pay_result_value varchar2 (60);
74 l_updating_action_id number;
75 --
76 BEGIN
77 --
78 error_string := to_char(p_input_value_id);
79 --
80 open element_type_value;
81 fetch element_type_value into l_el_pay_result_value,
82 l_updating_action_id;
83 close element_type_value;
84 --
85 -- Check to see whether the element entry value is update recurring
86 --
87 if l_updating_action_id is null then
88 --
89 -- Not update recurring, so select from Run Results
90 --
91 open result_type_value;
92 fetch result_type_value into pay_result_value;
93 close result_type_value;
94 --
95 if pay_result_value is null then
96 --
97 -- No R.R. val, so use the value retrieved by the element.
98 --
99 pay_result_value := l_el_pay_result_value;
100 --
101 end if;
102 --
103 else
104 --
105 -- E.E. Value is update recurring, use the value
106 --
107 pay_result_value := l_el_pay_result_value;
108 --
109 end if;
110 --
111 return pay_result_value;
112 --
113 EXCEPTION
114 WHEN NO_DATA_FOUND THEN
115 pay_result_value := NULL;
116 hr_utility.trace('TEST pay_result_value : NULL ');
117 return pay_result_value;
118 --
119 END get_tax_details;
120 --
121 -------------------------------------------------------------------------
122
123 /*FUNCTION get_parameter accepts payroll_action_id and returns parameter values
124 from legislative parameters in pay_payroll_actions */
125 --
126 FUNCTION get_parameter(p_payroll_action_id NUMBER,
127 p_token_name VARCHAR2) RETURN VARCHAR2 AS
128
129 CURSOR csr_parameter_info(p_pact_id IN NUMBER) IS
130 SELECT legislative_parameters
131 FROM pay_payroll_actions
132 WHERE payroll_action_id = p_pact_id;
133
134 l_token_value VARCHAR2(50);
135 l_parameter pay_payroll_actions.legislative_parameters%TYPE := NULL;
136 l_delimiter varchar2(1):=' ';
137 l_start_pos NUMBER;
138 --
139
140 BEGIN
141 --
142 hr_utility.set_location('p_token_name = ' || p_token_name,20);
143 OPEN csr_parameter_info(p_payroll_action_id);
144 FETCH csr_parameter_info INTO l_parameter;
145 CLOSE csr_parameter_info;
146 l_start_pos := instr(' '||l_parameter,l_delimiter||p_token_name||'=');
147 IF l_start_pos = 0 THEN
148 l_delimiter := '|';
149 l_start_pos := instr(' '||l_parameter,l_delimiter||p_token_name||'=');
150 end if;
151 IF l_start_pos <> 0 THEN
152 l_start_pos := l_start_pos + length(p_token_name||'=');
153 l_token_value := substr(l_parameter,
154 l_start_pos,
155 instr(l_parameter||' ',
156 l_delimiter,l_start_pos)
157 - l_start_pos);
158 end if;
159
160 --
161 l_token_value := trim(l_token_value);
162 --
163 hr_utility.set_location('l_token_value = ' || l_token_value,20);
164 hr_utility.set_location('Leaving ' || 'get_parameters',30);
165
166 RETURN l_token_value;
167
168 END get_parameter;
169
170
171 PROCEDURE get_parameters(p_payroll_action_id IN NUMBER,
172 p_token_name IN VARCHAR2,
173 p_token_value OUT NOCOPY VARCHAR2) IS
174 CURSOR csr_parameter_info(p_pact_id NUMBER,
175 p_token CHAR) IS
176 SELECT SUBSTR(legislative_parameters,
177 INSTR(legislative_parameters,p_token)+(LENGTH(p_token)+1),
178 INSTR(legislative_parameters,' ',
179 INSTR(legislative_parameters,p_token))
180 - (INSTR(legislative_parameters,p_token)+LENGTH(p_token))),
181 business_group_id
182 FROM pay_payroll_actions
183 WHERE payroll_action_id = p_pact_id;
184 l_business_group_id VARCHAR2(20);
185 l_token_value VARCHAR2(50);
186 BEGIN
187 hr_utility.set_location('p_token_name = ' || p_token_name,20);
188 OPEN csr_parameter_info(p_payroll_action_id,p_token_name);
189 FETCH csr_parameter_info INTO l_token_value,l_business_group_id;
190 CLOSE csr_parameter_info;
191 p_token_value := trim(l_token_value);
192 hr_utility.set_location('l_token_value = ' || l_token_value,20);
193 hr_utility.set_location('Leaving ' || 'get_parameters',30);
194 END get_parameters;
195 --------------------------------------------------------------------------------+
196 --Function get_paypathid is used to fetch Paypath ids for a Consolidation Set
197 --------------------------------------------------------------------------------+
198 -- Bug No 3060464 Start
199 -- Bug 5696117 cached the paypathid to improve the performace
200 FUNCTION get_paypathid return varchar2 as
201 --Cursor to fetch paypath ids for all payrolls within a consolidation set
202 CURSOR CSR_PAYROLLS
203 IS
204 SELECT count(distinct org_information8) paycount,
205 org_information8
206 FROM pay_all_payrolls_f papf
207 , hr_organization_information org
208 , hr_soft_coding_keyflex sck
209 WHERE
210 papf.consolidation_set_id = pay_magtape_generic.get_parameter_value('CONSOLIDATION_SET_ID')
211 and to_date(pay_magtape_generic.get_parameter_value('EFFECTIVE_DATE'),'YYYY/MM/DD HH24:MI:SS')
212 between papf.effective_start_date and papf.effective_end_date
213 and org.org_information_context = 'IE_PAYPATH_INFORMATION'
214 and papf.SOFT_CODING_KEYFLEX_ID = sck.SOFT_CODING_KEYFLEX_ID
215 and org.ORG_INFORMATION_ID = to_number(sck.segment2)
216 and org.org_information8 is not null
217 group by org_information8;
218 --Cursor to fetch first paypath id defined at BG Level
219 CURSOR CSR_BG_PAYPATH
220 IS
221 SELECT org.org_information8
222 FROM hr_organization_information org
223 , pay_payroll_actions ppa
224 WHERE
225 ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
226 and org.organization_id = ppa.business_group_id
227 and org.org_information_context = 'IE_PAYPATH_INFORMATION'
228 and rownum=1;
229 --Cursor to fetch paypath id for a specified payroll parameter
230 CURSOR CSR_PAYROLL_PAYPATH
231 IS
232 SELECT org.org_information8
233 FROM hr_organization_information org
234 WHERE
235 org.org_information_context = 'IE_PAYPATH_INFORMATION'
236 and org.ORG_INFORMATION_ID =
237 (SELECT to_number(segment2)
238 FROM
239 hr_soft_coding_keyflex sck
240 , pay_all_payrolls_f papf
241 , pay_payroll_actions ppa
242 WHERE
243 papf.SOFT_CODING_KEYFLEX_ID = sck.SOFT_CODING_KEYFLEX_ID
244 and papf.payroll_id = pay_magtape_generic.get_parameter_value('PAYROLL_ID')
245 and papf.consolidation_set_id = pay_magtape_generic.get_parameter_value('CONSOLIDATION_SET_ID')
246 and ppa.effective_date between papf.effective_start_date and papf.effective_end_date
247 and ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
248 and papf.payroll_id=ppa.payroll_id);
249 l_paypath_ids csr_payrolls%rowtype;
250 l_bg_pathid csr_bg_paypath%rowtype;
251 l_payroll_pathid csr_payroll_paypath%rowtype;
252 cnt number :=0;
253 e_submit_error exception;
254 l_paypathid varchar2(150);
255 BEGIN
256 --Only consolidation set specified as parameter in IE PayPath Process
257 IF pay_magtape_generic.get_parameter_value('PAYROLL_ID') is null then
258 BEGIN
259 IF (g_consolidation_set_id is null) then
260 OPEN csr_payrolls;
261 LOOP
262 FETCH csr_payrolls into l_paypath_ids;
263 EXIT when csr_payrolls%NOTFOUND;
264 l_paypathid:=l_paypath_ids.org_information8;
265 cnt:=cnt+1;
266 --PayPath process errors when a consolidation set has multiple payrolls, which in turn have multiple Paypath ID's
267 if cnt>=2 then
268 raise e_submit_error;
269 end if;
270 g_paypathid := l_paypathid;
271 END LOOP;
272 g_consolidation_set_id := pay_magtape_generic.get_parameter_value('CONSOLIDATION_SET_ID');
273 END IF;
274 --If no Paypath ids are specified at payroll level,Paypath id defined at BG level is picked up.
275 if cnt=0 then
276 IF (g_payroll_action_id is null) then
277 OPEN CSR_BG_PAYPATH;
278 FETCH CSR_BG_PAYPATH into l_bg_pathid;
279 --If no paypath ids defined at BG level and payroll level
280 IF CSR_BG_PAYPATH%NOTFOUND THEN
281 l_paypathid:= ' ';
282 g_paypathid := l_paypathid;
283 return l_paypathid;
284 END IF;
285 CLOSE CSR_BG_PAYPATH;
286 g_paypathid := l_bg_pathid.org_information8;
287 g_payroll_action_id := pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
288 return g_paypathid;
289 END IF;
290 end if;
291 --If payrolls in the consolidation set all have the same paypath id, then that paypath id is picked up.
292 return g_paypathid;
293 EXCEPTION when e_submit_error then
294 l_paypathid:='Error';
295 g_paypathid := l_paypathid ;
296 return g_paypathid;
297 END;
298 ELSE
299 --Payroll name as well as the consolidation set specified as parameter then ,select paypath id specified at the payroll level
300 IF (g_payroll_id is null or g_consolidation_set_id is null) then
301 OPEN CSR_PAYROLL_PAYPATH;
302 FETCH CSR_PAYROLL_PAYPATH into l_payroll_pathid;
303 --Bug No 3086034 Start
304 IF CSR_PAYROLL_PAYPATH%NOTFOUND THEN
305 --Payroll name specified as a parameter but ,no paypath id defined for that payroll,hence it picks up
306 --paypath id defined at BG Level
307 OPEN CSR_BG_PAYPATH;
308 FETCH CSR_BG_PAYPATH into l_bg_pathid;
309 CLOSE CSR_BG_PAYPATH;
310 g_paypathid := l_bg_pathid.org_information8;
311 return g_paypathid;
312 END IF;
313 --Bug No 3086034 End
314 CLOSE CSR_PAYROLL_PAYPATH;
315 g_paypathid := l_payroll_pathid.org_information8;
316 g_payroll_id := pay_magtape_generic.get_parameter_value('PAYROLL_ID');
317 g_consolidation_set_id := pay_magtape_generic.get_parameter_value('CONSOLIDATION_SET_ID');
318 return g_paypathid ;
319 END IF;
320 END IF;
321 return g_paypathid;
322 END get_paypathid;
323 -- Bug No 3060464 End
324
325 END PAY_IE_ARCHIVE_DETAIL_PKG;
[Home] [Help]