[Home] [Help]
PACKAGE BODY: APPS.PAY_ARCHIVE_CHEQUEWRITER
Source
1 PACKAGE BODY pay_archive_chequewriter as
2 /* $Header: paychqarch.pkb 120.4.12020000.12 2012/09/24 07:09:10 pkoduri ship $ */
3 /* +======================================================================+
4 | Copyright (c) 2003 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +======================================================================+
8 Package Name : pay_us_chkw_depad
9 Package File Name : payuschkdp.pkb
10
11 Description : Used for Archive Cheque Writer producing XML output.
12
13 Change List:
14 ------------
15
16 Name Date Version Bug Text
17 ------------- ----------- ------- ------- ------------------------------
18 sausingh 24-May-2007 115.0 5635335 Created.
19 sudedas
20 sudedas 12-Feb-2008 115.1 6802173 Added Code for producing Dummy
21 Check for Un-Archived Employee and
22 marking Action Status as 'Skipped'
23 sudedas 11-Jun-2008 115.2 6938195 Changed Cursor get_arch_asg_action
24 _id for Separate Payment Run.
25 sudedas 19-Mar-2009 115.3 8348725 Changed datatype for l_asg_num
26 abellur 23-Feb-2012 115.4 13029999 Changed cursor get_pay_meth added
27 call to generate_third_party to
28 generate XML for third party checks
29 based on the payment method.
30 sbachu 05-Mar-2012 115.5 13773808 Created cursor get_ca_arch_asg
31 _action_id to fetch correct
32 context id for CA. Modified the
33 procedure generate_xml_dummy
34 abellur 06-Mar-2012 115.6 13814029 Added cursor get_multi_arch_asg_
35 get_multi_arch_asg_action_id to
36 retrieve the archiver assignment
37 action for third party checks.
38 sbachu 14-Mar-2012 115.7 13844885 Modified cursor get_ca_arch_asg
39 _action_id to fetch correct
40 context id for CA
41 sbachu 03-Apr-2012 115.9 13876466 Modified cursor get_ca_arch_asg
42 _action_id
43 vvijayku 07-May-2012 115.10 13969852 Added code to fetch the custom_xml_code
44 13969858 defined by the user to add additional
45 XML data in the Archive Check Writer XML.
46 The code added is a common code which
47 can be used by any loc.
48 vvijayku 07-May-2012 115.11 Corrected the get_custom_xml and made it
49 generic.
50 pkoduri 24-Sep-2012 115.12 14647358 Putting back the version 115.11 with the
51 changes for P1 14647358.
52 ========================================================================*/
53
54 --
55 -- Global Variables
56 --
57 g_proc_name varchar2(240);
58 g_debug boolean;
59 g_document_type varchar2(50);
60
61 /****************************************************************************
62 Name : PRINT_BLOB
63 Description : This procedure prints contents of BLOB passed as parameter.
64 *****************************************************************************/
65
66 PROCEDURE PRINT_BLOB(p_blob BLOB) IS
67 BEGIN
68 IF g_debug THEN
69 pay_ac_utility.print_lob(p_blob);
70 END IF;
71 END PRINT_BLOB;
72
73 /****************************************************************************
74 Name : WRITE_TO_MAGTAPE_LOB
75 Description : This procedure appends passed BLOB parameter to
76 pay_mag_tape.g_blob_value
77 *****************************************************************************/
78
79 PROCEDURE WRITE_TO_MAGTAPE_LOB(p_blob BLOB) IS
80 BEGIN
81 IF dbms_lob.getLength (p_blob) IS NOT NULL THEN
82 pay_core_files.write_to_magtape_lob (p_blob);
83 END IF;
84 END WRITE_TO_MAGTAPE_LOB;
85
86 /****************************************************************************
87 Name : WRITE_TO_MAGTAPE_LOB
88 Description : This procedure appends passed varchar2 parameter to
89 pay_mag_tape.g_blob_value
90 *****************************************************************************/
91
92 PROCEDURE WRITE_TO_MAGTAPE_LOB(p_data varchar2) IS
93 BEGIN
94 pay_core_files.write_to_magtape_lob (p_data);
95 END WRITE_TO_MAGTAPE_LOB;
96
97 /****************************************************************************
98 Name : LOAD_XML
99 Description : This Function generates XML Tags and Pass
100 *****************************************************************************/
101
102 FUNCTION LOAD_XML (
103 P_NODE_TYPE varchar2,
104 P_NODE varchar2,
105 P_DATA varchar2
106 ) RETURN VARCHAR2 AS
107
108 l_proc_name varchar2(100);
109 l_tag_name varchar2(500);
110 l_struct_code fnd_id_flex_structures.id_flex_structure_code%type;
111 l_data VARCHAR2(300);
112 l_ret_xml VARCHAR2(32000);
113
114 BEGIN
115
116 IF UPPER(p_node) NOT LIKE '?XML%' AND UPPER(p_node) NOT LIKE 'XAPI%' THEN
117 l_tag_name := nvl(l_tag_name, TRANSLATE(p_node,' /', '__'));
118 IF p_node_type IN ('CS', 'CE') THEN
119 l_tag_name := TRANSLATE(p_node, ' /', '__');
120 END IF;
121 ELSE
122 l_tag_name := p_node;
123 END IF;
124
125 IF p_node_type = 'CS' THEN
126 l_ret_xml := '<'||l_tag_name||'>' ;
127 ELSIF p_node_type = 'CE' THEN
128 l_ret_xml := '</'||l_tag_name||'>' ;
129 ELSIF p_node_type = 'D' THEN
130 /* Handle special charaters in data */
131 l_data := REPLACE (p_data, '&', '&');
132 l_data := REPLACE (l_data, '>', '>');
133 l_data := REPLACE (l_data, '<', '<');
134 l_data := REPLACE (l_data, '''', ''');
135 l_data := REPLACE (l_data, '"', '"');
136 l_ret_xml := '<'||l_tag_name||'>'||l_data||'</'||l_tag_name||'>' ;
137 END IF;
138 RETURN l_ret_xml ;
139 END LOAD_XML;
140
141 /****************************************************************************
142 Name : LOAD_SEGMENT_XML
143 Description : This Function generates XML Child Tags for Segments
144 under each Context of Action Info DF
145 *****************************************************************************/
146
147 FUNCTION load_segment_xml(cp_segment_name IN VARCHAR2
148 ,cp_segment_val IN VARCHAR2 DEFAULT NULL)
149 RETURN VARCHAR2 AS
150 l_segment_name VARCHAR2(500);
151 l_ret_seg_xml VARCHAR2(32000);
152 BEGIN
153 l_segment_name := UPPER(REPLACE(cp_segment_name,' ' ,'_'));
154 l_ret_seg_xml := load_xml('D',l_segment_name, cp_segment_val);
155 RETURN l_ret_seg_xml;
156 END load_segment_xml;
157
158 /****************************************************************************
159 Name : LOAD_CTX_XML_START_TAG
160 Description : This Function generates XML Start Tags for each Context
161 of Action Info DF
162 *****************************************************************************/
163
164 FUNCTION load_ctx_xml_start_tag(cp_context_name IN VARCHAR2)
165 RETURN VARCHAR2 AS
166 l_ctx_name VARCHAR2(500);
167 l_ret_ctx_st_xml VARCHAR2(32000);
168 BEGIN
169 l_ctx_name := UPPER(REPLACE(cp_context_name,' ' ,'_'));
170 l_ret_ctx_st_xml := load_xml('CS',l_ctx_name , NULL);
171 RETURN l_ret_ctx_st_xml;
172 END load_ctx_xml_start_tag;
173
174 /****************************************************************************
175 Name : LOAD_CTX_XML_END_TAG
176 Description : This Function generates XML End Tags for each Context
177 of Action Info DF
178 *****************************************************************************/
179
180 FUNCTION load_ctx_xml_end_tag(cp_context_name IN VARCHAR2)
181 RETURN VARCHAR2 AS
182 l_ctx_name VARCHAR2(500);
183 l_ret_ctx_end_xml VARCHAR2(32000);
184 BEGIN
185 l_ctx_name := UPPER(REPLACE(cp_context_name,' ' ,'_'));
186 l_ret_ctx_end_xml := load_xml('CE',l_ctx_name, NULL);
187 RETURN l_ret_ctx_end_xml;
188 END load_ctx_xml_end_tag;
189
190 /****************************************************************************
191 Name : GET_CUSTOM_XML
192 Description : This Function fetches the custom xml code defined by the
193 user which will be used to generate the custom xml data.
194 *****************************************************************************/
195 FUNCTION get_custom_xml(p_document_type IN VARCHAR2
196 ,p_legislation_code IN VARCHAR2)
197 RETURN VARCHAR2 AS
198 lv_custom_xml_code VARCHAR2(180);
199 get_xml_statem VARCHAR2(256);
200 sql_cur NUMBER;
201 l_rows NUMBER;
202 BEGIN
203 hr_utility.trace('p_document_type: '||p_document_type);
204 hr_utility.trace('p_legislation_code: '||p_legislation_code);
205 get_xml_statem := 'BEGIN PAY_'||p_legislation_code||'_RULES.get_custom_xml_routine(:lv_document_type, :lv_xml_routine); END;';
206 sql_cur := dbms_sql.open_cursor;
207 dbms_sql.parse(sql_cur
208 ,get_xml_statem
209 ,dbms_sql.v7);
210 dbms_sql.bind_variable(sql_cur, 'lv_document_type', p_document_type, 50);
211 dbms_sql.bind_variable(sql_cur, 'lv_xml_routine', lv_custom_xml_code, 50);
212 l_rows := dbms_sql.execute(sql_cur);
213 dbms_sql.variable_value(sql_cur, 'lv_xml_routine', lv_custom_xml_code);
214 dbms_sql.close_cursor(sql_cur);
215 hr_utility.trace('lv_custom_xml_code: '||lv_custom_xml_code);
216 RETURN lv_custom_xml_code;
217 Exception
218 WHEN OTHERS THEN
219 lv_custom_xml_code := null;
220 hr_utility.trace('lv_custom_xml_code in the exceptions clause: '||lv_custom_xml_code);
221 --
222 IF dbms_sql.IS_OPEN(sql_cur) THEN
223 dbms_sql.close_cursor(sql_cur);
224 END IF;
225 RETURN lv_custom_xml_code;
226 END get_custom_xml;
227
228
229 /****************************************************************************
230 Name : generate_xml_dummy
231 Description : This procedure accept live data, converts it to XML
232 format and appends to pay_mag_tape.g_blob_value. This will
233 be used to generate XML for Check Writer (XML) when
234 Payroll Archive is NOT Run.
235 *****************************************************************************/
236
237 PROCEDURE generate_xml_dummy(cp_check_no IN VARCHAR2
238 ,cp_account_name IN VARCHAR2
239 ,cp_account_no IN VARCHAR2
240 ,cp_account_typ IN VARCHAR2
241 ,cp_transit_no IN VARCHAR2
242 ,cp_bank_name IN VARCHAR2
243 ,cp_branch_name IN VARCHAR2
244 ,cp_employee_no IN VARCHAR2
245 ,cp_full_name IN VARCHAR2
246 ,cp_national_identifier IN VARCHAR2
247 ,p_xml_dummy_tab OUT NOCOPY pay_archive_chequewriter.ltr_char_tab_typ
248 ,p_legislation_code IN per_business_groups.legislation_code%TYPE)
249 AS
250 CURSOR get_flex_segment(cp_flex_ctx_code IN VARCHAR2)
251 IS
252 SELECT end_user_column_name
253 FROM fnd_descr_flex_col_usage_vl
254 WHERE descriptive_flexfield_name like 'Action Information DF'
255 and descriptive_flex_context_code = cp_flex_ctx_code
256 AND enabled_flag = 'Y';
257
258 ltr_ctx_tag pay_archive_chequewriter.ltr_char_tab_typ;
259 ltr_xml_tab pay_archive_chequewriter.ltr_char_tab_typ;
260
261 i NUMBER;
262 cntr NUMBER;
263 xml_cntr NUMBER;
264 l_segment_name VARCHAR2(240);
265 l_val VARCHAR2(240);
266 BEGIN
267 hr_utility.trace('Entering generate_xml_dummy');
268 hr_utility.trace('cp_check_no := '|| cp_check_no);
269 hr_utility.trace('cp_account_name := '|| cp_account_name);
270 hr_utility.trace('cp_account_no := '|| cp_account_no);
271 hr_utility.trace('cp_account_typ := '|| cp_account_typ);
272 hr_utility.trace('cp_transit_no := '|| cp_transit_no);
273 hr_utility.trace('cp_bank_name := '|| cp_bank_name);
274 hr_utility.trace('cp_branch_name := '|| cp_branch_name);
275 hr_utility.trace('cp_employee_no := '|| cp_employee_no);
276 hr_utility.trace('cp_full_name := '|| cp_full_name);
277 hr_utility.trace('cp_national_identifier := '|| cp_national_identifier);
278
279 /* Changes for bug 13773808 starts here*/
280 i := 1;
281 ltr_ctx_tag(i) := 'EMPLOYEE DETAILS';
282 If p_legislation_code <> 'CA' then
283 i := i + 1;
284 ltr_ctx_tag(i) := 'US FEDERAL';
285 end if;
286 i := i + 1;
287 ltr_ctx_tag(i) := 'AC DEDUCTIONS';
288 i := i + 1;
289 ltr_ctx_tag(i) := 'EMPLOYEE NET PAY DISTRIBUTION';
290 i := i + 1;
291 ltr_ctx_tag(i) := 'AC SUMMARY CURRENT';
292 i := i + 1;
293 ltr_ctx_tag(i) := 'AC EARNINGS';
294 i := i + 1;
295 ltr_ctx_tag(i) := 'EMPLOYEE HOURS BY RATE';
296 i := i + 1;
297 ltr_ctx_tag(i) := 'ADDRESS DETAILS';
298 If p_legislation_code <> 'CA' then
299 i := i + 1;
300 ltr_ctx_tag(i) := 'US WITHHOLDINGS';
301 end if;
302 i := i + 1;
303 ltr_ctx_tag(i) := 'EMPLOYEE OTHER INFORMATION';
304 i := i + 1;
305 ltr_ctx_tag(i) := 'AC SUMMARY YTD';
306 If p_legislation_code <> 'CA' then
307 i := i + 1;
308 ltr_ctx_tag(i) := 'US STATE';
309 end if;
310 /* Changes for bug 13773808 ends here*/
311 ltr_xml_tab(ltr_xml_tab.count() + 1) := load_ctx_xml_start_tag('CHEQUE');
312
313 FOR cntr IN 1..i
314 LOOP
315 hr_utility.trace('ltr_ctx_tag(cntr) := '|| ltr_ctx_tag(cntr));
316
317 ltr_xml_tab(ltr_xml_tab.count() + 1) := load_ctx_xml_start_tag(ltr_ctx_tag(cntr));
318
319 IF ltr_ctx_tag(cntr) = 'EMPLOYEE DETAILS' THEN
320
321 ltr_xml_tab(ltr_xml_tab.count() + 1) := load_segment_xml('CHECK_NUMBER',cp_check_no);
322 ltr_xml_tab(ltr_xml_tab.count() + 1) := load_segment_xml('AMOUNT_IN_WORDS','XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
323 ltr_xml_tab(ltr_xml_tab.count() + 1) := load_segment_xml('CHECK_AMOUNT','XXXXXXXXXXXX');
324 ltr_xml_tab(ltr_xml_tab.count() + 1) := load_segment_xml('ACCOUNT_NAME',cp_account_name);
325 ltr_xml_tab(ltr_xml_tab.count() + 1) := load_segment_xml('ACCOUNT_TYPE',cp_account_typ);
326 ltr_xml_tab(ltr_xml_tab.count() + 1) := load_segment_xml('ACCOUNT_NUMBER',cp_account_no);
327 ltr_xml_tab(ltr_xml_tab.count() + 1) := load_segment_xml('TRANSIT_CODE',cp_transit_no);
328 ltr_xml_tab(ltr_xml_tab.count() + 1) := load_segment_xml('BANK_NAME',cp_bank_name);
329 ltr_xml_tab(ltr_xml_tab.count() + 1) := load_segment_xml('BANK_BRANCH',cp_branch_name);
330 END IF;
331
332 OPEN get_flex_segment(ltr_ctx_tag(cntr));
333 LOOP
334 FETCH get_flex_segment INTO l_segment_name ;
335 hr_utility.trace('l_segment_name := ' || l_segment_name);
336 EXIT WHEN get_flex_segment%NOTFOUND;
337 l_val := NULL ;
338 IF ltr_ctx_tag(cntr) = 'EMPLOYEE DETAILS' THEN
339 IF UPPER(REPLACE(l_segment_name,' ' ,'_')) = 'EMPLOYEE_NUMBER' THEN
340 l_val := cp_employee_no;
341 ELSIF UPPER(REPLACE(l_segment_name,' ' ,'_')) = 'FULL_NAME' THEN
342 l_val := cp_full_name;
343 ELSIF UPPER(REPLACE(l_segment_name,' ' ,'_')) = 'NATIONAL_IDENTIFIER' THEN
344 l_val := cp_national_identifier;
345 END IF;
346 END IF;
347 ltr_xml_tab(ltr_xml_tab.count() + 1) := load_segment_xml(l_segment_name, l_val);
348 END LOOP;
349
350 ltr_xml_tab(ltr_xml_tab.count() + 1) := load_ctx_xml_end_tag(ltr_ctx_tag(cntr));
351
352 CLOSE get_flex_segment;
353 END LOOP;
354 ltr_xml_tab(ltr_xml_tab.count() + 1) := load_ctx_xml_end_tag('CHEQUE');
355 p_xml_dummy_tab := ltr_xml_tab;
356
357 END generate_xml_dummy;
358
359
360 /****************************************************************************
361 Name : GENERATE_XML
362 Description : This procedure fetches archived data, converts it to XML
363 format and appends to pay_mag_tape.g_blob_value. This will
364 be used to generate XML for Archive Cheque Writer Process
365 *****************************************************************************/
366 PROCEDURE GENERATE_XML IS
367 -- Fetching Legislation Code
368 --
369 CURSOR get_legislation_code(cp_chk_action_id in number) is
370 SELECT pbg.legislation_code
371 FROM pay_payroll_actions ppa,
372 pay_assignment_actions paa,
373 per_business_groups pbg
374 WHERE paa.assignment_action_id = cp_chk_action_id
375 AND paa.payroll_action_id = ppa.payroll_action_id
376 AND ppa.business_group_id = pbg.business_group_id;
377
378 CURSOR get_action_status(cp_chk_action_id IN NUMBER) IS
379 SELECT action_status
380 FROM pay_assignment_actions
381 WHERE assignment_action_id = cp_chk_action_id ;
382
383 -- Fetching Assignment Action ID of the Payroll / Payslip Archive
384 -- From the Assignment Action ID of Cheque Writer
385 -- This cursor is to be used when Payroll / Payslip Archive
386 -- Locks PrePayment. (Cheque Writer always locks PrePayment)
387
388 CURSOR get_arch_asg_action_id(cp_chk_action_id in number,
389 cp_legislation_code in varchar2) is
390 SELECT pai_arch.locking_action_id
391 FROM pay_action_interlocks pai_chk,
392 pay_action_interlocks pai_arch,
393 pay_assignment_actions paa_arch,
394 pay_assignment_actions paa_chk,
395 pay_pre_payments ppp,
396 pay_payroll_actions ppa_arch,
397 pay_payroll_actions ppa_chk,
398 hr_lookups hrl
399 WHERE pai_chk.locking_action_id = cp_chk_action_id
400
401 --Pre-Payment Assignment Action ID is locked by both Check Writer
402 --and Payroll Archive Process (It can be locked by NACHA as well)
403
404 AND pai_arch.locked_action_id = pai_chk.locked_action_id
405 AND paa_chk.assignment_action_id = pai_chk.locking_action_id
406 AND paa_chk.payroll_action_id = ppa_chk.payroll_action_id
407 AND ppa_chk.action_type = 'H'
408 AND paa_chk.pre_payment_id = ppp.pre_payment_id
409 AND (
410 (ppp.source_action_id IS NOT NULL
411 AND SUBSTR(paa_arch.serial_number,0,2) in ('PN','UN','PY','UY') -- added for P1 14647358
412 AND ppp.source_action_id = fnd_number.canonical_to_number(SUBSTR(paa_arch.serial_number, 3))
413 AND (INSTR(paa_arch.serial_number, 'PY') <> 0
414 or INSTR(paa_arch.serial_number, 'UY') <> 0)
415 AND paa_arch.source_action_id IS NOT NULL)
416 OR
417 (ppp.source_action_id IS NULL
418 AND SUBSTR(paa_arch.serial_number,0,2) in ('PN','UN','PY','UY') -- added for P1 14647358
419 AND ppp.assignment_action_id = fnd_number.canonical_to_number(SUBSTR(paa_arch.serial_number, 3))
420 AND (INSTR(paa_arch.serial_number, 'PN') <> 0
421 or INSTR(paa_arch.serial_number, 'UN') <> 0)
422 AND paa_arch.source_action_id IS NULL)
423 )
424 AND paa_arch.assignment_action_id = pai_arch.locking_action_id
425 AND ppa_arch.payroll_action_id = paa_arch.payroll_action_id
426 AND ppa_arch.action_type = 'X'
427 AND hrl.lookup_type = 'PAYSLIP_REPORT_TYPES'
428 AND hrl.lookup_code = cp_legislation_code
429 AND ppa_arch.report_type = hrl.meaning ;
430
431 /* Changes for bug 13773808 starts here*/
432 -- Fetching Action Context ID of the Payroll Archive
433 -- From the Assignment Action ID of Cheque Writer
434 -- This cursor is to be used when Payroll Archive
435 -- Locks Payroll Run. (Cheque Writer always locks PrePayment)
436
437
438 CURSOR get_ca_arch_asg_action_id(cp_chk_action_id in number,
439 cp_legislation_code in varchar2) is
440 SELECT paa_arch_ctx.assignment_action_id
441 FROM pay_action_interlocks pai_chk,
442 pay_action_interlocks pai_arch,
443 pay_assignment_actions paa_arch,
444 pay_assignment_actions paa_arch_ctx,
445 pay_assignment_actions paa_chk,
446 pay_pre_payments ppp,
447 pay_payroll_actions ppa_arch,
448 pay_payroll_actions ppa_chk,
449 hr_lookups hrl
450 WHERE pai_chk.locking_action_id = cp_chk_action_id
451
452 --Pre-Payment Assignment Action ID is locked by both Check Writer
453 --and Payroll Archive Process (It can be locked by NACHA as well)
454
455 AND pai_arch.locked_action_id = pai_chk.locked_action_id
456 AND paa_chk.assignment_action_id = pai_chk.locking_action_id
457 AND paa_chk.payroll_action_id = ppa_chk.payroll_action_id
458 AND pai_arch.locking_action_id = paa_arch_ctx.source_action_id
459 /*bug 13876466 for multiassignment case for third party cheque writer*/
460 AND (
461 paa_arch_ctx.tax_unit_id = paa_chk.tax_unit_id
462 OR paa_arch_ctx.assignment_id <> paa_chk.assignment_id
463 )
464 AND ppa_chk.action_type = 'H'
465 AND paa_chk.pre_payment_id = ppp.pre_payment_id
466 AND (
467 (
468 ppp.source_action_id IS NOT NULL
469 AND ppp.source_action_id = fnd_number.canonical_to_number (substr (paa_arch_ctx.serial_number, 3))
470 AND (
471 instr (paa_arch_ctx.serial_number, 'PY') <> 0
472 OR instr (paa_arch_ctx.serial_number, 'UY') <> 0
473 )
474 )
475 OR (
476 ppp.source_action_id IS NULL
477 /*AND ppp.assignment_action_id = fnd_number.canonical_to_number (substr (paa_arch_ctx.serial_number, 3)) bug 13876466*/
478
479 AND (
480 instr (paa_arch_ctx.serial_number, 'PN') <> 0
481 OR instr (paa_arch_ctx.serial_number, 'UN') <> 0
482 )
483 )
484 )
485 AND paa_arch.assignment_action_id = pai_arch.locking_action_id
486 AND ppa_arch.payroll_action_id = paa_arch.payroll_action_id
487 AND ppa_arch.action_type = 'X'
488 AND hrl.lookup_type = 'PAYSLIP_REPORT_TYPES'
489 AND hrl.lookup_code = cp_legislation_code
490 AND ppa_arch.report_type = hrl.meaning ;
491
492 /* Changes for bug 13773808 ends here*/
493 -- Fetching Assignment Action ID of the Payroll / Payslip Archive
494 -- From the Assignment Action ID of Cheque Writer
495 -- This cursor is to be used when Payroll / Payslip Archive
496 -- Locks Payroll Run. (Cheque Writer always locks PrePayment)
497
498 CURSOR get_arch_run_asg_action_id(cp_chk_action_id in number,
499 cp_legislation_code in varchar2) is
500 SELECT paa_arch.assignment_action_id
501 FROM pay_action_interlocks pai_chk,
502 pay_action_interlocks pai_prepay,
503 pay_action_interlocks pai_arch,
504 pay_assignment_actions paa_arch,
505 pay_assignment_actions paa_prepay,
506 pay_payroll_actions ppa_arch,
507 pay_payroll_actions ppa_prepay,
508 hr_lookups hrl
509 WHERE pai_chk.locking_action_id = cp_chk_action_id
510 AND pai_chk.locked_action_id = pai_prepay.locking_action_id
511 AND pai_prepay.locking_action_id = paa_prepay.assignment_action_id
512 AND paa_prepay.payroll_action_id = ppa_prepay.payroll_action_id
513 AND ppa_prepay.action_type IN ('P','U')
514 AND pai_prepay.locked_action_id = pai_arch.locked_action_id
515 AND paa_arch.assignment_action_id = pai_arch.locking_action_id
516 AND ppa_arch.payroll_action_id = paa_arch.payroll_action_id
517 AND hrl.lookup_type = 'PAYSLIP_REPORT_TYPES'
518 AND hrl.lookup_code = cp_legislation_code
519 AND ppa_arch.report_type = hrl.meaning;
520
521
522 -- Added the below cursor to fetch the correct archiver assignment action
523 -- ID in multi assignment payroll.
524 CURSOR get_multi_arch_asg_action_id(cp_chk_action_id in number,
525 cp_legislation_code in varchar2) is
526 SELECT pai_arch.locking_action_id
527 FROM pay_action_interlocks pai_chk,
528 pay_action_interlocks pai_arch,
529 pay_assignment_actions paa_arch,
530 pay_assignment_actions paa_chi_pp,
531 pay_assignment_actions paa_chk,
532 pay_pre_payments ppp,
533 pay_payroll_actions ppa_arch,
534 pay_payroll_actions ppa_chk,
535 hr_lookups hrl
536 WHERE pai_chk.locking_action_id = cp_chk_action_id
537 --Pre-Payment Assignment Action ID is locked by both Check Writer
538 --and Payroll Archive Process (It can be locked by NACHA as well)
539 -- AND pai_arch.locked_action_id = pai_chk.locked_action_id
540 AND paa_chk.assignment_action_id = pai_chk.locking_action_id
541 AND paa_chk.payroll_action_id = ppa_chk.payroll_action_id
542 AND ppa_chk.action_type = 'H'
543 AND paa_chk.pre_payment_id = ppp.pre_payment_id
544 AND (
545 (ppp.source_action_id IS NOT NULL
546 AND SUBSTR(paa_arch.serial_number,0,2) in ('PN','UN','PY','UY') -- added for P1 14647358
547 AND ppp.source_action_id = fnd_number.canonical_to_number(SUBSTR(paa_arch.serial_number, 3))
548 AND (INSTR(paa_arch.serial_number, 'PY') <> 0
549 or INSTR(paa_arch.serial_number, 'UY') <> 0)
550 AND paa_arch.source_action_id IS NOT NULL)
551 OR
552 (ppp.source_action_id IS NULL
553 AND SUBSTR(paa_arch.serial_number,0,2) in ('PN','UN','PY','UY') -- added for P1 14647358
554 AND ppp.prepayment_action_id = fnd_number.canonical_to_number(SUBSTR(paa_arch.serial_number, 3))
555 AND (INSTR(paa_arch.serial_number, 'PN') <> 0
556 or INSTR(paa_arch.serial_number, 'UN') <> 0)
557 AND paa_arch.source_action_id IS NULL)
558 )
559 AND paa_chi_pp.assignment_action_id = pai_chk.locked_action_id
560 AND paa_chi_pp.source_action_id = pai_arch.locked_action_id
561 AND paa_arch.assignment_action_id = pai_arch.locking_action_id
562 AND ppa_arch.payroll_action_id = paa_arch.payroll_action_id
563 AND ppa_arch.action_type = 'X'
564 AND hrl.lookup_type = 'PAYSLIP_REPORT_TYPES'
565 AND hrl.lookup_code = cp_legislation_code
566 AND ppa_arch.report_type = hrl.meaning ;
567
568 -- changes for 13814029 end.
569
570 --
571 --
572 CURSOR get_asssignment_id(cp_chk_asg_act_id IN NUMBER)
573 IS
574 SELECT DISTINCT assignment_id
575 FROM pay_assignment_actions
576 WHERE assignment_action_id = cp_chk_asg_act_id;
577
578 --
579 --
580 CURSOR get_effective_date(cp_chk_asg_act_id IN NUMBER)
581 IS
582 SELECT ppa.effective_date
583 ,ppa.payroll_action_id
584 FROM pay_payroll_actions ppa
585 ,pay_assignment_actions paa
586 WHERE paa.assignment_action_id = cp_chk_asg_act_id
587 AND paa.payroll_action_id = ppa.payroll_action_id
588 AND ppa.action_type = 'H';
589
590 CURSOR get_employee_details(p_assignment_id in number
591 ,p_effective_date in date)
592 IS
593 SELECT ppf.first_name
594 , ppf.last_name
595 , ppf.order_name
596 , ppf.full_name
597 , ppf.national_identifier
598 , ppf.employee_number
599 , pj.name
600 , hou.name
601 , paf.payroll_id
602 , prl.payroll_name
603 , ppf.middle_names
604 , ppf.title
605 , paf.assignment_number
606 FROM per_all_assignments_f paf
607 , per_all_people_f ppf
608 , per_periods_of_service pps
609 , per_jobs pj
610 , hr_organization_units hou
611 , pay_payrolls_f prl
612 WHERE paf.person_id = ppf.person_id
613 and paf.assignment_id = p_assignment_id
614 AND paf.job_id = pj.job_id(+)
615 and paf.organization_id = hou.organization_id
616 and prl.payroll_id=paf.payroll_id
617 and p_effective_date between paf.effective_start_date
618 and paf.effective_end_date
619 and p_effective_date between ppf.effective_start_date
620 and ppf.effective_end_date
621 and p_effective_date between prl.effective_start_date
622 and prl.effective_end_date
623 and pps.person_id = ppf.person_id
624 and pps.date_start = (select max(pps1.date_start)
625 from per_periods_of_service pps1
626 where pps1.person_id = paf.person_id
627 and pps1.date_start <= p_effective_date);
628
629 --
630 --
631 CURSOR get_action_details(cp_chk_asg_act_id IN NUMBER)
632 IS
633 SELECT nvl(paa.serial_number,'-9999')
634 ,substr(fnd_date.date_to_canonical(ppa.effective_date),1,10)
635 ,substr(nvl(fnd_date.date_to_canonical(ppa.overriding_dd_date),fnd_date.date_to_canonical(ppa.effective_date)),1,10)
636 ,ppa.payroll_action_id
637 FROM pay_assignment_actions paa,pay_payroll_actions ppa
638 WHERE paa.assignment_action_id = cp_chk_asg_act_id
639 AND paa.payroll_action_id = ppa.payroll_action_id ;
640
641 --
642 --
643 CURSOR get_org_bank_details(p_org_payment_method_id VARCHAR2,
644 p_effective_date date) IS
645 SELECT pay_ce_support_pkg.bank_segment_value(pea.external_account_id,'BANK_NAME', pea.territory_code),
646 pay_ce_support_pkg.bank_segment_value(pea.external_account_id,'BANK_BRANCH', pea.territory_code),
647 pay_ce_support_pkg.bank_segment_value(pea.external_account_id,'BANK_ACCOUNT_NAME', pea.territory_code),
648 pay_ce_support_pkg.bank_segment_value(pea.external_account_id,'BANK_ACCOUNT_NUMBER', pea.territory_code),
649 pea.segment4,
650 pea.segment2
651 FROM pay_org_payment_methods_f popm
652 , pay_external_accounts pea
653 WHERE org_payment_method_id = p_org_payment_method_id
654 AND popm.external_account_id = pea.external_account_id
655 AND p_effective_date between popm.EFFECTIVE_START_DATE
656 and popm.EFFECTIVE_END_DATE;
657 --
658 CURSOR get_person_bank_details(p_per_pay_method NUMBER
659 ,p_effective_date DATE)
660 IS
661 SELECT pay_ce_support_pkg.bank_segment_value(pea.external_account_id,'BANK_NAME', pea.territory_code),
662 pay_ce_support_pkg.bank_segment_value(pea.external_account_id,'BANK_BRANCH', pea.territory_code),
663 pay_ce_support_pkg.bank_segment_value(pea.external_account_id,'BANK_ACCOUNT_NAME', pea.territory_code),
664 pay_ce_support_pkg.bank_segment_value(pea.external_account_id,'BANK_ACCOUNT_NUMBER', pea.territory_code),
665 pea.segment4,
666 pea.segment2
667 FROM pay_personal_payment_methods_f pppm
668 , pay_external_accounts pea
669 WHERE pppm.personal_payment_method_id = p_per_pay_method
670 AND pppm.external_account_id = pea.external_account_id
671 AND p_effective_date between pppm.EFFECTIVE_START_DATE
672 and pppm.EFFECTIVE_END_DATE;
673 --
674 --
675 CURSOR get_pay_meth(cp_chk_action_id IN NUMBER
676 ,cp_chk_payroll_act_id IN NUMBER)
677 IS
678 SELECT ppp.payees_org_payment_method_id,
679 ppp.personal_payment_method_id,
680 ppp.org_payment_method_id,
681 -- added for 13029999
682 popm.defined_balance_id,
683 ppp.pre_payment_id
684 FROM pay_pre_payments ppp
685 , pay_action_interlocks pai
686 , pay_assignment_actions paa
687 , pay_assignment_actions paa_chq
688 , pay_payroll_actions ppa
689 , pay_payroll_actions ppa_chq
690 , pay_org_payment_methods_f popm
691 WHERE paa_chq.assignment_action_id = cp_chk_action_id
692 and paa_chq.assignment_action_id = pai.locking_action_id
693 and pai.locked_action_id = paa.assignment_action_id
694 and paa.payroll_action_id = ppa.payroll_action_id
695 and ppp.assignment_action_id = paa.assignment_action_id
696 and ppp.pre_payment_id = paa_chq.pre_payment_id
697 and popm.org_payment_method_id = ppp.org_payment_method_id
698 and ppa_chq.payment_type_id=popm.payment_type_id
699 and (ppa_chq.org_payment_method_id is NULL
700 or
701 ppa_chq.org_payment_method_id = ppp.org_payment_method_id)
702 and (ppa_chq.payroll_action_id = cp_chk_payroll_act_id)
703 and ppa_chq.effective_date between popm.effective_start_date and popm.effective_end_date;
704
705
706 ltr_xml_dummy_tab pay_archive_chequewriter.ltr_char_tab_typ;
707 l_first_name per_all_people_f.first_name%TYPE;
708 l_last_name per_all_people_f.last_name%TYPE;
709 l_order_name per_all_people_f.order_name%TYPE;
710 l_full_name per_all_people_f.full_name%TYPE;
711 l_national_identifier per_all_people_f.national_identifier%TYPE;
712 l_employee_number per_all_people_f.employee_number%TYPE;
713 l_middle_names per_all_people_f.middle_names%TYPE;
714 l_title per_all_people_f.title%TYPE;
715 l_assignment_id NUMBER;
716 l_effective_date DATE;
717 l_payroll_name pay_payrolls_f.payroll_name%TYPE;
718 l_job per_jobs.name%TYPE;
719 l_employer hr_organization_units.name%TYPE;
720 l_payroll_id NUMBER;
721 l_asg_num per_all_assignments_f.assignment_number%TYPE;
722 l_det_org_pay_method NUMBER;
723 l_per_pay_method NUMBER;
724 l_payee_meth_id NUMBER;
725
726 l_cheque_no VARCHAR2(300);
727 l_chq_effective_date VARCHAR2(300);
728 l_deposit_date VARCHAR2(300);
729 l_pactid NUMBER;
730
731 l_bank_name VARCHAR2(2000);
732 l_branch_name VARCHAR2(2000);
733 l_account_name VARCHAR2(2000);
734 l_account_number VARCHAR2(2000);
735 l_transit_code VARCHAR2(2000);
736 l_account_typ VARCHAR2(2000);
737 lv_action_status pay_assignment_actions.action_status%type;
738
739 ln_chq_asg_action_id NUMBER ;
740 lv_legislation_code per_business_groups.legislation_code%TYPE ;
741 lv_full_name VARCHAR2(250);
742 l_xml BLOB;
743 ln_arch_assignment_action_id NUMBER;
744 l_proc_name varchar2(50) := 'pay_archive_chequewriter.generate_xml' ;
745 l_xml_dummy BLOB;
746 lr_xml_dummy RAW (32767);
747 ln_amt_dummy NUMBER;
748 dummy_xml BLOB;
749 --Added for 13029999
750 l_thpty_flag number;
751 l_pre_payment_id number;
752 lv_arch_cheque_custom_xml VARCHAR2(180);
753 lv_thpty_che_custom_xml VARCHAR2(180);
754 BEGIN
755 hr_utility.trace('Entering pay_archive_chequewriter.generate_xml');
756
757 ln_chq_asg_action_id := pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID');
758
759 hr_utility.trace('ln_chq_asg_action_id := '||ln_chq_asg_action_id);
760
761 OPEN get_action_status(ln_chq_asg_action_id);
762 FETCH get_action_status INTO lv_action_status;
763 CLOSE get_action_status;
764
765 hr_utility.trace('Action Status before Generating XML := ' || lv_action_status);
766
767
768 OPEN get_legislation_code(ln_chq_asg_action_id) ;
769 FETCH get_legislation_code INTO lv_legislation_code ;
770 CLOSE get_legislation_code ;
771
772 hr_utility.trace('Legislation Code := '||lv_legislation_code) ;
773
774 /* Changes for bug 13773808 starts here*/
775 IF lv_legislation_code = 'CA' then
776 OPEN get_ca_arch_asg_action_id(ln_chq_asg_action_id,
777 lv_legislation_code);
778
779 FETCH get_ca_arch_asg_action_id
780 INTO ln_arch_assignment_action_id;
781
782 IF get_ca_arch_asg_action_id%NOTFOUND THEN
783 OPEN get_arch_run_asg_action_id(ln_chq_asg_action_id,
784 lv_legislation_code);
785 FETCH get_arch_run_asg_action_id
786 INTO ln_arch_assignment_action_id ;
787 CLOSE get_arch_run_asg_action_id ;
788 END IF ;
789 close get_ca_arch_asg_action_id;
790 ELSE
791 OPEN get_arch_asg_action_id(ln_chq_asg_action_id,
792 lv_legislation_code);
793
794 FETCH get_arch_asg_action_id
795 INTO ln_arch_assignment_action_id;
796
797 IF get_arch_asg_action_id%NOTFOUND THEN
798 OPEN get_arch_run_asg_action_id(ln_chq_asg_action_id,
799 lv_legislation_code);
800 FETCH get_arch_run_asg_action_id
801 INTO ln_arch_assignment_action_id ;
802 CLOSE get_arch_run_asg_action_id ;
803 END IF ;
804 CLOSE get_arch_asg_action_id ;
805
806 -- Added the below for 13814029
807 IF ln_arch_assignment_action_id IS NULL THEN
808 --get_multi_arch_asg_action_id
809 hr_utility.trace('Fetching multi asg payroll action id.');
810 OPEN get_multi_arch_asg_action_id(ln_chq_asg_action_id,
811 lv_legislation_code);
812 FETCH get_multi_arch_asg_action_id
813 INTO ln_arch_assignment_action_id ;
814 CLOSE get_multi_arch_asg_action_id ;
815 END IF;
816
817 END IF;
818 /* Changes for bug 13773808 ends here*/
819
820 hr_utility.trace('ln_arch_assignment_action_id := '||ln_arch_assignment_action_id);
821
822 -- Setting Global Variable values
823 pay_archive_chequewriter.g_chq_asg_action_id := ln_chq_asg_action_id;
824 pay_archive_chequewriter.g_arch_asg_action_id := ln_arch_assignment_action_id;
825 --
826 --
827 IF ln_arch_assignment_action_id IS NOT NULL THEN
828
829 -- 13029999 changes start
830 OPEN get_effective_date(ln_chq_asg_action_id);
831 FETCH get_effective_date INTO l_effective_date
832 ,l_pactid ;
833 CLOSE get_effective_date;
834
835 OPEN get_pay_meth(ln_chq_asg_action_id,l_pactid);
836 FETCH get_pay_meth INTO l_payee_meth_id
837 ,l_per_pay_method
838 ,l_det_org_pay_method
839 ,l_thpty_flag
840 ,l_pre_payment_id;
841 CLOSE get_pay_meth;
842 -- 13029999 changes end;
843
844 if l_thpty_flag is not null then
845
846 lv_arch_cheque_custom_xml:= get_custom_xml('ARCHIVE_CHEQUE_WRITER',lv_legislation_code); -- Bug 13969852, 13969858
847
848 hr_utility.trace('The Custom XML code for Archive Cheque Writer is: '||lv_arch_cheque_custom_xml);
849
850 pay_core_files.write_to_magtape_lob('<?xml version="1.0" encoding="'||hr_mx_utility.get_IANA_charset||'"?>');
851 pay_core_files.write_to_magtape_lob('<ARCHIVE_CHEQUE_WRITER>');
852
853 -- Printing Global Variable Values
854 --hr_utility.trace('g_chq_asg_action_id := '||pay_archive_chequewriter.g_chq_asg_action_id);
855 --hr_utility.trace('g_arch_asg_action_id := '||pay_archive_chequewriter.g_arch_asg_action_id);
856
857 pay_payroll_xml_extract_pkg.generate (ln_arch_assignment_action_id , -- Action Context ID
858 lv_arch_cheque_custom_xml , -- Custom XML Procedure, Bug 13969852, 13969858
859 'N' , -- Generate Header Flag (Y/N)
860 'CHEQUE', -- Root Tag : For Identifying Loc Specific Archived Data
861 'ARCHIVE_CHEQUE_WRITER', -- Document Type
862 l_xml );
863 write_to_magtape_lob(l_xml);
864
865 print_blob(pay_mag_tape.g_blob_value);
866
867 pay_core_files.write_to_magtape_lob('</ARCHIVE_CHEQUE_WRITER>');
868 else
869
870 lv_thpty_che_custom_xml:= get_custom_xml('THIRD_PARTY_CHEQUE_WRITER',lv_legislation_code); -- Bug 13969852, 13969858
871
872 hr_utility.trace('The Custom XML code for Third Party Cheque Writer is: '||lv_thpty_che_custom_xml);
873
874 pay_core_files.write_to_magtape_lob('<?xml version="1.0" encoding="'||hr_mx_utility.get_IANA_charset||'"?>');
875 pay_core_files.write_to_magtape_lob('<ARCHIVE_CHEQUE_WRITER>');
876
877 -- Printing Global Variable Values
878 --hr_utility.trace('g_chq_asg_action_id := '||pay_archive_chequewriter.g_chq_asg_action_id);
879 --hr_utility.trace('g_arch_asg_action_id := '||pay_archive_chequewriter.g_arch_asg_action_id);
880
881 pay_payroll_xml_extract_pkg.generate_third_party (null -- Action Information ID
882 ,ln_arch_assignment_action_id -- Action Context ID
883 ,l_pre_payment_id -- Pre Payment ID
884 ,null -- Custom Action Info Category
885 ,lv_thpty_che_custom_xml -- Custom XML Procedure, Bug 13969852, 13969858
886 ,FALSE -- Generate Header Flag (Y/N)
887 ,'CHEQUE' -- Root Tag : For Identifying Loc Specific Archived Data
888 ,'THIRD_PARTY_CHEQUE_WRITER' -- Document Type
889 ,l_xml );
890 write_to_magtape_lob(l_xml);
891
892 print_blob(pay_mag_tape.g_blob_value);
893
894 pay_core_files.write_to_magtape_lob('</ARCHIVE_CHEQUE_WRITER>');
895
896 end if;
897
898 ELSE
899 hr_utility.trace('ln_arch_assignment_action_id IS NULL.');
900
901 OPEN get_action_details(ln_chq_asg_action_id);
902 FETCH get_action_details INTO
903 l_cheque_no
904 ,l_chq_effective_date
905 ,l_deposit_date
906 ,l_pactid;
907 CLOSE get_action_details;
908
909 hr_utility.trace('l_cheque_no := '|| l_cheque_no);
910
911 OPEN get_asssignment_id(ln_chq_asg_action_id);
912 FETCH get_asssignment_id INTO l_assignment_id;
913 CLOSE get_asssignment_id;
914
915 OPEN get_effective_date(ln_chq_asg_action_id);
916 FETCH get_effective_date INTO l_effective_date
917 ,l_pactid ;
918 CLOSE get_effective_date;
919
920 hr_utility.trace('l_effective_date := '|| TO_CHAR(l_effective_date,'DD-MON-YYYY'));
921
922 OPEN get_employee_details(l_assignment_id,l_effective_date);
923 FETCH get_employee_details INTO
924 l_first_name
925 , l_last_name
926 , l_order_name
927 , l_full_name
928 , l_national_identifier
929 , l_employee_number
930 , l_job
931 , l_employer
932 , l_payroll_id
933 , l_payroll_name
934 , l_middle_names
935 , l_title
936 , l_asg_num;
937 CLOSE get_employee_details;
938
939 hr_utility.trace('Before get_pay_meth');
940
941 OPEN get_pay_meth(ln_chq_asg_action_id,l_pactid);
942 FETCH get_pay_meth INTO l_payee_meth_id
943 ,l_per_pay_method
944 ,l_det_org_pay_method
945 -- Added for 13029999
946 ,l_thpty_flag
947 ,l_pre_payment_id;
948 CLOSE get_pay_meth;
949
950 hr_utility.trace('Before Bank Details');
951
952 if l_det_org_pay_method is not null then
953 OPEN get_org_bank_details(l_det_org_pay_method,l_effective_date);
954 FETCH get_org_bank_details INTO
955 l_bank_name
956 ,l_branch_name
957 ,l_account_name
958 ,l_account_number
959 ,l_transit_code
960 ,l_account_typ ;
961 CLOSE get_org_bank_details;
962 elsif ( l_payee_meth_id IS NULL AND l_per_pay_method IS NOT NULL ) then
963 OPEN get_person_bank_details(l_per_pay_method,l_effective_date);
964 FETCH get_person_bank_details INTO
965 l_bank_name
966 ,l_branch_name
967 ,l_account_name
968 ,l_account_number
969 ,l_transit_code
970 ,l_account_typ ;
971 CLOSE get_person_bank_details;
972 end if;
973
974 hr_utility.trace('Before Archive Cheque');
975
976 pay_core_files.write_to_magtape_lob('<?xml version="1.0" encoding="'||hr_mx_utility.get_IANA_charset||'"?>');
977 pay_core_files.write_to_magtape_lob('<ARCHIVE_CHEQUE_WRITER>');
978
979 hr_utility.trace('Calling generate_xml_dummy');
980
981 generate_xml_dummy(l_cheque_no
982 ,l_account_name
983 ,l_account_number
984 ,l_account_typ
985 ,l_transit_code
986 ,l_bank_name
987 ,l_branch_name
988 ,l_employee_number
989 ,l_full_name
990 ,l_national_identifier
991 ,ltr_xml_dummy_tab
992 ,lv_legislation_code) ; /* bug 13773808*/
993 hr_utility.trace('After Calling generate_xml_dummy');
994
995 dbms_lob.createTemporary(l_xml_dummy, true, dbms_lob.session);
996
997 FOR cntr IN ltr_xml_dummy_tab.first()..ltr_xml_dummy_tab.last()
998 LOOP
999 hr_utility.trace('Accessing..' || ltr_xml_dummy_tab(cntr));
1000
1001 lr_xml_dummy := utl_raw.cast_to_raw(ltr_xml_dummy_tab(cntr));
1002 ln_amt_dummy := utl_raw.length(lr_xml_dummy);
1003
1004 dbms_lob.writeAppend(l_xml_dummy,
1005 ln_amt_dummy,
1006 lr_xml_dummy);
1007 END LOOP;
1008
1009 dummy_xml := l_xml_dummy;
1010
1011 hr_utility.trace('Successful LOB Creation.');
1012 pay_core_files.write_to_magtape_lob(dummy_xml);
1013
1014 pay_core_files.write_to_magtape_lob('</ARCHIVE_CHEQUE_WRITER>');
1015
1016 BEGIN
1017 UPDATE pay_assignment_actions
1018 SET action_status = 'S'
1019 WHERE assignment_action_id = ln_chq_asg_action_id;
1020
1021 hr_utility.trace('Update Successful..');
1022 EXCEPTION
1023
1024 WHEN OTHERS THEN
1025 hr_utility.trace('Update Unsuccessful..');
1026 END;
1027 print_blob(pay_mag_tape.g_blob_value);
1028 dbms_lob.freeTemporary(l_xml_dummy);
1029
1030 END IF;
1031
1032 hr_utility.trace('Leaving pay_archive_chequewriter.generate_xml');
1033
1034 EXCEPTION WHEN OTHERS THEN
1035 hr_utility.trace('SQLERRM := '||SQLERRM) ;
1036 END GENERATE_XML ;
1037
1038 BEGIN
1039 g_proc_name := 'pay_archive_chequewriter';
1040 g_document_type := 'ARCHIVE_CHEQUE_WRITER';
1041 g_debug := hr_utility.debug_enabled;
1042
1043 END pay_archive_chequewriter;