[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.13.12010000.2 2008/08/22 10:34:18 ckesanap 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 *****************************************************************************/
91
92 g_proc_name varchar2(50);
93 g_debug boolean;
94 g_action_ctx_id number;
95 g_custom_context pay_action_information.action_information_category%type;
96
97
98 /****************************************************************************
99 Name : HR_UTILITY_TRACE
100 Description : This procedure prints debug messages during diagnostics mode.
101 *****************************************************************************/
102
103 PROCEDURE HR_UTILITY_TRACE(trc_data varchar2) IS
104 BEGIN
105 IF g_debug THEN
106 hr_utility.trace(trc_data);
107 END IF;
108 END HR_UTILITY_TRACE;
109
110
111 /****************************************************************************
112 Name : PRINT_BLOB
113 Description : This procedure prints contents of BLOB passed as parameter.
114 *****************************************************************************/
115
116 PROCEDURE PRINT_BLOB(p_blob BLOB) IS
117 BEGIN
118 IF g_debug THEN
119 pay_ac_utility.print_lob(p_blob);
120 END IF;
121 END PRINT_BLOB;
122
123 /****************************************************************************
124 Name : LOAD_XML_INTERNAL
125 Description : This procedure loads the global XML cache.
126 *****************************************************************************/
127 PROCEDURE LOAD_XML_INTERNAL (
128 P_NODE_TYPE varchar2,
129 P_NODE varchar2,
130 P_DATA varchar2
131 ) AS
132 l_proc_name varchar2(100);
133 l_data pay_action_information.action_information1%type;
134
135 BEGIN
136 l_proc_name := g_proc_name || 'LOAD_XML_INTERNAL';
137 hr_utility_trace ('Entering '||l_proc_name);
138
139 IF p_node_type = 'CS' THEN
140 load_xml ('<'||p_node||'>');
141 ELSIF p_node_type = 'CE' THEN
142 load_xml ('</'||p_node||'>');
143 ELSIF p_node_type = 'D' THEN
144 /* Handle special charaters in data */
145 l_data := REPLACE (p_data, '&', '&');
146 l_data := REPLACE (l_data, '>', '>');
147 l_data := REPLACE (l_data, '<', '<');
148 l_data := REPLACE (l_data, '''', ''');
149 l_data := REPLACE (l_data, '"', '"');
150 load_xml ('<'||p_node||'>'||l_data||'</'||p_node||'>');
151 END IF;
152
153 hr_utility_trace ('Leaving '||l_proc_name);
154 END LOAD_XML_INTERNAL;
155
156
157 /****************************************************************************
158 Name : LOAD_XML
159 Description : This procedure loads the global XML cache.
160 Parameters : P_NODE_TYPE This parameter can take one of these
161 values: -
162 1. CS - This signifies that string contained
163 in P_NODE parameter is start of
164 container node. P_DATA parameter is
165 ignored in this mode.
166 2. CE - This signifies that string
167 contained in P_NODE parameter is
168 end of container node. P_DATA
169 parameter is ignored in this mode.
170 3. D - This signifies that string
171 contained in P_NODE parameter is
172 data node and P_DATA carries actual
173 data to be contained by tag
174 specified by P_NODE parameter.
175
176 P_CONTEXT_CODE Context code of Action Information DF.
177
178 P_NODE Name of XML tag, or, application column
179 name of flex segment.
180
181 P_DATA Data to be contained by tag specified by
182 P_NODE parameter. P_DATA is not used unless
183 P_NODE_TYPE = D.
184 *****************************************************************************/
185
186 PROCEDURE LOAD_XML (
187 P_NODE_TYPE varchar2,
188 P_FLEXFIELD_NAME varchar2,
189 P_CONTEXT_CODE varchar2,
190 P_NODE varchar2,
191 P_DATA varchar2
192 ) AS
193
194 CURSOR csr_get_tag_name IS
195 SELECT TRANSLATE (UPPER(end_user_column_name), ' /','__') tag_name
196 FROM fnd_descr_flex_col_usage_vl
197 WHERE descriptive_flexfield_name = p_flexfield_name
198 AND descriptive_flex_context_code = p_context_code
199 AND application_column_name = UPPER (p_node)
200 AND application_id = 801; /* Bug 6522667 */
201
202 CURSOR csr_get_chk_no IS
203 SELECT paa_chk.serial_number
204 FROM pay_assignment_actions paa_xfr,
205 pay_action_interlocks pai_xfr,
206 pay_action_interlocks pai_chk,
207 pay_assignment_actions paa_chk,
208 pay_payroll_actions ppa_chk
209 WHERE paa_xfr.assignment_action_id = pai_xfr.locking_action_id
210 AND pai_xfr.locked_action_id = pai_chk.locked_action_id
211 AND pai_chk.locking_action_id = paa_chk.assignment_action_id
212 AND paa_chk.payroll_action_id = ppa_chk.payroll_action_id
213 AND ppa_chk.action_type = 'H'
214 AND paa_xfr.assignment_action_id = g_action_ctx_id;
215
216 l_proc_name varchar2(100);
217 l_tag_name varchar2(500);
218 l_chk_no pay_assignment_actions.serial_number%type;
219 l_data pay_action_information.action_information1%type;
220
221 BEGIN
222 l_proc_name := g_proc_name || 'LOAD_XML';
223 hr_utility_trace ('Entering '||l_proc_name);
224
225 IF p_node_type = 'D' THEN
226
227 /* Fetch segment names */
228 OPEN csr_get_tag_name;
229 FETCH csr_get_tag_name INTO l_tag_name;
230 CLOSE csr_get_tag_name;
231
232 /* Fetch cheque number */
233 IF p_flexfield_name = 'Action Information DF' AND
234 p_context_code = 'EMPLOYEE NET PAY DISTRIBUTION' AND
235 l_tag_name = 'CHECK_DEPOSIT_NUMBER' THEN
236 OPEN csr_get_chk_no;
237 FETCH csr_get_chk_no INTO l_chk_no;
238 CLOSE csr_get_chk_no;
239 END IF;
240 END IF;
241
242 IF UPPER(p_node) NOT LIKE '?XML%' AND UPPER(p_node) NOT LIKE 'XAPI%' THEN
243 l_tag_name := nvl(l_tag_name, TRANSLATE(p_node, ' /', '__'));
244 IF p_node_type IN ('CS', 'CE') THEN
245 l_tag_name := nvl(g_custom_context, TRANSLATE(p_node, ' /', '__'));
246 END IF;
247 ELSE
248 l_tag_name := p_node;
249 END IF;
250
251 l_data := nvl(l_chk_no, p_data);
252 load_xml_internal (p_node_type, l_tag_name, l_data);
253
254 hr_utility_trace ('Leaving '||l_proc_name);
255 END LOAD_XML;
256
257
258 /****************************************************************************
259 Name : LOAD_XML
260 Description : This procedure obtains segment title from the Action
261 Information DF. This is temporary, and is created only to
262 provide backward compatibility for payslip code. Once the
263 payslip processes are changed to pass the flexfield name,
264 this procedure can be removed.
265 *****************************************************************************/
266 PROCEDURE LOAD_XML (
267 P_NODE_TYPE varchar2,
268 P_CONTEXT_CODE varchar2,
269 P_NODE varchar2,
270 P_DATA varchar2
271 ) AS
272
273 l_proc_name varchar2(100);
274
275 BEGIN
276 l_proc_name := g_proc_name || 'LOAD_XML-4';
277 hr_utility_trace ('Entering '||l_proc_name);
278
279 load_xml(p_node_type => p_node_type,
280 p_flexfield_name => 'Action Information DF',
281 p_context_code => p_context_code,
282 p_node => p_node,
283 p_data => p_data);
284
285 hr_utility_trace ('Leaving '||l_proc_name);
286
287 END LOAD_XML;
288
289 /****************************************************************************
290 Name : LOAD_XML
291 Description : This procedure obtains segment title from the bank key
292 flexfield to be used as XML tag.
293 *****************************************************************************/
294 PROCEDURE LOAD_XML (
295 P_NODE_TYPE varchar2,
296 P_NODE varchar2,
297 P_DATA varchar2
298 ) AS
299
300 CURSOR csr_get_tag_name (p_id_flex_structure_code varchar2) IS
301 SELECT TRANSLATE (UPPER(seg.segment_name), ' /','__')
302 FROM fnd_id_flex_structures_vl ctx,
303 fnd_id_flex_segments_vl seg
304 WHERE ctx.id_flex_num = seg.id_flex_num
305 AND ctx.id_flex_code = seg.id_flex_code
306 AND seg.id_flex_code = 'BANK'
307 AND ctx.id_flex_structure_code = p_id_flex_structure_code
308 AND seg.application_column_name = UPPER(p_node);
309
310 l_proc_name varchar2(100);
311 l_tag_name varchar2(500);
312 l_struct_code fnd_id_flex_structures.id_flex_structure_code%type;
313
314 BEGIN
318 IF p_node_type = 'D' THEN
315 l_proc_name := g_proc_name || 'LOAD_XML-2';
316 hr_utility_trace ('Entering '||l_proc_name);
317
319 OPEN csr_get_tag_name (pay_payroll_xml_extract_pkg.g_leg_code||
320 '_BANK_DETAILS');
321 FETCH csr_get_tag_name INTO l_tag_name;
322 CLOSE csr_get_tag_name;
323 END IF;
324
325 IF UPPER(p_node) NOT LIKE '?XML%' AND UPPER(p_node) NOT LIKE 'XAPI%' THEN
326 l_tag_name := nvl(l_tag_name, TRANSLATE(p_node,' /', '__'));
327 IF p_node_type IN ('CS', 'CE') THEN
328 l_tag_name := nvl(g_custom_context, TRANSLATE(p_node, ' /', '__'));
329 END IF;
330 ELSE
331 l_tag_name := p_node;
332 END IF;
333
334 load_xml_internal (p_node_type, l_tag_name, p_data);
335
336 hr_utility_trace ('Leaving '||l_proc_name);
337 END LOAD_XML;
338
339 /****************************************************************************
340 Name : LOAD_XML
341 Description : This procedure accepts a well-formed XML and loads it into
342 global XML cache. Note that this procedure does not perform
343 any syntactical validations over passed XML data.
344 LOAD_XML_DATA should be used if such validations are required
345 to be performed implicitly.
346 *****************************************************************************/
347 PROCEDURE LOAD_XML (
348 P_XML pay_action_information.action_information1%type
349 ) AS
350
351 l_proc_name varchar2(100);
352
353 BEGIN
354 l_proc_name := g_proc_name || 'LOAD_XML-3';
355 hr_utility_trace ('Entering '||l_proc_name);
356
357 g_xml_table (g_xml_table.count() + 1) := p_xml;
358
359 hr_utility_trace ('Leaving '||l_proc_name);
360 END;
361
362 /****************************************************************************
363 Name : LOAD_XML_DATA
364 Description : This procedure accepts meta-data along with actual XML data
365 and loads the global XML cache. This is a public procedure
366 which performs basic validations to check well-formedness of
367 XML data before loading the cache. Please see parameter
368 description of public version of LOAD_XML to find what each
369 parameter signifies.
370 *****************************************************************************/
371 PROCEDURE LOAD_XML_DATA (
372 P_NODE_TYPE varchar2,
373 P_NODE varchar2,
374 P_DATA varchar2
375 ) AS
376 l_proc_name varchar2(100);
377 BEGIN
378 l_proc_name := g_proc_name || 'LOAD_XML_DATA';
379 hr_utility_trace ('Entering '||l_proc_name);
380
381 load_xml_internal (p_node_type,
382 p_node,
383 p_data);
384
385 hr_utility_trace ('Leaving '||l_proc_name);
386 END LOAD_XML_DATA;
387
388 /****************************************************************************
389 Name : FLEX_SEG_ENABLED
390 Description : This function returns TRUE if an application column is
391 registered with given context of Action Information DF.
392 Otherwise, it returns false.
393 *****************************************************************************/
394 FUNCTION FLEX_SEG_ENABLED
395 (
396 P_CONTEXT_CODE varchar2,
397 P_APPLICATION_COLUMN_NAME varchar2
398 ) RETURN BOOLEAN AS
399
400 CURSOR csr_seg_enabled IS
401 SELECT 'Y'
402 FROM fnd_descr_flex_col_usage_vl
403 WHERE descriptive_flexfield_name like 'Action Information DF'
404 AND descriptive_flex_context_code = p_context_code
405 AND application_column_name like p_application_column_name
406 AND application_id = 801 /* Bug 6522667 */
407 AND enabled_flag = 'Y';
408
409 l_proc_name varchar2(100);
410 l_exists varchar2(1);
411
412 BEGIN
413 l_proc_name := g_proc_name || 'FLEX_SEG_ENABLED';
414 hr_utility_trace ('Entering '||l_proc_name);
415
416 OPEN csr_seg_enabled;
417 FETCH csr_seg_enabled INTO l_exists;
418 CLOSE csr_seg_enabled;
419
420 hr_utility_trace ('Leaving '||l_proc_name);
421
422 IF l_exists = 'Y' THEN
423 RETURN (TRUE);
424 ELSE
425 RETURN (FALSE);
426 END IF;
427
428 END FLEX_SEG_ENABLED;
429
430
431 /****************************************************************************
432 Name : BUILD_SQL
433 Description : This procedure builds dynamic SQL string.
434 *****************************************************************************/
435
436 PROCEDURE BUILD_SQL
437 (
438 P_SQLSTR_TAB IN OUT NOCOPY dbms_sql.varchar2s,
439 P_CNTR IN OUT NOCOPY number,
440 P_STRING varchar2
441 ) AS
442 l_proc_name varchar2(100);
443 BEGIN
444 l_proc_name := g_proc_name || 'BUILD_SQL';
445 hr_utility_trace ('Entering '||l_proc_name);
446 p_sqlstr_tab(p_cntr) := p_string;
447 p_cntr := p_cntr + 1;
448 hr_utility_trace ('Leaving '||l_proc_name);
449 END;
450
451
455 XML and prints it to a BLOB. This is a private procedure.
452 /****************************************************************************
453 Name : GENERATE_INTERNAL
454 Description : This procedure interprets archived information, converts it to
456
457 IMP. NOTE: - This procedure can be invoked either by
458 action_information_id or action_context_id, one at a time. i.e
459 for any given call of this procedure, exactly one of these
460 parameters can be passed a NOT NULL value.
461 *****************************************************************************/
462
463 PROCEDURE GENERATE_INTERNAL
464 (
465 P_ACTION_INFORMATION_ID number,
466 P_ACTION_CONTEXT_ID number,
467 P_CUSTOM_ACTION_INFO_CAT varchar2,
468 P_CUSTOM_XML_PROCEDURE varchar2,
469 P_GENERATE_HEADER_FLAG boolean,
470 P_ROOT_TAG varchar2,
471 P_DOCUMENT_TYPE varchar2,
472 P_XML OUT NOCOPY BLOB
473 ) AS
474
475 CURSOR get_leg_code IS
476 SELECT hoi2.org_information9
477 FROM pay_assignment_actions paa,
478 pay_payroll_actions ppa,
479 hr_organization_units hou,
480 hr_organization_information hoi1,
481 hr_organization_information hoi2
482 WHERE paa.payroll_action_id = ppa.payroll_action_id
483 AND ppa.business_group_id = hou.organization_id
484 AND hou.organization_id = hoi1.organization_id
485 AND hoi1.organization_id = hoi2.organization_id
486 AND ppa.effective_date BETWEEN hou.date_from
487 AND nvl(hou.date_to,
488 hr_general.end_of_time)
489 AND hoi1.org_information_context = 'CLASS'
490 AND hoi1.org_information1 = 'HR_BG'
491 AND hoi2.org_information_context = 'Business Group Information'
492 AND ppa.action_type = 'X'
493 AND NVL (p_action_context_id, (SELECT action_context_id
494 FROM pay_action_information
495 WHERE action_information_id =
496 p_action_information_id))
497 = paa.assignment_action_id;
498
499 CURSOR csr_get_archived_regions IS
500 SELECT DISTINCT action_information_category
501 FROM pay_action_information
502 WHERE ((action_context_type = 'AAP'
503 AND action_context_id = p_action_context_id)
504 OR (action_context_type = 'PA'
505 AND action_context_id =
506 (SELECT payroll_action_id
507 FROM pay_assignment_actions
508 WHERE assignment_action_id = p_action_context_id)))
509 OR (action_information_id = p_action_information_id
510 AND p_action_information_id IS NOT NULL)
511 ORDER BY decode (action_information_category,'EMPLOYEE DETAILS', 1, 2);
512 /* NOTE - This ORDER BY clause will make sure that EMPLOYEE DETAILS gets
513 processed before all other action information categories so
514 that we have the organization_id (action_information2) for
515 filtering undesired ADDRESS DETAILS archived at payroll action
516 level (action_context_type = 'PA') by the payroll archiver.*/
517
518 l_proc_name varchar2(100);
519 sqlstr dbms_sql.varchar2s;
520 l_cntr_sql number;
521 l_xml BLOB;
522 csr number;
523 ret number;
524 cntr_flex_col number;
525 l_flex_col_num number; /* Max. number of flex segments in Action
526 Informtion DF */
527
528 l_kff_seg_start number; /* Segment number where bank KFF segments start.
529 Currently, it is ACTION_INFORMATION5, so,
530 l_kff_seg_start = 5 */
531
532 l_kff_seg_end number; /* Segment number where bank KFF segments end.
533 Currently, it is ACTION_INFORMATION14, so,
534 l_kff_seg_end = 14 */
535
536 l_action_information_id varchar2(100);
537 l_action_context_id varchar2(100);
538 lr_xml RAW (32767);
539 ln_amt number;
540
541 BEGIN
542 l_proc_name := g_proc_name || 'GENERATE_INTERNAL';
543 hr_utility_trace ('Entering '||l_proc_name);
544 hr_utility_trace ('Parameters ....');
545 hr_utility_trace ('P_ACTION_INFORMATION_ID ='||P_ACTION_INFORMATION_ID);
546 hr_utility_trace ('P_ACTION_CONTEXT_ID ='||P_ACTION_CONTEXT_ID);
547 IF p_generate_header_flag THEN
548 hr_utility_trace ('P_GENERATE_HEADER_FLAG = TRUE');
549 ELSE
550 hr_utility_trace ('P_GENERATE_HEADER_FLAG = FALSE');
551 END IF;
552 hr_utility_trace ('P_CUSTOM_ACTION_INFO_CAT ='||P_CUSTOM_ACTION_INFO_CAT);
553
554 g_xml_table.delete();
555 l_flex_col_num := 30;
556 l_kff_seg_start := 5;
557 l_kff_seg_end := 14;
558 l_cntr_sql := 1;
559 g_action_ctx_id := p_action_context_id;
560 g_custom_context := TRANSLATE(p_custom_action_info_cat, ' /', '__');
561
562 SELECT DECODE (p_action_information_id,
566 NULL, 'NULL',
563 NULL, 'NULL',
564 to_char(p_action_information_id)),
565 DECODE (p_action_context_id,
567 to_char(p_action_context_id))
568 INTO l_action_information_id,
569 l_action_context_id
570 FROM DUAL;
571
572 build_sql(sqlstr, l_cntr_sql, 'declare l_org_id varchar2(100);begin ');
573 IF p_generate_header_flag THEN
574 build_sql(sqlstr,
575 l_cntr_sql,
576 'pay_payroll_xml_extract_pkg.load_xml(''CS'', NULL, ''?xml version="1.0" encoding="'||
577 hr_mx_utility.get_IANA_charset||'"?'', NULL);');
578 END IF;
579 IF p_root_tag IS NOT NULL THEN
580 build_sql(sqlstr,
581 l_cntr_sql,
582 'pay_payroll_xml_extract_pkg.load_xml(''CS'', NULL, '''||
583 p_root_tag||''', NULL);');
584 END IF;
585
586 /* Fetch legislation_code. */
587 OPEN get_leg_code;
588 FETCH get_leg_code INTO pay_payroll_xml_extract_pkg.g_leg_code;
589 CLOSE get_leg_code;
590
591 FOR csr_get_archived_regions_rec IN csr_get_archived_regions LOOP
592 IF csr_get_archived_regions_rec.action_information_category IN
593 ('ADDRESS DETAILS', pay_payroll_xml_extract_pkg.g_leg_code ||
594 ' EMPLOYER DETAILS') THEN
595 build_sql(sqlstr,
596 l_cntr_sql,
597 'FOR csr_get_archived_info_rec IN pay_payroll_xml_extract_pkg.csr_get_archived_info (:l_action_context_id,'''||
598 csr_get_archived_regions_rec.action_information_category
599 ||''', l_org_id, :l_action_information_id) LOOP ');
600 ELSE
601 build_sql(sqlstr,
602 l_cntr_sql,
603 'FOR csr_get_archived_info_rec IN pay_payroll_xml_extract_pkg.csr_get_archived_info (:l_action_context_id,'''||
604 csr_get_archived_regions_rec.action_information_category
605 ||''', NULL, :l_action_information_id) LOOP ');
606 END IF;
607 build_sql(sqlstr,
608 l_cntr_sql,
609 'pay_payroll_xml_extract_pkg.load_xml(''CS'', NULL, ''' ||
610 csr_get_archived_regions_rec.action_information_category ||
611 ''', NULL);');
612 cntr_flex_col := 1;
613 LOOP
614 EXIT WHEN cntr_flex_col > l_flex_col_num;
615 IF flex_seg_enabled (
616 csr_get_archived_regions_rec.action_information_category,
617 'ACTION_INFORMATION'||cntr_flex_col) THEN
618 IF csr_get_archived_regions_rec.action_information_category =
619 'EMPLOYEE NET PAY DISTRIBUTION'
620 AND cntr_flex_col BETWEEN l_kff_seg_start
621 AND l_kff_seg_end THEN
622 build_sql(sqlstr,
623 l_cntr_sql,
624 'pay_payroll_xml_extract_pkg.load_xml(''D'', ''Segment'
625 || (cntr_flex_col - l_kff_seg_start + 1) ||
626 ''', LTRIM(RTRIM(csr_get_archived_info_rec.action_information'
627 || cntr_flex_col ||')));');
628 ELSE
629 build_sql(sqlstr,
630 l_cntr_sql,
631 'pay_payroll_xml_extract_pkg.load_xml(''D'', '''
632 || csr_get_archived_regions_rec.action_information_category ||
633 ''', ''ACTION_INFORMATION'|| cntr_flex_col ||
634 ''', LTRIM(RTRIM(csr_get_archived_info_rec.action_information'
635 || cntr_flex_col ||')));');
636 END IF;
637 END IF;
638 cntr_flex_col := cntr_flex_col + 1;
639 END LOOP;
640 /*Generate payroll details from time period id (action_information16)*/
641 IF csr_get_archived_regions_rec.action_information_category =
642 'EMPLOYEE DETAILS' THEN
643 build_sql(sqlstr,
644 l_cntr_sql,
645 'FOR csr_payroll_details_rec IN pay_payroll_xml_extract_pkg.csr_payroll_details(csr_get_archived_info_rec.action_information16) LOOP ');
646 build_sql(sqlstr,
647 l_cntr_sql,
648 'pay_payroll_xml_extract_pkg.load_xml(''D'', NULL, ''PAYROLL_NAME'', csr_payroll_details_rec.payroll_name);');
649 build_sql(sqlstr,
650 l_cntr_sql,
651 'pay_payroll_xml_extract_pkg.load_xml(''D'', NULL, ''PERIOD_TYPE'', csr_payroll_details_rec.period_type);');
652 build_sql(sqlstr,
653 l_cntr_sql,
654 'pay_payroll_xml_extract_pkg.load_xml(''D'', NULL, ''START_DATE'', csr_payroll_details_rec.start_date);');
655 build_sql(sqlstr,
656 l_cntr_sql,
657 'pay_payroll_xml_extract_pkg.load_xml(''D'', NULL, ''END_DATE'', csr_payroll_details_rec.end_date);');
658 build_sql(sqlstr,
659 l_cntr_sql,
663 'END LOOP;');
660 '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));');
661 build_sql(sqlstr,
662 l_cntr_sql,
664 build_sql(sqlstr,
665 l_cntr_sql,
666 'l_org_id := csr_get_archived_info_rec.action_information2;');
667 END IF;
668
669 -- Localization procedure call
670 build_sql(sqlstr,
671 l_cntr_sql,
672 'BEGIN ');
673 build_sql(sqlstr,
674 l_cntr_sql,
675 'EXECUTE IMMEDIATE (''BEGIN pay_'||
676 pay_payroll_xml_extract_pkg.g_leg_code||
677 '_rules.add_custom_xml('||l_action_context_id||
678 ', '''''|| csr_get_archived_regions_rec.action_information_category ||
679 ''''', '''''|| p_document_type ||
680 '''''); END;'');');
681
682 build_sql(sqlstr,
683 l_cntr_sql,
684 'IF pay_payroll_xml_extract_pkg.g_custom_xml.count() > 0 THEN ');
685 build_sql(sqlstr,
686 l_cntr_sql,
687 'pay_payroll_xml_extract_pkg.load_xml(''<!-- Following segment(s) were added by PAY_'||
688 pay_payroll_xml_extract_pkg.g_leg_code||
689 '_RULES.ADD_CUSTOM_XML -->'');');
690 build_sql(sqlstr,
691 l_cntr_sql,
692 'FOR cntr IN pay_payroll_xml_extract_pkg.g_custom_xml.first()..pay_payroll_xml_extract_pkg.g_custom_xml.last() LOOP ');
693 build_sql(sqlstr,
694 l_cntr_sql,
695 'pay_payroll_xml_extract_pkg.load_xml(pay_payroll_xml_extract_pkg.g_custom_xml(cntr));');
696 build_sql(sqlstr,
697 l_cntr_sql,
698 'END LOOP;');
699 build_sql(sqlstr,
700 l_cntr_sql,
701 'pay_payroll_xml_extract_pkg.g_custom_xml.delete();');
702 build_sql(sqlstr,
703 l_cntr_sql,
704 'END IF;');
705 build_sql(sqlstr,
706 l_cntr_sql,
707 'EXCEPTION ');
708 build_sql(sqlstr,
709 l_cntr_sql,
710 'WHEN OTHERS THEN NULL;');
711 build_sql(sqlstr,
712 l_cntr_sql,
713 'END;');
714
715 -- Custom procedure call
716 build_sql(sqlstr,
717 l_cntr_sql,
718 'BEGIN ');
719 build_sql(sqlstr,
720 l_cntr_sql,
721 'EXECUTE IMMEDIATE (''BEGIN '||p_custom_xml_procedure||
722 '('||l_action_context_id||
723 ', '''''||
724 csr_get_archived_regions_rec.action_information_category ||
725 ''''', '''''|| p_document_type ||
726 '''''); END;'');');
727 build_sql(sqlstr,
728 l_cntr_sql,
729 'IF pay_payroll_xml_extract_pkg.g_custom_xml.count() > 0 THEN ');
730 build_sql(sqlstr,
731 l_cntr_sql,
732 'pay_payroll_xml_extract_pkg.load_xml(''<!-- Following segment(s) were added by '||
733 UPPER(p_custom_xml_procedure)||' -->'');');
734 build_sql(sqlstr,
735 l_cntr_sql,
736 'FOR cntr IN pay_payroll_xml_extract_pkg.g_custom_xml.first()..pay_payroll_xml_extract_pkg.g_custom_xml.last() LOOP ');
737 build_sql(sqlstr,
738 l_cntr_sql,
739 'pay_payroll_xml_extract_pkg.load_xml(pay_payroll_xml_extract_pkg.g_custom_xml(cntr));');
740 build_sql(sqlstr,
741 l_cntr_sql,
742 'END LOOP;');
743 build_sql(sqlstr,
744 l_cntr_sql,
745 'pay_payroll_xml_extract_pkg.g_custom_xml.delete();');
746 build_sql(sqlstr,
747 l_cntr_sql,
748 'END IF;');
749 build_sql(sqlstr,
750 l_cntr_sql,
751 'EXCEPTION ');
752 build_sql(sqlstr,
753 l_cntr_sql,
754 'WHEN OTHERS THEN NULL;');
755 build_sql(sqlstr,
756 l_cntr_sql,
757 'END;');
758
759 build_sql(sqlstr,
760 l_cntr_sql,
761 'pay_payroll_xml_extract_pkg.load_xml(''CE'', NULL, ''' ||
762 csr_get_archived_regions_rec.action_information_category ||
763 ''', NULL);');
764 build_sql(sqlstr,
765 l_cntr_sql,
766 'END LOOP;');
767 END LOOP;
768
769 -- Localization procedure call
770 build_sql(sqlstr,
771 l_cntr_sql,
772 'BEGIN ');
773 build_sql(sqlstr,
774 l_cntr_sql,
778 ', '''''|| p_document_type ||
775 'EXECUTE IMMEDIATE (''BEGIN pay_'||
776 pay_payroll_xml_extract_pkg.g_leg_code||
777 '_rules.add_custom_xml('||l_action_context_id||', NULL'||
779 '''''); END;'');');
780 build_sql(sqlstr,
781 l_cntr_sql,
782 'IF pay_payroll_xml_extract_pkg.g_custom_xml.count() > 0 THEN ');
783 build_sql(sqlstr,
784 l_cntr_sql,
785 'pay_payroll_xml_extract_pkg.load_xml(''<!-- Following context(s) were added by PAY_'||
786 pay_payroll_xml_extract_pkg.g_leg_code||
787 '_RULES.ADD_CUSTOM_XML -->'');');
788 build_sql(sqlstr,
789 l_cntr_sql,
790 'FOR cntr IN pay_payroll_xml_extract_pkg.g_custom_xml.first()..pay_payroll_xml_extract_pkg.g_custom_xml.last() LOOP ');
791 build_sql(sqlstr,
792 l_cntr_sql,
793 'pay_payroll_xml_extract_pkg.load_xml(pay_payroll_xml_extract_pkg.g_custom_xml(cntr));');
794 build_sql(sqlstr,
795 l_cntr_sql,
796 'END LOOP;');
797 build_sql(sqlstr,
798 l_cntr_sql,
799 'pay_payroll_xml_extract_pkg.g_custom_xml.delete();');
800 build_sql(sqlstr,
801 l_cntr_sql,
802 'END IF;');
803 build_sql(sqlstr,
804 l_cntr_sql,
805 'EXCEPTION ');
806 build_sql(sqlstr,
807 l_cntr_sql,
808 'WHEN OTHERS THEN NULL;');
809 build_sql(sqlstr,
810 l_cntr_sql,
811 'END;');
812
813 -- Custom procedure call
814 build_sql(sqlstr,
815 l_cntr_sql,
816 'BEGIN ');
817 build_sql(sqlstr,
818 l_cntr_sql,
819 'EXECUTE IMMEDIATE (''BEGIN '||p_custom_xml_procedure||
820 '('||l_action_context_id||', NULL'||
821 ', '''''|| p_document_type ||
822 '''''); END;'');');
823 build_sql(sqlstr,
824 l_cntr_sql,
825 'IF pay_payroll_xml_extract_pkg.g_custom_xml.count() > 0 THEN ');
826 build_sql(sqlstr,
827 l_cntr_sql,
828 'pay_payroll_xml_extract_pkg.load_xml(''<!-- Following segment(s) were added by '||
829 UPPER(p_custom_xml_procedure)||' -->'');');
830 build_sql(sqlstr,
831 l_cntr_sql,
832 'FOR cntr IN pay_payroll_xml_extract_pkg.g_custom_xml.first()..pay_payroll_xml_extract_pkg.g_custom_xml.last() LOOP ');
833 build_sql(sqlstr,
834 l_cntr_sql,
835 'pay_payroll_xml_extract_pkg.load_xml(pay_payroll_xml_extract_pkg.g_custom_xml(cntr));');
836 build_sql(sqlstr,
837 l_cntr_sql,
838 'END LOOP;');
839 build_sql(sqlstr,
840 l_cntr_sql,
841 'pay_payroll_xml_extract_pkg.g_custom_xml.delete();');
842 build_sql(sqlstr,
843 l_cntr_sql,
844 'END IF;');
845 build_sql(sqlstr,
846 l_cntr_sql,
847 'EXCEPTION ');
848 build_sql(sqlstr,
849 l_cntr_sql,
850 'WHEN OTHERS THEN NULL;');
851 build_sql(sqlstr,
852 l_cntr_sql,
853 'END;');
854
855 IF p_root_tag IS NOT NULL THEN
856 build_sql(sqlstr,
857 l_cntr_sql,
858 'pay_payroll_xml_extract_pkg.load_xml(''CE'', NULL, '''||
859 p_root_tag||''', NULL);');
860 END IF;
861
862 build_sql(sqlstr,
863 l_cntr_sql,
864 'null;');
865 build_sql(sqlstr,
866 l_cntr_sql,
867 'end;');
868
872
869 FOR cntr IN sqlstr.first()..sqlstr.last() LOOP
870 hr_utility_trace(sqlstr(cntr));
871 END LOOP;
873 csr := dbms_sql.open_cursor;
874 dbms_sql.parse (csr,
875 sqlstr,
876 sqlstr.first(),
877 sqlstr.last(),
878 false,
879 dbms_sql.v7);
880 dbms_sql.bind_variable (csr,
881 ':l_action_context_id',
882 p_action_context_id);
883 dbms_sql.bind_variable (csr,
884 ':l_action_information_id',
885 p_action_information_id);
886 ret := dbms_sql.execute(csr);
887 dbms_sql.close_cursor(csr);
888
889 IF g_xml_table.count() <> 0 THEN
890 dbms_lob.createTemporary(l_xml, true, dbms_lob.session);
891 FOR cntr IN g_xml_table.first()..g_xml_table.last() LOOP
892 lr_xml := utl_raw.cast_to_raw(g_xml_table(cntr));
893 ln_amt := utl_raw.length(lr_xml);
894
895 dbms_lob.writeAppend(l_xml,
896 ln_amt,
897 lr_xml);
898
899 hr_utility_trace (g_xml_table(cntr));
900 END LOOP;
901 p_xml := l_xml;
902 dbms_lob.freeTemporary(l_xml);
903 END IF;
904
905 print_blob (p_xml);
906
907 -- Unset globals before exit;
908 g_xml_table.delete();
909 g_custom_context := NULL;
910
911 hr_utility_trace ('Leaving '||l_proc_name);
912 END GENERATE_INTERNAL;
913
914
915 /****************************************************************************
916 Name : GENERATE
917 Description : This procedure interprets archived information, converts it to
918 XML and prints it out to a BLOB. This is a public procedure
919 and is based on action_context_IDs passed by the calling
920 process. All archived records belonging to passed
921 action_context_id will be converted to XML. Currently, online
922 payslip and MX Pay Advice invoke this overloaded version.
923 *****************************************************************************/
924
925 PROCEDURE GENERATE
926 (
927 P_ACTION_CONTEXT_ID number,
928 P_CUSTOM_XML_PROCEDURE varchar2,
929 P_GENERATE_HEADER_FLAG varchar2, -- {Y/N}
930 P_ROOT_TAG varchar2,
931 P_DOCUMENT_TYPE varchar2,
932 P_XML OUT NOCOPY BLOB
933 ) AS
934 l_proc_name varchar2(100);
935 lb_header_flag boolean;
936 BEGIN
937 l_proc_name := g_proc_name || 'GENERATE';
938 hr_utility_trace ('Entering '||l_proc_name);
939
940 IF p_generate_header_flag = 'Y' THEN
941 lb_header_flag := TRUE;
942 ELSE
943 lb_header_flag := FALSE;
944 END IF;
945
946 generate_internal(
947 NULL,
948 p_action_context_id,
949 NULL,
950 p_custom_xml_procedure,
951 lb_header_flag,
952 p_root_tag,
953 p_document_type,
954 p_xml);
955
956 hr_utility_trace ('Leaving '||l_proc_name);
957 END GENERATE;
958
959
960 /****************************************************************************
961 Name : GENERATE
962 Description : This procedure interprets archived information, converts it to
963 XML and prints it out to a BLOB. This is a public procedure
964 and is driven off action_information_IDs set by the calling
965 process.
966
967 It also accepts a custom XML tag parameter, which if passed a
968 non-null value, will be used as parent enclosing tag of each
969 action_information_id irrespective of the actual action
970 information category.
971
972 Currently, MX SUA process invokes this overloaded version.
973 *****************************************************************************/
974
975 PROCEDURE GENERATE
976 (
977 P_ACTION_INF_ID_TAB int_tab_type,
978 P_CUSTOM_ACTION_INFO_CAT varchar2,
979 P_DOCUMENT_TYPE varchar2,
980 P_XML OUT NOCOPY BLOB
981 ) AS
982
983 l_xml BLOB;
984 l_xml_temp BLOB;
985 l_proc_name varchar2(100);
986 lr_buf RAW (2000);
987 l_last_param number;
988 BEGIN
989 l_proc_name := g_proc_name || 'GENERATE-2';
990 hr_utility_trace ('Entering '||l_proc_name);
991 hr_utility_trace ('Total action_information_IDs = '||
992 p_action_inf_id_tab.count());
993
994 IF p_action_inf_id_tab.count() > 0 THEN
995 FOR cntr_arch_rec IN
996 p_action_inf_id_tab.first()..p_action_inf_id_tab.last()
997 LOOP
998 hr_utility_trace(p_action_inf_id_tab (cntr_arch_rec));
999 END LOOP;
1000 END IF;
1001
1002 IF p_action_inf_id_tab.count() > 0 THEN
1003 dbms_lob.createTemporary (l_xml,
1004 TRUE,
1005 dbms_lob.SESSION);
1006 FOR cntr_arch_rec IN
1007 p_action_inf_id_tab.first()..p_action_inf_id_tab.last()
1008 LOOP
1009 l_last_param := pay_payroll_xml_extract_pkg.g_custom_params.last();
1010 IF l_last_param IS NULL THEN
1011 l_last_param := 0;
1012 END IF;
1013
1014 pay_payroll_xml_extract_pkg.g_custom_params(
1015 l_last_param + 1).parameter_name := 'action_information_id';
1016
1017 pay_payroll_xml_extract_pkg.g_custom_params(
1018 l_last_param + 1).parameter_value :=
1019 p_action_inf_id_tab(cntr_arch_rec);
1020
1021 generate_internal(
1022 p_action_inf_id_tab(cntr_arch_rec),
1023 NULL,
1024 p_custom_action_info_cat,
1025 NULL,
1026 FALSE,
1027 NULL,
1028 p_document_type,
1029 l_xml_temp);
1030
1031 dbms_lob.append(l_xml,
1032 l_xml_temp);
1033
1034 /* Remove the parameter 'action_information_id' before the next
1035 iteration (or returning to the calling program). Calling
1036 application is expected to clear this cache to avoid a
1037 possibility of stale parameter values in subsequent calls.*/
1038 pay_payroll_xml_extract_pkg.g_custom_params.delete(
1039 pay_payroll_xml_extract_pkg.g_custom_params.last());
1040 END LOOP;
1041 p_xml := l_xml;
1042 dbms_lob.freeTemporary(l_xml);
1043 END IF;
1044
1045 hr_utility_trace ('Leaving '||l_proc_name);
1046
1047 -- Bug 6068599
1048 EXCEPTION
1049 WHEN OTHERS THEN
1050 /* Remove the parameter 'action_information_id' in case of error. */
1051 pay_payroll_xml_extract_pkg.g_custom_params.delete(
1052 pay_payroll_xml_extract_pkg.g_custom_params.last());
1053 hr_utility_trace (sqlerrm);
1054 RAISE;
1055 END;
1056
1057 BEGIN
1058 --hr_utility.trace_on (null, 'MX_IDC');
1059 g_proc_name := 'PAY_PAYROLL_XML_EXTRACT_PKG.';
1060 g_debug := hr_utility.debug_enabled;
1061 END PAY_PAYROLL_XML_EXTRACT_PKG;