1 PACKAGE BODY IGS_RE_VAL_DMS AS
2 /* $Header: IGSRE08B.pls 115.3 2002/11/29 03:28:28 nsidana ship $ */
3 --
4 -- To validate IGS_RE_DFLT_MS_SET uniqueness
5 FUNCTION RESP_VAL_DMS_UNIQ(
6 p_course_cd IN VARCHAR2 ,
7 p_version_number IN NUMBER ,
8 p_attendance_type IN VARCHAR2 ,
9 p_message_name OUT NOCOPY VARCHAR2 )
10 RETURN BOOLEAN AS
11 BEGIN -- resp_val_dms_uniq
12 -- Validate that there are not ?logically? duplicate default IGS_PR_MILESTONE
13 -- records within a IGS_PS_COURSE version/attendance type combination.
14 -- ie. same milestone_type and offset_days.
15 -- This check is designed to be placed in a post-forms-commit section
16 -- of a form or an after statement of a database trigger.
17 DECLARE
18 v_return_false BOOLEAN;
19 CURSOR c_dms IS
20 SELECT dms.course_cd,
21 dms.version_number,
22 dms.attendance_type,
23 dms.milestone_type,
24 dms.offset_days,
25 count('x') duplicate_count
26 FROM IGS_RE_DFLT_MS_SET dms
27 WHERE course_cd = p_course_cd AND
28 dms.version_number = p_version_number AND
29 dms.attendance_type = p_attendance_type
30 GROUP BY
31 dms.course_cd,
32 dms.version_number,
33 dms.attendance_type,
34 dms.milestone_type,
35 dms.offset_days;
36 BEGIN
37 -- Set the defaults
38 p_message_name := null;
39 v_return_false := FALSE;
40 FOR v_dms_rec IN c_dms LOOP
41 IF v_dms_rec.duplicate_count > 1 THEN
42 p_message_name := 'IGS_RE_2_MILSTON_HAV_SAMEOFFS';
43 v_return_false := TRUE;
44 EXIT;
45 END IF;
46 END LOOP;
47 IF v_return_false THEN
48 RETURN FALSE;
49 END IF;
50 RETURN TRUE;
51 EXCEPTION
52 WHEN OTHERS THEN
53 IF c_dms%ISOPEN THEN
54 CLOSE c_dms;
55 END IF;
56 RAISE;
57 END;
58 EXCEPTION
59 WHEN OTHERS THEN
60 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
61 IGS_GE_MSG_STACK.ADD;
62 App_Exception.Raise_Exception;
63 END resp_val_dms_uniq;
64 END IGS_RE_VAL_DMS;
[Home] [Help]