1 PACKAGE BODY PER_ORG_STRUCTURE_VERSIONS_PKG as
2 /* $Header: peosv01t.pkb 115.6 2002/12/06 16:34:45 pkakar ship $ */
3 -------------------------------------------------------------------------------
4 FUNCTION get_next_free_no(p_Org_Structure_Version_Id NUMBER) return NUMBER is
5 --
6 -- Gets the next free number in an Organization Hierarchy.
7 --
8 l_next_no number; -- Next free version number in a hierarchy.
9 --
10 begin
11 --
12 -- No exception.
13 -- Will always return 1 row irrespective of p_Org_Structure_Version_Id
14 -- Existing or not.
15 --
16 select nvl(max(osv.version_number), 0) + 1
17 into l_next_no
18 from per_org_structure_versions osv
19 where osv.organization_structure_id = p_Org_Structure_Version_Id;
20 --
21 return l_next_no;
22 end get_next_free_no;
23 -------------------------------------------------------------------------------
24 PROCEDURE check_date_gaps(p_org_structure_id NUMBER
25 ,p_date_to DATE
26 ,p_date_from DATE
27 ,p_rowid VARCHAR2
28 ,p_gap_warning in out nocopy VARCHAR2) is
29 --
30 -- Test for Gaps between hierarchy versions.
31 -- i.e. 1 10-Feb-94 16-Feb-94
32 -- 2 20-Mar-94
33 -- would flag a message as gap exists.
34 --
35 l_max_end DATE; -- Maximum end date for the Hierarchy.
36 l_min_start DATE; -- Minimum start date for the hierarchy.
37 --
38 begin
39 p_gap_warning := 'N';
40 select max(osv.date_to)
41 into l_max_end
42 from per_org_structure_versions osv
43 where osv.organization_structure_id = p_org_structure_id
44 and osv.date_from < p_date_from
45 and (osv.rowid <> p_rowid
46 or p_rowid is null);
47 --
48 if (l_max_end is not null and p_date_from = (l_max_end + 1))
49 or (l_max_end is null) then
50 select min(osv.date_from)
51 into l_min_start
52 from per_org_structure_versions osv
53 where osv.organization_structure_id = p_org_structure_id
54 and osv.date_from > p_date_from
55 and (osv.rowid <> p_rowid
56 or p_rowid is null);
57 --
58 if l_min_start is null then
59 return;
60 elsif (p_date_to + 1) = l_min_start then
61 return;
62 end if;
63 end if;
64 p_gap_warning := 'Y';
65 end check_date_gaps;
66 -------------------------------------------------------------------------------
67 PROCEDURE check_version_number(p_org_structure_id NUMBER
68 ,p_version_number NUMBER
69 ,p_rowid VARCHAR2) is
70 --
71 -- Enusre the version number does not exist.
72 --
73 l_dummy VARCHAR2(1);
74 --
75 begin
76 select null
77 into l_dummy
78 from sys.dual
79 where exists (select 1
80 from per_org_structure_versions osv
81 where osv.organization_structure_id = p_org_structure_id
82 and osv.version_number = p_version_number
83 and (osv.rowid <> p_rowid
84 or p_rowid is null));
85 --
86 hr_utility.set_message('801','HR_6077_PO_POS_DUP_VER');
87 hr_utility.raise_error;
88 --
89 exception
90 when no_data_found then
91 null;
92 end check_version_number;
93 -------------------------------------------------------------------------------
94 PROCEDURE check_overlap(p_org_structure_id NUMBER
95 ,p_rowid VARCHAR2
96 ,p_date_from DATE
97 ,p_date_to DATE
98 ,p_end_of_time DATE
99 ,p_end_date_closedown in out nocopy VARCHAR2) is
100
101 --
102 -- Check for overlapping structure versions
103 --
104 l_dummy VARCHAR2(1);
105 --
106 begin
107 --
108 p_end_date_closedown := 'N';
109 --
110 begin
111 select null
112 into l_dummy
113 from sys.dual
114 where exists
115 (select 1
116 from per_org_structure_versions osv
117 where osv.organization_structure_id = p_org_structure_id
118 and p_date_from > osv.date_from
119 and osv.date_to is null);
120 --
121 -- If none exist it will exit normally
122 --
123 begin
124 --
125 -- Close down the open structures, before doing the test for overlaps
126 --
127 update per_org_structure_versions osv
128 set osv.date_to = (p_date_from - 1)
129 where osv.organization_structure_id = p_org_structure_id
130 and osv.date_to is null
131 and (osv.rowid <> p_rowid
132 or p_rowid is null);
133 --
134 if sql%rowcount <>0 then
135 p_end_date_closedown := 'Y';
136 end if;
137 end;
138 exception
139 when no_data_found then
140 null;
141 end;
142 begin
143 --
144 -- test for overlapping rows.
145 --
146 select null
147 into l_dummy
148 from sys.dual
149 where exists
150 (select 1
151 from per_org_structure_versions osv
152 where osv.date_from <= nvl(p_date_to,
153 p_end_of_time)
154 and nvl(osv.date_to, p_end_of_time)
155 >= p_date_from
156 and osv.organization_structure_id = p_org_structure_id
157 and (osv.rowid <> p_rowid
158 or p_rowid is null));
159 --
160 hr_utility.set_message('801','HR_6076_PO_POS_OVERLAP');
161 hr_utility.raise_error;
162 --
163 end;
164 exception
165 when no_data_found then
166 null;
167 end check_overlap;
168 -------------------------------------------------------------------------------
169 PROCEDURE check_position_flag (
170 p_organization_structure_id NUMBER
171 ,p_pos_control_enabled_flag VARCHAR2) is
172
173 cursor c1 is
174 select str.position_control_structure_flg
175 from per_organization_structures str
176 where str.organization_structure_id = p_organization_structure_id;
177
178 l_result varchar2(10);
179
180 begin
181 --
182 if p_pos_control_enabled_flag = 'Y' then
183 --
184 open c1;
185 fetch c1 into l_result;
186
187 if c1%found then
188 --
189 if l_result <> 'Y' then
190 --
191 close c1;
192 hr_utility.set_message('800','PER_50055_NON_POS_CTRL_STRUCT');
193 hr_utility.raise_error;
194 --
195 end if;
196 --
197 end if;
198
199 close c1;
200 --
201 end if;
202 --
203 end check_position_flag;
204 --------------------------------------------------------------------------------
205 PROCEDURE copy_elements(p_org_structure_version_id NUMBER
206 ,p_copy_structure_version_id NUMBER) IS
207 --
208 --
209 -- Define cursor for inserts
210 --
211 cursor struct_element is select *
212 from per_org_structure_elements ose
213 where ose.org_structure_version_id = p_copy_structure_version_id;
214 --
215 -- Local Variable
216 --
217 ele_record struct_element%ROWTYPE;
218 l_rowid VARCHAR2(20);
219 begin
220 open struct_element;
221 fetch struct_element into ele_record;
222 loop
223 exit when struct_element%NOTFOUND;
224 INSERT INTO PER_ORG_STRUCTURE_ELEMENTS(
225 org_structure_element_id,
226 business_group_id,
227 organization_id_parent,
228 org_structure_version_id,
229 organization_id_child
230 ) VALUES (
231 PER_ORG_STRUCTURE_ELEMENTS_S.NEXTVAL,
232 ele_record.Business_Group_Id,
233 ele_record.Organization_Id_Parent,
234 p_org_structure_version_id,
235 ele_record.Organization_Id_Child
236 );
237 --
238 fetch struct_element into ele_record;
239 end loop;
240 close struct_element;
241 end copy_elements;
242 ------------------------------------------------------------------------------
243 PROCEDURE Insert_Row(p_Rowid IN OUT NOCOPY VARCHAR2,
244 p_Org_Structure_Version_Id IN OUT NOCOPY NUMBER,
245 p_Business_Group_Id NUMBER,
246 p_Organization_Structure_Id NUMBER,
247 p_Date_From DATE,
248 p_Version_Number NUMBER,
249 p_Copy_Structure_Version_Id NUMBER,
250 p_Date_To DATE,
251 p_Pos_Ctrl_Enabled_Flag VARCHAR2,
252 p_end_of_time DATE,
253 p_Next_no_free IN OUT NOCOPY NUMBER,
254 p_closedown_warning IN OUT NOCOPY VARCHAR2,
255 p_gap_warning IN OUT NOCOPY VARCHAR2
256 ) IS
257 --
258 CURSOR C IS SELECT rowid
259 FROM PER_ORG_STRUCTURE_VERSIONS
260 WHERE org_structure_version_id = p_Org_Structure_Version_Id;
261 CURSOR C2 IS SELECT per_org_structure_versions_s.nextval
262 FROM sys.dual;
263 --
264 BEGIN
265 PER_ORG_STRUCTURE_VERSIONS_PKG.check_version_number(
266 p_org_structure_id => p_Organization_Structure_Id
267 ,p_version_number => p_version_number
268 ,p_rowid => p_rowid);
269 --
270 PER_ORG_STRUCTURE_VERSIONS_PKG.check_overlap(
271 p_org_structure_id => p_Organization_Structure_Id
272 ,p_rowid => p_rowid
273 ,p_date_from => p_date_from
274 ,p_date_to => p_date_to
275 ,p_end_of_time =>p_end_of_time
276 ,p_end_date_closedown => p_closedown_warning);
277 --
278 PER_ORG_STRUCTURE_VERSIONS_PKG.check_date_gaps(
279 p_org_structure_id=> p_Organization_Structure_Id
280 ,p_rowid => p_rowid
281 ,p_date_from => p_date_from
282 ,p_date_to => p_date_to
283 ,p_gap_warning =>p_gap_warning);
284 --
285 check_position_flag(
286 p_organization_structure_id => p_organization_structure_id
287 ,p_pos_control_enabled_flag => p_pos_ctrl_enabled_flag);
288 --
289
290 if (p_Org_Structure_Version_Id is NULL) then
291 OPEN C2;
292 FETCH C2 INTO p_Org_Structure_Version_Id;
293 CLOSE C2;
294 end if;
295 INSERT INTO PER_ORG_STRUCTURE_VERSIONS(
296 org_structure_version_id,
297 business_group_id,
298 organization_structure_id,
299 date_from,
300 version_number,
301 copy_structure_version_id,
302 date_to,
303 topnode_pos_ctrl_enabled_flag
304 ) VALUES (
305 p_Org_Structure_Version_Id,
306 p_Business_Group_Id,
307 p_Organization_Structure_Id,
308 p_Date_From,
309 p_Version_Number,
310 p_Copy_Structure_Version_Id,
311 p_Date_To,
312 p_Pos_Ctrl_Enabled_Flag
313 );
314 OPEN C;
315 FETCH C INTO p_Rowid;
316 if (C%NOTFOUND) then
317 CLOSE C;
318 RAISE NO_DATA_FOUND;
319 end if;
320 CLOSE C;
321 if p_copy_structure_version_id is not null then
322 copy_elements(p_org_structure_version_id => p_Org_Structure_Version_Id
323 ,p_copy_structure_version_id =>p_Copy_Structure_Version_Id);
324 end if;
325 p_Next_no_free:=get_next_free_no(p_Organization_Structure_Id);
326 END Insert_Row;
327 ------------------------------------------------------------------------------
331 p_Organization_Structure_Id NUMBER,
328 PROCEDURE Lock_Row(p_Rowid VARCHAR2,
329 p_Org_Structure_Version_Id NUMBER,
330 p_Business_Group_Id NUMBER,
332 p_Date_From DATE,
333 p_Version_Number NUMBER,
334 p_Copy_Structure_Version_Id NUMBER,
335 p_Date_To DATE,
336 p_Pos_Ctrl_Enabled_Flag VARCHAR2
337
338 ) IS
339 CURSOR C IS
340 SELECT *
341 FROM PER_ORG_STRUCTURE_VERSIONS
342 WHERE rowid = p_Rowid
343 FOR UPDATE of Org_Structure_Version_Id NOWAIT;
344 Recinfo C%ROWTYPE;
345 --
346 BEGIN
347 OPEN C;
348 FETCH C INTO Recinfo;
349 if (C%NOTFOUND) then
350 CLOSE C;
351 RAISE NO_DATA_FOUND;
352 end if;
353 CLOSE C;
354 if (
355 ( (Recinfo.org_structure_version_id = p_Org_Structure_Version_Id)
356 OR ( (Recinfo.org_structure_version_id IS NULL)
357 AND (p_Org_Structure_Version_Id IS NULL)))
358 AND ( (Recinfo.business_group_id = p_Business_Group_Id)
359 OR ( (Recinfo.business_group_id IS NULL)
360 AND (p_Business_Group_Id IS NULL)))
361 AND ( (Recinfo.organization_structure_id = p_Organization_Structure_Id)
362 OR ( (Recinfo.organization_structure_id IS NULL)
363 AND (p_Organization_Structure_Id IS NULL)))
364 AND ( (Recinfo.date_from = p_Date_From)
365 OR ( (Recinfo.date_from IS NULL)
366 AND (p_Date_From IS NULL)))
367 AND ( (Recinfo.version_number = p_Version_Number)
368 OR ( (Recinfo.version_number IS NULL)
369 AND (p_Version_Number IS NULL)))
370 AND ( (Recinfo.copy_structure_version_id = p_Copy_Structure_Version_Id)
371 OR ( (Recinfo.copy_structure_version_id IS NULL)
372 AND (p_Copy_Structure_Version_Id IS NULL)))
373 AND ( (Recinfo.topnode_pos_ctrl_enabled_flag = p_pos_ctrl_enabled_flag)
374 OR ( (Recinfo.topnode_pos_ctrl_enabled_flag IS NULL)
375 AND (p_pos_ctrl_enabled_flag IS NULL)))
376 AND ( (Recinfo.date_to = p_Date_To)
377 OR ( (Recinfo.date_to IS NULL)
378 AND (p_Date_To IS NULL)))
379 ) then
380 return;
381 else
382 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
383 APP_EXCEPTION.RAISE_EXCEPTION;
384 end if;
385 END Lock_Row;
386 ------------------------------------------------------------------------------
387 PROCEDURE Update_Row(p_Rowid VARCHAR2,
388 p_Org_Structure_Version_Id NUMBER,
389 p_Business_Group_Id NUMBER,
390 p_Organization_Structure_Id NUMBER,
391 p_Date_From DATE,
392 p_Version_Number NUMBER,
393 p_Copy_Structure_Version_Id NUMBER,
394 p_Date_To DATE,
395 p_Pos_Ctrl_Enabled_Flag VARCHAR2,
396 p_end_of_time DATE,
397 p_Next_no_free IN OUT NOCOPY NUMBER,
398 p_closedown_warning IN OUT NOCOPY VARCHAR2,
399 p_gap_warning IN OUT NOCOPY VARCHAR2
400 ) IS
401 BEGIN
402 --
403 PER_ORG_STRUCTURE_VERSIONS_PKG.check_version_number(
404 p_org_structure_id => p_Organization_Structure_Id
405 ,p_version_number => p_version_number
406 ,p_rowid => p_rowid);
407 --
408 PER_ORG_STRUCTURE_VERSIONS_PKG.check_overlap(
409 p_org_structure_id => p_Organization_Structure_Id
410 ,p_rowid => p_rowid
411 ,p_date_from => p_date_from
412 ,p_date_to => p_date_to
413 ,p_end_of_time =>p_end_of_time
414 ,p_end_date_closedown => p_closedown_warning);
415 --
416 check_position_flag(
417 p_organization_structure_id => p_organization_structure_id
418 ,p_pos_control_enabled_flag => p_pos_ctrl_enabled_flag);
419 --
420
421 UPDATE PER_ORG_STRUCTURE_VERSIONS
422 SET
423 org_structure_version_id = p_Org_Structure_Version_Id,
424 business_group_id = p_Business_Group_Id,
425 organization_structure_id = p_Organization_Structure_Id,
426 date_from = p_Date_From,
427 version_number = p_Version_Number,
428 copy_structure_version_id = p_Copy_Structure_Version_Id,
429 date_to = p_Date_To,
430 topnode_pos_ctrl_enabled_flag = p_Pos_Ctrl_Enabled_Flag
431 WHERE rowid = p_rowid;
432 if (SQL%NOTFOUND) then
433 RAISE NO_DATA_FOUND;
434 end if;
435 p_Next_no_free:=get_next_free_no(p_Organization_Structure_Id);
436 END Update_Row;
437 ------------------------------------------------------------------------------
438 --
439 --
440 PROCEDURE pre_delete_checks(p_org_Structure_Version_Id NUMBER,
441 p_Pa_Installed VARCHAR2) is
442 --
443 -- Ensure that there are no child records existing for this
444 -- version. Oracle 7's constraints will handle this but it is rather
445 -- unfriendly to the user.
446 --
447 l_dummy VARCHAR2(1);
448 begin
449 begin
450 select null
451 into l_dummy
452 from sys.dual
453 where exists ( select 1
454 from per_org_structure_elements ose
458 hr_utility.set_message('801','HR_6204_ORG_SUBORD_EXIST');
455 where ose.org_structure_version_id = p_org_Structure_Version_Id
456 );
457 --
459 hr_utility.raise_error;
460 --
461 exception
462 when no_data_found then
463 null;
464 end;
465 if p_Pa_Installed = 'I' then
466 pa_org.pa_osv_predel_validation(p_org_Structure_Version_Id);
467 end if;
468 end pre_delete_checks;
469 ------------------------------------------------------------------------------
470 PROCEDURe update_copied_versions(p_org_Structure_Version_Id NUMBER) is
471 --
472 -- If a version has been deleted and its structure has been copied by
473 -- another version then null this reference.
474 --
475 cursor update_osv is
476 select rowid
477 from per_org_structure_versions osv
478 where osv.copy_structure_version_id = p_org_Structure_Version_Id
479 for update of osv.copy_structure_version_id nowait;
480 --
481 l_copied_rowid ROWID;
482 --
483 begin
484 --
485 open update_osv;
486 loop
487 fetch update_osv into l_copied_rowid;
488 exit when update_osv%NOTFOUND;
489 --
490 update per_org_structure_versions osv
491 set osv.copy_structure_version_id = ''
492 where rowid = l_copied_rowid;
493 end loop;
494 close update_osv;
495 end;
496 ------------------------------------------------------------------------------
497 PROCEDURE Delete_Row(p_Rowid VARCHAR2,
498 p_Organization_Structure_Id NUMBER,
499 p_org_Structure_Version_Id NUMBER,
500 p_Pa_Installed VARCHAR2,
501 p_Date_From DATE,
502 p_Date_To DATE,
503 p_gap_warning IN OUT NOCOPY VARCHAR2,
504 p_Next_no_free IN OUT NOCOPY NUMBER) IS
505 BEGIN
506 pre_delete_checks(p_org_Structure_Version_Id => p_org_Structure_Version_Id,
507 p_Pa_Installed => p_Pa_Installed);
508 --
509 DELETE FROM PER_ORG_STRUCTURE_VERSIONS
510 WHERE rowid = p_Rowid;
511 if (SQL%NOTFOUND) then
512 RAISE NO_DATA_FOUND;
513 end if;
514 --
515 update_copied_versions(
519 p_org_structure_id=> p_Organization_Structure_Id
516 p_org_Structure_Version_Id =>p_org_Structure_Version_Id);
517 --
518 PER_ORG_STRUCTURE_VERSIONS_PKG.check_date_gaps(
520 ,p_rowid => p_rowid
521 ,p_date_from => p_date_from
522 ,p_date_to => p_date_to
523 ,p_gap_warning =>p_gap_warning);
524 --
525 p_Next_no_free:=get_next_free_no(p_Organization_Structure_Id);
526 END Delete_Row;
527 ------------------------------------------------------------------------------
528 END PER_ORG_STRUCTURE_VERSIONS_PKG;
[Home] [Help]