1 PACKAGE BODY IGS_PR_VAL_SPRGC AS
2 /* $Header: IGSPR15B.pls 115.4 2002/11/29 02:47:42 nsidana ship $ */
3 -------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 --smadathi 27-AUG-2001 Bug No. 1956374 .The function PRGP_VAL_APPEAL_DA removed
7 --smadathi 27-AUG-2001 Bug No. 1956374 .The function PRGP_VAL_CAUSE_DA removed
8 --smadathi 26-AUG-2001 Bug No. 1956374 .The function PRGP_VAL_DA_CLOSED removed
9 -------------------------------------------------------------------------------------------
10 -- Validate the appeal indicator being set
11 FUNCTION prgp_val_sprgc_apl(
12 p_appeal_ind IN VARCHAR2 ,
13 p_message_name OUT NOCOPY VARCHAR2 )
14 RETURN BOOLEAN AS
15 gv_other_detail VARCHAR2(255);
16 BEGIN -- prgp_val_sprgc_apl
17 -- Validate if appeal indicator is set to 'N' that no related
18 -- progression calendars have appeal lengths set
19 -- OR s_ou_prg_conf records with appeal_ind set to Y
20 -- OR s_crv_prg_conf records with appeal_ind set to Y
21 DECLARE
22 v_dummy VARCHAR2(1);
23 CURSOR c_spca IS
24 SELECT 'X'
25 FROM IGS_PR_S_PRG_CAL
26 WHERE appeal_length IS NOT NULL;
27 CURSOR c_sopc IS
28 SELECT 'X'
29 FROM IGS_PR_S_OU_PRG_CONF
30 WHERE appeal_ind = 'Y';
31 CURSOR c_scpc IS
32 SELECT 'X'
33 FROM IGS_PR_S_CRV_PRG_CON
34 WHERE appeal_ind = 'Y';
35 BEGIN
36 -- Set the default message name
37 p_message_name := Null;
38 IF p_appeal_ind = 'N' THEN
39 OPEN c_spca;
40 FETCH c_spca INTO v_dummy;
41 IF c_spca%FOUND THEN
42 p_message_name := 'IGS_PR_APPEAL_NOT_AVAILABLE';
43 CLOSE c_spca;
44 RETURN FALSE;
45 END IF;
46 CLOSE c_spca;
47 OPEN c_sopc;
48 FETCH c_sopc INTO v_dummy;
49 IF c_sopc%FOUND THEN
50 p_message_name := 'IGS_PR_REL_ORG_UNIT_PRG_AVAIL';
51 CLOSE c_sopc;
52 RETURN FALSE;
53 END IF;
54 CLOSE c_sopc;
55 OPEN c_scpc;
56 FETCH c_scpc INTO v_dummy;
57 IF c_scpc%FOUND THEN
58 p_message_name := 'IGS_PR_REL_COUR_VER_PRG_AVAIL';
59 CLOSE c_scpc;
60 RETURN FALSE;
61 END IF;
62 CLOSE c_scpc;
63 END IF;
64 RETURN TRUE;
65 EXCEPTION
66 WHEN OTHERS THEN
67 IF c_spca%ISOPEN THEN
68 CLOSE c_spca;
69 END IF;
70 IF c_sopc%ISOPEN THEN
71 CLOSE c_sopc;
72 END IF;
73 IF c_scpc%ISOPEN THEN
74 CLOSE c_scpc;
75 END IF;
76 RAISE;
77 END;
78 EXCEPTION
79 WHEN OTHERS THEN
80 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
81 FND_MESSAGE.SET_TOKEN ('NAME', 'IGS_PR_VAL_SPRGC.PRGP_VAL_SPRGC_APL');
82 IGS_GE_MSG_STACK.ADD;
83 App_Exception.Raise_Exception;
84 END prgp_val_sprgc_apl;
85 --
86 -- Validate the show cause indicator being set
87 FUNCTION prgp_val_sprgc_cause(
88 p_show_cause_ind IN VARCHAR2 ,
89 p_message_name OUT NOCOPY VARCHAR2 )
90 RETURN BOOLEAN AS
91 gv_other_detail VARCHAR2(255);
92 BEGIN -- prgp_val_sprgc_cause
93 -- Validate if show cause indicator is set to 'N' that no related
94 -- progression calendars have appeal lengths set
95 -- OR s_ou_prg_conf records with show_cause_ind set to Y
96 -- OR s_crv_prg_conf records with show_cause_ind set to Y
97 DECLARE
98 v_dummy VARCHAR2(1);
99 CURSOR c_spca IS
100 SELECT 'X'
101 FROM IGS_PR_S_PRG_CAL
102 WHERE show_cause_length IS NOT NULL;
103 CURSOR c_sopc IS
104 SELECT 'X'
105 FROM IGS_PR_S_OU_PRG_CONF
106 WHERE show_cause_ind = 'Y';
107 CURSOR c_scpc IS
108 SELECT 'X'
109 FROM IGS_PR_S_CRV_PRG_CON
110 WHERE show_cause_ind = 'Y';
111 BEGIN
112 -- Set the default message name
113 p_message_name := Null;
114 IF p_show_cause_ind = 'N' THEN
115 OPEN c_spca;
116 FETCH c_spca INTO v_dummy;
117 IF c_spca%FOUND THEN
118 p_message_name := 'IGS_PR_SHOW_CAUSE_NOT_AVAILAB';
119 CLOSE c_spca;
120 RETURN FALSE;
121 END IF;
122 CLOSE c_spca;
123 OPEN c_sopc;
124 FETCH c_sopc INTO v_dummy;
125 IF c_sopc%FOUND THEN
126 p_message_name := 'IGS_PR_ORG_UNIT_PRG_CONFIG';
127 CLOSE c_sopc;
128 RETURN FALSE;
129 END IF;
130 CLOSE c_sopc;
131 OPEN c_scpc;
132 FETCH c_scpc INTO v_dummy;
133 IF c_scpc%FOUND THEN
134 p_message_name := 'IGS_PR_COURS_VER_PRG_CONFIG';
135 CLOSE c_scpc;
136 RETURN FALSE;
137 END IF;
138 CLOSE c_scpc;
139 END IF;
140 RETURN TRUE ;
141 EXCEPTION
142 WHEN OTHERS THEN
143 IF c_spca%ISOPEN THEN
144 CLOSE c_spca;
145 END IF;
146 IF c_sopc%ISOPEN THEN
147 CLOSE c_sopc;
148 END IF;
149 IF c_scpc%ISOPEN THEN
150 CLOSE c_scpc;
151 END IF;
152 RAISE;
153 END;
154 EXCEPTION
155 WHEN OTHERS THEN
156 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
157 FND_MESSAGE.SET_TOKEN ('NAME', 'IGS_PR_VAL_SPRGC.PRGP_VAL_SPRGC_CAUSE');
158 IGS_GE_MSG_STACK.ADD;
159 App_Exception.Raise_Exception;
160 END prgp_val_sprgc_cause;
161 END IGS_PR_VAL_SPRGC;