[Home] [Help]
PACKAGE BODY: APPS.PAY_BALANCE_FEEDS_F_PKG
Source
1 PACKAGE BODY PAY_BALANCE_FEEDS_F_PKG as
2 /* $Header: pyblf01t.pkb 120.0 2005/05/29 03:19 appldev noship $ */
3 --
4 /*==========================================================================+
5 | Copyright (c) 1993 Oracle Corporation |
6 | Redwood Shores, California, USA |
7 | All rights reserved. |
8 +===========================================================================+
9 Name
10 pay_balance_feeds_f_pkg
11 Purpose
12 Used by PAYWSDBT (Define Balance Type) for the balance feeds block (BLF).
13 Notes
14
15 History
16 01-Mar-94 J.S.Hobbs 40.0 Date created.
17 20-Apr-94 J.S.Hobbs 40.1 Added rtrim to Lock_Row.
18 16-Jan-95 N Simpson 40.2 Added code to handle AOL Who columns
19 as per set 8 Changes 40.0
20 01-Feb-95 J.S.Hobbs 40.4 Removed aol WHO columns.
21 24-Apr-95 J.S.Hobbs 40.5 Added extra validation to stop the
22 mixing of manual and automatic balance
23 feeds (via classifications).
24 19-Jul-95 D.Kerr 40.6 Changes to support initial balance
25 upload.
26 02-Oct-95 D.Kerr 40.7 310643 : overloads for insert_row,
27 update_row and delete_row
28 05-Mar-97 J.Alloun 40.8 Changed all occurances of system.dual
29 to sys.dual for next release requirements.
30 08-Dec-03 T.Habara 115.1 Bug 3285363. Modified cursor C3 in
31 insert_row().
32 Added commit, dbdrv, whenever oserror.
33 Added nocopy changes.
34 26-APR-04 A.Logue 115.2 Performance fix to C1 in
35 check_run_result_usage.
36 ============================================================================*/
37 -----------------------------------------------------------------------------
38 -- Name --
39 -- Insert_Row --
40 -- Purpose --
41 -- Table handler procedure that supports the insert of a balance feed --
42 -- via the Define Balance Type form. --
43 -- Arguments --
44 -- See below. --
45 -- Notes --
46 -----------------------------------------------------------------------------
47 --
48 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
49 X_Balance_Feed_Id IN OUT NOCOPY NUMBER,
50 X_Effective_Start_Date DATE,
51 X_Effective_End_Date DATE,
52 X_Business_Group_Id NUMBER,
53 X_Legislation_Code VARCHAR2,
54 X_Balance_Type_Id NUMBER,
55 X_Input_Value_Id NUMBER,
56 X_Scale NUMBER,
57 X_Legislation_Subgroup VARCHAR2 ) IS
58 BEGIN
59 Insert_Row(X_Rowid => X_Rowid,
60 X_Balance_Feed_Id => X_Balance_Feed_Id,
61 X_Effective_Start_Date => X_Effective_Start_Date,
62 X_Effective_End_Date => X_Effective_End_Date,
63 X_Business_Group_Id => X_Business_Group_Id,
64 X_Legislation_Code => X_Legislation_Code,
65 X_Balance_Type_Id => X_Balance_Type_Id ,
66 X_Input_Value_Id => X_Input_Value_Id,
67 X_Scale => X_Scale,
68 X_Legislation_Subgroup => X_Legislation_Subgroup ,
69 X_Initial_Balance_Feed => FALSE ) ;
70 END Insert_Row;
71 --
72 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
73 X_Balance_Feed_Id IN OUT NOCOPY NUMBER,
74 X_Effective_Start_Date DATE,
75 X_Effective_End_Date DATE,
76 X_Business_Group_Id NUMBER,
77 X_Legislation_Code VARCHAR2,
78 X_Balance_Type_Id NUMBER,
79 X_Input_Value_Id NUMBER,
80 X_Scale NUMBER,
81 X_Legislation_Subgroup VARCHAR2,
82 X_Initial_Balance_Feed BOOLEAN ) IS
83 --
84 l_balance_feed_id number:= X_Balance_Feed_Id;
85 CURSOR C IS SELECT rowid FROM pay_balance_feeds_f
86 WHERE balance_feed_id = l_balance_feed_id;
87 --
88 CURSOR C2 IS SELECT pay_balance_feeds_s.nextval FROM sys.dual;
89 --
90 -- Bug 3285363.
91 -- Modified cursor C3 to select only those initial feed elements
92 -- that are available in the business group/legislation.
93 --
94 l_legislation_code varchar2(30)
95 := nvl(X_Legislation_Code
96 ,hr_api.return_legislation_code(X_Business_Group_Id));
97 --
98 CURSOR C3 IS SELECT 'Y'
99 FROM pay_balance_feeds_f blf,
100 pay_input_values_f inv,
101 pay_element_types_f elt,
102 pay_element_classifications ec
103 WHERE blf.balance_type_id = X_Balance_Type_Id
104 AND blf.input_value_id = inv.input_value_id
105 AND inv.element_type_id = elt.element_type_id
106 AND nvl(elt.legislation_code
107 ,nvl(l_legislation_code, '~nvl~'))
108 = nvl(l_legislation_code, '~nvl~')
109 AND nvl(elt.business_group_id
110 ,nvl(X_Business_Group_Id, -1))
111 = nvl(X_Business_Group_Id, -1)
112 AND elt.classification_id = ec.classification_id
113 AND ec.balance_initialization_flag = 'Y';
114
115 --
116 l_found_initial_balance_feed varchar2(1) := 'N' ; -- Is there another init.
117 -- bal. feed for this
118 -- element ?
119 BEGIN
120 --
121 -- Lock balance type to stop other users changing the balance feed.
122 --
123 hr_balance_feeds.lock_balance_type(X_Balance_Type_Id);
124 --
125 --
126 --
127 -- Make sure that balance is not fed by classifications which would make the
128 -- balance feeds READ-ONLY and therefore this operation invalid.
129 --
130 if ( not X_Initial_Balance_Feed )
131 then
132 --
133 if ( hr_balance_feeds.bal_classifications_exist(X_Balance_Type_Id))
134 then
135 hr_utility.set_message(801, 'HR_7444_BAL_FEED_READ_ONLY');
136 hr_utility.raise_error;
137 end if;
138 --
139 else
140 --
141 --
142 -- Make sure that there is only a single initial balance feed
143 --
144 OPEN C3 ;
145 FETCH C3 into l_found_initial_balance_feed ;
146 CLOSE C3 ;
147 --
148 if ( l_found_initial_balance_feed = 'Y' ) then
149 hr_utility.set_message(801,'HR_7875_BAL_FEED_HAS_INIT_FEED');
150 hr_utility.raise_error;
151 end if;
152 --
153 --
154 -- Make sure that there are no processed run results for this input
155 -- value.
156 --
157 check_run_result_usage( X_Input_Value_Id ) ;
158 --
159 end if;
160 --
161 --
162 if (l_balance_feed_id is NULL) then
163 OPEN C2;
164 FETCH C2 INTO l_balance_feed_id;
165 CLOSE C2;
166 end if;
167 --
168 INSERT INTO pay_balance_feeds_f
169 (balance_feed_id,
170 effective_start_date,
171 effective_end_date,
172 business_group_id,
173 legislation_code,
174 balance_type_id,
175 input_value_id,
176 scale,
177 legislation_subgroup)
178 VALUES
179 (l_balance_feed_id,
180 X_Effective_Start_Date,
181 X_Effective_End_Date,
182 X_Business_Group_Id,
183 X_Legislation_Code,
184 X_Balance_Type_Id,
185 X_Input_Value_Id,
186 X_Scale,
187 X_Legislation_Subgroup);
188 --
189 OPEN C;
190 FETCH C INTO X_Rowid;
191 if (C%NOTFOUND) then
192 CLOSE C;
193 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
194 hr_utility.set_message_token('PROCEDURE',
195 'pay_balance_feeds_f_pkg.insert_row');
196 hr_utility.set_message_token('STEP','1');
197 hr_utility.raise_error;
198 end if;
199 CLOSE C;
200 X_Balance_Feed_Id := l_balance_feed_id;
201 --
202 END Insert_Row;
203 --
204 -----------------------------------------------------------------------------
205 -- Name --
206 -- Lock_Row --
207 -- Purpose --
208 -- Table handler procedure that supports the insert , update and delete --
209 -- of a balance feed by applying a lock on a balance feed in the Define --
210 -- Balance Type form. --
211 -- Arguments --
212 -- See below. --
213 -- Notes --
214 -----------------------------------------------------------------------------
215 --
216 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
217 X_Balance_Feed_Id NUMBER,
218 X_Effective_Start_Date DATE,
219 X_Effective_End_Date DATE,
220 X_Business_Group_Id NUMBER,
221 X_Legislation_Code VARCHAR2,
222 X_Balance_Type_Id NUMBER,
223 X_Input_Value_Id NUMBER,
224 X_Scale NUMBER,
225 X_Legislation_Subgroup VARCHAR2) IS
226 --
227 CURSOR C IS SELECT * FROM pay_balance_feeds_f
228 WHERE rowid = X_Rowid FOR UPDATE of Balance_Feed_Id NOWAIT;
229 --
230 Recinfo C%ROWTYPE;
231 --
232 BEGIN
233 --
234 OPEN C;
235 FETCH C INTO Recinfo;
236 if (C%NOTFOUND) then
237 CLOSE C;
238 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
239 hr_utility.set_message_token('PROCEDURE',
240 'pay_balance_feeds_f_pkg.lock_row');
241 hr_utility.set_message_token('STEP','1');
242 hr_utility.raise_error;
243 end if;
244 CLOSE C;
245 --
246 -- Remove trailing spaces.
247 --
248 Recinfo.legislation_code := rtrim(Recinfo.legislation_code);
249 Recinfo.legislation_subgroup := rtrim(Recinfo.legislation_subgroup);
250 --
251 if ( ( (Recinfo.balance_feed_id = X_Balance_Feed_Id)
252 OR ( (Recinfo.balance_feed_id IS NULL)
253 AND (X_Balance_Feed_Id IS NULL)))
254 AND ( (Recinfo.effective_start_date = X_Effective_Start_Date)
255 OR ( (Recinfo.effective_start_date IS NULL)
256 AND (X_Effective_Start_Date IS NULL)))
257 AND ( (Recinfo.effective_end_date = X_Effective_End_Date)
258 OR ( (Recinfo.effective_end_date IS NULL)
259 AND (X_Effective_End_Date IS NULL)))
260 AND ( (Recinfo.business_group_id = X_Business_Group_Id)
261 OR ( (Recinfo.business_group_id IS NULL)
262 AND (X_Business_Group_Id IS NULL)))
263 AND ( (Recinfo.legislation_code = X_Legislation_Code)
264 OR ( (Recinfo.legislation_code IS NULL)
265 AND (X_Legislation_Code IS NULL)))
266 AND ( (Recinfo.balance_type_id = X_Balance_Type_Id)
267 OR ( (Recinfo.balance_type_id IS NULL)
268 AND (X_Balance_Type_Id IS NULL)))
269 AND ( (Recinfo.input_value_id = X_Input_Value_Id)
270 OR ( (Recinfo.input_value_id IS NULL)
271 AND (X_Input_Value_Id IS NULL)))
272 AND ( (Recinfo.scale = X_Scale)
273 OR ( (Recinfo.scale IS NULL)
274 AND (X_Scale IS NULL)))
275 AND ( (Recinfo.legislation_subgroup = X_Legislation_Subgroup)
276 OR ( (Recinfo.legislation_subgroup IS NULL)
277 AND (X_Legislation_Subgroup IS NULL)))
278 ) then
279 return;
280 else
281 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
282 APP_EXCEPTION.RAISE_EXCEPTION;
283 end if;
284 --
285 END Lock_Row;
286 --
287 -----------------------------------------------------------------------------
288 -- Name --
289 -- Update_Row --
290 -- Purpose --
291 -- Table handler procedure that supports the update of a balance feed --
292 -- via the Define Balance Type form. --
293 -- Arguments --
294 -- See below. --
295 -- Notes --
296 -----------------------------------------------------------------------------
297 --
298 PROCEDURE Update_Row(X_Rowid VARCHAR2,
299 X_Balance_Feed_Id NUMBER,
300 X_Effective_Start_Date DATE,
301 X_Effective_End_Date DATE,
302 X_Business_Group_Id NUMBER,
303 X_Legislation_Code VARCHAR2,
304 X_Balance_Type_Id NUMBER,
305 X_Input_Value_Id NUMBER,
306 X_Scale NUMBER,
307 X_Legislation_Subgroup VARCHAR2 ) IS
308 BEGIN
309 Update_Row(X_Rowid => X_Rowid,
310 X_Balance_Feed_Id => X_Balance_Feed_Id,
311 X_Effective_Start_Date => X_Effective_Start_Date,
312 X_Effective_End_Date => X_Effective_End_Date,
313 X_Business_Group_Id => X_Business_Group_Id,
314 X_Legislation_Code => X_Legislation_Code,
315 X_Balance_Type_Id => X_Balance_Type_Id,
316 X_Input_Value_Id => X_Input_Value_Id,
317 X_Scale => X_Scale,
318 X_Legislation_Subgroup => X_Legislation_Subgroup,
319 X_Initial_Balance_Feed => FALSE );
320 END Update_Row ;
321 PROCEDURE Update_Row(X_Rowid VARCHAR2,
322 X_Balance_Feed_Id NUMBER,
323 X_Effective_Start_Date DATE,
324 X_Effective_End_Date DATE,
325 X_Business_Group_Id NUMBER,
326 X_Legislation_Code VARCHAR2,
327 X_Balance_Type_Id NUMBER,
331 X_Initial_Balance_Feed BOOLEAN) IS
328 X_Input_Value_Id NUMBER,
329 X_Scale NUMBER,
330 X_Legislation_Subgroup VARCHAR2,
332 BEGIN
333 --
334 -- Lock balance type to stop other users changing the balance feed.
335 --
336 hr_balance_feeds.lock_balance_type(X_Balance_Type_Id);
337 --
338 -- Make sure that balance is not fed by classifications which would make the
339 -- balance feeds READ-ONLY and therefore this operation invalid.
340 --
341 if ( NOT X_initial_Balance_Feed )
342 then
343 --
344 if ( hr_balance_feeds.bal_classifications_exist(X_Balance_Type_Id))
345 then
346 hr_utility.set_message(801, 'HR_7444_BAL_FEED_READ_ONLY');
347 hr_utility.raise_error;
348 end if;
349 --
350 else
351 --
352 -- Make sure that there are no processed run results for this
353 -- input value
354 --
355 check_run_result_usage( X_Input_Value_Id ) ;
356 --
357 end if;
358 --
359 UPDATE pay_balance_feeds_f
360 SET balance_feed_id = X_Balance_Feed_Id,
361 effective_start_date = X_Effective_Start_Date,
362 effective_end_date = X_Effective_End_Date,
363 business_group_id = X_Business_Group_Id,
364 legislation_code = X_Legislation_Code,
365 balance_type_id = X_Balance_Type_Id,
366 input_value_id = X_Input_Value_Id,
367 scale = X_Scale,
368 legislation_subgroup = X_Legislation_Subgroup
369 WHERE rowid = X_rowid;
370 --
371 if (SQL%NOTFOUND) then
372 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
373 hr_utility.set_message_token('PROCEDURE',
374 'pay_balance_feeds_f_pkg.update_row');
375 hr_utility.set_message_token('STEP','1');
376 hr_utility.raise_error;
377 end if;
378 --
379 END Update_Row;
380 --
381 -----------------------------------------------------------------------------
382 -- Name --
383 -- Delete_Row --
384 -- Purpose --
385 -- Table handler procedure that supports the delete of a balance feed --
386 -- via the Define Balance Type form. --
387 -- Arguments --
388 -- See below. --
389 -- Notes --
390 -----------------------------------------------------------------------------
391 --
392 PROCEDURE Delete_Row(X_Rowid VARCHAR2,
393 -- Extra Columns
394 X_Balance_Type_Id NUMBER ) IS
395 BEGIN
396 Delete_Row(X_Rowid => X_Rowid,
397 X_Balance_Type_Id => X_Balance_Type_Id,
398 X_Input_Value_Id => NULL,
399 X_Initial_Balance_Feed => FALSE ) ;
400 END Delete_Row;
401 --
402 PROCEDURE Delete_Row(X_Rowid VARCHAR2,
403 -- Extra Columns
404 X_Balance_Type_Id NUMBER,
405 X_Input_Value_Id NUMBER,
406 X_Initial_Balance_Feed BOOLEAN) IS
407 BEGIN
408 --
409 -- Lock balance type to stop other users changing the balance feed.
410 --
411 hr_balance_feeds.lock_balance_type(X_Balance_Type_Id);
412 --
413 -- Make sure that balance is not fed by classifications which would make the
414 -- balance feeds READ-ONLY and therefore this operation invalid.
415 --
416 if ( NOT X_Initial_Balance_Feed ) then
417 --
418 if hr_balance_feeds.bal_classifications_exist(X_Balance_Type_Id) then
419 hr_utility.set_message(801, 'HR_7444_BAL_FEED_READ_ONLY');
420 hr_utility.raise_error;
421 end if;
422 --
423 else
424 --
425 -- Make sure that there are no processed run results for this
426 -- input value
427 --
428 check_run_result_usage( X_Input_Value_Id ) ;
429 --
430 end if;
431 --
432 DELETE FROM pay_balance_feeds_f
433 WHERE rowid = X_Rowid;
434 --
435 if (SQL%NOTFOUND) then
436 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
437 hr_utility.set_message_token('PROCEDURE',
438 'pay_balance_feeds_f_pkg.delete_row');
439 hr_utility.set_message_token('STEP','1');
440 hr_utility.raise_error;
441 end if;
442 --
443 END Delete_Row;
444 --
445 FUNCTION check_run_result_usage ( X_Input_Value_Id IN NUMBER )
446 RETURN BOOLEAN IS
447 CURSOR C1 IS SELECT 1
448 FROM pay_run_result_values rrv
449 WHERE rrv.input_value_id = X_Input_Value_Id ;
450 l_dummy NUMBER ;
451 l_return_value BOOLEAN ;
452 BEGIN
453 --
454 OPEN C1 ;
455 FETCH C1 INTO l_dummy ;
456 IF C1%FOUND THEN
457 hr_utility.set_message(801,'HR_7876_BAL_FEED_RESULTS_EXIST');
458 l_return_value := FALSE ;
459 ELSE
460 l_return_value := TRUE ;
461 END IF;
462 --
466 --
463 CLOSE C1 ;
464 --
465 return ( l_return_value ) ;
467 END check_run_result_usage ;
468 PROCEDURE check_run_result_usage ( X_Input_Value_Id IN NUMBER ) IS
469 BEGIN
470 --
471 IF ( NOT check_run_result_usage ( X_Input_Value_Id ) ) THEN
472 hr_utility.raise_error ;
473 END IF;
474 --
475 END check_run_result_usage ;
476 --
477 END PAY_BALANCE_FEEDS_F_PKG;