[Home] [Help]
PACKAGE BODY: APPS.PAY_ASSIGNMENT_ACTIONS_PKG
Source
1 PACKAGE BODY PAY_ASSIGNMENT_ACTIONS_PKG AS
2 /* $Header: pyasa01t.pkb 120.3.12010000.1 2008/07/27 22:03:38 appldev ship $ */
3 /*
4
5 PRODUCT
6 Oracle*Payroll
7 --
8 NAME
9 pyasa01t.pkh
10 --
11 DESCRIPTION
12 Contains routines used to support the Assignment level windows in the
13 Payroll Process Results window.
14 --
15 MODIFIED (DD-MON-YYYY)
16 dkerr 40.0 02-NOV-1993 Created
17 dkerr 40.4 11-APP-1996 Added get_action_status and
18 get_payment_status to support
19 void payments process.
20 jalloun 30-JUL-1996 Added error handling.
21 dkerr 40.7 05-SEP-1996 Bug 394529 :
22 Test for external manual payments
23 and magnetic tape. Changed cursor
24 and variable names appropriately.
25 sbilling 40.8 30-MAR-1998 Bug 596810 :
26 Added extra case ('A') on
27 action_type filter on cursor
28 get_locking_payments.
29 nbristow 115.2 27-JUN-2000 Changed get_action_status to
30 handle Continuous Calc.
31 nbristow 115.3 12-JUN-2001 Change to get_action_status to
32 handle RetroNotifications.
33 exjones 16-JAN-2002 Added ability to enable/disable
34 get_action_status to improve
35 query performance in PAYWSACT
36 exjones 115.5 03-MAY-2002 Added dbdrv commands
37 M.Reid 115.8 29-MAY-2003 Added get_payment_status_code
38 function for bug 2976050
39 A.Logue 115.6 13-JUN-2003 Added message_line_exists
40 function for 2981945
41 SuSivasu 115.10 16-Sep-2003 Modified get_action_status to
42 call PAY_CC_PROCESS_UTILS.
43 get_asg_act_status.
44 nbristow 115.11 23-MAY-2006 Changed get_payment_status
45 to include the Postal Payment
46 alogue 115.12 22-JAN-2007 Added archive_assignment_start_date
47 and archive_person_start_date.
48 alogue 115.13 29-JAN-2007 Handled future started asgs/pers
49 in above.
50 */
51 --
52 g_action_status_enabled varchar2(1) := 'Y';
53 g_asg_id per_all_assignments_f.assignment_id%type := null;
54 g_asg_eff_date date := null;
55 g_asg_date date := null;
56 g_per_id per_all_people_f.person_id%type := null;
57 g_per_eff_date date := null;
58 g_per_date date := null;
59 --
60 procedure update_row(p_rowid in varchar2,
61 p_action_status in varchar2 ) is
62 begin
63 --
64 update PAY_ASSIGNMENT_ACTIONS
65 set ACTION_STATUS = p_action_status
66 where ROWID = p_rowid;
67 --
68 end update_row;
69 --
70 -------------------------------------------------------------------------------
71 procedure delete_row(p_rowid in varchar2) is
72 --
73 begin
74 --
75 delete from PAY_ASSIGNMENT_ACTIONS
76 where ROWID = p_rowid;
77 --
78 end delete_row;
79 --
80 procedure lock_row (p_rowid in varchar2,
81 p_action_status in varchar2 ) is
82
83 --
84 cursor C is select *
85 from PAY_ASSIGNMENT_ACTIONS
86 where rowid = p_rowid
87 for update of PAYROLL_ACTION_ID NOWAIT ;
88 --
89 rowinfo C%rowtype;
90 --
91 begin
92 --
93 open C;
94 fetch C into rowinfo;
95 close C;
96 --
97 if ( (rowinfo.ACTION_STATUS = p_action_status)
98 or (rowinfo.ACTION_STATUS is null and p_action_status
99 is null ))
100 then
101 return ;
102 else
103 fnd_message.set_name( 'FND' , 'FORM_RECORD_CHANGED');
104 app_exception.raise_exception ;
105 end if;
106 end lock_row;
107 --
108
109 -------------------------------------------------------------------------------
110
111 function get_action_status ( p_assignment_action_id in number,
112 p_action_type in varchar2,
113 p_action_status in varchar2 )
114 return varchar2 is
115 -- Bug 2976915.
116 -- This function now uses the PAY_CC_PROCESS_UTILS.get_asg_act_status
117 -- function to derive its value.
118 -- --
119 -- -- A given assignment action is void if there is a payroll action of type 'D'
120 -- -- locks ( though PAY_ACTION_INTERLOCKS ) the assignment action.
121 -- -- Note that this cursor does not check whether the void assignment action has
122 -- -- a status of complete
123 -- --
124 -- cursor c_is_voided ( p_assignment_action_id in number ) is
125 -- select intloc.locking_action_id
126 -- from pay_assignment_actions assact,
127 -- pay_action_interlocks intloc,
128 -- pay_payroll_actions pact
129 -- where intloc.locked_action_id = p_assignment_action_id
130 -- and intloc.locking_action_id = assact.assignment_action_id
131 -- and assact.payroll_action_id = pact.payroll_action_id
132 -- and pact.action_type = 'D';
133 -- --
134 -- cursor run_modified (p_assignment_action_id in number ) is
135 -- select paa.assignment_action_id
136 -- from
137 -- pay_payroll_actions ppa,
138 -- pay_assignment_actions paa
139 -- where paa.assignment_action_id = p_assignment_action_id
140 -- and paa.payroll_action_id = ppa.payroll_action_id
141 -- and paa.action_status = 'C'
142 -- and exists (select ''
143 -- from pay_process_events ppe
144 -- where ppe.assignment_id = paa.assignment_id
145 -- and ppe.effective_date < ppa.effective_date
146 -- and ppe.change_type in ('GRE', 'DATE_EARNED', 'DATE_PROCESSED')
147 -- and ppe.status <> 'C'
148 -- )
149 -- and not exists (select ''
150 -- from pay_assignment_actions paa1, -- Prepay/Costing
151 -- pay_action_interlocks pai1,
152 -- pay_assignment_actions paa2,-- Payment/Trans GL
153 -- pay_action_interlocks pai2
154 -- where pai1.locked_action_id = paa.assignment_action_id
155 -- and pai1.locking_action_id = paa1.assignment_action_id
156 -- and pai2.locked_action_id = paa1.assignment_action_id
157 -- and pai2.locking_action_id = paa2.assignment_action_id);
158 -- --
159 -- cursor prepay_modified (p_assignment_action_id in number ) is
160 -- select paa.assignment_action_id
161 -- from
162 -- pay_payroll_actions ppa,
163 -- pay_assignment_actions paa
164 -- where paa.assignment_action_id = p_assignment_action_id
165 -- and paa.payroll_action_id = ppa.payroll_action_id
166 -- and paa.action_status = 'C'
167 -- and not exists (select ''
168 -- from pay_assignment_actions paa1, -- Payment/Trans GL
169 -- pay_action_interlocks pai1
170 -- where pai1.locked_action_id = paa.assignment_action_id
171 -- and pai1.locking_action_id = paa1.assignment_action_id)
172 -- and (exists (select ''
173 -- from pay_process_events ppe
174 -- where ppe.assignment_id = paa.assignment_id
175 -- and ppe.effective_date < ppa.effective_date
176 -- and ppe.change_type in ('PAYMENT')
177 -- and ppe.status <> 'C'
178 -- )
179 -- or
180 -- exists (select ''
181 -- from pay_action_interlocks pai,
182 -- pay_assignment_actions paa2,
183 -- pay_payroll_actions ppa2
184 -- where pai.locking_action_id = paa.assignment_action_id
185 -- and pai.locked_action_id = paa2.assignment_action_id
186 -- and paa2.payroll_action_id = ppa2.payroll_action_id
187 -- and ppa2.action_type in ('R','Q')
188 -- and exists (select ''
189 -- from pay_process_events ppe
190 -- where ppe.assignment_id = paa2.assignment_id
191 -- and ppe.effective_date < ppa2.effective_date
192 -- and ppe.change_type in ('GRE', 'DATE_EARNED', 'DATE_PROCESSED')
193 -- and ppe.status <> 'C'
194 -- )
195 -- )
196 -- );
197 -- --
198 -- cursor cost_modified (p_assignment_action_id in number ) is
199 -- select paa.assignment_action_id
200 -- from
201 -- pay_payroll_actions ppa,
202 -- pay_assignment_actions paa
203 -- where paa.assignment_action_id = p_assignment_action_id
204 -- and paa.payroll_action_id = ppa.payroll_action_id
205 -- and paa.action_status = 'C'
206 -- and not exists (select ''
207 -- from pay_assignment_actions paa1, -- Payment/Trans GL
208 -- pay_action_interlocks pai1
209 -- where pai1.locked_action_id = paa.assignment_action_id
210 -- and pai1.locking_action_id = paa1.assignment_action_id)
211 -- and exists (select ''
212 -- from pay_process_events ppe
213 -- where ppe.assignment_id = paa.assignment_id
214 -- and ppe.effective_date < ppa.effective_date
215 -- and ppe.change_type in ('COST_CENTRE')
216 -- and ppe.status <> 'C'
217 -- )
218 -- and exists (select ''
219 -- from pay_action_interlocks pai,
220 -- pay_assignment_actions paa2,
221 -- pay_payroll_actions ppa2
222 -- where pai.locking_action_id = paa.assignment_action_id
223 -- and pai.locked_action_id = paa2.assignment_action_id
224 -- and paa2.payroll_action_id = ppa2.payroll_action_id
225 -- and ppa2.action_type in ('R','Q')
226 -- and exists (select ''
227 -- from pay_process_events ppe
228 -- where ppe.assignment_id = paa2.assignment_id
229 -- and ppe.effective_date < ppa2.effective_date
230 -- and ppe.change_type in ('GRE', 'DATE_EARNED', 'DATE_PROCESSED')
231 -- and ppe.status <> 'C'
232 -- )
233 -- );
234 -- --
235 -- --
236 -- l_return_value hr_lookups.meaning%type ;
237 -- l_dummy_action_id pay_assignment_actions.assignment_action_id%type ;
238 -- ischanged boolean;
239 begin
240 -- --
241 -- if g_action_status_enabled = 'N' then
242 -- return null;
243 -- end if;
244 -- --
245 -- if ( p_action_type in ('R', 'Q')) then
246 -- --
247 -- ischanged := FALSE;
248 -- --
249 -- -- Check Run change.
250 ---- open run_modified( p_assignment_action_id );
251 -- fetch run_modified into l_dummy_action_id ;
252 -- if run_modified%found then
253 -- ischanged := TRUE;
254 -- end if;
255 -- close run_modified ;
256 -- --
257 -- if (ischanged) then
258 -- l_return_value := hr_general.decode_lookup('ACTION_STATUS','MO');
259 -- else
260 -- l_return_value := hr_general.decode_lookup('ACTION_STATUS',p_action_status ) ;
261 -- end if;
262 -- --
263 -- elsif ( p_action_type in ('P', 'U')) then
264 -- --
265 -- ischanged := FALSE;
266 -- --
267 -- -- Check Prepay change.
268 -- open prepay_modified( p_assignment_action_id );
269 -- fetch prepay_modified into l_dummy_action_id ;
270 -- if prepay_modified%found then
271 -- ischanged := TRUE;
272 -- end if;
273 -- close prepay_modified ;
274 -- --
275 -- if (ischanged) then
276 -- l_return_value := hr_general.decode_lookup('ACTION_STATUS','MO');
277 -- else
278 -- l_return_value := hr_general.decode_lookup('ACTION_STATUS',p_action_status ) ;
279 -- end if;
280 -- --
281 -- elsif ( p_action_type = 'C') then
282 -- --
283 -- ischanged := FALSE;
284 -- --
285 -- -- Check Costing change.
286 -- open cost_modified( p_assignment_action_id );
287 -- fetch cost_modified into l_dummy_action_id ;
288 -- if cost_modified%found then
289 -- ischanged := TRUE;
290 -- end if;
291 -- close cost_modified ;
292 -- --
293 -- if (ischanged) then
294 -- l_return_value := hr_general.decode_lookup('ACTION_STATUS','MO');
295 -- else
296 -- l_return_value := hr_general.decode_lookup('ACTION_STATUS',p_action_status ) ;
297 -- end if;
298 -- --
299 -- --
300 -- elsif ( p_action_type = 'H' ) then
301 -- open c_is_voided( p_assignment_action_id ) ;
302 -- fetch c_is_voided into l_dummy_action_id ;
303 -- if c_is_voided%found then
304 -- l_return_value := hr_general.decode_lookup('ACTION_STATUS','V');
305 -- else
306 -- l_return_value := hr_general.decode_lookup('ACTION_STATUS',p_action_status ) ;
307 -- end if;
308 -- close c_is_voided ;
309 -- else
310 -- l_return_value := hr_general.decode_lookup('ACTION_STATUS',p_action_status ) ;
311 -- end if;
312 -- return ( l_return_value ) ;
313 return (PAY_CC_PROCESS_UTILS.get_asg_act_status(p_assignment_action_id,
314 p_action_type,
315 p_action_status));
316 end get_action_status ;
317 -- --
321 end enable_action_status;
318 procedure enable_action_status is
319 begin
320 g_action_status_enabled := 'Y';
322 --
323 procedure disable_action_status is
324 begin
325 g_action_status_enabled := 'N';
326 end disable_action_status;
327 --
328 function action_status_enabled return varchar2 is
329 begin
330 return g_action_status_enabled;
331 end action_status_enabled;
332 --
333 -------------------------------------------------------------------------------
334 function get_payment_status_code ( p_assignment_action_id in number,
335 p_pre_payment_id in number )
336 return varchar2 is
337 --
338 -- This cursor retrieves all completed payments for the given
339 -- assignment action
340 --
341 cursor get_locking_payments ( p_assignment_action_id number,
342 p_pre_payment_id number ) is
343 select aac.assignment_action_id,
344 aac.action_status,
345 pac.action_type
346 from pay_payroll_actions pac,
347 pay_assignment_actions aac,
348 pay_action_interlocks loc
349 where loc.locked_action_id = p_assignment_action_id
350 and loc.locking_action_id = aac.assignment_action_id
351 and aac.pre_payment_id = p_pre_payment_id
352 and aac.action_status = 'C'
353 and pac.payroll_action_id = aac.payroll_action_id
354 and pac.action_type in ('H','E','M','A', 'PP') ;
355 --
356 -- This cursor retrieves a void action which locks a given
357 -- check action.
358 --
359 cursor get_locking_void_action ( p_assignment_action_id number ) is
360 select aac.assignment_action_id
361 from pay_payroll_actions pac,
362 pay_assignment_actions aac,
363 pay_action_interlocks loc
364 where loc.locked_action_id = p_assignment_action_id
365 and loc.locking_action_id = aac.assignment_action_id
366 and aac.payroll_action_id = pac.payroll_action_id
367 and pac.action_type = 'D' ;
368
369 status_code hr_lookups.lookup_code%type ;
370 l_void_assact pay_assignment_actions.assignment_action_id%type ;
371 found_payment boolean := FALSE ;
372 found_non_void_action boolean := FALSE ;
373
374 begin
375
376 for theRow in get_locking_payments( p_assignment_action_id ,
377 p_pre_payment_id ) loop
378 exit when found_non_void_action = TRUE ;
379
380 found_payment := TRUE ;
381
382 --
383 -- Only check actions can be voided
384 --
385 open get_locking_void_action ( theRow.assignment_action_id ) ;
386 fetch get_locking_void_action into l_void_assact ;
387
388 if get_locking_void_action%notfound
389 then
390 found_non_void_action := TRUE ;
391 end if;
392
393 close get_locking_void_action ;
394
395 end loop ;
396
397
398 --
399 -- If there was a check action found then return 'Paid' if a non-voided action
400 -- was found
401 --
402 if ( found_payment = TRUE )
403 then
404
405 if ( found_non_void_action = TRUE ) then
406
407 status_code := 'P' ;
408
409 else
410
411 status_code := 'V' ;
412
413 end if;
414
415 else
416
417 status_code := 'U' ;
418
419 end if;
420
421 return( status_code );
422
423 end get_payment_status_code ;
424
425 function get_payment_status (p_assignment_action_id in number,
426 p_pre_payment_id in number)
427 return varchar2 is
428 begin
429
430 return( hr_general.decode_lookup('PAY_STATUS',
431 get_payment_status_code (p_assignment_action_id, p_pre_payment_id) ) ) ;
432
433 end get_payment_status;
434
435 --
436 -------------------------------------------------------------------------------
437 function message_line_exists (p_assignment_action_id in number)
438 return varchar2 is
439 --
440 l_exists varchar2(1);
441 --
442 begin
443 --
444 begin
445 select 'Y'
446 into l_exists
447 from dual
448 where exists (
449 select null
450 from pay_message_lines pml
451 where pml.source_id = p_assignment_action_id
452 and pml.source_type = 'A');
453 exception
454 when others then
455 l_exists := 'N';
456 end;
457
458 return l_exists;
459 end message_line_exists;
460 --
461 -------------------------------------------------------------------------------
462 function archive_assignment_start_date (p_assignment_id in number,
463 p_effective_date in date)
464 return date is
465 --
466 l_date date := null;
467 --
468 begin
469 --
470 if (g_asg_id is not null and p_assignment_id = g_asg_id
471 and p_effective_date = g_asg_eff_date) then
472 l_date := g_asg_date;
473 else
474
475 select max(asg.effective_start_date)
476 into l_date
477 from per_all_assignments_f asg
478 where asg.assignment_id = p_assignment_id
479 and asg.effective_start_date <= p_effective_date;
480
481 if l_date is null then
482
483 select max(asg.effective_start_date)
484 into l_date
485 from per_all_assignments_f asg
486 where asg.assignment_id = p_assignment_id
487 and asg.effective_start_date >= p_effective_date;
488 end if;
489
490 g_asg_id := p_assignment_id;
491 g_asg_eff_date := p_effective_date;
492 g_asg_date := l_date;
493 end if;
494
495 return l_date;
496 end archive_assignment_start_date;
497 --
498 -------------------------------------------------------------------------------
499 function archive_person_start_date (p_person_id in number,
500 p_effective_date in date)
501 return date is
502 --
503 l_date date := null;
504 --
505 begin
506 --
507 if (g_per_id is not null and p_person_id = g_per_id
508 and p_effective_date = g_per_eff_date) then
509 l_date := g_per_date;
510 else
511 select max(pep.effective_start_date)
512 into l_date
513 from per_all_people_f pep
514 where pep.person_id = p_person_id
515 and pep.effective_start_date <= p_effective_date;
516
517 if l_date is null then
518
519 select max(pep.effective_start_date)
520 into l_date
521 from per_all_people_f pep
522 where pep.person_id = p_person_id
523 and pep.effective_start_date >= p_effective_date;
524 end if;
525
526 g_per_id := p_person_id;
527 g_per_eff_date := p_effective_date;
528 g_per_date := l_date;
529 end if;
530
531 return l_date;
532 end archive_person_start_date;
533 --
534
535 END PAY_ASSIGNMENT_ACTIONS_PKG;