1 PACKAGE BODY PAY_PAYROLL_ACTIONS_PKG AS
2 /* $Header: pypra02t.pkb 120.8.12010000.2 2008/08/06 08:14:26 ubhat ship $
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 p_effective_date between ety.effective_start_date
371 and ety.effective_end_date;
372 --
373 -- Get the archive details for the SQWL
374 -- Note that this has some US specific coding.
375 --
376 cursor get_archiver is
377 select /*+ INDEX (pac PAY_PAYROLL_ACTIONS_PK) */
378 pus.state_name||'-'||pac.report_type||decode(hou.name,
379 NULL, NULL, '-'||hou.name)
380 from pay_us_states pus,
381 hr_organization_units hou,
382 pay_payroll_actions pac,
383 per_business_groups_perf bg
384 where pac.payroll_action_id = p_payroll_action_id
385 and pac.report_qualifier = pus.state_abbrev
386 and pac.report_category is not null
387 and bg.business_group_id = pac.business_group_id
388 and bg.legislation_code in ('US', 'CA')
389 and hou.organization_id(+) = pay_core_utils.get_parameter('TRANSFER_GRE',pac.legislative_parameters)
390 --
391 -- decode(instr(pac.legislative_parameters,
392 -- 'TRANSFER_GRE'),
393 -- 0, -1,
394 -- substr(pac.legislative_parameters,
395 -- instr(pac.legislative_parameters,
396 -- 'TRANSFER_GRE') + 13)
397 -- )
398 union
399 select /*+ INDEX (pac PAY_PAYROLL_ACTIONS_PK) */
400 'Federal-'||pac.report_type||decode(hou.name,
401 NULL, NULL, '-'||hou.name)
402 from hr_all_organization_units hou,
403 pay_payroll_actions pac
404 where pac.payroll_action_id = p_payroll_action_id
405 and pac.report_category is not null
406 and pac.report_qualifier = 'FED'
407 and hou.organization_id(+) = pay_core_utils.get_parameter('TRANSFER_GRE',pac.legislative_parameters);
408 --
409 -- decode(instr(pac.legislative_parameters,
410 -- 'TRANSFER_GRE'),
411 -- 0, -1,
412 -- substr(pac.legislative_parameters,
413 -- instr(pac.legislative_parameters,
414 -- 'TRANSFER_GRE') + 13)
415 -- );
416 --
417 --
418 cursor get_archiver_gu is
419 -- derives the process names for Generic Upgrade Archiver Processes.
420 select pud.name
421 from pay_upgrade_definitions_vl pud,
422 pay_payroll_actions pac
423 where pac.payroll_action_id = p_payroll_action_id
424 and pud.short_name = pay_core_utils.get_parameter('UPG_DEF_NAME',pac.legislative_parameters);
425
426 cursor get_report_type is
427 select pac.report_type
428 from pay_payroll_actions pac
429 where pac.payroll_action_id = p_payroll_action_id;
430
431
432 cursor get_archiver_others is
433 select rfmtl.display_name
434 from pay_payroll_actions pac,
435 pay_report_format_mappings_f rfm,
436 pay_report_format_mappings_tl rfmtl
437 where pac.payroll_action_id = p_payroll_action_id
438 and pac.report_type = rfm.report_type
439 and pac.report_qualifier = rfm.report_qualifier
440 and pac.report_category = rfm.report_category
441 and p_effective_date between rfm.effective_start_date and rfm.effective_end_date
442 and rfm.report_format_mapping_id = rfmtl.report_format_mapping_id
443 and rfmtl.language = USERENV('LANG');
444
445
446 -- In the case of the Void process the payroll action of the assoicated
447 -- ChequeWriter run is not kept on the void payroll action record.
448 -- Instead it has to be retrieved through the interlock records it retrieves.
449 --
450 cursor get_void_chq is
451 select fnd_date.date_to_canonical(pacc.effective_date)||'-'||to_char(pacv.start_cheque_number)
452 ||'-'||to_char(pacv.end_cheque_number)
453 from pay_payroll_actions pacc,
454 pay_payroll_actions pacv
455 where pacv.payroll_action_id = p_payroll_action_id
456 and pacc.payroll_action_id = pacv.target_payroll_action_id ;
457 --
458 cursor batch_names is
459 select pbh.batch_name
460 from pay_batch_headers pbh,
461 pay_payroll_actions ppa
462 where ppa.batch_id = pbh.batch_id
463 and ppa.payroll_action_id = p_payroll_action_id;
464 --
465 begin
466 -- Don't do anything if we've switched off this fetch from the
467 -- form, just return quickly for the view fetch, we'll fill in
468 -- the details manually later (in the POST-QUERY)
469 -- N.B. This means you can't QBE on the action Name
470 if (not p_force) and (not g_server_validate) then
471 RETURN NULL;
472 end if;
473 --
474 if pay_payroll_actions_pkg.get_char_bindvar('ACTION_TYPE') is not null then
475 if ( p_action_type <> pay_payroll_actions_pkg.get_char_bindvar('ACTION_TYPE') ) then
476 RETURN NULL;
477 end if;
478 end if;
479 --
480 if ( p_action_type in ( 'C' , 'P' , 'M' , 'T' , 'H' , 'A', 'CP' ) ) then
481 open get_consolidation_set ;
482 fetch get_consolidation_set into l_status ;
483 close get_consolidation_set ;
484 elsif ( p_action_type = 'R' ) then
485 --
486 if ( p_assignment_set_id is not null ) then
487 open get_assignment_set ;
488 fetch get_assignment_set into l_asset ;
489 close get_assignment_set ;
490 end if;
491
492 if ( p_element_set_id is not null ) then
493 if (PAY_ADHOC_UTILS_PKG.chk_post_r11i = 'Y') then
494 open get_element_set_r12 ;
495 fetch get_element_set_r12 into l_eltset ;
496 close get_element_set_r12 ;
497 else
498 open get_element_set ;
499 fetch get_element_set into l_eltset ;
500 close get_element_set ;
501 end if;
502 end if;
503 l_status := p_display_run_number||'-'||l_asset||'-'||l_eltset ;
504 --
505 elsif ( p_action_type = 'V' ) THEN -- Bug 5892723
506 if ( p_assignment_set_id is not null ) then
507 open get_assignment_set ;
508 fetch get_assignment_set into l_asset ;
509 close get_assignment_set ;
510 l_status := l_asset;
511 else
512 open get_person_name ;
513 fetch get_person_name into l_status, l_element_type_id, l_legislative_parameters;
514 close get_person_name ;
515 end if;
516 elsif ( p_action_type in ( 'Q' , 'E' ) ) then
517 --
518 open get_person_name ;
519 fetch get_person_name into l_status, l_element_type_id, l_legislative_parameters;
520 close get_person_name ;
521 if ( p_action_type = 'Q' ) then
522 l_status := p_display_run_number||'-'||l_status ;
523 end if;
524 --
525 elsif ( p_action_type = 'B' ) then
526 --
527 open get_person_name;
528 fetch get_person_name into l_status, l_element_type_id, l_legislative_parameters;
529 close get_person_name;
530 open get_balance_adjustment ;
531 fetch get_balance_adjustment into l_element_name;
532 close get_balance_adjustment ;
533
534 --bug no. 3665606
535 /* l_status := l_status||'-'||l_element_name; */
536
537 -- Batch Balance Adjustment by PYUGEN
538 if l_element_type_id is not null then
539 if p_assignment_set_id is not null then
540 open get_assignment_set ;
541 fetch get_assignment_set into l_asset ;
542 close get_assignment_set ;
543 l_status := l_asset||'-'||l_element_name;
544 else
545 l_status := l_element_name;
546 end if;
547 elsif l_legislative_parameters is not null then
548 --
549 -- Batch Balance Adjustment by pay_bal_adjust.init_batch
550 --
551 l_status := l_legislative_parameters;
552 else
553 -- Ordinary Balance Adjustment
554 --
555 -- If no batch_name is set for pay_bal_adjust.init_batch procedure in
556 -- batch balance adjustment, v_name will pass through this routine.
557 --
558 l_status := l_status||'-'||l_element_name;
559 end if;
560 --
561 elsif ( p_action_type = 'D' ) then
562 --
563 open get_void_chq ;
564 fetch get_void_chq into l_status ;
565 close get_void_chq ;
566 elsif ( p_action_type = 'X' ) then
567
568 open get_archiver ;
569 fetch get_archiver into l_status ;
570 if get_archiver%notfound then
571 l_status := null;
572 end if;
573 close get_archiver ;
574
575 if l_status is null then
576 open get_report_type;
577 fetch get_report_type into l_report_type ;
578 close get_report_type ;
579
580 if l_report_type = 'GENERIC_UPGRADE' then
581 open get_archiver_gu ;
582 fetch get_archiver_gu into l_status ;
583 if get_archiver_gu%notfound then
584 l_status := null;
585 end if;
586 close get_archiver_gu ;
587 else
588 open get_archiver_others;
589 fetch get_archiver_others into l_status;
590 if get_archiver_others%notfound then
591 l_status := null;
592 end if;
593 close get_archiver_others;
594 end if;
595
596 end if;
597 --
598 elsif ( p_action_type = 'Z' ) then
599
600 open get_purge_phase ;
601 fetch get_purge_phase into l_status ;
602 if get_purge_phase%notfound then
603 l_status := null;
604 end if;
605 close get_purge_phase;
606 --
607 elsif ( p_action_type = 'BEE' ) then
608 --
609 open batch_names;
610 fetch batch_names into l_status;
611 if batch_names%notfound then
612 l_status := null;
613 end if;
614 close batch_names;
615 --
616 else
617 l_status := null ;
618 end if;
619 --
620 --
621 return (l_status) ;
622 --
623 end v_name;
624 -----------------------------------------------------------------------------------
625 procedure set_query_bindvar( p_context_name in varchar2,
626 p_context_value in varchar2 ) is
627 begin
628
629 hr_utility.trace( 'pay_payroll_actions_pkg.set_query_bindvar : '
630 ||p_context_name||'='||p_context_value);
631
632 if ( upper(p_context_name) = 'BUSINESS_GROUP_ID' )
633 then
634 g_business_group_id := to_number(p_context_value) ;
635 elsif ( upper(p_context_name) = 'PAYROLL_ID' )
636 then
637 g_payroll_id := to_number(p_context_value) ;
638 elsif ( upper(p_context_name) = 'PERIOD_DATE_FROM' )
639 then
640 g_period_date_from := to_date(p_context_value,'YYYY/MM/DD');
641 elsif ( upper(p_context_name) = 'PERIOD_DATE_TO' )
642 then
643 g_period_date_to := to_date(p_context_value,'YYYY/MM/DD');
644 elsif ( upper(p_context_name) = 'ACTION_TYPE')
645 then
646 g_action_type := p_context_value;
647 elsif ( upper(p_context_name) = 'SERVER_VALIDATE')
648 then
649 g_server_validate := (p_context_value='Y');
650 else
651 invalid_argument('pay_payroll_actions_pkg.set_query_bindvar',p_context_value);
652 end if;
653
654 end set_query_bindvar ;
655 -----------------------------------------------------------------------------------
656 function get_num_bindvar( p_context_name in varchar2 ) return number is
657 l_return_value number ;
658 begin
659 if ( upper(p_context_name) = 'BUSINESS_GROUP_ID' )
660 then
661 l_return_value := g_business_group_id ;
662 elsif ( upper(p_context_name) = 'PAYROLL_ID' )
663 then
664 l_return_value := g_payroll_id ;
665 else
666 invalid_argument('pay_payroll_actions_pkg.get_num_bindvar',p_context_name);
667 end if;
668
669 return (l_return_value) ;
670
671 end get_num_bindvar ;
672
673 ------------------------------------------------------------------------------------
674 function get_char_bindvar ( p_context_name in varchar2 ) return varchar2 is
675 l_return_value varchar2(60);
676 begin
677 --
678 if ( upper(p_context_name) = 'ACTION_TYPE')
679 then
680 l_return_value := g_action_type ;
681 elsif ( upper(p_context_name) = 'SERVER_VALIDATE')
682 then
683 if g_server_validate then
684 l_return_value := 'Y';
685 else
686 l_return_value :='N';
687 end if;
688 else
689 invalid_argument('pay_payroll_actions_pkg.get_char_bindvar',p_context_name);
690 end if;
691 return (l_return_value) ;
692 --
693 end get_char_bindvar;
694 -----------------------------------------------------------------------------------
695 function get_date_bindvar( p_context_name in varchar2 ) return date is
696 l_return_value date ;
697 begin
698
699 if ( upper(p_context_name) = 'PERIOD_DATE_FROM' )
700 then
701 l_return_value := g_period_date_from ;
702 elsif ( upper(p_context_name) = 'PERIOD_DATE_TO' )
703 then
704 l_return_value := g_period_date_to ;
705 else
706 invalid_argument('pay_payroll_actions_pkg.get_date_bindvar',p_context_name);
707 end if;
708
709 return (l_return_value) ;
710
711 end get_date_bindvar;
712 -----------------------------------------------------------------------------------
713
714 procedure set_where ( p_payroll_id in number,
715 p_date_from in date,
716 p_date_to in date,
717 p_action_type in varchar2,
718 p_server_validate in varchar2 default 'Y' ) is
719 begin
720 set_query_bindvar( 'BUSINESS_GROUP_ID',fnd_profile.value('PER_BUSINESS_GROUP_ID'));
721 set_query_bindvar( 'PAYROLL_ID', to_number(p_payroll_id));
722 set_query_bindvar( 'PERIOD_DATE_FROM', nvl(to_char(p_date_from,'YYYY/MM/DD'),
723 to_char(hr_general.start_of_time,'YYYY/MM/DD')));
724 set_query_bindvar( 'PERIOD_DATE_TO', nvl(to_char(p_date_to,'YYYY/MM/DD'),
725 to_char(hr_general.end_of_time,'YYYY/MM/DD')));
726 set_query_bindvar( 'ACTION_TYPE', p_action_type );
727 set_query_bindvar( 'SERVER_VALIDATE', p_server_validate );
728 end set_where;
729 --
730 procedure set_where ( p_payroll_id in number,
731 p_date_from in date,
732 p_date_to in date,
733 p_action_type in varchar2) is
734 begin
735 set_where(p_payroll_id,p_date_from,p_date_to,p_action_type,'Y');
736 end set_where;
737 -----------------------------------------------------------------------------------
738
739 function latest_balance_exists(p_assignment_action_id in number
740 ,p_defined_balance_id in number) return varchar2 is
741 --
742 l_exists varchar2(1) := 'N';
743 --
744 cursor c_asg_lb_exists is
745 select 'Y'
746 from pay_assignment_latest_balances
747 where assignment_action_id = p_assignment_action_id
748 and defined_balance_id = p_defined_balance_id;
749 --
750 cursor c_per_lb_exists is
751 select 'Y'
752 from pay_person_latest_balances
753 where assignment_action_id = p_assignment_action_id
754 and defined_balance_id = p_defined_balance_id;
755 --
756 cursor c_lb_exists is
757 select 'Y'
758 from pay_latest_balances
759 where assignment_action_id = p_assignment_action_id
760 and defined_balance_id = p_defined_balance_id;
761 begin
762 --
763 open c_asg_lb_exists;
764 fetch c_asg_lb_exists into l_exists;
765 if c_asg_lb_exists%FOUND then
766 --
767 close c_asg_lb_exists;
768 return(l_exists);
769 --
770 else
771 --
772 open c_per_lb_exists;
773 fetch c_per_lb_exists into l_exists;
774 if c_per_lb_exists%FOUND then
775 --
776 close c_per_lb_exists;
777 return(l_exists);
778 --
779 else
780 --
781 open c_lb_exists;
782 fetch c_lb_exists into l_exists;
783 if c_lb_exists%FOUND then
784 --
785 close c_lb_exists;
786 return(l_exists);
787 --
788 else
789 --
790 l_exists := 'N';
791 return(l_exists);
792 --
793 end if;
794 --
795 end if;
796 --
797 end if;
798 --
799 end;
800 ---------------------------------------------------------------------
801
802 function decode_cheque_type ( p_business_group_id number) return varchar2 is
803 --
804 -- returns the correct action type for the cheque writer process depending
805 -- on the current legislation code
806 --
807 cursor csr_cheque_name ( p_bus_grp_id number) is
808 select pli.validation_name
809 from pay_legislative_field_info pli,
810 per_business_groups pbg
811 where pli.legislation_code = pbg.legislation_code
812 and pbg.business_group_id = p_bus_grp_id
813 and pli.rule_type = 'H'
814 and pli.field_name = 'CHEQUE_CHECK';
815 --
816 l_cheque_type varchar2 (30);
817 --
818 begin
819 --
820 -- if the bus grp id passed is the same as the one cached then
821 -- return the cached cheque_type value, otherwise get the new
822 -- cheque_type value
823 --
824 if ( p_business_group_id = g_cached_business_group_id ) then
825 l_cheque_type := g_cached_cheque_type;
826 else
827 open csr_cheque_name ( p_business_group_id );
828 fetch csr_cheque_name into l_cheque_type;
829 close csr_cheque_name;
830 --
831 -- populate new cache values
832 --
833 g_cached_business_group_id := p_business_group_id;
834 g_cached_cheque_type := l_cheque_type;
835 --
836 end if;
837
838 return l_cheque_type;
839 --
840 end decode_cheque_type;
841 ---------------------------------------------------------------------
842 END PAY_PAYROLL_ACTIONS_PKG;
[Home] [Help]