1 PACKAGE BODY hr_jpdrb AS
2 /* $Header: pyjpdrb.pkb 115.7 2004/05/14 02:49:35 keyazawa ship $ */
3 /* ------------------------------------------------------------------------------------ */
4 --
5 /* ------------------------------------------------------------------------------------
6 --
7 -- FUNCTION get_balance
8 --
9 -- ------------------------------------------------------------------------------------ */
10 /* -- find out whether the balance exists in the latest balances table,
11 -- using get_latest_balance.
12 -- Check expiry of the balance if assignment action lower , using functionality
13 -- of expiry chk from pyjpbal.
14 -- If we do need the route code, the balance type id and dimension is going to have
15 -- to be known. Then, hr_jprte and hr_routes can be called accordingly. This can be acheived
16 -- using a get_route function (here) that performs necessary calculation
17 -- Person level balances - calls hr_jpbal, as there is a lot of calculation before hitting route. */
18 --
19 /* ------------------------------------------------------------------------------------
20 --
21 -- FUNCTION get_balance
22 -- Date Mode
23 --
24 -- The theory behind this is that we can check whether the balance has
25 -- expired before getting it, due to the fact that we navigate back to the
26 -- last assignment action and use the effective (session) date passed in
27 -- as criteria for expiry checking
28 --
29 -- ------------------------------------------------------------------------------------ */
30 FUNCTION get_balance (p_assignment_id IN NUMBER,
31 p_defined_balance_id IN NUMBER,
32 p_effective_date IN DATE)
33 RETURN NUMBER IS
34 --
35 l_assignment_action_id NUMBER;
36 l_dimension_jp_type VARCHAR2(15);
37 l_balance NUMBER;
38 l_expired BOOLEAN;
39 --
40 /* -- This gets the most recent assignment action of seq generating type, using
41 -- the effective date and assignment ID passed in */
42 --
43 cursor get_latest_id_for_effect
44 is
45 SELECT TO_NUMBER(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16))
46 FROM pay_payroll_actions ppa,
47 pay_assignment_actions paa
48 WHERE paa.assignment_id = p_assignment_id
49 AND ppa.payroll_action_id = paa.payroll_action_id
50 AND ppa.effective_date <= p_effective_date
51 AND ppa.action_type in ('R', 'Q', 'I', 'V', 'B');
52 --
53 cursor get_latest_id_for_earned
54 is
55 SELECT TO_NUMBER(substr(max(lpad(ASSACT.action_sequence,15,'0')||ASSACT.assignment_action_id),16))
56 FROM pay_payroll_actions PACT,
57 pay_assignment_actions ASSACT
58 WHERE ASSACT.assignment_id = p_assignment_id
59 AND PACT.payroll_action_id = ASSACT.payroll_action_id
60 AND PACT.date_earned <= p_effective_date
61 AND PACT.action_type in ('R', 'Q', 'I', 'V', 'B');
62 --
63 cursor balance_dimension
64 is
65 SELECT RTRIM(SUBSTRB(DIM.dimension_name,31,15),' ')
66 FROM pay_balance_dimensions DIM,
67 pay_defined_balances DB
68 WHERE DB.defined_balance_id = p_defined_balance_id
69 AND DIM.balance_dimension_id = DB.balance_dimension_id;
70 --
71 BEGIN
72 --
73 open balance_dimension;
74 fetch balance_dimension into l_dimension_jp_type;
75 close balance_dimension;
76 --
77 if l_dimension_jp_type = 'DATE_EARNED' then
78 --
79 open get_latest_id_for_earned;
80 fetch get_latest_id_for_earned into l_assignment_action_id;
81 close get_latest_id_for_earned;
82 --
83 else --l_dimension_jp_type = 'EFFECTIVE_DATE' or core dimension(l_dimension_jp_type is null)
84 --
85 open get_latest_id_for_effect;
86 fetch get_latest_id_for_effect into l_assignment_action_id;
87 close get_latest_id_for_effect;
88 --
89 end if;
90 --
91 if l_assignment_action_id is null then
92 l_balance := 0;
93 else
94 /* --Check expiry even before getting the VALUE, according to effective date */
95 l_expired := balance_expired(
96 l_assignment_action_id,
97 p_defined_balance_id,
98 null,
99 p_effective_date,
100 null);
101 if l_expired = TRUE then
102 l_balance := 0;
103 else
104 /* --get the balance value using the latest assignment action */
105 l_balance := get_balance(
106 p_assignment_action_id => l_assignment_action_id,
107 p_defined_balance_id => p_defined_balance_id);
108 end if;
109 end if;
110 --
111 RETURN l_balance;
112 --
113 END get_balance;
114 /* ------------------------------------------------------------------------------------
115 --
116 -- FUNCTION get_balance
117 -- Assignment Action Mode
118 --
119 -- The theory behind this is that we can check whether the balance has
120 -- expired before getting it, due to the fact that we navigate back to the
121 -- last assignment action and use the assigment action id
122 -- as criteria for expiry checking
123 --
124 -- ------------------------------------------------------------------------------------ */
125 FUNCTION get_balance (p_assignment_action_id IN NUMBER,
126 p_defined_balance_id IN NUMBER)
127 RETURN NUMBER IS
128 --
129 l_balance NUMBER;
130 --
131 BEGIN
132 --
133 l_balance := pay_balance_pkg.get_value(
134 p_assignment_action_id => p_assignment_action_id,
135 p_defined_balance_id => p_defined_balance_id);
136 --
137 RETURN l_balance;
138 END get_balance;
139 --
140 /* ------------------------------------------------------------------------------------
141 --
142 -- FUNCTION balance_expired
143 --
144 -- This function checks the expiry of an action's value ,
145 -- depending on which dimension type the value is for
146 --
147 -- ------------------------------------------------------------------------------------ */
148 FUNCTION balance_expired (p_assignment_action_id IN NUMBER,
149 p_defined_balance_id IN NUMBER,
150 p_dimension_name IN VARCHAR2,
151 p_effective_date IN DATE,
152 p_action_effective_date IN DATE)
153 --
154 RETURN BOOLEAN IS
155 --
156 /* --Check the expiry of an action depending on the defined balance's dimension
157 --type. */
158 --
159 l_dimension_name VARCHAR2(80);
160 l_dimension_jp_type VARCHAR2(15);
161 l_expired BOOLEAN;
162 l_return_date DATE;
163 l_business_group_id NUMBER(15);
164 l_date_earned DATE;
165 l_frequency NUMBER;
166 l_start_dd_mm VARCHAR2(6);
167 --
168 cursor get_dimension_type(c_defined_balance_id IN NUMBER)
169 IS
170 SELECT pbd.dimension_name
171 FROM pay_balance_dimensions pbd,
172 pay_defined_balances pdb
173 WHERE pdb.defined_balance_id = c_defined_balance_id
174 AND pbd.balance_dimension_id = pdb.balance_dimension_id;
175 --
176 cursor csr_business_group
177 is
178 SELECT PACT.business_group_id,
179 PACT.date_earned
180 FROM pay_payroll_actions PACT,
181 pay_assignment_actions ASSACT
182 WHERE ASSACT.assignment_action_id = p_assignment_action_id
183 AND PACT.payroll_action_id = ASSACT.payroll_action_id;
184 --
185 BEGIN
186 -- To solve gscc error
187 l_expired := FALSE;
188 --
189 open csr_business_group;
190 fetch csr_business_group into l_business_group_id, l_date_earned;
191 close csr_business_group;
192 --
193 if p_dimension_name is null then --date mode call
194 open get_dimension_type(p_defined_balance_id);
195 fetch get_dimension_type into l_dimension_name;
196 close get_dimension_type;
197 --
198 else
199 l_dimension_name := p_dimension_name;
200 end if;
201 --
202 l_dimension_jp_type := RTRIM( SUBSTRB(l_dimension_name,31,15),' ');
203 --
204 if l_dimension_name = hr_jprts.g_asg_run then
205 /* --run balance, so call period expiry */
206 if p_effective_date > expired_period_date(p_assignment_action_id) then
207 l_expired := TRUE;
208 end if;
209 elsif l_dimension_name = hr_jprts.g_asg_proc_ptd then
210 /* --period balance, so call period expiry */
211 if p_effective_date > expired_period_date(p_assignment_action_id) then
212 l_expired := TRUE;
213 end if;
214 --
215 elsif l_dimension_name = hr_jprts.g_asg_fytd_jp THEN
216 /* -- Do not add the function add_months for l_return_date.
217 -- Because add_months(1999/02/28,12) => 2000/02/29(actual next term date is 2000/02/28)
218 --
219 -- Ignore p_action_effective_date because it is not unique to get date_earned
220 -- while using effective_date (not action id). Always use action id at that time. */
221 l_return_date := hr_jprts.dimension_reset_date_userdef(
222 l_dimension_name,
223 add_months(l_date_earned,12),
224 'FLEX',
225 null,
226 l_business_group_id);
227 /* -- it's wrong because return date must be next term date.
228 --l_return_date := hr_jprts.dimension_reset_date_userdef(
229 -- l_dimension_name,
230 -- l_date_earned,
231 -- 'FLEX',
232 -- null,
233 -- l_business_group_id);
234 -- if calling function is Date Mode, p_effective_date is session_date.
235 -- l_next_start_date = l_return_date */
236 if p_effective_date >= l_return_date
237 or l_return_date is null then
238 l_expired := TRUE;
239 end if;
240 --
241 /* -- Actually, this function is not used so that hr_routes.retro_jp does not exist. */
242 elsif l_dimension_name = hr_jprts.g_retro then
243 if p_effective_date > expired_period_date(p_assignment_action_id) then
244 l_expired := TRUE;
245 end if;
246 elsif l_dimension_name = hr_jprts.g_payment then
247 if p_effective_date > expired_period_date(p_assignment_action_id) then
248 l_expired := TRUE;
249 end if;
250 /* -- the following treatment does not include ITD. */
251 else
252 IF l_dimension_jp_type = 'EFFECTIVE_DATE' then
253 l_return_date := hr_jprts.dimension_reset_last_date(
254 l_dimension_name,
255 nvl(p_action_effective_date,(get_action_date(p_assignment_action_id)))) + 1;
256 --
257 if p_effective_date >= l_return_date
258 or l_return_date is null then
259 l_expired := TRUE;
260 end if;
261 ELSIF l_dimension_jp_type = 'DATE_EARNED' then
262 l_return_date := hr_jprts.dimension_reset_last_date(l_dimension_name,l_date_earned) + 1;
263 --
264 -- if calling function is Date Mode, p_effective_date is session_date. */
265 if p_effective_date >= l_return_date
266 or l_return_date is null then
267 l_expired := TRUE;
268 end if;
269 END IF;
270 end if;
271 --
272 RETURN l_expired;
273 --
274 END balance_expired;
275 --
276 /* ------------------------------------------------------------------------------------
277 --
278 -- FUNCTION expired_period_date
279 --
280 -- This function returns the expiry of an action's time period
281 --
282 -- ------------------------------------------------------------------------------------ */
283 FUNCTION expired_period_date(p_assignment_action_id IN NUMBER)
284 --
285 RETURN DATE IS
286 --
287 l_end_date DATE;
288 --
289 cursor expired_time_period(c_assignment_action_id IN NUMBER)
290 is
291 SELECT ptp.end_date
292 FROM per_time_periods ptp,
293 pay_payroll_actions ppa,
294 pay_assignment_actions paa
295 WHERE paa.assignment_action_id = c_assignment_action_id
296 AND ppa.payroll_action_id = paa.payroll_action_id
297 AND ptp.time_period_id = ppa.time_period_id;
298 --
299 BEGIN
300 --
301 open expired_time_period(p_assignment_action_id);
302 fetch expired_time_period into l_end_date;
303 close expired_time_period;
304 --
305 RETURN l_end_date;
306 --
307 END expired_period_date;
308 --
309 /* ------------------------------------------------------------------------------------
310 -- FUNCTION get_action_date
311 -- This function gets the effective date of an assignment action
312 -- ------------------------------------------------------------------------------------ */
313 FUNCTION get_action_date(p_assignment_action_id IN NUMBER)
314 RETURN DATE IS
315 --
316 l_effective_date date;
317 --
318 cursor c_bal_date
319 is
320 SELECT ppa.effective_date
321 FROM pay_payroll_actions ppa,
322 pay_assignment_actions paa
323 WHERE paa.assignment_action_id = p_assignment_action_id
324 AND paa.payroll_action_id = ppa.payroll_action_id;
325 --
326 BEGIN
327 --
328 open c_bal_date;
329 fetch c_bal_date into l_effective_date;
330 if c_bal_date%NOTFOUND then
331 /* --raise_application_error(-20000,'This assignment action is invalid');
332 --cant use as violates pragma wnds, so set date to null */
333 l_effective_date := null;
334 end if;
335 close c_bal_date;
336 --
337 RETURN l_effective_date;
338 END get_action_date;
339 --
340 END hr_jpdrb;
[Home] [Help]