[Home] [Help]
PACKAGE BODY: APPS.PAY_CA_PAYRG_PKG
Source
1 package body pay_ca_payrg_pkg as
2 /* $Header: pycapreg.pkb 120.4 2007/10/07 08:12:03 amigarg noship $ */
3 /*
4 Copyright (c) Oracle Corporation 1991,1992,1993. All rights reserved
5 --
6 Name :This package defines the cursors needed to run Payroll Register Multi-Threaded
7 --
8 Change List
9 -----------
10 Date Name Vers Description
11 ----------- ---------- ----- -----------------------------------
12 07-OCT-2007 amigarg 115.20 changed the cursor c_actions to add the ro
13 ws for which tax_unit_id is not stamped
14 22-JUN-2005 ssouresr 115.19 Changed the cursor c_actions to not select
15 assignment actions with blank tax_unit_ids
16 also changed c_actions to break dependency
17 between payroll and consolidation set
18 01-SEP-2004 mmukherj 115.18 Added action_status check when joining
19 to pay_payment_information_v. This is done
20 due to changes to view for bug 3826732.
21 13-APR-2004 ssouresr 115.17 Corrected version 115.15 by changing the
22 cursors.
23 13-APR-2004 ssouresr 115.15 The function action_creation is changed
24 so that assignment actions are not created
25 twice for any reversals locked by any
26 prepayments.
27 25-MAR-2004 ssattini 115.14 Changed c_actions cursor to fix
28 11510 bug#3534182, to validate the
29 parameter values correctly.
30 12-JAN-2004 ssattini 115.13 Changed c_actions cursor to fix
31 11510 performance fix bug#3356268.
32 23-MAY-2003 vpandya 115.10 Changed for Multi GRE functionality:
33 action_creation is changed. Please do diff
34 with previous version to see changes.
35 06-MAR-2003 ssattini 115.7 Changed Sort Action query to consider
36 the terminated employees. Fix#2780747.
37 20-NOV-2002 ssouresr 115.6 Changed Organization and Location to caps,
38 because the these two parameters will not
39 be in lower case anymore.
40 29-OCT-2002 tclewis 115.4 Modified the action_creation procedure
41 specifically modifing c_payroll_run cursor
42 to return the max master assignment action id.
43 18-OCT-2002 tclewis 115.3 Modified the action_creation cursor removing
44 the for update of . . . added a for update
45 on the lock the created assignment_action_id.
46 28-AUG-2002 tclewis 115.2 Modified the action creation cursor
47 for the umbrella process and for
48 multiple assignment processing.
49 30-MAR-2001 jgoswami 115.1 Changed package name from
50 pay_payrg_pkg to pay_ca_payrg_pkg
51 as it was conflicting with pypayreg.pkb
52 29-OCT-1999 jgoswami 110.0 Created based on pypayreg.pkb 110.1 99/08/04 rthakur
53 Original file pypayreg.pkb info
54 09-MAR-1999 meshah 40.0 created
55 04-AUG-1999 rmonge 110.1 Made package body adchkdrv compliant.
56
57 --
58 */
59 ----------------------------------- range_cursor ----------------------------------
60 --
61 procedure range_cursor (pactid in number, sqlstr out nocopy varchar2) is
62 l_payroll_id number;
63 leg_param pay_payroll_actions.legislative_parameters%type;
64 --
65 begin
66 select legislative_parameters
67 into leg_param
68 from pay_payroll_actions ppa
69 where ppa.payroll_action_id = pactid;
70
71
72 /* pay reg code */
73
74 sqlstr := 'select distinct asg.person_id
75 from pay_assignment_actions act,
76 per_assignments_f asg,
77 pay_payroll_actions ppa2, /* run and quickpay payroll actions */
78 pay_assignment_actions act2, /* run and quickpay assignment actions */
79 pay_action_interlocks pai, /* interlocks table */
80 pay_payroll_actions ppa, /* PYUGEN information */
81 pay_payroll_actions pa1 /* Payroll Register information */
82 where ppa.payroll_action_id = :payroll_action_id
83 and pa1.consolidation_set_id =
84 nvl(pay_payrg_pkg.get_parameter(''C_ST_ID'',ppa.legislative_parameters),pa1.consolidation_set_id)
85 and pa1.payroll_id =
86 nvl(pay_payrg_pkg.get_parameter(''PY_ID'',ppa.legislative_parameters),pa1.payroll_id)
87 and pa1.effective_date between /* date join btwn payreg and pyugen ppa */
88 ppa.start_date and ppa.effective_date
89 and pa1.payroll_action_id = act.payroll_action_id
90 and asg.assignment_id = act.assignment_id
91 and pa1.effective_date between /* date join btwn payreg and asg */
92 asg.effective_start_date and asg.effective_end_date
93 and pa1.action_type in (''P'',''U'',''V'')
94 and act.action_status = ''C''
95 and act.assignment_action_id = pai.locking_action_id
96 and act2.assignment_action_id = pai.locked_action_id
97 and act2.payroll_action_id = ppa2.payroll_action_id
98 and ppa2.action_type in (''R'',''Q'')
99 and act2.action_status = ''C''
100 and act2.tax_unit_id =
101 nvl(pay_payrg_pkg.get_parameter(''T_U_ID'',ppa.legislative_parameters), act2.tax_unit_id)
102 and asg.organization_id =
103 nvl(pay_payrg_pkg.get_parameter(''O_ID'',ppa.legislative_parameters), asg.organization_id)
104 and asg.location_id =
105 nvl(pay_payrg_pkg.get_parameter(''L_ID'',ppa.legislative_parameters), asg.location_id)
106 and asg.person_id =
107 nvl(pay_payrg_pkg.get_parameter(''P_ID'',ppa.legislative_parameters), asg.person_id)
108 and asg.business_group_id +0 =
109 pay_payrg_pkg.get_parameter(''B_G_ID'',ppa.legislative_parameters)
110 order by asg.person_id';
111
112
113 end range_cursor;
114 ---------------------------------- action_creation ----------------------------------
115 --
116 procedure action_creation(pactid in number,
117 stperson in number,
118 endperson in number,
119 chunk in number) is
120
121 cursor c_actions(pactid number,
122 stperson number,
123 endperson number,
124 cp_cons_set_id number,
125 cp_payroll_id number,
126 cp_bg_id number,
127 cp_tax_unit_id number,
128 cp_org_id number,
129 cp_loc_id number,
130 cp_person_id number) is
131 select act.assignment_action_id,
132 act.assignment_id,
133 act.tax_unit_id,
134 ppa.action_type,
135 ppa.effective_date,
136 act.source_action_id
137 from pay_assignment_actions act,
138 per_assignments_f paf,
139 pay_payroll_actions ppa, /* pre-payments and reversals
140 payroll action id */
141 pay_payroll_actions ppa1, /* PYUGEN payroll action id */
142 pay_all_payrolls_f ppf
143 where ppa1.payroll_action_id = pactid
144 and ((ppf.payroll_id = cp_payroll_id) OR
145 (cp_payroll_id is null))
146 and paf.business_group_id = cp_bg_id
147 and paf.payroll_id = ppf.payroll_id
148 and paf.person_id between stperson and endperson
149 and ((paf.organization_id = cp_org_id) OR
150 (cp_org_id is null))
151 and ((paf.location_id = cp_loc_id) OR
152 (cp_loc_id is null ))
153 and ((paf.person_id = cp_person_id) OR
154 (cp_person_id is null))
155 and ppa.payroll_id = ppf.payroll_id
156 and ppa.consolidation_set_id = cp_cons_set_id
157 and ppa.effective_date between
158 ppa1.start_date and ppa1.effective_date
159 and ppa.effective_date between paf.effective_start_date
160 and paf.effective_end_date
161 and ppa.business_group_id = ppa1.business_group_id
162 and ppa.effective_date between ppf.effective_start_date
163 and ppf.effective_end_date
164 and act.payroll_action_id = ppa.payroll_action_id
165 and paf.assignment_id = act.assignment_id
166 and act.action_status = 'C'
167 and act.source_action_id is null
168 --and ((act.tax_unit_id = cp_tax_unit_id) OR
169 -- (cp_tax_unit_id is null) OR
170 -- (act.tax_unit_id is null))
171 and ( ((act.tax_unit_id = cp_tax_unit_id) and (cp_tax_unit_id is not null))
172 or ((act.tax_unit_id is not null) and (cp_tax_unit_id is null))
173 --changes started for bug 5152897
174 or (act.tax_unit_id is null)
175 -- changes ended for bug 5152897
176 )
177 and ( ( ppa.action_type in ('P','U') and
178 ( exists ( select 1
179 from pay_action_interlocks pai1
180 ,pay_assignment_actions paa1
181 ,pay_payroll_actions ppa2
182 where pai1.locking_action_id = act.assignment_action_id
183 and paa1.assignment_action_id = pai1.locked_action_id
184 and ppa2.payroll_action_id = paa1.payroll_action_id
185 and ppa2.action_type <> 'V' ))) OR
186 ( ppa.action_type = 'V' ) )
187 order by act.assignment_id;
188
189 cursor c_arch_lvl(cp_busi_grp_id number) is
190 select org_information1
191 from hr_organization_information
192 where organization_id = cp_busi_grp_id
193 and org_information_context = 'Payroll Archiver Level';
194
195 cursor c_payment_info(cp_prepay_action_id number) is
196 select assignment_id,
197 tax_unit_id,
198 nvl(source_action_id,-999)
199 from pay_payment_information_v
200 where assignment_action_id = cp_prepay_action_id
201 and action_status = 'C'
202 order by 3,1,2;
203
204 cursor c_sepchk_run_type is
205 select run_type_id
206 from pay_run_types_f
207 where legislation_code = 'CA'
208 and run_method = 'S'
209 and shortname = 'SEP_PAY';
210
211 cursor c_get_map_flag(cp_prepay_action_id number) is
212 select ppf.multi_assignments_flag
213 from pay_assignment_actions paa,
214 pay_payroll_actions ppa,
215 pay_all_payrolls_f ppf
216 where assignment_action_id = cp_prepay_action_id
217 and ppa.payroll_action_id = paa.payroll_action_id
218 and ppf.payroll_id = ppa.payroll_id
219 and ppa.effective_date between ppf.effective_start_date
220 and ppf.effective_end_date;
221
222 cursor c_child_pp_aaid(cp_prepay_action_id number
223 ,cp_assignment_id number
224 ,cp_tax_unit_id number) is
225 select paa.assignment_action_id
226 from pay_assignment_actions paa
227 where paa.source_action_id = cp_prepay_action_id
228 and paa.assignment_id = cp_assignment_id
229 and paa.tax_unit_id = cp_tax_unit_id;
230
231 cursor c_pp_aaid_for_sepchk(cp_source_action_id number) is
232 select paa.assignment_action_id
233 from pay_action_interlocks pai
234 ,pay_assignment_actions paa
235 ,pay_payroll_actions ppa
236 where pai.locked_action_id = cp_source_action_id
237 and paa.assignment_action_id = pai.locking_action_id
238 and paa.source_action_id is not null
239 and ppa.payroll_action_id = paa.payroll_action_id
240 and ppa.action_type in ( 'P', 'U' );
241
242 cursor c_max_run_aaid(cp_prepay_action_id number,
243 cp_assignment_id number,
244 cp_tax_unit_id number,
245 cp_sepchk_run_tp_id number) is
246 select max(paa.assignment_action_id)
247 from pay_assignment_actions paa,
248 pay_action_interlocks pai,
249 pay_run_types_f prt,
250 pay_payroll_actions ppa
251 where pai.locking_action_id = cp_prepay_action_id
252 and paa.assignment_action_id = pai.locked_action_id
253 and paa.assignment_id = cp_assignment_id
254 and paa.tax_unit_id = cp_tax_unit_id
255 and paa.run_type_id <> cp_sepchk_run_tp_id
256 and prt.legislation_code = 'CA'
257 and prt.run_type_id = paa.run_type_id
258 and paa.payroll_action_id = ppa.payroll_action_id
259 and ppa.action_type <> 'V'
260 and prt.run_method <> 'C';
261
262 cursor c_taxgrp_max_run_aaid(cp_prepay_action_id number,
263 cp_assignment_id number,
264 cp_sepchk_run_tp_id number) is
265 select max(paa.assignment_action_id)
266 from pay_assignment_actions paa,
267 pay_action_interlocks pai,
268 pay_run_types_f prt,
269 pay_payroll_actions ppa
270 where pai.locking_action_id = cp_prepay_action_id
271 and paa.assignment_action_id = pai.locked_action_id
272 and paa.assignment_id = cp_assignment_id
273 and paa.run_type_id <> cp_sepchk_run_tp_id
274 and prt.legislation_code = 'CA'
275 and prt.run_type_id = paa.run_type_id
276 and paa.payroll_action_id = ppa.payroll_action_id
277 and ppa.action_type <> 'V'
278 and prt.run_method <> 'C';
279
280 /****************************************************************
281 ** Getting all other elements of different assignments which are
282 ** needed to be printed for
283 ** separate payment when Multi Assignment is enabled.
284 ****************************************************************/
285
286 cursor c_sepchk_act_seq(cp_assignment_action_id number) is
287 select paa.action_sequence
288 from pay_assignment_actions paa
289 where paa.assignment_action_id = cp_assignment_action_id;
290
291 cursor c_other_asg_for_sepchk(cp_prepay_asg_act_id number
292 ,cp_assignment_id number) is
293 select distinct ppi.assignment_id
294 from pay_payment_information_v ppi
295 where ppi.assignment_action_id = cp_prepay_asg_act_id
296 and ppi.assignment_id <> cp_assignment_id
297 and ppi.action_status = 'C'
298 and ppi.source_action_id is null;
299
300 cursor c_multi_asg_max_aaid(cp_prepay_asg_act_id number
301 ,cp_assignment_id number
302 ,cp_action_sequence number) is
303 select paa_run.action_sequence, paa_run.assignment_action_id
304 from pay_action_interlocks pai,
305 pay_assignment_actions paa_run,
306 pay_payroll_actions ppa_run,
307 pay_run_types_f prt
308 where pai.locking_action_id = cp_prepay_asg_act_id
309 and paa_run.assignment_action_id = pai.locked_action_id
310 and paa_run.assignment_id = cp_assignment_id
311 and ppa_run.payroll_action_id = paa_run.payroll_action_id
312 and ppa_run.action_type in ( 'R', 'Q' )
313 and prt.legislation_code = 'CA'
314 and prt.run_type_id = paa_run.run_type_id
315 and prt.run_method <> 'C'
316 and ( ( prt.shortname <> 'SEP_PAY' ) OR
317 ( prt.shortname = 'SEP_PAY' and
318 paa_run.action_sequence <= cp_action_sequence )
319 )
320 order by paa_run.action_sequence desc;
321
322 lockingactid number;
323 lockedactid number;
327 runactid number;
324 assignid number;
325 greid number;
326 num number;
328 actiontype VARCHAR2(1);
329 serialno VARCHAR2(30);
330
331 l_leg_param VARCHAR2(300);
332 l_asg_set_id number;
333 l_asg_flag VARCHAR2(10);
334 l_effective_date date;
335 l_multi_asg_flag VARCHAR(1);
336 l_source_action_id NUMBER;
337
338 ln_pre_pymt_action_id NUMBER;
339
340 ln_master_action_id NUMBER;
341 lv_run_action_type VARCHAR2(1);
342 lv_sep_check VARCHAR2(1);
343 lv_multi_asg_flag VARCHAR2(1);
344 ln_source_action_id NUMBER;
345
346 l_asg_act_id number;
347 l_action_insert varchar2(1);
348 l_void_action varchar2(1);
349
350 ln_busi_grp_id number;
351 lv_pyrl_arch_lvl varchar2(240);
352 ln_pp_tax_unit_id number;
353 ln_pp_aaid number;
354
355 ln_assignment_id number;
356 ln_tax_unit_id number;
357 ln_sepchk_run_tp_id number;
358 ln_max_run_aa_id number;
359
360 prev_assignment_id NUMBER;
361 prev_tax_unit_id NUMBER;
362 prev_source_action_id NUMBER;
363
364 ln_sepchk_act_seq NUMBER;
365 ln_action_sequence NUMBER;
366 ln_map_max_aaid NUMBER;
367
368 ln_leg_payroll_id number(30);
369 ln_leg_cons_set_id number(30);
370 ln_leg_tax_unit_id number(30);
371 ln_leg_org_id number(30);
372 ln_leg_loc_id number(30);
373 ln_leg_person_id number(30);
374 ln_leg_bg_id number(30);
375
376 -- algorithm is quite similar to the other process cases,
377 -- but we have to take into account assignments and
378 -- personal payment methods.
379 BEGIN
380 --hr_utility.trace_on(null,'PAYREG');
381 hr_utility.set_location('procpyr',1);
382
383 prev_assignment_id := 0;
384 prev_tax_unit_id := 0;
385 prev_source_action_id := 0;
386
387 select legislative_parameters, business_group_id,
388 to_number(pay_payrg_pkg.get_parameter('PY_ID', ppa.legislative_parameters)),
389 to_number(pay_payrg_pkg.get_parameter('C_ST_ID', ppa.legislative_parameters)),
390 to_number(pay_payrg_pkg.get_parameter('T_U_ID', ppa.legislative_parameters)),
391 to_number(pay_payrg_pkg.get_parameter('O_ID', ppa.legislative_parameters)),
392 to_number(pay_payrg_pkg.get_parameter('L_ID', ppa.legislative_parameters)),
393 to_number(pay_payrg_pkg.get_parameter('P_ID', ppa.legislative_parameters)),
394 to_number(pay_payrg_pkg.get_parameter('B_G_ID', ppa.legislative_parameters))
395 into l_leg_param, ln_busi_grp_id,
396 ln_leg_payroll_id,
397 ln_leg_cons_set_id,
398 ln_leg_tax_unit_id,
399 ln_leg_org_id,
400 ln_leg_loc_id,
401 ln_leg_person_id,
402 ln_leg_bg_id
403 from pay_payroll_actions ppa
404 where ppa.payroll_action_id = pactid;
405
406 open c_arch_lvl(ln_busi_grp_id);
407 fetch c_arch_lvl into lv_pyrl_arch_lvl;
408 if c_arch_lvl%notfound then
409 lv_pyrl_arch_lvl := 'GRE';
410 end if;
411 close c_arch_lvl;
412
413 hr_utility.trace('lv_pyrl_arch_lvl = '||lv_pyrl_arch_lvl);
414
415 open c_sepchk_run_type;
416 fetch c_sepchk_run_type into ln_sepchk_run_tp_id;
417 close c_sepchk_run_type;
418
419 hr_utility.trace('ln_sepchk_run_tp_id = '||ln_sepchk_run_tp_id);
420
421 open c_actions(pactid,stperson,endperson,
422 ln_leg_cons_set_id,
423 ln_leg_payroll_id,
424 ln_leg_bg_id,
425 ln_leg_tax_unit_id,
426 ln_leg_org_id,
427 ln_leg_loc_id,
428 ln_leg_person_id);
429
430 l_asg_set_id := pay_payrg_pkg.get_parameter('PASID',l_leg_param);
431
432 num := 0;
433 loop
434 hr_utility.set_location('procpyr',2);
435
436 fetch c_actions into lockedactid
437 ,assignid
438 ,greid
439 ,actiontype
440 ,l_effective_date
441 ,l_source_action_id;
442 if c_actions%found then num := num + 1; end if;
443 exit when c_actions%notfound;
444
445 l_asg_flag := 'N';
446 l_action_insert := 'N';
447
448 if l_asg_set_id is not null then
449 l_asg_flag := hr_assignment_set.assignment_in_set(l_asg_set_id,
450 assignid);
451 else -- l_asg_set_id is null
452 l_asg_flag := 'Y';
453 end if;
454
455 ln_pp_aaid := lockedactid;
456
457 prev_assignment_id := 0;
458 prev_tax_unit_id := 0;
459 prev_source_action_id := 0;
460
461 open c_get_map_flag(lockedactid);
462 fetch c_get_map_flag into lv_multi_asg_flag;
463 close c_get_map_flag;
464
468
465 hr_utility.trace('lv_multi_asg_flag = '||lv_multi_asg_flag);
466
467 --if l_asg_flag = 'Y' then removed as no parameter for assignment set
469 if actiontype in ( 'P', 'U' ) then
470
471 open c_payment_info(lockedactid);
472 loop
473 fetch c_payment_info into ln_assignment_id
474 ,ln_tax_unit_id
475 ,ln_source_action_id;
476 exit when c_payment_info%notfound;
477
478 if ln_source_action_id <> -999 then -- Separate Cheque
479
480 lv_sep_check := 'Y';
481
482 open c_pp_aaid_for_sepchk(ln_source_action_id);
483 fetch c_pp_aaid_for_sepchk into ln_pp_aaid;
484 if c_pp_aaid_for_sepchk%notfound then
485 ln_pp_aaid := lockedactid;
486 end if;
487 close c_pp_aaid_for_sepchk;
488
489 ln_max_run_aa_id := ln_source_action_id;
490
491 else -- Normal Cheques
492
493 lv_sep_check := 'N';
494
495 if lv_pyrl_arch_lvl = 'TAXGRP' then
496
497 prev_tax_unit_id := ln_tax_unit_id;
498
499 if prev_assignment_id <> ln_assignment_id then
500
501 -- Get Max Asg Act Id for each assignment of Run
502
503 open c_taxgrp_max_run_aaid(lockedactid,
504 ln_assignment_id,
505 ln_sepchk_run_tp_id);
506 fetch c_taxgrp_max_run_aaid into ln_max_run_aa_id;
507 close c_taxgrp_max_run_aaid;
508
509 if prev_assignment_id <> 0 then
510
511 insert into pay_us_rpt_totals
512 (session_id,
513 tax_unit_id,
514 location_id,
515 value1,
516 value2)
517 values(pactid,
518 pactid,
519 lockingactid,
520 ln_max_run_aa_id,
521 ln_assignment_id);
522
523 end if;
524
525 end if;
526
527 else
528
529 if lv_multi_asg_flag = 'N' then
530 open c_child_pp_aaid(lockedactid,
531 ln_assignment_id,
532 ln_tax_unit_id);
533 fetch c_child_pp_aaid into ln_pp_aaid;
534 if c_child_pp_aaid%notfound then
535 ln_pp_aaid := lockedactid;
536 end if;
537 close c_child_pp_aaid;
538 end if;
539
540 -- Get Max Asg Act Id for each assignment and Tax Unit of Run
541
542 open c_max_run_aaid(lockedactid,
543 ln_assignment_id,
544 ln_tax_unit_id,
545 ln_sepchk_run_tp_id);
546 fetch c_max_run_aaid into ln_max_run_aa_id;
547 close c_max_run_aaid;
548
549 end if;
550
551 end if;
552
553 hr_utility.trace('lockedactid = '||lockedactid);
554 hr_utility.trace('ln_assignment_id = '||ln_assignment_id);
555 hr_utility.trace('ln_pp_aaid = '||ln_pp_aaid);
556 hr_utility.trace('lv_run_action_type = '||lv_run_action_type);
557 hr_utility.trace('lv_sep_check = '||lv_sep_check);
558
559 hr_utility.trace('----------------------------------');
560 hr_utility.trace('prev_tax_unit_id = '||prev_tax_unit_id);
561 hr_utility.trace('ln_tax_unit_id = '||ln_tax_unit_id);
562 hr_utility.trace('prev_source_action_id = '||prev_source_action_id);
563 hr_utility.trace('ln_source_action_id = '||ln_source_action_id);
564 hr_utility.trace('----------------------------------');
565
566 if ( ( ln_source_action_id <> prev_source_action_id ) or
567 ( ln_tax_unit_id <> prev_tax_unit_id ) ) then
568
569 select pay_assignment_actions_s.nextval
570 into lockingactid
571 from dual;
572
573 -- insert the action record.
574 hr_nonrun_asact.insact(lockingactid,
575 ln_assignment_id,
576 pactid,
577 chunk,
578 ln_tax_unit_id);
579
580 -- insert an interlock to this action.
581 hr_nonrun_asact.insint(lockingactid,ln_pp_aaid);
582
583 begin
584
585 serialno := nvl(lv_run_action_type,'R') ||
586 lv_sep_check ||
587 nvl(lv_multi_asg_flag,'N') ||
588 to_char(ln_max_run_aa_id);
589
593 update pay_assignment_actions
590 -- update pay_assignment_actions serial_number with
591 -- runactid.
592
594 set serial_number = serialno
595 where assignment_action_id = lockingactid
596 and tax_unit_id = ln_tax_unit_id;
597
598 exception when others then
599 null;
600 end;
601
602 insert into pay_us_rpt_totals
603 (session_id,
604 tax_unit_id,
605 location_id,
606 value1,
607 value2)
608 values(pactid,
609 pactid,
610 lockingactid,
611 ln_max_run_aa_id,
612 ln_assignment_id);
613
614 hr_utility.trace('if lockingactid = '||lockingactid);
615 hr_utility.trace('if ln_max_run_aa_id= '||ln_max_run_aa_id);
616
617 /*************************************************************
618 ** Getting all other elements of different assignments which
619 ** are needed to be printed for
620 ** separate payment when Multi Assignment is enabled.
621 *************************************************************/
622
623 if nvl(lv_multi_asg_flag,'N') = 'Y' then
624
625 if ln_source_action_id <> -999 then
626 open c_sepchk_act_seq(ln_max_run_aa_id);
627 fetch c_sepchk_act_seq into ln_sepchk_act_seq;
628 close c_sepchk_act_seq;
629
630 hr_utility.trace('ln_sepchk_act_seq= '||ln_sepchk_act_seq);
631 for c_asg in c_other_asg_for_sepchk(lockedactid
632 ,ln_assignment_id)
633 loop
634 hr_utility.trace('c_asg.assignment_id= '||c_asg.assignment_id);
635 open c_multi_asg_max_aaid(lockedactid
636 ,c_asg.assignment_id
637 ,ln_sepchk_act_seq);
638 fetch c_multi_asg_max_aaid into ln_action_sequence
639 ,ln_map_max_aaid;
640 close c_multi_asg_max_aaid;
641
642 hr_utility.trace('ln_action_sequence= '||ln_action_sequence);
643 hr_utility.trace('ln_map_max_aaid= '||ln_map_max_aaid);
644
645 insert into pay_us_rpt_totals
646 (session_id,
647 tax_unit_id,
648 location_id,
649 value1,
650 value2)
651 values(pactid,
652 pactid,
653 lockingactid,
654 ln_map_max_aaid,
655 c_asg.assignment_id);
656 end loop;
657
658 end if; -- ln_source_action_id
659
660 end if; -- lv_multi_asg_flag
661
662 else
663
664 if lv_pyrl_arch_lvl = 'GRE' then
665
666 -- Insert a row in pay_us_rpt_totals which includes
667 -- payroll_action_id,
668 -- report created assignment_action_id (lockingactid)
669 -- and "payroll run" assignment_action id.
670
671 insert into pay_us_rpt_totals
672 (session_id,
673 tax_unit_id,
674 location_id,
675 value1,
679 lockingactid,
676 value2)
677 values(pactid,
678 pactid,
680 ln_max_run_aa_id,
681 ln_assignment_id);
682
683 hr_utility.trace('else lockingactid = '||lockingactid);
684 hr_utility.trace('else ln_max_run_aa_id= '||ln_max_run_aa_id);
685
686 else
687 null;
688 end if;
689
690 end if;
691
692 prev_source_action_id := ln_source_action_id;
693 prev_tax_unit_id := ln_tax_unit_id;
694 prev_assignment_id := ln_assignment_id;
695
696 end loop; -- c_payment_info
697 close c_payment_info;
698 else
699
700 select pay_assignment_actions_s.nextval
701 into lockingactid
702 from dual;
703
704 hr_utility.trace('B4 insact'||to_char(lockingactid) ||','||
705 to_char(greid)||','||actiontype||','||to_char(runactid) );
706
707 -- insert the action record.
708 hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
709 hr_utility.trace('A4 insact'||to_char(lockingactid) ||','||
710 to_char(greid)||','||actiontype||','||to_char(runactid) );
711
712 -- insert an interlock to this action.
713 hr_nonrun_asact.insint(lockingactid,lockedactid);
714
715 begin
716 --serialno := 'V'||to_char(runactid);
717 serialno := actiontype || 'NN' || to_char(lockedactid);
718 -- update pay_assignment_actions serial_number with runactid.
719 update pay_assignment_actions
720 set serial_number = serialno
721 where assignment_action_id = lockingactid
722 and tax_unit_id = greid;
723 exception when others then
724 null;
725 end;
726
727 -- Insert a row in pay_us_rpt_totals which includes
728 -- payroll_action_id,
729 -- report created assignment_action_id (lockingactid)
730 -- and "payroll run" assignment_action id.
731
732 insert into pay_us_rpt_totals
733 (session_id,
734 tax_unit_id,
735 location_id,
736 value1)
737 values(pactid,
738 pactid,
739 lockingactid,
740 lockedactid);
741
742 end if; -- if action_type in ('P', 'U')
743
744 --end if; -- if l_asg_flag = 'Y'
745
746 end loop;
747 close c_actions;
748
749 end action_creation;
750 ---------------------------------- sort_action --------------------------
751 procedure sort_action
752 (
753 payactid in varchar2, /* payroll action id */
754 sqlstr in out nocopy varchar2, /* string holding the sql statement */
755 len out nocopy number /* length of the sql string */
756 ) is
757 begin
758 sqlstr := 'select paa1.rowid /* we need the row id of the assignment actions that are created by PYUGEN */
759 from hr_all_organization_units hou,
760 hr_all_organization_units hou1,
761 hr_locations_all loc,
762 per_all_people_f ppf,
763 per_all_assignments_f paf,
764 pay_assignment_actions paa1, /* PYUGEN assignment action */
765 pay_payroll_actions ppa1 /* PYUGEN payroll action id */
766 where ppa1.payroll_action_id = :pactid
767 and paa1.payroll_action_id = ppa1.payroll_action_id
768 and paa1.assignment_id = paf.assignment_id
769 and paf.effective_start_date =
770 ( select max(paf1.effective_start_date)
771 from per_all_assignments_f paf1
772 where paf1.assignment_id = paf.assignment_id
773 and paf1.effective_start_date <= ppa1.effective_date
774 and paf1.effective_end_date >= ppa1.start_date
775 )
776 and hou1.organization_id = paa1.tax_unit_id
777 and hou.organization_id = paf.organization_id
778 and loc.location_id = paf.location_id
779 and ppf.person_id = paf.person_id
780 and ppa1.effective_date between
781 ppf.effective_start_date and ppf.effective_end_date
782 order by
783 decode(pay_payrg_pkg.get_parameter(''P_S1'',ppa1.legislative_parameters),
784 ''GRE'',hou1.name,
785 ''ORGANIZATION'',hou.name,
786 ''LOCATION'',loc.location_code,null),
787 decode(pay_payrg_pkg.get_parameter(''P_S2'',ppa1.legislative_parameters),
788 ''GRE'',hou1.name,
789 ''ORGANIZATION'',hou.name,
790 ''LOCATION'',loc.location_code,null),
791 decode(pay_payrg_pkg.get_parameter(''P_S3'',ppa1.legislative_parameters),
792 ''GRE'',hou1.name,
793 ''ORGANIZATION'',hou.name,
794 ''LOCATION'',loc.location_code,null),
795 hou.name,ppf.full_name
796 for update of paa1.assignment_action_id';
797
798 len := length(sqlstr); -- return the length of the string.
799 end sort_action;
800 --
801 ------------------------------ get_parameter -------------------------------
802 function get_parameter(name in varchar2,
803 parameter_list varchar2) return varchar2
804 is
805 start_ptr number;
806 end_ptr number;
807 token_val pay_payroll_actions.legislative_parameters%type;
808 par_value pay_payroll_actions.legislative_parameters%type;
809 begin
810 --
811 token_val := name||'=';
812 --
813 start_ptr := instr(parameter_list, token_val) + length(token_val);
814 end_ptr := instr(parameter_list, ' ',start_ptr);
815 --
816 /* if there is no spaces use then length of the string */
817 if end_ptr = 0 then
818 end_ptr := length(parameter_list)+1;
819 end if;
820 --
821 /* Did we find the token */
822 if instr(parameter_list, token_val) = 0 then
823 par_value := NULL;
824 else
825 par_value := substr(parameter_list, start_ptr, end_ptr - start_ptr);
826 end if;
827 --
828 return par_value;
829 --
830 end get_parameter;
831
832 FUNCTION hours_bal_name (p_hours_balance IN NUMBER)
833 RETURN VARCHAR2 IS
834
835 V_BALANCE_NAME VARCHAR2(80);
836 BEGIN
837
838 V_BALANCE_NAME := NULL;
839
840 SELECT balance_name
841 INTO v_balance_name
842 FROM pay_balance_types
843 WHERE balance_type_id = p_hours_balance;
844
845 RETURN v_balance_name;
846
847 END hours_bal_name;
848
849 end pay_ca_payrg_pkg;