[Home] [Help]
PACKAGE BODY: APPS.PAY_CORE_DATES
Source
1 package body pay_core_dates as
2 /* $Header: pycordat.pkb 120.4.12020000.3 2012/07/06 09:38:31 vmaripal ship $ */
3 --
4 --
5 -- Set up the globals
6 --
7 g_debug boolean := hr_utility.debug_enabled;
8 --
9 procedure get_offset_date(p_period_type in varchar2,
10 p_period_units in number,
11 p_effective_date in date,
12 p_offset_date out nocopy date)
13 is
14 begin
15 --
16 /* do we need to alter the date, only if the
17 number of offset periods in question is not
18 zero
19 */
20 if (p_period_units = 0) then
21 --
22 p_offset_date := p_effective_date;
23 --
24 else
25 --
26 if (p_period_type = 'YEAR') then
27 --
28 p_offset_date := add_months(p_effective_date, (12 * p_period_units));
29 --
30 elsif (p_period_type = 'QUARTER') then
31 --
32 p_offset_date := add_months(p_effective_date, (3 * p_period_units));
33 --
34 elsif (p_period_type = 'MONTH') then
35 --
36 p_offset_date := add_months(p_effective_date, (1 * p_period_units));
37 --
38 else
39 --
40 p_offset_date := p_effective_date;
41 --
42 end if;
43 end if;
44 --
45 end get_offset_date;
46 --
47 procedure perform_day_adjustment(p_day_adjustment in varchar2,
48 p_effective_date in out nocopy date
49 )
50 is
51 l_day_adjustment_value number;
52 begin
53 --
54 if p_day_adjustment = 'NEXT' then
55 l_day_adjustment_value := 1;
56 elsif p_day_adjustment = 'PRIOR' then
57 l_day_adjustment_value := -1;
58 elsif p_day_adjustment = 'CURRENT' then
59 l_day_adjustment_value := 0;
60 else
61 begin
62 l_day_adjustment_value := to_number(p_day_adjustment);
63 exception
64 when others then
65 hr_utility.set_message(801,'HR_51153_INVAL_NUM_FORMAT');
66 hr_utility.raise_error;
67 end;
68 end if;
69 p_effective_date := p_effective_date + l_day_adjustment_value;
70
71 --
72 end perform_day_adjustment;
73 --
74 /* Bug 13537723, added p_payroll_id and p_asg_action */
75 procedure get_time_definition_date(p_time_def_id in number,
76 p_effective_date in date,
77 p_start_date out nocopy date,
78 p_bus_grp in number default null,
79 p_payroll_id in number default null,
80 p_asg_action in number default null)
81
82 is
83 l_return_date date;
84 --
85 l_period_type pay_time_definitions.period_type%type;
86 l_period_unit pay_time_definitions.period_unit%type;
87 l_day_adjustment pay_time_definitions.day_adjustment%type;
88 l_period_date date;
89 /* Bug 13537723, code changes start */
90 l_dynamic_code pay_time_definitions.dynamic_code%type;
91 l_statem varchar2(2000); -- used with dynamic pl/sql
92 sql_cursor integer;
93 l_rows integer;
94 /* Bug 13537723, code changes end */
95 --
96 begin
97 g_debug := hr_utility.debug_enabled;
98 --
99 /* Bug 13537723, added dynamic_code */
100 select period_type,
101 nvl(period_unit, 0),
102 nvl(day_adjustment,0),
103 dynamic_code
104 into l_period_type,
105 l_period_unit,
106 l_day_adjustment,
107 l_dynamic_code
108 from pay_time_definitions
109 where time_definition_id = p_time_def_id;
110 --
111 --
112 get_offset_date(l_period_type,
113 l_period_unit,
114 p_effective_date,
115 l_period_date);
116 --
117 l_return_date := null;
118 --
119 if (l_period_type = 'YEAR') then
120 --
121 l_return_date := trunc(l_period_date, 'Y');
122 --
123 elsif (l_period_type = 'QUARTER') then
124 --
125 l_return_date := trunc(l_period_date, 'Q');
126 --
127 elsif (l_period_type = 'START_OF_TIME') then
128 --
129 l_return_date := to_date('0001/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS');
130 --
131 elsif (l_period_type = 'END_OF_TIME') then
132 --
133 l_return_date := to_date('4712/12/31 00:00:00', 'YYYY/MM/DD HH24:MI:SS');
134 --
135 elsif (l_period_type = 'MONTH') then
136 --
137 l_return_date := trunc(l_period_date, 'MM');
138 --
139 elsif (l_period_type = 'RUN') then
140 --
141 l_return_date := l_period_date;
142 --
143 elsif (l_period_type = 'TYEAR') then
144 --
145 l_return_date:= pay_ip_route_support.tax_year(p_bus_grp,
146 l_period_date);
147 --
148 elsif (l_period_type = 'TQUARTER') then
149 --
150 l_return_date:= pay_ip_route_support.tax_quarter(p_bus_grp,
151 l_period_date);
152 --
153 elsif (l_period_type = 'FYEAR') then
154 --
155 l_return_date:= pay_ip_route_support.fiscal_year(p_bus_grp,
156 l_period_date);
157 --
158 elsif (l_period_type = 'FQUARTER') then
159 --
160 l_return_date:= pay_ip_route_support.fiscal_quarter(p_bus_grp,
161 l_period_date);
162 --
163 elsif (l_period_type = 'DAILY') then
164 --
165 l_return_date := l_period_date;
166 /* Bug 13537723, code changes start */
167 elsif (l_period_type = 'DYNAMIC') then
168
169 l_statem := 'begin ' || l_dynamic_code || '(';
170 l_statem := l_statem||' p_effective_date => :l_eff_date, ';
171 l_statem := l_statem||' p_start_date => :l_start_date, ';
172 l_statem := l_statem||' p_bus_grp => :l_bus_grp, ';
173 l_statem := l_statem||' p_payroll_id => :l_payroll_id, ';
174 l_statem := l_statem||' p_asg_action => :l_asg_action); end; ';
175 --
176 sql_cursor := dbms_sql.open_cursor;
177 --
178 dbms_sql.parse(sql_cursor, l_statem, dbms_sql.v7);
179 --
180 --
181 dbms_sql.bind_variable(sql_cursor, 'l_eff_date', p_effective_date);
182 dbms_sql.bind_variable(sql_cursor, 'l_start_date', p_start_date);
183 dbms_sql.bind_variable(sql_cursor, 'l_bus_grp', p_bus_grp);
184 dbms_sql.bind_variable(sql_cursor, 'l_payroll_id', p_payroll_id);
185 dbms_sql.bind_variable(sql_cursor, 'l_asg_action', p_asg_action);
186 --
187 l_rows := dbms_sql.execute(sql_cursor);
188 --
189 if (l_rows = 1) then
190 dbms_sql.variable_value(sql_cursor, 'l_start_date',
191 l_return_date);
192 dbms_sql.close_cursor(sql_cursor);
193 --
194 else
195 l_return_date := to_date('0001/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS');
196 dbms_sql.close_cursor(sql_cursor);
197 end if;
198 /* Bug 13537723, code changes end */
199 end if;
200 --
201 perform_day_adjustment(l_day_adjustment,
202 l_return_date
203 );
204
205 p_start_date := l_return_date;
206 --
207 end get_time_definition_date;
208 --
209 function get_time_definition_date(p_time_def_id in number,
210 p_effective_date in date,
211 p_bus_grp in number default null)
212 return date
213 is
214 l_start_date date;
215 begin
216 --
217 get_time_definition_date(p_time_def_id => p_time_def_id,
218 p_effective_date => p_effective_date,
219 p_start_date => l_start_date,
220 p_bus_grp => p_bus_grp);
221 --
222 return l_start_date;
223 --
224 end get_time_definition_date;
225 --
226 procedure is_date_in_span(p_start_time_def_id in number,
227 p_end_time_def_id in number,
228 p_test_date in date,
229 p_effective_date in date,
230 p_result out nocopy boolean,
231 p_bus_grp in number default null
232 )
233 is
234 l_start_date date;
235 l_end_date date;
236 begin
237 --
238 get_time_definition_date(p_time_def_id => p_start_time_def_id,
239 p_effective_date => p_effective_date,
240 p_start_date => l_start_date,
241 p_bus_grp => p_bus_grp);
242 --
243 get_time_definition_date(p_time_def_id => p_end_time_def_id,
244 p_effective_date => p_effective_date,
245 p_start_date => l_end_date,
246 p_bus_grp => p_bus_grp);
247 --
248 if (p_test_date between l_start_date and l_end_date) then
249 p_result := TRUE;
250 else
251 p_result := FALSE;
252 end if;
253 --
254 end is_date_in_span;
255 --
256 /* Bug 13537723, code changes */
257 function is_date_in_span(p_start_time_def_id in number,
258 p_end_time_def_id in number,
259 p_test_date in date,
260 p_effective_date in date,
261 p_bus_grp in number default null,
262 p_payroll_id in number default null,
263 p_asg_action in number default null
264 )
265 return varchar2
266 is
267 l_start_date date;
268 l_end_date date;
269 begin
270 --
271 get_time_definition_date(p_time_def_id => p_start_time_def_id,
272 p_effective_date => p_effective_date,
273 p_start_date => l_start_date,
274 p_bus_grp => p_bus_grp,
275 p_payroll_id => p_payroll_id,
276 p_asg_action => p_asg_action);
277 --
278 get_time_definition_date(p_time_def_id => p_end_time_def_id,
279 p_effective_date => p_effective_date,
280 p_start_date => l_end_date,
281 p_bus_grp => p_bus_grp,
282 p_payroll_id => p_payroll_id,
283 p_asg_action => p_asg_action);
284 --
285 if (p_test_date between l_start_date and l_end_date) then
286 return 'Y';
287 else
288 return 'N';
289 end if;
290 --
291 end is_date_in_span;
292 --
293 end pay_core_dates;