[Home] [Help]
PACKAGE BODY: APPS.PER_MX_SSAFFL_DISPMAG
Source
1 PACKAGE BODY per_mx_ssaffl_dispmag AS
2 /* $Header: permxdispmag.pkb 120.1 2006/05/18 23:58:38 vpandya 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_dispmag
21
22 Description : This package is used by the Social Security Affiliation
23 Magnetic (DISPMAG) report to produce the DISPMAG Magnetic file.
24
25 Change List
26 -----------
27 Date Name Vers Bug No Description
28 ----------- ---------- ------ ------- --------------------------
29 28-MAY-2004 kthirmiy 115.0 Created.
30 12-JUL-2004 kthirmiy 115.1 3748081 added nvl condition on getting the names
31 3751486 in the format_dispmag_emp_record procedure
32 to fix the bug
33 14-JUL-2004 kthirmiy 115.2 3766730 added upper to name components
34 15-Jul-2004 kthirmiy 115.3 3753718 added validation logic in
35 format_dispmag_emp_record to write in
36 error exception file
37 05-Aug-2004 kthirmiy 115.4 3794229 Added to write the error mesg using
38 push_message.
39 3815904 changed medical center to Prefix with 0.
40 19-Nov-2004 kthirmiy 115.5 added salary_details in the
41 format_dispmag_emp_record for
42 Social Security Salary Modification report
43 02-Dec-2004 kthirmiy 115.6 Added format to IDW to retrive 4,2 with
44 implied decimal
45 06-May-2005 kthirmiy 115.7 4353084 removed the redundant use of bind variable
46 payroll_action_id
47 18-May-2006 vpandya 115.8 5234421 Calling pay_mx_rules.strip_spl_chars
48 for all names that are printed in
49 output files to remove special
50 characters.
51 ******************************************************************************/
52
53 --
54 -- < PRIVATE GLOBALS > ---------------------------------------------------
55 --
56
57 gv_package VARCHAR2(100) ;
58 g_concurrent_flag VARCHAR2(1) ;
59 g_debug_flag VARCHAR2(1) ;
60
61
62
63 /******************************************************************************
64 Name : msg
65 Purpose : Log a message, either using fnd_file, or hr_utility.trace
66 ******************************************************************************/
67
68 PROCEDURE msg(p_text VARCHAR2)
69 IS
70 --
71 BEGIN
72 -- Write to the concurrent request log
73 fnd_file.put_line(fnd_file.log, p_text);
74
75 END msg;
76
77 /******************************************************************************
78 Name : dbg
79 Purpose : Log a message, either using fnd_file, or hr_utility.trace
80 if debuggging is enabled
81 ******************************************************************************/
82 PROCEDURE dbg(p_text VARCHAR2) IS
83
84 BEGIN
85
86 IF (g_debug_flag = 'Y') THEN
87 IF (g_concurrent_flag = 'Y') THEN
88 -- Write to the concurrent request log
89 fnd_file.put_line(fnd_file.log, p_text);
90 ELSE
91 -- Use HR trace
92 hr_utility.trace(p_text);
93 END IF;
94 END IF;
95
96 END dbg;
97
98
99 /******************************************************************************
100 Name : get_payroll_action_info
101 Purpose : This returns the Payroll Action level
102 information for SS Affiliation Magnetic DISPMAG report.
103 ******************************************************************************/
104 PROCEDURE get_payroll_action_info(p_payroll_action_id in number
105 ,p_business_group_id out nocopy number
106 ,p_trans_gre_id out nocopy number
107 ,p_gre_id out nocopy number
108 ,p_affl_type out nocopy varchar2
109 )
110 IS
111 -- cursor to get all the parameters from pay_payroll_actions table
112
113 cursor c_payroll_Action_info(cp_payroll_action_id in number) is
114 select business_group_id,
115 report_qualifier,
116 to_number(ltrim(rtrim(substr(legislative_parameters,
117 instr(legislative_parameters,
118 'GRE_ID=')
119 + length('GRE_ID='))))),
120 to_number(ltrim(rtrim(substr(legislative_parameters,
121 instr(legislative_parameters,
122 'TRANS_GRE=')
123 + length('TRANS_GRE='),
124 (instr(legislative_parameters,
125 'GRE_ID=') - 1 )
126 - (instr(legislative_parameters,
127 'TRANS_GRE=')
128 + length('TRANS_GRE='))))))
129 from pay_payroll_actions
130 where payroll_action_id = cp_payroll_action_id;
131
132
133 ln_business_group_id NUMBER;
134 ln_trans_gre_id NUMBER;
135 ln_gre_id NUMBER;
136 lv_affl_type VARCHAR2(11) ;
137
138 lv_procedure_name VARCHAR2(100) ;
139 lv_error_message VARCHAR2(200) ;
140 ln_step NUMBER;
141
142 BEGIN
143
144 lv_procedure_name := '.get_payroll_action_info';
145
146 hr_utility.set_location(gv_package || lv_procedure_name, 10);
147 ln_step := 1;
148 dbg('Entering get_payroll_action_info .......');
149
150 -- open the cursor to get all the parameters from pay_payroll_actions table
151 open c_payroll_action_info(p_payroll_action_id);
152 fetch c_payroll_action_info into ln_business_group_id,
153 lv_affl_type,
154 ln_gre_id,
155 ln_trans_gre_id
156 ;
157 close c_payroll_action_info;
158
159 ln_step := 2;
160
161 p_business_group_id := ln_business_group_id;
162 p_affl_type := lv_affl_type ;
163 p_gre_id := ln_gre_id;
164 p_trans_gre_id := ln_trans_gre_id;
165
166
167 dbg('business group id : ' || to_char(p_business_group_id)) ;
168 dbg('affliation type : ' || p_affl_type );
169 dbg('transmitter gre id : ' || to_char(p_trans_gre_id)) ;
170 dbg('gre id : ' || to_char(p_gre_id)) ;
171
172 hr_utility.set_location(gv_package || lv_procedure_name, 20);
173 ln_step := 3;
174
175 dbg('Exiting get_payroll_action_info .......');
176
177 EXCEPTION
178 when others then
179 lv_error_message := 'Error at step ' || ln_step || ' in ' ||
180 gv_package || lv_procedure_name;
181
182 dbg(lv_error_message || '-' || sqlerrm);
183 hr_utility.raise_error;
184
185 END get_payroll_action_info;
186
187
188 /******************************************************************
189 Name : range_cursor
190 Purpose : This returns the select statement that is
191 used to created the range rows for the
192 Social Security Affiliation Magnetic DISPMAG report.
193 Arguments :
194 ******************************************************************/
195 PROCEDURE range_cursor( p_payroll_action_id in number
196 ,p_sqlstr out nocopy varchar2)
197 IS
198
199 ln_business_group_id NUMBER;
200 ln_trans_gre_id NUMBER;
201 ln_gre_id NUMBER;
202 lv_affl_type VARCHAR2(11) ;
203
204 lv_sql_string VARCHAR2(32000);
205 lv_procedure_name VARCHAR2(100) ;
206
207 BEGIN
208
209 dbg('Entering range_cursor ....... ') ;
210
211 gv_package := 'per_mx_ssaffl_dispmag' ;
212
213 g_debug_flag := 'Y' ;
214 -- g_concurrent_flag := 'Y' ;
215
216 lv_procedure_name := '.range_cursor';
217 hr_utility.set_location(gv_package || lv_procedure_name, 10);
218
219 -- Get all the parameter information from pay_payroll_actions table
220 dbg('Get parameter information from pay_payroll_actions table' ) ;
221
222 get_payroll_action_info(p_payroll_action_id => p_payroll_action_id
223 ,p_business_group_id => ln_business_group_id
224 ,p_trans_gre_id => ln_trans_gre_id
225 ,p_gre_id => ln_gre_id
226 ,p_affl_type => lv_affl_type);
227
228 hr_utility.set_location(gv_package || lv_procedure_name, 20);
229
230 lv_sql_string := 'select distinct pai.assignment_id
231 from pay_action_information pai
232 where pai.action_information_category =
233 decode(''' ||lv_affl_type|| ''',''HIRES'',''MX SS HIRE DETAILS'',''SEPARATIONS'',''MX SS SEPARATION DETAILS'')
234 and pai.action_information22 =''A''
235 and :payroll_action_id > 0 ' ;
236
237 hr_utility.set_location(gv_package || lv_procedure_name, 30);
238 p_sqlstr := lv_sql_string;
239 hr_utility.set_location(gv_package || lv_procedure_name, 40);
240
241 dbg('Exiting range_cursor .......') ;
242
243 END range_cursor;
244
245
246 /************************************************************
247 Name : action_creation
248 Purpose : This creates the assignment actions for
249 a specific chunk of people to be archived
250 by the SS Affiliation Magnetic DISPMAG Report.
251 Arguments :
252 Notes : Calls procedure - get_payroll_action_info
253 ************************************************************/
254 PROCEDURE action_creation(
255 p_payroll_action_id in number
256 ,p_start_assignment_id in number
257 ,p_end_assignment_id in number
258 ,p_chunk in number)
259 IS
260
261 cursor c_get_asg( cp_start_assignment_id in number
262 ,cp_end_assignment_id in number
263 ,cp_trans_gre_id in number
264 ,cp_gre_id in number
265 ,cp_affl_type in varchar2
266 ) is
267
268 select pai.action_context_id,pai.assignment_id,pai.tax_unit_id
269 from pay_action_information pai
270 where pai.assignment_id between cp_start_assignment_id and cp_end_assignment_id
271 and pai.action_information_category =
272 decode(cp_affl_type,'HIRES','MX SS HIRE DETAILS','SEPARATIONS','MX SS SEPARATION DETAILS')
273 and pai.action_information22 ='A'
274 and pai.action_context_type='AAP'
275 and (( cp_trans_gre_id is not null and cp_gre_id is not null and pai.tax_unit_id= cp_gre_id )
276 or ( cp_trans_gre_id is not null and cp_gre_id is null and
277 pai.tax_unit_id in
278 (select organization_id
279 from hr_organization_information hoi
280 where hoi.org_information_context = 'MX_SOC_SEC_DETAILS'
281 and ((org_information6 = cp_trans_gre_id ) OR ( organization_id = cp_trans_gre_id and org_information3='Y')))))
282 and not exists (
283 select 'Y'
284 from pay_action_interlocks pal,
285 pay_assignment_actions paa,
286 pay_payroll_actions ppa
287 where pal.locked_action_id = pai.action_context_id
288 and pal.locking_action_id = paa.assignment_action_id
289 and paa.payroll_action_id = ppa.payroll_action_id
290 and ppa.action_type='X'
291 and ppa.report_type='SS_AFFILIATION'
292 and ppa.report_qualifier=cp_affl_type
293 and paa.tax_unit_id = pai.tax_unit_id
294 ) ;
295
296
297 ln_business_group_id NUMBER;
298 ln_trans_gre_id NUMBER;
299 ln_gre_id NUMBER;
300 lv_affl_type VARCHAR2(11) ;
301 ln_action_context_id NUMBER;
302 ln_assignment_id NUMBER;
303 ln_action_id NUMBER;
304 ln_tax_unit_id NUMBER;
305
306 lv_procedure_name VARCHAR2(100) ;
307 lv_error_message VARCHAR2(200);
308 ln_step NUMBER;
309
310 begin
311
312 dbg('Entering Action creation ..............') ;
313
314 gv_package := 'per_mx_ssaffl_dispmag' ;
315 g_debug_flag := 'Y' ;
316 -- g_concurrent_flag := 'Y' ;
317
318 lv_procedure_name := '.action_creation';
319
320 hr_utility.set_location(gv_package || lv_procedure_name, 10);
321 ln_step := 1;
322 dbg('Get parameter information from pay_payroll_actions table' ) ;
323
324 get_payroll_action_info(p_payroll_action_id => p_payroll_action_id
325 ,p_business_group_id => ln_business_group_id
326 ,p_trans_gre_id => ln_trans_gre_id
327 ,p_gre_id => ln_gre_id
328 ,p_affl_type => lv_affl_type);
329
330
331 hr_utility.set_location(gv_package || lv_procedure_name, 20);
332 ln_step := 2;
333 dbg('Action creation Query parameters') ;
334 dbg('Start assignment id : ' || to_char(p_start_assignment_id));
335 dbg('End assignment id : ' || to_char(p_end_assignment_id));
336 dbg('tansmitter gre id : ' || to_char(ln_trans_gre_id));
337 dbg('gre id : ' || to_char(ln_gre_id));
338 dbg('affl type : ' || lv_affl_type);
339 dbg('business_group_id : ' || to_char(ln_business_group_id));
340
341 open c_get_asg( p_start_assignment_id
342 ,p_end_assignment_id
343 ,ln_trans_gre_id
344 ,ln_gre_id
345 ,lv_affl_type);
346
347 -- Loop for all rows returned for SQL statement.
348 hr_utility.set_location(gv_package || lv_procedure_name, 30);
349 ln_step := 3;
350
351 loop
352 fetch c_get_asg into ln_action_context_id,ln_assignment_id,ln_tax_unit_id ;
353 exit when c_get_asg%notfound;
354
355 -- create assignment action
356
357 select pay_assignment_actions_s.nextval
358 into ln_action_id
359 from dual;
360
361
362 -- insert into pay_assignment_actions.
363 hr_nonrun_asact.insact(ln_action_id,
364 ln_assignment_id,
365 p_payroll_action_id,
366 p_chunk,
367 ln_tax_unit_id, -- nvl(ln_gre_id,ln_trans_gre_id),
368 null,
369 'U',
370 null);
371
372 dbg('assignment action id is ' || to_char(ln_action_id) );
373
374 -- insert an interlock to this action
375 dbg('Locking Action = ' || to_char(ln_action_id));
376 dbg('Locked Action = ' || to_char(ln_action_context_id));
377 hr_nonrun_asact.insint(ln_action_id,
378 ln_action_context_id);
379
380
381 end loop;
382 close c_get_asg;
383
384 hr_utility.set_location(gv_package || lv_procedure_name, 50);
385 ln_step := 5;
386
387 dbg('Exiting Action creation ..............') ;
388
389
390 EXCEPTION
391 when others then
392 lv_error_message := 'Error at step ' || ln_step || ' in ' ||
393 gv_package || lv_procedure_name;
394 dbg(lv_error_message || '-' || sqlerrm);
395 hr_utility.raise_error;
396
397 END action_creation;
398
399 /************************************************************
400 Name : archinit
401 Purpose : This procedure performs all the required initialization.
402 Arguments :
403 Notes :
404 ************************************************************/
405 PROCEDURE archinit(
406 p_payroll_action_id in number)
407 IS
408
409 ln_step NUMBER;
410 lv_procedure_name VARCHAR2(100) ;
411
412 BEGIN
413
414
415 dbg('Entering archinit .............');
416
417 gv_package := 'per_mx_ssaffl_dispmag' ;
418 g_debug_flag := 'Y' ; -- Y means debug is ON
419 -- g_concurrent_flag := 'Y' ; -- Y means write in log file
420 -- Null/N means write as a hr_utility.trace
421
422 lv_procedure_name := '.archinit';
423
424 hr_utility.set_location(gv_package || lv_procedure_name, 10);
425 ln_step := 1;
426
427 dbg('Exiting archinit .............');
428
429 END archinit;
430
431 /************************************************************
432 Name : format_data_string
433 Purpose : This function returns the input string formatted
434 with csv data and column delimitter
435 Arguments :
436 ************************************************************/
437 FUNCTION format_data_string
438 (p_input_string in varchar2
439 )
440 RETURN VARCHAR2
441 IS
442
443 lv_format varchar2(1000);
444 lv_csv_delimiter VARCHAR2(1) ;
445 lv_csv_data_delimiter VARCHAR2(1) ;
446
447 BEGIN
448
449 lv_csv_delimiter := ',';
450 lv_csv_data_delimiter := '"';
451
452 lv_format := lv_csv_data_delimiter || p_input_string ||
453 lv_csv_data_delimiter || lv_csv_delimiter;
454
455 return lv_format;
456
457 END format_data_string;
458
459
460 /************************************************************
461 Name : format_dispmag_emp_record
462 Purpose : This function retrieves the archived record from
463 pay action information table and format the record
464 in the dispmag employee record format.
465 This function is called from the DISPMAG_EMPLOYEE
466 fast formula by passing the parameters
467 Arguments : p_assignment_action_id - assignment_action_id context
468 p_affl_type - affl type parameter
469 p_flat_out - employee record output in
470 magnetic format
471 p_csvr_out - employee record output in
472 csv format
473 p_flat_ret_str_len - string length of flat record out
474 p_csvr_ret_str_len - string length of csv record out
475 p_error_flag - Error flag
476 p_error_mesg - Error Mesg
477 **************************************************************/
478 FUNCTION format_dispmag_emp_record (
479 p_assignment_action_id in number, -- context
480 p_affl_type in varchar2,
481 p_flat_out out nocopy varchar2,
482 p_csvr_out out nocopy varchar2,
483 p_flat_ret_str_len out nocopy number,
484 p_csvr_ret_str_len out nocopy number,
485 p_error_flag out nocopy varchar2,
486 p_error_mesg out nocopy varchar2
487 ) RETURN VARCHAR2
488 IS
489
490 /************************************************************
491 ** Cursor to get the hire/rehire affiliation records from
492 ** pay_action_information table
493 ************************************************************/
494
495 cursor c_hire_details( cp_assignment_action_id in number
496 ) is
497 select action_information1, -- employer ss id
498 action_information2, -- employer ss check digit
499 action_information3, -- employee ss id
500 action_information4, -- employee ss check digit
501 action_information5, -- Paternal Last Name
502 action_information7, -- Employee Name
503 action_information8, -- IDW
504 action_information10, -- Worker Type
505 action_information11, -- Salary Type
506 action_information12, -- Reduced Working Week
507 action_information13, -- Date of Hire/Rehire
508 action_information17, -- IMSS Waybill
509 rpad(action_information1,10,' ') || -- employer ss id
510 nvl(action_information2,' ') || -- employer ss check digit
511 rpad(action_information3,10,' ') || -- employee ss id
512 nvl(action_information4,' ') || -- employee ss check digit
513 rpad(substr(nvl(pay_mx_rules.STRIP_SPL_CHARS(upper(action_information5)),' '),1,27),27,' ') || -- Paternal Last Name
514 rpad(substr(nvl(pay_mx_rules.STRIP_SPL_CHARS(upper(action_information6)),' '),1,27),27,' ') || -- Maternal Last Name
515 rpad(substr(nvl(pay_mx_rules.STRIP_SPL_CHARS(upper(action_information7)),' '),1,27),27,' ') || -- Employee Name
516 lpad(to_char(to_number(nvl(action_information8,'0'))*100),6,'0') || -- IDW
517 rpad(' ',6,' ') || -- Filler
518 nvl(action_information10,' ')|| -- Worker Type
519 nvl(action_information11,' ') || -- Salary Type
520 nvl(action_information12,' ') || -- Reduced Working Week
521 rpad(nvl(action_information13,' '),8,' ') || -- Date of Hire/Rehire
522 lpad(nvl(action_information14,'000'),3,'0') || -- Medical Centre
523 rpad(' ',2,' ') || -- Filler
524 action_information16 || -- Type of Trans
525 rpad(action_information17,5,' ') || -- IMSS Waybill
526 rpad(action_information18,10,' ') || -- Worker Id
527 ' ' || -- Filler
528 rpad(action_information20,18,' ')|| -- CURP
529 action_information21 , -- Layout Identifier
530 format_data_string(rpad(action_information1,10,' ')) ||
531 format_data_string(nvl(action_information2,' ')) ||
532 format_data_string(rpad(action_information3,10,' ')) ||
533 format_data_string(nvl(action_information4,' ')) ||
534 format_data_string(rpad(substr(nvl(pay_mx_rules.STRIP_SPL_CHARS(upper(action_information5)),' '),1,27),27,' ')) ||
535 format_data_string(rpad(substr(nvl(pay_mx_rules.STRIP_SPL_CHARS(upper(action_information6)),' '),1,27),27,' ')) ||
536 format_data_string(rpad(substr(nvl(pay_mx_rules.STRIP_SPL_CHARS(upper(action_information7)),' '),1,27),27,' ')) ||
537 format_data_string(lpad(to_char(to_number(nvl(action_information8,'0'))*100),6,'0')) ||
538 format_data_string(rpad(' ',6,' ')) ||
539 format_data_string(nvl(action_information10,' ')) ||
540 format_data_string(nvl(action_information11,' ')) ||
541 format_data_string(nvl(action_information12,' ')) ||
542 format_data_string(rpad(nvl(action_information13,' '),8,' ')) ||
543 format_data_string(lpad(nvl(action_information14,'000'),3,'0')) ||
544 format_data_string(rpad(' ',2,' ')) ||
545 format_data_string(action_information16) ||
546 format_data_string(rpad(action_information17,5,' ')) ||
547 format_data_string(rpad(action_information18,10,' ')) ||
548 format_data_string(' ') ||
549 format_data_string(rpad(action_information20,18,' '))||
550 format_data_string(action_information21)
551 from pay_action_information pai,
552 pay_action_interlocks pal,
553 pay_assignment_actions paa
554 where pal.locking_action_id = cp_assignment_action_id
555 and pal.locked_action_id = pai.action_context_id
556 and pai.action_context_type ='AAP'
557 and pai.action_information_category = 'MX SS HIRE DETAILS'
558 and pai.action_information22 ='A'
559 and paa.assignment_action_id = pal.locking_action_id
560 and pai.tax_unit_id = paa.tax_unit_id ;
561
562
563 /************************************************************
564 ** Cursor to get the SEPARATION affiliation records from
565 ** pay_action_information table
566 ************************************************************/
567 cursor c_sep_details( cp_assignment_action_id in number
568 ) is
569
570 select action_information1, -- employer ss id
571 action_information2, -- employer ss check digit
572 action_information3, -- employee ss id
573 action_information4, -- employee ss check digit
574 action_information5, -- Paternal Last Name
575 action_information7, -- Employee Name
576 action_information9, -- Date of separation
577 action_information12, -- IMSS Waybill
578 action_information14, -- Leaving reason
579 rpad(action_information1,10,' ') || -- employer ss id
580 nvl(action_information2,' ') || -- employer ss check digit
581 rpad(action_information3,10,' ') || -- employee ss id
582 nvl(action_information4,' ') || -- employee ss check digit
583 rpad(substr(nvl(pay_mx_rules.STRIP_SPL_CHARS(upper(action_information5)),' '),1,27),27,' ') || -- Paternal Last Name
584 rpad(substr(nvl(pay_mx_rules.STRIP_SPL_CHARS(upper(action_information6)),' '),1,27),27,' ') || -- Maternal Last Name
585 rpad(substr(nvl(pay_mx_rules.STRIP_SPL_CHARS(upper(action_information7)),' '),1,27),27,' ') || -- Employee Name
586 rpad('0',15,'0') || -- Filler
587 rpad(nvl(action_information9,' '),8,' ') || -- Date of Emp Separation
588 rpad(' ',5,' ') || -- Filler
589 action_information11 || -- Type of Trans
590 rpad(nvl(action_information12,' '),5,' ') || -- IMSS Waybill
591 rpad(nvl(action_information13,' '),10,' ') || -- Worker Id
592 nvl(action_information14,' ') || -- Leaving reason
593 rpad(' ',18,' ') || -- Filler
594 action_information16 , -- Layout Identifier
595 format_data_string(rpad(action_information1,10,' ')) ||
596 format_data_string(nvl(action_information2,' ')) ||
597 format_data_string(rpad(action_information3,10,' ')) ||
598 format_data_string(nvl(action_information4,' ')) ||
599 format_data_string(rpad(substr(nvl(pay_mx_rules.STRIP_SPL_CHARS(upper(action_information5)),' '),1,27),27,' ')) ||
600 format_data_string(rpad(substr(nvl(pay_mx_rules.STRIP_SPL_CHARS(upper(action_information6)),' '),1,27),27,' ')) ||
601 format_data_string(rpad(substr(nvl(pay_mx_rules.STRIP_SPL_CHARS(upper(action_information7)),' '),1,27),27,' ')) ||
602 format_data_string(rpad('0',15,'0')) ||
603 format_data_string(rpad(action_information9,8,' ')) ||
604 format_data_string(rpad(' ',5,' ')) ||
605 format_data_string(action_information11) ||
606 format_data_string(rpad(action_information12,5,' ')) ||
607 format_data_string(rpad(action_information13,10,' ')) ||
608 format_data_string(nvl(action_information14,' ')) ||
609 format_data_string(rpad(' ',18,' ')) ||
610 format_data_string(action_information16)
611 from pay_action_information pai,
612 pay_action_interlocks pal,
613 pay_assignment_actions paa
614 where pal.locking_action_id = cp_assignment_action_id
615 and pal.locked_action_id = pai.action_context_id
616 and pai.action_context_type ='AAP'
617 and pai.action_information_category = 'MX SS SEPARATION DETAILS'
618 and pai.action_information22 ='A'
619 and paa.assignment_action_id = pal.locking_action_id
620 and pai.tax_unit_id = paa.tax_unit_id ;
621
622 /************************************************************
623 ** Cursor to get the salary affiliation records from
624 ** pay_action_information table
625 ************************************************************/
626
627 cursor c_salary_details( cp_assignment_action_id in number
628 ) is
629 select action_information1, -- employer ss id
630 action_information2, -- employer ss check digit
631 action_information3, -- employee ss id
632 action_information4, -- employee ss check digit
633 action_information5, -- Paternal Last Name
634 action_information7, -- Employee Name
635 action_information8, -- IDW
636 action_information10, -- Worker Type
637 action_information11, -- Salary Type
638 action_information12, -- Reduced Working Week
639 action_information13, -- Date of Salary modification
640 action_information17, -- IMSS Waybill
641 rpad(action_information1,10,' ') || -- employer ss id
642 nvl(action_information2,' ') || -- employer ss check digit
643 rpad(action_information3,10,' ') || -- employee ss id
644 nvl(action_information4,' ') || -- employee ss check digit
645 rpad(substr(nvl(pay_mx_rules.STRIP_SPL_CHARS(upper(action_information5)),' '),1,27),27,' ') || -- Paternal Last Name
646 rpad(substr(nvl(pay_mx_rules.STRIP_SPL_CHARS(upper(action_information6)),' '),1,27),27,' ') || -- Maternal Last Name
647 rpad(substr(nvl(pay_mx_rules.STRIP_SPL_CHARS(upper(action_information7)),' '),1,27),27,' ') || -- Employee Name
648 lpad(to_char(to_number(nvl(action_information8,'0'))*100),6,'0') || -- IDW
649 rpad(' ',6,' ') || -- Filler
650 nvl(action_information10,' ')|| -- Worker Type
651 nvl(action_information11,' ') || -- Salary Type
652 nvl(action_information12,' ') || -- Reduced Working Week
653 rpad(nvl(action_information13,' '),8,' ') || -- Date of Salary Modification
654 rpad(' ',5,' ') || -- Filler 5 spaces 3 for med center and 2
655 action_information16 || -- Type of Trans
656 rpad(action_information17,5,' ') || -- IMSS Waybill
657 rpad(action_information18,10,' ') || -- Worker Id
658 ' ' || -- Filler
659 rpad(action_information20,18,' ')|| -- CURP
660 action_information21 , -- Layout Identifier
661 format_data_string(rpad(action_information1,10,' ')) ||
662 format_data_string(nvl(action_information2,' ')) ||
663 format_data_string(rpad(action_information3,10,' ')) ||
664 format_data_string(nvl(action_information4,' ')) ||
665 format_data_string(rpad(substr(nvl(pay_mx_rules.STRIP_SPL_CHARS(upper(action_information5)),' '),1,27),27,' ')) ||
666 format_data_string(rpad(substr(nvl(pay_mx_rules.STRIP_SPL_CHARS(upper(action_information6)),' '),1,27),27,' ')) ||
667 format_data_string(rpad(substr(nvl(pay_mx_rules.STRIP_SPL_CHARS(upper(action_information7)),' '),1,27),27,' ')) ||
668 format_data_string(lpad(to_char(to_number(nvl(action_information8,'0'))*100),6,'0')) ||
669 format_data_string(rpad(' ',6,' ')) ||
670 format_data_string(nvl(action_information10,' ')) ||
671 format_data_string(nvl(action_information11,' ')) ||
672 format_data_string(nvl(action_information12,' ')) ||
673 format_data_string(rpad(nvl(action_information13,' '),8,' ')) ||
674 format_data_string(rpad(' ',5,' ')) ||
675 format_data_string(action_information16) ||
676 format_data_string(rpad(action_information17,5,' ')) ||
677 format_data_string(rpad(action_information18,10,' ')) ||
678 format_data_string(' ') ||
679 format_data_string(rpad(action_information20,18,' '))||
680 format_data_string(action_information21)
681 from pay_action_information pai
682 where pai.action_context_id = cp_assignment_action_id
683 and pai.action_information_category = 'MX SS SALARY DETAILS'
684 and pai.action_context_type ='AAP'
685 and pai.action_information22 ='A' ;
686
687 lv_return_value varchar2(100);
688 lv_er_ss_id varchar2(100);
689 lv_er_ss_chk_digit varchar2(100);
690 lv_ee_ss_id varchar2(100);
691 lv_ee_ss_chk_digit varchar2(100);
692 lv_paternal_last_name varchar2(100);
693 lv_name varchar2(100);
694 lv_IDW varchar2(100);
695 lv_worker_type varchar2(100);
696 lv_salary_type varchar2(100);
697 lv_rww varchar2(100);
698 lv_hire_date varchar2(100);
699 lv_sep_date varchar2(100);
700 lv_imss_waybill varchar2(100);
701 lv_leav_reason varchar2(100);
702 lv_flat_out varchar2(300);
703 lv_csvr_out varchar2(300);
704 lv_error_flag varchar2(1);
705 lv_error_mesg varchar2(300);
706
707 BEGIN
708
709 lv_return_value := '';
710 lv_er_ss_id := null ;
711 lv_er_ss_chk_digit := null ;
712 lv_ee_ss_id := null ;
713 lv_ee_ss_chk_digit := null ;
714 lv_paternal_last_name := null ;
715 lv_name := null ;
716 lv_IDW := null ;
717 lv_worker_type := null ;
718 lv_salary_type := null ;
719 lv_rww := null ;
720 lv_hire_date := null ;
721 lv_sep_date := null ;
722 lv_imss_waybill := null ;
723 lv_leav_reason := null ;
724 lv_flat_out := null ;
725 lv_csvr_out := null ;
726
727 lv_error_flag := 'N' ;
728 lv_error_mesg := '' ;
729
730 if p_affl_type ='HIRES' then -- Hires
731
732 open c_hire_details(p_assignment_action_id) ;
733 fetch c_hire_details into
734 lv_er_ss_id,
735 lv_er_ss_chk_digit,
736 lv_ee_ss_id,
737 lv_ee_ss_chk_digit,
738 lv_paternal_last_name,
739 lv_name,
740 lv_IDW,
741 lv_worker_type,
742 lv_salary_type,
743 lv_rww,
744 lv_hire_date,
745 lv_imss_waybill,
746 lv_flat_out, lv_csvr_out ;
747
748 if lv_er_ss_id is null or
749 lv_er_ss_chk_digit is null or
750 lv_ee_ss_id is null or
751 lv_ee_ss_chk_digit is null or
752 lv_name is null or
753 (to_number(nvl(lv_idw,'0')) <= 0 ) or
754 lv_worker_type is null or
755 lv_salary_type is null or
756 lv_rww is null or
757 lv_hire_date is null or
758 lv_imss_waybill is null then
759
760 lv_error_flag := 'Y' ;
761 lv_error_mesg := 'Error in DISPMAG record for Employee '||lv_paternal_last_name || ' - ' ;
762
763 if lv_er_ss_id is null then
764 lv_error_mesg := lv_error_mesg || 'Employer SS ID is Missing ' ;
765 pay_core_utils.push_message(800,'HR_MX_INVALID_DISPMAG_DATA','F');
766 pay_core_utils.push_token('RECORD_NAME','DISPMAG') ;
767 pay_core_utils.push_token('NAME_OR_NUMBER',lv_paternal_last_name) ;
768 pay_core_utils.push_token('DESCRIPTION','Employer SS ID is Missing') ;
769 end if;
770
771 if lv_er_ss_chk_digit is null then
772 lv_error_mesg := lv_error_mesg || 'Employer SS Check Digit is Missing ' ;
773 pay_core_utils.push_message(800,'HR_MX_INVALID_DISPMAG_DATA','F');
774 pay_core_utils.push_token('RECORD_NAME','DISPMAG') ;
775 pay_core_utils.push_token('NAME_OR_NUMBER',lv_paternal_last_name) ;
776 pay_core_utils.push_token('DESCRIPTION','Employer SS Check Digit is Missing') ;
777 end if;
778
779 if lv_ee_ss_id is null then
780 lv_error_mesg := lv_error_mesg || 'Employee SS ID is Missing ' ;
781 pay_core_utils.push_message(800,'HR_MX_INVALID_DISPMAG_DATA','F');
782 pay_core_utils.push_token('RECORD_NAME','DISPMAG') ;
783 pay_core_utils.push_token('NAME_OR_NUMBER',lv_paternal_last_name) ;
784 pay_core_utils.push_token('DESCRIPTION','Employee SS ID is Missing ') ;
785
786 end if;
787
788 if lv_ee_ss_chk_digit is null then
789 lv_error_mesg := lv_error_mesg || 'Employee SS Check Digit is Missing ' ;
790 pay_core_utils.push_message(800,'HR_MX_INVALID_DISPMAG_DATA','F');
791 pay_core_utils.push_token('RECORD_NAME','DISPMAG') ;
792 pay_core_utils.push_token('NAME_OR_NUMBER',lv_paternal_last_name) ;
793 pay_core_utils.push_token('DESCRIPTION','Employee SS Check Digit is Missing') ;
794
795 end if;
796
797
798 if lv_name is null then
799 lv_error_mesg := lv_error_mesg || 'First Name and/or Second Name is Missing ' ;
800 pay_core_utils.push_message(800,'HR_MX_INVALID_DISPMAG_DATA','F');
801 pay_core_utils.push_token('RECORD_NAME','DISPMAG') ;
802 pay_core_utils.push_token('NAME_OR_NUMBER',lv_paternal_last_name) ;
803 pay_core_utils.push_token('DESCRIPTION','First Name and/or Second Name is Missing ') ;
804
805 end if;
806
807
808 if to_number(nvl(lv_IDW,'0')) <= 0 then
809 lv_error_mesg := lv_error_mesg || 'IDW must be greater than zero ' ;
810 pay_core_utils.push_message(800,'HR_MX_INVALID_DISPMAG_DATA','F');
811 pay_core_utils.push_token('RECORD_NAME','DISPMAG') ;
812 pay_core_utils.push_token('NAME_OR_NUMBER',lv_paternal_last_name) ;
813 pay_core_utils.push_token('DESCRIPTION','IDW must be greater than zero ') ;
814
815 end if;
816
817 if lv_worker_type is null then
818 lv_error_mesg := lv_error_mesg || 'Worker Type is Missing ' ;
819 pay_core_utils.push_message(800,'HR_MX_INVALID_DISPMAG_DATA','F');
820 pay_core_utils.push_token('RECORD_NAME','DISPMAG') ;
821 pay_core_utils.push_token('NAME_OR_NUMBER',lv_paternal_last_name) ;
822 pay_core_utils.push_token('DESCRIPTION','Worker Type is Missing ') ;
823
824 end if;
825
826
827 if lv_salary_type is null then
828 lv_error_mesg := lv_error_mesg || 'Salary Type is Missing ' ;
829 pay_core_utils.push_message(800,'HR_MX_INVALID_DISPMAG_DATA','F');
830 pay_core_utils.push_token('RECORD_NAME','DISPMAG') ;
831 pay_core_utils.push_token('NAME_OR_NUMBER',lv_paternal_last_name) ;
832 pay_core_utils.push_token('DESCRIPTION','Salary Type is Missing ') ;
833
834 end if;
835
836 if lv_rww is null then
837 lv_error_mesg := lv_error_mesg || 'Reduced working week flag is Missing ' ;
838 pay_core_utils.push_message(800,'HR_MX_INVALID_DISPMAG_DATA','F');
839 pay_core_utils.push_token('RECORD_NAME','DISPMAG') ;
840 pay_core_utils.push_token('NAME_OR_NUMBER',lv_paternal_last_name) ;
841 pay_core_utils.push_token('DESCRIPTION','Reduced working week flag is Missing ') ;
842
843 end if;
844
845 if lv_hire_date is null then
846 lv_error_mesg := lv_error_mesg || 'Hire date is Missing ' ;
847 pay_core_utils.push_message(800,'HR_MX_INVALID_DISPMAG_DATA','F');
848 pay_core_utils.push_token('RECORD_NAME','DISPMAG') ;
849 pay_core_utils.push_token('NAME_OR_NUMBER',lv_paternal_last_name) ;
850 pay_core_utils.push_token('DESCRIPTION','Hire date is Missing ') ;
851
852 end if;
853
854 if lv_imss_waybill is null then
855 lv_error_mesg := lv_error_mesg || ' IMSS Waybill Number is Missing ' ;
856 pay_core_utils.push_message(800,'HR_MX_INVALID_DISPMAG_DATA','F');
857 pay_core_utils.push_token('RECORD_NAME','DISPMAG') ;
858 pay_core_utils.push_token('NAME_OR_NUMBER',lv_paternal_last_name) ;
859 pay_core_utils.push_token('DESCRIPTION',' IMSS Waybill Number is Missing ') ;
860
861 end if;
862
863 end if;
864 close c_hire_details ;
865
866 elsif p_affl_type ='SEPARATIONS' then -- Separations
867
868 open c_sep_details(p_assignment_action_id) ;
869
870 fetch c_sep_details into
871 lv_er_ss_id,
872 lv_er_ss_chk_digit,
873 lv_ee_ss_id,
874 lv_ee_ss_chk_digit,
875 lv_paternal_last_name,
876 lv_name,
877 lv_sep_date,
878 lv_imss_waybill,
879 lv_leav_reason,
880 lv_flat_out, lv_csvr_out ;
881
882 if lv_er_ss_id is null or
883 lv_er_ss_chk_digit is null or
884 lv_ee_ss_id is null or
885 lv_ee_ss_chk_digit is null or
886 lv_name is null or
887 lv_sep_date is null or
888 lv_imss_waybill is null or
889 lv_leav_reason is null then
890
891 lv_error_flag := 'Y' ;
892 lv_error_mesg := 'Error in DISPMAG record for Employee '||lv_paternal_last_name || ' - ' ;
893
894 if lv_er_ss_id is null then
895 lv_error_mesg := lv_error_mesg || 'Employer SS ID is Missing ' ;
896 pay_core_utils.push_message(800,'HR_MX_INVALID_DISPMAG_DATA','F');
897 pay_core_utils.push_token('RECORD_NAME','DISPMAG') ;
898 pay_core_utils.push_token('NAME_OR_NUMBER',lv_paternal_last_name) ;
899 pay_core_utils.push_token('DESCRIPTION','Employer SS ID is Missing ') ;
900
901 end if;
902
903 if lv_er_ss_chk_digit is null then
904 lv_error_mesg := lv_error_mesg || 'Employer SS Check Digit is Missing ' ;
905 pay_core_utils.push_message(800,'HR_MX_INVALID_DISPMAG_DATA','F');
906 pay_core_utils.push_token('RECORD_NAME','DISPMAG') ;
907 pay_core_utils.push_token('NAME_OR_NUMBER',lv_paternal_last_name) ;
908 pay_core_utils.push_token('DESCRIPTION','Employer SS Check Digit is Missing') ;
909
910 end if;
911
912 if lv_ee_ss_id is null then
913 lv_error_mesg := lv_error_mesg || 'Employee SS ID is Missing ' ;
914 pay_core_utils.push_message(800,'HR_MX_INVALID_DISPMAG_DATA','F');
915 pay_core_utils.push_token('RECORD_NAME','DISPMAG') ;
916 pay_core_utils.push_token('NAME_OR_NUMBER',lv_paternal_last_name) ;
917 pay_core_utils.push_token('DESCRIPTION','Employee SS ID is Missing ') ;
918
919 end if;
920
921 if lv_ee_ss_chk_digit is null then
922 lv_error_mesg := lv_error_mesg || 'Employee SS Check Digit is Missing ' ;
923 pay_core_utils.push_message(800,'HR_MX_INVALID_DISPMAG_DATA','F');
924 pay_core_utils.push_token('RECORD_NAME','DISPMAG') ;
925 pay_core_utils.push_token('NAME_OR_NUMBER',lv_paternal_last_name) ;
926 pay_core_utils.push_token('DESCRIPTION','Employee SS Check Digit is Missing ') ;
927
928 end if;
929
930 if lv_name is null then
931 lv_error_mesg := lv_error_mesg || 'First Name and/or Second Name is Missing ' ;
932 pay_core_utils.push_message(800,'HR_MX_INVALID_DISPMAG_DATA','F');
933 pay_core_utils.push_token('RECORD_NAME','DISPMAG') ;
934 pay_core_utils.push_token('NAME_OR_NUMBER',lv_paternal_last_name) ;
935 pay_core_utils.push_token('DESCRIPTION','First Name and/or Second Name is Missing') ;
936
937 end if;
938
939 if lv_sep_date is null then
940 lv_error_mesg := lv_error_mesg || 'Separation date is Missing ' ;
941 pay_core_utils.push_message(800,'HR_MX_INVALID_DISPMAG_DATA','F');
942 pay_core_utils.push_token('RECORD_NAME','DISPMAG') ;
943 pay_core_utils.push_token('NAME_OR_NUMBER',lv_paternal_last_name) ;
944 pay_core_utils.push_token('DESCRIPTION','Separation date is Missing ') ;
945
946 end if;
947
948 if lv_imss_waybill is null then
949 lv_error_mesg := lv_error_mesg || ' IMSS Waybill Number is Missing ' ;
950 pay_core_utils.push_message(800,'HR_MX_INVALID_DISPMAG_DATA','F');
951 pay_core_utils.push_token('RECORD_NAME','DISPMAG') ;
952 pay_core_utils.push_token('NAME_OR_NUMBER',lv_paternal_last_name) ;
953 pay_core_utils.push_token('DESCRIPTION','IMSS Waybill Number is Missing') ;
954
955 end if;
956
957 if lv_leav_reason is null then
958 lv_error_mesg := lv_error_mesg || ' Leaving Reason is Missing ' ;
959 pay_core_utils.push_message(800,'HR_MX_INVALID_DISPMAG_DATA','F');
960 pay_core_utils.push_token('RECORD_NAME','DISPMAG') ;
961 pay_core_utils.push_token('NAME_OR_NUMBER',lv_paternal_last_name) ;
962 pay_core_utils.push_token('DESCRIPTION',' Leaving Reason is Missing') ;
963 -- pay_core_utils.push_token('DESCRIPTION','(Assignment Action Id :'|| to_char(p_assignment_action_id)
964 -- ||') Leaving Reason is Missing') ;
965 end if;
966
967 end if;
968
969 close c_sep_details ;
970
971
972 elsif p_affl_type ='SALARY' then -- Salary
973
974 open c_salary_details(p_assignment_action_id) ;
975 fetch c_salary_details into
976 lv_er_ss_id,
977 lv_er_ss_chk_digit,
978 lv_ee_ss_id,
979 lv_ee_ss_chk_digit,
980 lv_paternal_last_name,
981 lv_name,
982 lv_IDW,
983 lv_worker_type,
984 lv_salary_type,
985 lv_rww,
986 lv_hire_date,
987 lv_imss_waybill,
988 lv_flat_out, lv_csvr_out ;
989
990 if lv_er_ss_id is null or
991 lv_er_ss_chk_digit is null or
992 lv_ee_ss_id is null or
993 lv_ee_ss_chk_digit is null or
994 lv_name is null or
995 (to_number(nvl(lv_idw,'0')) <= 0 ) or
996 lv_worker_type is null or
997 lv_salary_type is null or
998 lv_rww is null or
999 lv_hire_date is null or
1000 lv_imss_waybill is null then
1001
1002 lv_error_flag := 'Y' ;
1003 lv_error_mesg := 'Error in DISPMAG record for Employee '||lv_paternal_last_name || ' - ' ;
1004
1005 if lv_er_ss_id is null then
1006 lv_error_mesg := lv_error_mesg || 'Employer SS ID is Missing ' ;
1007 pay_core_utils.push_message(800,'HR_MX_INVALID_DISPMAG_DATA','F');
1008 pay_core_utils.push_token('RECORD_NAME','DISPMAG') ;
1009 pay_core_utils.push_token('NAME_OR_NUMBER',lv_paternal_last_name) ;
1010 pay_core_utils.push_token('DESCRIPTION','Employer SS ID is Missing') ;
1011 end if;
1012
1013 if lv_er_ss_chk_digit is null then
1014 lv_error_mesg := lv_error_mesg || 'Employer SS Check Digit is Missing ' ;
1015 pay_core_utils.push_message(800,'HR_MX_INVALID_DISPMAG_DATA','F');
1016 pay_core_utils.push_token('RECORD_NAME','DISPMAG') ;
1017 pay_core_utils.push_token('NAME_OR_NUMBER',lv_paternal_last_name) ;
1018 pay_core_utils.push_token('DESCRIPTION','Employer SS Check Digit is Missing') ;
1019 end if;
1020
1021 if lv_ee_ss_id is null then
1022 lv_error_mesg := lv_error_mesg || 'Employee SS ID is Missing ' ;
1023 pay_core_utils.push_message(800,'HR_MX_INVALID_DISPMAG_DATA','F');
1024 pay_core_utils.push_token('RECORD_NAME','DISPMAG') ;
1025 pay_core_utils.push_token('NAME_OR_NUMBER',lv_paternal_last_name) ;
1026 pay_core_utils.push_token('DESCRIPTION','Employee SS ID is Missing ') ;
1027
1028 end if;
1029
1030 if lv_ee_ss_chk_digit is null then
1031 lv_error_mesg := lv_error_mesg || 'Employee SS Check Digit is Missing ' ;
1032 pay_core_utils.push_message(800,'HR_MX_INVALID_DISPMAG_DATA','F');
1033 pay_core_utils.push_token('RECORD_NAME','DISPMAG') ;
1034 pay_core_utils.push_token('NAME_OR_NUMBER',lv_paternal_last_name) ;
1035 pay_core_utils.push_token('DESCRIPTION','Employee SS Check Digit is Missing') ;
1036
1037 end if;
1038
1039
1040 if lv_name is null then
1041 lv_error_mesg := lv_error_mesg || 'First Name and/or Second Name is Missing ' ;
1042 pay_core_utils.push_message(800,'HR_MX_INVALID_DISPMAG_DATA','F');
1043 pay_core_utils.push_token('RECORD_NAME','DISPMAG') ;
1044 pay_core_utils.push_token('NAME_OR_NUMBER',lv_paternal_last_name) ;
1045 pay_core_utils.push_token('DESCRIPTION','First Name and/or Second Name is Missing ') ;
1046
1047 end if;
1048
1049
1050 if to_number(nvl(lv_IDW,'0')) <= 0 then
1051 lv_error_mesg := lv_error_mesg || 'IDW must be greater than zero ' ;
1052 pay_core_utils.push_message(800,'HR_MX_INVALID_DISPMAG_DATA','F');
1053 pay_core_utils.push_token('RECORD_NAME','DISPMAG') ;
1054 pay_core_utils.push_token('NAME_OR_NUMBER',lv_paternal_last_name) ;
1055 pay_core_utils.push_token('DESCRIPTION','IDW must be greater than zero ') ;
1056 end if;
1057
1058 if lv_worker_type is null then
1059 lv_error_mesg := lv_error_mesg || 'Worker Type is Missing ' ;
1060 pay_core_utils.push_message(800,'HR_MX_INVALID_DISPMAG_DATA','F');
1061 pay_core_utils.push_token('RECORD_NAME','DISPMAG') ;
1062 pay_core_utils.push_token('NAME_OR_NUMBER',lv_paternal_last_name) ;
1063 pay_core_utils.push_token('DESCRIPTION','Worker Type is Missing ') ;
1064
1065 end if;
1066
1067
1068 if lv_salary_type is null then
1069 lv_error_mesg := lv_error_mesg || 'Salary Type is Missing ' ;
1070 pay_core_utils.push_message(800,'HR_MX_INVALID_DISPMAG_DATA','F');
1071 pay_core_utils.push_token('RECORD_NAME','DISPMAG') ;
1072 pay_core_utils.push_token('NAME_OR_NUMBER',lv_paternal_last_name) ;
1073 pay_core_utils.push_token('DESCRIPTION','Salary Type is Missing ') ;
1074
1075 end if;
1076
1077 if lv_rww is null then
1078 lv_error_mesg := lv_error_mesg || 'Reduced working week flag is Missing ' ;
1079 pay_core_utils.push_message(800,'HR_MX_INVALID_DISPMAG_DATA','F');
1080 pay_core_utils.push_token('RECORD_NAME','DISPMAG') ;
1081 pay_core_utils.push_token('NAME_OR_NUMBER',lv_paternal_last_name) ;
1082 pay_core_utils.push_token('DESCRIPTION','Reduced working week flag is Missing ') ;
1083
1084 end if;
1085
1086 if lv_hire_date is null then
1087 lv_error_mesg := lv_error_mesg || 'Hire date is Missing ' ;
1088 pay_core_utils.push_message(800,'HR_MX_INVALID_DISPMAG_DATA','F');
1089 pay_core_utils.push_token('RECORD_NAME','DISPMAG') ;
1090 pay_core_utils.push_token('NAME_OR_NUMBER',lv_paternal_last_name) ;
1091 pay_core_utils.push_token('DESCRIPTION','Hire date is Missing ') ;
1092
1093 end if;
1094
1095 if lv_imss_waybill is null then
1096 lv_error_mesg := lv_error_mesg || ' IMSS Waybill Number is Missing ' ;
1097 pay_core_utils.push_message(800,'HR_MX_INVALID_DISPMAG_DATA','F');
1098 pay_core_utils.push_token('RECORD_NAME','DISPMAG') ;
1099 pay_core_utils.push_token('NAME_OR_NUMBER',lv_paternal_last_name) ;
1100 pay_core_utils.push_token('DESCRIPTION',' IMSS Waybill Number is Missing ') ;
1101
1102 end if;
1103
1104 end if;
1105 close c_salary_details ;
1106
1107 end if; --
1108
1109 p_error_flag := lv_error_flag ;
1110 p_error_mesg := lv_error_mesg ;
1111 p_flat_out := lv_flat_out ;
1112 p_csvr_out := lv_csvr_out ;
1113 p_flat_ret_str_len := length(lv_flat_out) ;
1114 p_csvr_ret_str_len := length(lv_csvr_out) ;
1115
1116 return lv_return_value ;
1117
1118 END format_dispmag_emp_record ;
1119
1120 /************************************************************
1121 Name : format_dispmag_total_record
1122 Purpose : This function formats and returns the dispmag
1123 total record.
1124 This function is called from the DISPMAG_SUBMITTER_TOTAL
1125 fast formula by passing the parameters
1126 Arguments : p_trans_gre - Transmitter Gre Id parameter
1127 p_gre_id - GRE Id parameter
1128 p_total_emps - Total no of Employees parameter
1129 accumulated and passed from
1130 DISPMAG_EMPLOYEE fastformula
1131 p_flat_out - Total record output in
1132 magnetic format
1133 p_csvr_out - Total record output in
1134 csv format
1135 p_flat_ret_str_len - string length of flat record out
1136 p_csvr_ret_str_len - string length of csv record out
1137
1138 **************************************************************/
1139 FUNCTION format_dispmag_total_record(
1140 p_trans_gre in number,
1141 p_gre_id in number,
1142 p_total_emps in number,
1143 p_flat_out out nocopy varchar2,
1144 p_csvr_out out nocopy varchar2,
1145 p_flat_ret_str_len out nocopy number,
1146 p_csvr_ret_str_len out nocopy number
1147 ) RETURN VARCHAR2
1148 IS
1149
1150 -- Cursor to get the IMSS Waybill Number
1151 cursor c_get_org_information ( cp_organization_id in number)
1152 is
1153 select org_information5
1154 from hr_organization_information
1155 where org_information_context= 'MX_SOC_SEC_DETAILS'
1156 and organization_id = cp_organization_id ;
1157
1158
1159 lv_return_value varchar2(100);
1160 lv_total_f1 varchar2(100);
1161 lv_total_f2 varchar2(100);
1162 lv_total_f3 varchar2(100);
1163 lv_total_f4 varchar2(100);
1164 lv_total_f5 varchar2(100);
1165 lv_total_f6 varchar2(100);
1166 lv_total_f7 varchar2(1);
1167 lv_imss_waybill varchar2(5);
1168 lv_flat_out varchar2(300);
1169 lv_csvr_out varchar2(300);
1170
1171 BEGIN
1172
1173 lv_return_value := '';
1174 lv_imss_waybill := ' ' ;
1175
1176 -- Get way bill number for the Transmitter GRE
1177
1178 open c_get_org_information(p_trans_gre) ;
1179 fetch c_get_org_information into lv_imss_waybill ;
1180 close c_get_org_information ;
1181
1182 lv_total_f1 := '*************' ;
1183 lv_total_f2 := rpad(' ',43,' ');
1184 lv_total_f3 := lpad(to_char(p_total_emps),6,'0');
1185 lv_total_f4 := rpad(' ',71,' ');
1186 lv_total_f5 := rpad(nvl(lv_imss_waybill,' '),5,' ');
1187 lv_total_f6 := rpad(' ',29,' ');
1188 lv_total_f7 := '9';
1189
1190 lv_flat_out := lv_total_f1 || -- Asterisks
1191 lv_total_f2 || -- Filler
1192 lv_total_f3 || -- Total Number of Employees HIRE/SEPARATIONS
1193 lv_total_f4 || -- Filler
1194 lv_total_f5 || -- IMSS Waybill
1195 lv_total_f6 || -- Filler
1196 lv_total_f7 ; -- Layout Identifier
1197
1198 lv_csvr_out := format_data_string(lv_total_f1) ||
1199 format_data_string(lv_total_f2) ||
1200 format_data_string(lv_total_f3) ||
1201 format_data_string(lv_total_f4) ||
1202 format_data_string(lv_total_f5) ||
1203 format_data_string(lv_total_f6) ||
1204 format_data_string(lv_total_f7) ;
1205
1206 p_flat_out := lv_flat_out ;
1207 p_csvr_out := lv_csvr_out ;
1208 p_flat_ret_str_len := length(lv_flat_out) ;
1209 p_csvr_ret_str_len := length(lv_csvr_out) ;
1210
1211 return lv_return_value ;
1212
1213 END format_dispmag_total_record ;
1214
1215
1216 --begin
1217 --hr_utility.trace_on (null, 'SSDISPMAG');
1218
1219 end per_mx_ssaffl_dispmag;