[Home] [Help]
PACKAGE BODY: APPS.PER_POS_STRUCTURE_VERSIONS_PKG
Source
1 PACKAGE BODY PER_POS_STRUCTURE_VERSIONS_PKG as
2 /* $Header: pepsv01t.pkb 120.1 2006/06/29 12:06:48 hmehta ship $ */
3 -----------------------------------------------------------------------------
4 FUNCTION get_next_free_no(X_Position_Structure_Id NUMBER) return NUMBER IS
5 --
6 --
7 l_next_free_no NUMBER; --Next Free Number in a hierarchy.
8 begin
9 select nvl(max(psv.version_number),0) + 1
10 into l_next_free_no
11 from per_pos_structure_versions psv
12 where psv.Position_Structure_Id = X_Position_Structure_Id;
13 --
14 return l_next_free_no;
15 end get_next_free_no;
16 -----------------------------------------------------------------------------
17 PROCEDURE check_version_number(X_Position_Structure_Id NUMBER
18 ,X_Version_Number NUMBEr
19 ,X_Rowid VARCHAR2) IS
20 --
21 -- Local Variable.
22 --
23 l_dummy VARCHAR2(1);
24 begin
25 select null
26 into l_dummy
27 from sys.dual
28 where exists (select 1
29 from per_pos_structure_versions psv
30 where psv.position_structure_id = X_Position_Structure_Id
31 and psv.version_number = X_Version_Number
32 and (psv.rowid <> X_Rowid
33 or X_Rowid is null));
34 --
35 hr_utility.set_message('801','HR_6077_PO_POS_DUP_VER');
36 hr_utility.raise_error;
37 --
38 exception
39 when no_data_found then
40 null;
41 end check_version_number;
42 --
43 -----------------------------------------------------------------------------
44 PROCEDURE check_date_gap(X_Date_From DATE,
45 X_Date_To DATE ,
46 X_Position_Structure_Id NUMBER,
47 X_gap_warning IN OUT NOCOPY VARCHAR2,
48 X_Rowid VARCHAR2) IS
49 --
50 -- Test for Gaps between hierarchy versions.
51 -- i.e. 1 10-Feb-94 16-Feb-94
52 -- 2 20-Mar-94
53 -- would flag a message as gap exists.
54 --
55 -- Local Variable
56 --
57 l_max_end_date DATE;
58 l_min_start_date DATE;
59 --
60 begin
61 X_gap_warning := 'N';
62 select max(psv.date_to)
63 into l_max_end_date
64 from per_pos_structure_versions psv
65 where psv.date_from < X_Date_From
66 --
67 -- Bug 608815: add missing clause to restrict the hierarchy, or the code
68 -- looks for the max date across all hierarchies. RMF 09-Jan-98.
69 --
70 and psv.position_structure_id = X_Position_Structure_Id
71 and (psv.rowid <> X_Rowid
72 or X_Rowid is null);
73 --
74 if (l_max_end_date is not null and X_Date_from = (l_max_end_date +1)
75 or (l_max_end_date is null)) then
76 select min(psv.date_from)
77 into l_min_start_date
78 from per_pos_structure_versions psv
79 where psv.position_structure_id = X_Position_Structure_Id
80 and psv.date_from > X_Date_To
81 and (psv.rowid <> X_Rowid
82 or psv.rowid is null);
83 --
84 --
85 if l_min_start_date is null then
86 return;
87 elsif (X_Date_To +1) = l_min_start_date then
88 return;
89 end if;
90 end if;
91 X_gap_warning := 'Y';
92 end;
93 -----------------------------------------------------------------------------
94 PROCEDURE check_overlap(X_Position_Structure_Id NUMBER
95 ,X_Rowid VARCHAR2
96 ,X_Date_From DATE
97 ,X_Date_To DATE
98 ,X_End_Of_Time DATE
99 ,X_End_Date_Closedown IN OUT NOCOPY VARCHAR2) IS
100 --
101 -- Check for Overlapping structures
102 --
103 --
104 -- Local Variables
105 --
106 l_dummy VARCHAR2(1);
107 Begin
108 --
109 X_End_Date_Closedown := 'N';
110 --
111 begin
112 select null
113 into l_dummy
114 from sys.dual
115 where exists
116 (select 1
117 from per_pos_structure_versions psv
118 where psv.position_structure_id = X_Position_Structure_Id
119 and X_Date_From > psv.date_from
120 and psv.date_to is null);
121 --
122 -- If None exist it will exit normally
123 --
124 begin
125 --
126 -- Close down the open structures, before testing for overlaps
127 --
128 update per_pos_structure_versions psv
129 set psv.date_to = (X_date_from - 1)
130 where psv.position_structure_id = X_Position_Structure_Id
131 and psv.date_to is null
132 and (psv.rowid <> X_Rowid
133 or X_Rowid is null);
134 --
135 if sql%rowcount <>0 then
136 X_End_Date_Closedown := 'Y';
137 end if;
138 end;
139 exception
140 when no_data_found then
141 null;
142 end;
143 begin
144 --
145 -- Test for overlapping rows
146 --
147 select null
148 into l_dummy
149 from sys.dual
150 where exists
151 (select 1
152 from per_pos_structure_versions psv
153 where psv.date_from <= nvl(X_Date_To, X_End_Of_Time)
154 and nvl(psv.date_to,X_End_Of_Time) >= X_Date_From
155 and psv.position_structure_id = X_Position_Structure_Id
156 and (psv.rowid <> X_Rowid
157 or X_Rowid is null));
158 --
159 hr_utility.set_message('801','HR_6076_PO_POS_OVERLAP');
160 hr_utility.raise_error;
161 --
162 end;
163 exception
164 when no_data_found then
165 null;
166 end check_overlap;
167 -----------------------------------------------------------------------------
168 PROCEDURE copy_elements(X_Pos_Structure_Version_Id NUMBER
169 ,X_Copy_Structure_Version_Id NUMBER) IS
170 --
171 -- Define Cursor for the Inserts
172 --
173 Cursor Struct_element is
174 select *
175 from per_pos_structure_elements pse
176 where pse.pos_structure_version_id = X_Copy_Structure_Version_Id;
177 --
178 -- Local Variable
179 --
180 ele_record Struct_element%ROWTYPE;
181 l_Rowid VARCHAR2(20);
182 l_Structure_element_id NUMBER;
183 begin
184
185 INSERT INTO PER_POS_STRUCTURE_ELEMENTS(
186 pos_structure_element_id,
187 business_group_id,
188 pos_structure_version_id,
189 subordinate_position_id,
190 parent_position_id
191 )
192 select per_pos_structure_elements_s.nextval,
193 business_group_id,
194 x_pos_structure_version_id,
195 subordinate_position_id,
196 parent_position_id
197 from per_pos_structure_elements pse
198 where pse.pos_structure_version_id = X_Copy_Structure_Version_Id;
199 /*
200 open Struct_element;
201 fetch Struct_element into ele_record;
202 loop
203 exit when Struct_element%NOTFOUND;
204 PER_POS_STRUCTURE_ELEMENTS_PKG.Insert_Row(
205 X_Rowid => l_Rowid
206 ,X_Pos_Structure_Element_Id =>l_Structure_element_id
207 ,X_Business_Group_Id => ele_record.Business_Group_Id
208 ,X_Pos_Structure_Version_Id => X_Pos_Structure_Version_Id
209 ,X_Subordinate_Position_Id => ele_record.Subordinate_Position_Id
210 ,X_Parent_Position_Id => ele_record.Parent_Position_Id);
211 --
212 -- Reset value of element_id else we will get a 0001 oracle error
213 -- duplicate key etc.
214 --
215 l_Structure_element_id := NULL;
216 fetch Struct_element into ele_record;
217 end loop;
218 close Struct_element;
219 */
220 end copy_elements;
221 -----------------------------------------------------------------------------
222 PROCEDURE pre_delete_checks(X_Pos_Structure_Version_Id NUMBER,
223 X_Business_Group_Id NUMBER,
224 X_Position_Structure_Id NUMBER,
225 X_Hr_Installed VARCHAR2) IS
226 --
227 -- Local Variable
228 --
229 l_dummy VARCHAR2(1);
230 begin
231 begin
232 select null
233 into l_dummy
234 from sys.dual
235 where exists( select null
236 from PER_POS_STRUCTURE_ELEMENTS PSE
237 where PSE.POS_STRUCTURE_VERSION_ID = X_Pos_Structure_Version_Id);
238 --
239 hr_utility.set_message('801','HR_6205_PO_POS_POS_NO_DEL');
240 hr_utility.raise_error;
241 exception
242 when no_data_found then
243 null;
244 end;
245 if X_Hr_Installed <> 'N' then
246 begin
247 select null
248 into l_dummy
249 from sys.dual
250 where exists(select null
251 from per_security_profiles
252 where business_group_id + 0 = X_Business_Group_Id
253 and position_structure_id = X_Position_Structure_Id);
254 --
255 hr_utility.set_message('801','PAY_7694_PER_NO_DEL_STRUCTURE');
256 hr_utility.raise_error;
257 exception
258 when no_data_found then
259 null;
260 end;
261 end if;
262 end pre_delete_checks;
263 -----------------------------------------------------------------------------
264 PROCEDURE update_copies(X_Pos_Structure_Version_Id NUMBER) IS
265 --
266 --
267 --
268 cursor ele_update is
269 select rowid
270 from per_pos_structure_versions psv
271 where psv.copy_structure_version_id = X_Pos_Structure_Version_Id
272 for update of psv.copy_structure_version_id nowait;
273 --
274 -- Local Variables
275 --
276 l_Rowid VARCHAR2(20);
277 begin
278 open ele_update;
279 fetch ele_update into l_Rowid;
280 loop
281 exit when ele_update%NOTFOUND;
282 update per_pos_structure_versions psv
283 set psv.copy_structure_version_id = NULL
284 where psv.rowid = l_Rowid;
285 --
286 fetch ele_update into l_Rowid;
287 end loop;
288 close ele_update;
289 end;
290 -----------------------------------------------------------------------------
291 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
292 X_Pos_Structure_Version_Id IN OUT NOCOPY NUMBER,
293 X_Business_Group_Id NUMBER,
294 X_Position_Structure_Id NUMBER,
295 X_Date_From DATE,
296 X_Version_Number NUMBER,
297 X_Copy_Structure_Version_Id NUMBER ,
298 X_Date_To DATE ,
299 X_end_of_time DATE,
300 X_Next_no_free IN OUT NOCOPY NUMBER,
301 X_closedown_warning IN OUT NOCOPY VARCHAR2,
302 X_gap_warning IN OUT NOCOPY VARCHAR2
303 ) IS
304 CURSOR C IS SELECT rowid FROM PER_POS_STRUCTURE_VERSIONS
305 WHERE pos_structure_version_id = X_Pos_Structure_Version_Id;
306 CURSOR C2 IS SELECT per_pos_structure_versions_s.nextval FROM sys.dual;
307 BEGIN
308 --
309 PER_POS_STRUCTURE_VERSIONS_PKG.check_version_number(
310 X_Position_Structure_Id=> X_Position_Structure_Id
311 ,X_Version_Number => X_Version_Number
312 ,X_Rowid => X_Rowid);
313 --
314 PER_POS_STRUCTURE_VERSIONS_PKG.check_overlap(
315 X_Position_Structure_Id=> X_Position_Structure_Id
316 ,X_Rowid => X_Rowid
317 ,X_Date_From => X_Date_From
318 ,X_Date_To => X_Date_To
319 ,X_End_Of_Time => X_End_Of_Time
320 ,X_End_Date_Closedown => X_closedown_warning);
321 --
322 PER_POS_STRUCTURE_VERSIONS_PKG.check_date_gap(X_Date_From => X_Date_From
323 ,X_Date_To => X_Date_To
324 ,X_gap_warning => X_gap_warning
325 ,X_Position_Structure_Id =>X_Position_Structure_Id
326 ,X_Rowid => X_Rowid);
327 --
328 if (X_Pos_Structure_Version_Id is NULL) then
329 OPEN C2;
330 FETCH C2 INTO X_Pos_Structure_Version_Id;
331 CLOSE C2;
332 end if;
333 INSERT INTO PER_POS_STRUCTURE_VERSIONS(
334 pos_structure_version_id,
335 business_group_id,
336 position_structure_id,
337 date_from,
338 version_number,
339 copy_structure_version_id,
340 date_to
341 ) VALUES (
342 X_Pos_Structure_Version_Id,
343 X_Business_Group_Id,
344 X_Position_Structure_Id,
345 X_Date_From,
346 X_Version_Number,
347 X_Copy_Structure_Version_Id,
348 X_Date_To
349 );
350 OPEN C;
351 FETCH C INTO X_Rowid;
352 if (C%NOTFOUND) then
353 CLOSE C;
354 RAISE NO_DATA_FOUND;
355 end if;
356 CLOSE C;
357 if X_Copy_Structure_Version_Id is not null then
358 PER_POS_STRUCTURE_VERSIONS_PKG.copy_elements(
359 X_Copy_Structure_Version_Id => X_Copy_Structure_Version_Id
360 ,X_Pos_Structure_Version_Id => X_Pos_Structure_Version_Id);
361 end if;
362 X_Next_no_free :=PER_POS_STRUCTURE_VERSIONS_PKG.get_next_free_no(
363 X_Position_Structure_Id =>X_Position_Structure_Id);
364 end Insert_Row;
365 -----------------------------------------------------------------------------
366 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
367 X_Pos_Structure_Version_Id NUMBER,
368 X_Business_Group_Id NUMBER,
369 X_Position_Structure_Id NUMBER,
370 X_Date_From DATE,
371 X_Version_Number NUMBER,
372 X_Copy_Structure_Version_Id NUMBER ,
373 X_Date_To DATE
374 ) IS
375 CURSOR C IS
376 SELECT *
377 FROM PER_POS_STRUCTURE_VERSIONS
378 WHERE rowid = X_Rowid
379 FOR UPDATE of Pos_Structure_Version_Id NOWAIT;
380 Recinfo C%ROWTYPE;
381 BEGIN
382 OPEN C;
383 FETCH C INTO Recinfo;
384 if (C%NOTFOUND) then
385 CLOSE C;
386 RAISE NO_DATA_FOUND;
387 end if;
388 CLOSE C;
389 if (
390 ( (Recinfo.pos_structure_version_id = X_Pos_Structure_Version_Id)
391 OR ( (Recinfo.pos_structure_version_id IS NULL)
392 AND (X_Pos_Structure_Version_Id IS NULL)))
393 AND ( (Recinfo.business_group_id = X_Business_Group_Id)
394 OR ( (Recinfo.business_group_id IS NULL)
395 AND (X_Business_Group_Id IS NULL)))
396 AND ( (Recinfo.position_structure_id = X_Position_Structure_Id)
397 OR ( (Recinfo.position_structure_id IS NULL)
398 AND (X_Position_Structure_Id IS NULL)))
399 AND ( (Recinfo.date_from = X_Date_From)
400 OR ( (Recinfo.date_from IS NULL)
401 AND (X_Date_From IS NULL)))
402 AND ( (Recinfo.version_number = X_Version_Number)
403 OR ( (Recinfo.version_number IS NULL)
404 AND (X_Version_Number IS NULL)))
405 AND ( (Recinfo.copy_structure_version_id = X_Copy_Structure_Version_Id)
406 OR ( (Recinfo.copy_structure_version_id IS NULL)
407 AND (X_Copy_Structure_Version_Id IS NULL)))
408 AND ( (Recinfo.date_to = X_Date_To)
409 OR ( (Recinfo.date_to IS NULL)
410 AND (X_Date_To IS NULL)))
411 ) then
412 return;
413 else
414 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
415 APP_EXCEPTION.RAISE_EXCEPTION;
416 end if;
417 END Lock_Row;
418 -----------------------------------------------------------------------------
419 PROCEDURE Update_Row(X_Rowid VARCHAR2,
420 X_Pos_Structure_Version_Id NUMBER,
421 X_Business_Group_Id NUMBER,
422 X_Position_Structure_Id NUMBER,
423 X_Date_From DATE,
424 X_Version_Number NUMBER,
425 X_Copy_Structure_Version_Id NUMBER ,
426 X_Date_To DATE ,
427 X_end_of_time DATE,
428 X_Next_no_free IN OUT NOCOPY NUMBER,
429 X_closedown_warning IN OUT NOCOPY VARCHAR2,
430 X_gap_warning IN OUT NOCOPY VARCHAR2
431 ) IS
432 BEGIN
433 PER_POS_STRUCTURE_VERSIONS_PKG.check_version_number(
434 X_Position_Structure_Id=> X_Position_Structure_Id
435 ,X_Version_Number => X_Version_Number
436 ,X_Rowid => X_Rowid);
437 --
438 PER_POS_STRUCTURE_VERSIONS_PKG.check_overlap(
439 X_Position_Structure_Id=> X_Position_Structure_Id
440 ,X_Rowid => X_Rowid
441 ,X_Date_From => X_Date_From
442 ,X_Date_To => X_Date_To
443 ,X_End_Of_Time => X_End_Of_Time
444 ,X_End_Date_Closedown => X_closedown_warning);
445 --
446 PER_POS_STRUCTURE_VERSIONS_PKG.check_date_gap(X_Date_From => X_Date_From
447 ,X_Date_To => X_Date_To
448 ,X_gap_warning => X_gap_warning
449 ,X_Position_Structure_Id =>X_Position_Structure_Id
450 ,X_Rowid => X_Rowid);
451 --
452 UPDATE PER_POS_STRUCTURE_VERSIONS
453 SET
454 pos_structure_version_id = X_Pos_Structure_Version_Id,
455 business_group_id = X_Business_Group_Id,
456 position_structure_id = X_Position_Structure_Id,
457 date_from = X_Date_From,
458 version_number = X_Version_Number,
459 copy_structure_version_id = X_Copy_Structure_Version_Id,
460 date_to = X_Date_To
461 WHERE rowid = X_rowid;
462 if (SQL%NOTFOUND) then
463 RAISE NO_DATA_FOUND;
464 end if;
465 X_Next_no_free :=PER_POS_STRUCTURE_VERSIONS_PKG.get_next_free_no(
466 X_Position_Structure_Id =>X_Position_Structure_Id);
467 END Update_Row;
468 -----------------------------------------------------------------------------
469 PROCEDURE Delete_Row(X_Rowid VARCHAR2,
470 X_Pos_Structure_Version_Id NUMBER,
471 X_Business_Group_Id NUMBER,
472 X_Position_Structure_Id NUMBER,
473 X_Hr_Installed VARCHAR2,
474 X_Next_no_free IN OUT NOCOPY NUMBER,
475 X_closedown_warning IN OUT NOCOPY VARCHAR2) IS
476 BEGIN
477 pre_delete_checks(X_Pos_Structure_Version_Id => X_Pos_Structure_Version_Id,
478 X_Business_Group_Id => X_Business_Group_Id,
479 X_Position_Structure_Id => X_Position_Structure_Id,
480 X_Hr_Installed => X_Hr_Installed);
481 --
482 DELETE FROM PER_POS_STRUCTURE_VERSIONS
483 WHERE rowid = X_Rowid;
484 if (SQL%NOTFOUND) then
485 RAISE NO_DATA_FOUND;
486 end if;
487 X_Next_no_free :=PER_POS_STRUCTURE_VERSIONS_PKG.get_next_free_no(
488 X_Position_Structure_Id =>X_Position_Structure_Id);
489 END Delete_Row;
490 -----------------------------------------------------------------------------
491 END PER_POS_STRUCTURE_VERSIONS_PKG;