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