1 PACKAGE BODY pay_payrg_pkg AS
2 /* $Header: pypayreg.pkb 120.3 2007/07/05 05:33:27 vmkulkar noship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1992 Oracle Corporation UK Ltd., *
7 * Chertsey, England. *
8 * *
9 * All rights reserved. *
10 * *
11 * This material has been provided pursuant to an agreement *
12 * containing restrictions on its use. The material is also *
13 * protected by copyright law. No part of this material may *
14 * be copied or distributed, transmitted or transcribed, in *
15 * any form or by any means, electronic, mechanical, magnetic, *
16 * manual, or otherwise, or disclosed to third parties without *
17 * the express written permission of Oracle Corporation UK Ltd, *
18 * Oracle Park, Bittams Lane, Guildford Road, Chertsey, Surrey, *
19 * England. *
20 * *
21 ******************************************************************
22
23 Name: This package defines the cursors needed to run
24 Payroll Register Multi-Threaded
25
26 Change List
27 -----------
28 Date Name Vers Description
29 ----------- ---------- ----- -----------------------------------
30 09-MAR-1999 meshah 40.0 created
31 14-AUG-2000 SRAVURI 115.1 modified (addred the assignment
32 set funtionality)
33 16-AUG-2000 ahanda 115.2 Uncommented exit statement and
34 added commit
35 13-APR-2001 ahanda 115.3 Changed sort cursor and formated
36 the file. Changed HR_LOCATIONS to
37 HR_LOCATIONS_ALL.
38 26-apr-2001 tclewis 115.4 modified the cursor(s) in the range_cursor
39 and action creation to use secure views.
40 modified the sql query in the sort_code
41 routine to use base tables.
42 22-AUG-2001 tclewis 115.5 modifed the action creation cursor to
43 work the umbrella process.
44 21-DEC-2001 meshah 115.6 adding dbdrv.
45 24-DEC-2001 meshah 115.7 changed the cursor c_payroll_run to
46 work with the employees that were created
47 before the umbralle process. Also making
48 the action as dynamic.
49 27-DEC-2001 tclewis 115.8 modified the c_payroll_run cursor
50 removing the descending on the order by
51 clause as we want to return the minimum
52 Assignment_action_id first.
53 05-JAN-2002 ahanda 115.9 Modified the action creation cursor to
54 update serial numner with whether it is
55 master or child (sep check) action.
56 20-MAR-2002 tclewis 115.10 Added code to the action creation to
57 handle multi-assignment processing.
58 13-JUN-2002 tclewis 115.14 Modified the actions_creation cursro
59 non multiple assigmnet payroll register
60 to set a temporary flab l_action_insert
61 := 'Y' as to not insert an extra record
62 when the payment cursor returns no data.
63 13-JUN-2002 tclewis 115.15 fixed a bug where we are not exiting the
64 c_payments loop correctly.
65
66 07-AUG-2002 rmonge 115.16 Increase size of action_type to varchar2(30)
67 21-oct-2002 tclewis 115.17 removed the "for Update... " in the action_creation
68 code. Changed the "for update" clause
69 in the sort_cursor to paa.assignment_id from
70 paf.assignment_id
71 19-DEC-2002 tclewis 115.18 added nocopy.
72 27-DEC-2002 meshah 115.19 fixed gscc warning.
73 17-SEP-2003 ardsouza 115.20 modified sort_action procedure to sort based on
74 date paid of 'P','U'& 'V' process(Bug 2641972).
75 26-jan-2004 djoshi 115.22 modified action_creation for bug 3385676
76 We will insert multiple rows for when
77 pre-payment is locking multiple rows.
78 27-jan-2004 djoshi 115.23 Corrected missing exit statement
79 29-jan-2004 djoshi 115.24 the action creation cursor has been
80 changed to make sure we have
81 missing assignment actions
82 also Created
83
84 05-feb-2004 ssmukher 115.25 Bug 3372747: 11.5.10 Performance Changes
85 09-Feb-2004 ssmukher 115.26 Bug 3372747 - Corrected dec for
86 leg_param.
87 16-Feb-2004 djoshi 115.27 Bug 3423464. Regular Not showing up
88 15-Mar-2005 schauhan 115.37 Added Logic for showing Balance Adjustments on report.
89 Bug 4074976.
90 09-May-2006 ppanda 115.38 Bug # 5204333 Fixed
91 lv_max_run_flag which was used in action_creation
92 procedure was not re-initialized after processing
93 the Actions for Balance Adjustments.
94 This variable is initialized with default value N
95 after processing actions in the loop
96 20-Sep-2006 sjawid 115.39 Bug 5366862 fixed
97 i.added date effective join to c_payroll_def.
98 ii.changed the c_payroll_def Open statement to
99 use the EFFECTIVE_DATE from the
100 PRE_PAYMENTS PAYROLL ACTION,
101 not the effective date from the
102 payroll register payroll action.
103 28-jun-2007 vmkulkar 115.40 Created a new cursor c_actions_1
104 Bug 5502369
105
106 */
107
108 --------------------------- range_cursor ---------------------------------
109 PROCEDURE range_cursor (pactid in number,
110 sqlstr out nocopy varchar2) is
111
112 leg_param pay_payroll_actions.legislative_parameters%type;
113
114 l_consolidation_set_id number;
115 l_payroll_id number;
116 l_organization_id number;
117 l_location_id number;
118 l_person_id number;
119 l_leg_start_date date;
120 l_leg_end_date date;
121
122 l_business_group_id number;
123
124 l_payroll_text varchar2(70);
125 l_consolidation_set_text varchar2(50);
126
127 BEGIN
128 select legislative_parameters
129 into leg_param
130 from pay_payroll_actions ppa
131 where ppa.payroll_action_id = pactid;
132
133 select ppa.legislative_parameters,
134 pay_payrg_pkg.get_parameter('C_ST_ID', ppa.legislative_parameters),
135 pay_payrg_pkg.get_parameter('PY_ID', ppa.legislative_parameters),
136 pay_payrg_pkg.get_parameter('O_ID', ppa.legislative_parameters),
137 pay_payrg_pkg.get_parameter('L_ID', ppa.legislative_parameters),
138 pay_payrg_pkg.get_parameter('P_ID', ppa.legislative_parameters),
139 ppa.start_date,
140 ppa.effective_date,
141 ppa.business_group_id
142 into leg_param,
143 l_consolidation_set_id,
144 l_payroll_id,
145 l_organization_id,
146 l_location_id,
147 l_person_id,
148 l_leg_start_date,
149 l_leg_end_date,
150 l_business_group_id
151 from pay_payroll_actions ppa
152 where ppa.payroll_action_id = pactid;
153
154 IF l_consolidation_set_id is not null THEN
155
156 l_consolidation_set_text := 'and pa1.consolidation_set_id = ' || to_char(l_consolidation_set_id) ;
157
158
159 ELSE
160
161 l_consolidation_set_text := NULL;
162
163 END IF;
164
165 IF l_payroll_id is not null THEN
166
167 l_payroll_text := 'and pa1.payroll_id = ' || to_char(l_payroll_id) ;
168
169 ELSE
170
171 l_payroll_text := null;
172
173 /* if l_consolidation_set_id is not null then
174 l_payroll_text := null;
175 else
176 l_payroll_text := 'and pa1.payroll_id in (select payroll_id from pay_payrolls_f)';
177 end if;
178 */
179 END IF;
180
181
182
183
184 sqlstr :=
185 'select distinct asg.person_id
186 from pay_payroll_actions ppa,
187 pay_payroll_actions pa1,
188 pay_assignment_actions act,
189 per_assignments_f asg,
190 pay_payrolls_f ppf
191 where ppa.payroll_action_id = :payroll_action_id
192 '||l_consolidation_set_text||'
193 '||l_payroll_text||'
194 and pa1.effective_date between ppa.start_date
195 and ppa.effective_date
196 and pa1.effective_date between asg.effective_start_date
197 and asg.effective_end_date
198 and pa1.action_type in (''P'',''U'',''V'')
199 and pa1.payroll_action_id = act.payroll_action_id
200 and asg.assignment_id = act.assignment_id
201 and act.action_status = ''C''
202 and asg.organization_id = nvl('''||l_organization_id||''',
203 asg.organization_id)
204 and asg.location_id = nvl('''||l_location_id||''',
205 asg.location_id)
206 and asg.person_id = nvl('''||l_person_id||''',
207 asg.person_id)
208 and asg.business_group_id +0 = ppa.business_group_id
209 and asg.payroll_id = ppf.payroll_id
210 and ppa.effective_date between ppf.effective_start_date and ppf.effective_end_date
211 and ppf.payroll_id >=0
212 order by asg.person_id';
213
214
215 END range_cursor;
216
217
218 ----------------------------- action_creation --------------------------------
219 PROCEDURE action_creation( pactid in number,
220 stperson in number,
221 endperson in number,
222 chunk in number)
223 IS
224
225 cursor c_inputs(pactid number) is -- Bug 3372747
226 select pay_payrg_pkg.get_parameter('PY_ID',ppa.legislative_parameters) payroll_id,
227 pay_payrg_pkg.get_parameter('C_ST_ID',ppa.legislative_parameters) consolidation_set_id,
228 pay_payrg_pkg.get_parameter('T_U_ID',ppa.legislative_parameters) tax_unit_id,
229 pay_payrg_pkg.get_parameter('L_ID',ppa.legislative_parameters) location_id,
230 pay_payrg_pkg.get_parameter('O_ID',ppa.legislative_parameters) organization_id,
231 pay_payrg_pkg.get_parameter('P_ID',ppa.legislative_parameters) person_id,
232 pay_payrg_pkg.get_parameter('B_G_ID',ppa.legislative_parameters) business_group_id,
233 pay_payrg_pkg.get_parameter('PASID',ppa.legislative_parameters) assignment_set_id,
234 ppa.start_date start_date,
235 ppa.effective_date effective_date
236 from pay_payroll_actions ppa
237 where ppa.payroll_action_id = pactid;
238
239 cursor c_actions(
240 c_stperson number,
241 c_endperson number ,
242 c_payroll_id number,
243 c_consolidation_set_id number,
244 c_tax_unit_id number,
245 c_location_id number,
246 c_organization_id number,
247 c_person_id number,
248 c_business_group_id number,
249 c_start_date date,
250 c_effective_date date
251 ) is -- Bug 3372747
252 select /*+ ORDERED */
253 act.assignment_action_id,
254 act.assignment_id,
255 act.tax_unit_id,
256 ppa.action_type,
257 ppa.effective_date,
258 act.source_action_id,
259 nvl(ppa.start_date,ppa.effective_date)
260 from pay_payrolls_f ppf, -- Bug 3372747
261 pay_payroll_actions ppa, /* pre-payments and reversals
262 payroll action id */
263 pay_assignment_actions act,
264 per_assignments_f paf
265 where (c_payroll_id is NULL
266 or ppa.payroll_id = c_payroll_id)
267 and ppa.consolidation_set_id +0 = nvl(c_consolidation_set_id,
268 ppa.consolidation_set_id)
269 and ppa.effective_date >= c_start_date
270 and nvl(ppa.start_date,ppa.effective_date) <= c_effective_date
271 -- decode (ppa.action_type,'P', add_months(c_effective_date,12),
272 -- 'U', add_months(c_effective_date,12),
273 -- 'V', c_effective_date)
274 -- c_effective_date
275 and ppa.action_type in ('P','U','V')
276 and act.action_status = 'C'
277 and act.payroll_action_id = ppa.payroll_action_id
278 and ppa.business_group_id +0 = c_business_group_id
279 and paf.assignment_id = act.assignment_id
280 and (c_tax_unit_id is NULL
281 or act.tax_unit_id = c_tax_unit_id)
282 and (c_organization_id is NULL
283 or paf.organization_id = c_organization_id)
284 and (c_location_id is NULL
285 or paf.location_id = c_location_id)
286 and (c_person_id is NULL
287 or paf.person_id = c_person_id)
288 and paf.person_id between c_stperson and c_endperson
289 and paf.business_group_id +0 = c_business_group_id
290 and ppa.effective_date between paf.effective_start_date
291 and paf.effective_end_date
292 and ppa.payroll_id = ppf.payroll_id -- Bug 3372747
293 and ppa.effective_date between ppf.effective_start_date and ppf.effective_end_date
294 and ppf.payroll_id >= 0
295 order by act.assignment_id;
296
297 -- This cursor will take care of the assignment set id parameter.
298 cursor c_actions_1(
299 c_stperson number,
300 c_endperson number ,
301 c_payroll_id number,
302 c_consolidation_set_id number,
303 c_tax_unit_id number,
304 c_location_id number,
305 c_organization_id number,
306 c_person_id number,
307 c_business_group_id number,
308 c_assignment_set_id number,
309 c_start_date date,
310 c_effective_date date
311 ) is -- Bug 3372747
312 select /*+ ORDERED */
313 act.assignment_action_id,
314 act.assignment_id,
315 act.tax_unit_id,
316 ppa.action_type,
317 ppa.effective_date,
318 act.source_action_id,
319 nvl(ppa.start_date,ppa.effective_date)
320 from
321 HR_ASSIGNMENT_SET_AMENDMENTS HASA ,
322 PER_ASSIGNMENTS_F PAF ,
323 PAY_ASSIGNMENT_ACTIONS ACT ,
324 PAY_PAYROLL_ACTIONS PPA ,
325 PAY_PAYROLLS_F PPF
326 where (c_payroll_id is NULL
327 or ppa.payroll_id = c_payroll_id)
328
329 and ppa.consolidation_set_id +0 = nvl(c_consolidation_set_id,
330 ppa.consolidation_set_id)
334 -- 'U', add_months(c_effective_date,12),
331 and ppa.effective_date >= c_start_date
332 and nvl(ppa.start_date,ppa.effective_date) <= c_effective_date
333 -- decode (ppa.action_type,'P', add_months(c_effective_date,12),
335 -- 'V', c_effective_date)
336 -- c_effective_date
337 and ppa.action_type in ('P','U','V')
338 and act.action_status = 'C'
339 and act.payroll_action_id = ppa.payroll_action_id
340 and ppa.business_group_id +0 = c_business_group_id
341 and paf.assignment_id = act.assignment_id
342 and (c_tax_unit_id is NULL
343 or act.tax_unit_id = c_tax_unit_id)
344 and (c_organization_id is NULL
345 or paf.organization_id = c_organization_id)
346 and (c_location_id is NULL
347 or paf.location_id = c_location_id)
348 and (c_person_id is NULL
349 or paf.person_id = c_person_id)
350 and hasa.assignment_set_id = c_assignment_set_id
351 and hasa.assignment_id = paf.assignment_id
352 and paf.person_id between c_stperson and c_endperson
353 and paf.business_group_id +0 = c_business_group_id
354 and ppa.effective_date between paf.effective_start_date
355 and paf.effective_end_date
356 and ppa.payroll_id = ppf.payroll_id -- Bug 3372747
357 and ppa.effective_date between ppf.effective_start_date and ppf.effective_end_date
358 and ppf.payroll_id >= 0
359 order by act.assignment_id;
360
361
362 /*****************************************************************
363 ** This cursor will get all the source actions for which the
364 ** assignment should get a deposit advice.
365 ** assignment action for each pre-payment (bug 890222) i.e.
366 ** Seperate Depsoit Advice for Seperate Check and Regular Run
367 *****************************************************************/
368 cursor c_payments (cp_pre_pymt_action_id in number) is
369 select distinct ppp.source_action_id
370 from pay_pre_payments ppp
371 where ppp.assignment_action_id = cp_pre_pymt_action_id
372 order by ppp.source_action_id;
373
374 cursor c_payment_info (cp_pre_pymt_action_id in number) is
375 select distinct nvl(ppp.source_action_id,-999)
376 from pay_payment_information_v ppp
377 where ppp.assignment_action_id = cp_pre_pymt_action_id
378 and ppp.action_status = 'C'
379 order by 1;
380
381 cursor c_run_eff_date (cp_pre_pymt_action_id in number) is
382 select ppa.effective_date,
383 ppa.action_type
384 from pay_action_interlocks pai,
385 pay_assignment_actions paa,
386 pay_payroll_actions ppa
387 where pai.locking_action_id = cp_pre_pymt_action_id
388 and paa.assignment_action_id = pai.locked_action_id
389 and ppa.payroll_action_id = paa.payroll_action_id
390 and ppa.action_type in ('R', 'Q', 'B');
391
392 cursor c_payroll_run (cp_pre_pymt_action_id in number) is
393 select assignment_action_id, ppa.action_type
394 from pay_action_interlocks pai,
395 pay_assignment_actions paa,
396 pay_payroll_actions ppa
397 where pai.locking_action_id = cp_pre_pymt_action_id
398 and paa.assignment_action_id = pai.locked_action_id
399 and ppa.payroll_action_id = paa.payroll_action_id
400 and ((paa.run_type_id is null and paa.source_action_id is null) or
401 (paa.run_type_id is not null and paa.source_action_id is not null
402 and paa.run_type_id in
403 (select prt.run_type_id
404 from pay_run_types_f prt
405 where prt.shortname <> 'SEPCHECK'
406 and prt.legislation_code = 'US'
407 and ppa.effective_date between prt.effective_start_date
408 and prt.effective_end_date)
409 )
410 )
411 order by assignment_action_id desc;
412
413 -- Bug 4074976 -- Added a new cursor for Balance Adjustments for multiple assignment payroll.
414 cursor c_multi_ba_acts(cp_pre_pymt_action_id in number) is
415 select assignment_action_id, ppa.action_type
416 from pay_action_interlocks pai,
417 pay_assignment_actions paa,
418 pay_payroll_actions ppa
419 where pai.locking_action_id = cp_pre_pymt_action_id
420 and paa.assignment_action_id = pai.locked_action_id
421 and ppa.payroll_action_id = paa.payroll_action_id
422 and ((paa.run_type_id is null and paa.source_action_id is null) or
423 (paa.run_type_id is not null and paa.source_action_id is not null
424 and paa.run_type_id in
425 (select prt.run_type_id
426 from pay_run_types_f prt
427 where prt.shortname <> 'SEPCHECK'
428 and prt.legislation_code = 'US'
429 and ppa.effective_date between prt.effective_start_date
430 and prt.effective_end_date)
431 )
432 )
433 and ppa.action_type = 'B'
434 order by assignment_action_id desc;
435
436 cursor c_payroll_def (cp_assignment_id in number,
440 per_assignments_f paf
437 cp_effective_date in date) is
438 select multi_assignments_flag
439 from pay_payrolls_f ppf,
441 where paf.payroll_id = ppf.payroll_id
442 and cp_effective_date between paf.effective_start_date
443 and paf.effective_end_date
444 and cp_effective_date between ppf.effective_start_date --bug5366862
445 and ppf.effective_end_date
446 and paf.assignment_id = cp_assignment_id;
447
448
449 -- May need to add fetch of paa2.tax_unit_id and group by
450 -- this will corectly create the appropiate number of assignment
451 -- actions for the report. Also, pass tax_unit_id to this query
452 -- to only return the specific tu assignment actions.
453
454
455 cursor c_multi_asg_acts (cp_pre_pymt_action_id in number) is
456 select max(paa2.assignment_action_id)
457 from pay_assignment_actions paa2, -- assignment_actions for slave payroll runs.
458 pay_assignment_actions paa1, -- assignment_action for master payroll run
459 pay_run_Types_f prt,
460 pay_payroll_actions ppa,
461 pay_action_interlocks pai
462 where pai.locking_action_id = cp_pre_pymt_action_id
463 and pai.locked_action_id = paa1.assignment_action_id
464 and paa1.source_action_id is null -- master assignment_action
465 and paa1.assignment_action_id = paa2.source_action_id
466 and paa1.payroll_action_id = paa2.payroll_action_id
467 and paa2.run_type_id = prt.run_type_id
468 and prt.shortname <> 'SEPCHECK'
469 and prt.legislation_code = 'US'
470 and paa2.payroll_action_id = ppa.payroll_action_id
471 and ppa.effective_date between prt.effective_start_date
472 and prt.effective_end_date;
473
474
475
476 -- May need to add fetch of paa2.tax_unit_id and group by
477 -- this will corectly create the appropiate number of assignment
478 -- actions for the report. Also, pass tax_unit_id to this query
479 -- to only return the specific tu assignment actions.
480
481 cursor c_multi_asg_rpt_acts (cp_pre_pymt_action_id in number) is
482 select distinct max(paa2.assignment_action_id)
483 from pay_assignment_actions paa2,
484 -- assignment_actions for slave payroll runs.
485 pay_assignment_actions paa1,
486 -- assignment_action for master payroll run
487 pay_run_Types_f prt,
488 pay_payroll_actions ppa,
489 pay_action_interlocks pai
490 where pai.locking_action_id = cp_pre_pymt_action_id
491 and pai.locked_action_id = paa1.assignment_action_id
492 and paa1.source_action_id is null -- master assignment_action
493 and paa1.assignment_action_id = paa2.source_action_id
494 and paa1.payroll_action_id = paa2.payroll_action_id
495 and paa2.run_type_id = prt.run_type_id
496 and prt.shortname <> 'SEPCHECK'
497 and prt.legislation_code = 'US'
498 and paa2.payroll_action_id = ppa.payroll_action_id
499 and ppa.effective_date between prt.effective_start_date
500 and prt.effective_end_date
501 group by paa1.assignment_action_id;
502
503 cursor c_check_for_void (cp_pre_pymt_action_id in number) is
504 select 'Y'
505 from pay_action_interlocks pai,
506 pay_assignment_actions paa,
507 pay_payroll_actions ppa
508 where pai.locking_action_id = cp_pre_pymt_action_id
509 and paa.assignment_action_id = pai.locked_action_id
510 and ppa.payroll_action_id = paa.payroll_action_id
511 and action_type = 'V';
512
513 lockingactid number;
514 lockedactid number;
515 assignid number;
516 greid number;
517 num number;
518 runactid number;
519 actiontype VARCHAR2(1);
520 serialno VARCHAR2(30);
521
522 -- Bug 3372747
523 l_leg_param pay_payroll_actions.legislative_parameters%TYPE;
524 l_asg_set_id number;
525 l_asg_flag VARCHAR2(10);
526 l_effective_date date;
527 l_start_date date;
528 l_multi_asg_flag VARCHAR(1);
529 l_source_action_id NUMBER;
530
531 ln_pre_pymt_action_id NUMBER;
532
533 ln_source_action_id NUMBER;
534 ln_prev_source_action_id NUMBER := null;
535
536 ln_master_action_id NUMBER;
537 lv_run_action_type VARCHAR2(30);
538 lv_sep_check VARCHAR2(1);
539 lv_multi_asg_flag VARCHAR2(1);
540 lv_source_action_id NUMBER;
541
542 l_asg_act_id number;
543 l_action_insert varchar2(1);
544 l_void_action varchar2(1);
545
546 l_payroll_id pay_payroll_actions.payroll_id%TYPE;
547 l_location_id per_all_assignments_f.location_id%TYPE;
548 l_consolidation_set_id pay_payroll_actions.consolidation_set_id%TYPE;
549 l_tax_unit_id pay_assignment_actions.tax_unit_id%TYPE;
550 l_person_id per_all_assignments_f.person_id%TYPE;
551 l_business_group_id per_all_assignments_f.business_group_id%TYPE;
552 l_organization_id per_all_assignments_f.organization_id%TYPE;
556
553 l_assignment_set_id hr_assignment_set_amendments.assignment_set_id%TYPE;
554 cp_start_date pay_payroll_actions.effective_date%TYPE;
555 cp_effective_date pay_payroll_actions.effective_date%TYPE;
557 l_run_eff_date date;
558 run_action_type VARCHAR2(30);
559
560 lv_max_run_flag VARCHAR2(1) := 'N' ;
561 lv_max_run_id number;
562
563 -- algorithm is quite similar to the other process cases,
564 -- but we have to take into account assignments and
565 -- personal payment methods.
566 BEGIN
567 hr_utility.set_location('procpyr',1);
568
569 select legislative_parameters
570 into l_leg_param
571 from pay_payroll_actions ppa
572 where ppa.payroll_action_id = pactid;
573
574 -- hr_utility.trace('Payroll Action ID = '||pactid);
575
576 open c_inputs( pactid);
577
578 fetch c_inputs into l_payroll_id,
579 l_consolidation_set_id ,
580 l_tax_unit_id,
581 l_location_id,
582 l_organization_id ,
583 l_person_id,
584 l_business_group_id,
585 l_assignment_set_id,
586 cp_start_date,
587 cp_effective_date ;
588
589 close c_inputs;
590
591 -- hr_utility.trace('stperson '||stperson);
592 -- hr_utility.trace('endperson '||endperson);
593 -- hr_utility.trace('l_payroll_id '||l_payroll_id);
594 -- hr_utility.trace('l_consolidation_set_id '||l_consolidation_set_id);
595 -- hr_utility.trace('l_tax_unit_id '||l_tax_unit_id);
596 -- hr_utility.trace('l_location_id '||l_location_id);
597 -- hr_utility.trace('l_organization_id '||l_organization_id);
598 -- hr_utility.trace('l_person_id '||l_person_id);
599 -- hr_utility.trace('l_business_group_id '||l_business_group_id);
600 -- hr_utility.trace('l_assignment_set_id '||l_assignment_set_id);
601 -- hr_utility.trace('cp_start_date '||cp_start_date);
602 -- hr_utility.trace('cp_effective_date '||cp_effective_date);
603
604 if l_assignment_set_id is NULL then
605
606 open c_actions( stperson
607 ,endperson
608 ,l_payroll_id
609 ,l_consolidation_set_id
610 ,l_tax_unit_id
611 ,l_location_id
612 ,l_organization_id
613 ,l_person_id
614 ,l_business_group_id
615 ,cp_start_date
616 ,cp_effective_date);
617
618
619
620 else
621
622 open c_actions_1( stperson
623 ,endperson
624 ,l_payroll_id
625 ,l_consolidation_set_id
626 ,l_tax_unit_id
627 ,l_location_id
628 ,l_organization_id
629 ,l_person_id
630 ,l_business_group_id
631 ,l_assignment_set_id
632 ,cp_start_date
633 ,cp_effective_date);
634
635 end if;
636
637
638
639 l_asg_set_id := pay_payrg_pkg.get_parameter('PASID',l_leg_param);
640
641 num := 0;
642 loop
643 hr_utility.set_location('procpyr',2);
644
645 if l_assignment_set_id is NULL then
646
647 hr_utility.trace('in c_actions num= '||num);
648 fetch c_actions into lockedactid
649 ,assignid
650 ,greid
651 ,actiontype
652 ,l_effective_date
653 ,l_source_action_id
654 ,l_start_date;
655 if c_actions%found then num := num + 1; end if;
656 exit when c_actions%notfound;
657
658 else
659
660 hr_utility.trace('in c_actions_1 num= '||num);
661 fetch c_actions_1 into lockedactid
662 ,assignid
663 ,greid
664 ,actiontype
665 ,l_effective_date
666 ,l_source_action_id
667 ,l_start_date;
668 if c_actions_1%found then num := num + 1; end if;
669 exit when c_actions_1%notfound;
670 end if;
671
672 l_asg_flag := 'N';
673 l_action_insert := 'N';
674
675 if l_asg_set_id is not null then
676 l_asg_flag := hr_assignment_set.assignment_in_set(l_asg_set_id, assignid);
677 else -- l_asg_set_id is null
678 l_asg_flag := 'Y';
679 end if;
680
681 -- Checking if the payroll_run effective date is in the range
682 -- of c_start date and c_end date as the report must now run
683 -- on RUN effective_dates not Pre_payments effective_date
684 /* if l_start_date between cp_start_date and cp_effective_date
685 and l_effective_date between cp_start_date and cp_effective_date then
686
687 NULL;
688 ELSE
689 */
690 if (actiontype = 'P'
691 or actiontype = 'U') THEN
692 open c_run_eff_date (lockedactid) ;
693
697 l_asg_flag := 'N';
694 fetch c_run_eff_date into l_run_eff_date,
695 run_action_type;
696 if c_run_eff_date%NOTFOUND THEN
698 end if;
699 close c_run_eff_date;
700
701 if l_run_eff_date between cp_start_date and cp_effective_date then
702 NULL;
703 else
704 l_asg_flag := 'N';
705 end if;
706 end if;
707
708 /* end if;
709 */
710
711 if l_asg_flag = 'Y' then
712
713 -- check to see if the payroll on this assignment is
714 -- multi-assignmnet payroll enabled.
715
716 open c_payroll_def(assignid, l_effective_date); --bug5366862
717 fetch c_payroll_def into l_multi_asg_flag;
718 if c_payroll_def%NOTFOUND then
719 l_multi_asg_flag := 'N';
720 end if;
721 close c_payroll_def;
722
723 if l_multi_asg_flag = 'Y' then
724
725 IF actiontype in ('P', 'U') THEN
726
727 if l_source_action_id is not null then
728 -- this is a multi assignment payroll, however
729 -- we will treat separate check assignments as
730 -- no multi assignment as only one run action
731 -- will be returned.
732
733 lv_sep_check := 'Y';
734 lv_multi_asg_flag := 'N';
735 lv_source_action_id := NULL;
736
737 open c_payments (lockedactid) ;
738
739 loop
740
741 -- if there a multiple separate check elements for
742 -- this assignment we must create 1 payroll register
743 -- assignment action for each payment.
744
745 fetch c_payments into runactid;
746 exit when c_payments%NOTFOUND;
747 if runactid is not null then -- Bug 3928632
748
749 select pay_assignment_actions_s.nextval
750 into lockingactid
751 from dual;
752
753 -- insert the action record.
754 hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
755
756 -- insert an interlock to this action.
757 hr_nonrun_asact.insint(lockingactid,lockedactid);
758
759 begin
760
761 serialno := nvl(lv_run_action_type,'R') ||
762 lv_sep_check ||
763 lv_multi_asg_flag ||
764 to_char(runactid);
765
766 -- update pay_assignment_actions serial_number with runactid.
767
768 update pay_assignment_actions
769 set serial_number = serialno
770 where assignment_action_id = lockingactid
771 and tax_unit_id = greid;
772
773 exception when others then
774 null;
775 end;
776
777 -- Insert a row in pay_us_rpt_totals which includes
778 -- payroll_action_id,
779 -- report created assignment_action_id (lockingactid)
780 -- and "payroll run" assignment_action id.
781
782 insert into pay_us_rpt_totals
783 (session_id,
784 tax_unit_id,
785 location_id,
786 value1)
787 values(pactid,
788 pactid,
789 lockingactid,
790 runactid);
791 end if;
792 end loop;
793 close c_payments;
794
795 else -- this is a multi assignment payroll so we must
796 -- create only on assignment action for the Pre-payment
797 -- action returned in the query above.
798
799 lv_sep_check := 'N';
800 lv_multi_asg_flag := 'Y';
801 lv_source_action_id := lockedactid;
802 l_void_action := 'N';
803
804 open c_check_for_void ( lockedactid);
805
806 fetch c_check_for_void into l_void_action;
807
808 if c_check_for_void%NOTFOUND then
809 l_void_action := 'N';
810 end if;
811
812 close c_check_for_void;
813
814 if l_void_action = 'N' then
815
816 -- get the maximum runact for all assignments to be
817 -- included in this register row, as it will be used
818 -- for person level balance calls in the report.
822 fetch c_multi_asg_acts into runactid;
819
820 open c_multi_asg_acts (lockedactid) ;
821
823
824 close c_multi_asg_acts;
825
826 select pay_assignment_actions_s.nextval
827 into lockingactid
828 from dual;
829
830 -- insert the action record.
831 hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
832
833 -- insert an interlock to this action.
834 hr_nonrun_asact.insint(lockingactid,lockedactid);
835
836 --Bug 4074976 for MAP showning Balance Adjustment.
837
838 open c_multi_ba_acts (lockedactid);
839 loop
840 fetch c_multi_ba_acts into l_asg_act_id,run_action_type;
841 exit when c_multi_ba_acts%notfound;
842
843 insert into pay_us_rpt_totals
844 (session_id,
845 tax_unit_id,
846 location_id,
847 value1)
848 values(pactid,
849 pactid,
850 lockingactid,
851 l_asg_act_id);
852
853 if lv_max_run_flag = 'N' then
854 begin
855 serialno := nvl(run_action_type,'R') || -- Serial number updated for MAX BA only.
856 lv_sep_check ||
857 lv_multi_asg_flag ||
858 to_char(l_asg_act_id);
859
860 -- update pay_assignment_actions serial_number with runactid.
861
862 update pay_assignment_actions
863 set serial_number = serialno
864 where assignment_action_id = lockingactid
865 and tax_unit_id = greid;
866 lv_max_run_flag := 'Y' ;
867 exception when others then
868 null;
869 end;
870 end if;
871 end loop;
872 close c_multi_ba_acts;
873 lv_max_run_flag := 'N'; -- This is addded to Fix Bug # 5204333
874 -- pay_us_rpt_totals is populated for all Balance Adjustments.
875
876 begin
877 if runactid is not NULL then
878 serialno := nvl(lv_run_action_type,'R') ||
879 lv_sep_check ||
880 lv_multi_asg_flag ||
881 to_char(runactid);
882
883 -- update pay_assignment_actions serial_number with runactid.
884
885 update pay_assignment_actions
886 set serial_number = serialno
887 where assignment_action_id = lockingactid
888 and tax_unit_id = greid;
889 end if;
890 exception when others then
891 null;
892 end;
893
894 -- loop through and fetch all assignment actions
895 -- that were created.
896
897 open c_multi_asg_rpt_acts (lockedactid); -- This is for RUN.
898 loop
899
900 fetch c_multi_asg_rpt_acts into l_asg_act_id;
901
902 exit when c_multi_asg_rpt_acts%NOTFOUND;
903 -- Insert a row in pay_us_rpt_totals which includes
904 -- payroll_action_id,
905 -- report created assignment_action_id (lockingactid)
906 -- and "payroll run" assignment_action id.
907
908 insert into pay_us_rpt_totals
909 (session_id,
910 tax_unit_id,
911 location_id,
912 value1)
913 values(pactid,
914 pactid,
915 lockingactid,
916 l_asg_act_id);
917
918 end loop;
919
920 close c_multi_asg_rpt_acts;
921
922 end if; -- l_void_action = 'N'
923
924 end if; -- source action id is null
925
926 ELSE -- This is a void action.
927
928 select pay_assignment_actions_s.nextval
929 into lockingactid
930 from dual;
931
932 hr_utility.trace('B4 insact'||to_char(lockingactid) ||','||to_char(greid)||','||actiontype||','||to_char(runactid) );
933
934 -- insert the action record.
935 hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
936 hr_utility.trace('A4 insact'||to_char(lockingactid) ||','||to_char(greid)||','||actiontype||','||to_char(runactid) );
937
938 -- insert an interlock to this action.
942 --serialno := 'V'||to_char(runactid);
939 hr_nonrun_asact.insint(lockingactid,lockedactid);
940
941 begin
943 serialno := actiontype || 'NN' || to_char(lockedactid);
944 -- update pay_assignment_actions serial_number with runactid.
945 update pay_assignment_actions
946 set serial_number = serialno
947 where assignment_action_id = lockingactid
948 and tax_unit_id = greid;
949 exception when others then
950 null;
951 end;
952
953 -- Insert a row in pay_us_rpt_totals which includes
954 -- payroll_action_id,
955 -- report created assignment_action_id (lockingactid)
956 -- and "payroll run" assignment_action id.
957
958 insert into pay_us_rpt_totals
959 (session_id,
960 tax_unit_id,
961 location_id,
962 value1)
963 values(pactid,
964 pactid,
965 lockingactid,
966 lockedactid);
967
968 END IF; -- IF actiontype in ('P', 'U')
969
970 else -- THIS IS NOT A MULTI ASSIGNMENT PAYROLL
971
972 -- we need to insert one action for each of the
973 -- rows that we return from the cursor (i.e. one
974 -- for each assignment/pre-payment/reversal).
975 hr_utility.set_location('procpyr',3);
976
977 IF actiontype in ('P', 'U') THEN
978
979 open c_payment_info (lockedactid);
980 loop
981 fetch c_payment_info into ln_source_action_id;
982
983 if c_payment_info%notfound then
984
985 if l_action_insert = 'N' then
986
987
988 -- We need to make sure that the pre_pay assignment
989 -- action is not locking a void action as the void)
990 -- is handled else where
991
992 l_void_action := 'N';
993
994 open c_check_for_void ( lockedactid);
995
996 fetch c_check_for_void into l_void_action;
997
998 if c_check_for_void%NOTFOUND then
999 l_void_action := 'N';
1000 end if;
1001
1002 close c_check_for_void;
1003
1004 if l_void_action = 'N' then
1005
1006 -- we have a zero net pay pre-pay assignment_action
1007 -- insert one row for the action creation.
1008 -- insert the action record.
1009
1010 select pay_assignment_actions_s.nextval
1011 into lockingactid
1012 from dual;
1013
1014 -- insert the action record.
1015 hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
1016
1017 -- insert an interlock to this action.
1018 hr_nonrun_asact.insint(lockingactid,lockedactid);
1019
1020 open c_payroll_run (lockedactid);
1021 /* Pre-payment can lock more then one run so this should loop
1022 commented out following two lines - bug 3385676
1023 - insert multiple rows in pay_us_rpt_totals
1024
1025 fetch c_payroll_run into ln_master_action_id,lv_run_action_type;
1026 close c_payroll_run;
1027 */
1028
1029 fetch c_payroll_run into ln_master_action_id,lv_run_action_type;
1030 close c_payroll_run;
1031 runactid := ln_master_action_id;
1032 lv_sep_check := 'N';
1033
1034 begin
1035 /* we no longer user the serial number so there should
1036 no be any impact of looping */
1037
1038 serialno := nvl(lv_run_action_type,'R') ||
1039 lv_sep_check ||
1040 'N' || -- multi_asg_flag
1041 to_char(runactid);
1042
1043 -- update pay_assignment_actions serial_number with runactid.
1044
1045 update pay_assignment_actions
1046 set serial_number = serialno
1047 where assignment_action_id = lockingactid
1048 and tax_unit_id = greid;
1049
1050 exception when others then
1051 null;
1052 end;
1053
1054 -- Insert a row in pay_us_rpt_totals which includes
1058
1055 -- payroll_action_id,
1056 -- report created assignment_action_id (lockingactid)
1057 -- and "payroll run" assignment_action id.
1059 insert into pay_us_rpt_totals
1060 (session_id,
1061 tax_unit_id,
1062 location_id,
1063 value1)
1064 values(pactid,
1065 pactid,
1066 lockingactid,
1067 runactid);
1068
1069 end if; --l_void_action = 'N'
1070
1071 exit;
1072
1073 else -- l_action_insert = 'N'
1074
1075 exit;
1076
1077 end if; -- l_action_insert = 'N'
1078
1079 else -- if c_payment_info%notfound
1080
1081 /**************************************************************
1082 ** we need to insert one action for each of the rows that we
1083 ** return from the cursor (i.e. one for each assignment/pre-payment source).
1084 **************************************************************/
1085 if (ln_prev_source_action_id is null or
1086 ln_source_action_id <> ln_prev_source_action_id or
1087 ln_source_action_id = -999) then
1088
1089 -- insert the action record.
1090 select pay_assignment_actions_s.nextval
1091 into lockingactid
1092 from dual;
1093
1094 l_action_insert := 'Y';
1095 -- insert the action record.
1096 hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
1097
1098 -- insert an interlock to this action.
1099 hr_nonrun_asact.insint(lockingactid,lockedactid);
1100
1101 if ln_source_action_id <> -999 then
1102 runactid := ln_source_action_id;
1103 lv_sep_check := 'Y';
1104 begin
1105 serialno := nvl(lv_run_action_type,'R') ||
1106 lv_sep_check ||
1107 'N' || -- multi_asg_flag
1108 to_char(runactid);
1109
1110 -- update pay_assignment_actions serial_number with runactid.
1111
1112 update pay_assignment_actions
1113 set serial_number = serialno
1114 where assignment_action_id = lockingactid
1115 and tax_unit_id = greid;
1116
1117 exception when others then
1118 null;
1119 end;
1120
1121 -- Insert a row in pay_us_rpt_totals which includes
1122 -- payroll_action_id,
1123 -- report created assignment_action_id (lockingactid)
1124 -- and "payroll run" assignment_action id.
1125
1126 insert into pay_us_rpt_totals
1127 (session_id,
1128 tax_unit_id,
1129 location_id,
1130 value1)
1131 values(pactid,
1132 pactid,
1133 lockingactid,
1134 runactid);
1135
1136 -- skip till next source action id
1137
1138 ln_prev_source_action_id := ln_source_action_id;
1139 -- Bug 4074976 Begin-- We will loop the cursor c_payroll_run and insert rows in pay_us_rpt_totals for max(run) action
1143
1140 -- id and all the balance id's which the prepayment locks.
1141 else
1142 lv_max_run_flag := 'N' ; -- Initialise the variables.
1144 open c_payroll_run (lockedactid);
1145 loop
1146 fetch c_payroll_run into ln_master_action_id,lv_run_action_type;
1147 exit when c_payroll_run%notfound;
1148
1149 if (lv_max_run_flag = 'N' and lv_run_action_type in ('R','Q')) OR
1150 (lv_run_action_type = 'B') then -- Max run and all balance adjustments
1151 runactid := ln_master_action_id;
1152 lv_sep_check := 'N';
1153
1154 if lv_run_action_type in ('R','Q') then -- makes sure that run is inserted just once.
1155 lv_max_run_flag := 'Y';
1156 end if;
1157
1158 begin
1159 serialno := nvl(lv_run_action_type,'R') ||
1160 lv_sep_check ||
1161 'N' || -- multi_asg_flag
1162 to_char(runactid);
1163
1164 -- update pay_assignment_actions serial_number with runactid.
1165
1166 update pay_assignment_actions
1167 set serial_number = serialno
1168 where assignment_action_id = lockingactid
1169 and tax_unit_id = greid;
1170
1171 exception when others then
1172 null;
1173 end;
1174
1175 -- Insert a row in pay_us_rpt_totals which includes
1176 -- payroll_action_id,
1177 -- report created assignment_action_id (lockingactid)
1178 -- and "payroll run" assignment_action id.
1179
1180 insert into pay_us_rpt_totals
1181 (session_id,
1182 tax_unit_id,
1183 location_id,
1184 value1)
1185 values(pactid,
1186 pactid,
1187 lockingactid,
1188 runactid);
1189
1190 -- skip till next source action id
1191 ln_prev_source_action_id := ln_source_action_id;
1192 end if; -- (lv_max_run_flag = 'N' and lv_run_action_type = 'R') OR (lv_run_action_type = 'B')
1193 end loop;
1194 close c_payroll_run;
1195 -- Bug 4074976 -- End
1196 end if; -- ln_source_action_id <> -9999
1197 end if; -- (ln_prev_source_action_id is null or ...
1198
1199 end if; -- if c_payment_info%notfound
1200
1201 end loop;
1202
1203 close c_payment_info;
1204
1205 ELSE -- This is a void action.
1206
1207 select pay_assignment_actions_s.nextval
1208 into lockingactid
1209 from dual;
1210
1211 hr_utility.trace('B4 insact'||to_char(lockingactid) ||','||to_char(greid)||','||actiontype||','||to_char(runactid) );
1212
1213 -- insert the action record.
1214 hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
1215 hr_utility.trace('A4 insact'||to_char(lockingactid) ||','||to_char(greid)||','||actiontype||','||to_char(runactid) );
1216
1217 -- insert an interlock to this action.
1218 hr_nonrun_asact.insint(lockingactid,lockedactid);
1219
1220 begin
1221 --serialno := 'V'||to_char(runactid);
1222 serialno := actiontype || 'NN' || to_char(lockedactid);
1223 -- update pay_assignment_actions serial_number with runactid.
1224 update pay_assignment_actions
1225 set serial_number = serialno
1226 where assignment_action_id = lockingactid
1227 and tax_unit_id = greid;
1228 exception when others then
1229 null;
1230 end;
1231
1232 -- Insert a row in pay_us_rpt_totals which includes
1233 -- payroll_action_id,
1234 -- report created assignment_action_id (lockingactid)
1235 -- and "payroll run" assignment_action id.
1236
1237 insert into pay_us_rpt_totals
1238 (session_id,
1239 tax_unit_id,
1240 location_id,
1241 value1)
1242 values(pactid,
1243 pactid,
1244 lockingactid,
1245 lockedactid);
1246
1247
1248 END IF; -- if action_type in ('P', 'U');
1249
1250 end if; -- l_multi_asg_flag = 'Y'
1251
1252 end if; -- if l_asg_flag = 'Y'
1253
1254 end loop;
1255
1256 if l_assignment_set_id is NULL then
1257 -- hr_utility.trace('Closing c_actions');
1258 close c_actions;
1259 else
1260 -- hr_utility.trace('Closing c_actions_1');
1261 close c_actions_1;
1262 end if;
1263
1264 END action_creation;
1265
1266 ---------------------------------- sort_action ----------------------------------
1267 PROCEDURE sort_action(
1268 payactid in varchar2, /* payroll action id */
1269 sqlstr in out nocopy varchar2, /* string holding the sql statement */
1270 len out nocopy number /* length of the sql string */
1271 ) is
1272
1273 l_sort_1 varchar2(30);
1274 l_sort_2 varchar2(30);
1275 l_sort_3 varchar2(30);
1276
1277
1278 BEGIN
1279
1280 select pay_payrg_pkg.get_parameter('P_S1', ppa.legislative_parameters),
1281 pay_payrg_pkg.get_parameter('P_S2', ppa.legislative_parameters),
1282 pay_payrg_pkg.get_parameter('P_S3', ppa.legislative_parameters)
1283 into l_sort_1,
1284 l_sort_2,
1285 l_sort_3
1286 from pay_payroll_actions ppa
1287 where ppa.payroll_action_id = payactid;
1288
1289
1290 sqlstr :=
1291 'select paa.rowid
1292 from pay_assignment_actions paa,
1293 pay_payroll_actions ppa
1294 where ppa.payroll_action_id = :payactid
1295 and paa.payroll_action_id = ppa.payroll_action_id
1296 order by
1297 (decode('''||l_sort_1||''',
1298 null, null,
1299 pay_payrg_pkg.sort_option (
1300 '''||l_sort_1||''',
1301 paa.assignment_id,
1302 ppa.effective_date,
1303 paa.tax_unit_id))),
1304 (decode('''||l_sort_2||''',
1305 null, null,
1306 pay_payrg_pkg.sort_option (
1307 '''||l_sort_2||''',
1308 paa.assignment_id,
1309 ppa.effective_date,
1310 paa.tax_unit_id))),
1311 (decode('''||l_sort_3||''',
1312 null, null,
1313 pay_payrg_pkg.sort_option (
1314 '''||l_sort_3||''',
1315 paa.assignment_id,
1316 ppa.effective_date,
1317 paa.tax_unit_id))),
1318 (select hou.name
1319 from hr_all_organization_units hou, /* Assignment Org */
1320 per_assignments_f paf
1321 where paf.assignment_id = paa.assignment_id
1322 and ppa.effective_date between
1323 paf.effective_start_date and paf.effective_end_date
1324 and hou.organization_id = paf.organization_id
1325 and rownum = 1),
1326 (select distinct ppf.full_name
1327 from per_all_people_f ppf,
1328 per_all_assignments_f paf
1329 where paf.assignment_id = paa.assignment_id
1330 and ppf.person_id = paf.person_id
1331 and ppa.effective_date between
1332 paf.effective_start_date and paf.effective_end_date
1333 and ppa.effective_date between
1334 ppf.effective_start_date and ppf.effective_end_date
1338 pay_assignment_actions paa2
1335 ),
1336 (select ppa2.effective_date
1337 from pay_payroll_actions ppa2,
1339 where paa2.assignment_action_id = to_number(substr(paa.serial_number,4))
1340 and paa2.payroll_action_id = ppa2.payroll_action_id
1341 and ppa2.action_type in (''R'', ''Q'', ''V'', ''B'')
1342 )
1343 for update of paa.assignment_id';
1344
1345 len := length(sqlstr); -- return the length of the string.
1346
1347 END sort_action;
1348
1349
1350
1351 ----------------------------- get_parameter -------------------------------
1352 FUNCTION get_parameter(name in varchar2,
1353 parameter_list varchar2)
1354 RETURN VARCHAR2
1355 IS
1356 start_ptr number;
1357 end_ptr number;
1358 token_val pay_payroll_actions.legislative_parameters%type;
1359 par_value pay_payroll_actions.legislative_parameters%type;
1360 BEGIN
1361
1362 token_val := name || '=';
1363
1364 start_ptr := instr(parameter_list, token_val) + length(token_val);
1365 end_ptr := instr(parameter_list, ' ',start_ptr);
1366
1367 /* if there is no spaces use then length of the string */
1368 if end_ptr = 0 then
1369 end_ptr := length(parameter_list) + 1;
1370 end if;
1371
1372 /* Did we find the token */
1373 if instr(parameter_list, token_val) = 0 then
1374 par_value := NULL;
1375 else
1376 par_value := substr(parameter_list, start_ptr, end_ptr - start_ptr);
1377 end if;
1378
1379 return par_value;
1380
1381 END get_parameter;
1382
1383 FUNCTION sort_option (c_option_name in varchar2,
1384 c_assignment_id in number,
1385 c_effective_date in date,
1386 c_tax_unit_id in number)
1387 RETURN VARCHAR2
1388 IS
1389
1390 return_val varchar2(240);
1391
1392 BEGIN
1393
1394 if c_option_name = 'GRE' Then
1395
1396 select hou1.name
1397 into return_val
1398 from hr_all_organization_units hou1 /* Tax Unit */
1399 where hou1.organization_id = c_tax_unit_id
1400 and rownum = 1;
1401
1402 else
1403
1404 select decode(c_option_name,
1405 'Organization',hou.name,
1406 'Location',loc.location_code,
1407 null)
1408 into return_val
1409 from hr_all_organization_units hou, /* Assignment Org */
1410 hr_locations_all loc,
1411 per_assignments_f paf
1412 where paf.assignment_id = c_assignment_id
1413 and c_effective_date between
1414 paf.effective_start_date and paf.effective_end_date
1415 and hou.organization_id = paf.organization_id
1416 and loc.location_id = paf.location_id
1417 and rownum = 1;
1418
1419 end if;
1420
1421 return return_val;
1422
1423 EXCEPTION
1424 when others then
1425 return '1';
1426
1427 END sort_option;
1428
1429
1430 end pay_payrg_pkg;
[Home] [Help]