1 PACKAGE BODY PAY_IE_PAYROLL_ACTIONS_PKG AS
2 /* $Header: pyiesoe.pkb 120.2 2006/09/29 12:39:11 rbhardwa noship $ */
3 /*
4 **
5 ** Copyright (C) 1999 Oracle Corporation
6 ** All Rights Reserved
7 **
8 ** SOE package
9 **
10 ** Change List
11 ** ===========
12 **
13 ** Date Author Reference Description
14 ** -----------+--------+---------+-------------
15 ** 06 NOV 2001 kavenkat N/A Created
16 ** 05 DEC 2001 gpadmasa N/A Added dbdrv Commands
17 ** 10 JAN 2002 gpadmasa N/A Modified the Fetch_Action-Id Procedures
18 ** to handle Iterative Engine Run Results.
19 ** 12 DEC 2002 viviswan 2665701 Performance changes/nocopy changes.
20 ** 13 OCT 2004 mseshadr 3922415 Modified and added new cursors so that
21 ** SOE retrieves latest run,prepayments
22 ** quickpay and quickpay prepayments
23 ** 21 OCT 2004 mseshadr 3922415- Modified cur_assignment_action_id
24 ** 21 FEB 2006 sgajula 4771780 replaced cur_old with cur_lat_action
25 ** and cur_pact_details
26 ** 29 SEP 2006 rbhardwa 5574503 Modified fetch_action_id to return the
27 ** p_assignment_action_id for prepayments.
28 **
29 -------------------------------------------------------------------------------
30 */
31 procedure fetch_action_id (p_session_date in date,
32 p_payroll_exists in out nocopy varchar2,
33 p_assignment_action_id in out nocopy number,
34 p_run_assignment_action_id in out nocopy number,
35 p_paye_prsi_action_id out nocopy number,
36 p_assignment_id in number,
37 p_payroll_action_id in out nocopy number,
38 p_date_earned in out nocopy varchar2) IS
39 -- select the latest prepayments action for this individual and get the
40 -- details of the last run that that action locked
41 /*cursor csr_formula is
42 select
43 to_char(nvl(rppa.date_earned,rppa.effective_date),'YYYY/MM/DD'),
44 rpaa.payroll_action_id,
45 rpaa.assignment_action_id,
46 paa.assignment_action_id
47 from pay_assignment_actions paa,
48 pay_payroll_actions ppa,
49 pay_assignment_actions rpaa,
50 pay_payroll_actions rppa
51 where paa.payroll_action_id = ppa.payroll_action_id
52 and rppa.payroll_action_id = rpaa.payroll_action_id
53 and paa.assignment_action_id =
54 (select
55 to_number(substr(max(to_char(pa.effective_date,'J')||lpad(aa.assignment_action_id,15,'0')),8))
56 from pay_payroll_actions pa,
57 pay_assignment_actions aa
58 where pa.action_type in ('U','P')
59 and aa.action_status = 'C'
60 and pa.payroll_action_id = aa.payroll_action_id
61 and aa.assignment_id = p_assignment_id
62 and pa.effective_date <= p_session_date)
63 and ppa.action_type in ('P', 'U')
64 and rpaa.assignment_id = p_assignment_id
65 and rpaa.action_sequence =
66 (select max(aa.action_sequence)
67 from pay_assignment_actions aa,
68 pay_action_interlocks loc
69 where loc.locked_action_id = aa.assignment_action_id
70 and aa.source_action_id is null
71 and loc.locking_action_id = paa.assignment_action_id); */
72
73 --csrformula has been replaced by cur_old for bug 3922415
74 -- replaced cur_old with cur_lat_action and cur_pact_details 4771780
75 /* cursor cur_old( curvar2 number)is
76 select
77 to_char(nvl(rppa.date_earned,rppa.effective_date),'YYYY/MM/DD'),
78 rpaa.payroll_action_id,
79 rpaa.assignment_action_id,
80 paa.assignment_action_id
81 from pay_assignment_actions paa,
82 pay_payroll_actions ppa,
83 pay_assignment_actions rpaa,
84 pay_payroll_actions rppa
85 where paa.payroll_action_id = ppa.payroll_action_id
86 and rppa.payroll_action_id = rpaa.payroll_action_id
87 and paa.assignment_action_id = curvar2
88 and ppa.action_type in ('P', 'U')
89 and rpaa.assignment_id = p_assignment_id
90 and rpaa.action_sequence =
91 (select max(aa.action_sequence)
92 from pay_assignment_actions aa,
93 pay_action_interlocks loc
94 where loc.locked_action_id = aa.assignment_action_id
95 and aa.source_action_id is null
96 and loc.locking_action_id = paa.assignment_action_id);
97 */
98 cursor cur_lat_action( curvar2 number)is
99 select fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
100 paa.assignment_action_id),16))
101 from pay_assignment_actions paa,
102 pay_action_interlocks loc
103 where loc.locked_action_id = paa.assignment_action_id
104 and paa.source_action_id IS NULL
105 and loc.locking_action_id = curvar2;
106
107 cursor cur_pact_details(curvar3 number) IS
108 select to_char(nvl(rppa.date_earned,rppa.effective_date),'YYYY/MM/DD'),
109 rpaa.payroll_action_id
110 from pay_assignment_actions rpaa,
111 pay_payroll_actions rppa
112 where rpaa.payroll_action_id = rppa.payroll_action_id
113 and rpaa.assignment_action_id = curvar3;
114
115
116 ---This cursor is used to get the assignment action ids corresponding to the assignment Bug 3922415
117 ---The session date is used to retrieve the last date of the pay period (for the corresponding payroll id)
118
119 ---3922415 - This cursor is modified so that SOE does not display payroll run results when the session date is
120 ---before the date on which the payroll was run.It will display run results corresponding to prev.Payroll Run
121 ---in this case
122 cursor cur_assignment_action_id is
123 select to_number(substr(max(to_char(pa.effective_date,'J')||lpad(aa.assignment_action_id,15,'0')),8))
124 from pay_payroll_actions pa,
125 pay_assignment_actions aa
126 /*3922415- per_time_periods ptp */
127 where pa.action_type in ('Q','R','P','U')
128 and aa.action_status = 'C'
129 /*3922415-and ptp.payroll_id = pa.payroll_id*/
130 and pa.payroll_action_id = aa.payroll_action_id
131 and aa.assignment_id = p_assignment_id
132 /*3922415-and pa.effective_date <=ptp.regular_payment_date*/
133 /*3922415-and p_session_date between ptp.start_date and ptp.end_date;*/
134 and pa.effective_date <= p_session_date;
135
136
137 ---This is used to retrive the actio_type corresponding to the assignment action id found by prev cursor
138
139 cursor cur_action_type(curvar3 number) is
140 select distinct action_type
141 from pay_payroll_actions,pay_assignment_actions
142 where pay_payroll_actions.payroll_action_id = pay_assignment_actions.payroll_action_id
143 and assignment_action_id =curvar3;
144
145
146
147
148 cursor csr_get_stand_run is
149 select pac.assignment_action_id
150 from pay_assignment_actions pac,
151 pay_run_types_f prt
152 where pac.run_type_id = prt.run_type_id
153 -- Added to be driven by index bug 2665701
154 and pac.assignment_id = p_assignment_id
155 and pac.source_action_id = p_run_assignment_action_id
156 and prt.run_method='N';
157
158
159 --
160 l_payroll_exists VARCHAR2(30);
161 l_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
162 l_run_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
163 l_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE;
164 l_date_earned VARCHAR2(30);
165 l_end_date date;
166 l_action_type varchar2(2);
167 --
168 begin
169 --
170 l_payroll_exists := p_payroll_exists;
171 l_assignment_action_id := p_assignment_action_id;
172 l_run_assignment_action_id := p_run_assignment_action_id;
173 l_payroll_action_id := p_payroll_action_id;
174 l_date_earned := p_date_earned;
175
176 --
177 /*before 3922415 open csr_formula;
178 fetch csr_formula into p_date_earned,
179 p_payroll_action_id,
180 p_run_assignment_action_id,
181 p_assignment_action_id;
182
183 if csr_formula%FOUND then
184 p_payroll_exists := 'TRUE';
185 open csr_get_stand_run;
186 fetch csr_get_stand_run into p_paye_prsi_action_id;
187 close csr_get_stand_run;
188 end if;
189 close csr_formula;*/
190
191 open cur_assignment_action_id;
192 fetch cur_assignment_action_id into l_assignment_action_id;
193 close cur_assignment_action_id;
194
195 open cur_action_type(l_assignment_action_id);
196 fetch cur_action_type into l_action_type;
197 close cur_action_type;
198
199 IF l_action_type in ('P','U')
200 THEN
201 -- removed cur_old and split the same into two cursors which avoids non-mergeable view
202 /*
203 open cur_old(l_assignment_action_id);
204 fetch cur_old into p_date_earned,
205 p_payroll_action_id,
206 p_run_assignment_action_id,
207 p_assignment_action_id;
208
209 IF cur_old%FOUND
210 */
211
212
213 p_assignment_action_id := l_assignment_action_id; /* 5574503 */
214 open cur_lat_action(l_assignment_action_id);
215 fetch cur_lat_action into p_run_assignment_action_id;
216
217 IF cur_lat_action%FOUND
218 THEN
219 open cur_pact_details(p_run_assignment_action_id);
220 fetch cur_pact_details into p_date_earned,p_payroll_action_id;
221 close cur_pact_details;
222
223
224 p_payroll_exists := 'TRUE';
225
226 open csr_get_stand_run;
227 fetch csr_get_stand_run into p_paye_prsi_action_id;
228 close csr_get_stand_run;
229 END IF;
230 close cur_lat_action;
231 ELSE
232
233 IF l_action_type in ('Q','R')
234
235 THEN
236 p_assignment_action_id:=l_assignment_action_id;
237 p_payroll_exists := 'TRUE';
238 p_run_assignment_action_id := p_assignment_action_id;
239 p_assignment_action_id := p_run_assignment_action_id;
240 p_paye_prsi_action_id := p_run_assignment_action_id;
241 end if;
242 end if;
243
244
245
246
247 EXCEPTION
248 WHEN OTHERS THEN
249 -- in out
250 l_payroll_exists := p_payroll_exists;
251 l_assignment_action_id := p_assignment_action_id;
252 l_run_assignment_action_id := p_run_assignment_action_id;
253 l_payroll_action_id := p_payroll_action_id;
254 l_date_earned := p_date_earned;
255 -- out
256 p_paye_prsi_action_id := null;
257
258
259 end fetch_action_id;
260
261 procedure fetch_action_id ( p_assignment_action_id in out nocopy number,
262 p_run_assignment_action_id in out nocopy number,
263 p_paye_prsi_action_id out nocopy number,
264 p_assignment_id in out nocopy number) IS
265 -- if the action is a run then return the run details
266 -- if the action is a prepayment return the latest run details locked
267 cursor csr_formula is
268 -- find what type of action this is
269 select pact.action_type , assact.assignment_id
270 from pay_assignment_actions assact,
271 pay_payroll_actions pact
272 where assact.assignment_action_id = p_assignment_action_id
273 and pact.payroll_action_id = assact.payroll_action_id;
274 cursor csr_formula_2 is
275 -- for prepayment action find the latest interlocked run
276 select assact.assignment_action_id
277 from pay_assignment_actions assact,
278 pay_action_interlocks loc
279 where loc.locking_action_id = p_assignment_action_id
280 and assact.assignment_action_id = loc.locked_action_id
281 and assact.source_action_id is null
282 --order by loc.locked_action_id desc ;
283 order by assact.action_sequence desc ;
284 cursor csr_formula_3 is
285 -- for run action check if its been prepaid
286 select assact.assignment_action_id
287 from pay_assignment_actions assact,
288 pay_payroll_actions pact,
289 pay_action_interlocks loc
290 where loc.locked_action_id = p_assignment_action_id
291 and assact.assignment_action_id = loc.locking_action_id
292 and pact.payroll_action_id = assact.payroll_action_id
293 and pact.action_type in ('P','U') /* prepayments only */
294 order by assact.action_sequence desc ;
295 cursor csr_get_stand_run is
296 select pac.assignment_action_id
297 from pay_assignment_actions pac,
298 pay_run_types_f prt
299 where pac.run_type_id = prt.run_type_id
300 -- Added to be driven by index bug 2665701
301 and pac.assignment_id = p_assignment_id
302 and pac.source_action_id = p_run_assignment_action_id
303 and prt.run_method='N';
304 --
305 l_action_type varchar2(1);
306 --
307 l_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
308 l_run_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
309 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
310 --
311 begin
312 --
313 l_assignment_action_id := p_assignment_action_id;
314 l_run_assignment_action_id := p_run_assignment_action_id;
315 l_assignment_id := p_assignment_id;
316 --
317 open csr_formula;
318 fetch csr_formula into l_action_type, p_assignment_id;
319 close csr_formula;
320 --
321 if l_action_type in ('P', 'U') then
322 open csr_formula_2;
323 fetch csr_formula_2 into p_run_assignment_action_id;
324 open csr_get_stand_run;
325 fetch csr_get_stand_run into p_paye_prsi_action_id;
326 close csr_get_stand_run;
327 close csr_formula_2;
328 -- if its a run action it may or may not have been prepaid
329 else
330 p_run_assignment_action_id := p_assignment_action_id;
331 begin
332 open csr_formula_3;
333 fetch csr_formula_3 into p_assignment_action_id;
334 IF csr_formula_3%NOTFOUND then
335 p_assignment_action_id := p_run_assignment_action_id;
336 END IF;
337 close csr_formula_3;
338 p_paye_prsi_action_id := p_run_assignment_action_id;
339 end;
340 end if;
341
342 EXCEPTION
343 WHEN OTHERS THEN
344 -- in out
345 p_assignment_action_id := l_assignment_action_id;
346 p_run_assignment_action_id := l_run_assignment_action_id;
347 p_assignment_id := l_assignment_id;
348 -- out
349 p_paye_prsi_action_id := null;
350
351 END fetch_action_id;
352
353 function business_currency_code
354 (p_business_group_id in hr_organization_units.business_group_id%type)
355 return fnd_currencies.currency_code%type is
356
357 v_currency_code fnd_currencies.currency_code%type;
358
359 /* cursor currency_code
360 (c_business_group_id hr_organization_units.business_group_id%type) is
361 select fcu.currency_code
362 from hr_organization_information hoi,
363 hr_organization_units hou,
364 fnd_currencies fcu
365 where hou.business_group_id = c_business_group_id
366 and hou.organization_id = hoi.organization_id
367 and hoi.org_information_context = 'Business Group Information'
368 and fcu.issuing_territory_code = hoi.org_information9;
369 */
370
371 -- cursor currency_code modified for Performance Fix 2665701
372 cursor currency_code
373 (c_business_group_id hr_organization_units.business_group_id%type) is
374 select /*+ USE_NL(fcu hoi) */
375 fcu.currency_code
376 from hr_organization_information hoi,
377 fnd_currencies fcu
378 where hoi.organization_id = c_business_group_id
379 and hoi.org_information_context = 'Business Group Information'
380 and fcu.issuing_territory_code = hoi.org_information9;
381
382
383 begin
384 open currency_code (p_business_group_id);
385 fetch currency_code into v_currency_code;
386 close currency_code;
387
388 return v_currency_code;
389 end business_currency_code;
390
391 END PAY_IE_PAYROLL_ACTIONS_PKG ;
392
[Home] [Help]