[Home] [Help]
PACKAGE BODY: APPS.IGS_AS_CAL_CONF_PKG
Source
1 package body IGS_AS_CAL_CONF_PKG as
2 /* $Header: IGSDI45B.pls 115.8 2002/11/28 23:21:51 nsidana ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_AS_CAL_CONF%RowType;
6 new_references IGS_AS_CAL_CONF%RowType;
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_s_control_num IN NUMBER DEFAULT NULL,
11 x_ass_item_cutoff_dt_alias IN VARCHAR2 DEFAULT NULL,
12 x_mid_mgs_start_dt_alias IN VARCHAR2 DEFAULT NULL ,
13 x_mid_mgs_end_dt_alias IN VARCHAR2 DEFAULT NULL ,
14 x_efinal_mgs_start_dt_alias IN VARCHAR2 DEFAULT NULL ,
15 x_efinal_mgs_end_dt_alias IN VARCHAR2 DEFAULT NULL ,
16 x_final_mgs_start_dt_alias IN VARCHAR2 DEFAULT NULL ,
17 x_final_mgs_end_dt_alias IN VARCHAR2 DEFAULT NULL ,
18 x_creation_date IN DATE DEFAULT NULL,
19 x_created_by IN NUMBER DEFAULT NULL,
20 x_last_update_date IN DATE DEFAULT NULL,
21 x_last_updated_by IN NUMBER DEFAULT NULL,
22 x_last_update_login IN NUMBER DEFAULT NULL
23 ) as
24 CURSOR cur_old_ref_values IS
25 SELECT *
26 FROM IGS_AS_CAL_CONF
27 WHERE rowid = x_rowid;
28 BEGIN
29 l_rowid := x_rowid;
30 -- Code for setting the Old and New Reference Values.
31 -- Populate Old Values.
32 Open cur_old_ref_values;
33 Fetch cur_old_ref_values INTO old_references;
34 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
35 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
36 Igs_Ge_Msg_Stack.Add;
37 Close cur_old_ref_values;
38 App_Exception.Raise_Exception;
39 Return;
40 END IF;
41 Close cur_old_ref_values;
42 -- Populate New Values.
43 new_references.s_control_num := x_s_control_num;
44 new_references.ass_item_cutoff_dt_alias := x_ass_item_cutoff_dt_alias;
45 new_references.mid_mgs_start_dt_alias := x_mid_mgs_start_dt_alias ;
46 new_references.mid_mgs_end_dt_alias := x_mid_mgs_end_dt_alias ;
47 new_references.efinal_mgs_start_dt_alias := x_efinal_mgs_start_dt_alias ;
48 new_references.efinal_mgs_end_dt_alias := x_efinal_mgs_end_dt_alias ;
49 new_references.final_mgs_start_dt_alias:= x_final_mgs_start_dt_alias ;
50 new_references.final_mgs_end_dt_alias := x_final_mgs_end_dt_alias ;
51
52 IF (p_action = 'UPDATE') THEN
53 new_references.creation_date := old_references.creation_date;
54 new_references.created_by := old_references.created_by;
55 ELSE
56 new_references.creation_date := x_creation_date;
57 new_references.created_by := x_created_by;
58 END IF;
59 new_references.last_update_date := x_last_update_date;
60 new_references.last_updated_by := x_last_updated_by;
61 new_references.last_update_login := x_last_update_login;
62 END Set_Column_Values;
63 PROCEDURE BeforeRowInsertUpdate1(
64 p_inserting IN BOOLEAN DEFAULT FALSE,
65 p_updating IN BOOLEAN DEFAULT FALSE,
66 p_deleting IN BOOLEAN DEFAULT FALSE
67 ) as
68 v_message_name varchar2(30);
69 BEGIN
70 -- Validate the date alias values.
71 -- Assessment Item Cutoff Date Alias.
72
73 IF p_inserting OR
74 ((NVL(old_references.ass_item_cutoff_dt_alias, 'NULL') <>
75 NVL(new_references.ass_item_cutoff_dt_alias, 'NULL')) AND
76 new_references.ass_item_cutoff_dt_alias IS NOT NULL) THEN
77 IF IGS_AS_VAL_SACC.assp_val_sacc_da (
78 new_references.ass_item_cutoff_dt_alias,
79 v_message_name) = FALSE THEN
80 Fnd_Message.Set_Name('IGS', v_message_name);
81 Igs_Ge_Msg_Stack.Add;
82 App_Exception.Raise_Exception;
83 END IF;
84 END IF;
85
86 IF p_inserting OR
87 ((NVL(old_references.mid_mgs_start_dt_alias, 'NULL') <>
88 NVL(new_references.mid_mgs_start_dt_alias, 'NULL')) AND
89 new_references.mid_mgs_start_dt_alias IS NOT NULL) THEN
90 IF IGS_AS_VAL_SACC.assp_val_sacc_da (
91 new_references.mid_mgs_start_dt_alias,
92 v_message_name) = FALSE THEN
93 Fnd_Message.Set_Name('IGS', v_message_name);
94 Igs_Ge_Msg_Stack.Add;
95 App_Exception.Raise_Exception;
96 END IF;
97 END IF;
98
99 IF p_inserting OR
100 ((NVL(old_references.mid_mgs_end_dt_alias, 'NULL') <>
101 NVL(new_references.mid_mgs_end_dt_alias, 'NULL')) AND
102 new_references.mid_mgs_end_dt_alias IS NOT NULL) THEN
103 IF IGS_AS_VAL_SACC.assp_val_sacc_da (
104 new_references.mid_mgs_end_dt_alias,
105 v_message_name) = FALSE THEN
106 Fnd_Message.Set_Name('IGS', v_message_name);
107 Igs_Ge_Msg_Stack.Add;
108 App_Exception.Raise_Exception;
109 END IF;
110 END IF;
111
112 IF p_inserting OR
113 ((NVL(old_references.efinal_mgs_start_dt_alias, 'NULL') <>
114 NVL(new_references.efinal_mgs_start_dt_alias, 'NULL')) AND
115 new_references.efinal_mgs_start_dt_alias IS NOT NULL) THEN
116 IF IGS_AS_VAL_SACC.assp_val_sacc_da (
117 new_references.efinal_mgs_start_dt_alias,
118 v_message_name) = FALSE THEN
119 Fnd_Message.Set_Name('IGS', v_message_name);
120 Igs_Ge_Msg_Stack.Add;
121 App_Exception.Raise_Exception;
122 END IF;
123 END IF;
124
125 IF p_inserting OR
126 ((NVL(old_references.efinal_mgs_end_dt_alias, 'NULL') <>
127 NVL(new_references.efinal_mgs_end_dt_alias, 'NULL')) AND
128 new_references.efinal_mgs_end_dt_alias IS NOT NULL) THEN
129 IF IGS_AS_VAL_SACC.assp_val_sacc_da (
130 new_references.efinal_mgs_end_dt_alias,
131 v_message_name) = FALSE THEN
132 Fnd_Message.Set_Name('IGS', v_message_name);
133 Igs_Ge_Msg_Stack.Add;
134 App_Exception.Raise_Exception;
135 END IF;
136 END IF;
137
138 IF p_inserting OR
139 ((NVL(old_references.final_mgs_start_dt_alias, 'NULL') <>
140 NVL(new_references.final_mgs_start_dt_alias, 'NULL')) AND
141 new_references.final_mgs_start_dt_alias IS NOT NULL) THEN
142 IF IGS_AS_VAL_SACC.assp_val_sacc_da (
143 new_references.final_mgs_start_dt_alias,
144 v_message_name) = FALSE THEN
145 Fnd_Message.Set_Name('IGS', v_message_name);
146 Igs_Ge_Msg_Stack.Add;
147 App_Exception.Raise_Exception;
148 END IF;
149 END IF;
150
151 IF p_inserting OR
152 ((NVL(old_references.final_mgs_end_dt_alias, 'NULL') <>
153 NVL(new_references.final_mgs_end_dt_alias, 'NULL')) AND
154 new_references.final_mgs_end_dt_alias IS NOT NULL) THEN
155 IF IGS_AS_VAL_SACC.assp_val_sacc_da (
156 new_references.final_mgs_end_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
164
165
166 END BeforeRowInsertUpdate1;
167
168 PROCEDURE Check_Parent_Existance as
169 BEGIN
170 IF (((old_references.ass_item_cutoff_dt_alias = new_references.ass_item_cutoff_dt_alias)) OR
171 ((new_references.ass_item_cutoff_dt_alias IS NULL))) THEN
172 NULL;
173 ELSE
174 IF NOT(IGS_CA_DA_PKG.Get_PK_For_Validation (
175 new_references.ass_item_cutoff_dt_alias
176 ))THEN
177 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
178 Igs_Ge_Msg_Stack.Add;
179 App_Exception.Raise_Exception;
180 END IF;
181 END IF;
182 END Check_Parent_Existance;
183 PROCEDURE Check_Constraints (
184 Column_Name IN VARCHAR2 DEFAULT NULL,
185 Column_Value IN VARCHAR2 DEFAULT NULL
186 ) as
187 BEGIN
188 IF column_name is null then
189 NULL;
190 ELSIF upper(Column_name) = 'S_CONTROL_NUM' then
191 new_references.s_control_num:= IGS_GE_NUMBER.TO_NUM(column_value);
192 ELSIF upper(Column_name) = 'ASS_ITEM_CUTOFF_DT_ALIAS' then
193 new_references.ass_item_cutoff_dt_alias:= column_value;
194 END IF;
195
196 IF upper(column_name) = 'S_CONTROL_NUM' OR
197 column_name is null Then
198 IF new_references.s_control_num < 1 AND new_references.s_control_num > 1 Then
199 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
200 Igs_Ge_Msg_Stack.Add;
201 App_Exception.Raise_Exception;
202 END IF;
203 END IF;
204 IF upper(column_name) = 'ASS_ITEM_CUTOFF_DT_ALIAS' OR
205 column_name is null Then
206 IF new_references.ass_item_cutoff_dt_alias <> UPPER(new_references.ass_item_cutoff_dt_alias) Then
207 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
208 Igs_Ge_Msg_Stack.Add;
209 App_Exception.Raise_Exception;
210 END IF;
211 END IF;
212
213 IF upper(column_name) = 'MID_MGS_START_DT_ALIAS' OR
214 column_name is null Then
215 IF new_references.mid_mgs_start_dt_alias <> UPPER(new_references.mid_mgs_start_dt_alias) Then
216 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
217 Igs_Ge_Msg_Stack.Add;
218 App_Exception.Raise_Exception;
219 END IF;
220 END IF;
221
222 IF upper(column_name) = 'MID_MGS_END_DT_ALIAS' OR
223 column_name is null Then
224 IF new_references.mid_mgs_end_dt_alias <> UPPER(new_references.mid_mgs_end_dt_alias) Then
225 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
226 Igs_Ge_Msg_Stack.Add;
227 App_Exception.Raise_Exception;
228 END IF;
229 END IF;
230
231 IF upper(column_name) = 'EFINAL_MGS_START_DT_ALIAS' OR
232 column_name is null Then
233 IF new_references.efinal_mgs_start_dt_alias <> UPPER(new_references.efinal_mgs_start_dt_alias) Then
234 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
235 Igs_Ge_Msg_Stack.Add;
236 App_Exception.Raise_Exception;
237 END IF;
238 END IF;
239
240 IF upper(column_name) = 'EFINAL_MGS_END_DT_ALIAS' OR
241 column_name is null Then
242 IF new_references.efinal_mgs_end_dt_alias <> UPPER(new_references.efinal_mgs_end_dt_alias) Then
243 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
244 Igs_Ge_Msg_Stack.Add;
245 App_Exception.Raise_Exception;
246 END IF;
247 END IF;
248
249 IF upper(column_name) = 'FINAL_MGS_START_DT_ALIAS' OR
250 column_name is null Then
251 IF new_references.final_mgs_start_dt_alias <> UPPER(new_references.final_mgs_start_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) = 'FINAL_MGS_END_DT_ALIAS' OR
259 column_name is null Then
260 IF new_references.final_mgs_end_dt_alias <> UPPER(new_references.final_mgs_end_dt_alias) Then
261 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
262 Igs_Ge_Msg_Stack.Add;
263 App_Exception.Raise_Exception;
264 END IF;
265 END IF;
266
267 END Check_Constraints;
268
269
270 FUNCTION Get_PK_For_Validation (
271 x_s_control_num IN NUMBER
272 ) RETURN BOOLEAN AS
273 CURSOR cur_rowid IS
274 SELECT rowid
275 FROM IGS_AS_CAL_CONF
276 WHERE s_control_num = x_s_control_num
277 FOR UPDATE NOWAIT;
278 lv_rowid cur_rowid%RowType;
279 BEGIN
280 Open cur_rowid;
281 Fetch cur_rowid INTO lv_rowid;
282 IF (cur_rowid%FOUND) THEN
283 Close cur_rowid;
284 Return (TRUE);
285 ELSE
286 Close cur_rowid;
287 Return (FALSE);
288 END IF;
289 END Get_PK_For_Validation;
290
291 PROCEDURE GET_FK_IGS_CA_DA (
292 x_dt_alias IN VARCHAR2
293 ) as
294 CURSOR cur_rowid IS
295 SELECT rowid
296 FROM IGS_AS_CAL_CONF
297 WHERE ass_item_cutoff_dt_alias = x_dt_alias ;
298 lv_rowid cur_rowid%RowType;
299 BEGIN
300 Open cur_rowid;
301 Fetch cur_rowid INTO lv_rowid;
302 IF (cur_rowid%FOUND) THEN
303 Fnd_Message.Set_Name ('IGS', 'IGS_AS_SACC_DA_ASS_ITEM_FK');
304 Igs_Ge_Msg_Stack.Add;
305 Close cur_rowid;
306 App_Exception.Raise_Exception;
307 Return;
308 END IF;
309 Close cur_rowid;
310 END GET_FK_IGS_CA_DA;
311 PROCEDURE Before_DML (
312 p_action IN VARCHAR2,
313 x_rowid IN VARCHAR2 DEFAULT NULL,
314 x_s_control_num IN NUMBER DEFAULT NULL,
315 x_ass_item_cutoff_dt_alias IN VARCHAR2 DEFAULT NULL,
316 x_mid_mgs_start_dt_alias IN VARCHAR2 DEFAULT NULL ,
317 x_mid_mgs_end_dt_alias IN VARCHAR2 DEFAULT NULL ,
318 x_efinal_mgs_start_dt_alias IN VARCHAR2 DEFAULT NULL ,
319 x_efinal_mgs_end_dt_alias IN VARCHAR2 DEFAULT NULL ,
320 x_final_mgs_start_dt_alias IN VARCHAR2 DEFAULT NULL ,
321 x_final_mgs_end_dt_alias IN VARCHAR2 DEFAULT NULL ,
322 x_creation_date IN DATE DEFAULT NULL,
323 x_created_by IN NUMBER DEFAULT NULL,
324 x_last_update_date IN DATE DEFAULT NULL,
325 x_last_updated_by IN NUMBER DEFAULT NULL,
326 x_last_update_login IN NUMBER DEFAULT NULL
327 ) as
328 BEGIN
329 Set_Column_Values (
330 p_action,
331 x_rowid,
332 x_s_control_num,
333 x_ass_item_cutoff_dt_alias,
334 x_mid_mgs_start_dt_alias ,
335 x_mid_mgs_end_dt_alias ,
336 x_efinal_mgs_start_dt_alias,
337 x_efinal_mgs_end_dt_alias ,
338 x_final_mgs_start_dt_alias ,
339 x_final_mgs_end_dt_alias ,
340 x_creation_date,
341 x_created_by,
342 x_last_update_date,
343 x_last_updated_by,
344 x_last_update_login
345 );
346 IF (p_action = 'INSERT') THEN
347 -- Call all the procedures related to Before Insert.
348 BeforeRowInsertUpdate1 ( p_inserting => TRUE );
349 IF Get_PK_For_Validation (
350 new_references.s_control_num
351 ) THEN
352 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
353 Igs_Ge_Msg_Stack.Add;
354 App_Exception.Raise_Exception;
355 END IF;
356
357 Check_Constraints;
358 Check_Parent_Existance;
359 ELSIF (p_action = 'UPDATE') THEN
360 -- Call all the procedures related to Before Update.
361 BeforeRowInsertUpdate1 ( p_updating => TRUE );
362 Check_Constraints;
363 Check_Parent_Existance;
364 ELSIF (p_action = 'DELETE') THEN
365 -- Call all the procedures related to Before Delete.
366 Null;
367 ELSIF (p_action = 'VALIDATE_INSERT') THEN
368 IF Get_PK_For_Validation (
369 new_references.s_control_num
370 ) THEN
371 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
372 Igs_Ge_Msg_Stack.Add;
373 App_Exception.Raise_Exception;
374 END IF;
375 Check_Constraints;
376 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
377 Check_Constraints;
378 ELSIF (p_action = 'VALIDATE_DELETE') THEN
379 NULL;
380 END IF;
381 END Before_DML;
382 procedure INSERT_ROW (
383 X_ROWID in out NOCOPY VARCHAR2,
384 X_S_CONTROL_NUM in NUMBER,
385 X_ASS_ITEM_CUTOFF_DT_ALIAS in VARCHAR2,
386 X_MID_MGS_START_DT_ALIAS in VARCHAR2,
387 X_MID_MGS_END_DT_ALIAS in VARCHAR2,
388 X_EFINAL_MGS_START_DT_ALIAS in VARCHAR2,
389 X_EFINAL_MGS_END_DT_ALIAS in VARCHAR2,
390 X_FINAL_MGS_START_DT_ALIAS in VARCHAR2,
391 X_FINAL_MGS_END_DT_ALIAS in VARCHAR2,
392 X_MODE in VARCHAR2 default 'R'
393 ) as
394 cursor C is select ROWID from IGS_AS_CAL_CONF
395 where S_CONTROL_NUM = new_references.S_CONTROL_NUM;
396 X_LAST_UPDATE_DATE DATE;
397 X_LAST_UPDATED_BY NUMBER;
398 X_LAST_UPDATE_LOGIN NUMBER;
399 begin
400 X_LAST_UPDATE_DATE := SYSDATE;
401 if(X_MODE = 'I') then
402 X_LAST_UPDATED_BY := 1;
403 X_LAST_UPDATE_LOGIN := 0;
404 elsif (X_MODE = 'R') then
405 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
406 if X_LAST_UPDATED_BY is NULL then
407 X_LAST_UPDATED_BY := -1;
408 end if;
409 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
410 if X_LAST_UPDATE_LOGIN is NULL then
411 X_LAST_UPDATE_LOGIN := -1;
412 end if;
413 else
414 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
415 Igs_Ge_Msg_Stack.Add;
416 app_exception.raise_exception;
417 end if;
418 Before_DML(
419 p_action=>'INSERT',
420 x_rowid=>X_ROWID,
421 x_ass_item_cutoff_dt_alias=>X_ASS_ITEM_CUTOFF_DT_ALIAS,
422 x_s_control_num=>X_S_CONTROL_NUM,
423 x_mid_mgs_start_dt_alias => X_MID_MGS_START_DT_ALIAS,
424 x_mid_mgs_end_dt_alias => X_MID_MGS_END_DT_ALIAS,
425 x_efinal_mgs_start_dt_alias => X_EFINAL_MGS_START_DT_ALIAS,
426 x_efinal_mgs_end_dt_alias => X_EFINAL_MGS_END_DT_ALIAS,
427 x_final_mgs_start_dt_alias => X_FINAL_MGS_START_DT_ALIAS,
428 x_final_mgs_end_dt_alias => X_FINAL_MGS_END_DT_ALIAS ,
429 x_creation_date=>X_LAST_UPDATE_DATE,
430 x_created_by=>X_LAST_UPDATED_BY,
431 x_last_update_date=>X_LAST_UPDATE_DATE,
432 x_last_updated_by=>X_LAST_UPDATED_BY,
433 x_last_update_login=>X_LAST_UPDATE_LOGIN
434 );
435 insert into IGS_AS_CAL_CONF (
436 S_CONTROL_NUM,
437 ASS_ITEM_CUTOFF_DT_ALIAS,
438 MID_MGS_START_DT_ALIAS,
439 MID_MGS_END_DT_ALIAS,
440 EFINAL_MGS_START_DT_ALIAS,
441 EFINAL_MGS_END_DT_ALIAS ,
442 FINAL_MGS_START_DT_ALIAS,
443 FINAL_MGS_END_DT_ALIAS,
444 CREATION_DATE,
445 CREATED_BY,
446 LAST_UPDATE_DATE,
447 LAST_UPDATED_BY,
448 LAST_UPDATE_LOGIN
449 ) values (
450 NEW_REFERENCES.S_CONTROL_NUM,
451 NEW_REFERENCES.ASS_ITEM_CUTOFF_DT_ALIAS,
452 NEW_REFERENCES.MID_MGS_START_DT_ALIAS,
453 NEW_REFERENCES.MID_MGS_END_DT_ALIAS,
454 NEW_REFERENCES.EFINAL_MGS_START_DT_ALIAS,
455 NEW_REFERENCES.EFINAL_MGS_END_DT_ALIAS ,
456 NEW_REFERENCES.FINAL_MGS_START_DT_ALIAS,
457 NEW_REFERENCES.FINAL_MGS_END_DT_ALIAS,
458 X_LAST_UPDATE_DATE,
459 X_LAST_UPDATED_BY,
460 X_LAST_UPDATE_DATE,
461 X_LAST_UPDATED_BY,
462 X_LAST_UPDATE_LOGIN
463 );
464 open c;
465 fetch c into X_ROWID;
466 if (c%notfound) then
467 close c;
468 raise no_data_found;
469 end if;
470 close c;
471 end INSERT_ROW;
472 procedure LOCK_ROW (
473 X_ROWID in VARCHAR2,
474 X_S_CONTROL_NUM in NUMBER,
475 X_ASS_ITEM_CUTOFF_DT_ALIAS in VARCHAR2,
476 X_MID_MGS_START_DT_ALIAS in VARCHAR2,
477 X_MID_MGS_END_DT_ALIAS in VARCHAR2,
478 X_EFINAL_MGS_START_DT_ALIAS in VARCHAR2,
479 X_EFINAL_MGS_END_DT_ALIAS in VARCHAR2,
480 X_FINAL_MGS_START_DT_ALIAS in VARCHAR2,
481 X_FINAL_MGS_END_DT_ALIAS in VARCHAR2
482 ) as
483 cursor c1 is select
484 ASS_ITEM_CUTOFF_DT_ALIAS,
485 MID_MGS_START_DT_ALIAS,
486 MID_MGS_END_DT_ALIAS,
487 EFINAL_MGS_START_DT_ALIAS,
488 EFINAL_MGS_END_DT_ALIAS,
489 FINAL_MGS_START_DT_ALIAS,
490 FINAL_MGS_END_DT_ALIAS
491 from IGS_AS_CAL_CONF
492 where ROWID = X_ROWID for update nowait;
493 tlinfo c1%rowtype;
494 begin
495 open c1;
496 fetch c1 into tlinfo;
497 if (c1%notfound) then
498 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
499 Igs_Ge_Msg_Stack.Add;
500 close c1;
501 app_exception.raise_exception;
502 return;
503 end if;
504 close c1;
505 if (
506 ((tlinfo.ASS_ITEM_CUTOFF_DT_ALIAS = X_ASS_ITEM_CUTOFF_DT_ALIAS) OR ((tlinfo.ASS_ITEM_CUTOFF_DT_ALIAS is null) AND (X_ASS_ITEM_CUTOFF_DT_ALIAS is null))) AND
507 ((tlinfo.MID_MGS_START_DT_ALIAS = X_MID_MGS_START_DT_ALIAS) OR ((tlinfo.MID_MGS_START_DT_ALIAS is null) AND (X_MID_MGS_START_DT_ALIAS is null))) AND
508 ((tlinfo.MID_MGS_END_DT_ALIAS = X_MID_MGS_END_DT_ALIAS) OR ((tlinfo.MID_MGS_END_DT_ALIAS is null) AND (X_MID_MGS_END_DT_ALIAS is null))) AND
509 ((tlinfo.EFINAL_MGS_START_DT_ALIAS = X_EFINAL_MGS_START_DT_ALIAS) OR ((tlinfo.EFINAL_MGS_START_DT_ALIAS is null) AND (X_EFINAL_MGS_START_DT_ALIAS is null))) AND
510 ((tlinfo.EFINAL_MGS_END_DT_ALIAS = X_EFINAL_MGS_END_DT_ALIAS) OR ((tlinfo.EFINAL_MGS_END_DT_ALIAS is null) AND (X_EFINAL_MGS_END_DT_ALIAS is null))) AND
511 ((tlinfo.FINAL_MGS_START_DT_ALIAS = X_FINAL_MGS_START_DT_ALIAS) OR ((tlinfo.FINAL_MGS_START_DT_ALIAS is null) AND (X_FINAL_MGS_START_DT_ALIAS is null))) AND
512 ((tlinfo.FINAL_MGS_END_DT_ALIAS = X_FINAL_MGS_END_DT_ALIAS) OR ((tlinfo.FINAL_MGS_END_DT_ALIAS is null) AND (X_FINAL_MGS_END_DT_ALIAS is null)))
513 ) then
514 null;
515 else
516 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
517 Igs_Ge_Msg_Stack.Add;
518 app_exception.raise_exception;
519 end if;
520 return;
521 end LOCK_ROW;
522 procedure UPDATE_ROW (
523 X_ROWID in VARCHAR2,
524 X_S_CONTROL_NUM in NUMBER,
525 X_ASS_ITEM_CUTOFF_DT_ALIAS in VARCHAR2,
526 X_MID_MGS_START_DT_ALIAS in VARCHAR2,
527 X_MID_MGS_END_DT_ALIAS in VARCHAR2,
528 X_EFINAL_MGS_START_DT_ALIAS in VARCHAR2,
529 X_EFINAL_MGS_END_DT_ALIAS in VARCHAR2,
530 X_FINAL_MGS_START_DT_ALIAS in VARCHAR2,
531 X_FINAL_MGS_END_DT_ALIAS in VARCHAR2,
532 X_MODE in VARCHAR2 default 'R'
533 ) as
534 X_LAST_UPDATE_DATE DATE;
535 X_LAST_UPDATED_BY NUMBER;
536 X_LAST_UPDATE_LOGIN NUMBER;
537 begin
538 X_LAST_UPDATE_DATE := SYSDATE;
539 if(X_MODE = 'I') then
540 X_LAST_UPDATED_BY := 1;
541 X_LAST_UPDATE_LOGIN := 0;
542 elsif (X_MODE = 'R') then
543 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
544 if X_LAST_UPDATED_BY is NULL then
545 X_LAST_UPDATED_BY := -1;
546 end if;
547 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
548 if X_LAST_UPDATE_LOGIN is NULL then
549 X_LAST_UPDATE_LOGIN := -1;
550 end if;
551 else
552 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
553 Igs_Ge_Msg_Stack.Add;
554 app_exception.raise_exception;
555 end if;
556 Before_DML(
557 p_action=>'UPDATE',
558 x_rowid=>X_ROWID,
559 x_ass_item_cutoff_dt_alias=>X_ASS_ITEM_CUTOFF_DT_ALIAS,
560 x_s_control_num=>X_S_CONTROL_NUM,
561 x_mid_mgs_start_dt_alias => X_MID_MGS_START_DT_ALIAS,
562 x_mid_mgs_end_dt_alias => X_MID_MGS_END_DT_ALIAS,
563 x_efinal_mgs_start_dt_alias => X_EFINAL_MGS_START_DT_ALIAS,
564 x_efinal_mgs_end_dt_alias => X_EFINAL_MGS_END_DT_ALIAS,
565 x_final_mgs_start_dt_alias => X_FINAL_MGS_START_DT_ALIAS,
566 x_final_mgs_end_dt_alias => X_FINAL_MGS_END_DT_ALIAS ,
567 x_creation_date=>X_LAST_UPDATE_DATE,
568 x_created_by=>X_LAST_UPDATED_BY,
569 x_last_update_date=>X_LAST_UPDATE_DATE,
570 x_last_updated_by=>X_LAST_UPDATED_BY,
571 x_last_update_login=>X_LAST_UPDATE_LOGIN
572 );
573 update IGS_AS_CAL_CONF set
574 ASS_ITEM_CUTOFF_DT_ALIAS = NEW_REFERENCES.ASS_ITEM_CUTOFF_DT_ALIAS,
575 MID_MGS_START_DT_ALIAS = NEW_REFERENCES.MID_MGS_START_DT_ALIAS,
576 MID_MGS_END_DT_ALIAS = NEW_REFERENCES.MID_MGS_END_DT_ALIAS,
577 EFINAL_MGS_START_DT_ALIAS = NEW_REFERENCES.EFINAL_MGS_START_DT_ALIAS,
578 EFINAL_MGS_END_DT_ALIAS = NEW_REFERENCES.EFINAL_MGS_END_DT_ALIAS,
579 FINAL_MGS_START_DT_ALIAS = NEW_REFERENCES.FINAL_MGS_START_DT_ALIAS,
580 FINAL_MGS_END_DT_ALIAS = NEW_REFERENCES.FINAL_MGS_END_DT_ALIAS,
581 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
582 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
583 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
584 where ROWID = X_ROWID;
585 if (sql%notfound) then
586 raise no_data_found;
587 end if;
588 end UPDATE_ROW;
589 procedure ADD_ROW (
590 X_ROWID in out NOCOPY VARCHAR2,
591 X_S_CONTROL_NUM in NUMBER,
592 X_ASS_ITEM_CUTOFF_DT_ALIAS in VARCHAR2,
593 X_MID_MGS_START_DT_ALIAS in VARCHAR2,
594 X_MID_MGS_END_DT_ALIAS in VARCHAR2,
595 X_EFINAL_MGS_START_DT_ALIAS in VARCHAR2,
596 X_EFINAL_MGS_END_DT_ALIAS in VARCHAR2,
597 X_FINAL_MGS_START_DT_ALIAS in VARCHAR2,
598 X_FINAL_MGS_END_DT_ALIAS in VARCHAR2,
599 X_MODE in VARCHAR2 default 'R'
600 ) as
601 cursor c1 is select rowid from IGS_AS_CAL_CONF
602 where S_CONTROL_NUM = nvl(X_S_CONTROL_NUM,1);
603 begin
604 open c1;
605 fetch c1 into X_ROWID;
606 if (c1%notfound) then
607 close c1;
608 INSERT_ROW (
609 X_ROWID,
610 X_S_CONTROL_NUM,
611 X_ASS_ITEM_CUTOFF_DT_ALIAS,
612 X_MID_MGS_START_DT_ALIAS ,
613 X_MID_MGS_END_DT_ALIAS ,
614 X_EFINAL_MGS_START_DT_ALIAS ,
615 X_EFINAL_MGS_END_DT_ALIAS ,
616 X_FINAL_MGS_START_DT_ALIAS ,
617 X_FINAL_MGS_END_DT_ALIAS ,
618 X_MODE);
619 return;
620 end if;
621 close c1;
622 UPDATE_ROW (
623 X_ROWID,
624 X_S_CONTROL_NUM,
625 X_ASS_ITEM_CUTOFF_DT_ALIAS,
626 X_MID_MGS_START_DT_ALIAS ,
627 X_MID_MGS_END_DT_ALIAS ,
628 X_EFINAL_MGS_START_DT_ALIAS ,
629 X_EFINAL_MGS_END_DT_ALIAS ,
630 X_FINAL_MGS_START_DT_ALIAS ,
631 X_FINAL_MGS_END_DT_ALIAS ,
632 X_MODE);
633 end ADD_ROW;
634 procedure DELETE_ROW (
635 X_ROWID in VARCHAR2) as
636 begin
637 Before_DML(
638 p_action => 'DELETE',
639 x_rowid => X_ROWID
640 );
641 delete from IGS_AS_CAL_CONF
642 where ROWID = X_ROWID;
643 if (sql%notfound) then
644 raise no_data_found;
645 end if;
646 end DELETE_ROW;
647 end IGS_AS_CAL_CONF_PKG;