[Home] [Help]
PACKAGE BODY: APPS.PAY_US_NACHA_IAT_TAPE
Source
1 package body pay_us_nacha_iat_tape as
2 /* $Header: pytapnaciat.pkb 120.0.12010000.3 2010/03/31 12:20:37 mikarthi noship $ */
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_iat_tape
21
22 Description : This package holds building blocks used in the generation
23 of nacha IAT Tape.
24
25 Uses : hr_utility
26
27 Change List
28 -----------
29 Date Name Vers Bug No Description
30 ---- ---- ---- ------ ------------------------------
31 AUG-17-2009 MIKARTHI 115.0 Initial Version
32 JAN-20-2010 MIKARTHI 115.1 9275642 Modified cursor c_emp_details.
33 MAR-31-2010 MIKARTHI 115.2 9413224 Modified cursor c_emp_details.
34 New Cursor c_emp_details_override
35
36
37 */
38
39
40 -------------------------- get_addr_delim -------------------------------------
41
42 FUNCTION get_addr_delim (p_addr_detail VARCHAR2)
43 RETURN VARCHAR2 IS
44 BEGIN
45 IF p_addr_detail IS NULL THEN
46 RETURN NULL;
47 ELSE
48 RETURN '*';
49 END IF;
50
51 END;
52
53 -------------------------- run_formula -------------------------------------
54
55 PROCEDURE run_formula (p_business_group_id NUMBER,
56 p_effective_date DATE,
57 p_direct_dep_date VARCHAR2,
58 p_org_payment_method_id NUMBER,
59 p_csr_org_pay_third_party VARCHAR2,
60 p_file_id_modifier VARCHAR2,
61 p_test_file VARCHAR2,
62 p_payroll_id NUMBER) IS
63
64 v_prepayment_id NUMBER := NULL;
65 v_amount NUMBER := NULL;
66 v_block_count NUMBER := NULL;
67 n_person_id NUMBER;
68
69
70
71 --Address sorted on Address type so that if IAT address is present, precedence is given for that.
72 --Bug 9275642. Removed check for Address Type in case Primary Address is being picked.
73 -- Also ordering such that Nulls will be returned first so that if IAT address is present then it is given precedence
74 -- over the Primary address
75 --Bug 9413224. Passing the override date, rather than the end date
76 cursor c_emp_details_override (l_direct_dep_date date )is
77 (SELECT full_name ,
78 paa.organization_id,
79 pap.person_id ,
80 pap.employee_number,
81 pav.ADDRESS_LINE1,
82 pav.TOWN_OR_CITY ,
83 pav.REGION_2 ,
84 pav.REGION_1 ,
85 pav.D_COUNTRY ,
86 pav.POSTAL_CODE
87 FROM per_all_assignments_f paa,
88 per_all_people_f pap,
89 PER_ADDRESSES_V pav
90 WHERE paa.person_id = pap.person_id
91 AND paa.assignment_id = g_assignment_id
92 and l_direct_dep_date BETWEEN paa.EFFECTIVE_START_DATE AND NVL(paa.EFFECTIVE_END_DATE,l_direct_dep_date)
93 and pav.person_id = pap.person_id
94 and l_direct_dep_date BETWEEN pap.EFFECTIVE_START_DATE AND NVL(pap.EFFECTIVE_END_DATE,l_direct_dep_date)
95 AND (pav.ADDRESS_TYPE = 'IAT'
96 or (pav.primary_flag = 'Y' ))
97 and l_direct_dep_date BETWEEN pav.date_from and NVL(pav.date_to,l_direct_dep_date))
98 order by decode(pav.address_type, 'IAT',1,2);
99
100 --Bug 9413224. Fetching Date Paid from the pay_payroll_actions table corresponding
101 -- each pre-payment run, which is then used to fetch date tracked
102 -- address details and employee number changes.
103 cursor c_emp_details (l_prepayment_id number )is
104 (SELECT full_name ,
105 paaf.organization_id,
106 pap.person_id ,
107 pap.employee_number,
108 pav.ADDRESS_LINE1,
109 pav.TOWN_OR_CITY ,
110 pav.REGION_2 ,
111 pav.REGION_1 ,
112 pav.D_COUNTRY ,
113 pav.POSTAL_CODE
114 FROM per_all_assignments_f paaf,
115 per_all_people_f pap,
116 pay_pre_payments ppp,
117 pay_action_interlocks pai,
118 pay_payroll_actions ppa,
119 pay_assignment_actions paa,
120 PER_ADDRESSES_V pav
121 WHERE paaf.person_id = pap.person_id
122 AND paaf.assignment_id = g_assignment_id
123 and ppp.pre_payment_id = l_prepayment_id
124 and ppp.assignment_action_id = pai.locking_action_id
125 and pai.locked_action_id = paa.assignment_action_id
126 and ppa.payroll_action_id = paa.payroll_action_id
127 and ppa.action_type in ('R', 'Q')
128 and ((paa.source_action_id is not null and ppa.run_type_id is not null) or
129 (paa.source_action_id is null and ppa.run_type_id is null))
130 and ppa.effective_date BETWEEN paaf.EFFECTIVE_START_DATE AND NVL(paaf.EFFECTIVE_END_DATE,ppa.effective_date)
131 and pav.person_id = pap.person_id
132 and ppa.effective_date BETWEEN pap.EFFECTIVE_START_DATE AND NVL(pap.EFFECTIVE_END_DATE,ppa.effective_date)
133 AND (pav.ADDRESS_TYPE = 'IAT'
134 or (pav.primary_flag = 'Y'))
135 and ppa.effective_date BETWEEN pav.date_from and NVL(pav.date_to,ppa.effective_date))
136 order by decode(pav.address_type, 'IAT',1,2);
137
138 /* ***************************************************************
139 NAME
140 get_formula_id
141 DESCRIPTION
142 Gets Formula Id
143 NOTES
144 Local function.
145 *********************************************************************/
146
147
148 FUNCTION get_formula_id (p_formula_name VARCHAR2)
149 RETURN VARCHAR2 IS
150 ff_formula_id VARCHAR2(9);
151 BEGIN
152 hr_utility.set_location('pay_us_nacha_tape.get_formula_id', 1);
153 --
154 SELECT TO_CHAR(FORMULA_ID) INTO ff_formula_id
155 FROM FF_FORMULAS_F
156 WHERE p_effective_date BETWEEN EFFECTIVE_START_DATE AND
157 EFFECTIVE_END_DATE
158 AND FORMULA_NAME = p_formula_name;
159 --
160 hr_utility.TRACE('Formula ID : '|| ff_formula_id);
161 RETURN ff_formula_id;
162 EXCEPTION
163 WHEN no_data_found THEN
164 hr_utility.set_message(801, 'FFX37_FORMULA_NOT_FOUND');
165 hr_utility.set_message_token('1', p_formula_name);
166 hr_utility.raise_error;
167 END get_formula_id;
168
169
170 /* ***************************************************************
171 NAME
172 get_transfer_param
173 DESCRIPTION
174 Gets value for the named parameter
175 NOTES
176 Local function.
177 *********************************************************************/
178
179 FUNCTION get_transfer_param (p_param_name VARCHAR2 )
180 RETURN NUMBER IS
181 param_value NUMBER;
182 BEGIN
183 hr_utility.set_location('pay_us_nacha_tape.get_effective_date', 20);
184 IF pay_mag_tape.internal_prm_names(3) = p_param_name
185 THEN
186 param_value := fnd_number.canonical_to_number(pay_mag_tape.internal_prm_values(3));
187 ELSIF pay_mag_tape.internal_prm_names(4) = p_param_name
188 THEN
189 param_value := fnd_number.canonical_to_number(pay_mag_tape.internal_prm_values(4));
190 ELSIF pay_mag_tape.internal_prm_names(5) = p_param_name
191 THEN
192 param_value := fnd_number.canonical_to_number(pay_mag_tape.internal_prm_values(5));
193 ELSIF pay_mag_tape.internal_prm_names(6) = p_param_name
194 THEN
195 param_value := fnd_number.canonical_to_number(pay_mag_tape.internal_prm_values(6));
196 ELSIF pay_mag_tape.internal_prm_names(7) = p_param_name
197 THEN
198 param_value := fnd_number.canonical_to_number(pay_mag_tape.internal_prm_values(7));
199 ELSIF pay_mag_tape.internal_prm_names(8) = p_param_name
200 THEN
201 param_value := fnd_number.canonical_to_number(pay_mag_tape.internal_prm_values(8));
202 END IF;
203 RETURN param_value;
204 END get_transfer_param;
205
206 --==============================
207
208 --Writing File Header
209 PROCEDURE write_file_header IS
210
211 BEGIN
212
213 hr_utility.TRACE('Writing File Header');
214 hr_utility.TRACE('.... Writing File Header Context');
215
216 pay_mag_tape.internal_cxt_values(1) := '3';
217 pay_mag_tape.internal_cxt_names(2) := 'ORG_PAY_METHOD_ID';
218 pay_mag_tape.internal_cxt_values(2) := g_org_payment_method_id;
219 pay_mag_tape.internal_cxt_names(3) := 'DATE_EARNED';
220 pay_mag_tape.internal_cxt_values(3) := fnd_date.date_to_canonical(p_effective_date);
221 --
222 hr_utility.TRACE('.... Writing File Header Parameters');
223 hr_utility.TRACE('g_file_header ' || g_file_header);
224
225 pay_mag_tape.internal_prm_values(1) := '5';
226 pay_mag_tape.internal_prm_values(2) := g_file_header;
227 pay_mag_tape.internal_prm_names(3) := 'FILE_ID_MODIFIER';
228 pay_mag_tape.internal_prm_values(3) := p_file_id_modifier;
229 pay_mag_tape.internal_prm_names(4) := 'CREATION_DATE';
230 pay_mag_tape.internal_prm_values(4) := g_date;
231 pay_mag_tape.internal_prm_names(5) := 'CREATION_TIME';
232 pay_mag_tape.internal_prm_values(5) := g_time;
233
234 hr_utility.TRACE('Leaving File Header');
235
236 hr_utility.set_location('run_formula.File_head', 6);
237
238 END; /* end write_file_header */
239
240 --==================================================
241
242 --Write Batch Header
243 PROCEDURE write_batch_header
244 IS
245
246 BEGIN
247 hr_utility.TRACE('Writing IAT Batch Header');
248
249 g_overflow_batch := 'N';
250 hr_utility.TRACE('....IAT g_overflow_batch is : '|| g_overflow_batch);
251
252 --Two different cursors are used based on the oracle db version for performance improvement
253 IF (nvl(hr_general2.get_oracle_db_version, 0) < 10.0) THEN
254 OPEN csr_assignments (g_legal_company_id,
255 g_payroll_action_id,
256 g_csr_org_pay_meth_id,
257 g_rowid );
258 ELSE
259 OPEN csr_assignments_no_rule (g_legal_company_id,
260 g_payroll_action_id,
261 g_csr_org_pay_meth_id,
262 g_rowid );
263 END IF;
264
265 g_temp_count := 0;
266 g_batch_number := g_batch_number + 1;
267
268 -- Context for NACHA_BATCH_HEADER
269 -- first context is number of contexts
270 hr_utility.TRACE('.... Writing IAT Batch Header Context');
271
272 pay_mag_tape.internal_cxt_values(1) := '4';
273 pay_mag_tape.internal_cxt_names(2) := 'TAX_UNIT_ID';
274 pay_mag_tape.internal_cxt_values(2) := TO_CHAR(g_legal_company_id);
275 pay_mag_tape.internal_cxt_names(3) := 'DATE_EARNED';
276 pay_mag_tape.internal_cxt_values(3) := fnd_date.date_to_canonical(p_effective_date);
277 pay_mag_tape.internal_cxt_names(4) := 'ORG_PAY_METHOD_ID';
278 pay_mag_tape.internal_cxt_values(4) := g_org_payment_method_id;
279
280 -- Parameters for NACHA_BATCH_HEADER
281 -- first parameter is number of parameters
282 -- second parameter is formula is
283 hr_utility.TRACE('.... Writing Batch Header Parameters');
284
285 pay_mag_tape.internal_prm_values(1) := '6';
286 pay_mag_tape.internal_prm_values(2) := g_batch_header;
287
288 pay_mag_tape.internal_prm_names(3) := 'COMPANY_ENTRY_DESCRIPTION';
289 pay_mag_tape.internal_prm_values(3) := g_company_entry_desc;
290
291 pay_mag_tape.internal_prm_names(4) := 'EFFECTIVE_ENTRY_DATE';
292 pay_mag_tape.internal_prm_values(4) := nvl(p_direct_dep_date,
293 TO_CHAR(p_effective_date, 'YYMMDD'));
294 pay_mag_tape.internal_prm_names(5) := 'BATCH_NUMBER';
295 pay_mag_tape.internal_prm_values(5) := TO_CHAR(g_batch_number);
296
297 pay_mag_tape.internal_prm_names(6) := 'FORMAT_TYPE';
298 pay_mag_tape.internal_prm_values(6) := 'IAT';
299
300
301
302 hr_utility.TRACE('Leaving Batch Header');
303
304 END; /* write_batch_header */
305
306 --=========================================
307
308 /******************************************************************
309 NAME
310 write_entry_detail
311 DESCRIPTION
312 Writes the Entry Detail Record .
313 NOTES
314 Local function.
315 ********************************************************************/
316
317
318 PROCEDURE write_entry_detail IS
319
320 BEGIN
321
322 hr_utility.TRACE('Writing Entry Detail');
323
324 hr_utility.TRACE('.... Writing Entry Detail Context');
325 g_count := g_count + 1;
326
327
328 hr_utility.TRACE('Entry Detail : g_hash ' || g_hash);
329 --
330
331 -- Context Setup for NACHA_ENTRY_DETAIL
332 -- First context value is number of contexts
333 pay_mag_tape.internal_cxt_values(1) := '4';
334 pay_mag_tape.internal_cxt_names(2) := 'DATE_EARNED';
335 pay_mag_tape.internal_cxt_values(2) := fnd_date.date_to_canonical(p_effective_date);
336 pay_mag_tape.internal_cxt_names(3) := 'PER_PAY_METHOD_ID';
337 pay_mag_tape.internal_cxt_values(3) := to_char(g_personal_payment_method_id);
338 pay_mag_tape.internal_cxt_names(4) := 'ORG_PAY_METHOD_ID';
339 pay_mag_tape.internal_cxt_values(4) := g_org_payment_method_id;
340
341
342 -- Parameter Setup for NACHA_ENTRY_DETAIL
343 -- First parameter value is number of parameters
344 -- second parameter value is formula id
345
346 hr_utility.TRACE('.... Writing Entry Detail Parameters');
347
348 pay_mag_tape.internal_prm_values(1) := '8';
349 pay_mag_tape.internal_prm_values(2) := g_entry_detail;
350
351 IF g_temp_count = 0 THEN
352 -- If this is the first entry detail of a batch, reset these
353 -- parameters.
354 pay_mag_tape.internal_prm_names(3) := 'TRANSFER_ENTRY_COUNT';
355 pay_mag_tape.internal_prm_values(3) := '0';
356 pay_mag_tape.internal_prm_names(4) := 'TRANSFER_ENTRY_HASH';
357 pay_mag_tape.internal_prm_values(4) := '0';
358 pay_mag_tape.internal_prm_names(5) := 'TRANSFER_CREDIT_AMOUNT';
359 pay_mag_tape.internal_prm_values(5) := '0';
360 /* pay_mag_tape.internal_prm_names(7) := 'TRANSFER_ORG_PAY_TOT';
361 pay_mag_tape.internal_prm_values(7) := '0';*/
362
363
364 g_temp_count := 1;
365 hr_utility.set_location('run_formula.Assignment', 8);
366
367 END IF;
368
369 -- Parameters 3-5 are transferred from previous formula
370 -- 3 - TRANSFER_ENTRY_COUNT
371 -- 4 - TRANSFER_ENTRY_HASH
372 -- 5 - TRANSFER_CREDIT_AMOUNT
373 pay_mag_tape.internal_prm_names(6) := 'TRANSFER_PAY_VALUE';
374 pay_mag_tape.internal_prm_values(6) := fnd_number.number_to_canonical(v_amount);
375
376 pay_mag_tape.internal_prm_names(7) := 'TRACE_SEQUENCE_NUMBER';
377 pay_mag_tape.internal_prm_values(7) := TO_CHAR(g_count);
378
379 pay_mag_tape.internal_prm_names(8) := 'TEST_FILE';
380 pay_mag_tape.internal_prm_values(8) := p_test_file;
381
382 hr_utility.set_location('run_formula.Assignment', 7);
383
384
385
386 g_addenda_write := 'Y';
387
388
389 -- Update PRENOTE Date
390 IF v_amount = 0 THEN
391 UPDATE PAY_EXTERNAL_ACCOUNTS a
392 SET a.PRENOTE_DATE = nvl(to_date(p_direct_dep_date, 'YYMMDD'),
393 p_effective_date)
394 WHERE a.PRENOTE_DATE IS NULL
395 AND a.EXTERNAL_ACCOUNT_ID =
396 (SELECT b.EXTERNAL_ACCOUNT_ID
397 FROM PAY_PERSONAL_PAYMENT_METHODS_F b
398 WHERE b.PERSONAL_PAYMENT_METHOD_ID =
399 g_personal_payment_method_id
400 AND p_effective_date BETWEEN b.EFFECTIVE_START_DATE
401 AND b.EFFECTIVE_END_DATE);
402 END IF;
403
404 hr_utility.TRACE('Entry Detail : TRANSFER_ENTRY_HASH ' || get_transfer_param ('TRANSFER_ENTRY_HASH'));
405
406 hr_utility.TRACE('Leaving Entry Detail');
407
408 END; /* write_entry_detail */
409
410 /******************************************************************
411 NAME
412 write_org_entry_detail
413 DESCRIPTION
414 Writes the Org Entry Detail Record .
415 NOTES
416 Local function.
417 ********************************************************************/
418
419 PROCEDURE write_org_entry_detail IS
420
421 BEGIN
422
423 hr_utility.TRACE('Writing Org Entry Detail');
424
425 IF g_nacha_balance_flag = 'Y' THEN
426 g_count := g_count + 1;
427 g_addenda_write := 'Y';
428 g_org_addenda := 'Y';
429 END IF;
430
431 g_batch_control_write := 'Y';
432
433 IF g_overflow_flag = 'Y' THEN
434 g_overflow_flag := 'N';
435 g_overflow_batch := 'Y';
436 END IF;
437
438 -- Context Setup for NACHA_ORG_PAY_ENTRY_DETAIL
439 -- first context is number of context values
440 hr_utility.TRACE('.... Writing Org Entry Detail Context');
441
442 pay_mag_tape.internal_cxt_values(1) := '3';
443 pay_mag_tape.internal_cxt_names(2) := 'ORG_PAY_METHOD_ID';
444 pay_mag_tape.internal_cxt_values(2) := g_csr_org_pay_meth_id;
445 pay_mag_tape.internal_cxt_names(3) := 'DATE_EARNED';
446 pay_mag_tape.internal_cxt_values(3) := fnd_date.date_to_canonical(p_effective_date);
447
448 -- Parameter Setup for NACHA_ORG_PAY_ENTRY_DETAIL
449 -- first parameter is number of parameters
450 -- second parameter is formula id
451 hr_utility.TRACE('.... Writing Org Entry Detail Parameters');
452
453 pay_mag_tape.internal_prm_values(1) := '8';
454 pay_mag_tape.internal_prm_values(2) := g_org_pay_entry_detail;
455
456 -- Parameters 3-6 are transferred from previous formula
457 -- 3 - TRANSFER_ENTRY_COUNT
458 -- 4 - TRANSFER_ENTRY_HASH
459 -- 5 - TRANSFER_CREDIT_AMOUNT
460
461 pay_mag_tape.internal_prm_names(6) := 'TRANSFER_PAY_VALUE';
462 pay_mag_tape.internal_prm_values(6) := get_transfer_param ('TRANSFER_CREDIT_AMOUNT');
463
464 pay_mag_tape.internal_prm_names(7) := 'TRACE_SEQUENCE_NUMBER';
465 pay_mag_tape.internal_prm_values(7) := TO_CHAR(g_count);
466
467 pay_mag_tape.internal_prm_names(8) := 'TEST_FILE';
468 pay_mag_tape.internal_prm_values(8) := p_test_file;
469
470
471 --Closing the cursor which was opened based on db version
472 IF (nvl(hr_general2.get_oracle_db_version, 0) < 10.0) THEN
473 CLOSE csr_assignments;
474 ELSE
475 CLOSE csr_assignments_no_rule;
476 END IF;
477 hr_utility.set_location('run_formula.org_pay_entry_detail', 9);
478
479 hr_utility.TRACE('Leaving Org Entry Detail');
480
481 END; /* write_org_entry_detail */
482
483
484 /******************************************************************
485 NAME
486 write_addenda
487 DESCRIPTION
488 Writes the Addenda Record .
489 NOTES
490 Local function.
491 ********************************************************************/
492
493
494 PROCEDURE write_addenda IS
495
496 BEGIN
497
498 hr_utility.TRACE('Writing IAT Addenda');
499
500 g_addenda_num := g_addenda_num - 1;
501
502 IF g_addenda_num = 0 THEN
503 g_addenda_write := 'N';
504 END IF;
505
506 hr_utility.TRACE('g_addenda_num ' || g_addenda_num);
507
508 IF g_addenda_num = 6 THEN
509
510 hr_utility.TRACE('First Addenda');
511
512 pay_mag_tape.internal_cxt_values(1) := '3';
513
514 pay_mag_tape.internal_cxt_names(2) := 'DATE_EARNED';
515 pay_mag_tape.internal_cxt_values(2) := fnd_date.date_to_canonical(p_effective_date);
516
517 pay_mag_tape.internal_cxt_names(3) := 'PAYROLL_ID';
518 pay_mag_tape.internal_cxt_values(3) := g_payroll_id;
519
520 g_addenda := get_formula_id('NACHA_IAT_ADDENDA1');
521 pay_mag_tape.internal_prm_values(1) := '11';
522 pay_mag_tape.internal_prm_values(2) := g_addenda;
523
524 -- Parameters 3-6 are transferred from previous formula
525 -- 3 - TRANSFER_ENTRY_COUNT
526 -- 4 - TRANSFER_ENTRY_HASH
527 -- 5 - TRANSFER_CREDIT_AMOUNT
528
529 pay_mag_tape.internal_prm_names(7) := 'ADDENDA_NUMBER';
530 pay_mag_tape.internal_prm_values(7) := to_char(7 - g_addenda_num);
531
532 pay_mag_tape.internal_prm_names(8) := 'TRACE_SEQUENCE_NUMBER';
533 pay_mag_tape.internal_prm_values(8) := to_char(g_count);
534
535 pay_mag_tape.internal_prm_names(9) := 'TEST_FILE';
536 pay_mag_tape.internal_prm_values(9) := p_test_file;
537
538 pay_mag_tape.internal_prm_names(10) := 'FULL_NAME';
539 IF g_org_addenda = 'N' THEN
540 pay_mag_tape.internal_prm_values(10) := g_full_name;
541 ELSE
542 pay_mag_tape.internal_prm_values(10) := g_org_name;
543 END IF;
544
545 pay_mag_tape.internal_prm_names(11) := 'ORG_ADDENDA';
546 pay_mag_tape.internal_prm_values(11) := g_org_addenda;
547
548 ELSIF g_addenda_num = 5 THEN
549
550 hr_utility.TRACE('Second Addenda');
551
552 g_addenda := get_formula_id('NACHA_IAT_ADDENDA2');
553 pay_mag_tape.internal_prm_values(1) := '9';
554 pay_mag_tape.internal_prm_values(2) := g_addenda;
555
556
557 -- Parameters 3-6 are transferred from previous formula
558 -- 3 - TRANSFER_ENTRY_COUNT
559 -- 4 - TRANSFER_ENTRY_HASH
560 -- 5 - TRANSFER_CREDIT_AMOUNT
561
562 pay_mag_tape.internal_prm_names(6) := 'ADDENDA_NUMBER';
563 pay_mag_tape.internal_prm_values(6) := to_char(7 - g_addenda_num);
564 pay_mag_tape.internal_prm_names(7) := 'TRACE_SEQUENCE_NUMBER';
565 pay_mag_tape.internal_prm_values(7) := to_char(g_count);
566 pay_mag_tape.internal_prm_names(8) := 'ORG_NAME';
567 pay_mag_tape.internal_prm_values(8) := g_org_name;
568 pay_mag_tape.internal_prm_names(9) := 'ORG_STREET';
569 pay_mag_tape.internal_prm_values(9) := g_street_address;
570
571 ELSIF g_addenda_num = 4 THEN
572
573 hr_utility.TRACE('Third Addenda');
574
575 g_addenda := get_formula_id('NACHA_IAT_ADDENDA3');
576 pay_mag_tape.internal_prm_values(1) := '9';
577 pay_mag_tape.internal_prm_values(2) := g_addenda;
578
579 -- Parameters 3-6 are transferred from previous formula
580 -- 3 - TRANSFER_ENTRY_COUNT
581 -- 4 - TRANSFER_ENTRY_HASH
582 -- 5 - TRANSFER_CREDIT_AMOUNT
583 -- 6 - TRANSFER_PAY_VALUE
584 -- 7 - TRANSFER_ORG_PAY_TOT
585
586 pay_mag_tape.internal_prm_names(6) := 'ADDENDA_NUMBER';
587 pay_mag_tape.internal_prm_values(6) := to_char(7 - g_addenda_num);
588
589 pay_mag_tape.internal_prm_names(7) := 'TRACE_SEQUENCE_NUMBER';
590 pay_mag_tape.internal_prm_values(7) := to_char(g_count);
591
592 pay_mag_tape.internal_prm_names(8) := 'ORG_CITY_STATE';
593 pay_mag_tape.internal_prm_values(8) := g_city || get_addr_delim(g_state) || g_state || '\';
594
595 pay_mag_tape.internal_prm_names(9) := 'ORG_COUNTRY_POSTAL';
596 pay_mag_tape.internal_prm_values(9) := g_country || get_addr_delim(g_postal_code) || g_postal_code || '\';
597
598 ELSIF g_addenda_num = 3 THEN
599
600 hr_utility.TRACE('Fourth Addenda');
601
602 pay_mag_tape.internal_cxt_values(1) := '3';
603
604 pay_mag_tape.internal_cxt_names(2) := 'DATE_EARNED';
605 pay_mag_tape.internal_cxt_values(2) := fnd_date.date_to_canonical(p_effective_date);
606
607 pay_mag_tape.internal_cxt_names(3) := 'ORG_PAY_METHOD_ID';
608 pay_mag_tape.internal_cxt_values(3) := g_org_payment_method_id;
609
610 hr_utility.TRACE('Fourth Addenda');
611 g_addenda := get_formula_id('NACHA_IAT_ADDENDA4');
612 pay_mag_tape.internal_prm_values(1) := '7';
613 pay_mag_tape.internal_prm_values(2) := g_addenda;
614
615 -- Parameters 3-6 are transferred from previous formula
616 -- 3 - TRANSFER_ENTRY_COUNT
617 -- 4 - TRANSFER_ENTRY_HASH
618 -- 5 - TRANSFER_CREDIT_AMOUNT
619
620
621 pay_mag_tape.internal_prm_names(6) := 'ADDENDA_NUMBER';
622 pay_mag_tape.internal_prm_values(6) := to_char(7 - g_addenda_num);
623
624 pay_mag_tape.internal_prm_names(7) := 'TRACE_SEQUENCE_NUMBER';
625 pay_mag_tape.internal_prm_values(7) := to_char(g_count);
626
627 ELSIF g_addenda_num = 2 THEN
628
629 hr_utility.TRACE('Fifth Addenda');
630
631 g_addenda := get_formula_id('NACHA_IAT_ADDENDA5');
632
633 pay_mag_tape.internal_cxt_values(1) := '4';
634
635 pay_mag_tape.internal_cxt_names(2) := 'DATE_EARNED';
636 pay_mag_tape.internal_cxt_values(2) := fnd_date.date_to_canonical(p_effective_date);
637
638 pay_mag_tape.internal_cxt_names(3) := 'PER_PAY_METHOD_ID';
639 pay_mag_tape.internal_cxt_values(3) := to_char(g_personal_payment_method_id);
640
641 pay_mag_tape.internal_cxt_names(4) := 'ORG_PAY_METHOD_ID';
642 pay_mag_tape.internal_cxt_values(4) := g_org_payment_method_id;
643
644 pay_mag_tape.internal_prm_values(1) := '8';
645 pay_mag_tape.internal_prm_values(2) := g_addenda;
646
647 -- Parameters 3-6 are transferred from previous formula
648 -- 3 - TRANSFER_ENTRY_COUNT
649 -- 4 - TRANSFER_ENTRY_HASH
650 -- 5 - TRANSFER_CREDIT_AMOUNT
651
652
653 pay_mag_tape.internal_prm_names(6) := 'ADDENDA_NUMBER';
654 pay_mag_tape.internal_prm_values(6) := to_char(7 - g_addenda_num);
655
656 pay_mag_tape.internal_prm_names(7) := 'TRACE_SEQUENCE_NUMBER';
657 pay_mag_tape.internal_prm_values(7) := to_char(g_count);
658
659 pay_mag_tape.internal_prm_names(8) := 'ORG_ADDENDA';
660 pay_mag_tape.internal_prm_values(8) := g_org_addenda;
661
662
663 ELSIF g_addenda_num = 1 THEN
664
665 hr_utility.TRACE('Sixth Addenda');
666
667 pay_mag_tape.internal_cxt_values(1) := '3';
668
669 pay_mag_tape.internal_cxt_names(2) := 'DATE_EARNED';
670 pay_mag_tape.internal_cxt_values(2) := fnd_date.date_to_canonical(p_effective_date);
671
672 pay_mag_tape.internal_cxt_names(3) := 'ORG_PAY_METHOD_ID';
673 pay_mag_tape.internal_cxt_values(3) := g_org_payment_method_id;
674
675 g_addenda := get_formula_id('NACHA_IAT_ADDENDA6');
676 pay_mag_tape.internal_prm_values(1) := '10';
677 pay_mag_tape.internal_prm_values(2) := g_addenda;
678
679 -- Parameters 3-6 are transferred from previous formula
680 -- 3 - TRANSFER_ENTRY_COUNT
681 -- 4 - TRANSFER_ENTRY_HASH
682 -- 5 - TRANSFER_CREDIT_AMOUNT
683
684 pay_mag_tape.internal_prm_names(6) := 'ADDENDA_NUMBER';
685 pay_mag_tape.internal_prm_values(6) := to_char(7 - g_addenda_num);
686
687 pay_mag_tape.internal_prm_names(7) := 'TRACE_SEQUENCE_NUMBER';
688 pay_mag_tape.internal_prm_values(7) := to_char(g_count);
689
690 pay_mag_tape.internal_prm_names(8) := 'EMPLOYEE_NUMBER';
691 pay_mag_tape.internal_prm_values(8) := to_char(g_emp_num);
692
693 pay_mag_tape.internal_prm_names(9) := 'EMPLOYEE_ADDR';
694 pay_mag_tape.internal_prm_values(9) := to_char(g_emp_adress);
695
696 pay_mag_tape.internal_prm_names(10) := 'ORG_ADDENDA';
697 pay_mag_tape.internal_prm_values(10) := g_org_addenda;
698
699 ELSIF g_addenda_num = 0 THEN
700
701 hr_utility.TRACE('Seventh Addenda');
702
703 pay_mag_tape.internal_cxt_values(1) := '3';
704
705 pay_mag_tape.internal_cxt_names(2) := 'DATE_EARNED';
706 pay_mag_tape.internal_cxt_values(2) := fnd_date.date_to_canonical(p_effective_date);
707
708 pay_mag_tape.internal_cxt_names(3) := 'ORG_PAY_METHOD_ID';
709 pay_mag_tape.internal_cxt_values(3) := g_org_payment_method_id;
710
711 g_addenda := get_formula_id('NACHA_IAT_ADDENDA7');
712 pay_mag_tape.internal_prm_values(1) := '10';
713 pay_mag_tape.internal_prm_values(2) := g_addenda;
714
715 -- Parameters 3-6 are transferred from previous formula
716 -- 3 - TRANSFER_ENTRY_COUNT
717 -- 4 - TRANSFER_ENTRY_HASH
718 -- 5 - TRANSFER_CREDIT_AMOUNT
719
720 pay_mag_tape.internal_prm_names(6) := 'ADDENDA_NUMBER';
721 pay_mag_tape.internal_prm_values(6) := to_char(7 - g_addenda_num);
722
723 pay_mag_tape.internal_prm_names(7) := 'TRACE_SEQUENCE_NUMBER';
724 pay_mag_tape.internal_prm_values(7) := to_char(g_count);
725
726 pay_mag_tape.internal_prm_names(8) := 'EMP_CITY_STATE';
727 pay_mag_tape.internal_prm_values(8) := g_emp_city || get_addr_delim(g_emp_state )|| g_emp_state || '\';
728
729 pay_mag_tape.internal_prm_names(9) := 'EMP_COUNTRY_POSTAL';
730 pay_mag_tape.internal_prm_values(9) := g_emp_country || get_addr_delim(g_emp_postal) || g_emp_postal || '\';
731
732 pay_mag_tape.internal_prm_names(10) := 'ORG_ADDENDA';
733 pay_mag_tape.internal_prm_values(10) := g_org_addenda;
734
735 g_org_addenda := 'N';
736
737 ELSE
738
739 hr_utility.TRACE('No Addenda Records to Write');
740
741 END IF;
742
743
744 -- we do not change the count till after so we can have the same trace number
745 -- in both entry detail and addenda rec
746 g_addenda_count := g_addenda_count + 1;
747
748 hr_utility.TRACE('Leaving Addenda');
749
750 END; /* write_addenda */
751
752
753 /******************************************************************
754 NAME
755 write_batch_control
756 DESCRIPTION
757 Writes the Batch Control Record .
758 NOTES
759 Local function.
760 ********************************************************************/
761
762
763 PROCEDURE write_batch_control IS
764
765 BEGIN
766 hr_utility.TRACE('Writing IAT Batch Control');
767
768 g_batch_control_write := 'N';
769
770 g_hash := g_hash + get_transfer_param ('TRANSFER_ENTRY_HASH');
771
772 hr_utility.TRACE('Batch Control : g_hash ' || g_hash);
773 hr_utility.TRACE('Batch Control : TRANSFER_ENTRY_HASH ' || get_transfer_param ('TRANSFER_ENTRY_HASH'));
774
775 g_amount := g_amount + get_transfer_param ('TRANSFER_CREDIT_AMOUNT');
776
777 -- Context Setup for NACHA_BATCH_CONTROL
778 -- First context value is number of context values
779
780 hr_utility.TRACE('.... Writing Batch Control Context');
781
782 pay_mag_tape.internal_cxt_values(1) := '4';
783 pay_mag_tape.internal_cxt_names(2) := 'TAX_UNIT_ID';
784 pay_mag_tape.internal_cxt_values(2) := TO_CHAR(g_legal_company_id);
785 pay_mag_tape.internal_cxt_names(3) := 'DATE_EARNED';
786 pay_mag_tape.internal_cxt_values(3) := fnd_date.date_to_canonical(p_effective_date);
787 pay_mag_tape.internal_cxt_names(4) := 'ORG_PAY_METHOD_ID';
788 pay_mag_tape.internal_cxt_values(4) := g_org_payment_method_id;
789
790 -- Parameter Setup for NACHA_BATCH_CONTROL
791 -- First parameter value is number of parameters
792
793 hr_utility.TRACE('.... Writing Batch Control Parameters');
794
795 pay_mag_tape.internal_prm_values(1) := '6';
796 pay_mag_tape.internal_prm_values(2) := g_batch_control;
797
798 -- Parameters 4-7 are transferred from previous formula
799 -- 3 - TRANSFER_ENTRY_COUNT
800 -- 4 - TRANSFER_ENTRY_HASH
801 -- 5 - TRANSFER_CREDIT_AMOUNT
802
803
804 pay_mag_tape.internal_prm_names(6) := 'BATCH_NUMBER';
805 pay_mag_tape.internal_prm_values(6) := TO_CHAR(g_batch_number);
806
807 hr_utility.set_location('run_formula.Batch_ctrl', 9);
808
809 hr_utility.TRACE('Leaving Batch Control');
810
811 END; /* write_batch_control */
812
813 /******************************************************************
814 NAME
815 write_file_control
816 DESCRIPTION
817 Writes the File Control Record .
818 NOTES
819 Local function.
820 ********************************************************************/
821
822 PROCEDURE write_file_control IS
823
824 BEGIN
825
826 hr_utility.TRACE('Writing File Control');
827
828 /*
829 hr_utility.TRACE('g_batch_number: ' || g_batch_number);
830 hr_utility.TRACE('g_count: ' || g_count);
831 hr_utility.TRACE('g_addenda_count: ' || g_addenda_count);
832
833 hr_utility.TRACE('File Control : g_hash ' || g_hash);
834 */
835
836 v_block_count := CEIL(((2 * g_batch_number ) +
837 g_count + g_addenda_count + 2) / 10);
838 g_pad_count := (v_block_count * 10) -
839 ((2 * g_batch_number ) +
840 g_count + g_addenda_count + 2);
841
842 hr_utility.TRACE('.... Writing File Control Context');
843
844 -- dscully - added contexts for NACHA_BALANCED_NACHA_FILE DBI
845 pay_mag_tape.internal_cxt_values(1) := '3';
846 pay_mag_tape.internal_cxt_names(2) := 'ORG_PAY_METHOD_ID';
847 pay_mag_tape.internal_cxt_values(2) := g_org_payment_method_id;
848 pay_mag_tape.internal_cxt_names(3) := 'DATE_EARNED';
849 pay_mag_tape.internal_cxt_values(3) := fnd_date.date_to_canonical(p_effective_date);
850
851 hr_utility.TRACE('.... Writing File Control Parameters');
852
853 pay_mag_tape.internal_prm_values(1) := '8';
854 pay_mag_tape.internal_prm_values(2) := g_file_control;
855
856 pay_mag_tape.internal_prm_names(3) := 'BATCH_NUMBER';
857 pay_mag_tape.internal_prm_values(3) := TO_CHAR(g_batch_number);
858
859 pay_mag_tape.internal_prm_names(4) := 'BLOCK_COUNT';
860 pay_mag_tape.internal_prm_values(4) := TO_CHAR(v_block_count);
861
862 pay_mag_tape.internal_prm_names(5) := 'FILE_ENTRY_COUNT';
863 pay_mag_tape.internal_prm_values(5) := TO_CHAR(g_count + g_addenda_count);
864
865 pay_mag_tape.internal_prm_names(6) := 'FILE_ENTRY_HASH';
866 pay_mag_tape.internal_prm_values(6) := TO_CHAR(g_hash);
867
868 pay_mag_tape.internal_prm_names(7) := 'FILE_CREDIT_AMOUNT';
869 pay_mag_tape.internal_prm_values(7) := fnd_number.number_to_canonical(g_amount);
870
871 pay_mag_tape.internal_prm_names(8) := 'TRANSFER_PAD_COUNT';
872 pay_mag_tape.internal_prm_values(8) := TO_CHAR(g_pad_count);
873 --
874 hr_utility.set_location('run_formula.File_Control', 11);
875 hr_utility.TRACE('Leaving File Control');
876
877 END; /* write_file_control */
878
879 /******************************************************************
880 NAME
881 write_padding
882 DESCRIPTION
883 Writes the Padding Record .
884 NOTES
885 Local function.
886 ********************************************************************/
887 PROCEDURE write_padding IS
888
889 BEGIN
890
891 hr_utility.TRACE('Writing Padding');
892
893 pay_mag_tape.internal_cxt_values(1) := '1';
894
895 hr_utility.TRACE('Writing Padding for IAT');
896 pay_mag_tape.internal_prm_values(1) := '3';
897 pay_mag_tape.internal_prm_values(2) := g_padding;
898 pay_mag_tape.internal_prm_names(3) := 'TRANSFER_PAD_COUNT';
899 pay_mag_tape.internal_prm_values(3) := TO_CHAR(g_pad_count);
900
901 IF g_pad_count = 1 THEN
902 CLOSE csr_nacha_batch;
903 ELSE
904 g_pad_count := g_pad_count - 1;
905 END IF;
906
907
908 hr_utility.TRACE('Leaving IAT Padding');
909
910 END; /* write_padding */
911
912 /*****************************END of Local Functions ****************/
913
914
915 BEGIN
916
917 hr_utility.TRACE('Entering pay_us_nacha_iat_tape.run_formula');
918 pay_mag_tape.internal_prm_names(1) := 'NO_OF_PARAMETERS';
919 pay_mag_tape.internal_prm_names(2) := 'NEW_FORMULA_ID';
920 pay_mag_tape.internal_prm_values(1) := '2';
921
922 pay_mag_tape.internal_cxt_names(1) := 'NUMBER_OF_CONTEXT';
923 pay_mag_tape.internal_cxt_values(1) := '1';
924 hr_utility.set_location('pay_us_nacha_iat_tape.run_formula', 1);
925
926
927 IF NOT csr_nacha_batch%ISOPEN AND g_first_exec = 'Y' THEN
928
929 g_first_exec := 'N';
930
931 hr_utility.set_location('run_formula.Init', 5);
932 g_payroll_action_id := fnd_number.canonical_to_number(
933 pay_mag_tape.internal_prm_values(3));
934
935 hr_utility.TRACE('g_payroll_action_id : ' || g_payroll_action_id);
936
937
938 g_org_payment_method_id := p_org_payment_method_id;
939
940 g_payroll_id := p_payroll_id;
941
942 g_company_entry_desc := 'SALARY';
943 g_descriptive_date := g_date;
944
945
946 -- Intialize global varibles
947 g_temp_count := 0; /* Flag to initialize batch running totals */
948 g_pad_count := - 1; /* Number of times the padding formula called */
949
950
951 --Fetching the Formula ID for all the Formulae
952 g_file_header := get_formula_id('NACHA_IAT_FILE_HEADER');
953 g_batch_header := get_formula_id('NACHA_IAT_BATCH_HEADER');
954 g_entry_detail := get_formula_id('NACHA_IAT_ENTRY_DETAIL');
955 g_org_pay_entry_detail := get_formula_id('NACHA_IAT_ORG_PAY_ENTRY_DETAIL');
956 g_batch_control := get_formula_id('NACHA_IAT_BATCH_CONTROL');
957 g_file_control := get_formula_id('NACHA_IAT_FILE_CONTROL');
958 g_padding := get_formula_id('NACHA_IAT_PADDING');
959
960 IF g_org_payment_method_id IS NULL THEN
961 OPEN csr_org_flex_info (p_business_group_id,
962 g_payroll_action_id,
963 p_effective_date);
964 FETCH csr_org_flex_info INTO g_org_payment_method_id, g_payroll_id;
965 CLOSE csr_org_flex_info;
966
967 END IF;
968
969 hr_utility.TRACE('g_payroll_id : ' || g_payroll_id);
970 hr_utility.TRACE('g_organization_id : ' || g_organization_id);
971
972 IF g_org_payment_method_id IS NOT NULL THEN
973 hr_utility.TRACE('g_org_payment_method_id = ' || g_org_payment_method_id);
974 write_file_header;
975 ELSE
976 hr_utility.set_message(801, 'HR_7711_SCL_FLEX_NOT_FOUND');
977 hr_utility.raise_error;
978 END IF;
979
980 hr_utility.TRACE('p_business_group_id = ' || p_business_group_id);
981 hr_utility.TRACE('g_payroll_action_id <' || g_payroll_action_id);
982 hr_utility.TRACE('p_effective_date <' || p_effective_date);
983
984 OPEN csr_nacha_batch(p_business_group_id, g_payroll_action_id, p_effective_date);
985
986
987 /****************Level 1.2 The second major else if clause ***************/
988 ELSE /* main */
989
990 IF g_addenda_write = 'Y' THEN
991
992 write_addenda;
993
994 ELSIF g_batch_control_write = 'Y' THEN
995
996 write_batch_control;
997
998 ELSIF (csr_assignments%ISOPEN OR csr_assignments_no_rule%ISOPEN) THEN
999 IF (nvl(hr_general2.get_oracle_db_version, 0) < 10.0) THEN
1000
1001 FETCH csr_assignments INTO g_assignment_id, g_assignment_action_id,
1002 v_amount, g_personal_payment_method_id,
1003 v_prepayment_id, g_rowid;
1004
1005 IF csr_assignments%FOUND THEN
1006
1007 IF v_amount > 99999999.99 THEN
1008 hr_utility.set_message(801, 'PAY_US_PAYMENT_OVERFLOW');
1009 pay_core_utils.push_message(801, 'PAY_US_PAYMENT_OVERFLOW', 'P');
1010 pay_core_utils.push_token('ASSIGNMENT_NO', g_assignment_id);
1011 raise_application_error( - 20101, 'Error in pay_us_nacha_tape.run_formula');
1012
1013 END IF ;
1014
1015 --Bug 9413224. Use c_emp_details_override if Override date is provided
1016 -- Else use c_emp_details
1017 --OPEN c_emp_details(p_effective_date);
1018 if p_direct_dep_date is not null then
1019 hr_utility.TRACE('p_direct_dep_date is not null ' || p_direct_dep_date);
1020
1021 OPEN c_emp_details_override(to_date(p_direct_dep_date,'YYMMDD'));
1022 FETCH c_emp_details_override INTO g_full_name, g_organization_id, n_person_id, g_emp_num,
1023 g_emp_adress, g_emp_city, g_emp_state, g_emp_county, g_emp_country, g_emp_postal;
1024 CLOSE c_emp_details_override;
1025
1026 else
1027 hr_utility.TRACE('p_direct_dep_date is null ');
1028 OPEN c_emp_details(v_prepayment_id);
1029 FETCH c_emp_details INTO g_full_name, g_organization_id, n_person_id, g_emp_num,
1030 g_emp_adress, g_emp_city, g_emp_state, g_emp_county, g_emp_country, g_emp_postal;
1031 CLOSE c_emp_details;
1032
1033 hr_utility.TRACE('g_emp_num : ' || g_emp_num);
1034
1035 end if;
1036
1037 --No need of this check as any employee for which payroll has been run will atleast have the primary address
1038 /*IF c_emp_details%NOTFOUND THEN
1039 CLOSE c_emp_details;
1040 hr_utility.set_message(801, 'PAY_US_IAT_NO_ADDRESS');
1041 hr_utility.set_message_token('ASSIGN_NUM', g_assignment_id);
1042 hr_utility.raise_error;
1043 END IF;*/
1044
1045
1046
1047 hr_utility.TRACE('full_name : ' || g_full_name);
1048
1049 g_overflow_amount := g_overflow_amount + v_amount;
1050
1051 IF g_overflow_amount > 99999999.99 THEN
1052 g_overflow_amount := 0;
1053 g_overflow_flag := 'Y';
1054 g_addenda_num := 7;
1055 write_org_entry_detail;
1056
1057 ELSE
1058 g_addenda_num := 7;
1059 write_entry_detail;
1060 END IF;
1061
1062 ELSE /* setup context and params for NACHA_ORG_PAY_ENTRY_DETAIL */
1063 g_addenda_num := 7;
1064 write_org_entry_detail;
1065 END IF;
1066 ELSE
1067
1068 FETCH csr_assignments_no_rule INTO g_assignment_id, g_assignment_action_id,
1069 v_amount, g_personal_payment_method_id,
1070 v_prepayment_id, g_rowid;
1071
1072 IF csr_assignments_no_rule%FOUND THEN
1073
1074 g_overflow_amount := g_overflow_amount + v_amount;
1075
1076 IF v_amount > 99999999.99 THEN
1077 hr_utility.set_message(801, 'PAY_US_PAYMENT_OVERFLOW');
1078 pay_core_utils.push_message(801, 'PAY_US_PAYMENT_OVERFLOW', 'P');
1079 pay_core_utils.push_token('ASSIGNMENT_NO', g_assignment_id);
1080 raise_application_error( - 20101, 'Error in pay_us_nacha_tape.run_formula');
1081 END IF;
1082
1083
1084 --Bug 9413224. Use c_emp_details_override if Override date is provided
1085 -- Else use c_emp_details
1086 -- OPEN c_emp_details(p_effective_date);
1087 if p_direct_dep_date is not null then
1088 hr_utility.TRACE('p_direct_dep_date is not null ' || p_direct_dep_date);
1089
1090 OPEN c_emp_details_override(to_date(p_direct_dep_date,'YYMMDD'));
1091 FETCH c_emp_details_override INTO g_full_name, g_organization_id, n_person_id, g_emp_num,
1092 g_emp_adress, g_emp_city, g_emp_state, g_emp_county, g_emp_country, g_emp_postal;
1093 CLOSE c_emp_details_override;
1094
1095 hr_utility.TRACE('g_emp_num : ' || g_emp_num);
1096
1097 else
1098 hr_utility.TRACE('p_direct_dep_date is null ');
1099 OPEN c_emp_details(v_prepayment_id);
1100 FETCH c_emp_details INTO g_full_name, g_organization_id, n_person_id, g_emp_num,
1101 g_emp_adress, g_emp_city, g_emp_state, g_emp_county, g_emp_country, g_emp_postal;
1102 CLOSE c_emp_details;
1103
1104 hr_utility.TRACE('g_emp_num : ' || g_emp_num);
1105
1106 end if;
1107
1108
1109 --No need of this check as, any employee for which payroll has been run will atleast have the primary address
1110 /*IF c_emp_details%NOTFOUND THEN
1111 CLOSE c_emp_details;
1112 hr_utility.set_message(801, 'PAY_US_IAT_NO_ADDRESS');
1113 hr_utility.set_message_token('ASSIGN_NUM', g_assignment_id);
1114 hr_utility.raise_error;
1115 END IF;*/
1116 IF g_overflow_amount > 99999999.99 THEN
1117 g_overflow_amount := 0;
1118 g_overflow_flag := 'Y';
1119 g_addenda_num := 7;
1120 write_org_entry_detail;
1121 ELSE
1122 g_addenda_num := 7;
1123 write_entry_detail;
1124 END IF;
1125
1126 ELSE /* setup context and params for NACHA_ORG_PAY_ENTRY_DETAIL */
1127 g_addenda_num := 7;
1128 write_org_entry_detail;
1129
1130 END IF;
1131 END IF;
1132 ELSE
1133
1134 hr_utility.TRACE('Before Batch cursor');
1135
1136 FETCH csr_nacha_batch INTO g_csr_org_pay_meth_id,
1137 g_legal_company_id,
1138 g_nacha_balance_flag;
1139
1140 hr_utility.TRACE('after fetch Batch cursor');
1141
1142 IF csr_nacha_batch %FOUND THEN
1143 /* to reset rowid when GRE changes. Bug 1967949 */
1144 hr_utility.TRACE('b4 g_legal_company_id is : ' || g_legal_company_id);
1145 hr_utility.TRACE('b4 g_reset_greid is : ' || g_reset_greid);
1146 hr_utility.TRACE('b4 g_rowid is : ' || g_rowid);
1147
1148 IF g_reset_greid <> g_legal_company_id THEN
1149 g_rowid := NULL;
1150 g_reset_greid := g_legal_company_id;
1151 END IF;
1152
1153 --Fetching the Address of GRE
1154
1155 SELECT hou.name ,
1156 hl.ADDRESS_LINE_1,
1157 hl.TOWN_OR_CITY ,
1158 hl.REGION_2 ,
1159 hl.REGION_1 ,
1160 hl.COUNTRY ,
1161 hl.POSTAL_CODE
1162 INTO g_org_name ,
1163 g_street_address,
1164 g_city ,
1165 g_state ,
1166 g_county ,
1167 g_country ,
1168 g_postal_code
1169 FROM hr_organization_units hou,
1170 hr_locations hl
1171 WHERE hou.location_id = hl.location_id
1172 AND hou.organization_id = g_legal_company_id
1173 and p_effective_date between hou.date_from and nvl(hou.date_to,p_effective_date);
1174
1175 /*hr_utility.TRACE('p_business_group_id : ' || p_business_group_id);
1176 hr_utility.TRACE('g_legal_company_id : ' || g_legal_company_id);
1177 hr_utility.TRACE('g_organization_id : ' || g_organization_id);
1178 hr_utility.TRACE('g_org_name : ' || g_org_name);
1179 hr_utility.TRACE('g_street_address : ' || g_street_address);
1180 hr_utility.TRACE('g_city : ' || g_city);
1181 hr_utility.TRACE('g_state : ' || g_state);
1182 hr_utility.TRACE('g_county : ' || g_county);
1183 hr_utility.TRACE('g_country : ' || g_country);
1184 hr_utility.TRACE('g_postal_code : ' || g_postal_code);*/
1185
1186 write_batch_header;
1187
1188 ELSE
1189
1190 hr_utility.TRACE('g_pad_count ' || g_pad_count);
1191 IF g_pad_count = - 1 THEN
1192 write_file_control;
1193 ELSIF g_pad_count > 0 THEN
1194 write_padding;
1195 END IF;
1196 END IF;
1197 END IF; /* g_addenda_write = 'Y' */
1198 END IF; /* main */
1199
1200 END run_formula;
1201
1202 END pay_us_nacha_iat_tape;