1 PACKAGE BODY IGS_RE_VAL_SCH AS
2 /* $Header: IGSRE12B.pls 115.4 2002/11/29 03:29:30 nsidana ship $ */
3 -------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 --smadathi 28-AUG-2001 Bug No. 1956374 .The function genp_val_strt_end_dt removed
7 --smadathi 25-AUG-2001 Bug No. 1956374 .The function GENP_VAL_SDTT_SESS removed
8 -------------------------------------------------------------------------------------------
9
10 -- To validate scholarship_type closed indicator
11 FUNCTION RESP_VAL_SCHT_CLOSED(
12 p_scholarship_type IN VARCHAR2 ,
13 p_message_name OUT NOCOPY VARCHAR2 )
14 RETURN BOOLEAN AS
15 BEGIN -- resp_val_scht_closed
16 -- Validate the IGS_RE_SCHOLARSHIP type closed indicator
17 DECLARE
18 v_scht_found VARCHAR2(1);
19 CURSOR c_scht IS
20 SELECT 'x'
21 FROM IGS_RE_SCHL_TYPE
22 WHERE scholarship_type = p_scholarship_type AND
23 closed_ind = 'Y';
24 BEGIN
25 -- initialse the message_nameber
26 p_message_name := NULL;
27 OPEN c_scht;
28 FETCH c_scht INTO v_scht_found;
29 IF (c_scht%FOUND) THEN
30 CLOSE c_scht;
31 p_message_name := 'IGS_RE_SCHOLAR_TYPE_CLOSED';
32 RETURN FALSE;
33 END IF;
34 CLOSE c_scht;
35 RETURN TRUE;
36 EXCEPTION
37 WHEN OTHERS THEN
38 IF c_scht%ISOPEN THEN
39 CLOSE c_scht;
40 END IF;
41 RAISE;
42 END;
43 EXCEPTION
44 WHEN OTHERS THEN
45 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
46 IGS_GE_MSG_STACK.ADD;
47 App_Exception.Raise_Exception;
48 END resp_val_scht_closed;
49 --
50 -- To validate IGS_RE_SCHOLARSHIP date overlaps
51 FUNCTION RESP_VAL_SCH_OVRLP(
52 p_person_id IN NUMBER ,
53 p_ca_sequence_number IN NUMBER ,
54 p_scholarship_type IN VARCHAR2 ,
55 p_start_dt IN DATE ,
56 p_end_dt IN DATE ,
57 p_message_name OUT NOCOPY VARCHAR2 )
58 RETURN BOOLEAN AS
59 BEGIN -- resp_val_sch_ovrlp
60 -- Validate that the scolarship record being created or updated
61 -- does not overlap with an existing scolarship record of the
62 -- same scolarship_type.
63 DECLARE
64 v_end_dt IGS_RE_SCHOLARSHIP.end_dt%TYPE;
65 v_high_dt IGS_RE_SCHOLARSHIP.end_dt%TYPE;
66 v_exit_loop BOOLEAN DEFAULT FALSE;
67 CURSOR c_sch IS
68 SELECT sch.start_dt,
69 sch.end_dt
70 FROM IGS_RE_SCHOLARSHIP sch
71 WHERE sch.person_id = p_person_id AND
72 sch.ca_sequence_number = p_ca_sequence_number AND
73 sch.scholarship_type = p_scholarship_type AND
74 sch.start_dt <> p_start_dt;
75 BEGIN
76 p_message_name := NULL;
77 -- set_v_high_dt
78 v_high_dt := IGS_GE_DATE.IGSDATE('9999/01/01');
79 -- set v_end_dt to v_high_dt when p_end_dt is null
80 v_end_dt := NVL(p_end_dt, v_high_dt);
81 FOR v_sch_rec IN c_sch LOOP
82 -- check that the current date is between an existing date range.
83 IF p_start_dt > v_sch_rec.start_dt AND
84 p_start_dt <= NVL(v_sch_rec.end_dt, v_high_dt) THEN
85 v_exit_loop := TRUE;
86 p_message_name := 'IGS_RE_ST_DT_BET_EXIST_DT_RNG';
87 EXIT;
88 END IF;
89 -- check that the current end date is between an existing date range.
90 IF v_end_dt >= v_sch_rec.start_dt AND
91 v_end_dt <= NVL(v_sch_rec.end_dt, v_high_dt) THEN
92 v_exit_loop := TRUE;
93 p_message_name := 'IGS_RE_EN_DT_BET_EXIST_DT_RNG';
94 EXIT;
95 END IF;
96 -- check whether the current dates overlap an entire existing date range.
97 IF p_start_dt < v_sch_rec.start_dt AND
98 v_end_dt >= NVL(v_sch_rec.end_dt, v_high_dt) THEN
99 v_exit_loop := TRUE;
100 p_message_name := 'IGS_RE_DT_OVERLAP_WITH_DT_RNG';
101 EXIT;
102 END IF;
103 END LOOP;
104 IF v_exit_loop THEN
105 RETURN FALSE;
106 END IF;
107 RETURN TRUE;
108 EXCEPTION
109 WHEN OTHERS THEN
110 IF c_sch%ISOPEN THEN
111 CLOSE c_sch;
112 END IF;
113 RAISE;
114 END;
115 EXCEPTION
116 WHEN OTHERS THEN
117 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
118 IGS_GE_MSG_STACK.ADD;
119 App_Exception.Raise_Exception;
120 END resp_val_sch_ovrlp;
121
122 END IGS_RE_VAL_SCH;
[Home] [Help]