[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_CAL_CONF_PKG
Source
1 package body IGS_AD_CAL_CONF_PKG as
2 /* $Header: IGSAI70B.pls 115.7 2002/11/28 22:13:48 nsidana ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_AD_CAL_CONF%RowType;
6 new_references IGS_AD_CAL_CONF%RowType;
7
8 PROCEDURE Set_Column_Values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_s_control_num IN NUMBER DEFAULT NULL,
12 x_initialise_adm_perd_dt_alias IN VARCHAR2 DEFAULT NULL,
13 x_adm_appl_encmb_chk_dt_alias IN VARCHAR2 DEFAULT NULL,
14 x_ad_appl_course_strt_dt_alias IN VARCHAR2 DEFAULT NULL,
15 x_adm_appl_short_strt_dt_alias IN VARCHAR2 DEFAULT NULL,
16 x_adm_appl_due_dt_alias IN VARCHAR2 DEFAULT NULL,
17 x_adm_appl_final_dt_alias IN VARCHAR2 DEFAULT NULL,
18 x_ad_appl_chg_of_pref_dt_alias IN VARCHAR2 DEFAULT NULL,
19 x_adm_appl_offer_resp_dt_alias IN VARCHAR2 DEFAULT NULL,
20 x_ad_appl_e_comp_perd_dt_alias IN VARCHAR2 DEFAULT NULL,
21 x_ad_appl_m_comp_perd_dt_alias IN VARCHAR2 DEFAULT NULL,
22 x_ad_appl_s_comp_perd_dt_alias IN VARCHAR2 DEFAULT NULL,
23 x_creation_date IN DATE DEFAULT NULL,
24 x_created_by IN NUMBER DEFAULT NULL,
25 x_last_update_date IN DATE DEFAULT NULL,
26 x_last_updated_by IN NUMBER DEFAULT NULL,
27 x_last_update_login IN NUMBER DEFAULT NULL,
28 X_ADM_PRC_TRK_DT_ALIAS IN VARCHAR2 DEFAULT NULL,
29 X_POST_ADM_TRK_DT_ALIAS IN VARCHAR2 DEFAULT NULL,
30 X_INQ_CAL_TYPE IN VARCHAR2 DEFAULT NULL
31 ) AS
32
33 CURSOR cur_old_ref_values IS
34 SELECT *
35 FROM IGS_AD_CAL_CONF
36 WHERE rowid = x_rowid;
37
38 BEGIN
39
40 l_rowid := x_rowid;
41
42 -- Code for setting the Old and New Reference Values.
43 -- Populate Old Values.
44 Open cur_old_ref_values;
45 Fetch cur_old_ref_values INTO old_references;
46 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT')) THEN
47 Close cur_old_ref_values;
48 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
49 IGS_GE_MSG_STACK.ADD;
50 App_Exception.Raise_Exception;
51 Return;
52 END IF;
53 Close cur_old_ref_values;
54
55 -- Populate New Values.
56 new_references.s_control_num := x_s_control_num;
57 new_references.initialise_adm_perd_dt_alias := x_initialise_adm_perd_dt_alias;
58 new_references.adm_appl_encmb_chk_dt_alias := x_adm_appl_encmb_chk_dt_alias;
59 new_references.adm_appl_course_strt_dt_alias := x_ad_appl_course_strt_dt_alias;
60 new_references.adm_appl_short_strt_dt_alias := x_adm_appl_short_strt_dt_alias;
61 new_references.adm_appl_due_dt_alias := x_adm_appl_due_dt_alias;
62 new_references.adm_appl_final_dt_alias := x_adm_appl_final_dt_alias;
63 new_references.adm_appl_chng_of_pref_dt_alias := x_ad_appl_chg_of_pref_dt_alias;
64 new_references.adm_appl_offer_resp_dt_alias := x_adm_appl_offer_resp_dt_alias;
65 new_references.adm_appl_e_comp_perd_dt_alias := x_ad_appl_e_comp_perd_dt_alias;
66 new_references.adm_appl_m_comp_perd_dt_alias := x_ad_appl_m_comp_perd_dt_alias;
67 new_references.adm_appl_s_comp_perd_dt_alias := x_ad_appl_s_comp_perd_dt_alias;
68 new_references.ADM_PRC_TRK_DT_ALIAS := X_ADM_PRC_TRK_DT_ALIAS;
69 new_references.POST_ADM_TRK_DT_ALIAS := X_POST_ADM_TRK_DT_ALIAS;
70 new_references.INQ_CAL_TYPE := X_INQ_CAL_TYPE;
71 IF (p_action = 'UPDATE') THEN
72 new_references.creation_date := old_references.creation_date;
73 new_references.created_by := old_references.created_by;
74 ELSE
75 new_references.creation_date := x_creation_date;
76 new_references.created_by := x_created_by;
77 END IF;
78 new_references.last_update_date := x_last_update_date;
79 new_references.last_updated_by := x_last_updated_by;
80 new_references.last_update_login := x_last_update_login;
81
82 END Set_Column_Values;
83
84 PROCEDURE BeforeRowInsertUpdate1(
85 p_inserting IN BOOLEAN DEFAULT FALSE,
86 p_updating IN BOOLEAN DEFAULT FALSE,
87 p_deleting IN BOOLEAN DEFAULT FALSE
88 ) AS
89 v_message_name VARCHAR2(30);
90 BEGIN
91 -- Validate the date alias values.
92 -- Admission Application Offer Response Date Alias.
93 IF p_inserting OR
94 ((NVL(old_references.adm_appl_offer_resp_dt_alias, 'NULL') <>
95 NVL(new_references.adm_appl_offer_resp_dt_alias, 'NULL')) AND
96 new_references.adm_appl_offer_resp_dt_alias IS NOT NULL) THEN
97 IF IGS_AD_VAL_SACCO.admp_val_sacco_da (
98 new_references.adm_appl_offer_resp_dt_alias,
99 'ADM_APPL_OFFER_RESP_DT_ALIAS',
100 v_message_name) = FALSE THEN
101 Fnd_Message.Set_Name('IGS',v_message_name);
102 IGS_GE_MSG_STACK.ADD;
103 App_Exception.Raise_Exception;
104 END IF;
105 END IF;
106
107 -- Admission Application Change Of Preference Date Alias.
108 IF p_inserting OR
109 ((NVL(old_references.adm_appl_chng_of_pref_dt_alias, 'NULL') <>
110 NVL(new_references.adm_appl_chng_of_pref_dt_alias, 'NULL')) AND
111 new_references.adm_appl_chng_of_pref_dt_alias IS NOT NULL) THEN
112 IF IGS_AD_VAL_SACCO.admp_val_sacco_da (
113 new_references.adm_appl_chng_of_pref_dt_alias,
114 'ADM_APPL_CHNG_OF_PREF_DT_ALIAS',
115 v_message_name) = FALSE THEN
116 Fnd_Message.Set_Name('IGS',v_message_name);
117 IGS_GE_MSG_STACK.ADD;
118 App_Exception.Raise_Exception;
119 END IF;
120 END IF;
121 -- Admission Application Due Date Alias.
122 IF p_inserting OR
123 ((NVL(old_references.adm_appl_due_dt_alias, 'NULL') <>
124 NVL(new_references.adm_appl_due_dt_alias, 'NULL')) AND
125 new_references.adm_appl_due_dt_alias IS NOT NULL) THEN
126 IF IGS_AD_VAL_SACCO.admp_val_sacco_da (
127 new_references.adm_appl_due_dt_alias,
128 'ADM_APPL_DUE_DT_ALIAS',
129 v_message_name) = FALSE THEN
130 Fnd_Message.Set_Name('IGS',v_message_name);
131 IGS_GE_MSG_STACK.ADD;
132 App_Exception.Raise_Exception;
133 END IF;
134 END IF;
135 -- Admission Application Final Date Alias.
136 IF p_inserting OR
137 ((NVL(old_references.adm_appl_final_dt_alias, 'NULL') <>
138 NVL(new_references.adm_appl_final_dt_alias, 'NULL')) AND
139 new_references.adm_appl_final_dt_alias IS NOT NULL) THEN
140 IF IGS_AD_VAL_SACCO.admp_val_sacco_da (
141 new_references.adm_appl_final_dt_alias,
142 'ADM_APPL_FINAL_DT_ALIAS',
143 v_message_name) = FALSE THEN
144 Fnd_Message.Set_Name('IGS',v_message_name);
145 IGS_GE_MSG_STACK.ADD;
146 App_Exception.Raise_Exception;
147 END IF;
148 END IF;
149 -- Initialise Admission Period Date Alias .
150 IF p_inserting OR
151 ((NVL(old_references.initialise_adm_perd_dt_alias, 'NULL') <>
152 NVL(new_references.initialise_adm_perd_dt_alias, 'NULL')) AND
153 new_references.initialise_adm_perd_dt_alias IS NOT NULL) THEN
154 IF IGS_AD_VAL_SACCO.admp_val_sacco_da (
155 new_references.initialise_adm_perd_dt_alias,
156 'INITIALISE_ADM_PERD_DT_ALIAS',
157 v_message_name) = FALSE THEN
158 Fnd_Message.Set_Name('IGS',v_message_name);
159 IGS_GE_MSG_STACK.ADD;
160 App_Exception.Raise_Exception;
161 END IF;
162 END IF;
163 -- Admission Application Encumbrance Checking Date Alias.
164 IF p_inserting OR
165 ((NVL(old_references.adm_appl_encmb_chk_dt_alias, 'NULL') <>
166 NVL(new_references.adm_appl_encmb_chk_dt_alias, 'NULL')) AND
167 new_references.adm_appl_encmb_chk_dt_alias IS NOT NULL) THEN
168 IF IGS_AD_VAL_SACCO.admp_val_sacco_da (
169 new_references.adm_appl_encmb_chk_dt_alias,
170 'ADM_APPL_ENCMB_CHK_DT_ALIAS',
171 v_message_name) = FALSE THEN
172 Fnd_Message.Set_Name('IGS',v_message_name);
173 IGS_GE_MSG_STACK.ADD;
174 App_Exception.Raise_Exception;
175 END IF;
176 END IF;
177 -- Admission Application IGS_PS_COURSE Start Date Alias.
178 IF p_inserting OR
179 ((NVL(old_references.adm_appl_course_strt_dt_alias, 'NULL') <>
180 NVL(new_references.adm_appl_course_strt_dt_alias, 'NULL')) AND
181 new_references.adm_appl_course_strt_dt_alias IS NOT NULL) THEN
182 IF IGS_AD_VAL_SACCO.admp_val_sacco_da (
183 new_references.adm_appl_course_strt_dt_alias,
184 'ADM_APPL_COURSE_STRT_DT_ALIAS',
185 v_message_name) = FALSE THEN
186 Fnd_Message.Set_Name('IGS',v_message_name);
187 IGS_GE_MSG_STACK.ADD;
188 App_Exception.Raise_Exception;
189 END IF;
190 END IF;
191
192 -- Admission Application Short Admission Start Date Alias.
193 IF p_inserting OR
194 ((NVL(old_references.adm_appl_short_strt_dt_alias, 'NULL') <>
195 NVL(new_references.adm_appl_short_strt_dt_alias, 'NULL')) AND
196 new_references.adm_appl_short_strt_dt_alias IS NOT NULL) THEN
197 IF IGS_AD_VAL_SACCO.admp_val_sacco_da (
198 new_references.adm_appl_short_strt_dt_alias,
199 'ADM_APPL_SHORT_STRT_DT_ALIAS',
200 v_message_name) = FALSE THEN
201 Fnd_Message.Set_Name('IGS',v_message_name);
202 IGS_GE_MSG_STACK.ADD;
203 App_Exception.Raise_Exception;
204 END IF;
205 END IF;
206 -- Admission Application End Completion Period Date Alias.
207 IF p_inserting OR
208 ((NVL(old_references.adm_appl_e_comp_perd_dt_alias, 'NULL') <>
209 NVL(new_references.adm_appl_e_comp_perd_dt_alias, 'NULL')) AND
210 new_references.adm_appl_e_comp_perd_dt_alias IS NOT NULL) THEN
211 IF IGS_AD_VAL_SACCO.admp_val_sacco_da (
212 new_references.adm_appl_e_comp_perd_dt_alias,
213 'ADM_APPL_E_COMP_PERD_DT_ALIAS',
214 v_message_name) = FALSE THEN
215 Fnd_Message.Set_Name('IGS',v_message_name);
216 IGS_GE_MSG_STACK.ADD;
217 App_Exception.Raise_Exception;
218 END IF;
219 END IF;
220 -- Admission Application Mid Completion Period Date Alias.
221 IF p_inserting OR
222 ((NVL(old_references.adm_appl_m_comp_perd_dt_alias, 'NULL') <>
223 NVL(new_references.adm_appl_m_comp_perd_dt_alias, 'NULL')) AND
224 new_references.adm_appl_m_comp_perd_dt_alias IS NOT NULL) THEN
225 IF IGS_AD_VAL_SACCO.admp_val_sacco_da (
226 new_references.adm_appl_m_comp_perd_dt_alias,
227 'ADM_APPL_M_COMP_PERD_DT_ALIAS',
228 v_message_name) = FALSE THEN
229 Fnd_Message.Set_Name('IGS',v_message_name);
230 IGS_GE_MSG_STACK.ADD;
231 App_Exception.Raise_Exception;
232 END IF;
233 END IF;
234 -- Admission Application Summer Completion Period Date Alias.
235 IF p_inserting OR
236 ((NVL(old_references.adm_appl_s_comp_perd_dt_alias, 'NULL') <>
237 NVL(new_references.adm_appl_s_comp_perd_dt_alias, 'NULL')) AND
238 new_references.adm_appl_s_comp_perd_dt_alias IS NOT NULL) THEN
239 IF IGS_AD_VAL_SACCO.admp_val_sacco_da (
240 new_references.adm_appl_s_comp_perd_dt_alias,
241 'ADM_APPL_S_COMP_PERD_DT_ALIAS',
242 v_message_name) = FALSE THEN
243 Fnd_Message.Set_Name('IGS',v_message_name);
244 IGS_GE_MSG_STACK.ADD;
245 App_Exception.Raise_Exception;
246 END IF;
247 END IF;
248
249 END BeforeRowInsertUpdate1;
250
251 PROCEDURE Check_Constraints (
252 Column_Name IN VARCHAR2 DEFAULT NULL,
253 Column_Value IN VARCHAR2 DEFAULT NULL
254 )
255 AS
256 BEGIN
257 IF Column_Name is null then
258 NULL;
259 ELSIF upper(Column_Name) = 'ADM_APPL_CHNG_OF_PREF_DT_ALIAS' then
260 new_references.adm_appl_chng_of_pref_dt_alias := column_value;
261 ELSIF upper(Column_Name) = 'ADM_APPL_DUE_DT_ALIAS' then
262 new_references.adm_appl_due_dt_alias := column_value;
263 ELSIF upper(Column_Name) = 'ADM_APPL_FINAL_DT_ALIAS' then
264 new_references.adm_appl_final_dt_alias := column_value;
265 ELSIF upper(Column_Name) = 'ADM_APPL_OFFER_RESP_DT_ALIAS' then
266 new_references.adm_appl_offer_resp_dt_alias := column_value;
267 ELSIF upper(Column_Name) = 'INITIALISE_ADM_PERD_DT_ALIAS' then
268 new_references.initialise_adm_perd_dt_alias := column_value;
269 ELSIF upper(Column_Name) = 'ADM_APPL_ENCMB_CHK_DT_ALIAS' then
270 new_references.adm_appl_encmb_chk_dt_alias := column_value;
271 ELSIF upper(Column_Name) = 'ADM_APPL_SHORT_STRT_DT_ALIAS' then
272 new_references.adm_appl_short_strt_dt_alias := column_value;
273 ELSIF upper(Column_Name) = 'ADM_APPL_E_COMP_PERD_DT_ALIAS' then
274 new_references.adm_appl_e_comp_perd_dt_alias := column_value;
275 ELSIF upper(Column_Name) = 'ADM_APPL_M_COMP_PERD_DT_ALIAS' then
276 new_references.adm_appl_m_comp_perd_dt_alias := column_value;
277 ELSIF upper(Column_Name) = 'ADM_APPL_S_COMP_PERD_DT_ALIAS' then
278 new_references.adm_appl_s_comp_perd_dt_alias := column_value;
279 ELSIF upper(Column_Name) = 'ADM_APPL_COURSE_STRT_DT_ALIAS' then
280 new_references.adm_appl_course_strt_dt_alias := column_value;
281 ELSIF upper(Column_Name) = 'S_CONTROL_NUM' then
282 new_references.s_control_num := igs_ge_number.to_num(column_value);
283 ELSIF upper(Column_Name) = 'ADM_PRC_TRK_DT_ALIAS' then
284 new_references.adm_appl_course_strt_dt_alias := column_value;
288 new_references.inq_cal_type := column_value;
285 ELSIF upper(Column_Name) = 'POST_ADM_TRK_DT_ALIAS' then
286 new_references.adm_appl_course_strt_dt_alias := column_value;
287 ELSIF upper(Column_Name) = 'INQ_CAL_TYPE' then
289 END IF;
290
291 IF upper(Column_Name) = 'ADM_APPL_CHNG_OF_PREF_DT_ALIAS' OR Column_Name IS NULL THEN
292 IF new_references.adm_appl_chng_of_pref_dt_alias <> UPPER(new_references.adm_appl_chng_of_pref_dt_alias) THEN
293 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
294 IGS_GE_MSG_STACK.ADD;
295 App_Exception.Raise_Exception;
296 END IF;
297 END IF;
298 IF upper(Column_Name) = 'ADM_APPL_DUE_DT_ALIAS' OR Column_Name IS NULL THEN
299 IF new_references.adm_appl_due_dt_alias <> UPPER(new_references.adm_appl_due_dt_alias) THEN
300 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
301 IGS_GE_MSG_STACK.ADD;
302 App_Exception.Raise_Exception;
303 END IF;
304 END IF;
305 IF upper(Column_Name) = 'ADM_APPL_FINAL_DT_ALIAS' OR Column_Name IS NULL THEN
306 IF new_references.adm_appl_final_dt_alias <> UPPER(new_references.adm_appl_final_dt_alias) THEN
307 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
308 IGS_GE_MSG_STACK.ADD;
309 App_Exception.Raise_Exception;
310 END IF;
311 END IF;
312 IF upper(Column_Name) = 'ADM_APPL_OFFER_RESP_DT_ALIAS' OR Column_Name IS NULL THEN
313 IF new_references.adm_appl_offer_resp_dt_alias <> UPPER(new_references.adm_appl_offer_resp_dt_alias) THEN
314 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
315 IGS_GE_MSG_STACK.ADD;
316 App_Exception.Raise_Exception;
317 END IF;
318 END IF;
319 IF upper(Column_Name) = 'INITIALISE_ADM_PERD_DT_ALIAS' OR Column_Name IS NULL THEN
320 IF new_references.initialise_adm_perd_dt_alias <> UPPER(new_references.initialise_adm_perd_dt_alias) THEN
321 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
322 IGS_GE_MSG_STACK.ADD;
323 App_Exception.Raise_Exception;
324 END IF;
325 END IF;
326 IF upper(Column_Name) = 'ADM_APPL_ENCMB_CHK_DT_ALIAS' OR Column_Name IS NULL THEN
327 IF new_references.adm_appl_encmb_chk_dt_alias <> UPPER(new_references.adm_appl_encmb_chk_dt_alias) THEN
328 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
329 IGS_GE_MSG_STACK.ADD;
330 App_Exception.Raise_Exception;
331 END IF;
332 END IF;
333 IF upper(Column_Name) = 'ADM_APPL_SHORT_STRT_DT_ALIAS' OR Column_Name IS NULL THEN
334 IF new_references.adm_appl_short_strt_dt_alias <> UPPER(new_references.adm_appl_short_strt_dt_alias) THEN
335 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
336 IGS_GE_MSG_STACK.ADD;
337 App_Exception.Raise_Exception;
338 END IF;
339 END IF;
340 IF upper(Column_Name) = 'ADM_APPL_E_COMP_PERD_DT_ALIAS' OR Column_Name IS NULL THEN
341 IF new_references.adm_appl_e_comp_perd_dt_alias <> UPPER(new_references.adm_appl_e_comp_perd_dt_alias) THEN
342 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
343 IGS_GE_MSG_STACK.ADD;
344 App_Exception.Raise_Exception;
345 END IF;
346 END IF;
350 IGS_GE_MSG_STACK.ADD;
347 IF upper(Column_Name) = 'ADM_APPL_M_COMP_PERD_DT_ALIAS' OR Column_Name IS NULL THEN
348 IF new_references.adm_appl_m_comp_perd_dt_alias <> UPPER(new_references.adm_appl_m_comp_perd_dt_alias) THEN
349 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
351 App_Exception.Raise_Exception;
352 END IF;
353 END IF;
354 IF upper(Column_Name) = 'ADM_APPL_S_COMP_PERD_DT_ALIAS' OR Column_Name IS NULL THEN
355 IF new_references.adm_appl_s_comp_perd_dt_alias <> UPPER(new_references.adm_appl_s_comp_perd_dt_alias) THEN
356 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
357 IGS_GE_MSG_STACK.ADD;
358 App_Exception.Raise_Exception;
359 END IF;
360 END IF;
361 IF upper(Column_Name) = 'ADM_APPL_COURSE_STRT_DT_ALIAS' OR Column_Name IS NULL THEN
362 IF new_references.adm_appl_course_strt_dt_alias <> UPPER(new_references.adm_appl_course_strt_dt_alias) THEN
363 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
364 IGS_GE_MSG_STACK.ADD;
365 App_Exception.Raise_Exception;
366 END IF;
367 END IF;
368 IF upper(Column_Name) = 'S_CONTROL_NUM' OR Column_Name IS NULL THEN
369 IF new_references.s_control_num <> 1 THEN
370 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
371 IGS_GE_MSG_STACK.ADD;
372 App_Exception.Raise_Exception;
373 END IF;
374 END IF;
375
376 IF upper(Column_Name) = 'ADM_PRC_TRK_DT_ALIAS' OR Column_Name IS NULL THEN
377 IF new_references.ADM_PRC_TRK_DT_ALIAS <> UPPER(new_references.ADM_PRC_TRK_DT_ALIAS) THEN
378 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
379 IGS_GE_MSG_STACK.ADD;
380 App_Exception.Raise_Exception;
381 END IF;
382 END IF;
383
384 IF upper(Column_Name) = 'POST_ADM_TRK_DT_ALIAS' OR Column_Name IS NULL THEN
385 IF new_references.POST_ADM_TRK_DT_ALIAS <> UPPER(new_references.POST_ADM_TRK_DT_ALIAS) THEN
386 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
387 IGS_GE_MSG_STACK.ADD;
388 App_Exception.Raise_Exception;
389 END IF;
390 END IF;
391
392 IF upper(Column_Name) = 'INQ_CAL_TYPE' OR Column_Name IS NULL THEN
393 IF new_references.INQ_CAL_TYPE <> UPPER(new_references.INQ_CAL_TYPE) THEN
394 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
395 IGS_GE_MSG_STACK.ADD;
396 App_Exception.Raise_Exception;
397 END IF;
398 END IF;
399
400 END Check_Constraints;
401
402 PROCEDURE Check_Parent_Existance AS
403 BEGIN
404
405 IF (((old_references.adm_appl_course_strt_dt_alias = new_references.adm_appl_course_strt_dt_alias)) OR
406 ((new_references.adm_appl_course_strt_dt_alias IS NULL))) THEN
407 NULL;
408 ELSE
409 IF NOT IGS_CA_DA_PKG.Get_PK_For_Validation (
410 new_references.adm_appl_course_strt_dt_alias
411 ) THEN
412 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
413 IGS_GE_MSG_STACK.ADD;
414 App_Exception.Raise_Exception;
415 END IF;
416 END IF;
417
418 IF (((old_references.adm_appl_final_dt_alias = new_references.adm_appl_final_dt_alias)) OR
419 ((new_references.adm_appl_final_dt_alias IS NULL))) THEN
420 NULL;
421 ELSE
422 IF NOT IGS_CA_DA_PKG.Get_PK_For_Validation (
423 new_references.adm_appl_final_dt_alias
424 ) THEN
425 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
426 IGS_GE_MSG_STACK.ADD;
427 App_Exception.Raise_Exception;
428 END IF;
429 END IF;
430
431 IF (((old_references.adm_appl_due_dt_alias = new_references.adm_appl_due_dt_alias)) OR
432 ((new_references.adm_appl_due_dt_alias IS NULL))) THEN
433 NULL;
434 ELSE
435 IF NOT IGS_CA_DA_PKG.Get_PK_For_Validation (
436 new_references.adm_appl_due_dt_alias
437 ) THEN
438 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
439 IGS_GE_MSG_STACK.ADD;
440 App_Exception.Raise_Exception;
441 END IF;
442 END IF;
443
444 IF (((old_references.adm_appl_encmb_chk_dt_alias = new_references.adm_appl_encmb_chk_dt_alias)) OR
445 ((new_references.adm_appl_encmb_chk_dt_alias IS NULL))) THEN
446 NULL;
447 ELSE
448 IF NOT IGS_CA_DA_PKG.Get_PK_For_Validation (
449 new_references.adm_appl_encmb_chk_dt_alias
450 ) THEN
451 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
452 IGS_GE_MSG_STACK.ADD;
453 App_Exception.Raise_Exception;
454 END IF;
455 END IF;
456
457 IF (((old_references.adm_appl_e_comp_perd_dt_alias = new_references.adm_appl_e_comp_perd_dt_alias)) OR
458 ((new_references.adm_appl_e_comp_perd_dt_alias IS NULL))) THEN
459 NULL;
460 ELSE
461 IF NOT IGS_CA_DA_PKG.Get_PK_For_Validation (
462 new_references.adm_appl_e_comp_perd_dt_alias
463 ) THEN
464 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
465 IGS_GE_MSG_STACK.ADD;
466 App_Exception.Raise_Exception;
467 END IF;
468 END IF;
469
470 IF (((old_references.initialise_adm_perd_dt_alias = new_references.initialise_adm_perd_dt_alias)) OR
474 IF NOT IGS_CA_DA_PKG.Get_PK_For_Validation (
471 ((new_references.initialise_adm_perd_dt_alias IS NULL))) THEN
472 NULL;
473 ELSE
475 new_references.initialise_adm_perd_dt_alias
476 ) THEN
477 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
478 IGS_GE_MSG_STACK.ADD;
479 App_Exception.Raise_Exception;
480 END IF;
481 END IF;
482
483 IF (((old_references.adm_appl_m_comp_perd_dt_alias = new_references.adm_appl_m_comp_perd_dt_alias)) OR
484 ((new_references.adm_appl_m_comp_perd_dt_alias IS NULL))) THEN
485 NULL;
486 ELSE
487 IF NOT IGS_CA_DA_PKG.Get_PK_For_Validation (
488 new_references.adm_appl_m_comp_perd_dt_alias
489 ) THEN
490 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
491 IGS_GE_MSG_STACK.ADD;
492 App_Exception.Raise_Exception;
493 END IF;
494 END IF;
495
496 IF (((old_references.adm_appl_chng_of_pref_dt_alias = new_references.adm_appl_chng_of_pref_dt_alias)) OR
497 ((new_references.adm_appl_chng_of_pref_dt_alias IS NULL))) THEN
498 NULL;
499 ELSE
500 IF NOT IGS_CA_DA_PKG.Get_PK_For_Validation (
501 new_references.adm_appl_chng_of_pref_dt_alias
502 ) THEN
503 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
504 IGS_GE_MSG_STACK.ADD;
505 App_Exception.Raise_Exception;
506 END IF;
507 END IF;
508
509 IF (((old_references.adm_appl_offer_resp_dt_alias = new_references.adm_appl_offer_resp_dt_alias)) OR
510 ((new_references.adm_appl_offer_resp_dt_alias IS NULL))) THEN
511 NULL;
512 ELSE
513 IF NOT IGS_CA_DA_PKG.Get_PK_For_Validation (
514 new_references.adm_appl_offer_resp_dt_alias
515 ) THEN
516 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
517 IGS_GE_MSG_STACK.ADD;
518 App_Exception.Raise_Exception;
519 END IF;
520 END IF;
521
522 IF (((old_references.adm_appl_short_strt_dt_alias = new_references.adm_appl_short_strt_dt_alias)) OR
523 ((new_references.adm_appl_short_strt_dt_alias IS NULL))) THEN
524 NULL;
525 ELSE
526 IF NOT IGS_CA_DA_PKG.Get_PK_For_Validation (
527 new_references.adm_appl_short_strt_dt_alias
528 ) THEN
529 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
530 IGS_GE_MSG_STACK.ADD;
531 App_Exception.Raise_Exception;
532 END IF;
533 END IF;
534
535 IF (((old_references.adm_appl_s_comp_perd_dt_alias = new_references.adm_appl_s_comp_perd_dt_alias)) OR
536 ((new_references.adm_appl_s_comp_perd_dt_alias IS NULL))) THEN
537 NULL;
538 ELSE
539 IF NOT IGS_CA_DA_PKG.Get_PK_For_Validation (
540 new_references.adm_appl_s_comp_perd_dt_alias
541 ) THEN
542 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
543 IGS_GE_MSG_STACK.ADD;
544 App_Exception.Raise_Exception;
545 END IF;
546 END IF;
547
548 IF (((old_references.inq_cal_type = new_references.inq_cal_type)) OR
549 ((new_references.inq_cal_type IS NULL))) THEN
550 NULL;
551 ELSE
552 IF NOT IGS_CA_TYPE_PKG.Get_PK_For_Validation (
553 new_references.inq_cal_type
554 ) THEN
555 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
556 IGS_GE_MSG_STACK.ADD;
557 App_Exception.Raise_Exception;
558 END IF;
559 END IF;
560
561 END Check_Parent_Existance;
562
563 FUNCTION Get_PK_For_Validation (
564 x_s_control_num IN NUMBER
565 )return BOOLEAN AS
566
567 CURSOR cur_rowid IS
568 SELECT rowid
569 FROM IGS_AD_CAL_CONF
570 WHERE s_control_num = x_s_control_num
571 FOR UPDATE NOWAIT;
572 lv_rowid cur_rowid%RowType;
573
574 BEGIN
575
576 Open cur_rowid;
577 Fetch cur_rowid INTO lv_rowid;
578 IF (cur_rowid%FOUND) THEN
579 Close cur_rowid;
580 Return(TRUE);
581 ELSE
582 Close cur_rowid;
583 Return(FALSE);
584 END IF;
585
586 END Get_PK_For_Validation;
587
588 PROCEDURE GET_FK_IGS_CA_DA (
589 x_dt_alias IN VARCHAR2
590 ) AS
591
592 CURSOR cur_rowid IS
593 SELECT rowid
594 FROM IGS_AD_CAL_CONF
595 WHERE adm_appl_course_strt_dt_alias = x_dt_alias
596 OR adm_appl_final_dt_alias = x_dt_alias
597 OR adm_appl_due_dt_alias = x_dt_alias
598 OR adm_appl_encmb_chk_dt_alias = x_dt_alias
599 OR adm_appl_e_comp_perd_dt_alias = x_dt_alias
600 OR initialise_adm_perd_dt_alias = x_dt_alias
601 OR adm_appl_m_comp_perd_dt_alias = x_dt_alias
602 OR adm_appl_chng_of_pref_dt_alias = x_dt_alias
603 OR adm_appl_offer_resp_dt_alias = x_dt_alias
604 OR adm_appl_short_strt_dt_alias = x_dt_alias
605 OR adm_appl_s_comp_perd_dt_alias = x_dt_alias;
606
607 lv_rowid cur_rowid%RowType;
608
609 BEGIN
610
611 Open cur_rowid;
612 Fetch cur_rowid INTO lv_rowid;
613 IF (cur_rowid%FOUND) THEN
614 Close cur_rowid;
615 Fnd_Message.Set_Name ('IGS', 'IGS_AD_SACCO_DA_DUE_FK');
616 IGS_GE_MSG_STACK.ADD;
617 App_Exception.Raise_Exception;
621
618 Return;
619 END IF;
620 Close cur_rowid;
622 END GET_FK_IGS_CA_DA;
623
624
625
626 PROCEDURE GET_FK_IGS_CA_TYPE (
627 x_cal_type IN VARCHAR2
628 ) AS
629
630 CURSOR cur_rowid IS
631 SELECT rowid
632 FROM IGS_AD_CAL_CONF
633 WHERE inq_cal_type = x_cal_type;
634
635 lv_rowid cur_rowid%RowType;
636
637 BEGIN
638
639 Open cur_rowid;
640 Fetch cur_rowid INTO lv_rowid;
641 IF (cur_rowid%FOUND) THEN
642 Close cur_rowid;
643 Fnd_Message.Set_Name ('IGS', 'IGS_AD_SACCO_CAT_FK');
644 IGS_GE_MSG_STACK.ADD;
645 App_Exception.Raise_Exception;
646 Return;
647 END IF;
648 Close cur_rowid;
649
650 END GET_FK_IGS_CA_TYPE;
651
652 PROCEDURE Before_DML (
653 p_action IN VARCHAR2,
654 x_rowid IN VARCHAR2 DEFAULT NULL,
655 x_s_control_num IN NUMBER DEFAULT NULL,
656 x_initialise_adm_perd_dt_alias IN VARCHAR2 DEFAULT NULL,
657 x_adm_appl_encmb_chk_dt_alias IN VARCHAR2 DEFAULT NULL,
658 x_ad_appl_course_strt_dt_alias IN VARCHAR2 DEFAULT NULL,
659 x_adm_appl_short_strt_dt_alias IN VARCHAR2 DEFAULT NULL,
660 x_adm_appl_due_dt_alias IN VARCHAR2 DEFAULT NULL,
661 x_adm_appl_final_dt_alias IN VARCHAR2 DEFAULT NULL,
662 x_ad_appl_chg_of_pref_dt_alias IN VARCHAR2 DEFAULT NULL,
663 x_adm_appl_offer_resp_dt_alias IN VARCHAR2 DEFAULT NULL,
664 x_ad_appl_e_comp_perd_dt_alias IN VARCHAR2 DEFAULT NULL,
665 x_ad_appl_m_comp_perd_dt_alias IN VARCHAR2 DEFAULT NULL,
666 x_ad_appl_s_comp_perd_dt_alias IN VARCHAR2 DEFAULT NULL,
667 x_creation_date IN DATE DEFAULT NULL,
668 x_created_by IN NUMBER DEFAULT NULL,
669 x_last_update_date IN DATE DEFAULT NULL,
670 x_last_updated_by IN NUMBER DEFAULT NULL,
671 x_last_update_login IN NUMBER DEFAULT NULL,
672 X_ADM_PRC_TRK_DT_ALIAS IN VARCHAR2 DEFAULT NULL,
673 X_POST_ADM_TRK_DT_ALIAS IN VARCHAR2 DEFAULT NULL,
674 X_INQ_CAL_TYPE IN VARCHAR2 DEFAULT NULL
675 ) AS
676 BEGIN
677
678 Set_Column_Values (
679 p_action,
680 x_rowid,
681 x_s_control_num,
682 x_initialise_adm_perd_dt_alias,
683 x_adm_appl_encmb_chk_dt_alias,
684 x_ad_appl_course_strt_dt_alias,
685 x_adm_appl_short_strt_dt_alias,
686 x_adm_appl_due_dt_alias,
687 x_adm_appl_final_dt_alias,
688 x_ad_appl_chg_of_pref_dt_alias,
689 x_adm_appl_offer_resp_dt_alias,
690 x_ad_appl_e_comp_perd_dt_alias,
691 x_ad_appl_m_comp_perd_dt_alias,
692 x_ad_appl_s_comp_perd_dt_alias,
693 x_creation_date,
694 x_created_by,
695 x_last_update_date,
696 x_last_updated_by,
697 x_last_update_login,
698 X_ADM_PRC_TRK_DT_ALIAS,
699 X_POST_ADM_TRK_DT_ALIAS,
700 X_INQ_CAL_TYPE
701 );
702
703 IF (p_action = 'INSERT') THEN
704 BeforeRowInsertUpdate1 ( p_inserting => TRUE );
705 IF Get_PK_For_Validation (
706 new_references.s_control_num
707 ) THEN
708 Fnd_Message.Set_Name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
709 IGS_GE_MSG_STACK.ADD;
710 App_Exception.Raise_Exception;
711 END IF;
712 Check_Constraints;
713 Check_Parent_Existance;
714
715 ELSIF (p_action = 'UPDATE') THEN
716 BeforeRowInsertUpdate1 ( p_updating => TRUE );
717 Check_Constraints;
718 Check_Parent_Existance;
719
720 ELSIF (p_action = 'VALIDATE_INSERT') THEN
721 IF Get_PK_For_Validation (
722 new_references.s_control_num
723 ) THEN
727 END IF;
724 Fnd_Message.Set_Name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
725 IGS_GE_MSG_STACK.ADD;
726 App_Exception.Raise_Exception;
728 Check_Constraints;
729
730 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
731 Check_Constraints;
732 END IF;
733
734 END Before_DML;
735
736 PROCEDURE After_DML (
737 p_action IN VARCHAR2,
738 x_rowid IN VARCHAR2
739 ) AS
740 BEGIN
741
742 l_rowid := x_rowid;
743
744 END After_DML;
745
746 procedure INSERT_ROW (
747 X_ROWID in out NOCOPY VARCHAR2,
748 X_S_CONTROL_NUM in out NOCOPY NUMBER,
749 X_INITIALISE_AD_PERD_DT_ALIAS in VARCHAR2,
750 X_AD_APPL_ENCMB_CHK_DT_ALIAS in VARCHAR2,
751 X_AD_APPL_COURSE_STRT_DT_ALIAS in VARCHAR2,
752 X_AD_APPL_SHORT_STRT_DT_ALIAS in VARCHAR2,
753 X_AD_APPL_DUE_DT_ALIAS in VARCHAR2,
754 X_AD_APPL_FINAL_DT_ALIAS in VARCHAR2,
755 X_AD_APPL_CHG_OF_PREF_DT_ALIAS in VARCHAR2,
756 X_AD_APPL_OFFER_RESP_DT_ALIAS in VARCHAR2,
757 X_AD_APPL_E_COMP_PERD_DT_ALIAS in VARCHAR2,
758 X_AD_APPL_M_COMP_PERD_DT_ALIAS in VARCHAR2,
759 X_AD_APPL_S_COMP_PERD_DT_ALIAS in VARCHAR2,
760 X_MODE in VARCHAR2 default 'R',
761 X_ADM_PRC_TRK_DT_ALIAS IN VARCHAR2 DEFAULT NULL,
762 X_POST_ADM_TRK_DT_ALIAS IN VARCHAR2 DEFAULT NULL,
763 X_INQ_CAL_TYPE IN VARCHAR2 DEFAULT NULL
764 ) AS
765 cursor C is select ROWID from IGS_AD_CAL_CONF
766 where S_CONTROL_NUM = NEW_REFERENCES.S_CONTROL_NUM;
767 X_LAST_UPDATE_DATE DATE;
768 X_LAST_UPDATED_BY NUMBER;
769 X_LAST_UPDATE_LOGIN NUMBER;
770 begin
771 X_LAST_UPDATE_DATE := SYSDATE;
772 if(X_MODE = 'I') then
773 X_LAST_UPDATED_BY := 1;
774 X_LAST_UPDATE_LOGIN := 0;
775 elsif (X_MODE = 'R') then
776 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
777 if X_LAST_UPDATED_BY is NULL then
778 X_LAST_UPDATED_BY := -1;
779 end if;
780 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
781 if X_LAST_UPDATE_LOGIN is NULL then
782 X_LAST_UPDATE_LOGIN := -1;
783 end if;
784 else
785 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
786 IGS_GE_MSG_STACK.ADD;
787 app_exception.raise_exception;
788 end if;
789
790 Before_DML (
791 p_action => 'INSERT',
792 x_rowid => X_ROWID,
793 x_s_control_num => Nvl(X_S_CONTROL_NUM, 1),
794 x_initialise_adm_perd_dt_alias => X_INITIALISE_AD_PERD_DT_ALIAS,
795 x_adm_appl_encmb_chk_dt_alias => X_AD_APPL_ENCMB_CHK_DT_ALIAS,
796 x_ad_appl_course_strt_dt_alias => X_AD_APPL_COURSE_STRT_DT_ALIAS,
797 x_adm_appl_short_strt_dt_alias => X_AD_APPL_SHORT_STRT_DT_ALIAS,
798 x_adm_appl_due_dt_alias => X_AD_APPL_DUE_DT_ALIAS,
799 x_adm_appl_final_dt_alias => X_AD_APPL_FINAL_DT_ALIAS,
800 x_ad_appl_chg_of_pref_dt_alias => X_AD_APPL_CHG_OF_PREF_DT_ALIAS,
801 x_adm_appl_offer_resp_dt_alias => X_AD_APPL_OFFER_RESP_DT_ALIAS,
802 x_ad_appl_e_comp_perd_dt_alias => X_AD_APPL_E_COMP_PERD_DT_ALIAS,
803 x_ad_appl_m_comp_perd_dt_alias => X_AD_APPL_M_COMP_PERD_DT_ALIAS,
804 x_ad_appl_s_comp_perd_dt_alias => X_AD_APPL_S_COMP_PERD_DT_ALIAS,
805 x_creation_date => X_LAST_UPDATE_DATE,
806 x_created_by => X_LAST_UPDATED_BY,
807 x_last_update_date => X_LAST_UPDATE_DATE,
808 x_last_updated_by => X_LAST_UPDATED_BY,
809 x_last_update_login => X_LAST_UPDATE_LOGIN,
810 X_ADM_PRC_TRK_DT_ALIAS => X_ADM_PRC_TRK_DT_ALIAS,
811 X_POST_ADM_TRK_DT_ALIAS => X_POST_ADM_TRK_DT_ALIAS,
812 X_INQ_CAL_TYPE => X_INQ_CAL_TYPE
813 );
814
815 insert into IGS_AD_CAL_CONF (
816 S_CONTROL_NUM,
817 INITIALISE_ADM_PERD_DT_ALIAS,
818 ADM_APPL_ENCMB_CHK_DT_ALIAS,
819 ADM_APPL_COURSE_STRT_DT_ALIAS,
820 ADM_APPL_SHORT_STRT_DT_ALIAS,
821 ADM_APPL_DUE_DT_ALIAS,
822 ADM_APPL_FINAL_DT_ALIAS,
823 ADM_APPL_CHNG_OF_PREF_DT_ALIAS,
824 ADM_APPL_OFFER_RESP_DT_ALIAS,
825 ADM_APPL_E_COMP_PERD_DT_ALIAS,
826 ADM_APPL_M_COMP_PERD_DT_ALIAS,
827 ADM_APPL_S_COMP_PERD_DT_ALIAS,
828 CREATION_DATE,
829 CREATED_BY,
830 LAST_UPDATE_DATE,
831 LAST_UPDATED_BY,
832 LAST_UPDATE_LOGIN,
833 ADM_PRC_TRK_DT_ALIAS,
834 POST_ADM_TRK_DT_ALIAS,
835 INQ_CAL_TYPE
836 ) values (
837 NEW_REFERENCES.S_CONTROL_NUM,
838 NEW_REFERENCES.INITIALISE_ADM_PERD_DT_ALIAS,
839 NEW_REFERENCES.ADM_APPL_ENCMB_CHK_DT_ALIAS,
840 NEW_REFERENCES.ADM_APPL_COURSE_STRT_DT_ALIAS,
841 NEW_REFERENCES.ADM_APPL_SHORT_STRT_DT_ALIAS,
842 NEW_REFERENCES.ADM_APPL_DUE_DT_ALIAS,
843 NEW_REFERENCES.ADM_APPL_FINAL_DT_ALIAS,
844 NEW_REFERENCES.ADM_APPL_CHNG_OF_PREF_DT_ALIAS,
845 NEW_REFERENCES.ADM_APPL_OFFER_RESP_DT_ALIAS,
846 NEW_REFERENCES.ADM_APPL_E_COMP_PERD_DT_ALIAS,
847 NEW_REFERENCES.ADM_APPL_M_COMP_PERD_DT_ALIAS,
848 NEW_REFERENCES.ADM_APPL_S_COMP_PERD_DT_ALIAS,
849 X_LAST_UPDATE_DATE,
850 X_LAST_UPDATED_BY,
851 X_LAST_UPDATE_DATE,
852 X_LAST_UPDATED_BY,
853 X_LAST_UPDATE_LOGIN ,
854 NEW_REFERENCES.ADM_PRC_TRK_DT_ALIAS,
855 NEW_REFERENCES.POST_ADM_TRK_DT_ALIAS,
856 NEW_REFERENCES.INQ_CAL_TYPE
857 );
858
859 open c;
860 fetch c into X_ROWID;
861 if (c%notfound) then
862 close c;
863 raise no_data_found;
864 end if;
865 close c;
866
870
867 After_DML (
868 p_action => 'INSERT',
869 x_rowid => X_ROWID);
871 end INSERT_ROW;
872
873 procedure LOCK_ROW (
874 X_ROWID in VARCHAR2,
875 X_S_CONTROL_NUM in NUMBER,
876 X_INITIALISE_AD_PERD_DT_ALIAS in VARCHAR2,
877 X_AD_APPL_ENCMB_CHK_DT_ALIAS in VARCHAR2,
878 X_AD_APPL_COURSE_STRT_DT_ALIAS in VARCHAR2,
879 X_AD_APPL_SHORT_STRT_DT_ALIAS in VARCHAR2,
880 X_AD_APPL_DUE_DT_ALIAS in VARCHAR2,
881 X_AD_APPL_FINAL_DT_ALIAS in VARCHAR2,
882 X_AD_APPL_CHG_OF_PREF_DT_ALIAS in VARCHAR2,
883 X_AD_APPL_OFFER_RESP_DT_ALIAS in VARCHAR2,
884 X_AD_APPL_E_COMP_PERD_DT_ALIAS in VARCHAR2,
885 X_AD_APPL_M_COMP_PERD_DT_ALIAS in VARCHAR2,
886 X_AD_APPL_S_COMP_PERD_DT_ALIAS in VARCHAR2,
887 X_ADM_PRC_TRK_DT_ALIAS IN VARCHAR2 DEFAULT NULL,
888 X_POST_ADM_TRK_DT_ALIAS IN VARCHAR2 DEFAULT NULL,
889 X_INQ_CAL_TYPE IN VARCHAR2 DEFAULT NULL
890 ) AS
891 cursor c1 is select
892 INITIALISE_ADM_PERD_DT_ALIAS,
893 ADM_APPL_ENCMB_CHK_DT_ALIAS,
894 ADM_APPL_COURSE_STRT_DT_ALIAS,
895 ADM_APPL_SHORT_STRT_DT_ALIAS,
896 ADM_APPL_DUE_DT_ALIAS,
897 ADM_APPL_FINAL_DT_ALIAS,
898 ADM_APPL_CHNG_OF_PREF_DT_ALIAS,
899 ADM_APPL_OFFER_RESP_DT_ALIAS,
900 ADM_APPL_E_COMP_PERD_DT_ALIAS,
901 ADM_APPL_M_COMP_PERD_DT_ALIAS,
902 ADM_APPL_S_COMP_PERD_DT_ALIAS,
903 ADM_PRC_TRK_DT_ALIAS,
904 POST_ADM_TRK_DT_ALIAS,
905 INQ_CAL_TYPE
906 from IGS_AD_CAL_CONF
907 where ROWID = X_ROWID for update nowait;
908 tlinfo c1%rowtype;
909
910 begin
911 open c1;
912 fetch c1 into tlinfo;
913 if (c1%notfound) then
914 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
915 IGS_GE_MSG_STACK.ADD;
916 app_exception.raise_exception;
917 close c1;
918 return;
919 end if;
920 close c1;
921
922 if ( ((tlinfo.INITIALISE_ADM_PERD_DT_ALIAS = X_INITIALISE_AD_PERD_DT_ALIAS)
923 OR ((tlinfo.INITIALISE_ADM_PERD_DT_ALIAS is null)
924 AND (X_INITIALISE_AD_PERD_DT_ALIAS is null)))
925 AND ((tlinfo.ADM_APPL_ENCMB_CHK_DT_ALIAS = X_AD_APPL_ENCMB_CHK_DT_ALIAS)
926 OR ((tlinfo.ADM_APPL_ENCMB_CHK_DT_ALIAS is null)
927 AND (X_AD_APPL_ENCMB_CHK_DT_ALIAS is null)))
928 AND ((tlinfo.ADM_APPL_COURSE_STRT_DT_ALIAS = X_AD_APPL_COURSE_STRT_DT_ALIAS)
929 OR ((tlinfo.ADM_APPL_COURSE_STRT_DT_ALIAS is null)
930 AND (X_AD_APPL_COURSE_STRT_DT_ALIAS is null)))
931 AND ((tlinfo.ADM_APPL_SHORT_STRT_DT_ALIAS = X_AD_APPL_SHORT_STRT_DT_ALIAS)
932 OR ((tlinfo.ADM_APPL_SHORT_STRT_DT_ALIAS is null)
933 AND (X_AD_APPL_SHORT_STRT_DT_ALIAS is null)))
934 AND ((tlinfo.ADM_APPL_DUE_DT_ALIAS = X_AD_APPL_DUE_DT_ALIAS)
935 OR ((tlinfo.ADM_APPL_DUE_DT_ALIAS is null)
936 AND (X_AD_APPL_DUE_DT_ALIAS is null)))
937 AND ((tlinfo.ADM_APPL_FINAL_DT_ALIAS = X_AD_APPL_FINAL_DT_ALIAS)
938 OR ((tlinfo.ADM_APPL_FINAL_DT_ALIAS is null)
939 AND (X_AD_APPL_FINAL_DT_ALIAS is null)))
940 AND ((tlinfo.ADM_APPL_CHNG_OF_PREF_DT_ALIAS = X_AD_APPL_CHG_OF_PREF_DT_ALIAS)
941 OR ((tlinfo.ADM_APPL_CHNG_OF_PREF_DT_ALIAS is null)
942 AND (X_AD_APPL_CHG_OF_PREF_DT_ALIAS is null)))
943 AND ((tlinfo.ADM_APPL_OFFER_RESP_DT_ALIAS = X_AD_APPL_OFFER_RESP_DT_ALIAS)
944 OR ((tlinfo.ADM_APPL_OFFER_RESP_DT_ALIAS is null)
945 AND (X_AD_APPL_OFFER_RESP_DT_ALIAS is null)))
946 AND ((tlinfo.ADM_APPL_E_COMP_PERD_DT_ALIAS = X_AD_APPL_E_COMP_PERD_DT_ALIAS)
947 OR ((tlinfo.ADM_APPL_E_COMP_PERD_DT_ALIAS is null)
948 AND (X_AD_APPL_E_COMP_PERD_DT_ALIAS is null)))
949 AND ((tlinfo.ADM_APPL_M_COMP_PERD_DT_ALIAS = X_AD_APPL_M_COMP_PERD_DT_ALIAS)
950 OR ((tlinfo.ADM_APPL_M_COMP_PERD_DT_ALIAS is null)
951 AND (X_AD_APPL_M_COMP_PERD_DT_ALIAS is null)))
952 AND ((tlinfo.ADM_APPL_S_COMP_PERD_DT_ALIAS = X_AD_APPL_S_COMP_PERD_DT_ALIAS)
953 OR ((tlinfo.ADM_APPL_S_COMP_PERD_DT_ALIAS is null)
954 AND (X_AD_APPL_S_COMP_PERD_DT_ALIAS is null)))
955 AND ((tlinfo.ADM_PRC_TRK_DT_ALIAS = X_ADM_PRC_TRK_DT_ALIAS)
956 OR ((tlinfo.ADM_PRC_TRK_DT_ALIAS is null)
957 AND (X_ADM_PRC_TRK_DT_ALIAS is null)))
958 AND ((tlinfo.POST_ADM_TRK_DT_ALIAS = X_POST_ADM_TRK_DT_ALIAS)
959 OR ((tlinfo.POST_ADM_TRK_DT_ALIAS is null)
960 AND (X_POST_ADM_TRK_DT_ALIAS is null)))
961 AND ((tlinfo.INQ_CAL_TYPE = X_INQ_CAL_TYPE)
962 OR ((tlinfo.INQ_CAL_TYPE is null)
963 AND (X_INQ_CAL_TYPE is null)))
964 ) then
965 null;
966 else
967 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
968 IGS_GE_MSG_STACK.ADD;
969 app_exception.raise_exception;
970 end if;
971 return;
972 end LOCK_ROW;
973
974 procedure UPDATE_ROW (
975 X_ROWID in VARCHAR2,
976 X_S_CONTROL_NUM in NUMBER,
977 X_INITIALISE_AD_PERD_DT_ALIAS in VARCHAR2,
978 X_AD_APPL_ENCMB_CHK_DT_ALIAS in VARCHAR2,
979 X_AD_APPL_COURSE_STRT_DT_ALIAS in VARCHAR2,
980 X_AD_APPL_SHORT_STRT_DT_ALIAS in VARCHAR2,
981 X_AD_APPL_DUE_DT_ALIAS in VARCHAR2,
982 X_AD_APPL_FINAL_DT_ALIAS in VARCHAR2,
983 X_AD_APPL_CHG_OF_PREF_DT_ALIAS in VARCHAR2,
984 X_AD_APPL_OFFER_RESP_DT_ALIAS in VARCHAR2,
985 X_AD_APPL_E_COMP_PERD_DT_ALIAS in VARCHAR2,
989 X_ADM_PRC_TRK_DT_ALIAS IN VARCHAR2 DEFAULT NULL,
986 X_AD_APPL_M_COMP_PERD_DT_ALIAS in VARCHAR2,
987 X_AD_APPL_S_COMP_PERD_DT_ALIAS in VARCHAR2,
988 X_MODE in VARCHAR2 default 'R',
990 X_POST_ADM_TRK_DT_ALIAS IN VARCHAR2 DEFAULT NULL,
991 X_INQ_CAL_TYPE IN VARCHAR2 DEFAULT NULL
992 ) As
993 X_LAST_UPDATE_DATE DATE;
994 X_LAST_UPDATED_BY NUMBER;
995 X_LAST_UPDATE_LOGIN NUMBER;
996 begin
997 X_LAST_UPDATE_DATE := SYSDATE;
998 if(X_MODE = 'I') then
999 X_LAST_UPDATED_BY := 1;
1000 X_LAST_UPDATE_LOGIN := 0;
1001 elsif (X_MODE = 'R') then
1002 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1003 if X_LAST_UPDATED_BY is NULL then
1004 X_LAST_UPDATED_BY := -1;
1005 end if;
1006 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1007 if X_LAST_UPDATE_LOGIN is NULL then
1008 X_LAST_UPDATE_LOGIN := -1;
1009 end if;
1010 else
1011 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
1012 IGS_GE_MSG_STACK.ADD;
1013 app_exception.raise_exception;
1014 end if;
1015
1016 Before_DML (
1017 p_action => 'UPDATE',
1018 x_rowid => X_ROWID,
1019 x_s_control_num => X_S_CONTROL_NUM,
1020 x_initialise_adm_perd_dt_alias => X_INITIALISE_AD_PERD_DT_ALIAS,
1021 x_adm_appl_encmb_chk_dt_alias => X_AD_APPL_ENCMB_CHK_DT_ALIAS,
1022 x_ad_appl_course_strt_dt_alias => X_AD_APPL_COURSE_STRT_DT_ALIAS,
1023 x_adm_appl_short_strt_dt_alias => X_AD_APPL_SHORT_STRT_DT_ALIAS,
1024 x_adm_appl_due_dt_alias => X_AD_APPL_DUE_DT_ALIAS,
1025 x_adm_appl_final_dt_alias => X_AD_APPL_FINAL_DT_ALIAS,
1026 x_ad_appl_chg_of_pref_dt_alias => X_AD_APPL_CHG_OF_PREF_DT_ALIAS,
1027 x_adm_appl_offer_resp_dt_alias => X_AD_APPL_OFFER_RESP_DT_ALIAS,
1028 x_ad_appl_e_comp_perd_dt_alias => X_AD_APPL_E_COMP_PERD_DT_ALIAS,
1029 x_ad_appl_m_comp_perd_dt_alias => X_AD_APPL_M_COMP_PERD_DT_ALIAS,
1030 x_ad_appl_s_comp_perd_dt_alias => X_AD_APPL_S_COMP_PERD_DT_ALIAS,
1031 x_creation_date => X_LAST_UPDATE_DATE,
1032 x_created_by => X_LAST_UPDATED_BY,
1033 x_last_update_date => X_LAST_UPDATE_DATE,
1034 x_last_updated_by => X_LAST_UPDATED_BY,
1035 x_last_update_login => X_LAST_UPDATE_LOGIN,
1036 X_ADM_PRC_TRK_DT_ALIAS => X_ADM_PRC_TRK_DT_ALIAS,
1037 X_POST_ADM_TRK_DT_ALIAS => X_POST_ADM_TRK_DT_ALIAS,
1038 X_INQ_CAL_TYPE => X_INQ_CAL_TYPE
1039 );
1040
1041 update IGS_AD_CAL_CONF set
1042 INITIALISE_ADM_PERD_DT_ALIAS = NEW_REFERENCES.INITIALISE_ADM_PERD_DT_ALIAS,
1043 ADM_APPL_ENCMB_CHK_DT_ALIAS = NEW_REFERENCES.ADM_APPL_ENCMB_CHK_DT_ALIAS,
1044 ADM_APPL_COURSE_STRT_DT_ALIAS = NEW_REFERENCES.ADM_APPL_COURSE_STRT_DT_ALIAS,
1045 ADM_APPL_SHORT_STRT_DT_ALIAS = NEW_REFERENCES.ADM_APPL_SHORT_STRT_DT_ALIAS,
1046 ADM_APPL_DUE_DT_ALIAS = NEW_REFERENCES.ADM_APPL_DUE_DT_ALIAS,
1047 ADM_APPL_FINAL_DT_ALIAS = NEW_REFERENCES.ADM_APPL_FINAL_DT_ALIAS,
1051 ADM_APPL_M_COMP_PERD_DT_ALIAS = NEW_REFERENCES.ADM_APPL_M_COMP_PERD_DT_ALIAS,
1048 ADM_APPL_CHNG_OF_PREF_DT_ALIAS = NEW_REFERENCES.ADM_APPL_CHNG_OF_PREF_DT_ALIAS,
1049 ADM_APPL_OFFER_RESP_DT_ALIAS = NEW_REFERENCES.ADM_APPL_OFFER_RESP_DT_ALIAS,
1050 ADM_APPL_E_COMP_PERD_DT_ALIAS = NEW_REFERENCES.ADM_APPL_E_COMP_PERD_DT_ALIAS,
1052 ADM_APPL_S_COMP_PERD_DT_ALIAS = NEW_REFERENCES.ADM_APPL_S_COMP_PERD_DT_ALIAS,
1053 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1054 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1055 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1056 ADM_PRC_TRK_DT_ALIAS = NEW_REFERENCES.ADM_PRC_TRK_DT_ALIAS,
1057 POST_ADM_TRK_DT_ALIAS = NEW_REFERENCES.POST_ADM_TRK_DT_ALIAS,
1058 INQ_CAL_TYPE = NEW_REFERENCES.INQ_CAL_TYPE
1059 where ROWID = X_ROWID;
1060
1061 if (sql%notfound) then
1062 raise no_data_found;
1063 end if;
1064
1065 After_DML (
1066 p_action => 'UPDATE',
1067 x_rowid => X_ROWID);
1068
1069 end UPDATE_ROW;
1070
1071 procedure ADD_ROW (
1072 X_ROWID in out NOCOPY VARCHAR2,
1073 X_S_CONTROL_NUM in out NOCOPY NUMBER,
1074 X_INITIALISE_AD_PERD_DT_ALIAS in VARCHAR2,
1075 X_AD_APPL_ENCMB_CHK_DT_ALIAS in VARCHAR2,
1076 X_AD_APPL_COURSE_STRT_DT_ALIAS in VARCHAR2,
1077 X_AD_APPL_SHORT_STRT_DT_ALIAS in VARCHAR2,
1078 X_AD_APPL_DUE_DT_ALIAS in VARCHAR2,
1079 X_AD_APPL_FINAL_DT_ALIAS in VARCHAR2,
1080 X_AD_APPL_CHG_OF_PREF_DT_ALIAS in VARCHAR2,
1081 X_AD_APPL_OFFER_RESP_DT_ALIAS in VARCHAR2,
1082 X_AD_APPL_E_COMP_PERD_DT_ALIAS in VARCHAR2,
1083 X_AD_APPL_M_COMP_PERD_DT_ALIAS in VARCHAR2,
1084 X_AD_APPL_S_COMP_PERD_DT_ALIAS in VARCHAR2,
1085 X_MODE in VARCHAR2 default 'R' ,
1086 X_ADM_PRC_TRK_DT_ALIAS IN VARCHAR2 DEFAULT NULL,
1087 X_POST_ADM_TRK_DT_ALIAS IN VARCHAR2 DEFAULT NULL,
1088 X_INQ_CAL_TYPE IN VARCHAR2 DEFAULT NULL
1089 ) AS
1090 cursor c1 is select rowid from IGS_AD_CAL_CONF
1091 where S_CONTROL_NUM = NVL(X_S_CONTROL_NUM,1);
1092
1093 begin
1094 open c1;
1095 fetch c1 into X_ROWID;
1096 if (c1%notfound) then
1097 close c1;
1098 INSERT_ROW (
1099 X_ROWID,
1100 X_S_CONTROL_NUM,
1101 X_INITIALISE_AD_PERD_DT_ALIAS,
1102 X_AD_APPL_ENCMB_CHK_DT_ALIAS,
1103 X_AD_APPL_COURSE_STRT_DT_ALIAS,
1104 X_AD_APPL_SHORT_STRT_DT_ALIAS,
1105 X_AD_APPL_DUE_DT_ALIAS,
1106 X_AD_APPL_FINAL_DT_ALIAS,
1107 X_AD_APPL_CHG_OF_PREF_DT_ALIAS,
1108 X_AD_APPL_OFFER_RESP_DT_ALIAS,
1109 X_AD_APPL_E_COMP_PERD_DT_ALIAS,
1110 X_AD_APPL_M_COMP_PERD_DT_ALIAS,
1111 X_AD_APPL_S_COMP_PERD_DT_ALIAS,
1112 X_MODE,
1113 X_ADM_PRC_TRK_DT_ALIAS ,
1114 X_POST_ADM_TRK_DT_ALIAS,
1115 X_INQ_CAL_TYPE
1116 );
1117 return;
1118 end if;
1119 close c1;
1120 UPDATE_ROW (
1121 X_ROWID,
1122 X_S_CONTROL_NUM,
1123 X_INITIALISE_AD_PERD_DT_ALIAS,
1124 X_AD_APPL_ENCMB_CHK_DT_ALIAS,
1125 X_AD_APPL_COURSE_STRT_DT_ALIAS,
1126 X_AD_APPL_SHORT_STRT_DT_ALIAS,
1127 X_AD_APPL_DUE_DT_ALIAS,
1128 X_AD_APPL_FINAL_DT_ALIAS,
1129 X_AD_APPL_CHG_OF_PREF_DT_ALIAS,
1130 X_AD_APPL_OFFER_RESP_DT_ALIAS,
1131 X_AD_APPL_E_COMP_PERD_DT_ALIAS,
1132 X_AD_APPL_M_COMP_PERD_DT_ALIAS,
1133 X_AD_APPL_S_COMP_PERD_DT_ALIAS,
1134 X_MODE,
1135 X_ADM_PRC_TRK_DT_ALIAS ,
1136 X_POST_ADM_TRK_DT_ALIAS,
1137 X_INQ_CAL_TYPE
1138 );
1139 end ADD_ROW;
1140
1141 procedure DELETE_ROW (
1142 X_ROWID in VARCHAR2
1143 ) AS
1144 begin
1145 Before_DML (
1146 p_action => 'DELETE',
1147 x_rowid => X_ROWID);
1148
1149 delete from IGS_AD_CAL_CONF
1150 where ROWID = X_ROWID;
1151 if (sql%notfound) then
1152 raise no_data_found;
1153 end if;
1154
1155 After_DML (
1156 p_action => 'DELETE',
1157 x_rowid => X_ROWID);
1158
1159 end DELETE_ROW;
1160
1161 end IGS_AD_CAL_CONF_PKG;