[Home] [Help]
PACKAGE BODY: APPS.PAY_JP_ZENGIN_TAPE
Source
1 PACKAGE BODY pay_jp_zengin_tape AS
2 -- $Header: pyjptpzn.pkb 115.3 99/07/22 06:35:16 porting 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
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-1995 ASNELL 40.0 Created.
51 30-JUN-1995 NBRISTOW 40.1 Modified to use PL/SQL tables
52 to pass parameter and
53 and context rule data.
54 20-AUG-1995 TINEKUKU Created routines to get and
55 validate the process date,
56 i.e.check for weekends and Bank
57 Holidays.
58 30-JUL-1996 ALLOUN 40.2 Added error handling.
59 01-DEC-1996 TTAGAWA Package name is changed for Japanese
60 MAGTAPE process and recreated.
61 08-JAN-1999 YNEGORO 110.01 787405
62 03-JUN-1999 YNEGORO 115.02 Flex Date change
63 19-JUL-1999 TNANJYO 115.03 Add a semicolon to the exit statement.
64 Comment Out dbms_output.
65 */
66 --
67 --
68 -- Package body:
69 --
70 --
71 --
72 --
73 --
74 total_body_count NUMBER;
75 total_contra_count NUMBER;
76 count_for_block NUMBER;
77 block_count NUMBER;
78 org_payment_count NUMBER;
79 p_value NUMBER;
80 p_payroll_action_id NUMBER;
81 p_assignment_number per_assignments.ASSIGNMENT_NUMBER%TYPE;
82 p_personal_payment_method_id NUMBER;
83 p_org_payment_method_id NUMBER;
84 p_previous_payment_id NUMBER;
85 total_payment NUMBER;
86 total_payment_footer NUMBER;
87 submission_number VARCHAR2(6);
88 expiration_date VARCHAR2(11);
89 todays_date VARCHAR2(11);
90 final_contra BOOLEAN;
91 fetch_required BOOLEAN;
92 process_date VARCHAR2(11);
93 --
94 CURSOR bacs_assignments( p_payroll_action_id NUMBER)
95 IS
96 SELECT ppp.org_payment_method_id,
97 ppp.personal_payment_method_id,
98 ppp.value,
99 pa.assignment_number
100 FROM pay_assignment_actions paa,
101 pay_pre_payments ppp,
102 per_assignments pa
103 WHERE paa.payroll_action_id = p_payroll_action_id
104 AND ppp.pre_payment_id = paa.pre_payment_id
105 AND paa.assignment_id = pa.assignment_id
106 ORDER BY ppp.org_payment_method_id, pa.assignment_number;
107 --
108 --
109 --
110 PROCEDURE new_formula IS
111 --
112 select_count VARCHAR2(11);
113 --
114 --
115 FUNCTION get_formula_id(p_formula_name IN VARCHAR2) RETURN INTEGER IS
116 p_formula_id INTEGER;
117 BEGIN
118 hr_utility.set_location('bacsmgtp.get_formula_id',1);
119 SELECT DISTINCT formula_id
120 INTO p_formula_id
121 FROM ff_formulas_f
122 WHERE formula_name = p_formula_name;
123 hr_utility.set_location('bacsmgtp.formula_id',p_formula_id);
124 --
125 RETURN p_formula_id;
126 --
127 END get_formula_id;
128 --
129 FUNCTION get_todays_date RETURN VARCHAR2 IS
130 todays_date VARCHAR2(11);
131 BEGIN
132 hr_utility.set_location('bacsmgtp.get_todays_date',1);
133 todays_date := to_char(sysdate,'YYDDD');
134 RETURN todays_date;
135 END get_todays_date;
136 --
137 FUNCTION get_session_date RETURN VARCHAR2 IS
138 p_session_date VARCHAR2(11);
139 BEGIN
140 hr_utility.set_location('bacsmgtp.get_session_date',1);
141 -- FlexDate Change
142 -- SELECT to_char(effective_date,'DD-MON-YYYY')
143 SELECT fnd_date.date_to_canonical(effective_date)
144 INTO p_session_date
145 from fnd_sessions
146 where session_id = userenv('sessionid');
147 --
148 RETURN p_session_date;
149 --
150 END get_session_date;
151 --
152 FUNCTION get_expiration_date(p_payroll_action_id IN VARCHAR2)
153 RETURN VARCHAR2 is
154 p_expiration_date VARCHAR2(11);
155 BEGIN
156 hr_utility.set_location('bacsmgtp.get_expiration_date',1);
157 select nvl(substr(LEGISLATIVE_PARAMETERS,
158 decode(instr(LEGISLATIVE_PARAMETERS,'EXPIRATION_DATE='),
159 '0', null,
160 instr(LEGISLATIVE_PARAMETERS,'EXPIRATION_DATE='))+16,11),
161 -- to_char(add_months(sysdate,2),'DD-MON-YYYY') ) Expiration_date
162 fnd_date.date_to_canonical(add_months(sysdate,2)) ) Expiration_date
163 into p_expiration_date
164 from pay_payroll_actions
165 where PAYROLL_ACTION_ID = p_payroll_action_id;
166 --
167 RETURN p_expiration_date;
168 --
169 END get_expiration_date;
170 --
171 FUNCTION get_submission_number(p_payroll_action_id IN VARCHAR2)
172 RETURN VARCHAR2 is
173 p_submission_number VARCHAR2(6);
174 BEGIN
175 hr_utility.set_location('bacsmgtp.get_submission_number',1);
176 select
177 nvl(substr(LEGISLATIVE_PARAMETERS,
178 decode(instr(LEGISLATIVE_PARAMETERS,'SUBMISSION_NUMBER='),
179 '0', null,
180 instr(LEGISLATIVE_PARAMETERS,'SUBMISSION_NUMBER='))+18,6),
181 'NOLABL') Submission_number
182 into p_submission_number
183 from pay_payroll_actions
184 where PAYROLL_ACTION_ID = p_payroll_action_id;
185 --
186 hr_utility.set_location('bacsmgtp.get_submission_number',2);
187 RETURN p_submission_number;
188 --
189 END get_submission_number;
190 --
191 --
192 FUNCTION get_process_date(p_payroll_action_id IN VARCHAR2)
193 RETURN VARCHAR2 is
194 p_process_date VARCHAR2(11);
195 BEGIN
196 hr_utility.set_location('bacsmgtp.get_process_date',1);
197 hr_utility.trace('payroll_action_id='||p_payroll_action_id);
198 BEGIN
199 select
200 -- substr(to_char(OVERRIDING_DD_DATE ,'DD-MON-YYYY'),1,11) effdate
201 substr(fnd_date.date_to_canonical(OVERRIDING_DD_DATE),1,11) effdate
202 into p_process_date
203 from pay_payroll_actions
204 where PAYROLL_ACTION_ID = p_payroll_action_id;
205 --
206 EXCEPTION when others then
207 hr_utility.set_message(801, 'Other error in get_process_date f');
208 hr_utility.raise_error;
209 END;
210 --
211 hr_utility.set_location('bacsmgtp.get_process_date'||p_process_date,2);
212 RETURN p_process_date;
213 --
214 END get_process_date;
215 --
216 --
217 -- Because our Bacs data is de-normalized I have to cheat and just select
218 -- one row.
219 FUNCTION get_org_context(p_payroll_action_id IN NUMBER) RETURN INTEGER IS
220 p_org_payment_method_id INTEGER;
221 BEGIN
222 hr_utility.set_location('bacsmgtp.get_org_context',1);
223 SELECT ppp.org_payment_method_id
224 INTO p_org_payment_method_id
225 FROM pay_assignment_actions paa, pay_pre_payments ppp
226 WHERE paa.payroll_action_id = p_payroll_action_id
227 AND ppp.pre_payment_id = paa.pre_payment_id
228 AND ROWNUM = 1
229 ORDER BY ppp.org_payment_method_id;
230 hr_utility.set_location('org_context',p_org_payment_method_id);
231 RETURN p_org_payment_method_id;
232 END get_org_context;
233 --
234 BEGIN
235 -- temporary trace AS set trace on and delay for a while to set up pipemon
236 --IF NOT bacs_assignments %ISOPEN THEN
237 --hr_utility.trace_on;
238 -- declare loop_counter number;
239 --begin
240 --loop_counter := 1;
241 --while loop_counter < 500000 LOOP
242 --loop_counter := loop_counter +1;
243 --END LOOP;
244 --end;
245 --end if;
246 -- end temporary trace AS
247 --
248 -- Reserved positions
249 pay_mag_tape.internal_prm_names(1) := 'NO_OF_PARAMETERS';
250 pay_mag_tape.internal_prm_names(2) := 'NEW_FORMULA_ID';
251 --
252 pay_mag_tape.internal_cxt_names(1) := 'Number_of_contexts';
253 -- Initial value
254 pay_mag_tape.internal_cxt_values(1) := 1;
255 --
256 --
257 IF NOT bacs_assignments %ISOPEN THEN -- New file
258 hr_utility.set_location('bacsmgtp.new_formula',1);
259 --
260 total_body_count := 0; -- Initial value
261 total_contra_count := 0;
262 count_for_block := 0;
263 org_payment_count := 0;
264 block_count := 1;
265 fetch_required := TRUE;
266 pay_mag_tape.internal_cxt_names(2) := 'ORG_PAY_METHOD_ID';
267 pay_mag_tape.internal_cxt_names(3) := 'DATE_EARNED';
268 pay_mag_tape.internal_cxt_values(1) := 3;
269 pay_mag_tape.internal_cxt_values(3) := get_session_date;
270 --
271 pay_mag_tape.internal_prm_values(1) := 7;
272 pay_mag_tape.internal_prm_values(2) := get_formula_id('BACS_HEADER');
273 --
274 -- AS it looks like we have 3 parms so try this
275 if pay_mag_tape.internal_prm_names(3) = 'PAYROLL_ACTION_ID'
276 then p_payroll_action_id := to_number(
277 pay_mag_tape.internal_prm_values(3));
278 end if;
279 hr_utility.set_location('bacsmgtp.payroll_action_id',p_payroll_action_id);
280 --
281 expiration_date := get_expiration_date(p_payroll_action_id);
282 submission_number := get_submission_number(p_payroll_action_id);
283 process_date := get_process_date(p_payroll_action_id);
284 pay_mag_tape.internal_cxt_values(2) :=
285 get_org_context(p_payroll_action_id);
286 p_previous_payment_id := get_org_context(p_payroll_action_id);
287 --
288 --
289 total_payment := 0;
290 total_payment_footer :=0;
291 final_contra := FALSE;
292 pay_mag_tape.internal_prm_names(3) := 'TRANSFER_EXPIRATION_DATE';
293 pay_mag_tape.internal_prm_values(3) := expiration_date;
294 pay_mag_tape.internal_prm_names(4) := 'TRANSFER_SUBMISSION_NUMBER';
295 pay_mag_tape.internal_prm_values(4) := submission_number;
296 pay_mag_tape.internal_prm_names(5) := 'TRANSFER_BACS_PROCESS_DATE';
297 pay_mag_tape.internal_prm_values(5) := process_date;
298 pay_mag_tape.internal_prm_names(6) := 'TRANSFER_SELECT_COUNT';
299 pay_mag_tape.internal_prm_values(6) := '0001';
300 pay_mag_tape.internal_prm_names(7) := 'TRANSFER_TODAYS_DATE';
301 pay_mag_tape.internal_prm_values(7) := get_todays_date;
302 --
303 OPEN bacs_assignments ( p_payroll_action_id);
304 --
305 ELSE
306 hr_utility.set_location('bacsmgtp.new_formula',2);
307 --
308 IF fetch_required = TRUE then
309 FETCH bacs_assignments INTO
310 p_org_payment_method_id,
311 p_personal_payment_method_id,
312 p_value,
313 p_assignment_number;
314 END IF;
315 --
316 --
317 IF bacs_assignments %FOUND THEN
318 IF p_org_payment_method_id = p_previous_payment_id
319 THEN
320 hr_utility.set_location('bacsmgtp.new_formula',3);
321 pay_mag_tape.internal_prm_values(1) := 4;
322 pay_mag_tape.internal_prm_values(2) := get_formula_id('BACS_BODY');
323 pay_mag_tape.internal_prm_names(2) := 'NEW_FORMULA_ID';
324 pay_mag_tape.internal_prm_names(3) := 'TRANSFER_VALUE' ;
325 pay_mag_tape.internal_prm_values(3) := p_value * 100;
326 pay_mag_tape.internal_prm_names(4) := 'TRANSFER_ASSIGN_NO';
327 pay_mag_tape.internal_prm_values(4) := p_assignment_number;
328 pay_mag_tape.internal_cxt_names(2) := 'ORG_PAY_METHOD_ID';
329 pay_mag_tape.internal_cxt_names(3) := 'DATE_EARNED';
330 pay_mag_tape.internal_cxt_values(2):= p_org_payment_method_id;
331 pay_mag_tape.internal_cxt_values(3):= get_session_date;
332 pay_mag_tape.internal_cxt_values(1):= 4;
333 pay_mag_tape.internal_cxt_names(4) := 'PER_PAY_METHOD_ID';
334 pay_mag_tape.internal_cxt_values(4):= p_personal_payment_method_id;
335 org_payment_count := org_payment_count + 1;
336 total_body_count := total_body_count + 1;
337 total_payment := (p_value * 100) + total_payment;
338 total_payment_footer := (p_value * 100) + total_payment_footer;
339 p_previous_payment_id := p_org_payment_method_id;
340 fetch_required := TRUE;
341 --
342 -- Check for the block size
343 --
344 IF count_for_block = 20 then
345 hr_utility.set_location('bacsmgtp.new_formula',4);
346 block_count := block_count + 1;
347 count_for_block := 1;
348 ELSE count_for_block := count_for_block + 1;
349 hr_utility.set_location('bacsmgtp.new_formula',5);
350 END IF;
351 --
352 ELSE
353 hr_utility.set_location('bacsmgtp.new_formula',6);
354 pay_mag_tape.internal_prm_values(1) := 5;
355 pay_mag_tape.internal_prm_values(2) := get_formula_id('BACS_CONTRA');
356 pay_mag_tape.internal_prm_names(2) := 'NEW_FORMULA_ID';
357 pay_mag_tape.internal_prm_names(3) := 'TRANSFER_TOTAL_PAYMENT';
358 pay_mag_tape.internal_prm_values(3) := total_payment;
359 pay_mag_tape.internal_prm_names(4) := 'TRANSFER_PAYMENT_COUNT';
360 pay_mag_tape.internal_prm_values(4) := org_payment_count;
361 pay_mag_tape.internal_prm_names(5) := 'TRANSFER_LAST_CONTRA';
362 pay_mag_tape.internal_prm_values(5) := 'N';
363 pay_mag_tape.internal_cxt_values(1) := 3;
364 pay_mag_tape.internal_cxt_names(2) := 'ORG_PAY_METHOD_ID';
365 pay_mag_tape.internal_cxt_values(2) := p_previous_payment_id;
366 pay_mag_tape.internal_cxt_names(3) := 'DATE_EARNED';
367 pay_mag_tape.internal_cxt_values(3) := get_session_date;
368 total_contra_count := total_contra_count + 1;
369 count_for_block :=count_for_block + 1;
370 p_previous_payment_id := p_org_payment_method_id;
371 org_payment_count := 0;
372 total_payment := 0;
373 fetch_required := FALSE;
374 END IF;
375 --
376 --
377 -- I need to call the CONTRA record again if it is the
378 -- last call before doing the padding and the footer
379 ELSE
380 IF final_contra = FALSE THEN
381 pay_mag_tape.internal_prm_values(1) := 5;
382 pay_mag_tape.internal_prm_values(2) := get_formula_id('BACS_CONTRA');
383 pay_mag_tape.internal_prm_names(2) := 'NEW_FORMULA_ID';
384 pay_mag_tape.internal_prm_names(3) := 'TRANSFER_TOTAL_PAYMENT';
385 pay_mag_tape.internal_prm_values(3) := total_payment;
386 pay_mag_tape.internal_prm_names(4) := 'TRANSFER_PAYMENT_COUNT';
387 pay_mag_tape.internal_prm_values(4) := org_payment_count;
388 pay_mag_tape.internal_prm_names(5) := 'TRANSFER_LAST_CONTRA';
389 pay_mag_tape.internal_prm_values(5) := 'Y';
390 pay_mag_tape.internal_cxt_values(1) := 3;
391 pay_mag_tape.internal_cxt_names(2) := 'ORG_PAY_METHOD_ID';
392 pay_mag_tape.internal_cxt_values(2) := p_previous_payment_id;
393 pay_mag_tape.internal_cxt_names(3) := 'DATE_EARNED';
394 pay_mag_tape.internal_cxt_values(3) := get_session_date;
395 total_contra_count := total_contra_count + 1;
396 count_for_block :=count_for_block + 1;
397 final_contra :=TRUE;
398 ELSE
399 hr_utility.set_location('bacsmgtp.new_formula',8);
400 IF count_for_block < 20 then
401 hr_utility.set_location('bacsmgtp.new_formula',9);
402 pay_mag_tape.internal_prm_values(1) :=2;
403 pay_mag_tape.internal_prm_names(2) := 'NEW_FORMULA_ID';
404 pay_mag_tape.internal_prm_values(2) :=
405 get_formula_id('BACS_PADDING');
406 count_for_block:= count_for_block + 1;
407 ELSE
408 -- Padding finished - Now write footer,
409 --
410 hr_utility.set_location('bacsmgtp.new_formula',10);
411 pay_mag_tape.internal_cxt_values(1) := 3;
412 pay_mag_tape.internal_cxt_names(2) := 'ORG_PAY_METHOD_ID';
413 pay_mag_tape.internal_cxt_values(2) := p_org_payment_method_id;
414 pay_mag_tape.internal_cxt_names(3) := 'DATE_EARNED';
415 pay_mag_tape.internal_cxt_values(3) := get_session_date;
416 pay_mag_tape.internal_prm_values(1) := 6;
417 pay_mag_tape.internal_prm_values(2) :=
418 get_formula_id('BACS_FOOTER');
419 pay_mag_tape.internal_prm_names(2) := 'NEW_FORMULA_ID';
420 pay_mag_tape.internal_prm_names(3) :=
421 'TRANSFER_EXPIRATION_DATE';
422 pay_mag_tape.internal_prm_values(3) := expiration_date;
423 pay_mag_tape.internal_prm_names(4) :=
424 'TRANSFER_SUBMISSION_NUMBER';
425 pay_mag_tape.internal_prm_values(4) := submission_number;
426 pay_mag_tape.internal_prm_names(5) := 'TRANSFER_BODY_COUNT';
427 pay_mag_tape.internal_prm_values(5) := total_body_count;
428 pay_mag_tape.internal_prm_names(6) := 'TRANSFER_BLOCK_COUNT';
429 pay_mag_tape.internal_prm_values(6) := block_count;
430 pay_mag_tape.internal_prm_names(7) := 'TRANSFER_TODAYS_DATE';
431 pay_mag_tape.internal_prm_values(7) := get_todays_date;
432 pay_mag_tape.internal_prm_names(8) := 'TRANSFER_TOTAL_PAYMENT';
433 pay_mag_tape.internal_prm_values(8) := total_payment_footer;
434 pay_mag_tape.internal_prm_names(9) := 'TRANSFER_CONTRA_COUNT';
435 pay_mag_tape.internal_prm_values(9) := total_contra_count;
436 --
437 CLOSE bacs_assignments;
438 END IF;
439 --
440 END IF;
441 --
442 END IF;
443 END IF;
444
445 END new_formula;
446 --
447 FUNCTION check_hols(date_in DATE, sql_str VARCHAR2) return boolean IS
448 status_flag varchar2(1);
449 hols_id number(4);
450 begin
451 --
452 -- dbms_output.put_line('Check for Bank Holiday');
453 select inst.user_column_instance_id into hols_id
454 from pay_user_columns col1,
455 pay_user_tables tab1,
456 pay_user_rows_f row1,
457 pay_user_column_instances_f inst
458 where tab1.user_table_name = 'BANK_HOLIDAYS'
459 and row1.user_table_id = tab1.user_table_id
460 and col1.user_table_id = tab1.user_table_id
461 and col1.user_column_name = sql_str
462 and inst.user_column_id = col1.user_column_id
463 and inst.user_row_id = row1.user_row_id
464 and to_date(row1.ROW_LOW_RANGE_OR_NAME, 'DD-Mon-YYYY') = date_in;
465 --
466 return false;
467 --
468 EXCEPTION
469 when no_data_found then
470 -- dbms_output.put_line('no data returned');
471 return true;
472 when too_many_rows then
473 -- dbms_output.put_line('Too many rows returned');
474 return false;
475 end check_hols;
476 ----
477 FUNCTION main_routine (date1 DATE, sql_str VARCHAR2) RETURN DATE IS
478 --
479 valdate VARCHAR2(11);
480 return_date DATE := date1;
481 not_holiday_date boolean := false;
482 date_returned boolean := true;
483 added_value varchar(3) := '0';
484 date_ok boolean;
485 BEGIN
486 --
487 date_ok := false;
488 -- dbms_output.put_line('MAIN ROUTINE ENTERED');
489 while not date_ok
490 loop
491 valdate := to_char( return_date,'D');
492 added_value := '0';
493 IF valdate = '1' then
494 added_value := '-2';
495 end if;
496 if valdate = '7' then
497 added_value := '-1';
498 end if;
499 --
500 return_date := return_date + to_number(added_value);
501 --
502 -- dbms_output.put_line(valdate);
503 -- dbms_output.put_line(added_value);
504 -- dbms_output.put_line( to_char( return_date, 'day-DD-MON-YYYY'));
505 --
506 date_returned := check_hols(return_date, sql_str);
507 --
508 if date_returned = false then
509 -- dbms_output.put_line('date is a holiday');
510 return_date := return_date - 1;
511 date_ok := false;
512 else
513 -- dbms_output.put_line('date is not a holiday');
514 -- dbms_output.put_line( to_char(return_date, 'day-DD-MON-YYYY'));
515 date_ok := true;
516 end if;
517 --
518 if date_ok then
519 exit;
520 end if;
521 end loop;
522 RETURN return_date;
523 END main_routine;
524 --
525 --
526 FUNCTION get_process_date(p_assignment_action_id in number, p_entry_date in date)
527 return date is
528 --
529 dd_date DATE;
530 eff_date DATE;
531 diff2 number(8,2);
532 -- difference in the dates
533 --
534 BEGIN
535 --
536 --find the difference b/w the dates
537 if p_entry_date = hr_general.start_of_time then
538 select default_dd_date into dd_date
539 from pay_assignment_actions paa,
540 pay_payroll_actions ppa,
541 per_time_periods ptp
542 where paa.assignment_action_id =
543 p_assignment_action_id
544 and ppa.payroll_action_id =
545 paa.payroll_action_id
546 and ptp.time_period_id = ppa.time_period_id;
547 else
548 select ppa.effective_date into eff_date
549 from pay_payroll_actions ppa,
550 pay_assignment_actions paa
551 where paa.assignment_action_id = p_assignment_action_id
552 and ppa.payroll_action_id = paa.payroll_action_id;
553 --
554 -- dbms_output.put_line(eff_date);
555 diff2 := MONTHS_BETWEEN(eff_date, p_entry_date);
556 -- dbms_output.put_line(diff2);
557 --
558 -- Check if the payment day value not greater than effective day
559 if to_number(to_char(p_entry_date,'DD')) <=
560 to_number(to_char(eff_date,'DD')) then
561 dd_date := ADD_MONTHS(p_entry_date, diff2);
562 -- dbms_output.put_line(dd_date);
563 -- dbms_output.put_line('No round-up, lesser start day');
564 else
565 if round(diff2) < diff2 then
566 diff2 := round(diff2 , 2) + 1 ;
567 -- dbms_output.put_line('Date incremented');
568 else
569 diff2 := round(diff2) ;
570 -- dbms_output.put_line('Date rounded up');
571 end if;
572 dd_date := ADD_MONTHS(p_entry_date, diff2);
573 -- dbms_output.put_line(dd_date);
574 end if;
575 -- dbms_output.put_line(diff2);
576 end if;
577 RETURN dd_date;
578 end get_process_date;
579 --
580 --
581 FUNCTION validate_process_date(p_assignment_action_id in number, p_process_date in date)
582 return date is
583 CURSOR get_banks IS
584 select pea.segment8
585 from pay_org_payment_methods_f pop,
586 pay_personal_payment_methods_f ppp,
587 pay_assignment_actions paa,
588 pay_external_accounts pea,
589 pay_payment_types ppt
590 where paa.assignment_action_id =
591 p_assignment_action_id
592 and ppp.assignment_id =
593 paa.assignment_id
594 and pea.external_account_id =
595 ppp.external_account_id
596 and pop.org_payment_method_id =
597 ppp.org_payment_method_id
598 and ppt.payment_type_id =
599 pop.payment_type_id
600 and ppt.payment_type_name = 'BACS Tape';
601 --
602 CURSOR payment_rule(param1 DATE, param2 NUMBER) IS
603 SELECT target.SEGMENT9
604 FROM hr_soft_coding_keyflex target,
605 per_assignments_f ASSIGN,
606 pay_payrolls_f PAYROLL
607 -- WHERE to_date (param1, 'DD-MON-YYYY')
608 WHERE fnd_date.canonical_to_date(param1)
609 BETWEEN ASSIGN.effective_start_date
610 AND ASSIGN.effective_end_date
611 AND ASSIGN.assignment_id = param2
612 AND target.id_flex_num = 50106
613 AND target.enabled_flag = 'Y'
614 AND PAYROLL.payroll_id = ASSIGN.payroll_id
615 AND fnd_date.canonical_to_date(param1)
616 BETWEEN PAYROLL.effective_start_date
617 AND PAYROLL.effective_end_date
618 AND target.soft_coding_keyflex_id =
619 PAYROLL.soft_coding_keyflex_id;
620 --
621 proc_date DATE;
622 eff_date DATE;
623 dd_date DATE := p_process_date;
624 sql_str VARCHAR2 (50);
625 lowest_dd_date DATE := dd_date;
626 assignmt_id number;
627 scl_pay_gb_bacs_pay_rule VARCHAR2(1) := 'N';
628 --
629 begin
630 --
631 -- dbms_output.put_line('Display effective date');
632 select ppa.effective_date into eff_date
633 from pay_payroll_actions ppa,
634 pay_assignment_actions paa
635 where paa.assignment_action_id = p_assignment_action_id
636 and ppa.payroll_action_id = paa.payroll_action_id;
637 --
638 --
639 -- dbms_output.put_line(eff_date);
640 --
641 --dbms_output.put_line('Display assignment id');
642 select assignment_id into assignmt_id
643 from pay_assignment_actions
644 where assignment_action_id = p_assignment_action_id;
645 --
646 -- dbms_output.put_line(assignmt_id);
647 --
648 -- dbms_output.put_line('Display company payment rule');
649 --
650 open payment_rule(eff_date, assignmt_id);
651 loop
652 fetch payment_rule into scl_pay_gb_bacs_pay_rule;
653 exit when payment_rule%NOTFOUND;
654 end loop;
655 close payment_rule;
656 --
657 -- dbms_output.put_line(scl_pay_gb_bacs_pay_rule);
658 if scl_pay_gb_bacs_pay_rule = 'P' then
659 -- dbms_output.put_line('GET DEPOSIT DATE');
660 -- dbms_output.put_line( to_char(dd_date, 'day-DD-MON-YYYY'));
661 sql_str := 'England';
662 --
663 open get_banks;
664 loop
665 fetch get_banks into sql_str;
666 exit when get_banks%NOTFOUND;
667 if sql_str is NULL then
668 sql_str := 'England';
669 end if;
670 dd_date := main_routine (dd_date, sql_str);
671 if dd_date < lowest_dd_date then
672 lowest_dd_date := dd_date;
673 end if;
674 end loop;
675 close get_banks;
676 end if;
677 dd_date := lowest_dd_date;
678 proc_date := dd_date - 1;
679 sql_str := 'England';
680 -- dbms_output.put_line('GET PROCESS DATE');
681 -- dbms_output.put_line( to_char(proc_date, 'day-DD-MON-YYYY'));
682 proc_date := main_routine (proc_date, sql_str);
683 -- dbms_output.put_line( to_char(dd_date, 'day-DD-MON-YYYY'));
684 -- dbms_output.put_line( to_char(proc_date, 'day-DD-MON-YYYY'));
685 -- dbms_output.put_line(' Lowest DATE');
686 -- dbms_output.put_line( to_char(lowest_dd_date, 'day-DD-MON-YYYY'));
687 RETURN proc_date;
688 END validate_process_date;
689 --
690 END pay_jp_zengin_tape;