[Home] [Help]
PACKAGE BODY: APPS.PAY_GB_BACS_TAPE
Source
1 PACKAGE BODY pay_gb_bacs_tape AS
2 -- $Header: pytapbac.pkb 120.0.12010000.3 2009/07/17 14:46:33 namgoyal ship $
3 --
4 -- ***************************************************************************
5 --
6 -- Copyright (c) Oracle Corporation (UK) Ltd 1993.
7 -- All Rights Reserved.
8 --
9 -- PRODUCT
10 -- Oracle*Payroll
11 --
12 -- NAME
13 --
14 --
15 -- DESCRIPTION
16 -- Magnetic tape format procedure for bacs.
17 --
18 /*
19 OVERVIEW
20
21 BACS is submitted via the PYUGEN C program that sets up the Payroll
22 Action for the mag tape process and its attendent assignment actions.
23
27 PAYROLL_ID N N select by payroll
24 Parameters: Mand UK(used in UK or legilative parameter[L]
25 CONSOLIDATION_SET Y N Used in select to set up assignment actions
26 for unpaid Pre Payment Actions
28 START_DATE N N only include pre payments from this date
29 EFFECTIVE_DATE N Y end of period?
30 PAYMENT_TYPE_ID Y Y BACS payment type
31 ORG_PAYMENT_METHOD_ID N N us field to output for just one debit account
32 OVERRIDE_DD_DATE N Y BACS processin date
33 EXPIRATION_DATE N L when will the tape expire (for bacs header)
34 SUBMISSION_NUMBER N L Volume Serial Number for Volume/File headers
35 MEDIA N L if Media=TEL then it indicates no Headers
36 MULTI_DAY N L is this a multi day run
37 BUREAU N L is this a multi file run for a bureau
38
39
40 The Parameter passed to the PLSQL procedure on its 1st call is the
41 payroll_action_id. The rest of the parameters update the approprate
42 columns on the payroll actions table - the legislative parameters
43 are all stored with there token identifyer(e.g. SUBMISSION_NUMBER=TAPE1
44 MEDIA=TAPE..) in the legislative parameter column.
45
46 Change List
47 -----------
48 Date Name Vers Bug No Description
49 ---- ---- ---- ------ -----------
50 30-JUN-95 ASNELL 40.0 Created.
51 30-JUN-95 NBRISTOW 40.1 Modified to use PL/SQL tables
52 to pass parameter and
53 and context rule data.
54
55 20-AUG-95 TINEKUKU Created routines to get and
56 validate the process date, i.e. check for weekends and Bank Holidays.
57
58 30-JUL-96 JALLOUN 40.2 Added error handling.
59 26-NOV-97 APARKES 110.1 Bug 572503 removed to_date
60 functions in cursor Payment_rule
61 in validate_process_date function
62 Bug 572935 hr_general.decode_lookup
63 used to decode return from get_banks
64 cursor. Same as UK arcs (R10) v40.14
65 12-DEC-97 APARKES 110.2 599470 Removed use of to_date in the cursor in
66 function check_hols when restricting by
67 ROW_LOW_RANGE_OR_NAME as this col may
68 contain non-date strings and otherwise
69 cause ORA-01858.
70 17-FEB-1998 APARKES 110.3 Converted implicit cursors in
71 functions validate_process_date
72 and get_process_date into
73 explicit cursors.
74 621006 Catered for periods in
75 get_process_date.csr_get_period_info
76 18-MAR-1998 APARKES 110.4 640915 Changed ADD_MONTHS behaviour in
77 get_process_date() for entry days
78 at end of short month.
79 Prevented validate_process_date()
80 overridding to Next Banking Day
81 behaviour if no BACS personal payment
82 method exists.
83 27-APR-1998 APARKES 110.5 648085 Made check for bank holidays
84 case insensitive in function
85 check_hols
86 03-DEC-1998 FDUCHENE 110.6 749168 Changed cursors (see .pkh)
87 16-FEB-1999 APARKES 110.7 809367 Made get_process_date treat
88 monthly periods the same as
89 shorter ones
90 07-OCT-1999 PDAVIES 110.8 Replaced all occurrences of
91 DBMS_Output.Put_Line with
92 hr_utility.trace.
93 16-FEB-2000 SMROBINS 115.1 1071880 Handle date parameters in
94 canonical format
95 24-NOV-2000 AMILLS 115.2 1381231 Added territory_code to get_
96 banks cursor as unique key
97 pay_payment_types_uk changed.
98 05-MAR-2002 KTHAMPAN 115.6 2231983 Change select statement in function check_hols from
99 select inst.user_column_instance_id into hols_id
100 to select 1 into hols_id
101 06-MAR-2002 KTHAMPAN 115.7 Add dbdrv command
102 06-MAR-2002 GBUTLER 115.8 Changed sql_str declaration in validate_process_date
103 for UTF8 project.
104 11-JUN-2002 KTHAMPAN 115.9 2366717 Removing knowledge of hardcoded id_flex_num.
105 09-SEP-2004 KTHAMPAN 115.10 Fix GSCC error
106 07-Jul-09 NAMGOYAL 115.12 8505257 Added Cash Management Reconciliation
107 function
108 17-Jul-09 NAMGOYAL 115.13 8505257 Added Customer extensible User Table logic to
109 Cash Management Reconciliation function
113 --
110
111 */
112 --
114 -- Package body:
115 --
116 --
117 --
118 --
119 --
120 total_body_count NUMBER;
121 total_contra_count NUMBER;
122 count_for_block NUMBER;
123 block_count NUMBER;
124 org_payment_count NUMBER;
125 p_value NUMBER;
126 p_payroll_action_id NUMBER;
127 p_assignment_number per_assignments.ASSIGNMENT_NUMBER%TYPE;
128 p_personal_payment_method_id NUMBER;
129 p_org_payment_method_id NUMBER;
130 p_previous_payment_id NUMBER;
131 total_payment NUMBER;
132 total_payment_footer NUMBER;
133 submission_number VARCHAR2(6);
134 expiration_date VARCHAR2(30);
135 todays_date VARCHAR2(30);
136 final_contra BOOLEAN;
137 fetch_required BOOLEAN;
138 process_date VARCHAR2(30);
139 --
140 CURSOR bacs_assignments( p_payroll_action_id NUMBER)
141 IS
142 SELECT ppp.org_payment_method_id,
143 ppp.personal_payment_method_id,
144 ppp.value,
145 pa.assignment_number
146 FROM pay_assignment_actions paa,
147 pay_pre_payments ppp,
148 per_assignments pa
149 WHERE paa.payroll_action_id = p_payroll_action_id
150 AND ppp.pre_payment_id = paa.pre_payment_id
151 AND paa.assignment_id = pa.assignment_id
152 ORDER BY ppp.org_payment_method_id, pa.assignment_number;
153 --
154 --
155 --
156 PROCEDURE new_formula IS
157 --
158 select_count VARCHAR2(11);
159 --
160 --
161 FUNCTION get_formula_id(p_formula_name IN VARCHAR2) RETURN INTEGER IS
162 p_formula_id INTEGER;
163 BEGIN
164 hr_utility.set_location('bacsmgtp.get_formula_id',1);
165 SELECT DISTINCT formula_id
166 INTO p_formula_id
167 FROM ff_formulas_f
168 WHERE formula_name = p_formula_name;
169 hr_utility.set_location('bacsmgtp.formula_id',p_formula_id);
170 --
171 RETURN p_formula_id;
172 --
173 END get_formula_id;
174 --
175 FUNCTION get_todays_date RETURN VARCHAR2 IS
176 todays_date VARCHAR2(30);
177 BEGIN
178 hr_utility.set_location('bacsmgtp.get_todays_date',1);
179 todays_date := to_char(sysdate,'YYYY/MM/DD HH24:MI:SS');
180 RETURN todays_date;
181 END get_todays_date;
182 --
183 FUNCTION get_session_date RETURN VARCHAR2 IS
184 p_session_date VARCHAR2(30);
185 BEGIN
186 hr_utility.set_location('bacsmgtp.get_session_date',1);
187 SELECT to_char(effective_date,'YYYY/MM/DD HH24:MI:SS')
188 INTO p_session_date
189 from fnd_sessions
190 where session_id = userenv('sessionid');
191 --
192 RETURN p_session_date;
193 --
194 END get_session_date;
195 --
196 FUNCTION get_expiration_date(p_payroll_action_id IN VARCHAR2)
197 RETURN VARCHAR2 is
198 p_expiration_date VARCHAR2(30);
199 BEGIN
200 hr_utility.set_location('bacsmgtp.get_expiration_date',1);
201 select nvl(substr(LEGISLATIVE_PARAMETERS,
202 decode(instr(LEGISLATIVE_PARAMETERS,'EXPIRATION_DATE='),
203 '0', null,
204 instr(LEGISLATIVE_PARAMETERS,'EXPIRATION_DATE='))+16,11),
205 to_char(add_months(sysdate,2),'YYYY/MM/DD HH24:MI:SS') ) Expiration_date
206 into p_expiration_date
207 from pay_payroll_actions
208 where PAYROLL_ACTION_ID = p_payroll_action_id;
209 --
210 RETURN p_expiration_date;
211 --
212 END get_expiration_date;
213 --
214 FUNCTION get_submission_number(p_payroll_action_id IN VARCHAR2)
215 RETURN VARCHAR2 is
216 p_submission_number VARCHAR2(6);
217 BEGIN
218 hr_utility.set_location('bacsmgtp.get_submission_number',1);
219 select
220 nvl(substr(LEGISLATIVE_PARAMETERS,
221 decode(instr(LEGISLATIVE_PARAMETERS,'SUBMISSION_NUMBER='),
222 '0', null,
223 instr(LEGISLATIVE_PARAMETERS,'SUBMISSION_NUMBER='))+18,6),
224 'NOLABL') Submission_number
225 into p_submission_number
226 from pay_payroll_actions
227 where PAYROLL_ACTION_ID = p_payroll_action_id;
228 --
229 hr_utility.set_location('bacsmgtp.get_submission_number',2);
230 RETURN p_submission_number;
231 --
232 END get_submission_number;
233 --
234 --
235 FUNCTION get_process_date(p_payroll_action_id IN VARCHAR2)
236 RETURN VARCHAR2 is
237 p_process_date VARCHAR2(30);
238 BEGIN
239 hr_utility.set_location('bacsmgtp.get_process_date',1);
240 hr_utility.trace('payroll_action_id='||p_payroll_action_id);
241 BEGIN
242 select
243 to_char(OVERRIDING_DD_DATE ,'YYYY/MM/DD HH24:MI:SS') effdate
244 into p_process_date
245 from pay_payroll_actions
246 where PAYROLL_ACTION_ID = p_payroll_action_id;
247 --
248 EXCEPTION when others then
249 hr_utility.set_message(801, 'Other error in get_process_date f');
250 hr_utility.raise_error;
254 RETURN p_process_date;
251 END;
252 --
253 hr_utility.set_location('bacsmgtp.get_process_date'||p_process_date,2);
255 --
256 END get_process_date;
257 --
258 --
259 -- Because our Bacs data is de-normalized I have to cheat and just select
260 -- one row.
261 FUNCTION get_org_context(p_payroll_action_id IN NUMBER) RETURN INTEGER IS
262 p_org_payment_method_id INTEGER;
263 BEGIN
264 hr_utility.set_location('bacsmgtp.get_org_context',1);
265 SELECT ppp.org_payment_method_id
266 INTO p_org_payment_method_id
267 FROM pay_assignment_actions paa, pay_pre_payments ppp
268 WHERE paa.payroll_action_id = p_payroll_action_id
269 AND ppp.pre_payment_id = paa.pre_payment_id
270 AND ROWNUM = 1
271 ORDER BY ppp.org_payment_method_id;
272 hr_utility.set_location('org_context',p_org_payment_method_id);
273 RETURN p_org_payment_method_id;
274 END get_org_context;
275 --
276 BEGIN
277 -- temporary trace AS set trace on and delay for a while to set up pipemon
278 --IF NOT bacs_assignments %ISOPEN THEN
279 --hr_utility.trace_on;
280 -- declare loop_counter number;
281 --begin
282 --loop_counter := 1;
283 --while loop_counter < 500000 LOOP
284 --loop_counter := loop_counter +1;
285 --END LOOP;
286 --end;
287 --end if;
288 -- end temporary trace AS
289 --
290 -- Reserved positions
291 pay_mag_tape.internal_prm_names(1) := 'NO_OF_PARAMETERS';
292 pay_mag_tape.internal_prm_names(2) := 'NEW_FORMULA_ID';
293 --
294 pay_mag_tape.internal_cxt_names(1) := 'Number_of_contexts';
295 -- Initial value
296 pay_mag_tape.internal_cxt_values(1) := 1;
297 --
298 --
299 IF NOT bacs_assignments %ISOPEN THEN -- New file
300 hr_utility.set_location('bacsmgtp.new_formula',1);
301 --
302 total_body_count := 0; -- Initial value
303 total_contra_count := 0;
304 count_for_block := 0;
305 org_payment_count := 0;
306 block_count := 1;
307 fetch_required := TRUE;
308 pay_mag_tape.internal_cxt_names(2) := 'ORG_PAY_METHOD_ID';
309 pay_mag_tape.internal_cxt_names(3) := 'DATE_EARNED';
310 pay_mag_tape.internal_cxt_values(1) := 3;
311 pay_mag_tape.internal_cxt_values(3) := get_session_date;
312 --
313 pay_mag_tape.internal_prm_values(1) := 7;
314 pay_mag_tape.internal_prm_values(2) := get_formula_id('BACS_HEADER');
315 --
316 -- AS it looks like we have 3 parms so try this
317 if pay_mag_tape.internal_prm_names(3) = 'PAYROLL_ACTION_ID'
318 then p_payroll_action_id := to_number(
319 pay_mag_tape.internal_prm_values(3));
320 end if;
321 hr_utility.set_location('bacsmgtp.payroll_action_id',p_payroll_action_id);
322 --
323 expiration_date := get_expiration_date(p_payroll_action_id);
324 submission_number := get_submission_number(p_payroll_action_id);
325 process_date := get_process_date(p_payroll_action_id);
326 pay_mag_tape.internal_cxt_values(2) :=
327 get_org_context(p_payroll_action_id);
328 p_previous_payment_id := get_org_context(p_payroll_action_id);
329 --
330 --
331 total_payment := 0;
332 total_payment_footer :=0;
333 final_contra := FALSE;
334 pay_mag_tape.internal_prm_names(3) := 'TRANSFER_EXPIRATION_DATE';
335 pay_mag_tape.internal_prm_values(3) := expiration_date;
336 pay_mag_tape.internal_prm_names(4) := 'TRANSFER_SUBMISSION_NUMBER';
337 pay_mag_tape.internal_prm_values(4) := submission_number;
338 pay_mag_tape.internal_prm_names(5) := 'TRANSFER_BACS_PROCESS_DATE';
339 pay_mag_tape.internal_prm_values(5) := process_date;
340 pay_mag_tape.internal_prm_names(6) := 'TRANSFER_SELECT_COUNT';
341 pay_mag_tape.internal_prm_values(6) := '0001';
342 pay_mag_tape.internal_prm_names(7) := 'TRANSFER_TODAYS_DATE';
343 pay_mag_tape.internal_prm_values(7) := get_todays_date;
344 --
345 OPEN bacs_assignments ( p_payroll_action_id);
346 --
347 ELSE
348 hr_utility.set_location('bacsmgtp.new_formula',2);
349 --
350 IF fetch_required = TRUE then
351 FETCH bacs_assignments INTO
352 p_org_payment_method_id,
353 p_personal_payment_method_id,
354 p_value,
355 p_assignment_number;
356 END IF;
357 --
358 --
359 IF bacs_assignments %FOUND THEN
360 IF p_org_payment_method_id = p_previous_payment_id
361 THEN
362 hr_utility.set_location('bacsmgtp.new_formula',3);
363 pay_mag_tape.internal_prm_values(1) := 4;
364 pay_mag_tape.internal_prm_values(2) := get_formula_id('BACS_BODY');
365 pay_mag_tape.internal_prm_names(2) := 'NEW_FORMULA_ID';
366 pay_mag_tape.internal_prm_names(3) := 'TRANSFER_VALUE' ;
367 pay_mag_tape.internal_prm_values(3) := p_value * 100;
368 pay_mag_tape.internal_prm_names(4) := 'TRANSFER_ASSIGN_NO';
369 pay_mag_tape.internal_prm_values(4) := p_assignment_number;
370 pay_mag_tape.internal_cxt_names(2) := 'ORG_PAY_METHOD_ID';
371 pay_mag_tape.internal_cxt_names(3) := 'DATE_EARNED';
372 pay_mag_tape.internal_cxt_values(2):= p_org_payment_method_id;
373 pay_mag_tape.internal_cxt_values(3):= get_session_date;
374 pay_mag_tape.internal_cxt_values(1):= 4;
375 pay_mag_tape.internal_cxt_names(4) := 'PER_PAY_METHOD_ID';
379 total_payment := (p_value * 100) + total_payment;
376 pay_mag_tape.internal_cxt_values(4):= p_personal_payment_method_id;
377 org_payment_count := org_payment_count + 1;
378 total_body_count := total_body_count + 1;
380 total_payment_footer := (p_value * 100) + total_payment_footer;
381 p_previous_payment_id := p_org_payment_method_id;
382 fetch_required := TRUE;
383 --
384 -- Check for the block size
385 --
386 IF count_for_block = 20 then
387 hr_utility.set_location('bacsmgtp.new_formula',4);
388 block_count := block_count + 1;
389 count_for_block := 1;
390 ELSE count_for_block := count_for_block + 1;
391 hr_utility.set_location('bacsmgtp.new_formula',5);
392 END IF;
393 --
394 ELSE
395 hr_utility.set_location('bacsmgtp.new_formula',6);
396 pay_mag_tape.internal_prm_values(1) := 5;
397 pay_mag_tape.internal_prm_values(2) := get_formula_id('BACS_CONTRA');
398 pay_mag_tape.internal_prm_names(2) := 'NEW_FORMULA_ID';
399 pay_mag_tape.internal_prm_names(3) := 'TRANSFER_TOTAL_PAYMENT';
400 pay_mag_tape.internal_prm_values(3) := total_payment;
401 pay_mag_tape.internal_prm_names(4) := 'TRANSFER_PAYMENT_COUNT';
402 pay_mag_tape.internal_prm_values(4) := org_payment_count;
403 pay_mag_tape.internal_prm_names(5) := 'TRANSFER_LAST_CONTRA';
404 pay_mag_tape.internal_prm_values(5) := 'N';
405 pay_mag_tape.internal_cxt_values(1) := 3;
406 pay_mag_tape.internal_cxt_names(2) := 'ORG_PAY_METHOD_ID';
407 pay_mag_tape.internal_cxt_values(2) := p_previous_payment_id;
408 pay_mag_tape.internal_cxt_names(3) := 'DATE_EARNED';
409 pay_mag_tape.internal_cxt_values(3) := get_session_date;
410 total_contra_count := total_contra_count + 1;
411 count_for_block :=count_for_block + 1;
412 p_previous_payment_id := p_org_payment_method_id;
413 org_payment_count := 0;
414 total_payment := 0;
415 fetch_required := FALSE;
416 END IF;
417 --
418 --
419 -- I need to call the CONTRA record again if it is the
420 -- last call before doing the padding and the footer
421 ELSE
422 IF final_contra = FALSE THEN
423 pay_mag_tape.internal_prm_values(1) := 5;
424 pay_mag_tape.internal_prm_values(2) := get_formula_id('BACS_CONTRA');
425 pay_mag_tape.internal_prm_names(2) := 'NEW_FORMULA_ID';
426 pay_mag_tape.internal_prm_names(3) := 'TRANSFER_TOTAL_PAYMENT';
427 pay_mag_tape.internal_prm_values(3) := total_payment;
428 pay_mag_tape.internal_prm_names(4) := 'TRANSFER_PAYMENT_COUNT';
429 pay_mag_tape.internal_prm_values(4) := org_payment_count;
430 pay_mag_tape.internal_prm_names(5) := 'TRANSFER_LAST_CONTRA';
431 pay_mag_tape.internal_prm_values(5) := 'Y';
432 pay_mag_tape.internal_cxt_values(1) := 3;
433 pay_mag_tape.internal_cxt_names(2) := 'ORG_PAY_METHOD_ID';
434 pay_mag_tape.internal_cxt_values(2) := p_previous_payment_id;
435 pay_mag_tape.internal_cxt_names(3) := 'DATE_EARNED';
436 pay_mag_tape.internal_cxt_values(3) := get_session_date;
437 total_contra_count := total_contra_count + 1;
438 count_for_block :=count_for_block + 1;
439 final_contra :=TRUE;
440 ELSE
441 hr_utility.set_location('bacsmgtp.new_formula',8);
442 IF count_for_block < 20 then
443 hr_utility.set_location('bacsmgtp.new_formula',9);
444 pay_mag_tape.internal_prm_values(1) :=2;
445 pay_mag_tape.internal_prm_names(2) := 'NEW_FORMULA_ID';
446 pay_mag_tape.internal_prm_values(2) :=
447 get_formula_id('BACS_PADDING');
448 count_for_block:= count_for_block + 1;
449 ELSE
450 -- Padding finished - Now write footer,
451 --
452 hr_utility.set_location('bacsmgtp.new_formula',10);
453 pay_mag_tape.internal_cxt_values(1) := 3;
454 pay_mag_tape.internal_cxt_names(2) := 'ORG_PAY_METHOD_ID';
455 pay_mag_tape.internal_cxt_values(2) := p_org_payment_method_id;
456 pay_mag_tape.internal_cxt_names(3) := 'DATE_EARNED';
457 pay_mag_tape.internal_cxt_values(3) := get_session_date;
458 pay_mag_tape.internal_prm_values(1) := 6;
459 pay_mag_tape.internal_prm_values(2) :=
460 get_formula_id('BACS_FOOTER');
461 pay_mag_tape.internal_prm_names(2) := 'NEW_FORMULA_ID';
462 pay_mag_tape.internal_prm_names(3) :=
463 'TRANSFER_EXPIRATION_DATE';
464 pay_mag_tape.internal_prm_values(3) := expiration_date;
465 pay_mag_tape.internal_prm_names(4) :=
466 'TRANSFER_SUBMISSION_NUMBER';
467 pay_mag_tape.internal_prm_values(4) := submission_number;
468 pay_mag_tape.internal_prm_names(5) := 'TRANSFER_BODY_COUNT';
469 pay_mag_tape.internal_prm_values(5) := total_body_count;
470 pay_mag_tape.internal_prm_names(6) := 'TRANSFER_BLOCK_COUNT';
471 pay_mag_tape.internal_prm_values(6) := block_count;
472 pay_mag_tape.internal_prm_names(7) := 'TRANSFER_TODAYS_DATE';
473 pay_mag_tape.internal_prm_values(7) := get_todays_date;
474 pay_mag_tape.internal_prm_names(8) := 'TRANSFER_TOTAL_PAYMENT';
475 pay_mag_tape.internal_prm_values(8) := total_payment_footer;
476 pay_mag_tape.internal_prm_names(9) := 'TRANSFER_CONTRA_COUNT';
480 END IF;
477 pay_mag_tape.internal_prm_values(9) := total_contra_count;
478 --
479 CLOSE bacs_assignments;
481 --
482 END IF;
483 --
484 END IF;
485 END IF;
486
487 END new_formula;
488 --
489 FUNCTION check_hols(date_in DATE, sql_str VARCHAR2) return boolean IS
490 status_flag varchar2(1);
491 hols_id number(4);
492 date_in_char varchar2(12);
493 begin
494 --
495 date_in_char:=to_char(date_in,'DD-MON-YYYY');
496 hr_utility.trace('Check for Bank Holiday in '||sql_str||'. Date: '||date_in_char);
497 select 1 into hols_id
498 from pay_user_column_instances_f inst,
499 pay_user_rows_f row1,
500 pay_user_columns col1,
501 pay_user_tables tab1
502 where tab1.user_table_name = 'BANK_HOLIDAYS'
503 and tab1.business_group_id is null
504 and tab1.legislation_code = 'GB'
505 and row1.user_table_id = tab1.user_table_id
506 and col1.user_table_id = tab1.user_table_id
507 and col1.user_column_name = sql_str
508 and inst.user_column_id = col1.user_column_id
509 and inst.user_row_id = row1.user_row_id
510 and upper(row1.ROW_LOW_RANGE_OR_NAME) = date_in_char;
511 --
512 return false;
513 --
514 EXCEPTION
515 when no_data_found then
516 hr_utility.trace('no data returned');
517 return true;
518 when too_many_rows then
519 hr_utility.trace('Too many rows returned');
520 return false;
521 end check_hols;
522 ----
523 FUNCTION main_routine (date1 DATE, sql_str VARCHAR2) RETURN DATE IS
524 --
525 valdate VARCHAR2(11);
526 return_date DATE := date1;
527 not_holiday_date boolean := false;
528 date_returned boolean := true;
529 added_value number(3) := 0;
530 date_ok boolean;
531 BEGIN
532 --
533 date_ok := false;
534 hr_utility.trace('MAIN ROUTINE ENTERED');
535 while not date_ok
536 loop
537 valdate := to_char( return_date,'D');
538 added_value := 0;
539 IF valdate = '1' then
540 added_value := -2;
541 end if;
542 if valdate = '7' then
543 added_value := -1;
544 end if;
545 --
546 return_date := return_date + added_value;
547 --
548 hr_utility.trace(valdate||' '||added_value);
549 hr_utility.trace( to_char( return_date, 'day-DD-MON-YYYY'));
550 --
551 date_returned := check_hols(return_date, sql_str);
552 --
553 if date_returned = false then
554 hr_utility.trace('date is a holiday');
555 return_date := return_date - 1;
556 date_ok := false;
557 else
558 hr_utility.trace('date is not a holiday');
559 hr_utility.trace( to_char(return_date, 'day-DD-MON-YYYY'));
560 date_ok := true;
561 end if;
562 --
563 if date_ok then
564 exit;
565 end if;
566 end loop;
567 RETURN return_date;
568 END main_routine;
569 --
570 --
571 FUNCTION get_process_date(p_assignment_action_id in number,
572 p_entry_date in date)
573 return date is
574 --
575 CURSOR csr_get_default_date IS
576 select default_dd_date
577 from pay_assignment_actions paa,
578 pay_payroll_actions ppa,
579 per_time_periods ptp
580 where paa.assignment_action_id =
581 p_assignment_action_id
582 and ppa.payroll_action_id =
583 paa.payroll_action_id
584 and ptp.time_period_id = ppa.time_period_id;
585 --
586 CURSOR csr_get_period_info IS
587 select ppa.effective_date,
588 ptp.end_date,
589 decode(tpr.basic_period_type,'CM',tpr.periods_per_period,0)
590 from per_time_period_rules tpr,
591 per_time_period_types tpt,
592 per_time_periods ptp,
593 pay_payroll_actions ppa,
594 pay_assignment_actions paa
595 where paa.assignment_action_id = p_assignment_action_id
596 and ppa.payroll_action_id = paa.payroll_action_id
597 and ptp.time_period_id = ppa.time_period_id
598 and ptp.period_type = tpt.period_type
599 and tpt.number_per_fiscal_year = tpr.number_per_fiscal_year;
600 --
601 l_dd_date DATE;
602 l_eff_date DATE;
603 l_period_end DATE;
604 l_months_in_period number(2); -- number of months in the period if basic
605 -- period is calender month, else set to 0
606 l_mth_diff number(8,2); -- difference in the dates
607 l_day_diff number(2); -- difference between the days
608 --
609 BEGIN
610 --
611 if p_entry_date = hr_general.start_of_time then
612 open csr_get_default_date;
613 fetch csr_get_default_date into l_dd_date;
614 close csr_get_default_date;
615 else
616 open csr_get_period_info;
617 fetch csr_get_period_info into l_eff_date,
618 l_period_end, l_months_in_period;
619 close csr_get_period_info;
620 if l_months_in_period <= 1 then
621 -- period is shorter than calendar month
622 -- use same month as effective date
623 l_mth_diff := MONTHS_BETWEEN(l_eff_date, p_entry_date);
624 if to_number(to_char(p_entry_date,'DD')) <=
628 l_dd_date := ADD_MONTHS(p_entry_date, ceil(l_mth_diff));
625 to_number(to_char(l_eff_date,'DD')) then
626 l_dd_date := ADD_MONTHS(p_entry_date, floor(l_mth_diff));
627 else
629 end if;
630 else
631 -- find the difference b/w the dates, add appropriate no. of periods
632 l_mth_diff := MONTHS_BETWEEN(l_period_end, p_entry_date)/l_months_in_period;
633 l_dd_date := ADD_MONTHS(p_entry_date, floor(l_mth_diff)*l_months_in_period);
634 -- floor used to cater for future entry dates
635 end if;
636 -- change ADD_MONTHS behaviour for entry days at end of short month 640915
637 l_day_diff:=to_number(to_char(l_dd_date,'dd')) -
638 to_number(to_char(p_entry_date,'dd'));
639 l_dd_date := l_dd_date - greatest(0,l_day_diff);
640 end if;
641 RETURN l_dd_date;
642 end get_process_date;
643 --
644 FUNCTION validate_process_date(p_assignment_action_id in number,
645 p_process_date in date)
646 return date is
647 --
648 CURSOR csr_get_details is
649 select paa.assignment_id, ppa.effective_date
650 from pay_payroll_actions ppa,
651 pay_assignment_actions paa
652 where paa.assignment_action_id = p_assignment_action_id
653 and ppa.payroll_action_id = paa.payroll_action_id;
654 --
655 CURSOR get_banks IS
656 select pea.segment8
657 from pay_org_payment_methods_f pop,
658 pay_personal_payment_methods_f ppp,
659 pay_assignment_actions paa,
660 pay_external_accounts pea,
661 pay_payment_types ppt
662 where paa.assignment_action_id =
663 p_assignment_action_id
664 and ppp.assignment_id =
665 paa.assignment_id
666 and pea.external_account_id =
667 ppp.external_account_id
668 and pop.org_payment_method_id =
669 ppp.org_payment_method_id
670 and ppt.payment_type_id =
671 pop.payment_type_id +0
672 and ppt.payment_type_name = 'BACS Tape'
673 and ppt.territory_code = 'GB';
674 --
675 CURSOR payment_rule(param1 DATE, param2 NUMBER) IS
676 SELECT target.SEGMENT9
677 FROM hr_soft_coding_keyflex target,
678 fnd_id_flex_structures FNDID,
679 per_assignments_f ASSIGN,
680 pay_payrolls_f PAYROLL
681 WHERE param1 BETWEEN ASSIGN.effective_start_date
682 AND ASSIGN.effective_end_date
683 AND ASSIGN.assignment_id = param2
684 AND target.id_flex_num = FNDID.id_flex_num
685 AND FNDID.id_flex_structure_code = 'GB_STATUTORY_INFO.'
686 AND target.enabled_flag = 'Y'
687 AND PAYROLL.payroll_id = ASSIGN.payroll_id
688 AND param1 BETWEEN PAYROLL.effective_start_date
689 AND PAYROLL.effective_end_date
690 AND target.soft_coding_keyflex_id =
691 PAYROLL.soft_coding_keyflex_id;
692 --
693 proc_date DATE;
694 eff_date DATE;
695 dd_date DATE := p_process_date;
696 sql_str pay_external_accounts.segment8%TYPE;
697 lowest_dd_date DATE := dd_date;
698 assignmt_id number;
699 scl_pay_gb_bacs_pay_rule VARCHAR2(1) := 'N';
700 --
701 BEGIN
702 --
703 open csr_get_details;
704 fetch csr_get_details into assignmt_id, eff_date;
705 close csr_get_details;
706 hr_utility.trace('Display effective date');
707 hr_utility.trace(eff_date);
708 hr_utility.trace('Display assignment id');
709 hr_utility.trace(assignmt_id);
710 --
711 hr_utility.trace('Display company payment rule');
712 --
713 open payment_rule(eff_date, assignmt_id);
714 loop
715 fetch payment_rule into scl_pay_gb_bacs_pay_rule;
716 exit when payment_rule%NOTFOUND;
717 end loop;
718 close payment_rule;
719 --
720 hr_utility.trace(scl_pay_gb_bacs_pay_rule);
721 if scl_pay_gb_bacs_pay_rule = 'P' then
722 hr_utility.trace('GET DEPOSIT DATE');
723 hr_utility.trace( to_char(dd_date, 'day-DD-MON-YYYY'));
724 --
725 sql_str := NULL;
726 open get_banks;
727 loop
728 fetch get_banks into sql_str;
729 if get_banks%FOUND or get_banks%ROWCOUNT = 0 then
730 if sql_str is NULL then
731 sql_str := 'England';
732 else
733 sql_str := hr_general.decode_lookup('GB_COUNTRY',sql_str);
734 end if;
735 dd_date := main_routine (dd_date, sql_str);
736 if dd_date < lowest_dd_date then
737 lowest_dd_date := dd_date;
738 end if;
739 end if;
740 exit when get_banks%NOTFOUND;
741 end loop;
742 close get_banks;
743 end if;
744 dd_date := lowest_dd_date;
745 proc_date := dd_date - 1;
746 sql_str := 'England';
747 hr_utility.trace('GET PROCESS DATE');
748 hr_utility.trace( to_char(proc_date, 'day-DD-MON-YYYY'));
749 proc_date := main_routine (proc_date, sql_str);
750 hr_utility.trace( to_char(dd_date, 'day-DD-MON-YYYY'));
751 hr_utility.trace( to_char(proc_date, 'day-DD-MON-YYYY'));
752 hr_utility.trace(' Lowest DATE');
753 hr_utility.trace( to_char(lowest_dd_date, 'day-DD-MON-YYYY'));
754 RETURN proc_date;
755 END validate_process_date;
756
757 --Cash Management Reconciliation function
758 FUNCTION f_get_eft_recon_data (p_effective_date IN DATE,
759 p_identifier_name IN VARCHAR2,
760 p_payroll_action_id IN NUMBER,
761 p_payment_type_id IN NUMBER,
762 p_org_payment_method_id IN NUMBER,
763 p_personal_payment_method_id IN NUMBER,
764 p_assignment_action_id IN NUMBER,
765 p_pre_payment_id IN NUMBER,
766 p_delimiter_string IN VARCHAR2)
767 RETURN VARCHAR2
768 IS
769
770 CURSOR c_get_bus_grp
771 IS
772 Select business_group_id
773 From pay_payroll_actions
774 Where payroll_action_id = p_payroll_action_id;
775
776 CURSOR c_get_trx_date
777 IS
778 Select overriding_dd_date
779 From pay_payroll_actions
780 Where payroll_action_id = p_payroll_action_id;
781
782 CURSOR c_get_conc_ident
783 IS
784 Select ext.segment3, --Sort Code
785 ext.segment4 --Acc Num
786 From pay_external_accounts ext,
787 pay_org_payment_methods_f org
788 Where org.org_payment_method_id = p_org_payment_method_id
789 and p_effective_date between org.effective_start_date and org.effective_end_date
790 and org.external_account_id = ext.external_account_id;
791
792 l_business_grp_id NUMBER;
793 l_usr_fnc_name VARCHAR2(5000):= NULL;
794 l_return_value VARCHAR2(80) := NULL;
795 l_trx_date Date;
796 l_sort_code VARCHAR2(30);
797 l_acc_num VARCHAR2(30);
798
799 BEGIN
800
801 OPEN c_get_bus_grp;
802 FETCH c_get_bus_grp INTO l_business_grp_id;
803 CLOSE c_get_bus_grp;
804
805 SELECT hruserdt.get_table_value(l_business_grp_id,
806 'GB_EFT_RECONC_FUNC',
807 'RECONCILIATION',
808 'FUNCTION NAME',
809 p_effective_date)
810 INTO l_usr_fnc_name
811 FROM dual;
812
813 IF l_usr_fnc_name IS NOT NULL
814 THEN
815 EXECUTE IMMEDIATE 'select '||l_usr_fnc_name||'(:1,:2,:3,:4,:5,:6,:7,:8,:9) from dual'
816 INTO l_return_value
817 USING p_effective_date ,
818 p_identifier_name,
819 p_payroll_action_id,
820 p_payment_type_id,
821 p_org_payment_method_id,
822 p_personal_payment_method_id,
823 p_assignment_action_id,
824 p_pre_payment_id,
825 p_delimiter_string ;
826 ELSE
827 IF UPPER(p_identifier_name) = 'TRANSACTION_DATE'
828 THEN
829 OPEN c_get_trx_date;
830 FETCH c_get_trx_date INTO l_trx_date;
831 CLOSE c_get_trx_date;
832
833 l_return_value := to_char(l_trx_date, 'yyyy/mm/dd');
834
835 ELSIF UPPER(p_identifier_name) = 'TRANSACTION_GROUP'
836 THEN
837 l_return_value := p_payroll_action_id;
838
839 ELSIF UPPER(p_identifier_name) = 'CONCATENATED_IDENTIFIERS'
840 THEN
841 OPEN c_get_conc_ident;
842 FETCH c_get_conc_ident INTO l_sort_code,l_acc_num;
843 CLOSE c_get_conc_ident;
844
845 l_return_value := 'BACS'||p_delimiter_string||l_sort_code||p_delimiter_string||l_acc_num;
846
847 END IF;
848 END IF;
849
850 RETURN l_return_value;
851
852 END f_get_eft_recon_data;
853
854 --
855 END pay_gb_bacs_tape;