[Home] [Help]
PACKAGE BODY: APPS.IGS_PS_VAL_AM
Source
1 PACKAGE BODY Igs_Ps_Val_Am AS
2 /* $Header: IGSPS09B.pls 120.2 2006/05/01 07:15:11 sommukhe noship $ */
3
4
5 -- Validate Govt Attendance Mode is not closed.
6 FUNCTION CRSP_VAL_AM_GOVT(
7 p_govt_attendance_mode IN VARCHAR2 ,
8 p_message_name OUT NOCOPY VARCHAR2 )
9 RETURN BOOLEAN AS
10 v_closed_ind IGS_PS_GOVT_ATD_MODE.closed_ind%TYPE;
11
12 CURSOR c_govt_attendance_mode(
13 cp_govt_attendance_mode IGS_EN_ATD_MODE.govt_attendance_mode%TYPE)IS
14 SELECT closed_ind
15 FROM IGS_PS_GOVT_ATD_MODE
16 WHERE govt_attendance_mode = cp_govt_attendance_mode;
17 BEGIN
18 p_message_name := NULL;
19 OPEN c_govt_attendance_mode(
20 p_govt_attendance_mode);
21 FETCH c_govt_attendance_mode INTO v_closed_ind;
22 IF(c_govt_attendance_mode%NOTFOUND) THEN
23 CLOSE c_govt_attendance_mode;
24 RETURN TRUE;
25 END IF;
26 CLOSE c_govt_attendance_mode;
27 IF (v_closed_ind = 'N') THEN
28 RETURN TRUE;
29 ELSE
30 p_message_name := 'IGS_PS_GOVE_ATTEND_MODE_CLOSE';
31 RETURN FALSE;
32 END IF;
33 EXCEPTION
34 WHEN OTHERS THEN
35 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
36 Fnd_Message.Set_Token('NAME','IGS_PS_VAL_AM.crsp_val_am_govt');
37 IGS_GE_MSG_STACK.ADD;
38 App_Exception.Raise_Exception;
39 END crsp_val_am_govt;
40
41
42 PROCEDURE log_messages ( p_msg_name IN VARCHAR2 ,
43 p_msg_val IN VARCHAR2
44 ) IS
45 ------------------------------------------------------------------
46
47 --Change History:
48 --Who When What
49 --sommukhe 17-APR-2006 Bug#4111831, include this procedure to log the messages.
50 -------------------------------------------------------------------
51 BEGIN
52
53 FND_MESSAGE.SET_NAME('IGS','IGS_FI_CAL_BALANCES_LOG');
54 FND_MESSAGE.SET_TOKEN('PARAMETER_NAME',p_msg_name);
55 FND_MESSAGE.SET_TOKEN('PARAMETER_VAL' ,p_msg_val) ;
56 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
57
58 END log_messages ;
59
60 PROCEDURE schedule_rollover (
61 errbuf OUT NOCOPY VARCHAR2,
62 retcode OUT NOCOPY NUMBER,
63 p_old_sch_version IN IGS_PS_CATLG_VERS_ALL.CATALOG_VERSION%TYPE,
64 p_new_sch_version IN IGS_PS_CATLG_VERS_ALL.CATALOG_VERSION%TYPE,
65 p_override_flag IN VARCHAR2,
66 p_debug_flag IN VARCHAR2,
67 p_org_id IN NUMBER ) AS
68 /*------------------------------------------------------------------
69
70 Change History:
71 Who When What
72 sommukhe 17-APR-2006 Bug#4111831, included logging for parameters, raising error with valid message when old and
73 new schedule versions are same,removed note id,note type id, schedule idfrom log as they are
74 not relevant to user.
75 -------------------------------------------------------------------*/
76 CURSOR cur_sel_sch_vers(p_sch_version IGS_PS_CATLG_VERS.CATALOG_VERSION%TYPE) IS
77 SELECT catalog_version_id,
78 catalog_version,
79 description,
80 closed_ind,
81 catalog_schedule,
82 created_by,
83 creation_date,
84 last_update_date,
85 last_update_login,
86 last_updated_by
87 FROM IGS_PS_CATLG_VERS
88 WHERE catalog_version = p_sch_version
89 AND catalog_schedule = 'SCHEDULE';
90
91 CURSOR cur_sel_old_sch_notes(p_sch_version_id IGS_PS_CATLG_NOTES.CATALOG_VERSION_ID%TYPE) IS
92 SELECT catalog_note_id,
93 catalog_version_id,
94 note_type_id,
95 create_date,
96 end_date,
97 SEQUENCE,
98 note_text,
99 created_by,
100 creation_date,
101 last_update_date,
102 last_update_login,
103 last_updated_by
104 FROM IGS_PS_CATLG_NOTES
105 WHERE catalog_version_id = p_sch_version_id;
106
107 CURSOR cur_sel_new_sch_notes(p_sch_version_id IGS_PS_CATLG_NOTES.CATALOG_VERSION_ID%TYPE,
108 p_note_type_id IGS_PS_CATLG_NOTES.NOTE_TYPE_ID%TYPE,
109 p_sequence IGS_PS_CATLG_NOTES.SEQUENCE%TYPE) IS
110 SELECT ROWID,
111 catalog_note_id,
112 catalog_version_id,
113 note_type_id,
114 create_date,
115 end_date,
116 SEQUENCE,
117 note_text,
118 created_by,
119 creation_date,
120 last_update_date,
121 last_update_login,
122 last_updated_by
123 FROM IGS_PS_CATLG_NOTES
124 WHERE catalog_version_id = p_sch_version_id
125 AND note_type_id = p_note_type_id
126 AND SEQUENCE = p_sequence
127 FOR UPDATE NOWAIT;
128
129
130 v_sel_sch_vers cur_sel_sch_vers%ROWTYPE;
131 v_sel_old_sch_notes cur_sel_old_sch_notes%ROWTYPE;
132 v_sel_new_sch_notes cur_sel_new_sch_notes%ROWTYPE;
133
134
135 lv_new_sch_version_id IGS_PS_CATLG_VERS.CATALOG_VERSION_ID%TYPE;
136 lv_old_sch_version_id IGS_PS_CATLG_VERS.CATALOG_VERSION_ID%TYPE;
137 lv_sch_note_id IGS_PS_CATLG_NOTES.CATALOG_NOTE_ID%TYPE;
138
139 INVALID EXCEPTION;
140
141 BEGIN
142
143 -- Set the multi org ID
144 igs_ge_gen_003.set_org_id(p_org_id);
145
146 retcode:=0;
147 /** logs all the parameters in the LOG **/
148 Fnd_Message.Set_Name('IGS','IGS_FI_ANC_LOG_PARM');
149 Fnd_File.Put_Line(Fnd_File.LOG,FND_MESSAGE.GET);
150 log_messages('Old schedule version :',p_old_sch_version);
151 log_messages('New schedule version :',p_new_sch_version);
152 log_messages('Override flag :',p_override_flag);
153 log_messages('Debug flag :',p_debug_flag);
154 fnd_file.put_line(fnd_file.LOG,' ');
155 IF p_old_sch_version = p_new_sch_version THEN
156 FND_MESSAGE.SET_NAME('IGS','IGS_PS_SCH_VER_SAME');
157 fnd_file.put_line(fnd_file.LOG,FND_MESSAGE.GET);
158 fnd_file.put_line(fnd_file.LOG,' ');
159 RAISE INVALID;
160 END IF;
161 OPEN cur_sel_sch_vers(p_old_sch_version);
162 FETCH cur_sel_sch_vers INTO v_sel_sch_vers;
163 lv_old_sch_version_id := v_sel_sch_vers.catalog_version_id;
164 CLOSE cur_sel_sch_vers;
165
166 OPEN cur_sel_sch_vers(p_new_sch_version);
167 FETCH cur_sel_sch_vers INTO v_sel_sch_vers;
168 lv_new_sch_version_id := v_sel_sch_vers.catalog_version_id;
169 IF cur_sel_sch_vers%NOTFOUND THEN
170 CLOSE cur_sel_sch_vers;
171 OPEN cur_sel_sch_vers(p_old_sch_version);
172 FETCH cur_sel_sch_vers INTO v_sel_sch_vers;
173 IF cur_sel_sch_vers%NOTFOUND THEN
174 FND_MESSAGE.SET_NAME('IGS','IGS_PS_NO_SCH_VER');
175 fnd_file.put_line(fnd_file.LOG,FND_MESSAGE.GET);
176 fnd_file.put_line(fnd_file.LOG,' ');
177 RAISE INVALID;
178 ELSE
179 DECLARE
180 lv_rowid VARCHAR2(25);
181 BEGIN
182 igs_ps_catlg_vers_pkg.insert_row(
183 x_rowid => lv_rowid,
184 x_catalog_version_id => lv_new_sch_version_id,
185 x_catalog_version => p_new_sch_version,
186 x_description => v_sel_sch_vers.description,
187 x_closed_ind => v_sel_sch_vers.closed_ind,
188 x_catalog_schedule => v_sel_sch_vers.catalog_schedule,
189 x_mode => 'R',
190 x_org_id => p_org_id);
191 END;
192 END IF;
193 IF p_debug_flag = 'Y' THEN
194 FND_MESSAGE.SET_NAME('IGS','IGS_PS_NEW_SCH');
195 fnd_file.put_line(fnd_file.LOG,FND_MESSAGE.GET||lv_new_sch_version_id||' '||
196 p_new_sch_version ||' '||v_sel_sch_vers.description||' '||
197 v_sel_sch_vers.closed_ind||' '||v_sel_sch_vers.catalog_schedule||' '||
198 v_sel_old_sch_notes.note_text);
199 END IF;
200 CLOSE cur_sel_sch_vers;
201 END IF;
202 --Second part
203 OPEN cur_sel_old_sch_notes(lv_old_sch_version_id);
204 LOOP
205 FETCH cur_sel_old_sch_notes INTO v_sel_old_sch_notes;
206 IF cur_sel_old_sch_notes%NOTFOUND THEN
207 EXIT;
208 END IF;
209 OPEN cur_sel_new_sch_notes( lv_new_sch_version_id, --v_sel_old_sch_notes.catalog_version_id,
210 v_sel_old_sch_notes.note_type_id,
211 v_sel_old_sch_notes.SEQUENCE);
212 FETCH cur_sel_new_sch_notes INTO v_sel_new_sch_notes;
213 IF cur_sel_new_sch_notes%NOTFOUND THEN
214 DECLARE
215 lv_rowid VARCHAR2(25);
216 BEGIN
217 igs_ps_catlg_notes_pkg.insert_row(
218 x_rowid => lv_rowid,
219 x_catalog_note_id => lv_sch_note_id,
220 x_catalog_version_id => lv_new_sch_version_id,
221 x_note_type_id => v_sel_old_sch_notes.note_type_id,
222 x_create_date => v_sel_old_sch_notes.create_date,
223 x_end_date => v_sel_old_sch_notes.end_date,
224 x_sequence => v_sel_old_sch_notes.SEQUENCE,
225 x_note_text => v_sel_old_sch_notes.note_text,
226 x_mode => 'R',
227 x_org_id => p_org_id );
228 END;
229 IF p_debug_flag = 'Y' THEN
230 FND_MESSAGE.SET_NAME('IGS','IGS_PS_NEW_SCH_NOTES');
231 fnd_file.put_line(fnd_file.LOG,FND_MESSAGE.GET||v_sel_old_sch_notes.create_date||' '||
232 v_sel_old_sch_notes.end_date||' '||v_sel_old_sch_notes.SEQUENCE||' '||
233 v_sel_old_sch_notes.note_text);
234 END IF;
235 ELSE
236 IF p_override_flag = 'Y' THEN
237 igs_ps_catlg_notes_pkg.update_row(
238 x_rowid => v_sel_new_sch_notes.ROWID,
239 x_catalog_note_id => v_sel_new_sch_notes.catalog_note_id,
240 x_catalog_version_id => v_sel_new_sch_notes.catalog_version_id,
241 x_note_type_id => v_sel_new_sch_notes.note_type_id,
242 x_create_date => v_sel_new_sch_notes.create_date,
243 x_end_date => v_sel_new_sch_notes.end_date,
244 x_sequence => v_sel_new_sch_notes.SEQUENCE,
245 x_note_text => v_sel_old_sch_notes.note_text,
246 x_mode => 'R');
247 IF p_debug_flag = 'Y' THEN
248 FND_MESSAGE.SET_NAME('IGS','IGS_PS_UPD_SCH_NOTES');
249 fnd_file.put_line(fnd_file.LOG,FND_MESSAGE.GET||v_sel_new_sch_notes.create_date||' '|| v_sel_new_sch_notes.end_date||' '||
250 v_sel_new_sch_notes.SEQUENCE||' '|| v_sel_new_sch_notes.note_text);
251 END IF;
252 END IF;
253 END IF;
254 CLOSE cur_sel_new_sch_notes ;
255 END LOOP;
256 CLOSE cur_sel_old_sch_notes ;
257 EXCEPTION
258 WHEN INVALID THEN
259 ROLLBACK;
260 RETCODE:=2;
261 WHEN OTHERS THEN
262 ROLLBACK;
263 RETCODE:=2;
264 ERRBUF:=FND_MESSAGE.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION');
265 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
266 END schedule_rollover;
267 END Igs_Ps_Val_Am;