DBA Data[Home] [Help]

PACKAGE BODY: APPS.PAY_AU_RECONCILIATION_PKG

Source


1 package body pay_au_reconciliation_pkg as
2 /* $Header: pyaurecs.pkb 120.12.12020000.2 2012/07/06 11:30:54 dduvvuri ship $ */
3 
4 g_debug boolean;
5 g_package  varchar2(26);
6 
7 /* Bug 4036052
8    Initalize variables for Archive model -- Start */
9 
10   g_arc_payroll_action_id           pay_payroll_actions.payroll_action_id%type;
11   g_business_group_id		    hr_all_organization_units.organization_id%type;
12   g_prev_assignment_id              number;
13   g_def_bal_populted                varchar2(1);
14 
15   g_end_date                        date;
16   g_start_date                        date;   --Bug#3662449
17 
18 /*
19     Bug 9113084 - Added Function range_person_on
20 --------------------------------------------------------------------
21     Name  : range_person_on
22     Type  : Function
23     Access: Private
24     Description: Checks if RANGE_PERSON_ID is enabled for
25                  Archive process.
26   --------------------------------------------------------------------
27 */
28 
29 FUNCTION range_person_on
30 RETURN BOOLEAN
31 IS
32 
33  CURSOR csr_action_parameter is
34   select parameter_value
35   from pay_action_parameters
36   where parameter_name = 'RANGE_PERSON_ID';
37 
38  CURSOR csr_range_format_param is
39   select par.parameter_value
40   from   pay_report_format_parameters par,
41          pay_report_format_mappings_f map
42   where  map.report_format_mapping_id = par.report_format_mapping_id
43   and    map.report_type = 'AU_REC_SUMM_ARCHIVE'
44   and    map.report_format = 'AU_REC_SUMM_ARCHIVE'
45   and    map.report_qualifier = 'AU'
46   and    par.parameter_name = 'RANGE_PERSON_ID';
47 
48   l_return boolean;
49   l_action_param_val varchar2(30);
50   l_report_param_val varchar2(30);
51 
52 BEGIN
53 
54     g_debug := hr_utility.debug_enabled;
55 
56   BEGIN
57 
58     open csr_action_parameter;
59     fetch csr_action_parameter into l_action_param_val;
60     close csr_action_parameter;
61 
62     open csr_range_format_param;
63     fetch csr_range_format_param into l_report_param_val;
64     close csr_range_format_param;
65 
66   EXCEPTION WHEN NO_DATA_FOUND THEN
67      l_return := FALSE;
68   END;
69   --
70   IF l_action_param_val = 'Y' AND l_report_param_val = 'Y' THEN
71      l_return := TRUE;
72      IF g_debug THEN
73         hr_utility.set_location('Range Person = True',1);
74      END IF;
75   ELSE
76      l_return := FALSE;
77   END IF;
78 --
79  RETURN l_return;
80 --
81 END range_person_on;
82 
83   /* Procedure to pass all the balance results back in one call from report */
84 
85 procedure get_au_rec_balances
86   (p_assignment_action_id      	in pay_assignment_actions.assignment_action_id%type,
87    p_registered_employer        in NUMBER, --2610141
88    p_gross_earnings             out NOCOPY number,  /*Bug 3953706*/
89    p_non_taxable_earnings       out NOCOPY number,
90    p_pre_tax_deductions         out NOCOPY number, /*Bug 3953706*/
91    p_taxable_earnings           out NOCOPY number,
92    p_tax    			out NOCOPY number,
93    p_deductions			out NOCOPY number,
94    p_direct_payments            out NOCOPY number, /*Bug 3953706*/
95    p_net_payment        	out NOCOPY number,
96    p_employer_charges 		out NOCOPY number)
97 is
98 
99 /* start bug8682256 */
100 cursor get_aud_precision is
101 select nvl(precision,0) from fnd_currencies where currency_code = 'AUD' and enabled_flag = 'Y';
102 
103 l_aud_precision number;
104 /* end bug8682256 */
105 
106 begin
107 
108    IF g_debug THEN
109       hr_utility.trace('Entering:' || g_package  || 'get_au_rec_balances');
110       hr_utility.trace('Assignment action id value ===>' || p_assignment_action_id);
111    END IF;
112 
113     /* Call to this function below implements Batch Balance Retrieval for better performance */
114 
115 /*Changes made for bug 2610141 Start here*/
116      g_context_table(1).tax_unit_id := p_registered_employer;
117 
118      pay_balance_pkg.get_value(p_assignment_action_id => p_assignment_action_id,
119                                p_defined_balance_lst=>g_balance_value_tab,
120                                p_context_lst =>g_context_table,
121                                p_output_table=>g_result_table);
122 
123 /*Changes made for bug 2610141 Ends here*/
124 
125 /*   pay_balance_pkg.get_value ( p_assignment_action_id => p_assignment_action_id
126                              , p_defined_balance_lst  => g_balance_value_tab );*/
127 
128    IF g_debug THEN
129       hr_utility.trace('Balance values for RUN dimension');
130       hr_utility.trace('-------------------------------------');
131       hr_utility.trace('Earnings_Total        ===>' || g_result_table(1).balance_value);
132       hr_utility.trace('Direct Payments       ===>' || g_result_table(2).balance_value);
133       hr_utility.trace('Termination_Payments  ===>' || g_result_table(3).balance_value);
134       hr_utility.trace('Involuntary Deductions===>' || g_result_table(4).balance_value);
135       hr_utility.trace('Pre Tax Deductions    ===>' || g_result_table(5).balance_value);
136       hr_utility.trace('Termination Deductions===>' || g_result_table(6).balance_value);
137       hr_utility.trace('Voluntary Deductions  ===>' || g_result_table(7).balance_value);
138       hr_utility.trace('Total_Tax_Deduction   ===>' || g_result_table(8).balance_value);
139       hr_utility.trace('Earnings_Non_Taxable  ===>' || g_result_table(9).balance_value);
140       hr_utility.trace('Employer_Charges      ===>' || g_result_table(10).balance_value);
141    END IF;
142 
143    /* Bug 3953706 - Modified calculation of Earnings and deductions as given below:
144       Calculations :-
145       ===============
146       Gross Earnings       = Earnings Total + Termination Payments + Pre Tax Deductions
147       Taxable_earnings     = Gross Earnings - Pre_tax_deductions - Earnings_non_taxable
148       Non_taxable_earnings = Earnings_non_taxable
149       Post Tax Deductions  = Involuntary_deductions + Voluntary_deductions
150       Tax Deductions       = Tax_deductions + Termination_deductions
151       Net_payment          = Taxable_earnings + Non_taxable_earnings - Tax - Deductions + Direct_Payments
152       Direct Payments      = Direct_Payments
153       Employer_charges     = Employer_charges
154       Pre Tax Deductions   = Pre_tax_deductions */
155 
156 
157 
158    p_gross_earnings       := g_result_table(1).balance_value + g_result_table(3).balance_value + g_result_table(5).balance_value;
159    p_non_taxable_earnings := g_result_table(9).balance_value;
160    p_pre_tax_deductions    := g_result_table(5).balance_value;
161    p_taxable_earnings     := p_gross_earnings - p_pre_tax_deductions - p_non_taxable_earnings;
162    p_tax                  := g_result_table(8).balance_value + g_result_table(6).balance_value;
163    p_deductions           := g_result_table(4).balance_value + g_result_table(7).balance_value;
164    p_direct_payments      := g_result_table(2).balance_value;
165    p_net_payment          := p_taxable_earnings + p_non_taxable_earnings - p_tax - p_deductions + p_direct_payments;
166    p_employer_charges     := g_result_table(10).balance_value;
167 
168 /* start bug8682256 */
169    open get_aud_precision;
170    fetch get_aud_precision into l_aud_precision;
171    close get_aud_precision;
172 
173    IF l_aud_precision > 2 THEN
174       p_gross_earnings := round(p_gross_earnings,2);
175       p_non_taxable_earnings := round(p_non_taxable_earnings,2);
176       p_pre_tax_deductions := round(p_pre_tax_deductions,2);
177       p_taxable_earnings := round(p_taxable_earnings,2);
178       p_tax := round(p_tax,2);
179       p_deductions := round(p_deductions,2);
180       p_direct_payments := round(p_direct_payments,2);
181       p_net_payment := round(p_net_payment,2);
182       p_employer_charges := round(p_employer_charges,2);
183    END IF;
184 /* end bug8682256 */
185 
186    IF g_debug THEN
187       hr_utility.trace('p_taxable_earnings     ===>' || p_taxable_earnings);
188       hr_utility.trace('p_non_taxable_earnings ===>' || p_non_taxable_earnings);
189       hr_utility.trace('p_deductions           ===>' || p_deductions);
190       hr_utility.trace('p_tax                  ===>' || p_tax);
191       hr_utility.trace('p_net_payment          ===>' || p_net_payment);
192       hr_utility.trace('p_employer_charges     ===>' || p_employer_charges);
193       hr_utility.trace('p_gross_earnings     ===>' || p_gross_earnings);
194       hr_utility.trace('p_pre_tax_deduction     ===>' || p_pre_tax_deductions);
195       hr_utility.trace('p_direct_payments     ===>' || p_direct_payments);
196    END IF;
197 
198 
199 end get_au_rec_balances;
200 
201 
202   /* Procedure to pass all the YTD balance results back in one call from report */
203 
204 procedure get_ytd_au_rec_balances
205   (p_assignment_action_id      	in pay_assignment_actions.assignment_action_id%type,
206    p_registered_employer        in NUMBER, --2610141
207    p_ytd_gross_earnings         out NOCOPY number,   /*Bug 3953706*/
208    p_ytd_non_taxable_earnings   out NOCOPY number,
209    p_ytd_pre_tax_deductions     out NOCOPY number,   /*Bug 3953706*/
210    p_ytd_taxable_earnings       out NOCOPY number,
211    p_ytd_tax    		out NOCOPY number,
212    p_ytd_deductions		out NOCOPY number,
213    p_ytd_direct_payments        out NOCOPY number,   /*Bug 3953706*/
214    p_ytd_net_payment        	out NOCOPY number,
215    p_ytd_employer_charges 	out NOCOPY number)
216 is
217 
218 /* start bug8682256 */
219 cursor get_aud_precision is
220 select nvl(precision,0) from fnd_currencies where currency_code = 'AUD' and enabled_flag = 'Y';
221 
222 l_aud_precision number;
223 /* end bug8682256 */
224 
225 begin
226 
227    IF g_debug THEN
228       hr_utility.trace('Entering:' || g_package  || 'get_ytd_au_rec_balances');
229       hr_utility.trace('Assignment action id value ===>' || p_assignment_action_id);
230    END IF;
231 
232    /* Call to this function below implements Batch Balance Retrieval for better performance */
233 
234 /*Changes made for bug 2610141 Start here*/
235      g_context_table(1).tax_unit_id := p_registered_employer;
236 
240                                p_output_table=>g_result_table);
237      pay_balance_pkg.get_value(p_assignment_action_id => p_assignment_action_id,
238                                p_defined_balance_lst=>g_ytd_balance_value_tab, /*Bug 4040688*/
239                                p_context_lst =>g_context_table,
241 
242 /*Changes made for bug 2610141 Ends here*/
243 
244 /*   pay_balance_pkg.get_value ( p_assignment_action_id => p_assignment_action_id
245                              , p_defined_balance_lst  => g_balance_value_tab );*/
246 
247     IF g_debug THEN
248       hr_utility.trace('Balance values for YTD dimension');
249       hr_utility.trace('-------------------------------------');
250       hr_utility.trace('Earnings_Total        ===>' || g_result_table(1).balance_value);
251       hr_utility.trace('Direct Payments       ===>' || g_result_table(2).balance_value);
252       hr_utility.trace('Termination_Payments  ===>' || g_result_table(3).balance_value);
253       hr_utility.trace('Involuntary Deductions===>' || g_result_table(4).balance_value);
254       hr_utility.trace('Pre Tax Deductions    ===>' || g_result_table(5).balance_value);
255       hr_utility.trace('Termination Deductions===>' || g_result_table(6).balance_value);
256       hr_utility.trace('Voluntary Deductions  ===>' || g_result_table(7).balance_value);
257       hr_utility.trace('Total_Tax_Deduction   ===>' || g_result_table(8).balance_value);
258       hr_utility.trace('Earnings_Non_Taxable  ===>' || g_result_table(9).balance_value);
259       hr_utility.trace('Employer_Charges      ===>' || g_result_table(10).balance_value);
260    END IF;
261 
262    /* Bug 3953706 - Modified calculation of Earnings and deductions as given below:
263       Calculations :-
264       ===============
265       Gross Earnings       = Earnings Total + Termination Payments + Pre Tax Deductions
266       Taxable_earnings     = Gross Earnings - Pre_tax_deductions - Earnings_non_taxable
267       Non_taxable_earnings = Earnings_non_taxable
268       Post Tax Deductions  = Involuntary_deductions + Voluntary_deductions
269       Tax Deductions       = Tax_deductions + Termination_deductions
270       Net_payment          = Taxable_earnings + Non_taxable_earnings - Tax - Deductions + Direct_Payments
271       Direct Payments      = Direct_Payments
272       Employer_charges     = Employer_charges
273       Pre Tax Deductions   = Pre_tax_deductions */
274 
275    p_ytd_gross_earnings       := g_result_table(1).balance_value + g_result_table(3).balance_value + g_result_table(5).balance_value;
276    p_ytd_non_taxable_earnings := g_result_table(9).balance_value;
277    p_ytd_pre_tax_deductions    := g_result_table(5).balance_value;
278    p_ytd_taxable_earnings     := p_ytd_gross_earnings - p_ytd_pre_tax_deductions - p_ytd_non_taxable_earnings;
279    p_ytd_tax                  := g_result_table(8).balance_value + g_result_table(6).balance_value;
280    p_ytd_deductions           := g_result_table(4).balance_value + g_result_table(7).balance_value;
281    p_ytd_direct_payments      := g_result_table(2).balance_value;
282    p_ytd_net_payment          := p_ytd_taxable_earnings + p_ytd_non_taxable_earnings - p_ytd_tax - p_ytd_deductions + p_ytd_direct_payments;
283    p_ytd_employer_charges     := g_result_table(10).balance_value;
284 
285 /* start bug8682256 */
286    open get_aud_precision;
287    fetch get_aud_precision into l_aud_precision;
288    close get_aud_precision;
289 
290    IF l_aud_precision > 2 THEN
291       p_ytd_gross_earnings := round(p_ytd_gross_earnings,2);
292       p_ytd_non_taxable_earnings := round(p_ytd_non_taxable_earnings,2);
293       p_ytd_pre_tax_deductions := round(p_ytd_pre_tax_deductions,2);
294       p_ytd_taxable_earnings := round(p_ytd_taxable_earnings,2);
295       p_ytd_tax := round(p_ytd_tax,2);
296       p_ytd_deductions := round(p_ytd_deductions,2);
297       p_ytd_direct_payments := round(p_ytd_direct_payments,2);
298       p_ytd_net_payment := round(p_ytd_net_payment,2);
299       p_ytd_employer_charges := round(p_ytd_employer_charges,2);
300    END IF;
301 /* end bug8682256 */
302 
303    IF g_debug THEN
304       hr_utility.trace('p_ytd_taxable_earnings     ===>' || p_ytd_taxable_earnings);
305       hr_utility.trace('p_ytd_non_taxable_earnings ===>' || p_ytd_non_taxable_earnings);
306       hr_utility.trace('p_ytd_deductions           ===>' || p_ytd_deductions);
307       hr_utility.trace('p_ytd_tax                  ===>' || p_ytd_tax);
308       hr_utility.trace('p_ytd_net_payment          ===>' || p_ytd_net_payment);
309       hr_utility.trace('p_ytd_employer_charges     ===>' || p_ytd_employer_charges);
310       hr_utility.trace('p_ytd_gross_earnings     ===>' || p_ytd_gross_earnings);
311       hr_utility.trace('p_ytd_pre_tax_deduction     ===>' || p_ytd_pre_tax_deductions);
312       hr_utility.trace('p_ytd_direct_payments     ===>' || p_ytd_direct_payments);
313    END IF;
314 
315 end get_ytd_au_rec_balances;
316 
317 
318 PROCEDURE populate_defined_balance_ids
319           (p_ytd_totals varchar2,
320 	   p_registered_employer NUMBER)   IS --2610141
321 
322 CURSOR   csr_defined_balance_id
323           (c_database_item_suffix  pay_balance_dimensions.database_item_suffix%type)
324 IS
325 SELECT   decode(pbt.balance_name,'Earnings_Total',1,'Direct Payments',2,'Termination_Payments',3,
326                 'Involuntary Deductions',4,'Pre Tax Deductions',5,'Termination Deductions',6,
327                 'Voluntary Deductions',7,'Total_Tax_Deductions',8,'Earnings_Non_Taxable',9,
328                 'Employer_Charges',10) sort_index,
329          pdb.defined_balance_id defined_balance_id
330   FROM   pay_balance_types pbt,
331          pay_balance_dimensions pbd,
332          pay_defined_balances pdb
333  WHERE   pbt.balance_name         IN ('Earnings_Total','Direct Payments','Termination_Payments','Involuntary Deductions',
334                                       'Pre Tax Deductions','Termination Deductions','Voluntary Deductions','Total_Tax_Deductions',
338    AND   pbd.balance_dimension_id = pdb.balance_dimension_id
335                                       'Earnings_Non_Taxable','Employer_Charges')
336    AND   pbd.database_item_suffix = c_database_item_suffix
337    AND   pbt.balance_type_id      = pdb.balance_type_id
339    AND   pbt.legislation_code     = 'AU'
340 ORDER BY sort_index;
341 
342 i NUMBER := 0;
343 l_run_dimension_name VARCHAR2(15);--2610141
344 l_ytd_dimension_name VARCHAR2(15);--2610141
345 
346 BEGIN
347 
348    IF g_debug THEN
349       hr_utility.trace('Entering:' || g_package  || 'populate_defined_balance_ids');
350       hr_utility.trace('Parameter p_ytd_totals value ===>' || p_ytd_totals);
351    END IF;
352 
353    g_balance_value_tab.delete;
354 
355 /*Bug 2610141 - Code added to pick up the right dimension on the basis of input parameters for the report*/
356    IF p_registered_employer IS NULL THEN
357 	l_run_dimension_name := '_ASG_RUN';
358 	l_ytd_dimension_name := '_ASG_YTD';
359    ELSE
360         l_run_dimension_name := '_ASG_LE_RUN';
361 	l_ytd_dimension_name := '_ASG_LE_YTD';
362    END IF;
363 
364   /* The Balance's defined balance id are stored in the following order
365      -----------------------------------------------------
366         Storage Location of
367        Run Defined Balance Id      Balance Name
368      -----------------------------------------------------
369             1                   Earnings_Total
370             2                   Direct Payments
371             3                   Termination_Payments
372             4                   Involuntary Deductions
373             5                   Pre Tax Deductions
374             6                   Termination Deductions
375             7                   Voluntary Deductions
376             8                   Total_Tax_Deduction
377             9                   Earnings_Non_Taxable
378             10                  Employer_Charges
379      -----------------------------------------------------
380      If required, YTD defined balance ids are stored for all the balances in the same order as mentioned above
381      from location 10 to 19 */
382 
383    FOR csr_rec IN csr_defined_balance_id(l_run_dimension_name)
384       LOOP
385          g_balance_value_tab(csr_rec.sort_index).defined_balance_id := csr_rec.defined_balance_id;
386       END LOOP;
387 
388    IF g_debug THEN
389       hr_utility.trace('Defined Balance ids for RUN dimension');
390       hr_utility.trace('-------------------------------------');
391       hr_utility.trace('Earnings_Total        ===>' || g_balance_value_tab(1).defined_balance_id);
392       hr_utility.trace('Direct Payments       ===>' || g_balance_value_tab(2).defined_balance_id);
393       hr_utility.trace('Termination_Payments  ===>' || g_balance_value_tab(3).defined_balance_id);
394       hr_utility.trace('Involuntary Deductions===>' || g_balance_value_tab(4).defined_balance_id);
395       hr_utility.trace('Pre Tax Deductions    ===>' || g_balance_value_tab(5).defined_balance_id);
396       hr_utility.trace('Termination Deductions===>' || g_balance_value_tab(6).defined_balance_id);
397       hr_utility.trace('Voluntary Deductions  ===>' || g_balance_value_tab(7).defined_balance_id);
398       hr_utility.trace('Total_Tax_Deduction   ===>' || g_balance_value_tab(8).defined_balance_id);
399       hr_utility.trace('Earnings_Non_Taxable  ===>' || g_balance_value_tab(9).defined_balance_id);
400       hr_utility.trace('Employer_Charges      ===>' || g_balance_value_tab(10).defined_balance_id);
401    END IF;
402 
403    IF (p_ytd_totals = 'Y') THEN
404 
405       FOR csr_rec IN csr_defined_balance_id(l_ytd_dimension_name)
406          LOOP
407             g_ytd_balance_value_tab(csr_rec.sort_index).defined_balance_id := csr_rec.defined_balance_id; /*Bug 4040688*/
408          END LOOP;
409 
410       IF g_debug THEN
411          hr_utility.trace('Defined Balance ids for YTD dimension');
412          hr_utility.trace('-------------------------------------');
413          hr_utility.trace('Earnings_Total        ===>' || g_ytd_balance_value_tab(1).defined_balance_id);
414          hr_utility.trace('Direct Payments       ===>' || g_ytd_balance_value_tab(2).defined_balance_id);
415          hr_utility.trace('Termination_Payments  ===>' || g_ytd_balance_value_tab(3).defined_balance_id);
416          hr_utility.trace('Involuntary Deductions===>' || g_ytd_balance_value_tab(4).defined_balance_id);
417          hr_utility.trace('Pre Tax Deductions    ===>' || g_ytd_balance_value_tab(5).defined_balance_id);
418          hr_utility.trace('Termination Deductions===>' || g_ytd_balance_value_tab(6).defined_balance_id);
419          hr_utility.trace('Voluntary Deductionsn ===>' || g_ytd_balance_value_tab(7).defined_balance_id);
420          hr_utility.trace('Total_Tax_Deduction   ===>' || g_ytd_balance_value_tab(8).defined_balance_id);
421          hr_utility.trace('Earnings_Non_Taxable  ===>' || g_ytd_balance_value_tab(9).defined_balance_id);
422          hr_utility.trace('Employer_Charges      ===>' || g_ytd_balance_value_tab(10).defined_balance_id);
423       END IF;
424 
425    END IF;
426 
427 END;
428 
429 /* Bug 4036052
430 * Implemented the Horizontal Archive for Payroll Rec- Summary Report
431 * Procedures
432 * 1. range_code
433 * 2. assignment_action_code
434 * 3. archive_code
435 * 4. spawn_archive_reports
436 */
437 
438   procedure range_code
439   (p_payroll_action_id  in  pay_payroll_actions.payroll_action_id%type
440   ,p_sql                out NOCOPY varchar2
441   ) is
442 
443   l_procedure         varchar2(200) ;
444 
445   begin
446 
447     g_debug :=hr_utility.debug_enabled ;
448 
449     if g_debug then
450      l_procedure := g_package||'range_code';
451      hr_utility.set_location('Entering '||l_procedure,1);
452     end if ;
453 
454     -- Archive the payroll action level data  and EIT defintions.
455     --  sql string to SELECT a range of assignments eligible for archival.
456     p_sql := ' select distinct p.person_id'                             ||
457              ' from   per_people_f p,'                                  ||
458                     ' pay_payroll_actions pa'                           ||
459              ' where  pa.payroll_action_id = :payroll_action_id'        ||
460              ' and    p.business_group_id = pa.business_group_id'       ||
461              ' order by p.person_id';
462 
463     if g_debug then
464       hr_utility.set_location('Leaving '||l_procedure,1000);
465     end if;
466 
467   end range_code;
468 
469 procedure initialization_code
470   (p_payroll_action_id  in pay_payroll_actions.payroll_action_id%type)
471   is
472     l_procedure               varchar2(200) ;
473 
474 /*Bug 4132149 - Modification begins here */
475   --------------------------------------------------------------------+
476   -- Cursor      : csr_params
477   -- Description : Fetches User Parameters from Legislative_paramters
478   --               column.
479   --------------------------------------------------------------------+
480 
481    CURSOR csr_params(c_payroll_action_id  pay_payroll_actions.payroll_action_id%TYPE)
482       IS
483         SELECT pay_core_utils.get_parameter('PAY',legislative_parameters)        payroll_id,
484                    pay_core_utils.get_parameter('ORG',legislative_parameters)           org_id,
485                    pay_core_utils.get_parameter('BG',legislative_parameters)    business_group_id,
486                    to_date(pay_core_utils.get_parameter('SDATE',legislative_parameters),'YYYY/MM/DD') start_date,
487                    to_date(pay_core_utils.get_parameter('EDATE',legislative_parameters),'YYYY/MM/DD')   end_date,
488                    pay_core_utils.get_parameter('PACTID',legislative_parameters)        pact_id,
489                    pay_core_utils.get_parameter('LE',legislative_parameters) legal_employer,
490                    pay_core_utils.get_parameter('ASG',legislative_parameters) assignment_id,
491                    pay_core_utils.get_parameter('SO1',legislative_parameters)   sort_order_1,
492                    pay_core_utils.get_parameter('SO2',legislative_parameters)   sort_order_2,
493                    pay_core_utils.get_parameter('SO3',legislative_parameters)   sort_order_3,
494                    pay_core_utils.get_parameter('SO4',legislative_parameters)   sort_order_4,
495                    to_date(pay_core_utils.get_parameter('PEDATE',legislative_parameters),'YYYY/MM/DD') period_end_date,
496                    pay_core_utils.get_parameter('YTD_TOT',legislative_parameters)      ytd_totals,
497                    pay_core_utils.get_parameter('ZERO_REC',legislative_parameters)    zero_records,
498                    pay_core_utils.get_parameter('NEG_REC',legislative_parameters)     negative_records,
499                    decode(pay_core_utils.get_parameter('EMP_TYPE',legislative_parameters),'C','Y','T','N','%') employee_type,
500                    pay_core_utils.get_parameter('DEL_ACT',legislative_parameters)     delete_actions /*Bug# 4142159*/
501                    FROM pay_payroll_actions ppa
502       WHERE ppa.payroll_action_id  =  c_payroll_action_id;
503 
504  --------------------------------------------------------------------+
505   -- Cursor      : csr_period_date_earned
506   -- Description : Fetches Date Earned for a given payroll
507   --               run.
508   --------------------------------------------------------------------+
509       CURSOR csr_period_date_earned(c_payroll_action_id  pay_payroll_actions.payroll_action_id%TYPE)
510       IS
511         SELECT ppa.date_earned
512 	FROM pay_payroll_actions ppa
513         WHERE
514 	ppa.payroll_action_id = c_payroll_action_id;
515 
516 /*Bug 4132149 - Modification ends here */
517 
518   begin
519 
520     g_debug :=hr_utility.debug_enabled ;
521     if g_debug then
522         g_package := 'pay_au_reconciliation_pkg.' ;
523         l_procedure := g_package||'initialization_code';
524         hr_utility.set_location('Entering '||l_procedure,1);
525     end if;
526 
527 /*Bug 4132149 - Modification begins here */
528 
529     -- initialization_code to to set the global tables for EIT
530         -- that will be used by each thread in multi-threading.
531 
532     g_arc_payroll_action_id := p_payroll_action_id;
533 
534     -- Fetch the parameters by user passed into global variable.
535 
536         OPEN csr_params(p_payroll_action_id);
537      	FETCH csr_params into g_parameters;
538        	CLOSE csr_params;
539 
540 
541     if g_debug then
542         hr_utility.set_location('p_payroll_action_id.........= ' || p_payroll_action_id,30);
543         hr_utility.set_location('g_parameters.business_group_id.........= ' || g_parameters.business_group_id,30);
544         hr_utility.set_location('g_parameters.payroll_id..............= ' || g_parameters.payroll_id,30);
545         hr_utility.set_location('g_parameters.org_id................= ' || g_parameters.org_id,30);
546         hr_utility.set_location('g_parameters.legal_employer.........= ' || g_parameters.legal_employer,30);
547         hr_utility.set_location('g_parameters.start_date..............= ' || g_parameters.start_date,30);
548         hr_utility.set_location('g_parameters.end_date................= ' || g_parameters.end_date,30);
549         hr_utility.set_location('g_parameters.period_end_date.........= ' || g_parameters.period_end_date,30);
550         hr_utility.set_location('g_parameters.pact_id..............= ' || g_parameters.pact_id,30);
551         hr_utility.set_location('g_parameters.employee_type..........= '||g_parameters.employee_type,30);
552         hr_utility.set_location('g_parameters.sort_order1..........= '||g_parameters.sort_order_1,30);
553         hr_utility.set_location('g_parameters.sort_order2..........= '||g_parameters.sort_order_2,30);
554         hr_utility.set_location('g_parameters.sort_order3..........= '||g_parameters.sort_order_3,30);
555         hr_utility.set_location('g_parameters.sort_order4..........= '||g_parameters.sort_order_4,30);
556 	hr_utility.set_location('g_parameters.delete_actions..........= '||g_parameters.delete_actions,30);/*Bug# 4142159*/
557     end if;
558 
559 
560     g_business_group_id := g_parameters.business_group_id ;
561 
562     -- Set end date variable .This value is used to fetch latest assignment details of
563     -- employee for archival.In case of archive start date/end date - archive end date
564     -- taken and pact_id/period_end_date , period end date is picked.
565 
566     if g_parameters.end_date is not null
567     then
568         g_end_date := g_parameters.end_date;
569 	g_start_date := g_parameters.start_date; --Bug#3662449
570     else
571         if g_parameters.period_end_date is not null
572         then
573 	    open csr_period_date_earned(g_parameters.pact_id); --Bug#3662449
574 	    fetch csr_period_date_earned into g_start_date;
575             close csr_period_date_earned;
576             g_end_date  := g_parameters.period_end_date;
577         else
578 	    g_start_date := to_date('1900/01/01','YYYY/MM/DD');  --Bug#3662449
579             g_end_date  := to_date('4712/12/31','YYYY/MM/DD');
580         end if;
581     end if; /* End of outer if loop */
582 
583 /*Bug 4132149 - Modification ends here */
584 
585     pay_au_reconciliation_pkg.populate_defined_balance_ids('Y',g_parameters.legal_employer);
586     if g_debug then
587             hr_utility.set_location('Leaving '||l_procedure,1000);
588     end if;
589 
590   exception
591     when others then
592       hr_utility.set_location('Error in '||l_procedure,999999);
593       raise;
597   -- Name  : assignment_Action_code
594   end initialization_code;
595 
596   --------------------------------------------------------------------+
598   -- Type  : Procedure
599   -- Access: Public
600   -- This procedure further restricts the assignment_id's
601   -- returned by range_code
602   -- This procedure gets the parameters given by user and restricts
603   -- the assignments to be archived.
604   -- it then calls hr_nonrun.insact to create an assignment action id
605   -- it then archives Payroll Run assignment action id  details
606   -- in pay_Action_information with context 'AU_ARCHIVE_ASG_DETAILS'
607   -- for each assignment.
608   -- There are 10 different cursors for choosing the assignment ids.
609   -- Depending on the parameters passed,the appropriate cursor is used.
610   --------------------------------------------------------------------+
611 
612 procedure assignment_action_code
613   (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type
614   ,p_start_person      in per_all_people_f.person_id%type
615   ,p_end_person        in per_all_people_f.person_id%type
616   ,p_chunk             in number
617   ) is
618 
619   --------------------------------------------------------------------+
620   -- Cursor      : csr_assignment_org_period
621   -- Description : Fetches assignments when Organization,Archive
622   --               Start Date and End Date is specified
623   --------------------------------------------------------------------+
624 
625   cursor csr_assignment_org_period
626       (c_payroll_action_id  pay_payroll_actions.payroll_action_id%type
627       ,c_start_person       per_all_people_f.person_id%type
628       ,c_end_person         per_all_people_f.person_id%type
629       ,c_employee_type      per_all_people_f.current_employee_flag%type
630       ,c_business_group_id  hr_all_organization_units.organization_id%type
631       ,c_organization_id    hr_all_organization_units.organization_id%type
632       ,c_archive_start_date         date
633       ,c_archive_end_date           date
634        ) is
635       select 	paa.assignment_action_id,
636                 paa.action_sequence,
637       	   	paaf.assignment_id,
638       	   	paa.tax_unit_id
639        	from  	per_people_f pap,
640   		per_assignments_f paaf,
641   		pay_payroll_actions ppa,
642   		pay_payroll_actions ppa1,
643   		pay_assignment_actions paa,
644   		hr_organization_units hou,
645   		per_periods_of_service pps
646   	where   ppa.payroll_action_id        = c_payroll_action_id
647   	and     paa.assignment_id            = paaf.assignment_id
648   	and     pap.person_id                between c_start_person and c_end_person
649   	and     pap.person_id                = paaf.person_id
650   	and     pap.person_id                = pps.person_id
651   	and     pps.period_of_service_id     = paaf.period_of_service_id
652   	and     ppa1.date_earned between pap.effective_start_date  and pap.effective_end_date
653   	and    ppa1.payroll_action_id       = paa.payroll_action_id
654 	AND    paa.action_status ='C' /*Bug 4099317*/
655   	and    ppa1.business_group_id       = ppa.business_group_id
656   	and    ppa.business_group_id        = c_business_group_id
657   	and    ppa1.action_type             in ('R','Q','I','B','V')
658 	and    paa.source_action_id is null
659   	and    paaf.organization_id         = hou.organization_id
660   	and    hou.business_group_id        = c_business_group_id
661   	and    hou.organization_id          = c_organization_id
662   	and    ppa1.effective_date   between c_archive_start_date and c_archive_end_date
663        and   decode(pps.actual_termination_date,null,'Y',decode(sign(pps.actual_termination_date - (c_archive_end_date)),1,'Y','N')) LIKE c_employee_type  --Bug 4161540
664         and   paaf.effective_end_date = (select max(effective_end_date) --Bug# 3538810
665 					From  per_assignments_f iipaf
666 					WHERE iipaf.assignment_id  = paaf.assignment_id
667 					and iipaf.effective_end_date >= c_archive_start_date
668 					and iipaf.effective_start_date <= c_archive_end_date)
669   	order  by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
670 
671   --------------------------------------------------------------------+
672   -- Bug         : 9113084
673   -- Cursor      : rg_csr_assignment_org_period
674   -- Description : Fetches assignments when Organization,Archive
675   --               Start Date and End Date is specified
676   -- Usage       : When Range Person is enabled
677   --------------------------------------------------------------------+
678     cursor rg_csr_assignment_org_period
679       (c_payroll_action_id  pay_payroll_actions.payroll_action_id%type
680       ,c_chunk NUMBER
681       ,c_employee_type      per_all_people_f.current_employee_flag%type
682       ,c_business_group_id  hr_all_organization_units.organization_id%type
683       ,c_organization_id    hr_all_organization_units.organization_id%type
684       ,c_archive_start_date         date
685       ,c_archive_end_date           date
686        ) is
687       select 	paa.assignment_action_id,
688                 paa.action_sequence,
689       	   	paaf.assignment_id,
690       	   	paa.tax_unit_id
691        	from  	per_people_f pap,
692   		per_assignments_f paaf,
693   		pay_payroll_actions ppa,
694   		pay_payroll_actions ppa1,
695   		pay_assignment_actions paa,
696   		hr_organization_units hou,
697   		per_periods_of_service pps,
698 		pay_population_ranges ppr
699   	where   ppa.payroll_action_id        = c_payroll_action_id
700 	and     ppr.payroll_action_id       = ppa.payroll_action_id
701 	and     ppr.chunk_number = c_chunk
702   	and     paa.assignment_id            = paaf.assignment_id
703   	and     pap.person_id                = ppr.person_id
704   	and     pap.person_id                = paaf.person_id
705   	and     pap.person_id                = pps.person_id
709 	AND    paa.action_status ='C'
706   	and     pps.period_of_service_id     = paaf.period_of_service_id
707   	and     ppa1.date_earned between pap.effective_start_date  and pap.effective_end_date
708   	and    ppa1.payroll_action_id       = paa.payroll_action_id
710   	and    ppa1.business_group_id       = ppa.business_group_id
711   	and    ppa.business_group_id        = c_business_group_id
712   	and    ppa1.action_type             in ('R','Q','I','B','V')
713 	and    paa.source_action_id is null
714   	and    paaf.organization_id         = hou.organization_id
715   	and    hou.business_group_id        = c_business_group_id
716   	and    hou.organization_id          = c_organization_id
717   	and    ppa1.effective_date   between c_archive_start_date and c_archive_end_date
718        and   decode(pps.actual_termination_date,null,'Y',decode(sign(pps.actual_termination_date - (c_archive_end_date)),1,'Y','N')) LIKE c_employee_type  --Bug 4161540
719         and   paaf.effective_end_date = (select max(effective_end_date)
720 					From  per_assignments_f iipaf
721 					WHERE iipaf.assignment_id  = paaf.assignment_id
722 					and iipaf.effective_end_date >= c_archive_start_date
723 					and iipaf.effective_start_date <= c_archive_end_date)
724   	order  by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
725 
726   --------------------------------------------------------------------+
727   -- Cursor      : csr_assignment_org_run
728   -- Description : Fetches assignments when Organization,Payroll Run
729   --               and Period End Date is specified
730   --------------------------------------------------------------------+
731 
732   cursor csr_assignment_org_run
733       (c_payroll_action_id  pay_payroll_actions.payroll_action_id%type
734       ,c_start_person       per_all_people_f.person_id%type
735       ,c_end_person         per_all_people_f.person_id%type
736       ,c_employee_type      per_all_people_f.current_employee_flag%type
737       ,c_business_group_id  hr_all_organization_units.organization_id%type
738       ,c_organization_id    hr_all_organization_units.organization_id%type
739       ,c_period_end_date            date
740       ,c_pact_id            pay_payroll_actions.payroll_action_id%type
741       ) is
742       select 	paa.assignment_action_id,
743                 paa.action_sequence,
744           	   	paaf.assignment_id,
745           	   	paa.tax_unit_id
746            	from  	per_people_f pap,
747       		per_assignments_f paaf,
748       		pay_payroll_actions ppa,
749       		pay_payroll_actions ppa1,
750       		pay_assignment_actions paa,
751       		hr_organization_units hou,
752       		per_periods_of_service pps
753       	where   ppa.payroll_action_id        = c_payroll_action_id
754       	and     paa.assignment_id            = paaf.assignment_id
755       	and     pap.person_id                between c_start_person and c_end_person
756       	and     pap.person_id                = paaf.person_id
757       	and     pap.person_id                = pps.person_id
758       	and     pps.period_of_service_id     = paaf.period_of_service_id
759   	and     ppa1.date_earned between paaf.effective_start_date and paaf.effective_end_date
760   	and     ppa1.date_earned between pap.effective_start_date  and pap.effective_end_date
761       	and    ppa1.payroll_action_id       = paa.payroll_action_id
762 	AND    paa.action_status ='C' /*Bug 4099317*/
763       	and    ppa1.business_group_id       = ppa.business_group_id
764       	and    ppa.business_group_id        = c_business_group_id
765       	and    ppa1.action_type             in ('R','Q','I','B','V')
766 	and    paa.source_action_id is null
767       	and    paaf.organization_id         = hou.organization_id
768   	and    hou.business_group_id        = c_business_group_id
769       	and    NVL(pap.current_employee_flag,'N') like c_employee_type
770       	and    hou.organization_id          = c_organization_id
771       	and    ppa1.payroll_action_id       = c_pact_id
772       	order  by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
773 
774   --------------------------------------------------------------------+
775   -- Bug         : 9113084
776   -- Cursor      : rg_csr_assignment_org_run
777   -- Description : Fetches assignments when Organization,Payroll Run
778   --               and Period End Date is specified
779   -- Usage       : When Range Person is enabled
780   --------------------------------------------------------------------+
781   cursor rg_csr_assignment_org_run
782       (c_payroll_action_id  pay_payroll_actions.payroll_action_id%type
783       ,c_chunk NUMBER
784       ,c_employee_type      per_all_people_f.current_employee_flag%type
785       ,c_business_group_id  hr_all_organization_units.organization_id%type
786       ,c_organization_id    hr_all_organization_units.organization_id%type
787       ,c_period_end_date            date
788       ,c_pact_id            pay_payroll_actions.payroll_action_id%type
789       ) is
790       select 	paa.assignment_action_id,
791                 paa.action_sequence,
792           	   	paaf.assignment_id,
793           	   	paa.tax_unit_id
794            	from  	per_people_f pap,
795       		per_assignments_f paaf,
796       		pay_payroll_actions ppa,
797       		pay_payroll_actions ppa1,
798       		pay_assignment_actions paa,
799       		hr_organization_units hou,
800       		per_periods_of_service pps,
801 		pay_population_ranges ppr
802       	where   ppa.payroll_action_id        = c_payroll_action_id
803 	and     ppr.payroll_action_id = ppa.payroll_action_id
804 	and     ppr.chunk_number = c_chunk
805       	and     paa.assignment_id            = paaf.assignment_id
806       	and     pap.person_id                = ppr.person_id
807       	and     pap.person_id                = paaf.person_id
808       	and     pap.person_id                = pps.person_id
809       	and     pps.period_of_service_id     = paaf.period_of_service_id
810   	and     ppa1.date_earned between paaf.effective_start_date and paaf.effective_end_date
811   	and     ppa1.date_earned between pap.effective_start_date  and pap.effective_end_date
812       	and    ppa1.payroll_action_id       = paa.payroll_action_id
813 	AND    paa.action_status ='C'
814       	and    ppa1.business_group_id       = ppa.business_group_id
815       	and    ppa.business_group_id        = c_business_group_id
816       	and    ppa1.action_type             in ('R','Q','I','B','V')
817 	and    paa.source_action_id is null
818       	and    paaf.organization_id         = hou.organization_id
819   	and    hou.business_group_id        = c_business_group_id
820       	and    NVL(pap.current_employee_flag,'N') like c_employee_type
821       	and    hou.organization_id          = c_organization_id
822       	and    ppa1.payroll_action_id       = c_pact_id
823       	order  by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
824 
825   --------------------------------------------------------------------+
826   -- Cursor      : csr_assignment_legal_period
827   -- Description : Fetches assignments when Legal Employer,Archive
828   --               Start Date and End Date is specified
829   --------------------------------------------------------------------+
830 /*Bug 3935471 - modified cursor to return the assignment action id and
831                 tax unit of the master wherever there is a master-child
832                 relationship*/
833 
834   cursor csr_assignment_legal_period
835       (c_payroll_action_id  pay_payroll_actions.payroll_action_id%type
836       ,c_start_person       per_all_people_f.person_id%type
837       ,c_end_person         per_all_people_f.person_id%type
838       ,c_employee_type      per_all_people_f.current_employee_flag%type
839       ,c_business_group_id  hr_all_organization_units.organization_id%type
840       ,c_legal_employer     hr_all_organization_units.organization_id%type
841       ,c_archive_start_date         date
842       ,c_archive_end_date           date
843       ) is
844       select 	paa.assignment_action_id,
845                 paa.action_sequence,
846       	   	paaf.assignment_id,
847       	   	paa.tax_unit_id,
848            		paa.source_action_id master_action_id, /*Bug# 3935471*/
849          		paa2.tax_unit_id master_tax_unit_id /*Bug# 3935471*/
850        	from  	per_people_f pap,
851   		per_assignments_f paaf,
852   		pay_payroll_actions ppa,
853   		pay_payroll_actions ppa1,
854   		pay_assignment_actions paa,
855 		pay_assignment_actions paa2, /*Bug# 3935471*/
856   		per_periods_of_service pps
857 	where   ppa.payroll_action_id        = c_payroll_action_id
858   	and     paa.assignment_id            = paaf.assignment_id /*Bug# 3935471*/
859    AND     paa2.assignment_id           = paa.assignment_id
860   	and     pap.person_id                between c_start_person and c_end_person
861   	and     pap.person_id                = paaf.person_id
862   	and     pap.person_id                = pps.person_id
863   	and     pps.period_of_service_id     = paaf.period_of_service_id
864   	and     ppa1.date_earned between pap.effective_start_date  and pap.effective_end_date
865   	and    ppa1.payroll_action_id       = paa.payroll_action_id
866   	and    ppa1.payroll_action_id       = paa2.payroll_action_id /*Bug# 3935471*/
867 	AND    paa2.action_status ='C' /*Bug# 3935471*/
868 	AND    paa.action_status ='C' /*Bug 4099317*/
869 	AND    paa2.assignment_action_id = nvl(paa.source_action_id, paa.assignment_action_id) /*Bug# 3935471*/
870   	and    ppa1.business_group_id       = ppa.business_group_id
871   	and    ppa.business_group_id        = c_business_group_id
872   	and    ppa1.action_type             in ('R','Q','I','B','V')
873   	and    paa.tax_unit_id              = c_legal_employer
874   	and    ppa1.effective_date  between c_archive_start_date and c_archive_end_date
875         and   decode(pps.actual_termination_date,null,'Y',decode(sign(pps.actual_termination_date - (c_archive_end_date)),1,'Y','N')) LIKE c_employee_type  --Bug 4161540
876         and   paaf.effective_end_date = (select max(effective_end_date) --Bug# 3538810
877 					From  per_assignments_f iipaf
878 					WHERE iipaf.assignment_id  = paaf.assignment_id
879 					and iipaf.effective_end_date >= c_archive_start_date
880 					and iipaf.effective_start_date <= c_archive_end_date)
881   	order  by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id, paa.source_action_id, paa2.tax_unit_id; /*Bug# 3935471*/
882 
883   --------------------------------------------------------------------+
884   -- Bug         : 9113084
885   -- Cursor      : rg_csr_assignment_legal_period
886   -- Description : Fetches assignments when Legal Employer,Archive
887   --               Start Date and End Date is specified
888   -- Usage       : When Range Person is enabled
889   --------------------------------------------------------------------+
890   /* 14218576 - Added LEADING and INDEX Hints to the cursor to improve performance */
891 
892   cursor rg_csr_assignment_legal_period
893       (c_payroll_action_id  pay_payroll_actions.payroll_action_id%type
894        ,c_chunk NUMBER
895       ,c_employee_type      per_all_people_f.current_employee_flag%type
896       ,c_business_group_id  hr_all_organization_units.organization_id%type
897       ,c_legal_employer     hr_all_organization_units.organization_id%type
898       ,c_archive_start_date         date
899       ,c_archive_end_date           date
900       ) is
901       select    /*+ LEADING (ppa ppr pap paaf pps) INDEX(paaf PER_ASSIGNMENTS_F_N12) */
902                 paa.assignment_action_id,
903                 paa.action_sequence,
904                 paaf.assignment_id,
905                 paa.tax_unit_id,
906                 paa.source_action_id master_action_id,
907                 paa2.tax_unit_id master_tax_unit_id
908         from    per_people_f pap,
909                 per_assignments_f paaf,
910                 pay_payroll_actions ppa,
911                 pay_payroll_actions ppa1,
912                 pay_assignment_actions paa,
913                 pay_assignment_actions paa2,
914                 per_periods_of_service pps,
915                 pay_population_ranges ppr
916         where   ppa.payroll_action_id        = c_payroll_action_id
917         and     ppr.payroll_action_id = ppa.payroll_action_id
918         and     ppr.chunk_number = c_chunk
919         and     paa.assignment_id            = paaf.assignment_id
920    AND     paa2.assignment_id           = paa.assignment_id
921         and     pap.person_id                = ppr.person_id
922         and     pap.person_id                = paaf.person_id
923         and     pap.person_id                = pps.person_id
924         and     pps.period_of_service_id     = paaf.period_of_service_id
925         and     ppa1.date_earned between pap.effective_start_date  and pap.effective_end_date
926         and    ppa1.payroll_action_id       = paa.payroll_action_id
927         and    ppa1.payroll_action_id       = paa2.payroll_action_id
928         AND    paa2.action_status ='C'
929         AND    paa.action_status ='C'
930         AND    paa2.assignment_action_id = nvl(paa.source_action_id, paa.assignment_action_id)
931         and    ppa1.business_group_id       = ppa.business_group_id
932         and    ppa.business_group_id        = c_business_group_id
933         and    ppa1.action_type             in ('R','Q','I','B','V')
934         and    paa.tax_unit_id              = c_legal_employer
935         and    ppa1.effective_date  between c_archive_start_date and c_archive_end_date
936         and   decode(pps.actual_termination_date,null,'Y',decode(sign(pps.actual_termination_date - (c_archive_end_date)),1,'Y','N')) LIKE c_employee_type  --Bug 4161540
937         and   paaf.effective_end_date = (select max(effective_end_date)
938                                         From  per_assignments_f iipaf
939                                         WHERE iipaf.assignment_id  = paaf.assignment_id
940                                         and iipaf.effective_end_date >= c_archive_start_date
941                                         and iipaf.effective_start_date <= c_archive_end_date)
942         order  by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id, paa.source_action_id, paa2.tax_unit_id; /*Bug# 3935471*/
943 
944 
945   --------------------------------------------------------------------+
946   -- Cursor      : csr_assignment_legal_run
947   -- Description : Fetches assignments when Legal Employer,Payroll Run
948   --               and Period End Date is specified
949   --------------------------------------------------------------------+
950 /*Bug 3935471 - modified cursor to return the assignment action id and
951                 tax unit of the master wherever there is a master-child
952                 relationship*/
953 
954     cursor csr_assignment_legal_run
955       (c_payroll_action_id  pay_payroll_actions.payroll_action_id%type
956       ,c_start_person       per_all_people_f.person_id%type
957       ,c_end_person         per_all_people_f.person_id%type
958       ,c_employee_type      per_all_people_f.current_employee_flag%type
959       ,c_business_group_id  hr_all_organization_units.organization_id%type
960       ,c_legal_employer     hr_all_organization_units.organization_id%type
961       ,c_period_end_date            date
962       ,c_pact_id            pay_payroll_actions.payroll_action_id%type
963       ) is
964       select 	paa.assignment_action_id,
965                 paa.action_sequence,
966       	   	paaf.assignment_id,
967       	   	paa.tax_unit_id,
968          		paa.source_action_id master_action_id, /*Bug# 3935471*/
969          		paa2.tax_unit_id master_tax_unit_id /*Bug# 3935471*/
970        	from  	per_people_f pap,
971               		per_assignments_f paaf,
972               		pay_payroll_actions ppa,
973               		pay_payroll_actions ppa1,
974               		pay_assignment_actions paa,
975             		pay_assignment_actions paa2, /*Bug# 3935471*/
976               		per_periods_of_service pps
977          where   ppa.payroll_action_id        = c_payroll_action_id
978          and     paa.assignment_id            = paaf.assignment_id
979          AND     paa2.assignment_id           = paa.assignment_id /*Bug# 3935471*/
980          and     pap.person_id                between c_start_person and c_end_person
981          and     pap.person_id                = paaf.person_id
982          and     pap.person_id                = pps.person_id
983          and     pps.period_of_service_id     = paaf.period_of_service_id
984          and     ppa1.date_earned between paaf.effective_start_date and paaf.effective_end_date
985          and     ppa1.date_earned between pap.effective_start_date  and pap.effective_end_date
986          and    ppa1.payroll_action_id       = paa.payroll_action_id
987          AND    ppa1.payroll_action_id       = paa2.payroll_action_id /*Bug# 3935471*/
988          AND    paa2.assignment_action_id = nvl(paa.source_action_id, paa.assignment_action_id) /*Bug# 3935471*/
989          AND    paa.action_status ='C' /*Bug 4099317*/
990          AND    paa2.action_status = 'C' /*Bug# 3935471*/
991          and    ppa1.business_group_id       = ppa.business_group_id
992          and    ppa.business_group_id        = c_business_group_id
993          and    ppa1.action_type             in ('R','Q','I','B','V')
994          and    NVL(pap.current_employee_flag,'N') like c_employee_type
995          and    paa.tax_unit_id              = c_legal_employer
996          and    ppa1.payroll_action_id       = c_pact_id
997          order  by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id, paa.source_action_id, paa2.tax_unit_id; /*Bug# 3935471*/
998 
999   --------------------------------------------------------------------+
1000   -- Bug         : 9113084
1001   -- Cursor      : rg_csr_assignment_legal_run
1002   -- Description : Fetches assignments when Legal Employer,Payroll Run
1003   --               and Period End Date is specified
1004   -- Usage       : When Range Person is enabled
1005   --------------------------------------------------------------------+
1006     cursor rg_csr_assignment_legal_run
1007       (c_payroll_action_id  pay_payroll_actions.payroll_action_id%type
1008       ,c_chunk NUMBER
1009       ,c_employee_type      per_all_people_f.current_employee_flag%type
1010       ,c_business_group_id  hr_all_organization_units.organization_id%type
1011       ,c_legal_employer     hr_all_organization_units.organization_id%type
1012       ,c_period_end_date            date
1013       ,c_pact_id            pay_payroll_actions.payroll_action_id%type
1014       ) is
1015       select 	paa.assignment_action_id,
1016                 paa.action_sequence,
1017       	   	paaf.assignment_id,
1018       	   	paa.tax_unit_id,
1019          		paa.source_action_id master_action_id,
1020          		paa2.tax_unit_id master_tax_unit_id
1021        	from  	per_people_f pap,
1022               		per_assignments_f paaf,
1023               		pay_payroll_actions ppa,
1024               		pay_payroll_actions ppa1,
1025               		pay_assignment_actions paa,
1026             		pay_assignment_actions paa2,
1027               		per_periods_of_service pps,
1028 			pay_population_ranges ppr
1029          where   ppa.payroll_action_id        = c_payroll_action_id
1030 	 and     ppr.payroll_action_id = ppa.payroll_action_id
1031 	 and     ppr.chunk_number = c_chunk
1032          and     paa.assignment_id            = paaf.assignment_id
1033          AND     paa2.assignment_id           = paa.assignment_id
1034          and     pap.person_id                = ppr.person_id
1035          and     pap.person_id                = paaf.person_id
1036          and     pap.person_id                = pps.person_id
1037          and     pps.period_of_service_id     = paaf.period_of_service_id
1038          and     ppa1.date_earned between paaf.effective_start_date and paaf.effective_end_date
1039          and     ppa1.date_earned between pap.effective_start_date  and pap.effective_end_date
1040          and    ppa1.payroll_action_id       = paa.payroll_action_id
1041          AND    ppa1.payroll_action_id       = paa2.payroll_action_id
1042          AND    paa2.assignment_action_id = nvl(paa.source_action_id, paa.assignment_action_id)
1043          AND    paa.action_status ='C'
1044          AND    paa2.action_status = 'C'
1045          and    ppa1.business_group_id       = ppa.business_group_id
1046          and    ppa.business_group_id        = c_business_group_id
1047          and    ppa1.action_type             in ('R','Q','I','B','V')
1048          and    NVL(pap.current_employee_flag,'N') like c_employee_type
1049          and    paa.tax_unit_id              = c_legal_employer
1050          and    ppa1.payroll_action_id       = c_pact_id
1051          order  by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id, paa.source_action_id, paa2.tax_unit_id; /*Bug# 3935471*/
1052 
1053     --------------------------------------------------------------------+
1054     -- Cursor      : csr_assignment_payroll_period
1055     -- Description : Fetches assignments when Payroll,Archive Start
1056     --               Date and End Date is specified
1057     --------------------------------------------------------------------+
1058 
1059     cursor csr_assignment_payroll_period
1060       (c_payroll_action_id  pay_payroll_actions.payroll_action_id%type
1061       ,c_start_person       per_all_people_f.person_id%type
1062       ,c_end_person         per_all_people_f.person_id%type
1063       ,c_employee_type      per_all_people_f.current_employee_flag%type
1064       ,c_business_group_id  hr_all_organization_units.organization_id%type
1065       ,c_payroll_id         pay_payroll_actions.payroll_id%type
1066       ,c_archive_start_date         date
1067       ,c_archive_end_date           date
1068       ) is
1069        select 	paa.assignment_action_id,
1070                 paa.action_sequence,
1071       	   	paaf.assignment_id,
1072       	   	paa.tax_unit_id
1076   		pay_payroll_actions ppa1,
1073        	from  	per_people_f pap,
1074   		per_assignments_f paaf,
1075   		pay_payroll_actions ppa,
1077   		pay_assignment_actions paa,
1078   		per_periods_of_service pps
1079   	where   ppa.payroll_action_id        = c_payroll_action_id
1080   	and     paa.assignment_id            = paaf.assignment_id
1081   	and     pap.person_id                between c_start_person and c_end_person
1082   	and     pap.person_id                = paaf.person_id
1083   	and     pap.person_id                = pps.person_id
1084   	and     pps.period_of_service_id     = paaf.period_of_service_id
1085   	and     ppa1.date_earned between pap.effective_start_date  and pap.effective_end_date
1086   	and    ppa1.payroll_action_id       = paa.payroll_action_id
1087 	AND    paa.action_status ='C' /*Bug 4099317*/
1088   	and    ppa1.business_group_id       = ppa.business_group_id
1089   	and    ppa.business_group_id        = c_business_group_id
1090   	and    ppa1.action_type             in ('R','Q','I','B','V')
1091 	and    paa.source_action_id is null
1092   	and    ppa1.effective_date  between c_archive_start_date and c_archive_end_date
1093         AND    paaf.payroll_id              = c_payroll_id /*Bug 4040688*/
1094         and   decode(pps.actual_termination_date,null,'Y',decode(sign(pps.actual_termination_date - (c_archive_end_date)),1,'Y','N')) LIKE c_employee_type  --Bug 4161540
1095         and   paaf.effective_end_date = (select max(effective_end_date) --Bug# 3538810
1096 					From  per_assignments_f iipaf
1097 					WHERE iipaf.assignment_id  = paaf.assignment_id
1098 					and iipaf.effective_end_date >= c_archive_start_date
1099 					and iipaf.effective_start_date <= c_archive_end_date
1100                AND iipaf.payroll_id IS NOT NULL) /*Bug#4688800*/
1101   	order  by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
1102 
1103     --------------------------------------------------------------------+
1104     -- Bug         : 9113084
1105     -- Cursor      : rg_assignment_payroll_period
1106     -- Description : Fetches assignments when Payroll,Archive Start
1107     --               Date and End Date is specified
1108     -- Usage       : When Range Person is enabled
1109     --------------------------------------------------------------------+
1110    /* Bug 9871560 - Added LEADING and INDEX hints to improve the performance of the cursor query. */
1111 
1112     cursor rg_assignment_payroll_period
1113       (c_payroll_action_id  pay_payroll_actions.payroll_action_id%type
1114       , c_chunk NUMBER
1115       ,c_employee_type      per_all_people_f.current_employee_flag%type
1116       ,c_business_group_id  hr_all_organization_units.organization_id%type
1117       ,c_payroll_id         pay_payroll_actions.payroll_id%type
1118       ,c_archive_start_date         date
1119       ,c_archive_end_date           date
1120       ) is
1121        select 	/*+ LEADING (ppa ppr pap paaf pps) INDEX(paaf PER_ASSIGNMENTS_F_N12)*/
1122                 paa.assignment_action_id,
1123                 paa.action_sequence,
1124       	   	paaf.assignment_id,
1125       	   	paa.tax_unit_id
1126        	from  	pay_payroll_actions ppa,
1127 		pay_population_ranges ppr,
1128  	        per_people_f pap,
1129   		per_assignments_f paaf,
1130   		per_periods_of_service pps,
1131   		pay_assignment_actions paa,
1132 		pay_payroll_actions ppa1
1133   	where   ppa.payroll_action_id        = c_payroll_action_id
1134 	and     ppr.payroll_action_id = ppa.payroll_action_id
1135 	and ppr.chunk_number = c_chunk
1136   	and     paa.assignment_id            = paaf.assignment_id
1137   	and     pap.person_id                = ppr.person_id
1138   	and     pap.person_id                = paaf.person_id
1139   	and     pap.person_id                = pps.person_id
1140   	and     pps.period_of_service_id     = paaf.period_of_service_id
1141   	and     ppa1.date_earned between pap.effective_start_date  and pap.effective_end_date
1142   	and    ppa1.payroll_action_id       = paa.payroll_action_id
1143 	AND    paa.action_status ='C'
1144   	and    ppa1.business_group_id       = ppa.business_group_id
1145   	and    ppa.business_group_id        = c_business_group_id
1146   	and    ppa1.action_type             in ('R','Q','I','B','V')
1147 	and    paa.source_action_id is null
1148   	and    ppa1.effective_date  between c_archive_start_date and c_archive_end_date
1149         AND    paaf.payroll_id              = c_payroll_id
1150         and   decode(pps.actual_termination_date,null,'Y',decode(sign(pps.actual_termination_date - (c_archive_end_date)),1,'Y','N')) LIKE c_employee_type  --Bug 4161540
1151         and   paaf.effective_end_date = (select max(effective_end_date)
1152 					From  per_assignments_f iipaf
1153 					WHERE iipaf.assignment_id  = paaf.assignment_id
1154 					and iipaf.effective_end_date >= c_archive_start_date
1155 					and iipaf.effective_start_date <= c_archive_end_date
1156                AND iipaf.payroll_id IS NOT NULL)
1157   	order  by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
1158 
1159 
1160   --------------------------------------------------------------------+
1161   -- Cursor      : csr_assignment_payroll_run
1162   -- Description : Fetches assignments when Payroll,Payroll Run
1163   --               and Period End Date is specified
1164   --------------------------------------------------------------------+
1165 
1166   cursor csr_assignment_payroll_run
1167       (c_payroll_action_id  pay_payroll_actions.payroll_action_id%type
1168       ,c_start_person       per_all_people_f.person_id%type
1169       ,c_end_person         per_all_people_f.person_id%type
1170       ,c_employee_type      per_all_people_f.current_employee_flag%type
1171       ,c_business_group_id  hr_all_organization_units.organization_id%type
1172       ,c_payroll_id         pay_payroll_actions.payroll_id%type
1173       ,c_period_end_date            date
1174       ,c_pact_id            pay_payroll_actions.payroll_action_id%type
1175       ) is
1176       select      paa.assignment_action_id,
1177                 paa.action_sequence,
1178           	   	paaf.assignment_id,
1179           	   	paa.tax_unit_id
1180            	from  	per_people_f pap,
1181       		per_assignments_f paaf,
1182       		pay_payroll_actions ppa,
1183       		pay_payroll_actions ppa1,
1184       		pay_assignment_actions paa,
1185        		per_periods_of_service pps
1186       	where   ppa.payroll_action_id        = c_payroll_action_id
1187       	and     paa.assignment_id            = paaf.assignment_id
1188       	and     pap.person_id                between c_start_person and c_end_person
1189       	and     pap.person_id                = paaf.person_id
1190       	and     pap.person_id                = pps.person_id
1191       	and     pps.period_of_service_id     = paaf.period_of_service_id
1192   	and     ppa1.date_earned between paaf.effective_start_date and paaf.effective_end_date
1193   	and     ppa1.date_earned between pap.effective_start_date  and pap.effective_end_date
1194       	and    ppa1.payroll_action_id       = paa.payroll_action_id
1195 	AND    paa.action_status ='C' /*Bug 4099317*/
1196       	and    ppa1.business_group_id       = ppa.business_group_id
1197       	and    ppa.business_group_id        = c_business_group_id
1198       	and    ppa1.action_type             in ('R','Q','I','B','V')
1199 	and    paa.source_action_id is null
1200       	and    NVL(pap.current_employee_flag,'N') like c_employee_type
1201       	and    ppa1.payroll_id              = c_payroll_id
1202       	and    ppa1.payroll_action_id       = c_pact_id
1203       	order  by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
1204 
1205   --------------------------------------------------------------------+
1206   -- Bug         : 9113084
1207   -- Cursor      : rg_csr_assignment_payroll_run
1208   -- Description : Fetches assignments when Payroll,Payroll Run
1209   --               and Period End Date is specified
1210   -- Usage       : When Range Person is enabled
1211   --------------------------------------------------------------------+
1212   cursor rg_csr_assignment_payroll_run
1213       (c_payroll_action_id  pay_payroll_actions.payroll_action_id%type
1214       , c_chunk number
1215       ,c_employee_type      per_all_people_f.current_employee_flag%type
1216       ,c_business_group_id  hr_all_organization_units.organization_id%type
1217       ,c_payroll_id         pay_payroll_actions.payroll_id%type
1218       ,c_period_end_date            date
1219       ,c_pact_id            pay_payroll_actions.payroll_action_id%type
1220       ) is
1221       select      paa.assignment_action_id,
1222                 paa.action_sequence,
1223           	   	paaf.assignment_id,
1224           	   	paa.tax_unit_id
1225            	from  	per_people_f pap,
1226       		per_assignments_f paaf,
1227       		pay_payroll_actions ppa,
1228       		pay_payroll_actions ppa1,
1229       		pay_assignment_actions paa,
1230        		per_periods_of_service pps,
1231 		pay_population_ranges ppr
1232       	where   ppa.payroll_action_id        = c_payroll_action_id
1233 	and     ppr.payroll_action_id = ppa.payroll_action_id
1234 	and     ppr.chunk_number = c_chunk
1235       	and     paa.assignment_id            = paaf.assignment_id
1236       	and     pap.person_id                = ppr.person_id
1237       	and     pap.person_id                = paaf.person_id
1238       	and     pap.person_id                = pps.person_id
1239       	and     pps.period_of_service_id     = paaf.period_of_service_id
1240   	and     ppa1.date_earned between paaf.effective_start_date and paaf.effective_end_date
1241   	and     ppa1.date_earned between pap.effective_start_date  and pap.effective_end_date
1242       	and    ppa1.payroll_action_id       = paa.payroll_action_id
1243 	AND    paa.action_status ='C'
1244       	and    ppa1.business_group_id       = ppa.business_group_id
1245       	and    ppa.business_group_id        = c_business_group_id
1246       	and    ppa1.action_type             in ('R','Q','I','B','V')
1247 	and    paa.source_action_id is null
1248       	and    NVL(pap.current_employee_flag,'N') like c_employee_type
1249       	and    ppa1.payroll_id              = c_payroll_id
1250       	and    ppa1.payroll_action_id       = c_pact_id
1251       	order  by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
1252 
1253 
1254   --------------------------------------------------------------------+
1255   -- Cursor      : csr_assignment_period
1256   -- Description : Fetches assignments when Assignment,Archive Start
1257   --               Date and End Date is specified
1258   --------------------------------------------------------------------+
1259 
1260    cursor csr_assignment_period
1261       (c_payroll_action_id  pay_payroll_actions.payroll_action_id%type
1262       ,c_start_person       per_all_people_f.person_id%type
1263       ,c_end_person         per_all_people_f.person_id%type
1264       ,c_employee_type      per_all_people_f.current_employee_flag%type
1265       ,c_business_group_id  hr_all_organization_units.organization_id%type
1266       ,c_assignment_id      per_all_assignments_f.assignment_id%type
1267       ,c_archive_start_date         date
1268       ,c_archive_end_date           date
1269       ) is
1270       select 	paa.assignment_action_id,
1271                 paa.action_sequence,
1272       	   	paaf.assignment_id,
1273       	   	paa.tax_unit_id
1274        	from  	per_people_f pap,
1275   		per_assignments_f paaf,
1276   		pay_payroll_actions ppa,
1277   		pay_payroll_actions ppa1,
1278   		pay_assignment_actions paa,
1279   		per_periods_of_service pps
1280   	where   ppa.payroll_action_id        = c_payroll_action_id
1281   	and     paa.assignment_id            = paaf.assignment_id
1282   	and     pap.person_id                between c_start_person and c_end_person
1283   	and     pap.person_id                = paaf.person_id
1284   	and     pap.person_id                = pps.person_id
1285   	and     pps.period_of_service_id     = paaf.period_of_service_id
1286   	and     ppa1.date_earned between pap.effective_start_date  and pap.effective_end_date
1287   	and    ppa1.payroll_action_id       = paa.payroll_action_id
1288 	AND    paa.action_status ='C' /*Bug 4099317*/
1289   	and    ppa1.business_group_id       = ppa.business_group_id
1290   	and    ppa.business_group_id        = c_business_group_id
1291   	and    ppa1.action_type             in ('R','Q','I','B','V')
1292 	and    paa.source_action_id is null
1293   	and    paa.assignment_id            = c_assignment_id
1294   	and    ppa1.effective_date between c_archive_start_date and c_archive_end_date
1295        and   decode(pps.actual_termination_date,null,'Y',decode(sign(pps.actual_termination_date - (c_archive_end_date)),1,'Y','N')) LIKE c_employee_type  --Bug 4161540
1296         and   paaf.effective_end_date = (select max(effective_end_date) --Bug# 3538810
1297 					From  per_assignments_f iipaf
1298 					WHERE iipaf.assignment_id  = paaf.assignment_id
1299 					and iipaf.effective_end_date >= c_archive_start_date
1300 					and iipaf.effective_start_date <= c_archive_end_date)
1301 	order  by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
1302 
1303   --------------------------------------------------------------------+
1304   -- Bug         : 9113084
1305   -- Cursor      : rg_csr_assignment_period
1306   -- Description : Fetches assignments when Assignment,Archive Start
1307   --               Date and End Date is specified
1308   -- Usage       : When Range Person is enabled
1309   --------------------------------------------------------------------+
1310    cursor rg_csr_assignment_period
1311       (c_payroll_action_id  pay_payroll_actions.payroll_action_id%type
1312       ,c_chunk number
1313       ,c_employee_type      per_all_people_f.current_employee_flag%type
1314       ,c_business_group_id  hr_all_organization_units.organization_id%type
1315       ,c_assignment_id      per_all_assignments_f.assignment_id%type
1316       ,c_archive_start_date         date
1317       ,c_archive_end_date           date
1318       ) is
1319       select 	paa.assignment_action_id,
1320                 paa.action_sequence,
1321       	   	paaf.assignment_id,
1322       	   	paa.tax_unit_id
1323        	from  	per_people_f pap,
1324   		per_assignments_f paaf,
1325   		pay_payroll_actions ppa,
1326   		pay_payroll_actions ppa1,
1327   		pay_assignment_actions paa,
1328   		per_periods_of_service pps,
1329 		pay_population_ranges ppr
1330   	where   ppa.payroll_action_id        = c_payroll_action_id
1331 	and     ppr.payroll_action_id = ppa.payroll_action_id
1332 	and     ppr.chunk_number = c_chunk
1333   	and     paa.assignment_id            = paaf.assignment_id
1334   	and     pap.person_id                = ppr.person_id
1335   	and     pap.person_id                = paaf.person_id
1336   	and     pap.person_id                = pps.person_id
1337   	and     pps.period_of_service_id     = paaf.period_of_service_id
1338   	and     ppa1.date_earned between pap.effective_start_date  and pap.effective_end_date
1339   	and    ppa1.payroll_action_id       = paa.payroll_action_id
1340 	AND    paa.action_status ='C'
1341   	and    ppa1.business_group_id       = ppa.business_group_id
1342   	and    ppa.business_group_id        = c_business_group_id
1343   	and    ppa1.action_type             in ('R','Q','I','B','V')
1344 	and    paa.source_action_id is null
1345   	and    paa.assignment_id            = c_assignment_id
1346   	and    ppa1.effective_date between c_archive_start_date and c_archive_end_date
1347        and   decode(pps.actual_termination_date,null,'Y',decode(sign(pps.actual_termination_date - (c_archive_end_date)),1,'Y','N')) LIKE c_employee_type  --Bug 4161540
1348         and   paaf.effective_end_date = (select max(effective_end_date)
1349 					From  per_assignments_f iipaf
1350 					WHERE iipaf.assignment_id  = paaf.assignment_id
1351 					and iipaf.effective_end_date >= c_archive_start_date
1352 					and iipaf.effective_start_date <= c_archive_end_date)
1353 	order  by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
1354 
1355 
1356   -------------------------------------------------------------------+
1357   -- Cursor      : csr_assignment_run
1358   -- Description : Fetches assignments when Assignment,Payroll Run
1359   --               and Period End Date is specified
1360   --------------------------------------------------------------------+
1361 
1362       cursor csr_assignment_run
1363       (c_payroll_action_id  pay_payroll_actions.payroll_action_id%type
1364       ,c_start_person       per_all_people_f.person_id%type
1365       ,c_end_person         per_all_people_f.person_id%type
1366       ,c_employee_type      per_all_people_f.current_employee_flag%type
1367       ,c_business_group_id  hr_all_organization_units.organization_id%type
1368       ,c_assignment_id      per_all_assignments_f.assignment_id%type
1369       ,c_period_end_date            date
1370       ,c_pact_id            pay_payroll_actions.payroll_action_id%type
1371       ) is
1372       select 	paa.assignment_action_id,
1373                 paa.action_sequence,
1374       	   	paaf.assignment_id,
1375       	   	paa.tax_unit_id
1376        	from  	per_people_f pap,
1377   		per_assignments_f paaf,
1378   		pay_payroll_actions ppa,
1379   		pay_payroll_actions ppa1,
1380   		pay_assignment_actions paa,
1381     		per_periods_of_service pps
1382   	where   ppa.payroll_action_id        = c_payroll_action_id
1383   	and     paa.assignment_id            = paaf.assignment_id
1384   	and     pap.person_id                between c_start_person and c_end_person
1385   	and     pap.person_id                = paaf.person_id
1386   	and     pap.person_id                = pps.person_id
1387   	and     pps.period_of_service_id     = paaf.period_of_service_id
1388   	and     ppa1.date_earned between paaf.effective_start_date and paaf.effective_end_date
1389   	and     ppa1.date_earned between pap.effective_start_date  and pap.effective_end_date
1390   	and    ppa1.payroll_action_id       = paa.payroll_action_id
1391 	AND    paa.action_status ='C' /*Bug 4099317*/
1392   	and    ppa1.business_group_id       = ppa.business_group_id
1393   	and    ppa.business_group_id        = c_business_group_id
1394   	and    ppa1.action_type             in ('R','Q','I','B','V')
1395 	and    paa.source_action_id is null
1396   	and    NVL(pap.current_employee_flag,'N') like c_employee_type
1397   	and    paa.assignment_id            = c_assignment_id
1398   	and    ppa1.payroll_action_id       = c_pact_id
1399   	order  by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
1400 
1401   -------------------------------------------------------------------+
1402   -- Bug         : 9113084
1403   -- Cursor      : rg_csr_assignment_run
1404   -- Description : Fetches assignments when Assignment,Payroll Run
1405   --               and Period End Date is specified
1406   -- Usage       : When Range Person is enabled
1407   --------------------------------------------------------------------+
1408       cursor rg_csr_assignment_run
1409       (c_payroll_action_id  pay_payroll_actions.payroll_action_id%type
1410       , c_chunk number
1411       ,c_employee_type      per_all_people_f.current_employee_flag%type
1412       ,c_business_group_id  hr_all_organization_units.organization_id%type
1413       ,c_assignment_id      per_all_assignments_f.assignment_id%type
1414       ,c_period_end_date            date
1415       ,c_pact_id            pay_payroll_actions.payroll_action_id%type
1416       ) is
1417       select 	paa.assignment_action_id,
1418                 paa.action_sequence,
1419       	   	paaf.assignment_id,
1420       	   	paa.tax_unit_id
1421        	from  	per_people_f pap,
1422   		per_assignments_f paaf,
1423   		pay_payroll_actions ppa,
1424   		pay_payroll_actions ppa1,
1425   		pay_assignment_actions paa,
1426     		per_periods_of_service pps,
1427 		pay_population_ranges ppr
1428   	where   ppa.payroll_action_id        = c_payroll_action_id
1429 	and     ppr.payroll_action_id = ppa.payroll_action_id
1430 	and     ppr.chunk_number = c_chunk
1431   	and     paa.assignment_id            = paaf.assignment_id
1432   	and     pap.person_id                = ppr.person_id
1433   	and     pap.person_id                = paaf.person_id
1434   	and     pap.person_id                = pps.person_id
1435   	and     pps.period_of_service_id     = paaf.period_of_service_id
1436   	and     ppa1.date_earned between paaf.effective_start_date and paaf.effective_end_date
1437   	and     ppa1.date_earned between pap.effective_start_date  and pap.effective_end_date
1438   	and    ppa1.payroll_action_id       = paa.payroll_action_id
1439 	AND    paa.action_status ='C'
1440   	and    ppa1.business_group_id       = ppa.business_group_id
1441   	and    ppa.business_group_id        = c_business_group_id
1442   	and    ppa1.action_type             in ('R','Q','I','B','V')
1443 	and    paa.source_action_id is null
1444   	and    NVL(pap.current_employee_flag,'N') like c_employee_type
1445   	and    paa.assignment_id            = c_assignment_id
1446   	and    ppa1.payroll_action_id       = c_pact_id
1447   	order  by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
1448 
1449 
1450     --------------------------------------------------------------------+
1451     -- Cursor      : csr_assignment_default_period
1452     -- Description : Fetches assignments when Archive Start date
1453     --               and End Date is specified
1454     --------------------------------------------------------------------+
1455 
1456       cursor csr_assignment_default_period
1457       (c_payroll_action_id  pay_payroll_actions.payroll_action_id%type
1458       ,c_start_person       per_all_people_f.person_id%type
1459       ,c_end_person         per_all_people_f.person_id%type
1460       ,c_employee_type      per_all_people_f.current_employee_flag%type
1461       ,c_business_group_id  hr_all_organization_units.organization_id%type
1462       ,c_archive_start_date         date
1463       ,c_archive_end_date           date
1464       ) is
1465       select 	paa.assignment_action_id,
1466                 paa.action_sequence,
1467       	   	paaf.assignment_id,
1468       	   	paa.tax_unit_id
1469        	from  	per_people_f pap,
1470   		per_assignments_f paaf,
1471   		pay_payroll_actions ppa,
1472   		pay_payroll_actions ppa1,
1473   		pay_assignment_actions paa,
1474   		per_periods_of_service pps
1475   	where   ppa.payroll_action_id        = c_payroll_action_id
1476   	and     paa.assignment_id            = paaf.assignment_id
1477   	and     pap.person_id                between c_start_person and c_end_person
1478   	and     pap.person_id                = paaf.person_id
1479   	and     pap.person_id                = pps.person_id
1480   	and     pps.period_of_service_id     = paaf.period_of_service_id
1481   	and     ppa1.date_earned between pap.effective_start_date  and pap.effective_end_date
1482   	and    ppa1.payroll_action_id       = paa.payroll_action_id
1483 	AND    paa.action_status ='C' /*Bug 4099317*/
1484   	and    ppa1.business_group_id       = ppa.business_group_id
1485   	and    ppa.business_group_id        = c_business_group_id
1486   	and    ppa1.action_type             in ('R','Q','I','B','V')
1487 	and    paa.source_action_id is null
1488   	and    ppa1.effective_date   between c_archive_start_date and c_archive_end_date
1489         and   decode(pps.actual_termination_date,null,'Y',decode(sign(pps.actual_termination_date - (c_archive_end_date)),1,'Y','N')) LIKE c_employee_type  --Bug 4161540
1490         and   paaf.effective_end_date = (select max(effective_end_date) --Bug# 3538810
1491 					From  per_assignments_f iipaf
1492 					WHERE iipaf.assignment_id  = paaf.assignment_id
1493 					and iipaf.effective_end_date >= c_archive_start_date
1494 					and iipaf.effective_start_date <= c_archive_end_date)
1495   	order  by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
1496 
1497     --------------------------------------------------------------------+
1498     -- Bug         : 9113084
1499     -- Cursor      : rg_assignment_default_period
1500     -- Description : Fetches assignments when Archive Start date
1501     --               and End Date is specified
1502     -- Usage       : When Range Person is enabled
1503     --------------------------------------------------------------------+
1504       cursor rg_assignment_default_period
1505       (c_payroll_action_id  pay_payroll_actions.payroll_action_id%type
1506       , c_chunk NUMBER
1507       ,c_employee_type      per_all_people_f.current_employee_flag%type
1508       ,c_business_group_id  hr_all_organization_units.organization_id%type
1509       ,c_archive_start_date         date
1510       ,c_archive_end_date           date
1511       ) is
1512       select 	paa.assignment_action_id,
1513                 paa.action_sequence,
1514       	   	paaf.assignment_id,
1515       	   	paa.tax_unit_id
1516        	from  	per_people_f pap,
1517   		per_assignments_f paaf,
1518   		pay_payroll_actions ppa,
1519   		pay_payroll_actions ppa1,
1520   		pay_assignment_actions paa,
1521   		per_periods_of_service pps,
1522 		pay_population_ranges ppr
1523   	where   ppa.payroll_action_id        = c_payroll_action_id
1524 	and     ppr.payroll_action_id = ppa.payroll_action_id
1525 	and     ppr.chunk_number = c_chunk
1526   	and     paa.assignment_id            = paaf.assignment_id
1527   	and     pap.person_id                = ppr.person_id
1528   	and     pap.person_id                = paaf.person_id
1529   	and     pap.person_id                = pps.person_id
1530   	and     pps.period_of_service_id     = paaf.period_of_service_id
1531   	and     ppa1.date_earned between pap.effective_start_date  and pap.effective_end_date
1532   	and    ppa1.payroll_action_id       = paa.payroll_action_id
1533 	AND    paa.action_status ='C'
1534   	and    ppa1.business_group_id       = ppa.business_group_id
1535   	and    ppa.business_group_id        = c_business_group_id
1536   	and    ppa1.action_type             in ('R','Q','I','B','V')
1537 	and    paa.source_action_id is null
1538   	and    ppa1.effective_date   between c_archive_start_date and c_archive_end_date
1539         and   decode(pps.actual_termination_date,null,'Y',decode(sign(pps.actual_termination_date - (c_archive_end_date)),1,'Y','N')) LIKE c_employee_type  --Bug 4161540
1540         and   paaf.effective_end_date = (select max(effective_end_date)
1541 					From  per_assignments_f iipaf
1542 					WHERE iipaf.assignment_id  = paaf.assignment_id
1543 					and iipaf.effective_end_date >= c_archive_start_date
1544 					and iipaf.effective_start_date <= c_archive_end_date)
1545   	order  by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
1546 
1547 
1548   --------------------------------------------------------------------+
1549   -- Cursor      : csr_assignment_default_run
1550   -- Description : Fetches assignments when Payroll Run
1551   --               and Period End Date is specified
1552   --------------------------------------------------------------------+
1553 
1554       cursor csr_assignment_default_run
1555       (c_payroll_action_id  pay_payroll_actions.payroll_action_id%type
1556       ,c_start_person       per_all_people_f.person_id%type
1557       ,c_end_person         per_all_people_f.person_id%type
1558       ,c_employee_type      per_all_people_f.current_employee_flag%type
1559       ,c_business_group_id  hr_all_organization_units.organization_id%type
1560       ,c_period_end_date            date
1561       ,c_pact_id            pay_payroll_actions.payroll_action_id%type
1562       ) is
1563       select 	paa.assignment_action_id,
1564                 paa.action_sequence,
1565       	   	paaf.assignment_id,
1566       	   	paa.tax_unit_id
1567        	from  	per_people_f pap,
1568   		per_assignments_f paaf,
1569   		pay_payroll_actions ppa,
1570   		pay_payroll_actions ppa1,
1571   		pay_assignment_actions paa,
1572   		per_periods_of_service pps
1573   	where   ppa.payroll_action_id        = c_payroll_action_id
1574   	and     paa.assignment_id            = paaf.assignment_id
1575   	and     pap.person_id                between c_start_person and c_end_person
1576   	and     pap.person_id                = paaf.person_id
1577   	and     pap.person_id                = pps.person_id
1578   	and     pps.period_of_service_id     = paaf.period_of_service_id
1579   	and     ppa1.date_earned between paaf.effective_start_date and paaf.effective_end_date
1580   	and     ppa1.date_earned between pap.effective_start_date  and pap.effective_end_date
1581   	and    ppa1.payroll_action_id       = paa.payroll_action_id
1582 	AND    paa.action_status ='C' /*Bug 4099317*/
1583   	and    ppa1.business_group_id       = ppa.business_group_id
1584   	and    ppa.business_group_id        = c_business_group_id
1585   	and    ppa1.action_type             in ('R','Q','I','B','V')
1586 	and    paa.source_action_id is null
1587   	and    NVL(pap.current_employee_flag,'N') like c_employee_type
1588   	and    ppa1.payroll_action_id       = c_pact_id
1589   	order  by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
1590 
1591 
1592   --------------------------------------------------------------------+
1593   -- Bug         : 9113084
1594   -- Cursor      : rg_csr_assignment_default_run
1595   -- Description : Fetches assignments when Payroll Run
1596   --               and Period End Date is specified
1597   -- Usage       : When Range Person is enabled
1598   --------------------------------------------------------------------+
1599       cursor rg_csr_assignment_default_run
1600       (c_payroll_action_id  pay_payroll_actions.payroll_action_id%type
1601       , c_chunk number
1602       ,c_employee_type      per_all_people_f.current_employee_flag%type
1603       ,c_business_group_id  hr_all_organization_units.organization_id%type
1604       ,c_period_end_date            date
1605       ,c_pact_id            pay_payroll_actions.payroll_action_id%type
1606       ) is
1607       select 	paa.assignment_action_id,
1608                 paa.action_sequence,
1609       	   	paaf.assignment_id,
1610       	   	paa.tax_unit_id
1611        	from  	per_people_f pap,
1612   		per_assignments_f paaf,
1613   		pay_payroll_actions ppa,
1614   		pay_payroll_actions ppa1,
1615   		pay_assignment_actions paa,
1616   		per_periods_of_service pps,
1617 		pay_population_ranges ppr
1618   	where   ppa.payroll_action_id        = c_payroll_action_id
1619 	and     ppr.payroll_action_id = ppa.payroll_action_id
1620 	and     ppr.chunk_number = c_chunk
1621   	and     paa.assignment_id            = paaf.assignment_id
1622   	and     pap.person_id                = ppr.person_id
1623   	and     pap.person_id                = paaf.person_id
1624   	and     pap.person_id                = pps.person_id
1625   	and     pps.period_of_service_id     = paaf.period_of_service_id
1626   	and     ppa1.date_earned between paaf.effective_start_date and paaf.effective_end_date
1627   	and     ppa1.date_earned between pap.effective_start_date  and pap.effective_end_date
1628   	and    ppa1.payroll_action_id       = paa.payroll_action_id
1629 	AND    paa.action_status ='C'
1630   	and    ppa1.business_group_id       = ppa.business_group_id
1631   	and    ppa.business_group_id        = c_business_group_id
1632   	and    ppa1.action_type             in ('R','Q','I','B','V')
1633 	and    paa.source_action_id is null
1634   	and    NVL(pap.current_employee_flag,'N') like c_employee_type
1635   	and    ppa1.payroll_action_id       = c_pact_id
1636   	order  by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
1637 
1638   --------------------------------------------------------------------+
1639   -- Cursor      : csr_params
1640   -- Description : Fetches User Parameters from Legislative_paramters
1641   --               column.
1642   --------------------------------------------------------------------+
1643 
1644    CURSOR csr_params(c_payroll_action_id  pay_payroll_actions.payroll_action_id%TYPE)
1645       IS
1646         SELECT pay_core_utils.get_parameter('PAY',legislative_parameters)        payroll_id,
1647                    pay_core_utils.get_parameter('ORG',legislative_parameters)           org_id,
1648                    pay_core_utils.get_parameter('BG',legislative_parameters)    business_group_id,
1649                    to_date(pay_core_utils.get_parameter('SDATE',legislative_parameters),'YYYY/MM/DD') start_date,
1650                    to_date(pay_core_utils.get_parameter('EDATE',legislative_parameters),'YYYY/MM/DD')   end_date,
1651                    pay_core_utils.get_parameter('PACTID',legislative_parameters)        pact_id,
1652                    pay_core_utils.get_parameter('LE',legislative_parameters) legal_employer,
1653                    pay_core_utils.get_parameter('ASG',legislative_parameters) assignment_id,
1654                    pay_core_utils.get_parameter('SO1',legislative_parameters)   sort_order_1,
1655                    pay_core_utils.get_parameter('SO2',legislative_parameters)   sort_order_2,
1656                    pay_core_utils.get_parameter('SO3',legislative_parameters)   sort_order_3,
1657                    pay_core_utils.get_parameter('SO4',legislative_parameters)   sort_order_4,
1658                    to_date(pay_core_utils.get_parameter('PEDATE',legislative_parameters),'YYYY/MM/DD') period_end_date,
1659                    pay_core_utils.get_parameter('YTD_TOT',legislative_parameters)      ytd_totals,
1660                    pay_core_utils.get_parameter('ZERO_REC',legislative_parameters)    zero_records,
1661                    pay_core_utils.get_parameter('NEG_REC',legislative_parameters)     negative_records,
1662                    decode(pay_core_utils.get_parameter('EMP_TYPE',legislative_parameters),'C','Y','T','N','%') employee_type,
1663                    pay_core_utils.get_parameter('DEL_ACT',legislative_parameters)     delete_actions /*Bug# 4142159*/
1664                    FROM pay_payroll_actions ppa
1665       WHERE ppa.payroll_action_id  =  c_payroll_action_id;
1666 
1667  --------------------------------------------------------------------+
1668   -- Cursor      : csr_period_date_earned
1669   -- Description : Fetches Date Earned for a given payroll
1670   --               run.
1671   --------------------------------------------------------------------+
1672       /*Bug#3662449 *********/
1673       CURSOR csr_period_date_earned(c_payroll_action_id  pay_payroll_actions.payroll_action_id%TYPE)
1674       IS
1675         SELECT ppa.date_earned
1676 	FROM pay_payroll_actions ppa
1677         WHERE
1678 	ppa.payroll_action_id = c_payroll_action_id;
1679 
1680 
1681     cursor csr_next_action_id is
1682     select pay_assignment_actions_s.nextval
1683     from   dual;
1684 
1685     l_next_assignment_action_id       pay_assignment_actions.assignment_action_id%type;
1686     l_procedure               	      varchar2(200) ;
1687     i 				      number;
1688 
1689     l_action_information_id 	 	number;
1690     l_object_version_number		number;
1691 
1692 
1693 begin
1694     i := 1;
1695     g_debug :=hr_utility.debug_enabled ;
1696     if g_debug then
1697         g_package := 'pay_au_reconciliation_pkg.' ;
1698         l_procedure := g_package||'assignment_action_code';
1699         hr_utility.set_location('Entering ' || l_procedure,1);
1700         hr_utility.set_location('Entering assignment_Action_code',302);
1701     end if;
1702 
1703     -- initialization_code to to set the global tables for EIT
1704         -- that will be used by each thread in multi-threading.
1705 
1706     g_arc_payroll_action_id := p_payroll_action_id;
1707 
1708     -- Fetch the parameters by user passed into global variable.
1709 
1710         OPEN csr_params(p_payroll_action_id);
1711      	FETCH csr_params into g_parameters;
1712        	CLOSE csr_params;
1713 
1714 
1715     if g_debug then
1716         hr_utility.set_location('p_payroll_action_id.........= ' || p_payroll_action_id,30);
1717         hr_utility.set_location('p_start_person..............= ' || p_start_person,30);
1718         hr_utility.set_location('p_end_person................= ' || p_end_person,30);
1719         hr_utility.set_location('g_parameters.business_group_id.........= ' || g_parameters.business_group_id,30);
1720         hr_utility.set_location('g_parameters.payroll_id..............= ' || g_parameters.payroll_id,30);
1721         hr_utility.set_location('g_parameters.org_id................= ' || g_parameters.org_id,30);
1722         hr_utility.set_location('g_parameters.legal_employer.........= ' || g_parameters.legal_employer,30);
1723         hr_utility.set_location('g_parameters.start_date..............= ' || g_parameters.start_date,30);
1724         hr_utility.set_location('g_parameters.end_date................= ' || g_parameters.end_date,30);
1725         hr_utility.set_location('g_parameters.period_end_date.........= ' || g_parameters.period_end_date,30);
1726         hr_utility.set_location('g_parameters.pact_id..............= ' || g_parameters.pact_id,30);
1727         hr_utility.set_location('g_parameters.employee_type..........= '||g_parameters.employee_type,30);
1728         hr_utility.set_location('g_parameters.sort_order1..........= '||g_parameters.sort_order_1,30);
1729         hr_utility.set_location('g_parameters.sort_order2..........= '||g_parameters.sort_order_2,30);
1730         hr_utility.set_location('g_parameters.sort_order3..........= '||g_parameters.sort_order_3,30);
1731         hr_utility.set_location('g_parameters.sort_order4..........= '||g_parameters.sort_order_4,30);
1732 	hr_utility.set_location('g_parameters.delete_actions..........= '||g_parameters.delete_actions,30);/*Bug# 4142159*/
1733     end if;
1734 
1735 
1736     g_business_group_id := g_parameters.business_group_id ;
1737 
1738     -- Set end date variable .This value is used to fetch latest assignment details of
1739     -- employee for archival.In case of archive start date/end date - archive end date
1740     -- taken and pact_id/period_end_date , period end date is picked.
1741 
1742     if g_parameters.end_date is not null
1743     then
1744         g_end_date := g_parameters.end_date;
1745         g_start_date := g_parameters.start_date; --Bug#3662449
1746     else
1747         if g_parameters.period_end_date is not null
1748         then
1749             open csr_period_date_earned(g_parameters.pact_id); --Bug#3662449
1750             fetch csr_period_date_earned into g_start_date;
1751             close csr_period_date_earned;
1752             g_end_date  := g_parameters.period_end_date;
1753         else
1754             g_start_date := to_date('1900/01/01','YYYY/MM/DD');  --Bug#3662449
1755             g_end_date  := to_date('4712/12/31','YYYY/MM/DD');
1756         end if;
1757     end if; /* End of outer if loop */
1758 
1759 
1760 IF range_person_on THEN /* 9113084 - Use Range Person Cursors when RANGE_PERSON_ID is enabled */
1761 
1762     if g_parameters.org_id is not null
1763     then
1764        if g_parameters.start_date is not null and g_parameters.end_date is not null
1765        then
1766                  IF g_debug THEN
1767                    hr_utility.set_location('Using Range Person Cursor for fetching assignments',30);
1768                  END IF;
1769             FOR csr_rec in rg_csr_assignment_org_period(p_payroll_action_id,
1770                                                  p_chunk,
1771                                                  g_parameters.employee_type,
1772                                                  g_parameters.business_group_id,
1773                                                  g_parameters.org_id,
1774                                                  g_parameters.start_date,
1775                                                  g_parameters.end_date)
1776             LOOP /*Loop 1 Org,Archive start date,end date */
1777              open csr_next_action_id;
1778              fetch  csr_next_action_id into l_next_assignment_action_id;
1779              close csr_next_action_id;
1780 
1781               if g_debug then
1782 
1783                    hr_utility.set_location('p_payroll_action_id.........= '||p_payroll_action_id,20);
1784                    hr_utility.set_location('l_next_assignment_action_id.= '||l_next_assignment_action_id,20);
1785                    hr_utility.set_location('csr_rec.assignment_id.......= '||csr_rec.assignment_id,20);
1786                    hr_utility.set_location('csr_rec.tax_unit_id.........= '||csr_rec.tax_unit_id,20);
1787 
1788               end if;
1789 
1790 
1791 
1792             -- Create the archive assignment actions
1793              hr_nonrun_asact.insact(l_next_assignment_action_id, csr_rec.assignment_id, p_payroll_action_id, p_chunk, csr_rec.tax_unit_id);
1794 
1795 
1796                         insert into pay_action_information(
1797                                       action_information_id,
1798                                       action_context_id,
1799                                       action_context_type,
1800                                       effective_date,
1801                                       source_id,
1802                                       tax_unit_id,
1803                                       action_information_category,
1804                                       action_information1,
1805                                       action_information2,
1806                                       action_information3,
1807                                       assignment_id
1808                                       )
1809                                       values(
1810                                       pay_action_information_s.nextval,
1811                                       l_next_assignment_action_id,
1812                                       'AAP',
1813                                       null,
1814                                       null,
1815                                       csr_rec.tax_unit_id,
1816                                       'AU_ARCHIVE_ASG_DETAILS',
1817                                       csr_rec.assignment_action_id,
1818                                       p_payroll_action_id,
1819                                       csr_rec.action_sequence,
1820                                       csr_rec.assignment_id
1821                                       );
1822 
1823 
1824             END LOOP;/* Loop 1 */
1825             if g_debug then
1826             hr_utility.set_location('Leaving............Loop1 Org+Period....' || l_procedure,1000);
1827             end if;
1828 
1829        else
1830                   IF g_debug THEN
1831                    hr_utility.set_location('Using Range Person Cursor for fetching assignments',30);
1832                  END IF;
1833                FOR csr_rec in rg_csr_assignment_org_run(p_payroll_action_id,
1834                                                  p_chunk,
1835                                                  g_parameters.employee_type,
1836                                                  g_parameters.business_group_id,
1837                                                  g_parameters.org_id,
1838                                                  g_parameters.period_end_date,
1839                                                  g_parameters.pact_id)
1840                LOOP /*Loop 2 Org,Pact_id and period end date*/
1841                  open csr_next_action_id;
1842              fetch  csr_next_action_id into l_next_assignment_action_id;
1843              close csr_next_action_id;
1844 
1845              if g_debug then
1846              hr_utility.set_location('p_payroll_action_id.........= '||p_payroll_action_id,20);
1847              hr_utility.set_location('l_next_assignment_action_id.= '||l_next_assignment_action_id,20);
1848              hr_utility.set_location('csr_rec.assignment_id.......= '||csr_rec.assignment_id,20);
1849              hr_utility.set_location('csr_rec.tax_unit_id.........= '||csr_rec.tax_unit_id,20);
1850              end if;
1851 
1852             -- Create the archive assignment actions
1853              hr_nonrun_asact.insact(l_next_assignment_action_id, csr_rec.assignment_id, p_payroll_action_id, p_chunk, csr_rec.tax_unit_id);
1854 
1855                         insert into pay_action_information(
1856                                       action_information_id,
1857                                       action_context_id,
1858                                       action_context_type,
1859                                       effective_date,
1860                                       source_id,
1861                                       tax_unit_id,
1862                                       action_information_category,
1863                                       action_information1,
1864                                       action_information2,
1865                                       action_information3,
1866                                       assignment_id
1867                                       )
1868                                       values(
1869                                       pay_action_information_s.nextval,
1870                                       l_next_assignment_action_id,
1871                                       'AAP',
1872                                       null,
1873                                       null,
1874                                       csr_rec.tax_unit_id,
1875                                       'AU_ARCHIVE_ASG_DETAILS',
1876                                       csr_rec.assignment_action_id,
1877                                       p_payroll_action_id,
1878                                       csr_rec.action_sequence,
1879                                       csr_rec.assignment_id
1880                                       );
1881 
1882 
1883                END LOOP; /* Loop 2 */
1884             if g_debug then
1885             hr_utility.set_location('Leaving............Loop2 ,Org + Run....' || l_procedure,1000);
1886             end if;
1887         end if; /* End of Inner Organization  */
1888     else      /* Not Org,check for others */
1889 
1890     if g_parameters.legal_employer is not null
1891     then
1892        if g_parameters.start_date is not null and g_parameters.end_date is not null
1893        then
1894                  IF g_debug THEN
1895                    hr_utility.set_location('Using Range Person Cursor for fetching assignments',30);
1896                  END IF;
1897             FOR csr_rec in rg_csr_assignment_legal_period(p_payroll_action_id,
1898                                                  p_chunk,
1899                                                  g_parameters.employee_type,
1900                                                  g_parameters.business_group_id,
1901                                                  g_parameters.legal_employer,
1902                                                  g_parameters.start_date,
1903                                                  g_parameters.end_date)
1904             LOOP /*Loop 3 Leg Employer,Archive Start date,archive end date*/
1905             /*Bug 3935471 - IF Condition used to archive all master actions and only those child actions which have tax unit id not same as master*/
1906 
1907                  IF csr_rec.master_action_id IS NULL OR (csr_rec.tax_unit_id <> csr_rec.master_tax_unit_id AND csr_rec.master_action_id IS NOT NULL) THEN
1908                open csr_next_action_id;
1909                  fetch  csr_next_action_id into l_next_assignment_action_id;
1910                  close csr_next_action_id;
1911                  if g_debug then
1912                  hr_utility.set_location('p_payroll_action_id.........= '||p_payroll_action_id,20);
1913                  hr_utility.set_location('l_next_assignment_action_id.= '||l_next_assignment_action_id,20);
1914                  hr_utility.set_location('csr_rec.assignment_id.......= '||csr_rec.assignment_id,20);
1915                  hr_utility.set_location('csr_rec.tax_unit_id.........= '||csr_rec.tax_unit_id,20);
1916                  end if;
1917 
1918             -- Create the archive assignment actions
1919                  hr_nonrun_asact.insact(l_next_assignment_action_id, csr_rec.assignment_id, p_payroll_action_id, p_chunk, csr_rec.tax_unit_id);
1920 
1921                         insert into pay_action_information(
1922                                       action_information_id,
1923                                       action_context_id,
1924                                       action_context_type,
1925                                       effective_date,
1926                                       source_id,
1927                                       tax_unit_id,
1931                                       action_information3,
1928                                       action_information_category,
1929                                       action_information1,
1930                                       action_information2,
1932                                       assignment_id
1933                                       )
1934                                       values(
1935                                       pay_action_information_s.nextval,
1936                                       l_next_assignment_action_id,
1937                                       'AAP',
1938                                       null,
1939                                       null,
1940                                       csr_rec.tax_unit_id,
1941                                       'AU_ARCHIVE_ASG_DETAILS',
1942                                       csr_rec.assignment_action_id,
1943                                       p_payroll_action_id,
1944                                       csr_rec.action_sequence,
1945                                       csr_rec.assignment_id
1946                                       );
1947 
1948             END IF;
1949             END LOOP;/* Loop 3 */
1950             if g_debug then
1951             hr_utility.set_location('Leaving............Loop3.Legal Emp + period...' || l_procedure,1000);
1952             end if;
1953 
1954        else
1955                  IF g_debug THEN
1956                    hr_utility.set_location('Using Range Person Cursor for fetching assignments',30);
1957                  END IF;
1958                FOR csr_rec in rg_csr_assignment_legal_run(p_payroll_action_id,
1959                                                  p_chunk,
1960                                                  g_parameters.employee_type,
1961                                                  g_parameters.business_group_id,
1962                                                  g_parameters.legal_employer,
1963                                                  g_parameters.period_end_date,
1964                                                  g_parameters.pact_id)
1965                LOOP /*Loop 4 Leg employer,pact_id + period end date */
1966             /*Bug 3935471 - IF Condition used to archive all master actions and only those child actions which have tax unit id not same as master*/
1967 
1968                  IF csr_rec.master_action_id IS NULL OR (csr_rec.tax_unit_id <> csr_rec.master_tax_unit_id AND csr_rec.master_action_id IS NOT NULL) THEN
1969                      open csr_next_action_id;
1970                     fetch  csr_next_action_id into l_next_assignment_action_id;
1971                     close csr_next_action_id;
1972 
1973                      if g_debug then
1974                              hr_utility.set_location('p_payroll_action_id.........= '||p_payroll_action_id,20);
1975                       hr_utility.set_location('l_next_assignment_action_id.= '||l_next_assignment_action_id,20);
1976                         hr_utility.set_location('csr_rec.assignment_id.......= '||csr_rec.assignment_id,20);
1977                       hr_utility.set_location('csr_rec.tax_unit_id.........= '||csr_rec.tax_unit_id,20);
1978                      end if;
1979 
1980             -- Create the archive assignment actions
1981                      hr_nonrun_asact.insact(l_next_assignment_action_id, csr_rec.assignment_id, p_payroll_action_id, p_chunk, csr_rec.tax_unit_id);
1982 
1983                     insert into pay_action_information(
1984                                       action_information_id,
1985                                       action_context_id,
1986                                       action_context_type,
1987                                       effective_date,
1988                                       source_id,
1989                                       tax_unit_id,
1990                                       action_information_category,
1991                                       action_information1,
1992                                       action_information2,
1993                                       action_information3,
1994                                       assignment_id
1995                                       )
1996                                       values(
1997                                       pay_action_information_s.nextval,
1998                                       l_next_assignment_action_id,
1999                                       'AAP',
2000                                       null,
2001                                       null,
2002                                       csr_rec.tax_unit_id,
2003                                       'AU_ARCHIVE_ASG_DETAILS',
2004                                       csr_rec.assignment_action_id,
2005                                       p_payroll_action_id,
2006                                       csr_rec.action_sequence,
2007                                       csr_rec.assignment_id
2008                                       );
2009 
2010                   END IF;
2011                END LOOP; /* Loop 4 */
2012             if g_debug then
2013             hr_utility.set_location('Leaving............Loop4.Legal Emp + Run...' || l_procedure,1000);
2014             end if;
2015         end if; /* End of Inner Legal Employer  */
2016     else /* Not Org,Legal Emp Check others */
2017 
2018     if g_parameters.payroll_id is not null
2019     then
2020        if g_parameters.start_date is not null and g_parameters.end_date is not null
2021        then
2022                  IF g_debug THEN
2023                    hr_utility.set_location('Using Range Person Cursor for fetching assignments',30);
2024                  END IF;
2025             FOR csr_rec in rg_assignment_payroll_period(p_payroll_action_id,
2026                                                  p_chunk,
2027                                                  g_parameters.employee_type,
2028                                                  g_parameters.business_group_id,
2029                                                  g_parameters.payroll_id,
2030                                                  g_parameters.start_date,
2031                                                  g_parameters.end_date)
2032             LOOP /*Loop 5 Payroll, Archive start date,end date */
2033                  open csr_next_action_id;
2034              fetch  csr_next_action_id into l_next_assignment_action_id;
2035              close csr_next_action_id;
2036 
2037              if g_debug then
2038              hr_utility.set_location('p_payroll_action_id.........= '||p_payroll_action_id,20);
2039              hr_utility.set_location('l_next_assignment_action_id.= '||l_next_assignment_action_id,20);
2040              hr_utility.set_location('csr_rec.assignment_id.......= '||csr_rec.assignment_id,20);
2041              hr_utility.set_location('csr_rec.tax_unit_id.........= '||csr_rec.tax_unit_id,20);
2042              end if;
2043 
2044             -- Create the archive assignment actions
2045              hr_nonrun_asact.insact(l_next_assignment_action_id, csr_rec.assignment_id, p_payroll_action_id, p_chunk, csr_rec.tax_unit_id);
2046 
2047                         insert into pay_action_information(
2048                                       action_information_id,
2049                                       action_context_id,
2050                                       action_context_type,
2051                                       effective_date,
2052                                       source_id,
2053                                       tax_unit_id,
2054                                       action_information_category,
2055                                       action_information1,
2056                                       action_information2,
2057                                       action_information3,
2058                                       assignment_id
2059                                       )
2060                                       values(
2061                                       pay_action_information_s.nextval,
2062                                       l_next_assignment_action_id,
2063                                       'AAP',
2064                                       null,
2065                                       null,
2066                                       csr_rec.tax_unit_id,
2067                                       'AU_ARCHIVE_ASG_DETAILS',
2068                                       csr_rec.assignment_action_id,
2069                                       p_payroll_action_id,
2070                                       csr_rec.action_sequence,
2071                                       csr_rec.assignment_id
2072                                       );
2073 
2074 
2075             END LOOP;/* Loop 5 */
2076 
2077             if g_debug then
2078             hr_utility.set_location('Leaving............Loop5 Payroll + Period....' || l_procedure,1000);
2079             end if;
2080 
2081        else
2082                  IF g_debug THEN
2083                    hr_utility.set_location('Using Range Person Cursor for fetching assignments',30);
2084                  END IF;
2085                FOR csr_rec in rg_csr_assignment_payroll_run(p_payroll_action_id,
2086                                                  p_chunk,
2087                                                  g_parameters.employee_type,
2088                                                  g_parameters.business_group_id,
2089                                                  g_parameters.payroll_id,
2090                                                  g_parameters.period_end_date,
2091                                                  g_parameters.pact_id)
2092                LOOP /*Loop 6 Payroll, pact_id + period end date*/
2093                  open csr_next_action_id;
2094              fetch  csr_next_action_id into l_next_assignment_action_id;
2095              close csr_next_action_id;
2096 
2097              if g_debug then
2098              hr_utility.set_location('p_payroll_action_id.........= '||p_payroll_action_id,20);
2099              hr_utility.set_location('l_next_assignment_action_id.= '||l_next_assignment_action_id,20);
2100              hr_utility.set_location('csr_rec.assignment_id.......= '||csr_rec.assignment_id,20);
2101              hr_utility.set_location('csr_rec.tax_unit_id.........= '||csr_rec.tax_unit_id,20);
2102              end if;
2103 
2104             -- Create the archive assignment actions
2105              hr_nonrun_asact.insact(l_next_assignment_action_id, csr_rec.assignment_id, p_payroll_action_id, p_chunk, csr_rec.tax_unit_id);
2106 
2110                                       action_context_type,
2107                         insert into pay_action_information(
2108                                       action_information_id,
2109                                       action_context_id,
2111                                       effective_date,
2112                                       source_id,
2113                                       tax_unit_id,
2114                                       action_information_category,
2115                                       action_information1,
2116                                       action_information2,
2117                                       action_information3,
2118                                       assignment_id
2119                                       )
2120                                       values(
2121                                       pay_action_information_s.nextval,
2122                                       l_next_assignment_action_id,
2123                                       'AAP',
2124                                       null,
2125                                       null,
2126                                       csr_rec.tax_unit_id,
2127                                       'AU_ARCHIVE_ASG_DETAILS',
2128                                       csr_rec.assignment_action_id,
2129                                       p_payroll_action_id,
2130                                       csr_rec.action_sequence,
2131                                       csr_rec.assignment_id
2132                                       );
2133 
2134 
2135                END LOOP; /* Loop 6 */
2136             if g_debug then
2137             hr_utility.set_location('Leaving............Loop6 Payroll+ Run....' || l_procedure,1000);
2138             end if;
2139         end if; /* End of Inner Payroll */
2140     else /* Not Org,Legal,Payroll check others */
2141 
2142     if g_parameters.assignment_id is not null
2143     then
2144          if g_parameters.start_date is not null and g_parameters.end_date is not null
2145             then
2146                    IF g_debug THEN
2147                      hr_utility.set_location('Using Range Person Cursor for fetching assignments',30);
2148                    END IF;
2149                  FOR csr_rec in rg_csr_assignment_period(p_payroll_action_id,
2150                                                          p_chunk,
2151                                                          g_parameters.employee_type,
2152                                                          g_parameters.business_group_id,
2153                                                          g_parameters.assignment_id,
2154                                                          g_parameters.start_date,
2155                                                          g_parameters.end_date)
2156                  LOOP /*Loop 7 Assignment ,Archive start date,end date*/
2157                       open csr_next_action_id;
2158                      fetch  csr_next_action_id into l_next_assignment_action_id;
2159                      close csr_next_action_id;
2160 
2161                   if g_debug then
2162                      hr_utility.set_location('p_payroll_action_id.........= '||p_payroll_action_id,20);
2163                      hr_utility.set_location('l_next_assignment_action_id.= '||l_next_assignment_action_id,20);
2164                      hr_utility.set_location('csr_rec.assignment_id.......= '||csr_rec.assignment_id,20);
2165                      hr_utility.set_location('csr_rec.tax_unit_id.........= '||csr_rec.tax_unit_id,20);
2166                   end if;
2167 
2168                     -- Create the archive assignment actions
2169                      hr_nonrun_asact.insact(l_next_assignment_action_id, csr_rec.assignment_id, p_payroll_action_id, p_chunk, csr_rec.tax_unit_id);
2170 
2171                         insert into pay_action_information(
2172                                       action_information_id,
2173                                       action_context_id,
2174                                       action_context_type,
2175                                       effective_date,
2176                                       source_id,
2177                                       tax_unit_id,
2178                                       action_information_category,
2179                                       action_information1,
2180                                       action_information2,
2181                                       action_information3,
2182                                       assignment_id
2183                                       )
2184                                       values(
2185                                       pay_action_information_s.nextval,
2186                                       l_next_assignment_action_id,
2187                                       'AAP',
2188                                       null,
2189                                       null,
2190                                       csr_rec.tax_unit_id,
2191                                       'AU_ARCHIVE_ASG_DETAILS',
2192                                       csr_rec.assignment_action_id,
2193                                       p_payroll_action_id,
2194                                       csr_rec.action_sequence,
2195                                       csr_rec.assignment_id
2196                                       );
2197 
2198 
2199                  END LOOP;/* Loop 7 */
2200                  if g_debug then
2201                  hr_utility.set_location('Leaving............Loop7. Asg + Period...' || l_procedure,1000);
2202                  end if;
2203 
2204             else
2205                     IF g_debug THEN
2206                       hr_utility.set_location('Using Range Person Cursor for fetching assignments',30);
2207                      END IF;
2208                     FOR csr_rec in rg_csr_assignment_run(p_payroll_action_id,
2209                                                          p_chunk,
2210                                                          g_parameters.employee_type,
2211                                                          g_parameters.business_group_id,
2212                                                          g_parameters.assignment_id,
2213                                                          g_parameters.period_end_date,
2214                                                          g_parameters.pact_id)
2215                     LOOP /*Loop 8 Assignment Pact_id,Period end date */
2216                      open csr_next_action_id;
2217                      fetch  csr_next_action_id into l_next_assignment_action_id;
2218                      close csr_next_action_id;
2219 
2220                      if g_debug then
2221                      hr_utility.set_location('p_payroll_action_id.........= '||p_payroll_action_id,20);
2222                      hr_utility.set_location('l_next_assignment_action_id.= '||l_next_assignment_action_id,20);
2223                      hr_utility.set_location('csr_rec.assignment_id.......= '||csr_rec.assignment_id,20);
2224                      hr_utility.set_location('csr_rec.tax_unit_id.........= '||csr_rec.tax_unit_id,20);
2225                      end if;
2226 
2227                     -- Create the archive assignment actions
2228                      hr_nonrun_asact.insact(l_next_assignment_action_id, csr_rec.assignment_id, p_payroll_action_id, p_chunk, csr_rec.tax_unit_id);
2229 
2230                         insert into pay_action_information(
2231                                       action_information_id,
2232                                       action_context_id,
2233                                       action_context_type,
2234                                       effective_date,
2235                                       source_id,
2236                                       tax_unit_id,
2237                                       action_information_category,
2238                                       action_information1,
2239                                       action_information2,
2240                                       action_information3,
2241                                       assignment_id
2242                                       )
2243                                       values(
2244                                       pay_action_information_s.nextval,
2245                                       l_next_assignment_action_id,
2246                                       'AAP',
2247                                       null,
2248                                       null,
2249                                       csr_rec.tax_unit_id,
2250                                       'AU_ARCHIVE_ASG_DETAILS',
2251                                       csr_rec.assignment_action_id,
2252                                       p_payroll_action_id,
2253                                       csr_rec.action_sequence,
2254                                       csr_rec.assignment_id
2255                                       );
2256 
2257 
2258                     END LOOP; /* Loop 8 */
2259                  if g_debug then
2260                  hr_utility.set_location('Leaving............Loop8.Asg + Run...' || l_procedure,1000);
2261                  end if;
2262              end if; /* End of Inner Assignment */
2263 
2264     else
2265 
2266     /* Default Begins */
2267 
2268        if g_parameters.start_date is not null and g_parameters.end_date is not null
2269        then
2270                  IF g_debug THEN
2271                    hr_utility.set_location('Using Range Person Cursor for fetching assignments',30);
2272                  END IF;
2273             FOR csr_rec in rg_assignment_default_period(p_payroll_action_id,
2274                                                  p_chunk,
2275                                                  g_parameters.employee_type,
2276                                                  g_parameters.business_group_id,
2277                                                  g_parameters.start_date,
2278                                                  g_parameters.end_date)
2279             LOOP /*Loop 9*/
2280              open csr_next_action_id;
2281              fetch  csr_next_action_id into l_next_assignment_action_id;
2282              close csr_next_action_id;
2283 
2284              if g_debug then
2285              hr_utility.set_location('p_payroll_action_id.........= '||p_payroll_action_id,20);
2286              hr_utility.set_location('l_next_assignment_action_id.= '||l_next_assignment_action_id,20);
2287              hr_utility.set_location('csr_rec.assignment_id.......= '||csr_rec.assignment_id,20);
2288              hr_utility.set_location('csr_rec.tax_unit_id.........= '||csr_rec.tax_unit_id,20);
2289              end if;
2290 
2291             -- Create the archive assignment actions
2292              hr_nonrun_asact.insact(l_next_assignment_action_id, csr_rec.assignment_id, p_payroll_action_id, p_chunk, csr_rec.tax_unit_id);
2293 
2294                         insert into pay_action_information(
2295                                       action_information_id,
2296                                       action_context_id,
2297                                       action_context_type,
2298                                       effective_date,
2299                                       source_id,
2300                                       tax_unit_id,
2301                                       action_information_category,
2302                                       action_information1,
2303                                       action_information2,
2304                                       action_information3,
2305                                       assignment_id
2306                                       )
2307                                       values(
2308                                       pay_action_information_s.nextval,
2309                                       l_next_assignment_action_id,
2310                                       'AAP',
2311                                       null,
2312                                       null,
2313                                       csr_rec.tax_unit_id,
2314                                       'AU_ARCHIVE_ASG_DETAILS',
2315                                       csr_rec.assignment_action_id,
2316                                       p_payroll_action_id,
2317                                       csr_rec.action_sequence,
2318                                       csr_rec.assignment_id
2319                                       );
2320 
2321 
2322             END LOOP;/* Loop 9 */
2323             if g_debug then
2324             hr_utility.set_location('Leaving............Loop9..Default + Period..' || l_procedure,1000);
2325             end if;
2326 
2327        else
2328                  IF g_debug THEN
2329                    hr_utility.set_location('Using Range Person Cursor for fetching assignments',30);
2330                  END IF;
2331                FOR csr_rec in rg_csr_assignment_default_run(p_payroll_action_id,
2332                                                  p_chunk,
2333                                                  g_parameters.employee_type,
2334                                                  g_parameters.business_group_id,
2335                                                  g_parameters.period_end_date,
2336                                                  g_parameters.pact_id)
2337                LOOP /*Loop 10 */
2338                  open csr_next_action_id;
2339              fetch  csr_next_action_id into l_next_assignment_action_id;
2340              close csr_next_action_id;
2341 
2342              if g_debug then
2343              hr_utility.set_location('p_payroll_action_id.........= '||p_payroll_action_id,20);
2344              hr_utility.set_location('l_next_assignment_action_id.= '||l_next_assignment_action_id,20);
2345              hr_utility.set_location('csr_rec.assignment_id.......= '||csr_rec.assignment_id,20);
2346              hr_utility.set_location('csr_rec.tax_unit_id.........= '||csr_rec.tax_unit_id,20);
2347              end if;
2348 
2349             -- Create the archive assignment actions
2350              hr_nonrun_asact.insact(l_next_assignment_action_id, csr_rec.assignment_id, p_payroll_action_id, p_chunk, csr_rec.tax_unit_id);
2351 
2352                         insert into pay_action_information(
2353                                       action_information_id,
2354                                       action_context_id,
2355                                       action_context_type,
2356                                       effective_date,
2357                                       source_id,
2358                                       tax_unit_id,
2359                                       action_information_category,
2360                                       action_information1,
2361                                       action_information2,
2362                                       action_information3,
2363                                       assignment_id
2364                                       )
2365                                       values(
2366                                       pay_action_information_s.nextval,
2367                                       l_next_assignment_action_id,
2368                                       'AAP',
2369                                       null,
2370                                       null,
2371                                       csr_rec.tax_unit_id,
2372                                       'AU_ARCHIVE_ASG_DETAILS',
2373                                       csr_rec.assignment_action_id,
2374                                       p_payroll_action_id,
2375                                       csr_rec.action_sequence,
2376                                       csr_rec.assignment_id
2377                                       );
2378 
2379 
2380                END LOOP; /* Loop 10 */
2381             if g_debug then
2382             hr_utility.set_location('Leaving............Loop10 Default + Run....' || l_procedure,1000);
2383             end if;
2384         end if; /* End of Inner Default */
2385 
2386 
2387     end if ;/*End Assignment id */
2388     end if ; /* End Payroll */
2389     end if; /* End Legal */
2390 end if; /* End Organization */
2391 
2392 ELSE /* 9113084 - Use Old Logic when RANGE_PERSON_ID is disabled */
2393 
2394     if g_parameters.org_id is not null
2395     then
2396        if g_parameters.start_date is not null and g_parameters.end_date is not null
2397        then
2398             FOR csr_rec in csr_assignment_org_period(p_payroll_action_id,
2399             					 p_start_person,
2400             					 p_end_person,
2401             					 g_parameters.employee_type,
2402             					 g_parameters.business_group_id,
2403             					 g_parameters.org_id,
2404             					 g_parameters.start_date,
2405             					 g_parameters.end_date)
2406             LOOP /*Loop 1 Org,Archive start date,end date */
2407              open csr_next_action_id;
2408     	     fetch  csr_next_action_id into l_next_assignment_action_id;
2409     	     close csr_next_action_id;
2410 
2411     	      if g_debug then
2412 
2413     	           hr_utility.set_location('p_payroll_action_id.........= '||p_payroll_action_id,20);
2414 	           hr_utility.set_location('l_next_assignment_action_id.= '||l_next_assignment_action_id,20);
2415 	           hr_utility.set_location('csr_rec.assignment_id.......= '||csr_rec.assignment_id,20);
2416 	           hr_utility.set_location('csr_rec.tax_unit_id.........= '||csr_rec.tax_unit_id,20);
2417 
2418 	      end if;
2419 
2420 
2421 
2422     	    -- Create the archive assignment actions
2423     	     hr_nonrun_asact.insact(l_next_assignment_action_id, csr_rec.assignment_id, p_payroll_action_id, p_chunk, csr_rec.tax_unit_id);
2424 
2425 
2426                   	insert into pay_action_information(
2427                   	              action_information_id,
2428                   	              action_context_id,
2429                   	              action_context_type,
2430                   	              effective_date,
2431                   	              source_id,
2432                   	              tax_unit_id,
2433                   	              action_information_category,
2434                   	              action_information1,
2435                   	              action_information2,
2436                   	              action_information3,
2437                   	              assignment_id
2438                   	              )
2439                   	              values(
2440                   	              pay_action_information_s.nextval,
2441                   	              l_next_assignment_action_id,
2442                   	              'AAP',
2443                   	              null,
2444                   	              null,
2445                   	              csr_rec.tax_unit_id,
2446                   	              'AU_ARCHIVE_ASG_DETAILS',
2447                   	              csr_rec.assignment_action_id,
2448                   	              p_payroll_action_id,
2449                   	              csr_rec.action_sequence,
2450                   	              csr_rec.assignment_id
2451                   	              );
2452 
2453 
2454             END LOOP;/* Loop 1 */
2455             if g_debug then
2456             hr_utility.set_location('Leaving............Loop1 Org+Period....' || l_procedure,1000);
2457             end if;
2458 
2459        else
2460                FOR csr_rec in csr_assignment_org_run(p_payroll_action_id,
2461                					 p_start_person,
2462                					 p_end_person,
2463                					 g_parameters.employee_type,
2464                					 g_parameters.business_group_id,
2465                					 g_parameters.org_id,
2466                					 g_parameters.period_end_date,
2467                					 g_parameters.pact_id)
2468                LOOP /*Loop 2 Org,Pact_id and period end date*/
2469                  open csr_next_action_id;
2470        	     fetch  csr_next_action_id into l_next_assignment_action_id;
2471        	     close csr_next_action_id;
2472 
2473        	     if g_debug then
2474        	     hr_utility.set_location('p_payroll_action_id.........= '||p_payroll_action_id,20);
2475        	     hr_utility.set_location('l_next_assignment_action_id.= '||l_next_assignment_action_id,20);
2476        	     hr_utility.set_location('csr_rec.assignment_id.......= '||csr_rec.assignment_id,20);
2477        	     hr_utility.set_location('csr_rec.tax_unit_id.........= '||csr_rec.tax_unit_id,20);
2478        	     end if;
2479 
2480        	    -- Create the archive assignment actions
2481        	     hr_nonrun_asact.insact(l_next_assignment_action_id, csr_rec.assignment_id, p_payroll_action_id, p_chunk, csr_rec.tax_unit_id);
2482 
2483                   	insert into pay_action_information(
2484                   	              action_information_id,
2485                   	              action_context_id,
2486                   	              action_context_type,
2487                   	              effective_date,
2488                   	              source_id,
2489                   	              tax_unit_id,
2490                   	              action_information_category,
2491                   	              action_information1,
2492                   	              action_information2,
2493                   	              action_information3,
2494                   	              assignment_id
2495                   	              )
2496                   	              values(
2497                   	              pay_action_information_s.nextval,
2498                   	              l_next_assignment_action_id,
2499                   	              'AAP',
2500                   	              null,
2501                   	              null,
2502                   	              csr_rec.tax_unit_id,
2503                   	              'AU_ARCHIVE_ASG_DETAILS',
2504                   	              csr_rec.assignment_action_id,
2505                   	              p_payroll_action_id,
2506                   	              csr_rec.action_sequence,
2507                   	              csr_rec.assignment_id
2508                   	              );
2509 
2510 
2511                END LOOP; /* Loop 2 */
2512             if g_debug then
2513             hr_utility.set_location('Leaving............Loop2 ,Org + Run....' || l_procedure,1000);
2514             end if;
2515         end if; /* End of Inner Organization  */
2516     else      /* Not Org,check for others */
2517 
2518     if g_parameters.legal_employer is not null
2519     then
2520        if g_parameters.start_date is not null and g_parameters.end_date is not null
2521        then
2522             FOR csr_rec in csr_assignment_legal_period(p_payroll_action_id,
2523             					 p_start_person,
2524             					 p_end_person,
2525             					 g_parameters.employee_type,
2526             					 g_parameters.business_group_id,
2527             					 g_parameters.legal_employer,
2528             					 g_parameters.start_date,
2529             					 g_parameters.end_date)
2530             LOOP /*Loop 3 Leg Employer,Archive Start date,archive end date*/
2531             /*Bug 3935471 - IF Condition used to archive all master actions and only those child actions which have tax unit id not same as master*/
2532 
2533  	         IF csr_rec.master_action_id IS NULL OR (csr_rec.tax_unit_id <> csr_rec.master_tax_unit_id AND csr_rec.master_action_id IS NOT NULL) THEN
2534                open csr_next_action_id;
2535     	         fetch  csr_next_action_id into l_next_assignment_action_id;
2536     	         close csr_next_action_id;
2537     	         if g_debug then
2538     	         hr_utility.set_location('p_payroll_action_id.........= '||p_payroll_action_id,20);
2539     	         hr_utility.set_location('l_next_assignment_action_id.= '||l_next_assignment_action_id,20);
2540     	         hr_utility.set_location('csr_rec.assignment_id.......= '||csr_rec.assignment_id,20);
2541     	         hr_utility.set_location('csr_rec.tax_unit_id.........= '||csr_rec.tax_unit_id,20);
2542     	         end if;
2543 
2544     	    -- Create the archive assignment actions
2545     	         hr_nonrun_asact.insact(l_next_assignment_action_id, csr_rec.assignment_id, p_payroll_action_id, p_chunk, csr_rec.tax_unit_id);
2546 
2547                   	insert into pay_action_information(
2548                   	              action_information_id,
2549                   	              action_context_id,
2550                   	              action_context_type,
2551                   	              effective_date,
2552                   	              source_id,
2553                   	              tax_unit_id,
2554                   	              action_information_category,
2555                   	              action_information1,
2556                   	              action_information2,
2557                   	              action_information3,
2558                   	              assignment_id
2559                   	              )
2560                   	              values(
2561                   	              pay_action_information_s.nextval,
2562                   	              l_next_assignment_action_id,
2563                   	              'AAP',
2564                   	              null,
2565                   	              null,
2566                   	              csr_rec.tax_unit_id,
2567                   	              'AU_ARCHIVE_ASG_DETAILS',
2568                   	              csr_rec.assignment_action_id,
2569                   	              p_payroll_action_id,
2570                   	              csr_rec.action_sequence,
2571                   	              csr_rec.assignment_id
2572                   	              );
2573 
2574             END IF;
2575             END LOOP;/* Loop 3 */
2576             if g_debug then
2577             hr_utility.set_location('Leaving............Loop3.Legal Emp + period...' || l_procedure,1000);
2581                FOR csr_rec in csr_assignment_legal_run(p_payroll_action_id,
2578             end if;
2579 
2580        else
2582                					 p_start_person,
2583                					 p_end_person,
2584                					 g_parameters.employee_type,
2585                					 g_parameters.business_group_id,
2586                					 g_parameters.legal_employer,
2587                					 g_parameters.period_end_date,
2588                					 g_parameters.pact_id)
2589                LOOP /*Loop 4 Leg employer,pact_id + period end date */
2590             /*Bug 3935471 - IF Condition used to archive all master actions and only those child actions which have tax unit id not same as master*/
2591 
2592        	         IF csr_rec.master_action_id IS NULL OR (csr_rec.tax_unit_id <> csr_rec.master_tax_unit_id AND csr_rec.master_action_id IS NOT NULL) THEN
2593                      open csr_next_action_id;
2594        	            fetch  csr_next_action_id into l_next_assignment_action_id;
2595        	            close csr_next_action_id;
2596 
2597              	     if g_debug then
2598                  	     hr_utility.set_location('p_payroll_action_id.........= '||p_payroll_action_id,20);
2599        	              hr_utility.set_location('l_next_assignment_action_id.= '||l_next_assignment_action_id,20);
2600               	        hr_utility.set_location('csr_rec.assignment_id.......= '||csr_rec.assignment_id,20);
2601        	              hr_utility.set_location('csr_rec.tax_unit_id.........= '||csr_rec.tax_unit_id,20);
2602              	     end if;
2603 
2604        	    -- Create the archive assignment actions
2605              	     hr_nonrun_asact.insact(l_next_assignment_action_id, csr_rec.assignment_id, p_payroll_action_id, p_chunk, csr_rec.tax_unit_id);
2606 
2607                     insert into pay_action_information(
2608                   	              action_information_id,
2609                   	              action_context_id,
2610                   	              action_context_type,
2611                   	              effective_date,
2612                   	              source_id,
2613                   	              tax_unit_id,
2614                   	              action_information_category,
2615                   	              action_information1,
2616                   	              action_information2,
2617                   	              action_information3,
2618                   	              assignment_id
2619                   	              )
2620                   	              values(
2621                   	              pay_action_information_s.nextval,
2622                   	              l_next_assignment_action_id,
2623                   	              'AAP',
2624                   	              null,
2625                   	              null,
2626                   	              csr_rec.tax_unit_id,
2627                   	              'AU_ARCHIVE_ASG_DETAILS',
2628                   	              csr_rec.assignment_action_id,
2629                   	              p_payroll_action_id,
2630                   	              csr_rec.action_sequence,
2631                   	              csr_rec.assignment_id
2632                   	              );
2633 
2634                   END IF;
2635                END LOOP; /* Loop 4 */
2636             if g_debug then
2637             hr_utility.set_location('Leaving............Loop4.Legal Emp + Run...' || l_procedure,1000);
2638             end if;
2639         end if; /* End of Inner Legal Employer  */
2640     else /* Not Org,Legal Emp Check others */
2641 
2642     if g_parameters.payroll_id is not null
2643     then
2644        if g_parameters.start_date is not null and g_parameters.end_date is not null
2645        then
2646             FOR csr_rec in csr_assignment_payroll_period(p_payroll_action_id,
2647             					 p_start_person,
2648             					 p_end_person,
2649             					 g_parameters.employee_type,
2650             					 g_parameters.business_group_id,
2651             					 g_parameters.payroll_id,
2652             					 g_parameters.start_date,
2653             					 g_parameters.end_date)
2654             LOOP /*Loop 5 Payroll, Archive start date,end date */
2655                  open csr_next_action_id;
2656     	     fetch  csr_next_action_id into l_next_assignment_action_id;
2657     	     close csr_next_action_id;
2658 
2659     	     if g_debug then
2660     	     hr_utility.set_location('p_payroll_action_id.........= '||p_payroll_action_id,20);
2661     	     hr_utility.set_location('l_next_assignment_action_id.= '||l_next_assignment_action_id,20);
2662     	     hr_utility.set_location('csr_rec.assignment_id.......= '||csr_rec.assignment_id,20);
2663     	     hr_utility.set_location('csr_rec.tax_unit_id.........= '||csr_rec.tax_unit_id,20);
2664     	     end if;
2665 
2666     	    -- Create the archive assignment actions
2667     	     hr_nonrun_asact.insact(l_next_assignment_action_id, csr_rec.assignment_id, p_payroll_action_id, p_chunk, csr_rec.tax_unit_id);
2668 
2669                   	insert into pay_action_information(
2670                   	              action_information_id,
2671                   	              action_context_id,
2672                   	              action_context_type,
2673                   	              effective_date,
2674                   	              source_id,
2675                   	              tax_unit_id,
2676                   	              action_information_category,
2677                   	              action_information1,
2678                   	              action_information2,
2679                   	              action_information3,
2680                   	              assignment_id
2681                   	              )
2682                   	              values(
2683                   	              pay_action_information_s.nextval,
2684                   	              l_next_assignment_action_id,
2685                   	              'AAP',
2686                   	              null,
2687                   	              null,
2688                   	              csr_rec.tax_unit_id,
2689                   	              'AU_ARCHIVE_ASG_DETAILS',
2690                   	              csr_rec.assignment_action_id,
2691                   	              p_payroll_action_id,
2692                   	              csr_rec.action_sequence,
2693                   	              csr_rec.assignment_id
2694                   	              );
2695 
2696 
2697             END LOOP;/* Loop 5 */
2698 
2699             if g_debug then
2700             hr_utility.set_location('Leaving............Loop5 Payroll + Period....' || l_procedure,1000);
2701             end if;
2702 
2703        else
2704                FOR csr_rec in csr_assignment_payroll_run(p_payroll_action_id,
2705                					 p_start_person,
2706                					 p_end_person,
2707                					 g_parameters.employee_type,
2708                					 g_parameters.business_group_id,
2709                					 g_parameters.payroll_id,
2710                					 g_parameters.period_end_date,
2711                					 g_parameters.pact_id)
2712                LOOP /*Loop 6 Payroll, pact_id + period end date*/
2713                  open csr_next_action_id;
2714        	     fetch  csr_next_action_id into l_next_assignment_action_id;
2715        	     close csr_next_action_id;
2716 
2717        	     if g_debug then
2718 	     hr_utility.set_location('p_payroll_action_id.........= '||p_payroll_action_id,20);
2719        	     hr_utility.set_location('l_next_assignment_action_id.= '||l_next_assignment_action_id,20);
2720        	     hr_utility.set_location('csr_rec.assignment_id.......= '||csr_rec.assignment_id,20);
2721        	     hr_utility.set_location('csr_rec.tax_unit_id.........= '||csr_rec.tax_unit_id,20);
2722        	     end if;
2723 
2724        	    -- Create the archive assignment actions
2725        	     hr_nonrun_asact.insact(l_next_assignment_action_id, csr_rec.assignment_id, p_payroll_action_id, p_chunk, csr_rec.tax_unit_id);
2726 
2727                   	insert into pay_action_information(
2728                   	              action_information_id,
2729                   	              action_context_id,
2730                   	              action_context_type,
2731                   	              effective_date,
2732                   	              source_id,
2733                   	              tax_unit_id,
2734                   	              action_information_category,
2735                   	              action_information1,
2736                   	              action_information2,
2737                   	              action_information3,
2738                   	              assignment_id
2739                   	              )
2740                   	              values(
2741                   	              pay_action_information_s.nextval,
2742                   	              l_next_assignment_action_id,
2743                   	              'AAP',
2744                   	              null,
2745                   	              null,
2746                   	              csr_rec.tax_unit_id,
2747                   	              'AU_ARCHIVE_ASG_DETAILS',
2748                   	              csr_rec.assignment_action_id,
2749                   	              p_payroll_action_id,
2750                   	              csr_rec.action_sequence,
2751                   	              csr_rec.assignment_id
2752                   	              );
2753 
2754 
2755                END LOOP; /* Loop 6 */
2756             if g_debug then
2757             hr_utility.set_location('Leaving............Loop6 Payroll+ Run....' || l_procedure,1000);
2758             end if;
2759         end if; /* End of Inner Payroll */
2760     else /* Not Org,Legal,Payroll check others */
2761 
2762     if g_parameters.assignment_id is not null
2763     then
2764          if g_parameters.start_date is not null and g_parameters.end_date is not null
2765             then
2766                  FOR csr_rec in csr_assignment_period(p_payroll_action_id,
2767                  					 p_start_person,
2768                  					 p_end_person,
2769                  					 g_parameters.employee_type,
2770                  					 g_parameters.business_group_id,
2771                  					 g_parameters.assignment_id,
2772                  					 g_parameters.start_date,
2773                  					 g_parameters.end_date)
2774                  LOOP /*Loop 7 Assignment ,Archive start date,end date*/
2775                       open csr_next_action_id;
2776          	     fetch  csr_next_action_id into l_next_assignment_action_id;
2777          	     close csr_next_action_id;
2778 
2782          	     hr_utility.set_location('csr_rec.assignment_id.......= '||csr_rec.assignment_id,20);
2779 		  if g_debug then
2780          	     hr_utility.set_location('p_payroll_action_id.........= '||p_payroll_action_id,20);
2781          	     hr_utility.set_location('l_next_assignment_action_id.= '||l_next_assignment_action_id,20);
2783          	     hr_utility.set_location('csr_rec.tax_unit_id.........= '||csr_rec.tax_unit_id,20);
2784          	  end if;
2785 
2786          	    -- Create the archive assignment actions
2787          	     hr_nonrun_asact.insact(l_next_assignment_action_id, csr_rec.assignment_id, p_payroll_action_id, p_chunk, csr_rec.tax_unit_id);
2788 
2789                   	insert into pay_action_information(
2790                   	              action_information_id,
2791                   	              action_context_id,
2792                   	              action_context_type,
2793                   	              effective_date,
2794                   	              source_id,
2795                   	              tax_unit_id,
2796                   	              action_information_category,
2797                   	              action_information1,
2798                   	              action_information2,
2799                   	              action_information3,
2800                   	              assignment_id
2801                   	              )
2802                   	              values(
2803                   	              pay_action_information_s.nextval,
2804                   	              l_next_assignment_action_id,
2805                   	              'AAP',
2806                   	              null,
2807                   	              null,
2808                   	              csr_rec.tax_unit_id,
2809                   	              'AU_ARCHIVE_ASG_DETAILS',
2810                   	              csr_rec.assignment_action_id,
2811                   	              p_payroll_action_id,
2812                   	              csr_rec.action_sequence,
2813                   	              csr_rec.assignment_id
2814                   	              );
2815 
2816 
2817                  END LOOP;/* Loop 7 */
2818                  if g_debug then
2819                  hr_utility.set_location('Leaving............Loop7. Asg + Period...' || l_procedure,1000);
2820                  end if;
2821 
2822             else
2823                     FOR csr_rec in csr_assignment_run(p_payroll_action_id,
2824                     					 p_start_person,
2825                     					 p_end_person,
2826                     					 g_parameters.employee_type,
2827                     					 g_parameters.business_group_id,
2828                     					 g_parameters.assignment_id,
2829                     					 g_parameters.period_end_date,
2830                     					 g_parameters.pact_id)
2831                     LOOP /*Loop 8 Assignment Pact_id,Period end date */
2832                      open csr_next_action_id;
2833             	     fetch  csr_next_action_id into l_next_assignment_action_id;
2834             	     close csr_next_action_id;
2835 
2836             	     if g_debug then
2837             	     hr_utility.set_location('p_payroll_action_id.........= '||p_payroll_action_id,20);
2838             	     hr_utility.set_location('l_next_assignment_action_id.= '||l_next_assignment_action_id,20);
2839             	     hr_utility.set_location('csr_rec.assignment_id.......= '||csr_rec.assignment_id,20);
2840             	     hr_utility.set_location('csr_rec.tax_unit_id.........= '||csr_rec.tax_unit_id,20);
2841             	     end if;
2842 
2843             	    -- Create the archive assignment actions
2844             	     hr_nonrun_asact.insact(l_next_assignment_action_id, csr_rec.assignment_id, p_payroll_action_id, p_chunk, csr_rec.tax_unit_id);
2845 
2846                    	insert into pay_action_information(
2847                   	              action_information_id,
2848                   	              action_context_id,
2849                   	              action_context_type,
2850                   	              effective_date,
2851                   	              source_id,
2852                   	              tax_unit_id,
2853                   	              action_information_category,
2854                   	              action_information1,
2855                   	              action_information2,
2856                   	              action_information3,
2857                   	              assignment_id
2858                   	              )
2859                   	              values(
2860                   	              pay_action_information_s.nextval,
2861                   	              l_next_assignment_action_id,
2862                   	              'AAP',
2863                   	              null,
2864                   	              null,
2865                   	              csr_rec.tax_unit_id,
2866                   	              'AU_ARCHIVE_ASG_DETAILS',
2867                   	              csr_rec.assignment_action_id,
2868                   	              p_payroll_action_id,
2869                   	              csr_rec.action_sequence,
2870                   	              csr_rec.assignment_id
2871                   	              );
2872 
2873 
2874                     END LOOP; /* Loop 8 */
2875                  if g_debug then
2876                  hr_utility.set_location('Leaving............Loop8.Asg + Run...' || l_procedure,1000);
2877                  end if;
2878              end if; /* End of Inner Assignment */
2879 
2880     else
2881 
2882     /* Default Begins */
2883 
2884        if g_parameters.start_date is not null and g_parameters.end_date is not null
2885        then
2889             					 g_parameters.employee_type,
2886             FOR csr_rec in csr_assignment_default_period(p_payroll_action_id,
2887             					 p_start_person,
2888             					 p_end_person,
2890             					 g_parameters.business_group_id,
2891             					 g_parameters.start_date,
2892             					 g_parameters.end_date)
2893             LOOP /*Loop 9*/
2894              open csr_next_action_id;
2895     	     fetch  csr_next_action_id into l_next_assignment_action_id;
2896     	     close csr_next_action_id;
2897 
2898     	     if g_debug then
2899     	     hr_utility.set_location('p_payroll_action_id.........= '||p_payroll_action_id,20);
2900     	     hr_utility.set_location('l_next_assignment_action_id.= '||l_next_assignment_action_id,20);
2901     	     hr_utility.set_location('csr_rec.assignment_id.......= '||csr_rec.assignment_id,20);
2902     	     hr_utility.set_location('csr_rec.tax_unit_id.........= '||csr_rec.tax_unit_id,20);
2903     	     end if;
2904 
2905     	    -- Create the archive assignment actions
2906     	     hr_nonrun_asact.insact(l_next_assignment_action_id, csr_rec.assignment_id, p_payroll_action_id, p_chunk, csr_rec.tax_unit_id);
2907 
2908                   	insert into pay_action_information(
2909                   	              action_information_id,
2910                   	              action_context_id,
2911                   	              action_context_type,
2912                   	              effective_date,
2913                   	              source_id,
2914                   	              tax_unit_id,
2915                   	              action_information_category,
2916                   	              action_information1,
2917                   	              action_information2,
2918                   	              action_information3,
2919                   	              assignment_id
2920                   	              )
2921                   	              values(
2922                   	              pay_action_information_s.nextval,
2923                   	              l_next_assignment_action_id,
2924                   	              'AAP',
2925                   	              null,
2926                   	              null,
2927                   	              csr_rec.tax_unit_id,
2928                   	              'AU_ARCHIVE_ASG_DETAILS',
2929                   	              csr_rec.assignment_action_id,
2930                   	              p_payroll_action_id,
2931                   	              csr_rec.action_sequence,
2932                   	              csr_rec.assignment_id
2933                   	              );
2934 
2935 
2936             END LOOP;/* Loop 9 */
2937             if g_debug then
2938             hr_utility.set_location('Leaving............Loop9..Default + Period..' || l_procedure,1000);
2939             end if;
2940 
2941        else
2942                FOR csr_rec in csr_assignment_default_run(p_payroll_action_id,
2943                					 p_start_person,
2944                					 p_end_person,
2945                					 g_parameters.employee_type,
2946                					 g_parameters.business_group_id,
2947                					 g_parameters.period_end_date,
2948                					 g_parameters.pact_id)
2949                LOOP /*Loop 10 */
2950                  open csr_next_action_id;
2951        	     fetch  csr_next_action_id into l_next_assignment_action_id;
2952        	     close csr_next_action_id;
2953 
2954        	     if g_debug then
2955        	     hr_utility.set_location('p_payroll_action_id.........= '||p_payroll_action_id,20);
2956        	     hr_utility.set_location('l_next_assignment_action_id.= '||l_next_assignment_action_id,20);
2957        	     hr_utility.set_location('csr_rec.assignment_id.......= '||csr_rec.assignment_id,20);
2958        	     hr_utility.set_location('csr_rec.tax_unit_id.........= '||csr_rec.tax_unit_id,20);
2959        	     end if;
2960 
2961        	    -- Create the archive assignment actions
2962        	     hr_nonrun_asact.insact(l_next_assignment_action_id, csr_rec.assignment_id, p_payroll_action_id, p_chunk, csr_rec.tax_unit_id);
2963 
2964                   	insert into pay_action_information(
2965                   	              action_information_id,
2966                   	              action_context_id,
2967                   	              action_context_type,
2968                   	              effective_date,
2969                   	              source_id,
2970                   	              tax_unit_id,
2971                   	              action_information_category,
2972                   	              action_information1,
2973                   	              action_information2,
2974                   	              action_information3,
2975                   	              assignment_id
2976                   	              )
2977                   	              values(
2978                   	              pay_action_information_s.nextval,
2979                   	              l_next_assignment_action_id,
2980                   	              'AAP',
2981                   	              null,
2982                   	              null,
2983                   	              csr_rec.tax_unit_id,
2984                   	              'AU_ARCHIVE_ASG_DETAILS',
2985                   	              csr_rec.assignment_action_id,
2986                   	              p_payroll_action_id,
2987                   	              csr_rec.action_sequence,
2988                   	              csr_rec.assignment_id
2989                   	              );
2990 
2991 
2992                END LOOP; /* Loop 10 */
2993             if g_debug then
2997 
2994             hr_utility.set_location('Leaving............Loop10 Default + Run....' || l_procedure,1000);
2995             end if;
2996         end if; /* End of Inner Default */
2998 
2999     end if ;/*End Assignment id */
3000     end if ; /* End Payroll */
3001     end if; /* End Legal */
3002 end if; /* End Organization */
3003 
3004 END IF;
3005 
3006 exception
3007     when others then
3008       hr_utility.set_location('Error in '||l_procedure,999999);
3009       raise;
3010 end assignment_action_code;
3011 
3012 procedure archive_code
3013   (p_assignment_action_id  in pay_assignment_actions.assignment_action_id%type
3014   ,p_effective_date        in pay_payroll_actions.effective_date%type
3015   ) is
3016 
3017  /* Bug 3873942
3018     Cursor c_employee_details - Added join with table per_grades_tl to fetch and archive the grade name.
3019     Outer Join introduced between per_grades_tl and per_assignments_f based on grade_id.
3020  */
3021 
3022  cursor c_employee_details(c_business_group_id hr_all_organization_units.organization_id%TYPE,
3023    	                    c_assignment_id number,c_end_date date, c_start_date date) /*Bug#3662449 c_start_date parameter added*/
3024   is
3025   select pap.full_name,
3026     	 paa.assignment_number,
3027 	 paa.assignment_id,
3028 	 paa.organization_id,
3029 	 hou.NAME organization_name, /*Bug 4132525*/
3030 --	 paa.payroll_id, /*Bug 4688800*/
3031 --	 papf.payroll_name, /*Bug 4132525, Bug 4688800*/
3032 	 hsc.segment1 tax_unit_id, /*Bug 4040688*/
3033 	 hou1.NAME Legal_Employer /*Bug 4132525*/
3034   from  per_people_f pap,
3035        	per_assignments_f paa,
3036 	hr_soft_coding_keyflex hsc, /*Bug 4040688*/
3037 	hr_all_organization_units hou, /*Bug 4132525*/
3038 	hr_all_organization_units hou1 /*Bug 4132525*/
3039 --        pay_payrolls_f papf /*Bug 4132525, Bug 4688800*/
3040   where  pap.person_id = paa.person_id
3041   and    paa.assignment_id = c_assignment_id
3042   and    paa.business_group_id = c_business_group_id
3043   AND    hsc.soft_coding_keyflex_id = paa.soft_coding_keyflex_id   /*Bug 4040688*/
3044   AND    hou.organization_id = paa.organization_id /*Bug 4132525*/
3045   AND    hou1.organization_id = hsc.segment1 /*Bug 4132525*/
3046 --  AND    papf.payroll_id = paa.payroll_id /*Bug 4132525, Bug 4688800*/
3047 --  AND    c_end_date BETWEEN papf.effective_start_date AND papf.effective_end_date /*Bug 4132525, Bug 4688800*/
3048   and    paa.effective_end_date = ( select max(effective_end_date) /*Bug#3662449 sub query added*/
3049                                     from  per_assignments_f
3050                                     WHERE assignment_id  =  c_assignment_id
3051                                     and effective_end_date >= c_start_date
3052                                     and effective_start_date <= c_end_date)
3053   and   c_end_date between pap.effective_start_date and pap.effective_end_date;
3054 
3055 
3056 /*Bug# 4688800 - Introduced a new cursor to get the payroll name for the employee. This has been done to take care of cases
3057                     where assignment has payroll attached to it for few months but is not attached at the end of year*/
3058  CURSOR c_get_payroll_name(c_assignment_id number,c_end_date date, c_start_date date)
3059  IS
3060  SELECT paaf.payroll_id, pay.payroll_name
3061  FROM per_all_assignments_f        paaf,
3062       pay_payrolls_f               pay
3063  WHERE paaf.assignment_id = c_assignment_id
3064  and   paaf.effective_end_date = (select max(effective_end_date)
3065    	                           From  per_assignments_f iipaf
3066 				                     WHERE iipaf.assignment_id  = c_assignment_id
3067 				                     and iipaf.effective_end_date >= c_start_date
3068 				                     and iipaf.effective_start_date <= c_end_date
3069                                  AND iipaf.payroll_id IS NOT NULL)
3070  AND  pay.payroll_id = paaf.payroll_id
3071  AND  paaf.effective_end_date BETWEEN pay.effective_start_date AND pay.effective_end_date;
3072 
3073     cursor csr_get_data (c_arc_ass_act_id number)
3074     is
3075     select pai.action_information1, pai.tax_unit_id, pai.assignment_id,pai.action_information3
3076     from pay_action_information pai
3077     where action_information_category = 'AU_ARCHIVE_ASG_DETAILS'
3078     and  pai.action_context_id = c_arc_ass_act_id;
3079 
3080 /*Bug 4040688 - Two cursors introduced to get the maximum assignment action id for the assignment*/
3081     cursor csr_get_max_asg_dates (c_assignment_id number,
3082                                    c_start_date DATE,
3083 				   c_end_date DATE,
3084 				   c_tax_unit_id number)
3085     is
3086     select  to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16))
3087             ,max(paa.action_sequence)
3088     from    pay_assignment_actions      paa
3089     ,       pay_payroll_actions         ppa
3090 	,       per_assignments_f           paf
3091     where   paa.assignment_id           = paf.assignment_id
3092 	and     paf.assignment_id           = c_assignment_id
3093             and ppa.payroll_action_id   = paa.payroll_action_id
3094             and ppa.effective_date      between c_start_date and c_end_date
3095 	    and ppa.payroll_id        =  paf.payroll_id
3096             and ppa.action_type        in ('R', 'Q', 'I', 'V', 'B')
3097 	    and ppa.effective_date between paf.effective_start_date and paf.effective_end_date
3098             and paa.action_status='C'
3099 	    AND paa.tax_unit_id = nvl(c_tax_unit_id, paa.tax_unit_id);
3100 
3101 
3102     cursor csr_get_max_asg_action (c_assignment_id number,
3103                                    c_payroll_action_id number,
3104 				   c_tax_unit_id number)
3105     is
3106     select  to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16))
3107             ,max(paa.action_sequence)
3108     from    pay_assignment_actions      paa
3109     ,       pay_payroll_actions         ppa
3110 	,       per_assignments_f           paf
3111     where   paa.assignment_id           = paf.assignment_id
3112 	and     paf.assignment_id           = c_assignment_id
3113             and ppa.payroll_action_id   = paa.payroll_action_id
3114             and ppa.payroll_action_id      = c_payroll_action_id
3115 	    and ppa.payroll_id        =  paf.payroll_id
3116             and ppa.action_type        in ('R', 'Q', 'I', 'V', 'B')
3117 	    and ppa.effective_date between paf.effective_start_date and paf.effective_end_date
3118             and paa.action_status='C'
3119 	    AND paa.tax_unit_id = nvl(c_tax_unit_id, paa.tax_unit_id);
3120 
3121 /*Bug 4040688 - end of modification*/
3122 
3123     l_procedure                       varchar2(200);
3124     l_action_information_id    	    number;
3125     l_object_version_number	    number;
3126 
3127     l_TAXABLE_EARNINGS                number;
3128     l_GROSS_EARNINGS		    number;
3129     l_PRE_TAX_DEDUCTIONS            number;
3130     l_DIRECT_PAYMENTS               number;
3131     l_NON_TAXABLE_EARNINGS    	    number;
3132     l_DEDUCTIONS          	    number;
3133     l_TAX                             number;
3134     l_NET_PAYMENT           	    number;
3135     l_EMPLOYER_CHARGES		    number;
3136 
3137     l_YTD_TAXABLE_EARNINGS            number;
3138     l_YTD_NON_TAXABLE_EARNINGS        number;
3139     l_YTD_GROSS_EARNINGS		    number;
3140     l_YTD_PRE_TAX_DEDUCTIONS            number;
3141     l_YTD_DIRECT_PAYMENTS               number;
3142     l_YTD_DEDUCTIONS          	    number;
3143     l_YTD_TAX                         number;
3144     l_YTD_NET_PAYMENT           	    number;
3145     l_YTD_EMPLOYER_CHARGES	    number;
3146 
3147     l_ass_act_id 		    number;
3148     l_tax_unit_id 	   number;
3149     l_assignment_id 		number;
3150 
3151     l_action_sequence      number;
3152     l_max_asg_action_id number; /*Bug 4040688*/
3153     l_max_action_sequence  number; /*Bug 4040688*/
3154 
3155     l_payroll_id           number;     /*Bug 4688800*/
3156     l_payroll_name         pay_payrolls_f.payroll_name%type;     /*Bug 4688800*/
3157 
3158 begin
3159 
3160     g_debug :=hr_utility.debug_enabled ;
3161 
3162     l_YTD_GROSS_EARNINGS := 0;
3163     l_YTD_NON_TAXABLE_EARNINGS := 0;
3164     l_YTD_PRE_TAX_DEDUCTIONS := 0;
3165     l_YTD_TAXABLE_EARNINGS := 0;
3166     l_YTD_TAX		 := 0;
3167     l_YTD_DEDUCTIONS := 0;
3168     l_YTD_DIRECT_PAYMENTS := 0;
3169     l_YTD_NET_PAYMENT := 0;
3170     l_YTD_EMPLOYER_CHARGES := 0;
3171 
3172     if g_debug then
3173     g_package := 'pay_au_reconciliation_pkg.' ;
3174     l_procedure  := g_package||'archive_code';
3175     hr_utility.set_location('Entering '||l_procedure,1);
3176     hr_utility.set_location('p_assignment_action_id......= '|| p_assignment_action_id,10);
3177     hr_utility.set_location('p_effective_date............= '|| to_char(p_effective_date,'DD-MON-YYYY'),10);
3178     end if;
3179 
3180     OPEN csr_get_data(p_assignment_action_id);
3181     FETCH csr_get_data into l_ass_act_id, l_tax_unit_id, l_assignment_id,l_action_sequence;
3182     CLOSE csr_get_data;
3183 
3184     if g_debug then
3185     hr_utility.set_location('l_ass_act_id......= '|| l_ass_act_id,10);
3186     hr_utility.set_location('l_tax_unit_id............= '|| l_tax_unit_id,10);
3187     hr_utility.set_location('l_assignment_id......= '|| l_assignment_id,10);
3188     end if;
3189 
3190  FOR csr_rec in c_employee_details(g_business_group_id,l_assignment_id,g_end_date,g_start_date) --Bug#3662449
3191  LOOP
3192 
3193      if g_debug then
3194      hr_utility.set_location('csr_rec.full_name............= '|| csr_rec.full_name,10);
3195      end if;
3196 
3197 
3198      IF (NVL(g_prev_assignment_id,0) <> csr_rec.assignment_id) THEN
3199      	g_prev_assignment_id := csr_rec.assignment_id;
3200 
3201 /*Bug 4040688 - Calling the cursor to get maximum assignment action id*/
3202 	IF g_parameters.pact_id IS NULL THEN
3203 	  OPEN csr_get_max_asg_dates(csr_rec.assignment_id, g_start_date, g_end_date, g_parameters.legal_employer);
3204           FETCH csr_get_max_asg_dates INTO l_max_asg_action_id, l_max_action_sequence;
3205 	  CLOSE csr_get_max_asg_dates;
3206 	ELSE
3207   	  OPEN csr_get_max_asg_action(csr_rec.assignment_id, g_parameters.pact_id, g_parameters.legal_employer);
3208           FETCH csr_get_max_asg_action INTO l_max_asg_action_id, l_max_action_sequence;
3209 	  CLOSE csr_get_max_asg_action;
3210 	END IF ;
3211 
3212  -- Archive YTD balance details
3213        	   /*Bug 3953706 - Modfied the call to procedure introduce new parameters*/
3214 	   /*Bug 4040688 - YTD Balances will be called for the maximum assignment action id of the assignment*/
3215 
3216 	 IF l_max_asg_action_id IS NOT NULL THEN
3217             pay_au_reconciliation_pkg.GET_YTD_AU_REC_BALANCES(
3218                  P_ASSIGNMENT_ACTION_ID         => l_max_asg_action_id,
3219 		 P_REGISTERED_EMPLOYER          => g_parameters.legal_employer, --2610141
3220 		 P_YTD_GROSS_EARNINGS		=> l_YTD_GROSS_EARNINGS,
3221               	 P_YTD_NON_TAXABLE_EARNINGS	=> l_YTD_NON_TAXABLE_EARNINGS,
3222 		 P_YTD_PRE_TAX_DEDUCTIONS	=> l_YTD_PRE_TAX_DEDUCTIONS,
3223              	 P_YTD_TAXABLE_EARNINGS		=> l_YTD_TAXABLE_EARNINGS,
3224               	 P_YTD_TAX			=> l_YTD_TAX		,
3225               	 P_YTD_DEDUCTIONS		=> l_YTD_DEDUCTIONS	,
3226 		 P_YTD_DIRECT_PAYMENTS		=> l_YTD_DIRECT_PAYMENTS,
3227                  P_YTD_NET_PAYMENT		=> l_YTD_NET_PAYMENT	,
3228        	         P_YTD_EMPLOYER_CHARGES		=> l_YTD_EMPLOYER_CHARGES);
3229           END IF ;
3230 
3231                      insert into pay_action_information (
3232                                       action_information_id,
3233                                       action_context_id,
3234                                       action_context_type,
3235              			       effective_date,
3236              			       source_id,
3237                                       tax_unit_id,
3238                                       assignment_id,
3239                                       action_information_category,
3240                                       action_information1,
3241                                       action_information2,
3242                                       action_information3,
3243                                       action_information4,
3244                                       action_information5,
3245                                       action_information6,
3246                                       action_information7,
3247                                       action_information8,
3248                                       action_information9,
3249                                       action_information10)
3250                           values (
3251                                 pay_action_information_s.nextval,
3252                                 p_assignment_action_id,
3253              			 'AAP',
3254                                 p_effective_date,
3255                                 null,
3256              			 null,
3257              			 l_assignment_id,
3258              			 'AU_BALANCE_RECON_DETAILS_YTD',
3259              			 l_YTD_TAXABLE_EARNINGS,
3260              			 l_YTD_NON_TAXABLE_EARNINGS,
3261              			 l_YTD_DEDUCTIONS,
3262              			 l_YTD_TAX,
3263              			 l_YTD_NET_PAYMENT,
3264              			 l_YTD_EMPLOYER_CHARGES,
3265 				 l_YTD_GROSS_EARNINGS,
3266 				 l_YTD_PRE_TAX_DEDUCTIONS,
3267 				 l_YTD_DIRECT_PAYMENTS,
3268                                  l_max_action_sequence);
3269 
3270 /*Bug 4040688 - end of modification*/
3271 
3272 
3273         /*Bug 4688800*/
3274         OPEN c_get_payroll_name(l_assignment_id,g_end_date,g_start_date);
3275         FETCH c_get_payroll_name INTO l_payroll_id,l_payroll_name;
3276         CLOSE c_get_payroll_name;
3277 
3278         insert into pay_action_information(
3279                             action_information_id,
3280 			    action_context_id,
3281 			    action_context_type,
3282 			    effective_date,
3283 			    source_id,
3284 			    tax_unit_id,
3285 			    action_information_category,
3286 			    action_information1,
3287 			    action_information2,
3288 			    action_information8,
3289 			    action_information9,
3290 			    action_information10,
3291 			    assignment_id)
3292 		    values(
3293 		            pay_action_information_s.nextval,
3294 		            g_arc_payroll_action_id,
3295 		            'PA',
3296 		            p_effective_date,
3297 		            null,
3298 		            l_tax_unit_id,
3299 		            'AU_EMPLOYEE_RECON_DETAILS',
3300 		            csr_rec.full_name,
3301 		            csr_rec.assignment_number,
3302 		            csr_rec.organization_name, /*Bug 4132525*/
3303 		            csr_rec.Legal_Employer, /*Bug 4040688, Bug 4132525*/
3304 		            l_payroll_name, /*Bug 4132525,  Bug 4688800*/
3305 		            l_assignment_id);
3306      END IF;
3307           -- Balances Coding for BBR
3308 
3309             -- Get The Action Sequence for the Assignment_Action_Id.
3310 
3311 	   /*Bug 3891564 - Modfied the call to procedure introduce new parameters*/
3312             pay_au_reconciliation_pkg.GET_AU_REC_BALANCES(
3313                  P_ASSIGNMENT_ACTION_ID         => l_ass_act_id,
3314 		 P_REGISTERED_EMPLOYER          => g_parameters.legal_employer,
3315 		 P_GROSS_EARNINGS		=> l_GROSS_EARNINGS,
3316              	 P_NON_TAXABLE_EARNINGS         => l_NON_TAXABLE_EARNINGS,
3317 		 P_PRE_TAX_DEDUCTIONS		=> l_PRE_TAX_DEDUCTIONS,
3318                  P_TAXABLE_EARNINGS             => l_TAXABLE_EARNINGS    ,
3319              	 P_TAX                          => l_TAX                 ,
3320              	 P_DEDUCTIONS                   => l_DEDUCTIONS          ,
3321 		 P_DIRECT_PAYMENTS		=> l_DIRECT_PAYMENTS,
3322              	 P_NET_PAYMENT                  => l_NET_PAYMENT         ,
3323              	 P_EMPLOYER_CHARGES             => l_EMPLOYER_CHARGES);
3324 
3325            --
3326            -- Insert the balance data into pay_action_information table
3327            -- This Direct Insert statement is for Performance Reasons.
3328            --
3329              /*Bug 4040688 - Modified contexts which will store only the run balance values.*/
3330              insert into pay_action_information (
3331                                       action_information_id,
3332                                       action_context_id,
3333                                       action_context_type,
3334              			       effective_date,
3335              			       source_id,
3336                                       tax_unit_id,
3337                                       assignment_id,
3338                                       action_information_category,
3339                                       action_information1,
3340                                       action_information2,
3341                                       action_information3,
3342                                       action_information4,
3343                                       action_information5,
3344                                       action_information6,
3345                                       action_information7,
3346                                       action_information8,
3347                                       action_information9,
3348                                       action_information10)
3349                           values (
3350                                 pay_action_information_s.nextval,
3351                                 p_assignment_action_id,
3352              			 'AAP',
3353                                 p_effective_date,
3354                                 null,
3355              			 l_tax_unit_id,
3356              			 l_assignment_id,
3357              			 'AU_BALANCE_RECON_DETAILS_RUN',
3358              			 l_taxable_earnings,
3359              			 l_NON_TAXABLE_EARNINGS,
3360              			 l_DEDUCTIONS,
3361              			 l_TAX,
3362              			 l_NET_PAYMENT,
3363              			 l_EMPLOYER_CHARGES,
3364 				 l_GROSS_EARNINGS,
3365 				 l_PRE_TAX_DEDUCTIONS,
3366 				 l_DIRECT_PAYMENTS,
3367                                  l_action_sequence);
3368 
3369  END LOOP; /* End of assignments for employee */
3370 
3371 end archive_code;
3372 
3373 procedure spawn_archive_reports
3374 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type)
3375   is
3376  l_count                number;
3377  ps_request_id          NUMBER;
3378  l_print_style          VARCHAR2(2);
3379  l_print_together       VARCHAR2(80);
3380  l_print_return         BOOLEAN;
3381  l_procedure         varchar2(50);
3382  l_program_name      varchar2(50);   /* Bug 4947424 */
3383 
3384   --------------------------------------------------------------------+
3385   -- Cursor      : csr_params
3386   -- Description : Fetches User Parameters from Legislative_paramters
3387   --               column.
3388   --------------------------------------------------------------------+
3389 
3390    CURSOR csr_report_params(c_payroll_action_id  pay_payroll_actions.payroll_action_id%TYPE)
3391       IS
3392         SELECT pay_core_utils.get_parameter('PAY',legislative_parameters)        payroll_id,
3393                    pay_core_utils.get_parameter('ORG',legislative_parameters)           org_id,
3394                    pay_core_utils.get_parameter('BG',legislative_parameters)    business_group_id,
3395                    to_date(pay_core_utils.get_parameter('SDATE',legislative_parameters),'YYYY/MM/DD') start_date,
3396                    to_date(pay_core_utils.get_parameter('EDATE',legislative_parameters),'YYYY/MM/DD')   end_date,
3397                    pay_core_utils.get_parameter('PACTID',legislative_parameters)        pact_id,
3398                    pay_core_utils.get_parameter('LE',legislative_parameters) legal_employer,
3399                    pay_core_utils.get_parameter('ASG',legislative_parameters) assignment_id,
3400                    pay_core_utils.get_parameter('SO1',legislative_parameters)   sort_order_1,
3401                    pay_core_utils.get_parameter('SO2',legislative_parameters)   sort_order_2,
3402                    pay_core_utils.get_parameter('SO3',legislative_parameters)   sort_order_3,
3403                    pay_core_utils.get_parameter('SO4',legislative_parameters)   sort_order_4,
3404                    to_date(pay_core_utils.get_parameter('PEDATE',legislative_parameters),'YYYY/MM/DD') period_end_date,
3405                    pay_core_utils.get_parameter('YTD_TOT',legislative_parameters)      ytd_totals,
3406                    pay_core_utils.get_parameter('ZERO_REC',legislative_parameters)    zero_records,
3407                    pay_core_utils.get_parameter('NEG_REC',legislative_parameters)     negative_records,
3408                    pay_core_utils.get_parameter('EMP_TYPE',legislative_parameters) employee_type,
3409                    pay_core_utils.get_parameter('DEL_ACT',legislative_parameters) delete_actions,  /*Bug# 4142159*/
3410                    pay_core_utils.get_parameter('OUTPUT_TYPE',legislative_parameters) output_type  /*Bug# 4947424*/
3411                    FROM pay_payroll_actions ppa
3412       WHERE ppa.payroll_action_id  =  c_payroll_action_id;
3413 
3414 
3415  cursor csr_get_print_options(p_payroll_action_id NUMBER) IS
3416  SELECT printer,
3417           print_style,
3418           decode(save_output_flag, 'Y', 'TRUE', 'N', 'FALSE') save_output
3419       ,number_of_copies /* Bug 4116833*/
3420     FROM  pay_payroll_actions pact,
3421           fnd_concurrent_requests fcr
3422     WHERE fcr.request_id = pact.request_id
3423     AND   pact.payroll_action_id = p_payroll_action_id;
3424 
3425 
3426  rec_print_options  csr_get_print_options%ROWTYPE;
3427 
3428  l_parameters csr_report_params%ROWTYPE; /* Bug 3891577*/
3429 
3430   Begin
3431     l_count           :=0;
3432     ps_request_id     :=-1;
3433     g_debug :=hr_utility.debug_enabled ;
3434 
3435 
3436              if g_debug then
3437              g_package := 'pay_au_reconciliation_pkg.' ;
3438              l_procedure := g_package||' spawn_archive_reports';
3439              hr_utility.set_location('Entering '||l_procedure,999);
3440              end if;
3441 
3442 -- Set User Parameters for Report.
3443 
3444              open csr_report_params(p_payroll_action_id);
3445              fetch csr_report_params into l_parameters;
3446              close csr_report_params;
3447 
3448              /*Bug 4040688 -- Moved the call to check parameters validations from report to archive code.*/
3449              check_report_parameters(l_parameters.start_date
3450                                     ,l_parameters.end_date
3451                                     ,l_parameters.period_end_date);
3452 
3453           if g_debug then
3454                    hr_utility.set_location('payroll_parameters.action '||p_payroll_action_id,0);
3455                    hr_utility.set_location('in BG_ID '||l_parameters.business_group_id,1);
3456                    hr_utility.set_location('in org_id '||l_parameters.org_id,2);
3457                    hr_utility.set_location('in payroll_parameters.id '||l_parameters.payroll_id,3);
3458                    hr_utility.set_location('in asg_id '||l_parameters.assignment_id,4);
3459                    hr_utility.set_location('in archive start date '||to_char(l_parameters.start_date,'YYYY/MM/DD'),5);
3460                    hr_utility.set_location('in archive end date '||to_char(l_parameters.end_date,'YYYY/MM/DD'),6);
3461                    hr_utility.set_location('in pact_id '||l_parameters.pact_id,7);
3462                    hr_utility.set_location('in legal employer '||l_parameters.legal_employer,8);
3463                    hr_utility.set_location('in PERIOD END DATE '||to_char(l_parameters.period_end_date,'YYYY/MM/DD'),9);
3464                    hr_utility.set_location('in YTD totals '||l_parameters.ytd_totals,10);
3465                    hr_utility.set_location('in zero records'||l_parameters.zero_records,11);
3466                    hr_utility.set_location('in Negative records'||l_parameters.negative_records,12);
3467                    hr_utility.set_location('in emp_type '||l_parameters.employee_type,14);
3468                    hr_utility.set_location('in sort order 1'||l_parameters.sort_order_1,15);
3469                    hr_utility.set_location('in sort order 2'||l_parameters.sort_order_2,16);
3470                    hr_utility.set_location('in sort order 3'||l_parameters.sort_order_3,17);
3471                    hr_utility.set_location('in sort order 4'||l_parameters.sort_order_4,18);
3472                    hr_utility.set_location('in delete action'||l_parameters.delete_actions,19); /*Bug# 4142159*/
3473                    hr_utility.set_location('in Output Type '||l_parameters.output_type,20);     /*Bug# 4947424*/
3474             end if;
3475 
3476      if g_debug then
3477       hr_utility.set_location('Afer payroll action ' || p_payroll_action_id , 125);
3478 
3479       hr_utility.set_location('Before calling report',24);
3480       end if;
3481 
3482        OPEN csr_get_print_options(p_payroll_action_id);
3483        FETCH csr_get_print_options INTO rec_print_options;
3484        CLOSE csr_get_print_options;
3485        --
3486        l_print_together := nvl(fnd_profile.value('CONC_PRINT_TOGETHER'), 'N');
3487        --
3488        -- Set printer options
3489        l_print_return := fnd_request.set_print_options
3490                            (printer        => rec_print_options.printer,
3491                             style          => rec_print_options.print_style,
3492                             copies         => rec_print_options.number_of_copies,/* Bug 4116833*/
3493                             save_output    => hr_general.char_to_bool(rec_print_options.save_output),
3494                             print_together => l_print_together);
3495     -- Submit report
3496       if g_debug then
3497       hr_utility.set_location('payroll_action id    '|| p_payroll_action_id,25);
3498       end if;
3499 
3500 /* Bug 4947424 - Check for Report Output Type and choose appropriate
3501                  concurrent program to submit
3502 */
3503        if l_parameters.output_type = 'TEXT'
3504        then
3505             l_program_name := 'PYAURECSR_TXT';
3506        else
3507             l_program_name := 'PYAURECSR';
3508        end if;
3509 
3510 /* Bug 3891577 - Added the Template Name parameter to Report.
3511                  This is done to enable the PDF Output for report.
3512     Backed out this parameter as template name is not specified here in 2-setp process.*/
3513 ps_request_id := fnd_request.submit_request
3514  ('PAY',
3515   l_program_name,                                   /* Bug 4947424 */
3516    null,
3517    null,
3518    false,
3519    'P_PAYROLL_ACTION_ID='||to_char(p_payroll_action_id),
3520    'P_BUSINESS_GROUP_ID='||l_parameters.business_group_id,
3521    'P_ORGANIZATION_ID='||l_parameters.org_id,
3522    'P_PAYROLL_ID='||l_parameters.payroll_id,
3523    'P_REGISTERED_EMPLOYER='||l_parameters.legal_employer,
3524    'P_ASSIGNMENT_ID='||l_parameters.assignment_id,
3525    'P_START_DATE='||to_char(l_parameters.start_date,'YYYY/MM/DD'),
3526    'P_END_DATE='||to_char(l_parameters.end_date,'YYYY/MM/DD'),
3527    'P_PAYROLL_RUN_ID='||l_parameters.pact_id,
3528    'P_PERIOD_END_DATE='||to_char(l_parameters.period_end_date,'YYYY/MM/DD'),
3529    'P_EMPLOYEE_TYPE='||l_parameters.employee_type,
3530    'P_YTD_TOTALS='||l_parameters.ytd_totals,
3531    'P_ZERO_RECORDS='||l_parameters.zero_records,
3532    'P_NEGATIVE_RECORDS='||l_parameters.negative_records,
3533    'P_SORT_ORDER_1='||l_parameters.sort_order_1,
3534    'P_SORT_ORDER_2='||l_parameters.sort_order_2,
3535    'P_SORT_ORDER_3='||l_parameters.sort_order_3,
3536    'P_SORT_ORDER_4='||l_parameters.sort_order_4,
3537    'P_DELETE_ACTIONS='||l_parameters.delete_actions, /*Bug# 4142159*/
3538    'BLANKPAGES=NO',
3539    NULL,   NULL,   NULL,   NULL,   NULL,   NULL,
3540    NULL,   NULL,   NULL,   NULL,   NULL,   NULL,
3541    NULL,   NULL,   NULL,   NULL,   NULL,   NULL,
3542    NULL,   NULL,   NULL,   NULL,   NULL,   NULL,
3543    NULL,   NULL,   NULL,   NULL,   NULL,   NULL,
3544    NULL,   NULL,   NULL,   NULL,   NULL,   NULL,
3545    NULL,   NULL,   NULL,   NULL,   NULL,   NULL,
3546    NULL,   NULL,   NULL,   NULL,   NULL,   NULL,
3547    NULL,   NULL,   NULL,   NULL,   NULL,   NULL,
3548    NULL,   NULL,   NULL,   NULL,   NULL,   NULL,
3549    NULL,   NULL,   NULL,   NULL,   NULL,   NULL,
3550    NULL,   NULL,   NULL,   NULL,   NULL,   NULL,
3551    NULL,   NULL,   NULL,   NULL
3552 );
3553       if g_debug then
3554       hr_utility.set_location('After calling report',24);
3555       end if;
3556 
3557 end spawn_archive_reports;
3558 
3559 procedure check_report_parameters
3560           (p_start_date        IN date,
3561            p_end_date          IN date,
3562            p_period_end_date   IN date) is
3563 
3564     e_bad_end_date                    exception ;
3565     e_bad_combination_date            exception ;
3566 
3567 begin
3568 
3569    IF g_debug THEN
3570       hr_utility.trace('Entering:' || g_package  || 'check_report_parameters');
3571    END IF;
3572 
3573    if p_start_date is not null and p_end_date is null then
3574      raise e_bad_end_date;
3575    end if;
3576 
3577    if p_start_date is null and p_period_end_date is null then
3578      raise e_bad_combination_date;
3579    end if;
3580 
3581 
3582 exception
3583    when e_bad_end_date
3584    then
3585      hr_utility.set_message(801, 'HR_AU_REC_MISSING_END_DATE');
3586      hr_utility.raise_error;
3587    when e_bad_combination_date
3588    then
3589      hr_utility.set_message(801, 'HR_AU_REC_COMBINATION_DATES');
3590      hr_utility.raise_error;
3591 
3592 end check_report_parameters;
3593 
3594 BEGIN
3595 /*Bug 2610141 - Code added to remove the gscc warnings */
3596 g_debug := hr_utility.debug_enabled;
3597 g_package := 'pay_au_reconciliation_pkg.';
3598 
3599 end pay_au_reconciliation_pkg;