1 PACKAGE BODY PAY_ELE_CLASS_RULES_PKG as
2 /* $Header: pyecr01t.pkb 115.3 2002/12/16 17:45:56 dsaxby ship $ */
3 --
4 /*==========================================================================+
5 | Copyright (c) 1993 Oracle Corporation |
6 | Redwood Shores, California, USA |
7 | All rights reserved. |
8 +===========================================================================+
9 Name
10 pay_ele_class_rules_pkg
11 Purpose
12 Supports the ECR block in the form PAYWSDRP (Define Element and
13 Distributuion Set.
14 Notes
15
16 HIStory
17 24-Mar-94 J.S.Hobbs 40.0 Date created.
18 25-JUL-95 AMILLS 40.4 Replaced tokenised message
19 'HR_6056_ELE_SET_CLASS_EXISTS' with h/c
20 'HR_7879_ELE_SET_UNIQUE_RULES'.
21 24-FEB-99 J. Moyano 115.1 MLS Changes. Reference to
22 pay_element_classification_tl table
23 in cursor csr_classification_name.
24 16-DEC-02 D. Saxby 115.3 2692195 Nocopy changes.
25 --
26 ============================================================================*/
27 --
28 -----------------------------------------------------------------------------
29 -- Name --
30 -- get_classification_name --
31 -- Purpose --
32 -- Returns the classification name for a particular classification_id. --
33 -- Arguments --
34 -- See below. --
35 -- Notes --
36 -- None --
37 -----------------------------------------------------------------------------
38 function get_classification_name
39 (
40 p_classification_id number
41 ) return varchar2 is
42 --
43 cursor csr_classification_name is
44 select ecl_tl.classification_name
45 from pay_element_classifications_tl ecl_tl,
46 pay_element_classifications ecl
47 where ecl.classification_id = ecl_tl.classification_id
48 and ecl.classification_id = p_classification_id
49 and userenv('LANG') = ecl_tl.language;
50 --
51 v_classification_name pay_element_classifications.classification_name%type;
52 --
53 begin
54 --
55 open csr_classification_name;
56 fetch csr_classification_name into v_classification_name;
57 if csr_classification_name%notfound then
58 close csr_classification_name;
59 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
60 hr_utility.set_message_token('PROCEDURE',
61 'pay_ele_class_rules_pkg.get_classification_name');
62 hr_utility.set_message_token('STEP','1');
63 hr_utility.raise_error;
64 else
65 close csr_classification_name;
66 end if;
67 --
68 return v_classification_name;
69 --
70 end get_classification_name;
71 --
72 -----------------------------------------------------------------------------
73 -- Name --
74 -- chk_insert_ele_classification --
75 -- Purpose --
76 -- Validates the creation of a classification rule. --
77 -- Arguments --
78 -- See below. --
79 -- Notes --
80 -- Makes sure that each primary element classification can only be used --
81 -- once within an element set. --
82 -- Makes sure there are no element type rules for the classification ie. --
83 -- there are no include element type rules for the classification. . --
84 -----------------------------------------------------------------------------
85 --
86 procedure chk_insert_ele_classification
87 (
88 p_rowid varchar2,
89 p_element_set_id number,
90 p_classification_id number
91 ) is
92 --
93 cursor csr_ele_class_rule is
94 select ecr.classification_id
95 from pay_ele_classification_rules ecr
96 where ecr.element_set_id = p_element_set_id
97 and ecr.classification_id = p_classification_id
98 and (p_rowid is null or
99 (p_rowid is not null and chartorowid(p_rowid) <> ecr.rowid));
100 --
101 cursor csr_ele_type_rule is
102 select etr.element_type_id
103 from pay_element_type_rules etr,
104 pay_element_types_f et
105 where etr.element_set_id = p_element_set_id
106 and et.element_type_id = etr.element_type_id
107 and et.classification_id = p_classification_id;
108 --
109 v_dummy number;
110 --
111 begin
112 --
113 open csr_ele_class_rule;
114 fetch csr_ele_class_rule into v_dummy;
115 if csr_ele_class_rule%found then
116 close csr_ele_class_rule;
117 hr_utility.set_message(801, 'HR_7879_ELE_SET_UNIQUE_RULES');
118 hr_utility.raise_error;
119 else
120 close csr_ele_class_rule;
121 end if;
122 --
123 open csr_ele_type_rule;
124 fetch csr_ele_type_rule into v_dummy;
125 if csr_ele_type_rule%found then
126 close csr_ele_type_rule;
127 hr_utility.set_message(801, 'HR_6014_ELE_SET_RULES_EXIST');
128 hr_utility.set_message_token('INS_OR_DEL', 'insert');
129 hr_utility.set_message_token('CLASSIFICATION_NAME',
130 get_classification_name(p_classification_id));
131 hr_utility.set_message_token('INC_OR_EXC', 'included');
132 hr_utility.raise_error;
133 else
134 close csr_ele_type_rule;
135 end if;
136 --
137 end chk_insert_ele_classification;
138 --
139 -----------------------------------------------------------------------------
140 -- Name --
141 -- chk_delete_ele_classification --
142 -- Purpose --
143 -- Validates the removal of a classification rule. --
144 -- Arguments --
145 -- See below. --
146 -- Notes --
147 -- Makes sure there are no element type rules for the classification ie. --
148 -- there are no exclude element type rules for the classification. . --
149 -----------------------------------------------------------------------------
150 --
151 procedure chk_delete_ele_classification
152 (
153 p_element_set_id number,
154 p_classification_id number
155 ) is
156 --
157 cursor csr_ele_type_rule is
158 select etr.element_type_id
159 from pay_element_type_rules etr,
160 pay_element_types_f et
161 where etr.element_set_id = p_element_set_id
162 and et.element_type_id = etr.element_type_id
163 and et.classification_id = p_classification_id;
164 --
165 v_dummy number;
166 --
167 begin
168 --
169 open csr_ele_type_rule;
170 fetch csr_ele_type_rule into v_dummy;
171 if csr_ele_type_rule%found then
172 close csr_ele_type_rule;
173 hr_utility.set_message(801, 'HR_6014_ELE_SET_RULES_EXIST');
174 hr_utility.set_message_token('INS_OR_DEL', 'delete');
175 hr_utility.set_message_token('CLASSIFICATION_NAME',
176 get_classification_name(p_classification_id));
177 hr_utility.set_message_token('INC_OR_EXC', 'excluded');
178 hr_utility.raise_error;
179 else
180 close csr_ele_type_rule;
181 end if;
182 --
183 end chk_delete_ele_classification;
184 --
185 -----------------------------------------------------------------------------
186 -- Name --
187 -- Insert_Row --
188 -- Purpose --
189 -- Table handler procedure that supports the insert of a classification --
190 -- rule via the Define Element and Distributuion Set form. --
191 -- Arguments --
192 -- See below. --
193 -- Notes --
194 -----------------------------------------------------------------------------
195 --
196 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
197 X_Element_Set_Id IN OUT NOCOPY NUMBER,
198 X_Classification_Id NUMBER,
199 -- Extra Columns
200 X_Element_Set_Type VARCHAR2) IS
201 --
202 CURSOR C IS SELECT rowid FROM pay_ele_classification_rules
203 WHERE element_set_id = X_Element_Set_Id
204 AND classification_id = X_Classification_Id;
205 --
206 BEGIN
207 --
208 -- Lock element set to preserve the current definition.
209 pay_element_sets_pkg.lock_element_set
210 (X_Element_Set_Id);
211 --
212 chk_insert_ele_classification
213 (X_Rowid,
214 X_Element_Set_Id,
215 X_Classification_Id);
216 --
217 -- If adding a classification to a distribution set make sure the debit or
218 -- credit status is the same for all elements within the set.
219 if X_Element_Set_Type = 'D' then
220 --
221 pay_element_sets_pkg.chk_debit_or_credit
222 (X_Element_Set_Id,
223 X_Classification_Id);
224 --
225 end if;
226 --
227 INSERT INTO pay_ele_classification_rules
228 (element_set_id,
229 classification_id)
230 VALUES
231 (X_Element_Set_Id,
232 X_Classification_Id);
233 --
234 OPEN C;
235 FETCH C INTO X_Rowid;
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_ele_class_rules_pkg.insert_row');
241 hr_utility.set_message_token('STEP','1');
242 hr_utility.raise_error;
243 end if;
244 CLOSE C;
245 --
246 END Insert_Row;
247 --
248 -----------------------------------------------------------------------------
249 -- Name --
250 -- Lock_Row --
251 -- Purpose --
252 -- Table handler procedure that supports the insert , update and delete --
253 -- of a classification rule by applying a lock on a classification rule --
254 -- within the Define Element and Distribution Set form. --
255 -- Arguments --
256 -- See below. --
257 -- Notes --
258 -- None. --
259 -----------------------------------------------------------------------------
260 --
261 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
262 X_Element_Set_Id NUMBER,
263 X_Classification_Id NUMBER) IS
264 --
265 CURSOR C IS SELECT * FROM pay_ele_classification_rules
266 WHERE rowid = X_Rowid FOR UPDATE of Element_Set_Id NOWAIT;
267 --
268 Recinfo C%ROWTYPE;
269 --
270 BEGIN
271 --
272 OPEN C;
273 FETCH C INTO Recinfo;
274 if (C%NOTFOUND) then
275 CLOSE C;
276 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
277 hr_utility.set_message_token('PROCEDURE',
278 'pay_ele_class_rules_pkg.lock_row');
279 hr_utility.set_message_token('STEP','1');
280 hr_utility.raise_error;
281 end if;
282 CLOSE C;
283 --
284 if ( ( (Recinfo.element_set_id = X_Element_Set_Id)
285 OR ( (Recinfo.element_set_id IS NULL)
286 AND (X_Element_Set_Id IS NULL)))
287 AND ( (Recinfo.classification_id = X_Classification_Id)
288 OR ( (Recinfo.classification_id IS NULL)
289 AND (X_Classification_Id IS NULL)))
290 ) then
291 return;
292 else
293 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
294 APP_EXCEPTION.RAISE_EXCEPTION;
295 end if;
296 --
297 END Lock_Row;
298 --
299 -----------------------------------------------------------------------------
300 -- Name --
301 -- Update_Row --
302 -- Purpose --
303 -- Table handler procedure that supports the update of a classification --
304 -- rule via the Define Element and Distributuion Set form. --
305 -- Arguments --
306 -- See below. --
307 -- Notes --
308 -----------------------------------------------------------------------------
309 --
310 PROCEDURE Update_Row(X_Rowid VARCHAR2,
311 X_Element_Set_Id NUMBER,
312 X_Classification_Id NUMBER) IS
313 BEGIN
314 --
315 UPDATE pay_ele_classification_rules
316 SET element_set_id = X_Element_Set_Id,
317 classification_id = X_Classification_Id
318 WHERE rowid = X_rowid;
319 --
320 if (SQL%NOTFOUND) then
321 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
322 hr_utility.set_message_token('PROCEDURE',
323 'pay_ele_class_rules_pkg.update_row');
324 hr_utility.set_message_token('STEP','1');
325 hr_utility.raise_error;
326 end if;
327 --
328 END Update_Row;
329 --
330 -----------------------------------------------------------------------------
331 -- Name --
332 -- Delete_Row --
333 -- Purpose --
334 -- Table handler procedure that supports the delete of a classification --
335 -- rule via the Define Element and Distributuion Set form. --
336 -- Arguments --
337 -- See below. --
338 -- Notes --
339 -----------------------------------------------------------------------------
340 --
341 PROCEDURE Delete_Row(X_Rowid VARCHAR2,
342 -- Extra Columns
343 X_Element_Set_Id NUMBER,
344 X_Classification_Id NUMBER) IS
345 BEGIN
346 --
347 -- Lock element set to preserve the current definition.
348 pay_element_sets_pkg.lock_element_set
349 (X_Element_Set_Id);
350 --
351 chk_delete_ele_classification
352 (X_Element_Set_Id,
353 X_Classification_Id);
354 --
355 DELETE FROM pay_ele_classification_rules
356 WHERE rowid = X_Rowid;
357 --
358 if (SQL%NOTFOUND) then
359 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
360 hr_utility.set_message_token('PROCEDURE',
361 'pay_ele_class_rules_pkg.delete_row');
362 hr_utility.set_message_token('STEP','1');
363 hr_utility.raise_error;
364 end if;
365 --
366 END Delete_Row;
367 --
368 END PAY_ELE_CLASS_RULES_PKG;
[Home] [Help]