1 PACKAGE BODY IGS_AS_VAL_GS AS
2 /* $Header: IGSAS23B.pls 115.4 2002/11/28 22:45:11 nsidana ship $ */
3 -------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 --smadathi 27-AUG-2001 Bug No. 1956374 .The function genp_val_strt_end_dt removed
7 -------------------------------------------------------------------------------------------
8 --
9 -- Validate for one open version of grading schema
10 FUNCTION assp_val_gs_one_open(
11 p_grading_schema_cd IN IGS_AS_GRD_SCHEMA.grading_schema_cd%TYPE ,
12 p_version_number IN IGS_AS_GRD_SCHEMA.version_number%TYPE ,
13 p_message_name OUT NOCOPY varchar2 )
14 RETURN BOOLEAN IS
15 gv_other_detail VARCHAR2(255);
16 BEGIN
17 DECLARE
18 CURSOR c_gs_count(
19 cp_grading_schema_cd IGS_AS_GRD_SCHEMA.grading_schema_cd%TYPE,
20 cp_version_number IGS_AS_GRD_SCHEMA.version_number%TYPE) IS
21 SELECT COUNT(*)
22 FROM IGS_AS_GRD_SCHEMA
23 WHERE grading_schema_cd = cp_grading_schema_cd AND
24 version_number <> cp_version_number AND
25 end_dt IS NULL;
26 v_gs_count NUMBER;
27 BEGIN
28 -- Validate for one open version of a grading schema.
29 -- Ensure that only one version of a grading schema is open, otherwise
30 -- generate an error message.
31 p_message_name := null;
32 v_gs_count := 0;
33 OPEN c_gs_count(
34 p_grading_schema_cd,
35 p_version_number);
36 FETCH c_gs_count INTO v_gs_count;
37 IF c_gs_count%NOTFOUND THEN
38 CLOSE c_gs_count;
39 RETURN TRUE;
40 END IF;
41 CLOSE c_gs_count;
42 IF v_gs_count > 0 THEN
43 p_message_name := 'IGS_AS_MULTIPLE_VER_GRDSCHEMA';
44 RETURN FALSE;
45 END IF;
46 -- Successful completion
47 RETURN TRUE;
48 END;
49 EXCEPTION
50 WHEN OTHERS THEN
51 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
52 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_VAL_GS.assp_val_gs_one_open');
53 IGS_GE_MSG_STACK.ADD;
54 App_Exception.Raise_Exception;
55 END assp_val_gs_one_open;
56 --
57 -- Validate for overlapping dates for grading schemas
58 FUNCTION assp_val_gs_ovrlp(
59 p_grading_schema_cd IN IGS_AS_GRD_SCHEMA.grading_schema_cd%TYPE ,
60 p_version_number IN IGS_AS_GRD_SCHEMA.version_number%TYPE ,
61 p_start_dt IN IGS_AS_GRD_SCHEMA.start_dt%TYPE ,
62 p_end_dt IN IGS_AS_GRD_SCHEMA.end_dt%TYPE ,
63 p_message_name OUT NOCOPY varchar2 )
64 RETURN BOOLEAN IS
65 gv_other_detail VARCHAR2(255);
66 BEGIN
67 DECLARE
68 CURSOR c_gs IS
69 SELECT start_dt,
70 NVL(end_dt, IGS_GE_DATE.IGSDATE('9999/01/01'))
71 FROM IGS_AS_GRD_SCHEMA
72 WHERE grading_schema_cd = p_grading_schema_cd AND
73 version_number <> p_version_number;
74 v_gs_rec c_gs%ROWTYPE;
75 v_error_flag BOOLEAN;
76 v_start_dt IGS_AS_GRD_SCHEMA.start_dt%TYPE;
77 v_end_dt IGS_AS_GRD_SCHEMA.end_dt%TYPE;
78 v_p_end_dt IGS_AS_GRD_SCHEMA.end_dt%TYPE;
79 BEGIN
80 p_message_name := null;
81 -- set p_end_dt to a high date if null
82 v_p_end_dt := NVL(p_end_dt, IGS_GE_DATE.IGSDATE('9999/01/01'));
83 OPEN c_gs;
84 -- Validation will fail if any of the following are true
85 LOOP
86 EXIT WHEN (c_gs%NOTFOUND);
87 FETCH c_gs INTO v_start_dt,
88 v_end_dt;
89 -- (a) The current start date is between an existing date range.
90 IF (p_start_dt >= v_start_dt AND
91 p_start_dt <= v_end_dt) THEN
92 CLOSE c_gs;
93 p_message_name := 'IGS_AS_DO_STDT_EXIST_DT_RANGE';
94 RETURN FALSE;
95 END IF;
96 -- (b) The current end date is between an existing date range.
97 IF (v_p_end_dt >= v_start_dt AND
98 v_p_end_dt <= v_end_dt) THEN
99 CLOSE c_gs;
100 p_message_name := 'IGS_AS_DO_ENDT_EXIST_DT_RANGE';
101 RETURN FALSE;
102 END IF;
103 -- (c) The current dates overlap an entire existing date range.
104 IF (p_start_dt <= v_start_dt AND
105 v_p_end_dt >= v_end_dt) THEN
106 CLOSE c_gs;
107 p_message_name := 'IGS_AS_DO_DATE_OVERLAP_DTRANG';
108 Return FALSE;
109 END IF;
110 END LOOP;
111 CLOSE c_gs;
112 RETURN TRUE;
113 END;
114 EXCEPTION
115 WHEN OTHERS THEN
116 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
117 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_VAL_GS.assp_val_gs_ovrlp');
118 IGS_GE_MSG_STACK.ADD;
119 App_Exception.Raise_Exception;
120 END assp_val_gs_ovrlp;
121 --
122
123 END IGS_AS_VAL_GS;
[Home] [Help]