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