[Home] [Help]
PACKAGE BODY: APPS.PAY_GB_BACS_TAPE
Source
1 PACKAGE BODY pay_gb_bacs_tape AS
2 -- $Header: pytapbac.pkb 120.0.12000000.1 2007/01/18 01:44:43 appldev noship $
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
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
27 PAYROLL_ID N N select by payroll
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 */
107 --
108 --
109 -- Package body:
110 --
111 --
112 --
113 --
114 --
115 total_body_count NUMBER;
116 total_contra_count NUMBER;
117 count_for_block NUMBER;
118 block_count NUMBER;
119 org_payment_count NUMBER;
120 p_value NUMBER;
121 p_payroll_action_id NUMBER;
122 p_assignment_number per_assignments.ASSIGNMENT_NUMBER%TYPE;
123 p_personal_payment_method_id NUMBER;
124 p_org_payment_method_id NUMBER;
125 p_previous_payment_id NUMBER;
126 total_payment NUMBER;
127 total_payment_footer NUMBER;
128 submission_number VARCHAR2(6);
129 expiration_date VARCHAR2(30);
130 todays_date VARCHAR2(30);
131 final_contra BOOLEAN;
132 fetch_required BOOLEAN;
133 process_date VARCHAR2(30);
134 --
135 CURSOR bacs_assignments( p_payroll_action_id NUMBER)
136 IS
137 SELECT ppp.org_payment_method_id,
138 ppp.personal_payment_method_id,
139 ppp.value,
140 pa.assignment_number
141 FROM pay_assignment_actions paa,
142 pay_pre_payments ppp,
143 per_assignments pa
144 WHERE paa.payroll_action_id = p_payroll_action_id
145 AND ppp.pre_payment_id = paa.pre_payment_id
146 AND paa.assignment_id = pa.assignment_id
147 ORDER BY ppp.org_payment_method_id, pa.assignment_number;
148 --
149 --
150 --
151 PROCEDURE new_formula IS
152 --
153 select_count VARCHAR2(11);
154 --
155 --
156 FUNCTION get_formula_id(p_formula_name IN VARCHAR2) RETURN INTEGER IS
157 p_formula_id INTEGER;
158 BEGIN
159 hr_utility.set_location('bacsmgtp.get_formula_id',1);
160 SELECT DISTINCT formula_id
161 INTO p_formula_id
162 FROM ff_formulas_f
163 WHERE formula_name = p_formula_name;
164 hr_utility.set_location('bacsmgtp.formula_id',p_formula_id);
165 --
166 RETURN p_formula_id;
167 --
168 END get_formula_id;
169 --
170 FUNCTION get_todays_date RETURN VARCHAR2 IS
171 todays_date VARCHAR2(30);
172 BEGIN
173 hr_utility.set_location('bacsmgtp.get_todays_date',1);
174 todays_date := to_char(sysdate,'YYYY/MM/DD HH24:MI:SS');
175 RETURN todays_date;
176 END get_todays_date;
177 --
178 FUNCTION get_session_date RETURN VARCHAR2 IS
179 p_session_date VARCHAR2(30);
180 BEGIN
181 hr_utility.set_location('bacsmgtp.get_session_date',1);
182 SELECT to_char(effective_date,'YYYY/MM/DD HH24:MI:SS')
183 INTO p_session_date
184 from fnd_sessions
185 where session_id = userenv('sessionid');
186 --
187 RETURN p_session_date;
188 --
189 END get_session_date;
190 --
191 FUNCTION get_expiration_date(p_payroll_action_id IN VARCHAR2)
192 RETURN VARCHAR2 is
193 p_expiration_date VARCHAR2(30);
194 BEGIN
195 hr_utility.set_location('bacsmgtp.get_expiration_date',1);
196 select nvl(substr(LEGISLATIVE_PARAMETERS,
197 decode(instr(LEGISLATIVE_PARAMETERS,'EXPIRATION_DATE='),
198 '0', null,
199 instr(LEGISLATIVE_PARAMETERS,'EXPIRATION_DATE='))+16,11),
200 to_char(add_months(sysdate,2),'YYYY/MM/DD HH24:MI:SS') ) Expiration_date
201 into p_expiration_date
202 from pay_payroll_actions
203 where PAYROLL_ACTION_ID = p_payroll_action_id;
204 --
205 RETURN p_expiration_date;
206 --
207 END get_expiration_date;
208 --
209 FUNCTION get_submission_number(p_payroll_action_id IN VARCHAR2)
210 RETURN VARCHAR2 is
211 p_submission_number VARCHAR2(6);
212 BEGIN
213 hr_utility.set_location('bacsmgtp.get_submission_number',1);
214 select
215 nvl(substr(LEGISLATIVE_PARAMETERS,
216 decode(instr(LEGISLATIVE_PARAMETERS,'SUBMISSION_NUMBER='),
217 '0', null,
218 instr(LEGISLATIVE_PARAMETERS,'SUBMISSION_NUMBER='))+18,6),
219 'NOLABL') Submission_number
220 into p_submission_number
221 from pay_payroll_actions
222 where PAYROLL_ACTION_ID = p_payroll_action_id;
223 --
224 hr_utility.set_location('bacsmgtp.get_submission_number',2);
225 RETURN p_submission_number;
226 --
227 END get_submission_number;
228 --
229 --
230 FUNCTION get_process_date(p_payroll_action_id IN VARCHAR2)
231 RETURN VARCHAR2 is
232 p_process_date VARCHAR2(30);
233 BEGIN
234 hr_utility.set_location('bacsmgtp.get_process_date',1);
235 hr_utility.trace('payroll_action_id='||p_payroll_action_id);
236 BEGIN
237 select
238 to_char(OVERRIDING_DD_DATE ,'YYYY/MM/DD HH24:MI:SS') effdate
239 into p_process_date
240 from pay_payroll_actions
241 where PAYROLL_ACTION_ID = p_payroll_action_id;
242 --
243 EXCEPTION when others then
244 hr_utility.set_message(801, 'Other error in get_process_date f');
245 hr_utility.raise_error;
246 END;
247 --
248 hr_utility.set_location('bacsmgtp.get_process_date'||p_process_date,2);
249 RETURN p_process_date;
250 --
251 END get_process_date;
252 --
253 --
254 -- Because our Bacs data is de-normalized I have to cheat and just select
255 -- one row.
256 FUNCTION get_org_context(p_payroll_action_id IN NUMBER) RETURN INTEGER IS
257 p_org_payment_method_id INTEGER;
258 BEGIN
259 hr_utility.set_location('bacsmgtp.get_org_context',1);
260 SELECT ppp.org_payment_method_id
261 INTO p_org_payment_method_id
262 FROM pay_assignment_actions paa, pay_pre_payments ppp
263 WHERE paa.payroll_action_id = p_payroll_action_id
264 AND ppp.pre_payment_id = paa.pre_payment_id
265 AND ROWNUM = 1
266 ORDER BY ppp.org_payment_method_id;
267 hr_utility.set_location('org_context',p_org_payment_method_id);
268 RETURN p_org_payment_method_id;
269 END get_org_context;
270 --
271 BEGIN
272 -- temporary trace AS set trace on and delay for a while to set up pipemon
273 --IF NOT bacs_assignments %ISOPEN THEN
274 --hr_utility.trace_on;
275 -- declare loop_counter number;
276 --begin
277 --loop_counter := 1;
278 --while loop_counter < 500000 LOOP
279 --loop_counter := loop_counter +1;
280 --END LOOP;
281 --end;
282 --end if;
283 -- end temporary trace AS
284 --
285 -- Reserved positions
286 pay_mag_tape.internal_prm_names(1) := 'NO_OF_PARAMETERS';
287 pay_mag_tape.internal_prm_names(2) := 'NEW_FORMULA_ID';
288 --
289 pay_mag_tape.internal_cxt_names(1) := 'Number_of_contexts';
290 -- Initial value
291 pay_mag_tape.internal_cxt_values(1) := 1;
292 --
293 --
294 IF NOT bacs_assignments %ISOPEN THEN -- New file
295 hr_utility.set_location('bacsmgtp.new_formula',1);
296 --
297 total_body_count := 0; -- Initial value
298 total_contra_count := 0;
299 count_for_block := 0;
300 org_payment_count := 0;
301 block_count := 1;
302 fetch_required := TRUE;
303 pay_mag_tape.internal_cxt_names(2) := 'ORG_PAY_METHOD_ID';
304 pay_mag_tape.internal_cxt_names(3) := 'DATE_EARNED';
305 pay_mag_tape.internal_cxt_values(1) := 3;
306 pay_mag_tape.internal_cxt_values(3) := get_session_date;
307 --
308 pay_mag_tape.internal_prm_values(1) := 7;
309 pay_mag_tape.internal_prm_values(2) := get_formula_id('BACS_HEADER');
310 --
311 -- AS it looks like we have 3 parms so try this
312 if pay_mag_tape.internal_prm_names(3) = 'PAYROLL_ACTION_ID'
313 then p_payroll_action_id := to_number(
314 pay_mag_tape.internal_prm_values(3));
315 end if;
316 hr_utility.set_location('bacsmgtp.payroll_action_id',p_payroll_action_id);
317 --
318 expiration_date := get_expiration_date(p_payroll_action_id);
319 submission_number := get_submission_number(p_payroll_action_id);
320 process_date := get_process_date(p_payroll_action_id);
321 pay_mag_tape.internal_cxt_values(2) :=
322 get_org_context(p_payroll_action_id);
323 p_previous_payment_id := get_org_context(p_payroll_action_id);
324 --
325 --
326 total_payment := 0;
327 total_payment_footer :=0;
328 final_contra := FALSE;
329 pay_mag_tape.internal_prm_names(3) := 'TRANSFER_EXPIRATION_DATE';
330 pay_mag_tape.internal_prm_values(3) := expiration_date;
331 pay_mag_tape.internal_prm_names(4) := 'TRANSFER_SUBMISSION_NUMBER';
332 pay_mag_tape.internal_prm_values(4) := submission_number;
333 pay_mag_tape.internal_prm_names(5) := 'TRANSFER_BACS_PROCESS_DATE';
334 pay_mag_tape.internal_prm_values(5) := process_date;
335 pay_mag_tape.internal_prm_names(6) := 'TRANSFER_SELECT_COUNT';
336 pay_mag_tape.internal_prm_values(6) := '0001';
337 pay_mag_tape.internal_prm_names(7) := 'TRANSFER_TODAYS_DATE';
338 pay_mag_tape.internal_prm_values(7) := get_todays_date;
339 --
340 OPEN bacs_assignments ( p_payroll_action_id);
341 --
342 ELSE
343 hr_utility.set_location('bacsmgtp.new_formula',2);
344 --
345 IF fetch_required = TRUE then
346 FETCH bacs_assignments INTO
347 p_org_payment_method_id,
348 p_personal_payment_method_id,
349 p_value,
350 p_assignment_number;
351 END IF;
352 --
353 --
354 IF bacs_assignments %FOUND THEN
355 IF p_org_payment_method_id = p_previous_payment_id
356 THEN
357 hr_utility.set_location('bacsmgtp.new_formula',3);
358 pay_mag_tape.internal_prm_values(1) := 4;
359 pay_mag_tape.internal_prm_values(2) := get_formula_id('BACS_BODY');
360 pay_mag_tape.internal_prm_names(2) := 'NEW_FORMULA_ID';
361 pay_mag_tape.internal_prm_names(3) := 'TRANSFER_VALUE' ;
362 pay_mag_tape.internal_prm_values(3) := p_value * 100;
363 pay_mag_tape.internal_prm_names(4) := 'TRANSFER_ASSIGN_NO';
364 pay_mag_tape.internal_prm_values(4) := p_assignment_number;
365 pay_mag_tape.internal_cxt_names(2) := 'ORG_PAY_METHOD_ID';
366 pay_mag_tape.internal_cxt_names(3) := 'DATE_EARNED';
367 pay_mag_tape.internal_cxt_values(2):= p_org_payment_method_id;
368 pay_mag_tape.internal_cxt_values(3):= get_session_date;
369 pay_mag_tape.internal_cxt_values(1):= 4;
370 pay_mag_tape.internal_cxt_names(4) := 'PER_PAY_METHOD_ID';
371 pay_mag_tape.internal_cxt_values(4):= p_personal_payment_method_id;
372 org_payment_count := org_payment_count + 1;
373 total_body_count := total_body_count + 1;
374 total_payment := (p_value * 100) + total_payment;
375 total_payment_footer := (p_value * 100) + total_payment_footer;
376 p_previous_payment_id := p_org_payment_method_id;
377 fetch_required := TRUE;
378 --
379 -- Check for the block size
380 --
381 IF count_for_block = 20 then
382 hr_utility.set_location('bacsmgtp.new_formula',4);
383 block_count := block_count + 1;
384 count_for_block := 1;
385 ELSE count_for_block := count_for_block + 1;
386 hr_utility.set_location('bacsmgtp.new_formula',5);
387 END IF;
388 --
389 ELSE
390 hr_utility.set_location('bacsmgtp.new_formula',6);
391 pay_mag_tape.internal_prm_values(1) := 5;
392 pay_mag_tape.internal_prm_values(2) := get_formula_id('BACS_CONTRA');
393 pay_mag_tape.internal_prm_names(2) := 'NEW_FORMULA_ID';
394 pay_mag_tape.internal_prm_names(3) := 'TRANSFER_TOTAL_PAYMENT';
395 pay_mag_tape.internal_prm_values(3) := total_payment;
396 pay_mag_tape.internal_prm_names(4) := 'TRANSFER_PAYMENT_COUNT';
397 pay_mag_tape.internal_prm_values(4) := org_payment_count;
398 pay_mag_tape.internal_prm_names(5) := 'TRANSFER_LAST_CONTRA';
399 pay_mag_tape.internal_prm_values(5) := 'N';
400 pay_mag_tape.internal_cxt_values(1) := 3;
401 pay_mag_tape.internal_cxt_names(2) := 'ORG_PAY_METHOD_ID';
402 pay_mag_tape.internal_cxt_values(2) := p_previous_payment_id;
403 pay_mag_tape.internal_cxt_names(3) := 'DATE_EARNED';
404 pay_mag_tape.internal_cxt_values(3) := get_session_date;
405 total_contra_count := total_contra_count + 1;
406 count_for_block :=count_for_block + 1;
407 p_previous_payment_id := p_org_payment_method_id;
408 org_payment_count := 0;
409 total_payment := 0;
410 fetch_required := FALSE;
411 END IF;
412 --
413 --
414 -- I need to call the CONTRA record again if it is the
415 -- last call before doing the padding and the footer
416 ELSE
417 IF final_contra = FALSE THEN
418 pay_mag_tape.internal_prm_values(1) := 5;
419 pay_mag_tape.internal_prm_values(2) := get_formula_id('BACS_CONTRA');
420 pay_mag_tape.internal_prm_names(2) := 'NEW_FORMULA_ID';
421 pay_mag_tape.internal_prm_names(3) := 'TRANSFER_TOTAL_PAYMENT';
422 pay_mag_tape.internal_prm_values(3) := total_payment;
423 pay_mag_tape.internal_prm_names(4) := 'TRANSFER_PAYMENT_COUNT';
424 pay_mag_tape.internal_prm_values(4) := org_payment_count;
425 pay_mag_tape.internal_prm_names(5) := 'TRANSFER_LAST_CONTRA';
426 pay_mag_tape.internal_prm_values(5) := 'Y';
427 pay_mag_tape.internal_cxt_values(1) := 3;
428 pay_mag_tape.internal_cxt_names(2) := 'ORG_PAY_METHOD_ID';
429 pay_mag_tape.internal_cxt_values(2) := p_previous_payment_id;
430 pay_mag_tape.internal_cxt_names(3) := 'DATE_EARNED';
431 pay_mag_tape.internal_cxt_values(3) := get_session_date;
432 total_contra_count := total_contra_count + 1;
433 count_for_block :=count_for_block + 1;
434 final_contra :=TRUE;
435 ELSE
436 hr_utility.set_location('bacsmgtp.new_formula',8);
437 IF count_for_block < 20 then
438 hr_utility.set_location('bacsmgtp.new_formula',9);
439 pay_mag_tape.internal_prm_values(1) :=2;
440 pay_mag_tape.internal_prm_names(2) := 'NEW_FORMULA_ID';
441 pay_mag_tape.internal_prm_values(2) :=
442 get_formula_id('BACS_PADDING');
443 count_for_block:= count_for_block + 1;
444 ELSE
445 -- Padding finished - Now write footer,
446 --
447 hr_utility.set_location('bacsmgtp.new_formula',10);
448 pay_mag_tape.internal_cxt_values(1) := 3;
449 pay_mag_tape.internal_cxt_names(2) := 'ORG_PAY_METHOD_ID';
450 pay_mag_tape.internal_cxt_values(2) := p_org_payment_method_id;
451 pay_mag_tape.internal_cxt_names(3) := 'DATE_EARNED';
452 pay_mag_tape.internal_cxt_values(3) := get_session_date;
453 pay_mag_tape.internal_prm_values(1) := 6;
454 pay_mag_tape.internal_prm_values(2) :=
455 get_formula_id('BACS_FOOTER');
456 pay_mag_tape.internal_prm_names(2) := 'NEW_FORMULA_ID';
457 pay_mag_tape.internal_prm_names(3) :=
458 'TRANSFER_EXPIRATION_DATE';
459 pay_mag_tape.internal_prm_values(3) := expiration_date;
460 pay_mag_tape.internal_prm_names(4) :=
461 'TRANSFER_SUBMISSION_NUMBER';
462 pay_mag_tape.internal_prm_values(4) := submission_number;
463 pay_mag_tape.internal_prm_names(5) := 'TRANSFER_BODY_COUNT';
464 pay_mag_tape.internal_prm_values(5) := total_body_count;
465 pay_mag_tape.internal_prm_names(6) := 'TRANSFER_BLOCK_COUNT';
466 pay_mag_tape.internal_prm_values(6) := block_count;
467 pay_mag_tape.internal_prm_names(7) := 'TRANSFER_TODAYS_DATE';
468 pay_mag_tape.internal_prm_values(7) := get_todays_date;
469 pay_mag_tape.internal_prm_names(8) := 'TRANSFER_TOTAL_PAYMENT';
470 pay_mag_tape.internal_prm_values(8) := total_payment_footer;
471 pay_mag_tape.internal_prm_names(9) := 'TRANSFER_CONTRA_COUNT';
472 pay_mag_tape.internal_prm_values(9) := total_contra_count;
473 --
474 CLOSE bacs_assignments;
475 END IF;
476 --
477 END IF;
478 --
479 END IF;
480 END IF;
481
482 END new_formula;
483 --
484 FUNCTION check_hols(date_in DATE, sql_str VARCHAR2) return boolean IS
485 status_flag varchar2(1);
486 hols_id number(4);
487 date_in_char varchar2(12);
488 begin
489 --
490 date_in_char:=to_char(date_in,'DD-MON-YYYY');
491 hr_utility.trace('Check for Bank Holiday in '||sql_str||'. Date: '||date_in_char);
492 select 1 into hols_id
493 from pay_user_column_instances_f inst,
494 pay_user_rows_f row1,
495 pay_user_columns col1,
496 pay_user_tables tab1
497 where tab1.user_table_name = 'BANK_HOLIDAYS'
498 and tab1.business_group_id is null
499 and tab1.legislation_code = 'GB'
500 and row1.user_table_id = tab1.user_table_id
501 and col1.user_table_id = tab1.user_table_id
502 and col1.user_column_name = sql_str
503 and inst.user_column_id = col1.user_column_id
504 and inst.user_row_id = row1.user_row_id
505 and upper(row1.ROW_LOW_RANGE_OR_NAME) = date_in_char;
506 --
507 return false;
508 --
509 EXCEPTION
510 when no_data_found then
511 hr_utility.trace('no data returned');
512 return true;
513 when too_many_rows then
514 hr_utility.trace('Too many rows returned');
515 return false;
516 end check_hols;
517 ----
518 FUNCTION main_routine (date1 DATE, sql_str VARCHAR2) RETURN DATE IS
519 --
520 valdate VARCHAR2(11);
521 return_date DATE := date1;
522 not_holiday_date boolean := false;
523 date_returned boolean := true;
524 added_value number(3) := 0;
525 date_ok boolean;
526 BEGIN
527 --
528 date_ok := false;
529 hr_utility.trace('MAIN ROUTINE ENTERED');
530 while not date_ok
531 loop
532 valdate := to_char( return_date,'D');
533 added_value := 0;
534 IF valdate = '1' then
535 added_value := -2;
536 end if;
537 if valdate = '7' then
538 added_value := -1;
539 end if;
540 --
541 return_date := return_date + added_value;
542 --
543 hr_utility.trace(valdate||' '||added_value);
544 hr_utility.trace( to_char( return_date, 'day-DD-MON-YYYY'));
545 --
546 date_returned := check_hols(return_date, sql_str);
547 --
548 if date_returned = false then
549 hr_utility.trace('date is a holiday');
550 return_date := return_date - 1;
551 date_ok := false;
552 else
553 hr_utility.trace('date is not a holiday');
554 hr_utility.trace( to_char(return_date, 'day-DD-MON-YYYY'));
555 date_ok := true;
556 end if;
557 --
558 if date_ok then
559 exit;
560 end if;
561 end loop;
562 RETURN return_date;
563 END main_routine;
564 --
565 --
566 FUNCTION get_process_date(p_assignment_action_id in number,
567 p_entry_date in date)
568 return date is
569 --
570 CURSOR csr_get_default_date IS
571 select default_dd_date
572 from pay_assignment_actions paa,
573 pay_payroll_actions ppa,
574 per_time_periods ptp
575 where paa.assignment_action_id =
576 p_assignment_action_id
577 and ppa.payroll_action_id =
578 paa.payroll_action_id
579 and ptp.time_period_id = ppa.time_period_id;
580 --
581 CURSOR csr_get_period_info IS
582 select ppa.effective_date,
583 ptp.end_date,
584 decode(tpr.basic_period_type,'CM',tpr.periods_per_period,0)
585 from per_time_period_rules tpr,
586 per_time_period_types tpt,
587 per_time_periods ptp,
588 pay_payroll_actions ppa,
589 pay_assignment_actions paa
590 where paa.assignment_action_id = p_assignment_action_id
591 and ppa.payroll_action_id = paa.payroll_action_id
592 and ptp.time_period_id = ppa.time_period_id
593 and ptp.period_type = tpt.period_type
594 and tpt.number_per_fiscal_year = tpr.number_per_fiscal_year;
595 --
596 l_dd_date DATE;
597 l_eff_date DATE;
598 l_period_end DATE;
599 l_months_in_period number(2); -- number of months in the period if basic
600 -- period is calender month, else set to 0
601 l_mth_diff number(8,2); -- difference in the dates
602 l_day_diff number(2); -- difference between the days
603 --
604 BEGIN
605 --
606 if p_entry_date = hr_general.start_of_time then
607 open csr_get_default_date;
608 fetch csr_get_default_date into l_dd_date;
609 close csr_get_default_date;
610 else
611 open csr_get_period_info;
612 fetch csr_get_period_info into l_eff_date,
613 l_period_end, l_months_in_period;
614 close csr_get_period_info;
615 if l_months_in_period <= 1 then
616 -- period is shorter than calendar month
617 -- use same month as effective date
618 l_mth_diff := MONTHS_BETWEEN(l_eff_date, p_entry_date);
619 if to_number(to_char(p_entry_date,'DD')) <=
620 to_number(to_char(l_eff_date,'DD')) then
621 l_dd_date := ADD_MONTHS(p_entry_date, floor(l_mth_diff));
622 else
623 l_dd_date := ADD_MONTHS(p_entry_date, ceil(l_mth_diff));
624 end if;
625 else
626 -- find the difference b/w the dates, add appropriate no. of periods
627 l_mth_diff := MONTHS_BETWEEN(l_period_end, p_entry_date)/l_months_in_period;
628 l_dd_date := ADD_MONTHS(p_entry_date, floor(l_mth_diff)*l_months_in_period);
629 -- floor used to cater for future entry dates
630 end if;
631 -- change ADD_MONTHS behaviour for entry days at end of short month 640915
632 l_day_diff:=to_number(to_char(l_dd_date,'dd')) -
633 to_number(to_char(p_entry_date,'dd'));
634 l_dd_date := l_dd_date - greatest(0,l_day_diff);
635 end if;
636 RETURN l_dd_date;
637 end get_process_date;
638 --
639 FUNCTION validate_process_date(p_assignment_action_id in number,
640 p_process_date in date)
641 return date is
642 --
643 CURSOR csr_get_details is
644 select paa.assignment_id, ppa.effective_date
645 from pay_payroll_actions ppa,
646 pay_assignment_actions paa
647 where paa.assignment_action_id = p_assignment_action_id
648 and ppa.payroll_action_id = paa.payroll_action_id;
649 --
650 CURSOR get_banks IS
651 select pea.segment8
652 from pay_org_payment_methods_f pop,
653 pay_personal_payment_methods_f ppp,
654 pay_assignment_actions paa,
655 pay_external_accounts pea,
656 pay_payment_types ppt
657 where paa.assignment_action_id =
658 p_assignment_action_id
659 and ppp.assignment_id =
660 paa.assignment_id
661 and pea.external_account_id =
662 ppp.external_account_id
663 and pop.org_payment_method_id =
664 ppp.org_payment_method_id
665 and ppt.payment_type_id =
666 pop.payment_type_id +0
667 and ppt.payment_type_name = 'BACS Tape'
668 and ppt.territory_code = 'GB';
669 --
670 CURSOR payment_rule(param1 DATE, param2 NUMBER) IS
671 SELECT target.SEGMENT9
672 FROM hr_soft_coding_keyflex target,
673 fnd_id_flex_structures FNDID,
674 per_assignments_f ASSIGN,
675 pay_payrolls_f PAYROLL
676 WHERE param1 BETWEEN ASSIGN.effective_start_date
677 AND ASSIGN.effective_end_date
678 AND ASSIGN.assignment_id = param2
679 AND target.id_flex_num = FNDID.id_flex_num
680 AND FNDID.id_flex_structure_code = 'GB_STATUTORY_INFO.'
681 AND target.enabled_flag = 'Y'
682 AND PAYROLL.payroll_id = ASSIGN.payroll_id
683 AND param1 BETWEEN PAYROLL.effective_start_date
684 AND PAYROLL.effective_end_date
685 AND target.soft_coding_keyflex_id =
686 PAYROLL.soft_coding_keyflex_id;
687 --
688 proc_date DATE;
689 eff_date DATE;
690 dd_date DATE := p_process_date;
691 sql_str pay_external_accounts.segment8%TYPE;
692 lowest_dd_date DATE := dd_date;
693 assignmt_id number;
694 scl_pay_gb_bacs_pay_rule VARCHAR2(1) := 'N';
695 --
696 BEGIN
697 --
698 open csr_get_details;
699 fetch csr_get_details into assignmt_id, eff_date;
700 close csr_get_details;
701 hr_utility.trace('Display effective date');
702 hr_utility.trace(eff_date);
703 hr_utility.trace('Display assignment id');
704 hr_utility.trace(assignmt_id);
705 --
706 hr_utility.trace('Display company payment rule');
707 --
708 open payment_rule(eff_date, assignmt_id);
709 loop
710 fetch payment_rule into scl_pay_gb_bacs_pay_rule;
711 exit when payment_rule%NOTFOUND;
712 end loop;
713 close payment_rule;
714 --
715 hr_utility.trace(scl_pay_gb_bacs_pay_rule);
716 if scl_pay_gb_bacs_pay_rule = 'P' then
717 hr_utility.trace('GET DEPOSIT DATE');
718 hr_utility.trace( to_char(dd_date, 'day-DD-MON-YYYY'));
719 --
720 sql_str := NULL;
721 open get_banks;
722 loop
723 fetch get_banks into sql_str;
724 if get_banks%FOUND or get_banks%ROWCOUNT = 0 then
725 if sql_str is NULL then
726 sql_str := 'England';
727 else
728 sql_str := hr_general.decode_lookup('GB_COUNTRY',sql_str);
729 end if;
730 dd_date := main_routine (dd_date, sql_str);
731 if dd_date < lowest_dd_date then
732 lowest_dd_date := dd_date;
733 end if;
734 end if;
735 exit when get_banks%NOTFOUND;
736 end loop;
737 close get_banks;
738 end if;
739 dd_date := lowest_dd_date;
740 proc_date := dd_date - 1;
741 sql_str := 'England';
742 hr_utility.trace('GET PROCESS DATE');
743 hr_utility.trace( to_char(proc_date, 'day-DD-MON-YYYY'));
744 proc_date := main_routine (proc_date, sql_str);
745 hr_utility.trace( to_char(dd_date, 'day-DD-MON-YYYY'));
746 hr_utility.trace( to_char(proc_date, 'day-DD-MON-YYYY'));
747 hr_utility.trace(' Lowest DATE');
748 hr_utility.trace( to_char(lowest_dd_date, 'day-DD-MON-YYYY'));
749 RETURN proc_date;
750 END validate_process_date;
751 --
752 END pay_gb_bacs_tape;