[Home] [Help]
PACKAGE BODY: APPS.PAY_ARCHIVE_CHEQUEWRITER
Source
1 PACKAGE BODY pay_archive_chequewriter as
2 /* $Header: paychqarch.pkb 120.0.12010000.3 2008/08/06 06:31:08 ubhat 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 ========================================================================*/
26
27 --
28 -- Global Variables
29 --
30 g_proc_name varchar2(240);
31 g_debug boolean;
32 g_document_type varchar2(50);
33
34 /****************************************************************************
35 Name : PRINT_BLOB
36 Description : This procedure prints contents of BLOB passed as parameter.
37 *****************************************************************************/
38
39 PROCEDURE PRINT_BLOB(p_blob BLOB) IS
40 BEGIN
41 IF g_debug THEN
42 pay_ac_utility.print_lob(p_blob);
43 END IF;
44 END PRINT_BLOB;
45
46 /****************************************************************************
47 Name : WRITE_TO_MAGTAPE_LOB
48 Description : This procedure appends passed BLOB parameter to
49 pay_mag_tape.g_blob_value
50 *****************************************************************************/
51
52 PROCEDURE WRITE_TO_MAGTAPE_LOB(p_blob BLOB) IS
53 BEGIN
54 IF dbms_lob.getLength (p_blob) IS NOT NULL THEN
55 pay_core_files.write_to_magtape_lob (p_blob);
56 END IF;
57 END WRITE_TO_MAGTAPE_LOB;
58
59 /****************************************************************************
60 Name : WRITE_TO_MAGTAPE_LOB
61 Description : This procedure appends passed varchar2 parameter to
62 pay_mag_tape.g_blob_value
63 *****************************************************************************/
64
65 PROCEDURE WRITE_TO_MAGTAPE_LOB(p_data varchar2) IS
66 BEGIN
67 pay_core_files.write_to_magtape_lob (p_data);
68 END WRITE_TO_MAGTAPE_LOB;
69
70 /****************************************************************************
71 Name : LOAD_XML
72 Description : This Function generates XML Tags and Pass
73 *****************************************************************************/
74
75 FUNCTION LOAD_XML (
76 P_NODE_TYPE varchar2,
77 P_NODE varchar2,
78 P_DATA varchar2
79 ) RETURN VARCHAR2 AS
80
81 l_proc_name varchar2(100);
82 l_tag_name varchar2(500);
83 l_struct_code fnd_id_flex_structures.id_flex_structure_code%type;
84 l_data VARCHAR2(300);
85 l_ret_xml VARCHAR2(32000);
86
87 BEGIN
88
89 IF UPPER(p_node) NOT LIKE '?XML%' AND UPPER(p_node) NOT LIKE 'XAPI%' THEN
90 l_tag_name := nvl(l_tag_name, TRANSLATE(p_node,' /', '__'));
91 IF p_node_type IN ('CS', 'CE') THEN
92 l_tag_name := TRANSLATE(p_node, ' /', '__');
93 END IF;
94 ELSE
95 l_tag_name := p_node;
96 END IF;
97
98 IF p_node_type = 'CS' THEN
99 l_ret_xml := '<'||l_tag_name||'>' ;
100 ELSIF p_node_type = 'CE' THEN
101 l_ret_xml := '</'||l_tag_name||'>' ;
102 ELSIF p_node_type = 'D' THEN
103 /* Handle special charaters in data */
104 l_data := REPLACE (p_data, '&', '&');
105 l_data := REPLACE (l_data, '>', '>');
106 l_data := REPLACE (l_data, '<', '<');
107 l_data := REPLACE (l_data, '''', ''');
108 l_data := REPLACE (l_data, '"', '"');
109 l_ret_xml := '<'||l_tag_name||'>'||l_data||'</'||l_tag_name||'>' ;
110 END IF;
111 RETURN l_ret_xml ;
112 END LOAD_XML;
113
114 /****************************************************************************
115 Name : LOAD_SEGMENT_XML
116 Description : This Function generates XML Child Tags for Segments
117 under each Context of Action Info DF
118 *****************************************************************************/
119
120 FUNCTION load_segment_xml(cp_segment_name IN VARCHAR2
121 ,cp_segment_val IN VARCHAR2 DEFAULT NULL)
122 RETURN VARCHAR2 AS
123 l_segment_name VARCHAR2(500);
124 l_ret_seg_xml VARCHAR2(32000);
125 BEGIN
126 l_segment_name := UPPER(REPLACE(cp_segment_name,' ' ,'_'));
127 l_ret_seg_xml := load_xml('D',l_segment_name, cp_segment_val);
128 RETURN l_ret_seg_xml;
129 END load_segment_xml;
130
131 /****************************************************************************
132 Name : LOAD_CTX_XML_START_TAG
133 Description : This Function generates XML Start Tags for each Context
134 of Action Info DF
135 *****************************************************************************/
136
137 FUNCTION load_ctx_xml_start_tag(cp_context_name IN VARCHAR2)
138 RETURN VARCHAR2 AS
139 l_ctx_name VARCHAR2(500);
140 l_ret_ctx_st_xml VARCHAR2(32000);
141 BEGIN
142 l_ctx_name := UPPER(REPLACE(cp_context_name,' ' ,'_'));
143 l_ret_ctx_st_xml := load_xml('CS',l_ctx_name , NULL);
144 RETURN l_ret_ctx_st_xml;
145 END load_ctx_xml_start_tag;
146
147 /****************************************************************************
148 Name : LOAD_CTX_XML_END_TAG
149 Description : This Function generates XML End Tags for each Context
150 of Action Info DF
151 *****************************************************************************/
152
153 FUNCTION load_ctx_xml_end_tag(cp_context_name IN VARCHAR2)
154 RETURN VARCHAR2 AS
155 l_ctx_name VARCHAR2(500);
156 l_ret_ctx_end_xml VARCHAR2(32000);
157 BEGIN
158 l_ctx_name := UPPER(REPLACE(cp_context_name,' ' ,'_'));
159 l_ret_ctx_end_xml := load_xml('CE',l_ctx_name, NULL);
160 RETURN l_ret_ctx_end_xml;
161 END load_ctx_xml_end_tag;
162
163 /****************************************************************************
164 Name : generate_xml_dummy
165 Description : This procedure accept live data, converts it to XML
166 format and appends to pay_mag_tape.g_blob_value. This will
167 be used to generate XML for Check Writer (XML) when
168 Payroll Archive is NOT Run.
169 *****************************************************************************/
170
171 PROCEDURE generate_xml_dummy(cp_check_no IN VARCHAR2
172 ,cp_account_name IN VARCHAR2
173 ,cp_account_no IN VARCHAR2
174 ,cp_account_typ IN VARCHAR2
175 ,cp_transit_no IN VARCHAR2
176 ,cp_bank_name IN VARCHAR2
177 ,cp_branch_name IN VARCHAR2
178 ,cp_employee_no IN VARCHAR2
179 ,cp_full_name IN VARCHAR2
180 ,cp_national_identifier IN VARCHAR2
181 ,p_xml_dummy_tab OUT NOCOPY pay_archive_chequewriter.ltr_char_tab_typ)
182 AS
183 CURSOR get_flex_segment(cp_flex_ctx_code IN VARCHAR2)
184 IS
185 SELECT end_user_column_name
186 FROM fnd_descr_flex_col_usage_vl
187 WHERE descriptive_flexfield_name like 'Action Information DF'
188 and descriptive_flex_context_code = cp_flex_ctx_code
189 AND enabled_flag = 'Y';
190
191 ltr_ctx_tag pay_archive_chequewriter.ltr_char_tab_typ;
192 ltr_xml_tab pay_archive_chequewriter.ltr_char_tab_typ;
193
194 i NUMBER;
195 cntr NUMBER;
196 xml_cntr NUMBER;
197 l_segment_name VARCHAR2(240);
198 l_val VARCHAR2(240);
199 BEGIN
200 hr_utility.trace('Entering generate_xml_dummy');
201 hr_utility.trace('cp_check_no := '|| cp_check_no);
202 hr_utility.trace('cp_account_name := '|| cp_account_name);
203 hr_utility.trace('cp_account_no := '|| cp_account_no);
204 hr_utility.trace('cp_account_typ := '|| cp_account_typ);
205 hr_utility.trace('cp_transit_no := '|| cp_transit_no);
206 hr_utility.trace('cp_bank_name := '|| cp_bank_name);
207 hr_utility.trace('cp_branch_name := '|| cp_branch_name);
208 hr_utility.trace('cp_employee_no := '|| cp_employee_no);
209 hr_utility.trace('cp_full_name := '|| cp_full_name);
210 hr_utility.trace('cp_national_identifier := '|| cp_national_identifier);
211
212 i := 1;
213 ltr_ctx_tag(i) := 'EMPLOYEE DETAILS';
214 i := i + 1;
215 ltr_ctx_tag(i) := 'US FEDERAL';
216 i := i + 1;
217 ltr_ctx_tag(i) := 'AC DEDUCTIONS';
218 i := i + 1;
219 ltr_ctx_tag(i) := 'EMPLOYEE NET PAY DISTRIBUTION';
220 i := i + 1;
221 ltr_ctx_tag(i) := 'AC SUMMARY CURRENT';
222 i := i + 1;
223 ltr_ctx_tag(i) := 'AC EARNINGS';
224 i := i + 1;
225 ltr_ctx_tag(i) := 'EMPLOYEE HOURS BY RATE';
226 i := i + 1;
227 ltr_ctx_tag(i) := 'ADDRESS DETAILS';
228 i := i + 1;
229 ltr_ctx_tag(i) := 'US WITHHOLDINGS';
230 i := i + 1;
231 ltr_ctx_tag(i) := 'EMPLOYEE OTHER INFORMATION';
232 i := i + 1;
233 ltr_ctx_tag(i) := 'AC SUMMARY YTD';
234 i := i + 1;
235 ltr_ctx_tag(i) := 'US STATE';
236
237 ltr_xml_tab(ltr_xml_tab.count() + 1) := load_ctx_xml_start_tag('CHEQUE');
238
239 FOR cntr IN 1..i
240 LOOP
241 hr_utility.trace('ltr_ctx_tag(cntr) := '|| ltr_ctx_tag(cntr));
242
243 ltr_xml_tab(ltr_xml_tab.count() + 1) := load_ctx_xml_start_tag(ltr_ctx_tag(cntr));
244
245 IF ltr_ctx_tag(cntr) = 'EMPLOYEE DETAILS' THEN
246
247 ltr_xml_tab(ltr_xml_tab.count() + 1) := load_segment_xml('CHECK_NUMBER',cp_check_no);
248 ltr_xml_tab(ltr_xml_tab.count() + 1) := load_segment_xml('AMOUNT_IN_WORDS','XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
249 ltr_xml_tab(ltr_xml_tab.count() + 1) := load_segment_xml('CHECK_AMOUNT','XXXXXXXXXXXX');
250 ltr_xml_tab(ltr_xml_tab.count() + 1) := load_segment_xml('ACCOUNT_NAME',cp_account_name);
251 ltr_xml_tab(ltr_xml_tab.count() + 1) := load_segment_xml('ACCOUNT_TYPE',cp_account_typ);
252 ltr_xml_tab(ltr_xml_tab.count() + 1) := load_segment_xml('ACCOUNT_NUMBER',cp_account_no);
253 ltr_xml_tab(ltr_xml_tab.count() + 1) := load_segment_xml('TRANSIT_CODE',cp_transit_no);
254 ltr_xml_tab(ltr_xml_tab.count() + 1) := load_segment_xml('BANK_NAME',cp_bank_name);
255 ltr_xml_tab(ltr_xml_tab.count() + 1) := load_segment_xml('BANK_BRANCH',cp_branch_name);
256 END IF;
257
258 OPEN get_flex_segment(ltr_ctx_tag(cntr));
259 LOOP
260 FETCH get_flex_segment INTO l_segment_name ;
261 hr_utility.trace('l_segment_name := ' || l_segment_name);
262 EXIT WHEN get_flex_segment%NOTFOUND;
263 l_val := NULL ;
264 IF ltr_ctx_tag(cntr) = 'EMPLOYEE DETAILS' THEN
265 IF UPPER(REPLACE(l_segment_name,' ' ,'_')) = 'EMPLOYEE_NUMBER' THEN
266 l_val := cp_employee_no;
267 ELSIF UPPER(REPLACE(l_segment_name,' ' ,'_')) = 'FULL_NAME' THEN
268 l_val := cp_full_name;
269 ELSIF UPPER(REPLACE(l_segment_name,' ' ,'_')) = 'NATIONAL_IDENTIFIER' THEN
270 l_val := cp_national_identifier;
271 END IF;
272 END IF;
273 ltr_xml_tab(ltr_xml_tab.count() + 1) := load_segment_xml(l_segment_name, l_val);
274 END LOOP;
275
276 ltr_xml_tab(ltr_xml_tab.count() + 1) := load_ctx_xml_end_tag(ltr_ctx_tag(cntr));
277
278 CLOSE get_flex_segment;
279 END LOOP;
280 ltr_xml_tab(ltr_xml_tab.count() + 1) := load_ctx_xml_end_tag('CHEQUE');
281 p_xml_dummy_tab := ltr_xml_tab;
282
283 END generate_xml_dummy;
284
285 /****************************************************************************
286 Name : GENERATE_XML
287 Description : This procedure fetches archived data, converts it to XML
288 format and appends to pay_mag_tape.g_blob_value. This will
289 be used to generate XML for Archive Cheque Writer Process
290 *****************************************************************************/
291 PROCEDURE GENERATE_XML IS
292 -- Fetching Legislation Code
293 --
294 CURSOR get_legislation_code(cp_chk_action_id in number) is
295 SELECT pbg.legislation_code
296 FROM pay_payroll_actions ppa,
297 pay_assignment_actions paa,
298 per_business_groups pbg
299 WHERE paa.assignment_action_id = cp_chk_action_id
300 AND paa.payroll_action_id = ppa.payroll_action_id
301 AND ppa.business_group_id = pbg.business_group_id;
302
303 CURSOR get_action_status(cp_chk_action_id IN NUMBER) IS
304 SELECT action_status
305 FROM pay_assignment_actions
306 WHERE assignment_action_id = cp_chk_action_id ;
307
308 -- Fetching Assignment Action ID of the Payroll / Payslip Archive
309 -- From the Assignment Action ID of Cheque Writer
310 -- This cursor is to be used when Payroll / Payslip Archive
311 -- Locks PrePayment. (Cheque Writer always locks PrePayment)
312
313 CURSOR get_arch_asg_action_id(cp_chk_action_id in number,
314 cp_legislation_code in varchar2) is
315 SELECT pai_arch.locking_action_id
316 FROM pay_action_interlocks pai_chk,
317 pay_action_interlocks pai_arch,
318 pay_assignment_actions paa_arch,
319 pay_assignment_actions paa_chk,
320 pay_pre_payments ppp,
321 pay_payroll_actions ppa_arch,
322 pay_payroll_actions ppa_chk,
323 hr_lookups hrl
324 WHERE pai_chk.locking_action_id = cp_chk_action_id
325
326 --Pre-Payment Assignment Action ID is locked by both Check Writer
327 --and Payroll Archive Process (It can be locked by NACHA as well)
328
329 AND pai_arch.locked_action_id = pai_chk.locked_action_id
330 AND paa_chk.assignment_action_id = pai_chk.locking_action_id
331 AND paa_chk.payroll_action_id = ppa_chk.payroll_action_id
332 AND ppa_chk.action_type = 'H'
333 AND paa_chk.pre_payment_id = ppp.pre_payment_id
334 AND (
335 (ppp.source_action_id IS NOT NULL
336 AND ppp.source_action_id = fnd_number.canonical_to_number(SUBSTR(paa_arch.serial_number, 3))
337 AND (INSTR(paa_arch.serial_number, 'PY') <> 0
338 or INSTR(paa_arch.serial_number, 'UY') <> 0)
339 AND paa_arch.source_action_id IS NOT NULL)
340 OR
341 (ppp.source_action_id IS NULL
342 AND ppp.assignment_action_id = fnd_number.canonical_to_number(SUBSTR(paa_arch.serial_number, 3))
343 AND (INSTR(paa_arch.serial_number, 'PN') <> 0
344 or INSTR(paa_arch.serial_number, 'UN') <> 0)
345 AND paa_arch.source_action_id IS NULL)
346 )
347 AND paa_arch.assignment_action_id = pai_arch.locking_action_id
348 AND ppa_arch.payroll_action_id = paa_arch.payroll_action_id
349 AND ppa_arch.action_type = 'X'
350 AND hrl.lookup_type = 'PAYSLIP_REPORT_TYPES'
351 AND hrl.lookup_code = cp_legislation_code
352 AND ppa_arch.report_type = hrl.meaning ;
353
354 -- Fetching Assignment Action ID of the Payroll / Payslip Archive
355 -- From the Assignment Action ID of Cheque Writer
356 -- This cursor is to be used when Payroll / Payslip Archive
357 -- Locks Payroll Run. (Cheque Writer always locks PrePayment)
358
359 CURSOR get_arch_run_asg_action_id(cp_chk_action_id in number,
360 cp_legislation_code in varchar2) is
361 SELECT paa_arch.assignment_action_id
362 FROM pay_action_interlocks pai_chk,
363 pay_action_interlocks pai_prepay,
364 pay_action_interlocks pai_arch,
365 pay_assignment_actions paa_arch,
366 pay_assignment_actions paa_prepay,
367 pay_payroll_actions ppa_arch,
368 pay_payroll_actions ppa_prepay,
369 hr_lookups hrl
370 WHERE pai_chk.locking_action_id = cp_chk_action_id
371 AND pai_chk.locked_action_id = pai_prepay.locking_action_id
372 AND pai_prepay.locking_action_id = paa_prepay.assignment_action_id
373 AND paa_prepay.payroll_action_id = ppa_prepay.payroll_action_id
374 AND ppa_prepay.action_type IN ('P','U')
375 AND pai_prepay.locked_action_id = pai_arch.locked_action_id
376 AND paa_arch.assignment_action_id = pai_arch.locking_action_id
377 AND ppa_arch.payroll_action_id = paa_arch.payroll_action_id
378 AND hrl.lookup_type = 'PAYSLIP_REPORT_TYPES'
379 AND hrl.lookup_code = cp_legislation_code
380 AND ppa_arch.report_type = hrl.meaning;
381
382 --
383 --
384 CURSOR get_asssignment_id(cp_chk_asg_act_id IN NUMBER)
385 IS
386 SELECT DISTINCT assignment_id
387 FROM pay_assignment_actions
388 WHERE assignment_action_id = cp_chk_asg_act_id;
389
390 --
391 --
392 CURSOR get_effective_date(cp_chk_asg_act_id IN NUMBER)
393 IS
394 SELECT ppa.effective_date
395 ,ppa.payroll_action_id
396 FROM pay_payroll_actions ppa
397 ,pay_assignment_actions paa
398 WHERE paa.assignment_action_id = cp_chk_asg_act_id
399 AND paa.payroll_action_id = ppa.payroll_action_id
400 AND ppa.action_type = 'H';
401
402 CURSOR get_employee_details(p_assignment_id in number
403 ,p_effective_date in date)
404 IS
405 SELECT ppf.first_name
406 , ppf.last_name
407 , ppf.order_name
408 , ppf.full_name
409 , ppf.national_identifier
410 , ppf.employee_number
411 , pj.name
412 , hou.name
413 , paf.payroll_id
414 , prl.payroll_name
415 , ppf.middle_names
416 , ppf.title
417 , paf.assignment_number
418 FROM per_all_assignments_f paf
419 , per_all_people_f ppf
420 , per_periods_of_service pps
421 , per_jobs pj
422 , hr_organization_units hou
423 , pay_payrolls_f prl
424 WHERE paf.person_id = ppf.person_id
425 and paf.assignment_id = p_assignment_id
426 AND paf.job_id = pj.job_id(+)
427 and paf.organization_id = hou.organization_id
428 and prl.payroll_id=paf.payroll_id
429 and p_effective_date between paf.effective_start_date
430 and paf.effective_end_date
431 and p_effective_date between ppf.effective_start_date
432 and ppf.effective_end_date
433 and p_effective_date between prl.effective_start_date
434 and prl.effective_end_date
435 and pps.person_id = ppf.person_id
436 and pps.date_start = (select max(pps1.date_start)
437 from per_periods_of_service pps1
438 where pps1.person_id = paf.person_id
439 and pps1.date_start <= p_effective_date);
440
441 --
442 --
443 CURSOR get_action_details(cp_chk_asg_act_id IN NUMBER)
444 IS
445 SELECT nvl(paa.serial_number,'-9999')
446 ,substr(fnd_date.date_to_canonical(ppa.effective_date),1,10)
447 ,substr(nvl(fnd_date.date_to_canonical(ppa.overriding_dd_date),fnd_date.date_to_canonical(ppa.effective_date)),1,10)
448 ,ppa.payroll_action_id
449 FROM pay_assignment_actions paa,pay_payroll_actions ppa
450 WHERE paa.assignment_action_id = cp_chk_asg_act_id
451 AND paa.payroll_action_id = ppa.payroll_action_id ;
452
453 --
454 --
455 CURSOR get_org_bank_details(p_org_payment_method_id VARCHAR2,
456 p_effective_date date) IS
457 SELECT pay_ce_support_pkg.bank_segment_value(pea.external_account_id,'BANK_NAME', pea.territory_code),
458 pay_ce_support_pkg.bank_segment_value(pea.external_account_id,'BANK_BRANCH', pea.territory_code),
459 pay_ce_support_pkg.bank_segment_value(pea.external_account_id,'BANK_ACCOUNT_NAME', pea.territory_code),
460 pay_ce_support_pkg.bank_segment_value(pea.external_account_id,'BANK_ACCOUNT_NUMBER', pea.territory_code),
461 pea.segment4,
462 pea.segment2
463 FROM pay_org_payment_methods_f popm
464 , pay_external_accounts pea
465 WHERE org_payment_method_id = p_org_payment_method_id
466 AND popm.external_account_id = pea.external_account_id
467 AND p_effective_date between popm.EFFECTIVE_START_DATE
468 and popm.EFFECTIVE_END_DATE;
469 --
470 CURSOR get_person_bank_details(p_per_pay_method NUMBER
471 ,p_effective_date DATE)
472 IS
473 SELECT pay_ce_support_pkg.bank_segment_value(pea.external_account_id,'BANK_NAME', pea.territory_code),
474 pay_ce_support_pkg.bank_segment_value(pea.external_account_id,'BANK_BRANCH', pea.territory_code),
475 pay_ce_support_pkg.bank_segment_value(pea.external_account_id,'BANK_ACCOUNT_NAME', pea.territory_code),
476 pay_ce_support_pkg.bank_segment_value(pea.external_account_id,'BANK_ACCOUNT_NUMBER', pea.territory_code),
477 pea.segment4,
478 pea.segment2
479 FROM pay_personal_payment_methods_f pppm
480 , pay_external_accounts pea
481 WHERE pppm.personal_payment_method_id = p_per_pay_method
482 AND pppm.external_account_id = pea.external_account_id
483 AND p_effective_date between pppm.EFFECTIVE_START_DATE
484 and pppm.EFFECTIVE_END_DATE;
485 --
486 --
487 CURSOR get_pay_meth(cp_chk_action_id IN NUMBER
488 ,cp_chk_payroll_act_id IN NUMBER)
489 IS
490 SELECT ppp.payees_org_payment_method_id,
491 ppp.personal_payment_method_id,
492 ppp.org_payment_method_id
493 FROM pay_pre_payments ppp
494 , pay_action_interlocks pai
495 , pay_assignment_actions paa
496 , pay_assignment_actions paa_chq
497 , pay_payroll_actions ppa
498 , pay_payroll_actions ppa_chq
499 , pay_org_payment_methods_f popm
500 WHERE paa_chq.assignment_action_id = cp_chk_action_id
501 and paa_chq.assignment_action_id = pai.locking_action_id
502 and pai.locked_action_id = paa.assignment_action_id
503 and paa.payroll_action_id = ppa.payroll_action_id
504 and ppp.assignment_action_id = paa.assignment_action_id
505 and ppp.pre_payment_id = paa_chq.pre_payment_id
506 and popm.org_payment_method_id = ppp.org_payment_method_id
507 and ppa_chq.payment_type_id=popm.payment_type_id
508 and (ppa_chq.org_payment_method_id is NULL
509 or
510 ppa_chq.org_payment_method_id = ppp.org_payment_method_id)
511 and (ppa_chq.payroll_action_id = cp_chk_payroll_act_id)
512 and ppa_chq.effective_date between popm.effective_start_date and popm.effective_end_date;
513
514
515 ltr_xml_dummy_tab pay_archive_chequewriter.ltr_char_tab_typ;
516 l_first_name per_all_people_f.first_name%TYPE;
517 l_last_name per_all_people_f.last_name%TYPE;
518 l_order_name per_all_people_f.order_name%TYPE;
519 l_full_name per_all_people_f.full_name%TYPE;
520 l_national_identifier per_all_people_f.national_identifier%TYPE;
521 l_employee_number per_all_people_f.employee_number%TYPE;
522 l_middle_names per_all_people_f.middle_names%TYPE;
523 l_title per_all_people_f.title%TYPE;
524 l_assignment_id NUMBER;
525 l_effective_date DATE;
526 l_payroll_name pay_payrolls_f.payroll_name%TYPE;
527 l_job per_jobs.name%TYPE;
528 l_employer hr_organization_units.name%TYPE;
529 l_payroll_id NUMBER;
530 l_asg_num NUMBER;
531 l_det_org_pay_method NUMBER;
532 l_per_pay_method NUMBER;
533 l_payee_meth_id NUMBER;
534
535 l_cheque_no VARCHAR2(300);
536 l_chq_effective_date VARCHAR2(300);
537 l_deposit_date VARCHAR2(300);
538 l_pactid NUMBER;
539
540 l_bank_name VARCHAR2(2000);
541 l_branch_name VARCHAR2(2000);
542 l_account_name VARCHAR2(2000);
543 l_account_number VARCHAR2(2000);
544 l_transit_code VARCHAR2(2000);
545 l_account_typ VARCHAR2(2000);
546 lv_action_status pay_assignment_actions.action_status%type;
547
548 ln_chq_asg_action_id NUMBER ;
549 lv_legislation_code per_business_groups.legislation_code%TYPE ;
550 lv_full_name VARCHAR2(250);
551 l_xml BLOB;
552 ln_arch_assignment_action_id NUMBER;
553 l_proc_name varchar2(50) := 'pay_archive_chequewriter.generate_xml' ;
554 l_xml_dummy BLOB;
555 lr_xml_dummy RAW (32767);
556 ln_amt_dummy NUMBER;
557 dummy_xml BLOB;
558
559 BEGIN
560 hr_utility.trace('Entering pay_archive_chequewriter.generate_xml');
561
562 ln_chq_asg_action_id := pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID');
563
564 hr_utility.trace('ln_chq_asg_action_id := '||ln_chq_asg_action_id);
565
566 OPEN get_action_status(ln_chq_asg_action_id);
567 FETCH get_action_status INTO lv_action_status;
568 CLOSE get_action_status;
569
570 hr_utility.trace('Action Status before Generating XML := ' || lv_action_status);
571
572
573 OPEN get_legislation_code(ln_chq_asg_action_id) ;
574 FETCH get_legislation_code INTO lv_legislation_code ;
575 CLOSE get_legislation_code ;
576
577 hr_utility.trace('Legislation Code := '||lv_legislation_code) ;
578
579 OPEN get_arch_asg_action_id(ln_chq_asg_action_id,
580 lv_legislation_code);
581
582 FETCH get_arch_asg_action_id
583 INTO ln_arch_assignment_action_id;
584
585 IF get_arch_asg_action_id%NOTFOUND THEN
586 OPEN get_arch_run_asg_action_id(ln_chq_asg_action_id,
587 lv_legislation_code);
588 FETCH get_arch_run_asg_action_id
589 INTO ln_arch_assignment_action_id ;
590 CLOSE get_arch_run_asg_action_id ;
591 END IF ;
592
593 CLOSE get_arch_asg_action_id ;
594
595 hr_utility.trace('ln_arch_assignment_action_id := '||ln_arch_assignment_action_id);
596
597 -- Setting Global Variable values
598 pay_archive_chequewriter.g_chq_asg_action_id := ln_chq_asg_action_id;
599 pay_archive_chequewriter.g_arch_asg_action_id := ln_arch_assignment_action_id;
600 --
601 --
602 IF ln_arch_assignment_action_id IS NOT NULL THEN
603
604 pay_core_files.write_to_magtape_lob('<?xml version="1.0" encoding="'||hr_mx_utility.get_IANA_charset||'"?>');
605 pay_core_files.write_to_magtape_lob('<ARCHIVE_CHEQUE_WRITER>');
606
607 -- Printing Global Variable Values
608 --hr_utility.trace('g_chq_asg_action_id := '||pay_archive_chequewriter.g_chq_asg_action_id);
609 --hr_utility.trace('g_arch_asg_action_id := '||pay_archive_chequewriter.g_arch_asg_action_id);
610
611 pay_payroll_xml_extract_pkg.generate ( ln_arch_assignment_action_id , -- Action Context ID
612 null , -- Custom XML Procedure
613 'N' , -- Generate Header Flag (Y/N)
614 'CHEQUE', -- Root Tag : For Identifying Loc Specific Archived Data
615 'ARCHIVE_CHEQUE_WRITER', -- Document Type
616 l_xml );
617 write_to_magtape_lob(l_xml);
618
619 print_blob(pay_mag_tape.g_blob_value);
620
621 pay_core_files.write_to_magtape_lob('</ARCHIVE_CHEQUE_WRITER>');
622
623
624 ELSE
625 hr_utility.trace('ln_arch_assignment_action_id IS NULL.');
626
627 OPEN get_action_details(ln_chq_asg_action_id);
628 FETCH get_action_details INTO
629 l_cheque_no
630 ,l_chq_effective_date
631 ,l_deposit_date
632 ,l_pactid;
633 CLOSE get_action_details;
634
635 hr_utility.trace('l_cheque_no := '|| l_cheque_no);
636
637 OPEN get_asssignment_id(ln_chq_asg_action_id);
638 FETCH get_asssignment_id INTO l_assignment_id;
639 CLOSE get_asssignment_id;
640
641 OPEN get_effective_date(ln_chq_asg_action_id);
642 FETCH get_effective_date INTO l_effective_date
643 ,l_pactid ;
644 CLOSE get_effective_date;
645
646 hr_utility.trace('l_effective_date := '|| TO_CHAR(l_effective_date,'DD-MON-YYYY'));
647
648 OPEN get_employee_details(l_assignment_id,l_effective_date);
649 FETCH get_employee_details INTO
650 l_first_name
651 , l_last_name
652 , l_order_name
653 , l_full_name
654 , l_national_identifier
655 , l_employee_number
656 , l_job
657 , l_employer
658 , l_payroll_id
659 , l_payroll_name
660 , l_middle_names
661 , l_title
662 , l_asg_num;
663 CLOSE get_employee_details;
664
665 hr_utility.trace('Before get_pay_meth');
666
667 OPEN get_pay_meth(ln_chq_asg_action_id,l_pactid);
668 FETCH get_pay_meth INTO l_payee_meth_id
669 ,l_per_pay_method
670 ,l_det_org_pay_method;
671 CLOSE get_pay_meth;
672
673 hr_utility.trace('Before Bank Details');
674
675 if l_det_org_pay_method is not null then
676 OPEN get_org_bank_details(l_det_org_pay_method,l_effective_date);
677 FETCH get_org_bank_details INTO
678 l_bank_name
679 ,l_branch_name
680 ,l_account_name
681 ,l_account_number
682 ,l_transit_code
683 ,l_account_typ ;
684 CLOSE get_org_bank_details;
685 elsif ( l_payee_meth_id IS NULL AND l_per_pay_method IS NOT NULL ) then
686 OPEN get_person_bank_details(l_per_pay_method,l_effective_date);
687 FETCH get_person_bank_details INTO
688 l_bank_name
689 ,l_branch_name
690 ,l_account_name
691 ,l_account_number
692 ,l_transit_code
693 ,l_account_typ ;
694 CLOSE get_person_bank_details;
695 end if;
696
697 hr_utility.trace('Before Archive Cheque');
698
699 pay_core_files.write_to_magtape_lob('<?xml version="1.0" encoding="'||hr_mx_utility.get_IANA_charset||'"?>');
700 pay_core_files.write_to_magtape_lob('<ARCHIVE_CHEQUE_WRITER>');
701
702 hr_utility.trace('Calling generate_xml_dummy');
703
704 generate_xml_dummy(l_cheque_no
705 ,l_account_name
706 ,l_account_number
707 ,l_account_typ
708 ,l_transit_code
709 ,l_bank_name
710 ,l_branch_name
711 ,l_employee_number
712 ,l_full_name
713 ,l_national_identifier
714 ,ltr_xml_dummy_tab) ;
715 hr_utility.trace('After Calling generate_xml_dummy');
716
717 dbms_lob.createTemporary(l_xml_dummy, true, dbms_lob.session);
718
719 FOR cntr IN ltr_xml_dummy_tab.first()..ltr_xml_dummy_tab.last()
720 LOOP
721 hr_utility.trace('Accessing..' || ltr_xml_dummy_tab(cntr));
722
723 lr_xml_dummy := utl_raw.cast_to_raw(ltr_xml_dummy_tab(cntr));
724 ln_amt_dummy := utl_raw.length(lr_xml_dummy);
725
726 dbms_lob.writeAppend(l_xml_dummy,
727 ln_amt_dummy,
728 lr_xml_dummy);
729 END LOOP;
730
731 dummy_xml := l_xml_dummy;
732
733 hr_utility.trace('Successful LOB Creation.');
734 pay_core_files.write_to_magtape_lob(dummy_xml);
735
736 pay_core_files.write_to_magtape_lob('</ARCHIVE_CHEQUE_WRITER>');
737
738 BEGIN
739 UPDATE pay_assignment_actions
740 SET action_status = 'S'
741 WHERE assignment_action_id = ln_chq_asg_action_id;
742
743 hr_utility.trace('Update Successful..');
744 EXCEPTION
745
746 WHEN OTHERS THEN
747 hr_utility.trace('Update Unsuccessful..');
748 END;
749 print_blob(pay_mag_tape.g_blob_value);
750 dbms_lob.freeTemporary(l_xml_dummy);
751
752 END IF;
753
754 hr_utility.trace('Leaving pay_archive_chequewriter.generate_xml');
755
756 EXCEPTION WHEN OTHERS THEN
757 hr_utility.trace('SQLERRM := '||SQLERRM) ;
758 END GENERATE_XML ;
759
760 BEGIN
761 g_proc_name := 'pay_archive_chequewriter';
762 g_document_type := 'ARCHIVE_CHEQUE_WRITER';
763 g_debug := hr_utility.debug_enabled;
764
765 END pay_archive_chequewriter;