[Home] [Help]
PACKAGE BODY: APPS.PAY_DIRECT_DEPOSIT_XML
Source
1 PACKAGE BODY PAY_DIRECT_DEPOSIT_XML AS
2 /* $Header: payddxml.pkb 120.10 2006/01/15 22:47 sdahiya noship $ */
3 /* +======================================================================+
4 | Copyright (c) 2003 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +======================================================================+
8 Package Name : pay_direct_deposit_xml
9 Package File Name : payddxml.pkb
10
11 Description : Used for Direct Deposit Extract
12
13 Change List:
14 ------------
15
16 Name Date Version Bug Text
17 ------------- ----------- ------- ------- ------------------------------
18 sodhingr 20-Jul-2005 115.0 Initial Version
19 sodhingr 22-Aug-2005 115.1 changed the PL/sql table name
20 from g_payslip_xml to g_xml_table
21 vmehta 05-Oct-2005 115.3 Delete the parameter table
22 pay_payroll_xml_extract_pkg.
23 g_custom_params before setting.
24 mmukherj 21-Oct-2005 115.4 Created another TAG in the
25 employee details level which
26 prints the deposit amount
27 multiplied by 100. This is needed
28 because the output format for some
29 legislations needs the deposit
30 amount printed that way. The name
31 of the new Tag is:
32 DEPOSIT_AMOUNT100.
33 vmehta 24-Oct-2005 115.5 Removed the new tag
34 DEPOSIT_AMOUNT100. Changed
35 AMOUNT to varchar. This is stored
36 in the '9999999999D99' format
37 to address the trailing zero issue
38 vmehta 20-Nov-2005 115.6 Modified gen_employer_level_xml
39 to fetch information about
40 Paymeth Developer DF and add to
41 XML
42 vmehta 21-Nov-2005 115.7 Add Paymeth Developer DF related
43 segments only if not null.
44 sdahiya 25-Nov-2005 115.8 4761066 Added FM to number format mask
45 to eliminate leading spaces.
46 sdahiya 30-Nov-2005 115.9 4773967 CLOB to BLOB migration.
47 sdahiya 01-Dec-2005 115.10 Modified PRINT_BLOB to use
48 pay_ac_utility.print_lob.
49 sdahiya 01-Dec-2005 115.11 Used core procedure
50 pay_core_files.write_to_magtape_lob
51 to manipulate core magtape BLOB.
52 sdahiya 22-Dec-2005 115.12 Removed XML header information.
53 PYUGEN will generate XML headers.
54 ========================================================================*/
55
56 --
57 -- Global Variables
58 --
59 TYPE char_tab IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
60 g_xml_cache char_tab;
61 g_proc_name varchar2(240);
62 g_debug boolean;
63 g_leg_code varchar2(5);
64 g_person_flex char_tab;
65 g_currency_code varchar2(10);
66 g_org_flex char_tab;
67 g_pmeth_flex char_tab;
68
69
70 CURSOR c_get_leg_code (p_business_group_id NUMBER) IS
71 SELECT legislation_code
72 FROM per_business_groups
73 WHERE business_group_id = p_business_group_id;
74
75
76 /****************************************************************************
77 Name : HR_UTILITY_TRACE
78 Description : This procedure prints debug messages.
79 *****************************************************************************/
80 PROCEDURE HR_UTILITY_TRACE
81 (
82 P_TRC_DATA varchar2
83 ) AS
84 BEGIN
85 IF g_debug THEN
86 hr_utility.trace(p_trc_data);
87 END IF;
88 END HR_UTILITY_TRACE;
89
90 /****************************************************************************
91 Name : PRINT_BLOB
92 Description : This procedure prints contents of BLOB passed as parameter.
93 *****************************************************************************/
94
95 PROCEDURE PRINT_BLOB(p_blob BLOB) IS
96 BEGIN
97 IF g_debug THEN
98 pay_ac_utility.print_lob(p_blob);
99 END IF;
100 END PRINT_BLOB;
101
102
103 /****************************************************************************
104 Name : WRITE_TO_MAGTAPE_LOB
105 Description : This procedure appends passed BLOB parameter to
106 pay_mag_tape.g_blob_value
107 *****************************************************************************/
108
109 PROCEDURE WRITE_TO_MAGTAPE_LOB(p_blob BLOB) IS
110 BEGIN
111 IF dbms_lob.getLength (p_blob) IS NOT NULL THEN
112 pay_core_files.write_to_magtape_lob (p_blob);
113 END IF;
114 END WRITE_TO_MAGTAPE_LOB;
115
116
117 /****************************************************************************
118 Name : WRITE_TO_MAGTAPE_LOB
119 Description : This procedure appends passed varchar2 parameter to
120 pay_mag_tape.g_blob_value
121 *****************************************************************************/
122
123 PROCEDURE WRITE_TO_MAGTAPE_LOB(p_data varchar2) IS
124 BEGIN
125 pay_core_files.write_to_magtape_lob (p_data);
126 END WRITE_TO_MAGTAPE_LOB;
127
128
129 /****************************************************************************
130 Name : GENERATE_XML
131 Description : This procedure fetches archived data, converts it to XML
132 format and appends to pay_mag_tape.g_blob_value.
133 *****************************************************************************/
134 PROCEDURE GENERATE_XML AS
135
136 CURSOR get_person_bank_details(p_per_pay_method NUMBER,
137 p_effective_date DATE) IS
138 SELECT segment1 ,segment2 ,segment3
139 ,segment4 ,segment5 ,segment6 ,segment7
140 ,segment8 ,segment9 ,segment10 ,segment11
141 ,segment12 ,segment13 ,segment14 ,segment15
142 ,segment16 ,segment17 ,segment18 ,segment19
143 ,segment20 ,segment21 ,segment22 ,segment23
144 ,segment24 ,segment25 ,segment26 ,segment27
145 ,segment28 ,segment29 ,segment30 ,org_payment_method_id
146 FROM pay_personal_payment_methods_f pppm,
147 pay_external_accounts pea
148 WHERE --pppm.assignment_id = p_assignment_id
149 pppm.personal_payment_method_id = p_per_pay_method
150 AND pppm.external_account_id = pea.external_account_id
151 AND p_effective_date between pppm.EFFECTIVE_START_DATE
152 and pppm.EFFECTIVE_END_DATE;
153
154
155 CURSOR get_employee_details(p_assignment_id in number
156 ,p_effective_date in date) IS
157 SELECT ppf.first_name, ppf.last_name, ppf.middle_names, ppf.order_name,
158 ppf.full_name, ppf.national_identifier,
159 ppf.employee_number
160 FROM per_assignments_f paf,
161 per_all_people_f ppf,
162 per_periods_of_service pps
163 WHERE paf.person_id = ppf.person_id
164 and paf.assignment_id = p_assignment_id
165 and p_effective_date between paf.effective_start_date
166 and paf.effective_end_date
167 and p_effective_date between ppf.effective_start_date
168 and ppf.effective_end_date
169 and pps.person_id = ppf.person_id
170 and pps.date_start = (select max(pps1.date_start)
171 from per_periods_of_service pps1
172 where pps1.person_id = paf.person_id
173 and pps1.date_start <= p_effective_date);
174
175
176 CURSOR get_payroll_details(p_prepay_asg_act in number) IS
177 SELECT ppa.start_date,ppa.effective_date,
178 pp.payroll_name
179 FROM pay_assignment_actions paa
180 ,pay_payroll_actions ppa
181 ,pay_payrolls_f pp
182 WHERE paa.assignment_action_id = p_prepay_asg_act
183 and ppa.payroll_action_id = paa.payroll_action_id
184 and pp.payroll_id = ppa.payroll_id
185 and ppa.effective_date between pp.effective_start_date
186 and pp.effective_end_date;
187
188
189 l_org_payment_method_id pay_personal_payment_methods_f.org_payment_method_id%TYPE;
190 lv_first_name per_all_people_f.first_name%TYPE;
191 lv_last_name per_all_people_f.last_name%TYPE;
192 lv_middle_names per_all_people_f.middle_names%TYPE;
193 lv_order_name per_all_people_f.order_name%TYPE;
194 lv_full_name per_all_people_f.full_name%TYPE;
195 lv_national_identifier per_all_people_f.national_identifier%TYPE;
196 lv_employee_number per_all_people_f.employee_number%TYPE;
197 ln_business_group_id number;
198 ln_per_pay_method number;
199 ln_pre_pay_id number;
200 ln_prepay_asg_act number;
201 ld_payroll_start_date date;
202 ld_payroll_end_date date;
203 lv_payroll_name pay_payrolls_f.payroll_name%TYPE;
204
205 l_proc_name varchar2(100);
206 ld_effective_date date;
207 ln_assignment_action_id number;
208 ln_assignment_id number;
209 l_xml BLOB;
210 l_custom_ee_xml BLOB;
211 ln_chars number;
212 ln_offset number;
213 lv_deposit_amount varchar2(15);
214 lv_buf varchar2(2000);
215 ln_param_count number;
216 lr_xml RAW (32767);
217 ln_amt number;
218
219
220 BEGIN
221 l_proc_name := g_proc_name || 'GENERATE_XML';
222 hr_utility_trace ('Entering '||l_proc_name);
223
224 ln_chars := 2000;
225 ln_offset := 1;
226
227 ln_assignment_action_id :=
228 pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID');
229
230 ln_assignment_id :=
231 pay_magtape_generic.get_parameter_value('TRANSFER_ASSIGNMENT_ID');
232
233 ld_effective_date :=
234 fnd_date.canonical_to_date(pay_magtape_generic.get_parameter_value( 'TRANSFER_EFFECTIVE_DATE'));
235
236 -- Bug 4761066
237 lv_deposit_amount :=
238 TO_CHAR(TO_NUMBER(pay_magtape_generic.get_parameter_value(
239 'DEPOSIT_AMOUNT')), 'FM9999999999D99');
240
241 ln_business_group_id :=
242 pay_magtape_generic.get_parameter_value(
243 'TRANSFER_BUSINESS_GROUP_ID');
244 ln_per_pay_method :=
245 pay_magtape_generic.get_parameter_value(
246 'TRANSFER_PERSONAL_PAY_METH');
247
248 ln_pre_pay_id :=
249 pay_magtape_generic.get_parameter_value('TRANSFER_PRE_PAY_ID');
250
251 ln_prepay_asg_act :=
252 pay_magtape_generic.get_parameter_value('TRANSFER_PREPAY_ASG_ACT');
253
254
255 /*Clear the details of previous assignmentId */
256 IF g_person_flex.count <> 0 THEN
257 g_person_flex.delete;
258 END IF;
259
260 /* IF pay_payroll_xml_extract_pkg.g_leg_code IS NULL THEN
261 OPEN get_leg_code(ln_assignment_action_id);
262 FETCH get_leg_code INTO pay_payroll_xml_extract_pkg.g_leg_code;
263 CLOSE get_leg_code;
264 END IF;
265 */
266
267 /* Get Personal Bank Details */
268 IF pay_payroll_xml_extract_pkg.g_leg_code IS NULL THEN
269 OPEN c_get_leg_code(ln_business_group_id);
270 FETCH c_get_leg_code INTO pay_payroll_xml_extract_pkg.g_leg_code;
271 CLOSE c_get_leg_code;
272 END IF;
273
274 OPEN get_person_bank_details(ln_per_pay_method,ld_effective_date);
275 FETCH get_person_bank_details INTO
276 g_person_flex(1),g_person_flex(2),g_person_flex(3),
277 g_person_flex(4),g_person_flex(5),g_person_flex(6),
278 g_person_flex(7),g_person_flex(8),g_person_flex(9),
279 g_person_flex(10),g_person_flex(11),g_person_flex(12),
280 g_person_flex(13),g_person_flex(14),
281 g_person_flex(15),g_person_flex(16),g_person_flex(17),
282 g_person_flex(18),g_person_flex(19),g_person_flex(20),
283 g_person_flex(21),g_person_flex(22),g_person_flex(23),
284 g_person_flex(24),g_person_flex(25),g_person_flex(26),
285 g_person_flex(27),g_person_flex(28),g_person_flex(29),
286 g_person_flex(30),l_org_payment_method_id;
287
288
289 CLOSE get_person_bank_details;
290
291 /*Get Employee Details */
292
293 OPEN get_employee_details(ln_assignment_id,ld_effective_date);
294 FETCH get_employee_details INTO
295 lv_first_name , lv_last_name, lv_middle_names, lv_order_name,
296 lv_full_name , lv_national_identifier,
297 lv_employee_number;
298 CLOSE get_employee_details;
299
300 /* Get Payroll Details */
301 OPEN get_payroll_details(ln_prepay_asg_act);
302 FETCH get_payroll_details INTO
303 ld_payroll_start_date, ld_payroll_end_date,
304 lv_payroll_name;
305
306 CLOSE get_payroll_details;
307
308 /* Build XML */
309 pay_payroll_xml_extract_pkg.load_xml('CS','DEPOSIT_DETAILS','');
310
311 FOR cntr IN 1..30 LOOP
312 IF g_person_flex(cntr) IS NOT NULL THEN
313 pay_payroll_xml_extract_pkg.load_xml('D','Segment'||cntr,g_person_flex(cntr));
314 END IF;
315 END LOOP;
316
317
318 pay_payroll_xml_extract_pkg.load_xml('D','PAYROLL_START_DATE',
319 fnd_date.date_to_canonical(ld_payroll_start_date));
320
321 pay_payroll_xml_extract_pkg.load_xml('D','PAYROLL_END_DATE',
322 fnd_date.date_to_canonical(ld_payroll_end_date));
323
324 pay_payroll_xml_extract_pkg.load_xml('D','PAYROLL_NAME',lv_payroll_name);
325
326 pay_payroll_xml_extract_pkg.load_xml('D','EMPLOYEE_NUMBER',
327 lv_employee_number);
328
329 pay_payroll_xml_extract_pkg.load_xml('D','FIRST_NAME',lv_first_name);
330
331 pay_payroll_xml_extract_pkg.load_xml('D','LAST_NAME',lv_last_name);
332
333 pay_payroll_xml_extract_pkg.load_xml('D','MIDDLE_NAMES',lv_middle_names);
334
335 pay_payroll_xml_extract_pkg.load_xml('D','FULL_NAME',lv_full_name);
336
337 pay_payroll_xml_extract_pkg.load_xml('D','CURRENCY',g_currency_code);
338
339 pay_payroll_xml_extract_pkg.load_xml('D','DEPOSIT_AMOUNT',
340 lv_deposit_amount);
341
342
343 pay_payroll_xml_extract_pkg.g_custom_params.DELETE;
344 ln_param_count := pay_payroll_xml_extract_pkg.g_custom_params.COUNT;
345
346 pay_payroll_xml_extract_pkg.g_custom_params(ln_param_count).parameter_name
347 := 'p_xml_level';
348
349 pay_payroll_xml_extract_pkg.g_custom_params(ln_param_count).parameter_value
350 := 'EE';
351
352 /*Employee Information -Legislation Specific*/
353 EXECUTE IMMEDIATE 'BEGIN PAY_'||pay_payroll_xml_extract_pkg.g_leg_code||
354 '_RULES.add_custom_xml(:1,:2,:3); END;'
355 USING IN ln_assignment_action_id,'','DEPOSIT_XML';
356
357 pay_payroll_xml_extract_pkg.load_xml('CE','DEPOSIT_DETAILS','');
358
359 IF pay_payroll_xml_extract_pkg.g_xml_table.count() <> 0 THEN
360 dbms_lob.createTemporary(l_xml, true, dbms_lob.session);
361 FOR cntr IN
362 pay_payroll_xml_extract_pkg.g_xml_table.first()..pay_payroll_xml_extract_pkg.g_xml_table.last() LOOP
363 lr_xml := utl_raw.cast_to_raw(
364 pay_payroll_xml_extract_pkg.g_xml_table(cntr));
365 ln_amt := utl_raw.length(lr_xml);
366
367 dbms_lob.writeAppend(l_xml,
368 ln_amt,
369 lr_xml);
370
371 hr_utility_trace (pay_payroll_xml_extract_pkg.g_xml_table(cntr));
372 END LOOP;
373 pay_payroll_xml_extract_pkg.g_xml_table.delete();
374
375 END IF;
376
377 write_to_magtape_lob (l_xml);
378 dbms_lob.freeTemporary(l_xml);
379
380 hr_utility_trace ('BLOB contents for assignment action '||
381 ln_assignment_action_id);
382 print_blob (pay_mag_tape.g_blob_value);
383
384 hr_utility_trace ('Leaving '||l_proc_name);
385 END GENERATE_XML;
386
387
388 /****************************************************************************
389 Name : GEN_XML_HEADER
390 Description : This procedure generates XML header information and appends to
391 pay_mag_tape.g_blob_value.
392 *****************************************************************************/
393 PROCEDURE GET_HEADERS AS
394 l_proc_name varchar2(100);
395 lv_buf varchar2(2000);
396 BEGIN
397 l_proc_name := g_proc_name || 'GEN_XML_HEADER';
398 --hr_utility.trace_on(null,'dd');
399 hr_utility_trace ('Entering '||l_proc_name);
400
401 lv_buf := pay_magtape_generic.get_parameter_value('ROOT_XML_TAG');
402
403 hr_utility_trace ('Header = '||lv_buf);
404
405 write_to_magtape_lob (lv_buf);
406
407 hr_utility_trace ('BLOB contents after appending header information');
408 print_blob (pay_mag_tape.g_blob_value);
409
410 hr_utility_trace ('Leaving '||l_proc_name);
411 END GET_HEADERS;
412
413
414
415 /****************************************************************************
416 Name : GEN_EMPLOYER_LEVEL_XML
417 Description : This procedure generates XML header information and appends to
418 pay_mag_tape.g_blob_value.
419 *****************************************************************************/
420 PROCEDURE get_deposit_header AS
421 l_proc_name varchar2(100);
422 lv_buf varchar2(2000);
423 ln_org_pay_method number;
424 ln_tax_unit_id number;
425 ln_payroll_action_id number;
426 ln_business_group_id number;
427 ld_effective_date date;
428 lv_dd_date varchar2(19);
429 l_xml BLOB;
430 l_custom_er_xml BLOB;
431 lv_leg_code varchar2(10);
432 ln_param_count number;
433 lv_pmeth_cat varchar2(100);
434 lr_xml RAW (32767);
435 ln_amt number;
436
437
438 CURSOR get_org_bank_details(p_org_payment_method_id VARCHAR2,
439 p_effective_date date) IS
440 SELECT segment1 ,segment2 ,segment3
441 ,segment4 ,segment5 ,segment6 ,segment7
442 ,segment8 ,segment9 ,segment10 ,segment11
443 ,segment12 ,segment13 ,segment14 ,segment15
444 ,segment16 ,segment17 ,segment18 ,segment19
445 ,segment20 ,segment21 ,segment22 ,segment23
446 ,segment24 ,segment25 ,segment26 ,segment27
447 ,segment28 ,segment29 ,segment30 ,popm.currency_code
448 ,pmeth_information_category
449 ,pmeth_information1 ,pmeth_information2 ,pmeth_information3
450 ,pmeth_information4 ,pmeth_information5 ,pmeth_information6
451 ,pmeth_information7 ,pmeth_information8 ,pmeth_information9
452 ,pmeth_information10 ,pmeth_information11 ,pmeth_information12
453 ,pmeth_information13 ,pmeth_information14 ,pmeth_information15
454 ,pmeth_information16 ,pmeth_information17 ,pmeth_information18
455 ,pmeth_information19 ,pmeth_information20
456 FROM pay_org_payment_methods_f popm,
457 pay_external_accounts pea
458 WHERE org_payment_method_id = p_org_payment_method_id
459 AND popm.external_account_id = pea.external_account_id
460 AND p_effective_date between popm.EFFECTIVE_START_DATE
461 and popm.EFFECTIVE_END_DATE;
462
463
464
465 BEGIN
466 l_proc_name := g_proc_name || 'GEN_EMPLOYER_LEVEL_XML';
467 hr_utility_trace ('Entering '||l_proc_name);
468 lv_pmeth_cat := NULL;
469
470 ln_org_pay_method :=
471 pay_magtape_generic.get_parameter_value('TRANSFER_ORG_PAY_METHOD');
472
473 ld_effective_date :=
474 fnd_date.canonical_to_date(pay_magtape_generic.get_parameter_value(
475 'TRANSFER_EFFECTIVE_DATE'));
476
477 lv_dd_date :=
478 pay_magtape_generic.get_parameter_value('TRANSFER_DD_DATE');
479
480 ln_payroll_action_id :=
481 pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID');
482
483 ln_business_group_id :=
484 pay_magtape_generic.get_parameter_value('TRANSFER_BUSINESS_GROUP_ID');
485
486 hr_utility_trace ('ln_org_pay_method '||ln_org_pay_method);
487 hr_utility_trace ('ld_effective_date '||ld_effective_date);
488 hr_utility_trace ('Direct Deposit Date '||lv_dd_date);
489 hr_utility_trace ('ln_payroll_action_id '||ln_payroll_action_id);
490 hr_utility_trace ('ln_business_group_id '||ln_business_group_id);
491
492
493 pay_payroll_xml_extract_pkg.load_xml('CS','DEPOSIT_HEADER','');
494 /*Clear the details of previous assignmentId */
495 IF g_org_flex.count <> 0 THEN
496 g_org_flex.delete;
497 END IF;
498
499 IF g_pmeth_flex.count <> 0 THEN
500 g_pmeth_flex.delete;
501 END IF;
502
503 IF pay_payroll_xml_extract_pkg.g_leg_code IS NULL THEN
504 OPEN c_get_leg_code(ln_business_group_id);
505 FETCH c_get_leg_code INTO lv_leg_code;
506 hr_utility_trace ('Legislation Code '||lv_leg_code);
507
508 pay_payroll_xml_extract_pkg.g_leg_code :=lv_leg_code;
509 CLOSE c_get_leg_code;
510 END IF;
511
512 hr_utility_trace ('Legislation Code '||pay_payroll_xml_extract_pkg.g_leg_code);
513
514 OPEN get_org_bank_details(ln_org_pay_method,ld_effective_date);
515 FETCH get_org_bank_details INTO
516 g_org_flex(1),g_org_flex(2),g_org_flex(3),
517 g_org_flex(4),g_org_flex(5),g_org_flex(6),
518 g_org_flex(7),g_org_flex(8),g_org_flex(9),
519 g_org_flex(10),g_org_flex(11),g_org_flex(12),
520 g_org_flex(13),g_org_flex(14),
521 g_org_flex(15),g_org_flex(16),g_org_flex(17),
522 g_org_flex(18),g_org_flex(19),g_org_flex(20),
523 g_org_flex(21),g_org_flex(22),g_org_flex(23),
524 g_org_flex(24),g_org_flex(25),g_org_flex(26),
525 g_org_flex(27),g_org_flex(28),g_org_flex(29),
526 g_org_flex(30),g_currency_code, lv_pmeth_cat,
527 g_pmeth_flex(1), g_pmeth_flex(2), g_pmeth_flex(3),
528 g_pmeth_flex(4), g_pmeth_flex(5), g_pmeth_flex(6),
529 g_pmeth_flex(7), g_pmeth_flex(8), g_pmeth_flex(9),
530 g_pmeth_flex(10), g_pmeth_flex(11), g_pmeth_flex(12),
531 g_pmeth_flex(13), g_pmeth_flex(14), g_pmeth_flex(15),
532 g_pmeth_flex(16), g_pmeth_flex(17), g_pmeth_flex(18),
533 g_pmeth_flex(19), g_pmeth_flex(20);
534
535 CLOSE get_org_bank_details;
536
537 FOR cntr IN 1..30 LOOP
538 IF g_org_flex(cntr) IS NOT NULL THEN
539 pay_payroll_xml_extract_pkg.load_xml('D','Segment'||cntr,g_org_flex(cntr));
540 END IF;
541 END LOOP;
542 IF (lv_pmeth_cat IS NOT NULL)
543 THEN
544 FOR cntr IN 1..20 LOOP
545 IF g_pmeth_flex(cntr) IS NOT NULL THEN
546 pay_payroll_xml_extract_pkg.load_xml('D', 'Paymeth Developer DF',
547 lv_pmeth_cat, 'PMETH_INFORMATION'||cntr,g_pmeth_flex(cntr));
548 END IF;
549 END LOOP;
550 END IF;
551 pay_payroll_xml_extract_pkg.load_xml('D','DEPOSIT_DATE',lv_dd_date);
552 pay_payroll_xml_extract_pkg.load_xml('D','CURRENCY',g_currency_code);
553
554 IF pay_payroll_xml_extract_pkg.g_xml_table.count() <> 0 THEN
555 dbms_lob.createTemporary(l_xml, true, dbms_lob.session);
556 FOR cntr IN
557 pay_payroll_xml_extract_pkg.g_xml_table.first()..pay_payroll_xml_extract_pkg.g_xml_table.last() LOOP
558 lr_xml := utl_raw.cast_to_raw(
559 pay_payroll_xml_extract_pkg.g_xml_table(cntr));
560 ln_amt := utl_raw.length(lr_xml);
561
562 dbms_lob.writeAppend(l_xml,
563 ln_amt,
564 lr_xml);
565
566 hr_utility_trace (pay_payroll_xml_extract_pkg.g_xml_table(cntr));
567 END LOOP;
568 pay_payroll_xml_extract_pkg.g_xml_table.delete();
569
570 END IF;
571 write_to_magtape_lob (l_xml);
572 dbms_lob.freeTemporary(l_xml);
573
574 pay_payroll_xml_extract_pkg.g_custom_params.DELETE;
575 ln_param_count := pay_payroll_xml_extract_pkg.g_custom_params.COUNT;
576 pay_payroll_xml_extract_pkg.g_custom_params(ln_param_count).parameter_name := 'p_xml_level';
577 pay_payroll_xml_extract_pkg.g_custom_params(ln_param_count).parameter_value := 'ER';
578
579 /*Employee Information -Legislation Specific*/
580 EXECUTE IMMEDIATE 'BEGIN PAY_'||pay_payroll_xml_extract_pkg.g_leg_code||
581 '_RULES.add_custom_xml(:1,:2,:3); END;'
582 USING IN ln_payroll_action_id,'','DEPOSIT_XML';
583
584
585
586 hr_utility_trace ('BLOB contents after appending header information');
587 print_blob (pay_mag_tape.g_blob_value);
588
589 hr_utility_trace ('Leaving '||l_proc_name);
590 END get_deposit_header;
591
592 /****************************************************************************
593 Name : GEN_XML_FOOTER
594 Description : This procedure generates XML information for GRE and the final
595 closing tag. Final result is appended to
596 pay_mag_tape.g_blob_value.
597 *****************************************************************************/
598 PROCEDURE get_deposit_footer AS
599 lv_buf varchar2(2000);
600 l_proc_name varchar2(200);
601 BEGIN
602 l_proc_name := g_proc_name || 'GET_EMPLOYER_FOOTER';
603 hr_utility_trace ('Entering '||l_proc_name);
604
605 lv_buf := '</DEPOSIT_HEADER>';
606
607 write_to_magtape_lob (lv_buf);
608
609 hr_utility_trace ('BLOB contents after appending footer information');
610 print_blob (pay_mag_tape.g_blob_value);
611
612 hr_utility_trace ('Leaving '||l_proc_name);
613 END get_deposit_footer;
614
615 PROCEDURE GET_FOOTERS AS
616 lv_buf varchar2(2000);
617 l_proc_name varchar2(200);
618 BEGIN
619 l_proc_name := g_proc_name || 'GEN_XML_FOOTER';
620 hr_utility_trace ('Entering '||l_proc_name);
621
622 lv_buf := '</DIRECT_DEPOSIT>' ;
623
624 write_to_magtape_lob (lv_buf);
625
626 hr_utility_trace ('BLOB contents after appending footer information');
627 print_blob (pay_mag_tape.g_blob_value);
628
629 hr_utility_trace ('Leaving '||l_proc_name);
630 END GET_FOOTERS;
631
632 BEGIN
633 g_proc_name := 'pay_direct_deposit_xml.';
634 g_debug := hr_utility.debug_enabled;
635 END pay_direct_deposit_xml;