1 PACKAGE BODY PAY_BAL_CLASSIFICATIONS_PKG as
2 /* $Header: pyblc01t.pkb 115.5 2002/12/06 14:20:26 alogue ship $ */
3 --
4 /*==========================================================================+
5 | Copyright (c) 1993 Oracle Corporation |
6 | Redwood Shores, California, USA |
7 | All rights reserved. |
8 +===========================================================================+
9 Name
10 pay_bal_classifications_pkg
11 Purpose
12 Used by PAYWSDBT (Define Balance Type) for the balance classification
13 block (BLC).
14 Notes
15
16 History
17 01-Mar-94 J.S.Hobbs 40.0 Date created.
18 20-Apr-94 J.S.Hobbs 40.1 Added rtrim to Lock_Row.
19 01-Feb-95 J.S.Hobbs 40.5 Removed aol WHO columns.
20 24-Apr-95 J.S.Hobbs 40.6 Added extra validation to stop the
21 mixing of manual and automatic balance
22 feeds (via classifications).
23 05-Mar-97 J.Alloun 40.7 Changed all occurances of system.dual
24 to sys.dual for next release
25 requirements.
26 16-NOV-2001 RThirlby 115.2 930964 New parameter X_mode added to procedure
27 delivered in patch 2000669 insert_row so that the startup mode
28 (either GENERIC, STARTUP or USER) can
29 be identified. This is required, as a
30 enhancement request was made where by
31 functionality for chk_bal_clasification
32 if different depending on what mode
33 you are in. In USER mode there is no
34 change. In STARTUP mode, it is now
35 possible to feed a balance from more
36 than one secondary classifcation.
37 16-NOV-2001 RThirlby 115.3 Added commit to end of file for GSCC
38 standards.
39 01-JUL-2002 RCallaghan 115.4 Added checkfile line.
40 06-DEC-2002 ALogue 115.5 NOCOPY changes. Bug 2692195
41 ============================================================================*/
42 --
43 -----------------------------------------------------------------------------
44 -- Name --
45 -- chk_balance_classification --
46 -- Purpose --
47 -- Make sure the balance classification is unique ie. only one --
48 -- classification per family is allowed for a balance. --
49 -- Bug 930964 - It is now possible to have more than one balance --
50 -- classification per family, when in STARTUP mode. --
51 -- The restriction will remain for USER mode, to help --
52 -- prevent users creating duplicate feeds to balances. --
53 -- Arguments --
54 -- See below. --
55 -- Bug 930964 - Added new parameter p_mode. The where clause restriction --
56 -- alters depending on this value. This should be left null --
57 -- when in USER mode.
58 -- Notes --
59 -----------------------------------------------------------------------------
60 --
61 procedure chk_balance_classification
62 (
63 p_row_id varchar2,
64 p_balance_type_id number,
65 p_classification_id number,
66 p_mode varchar2 default null
67 ) is
68 --
69 cursor csr_unique_bal_classification (p_startup_mode varchar2) is
70 select ecl.classification_id
71 from pay_element_classifications ecl
72 where ecl.classification_id = p_classification_id
73 and not exists
74 (select null
75 from pay_balance_classifications bcl
76 where bcl.balance_type_id = p_balance_type_id
77 and bcl.classification_id = ecl.classification_id
78 and (p_row_id is null or
79 (p_row_id is not null and
80 chartorowid(p_row_id) <> bcl.rowid)))
81 and ((p_startup_mode is null
82 and ((ecl.parent_classification_id is null and
83 not exists
84 (select null
85 from pay_balance_classifications bcl2,
86 pay_element_classifications ecl2
87 where bcl2.balance_type_id = p_balance_type_id
88 and ecl2.classification_id = bcl2.classification_id
89 and ecl2.parent_classification_id = ecl.classification_id
90 and (p_row_id is null or
91 (p_row_id is not null and
92 chartorowid(p_row_id) <> bcl2.rowid))))
93 or (ecl.parent_classification_id is not null and
94 not exists
95 (select null
96 from pay_balance_classifications bcl3,
97 pay_element_classifications ecl3
98 where bcl3.balance_type_id = p_balance_type_id
99 and ecl3.classification_id = bcl3.classification_id
100 and (ecl3.parent_classification_id =
101 ecl.parent_classification_id or
102 ecl3.classification_id =
103 ecl.parent_classification_id)
104 and (p_row_id is null or
105 (p_row_id is not null and
106 chartorowid(p_row_id) <> bcl3.rowid))))))
107 or (p_startup_mode is not null));
108 --
109 v_bal_classification_id number;
110 l_mode varchar2(10);
111 --
112 begin
113 --
114 -- If p_mode is passed through as USER, set it to null. It should only
115 -- be populated if in STARTUP or GENERIC mode.
116 --
117 if p_mode = 'USER' then
118 l_mode := '';
119 else
120 l_mode := p_mode;
121 end if;
122 --
123 open csr_unique_bal_classification(l_mode);
124 fetch csr_unique_bal_classification into v_bal_classification_id;
125 if csr_unique_bal_classification%notfound then
126 close csr_unique_bal_classification;
127 hr_utility.set_message(801, 'HR_6116_BAL_UNI_CLAS');
128 hr_utility.raise_error;
129 else
130 close csr_unique_bal_classification;
131 end if;
132 --
133 end chk_balance_classification;
134 --
135 -----------------------------------------------------------------------------
136 -- Name --
137 -- Insert_Row --
138 -- Purpose --
139 -- Table handler procedure that supports the insert of a balance --
140 -- classification via the Define Balance Type form. --
141 -- Arguments --
142 -- See below. --
143 -- Bug 930964 - new parameter X_mode, to enable different functionality --
144 -- in chk_balance_classification depending on the startup --
145 -- mode. See chk_balance_classification for more details. --
146 -- Notes --
147 -----------------------------------------------------------------------------
148 --
149 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
150 X_Balance_Classification_Id IN OUT NOCOPY NUMBER,
151 X_Business_Group_Id NUMBER,
152 X_Legislation_Code VARCHAR2,
153 X_Balance_Type_Id NUMBER,
154 X_Classification_Id NUMBER,
155 X_Scale NUMBER,
156 X_Legislation_Subgroup VARCHAR2,
157 X_mode VARCHAR2 default null)
158 IS
159 --
160 CURSOR C IS SELECT rowid FROM pay_balance_classifications
161 WHERE balance_classification_id = X_Balance_Classification_Id;
162 --
163 CURSOR C2 IS SELECT pay_balance_classifications_s.nextval FROM sys.dual;
164 --
165 BEGIN
166 --
167 -- Lock balance type to stop other users changing the balance.
168 --
169 hr_balance_feeds.lock_balance_type(X_Balance_Type_Id);
170 --
171 -- Make sure that balance is not fed by manual balance feeds which would
172 -- disable the use of classifications and therefore make this operation
173 -- invalid.
174 --
175 if hr_balance_feeds.manual_bal_feeds_exist(X_Balance_Type_Id) then
176 hr_utility.set_message(801, 'HR_7445_BAL_CLASS_NO_CREATE');
177 hr_utility.raise_error;
178 end if;
179 --
180 -- Make sure that there are no duplicate balance classifications NB. there
181 -- can only be one classification per family ie. one of parent and its sub
182 -- classifications.
183 --
184 chk_balance_classification
185 (X_RowId,
186 X_Balance_Type_Id,
187 X_Classification_Id,
188 X_mode);
189 --
190 if (X_Balance_Classification_Id is NULL) then
191 OPEN C2;
192 FETCH C2 INTO X_Balance_Classification_Id;
193 CLOSE C2;
194 end if;
195 --
196 INSERT INTO pay_balance_classifications
197 (balance_classification_id,
198 business_group_id,
199 legislation_code,
200 balance_type_id,
201 classification_id,
202 scale,
203 legislation_subgroup)
204 VALUES
205 (X_Balance_Classification_Id,
206 X_Business_Group_Id,
207 X_Legislation_Code,
208 X_Balance_Type_Id,
209 X_Classification_Id,
210 X_Scale,
211 X_Legislation_Subgroup);
212 --
213 OPEN C;
214 FETCH C INTO X_Rowid;
215 if (C%NOTFOUND) then
216 CLOSE C;
217 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
218 hr_utility.set_message_token('PROCEDURE',
219 'pay_bal_classifications_pkg.insert_row');
220 hr_utility.set_message_token('STEP','1');
221 hr_utility.raise_error;
222 end if;
223 CLOSE C;
224 --
225 -- Create balance feeds for elements with the same Classification /
226 -- Sub Classification which have a Pay Value that has the same
227 -- Units as the balance and also the same currency if the
228 -- Units are money.
229 --
230 hr_balance_feeds.ins_bf_bal_class
231 (X_Balance_Type_Id,
232 X_Balance_Classification_Id);
233 --
234 END Insert_Row;
235 --
236 -----------------------------------------------------------------------------
237 -- Name --
238 -- Lock_Row --
239 -- Purpose --
240 -- Table handler procedure that supports the insert , update and delete --
241 -- of a balance classification by applying a lock on a balance --
242 -- classification in the Define Balance Type form. --
243 -- Arguments --
244 -- See below. --
245 -- Notes --
246 -----------------------------------------------------------------------------
247 --
248 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
249 X_Balance_Classification_Id NUMBER,
250 X_Business_Group_Id NUMBER,
251 X_Legislation_Code VARCHAR2,
252 X_Balance_Type_Id NUMBER,
253 X_Classification_Id NUMBER,
254 X_Scale NUMBER,
255 X_Legislation_Subgroup VARCHAR2) IS
256 --
257 CURSOR C IS SELECT * FROM pay_balance_classifications
258 WHERE rowid = X_Rowid FOR UPDATE of Balance_Classification_Id
259 NOWAIT;
260 --
261 Recinfo C%ROWTYPE;
262 --
263 BEGIN
264 --
265 OPEN C;
266 FETCH C INTO Recinfo;
267 if (C%NOTFOUND) then
268 CLOSE C;
269 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
270 hr_utility.set_message_token('PROCEDURE',
271 'pay_bal_classifications_pkg.lock_row');
272 hr_utility.set_message_token('STEP','1');
273 hr_utility.raise_error;
274 end if;
275 CLOSE C;
279 Recinfo.legislation_code := rtrim(Recinfo.legislation_code);
276 --
277 -- Remove trailing spaces.
278 --
280 Recinfo.legislation_subgroup := rtrim(Recinfo.legislation_subgroup);
281 --
282 if ( ( (Recinfo.balance_classification_id = X_Balance_Classification_Id)
283 OR ( (Recinfo.balance_classification_id IS NULL)
284 AND (X_Balance_Classification_Id IS NULL)))
285 AND ( (Recinfo.business_group_id = X_Business_Group_Id)
286 OR ( (Recinfo.business_group_id IS NULL)
287 AND (X_Business_Group_Id IS NULL)))
288 AND ( (Recinfo.legislation_code = X_Legislation_Code)
289 OR ( (Recinfo.legislation_code IS NULL)
290 AND (X_Legislation_Code IS NULL)))
291 AND ( (Recinfo.balance_type_id = X_Balance_Type_Id)
292 OR ( (Recinfo.balance_type_id IS NULL)
293 AND (X_Balance_Type_Id IS NULL)))
294 AND ( (Recinfo.classification_id = X_Classification_Id)
295 OR ( (Recinfo.classification_id IS NULL)
296 AND (X_Classification_Id IS NULL)))
297 AND ( (Recinfo.scale = X_Scale)
298 OR ( (Recinfo.scale IS NULL)
299 AND (X_Scale IS NULL)))
300 AND ( (Recinfo.legislation_subgroup = X_Legislation_Subgroup)
301 OR ( (Recinfo.legislation_subgroup IS NULL)
302 AND (X_Legislation_Subgroup IS NULL)))
303 ) then
304 return;
305 else
306 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
307 APP_EXCEPTION.RAISE_EXCEPTION;
308 end if;
309 --
310 END Lock_Row;
311 --
312 -----------------------------------------------------------------------------
313 -- Name --
314 -- Update_Row --
315 -- Purpose --
316 -- Table handler procedure that supports the update of a balance --
317 -- classification via the Define Balance Type form. --
318 -- Arguments --
319 -- See below. --
320 -- Notes --
321 -----------------------------------------------------------------------------
322 --
323 PROCEDURE Update_Row(X_Rowid VARCHAR2,
324 X_Balance_Classification_Id NUMBER,
325 X_Business_Group_Id NUMBER,
326 X_Legislation_Code VARCHAR2,
327 X_Balance_Type_Id NUMBER,
328 X_Classification_Id NUMBER,
329 X_Scale NUMBER,
330 X_Legislation_Subgroup VARCHAR2) IS
331 BEGIN
332 --
333 UPDATE pay_balance_classifications
334 SET balance_classification_id = X_Balance_Classification_Id,
335 business_group_id = X_Business_Group_Id,
336 legislation_code = X_Legislation_Code,
337 balance_type_id = X_Balance_Type_Id,
338 classification_id = X_Classification_Id,
339 scale = X_Scale,
340 legislation_subgroup = X_Legislation_Subgroup
341 WHERE rowid = X_rowid;
342 --
343 if (SQL%NOTFOUND) then
344 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
345 hr_utility.set_message_token('PROCEDURE',
346 'pay_bal_classifications_pkg.update_row');
347 hr_utility.set_message_token('STEP','1');
348 hr_utility.raise_error;
349 end if;
350 --
351 -- Update all balance feeds that are linked to the balance classification
352 -- NB. the only attribute that can be updated is Add or Subtract.
353 --
354 hr_balance_feeds.upd_del_bf_bal_class
355 ('UPDATE',
356 X_Balance_Classification_Id,
357 X_Scale);
358 --
359 END Update_Row;
360 --
361 -----------------------------------------------------------------------------
362 -- Name --
363 -- Delete_Row --
364 -- Purpose --
365 -- Table handler procedure that supports the delete of a balance --
366 -- classification via the Define Balance Type form. --
367 -- Arguments --
368 -- See below. --
369 -- Notes --
370 -----------------------------------------------------------------------------
371 --
372 PROCEDURE Delete_Row(X_Rowid VARCHAR2,
373 -- Extra Columns
374 X_Balance_Classification_Id NUMBER) IS
375 BEGIN
376 --
377 -- Remove all balance feeds that are linked to the balance classification.
378 --
379 hr_balance_feeds.upd_del_bf_bal_class
380 ('DELETE',
381 X_Balance_Classification_Id,
382 null);
383 --
384 DELETE FROM pay_balance_classifications
385 WHERE rowid = X_Rowid;
386 --
387 if (SQL%NOTFOUND) then
388 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
389 hr_utility.set_message_token('PROCEDURE',
390 'pay_bal_classifications_pkg.delete_row');
391 hr_utility.set_message_token('STEP','1');
395 END Delete_Row;
392 hr_utility.raise_error;
393 end if;
394 --
396 --
397 END PAY_BAL_CLASSIFICATIONS_PKG;
[Home] [Help]