1 package body pay_fr_overtime_mapping as
2 /* $Header: pyfromap.pkb 115.2 2002/11/22 11:35:08 sfmorris noship $ */
3 /*
4 +======================================================================+
5 | Copyright (c) 1997 Oracle Corporation UK Ltd |
6 | Reading, Berkshire, England |
7 | All rights reserved. |
8 +======================================================================+
9 Package Body Name : pay_fr_overtime_mapping
10 Package File Name : pyfromap.pkb
11 Description : This package contains procedures to support the PYFROMAP
12 concurrent process
13
14 Change List:
15 ------------
16
17 Name Date Version Bug Text
18 -------------- ---------- ------- ------- ------------------------------
19 J.Rhodes 02-May-01 115.0 Initial Version
20 J.Rhodes 21-Nov-02 115.1 NOCOPY Changes
21 S.Morrison 22-Nov-02 115.2 Fixed GSCC Errors
22 ========================================================================
23 */
24
25 /* ---------------------------------------------------------------------
26 NAME
27 generate
28 DESCRIPTION
29 This procedure generate a mapping of overtime weeks onto the payroll
30 period in which they will be paid
31 --------------------------------------------------------------------- */
32 procedure generate
33 (errbuf out nocopy varchar2
34 ,retcode out nocopy number
35 ,p_overtime_payroll_id number
36 ,p_start_ot_period_id number
37 ,p_end_ot_period_id number
38 ,p_payroll_id number
39 ,p_start_py_period_id number
40 ,p_end_py_period_id number
41 ,p_pattern varchar2
42 ,p_override varchar2) is
43 --
44 TYPE period_type is TABLE of NUMBER INDEX by binary_integer;
45 period period_type;
46 --
47 l_dummy date;
48 l_period_end date;
49 --
50 l_min_ot_period date;
51 l_max_ot_period date;
52 --
53 l_min_py_period date;
54 l_max_py_period date;
55 --
56 l_pattern_components number;
57 l_py_period_counter number;
58 l_ot_period_counter number;
59 l_ot_weeks number;
60 l_py_period_id number;
61 l_py_period_end_date date;
62 --
63 cursor c_get_period_date(p_time_period_id number) is
64 select start_date,end_date
65 from per_time_periods
66 where time_period_id = p_time_period_id;
67 --
68 cursor c_get_last_period_end_date(p_payroll_id number) is
69 select max(end_date)
70 from per_time_periods
71 where payroll_id = p_payroll_id;
72 --
73 cursor c_payroll_periods is
74 select time_period_id,end_date
75 from per_time_periods
76 where payroll_id = p_payroll_id
77 and end_date >= l_min_py_period
78 and end_date <= l_max_py_period
79 order by end_date;
80 --
81 cursor c_overtime_periods is
82 select time_period_id,end_date
83 from per_time_periods
84 where payroll_id = p_overtime_payroll_id
85 and end_date >= l_min_ot_period
86 and end_date <= l_max_ot_period
87 order by end_date;
88 --
89 begin
90 --
91 /* Determine the minimum and maximum end dates of overtime periods and
92 payroll periods based on input parameters */
93 --
94 open c_get_period_date(P_START_OT_PERIOD_ID);
95 fetch c_get_period_date into l_min_ot_period,l_dummy;
96 close c_get_period_date;
97 --
98 if P_END_OT_PERIOD_ID is not null then
99 open c_get_period_date(P_END_OT_PERIOD_ID);
100 fetch c_get_period_date into l_dummy,l_period_end;
101 close c_get_period_date;
102 end if;
103 --
104 open c_get_last_period_end_date(P_OVERTIME_PAYROLL_ID);
105 fetch c_get_last_period_end_date into l_max_ot_period;
106 close c_get_last_period_end_date;
107 --
108 if l_period_end is not null then
109 l_max_ot_period := least(l_period_end,l_max_ot_period);
110 end if;
111 --
112 open c_get_period_date(P_START_PY_PERIOD_ID);
113 fetch c_get_period_date into l_min_py_period,l_dummy;
114 close c_get_period_date;
115 --
116 l_period_end := null;
117 if P_END_PY_PERIOD_ID is not null then
118 open c_get_period_date(P_END_PY_PERIOD_ID);
119 fetch c_get_period_date into l_dummy,l_period_end;
120 close c_get_period_date;
121 end if;
122 --
123 open c_get_last_period_end_date(P_PAYROLL_ID);
124 fetch c_get_last_period_end_date into l_max_py_period;
125 close c_get_last_period_end_date;
126 --
127 if l_period_end is not null then
128 l_max_py_period := least(l_period_end,l_max_py_period);
129 end if;
130 --
131 /* Determine the number of components in the pattern */
132 --
133 l_pattern_components := LENGTH(P_pattern);
134 --
135 /* Load the pattern into local array */
136 --
137 FOR x in 1..l_pattern_components LOOP
138 if SUBSTR(P_pattern,x,1) between '1' and '6' then
139 PERIOD(x) := to_number(SUBSTR(P_pattern,x,1));
140 else
141 fnd_message.set_name('PAY','PAY_74951_INVALID_PATTERN');
142 fnd_message.raise_error;
143 end if;
144 END LOOP;
145 --
146 /* Initialise the payroll period counter */
147 --
148 l_py_period_counter := 1;
149 --
150 /* C_PAYROLL_PERIODS will select all the payroll periods in the
151 payroll calendar starting with the one defined by
152 P_START_PY_PERIOD_ID, ordered by PERIOD_START_DATE */
153 --
154 open C_PAYROLL_PERIODS;
155 fetch C_PAYROLL_PERIODS into l_py_period_id,l_py_period_end_date;
156 --
157 /* Initialise the Overtime Period Counter and get the number of
158 overtime weeks for the payroll period */
159 --
160 l_ot_period_counter := 1;
161 l_ot_weeks := PERIOD(l_py_period_counter);
162 --
163 /* C_OVERTIME_PERIODS will select all the overtime periods in the
164 overtime calendar starting with the one defined by
165 P_START_OT_PERIOD_ID, ordered by PERIOD_START_DATE */
166 --
167 For o in C_OVERTIME_PERIODS loop
168 --
169 /* Ensure that the overtime week end date is on or before the payroll period
170 end date */
171 --
172 IF o.end_date > l_py_period_end_date then
173 fnd_message.set_name('PAY','PAY_74950_OT_AFTER_PAYROLL');
174 fnd_message.raise_error;
175 END IF;
176 --
177 --
178 /* Update the overtime period record to record the payroll period in which
179 it will be paid */
180 --
181 UPDATE PER_TIME_PERIODS
182 SET PRD_INFORMATION_CATEGORY = 'FR',
183 PRD_INFORMATION1 = to_char(p_payroll_id),
184 PRD_INFORMATION2 = to_char(l_py_period_id)
185 where TIME_PERIOD_ID = o.TIME_PERIOD_ID
186 and (P_OVERRIDE = 'Y' or
187 (nvl(P_OVERRIDE,'N') <> 'Y' and PRD_INFORMATION1 is null));
188 --
189 /* Increment the counters - if the counters have reached the end of the
190 number of weeks in the pattern then reset the weeks counter, get the next
191 payroll period and increment the period counter, otherwise just
192 increment the weeks counter */
193 --
194 IF l_OT_PERIOD_COUNTER = l_OT_WEEKS THEN
195 fetch C_PAYROLL_PERIODS into l_py_period_id,l_py_period_end_date;
196 if C_PAYROLL_PERIODS%notfound then
197 exit;
198 end if;
199 l_PY_PERIOD_COUNTER :=
200 CEIL(MOD(l_PY_PERIOD_COUNTER,l_PATTERN_COMPONENTS)) + 1;
201 l_OT_WEEKS := PERIOD(l_PY_PERIOD_COUNTER);
202 l_OT_PERIOD_COUNTER := 1;
203 ELSE
204 l_OT_PERIOD_COUNTER := l_OT_PERIOD_COUNTER + 1;
205 END IF;
206 --
207 END LOOP; /* End of Overtime weeks loop */
208 --
209 --
210 close C_PAYROLL_PERIODS;
211 --
212 EXCEPTION WHEN OTHERS THEN
213 errbuf := sqlerrm;
214 end generate;
215 --
216 end pay_fr_overtime_mapping;
[Home] [Help]