[Home] [Help]
PACKAGE BODY: APPS.IGS_CA_DA_INST_PKG
Source
1 package body IGS_CA_DA_INST_PKG AS
2 /* $Header: IGSCI04B.pls 120.0 2005/06/02 03:39:05 appldev noship $ */
3 l_rowid VARCHAR2(25);
4 old_references IGS_CA_DA_INST%RowType;
5 new_references IGS_CA_DA_INST%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2,
10 x_dt_alias IN VARCHAR2,
11 x_sequence_number IN NUMBER,
12 x_cal_type IN VARCHAR2,
13 x_ci_sequence_number IN NUMBER,
14 x_absolute_val IN DATE,
15 x_creation_date IN DATE,
16 x_created_by IN NUMBER,
17 x_last_update_date IN DATE,
18 x_last_updated_by IN NUMBER,
19 x_last_update_login IN NUMBER
20 ) AS
21 /*----------------------------------------------------------------------------
22 || Created By :
23 || Created On :
24 || Purpose :
25 || Known limitations, enhancements or remarks :
26 || Change History :
27 || Who When What
28 || (reverse chronological order - newest change first)
29 || vvutukur 02-Sep-2002 Bug#2531390.Removed default values for parameters to avoid gscc
30 || warnings.
31 ----------------------------------------------------------------------------*/
32
33 CURSOR cur_old_ref_values IS
34 SELECT *
35 FROM IGS_CA_DA_INST
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.dt_alias := x_dt_alias;
57 new_references.sequence_number := x_sequence_number;
58 new_references.cal_type := x_cal_type;
59 new_references.ci_sequence_number := x_ci_sequence_number;
60 new_references.absolute_val := x_absolute_val;
61 IF (p_action = 'UPDATE') THEN
62 new_references.creation_date := old_references.creation_date;
63 new_references.created_by := old_references.created_by;
64 ELSE
65 new_references.creation_date := x_creation_date;
66 new_references.created_by := x_created_by;
67 END IF;
68 new_references.last_update_date := x_last_update_date;
69 new_references.last_updated_by := x_last_updated_by;
70 new_references.last_update_login := x_last_update_login;
71
72 END Set_Column_Values;
73
74 -- Trigger description :-
75 -- "OSS_TST".trg_dai_br_iud
76 -- BEFORE INSERT OR DELETE OR UPDATE
77 -- ON IGS_CA_DA_INST
78 -- FOR EACH ROW
79
80 PROCEDURE BeforeRowInsertUpdateDelete1(
81 p_inserting IN BOOLEAN,
82 p_updating IN BOOLEAN,
83 p_deleting IN BOOLEAN
84 ) AS
85 /*----------------------------------------------------------------------------
86 || Created By :
87 || Created On :
88 || Purpose :
89 || Known limitations, enhancements or remarks :
90 || Change History :
91 || Who When What
92 || (reverse chronological order - newest change first)
93 || vvutukur 02-Sep-2002 Bug#2531390.Removed default values of parameters to avoid gscc
94 || warnings.
95 ----------------------------------------------------------------------------*/
96
97 v_message_name varchar2(30);
98 BEGIN
99
100 IF p_inserting OR p_updating THEN
101 -- Absolute value must be entered if category is 'HOLIDAY'
102 IF new_references.absolute_val IS NULL THEN
103 IF IGS_CA_VAL_DAI.calp_val_holiday_cat (new_references.cal_type,
104 v_message_name) = TRUE
105 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 END IF;
112 END BeforeRowInsertUpdateDelete1;
113
114 -- Trigger description :-
115 -- "OSS_TST".trg_dai_as_iud
116 -- AFTER INSERT OR DELETE OR UPDATE
117 -- ON IGS_CA_DA_INST
118
119 PROCEDURE AfterStmtInsertUpdateDelete3(
120 p_inserting IN BOOLEAN,
121 p_updating IN BOOLEAN,
122 p_deleting IN BOOLEAN
123 ) AS
124 /*----------------------------------------------------------------------------
125 || Created By :
126 || Created On :
127 || Purpose :
128 || Known limitations, enhancements or remarks :
129 || Change History :
130 || Who When What
131 || (reverse chronological order - newest change first)
132 || vvutukur 02-Sep-2002 Bug#2531390.Removed default values of the parameters to avoid gscc
133 || warnings.
134 ----------------------------------------------------------------------------*/
135
136 v_message_name varchar2(30);
137 BEGIN
138 -- Validation routine calls.
139 IF p_inserting OR p_updating
140 THEN
141 -- Validate date alias instance
142 IF IGS_CA_VAL_DAI.calp_val_dai_upd(NVL (new_references.dt_alias, old_references.dt_alias),
143 NVL (new_references.sequence_number, old_references.sequence_number),
144 NVL (new_references.cal_type, old_references.cal_type),
145 NVL (new_references.ci_sequence_number, old_references.ci_sequence_number),
146 v_message_name) = FALSE
147 THEN
148 Fnd_Message.Set_Name('IGS',v_message_name);
149 IGS_GE_MSG_STACK.ADD;
150 APP_EXCEPTION.RAISE_EXCEPTION;
151 END IF;
152 IF p_inserting
153 THEN
154 -- Validate date alias instance date alias
155 IF IGS_CA_VAL_DAI.calp_val_dai_da(NVL (new_references.dt_alias, old_references.dt_alias),
156 NVL (new_references.cal_type, old_references.cal_type),
157 v_message_name) = FALSE
158 THEN
159 Fnd_Message.Set_Name('IGS',v_message_name);
160 IGS_GE_MSG_STACK.ADD;
161 APP_EXCEPTION.RAISE_EXCEPTION;
162 END IF;
163 END IF;
164 END IF;
165 END AfterStmtInsertUpdateDelete3;
166
167 PROCEDURE Check_Constraints (
168 Column_Name IN VARCHAR2,
169 Column_Value IN VARCHAR2
170 )
171 IS
172 BEGIN
173 IF column_name is null then
174 NULL;
175 Elsif UPPER(column_name) = 'CAL_TYPE ' Then
176 new_references.cal_type := column_value;
177
178 Elsif UPPER(column_name) = 'DT_ALIAS' Then
179 new_references.dt_alias := column_value;
180 end if;
181 if upper(column_name) = 'CAL_TYPE' or column_name is null Then
182 if new_references.cal_type <> UPPER( new_references.cal_type) 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) = 'DT_ALIAS' or column_name is null Then
189 if new_references.dt_alias <> UPPER( new_references.dt_alias) then
190 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
191 IGS_GE_MSG_STACK.ADD;
192 App_Exception.Raise_Exception;
193 end if;
194 end if;
195
196 END Check_Constraints ;
197
198
199 PROCEDURE Check_Parent_Existance AS
200 BEGIN
201
202 IF (((old_references.cal_type = new_references.cal_type) AND
203 (old_references.ci_sequence_number = new_references.ci_sequence_number)) OR
204 ((new_references.cal_type IS NULL) OR
205 (new_references.ci_sequence_number IS NULL))) THEN
206 NULL;
207 ELSE
208 IF NOT IGS_CA_INST_PKG.Get_PK_For_Validation (
209 new_references.cal_type,
210 new_references.ci_sequence_number
211 ) THEN
212 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
213 IGS_GE_MSG_STACK.ADD;
214 App_Exception.Raise_Exception;
215 END IF;
216 END IF;
217 IF (((old_references.dt_alias = new_references.dt_alias)) OR
218 ((new_references.dt_alias IS NULL))) THEN
219 NULL;
220 ELSE
221 IF NOT IGS_CA_DA_PKG.Get_PK_For_Validation (
222 new_references.dt_alias
223 ) THEN
224 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
225 IGS_GE_MSG_STACK.ADD;
226 App_Exception.Raise_Exception;
227 END IF;
228 END IF;
229
230 END Check_Parent_Existance;
231
232 PROCEDURE check_Child_Existance
233 /*----------------------------------------------------------------------------
234 || Created By :
235 || Created On :
236 || Purpose :
237 || Known limitations, enhancements or remarks :
238 || Change History :
239 || Who When What
240 || (reverse chronological order - newest change first)
241 || rmaddipa 07-Sep-2004 Enh#3316063 Added call to IGS_FI_TP_RET_SCHD_PKG.GET_FK_IGS_CA_DA_INST
242 || vvutukur 26-Aug-2002 Bug#2531390.Removed call to IGS_FI_FEE_PAY_SCHD_PKG.GET_FK_IGS_CA_DA_INST
243 || to remove the foreign key relationship between tables igs_ca_da_inst
244 || and igs_fi_fee_pay_schd.
245 ----------------------------------------------------------------------------*/
246 AS
247 BEGIN
248 IGS_AD_PECRS_OFOP_DT_PKG.GET_FK_IGS_CA_DA_INST (
249 old_references.dt_alias,
250 old_references.sequence_number,
251 old_references.cal_type,
252 old_references.ci_sequence_number
253 );
254
255 IGS_GR_CRMN_ROUND_PKG.GET_FK_IGS_CA_DA_INST (
256 old_references.dt_alias,
257 old_references.sequence_number,
258 old_references.cal_type,
259 old_references.ci_sequence_number
260 );
261
262 IGS_CA_DA_INST_OFST_PKG.GET_FK_IGS_CA_DA_INST (
263 old_references.dt_alias,
264 old_references.sequence_number,
265 old_references.cal_type,
266 old_references.ci_sequence_number
267 );
268
269
270 IGS_CA_DA_INST_PAIR_PKG.GET_FK_IGS_CA_DA_INST (
271 old_references.dt_alias,
272 old_references.sequence_number,
273 old_references.cal_type,
274 old_references.ci_sequence_number
275 );
276
277
278 IGS_AS_EXAM_SESSION_PKG.GET_FK_IGS_CA_DA_INST (
279 old_references.dt_alias,
280 old_references.sequence_number,
281 old_references.cal_type,
282 old_references.ci_sequence_number
283 );
284
285 IGS_FI_F_CAT_CA_INST_PKG.GET_FK_IGS_CA_DA_INST (
286 old_references.dt_alias,
287 old_references.sequence_number,
288 old_references.cal_type,
289 old_references.ci_sequence_number
290 );
291
292 IGS_FI_F_CAT_FEE_LBL_PKG.GET_FK_IGS_CA_DA_INST (
293 old_references.dt_alias,
294 old_references.sequence_number,
295 old_references.cal_type,
296 old_references.ci_sequence_number
297 );
298
299 /* Removed call to IGS_FI_FEE_ENCMB_PKG.GET_FK_IGS_CA_DA_INST since the TBH is obseleted as part of
300 bug 2126091-sykrishn */
301
302 --Removed call to IGS_FI_FEE_PAY_SCHD_PKG.GET_FK_IGS_CA_DA_INST as part of SFCR005 CleanUp.Bug#2531390.
303
304 IGS_FI_FEE_RET_SCHD_PKG.GET_FK_IGS_CA_DA_INST (
305 old_references.dt_alias,
306 old_references.sequence_number,
307 old_references.cal_type,
308 old_references.ci_sequence_number
309 );
310
311 IGS_FI_F_TYP_CA_INST_PKG.GET_FK_IGS_CA_DA_INST (
312 old_references.dt_alias,
313 old_references.sequence_number,
314 old_references.cal_type,
315 old_references.ci_sequence_number
316 );
317
318 IGS_GR_CRMN_PKG.GET_FK_IGS_CA_DA_INST (
319 old_references.dt_alias,
320 old_references.sequence_number,
321 old_references.cal_type,
322 old_references.ci_sequence_number
323 );
324 IGS_FI_TP_RET_SCHD_PKG.GET_FK_IGS_CA_DA_INST(
325 x_dt_alias => old_references.dt_alias,
326 x_dai_sequence_number => old_references.sequence_number,
327 x_teach_cal_type => old_references.cal_type,
328 x_teach_ci_sequence_number => old_references.ci_sequence_number
329 );
330 END Check_Child_Existance;
331
332 FUNCTION Get_PK_For_Validation (
333 x_dt_alias IN VARCHAR2,
334 x_sequence_number IN NUMBER,
335 x_cal_type IN VARCHAR2,
336 x_ci_sequence_number IN NUMBER
337 )RETURN BOOLEAN AS
338
339 CURSOR cur_rowid IS
340 SELECT rowid
341 FROM IGS_CA_DA_INST
342 WHERE dt_alias = x_dt_alias
343 AND sequence_number = x_sequence_number
344 AND cal_type = x_cal_type
345 AND ci_sequence_number = x_ci_sequence_number
346 FOR UPDATE NOWAIT;
347
348 lv_rowid cur_rowid%RowType;
349
350 BEGIN
351
352 Open cur_rowid;
353 Fetch cur_rowid INTO lv_rowid;
354 IF (cur_rowid%FOUND) THEN
355 Close cur_rowid;
356 Return (TRUE);
357 ELSE
358 Close cur_rowid;
359 Return (FALSE);
360 END IF;
361
362 END Get_PK_For_Validation;
363
364 PROCEDURE GET_FK_IGS_CA_INST (
365 x_cal_type IN VARCHAR2,
366 x_sequence_number IN NUMBER
367 ) AS
368
369 CURSOR cur_rowid IS
370 SELECT rowid
371 FROM IGS_CA_DA_INST
372 WHERE cal_type = x_cal_type
373 AND ci_sequence_number = x_sequence_number ;
374
375 lv_rowid cur_rowid%RowType;
376
377 BEGIN
378
379 Open cur_rowid;
380 Fetch cur_rowid INTO lv_rowid;
381 IF (cur_rowid%FOUND) THEN
382 Close cur_rowid;
383 Fnd_Message.Set_Name ('IGS', 'IGS_CA_DAI_CI_FK');
384 IGS_GE_MSG_STACK.ADD;
385 App_Exception.Raise_Exception;
386 Return;
387 END IF;
388 Close cur_rowid;
389
390 END GET_FK_IGS_CA_INST;
391
392 PROCEDURE GET_FK_IGS_CA_DA (
393 x_dt_alias IN VARCHAR2
394 ) AS
395
396 CURSOR cur_rowid IS
397 SELECT rowid
398 FROM IGS_CA_DA_INST
399 WHERE dt_alias = x_dt_alias ;
400
401 lv_rowid cur_rowid%RowType;
402
403 BEGIN
404
405 Open cur_rowid;
406 Fetch cur_rowid INTO lv_rowid;
407 IF (cur_rowid%FOUND) THEN
408 Close cur_rowid;
409 Fnd_Message.Set_Name ('IGS', 'IGS_CA_DAI_DA_FK');
410 IGS_GE_MSG_STACK.ADD;
411 App_Exception.Raise_Exception;
412 Return;
413 END IF;
414 Close cur_rowid;
415
416 END GET_FK_IGS_CA_DA;
417
418 PROCEDURE Before_DML (
419 p_action IN VARCHAR2,
420 x_rowid IN VARCHAR2,
421 x_dt_alias IN VARCHAR2,
422 x_sequence_number IN NUMBER,
423 x_cal_type IN VARCHAR2,
424 x_ci_sequence_number IN NUMBER,
425 x_absolute_val IN DATE,
426 x_creation_date IN DATE,
427 x_created_by IN NUMBER,
428 x_last_update_date IN DATE,
429 x_last_updated_by IN NUMBER,
430 x_last_update_login IN NUMBER
431 ) AS
432 /*----------------------------------------------------------------------------
433 || Created By :
434 || Created On :
435 || Purpose :
436 || Known limitations, enhancements or remarks :
437 || Change History :
438 || Who When What
439 || (reverse chronological order - newest change first)
440 || vvutukur 02-Sep-2002 Bug#2531390.The calls to BeforeRowInsertUpdateDelete1 procedure are
441 || modified as defaulting the parametes in that procedure using DEFAULT
442 || clause is removed.Hence passed FALSE to the parameters which were not
443 || passed earlier.
444 ----------------------------------------------------------------------------*/
445 BEGIN
446
447 Set_Column_Values (
448 p_action,
449 x_rowid,
450 x_dt_alias,
451 x_sequence_number,
452 x_cal_type,
453 x_ci_sequence_number,
454 x_absolute_val,
455 x_creation_date,
456 x_created_by,
457 x_last_update_date,
458 x_last_updated_by,
459 x_last_update_login
460 );
461
462 IF (p_action = 'INSERT') THEN
463 -- Call all the procedures related to Before Insert.
464 BeforeRowInsertUpdateDelete1 ( p_inserting => TRUE,
465 p_updating => FALSE,
466 p_deleting => FALSE);
467 IF Get_PK_For_Validation (
468 new_references.dt_alias ,
469 new_references.sequence_number ,
470 new_references.cal_type ,
471 new_references.ci_sequence_number ) THEN
472 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
473 IGS_GE_MSG_STACK.ADD;
474 App_Exception.Raise_Exception;
475 END IF;
476 CHECK_CONSTRAINTS;
477 Check_Parent_Existance;
478 ELSIF (p_action = 'VALIDATE_INSERT') THEN
479 IF Get_PK_For_Validation (
480 new_references.dt_alias ,
481 new_references.sequence_number ,
482 new_references.cal_type ,
483 new_references.ci_sequence_number ) THEN
484 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
485 IGS_GE_MSG_STACK.ADD;
486 App_Exception.Raise_Exception;
487 END IF;
488 CHECK_CONSTRAINTS;
489 ELSIF (p_action = 'UPDATE') THEN
490 -- Call all the procedures related to Before Update.
491 BeforeRowInsertUpdateDelete1 ( p_inserting => FALSE,
492 p_updating => TRUE,
493 p_deleting => FALSE);
494 CHECK_CONSTRAINTS;
495 Check_Parent_Existance;
496 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
497 Check_Constraints;
498 ELSIF (p_action = 'DELETE') THEN
499 -- Call all the procedures related to Before Delete.
500 BeforeRowInsertUpdateDelete1 ( p_inserting => FALSE,
501 p_updating => FALSE,
502 p_deleting => TRUE );
503 Check_Child_Existance;
504
505 ELSIF (p_action = 'VALIDATE_DELETE') THEN
506 Check_Child_Existance;
507 END IF;
508
509 END Before_DML;
510
511 PROCEDURE After_DML (
512 p_action IN VARCHAR2,
513 x_rowid IN VARCHAR2
514 ) AS
515 /*----------------------------------------------------------------------------
516 || Created By :
517 || Created On :
518 || Purpose :
519 || Known limitations, enhancements or remarks :
520 || Change History :
521 || Who When What
522 || (reverse chronological order - newest change first)
523 || vvutukur 02-Sep-2002 Bug#2531390.The calls to AfterStmtInsertUpdateDelete3 procedure, are
524 || modified because defaulting the parametes in that procedure using DEFAULT
525 || clause is removed.Hence passed FALSE to the parameters which were not
526 || passed earlier.
527 ----------------------------------------------------------------------------*/
528 BEGIN
529
530 l_rowid := x_rowid;
531
532 IF (p_action = 'INSERT') THEN
533 -- Call all the procedures related to After Insert.
534 AfterStmtInsertUpdateDelete3 ( p_inserting => TRUE,
535 p_updating => FALSE,
536 p_deleting => FALSE);
537 ELSIF (p_action = 'UPDATE') THEN
538 -- Call all the procedures related to After Update.
539 AfterStmtInsertUpdateDelete3 ( p_inserting => FALSE,
540 p_updating => TRUE,
541 p_deleting => FALSE);
542 ELSIF (p_action = 'DELETE') THEN
543 -- Call all the procedures related to After Delete.
544 AfterStmtInsertUpdateDelete3 ( p_inserting => FALSE,
545 p_updating => FALSE,
546 p_deleting => TRUE );
547 END IF;
548
549 END After_DML;
550
551 procedure INSERT_ROW (
552 X_ROWID in out NOCOPY VARCHAR2,
553 X_DT_ALIAS in VARCHAR2,
554 X_SEQUENCE_NUMBER in NUMBER,
555 X_CAL_TYPE in VARCHAR2,
556 X_CI_SEQUENCE_NUMBER in NUMBER,
557 X_ABSOLUTE_VAL in DATE,
558 X_MODE in VARCHAR2
559 ) AS
560 cursor C is select ROWID from IGS_CA_DA_INST
561 where DT_ALIAS = X_DT_ALIAS
562 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER
563 and CAL_TYPE = X_CAL_TYPE
564 and CI_SEQUENCE_NUMBER = X_CI_SEQUENCE_NUMBER;
565 X_LAST_UPDATE_DATE DATE;
566 X_LAST_UPDATED_BY NUMBER;
567 X_LAST_UPDATE_LOGIN NUMBER;
568 begin
569 X_LAST_UPDATE_DATE := SYSDATE;
570 if(X_MODE = 'I') then
571 X_LAST_UPDATED_BY := 1;
572 X_LAST_UPDATE_LOGIN := 0;
573 elsif (X_MODE = 'R') then
574 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
575 if X_LAST_UPDATED_BY is NULL then
576 X_LAST_UPDATED_BY := -1;
577 end if;
578 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
579 if X_LAST_UPDATE_LOGIN is NULL then
580 X_LAST_UPDATE_LOGIN := -1;
581 end if;
582 else
583 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
584 IGS_GE_MSG_STACK.ADD;
585 app_exception.raise_exception;
586 end if;
587 Before_DML (
588 p_action =>'INSERT',
589 x_rowid =>X_ROWID,
590 x_dt_alias =>X_DT_ALIAS,
591 x_sequence_number =>X_SEQUENCE_NUMBER,
592 x_cal_type =>X_CAL_TYPE,
593 x_ci_sequence_number =>X_CI_SEQUENCE_NUMBER,
594 x_absolute_val =>X_ABSOLUTE_VAL,
595 x_creation_date =>X_LAST_UPDATE_DATE,
596 x_created_by =>X_LAST_UPDATED_BY,
597 x_last_update_date =>X_LAST_UPDATE_DATE,
598 x_last_updated_by =>X_LAST_UPDATED_BY,
599 x_last_update_login =>X_LAST_UPDATE_LOGIN
600 );
601 insert into IGS_CA_DA_INST (
602 DT_ALIAS,
603 SEQUENCE_NUMBER,
604 CAL_TYPE,
605 CI_SEQUENCE_NUMBER,
606 ABSOLUTE_VAL,
607 CREATION_DATE,
608 CREATED_BY,
609 LAST_UPDATE_DATE,
610 LAST_UPDATED_BY,
611 LAST_UPDATE_LOGIN
612 ) values (
613 NEW_REFERENCES.DT_ALIAS,
614 NEW_REFERENCES.SEQUENCE_NUMBER,
615 NEW_REFERENCES.CAL_TYPE,
616 NEW_REFERENCES.CI_SEQUENCE_NUMBER,
617 NEW_REFERENCES.ABSOLUTE_VAL,
618 X_LAST_UPDATE_DATE,
619 X_LAST_UPDATED_BY,
620 X_LAST_UPDATE_DATE,
621 X_LAST_UPDATED_BY,
622 X_LAST_UPDATE_LOGIN
623 );
624
625 open c;
626 fetch c into X_ROWID;
627 if (c%notfound) then
628 close c;
629 raise no_data_found;
630 end if;
631 close c;
632 After_DML (
633 p_action =>'INSERT',
634 x_rowid =>X_ROWID
635 );
636 end INSERT_ROW;
637
638 procedure LOCK_ROW (
639 X_ROWID in VARCHAR2,
640 X_DT_ALIAS in VARCHAR2,
641 X_SEQUENCE_NUMBER in NUMBER,
642 X_CAL_TYPE in VARCHAR2,
643 X_CI_SEQUENCE_NUMBER in NUMBER,
644 X_ABSOLUTE_VAL in DATE
645 ) AS
646 cursor c1 is select
647 ABSOLUTE_VAL
648 from IGS_CA_DA_INST
649 where ROWID = X_ROWID
650 for update nowait;
651 tlinfo c1%rowtype;
652
653 begin
654 open c1;
655 fetch c1 into tlinfo;
656 if (c1%notfound) then
657 close c1;
658 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
659 IGS_GE_MSG_STACK.ADD;
660 app_exception.raise_exception;
661 return;
662 end if;
663 close c1;
664
665 if ( ((tlinfo.ABSOLUTE_VAL = X_ABSOLUTE_VAL)
666 OR ((tlinfo.ABSOLUTE_VAL is null)
667 AND (X_ABSOLUTE_VAL is null)))
668 ) then
669 null;
670 else
671 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
672 IGS_GE_MSG_STACK.ADD;
673 app_exception.raise_exception;
674 end if;
675 return;
676 end LOCK_ROW;
677
678 procedure UPDATE_ROW (
679 X_ROWID in VARCHAR2,
680 X_DT_ALIAS in VARCHAR2,
681 X_SEQUENCE_NUMBER in NUMBER,
682 X_CAL_TYPE in VARCHAR2,
683 X_CI_SEQUENCE_NUMBER in NUMBER,
684 X_ABSOLUTE_VAL in DATE,
685 X_MODE in VARCHAR2
686 ) AS
687 X_LAST_UPDATE_DATE DATE;
688 X_LAST_UPDATED_BY NUMBER;
689 X_LAST_UPDATE_LOGIN NUMBER;
690 begin
691 X_LAST_UPDATE_DATE := SYSDATE;
692 if(X_MODE = 'I') then
693 X_LAST_UPDATED_BY := 1;
694 X_LAST_UPDATE_LOGIN := 0;
695 elsif (X_MODE = 'R') then
696 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
697 if X_LAST_UPDATED_BY is NULL then
698 X_LAST_UPDATED_BY := -1;
699 end if;
700 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
701 if X_LAST_UPDATE_LOGIN is NULL then
702 X_LAST_UPDATE_LOGIN := -1;
703 end if;
704 else
705 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
706 IGS_GE_MSG_STACK.ADD;
707 app_exception.raise_exception;
708 end if;
709 Before_DML (
710 p_action =>'UPDATE',
711 x_rowid =>X_ROWID,
712 x_dt_alias =>X_DT_ALIAS,
713 x_sequence_number =>X_SEQUENCE_NUMBER,
714 x_cal_type =>X_CAL_TYPE,
715 x_ci_sequence_number =>X_CI_SEQUENCE_NUMBER,
716 x_absolute_val =>X_ABSOLUTE_VAL,
717 x_creation_date =>X_LAST_UPDATE_DATE,
718 x_created_by =>X_LAST_UPDATED_BY,
719 x_last_update_date =>X_LAST_UPDATE_DATE,
720 x_last_updated_by =>X_LAST_UPDATED_BY,
721 x_last_update_login =>X_LAST_UPDATE_LOGIN
722 );
723 update IGS_CA_DA_INST set
724 ABSOLUTE_VAL = NEW_REFERENCES.ABSOLUTE_VAL,
725 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
726 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
727 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
728 where ROWID=X_ROWID
729 ;
730 if (sql%notfound) then
731 raise no_data_found;
732 end if;
733 After_DML (
734 p_action =>'UPDATE',
735 x_rowid =>X_ROWID
736 );
737 end UPDATE_ROW;
738
739 procedure ADD_ROW (
740 X_ROWID in out NOCOPY VARCHAR2,
741 X_DT_ALIAS in VARCHAR2,
742 X_SEQUENCE_NUMBER in NUMBER,
743 X_CAL_TYPE in VARCHAR2,
744 X_CI_SEQUENCE_NUMBER in NUMBER,
745 X_ABSOLUTE_VAL in DATE,
746 X_MODE in VARCHAR2
747 ) AS
748 cursor c1 is select rowid from IGS_CA_DA_INST
749 where DT_ALIAS = X_DT_ALIAS
750 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER
751 and CAL_TYPE = X_CAL_TYPE
752 and CI_SEQUENCE_NUMBER = X_CI_SEQUENCE_NUMBER
753 ;
754 begin
755 open c1;
756 fetch c1 into X_ROWID;
757 if (c1%notfound) then
758 close c1;
759 INSERT_ROW (
760 X_ROWID,
761 X_DT_ALIAS,
762 X_SEQUENCE_NUMBER,
763 X_CAL_TYPE,
764 X_CI_SEQUENCE_NUMBER,
765 X_ABSOLUTE_VAL,
766 X_MODE);
767 return;
768 end if;
769 close c1;
770 UPDATE_ROW (
771 X_ROWID,
772 X_DT_ALIAS,
773 X_SEQUENCE_NUMBER,
774 X_CAL_TYPE,
775 X_CI_SEQUENCE_NUMBER,
776 X_ABSOLUTE_VAL,
777 X_MODE);
778 end ADD_ROW;
779
780 procedure DELETE_ROW (X_ROWID in VARCHAR2
781 ) AS
782 begin
783 After_DML (
784 p_action =>'DELETE',
785 x_rowid =>X_ROWID
786 );
787 delete from IGS_CA_DA_INST
788 where ROWID = X_ROWID;
789 if (sql%notfound) then
790 raise no_data_found;
791 end if;
792 After_DML (
793 p_action =>'DELETE',
794 x_rowid =>X_ROWID
795 );
796 end DELETE_ROW;
797
798 end IGS_CA_DA_INST_PKG;