[Home] [Help]
PACKAGE BODY: APPS.IGS_EN_CAL_CONF_PKG
Source
1 package body IGS_EN_CAL_CONF_PKG AS
2 /* $Header: IGSEI26B.pls 120.1 2005/06/15 01:43:28 appldev $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_EN_CAL_CONF%RowType;
6 new_references IGS_EN_CAL_CONF%RowType;
7
8
9 PROCEDURE Set_Column_Values (
10 p_action IN VARCHAR2,
11 x_rowid IN VARCHAR2 DEFAULT NULL,
12 x_s_control_num IN NUMBER DEFAULT NULL,
13 x_commence_cutoff_dt_alias IN VARCHAR2 DEFAULT NULL,
14 x_commencement_dt_alias IN VARCHAR2 DEFAULT NULL,
15 x_effect_enr_strt_dt_alias IN VARCHAR2 DEFAULT NULL,
16 x_record_open_dt_alias IN VARCHAR2 DEFAULT NULL,
17 x_record_cutoff_dt_alias IN VARCHAR2 DEFAULT NULL,
18 x_sub_unit_dt_alias IN VARCHAR2 DEFAULT NULL,
19 x_variation_cutoff_dt_alias IN VARCHAR2 DEFAULT NULL,
20 x_enr_form_due_dt_alias IN VARCHAR2 DEFAULT NULL,
21 x_enr_pckg_prod_dt_alias IN VARCHAR2 DEFAULT NULL,
22 x_load_effect_dt_alias IN VARCHAR2 DEFAULT NULL,
23 x_enr_rule_cutoff_dt_alias IN VARCHAR2 DEFAULT NULL,
24 x_invalid_rule_cutoff_dt_alias IN VARCHAR2 DEFAULT NULL,
25 x_enr_cleanup_dt_alias IN VARCHAR2 DEFAULT NULL,
26 x_lapse_dt_alias IN VARCHAR2 DEFAULT NULL,
27 x_creation_date IN DATE DEFAULT NULL,
28 x_created_by IN NUMBER DEFAULT NULL,
29 x_last_update_date IN DATE DEFAULT NULL,
30 x_last_updated_by IN NUMBER DEFAULT NULL,
31 x_last_update_login IN NUMBER DEFAULT NULL,
32 x_grading_schema_dt_alias IN VARCHAR2 DEFAULT NULL,
33 x_begin_trans_dt_alias IN VARCHAR2 DEFAULT NULL,
34 x_clean_trans_dt_alias IN VARCHAR2 DEFAULT NULL,
35 x_planning_open_dt_alias IN VARCHAR2 DEFAULT NULL,
36 x_schedule_open_dt_alias IN VARCHAR2 ,
37 x_audit_status_dt_alias IN VARCHAR2 DEFAULT NULL
38 ) AS
39
40 CURSOR cur_old_ref_values IS
41 SELECT *
42 FROM IGS_EN_CAL_CONF
43 WHERE rowid = x_rowid;
44
45 BEGIN
46
47 l_rowid := x_rowid;
48
49 -- Code for setting the Old and New Reference Values.
50 -- Populate Old Values.
51 Open cur_old_ref_values;
52 Fetch cur_old_ref_values INTO old_references;
53 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN( 'INSERT','VALIDATE_INSERT' )) THEN
54 Close cur_old_ref_values;
55 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
56 IGS_GE_MSG_STACK.ADD;
57 App_Exception.Raise_Exception;
58 Return;
59 END IF;
60 Close cur_old_ref_values;
61
62 -- Populate New Values.
63 new_references.s_control_num := x_s_control_num;
64 new_references.commence_cutoff_dt_alias := x_commence_cutoff_dt_alias;
65 new_references.commencement_dt_alias := x_commencement_dt_alias;
66 new_references.effect_enr_strt_dt_alias := x_effect_enr_strt_dt_alias;
67 new_references.record_open_dt_alias := x_record_open_dt_alias;
68 new_references.record_cutoff_dt_alias := x_record_cutoff_dt_alias;
69 new_references.sub_unit_dt_alias := x_sub_unit_dt_alias;
70 new_references.variation_cutoff_dt_alias := x_variation_cutoff_dt_alias;
71 new_references.enr_form_due_dt_alias := x_enr_form_due_dt_alias;
72 new_references.enr_pckg_prod_dt_alias := x_enr_pckg_prod_dt_alias;
73 new_references.load_effect_dt_alias := x_load_effect_dt_alias;
74 new_references.enrolled_rule_cutoff_dt_alias := x_enr_rule_cutoff_dt_alias;
75 new_references.invalid_rule_cutoff_dt_alias := x_invalid_rule_cutoff_dt_alias;
76 new_references.enr_cleanup_dt_alias := x_enr_cleanup_dt_alias;
77 new_references.lapse_dt_alias := x_lapse_dt_alias;
78 new_references.grading_schema_dt_alias := x_grading_schema_dt_alias;
79 new_references.begin_trans_dt_alias := x_begin_trans_dt_alias;
80 new_references.clean_trans_dt_alias := x_clean_trans_dt_alias;
81 new_references.planning_open_dt_alias := x_planning_open_dt_alias;
82 new_references.schedule_open_dt_alias := x_schedule_open_dt_alias;
83 new_references.audit_status_dt_alias := x_audit_status_dt_alias;
84
85 IF (p_action = 'UPDATE') THEN
86 new_references.creation_date := old_references.creation_date;
87 new_references.created_by := old_references.created_by;
88 ELSE
89 new_references.creation_date := x_creation_date;
90 new_references.created_by := x_created_by;
91 END IF;
92 new_references.last_update_date := x_last_update_date;
93 new_references.last_updated_by := x_last_updated_by;
94 new_references.last_update_login := x_last_update_login;
95
96 END Set_Column_Values;
97
98
99
100
101 procedure Check_constraints(
102 column_name IN VARCHAR2 DEFAULT NULL,
103 column_value IN VARCHAR2 DEFAULT NULL
104 ) AS
105 begin
106 IF column_name is null then
107 NULL;
108 ELSIF upper(column_name) = 'S_CONTROL_NUM' THEN
109 new_references.s_control_num := igs_ge_number.to_num(TO_NUMBER(column_value));
110 ELSIF upper(column_name) = 'COMMENCEMENT_DT_ALIAS' THEN
111 new_references.commencement_dt_alias := column_value;
112 ELSIF upper(column_name) = 'COMMENCE_CUTOFF_DT_ALIAS' THEN
113 new_references.commence_cutoff_dt_alias := column_value;
114 ELSIF upper(column_name) = 'EFFECT_ENR_STRT_DT_ALIAS' THEN
115 new_references.effect_enr_strt_dt_alias := column_value;
116 ELSIF upper(column_name) = 'ENROLLED_RULE_CUTOFF_DT_ALIAS' THEN
117 new_references.enrolled_rule_cutoff_dt_alias := column_value;
118 ELSIF upper(column_name) = 'ENR_CLEANUP_DT_ALIAS' THEN
119 new_references.enr_cleanup_dt_alias := column_value;
120 ELSIF upper(column_name) = 'ENR_FORM_DUE_DT_ALIAS' THEN
121 new_references.enr_form_due_dt_alias := column_value;
122 ELSIF upper(column_name) = 'ENR_PCKG_PROD_DT_ALIAS' THEN
123 new_references.enr_pckg_prod_dt_alias := column_value;
124 ELSIF upper(column_name) = 'INVALID_RULE_CUTOFF_DT_ALIAS' THEN
125 new_references.invalid_rule_cutoff_dt_alias := column_value;
126 ELSIF upper(column_name) = 'LAPSE_DT_ALIAS' THEN
127 new_references.lapse_dt_alias := column_value;
128 ELSIF upper(column_name) = 'LOAD_EFFECT_DT_ALIAS' THEN
129 new_references.load_effect_dt_alias := column_value;
130 ELSIF upper(column_name) = 'RECORD_CUTOFF_DT_ALIAS' THEN
131 new_references.record_cutoff_dt_alias := column_value;
132 ELSIF upper(column_name) = 'RECORD_OPEN_DT_ALIAS' THEN
133 new_references.record_open_dt_alias := column_value;
134 ELSIF upper(column_name) = 'SUB_UNIT_DT_ALIAS' THEN
135 new_references.sub_unit_dt_alias := column_value;
136 ELSIF upper(column_name) = 'VARIATION_CUTOFF_DT_ALIAS' THEN
137 new_references.variation_cutoff_dt_alias := column_value;
138 END IF;
139
140 IF upper(column_name) = 'S_CONTROL_NUM' OR
141 Column_name is null THEN
142 IF new_references.s_control_num NOT IN ( 1 ) THEN
143 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
144 IGS_GE_MSG_STACK.ADD;
145 App_Exception.Raise_Exception;
146 END IF;
147 END IF;
148
149 IF upper(column_name) = 'COMMENCEMENT_DT_ALIAS' OR
150 Column_name is null THEN
151 IF new_references.commencement_dt_alias <>
152 upper(new_references.commencement_dt_alias) THEN
153 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
154 IGS_GE_MSG_STACK.ADD;
155 App_Exception.Raise_Exception;
156 END IF;
157 END IF;
158
159 IF upper(column_name) = 'COMMENCE_CUTOFF_DT_ALIAS' OR
160 Column_name is null THEN
161 IF new_references.commence_cutoff_dt_alias <>
162 upper(new_references.commence_cutoff_dt_alias) THEN
163 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
164 IGS_GE_MSG_STACK.ADD;
165 App_Exception.Raise_Exception;
166 END IF;
167 END IF;
168
169 IF upper(column_name) = 'EFFECT_ENR_STRT_DT_ALIAS' OR
170 Column_name is null THEN
171 IF new_references.effect_enr_strt_dt_alias <>
172 upper(new_references.effect_enr_strt_dt_alias) THEN
173 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
174 IGS_GE_MSG_STACK.ADD;
175 App_Exception.Raise_Exception;
176 END IF;
177 END IF;
178
179 IF upper(column_name) = 'ENROLLED_RULE_CUTOFF_DT_ALIAS' OR
180 Column_name is null THEN
181 IF new_references.enrolled_rule_cutoff_dt_alias <>
182 upper(new_references.enrolled_rule_cutoff_dt_alias) THEN
183 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
184 IGS_GE_MSG_STACK.ADD;
185 App_Exception.Raise_Exception;
186 END IF;
187 END IF;
188 IF upper(column_name) = 'ENR_CLEANUP_DT_ALIAS' OR
189 Column_name is null THEN
190 IF new_references.enr_cleanup_dt_alias <>
191 upper(new_references.enr_cleanup_dt_alias) THEN
192 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
193 IGS_GE_MSG_STACK.ADD;
194 App_Exception.Raise_Exception;
195 END IF;
196 END IF;
197
198 IF upper(column_name) = 'ENR_FORM_DUE_DT_ALIAS' OR
199 Column_name is null THEN
200 IF new_references.enr_form_due_dt_alias <>
201 upper(new_references.enr_form_due_dt_alias) THEN
202 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
203 IGS_GE_MSG_STACK.ADD;
204 App_Exception.Raise_Exception;
205 END IF;
206 END IF;
207
208 IF upper(column_name) = 'ENR_PCKG_PROD_DT_ALIAS' OR
209 Column_name is null THEN
210 IF new_references.enr_pckg_prod_dt_alias <>
211 upper(new_references.enr_pckg_prod_dt_alias) THEN
212 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
213 IGS_GE_MSG_STACK.ADD;
214 App_Exception.Raise_Exception;
215 END IF;
216 END IF;
217
218 IF upper(column_name) = 'INVALID_RULE_CUTOFF_DT_ALIAS' OR
219 Column_name is null THEN
220 IF new_references.invalid_rule_cutoff_dt_alias <>
221 upper(new_references.invalid_rule_cutoff_dt_alias) THEN
222 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
223 IGS_GE_MSG_STACK.ADD;
224 App_Exception.Raise_Exception;
225 END IF;
226 END IF;
227
228 IF upper(column_name) = 'LAPSE_DT_ALIAS' OR
229 Column_name is null THEN
230 IF new_references.lapse_dt_alias <>
231 upper(new_references.lapse_dt_alias) THEN
232 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
233 IGS_GE_MSG_STACK.ADD;
234 App_Exception.Raise_Exception;
235 END IF;
236 END IF;
237
238 IF upper(column_name) = 'LOAD_EFFECT_DT_ALIAS' OR
239 Column_name is null THEN
240 IF new_references.load_effect_dt_alias <>
241 upper(new_references.load_effect_dt_alias) THEN
242 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
243 IGS_GE_MSG_STACK.ADD;
244 App_Exception.Raise_Exception;
245 END IF;
246 END IF;
247
248 IF upper(column_name) = 'RECORD_CUTOFF_DT_ALIAS' OR
249 Column_name is null THEN
250 IF new_references.record_cutoff_dt_alias <>
251 upper(new_references.record_cutoff_dt_alias) THEN
252 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
253 IGS_GE_MSG_STACK.ADD;
254 App_Exception.Raise_Exception;
255 END IF;
256 END IF;
257
258 IF upper(column_name) = 'RECORD_OPEN_DT_ALIAS' OR
259 Column_name is null THEN
260 IF new_references.record_open_dt_alias <>
261 upper(new_references.record_open_dt_alias) THEN
262 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
263 IGS_GE_MSG_STACK.ADD;
264 App_Exception.Raise_Exception;
265 END IF;
266 END IF;
267
268 IF upper(column_name) = 'SUB_UNIT_DT_ALIAS' OR
269 Column_name is null THEN
270 IF new_references.sub_unit_dt_alias <>
271 upper(new_references.sub_unit_dt_alias) THEN
272 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
273 IGS_GE_MSG_STACK.ADD;
274 App_Exception.Raise_Exception;
275 END IF;
276 END IF;
277
278 IF upper(column_name) = 'VARIATION_CUTOFF_DT_ALIAS' OR
279 Column_name is null THEN
280 IF new_references.variation_cutoff_dt_alias <>
281 upper(new_references.variation_cutoff_dt_alias) THEN
282 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
283 IGS_GE_MSG_STACK.ADD;
284 App_Exception.Raise_Exception;
285 END IF;
286 END IF;
287
288 IF upper(column_name) = 'BEGIN_TRANS_DT_ALIAS' OR
289 Column_name is null THEN
290 IF new_references.begin_trans_dt_alias <>
291 upper(new_references.begin_trans_dt_alias) THEN
292 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
293 IGS_GE_MSG_STACK.ADD;
294 App_Exception.Raise_Exception;
295 END IF;
296 END IF;
297
298 IF upper(column_name) = 'CLEAN_TRANS_DT_ALIAS' OR
299 Column_name is null THEN
300 IF new_references.clean_trans_dt_alias <>
301 upper(new_references.clean_trans_dt_alias) THEN
302 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
303 IGS_GE_MSG_STACK.ADD;
304 App_Exception.Raise_Exception;
305 END IF;
306 END IF;
307
308 IF upper(column_name) = 'PLANNING_OPEN_DT_ALIAS' OR
309 Column_name is null THEN
310 IF new_references.planning_open_dt_alias <>
311 upper(new_references.planning_open_dt_alias) THEN
312 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
313 IGS_GE_MSG_STACK.ADD;
314 App_Exception.Raise_Exception;
315 END IF;
316 END IF;
317
318 IF upper(column_name) = 'SCHEDULE_OPEN_DT_ALIAS' OR
319 Column_name is null THEN
323 IGS_GE_MSG_STACK.ADD;
320 IF new_references.schedule_open_dt_alias <>
321 upper(new_references.schedule_open_dt_alias) THEN
322 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
324 App_Exception.Raise_Exception;
325 END IF;
326 END IF;
327
328 IF upper(column_name) = 'AUDIT_STATUS_DT_ALIAS' OR
329 Column_name is null THEN
330 IF new_references.audit_status_dt_alias <>
331 upper(new_references.audit_status_dt_alias) THEN
332 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
333 IGS_GE_MSG_STACK.ADD;
334 App_Exception.Raise_Exception;
335 END IF;
336 END IF;
337
338
339 END check_constraints;
340
341 PROCEDURE Check_Parent_Existance AS
342 BEGIN
343
344 IF (((old_references.enr_pckg_prod_dt_alias = new_references.enr_pckg_prod_dt_alias)) OR
345 ((new_references.enr_pckg_prod_dt_alias IS NULL))) THEN
346 NULL;
347 ELSE
348 if not IGS_CA_DA_PKG.Get_PK_For_Validation (
349 new_references.enr_pckg_prod_dt_alias
350 ) then
351 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
352 IGS_GE_MSG_STACK.ADD;
353 App_Exception.Raise_Exception;
354 end if;
355 END IF;
356
357 IF (((old_references.record_cutoff_dt_alias = new_references.record_cutoff_dt_alias)) OR
358 ((new_references.record_cutoff_dt_alias IS NULL))) THEN
359 NULL;
360 ELSE
361 if not IGS_CA_DA_PKG.Get_PK_For_Validation (
362 new_references.record_cutoff_dt_alias
363 ) then
364 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
365 IGS_GE_MSG_STACK.ADD;
366 App_Exception.Raise_Exception;
367 end if;
368 END IF;
369
370 IF (((old_references.record_open_dt_alias = new_references.record_open_dt_alias)) OR
371 ((new_references.record_open_dt_alias IS NULL))) THEN
372 NULL;
373 ELSE
374 IF NOT IGS_CA_DA_PKG.Get_PK_For_Validation (
375 new_references.record_open_dt_alias
376 ) then
377 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
378 IGS_GE_MSG_STACK.ADD;
379 App_Exception.Raise_Exception;
380 end if;
381 END IF;
382
383 IF (((old_references.sub_unit_dt_alias = new_references.sub_unit_dt_alias)) OR
384 ((new_references.sub_unit_dt_alias IS NULL))) THEN
385 NULL;
386 ELSE
387 IF NOT IGS_CA_DA_PKG.Get_PK_For_Validation (
388 new_references.sub_unit_dt_alias
389 ) then
390 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
391 IGS_GE_MSG_STACK.ADD;
392 App_Exception.Raise_Exception;
393 end if;
394 END IF;
395
396 IF (((old_references.variation_cutoff_dt_alias = new_references.variation_cutoff_dt_alias)) OR
397 ((new_references.variation_cutoff_dt_alias IS NULL))) THEN
398 NULL;
399 ELSE
400 IF NOT IGS_CA_DA_PKG.Get_PK_For_Validation (
401 new_references.variation_cutoff_dt_alias
402 ) then
403 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
404 IGS_GE_MSG_STACK.ADD;
405 App_Exception.Raise_Exception;
406 end if;
407 END IF;
408
409 IF (((old_references.commencement_dt_alias = new_references.commencement_dt_alias)) OR
410 ((new_references.commencement_dt_alias IS NULL))) THEN
411 NULL;
412 ELSE
413 IF NOT IGS_CA_DA_PKG.Get_PK_For_Validation (
414 new_references.commencement_dt_alias
415 ) then
416 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
417 IGS_GE_MSG_STACK.ADD;
418 App_Exception.Raise_Exception;
419 end if;
420 END IF;
421
422 IF (((old_references.commence_cutoff_dt_alias = new_references.commence_cutoff_dt_alias)) OR
423 ((new_references.commence_cutoff_dt_alias IS NULL))) THEN
424 NULL;
425 ELSE
426 IF NOT IGS_CA_DA_PKG.Get_PK_For_Validation (
427 new_references.commence_cutoff_dt_alias
428 ) then
429 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
430 IGS_GE_MSG_STACK.ADD;
431 App_Exception.Raise_Exception;
432 end if;
433 END IF;
434
435 IF (((old_references.enr_form_due_dt_alias = new_references.enr_form_due_dt_alias)) OR
436 ((new_references.enr_form_due_dt_alias IS NULL))) THEN
437 NULL;
438 ELSE
439 IF NOT IGS_CA_DA_PKG.Get_PK_For_Validation (
440 new_references.enr_form_due_dt_alias
441 ) then
442 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
443 IGS_GE_MSG_STACK.ADD;
444 App_Exception.Raise_Exception;
445 end if;
446 END IF;
447
448 IF (((old_references.effect_enr_strt_dt_alias = new_references.effect_enr_strt_dt_alias)) OR
449 ((new_references.effect_enr_strt_dt_alias IS NULL))) THEN
450 NULL;
451 ELSE
452 IF NOT IGS_CA_DA_PKG.Get_PK_For_Validation (
453 new_references.effect_enr_strt_dt_alias
454 ) then
455 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
456 IGS_GE_MSG_STACK.ADD;
460
457 App_Exception.Raise_Exception;
458 end if;
459 END IF;
461 IF (((old_references.enrolled_rule_cutoff_dt_alias = new_references.enrolled_rule_cutoff_dt_alias)) OR
462 ((new_references.enrolled_rule_cutoff_dt_alias IS NULL))) THEN
463 NULL;
464 ELSE
465 IF NOT IGS_CA_DA_PKG.Get_PK_For_Validation (
466 new_references.enrolled_rule_cutoff_dt_alias
467 ) then
468 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
469 IGS_GE_MSG_STACK.ADD;
470 App_Exception.Raise_Exception;
471 end if;
472 END IF;
473
474 IF (((old_references.invalid_rule_cutoff_dt_alias = new_references.invalid_rule_cutoff_dt_alias)) OR
475 ((new_references.invalid_rule_cutoff_dt_alias IS NULL))) THEN
476 NULL;
477 ELSE
478 IF NOT IGS_CA_DA_PKG.Get_PK_For_Validation (
479 new_references.invalid_rule_cutoff_dt_alias
480 ) then
481 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
482 IGS_GE_MSG_STACK.ADD;
483 App_Exception.Raise_Exception;
484 end if;
485 END IF;
486
487 IF (((old_references.lapse_dt_alias = new_references.lapse_dt_alias)) OR
488 ((new_references.lapse_dt_alias IS NULL))) THEN
489 NULL;
490 ELSE
491 IF NOT IGS_CA_DA_PKG.Get_PK_For_Validation (
492 new_references.lapse_dt_alias
493 ) then
494 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
495 IGS_GE_MSG_STACK.ADD;
496 App_Exception.Raise_Exception;
497 end if;
498 END IF;
499
500 IF (((old_references.load_effect_dt_alias = new_references.load_effect_dt_alias)) OR
501 ((new_references.load_effect_dt_alias IS NULL))) THEN
502 NULL;
503 ELSE
504 IF NOT IGS_CA_DA_PKG.Get_PK_For_Validation (
505 new_references.load_effect_dt_alias
506 ) then
507 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
508 IGS_GE_MSG_STACK.ADD;
509 App_Exception.Raise_Exception;
510 end if;
511 END IF;
512
513 IF (((old_references.enr_cleanup_dt_alias = new_references.enr_cleanup_dt_alias)) OR
514 ((new_references.enr_cleanup_dt_alias IS NULL))) THEN
515 NULL;
516 ELSE
517 IF NOT IGS_CA_DA_PKG.Get_PK_For_Validation (
518 new_references.enr_cleanup_dt_alias
519 ) then
520 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
521 IGS_GE_MSG_STACK.ADD;
522 App_Exception.Raise_Exception;
523 end if;
524 END IF;
525
526 IF (((old_references.begin_trans_dt_alias = new_references.begin_trans_dt_alias)) OR
527 ((new_references.begin_trans_dt_alias IS NULL))) THEN
528 NULL;
529 ELSE
530 IF NOT IGS_CA_DA_PKG.Get_PK_For_Validation (
531 new_references.begin_trans_dt_alias
532 ) then
533 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
534 IGS_GE_MSG_STACK.ADD;
535 App_Exception.Raise_Exception;
536 end if;
537 END IF;
538
539 IF (((old_references.clean_trans_dt_alias = new_references.clean_trans_dt_alias)) OR
540 ((new_references.clean_trans_dt_alias IS NULL))) THEN
541 NULL;
542 ELSE
543 IF NOT IGS_CA_DA_PKG.Get_PK_For_Validation (
544 new_references.clean_trans_dt_alias
545 ) then
546 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
547 IGS_GE_MSG_STACK.ADD;
548 App_Exception.Raise_Exception;
549 end if;
550 END IF;
551
552 IF (((old_references.planning_open_dt_alias = new_references.planning_open_dt_alias)) OR
553 ((new_references.planning_open_dt_alias IS NULL))) THEN
554 NULL;
555 ELSE
556 IF NOT IGS_CA_DA_PKG.Get_PK_For_Validation (
557 new_references.planning_open_dt_alias
558 ) then
559 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
560 IGS_GE_MSG_STACK.ADD;
561 App_Exception.Raise_Exception;
562 end if;
563 END IF;
564
565 IF (((old_references.schedule_open_dt_alias = new_references.schedule_open_dt_alias)) OR
566 ((new_references.schedule_open_dt_alias IS NULL))) THEN
567 NULL;
568 ELSE
569 IF NOT IGS_CA_DA_PKG.Get_PK_For_Validation (
570 new_references.schedule_open_dt_alias
571 ) then
572 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
573 IGS_GE_MSG_STACK.ADD;
574 App_Exception.Raise_Exception;
575 end if;
576 END IF;
577
578 IF (((old_references.audit_status_dt_alias = new_references.audit_status_dt_alias)) OR
579 ((new_references.audit_status_dt_alias IS NULL))) THEN
580 NULL;
581 ELSE
582 IF NOT IGS_CA_DA_PKG.Get_PK_For_Validation (
583 new_references.audit_status_dt_alias
584 ) then
585 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
586 IGS_GE_MSG_STACK.ADD;
587 App_Exception.Raise_Exception;
588 end if;
589 END IF;
590
591
592 END Check_Parent_Existance;
593
594 FUNCTION Get_PK_For_Validation (
595 x_s_control_num IN NUMBER
596 )RETURN BOOLEAN AS
597
598 CURSOR cur_rowid IS
599 SELECT rowid
600 FROM IGS_EN_CAL_CONF
604 lv_rowid cur_rowid%RowType;
601 WHERE s_control_num = x_s_control_num
602 FOR UPDATE NOWAIT;
603
605
606 BEGIN
607
608 Open cur_rowid;
609 Fetch cur_rowid INTO lv_rowid;
610 IF (cur_rowid%FOUND) THEN
611 Close cur_rowid;
612 return(TRUE);
613 else
614 Close cur_rowid;
615 Return(FALSE);
616 END IF;
617
618 END Get_PK_For_Validation;
619
620 PROCEDURE GET_FK_IGS_CA_DA (
621 x_dt_alias IN VARCHAR2
622 ) AS
623
624 CURSOR cur_rowid IS
625 SELECT rowid
626 FROM IGS_EN_CAL_CONF
627 WHERE commence_cutoff_dt_alias = x_dt_alias
628 OR commencement_dt_alias = x_dt_alias
629 OR effect_enr_strt_dt_alias = x_dt_alias
630 OR enr_cleanup_dt_alias = x_dt_alias
631 OR enr_form_due_dt_alias = x_dt_alias
632 OR enr_pckg_prod_dt_alias = x_dt_alias
633 OR enrolled_rule_cutoff_dt_alias = x_dt_alias
634 OR invalid_rule_cutoff_dt_alias = x_dt_alias
635 OR lapse_dt_alias = x_dt_alias
636 OR load_effect_dt_alias = x_dt_alias
637 OR record_cutoff_dt_alias = x_dt_alias
638 OR record_open_dt_alias = x_dt_alias
639 OR sub_unit_dt_alias = x_dt_alias
640 OR variation_cutoff_dt_alias = x_dt_alias
641 OR begin_trans_dt_alias = x_dt_alias
642 OR clean_trans_dt_alias = x_dt_alias
643 OR planning_open_dt_alias = x_dt_alias
644 OR schedule_open_dt_alias = x_dt_alias
645 OR audit_status_dt_alias = x_dt_alias;
646
647 lv_rowid cur_rowid%RowType;
648
649 BEGIN
650
651 Open cur_rowid;
652 Fetch cur_rowid INTO lv_rowid;
653 IF (cur_rowid%FOUND) THEN
654 Close cur_rowid;
655 Fnd_Message.Set_Name ('IGS', 'IGS_EN_SECC_DA_FK');
656 IGS_GE_MSG_STACK.ADD;
657 App_Exception.Raise_Exception;
658 Return;
659 END IF;
660 Close cur_rowid;
661
662 END GET_FK_IGS_CA_DA;
663
664 PROCEDURE Before_DML (
665 p_action IN VARCHAR2,
666 x_rowid IN VARCHAR2 DEFAULT NULL,
667 x_s_control_num IN NUMBER DEFAULT NULL,
668 x_commence_cutoff_dt_alias IN VARCHAR2 DEFAULT NULL,
669 x_commencement_dt_alias IN VARCHAR2 DEFAULT NULL,
670 x_effect_enr_strt_dt_alias IN VARCHAR2 DEFAULT NULL,
671 x_record_open_dt_alias IN VARCHAR2 DEFAULT NULL,
672 x_record_cutoff_dt_alias IN VARCHAR2 DEFAULT NULL,
673 x_sub_unit_dt_alias IN VARCHAR2 DEFAULT NULL,
674 x_variation_cutoff_dt_alias IN VARCHAR2 DEFAULT NULL,
675 x_enr_form_due_dt_alias IN VARCHAR2 DEFAULT NULL,
676 x_enr_pckg_prod_dt_alias IN VARCHAR2 DEFAULT NULL,
677 x_load_effect_dt_alias IN VARCHAR2 DEFAULT NULL,
678 x_enr_rule_cutoff_dt_alias IN VARCHAR2 DEFAULT NULL,
679 x_invalid_rule_cutoff_dt_alias IN VARCHAR2 DEFAULT NULL,
680 x_enr_cleanup_dt_alias IN VARCHAR2 DEFAULT NULL,
681 x_lapse_dt_alias IN VARCHAR2 DEFAULT NULL,
682 x_creation_date IN DATE DEFAULT NULL,
683 x_created_by IN NUMBER DEFAULT NULL,
684 x_last_update_date IN DATE DEFAULT NULL,
685 x_last_updated_by IN NUMBER DEFAULT NULL,
686 x_last_update_login IN NUMBER DEFAULT NULL,
687 x_grading_schema_dt_alias IN VARCHAR2 DEFAULT NULL,
688 x_begin_trans_dt_alias IN VARCHAR2 DEFAULT NULL,
689 x_clean_trans_dt_alias IN VARCHAR2 DEFAULT NULL,
690 x_planning_open_dt_alias IN VARCHAR2 DEFAULT NULL,
691 x_schedule_open_dt_alias IN VARCHAR2 DEFAULT NULL ,
692 x_audit_status_dt_alias IN VARCHAR2 DEFAULT NULL
693 ) AS
694 BEGIN
695
696 Set_Column_Values (
697 p_action,
698 x_rowid,
699 x_s_control_num,
700 x_commence_cutoff_dt_alias,
701 x_commencement_dt_alias,
702 x_effect_enr_strt_dt_alias,
703 x_record_open_dt_alias,
704 x_record_cutoff_dt_alias,
705 x_sub_unit_dt_alias,
706 x_variation_cutoff_dt_alias,
707 x_enr_form_due_dt_alias,
708 x_enr_pckg_prod_dt_alias,
709 x_load_effect_dt_alias,
710 x_enr_rule_cutoff_dt_alias,
711 x_invalid_rule_cutoff_dt_alias,
712 x_enr_cleanup_dt_alias,
713 x_lapse_dt_alias,
714 x_creation_date,
715 x_created_by,
716 x_last_update_date,
717 x_last_updated_by,
718 x_last_update_login,
719 x_grading_schema_dt_alias,
720 x_begin_trans_dt_alias,
721 x_clean_trans_dt_alias,
722 x_planning_open_dt_alias ,
723 x_schedule_open_dt_alias ,
724 x_audit_status_dt_alias
725 );
726
727 IF (p_action = 'INSERT') THEN
728 -- Call all the procedures related to Before Insert.
729 Null;
730 if Get_PK_For_Validation (
731 new_references.s_control_num
732 ) then
733 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
734 IGS_GE_MSG_STACK.ADD;
735 App_Exception.Raise_Exception;
736 end if;
737 Check_constraints;
738 Check_Parent_Existance;
739 ELSIF (p_action = 'UPDATE') THEN
740 -- Call all the procedures related to Before Update.
741 Null;
742 Check_constraints;
743 Check_Parent_Existance;
744 ELSIF (p_action = 'DELETE') THEN
745 -- Call all the procedures related to Before Delete.
746 Null;
750 ) then
747 ELSIF (p_action = 'VALIDATE_INSERT') then
748 if Get_PK_For_Validation (
749 new_references.s_control_num
751 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
752 IGS_GE_MSG_STACK.ADD;
753 App_Exception.Raise_Exception;
754 end if;
755 Check_constraints;
756 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
757 Check_constraints;
758 ELSIF (p_action = 'VALIDATE_DELETE') THEN
759 null;
760 END IF;
761
762 END Before_DML;
763
764 PROCEDURE After_DML (
765 p_action IN VARCHAR2,
766 x_rowid IN VARCHAR2
767 ) AS
768 BEGIN
769
770 l_rowid := x_rowid;
771
772 IF (p_action = 'INSERT') THEN
773 -- Call all the procedures related to After Insert.
774 Null;
775 ELSIF (p_action = 'UPDATE') THEN
776 -- Call all the procedures related to After Update.
777 Null;
778 ELSIF (p_action = 'DELETE') THEN
779 -- Call all the procedures related to After Delete.
780 Null;
781 END IF;
782
783 END After_DML;
784
785
786 procedure INSERT_ROW (
787 X_ROWID in out NOCOPY VARCHAR2,
788 X_S_CONTROL_NUM in out NOCOPY NUMBER,
789 X_COMMENCE_CUTOFF_DT_ALIAS in VARCHAR2,
790 X_COMMENCEMENT_DT_ALIAS in VARCHAR2,
791 X_EFFECT_ENR_STRT_DT_ALIAS in VARCHAR2,
792 X_RECORD_OPEN_DT_ALIAS in VARCHAR2,
793 X_RECORD_CUTOFF_DT_ALIAS in VARCHAR2,
794 X_SUB_UNIT_DT_ALIAS in VARCHAR2,
795 X_VARIATION_CUTOFF_DT_ALIAS in VARCHAR2,
796 X_ENR_FORM_DUE_DT_ALIAS in VARCHAR2,
797 X_ENR_PCKG_PROD_DT_ALIAS in VARCHAR2,
798 X_LOAD_EFFECT_DT_ALIAS in VARCHAR2,
799 X_ENR_RULE_CUTOFF_DT_ALIAS in VARCHAR2,
800 X_INVALID_RULE_CUTOFF_DT_ALIAS in VARCHAR2,
801 X_LAPSE_DT_ALIAS in VARCHAR2,
802 X_ENR_CLEANUP_DT_ALIAS in VARCHAR2,
803 X_MODE in VARCHAR2 default 'R',
804 x_grading_schema_dt_alias IN VARCHAR2,
805 x_begin_trans_dt_alias IN VARCHAR2,
806 x_clean_trans_dt_alias IN VARCHAR2,
807 x_planning_open_dt_alias IN VARCHAR2 ,
808 x_schedule_open_dt_alias IN VARCHAR2 ,
809 x_audit_status_dt_alias IN VARCHAR2
810
811 ) AS
812 cursor C is select ROWID from IGS_EN_CAL_CONF
813 where S_CONTROL_NUM = NEW_REFERENCES.S_CONTROL_NUM;
814 X_LAST_UPDATE_DATE DATE;
815 X_LAST_UPDATED_BY NUMBER;
816 X_LAST_UPDATE_LOGIN NUMBER;
817 begin
818 X_LAST_UPDATE_DATE := SYSDATE;
819 if(X_MODE = 'I') then
820 X_LAST_UPDATED_BY := 1;
821 X_LAST_UPDATE_LOGIN := 0;
822 elsif (X_MODE = 'R') then
823 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
824 if X_LAST_UPDATED_BY is NULL then
825 X_LAST_UPDATED_BY := -1;
826 end if;
827 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
828 if X_LAST_UPDATE_LOGIN is NULL then
829 X_LAST_UPDATE_LOGIN := -1;
830 end if;
831 else
832 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
833 IGS_GE_MSG_STACK.ADD;
834 app_exception.raise_exception;
835 end if;
836
837 Before_DML (
838 p_action => 'INSERT' ,
839 x_rowid => x_rowid ,
840 x_s_control_num => NVL(x_s_control_num,1) ,
841 x_commence_cutoff_dt_alias => x_commence_cutoff_dt_alias ,
842 x_commencement_dt_alias => x_commencement_dt_alias ,
843 x_effect_enr_strt_dt_alias => x_effect_enr_strt_dt_alias ,
844 x_record_open_dt_alias => x_record_open_dt_alias ,
845 x_record_cutoff_dt_alias => x_record_cutoff_dt_alias ,
846 x_sub_unit_dt_alias => x_sub_unit_dt_alias ,
847 x_variation_cutoff_dt_alias => x_variation_cutoff_dt_alias ,
848 x_enr_form_due_dt_alias => x_enr_form_due_dt_alias ,
849 x_enr_pckg_prod_dt_alias => x_enr_pckg_prod_dt_alias ,
850 x_load_effect_dt_alias => x_load_effect_dt_alias ,
851 x_enr_rule_cutoff_dt_alias => x_enr_rule_cutoff_dt_alias ,
852 x_invalid_rule_cutoff_dt_alias => x_invalid_rule_cutoff_dt_alias ,
853 x_enr_cleanup_dt_alias => x_enr_cleanup_dt_alias ,
854 x_lapse_dt_alias => x_lapse_dt_alias ,
855 x_creation_date => x_last_update_date ,
856 x_created_by => x_last_updated_by ,
857 x_last_update_date => x_last_update_date ,
858 x_last_updated_by => x_last_updated_by ,
859 x_last_update_login => x_last_update_login,
860 x_grading_schema_dt_alias => x_grading_schema_dt_alias,
861 x_begin_trans_dt_alias => x_begin_trans_dt_alias,
862 x_clean_trans_dt_alias => x_clean_trans_dt_alias,
863 x_planning_open_dt_alias =>x_planning_open_dt_alias,
864 x_schedule_open_dt_alias => x_schedule_open_dt_alias,
865 x_audit_status_dt_alias => x_audit_status_dt_alias
866 );
867
868
869 insert into IGS_EN_CAL_CONF (
870 S_CONTROL_NUM,
871 COMMENCE_CUTOFF_DT_ALIAS,
872 COMMENCEMENT_DT_ALIAS,
873 EFFECT_ENR_STRT_DT_ALIAS,
874 RECORD_OPEN_DT_ALIAS,
875 RECORD_CUTOFF_DT_ALIAS,
876 SUB_UNIT_DT_ALIAS,
877 VARIATION_CUTOFF_DT_ALIAS,
878 ENR_FORM_DUE_DT_ALIAS,
879 ENR_PCKG_PROD_DT_ALIAS,
880 LOAD_EFFECT_DT_ALIAS,
881 ENROLLED_RULE_CUTOFF_DT_ALIAS,
882 INVALID_RULE_CUTOFF_DT_ALIAS,
883 LAPSE_DT_ALIAS,
884 ENR_CLEANUP_DT_ALIAS,
885 CREATION_DATE,
886 CREATED_BY,
887 LAST_UPDATE_DATE,
888 LAST_UPDATED_BY,
889 LAST_UPDATE_LOGIN,
893 planning_open_dt_alias,
890 grading_schema_dt_alias,
891 begin_trans_dt_alias,
892 clean_trans_dt_alias,
894 schedule_open_dt_alias,
895 audit_status_dt_alias
896 ) values (
897 NEW_REFERENCES.S_CONTROL_NUM,
898 NEW_REFERENCES.COMMENCE_CUTOFF_DT_ALIAS,
899 NEW_REFERENCES.COMMENCEMENT_DT_ALIAS,
900 NEW_REFERENCES.EFFECT_ENR_STRT_DT_ALIAS,
901 NEW_REFERENCES.RECORD_OPEN_DT_ALIAS,
902 NEW_REFERENCES.RECORD_CUTOFF_DT_ALIAS,
903 NEW_REFERENCES.SUB_UNIT_DT_ALIAS,
904 NEW_REFERENCES.VARIATION_CUTOFF_DT_ALIAS,
905 NEW_REFERENCES.ENR_FORM_DUE_DT_ALIAS,
906 NEW_REFERENCES.ENR_PCKG_PROD_DT_ALIAS,
907 NEW_REFERENCES.LOAD_EFFECT_DT_ALIAS,
908 NEW_REFERENCES.ENROLLED_RULE_CUTOFF_DT_ALIAS,
909 NEW_REFERENCES.INVALID_RULE_CUTOFF_DT_ALIAS,
910 NEW_REFERENCES.LAPSE_DT_ALIAS,
911 NEW_REFERENCES.ENR_CLEANUP_DT_ALIAS,
912 X_LAST_UPDATE_DATE,
913 X_LAST_UPDATED_BY,
914 X_LAST_UPDATE_DATE,
915 X_LAST_UPDATED_BY,
916 X_LAST_UPDATE_LOGIN,
917 x_grading_schema_dt_alias,
918 x_begin_trans_dt_alias,
919 x_clean_trans_dt_alias,
920 x_planning_open_dt_alias,
921 x_schedule_open_dt_alias,
922 x_audit_status_dt_alias
923 );
924
925 open c;
926 fetch c into X_ROWID;
927 if (c%notfound) then
928 close c;
929 raise no_data_found;
930 end if;
931 close c;
932
933 After_DML(
934 p_action => 'INSERT',
935 x_rowid => X_ROWID
936 );
937 end INSERT_ROW;
938
939 procedure LOCK_ROW (
940 X_ROWID IN VARCHAR2,
941 X_S_CONTROL_NUM in NUMBER,
942 X_COMMENCE_CUTOFF_DT_ALIAS in VARCHAR2,
943 X_COMMENCEMENT_DT_ALIAS in VARCHAR2,
944 X_EFFECT_ENR_STRT_DT_ALIAS in VARCHAR2,
945 X_RECORD_OPEN_DT_ALIAS in VARCHAR2,
946 X_RECORD_CUTOFF_DT_ALIAS in VARCHAR2,
947 X_SUB_UNIT_DT_ALIAS in VARCHAR2,
948 X_VARIATION_CUTOFF_DT_ALIAS in VARCHAR2,
949 X_ENR_FORM_DUE_DT_ALIAS in VARCHAR2,
950 X_ENR_PCKG_PROD_DT_ALIAS in VARCHAR2,
951 X_LOAD_EFFECT_DT_ALIAS in VARCHAR2,
952 X_ENR_RULE_CUTOFF_DT_ALIAS in VARCHAR2,
953 X_INVALID_RULE_CUTOFF_DT_ALIAS in VARCHAR2,
954 X_LAPSE_DT_ALIAS in VARCHAR2,
955 X_ENR_CLEANUP_DT_ALIAS in VARCHAR2,
956 x_grading_schema_dt_alias IN VARCHAR2 ,
957 x_begin_trans_dt_alias IN VARCHAR2,
958 x_clean_trans_dt_alias IN VARCHAR2,
959 x_planning_open_dt_alias IN VARCHAR2 DEFAULT NULL,
960 x_schedule_open_dt_alias IN VARCHAR2 ,
961 x_audit_status_dt_alias IN VARCHAR2 DEFAULT NULL
962 ) AS
963 cursor c1 is select
964 COMMENCE_CUTOFF_DT_ALIAS,
965 COMMENCEMENT_DT_ALIAS,
966 EFFECT_ENR_STRT_DT_ALIAS,
967 RECORD_OPEN_DT_ALIAS,
968 RECORD_CUTOFF_DT_ALIAS,
969 SUB_UNIT_DT_ALIAS,
970 VARIATION_CUTOFF_DT_ALIAS,
971 ENR_FORM_DUE_DT_ALIAS,
972 ENR_PCKG_PROD_DT_ALIAS,
973 LOAD_EFFECT_DT_ALIAS,
974 ENROLLED_RULE_CUTOFF_DT_ALIAS,
975 INVALID_RULE_CUTOFF_DT_ALIAS,
976 LAPSE_DT_ALIAS,
977 ENR_CLEANUP_DT_ALIAS,
978 grading_schema_dt_alias ,
979 begin_trans_dt_alias,
980 clean_trans_dt_alias,
981 planning_open_dt_alias,
982 schedule_open_dt_alias,
983 audit_status_dt_alias
984 from IGS_EN_CAL_CONF
985 where ROWID = X_ROWID
986 for update nowait;
987 tlinfo c1%rowtype;
988
989 begin
990 open c1;
991 fetch c1 into tlinfo;
992 if (c1%notfound) then
993 close c1;
994 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
995 IGS_GE_MSG_STACK.ADD;
996 app_exception.raise_exception;
997 return;
998 end if;
999 close c1;
1000
1001 if ( (tlinfo.COMMENCE_CUTOFF_DT_ALIAS = X_COMMENCE_CUTOFF_DT_ALIAS)
1002 AND ((tlinfo.COMMENCEMENT_DT_ALIAS = X_COMMENCEMENT_DT_ALIAS)
1003 OR ((tlinfo.COMMENCEMENT_DT_ALIAS is null)
1004 AND (X_COMMENCEMENT_DT_ALIAS is null)))
1005 AND (tlinfo.EFFECT_ENR_STRT_DT_ALIAS = X_EFFECT_ENR_STRT_DT_ALIAS)
1006 AND ((tlinfo.RECORD_OPEN_DT_ALIAS = X_RECORD_OPEN_DT_ALIAS)
1007 OR ((tlinfo.RECORD_OPEN_DT_ALIAS is null)
1008 AND (X_RECORD_OPEN_DT_ALIAS is null)))
1009 AND ((tlinfo.RECORD_CUTOFF_DT_ALIAS = X_RECORD_CUTOFF_DT_ALIAS)
1010 OR ((tlinfo.RECORD_CUTOFF_DT_ALIAS is null)
1011 AND (X_RECORD_CUTOFF_DT_ALIAS is null)))
1012 AND ((tlinfo.SUB_UNIT_DT_ALIAS = X_SUB_UNIT_DT_ALIAS)
1013 OR ((tlinfo.SUB_UNIT_DT_ALIAS is null)
1014 AND (X_SUB_UNIT_DT_ALIAS is null)))
1015 AND ((tlinfo.VARIATION_CUTOFF_DT_ALIAS = X_VARIATION_CUTOFF_DT_ALIAS)
1016 OR ((tlinfo.VARIATION_CUTOFF_DT_ALIAS is null)
1017 AND (X_VARIATION_CUTOFF_DT_ALIAS is null)))
1018 AND ((tlinfo.ENR_FORM_DUE_DT_ALIAS = X_ENR_FORM_DUE_DT_ALIAS)
1019 OR ((tlinfo.ENR_FORM_DUE_DT_ALIAS is null)
1020 AND (X_ENR_FORM_DUE_DT_ALIAS is null)))
1021 AND ((tlinfo.ENR_PCKG_PROD_DT_ALIAS = X_ENR_PCKG_PROD_DT_ALIAS)
1022 OR ((tlinfo.ENR_PCKG_PROD_DT_ALIAS is null)
1023 AND (X_ENR_PCKG_PROD_DT_ALIAS is null)))
1024 AND (tlinfo.LOAD_EFFECT_DT_ALIAS = X_LOAD_EFFECT_DT_ALIAS)
1025 AND ((tlinfo.ENROLLED_RULE_CUTOFF_DT_ALIAS = X_ENR_RULE_CUTOFF_DT_ALIAS)
1029 OR ((tlinfo.INVALID_RULE_CUTOFF_DT_ALIAS is null)
1026 OR ((tlinfo.ENROLLED_RULE_CUTOFF_DT_ALIAS is null)
1027 AND (X_ENR_RULE_CUTOFF_DT_ALIAS is null)))
1028 AND ((tlinfo.INVALID_RULE_CUTOFF_DT_ALIAS = X_INVALID_RULE_CUTOFF_DT_ALIAS)
1030 AND (X_INVALID_RULE_CUTOFF_DT_ALIAS is null)))
1031 AND (tlinfo.LAPSE_DT_ALIAS = X_LAPSE_DT_ALIAS)
1032 AND ((tlinfo.ENR_CLEANUP_DT_ALIAS = X_ENR_CLEANUP_DT_ALIAS)
1033 OR ((tlinfo.ENR_CLEANUP_DT_ALIAS is null)
1034 AND (X_ENR_CLEANUP_DT_ALIAS is null)))
1035 AND ((tlinfo.grading_schema_dt_alias = X_grading_schema_dt_alias)
1036 OR ((tlinfo.grading_schema_dt_alias is null)
1037 AND (X_grading_schema_dt_alias is null)))
1038 AND ((tlinfo.begin_trans_dt_alias = x_begin_trans_dt_alias)
1039 OR ((tlinfo.begin_trans_dt_alias is null)
1040 AND (x_begin_trans_dt_alias is null)))
1041 AND ((tlinfo.clean_trans_dt_alias = x_clean_trans_dt_alias)
1042 OR ((tlinfo.clean_trans_dt_alias is null)
1043 AND (x_clean_trans_dt_alias is null)))
1044
1045 AND ((tlinfo.planning_open_dt_alias = x_planning_open_dt_alias)
1046 OR ((tlinfo.planning_open_dt_alias is null)
1047 AND (x_planning_open_dt_alias is null)))
1048 AND ((tlinfo.schedule_open_dt_alias = x_schedule_open_dt_alias)
1049 OR ((tlinfo.schedule_open_dt_alias is null)
1050 AND (x_schedule_open_dt_alias is null)))
1051 AND ((tlinfo.audit_status_dt_alias = x_audit_status_dt_alias)
1052 OR ((tlinfo.audit_status_dt_alias is null)
1053 AND (x_audit_status_dt_alias is null)))
1054
1055 ) then
1056 null;
1057 else
1058 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1059 IGS_GE_MSG_STACK.ADD;
1060 app_exception.raise_exception;
1061 end if;
1062 return;
1063 end LOCK_ROW;
1064
1065 procedure UPDATE_ROW (
1066 X_ROWID IN VARCHAR2,
1067 X_S_CONTROL_NUM in NUMBER,
1068 X_COMMENCE_CUTOFF_DT_ALIAS in VARCHAR2,
1069 X_COMMENCEMENT_DT_ALIAS in VARCHAR2,
1070 X_EFFECT_ENR_STRT_DT_ALIAS in VARCHAR2,
1071 X_RECORD_OPEN_DT_ALIAS in VARCHAR2,
1072 X_RECORD_CUTOFF_DT_ALIAS in VARCHAR2,
1073 X_SUB_UNIT_DT_ALIAS in VARCHAR2,
1074 X_VARIATION_CUTOFF_DT_ALIAS in VARCHAR2,
1075 X_ENR_FORM_DUE_DT_ALIAS in VARCHAR2,
1076 X_ENR_PCKG_PROD_DT_ALIAS in VARCHAR2,
1077 X_LOAD_EFFECT_DT_ALIAS in VARCHAR2,
1078 X_ENR_RULE_CUTOFF_DT_ALIAS in VARCHAR2,
1079 X_INVALID_RULE_CUTOFF_DT_ALIAS in VARCHAR2,
1080 X_LAPSE_DT_ALIAS in VARCHAR2,
1081 X_ENR_CLEANUP_DT_ALIAS in VARCHAR2,
1082 X_MODE in VARCHAR2 default 'R',
1083 x_grading_schema_dt_alias IN VARCHAR2 ,
1084 x_begin_trans_dt_alias IN VARCHAR2,
1085 x_clean_trans_dt_alias IN VARCHAR2,
1086 x_planning_open_dt_alias IN VARCHAR2 ,
1087 x_schedule_open_dt_alias IN VARCHAR2 ,
1088 x_audit_status_dt_alias IN VARCHAR2
1089 ) AS
1090 X_LAST_UPDATE_DATE DATE;
1091 X_LAST_UPDATED_BY NUMBER;
1092 X_LAST_UPDATE_LOGIN NUMBER;
1093 begin
1094 X_LAST_UPDATE_DATE := SYSDATE;
1095 if(X_MODE = 'I') then
1096 X_LAST_UPDATED_BY := 1;
1097 X_LAST_UPDATE_LOGIN := 0;
1098 elsif (X_MODE = 'R') then
1099 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1100 if X_LAST_UPDATED_BY is NULL then
1101 X_LAST_UPDATED_BY := -1;
1102 end if;
1103 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1104 if X_LAST_UPDATE_LOGIN is NULL then
1105 X_LAST_UPDATE_LOGIN := -1;
1106 end if;
1107 else
1108 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
1109 IGS_GE_MSG_STACK.ADD;
1110 app_exception.raise_exception;
1111 end if;
1112
1113
1114 Before_DML (
1115 p_action => 'UPDATE' ,
1116 x_rowid => x_rowid ,
1117 x_s_control_num => x_s_control_num ,
1118 x_commence_cutoff_dt_alias => x_commence_cutoff_dt_alias ,
1119 x_commencement_dt_alias => x_commencement_dt_alias ,
1120 x_effect_enr_strt_dt_alias => x_effect_enr_strt_dt_alias ,
1121 x_record_open_dt_alias => x_record_open_dt_alias ,
1122 x_record_cutoff_dt_alias => x_record_cutoff_dt_alias ,
1123 x_sub_unit_dt_alias => x_sub_unit_dt_alias ,
1124 x_variation_cutoff_dt_alias => x_variation_cutoff_dt_alias ,
1125 x_enr_form_due_dt_alias => x_enr_form_due_dt_alias ,
1126 x_enr_pckg_prod_dt_alias => x_enr_pckg_prod_dt_alias ,
1127 x_load_effect_dt_alias => x_load_effect_dt_alias ,
1128 x_enr_rule_cutoff_dt_alias => x_enr_rule_cutoff_dt_alias ,
1129 x_invalid_rule_cutoff_dt_alias => x_invalid_rule_cutoff_dt_alias ,
1130 x_enr_cleanup_dt_alias => x_enr_cleanup_dt_alias ,
1131 x_lapse_dt_alias => x_lapse_dt_alias ,
1132 x_creation_date => x_last_update_date ,
1133 x_created_by => x_last_updated_by ,
1134 x_last_update_date => x_last_update_date ,
1135 x_last_updated_by => x_last_updated_by ,
1136 x_last_update_login => x_last_update_login ,
1137 x_grading_schema_dt_alias => x_grading_schema_dt_alias,
1138 x_begin_trans_dt_alias => x_begin_trans_dt_alias,
1139 x_clean_trans_dt_alias => x_clean_trans_dt_alias,
1140 x_planning_open_dt_alias =>x_planning_open_dt_alias,
1141 x_schedule_open_dt_alias => x_schedule_open_dt_alias,
1142 x_audit_status_dt_alias => x_audit_status_dt_alias
1143 );
1144
1145
1146 update IGS_EN_CAL_CONF set
1147 COMMENCE_CUTOFF_DT_ALIAS = NEW_REFERENCES.COMMENCE_CUTOFF_DT_ALIAS,
1148 COMMENCEMENT_DT_ALIAS = NEW_REFERENCES.COMMENCEMENT_DT_ALIAS,
1149 EFFECT_ENR_STRT_DT_ALIAS = NEW_REFERENCES.EFFECT_ENR_STRT_DT_ALIAS,
1150 RECORD_OPEN_DT_ALIAS = NEW_REFERENCES.RECORD_OPEN_DT_ALIAS,
1151 RECORD_CUTOFF_DT_ALIAS = NEW_REFERENCES.RECORD_CUTOFF_DT_ALIAS,
1152 SUB_UNIT_DT_ALIAS = NEW_REFERENCES.SUB_UNIT_DT_ALIAS,
1153 VARIATION_CUTOFF_DT_ALIAS = NEW_REFERENCES.VARIATION_CUTOFF_DT_ALIAS,
1154 ENR_FORM_DUE_DT_ALIAS = NEW_REFERENCES.ENR_FORM_DUE_DT_ALIAS,
1155 ENR_PCKG_PROD_DT_ALIAS = NEW_REFERENCES.ENR_PCKG_PROD_DT_ALIAS,
1156 LOAD_EFFECT_DT_ALIAS = NEW_REFERENCES.LOAD_EFFECT_DT_ALIAS,
1157 ENROLLED_RULE_CUTOFF_DT_ALIAS = NEW_REFERENCES.ENROLLED_RULE_CUTOFF_DT_ALIAS,
1158 INVALID_RULE_CUTOFF_DT_ALIAS = NEW_REFERENCES.INVALID_RULE_CUTOFF_DT_ALIAS,
1159 LAPSE_DT_ALIAS = NEW_REFERENCES.LAPSE_DT_ALIAS,
1160 ENR_CLEANUP_DT_ALIAS = NEW_REFERENCES.ENR_CLEANUP_DT_ALIAS,
1161 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1162 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1163 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1164 grading_schema_dt_alias = x_grading_schema_dt_alias ,
1165 begin_trans_dt_alias = x_begin_trans_dt_alias,
1166 clean_trans_dt_alias = x_clean_trans_dt_alias,
1167 planning_open_dt_alias=x_planning_open_dt_alias,
1168 schedule_open_dt_alias=x_schedule_open_dt_alias,
1169 audit_status_dt_alias=x_audit_status_dt_alias
1170 where ROWID = X_ROWID
1171 ;
1172 if (sql%notfound) then
1173 raise no_data_found;
1174 end if;
1175
1176
1177 After_DML(
1178 p_action => 'UPDATE',
1179 x_rowid => X_ROWID
1180 );
1181 end UPDATE_ROW;
1182
1183 procedure ADD_ROW (
1184 X_ROWID in out NOCOPY VARCHAR2,
1185 X_S_CONTROL_NUM in out NOCOPY NUMBER,
1186 X_COMMENCE_CUTOFF_DT_ALIAS in VARCHAR2,
1187 X_COMMENCEMENT_DT_ALIAS in VARCHAR2,
1188 X_EFFECT_ENR_STRT_DT_ALIAS in VARCHAR2,
1189 X_RECORD_OPEN_DT_ALIAS in VARCHAR2,
1190 X_RECORD_CUTOFF_DT_ALIAS in VARCHAR2,
1191 X_SUB_UNIT_DT_ALIAS in VARCHAR2,
1192 X_VARIATION_CUTOFF_DT_ALIAS in VARCHAR2,
1193 X_ENR_FORM_DUE_DT_ALIAS in VARCHAR2,
1194 X_ENR_PCKG_PROD_DT_ALIAS in VARCHAR2,
1195 X_LOAD_EFFECT_DT_ALIAS in VARCHAR2,
1196 X_ENR_RULE_CUTOFF_DT_ALIAS in VARCHAR2,
1197 X_INVALID_RULE_CUTOFF_DT_ALIAS in VARCHAR2,
1198 X_LAPSE_DT_ALIAS in VARCHAR2,
1199 X_ENR_CLEANUP_DT_ALIAS in VARCHAR2,
1200 X_MODE in VARCHAR2 default 'R',
1201 x_grading_schema_dt_alias IN VARCHAR2 ,
1202 x_begin_trans_dt_alias IN VARCHAR2,
1203 x_clean_trans_dt_alias IN VARCHAR2,
1204 x_planning_open_dt_alias IN VARCHAR2 DEFAULT NULL,
1205 x_schedule_open_dt_alias IN VARCHAR2 ,
1206 x_audit_status_dt_alias IN VARCHAR2 DEFAULT NULL
1207 ) AS
1208 cursor c1 is select rowid from IGS_EN_CAL_CONF
1209 where S_CONTROL_NUM = nvl(X_S_CONTROL_NUM,1)
1210 ;
1211
1212 begin
1213 open c1;
1214 fetch c1 into X_ROWID;
1215 if (c1%notfound) then
1216 close c1;
1217 INSERT_ROW (
1218 X_ROWID,
1219 X_S_CONTROL_NUM,
1220 X_COMMENCE_CUTOFF_DT_ALIAS,
1221 X_COMMENCEMENT_DT_ALIAS,
1222 X_EFFECT_ENR_STRT_DT_ALIAS,
1223 X_RECORD_OPEN_DT_ALIAS,
1224 X_RECORD_CUTOFF_DT_ALIAS,
1225 X_SUB_UNIT_DT_ALIAS,
1226 X_VARIATION_CUTOFF_DT_ALIAS,
1227 X_ENR_FORM_DUE_DT_ALIAS,
1228 X_ENR_PCKG_PROD_DT_ALIAS,
1229 X_LOAD_EFFECT_DT_ALIAS,
1230 X_ENR_RULE_CUTOFF_DT_ALIAS,
1231 X_INVALID_RULE_CUTOFF_DT_ALIAS,
1232 X_LAPSE_DT_ALIAS,
1233 X_ENR_CLEANUP_DT_ALIAS,
1234 X_MODE,
1235 x_grading_schema_dt_alias,
1236 x_begin_trans_dt_alias,
1237 x_clean_trans_dt_alias,
1238 x_planning_open_dt_alias ,
1239 x_schedule_open_dt_alias ,
1240 x_audit_status_dt_alias
1241 );
1242 return;
1243 end if;
1244 close c1;
1245 UPDATE_ROW (
1246 X_ROWID,
1247 X_S_CONTROL_NUM,
1248 X_COMMENCE_CUTOFF_DT_ALIAS,
1249 X_COMMENCEMENT_DT_ALIAS,
1250 X_EFFECT_ENR_STRT_DT_ALIAS,
1251 X_RECORD_OPEN_DT_ALIAS,
1252 X_RECORD_CUTOFF_DT_ALIAS,
1253 X_SUB_UNIT_DT_ALIAS,
1254 X_VARIATION_CUTOFF_DT_ALIAS,
1255 X_ENR_FORM_DUE_DT_ALIAS,
1256 X_ENR_PCKG_PROD_DT_ALIAS,
1257 X_LOAD_EFFECT_DT_ALIAS,
1258 X_ENR_RULE_CUTOFF_DT_ALIAS,
1259 X_INVALID_RULE_CUTOFF_DT_ALIAS,
1260 X_LAPSE_DT_ALIAS,
1261 X_ENR_CLEANUP_DT_ALIAS,
1262 X_MODE,
1263 x_grading_schema_dt_alias,
1264 x_begin_trans_dt_alias,
1265 x_clean_trans_dt_alias,
1266 x_planning_open_dt_alias ,
1267 x_schedule_open_dt_alias ,
1268 x_audit_status_dt_alias
1269 );
1270
1271 end ADD_ROW;
1272
1273 procedure DELETE_ROW (
1274 X_ROWID IN VARCHAR2
1275 ) AS
1276 begin
1277
1278 Before_DML(
1279 p_action => 'DELETE',
1280 x_rowid => X_ROWID
1281 );
1282
1283 delete from IGS_EN_CAL_CONF
1284 where ROWID = X_ROWID;
1285 if (sql%notfound) then
1286 raise no_data_found;
1287 end if;
1288
1289 After_DML(
1290 p_action => 'DELETE',
1291 x_rowid => X_ROWID
1292 );
1293
1294
1295 end DELETE_ROW;
1296
1297 end IGS_EN_CAL_CONF_PKG;