[Home] [Help]
PACKAGE BODY: APPS.PAY_AU_PAYMENT_SUMMARY_REPORT
Source
1 package body pay_au_payment_summary_report as
2 /* $Header: pyaupsrp.pkb 120.4.12010000.3 2008/08/06 06:53:21 ubhat ship $*/
3 /*
4 *** ------------------------------------------------------------------------+
5 *** Program: pay_au_payment_summary_report (Package Body)
6 ***
7 *** Change History
8 ***
9 *** Date Changed By Version Description of Change
10 *** --------- ---------- ------- ----------------------------------------+
11 *** 01 MAR 01 kaverma 1.0 Initial version
12 *** 28 Nov 01 nnaresh 1.1 Updated for GSCC Standards
13 *** 29 Nov 01 nnaresh 1.2 Replaced REM with ***
14 *** 03 DEC 02 Ragovind 1.7 Added NOCOPY for the function range_code.
15 *** 18 FEB 03 nanuradh 1.8 2786549 Removed number_of_copies parameter when setting
16 *** printer options
17 *** 25 FEB 03 nanuradh 1.9 2786549 Removed the fix done for the bug #2786549
18 *** 29 MAY 03 apunekar 1.10 2920725 Corrected base tables to support security model
19 *** 17 NOV 03 avenkatk 1.11 3132172 Added support for printing report on
20 *** duplex printers.
21 *** 10 FEB 04 punmehta 1.12 3098353 Added check for archive flag
22 *** 21 JUL 04 srrajago 1.13 3768288 Modified cursor 'csr_get_print_options' to fetch value 'number_of_copies'
23 *** and the same is passed to fnd_request.set_print_options.Resolved GSCC warning in
24 *** assigning value -1 to ps_request_id.
25 *** 09 AUG 04 abhkumar 1.14 2610141 Legal Employer Enhancement
26 *** 09 DEC 04 ksingla 1.15 3937976 Added check for archive flag X_CURR_TERM_0_BAL_FLAG
27 *** 06 DEC 05 avenkatk 1.16 4859876 Added support for XML Publisher PDF Template
28 *** 02 JAN 06 avenkatk 1.17 4891196 Added support for PDF and Postscript generation of report.
29 *** 03 Jan 06 abhargav 1.18 4726357 Added function to get the self serivce option.
30 *** 28_feb-07 abhargav 1.20 5743270 Added check so that empty self printed report will not be generated
31 *** for cases where for all the assignment Self Printed flag is set 'Yes'.
32 *** 09-Jan-08 avenkatk 115.21 6470581 Added Changes for Amended Payment Summary
33 *** 23-Jan-08 avenkatk 115.22 6470581 Resolved GSCC Errors
34 *** ------------------------------------------------------------------------+
35 */
36
37 g_debug boolean; /* Bug 6470581 */
38
39 -------------------------------------------------------------------------
40 -- This procedure returns a sql string to select a range
41 -- of assignments eligible for archive report process.
42 -------------------------------------------------------------------------
43
44 procedure range_code
45 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
46 p_sql out NOCOPY varchar2) is
47 begin
48 hr_utility.set_location('Start of range_code',1);
49
50 /*Bug2920725 Corrected base tables to support security model*/
51
52 p_sql := ' select distinct p.person_id' ||
53 ' from per_people_f p,' ||
54 ' pay_payroll_actions pa' ||
55 ' where pa.payroll_action_id = :payroll_action_id' ||
56 ' and p.business_group_id = pa.business_group_id' ||
57 ' order by p.person_id';
58
59 hr_utility.set_location('End of range_code',2);
60 end range_code;
61
62 -------------------------------------------------------------------------
63 -- This procedure further restricts the assignment_id's
64 -- returned by range_code and locks the Assignment Actions for which
65 -- a Payment Summry Report has been printed.
66 -------------------------------------------------------------------------
67
68
69 -- this procedure filters the assignments selected by range_code procedure
70 -- it then calls hr_nonrun.insact to create an assignment id
71 -- the cursor to select assignment action selects assignment id for which
72 -- archival has been done.
73
74 procedure assignment_action_code
75 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
76 p_start_person_id in per_all_people_f.person_id%type,
77 p_end_person_id in per_all_people_f.person_id%type,
78 p_chunk in number) is
79
80 v_next_action_id pay_assignment_actions.assignment_action_id%type;
81
82 /*Bug2920725 Corrected base tables to support security model*/
83
84
85 cursor process_assignments
86 (c_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
87 c_start_person_id in per_all_people_f.person_id%type,
88 c_end_person_id in per_all_people_f.person_id%type) is
89 select distinct a.assignment_id,
90 pay_core_utils.get_parameter('ARCHIVE_ID', pa.legislative_parameters) archive_action_id,
91 ppac.assignment_action_id
92 from per_assignments_f a,
93 per_people_f p,
94 pay_payroll_actions pa,
95 pay_payroll_actions ppa,
96 pay_assignment_actions ppac
97 where pa.payroll_action_id = c_payroll_action_id
98 and p.person_id between c_start_person_id and c_end_person_id
99 and p.person_id = a.person_id
100 and p.business_group_id = pa.business_group_id
101 and ppa.payroll_action_id = ppac.payroll_action_id
102 and a.assignment_id = ppac.assignment_id
103 and ppa.payroll_action_id = pay_core_utils.get_parameter('ARCHIVE_ID', pa.legislative_parameters)
104 And ppa.action_type = 'X'
105 and ppa.action_status = 'C'
106 and pay_au_payment_summary.get_archive_value('X_REPORTING_FLAG', ppac.assignment_action_id)='YES' --3098353
107 and pay_au_payment_summary.get_archive_value('X_CURR_TERM_0_BAL_FLAG', ppac.assignment_action_id)='NO' --3937976
108 and not exists
109 (select locked_action_id
110 FROM pay_action_interlocks pail
111 WHERE pail.locked_action_id=ppac.assignment_action_id)
112 and ppac.assignment_action_id in
113 (select max(ppac1.assignment_action_id)
114 from pay_assignment_actions ppac1,
115 pay_payroll_Actions ppaa
116 where ppaa.action_type ='X'
117 and ppaa.action_status ='C'
118 and pay_core_utils.get_parameter('REGISTERED_EMPLOYER', ppaa.legislative_parameters) =
119 pay_core_utils.get_parameter('REGISTERED_EMPLOYER', pa.legislative_parameters) --2610141
120 and ppaa.report_type ='AU_PAYMENT_SUMMARY'
121 and ppaa.payroll_Action_id = ppac1.payroll_action_id
122 group by ppac1.assignment_id);
123
124 cursor next_action_id is
125 select pay_assignment_actions_s.nextval
126 from dual;
127
128
129 /* Bug 6470581 - Added the Following cursors to get Payment Summary Information
130 and asignments eligible for Self Printed process for Amended PS
131 */
132
133 CURSOR c_get_paysum_details
134 (c_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE)
135 IS
136 SELECT to_number(pay_core_utils.get_parameter('REGISTERED_EMPLOYER', ppa.legislative_parameters)) registered_employer
137 ,pay_core_utils.get_parameter('ARCHIVE_ID', ppa.legislative_parameters) archive_id
138 ,NVL(pay_core_utils.get_parameter('PAY_SUM_TYPE', ppa.legislative_parameters),'O') payment_summary_type
139 ,pay_core_utils.get_parameter('FINANCIAL_YEAR', ppa.legislative_parameters) fin_year
140 FROM pay_payroll_actions ppa
141 WHERE ppa.payroll_action_id = c_payroll_action_id;
142
143
144 CURSOR c_amend_process_assignments
145 (c_payroll_action_id IN pay_payroll_actions.payroll_action_id%TYPE
146 ,c_start_person_id IN per_all_people_f.person_id%TYPE
147 ,c_end_person_id IN per_all_people_f.person_id%TYPE
148 ,c_archive_id IN pay_payroll_actions.payroll_action_id%TYPE
149 ,c_reg_emp IN pay_assignment_actions.tax_unit_id%TYPE
150 ,c_financial_year VARCHAR2)
151 IS
152 SELECT DISTINCT a.assignment_id
153 ,pay_core_utils.get_parameter('ARCHIVE_ID', pa.legislative_parameters) archive_action_id
154 ,ppac.assignment_action_id
155 ,pmaa.assignment_action_id datafile_action_id
156 FROM per_assignments_f a
157 ,per_people_f p
158 ,pay_payroll_actions pa
159 ,pay_payroll_actions ppa
160 ,pay_assignment_actions ppac
161 ,pay_assignment_actions pmaa
162 ,pay_payroll_actions pmpa
163 WHERE pa.payroll_action_id = c_payroll_action_id
164 AND p.person_id between c_start_person_id and c_end_person_id
165 AND p.person_id = a.person_id
166 AND p.business_group_id = pa.business_group_id
167 AND ppa.payroll_action_id = ppac.payroll_action_id
168 AND a.assignment_id = ppac.assignment_id
169 AND ppa.payroll_action_id = c_archive_id
170 AND ppa.action_type = 'X'
171 AND ppa.action_status = 'C'
172 AND pay_au_payment_summary.get_archive_value('X_REPORTING_FLAG', ppac.assignment_action_id)='YES'
173 AND pay_au_payment_summary.get_archive_value('X_CURR_TERM_0_BAL_FLAG', ppac.assignment_action_id)='NO'
174 AND pay_au_payment_summary.get_archive_value('X_PAYMENT_SUMMARY_TYPE', ppac.assignment_action_id)='A' /* Indicates something has changed */
175 AND pmaa.assignment_id = ppac.assignment_id
176 AND pmaa.payroll_action_id = pmpa.payroll_action_id
177 AND pmpa.report_type = 'AU_PS_DATA_FILE'
178 AND pmpa.action_type = 'X'
179 AND pmpa.action_status = 'C'
180 AND pay_core_utils.get_parameter('REGISTERED_EMPLOYER', pmpa.legislative_parameters) = c_reg_emp
181 AND pay_core_utils.get_parameter('FINANCIAL_YEAR', pmpa.legislative_parameters) = c_financial_year
182 AND NOT EXISTS
183 (SELECT locked_action_id
184 FROM pay_action_interlocks pail
185 WHERE pail.locked_action_id=ppac.assignment_action_id)
186 AND ppac.assignment_action_id IN
187 (SELECT MAX(ppac1.assignment_action_id)
188 FROM pay_assignment_actions ppac1,
189 pay_payroll_Actions ppaa
190 WHERE ppaa.action_type ='X'
191 AND ppaa.action_status ='C'
192 AND pay_core_utils.get_parameter('REGISTERED_EMPLOYER', ppaa.legislative_parameters) =
193 pay_core_utils.get_parameter('REGISTERED_EMPLOYER', pa.legislative_parameters) --2610141
194 AND ppaa.report_type ='AU_PAY_SUMM_AMEND'
195 AND ppaa.payroll_Action_id = ppac1.payroll_action_id
196 GROUP BY ppac1.assignment_id);
197
198 l_get_paysum_details c_get_paysum_details%ROWTYPE;
199
200 /* End Bug 6470581 */
201
202 BEGIN
203
204 g_debug := hr_utility.debug_enabled;
205
206 IF g_debug
207 THEN
208 hr_utility.set_location('Start of assignment_action_code',3);
209 hr_utility.set_location('The payroll_action_id passed '|| p_payroll_action_id,4);
210 hr_utility.set_location('The p_start_person_id '|| p_start_person_id,5);
211 hr_utility.set_location('The p_end_person_id '|| p_end_person_id,6);
212 hr_utility.set_location('The p_chunk number '|| p_chunk ,7);
213 END IF;
214
215 /* Bug 6470581 - Fetch the Payment Summary Type details.
216 If Type is 'O' (Original), lock only Archive action
217 If Type is 'A' (Amended) , lock Archive and Original Data file actions
218 */
219
220 OPEN c_get_paysum_details(p_payroll_action_id);
221 FETCH c_get_paysum_details INTO l_get_paysum_details;
222 CLOSE c_get_paysum_details;
223
224 IF l_get_paysum_details.payment_summary_type = 'O'
225 THEN
226
227 for process_rec in process_assignments (p_payroll_action_id,
228 p_start_person_id,
229 p_end_person_id)
230 loop
231 hr_utility.set_location('LOOP STARTED '|| process_rec.assignment_id ,14);
232 open next_action_id;
233 fetch next_action_id into v_next_action_id;
234 close next_action_id;
235 hr_utility.set_location('before calling insact '|| v_next_action_id ,14);
236 hr_nonrun_asact.insact(v_next_action_id,
237 process_rec.assignment_id,
238 p_payroll_action_id,
239 p_chunk,
240 null);
241 hr_utility.set_location('inserted assigment action assignment '|| process_rec.assignment_id ,15);
242 hr_utility.set_location('Before calling hr_nonrun_asact.insint archive ' || process_rec.archive_action_id,16);
243 hr_utility.set_location('v_next_action_id' || v_next_action_id,16);
244 hr_nonrun_asact.insint(v_next_action_id,process_rec.assignment_action_id);
245 hr_utility.set_location('After calling hr_nonrun_asact.insint',14);
246 end loop;
247 hr_utility.set_location('End of assignment_action_code',5);
248
249 ELSIF l_get_paysum_details.payment_summary_type = 'A'
250 THEN
251
252 IF g_debug
253 THEN
254 hr_utility.set_location('Inside Amended Payment Summary Section ',30);
255 END IF;
256
257 FOR csr_rec IN c_amend_process_assignments(p_payroll_action_id
258 ,p_start_person_id
259 ,p_end_person_id
260 ,l_get_paysum_details.archive_id
261 ,l_get_paysum_details.registered_employer
262 ,l_get_paysum_details.fin_year)
263 LOOP
264
265 OPEN next_action_id;
266 FETCH next_action_id into v_next_action_id;
267 CLOSE next_action_id;
268 hr_nonrun_asact.insact(v_next_action_id,
269 csr_rec.assignment_id,
270 p_payroll_action_id,
271 p_chunk,
272 null);
273 hr_nonrun_asact.insint(v_next_action_id,csr_rec.assignment_action_id);
274 hr_nonrun_asact.insint(v_next_action_id,csr_rec.datafile_action_id);
275
276 IF g_debug
277 THEN
278 hr_utility.set_location('Assignment_ID '||csr_rec.assignment_id,35);
279 hr_utility.set_location('New Ass Action ID '||v_next_action_id,40);
280 hr_utility.set_location('Locked Archive Action ID '||csr_rec.assignment_action_id,45);
281 hr_utility.set_location('Locked Data file Action ID '||csr_rec.datafile_action_id,50);
282 END IF;
283 END LOOP;
284 END IF;
285
286 exception
287 when others then
288 hr_utility.set_location('error raised in assignment_action_code procedure ',5);
289 raise;
290 end assignment_action_code;
291
292
293 --------------------------------------------------------------------------
294 -- This Procedure Actually Calls the Payment Summary Report.
295 --------------------------------------------------------------------------
296
297 procedure spawn_archive_reports
298 is
299 l_count number :=0;
300 ps_request_id NUMBER;
301 l_formula_id ff_formulas_f.formula_id%TYPE;
302 l_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE;
303 l_sort_order1 varchar2(40);
304 l_sort_order2 varchar2(40):=null;
305 l_sort_order3 varchar2(40):=null;
306 l_sort_order4 varchar2(40):=null;
307 l_passed_sort_order varchar2(40);
308 l_print_style VARCHAR2(2);
309 l_current_chunk_number pay_payroll_actions.current_chunk_number%TYPE;
310 l_print_together VARCHAR2(80);
311 l_print_return BOOLEAN;
312 l_duplex_print_flag varchar2(2);
313 l_template_name varchar2(80); -- Bug 4859876
314 l_program_name varchar2(80); -- Bug 4891196
315
316
317
318 cursor csr_get_current_chunk_number(p_payroll_action_id number) is
319 select p.current_chunk_number
320 from pay_payroll_actions p
321 where payroll_action_id = p_payroll_action_id;
322
323
324 cursor csr_get_formula_id(p_formula_name VARCHAR2) IS
325 SELECT a.formula_id
326 FROM ff_formulas_f a,
327 ff_formula_types t
328 WHERE a.formula_name = p_formula_name
329 AND business_group_id IS NULL
330 AND legislation_code = 'AU'
331 AND a.formula_type_id = t.formula_type_id
332 AND t.formula_type_name = 'Oracle Payroll';
333
334
335 cursor csr_get_print_options(p_payroll_action_id NUMBER) IS
336 SELECT printer,
337 print_style,
338 decode(save_output_flag, 'Y', 'TRUE', 'N', 'FALSE') save_output,
339 number_of_copies /* Bug: 3768288 */
340 FROM pay_payroll_actions pact,
341 fnd_concurrent_requests fcr
342 WHERE fcr.request_id = pact.request_id
343 AND pact.payroll_action_id = p_payroll_action_id;
344
345 /*Bug# 5743270
346 Cursor checks whether any assignment exist for which Printed Payment Summary(PUI) need to be produced
347 */
348 cursor csr_is_assignemnt_exist (p_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE) is
349 select count(ppav.assignment_id)
350 from pay_au_eoy_values_v ppav,
351 pay_payroll_actions ppa,
352 pay_assignment_actions pac
353 where ppa.payroll_action_id= p_payroll_action_id
354 and ppav.payroll_action_id =pay_core_utils.get_parameter('ARCHIVE_ID',ppa.legislative_parameters)
355 and ppa.report_type='AU_PAYMENT_SUMMARY_REPORT'
356 and ppav.assignment_id=pac.assignment_id
357 and ppav.X_REPORTING_FLAG = 'YES'
358 and ppav.X_CURR_TERM_0_BAL_FLAG='NO'
359 and pac.payroll_action_id=p_payroll_action_id
360 and decode(pay_core_utils.get_parameter('SS_PREF',ppa.legislative_parameters),'N',ss_pref(pac.assignment_id),'N') ='N'
361 ;
362
363
364 rec_print_options csr_get_print_options%ROWTYPE;
365 l_assignment_exist number; /* Bug#5743270 */
366 Function get_sort_order_value(l_passed_sort_order in varchar2)
367 return varchar2 is
368 l_sort_order varchar2(40);
369 begin
370 if l_passed_sort_order = 'EMPLOYEE_TYPE'
371 then l_sort_order := 'employee_type';
372 elsif l_passed_sort_order = 'ASSIGNMENT_LOCATION'
373 then l_sort_order := 'assignment_location';
374 elsif l_passed_sort_order = 'EMPLOYEE_NUMBER'
375 then l_sort_order := 'employee_number';
376 elsif l_passed_sort_order = 'PAYROLL'
377 then l_sort_order := 'payroll';
378 elsif l_passed_sort_order = 'EMPLOYEE_SURNAME'
379 then l_sort_order := 'employee_last_name';
380 else
381 l_sort_order:=null;
382 end if;
383 return l_sort_order;
384 end get_sort_order_value;
385
386
387 Begin
388 ps_request_id := -1;
389
390 Begin
391 LOOP
392 l_count := l_count + 1;
393 hr_utility.set_location('Before payroll action' , 25);
394 hr_utility.set_location('mag_internal ' || pay_mag_tape.internal_prm_names(l_count) , 105);
395 hr_utility.set_location('mag_internal ' || pay_mag_tape.internal_prm_values(l_count) , 115);
396 l_passed_sort_order:=pay_mag_tape.internal_prm_names(l_count);
397 IF pay_mag_tape.internal_prm_names(l_count) = 'TRANSFER_PAYROLL_ACTION_ID'
398 THEN
399 l_payroll_action_id := to_number(pay_mag_tape.internal_prm_values(l_count));
400 hr_utility.set_location('payroll_action ',0);
401 ELSIF l_passed_sort_order= 'SORT_ORDER1'
402 THEN
403 l_sort_order1 := pay_mag_tape.internal_prm_values(l_count);
404 hr_utility.set_location('in sort order1 ',1);
405 ELSIF l_passed_sort_order= 'SORT_ORDER2'
406 THEN
407 l_sort_order2 := pay_mag_tape.internal_prm_values(l_count);
408 hr_utility.set_location('in sort_order 2 ',2);
409 ELSIF l_passed_sort_order= 'SORT_ORDER3'
410 THEN
411 hr_utility.set_location('in sort_order3 ',3);
412 l_sort_order3 := pay_mag_tape.internal_prm_values(l_count);
413 ELSIF l_passed_sort_order= 'SORT_ORDER4'
414 THEN
415 l_sort_order4 := pay_mag_tape.internal_prm_values(l_count);
416 hr_utility.set_location('in sort_orderr4 ',4);
417 /* Bug 3132172 Duplex Printing Support*/
418 ELSIF pay_mag_tape.internal_prm_names(l_count) = 'DUPLEX_PRINT_FLAG'
419 THEN
420 l_duplex_print_flag := pay_mag_tape.internal_prm_values(l_count);
421 hr_utility.set_location('in duplex_print_flag',5);
422 /* Bug 4859876 - Template Code for PDF Output */
423 ELSIF pay_mag_tape.internal_prm_names(l_count) = 'TMPL'
424 THEN
425 l_template_name := pay_mag_tape.internal_prm_values(l_count);
426 hr_utility.set_location('in Template Names'||l_template_name,6);
427 END IF;
428
429 END LOOP;
430 EXCEPTION
431 WHEN no_data_found THEN
432 -- Use this exception to exit loop as no. of plsql tab items
433 -- is not known beforehand. All values should be assigned.
434 NULL;
435 WHEN value_error THEN
436 NULL;
437 End;
438
439
440 l_sort_order1:=get_sort_order_value(l_sort_order1);
441 hr_utility.set_location('getting sort_order1'||l_sort_order1, 121);
442 l_sort_order2:=get_sort_order_value(l_sort_order2);
443 hr_utility.set_location('getting sort_order2'||l_sort_order2, 122);
444 l_sort_order3:=get_sort_order_value(l_sort_order3);
445 hr_utility.set_location('getting sort_order3'||l_sort_order3, 123);
446 l_sort_order4:=get_sort_order_value(l_sort_order4);
447 hr_utility.set_location('getting sort_order4'||l_sort_order4, 124);
448
449
450
451 hr_utility.set_location('getting current chunk_number ', 125);
452
453 OPEN csr_get_current_chunk_number(l_payroll_action_id);
454 fetch csr_get_current_chunk_number into l_current_chunk_number;
455 CLOSE csr_get_current_chunk_number;
456
457 /* Bug#5743270
458 Cursor checks whether any assignment exist for which Printed Payment Summary(PUI) need to be produced
459 */
460 OPEN csr_is_assignemnt_exist(l_payroll_action_id);
461 fetch csr_is_assignemnt_exist into l_assignment_exist;
462 CLOSE csr_is_assignemnt_exist;
463
464
465 if l_current_chunk_number <> 0 and l_assignment_exist > 0
466 then
467
468 hr_utility.set_location('Afer payroll action ' || l_payroll_action_id , 125);
469 --hr_utility.set_location('sort ' || l_sort_order,166);
470 hr_utility.set_location('Before calling report',24);
471
472 OPEN csr_get_print_options(l_payroll_action_id);
473 FETCH csr_get_print_options INTO rec_print_options;
474 CLOSE csr_get_print_options;
475 --
476 l_print_together := nvl(fnd_profile.value('CONC_PRINT_TOGETHER'), 'N');
477 --
478 -- Set printer options
479 l_print_return := fnd_request.set_print_options
480 (printer => rec_print_options.printer,
481 style => rec_print_options.print_style,
482 copies => rec_print_options.number_of_copies, /* Bug: 3768288 */
483 save_output => hr_general.char_to_bool(rec_print_options.save_output),
484 print_together => l_print_together);
485 -- Submit report
486 hr_utility.set_location('payroll_action id '|| l_payroll_action_id,25);
487
488 /* Bug 4891196 - Determine Report to be submitted,
489 i. If Template is Null, then Postscript output
490 ii. If Template is NOT Null, then XML/PDF output.
491 */
492
493 if l_template_name is NULL
494 then
495 l_program_name := 'PYAUPSRP_PS';
496 else
497 l_program_name := 'PYAUPSRP';
498 end if;
499
500 ps_request_id := fnd_request.submit_request
501 ('PAY',
502 l_program_name, -- Bug 4891196
503 null,
504 null,
505 false,
506 'P_PAYROLL_ACTION_ID='||to_char(l_payroll_action_id),
507 'P_SORT_ORDER1='||l_sort_order1,
508 'P_SORT_ORDER2='||l_sort_order2,
509 'P_SORT_ORDER3='||l_sort_order3,
510 'P_SORT_ORDER4='||l_sort_order4,
511 'P_DUPLEX_PRINT_FLAG='|| l_duplex_print_flag, -- Bug 3132172
512 'P_TEMPLATE_NAME='||l_template_name, -- Bug 4859876
513 'BLANKPAGES=NO',
514 NULL, NULL, NULL,
515 NULL, NULL, NULL, NULL, NULL, NULL,
516 NULL, NULL, NULL, NULL, NULL, NULL,
517 NULL, NULL, NULL, NULL, NULL, NULL,
518 NULL, NULL, NULL, NULL, NULL, NULL,
519 NULL, NULL, NULL, NULL, NULL, NULL,
520 NULL, NULL, NULL, NULL, NULL, NULL,
521 NULL, NULL, NULL, NULL, NULL, NULL,
522 NULL, NULL, NULL, NULL, NULL, NULL,
523 NULL, NULL, NULL, NULL, NULL, NULL,
524 NULL, NULL, NULL, NULL, NULL, NULL,
525 NULL, NULL, NULL, NULL, NULL, NULL,
526 NULL, NULL, NULL, NULL, NULL, NULL,
527 NULL, NULL, NULL, NULL, NULL, NULL,
528 NULL, NULL, NULL, NULL, NULL, NULL,
529 NULL, NULL, NULL, NULL, NULL
530 );
531
532 hr_utility.set_location('After calling report',24);
533
534
535 end if;
536
537 hr_utility.set_location('Before calling formula',22);
538
539 OPEN csr_get_formula_id('AU_PS_REPORT');
540 FETCH csr_get_formula_id INTO l_formula_id;
541 CLOSE csr_get_formula_id;
542
543 pay_mag_tape.internal_prm_names(1) := 'NO_OF_PARAMETERS';
544 pay_mag_tape.internal_prm_values(1) := '5';
545 pay_mag_tape.internal_prm_names(2) := 'NEW_FORMULA_ID';
546 pay_mag_tape.internal_prm_values(2) := to_char(l_formula_id);
547 pay_mag_tape.internal_prm_names(3) := 'PS_REQUEST_ID';
548 pay_mag_tape.internal_prm_values(3) := to_char(ps_request_id);
549 pay_mag_tape.internal_prm_names(4) := 'PAYROLL_ACTION_ID';
550 pay_mag_tape.internal_prm_values(4) := to_char(l_payroll_action_id);
551 pay_mag_tape.internal_prm_names(5) := 'SORT_ORDER1';
552 pay_mag_tape.internal_prm_values(5) :=l_sort_order1;
553 pay_mag_tape.internal_prm_names(6) := 'SORT_ORDER2';
554 pay_mag_tape.internal_prm_values(6) :=l_sort_order2;
555 pay_mag_tape.internal_prm_names(7) := 'SORT_ORDER3';
556 pay_mag_tape.internal_prm_values(7) :=l_sort_order3;
557 pay_mag_tape.internal_prm_names(8) := 'SORT_ORDER4';
558 pay_mag_tape.internal_prm_values(8) :=l_sort_order4;
559 -- hr_utility.trace_off;
560 end spawn_archive_reports;
561
562 ---
563 -- Bug 4726357 Added to check whether Self Service Option is enabled for the employee
564 ---
565
566 function ss_pref(p_assignemnt_id per_assignments_f.assignment_id%type) return varchar2
567 is
568
569 l_bg_id number;
570 l_loc_id number;
571 l_org_id number;
572 l_person_id number;
573 l_online_opt char(1);
574
575 /* Cursor to get the business group id, location id, organization id and person id */
576 cursor asg_info is
577 select paf.business_group_id, paf.location_id, paf.organization_id,paf.person_id
578 from per_assignments_f paf
579 where paf.assignment_id = p_assignemnt_id
580 and paf.effective_start_date =
581 (select max(effective_start_date)
582 from per_assignments_f paf2
583 where paf2.assignment_id = paf.assignment_id
584 );
585
586 /* Cursor to get the option sets at different level i.e Person level, Location Level, Organization Level and
587 Business group level. The cursor fetches option in hierarchy . The person level will override location.
588 Location overrides HR Organization, and HR Organization overrides the option defined at Business Group */
589
590 cursor ss_pref (c_bg_id number,c_loc_id number,c_org_id number, c_person_id number)
591 is
592 SELECT online_opt
593 FROM
594 (
595 Select PEI_INFORMATION2 online_opt, 1 sort_col
596 from PER_PEOPLE_EXTRA_INFO ppit
597 where ppit.person_id=c_person_id
598 and ppit.pei_information1= 'PAYMENTSUMMARY'
599 and ppit.information_type='HR_SELF_SERVICE_PER_PREFERENCE'
600 union
601 Select LEI_INFORMATION2 online_opt, 2 sort_col
602 FROM hr_location_extra_info hlei
603 WHERE hlei.location_id = c_loc_id
604 And hlei.lei_information1= 'PAYMENTSUMMARY'
605 AND hlei.information_type = 'HR_SELF_SERVICE_LOC_PREFERENCE'
606 UNION
607 SELECT org_information2 online_opt,
608 3 sort_col
609 FROM hr_organization_information hoi
610 WHERE hoi.organization_id = c_org_id
611 and hoi.org_information1 = 'PAYMENTSUMMARY'
612 AND hoi.org_information_context = 'HR_SELF_SERVICE_ORG_PREFERENCE'
613 UNION
614 SELECT org_information2 online_opt,
615 4 sort_col
616 FROM hr_organization_information hoi
617 WHERE hoi.organization_id = c_bg_id
618 And hoi.org_information1 = 'PAYMENTSUMMARY'
619 AND hoi.org_information_context = 'HR_SELF_SERVICE_BG_PREFERENCE'
620 )
621 WHERE online_opt IS NOT NULL
622 ORDER BY sort_col;
623 Begin
624 open asg_info;
625 fetch asg_info into l_bg_id,l_loc_id,l_org_id,l_person_id;
626 close asg_info;
627
628
629 open ss_pref (l_bg_id,l_loc_id,l_org_id,l_person_id);
630 fetch ss_pref into l_online_opt;
631
632 /*If no option set at any level online option will be set as No */
633 if ss_pref%NOTFOUND THEN
634 l_online_opt := 'N';
635 end if;
636 close ss_pref;
637
638 return l_online_opt;
639 end;
640
641 END pay_au_payment_summary_report;