[Home] [Help]
PACKAGE BODY: APPS.PAY_PAYROLL_XML_EXTRACT_PKG
Source
1 PACKAGE BODY PAY_PAYROLL_XML_EXTRACT_PKG as
2 /* $Header: pyxmlxtr.pkb 120.22.12020000.11 2013/03/07 11:26:45 pkoduri ship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 2004, Oracle India Pvt. Ltd., Hyderabad *
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 : pay_payroll_xml_extract_pkg
21
22 Description : This package handles generation of XML from data archived
23 in pay_action_information. Calling applications can invoke
24 one of the overloaded versions of GENERATE procedure with
25 appropriate parameters to obtain the XML. This package has
26 other public procedures which GENERATE uses for processing.
27 They might not be of much use if invoked directly by calling
28 applications.
29
30 Change List
31 -----------
32 Date Name Vers Bug No Description
33 ----------- ---------- ------ ------- -------------------------------
34 23-NOV-2004 sdahiya 115.0 Created.
35 09-DEC-2004 sdahiya 115.1 Added XML meta tag.
36 22-DEC-2004 sdahiya 115.2 - Modified procedure GENERATE to
37 retrieve records archived at
38 payroll action level too.
39 - Modified LOAD_XML_INTERNAL to
40 handle occurance of special
41 characters in XML data.
42 - Added DocumentProcessor XML tags.
43 20-FEB-2005 sdahiya 115.3 Modified parameters of GENERATE
44 procedure. Created local procedure
45 BUILD_SQL.
46 06-APR-2005 sdahiya 115.4 Modified LOAD_XML procedure to fetch
47 cheque number for
48 EMPLOYEE NET PAY DISTRIBUTION
49 context.
50 11-JUL-2005 sdahiya 115.5 Added overloaded versions of
51 GENERATE procedure so that it can be
52 driven off action_information_id
53 too.
54 15-JUL-2005 sdahiya 115.6 Modified signature of GENERATE
55 overloaded procedure to handle
56 custom XML tags.
57 01-AUG-2005 sdahiya 115.7 Added support for localization
58 package and removed
59 DocumentProcessor XML tags.
60 04-AUG-2005 sdahiya 115.8 4534551 Added LTRIM and RTRIM functions.
61 18-AUG-2005 sdahiya 115.9 Added LOAD_XML_DATA procedure.
62 Renamed global variable
63 g_xml_payslip to g_xml_table and
64 moved it to package header.
65 07-NOV-2005 sdahiya 115.10 Used bind variables instead of
66 literals while opening
67 csr_get_archived_info_rec cursor in
68 generate_internal procedure.
69 08-NOV-2005 sdahiya 115.11 Formatting and indentation changes.
70 20-NOV-2005 vmehta 115.12 Added overloaded version of LOAD_XML
71 which accepts flexfield name.
72 21-NOV-2005 sdahiya 115.13 4773967 Modified procedures to return
73 generated XML as a BLOB instead of
74 CLOB.
75 01-DEC-2005 sdahiya 115.14 Modified PRINT_BLOB to use
76 pay_ac_utility.print_lob.
77 28-MAR-2006 sdahiya 115.15 Dynamically fetch IANA charset to
78 identify XML encoding.
79 06-APR-2006 sdahiya 115.16 Appended action_information_id
80 parameter to the custom parameter
81 list (g_custom_params) for use in
82 PAY_<LEG_CODE>_RULES.
83 24-MAY-2006 sdahiya 115.17 6068599 Cache g_custom_params should be
84 cleared in case of abnormal
85 termination.
86 21-AUG-2008 jalin 115.18 6522667 Fixed performance issue, added
87 application_id=801 condition into
88 cursor csr_get_tag_name and
89 cursor csr_csr_seg_enabled
90 24-FEB-2010 sjawid 115.19 9384276 Passing payment_method_id(action_information2)
91 to g_custom_params before localization procedure
92 call for US payslip.
93 19-MAR-2010 sjawid 115.20 9488426 Revert back the changes made for bug 9384276
94 Modified generate_internal procedure to get
95 Check_number and masked account number for
96 Employee Third party payments.
97 11-Aug-2011 pkoduri 115.21 12636751 Added code in generate_internal() procedure
98 to Validate the display of dates on a PDF
99 payslip using user tables.
100 23-Feb-2012 abellur 115.26 13029999 Added proc generate_third_party to generate
101 XML for third party checks.
102 28-Feb-2012 sjawid 115.12 13743223 Changes for Payslip functionality of
103 International Payroll.
104 12-Mar-2012 sgotlasw 115.28 13834362 Placed 'g_install_leg_check' initialization
105 appropriately.
106 23-May-2012 pkoduri 115.29 14105250 Modified code to pass business group id instead
107 of organization Id to fetch usertable value.
108 10-Jan-2013 pracagra 115.32 16008057 Modified code to substr l_data for GRE length issue.
109 04-Feb-2013 emunisek 115.33 16082307 Created Procedure SIMULATION_GENERATE to generate
110 the XML Data for Payroll Simulation Output. Also
111 updated other procedures as necessary for Payroll
112 Simulation.
113 07-Mar-2013 emunisek 115.16 16281614 Moved the procedure SIMULATION_GENERATE
114 to package PAY_US_PAYSLIP_SIMULATION_MAIN
115 so that this package can have no dependancies
116 on Simulation Code.
117 07-Mar-2013 pkoduri 115.17 16451278 Replaced orgId with BG Id for determinig the
118 display of RATE DETAILS section on PDF Payslip.
119 *****************************************************************************/
120
121 g_proc_name varchar2(50);
122 g_debug boolean;
123 g_action_ctx_id number;
124 g_custom_context pay_action_information.action_information_category%type;
125
126
127 /****************************************************************************
128 Name : HR_UTILITY_TRACE
129 Description : This procedure prints debug messages during diagnostics mode.
130 *****************************************************************************/
131
132 PROCEDURE HR_UTILITY_TRACE(trc_data varchar2) IS
133 BEGIN
134 IF g_debug THEN
135 hr_utility.trace(trc_data);
136 END IF;
137 END HR_UTILITY_TRACE;
138
139
140 /****************************************************************************
141 Name : PRINT_BLOB
142 Description : This procedure prints contents of BLOB passed as parameter.
143 *****************************************************************************/
144
145 PROCEDURE PRINT_BLOB(p_blob BLOB) IS
146 BEGIN
147 IF g_debug THEN
148 pay_ac_utility.print_lob(p_blob);
149 END IF;
150 END PRINT_BLOB;
151
152 /****************************************************************************
153 Name : LOAD_XML_INTERNAL
154 Description : This procedure loads the global XML cache.
155 *****************************************************************************/
156 PROCEDURE LOAD_XML_INTERNAL (
157 P_NODE_TYPE varchar2,
158 P_NODE varchar2,
159 P_DATA varchar2
160 ) AS
161 l_proc_name varchar2(100);
162 l_data pay_action_information.action_information1%type;
163
164 BEGIN
165 l_proc_name := g_proc_name || 'LOAD_XML_INTERNAL';
166 hr_utility_trace ('Entering '||l_proc_name);
167
168 IF p_node_type = 'CS' THEN
169 load_xml ('<'||p_node||'>');
170 ELSIF p_node_type = 'CE' THEN
171 load_xml ('</'||p_node||'>');
172 ELSIF p_node_type = 'D' THEN
173 /* Handle special charaters in data */
174 l_data := REPLACE (p_data, '&', '&');
175 l_data := REPLACE (l_data, '>', '>');
176 l_data := REPLACE (l_data, '<', '<');
177 l_data := REPLACE (l_data, '''', ''');
178 l_data := REPLACE (l_data, '"', '"');
179 l_data := substr (l_data, 1, 200); --Added for GRE length issue Bug 16008057
180 load_xml ('<'||p_node||'>'||l_data||'</'||p_node||'>');
181 END IF;
182
183 hr_utility_trace ('Leaving '||l_proc_name);
184 END LOAD_XML_INTERNAL;
185
186
187 /****************************************************************************
188 Name : LOAD_XML
189 Description : This procedure loads the global XML cache.
190 Parameters : P_NODE_TYPE This parameter can take one of these
191 values: -
192 1. CS - This signifies that string contained
193 in P_NODE parameter is start of
194 container node. P_DATA parameter is
195 ignored in this mode.
196 2. CE - This signifies that string
197 contained in P_NODE parameter is
198 end of container node. P_DATA
199 parameter is ignored in this mode.
200 3. D - This signifies that string
201 contained in P_NODE parameter is
202 data node and P_DATA carries actual
203 data to be contained by tag
204 specified by P_NODE parameter.
205
206 P_CONTEXT_CODE Context code of Action Information DF.
207
208 P_NODE Name of XML tag, or, application column
209 name of flex segment.
210
211 P_DATA Data to be contained by tag specified by
212 P_NODE parameter. P_DATA is not used unless
213 P_NODE_TYPE = D.
214 *****************************************************************************/
215
216 PROCEDURE LOAD_XML (
217 P_NODE_TYPE varchar2,
218 P_FLEXFIELD_NAME varchar2,
219 P_CONTEXT_CODE varchar2,
220 P_NODE varchar2,
221 P_DATA varchar2
222 ) AS
223
224 CURSOR csr_get_tag_name IS
225 SELECT TRANSLATE (UPPER(end_user_column_name), ' /','__') tag_name,
226 end_user_column_name segment_name
227 FROM fnd_descr_flex_col_usage_vl
228 WHERE descriptive_flexfield_name = p_flexfield_name
229 AND descriptive_flex_context_code = p_context_code
230 AND application_column_name = UPPER (p_node)
231 AND application_id = 801; /* Bug 6522667 */
232
233 CURSOR csr_get_chk_no IS
234 SELECT paa_chk.serial_number
235 FROM pay_assignment_actions paa_xfr,
236 pay_action_interlocks pai_xfr,
237 pay_action_interlocks pai_chk,
238 pay_assignment_actions paa_chk,
239 pay_payroll_actions ppa_chk
240 WHERE paa_xfr.assignment_action_id = pai_xfr.locking_action_id
241 AND pai_xfr.locked_action_id = pai_chk.locked_action_id
242 AND pai_chk.locking_action_id = paa_chk.assignment_action_id
243 AND paa_chk.payroll_action_id = ppa_chk.payroll_action_id
244 AND ppa_chk.action_type = 'H'
245 AND paa_xfr.assignment_action_id = g_action_ctx_id;
246
247 l_proc_name varchar2(100);
248 l_tag_name varchar2(500);
249 l_segment_name varchar2(500);
250 l_chk_no pay_assignment_actions.serial_number%type;
251 l_data pay_action_information.action_information1%type;
252 l_data_tag varchar2(500);
253
254 BEGIN
255 l_proc_name := g_proc_name || 'LOAD_XML';
256 hr_utility_trace ('Entering '||l_proc_name);
257
258 IF p_node_type = 'D' THEN
259
260 /* Fetch segment names */
261 OPEN csr_get_tag_name;
262 FETCH csr_get_tag_name INTO l_tag_name,l_segment_name;
263 CLOSE csr_get_tag_name;
264
265 /* Fetch cheque number */
266 IF p_flexfield_name = 'Action Information DF' AND
267 p_context_code = 'EMPLOYEE NET PAY DISTRIBUTION' AND
268 l_tag_name = 'CHECK_DEPOSIT_NUMBER' AND g_install_leg_check = 'Y' THEN /*bug 13743223*/
269 OPEN csr_get_chk_no;
270 FETCH csr_get_chk_no INTO l_chk_no;
271 CLOSE csr_get_chk_no;
272 END IF;
273 END IF;
274
275 IF UPPER(p_node) NOT LIKE '?XML%' AND UPPER(p_node) NOT LIKE 'XAPI%' THEN
276 l_tag_name := nvl(l_tag_name, TRANSLATE(p_node, ' /', '__'));
277 IF p_node_type IN ('CS', 'CE') THEN
278 l_tag_name := nvl(g_custom_context, TRANSLATE(p_node, ' /', '__'));
279 END IF;
280 ELSE
281 l_tag_name := p_node;
282 END IF;
283
284 l_data := nvl(l_chk_no, p_data);
285
286 /* Special Processing for Payroll Simulation Related Contexts */
287 IF p_flexfield_name = 'Action Information DF' AND
288 (p_context_code = 'AC SIM GRAPH ACTUAL' OR
289 p_context_code = 'AC SIM GRAPH ESTIMATED')
290 THEN
291
292 IF p_context_code = 'AC SIM GRAPH ACTUAL' THEN
293
294 l_data_tag := 'CURRENT_DATA';
295
296 ELSE
297
298 l_data_tag := 'ESTIMATED_DATA';
299
300 END IF;
301
302 load_xml('CS', NULL, l_data_tag, NULL);
303 load_xml_internal (p_node_type, 'CATEGORY',l_segment_name);
304 load_xml_internal (p_node_type, 'AMOUNT',l_data);
305 load_xml('CE', NULL, l_data_tag, NULL);
306
307 ELSE
308
309 load_xml_internal (p_node_type, l_tag_name, l_data);
310
311 END IF;
312
313
314
315 hr_utility_trace ('Leaving '||l_proc_name);
316 END LOAD_XML;
317
318
319 /****************************************************************************
320 Name : LOAD_XML
321 Description : This procedure obtains segment title from the Action
322 Information DF. This is temporary, and is created only to
323 provide backward compatibility for payslip code. Once the
324 payslip processes are changed to pass the flexfield name,
325 this procedure can be removed.
326 *****************************************************************************/
327 PROCEDURE LOAD_XML (
328 P_NODE_TYPE varchar2,
329 P_CONTEXT_CODE varchar2,
330 P_NODE varchar2,
331 P_DATA varchar2
332 ) AS
333
334 l_proc_name varchar2(100);
335
336 BEGIN
337 l_proc_name := g_proc_name || 'LOAD_XML-4';
338 hr_utility_trace ('Entering '||l_proc_name);
339
340 load_xml(p_node_type => p_node_type,
341 p_flexfield_name => 'Action Information DF',
342 p_context_code => p_context_code,
343 p_node => p_node,
344 p_data => p_data);
345
346 hr_utility_trace ('Leaving '||l_proc_name);
347
348 END LOAD_XML;
349
350 /****************************************************************************
351 Name : LOAD_XML
352 Description : This procedure obtains segment title from the bank key
353 flexfield to be used as XML tag.
354 *****************************************************************************/
355 PROCEDURE LOAD_XML (
356 P_NODE_TYPE varchar2,
357 P_NODE varchar2,
358 P_DATA varchar2
359 ) AS
360
361 CURSOR csr_get_tag_name (p_id_flex_structure_code varchar2) IS
362 SELECT TRANSLATE (UPPER(seg.segment_name), ' /','__')
363 FROM fnd_id_flex_structures_vl ctx,
364 fnd_id_flex_segments_vl seg
365 WHERE ctx.id_flex_num = seg.id_flex_num
366 AND ctx.id_flex_code = seg.id_flex_code
367 AND seg.id_flex_code = 'BANK'
368 AND ctx.id_flex_structure_code = p_id_flex_structure_code
369 AND seg.application_column_name = UPPER(p_node);
370
371 l_proc_name varchar2(100);
372 l_tag_name varchar2(500);
373 l_struct_code fnd_id_flex_structures.id_flex_structure_code%type;
374
375 BEGIN
376 l_proc_name := g_proc_name || 'LOAD_XML-2';
377 hr_utility_trace ('Entering '||l_proc_name);
378
379 IF p_node_type = 'D' THEN
380 OPEN csr_get_tag_name (pay_payroll_xml_extract_pkg.g_leg_code||
381 '_BANK_DETAILS');
382 FETCH csr_get_tag_name INTO l_tag_name;
383 CLOSE csr_get_tag_name;
384 END IF;
385
386 IF UPPER(p_node) NOT LIKE '?XML%' AND UPPER(p_node) NOT LIKE 'XAPI%' THEN
387 l_tag_name := nvl(l_tag_name, TRANSLATE(p_node,' /', '__'));
388 IF p_node_type IN ('CS', 'CE') THEN
389 l_tag_name := nvl(g_custom_context, TRANSLATE(p_node, ' /', '__'));
390 END IF;
391 ELSE
392 l_tag_name := p_node;
393 END IF;
394
395 load_xml_internal (p_node_type, l_tag_name, p_data);
396
397 hr_utility_trace ('Leaving '||l_proc_name);
398 END LOAD_XML;
399
400 /****************************************************************************
401 Name : LOAD_XML
402 Description : This procedure accepts a well-formed XML and loads it into
403 global XML cache. Note that this procedure does not perform
404 any syntactical validations over passed XML data.
405 LOAD_XML_DATA should be used if such validations are required
406 to be performed implicitly.
407 *****************************************************************************/
408 PROCEDURE LOAD_XML (
409 P_XML pay_action_information.action_information1%type
410 ) AS
411
412 l_proc_name varchar2(100);
413
414 BEGIN
415 l_proc_name := g_proc_name || 'LOAD_XML-3';
416 hr_utility_trace ('Entering '||l_proc_name);
417
418 g_xml_table (g_xml_table.count() + 1) := p_xml;
419
420 hr_utility_trace ('Leaving '||l_proc_name);
421 END;
422
423 /****************************************************************************
424 Name : LOAD_XML_DATA
425 Description : This procedure accepts meta-data along with actual XML data
426 and loads the global XML cache. This is a public procedure
427 which performs basic validations to check well-formedness of
428 XML data before loading the cache. Please see parameter
429 description of public version of LOAD_XML to find what each
430 parameter signifies.
431 *****************************************************************************/
432 PROCEDURE LOAD_XML_DATA (
433 P_NODE_TYPE varchar2,
434 P_NODE varchar2,
435 P_DATA varchar2
436 ) AS
437 l_proc_name varchar2(100);
438 BEGIN
439 l_proc_name := g_proc_name || 'LOAD_XML_DATA';
440 hr_utility_trace ('Entering '||l_proc_name);
441
442 load_xml_internal (p_node_type,
443 p_node,
444 p_data);
445
446 hr_utility_trace ('Leaving '||l_proc_name);
447 END LOAD_XML_DATA;
448
449 /****************************************************************************
450 Name : FLEX_SEG_ENABLED
451 Description : This function returns TRUE if an application column is
452 registered with given context of Action Information DF.
453 Otherwise, it returns false.
454 *****************************************************************************/
455 FUNCTION FLEX_SEG_ENABLED
456 (
457 P_CONTEXT_CODE varchar2,
458 P_APPLICATION_COLUMN_NAME varchar2
459 ) RETURN BOOLEAN AS
460
461 CURSOR csr_seg_enabled IS
462 SELECT 'Y'
463 FROM fnd_descr_flex_col_usage_vl
464 WHERE descriptive_flexfield_name like 'Action Information DF'
465 AND descriptive_flex_context_code = p_context_code
466 AND application_column_name like p_application_column_name
467 AND application_id = 801 /* Bug 6522667 */
468 AND enabled_flag = 'Y';
469
470 l_proc_name varchar2(100);
471 l_exists varchar2(1);
472
473 BEGIN
474 l_proc_name := g_proc_name || 'FLEX_SEG_ENABLED';
475 hr_utility_trace ('Entering '||l_proc_name);
476
477 OPEN csr_seg_enabled;
478 FETCH csr_seg_enabled INTO l_exists;
479 CLOSE csr_seg_enabled;
480
481 hr_utility_trace ('Leaving '||l_proc_name);
482
483 IF l_exists = 'Y' THEN
484 RETURN (TRUE);
485 ELSE
486 RETURN (FALSE);
487 END IF;
488
489 END FLEX_SEG_ENABLED;
490
491
492 /****************************************************************************
493 Name : BUILD_SQL
494 Description : This procedure builds dynamic SQL string.
495 *****************************************************************************/
496
497 PROCEDURE BUILD_SQL
498 (
499 P_SQLSTR_TAB IN OUT NOCOPY dbms_sql.varchar2s,
500 P_CNTR IN OUT NOCOPY number,
501 P_STRING varchar2
502 ) AS
503 l_proc_name varchar2(100);
504 BEGIN
505 l_proc_name := g_proc_name || 'BUILD_SQL';
506 hr_utility_trace ('Entering '||l_proc_name);
507 p_sqlstr_tab(p_cntr) := p_string;
508 p_cntr := p_cntr + 1;
509 hr_utility_trace ('Leaving '||l_proc_name);
510 END;
511
512
513 /****************************************************************************
514 Name : GENERATE_INTERNAL
515 Description : This procedure interprets archived information, converts it to
516 XML and prints it to a BLOB. This is a private procedure.
517
518 IMP. NOTE: - This procedure can be invoked either by
519 action_information_id or action_context_id, one at a time. i.e
520 for any given call of this procedure, exactly one of these
521 parameters can be passed a NOT NULL value.
522 *****************************************************************************/
523
524 PROCEDURE GENERATE_INTERNAL
525 (
526 P_ACTION_INFORMATION_ID number,
527 P_ACTION_CONTEXT_ID number,
528 P_CUSTOM_ACTION_INFO_CAT varchar2,
529 P_CUSTOM_XML_PROCEDURE varchar2,
530 P_GENERATE_HEADER_FLAG boolean,
531 P_ROOT_TAG varchar2,
532 P_DOCUMENT_TYPE varchar2,
533 P_XML OUT NOCOPY BLOB
534 ) AS
535
536 CURSOR get_leg_code IS
537 SELECT hoi2.org_information9
538 FROM pay_assignment_actions paa,
539 pay_payroll_actions ppa,
540 hr_organization_units hou,
541 hr_organization_information hoi1,
542 hr_organization_information hoi2
543 WHERE paa.payroll_action_id = ppa.payroll_action_id
544 AND ppa.business_group_id = hou.organization_id
545 AND hou.organization_id = hoi1.organization_id
546 AND hoi1.organization_id = hoi2.organization_id
547 AND ppa.effective_date BETWEEN hou.date_from
548 AND nvl(hou.date_to,
549 hr_general.end_of_time)
550 AND hoi1.org_information_context = 'CLASS'
551 AND hoi1.org_information1 = 'HR_BG'
552 AND hoi2.org_information_context = 'Business Group Information'
553 AND ppa.action_type = 'X'
554 AND NVL (p_action_context_id, (SELECT action_context_id
555 FROM pay_action_information
556 WHERE action_information_id =
557 p_action_information_id))
558 = paa.assignment_action_id;
559
560 CURSOR csr_get_archived_regions IS
561 SELECT DISTINCT action_information_category
562 FROM pay_action_information
563 WHERE ((action_context_type = 'AAP'
564 AND action_context_id = p_action_context_id)
565 OR (action_context_type = 'PA'
566 AND action_context_id =
567 (SELECT payroll_action_id
568 FROM pay_assignment_actions
569 WHERE assignment_action_id = p_action_context_id)))
570 OR (action_information_id = p_action_information_id
571 AND p_action_information_id IS NOT NULL)
572 ORDER BY decode (action_information_category,'EMPLOYEE DETAILS', 1, 2);
573 /* NOTE - This ORDER BY clause will make sure that EMPLOYEE DETAILS gets
574 processed before all other action information categories so
575 that we have the organization_id (action_information2) for
576 filtering undesired ADDRESS DETAILS archived at payroll action
577 level (action_context_type = 'PA') by the payroll archiver.*/
578
579 /*14105250 -- Start*/
580 CURSOR c_bgid (cp_assgn_action_id in number) is
581 SELECT business_group_id
582 FROM pay_payroll_actions ppa,
583 pay_assignment_actions paa
584 WHERE
585 paa.payroll_action_id = ppa.payroll_action_id
586 AND paa.assignment_action_id=cp_assgn_action_id;
587 lt_per_bgId_value number;
588 /*14105250 -- End*/
589 l_proc_name varchar2(100);
590 sqlstr dbms_sql.varchar2s;
591 l_cntr_sql number;
592 l_xml BLOB;
593 csr number;
594 ret number;
595 cntr_flex_col number;
596 l_flex_col_num number; /* Max. number of flex segments in Action
597 Informtion DF */
598
599 l_kff_seg_start number; /* Segment number where bank KFF segments start.
600 Currently, it is ACTION_INFORMATION5, so,
601 l_kff_seg_start = 5 */
602
603 l_kff_seg_end number; /* Segment number where bank KFF segments end.
604 Currently, it is ACTION_INFORMATION14, so,
605 l_kff_seg_end = 14 */
606
607 l_action_information_id varchar2(100);
608 l_action_context_id varchar2(100);
609 lr_xml RAW (32767);
610 ln_amt number;
611
612 BEGIN
613 l_proc_name := g_proc_name || 'GENERATE_INTERNAL';
614 hr_utility_trace ('Entering '||l_proc_name);
615 hr_utility_trace ('Parameters ....');
616 hr_utility_trace ('P_ACTION_INFORMATION_ID ='||P_ACTION_INFORMATION_ID);
617 hr_utility_trace ('P_ACTION_CONTEXT_ID ='||P_ACTION_CONTEXT_ID);
618 IF p_generate_header_flag THEN
619 hr_utility_trace ('P_GENERATE_HEADER_FLAG = TRUE');
620 ELSE
621 hr_utility_trace ('P_GENERATE_HEADER_FLAG = FALSE');
622 END IF;
623 hr_utility_trace ('P_CUSTOM_ACTION_INFO_CAT ='||P_CUSTOM_ACTION_INFO_CAT);
624
625
626 g_xml_table.delete();
627 l_flex_col_num := 30;
628 l_kff_seg_start := 5;
629 l_kff_seg_end := 14;
630 l_cntr_sql := 1;
631 g_action_ctx_id := p_action_context_id;
632 g_custom_context := TRANSLATE(p_custom_action_info_cat, ' /', '__');
633
634 SELECT DECODE (p_action_information_id,
635 NULL, 'NULL',
636 to_char(p_action_information_id)),
637 DECODE (p_action_context_id,
638 NULL, 'NULL',
639 to_char(p_action_context_id))
640 INTO l_action_information_id,
641 l_action_context_id
642 FROM DUAL;
643
644 build_sql(sqlstr, l_cntr_sql, 'declare l_org_id varchar2(100);');
645
646 /*12636751*/
647 build_sql(sqlstr, l_cntr_sql, 'lt_pay_date DATE := TO_DATE(''0001/01/01'',''YYYY/MM/DD'');');
648 build_sql(sqlstr, l_cntr_sql, 'lt_state_cd VARCHAR2(2);');
649 build_sql(sqlstr, l_cntr_sql, 'lt_element_type VARCHAR2(240);');
650 build_sql(sqlstr, l_cntr_sql, 'lt_ret_val VARCHAR2(2);');
651 build_sql(sqlstr, l_cntr_sql, 'lt_rate_flg VARCHAR2(2);');
652 /*12636751*/
653 build_sql(sqlstr, l_cntr_sql, 'begin ');
654 /*12636751*/
655 build_sql(sqlstr, l_cntr_sql, 'lt_state_cd :=NULL;');
656 build_sql(sqlstr, l_cntr_sql, 'lt_element_type :=NULL;');
657 build_sql(sqlstr, l_cntr_sql, 'lt_ret_val :=''N'';');
658 build_sql(sqlstr, l_cntr_sql, 'lt_rate_flg :=''N'';');
659 /*12636751*/
660 /*14105250 -- Start*/
661 lt_per_bgId_value := 0;
662 /* Fetch Businesss Grp Id */
663 OPEN c_bgid(g_action_ctx_id);
664 FETCH c_bgid INTO lt_per_bgId_value;
665 CLOSE c_bgid;
666 /*14105250 -- end*/
667
668 IF p_generate_header_flag THEN
669 build_sql(sqlstr,
670 l_cntr_sql,
671 'pay_payroll_xml_extract_pkg.load_xml(''CS'', NULL, ''?xml version="1.0" encoding="'||
672 hr_mx_utility.get_IANA_charset||'"?'', NULL);');
673 END IF;
674 IF p_root_tag IS NOT NULL THEN
675 build_sql(sqlstr,
676 l_cntr_sql,
677 'pay_payroll_xml_extract_pkg.load_xml(''CS'', NULL, '''||
678 p_root_tag||''', NULL);');
679 END IF;
680
681 /* Fetch legislation_code. */
682 OPEN get_leg_code;
683 FETCH get_leg_code INTO pay_payroll_xml_extract_pkg.g_leg_code;
684 CLOSE get_leg_code;
685
686
687 /* Checking for International Payroll Legislation,
688 The flag g_install_leg_check returns 'N' for International Payroll localization and 'Y' for supported legislations.
689 */
690 g_install_leg_check := pay_ip_utility.get_ip_installation(pay_payroll_xml_extract_pkg.g_leg_code);
691
692 FOR csr_get_archived_regions_rec IN csr_get_archived_regions LOOP
693 IF csr_get_archived_regions_rec.action_information_category IN
694 ('ADDRESS DETAILS', pay_payroll_xml_extract_pkg.g_leg_code ||
695 ' EMPLOYER DETAILS') THEN
696 build_sql(sqlstr,
697 l_cntr_sql,
698 'FOR csr_get_archived_info_rec IN pay_payroll_xml_extract_pkg.csr_get_archived_info (:l_action_context_id,'''||
699 csr_get_archived_regions_rec.action_information_category
700 ||''', l_org_id, :l_action_information_id) LOOP ');
701 ELSE
702 build_sql(sqlstr,
703 l_cntr_sql,
704 'FOR csr_get_archived_info_rec IN pay_payroll_xml_extract_pkg.csr_get_archived_info (:l_action_context_id,'''||
705 csr_get_archived_regions_rec.action_information_category
706 ||''', NULL, :l_action_information_id) LOOP ');
707 END IF;
708 build_sql(sqlstr,
709 l_cntr_sql,
710 'pay_payroll_xml_extract_pkg.load_xml(''CS'', NULL, ''' ||
711 csr_get_archived_regions_rec.action_information_category ||
712 ''', NULL);');
713 /*12636751 --Start */
714 IF csr_get_archived_regions_rec.action_information_category = 'EMPLOYEE DETAILS'
715 AND pay_payroll_xml_extract_pkg.g_leg_code = 'US' THEN
716 build_sql(sqlstr, l_cntr_sql, 'lt_pay_date := csr_get_archived_info_rec.effective_date;');
717
718 build_sql(sqlstr,l_cntr_sql,
719 ' SELECT NVL((select lei_information13 from hr_location_extra_info hle ,HR_LOCATIONS_ALL HLA '||
720 'WHERE hle.information_type = ''HR_SELF_SERVICE_LOC_PREFERENCE''');
721 build_sql(sqlstr,l_cntr_sql,
722 'AND hle.lei_information1 =''PAYSLIP'' AND hla.LOCATION_ID = hle.LOCATION_ID AND '||
723 'hla.LOCATION_CODE=csr_get_archived_info_rec.action_information30),''K'') INTO lt_rate_flg FROM DUAL;');
724 build_sql(sqlstr,l_cntr_sql,
725 'IF lt_rate_flg = ''K'' THEN');
726 build_sql(sqlstr,l_cntr_sql,
727 ' SELECT NVL((select org_information13 FROM hr_organization_information '||
728 'WHERE org_information_context = ''HR_SELF_SERVICE_ORG_PREFERENCE'' and ORG_INFORMATION1=''PAYSLIP''');
729 build_sql(sqlstr,l_cntr_sql,
730 'AND organization_id=csr_get_archived_info_rec.action_information2),''K'') INTO lt_rate_flg FROM DUAL ;');
731 build_sql(sqlstr,l_cntr_sql,
732 'IF lt_rate_flg = ''K'' THEN ');
733 build_sql(sqlstr,l_cntr_sql,
734 ' SELECT NVL((select org_information13 FROM hr_organization_information '||
735 'WHERE org_information_context = ''HR_SELF_SERVICE_BG_PREFERENCE'' and ORG_INFORMATION1=''PAYSLIP''');
736 build_sql(sqlstr,l_cntr_sql,
737 'AND organization_id='||lt_per_bgId_value||'),''K'') INTO lt_rate_flg FROM DUAL;'); --Bug 16451278
738 build_sql(sqlstr,l_cntr_sql,
739 'IF lt_rate_flg = ''K'' THEN ');
740 build_sql(sqlstr,l_cntr_sql,
741 ' lt_rate_flg := ''N'';');
742 build_sql(sqlstr,l_cntr_sql,
743 'END IF;'||
744 'END IF;'||
745 'END IF;');
746 build_sql(sqlstr,l_cntr_sql,
747 'pay_payroll_xml_extract_pkg.load_xml(''D'', ''RATE_DISP_FLG'', lt_rate_flg);');
748
749 END IF;
750
751 IF csr_get_archived_regions_rec.action_information_category = 'AC EARNINGS'
752 AND pay_payroll_xml_extract_pkg.g_leg_code = 'US' THEN
753 build_sql(sqlstr, l_cntr_sql, 'lt_state_cd := substr(csr_get_archived_info_rec.action_information21,1,2);');
754 build_sql(sqlstr, l_cntr_sql, 'lt_element_type := csr_get_archived_info_rec.action_information20;');
755 build_sql(sqlstr,
756 l_cntr_sql,
757 'SELECT NVL(hruserdt.get_table_value('||lt_per_bgId_value||',''ONLINE_PAYSLIP_DATE_CONTROL_TBL'',lt_state_cd'||
758 ',lt_element_type,lt_pay_date),''N'') INTO lt_ret_val FROM DUAL ;');
759 build_sql(sqlstr,
760 l_cntr_sql,
761 ' pay_payroll_xml_extract_pkg.load_xml(''D'', ''DATE_DISP_FLG'', lt_ret_val);');
762 END IF;
763 /*12636751 --End */
764 /*13743223: */
765 IF csr_get_archived_regions_rec.action_information_category =
766 'EMPLOYEE NET PAY DISTRIBUTION'
767 AND g_install_leg_check = 'N' THEN
768
769 hr_utility.trace('Entering employee net pay '||g_install_leg_check);
770 build_sql(sqlstr,
771 l_cntr_sql,
772 'csr_get_archived_info_rec.action_information4 :=
773 pay_ip_utility.get_check_number(csr_get_archived_info_rec.action_information17
774 ,csr_get_archived_info_rec.action_information15);');
775
776 END IF;
777
778
779 cntr_flex_col := 1;
780 LOOP
781 EXIT WHEN cntr_flex_col > l_flex_col_num;
782 IF flex_seg_enabled (
783 csr_get_archived_regions_rec.action_information_category,
784 'ACTION_INFORMATION'||cntr_flex_col) THEN
785 IF csr_get_archived_regions_rec.action_information_category =
786 'EMPLOYEE NET PAY DISTRIBUTION'
787 AND cntr_flex_col BETWEEN l_kff_seg_start
788 AND l_kff_seg_end THEN
789 build_sql(sqlstr,
790 l_cntr_sql,
791 'pay_payroll_xml_extract_pkg.load_xml(''D'', ''Segment'
792 || (cntr_flex_col - l_kff_seg_start + 1) ||
793 ''', LTRIM(RTRIM(csr_get_archived_info_rec.action_information'
794 || cntr_flex_col ||')));');
795 ELSE
796
797 /*bug:9488426: Added code to feed the check number action_information4
798 and account_number field action_information7 masked for Employee Third party payments*/
799
800 IF csr_get_archived_regions_rec.action_information_category =
801 'EMPLOYEE THIRD PARTY PAYMENTS'
802 AND pay_payroll_xml_extract_pkg.g_leg_code = 'US' THEN
803 build_sql(sqlstr,
804 l_cntr_sql,
805 'csr_get_archived_info_rec.action_information4 :=
806 pay_us_employee_payslip_web.get_check_number(csr_get_archived_info_rec.action_information17
807 ,csr_get_archived_info_rec.action_information15);');
808
809 build_sql(sqlstr,
810 l_cntr_sql,
811 'csr_get_archived_info_rec.action_information7 :=
812 HR_GENERAL2.mask_characters(csr_get_archived_info_rec.action_information7);');
813 END IF;
814
815 build_sql(sqlstr,
816 l_cntr_sql,
817 'pay_payroll_xml_extract_pkg.load_xml(''D'', '''
818 || csr_get_archived_regions_rec.action_information_category ||
819 ''', ''ACTION_INFORMATION'|| cntr_flex_col ||
820 ''', LTRIM(RTRIM(csr_get_archived_info_rec.action_information'
821 || cntr_flex_col ||')));');
822 END IF;
823 END IF;
824 cntr_flex_col := cntr_flex_col + 1;
825 END LOOP;
826 /*Generate payroll details from time period id (action_information16)*/
827 IF csr_get_archived_regions_rec.action_information_category =
828 'EMPLOYEE DETAILS' THEN
829 build_sql(sqlstr,
830 l_cntr_sql,
831 'FOR csr_payroll_details_rec IN pay_payroll_xml_extract_pkg.csr_payroll_details(csr_get_archived_info_rec.action_information16) LOOP ');
832 build_sql(sqlstr,
833 l_cntr_sql,
834 'pay_payroll_xml_extract_pkg.load_xml(''D'', NULL, ''PAYROLL_NAME'', csr_payroll_details_rec.payroll_name);');
835 build_sql(sqlstr,
836 l_cntr_sql,
837 'pay_payroll_xml_extract_pkg.load_xml(''D'', NULL, ''PERIOD_TYPE'', csr_payroll_details_rec.period_type);');
838 build_sql(sqlstr,
839 l_cntr_sql,
840 'pay_payroll_xml_extract_pkg.load_xml(''D'', NULL, ''START_DATE'', csr_payroll_details_rec.start_date);');
841 build_sql(sqlstr,
842 l_cntr_sql,
843 'pay_payroll_xml_extract_pkg.load_xml(''D'', NULL, ''END_DATE'', csr_payroll_details_rec.end_date);');
844 build_sql(sqlstr,
845 l_cntr_sql,
846 'pay_payroll_xml_extract_pkg.load_xml(''D'', NULL, ''PAYMENT_DATE'', substr(fnd_date.date_to_canonical(csr_get_archived_info_rec.effective_date),1,10));');
847 build_sql(sqlstr,
848 l_cntr_sql,
849 'END LOOP;');
850 build_sql(sqlstr,
851 l_cntr_sql,
852 'l_org_id := csr_get_archived_info_rec.action_information2;');
853 END IF;
854
855 -- Localization procedure call
856 build_sql(sqlstr,
857 l_cntr_sql,
858 'BEGIN ');
859
860 IF g_install_leg_check = 'N' THEN
861
862 /* bug:13743223: get_ip_rules package is used to append legislative specific xml data
863 if the legislation belongs to International Payroll */
864
865 build_sql(sqlstr,
866 l_cntr_sql,
867 'EXECUTE IMMEDIATE (''BEGIN pay_'||
868 'ip'||
869 '_rules.add_custom_xml('||l_action_context_id||
870 ', '''''|| csr_get_archived_regions_rec.action_information_category ||
871 ''''', '''''|| p_document_type ||
872 '''''); END;'');');
873 ELSE
874
875 build_sql(sqlstr,
876 l_cntr_sql,
877 'EXECUTE IMMEDIATE (''BEGIN pay_'||
878 pay_payroll_xml_extract_pkg.g_leg_code||
879 '_rules.add_custom_xml('||l_action_context_id||
880 ', '''''|| csr_get_archived_regions_rec.action_information_category ||
881 ''''', '''''|| p_document_type ||
882 '''''); END;'');');
883 END IF;
884
885 build_sql(sqlstr,
886 l_cntr_sql,
887 'IF pay_payroll_xml_extract_pkg.g_custom_xml.count() > 0 THEN ');
888 build_sql(sqlstr,
889 l_cntr_sql,
890 'pay_payroll_xml_extract_pkg.load_xml(''<!-- Following segment(s) were added by PAY_'||
891 pay_payroll_xml_extract_pkg.g_leg_code||
892 '_RULES.ADD_CUSTOM_XML -->'');');
893 build_sql(sqlstr,
894 l_cntr_sql,
895 'FOR cntr IN pay_payroll_xml_extract_pkg.g_custom_xml.first()..pay_payroll_xml_extract_pkg.g_custom_xml.last() LOOP ');
896 build_sql(sqlstr,
897 l_cntr_sql,
898 'pay_payroll_xml_extract_pkg.load_xml(pay_payroll_xml_extract_pkg.g_custom_xml(cntr));');
899 build_sql(sqlstr,
900 l_cntr_sql,
901 'END LOOP;');
902 build_sql(sqlstr,
903 l_cntr_sql,
904 'pay_payroll_xml_extract_pkg.g_custom_xml.delete();');
905 build_sql(sqlstr,
906 l_cntr_sql,
907 'END IF;');
908 build_sql(sqlstr,
909 l_cntr_sql,
910 'EXCEPTION ');
911 build_sql(sqlstr,
912 l_cntr_sql,
913 'WHEN OTHERS THEN NULL;');
914 build_sql(sqlstr,
915 l_cntr_sql,
916 'END;');
917
918 -- Custom procedure call
919 build_sql(sqlstr,
920 l_cntr_sql,
921 'BEGIN ');
922 build_sql(sqlstr,
923 l_cntr_sql,
924 'EXECUTE IMMEDIATE (''BEGIN '||p_custom_xml_procedure||
925 '('||l_action_context_id||
926 ', '''''||
927 csr_get_archived_regions_rec.action_information_category ||
928 ''''', '''''|| p_document_type ||
929 '''''); END;'');');
930 build_sql(sqlstr,
931 l_cntr_sql,
932 'IF pay_payroll_xml_extract_pkg.g_custom_xml.count() > 0 THEN ');
933 build_sql(sqlstr,
934 l_cntr_sql,
935 'pay_payroll_xml_extract_pkg.load_xml(''<!-- Following segment(s) were added by '||
936 UPPER(p_custom_xml_procedure)||' -->'');');
937 build_sql(sqlstr,
938 l_cntr_sql,
939 'FOR cntr IN pay_payroll_xml_extract_pkg.g_custom_xml.first()..pay_payroll_xml_extract_pkg.g_custom_xml.last() LOOP ');
940 build_sql(sqlstr,
941 l_cntr_sql,
942 'pay_payroll_xml_extract_pkg.load_xml(pay_payroll_xml_extract_pkg.g_custom_xml(cntr));');
943 build_sql(sqlstr,
944 l_cntr_sql,
945 'END LOOP;');
946 build_sql(sqlstr,
947 l_cntr_sql,
948 'pay_payroll_xml_extract_pkg.g_custom_xml.delete();');
949 build_sql(sqlstr,
950 l_cntr_sql,
951 'END IF;');
952 build_sql(sqlstr,
953 l_cntr_sql,
954 'EXCEPTION ');
955 build_sql(sqlstr,
956 l_cntr_sql,
957 'WHEN OTHERS THEN NULL;');
958 build_sql(sqlstr,
959 l_cntr_sql,
960 'END;');
961
962 build_sql(sqlstr,
963 l_cntr_sql,
964 'pay_payroll_xml_extract_pkg.load_xml(''CE'', NULL, ''' ||
965 csr_get_archived_regions_rec.action_information_category ||
966 ''', NULL);');
967 build_sql(sqlstr,
968 l_cntr_sql,
969 'END LOOP;');
970 END LOOP;
971
972 -- Localization procedure call
973 build_sql(sqlstr,
974 l_cntr_sql,
975 'BEGIN ');
976 /* bug:13743223: get_ip_rules package is used to append legislative specific xml data
977 if the legislation belongs to International Payroll */
978 IF g_install_leg_check = 'N' THEN
979
980 build_sql(sqlstr,
981 l_cntr_sql,
982 'EXECUTE IMMEDIATE (''BEGIN pay_'||
983 'ip'||
984 '_rules.add_custom_xml('||l_action_context_id||', NULL'||
985 ', '''''|| p_document_type ||
986 '''''); END;'');');
987 ELSE
988
989 build_sql(sqlstr,
990 l_cntr_sql,
991 'EXECUTE IMMEDIATE (''BEGIN pay_'||
992 pay_payroll_xml_extract_pkg.g_leg_code||
993 '_rules.add_custom_xml('||l_action_context_id||', NULL'||
994 ', '''''|| p_document_type ||
995 '''''); END;'');');
996 END IF;
997 build_sql(sqlstr,
998 l_cntr_sql,
999 'IF pay_payroll_xml_extract_pkg.g_custom_xml.count() > 0 THEN ');
1000 build_sql(sqlstr,
1001 l_cntr_sql,
1002 'pay_payroll_xml_extract_pkg.load_xml(''<!-- Following context(s) were added by PAY_'||
1003 pay_payroll_xml_extract_pkg.g_leg_code||
1004 '_RULES.ADD_CUSTOM_XML -->'');');
1005 build_sql(sqlstr,
1006 l_cntr_sql,
1007 'FOR cntr IN pay_payroll_xml_extract_pkg.g_custom_xml.first()..pay_payroll_xml_extract_pkg.g_custom_xml.last() LOOP ');
1008 build_sql(sqlstr,
1009 l_cntr_sql,
1010 'pay_payroll_xml_extract_pkg.load_xml(pay_payroll_xml_extract_pkg.g_custom_xml(cntr));');
1011 build_sql(sqlstr,
1012 l_cntr_sql,
1013 'END LOOP;');
1014 build_sql(sqlstr,
1015 l_cntr_sql,
1016 'pay_payroll_xml_extract_pkg.g_custom_xml.delete();');
1017 build_sql(sqlstr,
1018 l_cntr_sql,
1019 'END IF;');
1020 build_sql(sqlstr,
1021 l_cntr_sql,
1022 'EXCEPTION ');
1023 build_sql(sqlstr,
1024 l_cntr_sql,
1025 'WHEN OTHERS THEN NULL;');
1026 build_sql(sqlstr,
1027 l_cntr_sql,
1028 'END;');
1029
1030 -- Custom procedure call
1031 build_sql(sqlstr,
1032 l_cntr_sql,
1033 'BEGIN ');
1034 build_sql(sqlstr,
1035 l_cntr_sql,
1036 'EXECUTE IMMEDIATE (''BEGIN '||p_custom_xml_procedure||
1037 '('||l_action_context_id||', NULL'||
1038 ', '''''|| p_document_type ||
1039 '''''); END;'');');
1040 build_sql(sqlstr,
1041 l_cntr_sql,
1042 'IF pay_payroll_xml_extract_pkg.g_custom_xml.count() > 0 THEN ');
1043 build_sql(sqlstr,
1044 l_cntr_sql,
1045 'pay_payroll_xml_extract_pkg.load_xml(''<!-- Following segment(s) were added by '||
1046 UPPER(p_custom_xml_procedure)||' -->'');');
1047 build_sql(sqlstr,
1048 l_cntr_sql,
1049 'FOR cntr IN pay_payroll_xml_extract_pkg.g_custom_xml.first()..pay_payroll_xml_extract_pkg.g_custom_xml.last() LOOP ');
1050 build_sql(sqlstr,
1051 l_cntr_sql,
1052 'pay_payroll_xml_extract_pkg.load_xml(pay_payroll_xml_extract_pkg.g_custom_xml(cntr));');
1053 build_sql(sqlstr,
1054 l_cntr_sql,
1055 'END LOOP;');
1056 build_sql(sqlstr,
1057 l_cntr_sql,
1058 'pay_payroll_xml_extract_pkg.g_custom_xml.delete();');
1059 build_sql(sqlstr,
1060 l_cntr_sql,
1061 'END IF;');
1062 build_sql(sqlstr,
1063 l_cntr_sql,
1064 'EXCEPTION ');
1065 build_sql(sqlstr,
1066 l_cntr_sql,
1067 'WHEN OTHERS THEN NULL;');
1068 build_sql(sqlstr,
1069 l_cntr_sql,
1070 'END;');
1071
1072 IF p_root_tag IS NOT NULL THEN
1073 build_sql(sqlstr,
1074 l_cntr_sql,
1075 'pay_payroll_xml_extract_pkg.load_xml(''CE'', NULL, '''||
1076 p_root_tag||''', NULL);');
1077 END IF;
1078
1079 build_sql(sqlstr,
1080 l_cntr_sql,
1081 'null;');
1082 build_sql(sqlstr,
1083 l_cntr_sql,
1084 'end;');
1085
1086 FOR cntr IN sqlstr.first()..sqlstr.last() LOOP
1087 hr_utility_trace(sqlstr(cntr));
1088 END LOOP;
1089
1090 csr := dbms_sql.open_cursor;
1091 dbms_sql.parse (csr,
1092 sqlstr,
1093 sqlstr.first(),
1094 sqlstr.last(),
1095 false,
1096 dbms_sql.v7);
1097 dbms_sql.bind_variable (csr,
1098 ':l_action_context_id',
1099 p_action_context_id);
1100 dbms_sql.bind_variable (csr,
1101 ':l_action_information_id',
1102 p_action_information_id);
1103 ret := dbms_sql.execute(csr);
1104 dbms_sql.close_cursor(csr);
1105
1106 IF g_xml_table.count() <> 0 THEN
1107 dbms_lob.createTemporary(l_xml, true, dbms_lob.session);
1108 FOR cntr IN g_xml_table.first()..g_xml_table.last() LOOP
1109 lr_xml := utl_raw.cast_to_raw(g_xml_table(cntr));
1110 ln_amt := utl_raw.length(lr_xml);
1111
1112 dbms_lob.writeAppend(l_xml,
1113 ln_amt,
1114 lr_xml);
1115
1116 hr_utility_trace (g_xml_table(cntr));
1117 END LOOP;
1118 p_xml := l_xml;
1119 dbms_lob.freeTemporary(l_xml);
1120 END IF;
1121
1122 print_blob (p_xml);
1123
1124 -- Unset globals before exit;
1125 g_xml_table.delete();
1126 g_custom_context := NULL;
1127
1128 hr_utility_trace ('Leaving '||l_proc_name);
1129 END GENERATE_INTERNAL;
1130
1131
1132 /****************************************************************************
1133 Name : GENERATE
1134 Description : This procedure interprets archived information, converts it to
1135 XML and prints it out to a BLOB. This is a public procedure
1136 and is based on action_context_IDs passed by the calling
1137 process. All archived records belonging to passed
1138 action_context_id will be converted to XML. Currently, online
1139 payslip and MX Pay Advice invoke this overloaded version.
1140 *****************************************************************************/
1141
1142 PROCEDURE GENERATE
1143 (
1144 P_ACTION_CONTEXT_ID number,
1145 P_CUSTOM_XML_PROCEDURE varchar2,
1146 P_GENERATE_HEADER_FLAG varchar2, -- {Y/N}
1147 P_ROOT_TAG varchar2,
1148 P_DOCUMENT_TYPE varchar2,
1149 P_XML OUT NOCOPY BLOB
1150 ) AS
1151 l_proc_name varchar2(100);
1152 lb_header_flag boolean;
1153 BEGIN
1154 l_proc_name := g_proc_name || 'GENERATE';
1155 hr_utility_trace ('Entering '||l_proc_name);
1156
1157 IF p_generate_header_flag = 'Y' THEN
1158 lb_header_flag := TRUE;
1159 ELSE
1160 lb_header_flag := FALSE;
1161 END IF;
1162
1163 generate_internal(
1164 NULL,
1165 p_action_context_id,
1166 NULL,
1167 p_custom_xml_procedure,
1168 lb_header_flag,
1169 p_root_tag,
1170 p_document_type,
1171 p_xml);
1172
1173 hr_utility_trace ('Leaving '||l_proc_name);
1174 END GENERATE;
1175
1176
1177 /****************************************************************************
1178 Name : GENERATE
1179 Description : This procedure interprets archived information, converts it to
1180 XML and prints it out to a BLOB. This is a public procedure
1181 and is driven off action_information_IDs set by the calling
1182 process.
1183
1184 It also accepts a custom XML tag parameter, which if passed a
1185 non-null value, will be used as parent enclosing tag of each
1186 action_information_id irrespective of the actual action
1187 information category.
1188
1189 Currently, MX SUA process invokes this overloaded version.
1190 *****************************************************************************/
1191
1192 PROCEDURE GENERATE
1193 (
1194 P_ACTION_INF_ID_TAB int_tab_type,
1195 P_CUSTOM_ACTION_INFO_CAT varchar2,
1196 P_DOCUMENT_TYPE varchar2,
1197 P_XML OUT NOCOPY BLOB
1198 ) AS
1199
1200 l_xml BLOB;
1201 l_xml_temp BLOB;
1202 l_proc_name varchar2(100);
1203 lr_buf RAW (2000);
1204 l_last_param number;
1205 BEGIN
1206 l_proc_name := g_proc_name || 'GENERATE-2';
1207 hr_utility_trace ('Entering '||l_proc_name);
1208 hr_utility_trace ('Total action_information_IDs = '||
1209 p_action_inf_id_tab.count());
1210
1211 IF p_action_inf_id_tab.count() > 0 THEN
1212 FOR cntr_arch_rec IN
1213 p_action_inf_id_tab.first()..p_action_inf_id_tab.last()
1214 LOOP
1215 hr_utility_trace(p_action_inf_id_tab (cntr_arch_rec));
1216 END LOOP;
1217 END IF;
1218
1219 IF p_action_inf_id_tab.count() > 0 THEN
1220 dbms_lob.createTemporary (l_xml,
1221 TRUE,
1222 dbms_lob.SESSION);
1223 FOR cntr_arch_rec IN
1224 p_action_inf_id_tab.first()..p_action_inf_id_tab.last()
1225 LOOP
1226 l_last_param := pay_payroll_xml_extract_pkg.g_custom_params.last();
1227 IF l_last_param IS NULL THEN
1228 l_last_param := 0;
1229 END IF;
1230
1231 pay_payroll_xml_extract_pkg.g_custom_params(
1232 l_last_param + 1).parameter_name := 'action_information_id';
1233
1234 pay_payroll_xml_extract_pkg.g_custom_params(
1235 l_last_param + 1).parameter_value :=
1236 p_action_inf_id_tab(cntr_arch_rec);
1237
1238 generate_internal(
1239 p_action_inf_id_tab(cntr_arch_rec),
1240 NULL,
1241 p_custom_action_info_cat,
1242 NULL,
1243 FALSE,
1244 NULL,
1245 p_document_type,
1246 l_xml_temp);
1247
1248 dbms_lob.append(l_xml,
1249 l_xml_temp);
1250
1251 /* Remove the parameter 'action_information_id' before the next
1252 iteration (or returning to the calling program). Calling
1253 application is expected to clear this cache to avoid a
1254 possibility of stale parameter values in subsequent calls.*/
1255 pay_payroll_xml_extract_pkg.g_custom_params.delete(
1256 pay_payroll_xml_extract_pkg.g_custom_params.last());
1257 END LOOP;
1258 p_xml := l_xml;
1259 dbms_lob.freeTemporary(l_xml);
1260 END IF;
1261
1262 hr_utility_trace ('Leaving '||l_proc_name);
1263
1264 -- Bug 6068599
1265 EXCEPTION
1266 WHEN OTHERS THEN
1267 /* Remove the parameter 'action_information_id' in case of error. */
1268 pay_payroll_xml_extract_pkg.g_custom_params.delete(
1269 pay_payroll_xml_extract_pkg.g_custom_params.last());
1270 hr_utility_trace (sqlerrm);
1271 RAISE;
1272 END;
1273
1274
1275
1276 --13029999 Procedure to generate XML for Third Party Cheques start
1277
1278 PROCEDURE GENERATE_THIRD_PARTY
1279 (
1280 P_ACTION_INFORMATION_ID number,
1281 P_ACTION_CONTEXT_ID number,
1282 P_PRE_PAYMENT_ID number,
1283 P_CUSTOM_ACTION_INFO_CAT varchar2,
1284 P_CUSTOM_XML_PROCEDURE varchar2,
1285 P_GENERATE_HEADER_FLAG boolean,
1286 P_ROOT_TAG varchar2,
1287 P_DOCUMENT_TYPE varchar2,
1288 P_XML OUT NOCOPY BLOB
1289 ) AS
1290
1291 CURSOR get_leg_code IS
1292 SELECT hoi2.org_information9
1293 FROM pay_assignment_actions paa,
1294 pay_payroll_actions ppa,
1295 hr_organization_units hou,
1296 hr_organization_information hoi1,
1297 hr_organization_information hoi2
1298 WHERE paa.payroll_action_id = ppa.payroll_action_id
1299 AND ppa.business_group_id = hou.organization_id
1300 AND hou.organization_id = hoi1.organization_id
1301 AND hoi1.organization_id = hoi2.organization_id
1302 AND ppa.effective_date BETWEEN hou.date_from
1303 AND nvl(hou.date_to,
1304 hr_general.end_of_time)
1305 AND hoi1.org_information_context = 'CLASS'
1306 AND hoi1.org_information1 = 'HR_BG'
1307 AND hoi2.org_information_context = 'Business Group Information'
1308 AND ppa.action_type = 'X'
1309 AND NVL (p_action_context_id, (SELECT action_context_id
1310 FROM pay_action_information
1311 WHERE action_information_id =
1312 p_action_information_id))
1313 = paa.assignment_action_id;
1314
1315 CURSOR csr_get_archived_regions IS
1316 SELECT DISTINCT action_information_category
1317 FROM pay_action_information
1318 WHERE ((action_context_type = 'AAP'
1319 AND action_context_id = p_action_context_id)
1320 OR (action_context_type = 'PA'
1321 AND action_context_id =
1322 (SELECT payroll_action_id
1323 FROM pay_assignment_actions
1324 WHERE assignment_action_id = p_action_context_id)))
1325 OR (action_information_id = p_action_information_id
1326 AND p_action_information_id IS NOT NULL)
1327 ORDER BY decode (action_information_category,'EMPLOYEE DETAILS', 1, 2);
1328 /* NOTE - This ORDER BY clause will make sure that EMPLOYEE DETAILS gets
1329 processed before all other action information categories so
1330 that we have the organization_id (action_information2) for
1331 filtering undesired ADDRESS DETAILS archived at payroll action
1332 level (action_context_type = 'PA') by the payroll archiver.*/
1333
1334 l_proc_name varchar2(100);
1335 sqlstr dbms_sql.varchar2s;
1336 l_cntr_sql number;
1337 l_xml BLOB;
1338 csr number;
1339 ret number;
1340 cntr_flex_col number;
1341 l_flex_col_num number; /* Max. number of flex segments in Action
1342 Informtion DF */
1343
1344 l_action_information_id varchar2(100);
1345 l_action_context_id varchar2(100);
1346 l_pre_payment_id varchar2(100);
1347 lr_xml RAW (32767);
1348 ln_amt number;
1349
1350 BEGIN
1351 l_proc_name := g_proc_name || 'GENERATE_THIRD_PARTY';
1352 hr_utility_trace ('Entering '||l_proc_name);
1353 hr_utility_trace ('Parameters ....');
1354 hr_utility_trace ('P_ACTION_INFORMATION_ID ='||P_ACTION_INFORMATION_ID);
1355 hr_utility_trace ('P_ACTION_CONTEXT_ID ='||P_ACTION_CONTEXT_ID);
1356 IF p_generate_header_flag THEN
1357 hr_utility_trace ('P_GENERATE_HEADER_FLAG = TRUE');
1358 ELSE
1359 hr_utility_trace ('P_GENERATE_HEADER_FLAG = FALSE');
1360 END IF;
1361 hr_utility_trace ('P_CUSTOM_ACTION_INFO_CAT ='||P_CUSTOM_ACTION_INFO_CAT);
1362
1363 g_xml_table.delete();
1364 l_flex_col_num := 30;
1365 l_cntr_sql := 1;
1366 g_action_ctx_id := p_action_context_id;
1367 g_custom_context := TRANSLATE(p_custom_action_info_cat, ' /', '__');
1368
1369 SELECT DECODE (p_action_information_id,
1370 NULL, 'NULL',
1371 to_char(p_action_information_id)),
1372 DECODE (p_action_context_id,
1373 NULL, 'NULL',
1374 to_char(p_action_context_id)),
1375 DECODE (p_pre_payment_id,
1376 NULL, 'NULL',
1377 to_char(p_pre_payment_id))
1378 INTO l_action_information_id,
1379 l_action_context_id,
1380 l_pre_payment_id
1381 FROM DUAL;
1382
1383 build_sql(sqlstr, l_cntr_sql, 'begin ');
1384
1385 if p_generate_header_flag then
1386 hr_utility_trace('p_generate_header_flag is true. p_root_tag is '||p_root_tag);
1387 else
1388 hr_utility_trace('p_generate_header_flag is false. p_root_tag is '||p_root_tag);
1389 end if;
1390
1391 hr_utility_trace('l_action_information_id is '||l_action_information_id);
1392 hr_utility_trace('l_action_context_id '||l_action_context_id);
1393
1394 IF p_generate_header_flag THEN
1395 build_sql(sqlstr,
1396 l_cntr_sql,
1397 'pay_payroll_xml_extract_pkg.load_xml(''CS'', NULL, ''?xml version="1.0" encoding="'||
1398 hr_mx_utility.get_IANA_charset||'"?'', NULL);');
1399 END IF;
1400 IF p_root_tag IS NOT NULL THEN
1401 build_sql(sqlstr,
1402 l_cntr_sql,
1403 'pay_payroll_xml_extract_pkg.load_xml(''CS'', NULL, '''||
1404 p_root_tag||''', NULL);');
1405 END IF;
1406
1407 /* Fetch legislation_code. */
1408 OPEN get_leg_code;
1409 FETCH get_leg_code INTO pay_payroll_xml_extract_pkg.g_leg_code;
1410 CLOSE get_leg_code;
1411 FOR csr_get_archived_regions_rec IN csr_get_archived_regions LOOP
1412 hr_utility_trace('csr_get_archived_regions_rec = '||csr_get_archived_regions_rec.action_information_category);
1413 hr_utility_trace('pay_payroll_xml_extract_pkg.g_leg_code = '||pay_payroll_xml_extract_pkg.g_leg_code);
1414
1415 IF (csr_get_archived_regions_rec.action_information_category LIKE pay_payroll_xml_extract_pkg.g_leg_code||'%THIRD PARTY CHECKS' OR
1416 csr_get_archived_regions_rec.action_information_category LIKE pay_payroll_xml_extract_pkg.g_leg_code||'%THIRD PARTY CHEQUES') THEN
1417 build_sql(sqlstr,
1418 l_cntr_sql,
1419 'FOR csr_get_archived_info_rec IN pay_payroll_xml_extract_pkg.csr_get_thd_pty_arch_info (:l_action_context_id,'''||
1420 csr_get_archived_regions_rec.action_information_category
1421 ||''', :l_pre_payment_id) LOOP ');
1422 cntr_flex_col := 1;
1423 LOOP
1424 hr_utility_trace('Step 4');
1425 EXIT WHEN cntr_flex_col > l_flex_col_num;
1426 IF flex_seg_enabled (
1427 csr_get_archived_regions_rec.action_information_category,
1428 'ACTION_INFORMATION'||cntr_flex_col) THEN
1429 build_sql(sqlstr,
1430 l_cntr_sql,
1431 'pay_payroll_xml_extract_pkg.load_xml(''D'', '''
1432 || csr_get_archived_regions_rec.action_information_category ||
1433 ''', ''ACTION_INFORMATION'|| cntr_flex_col ||
1434 ''', LTRIM(RTRIM(csr_get_archived_info_rec.action_information'
1435 || cntr_flex_col ||')));');
1436 END IF;
1437 cntr_flex_col := cntr_flex_col + 1;
1438 END LOOP;
1439
1440
1441 -- Localization procedure call
1442 build_sql(sqlstr,
1443 l_cntr_sql,
1444 'BEGIN ');
1445 build_sql(sqlstr,
1446 l_cntr_sql,
1447 'EXECUTE IMMEDIATE (''BEGIN pay_'||
1448 pay_payroll_xml_extract_pkg.g_leg_code||
1449 '_rules.add_custom_xml('||l_action_context_id||
1450 ', '''''|| csr_get_archived_regions_rec.action_information_category ||
1451 ''''', '''''|| p_document_type ||
1452 '''''); END;'');');
1453 build_sql(sqlstr,
1454 l_cntr_sql,
1455 'IF pay_payroll_xml_extract_pkg.g_custom_xml.count() > 0 THEN ');
1456 build_sql(sqlstr,
1457 l_cntr_sql,
1458 'pay_payroll_xml_extract_pkg.load_xml(''<!-- Following segment(s) were added by PAY_'||
1459 pay_payroll_xml_extract_pkg.g_leg_code||
1460 '_RULES.ADD_CUSTOM_XML -->'');');
1461 build_sql(sqlstr,
1462 l_cntr_sql,
1463 'FOR cntr IN pay_payroll_xml_extract_pkg.g_custom_xml.first()..pay_payroll_xml_extract_pkg.g_custom_xml.last() LOOP ');
1464 build_sql(sqlstr,
1465 l_cntr_sql,
1466 'pay_payroll_xml_extract_pkg.load_xml(pay_payroll_xml_extract_pkg.g_custom_xml(cntr));');
1467 build_sql(sqlstr,
1468 l_cntr_sql,
1469 'END LOOP;');
1470 build_sql(sqlstr,
1471 l_cntr_sql,
1472 'pay_payroll_xml_extract_pkg.g_custom_xml.delete();');
1473 build_sql(sqlstr,
1474 l_cntr_sql,
1475 'END IF;');
1476 build_sql(sqlstr,
1477 l_cntr_sql,
1478 'EXCEPTION ');
1479 build_sql(sqlstr,
1480 l_cntr_sql,
1481 'WHEN OTHERS THEN NULL;');
1482 build_sql(sqlstr,
1483 l_cntr_sql,
1484 'END;');
1485
1486 -- Custom procedure call
1487 build_sql(sqlstr,
1488 l_cntr_sql,
1489 'BEGIN ');
1490 build_sql(sqlstr,
1491 l_cntr_sql,
1492 'EXECUTE IMMEDIATE (''BEGIN '||p_custom_xml_procedure||
1493 '('||l_action_context_id||
1494 ', '''''||
1495 csr_get_archived_regions_rec.action_information_category ||
1496 ''''', '''''|| p_document_type ||
1497 '''''); END;'');');
1498 build_sql(sqlstr,
1499 l_cntr_sql,
1500 'IF pay_payroll_xml_extract_pkg.g_custom_xml.count() > 0 THEN ');
1501 build_sql(sqlstr,
1502 l_cntr_sql,
1503 'pay_payroll_xml_extract_pkg.load_xml(''<!-- Following segment(s) were added by '||
1504 UPPER(p_custom_xml_procedure)||' -->'');');
1505 build_sql(sqlstr,
1506 l_cntr_sql,
1507 'FOR cntr IN pay_payroll_xml_extract_pkg.g_custom_xml.first()..pay_payroll_xml_extract_pkg.g_custom_xml.last() LOOP ');
1508 build_sql(sqlstr,
1509 l_cntr_sql,
1510 'pay_payroll_xml_extract_pkg.load_xml(pay_payroll_xml_extract_pkg.g_custom_xml(cntr));');
1511 build_sql(sqlstr,
1512 l_cntr_sql,
1513 'END LOOP;');
1514 build_sql(sqlstr,
1515 l_cntr_sql,
1516 'pay_payroll_xml_extract_pkg.g_custom_xml.delete();');
1517 build_sql(sqlstr,
1518 l_cntr_sql,
1519 'END IF;');
1520 build_sql(sqlstr,
1521 l_cntr_sql,
1522 'EXCEPTION ');
1523 build_sql(sqlstr,
1524 l_cntr_sql,
1525 'WHEN OTHERS THEN NULL;');
1526 build_sql(sqlstr,
1527 l_cntr_sql,
1528 'END;');
1529
1530 build_sql(sqlstr,
1531 l_cntr_sql,
1532 'END LOOP;');
1533 END IF;
1534 END LOOP;
1535
1536 -- Localization procedure call
1537 build_sql(sqlstr,
1538 l_cntr_sql,
1539 'BEGIN ');
1540 build_sql(sqlstr,
1541 l_cntr_sql,
1542 'EXECUTE IMMEDIATE (''BEGIN pay_'||
1543 pay_payroll_xml_extract_pkg.g_leg_code||
1544 '_rules.add_custom_xml('||l_action_context_id||', NULL'||
1545 ', '''''|| p_document_type ||
1546 '''''); END;'');');
1547 build_sql(sqlstr,
1548 l_cntr_sql,
1549 'IF pay_payroll_xml_extract_pkg.g_custom_xml.count() > 0 THEN ');
1550 build_sql(sqlstr,
1551 l_cntr_sql,
1552 'pay_payroll_xml_extract_pkg.load_xml(''<!-- Following context(s) were added by PAY_'||
1553 pay_payroll_xml_extract_pkg.g_leg_code||
1554 '_RULES.ADD_CUSTOM_XML -->'');');
1555 build_sql(sqlstr,
1556 l_cntr_sql,
1557 'FOR cntr IN pay_payroll_xml_extract_pkg.g_custom_xml.first()..pay_payroll_xml_extract_pkg.g_custom_xml.last() LOOP ');
1558 build_sql(sqlstr,
1559 l_cntr_sql,
1560 'pay_payroll_xml_extract_pkg.load_xml(pay_payroll_xml_extract_pkg.g_custom_xml(cntr));');
1561 build_sql(sqlstr,
1562 l_cntr_sql,
1563 'END LOOP;');
1564 build_sql(sqlstr,
1565 l_cntr_sql,
1566 'pay_payroll_xml_extract_pkg.g_custom_xml.delete();');
1567 build_sql(sqlstr,
1568 l_cntr_sql,
1569 'END IF;');
1570 build_sql(sqlstr,
1571 l_cntr_sql,
1572 'EXCEPTION ');
1573 build_sql(sqlstr,
1574 l_cntr_sql,
1575 'WHEN OTHERS THEN NULL;');
1576 build_sql(sqlstr,
1577 l_cntr_sql,
1578 'END;');
1579
1580 -- Custom procedure call
1581 build_sql(sqlstr,
1582 l_cntr_sql,
1583 'BEGIN ');
1584 build_sql(sqlstr,
1585 l_cntr_sql,
1586 'EXECUTE IMMEDIATE (''BEGIN '||p_custom_xml_procedure||
1587 '('||l_action_context_id||', NULL'||
1588 ', '''''|| p_document_type ||
1589 '''''); END;'');');
1590 build_sql(sqlstr,
1591 l_cntr_sql,
1592 'IF pay_payroll_xml_extract_pkg.g_custom_xml.count() > 0 THEN ');
1593 build_sql(sqlstr,
1594 l_cntr_sql,
1595 'pay_payroll_xml_extract_pkg.load_xml(''<!-- Following segment(s) were added by '||
1596 UPPER(p_custom_xml_procedure)||' -->'');');
1597 build_sql(sqlstr,
1598 l_cntr_sql,
1599 'FOR cntr IN pay_payroll_xml_extract_pkg.g_custom_xml.first()..pay_payroll_xml_extract_pkg.g_custom_xml.last() LOOP ');
1600 build_sql(sqlstr,
1601 l_cntr_sql,
1602 'pay_payroll_xml_extract_pkg.load_xml(pay_payroll_xml_extract_pkg.g_custom_xml(cntr));');
1603 build_sql(sqlstr,
1604 l_cntr_sql,
1605 'END LOOP;');
1606 build_sql(sqlstr,
1607 l_cntr_sql,
1608 'pay_payroll_xml_extract_pkg.g_custom_xml.delete();');
1609 build_sql(sqlstr,
1610 l_cntr_sql,
1611 'END IF;');
1612 build_sql(sqlstr,
1613 l_cntr_sql,
1614 'EXCEPTION ');
1615 build_sql(sqlstr,
1616 l_cntr_sql,
1617 'WHEN OTHERS THEN NULL;');
1618 build_sql(sqlstr,
1619 l_cntr_sql,
1620 'END;');
1621
1622 IF p_root_tag IS NOT NULL THEN
1623 build_sql(sqlstr,
1624 l_cntr_sql,
1625 'pay_payroll_xml_extract_pkg.load_xml(''CE'', NULL, '''||
1626 p_root_tag||''', NULL);');
1627 END IF;
1628
1629 build_sql(sqlstr,
1630 l_cntr_sql,
1631 'null;');
1632 build_sql(sqlstr,
1633 l_cntr_sql,
1634 'end;');
1635
1636 hr_utility_trace ('The dyn SQL start:');
1637 FOR cntr IN sqlstr.first()..sqlstr.last() LOOP
1638 hr_utility_trace(sqlstr(cntr));
1639 END LOOP;
1640 hr_utility_trace ('The dyn SQL ends.');
1641
1642 csr := dbms_sql.open_cursor;
1643 dbms_sql.parse (csr,
1644 sqlstr,
1645 sqlstr.first(),
1646 sqlstr.last(),
1647 false,
1648 dbms_sql.v7);
1649
1650 begin
1651 dbms_sql.bind_variable (csr,
1652 ':l_action_context_id',
1653 p_action_context_id);
1654 dbms_sql.bind_variable (csr,
1655 ':l_pre_payment_id',
1656 p_pre_payment_id);
1657 exception when others then hr_utility_trace('exception in binding variables, procedure generate third party.'); end;
1658 ret := dbms_sql.execute(csr);
1659 dbms_sql.close_cursor(csr);
1660
1661 IF g_xml_table.count() <> 0 THEN
1662 dbms_lob.createTemporary(l_xml, true, dbms_lob.session);
1663 FOR cntr IN g_xml_table.first()..g_xml_table.last() LOOP
1664 lr_xml := utl_raw.cast_to_raw(g_xml_table(cntr));
1665 ln_amt := utl_raw.length(lr_xml);
1666
1667 dbms_lob.writeAppend(l_xml,
1668 ln_amt,
1669 lr_xml);
1670
1671 hr_utility_trace (g_xml_table(cntr));
1672 END LOOP;
1673 p_xml := l_xml;
1674 dbms_lob.freeTemporary(l_xml);
1675 END IF;
1676
1677 print_blob (p_xml);
1678
1679 -- Unset globals before exit;
1680 g_xml_table.delete();
1681 g_custom_context := NULL;
1682
1683 hr_utility_trace ('Leaving '||l_proc_name);
1684 END GENERATE_THIRD_PARTY;
1685
1686 --13029999 procedure generate third party end.
1687
1688
1689
1690 BEGIN
1691 --hr_utility.trace_on (null, 'MX_IDC');
1692 g_proc_name := 'PAY_PAYROLL_XML_EXTRACT_PKG.';
1693 g_debug := hr_utility.debug_enabled;
1694 END PAY_PAYROLL_XML_EXTRACT_PKG;