1 PACKAGE BODY PAY_ELEMENT_TYPE_RULES_PKG as
2 /* $Header: pyetr01t.pkb 115.0 99/07/17 06:02:13 porting ship $ */
3 --
4 /*==========================================================================+
5 | Copyright (c) 1993 Oracle Corporation |
6 | Redwood Shores, California, USA |
7 | All rights reserved. |
8 +===========================================================================+
9 Name
10 pay_element_type_rules_pkg
11 Purpose
12 Supports the ETR 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 22-Apr-94 J.S.Hobbs 40.1 Added rtrim to Lock_Row.
19 25-Jul-95 A.R.R.Mills 40.4 Changed tokenised message
20 'HR_6056_ELE_SET_CLASS_EXISTS'
21 to hard coded message
22 'HR_7701_ELE_SET_CLASS_EXIST'.
23
24
25 ============================================================================*/
26 --
27 -----------------------------------------------------------------------------
28 -- Name --
29 -- include_element --
30 -- Purpose --
31 -- Adds an element to a set NB. this may involve adding an include --
32 -- element rule or removing an exclude element rule. --
33 -- Arguments --
34 -- See below. --
35 -- Notes --
36 -- None. --
37 -----------------------------------------------------------------------------
38 --
39 procedure include_element
40 (
41 p_element_set_id number,
42 p_element_type_id number,
43 p_classification_id number,
44 p_element_set_type varchar2
45 ) is
46 --
47 cursor csr_class_rule is
48 select ecr.classification_id
49 from pay_ele_classification_rules ecr
50 where ecr.element_set_id = p_element_set_id
51 and ecr.classification_id = p_classification_id
52 and not exists
53 (select null
54 from pay_element_type_rules etr
55 where etr.element_set_id = ecr.element_set_id
56 and etr.element_type_id = p_element_type_id
57 and etr.include_or_exclude = 'E');
58 --
59 v_dummy number;
60 --
61 begin
62 --
63 -- Lock element set to preserve the current definition.
64 pay_element_sets_pkg.lock_element_set
65 (p_element_set_id);
66 --
67 -- Check to see if the element rule to be inserted will clash with an
68 -- existing classification rule ie. an element type can only be included
69 -- once - either by a classification rule or an include element rule.
70 open csr_class_rule;
71 fetch csr_class_rule into v_dummy;
72 if csr_class_rule%found then
73 close csr_class_rule;
74 hr_utility.set_message(801, 'HR_7102_ELE_SET_CLASS_EXISTS');
75 hr_utility.raise_error;
76 else
77 close csr_class_rule;
78 end if;
79 --
80 -- If an element type rule is being added to a distribution set then make
81 -- sure that all the elements belong to the same debit or credit status.
82 if p_element_set_type = 'D' then
83 --
84 pay_element_sets_pkg.chk_debit_or_credit
85 (p_element_set_id,
86 p_classification_id);
87 --
88 end if;
89 --
90 -- Create an include element type rule for the element.
91 insert into pay_element_type_rules
92 (element_type_id,
93 element_set_id,
94 include_or_exclude,
95 last_update_date,
96 last_updated_by,
97 last_update_login,
98 created_by,
99 creation_date)
100 select
101 p_element_type_id,
102 p_element_set_id,
103 'I',
104 trunc(sysdate),
105 0,
106 0,
107 0,
108 trunc(sysdate)
109 from sys.dual
110 where not exists
111 (select null
112 from pay_element_type_rules etr
113 where etr.element_set_id = p_element_set_id
114 and etr.element_type_id = p_element_type_id);
115 --
116 -- Element rule could not be created as there already exists an element rule
117 -- for this element. Remove the element rule if it is excluded ie. this will
118 -- then make the element part of the set.
119 if sql%notfound then
120 --
121 delete from pay_element_type_rules etr
122 where etr.element_set_id = p_element_set_id
123 and etr.element_type_id = p_element_type_id
124 and etr.include_or_exclude = 'E';
125 --
126 -- The insert of an include element rule and the removal of an exclude
127 -- element rule have both failed which means the element is already part
128 -- of the set.
129 if sql%notfound then
130 hr_utility.set_message(801, 'HR_7701_ELE_SET_CLASS_EXIST');
131 hr_utility.raise_error;
132 end if;
133 --
134 end if;
135 --
136 end include_element;
137 --
138 -----------------------------------------------------------------------------
139 -- Name --
140 -- exclude_element --
141 -- Purpose --
142 -- Removes an element from a set NB. this may involve adding an exclude --
143 -- element rule or removing an include element rule. --
144 -- Arguments --
145 -- See below. --
146 -- Notes --
147 -- None. --
148 -----------------------------------------------------------------------------
149 --
150 procedure exclude_element
151 (
152 p_element_set_id number,
153 p_element_type_id number
154 ) is
155 --
156 begin
157 --
158 -- Lock element set to preserve the current definition.
159 pay_element_sets_pkg.lock_element_set
160 (p_element_set_id);
161 --
162 -- Create an exclude element type rule for the element.
163 insert into pay_element_type_rules
164 (element_type_id,
165 element_set_id,
166 include_or_exclude,
167 last_update_date,
168 last_updated_by,
169 last_update_login,
170 created_by,
171 creation_date)
172 select
173 p_element_type_id,
174 p_element_set_id,
175 'E',
176 trunc(sysdate),
177 0,
178 0,
179 0,
180 trunc(sysdate)
181 from sys.dual
182 where not exists
183 (select null
184 from pay_element_type_rules etr
185 where etr.element_set_id = p_element_set_id
186 and etr.element_type_id = p_element_type_id);
187 --
188 -- Element rule could not be created as there already exists an element rule
189 -- for this element. Remove the element rule if it is included ie. this will
190 -- then remove the element from the set.
191 if sql%notfound then
192 --
193 delete from pay_element_type_rules etr
194 where etr.element_set_id = p_element_set_id
195 and etr.element_type_id = p_element_type_id
196 and etr.include_or_exclude = 'I';
197 --
198 end if;
199 --
200 end exclude_element;
201 --
202 -----------------------------------------------------------------------------
203 -- Name --
204 -- Insert_Row --
205 -- Purpose --
206 -- Table handler procedure that supports the insert of an element rule --
207 -- via the Define Element and Distributuion Set form. --
208 -- Arguments --
209 -- See below. --
210 -- Notes --
211 -- None. --
212 -----------------------------------------------------------------------------
213 --
214 PROCEDURE Insert_Row(X_Rowid IN OUT VARCHAR2,
215 X_Element_Type_Id NUMBER,
216 X_Element_Set_Id NUMBER,
217 X_Include_Or_Exclude VARCHAR2,
218 X_Last_Update_Date DATE,
219 X_Last_Updated_By NUMBER,
220 X_Last_Update_Login NUMBER,
221 X_Created_By NUMBER,
222 X_Creation_Date DATE) IS
223 --
224 CURSOR C IS SELECT rowid FROM pay_element_type_rules
225 WHERE element_set_id = X_Element_Set_Id
226 AND element_type_id = X_Element_Type_Id;
227 --
228 BEGIN
229 --
230 INSERT INTO pay_element_type_rules
231 (element_type_id,
232 element_set_id,
233 include_or_exclude,
234 last_update_date,
235 last_updated_by,
236 last_update_login,
237 created_by,
238 creation_date)
239 VALUES
240 (X_Element_Type_Id,
241 X_Element_Set_Id,
242 X_Include_Or_Exclude,
243 X_Last_Update_Date,
244 X_Last_Updated_By,
245 X_Last_Update_Login,
246 X_Created_By,
247 X_Creation_Date);
248 --
249 OPEN C;
250 FETCH C INTO X_Rowid;
251 if (C%NOTFOUND) then
252 CLOSE C;
253 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
254 hr_utility.set_message_token('PROCEDURE',
255 'pay_element_type_rules_pkg.insert_row');
256 hr_utility.set_message_token('STEP','1');
257 hr_utility.raise_error;
258 end if;
259 CLOSE C;
260 --
261 END Insert_Row;
262 --
263 -----------------------------------------------------------------------------
264 -- Name --
265 -- Lock_Row --
266 -- Purpose --
267 -- Table handler procedure that supports the insert , update and delete --
268 -- of an element rule by applying a lock on an element rule within the --
269 -- Define Element and Distribution Set form. --
270 -- Arguments --
271 -- See below. --
272 -- None. --
273 -----------------------------------------------------------------------------
274 --
275 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
276 X_Element_Type_Id NUMBER,
277 X_Element_Set_Id NUMBER,
278 X_Include_Or_Exclude VARCHAR2) IS
279 --
280 CURSOR C IS SELECT * FROM pay_element_type_rules
281 WHERE rowid = X_Rowid FOR UPDATE of Element_Set_Id NOWAIT;
282 --
283 Recinfo C%ROWTYPE;
284 --
285 BEGIN
286 --
287 OPEN C;
288 FETCH C INTO Recinfo;
289 if (C%NOTFOUND) then
290 CLOSE C;
291 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
292 hr_utility.set_message_token('PROCEDURE',
293 'pay_element_type_rules_pkg.lock_row');
294 hr_utility.set_message_token('STEP','1');
295 hr_utility.raise_error;
296 end if;
297 CLOSE C;
298 --
299 -- Remove trailing spaces.
300 Recinfo.include_or_exclude := rtrim(Recinfo.include_or_exclude);
301 --
302 if ( ( (Recinfo.element_type_id = X_Element_Type_Id)
303 OR ( (Recinfo.element_type_id IS NULL)
304 AND (X_Element_Type_Id IS NULL)))
305 AND ( (Recinfo.element_set_id = X_Element_Set_Id)
306 OR ( (Recinfo.element_set_id IS NULL)
307 AND (X_Element_Set_Id IS NULL)))
308 AND ( (Recinfo.include_or_exclude = X_Include_Or_Exclude)
309 OR ( (Recinfo.include_or_exclude IS NULL)
310 AND (X_Include_Or_Exclude IS NULL)))
311 ) then
312 return;
313 else
314 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
315 APP_EXCEPTION.RAISE_EXCEPTION;
316 end if;
317 --
318 END Lock_Row;
319 --
320 -----------------------------------------------------------------------------
321 -- Name --
322 -- Update_Row --
323 -- Purpose --
324 -- Table handler procedure that supports the update of an element rule --
325 -- via the Define Element and Distributuion Set form. --
326 -- Arguments --
327 -- See below. --
328 -- Notes --
329 -- None. --
330 -----------------------------------------------------------------------------
331 --
332 PROCEDURE Update_Row(X_Rowid VARCHAR2,
333 X_Element_Type_Id NUMBER,
334 X_Element_Set_Id NUMBER,
335 X_Include_Or_Exclude VARCHAR2,
336 X_Last_Update_Date DATE,
337 X_Last_Updated_By NUMBER,
338 X_Last_Update_Login NUMBER) IS
339 BEGIN
340 --
341 UPDATE pay_element_type_rules
342 SET element_type_id = X_Element_Type_Id,
343 element_set_id = X_Element_Set_Id,
344 include_or_exclude = X_Include_Or_Exclude,
345 last_update_date = X_Last_Update_Date,
346 last_updated_by = X_Last_Updated_By,
347 last_update_login = X_Last_Update_Login
348 WHERE rowid = X_rowid;
349 --
350 if (SQL%NOTFOUND) then
351 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
352 hr_utility.set_message_token('PROCEDURE',
353 'pay_element_type_rules_pkg.update_row');
354 hr_utility.set_message_token('STEP','1');
355 hr_utility.raise_error;
356 end if;
357 --
358 END Update_Row;
359 --
360 -----------------------------------------------------------------------------
361 -- Name --
362 -- Delete_Row --
363 -- Purpose --
364 -- Table handler procedure that supports the delete of an element rule --
365 -- via the Define Element and Distributuion Set form. --
366 -- Arguments --
367 -- See below. --
368 -- Notes --
369 -- None. --
370 -----------------------------------------------------------------------------
371 --
372 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
373 BEGIN
374 --
375 DELETE FROM pay_element_type_rules
376 WHERE rowid = X_Rowid;
377 --
378 if (SQL%NOTFOUND) then
379 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
380 hr_utility.set_message_token('PROCEDURE',
381 'pay_element_type_rules_pkg.delete_row');
382 hr_utility.set_message_token('STEP','1');
383 hr_utility.raise_error;
384 end if;
385 --
386 END Delete_Row;
387 --
388 END PAY_ELEMENT_TYPE_RULES_PKG;
[Home] [Help]