DBA Data[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;