1 Package per_asp_bus AUTHID CURRENT_USER as
2 /* $Header: peasprhi.pkh 115.11 2002/12/05 13:03:31 apholt ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- |---------------------------< insert_validate >----------------------------|
6 -- ----------------------------------------------------------------------------
7 -- {Start Of Comments}
8 --
9 -- Description:
10 -- This procedure controls the execution of all insert business rules
11 -- validation.
12 --
13 -- Prerequisites:
14 -- This private procedure is called from ins procedure.
15 --
16 -- In Parameters:
17 -- A Pl/Sql record structre.
18 --
19 -- Post Success:
20 -- Processing continues.
21 --
22 -- Post Failure:
23 -- If a business rules fails the error will not be handled by this procedure
24 -- unless explicity coded.
25 --
26 -- Developer Implementation Notes:
27 -- For insert, your business rules should be executed from this procedure and
28 -- should ideally (unless really necessary) just be straight procedure or
29 -- function calls. Try and avoid using conditional branching logic.
30 --
31 -- Access Status:
32 -- Internal Row Handler Use Only.
33 --
34 -- {End Of Comments}
35 -- ----------------------------------------------------------------------------
36 Procedure insert_validate(p_rec in per_asp_shd.g_rec_type);
37 --
38 -- ----------------------------------------------------------------------------
39 -- |---------------------------< update_validate >----------------------------|
40 -- ----------------------------------------------------------------------------
41 -- {Start Of Comments}
42 --
43 -- Description:
44 -- This procedure controls the execution of all update business rules
45 -- validation.
46 --
47 -- Prerequisites:
48 -- This private procedure is called from upd procedure.
49 --
50 -- In Parameters:
51 -- A Pl/Sql record structre.
52 --
53 -- Post Success:
54 -- Processing continues.
55 --
56 -- Post Failure:
57 -- If a business rules fails the error will not be handled by this procedure
58 -- unless explicity coded.
59 --
60 -- Developer Implementation Notes:
61 -- For update, your business rules should be executed from this procedure and
62 -- should ideally (unless really necessary) just be straight procedure or
63 -- function calls. Try and avoid using conditional branching logic.
64 --
65 -- Access Status:
66 -- Internal Row Handler Use Only.
67 --
68 -- {End Of Comments}
69 -- ----------------------------------------------------------------------------
70 Procedure update_validate(p_rec in per_asp_shd.g_rec_type);
71 --
72 -- ----------------------------------------------------------------------------
73 -- |-< chk_assignment_dates >-------------------------------------------------|
74 -- ----------------------------------------------------------------------------
75 -- {Start Of Comments}
76 --
77 -- Description:
78 -- This procedure performs basic checks on the assignment dates to ensure
79 -- that they conform with the business rules.
80 -- At the moment the only business rule enforced in this procedure is that
81 -- the end date must be >= the start date and that the start date is not
82 -- null.
83 --
84 -- Prerequisites:
85 -- None.
86 --
87 -- In Parameters:
88 -- p_user_id
89 -- p_responsibility_id
90 -- p_application_id
91 -- p_security_group_id
92 -- p_start_date
93 -- p_end_date
94 --
95 -- Post Success:
96 -- Processing continues.
97 --
98 -- Post Failure:
99 -- An exception is raised.
100 --
101 -- Developer Implementation Notes:
102 -- None.
103 --
104 -- Access Status:
105 -- Internal Row Handler Use Only.
106 --
107 -- {End Of Comments}
108 -- ----------------------------------------------------------------------------
109 --
110 PROCEDURE chk_assignment_dates
111 (p_user_id
112 IN per_sec_profile_assignments.user_id%TYPE
113 ,p_responsibility_id
114 IN per_sec_profile_assignments.responsibility_id%TYPE
115 ,p_application_id
116 IN per_sec_profile_assignments.responsibility_application_id%TYPE
117 ,p_security_group_id
118 IN per_sec_profile_assignments.security_group_id%TYPE
119 ,p_start_date
120 IN per_sec_profile_assignments.start_date%TYPE
121 ,p_end_date
122 IN per_sec_profile_assignments.end_date%TYPE
123 );
124 --
125 -- ----------------------------------------------------------------------------
126 -- |-< chk_invalid_dates >----------------------------------------------------|
127 -- ----------------------------------------------------------------------------
128 -- {Start Of Comments}
129 --
130 -- Description:
131 -- This procedure is used to enforce the business rule that the start/end
132 -- dates of new/updated records cannot overlap both the start and the end
133 -- dates of existing records.
134 --
135 -- Prerequisites:
136 -- None.
137 --
138 -- In Parameters:
139 -- p_sec_profile_assignment_id
140 -- p_user_id
141 -- p_responsibility_id
142 -- p_application_id
143 -- p_security_group_id
144 -- p_security_profile_id
145 -- p_start_date
146 -- p_end_date
147 --
148 -- Post Success:
149 -- Processing continues.
150 --
151 -- Post Failure:
152 -- An exception is raised.
153 --
154 -- Developer Implementation Notes:
155 -- None.
156 --
157 -- Access Status:
158 -- Internal Row Handler Use Only.
159 --
160 -- {End Of Comments}
161 -- ----------------------------------------------------------------------------
162 --
163 PROCEDURE chk_invalid_dates
164 (p_sec_profile_assignment_id
165 IN per_sec_profile_assignments.sec_profile_assignment_id%TYPE
166 DEFAULT NULL
167 ,p_user_id
168 IN per_sec_profile_assignments.user_id%TYPE
169 ,p_responsibility_id
170 IN per_sec_profile_assignments.responsibility_id%TYPE
171 ,p_application_id
172 IN per_sec_profile_assignments.responsibility_application_id%TYPE
173 ,p_security_group_id
174 IN per_sec_profile_assignments.security_group_id%TYPE
175 ,p_business_group_id
176 IN per_sec_profile_assignments.business_group_id%TYPE
177 ,p_security_profile_id
178 IN per_sec_profile_assignments.security_profile_id%TYPE
179 ,p_start_date
180 IN per_sec_profile_assignments.start_date%TYPE
181 ,p_end_date
182 IN per_sec_profile_assignments.end_date%TYPE
183 );
184 --
185 -- ----------------------------------------------------------------------------
186 -- |-< chk_duplicate_assignments >--------------------------------------------|
187 -- ----------------------------------------------------------------------------
188 -- {Start Of Comments}
189 --
190 -- Description:
191 -- This procedure is used to enforce the business rule that there must not
192 -- be assignments for the same U/R/A/SG but with a different SP.
193 --
194 -- Prerequisites:
195 -- None.
196 --
197 -- In Parameters:
198 -- p_user_id
199 -- p_responsibility_id
200 -- p_application_id
201 -- p_security_group_id
202 --
203 -- Post Success:
204 -- Processing continues.
205 --
206 -- Post Failure:
207 -- An exception is raised.
208 --
209 -- Developer Implementation Notes:
210 -- None.
211 --
212 -- Access Status:
213 -- Internal Row Handler Use Only.
214 --
215 -- {End Of Comments}
216 -- ----------------------------------------------------------------------------
217 --
218 PROCEDURE chk_duplicate_assignments
219 (p_user_id
220 IN per_sec_profile_assignments.user_id%TYPE
221 ,p_responsibility_id
222 IN per_sec_profile_assignments.responsibility_id%TYPE
223 ,p_application_id
224 IN per_sec_profile_assignments.responsibility_application_id%TYPE
225 ,p_security_group_id
226 IN per_sec_profile_assignments.security_group_id%TYPE
227 ,p_business_group_id
228 IN per_sec_profile_assignments.business_group_id%TYPE
229 ,p_security_profile_id
230 IN per_sec_profile_assignments.security_profile_id%TYPE
231 ,p_start_date
232 IN per_sec_profile_assignments.start_date%TYPE
233 ,p_end_date
234 IN per_sec_profile_assignments.end_date%TYPE
235 );
236 --
237 -- ----------------------------------------------------------------------------
238 -- |-< chk_overlapping_dates >------------------------------------------------|
239 -- ----------------------------------------------------------------------------
240 -- {Start Of Comments}
241 --
242 -- Description:
243 -- This procedure is used to enforce the business rule that dates of
244 -- records cannot overlap.
245 --
246 -- Prerequisites:
247 -- None.
248 --
249 -- In Parameters:
250 -- p_sec_profile_assignment_id
251 -- p_user_id
252 -- p_responsibility_id
253 -- p_application_id
254 -- p_security_group_id
255 -- p_security_profile_id
256 -- p_start_date
257 -- p_end_date
258 --
259 -- Post Success:
260 -- Processing continues.
261 --
262 -- Post Failure:
263 -- An exception is raised.
264 --
265 -- Developer Implementation Notes:
266 -- None.
267 --
268 -- Access Status:
269 -- Internal Row Handler Use Only.
270 --
271 -- {End Of Comments}
272 -- ----------------------------------------------------------------------------
273 --
274 PROCEDURE chk_overlapping_dates
275 (p_sec_profile_assignment_id
276 IN per_sec_profile_assignments.sec_profile_assignment_id%TYPE
277 DEFAULT NULL
278 ,p_user_id
279 IN per_sec_profile_assignments.user_id%TYPE
280 ,p_responsibility_id
281 IN per_sec_profile_assignments.responsibility_id%TYPE
282 ,p_application_id
283 IN per_sec_profile_assignments.responsibility_application_id%TYPE
284 ,p_security_group_id
285 IN per_sec_profile_assignments.security_group_id%TYPE
286 ,p_business_group_id
287 IN per_sec_profile_assignments.business_group_id%TYPE
288 ,p_security_profile_id
289 IN per_sec_profile_assignments.security_profile_id%TYPE
290 ,p_start_date
291 IN per_sec_profile_assignments.start_date%TYPE
292 ,p_end_date
293 IN per_sec_profile_assignments.end_date%TYPE
294 );
295 --
296 -- ----------------------------------------------------------------------------
297 -- |-< chk_overlapping_dates >------------------------------------------------|
298 -- ----------------------------------------------------------------------------
299 -- {Start Of Comments}
300 --
301 -- Description:
302 -- This procedure is used to enforce the business rule that dates of
303 -- records cannot overlap.
304 --
305 -- Prerequisites:
306 -- None.
307 --
308 -- In Parameters:
309 -- p_sec_profile_assignment_id
310 -- p_user_id
311 -- p_responsibility_id
312 -- p_application_id
313 -- p_security_group_id
314 -- p_security_profile_id
315 -- p_start_date
316 -- p_end_date
317 -- p_clashing_id
318 -- p_clashing_ovn
319 -- p_clashing_start_date
320 -- p_clashing_end_date
321 --
322 -- Post Success:
323 -- Processing continues.
324 --
325 -- Post Failure:
326 -- The id of the record which overlaps is returned.
327 --
328 -- Developer Implementation Notes:
329 -- None.
330 --
331 -- Access Status:
332 -- Internal Row Handler Use Only.
333 --
334 -- {End Of Comments}
335 -- ----------------------------------------------------------------------------
336 --
337 PROCEDURE chk_overlapping_dates
338 (p_sec_profile_assignment_id
339 IN per_sec_profile_assignments.sec_profile_assignment_id%TYPE
340 DEFAULT NULL
341 ,p_user_id
342 IN per_sec_profile_assignments.user_id%TYPE
343 ,p_responsibility_id
344 IN per_sec_profile_assignments.responsibility_id%TYPE
345 ,p_application_id
346 IN per_sec_profile_assignments.responsibility_application_id%TYPE
347 ,p_security_group_id
348 IN per_sec_profile_assignments.security_group_id%TYPE
349 ,p_business_group_id
350 IN per_sec_profile_assignments.business_group_id%TYPE
351 ,p_security_profile_id
352 IN per_sec_profile_assignments.security_profile_id%TYPE
353 ,p_start_date
354 IN per_sec_profile_assignments.start_date%TYPE
355 ,p_end_date
356 IN per_sec_profile_assignments.end_date%TYPE
357 ,p_clashing_id
358 OUT NOCOPY per_sec_profile_assignments.sec_profile_assignment_id%TYPE
359 ,p_clashing_ovn
360 OUT NOCOPY per_sec_profile_assignments.object_version_number%TYPE
361 ,p_clashing_start_date
362 OUT NOCOPY per_sec_profile_assignments.start_date%TYPE
363 ,p_clashing_end_date
364 OUT NOCOPY per_sec_profile_assignments.end_date%TYPE
365 );
366 --
367 -- ----------------------------------------------------------------------------
368 -- |-< chk_assignment_exists >------------------------------------------------|
369 -- ----------------------------------------------------------------------------
370 -- {Start Of Comments}
371 --
372 -- Description:
373 -- This procedure is a wrapper on the function of the same name, which
374 -- will raise an exception if the business rule check fails. The
375 -- intention is that this procedure will be used from within the api
376 -- whereas the function is to be used in the form enabled interaction
377 -- with the user to allowing a prompt to ask if they want to modify their
378 -- record.
379 --
380 -- Prerequisites:
381 -- None
382 --
383 -- In Parameters:
384 -- p_user_id
385 -- p_responsibility_id
386 -- p_application_id
387 -- p_security_group_id
388 --
389 -- Post Success:
390 -- Processing continues.
391 --
392 -- Post Failure:
393 -- An exception is raised.
394 --
395 -- Developer Implementation Notes:
396 -- None.
397 --
398 -- Access Status:
399 -- Internal Row Handler Use Only.
400 --
401 -- {End Of Comments}
402 -- ----------------------------------------------------------------------------
403 --
404 PROCEDURE chk_assignment_exists
405 (p_user_id
406 IN per_sec_profile_assignments.user_id%TYPE
407 ,p_responsibility_id
408 IN per_sec_profile_assignments.responsibility_id%TYPE
409 ,p_application_id
410 IN per_sec_profile_assignments.responsibility_application_id%TYPE
411 ,p_security_group_id
412 IN per_sec_profile_assignments.security_group_id%TYPE
413 );
414 --
415 -- ----------------------------------------------------------------------------
416 -- |-< chk_assignment_exists >-------------------------------------------------|
417 -- ----------------------------------------------------------------------------
418 -- {Start Of Comments}
419 --
420 -- Description:
421 -- This function will return TRUE is the specified assignment exists, or
422 -- FALSE otherwise.
423 --
424 -- Prerequisites:
425 -- None.
426 --
427 -- In Parameters:
428 -- p_user_id
429 -- p_responsibility_id
430 -- p_application_id
431 -- p_security_group_id
432 --
433 -- Post Success:
434 -- FALSE is returned from the function.
435 --
436 -- Post Failure:
437 -- TRUE is returned from the function.
438 --
439 -- Developer Implementation Notes:
440 -- None.
441 --
442 -- Access Status:
443 -- Internal Row Handler Use Only.
444 --
445 -- {End Of Comments}
446 -- ----------------------------------------------------------------------------
447 --
448 FUNCTION chk_assignment_exists
449 (p_user_id
450 IN per_sec_profile_assignments.user_id%TYPE
451 ,p_responsibility_id
452 IN per_sec_profile_assignments.responsibility_id%TYPE
453 ,p_application_id
454 IN per_sec_profile_assignments.responsibility_application_id%TYPE
455 ,p_security_group_id
456 IN per_sec_profile_assignments.security_group_id%TYPE
457 ) RETURN BOOLEAN;
458 --
459 -- ----------------------------------------------------------------------------
460 -- |-< get_security_group_id >------------------------------------------------|
461 -- ----------------------------------------------------------------------------
462 -- {Start Of Comments}
463 --
464 -- Description:
465 -- This function can be used to retrieve the security_group_id for a given
466 -- business group name.
467 --
468 -- Prerequisites:
469 --
470 -- In Parameters:
471 -- p_business_group_name - the business group name
472 --
473 -- Post Success:
474 -- The security group id for the business group is returned.
475 --
476 -- Post Failure:
477 -- An exception is raised.
478 --
479 -- Developer Implementation Notes:
480 -- None.
481 --
482 -- Access Status:
483 -- Internal Row Handler Use Only.
484 --
485 --
486 -- {End Of Comments}
487 -- ----------------------------------------------------------------------------
488 --
489 FUNCTION get_security_group_id
490 (p_business_group_id IN NUMBER
491 ) RETURN NUMBER;
492 --
493 -- ----------------------------------------------------------------------------
494 -- |-< Synchronize_Assignment_Dates >-----------------------------------------|
495 -- ----------------------------------------------------------------------------
496 -- {Start Of Comments}
497 --
498 -- Description:
499 -- This procedure is used to ensure that the dates in FND_USER_RESP_GROUPS
500 -- table are synchronized with the dates in the PER_SEC_PROFILE_ASSIGNMENTS
501 -- table. The basic rule is that the start date in F_U_R_G is set to the
502 -- minimum start date in P_S_P_A, and the end date in F_U_R_G is set to the
506 -- Prerequisites:
503 -- maximum end date in P_S_P_A (or the end of time if a null entry for the
504 -- end date exists).
505 --
507 -- None.
508 --
509 -- In Parameters:
510 -- p_user_id
511 -- p_responsibility_id
512 -- p_application_id
513 -- p_security_group_id
514 --
515 -- Post Success:
516 -- The dates in FND_USER_RESP_GROUPS are synchronized and processing
517 -- continues.
518 --
519 -- Post Failure:
520 -- An exception is raised.
521 --
522 -- Developer Implementation Notes:
523 -- None.
524 --
525 -- Access Status:
526 -- Internal Row Handler Use Only.
527 --
528 -- {End Of Comments}
529 -- ----------------------------------------------------------------------------
530 --
531 PROCEDURE Synchronize_Assignment_Dates
532 (p_user_id
533 IN per_sec_profile_assignments.user_id%TYPE
534 ,p_responsibility_id
535 IN per_sec_profile_assignments.responsibility_id%TYPE
536 ,p_application_id
537 IN per_sec_profile_assignments.responsibility_application_id%TYPE
538 ,p_security_group_id
539 IN per_sec_profile_assignments.security_group_id%TYPE
540 ,p_business_group_id
541 IN per_sec_profile_assignments.business_group_id%TYPE
542 );
543 --
544 end per_asp_bus;
[Home] [Help]