[Home] [Help]
PACKAGE BODY: APPS.IGS_GR_CRMN_ROUND_PKG
Source
1 package body IGS_GR_CRMN_ROUND_PKG as
2 /* $Header: IGSGI07B.pls 115.9 2003/09/22 06:29:12 nalkumar ship $ */
3 -- BUG #1956374 , Procedure assp_val_ci_status reference is changed
4 l_rowid VARCHAR2(25);
5 old_references IGS_GR_CRMN_ROUND_ALL%RowType;
6 new_references IGS_GR_CRMN_ROUND_ALL%RowType;
7
8 PROCEDURE Set_Column_Values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_grd_cal_type IN VARCHAR2 DEFAULT NULL,
12 x_grd_ci_sequence_number IN NUMBER DEFAULT NULL,
13 x_start_dt_alias IN VARCHAR2 DEFAULT NULL,
14 x_start_dai_sequence_number IN NUMBER DEFAULT NULL,
15 x_end_dt_alias IN VARCHAR2 DEFAULT NULL,
16 x_end_dai_sequence_number IN NUMBER DEFAULT NULL,
17 x_creation_date IN DATE DEFAULT NULL,
18 x_created_by IN NUMBER DEFAULT NULL,
19 x_last_update_date IN DATE DEFAULT NULL,
20 x_last_updated_by IN NUMBER DEFAULT NULL,
21 x_last_update_login IN NUMBER DEFAULT NULL ,
22 x_org_id IN NUMBER DEFAULT NULL,
23 x_conferral_start_date IN DATE DEFAULT NULL,
24 x_conferral_end_date IN DATE DEFAULT NULL,
25 x_completion_start_date IN DATE DEFAULT NULL,
26 x_completion_end_date IN DATE DEFAULT NULL
27 ) AS
28
29 CURSOR cur_old_ref_values IS
30 SELECT *
31 FROM IGS_GR_CRMN_ROUND_ALL
32 WHERE rowid = x_rowid;
33
34 BEGIN
35
36 l_rowid := x_rowid;
37
38 -- Code for setting the Old and New Reference Values.
39 -- Populate Old Values.
40 Open cur_old_ref_values;
41 Fetch cur_old_ref_values INTO old_references;
42 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT')) THEN
43 Close cur_old_ref_values;
44 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
45 IGS_GE_MSG_STACK.ADD;
46 App_Exception.Raise_Exception;
47 Return;
48 END IF;
49 Close cur_old_ref_values;
50
51 -- Populate New Values.
52 new_references.grd_cal_type := x_grd_cal_type;
53 new_references.grd_ci_sequence_number := x_grd_ci_sequence_number;
54 new_references.start_dt_alias := x_start_dt_alias;
55 new_references.start_dai_sequence_number := x_start_dai_sequence_number;
56 new_references.end_dt_alias := x_end_dt_alias;
57 new_references.end_dai_sequence_number := x_end_dai_sequence_number;
58 new_references.org_id := x_org_id;
59 new_references.conferral_start_date := x_conferral_start_date ;
60 new_references.conferral_end_date := x_conferral_end_date ;
61 new_references.completion_start_date := x_completion_start_date;
62 new_references.completion_end_date := x_completion_end_date ;
63 IF (p_action = 'UPDATE') THEN
64 new_references.creation_date := old_references.creation_date;
65 new_references.created_by := old_references.created_by;
66 ELSE
67 new_references.creation_date := x_creation_date;
68 new_references.created_by := x_created_by;
69 END IF;
70 new_references.last_update_date := x_last_update_date;
71 new_references.last_updated_by := x_last_updated_by;
72 new_references.last_update_login := x_last_update_login;
73
74 END Set_Column_Values;
75
76 -- Trigger description :-
77 -- "OSS_TST".trg_crd_br_iu
78 -- BEFORE INSERT OR UPDATE
79 -- ON IGS_GR_CRMN_ROUND_ALL
80 -- FOR EACH ROW
81
82 PROCEDURE BeforeRowInsertUpdate1(
83 p_inserting IN BOOLEAN DEFAULT FALSE,
84 p_updating IN BOOLEAN DEFAULT FALSE,
85 p_deleting IN BOOLEAN DEFAULT FALSE
86 ) AS
87 v_message_name VARCHAR2(30);
88 BEGIN
89 -- Validate the graduation cal instance is of the correct category and status
90 IF p_inserting THEN
91 IF IGS_GR_VAL_CRD.grdp_val_ci_grad(
92 new_references.grd_cal_type,
93 v_message_name) = FALSE THEN
94 Fnd_Message.Set_Name('IGS', v_message_name);
95 IGS_GE_MSG_STACK.ADD;
96 App_Exception.Raise_Exception;
97 END IF;
98
99 IF IGS_AS_VAL_EVSA.assp_val_ci_status(
100 new_references.grd_cal_type,
101 new_references.grd_ci_sequence_number,
102 v_message_name) = FALSE THEN
103 Fnd_Message.Set_Name('IGS', v_message_name);
104 IGS_GE_MSG_STACK.ADD;
105 App_Exception.Raise_Exception;
106 END IF;
107 END IF;
108
109
110 END BeforeRowInsertUpdate1;
111
112 PROCEDURE Check_Parent_Existance AS
113 BEGIN
114
115 IF (((old_references.grd_cal_type = new_references.grd_cal_type) AND
116 (old_references.grd_ci_sequence_number = new_references.grd_ci_sequence_number)) OR
117 ((new_references.grd_cal_type IS NULL) OR
118 (new_references.grd_ci_sequence_number IS NULL))) THEN
119 NULL;
120 ELSE
121 IF NOT IGS_CA_INST_PKG.Get_PK_For_Validation (
122 new_references.grd_cal_type,
123 new_references.grd_ci_sequence_number
124 ) THEN
125 FND_MESSAGE.SET_NAME ('FND', 'FORM_RECORD_DELETED');
126 IGS_GE_MSG_STACK.ADD;
127 APP_EXCEPTION.RAISE_EXCEPTION;
128 END IF;
129
130 END IF;
131
132 IF (((old_references.end_dt_alias = new_references.end_dt_alias) AND
133 (old_references.end_dai_sequence_number = new_references.end_dai_sequence_number) AND
134 (old_references.grd_cal_type = new_references.grd_cal_type) AND
135 (old_references.grd_ci_sequence_number = new_references.grd_ci_sequence_number)) OR
136 ((new_references.end_dt_alias IS NULL) OR
137 (new_references.end_dai_sequence_number IS NULL) OR
138 (new_references.grd_cal_type IS NULL) OR
139 (new_references.grd_ci_sequence_number IS NULL))) THEN
140 NULL;
141 ELSE
142 IF NOT IGS_CA_DA_INST_PKG.Get_PK_For_Validation (
143 new_references.end_dt_alias,
144 new_references.end_dai_sequence_number,
145 new_references.grd_cal_type,
146 new_references.grd_ci_sequence_number
147 ) THEN
148 FND_MESSAGE.SET_NAME ('FND', 'FORM_RECORD_DELETED');
149 IGS_GE_MSG_STACK.ADD;
150 APP_EXCEPTION.RAISE_EXCEPTION;
151 END IF;
152
153 END IF;
154
155 IF (((old_references.start_dt_alias = new_references.start_dt_alias) AND
156 (old_references.start_dai_sequence_number = new_references.start_dai_sequence_number) AND
157 (old_references.grd_cal_type = new_references.grd_cal_type) AND
158 (old_references.grd_ci_sequence_number = new_references.grd_ci_sequence_number)) OR
159 ((new_references.start_dt_alias IS NULL) OR
160 (new_references.start_dai_sequence_number IS NULL) OR
161 (new_references.grd_cal_type IS NULL) OR
162 (new_references.grd_ci_sequence_number IS NULL))) THEN
163 NULL;
164 ELSE
165 IF NOT IGS_CA_DA_INST_PKG.Get_PK_For_Validation (
166 new_references.start_dt_alias,
167 new_references.start_dai_sequence_number,
168 new_references.grd_cal_type,
169 new_references.grd_ci_sequence_number
170 ) THEN
171 FND_MESSAGE.SET_NAME ('FND', 'FORM_RECORD_DELETED');
172 IGS_GE_MSG_STACK.ADD;
173 APP_EXCEPTION.RAISE_EXCEPTION;
174 END IF;
175
176 END IF;
177
178 END Check_Parent_Existance;
179
180 PROCEDURE CHECK_CONSTRAINTS(
181 Column_Name IN VARCHAR2 DEFAULT NULL,
182 Column_Value IN VARCHAR2 DEFAULT NULL
183 ) AS
184 BEGIN
185 IF Column_Name is null THEN
186 NULL;
187
188 ELSIF upper(Column_name) = 'START_DAI_SEQUENCE_NUMBER' THEN
189 new_references.START_DAI_SEQUENCE_NUMBER:= IGS_GE_NUMBER.to_num(COLUMN_VALUE);
190
191 ELSIF upper(Column_name) = 'END_DAI_SEQUENCE_NUMBER' THEN
192 new_references.END_DAI_SEQUENCE_NUMBER:= IGS_GE_NUMBER.to_num(COLUMN_VALUE);
193
194 ELSIF upper(Column_name) = 'GRD_CI_SEQUENCE_NUMBER' THEN
195 new_references.GRD_CI_SEQUENCE_NUMBER:= IGS_GE_NUMBER.to_num(COLUMN_VALUE);
196
197 ELSIF upper(Column_name) = 'END_DT_ALIAS' THEN
198 new_references.END_DT_ALIAS:= COLUMN_VALUE ;
199
200 ELSIF upper(Column_name) = 'GRD_CAL_TYPE' THEN
201 new_references.GRD_CAL_TYPE:= COLUMN_VALUE ;
202
203 ELSIF upper(Column_name) = 'START_DT_ALIAS' THEN
204 new_references.START_DT_ALIAS:= COLUMN_VALUE ;
205
206 END IF ;
207
208 IF upper(Column_name) = 'START_DAI_SEQUENCE_NUMBER' OR COLUMN_NAME IS NULL THEN
209 IF new_references.START_DAI_SEQUENCE_NUMBER < 1 OR new_references.START_DAI_SEQUENCE_NUMBER > 999999 then
210 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
211 IGS_GE_MSG_STACK.ADD;
212 App_Exception.Raise_Exception ;
213 END IF;
214
215 END IF ;
216
217 IF upper(Column_name) = 'END_DAI_SEQUENCE_NUMBER' OR COLUMN_NAME IS NULL THEN
218 IF new_references.END_DAI_SEQUENCE_NUMBER < 1 OR new_references.END_DAI_SEQUENCE_NUMBER > 999999 then
219 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
220 IGS_GE_MSG_STACK.ADD;
221 App_Exception.Raise_Exception ;
222 END IF;
223
224 END IF ;
225
226 IF upper(Column_name) = 'GRD_CI_SEQUENCE_NUMBER' OR COLUMN_NAME IS NULL THEN
227 IF new_references.GRD_CI_SEQUENCE_NUMBER < 1 OR new_references.GRD_CI_SEQUENCE_NUMBER > 999999 then
228 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
229 IGS_GE_MSG_STACK.ADD;
230 App_Exception.Raise_Exception ;
231 END IF;
232
233 END IF ;
234
235 IF upper(Column_name) = 'END_DT_ALIAS' OR COLUMN_NAME IS NULL THEN
236 IF new_references.END_DT_ALIAS<> upper(NEW_REFERENCES.END_DT_ALIAS) then
237 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
238 IGS_GE_MSG_STACK.ADD;
239 App_Exception.Raise_Exception ;
240 END IF;
241
242 END IF ;
243
244 IF upper(Column_name) = 'GRD_CAL_TYPE' OR COLUMN_NAME IS NULL THEN
245 IF new_references.GRD_CAL_TYPE<> upper(NEW_REFERENCES.GRD_CAL_TYPE) then
246 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
247 IGS_GE_MSG_STACK.ADD;
248 App_Exception.Raise_Exception ;
249 END IF;
250
251 END IF ;
252
253 IF upper(Column_name) = 'START_DT_ALIAS' OR COLUMN_NAME IS NULL THEN
254 IF new_references.START_DT_ALIAS<> upper(NEW_REFERENCES.START_DT_ALIAS) then
255 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
256 IGS_GE_MSG_STACK.ADD;
257 App_Exception.Raise_Exception ;
258 END IF;
259
260 END IF ;
261
262 END;
263
264 PROCEDURE Check_Child_Existance AS
265 BEGIN
266
267 IGS_GR_CRM_ROUND_PRD_PKG.GET_FK_IGS_GR_CRMN_ROUND (
268 old_references.grd_cal_type,
269 old_references.grd_ci_sequence_number
270 );
271
272 IGS_GR_GRADUAND_PKG.GET_FK_IGS_GR_CRMN_ROUND (
273 old_references.grd_cal_type,
274 old_references.grd_ci_sequence_number
275 );
276
277 IGS_GR_AWD_CRMN_PKG.GET_FK_IGS_GR_CRMN_ROUND (
278 old_references.grd_cal_type,
279 old_references.grd_ci_sequence_number
280 );
281
282 IGS_GR_CRMN_PKG.GET_FK_IGS_GR_CRMN_ROUND (
283 old_references.grd_cal_type,
284 old_references.grd_ci_sequence_number
285 );
286
287 END Check_Child_Existance;
288
289 FUNCTION Get_PK_For_Validation (
290 x_grd_cal_type IN VARCHAR2,
291 x_grd_ci_sequence_number IN NUMBER
292 ) RETURN BOOLEAN AS
293
294 CURSOR cur_rowid IS
295 SELECT rowid
296 FROM IGS_GR_CRMN_ROUND_ALL
297 WHERE grd_cal_type = x_grd_cal_type
298 AND grd_ci_sequence_number = x_grd_ci_sequence_number ;
299
300 lv_rowid cur_rowid%RowType;
301
302 BEGIN
303
304 Open cur_rowid;
305 Fetch cur_rowid INTO lv_rowid;
306 IF (cur_rowid%FOUND) THEN
307 Close cur_rowid;
308 Return (TRUE);
309 ELSE
310 Close cur_rowid;
311 Return (FALSE);
312 END IF;
313 END Get_PK_For_Validation;
314
315 PROCEDURE GET_FK_IGS_CA_INST (
316 x_cal_type IN VARCHAR2,
317 x_sequence_number IN NUMBER
318 ) AS
319
320 CURSOR cur_rowid IS
321 SELECT rowid
322 FROM IGS_GR_CRMN_ROUND_ALL
323 WHERE grd_cal_type = x_cal_type
324 AND grd_ci_sequence_number = x_sequence_number ;
325
326 lv_rowid cur_rowid%RowType;
327
328 BEGIN
329
330 Open cur_rowid;
331 Fetch cur_rowid INTO lv_rowid;
332 IF (cur_rowid%FOUND) THEN
333 Close cur_rowid;
334 Fnd_Message.Set_Name ('IGS', 'IGS_GR_CRD_CI_FK');
335 IGS_GE_MSG_STACK.ADD;
336 App_Exception.Raise_Exception;
337 Return;
338 END IF;
339 Close cur_rowid;
340
341 END GET_FK_IGS_CA_INST;
342
343 PROCEDURE GET_FK_IGS_CA_DA_INST (
344 x_dt_alias IN VARCHAR2,
345 x_sequence_number IN NUMBER,
346 x_cal_type IN VARCHAR2,
347 x_ci_sequence_number IN NUMBER
348 ) AS
349
350 CURSOR cur_rowid IS
351 SELECT rowid
352 FROM IGS_GR_CRMN_ROUND_ALL
353 WHERE (end_dt_alias = x_dt_alias
354 AND end_dai_sequence_number = x_sequence_number
355 AND grd_cal_type = x_cal_type
356 AND grd_ci_sequence_number = x_ci_sequence_number)
357 OR (start_dt_alias = x_dt_alias
358 AND start_dai_sequence_number = x_sequence_number
359 AND grd_cal_type = x_cal_type
360 AND grd_ci_sequence_number = x_ci_sequence_number) ;
361 lv_rowid cur_rowid%RowType;
362
363 BEGIN
364
365 Open cur_rowid;
366 Fetch cur_rowid INTO lv_rowid;
367 IF (cur_rowid%FOUND) THEN
368 Close cur_rowid;
369 Fnd_Message.Set_Name ('IGS', 'IGS_GR_CRD_END_DAIV_FK');
370 IGS_GE_MSG_STACK.ADD;
371 App_Exception.Raise_Exception;
372 Return;
373 END IF;
374 Close cur_rowid;
375
376 END GET_FK_IGS_CA_DA_INST;
377
378 PROCEDURE Before_DML (
379 p_action IN VARCHAR2,
380 x_rowid IN VARCHAR2 DEFAULT NULL,
381 x_grd_cal_type IN VARCHAR2 DEFAULT NULL,
382 x_grd_ci_sequence_number IN NUMBER DEFAULT NULL,
383 x_start_dt_alias IN VARCHAR2 DEFAULT NULL,
384 x_start_dai_sequence_number IN NUMBER DEFAULT NULL,
385 x_end_dt_alias IN VARCHAR2 DEFAULT NULL,
386 x_end_dai_sequence_number IN NUMBER DEFAULT NULL,
387 x_creation_date IN DATE DEFAULT NULL,
388 x_created_by IN NUMBER DEFAULT NULL,
389 x_last_update_date IN DATE DEFAULT NULL,
390 x_last_updated_by IN NUMBER DEFAULT NULL,
391 x_last_update_login IN NUMBER DEFAULT NULL,
392 x_org_id IN NUMBER DEFAULT NULL,
393 x_conferral_start_date IN DATE DEFAULT NULL,
394 x_conferral_end_date IN DATE DEFAULT NULL,
395 x_completion_start_date IN DATE DEFAULT NULL,
396 x_completion_end_date IN DATE DEFAULT NULL
397 ) AS
398 BEGIN
399
400 Set_Column_Values (
401 p_action,
402 x_rowid,
403 x_grd_cal_type,
404 x_grd_ci_sequence_number,
405 x_start_dt_alias,
406 x_start_dai_sequence_number,
407 x_end_dt_alias,
408 x_end_dai_sequence_number,
409 x_creation_date,
410 x_created_by,
411 x_last_update_date,
412 x_last_updated_by,
413 x_last_update_login,
414 x_org_id,
415 x_conferral_start_date ,
416 x_conferral_end_date ,
417 x_completion_start_date,
418 x_completion_end_date );
419
420 IF (p_action = 'INSERT') THEN
421 -- Call all the procedures related to Before Insert.
422 BeforeRowInsertUpdate1 ( p_inserting => TRUE );
423 IF Get_PK_For_Validation (
424 NEW_REFERENCES.grd_cal_type,
425 NEW_REFERENCES.grd_ci_sequence_number
426 ) THEN
427 Fnd_Message.Set_Name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
428 IGS_GE_MSG_STACK.ADD;
429 App_Exception.Raise_Exception;
430 END IF;
431
432 Check_Constraints;
433 Check_Parent_Existance;
434 ELSIF (p_action = 'UPDATE') THEN
435 -- Call all the procedures related to Before Update.
436 BeforeRowInsertUpdate1 ( p_updating => TRUE );
437
438 Check_Constraints;
439 Check_Parent_Existance;
440 ELSIF (p_action = 'DELETE') THEN
441 -- Call all the procedures related to Before Delete.
442 Check_Child_Existance;
443 ELSIF (p_action = 'VALIDATE_INSERT') THEN
444 IF GET_PK_FOR_VALIDATION(
445 NEW_REFERENCES.grd_cal_type,
446 NEW_REFERENCES.grd_ci_sequence_number
447 ) THEN
448 Fnd_Message.Set_Name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
449 IGS_GE_MSG_STACK.ADD;
450 App_Exception.Raise_Exception;
451 END IF;
452
453 check_constraints;
454 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
455
456 check_constraints;
457 END IF;
458
459 END Before_DML;
460
461 procedure INSERT_ROW (
462 X_ROWID in out NOCOPY VARCHAR2,
463 X_GRD_CAL_TYPE in VARCHAR2,
464 X_GRD_CI_SEQUENCE_NUMBER in NUMBER,
465 X_START_DT_ALIAS in VARCHAR2,
466 X_START_DAI_SEQUENCE_NUMBER in NUMBER,
467 X_END_DT_ALIAS in VARCHAR2,
468 X_END_DAI_SEQUENCE_NUMBER in NUMBER,
469 X_MODE in VARCHAR2 default 'R',
470 X_ORG_ID IN NUMBER,
471 X_CONFERRAL_START_DATE IN DATE DEFAULT NULL,
472 X_CONFERRAL_END_DATE IN DATE DEFAULT NULL,
473 X_COMPLETION_START_DATE IN DATE DEFAULT NULL,
474 X_COMPLETION_END_DATE IN DATE DEFAULT NULL
475 ) AS
476 cursor C is select ROWID from IGS_GR_CRMN_ROUND_ALL
477 where GRD_CAL_TYPE = X_GRD_CAL_TYPE
478 and GRD_CI_SEQUENCE_NUMBER = X_GRD_CI_SEQUENCE_NUMBER;
479 X_LAST_UPDATE_DATE DATE;
480 X_LAST_UPDATED_BY NUMBER;
481 X_LAST_UPDATE_LOGIN NUMBER;
482 begin
483 X_LAST_UPDATE_DATE := SYSDATE;
484 if(X_MODE = 'I') then
485 X_LAST_UPDATED_BY := 1;
486 X_LAST_UPDATE_LOGIN := 0;
487 elsif (X_MODE = 'R') then
488 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
489 if X_LAST_UPDATED_BY is NULL then
490 X_LAST_UPDATED_BY := -1;
491 end if;
492 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
493 if X_LAST_UPDATE_LOGIN is NULL then
494 X_LAST_UPDATE_LOGIN := -1;
495 end if;
496 else
497 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
498 IGS_GE_MSG_STACK.ADD;
499 app_exception.raise_exception;
500 end if;
501
502 Before_DML (
503 p_action => 'INSERT',
504 x_rowid => X_ROWID,
505 x_grd_cal_type => X_GRD_CAL_TYPE,
506 x_grd_ci_sequence_number => X_GRD_CI_SEQUENCE_NUMBER,
507 x_start_dt_alias => X_START_DT_ALIAS,
508 x_start_dai_sequence_number => X_START_DAI_SEQUENCE_NUMBER,
509 x_end_dt_alias => X_END_DT_ALIAS,
510 x_end_dai_sequence_number => X_END_DAI_SEQUENCE_NUMBER,
511 x_creation_date => X_LAST_UPDATE_DATE,
512 x_created_by => X_LAST_UPDATED_BY,
513 x_last_update_date => X_LAST_UPDATE_DATE,
514 x_last_updated_by => X_LAST_UPDATED_BY,
515 x_last_update_login => X_LAST_UPDATE_LOGIN,
516 x_org_id => igs_ge_gen_003.get_org_id,
517 x_conferral_start_date => X_CONFERRAL_START_DATE ,
518 x_conferral_end_date => X_CONFERRAL_END_DATE ,
519 x_completion_start_date => X_COMPLETION_START_DATE,
520 x_completion_end_date => X_COMPLETION_END_DATE
521 );
522
523 insert into IGS_GR_CRMN_ROUND_ALL (
524 GRD_CAL_TYPE,
525 GRD_CI_SEQUENCE_NUMBER,
526 START_DT_ALIAS,
527 START_DAI_SEQUENCE_NUMBER,
528 END_DT_ALIAS,
529 END_DAI_SEQUENCE_NUMBER,
530 CREATION_DATE,
531 CREATED_BY,
532 LAST_UPDATE_DATE,
533 LAST_UPDATED_BY,
534 LAST_UPDATE_LOGIN,
535 ORG_ID,
536 CONFERRAL_START_DATE ,
537 CONFERRAL_END_DATE ,
538 COMPLETION_START_DATE,
539 COMPLETION_END_DATE
540 ) values (
541 NEW_REFERENCES.GRD_CAL_TYPE,
542 NEW_REFERENCES.GRD_CI_SEQUENCE_NUMBER,
543 NEW_REFERENCES.START_DT_ALIAS,
544 NEW_REFERENCES.START_DAI_SEQUENCE_NUMBER,
545 NEW_REFERENCES.END_DT_ALIAS,
546 NEW_REFERENCES.END_DAI_SEQUENCE_NUMBER,
547 X_LAST_UPDATE_DATE,
548 X_LAST_UPDATED_BY,
549 X_LAST_UPDATE_DATE,
550 X_LAST_UPDATED_BY,
551 X_LAST_UPDATE_LOGIN,
552 NEW_REFERENCES.ORG_ID,
553 NEW_REFERENCES.CONFERRAL_START_DATE ,
554 NEW_REFERENCES.CONFERRAL_END_DATE ,
555 NEW_REFERENCES.COMPLETION_START_DATE,
556 NEW_REFERENCES.COMPLETION_END_DATE
557 );
558
559 open c;
560 fetch c into X_ROWID;
561 if (c%notfound) then
562 close c;
563 raise no_data_found;
564 end if;
565 close c;
566
567 end INSERT_ROW;
568
569 procedure LOCK_ROW (
570 X_ROWID in VARCHAR2,
571 X_GRD_CAL_TYPE in VARCHAR2,
572 X_GRD_CI_SEQUENCE_NUMBER in NUMBER,
573 X_START_DT_ALIAS in VARCHAR2,
574 X_START_DAI_SEQUENCE_NUMBER in NUMBER,
575 X_END_DT_ALIAS in VARCHAR2,
576 X_END_DAI_SEQUENCE_NUMBER in NUMBER,
577 X_CONFERRAL_START_DATE IN DATE,
578 X_CONFERRAL_END_DATE IN DATE,
579 X_COMPLETION_START_DATE IN DATE,
580 X_COMPLETION_END_DATE IN DATE
581 ) AS
582 cursor c1 is select
583 START_DT_ALIAS,
584 START_DAI_SEQUENCE_NUMBER,
585 END_DT_ALIAS,
586 END_DAI_SEQUENCE_NUMBER
587 from IGS_GR_CRMN_ROUND_ALL
588 where ROWID = X_ROWID for update nowait;
589 tlinfo c1%rowtype;
590
591 begin
592 open c1;
593 fetch c1 into tlinfo;
594 if (c1%notfound) then
595 close c1;
596 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
597 app_exception.raise_exception;
598 return;
599 end if;
600 close c1;
601
602 if ( (tlinfo.START_DT_ALIAS = X_START_DT_ALIAS)
603 AND (tlinfo.START_DAI_SEQUENCE_NUMBER = X_START_DAI_SEQUENCE_NUMBER)
604 AND (tlinfo.END_DT_ALIAS = X_END_DT_ALIAS)
605 AND (tlinfo.END_DAI_SEQUENCE_NUMBER = X_END_DAI_SEQUENCE_NUMBER)
606 ) then
607 null;
608 else
609 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
610 app_exception.raise_exception;
611 end if;
612 return;
613 end LOCK_ROW;
614
615 procedure UPDATE_ROW (
616 X_ROWID in VARCHAR2,
617 X_GRD_CAL_TYPE in VARCHAR2,
618 X_GRD_CI_SEQUENCE_NUMBER in NUMBER,
619 X_START_DT_ALIAS in VARCHAR2,
620 X_START_DAI_SEQUENCE_NUMBER in NUMBER,
621 X_END_DT_ALIAS in VARCHAR2,
622 X_END_DAI_SEQUENCE_NUMBER in NUMBER,
623 X_MODE IN VARCHAR2 default 'R',
624 X_CONFERRAL_START_DATE IN DATE DEFAULT NULL,
625 X_CONFERRAL_END_DATE IN DATE DEFAULT NULL,
626 X_COMPLETION_START_DATE IN DATE DEFAULT NULL,
627 X_COMPLETION_END_DATE IN DATE DEFAULT NULL
628 ) AS
629 X_LAST_UPDATE_DATE DATE;
630 X_LAST_UPDATED_BY NUMBER;
631 X_LAST_UPDATE_LOGIN NUMBER;
632 begin
633 X_LAST_UPDATE_DATE := SYSDATE;
634 if(X_MODE = 'I') then
635 X_LAST_UPDATED_BY := 1;
636 X_LAST_UPDATE_LOGIN := 0;
637 elsif (X_MODE = 'R') then
638 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
639 if X_LAST_UPDATED_BY is NULL then
640 X_LAST_UPDATED_BY := -1;
641 end if;
642 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
643 if X_LAST_UPDATE_LOGIN is NULL then
644 X_LAST_UPDATE_LOGIN := -1;
645 end if;
646 else
647 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
648 IGS_GE_MSG_STACK.ADD;
649 app_exception.raise_exception;
650 end if;
651
652 Before_DML (
653 p_action => 'UPDATE',
654 x_rowid => X_ROWID,
655 x_grd_cal_type => X_GRD_CAL_TYPE,
656 x_grd_ci_sequence_number => X_GRD_CI_SEQUENCE_NUMBER,
657 x_start_dt_alias => X_START_DT_ALIAS,
658 x_start_dai_sequence_number => X_START_DAI_SEQUENCE_NUMBER,
659 x_end_dt_alias => X_END_DT_ALIAS,
660 x_end_dai_sequence_number => X_END_DAI_SEQUENCE_NUMBER,
661 x_creation_date => X_LAST_UPDATE_DATE,
662 x_created_by => X_LAST_UPDATED_BY,
663 x_last_update_date => X_LAST_UPDATE_DATE,
664 x_last_updated_by => X_LAST_UPDATED_BY,
665 x_conferral_start_date => X_CONFERRAL_START_DATE ,
666 x_conferral_end_date => X_CONFERRAL_END_DATE ,
667 x_completion_start_date => X_COMPLETION_START_DATE,
668 x_completion_end_date => X_COMPLETION_END_DATE
669 );
670
671 update IGS_GR_CRMN_ROUND_ALL set
672 START_DT_ALIAS = NEW_REFERENCES.START_DT_ALIAS,
673 START_DAI_SEQUENCE_NUMBER = NEW_REFERENCES.START_DAI_SEQUENCE_NUMBER,
674 END_DT_ALIAS = NEW_REFERENCES.END_DT_ALIAS,
675 END_DAI_SEQUENCE_NUMBER = NEW_REFERENCES.END_DAI_SEQUENCE_NUMBER,
676 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
677 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
678 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
679 CONFERRAL_START_DATE = X_CONFERRAL_START_DATE ,
680 CONFERRAL_END_DATE = X_CONFERRAL_END_DATE ,
681 COMPLETION_START_DATE= X_COMPLETION_START_DATE,
682 COMPLETION_END_DATE = X_COMPLETION_END_DATE
683 where ROWID = X_ROWID
684 ;
685 if (sql%notfound) then
686 raise no_data_found;
687 end if;
688
689 end UPDATE_ROW;
690
691 procedure ADD_ROW (
692 X_ROWID in out NOCOPY VARCHAR2,
693 X_GRD_CAL_TYPE in VARCHAR2,
694 X_GRD_CI_SEQUENCE_NUMBER in NUMBER,
695 X_START_DT_ALIAS in VARCHAR2,
696 X_START_DAI_SEQUENCE_NUMBER in NUMBER,
697 X_END_DT_ALIAS in VARCHAR2,
698 X_END_DAI_SEQUENCE_NUMBER in NUMBER,
699 X_MODE in VARCHAR2 default 'R',
700 X_ORG_ID IN NUMBER,
701 X_CONFERRAL_START_DATE IN DATE,
702 X_CONFERRAL_END_DATE IN DATE,
703 X_COMPLETION_START_DATE IN DATE,
704 X_COMPLETION_END_DATE IN DATE
705 ) AS
706 cursor c1 is select rowid from IGS_GR_CRMN_ROUND_ALL
707 where GRD_CAL_TYPE = X_GRD_CAL_TYPE
708 and GRD_CI_SEQUENCE_NUMBER = X_GRD_CI_SEQUENCE_NUMBER
709 ;
710
711 begin
712 open c1;
713 fetch c1 into X_ROWID;
714 if (c1%notfound) then
715 close c1;
716 INSERT_ROW (
717 X_ROWID,
718 X_GRD_CAL_TYPE,
719 X_GRD_CI_SEQUENCE_NUMBER,
720 X_START_DT_ALIAS,
721 X_START_DAI_SEQUENCE_NUMBER,
722 X_END_DT_ALIAS,
723 X_END_DAI_SEQUENCE_NUMBER,
724 X_MODE,
725 x_org_id,
726 X_CONFERRAL_START_DATE ,
727 X_CONFERRAL_END_DATE ,
728 X_COMPLETION_START_DATE,
729 X_COMPLETION_END_DATE
730 );
731 return;
732 end if;
733 close c1;
734 UPDATE_ROW (
735 X_ROWID,
736 X_GRD_CAL_TYPE,
737 X_GRD_CI_SEQUENCE_NUMBER,
738 X_START_DT_ALIAS,
739 X_START_DAI_SEQUENCE_NUMBER,
740 X_END_DT_ALIAS,
741 X_END_DAI_SEQUENCE_NUMBER,
742 X_MODE,
743 X_CONFERRAL_START_DATE ,
744 X_CONFERRAL_END_DATE ,
745 X_COMPLETION_START_DATE,
746 X_COMPLETION_END_DATE
747 );
748 end ADD_ROW;
749
750 end IGS_GR_CRMN_ROUND_PKG;