[Home] [Help]
PACKAGE BODY: APPS.PER_POSITION_STRUCTURES_PKG
Source
1 PACKAGE BODY PER_POSITION_STRUCTURES_PKG as
2 /* $Header: pepst01t.pkb 120.1 2006/01/11 04:15:38 asahay noship $ */
3
4 procedure form_startup(p_business_group_id NUMBER,
5 p_security_profile_id IN OUT NOCOPY NUMBER,
6 p_view_all_poss IN OUT NOCOPY VARCHAR2,
7 p_hr_installed IN OUT NOCOPY VARCHAR2,
8 p_pa_installed IN OUT NOCOPY VARCHAR2) is
9 l_industry VARCHAR2(1);
10 l_installed BOOLEAN;
11 --
12 function get_hr_status return VARCHAR2 is
13 --
14 l_hr_installed varchar2(1);
15 begin
16 select 'I'
17 into l_hr_installed
18 from sys.dual
19 where exists (select 'I'
20 from fnd_product_installations
21 where application_id between 800 and 899
22 and status = 'I');
23 return l_hr_installed;
24 exception
25 when no_data_found then
26 return 'S';
27 end;
28 --
29 begin
30 -- Get PA's installation status
31 l_installed :=fnd_installation.get(appl_id => 275
32 ,dep_appl_id => 275
33 ,status => p_pa_installed
34 ,industry => l_industry);
35 --
36 -- Get HR installation Status
37 p_hr_installed := get_hr_status;
38 --
39 -- Now the security Profile.
40 --
41 -- Bug 462590. Cursor below was picking up the wrong flag.
42 --
43 if p_hr_installed = 'I' then
44 begin
45 select psp.view_all_positions_flag
46 , psp.security_profile_id
47 into p_view_all_poss
48 , p_security_profile_id
49 from per_security_profiles psp
50 -- where psp.security_profile_id = hr_security.get_security_profile
51 where psp.security_profile_id = fnd_profile.value('PER_SECURITY_PROFILE_ID')
52 and (psp.business_group_id + 0 = p_Business_group_id
53 or psp.business_group_id is null);
54
55 --
56 exception
57 when NO_DATA_FOUND then
58 -- fnd_message.set_name('PER', 'HR_289296_SEC_PROF_SETUP_ERR');
59 fnd_message.set_name('PER', 'HR_289521_GLOBAL_SEC_PROFILE');
60 fnd_message.raise_error;
61 end;
62 else
63 p_view_all_poss := 'Y';
64 p_security_profile_id := 0;
65 end if;
66 end form_startup;
67
68 PROCEDURE check_name_unique(X_Rowid VARCHAR2,
69 X_Name VARCHAR2,
70 X_Business_group_id NUMBER) IS
71 -- Local variables
72 --
73 l_duplicate_name VARCHAR2(20);
74 begin
75 select 'Duplicate exists'
76 into l_duplicate_name
77 from sys.dual
78 where exists (
79 select 1
80 from per_position_structures psp
81 where (psp.rowid <> X_Rowid
82 or X_Rowid is null)
83 and upper(psp.name) = upper(X_Name)
84 and psp.business_group_id + 0 = X_Business_group_id);
85 fnd_message.set_name('PAY', 'PER_7901_SYS_DUPLICATE_RECORDS');
86 fnd_message.raise_error;
87 exception
88 when no_data_found then
89 null;
90 when others then
91 raise;
92 end;
93
94 PROCEDURE check_primary_flag(X_Rowid VARCHAR2,
95 X_Primary_flag VARCHAR2,
96 X_Business_group_id NUMBER) IS
97 --
98 -- Local Variable
99 --
100 l_primary_exists VARCHAR2(20);
101 begin
102 if X_Primary_flag = 'Y' then
103 begin
104 select 'Primary Exists'
105 into l_primary_exists
106 from sys.dual
107 where exists (
108 select 1
109 from per_position_structures psp
110 where (psp.rowid <> X_Rowid
111 or X_Rowid is null)
112 and psp.primary_position_flag = 'Y'
113 and psp.business_group_id + 0 = X_Business_group_id);
114 --
115 hr_utility.set_message('801', 'HR_6085_PO_POS_ONE_PRIMARY');
116 hr_utility.raise_error;
117 --
118 exception
119 when no_data_found then
120 null;
121 when others then
122 raise;
123 end;
124 end if;
125 end;
126
127 PROCEDURE pre_delete_checks(X_Position_Structure_Id NUMBER,
128 X_Business_Group_Id NUMBER,
129 X_Po_Installed VARCHAR2,
130 X_Hr_Installed VARCHAR2) IS
131 --
132 --
133 -- Local variable
134 --
135 l_exists VARCHAR2(20);
136 l_sql_cursor NUMBER;
137 l_sql_text VARCHAR2(2000);
138 l_oci_out VARCHAR2(1);
139 l_rows_fetched varchar2(1);
140 Begin
141 begin
142 select 'Versions Exist'
143 into l_exists
144 from sys.dual
145 where exists(
146 select 1
147 from per_pos_structure_versions psv
148 where psv.position_structure_id = X_Position_Structure_Id);
149 --
150 fnd_message.set_name('PAY','HR_6084_PO_POS_HAS_HIER_VER');
151 fnd_message.raise_error;
152 --
153 exception
154 when no_data_found then
155 null;
156 when others then
157 raise;
158 end;
159 if X_Hr_Installed = 'Y' then
160 begin
161 select 'Security exists'
162 into l_exists
163 from sys.dual
164 where exists (
165 select null
166 from per_security_profiles sec
167 where sec.business_group_id + 0 = X_Business_Group_Id
168 and sec.position_structure_id = X_Position_Structure_Id);
169 --
170 fnd_message.set_name('PAY','PAY_7694_PER_NO_DEL_STRUCTURE');
171 fnd_message.raise_error;
172 --
173 exception
174 when no_data_found then
175 null;
176 when others then
177 raise;
178 end;
179 end if;
180 --
181 if X_Po_Installed = 'I' then
182 -- run the PO stuff
183 -- Dynamic SQL cursor to get round the problem of Table not existing.
184 -- Shouldn't be a problem after 10.6, but better safe than sorry.
185 -- This uses a similar method to OCI but Via PL/SQL instead.
186 --
187 begin
188 l_sql_text := 'select null '
189 ||'from sys.dual '
190 ||'where exists( select null '
191 ||' from po_system_parameters '
192 ||' where security_position_structure_id = '
193 ||to_char(X_Position_Structure_Id)
194 ||' ) '
195 ||'or exists( select null '
196 ||' from po_employee_hierarchies '
197 ||' where position_structure_id = '||to_char(X_Position_Structure_Id)
198 ||' ) '
199 ||'or exists( select null '
200 ||' from po_action_history '
201 ||' where approval_path_id = '||to_char(X_Position_Structure_Id)
202 ||' ) '
203 ||'or exists( select null '
204 ||' from po_document_types '
205 ||' where default_approval_path_id = '
206 ||to_char(X_Position_Structure_Id)
207 ||' ) ';
208 --
209 -- Open Cursor for Processing Sql statment.
210 --
211 l_sql_cursor := dbms_sql.open_cursor;
212 --
213 -- Parse SQL statement.
214 --
215 dbms_sql.parse(l_sql_cursor, l_sql_text, dbms_sql.v7);
216 --
217 -- Map the local variables to each returned Column
218 --
219 dbms_sql.define_column(l_sql_cursor, 1,l_oci_out,1);
220 --
221 -- Execute the SQL statement.
222 --
223 l_rows_fetched := dbms_sql.execute(l_sql_cursor);
224 --
225 if (dbms_sql.fetch_rows(l_sql_cursor) > 0)
226 then
227 fnd_message.set_name('PER','HR_6048_PO_POS_DEL_POS_CONT');
228 fnd_message.raise_error;
229 end if;
230 --
231 -- Close cursor used for processing SQL statement.
232 --
233 dbms_sql.close_cursor(l_sql_cursor);
234 end;
235 end if;
236 end;
237
238 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
239 X_Position_Structure_Id IN OUT NOCOPY NUMBER,
240 X_Business_Group_Id NUMBER,
241 X_Name VARCHAR2,
242 X_Comments VARCHAR2 ,
243 X_Primary_Position_Flag VARCHAR2 ,
244 X_Attribute_Category VARCHAR2 ,
245 X_Attribute1 VARCHAR2 ,
246 X_Attribute2 VARCHAR2 ,
247 X_Attribute3 VARCHAR2 ,
248 X_Attribute4 VARCHAR2 ,
249 X_Attribute5 VARCHAR2 ,
250 X_Attribute6 VARCHAR2 ,
251 X_Attribute7 VARCHAR2 ,
252 X_Attribute8 VARCHAR2 ,
253 X_Attribute9 VARCHAR2 ,
254 X_Attribute10 VARCHAR2 ,
255 X_Attribute11 VARCHAR2 ,
256 X_Attribute12 VARCHAR2 ,
257 X_Attribute13 VARCHAR2 ,
258 X_Attribute14 VARCHAR2 ,
259 X_Attribute15 VARCHAR2 ,
260 X_Attribute16 VARCHAR2 ,
261 X_Attribute17 VARCHAR2 ,
262 X_Attribute18 VARCHAR2 ,
263 X_Attribute19 VARCHAR2 ,
264 X_Attribute20 VARCHAR2
265 ) IS
266 CURSOR C IS SELECT rowid FROM PER_POSITION_STRUCTURES
267
268 WHERE position_structure_id = X_Position_Structure_Id;
269 CURSOR C2 IS SELECT per_position_structures_s.nextval FROM sys.dual;
270 BEGIN
271 check_name_unique(X_Rowid => X_Rowid,
272 X_Name => X_Name,
273 X_Business_Group_id => X_Business_Group_id);
274 --
275 check_primary_flag(X_Rowid => X_Rowid,
276 X_Primary_flag => X_Primary_Position_Flag,
277 X_Business_Group_id => X_Business_Group_id);
278 --
279 if (X_Position_Structure_Id is NULL) then
280 OPEN C2;
281 FETCH C2 INTO X_Position_Structure_Id;
282 CLOSE C2;
283 end if;
284 INSERT INTO PER_POSITION_STRUCTURES(
285 position_structure_id,
286 business_group_id,
287 name,
288 comments,
289 primary_position_flag,
290 attribute_category,
291 attribute1,
292 attribute2,
293 attribute3,
294 attribute4,
295 attribute5,
296 attribute6,
297 attribute7,
298 attribute8,
299 attribute9,
300 attribute10,
301 attribute11,
302 attribute12,
303 attribute13,
304 attribute14,
305 attribute15,
306 attribute16,
307 attribute17,
308 attribute18,
309 attribute19,
310 attribute20
311 ) VALUES (
312 X_Position_Structure_Id,
313 X_Business_Group_Id,
314 X_Name,
315 X_Comments,
316 X_Primary_Position_Flag,
317 X_Attribute_Category,
318 X_Attribute1,
319 X_Attribute2,
320 X_Attribute3,
321 X_Attribute4,
322 X_Attribute5,
323 X_Attribute6,
324 X_Attribute7,
325 X_Attribute8,
326 X_Attribute9,
327 X_Attribute10,
328 X_Attribute11,
329 X_Attribute12,
330 X_Attribute13,
331 X_Attribute14,
332 X_Attribute15,
333 X_Attribute16,
334 X_Attribute17,
335 X_Attribute18,
336 X_Attribute19,
337 X_Attribute20
338 );
339 OPEN C;
340 FETCH C INTO X_Rowid;
341 if (C%NOTFOUND) then
342 CLOSE C;
343 RAISE NO_DATA_FOUND;
344 end if;
345 CLOSE C;
346 END Insert_Row;
347
348 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
349 X_Position_Structure_Id NUMBER,
350 X_Business_Group_Id NUMBER,
351 X_Name VARCHAR2,
352 X_Comments VARCHAR2 ,
353 X_Primary_Position_Flag VARCHAR2 ,
354 X_Attribute_Category VARCHAR2 ,
355 X_Attribute1 VARCHAR2 ,
356 X_Attribute2 VARCHAR2 ,
357 X_Attribute3 VARCHAR2 ,
358 X_Attribute4 VARCHAR2 ,
359 X_Attribute5 VARCHAR2 ,
360 X_Attribute6 VARCHAR2 ,
361 X_Attribute7 VARCHAR2 ,
362 X_Attribute8 VARCHAR2 ,
363 X_Attribute9 VARCHAR2 ,
364 X_Attribute10 VARCHAR2 ,
365 X_Attribute11 VARCHAR2 ,
366 X_Attribute12 VARCHAR2 ,
367 X_Attribute13 VARCHAR2 ,
368 X_Attribute14 VARCHAR2 ,
369 X_Attribute15 VARCHAR2 ,
370 X_Attribute16 VARCHAR2 ,
371 X_Attribute17 VARCHAR2 ,
372 X_Attribute18 VARCHAR2 ,
373 X_Attribute19 VARCHAR2 ,
374 X_Attribute20 VARCHAR2
375 ) IS
376 CURSOR C IS
377 SELECT *
378 FROM PER_POSITION_STRUCTURES
379 WHERE rowid = X_Rowid
380 FOR UPDATE of Position_Structure_Id NOWAIT;
381 Recinfo C%ROWTYPE;
382 BEGIN
383 OPEN C;
384 FETCH C INTO Recinfo;
385 if (C%NOTFOUND) then
386 CLOSE C;
387 RAISE NO_DATA_FOUND;
388 end if;
389 CLOSE C;
390 if (
391 ( (Recinfo.position_structure_id = X_Position_Structure_Id)
392 OR ( (Recinfo.position_structure_id IS NULL)
393 AND (X_Position_Structure_Id IS NULL)))
394 AND ( (Recinfo.business_group_id = X_Business_Group_Id)
395 OR ( (Recinfo.business_group_id IS NULL)
396 AND (X_Business_Group_Id IS NULL)))
397 AND ( (Recinfo.name = X_Name)
398 OR ( (Recinfo.name IS NULL)
399 AND (X_Name IS NULL)))
400 AND ( (Recinfo.comments = X_Comments)
401 OR ( (Recinfo.comments IS NULL)
402 AND (X_Comments IS NULL)))
403 AND ( (Recinfo.primary_position_flag = X_Primary_Position_Flag)
404 OR ( (Recinfo.primary_position_flag IS NULL)
405 AND (X_Primary_Position_Flag IS NULL)))
406 AND ( (Recinfo.attribute_category = X_Attribute_Category)
407 OR ( (Recinfo.attribute_category IS NULL)
408 AND (X_Attribute_Category IS NULL)))
409 AND ( (Recinfo.attribute1 = X_Attribute1)
410 OR ( (Recinfo.attribute1 IS NULL)
411 AND (X_Attribute1 IS NULL)))
412 AND ( (Recinfo.attribute2 = X_Attribute2)
413 OR ( (Recinfo.attribute2 IS NULL)
414 AND (X_Attribute2 IS NULL)))
415 AND ( (Recinfo.attribute3 = X_Attribute3)
416 OR ( (Recinfo.attribute3 IS NULL)
417 AND (X_Attribute3 IS NULL)))
418 AND ( (Recinfo.attribute4 = X_Attribute4)
419 OR ( (Recinfo.attribute4 IS NULL)
420 AND (X_Attribute4 IS NULL)))
421 AND ( (Recinfo.attribute5 = X_Attribute5)
422 OR ( (Recinfo.attribute5 IS NULL)
423 AND (X_Attribute5 IS NULL)))
424 AND ( (Recinfo.attribute6 = X_Attribute6)
425 OR ( (Recinfo.attribute6 IS NULL)
426 AND (X_Attribute6 IS NULL)))
427 AND ( (Recinfo.attribute7 = X_Attribute7)
428 OR ( (Recinfo.attribute7 IS NULL)
429 AND (X_Attribute7 IS NULL)))
430 AND ( (Recinfo.attribute8 = X_Attribute8)
431 OR ( (Recinfo.attribute8 IS NULL)
432 AND (X_Attribute8 IS NULL)))
433 AND ( (Recinfo.attribute9 = X_Attribute9)
434 OR ( (Recinfo.attribute9 IS NULL)
435 AND (X_Attribute9 IS NULL)))
436 AND ( (Recinfo.attribute10 = X_Attribute10)
437 OR ( (Recinfo.attribute10 IS NULL)
438 AND (X_Attribute10 IS NULL)))
439 AND ( (Recinfo.attribute11 = X_Attribute11)
440 OR ( (Recinfo.attribute11 IS NULL)
441 AND (X_Attribute11 IS NULL)))
442 AND ( (Recinfo.attribute12 = X_Attribute12)
443 OR ( (Recinfo.attribute12 IS NULL)
444 AND (X_Attribute12 IS NULL)))
445 AND ( (Recinfo.attribute13 = X_Attribute13)
446 OR ( (Recinfo.attribute13 IS NULL)
447 AND (X_Attribute13 IS NULL)))
448 AND ( (Recinfo.attribute14 = X_Attribute14)
449 OR ( (Recinfo.attribute14 IS NULL)
450 AND (X_Attribute14 IS NULL)))
451 AND ( (Recinfo.attribute15 = X_Attribute15)
452 OR ( (Recinfo.attribute15 IS NULL)
453 AND (X_Attribute15 IS NULL)))
454 AND ( (Recinfo.attribute16 = X_Attribute16)
455 OR ( (Recinfo.attribute16 IS NULL)
456 AND (X_Attribute16 IS NULL)))
457 AND ( (Recinfo.attribute17 = X_Attribute17)
458 OR ( (Recinfo.attribute17 IS NULL)
459 AND (X_Attribute17 IS NULL)))
460 AND ( (Recinfo.attribute18 = X_Attribute18)
461 OR ( (Recinfo.attribute18 IS NULL)
462 AND (X_Attribute18 IS NULL)))
463 AND ( (Recinfo.attribute19 = X_Attribute19)
464 OR ( (Recinfo.attribute19 IS NULL)
465 AND (X_Attribute19 IS NULL)))
466 AND ( (Recinfo.attribute20 = X_Attribute20)
467 OR ( (Recinfo.attribute20 IS NULL)
468 AND (X_Attribute20 IS NULL)))
469 ) then
470 return;
471 else
472 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
473 APP_EXCEPTION.RAISE_EXCEPTION;
474 end if;
475 END Lock_Row;
476
477 PROCEDURE Update_Row(X_Rowid VARCHAR2,
478 X_Position_Structure_Id NUMBER,
479 X_Business_Group_Id NUMBER,
480 X_Name VARCHAR2,
481 X_Comments VARCHAR2 ,
482 X_Primary_Position_Flag VARCHAR2 ,
483 X_Attribute_Category VARCHAR2 ,
484 X_Attribute1 VARCHAR2 ,
485 X_Attribute2 VARCHAR2 ,
486 X_Attribute3 VARCHAR2 ,
487 X_Attribute4 VARCHAR2 ,
488 X_Attribute5 VARCHAR2 ,
489 X_Attribute6 VARCHAR2 ,
490 X_Attribute7 VARCHAR2 ,
491 X_Attribute8 VARCHAR2 ,
492 X_Attribute9 VARCHAR2 ,
493 X_Attribute10 VARCHAR2 ,
494 X_Attribute11 VARCHAR2 ,
495 X_Attribute12 VARCHAR2 ,
496 X_Attribute13 VARCHAR2 ,
497 X_Attribute14 VARCHAR2 ,
498 X_Attribute15 VARCHAR2 ,
499 X_Attribute16 VARCHAR2 ,
500 X_Attribute17 VARCHAR2 ,
501 X_Attribute18 VARCHAR2 ,
502 X_Attribute19 VARCHAR2 ,
503 X_Attribute20 VARCHAR2
504 ) IS
505 BEGIN
506 check_name_unique(X_Rowid => X_Rowid,
507 X_Name => X_Name,
508 X_Business_Group_id => X_Business_Group_id);
509 --
510 check_primary_flag(X_Rowid => X_Rowid,
511 X_Primary_Flag => X_Primary_Position_Flag,
512 X_Business_Group_id => X_Business_Group_id);
513 --
514 UPDATE PER_POSITION_STRUCTURES
515 SET
516 position_structure_id = X_Position_Structure_Id,
517 business_group_id = X_Business_Group_Id,
518 name = X_Name,
519 comments = X_Comments,
520 primary_position_flag = X_Primary_Position_Flag,
521 attribute_category = X_Attribute_Category,
522 attribute1 = X_Attribute1,
523 attribute2 = X_Attribute2,
524 attribute3 = X_Attribute3,
525 attribute4 = X_Attribute4,
526 attribute5 = X_Attribute5,
527 attribute6 = X_Attribute6,
528 attribute7 = X_Attribute7,
529 attribute8 = X_Attribute8,
530 attribute9 = X_Attribute9,
531 attribute10 = X_Attribute10,
532 attribute11 = X_Attribute11,
533 attribute12 = X_Attribute12,
534 attribute13 = X_Attribute13,
535 attribute14 = X_Attribute14,
536 attribute15 = X_Attribute15,
537 attribute16 = X_Attribute16,
538 attribute17 = X_Attribute17,
539 attribute18 = X_Attribute18,
540 attribute19 = X_Attribute19,
541 attribute20 = X_Attribute20
542 WHERE rowid = X_rowid;
543 if (SQL%NOTFOUND) then
544 RAISE NO_DATA_FOUND;
545 end if;
546 END Update_Row;
547
548 PROCEDURE Delete_Row(X_Rowid VARCHAR2,
549 X_Position_Structure_Id NUMBER,
550 X_Business_Group_Id NUMBER,
551 X_Po_Installed VARCHAR2,
552 X_Hr_Installed VARCHAR2) IS
553 BEGIN
554 --
555 pre_delete_checks(X_Position_Structure_Id => X_Position_Structure_Id,
556 X_Business_Group_Id => X_Business_Group_Id,
557 X_Po_Installed => X_Po_Installed,
558 X_Hr_Installed => X_Hr_Installed);
559 --
560 DELETE FROM PER_POSITION_STRUCTURES
561 WHERE rowid = X_Rowid;
562 if (SQL%NOTFOUND) then
563 RAISE NO_DATA_FOUND;
564 end if;
565 END Delete_Row;
566
567 function postform(p_business_group_id NUMBER) return boolean is
568 --
569 -- local variables
570 --
571 l_dummy VARCHAR2(1);
572 begin
573 select null
574 into l_dummy
575 from sys.dual
576 where exists( select 1
577 from per_security_profiles sp
578 where sp.position_id is not null
579 and sp.business_group_id + 0 = p_business_group_id
580 and exists (select null
581 from per_pos_structure_versions psv
582 where psv.business_group_id = p_business_group_id
583 and not exists
584 ( select null
585 from per_pos_Structure_elements pse
586 where pse.pos_structure_version_id =
587 psv.pos_Structure_version_id
588 and ( sp.position_id =
589 pse.subordinate_position_id)
590 or (sp.position_id =
591 pse.parent_position_id)
592 )
593 )
594 );
595 --
596 return TRUE;
597 --
598 exception
599 when no_data_found then
600 return false;
601 end postform;
602
603 END PER_POSITION_STRUCTURES_PKG;