[Home] [Help]
Skip to content
PACKAGE BODY: APPS.PAY_MX_SUBSIDY_EMP_PD_RPT
Source
1 PACKAGE BODY PAY_MX_SUBSIDY_EMP_PD_RPT AS
2 /* $Header: paymxsubemplpaid.pkb 120.0.12010000.3 2009/06/01 11:35:16 sivanara noship $ */
3 /* +======================================================================+
4 | Copyright (c) 2003 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +======================================================================+
8 Package Name : PAY_MX_SUBSIDY_EMP_PD_RPT
9 Package File Name : paymxsubemprpt.pkb
10
11 Description : Used for ISR Subsidy For Employment Paid Report
12
13 Change List:
14 ------------
15
16 Name Date Version Bug Text
17 ------------- ----------- ------- ------- ----------------------------------
18 sivanara 07-JAN-2008 115.0 Initial Version
19 sivanara 06-Feb-2008 115.1 8231096 Changed generate xml RFC id from
20 ER to employee's.
21 sivanara 09-Feb-2009 115.2 8240711 Multiple rows will be showed for
22 re-hired employee.
23 sivanara 10-Feb-2009 115.4 8248748 Modified generate_xml procedure
24 to filter re-hire employee from
25 other LE.
26 sivanara 17-Feb-2009 115.6 8240711 To cursor c_subemppaid_rec added
27 assignment_action_id filter
28 condition for re-hire issue.
29 sivanara 01-Jun-2009 115.7 8554088 Added code to generate_xml_header
30 to populate global variable value
31 even in multi-thread mode.
32 ==========================================================================*/
33
34 --
35 -- Global Variables
36 --
37
38
39 subemppaid_xml_tbl xml_tbl;
40
41 g_proc_name VARCHAR2(240);
42 g_debug BOOLEAN;
43 g_document_type VARCHAR2(50);
44 gd_effective_date DATE;
45 gd_start_date DATE;
46 gd_end_date DATE;
47 gn_business_group_id NUMBER;
48 gn_legal_er_id NUMBER;
49 gv_sort_opt VARCHAR2(4);
50 gv_assignment_set VARCHAR2(100);
51 gv_ass_set_id NUMBER;
52 gv_rpting_year VARCHAR2(4);
53 gn_success_fail NUMBER;
54 gn_sep_bal NUMBER;
55 gn_ass_bal NUMBER;
56 gn_emp_bal NUMBER;
57
58 EOL VARCHAR2(5);
59
60 /****************************************************************************
61 Name : HR_UTILITY_TRACE
62 Description : This procedure prints debug messages.
63 *****************************************************************************/
64 PROCEDURE hr_utility_trace ( P_TRC_DATA VARCHAR2) AS
65 BEGIN
66 IF g_debug THEN
67 hr_utility.trace(p_trc_data);
68 END IF;
69 END hr_utility_trace;
70
71
72 /****************************************************************************
73 Name : PRINT_BLOB
74 Description : This procedure prints contents of BLOB passed as parameter.
75 *****************************************************************************/
76
77 PROCEDURE print_blob(p_blob BLOB) IS
78 BEGIN
79 IF g_debug THEN
80 pay_ac_utility.print_lob(p_blob);
81 END IF;
82 END print_blob;
83
84
85 /****************************************************************************
86 Name : WRITE_TO_MAGTAPE_LOB
87 Description : This procedure appends passed BLOB parameter to
88 pay_mag_tape.g_blob_value
89 *****************************************************************************/
90
91 PROCEDURE write_to_magtape_lob(p_blob BLOB) IS
92 BEGIN
93 IF dbms_lob.getLength (p_blob) IS NOT NULL THEN
94 pay_core_files.write_to_magtape_lob (p_blob);
95 END IF;
96 END write_to_magtape_lob;
97
98
99 /****************************************************************************
100 Name : WRITE_TO_MAGTAPE_LOB
101 Description : This procedure appends passed varchar2 parameter to
102 pay_mag_tape.g_blob_value
103 *****************************************************************************/
104
105 PROCEDURE write_to_magtape_lob(p_data VARCHAR2) IS
106 BEGIN
107 pay_core_files.write_to_magtape_lob (p_data);
108 END write_to_magtape_lob;
109
110
111 /****************************************************************************
112 Name : POPULATE_XML_TABLE
113 Description : This procedure creates a table that uses for XML creation.
114 *****************************************************************************/
115 PROCEDURE populate_xml_table( name IN VARCHAR2
116 ,value IN VARCHAR2
117 ,type IN VARCHAR2 ) IS
118 ln_index NUMBER;
119
120 BEGIN
121
122 ln_index := subemppaid_xml_tbl.COUNT;
123
124 subemppaid_xml_tbl(ln_index).name := name;
125 subemppaid_xml_tbl(ln_index).value := value;
126
127 END populate_xml_table;
128
129 /****************************************************************************
130 Name : LOAD_XML_INTERNAL
131 Description : This procedure loads the global XML cache.
132 Parameters : P_NODE_TYPE This parameter can take one of these
133 values: -
134 1. CS - This signifies that string contained
135 in P_NODE parameter is start of
136 container node. P_DATA parameter is
137 ignored in this mode.
138 2. CE - This signifies that string
139 contained in P_NODE parameter is
140 end of container node. P_DATA
141 parameter is ignored in this mode.
142 3. D - This signifies that string
143 contained in P_NODE parameter is
144 data node and P_DATA carries actual
145 data to be contained by tag
146 specified by P_NODE parameter.
147
148 P_NODE Name of XML tag, or, application column
149 name of flex segment.
150
151 P_DATA Data to be contained by tag specified by
152 P_NODE parameter. P_DATA is not used unless
153 P_NODE_TYPE = D.
154 *****************************************************************************/
155 PROCEDURE load_xml_internal ( p_node_type VARCHAR2
156 ,p_node VARCHAR2
157 ,p_data VARCHAR2) IS
158 l_proc_name VARCHAR2(100);
159 l_data VARCHAR2(240);
160 l_xml VARCHAR2(240);
161
162 BEGIN
163 l_proc_name := g_proc_name || 'LOAD_XML_INTERNAL';
164 hr_utility_trace ('Entering '||l_proc_name);
165
166 IF p_node_type = 'CS' THEN
167
168 l_xml := '<'||p_node||'>'||EOL;
169
170 ELSIF p_node_type = 'CE' THEN
171
172 l_xml := '</'||p_node||'>'||EOL;
173
174 ELSIF p_node_type = 'D' THEN
175
176 /* Handle special charaters in data */
177 l_data := REPLACE (p_data, '&', '&');
178 l_data := REPLACE (l_data, '>', '>');
179 l_data := REPLACE (l_data, '<', '<');
180 l_data := REPLACE (l_data, '''', ''');
181 l_data := REPLACE (l_data, '"', '"');
182 l_xml := '<'||p_node||'>'||l_data||'</'||p_node||'>'||EOL;
183
184 END IF;
185
186 write_to_magtape_lob (l_xml);
187
188 hr_utility_trace ('Leaving '||l_proc_name);
189
190 END load_xml_internal;
191
192 /****************************************************************************
193 Name : GET_PAYROLL_ACTION_INFO
194 Description : This procedure fetches payroll action level information.
195 *****************************************************************************/
196 PROCEDURE get_payroll_action_info(p_payroll_action_id IN NUMBER
197 ,p_end_date OUT NOCOPY DATE
198 ,p_business_group_id OUT NOCOPY NUMBER
199 ,p_legal_employer_id OUT NOCOPY NUMBER
200 ,p_sort_opt OUT NOCOPY VARCHAR2
201 )
202 IS
203 CURSOR c_payroll_Action_info (cp_payroll_action_id IN NUMBER) IS
204 SELECT effective_date,
205 business_group_id,
206 pay_mx_utility.get_legi_param_val( 'LEGAL_EMPLOYER'
207 ,legislative_parameters),
208 pay_mx_utility.get_legi_param_val( 'SORT_OPT'
209 ,legislative_parameters) ,
210 pay_mx_utility.get_legi_param_val( 'ASS_SET'
211 ,legislative_parameters)
212 , pay_mx_utility.get_legi_param_val( 'RPT_YEAR'
213 ,legislative_parameters)
214 , pay_mx_utility.get_legi_param_val( 'START_DATE'
215 ,legislative_parameters)
216 ,pay_mx_utility.get_legi_param_val( 'END_DATE'
217 ,legislative_parameters)
218
219 FROM pay_payroll_actions
220 WHERE payroll_action_id = cp_payroll_action_id;
221
222
223 CURSOR c_ass_set_name (cp_ass_set_id NUMBER) IS
224 SELECT assignment_set_name
225 FROM hr_assignment_sets
226 WHERE assignment_set_id = cp_ass_set_id;
227
228 ld_end_date DATE;
229 ln_business_group_id NUMBER;
230 ln_asg_set_id NUMBER;
231 ln_legal_er_id NUMBER;
232 lv_sort_opt VARCHAR2(4) ;
233 lv_procedure_name VARCHAR2(100);
234 lv_assignment_set VARCHAR2(100);
235 lv_rpting_year VARCHAR2(100);
236 lv_error_message VARCHAR2(200);
237 ln_step NUMBER;
238 ld_rpt_start_dt VARCHAR2(30) ;
239 ld_rpt_end_dt VARCHAR2(30);
240
241 BEGIN
242
243 lv_procedure_name := g_proc_name ||'.get_payroll_action_info';
244
245 hr_utility.set_location(lv_procedure_name, 10);
246
247 ln_step := 1;
248
249 OPEN c_payroll_action_info(p_payroll_action_id);
250 FETCH c_payroll_action_info INTO ld_end_date
251 ,ln_business_group_id
252 ,ln_legal_er_id
253 ,lv_sort_opt
254 ,lv_assignment_set
255 ,lv_rpting_year
256 ,ld_rpt_start_dt
257 ,ld_rpt_end_dt;
258 CLOSE c_payroll_action_info;
259
260
261 ln_step := 2;
262 hr_utility.set_location(lv_procedure_name, 30);
263 hr_utility_trace ('ld_rpt_start_dt = '|| ld_rpt_start_dt);
264 hr_utility_trace ('ld_rpt_end_dt = '|| ld_rpt_end_dt);
265
266 IF lv_assignment_set IS NOT NULL THEN
267 OPEN c_ass_set_name(lv_assignment_set);
268 FETCH c_ass_set_name INTO gv_assignment_set;
269 CLOSE c_ass_set_name;
270 END IF;
271 -- Set the global variable for cp parameters value
272 gv_ass_set_id := lv_assignment_set;
273 gd_start_date := fnd_date.canonical_to_date(ld_rpt_start_dt);
274 gd_end_date := fnd_date.canonical_to_date(ld_rpt_end_dt);
275 gv_rpting_year := lv_rpting_year;
276 hr_utility_trace ('gd_start_date = '|| gd_start_date);
277 hr_utility_trace ('gd_end_date = '|| gd_end_date);
278 p_end_date := TRUNC(ld_end_date,'Y');
279 p_business_group_id := ln_business_group_id;
280 p_legal_employer_id := ln_legal_er_id;
281 p_sort_opt := lv_sort_opt;
282
283 hr_utility.set_location(lv_procedure_name, 50);
284
285 EXCEPTION
286 WHEN OTHERS THEN
287 lv_error_message := 'Error at step ' || ln_step || ' IN ' ||
288 lv_procedure_name;
289
290 hr_utility.trace(lv_error_message || '-' || SQLERRM);
291
292 lv_error_message :=
293 pay_emp_action_arch.set_error_message(lv_error_message);
294
295 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
296 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
297 hr_utility.raise_error;
298
299 END get_payroll_action_info;
300
301 /****************************************************************************
302 Name : RANGE_CURSOR
303 Description : This procedure prepares range of persons to be processed.
304 *****************************************************************************/
305 PROCEDURE range_cursor ( P_PAYROLL_ACTION_ID NUMBER
306 ,P_SQLSTR OUT NOCOPY VARCHAR2 ) AS
307
308 l_proc_name varchar2(100);
309
310 BEGIN
311 l_proc_name := g_proc_name || 'RANGE_CURSOR';
312
313 hr_utility_trace ('Entering '||l_proc_name);
314
315 hr_utility_trace ('P_PAYROLL_ACTION_ID = '|| p_payroll_action_id);
316
317 get_payroll_action_info (p_payroll_action_id
318 ,gd_effective_date
319 ,gn_business_group_id
320 ,gn_legal_er_id
321 ,gv_sort_opt);
322
323
324 hr_utility_trace ('gd_effective_date = '|| gd_effective_date);
325 hr_utility_trace ('gn_business_group_id = '|| gn_business_group_id);
326 hr_utility_trace ('gn_legal_er_id = '|| gn_legal_er_id);
327 hr_utility_trace ('gv_sort_opt = '|| gv_sort_opt);
328
329 p_sqlstr := '
330 SELECT DISTINCT paa_arch.serial_number
331 FROM pay_assignment_actions paa_arch
332 ,pay_payroll_actions ppa_arch
333 WHERE ppa_arch.business_group_id = '|| gn_business_group_id ||'
334 AND ppa_arch.report_type = ''MX_YREND_ARCHIVE''
335 AND ppa_arch.report_qualifier = ''MX''
336 AND ppa_arch.report_category = ''ARCHIVE''
337 AND pay_mx_utility.get_parameter(''TRANSFER_LEGAL_EMPLOYER'',
338 ppa_arch.legislative_parameters) = '||gn_legal_er_id||'
339 AND TRUNC(ppa_arch.effective_date,''Y'') =
340 fnd_date.canonical_to_date('''||
341 fnd_date.date_to_canonical(gd_effective_date)||''')
342 AND paa_arch.payroll_action_id = ppa_arch.payroll_action_id
343 AND paa_arch.action_status = ''C''
344 AND :p_payroll_action_id = '||p_payroll_action_id||'
345 ORDER BY serial_number';
346
347 hr_utility_trace ('Range cursor query : ' || p_sqlstr);
348 hr_utility_trace ('Leaving '||l_proc_name);
349
350 END range_cursor;
351
352
353 /****************************************************************************
354 Name : ACTION_CREATION
355 Description : This procedure creates assignment actions for
356 ISR Subsidy For Employment Paid Report process.
357 *****************************************************************************/
358 PROCEDURE action_creation ( p_payroll_action_id NUMBER,
359 p_start_person_id NUMBER,
360 p_end_person_id NUMBER,
361 p_chunk NUMBER) AS
362
363 CURSOR c_arch_asg ( cp_business_group_id NUMBER
364 ,cp_legal_er_id NUMBER
365 ,cp_effective_date DATE
366 ,cp_start_person_id NUMBER
367 ,cp_end_person_id NUMBER) IS
368 SELECT paa_arch.assignment_action_id
369 ,paa_arch.assignment_id
370 ,paa_arch.serial_number person_id
371 ,ppa_arch.payroll_action_id
372 FROM pay_assignment_actions paa_arch,
373 pay_payroll_actions ppa_arch
374 WHERE ppa_arch.business_group_id = cp_business_group_id
375 AND ppa_arch.report_type = 'MX_YREND_ARCHIVE'
376 AND ppa_arch.report_qualifier = 'MX'
377 AND ppa_arch.report_category = 'ARCHIVE'
378 AND pay_mx_utility.get_parameter('TRANSFER_LEGAL_EMPLOYER',
379 ppa_arch.legislative_parameters) = cp_legal_er_id
380 AND TRUNC(ppa_arch.effective_date,'Y') = TRUNC(cp_effective_date,'Y')
381 AND paa_arch.payroll_action_id = ppa_arch.payroll_action_id
382 AND paa_arch.action_status = 'C'
383 AND paa_arch.serial_number BETWEEN cp_start_person_id
384 AND cp_end_person_id
385 AND (gv_ass_set_id IS NULL OR (exists ( select 1 /* Selected Assignment Set */
386 from hr_assignment_set_amendments hasa
387 where hasa.assignment_set_id = gv_ass_set_id
388 and hasa.assignment_id = paa_arch.assignment_id
389 and upper(hasa.include_or_exclude) = 'I')))
390 ORDER BY paa_arch.serial_number,
391 paa_arch.assignment_action_id desc;
392
393 CURSOR c_arch_asg_range ( cp_business_group_id NUMBER
394 ,cp_legal_er_id NUMBER
395 ,cp_effective_date DATE
396 ,cp_chunk NUMBER
397 ,cp_payroll_action_id NUMBER) IS
398 SELECT paa_arch.assignment_action_id
399 ,paa_arch.assignment_id
400 ,paa_arch.serial_number person_id
401 ,ppa_arch.payroll_action_id
402 FROM pay_assignment_actions paa_arch,
403 pay_payroll_actions ppa_arch,
404 pay_population_ranges ppr
405 WHERE ppa_arch.business_group_id = cp_business_group_id
406 AND ppa_arch.report_type = 'MX_YREND_ARCHIVE'
407 AND ppa_arch.report_qualifier = 'MX'
408 AND ppa_arch.report_category = 'ARCHIVE'
409 AND pay_mx_utility.get_parameter('TRANSFER_LEGAL_EMPLOYER',
410 ppa_arch.legislative_parameters) = cp_legal_er_id
411 AND TRUNC(ppa_arch.effective_date,'Y') = TRUNC(cp_effective_date,'Y')
412 AND paa_arch.payroll_action_id = ppa_arch.payroll_action_id
413 AND paa_arch.action_status = 'C'
414 AND paa_arch.serial_number = ppr.person_id
415 AND ppr.chunk_number = cp_chunk
416 AND ppr.payroll_action_id = cp_payroll_action_id
417 AND (gv_ass_set_id IS NULL OR (exists ( select 1 /* Selected Assignment Set */
418 from hr_assignment_set_amendments hasa
419 where hasa.assignment_set_id = gv_ass_set_id
420 and hasa.assignment_id = paa_arch.assignment_id
421 and upper(hasa.include_or_exclude) = 'I')))
422 ORDER BY paa_arch.serial_number,
423 paa_arch.assignment_action_id desc;
424
425 l_proc_name varchar2(100);
426 lv_future_magtape_exists varchar2(1);
427 lb_range_person_on boolean;
428 ln_person_id number;
429 ln_prev_arch_pact_id number;
430 ln_arch_pact_id number;
431 ln_prev_person_id number;
432 ln_prev_asg_id number;
433 ln_mag_asg_act_id number;
434 ln_assignment_id number;
435 ln_arch_act_id number;
436 ln_asg_count number;
437 BEGIN
438 l_proc_name := g_proc_name || 'ACTION_CREATION';
439
440 hr_utility_trace ('Entering '||l_proc_name);
441 hr_utility_trace ('Parameters ....');
442 hr_utility_trace ('P_PAYROLL_ACTION_ID = '|| P_PAYROLL_ACTION_ID);
443 hr_utility_trace ('P_START_PERSON_ID = '|| P_START_PERSON_ID);
444 hr_utility_trace ('P_END_PERSON_ID = '|| P_END_PERSON_ID);
445 hr_utility_trace ('P_CHUNK = '|| P_CHUNK);
446
447 ln_prev_person_id := -1;
448 ln_prev_asg_id := -1;
449 ln_prev_arch_pact_id := -1;
450
451 IF gn_legal_er_id IS NULL THEN
452
453 get_payroll_action_info (p_payroll_action_id
454 ,gd_effective_date
455 ,gn_business_group_id
456 ,gn_legal_er_id
457 ,gv_sort_opt);
458
459 END IF;
460
461 ln_asg_count := 0;
462
463 lb_range_person_on := pay_ac_utility.range_person_on(
464 p_report_type => 'ISR_SUBSIDY_EMP'
465 ,p_report_format => 'ISR_SUBSIDY_EMP'
466 ,p_report_qualifier => 'ISR_SUBSIDY_EMP'
467 ,p_report_category => 'RT');
468
469 IF lb_range_person_on THEN
470
471 hr_utility_trace ('Person ranges are ON');
472
473 OPEN c_arch_asg_range( gn_business_group_id
474 ,gn_legal_er_id
475 ,gd_effective_date
476 ,p_chunk
477 ,p_payroll_action_id);
478
479 ELSE
480
481 hr_utility_trace ('Person ranges are OFF');
482
483 OPEN c_arch_asg( gn_business_group_id
484 ,gn_legal_er_id
485 ,gd_effective_date
486 ,p_start_person_id
487 ,p_end_person_id);
488
489 END IF;
490
491 LOOP
492 IF lb_range_person_on THEN
493 FETCH c_arch_asg_range INTO ln_arch_act_id,
494 ln_assignment_id,
495 ln_person_id,
496 ln_arch_pact_id;
497 EXIT WHEN c_arch_asg_range%NOTFOUND;
498 ELSE
499 FETCH c_arch_asg INTO ln_arch_act_id,
500 ln_assignment_id,
501 ln_person_id,
502 ln_arch_pact_id;
503 EXIT WHEN c_arch_asg%NOTFOUND;
504 END IF;
505
506 ln_asg_count := ln_asg_count + 1;
507
508 hr_utility_trace ('-------------');
509 hr_utility_trace('Current archiver asg action = '||ln_arch_act_id);
510 hr_utility_trace('Current person = '||ln_person_id);
511 hr_utility_trace('Previous person = '||ln_prev_person_id);
512
513 IF (ln_person_id <> ln_prev_person_id) THEN
514
515 SELECT pay_assignment_actions_s.nextval
516 INTO ln_mag_asg_act_id
517 FROM dual;
518
519 hr_utility_trace('Creating report assignment action '||
520 ln_mag_asg_act_id);
521
522 hr_nonrun_asact.insact(ln_mag_asg_act_id
523 ,ln_assignment_id
524 ,p_payroll_action_id
525 ,p_chunk
526 ,gn_legal_er_id
527 ,null
528 ,'U'
529 ,null);
530
531 -- insert an interlock to this action
532 hr_utility.trace('Locking Action in IF = ' || ln_mag_asg_act_id);
533 hr_utility.trace('Locked Action in IF = ' || ln_arch_act_id);
534
535 hr_nonrun_asact.insint(ln_mag_asg_act_id,
536 ln_arch_act_id);
537
538 ELSE
539
540 -- insert an interlock to this action
541 hr_utility.trace('Locking Action in ELSE = ' || ln_mag_asg_act_id);
542 hr_utility.trace('Locked Action in ELSE = ' || ln_arch_act_id);
543
544 hr_nonrun_asact.insint(ln_mag_asg_act_id,
545 ln_arch_act_id);
546
547 END IF;
548
549 ln_prev_person_id := ln_person_id;
550
551 END LOOP;
552
553 hr_utility_trace(ln_asg_count || ' archiver actions processed in chunk '||
554 p_chunk);
555
556 IF lb_range_person_on THEN
557 CLOSE c_arch_asg_range;
558 ELSE
559 CLOSE c_arch_asg;
560 END IF;
561
562 hr_utility_trace ('Leaving '||l_proc_name);
563
564 END action_creation;
565
566 /****************************************************************************
567 Name : GENERATE_XML
568 Description : This procedure fetches archived data, converts it to XML
569 format and appends to pay_mag_tape.g_blob_value.
570 *****************************************************************************/
571 PROCEDURE generate_xml AS
572
573 CURSOR c_subemppaid_rec (cp_assignment_action_id number) IS
574 SELECT DISTINCT subemp_rpt.person_id PERSON_ID
575 ,pos.period_of_service_id pos_id
576 ,ppf.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
577 ,subemp_rpt.ER_LEGAL_NAME ER_LEGAL_NAME
578 ,subemp_rpt.FISCAL_YEAR_REPORTING FISCAL_YEAR
579 ,replace(RFC_ID,'-','') RFC_ID
580 ,CURP
581 ,pos.date_start HIRE_DATE
582 ,pos.actual_termination_date SEPARATION_DATE
583 ,ltrim(rtrim(PATERNAL_LAST_NAME))||' '||
584 ltrim(rtrim(MATERNAL_LAST_NAME))||' '||
585 ltrim(rtrim(NAMES)) NAME
586 ,nvl( ISR_SUBSIDY_FOR_EMP_PAID,0) ISR_SUBSIDY_FOR_EMPL_PAID
587 ,nvl( ISR_SUBSIDY_FOR_EMP,0) ISR_SUBSIDY_FOR_EMP
588 ,nvl(TOTAL_SUBJECT_EARNINGS,0)+
589 nvl(TOTAL_EXEMPT_EARNINGS,0) TOTAL_EARNINGS
590 FROM PAY_MX_ISR_TAX_FORMAT37_V subemp_rpt
591 ,pay_assignment_actions paa
592 ,pay_action_interlocks pai
593 ,per_periods_of_service pos
594 ,per_all_people_f ppf
595 ,per_all_assignments_f paf
596 WHERE subemp_rpt.legal_employer_id = gn_legal_er_id
597 AND subemp_rpt.payroll_action_id = paa.payroll_action_id
598 AND subemp_rpt.person_id = to_number(paa.serial_number)
599 and paa.assignment_id = paf.assignment_id
600 AND pos.person_id = subemp_rpt.person_id
601 AND paa.assignment_action_id = pai.locked_action_id
602 AND pai.locking_action_id = cp_assignment_action_id
603 AND paa.assignment_action_id = subemp_rpt.assignment_action_id
604 AND subemp_rpt.effective_date BETWEEN gd_start_date AND gd_end_date
605 AND ppf.person_id = pos.person_id
606 AND pos.period_of_service_id = paf.period_of_service_id
607 AND subemp_rpt.effective_date between ppf.effective_start_date AND ppf.effective_end_date
608 AND NVL(pos.actual_termination_date, gd_end_date) > gd_start_date;
609
610 CURSOR c_pact_id (cp_assignment_action_id NUMBER) IS
611 SELECT paa.payroll_action_id
612 FROM pay_assignment_actions paa
613 WHERE paa.assignment_action_id = cp_assignment_action_id;
614
615
616
617 l_proc_name varchar2(100);
618 l_xml BLOB;
619 lb_person_processed boolean;
620
621 ln_assignment_action_id NUMBER;
622 ln_business_group_id NUMBER;
623 ln_payroll_action_id NUMBER;
624
625 subemppaid c_subemppaid_rec%ROWTYPE;
626 ln_min_wage NUMBER;
627 ln_avg_daily_sal NUMBER;
628 lv_level VARCHAR2(10);
629 ln_count NUMBER;
630 ln_session_id NUMBER;
631
632
633 BEGIN
634 l_proc_name := g_proc_name || 'GENERATE_XML';
635 hr_utility_trace ('Entering '||l_proc_name);
636
637 ln_assignment_action_id := pay_magtape_generic.get_parameter_value
638 ('TRANSFER_ACT_ID');
639
640 hr_utility_trace ('Fetching transactions for magtape asg action '||
641 ln_assignment_action_id);
642
643 subemppaid_xml_tbl.DELETE;
644
645 gn_success_fail := 0;
646 gn_sep_bal := 0;
647 gn_ass_bal := 0;
648 gn_emp_bal := 0;
649 ln_avg_daily_sal := 0;
650 lv_level := '0';
651
652 SELECT fnd_global.local_chr(13) || fnd_global.local_chr(10)
653 INTO EOL
654 FROM dual;
655
656 OPEN c_pact_id( ln_assignment_action_id );
657 FETCH c_pact_id INTO ln_payroll_action_id;
658 CLOSE c_pact_id;
659
660 IF gn_legal_er_id IS NULL THEN
661
662 get_payroll_action_info (ln_payroll_action_id
663 ,gd_effective_date
664 ,gn_business_group_id
665 ,gn_legal_er_id
666 ,gv_sort_opt);
667
668 END IF;
669
670 hr_utility_trace ('Getting report parameters....');
671 hr_utility_trace ('gd_effective_date = '|| gd_effective_date);
672 hr_utility_trace ('gn_business_group_id = '|| gn_business_group_id);
673 hr_utility_trace ('gn_legal_er_id = '|| gn_legal_er_id);
674 hr_utility_trace ('gv_sort_opt = '|| gv_sort_opt);
675 OPEN c_subemppaid_rec(ln_assignment_action_id);
676 LOOP
677 FETCH c_subemppaid_rec INTO subemppaid;
678 EXIT WHEN c_subemppaid_rec%NOTFOUND;
679
680 populate_xml_table('FISCAL_YEAR', subemppaid.FISCAL_YEAR,'TEXT');
681 populate_xml_table('ER_LEGAL_NAME', subemppaid.ER_LEGAL_NAME,'TEXT');
682 populate_xml_table('EMPLOYEE_NUMBER', subemppaid.EMPLOYEE_NUMBER,'TEXT');
683 populate_xml_table('HIRE_DATE', subemppaid.HIRE_DATE,'TEXT');
684 populate_xml_table('RFC_ID', subemppaid.RFC_ID,'TEXT');
685 populate_xml_table('CURP', subemppaid.CURP,'TEXT');
686 populate_xml_table('NAME', subemppaid.NAME,'TEXT');
687 populate_xml_table('SEPARATION_DATE', subemppaid.SEPARATION_DATE,'TEXT');
688 populate_xml_table('TOTAL_EARNINGS',
689 subemppaid.TOTAL_EARNINGS,'TEXT');
690 populate_xml_table('SUBSIDY_FOR_EMPL',
691 subemppaid.ISR_SUBSIDY_FOR_EMP,'TEXT');
692 populate_xml_table('SUBSIDY_FOR_EMPL_PAID',
693 subemppaid.ISR_SUBSIDY_FOR_EMPL_PAID,'TEXT');
694
695
696 load_xml_internal('CS','ISR_SUBSIDY_EMP',NULL);
697
698 FOR i IN subemppaid_xml_tbl.FIRST..subemppaid_xml_tbl.LAST LOOP
699
700 load_xml_internal('D',subemppaid_xml_tbl(i).name,subemppaid_xml_tbl(i).value);
701
702 END LOOP;
703
704 load_xml_internal('CE','ISR_SUBSIDY_EMP',NULL);
705 subemppaid_xml_tbl.DELETE;
706 END LOOP;
707 CLOSE c_subemppaid_rec;
708
709
710 ln_session_id := USERENV('sessionid');
711
712 SELECT COUNT(*)
713 INTO ln_count
714 FROM pay_us_rpt_totals
715 WHERE tax_unit_id = ln_payroll_action_id
716 AND session_id = ln_session_id;
717
718 IF ln_count = 0 THEN
719
720 INSERT INTO pay_us_rpt_totals ( session_id
721 ,business_group_id
722 ,tax_unit_id
723 ,organization_name )
724 VALUES ( ln_session_id
725 ,gn_business_group_id
726 ,ln_payroll_action_id
727 ,subemppaid.ER_LEGAL_NAME );
728
729 END IF;
730
731 hr_utility_trace ('Leaving '||l_proc_name);
732
733 /*EXCEPTION
734 WHEN OTHERS THEN
735 hr_utility_trace (SQLERRM);
736 RAISE; */
737 END GENERATE_XML;
738
739
740 /****************************************************************************
741 Name : GENERATE_XML_HEADER
742 Description : This procedure generates XML header information and appends to
743 pay_mag_tape.g_blob_value.
744 *****************************************************************************/
745 PROCEDURE generate_xml_header AS
746
747 CURSOR c_le_name IS
748 SELECT hou.name
749 FROM hr_organization_units hou
750 WHERE hou.organization_id = gn_legal_er_id;
751
752 l_proc_name varchar2(100);
753 lv_buf varchar2(2000);
754 lv_rpt_sort_opt VARCHAR2(100);
755 lv_le_name hr_organization_units.name%type;
756 ln_payroll_action_id NUMBER;
757 BEGIN
758 l_proc_name := g_proc_name || 'GENERATE_XML_HEADER';
759 hr_utility_trace ('Entering '||l_proc_name);
760 subemppaid_xml_tbl.delete;
761 ln_payroll_action_id := pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID');
762 hr_utility_trace ('ln_payroll_action_id = '|| ln_payroll_action_id);
763 IF gn_legal_er_id IS NULL THEN
764
765 get_payroll_action_info (ln_payroll_action_id
766 ,gd_effective_date
767 ,gn_business_group_id
768 ,gn_legal_er_id
769 ,gv_sort_opt);
770
771 END IF;
772
773 hr_utility_trace ('Getting report parameters....');
774 hr_utility_trace ('gd_effective_date = '|| gd_effective_date);
775 hr_utility_trace ('gn_business_group_id = '|| gn_business_group_id);
776 hr_utility_trace ('gn_legal_er_id = '|| gn_legal_er_id);
777 hr_utility_trace ('gv_sort_opt = '|| gv_sort_opt);
778 hr_utility_trace ('Root XML tag = '||
779 pay_magtape_generic.get_parameter_value('ROOT_XML_TAG'));
780
781 lv_buf := pay_magtape_generic.get_parameter_value('ROOT_XML_TAG');
782 write_to_magtape_lob (lv_buf);
783
784 OPEN c_le_name;
785 FETCH c_le_name INTO lv_le_name;
786 CLOSE c_le_name;
787
788 lv_rpt_sort_opt := hr_general.decode_lookup('REPORT_SELECT_SORT_CODE',gv_sort_opt);
789
790 hr_utility_trace ('lv_le_name = '|| lv_le_name);
791 hr_utility_trace ('lv_rpt_sort_opt = '|| lv_rpt_sort_opt);
792 hr_utility_trace ('Building XML for Report param val....');
793
794 populate_xml_table('RPT_ER_LEGAL_NAME', lv_le_name,'TEXT');
795 populate_xml_table('RPT_FISCAL_YEAR',gv_rpting_year,'TEXT');
796 populate_xml_table('RPT_ASS_SET', gv_assignment_set,'TEXT');
797 populate_xml_table('RPT_SORT_OPT', lv_rpt_sort_opt,'TEXT');
798
799
800 load_xml_internal('CS','REPORT_PARM',NULL);
801
802 FOR i IN subemppaid_xml_tbl.FIRST..subemppaid_xml_tbl.LAST LOOP
803
804 load_xml_internal('D',subemppaid_xml_tbl(i).name,subemppaid_xml_tbl(i).value);
805
806 END LOOP;
807
808 load_xml_internal('CE','REPORT_PARM',NULL);
809
810 hr_utility_trace ('Leaving '||l_proc_name);
811 END generate_xml_header;
812
813
814 /****************************************************************************
815 Name : GENERATE_XML_FOOTER
816 Description : This procedure generates XML information for GRE and the final
817 closing tag. Final result is appended to
818 pay_mag_tape.g_blob_value.
819 *****************************************************************************/
820 PROCEDURE generate_xml_footer AS
821
822 lt_act_info_id pay_payroll_xml_extract_pkg.int_tab_type;
823 ln_pact_id number;
824 l_xml BLOB;
825 l_proc_name varchar2(100);
826 ln_chars number;
827 ln_offset number;
828 lv_buf varchar2(8000);
829 lr_xml RAW (32767);
830 ln_amt number;
831 BEGIN
832 l_proc_name := g_proc_name || 'GENERATE_XML_FOOTER';
833 hr_utility_trace ('Entering '||l_proc_name);
834
835 lv_buf := '</' ||
836 SUBSTR(pay_magtape_generic.get_parameter_value('ROOT_XML_TAG'),
837 2);
838
839 write_to_magtape_lob (lv_buf);
840
841 hr_utility_trace ('Leaving '||l_proc_name);
842 END generate_xml_footer;
843
844 BEGIN
845 -- hr_utility.trace_on(null, 'PAYMXSUBEMPL');
846 g_proc_name := 'PAY_MX_SUBSIDY_EMP_PD_RPT.';
847 g_debug := hr_utility.debug_enabled;
848 g_document_type := 'MX_subemppaid';
849 END PAY_MX_SUBSIDY_EMP_PD_RPT;