1 PACKAGE BODY PER_MX_SSAFFL_ARCHIVE AS
2 /* $Header: pemxafar.pkb 120.2 2006/08/04 10:05:24 sbairagi 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_archive
21
22 Description : This package is used by the Social Security Affiliation
23 Archive process to archive the Hire/Rehire, Termination
24 Affiliation records in pay_action_information table.
25
26 Change List
27 -----------
28 Date Name Vers Bug No Description
29 ----------- ---------- ------ ------- --------------------------
30 03-MAY-2004 kthirmiy 115.0 Created.
31 17-MAY-2004 kthirmiy 115.1 Changed to get gre from location
32 when gre_id from soft_coding_keyflex_id
33 is null.
34 11-JUN-2004 kthirmiy 115.2 Changed to check both tran_gre_id and
35 gre_id is not null
36 17-JUN-2004 kthirmiy 115.3 If both tran_gre_id and gre_id
37 is not null then use gre_id.
38 If tran_gre_id is not null and
39 gre_id is null then use trans_gre_id
40 in the c_get_asg query in
41 action_creation.
42 17-JUN-2004 kthirmiy 115.4 removed substr and archiving names
43 all the characters and dispmag mag report
44 will substr to put 27 chars in the tape.
45 28-JUN-2004 kthirmiy 115.5 3722795 Name changed to archive as
46 First_name || ' '||middle_names
47 29-Jun-2004 kthirmiy 115.6 set the g_concurrent_flag to yes
48 01-Jul-2004 kthirmiy 115.7 changed lv_assignment_number
49 from Number to VARCHAR2(30)
50 14-Jul-2004 kthirmiy 115.8 fixed the message to show correctly
51 16-Jul-2004 kthirmiy 115.9 Added logic to remove the multiple
52 correction records in the
53 interpret_all_asg_events.
54 Added start_date to get it from the
55 pay_payroll_actions table in the
56 get_payroll_action_info procedure.
57 Added a new function get_start_date
58 to return the archive_start_date
59 from the passed parameter legal employer,
60 transmitter gre and gre.
61 19-Jul-2004 kthirmiy 115.10 3773620 changed to get the separation date as
62 actual termination date when archiving
63 separation details for termination.
64 27-Jul-2004 kthirmiy 115.11 Added to get the values from the table.
65 02-Aug-2004 kthirmiy 115.12 3797326 Changed interpret_all_asg_events procedure
66 to handle the reverse termination issue.
67 Now the event group has column
68 assignment_status_type_id to track the changes.
69 Renamed write_arch_plsql_table procedure to
70 arch_hire_separation_data.
71 Also moved the archive hire or separation
72 details from archive_data to
73 arch_hire_separation_data
74 05-Aug-2004 kthrimiy 115.13 3814482 changed process_end_date procedure
75 to handle term and rev term issue
76 26-Aug-2004 kthirmiy 115.14 3856502 changed to archive jurisdiction
77 column as blank.
78 01-Dec-2004 kthirmiy 115.15 Added get_idw function call in
79 archive_hire_details procedure to
80 get the idw.
81 02-Dec-2004 kthirmiy 115.16 round idw to 2 decimal and archive
82 03-Jan-2005 kthirmiy 115.17 4084628 IDW is limited to 25 times of minimum wages
83 of Zone A for reporting purposes
84 07-Jan-2005 kthirmiy 115.18 4104743 Default Implementation date is derived from
85 pay_mx_legislation_info_f table.
86 07-Jan-2005 kthirmiy 115.19 fixed gscc error
87 20-Jan-2005 ardsouza 115.20 4129001 Added p_business_group_id parameter to
88 procedure "derive_gre_from_loc_scl".
89 06-May-2005 kthirmiy 115.21 4353084 removed the redundant use of bind variable
90 payroll_action_id
91 01-Aug-2005 kthirmiy 115.22 4528984 Added where condition to get the correct
92 minimum wage based on the effective_date
93 02-AUG-2006 sbairagi 115.23 4872076 CURSOR c_chk_dyn_triggers_enabled od procedure
94 range_cursor is tuned.
95 ******************************************************************************/
96
97
98 --
99 -- < PRIVATE GLOBALS > ---------------------------------------------------
100 --
101
102 gv_package VARCHAR2(100) ;
103
104 gv_event_group VARCHAR2(40) ;
105
106 g_ambiguous_error VARCHAR2(100) ;
107 g_missing_gre_error VARCHAR2(100) ;
108
109 g_report_imp_date DATE ;
110
111 g_event_group_id NUMBER ;
112
113 g_action_hire_category VARCHAR2(100) ;
114 g_action_sep_category VARCHAR2(100) ;
115
116 -- flag to write the debug messages in the concurrent program log file
117 g_concurrent_flag VARCHAR2(1) ;
118 -- flag to write the debug messages in the trace file
119 g_debug_flag VARCHAR2(1) ;
120
121
122
123 /******************************************************************************
124 Name : msg
125 Purpose : Log a message, either using fnd_file, or hr_utility.trace
126 ******************************************************************************/
127
128 PROCEDURE msg(p_text VARCHAR2)
129 IS
130 --
131 BEGIN
132 -- Write to the concurrent request log
133 fnd_file.put_line(fnd_file.log, p_text);
134
135 END msg;
136
137 /******************************************************************************
138 Name : dbg
139 Purpose : Log a message, either using fnd_file, or hr_utility.trace
140 if debuggging is enabled
141 ******************************************************************************/
142 PROCEDURE dbg(p_text VARCHAR2) IS
143
144 BEGIN
145
146 IF (g_debug_flag = 'Y') THEN
147 IF (g_concurrent_flag = 'Y') THEN
148 -- Write to the concurrent request log
149 fnd_file.put_line(fnd_file.log, p_text);
150 ELSE
151 -- Use HR trace
152 hr_utility.trace(p_text);
153 END IF;
154 END IF;
155
156 END dbg;
157
158
159 /******************************************************************************
160 Name : get_default_imp_date
161 Purpose : This function returns the default implementation date
162 from pay_mx_legislation_info_f table
163 ******************************************************************************/
164 FUNCTION get_default_imp_date
165 RETURN VARCHAR2
166 IS
167
168 cursor c_get_def_imp_date
169 is
170 select fnd_date.canonical_to_date(legislation_info1)
171 from pay_mx_legislation_info_f
172 where legislation_info_type='MX Social Security Reporting' ;
173
174 ld_def_date date ;
175
176 begin
177
178 open c_get_def_imp_date ;
179 fetch c_get_def_imp_date into ld_def_date ;
180 close c_get_def_imp_date;
181
182 return fnd_date.date_to_canonical(ld_def_date) ;
183
184 end get_default_imp_date;
185
186
187 /******************************************************************************
188 Name : get_start_date
189 Purpose : This function returns the archive start date based on the parameters
190 1) Get the report implementation date for the legal employer id
191 2) If it is null then default
192 report imp date = default implementation date from
193 mx pay legislation info f table
194 3) Get the Last time archive process ran date for a tax unit id
195 4) If it is null then first time running the report
196 so default it to report imp date
197 Note : This function is called from the conc program
198 Social Security Affiliation Data Archive Process
199 parameter conc_start_date
200 ******************************************************************************/
201 FUNCTION get_start_date( p_legal_emp_id in varchar2
202 ,p_tran_gre_id in varchar2
203 ,p_gre_id in varchar2
204 ) RETURN VARCHAR2
205 IS
206
207 cursor c_get_start_date(cp_tax_unit_id in number)
208 is
209 select fnd_date.canonical_to_date(ltrim(rtrim(substr(ppa.legislative_parameters,
210 instr(ppa.legislative_parameters,
211 'END_DATE=')
212 + length('END_DATE='),
213 (instr(ppa.legislative_parameters,
214 'LEGAL_EMPLOYER=') - 1 )
215 - (instr(ppa.legislative_parameters,
216 'END_DATE=')
217 + length('END_DATE='))))))
218 from pay_assignment_actions paa,
219 pay_payroll_actions ppa
220 where paa.tax_unit_id = cp_tax_unit_id
221 and ppa.payroll_action_id=paa.payroll_action_id
222 and ppa.report_type='SS_AFFILIATION'
223 and ppa.report_qualifier ='IMSS'
224 order by paa.payroll_action_id desc ;
225
226
227 cursor c_get_imp_date(cp_organization_id in number)
228 is
229 select fnd_date.canonical_to_date(org_information6)
230 from hr_organization_information
231 where org_information_context= 'MX_TAX_REGISTRATION'
232 and organization_id = cp_organization_id ;
233
234 ld_report_imp_date date ;
235 ld_start_date date ;
236 lv_start_date varchar2(50);
237 ln_tax_unit_id NUMBER;
238
239 begin
240
241 -- get the report Implementation Date from p_legal_emp_id
242 open c_get_imp_date(to_number(p_legal_emp_id)) ;
243 fetch c_get_imp_date into ld_report_imp_date ;
244 if c_get_imp_date%notfound then
245 -- defaulting to Report Implementation Date from mx pay legislation info table
246 ld_report_imp_date := fnd_date.canonical_to_date(get_default_imp_date) ;
247 end if;
248 close c_get_imp_date;
249
250 if p_gre_id is not null then
251 ln_tax_unit_id := to_number(p_gre_id) ;
252 else
253 ln_tax_unit_id := to_number(p_tran_gre_id) ;
254 end if ;
255
256 open c_get_start_date(ln_tax_unit_id);
257 fetch c_get_start_date into ld_start_date ;
258 if c_get_start_date%notfound then
259 -- assign the ld_start_date from rep imp date
260 ld_start_date := ld_report_imp_date ;
261 end if;
262 close c_get_start_date;
263
264 lv_start_date := fnd_date.date_to_canonical(ld_start_date) ;
265
266 return lv_start_date ;
267
268 end get_start_date;
269
270
271 /******************************************************************************
272 Name : get_payroll_action_info
273 Purpose : This procedue returns the Payroll Action level parameter
274 information for SS Affiliation Archiver.
275 Arguments : p_payroll_action_id - Payroll_Action_id of archiver
276 p_start_date - Start date of Archiver
277 p_end_date - End date of Archiver
278 p_business_group_id - Business Group ID
279 p_tran_gre_id - Transmiter GRE Id
280 p_gre_id - GRE Id
281 p_event_group_id - Event Group Id
282 ******************************************************************************/
283 PROCEDURE get_payroll_action_info(p_payroll_action_id in number
284 ,p_start_date out nocopy date
285 ,p_end_date out nocopy date
286 ,p_business_group_id out nocopy number
287 ,p_tran_gre_id out nocopy number
288 ,p_gre_id out nocopy number
289 ,p_event_group_id out nocopy number
290 )
291 IS
292 -- cursor to get all the parameters from pay_payroll_actions table
293
294 cursor c_payroll_Action_info(cp_payroll_action_id in number) is
295 select business_group_id,
296 to_number(substr(legislative_parameters,
297 instr(legislative_parameters,
298 'GRE_ID=')
299 + length('GRE_ID='))) , -- gre_id
300 to_number(ltrim(rtrim(substr(legislative_parameters,
301 instr(legislative_parameters,
302 'TRANS_GRE=')
303 + length('TRANS_GRE='),
304 (instr(legislative_parameters,
305 'GRE_ID=') - 1 )
306 - (instr(legislative_parameters,
307 'TRANS_GRE=')
308 + length('TRANS_GRE=')))))) , -- trans_gre
309
310 to_number(ltrim(rtrim(substr(legislative_parameters,
311 instr(legislative_parameters,
312 'LEGAL_EMPLOYER=')
313 + length('LEGAL_EMPLOYER='),
314 (instr(legislative_parameters,
315 'TRANS_GRE=') - 1 )
316 - (instr(legislative_parameters,
317 'LEGAL_EMPLOYER=')
318 + length('LEGAL_EMPLOYER=')))))) , -- legal_employer
319
320 fnd_date.canonical_to_date(ltrim(rtrim(substr(legislative_parameters,
321 instr(legislative_parameters,
322 'END_DATE=')
323 + length('END_DATE='),
324 (instr(legislative_parameters,
325 'LEGAL_EMPLOYER=') - 1 )
326 - (instr(legislative_parameters,
327 'END_DATE=')
328 + length('END_DATE=')))))), -- end_date
329
330 fnd_date.canonical_to_date(ltrim(rtrim(substr(legislative_parameters,
331 instr(legislative_parameters,
332 'START_DATE=')
333 + length('START_DATE='),
334 (instr(legislative_parameters,
335 'END_DATE=') - 1 )
336 - (instr(legislative_parameters,
337 'START_DATE=')
338 + length('START_DATE=')))))) -- start_date
339 from pay_payroll_actions
340 where payroll_action_id = cp_payroll_action_id;
341
342 cursor c_get_imp_date(cp_organization_id in number)
343 is
344 select fnd_date.canonical_to_date(org_information6)
345 from hr_organization_information
346 where org_information_context= 'MX_TAX_REGISTRATION'
347 and organization_id = cp_organization_id ;
348
349
350 cursor c_get_event_group (cp_event_group_name in varchar2) is
351 select event_group_id
352 from pay_event_groups
353 where event_group_name = cp_event_group_name ;
354
355 ld_end_date DATE;
356 ld_start_date DATE;
357 ln_business_group_id NUMBER;
358 ln_tran_gre_id NUMBER;
359 ln_gre_id NUMBER;
360
361 ln_tax_unit_id NUMBER;
362 ln_legal_emp_id NUMBER;
363 ln_event_group_id NUMBER;
364
365 ld_report_imp_date DATE;
366
367 lv_procedure_name VARCHAR2(100) ;
368 lv_error_message VARCHAR2(200) ;
369 ln_step NUMBER;
370
371 BEGIN
372
373 lv_procedure_name := '.get_payroll_action_info';
374
375 hr_utility.set_location(gv_package || lv_procedure_name, 10);
376 ln_step := 1;
377 dbg('Entering get_payroll_action_info .......');
378
379 -- open the cursor to get all the parameters from pay_payroll_actions table
380 open c_payroll_action_info(p_payroll_action_id);
381 fetch c_payroll_action_info into ln_business_group_id,
382 ln_gre_id,
383 ln_tran_gre_id,
384 ln_legal_emp_id,
385 ld_end_date,
386 ld_start_date
387 ;
388 close c_payroll_action_info;
389
390 -- get the report Implementation Date from ln_legal_emp_id and set it to the
391 -- global variable g_report_imp_date
392 hr_utility.set_location(gv_package || lv_procedure_name, 20);
393 ln_step := 2;
394 dbg('Get report Impl date for Legal employer id ' ||to_char(ln_legal_emp_id) );
395
396 open c_get_imp_date(ln_legal_emp_id) ;
397 fetch c_get_imp_date into ld_report_imp_date ;
398 if c_get_imp_date%notfound then
399 dbg('WARNING : Report Implementaton date is not entered for legal employer ' );
400 dbg('so defaulting to Report Implementation Date from pay mx legislation info table');
401 ld_report_imp_date := fnd_date.canonical_to_date(get_default_imp_date) ;
402 end if;
403 close c_get_imp_date;
404 dbg('report impl date is '||to_char(ld_report_imp_date) );
405 g_report_imp_date := ld_report_imp_date;
406
407 hr_utility.set_location(gv_package || lv_procedure_name, 40);
408 ln_step := 3;
409 dbg('Get Event Group Id ' );
410
411 open c_get_event_group(gv_event_group) ;
412 fetch c_get_event_group into ln_event_group_id ;
413 close c_get_event_group ;
414
415 p_start_date := ld_start_date;
416 p_end_date := ld_end_date;
417 p_business_group_id := ln_business_group_id;
418 p_tran_gre_id := ln_tran_gre_id;
419 p_gre_id := ln_gre_id;
420 p_event_group_id := ln_event_group_id ;
421
422 dbg('Parameters.....');
423 dbg('start date : ' || fnd_date.date_to_canonical(p_start_date)) ;
424 dbg('end date : ' || fnd_date.date_to_canonical(p_end_date)) ;
425 dbg('bus group id : ' || to_char(p_business_group_id)) ;
426 dbg('trans gre id : ' || to_char(p_tran_gre_id)) ;
427 dbg('gre id : ' || to_char(p_gre_id)) ;
428 dbg('event group id : ' || to_char(p_event_group_id) );
429
430 hr_utility.set_location(gv_package || lv_procedure_name, 40);
431 ln_step := 4;
432
433 dbg('Exiting get_payroll_action_info .......');
434
435 EXCEPTION
436 when others then
437 lv_error_message := 'Error at step ' || ln_step || ' in ' ||
438 gv_package || lv_procedure_name;
439
440 dbg(lv_error_message || '-' || sqlerrm);
441 hr_utility.raise_error;
442
443 END get_payroll_action_info;
444
445
446 /******************************************************************
447 Name : range_cursor
448 Purpose : This returns the select statement that is
449 used to created the range rows for the
450 Social Security Affiliation Archiver.
451 Notes : Calls procedure - get_payroll_action_info
452 ******************************************************************/
453 PROCEDURE range_cursor( p_payroll_action_id in number
454 ,p_sqlstr out nocopy varchar2)
455 IS
456
457 CURSOR c_chk_dyn_triggers_enabled(cp_func_area in VARCHAR2)
458 IS
459 select /*+INDEX(PFA PAY_FUNCTIONAL_AREAS_PK)
460 INDEX(PTE PAY_TRIGGER_EVENTS_PK)*/
461 pte.short_name
462 from pay_functional_areas pfa,
463 pay_functional_triggers pft,
464 pay_trigger_events pte
465 where pfa.short_name = cp_func_area
466 and pfa.area_id = pft.area_id
467 and pft.event_id = pte.event_id
468 and ( pte.generated_flag <> 'Y' or pte.enabled_flag <> 'Y' ) ;
469
470 ld_start_date DATE;
471 ld_end_date DATE;
472 ln_business_group_id NUMBER;
473 ln_tran_gre_id NUMBER;
474 ln_gre_id NUMBER;
475 ln_event_group_id NUMBER;
476
477 lv_sql_string VARCHAR2(32000);
478 lv_procedure_name VARCHAR2(100) ;
479
480 lv_func_area VARCHAR2(40);
481 lv_trigger_name VARCHAR2(100);
482 BEGIN
483
484 dbg('Entering range_cursor ....... ') ;
485
486 gv_package := 'per_mx_ssaffl_archive' ;
487 gv_event_group := 'MX_HIRE_SEPARATION_EVG' ;
488 g_ambiguous_error := '1' ; -- Multiple GRE found
489 g_missing_gre_error := '2' ; -- Location is not in the hierarchy';
490 g_debug_flag := 'Y' ;
491 -- g_concurrent_flag := 'Y' ;
492
493 lv_procedure_name := '.range_cursor';
494 hr_utility.set_location(gv_package || lv_procedure_name, 10);
495
496 lv_func_area := 'SS Affiliation Events' ;
497
498 -- Get all the parameter information from pay_payroll_actions table
499 dbg('Get parameter information from pay_payroll_actions table' ) ;
500
501 get_payroll_action_info(p_payroll_action_id => p_payroll_action_id
502 ,p_start_date => ld_start_date
503 ,p_end_date => ld_end_date
504 ,p_business_group_id => ln_business_group_id
505 ,p_tran_gre_id => ln_tran_gre_id
506 ,p_gre_id => ln_gre_id
507 ,p_event_group_id => ln_event_group_id);
508
509 hr_utility.set_location(gv_package || lv_procedure_name, 20);
510
511 -- Check the dynamic triggers are enable for functional area
512 dbg('Check dynamic triggers enabled' ) ;
513
514 open c_chk_dyn_triggers_enabled(lv_func_area );
515 fetch c_chk_dyn_triggers_enabled into lv_trigger_name ;
516
517 if c_chk_dyn_triggers_enabled%found then
518 close c_chk_dyn_triggers_enabled;
519
520 dbg('Error : Dynamic triggers NOT enabled' ) ;
521 lv_sql_string := null;
522
523 hr_utility.raise_error;
524
525 else
526
527 dbg('Dynamic triggers Enabled' ) ;
528 close c_chk_dyn_triggers_enabled ;
529
530 lv_sql_string := 'select distinct ppe.assignment_id
531 from pay_process_events ppe,
532 pay_event_updates peu,
533 pay_datetracked_events pde
534 where ppe.creation_date between
535 fnd_date.canonical_to_date(''' || fnd_date.date_to_canonical(ld_start_date) || ''')
536 and fnd_date.canonical_to_date(''' || fnd_date.date_to_canonical(ld_end_date) || ''')
537 and ppe.event_update_id = peu.event_update_id
538 and peu.dated_table_id = pde.dated_table_id
539 and pde.event_group_id = ''' ||ln_event_group_id || '''
540 and ppe.business_group_id = ''' ||ln_business_group_id || '''
541 and (( decode(peu.column_name,''EFFECTIVE_END_DATE'',''1'') = nvl(pde.column_name,''1'')
542 and decode(peu.event_type,''U'',''E'')=pde.update_type )
543 or ( nvl(peu.column_name,1) = nvl(pde.column_name,1)
544 and peu.event_type=pde.update_type ))
545 and :payroll_action_id > 0 ' ;
546
547 end if;
548
549 hr_utility.set_location(gv_package || lv_procedure_name, 30);
550 p_sqlstr := lv_sql_string;
551 hr_utility.set_location(gv_package || lv_procedure_name, 40);
552
553 dbg('Exiting range_cursor .......') ;
554
555 END range_cursor;
556
557 /************************************************************
558 Name : derive_gre_from_loc_scl
559 Purpose : This function derives the gre from the parmeters
560 Location, BG and SCL(soft coding keyflex id)
561 ************************************************************/
562 FUNCTION derive_gre_from_loc_scl(
563 p_location_id in number
564 ,p_business_group_id in number
565 ,p_soft_coding_keyflex_id in number
566 ,p_effective_date in date ) RETURN NUMBER
567 IS
568
569 ln_gre_id NUMBER;
570 l_is_ambiguous BOOLEAN ;
571 l_missing_gre BOOLEAN ;
572
573 BEGIN
574
575 if p_soft_coding_keyflex_id is not null then
576 -- get the gre_id using scl
577 ln_gre_id := hr_mx_utility.get_gre_from_scl(p_soft_coding_keyflex_id) ;
578
579 end if;
580
581 if ln_gre_id is null then
582 -- get the gre_id using location
583 ln_gre_id := hr_mx_utility.get_gre_from_location(
584 p_location_id,
585 p_business_group_id,
586 p_effective_date,
587 l_is_ambiguous,
588 l_missing_gre ) ;
589 if ln_gre_id is null then
590 -- set the error message
591 if l_is_ambiguous then
592 ln_gre_id := -1 ;
593 end if;
594 if l_missing_gre then
595 ln_gre_id := -2 ;
596 end if;
597 end if;
598
599 end if;
600
601 return (ln_gre_id) ;
602
603 END derive_gre_from_loc_scl ;
604
605
606
607 /************************************************************
608 Name : action_creation
609 Purpose : This creates the assignment actions for
610 a specific chunk of people to be archived
611 by the SS Affiliation Archiver process.
612 Notes : Calls procedure - get_payroll_action_info
613 ************************************************************/
614 PROCEDURE action_creation(
615 p_payroll_action_id in number
616 ,p_start_assignment_id in number
617 ,p_end_assignment_id in number
618 ,p_chunk in number)
619 IS
620
621 cursor c_get_asg( cp_start_assignment_id in number
622 ,cp_end_assignment_id in number
623 ,cp_tran_gre_id in number
624 ,cp_gre_id in number
625 ,cp_business_group_id in number
626 ,cp_start_date in date
627 ,cp_end_date in date
628 ,cp_event_group_id in number
629 ) is
630 select distinct ppe.assignment_id
631 -- ,paf.person_id,
632 -- ,per_mx_ssaffl_archive.derive_gre_from_loc_scl(paf.location_id,paf.business_group_id,paf.soft_coding_keyflex_id,ppe.effective_date)
633 from pay_process_events ppe,
634 pay_event_updates peu,
635 pay_datetracked_events pde,
636 per_all_assignments_f paf
637 where ppe.creation_date between cp_start_date and cp_end_date
638 and peu.event_update_id = ppe.event_update_id
639 and ppe.business_group_id = cp_business_group_id
640 and pde.dated_table_id = peu.dated_table_id
641 and pde.event_group_id = cp_event_group_id
642 and (( decode(peu.column_name,'EFFECTIVE_END_DATE','1') = nvl(pde.column_name,'1')
643 and decode(peu.event_type,'U','E')=pde.update_type )
644 or ( nvl(peu.column_name,1) = nvl(pde.column_name,1)
645 and peu.event_type=pde.update_type ) )
646 and paf.assignment_id = ppe.assignment_id
647 and paf.assignment_id between cp_start_assignment_id and cp_end_assignment_id
648 and ppe.effective_date between paf.effective_start_date and paf.effective_end_date
649 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 )
650 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 )
651 or ( cp_tran_gre_id is not null and cp_gre_id is not null and
652 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 )
653 or ( cp_tran_gre_id is not null and cp_gre_id is null and
654 per_mx_ssaffl_archive.derive_gre_from_loc_scl(paf.location_id,paf.business_group_id,paf.soft_coding_keyflex_id,ppe.effective_date)
655 in
656 (select organization_id
657 from hr_organization_information hoi
658 where hoi.org_information_context = 'MX_SOC_SEC_DETAILS'
659 and ((org_information6 = cp_tran_gre_id ) OR ( organization_id = cp_tran_gre_id and org_information3='Y'))))) ;
660
661
662 ld_start_date DATE;
663 ld_end_date DATE;
664 ln_business_group_id NUMBER;
665 ln_tran_gre_id NUMBER;
666 ln_gre_id NUMBER;
667 ln_person_id NUMBER;
668 ln_tax_unit_id NUMBER;
669 ln_event_group_id NUMBER;
670 ln_assignment_id NUMBER;
671 ln_action_id NUMBER;
672 lv_procedure_name VARCHAR2(100) ;
673 lv_error_message VARCHAR2(200);
674 ln_step NUMBER;
675
676 begin
677
678 dbg('Entering Action creation ..............') ;
679
680 gv_package := 'per_mx_ssaffl_archive' ;
681 gv_event_group := 'MX_HIRE_SEPARATION_EVG' ;
682 g_debug_flag := 'Y' ;
683 -- g_concurrent_flag := 'Y' ;
684
685 lv_procedure_name := '.action_creation';
686
687 hr_utility.set_location(gv_package || lv_procedure_name, 10);
688 ln_step := 1;
689 dbg('Get parameter information from pay_payroll_actions table' ) ;
690
691 get_payroll_action_info(p_payroll_action_id => p_payroll_action_id
692 ,p_start_date => ld_start_date
693 ,p_end_date => ld_end_date
694 ,p_business_group_id => ln_business_group_id
695 ,p_tran_gre_id => ln_tran_gre_id
696 ,p_gre_id => ln_gre_id
697 ,p_event_group_id => ln_event_group_id
698 );
699
700
701 hr_utility.set_location(gv_package || lv_procedure_name, 20);
702 ln_step := 2;
703 dbg('Action creation Query parameters') ;
704 dbg('Start assignment id : ' || to_char(p_start_assignment_id));
705 dbg('End assignment id : ' || to_char(p_end_assignment_id));
706 dbg('tansmitter gre id : ' || to_char(ln_tran_gre_id));
707 dbg('gre id : ' || to_char(ln_gre_id));
708 dbg('event group id : ' || to_char(ln_event_group_id));
709 dbg('business_group_id : ' || to_char(ln_business_group_id));
710 dbg('start date is : ' || fnd_date.date_to_canonical(ld_start_date)) ;
711 dbg('end date is : ' || fnd_date.date_to_canonical(ld_end_date)) ;
712
713 open c_get_asg( p_start_assignment_id
714 ,p_end_assignment_id
715 ,ln_tran_gre_id
716 ,ln_gre_id
717 ,ln_business_group_id
718 ,ld_start_date
719 ,ld_end_date
720 ,ln_event_group_id);
721
722 -- Loop for all rows returned for SQL statement.
723 hr_utility.set_location(gv_package || lv_procedure_name, 30);
724 ln_step := 3;
725
726 loop
727 fetch c_get_asg into ln_assignment_id ;
728 exit when c_get_asg%notfound;
729
730 -- if gre_id is not null then tax_unit_id= gre_id
731 -- if tran_gre_id is not null then tax_unit_id = tran_gre_id
732
733 hr_utility.set_location(gv_package || lv_procedure_name, 40);
734 ln_step := 4;
735 dbg('creating aaid for assignment_id = ' || to_char(ln_assignment_id) ||
736 ' Tax Unit Id = ' || to_char(nvl(ln_gre_id,ln_tran_gre_id)) ) ;
737
738 select pay_assignment_actions_s.nextval
739 into ln_action_id
740 from dual;
741
742
743 -- insert into pay_assignment_actions.
744 hr_nonrun_asact.insact(ln_action_id,
745 ln_assignment_id,
746 p_payroll_action_id,
747 p_chunk,
748 nvl(ln_gre_id,ln_tran_gre_id),
749 null,
750 'U',
751 null);
752
753 dbg('assignment action id is ' || to_char(ln_action_id) );
754
755 end loop;
756 close c_get_asg;
757
758 hr_utility.set_location(gv_package || lv_procedure_name, 50);
759 ln_step := 5;
760
761 dbg('Exiting Action creation ..............') ;
762
763
764 EXCEPTION
765 when others then
766 lv_error_message := 'Error at step ' || ln_step || ' in ' ||
767 gv_package || lv_procedure_name;
768 dbg(lv_error_message || '-' || sqlerrm);
769 hr_utility.raise_error;
770
771 END action_creation;
772
773 /******************************************************************************
774 Name : archinit
775 Purpose : This procedure performs all the required initialization.
776 ******************************************************************************/
777 PROCEDURE archinit( p_payroll_action_id in number)
778 IS
779
780 ln_step NUMBER;
781 lv_procedure_name VARCHAR2(100) ;
782
783 BEGIN
784
785
786 dbg('Entering archinit .............');
787
788 gv_package := 'per_mx_ssaffl_archive' ;
789 gv_event_group := 'MX_HIRE_SEPARATION_EVG' ;
790 g_ambiguous_error := '1' ; -- Multiple GRE found
791 g_missing_gre_error := '2' ; -- Location is not in the hierarchy';
792 g_action_hire_category := 'MX SS HIRE DETAILS' ;
793 g_action_sep_category := 'MX SS SEPARATION DETAILS';
794 g_debug_flag := 'Y' ;
795 -- g_concurrent_flag := 'Y' ;
796
797 lv_procedure_name := '.archinit';
798
799 hr_utility.set_location(gv_package || lv_procedure_name, 10);
800 ln_step := 1;
801
802 dbg('Exiting archinit .............');
803
804 END archinit;
805
806 /*****************************************************************************
807 Name : chk_active_asg_exists
808 Purpose : This function check any active assignment exists for the passed
809 parameters and return the flag.
810 ******************************************************************************/
811 FUNCTION chk_active_asg_exists(
812 p_skip_assignment_id in number
813 ,p_person_id in number
814 ,p_gre_id in number
815 ,p_effective_date in date
816 ) RETURN VARCHAR2
817 IS
818
819 cursor c_get_active_asg(cp_skip_assignment_id in number
820 ,cp_person_id in number
821 ,cp_gre_id in number
822 ,cp_effective_date in date )
823 IS
824 select 'Y'
825 from per_all_assignments_f paf
826 where paf.assignment_id <> cp_skip_assignment_id
827 and paf.person_id = cp_person_id
828 and cp_effective_date between paf.effective_start_date and paf.effective_end_date
829 and per_mx_ssaffl_archive.derive_gre_from_loc_scl(paf.location_id,paf.business_group_id,paf.soft_coding_keyflex_id,cp_effective_date)
830 = cp_gre_id ;
831
832 lv_flag VARCHAR2(1);
833
834 BEGIN
835
836 lV_flag := 'N' ;
837 open c_get_active_asg(p_skip_assignment_id
838 ,p_person_id
839 ,p_gre_id
840 ,p_effective_date );
841 fetch c_get_active_asg into lv_flag ;
842 if c_get_active_asg%notfound then
843 lv_flag :='N' ;
844 end if;
845 close c_get_active_asg ;
846
847 RETURN (lv_flag) ;
848
849 END chk_active_asg_exists ;
850
851
852 /******************************************************************************
853 Name : get_rww_ind
854 Purpose : This function returns the reduced working week indicator
855 ******************************************************************************/
856
857 PROCEDURE get_rww_ind(p_workschedule in varchar2
858 ,p_rww_ind out nocopy varchar2)
859 is
860
861 cursor c_rww(cp_workschedule in varchar2) is
862 select sum(decode(to_number(puci.value),0,0,1))
863 from pay_user_column_instances_f puci,
864 pay_user_columns puc
865 where puc.user_column_name = cp_workschedule
866 and puc.legislation_code='MX'
867 and puc.user_column_id = puci.user_column_id ;
868
869 ln_rww number ;
870
871 BEGIN
872
873 if p_workschedule is not null then
874
875 open c_rww(p_workschedule) ;
876 fetch c_rww into ln_rww ;
877 close c_rww ;
878
879 if ln_rww = 7 then
880 -- can not be 7 if it is 7 then just assign it to 6
881 -- need to check the sum logic will it work for all the values
882 ln_rww := 6 ;
883 end if;
884
885 p_rww_ind := to_char(ln_rww) ;
886 else
887 p_rww_ind := null ;
888 end if;
889
890 END ;
891
892
893 /************************************************************************
894 Name : archive_sep_details
895 Purpose : This procedure Archives separation details for the passed
896 assignment_action_id and assignment_id
897 *************************************************************************/
898 PROCEDURE archive_sep_details( p_assignment_action_id in number
899 ,p_assignment_id in number
900 ,p_effective_date in date
901 ,p_tax_unit_id in number
902 ,p_arch_status in varchar2
903 ,p_arch_reason in varchar2
904 )
905 IS
906
907 cursor c_get_sep_details (cp_assignment_id in number
908 , cp_effective_date in date )
909 is
910 select replace(ppf.per_information3,'-','') emp_ss_number
911 ,ppf.last_name paternal_last_name
912 ,ppf.per_information1 maternal_last_name
913 ,ppf.first_name || ' ' ||ppf.middle_names name
914 ,ppf.employee_number worker_id
915 from per_all_assignments_f paf,
916 per_all_people_f ppf
917 where paf.assignment_id = cp_assignment_id
918 and paf.person_id = ppf.person_id
919 and cp_effective_date between paf.effective_start_date and paf.effective_end_date
920 and cp_effective_date between ppf.effective_start_date and ppf.effective_end_date ;
921
922 cursor c_get_er_ss_number(cp_gre_id in number )
923 is
924 select org_information1
925 from hr_organization_information
926 where org_information_context= 'MX_SOC_SEC_DETAILS'
927 and organization_id = cp_gre_id ;
928
929 cursor c_get_org_information ( cp_organization_id in number)
930 is
931 select org_information3,org_information5, org_information6
932 from hr_organization_information
933 where org_information_context= 'MX_SOC_SEC_DETAILS'
934 and organization_id = cp_organization_id ;
935
936 cursor c_get_leaving_reason( cp_assignment_id in number
937 ,cp_effective_date in date
938 ,cp_gre_id in number
939 )
940 is
941 select aei_information3
942 from per_assignment_extra_info pae
943 where pae.assignment_id = cp_assignment_id
944 and information_type = 'MX_SS_EMP_TRANS_REASON'
945 and fnd_date.canonical_to_date(aei_information1) = cp_effective_date
946 and aei_information2 = cp_gre_id ;
947
948 cursor c_get_pos_leaving_reason(cp_assignment_id in number
949 ,cp_effective_date in date )
950 is
951 select pds_information1, actual_termination_date
952 from per_periods_of_service ppos,
953 per_all_assignments_f paf
954 where paf.assignment_id = cp_assignment_id
955 and paf.person_id = ppos.person_id
956 and cp_effective_date between paf.effective_start_date and paf.effective_end_date
957 and pds_information_category='MX' ;
958
959
960 lv_emp_ss_number varchar2(240);
961 lv_er_ss_number varchar2(240);
962 lv_paternal_last_name varchar2(240);
963 lv_maternal_last_name varchar2(240);
964 lv_name varchar2(240);
965 lv_worker_id varchar2(240);
966 lv_type_of_tran varchar2(240);
967 lv_imss_way_bill varchar2(240);
968 lv_layout_identifier varchar2(240);
969
970 lv_leaving_reason varchar2(240);
971
972 lv_transmitter VARCHAR2(1);
973 ln_way_bill NUMBER;
974 ln_tr_gre_id NUMBER;
975
976 ln_action_information_id NUMBER ;
977 ln_object_version_number NUMBER ;
978
979 lv_procedure_name VARCHAR2(100) ;
980 lv_error_message VARCHAR2(200) ;
981 ln_step NUMBER;
982 ld_sep_date DATE ;
983
984 BEGIN
985
986 lv_procedure_name := '.archive_sep_details';
987
988 dbg('Entering archive_sep_details .........');
989
990 lv_type_of_tran := '02' ;
991 lv_layout_identifier := '9' ;
992 ld_sep_date := p_effective_date-1 ;
993
994 ln_step := 1;
995 hr_utility.set_location(gv_package || lv_procedure_name, 10);
996
997 dbg('Get employer ss id ');
998 -- get employer ss id for p_tax_unit_id
999 open c_get_er_ss_number(p_tax_unit_id) ;
1000 fetch c_get_er_ss_number into lv_er_ss_number ;
1001 close c_get_er_ss_number ;
1002
1003 ln_step := 2;
1004 hr_utility.set_location(gv_package || lv_procedure_name, 20);
1005
1006 dbg('Get GRE leaving reason from assignment extra info ');
1007 -- get GRE leaving reason
1008 open c_get_leaving_reason( p_assignment_id
1009 ,p_effective_date-1
1010 ,p_tax_unit_id
1011 ) ;
1012 fetch c_get_leaving_reason into lv_leaving_reason ;
1013 close c_get_leaving_reason ;
1014 if lv_leaving_reason is null then
1015 dbg('Get GRE leaving reason from period of service ');
1016 -- get it from periods of service
1017 -- also the effective date passed is not correct
1018 -- so need to get the actual termination date
1019 open c_get_pos_leaving_reason(p_assignment_id
1020 ,p_effective_date ) ;
1021 fetch c_get_pos_leaving_reason into lv_leaving_reason, ld_sep_date ;
1022 close c_get_pos_leaving_reason ;
1023 end if;
1024
1025
1026 ln_step := 3;
1027 hr_utility.set_location(gv_package || lv_procedure_name, 30);
1028
1029 dbg('Get IMSS way bill for gre '|| to_char(p_tax_unit_id));
1030
1031 -- get IMSS Waybill for p_tax_unit_id
1032 open c_get_org_information ( p_tax_unit_id ) ;
1033 fetch c_get_org_information into lv_transmitter,
1034 ln_way_bill,
1035 ln_tr_gre_id ;
1036 close c_get_org_information ;
1037
1038 dbg('Transmitter flag for this GRE is '|| lv_transmitter);
1039
1040 if lv_transmitter = 'Y' then
1041 lv_imss_way_bill:= ln_way_bill ;
1042 else
1043 dbg('Null or No then get the waybill number from the trans gre' );
1044 open c_get_org_information ( ln_tr_gre_id ) ;
1045 fetch c_get_org_information into lv_transmitter,
1046 ln_way_bill,
1047 ln_tr_gre_id ;
1048 lv_imss_way_bill:= ln_way_bill ;
1049 close c_get_org_information ;
1050 end if;
1051
1052 dbg('IMSS Waybill Number is '||lv_imss_way_bill );
1053
1054 ln_step := 4;
1055 hr_utility.set_location(gv_package || lv_procedure_name, 40);
1056
1057 dbg('Get separation details from assignment' );
1058 -- get the asg details from the table
1059 open c_get_sep_details( p_assignment_id,
1060 p_effective_date ) ;
1061 fetch c_get_sep_details into
1062 lv_emp_ss_number
1063 ,lv_paternal_last_name
1064 ,lv_maternal_last_name
1065 ,lv_name
1066 ,lv_worker_id ;
1067
1068 close c_get_sep_details ;
1069
1070
1071 ln_step := 5;
1072 hr_utility.set_location(gv_package || lv_procedure_name, 50);
1073
1074 msg('Calling the Api to create the separation details with the parameters ');
1075
1076 msg('Action_information_category : ' || 'MX SS SEPARATION DETAILS' );
1077 msg('Action Context Id : ' || to_char(p_assignment_action_id) );
1078 msg('ER SS Number is : ' || lv_er_ss_number );
1079 msg('EE SS Number is : ' || lv_emp_ss_number );
1080 msg('Paternal Last Name : ' || lv_paternal_last_name );
1081 msg('Maternal Last Name : ' || lv_maternal_last_name );
1082 msg('Name : ' || lv_name );
1083 msg('Separation Date : ' || to_char(ld_sep_date,'DDMMYYYY'));
1084 msg('transaction type : ' || lv_type_of_tran );
1085 msg('IMSS Waybill : ' || lv_imss_way_bill );
1086 msg('Worker ID : ' || lv_worker_id );
1087 msg('Leaving Reason : ' || lv_leaving_reason );
1088 msg('Layout Identifier : ' || lv_layout_identifier);
1089
1090 -- call the api to insert the record in pay_action_information
1091 pay_action_information_api.create_action_information(
1092 p_action_information_id => ln_action_information_id
1093 ,p_object_version_number => ln_object_version_number
1094 ,p_action_information_category => 'MX SS SEPARATION DETAILS'
1095 ,p_action_context_id => p_assignment_action_id
1096 ,p_action_context_type => 'AAP'
1097 ,p_jurisdiction_code => null
1098 ,p_assignment_id => p_assignment_id
1099 ,p_tax_unit_id => p_tax_unit_id
1100 ,p_effective_date => p_effective_date
1101 ,p_action_information1 => substr(lv_er_ss_number,1,10)
1102 ,p_action_information2 => substr(lv_er_ss_number,length(lv_er_ss_number),1)
1103 ,p_action_information3 => substr(lv_emp_ss_number,1,10)
1104 ,p_action_information4 => substr(lv_emp_ss_number,length(lv_emp_ss_number),1)
1105 ,p_action_information5 => lv_paternal_last_name
1106 ,p_action_information6 => lv_maternal_last_name
1107 ,p_action_information7 => lv_name
1108 ,p_action_information8 => null -- filler1
1109 ,p_action_information9 => to_char(ld_sep_date,'DDMMYYYY')
1110 ,p_action_information10 => null -- filler2
1111 ,p_action_information11 => lv_type_of_tran
1112 ,p_action_information12 => lv_imss_way_bill
1113 ,p_action_information13 => lv_worker_id
1114 ,p_action_information14 => lv_leaving_reason
1115 ,p_action_information15 => null -- filler3
1116 ,p_action_information16 => lv_layout_identifier
1117 ,p_action_information22 => p_arch_status
1118 ,p_action_information23 => p_arch_reason
1119 );
1120 msg('Successfully Archived. Action Information Id is : ' || to_char(ln_action_information_id) );
1121
1122 ln_step := 10;
1123 hr_utility.set_location(gv_package || lv_procedure_name, 100);
1124
1125 dbg('Exiting archive_sep_details .........');
1126
1127 EXCEPTION
1128 when others then
1129 lv_error_message := 'Error at step ' || ln_step || ' in ' ||
1130 gv_package || lv_procedure_name;
1131
1132 dbg(lv_error_message || '-' || sqlerrm);
1133
1134 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
1135 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
1136 hr_utility.raise_error;
1137
1138 END archive_sep_details ;
1139
1140
1141 /*************************************************************************
1142 Name : archive_hire_details
1143 Purpose : This procedure Archives hire details for the passed
1144 assignment_action_id and assignment_id
1145 **************************************************************************/
1146 PROCEDURE archive_hire_details( p_assignment_action_id in number
1147 ,p_assignment_id in number
1148 ,p_effective_date in date
1149 ,p_tax_unit_id in number
1150 ,p_arch_status in varchar2
1151 ,p_arch_reason in varchar2
1152 )
1153 IS
1154 cursor c_get_hire_details (cp_assignment_id in number
1155 , cp_effective_date in date )
1156 is
1157 select replace(ppf.per_information3,'-','') emp_ss_number
1158 ,ppf.last_name paternal_last_name
1159 ,per_information1 maternal_last_name
1160 ,ppf.first_name || ' ' || ppf.middle_names name
1161 ,substr(employment_category,3,1) worker_type
1162 ,hsc.segment6 salary_type
1163 ,puc.user_column_name work_schedule
1164 ,per_information4 med_center
1165 ,employee_number worker_id
1166 ,national_identifier CURP
1167 from per_all_assignments_f paf,
1168 per_all_people_f ppf,
1169 hr_soft_coding_keyflex hsc,
1170 pay_user_columns puc
1171 where paf.assignment_id = cp_assignment_id
1172 and paf.person_id = ppf.person_id
1173 and paf.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id (+)
1174 and hsc.segment4 = puc.user_column_id(+)
1175 and trunc(cp_effective_date) between paf.effective_start_date and paf.effective_end_date
1176 and trunc(cp_effective_date) between ppf.effective_start_date and ppf.effective_end_date ;
1177
1178 cursor c_get_er_ss_number(cp_gre_id in number )
1179 is
1180 select replace(org_information1,'-','')
1181 from hr_organization_information
1182 where org_information_context= 'MX_SOC_SEC_DETAILS'
1183 and organization_id = cp_gre_id ;
1184
1185 cursor c_get_org_information ( cp_organization_id in number)
1186 is
1187 select org_information3,org_information5, org_information6
1188 from hr_organization_information
1189 where org_information_context= 'MX_SOC_SEC_DETAILS'
1190 and organization_id = cp_organization_id ;
1191
1192
1193 -- cursor to get the minimum wage for Zone A
1194 cursor c_minimum_wage_zonea (cp_effective_date in date )
1195 is
1196 select legislation_info2
1197 from pay_mx_legislation_info_f
1198 where legislation_info_type='MX Minimum Wage Information'
1199 and legislation_info1='MWA'
1200 and cp_effective_date between effective_start_date and effective_end_date ;
1201
1202 lv_emp_ss_number varchar2(240);
1203 lv_er_ss_number varchar2(240);
1204 lv_paternal_last_name varchar2(240);
1205 lv_maternal_last_name varchar2(240);
1206 lv_name varchar2(240);
1207 lv_worker_type varchar2(240);
1208 lv_salary_type varchar2(240);
1209 lv_work_schedule varchar2(240);
1210 lv_rww_indicator varchar2(240);
1211 lv_med_center varchar2(240);
1212 lv_worker_id varchar2(240);
1213 lv_CURP varchar2(240);
1214
1215 ln_idw NUMBER;
1216 ln_min_wage NUMBER;
1217
1218 ln_fixed_idw NUMBER;
1219 ln_variable_idw NUMBER;
1220
1221 lv_type_of_tran varchar2(240);
1222 lv_imss_way_bill varchar2(240);
1223 lv_layout_identifier varchar2(240);
1224
1225 lv_transmitter VARCHAR2(1);
1226 ln_way_bill NUMBER;
1227 ln_tr_gre_id NUMBER;
1228
1229 ln_action_information_id NUMBER ;
1230 ln_object_version_number NUMBER ;
1231
1232 lv_procedure_name VARCHAR2(100) ;
1233 lv_error_message VARCHAR2(200);
1234 ln_step NUMBER;
1235
1236 BEGIN
1237
1238 lv_procedure_name := '.archive_hire_details';
1239
1240 dbg('Entering Archive hire details.........');
1241
1242 lv_type_of_tran := '08' ;
1243 lv_layout_identifier := '9' ;
1244
1245
1246 ln_step := 1;
1247 hr_utility.set_location(gv_package || lv_procedure_name, 10);
1248
1249 dbg('Get employer ssid ');
1250 -- get employer ss id for p_tax_unit_id
1251 open c_get_er_ss_number(p_tax_unit_id) ;
1252 fetch c_get_er_ss_number into lv_er_ss_number ;
1253 close c_get_er_ss_number ;
1254
1255 ln_step := 2;
1256 hr_utility.set_location(gv_package || lv_procedure_name, 20);
1257
1258 dbg('Get IMSS waybill for gre '|| to_char(p_tax_unit_id) );
1259
1260 -- get IMSS Waybill for p_tax_unit_id
1261 open c_get_org_information ( p_tax_unit_id ) ;
1262 fetch c_get_org_information into lv_transmitter,
1263 ln_way_bill,
1264 ln_tr_gre_id ;
1265 close c_get_org_information ;
1266
1267 dbg('Transmitter flag for this GRE is '|| lv_transmitter);
1268
1269 if lv_transmitter = 'Y' then
1270 lv_imss_way_bill:= ln_way_bill ;
1271 else
1272
1273 dbg('Null or No then get the waybill number from the trans gre' );
1274 open c_get_org_information ( ln_tr_gre_id ) ;
1275 fetch c_get_org_information into lv_transmitter,
1276 ln_way_bill,
1277 ln_tr_gre_id ;
1278 lv_imss_way_bill:= ln_way_bill ;
1279 close c_get_org_information ;
1280 end if;
1281
1282 dbg('way bill number is ' || lv_imss_way_bill );
1283
1284 ln_step := 3;
1285 hr_utility.set_location(gv_package || lv_procedure_name, 30);
1286
1287 dbg('Get hire details from assignment ' );
1288 dbg('Assignment Id : ' || to_char(p_assignment_id) );
1289 dbg('Effective Date : ' || to_char(p_effective_date,'DD-MON-YYYY'));
1290
1291 -- get the asg details from the base table
1292 open c_get_hire_details(p_assignment_id
1293 ,p_effective_date ) ;
1294 fetch c_get_hire_details into
1295 lv_emp_ss_number
1296 ,lv_paternal_last_name
1297 ,lv_maternal_last_name
1298 ,lv_name
1299 ,lv_worker_type
1300 ,lv_salary_type
1301 ,lv_work_schedule
1302 ,lv_med_center
1303 ,lv_worker_id
1304 ,lv_CURP ;
1305
1306 close c_get_hire_details ;
1307
1308 ln_step := 4;
1309 hr_utility.set_location(gv_package || lv_procedure_name, 40);
1310
1311 dbg('Get reduced working week indicator from workschedule ' );
1312
1313 -- derive Reduced Working-week indicator from workschedule
1314 if lv_work_schedule is not null then
1315 get_rww_ind(lv_work_schedule,lv_rww_indicator );
1316 else
1317 lv_rww_indicator := null ;
1318 end if;
1319
1320 ln_step := 5;
1321 hr_utility.set_location(gv_package || lv_procedure_name, 50);
1322
1323 dbg('Get IDW' );
1324
1325 dbg('Assignment Id '||to_char(p_assignment_id) );
1326 dbg('Tax unit Id '||to_char(p_tax_unit_id) );
1327 dbg('Effective Date '||to_char(p_effective_date) );
1328 dbg('Mode '||'BIMONTH_REPORT' );
1329
1330 ln_min_wage := 0 ;
1331
1332 -- get the minimum wage for Zone A
1333 /* bug fix 4528984 */
1334 open c_minimum_wage_zonea (p_effective_date) ;
1335 fetch c_minimum_wage_zonea into ln_min_wage ;
1336 close c_minimum_wage_zonea ;
1337
1338 dbg('Zone A Minimum Wage '||to_char(ln_min_wage) );
1339
1340 ln_idw := 0 ;
1341 ln_idw := pay_mx_ff_udfs.get_idw( p_assignment_id => p_assignment_id
1342 ,p_tax_unit_id => p_tax_unit_id
1343 ,p_effective_date => p_effective_date
1344 ,p_mode => 'BIMONTH_REPORT'
1345 ,p_fixed_idw => ln_fixed_idw
1346 ,p_variable_idw => ln_variable_idw
1347 ) ;
1348
1349 dbg('Calulated IDW from get_idw '||to_char(ln_idw) );
1350
1351 -- check the IDW with 25 times of zone A minimum wage
1352 -- if idw is greater than 25 times of zone A minimum wage then
1353 -- idw = 25 times of zone A minimum wage
1354 -- else
1355 -- idw = calculated one
1356 -- end if
1357
1358 if ln_idw > ( 25 * ln_min_wage ) then
1359 ln_idw := 25 * ln_min_wage ;
1360 end if;
1361
1362 dbg('IDW after compared with min wage '||to_char(ln_idw) );
1363
1364 -- round to 2 decimal and archive
1365 ln_idw := round(ln_idw,2) ;
1366
1367 dbg('IDW with 2 decimal '||to_char(ln_idw) );
1368
1369 dbg('call api to insert the record in pay action information with parameters' );
1370 msg('Action_information_category : ' || 'MX SS HIRE DETAILS' );
1371 msg('Action Context Id : ' || to_char(p_assignment_action_id) );
1372 msg('ER SS Number is : ' || lv_er_ss_number );
1373 msg('EE SS Number is : ' || lv_emp_ss_number );
1374 msg('Paternal Last Name : ' || lv_paternal_last_name );
1375 msg('Maternal Last Name : ' || lv_maternal_last_name );
1376 msg('Name : ' || lv_name );
1377 msg('IDW : ' || to_char(ln_idw) );
1378 msg('Worker Type : ' || lv_worker_type );
1379 msg('Salary Type : ' || lv_salary_type );
1380 msg('RWW Indicator : ' || lv_rww_indicator);
1381 msg('Hire Date : ' || to_char(p_effective_date,'DDMMYYYY'));
1382 msg('Med Center : ' || lv_med_center );
1383 msg('transaction type : ' || lv_type_of_tran );
1384 msg('IMSS Waybill : ' || lv_imss_way_bill );
1385 msg('Worker ID : ' || lv_worker_id );
1386 msg('CURP : ' || lv_curp );
1387 msg('Layout Identifier : ' || lv_layout_identifier );
1388
1389
1390 -- call the api to insert the record in pay_action_information
1391 pay_action_information_api.create_action_information(
1392 p_action_information_id => ln_action_information_id
1393 ,p_object_version_number => ln_object_version_number
1394 ,p_action_information_category => 'MX SS HIRE DETAILS'
1395 ,p_action_context_id => p_assignment_action_id
1396 ,p_action_context_type => 'AAP'
1397 ,p_jurisdiction_code => null
1398 ,p_assignment_id => p_assignment_id
1399 ,p_tax_unit_id => p_tax_unit_id
1400 ,p_effective_date => p_effective_date
1401 ,p_action_information1 => substr(lv_er_ss_number,1,10)
1402 ,p_action_information2 => substr(lv_er_ss_number,length(lv_er_ss_number),1)
1403 ,p_action_information3 => substr(lv_emp_ss_number,1,10)
1404 ,p_action_information4 => substr(lv_emp_ss_number,length(lv_emp_ss_number),1)
1405 ,p_action_information5 => lv_paternal_last_name
1406 ,p_action_information6 => lv_maternal_last_name
1407 ,p_action_information7 => lv_name
1408 ,p_action_information8 => to_char(ln_idw)
1409 ,p_action_information9 => null -- filler1
1410 ,p_action_information10 => lv_worker_type
1411 ,p_action_information11 => lv_salary_type
1412 ,p_action_information12 => lv_rww_indicator
1413 ,p_action_information13 => to_char(p_effective_date,'DDMMYYYY')
1414 ,p_action_information14 => lv_med_center
1415 ,p_action_information15 => null -- filler2
1416 ,p_action_information16 => lv_type_of_tran
1417 ,p_action_information17 => lv_imss_way_bill
1418 ,p_action_information18 => lv_worker_id
1419 ,p_action_information19 => null -- filler3
1420 ,p_action_information20 => lv_curp
1421 ,p_action_information21 => lv_layout_identifier
1422 ,p_action_information22 => p_arch_status
1423 ,p_action_information23 => p_arch_reason
1424 );
1425
1426 msg('Successfully Archived. Action Information Id is : ' || to_char(ln_action_information_id) );
1427
1428 ln_step := 6;
1429 hr_utility.set_location(gv_package || lv_procedure_name, 100);
1430
1431 dbg('Exiting archive_hire_details .........');
1432
1433 EXCEPTION
1434 when others then
1435 lv_error_message := 'Error at step ' || ln_step || ' in ' ||
1436 gv_package || lv_procedure_name;
1437
1438 dbg(lv_error_message || '-' || sqlerrm);
1439
1440 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
1441 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
1442 hr_utility.raise_error;
1443
1444 END archive_hire_details ;
1445
1446 /************************************************************
1447 Name : arch_hire_separation_data
1448 Purpose : This procedure archives the hire and separation details
1449 ************************************************************/
1450 PROCEDURE arch_hire_separation_data
1451 ( p_assignment_action_id in number,
1452 p_arch_type in varchar2,
1453 p_assignment_id in number,
1454 p_assignment_number in varchar2,
1455 p_location_id in number,
1456 p_effective_date in date,
1457 p_gre_id in number,
1458 p_error_mesg in varchar2,
1459 p_event_type in varchar2
1460 )
1461 is
1462
1463 CURSOR c_get_location_code (cp_location_id in number)
1464 IS
1465 select location_code
1466 from hr_locations
1467 where location_id = cp_location_id ;
1468
1469 CURSOR c_get_gre_name (cp_gre_id in number)
1470 IS
1471 select name
1472 from hr_organization_units
1473 where organization_id = cp_gre_id ;
1474
1475
1476 lv_procedure_name VARCHAR2(100) ;
1477 lv_location_code VARCHAR2(30);
1478 lv_gre_name VARCHAR2(100);
1479 lv_error_message VARCHAR2(200);
1480 ln_step NUMBER;
1481
1482 BEGIN
1483
1484
1485 lv_procedure_name := '.arch_hire_separation_data';
1486 hr_utility.set_location(gv_package || lv_procedure_name, 10);
1487 ln_step := 1;
1488
1489 dbg('Entering arch_hire_separation_data .........');
1490
1491 if p_arch_type='E' then
1492
1493 open c_get_location_code(p_location_id) ;
1494 fetch c_get_location_code into lv_location_code ;
1495 close c_get_location_code ;
1496
1497 if p_error_mesg = '1' then
1498
1499 msg('Error : ' || p_assignment_number || ' assignment ' || lv_location_code
1500 || ' location is assigned to multiple GREs in the Generic Hierarchy' );
1501 pay_core_utils.push_message(800,'HR_MX_GRE_AMBIGUOUS','F') ;
1502 else
1503 msg('Error : ' || p_assignment_number || ' assignment ' || lv_location_code
1504 || ' location is not assigned to a GRE in the Generic Hierarchy' );
1505 pay_core_utils.push_message(800,'HR_MX_LOC_MISSING_GEN_HIER','F') ;
1506
1507 end if;
1508 pay_core_utils.push_token('LOC_CODE',lv_location_code) ;
1509 pay_core_utils.push_token('ASG_NUMBER',p_assignment_number) ;
1510
1511 else
1512
1513 open c_get_gre_name(p_gre_id) ;
1514 fetch c_get_gre_name into lv_gre_name ;
1515 close c_get_gre_name ;
1516
1517 if p_arch_type ='H' then
1518 msg('Archiving Hire details for assignment number ' || p_assignment_number );
1519 msg('GRE : ' || lv_gre_name );
1520 msg('Assignment Id : ' || to_char(p_assignment_id) ) ;
1521 msg('GRE Id : ' || to_char(p_gre_id) ) ;
1522 -- call the archive_hire_details to insert into pay_action_information table
1523 archive_hire_details( p_assignment_action_id => p_assignment_action_id
1524 ,p_assignment_id => p_assignment_id
1525 ,p_effective_date => p_effective_date
1526 ,p_tax_unit_id => p_gre_id
1527 ,p_arch_status => 'A'
1528 ,p_arch_reason => 'Archived'
1529 ) ;
1530 elsif p_arch_type='S' then
1531 msg('Archiving Separation details for assignment number ' || p_assignment_number );
1532 msg('GRE : ' || lv_gre_name );
1533 msg('Assignment Id : ' || to_char(p_assignment_id ) ) ;
1534 msg('GRE Id : ' || to_char(p_gre_id ) ) ;
1535 -- call the archive_sep_details to insert into pay_action_information table
1536 archive_sep_details( p_assignment_action_id => p_assignment_action_id
1537 ,p_assignment_id => p_assignment_id
1538 ,p_effective_date => p_effective_date
1539 ,p_tax_unit_id => p_gre_id
1540 ,p_arch_status => 'A'
1541 ,p_arch_reason => 'Archived'
1542 ) ;
1543
1544 elsif p_arch_type='R' then
1545 msg('Archiving Reverse Terminataion Rehire details for assignment number ' || p_assignment_number );
1546 msg('GRE : ' || lv_gre_name );
1547 msg('Assignment Id : ' || to_char(p_assignment_id) ) ;
1548 msg('GRE Id : ' || to_char(p_gre_id) ) ;
1549 -- call the archive_hire_details to insert into pay_action_information table
1550 archive_hire_details( p_assignment_action_id => p_assignment_action_id
1551 ,p_assignment_id => p_assignment_id
1552 ,p_effective_date => p_effective_date
1553 ,p_tax_unit_id => p_gre_id
1554 ,p_arch_status => 'R'
1555 ,p_arch_reason => 'Reverse Termination Rehired'
1556 ) ;
1557 end if;
1558 end if;
1559
1560
1561 hr_utility.set_location(gv_package || lv_procedure_name, 100);
1562 dbg('Exiting arch_hire_separation_data .........');
1563
1564 EXCEPTION
1565 when others then
1566 lv_error_message := 'Error at step ' || ln_step || ' in ' ||
1567 gv_package || lv_procedure_name;
1568 dbg(lv_error_message || '-' || sqlerrm);
1569 hr_utility.raise_error;
1570
1571 END arch_hire_separation_data;
1572
1573 /************************************************************
1574 Name : process_insert_event
1575 Purpose : This procedure process insert event.
1576 This procedure is called
1577 from interpret_all_asg_events procedure
1578 ************************************************************/
1579 PROCEDURE process_insert_event(
1580 p_assignment_action_id in number
1581 ,p_assignment_id in number
1582 ,p_effective_date in date
1583 )
1584 IS
1585
1586 -- Cursor to check the record exist in the archive table
1587 cursor c_chk_archive ( cp_person_id in number,
1588 cp_gre_id in number,
1589 cp_effective_date date,
1590 cp_action_info_category varchar2 )
1591 is
1592 select 'Y'
1593 from pay_action_information pai,
1594 per_all_assignments_f paf
1595 where pai.action_context_type ='AAP'
1596 and pai.action_information_category = cp_action_info_category
1597 and pai.tax_unit_id = cp_gre_id
1598 and pai.assignment_id = paf.assignment_id
1599 and paf.person_id = cp_person_id
1600 and cp_effective_date between paf.effective_start_date and paf.effective_end_date
1601 order by pai.effective_date desc ;
1602
1603
1604 cursor c_asg_details(cp_assignment_id in number,
1605 cp_effective_date in date )
1606 is
1607 select paf.person_id,
1608 paf.assignment_number,
1609 paf.location_id,
1610 paf.soft_coding_keyflex_id,
1611 paf.business_group_id
1612 from per_all_assignments_f paf
1613 where paf.assignment_id = cp_assignment_id
1614 and cp_effective_date between paf.effective_start_date
1615 and paf.effective_end_date ;
1616
1617 ln_gre_id NUMBER ;
1618 ln_person_id NUMBER ;
1619 lv_assignment_number VARCHAR2(30);
1620 ln_location_id NUMBER ;
1621 ln_soft_coding_keyflex_id NUMBER ;
1622 ln_business_group_id NUMBER ;
1623 lv_gre_error_mesg VARCHAR2(100);
1624 lv_chk VARCHAR2(1);
1625
1626 lv_asg_flag VARCHAR2(1);
1627
1628 lv_procedure_name VARCHAR2(100);
1629 lv_error_message VARCHAR2(200);
1630 ln_step NUMBER;
1631
1632 BEGIN
1633
1634 dbg('Entering process insert event..........' );
1635
1636 lv_procedure_name := '.process_insert_event';
1637
1638 hr_utility.set_location(gv_package || lv_procedure_name, 10);
1639 ln_step := 1;
1640 dbg('Get assignment details ' );
1641
1642 open c_asg_details( p_assignment_id,
1643 p_effective_date ) ;
1644 fetch c_asg_details into ln_person_id,
1645 lv_assignment_number,
1646 ln_location_id,
1647 ln_soft_coding_keyflex_id,
1648 ln_business_group_id;
1649 close c_asg_details ;
1650
1651 hr_utility.set_location(gv_package || lv_procedure_name, 20);
1652 ln_step := 2;
1653 dbg('Dervie GRE from location and SCL ' );
1654 ln_gre_id := derive_gre_from_loc_scl( ln_location_id
1655 ,ln_business_group_id
1656 ,ln_soft_coding_keyflex_id
1657 ,p_effective_date ) ;
1658
1659 hr_utility.set_location(gv_package || lv_procedure_name, 30);
1660 ln_step := 3;
1661
1662 if ln_gre_id = -1 or ln_gre_id = -2 then
1663 if ln_gre_id = -1 then
1664 lv_gre_error_mesg := g_ambiguous_error ;
1665 else
1666 lv_gre_error_mesg := g_missing_gre_error ;
1667 end if;
1668
1669 arch_hire_separation_data(p_assignment_action_id => p_assignment_action_id,
1670 p_arch_type => 'E',
1671 p_assignment_id => p_assignment_id,
1672 p_assignment_number => lv_assignment_number,
1673 p_location_id => ln_location_id,
1674 p_effective_date => p_effective_date,
1675 p_gre_id => null,
1676 p_error_mesg => lv_gre_error_mesg,
1677 p_event_type => 'I'
1678 ) ;
1679 dbg('Error in deriving GRE for ' || to_char(ln_location_id) );
1680 return ;
1681
1682 end if ;
1683
1684 hr_utility.set_location(gv_package || lv_procedure_name, 40);
1685 ln_step := 4;
1686
1687 -- Check record exists in Archive table for person_id, gre_id,
1688 -- effective_date, hire_category
1689
1690 dbg('Check record exists in archive table with HIRE as info category' );
1691 dbg(' person id = '||to_char(ln_person_id) );
1692 dbg(' gre id = '||to_char(ln_gre_id) );
1693 dbg(' eff date = '||to_char(p_effective_date,'DD-MON-YYYY') );
1694 dbg(' Action info category = ' ||g_action_hire_category );
1695
1696 open c_chk_archive ( ln_person_id, ln_gre_id, p_effective_date, g_action_hire_category ) ;
1697 fetch c_chk_archive into lv_chk ;
1698 if c_chk_archive%found then
1699
1700 -- if record found then
1701 dbg('HIRE record exists in archive table');
1702
1703 close c_chk_archive ;
1704
1705 -- Check the separation record exists in archive table for
1706 -- person_id, ger_id, effective_date, sep_category
1707
1708 hr_utility.set_location(gv_package || lv_procedure_name, 50);
1709 ln_step := 5;
1710
1711 dbg('Check record exists in archive table with SEPARATION as info category' );
1712 dbg(' person id = '||to_char(ln_person_id) );
1713 dbg(' gre id = '||to_char(ln_gre_id) );
1714 dbg(' eff date = '||to_char(p_effective_date,'DD-MON-YYYY') );
1715 dbg(' Action info category = '||g_action_sep_category );
1716
1717 open c_chk_archive ( ln_person_id, ln_gre_id, p_effective_date, g_action_sep_category ) ;
1718 fetch c_chk_archive into lv_chk ;
1719 if c_chk_archive%found then
1720
1721 -- if it is there then event is a rehire
1722 dbg('SEPARATION record found then it is a rehire record' );
1723 dbg('Archieve data as arch_type = H ');
1724
1725 arch_hire_separation_data(p_assignment_action_id => p_assignment_action_id,
1726 p_arch_type => 'H',
1727 p_assignment_id => p_assignment_id,
1728 p_assignment_number => lv_assignment_number,
1729 p_location_id => ln_location_id,
1730 p_effective_date => p_effective_date,
1731 p_gre_id => ln_gre_id ,
1732 p_error_mesg => null,
1733 p_event_type => 'I'
1734 ) ;
1735 end if;
1736 close c_chk_archive ;
1737
1738 else
1739 close c_chk_archive ;
1740
1741 hr_utility.set_location(gv_package || lv_procedure_name, 60);
1742
1743 -- record does not exists in archive table
1744 dbg('HIRE Record not found in archive table');
1745
1746 -- find out this person is reported to IMSS prior to g_report_imp_date by legacy system
1747 -- by looking at assignment records
1748
1749 dbg('Check this person is reported to IMSS prior to rep imp date ' );
1750 dbg(' person id = '||to_char(ln_person_id) );
1751 dbg(' gre id = '||to_char(ln_gre_id) );
1752 dbg(' eff date = '||to_char(g_report_imp_date-1,'DD-MON-YYYY') );
1753
1754 lv_asg_flag := chk_active_asg_exists( p_assignment_id
1755 ,ln_person_id
1756 ,ln_gre_id
1757 ,g_report_imp_date-1
1758 ) ;
1759 if lv_asg_flag ='N' then
1760 dbg('record not found ' );
1761 -- record does not exists
1762 -- this person NOT reported by legacy system
1763 -- so write to hire plsql table
1764 dbg('Archive data as arch_type = H ');
1765 arch_hire_separation_data(p_assignment_action_id => p_assignment_action_id,
1766 p_arch_type => 'H',
1767 p_assignment_id => p_assignment_id,
1768 p_assignment_number => lv_assignment_number,
1769 p_location_id => ln_location_id,
1770 p_effective_date => p_effective_date,
1771 p_gre_id => ln_gre_id ,
1772 p_error_mesg => null,
1773 p_event_type => 'I'
1774 );
1775 end if;
1776
1777 end if ;
1778
1779 hr_utility.set_location(gv_package || lv_procedure_name, 100);
1780 ln_step := 6;
1781
1782 dbg('Exiting process insert event..........' );
1783
1784 exception
1785 when others then
1786 lv_error_message := 'Error at step ' || ln_step ||
1787 ' in ' || gv_package || lv_procedure_name;
1788 dbg(lv_error_message || '-' || sqlerrm);
1789 hr_utility.raise_error;
1790
1791 END process_insert_event ;
1792
1793 /************************************************************
1794 Name : process_correction_event
1795 Purpose : This procedure process correction event.
1796 This procedure is called
1797 from interpret_all_asg_events procedure
1798 ************************************************************/
1799 PROCEDURE process_correction_event
1800 ( p_assignment_action_id in number
1801 ,p_assignment_id in number
1802 ,p_effective_date in date
1803 ,p_column_name in varchar
1804 ,p_old_value in number
1805 ,p_new_value in number
1806 ,p_column_name1 in varchar
1807 ,p_old_value1 in number
1808 ,p_new_value1 in number
1809 )
1810 IS
1811
1812 -- Cursor to get person details
1813 cursor c_person_details(cp_assignment_id in number,
1814 cp_effective_date in date )
1815 is
1816 select person_id,
1817 assignment_number,
1818 location_id,
1819 soft_coding_keyflex_id,
1820 business_group_id
1821 from per_all_assignments_f paf
1822 where paf.assignment_id = cp_assignment_id
1823 and cp_effective_date between paf.effective_start_date
1824 and paf.effective_end_date ;
1825
1826
1827 -- Cursor to check the record exist in the archive table
1828 cursor c_chk_archive ( cp_person_id in number,
1829 cp_gre_id in number,
1830 cp_effective_date date,
1831 cp_action_info_category varchar2 )
1832 is
1833 select 'Y'
1834 from pay_action_information pai,
1835 per_all_assignments_f paf
1836 where pai.action_context_type ='AAP'
1837 and pai.action_information_category = cp_action_info_category
1838 and pai.tax_unit_id = cp_gre_id
1839 and pai.assignment_id = paf.assignment_id
1840 and paf.person_id = cp_person_id
1841 and cp_effective_date between paf.effective_start_date and paf.effective_end_date
1842 order by pai.effective_date desc ;
1843
1844
1845 l_is_ambiguous BOOLEAN ;
1846 l_missing_gre BOOLEAN ;
1847 ln_old_gre_id NUMBER ;
1848 ln_new_gre_id NUMBER ;
1849 ln_person_id NUMBER ;
1850 ln_business_group_id NUMBER ;
1851 lv_assignment_number VARCHAR2(30);
1852 lv_gre_error_mesg VARCHAR2(100);
1853 lv_chk VARCHAR2(1);
1854
1855 lv_asg_flag VARCHAR2(1);
1856
1857 ln_location_id NUMBER ;
1858 ln_soft_coding_keyflex_id NUMBER ;
1859 ln_old_value NUMBER ;
1860 ln_new_value NUMBER ;
1861 ln_old_value1 NUMBER ;
1862 ln_new_value1 NUMBER ;
1863
1864 lv_procedure_name VARCHAR2(100) ;
1865 lv_error_message VARCHAR2(200);
1866 ln_step NUMBER;
1867
1868 BEGIN
1869
1870 dbg('Entering process correction event..............');
1871
1872 ln_step := 1;
1873 hr_utility.set_location(gv_package || lv_procedure_name, 10);
1874
1875 -- assign the values to local variable
1876
1877 lv_procedure_name := '.process_correction_event';
1878
1879 ln_old_value := p_old_value ;
1880 ln_new_value := p_new_value ;
1881 ln_old_value1 := p_old_value1 ;
1882 ln_new_value1 := p_new_value1 ;
1883
1884 dbg('Get person details ' );
1885
1886 -- get the person_id for this assignment
1887 open c_person_details(p_assignment_id,
1888 p_effective_date ) ;
1889
1890 fetch c_person_details into ln_person_id, lv_assignment_number,
1891 ln_location_id,ln_soft_coding_keyflex_id,
1892 ln_business_group_id;
1893
1894 close c_person_details ;
1895
1896 -- If the user made the correction only on location_id then
1897 -- p_old_value = old value of location id
1898 -- p_new_value = new value of location id will be passed
1899 -- else if the user made the correction only on soft_coding_keyflex_id then
1900 -- p_old_value1 = old value of soft_coding_keyflex_id
1901 -- p_new_value1 = new value of soft_coding_keyflex_id will be passed
1902
1903 -- Check location id is null
1904 -- if it is null then the user did not update the
1905 -- location id so get it from the table and assign
1906 -- old and new value equal to the table value
1907
1908 if p_old_value is null and p_new_value is null then
1909
1910 dbg('Assigning location id from table values');
1911
1912 ln_old_value := ln_location_id ;
1913 ln_new_value := ln_location_id ;
1914 end if;
1915
1916 -- Check soft coding key flex value is null
1917 -- if it is null then the user did not update the
1918 -- soft coding keyflex so get it from table and assign
1919 -- old and new value equal to the table value
1920
1921 if p_old_value1 is null and p_new_value1 is null then
1922
1923 dbg('Assigning soft coding keyflex id from table values');
1924
1925 ln_old_value1 := ln_soft_coding_keyflex_id ;
1926 ln_new_value1 := ln_soft_coding_keyflex_id ;
1927 end if;
1928
1929 dbg('After the values got from the table values');
1930 dbg('Column Name :' || 'LOCATION_ID' );
1931 dbg('Old Value :' || to_char(ln_old_value) );
1932 dbg('new Value :' || to_char(ln_new_value) );
1933 dbg('Column Name :' || 'SOFT_CODING_KEYFLEX_ID' );
1934 dbg('Old Value :' || to_char(ln_old_value1) );
1935 dbg('new Value :' || to_char(ln_new_value1) );
1936
1937
1938 dbg('Dervie old GRE from old location and old SCL ' );
1939 ln_old_gre_id := derive_gre_from_loc_scl( ln_old_value
1940 ,ln_business_group_id
1941 ,ln_old_value1
1942 ,p_effective_date ) ;
1943 hr_utility.set_location(gv_package || lv_procedure_name, 30);
1944 ln_step := 2;
1945
1946 if ln_old_gre_id = -1 or ln_old_gre_id = -2 then
1947 if ln_old_gre_id = -1 then
1948 lv_gre_error_mesg := g_ambiguous_error ;
1949 else
1950 lv_gre_error_mesg := g_missing_gre_error ;
1951 end if;
1952
1953 dbg('Error in deriving GRE for OLD Location ' );
1954 msg('Error in deriving GRE for OLD Location ' );
1955
1956 arch_hire_separation_data(p_assignment_action_id => p_assignment_action_id,
1957 p_arch_type => 'E',
1958 p_assignment_id => p_assignment_id,
1959 p_assignment_number => lv_assignment_number,
1960 p_location_id => ln_old_value,
1961 p_effective_date => p_effective_date,
1962 p_gre_id => null,
1963 p_error_mesg => lv_gre_error_mesg,
1964 p_event_type => 'C'
1965 ) ;
1966
1967 end if ;
1968
1969 dbg('Dervie New GRE from new location and new SCL ' );
1970 ln_new_gre_id := derive_gre_from_loc_scl( ln_new_value
1971 ,ln_business_group_id
1972 ,ln_new_value1
1973 ,p_effective_date ) ;
1974 hr_utility.set_location(gv_package || lv_procedure_name, 30);
1975 ln_step := 3;
1976
1977 if ln_new_gre_id = -1 or ln_new_gre_id = -2 then
1978 if ln_new_gre_id = -1 then
1979 lv_gre_error_mesg := g_ambiguous_error ;
1980 else
1981 lv_gre_error_mesg := g_missing_gre_error ;
1982 end if;
1983
1984 dbg('Error in deriving GRE for NEW Location ' );
1985 msg('Error in deriving GRE for NEW Location ' );
1986
1987 arch_hire_separation_data(p_assignment_action_id => p_assignment_action_id,
1988 p_arch_type => 'E',
1989 p_assignment_id => p_assignment_id,
1990 p_assignment_number => lv_assignment_number,
1991 p_location_id => ln_new_value,
1992 p_effective_date => p_effective_date,
1993 p_gre_id => null,
1994 p_error_mesg => lv_gre_error_mesg,
1995 p_event_type => 'C'
1996 ) ;
1997 end if ;
1998
1999 ln_step := 4;
2000
2001 dbg('old GRE : ' || to_char(ln_old_gre_id) );
2002 dbg('new GRE : ' || to_char(ln_new_gre_id) );
2003
2004
2005 if (ln_old_gre_id is not null and
2006 ln_new_gre_id is not null and
2007 ln_old_gre_id = ln_new_gre_id ) then
2008
2009 hr_utility.set_location(gv_package || lv_procedure_name, 50);
2010 dbg('old and new GREs are same no need to process');
2011
2012 Return ;
2013
2014 end if;
2015
2016
2017 ln_step := 5;
2018 hr_utility.set_location(gv_package || lv_procedure_name, 70);
2019
2020 -- if old gre is not null then process for old GRE
2021 if ln_old_gre_id <> -1 and ln_old_gre_id <> -2 and ln_old_gre_id is not null then
2022 dbg('Process for old GRE ' );
2023 -- check record exists in Archive table with old GRE, person id, effective_date
2024 dbg('Check record is archived in archive table with HIRE as info category' );
2025 dbg(' person id = '||to_char(ln_person_id) );
2026 dbg(' gre id = '||to_char(ln_old_gre_id) );
2027 dbg(' eff date = '||to_char(p_effective_date,'DD-MON-YYYY') );
2028 dbg(' Action info category = ' ||g_action_hire_category );
2029
2030 open c_chk_archive ( ln_person_id, ln_old_gre_id, p_effective_date, g_action_hire_category ) ;
2031 fetch c_chk_archive into lv_chk ;
2032 if c_chk_archive%found then
2033 close c_chk_archive ;
2034 -- record exists then this person already reported with old GRE
2035 dbg('record found this person already reported with old GRE' );
2036 -- do we want to separate from old GRE A
2037 -- if yes then check any other active assignments with old GRE
2038 dbg('Check any other active assignments exists for this employee ' );
2039 dbg(' assignment id = '||to_char(p_assignment_id) );
2040 dbg(' person id = '||to_char(ln_person_id) );
2041 dbg(' gre id = '||to_char(ln_old_gre_id) );
2042 dbg(' eff date = '||to_char(p_effective_date,'DD-MON-YYYY') );
2043
2044 lv_asg_flag := chk_active_asg_exists( p_assignment_id
2045 ,ln_person_id
2046 ,ln_old_gre_id
2047 ,p_effective_date
2048 ) ;
2049 if lv_asg_flag ='N' then
2050 -- no record found then archive separation data
2051 dbg('no active assignments found so archive data as arch_type=S ' );
2052 arch_hire_separation_data(p_assignment_action_id => p_assignment_action_id
2053 ,p_arch_type => 'S'
2054 ,p_assignment_id => p_assignment_id
2055 ,p_assignment_number => lv_assignment_number
2056 ,p_location_id => ln_old_value
2057 ,p_effective_date => p_effective_date
2058 ,p_gre_id => ln_old_gre_id
2059 ,p_error_mesg => null
2060 ,p_event_type => 'C'
2061 ) ;
2062
2063 end if;
2064
2065 end if;
2066
2067 end if; -- process for old gre
2068
2069 ln_step := 6;
2070 hr_utility.set_location(gv_package || lv_procedure_name, 80);
2071
2072
2073 -- if new gre id is not null then process for the new GRE
2074 if ln_new_gre_id <> -1 and ln_new_gre_id <> -2 and ln_new_gre_id is not null then
2075 dbg('Process for the new GRE ' );
2076 -- check any other active assignments with new GRE
2077 -- if yes then no need to archive
2078 -- if no then archive hire details
2079 dbg('Check any other active assignments exists for this employee ' );
2080 dbg(' assignment id = '||to_char(p_assignment_id) );
2081 dbg(' person id = '||to_char(ln_person_id) );
2082 dbg(' gre id = '||to_char(ln_new_gre_id) );
2083 dbg(' eff date = '||to_char(p_effective_date,'DD-MON-YYYY') );
2084
2085 lv_asg_flag := chk_active_asg_exists( p_assignment_id
2086 ,ln_person_id
2087 ,ln_new_gre_id
2088 ,p_effective_date
2089 ) ;
2090 if lv_asg_flag ='N' then
2091 -- write in plsql table with hire
2092 dbg('no active assignments found so archive data as arch_type=H ' );
2093 arch_hire_separation_data(p_assignment_action_id => p_assignment_action_id
2094 ,p_arch_type => 'H'
2095 ,p_assignment_id => p_assignment_id
2096 ,p_assignment_number => lv_assignment_number
2097 ,p_location_id => ln_new_value
2098 ,p_effective_date => p_effective_date
2099 ,p_gre_id => ln_new_gre_id
2100 ,p_error_mesg => null
2101 ,p_event_type => 'C'
2102 ) ;
2103 end if;
2104
2105 end if; -- process for new gre
2106
2107 ln_step := 7;
2108 hr_utility.set_location(gv_package || lv_procedure_name, 100);
2109
2110 dbg('Exiting process correction event..........' );
2111
2112 exception
2113 when others then
2114 lv_error_message := 'Error at step ' || ln_step ||
2115 ' in ' || gv_package || lv_procedure_name;
2116 dbg(lv_error_message || '-' || sqlerrm);
2117 hr_utility.raise_error;
2118
2119 END process_correction_event;
2120
2121
2122 /************************************************************
2123 Name : process_update_event
2124 Purpose : This procedure process the update event.
2125 This procedure is called
2126 from interpret_all_asg_events procedure
2127 ************************************************************/
2128 PROCEDURE process_update_event ( p_assignment_action_id in number
2129 ,p_assignment_id in number
2130 ,p_effective_date in date
2131 ,p_column_name in varchar
2132 ,p_old_value in number
2133 ,p_new_value in number
2134 ,p_column_name1 in varchar
2135 ,p_old_value1 in number
2136 ,p_new_value1 in number
2137 )
2138 IS
2139
2140 -- Cursor to get person details
2141 cursor c_person_details(cp_assignment_id in number,
2142 cp_effective_date in date )
2143 is
2144 select person_id,
2145 assignment_number,
2146 location_id,
2147 soft_coding_keyflex_id,
2148 business_group_id
2149 from per_all_assignments_f paf
2150 where paf.assignment_id = cp_assignment_id
2151 and cp_effective_date between paf.effective_start_date
2152 and paf.effective_end_date ;
2153
2154 -- Cursor to check the record exist in the archive table
2155 cursor c_chk_archive ( cp_person_id in number,
2156 cp_gre_id in number,
2157 cp_effective_date date,
2158 cp_action_info_category varchar2 )
2159 is
2160 select 'Y'
2161 from pay_action_information pai,
2162 per_all_assignments_f paf
2163 where pai.action_context_type ='AAP'
2164 and pai.action_information_category = cp_action_info_category
2165 and pai.tax_unit_id = cp_gre_id
2166 and pai.assignment_id = paf.assignment_id
2167 and cp_effective_date between paf.effective_start_date and paf.effective_end_date
2168 and paf.person_id = CP_PERSON_ID
2169 order by pai.effective_date desc ;
2170
2171
2172 l_is_ambiguous BOOLEAN ;
2173 l_missing_gre BOOLEAN ;
2174 ln_old_gre_id NUMBER ;
2175 ln_new_gre_id NUMBER ;
2176 ln_person_id NUMBER ;
2177 lv_assignment_number VARCHAR2(30);
2178 lv_gre_error_mesg VARCHAR2(100);
2179 lv_chk VARCHAR2(1);
2180 lv_asg_flag VARCHAR2(1);
2181
2182 ln_location_id NUMBER ;
2183 ln_business_group_id NUMBER ;
2184 ln_soft_coding_keyflex_id NUMBER ;
2185 ln_old_value NUMBER ;
2186 ln_new_value NUMBER ;
2187 ln_old_value1 NUMBER ;
2188 ln_new_value1 NUMBER ;
2189
2190 lv_procedure_name VARCHAR2(100);
2191 lv_error_message VARCHAR2(200);
2192 ln_step NUMBER;
2193
2194 BEGIN
2195
2196 dbg('Entering process_update_event ..........');
2197
2198 ln_step := 1;
2199
2200 -- assign the values to local variable
2201 lv_procedure_name := '.process_update_event';
2202
2203 ln_old_value := p_old_value ;
2204 ln_new_value := p_new_value ;
2205 ln_old_value1 := p_old_value1 ;
2206 ln_new_value1 := p_new_value1 ;
2207
2208 hr_utility.set_location(gv_package || lv_procedure_name, 10);
2209 dbg('Get person details ' );
2210
2211 -- get the person_id for this assignment
2212 open c_person_details(p_assignment_id,
2213 p_effective_date ) ;
2214 fetch c_person_details into ln_person_id, lv_assignment_number,
2215 ln_location_id,ln_soft_coding_keyflex_id,
2216 ln_business_group_id;
2217 close c_person_details ;
2218
2219 -- If the user made the update only on location_id then
2220 -- p_old_value = old value of location id
2221 -- p_new_value = new value of location id will be passed
2222 -- else if the user made the update only on soft_coding_keyflex_id then
2223 -- p_old_value1 = old value of soft_coding_keyflex_id
2224 -- p_new_value1 = new value of soft_coding_keyflex_id will be passed
2225
2226 -- Check location id is null
2227 -- if it is null then the user did not update the
2228 -- location id so get it from the table and assign
2229 -- old and new value equal to the table value
2230
2231 if p_old_value is null and p_new_value is null then
2232
2233 dbg('Assigning location id from table values');
2234
2235 ln_old_value := ln_location_id ;
2236 ln_new_value := ln_location_id ;
2237 end if;
2238
2239 -- Check soft coding key flex value is null
2240 -- if it is null then the user did not update the
2241 -- soft coding keyflex so get it from table and assign
2242 -- old and new value equal to the table value
2243
2244 if p_old_value1 is null and p_new_value1 is null then
2245
2246 dbg('Assigning soft coding keyflex id from table values');
2247
2248 ln_old_value1 := ln_soft_coding_keyflex_id ;
2249 ln_new_value1 := ln_soft_coding_keyflex_id ;
2250 end if;
2251
2252 dbg('After the values got from the table values');
2253 dbg('Column Name :' || 'LOCATION_ID' );
2254 dbg('Old Value :' || to_char(ln_old_value) );
2255 dbg('new Value :' || to_char(ln_new_value) );
2256 dbg('Column Name :' || 'SOFT_CODING_KEYFLEX_ID' );
2257 dbg('Old Value :' || to_char(ln_old_value1) );
2258 dbg('new Value :' || to_char(ln_new_value1) );
2259
2260
2261 dbg('Dervie old GRE from old location and old SCL ' );
2262 ln_old_gre_id := derive_gre_from_loc_scl( ln_old_value
2263 ,ln_business_group_id
2264 ,ln_old_value1
2265 ,p_effective_date ) ;
2266 hr_utility.set_location(gv_package || lv_procedure_name, 30);
2267 ln_step := 2;
2268
2269 if ln_old_gre_id = -1 or ln_old_gre_id = -2 then
2270 if ln_old_gre_id = -1 then
2271 lv_gre_error_mesg := g_ambiguous_error ;
2272 else
2273 lv_gre_error_mesg := g_missing_gre_error ;
2274 end if;
2275
2276 dbg('Error in deriving GRE for OLD Location ' );
2277 msg('Error in deriving GRE for OLD Location ' );
2278
2279 arch_hire_separation_data(p_assignment_action_id => p_assignment_action_id,
2280 p_arch_type => 'E',
2281 p_assignment_id => p_assignment_id,
2282 p_assignment_number => lv_assignment_number,
2283 p_location_id => ln_old_value,
2284 p_effective_date => p_effective_date,
2285 p_gre_id => null,
2286 p_error_mesg => lv_gre_error_mesg,
2287 p_event_type => 'C'
2288 ) ;
2289
2290 end if ;
2291
2292 dbg('Dervie New GRE from new location and new SCL ' );
2293 ln_new_gre_id := derive_gre_from_loc_scl( ln_new_value
2294 ,ln_business_group_id
2295 ,ln_new_value1
2296 ,p_effective_date ) ;
2297 hr_utility.set_location(gv_package || lv_procedure_name, 30);
2298 ln_step := 3;
2299
2300 if ln_new_gre_id = -1 or ln_new_gre_id = -2 then
2301 if ln_new_gre_id = -1 then
2302 lv_gre_error_mesg := g_ambiguous_error ;
2303 else
2304 lv_gre_error_mesg := g_missing_gre_error ;
2305 end if;
2306
2307
2308 dbg('Error in deriving GRE for NEW Location ' );
2309 msg('Error in deriving GRE for NEW Location ' );
2310
2311 arch_hire_separation_data(p_assignment_action_id => p_assignment_action_id,
2312 p_arch_type => 'E',
2313 p_assignment_id => p_assignment_id,
2314 p_assignment_number => lv_assignment_number,
2315 p_location_id => ln_new_value,
2316 p_effective_date => p_effective_date,
2317 p_gre_id => null,
2318 p_error_mesg => lv_gre_error_mesg,
2319 p_event_type => 'C'
2320 ) ;
2321 end if ;
2322
2323 ln_step := 4;
2324
2325 dbg('old GRE : ' || to_char(ln_old_gre_id) );
2326 dbg('new GRE : ' || to_char(ln_new_gre_id) );
2327
2328 if (ln_old_gre_id is not null and
2329 ln_new_gre_id is not null and
2330 ln_old_gre_id = ln_new_gre_id) then
2331
2332 hr_utility.set_location(gv_package || lv_procedure_name, 60);
2333 dbg('old and new GREs are same no need to process');
2334
2335 Return ;
2336
2337 end if;
2338
2339
2340 if ln_old_gre_id <> -1 and ln_old_gre_id <> -2 and ln_old_gre_id is not null then
2341 -- process for old GRE
2342 dbg('Process for old GRE ' );
2343
2344 -- Check any other active assignments exists with old_gre_id for this person_id
2345 dbg('Check any other active assignments exists for this employee ' );
2346 dbg(' assignment id = '||to_char(p_assignment_id) );
2347 dbg(' person id = '||to_char(ln_person_id) );
2348 dbg(' gre id = '||to_char(ln_old_gre_id) );
2349 dbg(' eff date = '||to_char(g_report_imp_date-1,'DD-MON-YYYY') );
2350
2351 lv_asg_flag := chk_active_asg_exists( p_assignment_id
2352 ,ln_person_id
2353 ,ln_old_gre_id
2354 ,g_report_imp_date-1
2355 ) ;
2356 if lv_asg_flag ='N' then
2357 dbg('no active assignments found so archive data as arch_type=S ' );
2358 arch_hire_separation_data( p_assignment_action_id => p_assignment_action_id,
2359 p_arch_type => 'S',
2360 p_assignment_id => p_assignment_id,
2361 p_assignment_number => lv_assignment_number,
2362 p_location_id => ln_old_value,
2363 p_effective_date => p_effective_date,
2364 p_gre_id => ln_old_gre_id,
2365 p_error_mesg => lv_gre_error_mesg,
2366 p_event_type => 'U'
2367 ) ;
2368 end if ;
2369 hr_utility.set_location(gv_package || lv_procedure_name, 80);
2370
2371 end if; -- old gre
2372
2373 if ln_new_gre_id <> -1 and ln_new_gre_id <> -2 and ln_new_gre_id is not null then
2374
2375 -- process for new GRE
2376 dbg('Process for new GRE ' );
2377 -- check record exists in Archive table for person_id, new_gre_id and effective_date
2378 dbg('Check record is archived in archive table with HIRE as info category' );
2379 dbg(' person id = '||to_char(ln_person_id) );
2380 dbg(' gre id = '||to_char(ln_new_gre_id) );
2381 dbg(' eff date = '||to_char(p_effective_date,'DD-MON-YYYY') );
2382 dbg(' Action info category = ' ||g_action_hire_category );
2383 open c_chk_archive( ln_person_id, ln_new_gre_id, p_effective_date, g_action_hire_category ) ;
2384 fetch c_chk_archive into lv_chk ;
2385 if c_chk_archive%notfound then
2386 close c_chk_archive ;
2387 -- record not found then
2388 dbg('HIRE Record not found in archive table');
2389 -- find out this person is reported to IMSS prior to g_report_imp_date by legacy system
2390 -- by looking at assignment records
2391 dbg('Check this person is reported to IMSS prior to rep imp date ' );
2392 dbg(' person id = '||to_char(ln_person_id) );
2393 dbg(' gre id = '||to_char(ln_new_gre_id) );
2394 dbg(' eff date = '||to_char(g_report_imp_date-1,'DD-MON-YYYY') );
2395
2396 lv_asg_flag := chk_active_asg_exists( p_assignment_id
2397 ,ln_person_id
2398 ,ln_new_gre_id
2399 ,g_report_imp_date-1
2400 ) ;
2401 if lv_asg_flag ='N' then
2402 -- archive the data
2403 dbg('Not reported to IMSS so write in arch plsql table arch_type=H ');
2404 arch_hire_separation_data(p_assignment_action_id => p_assignment_action_id,
2405 p_arch_type => 'H',
2406 p_assignment_id => p_assignment_id,
2407 p_assignment_number => lv_assignment_number,
2408 p_location_id => ln_new_value,
2409 p_effective_date => p_effective_date,
2410 p_gre_id => ln_new_gre_id ,
2411 p_error_mesg => null,
2412 p_event_type => 'U'
2413 ) ;
2414 end if;
2415 end if ; -- chk_archive
2416 end if ; -- new gre
2417
2418 ln_step := 5;
2419 hr_utility.set_location(gv_package || lv_procedure_name, 100);
2420
2421 dbg('Exiting process update event..........' );
2422
2423 exception
2424 when others then
2425 lv_error_message := 'Error at step ' || ln_step ||
2426 ' in ' || gv_package || lv_procedure_name;
2427 dbg(lv_error_message || '-' || sqlerrm);
2428
2429 hr_utility.raise_error;
2430
2431 END process_update_event;
2432
2433
2434 /************************************************************
2435 Name : process_enddate_event
2436 Purpose : This procedure process enddate event.
2437 This procedure is called
2438 from interpret_all_asg_events procedure
2439 ************************************************************/
2440 PROCEDURE process_enddate_event( p_assignment_action_id in number
2441 ,p_assignment_id in number
2442 ,p_effective_date in date
2443 ,p_old_value in varchar2
2444 ,p_new_value in varchar2
2445 )
2446 IS
2447
2448 cursor c_asg_details(cp_assignment_id in number,
2449 cp_effective_date in date )
2450 is
2451 select paf.person_id,paf.assignment_number,paf.location_id,
2452 paf.soft_coding_keyflex_id, pas.per_system_status,
2453 paf.business_group_id
2454 from per_all_assignments_f paf,
2455 per_assignment_status_types pas
2456 where paf.assignment_id = cp_assignment_id
2457 and pas.assignment_status_type_id = paf.assignment_status_type_id
2458 and cp_effective_date between paf.effective_start_date
2459 and paf.effective_end_date ;
2460
2461 -- Cursor to check the record exist in the archive table
2462 cursor c_chk_archive ( cp_assignment_id in number,
2463 cp_effective_date date,
2464 cp_action_info_category varchar2 )
2465 is
2466 select 'Y'
2467 from pay_action_information pai
2468 where pai.action_context_type ='AAP'
2469 and pai.action_information_category = cp_action_info_category
2470 and pai.assignment_id = cp_assignment_id
2471 and trunc(pai.effective_date) = trunc(cp_effective_date) ;
2472
2473
2474 ln_gre_id NUMBER ;
2475 ln_person_id NUMBER ;
2476 lv_assignment_number VARCHAR2(30);
2477 ln_location_id NUMBER ;
2478 ln_business_group_id NUMBER ;
2479 ln_soft_coding_keyflex_id NUMBER ;
2480 lv_per_system_status VARCHAR2(100);
2481 lv_chk VARCHAR2(1);
2482 lv_gre_error_mesg VARCHAR2(100);
2483 lv_asg_flag VARCHAR2(1);
2484 lv_procedure_name VARCHAR2(100);
2485 lv_error_message VARCHAR2(200);
2486 ln_step NUMBER;
2487
2488 BEGIN
2489
2490 dbg('Entering process enddate event ..........');
2491
2492 lv_procedure_name := '.process_enddate_event';
2493 ln_step := 1;
2494 hr_utility.set_location(gv_package || lv_procedure_name, 10);
2495
2496 open c_asg_details( p_assignment_id,
2497 p_effective_date ) ;
2498 fetch c_asg_details into ln_person_id,
2499 lV_assignment_number,
2500 ln_location_id,
2501 ln_soft_coding_keyflex_id,
2502 lv_per_system_status,
2503 ln_business_group_id;
2504 close c_asg_details ;
2505
2506 dbg('Dervie GRE from location and SCL ' );
2507
2508 ln_gre_id := derive_gre_from_loc_scl( ln_location_id
2509 ,ln_business_group_id
2510 ,ln_soft_coding_keyflex_id
2511 ,p_effective_date ) ;
2512 if ln_gre_id = -1 or ln_gre_id = -2 then
2513 if ln_gre_id = -1 then
2514 lv_gre_error_mesg := g_ambiguous_error ;
2515 else
2516 lv_gre_error_mesg := g_missing_gre_error ;
2517 end if;
2518 arch_hire_separation_data(p_assignment_action_id => p_assignment_action_id,
2519 p_arch_type =>'E',
2520 p_assignment_id => p_assignment_id,
2521 p_assignment_number => lv_assignment_number,
2522 p_location_id => ln_location_id,
2523 p_effective_date => p_effective_date,
2524 p_gre_id => null,
2525 p_error_mesg => lv_gre_error_mesg,
2526 p_event_type => 'E'
2527 ) ;
2528 dbg('Error in deriving GRE for Location Id ' || to_char(ln_location_id) );
2529 return ;
2530 end if ; -- ln_gre_id
2531
2532 if to_char(p_effective_date,'DD/MM/YYYY') <> '31/12/4712' then
2533 dbg('Effective date is not equal to 31-DEC-4712' );
2534
2535 if lv_per_system_status = 'ACTIVE_ASSIGN' then
2536
2537 ln_step := 2;
2538
2539 -- Check any other active assignments exists with gre_id for this person_id
2540 dbg('Check any other active assignments exists for this employee ' );
2541 dbg(' assignment id = '||to_char(p_assignment_id) );
2542 dbg(' person id = '||to_char(ln_person_id) );
2543 dbg(' gre id = '||to_char(ln_gre_id) );
2544 dbg(' eff date = '||to_char(p_effective_date,'DD-MON-YYYY') );
2545
2546 lv_asg_flag :=chk_active_asg_exists( p_assignment_id
2547 ,ln_person_id
2548 ,ln_gre_id
2549 ,p_effective_date
2550 ) ;
2551 if lv_asg_flag ='N' then
2552 -- no record found then archive separation data
2553 dbg( 'No Active assignment found archive data as arch_type=S ');
2554 arch_hire_separation_data(p_assignment_action_id => p_assignment_action_id,
2555 p_arch_type =>'S',
2556 p_assignment_id => p_assignment_id,
2557 p_assignment_number => lv_assignment_number,
2558 p_location_id => ln_location_id,
2559 p_effective_date => p_effective_date,
2560 p_gre_id => ln_gre_id,
2561 p_error_mesg => null,
2562 p_event_type => 'U'
2563 ) ;
2564 end if ;
2565
2566 end if ; -- lv_per_system_status
2567
2568 else
2569 -- effective_date is equal to EOT 31-DEC-4712
2570 -- Check the separation record exists in archive table with
2571 -- effective date as old value
2572 -- if record exists then
2573 -- termination record is already archived so
2574 -- rehire this record with the status of R-Reverse Terminated
2575 -- else
2576 -- termination record is not archived so reverse term record is ignored
2577 -- end if
2578
2579 dbg('Effective date equal to 31-DEC-4712' );
2580 -- check record exists in Archive table for person_id, new_gre_id and effective_date
2581 dbg('Check record is archived in archive table with SEPARATION as info category' );
2582 dbg(' assignment id = '||to_char(p_assignment_id) );
2583 dbg(' eff date = '||p_old_value );
2584 dbg(' Action info category = ' ||g_action_sep_category );
2585 open c_chk_archive( p_assignment_id,
2586 to_date(p_old_value,'DD/MM/YY'),
2587 g_action_sep_category ) ;
2588 fetch c_chk_archive into lv_chk ;
2589 if c_chk_archive%found then
2590 -- termination record is already archived so
2591 -- rehire this record with the status of R-Reverse Terminated
2592 dbg('Separation record is reported to IMSS so write in arch plsql table arch_type=R ');
2593 arch_hire_separation_data(p_assignment_action_id => p_assignment_action_id,
2594 p_arch_type => 'R',
2595 p_assignment_id => p_assignment_id,
2596 p_assignment_number => lv_assignment_number,
2597 p_location_id => ln_location_id,
2598 p_effective_date => to_date(p_old_value,'DD/MM/YY') + 1,
2599 p_gre_id => ln_gre_id ,
2600 p_error_mesg => null,
2601 p_event_type => 'E'
2602 ) ;
2603 else
2604 dbg('Separation record is NOT reported to IMSS');
2605 dbg('Skipping the Reverse Termination record ');
2606 end if;
2607 close c_chk_archive ;
2608
2609 end if; --to_char(p_effective_date)
2610
2611 ln_step := 3;
2612 hr_utility.set_location(gv_package || lv_procedure_name, 100);
2613
2614 dbg('Exiting process end date event..........' );
2615
2616 exception
2617 when others then
2618 lv_error_message := 'Error at step ' || ln_step ||
2619 ' in ' || gv_package || lv_procedure_name;
2620 dbg(lv_error_message || '-' || sqlerrm);
2621
2622 hr_utility.raise_error;
2623
2624 END process_enddate_event;
2625
2626 /************************************************************
2627 Name : interpret_all_asg_events
2628 Purpose : interpret all the change events for an assignment
2629 ************************************************************/
2630 PROCEDURE interpret_all_asg_events(
2631 p_assignment_action_id in number
2632 ,p_assignment_id in number
2633 ,p_start_date in date
2634 ,p_end_date in date
2635 ,p_event_group_id in number
2636 )
2637
2638 IS
2639
2640 cursor c_asg_status_type(cp_assignment_status_type_id in number)
2641 is
2642 select PER_SYSTEM_STATUS from per_assignment_status_types
2643 where assignment_status_type_id = cp_assignment_status_type_id ;
2644
2645
2646 int_pkg_events pay_interpreter_pkg.t_detailed_output_table_type;
2647 asg_events_table t_int_asg_event_table;
2648 l_proration_dates pay_interpreter_pkg.t_proration_dates_table_type;
2649 l_proration_changes pay_interpreter_pkg.t_proration_type_table_type;
2650 l_pro_type_tab pay_interpreter_pkg.t_proration_type_table_type;
2651
2652 lv_change_values VARCHAR2(100);
2653 ln_old_value NUMBER ;
2654 ln_new_value NUMBER ;
2655 ln_old_value1 NUMBER ;
2656 ln_new_value1 NUMBER ;
2657
2658 lv_old_value VARCHAR2(100) ;
2659 lv_new_value VARCHAR2(100) ;
2660
2661 lv_old_asg_status VARCHAR2(100);
2662 lv_new_asg_status VARCHAR2(100);
2663
2664 lv_procedure_name VARCHAR2(100) ;
2665 lv_error_message VARCHAR2(200);
2666 ln_step NUMBER;
2667 lv_insert_found VARCHAR2(1);
2668 lv_enddate_found VARCHAR2(1);
2669 lv_row_found VARCHAR2(1);
2670
2671 ln_index NUMBER ;
2672
2673
2674 BEGIN
2675
2676 dbg('Entering interpret_all_asg_events...........' );
2677 dbg('Processing Assignment Id '|| to_char(p_assignment_id) );
2678 msg('Processing Assignment Id '|| to_char(p_assignment_id) );
2679
2680 lv_procedure_name := '.interpret_all_asg_events';
2681
2682 hr_utility.set_location(gv_package || lv_procedure_name, 10);
2683 ln_step := 1;
2684 dbg('call the interpreter pkg ' );
2685
2686 -- call the interpreter pkg
2687 pay_interpreter_pkg.entry_affected(
2688 p_element_entry_id => null
2689 ,p_assignment_action_id => NULL
2690 ,p_assignment_id => p_assignment_id
2691 ,p_mode => NULL
2692 ,p_process => NULL
2693 ,p_event_group_id => p_event_group_id
2694 ,p_process_mode => 'ENTRY_CREATION_DATE'
2695 ,p_start_date => p_start_date
2696 ,p_end_date => p_end_date
2697 ,p_unique_sort => 'N' -- tells the interpreter not to do a unique sort
2698 ,p_business_group_id => null
2699 ,t_detailed_output => int_pkg_events --OUTPUT OF RESULTS
2700 ,t_proration_dates => l_proration_dates
2701 ,t_proration_change_type => l_proration_changes
2702 ,t_proration_type => l_pro_type_tab);
2703
2704 dbg('Rows returned from pay_interpreter_pkg.entry_affected are');
2705 FOR i in 1..int_pkg_events.COUNT
2706 LOOP
2707 dbg('Result row :' ||to_char(i));
2708 dbg('Datetracked_event: '||int_pkg_events(i).datetracked_event );
2709 dbg('Change_mode : '||int_pkg_events(i).change_mode );
2710 dbg('Effective_date : '||to_char(int_pkg_events(i).effective_date,'DD-MON-YYYY')) ;
2711 dbg('dated_table_id : '||TO_CHAR(int_pkg_events(i).dated_table_id)) ;
2712 dbg('column_name : '||int_pkg_events(i).column_name ) ;
2713 dbg('Update_type : '||int_pkg_events(i).update_type ) ;
2714 dbg('old_value : '||int_pkg_events(i).old_value ) ;
2715 dbg('new_value : '||int_pkg_events(i).new_value ) ;
2716 dbg('change_values : '||int_pkg_events(i).change_values ) ;
2717 END LOOP ;
2718 dbg('Total rows returned from interpreter pkg ' || to_char(int_pkg_events.COUNT) );
2719
2720 /* The following is the multiple scenerios and the event records returned from the
2721 Interpreter pacakge
2722
2723 1) User Entry : Insert record
2724 on 01-JAN-2004 record created
2725 (default record inserted while entering the person record)
2726 Int pkg Returns : Returns one record
2727 Process : Process only the insert record
2728
2729 2) User Entry : Insert followed by multiple correction record
2730 a)on 01-JAN-2004 record inserted
2731 b)DTrack 01-JAN-2004 corrected either location or GRE in the SCL
2732
2733 Int pkg Returns : Returns 3 records
2734 i) Insert
2735 ii) Location correction
2736 iii)SCL correction
2737
2738 Process : Process only the insert and skip the correction records
2739
2740 3) User Entry : Insert record and Update record followed by correction record
2741 a)on 01-JAN-2004 record inserted
2742 b)DTrack 05-JAN-2004 updated either location or GRE in the SCL
2743 c)DTrack 05-JAN-2004 corrected either location or GRE in the SCL
2744
2745 Int pkg Returns : Returns 5 records
2746 i) insert
2747 ii) Location update
2748 iii) SCL update
2749 iv) location correction
2750 v) SCL Correction
2751
2752 Process : process insert and update record with the last correction record.
2753
2754 4) User Entry : Single or multiple correction record
2755 a)DTrack 05-JAN-2004 corrected either location or GRE in the SCL
2756 Int Pkg Returns : Single or Multiple records
2757 Process : Process the last correction record
2758 Example 05-JAN-2004 A to B on last update date 06-Jan-2004 at 10:00 am
2759 05-JAN-2004 B to C on last update date 06-Jan-2004 at 11:00 am
2760 05-JAN-2004 c to D on last update date 06-Jan-2004 at 12:00 am
2761 Take the last record and new value is D
2762
2763 5) User Entry : Single or multiple update record
2764 a)DTrack 05-FEB-2004 updated either location or GRE in the SCL
2765 Int Pkg Returns : Single or Multiple records
2766 Process : Process the update record with both location and scl
2767
2768 6) User Entry : Multiple update record followed by correction record
2769 a)DTrack 05-MAR-2004 updated both location and GRE in the SCL
2770 b)DTrack 05-MAR-2004 corrected both location and GRE in the SCL
2771 Int Pkg Returns : Single or Multiple records
2772 Process : Process the update record with the correction value
2773
2774 7) User Entry : Hire, Correction and Terminate record
2775 a)DTrack 01-JUL-2004 record Inserted
2776 b)DTrack 01-JUL-2004 corrected both location and GRE in the SCL
2777 C)End Employement on 31-JUL-2004 with
2778 actual_termination_date = final process date = 31-JUL-2004
2779 Int Pkg Returns : Single or Multiple records
2780 Process : Process Insert, correction and termination record
2781
2782 8) User Entry : Hire, Correction,Terminate record and Reverse terminate
2783 a)DTrack 01-JUL-2004 record Inserted
2784 b)DTrack 01-JUL-2004 corrected both location and GRE in the SCL
2785 C)End Employement on 31-JUL-2004 with
2786 actual_termination_date = final process date = 31-JUL-2004
2787 d)Reverse Terminate
2788 Int Pkg Returns : Single or Multiple records
2789 Process : Process Insert, correction records ( ignore termination and
2790 corresponding reverse termination)
2791 9) User Entry : Hire, Correction and Terminate record
2792 a)DTrack 01-JUL-2004 record Inserted
2793 b)DTrack 01-JUL-2004 corrected both location and GRE in the SCL
2794 C)End Employement on 31-JUL-2004 with
2795 actual_termination_date = 31-JUL-2004
2796 final process date = null
2797 Int Pkg Returns : Single or Multiple records
2798 Process : Process Insert, correction and termination record
2799
2800 10)User Entry : Hire, Correction,Terminate record and Reverse terminate
2801 a)DTrack 01-JUL-2004 record Inserted
2802 b)DTrack 01-JUL-2004 corrected both location and GRE in the SCL
2803 C)End Employement on 31-JUL-2004 with
2804 actual_termination_date = 31-JUL-2004
2805 final process date = null
2806 d)Reverse Terminate
2807 Int Pkg Returns : Single or Multiple records
2808 Process : Process Insert, correction records ( ignore termination and
2809 corresponding reverse termination)
2810 */
2811
2812 hr_utility.set_location(gv_package || lv_procedure_name, 20);
2813 ln_step := 2;
2814 dbg('Remove the duplication rows on the same effective date');
2815
2816 FOR i in 1..int_pkg_events.COUNT
2817 LOOP
2818 dbg('Processing int pkg results row :' ||to_char(i));
2819 dbg('Datetracked_event: '||int_pkg_events(i).datetracked_event );
2820 dbg('Change_mode : '||int_pkg_events(i).change_mode );
2821 dbg('Effective_date : '||to_char(int_pkg_events(i).effective_date,'DD-MON-YYYY')) ;
2822 dbg('dated_table_id : '||TO_CHAR(int_pkg_events(i).dated_table_id)) ;
2823 dbg('column_name : '||int_pkg_events(i).column_name ) ;
2824 dbg('Update_type : '||int_pkg_events(i).update_type ) ;
2825 dbg('old_value : '||int_pkg_events(i).old_value ) ;
2826 dbg('new_value : '||int_pkg_events(i).new_value ) ;
2827 dbg('change_values : '||int_pkg_events(i).change_values ) ;
2828
2829
2830 if int_pkg_events(i).update_type ='I' then
2831 ln_index := asg_events_table.COUNT + 1 ;
2832 asg_events_table(ln_index).update_type := int_pkg_events(i).update_type ;
2833 asg_events_table(ln_index).effective_date := int_pkg_events(i).effective_date ;
2834 asg_events_table(ln_index).column_name := int_pkg_events(i).column_name ;
2835 asg_events_table(ln_index).old_value := int_pkg_events(i).old_value ;
2836 asg_events_table(ln_index).new_value := int_pkg_events(i).new_value ;
2837
2838 elsif int_pkg_events(i).update_type ='E' then
2839 -- convert the values from change_values to old and new value
2840 -- change values will have <old_value> -> <new_value> ie 31-DEC-12 -> 31-JUL-04
2841 lv_change_values := int_pkg_events(i).change_values ;
2842 if ltrim(rtrim(SUBSTR(lv_change_values,1,INSTR(lv_change_values,'->')-1))) = '<null>' then
2843 lv_old_value := '<null>';
2844 else
2845 lv_old_value := ltrim(rtrim(SUBSTR(lv_change_values,1,INSTR(lv_change_values,'->')-1)));
2846 end if;
2847 if ltrim(rtrim(SUBSTR(lv_change_values,INSTR(lv_change_values,'->')+3))) = '<null>' then
2848 lv_new_value := '<null>';
2849 else
2850 lv_new_value := ltrim(rtrim(SUBSTR(lv_change_values,INSTR(lv_change_values,'->')+3))) ;
2851 end if;
2852 -- add row in asg_events_table
2853 ln_index := asg_events_table.COUNT + 1 ;
2854 asg_events_table(ln_index).update_type := int_pkg_events(i).update_type ;
2855 asg_events_table(ln_index).effective_date := int_pkg_events(i).effective_date ;
2856 asg_events_table(ln_index).column_name := int_pkg_events(i).column_name ;
2857 asg_events_table(ln_index).old_value := lv_old_value ;
2858 asg_events_table(ln_index).new_value := lv_new_value ;
2859
2860 elsif (int_pkg_events(i).column_name = 'ASSIGNMENT_STATUS_TYPE_ID') AND
2861 (int_pkg_events(i).update_type ='C' or
2862 int_pkg_events(i).update_type ='U' ) THEN
2863
2864 -- convert the values from change_values to old and new value
2865 dbg( 'convert the values from change_values to old and new value' );
2866 if int_pkg_events(i).update_type = 'C' then
2867 -- change values will have <old_value> -> <new_value> ie 590 -> 610
2868 lv_change_values := int_pkg_events(i).change_values ;
2869 if ltrim(rtrim(SUBSTR(lv_change_values,1,INSTR(lv_change_values,'->')-1))) = '<null>' then
2870 lv_old_value := '<null>';
2871 else
2872 lv_old_value := ltrim(rtrim(SUBSTR(lv_change_values,1,INSTR(lv_change_values,'->')-1)));
2873 end if;
2874 if ltrim(rtrim(SUBSTR(lv_change_values,INSTR(lv_change_values,'->')+3))) = '<null>' then
2875 lv_new_value := '<null>';
2876 else
2877 lv_new_value := ltrim(rtrim(SUBSTR(lv_change_values,INSTR(lv_change_values,'->')+3))) ;
2878 end if;
2879 elsif int_pkg_events(i).update_type = 'U' then
2880 lv_old_value := int_pkg_events(i).old_value ;
2881 lv_new_value := int_pkg_events(i).new_value ;
2882 end if;
2883 dbg( 'old value :'||lv_old_value );
2884 dbg( 'new value :'||lv_new_value );
2885
2886 -- ACTIVE_ASSIGN to TERM_ASSIGN ok
2887 -- TERM_ASSIGN TO ACTIVE_ASSIGN This is a reverse termination so skip the record
2888
2889 if lv_old_value <> '<null>' then
2890 open c_asg_status_type(to_number(lv_old_value)) ;
2891 fetch c_asg_status_type into lv_old_asg_status ;
2892 close c_asg_status_type ;
2893 end if;
2894
2895 if lv_new_value <> '<null>' then
2896 open c_asg_status_type(to_number(lv_new_value)) ;
2897 fetch c_asg_status_type into lv_new_asg_status ;
2898 close c_asg_status_type ;
2899 end if;
2900
2901 dbg( 'old assignment status :'||lv_old_asg_status );
2902 dbg( 'new assignment status :'||lv_new_asg_status );
2903
2904 if lv_old_asg_status = 'ACTIVE_ASSIGN' and lv_new_asg_status='TERM_ASSIGN' then
2905 -- insert the record
2906 ln_index := asg_events_table.COUNT + 1 ;
2907 asg_events_table(ln_index).update_type := 'E' ;
2908 asg_events_table(ln_index).effective_date := int_pkg_events(i).effective_date - 1 ;
2909 asg_events_table(ln_index).column_name := int_pkg_events(i).column_name ;
2910 asg_events_table(ln_index).old_value := int_pkg_events(i).old_value ;
2911 asg_events_table(ln_index).new_value := int_pkg_events(i).new_value ;
2912 else
2913 dbg('Change of Assignment_Status_type_id event record is skipped') ;
2914 end if;
2915
2916
2917 elsif ( int_pkg_events(i).column_name = 'LOCATION_ID' or
2918 int_pkg_events(i).column_name = 'SOFT_CODING_KEYFLEX_ID' ) AND
2919 ( int_pkg_events(i).update_type ='C' or
2920 int_pkg_events(i).update_type ='U' ) THEN
2921
2922 -- check the row exists in asg_events_table with matching effective_date and
2923 -- update_type = I
2924 lV_insert_found := 'N' ;
2925 FOR j in 1..asg_events_table.COUNT
2926 LOOP
2927 if (asg_events_table(j).effective_date = int_pkg_events(i).effective_date and
2928 asg_events_table(j).update_type = 'I' ) then
2929 lV_insert_found :='Y' ;
2930 exit ;
2931 end if;
2932 END LOOP ;
2933 if lV_insert_found = 'Y' then
2934 dbg( 'row skipped from int_pkg_events as the insert record exists on the same effective date' );
2935 else
2936 -- convert the values from change_values to old and new value
2937 dbg( 'convert the values from change_values to old and new value' );
2938 if int_pkg_events(i).update_type = 'C' then
2939 -- change values will have <old_value> -> <new_value> ie 590 -> 610
2940 lv_change_values := int_pkg_events(i).change_values ;
2941 if ltrim(rtrim(SUBSTR(lv_change_values,1,INSTR(lv_change_values,'->')-1))) = '<null>' then
2942 lv_old_value := '<null>';
2943 else
2944 lv_old_value := ltrim(rtrim(SUBSTR(lv_change_values,1,INSTR(lv_change_values,'->')-1)));
2945 end if;
2946 if ltrim(rtrim(SUBSTR(lv_change_values,INSTR(lv_change_values,'->')+3))) = '<null>' then
2947 lv_new_value := '<null>';
2948 else
2949 lv_new_value := ltrim(rtrim(SUBSTR(lv_change_values,INSTR(lv_change_values,'->')+3))) ;
2950 end if;
2951 elsif int_pkg_events(i).update_type = 'U' then
2952 lv_old_value := int_pkg_events(i).old_value ;
2953 lv_new_value := int_pkg_events(i).new_value ;
2954 end if;
2955 dbg( 'old value :'||lv_old_value );
2956 dbg( 'new value :'||lv_new_value );
2957
2958 -- Check the row exists in asg_events_table with matching efective_date and
2959 -- update_type ='C' or update_type='U'
2960 lV_row_found := 'N' ;
2961 FOR j in 1..asg_events_table.COUNT
2962 LOOP
2963 if (asg_events_table(j).effective_date = int_pkg_events(i).effective_date and
2964 (asg_events_table(j).update_type = 'U' or asg_events_table(j).update_type = 'C')
2965 ) then
2966
2967 lV_row_found :='Y' ;
2968 -- record found so update the row with the current values
2969 if int_pkg_events(i).column_name = 'LOCATION_ID' then
2970 asg_events_table(j).column_name := int_pkg_events(i).column_name ;
2971 asg_events_table(j).new_value := lv_new_value ;
2972 if asg_events_table(j).old_value is null then
2973 asg_events_table(j).old_value := lv_old_value ;
2974 end if;
2975 elsif int_pkg_events(i).column_name = 'SOFT_CODING_KEYFLEX_ID' then
2976 asg_events_table(j).column_name1 := int_pkg_events(i).column_name ;
2977 asg_events_table(j).new_value1 := lv_new_value ;
2978 if asg_events_table(j).old_value1 is null then
2979 asg_events_table(j).old_value1 := lv_old_value ;
2980 end if;
2981 end if;
2982 dbg( 'row updated with current value as multiple correction/update record found' );
2983 exit ;
2984 end if;
2985 END LOOP ;
2986 if lv_row_found = 'N' then
2987 -- add row in asg_events_table
2988 dbg('record not found so add the record ');
2989 ln_index := asg_events_table.COUNT + 1 ;
2990 asg_events_table(ln_index).update_type := int_pkg_events(i).update_type ;
2991 asg_events_table(ln_index).effective_date := int_pkg_events(i).effective_date ;
2992
2993 if int_pkg_events(i).column_name = 'LOCATION_ID' then
2994 asg_events_table(ln_index).column_name := int_pkg_events(i).column_name ;
2995 asg_events_table(ln_index).old_value := lv_old_value ;
2996 asg_events_table(ln_index).new_value := lv_new_value ;
2997
2998 elsif int_pkg_events(i).column_name = 'SOFT_CODING_KEYFLEX_ID' then
2999 asg_events_table(ln_index).column_name1 := int_pkg_events(i).column_name ;
3000 asg_events_table(ln_index).old_value1 := lv_old_value ;
3001 asg_events_table(ln_index).new_value1 := lv_new_value ;
3002 end if;
3003 end if; -- lv_row_found
3004 end if; --lv_insert_found
3005 end if; -- update_type
3006
3007 END LOOP ;
3008
3009 hr_utility.set_location(gv_package || lv_procedure_name, 30);
3010 ln_step := 3;
3011 dbg('Process after removed the duplication rows' );
3012 dbg('Total rows need to process from the asg events is ' || to_char(asg_events_table.COUNT) );
3013
3014 FOR i in 1..asg_events_table.COUNT
3015 LOOP
3016
3017 dbg('Assignment Id :' || to_char(p_assignment_id) );
3018 dbg('Effective Date :' || to_char(asg_events_table(i).effective_date,'DD-MON-YYYY') );
3019 dbg('Update Type :' || asg_events_table(i).update_type );
3020 dbg('Column Name :' || asg_events_table(i).column_name );
3021 dbg('Old Value :' || asg_events_table(i).old_value );
3022 dbg('new Value :' || asg_events_table(i).new_value );
3023 dbg('Column Name :' || asg_events_table(i).column_name1 );
3024 dbg('Old Value :' || asg_events_table(i).old_value1);
3025 dbg('new Value :' || asg_events_table(i).new_value1 );
3026
3027
3028 if asg_events_table(i).update_type = 'I' then
3029
3030 dbg('call process insert event' );
3031 msg('Processing Insert Event' );
3032 process_insert_event( p_assignment_action_id
3033 ,p_assignment_id
3034 ,asg_events_table(i).effective_date
3035 ) ;
3036
3037 elsif asg_events_table(i).update_type = 'E' then
3038
3039 dbg('call process_endate_event' );
3040 msg('Processing Enddate Event' );
3041 process_enddate_event(p_assignment_action_id
3042 ,p_assignment_id
3043 ,asg_events_table(i).effective_date
3044 ,asg_events_table(i).old_value
3045 ,asg_events_table(i).new_value
3046 );
3047
3048 elsif ( asg_events_table(i).update_type = 'C' or
3049 asg_events_table(i).update_type = 'U' ) then
3050
3051 dbg('Event update type is C or U ' );
3052
3053 if asg_events_table(i).old_value = '<null>' then
3054 ln_old_value := null;
3055 else
3056 ln_old_value := to_number(asg_events_table(i).old_value);
3057 end if;
3058 if asg_events_table(i).new_value = '<null>' then
3059 ln_new_value := null;
3060 else
3061 ln_new_value := to_number(asg_events_table(i).new_value) ;
3062 end if;
3063 if asg_events_table(i).old_value1 = '<null>' then
3064 ln_old_value1 := null;
3065 else
3066 ln_old_value1 := to_number(asg_events_table(i).old_value1);
3067 end if;
3068 if asg_events_table(i).new_value1 = '<null>' then
3069 ln_new_value1 := null;
3070 else
3071 ln_new_value1 := to_number(asg_events_table(i).new_value1) ;
3072 end if;
3073
3074 dbg('After Old and New values converted to numeric ' );
3075 dbg('Assignment Id :' || to_char(p_assignment_id) );
3076 dbg('Effective Date :' || to_char(asg_events_table(i).effective_date,'DD-MON-YYYY') );
3077 dbg('Update Type :' || asg_events_table(i).update_type );
3078 dbg('Column Name :' || asg_events_table(i).column_name );
3079 dbg('Old Value :' || to_char(ln_old_value) );
3080 dbg('new Value :' || to_char(ln_new_value) );
3081 dbg('Column Name :' || asg_events_table(i).column_name1 );
3082 dbg('Old Value :' || to_char(ln_old_value1) );
3083 dbg('new Value :' || to_char(ln_new_value1) );
3084
3085 if asg_events_table(i).update_type = 'C' then
3086
3087 dbg('call process_correction_event' );
3088 msg('Processing Correction Event' );
3089 process_correction_event( p_assignment_action_id
3090 ,p_assignment_id
3091 ,asg_events_table(i).effective_date
3092 ,asg_events_table(i).column_name
3093 ,ln_old_value
3094 ,ln_new_value
3095 ,asg_events_table(i).column_name1
3096 ,ln_old_value1
3097 ,ln_new_value1
3098 );
3099
3100 else
3101 -- asg_events_table(i).update_type = 'U'
3102
3103 dbg('call process_update_event' );
3104 msg('Processing Update Event' );
3105 process_update_event( p_assignment_action_id
3106 ,p_assignment_id
3107 ,asg_events_table(i).effective_date
3108 ,asg_events_table(i).column_name
3109 ,ln_old_value
3110 ,ln_new_value
3111 ,asg_events_table(i).column_name1
3112 ,ln_old_value1
3113 ,ln_new_value1
3114 );
3115
3116 end if; -- C or U
3117
3118 end if;
3119
3120 END LOOP ;
3121
3122 ln_step := 4;
3123 hr_utility.set_location(gv_package || lv_procedure_name, 40);
3124
3125 dbg('Exiting interpret_all_asg_events...........' );
3126
3127
3128 exception
3129 when others then
3130 lv_error_message := 'Error at step ' || ln_step ||
3131 ' in ' || gv_package || lv_procedure_name;
3132 dbg(lv_error_message || '-' || sqlerrm);
3133 hr_utility.raise_error;
3134
3135 END interpret_all_asg_events ;
3136
3137 /************************************************************
3138 Name : archive_data
3139 Purpose : This procedure Archives data which will be used
3140 in the SS Worksheet report and magtape report.
3141 ************************************************************/
3142 PROCEDURE archive_data(p_assignment_action_id in number
3143 ,p_effective_date in date)
3144 IS
3145
3146 cursor c_asg_action_info (cp_assignment_action in number) is
3147 select paa.payroll_action_id,
3148 paa.assignment_id,
3149 paa.tax_unit_id
3150 from pay_assignment_actions paa
3151 where paa.assignment_action_id = cp_assignment_action;
3152
3153
3154 lv_procedure_name VARCHAR2(100);
3155 lv_error_message VARCHAR2(200);
3156 ln_step NUMBER;
3157
3158 ln_payroll_action_id NUMBER;
3159 ln_assignment_action_id NUMBER;
3160 ln_assignment_iD NUMBER;
3161 ln_tax_unit_id NUMBER;
3162 ld_start_date DATE;
3163 ld_end_date DATE;
3164 ln_business_group_id NUMBER;
3165 ln_tran_gre_id NUMBER;
3166 ln_gre_id NUMBER;
3167 ln_event_group_id NUMBER;
3168
3169 BEGIN
3170
3171 dbg('Entering archive data ...........');
3172 dbg('assignment action id is ' || to_char(p_assignment_action_id) );
3173
3174 lv_procedure_name := '.archive_data';
3175 hr_utility.set_location(gv_package || lv_procedure_name, 10);
3176 ln_step := 1;
3177
3178 dbg('Get Payroll Action Id ');
3179 open c_asg_action_info (p_assignment_action_id);
3180 fetch c_asg_action_info into ln_payroll_action_id,
3181 ln_assignment_id,
3182 ln_tax_unit_id ;
3183 close c_asg_action_info;
3184 dbg('Payroll action id' || to_char(ln_payroll_action_id) );
3185
3186 hr_utility.set_location(gv_package || lv_procedure_name, 20);
3187 ln_step := 2;
3188 dbg('Get parameter information from pay_payroll_actions table' ) ;
3189 get_payroll_action_info(p_payroll_action_id => ln_payroll_action_id
3190 ,p_start_date => ld_start_date
3191 ,p_end_date => ld_end_date
3192 ,p_business_group_id => ln_business_group_id
3193 ,p_tran_gre_id => ln_tran_gre_id
3194 ,p_gre_id => ln_gre_id
3195 ,p_event_group_id => ln_event_group_id);
3196
3197 hr_utility.set_location(gv_package || lv_procedure_name, 30);
3198 ln_step := 3;
3199 dbg('call Interpret_all_asg_events');
3200
3201 interpret_all_asg_events( p_assignment_action_id => p_assignment_action_id
3202 ,p_assignment_id => ln_assignment_id
3203 ,p_start_date => ld_start_date
3204 ,p_end_date => ld_end_date
3205 ,p_event_group_id => ln_event_group_id
3206 ) ;
3207
3208 hr_utility.set_location(gv_package || lv_procedure_name, 40);
3209
3210 dbg('Exiting archive data ...........');
3211
3212 EXCEPTION
3213 when others then
3214 lv_error_message := 'Error at step ' || ln_step || ' in ' ||
3215 gv_package || lv_procedure_name;
3216 dbg(lv_error_message || '-' || sqlerrm);
3217 hr_utility.raise_error;
3218
3219 end archive_data;
3220
3221
3222 --begin
3223 --hr_utility.trace_on (null, 'SSAFFL');
3224
3225 end per_mx_ssaffl_archive;
[Home] [Help]