[Home] [Help]
PACKAGE BODY: APPS.PAY_US_NACHA_TAPE
Source
1 package body pay_us_nacha_tape as
2 /* $Header: pytapnac.pkb 120.9 2011/10/05 10:02:52 abellur ship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1993 Oracle Corporation. *
7 * All rights reserved. *
8 * *
9 * This material has been provided pursuant to an agreement *
10 * containing restrictions on its use. The material is also *
11 * protected by copyright law. No part of this material may *
12 * be copied or distributed, transmitted or transcribed, in *
13 * any form or by any means, electronic, mechanical, magnetic, *
14 * manual, or otherwise, or disclosed to third parties without *
15 * the express written permission of Oracle Corporation, *
16 * 500 Oracle Parkway, Redwood City, CA, 94065. *
17 * *
18 ******************************************************************
19
20 Name : pay_us_nacha_tape
21
22 Description : This package holds building blocks used in the generation
23 of nacha Tape.
24
25 Uses : hr_utility
26
27 Change List
28 -----------
29 Date Name Vers Bug No Description
30 ---- ---- ---- ------ -----------
31 JUL-23-1993 RMAMGAIN 1.0 Created with following proc.
32 . run_formula
33
34 NOV-01-1993 RMAMGAIN 2.0 Included Exception handling
35 JUN-28-1995 NBRISTOW 40.2 Package now uses PL/SQL
36 tables to interface with
37 the C process.
38 JUN-30-1995 NBRISTOW 40.3 NACHA_FILE_CONTROL parameters
39 were setup as context rules.
40 JUL-25-1995 AMILLS 40.3 Changed tokenised error
41 message 'HR_13103_SCL_FLEX_
42 NOT_FOUND' to hard coded
43 'HR_7711_SCL_FLEX_NOT_FOUND'
44
45 APR-12-1996 ALLEE Changed g_company_entry_date
46 to default to 'SALARY'
47 Added padding functionality
48 Added extra parameter for
49 Trace Number.
50
51 APR-19-1996 ALLEE Added Tax_unit_id context to
52 the Entry Description
53
54 APR-23-1996 ALLEE Passed the Transfer_pad_count
55 to the NACHA_FILE_HEADER
56 formula
57
58 APR-29-1996 ALLEE Removed the show errors
59 at the end.
60
61 JUL-25-1996 ALLEE Added the org_pay_meth cursor.
62 Included the Org_pay_entry_detail
63 and the org_pay_dummy formulas.
64
65 MAY-17-2000 DSCULLY Added support for child care
66 addenda records. Rewrote cursors
67
68 JUN-17-2000 DSCULLY Fixed error where seq. number for
69 entry detail records was being incremented
70 when addenda record was present. We now
71 increment g_addenda_count for addenda
72 records instead of g_count.
73 g_addenda_count is added to the record
74 total for block/padding purposes
75
76 ***************************************************************************************
77 Due to extensive changes in the 11.0 version, and little difference between the
78 previous 11.0 version and the 11.5 version, we are taking the modified 11.0 version
79 and redoing the changes made in earlier revisions of the 11.5 version
80 ***************************************************************************************
81 JUN-17-2000 DSCULLY 115.4 Modified 11.0 version and arcs into 11.5
82 codetree
83 OCT-10-2000 DSCULLY 115.6 Added contexts for NACHA_FILE_CONTROL
84 formula
85 MAY-01-2001 DGARG 115.7 1732778 Added code to set parameter value of
86 FILE_ID_MODIFIER as specified by
87 the user instead of defaulting it to 0.
88 MAY-08-2001 ahanda 115.8 Changed package to set the value of
89 g_file_id_modifier to 0.
90 JUL-10-2001 meshah 115.9 1167074 Created procedures for each formula.
91 JUL-19-2001 MESHAH 110.5 1357404 Changed write_batch_header,
92 write_org_entry_detail to set and reset the
93 flags and the fetch in the cursor
94 csr_assignments to get the rowid. Flags are
95 used to indicated if batch header should
96 be printed again along with all other records
97 following it. Also checking on the limit of
98 99,999,999.99 to indicate if the batch header
99 should be printed again.
100
101 JUL-27-2001 MESHAH 115.11 New parameter in legislative parameters,
102 TEST FILE. This parameter is passed to the
103 formula ENTRY DETAIL and ORG_PAY_ENTRY...
104 TEST_FILE is added to NACHA_ADDENDA also.
105 JUL-31-2001 MESHAH 115.12 New parameter of THIRD_PARTY for
106 NACHA_BATCH_HEADER.
107 DEC-20-2002 MESHAH 115.14 made nocopy and dbdrv changes.
108 FEB-18-2004 kvsankar 115.15 3331019 Bug fix as part of 10G certification.
109 Changes made to make use of RULE hint
110 only if db version is less than 10.0.
111 JUL-04-2004 ahanda 115.16 Fixes bugs 3715300, 3712003, 3711912,
112 3711907, 3704992
113 JUL-07-2004 ahanda 115.17 Fixed gscc warning.
114 OCT-21-2004 jgoswami 115.18 3962987 Change the length of v_attach_number to 50
115 NOV-21-2004 ahanda 115.19 Added NVL for reference/attachment number
116 MAR-09-2006 saurgupt 115.20 5019804 Set the message to error out process if
117 payment is greater than 99999999.99 .
118 JUL-23-2006 djoshi 115.21 5397759 Changed the logic to check for individual
119 employee and not the batch
120 Nov-07-2008 sudedas 115.22 7510559 Changed procedure get_third_party_details
121 to show correct Attachment Number and
122 Amount to show against Wage Attach Elements.
123 Nov-11-2008 sudedas 115.23 Changed the logic of determining Wage
124 Attachment Architecture.
125 Aug-05-2009 kagangul 115.24 Added function f_get_batch_transact_ident
126 for supporting the EFT reconciliation.
127 Aug-08-2009 mikarthi 115.25 Modifications for Nacha IAT enhancement
128 Feb-05-2009 asgugupt 115.26 8944172 Modified get_third_party_details so
129 that it gives meaningful error and info
130 so that user can correct EE for Garn EEs
131 Oct-05-2011 abellur 115.27 12848280 Modified f_get_batch_transact_ident
132 for 'Override Company ID' enhancement.
133 */
134 --
135 -------------------------- run_formula -------------------------------------
136 /*
137 NAME
138 run_formula
139 DESCRIPTION
140 Setup contexts and parameter for the formula. Setup next formula to call
141 so that Magtape ('C' process) could call appropriate formula
142 NOTES
143 C process 'pymx' uses parameters and contexts set by this procedure
144 to setup the interface for the formula and to call the formula.
145
146 begin
147 if g_business_group_id is null then
148 {
149 initialize global variables
150 open csr_org_flex_info
151 put all Org. payment info in Parameters
152 close csr_org_flex_info
153 Setup contexts and params for NACHA_FILE_HEADER formula
154 open csr_nacha_leg_comp
155 }
156 else
157 {
158 if g_addenda_write = 'Y'
159 {
160 set g_addenda_write = 'N'
161 setup NACHA_ADDENDA formula params and contexts
162 }
163
164 elsif g_batch_control_write = 'Y'
165 {
166 set g_batch_control_write = 'N'
167 setup NACHA_BATCH_CONTROL formula params and contexts
168 }
169
170 elsif csr_assignment is open then
171 {
172 fetch csr_assignments
173 if FOUND
174 then
175 {
176 setup NACHA_ENTRY_DETAIL formula params and Contexts
177 if payment of type third party, set g_addenda_write to 'Y'
178 }
179 else
180 {
181 set g_batch_control_write 'Y'
182 setup context and params for NACHA_ORG_PAY_ENTRY_DETAIL formula
183 }
184 end if;
185 }
186 else -- (csr_nacha_batch is open)
187 {
188 fetch csr_nacha_batch
189 if FOUND
190 then
191 {
192 open csr_assignment;
193 setup NACHA_BATCH_HEADER formula;
194 }
195 else
196 {
197 if pad_count = -1
198 then
199 {
200 initialize pad count
201 setup context and params for NACHA_FILE_CONTROL Formula
202 }
203 else if pad_count > 0 then
204 {
205 setup context and params for NACHA_PADDING Formula
206
207 if pad_count = 1 then
208 close csr_nacha_leg_comp
209 end if;
210 }
211 end if
212
213 end if
214 }
215 }
216 end if
217
218 end
219 */
220 --
221 PROCEDURE run_formula IS
222 --
223 -- Local Variable
224 v_prepayment_id number := null;
225 v_amount number := null;
226 v_block_count number := null;
227 v_fips_code varchar2(10);
228 v_med_ind varchar2(1);
229 v_payment_date date;
230 v_attach_number varchar2(50);
231
232 /***************************** Local Functions *******************/
233 /* ****************************************************************
234 NAME
235 get_third_party_details
236 DESCRIPTION
237 Gets element entry details for third party child care deductions.
238 NOTES
239 Local function.
240 ********************************************************************/
241
242 PROCEDURE get_third_party_details(p_amount number,
243 p_ppm_id number,
244 p_payment_date out nocopy date,
245 p_ref_no out nocopy varchar2,
246 p_fips_code out nocopy varchar2,
247 p_med_ind out nocopy varchar2)
248 IS
249 -- Following cursor is commented for Bug# 7510559
250 -- Instead introduced 2 cursors one for Old another for New
251 -- Wage Attachment Architecture Elements
252 -- Cursor csr_ele_details_old_arch for Old Wage Attach Arch
253 -- Cursor csr_ele_details_new_arch for New Wage Attach Arch
254
255 /*
256 CURSOR csr_ele_details is
257 select nvl(peev.screen_entry_value,'NULL') ref_no
258 ,nvl(peef.entry_information2,'NONE') -- FIPS code
259 ,nvl(peef.entry_information1,'N') -- Medical Indicator
260 ,ppa.effective_date payment_date
261 ,prr.run_result_id
262 from pay_element_entry_values_f peev,
263 pay_input_values_f piv_att,
264 pay_element_entries_f peef,
265 pay_run_results prr,
266 pay_payroll_actions ppa,
267 pay_assignment_actions paa,
268 pay_action_interlocks pai,
269 pay_pre_payments ppp
270 WHERE ppp.value = p_amount
271 and ppp.pre_payment_id = p_ppm_id
272 and ppp.assignment_action_id = pai.locking_action_id
273 and pai.locked_action_id = paa.assignment_action_id
274 and ppa.payroll_action_id = paa.payroll_action_id
275 and ppa.action_type in ('R', 'Q')
276 and ((paa.source_action_id is not null and ppa.run_type_id is not null) or
277 (paa.source_action_id is null and ppa.run_type_id is null))
278 and ppp.personal_payment_method_id = peef.personal_payment_method_id
279 and peef.assignment_id = paa.assignment_id
280 and ppa.date_earned between peev.effective_start_date
281 and peev.effective_end_date
282 and ppa.date_earned between peef.effective_start_date
283 and peef.effective_end_date
284 and piv_att.input_value_id = peev.input_value_id
285 and upper(piv_att.name) = 'ATTACHMENT NUMBER'
286 and ppa.effective_Date between piv_att.effective_start_date
287 and piv_att.effective_end_date
288 and peef.element_entry_id = peev.element_entry_id
289 and paa.assignment_Action_id = prr.assignment_Action_id
290 and prr.element_type_id = peef.element_type_id
291 order by prr.run_result_id;
292 */
293
294 -- Check if the Business Group is already upgraded
295 -- into New Wage Attach Architecture using 'Generic Upgrade Mechanism'
296
297 CURSOR csr_garn_arch IS
298 select distinct 'Y'
299 from pay_upgrade_definitions pud
300 ,pay_upgrade_status pus
301 where pud.short_name = 'US_INV_DEDN_UPGRADE'
302 and pud.legislation_code = 'US'
303 and pud.upgrade_definition_id = pus.upgrade_definition_id
304 and pus.status = 'C'
305 and pus.business_group_id = g_business_group_id;
306
307 -- Check Action Parameter Settings
308 -- Parameter Value 'Y' indicates New WA Arch
309 -- This parameter value will be used in conjunction
310 -- with value returned by cursor csr_garn_arch to determine
311 -- What Wage Attachment Architecture is in use
312
313 CURSOR csr_action_param IS
314 select parameter_value
315 from pay_action_parameters
316 where parameter_name = 'US_ADVANCED_WAGE_ATTACHMENT';
317
318 -- Fetch data for Old Arch Garn Elements
319 -- Query should return Correct Amount and Attachment Number
320 -- associated to Garnishment Elements
321
322 CURSOR csr_ele_details_old_arch IS
323 select nvl(peev.screen_entry_value,'NULL') ref_no
324 ,nvl(peef.entry_information2,'NONE') -- FIPS code
325 ,nvl(peef.entry_information1,'N') -- Medical Indicator
326 ,ppa.effective_date payment_date
327 ,prr_pay.run_result_id
328 from pay_element_entry_values_f peev,
329 pay_element_entry_values_f peev_pay,
330 pay_input_values_f piv_att,
331 pay_element_entries_f peef,
332 pay_run_results prr_att,
333 pay_run_results prr_pay,
334 pay_payroll_actions ppa,
335 pay_assignment_actions paa,
336 pay_action_interlocks pai,
337 pay_pre_payments ppp,
338 pay_input_values_f piv_pay,
339 pay_run_result_values prrv_att,
340 pay_run_result_values prrv_pay
341 WHERE ppp.value = p_amount
342 and ppp.pre_payment_id = p_ppm_id
343 and ppp.assignment_action_id = pai.locking_action_id
344 and pai.locked_action_id = paa.assignment_action_id
345 and ppa.payroll_action_id = paa.payroll_action_id
346 and ppa.action_type in ('R', 'Q')
347 and ((paa.source_action_id is not null and ppa.run_type_id is not null) or
348 (paa.source_action_id is null and ppa.run_type_id is null))
349 and ppp.personal_payment_method_id = peef.personal_payment_method_id
350 and peef.assignment_id = paa.assignment_id
351 and ppa.date_earned between peev.effective_start_date
352 and peev.effective_end_date
353 and ppa.date_earned between peef.effective_start_date
354 and peef.effective_end_date
355 and piv_att.input_value_id = peev.input_value_id
356 and upper(piv_att.name) = 'ATTACHMENT NUMBER'
357 and ppa.effective_Date between piv_att.effective_start_date
358 and piv_att.effective_end_date
359 and piv_att.input_value_id = prrv_att.input_value_id
360 and prrv_att.result_value = peev.screen_entry_value
361 and piv_pay.input_value_id = peev_pay.input_value_id
362 and ppa.effective_date between piv_pay.effective_start_date
363 and piv_pay.effective_end_date
364 and ppa.date_earned between peev_pay.effective_start_date
365 and peev_pay.effective_end_date
366 and piv_pay.input_value_id = prrv_pay.input_value_id
367 and upper(piv_pay.name) = 'PAY VALUE'
368 and fnd_number.number_to_canonical(ppp.value) = prrv_pay.result_value
369 and peef.element_entry_id = peev.element_entry_id
370 and peef.element_entry_id = peev_pay.element_entry_id
371 and paa.assignment_Action_id = prr_att.assignment_Action_id
372 and prr_att.run_result_id = prrv_att.run_result_id
373 and paa.assignment_action_id = prr_pay.assignment_action_id
374 and prr_pay.run_result_id = prrv_pay.run_result_id
375 and prr_att.element_type_id = peef.element_type_id
376 and prr_pay.element_type_id = peef.element_type_id
377 order by prr_pay.run_result_id;
378
379 -- Fetch data for New Arch Garn Elements
380 -- Query should return Correct Amount and Attachment Number
381 -- associated to Garnishment Elements
382
383 CURSOR csr_ele_details_new_arch IS
384 select nvl(peev.screen_entry_value,'NULL') ref_no
385 ,nvl(peef.entry_information2,'NONE') -- FIPS code
386 ,nvl(peef.entry_information1,'N') -- Medical Indicator
387 ,ppa.effective_date payment_date
388 ,prr_att.run_result_id
389 from pay_element_entry_values_f peev,
390 pay_input_values_f piv_att,
391 pay_element_entries_f peef,
392 pay_run_results prr_att,
393 pay_run_results prr_pay,
394 pay_payroll_actions ppa,
395 pay_assignment_actions paa,
396 pay_action_interlocks pai,
397 pay_pre_payments ppp,
398 pay_input_values_f piv_pay,
399 pay_run_result_values prrv_att,
400 pay_run_result_values prrv_pay,
401 pay_element_types_f pet,
402 pay_element_types_f pet_calc,
403 pay_element_classifications pec
404 WHERE ppp.value = p_amount
405 and ppp.pre_payment_id = p_ppm_id
406 and ppp.assignment_action_id = pai.locking_action_id
407 and pai.locked_action_id = paa.assignment_action_id
408 and ppa.payroll_action_id = paa.payroll_action_id
409 and ppa.action_type in ('R', 'Q')
410 and ((paa.source_action_id is not null and ppa.run_type_id is not null) or
411 (paa.source_action_id is null and ppa.run_type_id is null))
412 and ppp.personal_payment_method_id = peef.personal_payment_method_id
413 and peef.assignment_id = paa.assignment_id
414 and ppa.date_earned between peev.effective_start_date
415 and peev.effective_end_date
416 and ppa.date_earned between peef.effective_start_date
417 and peef.effective_end_date
418 and piv_att.input_value_id = peev.input_value_id
419 and upper(piv_att.name) = 'ATTACHMENT NUMBER'
420 and ppa.effective_Date between piv_att.effective_start_date
421 and piv_att.effective_end_date
422 and piv_att.input_value_id = prrv_att.input_value_id
423 and prrv_att.result_value = peev.screen_entry_value
424 and prrv_att.run_result_id = prr_att.run_result_id
425 and paa.assignment_Action_id = prr_att.assignment_Action_id
426 and prr_att.element_type_id = peef.element_type_id
427 and peef.element_type_id = pet.element_type_id
428 and pet.classification_id = pec.classification_id
429 and pec.classification_name = 'Involuntary Deductions'
430 and pec.legislation_code = 'US'
431 and pec.business_group_id IS NULL
432 and fnd_number.canonical_to_number(pet.element_information5) = pet_calc.element_type_id
433 and pet_calc.element_name like pet.element_name || '%Calculator'
434 and pet_calc.element_type_id = piv_pay.element_type_id
435 and NVL(ppa.date_earned, ppa.effective_date) between piv_pay.effective_start_date and piv_pay.effective_end_date
436 and piv_pay.input_value_id = prrv_pay.input_value_id
437 and upper(piv_pay.name) = 'PAY VALUE'
438 and fnd_number.number_to_canonical(ppp.value) = prrv_pay.result_value
439 and prr_pay.run_result_id = prrv_pay.run_result_id
440 and prr_pay.assignment_action_id = paa.assignment_action_id
441 and prr_pay.element_type_id = pet_calc.element_type_id
442 order by prr_att.run_result_id;
443
444 lv_ref_no VARCHAR2(50);
445 lv_fips_code VARCHAR2(10);
446 lv_med_ind VARCHAR2(1);
447 ld_payment_date DATE;
448 ln_run_result_id NUMBER;
449 lv_garn_arch VARCHAR2(10);
450 lv_act_param pay_action_parameters.parameter_value%TYPE;
451 lb_use_new_arch BOOLEAN ;
452
453 begin
454 hr_utility.trace('Entering pay_nacha_tape.get_third_party_details');
455 hr_utility.trace('p_amount = ' || p_amount);
456 hr_utility.trace('p_ppm_id = ' || p_ppm_id);
457 hr_utility.trace('g_business_group_id := ' || g_business_group_id);
458
459 open csr_garn_arch;
460 fetch csr_garn_arch into lv_garn_arch;
461 if csr_garn_arch%NOTFOUND then
462 lv_garn_arch := 'X';
463 end if;
464 close csr_garn_arch;
465 hr_utility.trace('lv_garn_arch := ' || lv_garn_arch);
466
467 open csr_action_param;
468 fetch csr_action_param into lv_act_param;
469 if csr_action_param%NOTFOUND then
470 lv_act_param := 'X';
471 end if;
472 hr_utility.trace('lv_act_param := ' || lv_act_param);
473
474 /* Following is the logic to determine what WA Arch is in use
475 BG Upgraded + Action Param 'Y' = New WA Arch
476 BG Upgraded + Action Param 'N' = New WA Arch
477 BG Upgraded + NO Action Param = New WA Arch
478 BG Not Upgraded + Action Param 'Y' = New WA Arch
479 BG Not Upgraded + Action Param 'N' = Old WA Arch
480 BG Not Upgraded + NO Action Param = New WA Arch
481 */
482
483 lb_use_new_arch := FALSE;
484
485 if ( NVL(lv_garn_arch, 'X') = 'Y'
486 or NVL(lv_act_param, 'X') = 'Y'
487 or ( NVL(lv_garn_arch, 'X') = 'X' AND NVL(lv_act_param, 'X') = 'X' ) ) then
488
489 lb_use_new_arch := TRUE;
490 else
491 lb_use_new_arch := FALSE;
492 end if;
493
494 if lb_use_new_arch then
495 open csr_ele_details_new_arch;
496 else
497 open csr_ele_details_old_arch;
498 end if;
499
500 hr_utility.trace('Open cursor csr_ele_details');
501
502 LOOP
503 if lb_use_new_arch then
504
505 fetch csr_ele_details_new_arch into
506 lv_ref_no,
507 lv_fips_code,
508 lv_med_ind,
509 ld_payment_date,
510 ln_run_result_id;
511
512 hr_utility.trace('After fetch csr_ele_details_new_arch');
513 hr_utility.trace('lv_ref_no := ' || lv_ref_no);
514 hr_utility.trace('lv_fips_code := ' || lv_fips_code);
515 hr_utility.trace('lv_med_ind := ' || lv_med_ind);
516 hr_utility.trace('ld_payment_date := ' || TO_CHAR(ld_payment_date));
517 hr_utility.trace('ln_run_result_id := ' || ln_run_result_id);
518
519 if csr_ele_details_new_arch%notfound then
520 hr_utility.trace('Not Found csr_ele_details_new_arch');
521 exit;
522 end if;
523 else
524
525 fetch csr_ele_details_old_arch into
526 lv_ref_no,
527 lv_fips_code,
528 lv_med_ind,
529 ld_payment_date,
530 ln_run_result_id;
531
532 hr_utility.trace('After fetch csr_ele_details_old_arch');
533 hr_utility.trace('lv_ref_no := ' || lv_ref_no);
534 hr_utility.trace('lv_fips_code := ' || lv_fips_code);
535 hr_utility.trace('lv_med_ind := ' || lv_med_ind);
536 hr_utility.trace('ld_payment_date := ' || TO_CHAR(ld_payment_date));
537 hr_utility.trace('ln_run_result_id := ' || ln_run_result_id);
538
539 if csr_ele_details_old_arch%notfound then
540 hr_utility.trace('Not Found csr_ele_details_old_arch');
541 exit;
542 end if;
543 end if;
544
545 if not (g_used_results_tab.EXISTS(ln_run_result_id)) then
546
547 hr_utility.trace('g_used_results_tab.EXISTS for := ' || ln_run_result_id);
548
549 g_used_results_tab(ln_run_result_id) := p_amount;
550 p_payment_date := ld_payment_date;
551 p_ref_no := lv_ref_no;
552 p_fips_code := lv_fips_code;
553 p_med_ind := lv_med_ind;
554
555 if lb_use_new_arch then
556 close csr_ele_details_new_arch;
557 else
558 close csr_ele_details_old_arch;
559 end if;
560
561 --close csr_ele_details;
562 hr_utility.trace('Exiting pay_nacha_tape.get_third_party_details - success');
563 return;
564 end if;
565
566 end LOOP;
567
568 --close csr_ele_details;
569 if lb_use_new_arch then
570 if csr_ele_details_new_arch%ISOPEN then
571 close csr_ele_details_new_arch;
572 end if;
573 else
574 if csr_ele_details_old_arch%ISOPEN then
575 close csr_ele_details_old_arch;
576 end if;
577 end if;
578
579 -- if we got here then we did not find a element that matches our needs
580 --Changes for Bug 8944172
581 hr_utility.trace('Exiting pay_nacha_tape.get_third_part_details - error');
582 hr_utility.set_message(801,'PAY_GARNISH_ELE_NOT_FOUND');
583 pay_core_utils.push_message(801,'PAY_GARNISH_ELE_NOT_FOUND','P');
584 pay_core_utils.push_token('ASSIGNMENT_ID',g_assignment_id);
585 raise_application_error(-20101, 'Error in pay_us_nacha_tape.get_third_party_details');
586 --Changes for Bug 8944172
587 end get_third_party_details;
588
589 /* ****************************************************************
590 NAME
591 get_formula_id
592 DESCRIPTION
593 Gets Formula id for a given formula name
594 NOTES
595 Local function.
596 ********************************************************************/
597
598 FUNCTION get_formula_id (p_formula_name varchar2)
599 RETURN varchar2 IS
600 ff_formula_id varchar2(9);
601 BEGIN
602 hr_utility.set_location('pay_us_nacha_tape.get_formula_id',1);
603 --
604 select TO_CHAR(FORMULA_ID) INTO ff_formula_id
605 from FF_FORMULAS_F
606 where g_effective_date between EFFECTIVE_START_DATE and
607 EFFECTIVE_END_DATE
608 and FORMULA_NAME = p_formula_name;
609 --
610 hr_utility.trace('Formula ID : '||ff_formula_id);
611 RETURN ff_formula_id;
612 exception
613 when no_data_found then
614 hr_utility.set_message(801,'FFX37_FORMULA_NOT_FOUND');
615 hr_utility.set_message_token('1',p_formula_name);
616 hr_utility.raise_error;
617 END get_formula_id;
618 ----
619 ----
620 --.
621 /* ***************************************************************
622 NAME
623 get_transfer_param
624 DESCRIPTION
625 Gets value for the named parameter
626 NOTES
627 Local function.
628 *** TEMP need to change when arrays are available.
629 *********************************************************************/
630
631 FUNCTION get_transfer_param ( p_param_name varchar2 )
632 RETURN Number IS
633 param_value number;
634 BEGIN
635 hr_utility.set_location('pay_us_nacha_tape.get_effective_date',20);
636 IF pay_mag_tape.internal_prm_names(3) = p_param_name
637 THEN
638 param_value := fnd_number.canonical_to_number(pay_mag_tape.internal_prm_values(3));
639 ELSIF pay_mag_tape.internal_prm_names(4) = p_param_name
640 THEN
641 param_value := fnd_number.canonical_to_number(pay_mag_tape.internal_prm_values(4));
642 ELSIF pay_mag_tape.internal_prm_names(5) = p_param_name
643 THEN
644 param_value := fnd_number.canonical_to_number(pay_mag_tape.internal_prm_values(5));
645 ELSIF pay_mag_tape.internal_prm_names(6) = p_param_name
646 THEN
647 param_value := fnd_number.canonical_to_number(pay_mag_tape.internal_prm_values(6));
648 ELSIF pay_mag_tape.internal_prm_names(7) = p_param_name
649 THEN
650 param_value := fnd_number.canonical_to_number(pay_mag_tape.internal_prm_values(7));
651 ELSIF pay_mag_tape.internal_prm_names(8) = p_param_name
652 THEN
653 param_value := fnd_number.canonical_to_number(pay_mag_tape.internal_prm_values(8));
654 END IF;
655 RETURN param_value;
656 END get_transfer_param;
657 --
658
659 /******************************************************************
660 NAME
661 write_file_header
662 DESCRIPTION
663 Writes the File Header Record .
664 NOTES
665 Local function.
666 ********************************************************************/
667
668
669 PROCEDURE write_file_header IS
670
671 BEGIN
672
673 hr_utility.trace('Writing File Header');
674 hr_utility.trace('.... Writing File Header Context');
675
676 pay_mag_tape.internal_cxt_values(1) := '3';
677 pay_mag_tape.internal_cxt_names(2) := 'ORG_PAY_METHOD_ID';
678 pay_mag_tape.internal_cxt_values(2) := g_org_payment_method_id;
679 pay_mag_tape.internal_cxt_names(3) := 'DATE_EARNED';
680 pay_mag_tape.internal_cxt_values(3) := fnd_date.date_to_canonical(g_effective_date);
681 --
682 hr_utility.trace('.... Writing File Header Parameters');
683
684 pay_mag_tape.internal_prm_values(1) := '6';
685 pay_mag_tape.internal_prm_values(2) := g_file_header;
686 pay_mag_tape.internal_prm_names(3) := 'TRANSFER_THIRD_PARTY';
687 pay_mag_tape.internal_prm_values(3) := g_csr_org_pay_third_party;
688 pay_mag_tape.internal_prm_names(4) := 'FILE_ID_MODIFIER';
689 pay_mag_tape.internal_prm_values(4) := g_file_id_modifier;
690 pay_mag_tape.internal_prm_names(5) := 'CREATION_DATE';
691 pay_mag_tape.internal_prm_values(5) := g_date;
692 pay_mag_tape.internal_prm_names(6) := 'CREATION_TIME';
693 pay_mag_tape.internal_prm_values(6) := g_time;
694
695 hr_utility.trace('Leaving File Header');
696
697 hr_utility.set_location('run_formula.File_head',6);
698
699 END; /* end write_file_header */
700
701 /******************************************************************
702 * NAME
703 * write_batch_header
704 * DESCRIPTION
705 * Writes the Batch Header Record .
706 * NOTES
707 * Local function.
708 *******************************************************************/
709 PROCEDURE write_batch_header
710 IS
711
712 BEGIN
713 hr_utility.trace('Writing Batch Header');
714
715 g_overflow_batch := 'N';
716 hr_utility.trace('.... g_overflow_batch is : '|| g_overflow_batch);
717
718 -- Bug 3331019
719 if (nvl(hr_general2.get_oracle_db_version, 0) < 10.0) then
720 OPEN csr_assignments (g_legal_company_id,
721 g_payroll_action_id,
722 g_csr_org_pay_meth_id,
723 g_rowid );
724 else
725 OPEN csr_assignments_no_rule (g_legal_company_id,
726 g_payroll_action_id,
727 g_csr_org_pay_meth_id,
728 g_rowid );
729 end if;
730
731 g_temp_count := 0;
732 g_batch_number := g_batch_number + 1;
733
734 -- Context for NACHA_BATCH_HEADER
735 -- first context is number of contexts
736 hr_utility.trace('.... Writing Batch Header Context');
737
738 pay_mag_tape.internal_cxt_values(1) := '4';
739 pay_mag_tape.internal_cxt_names(2) := 'TAX_UNIT_ID';
740 pay_mag_tape.internal_cxt_values(2) := TO_CHAR(g_legal_company_id);
741 pay_mag_tape.internal_cxt_names(3) := 'DATE_EARNED';
742 pay_mag_tape.internal_cxt_values(3) := fnd_date.date_to_canonical(g_effective_date);
743 pay_mag_tape.internal_cxt_names(4) := 'ORG_PAY_METHOD_ID';
744 pay_mag_tape.internal_cxt_values(4) := g_org_payment_method_id;
745
746 -- Parameters for NACHA_BATCH_HEADER
747 -- first parameter is number of parameters
748 -- second parameter is formula is
749 hr_utility.trace('.... Writing Batch Header Parameters');
750
751 pay_mag_tape.internal_prm_values(1) := '8';
752 pay_mag_tape.internal_prm_values(2) := g_batch_header;
753
754 -- 3 TRANSFER_THIRD_PARTY
755 -- pay_mag_tape.internal_prm_names(8) := 'TRANSFER_THIRD_PARTY';
756 -- pay_mag_tape.internal_prm_values(8) := g_csr_org_pay_third_party;
757
758 pay_mag_tape.internal_prm_names(5) := 'COMPANY_DESCRIPTIVE_DATE';
759 pay_mag_tape.internal_prm_values(5) := g_descriptive_date;
760 pay_mag_tape.internal_prm_names(6) := 'EFFECTIVE_ENTRY_DATE';
761 pay_mag_tape.internal_prm_values(6) := nvl(g_direct_dep_date,
762 TO_CHAR(g_effective_date,'YYMMDD'));
763 pay_mag_tape.internal_prm_names(7) := 'BATCH_NUMBER';
764 pay_mag_tape.internal_prm_values(7) := TO_CHAR(g_batch_number);
765
766 pay_mag_tape.internal_prm_names(4) := 'COMPANY_ENTRY_DESCRIPTION';
767 pay_mag_tape.internal_prm_names(8) := 'FORMAT_TYPE';
768
769 -- the format type depends on whether the opm is third party or not
770 if g_csr_org_pay_third_party = 'Y' then
771 pay_mag_tape.internal_prm_values(4) := 'CHILD SUPP';
772 pay_mag_tape.internal_prm_values(8) := 'CCD';
773 else
774 pay_mag_tape.internal_prm_values(4) := g_company_entry_desc;
775 pay_mag_tape.internal_prm_values(8) := 'PPD';
776 end if;
777
778
779 hr_utility.trace('Leaving Batch Header');
780
781 END; /* write_batch_header */
782
783 /******************************************************************
784 NAME
785 write_entry_detail
786 DESCRIPTION
787 Writes the Entry Detail Record .
788 NOTES
789 Local function.
790 ********************************************************************/
791
792
793 PROCEDURE write_entry_detail IS
794
795 BEGIN
796
797 hr_utility.trace('Writing Entry Detail');
798
799 hr_utility.trace('.... Writing Entry Detail Context');
800 g_count := g_count + 1;
801
802 -- Context Setup for NACHA_ENTRY_DETAIL
803 -- First context value is number of contexts
804 pay_mag_tape.internal_cxt_values(1) := '7';
805 pay_mag_tape.internal_cxt_names(2) := 'ASSIGNMENT_ID';
806 pay_mag_tape.internal_cxt_values(2) := TO_CHAR(g_assignment_id);
807 pay_mag_tape.internal_cxt_names(3) := 'ASSIGNMENT_ACTION_ID';
808 pay_mag_tape.internal_cxt_values(3) := TO_CHAR(g_assignment_action_id);
809 pay_mag_tape.internal_cxt_names(4) := 'DATE_EARNED';
810 pay_mag_tape.internal_cxt_values(4) := fnd_date.date_to_canonical(g_effective_date);
811 pay_mag_tape.internal_cxt_names(5) := 'PER_PAY_METHOD_ID';
812 pay_mag_tape.internal_cxt_values(5) := to_char(g_personal_payment_method_id);
813 pay_mag_tape.internal_cxt_names(6) := 'ORG_PAY_METHOD_ID';
814 pay_mag_tape.internal_cxt_values(6) := g_org_payment_method_id;
815 pay_mag_tape.internal_cxt_names(7) := 'TAX_UNIT_ID';
816 pay_mag_tape.internal_cxt_values(7) := TO_CHAR(g_legal_company_id);
817
818 -- Parameter Setup for NACHA_ENTRY_DETAIL
819 -- First parameter value is number of parameters
820 -- second parameter value is formula id
821
822 hr_utility.trace('.... Writing Entry Detail Parameters');
823
824 pay_mag_tape.internal_prm_values(1) := '11';
825 pay_mag_tape.internal_prm_values(2) := g_entry_detail;
826
827 -- Parameters 3-6 are transferred from previous formula
828 -- 3 - TRANSFER_THIRD_PARTY
829 -- 4 - TRANSFER_ENTRY_COUNT
830 -- 5 - TRANSFER_ENTRY_HASH
831 -- 6 - TRANSFER_CREDIT_AMOUNT
832 pay_mag_tape.internal_prm_names(7) := 'TRANSFER_PAY_VALUE';
833 pay_mag_tape.internal_prm_values(7) := fnd_number.number_to_canonical(v_amount);
834
835 pay_mag_tape.internal_prm_names(8) := 'TRANSFER_PREPAYMENT_ID';
836 pay_mag_tape.internal_prm_values(8) := TO_CHAR(v_prepayment_id);
837
838 -- Parameter 9 is transferred from previous formula - TRANSFER_ORG_PAY_TOT
839
840 pay_mag_tape.internal_prm_names(10) := 'TRACE_SEQUENCE_NUMBER';
841 pay_mag_tape.internal_prm_values(10) := TO_CHAR(g_count);
842
843 pay_mag_tape.internal_prm_names(11) := 'TEST_FILE';
844 pay_mag_tape.internal_prm_values(11) := g_test_file;
845
846 hr_utility.set_location('run_formula.Assignment',7);
847 IF g_temp_count = 0 THEN
848 -- If this is the first entry detail of a batch, reset these
849 -- parameters.
850 pay_mag_tape.internal_prm_names(4) := 'TRANSFER_ENTRY_COUNT';
851 pay_mag_tape.internal_prm_values(4) := '0';
852 pay_mag_tape.internal_prm_names(5) := 'TRANSFER_ENTRY_HASH';
853 pay_mag_tape.internal_prm_values(5) := '0';
854 pay_mag_tape.internal_prm_names(6) := 'TRANSFER_CREDIT_AMOUNT';
855 pay_mag_tape.internal_prm_values(6) := '0';
856 pay_mag_tape.internal_prm_names(9) := 'TRANSFER_ORG_PAY_TOT';
857 pay_mag_tape.internal_prm_values(9) := '0';
858
859 g_temp_count := 1;
860 hr_utility.set_location('run_formula.Assignment',8);
861 END IF;
862
863 IF g_csr_org_pay_third_party = 'Y' THEN
864 g_addenda_write := 'Y';
865 ELSE
866 g_addenda_write := 'N';
867 END IF;
868
869 -- Update PRENOTE Date
870 if v_amount = 0 then
871 update PAY_EXTERNAL_ACCOUNTS a
872 set a.PRENOTE_DATE = nvl(to_date(g_direct_dep_date,'YYMMDD'),
873 g_effective_date)
874 where a.PRENOTE_DATE is null
875 and a.EXTERNAL_ACCOUNT_ID =
876 ( select b.EXTERNAL_ACCOUNT_ID
877 from PAY_PERSONAL_PAYMENT_METHODS_F b
878 where b.PERSONAL_PAYMENT_METHOD_ID =
879 g_personal_payment_method_id
880 and g_effective_date between b.EFFECTIVE_START_DATE
881 and b.EFFECTIVE_END_DATE);
882 end if;
883
884
885 hr_utility.trace('Leaving Entry Detail');
886
887 END; /* write_entry_detail */
888
889 /******************************************************************
890 NAME
891 write_addenda
892 DESCRIPTION
893 Writes the Addenda Record .
894 NOTES
895 Local function.
896 ********************************************************************/
897
898
899 PROCEDURE write_addenda IS
900
901 BEGIN
902
903 hr_utility.trace('Writing Addenda');
904
905 g_addenda_write := 'N';
906
907 get_third_party_details(
908 fnd_number.canonical_to_number(get_transfer_param('TRANSFER_PAY_VALUE')),
909 fnd_number.canonical_to_number(get_transfer_param('TRANSFER_PREPAYMENT_ID')),
910 v_payment_date,
911 v_attach_number,
912 v_fips_code,
913 v_med_ind);
914
915 hr_utility.trace('.... Writing Addenda Context');
916
917 -- Context Setup for NACHA_ADDENDA
918 -- First context value is number of Context Values
919
920 pay_mag_tape.internal_cxt_values(1) := '7';
921 pay_mag_tape.internal_cxt_names(2) := 'ASSIGNMENT_ID';
922 pay_mag_tape.internal_cxt_values(2) := TO_CHAR(g_assignment_id);
923 pay_mag_tape.internal_cxt_names(3) := 'ASSIGNMENT_ACTION_ID';
924 pay_mag_tape.internal_cxt_values(3) := TO_CHAR(g_assignment_action_id);
925 pay_mag_tape.internal_cxt_names(4) := 'DATE_EARNED';
926 pay_mag_tape.internal_cxt_values(4) := fnd_date.date_to_canonical(g_effective_date);
927 pay_mag_tape.internal_cxt_names(5) := 'PER_PAY_METHOD_ID';
928 pay_mag_tape.internal_cxt_values(5) := to_char(g_personal_payment_method_id);
929 pay_mag_tape.internal_cxt_names(6) := 'ORG_PAY_METHOD_ID';
930 pay_mag_tape.internal_cxt_values(6) := g_org_payment_method_id;
931 pay_mag_tape.internal_cxt_names(7) := 'TAX_UNIT_ID';
932 pay_mag_tape.internal_cxt_values(7) := TO_CHAR(g_legal_company_id);
933
934 hr_utility.trace('.... Writing Addenda Parameters');
935
936 -- Parameter Setup for NACHA_ADDENDA
937 -- First Parameter Value is number of parameters
938 pay_mag_tape.internal_prm_values(1) := '14';
939 -- second is formula id
940 pay_mag_tape.internal_prm_values(2) := g_addenda;
941
942 -- Parameters 3-6 are transferred from previous formula
943 -- 3 - TRANSFER_THIRD_PARTY
944 -- 4 - TRANSFER_ENTRY_COUNT
945 -- 5 - TRANSFER_ENTRY_HASH
946 -- 6 - TRANSFER_CREDIT_AMOUNT
947
948 pay_mag_tape.internal_prm_names(8) := 'TRACE_SEQUENCE_NUMBER';
949 pay_mag_tape.internal_prm_values(8) := TO_CHAR(g_count);
950
951 -- Parameter 9 is transferred from previous formula - TRANSFER_ORG_PAY_TOT
952
953 pay_mag_tape.internal_prm_names(10) := 'FIPS_CODE';
954 pay_mag_tape.internal_prm_values(10) := v_fips_code;
955 pay_mag_tape.internal_prm_names(11) := 'MEDICAL_INDICATOR';
956 pay_mag_tape.internal_prm_values(11) := v_med_ind;
957 pay_mag_tape.internal_prm_names(12) := 'REFERENCE_NUMBER';
958 pay_mag_tape.internal_prm_values(12) := v_attach_number;
959 pay_mag_tape.internal_prm_names(13) := 'PAY_DATE';
960 pay_mag_tape.internal_prm_values(13) := to_char(v_payment_date,'YYMMDD');
961
962 pay_mag_tape.internal_prm_names(14) := 'TEST_FILE';
963 pay_mag_tape.internal_prm_values(14) := g_test_file;
964
965
966 -- we do not change the count till after so we can have the same trace number
967 -- in both entry detail and addenda rec
968
969 g_addenda_count := g_addenda_count + 1;
970 hr_utility.trace('Leaving Addenda');
971
972 END; /* write_addenda */
973
974 /******************************************************************
975 NAME
976 write_org_entry_detail
977 DESCRIPTION
978 Writes the Org Entry Detail Record .
979 NOTES
980 Local function.
981 ********************************************************************/
982
983
984 PROCEDURE write_org_entry_detail IS
985
986 BEGIN
987
988 hr_utility.trace('Writing Org Entry Detail');
989
990 If g_nacha_balance_flag = 'Y' then
991
992 g_count := g_count + 1;
993
994 end if;
995 g_batch_control_write := 'Y';
996
997 if g_overflow_flag = 'Y' then
998 g_overflow_flag := 'N';
999 g_overflow_batch := 'Y';
1000 end if;
1001
1002 -- Context Setup for NACHA_ORG_PAY_ENTRY_DETAIL
1003 -- first context is number of context values
1004 hr_utility.trace('.... Writing Org Entry Detail Context');
1005
1006 pay_mag_tape.internal_cxt_values(1) := '3';
1007 pay_mag_tape.internal_cxt_names(2) := 'ORG_PAY_METHOD_ID';
1008 pay_mag_tape.internal_cxt_values(2) := g_csr_org_pay_meth_id;
1009 pay_mag_tape.internal_cxt_names(3) := 'DATE_EARNED';
1010 pay_mag_tape.internal_cxt_values(3) := fnd_date.date_to_canonical(g_effective_date);
1011
1012 -- Parameter Setup for NACHA_ORG_PAY_ENTRY_DETAIL
1013 -- first parameter is number of parameters
1014 -- second parameter is formula is
1015 hr_utility.trace('.... Writing Org Entry Detail Parameters');
1016
1017 pay_mag_tape.internal_prm_values(1) := '10';
1018 pay_mag_tape.internal_prm_values(2) := g_org_pay_entry_detail;
1019
1020 -- Parameters 3-6 are transferred from previous formula
1021 -- 3 - TRANSFER_THIRD_PARTY
1022 -- 4 - TRANSFER_ENTRY_COUNT
1023 -- 5 - TRANSFER_ENTRY_HASH
1024 -- 6 - TRANSFER_CREDIT_AMOUNT
1025
1026 pay_mag_tape.internal_prm_names(8) := 'TRACE_SEQUENCE_NUMBER';
1027 pay_mag_tape.internal_prm_values(8) := TO_CHAR(g_count);
1028
1029 -- Parameter 9 is transferred from previous formula - TRANSFER_ORG_PAY_TOT
1030
1031 pay_mag_tape.internal_prm_names(10) := 'TEST_FILE';
1032 pay_mag_tape.internal_prm_values(10) := g_test_file;
1033
1034
1035 -- Bug 3331019
1036 if (nvl(hr_general2.get_oracle_db_version, 0) < 10.0) then
1037 CLOSE csr_assignments;
1038 else
1039 CLOSE csr_assignments_no_rule;
1040 end if;
1041 hr_utility.set_location('run_formula.org_pay_entry_detail',9);
1042
1043 hr_utility.trace('Leaving Org Entry Detail');
1044
1045 END; /* write_org_entry_detail */
1046
1047
1048 /******************************************************************
1049 NAME
1050 write_batch_control
1051 DESCRIPTION
1052 Writes the Batch Control Record .
1053 NOTES
1054 Local function.
1055 ********************************************************************/
1056
1057
1058 PROCEDURE write_batch_control IS
1059
1060 BEGIN
1061 hr_utility.trace('Writing Batch Control');
1062
1063 g_batch_control_write := 'N';
1064
1065 g_hash := g_hash + get_transfer_param ('TRANSFER_ENTRY_HASH');
1066 g_amount := g_amount + get_transfer_param ('TRANSFER_CREDIT_AMOUNT');
1067
1068 -- Context Setup for NACHA_BATCH_CONTROL
1069 -- First context value is number of context values
1070
1071 hr_utility.trace('.... Writing Batch Control Context');
1072
1073 pay_mag_tape.internal_cxt_values(1) := '4';
1074 pay_mag_tape.internal_cxt_names(2) := 'TAX_UNIT_ID';
1075 pay_mag_tape.internal_cxt_values(2) := TO_CHAR(g_legal_company_id);
1076 pay_mag_tape.internal_cxt_names(3) := 'DATE_EARNED';
1077 pay_mag_tape.internal_cxt_values(3) := fnd_date.date_to_canonical(g_effective_date);
1078 pay_mag_tape.internal_cxt_names(4) := 'ORG_PAY_METHOD_ID';
1079 pay_mag_tape.internal_cxt_values(4) := g_org_payment_method_id;
1080
1081 -- Parameter Setup for NACHA_BATCH_CONTROL
1082 -- First parameter value is number of parameters
1083
1084 hr_utility.trace('.... Writing Batch Control Parameters');
1085
1086 pay_mag_tape.internal_prm_values(1) := '7';
1087 pay_mag_tape.internal_prm_values(2) := g_batch_control;
1088
1089 -- Parameters 4-7 are transferred from previous formula
1090 -- 3 - TRANSFER_ENTRY_COUNT
1091 -- 4 - TRANSFER_ENTRY_HASH
1092 -- 5 - TRANSFER_CREDIT_AMOUNT
1093 -- 6 - TRANSFER_THIRD_PARTY
1094
1095 pay_mag_tape.internal_prm_names(7) := 'BATCH_NUMBER';
1096 pay_mag_tape.internal_prm_values(7) := TO_CHAR(g_batch_number);
1097
1098 hr_utility.set_location('run_formula.Batch_ctrl',9);
1099
1100 hr_utility.trace('Leaving Batch Control');
1101
1102 END; /* write_batch_control */
1103
1104
1105 /******************************************************************
1106 NAME
1107 write_file_control
1108 DESCRIPTION
1109 Writes the File Control Record .
1110 NOTES
1111 Local function.
1112 ********************************************************************/
1113
1114 PROCEDURE write_file_control IS
1115
1116 BEGIN
1117
1118 hr_utility.trace('Writing File Control');
1119
1120 v_block_count := CEIL(((2 * g_batch_number ) +
1121 g_count + g_addenda_count + 2)/10);
1122 g_pad_count := (v_block_count * 10) -
1123 ((2 * g_batch_number ) +
1124 g_count + g_addenda_count + 2);
1125
1126 hr_utility.trace('.... Writing File Control Context');
1127
1128 -- dscully - added contexts for NACHA_BALANCED_NACHA_FILE DBI
1129 pay_mag_tape.internal_cxt_values(1) := '3';
1130 pay_mag_tape.internal_cxt_names(2) := 'ORG_PAY_METHOD_ID';
1131 pay_mag_tape.internal_cxt_values(2) := g_org_payment_method_id;
1132 pay_mag_tape.internal_cxt_names(3) := 'DATE_EARNED';
1133 pay_mag_tape.internal_cxt_values(3) := fnd_date.date_to_canonical(g_effective_date);
1134
1135 hr_utility.trace('.... Writing File Control Parameters');
1136
1137 pay_mag_tape.internal_prm_values(1) := '8';
1138 pay_mag_tape.internal_prm_values(2) := g_file_control;
1139 pay_mag_tape.internal_prm_names(3) := 'BATCH_NUMBER';
1140 pay_mag_tape.internal_prm_values(3) := TO_CHAR(g_batch_number);
1141 pay_mag_tape.internal_prm_names(4) := 'BLOCK_COUNT';
1142 pay_mag_tape.internal_prm_values(4) := TO_CHAR(v_block_count);
1143 pay_mag_tape.internal_prm_names(5) := 'FILE_ENTRY_COUNT';
1144 pay_mag_tape.internal_prm_values(5) := TO_CHAR(g_count + g_addenda_count);
1145 pay_mag_tape.internal_prm_names(6) := 'FILE_ENTRY_HASH';
1146 pay_mag_tape.internal_prm_values(6) := TO_CHAR(g_hash);
1147 pay_mag_tape.internal_prm_names(7) := 'FILE_CREDIT_AMOUNT';
1148 pay_mag_tape.internal_prm_values(7) := fnd_number.number_to_canonical(g_amount);
1149 pay_mag_tape.internal_prm_names(8) := 'TRANSFER_PAD_COUNT';
1150 pay_mag_tape.internal_prm_values(8) := TO_CHAR(g_pad_count);
1151 --
1152 hr_utility.set_location('run_formula.File_Control',11);
1153 hr_utility.trace('Leaving File Control');
1154
1155 END; /* write_file_control */
1156
1157
1158 /******************************************************************
1159 NAME
1160 write_padding
1161 DESCRIPTION
1162 Writes the Padding Record .
1163 NOTES
1164 Local function.
1165 ********************************************************************/
1166 PROCEDURE write_padding IS
1167
1168 BEGIN
1169
1170 hr_utility.trace('Writing Padding');
1171
1172 hr_utility.trace('.... Writing Padding Context');
1173 pay_mag_tape.internal_cxt_values(1) := '1';
1174
1175 hr_utility.trace('.... Writing Padding Parameters');
1176
1177 pay_mag_tape.internal_prm_values(1) := '3';
1178 pay_mag_tape.internal_prm_values(2) := g_padding;
1179 pay_mag_tape.internal_prm_names(3) := 'TRANSFER_PAD_COUNT';
1180 pay_mag_tape.internal_prm_values(3) := TO_CHAR(g_pad_count);
1181
1182 hr_utility.set_location('run_formula.padding',12);
1183 IF g_pad_count = 1 THEN
1184 CLOSE csr_nacha_batch;
1185 ELSE
1186 g_pad_count := g_pad_count - 1;
1187 END IF;
1188
1189 hr_utility.trace('Leaving Padding');
1190
1191 END; /* write_padding */
1192
1193 /*****************************END of Local Functions ****************/
1194
1195
1196 BEGIN
1197 hr_utility.trace('Entering pay_us_nacha_tape.run_formula');
1198 pay_mag_tape.internal_prm_names(1) := 'NO_OF_PARAMETERS';
1199 pay_mag_tape.internal_prm_names(2) := 'NEW_FORMULA_ID';
1200 pay_mag_tape.internal_prm_values(1) := '2';
1201
1202 pay_mag_tape.internal_cxt_names(1) := 'NUMBER_OF_CONTEXT';
1203 pay_mag_tape.internal_cxt_values(1) := '1';
1204 hr_utility.set_location('pay_us_nacha_tape.run_formula',1);
1205
1206 hr_utility.set_location ('run_formula loop',1);
1207
1208 --Checking If the transaction is IAT
1209 IF g_foreign_transact = 'Y' then
1210 --Call the new package for IAT
1211 pay_us_nacha_iat_tape.run_formula (g_business_group_id,
1212 g_effective_date,
1213 g_direct_dep_date,
1214 g_org_payment_method_id,
1215 g_csr_org_pay_third_party,
1216 g_file_id_modifier,
1217 g_test_file,
1218 g_payroll_id);
1219 ELSE
1220
1221 /****************Level 1.1 The first major if clause ***************/
1222 IF NOT csr_nacha_batch%ISOPEN and g_business_group_id is NULL THEN
1223 /* main */
1224
1225 hr_utility.set_location('run_formula.Init',5);
1226 g_payroll_action_id := fnd_number.canonical_to_number(
1227 pay_mag_tape.internal_prm_values(3));
1228
1229 /* Select all the relevent information using payroll action id */
1230 select ppa.business_group_id,
1231 ppa.effective_date,
1232 to_char(ppa.overriding_dd_date,'YYMMDD'),
1233 ppa.org_payment_method_id,
1234 ppa.legislative_parameters,
1235 decode(nvl(to_char(opm.defined_balance_id),'Y'),'Y','Y','N'),
1236 ppa.payroll_id
1237 into g_business_group_id,
1238 g_effective_date,
1239 g_direct_dep_date,
1240 g_org_payment_method_id,
1241 g_legislative_parameters,
1242 g_csr_org_pay_third_party,
1243 g_payroll_id
1244 from pay_payroll_actions ppa,
1245 pay_org_payment_methods_f opm
1246 where ppa.payroll_action_id = g_payroll_action_id
1247 and opm.ORG_PAYMENT_METHOD_ID = ppa.org_payment_method_id
1248 and ppa.effective_date between opm.EFFECTIVE_START_DATE
1249 and opm.EFFECTIVE_END_DATE;
1250 if SQL%NOTFOUND then
1251 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1252 hr_utility.set_message_token('PROCEDURE','pay_us_nacha_tape');
1253 hr_utility.set_message_token('STEP','1');
1254 hr_utility.raise_error;
1255 end if;
1256
1257 /* Set The default to Zero */
1258 g_file_id_modifier := '0';
1259 if g_legislative_parameters is not null then
1260
1261 -- get the FILE_ID_MODIFIER
1262 if INSTR(g_legislative_parameters,'FILE_ID_MODIFIER=') <> 0 then
1263 g_file_id_modifier := SUBSTR(g_legislative_parameters,
1264 INSTR(g_legislative_parameters,
1265 'FILE_ID_MODIFIER=')
1266 + Length('FILE_ID_MODIFIER='), 1 );
1267 end if;
1268
1269 -- Get the Test File indicator.
1270 if INSTR(g_legislative_parameters,'TEST_FILE=') <> 0 then
1271 g_test_file := SUBSTR(g_legislative_parameters,
1272 INSTR(g_legislative_parameters,
1273 'TEST_FILE=')
1274 + Length('TEST_FILE='), 1 );
1275 end if;
1276
1277
1278 if INSTR(g_legislative_parameters,'FOREIGN_TRANSACT=') <> 0 then
1279 g_foreign_transact := SUBSTR(g_legislative_parameters,
1280 INSTR(g_legislative_parameters,
1281 'FOREIGN_TRANSACT=')
1282 + Length('FOREIGN_TRANSACT='), 1 );
1283 end if;
1284
1285 end if;
1286
1287 hr_utility.trace('g_foreign_transact: ' || g_foreign_transact);
1288
1289 --Checking If the transaction is IAT
1290 IF g_foreign_transact = 'Y' then
1291 --Call the new package for IAT
1292 pay_us_nacha_iat_tape.run_formula (g_business_group_id,
1293 g_effective_date,
1294 g_direct_dep_date,
1295 g_org_payment_method_id,
1296 g_csr_org_pay_third_party,
1297 g_file_id_modifier,
1298 g_test_file,
1299 g_payroll_id);
1300 ELSE
1301 g_company_entry_desc := 'SALARY';
1302 g_descriptive_date := g_date;
1303
1304
1305 -- Intialize global varibles
1306 g_temp_count := 0; /* Flag to initialize batch running totals */
1307 g_pad_count := -1; /* Number of times the padding formula called */
1308
1309
1310 -- Get all the formula id's in the global variable.
1311 g_file_header := get_formula_id('NACHA_FILE_HEADER');
1312 g_batch_header := get_formula_id('NACHA_BATCH_HEADER');
1313 g_entry_detail := get_formula_id('NACHA_ENTRY_DETAIL');
1314 g_addenda := get_formula_id('NACHA_ADDENDA');
1315 g_org_pay_entry_detail := get_formula_id('NACHA_ORG_PAY_ENTRY_DETAIL');
1316 g_batch_control := get_formula_id('NACHA_BATCH_CONTROL');
1317 g_file_control := get_formula_id('NACHA_FILE_CONTROL');
1318 g_padding := get_formula_id('NACHA_PADDING');
1319
1320
1321 -- If Org payment method is supplied then use it for getting SCL FLEX
1322 -- information otherwise use business group for to get the information.
1323 --
1324 IF g_org_payment_method_id is null THEN
1325 OPEN csr_org_flex_info (g_business_group_id,
1326 g_payroll_action_id);
1327 FETCH csr_org_flex_info INTO g_org_payment_method_id,
1328 g_csr_org_pay_third_party;
1329 CLOSE csr_org_flex_info;
1330 END IF;
1331
1332 IF g_org_payment_method_id is not null THEN
1333 hr_utility.trace('g_org_payment_method_id = ' ||
1334 g_org_payment_method_id);
1335 hr_utility.trace('g_csr_org_pay_third_party = ' ||
1336 g_csr_org_pay_third_party);
1337 write_file_header;
1338 ELSE
1339 hr_utility.set_message(801, 'HR_7711_SCL_FLEX_NOT_FOUND');
1340 hr_utility.raise_error;
1341 END IF;
1342
1343 OPEN csr_nacha_batch(g_business_group_id,g_payroll_action_id);
1344
1345 END IF;
1346
1347 /****************Level 1.2 The second major else if clause ***************/
1348 ELSE /* main */
1349
1350 hr_utility.set_location ('run_formula loop',2);
1351
1352 IF g_addenda_write = 'Y' THEN
1353
1354 write_addenda;
1355
1356 ELSIF g_batch_control_write = 'Y' THEN
1357
1358 write_batch_control;
1359
1360 -- Bug 3331019
1361 ELSIF (csr_assignments%ISOPEN OR csr_assignments_no_rule%ISOPEN) THEN
1362 if (nvl(hr_general2.get_oracle_db_version, 0) < 10.0) then
1363 FETCH csr_assignments INTO g_assignment_id,g_assignment_action_id,
1364 v_amount, g_personal_payment_method_id,
1365 v_prepayment_id,g_rowid;
1366 IF csr_assignments%FOUND THEN
1367
1368
1369 IF v_amount > 99999999.99 THEN
1370 hr_utility.set_message(801,'PAY_US_PAYMENT_OVERFLOW');
1371 pay_core_utils.push_message(801,'PAY_US_PAYMENT_OVERFLOW','P');
1372 pay_core_utils.push_token('ASSIGNMENT_NO',g_assignment_id);
1373 raise_application_error(-20101, 'Error in pay_us_nacha_tape.run_formula');
1374
1375 END IF ;
1376
1377
1378 g_overflow_amount := g_overflow_amount + v_amount;
1379
1380 hr_utility.trace('G_OVERFLOW_AMOUNT is : '|| g_overflow_amount);
1381 hr_utility.trace('G_ROWID value is : '|| g_rowid);
1382
1383 if g_overflow_amount > 99999999.99 then
1384 g_overflow_amount := 0;
1385 g_overflow_flag := 'Y';
1386 write_org_entry_detail;
1387 else
1388 write_entry_detail;
1389 end if;
1390
1391 ELSE /* setup context and params for NACHA_ORG_PAY_ENTRY_DETAIL */
1392
1393 write_org_entry_detail;
1394
1395 END IF;
1396 else
1397 FETCH csr_assignments_no_rule INTO g_assignment_id,g_assignment_action_id,
1398 v_amount, g_personal_payment_method_id,
1399 v_prepayment_id,g_rowid;
1400 IF csr_assignments_no_rule%FOUND THEN
1401
1402 g_overflow_amount := g_overflow_amount + v_amount;
1403
1404 IF v_amount > 99999999.99 THEN
1405 hr_utility.set_message(801,'PAY_US_PAYMENT_OVERFLOW');
1406 pay_core_utils.push_message(801,'PAY_US_PAYMENT_OVERFLOW','P');
1407 pay_core_utils.push_token('ASSIGNMENT_NO',g_assignment_id);
1408 raise_application_error(-20101, 'Error in pay_us_nacha_tape.run_formula');
1409 END IF;
1410
1411 hr_utility.trace('G_OVERFLOW_AMOUNT is : '|| g_overflow_amount);
1412 hr_utility.trace('G_ROWID value is : '|| g_rowid);
1413
1414 if g_overflow_amount > 99999999.99 then
1415 g_overflow_amount := 0;
1416 g_overflow_flag := 'Y';
1417 write_org_entry_detail;
1418 else
1419 write_entry_detail;
1420 end if;
1421
1422 ELSE /* setup context and params for NACHA_ORG_PAY_ENTRY_DETAIL */
1423
1424 write_org_entry_detail;
1425
1426 END IF;
1427 end if; /* nvl(hr_general2.get_oracle_db_version, 0) < 10.0 */
1428
1429 /****************Level 1.3 The third major else if clause ***************/
1430
1431 ELSE /* g_addenda_write = 'Y' */
1432
1433 hr_utility.trace('g_overflow_batch flag is : '|| g_overflow_batch);
1434
1435 IF g_overflow_batch = 'Y' then
1436 write_batch_header;
1437 ELSE /* g_overflow_batch */
1438
1439 FETCH csr_nacha_batch INTO g_csr_org_pay_meth_id,
1440 g_csr_org_pay_third_party,
1441 g_legal_company_id,
1442 g_nacha_balance_flag;
1443 IF csr_nacha_batch %FOUND THEN
1444
1445 /* to reset rowid when GRE changes. Bug 1967949 */
1446 hr_utility.trace('b4 g_legal_company_id is : ' || g_legal_company_id);
1447 hr_utility.trace('b4 g_reset_greid is : ' || g_reset_greid);
1448 hr_utility.trace('b4 g_rowid is : ' || g_rowid);
1449
1450 IF g_reset_greid <> g_legal_company_id then
1451
1452 g_rowid := null;
1453 g_reset_greid := g_legal_company_id;
1454
1455 END IF;
1456
1457 hr_utility.trace('a4 g_legal_company_id is : ' || g_legal_company_id);
1458 hr_utility.trace('a4 g_reset_greid is : ' || g_reset_greid);
1459 hr_utility.trace('a4 g_rowid is : ' || g_rowid);
1460
1461 /* to reset rowid when GRE changes. Bug 1967949 */
1462
1463
1464 write_batch_header;
1465
1466 ELSE /* We'll kick off the Nacha_file_control stuff */
1467
1468 IF g_pad_count = -1 THEN
1469
1470 write_file_control;
1471
1472 ELSIF g_pad_count > 0 THEN
1473
1474 write_padding;
1475
1476 END IF;
1477 END IF; /* csr_nacha_batch %FOUND */
1478 END IF; /* g_overflow_batch */
1479 END IF; /* g_addenda_write = 'Y' */
1480 END IF; /* main */
1481 END IF;
1482
1483 END run_formula;
1484 /* Bug 5098064 : Added for supporting EFT reconciliation */
1485
1486 FUNCTION f_get_batch_transact_ident(p_effective_date DATE,
1487 p_identifier_name VARCHAR2,
1488 p_payroll_action_id NUMBER,
1489 p_payment_type_id NUMBER,
1490 p_org_payment_method_id NUMBER,
1491 p_personal_payment_method_id NUMBER,
1492 p_assignment_action_id NUMBER,
1493 p_pre_payment_id NUMBER,
1494 p_delimiter_string VARCHAR2)
1495 RETURN VARCHAR2
1496 IS
1497
1498 CURSOR csr_get_payee_bank_details IS
1499 SELECT pea.segment1,pea.segment3,pea.segment5,pea.segment6
1500 FROM pay_external_accounts pea, pay_personal_payment_methods_f ppm,
1501 pay_payroll_actions ppa
1502 WHERE ppa.payroll_action_id = p_payroll_action_id
1503 AND ppm.personal_payment_method_id = p_personal_payment_method_id
1504 AND ppa.effective_date BETWEEN ppm.effective_start_date AND ppm.effective_end_date
1505 AND pea.external_account_id (+) = ppm.external_account_id;
1506
1507 lr_bank_detail_rec csr_get_payee_bank_details%ROWTYPE;
1508
1509 CURSOR csr_get_transact_date IS
1510 SELECT to_char(ppa.effective_date,'YYYY/MM/DD') effective_date,
1511 to_char(ppa.overriding_dd_date,'YYYY/MM/DD') overriding_dd_date
1512 FROM pay_payroll_actions ppa, pay_org_payment_methods_f opm, pay_assignment_actions paa
1513 WHERE ppa.payroll_action_id = p_payroll_action_id
1514 AND opm.org_payment_method_id = ppa.org_payment_method_id
1515 AND opm.org_payment_method_id = p_org_payment_method_id
1516 AND ppa.effective_date BETWEEN opm.effective_start_date AND opm.effective_end_date
1517 AND ppa.payroll_action_id = paa.payroll_action_id
1518 AND paa.assignment_action_id = p_assignment_action_id;
1519
1520 lr_transact_date_rec csr_get_transact_date%ROWTYPE;
1521
1522 CURSOR csr_get_batch_no IS
1523 SELECT ppp.org_payment_method_id, hou.organization_id, paa.assignment_action_id
1524 FROM pay_pre_payments ppp, pay_org_payment_methods_f opm,
1525 hr_organization_units hou, hr_organization_information hoi,
1526 pay_payroll_actions ppa, pay_assignment_actions paa
1527 WHERE opm.org_payment_method_id = ppp.org_payment_method_id
1528 AND opm.org_payment_method_id = ppa.org_payment_method_id
1529 AND opm.org_payment_method_id = p_org_payment_method_id
1530 AND ppa.payroll_action_id = p_payroll_action_id
1531 AND ppa.effective_date BETWEEN opm.effective_start_date AND opm.effective_end_date
1532 AND hou.business_group_id = ppa.business_group_id
1533 AND opm.business_group_id = ppa.business_group_id
1534 AND hou.organization_id = hoi.organization_id
1535 AND hoi.org_information_context = 'CLASS'
1536 AND hoi.org_information1 = 'HR_LEGAL'
1537 AND hoi.org_information2 = 'Y'
1538 AND paa.payroll_action_id = ppa.payroll_action_id
1539 AND paa.tax_unit_id = hou.organization_id
1540 AND paa.pre_payment_id = ppp.pre_payment_id
1541 AND EXISTS
1542 ( SELECT 1
1543 FROM per_assignments_f paf
1544 WHERE paf.assignment_id = paa.assignment_id
1545 AND ppa.effective_date BETWEEN paf.effective_start_date and paf.effective_end_date
1546 )
1547 ORDER BY ppp.org_payment_method_id, hou.organization_id;
1548
1549 lr_batch_no csr_get_batch_no%ROWTYPE;
1550
1551 CURSOR csr_get_conc_ident IS
1552 SELECT flv.meaning
1553 FROM fnd_lookup_types flt, fnd_lookup_values flv
1554 WHERE flt.lookup_type = 'PAYMENT_TRX_CONC_IDENTS'
1555 AND flt.application_id = (SELECT application_id FROM fnd_application
1556 WHERE application_short_name = 'PAY')
1557 AND flt.lookup_type = flv.lookup_type
1558 AND flv.lookup_code = 'NACHA'
1559 AND flv.language = 'US'
1560 AND flv.enabled_flag = 'Y'
1561 AND sysdate between flv.start_date_active and nvl(flv.end_date_active, sysdate);
1562
1563 lr_conc_ident csr_get_conc_ident%ROWTYPE;
1564
1565 ln_org_pay_method_id pay_pre_payments.org_payment_method_id%TYPE := -1;
1566 ln_org_id hr_organization_units.organization_id%TYPE := -1;
1567 ln_batch_number NUMBER(5) := 0;
1568 lv_return_value VARCHAR2(80) := NULL;
1569
1570 TYPE lt_identifier_rec_type IS RECORD (ident_name fnd_lookup_values.meaning%TYPE,
1571 ident_position NUMBER);
1572 lt_identifier_rec lt_identifier_rec_type;
1573
1574 TYPE lt_identifier_rec_table IS TABLE OF lt_identifier_rec_type INDEX BY BINARY_INTEGER;
1575 lt_identifier_table lt_identifier_rec_table;
1576
1577 counter NUMBER(5) := 1;
1578
1579 -- added opm.pmeth_information17 for 12848280
1580 CURSOR csr_get_conc_ident_values IS
1581 SELECT opm.pmeth_information3, nvl(opm.pmeth_information17,
1582 hoi.org_information4) org_information4
1583 FROM pay_org_payment_methods_f opm, pay_payroll_actions ppa,
1584 pay_assignment_actions paa, hr_organization_information hoi
1585 WHERE ppa.payroll_action_id = p_payroll_action_id
1586 AND ppa.effective_date BETWEEN opm.effective_start_date AND opm.effective_end_date
1587 AND ppa.org_payment_method_id = opm.org_payment_method_id
1588 AND ppa.payment_type_id = opm.payment_type_id
1589 and ppa.payroll_action_id = paa.payroll_action_id
1590 and paa.assignment_action_id = p_assignment_action_id
1591 and paa.tax_unit_id = hoi.organization_id
1592 and hoi.org_information_context = 'NACHA Rules';
1593
1594 lr_conc_ident_values csr_get_conc_ident_values%ROWTYPE;
1595 lb_add_delimiter BOOLEAN := FALSE;
1596
1597 BEGIN
1598
1599 IF UPPER(p_identifier_name) IN ('PAYEE_BANK_BRANCH','PAYEE_BANK_NAME',
1600 'PAYEE_BANK_ACCOUNT_NAME', 'PAYEE_BANK_ACCOUNT_NUMBER') THEN
1601
1602 OPEN csr_get_payee_bank_details;
1603 FETCH csr_get_payee_bank_details INTO lr_bank_detail_rec;
1604
1605 IF csr_get_payee_bank_details%FOUND THEN
1606 IF UPPER(p_identifier_name) = 'PAYEE_BANK_BRANCH' THEN
1607 lv_return_value := substr(lr_bank_detail_rec.segment6,1,80);
1608 ELSIF UPPER(p_identifier_name) = 'PAYEE_BANK_NAME' THEN
1609 lv_return_value := substr(lr_bank_detail_rec.segment5,1,80);
1610 ELSIF UPPER(p_identifier_name) = 'PAYEE_BANK_ACCOUNT_NAME' THEN
1611 lv_return_value := substr(lr_bank_detail_rec.segment1,1,80);
1612 ELSIF UPPER(p_identifier_name) = 'PAYEE_BANK_ACCOUNT_NUMBER' THEN
1613 lv_return_value := substr(lr_bank_detail_rec.segment3,1,80);
1614 END IF;
1615 ELSE
1616 lv_return_value := NULL;
1617 END IF;
1618
1619 CLOSE csr_get_payee_bank_details;
1620
1621 ELSIF UPPER(p_identifier_name) = 'TRANSACTION_DATE' THEN
1622
1623 OPEN csr_get_transact_date;
1624 FETCH csr_get_transact_date INTO lr_transact_date_rec;
1625
1626 IF csr_get_transact_date%FOUND THEN
1627 lv_return_value := NVL(lr_transact_date_rec.overriding_dd_date,
1628 lr_transact_date_rec.effective_date);
1629 ELSE
1630 lv_return_value := NULL;
1631 END IF;
1632
1633 CLOSE csr_get_transact_date;
1634
1635 ELSIF UPPER(p_identifier_name) = 'TRANSACTION_GROUP' THEN
1636
1637 OPEN csr_get_batch_no;
1638 LOOP
1639 FETCH csr_get_batch_no INTO lr_batch_no;
1640 EXIT WHEN csr_get_batch_no%NOTFOUND;
1641
1642 IF ( (lr_batch_no.org_payment_method_id <> ln_org_pay_method_id) OR
1643 (lr_batch_no.organization_id <> ln_org_id) ) THEN
1644
1645 ln_org_pay_method_id := lr_batch_no.org_payment_method_id;
1646 ln_org_id := lr_batch_no.organization_id;
1647 ln_batch_number := ln_batch_number + 1;
1648
1649 END IF;
1650
1651 IF (lr_batch_no.assignment_action_id = p_assignment_action_id) THEN
1652 EXIT;
1653 END IF;
1654
1655 END LOOP;
1656
1657 IF (lr_batch_no.assignment_action_id = p_assignment_action_id) THEN
1658 lv_return_value := TO_CHAR((p_payroll_action_id * 1000000000) + TO_NUMBER(RPAD(ln_batch_number,6,'0')));
1659 END IF;
1660
1661 CLOSE csr_get_batch_no;
1662
1663 ELSIF UPPER(p_identifier_name) = 'CONCATENATED_IDENTIFIERS' THEN
1664
1665 OPEN csr_get_conc_ident;
1666 FETCH csr_get_conc_ident INTO lr_conc_ident;
1667 CLOSE csr_get_conc_ident;
1668
1669 lt_identifier_table(counter).ident_name := 'File Id';
1670 lt_identifier_table(counter).ident_position := counter;
1671
1672 counter := counter + 1;
1673
1674 lt_identifier_table(counter).ident_name := 'Company Id';
1675 lt_identifier_table(counter).ident_position := counter;
1676
1677 FOR counter IN lt_identifier_table.FIRST..lt_identifier_table.LAST
1678 LOOP
1679 lt_identifier_table(counter).ident_position := INSTR(lr_conc_ident.meaning,
1680 lt_identifier_table(counter).ident_name);
1681 END LOOP;
1682
1683 FOR counter IN 1..lt_identifier_table.COUNT
1684 LOOP
1685 FOR i IN lt_identifier_table.FIRST..(lt_identifier_table.LAST-1)
1686 LOOP
1687 IF lt_identifier_table(i).ident_position > lt_identifier_table(i+1).ident_position THEN
1688 lt_identifier_rec := lt_identifier_table(i);
1689 lt_identifier_table(i) := lt_identifier_table(i+1);
1690 lt_identifier_table(i+1) := lt_identifier_rec;
1691 END IF;
1692
1693 END LOOP;
1694
1695 END LOOP;
1696
1697 FOR counter IN lt_identifier_table.FIRST..lt_identifier_table.LAST
1698 LOOP
1699 IF lt_identifier_table(counter).ident_name = 'File Id' THEN
1700
1701 OPEN csr_get_conc_ident_values;
1702 FETCH csr_get_conc_ident_values INTO lr_conc_ident_values;
1703 CLOSE csr_get_conc_ident_values;
1704
1705 IF lb_add_delimiter THEN
1706 lv_return_value := lv_return_value || p_delimiter_string || UPPER(LPAD(lr_conc_ident_values.pmeth_information3,10,' '));
1707 ELSE
1708 lv_return_value := UPPER(LPAD(lr_conc_ident_values.pmeth_information3,10,' '));
1709 END IF;
1710
1711 lb_add_delimiter := TRUE;
1712
1713 ELSIF lt_identifier_table(counter).ident_name = 'Company Id' THEN
1714
1715 OPEN csr_get_conc_ident_values;
1716 FETCH csr_get_conc_ident_values INTO lr_conc_ident_values;
1717 CLOSE csr_get_conc_ident_values;
1718
1719 IF lb_add_delimiter THEN
1720 lv_return_value := lv_return_value || p_delimiter_string || UPPER(RPAD(lr_conc_ident_values.org_information4,10,' '));
1721 ELSE
1722 lv_return_value := UPPER(RPAD(lr_conc_ident_values.org_information4,10,' '));
1723 END IF;
1724
1725 lb_add_delimiter := TRUE;
1726
1727 END IF;
1728
1729 END LOOP;
1730
1731 END IF;
1732
1733 RETURN lv_return_value;
1734
1735 END f_get_batch_transact_ident;
1736
1737 --BEGIN
1738 -- hr_utility.trace_on(null, 'NACHA');
1739 END pay_us_nacha_tape;