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