[Home] [Help]
PACKAGE BODY: APPS.PAY_MX_SSAFFL_SALARY
Source
1 PACKAGE BODY pay_mx_ssaffl_salary AS
2 /* $Header: paymxsalary.pkb 120.2 2005/08/01 12:34:05 kthirmiy noship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1993 Oracle Corporation. *
7 * All rights reserved. *
8 * *
9 * This material has been provided pursuant to an agreement *
10 * containing restrictions on its use. The material is also *
11 * protected by copyright law. No part of this material may *
12 * be copied or distributed, transmitted or transcribed, in *
13 * any form or by any means, electronic, mechanical, magnetic, *
14 * manual, or otherwise, or disclosed to third parties without *
15 * the express written permission of Oracle Corporation, *
16 * 500 Oracle Parkway, Redwood City, CA, 94065. *
17 * *
18 ******************************************************************
19
20 Name : per_mx_ssaffl_salary
21
22 Description : This package is used by the Social Security Affiliation
23 Salary Modification report to
24 1) Archive Salary affiliation records in
25 pay_action_information table
26 2) Produce salary modification dispmag tape report
27
28 Change List
29 -----------
30 Date Name Vers Bug No Description
31 ----------- ---------- ------ ------- --------------------------
32 15-NOV-2004 kthirmiy 115.0 Created.
33 02-DEC-2004 kthirmiy 115.1 round idw to 2 decimal and archive
34 03-Jan-2005 kthirmiy 115.2 4084628 IDW is limited to 25 times of minimum wages
35 of Zone A for reporting purposes
36 07-Jan-2005 kthirmiy 115.3 4104743 Default Implementation date is derived from
37 pay_mx_legislation_info_f table.
38 20-Jan-2005 ardsouza 115.4 4129001 Added business_group_id parameter in
39 calls to procedure "per_mx_ssaffl_
40 archive.derive_gre_from_loc_scl".
41 16-Feb-2005 kthirmiy 115.5 4184215 Changed MXIDWF and to MXIDWV to
42 MX_IDWF and MX_IDWV
43 24-Feb-2005 kthirmiy 115.6 4201693 increased lv_idw_mode to 20 characters
44 06-May-2005 kthirmiy 115.7 4353084 removed the redundant use of bind variable
45 payroll_action_id
46 31-May-2005 ardsouza 115.8 4403044 Corrected condition to default impl
47 date in get_start_date procedure.
48 01-Aug-2005 kthirmiy 115.9 4528984 Added where condition to get the correct
49 minimum wage based on the effective_date
50 ******************************************************************************/
51
52
53 --
54 -- < PRIVATE GLOBALS > ---------------------------------------------------
55 --
56
57 gv_package VARCHAR2(100) ;
58
59 gv_event_group VARCHAR2(40) ;
60
61 g_ambiguous_error VARCHAR2(100) ;
62 g_missing_gre_error VARCHAR2(100) ;
63
64 g_report_imp_date DATE ;
65
66 g_event_group_id NUMBER ;
67
68 g_action_salary_category VARCHAR2(100) ;
69 g_action_sep_category VARCHAR2(100) ;
70
71 -- flag to write the debug messages in the concurrent program log file
72 g_concurrent_flag VARCHAR2(1) ;
73 -- flag to write the debug messages in the trace file
74 g_debug_flag VARCHAR2(1) ;
75
76
77
78 /******************************************************************************
79 Name : msg
80 Purpose : Log a message, either using fnd_file, or hr_utility.trace
81 ******************************************************************************/
82
83 PROCEDURE msg(p_text VARCHAR2)
84 IS
85 --
86 BEGIN
87 -- Write to the concurrent request log
88 fnd_file.put_line(fnd_file.log, p_text);
89
90 END msg;
91
92 /******************************************************************************
93 Name : dbg
94 Purpose : Log a message, either using fnd_file, or hr_utility.trace
95 if debuggging is enabled
96 ******************************************************************************/
97 PROCEDURE dbg(p_text VARCHAR2) IS
98
99 BEGIN
100
101 IF (g_debug_flag = 'Y') THEN
102 IF (g_concurrent_flag = 'Y') THEN
103 -- Write to the concurrent request log
104 fnd_file.put_line(fnd_file.log, p_text);
105 ELSE
106 -- Use HR trace
107 hr_utility.trace(p_text);
108 END IF;
109 END IF;
110
111 END dbg;
112
113
114 FUNCTION get_start_date( p_business_group_id in varchar2
115 ,p_tran_gre_id in varchar2
116 ,p_gre_id in varchar2
117 ) RETURN VARCHAR2
118 IS
119
120 cursor c_get_start_date(cp_tax_unit_id in number)
121 is
122 select fnd_date.canonical_to_date(ltrim(rtrim(substr(ppa.legislative_parameters,
123 instr(ppa.legislative_parameters,
124 'END_DATE=')
125 + length('END_DATE='),
126 (instr(ppa.legislative_parameters,
127 'TRANS_GRE=') - 1 )
128 - (instr(ppa.legislative_parameters,
129 'END_DATE=')
130 + length('END_DATE='))))))
131 from pay_assignment_actions paa,
132 pay_payroll_actions ppa
133 where paa.tax_unit_id = cp_tax_unit_id
134 and ppa.payroll_action_id=paa.payroll_action_id
135 and ppa.report_type='SS_AFFILIATION'
136 and ppa.report_qualifier ='SALARY'
137 order by paa.payroll_action_id desc ;
138
139
140 cursor c_get_imp_date(cp_organization_id in number)
141 is
142 select fnd_date.canonical_to_date(org_information6)
143 from hr_organization_information
144 where org_information_context= 'MX_TAX_REGISTRATION'
145 and organization_id = cp_organization_id ;
146
147 ld_report_imp_date date ;
148 ld_start_date date ;
149 lv_start_date varchar2(50);
150 ln_tax_unit_id NUMBER;
151 ln_legal_emp_id NUMBER;
152
153 begin
154
155
156 -- get the legal employer id from p_trans_gre_id
157
158 ln_legal_emp_id := hr_mx_utility.get_legal_employer(p_business_group_id,
159 p_tran_gre_id) ;
160
161
162 -- get the report Implementation Date from p_legal_emp_id
163 open c_get_imp_date(ln_legal_emp_id) ;
164 fetch c_get_imp_date into ld_report_imp_date ;
165 -- Bug 4403044 - Corrected condition
166 --
167 if (c_get_imp_date%notfound) OR (ld_report_imp_date IS NULL) then
168 -- defaulting to Report Implementation Date from mx pay legislation info table
169 ld_report_imp_date := fnd_date.canonical_to_date(per_mx_ssaffl_archive.get_default_imp_date) ;
170 end if;
171 close c_get_imp_date;
172
173 if p_gre_id is not null then
174 ln_tax_unit_id := to_number(p_gre_id) ;
175 else
176 ln_tax_unit_id := to_number(p_tran_gre_id) ;
177 end if ;
178
179 open c_get_start_date(ln_tax_unit_id);
180 fetch c_get_start_date into ld_start_date ;
181 if c_get_start_date%notfound then
182 -- assign the ld_start_date from rep imp date
183 ld_start_date := ld_report_imp_date ;
184 end if;
185 close c_get_start_date;
186
187 lv_start_date := fnd_date.date_to_canonical(ld_start_date) ;
188
189 return lv_start_date ;
190
191 end get_start_date;
192
193
194
195 /******************************************************************************
196 Name : get_payroll_action_info
197 Purpose : This procedue returns the Payroll Action level parameter
198 information for SS Affiliation Archiver.
199 Arguments : p_payroll_action_id - Payroll_Action_id of archiver
200 p_report_mode - Fixed Salary, Bimonthly Salary
201 p_period_start_date - Bimonthly period start date
202 p_period_end_date - Bimonthly period end date
203 p_start_date - Start date of Archiver
204 p_end_date - End date of Archiver
205 p_business_group_id - Business Group ID
206 p_tran_gre_id - Transmiter GRE Id
207 p_gre_id - GRE Id
208 p_event_group_id - Event Group Id
209
210 ******************************************************************************/
211 PROCEDURE get_payroll_action_info(p_payroll_action_id in number
212 ,p_report_mode out nocopy varchar2
213 ,p_period_start_date out nocopy date
214 ,p_period_end_date out nocopy date
215 ,p_start_date out nocopy date
216 ,p_end_date out nocopy date
217 ,p_business_group_id out nocopy number
218 ,p_tran_gre_id out nocopy number
219 ,p_gre_id out nocopy number
220 ,p_event_group_id out nocopy number
221 )
222 IS
223 -- cursor to get all the parameters from pay_payroll_actions table
224
225 cursor c_payroll_Action_info(cp_payroll_action_id in number) is
226 select business_group_id,
227 to_number(substr(legislative_parameters,
228 instr(legislative_parameters,
229 'GRE_ID=')
230 + length('GRE_ID='))) , -- gre_id
231 to_number(ltrim(rtrim(substr(legislative_parameters,
232 instr(legislative_parameters,
233 'TRANS_GRE=')
234 + length('TRANS_GRE='),
235 (instr(legislative_parameters,
236 'GRE_ID=') - 1 )
237 - (instr(legislative_parameters,
238 'TRANS_GRE=')
239 + length('TRANS_GRE=')))))) , -- trans_gre
240
241 fnd_date.canonical_to_date(ltrim(rtrim(substr(legislative_parameters,
242 instr(legislative_parameters,
243 'END_DATE=')
244 + length('END_DATE='),
245 (instr(legislative_parameters,
246 'TRANS_GRE=') - 1 )
247 - (instr(legislative_parameters,
248 'END_DATE=')
249 + length('END_DATE=')))))), -- end_date
250
251 fnd_date.canonical_to_date(ltrim(rtrim(substr(legislative_parameters,
252 instr(legislative_parameters,
253 'START_DATE=')
254 + length('START_DATE='),
255 (instr(legislative_parameters,
256 'END_DATE=') - 1 )
257 - (instr(legislative_parameters,
258 'START_DATE=')
259 + length('START_DATE=')))))), -- start_date
260
261
262 fnd_date.canonical_to_date(ltrim(rtrim(substr(legislative_parameters,
263 instr(legislative_parameters,
264 'PERIOD_ENDING_DATE=')
265 + length('PERIOD_ENDING_DATE='),
266 (instr(legislative_parameters,
267 'START_DATE=') - 1 )
268 - (instr(legislative_parameters,
269 'PERIOD_ENDING_DATE=')
270 + length('PERIOD_ENDING_DATE=')))))), -- period_ending_date
271
272 trunc( add_months (
273 fnd_date.canonical_to_date(ltrim(rtrim(substr(legislative_parameters,
274 instr(legislative_parameters,
275 'PERIOD_ENDING_DATE=')
276 + length('PERIOD_ENDING_DATE='),
277 (instr(legislative_parameters,
278 'START_DATE=') - 1 )
279 - (instr(legislative_parameters,
280 'PERIOD_ENDING_DATE=')
281 + length('PERIOD_ENDING_DATE=')))))) , -1 ),'MM'), -- period_start_date
282
283 ltrim(rtrim(substr(legislative_parameters,
284 instr(legislative_parameters,
285 'REPORT_MODE=')
286 + length('REPORT_MODE='),
287 (instr(legislative_parameters,
288 'PERIOD_ENDING_DATE=') - 1 )
289 - (instr(legislative_parameters,
290 'REPORT_MODE=')
291 + length('REPORT_MODE='))))) -- report_mode
292
293 from pay_payroll_actions
294 where payroll_action_id = cp_payroll_action_id;
295
296 cursor c_get_imp_date(cp_organization_id in number)
297 is
298 select fnd_date.canonical_to_date(org_information6)
299 from hr_organization_information
300 where org_information_context= 'MX_TAX_REGISTRATION'
301 and organization_id = cp_organization_id ;
302
303
304 cursor c_get_event_group (cp_event_group_name in varchar2) is
305 select event_group_id
306 from pay_event_groups
307 where event_group_name = cp_event_group_name ;
308
309 lv_report_mode VARCHAR2(1);
310 ld_period_start_date DATE;
311 ld_period_end_date DATE;
312 ld_end_date DATE;
313 ld_start_date DATE;
314 ln_business_group_id NUMBER;
315 ln_tran_gre_id NUMBER;
316 ln_gre_id NUMBER;
317
318 ln_tax_unit_id NUMBER;
319 ln_legal_emp_id NUMBER;
320 ln_event_group_id NUMBER;
321
322 ld_report_imp_date DATE;
323
324 lv_procedure_name VARCHAR2(100) ;
325 lv_error_message VARCHAR2(200) ;
326 ln_step NUMBER;
327
328 BEGIN
329
330 lv_procedure_name := '.get_payroll_action_info';
331
332 hr_utility.set_location(gv_package || lv_procedure_name, 10);
333 ln_step := 1;
334 dbg('Entering get_payroll_action_info .......');
335
336 -- open the cursor to get all the parameters from pay_payroll_actions table
337 open c_payroll_action_info(p_payroll_action_id);
338 fetch c_payroll_action_info into ln_business_group_id,
339 ln_gre_id,
340 ln_tran_gre_id,
341 ld_end_date,
342 ld_start_date,
343 ld_period_end_date,
344 ld_period_start_date,
345 lv_report_mode
346 ;
347 close c_payroll_action_info;
348
349 ln_legal_emp_id := hr_mx_utility.get_legal_employer(ln_business_group_id,
350 ln_tran_gre_id ) ;
351
352 -- get the report Implementation Date from ln_legal_emp_id and set it to the
353 -- global variable g_report_imp_date
354 hr_utility.set_location(gv_package || lv_procedure_name, 20);
355 ln_step := 2;
356 dbg('Get report Impl date for Legal employer id ' ||to_char(ln_legal_emp_id) );
357
358 open c_get_imp_date(ln_legal_emp_id) ;
359 fetch c_get_imp_date into ld_report_imp_date ;
360 if c_get_imp_date%notfound then
361 dbg('WARNING : Report Implementaton date is not entered for legal employer ' );
362 dbg('so defaulting to Report Implementation Date from pay mx legislation info table');
363 ld_report_imp_date := fnd_date.canonical_to_date(per_mx_ssaffl_archive.get_default_imp_date) ;
364 end if;
365 close c_get_imp_date;
366 dbg('report impl date is '||to_char(ld_report_imp_date) );
367 g_report_imp_date := ld_report_imp_date;
368
369 hr_utility.set_location(gv_package || lv_procedure_name, 30);
370 ln_step := 3;
371 dbg('Get Event Group Id ' );
372
373 open c_get_event_group(gv_event_group) ;
374 fetch c_get_event_group into ln_event_group_id ;
375 close c_get_event_group ;
376
377
378 p_report_mode := lv_report_mode;
379 p_period_start_date := ld_period_start_date;
380 p_period_end_date := ld_period_end_date;
381 p_start_date := ld_start_date;
382 p_end_date := ld_end_date;
383 p_business_group_id := ln_business_group_id;
384 p_tran_gre_id := ln_tran_gre_id;
385 p_gre_id := ln_gre_id;
386 p_event_group_id := ln_event_group_id ;
387
388 dbg('Parameters.....');
389
390 dbg('report mode : ' || p_report_mode ) ;
391 dbg('period start date : ' || fnd_date.date_to_canonical(p_period_start_date)) ;
392 dbg('period end date : ' || fnd_date.date_to_canonical(p_period_end_date)) ;
393 dbg('start date : ' || fnd_date.date_to_canonical(p_start_date)) ;
394 dbg('end date : ' || fnd_date.date_to_canonical(p_end_date)) ;
395 dbg('bus group id : ' || to_char(p_business_group_id)) ;
396 dbg('trans gre id : ' || to_char(p_tran_gre_id)) ;
397 dbg('gre id : ' || to_char(p_gre_id)) ;
398 dbg('event group id : ' || to_char(p_event_group_id) );
399
400 hr_utility.set_location(gv_package || lv_procedure_name, 40);
401 ln_step := 4;
402
403 dbg('Exiting get_payroll_action_info .......');
404
405 EXCEPTION
406 when others then
407 lv_error_message := 'Error at step ' || ln_step || ' in ' ||
408 gv_package || lv_procedure_name;
409
410 dbg(lv_error_message || '-' || sqlerrm);
411 hr_utility.raise_error;
412
413 END get_payroll_action_info;
414
415
416 /******************************************************************
417 Name : range_cursor
418 Purpose : This returns the select statement that is
419 used to created the range rows for the
420 Social Security Affiliation Archiver.
421 Notes : Calls procedure - get_payroll_action_info
422 ******************************************************************/
423 PROCEDURE range_cursor( p_payroll_action_id in number
424 ,p_sqlstr out nocopy varchar2)
425 IS
426
427 CURSOR c_chk_dyn_triggers_enabled(cp_func_area in VARCHAR2)
428 IS
429 select pte.short_name
430 from pay_functional_areas pfa,
431 pay_functional_triggers pft,
432 pay_trigger_events pte
433 where pfa.short_name = cp_func_area
434 and pfa.area_id = pft.area_id
435 and pft.event_id = pte.event_id
436 and ( pte.generated_flag <> 'Y' or pte.enabled_flag <> 'Y' ) ;
437
438 lv_report_mode VARCHAR2(1);
439 ld_period_start_date DATE;
440 ld_period_end_date DATE;
441
442 ld_start_date DATE;
443 ld_end_date DATE;
444 ln_business_group_id NUMBER;
445 ln_tran_gre_id NUMBER;
446 ln_gre_id NUMBER;
447 ln_event_group_id NUMBER;
448
449 lv_sql_string VARCHAR2(32000);
450 lv_procedure_name VARCHAR2(100) ;
451
452 lv_func_area VARCHAR2(40);
453 lv_trigger_name VARCHAR2(100);
454 BEGIN
455
456 dbg('Entering range_cursor ....... ') ;
457
458 gv_package := 'per_mx_ssaffl_salary' ;
459 gv_event_group := 'MX_SALARY_EVG' ;
460 g_ambiguous_error := '1' ; -- Multiple GRE found
461 g_missing_gre_error := '2' ; -- Location is not in the hierarchy';
462 g_debug_flag := 'Y' ;
463 -- g_concurrent_flag := 'Y' ;
464
465 lv_procedure_name := '.range_cursor';
466 hr_utility.set_location(gv_package || lv_procedure_name, 10);
467
468 lv_func_area := 'SS Affiliation Salary Events' ;
469
470 -- Get all the parameter information from pay_payroll_actions table
471 dbg('Get parameter information from pay_payroll_actions table' ) ;
472
473 get_payroll_action_info( p_payroll_action_id => p_payroll_action_id
474 ,p_report_mode => lv_report_mode
475 ,p_period_start_date => ld_period_start_date
476 ,p_period_end_date => ld_period_end_date
477 ,p_start_date => ld_start_date
478 ,p_end_date => ld_end_date
479 ,p_business_group_id => ln_business_group_id
480 ,p_tran_gre_id => ln_tran_gre_id
481 ,p_gre_id => ln_gre_id
482 ,p_event_group_id => ln_event_group_id);
483
484 hr_utility.set_location(gv_package || lv_procedure_name, 20);
485
486 -- Check the dynamic triggers are enable for functional area
487 dbg('Check dynamic triggers enabled' ) ;
488
489 open c_chk_dyn_triggers_enabled(lv_func_area );
490 fetch c_chk_dyn_triggers_enabled into lv_trigger_name ;
491
492 if c_chk_dyn_triggers_enabled%found then
493 close c_chk_dyn_triggers_enabled;
494
495 dbg('Error : Dynamic triggers NOT enabled' ) ;
496 lv_sql_string := null;
497
498 hr_utility.raise_error;
499
500 else
501
502 dbg('Dynamic triggers Enabled' ) ;
503 close c_chk_dyn_triggers_enabled ;
504
505 if lv_report_mode = 'F' then -- Fixed Salary
506
507 lv_sql_string := 'select distinct paf.person_id
508 from pay_process_events ppe,
509 pay_datetracked_events pde,
510 pay_event_updates peu,
511 pay_element_entries_f pee,
512 pay_element_types_f pet,
513 pay_element_type_extra_info petei,
514 per_all_assignments_f paf
515 where ppe.creation_date between
516 fnd_date.canonical_to_date(''' || fnd_date.date_to_canonical(ld_start_date) || ''')
517 and fnd_date.canonical_to_date(''' || fnd_date.date_to_canonical(ld_end_date) || ''')
518 and peu.event_update_id =ppe.event_update_id
519 and peu.dated_table_id = pde.dated_table_id
520 and pde.event_group_id = ''' ||ln_event_group_id || '''
521 and ppe.business_group_id = ''' ||ln_business_group_id || '''
522 and nvl(peu.column_name,1) = nvl(pde.column_name,1)
523 and decode(pde.update_type,''I'',''INSERT'',''U'',''UPDATE'',pde.update_type) = peu.event_type
524 and peu.change_type = ''DATE_EARNED''
525 and pee.element_entry_id = ppe.surrogate_key
526 and pet.element_type_id = pee.element_type_id
527 and petei.element_type_id = pee.element_type_id
528 and petei.eei_information_category=''PQP_UK_RATE_TYPE''
529 and petei.eei_information1=''MX_IDWF''
530 and ppe.effective_date between pee.effective_start_date and pee.effective_end_date
531 and paf.assignment_id = ppe.assignment_id
532 and ppe.effective_date between paf.effective_start_date and paf.effective_end_date
533 and (( per_mx_ssaffl_archive.derive_gre_from_loc_scl(paf.location_id,paf.business_group_id,paf.soft_coding_keyflex_id,ppe.effective_date) = -1 )
534 or ( per_mx_ssaffl_archive.derive_gre_from_loc_scl(paf.location_id,paf.business_group_id,paf.soft_coding_keyflex_id,ppe.effective_date) = -2 )
535 or ( ''' ||ln_tran_gre_id || ''' is not null and ''' ||ln_gre_id || ''' is not null and
536 per_mx_ssaffl_archive.derive_gre_from_loc_scl(paf.location_id,paf.business_group_id,paf.soft_coding_keyflex_id,ppe.effective_date)=''' ||ln_gre_id || ''' )
537 or ( ''' ||ln_tran_gre_id || ''' is not null and ''' ||ln_gre_id || ''' is null and
538 per_mx_ssaffl_archive.derive_gre_from_loc_scl(paf.location_id,paf.business_group_id,paf.soft_coding_keyflex_id,ppe.effective_date)
539 in
540 (select organization_id
541 from hr_organization_information hoi
542 where hoi.org_information_context = ''MX_SOC_SEC_DETAILS''
543 and ((org_information6 = ''' ||ln_tran_gre_id || ''' ) OR
544 ( organization_id = ''' ||ln_tran_gre_id || ''' and org_information3=''Y'')))))
545 and :payroll_action_id > 0 ' ;
546
547 elsif lv_report_mode = 'P' then -- Bi-monthly Salary
548
549 lv_sql_string := 'select paf1.person_id
550 from
551 (
552 select distinct paf.assignment_id
553 from pay_process_events ppe,
554 pay_datetracked_events pde,
555 pay_event_updates peu,
556 pay_element_entries_f pee,
557 pay_element_types_f pet,
558 pay_element_type_extra_info petei,
559 per_all_assignments_f paf
560 where ppe.creation_date between
561 fnd_date.canonical_to_date(''' || fnd_date.date_to_canonical(ld_start_date) || ''')
562 and fnd_date.canonical_to_date(''' || fnd_date.date_to_canonical(ld_end_date) || ''')
563 and peu.event_update_id =ppe.event_update_id
564 and peu.dated_table_id = pde.dated_table_id
565 and pde.event_group_id = ''' ||ln_event_group_id || '''
566 and ppe.business_group_id = ''' ||ln_business_group_id || '''
567 and nvl(peu.column_name,1) = nvl(pde.column_name,1)
568 and decode(pde.update_type,''I'',''INSERT'',''U'',''UPDATE'',pde.update_type) = peu.event_type
569 and peu.change_type = ''DATE_EARNED''
570 and pee.element_entry_id = ppe.surrogate_key
571 and pet.element_type_id = pee.element_type_id
572 and petei.element_type_id = pee.element_type_id
573 and petei.eei_information_category=''PQP_UK_RATE_TYPE''
574 and petei.eei_information1=''MX_IDWF''
575 and ppe.effective_date between pee.effective_start_date and pee.effective_end_date
576 and paf.assignment_id = ppe.assignment_id
577 and ppe.effective_date between paf.effective_start_date and paf.effective_end_date
578 union
579 select distinct pee.assignment_id
580 from pay_element_entries_f pee,
581 pay_element_type_extra_info petei
582 where pee.effective_start_date between
583 fnd_date.canonical_to_date(''' || fnd_date.date_to_canonical(ld_period_start_date) || ''')
584 and fnd_date.canonical_to_date(''' || fnd_date.date_to_canonical(ld_period_end_date) || ''')
585 and petei.element_type_id = pee.element_type_id
586 and petei.eei_information_category=''PQP_UK_RATE_TYPE''
587 and petei.eei_information1=''MX_IDWV''
588 ) x,
589 per_all_assignments_f paf1
590 where x.assignment_id = paf1.assignment_id
591 and fnd_date.canonical_to_date(''' || fnd_date.date_to_canonical(ld_period_end_date) || ''')
592 between paf1.effective_start_date and paf1.effective_end_date
593 and (( per_mx_ssaffl_archive.derive_gre_from_loc_scl(paf1.location_id,paf1.business_group_id,paf1.soft_coding_keyflex_id,
594 fnd_date.canonical_to_date(''' || fnd_date.date_to_canonical(ld_period_end_date) || ''')) = -1 )
595 or ( per_mx_ssaffl_archive.derive_gre_from_loc_scl(paf1.location_id,paf1.business_group_id,paf1.soft_coding_keyflex_id,
596 fnd_date.canonical_to_date(''' || fnd_date.date_to_canonical(ld_period_end_date) || ''')) = -2 )
597 or ( ''' ||ln_tran_gre_id || ''' is not null and ''' ||ln_gre_id || ''' is not null and
598 per_mx_ssaffl_archive.derive_gre_from_loc_scl(paf1.location_id,paf1.business_group_id,paf1.soft_coding_keyflex_id,
599 fnd_date.canonical_to_date(''' || fnd_date.date_to_canonical(ld_period_end_date) || '''))=''' ||ln_gre_id || ''')
600 or ( ''' ||ln_tran_gre_id || ''' is not null and ''' ||ln_gre_id || ''' is null and
601 per_mx_ssaffl_archive.derive_gre_from_loc_scl(paf1.location_id,paf1.business_group_id,paf1.soft_coding_keyflex_id,
602 fnd_date.canonical_to_date(''' || fnd_date.date_to_canonical(ld_period_end_date) || '''))
603 in
604 (select organization_id
605 from hr_organization_information hoi
606 where hoi.org_information_context = ''MX_SOC_SEC_DETAILS''
607 and ((org_information6 = ''' ||ln_tran_gre_id || ''' ) OR
608 ( organization_id = ''' ||ln_tran_gre_id || ''' and org_information3=''Y'')))))
609 and :payroll_action_id > 0 ' ;
610
611
612 end if;
613 end if;
614 hr_utility.set_location(gv_package || lv_procedure_name, 30);
615 p_sqlstr := lv_sql_string;
616 hr_utility.set_location(gv_package || lv_procedure_name, 40);
617
618 dbg('Exiting range_cursor .......') ;
619
620 END range_cursor;
621
622
623 /************************************************************
624 Name : action_creation
625 Purpose : This creates the assignment actions for
626 a specific chunk of people to be archived
627 by the SS Affiliation Salary Modification Report.
628 Notes : Calls procedure - get_payroll_action_info
629 ************************************************************/
630 PROCEDURE action_creation(
631 p_payroll_action_id in number
632 ,p_start_person_id in number
633 ,p_end_person_id in number
634 ,p_chunk in number)
635 IS
636
637 cursor c_get_fix_sal_asg( cp_start_person_id in number
638 ,cp_end_person_id in number
639 ,cp_tran_gre_id in number
640 ,cp_gre_id in number
641 ,cp_business_group_id in number
642 ,cp_start_date in date
643 ,cp_end_date in date
644 ,cp_event_group_id in number
645 ) is
646 select distinct ppe.assignment_id
647 from pay_process_events ppe,
648 pay_datetracked_events pde,
649 pay_event_updates peu,
650 pay_element_entries_f pee,
651 pay_element_types_f pet,
652 pay_element_type_extra_info petei,
653 per_all_assignments_f paf
654 where ppe.creation_date between cp_start_date and cp_end_date
655 and peu.event_update_id =ppe.event_update_id
656 and peu.dated_table_id = pde.dated_table_id
657 and pde.event_group_id = cp_event_group_id
658 and ppe.business_group_id = cp_business_group_id
659 and nvl(peu.column_name,1) = nvl(pde.column_name,1)
660 and decode(pde.update_type,'I','INSERT','U','UPDATE',pde.update_type) = peu.event_type
661 and peu.change_type = 'DATE_EARNED'
662 and pee.element_entry_id = ppe.surrogate_key
663 and pet.element_type_id = pee.element_type_id
664 and petei.element_type_id = pee.element_type_id
665 and petei.eei_information_category='PQP_UK_RATE_TYPE'
666 and petei.eei_information1='MX_IDWF'
667 and ppe.effective_date between pee.effective_start_date and pee.effective_end_date
668 and paf.assignment_id = ppe.assignment_id
669 and paf.person_id between cp_start_person_id and cp_end_person_id
670 and ppe.effective_date between paf.effective_start_date and paf.effective_end_date
671 and (( per_mx_ssaffl_archive.derive_gre_from_loc_scl(paf.location_id,paf.business_group_id,paf.soft_coding_keyflex_id,ppe.effective_date) = -1 )
672 or ( per_mx_ssaffl_archive.derive_gre_from_loc_scl(paf.location_id,paf.business_group_id,paf.soft_coding_keyflex_id,ppe.effective_date) = -2 )
673 or ( cp_tran_gre_id is not null and cp_gre_id is not null and
674 per_mx_ssaffl_archive.derive_gre_from_loc_scl(paf.location_id,paf.business_group_id,paf.soft_coding_keyflex_id,ppe.effective_date)=cp_gre_id )
675 or ( cp_tran_gre_id is not null and cp_gre_id is null and
676 per_mx_ssaffl_archive.derive_gre_from_loc_scl(paf.location_id,paf.business_group_id,paf.soft_coding_keyflex_id,ppe.effective_date)
677 in
678 (select organization_id
679 from hr_organization_information hoi
680 where hoi.org_information_context = 'MX_SOC_SEC_DETAILS'
681 and ((org_information6 = cp_tran_gre_id ) OR ( organization_id = cp_tran_gre_id and org_information3='Y')))))
682 ;
683
684
685 cursor c_get_all_sal_asg( cp_start_person_id in number
686 ,cp_end_person_id in number
687 ,cp_tran_gre_id in number
688 ,cp_gre_id in number
689 ,cp_business_group_id in number
690 ,cp_start_date in date
691 ,cp_end_date in date
692 ,cp_event_group_id in number
693 ,cp_period_start_date in date
694 ,cp_period_end_date in date
695 ) is
696 select x.assignment_id
697 from
698 (
699 select distinct paf.assignment_id
700 from pay_process_events ppe,
701 pay_datetracked_events pde,
702 pay_event_updates peu,
703 pay_element_entries_f pee,
704 pay_element_types_f pet,
705 pay_element_type_extra_info petei,
706 per_all_assignments_f paf
707 where ppe.creation_date between cp_start_date and cp_end_date
708 and peu.event_update_id =ppe.event_update_id
709 and peu.dated_table_id = pde.dated_table_id
710 and pde.event_group_id = cp_event_group_id
711 and ppe.business_group_id = cp_business_group_id
712 and nvl(peu.column_name,1) = nvl(pde.column_name,1)
713 and decode(pde.update_type,'I','INSERT','U','UPDATE',pde.update_type) = peu.event_type
714 and peu.change_type = 'DATE_EARNED'
715 and pee.element_entry_id = ppe.surrogate_key
716 and pet.element_type_id = pee.element_type_id
717 and petei.element_type_id = pee.element_type_id
718 and petei.eei_information_category='PQP_UK_RATE_TYPE'
719 and petei.eei_information1='MX_IDWF'
720 and ppe.effective_date between pee.effective_start_date and pee.effective_end_date
721 and paf.assignment_id = ppe.assignment_id
722 and ppe.effective_date between paf.effective_start_date and paf.effective_end_date
723 union
724 select distinct pee.assignment_id
725 from pay_element_entries_f pee,
726 pay_element_type_extra_info petei
727 where pee.effective_start_date between cp_period_start_date and cp_period_end_date
728 and petei.element_type_id = pee.element_type_id
729 and petei.eei_information_category='PQP_UK_RATE_TYPE'
730 and petei.eei_information1='MX_IDWV'
731 ) x,
732 per_all_assignments_f paf1
733 where x.assignment_id = paf1.assignment_id
734 and paf1.person_id between cp_start_person_id and cp_end_person_id
735 and cp_period_end_date between paf1.effective_start_date and paf1.effective_end_date
736 and (( per_mx_ssaffl_archive.derive_gre_from_loc_scl(paf1.location_id,paf1.business_group_id,paf1.soft_coding_keyflex_id,
737 cp_period_end_date) = -1 )
738 or ( per_mx_ssaffl_archive.derive_gre_from_loc_scl(paf1.location_id,paf1.business_group_id,paf1.soft_coding_keyflex_id,
739 cp_period_end_date) = -2 )
740 or ( cp_tran_gre_id is not null and cp_gre_id is not null and
741 per_mx_ssaffl_archive.derive_gre_from_loc_scl(paf1.location_id,paf1.business_group_id,paf1.soft_coding_keyflex_id,
742 cp_period_end_date)=cp_gre_id )
743 or ( cp_tran_gre_id is not null and cp_gre_id is null and
744 per_mx_ssaffl_archive.derive_gre_from_loc_scl(paf1.location_id,paf1.business_group_id,paf1.soft_coding_keyflex_id,cp_period_end_date)
745 in
746 (select organization_id
747 from hr_organization_information hoi
748 where hoi.org_information_context = 'MX_SOC_SEC_DETAILS'
749 and ((org_information6 = cp_tran_gre_id )
750 OR ( organization_id = cp_tran_gre_id and org_information3='Y'))))) ;
751
752
753 lv_report_mode VARCHAR2(1);
754 ld_period_start_date DATE;
755 ld_period_end_date DATE;
756 ld_start_date DATE;
757 ld_end_date DATE;
758 ln_business_group_id NUMBER;
759 ln_tran_gre_id NUMBER;
760 ln_gre_id NUMBER;
761 ln_person_id NUMBER;
762 ln_tax_unit_id NUMBER;
763 ln_event_group_id NUMBER;
764 ln_assignment_id NUMBER;
765 ln_action_id NUMBER;
766 lv_procedure_name VARCHAR2(100) ;
767 lv_error_message VARCHAR2(200);
768 ln_step NUMBER;
769
770 begin
771
772 dbg('Entering Action creation ..............') ;
773
774 gv_package := 'per_mx_ssaffl_salary' ;
775 gv_event_group := 'MX_SALARY_EVG' ;
776 g_debug_flag := 'Y' ;
777 -- g_concurrent_flag := 'Y' ;
778
779 lv_procedure_name := '.action_creation';
780
781 hr_utility.set_location(gv_package || lv_procedure_name, 10);
782 ln_step := 1;
783 dbg('Get parameter information from pay_payroll_actions table' ) ;
784
785 get_payroll_action_info( p_payroll_action_id => p_payroll_action_id
786 ,p_report_mode => lv_report_mode
787 ,p_period_start_date => ld_period_start_date
788 ,p_period_end_date => ld_period_end_date
789 ,p_start_date => ld_start_date
790 ,p_end_date => ld_end_date
791 ,p_business_group_id => ln_business_group_id
792 ,p_tran_gre_id => ln_tran_gre_id
793 ,p_gre_id => ln_gre_id
794 ,p_event_group_id => ln_event_group_id);
795
796 hr_utility.set_location(gv_package || lv_procedure_name, 20);
797 ln_step := 2;
798 dbg('Action creation Query parameters') ;
799 dbg('Start person id : ' || to_char(p_start_person_id));
800 dbg('End person id : ' || to_char(p_end_person_id));
801 dbg('tansmitter gre id : ' || to_char(ln_tran_gre_id));
802 dbg('gre id : ' || to_char(ln_gre_id));
803 dbg('event group id : ' || to_char(ln_event_group_id));
804 dbg('business_group_id : ' || to_char(ln_business_group_id));
805 dbg('start date is : ' || fnd_date.date_to_canonical(ld_start_date)) ;
806 dbg('end date is : ' || fnd_date.date_to_canonical(ld_end_date)) ;
807
808 if lv_report_mode = 'F' then -- Fixed Salary
809
810 open c_get_fix_sal_asg( p_start_person_id
811 ,p_end_person_id
812 ,ln_tran_gre_id
813 ,ln_gre_id
814 ,ln_business_group_id
815 ,ld_start_date
816 ,ld_end_date
817 ,ln_event_group_id);
818
819 elsif lv_report_mode = 'P' then -- Bimonthly Salary
820
821 open c_get_all_sal_asg( p_start_person_id
822 ,p_end_person_id
823 ,ln_tran_gre_id
824 ,ln_gre_id
825 ,ln_business_group_id
826 ,ld_start_date
827 ,ld_end_date
828 ,ln_event_group_id
829 ,ld_period_start_date
830 ,ld_period_end_date );
831
832 end if ;
833
834
835 -- Loop for all rows returned for SQL statement.
836 hr_utility.set_location(gv_package || lv_procedure_name, 30);
837 ln_step := 3;
838
839 loop
840
841 if lv_report_mode = 'F' then
842
843 fetch c_get_fix_sal_asg into ln_assignment_id ;
844 exit when c_get_fix_sal_asg%notfound;
845
846
847 elsif lv_report_mode = 'P' then -- Bimonthly Salary
848
849 fetch c_get_all_sal_asg into ln_assignment_id ;
850 exit when c_get_all_sal_asg%notfound;
851
852 end if;
853 -- if gre_id is not null then tax_unit_id= gre_id
854 -- if tran_gre_id is not null then tax_unit_id = tran_gre_id
855
856 hr_utility.set_location(gv_package || lv_procedure_name, 40);
857 ln_step := 4;
858 dbg('creating aaid for assignment_id = ' || to_char(ln_assignment_id) ||
859 ' Tax Unit Id = ' || to_char(nvl(ln_gre_id,ln_tran_gre_id)) ) ;
860
861 select pay_assignment_actions_s.nextval
862 into ln_action_id
863 from dual;
864
865 -- insert into pay_assignment_actions.
866 hr_nonrun_asact.insact(ln_action_id,
867 ln_assignment_id,
868 p_payroll_action_id,
869 p_chunk,
870 nvl(ln_gre_id,ln_tran_gre_id),
871 null,
872 'U',
873 null);
874 dbg('assignment action id is ' || to_char(ln_action_id) );
875
876
877 end loop;
878
879
880 if lv_report_mode = 'F' then
881
882 close c_get_fix_sal_asg ;
883
884 elsif lv_report_mode = 'P' then -- Bimonthly Salary
885
886 close c_get_all_sal_asg ;
887
888 end if;
889
890 hr_utility.set_location(gv_package || lv_procedure_name, 50);
891 ln_step := 5;
892
893 dbg('Exiting Action creation ..............') ;
894
895
896 EXCEPTION
897 when others then
898 lv_error_message := 'Error at step ' || ln_step || ' in ' ||
899 gv_package || lv_procedure_name;
900 dbg(lv_error_message || '-' || sqlerrm);
901 hr_utility.raise_error;
902
903 END action_creation;
904
905 /******************************************************************************
906 Name : archinit
907 Purpose : This procedure performs all the required initialization.
908 ******************************************************************************/
909 PROCEDURE archinit( p_payroll_action_id in number)
910 IS
911
912 ln_step NUMBER;
913 lv_procedure_name VARCHAR2(100) ;
914
915 BEGIN
916
917
918 dbg('Entering archinit .............');
919
920 gv_package := 'per_mx_ssaffl_salary' ;
921 gv_event_group := 'MX_SALARY_EVG' ;
922 g_ambiguous_error := '1' ; -- Multiple GRE found
923 g_missing_gre_error := '2' ; -- Location is not in the hierarchy';
924 g_action_salary_category := 'MX SS SALARY DETAILS' ;
925 g_debug_flag := 'Y' ;
926 -- g_concurrent_flag := 'Y' ;
927
928 lv_procedure_name := '.archinit';
929
930 hr_utility.set_location(gv_package || lv_procedure_name, 10);
931 ln_step := 1;
932
933 dbg('Exiting archinit .............');
934
935 END archinit;
936
937
938 /******************************************************************************
939 Name : get_rww_ind
940 Purpose : This function returns the reduced working week indicator
941 ******************************************************************************/
942
943 PROCEDURE get_rww_ind(p_workschedule in varchar2
944 ,p_rww_ind out nocopy varchar2)
945 is
946
947 cursor c_rww(cp_workschedule in varchar2) is
948 select sum(decode(to_number(puci.value),0,0,1))
949 from pay_user_column_instances_f puci,
950 pay_user_columns puc
951 where puc.user_column_name = cp_workschedule
952 and puc.legislation_code='MX'
953 and puc.user_column_id = puci.user_column_id ;
954
955 ln_rww number ;
956
957 BEGIN
958
959 if p_workschedule is not null then
960
961 open c_rww(p_workschedule) ;
962 fetch c_rww into ln_rww ;
963 close c_rww ;
964
965 if ln_rww = 7 then
966 -- can not be 7 if it is 7 then just assign it to 6
967 -- need to check the sum logic will it work for all the values
968 ln_rww := 6 ;
969 end if;
970
971 p_rww_ind := to_char(ln_rww) ;
972 else
973 p_rww_ind := null ;
974 end if;
975
976 END ;
977
978
979 /*************************************************************************
980 Name : archive_salary_details
981 Purpose : This procedure Archives salary details for the passed
982 assignment_action_id and assignment_id
983 **************************************************************************/
984 PROCEDURE archive_salary_details( p_assignment_action_id in number
985 ,p_assignment_id in number
986 ,p_effective_date in date
987 ,p_tax_unit_id in number
988 ,p_report_mode in varchar2
989 ,p_arch_status in varchar2
990 ,p_arch_reason in varchar2
991 )
992 IS
993 cursor c_get_salary_details (cp_assignment_id in number
994 , cp_effective_date in date )
995 is
996 select replace(ppf.per_information3,'-','') emp_ss_number
997 ,ppf.last_name paternal_last_name
998 ,per_information1 maternal_last_name
999 ,ppf.first_name || ' ' || ppf.middle_names name
1000 ,substr(employment_category,3,1) worker_type
1001 ,hsc.segment6 salary_type
1002 ,puc.user_column_name work_schedule
1003 ,per_information4 med_center
1004 ,employee_number worker_id
1005 ,national_identifier CURP
1006 from per_all_assignments_f paf,
1007 per_all_people_f ppf,
1008 hr_soft_coding_keyflex hsc,
1009 pay_user_columns puc
1010 where paf.assignment_id = cp_assignment_id
1011 and paf.person_id = ppf.person_id
1012 and paf.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id (+)
1013 and hsc.segment4 = puc.user_column_id(+)
1014 and trunc(cp_effective_date) between paf.effective_start_date and paf.effective_end_date
1015 and trunc(cp_effective_date) between ppf.effective_start_date and ppf.effective_end_date ;
1016
1017 cursor c_get_er_ss_number(cp_gre_id in number )
1018 is
1019 select replace(org_information1,'-','')
1020 from hr_organization_information
1021 where org_information_context= 'MX_SOC_SEC_DETAILS'
1022 and organization_id = cp_gre_id ;
1023
1024 cursor c_get_org_information ( cp_organization_id in number)
1025 is
1026 select org_information3,org_information5, org_information6
1027 from hr_organization_information
1028 where org_information_context= 'MX_SOC_SEC_DETAILS'
1029 and organization_id = cp_organization_id ;
1030
1031 -- cursor to get the minimum wage for Zone A
1032 cursor c_minimum_wage_zonea (cp_effective_date in date )
1033 is
1034 select legislation_info2
1035 from pay_mx_legislation_info_f
1036 where legislation_info_type='MX Minimum Wage Information'
1037 and legislation_info1='MWA'
1038 and cp_effective_date between effective_start_date and effective_end_date ;
1039
1040 lv_emp_ss_number varchar2(240);
1041 lv_er_ss_number varchar2(240);
1042 lv_paternal_last_name varchar2(240);
1043 lv_maternal_last_name varchar2(240);
1044 lv_name varchar2(240);
1045 lv_worker_type varchar2(240);
1046 lv_salary_type varchar2(240);
1047 lv_work_schedule varchar2(240);
1048 lv_rww_indicator varchar2(240);
1049 lv_med_center varchar2(240);
1050 lv_worker_id varchar2(240);
1051 lv_CURP varchar2(240);
1052 lv_type_of_tran varchar2(240);
1053 lv_imss_way_bill varchar2(240);
1054 lv_layout_identifier varchar2(240);
1055
1056 ln_idw NUMBER;
1057 ln_min_wage NUMBER;
1058
1059 lv_idw_mode VARCHAR2(20);
1060
1061 ln_fixed_idw NUMBER;
1062 ln_variable_idw NUMBER;
1063
1064 lv_transmitter VARCHAR2(1);
1065 ln_way_bill NUMBER;
1066 ln_tr_gre_id NUMBER;
1067
1068 ln_action_information_id NUMBER ;
1069 ln_object_version_number NUMBER ;
1070
1071 lv_procedure_name VARCHAR2(100) ;
1072 lv_error_message VARCHAR2(200);
1073 ln_step NUMBER;
1074
1075 BEGIN
1076
1077 lv_procedure_name := '.archive_salary_details';
1078
1079 dbg('Entering Archive Salary details.........');
1080
1081 lv_type_of_tran := '07' ;
1082 lv_layout_identifier := '9' ;
1083
1084
1085 ln_step := 1;
1086 hr_utility.set_location(gv_package || lv_procedure_name, 10);
1087
1088 dbg('Get employer ssid ');
1089 -- get employer ss id for p_tax_unit_id
1090 open c_get_er_ss_number(p_tax_unit_id) ;
1091 fetch c_get_er_ss_number into lv_er_ss_number ;
1092 close c_get_er_ss_number ;
1093
1094 ln_step := 2;
1095 hr_utility.set_location(gv_package || lv_procedure_name, 20);
1096
1097 dbg('Get IMSS waybill for gre '|| to_char(p_tax_unit_id) );
1098
1099 -- get IMSS Waybill for p_tax_unit_id
1100 open c_get_org_information ( p_tax_unit_id ) ;
1101 fetch c_get_org_information into lv_transmitter,
1102 ln_way_bill,
1103 ln_tr_gre_id ;
1104 close c_get_org_information ;
1105
1106 dbg('Transmitter flag for this GRE is '|| lv_transmitter);
1107
1108 if lv_transmitter = 'Y' then
1109 lv_imss_way_bill:= ln_way_bill ;
1110 else
1111
1112 dbg('Null or No then get the waybill number from the trans gre' );
1113 open c_get_org_information ( ln_tr_gre_id ) ;
1114 fetch c_get_org_information into lv_transmitter,
1115 ln_way_bill,
1116 ln_tr_gre_id ;
1117 lv_imss_way_bill:= ln_way_bill ;
1118 close c_get_org_information ;
1119 end if;
1120
1121 dbg('way bill number is ' || lv_imss_way_bill );
1122
1123 ln_step := 3;
1124 hr_utility.set_location(gv_package || lv_procedure_name, 30);
1125
1126 dbg('Get salary details from assignment ' );
1127 dbg('Assignment Id : ' || to_char(p_assignment_id) );
1128 dbg('Effective Date : ' || to_char(p_effective_date,'DD-MON-YYYY'));
1129
1130 -- get the asg details from the base table
1131 open c_get_salary_details(p_assignment_id
1132 ,p_effective_date ) ;
1133 fetch c_get_salary_details into
1134 lv_emp_ss_number
1135 ,lv_paternal_last_name
1136 ,lv_maternal_last_name
1137 ,lv_name
1138 ,lv_worker_type
1139 ,lv_salary_type
1140 ,lv_work_schedule
1141 ,lv_med_center
1142 ,lv_worker_id
1143 ,lv_CURP ;
1144
1145 close c_get_salary_details ;
1146
1147 ln_step := 4;
1148 hr_utility.set_location(gv_package || lv_procedure_name, 40);
1149
1150 dbg('Get reduced working week indicator from workschedule ' );
1151
1152 -- derive Reduced Working-week indicator from workschedule
1153 if lv_work_schedule is not null then
1154 get_rww_ind(lv_work_schedule,lv_rww_indicator );
1155 else
1156 lv_rww_indicator := null ;
1157 end if;
1158
1159
1160 ln_step := 5;
1161 hr_utility.set_location(gv_package || lv_procedure_name, 50);
1162
1163 dbg('Get IDW' );
1164
1165 if p_report_mode = 'F' then -- Fixed Salary
1166 lv_idw_mode := 'REPORT' ;
1167 elsif p_report_mode ='P' then -- Bimonthly Salary
1168 lv_idw_mode := 'BIMONTH_REPORT' ;
1169 end if;
1170
1171 dbg('Assignment Id '||to_char(p_assignment_id) );
1172 dbg('Tax unit Id '||to_char(p_tax_unit_id) );
1173 dbg('Effective Date '||to_char(p_effective_date) );
1174 dbg('Mode '||lv_idw_mode );
1175
1176
1177 ln_min_wage := 0 ;
1178
1179 -- get the minimum wage for Zone A
1180 /* bug fix 4528984 */
1181 open c_minimum_wage_zonea (p_effective_date) ;
1182 fetch c_minimum_wage_zonea into ln_min_wage ;
1183 close c_minimum_wage_zonea ;
1184
1185 dbg('Zone A Minimum Wage '||to_char(ln_min_wage) );
1186
1187 ln_idw := 0 ;
1188 ln_idw := pay_mx_ff_udfs.get_idw( p_assignment_id => p_assignment_id
1189 ,p_tax_unit_id => p_tax_unit_id
1190 ,p_effective_date => p_effective_date
1191 ,p_mode => lv_idw_mode
1192 ,p_fixed_idw => ln_fixed_idw
1193 ,p_variable_idw => ln_variable_idw
1194 ) ;
1195
1196 dbg('Calulated IDW from get_idw '||to_char(ln_idw) );
1197
1198 -- check the IDW with 25 times of zone A minimum wage
1199 -- if idw is greater than 25 times of zone A minimum wage then
1200 -- idw = 25 times of zone A minimum wage
1201 -- else
1202 -- idw = calculated one
1203 -- end if
1204
1205 if ln_idw > ( 25 * ln_min_wage ) then
1206 ln_idw := 25 * ln_min_wage ;
1207 end if;
1208
1209 dbg('IDW after compared with min wage '||to_char(ln_idw) );
1210
1211 -- round to 2 decimal and archive
1212 ln_idw := round(ln_idw,2) ;
1213
1214 dbg('IDW with 2 decimal '||to_char(ln_idw) );
1215
1216 ln_step := 6;
1217 hr_utility.set_location(gv_package || lv_procedure_name, 60);
1218
1219 dbg('call api to insert the record in pay action information with parameters' );
1220 msg('Action_information_category : ' || 'MX SS SALARY DETAILS' );
1221 msg('Action Context Id : ' || to_char(p_assignment_action_id) );
1222 msg('ER SS Number is : ' || lv_er_ss_number );
1223 msg('EE SS Number is : ' || lv_emp_ss_number );
1224 msg('Paternal Last Name : ' || lv_paternal_last_name );
1225 msg('Maternal Last Name : ' || lv_maternal_last_name );
1226 msg('Name : ' || lv_name );
1227 msg('IDW : ' || to_char(ln_idw) );
1228 msg('Worker Type : ' || lv_worker_type );
1229 msg('Salary Type : ' || lv_salary_type );
1230 msg('RWW Indicator : ' || lv_rww_indicator);
1231 msg('Salary Modification Date : ' || to_char(p_effective_date,'DDMMYYYY'));
1232 msg('transaction type : ' || lv_type_of_tran );
1233 msg('IMSS Waybill : ' || lv_imss_way_bill );
1234 msg('Worker ID : ' || lv_worker_id );
1235 msg('CURP : ' || lv_curp );
1236 msg('Layout Identifier : ' || lv_layout_identifier );
1237
1238
1239 -- call the api to insert the record in pay_action_information
1240 pay_action_information_api.create_action_information(
1241 p_action_information_id => ln_action_information_id
1242 ,p_object_version_number => ln_object_version_number
1243 ,p_action_information_category => 'MX SS SALARY DETAILS'
1244 ,p_action_context_id => p_assignment_action_id
1245 ,p_action_context_type => 'AAP'
1246 ,p_jurisdiction_code => null
1247 ,p_assignment_id => p_assignment_id
1248 ,p_tax_unit_id => p_tax_unit_id
1249 ,p_effective_date => p_effective_date
1250 ,p_action_information1 => substr(lv_er_ss_number,1,10)
1251 ,p_action_information2 => substr(lv_er_ss_number,length(lv_er_ss_number),1)
1252 ,p_action_information3 => substr(lv_emp_ss_number,1,10)
1253 ,p_action_information4 => substr(lv_emp_ss_number,length(lv_emp_ss_number),1)
1254 ,p_action_information5 => lv_paternal_last_name
1255 ,p_action_information6 => lv_maternal_last_name
1256 ,p_action_information7 => lv_name
1257 ,p_action_information8 => to_char(ln_idw)
1258 ,p_action_information9 => null -- filler1
1259 ,p_action_information10 => lv_worker_type
1260 ,p_action_information11 => lv_salary_type
1261 ,p_action_information12 => lv_rww_indicator
1262 ,p_action_information13 => to_char(p_effective_date,'DDMMYYYY')
1263 ,p_action_information15 => null -- filler2
1264 ,p_action_information16 => lv_type_of_tran
1265 ,p_action_information17 => lv_imss_way_bill
1266 ,p_action_information18 => lv_worker_id
1267 ,p_action_information19 => null -- filler3
1268 ,p_action_information20 => lv_curp
1269 ,p_action_information21 => lv_layout_identifier
1270 ,p_action_information22 => p_arch_status
1271 ,p_action_information23 => p_arch_reason
1272 );
1273
1274 msg('Successfully Archived. Action Information Id is : ' || to_char(ln_action_information_id) );
1275
1276 ln_step := 7;
1277 hr_utility.set_location(gv_package || lv_procedure_name, 70);
1278
1279 dbg('Exiting archive_salary_details .........');
1280
1281 EXCEPTION
1282 when others then
1283 lv_error_message := 'Error at step ' || ln_step || ' in ' ||
1284 gv_package || lv_procedure_name;
1285
1286 dbg(lv_error_message || '-' || sqlerrm);
1287
1288 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
1289 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
1290 hr_utility.raise_error;
1291
1292 END archive_salary_details ;
1293
1294
1295 /************************************************************
1296 Name : archive_data
1297 Purpose : This procedure Archives data
1298 ************************************************************/
1299 PROCEDURE archive_data(p_assignment_action_id in number
1300 ,p_effective_date in date)
1301 IS
1302
1303 cursor c_asg_action_info (cp_assignment_action in number) is
1304 select paa.payroll_action_id,
1305 paa.assignment_id,
1306 paa.tax_unit_id
1307 from pay_assignment_actions paa
1308 where paa.assignment_action_id = cp_assignment_action;
1309
1310
1311 cursor c_get_fix_sal_date( cp_assignment_id in number
1312 ,cp_business_group_id in number
1313 ,cp_start_date in date
1314 ,cp_end_date in date
1315 ,cp_event_group_id in number
1316 ) is
1317 select max(ppe.effective_date)
1318 from pay_process_events ppe,
1319 pay_datetracked_events pde,
1320 pay_event_updates peu,
1321 pay_element_entries_f pee,
1322 pay_element_types_f pet,
1323 pay_element_type_extra_info petei
1324 where ppe.assignment_id = cp_assignment_id
1325 and ppe.creation_date between cp_start_date and cp_end_date
1326 and peu.event_update_id =ppe.event_update_id
1327 and peu.dated_table_id = pde.dated_table_id
1328 and pde.event_group_id = cp_event_group_id
1329 and ppe.business_group_id = cp_business_group_id
1330 and nvl(peu.column_name,1) = nvl(pde.column_name,1)
1331 and decode(pde.update_type,'I','INSERT','U','UPDATE',pde.update_type) = peu.event_type
1332 and peu.change_type = 'DATE_EARNED'
1333 and pee.element_entry_id = ppe.surrogate_key
1334 and pet.element_type_id = pee.element_type_id
1335 and petei.element_type_id = pee.element_type_id
1336 and petei.eei_information_category='PQP_UK_RATE_TYPE'
1337 and petei.eei_information1='MX_IDWF'
1338 and ppe.effective_date between pee.effective_start_date and pee.effective_end_date
1339 group by ppe.assignment_id ;
1340
1341 cursor chk_asg_error( cp_assignment_id in number
1342 ,cp_effective_date in date )
1343 is
1344 select location_code ,
1345 assignment_number,
1346 per_mx_ssaffl_archive.derive_gre_from_loc_scl(paf.location_id,paf.business_group_id,paf.soft_coding_keyflex_id,cp_effective_date)
1347 from per_all_assignments_f paf,
1348 hr_locations hrl
1349 where paf.assignment_id = cp_assignment_id
1350 and cp_effective_date between paf.effective_start_date and paf.effective_end_date
1351 and hrl.location_id = paf.location_id ;
1352
1353
1354 lv_procedure_name VARCHAR2(100);
1355 lv_error_message VARCHAR2(200);
1356 ln_step NUMBER;
1357
1358 ln_payroll_action_id NUMBER;
1359 ln_assignment_action_id NUMBER;
1360 ln_assignment_iD NUMBER;
1361 ln_tax_unit_id NUMBER;
1362
1363 lv_report_mode VARCHAR2(1);
1364 ld_period_start_date DATE;
1365 ld_period_end_date DATE;
1366 ld_start_date DATE;
1367 ld_end_date DATE;
1368 ln_business_group_id NUMBER;
1369 ln_tran_gre_id NUMBER;
1370 ln_gre_id NUMBER;
1371 ln_id NUMBER;
1372 ln_event_group_id NUMBER;
1373 ld_effective_date DATE;
1374 lv_location_code VARCHAR2(100);
1375 lv_assignment_number VARCHAR2(100);
1376
1377 BEGIN
1378
1379 dbg('Entering archive data ...........');
1380 dbg('assignment action id is ' || to_char(p_assignment_action_id) );
1381
1382 lv_procedure_name := '.archive_data';
1383 hr_utility.set_location(gv_package || lv_procedure_name, 10);
1384 ln_step := 1;
1385
1386 dbg('Get Payroll Action Id ');
1387 open c_asg_action_info (p_assignment_action_id);
1388 fetch c_asg_action_info into ln_payroll_action_id,
1389 ln_assignment_id,
1390 ln_tax_unit_id ;
1391 close c_asg_action_info;
1392 dbg('Payroll action id' || to_char(ln_payroll_action_id) );
1393
1394 hr_utility.set_location(gv_package || lv_procedure_name, 20);
1395 ln_step := 2;
1396 dbg('Get parameter information from pay_payroll_actions table' ) ;
1397 get_payroll_action_info( p_payroll_action_id => ln_payroll_action_id
1398 ,p_report_mode => lv_report_mode
1399 ,p_period_start_date => ld_period_start_date
1400 ,p_period_end_date => ld_period_end_date
1401 ,p_start_date => ld_start_date
1402 ,p_end_date => ld_end_date
1403 ,p_business_group_id => ln_business_group_id
1404 ,p_tran_gre_id => ln_tran_gre_id
1405 ,p_gre_id => ln_gre_id
1406 ,p_event_group_id => ln_event_group_id);
1407
1408 hr_utility.set_location(gv_package || lv_procedure_name, 30);
1409 ln_step := 3;
1410
1411
1412 if lv_report_mode = 'F' then -- Fixed Salary
1413
1414 -- get the max effective date from ppe table
1415 open c_get_fix_sal_date ( ln_assignment_id
1416 ,ln_business_group_id
1417 ,ld_start_date
1418 ,ld_end_date
1419 ,ln_event_group_id ) ;
1420
1421 fetch c_get_fix_sal_date into ld_effective_date ;
1422
1423 close c_get_fix_sal_date ;
1424
1425
1426 elsif lv_report_mode = 'P' then -- Bimonthly Salary
1427
1428 -- set the effective_date = period_end_date
1429
1430 ld_effective_date := ld_period_end_date ;
1431
1432 end if ;
1433
1434 -- check to see the assignment has any error as of effective date
1435 -- ie check asg has the valid location to scl to derive the GRE
1436
1437 open chk_asg_error( ln_assignment_id
1438 ,ld_effective_date ) ;
1439
1440 fetch chk_asg_error into lv_location_code,lv_assignment_number, ln_id ;
1441
1442 if ln_id = -1 and ln_id = -2 then
1443
1444 pay_core_utils.push_token('LOC_CODE',lv_location_code) ;
1445 pay_core_utils.push_token('ASG_NUMBER',lv_assignment_number) ;
1446
1447 if ln_id = -1 then
1448 msg('Error : ' || lv_assignment_number || ' assignment ' || lv_location_code
1449 || ' location is assigned to multiple GREs in the Generic Hierarchy' );
1450 pay_core_utils.push_message(800,'HR_MX_GRE_AMBIGUOUS','F') ;
1451 else
1452
1453 msg('Error : ' || lv_assignment_number || ' assignment ' || lv_location_code
1454 || ' location is not assigned to a GRE in the Generic Hierarchy' );
1455 pay_core_utils.push_message(800,'HR_MX_LOC_MISSING_GEN_HIER','F') ;
1456
1457 end if;
1458
1459 else
1460 -- no error then call the archive_salary_details
1461
1462 -- call the archive_salary_details to insert into pay_action_information table
1463 archive_salary_details( p_assignment_action_id => p_assignment_action_id
1464 ,p_assignment_id => ln_assignment_id
1465 ,p_effective_date => ld_effective_date
1466 ,p_tax_unit_id => nvl(ln_gre_id,ln_tran_gre_id)
1467 ,p_report_mode => lv_report_mode
1468 ,p_arch_status => 'A'
1469 ,p_arch_reason => 'Archived'
1470 ) ;
1471
1472 end if;
1473
1474 hr_utility.set_location(gv_package || lv_procedure_name, 40);
1475
1476 dbg('Exiting archive data ...........');
1477
1478 EXCEPTION
1479 when others then
1480 lv_error_message := 'Error at step ' || ln_step || ' in ' ||
1481 gv_package || lv_procedure_name;
1482 dbg(lv_error_message || '-' || sqlerrm);
1483 hr_utility.raise_error;
1484
1485 end archive_data;
1486
1487
1488 --begin
1489 --hr_utility.trace_on (null, 'SSAFFLSAL');
1490
1491 end pay_mx_ssaffl_salary;