[Home] [Help]
PACKAGE BODY: APPS.PAY_FR_ARC_PKG
Source
1 package body PAY_FR_ARC_PKG as
2 /* $Header: pyfrarch.pkb 120.3 2006/03/05 12:07:10 aparkes noship $ */
3 --
4 -- Globals
5 --
6 g_package CONSTANT VARCHAR2(20):= 'pay_fr_arc_pkg.';
7 --
8 -- Parameters to the process - g_payroll_action_id is the cache context
9 --
10 g_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE;
11 g_param_payroll_id pay_all_payrolls_f.payroll_id%TYPE;
12 g_param_assignment_id per_all_assignments_f.assignment_id%TYPE;
13 g_param_assignment_set_id hr_assignment_sets.assignment_set_id%TYPE;
14 g_param_business_group_id per_business_Groups.business_group_id%TYPE;
15 g_param_start_date date;
16 g_param_effective_date date;
17 --
18 --
19 -- Globals for scope of ARCHINIT and ARCHIVE CODE
20 --
21 --
22 -- Global Defined Balance IDs
23 --
24 g_1total_gross_pay_db pay_defined_balances.defined_balance_id%TYPE;
25 g_2ss_ceiling_db pay_defined_balances.defined_balance_id%TYPE;
26 g_3es_total_contributions_db pay_defined_balances.defined_balance_id%TYPE;
27 g_4statutory_er_charges_db pay_defined_balances.defined_balance_id%TYPE;
28 g_5conventional_er_charges_db pay_defined_balances.defined_balance_id%TYPE;
29 g_6t1_arrco_band_db pay_defined_balances.defined_balance_id%TYPE;
30 g_7t2_arrco_band_db pay_defined_balances.defined_balance_id%TYPE;
31 g_8tb_argic_band_db pay_defined_balances.defined_balance_id%TYPE;
32 g_9tc_agirc_band_db pay_defined_balances.defined_balance_id%TYPE;
33 g_10gmp_agirc_band_db pay_defined_balances.defined_balance_id%TYPE;
34 g_11total_cost_to_employer_db pay_defined_balances.defined_balance_id%TYPE;
35 g_12taxable_income_db pay_defined_balances.defined_balance_id%TYPE;
36 --
37 -- Global dimension IDs
38 --
39 g_asg_run pay_defined_balances.balance_dimension_id%TYPE;
40 g_asg_pro_run pay_defined_balances.balance_dimension_id%TYPE;
41 g_asg_et_pr_ra_cu_run pay_defined_balances.balance_dimension_id%TYPE;
42 g_asg_et_pr_ra_cu_pro_run pay_defined_balances.balance_dimension_id%TYPE;
43 g_asg_et_pr_cu_run pay_defined_balances.balance_dimension_id%TYPE;
44 g_asg_et_pr_cu_pro_run pay_defined_balances.balance_dimension_id%TYPE;
45 --
46 -- global totals contexts (holds lookup codes to get meanings)
47 --
48 g_totals_c1_total_gross varchar2(30) := 'TOTAL_GROSS';
49 g_totals_c2_total_subject varchar2(30) := 'TOTAL_SUBJECT';
50 g_totals_c3_total_deductions varchar2(30) := 'TOTAL_DEDUCTIONS'; /* holds name not value */
51 g_totals_c3_total_deduct_ee varchar2(30) := 'TOTAL_DEDUCTIONS_EE'; /* not used in names, used in values */
52 g_totals_c3_total_deduct_er varchar2(30) := 'TOTAL_DEDUCTIONS_ER'; /* not used in names, used in values */
53 g_totals_c4_taxable_income varchar2(30) := 'TAXABLE_INCOME';
54 g_totals_c5_total_charges varchar2(30) := 'TOTAL_CHARGES'; /* holds name not value */
55 g_totals_c5_total_charges_ee varchar2(30) := 'TOTAL_CHARGES_EE'; /* not used in names, used in values */
56 g_totals_c5_total_charges_er varchar2(30) := 'TOTAL_CHARGES_ER'; /* not used in names, used in values */
57 g_totals_c6_net_salary varchar2(30) := 'NET_SALARY';
58 g_totals_c7_total_pay varchar2(30) := 'TOTAL_PAY';
59 g_totals_c8_previous_advice varchar2(30) := 'PREVIOUS_ADVICE';
60 g_totals_c9_this_advice varchar2(30) := 'THIS_ADVICE';
61 g_totals_c10_net_advice varchar2(30) := 'NET_ADVICE';
62 --
63 -- global balance contexts (holds lookup codes to get meanings)
64 --
65 g_balance_c1_total_gross varchar2(30) := 'TOTAL_GROSS_PAY';
66 g_balance_c2_ss_ceiling varchar2(30) := 'SS_CEILING';
67 g_balance_c3_ee_total_conts varchar2(30) := 'EMPLOYEES_TOTAL_CONTRIBUTIONS';
68 g_balance_c4_stat_er_charges varchar2(30) := 'STATUTORY_EMPLOYER_CHARGES';
69 g_balance_c5_conv_er_charges varchar2(30) := 'CONVENTIONAL_EMPLOYER_CHARGES';
70 g_balance_c6_t1_arrco varchar2(30) := 'T1_ARRCO_BAND';
71 g_balance_c7_t2_arrco varchar2(30) := 'T2_ARRCO_BAND';
72 g_balance_c8_tb_agirc varchar2(30) := 'TB_AGIRC_BAND';
73 g_balance_c9_tc_agirc varchar2(30) := 'TC_AGIRC_BAND';
74 g_balance_c10_gmp varchar2(30) := 'GMP_AGIRC_BAND';
75 g_balance_c11_total_er_cost varchar2(30) := 'TOTAL_COST_TO_EMPLOYER';
76 g_balance_c12_taxable_income varchar2(30) := 'TAXABLE_INCOME';
77 --
78 -- globals for name translations
79 --
80 g_us_name_pay_value varchar2(10) := 'Pay Value';
81 g_us_name_rate varchar2(10) := 'Rate';
82 g_us_name_base varchar2(10) := 'Base';
83 g_us_name_start_date varchar2(10) := 'Start Date';
84 g_us_name_end_date varchar2(10) := 'End Date';
85 g_retro_tl fnd_lookup_values.meaning%TYPE := 'Default Retro';
86 g_fr_name_pay_value fnd_lookup_values.meaning%TYPE;
87 g_fr_name_rate fnd_lookup_values.meaning%TYPE;
88 g_fr_name_base fnd_lookup_values.meaning%TYPE;
89 g_fr_name_start_date fnd_lookup_values.meaning%TYPE;
90 g_fr_name_end_date fnd_lookup_values.meaning%TYPE;
91 --
92 --
93 g_source_text ff_contexts.context_id%TYPE;
94 --
95 -- To hold termination element id - special processing for termination
96 --
97 g_term_ele_subject_to_ss pay_element_types_f.element_Type_id%TYPE;
98 g_term_ele_exempt_of_ss pay_element_types_f.element_Type_id%TYPE;
99 --
100 -- to hold the application_id of 'PER'
101 --
102 g_per_id fnd_application.application_id%TYPE;
103 --
104 g_CSG_non_Deductible pay_balance_categories_f.balance_category_id%TYPE;
105 g_Conv_EE_Deductions pay_balance_categories_f.balance_category_id%TYPE;
106 g_Conv_ER_Charges pay_balance_categories_f.balance_category_id%TYPE;
107 g_Income_Tax_Excess pay_balance_categories_f.balance_category_id%TYPE;
108 g_Rebates pay_balance_categories_f.balance_category_id%TYPE;
109 g_Stat_EE_Deductions pay_balance_categories_f.balance_category_id%TYPE;
110 g_Stat_ER_Charges pay_balance_categories_f.balance_category_id%TYPE;
111 --
112 g_ele_class_CSG_non_Deductible pay_element_classifications.classification_id%TYPE;
113 g_ele_class_Conv_EE_Deductions pay_element_classifications.classification_id%TYPE;
114 g_ele_class_Conv_ER_Charges pay_element_classifications.classification_id%TYPE;
115 g_ele_class_Income_Tax_Excess pay_element_classifications.classification_id%TYPE;
116 g_ele_class_Rebates pay_element_classifications.classification_id%TYPE;
117 g_ele_class_Stat_EE_Deductions pay_element_classifications.classification_id%TYPE;
118 g_ele_class_Stat_ER_Charges pay_element_classifications.classification_id%TYPE;
119 g_ele_class_Net_EE_Deductions pay_element_classifications.classification_id%TYPE;
120 g_ele_class_ER_LV_Charges pay_element_classifications.classification_id%TYPE;
121 --
122 -- Globals added for security groups (bug 3683906)
123 g_sec_grp_id_user_element_grp FND_LOOKUP_VALUES.SECURITY_GROUP_ID%TYPE;
124 g_sec_grp_id_base_unit FND_LOOKUP_VALUES.SECURITY_GROUP_ID%TYPE;
125 g_sec_grp_id_element_grp FND_LOOKUP_VALUES.SECURITY_GROUP_ID%TYPE;
126 -- more added for bug 4778143:
127 g_sec_grp_id_process_type FND_LOOKUP_VALUES.SECURITY_GROUP_ID%TYPE;
128 g_sec_grp_id_fixed_time_units FND_LOOKUP_VALUES.SECURITY_GROUP_ID%TYPE;
129 g_sec_grp_id_fixed_time_freq FND_LOOKUP_VALUES.SECURITY_GROUP_ID%TYPE;
130 --
131 ---------------------------------------------------------------------------------------------------
132 -- ARCHIVE HOOK POINTS
133 --
134 ---------------------------------------------------------------------------------------------------
135 -- RANGE CURSOR
136 -- DESCRIPTION : Single threaded. Performs 1-off archiving of :
137 -- - Establishment data and address
138 -- - Company data and address
139 -- Returns the Range Cursor String
140 ---------------------------------------------------------------------------------------------------
141 procedure range_cursor (
142 pactid in number
143 ,sqlstr out nocopy varchar) is
144 --
145 l_proc VARCHAR2(40) := g_package||' range_cursor ';
146 --
147 BEGIN
148 --
149 -- Load the boilerplate for totals and balances against their entities
150 --
151 hr_utility.set_location('Entering ' || l_proc,10);
152 --
153 -- Get the descriptive text for running subtotals and YTD balances
154 --
155 pay_fr_arc_pkg.load_payslip_text (
156 p_action_id => pactid);
157 --
158 hr_utility.set_location('Step ' || l_proc,20);
159 --
160 -- Return the select string
161 --
162 sqlstr := 'SELECT DISTINCT person_id
163 FROM per_people_f ppf
164 ,pay_payroll_actions ppa
165 WHERE ppa.payroll_action_id = :payroll_action_id
166 AND ppa.business_group_id = ppf.business_group_id
167 ORDER BY ppf.person_id';
168 --
169 hr_utility.set_location(' Leaving: '||l_proc,50);
170 EXCEPTION
171 WHEN OTHERS THEN
172 hr_utility.set_location(' Leaving with EXCEPTION: '||l_proc,50);
173 -- Return cursor that selects no rows
174 sqlstr := 'select 1 from dual where to_char(:payroll_action_id) = dummy';
175 END range_cursor;
176 ---------------------------------------------------------------------------------------------------
177 -- ACTION CREATION --
178 -- DESCRIPTION : Creates new assignment actions under the (archive) payroll action
179 -- creates one per master or normal prepayment action.
180 -- restricts to user chosen parameters - assignment_id
181 -- - payroll_id
182 -- and Locks all prepayment master / normal actions for the assignment in period
183 ---------------------------------------------------------------------------------------------------
184 PROCEDURE action_creation (pactid IN NUMBER,
185 stperson IN NUMBER,
186 endperson IN NUMBER,
187 chunk IN NUMBER) IS
188 --
189 l_actid pay_assignment_actions.assignment_action_id%TYPE;
190 --
191 l_proc VARCHAR2(60):= g_package||' action_creation ';
192 --
193 --This cursor fetches all master (or standard) prepayment assignment actions
194 --
195 cursor csr_assignments (p_stperson number, p_endperson number) IS
196 SELECT pre_assact.assignment_action_id assignment_action_id
197 ,pre_assact.assignment_id assignment_id
198 ,pre_assact.tax_unit_id establishment_id
199 FROM per_all_assignments_f asg
200 ,pay_assignment_actions pre_assact
201 ,pay_payroll_actions pre_payact
202 WHERE asg.person_id between p_stperson and p_endperson
203 and asg.period_of_service_id is not null
204 and g_param_effective_date between asg.effective_start_date
205 and asg.effective_end_date
206 and asg.assignment_id = pre_assact.assignment_id
207 and pre_assact.source_action_id is null /* not a child */
208 and pre_assact.action_status = 'C'
209 and pre_payact.payroll_action_id = pre_assact.payroll_action_id
210 and pre_payact.action_status = 'C'
211 and pre_payact.action_type in ('P','U')
212 and pre_payact.payroll_id = g_param_payroll_id
213 and pre_payact.effective_date between g_param_start_date
214 and g_param_effective_date;
215 --
216 rec_this csr_assignments%ROWTYPE;
217 l_counter number := 0;
218 --
219 BEGIN
220 hr_utility.set_location('Entering ' || l_proc,20);
221
222 if g_payroll_action_id is null
223 or g_payroll_action_id <> pactid
224 then
225 pay_fr_arc_pkg.get_all_parameters
226 (p_payroll_action_id => pactid
227 ,p_payroll_id => g_param_payroll_id
228 ,p_assignment_id => g_param_assignment_id
229 ,p_assignment_set_id => g_param_assignment_set_id
230 ,p_business_Group_id => g_param_business_group_id
231 ,p_start_date => g_param_start_date
232 ,p_effective_date => g_param_effective_date);
233 g_payroll_action_id := pactid;
234 end if;
235 --
236 -- Fetch the first record
237 --
238
239 open csr_assignments(stperson, endperson);
240 LOOP
241 Fetch csr_assignments into rec_this;
242 EXIT WHEN csr_assignments%NOTFOUND;
243 -- create the action
244 SELECT pay_assignment_actions_s.nextval
245 INTO l_actid
246 FROM dual;
247 hr_nonrun_asact.insact(l_actid
248 ,rec_this.assignment_id
249 ,pactid
250 ,chunk
251 ,rec_this.establishment_id);
252 -- lock this prepayment record with the newly created assignment action
253 hr_nonrun_asact.insint(l_actid, rec_this.assignment_action_id);
254 END LOOP;
255 hr_utility.set_location('Leaving ' || l_proc, 100);
256 EXCEPTION
257 WHEN OTHERS THEN
258 hr_utility.set_location(' Leaving with EXCEPTION: '||l_proc,50);
259 END action_creation;
260 ---------------------------------------------------------------------------------------------------
261 -- ARCHINIT --
262 -- DESCRIPTION : populates defined balance ids for later use.
263 -- loads all entity ids into globals
264 -- gets rebate sub classs id into global
265 -- loads user parameters into globals.
266 ---------------------------------------------------------------------------------------------------
267 procedure archinit(
268 p_payroll_action_id in number) is
269 --
270 cursor csr_context(p_name varchar2) is
271 select context_id
272 from ff_contexts
273 where context_name = p_name;
274 --
275 -- Get the element_type_ids of the termination elements
276 --
277 cursor csr_termination_elements (p_element_name varchar2) is
278 select element_type_id
279 from pay_element_types_f
280 where element_name = p_element_name
281 and legislation_code = 'FR';
282 --
283 cursor csr_get_dimension (p_name varchar2) is
284 select balance_dimension_id
285 from pay_balance_dimensions
286 where legislation_code = 'FR'
287 and dimension_name = p_name;
288 --
289 cursor csr_get_per_id is
290 select application_id
291 from fnd_application
292 where application_short_name = 'PER';
293 --
294 -- get the balance category IDs
295 cursor csr_get_bal_cat (p_name varchar2) is
296 select balance_category_id
297 from pay_balance_categories_f
298 where legislation_code = 'FR'
299 and category_name = p_name
300 order by effective_start_date asc;
301 --
302 -- get the element_classification IDs
303 --
304 cursor csr_get_ele_class (p_name varchar2) is
305 select classification_id
306 from pay_element_classifications
307 where legislation_code = 'FR'
308 and classification_name = p_name;
309 --
310 l_proc VARCHAR2(40):= g_package||' archinit ';
311 BEGIN
312 --
313 -- Load the defined balance ids, note balance 12 is a PTD
314 --
315 hr_utility.set_location('Entering ' || l_proc, 10);
316 hr_utility.set_location('Loading def balance ids ' || l_proc, 20);
317 g_1total_gross_pay_db := pay_fr_arc_pkg.get_balance_id('FR_TOTAL_GROSS_PAY'
318 ,'Assignment Establishment Year To Date');
319 g_2ss_ceiling_db := pay_fr_arc_pkg.get_balance_id('FR_SS_CEILING'
320 ,'Assignment Establishment Year To Date');
321 g_3es_total_contributions_db := pay_fr_arc_pkg.get_balance_id('FR_EMPLOYEES_TOTAL_CONTRIBUTIONS'
322 ,'Assignment Establishment Year To Date');
323 g_4statutory_er_charges_db := pay_fr_arc_pkg.get_balance_id('FR_STATUTORY_EMPLOYER_CHARGES'
324 ,'Assignment Establishment Year To Date');
325 g_5conventional_er_charges_db := pay_fr_arc_pkg.get_balance_id('FR_CONVENTIONAL_EMPLOYER_CHARGES'
326 ,'Assignment Establishment Year To Date');
327 g_6t1_arrco_band_db := pay_fr_arc_pkg.get_balance_id('FR_T1_ARRCO_BAND'
328 ,'Assignment Establishment Year To Date');
329 g_7t2_arrco_band_db := pay_fr_arc_pkg.get_balance_id('FR_T2_ARRCO_BAND'
330 ,'Assignment Establishment Year To Date');
331 g_8tb_argic_band_db := pay_fr_arc_pkg.get_balance_id('FR_TB_AGIRC_BAND'
332 ,'Assignment Establishment Year To Date');
333 g_9tc_agirc_band_db := pay_fr_arc_pkg.get_balance_id('FR_TC_AGIRC_BAND'
334 ,'Assignment Establishment Year To Date');
335 g_10gmp_agirc_band_db := pay_fr_arc_pkg.get_balance_id('FR_GMP_AGIRC_BAND'
336 ,'Assignment Establishment Year To Date');
337 g_11total_cost_to_employer_db := pay_fr_arc_pkg.get_balance_id('FR_TOTAL_COST_TO_EMPLOYER'
338 ,'Assignment Establishment Year To Date');
339 g_12taxable_income_db := pay_fr_arc_pkg.get_balance_id('FR_TAXABLE_INCOME'
340 ,'Assignment Establishment Period To Date');
341 --
342 -- Get the translated names of input values from NAME Translations lookup
343 --
344 hr_utility.set_location('Loading translated names ' || l_proc, 50);
345 g_fr_name_pay_value := nvl(substr(hr_general.decode_lookup('NAME_TRANSLATIONS','PAY VALUE'),1,30), g_us_name_pay_value);
346 g_retro_tl := nvl(substr(hr_general.decode_lookup('NAME_TRANSLATIONS','RETRO'),1,30), g_retro_tl);
347 g_fr_name_rate := nvl(substr(hr_general.decode_lookup('NAME_TRANSLATIONS','RATE'),1,30), g_fr_name_rate);
348 g_fr_name_base := nvl(substr(hr_general.decode_lookup('NAME_TRANSLATIONS','BASE'),1,30), g_fr_name_base);
349 g_fr_name_start_date := nvl(hr_general.decode_lookup('NAME_TRANSLATIONS','START_DATE'), g_fr_name_start_date);
350 g_fr_name_end_date := nvl(hr_general.decode_lookup('NAME_TRANSLATIONS','END_DATE'), g_fr_name_end_date);
351 --
352 -- Get the context id of SOURCE_TEXT in ff_contexts
353 --
354 open csr_context('SOURCE_TEXT');
355 fetch csr_context INTO g_source_text;
356 close csr_context;
357 --
358 -- Get the termination element type ids
359 --
360 open csr_termination_elements('FR_TERMINATION_SUBJECT_TO_SS');
361 fetch csr_termination_elements into g_term_ele_subject_to_ss;
362 close csr_termination_elements;
363 open csr_termination_elements('FR_TERMINATION_EXEMPT_OF_SS');
364 fetch csr_termination_elements into g_term_ele_exempt_of_ss;
365 close csr_termination_elements;
366 --
367 -- Load the parameters to the process
368 --
369 if g_payroll_action_id is null
370 or g_payroll_action_id <> p_payroll_action_id
371 then
372 hr_utility.set_location('Loading parameters ' || l_proc, 60);
373 pay_fr_arc_pkg.get_all_parameters
374 (p_payroll_action_id => p_payroll_action_id
375 ,p_payroll_id => g_param_payroll_id
376 ,p_assignment_id => g_param_assignment_id
377 ,p_assignment_set_id => g_param_assignment_set_id
378 ,p_business_Group_id => g_param_business_group_id
379 ,p_start_date => g_param_start_date
380 ,p_effective_date => g_param_effective_date);
381 g_payroll_action_id := p_payroll_action_id;
382 end if;
383 --
384 hr_utility.set_location('Loading dimension ' || l_proc, 70);
385 open csr_get_dimension('Assignment Run To Date');
386 fetch csr_get_dimension into g_asg_run;
387 close csr_get_dimension;
388 --
389 open csr_get_dimension('Assignment Proration Run To Date');
390 fetch csr_get_dimension into g_asg_pro_run;
391 close csr_get_dimension;
392 --
393 open csr_get_dimension('ASG_ET_PR_RA_CU_RUN contexts Establishment, Process Type, Rate, CU_ID');
394 fetch csr_get_dimension into g_asg_et_pr_ra_cu_run;
395 close csr_get_dimension;
396 --
397 open csr_get_dimension('ASG_ET_PR_RA_CU_PRO_RUN contexts Establishment, Process Type, Rate, CU_ID');
398 fetch csr_get_dimension into g_asg_et_pr_ra_cu_pro_run;
399 close csr_get_dimension;
400 --
401 open csr_get_dimension('ASG_ET_PR_CU_RUN contexts Establishment, Process Type, Contribution Usage');
402 fetch csr_get_dimension into g_asg_et_pr_cu_run;
403 close csr_get_dimension;
404 --
405 open csr_get_dimension('ASG_ET_PR_CU_PRO_RUN contexts Establishment, Process Type, Contribution Usage');
406 fetch csr_get_dimension into g_asg_et_pr_cu_pro_run;
407 close csr_get_dimension;
408 --
409 -- get the PER application ID
410 --
411 open csr_get_per_id;
412 fetch csr_get_per_id into g_per_id;
413 close csr_get_per_id;
414 --
415 -- Get the balance category ids.
416 --
417 open csr_get_bal_cat('CSG Non-Deductible');
418 fetch csr_get_bal_cat into g_CSG_non_Deductible;
419 close csr_get_bal_cat;
420 --
421 open csr_get_bal_cat('Conventional EE Deductions');
422 fetch csr_get_bal_cat into g_Conv_EE_Deductions;
423 close csr_get_bal_cat;
424 --
425 open csr_get_bal_cat('Conventional ER Charges');
426 fetch csr_get_bal_cat into g_Conv_ER_Charges;
427 close csr_get_bal_cat;
428 --
429 open csr_get_bal_cat('Income Tax Excess');
430 fetch csr_get_bal_cat into g_Income_Tax_Excess;
431 close csr_get_bal_cat;
432 --
433 open csr_get_bal_cat('Rebates');
434 fetch csr_get_bal_cat into g_Rebates;
435 close csr_get_bal_cat;
436 --
437 open csr_get_bal_cat('Statutory EE Deductions');
438 fetch csr_get_bal_cat into g_Stat_EE_Deductions;
439 close csr_get_bal_cat;
440 --
441 open csr_get_bal_cat('Statutory ER Charges');
442 fetch csr_get_bal_cat into g_Stat_ER_Charges;
443 close csr_get_bal_cat;
444 --
445 --
446 -- Get the element classification ids.
447 --
448 open csr_get_ele_class('CSG Non-Deductible');
449 fetch csr_get_ele_class into g_ele_class_CSG_non_Deductible;
450 close csr_get_ele_class;
451 --
452 open csr_get_ele_class('Conventional EE Deductions');
453 fetch csr_get_ele_class into g_ele_class_Conv_EE_Deductions;
454 close csr_get_ele_class;
455 --
456 open csr_get_ele_class('Conventional ER Charges');
457 fetch csr_get_ele_class into g_ele_class_Conv_ER_Charges;
458 close csr_get_ele_class;
459 --
460 open csr_get_ele_class('Income Tax Excess');
461 fetch csr_get_ele_class into g_ele_class_Income_Tax_Excess;
462 close csr_get_ele_class;
463 --
464 open csr_get_ele_class('Rebates');
465 fetch csr_get_ele_class into g_ele_class_Rebates;
466 close csr_get_ele_class;
467 --
468 open csr_get_ele_class('Statutory EE Deductions');
469 fetch csr_get_ele_class into g_ele_class_Stat_EE_Deductions;
470 close csr_get_ele_class;
471 --
472 open csr_get_ele_class('Statutory ER Charges');
473 fetch csr_get_ele_class into g_ele_class_Stat_ER_Charges;
474 close csr_get_ele_class;
475 --
476 open csr_get_ele_class('Net EE Deductions');
477 fetch csr_get_ele_class into g_ele_class_Net_EE_Deductions;
478 close csr_get_ele_class;
479 --
480 open csr_get_ele_class('ER LV Charges');
481 fetch csr_get_ele_class into g_ele_class_ER_LV_Charges;
482 close csr_get_ele_class;
483 --
484 -- Retrieving security code (bug 3683906) - moved here from archive_code_sub
485 -- and altered as part of bug 4778143
486 g_sec_grp_id_user_element_grp :=
487 fnd_global.lookup_security_group('FR_USER_ELEMENT_GROUP',3);
488 g_sec_grp_id_base_unit := fnd_global.lookup_security_group('FR_BASE_UNIT',3);
489 g_sec_grp_id_element_grp :=
490 fnd_global.lookup_security_group('FR_ELEMENT_GROUP',3);
491 g_sec_grp_id_process_type :=
492 fnd_global.lookup_security_group('FR_PROCESS_TYPE',3);
493 g_sec_grp_id_fixed_time_units :=
494 fnd_global.lookup_security_group('FR_FIXED_TIME_UNITS',3);
495 g_sec_grp_id_fixed_time_freq :=
496 fnd_global.lookup_security_group('FR_FIXED_TIME_FREQUENCY',3);
497 --
498 hr_utility.set_location('Leaving ' || l_proc, 100);
499 --
500 END archinit;
501 ---------------------------------------------------------------------------------------------------
502 -- ARCHIVE CODE
503 -- DESCRIPTION : Main routine that determins if child actions should be created. If so creates
504 -- child actions and loads archive data against those actions. Otherwise creates
505 -- archive data against the parameter assignment action.
506 ---------------------------------------------------------------------------------------------------
507 procedure archive_code(
508 p_assactid in number
509 ,p_effective_date in date) is
510 --
511 l_proc VARCHAR2(40):= g_package||' Archive code ';
512 --
513 cursor csr_prepay_children is
514 SELECT child_pre.assignment_action_id assignment_action_id
515 ,child_pre.assignment_id assignment_id
516 ,child_pre.tax_unit_id establishment_id
517 ,master_arc.chunk_number chunk_number
518 ,master_arc.payroll_action_id payroll_action_id
519 ,pay_assignment_actions_s.nextval new_ass_act_id
520 FROM pay_assignment_Actions master_arc
521 ,pay_action_interlocks lok
522 ,pay_assignment_Actions child_pre
523 WHERE master_arc.assignment_action_id = p_assactid
524 and lok.locking_Action_id = master_arc.assignment_action_id
525 and lok.locked_action_id = child_pre.source_action_id;
526 --
527 l_child boolean;
528 --
529 BEGIN
530 hr_utility.set_location('Entering ' || l_proc,10);
531 --
532 -- Determine if need to create child actions and store in a loop, or
533 -- just archive the data under the action created in Action Creation
534 --
535 l_child := false;
536 FOR child IN csr_prepay_children LOOP
537 --
538 l_child := true;
539 hr_nonrun_asact.insact(lockingactid => child.new_ass_act_id
540 ,assignid => child.assignment_id
541 ,pactid => child.payroll_action_id
542 ,chunk => child.chunk_number
543 ,greid => child.establishment_id
544 ,source_act => p_assactid);
545 --
546 -- insert the lock archive child action->prepay child action
547 --
548 hr_nonrun_asact.insint(child.new_ass_act_id,child.assignment_action_id);
549 --
550 -- process the child action
551 --
552
553 archive_code_sub(p_assactid => child.new_ass_act_id
554 ,p_effective_date => p_effective_date);
555 --
556 -- this child action is now complete
557 --
558 update pay_assignment_actions
559 set action_status = 'C'
560 where assignment_action_id = child.new_ass_act_id;
561 END LOOP;
562 --
563 -- Only process the parent action if it has no child actions
564 --
565 IF not l_child THEN
566 --
567 -- process the main action
568 --
569 archive_code_sub(p_assactid => p_assactid
570 ,p_effective_date => p_effective_date);
571 END IF;
572 --
573 --
574 END Archive_Code;
575
576 ---------------------------------------------------------------------------------------------------
577 -- ARCHIVE CODE SUB
578 -- DESCRIPTION : routine that calls all other procedures to archive for an assignment aciton.
579 --
580 ---------------------------------------------------------------------------------------------------
581 procedure archive_code_sub(
582 p_assactid in number
583 ,p_effective_date in date) is
584 --
585 l_establishment_id pay_assignment_actions.tax_unit_id%TYPE;
586 l_person_id per_all_people_f.person_id%TYPE;
587 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
588 l_payroll_id pay_all_payrolls_f.payroll_id%TYPE;
589 l_latest_assignment_action_id pay_assignment_actions.assignment_id%TYPE;
590 l_latest_date_paid date;
591 l_ee_asat_date date;
592 l_total_gross_pay number;
593 l_net_payments number;
594 l_court_orders number;
595 l_action_info_id number (15);
596 l_ovn number (15);
597 l_archive_type varchar2(3) := 'AAP';
598 l_latest_process_type varchar2(30);
599 l_ee_info_id pay_action_information.action_information_id%TYPE;
600 l_term_reason fnd_lookup_values.meaning%TYPE;
601 l_term_pay_schedule fnd_lookup_values.lookup_code%TYPE;
602 l_term_atd date;
603 l_term_lwd date;
604 --
605 -- variables to hold totals values, passed back from various functions
606 --
607 l_totals_c1_total_gross number(15,2) := 0.00;
608 l_totals_c2_total_subject number(15,2) := 0.00;
609 l_totals_c3_total_deduct_ee number(15,2) := 0.00;
610 l_totals_c3_total_deduct_er number(15,2) := 0.00;
611 l_totals_c4_taxable_income number(15,2) := 0.00;
612 l_totals_c5_total_charges_ee number(15,2) := 0.00;
613 l_totals_c5_total_charges_er number(15,2) := 0.00;
614 l_totals_c6_net_salary number(15,2) := 0.00;
615 l_totals_c7_total_pay number(15,2) := 0.00;
616 l_totals_c8_previous_advice number(15,2) := 0.00;
617 l_totals_c9_this_advice number(15,2) := 0.00;
618 l_totals_c10_net_advice number(15,2) := 0.00;
619 --
620 l_total_ee_net_deductions number(15,2) := 0.00;
621 l_reductions number(15,2) := 0.00;
622 --
623 l_processing_aborted exception;
624 --
625 l_proc VARCHAR2(40):= g_package||' Archive code Sub';
626 --
627 BEGIN
628 hr_utility.set_location('Entering ' || l_proc,10);
629 --
630 -------------------------------------------------------------------
631 -- Get instance variables for this particular assignment action
632 -------------------------------------------------------------------
633 pay_fr_arc_pkg.get_instance_variables (
634 p_assignment_action_id => p_assactid
635 ,p_person_id => l_person_id /* out */
636 ,p_establishment_id => l_establishment_id /* out */
637 ,p_assignment_id => l_assignment_id /* out */
638 ,p_payroll_id => l_payroll_id); /* out */
639 --
640 -------------------------------------------------------------------
641 -- Get latest date earned (no archiving) and process type
642 -------------------------------------------------------------------
643 hr_utility.set_location('Step ' || l_proc,20);
644 pay_fr_arc_pkg.get_latest_run_data (
645 p_archive_action_id => p_assactid
646 ,p_assignment_id => l_assignment_id
647 ,p_establishment_id => l_establishment_id
648 ,p_date_earned => l_latest_date_paid /* out */
649 ,p_latest_process_type => l_latest_process_type /* out */
650 ,p_latest_assignment_action_id => l_latest_assignment_action_id ); /* out */
651 --
652 if l_latest_date_paid is null then
653 -- no non-reversed run action found by get_latest_run_data so don't archive
654 raise l_processing_aborted;
655 end if;
656 -------------------------------------------------------------------
657 -- Get the employee dates start / end estab, pay_period dates,
658 -- term date, sen date and archive
659 -------------------------------------------------------------------
660 hr_utility.set_location('Step ' || l_proc,30);
661 pay_fr_arc_pkg.load_employee_dates (
662 p_assignment_id => l_assignment_id
663 ,p_effective_date => g_param_effective_date
664 ,p_assignment_action_id => p_assactid
665 ,p_latest_date_earned => l_latest_date_paid
666 ,p_asat_date => l_ee_asat_date /* out */
667 ,p_payroll_id => l_payroll_id
668 ,p_establishment_id => l_establishment_id
669 ,p_term_reason => l_term_reason
670 ,p_term_atd => l_term_atd
671 ,p_term_lwd => l_term_lwd
672 ,p_term_pay_schedule => l_term_pay_schedule);
673 --
674 hr_utility.trace('asg ' || to_char(l_assignment_id));
675 hr_utility.trace('est ' || to_char(l_establishment_id));
676 hr_utility.trace('asat ' || to_char(l_ee_asat_date));
677 -------------------------------------------------------------------
678 -- Get the employee data and archive AS AT p_asat date
679 -------------------------------------------------------------------
680 hr_utility.set_location('Step ' || l_proc,40);
681 hr_utility.set_location('person_id is ' || to_char(l_person_id),12);
682 pay_fr_arc_pkg.load_employee (
683 p_assignment_id => l_assignment_id
684 ,p_person_id => l_person_id
685 ,p_asat_date => l_ee_asat_date
686 ,p_assignment_action_id => p_assactid
687 ,p_latest_date_earned => l_latest_date_paid
688 ,p_establishment_id => l_establishment_id
689 ,p_ee_info_id => l_ee_info_id);
690 --
691 -------------------------------------------------------------------
692 -- Get the balance values and archive
693 -------------------------------------------------------------------
694 hr_utility.set_location('Step ' || l_proc,50);
695 pay_fr_arc_pkg.load_balances(
696 p_assignment_action_id => l_latest_assignment_action_id /* TO GET BALANCE VALUES */
697 ,p_archive_action_id => p_assactid
698 ,p_context_id => l_establishment_id
699 ,p_totals_taxable_income => l_totals_c4_taxable_income);
700 --
701 -------------------------------------------------------------------
702 -- Get the Holidays values and archive
703 -------------------------------------------------------------------
704 hr_utility.set_location('Step ' || l_proc,60);
705 pay_fr_arc_pkg.load_holidays (
706 p_assignment_id => l_assignment_id
707 ,p_person_id => l_person_id
708 ,p_effective_date => g_param_effective_date
709 ,p_assignment_action_id => p_assactid
710 ,p_establishment_id => l_establishment_id
711 ,p_business_group_id => g_param_business_group_id);
712 --
713 -------------------------------------------------------------------
714 -- Get the BANK values and archive
715 -------------------------------------------------------------------
716 hr_utility.set_location('Step ' || l_proc,70);
717 pay_fr_arc_pkg.load_bank (
718 p_assignment_action_id => p_assactid
719 ,p_assignment_id => l_assignment_id
720 ,p_totals_previous_advice => l_totals_c8_previous_advice
721 ,p_totals_this_advice => l_totals_c9_this_advice
722 ,p_totals_net_advice => l_totals_c10_net_advice
723 ,p_establishment_id => l_establishment_id
724 ,p_asat_date => l_ee_asat_date);
725 --
726 -------------------------------------------------------------------
727 -- Get the MESSAGES values and archive
728 -------------------------------------------------------------------
729 hr_utility.set_location('Step ' || l_proc,80);
730 pay_fr_arc_pkg.load_messages (
731 p_archive_assignment_action_id => p_assactid
732 ,p_establishment_id => l_establishment_id
733 ,p_term_atd => l_term_atd
734 ,p_term_reason => l_term_reason);
735 --
736 -------------------------------------------------------------------
737 -- Get the Rate GROUPED Earnings and Net payments run values and archive
738 -- pass back total gross pay, net pay and court orders
739 -------------------------------------------------------------------
740 hr_utility.set_location('Step ' || l_proc,90);
741 pay_fr_arc_pkg.load_ee_rate_grouped_runs(
742 p_archive_assignment_action_id => p_assactid
743 ,p_assignment_id => l_assignment_id
744 ,p_latest_process_type => l_latest_process_type
745 ,p_total_gross_pay => l_totals_c1_total_gross /* out */
746 ,p_reductions => l_reductions /* out */
747 ,p_net_payments => l_net_payments /* out */
748 ,p_court_orders => l_court_orders /* out */
749 ,p_establishment_id => l_establishment_id
750 ,p_effective_date => p_effective_date
751 ,p_termination_reason => l_term_reason
752 ,p_term_st_ele_id => g_term_ele_subject_to_ss
753 ,p_term_ex_ele_id => g_term_ele_exempt_of_ss
754
755 );
756 --
757 -------------------------------------------------------------------
758 -- Get the DEDUCTIONS values and archive
759 -------------------------------------------------------------------
760 hr_utility.set_location('Step ' || l_proc,100);
761 pay_fr_arc_pkg.load_deductions(
762 p_archive_assignment_action_id => p_assactid
763 ,p_assignment_id => l_assignment_id
764 ,p_latest_process_type => l_latest_process_type
765 ,p_total_deduct_ee => l_totals_c3_total_deduct_ee /* out */
766 ,p_total_deduct_er => l_totals_c3_total_deduct_er /* out */
767 ,p_total_charge_ee => l_totals_c5_total_charges_ee /* out */
768 ,p_total_charge_er => l_totals_c5_total_charges_er /* out */
769 ,p_establishment_id => l_establishment_id
770 ,p_effective_date => p_effective_date);
771 --
772 -------------------------------------------------------------------
773 -- Get the RATE GROUPED run values and archive (net ee and ER LV)
774 -------------------------------------------------------------------
775 hr_utility.set_location('Step ' || l_proc,110);
776 pay_fr_arc_pkg.load_rate_grouped_runs(
777 p_archive_assignment_action_id => p_assactid
778 ,p_assignment_id => l_assignment_id
779 ,p_latest_process_type => l_latest_process_type
780 ,p_total_ee_net_deductions => l_total_ee_net_deductions
781 ,p_establishment_id => l_establishment_id
782 ,p_total_gross_pay => l_totals_c1_total_gross /* in out */
783 ,p_effective_date => p_effective_date);
784 --
785 -------------------------------------------------------------------
786 -- Calculate all running totals and archive
787 -------------------------------------------------------------------
788 l_totals_c2_total_subject := nvl(l_totals_c1_total_gross,0) - l_reductions;
789 l_totals_c6_net_salary := nvl(l_totals_c1_total_gross, 0) - nvl(l_totals_c5_total_charges_ee,0);
790 l_totals_c7_total_pay := nvl(l_totals_c6_net_salary, 0)
791 + nvl(l_net_payments, 0)
792 - nvl(l_total_ee_net_deductions,0)
793 - nvl(l_court_orders, 0);
794 --
795 pay_action_information_api.create_action_information (
796 p_action_information_id => l_action_info_id
797 , p_action_context_id => p_assactid
798 , p_action_context_type => l_archive_type
799 , p_object_version_number => l_ovn
800 , p_action_information_category => 'FR_SOE_EE_TOTALS'
801 , p_tax_unit_id => l_establishment_id
802 , p_action_information4 => to_char(l_totals_c1_total_gross)
803 , p_action_information5 => to_char(l_totals_c2_total_subject)
804 , p_action_information6 => to_char(l_totals_c3_total_deduct_ee)
805 , p_action_information7 => to_char(l_totals_c3_total_deduct_er)
806 , p_action_information8 => to_char(l_totals_c4_taxable_income)
807 , p_action_information9 => to_char(l_totals_c5_total_charges_ee)
808 , p_action_information10 => to_char(l_totals_c5_total_charges_er)
809 , p_action_information11 => to_char(l_totals_c6_net_salary)
810 , p_action_information12 => to_char(l_totals_c7_total_pay)
811 , p_action_information13 => to_char(l_totals_c8_previous_advice)
812 , p_action_information14 => to_char(l_totals_c9_this_advice)
813 , p_action_information15 => to_char(l_totals_c10_net_advice));
814 --
815 -- Test to ensure TOTAL_PAY is always equal to the sum of all payments to bank accounts
816 -- (ie notified by this payslip)
817 --
818 if greatest(to_number(nvl(l_totals_c7_total_pay,0)),0) <>
819 greatest(to_number(nvl(l_totals_c9_this_advice,0)),0) then
820 hr_utility.trace(' l_totals_c7_total_pay ' || l_totals_c7_total_pay);
821 hr_utility.trace(' l_totals_c9_this_advice ' || l_totals_c9_this_advice);
822 hr_utility.set_message(801, 'PAY_74982_INCONSISTENT_PAY');
823 -- hr_utility.raise_error;
824 end if;
825 --
826 -- test if this payslip should be suppressed
827 --
828 if l_term_atd is not null
829 AND l_term_pay_schedule = 'LAST_DAY_WORKED'
830 AND nvl(l_term_atd, sysdate) <> nvl(l_term_lwd, sysdate)
831 AND g_param_effective_date > nvl(l_term_atd, g_param_effective_date)
832 AND g_param_effective_date < nvl(l_term_lwd, g_param_effective_date)
833 AND nvl(l_totals_c7_total_pay,0) = 0 THEN
834 pay_action_information_api.update_action_information (
835 p_action_information_id => l_ee_info_id
836 ,p_object_version_number => l_ovn
837 ,p_action_information16 => 'Y');
838 end if;
839 EXCEPTION
840 when l_processing_aborted then null;
841 END archive_code_sub;
842 -------------------------------------------------------------------------------
843 -- LOAD_PAYSLIP_TEXT loads bolierplate
844 -- DESCRIPTION : Archives all text (from lookup) against the payroll action,
845 -- ie not per asg.
846 -------------------------------------------------------------------------------
847 procedure load_payslip_text (p_action_id in number ) is
848 --
849 l_action_info_id number(15);
850 l_archive_type varchar2(3) := 'PA';
851 l_ovn number (15);
852 l_proc VARCHAR2(40):= g_package||' load payslip text ';
853 l_text1 fnd_lookup_values.meaning%TYPE;
854 l_text2 fnd_lookup_values.meaning%TYPE;
855 l_text3 fnd_lookup_values.meaning%TYPE;
856 l_text4 fnd_lookup_values.meaning%TYPE;
857 l_text5 fnd_lookup_values.meaning%TYPE;
858 l_text6 fnd_lookup_values.meaning%TYPE;
859 l_text7 fnd_lookup_values.meaning%TYPE;
860 l_text8 fnd_lookup_values.meaning%TYPE;
861 l_text9 fnd_lookup_values.meaning%TYPE;
862 l_text10 fnd_lookup_values.meaning%TYPE;
863 l_text11 fnd_lookup_values.meaning%TYPE;
864 l_text12 fnd_lookup_values.meaning%TYPE;
865 BEGIN
866 --
867 -- Load the TOTALS bolierplate text
868 --
869 hr_utility.set_location('Entering ' || l_proc || 'loading totals names', 10);
870 --
871 l_text1 := hr_general.decode_lookup('FR_PAYSLIP_TEXT',g_totals_c1_total_gross);
872 l_text2 := hr_general.decode_lookup('FR_PAYSLIP_TEXT',g_totals_c2_total_subject);
873 l_text3 := hr_general.decode_lookup('FR_PAYSLIP_TEXT',g_totals_c3_total_deductions);
874 l_text4 := hr_general.decode_lookup('FR_PAYSLIP_TEXT',g_totals_c4_taxable_income);
875 l_text5 := hr_general.decode_lookup('FR_PAYSLIP_TEXT',g_totals_c5_total_charges);
876 l_text6 := hr_general.decode_lookup('FR_PAYSLIP_TEXT',g_totals_c6_net_salary);
877 l_text7 := hr_general.decode_lookup('FR_PAYSLIP_TEXT',g_totals_c7_total_pay);
878 l_text8 := hr_general.decode_lookup('FR_PAYSLIP_TEXT',g_totals_c8_previous_advice);
879 l_text9 := hr_general.decode_lookup('FR_PAYSLIP_TEXT',g_totals_c9_this_advice);
880 l_text10 := hr_general.decode_lookup('FR_PAYSLIP_TEXT',g_totals_c10_net_advice);
881 --
882 -- Archive the bolilerplate to the running totals
883 --
884 pay_action_information_api.create_action_information(
885 p_action_information_id => l_action_info_id
886 , p_action_context_id => p_action_id
887 , p_action_context_type => l_archive_type
888 , p_object_version_number => l_ovn
889 , p_action_information_category => 'FR_SOE_TOTALS_TEXT'
890 , p_action_information4 => l_text1
891 , p_action_information5 => l_text2
892 , p_action_information6 => l_text3
893 -- not using 7 to ease mapping to ee_totals values
894 , p_action_information8 => l_text4
895 , p_action_information9 => l_text5
896 -- not using 10 to ease mapping to ee_totals values
897 , p_action_information11 => l_text6
898 , p_action_information12 => l_text7
899 , p_action_information13 => l_text8
900 , p_action_information14 => l_text9
901 , p_action_information15 => l_text10);
902 --
903 -- Load balance names
904 --
905 hr_utility.set_location('Entering ' || l_proc || 'loading balance names', 20);
906 --
907 l_text1 := hr_general.decode_lookup('FR_PAYSLIP_TOTALS',g_balance_c1_total_gross);
908 l_text2 := hr_general.decode_lookup('FR_PAYSLIP_TOTALS',g_balance_c2_ss_ceiling);
909 l_text3 := hr_general.decode_lookup('FR_PAYSLIP_TOTALS',g_balance_c3_ee_total_conts);
910 l_text4 := hr_general.decode_lookup('FR_PAYSLIP_TOTALS',g_balance_c4_stat_er_charges);
911 l_text5 := hr_general.decode_lookup('FR_PAYSLIP_TOTALS',g_balance_c5_conv_er_charges);
912 l_text6 := hr_general.decode_lookup('FR_PAYSLIP_TOTALS',g_balance_c6_t1_arrco);
913 l_text7 := hr_general.decode_lookup('FR_PAYSLIP_TOTALS',g_balance_c7_t2_arrco);
914 l_text8 := hr_general.decode_lookup('FR_PAYSLIP_TOTALS',g_balance_c8_tb_agirc);
915 l_text9 := hr_general.decode_lookup('FR_PAYSLIP_TOTALS',g_balance_c9_tc_agirc);
916 l_text10 := hr_general.decode_lookup('FR_PAYSLIP_TOTALS',g_balance_c10_gmp);
917 l_text11 := hr_general.decode_lookup('FR_PAYSLIP_TOTALS',g_balance_c11_total_er_cost);
918 -- l_text12 := hr_general.decode_lookup('FR_PAYSLIP_TOTALS',g_balance_c12_taxable_income);
919 --
920 -- Archive the bolilerplate to the Balances
921 --
922 hr_utility.set_location('Entering ' || l_proc || 'archiving balance names', 30);
923 --
924 pay_action_information_api.create_action_information (
925 p_action_information_id => l_action_info_id
926 , p_action_context_id => p_action_id
927 , p_action_context_type => l_archive_type
928 , p_object_version_number => l_ovn
929 , p_action_information_category => 'FR_SOE_BALANCE_TEXT'
930 , p_action_information4 => l_text1
931 , p_action_information5 => l_text2
932 , p_action_information6 => l_text3
933 , p_action_information7 => l_text4
934 , p_action_information8 => l_text5
935 , p_action_information9 => l_text6
936 , p_action_information10 => l_text7
937 , p_action_information11 => l_text8
938 , p_action_information12 => l_text9
939 , p_action_information13 => l_text10
940 , p_action_information14 => l_text11);
941 --
942 END load_payslip_text;
943 -------------------------------------------------------------------------------
944 -- LOAD_ORGANIZATION_DETAILS
945 -- DESCRIPTION : archives company and est data and addresses
946 -- against the payroll action, ie not asg
947 -------------------------------------------------------------------------------
948 procedure load_organization_details(
949 p_payroll_action_id in number
950 ,p_business_group_id in number
951 ,p_payroll_id in number
952 ,p_assignment_id in number
953 ,p_assignment_set_id in number
954 ,p_effective_date in date
955 ,p_start_date in date) is
956 --
957 -- Archiver local variables
958 l_action_info_id number (15);
959 l_ovn number (15);
960 l_archive_type varchar2(3) := 'PA';
961 l_error_flag varchar2(3) := 'N';
962 --
963 -- csr_urssaf - was part of csr_company, but split out for
964 -- performance repository
965 --
966 cursor csr_urssaf (p_urssaf_id number) is
967 select urssaf_info.org_information1 code,
968 substr(urssaf.name,1,150) urssaf_name
969 from hr_all_organization_units_tl urssaf,
970 hr_organization_information urssaf_info
971 where urssaf.language = userenv('lang')
972 and urssaf.organization_id = urssaf_info.organization_id
973 and urssaf_info.org_information_context = 'FR_URSSAF_CENTR_INFO'
974 and urssaf_info.organization_id = p_urssaf_id;
975 --
976 -- csr_company
977 -- Get all companies referenced by establishments in the actions
978 --
979 cursor csr_company (p_payroll_action_id number ) is
980 select /*+ORDERED USE_NL(est_info comp_info) */
981 distinct comp.organization_id,
982 substr(comptl.name,1,150) name,
983 comp_info.org_information1 siren,
984 comp.location_id,
985 comp_info.org_information3 urssaf_org_id,
986 comp_info.org_information2 naf_code,
987 substr(addr.address_line_1,1,150) address_line_1
988 from (select distinct tax_unit_id id
989 from pay_assignment_actions paa1
990 where paa1.payroll_action_id = p_payroll_action_id) tax_unit,
991 hr_organization_information est_info,
992 hr_all_organization_units comp,
993 hr_all_organization_units_tl comptl,
994 hr_organization_information comp_info,
995 hr_locations_all addr
996 where addr.location_id(+) = comp.location_id
997 and comptl.organization_id = comp.organization_id
998 and comptl.language = userenv('lang')
999 and comp_info.organization_id(+) = comp.organization_id
1000 and comp_info.org_information_context(+) = 'FR_COMP_INFO'
1001 and est_info.org_information_context = 'FR_ESTAB_INFO'
1002 and est_info.org_information1 = comp.organization_id
1003 and est_info.organization_id = tax_unit.id;
1004 --
1005 -- csr_establishment
1006 --
1007 -- This gets all the establishment data, for any establishments referenced by the
1008 -- payroll action.
1009 --
1010 cursor csr_establishment (p_business_group_id number
1011 ,p_payroll_action_id number) is
1012 select distinct
1013 est.organization_id organization_id,
1014 substr(esttl.name,1,150) name,
1015 est_info.org_information2 siret_info,
1016 est_info.org_information1 company_org_id,
1017 est.location_id
1018 from hr_all_organization_units est
1019 , hr_organization_information est_info
1020 , hr_all_organization_units_tl esttl
1021 , pay_assignment_Actions paa
1022 where paa.payroll_action_id = p_payroll_action_id
1023 and est.organization_id = paa.tax_unit_id
1024 and est_info.organization_id(+) = est.organization_id
1025 and est_info.org_information_context(+) = 'FR_ESTAB_INFO'
1026 and esttl.organization_id = est.organization_id
1027 and esttl.language = userenv('lang')
1028 and est.business_group_id = p_business_group_id;
1029 --
1030 -- csr_addresses
1031 -- Get all the distinct address ids used by the companies and establishments just archived
1032 -- and archive these values.
1033 --
1034 cursor csr_addresses (p_pay_act_id number) is
1035 select /*+ordered*/
1036 distinct addr.location_id,
1037 substr(addr.address_line_1,1,150) address_line_1,
1038 substr(addr.address_line_2,1,150) address_line_2,
1039 substr(addr.address_line_3,1,150) address_line_3,
1040 substr(addr.region_2,1,150) region_2,
1041 substr(addr.region_3,1,150) region_3,
1042 addr.town_or_city,
1043 addr.postal_code
1044 from pay_action_information pai,
1045 hr_locations_all addr
1046 where addr.location_id = pai.ACTION_INFORMATION2
1047 and pai.action_context_type = 'PA'
1048 and pai.action_information_category in ('FR_SOE_COMPANY_DETAILS',
1049 'FR_SOE_ESTAB_INFORMATION')
1050 and pai.action_context_id = p_pay_act_id;
1051 --
1052 rec_company csr_company%ROWTYPE;
1053 rec_establishment csr_establishment%ROWTYPE;
1054 rec_addresses csr_addresses%ROWTYPE;
1055 rec_urssaf csr_urssaf%ROWTYPE;
1056 l_proc VARCHAR2(60):= g_package||' Load_Organization_Details ';
1057 --
1058 BEGIN
1059 hr_utility.set_location('Entering ' || l_proc, 10);
1060 --
1061 BEGIN
1062 --
1063 -- delete any previous rows previously created - only relevent
1064 -- in the case of a retry.
1065 --
1066 Delete from pay_action_information
1067 Where action_context_id = p_payroll_Action_id
1068 And action_context_type = 'PA'
1069 And action_information_category in ('FR_SOE_COMPANY_DETAILS'
1070 ,'FR_SOE_ESTAB_INFORMATION'
1071 ,'FR_SOE_ER_ADDRESSES');
1072 EXCEPTION
1073 /* this is only used in retry and is not critical */
1074 WHEN OTHERS THEN NULL;
1075 END;
1076 --
1077 BEGIN
1078 --
1079 -- COMPANY
1080 --
1081 open csr_company(p_payroll_action_id);
1082 LOOP
1083 fetch csr_company INTO rec_company;
1084 EXIT WHEN csr_company%NOTFOUND;
1085 --
1086 -- check that the company has an address, and address line 1 is not null
1087 -- else raise an error
1088 --
1089 if rec_company.address_line_1 is null then
1090 hr_utility.set_location('Archiving Error ',30);
1091 l_error_flag := 'Y';
1092 else
1093 l_error_flag := 'N';
1094 end if;
1095 --
1096 -- Get the urssaf details (separate cursor for performance repository)
1097 --
1098 if rec_company.urssaf_org_id is not null then
1099 BEGIN
1100 open csr_urssaf(rec_company.urssaf_org_id);
1101 fetch csr_urssaf into rec_urssaf;
1102 close csr_urssaf;
1103 EXCEPTION
1104 when others then null;
1105 END;
1106 end if;
1107 hr_utility.trace('Fetched urssaf ' || rec_urssaf.urssaf_name);
1108 --
1109 -- Archive the Company details, using source_id to hold context of company id
1110 --
1111 pay_action_information_api.create_action_information (
1112 p_action_information_id => l_action_info_id
1113 , p_action_context_id => p_payroll_action_id
1114 , p_action_context_type => l_archive_type
1115 , p_object_version_number => l_ovn
1116 , p_action_information_category => 'FR_SOE_COMPANY_DETAILS'
1117 , p_action_information1 => to_char(rec_company.organization_id)
1118 , p_action_information2 => to_char(rec_company.location_id)
1119 , p_action_information4 => rec_company.name
1120 , p_action_information5 => rec_company.siren
1121 , p_action_information6 => rec_urssaf.code
1122 , p_action_information7 => rec_urssaf.urssaf_name
1123 , p_action_information8 => rec_company.naf_code
1124 , p_action_information10 => l_error_flag);
1125 --
1126 END LOOP;
1127 close csr_company;
1128 END;
1129 --
1130 -- Repeat for the Establishment details
1131 --
1132 BEGIN
1133 --
1134 -- ESTABLISHMENT
1135 --
1136 hr_utility.set_location('Step ' || l_proc, 30);
1137 open csr_establishment(p_business_group_id, p_payroll_action_id);
1138 LOOP
1139 fetch csr_establishment INTO rec_establishment;
1140 hr_utility.set_location('Step ' || l_proc, 31);
1141 EXIT WHEN csr_establishment%NOTFOUND;
1142 --
1143 if rec_establishment.location_id is null then
1144 hr_utility.set_location('Archiving Error ',35);
1145 l_error_flag := 'Y';
1146 else
1147 l_error_flag := 'N';
1148 end if;
1149 --
1150 -- Archive the Establishment details, using source_id to hold context of establishment id
1151 --
1152 pay_action_information_api.create_action_information (
1153 p_action_information_id => l_action_info_id
1154 , p_action_context_id => p_payroll_action_id
1155 , p_action_context_type => l_archive_type
1156 , p_object_version_number => l_ovn
1157 , p_action_information_category => 'FR_SOE_ESTAB_INFORMATION'
1158 , p_action_information1 => to_char(rec_establishment.organization_id)
1159 , p_action_information2 => to_char(rec_establishment.location_id)
1160 , p_action_information3 => rec_establishment.company_org_id
1161 , p_action_information4 => rec_establishment.name
1162 , p_action_information5 => rec_establishment.siret_info
1163 , p_action_information10 => l_error_flag);
1164 --
1165 END LOOP;
1166 close csr_establishment;
1167 END;
1168 --
1169 -- Repeat for all location_ids (addresses) used by either the est or coy
1170 --
1171 -- ADDRESS items
1172 BEGIN
1173 hr_utility.set_location('Step ' || l_proc, 40);
1174 open csr_addresses(p_payroll_action_id);
1175 LOOP
1176 fetch csr_addresses INTO rec_addresses;
1177 EXIT WHEN csr_addresses%NOTFOUND;
1178 --
1179 -- Archive the Address details, using source_id to hold context of the address id
1180 --
1181 pay_action_information_api.create_action_information (
1182 p_action_information_id => l_action_info_id
1183 , p_action_context_id => p_payroll_action_id
1184 , p_action_context_type => l_archive_type
1185 , p_object_version_number => l_ovn
1186 , p_action_information_category => 'FR_SOE_ER_ADDRESSES'
1187 , p_action_information1 => to_char(rec_addresses.location_id)
1188 , p_action_information4 => rec_addresses.address_line_2
1189 , p_action_information5 => rec_addresses.address_line_1
1190 , p_action_information6 => rec_addresses.address_line_3
1191 , p_action_information7 => rec_addresses.region_2
1192 , p_action_information8 => rec_addresses.region_3
1193 , p_action_information9 => rec_addresses.town_or_city
1194 , p_action_information10 => rec_addresses.postal_code);
1195 --
1196 END LOOP;
1197 close csr_addresses;
1198 hr_utility.set_location('Leaving ' || l_proc, 100);
1199 END;
1200 end load_organization_details;
1201 -------------------------------------------------------------------------------
1202 -- GET_INSTANCE_VARIABLES
1203 --
1204 -------------------------------------------------------------------------------
1205 procedure get_instance_variables (
1206 p_assignment_action_id in number
1207 ,p_person_id out nocopy number
1208 ,p_establishment_id out nocopy number
1209 ,p_assignment_id out nocopy number
1210 ,p_payroll_id out nocopy number) is
1211 --
1212 l_tax_unit_id pay_assignment_actions.tax_unit_id%TYPE;
1213 l_person_id per_all_people_f.person_id%TYPE;
1214 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
1215 l_payroll_id pay_all_payrolls_f.payroll_id%TYPE;
1216 --
1217 l_proc VARCHAR2(40):= g_package||' get_instance_variables ';
1218 cursor csr_establishment(p_paa_id number) is
1219 select paa.tax_unit_id, paa.assignment_id, asg.person_id, asg.payroll_id
1220 from pay_assignment_actions paa
1221 ,per_assignments asg
1222 where paa.assignment_action_id = p_paa_id
1223 and paa.assignment_id = asg.assignment_id;
1224 BEGIN
1225 --
1226 hr_utility.set_location(' Entering ' || l_proc, 10);
1227 --
1228 open csr_establishment(p_assignment_action_id);
1229 fetch csr_establishment into l_tax_unit_id, l_assignment_id, l_person_id, l_payroll_id;
1230 if csr_establishment%NOTFOUND then
1231 close csr_establishment;
1232 hr_utility.set_location('DEV ERROR : BAD assignment action : ' || to_char(p_assignment_action_id), 20);
1233 else
1234 close csr_establishment;
1235 p_person_id := l_person_id;
1236 p_establishment_id := l_tax_unit_id;
1237 p_assignment_id := l_assignment_id;
1238 p_payroll_id := l_payroll_id;
1239 end if;
1240 hr_utility.set_location(' Leaving ' || l_proc, 100);
1241 end get_instance_variables;
1242 -------------------------------------------------------------------------------
1243 -- GET_LATEST_RUN_DATA
1244 -- DESCRIPTION : gets the latest process type and date earned in set of
1245 -- archived actions, and the latest run aa_id
1246 -------------------------------------------------------------------------------
1247 procedure get_latest_run_data(
1248 p_archive_action_id in number
1249 ,p_assignment_id in number
1250 ,p_establishment_id in number
1251 ,p_date_earned out nocopy date
1252 ,p_latest_process_type out nocopy varchar2
1253 ,p_latest_assignment_action_id out nocopy number) is
1254 --
1255 l_date_earned date;
1256 l_latest_action_sequence pay_assignment_actions.action_sequence%TYPE;
1257 l_latest_asg_action_id pay_assignment_actions.assignment_action_id%TYPE;
1258 l_latest_process_type varchar2(30);
1259 l_dummy number;
1260 --
1261 l_proc VARCHAR2(40):= g_package||' get_latest_run_data ';
1262 --
1263 cursor csr_date_earned(p_establishment_id number
1264 ,p_archive_action_id number) is
1265 SELECT run_payact.effective_date, run_assact.action_sequence,
1266 run_assact.assignment_action_id, proc_type.context_value
1267 FROM pay_action_interlocks arc_interlock
1268 , pay_action_interlocks pre_interlock
1269 , pay_assignment_actions run_assact
1270 , pay_payroll_actions run_payact
1271 , pay_action_contexts proc_type /*will exclude proration actions*/
1272 WHERE arc_interlock.locking_action_id = p_archive_action_id
1273 and pre_interlock.locking_action_id = arc_interlock.locked_action_id
1274 and pre_interlock.locked_action_id = run_assact.assignment_action_id
1275 and proc_type.assignment_id = run_assact.assignment_id
1276 and proc_type.assignment_action_id = run_assact.assignment_action_id
1277 and proc_type.context_id = g_source_text
1278 and run_payact.payroll_action_id = run_assact.payroll_action_id
1279 and run_payact.action_type in ('Q','R')
1280 and run_assact.run_type_id is not null
1281 order by run_payact.effective_date desc
1282 ,run_assact.action_sequence desc ;
1283 --
1284 CURSOR csr_locking_reversal (p_run_act_id number) is
1285 SELECT 1 /* if the run action is reversed exclude it */
1286 FROM pay_action_interlocks rev_interlock
1287 , pay_assignment_actions rev_assact
1288 , pay_payroll_actions rev_payact
1289 WHERE rev_interlock.locked_action_id = p_run_act_id
1290 AND rev_interlock.locking_action_id = rev_assact.assignment_action_id
1291 AND rev_assact.action_status = 'C'
1292 AND rev_payact.payroll_action_id = rev_assact.payroll_action_id
1293 AND rev_payact.action_type = 'V'
1294 AND rev_payact.action_status = 'C';
1295 --
1296 BEGIN
1297 --
1298 hr_utility.set_location('Entering ' || l_proc , 10);
1299 --
1300 open csr_date_earned(p_establishment_id, p_archive_action_id);
1301 loop
1302 fetch csr_date_earned into l_date_earned, l_latest_action_sequence,
1303 l_latest_asg_action_id, l_latest_process_type;
1304 exit when csr_date_earned%NOTFOUND;
1305 open csr_locking_reversal(l_latest_asg_action_id);
1306 fetch csr_locking_reversal into l_dummy;
1307 if csr_locking_reversal%FOUND then
1308 l_date_earned := null;
1309 l_latest_asg_action_id := null;
1310 l_latest_process_type := null;
1311 close csr_locking_reversal;
1312 else
1313 close csr_locking_reversal;
1314 exit;
1315 end if;
1316 end loop;
1317 --
1318 close csr_date_earned;
1319 p_date_earned := l_date_earned;
1320 p_latest_process_type := l_latest_process_type;
1321 p_latest_assignment_action_id := l_latest_asg_action_id;
1322 hr_utility.set_location(' Leaving ' || l_proc, 100);
1323 end get_latest_run_data;
1324 ------------------------------------------------------------------------------
1325 -- LOAD EMPLOYEE DATES
1326 -- DESCRIPTION : fetches and adjustes employee dates
1327 ------------------------------------------------------------------------------
1328 procedure load_employee_dates(
1329 p_assignment_id in number
1330 ,p_effective_date in date
1331 ,p_assignment_action_id in number
1332 ,p_latest_date_earned in date
1333 ,p_asat_date out nocopy date
1334 ,p_payroll_id in number
1335 ,p_establishment_id in number
1336 ,p_term_reason OUT nocopy varchar2
1337 ,p_term_atd OUT nocopy date
1338 ,p_term_lwd OUT nocopy date
1339 ,p_term_pay_schedule OUT nocopy varchar2) is
1340
1341 --
1342 -- employee dates to determine
1343 --
1344 l_ee_est_start_date date;
1345 l_ee_est_end_date date;
1346 l_ee_pay_period_start_date date;
1347 l_ee_pay_period_end_date date;
1348 l_ee_pay_date date;
1349 l_ee_deposit_date date;
1350 l_direct_dd_date date;
1351 l_ee_seniority_date date;
1352 l_ee_termination_date date;
1353 l_ee_adjusted_term_date date; -- this is term date bounded by period dates
1354 --
1355 l_action_info_id number (15);
1356 l_ovn number (15);
1357 l_archive_type varchar2(3) := 'AAP';
1358 l_proc VARCHAR2(60):= g_package||' Load_Employee_dates ';
1359 --
1360 -- csr_estab_start_date Gets the employees estab start dates
1361 --
1362 cursor csr_ee_estab_start_date (p_assignment_id number, p_date_earned date, p_establishment_id number) is
1363 select min(asg1.effective_start_date)
1364 from per_all_assignments_f asg1
1365 ,per_all_assignments_f asg2
1366 where asg1.assignment_id = p_assignment_id
1367 and asg2.assignment_id = p_assignment_id
1368 and asg1.establishment_id = asg2.establishment_id
1369 and asg2.effective_start_date >= asg1.effective_start_date
1370 and not exists /* no in between row with different estab id */
1371 (select null
1372 from per_all_assignments_f asg
1373 where asg.effective_start_date > asg1.effective_end_Date
1374 and asg.effective_end_date < asg2.effective_start_date
1375 and asg.establishment_id <> asg1.establishment_id
1376 and asg.assignment_id = asg1.assignment_id)
1377 and asg2.effective_start_date =
1378 (select max(effective_Start_Date)
1379 from per_all_assignments_f
1380 where assignment_id = p_assignment_id
1381 and establishment_id = p_establishment_id
1382 and effective_Start_Date <= p_date_earned);
1383
1384 --
1385 -- csr_estab_end_date Gets the employees estab end dates
1386 --
1387 cursor csr_ee_estab_end_date (p_assignment_id number, p_date_earned date, p_establishment_id number) is
1388 select max(asg4.effective_end_date)
1389 from per_all_assignments_f asg3
1390 ,per_all_assignments_f asg4
1391 where asg3.assignment_id = p_assignment_id
1392 and asg4.assignment_id = p_assignment_id
1393 and asg3.establishment_id = asg4.establishment_id
1394 and asg4.effective_start_date >= asg3.effective_start_date
1395 and not exists /* no in between row with different estab id */
1396 (select null
1397 from per_all_assignments_f asg
1398 where asg.effective_start_date < asg4.effective_start_Date
1399 and asg.effective_start_date > asg3.effective_start_date
1400 and asg.establishment_id <> asg4.establishment_id
1401 and asg.assignment_id = asg4.assignment_id)
1402 and asg4.effective_start_date =
1403 (select max(effective_Start_Date)
1404 from per_all_assignments_f
1405 where assignment_id = p_assignment_id
1406 and establishment_id = p_establishment_id
1407 and effective_Start_Date <= p_date_earned);
1408
1409 --
1410 -- csr_direct deposit date gets the default bank deposit date
1411 --
1412 cursor csr_deposit_date (p_payroll_id number, p_latest_date_earned date) is
1413 select default_dd_date
1414 from per_time_periods
1415 where payroll_id = p_payroll_id
1416 and p_latest_date_earned between start_date and end_date;
1417 --
1418 -- If there has already been a mag tape transfer, get the transfer date from
1419 -- that payroll action.
1420 -- The transfer action's assignment action will be locking the same prepayment
1421 -- actions as the archive extract.
1422 --
1423 cursor csr_actual_deposit_date (p_payroll_id number
1424 ,p_assignment_action_id number
1425 ,p_assignment_id number) is
1426 select m_ppa.overriding_dd_date
1427 from pay_action_interlocks a_lock
1428 ,pay_payroll_actions a_ppa
1429 ,pay_assignment_Actions a_asg
1430 ,pay_assignment_actions m_asg
1431 ,pay_assignment_actions p_asg
1432 ,pay_payroll_actions m_ppa
1433 ,pay_action_interlocks m_lock
1434 where a_lock.locking_Action_id = p_assignment_action_id
1435 and a_lock.locked_action_id = a_asg.assignment_Action_id
1436 and a_asg.payroll_action_id = a_ppa.payroll_action_id
1437 and a_asg.assignment_id = p_assignment_id
1438 and a_ppa.action_type = 'U'
1439 and a_ppa.action_status = 'C'
1440 and a_ppa.payroll_action_id = p_asg.payroll_action_id
1441 and p_asg.assignment_id = p_assignment_id
1442 and p_asg.assignment_Action_id = m_lock.locked_action_id
1443 and m_lock.locking_action_id = m_asg.assignment_action_id
1444 and m_asg.assignment_id = p_assignment_id
1445 and m_asg.payroll_action_id = m_ppa.payroll_action_id
1446 and m_ppa.action_type = 'M'
1447 and m_ppa.action_status = 'C';
1448 --
1449 -- svc history record, get termination date and seniority date
1450 -- There must be a svc record as at effective date
1451 --
1452 cursor csr_get_service_dates (p_assignment_id number, p_effective_date date) is
1453 select svc.actual_termination_date ATD
1454 ,nvl(svc.adjusted_svc_date, svc.date_start)
1455 ,hr_general.decode_lookup('LEAV_REAS', svc.leaving_reason)
1456 ,svc.pds_information11 final_pay_schedule
1457 ,fnd_date.canonical_to_date(svc.pds_information10) LWD
1458 from per_periods_of_service svc
1459 ,per_all_assignments_f asg
1460 where asg.period_of_service_id = svc.period_of_service_id
1461 and p_effective_date between asg.effective_start_date and asg.effective_end_date
1462 and asg.assignment_id = p_assignment_id;
1463 --
1464 BEGIN
1465
1466 hr_utility.set_location('Entering ' || l_proc, 10);
1467 --
1468 -- Fetch the address data
1469 --
1470 open csr_ee_estab_start_date(p_assignment_id, p_latest_date_earned, p_establishment_id);
1471 fetch csr_ee_estab_start_date INTO l_ee_est_start_date;
1472 close csr_ee_estab_start_date;
1473 --
1474 --
1475 -- hr_utility.trace('p_assignment_id ' || to_char(p_assignment_id));
1476 -- hr_utility.trace('p_latest_date_earned ' || to_char(p_latest_date_earned));
1477 -- hr_utility.trace('p_establishment_id ' || to_char(p_establishment_id));
1478 --
1479 open csr_ee_estab_end_date(p_assignment_id, p_latest_date_earned, p_establishment_id);
1480 fetch csr_ee_estab_end_date INTO l_ee_est_end_date; /* may be eot */
1481 close csr_ee_estab_end_date;
1482 --
1483 -- Fetch the actual direct deposit dates
1484 --
1485 hr_utility.trace('p_assignment_action_id ' || p_assignment_action_id);
1486
1487 open csr_actual_deposit_date(p_payroll_id, p_assignment_action_id, p_assignment_id);
1488 fetch csr_actual_deposit_date INTO l_direct_dd_date;
1489 close csr_actual_deposit_date;
1490 --
1491 -- Fetch the direct deposit dates, if actual does not exist;
1492 --
1493 if l_direct_dd_date is null then
1494 open csr_deposit_date(p_payroll_id, p_latest_date_earned);
1495 fetch csr_deposit_date INTO l_direct_dd_date;
1496 close csr_deposit_date;
1497 end if;
1498 --
1499 -- Get the service history dates
1500 --
1501 hr_utility.set_location('Entering ' || l_proc, 22);
1502 open csr_get_service_dates(p_assignment_id, p_effective_date);
1503 hr_utility.set_location('Entering ' || l_proc, 23);
1504 fetch csr_get_service_dates INTO l_ee_termination_date
1505 ,l_ee_seniority_date
1506 ,p_term_reason
1507 ,p_term_pay_schedule
1508 ,p_term_lwd;
1509 hr_utility.set_location('Entering ' || l_proc, 24);
1510 /* pass actual termination date to out variable */
1511 p_term_atd := l_ee_termination_date;
1512 close csr_get_service_dates;
1513 --
1514 -- Store the dates
1515 --
1516 hr_utility.set_location('Adjusting Dates ' || l_proc, 20);
1517 --
1518 -- Adjust the employee dates
1519 --
1520 -- adjust termination date to be last day of period if null or outside period
1521 l_ee_adjusted_term_date := greatest(least(nvl(l_ee_termination_date
1522 ,g_param_effective_date) ,g_param_effective_date)
1523 ,greatest(nvl(l_ee_termination_date, g_param_start_date)
1524 ,g_param_start_date));
1525 --
1526 l_ee_pay_period_start_date := greatest(g_param_start_date, l_ee_est_start_date);
1527 --
1528 l_ee_pay_period_end_date := least(g_param_effective_date
1529 ,l_ee_adjusted_term_date
1530 ,l_ee_est_end_date);
1531 --
1532 l_ee_pay_date := least( l_ee_pay_period_end_date ,p_latest_date_earned);
1533 --
1534 l_ee_deposit_date := l_direct_dd_date;
1535 --
1536 --
1537 -- Archive the employee dates details
1538 --
1539 pay_action_information_api.create_action_information (
1540 p_action_information_id => l_action_info_id
1541 , p_action_context_id => p_assignment_action_id
1542 , p_action_context_type => l_archive_type
1543 , p_object_version_number => l_ovn
1544 , p_tax_unit_id => p_establishment_id
1545 , p_action_information_category => 'FR_SOE_EE_DATES'
1546 , p_action_information4 => fnd_date.date_to_canonical(l_ee_est_start_date)
1547 , p_action_information5 => fnd_date.date_to_canonical(l_ee_est_end_date)
1548 , p_action_information6 => fnd_date.date_to_canonical(l_ee_pay_date)
1549 , p_action_information7 => fnd_date.date_to_canonical(l_ee_pay_period_start_date)
1550 , p_action_information8 => fnd_date.date_to_canonical(l_ee_pay_period_end_date)
1551 , p_action_information9 => fnd_date.date_to_canonical(l_ee_deposit_date)
1552 , p_action_information10 => fnd_date.date_to_canonical(l_ee_seniority_date));
1553 --
1554 -- Termination date is not archived
1555 --
1556 -- pass back the date as at to fetch the employee data
1557 --
1558 p_asat_date := l_ee_pay_period_end_date;
1559 --
1560 hr_utility.set_location('Leaving ' || l_proc, 100);
1561 end load_employee_dates;
1562 -------------------------------------------------------------------------------
1563 -- LOAD EMPLOYEE
1564 -- DESCRIPTION : Archives basic employee datails as at p_asat_date
1565 -- Archives ee address
1566 -- Archives ee collective agreement grade(s) and coefficient
1567 -------------------------------------------------------------------------------
1568 procedure load_employee(
1569 p_assignment_id in number
1570 ,p_person_id in number
1571 ,p_asat_date in date /* fetch ee data as at this date */
1572 ,p_assignment_action_id in number
1573 ,p_latest_date_earned in date
1574 ,p_establishment_id in number
1575 ,p_ee_info_id out nocopy number) is
1576 --
1577 -- ADDRESS items
1578 --
1579 l_address_1 varchar2(150);
1580 l_address_2 varchar2(150);
1581 l_address_3 varchar2(150);
1582 l_address_insee_code varchar2(150);
1583 l_address_small_town varchar2(150);
1584 l_address_city varchar2(150);
1585 l_address_post_code varchar2(150);
1586 --
1587 -- ee details cursor items
1588 --
1589 l_ee_soc_sec_number varchar2(30);
1590 l_ee_last_name per_all_people_f.last_name%TYPE;
1591 l_ee_first_name per_all_people_f.first_name%TYPE;
1592 l_ee_full_name per_all_people_f.full_name%TYPE;
1593 l_ee_job_name per_jobs.name%TYPE;
1594 l_ee_job_id per_jobs.job_id%type;
1595 l_ee_job_definition_id per_jobs.job_definition_id%type;
1596 l_ee_position_name per_positions.name%TYPE;
1597 l_ee_coll_agree_name per_collective_agreements.name%TYPE;
1598 l_ee_coy_id varchar2(150);
1599 l_ee_payroll_id per_all_assignments_f.payroll_id%TYPE;
1600 l_ee_maiden_name per_all_people_f.previous_last_name%TYPE;
1601 l_ee_assignment_number per_all_assignments_f.assignment_number%TYPE;
1602 l_ee_org_name hr_all_organization_units.name%TYPE;
1603 --
1604 NO_ADDRESS EXCEPTION; /* error as must be sent to a home address */
1605 --
1606 -- ee CAGR details
1607 --
1608 l_qualifier fnd_segment_attribute_values.segment_attribute_type%TYPE;
1609 l_cagr_value per_cagr_grades_def.segment1%TYPE;
1610 l_coefficient per_cagr_grades_def.segment1%TYPE;
1611 l_coefficient_name fnd_id_flex_segments.segment_name%TYPE;
1612 --
1613 -- Archiver local variables
1614 --
1615 l_action_info_id number (15);
1616 l_ovn number (15);
1617 l_archive_type varchar2(3) := 'AAP';
1618 --
1619 -- Local variables added as part of tiem analysis changes
1620 l_con_fixed_working_time per_contracts_f.ctr_information10%type;
1621 l_amount per_contracts_f.ctr_information11%type;
1622 l_units per_contracts_f.ctr_information12%type;
1623 l_units_mean hr_lookups.meaning%type;
1624 l_frequency per_contracts_f.ctr_information13%type;
1625 l_freq_mean hr_lookups.meaning%type;
1626 --
1627 -- csr_ee_address Gets the employees primary address
1628 --
1629 cursor csr_ee_address (p_person_id number, p_asat_date date) is
1630 select addr.address_line1, addr.address_line2, addr.address_line3
1631 ,addr.region_2, addr.region_3, addr.town_or_city, addr.postal_code
1632 from per_addresses addr
1633 where addr.person_id = p_person_id
1634 and addr.primary_flag = 'Y'
1635 and p_asat_date >= addr.date_from
1636 and p_asat_date <= nvl(addr.date_to, p_asat_date);
1637
1638 l_proc VARCHAR2(40):= g_package||' Load_Employee ';
1639 --
1640 -- csr_ee_details gets basic person and assignment details
1641 --
1642 -- Changed for Time_analysis
1643 --
1644 cursor csr_ee_details(p_assignment_id number, p_asat_date date) is
1645 Select peo.national_identifier
1646 ,peo.last_name
1647 ,peo.first_name
1648 ,peo.full_name
1649 ,job.job_id
1650 ,job.job_definition_id
1651 ,postl.name
1652 ,cag.name
1653 ,to_number(estinfo.org_information1)
1654 ,asg.payroll_id
1655 ,peo.previous_last_name
1656 ,asg.assignment_number
1657 ,orgtl.name
1658 ,ctr.ctr_information10 fixed_working_time
1659 ,ctr.ctr_information11 Con_Amount
1660 ,ctr.ctr_information12 Con_Units
1661 ,hrl.meaning Con_Unit_mean
1662 ,ctr.ctr_information13 Con_Frequency
1663 ,hrl1.meaning Con_freq_mean
1664 from
1665 per_all_assignments_f asg
1666 ,per_all_people_f peo
1667 ,per_jobs job
1668 ,hr_all_positions_f_tl postl
1669 ,per_collective_agreements cag
1670 ,hr_all_organization_units est
1671 ,hr_organization_information estinfo
1672 ,hr_all_organization_units_tl orgtl
1673 ,per_contracts_f ctr
1674 ,fnd_lookup_values hrl
1675 ,fnd_lookup_values hrl1
1676 where asg.assignment_id = p_assignment_id
1677 and p_asat_date between asg.effective_start_date
1678 and asg.effective_end_date
1679 and asg.establishment_id = est.organization_id
1680 and p_asat_date between est.date_from
1681 and nvl(est.date_to, p_asat_date)
1682 and estinfo.organization_id = est.organization_id
1683 and estinfo.org_information_context = 'FR_ESTAB_INFO'
1684 and asg.collective_agreement_id = cag.collective_agreement_id(+)
1685 and asg.person_id = peo.person_id
1686 and asg.position_id = postl.position_id(+)
1687 and postl.language(+) = userenv('LANG')
1688 and asg.job_id = job.job_id(+)
1689 and orgtl.organization_id = asg.organization_id
1690 and orgtl.language = userenv('LANG')
1691 and p_asat_date between peo.effective_start_date
1692 and peo.effective_end_date
1693 and asg.contract_id = ctr.contract_id
1694 and p_asat_date between ctr.effective_start_date
1695 and ctr.effective_end_date
1696 and hrl.lookup_type = 'FR_FIXED_TIME_UNITS'
1697 and hrl.view_application_id = 3
1698 and hrl.security_group_id = g_sec_grp_id_fixed_time_units
1699 and hrl.language = userenv('LANG')
1700 and hrl.lookup_code = ctr.ctr_information12
1701 and hrl1.lookup_type = 'FR_FIXED_TIME_FREQUENCY'
1702 and hrl1.view_application_id = 3
1703 and hrl1.security_group_id = g_sec_grp_id_fixed_time_freq
1704 and hrl1.language = userenv('LANG')
1705 and hrl1.lookup_code = ctr.ctr_information13;
1706 --
1707 -- csr_ee_cagr_details gets qualified collective agreement grade segment values
1708 -- nb _all_ tables used as not all columns available in session date views
1709 -- nb any number of qualifiers may be being used.
1710 -- report only 'GRADE' and 'COEFFICIENT' qualified values.
1711 --
1712 cursor csr_ee_cagr_details (p_assignment_id number, p_asat_date date, p_per_id number) is
1713 Select gqual.segment_attribute_type qualifier
1714 ,seg_tl.Form_left_prompt seg_name
1715 ,substr(decode(gqual.application_column_name
1716 ,'SEGMENT1', CAGR.segment1
1717 ,'SEGMENT2', CAGR.segment2
1718 ,'SEGMENT3', CAGR.segment3
1719 ,'SEGMENT4', CAGR.segment4
1720 ,'SEGMENT5', CAGR.segment5
1721 ,'SEGMENT6', CAGR.segment6
1722 ,'SEGMENT7', CAGR.segment7
1723 ,'SEGMENT8', CAGR.segment8
1724 ,'SEGMENT9', CAGR.segment9
1725 ,'SEGMENT10', CAGR.segment10
1726 ,'SEGMENT11', CAGR.segment11
1727 ,'SEGMENT12', CAGR.segment12
1728 ,'SEGMENT13', CAGR.segment13
1729 ,'SEGMENT14', CAGR.segment14
1730 ,'SEGMENT15', CAGR.segment15
1731 ,'SEGMENT16', CAGR.segment16
1732 ,'SEGMENT17', CAGR.segment17
1733 ,'SEGMENT18', CAGR.segment18
1734 ,'SEGMENT19', CAGR.segment19
1735 ,'SEGMENT20', CAGR.segment20, null),1,60) seg_value
1736 from
1737 per_all_assignments_f asg
1738 ,per_cagr_grades_def cagr
1739 ,fnd_id_flex_segments seg
1740 ,fnd_id_flex_segments_tl seg_tl
1741 ,fnd_segment_attribute_values gqual
1742 where asg.assignment_id = p_assignment_id
1743 and asg.cagr_grade_def_id = CAGR.cagr_grade_def_id (+)
1744 and gqual.id_flex_num(+) = CAGR.id_flex_num
1745 and gqual.id_flex_code(+) = 'CAGR'
1746 and gqual.attribute_value(+) = 'Y'
1747 and seg.id_flex_code = 'CAGR'
1748 and seg.id_flex_num = asg.cagr_id_flex_num
1749 and seg.application_id = p_per_id
1750 and gqual.application_id = p_per_id
1751 and seg.application_column_name = gqual.application_column_name
1752 and p_asat_date between asg.effective_start_date and asg.effective_end_date
1753 and (gqual.segment_attribute_type = 'COEFFICIENT'
1754 or
1755 gqual.segment_attribute_type = 'GRADE')
1756 and seg_tl.application_id = seg.application_id
1757 and seg_tl.id_flex_code = 'CAGR'
1758 and seg_tl.id_flex_num = asg.cagr_id_flex_num
1759 and seg_tl.application_column_name = seg.application_column_name
1760 and seg_tl.language = userenv('LANG')
1761 order by seg.segment_num;
1762
1763 rec_ee_cagr_details csr_ee_cagr_details%ROWTYPE;
1764 --
1765 BEGIN
1766 hr_utility.set_location(' Entering ' || l_proc, 10);
1767 hr_utility.set_location(' as at date is ' || to_char(p_asat_date, 'yyyy-mm-dd'),20);
1768 --
1769 -- Fetch the address data
1770 --
1771 open csr_ee_address(p_person_id, p_asat_date);
1772 fetch csr_ee_address INTO l_address_1, l_address_2, l_address_3
1773 ,l_address_insee_code, l_address_small_town, l_address_city
1774 ,l_address_post_code;
1775 if csr_ee_address%NOTFOUND then
1776 hr_utility.set_message(800, 'PER_52990_ASG_PRADD_NE_PAY');
1777 hr_utility.raise_error;
1778 close csr_ee_address;
1779 else
1780 close csr_ee_address;
1781 --
1782 -- Archive the employee address details
1783 --
1784 pay_action_information_api.create_action_information (
1785 p_action_information_id => l_action_info_id
1786 , p_action_context_id => p_assignment_action_id
1787 , p_action_context_type => l_archive_type
1788 , p_object_version_number => l_ovn
1789 , p_tax_unit_id => p_establishment_id
1790 , p_action_information_category => 'FR_SOE_EE_ADDRESS'
1791 , p_action_information4 => l_address_2
1792 , p_action_information5 => l_address_1
1793 , p_action_information6 => l_address_3
1794 , p_action_information7 => l_address_insee_code
1795 , p_action_information8 => l_address_small_town
1796 , p_action_information9 => l_address_city
1797 , p_action_information10 => l_address_post_code);
1798 end if;
1799 -------------------------------------------------------
1800 -- Get basic employee details
1801 -------------------------------------------------------
1802 hr_utility.set_location('Getting Basic Detail ' || l_proc, 30);
1803 open csr_ee_details(p_assignment_id, p_asat_date);
1804 fetch csr_ee_details INTO l_ee_soc_sec_number, l_ee_last_name, l_ee_first_name
1805 ,l_ee_full_name, l_ee_job_id, l_ee_job_definition_id, l_ee_position_name
1806 ,l_ee_coll_agree_name, l_ee_coy_id
1807 ,l_ee_payroll_id, l_ee_maiden_name, l_ee_assignment_number
1808 ,l_ee_org_name
1809 --Time_analysis changes
1810 ,l_con_fixed_working_time
1811 ,l_amount
1812 ,l_units
1813 ,l_units_mean
1814 ,l_frequency
1815 ,l_freq_mean;
1816 close csr_ee_details;
1817 /* 3815632 appropriate job name is obtained using job_id and job_definition_id values */
1818 if l_ee_job_definition_id is not null then
1819 l_ee_job_name := per_fr_report_utilities.get_job_names (p_job_id => l_ee_job_id,
1820 p_job_definition_id => l_ee_job_definition_id,
1821 p_report_name => 'PAYSLIP');
1822 else
1823 l_ee_job_name := NULL;
1824 end if;
1825 /* 3815632 appropriate job name is obtained using job_id and job_definition_id values */
1826 -----------------------------------------------------------------
1827 -- Get employees CAGR grade(s) and coefficient, if present
1828 -----------------------------------------------------------------
1829 hr_utility.set_location('Fetching CAGR details' || l_proc, 10);
1830 open csr_ee_cagr_details(p_assignment_id, p_asat_date, g_per_id);
1831 LOOP
1832 --@c:\local\fr\bal\pyfrarch.pkb;
1833 fetch csr_ee_cagr_details INTO rec_ee_cagr_details;
1834 EXIT WHEN csr_ee_cagr_details%NOTFOUND;
1835 --
1836 -- Archive the values
1837 --
1838 if rec_ee_cagr_details.qualifier = 'COEFFICIENT' then
1839 -- there is only one coefficient
1840 l_coefficient := rec_ee_cagr_details.seg_value;
1841 l_coefficient_name := rec_ee_cagr_details.seg_name;
1842 --
1843 end if;
1844 if rec_ee_cagr_details.qualifier = 'GRADE' then
1845 pay_action_information_api.create_action_information (
1846 p_action_information_id => l_action_info_id
1847 , p_action_context_id => p_assignment_action_id
1848 , p_action_context_type => l_archive_type
1849 , p_object_version_number => l_ovn
1850 , p_tax_unit_id => p_establishment_id
1851 , p_action_information_category => 'FR_SOE_EE_CAGR'
1852 , p_action_information1 => to_char(csr_ee_cagr_details%ROWCOUNT)
1853 , p_action_information4 => rec_ee_cagr_details.seg_value
1854 , p_action_information5 => rec_ee_cagr_details.seg_name);
1855 end if;
1856 END LOOP;
1857 close csr_ee_cagr_details;
1858 --
1859 -- Now archive the ee basic details with the collectiva agreement coefficient + desc
1860 --
1861 pay_action_information_api.create_action_information (
1862 p_action_information_id => l_action_info_id
1863 , p_action_context_id => p_assignment_action_id
1864 , p_action_context_type => l_archive_type
1865 , p_object_version_number => l_ovn
1866 , p_tax_unit_id => p_establishment_id
1867 , p_action_information_category => 'FR_SOE_EE_DETAILS'
1868 , p_action_information1 => to_char(l_ee_payroll_id)
1869 , p_action_information2 => to_char(p_establishment_id) /* for the estab to address */
1870 , p_action_information3 => l_ee_coy_id /* for the coy to address */
1871 , p_action_information4 => l_ee_soc_sec_number
1872 , p_action_information5 => l_ee_last_name
1873 , p_action_information6 => l_ee_first_name
1874 , p_action_information7 => substr(l_ee_full_name,1,150)
1875 , p_action_information8 => substr(l_ee_job_name, 1,150)
1876 , p_action_information9 => substr(l_ee_position_name, 1,150)
1877 , p_action_information10 => l_ee_coll_agree_name
1878 , p_action_information11 => l_ee_maiden_name
1879 , p_action_information12 => l_coefficient
1880 , p_action_information13 => l_coefficient_name
1881 , p_action_information14 => l_ee_assignment_number
1882 , p_action_information15 => l_ee_org_name
1883 --Time Analysis Changes
1884 , p_action_information17 => l_con_fixed_working_time
1885 , p_action_information18 => l_amount
1886 , p_action_information19 => l_units_mean
1887 , p_action_information20 => l_freq_mean);
1888 --
1889 -- pass out the action_information_id of the ee_details, as may need for update
1890 --
1891 p_ee_info_id := l_action_info_id;
1892 --
1893 hr_utility.set_location('Leaving ' || l_proc, 100);
1894 end load_employee;
1895 -------------------------------------------------------------------------------
1896 -- LOAD_BALANCES
1897 -- DESCRIPTION : Gets the balance values and archives for the given asg.
1898 -------------------------------------------------------------------------------
1899 procedure load_balances(
1900 p_assignment_action_id in number
1901 ,p_archive_action_id in number
1902 ,p_context_id in number
1903 ,p_totals_taxable_income out nocopy number ) is
1904 --
1905 l_balance_value1 pay_assignment_latest_balances.value%TYPE;
1906 l_balance_value2 pay_assignment_latest_balances.value%TYPE;
1907 l_balance_value3 pay_assignment_latest_balances.value%TYPE;
1908 l_balance_value4 pay_assignment_latest_balances.value%TYPE;
1909 l_balance_value5 pay_assignment_latest_balances.value%TYPE;
1910 l_balance_value6 pay_assignment_latest_balances.value%TYPE;
1911 l_balance_value7 pay_assignment_latest_balances.value%TYPE;
1912 l_balance_value8 pay_assignment_latest_balances.value%TYPE;
1913 l_balance_value9 pay_assignment_latest_balances.value%TYPE;
1914 l_balance_value10 pay_assignment_latest_balances.value%TYPE;
1915 l_balance_value11 pay_assignment_latest_balances.value%TYPE;
1916 l_balance_value12 pay_assignment_latest_balances.value%TYPE;
1917 --
1918 l_action_info_id number(15);
1919 l_archive_type varchar2(3) := 'AAP';
1920 l_ovn number (15);
1921 --
1922 l_proc VARCHAR2(40):= g_package||' load_balances ';
1923 BEGIN
1924 hr_utility.set_location(' Entering ' || l_proc, 10);
1925 --
1926 -- set the contexts
1927 --
1928 pay_balance_pkg.set_context ('ASSIGNMENT_ACTION_ID',to_char(p_assignment_action_id));
1929 pay_balance_pkg.set_context ('TAX_UNIT_ID',to_char(p_context_id));
1930 --
1931 -- Get all the defined balance values for this assignment
1932 --
1933 -- BALANCE 1 total gross pay
1934 l_balance_value1 := pay_balance_pkg.get_value (
1935 p_defined_balance_id => g_1total_gross_pay_db,
1936 p_assignment_action_id => p_assignment_action_id);
1937 hr_utility.set_location('Balance 1 is ' || to_char(l_balance_value1), 15);
1938 -- BALANCE 2 ss ceiling
1939 l_balance_value2 := pay_balance_pkg.get_value (
1940 p_defined_balance_id => g_2ss_ceiling_db
1941 ,p_assignment_action_id => p_assignment_action_id);
1942 hr_utility.set_location('Balance 2 is ' || to_char(l_balance_value2), 20);
1943 -- BALANCE 3 employees total contributions
1944 l_balance_value3 := pay_balance_pkg.get_value (
1945 p_defined_balance_id => g_3es_total_contributions_db
1946 ,p_assignment_action_id => p_assignment_action_id);
1947 hr_utility.set_location('Balance 3 is ' || to_char(l_balance_value3), 30);
1948 -- BALANCE 4 statutory employer charges
1949 l_balance_value4 := pay_balance_pkg.get_value (
1950 p_defined_balance_id => g_4statutory_er_charges_db
1951 ,p_assignment_action_id => p_assignment_action_id);
1952 hr_utility.set_location('Balance 4 is ' || to_char(l_balance_value4), 40);
1953 -- BALANCE 5 conventional employer charges
1954 l_balance_value5 := pay_balance_pkg.get_value (
1955 p_defined_balance_id => g_5conventional_er_charges_db
1956 ,p_assignment_action_id => p_assignment_action_id);
1957 hr_utility.set_location('Balance 5 is ' || to_char(l_balance_value5), 50);
1958 -- BALANCE 6 t1 arrco band
1959 l_balance_value6 := pay_balance_pkg.get_value (
1960 p_defined_balance_id => g_6t1_arrco_band_db
1961 ,p_assignment_action_id => p_assignment_action_id);
1962 hr_utility.set_location('Balance 6 is ' || to_char(l_balance_value6), 60);
1963 -- BALANCE 7 t2 arrco band
1964 l_balance_value7 := pay_balance_pkg.get_value (
1965 p_defined_balance_id => g_7t2_arrco_band_db
1966 ,p_assignment_action_id => p_assignment_action_id);
1967 hr_utility.set_location('Balance 7 is ' || to_char(l_balance_value7), 70);
1968 -- BALANCE 8 tb arrco band
1969 l_balance_value8 := pay_balance_pkg.get_value (
1970 p_defined_balance_id => g_8tb_argic_band_db
1971 ,p_assignment_action_id => p_assignment_action_id);
1972 hr_utility.set_location('Balance 8 is ' || to_char(l_balance_value8), 80);
1973 -- BALANCE 9 tc arrco band
1974 l_balance_value9 := pay_balance_pkg.get_value (
1975 p_defined_balance_id => g_9tc_agirc_band_db
1976 ,p_assignment_action_id => p_assignment_action_id);
1977 hr_utility.set_location('Balance 9 is ' || to_char(l_balance_value9), 90);
1978 -- BALANCE 10 gmp agirc band
1979 l_balance_value10 := pay_balance_pkg.get_value (
1980 p_defined_balance_id => g_10gmp_agirc_band_db
1981 ,p_assignment_action_id => p_assignment_action_id);
1982 hr_utility.set_location('Balance 10 is ' || to_char(l_balance_value10), 100);
1983 -- BALANCE 11 total cost to employer
1984 l_balance_value11 := pay_balance_pkg.get_value (
1985 p_defined_balance_id => g_11total_cost_to_employer_db
1986 ,p_assignment_action_id => p_assignment_action_id);
1987 hr_utility.set_location('Balance 110 is ' || to_char(l_balance_value11), 110);
1988 --
1989 -- Archive the employee balances
1990 --
1991 pay_action_information_api.create_action_information (
1992 p_action_information_id => l_action_info_id
1993 , p_action_context_id => p_archive_action_id
1994 , p_action_context_type => l_archive_type
1995 , p_object_version_number => l_ovn
1996 , p_tax_unit_id => p_context_id
1997 , p_action_information_category => 'FR_SOE_EE_BALANCES'
1998 , p_action_information4 => to_char(l_balance_value1)
1999 , p_action_information5 => to_char(l_balance_value2)
2000 , p_action_information6 => to_char(l_balance_value3)
2001 , p_action_information7 => to_char(l_balance_value4)
2002 , p_action_information8 => to_char(l_balance_value5)
2003 , p_action_information9 => to_char(l_balance_value6)
2004 , p_action_information10 => to_char(l_balance_value7)
2005 , p_action_information11 => to_char(l_balance_value8)
2006 , p_action_information12 => to_char(l_balance_value9)
2007 , p_action_information13 => to_char(l_balance_value10)
2008 , p_action_information14 => to_char(l_balance_value11));
2009 --
2010 -- BALANCE 12 taxable income
2011 -- This is an exception, it is stored as a totals value as it appears within
2012 -- the deductions block against totals text, and is a PTD dimension
2013 --
2014 l_balance_value12 := pay_balance_pkg.get_value (
2015 p_defined_balance_id => g_12taxable_income_db
2016 ,p_assignment_action_id => p_assignment_action_id);
2017 hr_utility.set_location('Balance 12 is ' || to_char(l_balance_value12), 120);
2018 hr_utility.set_location('Balance 12 db ' || to_char(g_12taxable_income_db), 121);
2019 --
2020 -- Set out variables
2021 --
2022 p_totals_taxable_income := l_balance_value12;
2023 --
2024 hr_utility.set_location(' Leaving ' || l_proc, 150);
2025 --
2026 end load_balances;
2027 -------------------------------------------------------------------------------
2028 -- LOAD_HOLIDAYS
2029 -- DESCRIPTION : Calls pay_fr_pto_pkg function to load a pl/sql table
2030 -- with results for archiving,
2031 -- Then fetches from the table
2032 -------------------------------------------------------------------------------
2033 procedure load_holidays(
2034 p_assignment_id in number
2035 ,p_person_id in number
2036 ,p_effective_date in date
2037 ,p_assignment_action_id in number
2038 ,p_establishment_id in number
2039 ,p_business_Group_id in number) is
2040 --
2041 l_action_info_id number (15);
2042 l_ovn number (15);
2043 l_archive_type varchar2(3) := 'AAP';
2044 --
2045 l_proc VARCHAR2(40):= g_package||' Load Holidays ';
2046 --
2047 BEGIN
2048 hr_utility.set_location('Entering ' || l_proc, 10);
2049 --
2050 -- Get the holiday values
2051 --
2052 pay_fr_pto_pkg.load_fr_payslip_accrual_data
2053 (p_business_group_id => p_business_Group_id
2054 ,p_date_earned => p_effective_date
2055 ,p_assignment_id => p_assignment_id );
2056 --
2057 hr_utility.set_location('Step ' || l_proc, 20);
2058 --
2059 -- Loop through each record to fetch and archive
2060 --
2061 BEGIN
2062 FOR i in 1 .. pay_fr_pto_pkg.g_fr_payslip_info.LAST LOOP
2063 hr_utility.set_location('I loop is ' || l_proc, i);
2064 pay_action_information_api.create_action_information (
2065 p_action_information_id => l_action_info_id
2066 , p_action_context_id => p_assignment_action_id
2067 , p_action_context_type => l_archive_type
2068 , p_object_version_number => l_ovn
2069 , p_tax_unit_id => p_establishment_id
2070 , p_action_information_category => 'FR_SOE_EE_HOLIDAYS'
2071 , p_action_information4 => to_char(pay_fr_pto_pkg.g_fr_payslip_info(i).entitlement)
2072 , p_action_information5 => to_char(pay_fr_pto_pkg.g_fr_payslip_info(i).accrual)
2073 , p_action_information6 => to_char(pay_fr_pto_pkg.g_fr_payslip_info(i).taken)
2074 , p_action_information7 => to_char(pay_fr_pto_pkg.g_fr_payslip_info(i).balance)
2075 , p_action_information8 => pay_fr_pto_pkg.g_fr_payslip_info(i).plan_name
2076 );
2077 END LOOP;
2078 EXCEPTION
2079 when others then null;
2080 END;
2081 --
2082 hr_utility.set_location('Leaving ' || l_proc, 100);
2083 end load_holidays;
2084 -------------------------------------------------------------------------------
2085 -- LOAD_BANK
2086 -- DESCRIPTION : fetches and archives all prepayments to be shown on payslip.
2087 -- includes bank details, and determines if another soe archive
2088 -- action is also locking it, in which case the value is
2089 -- 'previously notified'
2090 -------------------------------------------------------------------------------
2091 procedure load_bank(
2092 p_assignment_action_id in number
2093 ,p_assignment_id in number
2094 ,p_totals_previous_advice out nocopy number
2095 ,p_totals_this_advice out nocopy number
2096 ,p_totals_net_advice out nocopy number
2097 ,p_establishment_id in number
2098 ,p_asat_date in date) is
2099 --
2100 cursor csr_bank_details(p_archive_action_id number) is
2101 select pppmf.external_account_id external_account_id
2102 ,ppp.pre_payment_id pre_payment_id
2103 ,opmtl.org_payment_method_name payment_method
2104 ,ppp.value amount
2105 ,count(decode(all_arc_lock.locking_action_id
2106 ,this_arc_lock.locking_action_id,null
2107 ,all_arc_lock.locking_action_id)) num_previous
2108 from pay_action_interlocks this_arc_lock
2109 ,pay_action_interlocks all_arc_lock
2110 ,pay_assignment_actions arc_assact
2111 ,pay_payroll_actions arc_payact
2112 ,pay_pre_payments ppp
2113 ,pay_personal_payment_methods_f pppmf
2114 ,pay_org_payment_methods_f_tl opmtl
2115 where this_arc_lock.locking_action_id = p_archive_action_id
2116 and all_arc_lock.locked_action_id = this_arc_lock.locked_action_id
2117 and arc_assact.assignment_action_id = all_arc_lock.locking_action_id
2118 and arc_payact.payroll_action_id = arc_assact.payroll_action_id
2119 and arc_payact.action_type = 'X'
2120 and arc_payact.report_qualifier = 'FR'
2121 and arc_payact.report_category = 'SOE_ARCHIVE'
2122 and arc_payact.report_type = 'SOE_ARCHIVE'
2123 and this_arc_lock.locked_action_id = ppp.assignment_action_id
2124 and ppp.value <> 0
2125 and opmtl.org_payment_method_id = ppp.org_payment_method_id
2126 and opmtl.language = userenv('LANG')
2127 and pppmf.personal_payment_method_id(+) = ppp.personal_payment_method_id
2128 and p_asat_date between pppmf.effective_start_date(+)
2129 and pppmf.effective_end_date(+)
2130 group by opmtl.org_payment_method_name, ppp.value, ppp.pre_payment_id,
2131 pppmf.external_account_id
2132 order by opmtl.org_payment_method_name, ppp.value;
2133
2134 cursor csr_bank(p_account_id number) is
2135 select bank.meaning bank_name
2136 ,substr(pxa.segment2, 1, 5) bank_code
2137 ,substr(pxa.segment3, 1, 5) branch_code
2138 ,substr(pxa.segment5, 1, 14) account_number
2139 from pay_external_accounts pxa
2140 ,hr_lookups bank
2141 where bank.lookup_type(+) = 'FR_BANK'
2142 and bank.lookup_code(+) = pxa.segment1
2143 and pxa.external_account_id = p_account_id;
2144 --
2145 l_proc VARCHAR2(60):= g_package||' Load Bank ';
2146 l_already_notified number(15,2) := 0.00;
2147 l_running_total number(15,2) := 0.00;
2148 l_net_deposit number(15,2) := 0.00;
2149 l_loop_counter smallint := 0;
2150 rec_bank csr_bank_details%ROWTYPE;
2151 rec_acct csr_bank%ROWTYPE;
2152 l_action_info_id number (15);
2153 l_ovn number (15);
2154 l_archive_type varchar2(3) := 'AAP';
2155 --
2156 BEGIN
2157 hr_utility.set_location('Entering ' || l_proc, 10);
2158 --
2159 open csr_bank_details(p_assignment_action_id);
2160 LOOP
2161 fetch csr_bank_details INTO rec_bank;
2162 EXIT WHEN csr_bank_details%NOTFOUND;
2163 l_loop_counter := l_loop_counter + 1;
2164 --
2165 rec_acct.bank_name := null;
2166 rec_acct.bank_code := null;
2167 rec_acct.branch_code := null;
2168 rec_acct.account_number := null;
2169 if rec_bank.external_account_id is not null then
2170 open csr_bank(rec_bank.external_account_id);
2171 fetch csr_bank into rec_acct;
2172 close csr_bank;
2173 end if;
2174 pay_action_information_api.create_action_information (
2175 p_action_information_id => l_action_info_id
2176 ,p_action_context_id => p_assignment_action_id
2177 ,p_action_context_type => l_archive_type
2178 ,p_object_version_number => l_ovn
2179 ,p_tax_unit_id => p_establishment_id
2180 ,p_action_information_category => 'FR_SOE_EE_BANK_DEPOSIT'
2181 ,p_action_information1 => to_char(l_loop_counter) /* to sort by*/
2182 ,p_action_information4 => rec_bank.payment_method
2183 ,p_action_information5 => rec_acct.bank_name
2184 ,p_action_information6 => rec_acct.bank_code
2185 ,p_action_information7 => rec_acct.branch_code
2186 ,p_action_information8 => rec_acct.account_number
2187 ,p_action_information9 => to_char(rec_bank.amount));
2188 --
2189 l_running_total := l_running_total + rec_bank.amount;
2190 if rec_bank.num_previous > 0 then
2191 l_already_notified := l_already_notified + rec_bank.amount;
2192 end if;
2193 END LOOP;
2194 close csr_bank_details;
2195 l_net_deposit := l_running_total - l_already_notified;
2196 hr_utility.set_location('Step ' || l_proc, 50);
2197 --
2198 -- Set out variables
2199 --
2200 p_totals_previous_advice := l_already_notified;
2201 p_totals_this_advice := l_running_total;
2202 p_totals_net_advice := l_net_deposit;
2203 --
2204 hr_utility.set_location('Leaving ' || l_proc, 100);
2205 END load_bank;
2206 -------------------------------------------------------------------------------
2207 -- LOAD_MESSAGES
2208 -- DESCRIPTION : fetches and archives all the payroll action messages locked
2209 -- by this action, in order. eg 'Happy new Year'.
2210 -- also constructs the termination date and reason message line.
2211 -------------------------------------------------------------------------------
2212 procedure load_messages(
2213 p_archive_assignment_action_id in number
2214 ,p_establishment_id in number
2215 ,p_term_atd in date
2216 ,p_term_reason in varchar2) is
2217 --
2218 -- Select all the messages from pay_payroll_actions.pay_advice_message
2219 -- for runs that are locked by this archive assignment action id
2220 -- archiving per assignment allows for future expansion of assignment level
2221 -- messages
2222 --
2223 -- nb messages restricted to 150, as that size in FND_COLUMNS.
2224 --
2225
2226 cursor csr_message_details(p_archive_assignment_action_id number) is
2227 select substrb(run_payact.pay_advice_message, 1, 240) message
2228 from pay_payroll_actions pre_payact
2229 ,pay_assignment_actions pre_assact
2230 ,pay_payroll_actions run_payact
2231 ,pay_assignment_actions run_assact
2232 ,pay_action_interlocks arc_lock
2233 ,pay_action_interlocks pre_lock
2234 where
2235 arc_lock.locking_action_id = p_archive_assignment_action_id
2236 and arc_lock.locked_action_id = pre_assact.assignment_action_id
2237 and pre_lock.locking_action_id = pre_assact.assignment_action_id
2238 and pre_lock.locked_action_id = run_assact.assignment_action_id
2239 and pre_payact.payroll_action_id = pre_assact.payroll_action_id
2240 and run_payact.payroll_action_id = run_assact.payroll_action_id
2241 and run_payact.action_type in ('Q', 'R')
2242 and pre_payact.action_type in ('P', 'U')
2243 and run_payact.pay_advice_message is not null
2244 order by run_payact.action_sequence;
2245 --
2246 l_proc VARCHAR2(60) := g_package||' Load Messages ';
2247 l_message fnd_new_messages.message_text%TYPE;
2248 l_loop_counter smallint := 0;
2249 l_action_info_id number (15);
2250 l_ovn number (15);
2251 l_archive_type varchar2(3) := 'AAP';
2252 --
2253 BEGIN
2254 hr_utility.set_location('Entering ' || l_proc, 10);
2255 open csr_message_details(p_archive_assignment_action_id);
2256 LOOP
2257 fetch csr_message_details INTO l_message;
2258 EXIT WHEN csr_message_details%NOTFOUND;
2259 l_loop_counter := l_loop_counter + 1;
2260 --
2261 -- Archive the Message Details
2262 --
2263 pay_action_information_api.create_action_information (
2264 p_action_information_id => l_action_info_id
2265 , p_action_context_id => p_archive_assignment_action_id
2266 , p_action_context_type => l_archive_type
2267 , p_object_version_number => l_ovn
2268 , p_tax_unit_id => p_establishment_id
2269 , p_action_information_category => 'FR_SOE_EE_MESSAGES'
2270 , p_action_information1 => to_char(l_loop_counter)
2271 , p_action_information4 => substrb(l_message, 1, 150));
2272 --
2273 END LOOP;
2274 --
2275 -- Construct the termination message
2276 --
2277 hr_utility.set_location('Step ' || l_proc, 50);
2278 if p_term_atd is not null then
2279 fnd_message.set_name('PAY', 'PAY_75057_SOE_TERM_DATA');
2280 fnd_message.set_token('TERM_DATE', fnd_date.date_to_displaydate(p_term_atd));
2281 fnd_message.set_token('TERM_REASON',p_term_reason);
2282 l_message := hr_utility.get_message;
2283 l_message := substrb(l_message, 1, 240);
2284 --
2285 -- archive the termination message
2286 --
2287 hr_utility.set_location('Step ' || l_proc, 55);
2288 pay_action_information_api.create_action_information (
2289 p_action_information_id => l_action_info_id
2290 ,p_action_context_id => p_archive_assignment_action_id
2291 ,p_action_context_type => l_archive_type
2292 ,p_object_version_number => l_ovn
2293 ,p_tax_unit_id => p_establishment_id
2294 ,p_action_information_category => 'FR_SOE_EE_MESSAGES'
2295 ,p_action_information1 => to_char(l_loop_counter+1)
2296 ,p_action_information4 => substrb(l_message, 1, 150));
2297 end if;
2298 --
2299 hr_utility.set_location('Leaving ' || l_proc, 100);
2300 close csr_message_details;
2301 END load_messages;
2302 ---------------------------------------------------------------------------------------------------
2303 -- LOAD_EE_RATE_GROUPED_RUNS EARNINGS and NET PAYMENTS
2304 -- DESCRIPTION : loads all element data that is only grouped to consolidate several runs into one
2305 -- payslip.
2306 -- Court Orders is included here, with manual summing within a rubric, to save a cursor.
2307 -- Usually there will be one rubric for all COs, but if the user sets up more than 1
2308 -- it will handle this.
2309 -- Benefits is a special case, the values must be reported separately.
2310 ---------------------------------------------------------------------------------------------------
2311 procedure load_ee_rate_grouped_runs(
2312 p_archive_assignment_action_id in number
2313 ,p_assignment_id in number
2314 ,p_latest_process_type in varchar2
2315 ,p_total_gross_pay out nocopy number
2316 ,p_reductions out nocopy number
2317 ,p_net_payments out nocopy number
2318 ,p_court_orders out nocopy number
2319 ,p_establishment_id in number
2320 ,p_effective_date in date
2321 ,p_termination_reason in varchar2
2322 ,p_term_st_ele_id in number
2323 ,p_term_ex_ele_id in number) is
2324 --
2325 -- Select all the entries in parameterized element classifications for this assignment
2326 -- action.
2327 -- All entries are fetched separately.
2328 --
2329 cursor csr_get_ee_rate_grouped
2330 (p_ee_class1 varchar2, p_ee_class2 varchar2
2331 ,p_ee_class3 varchar2, p_ee_class4 varchar2
2332 ,p_ee_class5 varchar2, p_ee_class6 varchar2
2333 ,p_archive_assignment_action_id number
2334 ,p_us_base_name varchar2 ,p_fr_base_name varchar2
2335 ,p_us_rate_name varchar2 ,p_fr_rate_name varchar2
2336 ,p_us_pay_value_name varchar2 ,p_fr_pay_value_name varchar2
2337 ,p_us_start_name varchar2 ,p_fr_start_name varchar2
2338 ,p_us_end_name varchar2 ,p_fr_end_name varchar2
2339 ,p_retro_tl varchar2) is
2340 select result_rollup.rubric_code Rubric
2341 ,result_rollup.description Description
2342 ,result_rollup.classification_name Class
2343 ,result_rollup.element_information1 Group_Code
2344 ,result_rollup.element_type_id Element
2345 ,result_rollup.process_type Process
2346 ,sum(base_value) Base
2347 ,rate_value Rate
2348 ,sum(pay_value) Amount
2349 ,base_units_meaning base_units
2350 ,week_end_date week_end_date
2351 ,factor factor
2352 ,label label
2353 ,absattid absence_attendance_id
2354 ,decode(classification_name, 'Benefits', run_result_id, 0) distinct_ben
2355 ,decode(classification_name, 'Earnings Adjustment', -1, 1) adjust_sign
2356 /* get the correct dates for non-retro - input value names or proration dates if they exist */
2357 ,nvl(result_rollup.start_date, fnd_date.date_to_canonical(result_rollup.prorate_start_date)) std_start_date
2358 ,nvl(result_rollup.end_date, fnd_date.date_to_canonical(result_rollup.prorate_end_date)) std_end_Date
2359 /* get the correct dates for retro - retro proration dates or original period */
2360 ,decode(creator_type,'EE', nvl(result_rollup.retro_pro_start, ptp.start_date), 'RR', nvl(result_rollup.retro_pro_start, ptp.start_date)) retro_pro_start
2361 ,decode(creator_type,'EE', nvl(result_rollup.retro_pro_end, ptp.end_date) , 'RR', nvl(result_rollup.retro_pro_end, ptp.end_date)) retro_pro_end
2362 ,creator_type
2363 from (
2364 select
2365 pee.creator_type creator_type,
2366 max(decode(piv.name,p_fr_start_name,prrv.result_value,
2367 p_us_start_name,prrv.result_value)) start_date,
2368 max(decode(piv.name,p_fr_end_name,prrv.result_value,
2369 p_us_end_name,prrv.result_value)) end_date,
2370 decode(pee.creator_type,
2371 'RR',rr_ret.assignment_action_id,
2372 'EE',pee.source_asg_action_id) retro_act_id,
2373 nvl(user_rubric.tag,seed_rubric.tag) rubric_code,
2374 nvl(user_rubric.meaning,seed_rubric.meaning)
2375 || decode(pee.creator_type,'EE',' '|| p_retro_tl,
2376 'RR',' '|| p_retro_tl) description,
2377 pec.classification_name,
2378 pet.element_information1,
2379 pet.element_type_id,
2380 max(decode(piv.name,'Process_Type',prrv.result_value)) process_type,
2381 max(decode(piv.name,p_fr_base_name,prrv.result_value,
2382 p_us_base_name,prrv.result_value)) base_value,
2383 max(decode(piv.name,p_fr_rate_name,prrv.result_value,
2384 p_us_rate_name,prrv.result_value)) rate_value,
2385 max(decode(piv.name,p_fr_pay_value_name,prrv.result_value,
2386 p_us_pay_value_name,prrv.result_value)) pay_value,
2387 base_units.meaning base_units_meaning,
2388 max(decode(piv.name,'Week End Date',prrv.result_value)) week_end_date,
2389 max(decode(piv.name,'Overtime Factor',prrv.result_value)) factor,
2390 max(decode(piv.name,'Label',prrv.result_value)) label,
2391 max(decode(piv.name,'Absence Attendance ID',prrv.result_value)) absattid,
2392 prrv.run_result_id,
2393 prr.start_date prorate_start_date,
2394 prr.end_date prorate_end_date,
2395 decode(pee.creator_type,
2396 'EE',pee.source_start_date,
2397 'RR',rr_ret.start_date) retro_pro_start,
2398 decode(pee.creator_type,
2399 'EE',pee.source_end_date,
2400 'RR',rr_ret.end_date) retro_pro_end
2401 from pay_run_result_values prrv
2402 ,pay_run_results prr
2403 ,pay_element_types_f pet
2404 ,pay_element_classifications pec
2405 ,pay_input_values_f_tl piv
2406 ,fnd_lookup_values user_rubric
2407 ,fnd_lookup_values seed_rubric
2408 ,fnd_lookup_values base_units
2409 ,pay_payroll_actions pre_payact
2410 ,pay_assignment_actions pre_assact
2411 ,pay_payroll_actions run_payact
2412 ,pay_assignment_actions run_assact
2413 ,pay_action_interlocks arc_lock
2414 ,pay_action_interlocks pre_lock
2415 ,pay_element_entries_f pee
2416 ,pay_run_results rr_ret
2417 where pee.element_entry_id(+) = prr.source_id
2418 and rr_ret.run_result_id(+) = pee.source_id
2419 and prrv.run_result_id = prr.run_result_id
2420 and prr.element_type_id = pet.element_type_id
2421 and prr.element_type_id = pet.element_type_id
2422 and p_effective_date between pet.effective_start_date
2423 and pet.effective_end_date
2424 and pet.classification_id = pec.classification_id
2425 and pec.classification_name in (p_ee_class1,p_ee_class2
2426 ,p_ee_class3,p_ee_class4
2427 ,p_ee_class5,p_ee_class6)
2428 and piv.input_value_id = prrv.input_value_id
2429 and piv.language = userenv('lang')
2430 and piv.name in (p_us_pay_value_name, p_fr_pay_value_name
2431 ,p_us_base_name, p_fr_base_name
2432 ,p_us_rate_name, p_fr_rate_name
2433 ,p_us_start_name, p_fr_start_name
2434 ,p_us_end_name, p_fr_end_name
2435 ,'Overtime Factor','Label'
2436 ,'Process_Type','Absence Attendance ID', 'Week End Date')
2437 and prr.assignment_action_id = run_assact.assignment_action_id
2438 and arc_lock.locking_action_id = p_archive_assignment_action_id
2439 and arc_lock.locked_action_id = pre_assact.assignment_action_id
2440 and pre_lock.locking_action_id = pre_assact.assignment_action_id
2441 and pre_lock.locked_action_id = run_assact.assignment_action_id
2442 and pre_payact.payroll_action_id = pre_assact.payroll_action_id
2443 and run_payact.payroll_action_id = run_assact.payroll_action_id
2444 and run_payact.action_type in ('Q', 'R')
2445 and pre_payact.action_type in ('P', 'U')
2446 and user_rubric.lookup_code(+) = pet.element_information1
2447 and user_rubric.lookup_type(+) = 'FR_USER_ELEMENT_GROUP'
2448 and user_rubric.LANGUAGE(+) = USERENV('LANG')
2449 and user_rubric.security_group_id(+) = g_sec_grp_id_user_element_grp
2450 and user_rubric.VIEW_APPLICATION_ID(+) = 3
2451 and seed_rubric.lookup_code(+) = pet.element_information1
2452 and seed_rubric.lookup_type(+) = 'FR_ELEMENT_GROUP'
2453 and seed_rubric.LANGUAGE(+) = USERENV('LANG')
2454 and seed_rubric.security_group_id(+) = g_sec_grp_id_element_grp
2455 and seed_rubric.VIEW_APPLICATION_ID(+) = 3
2456 and base_units.lookup_type(+) = 'FR_BASE_UNIT'
2457 and base_units.lookup_code(+) = pet.element_information2
2458 and base_units.LANGUAGE(+) = USERENV('LANG') /*bug 3683906*/
2459 and base_units.security_group_id(+) = g_sec_grp_id_base_unit
2460 and base_units.VIEW_APPLICATION_ID(+) = 3
2461 and prrv.result_value is not null
2462 group by nvl(user_rubric.tag,seed_rubric.tag),
2463 nvl(user_rubric.meaning,seed_rubric.meaning) || decode(pee.creator_type,'EE',' '|| p_retro_tl,'RR',' '|| p_retro_tl),
2464 pec.classification_name,
2465 pet.element_information1,
2466 pet.element_type_id,
2467 base_units.meaning,
2468 prrv.run_result_id,
2469 prr.start_date,
2470 prr.end_date,
2471 decode(pee.creator_type,
2472 'RR',rr_ret.assignment_action_id,
2473 'EE',pee.source_asg_action_id),
2474 pee.creator_type,
2475 decode(pee.creator_type,
2476 'EE',pee.source_start_date,
2477 'RR',rr_ret.start_date),
2478 decode(pee.creator_type,
2479 'EE',pee.source_end_date,
2480 'RR',rr_ret.end_date)
2481 ) result_rollup
2482 ,pay_assignment_actions paa
2483 ,pay_payroll_actions ppa
2484 ,per_time_periods ptp
2485 where paa.assignment_action_id (+) = result_rollup.retro_act_id
2486 and ppa.payroll_action_id (+) = paa.payroll_action_id
2487 and ptp.time_period_id (+) = ppa.time_period_id
2488 group by result_rollup.start_date
2489 ,result_rollup.end_date
2490 ,result_rollup.rubric_code
2491 ,result_rollup.description
2492 ,result_rollup.classification_name
2493 ,result_rollup.element_information1
2494 ,result_rollup.element_type_id
2495 ,result_rollup.process_type
2496 ,result_rollup.rate_value
2497 ,result_rollup.base_units_meaning
2498 ,result_rollup.week_end_date
2499 ,result_rollup.factor
2500 ,result_rollup.label
2501 ,result_rollup.absattid
2502 ,decode(classification_name, 'Benefits', run_result_id, 0)
2503 ,decode(classification_name, 'Earnings Adjustment', -1, 1)
2504 ,result_rollup.prorate_start_date
2505 ,result_rollup.prorate_end_date
2506 ,result_rollup.creator_type
2507 ,decode(creator_type,'EE', nvl(result_rollup.retro_pro_start, ptp.start_date), 'RR', nvl(result_rollup.retro_pro_start, ptp.start_date))
2508 ,decode(creator_type,'EE', nvl(result_rollup.retro_pro_end, ptp.end_date) , 'RR', nvl(result_rollup.retro_pro_end, ptp.end_date))
2509 ,nvl(result_rollup.start_date, fnd_date.date_to_canonical(result_rollup.prorate_start_date))
2510 ,nvl(result_rollup.end_date, fnd_date.date_to_canonical(result_rollup.prorate_end_date))
2511 order by decode(absattid,null,null,result_rollup.start_date)
2512 ,rubric_code
2513 ,result_rollup.description desc
2514 ,week_end_date
2515 ,label
2516 ,decode(creator_type,'EE', nvl(result_rollup.retro_pro_start, ptp.start_date), 'RR', nvl(result_rollup.retro_pro_start, ptp.start_date))
2517 ,decode(creator_type,'EE',2, 'RR',1,3)
2518 ,process_type
2519 ,sum(base_value);
2520
2521 cursor csr_process_meaning (p_process_type varchar2) is
2522 select upper(meaning)
2523 from fnd_lookup_values
2524 where lookup_type = 'FR_PROCESS_TYPE'
2525 and view_application_id = 3
2526 and lookup_code = p_process_type
2527 and security_group_id = g_sec_grp_id_process_type
2528 and language = userenv('LANG');
2529 --
2530 rec_results csr_get_ee_rate_grouped%ROWTYPE;
2531 l_proc VARCHAR2(60):= g_package||' load_ee_rate_grouped_runs ';
2532 l_ee_classification1 varchar2(60);
2533 l_ee_classification2 varchar2(60);
2534 l_ee_classification3 varchar2(60);
2535 l_ee_classification4 varchar2(60);
2536 l_ee_classification5 varchar2(60);
2537 l_ee_classification6 varchar2(60);
2538 --
2539 l_context_prefix varchar2(30);
2540 l_context varchar2(30) := 'FR_SOE_ELEMENTS';
2541 l_loop smallint;
2542 --
2543 -- Archiver local variables
2544 l_action_info_id number (15);
2545 l_ovn number (15);
2546 l_archive_type varchar2(3) := 'AAP';
2547 --
2548 l_total_gross_pay number(15,2) := 0.00;
2549 l_total_subject number(15,2) := 0.00;
2550 l_total_net_payments number(15,2) := 0.00;
2551 l_total_court_orders number(15,2) := 0.00;
2552 l_total_loop_court_orders number(15,2) := 0.00;
2553 l_total_reductions number(15,2) := 0.00;
2554 l_loop_counter smallint;
2555 l_previous_rubric varchar2(10);
2556 l_previous_process varchar2(20);
2557 l_this_process_type varchar2(80);
2558 l_base_units varchar2(80);
2559 l_description varchar2(200);
2560 l_previous_description varchar2(200);
2561 l_previous_class varchar2(20);
2562 l_previous_base number(15,2);
2563 l_sign_adjust_amount number(15,2);
2564 l_ee_context_order smallint; /* context order for ee values in rubric/process/base */
2565 l_append_dates varchar2(150); /* temp area for for constructing dd-mm - dd-mm */
2566 l_debug number;
2567 --
2568 l_previous_absence_id number;
2569 l_previous_pto_rubric number;
2570 l_substitute_rubric varchar2(30);
2571 l_printed_start_date varchar2(60);
2572 l_printed_end_date varchar2(60);
2573 --
2574 -----------------------------------------------------------------------------------------
2575 -- BEGIN LOAD_EE_RATE_GROUPED_RUNS
2576 -----------------------------------------------------------------------------------------
2577 BEGIN
2578
2579 hr_utility.set_location('Entering ' || l_proc, 5);
2580 for l_loop in 1..4 LOOP
2581 hr_utility.set_location('Major Loop ='||to_char(l_loop), 9);
2582 l_loop_counter := 0;
2583 l_ee_context_order := 0;
2584 l_previous_rubric := ' ';
2585 l_previous_process := ' ';
2586 l_previous_base := -1;
2587 l_previous_class := ' ';
2588 l_previous_description := ' ';
2589 --
2590 if l_loop = 1 THEN
2591 l_ee_classification1 := 'Earnings';
2592 l_ee_classification2 := 'Supplementary Earnings';
2593 l_ee_classification3 := 'Overtime';
2594 l_ee_classification4 := 'Earnings Adjustment';
2595 l_ee_classification5 := 'Payment for Absence';
2596 l_ee_classification6 := 'Benefits';
2597 l_context_prefix := 'EARNINGS';
2598 elsif l_loop = 2 THEN
2599 l_ee_classification1 := 'Reductions';
2600 l_ee_classification2 := null;
2601 l_ee_classification3 := null;
2602 l_ee_classification4 := null;
2603 l_ee_classification5 := null;
2604 l_ee_classification6 := null;
2605 l_context_prefix := 'EARNINGS_REDUCTIONS';
2606 elsif l_loop = 3 THEN
2607 l_ee_classification1 := 'Net Payments';
2608 l_ee_classification2 := null;
2609 l_ee_classification3 := null;
2610 l_ee_classification4 := null;
2611 l_ee_classification5 := null;
2612 l_ee_classification6 := null;
2613 l_context_prefix := 'NET_PAYMENTS';
2614 elsif l_loop = 4 THEN
2615 l_ee_classification1 := 'Court Orders';
2616 l_ee_classification2 := null;
2617 l_ee_classification3 := null;
2618 l_ee_classification4 := null;
2619 l_ee_classification5 := null;
2620 l_ee_classification6 := null;
2621 l_context_prefix := 'NET_PAYMENTS';
2622 end if;
2623 open csr_get_ee_rate_grouped(l_ee_classification1, l_ee_classification2
2624 ,l_ee_classification3, l_ee_classification4
2625 ,l_ee_classification5, l_ee_classification6
2626 ,p_archive_assignment_action_id
2627 ,g_us_name_base, g_fr_name_base
2628 ,g_us_name_rate, g_fr_name_rate
2629 ,g_us_name_pay_value, g_fr_name_pay_value
2630 ,g_us_name_start_date,g_fr_name_start_date
2631 ,g_fr_name_end_date, g_fr_name_end_date
2632 ,g_retro_tl);
2633 LOOP
2634 fetch csr_get_ee_rate_grouped INTO rec_results;
2635 EXIT WHEN csr_get_ee_rate_grouped%NOTFOUND;
2636 l_loop_counter := l_loop_counter + 1;
2637 l_append_dates := null;
2638 l_this_process_type := null;
2639 l_substitute_rubric := null;
2640
2641 rec_results.amount := nvl(rec_results.amount, 0) * rec_results.adjust_sign;
2642 if rec_results.base is not null then
2643 rec_results.base := rec_results.base * rec_results.adjust_sign;
2644 end if;
2645 --
2646 l_ee_context_order := l_ee_context_order + 1; /* each is on a new line */
2647 --
2648 -- use local variable l_description as may need to append to it for overtime.
2649 --
2650 l_description := substrb(rec_results.description, 1, 200);
2651 --
2652 -- Adjust the start and end dates to be 'dd-mm - dd-mm'
2653 --
2654 begin
2655 if rec_results.creator_type = 'EE' or rec_results.creator_type = 'RR' THEN
2656 l_printed_start_date := fnd_date.date_to_canonical(rec_results.retro_pro_start);
2657 l_printed_end_date := fnd_date.date_to_canonical(rec_results.retro_pro_end);
2658 else
2659 l_printed_start_date := rec_results.std_start_date;
2660 l_printed_end_date := rec_results.std_end_date;
2661 end if;
2662
2663 if l_printed_start_date is not null and l_loop <> 4 and rec_results.class <> 'Overtime' THEN
2664 l_append_dates := to_char(fnd_date.canonical_to_date(l_printed_start_date), ' dd-mm');
2665 end if;
2666 if l_printed_end_date is not null and l_loop <> 4 and rec_results.class <> 'Overtime' THEN
2667 l_append_dates := l_append_dates || to_char(fnd_date.canonical_to_date(l_printed_end_date), ' - dd-mm');
2668 end if;
2669 exception
2670 when others then null; /* fnd date may raise error */
2671 end;
2672 --
2673 -- Special procesing for overtime.
2674 --
2675 if rec_results.class = 'Overtime' THEN
2676 begin
2677 -- Adjust the rubric description to be Label @ factor eg 'overtime @ 125%'
2678 l_description := substrb(trim(rec_results.label || ' @ ' || rec_results.factor) || '%' , 1, 200);
2679 if rec_results.creator_type = 'EE' or rec_results.creator_type = 'RR' THEN
2680 l_description := substrb(l_description || ' '|| g_retro_tl, 1, 200);
2681 end if;
2682 --
2683 -- Adjust the overtime date to be displayable
2684 if rec_results.week_end_date is not null THEN
2685 l_append_dates := fnd_date.date_to_displaydate(fnd_date.canonical_to_date(rec_results.week_end_date));
2686 end if;
2687 exception
2688 when others then null; /* fnd date may raise error */
2689 end;
2690 end if;
2691 --
2692 -- Special processing for termination
2693 --
2694 if p_termination_reason is not null and rec_results.class = 'Earnings'
2695 and (rec_results.element = nvl(p_term_st_ele_id,-1)
2696 or
2697 rec_results.element = nvl(p_term_ex_ele_id,-1)
2698 ) THEN
2699 --
2700 -- Prefix the termination reason to the rubric
2701 --
2702 --hr_utility.trace('reason ' || p_termination_reason);
2703 --hr_utility.trace('descr ' || l_description);
2704 l_description := substrb(p_termination_reason || '-' || l_description, 1, 200);
2705 End If;
2706 --
2707 -- Only store the base units if the base is not null and not zero
2708 --
2709 if nvl(rec_results.base, 0) = 0 then
2710 l_base_units := null;
2711 else
2712 l_base_units := rec_results.base_units;
2713 end if;
2714 --
2715 -- Arcvive Process Type, only if it's not the latest process type from the latest run in this set
2716 -- do not archive if in the net pay section
2717 --
2718 if p_latest_process_type <> rec_results.process and l_loop <= 2 and rec_results.process is not null THEN
2719 BEGIN
2720 open csr_process_meaning(rec_results.process);
2721 fetch csr_process_meaning into l_this_process_type;
2722 close csr_process_meaning;
2723 EXCEPTION
2724 when others then null;
2725 END;
2726 else
2727 l_this_process_type := null;
2728 end if;
2729 If rec_results.class = 'Earnings Adjustment' and rec_results.absence_attendance_id is not null then
2730 --
2731 -- Store these values incase the next fetch is the corresponding payment
2732 --
2733 l_previous_absence_id := rec_results.absence_attendance_id;
2734 l_previous_pto_rubric := rec_results.rubric;
2735 ELSIF rec_results.class = 'Payment for Absence' and rec_results.absence_attendance_id is not null
2736 AND nvl(l_previous_absence_id,-1) = rec_results.absence_attendance_id THEN
2737 --
2738 -- Store this payment with the deduction rubric values
2739 --
2740 l_substitute_rubric := l_previous_pto_rubric;
2741 END IF;
2742 --
2743 -- Arcvive ee values
2744 -- only archive the pay_value and rubric and description for court orders
2745 -- and sum the pay_values until the end of fetch or a new rubric is fetched.
2746 --
2747 hr_utility.set_location('context='||l_context_prefix, 30);
2748 if l_loop <> 4 and rec_results.amount <> 0 then
2749 pay_action_information_api.create_action_information (
2750 p_action_information_id => l_action_info_id
2751 , p_action_context_id => p_archive_assignment_action_id
2752 , p_action_context_type => l_archive_type
2753 , p_object_version_number => l_ovn
2754 , p_tax_unit_id => p_establishment_id
2755 , p_action_information_category => l_context /* FR_SOE_ELEMENTS */
2756 , p_action_information1 => l_context_prefix /* EARNINGS... */
2757 , p_action_information2 => nvl(l_substitute_rubric, rec_results.rubric)
2758 , p_action_information3 => l_ee_context_order
2759 , p_action_information4 => rec_results.rubric
2760 , p_action_information5 => l_description
2761 , p_action_information6 => l_append_dates
2762 , p_action_information7 => l_this_process_type
2763 , p_action_information8 => l_base_units
2764 , p_action_information9 => rec_results.base
2765 , p_action_information10 => rec_results.rate
2766 , p_action_information11 => rec_results.amount);
2767 --
2768 end if;
2769 --
2770 -- If the previous fetch was a court order, and this is a new rubric,
2771 -- and it's not the first court order fetch, then archive the old CO values
2772 -- dont archive process type or dates, or base units
2773 --
2774 if l_loop = 4 and l_previous_rubric <> rec_results.rubric and l_previous_rubric <> ' '
2775 and l_total_loop_court_orders <> 0 THEN
2776 pay_action_information_api.create_action_information (
2777 p_action_information_id => l_action_info_id
2778 , p_action_context_id => p_archive_assignment_action_id
2779 , p_action_context_type => l_archive_type
2780 , p_object_version_number => l_ovn
2781 , p_tax_unit_id => p_establishment_id
2782 , p_action_information_category => l_context /* FR_SOE_ELEMENTS */
2783 , p_action_information1 => l_context_prefix
2784 , p_action_information2 => l_previous_rubric
2785 , p_action_information3 => l_ee_context_order
2786 , p_action_information4 => l_previous_rubric
2787 , p_action_information5 => l_previous_description
2788 , p_action_information11 => l_total_loop_court_orders * -1); /* store as negative */
2789 --
2790 /* this court order rubric finished, clear down for next one; */
2791 l_total_loop_court_orders := 0;
2792 end if;
2793 --
2794 -- Maintain the running totals
2795 --
2796 if l_loop = 1 then /* Earnings */
2797 l_total_gross_pay := l_total_gross_pay + rec_results.amount;
2798 elsif l_loop = 2 then /* Reductions */
2799 l_total_reductions := l_total_reductions + rec_results.amount;
2800 elsif l_loop = 3 then /* Net Payments */
2801 l_total_net_payments := l_total_net_payments + rec_results.amount;
2802 else /* Court Orders */
2803 l_total_court_orders := l_total_court_orders + rec_results.amount;
2804 l_total_loop_court_orders := l_total_loop_court_orders + rec_results.amount;
2805 --
2806 -- bug 2683309 only record non-null rubrics, as cursor will fetch
2807 -- 'Court Orders' with null pay values and descriptions
2808 --
2809 l_previous_rubric := nvl(rec_results.rubric, l_previous_rubric);
2810 l_previous_description := nvl(l_description, l_previous_description);
2811 l_append_dates := null;
2812 end if;
2813 --
2814 l_printed_start_date := null;
2815 l_printed_end_date := null;
2816
2817 --
2818 END LOOP; /* cursor loop */
2819 hr_utility.set_location('End of Prefix Loop ' || l_proc, 90);
2820 --
2821 -- Write any CO fetched on the very last fetch of loop = 4
2822 --
2823 if l_loop = 4 and l_total_loop_court_orders <> 0 then
2824 pay_action_information_api.create_action_information (
2825 p_action_information_id => l_action_info_id
2826 , p_action_context_id => p_archive_assignment_action_id
2827 , p_action_context_type => l_archive_type
2828 , p_object_version_number => l_ovn
2829 , p_tax_unit_id => p_establishment_id
2830 , p_action_information_category => l_context /* FR_SOE_ELEMENTS */
2831 , p_action_information1 => l_context_prefix
2832 , p_action_information2 => l_previous_rubric
2833 , p_action_information3 => l_ee_context_order
2834 , p_action_information4 => l_previous_rubric
2835 , p_action_information5 => l_previous_description
2836 , p_action_information11 => l_total_loop_court_orders * -1 ); /* store as negative */
2837 --
2838 end if;
2839 close csr_get_ee_rate_grouped;
2840 END LOOP; /* loop of statutory deductions */
2841 hr_utility.set_location('End of Major Loop ' || l_proc, 100);
2842 --
2843 -- pass out the totals needed in future calculations of totals
2844 --
2845 p_total_gross_pay := nvl(l_total_gross_pay, 0);
2846 p_reductions := nvl(l_total_reductions, 0);
2847 p_net_payments := nvl(l_total_net_payments, 0);
2848 p_court_orders := nvl(l_total_court_orders, 0);
2849 --
2850 hr_utility.set_location('Leaving ' || l_proc, 110);
2851 END load_ee_rate_grouped_runs;
2852 -------------------------------------------------------------------------------
2853 -- LOAD_DEDUCTIONS1
2854 -- DESCRIPTION : OLD
2855 -------------------------------------------------------------------------------
2856 procedure load_deductions1(
2857 p_archive_assignment_action_id in number
2858 ,p_assignment_id in number
2859 ,p_latest_process_type in varchar2
2860 ,p_total_deduct_ee out nocopy number
2861 ,p_total_deduct_er out nocopy number
2862 ,p_total_charge_ee out nocopy number
2863 ,p_total_charge_er out nocopy number
2864 ,p_establishment_id in number
2865 ,p_effective_date in date ) is
2866 --
2867 BEGIN
2868 null;
2869 END load_deductions1;
2870 ------------------------------------------------------------------------------
2871 -- WRITE_ARCHIVE
2872 -- DESCRIPTION : writes a new line to the archive only if an existing line to
2873 -- update is not found.
2874 ------------------------------------------------------------------------------
2875 procedure write_archive(
2876 p_action_context_id in number
2877 ,p_action_context_type in varchar2
2878 ,p_rubric in varchar2
2879 ,p_rubric_sort in number
2880 ,p_tax_unit_id in number
2881 ,p_context_prefix in varchar2
2882 ,p_action_information_category in varchar2
2883 ,p_action_information4 in varchar2 default null
2884 ,p_action_information5 in varchar2 default null
2885 ,p_action_information6 in varchar2 default null
2886 ,p_action_information7 in varchar2 default null
2887 ,p_action_information8 in varchar2 default null
2888 ,p_action_information9 in varchar2 default null
2889 ,p_action_information10 in varchar2 default null
2890 ,p_action_information11 in varchar2 default null
2891 ,p_action_information12 in varchar2 default null
2892 ,p_action_information13 in varchar2 default null ) is
2893 --
2894 cursor csr_find_row is
2895 select action_information_id, object_version_number
2896 from pay_action_information
2897 where action_context_id = p_action_context_id
2898 and action_context_type = p_action_context_type
2899 and action_information_category = p_action_information_category
2900 and action_information1 = p_context_prefix
2901 and action_information2 = p_rubric
2902 and action_information3 = to_char(p_rubric_sort);
2903
2904 l_action_info_id pay_action_information.action_information_id%TYPE;
2905 l_ovn pay_action_information.object_version_number%TYPE;
2906 --
2907 l_proc VARCHAR2(60):= g_package||' Write Archive ';
2908 --
2909 BEGIN
2910 hr_utility.set_location('Entering ' || l_proc, 10);
2911 open csr_find_row;
2912 fetch csr_find_row into l_action_info_id, l_ovn;
2913 if csr_find_row%NOTFOUND THEN
2914 pay_action_information_api.create_action_information (
2915 p_action_information_id => l_action_info_id
2916 , p_action_context_id => p_action_context_id
2917 , p_action_context_type => p_action_context_type
2918 , p_action_information1 => p_context_prefix
2919 , p_action_information2 => p_rubric
2920 , p_action_information3 => to_char(p_rubric_sort)
2921 , p_tax_unit_id => p_tax_unit_id
2922 , p_object_version_number => l_ovn
2923 , p_action_information_category => p_action_information_category
2924 , p_action_information4 => p_action_information4
2925 , p_action_information5 => p_action_information5
2926 , p_action_information6 => p_action_information6
2927 , p_action_information7 => p_action_information7
2928 , p_action_information8 => p_action_information8
2929 , p_action_information9 => p_action_information9
2930 , p_action_information10 => p_action_information10
2931 , p_action_information11 => p_action_information11
2932 , p_action_information12 => p_action_information12
2933 , p_action_information13 => p_action_information13);
2934 else
2935 pay_action_information_api.update_action_information (
2936 p_action_information_id => l_action_info_id
2937 -- , p_action_context_id => p_action_context_id
2938 -- , p_action_context_type => p_action_context_type
2939 , p_object_version_number => l_ovn
2940 , p_action_information1 => nvl(p_context_prefix, hr_api.g_varchar2)
2941 , p_action_information2 => nvl(p_rubric, hr_api.g_varchar2)
2942 , p_action_information3 => nvl(to_char(p_rubric_sort), hr_api.g_varchar2)
2943 -- , p_tax_unit_id => p_tax_unit_id
2944 -- , p_action_information_category => p_action_information_category
2945 , p_action_information4 => nvl(p_action_information4, hr_api.g_varchar2)
2946 , p_action_information5 => nvl(p_action_information5, hr_api.g_varchar2)
2947 , p_action_information6 => nvl(p_action_information6, hr_api.g_varchar2)
2948 , p_action_information7 => nvl(p_action_information7, hr_api.g_varchar2)
2949 , p_action_information8 => nvl(p_action_information8, hr_api.g_varchar2)
2950 , p_action_information9 => nvl(p_action_information9, hr_api.g_varchar2)
2951 , p_action_information10 => nvl(p_action_information10, hr_api.g_varchar2)
2952 , p_action_information11 => nvl(p_action_information11, hr_api.g_varchar2)
2953 , p_action_information12 => nvl(p_action_information12, hr_api.g_varchar2)
2954 , p_action_information13 => nvl(p_action_information13, hr_api.g_varchar2));
2955 end if;
2956 close csr_find_row;
2957 hr_utility.set_location('Leaving ' || l_proc, 100);
2958 EXCEPTION
2959 WHEN OTHERS then
2960 raise; /* error as no write to archive */
2961 END write_archive;
2962 ------------------------------------------------------------------------------
2963 -- LOAD_RATE_GROUPED_RUNS_RUNS
2964 -- DESCRIPTION : fetches and archives elements grouped by rate.
2965 -- these are net ee deductions and ER luncheon vouchers
2966 ------------------------------------------------------------------------------
2967 procedure load_rate_grouped_runs(
2968 p_archive_assignment_action_id in number
2969 ,p_assignment_id in number
2970 ,p_latest_process_type in varchar2
2971 ,p_total_ee_net_deductions out nocopy number
2972 ,p_establishment_id in number
2973 ,p_total_gross_pay in out nocopy number
2974 ,p_effective_date in date ) is
2975 --
2976 -- csr_get_results
2977 -- Select all the entries in parameterized element classifications for this assignment
2978 -- action.
2979 -- This code will be designmed so that is can handle other classifcations that also need
2980 -- to be grouped.
2981 -- For Luncheon vourcers, we are expecting er to be an indirect of ee, but cannot enforce
2982 -- this. So source_id is ordered by, so that in the normal case grouped
2983 -- ee lines will appear with their equivalent grouped er lines.
2984 -- Check this for retropay entries.
2985 -- Modified for 3683906, commented out under bug 4778143
2986 -- l_sec_grp_id_user_ele_grp number;
2987 -- l_sec_grp_id_base_unit number;
2988 --
2989 cursor csr_get_results (p_ee_classification number , p_er_classification number
2990 ,p_archive_assignment_action_id number
2991 ,p_us_base_name varchar2 , p_fr_base_name varchar2
2992 ,p_us_rate_name varchar2 , p_fr_rate_name varchar2
2993 ,p_us_pay_value_name varchar2 , p_fr_pay_value_name varchar2) is
2994 select class
2995 ,element_type_id Element_type
2996 ,sum(fnd_number.canonical_to_number(base_value)) Base
2997 ,rate_value Rate
2998 ,sum(fnd_number.canonical_to_number(pay_value)) Amount
2999 ,rubric Rubric
3000 ,description Description
3001 ,sum(source_id) Source_id
3002 ,base_units
3003 from (
3004 select prr.run_result_id
3005 ,decode(pet.classification_id
3006 ,p_ee_classification, 'EE', 'ER') Class
3007 ,pet.element_type_id element_type_id
3008 ,max(decode(piv.name
3009 ,p_fr_base_name,prrv.result_value
3010 ,p_us_base_name,prrv.result_value)) Base_value
3011 ,max(decode(piv.name
3012 ,p_fr_rate_name,prrv.result_value
3013 ,p_us_rate_name,prrv.result_value)) Rate_value
3014 ,max(decode(piv.name
3015 ,p_fr_pay_value_name,prrv.result_value
3016 ,p_us_pay_value_name,prrv.result_value)) pay_value
3017 ,prr.source_id Source_id
3018 ,nvl(user_rubric.tag,seed_rubric.tag) Rubric
3019 ,nvl(user_rubric.meaning,seed_rubric.meaning) Description
3020 ,base_units.meaning BASE_UNITS
3021 from pay_element_types_f pet
3022 ,pay_run_results prr
3023 ,pay_input_values_f piv
3024 ,pay_run_result_values prrv
3025 ,fnd_lookup_values user_rubric
3026 ,fnd_lookup_values seed_rubric
3027 ,fnd_lookup_values base_units
3028 ,pay_action_interlocks arc_lock
3029 ,pay_action_interlocks pre_lock
3030 ,pay_assignment_actions run_assact
3031 ,pay_payroll_actions run_payact
3032 where pet.classification_id in (p_ee_classification, p_er_classification)
3033 and (pet.legislation_code = 'FR' or pet.legislation_code is null)
3034 and pet.element_type_id = prr.element_Type_id
3035 and p_effective_date between pet.effective_start_date
3036 and pet.effective_end_date
3037 and prr.assignment_action_id = run_assact.assignment_action_id
3038 and prrv.run_result_id = prr.run_result_id
3039 and piv.element_type_id = pet.element_Type_id
3040 and p_effective_date between piv.effective_start_date
3041 and piv.effective_end_date
3042 and piv.input_value_id = prrv.input_value_id
3043 and piv.name in (p_us_pay_value_name, p_fr_pay_value_name
3044 ,p_us_base_name, p_fr_base_name
3045 ,p_us_rate_name, p_fr_rate_name)
3046 and arc_lock.locking_action_id = p_archive_assignment_action_id
3047 and arc_lock.locked_action_id = pre_lock.locking_action_id
3048 and pre_lock.locked_action_id = run_assact.assignment_action_id
3049 and run_payact.payroll_action_id = run_assact.payroll_action_id
3050 and run_payact.action_type in ('Q', 'R')
3051 and user_rubric.lookup_code(+) = pet.element_information1
3052 and user_rubric.lookup_type(+) = 'FR_USER_ELEMENT_GROUP'
3053 and user_rubric.LANGUAGE(+) = USERENV('LANG')
3054 and user_rubric.security_group_id(+) = g_sec_grp_id_user_element_grp
3055 and user_rubric.VIEW_APPLICATION_ID(+) = 3
3056 and seed_rubric.lookup_code(+) = pet.element_information1
3057 and seed_rubric.lookup_type(+) = 'FR_ELEMENT_GROUP'
3058 and seed_rubric.LANGUAGE(+) = USERENV('LANG')
3059 and seed_rubric.security_group_id(+) = g_sec_grp_id_element_grp
3060 and seed_rubric.VIEW_APPLICATION_ID(+) = 3
3061 and base_units.lookup_code(+) = pet.element_information2
3062 and base_units.lookup_type(+) = 'FR_BASE_UNIT'
3063 and base_units.LANGUAGE(+) = USERENV('LANG')
3064 and base_units.security_group_id(+) = g_sec_grp_id_base_unit
3065 and base_units.VIEW_APPLICATION_ID(+) = 3
3066 group by
3067 decode(pet.classification_id, p_ee_classification, 'EE', 'ER')
3068 ,pet.element_type_id
3069 ,prr.source_id
3070 ,nvl(user_rubric.tag,seed_rubric.tag)
3071 ,nvl(user_rubric.meaning,seed_rubric.meaning)
3072 ,base_units.meaning
3073 ,prr.run_result_id) result_rollup
3074 group by
3075 rubric
3076 ,description
3077 ,class
3078 ,element_type_id
3079 ,rate_value
3080 ,base_units
3081 order by rubric
3082 ,source_id;
3083 --
3084 --
3085 rec_results csr_get_results%ROWTYPE;
3086 l_proc VARCHAR2(60):= g_package||' Load Rate Grouped ';
3087 --
3088 l_context_prefix varchar2(30);
3089 l_context varchar2(30) := 'FR_SOE_ELEMENTS';
3090 l_total_ee_net_deductions number(15,2) := 0;
3091 l_previous_base number(15,2);
3092 --
3093 l_loop_counter smallint;
3094 l_previous_rubric varchar2(10);
3095 l_ee_context_order smallint; /* context order for ee values in rubric/source */
3096 l_er_context_order smallint; /* context order for er values in rubric/source */
3097 l_archive_type varchar2(3) := 'AAP';
3098 --
3099 -----------------------------------------------------------------------------
3100 -- BEGIN LOAD_RATE_GROUPED_RUNS net payments, ER LV charges
3101 -----------------------------------------------------------------------------
3102 BEGIN
3103 hr_utility.set_location('Entering ' || l_proc, 5);
3104 -- Modified for bug 3683906, commented out under bug 4778143
3105 -- l_sec_grp_id_user_ele_grp := g_sec_grp_id_user_element_grp;
3106 -- l_sec_grp_id_base_unit := g_sec_grp_id_base_unit;
3107 --
3108 l_loop_counter := 0;
3109 l_ee_context_order := 0;
3110 l_er_context_order := 0;
3111 l_previous_rubric := ' ';
3112 l_previous_base := 0;
3113 --
3114 l_context_prefix := 'NET_PAYMENTS';
3115 open csr_get_results (g_ele_class_Net_EE_Deductions
3116 ,g_ele_class_ER_LV_Charges
3117 ,p_archive_assignment_action_id
3118 ,g_us_name_base, g_fr_name_base
3119 ,g_us_name_rate, g_fr_name_rate
3120 ,g_us_name_pay_value, g_fr_name_pay_value);
3121 LOOP
3122 fetch csr_get_results INTO rec_results;
3123 EXIT WHEN csr_get_results%NOTFOUND;
3124 l_loop_counter := l_loop_counter + 1;
3125 rec_results.amount := nvl(rec_results.amount, 0);
3126 --
3127 -- Manage the context order.
3128 --
3129 if ( l_previous_rubric <> nvl(rec_results.rubric, ' ')
3130 or(l_previous_base <> nvl(rec_results.base, 0))
3131 ) THEN
3132 l_ee_context_order := l_loop_counter; /* The bases MAY be different for the same rubric, if they are, */
3133 l_er_context_order := l_loop_counter; /* report them on separate lines - should never happen */
3134 end if;
3135 --
3136 -- Arcvive ee values
3137 --
3138 if rec_results.class = 'EE' then
3139 l_ee_context_order := l_ee_context_order + 1;
3140 pay_fr_arc_pkg.write_archive(p_action_context_id => p_archive_assignment_action_id
3141 ,p_action_context_type => l_archive_type
3142 ,p_context_prefix => l_context_prefix
3143 ,p_rubric => rec_results.rubric
3144 ,p_rubric_sort => l_ee_context_order
3145 ,p_tax_unit_id => p_establishment_id
3146 ,p_action_information_category => l_context
3147 ,p_action_information4 => rec_results.rubric
3148 ,p_action_information5 => rec_results.description
3149 ,p_action_information8 => rec_results.base_units
3150 ,p_action_information9 => rec_results.base
3151 ,p_action_information10 => rec_results.rate
3152 ,p_action_information11 => rec_results.amount * -1);
3153 else
3154 --
3155 -- Arcvive er values
3156 --
3157 l_er_context_order := l_er_context_order + 1;
3158 pay_fr_arc_pkg.write_archive(p_action_context_id => p_archive_assignment_action_id
3159 ,p_action_context_type => l_archive_type
3160 ,p_context_prefix => l_context_prefix
3161 ,p_rubric => rec_results.rubric
3162 ,p_rubric_sort => l_er_context_order
3163 ,p_tax_unit_id => p_establishment_id
3164 ,p_action_information_category => l_context
3165 ,p_action_information4 => rec_results.rubric
3166 ,p_action_information5 => rec_results.description
3167 ,p_action_information8 => rec_results.base_units
3168 ,p_action_information9 => rec_results.base
3169 ,p_action_information12 => rec_results.rate
3170 ,p_action_information13 => rec_results.amount);
3171 end if;
3172 --
3173 -- Maintain the running totals
3174 --
3175 if rec_results.class = 'EE' then
3176 l_total_ee_net_deductions := l_total_ee_net_deductions + rec_results.amount;
3177 end if;
3178 l_previous_rubric := nvl(rec_results.rubric, '.');
3179 l_previous_base := nvl(rec_results.base, 0);
3180 END LOOP; -- cursor loop
3181 hr_utility.set_location('End of Prefix Loop ' || l_proc, 90);
3182 close csr_get_results;
3183 --
3184 -- Pass back the return variable
3185 --
3186 p_total_ee_net_deductions := l_total_ee_net_deductions;
3187 --
3188 hr_utility.set_location('Leaving ' || l_proc, 110);
3189 END load_rate_grouped_runs;
3190 -------------------------------------------------------------------------------
3191 -- SUPPORT CODE
3192 -------------------------------------------------------------------------------
3193 -- GET_PARAMETER used in sql to decode legislative parameters
3194 -- copied from uk code.
3195 -------------------------------------------------------------------------------
3196 function get_parameter(
3197 p_parameter_string in varchar2
3198 ,p_token in varchar2
3199 ,p_segment_number in number default null ) RETURN varchar2
3200 IS
3201 l_parameter pay_payroll_actions.legislative_parameters%TYPE:=NULL;
3202 l_start_pos NUMBER;
3203 l_delimiter varchar2(1):=' ';
3204 l_proc VARCHAR2(40):= g_package||' get parameter ';
3205 BEGIN
3206 hr_utility.set_location('Entering ' || l_proc, 20);
3207 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
3208 IF l_start_pos = 0 THEN
3209 l_delimiter := '|';
3210 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
3211 end if;
3212 IF l_start_pos <> 0 THEN
3213 l_start_pos := l_start_pos + length(p_token||'=');
3214 l_parameter := substr(p_parameter_string,
3215 l_start_pos,
3216 instr(p_parameter_string||' ',
3217 l_delimiter,l_start_pos)
3218 - l_start_pos);
3219 IF p_segment_number IS NOT NULL THEN
3220 l_parameter := ':'||l_parameter||':';
3221 l_parameter := substr(l_parameter,
3222 instr(l_parameter,':',1,p_segment_number)+1,
3223 instr(l_parameter,':',1,p_segment_number+1) -1
3224 - instr(l_parameter,':',1,p_segment_number));
3225 END IF;
3226 END IF;
3227 hr_utility.set_location('Leaving ' || l_proc, 100);
3228 RETURN l_parameter;
3229 END get_parameter;
3230 -------------------------------------------------------------------------------
3231 -- GET_ALL_PARAMETERS gets all parameters for the payroll action
3232 -------------------------------------------------------------------------------
3233 procedure get_all_parameters (
3234 p_payroll_action_id in number
3235 ,p_payroll_id out nocopy number
3236 ,p_assignment_id out nocopy number
3237 ,p_assignment_set_id out nocopy number
3238 ,p_business_group_id out nocopy number
3239 ,p_start_date out nocopy date
3240 ,p_effective_date out nocopy date) is
3241 --
3242 cursor csr_parameter_info(p_payroll_action_id NUMBER) IS
3243 SELECT pay_fr_arc_pkg.get_parameter(legislative_parameters, 'PAYROLL_ID')
3244 ,pay_fr_arc_pkg.get_parameter(legislative_parameters, 'ASSIGNMENT_ID')
3245 ,pay_fr_arc_pkg.get_parameter(legislative_parameters, 'ASSIGNMENT_SET')
3246 ,business_group_id
3247 ,start_date
3248 ,effective_date
3249 FROM pay_payroll_actions
3250 WHERE payroll_action_id = p_payroll_action_id;
3251 l_proc VARCHAR2(40):= g_package||' get_all_parameters ';
3252
3253 BEGIN
3254 hr_utility.set_location('Entering ' || l_proc, 20);
3255 open csr_parameter_info (p_payroll_action_id);
3256 fetch csr_parameter_info into p_payroll_id, p_assignment_id, p_assignment_set_id
3257 ,p_business_group_id, p_start_date, p_effective_date;
3258 close csr_parameter_info;
3259
3260 hr_utility.set_location('Leaving ' || l_proc, 100);
3261 END get_all_parameters;
3262 -------------------------------------------------------------------------------
3263 -- GET_BALANCE_ID
3264 -- DESCRIPTION : gets a defined balance id
3265 -------------------------------------------------------------------------------
3266 function get_balance_id (
3267 p_balance_name in varchar2
3268 ,p_dimension in varchar2) RETURN number is
3269 --
3270 l_defined_balance_id pay_defined_balances.defined_balance_id%TYPE;
3271 --
3272 l_proc VARCHAR2(40):= g_package||' get_balance_id ';
3273 --
3274 BEGIN
3275 --
3276 hr_utility.set_location('Entering ' || l_proc, 10);
3277 SELECT defined_balance_id
3278 INTO l_defined_balance_id
3279 FROM pay_defined_balances db
3280 ,pay_balance_types b
3281 ,pay_balance_dimensions d
3282 WHERE b.balance_name = p_balance_name
3283 AND d.dimension_name = p_dimension
3284 AND db.balance_type_id = b.balance_type_id
3285 AND db.balance_dimension_id = d.balance_dimension_id
3286 AND d.legislation_code = 'FR';
3287 hr_utility.set_location(' Leaving ' || l_proc, 100);
3288 return l_defined_balance_id;
3289 EXCEPTION
3290 when NO_DATA_FOUND then
3291 hr_utility.set_location('DEV ERROR Balance Name not found ' || p_balance_name, 20);
3292 raise;
3293 when TOO_MANY_ROWS then
3294 hr_utility.set_location('DEV ERROR Balance Name ambiguous ' || p_balance_name, 20);
3295 raise;
3296 end get_balance_id;
3297 -------------------------------------------------------------------------------
3298 -- DEINITIALIZE
3299 -- DESCRIPTION : Called once per payroll action; to load all org details
3300 -------------------------------------------------------------------------------
3301 procedure deinitialize(
3302 p_payroll_action_id in number) is
3303 --
3304 cursor csr_check_archive_org_address (p_payroll_action_id number) is
3305 select action_information4 name
3306 ,ppa.payroll_id payroll_id
3307 from pay_action_information pai
3308 ,pay_payroll_actions ppa
3309 where pai.action_information_category in('FR_SOE_ESTAB_INFORMATION', 'FR_SOE_COMPANY_DETAILS')
3310 and pai.action_information2 is null /* address id */
3311 and pai.action_context_id = p_payroll_action_id
3312 and pai.action_context_id = ppa.payroll_Action_id
3313 and pai.action_context_type = 'PA';
3314 --
3315 l_proc VARCHAR2(40) := g_package||' deinitialize ';
3316 l_error boolean := FALSE;
3317 l_message varchar2(240);
3318 --
3319 BEGIN
3320 --
3321 if g_payroll_action_id is null
3322 or g_payroll_action_id <> p_payroll_action_id
3323 then
3324 pay_fr_arc_pkg.get_all_parameters (
3325 p_payroll_action_id => p_payroll_action_id
3326 ,p_payroll_id => g_param_payroll_id
3327 ,p_assignment_id => g_param_assignment_id
3328 ,p_assignment_set_id => g_param_assignment_set_id
3329 ,p_business_Group_id => g_param_business_group_id
3330 ,p_start_date => g_param_start_date
3331 ,p_effective_date => g_param_effective_date);
3332 g_payroll_action_id := p_payroll_action_id;
3333 end if;
3334 --
3335 --
3336 -- Get the company addresses and establishment addresses and
3337 -- company and establishment details
3338 --
3339 hr_utility.set_location('Step ' || l_proc,30);
3340 pay_fr_arc_pkg.load_organization_details(
3341 p_payroll_action_id => p_payroll_action_id
3342 ,p_business_Group_id => g_param_business_group_id
3343 ,p_payroll_id => g_param_payroll_id
3344 ,p_assignment_id => g_param_assignment_id
3345 ,p_assignment_set_id => g_param_assignment_set_id
3346 ,p_effective_date => g_param_effective_date
3347 ,p_start_date => g_param_start_date);
3348 --
3349 -- Error if any company or establishment
3350 -- addresses are missing.
3351 --
3352 hr_utility.set_location('Step ' || l_proc,30);
3353 FOR missing_address in csr_check_archive_org_address(p_payroll_action_id)
3354 LOOP
3355 hr_utility.set_message(801, 'PAY_74979_INCOMPLETE_ADDRESS');
3356 hr_utility.set_message_token(801,'ORGANIZATION',missing_address.name);
3357 l_message := substr(hr_utility.get_message,1,240);
3358 l_error := TRUE;
3359 END LOOP;
3360 IF l_error = TRUE THEN
3361 fnd_file.put_line (fnd_file.LOG, l_message);
3362 hr_utility.raise_error;
3363 END IF;
3364 hr_utility.set_location('Leaving ' || l_proc, 100);
3365 end deinitialize;
3366 -------------------------------------------------------------------------------
3367 -- LOAD_DEDUCTIONS
3368 -- DESCRIPTION : New
3369 -------------------------------------------------------------------------------
3370 procedure load_deductions(
3371 p_archive_assignment_action_id in number
3372 ,p_assignment_id in number
3373 ,p_latest_process_type in varchar2
3374 ,p_total_deduct_ee out nocopy number
3375 ,p_total_deduct_er out nocopy number
3376 ,p_total_charge_ee out nocopy number
3377 ,p_total_charge_er out nocopy number
3378 ,p_establishment_id in number
3379 ,p_effective_date in date ) is
3380 --
3381 /* Bulk fetches into table of records not supported in 8.1.7 */
3382 TYPE t_char_tbl is TABLE of varchar2(2000) INDEX by BINARY_INTEGER;
3383 TYPE t_date_tbl is TABLE of date INDEX by BINARY_INTEGER;
3384 TYPE t_num_tbl is TABLE of number INDEX by BINARY_INTEGER;
3385 TYPE t_binint_tbl is TABLE of BINARY_INTEGER INDEX by BINARY_INTEGER;
3386 tbl_tax_unit_id t_num_tbl;
3387 tbl_process_type t_char_tbl;
3388 tbl_ee_rate t_num_tbl;
3389 tbl_er_rate t_num_tbl;
3390 tbl_ee_amount t_num_tbl;
3391 tbl_er_amount t_num_tbl;
3392 tbl_cu_id t_num_tbl;
3393 tbl_EE_ER t_char_tbl;
3394 tbl_cxt_prefix t_char_tbl;
3395 tbl_action_id t_num_tbl;
3396 tbl_group_code t_char_tbl;
3397 tbl_row_base t_num_tbl;
3398 tbl_base t_num_tbl;
3399 tbl_start_date t_date_tbl;
3400 tbl_end_date t_date_tbl;
3401 tbl_pos_idx t_binint_tbl;
3402 tbl_retrieval_list pay_balance_pkg.t_balance_value_tab;
3403 l_pos_offset BINARY_INTEGER;
3404 l_grouped_rate_ptr BINARY_INTEGER;
3405 l_action_ptr BINARY_INTEGER;
3406 l_current_ptr BINARY_INTEGER;
3407 --
3408 -- Modified for bug 3683906, commented out under bug 4778143
3409 -- l_sec_grp_id_user_ele_grp number;
3410 -- l_sec_grp_id_ele_grp number;
3411 --
3412 l_proc VARCHAR2(60):= g_package||' Load Deductions ';
3413 l_this_process_type fnd_lookup_values.meaning%TYPE;
3414 l_proc_type fnd_lookup_values.lookup_code%TYPE;
3415 l_proc_type_meaning fnd_lookup_values.meaning%TYPE;
3416 l_total_ee_deductions number(15,2) := 0.00;
3417 l_total_er_deductions number(15,2) := 0.00;
3418 l_total_ee_csg number(15,2) := 0.00;
3419 l_context varchar2(20) := 'FR_SOE_ELEMENTS';
3420 l_action_info_id pay_action_information.action_information_id%TYPE;
3421 l_ovn pay_action_information.object_version_number%TYPE;
3422 l_archive_type varchar2(3) := 'AAP';
3423 l_group_code fnd_lookup_values.lookup_code%TYPE;
3424 l_def_bal_id pay_defined_balances.defined_balance_id%TYPE;
3425 l_rubric fnd_lookup_values.tag%TYPE;
3426 l_description fnd_lookup_values.meaning%TYPE;
3427 l_retro_tl fnd_lookup_values.meaning%TYPE;
3428 --
3429 l_append_dates varchar2(150); /* temp area for for constructing dd-mm - dd-mm */
3430 --
3431 cursor csr_get_run_bals is
3432 select rb.tax_unit_id /* estab */
3433 , rb.source_text process_type
3434 , rb.source_number rate
3435 , rb.source_id cu_id
3436 , decode(b.balance_category_id
3437 ,g_Stat_ER_Charges ,'ER'
3438 ,g_Conv_ER_Charges ,'ER'
3439 ,g_Rebates ,'ER'
3440 ,'EE') EE_ER
3441 , decode(b.balance_category_id
3442 ,g_Rebates ,'CONTRIBUTIONS_REBATE'
3443 ,g_Income_Tax_Excess ,'CONTRIBUTIONS_TAX'
3444 ,g_CSG_non_Deductible ,'CONTRIBUTIONS_CSG'
3445 ,'CONTRIBUTIONS') balcat
3446 , db2.defined_balance_id defbal
3447 , max(aa.assignment_action_id) assignment_action_id
3448 , nvl(fcu.group_code, pet.element_information1) element_group
3449 from pay_action_interlocks arclck
3450 , pay_action_interlocks prelck
3451 , pay_balance_types b
3452 , pay_run_balances rb
3453 , pay_defined_balances db
3454 , pay_payroll_actions pa
3455 , pay_assignment_actions aa
3456 , pay_defined_balances db2
3457 , pay_fr_contribution_usages fcu
3458 , pay_input_values_f piv
3459 , pay_element_types_f pet
3460 where arclck.locking_action_id = p_archive_assignment_action_id
3461 and prelck.locking_action_id = arclck.locked_action_id
3462 and aa.assignment_action_id = prelck.locked_action_id
3463 and b.balance_category_id in (g_Income_Tax_Excess
3464 ,g_Stat_EE_Deductions
3465 ,g_Stat_ER_Charges
3466 ,g_Conv_ER_Charges
3467 ,g_Conv_EE_Deductions
3468 ,g_Rebates
3469 ,g_CSG_non_Deductible)
3470 and db.balance_type_id = b.balance_type_id
3471 and db.balance_dimension_id in (g_asg_et_pr_ra_cu_run
3472 ,g_asg_et_pr_cu_run
3473 ,g_asg_run)
3474 and (db.balance_dimension_id <> g_asg_run or not exists (select 1
3475 from pay_defined_balances db1
3476 where db1.balance_type_id = db.balance_type_id
3477 and db1.balance_dimension_id in (g_asg_et_pr_ra_cu_run
3478 ,g_asg_et_pr_cu_run)))
3479 and rb.defined_balance_id = db.defined_balance_id
3480 and ((db.business_group_id is null and db.legislation_code = 'FR') or
3481 (db.business_group_id = g_param_business_group_id))
3482 and rb.assignment_action_id = aa.assignment_action_id
3483 and pa.action_type in ('Q','R')
3484 and pa.payroll_action_id = aa.payroll_action_id
3485 and aa.run_type_id is not null
3486 and fcu.contribution_usage_id(+) = rb.source_id
3487 and b.input_value_id = piv.input_value_id(+)
3488 and p_effective_Date between piv.effective_start_date(+)
3489 and piv.effective_end_date(+)
3490 and piv.element_type_id = pet.element_type_id(+)
3491 and p_effective_Date between pet.effective_start_date(+)
3492 and pet.effective_end_date(+)
3493 and db2.balance_type_id = b.balance_type_id
3494 and db2.balance_dimension_id = decode(db.balance_dimension_id
3495 ,g_asg_et_pr_ra_cu_run
3496 ,g_asg_et_pr_ra_cu_pro_run
3497 ,g_asg_et_pr_cu_run
3498 ,g_asg_et_pr_cu_pro_run
3499 ,g_asg_run
3500 ,g_asg_pro_run)
3501 and (db2.business_group_id = g_param_business_group_id or
3502 (db2.business_group_id is null and db2.legislation_code = 'FR'))
3503 group by aa.source_action_id
3504 , nvl(fcu.group_code, pet.element_information1)
3505 , rb.source_text
3506 , rb.tax_unit_id
3507 , rb.source_number /* rate */
3508 , rb.source_id /* cu_id */
3509 , decode(b.balance_category_id
3510 ,g_Stat_ER_Charges ,'ER'
3511 ,g_Conv_ER_Charges ,'ER'
3512 ,g_Rebates ,'ER'
3513 ,'EE')
3514 , decode(b.balance_category_id
3515 ,g_Rebates ,'CONTRIBUTIONS_REBATE'
3516 ,g_Income_Tax_Excess ,'CONTRIBUTIONS_TAX'
3517 ,g_CSG_non_Deductible ,'CONTRIBUTIONS_CSG'
3518 ,'CONTRIBUTIONS')
3519 , db2.defined_balance_id
3520 order by max(aa.assignment_action_id)
3521 , nvl(fcu.group_code, pet.element_information1)
3522 , rb.source_text
3523 , decode(b.balance_category_id
3524 ,g_Rebates ,'CONTRIBUTIONS_REBATE'
3525 ,g_Income_Tax_Excess ,'CONTRIBUTIONS_TAX'
3526 ,g_CSG_non_Deductible ,'CONTRIBUTIONS_CSG'
3527 ,'CONTRIBUTIONS');
3528 -- Above order by is important for subsequent looping/grouping
3529 --
3530 cursor csr_get_retros is
3531 select
3532 pcu.group_code group_code
3533 , fnd_number.canonical_to_number(result.base_value) base_value
3534 , sum(decode(result.EE_ER
3535 ,'EE',fnd_number.canonical_to_number(result.rate))) ee_rate
3536 , sum(decode(result.EE_ER
3537 ,'EE',fnd_number.canonical_to_number(result.amount))) ee_amount
3538 , sum(decode(result.EE_ER
3539 ,'ER',fnd_number.canonical_to_number(result.rate))) er_rate
3540 , sum(decode(result.EE_ER
3541 ,'ER',fnd_number.canonical_to_number(result.amount))) er_amount
3542 , result.process_type process
3543 , elecls
3544 , ptp_start_date
3545 , ptp_end_date
3546 from pay_fr_contribution_usages pcu,
3547 (
3548 select
3549 max(decode(piv.name,'Process_Type',prrv.result_value)) process_type
3550 ,max(decode(piv.name,g_fr_name_base,prrv.result_value,
3551 g_us_name_base,prrv.result_value)) base_value
3552 ,max(decode(piv.name,g_fr_name_rate,prrv.result_value,
3553 g_us_name_rate,prrv.result_value)) rate
3554 ,max(decode(piv.name,g_fr_name_pay_value,prrv.result_value,
3555 g_us_name_pay_value,prrv.result_value)) amount
3556 ,max(decode(piv.name,'Contribution_Usage_ID',prrv.result_value)) cu_id
3557 ,prrv.run_result_id
3558 ,decode(pet.classification_id
3559 ,g_ele_class_Stat_EE_Deductions ,'EE'
3560 ,g_ele_class_Stat_ER_Charges ,'ER'
3561 ,g_ele_class_Conv_EE_Deductions ,'EE'
3562 ,g_ele_class_Conv_ER_Charges ,'ER'
3563 ,g_ele_class_CSG_non_Deductible ,'EE'
3564 ,g_ele_class_Rebates ,'ER'
3565 ,g_ele_class_Income_Tax_Excess ,'EE') EE_ER
3566 ,decode(pet.classification_id
3567 ,g_ele_class_Stat_EE_Deductions ,'CONTRIBUTIONS'
3568 ,g_ele_class_Stat_ER_Charges ,'CONTRIBUTIONS'
3569 ,g_ele_class_Conv_EE_Deductions ,'CONTRIBUTIONS'
3570 ,g_ele_class_Conv_ER_Charges ,'CONTRIBUTIONS'
3571 ,g_ele_class_CSG_non_Deductible ,'CONTRIBUTIONS_CSG'
3572 ,g_ele_class_Rebates ,'CONTRIBUTIONS_REBATE'
3573 ,g_ele_class_Income_Tax_Excess ,'CONTRIBUTIONS_TAX') elecls
3574 ,ptp_date.start_date ptp_start_date
3575 ,ptp_date.end_date ptp_end_date
3576 from pay_run_result_values prrv
3577 ,pay_run_results prr
3578 ,pay_element_types_f pet
3579 ,pay_input_values_f_tl piv
3580 ,pay_payroll_actions run_payact
3581 ,pay_assignment_actions run_assact
3582 ,pay_action_interlocks arc_lock
3583 ,pay_action_interlocks pre_lock
3584 ,pay_entry_process_details epd
3585 ,per_time_periods ptp_date
3586 ,pay_payroll_actions ppa_date
3587 ,pay_assignment_actions paa_date
3588 where epd.element_entry_id = prr.element_entry_id
3589 and epd.retro_component_id is not null
3590 and prrv.run_result_id = prr.run_result_id
3591 and prr.element_type_id = pet.element_type_id
3592 and run_payact.date_earned between pet.effective_start_date
3593 and pet.effective_end_date
3594 and pet.classification_id in (g_ele_class_Conv_EE_Deductions
3595 ,g_ele_class_Conv_ER_Charges
3596 ,g_ele_class_Stat_EE_Deductions
3597 ,g_ele_class_Stat_ER_Charges
3598 ,g_ele_class_CSG_non_Deductible
3599 ,g_ele_class_Rebates
3600 ,g_ele_class_Income_Tax_Excess)
3601 and piv.input_value_id = prrv.input_value_id
3602 and piv.language = userenv('lang')
3603 and piv.name in ( g_us_name_pay_value, g_fr_name_pay_value
3604 ,g_us_name_base, g_fr_name_base
3605 ,g_us_name_rate, g_fr_name_rate
3606 ,'Contribution_Usage_ID', 'Process_Type')
3607 and prr.assignment_action_id = run_assact.assignment_action_id
3608 and arc_lock.locking_action_id = p_archive_assignment_action_id
3609 and arc_lock.locked_action_id = pre_lock.locking_action_id
3610 and pre_lock.locked_action_id = run_assact.assignment_action_id
3611 and run_payact.payroll_action_id = run_assact.payroll_action_id
3612 and run_payact.action_type in ('Q', 'R')
3613 and prrv.result_value is not null
3614 and epd.source_asg_action_id = paa_date.assignment_action_id
3615 and ppa_date.payroll_action_id = paa_date.payroll_action_id
3616 and ptp_date.time_period_id = ppa_date.time_period_id
3617 group by
3618 prrv.run_result_id,
3619 decode(pet.classification_id
3620 ,g_ele_class_Stat_EE_Deductions ,'EE'
3621 ,g_ele_class_Stat_ER_Charges ,'ER'
3622 ,g_ele_class_Conv_EE_Deductions ,'EE'
3623 ,g_ele_class_Conv_ER_Charges ,'ER'
3624 ,g_ele_class_CSG_non_Deductible ,'EE'
3625 ,g_ele_class_Rebates ,'ER'
3626 ,g_ele_class_Income_Tax_Excess ,'EE'),
3627 decode(pet.classification_id
3628 ,g_ele_class_Stat_EE_Deductions ,'CONTRIBUTIONS'
3629 ,g_ele_class_Stat_ER_Charges ,'CONTRIBUTIONS'
3630 ,g_ele_class_Conv_EE_Deductions ,'CONTRIBUTIONS'
3631 ,g_ele_class_Conv_ER_Charges ,'CONTRIBUTIONS'
3632 ,g_ele_class_CSG_non_Deductible ,'CONTRIBUTIONS_CSG'
3633 ,g_ele_class_Rebates ,'CONTRIBUTIONS_REBATE'
3634 ,g_ele_class_Income_Tax_Excess ,'CONTRIBUTIONS_TAX'),
3635 ptp_date.start_date,
3636 ptp_date.end_date
3637 ) result
3638 where pcu.contribution_usage_id = result.cu_id
3639 group by pcu.group_code
3640 , result.process_type
3641 , result.base_value
3642 , result.ptp_start_date
3643 , result.ptp_end_date
3644 , result.elecls
3645 order by 1,7,2;
3646
3647 --
3648 cursor csr_process_meaning (p_process_type varchar2) is
3649 select upper(meaning)
3650 from fnd_lookup_values
3651 where lookup_type = 'FR_PROCESS_TYPE'
3652 and view_application_id = 3
3653 and lookup_code = p_process_type
3654 and security_group_id = g_sec_grp_id_process_type
3655 and language = userenv('LANG');
3656
3657 -- modified for bug 3683906
3658 cursor csr_get_rubric(p_group_code varchar2,
3659 p_sec_grp_id_ele_grp number,
3660 p_sec_grp_id_user_ele_grp number) is
3661 select tag ,meaning
3662 from fnd_lookup_values
3663 where ((lookup_type = 'FR_ELEMENT_GROUP'
3664 and security_group_id = p_sec_grp_id_ele_grp)
3665 OR (lookup_type = 'FR_USER_ELEMENT_GROUP'
3666 and security_group_id = p_sec_grp_id_user_ele_grp))
3667 and lookup_code = p_group_code
3668 and LANGUAGE = USERENV('LANG')
3669 and VIEW_APPLICATION_ID = 3
3670 order by lookup_type desc;
3671 --
3672 BEGIN
3673 hr_utility.set_location('Entering ' || l_proc, 5);
3674 -- Modified for bug 3683906, commented out under bug 4778143
3675 -- l_sec_grp_id_user_ele_grp := g_sec_grp_id_user_element_grp;
3676 -- l_sec_grp_id_ele_grp := g_sec_grp_id_element_grp;
3677 --
3678 hr_utility.trace('LOAD DEDUCTIONS 1');
3679 l_pos_offset := 0;
3680 open csr_get_run_bals;
3681 fetch csr_get_run_bals bulk collect into
3682 tbl_tax_unit_id, tbl_process_type, tbl_ee_rate, tbl_cu_id, tbl_EE_ER,
3683 tbl_cxt_prefix, tbl_ee_amount, tbl_action_id, tbl_group_code;
3684 close csr_get_run_bals;
3685 l_action_ptr := tbl_action_id.FIRST;
3686 l_grouped_rate_ptr := 0;
3687 tbl_action_id(0) := null; -- invoke null trap first time through loop
3688 l_current_ptr := l_action_ptr;
3689 --
3690 -- loop through whole table, grouping EE/ER rows
3691 WHILE l_current_ptr IS NOT NULL LOOP
3692 -- Get the balance amount (tbl_ee_amount currently stores the def bal id)
3693 tbl_ee_amount(l_current_ptr) := pay_balance_pkg.get_value(
3694 tbl_ee_amount(l_current_ptr)
3695 ,tbl_action_id(l_current_ptr)
3696 ,tbl_tax_unit_id(l_current_ptr)
3697 ,null
3698 ,tbl_cu_id(l_current_ptr)
3699 ,tbl_process_type(l_current_ptr)
3700 ,null ,null ,null ,null ,null
3701 ,tbl_ee_rate(l_current_ptr));
3702 -- if current row matches the group row
3703 if tbl_action_id(l_current_ptr) = tbl_action_id(l_grouped_rate_ptr)
3704 and tbl_group_code(l_current_ptr) = tbl_group_code(l_grouped_rate_ptr)
3705 and nvl(tbl_process_type(l_current_ptr),'<null>') =
3706 nvl(tbl_process_type(l_grouped_rate_ptr),'<null>')
3707 and tbl_cxt_prefix(l_current_ptr) = tbl_cxt_prefix(l_grouped_rate_ptr)
3708 then
3709 -- if balance amount <> 0
3710 if tbl_ee_amount(l_current_ptr) <> 0 then
3711 -- add current row values to appropriate cols of group row
3712 if tbl_EE_ER(l_current_ptr) = 'ER' then
3713 if tbl_ee_rate(l_current_ptr) is not null then
3714 tbl_er_rate(l_grouped_rate_ptr) :=
3715 nvl(tbl_er_rate(l_grouped_rate_ptr),0) +
3716 tbl_ee_rate(l_current_ptr);
3717 end if;
3718 tbl_er_amount(l_grouped_rate_ptr) :=
3719 nvl(tbl_er_amount(l_grouped_rate_ptr),0) +
3720 tbl_ee_amount(l_current_ptr);
3721 else
3722 if tbl_ee_rate(l_current_ptr) is not null then
3723 tbl_ee_rate(l_grouped_rate_ptr) :=
3724 nvl(tbl_ee_rate(l_grouped_rate_ptr),0) +
3725 tbl_ee_rate(l_current_ptr);
3726 end if;
3727 tbl_ee_amount(l_grouped_rate_ptr) :=
3728 nvl(tbl_ee_amount(l_grouped_rate_ptr),0) +
3729 tbl_ee_amount(l_current_ptr);
3730 end if;
3731 end if;
3732 else -- (current row doesn't match the group row)
3733 -- if balance amount <> 0 then
3734 if tbl_ee_amount(l_current_ptr) <> 0 then
3735 -- delete any rows between the group row and current row non-inclusive
3736 tbl_tax_unit_id.delete(l_grouped_rate_ptr+1,l_current_ptr-1);
3737 tbl_process_type.delete(l_grouped_rate_ptr+1,l_current_ptr-1);
3738 tbl_ee_rate.delete(l_grouped_rate_ptr+1,l_current_ptr-1);
3739 tbl_cu_id.delete(l_grouped_rate_ptr+1,l_current_ptr-1);
3740 tbl_EE_ER.delete(l_grouped_rate_ptr+1,l_current_ptr-1);
3741 tbl_cxt_prefix.delete(l_grouped_rate_ptr+1,l_current_ptr-1);
3742 tbl_ee_amount.delete(l_grouped_rate_ptr+1,l_current_ptr-1);
3743 tbl_action_id.delete(l_grouped_rate_ptr+1,l_current_ptr-1);
3744 tbl_group_code.delete(l_grouped_rate_ptr+1,l_current_ptr-1);
3745 -- make the current row the new group row
3746 l_grouped_rate_ptr := l_current_ptr;
3747 -- if rate is not null then
3748 if tbl_ee_rate(l_current_ptr) is not null
3749 then
3750 -- get base name from group code then base def bal id from base name
3751 if nvl(l_group_code,'<null>') <> tbl_group_code(l_current_ptr) then
3752 l_group_code := tbl_group_code(l_current_ptr);
3753 l_def_bal_id :=
3754 get_balance_id(PAY_FR_GENERAL.get_base_name
3755 (g_param_business_group_id,l_group_code)
3756 ,'Assignment Proration Run To Date');
3757 end if;
3758 if not tbl_pos_idx.exists(l_def_bal_id) then
3759 -- store base def bal id in next row of tbl_retrieval_list (from 1)
3760 tbl_retrieval_list(nvl(tbl_retrieval_list.last+1
3761 ,1)).defined_balance_id := l_def_bal_id;
3762 -- store (id of above row)+offset in tbl_pos_idx(def_bal_id)
3763 tbl_pos_idx(l_def_bal_id) := tbl_retrieval_list.last+l_pos_offset;
3764 end if;
3765 -- store tbl_pos_idx(def_bal_id) against current row
3766 tbl_row_base(l_current_ptr) := tbl_pos_idx(l_def_bal_id);
3767 end if; -- rate exists
3768 -- if ER then
3769 if tbl_EE_ER(l_current_ptr) = 'ER' then
3770 -- move amount to correct col, nullify EE cols
3771 tbl_er_rate(l_current_ptr) := tbl_ee_rate(l_current_ptr);
3772 tbl_ee_rate(l_current_ptr) := null;
3773 tbl_er_amount(l_current_ptr) := tbl_ee_amount(l_current_ptr);
3774 tbl_ee_amount(l_current_ptr) := null;
3775 -- else initialise ER cols
3776 else
3777 tbl_er_rate(l_current_ptr) := null;
3778 tbl_er_amount(l_current_ptr) := null;
3779 end if;
3780 else
3781 -- (else balance amount = 0)
3782 -- if l_action_ptr points to the current row
3783 if l_action_ptr = l_current_ptr then
3784 -- set l_action_ptr to the next row; current row will be deleted
3785 l_action_ptr := l_action_ptr + 1;
3786 end if;
3787 end if;
3788 end if; -- row matching
3789 -- Queue up next row
3790 l_current_ptr := tbl_action_id.next(l_current_ptr);
3791 -- If change of action or end of table
3792 if l_current_ptr is null
3793 or tbl_action_id(l_current_ptr) <> tbl_action_id(l_action_ptr)
3794 then
3795 -- if there were deductions with bases against previous action then
3796 if tbl_pos_idx.count > 0 then
3797 -- fetch tbl_retrieval_list for previous action id
3798 pay_balance_pkg.get_value(tbl_action_id(l_action_ptr)
3799 ,tbl_retrieval_list);
3800 -- copy each tbl_retrieval_list(i).balance_value to tbl_base(i+offset)
3801 for i in tbl_retrieval_list.first..tbl_retrieval_list.last loop
3802 tbl_base(i+l_pos_offset):=nvl(tbl_retrieval_list(i).balance_value,0);
3803 end loop;
3804 l_pos_offset := tbl_base.last;
3805 -- clear bases tables tbl_pos_idx and tbl_retrieval_list
3806 tbl_retrieval_list.delete;
3807 tbl_pos_idx.delete;
3808 end if;
3809 -- delete tbl_action_id rows between l_action_ptr and last grouped row
3810 -- non-inc
3811 -- (remaining tbl_action_id rows serve as end markers for later grouping)
3812 tbl_action_id.delete(l_action_ptr,l_grouped_rate_ptr-1);
3813 -- set l_action_ptr
3814 l_action_ptr := l_current_ptr;
3815 end if;
3816 end loop; -- ee/er pairing.
3817 -- delete any rows following the (last) group row.
3818 tbl_process_type.delete(l_grouped_rate_ptr+1,tbl_process_type.last);
3819 tbl_ee_rate.delete(l_grouped_rate_ptr+1,tbl_ee_rate.last);
3820 tbl_cxt_prefix.delete(l_grouped_rate_ptr+1,tbl_cxt_prefix.last);
3821 tbl_ee_amount.delete(l_grouped_rate_ptr+1,tbl_ee_amount.last);
3822 tbl_action_id.delete(l_grouped_rate_ptr+1,tbl_action_id.last);
3823 tbl_group_code.delete(l_grouped_rate_ptr+1,tbl_group_code.last);
3824 -- delete cols no longer required
3825 tbl_cu_id.delete;
3826 tbl_tax_unit_id.delete;
3827 tbl_EE_ER.delete;
3828 l_group_code := null; -- is the context to a different 'cache' below.
3829
3830 -- loop through whole (sparse) table again, grouping by rate combination
3831 -- then again for retros, which would be already grouped.
3832 --
3833 for l_retro_processing in 0..1 loop
3834 if l_retro_processing = 1 then
3835 open csr_get_retros;
3836 fetch csr_get_retros bulk collect into
3837 tbl_group_code, tbl_row_base, tbl_ee_rate, tbl_ee_amount, tbl_er_rate,
3838 tbl_er_amount, tbl_process_type, tbl_cxt_prefix, tbl_start_date,
3839 tbl_end_date;
3840 close csr_get_retros;
3841 l_retro_tl := ' ' ||g_retro_tl;
3842 end if;
3843 l_grouped_rate_ptr := tbl_group_code.first;
3844 <<grouped_rate_loop>>
3845 WHILE l_grouped_rate_ptr IS NOT NULL LOOP
3846 if l_retro_processing = 0 then
3847 -- processing non-retros, which need further grouping
3848 if tbl_row_base.exists(l_grouped_rate_ptr)
3849 then
3850 -- replace pointer to base with actual base value, reusing column
3851 tbl_row_base(l_grouped_rate_ptr) :=
3852 tbl_base(tbl_row_base(l_grouped_rate_ptr));
3853 else
3854 -- initialise the base
3855 tbl_row_base(l_grouped_rate_ptr) := 0;
3856 end if;
3857 -- loop through subsequent rows looking for matches
3858 l_current_ptr := tbl_group_code.next(l_grouped_rate_ptr);
3859 <<match_loop>>
3860 WHILE l_current_ptr IS NOT NULL LOOP
3861 if tbl_group_code(l_current_ptr) > tbl_group_code(l_grouped_rate_ptr)
3862 or (tbl_group_code(l_current_ptr)= tbl_group_code(l_grouped_rate_ptr)
3863 and tbl_process_type(l_current_ptr) >
3864 tbl_process_type(l_grouped_rate_ptr))
3865 then
3866 -- skip to first row for the next action
3867 -- (Nb. it will be after the current actions end marker, which may
3868 -- actually be the current row)
3869 l_current_ptr :=
3870 tbl_group_code.next(nvl(tbl_action_id.next(l_current_ptr-1)
3871 ,tbl_group_code.last));
3872 else
3873 if nvl(tbl_ee_rate(l_current_ptr),0) =
3874 nvl(tbl_ee_rate(l_grouped_rate_ptr),0)
3875 and nvl(tbl_er_rate(l_current_ptr),0) =
3876 nvl(tbl_er_rate(l_grouped_rate_ptr),0)
3877 and tbl_group_code(l_current_ptr) =
3878 tbl_group_code(l_grouped_rate_ptr)
3879 and nvl(tbl_process_type(l_current_ptr),'<null>') =
3880 nvl(tbl_process_type(l_grouped_rate_ptr),'<null>')
3881 and tbl_cxt_prefix(l_current_ptr) =
3882 tbl_cxt_prefix(l_grouped_rate_ptr)
3883 then
3884 if tbl_ee_amount(l_current_ptr) is not null then
3885 tbl_ee_amount(l_grouped_rate_ptr) :=
3886 nvl(tbl_ee_amount(l_grouped_rate_ptr),0) +
3887 tbl_ee_amount(l_current_ptr);
3888 end if;
3889 if tbl_er_amount(l_current_ptr) is not null then
3890 tbl_er_amount(l_grouped_rate_ptr) :=
3891 nvl(tbl_er_amount(l_grouped_rate_ptr),0) +
3892 tbl_er_amount(l_current_ptr);
3893 end if;
3894 if tbl_row_base.exists(l_current_ptr) then
3895 tbl_row_base(l_grouped_rate_ptr) :=
3896 tbl_row_base(l_grouped_rate_ptr) +
3897 tbl_base(tbl_row_base(l_current_ptr));
3898 end if;
3899 -- delete the current row
3900 tbl_process_type.delete(l_current_ptr);
3901 tbl_ee_rate.delete(l_current_ptr);
3902 tbl_er_rate.delete(l_current_ptr);
3903 tbl_cxt_prefix.delete(l_current_ptr);
3904 tbl_ee_amount.delete(l_current_ptr);
3905 tbl_er_amount.delete(l_current_ptr);
3906 tbl_group_code.delete(l_current_ptr);
3907 tbl_row_base.delete(l_current_ptr);
3908 end if; -- match
3909 l_current_ptr := tbl_group_code.next(l_current_ptr);
3910 end if;
3911 end loop match_loop;
3912 else -- processing retros; format dates
3913 l_append_dates := to_char(tbl_start_date(l_grouped_rate_ptr),' dd-mm')
3914 || to_char(tbl_end_date(l_grouped_rate_ptr),' - dd-mm');
3915 end if; -- end retro processing
3916 --
3917 -- Can now archive the row.
3918 -- First fetch the rubric
3919 if nvl(l_group_code,'<null>') <> tbl_group_code(l_grouped_rate_ptr) then
3920 l_group_code := tbl_group_code(l_grouped_rate_ptr);
3921 open csr_get_rubric(l_group_code,
3922 g_sec_grp_id_element_grp,
3923 g_sec_grp_id_user_element_grp);
3924 fetch csr_get_rubric into l_rubric, l_description;
3925 close csr_get_rubric;
3926 end if;
3927 -- Derive Process Type, only if it's not the latest process type from
3928 -- the latest run in this archive set
3929 --
3930 if p_latest_process_type <> tbl_process_type(l_grouped_rate_ptr) then
3931 if nvl(l_proc_type,'<null>') <> tbl_process_type(l_grouped_rate_ptr)
3932 then
3933 l_proc_type := tbl_process_type(l_grouped_rate_ptr);
3934 open csr_process_meaning(l_proc_type);
3935 fetch csr_process_meaning into l_proc_type_meaning;
3936 close csr_process_meaning;
3937 end if;
3938 l_this_process_type := l_proc_type_meaning;
3939 else
3940 l_this_process_type := null;
3941 end if;
3942 -- Maintain the running totals
3943 if tbl_cxt_prefix(l_grouped_rate_ptr) = 'CONTRIBUTIONS' then
3944 l_total_ee_deductions := l_total_ee_deductions +
3945 nvl(tbl_ee_amount(l_grouped_rate_ptr),0);
3946 l_total_er_deductions := l_total_er_deductions +
3947 nvl(tbl_er_amount(l_grouped_rate_ptr),0);
3948 elsif tbl_cxt_prefix(l_grouped_rate_ptr) = 'CONTRIBUTIONS_CSG' then
3949 l_total_ee_csg := l_total_ee_csg +
3950 nvl(tbl_ee_amount(l_grouped_rate_ptr),0);
3951 elsif tbl_cxt_prefix(l_grouped_rate_ptr) = 'CONTRIBUTIONS_REBATE' then
3952 tbl_er_amount(l_grouped_rate_ptr) :=
3953 tbl_er_amount(l_grouped_rate_ptr)*-1;
3954 l_total_er_deductions := l_total_er_deductions +
3955 nvl(tbl_er_amount(l_grouped_rate_ptr),0);
3956 end if;
3957 -- Do not print zeros
3958 --
3959 if tbl_row_base(l_grouped_rate_ptr) = 0 then
3960 tbl_row_base(l_grouped_rate_ptr) := null;
3961 end if;
3962
3963 -- Write this line to the archive
3964 --
3965 pay_action_information_api.create_action_information (
3966 p_action_information_id => l_action_info_id
3967 , p_action_context_id => p_archive_assignment_action_id
3968 , p_action_context_type => l_archive_type
3969 , p_action_information1 => tbl_cxt_prefix(l_grouped_rate_ptr)
3970 , p_action_information2 => l_rubric
3971 , p_action_information3 => l_grouped_rate_ptr
3972 , p_tax_unit_id => p_establishment_id
3973 , p_object_version_number => l_ovn
3974 , p_action_information_category => l_context
3975 , p_action_information4 => l_rubric
3976 , p_action_information5 => l_description||l_retro_tl
3977 , p_action_information6 => l_append_dates
3978 , p_action_information7 => l_this_process_type
3979 , p_action_information9 => fnd_number.number_to_canonical(
3980 tbl_row_base(l_grouped_rate_ptr))
3981 , p_action_information10 => fnd_number.number_to_canonical(
3982 tbl_ee_rate(l_grouped_rate_ptr))
3983 , p_action_information11 => fnd_number.number_to_canonical(
3984 tbl_ee_amount(l_grouped_rate_ptr))
3985 , p_action_information12 => fnd_number.number_to_canonical(
3986 tbl_er_rate(l_grouped_rate_ptr))
3987 , p_action_information13 => fnd_number.number_to_canonical(
3988 tbl_er_amount(l_grouped_rate_ptr)));
3989 --
3990 l_grouped_rate_ptr := tbl_group_code.next(l_grouped_rate_ptr);
3991 end loop grouped_rate_loop;
3992 -- clear all the tables
3993 tbl_row_base.delete;
3994 tbl_ee_rate.delete;
3995 tbl_ee_amount.delete;
3996 tbl_er_rate.delete;
3997 tbl_er_amount.delete;
3998 tbl_cxt_prefix.delete;
3999 tbl_process_type.delete;
4000 tbl_group_code.delete;
4001 tbl_action_id.delete;
4002 end loop; -- retro / non retro
4003 --
4004 -- pass back the total ee charges for further totals derivation
4005 --
4006 p_total_deduct_ee := l_total_ee_deductions;
4007 p_total_deduct_er := l_total_er_deductions;
4008 p_total_charge_ee := l_total_ee_deductions + l_total_ee_csg;
4009 p_total_charge_er := l_total_er_deductions;
4010 --
4011 hr_utility.trace('p_total_deduct_ee='|| p_total_deduct_ee);
4012 hr_utility.trace('p_total_deduct_er='|| p_total_deduct_er);
4013 hr_utility.trace('p_total_charge_ee='|| p_total_charge_ee);
4014 hr_utility.trace('p_total_charge_er='|| p_total_charge_er);
4015 --
4016 hr_utility.set_location('Leaving ' || l_proc, 210);
4017 END load_deductions;
4018 END pay_fr_arc_pkg;