1 PACKAGE BODY PAY_PAYROLL_ACTIONS_PKG AS
2 /* $Header: pypra02t.pkb 120.10 2011/08/26 08:26:17 ranarra noship $
3 --
4 PRODUCT
5 Oracle*Payroll
6 --
7 NAME
8 pypra02t.pkb
9 --
10 DESCRIPTION
11 Contains routines used to support the Payroll Action level window
12 Payroll Process Results form.
13 --
14 MODIFIED (DD-MON-YYYY)
15 dkerr 40.0 02-NOV-1993 Created
16 dkerr 40.5 11-APP-1996 Added header
17 Modified get_status to display details
18 for voided payments process actions.
19 jalloun 30-JUL-1996 Added error handling.
20 dkerr 40.11 18-MAR-1998 Added bind variable routines for
21 bug 643154.
22 nbristow 40.12 26-MAY-1998 Added name value for Archive
23 processes.
24 nbristow 40.13 02-JUN-1998 Now check report category
25 for Archive process.
26 mreid 110.4 10-SEP-1998 Removed show errors.
27 nbristow 110.5 14-SEP-1998 Added GRE for the archiver.
28 mreid 110.6 18-SEP-1998 Fixed truncated lines in
29 set_where procedure.
30 sdoshi 115.8 06-APR-1999 Flexible Dates Conversion
31 mreid 115.9 07-MAR-2000 Changed get_archiver cursor for
32 performance bugfix 1224836
33 mreid 115.11 20-APR-2001 Bugfix 1711873 - added hint to
34 full_name select
35 mreid 115.13 28-JUN-2001 Bugfix 1855543 - rewrote
36 balance adjustment name select.
37 exjones 115.14 14-AUG-2001 Allow the g_server_validate thing
38 to switch off the v_name fetch
39 for performance in PAYWSACT
40 jtomkins 115.15 30-OCT-2001 Added function latest_balance_exists
41 for performance support of
42 pay_balances_v (1509490)
43 kkawol 115.16 02-NOV-2001 v_name procedure altered for purge.
44 Added get_purge_phase.
45 dsaxby 115.17 28-JAN-2002 Added dbdrv commands.
46 jbarker 115.18 06-SEP-2002 Added support for BEE status type in
47 v_name procedure.
48 alogue 115.20 06-JAN-2003 Performance fix to get_balance_adjustment
49 in v_name function. Bug 2653089.
50 mreid 115.21 24-FEB-2003 Bug 2802446 - corrected possible
51 invalid number in US archive
52 retrieval (added Hint)
53 SuSivasu 115.22 04-APR-2003 Fixed the issue in Bug 2802446, where by
54 using pay_core_utils.get_parameter to extract GRE info.
55 JBarker 115.23 11-JUN-2003 Added decode_cheque_type function
56 alogue 115.24 24-FEB-2003 Bug 3166075 - fix v_name procedure for
57 archiver.
58 tvankayl 115.25 29-DEC-2003 Bug 3261430 - v_name procedure
59 modified to return process names
60 for all archiver processes.
61 alogue 115.26 24-JUN-2004 Further Performance fixes to get_person_name
62 and get_balance_adjustment in v_name
63 function. Bug 3720619.
64 adkumar 115.27 30-JUL-2004 Bug No. 3665606. Batch Balance Adjustment process
65 should display <Assignment Set> - <Element Name>
66 becuase the process may have multiple assignment
67 actions.
68 tvankayl 115.28 29-AUG-2005 Bug 4584489. Support for Action Type 'CP'.
69 SuSivasu 115.29 21-OCT-2005 Added support for SERVER_VALIDATION in
70 get_char_bindvar.
71 alogue 115.30 04-JAN-2006 Performance Repository fix to get_archiver
72 cursor.
73 alogue 115.31 28-MAR-2007 Support for single latest balance table in
74 latest_balance_exists. Bug 5956216.
75 alogue 115.32 26-JUL-2007 Bug 6130796 - check within v_name procedure
76 pay_payroll_actions_pkg.get_char_bindvar('ACTION_TYPE')
77 is same as action_type passed in.
78 mshingan 115.33 21-AUG-2007 Bug 6353676 - Translated element set name is used.
79 Cursor get_element_set_name is using pay_element_sets_tl
80 instead of pay_element_sets.
81 mshingan 115.34 21-AUG-2007 Bug 6353676 - changed declaration of variable l_eltset.
82 mshingan 115.35 22-AUG-2007 Bug 6353676 - Translated element set name functionality
83 is available only in r12 and not in 11i.Hence added
84 new cursor for R12.
85 ckesanap 115.36 08-Jul-2008 Bug 5892723 - Modified the v_name() procedure for
86 action_type 'V'. Assignment set is passed as Name for
87 batch reversal process.
88 */
89 --
90 --
91 -- GLOBAL VARIABLES
92 --
93 g_business_group_id number ;
94 g_payroll_id number ;
95 g_period_date_from date ;
96 g_period_date_to date ;
97 g_action_type varchar2(60);
98 g_server_validate boolean;
99 g_cached_business_group_id number; -- used in decode_cheque_type function
100 g_cached_cheque_type varchar2(30); -- used in decode_cheque_type function
101 --
102 -- PRIVATE PROCEDURES
103 --
104 -- To simplify patching. This routine does not require the db patch which gives
105 -- the required purity assertion to raise_application_error. It simply raises a value_error
106 --
107 procedure invalid_argument( p_procedure_name in varchar2,
108 p_parameter_name in varchar2 ) is
109 begin
110 raise value_error ;
111 end invalid_argument;
112 --
113 -- PUBLIC PROCEDURES
114 --
115 procedure update_row(p_rowid in varchar2,
116 p_action_status in varchar2 ) is
117 begin
118 --
119 update PAY_PAYROLL_ACTIONS
120 set ACTION_STATUS = p_action_status
121 where ROWID = p_rowid;
122 --
123 end update_row;
124 --
125 ------------------------------------------------------------------------------------
126 procedure delete_row(p_rowid in varchar2) is
127 --
128 begin
129 --
130 delete from PAY_PAYROLL_ACTIONS
131 where ROWID = p_rowid;
132 --
133 end delete_row;
134 --
135 ------------------------------------------------------------------------------------
136 procedure lock_row (p_rowid in varchar2,
137 p_action_status in varchar2 ) is
138 --
139 --
140 cursor C is select *
141 from PAY_PAYROLL_ACTIONS
142 where rowid = p_rowid
143 for update of PAYROLL_ACTION_ID NOWAIT ;
144 --
145 rowinfo C%rowtype;
146 --
147 begin
148 --
149 open C;
150 fetch C into rowinfo;
151 close C;
152 --
153 if ( (rowinfo.ACTION_STATUS = p_action_status)
154 or (rowinfo.ACTION_STATUS is null and p_action_status
155 is null ))
156 then
157 return ;
158 else
159 fnd_message.set_name( 'FND' , 'FORM_RECORD_CHANGED');
160 app_exception.raise_exception ;
161 end if;
162 end lock_row;
163 --
164 ------------------------------------------------------------------------------------
165 function v_action_status(p_payroll_action_id in number,
166 p_payroll_action_status in varchar2,
167 p_request_id in number)
168 return varchar2 is
169 begin
170 return v_action_status(p_payroll_action_id,
171 p_payroll_action_status,
172 p_request_id,
173 FALSE);
174 end v_action_status;
175 --
176 function v_action_status(p_payroll_action_id in number,
177 p_payroll_action_status in varchar2,
178 p_request_id in number,
179 p_force in boolean)
180 return varchar2 is
181 l_status varchar2(80) ;
182 l_dummy number ;
183 --
184 cursor c1 is
185 select 1
186 from pay_assignment_actions
187 where payroll_action_id = p_payroll_action_id
188 and action_status in ('E','M','U');
189 --
190 cursor c2 is
191 select status.meaning
192 from fnd_concurrent_requests r,
193 fnd_lookups status
194 where r.request_id = p_request_id
195 and r.status_code = status.lookup_code
196 and r.phase_code = 'C'
197 and status.lookup_type = 'CP_STATUS_CODE' ;
198 --
199 begin
200 --
201 if (not p_force) and (not g_server_validate) then
202 return hr_general.decode_lookup('ACTION_STATUS',p_payroll_action_status);
203 end if;
204 --
205 if ( p_payroll_action_status = 'C' ) then
206 --
207 open c1 ;
208 fetch c1 into l_dummy ;
209 if c1%found then
210 l_status := hr_general.decode_lookup( 'ACTION_STATUS' , 'I') ;
211 else
212 l_status := hr_general.decode_lookup( 'ACTION_STATUS' , 'C') ;
213 end if ;
214 close c1 ;
215 --
216 elsif ( p_payroll_action_status = 'P' and p_request_id is not null ) then
217 --
218 -- If the Payroll Action is marked as Processing check that the
219 -- concurrent request is not already complete. If it is complete
220 -- then return the request status otherwise decode the 'P' status.
221 --
222 open c2 ;
223 fetch c2 into l_status ;
224 if c2%notfound
225 then
226 l_status := hr_general.decode_lookup('ACTION_STATUS','P');
227 end if;
228 close c2 ;
229 --
230 else
231 --
232 l_status := hr_general.decode_lookup('ACTION_STATUS',p_payroll_action_status ) ;
233 --
234 end if ;
235 --
236 return l_status ;
237 --
238 end v_action_status;
239 --
240 ------------------------------------------------------------------------------------
241 function v_messages_exist(p_payroll_action_id in number) return varchar2 is
242 l_status varchar2(1) ;
243 l_dummy number ;
244 cursor c1 is
245 select 1
246 from pay_message_lines
247 where source_id = p_payroll_action_id
248 and source_type = 'P' ;
249 begin
250 open c1 ;
251 fetch c1 into l_dummy ;
252 if c1%found then
253 l_status := 'Y' ;
254 else
255 l_status := 'N' ;
256 end if ;
257 close c1 ;
258 --
259 return (l_status) ;
260 --
261 end v_messages_exist ;
262 --
263 function v_name(p_payroll_action_id in number,
264 p_action_type in varchar2,
265 p_consolidation_set_id in number,
266 p_display_run_number in number,
267 p_element_set_id in number,
268 p_assignment_set_id in number,
269 p_effective_date in date ) return varchar2 is
270 begin
271 return v_name(
272 p_payroll_action_id,
273 p_action_type,
274 p_consolidation_set_id,
275 p_display_run_number,
276 p_element_set_id,
277 p_assignment_set_id,
278 p_effective_date,
279 FALSE
280 );
281 end v_name;
282 --
283 function v_name(p_payroll_action_id in number,
284 p_action_type in varchar2,
285 p_consolidation_set_id in number,
286 p_display_run_number in number,
287 p_element_set_id in number,
288 p_assignment_set_id in number,
289 p_effective_date in date,
290 p_force in boolean ) return varchar2 is
291 l_status varchar2(2000) ;
292 l_element_name pay_element_types_f_tl.element_name%type;
293 l_asset hr_assignment_sets.assignment_set_name%type ;
294 l_eltset pay_element_sets_tl.element_set_name%type ;
295 l_dummy number ;
296 l_report_type pay_payroll_actions.report_type%type;
297
298 --bug no. 3665606
299 l_element_type_id pay_payroll_actions.element_type_id%type;
300 l_legislative_parameters pay_payroll_actions.legislative_parameters%type;
301
302
303 cursor get_consolidation_set is
304 select consolidation_set_name
305 from pay_consolidation_sets
306 where consolidation_set_id = p_consolidation_set_id ;
307 --
308 --
309 cursor get_element_set is
310 select els.element_set_name
311 from pay_element_sets els
312 where els.element_set_id = p_element_set_id ;
313
314 -- Bug 6353676
315 -- the translated Element Set Name is available in R12 only.
316 cursor get_element_set_r12 is
317 select pes_tl.element_set_name
318 from pay_element_sets_tl pes_tl
319 where pes_tl.element_set_id = p_element_set_id
320 and pes_tl.language = USERENV('LANG');
321 --
322 cursor get_assignment_set is
323 select ast.assignment_set_name
324 from hr_assignment_sets ast
325 where ast.assignment_set_id = p_assignment_set_id;
326 --
327 cursor get_purge_phase is
328 select hr_general.decode_lookup('PURGE_PHASE', to_char(ppa.purge_phase))
329 from pay_payroll_actions ppa
330 where ppa.payroll_action_id = p_payroll_action_id;
331 --
332 cursor get_person_name is
333 select /*+ INDEX
334 (aac PAY_ASSIGNMENT_ACTIONS_N50,
335 peo PER_PEOPLE_F_PK,
336 asg PER_ASSIGNMENTS_F_PK)
337 USE_NL(aac, peo, asg) */
338 peo.full_name
339 ,pac.element_type_id --bug no. 3665606
340 ,pac.legislative_parameters --bug no. 3665606
341 from pay_assignment_actions aac,
342 pay_payroll_actions pac,
343 per_all_people_f peo,
344 per_all_assignments_f asg
345 where pac.payroll_action_id = p_payroll_action_id
346 and aac.payroll_action_id = pac.payroll_action_id
347 and asg.assignment_id = aac.assignment_id
348 and p_effective_date between asg.effective_start_date
349 and asg.effective_end_date
350 and peo.person_id = asg.person_id
351 and p_effective_date between peo.effective_start_date
352 and peo.effective_end_date ;
353 --
354 --
355 cursor get_balance_adjustment is
356 select /*+ ORDERED
357 INDEX(rrs PAY_RUN_RESULTS_N50)
358 USE_NL(rrs)*/
359 tl.element_name
360 from pay_payroll_actions pac,
361 pay_assignment_actions aac,
362 pay_run_results rrs,
363 pay_element_types_f ety,
364 pay_element_types_f_tl tl
365 where pac.payroll_action_id = p_payroll_action_id
366 and aac.payroll_action_id = pac.payroll_action_id
367 and aac.assignment_action_id = rrs.assignment_action_id
368 and rrs.element_type_id = ety.element_type_id
369 and ety.element_type_id = tl.element_type_id
370 and tl.language = USERENV('LANG')
371 and p_effective_date between ety.effective_start_date
372 and ety.effective_end_date;
373 --
374 -- Get the archive details for the SQWL
375 -- Note that this has some US specific coding.
376 --
377 cursor get_archiver is
378 select /*+ INDEX (pac PAY_PAYROLL_ACTIONS_PK) */
379 pus.state_name||'-'||pac.report_type||decode(hou.name,
380 NULL, NULL, '-'||hou.name)
381 from pay_us_states pus,
382 hr_organization_units hou,
383 pay_payroll_actions pac,
384 per_business_groups_perf bg
385 where pac.payroll_action_id = p_payroll_action_id
386 and pac.report_qualifier = pus.state_abbrev
387 and pac.report_category is not null
388 and bg.business_group_id = pac.business_group_id
389 and bg.legislation_code in ('US', 'CA')
390 and hou.organization_id(+) = pay_core_utils.get_parameter('TRANSFER_GRE',pac.legislative_parameters)
391 --
392 -- decode(instr(pac.legislative_parameters,
393 -- 'TRANSFER_GRE'),
394 -- 0, -1,
395 -- substr(pac.legislative_parameters,
396 -- instr(pac.legislative_parameters,
397 -- 'TRANSFER_GRE') + 13)
398 -- )
399 union
400 select /*+ INDEX (pac PAY_PAYROLL_ACTIONS_PK) */
401 'Federal-'||pac.report_type||decode(hou.name,
402 NULL, NULL, '-'||hou.name)
403 from hr_all_organization_units hou,
404 pay_payroll_actions pac
405 where pac.payroll_action_id = p_payroll_action_id
406 and pac.report_category is not null
407 and pac.report_qualifier = 'FED'
408 and hou.organization_id(+) = pay_core_utils.get_parameter('TRANSFER_GRE',pac.legislative_parameters);
409 --
410 -- decode(instr(pac.legislative_parameters,
411 -- 'TRANSFER_GRE'),
412 -- 0, -1,
413 -- substr(pac.legislative_parameters,
414 -- instr(pac.legislative_parameters,
415 -- 'TRANSFER_GRE') + 13)
416 -- );
417 --
418 --
419 cursor get_archiver_gu is
420 -- derives the process names for Generic Upgrade Archiver Processes.
421 select pud.name
422 from pay_upgrade_definitions_vl pud,
423 pay_payroll_actions pac
424 where pac.payroll_action_id = p_payroll_action_id
425 and pud.short_name = pay_core_utils.get_parameter('UPG_DEF_NAME',pac.legislative_parameters);
426
427 cursor get_report_type is
428 select pac.report_type
429 from pay_payroll_actions pac
430 where pac.payroll_action_id = p_payroll_action_id;
431
432
433 cursor get_archiver_others is
434 select rfmtl.display_name
435 from pay_payroll_actions pac,
436 pay_report_format_mappings_f rfm,
437 pay_report_format_mappings_tl rfmtl
438 where pac.payroll_action_id = p_payroll_action_id
439 and pac.report_type = rfm.report_type
440 and pac.report_qualifier = rfm.report_qualifier
441 and pac.report_category = rfm.report_category
442 and p_effective_date between rfm.effective_start_date and rfm.effective_end_date
443 and rfm.report_format_mapping_id = rfmtl.report_format_mapping_id
444 and rfmtl.language = USERENV('LANG');
445
446
447 -- In the case of the Void process the payroll action of the assoicated
448 -- ChequeWriter run is not kept on the void payroll action record.
449 -- Instead it has to be retrieved through the interlock records it retrieves.
450 --
451 cursor get_void_chq is
452 select fnd_date.date_to_canonical(pacc.effective_date)||'-'||to_char(pacv.start_cheque_number)
453 ||'-'||to_char(pacv.end_cheque_number)
454 from pay_payroll_actions pacc,
455 pay_payroll_actions pacv
456 where pacv.payroll_action_id = p_payroll_action_id
457 and pacc.payroll_action_id = pacv.target_payroll_action_id ;
458 --
459 cursor batch_names is
460 select pbh.batch_name
461 from pay_batch_headers pbh,
462 pay_payroll_actions ppa
463 where ppa.batch_id = pbh.batch_id
464 and ppa.payroll_action_id = p_payroll_action_id;
465 --
466 begin
467 -- Don't do anything if we've switched off this fetch from the
468 -- form, just return quickly for the view fetch, we'll fill in
469 -- the details manually later (in the POST-QUERY)
470 -- N.B. This means you can't QBE on the action Name
471 if (not p_force) and (not g_server_validate) then
472 RETURN NULL;
473 end if;
474 --
475 if pay_payroll_actions_pkg.get_char_bindvar('ACTION_TYPE') is not null then
476 if ( p_action_type <> pay_payroll_actions_pkg.get_char_bindvar('ACTION_TYPE') ) then
477 RETURN NULL;
478 end if;
479 end if;
480 --
481 if ( p_action_type in ( 'C' , 'P' , 'M' , 'T' , 'H' , 'A', 'CP' ) ) then
482 open get_consolidation_set ;
483 fetch get_consolidation_set into l_status ;
484 close get_consolidation_set ;
485 elsif ( p_action_type = 'R' ) then
486 --
487 if ( p_assignment_set_id is not null ) then
488 open get_assignment_set ;
489 fetch get_assignment_set into l_asset ;
490 close get_assignment_set ;
491 end if;
492
493 if ( p_element_set_id is not null ) then
494 if (PAY_ADHOC_UTILS_PKG.chk_post_r11i = 'Y') then
495 open get_element_set_r12 ;
496 fetch get_element_set_r12 into l_eltset ;
497 close get_element_set_r12 ;
498 else
499 open get_element_set ;
500 fetch get_element_set into l_eltset ;
501 close get_element_set ;
502 end if;
503 end if;
504 l_status := p_display_run_number||'-'||l_asset||'-'||l_eltset ;
505 --
506 elsif ( p_action_type = 'V' ) THEN -- Bug 5892723
507 if ( p_assignment_set_id is not null ) then
508 open get_assignment_set ;
509 fetch get_assignment_set into l_asset ;
510 close get_assignment_set ;
511 l_status := l_asset;
512 else
513 open get_person_name ;
514 fetch get_person_name into l_status, l_element_type_id, l_legislative_parameters;
515 close get_person_name ;
516 end if;
517 elsif ( p_action_type in ( 'Q' , 'E' ) ) then
518 --
519 open get_person_name ;
520 fetch get_person_name into l_status, l_element_type_id, l_legislative_parameters;
521 close get_person_name ;
522 if ( p_action_type = 'Q' ) then
523 l_status := p_display_run_number||'-'||l_status ;
524 end if;
525 --
526 elsif ( p_action_type = 'B' ) then
527 --
528 open get_person_name;
529 fetch get_person_name into l_status, l_element_type_id, l_legislative_parameters;
530 close get_person_name;
531 open get_balance_adjustment ;
532 fetch get_balance_adjustment into l_element_name;
533 close get_balance_adjustment ;
534
535 --bug no. 3665606
536 /* l_status := l_status||'-'||l_element_name; */
537
538 -- Batch Balance Adjustment by PYUGEN
539 if l_element_type_id is not null then
540 if p_assignment_set_id is not null then
541 open get_assignment_set ;
542 fetch get_assignment_set into l_asset ;
543 close get_assignment_set ;
544 l_status := l_asset||'-'||l_element_name;
545 else
546 l_status := l_element_name;
547 end if;
548 elsif l_legislative_parameters is not null then
549 --
550 -- Batch Balance Adjustment by pay_bal_adjust.init_batch
551 --
552 l_status := l_legislative_parameters;
553 else
554 -- Ordinary Balance Adjustment
555 --
556 -- If no batch_name is set for pay_bal_adjust.init_batch procedure in
557 -- batch balance adjustment, v_name will pass through this routine.
558 --
559 l_status := l_status||'-'||l_element_name;
560 end if;
561 --
562 elsif ( p_action_type = 'D' ) then
563 --
564 open get_void_chq ;
565 fetch get_void_chq into l_status ;
566 close get_void_chq ;
567 elsif ( p_action_type = 'X' ) then
568
569 open get_archiver ;
570 fetch get_archiver into l_status ;
571 if get_archiver%notfound then
572 l_status := null;
573 end if;
574 close get_archiver ;
575
576 if l_status is null then
577 open get_report_type;
578 fetch get_report_type into l_report_type ;
579 close get_report_type ;
580
581 if l_report_type = 'GENERIC_UPGRADE' then
582 open get_archiver_gu ;
583 fetch get_archiver_gu into l_status ;
584 if get_archiver_gu%notfound then
585 l_status := null;
586 end if;
587 close get_archiver_gu ;
588 else
589 open get_archiver_others;
590 fetch get_archiver_others into l_status;
591 if get_archiver_others%notfound then
592 l_status := null;
593 end if;
594 close get_archiver_others;
595 end if;
596
597 end if;
598 --
599 elsif ( p_action_type = 'Z' ) then
600
601 open get_purge_phase ;
602 fetch get_purge_phase into l_status ;
603 if get_purge_phase%notfound then
604 l_status := null;
605 end if;
606 close get_purge_phase;
607 --
608 elsif ( p_action_type = 'BEE' ) then
609 --
610 open batch_names;
611 fetch batch_names into l_status;
612 if batch_names%notfound then
613 l_status := null;
614 end if;
615 close batch_names;
616 --
617 else
618 l_status := null ;
619 end if;
620 --
621 --
622 return (l_status) ;
623 --
624 end v_name;
625 -----------------------------------------------------------------------------------
626 procedure set_query_bindvar( p_context_name in varchar2,
627 p_context_value in varchar2 ) is
628 begin
629
630 hr_utility.trace( 'pay_payroll_actions_pkg.set_query_bindvar : '
631 ||p_context_name||'='||p_context_value);
632
633 if ( upper(p_context_name) = 'BUSINESS_GROUP_ID' )
634 then
635 g_business_group_id := to_number(p_context_value) ;
636 elsif ( upper(p_context_name) = 'PAYROLL_ID' )
637 then
638 g_payroll_id := to_number(p_context_value) ;
639 elsif ( upper(p_context_name) = 'PERIOD_DATE_FROM' )
640 then
641 g_period_date_from := to_date(p_context_value,'YYYY/MM/DD');
642 elsif ( upper(p_context_name) = 'PERIOD_DATE_TO' )
643 then
644 g_period_date_to := to_date(p_context_value,'YYYY/MM/DD');
645 elsif ( upper(p_context_name) = 'ACTION_TYPE')
646 then
647 g_action_type := p_context_value;
648 elsif ( upper(p_context_name) = 'SERVER_VALIDATE')
649 then
650 g_server_validate := (p_context_value='Y');
651 else
652 invalid_argument('pay_payroll_actions_pkg.set_query_bindvar',p_context_value);
653 end if;
654
655 end set_query_bindvar ;
656 -----------------------------------------------------------------------------------
657 function get_num_bindvar( p_context_name in varchar2 ) return number is
658 l_return_value number ;
659 begin
660 if ( upper(p_context_name) = 'BUSINESS_GROUP_ID' )
661 then
662 l_return_value := g_business_group_id ;
663 elsif ( upper(p_context_name) = 'PAYROLL_ID' )
664 then
665 l_return_value := g_payroll_id ;
666 else
667 invalid_argument('pay_payroll_actions_pkg.get_num_bindvar',p_context_name);
668 end if;
669
670 return (l_return_value) ;
671
672 end get_num_bindvar ;
673
674 ------------------------------------------------------------------------------------
675 function get_char_bindvar ( p_context_name in varchar2 ) return varchar2 is
676 l_return_value varchar2(60);
677 begin
678 --
679 if ( upper(p_context_name) = 'ACTION_TYPE')
680 then
681 l_return_value := g_action_type ;
682 elsif ( upper(p_context_name) = 'SERVER_VALIDATE')
683 then
684 if g_server_validate then
685 l_return_value := 'Y';
686 else
687 l_return_value :='N';
688 end if;
689 else
690 invalid_argument('pay_payroll_actions_pkg.get_char_bindvar',p_context_name);
691 end if;
692 return (l_return_value) ;
693 --
694 end get_char_bindvar;
695 -----------------------------------------------------------------------------------
696 function get_date_bindvar( p_context_name in varchar2 ) return date is
697 l_return_value date ;
698 begin
699
700 if ( upper(p_context_name) = 'PERIOD_DATE_FROM' )
701 then
702 l_return_value := g_period_date_from ;
703 elsif ( upper(p_context_name) = 'PERIOD_DATE_TO' )
704 then
705 l_return_value := g_period_date_to ;
706 else
707 invalid_argument('pay_payroll_actions_pkg.get_date_bindvar',p_context_name);
708 end if;
709
710 return (l_return_value) ;
711
712 end get_date_bindvar;
713 -----------------------------------------------------------------------------------
714
715 procedure set_where ( p_payroll_id in number,
716 p_date_from in date,
717 p_date_to in date,
718 p_action_type in varchar2,
719 p_server_validate in varchar2 default 'Y' ) is
720 begin
721 set_query_bindvar( 'BUSINESS_GROUP_ID',fnd_profile.value('PER_BUSINESS_GROUP_ID'));
722 set_query_bindvar( 'PAYROLL_ID', to_number(p_payroll_id));
723 set_query_bindvar( 'PERIOD_DATE_FROM', nvl(to_char(p_date_from,'YYYY/MM/DD'),
724 to_char(hr_general.start_of_time,'YYYY/MM/DD')));
725 set_query_bindvar( 'PERIOD_DATE_TO', nvl(to_char(p_date_to,'YYYY/MM/DD'),
726 to_char(hr_general.end_of_time,'YYYY/MM/DD')));
727 set_query_bindvar( 'ACTION_TYPE', p_action_type );
728 set_query_bindvar( 'SERVER_VALIDATE', p_server_validate );
729 end set_where;
730 --
731 procedure set_where ( p_payroll_id in number,
732 p_date_from in date,
733 p_date_to in date,
734 p_action_type in varchar2) is
735 begin
736 set_where(p_payroll_id,p_date_from,p_date_to,p_action_type,'Y');
737 end set_where;
738 -----------------------------------------------------------------------------------
739
740 function latest_balance_exists(p_assignment_action_id in number
741 ,p_defined_balance_id in number) return varchar2 is
742 --
743 l_exists varchar2(1) := 'N';
744 --
745 cursor c_asg_lb_exists is
746 select 'Y'
747 from pay_assignment_latest_balances
748 where assignment_action_id = p_assignment_action_id
749 and defined_balance_id = p_defined_balance_id;
750 --
751 cursor c_per_lb_exists is
752 select 'Y'
753 from pay_person_latest_balances
754 where assignment_action_id = p_assignment_action_id
755 and defined_balance_id = p_defined_balance_id;
756 --
757 cursor c_lb_exists is
758 select 'Y'
759 from pay_latest_balances
760 where assignment_action_id = p_assignment_action_id
761 and defined_balance_id = p_defined_balance_id;
762 begin
763 --
764 open c_asg_lb_exists;
765 fetch c_asg_lb_exists into l_exists;
766 if c_asg_lb_exists%FOUND then
767 --
768 close c_asg_lb_exists;
769 return(l_exists);
770 --
771 else
772 --
773 open c_per_lb_exists;
774 fetch c_per_lb_exists into l_exists;
775 if c_per_lb_exists%FOUND then
776 --
777 close c_per_lb_exists;
778 return(l_exists);
779 --
780 else
781 --
782 open c_lb_exists;
783 fetch c_lb_exists into l_exists;
784 if c_lb_exists%FOUND then
785 --
786 close c_lb_exists;
787 return(l_exists);
788 --
789 else
790 --
791 l_exists := 'N';
792 return(l_exists);
793 --
794 end if;
795 --
796 end if;
797 --
798 end if;
799 --
800 end;
801 ---------------------------------------------------------------------
802
803 function decode_cheque_type ( p_business_group_id number) return varchar2 is
804 --
805 -- returns the correct action type for the cheque writer process depending
806 -- on the current legislation code
807 --
808 cursor csr_cheque_name ( p_bus_grp_id number) is
809 select pli.validation_name
810 from pay_legislative_field_info pli,
811 per_business_groups pbg
812 where pli.legislation_code = pbg.legislation_code
813 and pbg.business_group_id = p_bus_grp_id
814 and pli.rule_type = 'H'
815 and pli.field_name = 'CHEQUE_CHECK';
816 --
817 l_cheque_type varchar2 (30);
818 --
819 begin
820 --
821 -- if the bus grp id passed is the same as the one cached then
822 -- return the cached cheque_type value, otherwise get the new
823 -- cheque_type value
824 --
825 if ( p_business_group_id = g_cached_business_group_id ) then
826 l_cheque_type := g_cached_cheque_type;
827 else
828 open csr_cheque_name ( p_business_group_id );
829 fetch csr_cheque_name into l_cheque_type;
830 close csr_cheque_name;
831 --
832 -- populate new cache values
833 --
834 g_cached_business_group_id := p_business_group_id;
835 g_cached_cheque_type := l_cheque_type;
836 --
837 end if;
838
839 return l_cheque_type;
840 --
841 end decode_cheque_type;
842 ---------------------------------------------------------------------
843 END PAY_PAYROLL_ACTIONS_PKG;
[Home] [Help]