[Home] [Help]
PACKAGE BODY: APPS.PAY_PAYMENT_XML_PKG
Source
1 PACKAGE BODY PAY_PAYMENT_XML_PKG AS
2 /* $Header: pypayxml.pkb 120.19.12010000.1 2008/07/27 23:19:40 appldev ship $ */
3 --
4 -- Global Variables
5 --
6 TYPE char_tab IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
7 g_xml_cache char_tab;
8 g_proc_name varchar2(240);
9 g_debug boolean;
10 g_leg_code varchar2(5);
11 g_person_flex char_tab;
12 g_org_flex char_tab;
13 g_source_bank char_tab;
14 g_per_info char_tab;
15 g_opm_info char_tab;
16 --
17 CURSOR c_get_leg_code (p_business_group_id NUMBER)
18 IS
19 SELECT legislation_code
20 FROM per_business_groups
21 WHERE business_group_id = p_business_group_id;
22 --
23 -- Internal procedures
24 --
25 ------------------------------------------------------------------------------
26 -- Name : HR_UTILITY_TRACE
27 -- Description : This procedure prints debug messages.
28 ------------------------------------------------------------------------------
29 PROCEDURE HR_UTILITY_TRACE(P_TRC_DATA varchar2)
30 AS
31 BEGIN
32 IF g_debug THEN
33 hr_utility.trace(p_trc_data);
34 END IF;
35 END HR_UTILITY_TRACE;
36 ------------------------------------------------------------------------------
37 -- Name : PRINT_CLOB
38 -- Description : This procedure prints contents of a CLOB object passed as
39 -- parameter.
40 ------------------------------------------------------------------------------
41 PROCEDURE PRINT_CLOB(P_CLOB CLOB)
42 AS
43 l_chars number;
44 l_offset number;
45 l_buf varchar2(255);
46 BEGIN
47 l_chars := 255;
48 l_offset := 1;
49 LOOP
50 dbms_lob.read(p_clob,
51 l_chars,
52 l_offset,
53 l_buf);
54 --
55 hr_utility_trace(l_buf);
56 l_offset := l_offset + 255;
57 l_chars:= 255;
58 END LOOP;
59 --
60 EXCEPTION
61 WHEN NO_DATA_FOUND THEN
62 hr_utility_trace ('CLOB contents end.');
63 --
64 END PRINT_CLOB;
65
66 FUNCTION convert_uppercase(p_input_string varchar2)
67 RETURN varchar2 IS
68 --
69 l_output_string varchar2(2000);
70
71 -- converts the french accented characters to American English
72 -- in uppercase, used for direct deposit mag tape data
73 cursor c_uppercase(cp_input_string varchar2) is
74 select
75 replace(
76 replace(
77 replace(
78 replace(
79 replace(
80 replace(
81 replace(
82 replace(
83 replace(
84 replace(
85 replace(
86 replace(
87 replace(
88 replace(
89 replace(convert(upper(cp_input_string),'UTF8'),
90 utl_raw.cast_to_varchar2(hextoraw('C380')),'A'),
91 utl_raw.cast_to_varchar2(hextoraw('C38A')),'E'),
92 utl_raw.cast_to_varchar2(hextoraw('C387')),'C'),
93 utl_raw.cast_to_varchar2(hextoraw('C389')),'E'),
94 utl_raw.cast_to_varchar2(hextoraw('C39C')),'U'),
95 utl_raw.cast_to_varchar2(hextoraw('C399')),'U'),
96 utl_raw.cast_to_varchar2(hextoraw('C39B')),'U'),
97 utl_raw.cast_to_varchar2(hextoraw('C394')),'O'),
98 utl_raw.cast_to_varchar2(hextoraw('C38F')),'I'),
99 utl_raw.cast_to_varchar2(hextoraw('C38E')),'I'),
100 utl_raw.cast_to_varchar2(hextoraw('C388')),'E'),
101 utl_raw.cast_to_varchar2(hextoraw('C38B')),'E'),
102 utl_raw.cast_to_varchar2(hextoraw('C382')),'A'),
103 utl_raw.cast_to_varchar2(hextoraw('C592')),'OE'),
104 utl_raw.cast_to_varchar2(hextoraw('C386')),'AE')
105 from dual;
106
107 begin
108 open c_uppercase(p_input_string);
109 fetch c_uppercase into l_output_string;
110 if c_uppercase%NOTFOUND then
111 l_output_string := p_input_string;
112 end if;
113 close c_uppercase;
114
115 return l_output_string;
116
117 end convert_uppercase;
118
119
120 PROCEDURE LOAD_XML (
121 P_NODE_TYPE varchar2,
122 P_NODE varchar2,
123 P_DATA varchar2
124 ) AS
125
126 CURSOR csr_get_tag_name (p_id_flex_structure_code varchar2) IS
127 SELECT TRANSLATE (UPPER(seg.segment_name), ' /','__')
128 FROM fnd_id_flex_structures_vl ctx,
129 fnd_id_flex_segments_vl seg
130 WHERE ctx.id_flex_num = seg.id_flex_num
131 AND ctx.id_flex_code = seg.id_flex_code
132 AND seg.id_flex_code = 'BANK'
133 AND ctx.id_flex_structure_code = p_id_flex_structure_code
134 AND seg.application_column_name = UPPER(p_node);
135
136 l_proc_name varchar2(100);
137 l_tag_name varchar2(500);
138 l_struct_code fnd_id_flex_structures.id_flex_structure_code%type;
139 l_data pay_action_information.action_information1%type;
140 BEGIN
141
142 IF p_node_type = 'D' THEN
143 OPEN csr_get_tag_name (g_leg_code|| '_BANK_DETAILS');
144 FETCH csr_get_tag_name INTO l_tag_name;
145 CLOSE csr_get_tag_name;
146
147 l_tag_name:=convert_uppercase(l_tag_name);
148 END IF;
149
150 IF UPPER(p_node) NOT LIKE '?XML%' AND UPPER(p_node) NOT LIKE 'XAPI%' THEN
151 l_tag_name := nvl(l_tag_name, TRANSLATE(p_node,' /', '__'));
152 IF p_node_type IN ('CS', 'CE') THEN
153 l_tag_name := TRANSLATE(p_node, ' /', '__');
154 END IF;
155 ELSE
156 l_tag_name := p_node;
157 END IF;
158
159 IF p_node_type = 'CS' THEN
160 pay_core_files.write_to_magtape_lob('<'||l_tag_name||'>');
161 ELSIF p_node_type = 'CE' THEN
162 pay_core_files.write_to_magtape_lob('</'||l_tag_name||'>');
163 ELSIF p_node_type = 'D' THEN
164 /* Handle special charaters in data */
165 l_data := REPLACE (p_data, '&', '&');
166 l_data := REPLACE (l_data, '>', '>');
167 l_data := REPLACE (l_data, '<', '<');
168 l_data := REPLACE (l_data, '''', ''');
169 l_data := REPLACE (l_data, '"', '"');
170 pay_core_files.write_to_magtape_lob('<'||l_tag_name||'>'||l_data||'</'||l_tag_name||'>');
171 END IF;
172
173
174 END LOAD_XML;
175
176 PROCEDURE file_creation_no(p_pact_id in number)
177 AS
178 statem varchar2(2000);
179 sql_cursor integer;
180 l_rows integer;
181 file_no number;
182 found boolean:=false;
183 get_no varchar2(1):='N';
184 begin
185
186
187 pay_core_utils.get_legislation_rule(
188 'XML_FILE_CREATION_NO',
189 g_leg_code,
190 get_no,found);
191
192 if (found=true and get_no='Y')
193 then
194 statem := 'begin pay_'||g_leg_code||'_rules.get_file_creation_no(';
195 statem := statem||':pactid, :file_no); end;';
196 sql_cursor := dbms_sql.open_cursor;
197 dbms_sql.parse(sql_cursor, statem, dbms_sql.v7);
198 dbms_sql.bind_variable(sql_cursor, 'pactid', p_pact_id);
199 dbms_sql.bind_variable(sql_cursor, 'file_no', file_no);
200 l_rows := dbms_sql.execute (sql_cursor);
201 if (l_rows = 1) then
202 dbms_sql.variable_value(sql_cursor, 'file_no', file_no);
203 dbms_sql.close_cursor(sql_cursor);
204 load_xml('D','FILE_CREATION_NO',file_no);
205 end if;
206 end if;
207
208
209 END file_creation_no;
210
211
212 procedure get_opm_segment_name(p_org_meth_id in number,p_eff_date in date,p_tag_name in out nocopy varchar2)
213 as
214
215 CURSOR csr_get_tag_name (p_org_meth_id number,p_eff_date date ,p_tag_name varchar2) IS
216 select fcu.end_user_column_name
217 from pay_org_payment_methods_f opm
218 ,fnd_descr_flex_column_usages fcu
219 where opm.org_payment_method_id=p_org_meth_id
220 and fcu.descriptive_flex_context_code=opm.pmeth_information_category
221 and fcu.application_column_name =p_tag_name
222 and p_eff_date between opm.effective_start_date and effective_end_date;
223 begin
224
225 OPEN csr_get_tag_name (p_org_meth_id,p_eff_date,p_tag_name);
226 FETCH csr_get_tag_name INTO p_tag_name;
227 CLOSE csr_get_tag_name;
228
229 end get_opm_segment_name;
230
231
232 procedure get_ppm_segment_name(p_per_meth_id in number,p_eff_date in date,p_tag_name in out nocopy varchar2)
233 as
234
235 CURSOR csr_get_tag_name (p_per_meth_id number,p_eff_date date ,p_tag_name varchar2) IS
236 select fcu.end_user_column_name
237 from pay_personal_payment_methods_f ppm
238 ,fnd_descr_flex_column_usages fcu
239 where ppm.personal_payment_method_id=p_per_meth_id
240 and fcu.descriptive_flex_context_code=ppm.ppm_information_category
241 and fcu.application_column_name =p_tag_name
242 and p_eff_date between ppm.effective_start_date and effective_end_date;
243 begin
244
245 OPEN csr_get_tag_name (p_per_meth_id,p_eff_date,p_tag_name);
246 FETCH csr_get_tag_name INTO p_tag_name;
247 CLOSE csr_get_tag_name;
248
249 end get_ppm_segment_name;
250
251 --
252 -- External procedures
253 --
254 ------------------------------------------------------------------------------
255 -- Name : gen_header_xml
256 -- Description : This procedure generates the xml header. There will be 1 per
257 -- xml file.
258 ------------------------------------------------------------------------------
259 PROCEDURE gen_header_xml
260 AS
261 l_proc_name varchar2(50) := 'pay_payment_xml_pkg.gen_header_xml';
262 l_payroll_action_id number;
263 l_business_group_id number;
264 l_effective_date date;
265 --
266 BEGIN
267 hr_utility_trace('Entering '||l_proc_name);
268 --
269 l_payroll_action_id := pay_magtape_generic.get_parameter_value
270 ('TRANSFER_PAYROLL_ACTION_ID');
271 if (l_payroll_action_id is null) then
272 l_payroll_action_id := pay_magtape_generic.get_parameter_value
273 ('PAYROLL_ACTION_ID');
274
275 end if;
276 l_business_group_id := pay_magtape_generic.get_parameter_value
277 ('BG_ID');
278 --
279 hr_utility_trace ('l_payroll_action_id '||l_payroll_action_id);
280 hr_utility_trace ('l_business_group_id '||l_business_group_id);
281 --
282 load_xml('CS','PAYMENT_HEADER_FOOTER','');
283 --
284 file_creation_no(l_payroll_action_id);
285 hr_utility_trace ('CLOB contents after appending header information');
286 --print_clob (pay_mag_tape.g_clob_value);
287
288 hr_utility_trace('Leaving '||l_proc_name);
289 END gen_header_xml;
290
291 PROCEDURE gen_bank_header_xml
292 AS
293 CURSOR get_bank_details(p_ext_act_id in NUMBER)
294 IS
295 SELECT pay_ce_support_pkg.bank_segment_value(pea.external_account_id,'BANK_NAME', pea.territory_code),
296 pay_ce_support_pkg.bank_segment_value(pea.external_account_id,'BANK_BRANCH', pea.territory_code),
297 pay_ce_support_pkg.bank_segment_value(pea.external_account_id,'BANK_ACCOUNT_NAME', pea.territory_code),
298 pay_ce_support_pkg.bank_segment_value(pea.external_account_id,'BANK_ACCOUNT_NUMBER', pea.territory_code),
299 pay_ce_support_pkg.bank_segment_value(pea.external_account_id,'TRANSIT_CODE', pea.territory_code),
300 pay_ce_support_pkg.bank_segment_value(pea.external_account_id,'SWIFT_CODE', pea.territory_code),
301 pay_ce_support_pkg.bank_segment_value(pea.external_account_id,'INTL_BANK_CODE', pea.territory_code)
302 FROM pay_external_accounts pea
303 WHERE p_ext_act_id = pea.external_account_id;
304
305 l_proc_name varchar2(50) := 'pay_payment_xml_pkg.gen_bank_header_xml';
306 l_bank_name varchar2(2000);
307 l_branch_name varchar2(2000);
308 l_account_name varchar2(2000);
309 l_account_number varchar2(2000);
310 l_transit_code varchar(2000);
311 l_swift_code varchar(2000);
312 l_intl_bank_code varchar(2000);
313 l_ext_act_id number;
314 --
315 BEGIN
316 hr_utility_trace('Entering '||l_proc_name);
317
318 l_ext_act_id := pay_magtape_generic.get_parameter_value('EXT_ACT_ID');
319
320 OPEN get_bank_details(l_ext_act_id);
321 FETCH get_bank_details INTO
322 l_bank_name,l_branch_name,l_account_name,l_account_number,l_transit_code,
323 l_swift_code,l_intl_bank_code;
324 CLOSE get_bank_details;
325
326 load_xml('CS','GRP_PAYMENT_SOURCE_BANK','');
327 load_xml('D','BANK_NAME' , l_bank_name);
328 load_xml('D','BRANCH_NAME' , l_branch_name);
329 load_xml('D','ACCOUNT_NAME' , l_account_name);
330 load_xml('D','ACCOUNT_NUMBER' , l_account_number);
331 load_xml('D','TRANSIT_CODE' , l_transit_code);
332 load_xml('D','SWIFT_CODE' , l_swift_code);
333 load_xml('D','INTL_BANK_CODE' , l_intl_bank_code);
334
335 hr_utility_trace('Leaving '||l_proc_name);
336
337 end gen_bank_header_xml;
338
339 PROCEDURE gen_bank_footer_xml
340 AS
341 l_proc_name varchar2(50) := 'pay_payment_xml_pkg.gen_bank_footer_xml';
342 BEGIN
343 hr_utility_trace('Entering '||l_proc_name);
344
345 load_xml('CE','GRP_PAYMENT_SOURCE_BANK','');
346
347 hr_utility_trace('Leaving '||l_proc_name);
348 end gen_bank_footer_xml;
349
350 ------------------------------------------------------------------------------
351 -- Name : gen_footer_xml
352 -- Description : This procedure generates the xml footer. There will be 1 per
353 -- xml file.
354 ------------------------------------------------------------------------------
355 PROCEDURE gen_footer_xml
356 AS
357 l_proc_name varchar2(50) := 'pay_payment_xml_pkg.gen_footer_xml';
358 BEGIN
359 hr_utility_trace('Entering '||l_proc_name);
360
361 load_xml('CE','PAYMENT_HEADER_FOOTER','');
362
363 hr_utility_trace ('CLOB contents after appending footer information');
364 --print_clob (pay_mag_tape.g_clob_value);
365
366 hr_utility_trace('Leaving '||l_proc_name);
367 END gen_footer_xml;
368 ------------------------------------------------------------------------------
369 -- Name : gen_payment_details_xml
370 -- Description : This procedure generates the xml payment details. There will
371 -- be 1 per payment.
372 ------------------------------------------------------------------------------
373 PROCEDURE gen_payment_details_xml
374 AS
375 CURSOR get_org_bank_details(p_org_payment_method_id VARCHAR2,
376 p_effective_date date) IS
377 SELECT segment1 ,segment2 ,segment3
378 ,segment4 ,segment5 ,segment6 ,segment7
379 ,segment8 ,segment9 ,segment10 ,segment11
380 ,segment12 ,segment13 ,segment14 ,segment15
381 ,segment16 ,segment17 ,segment18 ,segment19
382 ,segment20 ,segment21 ,segment22 ,segment23
383 ,segment24 ,segment25 ,segment26 ,segment27
384 ,segment28 ,segment29 ,segment30 ,popm.currency_code,
385 pay_ce_support_pkg.bank_segment_value(pea.external_account_id,'BANK_NAME', pea.territory_code),
386 pay_ce_support_pkg.bank_segment_value(pea.external_account_id,'BANK_BRANCH', pea.territory_code),
387 pay_ce_support_pkg.bank_segment_value(pea.external_account_id,'BANK_ACCOUNT_NAME', pea.territory_code),
388 pay_ce_support_pkg.bank_segment_value(pea.external_account_id,'BANK_ACCOUNT_NUMBER', pea.territory_code),
389 pay_ce_support_pkg.bank_segment_value(pea.external_account_id,'TRANSIT_CODE', pea.territory_code),
390 pay_ce_support_pkg.bank_segment_value(pea.external_account_id,'SWIFT_CODE', pea.territory_code),
391 pay_ce_support_pkg.bank_segment_value(pea.external_account_id,'INTL_BANK_CODE', pea.territory_code)
392 FROM pay_org_payment_methods_f popm
393 , pay_external_accounts pea
394 WHERE org_payment_method_id = p_org_payment_method_id
395 AND popm.external_account_id = pea.external_account_id
396 AND p_effective_date between popm.EFFECTIVE_START_DATE
397 and popm.EFFECTIVE_END_DATE;
398 --
399 CURSOR get_person_bank_details(p_per_pay_method NUMBER
400 ,p_effective_date DATE)
401 IS
402 SELECT segment1 ,segment2 ,segment3
403 ,segment4 ,segment5 ,segment6 ,segment7
404 ,segment8 ,segment9 ,segment10 ,segment11
405 ,segment12 ,segment13 ,segment14 ,segment15
406 ,segment16 ,segment17 ,segment18 ,segment19
407 ,segment20 ,segment21 ,segment22 ,segment23
408 ,segment24 ,segment25 ,segment26 ,segment27
409 ,segment28 ,segment29 ,segment30 ,org_payment_method_id,
410 pay_ce_support_pkg.bank_segment_value(pea.external_account_id,'BANK_NAME', pea.territory_code),
411 pay_ce_support_pkg.bank_segment_value(pea.external_account_id,'BANK_BRANCH', pea.territory_code),
412 pay_ce_support_pkg.bank_segment_value(pea.external_account_id,'BANK_ACCOUNT_NAME', pea.territory_code),
413 pay_ce_support_pkg.bank_segment_value(pea.external_account_id,'BANK_ACCOUNT_NUMBER', pea.territory_code),
414 pay_ce_support_pkg.bank_segment_value(pea.external_account_id,'TRANSIT_CODE', pea.territory_code),
415 pay_ce_support_pkg.bank_segment_value(pea.external_account_id,'SWIFT_CODE', pea.territory_code),
416 pay_ce_support_pkg.bank_segment_value(pea.external_account_id,'INTL_BANK_CODE', pea.territory_code),
417 pppm.payee_id,pppm.payee_type
418 FROM pay_personal_payment_methods_f pppm
419 , pay_external_accounts pea
420 WHERE pppm.personal_payment_method_id = p_per_pay_method
421 AND pppm.external_account_id = pea.external_account_id
422 AND p_effective_date between pppm.EFFECTIVE_START_DATE
423 and pppm.EFFECTIVE_END_DATE;
424 --
425 CURSOR get_orgpayee_bank_details(p_org_pay_method NUMBER
426 ,p_effective_date DATE)
427 IS
428 SELECT segment1 ,segment2 ,segment3
429 ,segment4 ,segment5 ,segment6 ,segment7
430 ,segment8 ,segment9 ,segment10 ,segment11
431 ,segment12 ,segment13 ,segment14 ,segment15
432 ,segment16 ,segment17 ,segment18 ,segment19
433 ,segment20 ,segment21 ,segment22 ,segment23
434 ,segment24 ,segment25 ,segment26 ,segment27
435 ,segment28 ,segment29 ,segment30 ,org_payment_method_id,
436 pay_ce_support_pkg.bank_segment_value(pea.external_account_id,'BANK_NAME', pea.territory_code),
437 pay_ce_support_pkg.bank_segment_value(pea.external_account_id,'BANK_BRANCH', pea.territory_code),
438 pay_ce_support_pkg.bank_segment_value(pea.external_account_id,'BANK_ACCOUNT_NAME', pea.territory_code),
439 pay_ce_support_pkg.bank_segment_value(pea.external_account_id,'BANK_ACCOUNT_NUMBER', pea.territory_code),
440 pay_ce_support_pkg.bank_segment_value(pea.external_account_id,'TRANSIT_CODE', pea.territory_code),
441 pay_ce_support_pkg.bank_segment_value(pea.external_account_id,'SWIFT_CODE', pea.territory_code),
442 pay_ce_support_pkg.bank_segment_value(pea.external_account_id,'INTL_BANK_CODE', pea.territory_code)
443 FROM pay_org_payment_methods_f popm
444 , pay_external_accounts pea
445 WHERE org_payment_method_id = p_org_pay_method
446 AND popm.external_account_id = pea.external_account_id
447 AND p_effective_date between popm.EFFECTIVE_START_DATE
448 and popm.EFFECTIVE_END_DATE;
449
450 CURSOR get_third_party_payee_details(p_person_id in number
451 ,p_effective_date in date)
452 IS
453 SELECT first_name
454 , last_name
455 , order_name
456 , full_name
457 , middle_names
458 , title
459 FROM per_all_people_f
460 where person_id=p_person_id
461 and p_effective_date between effective_start_date and effective_end_date;
462
463
464 CURSOR get_employee_details(p_assignment_id in number
465 ,p_effective_date in date)
466 IS
467 SELECT ppf.first_name
468 , ppf.last_name
469 , ppf.order_name
470 , ppf.full_name
471 , ppf.national_identifier
472 , ppf.employee_number
473 , pj.name
474 , hou.name
475 , paf.payroll_id
476 , prl.payroll_name
477 , ppf.middle_names
478 , ppf.title
479 , paf.assignment_number
480 FROM per_all_assignments_f paf
481 , per_all_people_f ppf
482 , per_periods_of_service pps
483 , per_jobs pj
484 , hr_organization_units hou
485 , pay_payrolls_f prl
486 WHERE paf.person_id = ppf.person_id
487 and paf.assignment_id = p_assignment_id
488 AND paf.job_id = pj.job_id(+)
489 and paf.organization_id = hou.organization_id
490 and prl.payroll_id=paf.payroll_id
491 and p_effective_date between paf.effective_start_date
492 and paf.effective_end_date
493 and p_effective_date between ppf.effective_start_date
494 and ppf.effective_end_date
495 and p_effective_date between prl.effective_start_date
496 and prl.effective_end_date
497 and pps.person_id = ppf.person_id
498 and pps.date_start = (select max(pps1.date_start)
499 from per_periods_of_service pps1
500 where pps1.person_id = paf.person_id
501 and pps1.date_start <= p_effective_date);
502 --
503 CURSOR get_org_payee_details(p_organization_id in number)
504 IS
505 SELECT hou.name
506 from hr_organization_units hou
507 WHERE hou.organization_id=p_organization_id;
508
509 CURSOR get_payroll_details(p_prepru_pact_id in number)
510 IS
511 SELECT ppa.start_date
512 FROM pay_payroll_actions ppa
513 , pay_payrolls_f pp
514 WHERE ppa.payroll_action_id = p_prepru_pact_id
515 and pp.payroll_id = ppa.payroll_id
516 and ppa.effective_date between pp.effective_start_date
517 and pp.effective_end_date;
518
519 CURSOR get_action_details(p_asg_act in number)
520 is
521 select nvl(paa.serial_number,'-9999'),
522 substr(fnd_date.date_to_canonical(ppa.effective_date),1,10),
523 substr(nvl(fnd_date.date_to_canonical(ppa.overriding_dd_date),fnd_date.date_to_canonical(ppa.effective_date)),1,10),
524 ppa.payroll_action_id
525 from pay_assignment_actions paa,pay_payroll_actions ppa
526 where paa.assignment_action_id = p_asg_act
527 and paa.payroll_action_id=ppa.payroll_action_id;
528
529
530 CURSOR csr_get_earn(p_bg_id in number,p_leg_code in varchar,p_asg_act in number)
531 is
532 select nvl(pbt.reporting_name,pbt.balance_name),
533 pay_balance_pkg.get_value(pba.defined_balance_id,p_asg_act)
534 from pay_balance_attributes pba
535 , pay_bal_attribute_definitions pbad
536 , pay_defined_balances pdb
537 , pay_balance_types pbt
538 where pbad.attribute_name='PAYMENT_EARNINGS'
539 and pba.attribute_id=pbad.attribute_id
540 and (nvl(pba.legislation_code,'XXX')=p_leg_code
541 or
542 nvl(pba.business_group_id,-999)=p_bg_id)
543 and pba.defined_balance_id=pdb.defined_balance_id
544 and pbt.balance_type_id = pdb.balance_type_id;
545
546 CURSOR csr_get_dedn(p_bg_id in number,p_leg_code in varchar,p_asg_act in number)
547 is
548 select nvl(pbt.reporting_name,pbt.balance_name),
549 pay_balance_pkg.get_value(pba.defined_balance_id,p_asg_act)
550 from pay_balance_attributes pba
551 , pay_bal_attribute_definitions pbad
552 , pay_defined_balances pdb
553 , pay_balance_types pbt
554 where pbad.attribute_name='PAYMENT_DEDUCTIONS'
555 and pba.attribute_id=pbad.attribute_id
556 and (nvl(pba.legislation_code,'XXX')=p_leg_code
557 or
558 nvl(pba.business_group_id,-999)=p_bg_id)
559 and pba.defined_balance_id=pdb.defined_balance_id
560 and pbt.balance_type_id = pdb.balance_type_id;
561
562 CURSOR csr_leave_balance (p_assignment_id in number,p_eff_date in date)
563 is
564 SELECT pap.accrual_plan_name
565 , pap.accrual_plan_id
566 FROM pay_accrual_plans pap
567 ,pay_element_types_f pet
568 ,pay_element_links_f pel
569 ,pay_element_entries_f pee
570 WHERE pet.element_type_id = pap.accrual_plan_element_type_id
571 AND pel.element_type_id = pet.element_type_id
572 AND pee.element_link_id = pel.element_link_id
573 AND p_assignment_id = pee.assignment_id
574 and p_eff_date between pet.effective_start_date and pet.effective_end_date
575 and p_eff_date between pel.effective_start_date and pel.effective_end_date
576 and p_eff_date between pee.effective_start_date and pee.effective_end_date;
577
578 CURSOR csr_get_time_period(p_asg_act_id in number)
579 is
580 select min(ptp.start_date),max(ptp.end_date), max(paa.assignment_Action_id)
581 from pay_action_interlocks pai
582 ,pay_assignment_actions paa
583 ,pay_payroll_actions ppa
584 ,per_time_periods ptp
585 where p_asg_act_id=pai.locking_action_id
586 and pai.locked_action_id=paa.assignment_action_id
587 and paa.source_action_id is null
588 and ppa.payroll_action_id = paa.payroll_action_id
589 AND ppa.action_type IN ('R','Q')
590 AND ppa.action_status = 'C'
591 AND ppa.time_period_id = ptp.time_period_id;
592
593 CURSOR csr_ppm_info (p_ppm_id NUMBER,
594 p_effective_date DATE)
595 IS
596 SELECT ppm_information1 ,ppm_information2,ppm_information3,ppm_information4,ppm_information5,
597 ppm_information6,ppm_information7,ppm_information8,ppm_information9,ppm_information10,
598 ppm_information11,ppm_information12,ppm_information13,ppm_information14,ppm_information15,
599 ppm_information16,ppm_information17,ppm_information18,ppm_information19,ppm_information20,
600 ppm_information21,ppm_information22,ppm_information23,ppm_information24,ppm_information25,
601 ppm_information26,ppm_information27,ppm_information28,ppm_information29,ppm_information30
602 FROM pay_personal_payment_methods_f
603 WHERE personal_payment_method_id = p_ppm_id
604 and p_effective_date between effective_start_date and effective_end_date;
605
606 CURSOR csr_opm_info (p_opm_id NUMBER,
607 p_effective_date DATE)
608 IS
609 SELECT pmeth_information1 ,pmeth_information2,pmeth_information3,pmeth_information4,pmeth_information5,
610 pmeth_information6,pmeth_information7,pmeth_information8,pmeth_information9,pmeth_information10,
611 pmeth_information11,pmeth_information12,pmeth_information13,pmeth_information14,pmeth_information15,
612 pmeth_information16,pmeth_information17,pmeth_information18,pmeth_information19,pmeth_information20
613 FROM pay_org_payment_methods_f
614 WHERE org_payment_method_id = p_opm_id
615 and p_effective_date between effective_start_date and effective_end_date;
616 --
617 l_org_payment_method_id pay_personal_payment_methods_f.org_payment_method_id%TYPE;
618 l_first_name per_all_people_f.first_name%TYPE;
619 l_last_name per_all_people_f.last_name%TYPE;
620 l_order_name per_all_people_f.order_name%TYPE;
621 l_full_name per_all_people_f.full_name%TYPE;
622 l_national_identifier per_all_people_f.national_identifier%TYPE;
623 l_employee_number per_all_people_f.employee_number%TYPE;
624 l_middle_names per_all_people_f.middle_names%TYPE;
625 l_title per_all_people_f.title%TYPE;
626 l_business_group_id number;
627 l_per_pay_method number;
628 l_pre_pay_id number;
629 l_prepay_asg_act number;
630 l_payroll_start_date date;
631 l_payroll_end_date date;
632 l_cheque_no varchar2(30);
633 l_effective_date date;
634 l_payroll_name pay_payrolls_f.payroll_name%TYPE;
635 l_job per_jobs.name%TYPE;
636 l_employer hr_organization_units.name%TYPE;
637 l_payroll_id number;
638 l_chq_effective_date varchar2(30);
639 l_deposit_date varchar2(30);
640 l_assignment_action_id number;
641 l_tran_action_id number;
642 l_assignment_id number;
643 l_det_org_pay_method number;
644 l_org_meth number;
645 l_payee_meth number;
646 l_payee_org_id number;
647 l_pre_pru_pact_id number;
648 l_xml CLOB;
649 l_custom_ee_xml CLOB;
650 l_chars number;
651 l_offset number;
652 l_deposit_amount varchar2(30);
653 l_amount_in_words varchar2(2000);
654 l_amount_in_words_line1 varchar2(2000);
655 l_amount_in_words_line2 varchar2(2000);
656 l_buf varchar2(2000);
657 l_param_count number;
658 l_proc_name varchar2(50) := 'pay_payment_xml_pkg.gen_payment_details_xml';
659 l_leg_code varchar2(10);
660 l_bank_name varchar2(2000);
661 l_branch_name varchar2(2000);
662 l_account_name varchar2(2000);
663 l_account_number varchar2(2000);
664 l_transit_code varchar(2000);
665 l_swift_code varchar(2000);
666 l_intl_bank_code varchar(2000);
667 l_dbank_name varchar2(2000);
668 l_dbranch_name varchar2(2000);
669 l_daccount_name varchar2(2000);
670 l_daccount_number varchar2(2000);
671 l_dtransit_code varchar(2000);
672 l_dswift_code varchar(2000);
673 l_dintl_bank_code varchar(2000);
674 l_bal varchar2(50);
675 l_value number;
676 l_accrual_plan_id NUMBER;
677 l_start_date DATE;
678 l_end_date DATE;
679 l_accrual_end_date DATE;
680 l_accrual NUMBER;
681 l_annual_leave_balance NUMBER;
682 l_period_end_date DATE;
683 l_period_start_date DATE;
684 l_leave_taken NUMBER;
685 l_accrual_plan_name pay_accrual_plans.accrual_plan_name%TYPE;
686 l_run_aa_id number;
687 l_pactid NUMBER;
688 decimal_amount varchar2(2000);
689 found boolean:=false;
690 addtl_data varchar2(1):='N';
691 is_decimal number;
692 dest_bank number;
693 l_asg_num varchar2(50);
694 l_payee_id number;
695 l_payee_type varchar2(50);
696 currency_description varchar2(240);
697 currency_precision number;
698 CURR_NO_OF_DECIMALS number;
699 --
700 BEGIN
701 hr_utility_trace('Entering '||l_proc_name);
702 l_chars := 2000;
703 l_offset := 1;
704 --
705 l_tran_action_id := pay_magtape_generic.get_parameter_value
706 ('TRANSFER_ACT_ID');
707 l_assignment_id := pay_magtape_generic.get_parameter_value
708 ('ASG_ID');
709 l_effective_date := pay_magtape_generic.get_parameter_value
710 ('PRE_PAY_EFF_DATE');
711 l_deposit_amount := pay_magtape_generic.get_parameter_value
712 ('PAYMENT_AMOUNT');
713 l_business_group_id := pay_magtape_generic.get_parameter_value
714 ('DET_BG_ID');
715 l_per_pay_method := pay_magtape_generic.get_parameter_value
716 ('PERSONAL_PAY_METH');
717 l_pre_pay_id := pay_magtape_generic.get_parameter_value
718 ('PRE_PAY_ID');
719 l_prepay_asg_act := pay_magtape_generic.get_parameter_value
720 ('PRE_PAY_ASG_ACT');
721 l_org_meth := pay_magtape_generic.get_parameter_value
722 ('ORG_PAY_METHOD');
723 hr_utility.trace('l_org_meth: '||to_char(l_org_meth));
724 l_det_org_pay_method := pay_magtape_generic.get_parameter_value
725 ('DET_ORG_PAY_METH');
726 l_payee_meth := pay_magtape_generic.get_parameter_value
727 ('PAYEE_PAY_METH_ID');
728 l_payee_org_id := pay_magtape_generic.get_parameter_value
729 ('ORG_ID');
730 l_pre_pru_pact_id :=pay_magtape_generic.get_parameter_value
731 ('PRE_PRU_PAY_PACT_ID');
732
733 --
734 -- Get source bank details
735 --
736 IF g_source_bank.count <> 0 THEN
737 g_source_bank.delete;
738 END IF;
739 --
740 IF g_leg_code IS NULL THEN
741 OPEN c_get_leg_code(l_business_group_id);
742 FETCH c_get_leg_code INTO l_leg_code;
743 g_leg_code := l_leg_code;
744 CLOSE c_get_leg_code;
745 END IF;
746 --
747 hr_utility_trace ('Legislation Code '||
748 g_leg_code);
749 --
750 OPEN get_org_bank_details(l_det_org_pay_method,l_effective_date);
751 FETCH get_org_bank_details INTO
752 g_source_bank(1),g_source_bank(2),g_source_bank(3),g_source_bank(4),
753 g_source_bank(5),g_source_bank(6),g_source_bank(7),g_source_bank(8),
754 g_source_bank(9),g_source_bank(10),g_source_bank(11),g_source_bank(12),
755 g_source_bank(13),g_source_bank(14),g_source_bank(15),g_source_bank(16),
756 g_source_bank(17),g_source_bank(18),g_source_bank(19),g_source_bank(20),
757 g_source_bank(21),g_source_bank(22),g_source_bank(23),g_source_bank(24),
758 g_source_bank(25),g_source_bank(26),g_source_bank(27),g_source_bank(28),
759 g_source_bank(29),g_source_bank(30), g_currency_code,
760 l_bank_name,l_branch_name,l_account_name,l_account_number,l_transit_code,
761 l_swift_code,l_intl_bank_code;
762 CLOSE get_org_bank_details;
763
764 --
765 -- Convert l_deposit_amount in correct number of decimals
766 --
767 select instr(l_deposit_amount,'.'), precision
768 into is_decimal, currency_precision
769 from fnd_currencies fc,
770 fnd_currencies_tl fctl
771 where fc.currency_code=g_currency_code
772 and fc.currency_code=fctl.currency_code
773 and fctl.language=userenv('lang');
774 --
775 if (is_decimal<>0)
776 then
777 select length(substr(l_deposit_amount, is_decimal+1, length(l_deposit_amount)))
778 into curr_no_of_decimals
779 from dual;
780
781 while(curr_no_of_decimals <> currency_precision) loop
782 select rpad(l_deposit_amount, length(l_deposit_amount)+ 1, '0')
783 into l_deposit_amount
784 from dual;
785 curr_no_of_decimals := curr_no_of_decimals+1;
786 end loop;
787 end if;
788 --
789 --
790 if l_deposit_amount > 5373484 or l_deposit_amount <1 then
791 select
792 instr(l_deposit_amount,'.'),
793 substr(l_deposit_amount,-1*precision,precision)
794 ,nvl(fctl.description,fc.currency_code)
795 into is_decimal,decimal_amount,currency_description
796 from fnd_currencies fc,
797 fnd_currencies_tl fctl
798 where fc.currency_code=g_currency_code
799 and fc.currency_code=fctl.currency_code
800 and fctl.language=userenv('lang');
801 l_amount_in_words := null;
802 else
803 select to_char(to_Date (substr(to_char(trunc(l_deposit_amount)), 1), 'j'), 'jsp'),
804 instr(l_deposit_amount,'.'),
805 substr(l_deposit_amount,-1*precision,precision)
806 ,nvl(fctl.description,fc.currency_code)
807 into l_amount_in_words,is_decimal,decimal_amount,currency_description
808 from fnd_currencies fc ,
809 fnd_currencies_tl fctl
810 where fc.currency_code=g_currency_code
811 and fc.currency_code=fctl.currency_code
812 and fctl.language=userenv('lang');
813 end if;
814
815 if is_decimal<>0
816 then
817 l_amount_in_words:= l_amount_in_words || ' ' ||currency_description||' and '||decimal_amount;
818 else
819 l_amount_in_words:= l_amount_in_words || ' ' ||currency_description;
820 end if;
821 /* need to wrap over 2 lines */
822 l_amount_in_words_line1:= substr(l_amount_in_words,1,59);
823 l_amount_in_words_line2:=substr(l_amount_in_words,59);
824 --
825 -- Clear the details of previous assignmentId
826 --
827 IF g_person_flex.count <> 0 THEN
828 g_person_flex.delete;
829 END IF;
830 --
831 -- Get Personal Bank Details
832 --
833 --or org payee details
834 --
835 if l_payee_meth is null
836 then
837 if (l_per_pay_method is not null) then
838 OPEN get_person_bank_details(l_per_pay_method,l_effective_date);
839 FETCH get_person_bank_details INTO
840 g_person_flex(1),g_person_flex(2),g_person_flex(3),g_person_flex(4),
841 g_person_flex(5),g_person_flex(6),g_person_flex(7),g_person_flex(8),
842 g_person_flex(9),g_person_flex(10),g_person_flex(11),g_person_flex(12),
843 g_person_flex(13),g_person_flex(14),g_person_flex(15),g_person_flex(16),
844 g_person_flex(17),g_person_flex(18),g_person_flex(19),g_person_flex(20),
845 g_person_flex(21),g_person_flex(22),g_person_flex(23),g_person_flex(24),
846 g_person_flex(25),g_person_flex(26),g_person_flex(27),g_person_flex(28),
847 g_person_flex(29),g_person_flex(30),l_org_payment_method_id,
848 l_dbank_name,l_dbranch_name,l_daccount_name,l_daccount_number,l_dtransit_code,
849 l_dswift_code,l_dintl_bank_code,l_payee_id,l_payee_type;
850 CLOSE get_person_bank_details;
851 end if;
852 else
853 OPEN get_orgpayee_bank_details(l_payee_meth,l_effective_date);
854 FETCH get_orgpayee_bank_details INTO
855 g_person_flex(1),g_person_flex(2),g_person_flex(3),g_person_flex(4),
856 g_person_flex(5),g_person_flex(6),g_person_flex(7),g_person_flex(8),
857 g_person_flex(9),g_person_flex(10),g_person_flex(11),g_person_flex(12),
858 g_person_flex(13),g_person_flex(14),g_person_flex(15),g_person_flex(16),
859 g_person_flex(17),g_person_flex(18),g_person_flex(19),g_person_flex(20),
860 g_person_flex(21),g_person_flex(22),g_person_flex(23),g_person_flex(24),
861 g_person_flex(25),g_person_flex(26),g_person_flex(27),g_person_flex(28),
862 g_person_flex(29),g_person_flex(30),l_org_payment_method_id,
863 l_dbank_name,l_dbranch_name,l_daccount_name,l_daccount_number,l_dtransit_code,
864 l_dswift_code,l_dintl_bank_code;
865 CLOSE get_orgpayee_bank_details;
866 end if;
867 --
868 -- Get Employee Details
869 -- or org details
870 --
871 if l_payee_meth is null
872 then
873 OPEN get_employee_details(l_assignment_id,l_effective_date);
874 FETCH get_employee_details INTO
875 l_first_name, l_last_name, l_order_name,
876 l_full_name , l_national_identifier,
877 l_employee_number,l_job,l_employer,l_payroll_id,l_payroll_name,
878 l_middle_names,l_title,l_asg_num;
879 CLOSE get_employee_details;
880 else
881 OPEN get_org_payee_details(l_payee_org_id);
882 FETCH get_org_payee_details INTO
883 l_full_name ;
884 CLOSE get_org_payee_details;
885
886 end if;
887
888 -- if third party payment , get third party payee name
889
890 if (l_payee_id is not null ) then
891
892 if l_payee_type='P' then
893 OPEN get_third_party_payee_details(l_payee_id,l_effective_date);
894 FETCH get_third_party_payee_details INTO
895 l_first_name, l_last_name, l_order_name,
896 l_full_name ,l_middle_names,l_title;
897 CLOSE get_third_party_payee_details;
898 elsif l_payee_type='O' then
899 OPEN get_org_payee_details(l_payee_id);
900 FETCH get_org_payee_details INTO
901 l_full_name ;
902 CLOSE get_org_payee_details;
903 end if;
904
905
906
907 end if;
908
909 -- get chq details
910 OPEN get_action_details(l_tran_action_id);
911 FETCH get_action_details INTO
912 l_cheque_no,l_chq_effective_date,l_deposit_date,l_pactid;
913 CLOSE get_action_details;
914 --
915 -- Get Payroll Details
916 ---`for pru aswell
917 --
918 OPEN get_payroll_details(l_pre_pru_pact_id);
919 FETCH get_payroll_details INTO l_payroll_start_date;
920 CLOSE get_payroll_details;
921 --
922 -- Build XML
923 --
924 load_xml('CS','PAYMENT_DETAILS','');
925 --
926 load_xml('CS','SOURCE_BANK','');
927 --
928 load_xml('D','BANK_NAME' , l_bank_name);
929 load_xml('D','BRANCH_NAME' , l_branch_name);
930 load_xml('D','ACCOUNT_NAME' , l_account_name);
931 load_xml('D','ACCOUNT_NUMBER' , l_account_number);
932 load_xml('D','TRANSIT_CODE' , l_transit_code);
933 load_xml('D','SWIFT_CODE' , l_swift_code);
934 load_xml('D','INTL_BANK_CODE' , l_intl_bank_code);
935 load_xml('CS','SEGMENT_DATA','');
936 FOR cntr IN 1..30 LOOP
937 IF g_source_bank(cntr) IS NOT NULL THEN
938 load_xml('D','Segment'||cntr ,g_source_bank(cntr));
939 END IF;
940 END LOOP;
941 load_xml('CE','SEGMENT_DATA','');
942 --
943 load_xml('CE','SOURCE_BANK','');
944 --
945 if g_person_flex.count <> 0 then
946 load_xml('CS','DESTINATION_BANK','');
947 --
948 load_xml('D','BANK_NAME' , l_dbank_name);
949 load_xml('D','BRANCH_NAME' , l_dbranch_name);
950 load_xml('D','ACCOUNT_NAME' , l_daccount_name);
951 load_xml('D','ACCOUNT_NUMBER' , l_daccount_number);
952 load_xml('D','TRANSIT_CODE' , l_dtransit_code);
953 load_xml('D','SWIFT_CODE' , l_dswift_code);
954 load_xml('D','INTL_BANK_CODE' , l_dintl_bank_code);
955 load_xml('CS','SEGMENT_DATA','');
956 FOR cntr IN 1..30 LOOP
957 IF g_person_flex(cntr) IS NOT NULL THEN
958 load_xml('D','Segment'||cntr ,g_person_flex(cntr));
959 END IF;
960 END LOOP;
961 load_xml('CE','SEGMENT_DATA','');
962 --
963 load_xml('CE','DESTINATION_BANK','');
964 else
965 load_xml('CS','DESTINATION_BANK','');
966 load_xml ('D','DEST_BANK_TAG' ,'No personal bank details for this payment type');
967 load_xml('CE','DESTINATION_BANK','');
968 end if;
969 load_xml('D','PAYROLL_NAME',l_payroll_name);
970 load_xml('D','EMPLOYEE_NUMBER' ,l_employee_number);
971 load_xml('D','FIRST_NAME',l_first_name);
972 load_xml('D','LAST_NAME',l_last_name);
973 load_xml('D','NAME',l_full_name);
974 load_xml('D','MIDDLE_NAMES',l_middle_names);
975 load_xml('D','TITLE',l_title);
976 load_xml('D','ASSIGNMENT_NUMBER',l_asg_num);
977 load_xml('D','CURRENCY',g_currency_code);
978 load_xml('D','PAYMENT_AMOUNT',l_deposit_amount);
979 load_xml('D','AMOUNT_CURRENCY','*** '||l_deposit_amount||' '||g_currency_code||' ***');
980 load_xml('D','PAYMENT_AMOUNT_WORDS',l_amount_in_words);
981 load_xml('D','PAYMENT_AMOUNT_WORDS_1',l_amount_in_words_line1);
982 if (l_amount_in_words_line2 is not null) then
983 load_xml('D','PAYMENT_AMOUNT_WORDS_2',l_amount_in_words_line2);
984 end if;
985 if (l_cheque_no <> '-9999')
986 then
987 load_xml('D','CHEQUE NUMBER',l_cheque_no);
988 end if;
989 load_xml('D','EFFECTIVE_DATE',l_chq_effective_date);
990 load_xml('D','DEPOSIT_DATE',l_deposit_date);
991
992 load_xml('D','JOB',l_job);
993 load_xml('D','EMPLOYER',l_employer);
994
995
996 if (l_per_pay_method is not null) then
997 OPEN csr_ppm_info(l_per_pay_method,l_effective_date);
998 FETCH csr_ppm_info INTO g_per_info(1),g_per_info(2),g_per_info(3),g_per_info(4),g_per_info(5),
999 g_per_info(6),g_per_info(7),g_per_info(8),g_per_info(9),g_per_info(10),
1000 g_per_info(11),g_per_info(12),g_per_info(13),g_per_info(14),g_per_info(15),
1001 g_per_info(16),g_per_info(17),g_per_info(18),g_per_info(19),g_per_info(20),
1002 g_per_info(21),g_per_info(22),g_per_info(23),g_per_info(24),g_per_info(25),
1003 g_per_info(26),g_per_info(27),g_per_info(28),g_per_info(29),g_per_info(30);
1004 CLOSE csr_ppm_info;
1005
1006 load_xml('CS','PERSONAL_PAYMENT_METHOD_INFO','');
1007 FOR cntr IN 1..30 LOOP
1008 IF g_per_info(cntr) IS NOT NULL THEN
1009 get_ppm_segment_name(l_per_pay_method,fnd_date.canonical_to_date(l_chq_effective_date),g_per_info(cntr));
1010 load_xml('D','INFORMATION'||cntr ,g_per_info(cntr));
1011 END IF;
1012 END LOOP;
1013 load_xml('CE','PERSONAL_PAYMENT_METHOD_INFO','');
1014 end if;
1015
1016
1017 OPEN csr_opm_info(l_det_org_pay_method,l_effective_date);
1018 FETCH csr_opm_info INTO g_opm_info(1),g_opm_info(2),g_opm_info(3),g_opm_info(4),g_opm_info(5),
1019 g_opm_info(6),g_opm_info(7),g_opm_info(8),g_opm_info(9),g_opm_info(10),
1020 g_opm_info(11),g_opm_info(12),g_opm_info(13),g_opm_info(14),g_opm_info(15),
1021 g_opm_info(16),g_opm_info(17),g_opm_info(18),g_opm_info(19),g_opm_info(20);
1022 CLOSE csr_opm_info;
1023
1024 load_xml('CS','ORG_PAYMENT_METHOD_INFO','');
1025 FOR cntr IN 1..20 LOOP
1026 IF g_opm_info(cntr) IS NOT NULL THEN
1027 get_opm_segment_name(l_det_org_pay_method,fnd_date.canonical_to_date(l_chq_effective_date),g_opm_info(cntr));
1028 load_xml('D','INFORMATION'||cntr ,g_opm_info(cntr));
1029 END IF;
1030 END LOOP;
1031 load_xml('CE','ORG_PAYMENT_METHOD_INFO','');
1032
1033
1034
1035
1036 pay_core_utils.get_legislation_rule(
1037 'ADDITIONAL_CHQ_DATA',
1038 g_leg_code,
1039 addtl_data,found);
1040
1041 If (found=true and upper(addtl_data)='Y')
1042 then
1043
1044 OPEN csr_get_time_period(l_prepay_asg_act);
1045
1046 FETCH csr_get_time_period into
1047 l_period_start_date
1048 ,l_period_end_date
1049 ,l_run_aa_id;
1050 close csr_get_time_period;
1051
1052 open csr_get_earn(l_business_group_id,g_leg_code,l_run_aa_id);
1053 LOOP
1054 FETCH csr_get_earn INTO l_bal,l_value;
1055 EXIT WHEN csr_get_earn%NOTFOUND;
1056
1057 load_xml('CS','EARNINGS','');
1058 load_xml('D','EARN_ELEMENT',l_bal);
1059 load_xml('D','EARN_VALUE' ,l_value);
1060 load_xml('CE','EARNINGS','');
1061
1062 END LOOP;
1063 CLOSE csr_get_earn;
1064
1065 open csr_get_dedn(l_business_group_id,g_leg_code,l_run_aa_id);
1066 LOOP
1067 FETCH csr_get_dedn INTO l_bal,l_value;
1068 EXIT WHEN csr_get_dedn%NOTFOUND;
1069
1070 load_xml('CS','DEDUCTIONS','');
1071 load_xml('D','DEDN_ELEMENT',l_bal);
1072 load_xml('D','DEDN_VALUE' ,l_value);
1073 load_xml('CE','DEDUCTIONS','');
1074
1075 END LOOP;
1076 CLOSE csr_get_dedn;
1077
1078 -- calc absences
1079
1080
1081 OPEN csr_leave_balance(l_assignment_id,l_effective_date);
1082 LOOP
1083 FETCH csr_leave_balance INTO
1084 l_accrual_plan_name
1085 ,l_accrual_plan_id;
1086
1087 EXIT WHEN csr_leave_balance%NOTFOUND;
1088 IF csr_leave_balance%FOUND THEN
1089 -- Call to get annual leave balance
1090 per_accrual_calc_functions.get_net_accrual
1091 (p_assignment_id => l_assignment_id -- number in
1092 ,p_plan_id => l_accrual_plan_id -- number in
1093 ,p_payroll_id => l_payroll_id -- number in
1094 ,p_business_group_id => l_business_group_id -- number in
1095 ,p_calculation_date => l_effective_date -- date in
1096 ,p_start_date => l_start_date -- date out
1097 ,p_end_date => l_end_date -- date out
1098 ,p_accrual_end_date => l_accrual_end_date -- date out
1099 ,p_accrual => l_accrual -- number out
1100 ,p_net_entitlement => l_annual_leave_balance -- number out
1101 );
1102
1103 IF l_annual_leave_balance IS NULL THEN
1104 l_annual_leave_balance := 0;
1105 END IF;
1106
1107 l_leave_taken := per_accrual_calc_functions.get_absence
1108 (l_assignment_id
1109 ,l_accrual_plan_id
1110 ,l_period_end_date
1111 ,l_period_start_date);
1112
1113 load_xml('CS','ABSENCE_DETAILS','');
1114 load_xml('D','ACCRUAL_PLAN',l_accrual_plan_name);
1115 load_xml('D','ABSENCE_DAYS',l_leave_taken);
1116 load_xml('D','ANNUAL_LEAVE_BALANCE' ,l_annual_leave_balance);
1117 load_xml('CE','ABSENCE_DETAILS','');
1118 END IF;
1119 END LOOP;
1120 CLOSE csr_leave_balance;
1121
1122 end if;
1123
1124
1125 --
1126 --
1127 -- Employee Information - Legislation Specific
1128 --
1129 pay_mag_tape.call_leg_xml_proc;
1130 load_xml('CE','PAYMENT_DETAILS','');
1131 --
1132 --
1133 hr_utility_trace ('CLOB contents for assignment action '||
1134 l_assignment_action_id);
1135 --
1136 --print_clob (pay_mag_tape.g_clob_value);
1137 --
1138 hr_utility_trace('Leaving '||l_proc_name);
1139 END gen_payment_details_xml;
1140 ------------------------------------------------------------------------------
1141 END PAY_PAYMENT_XML_PKG;