[Home] [Help]
PACKAGE BODY: APPS.IGS_CA_DA_INST_PAIR_PKG
Source
1 package body IGS_CA_DA_INST_PAIR_PKG AS
2 /* $Header: IGSCI07B.pls 115.3 2002/11/28 23:01:08 nsidana ship $ */
3 l_rowid VARCHAR2(25);
4 old_references IGS_CA_DA_INST_PAIR%RowType;
5 new_references IGS_CA_DA_INST_PAIR%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_dt_alias IN VARCHAR2 DEFAULT NULL,
11 x_dai_sequence_number IN NUMBER DEFAULT NULL,
12 x_cal_type IN VARCHAR2 DEFAULT NULL,
13 x_ci_sequence_number IN NUMBER DEFAULT NULL,
14 x_related_dt_alias IN VARCHAR2 DEFAULT NULL,
15 x_related_dai_sequence_number IN NUMBER DEFAULT NULL,
16 x_related_cal_type IN VARCHAR2 DEFAULT NULL,
17 x_related_ci_sequence_number IN NUMBER 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
25 CURSOR cur_old_ref_values IS
26 SELECT *
27 FROM IGS_CA_DA_INST_PAIR
28 WHERE rowid = x_rowid;
29
30 BEGIN
31
32 l_rowid := x_rowid;
33
34 -- Code for setting the Old and New Reference Values.
35 -- Populate Old Values.
36 Open cur_old_ref_values;
37 Fetch cur_old_ref_values INTO old_references;
38 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT')) THEN
39 Close cur_old_ref_values;
40 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
41 IGS_GE_MSG_STACK.ADD;
42 App_Exception.Raise_Exception;
43 Return;
44 END IF;
45 Close cur_old_ref_values;
46
47 -- Populate New Values.
48 new_references.dt_alias := x_dt_alias;
49 new_references.dai_sequence_number := x_dai_sequence_number;
50 new_references.cal_type := x_cal_type;
51 new_references.ci_sequence_number := x_ci_sequence_number;
52 new_references.related_dt_alias := x_related_dt_alias;
53 new_references.related_dai_sequence_number := x_related_dai_sequence_number;
54 new_references.related_cal_type := x_related_cal_type;
55 new_references.related_ci_sequence_number := x_related_ci_sequence_number;
56 IF (p_action = 'UPDATE') THEN
57 new_references.creation_date := old_references.creation_date;
58 new_references.created_by := old_references.created_by;
59 ELSE
60 new_references.creation_date := x_creation_date;
61 new_references.created_by := x_created_by;
62 END IF;
63 new_references.last_update_date := x_last_update_date;
64 new_references.last_updated_by := x_last_updated_by;
65 new_references.last_update_login := x_last_update_login;
66
67 END Set_Column_Values;
68
69 -- Trigger description :-
70 -- "OSS_TST".trg_daip_br_iud
71 -- BEFORE INSERT OR DELETE OR UPDATE
72 -- ON IGS_CA_DA_INST_PAIR
73 -- FOR EACH ROW
74
75 PROCEDURE BeforeRowInsertUpdateDelete1(
76 p_inserting IN BOOLEAN DEFAULT FALSE,
77 p_updating IN BOOLEAN DEFAULT FALSE,
78 p_deleting IN BOOLEAN DEFAULT FALSE
79 ) AS
80 v_message_name varchar2(30);
81 BEGIN
82 IF p_inserting OR p_updating
83 THEN
84 -- Validate related date alias instance is different to parent.
85 IF IGS_CA_VAL_DAIP.calp_val_daip_dai (new_references.dt_alias,
86 new_references.dai_sequence_number,
87 new_references.cal_type,
88 new_references.ci_sequence_number,
89 new_references.related_dt_alias,
90 new_references.related_dai_sequence_number,
91 new_references.related_cal_type,
92 new_references.related_ci_sequence_number,
93 v_message_name) = FALSE
94 THEN
95 Fnd_Message.Set_Name('IGS',v_message_name);
96 IGS_GE_MSG_STACK.ADD;
97 APP_EXCEPTION.RAISE_EXCEPTION;
98 END IF;
99 -- Validate related date alias instance value is different to parent.
100 IF IGS_CA_VAL_DAIP.calp_val_daip_value (new_references.dt_alias,
101 new_references.dai_sequence_number,
102 new_references.cal_type,
103 new_references.ci_sequence_number,
104 new_references.related_dt_alias,
105 new_references.related_dai_sequence_number,
106 new_references.related_cal_type,
107 new_references.related_ci_sequence_number,
108 v_message_name) = FALSE
109 THEN
110 Fnd_Message.Set_Name('IGS',v_message_name);
111 IGS_GE_MSG_STACK.ADD;
112 APP_EXCEPTION.RAISE_EXCEPTION;
113 END IF;
114 -- Validate related date alias instance calendar type.
115 IF IGS_CA_VAL_DAIP.calp_val_daip_ct (new_references.cal_type,
116 new_references.related_cal_type,
117 v_message_name) = FALSE
118 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 END BeforeRowInsertUpdateDelete1;
125
126 -- Trigger description :-
127 -- "OSS_TST".trg_daip_as_iu
128 -- AFTER INSERT OR UPDATE
129 -- ON IGS_CA_DA_INST_PAIR
130
131 PROCEDURE AfterStmtInsertUpdate3(
132 p_inserting IN BOOLEAN DEFAULT FALSE,
133 p_updating IN BOOLEAN DEFAULT FALSE,
134 p_deleting IN BOOLEAN DEFAULT FALSE
135 ) AS
136 v_message_name varchar2(30);
137 BEGIN
138 -- Validate the dt alias instance pair.
139 IF p_inserting THEN
140 IF IGS_CA_VAL_DAIP.calp_val_daip_unique (new_references.dt_alias,
141 new_references.dai_sequence_number,
142 new_references.cal_type,
143 new_references.ci_sequence_number,
144 new_references.related_dt_alias,
145 new_references.related_dai_sequence_number,
146 new_references.related_cal_type,
147 new_references.related_ci_sequence_number,
148 v_message_name) = FALSE THEN
149 Fnd_Message.Set_Name('IGS',v_message_name);
150 IGS_GE_MSG_STACK.ADD;
151 APP_EXCEPTION.RAISE_EXCEPTION;
152 END IF;
153 END IF;
154 END AfterStmtInsertUpdate3;
155
156 PROCEDURE Check_Constraints (
157 Column_Name IN VARCHAR2 DEFAULT NULL,
158 Column_Value IN VARCHAR2 DEFAULT NULL
159 )
160 IS
161 BEGIN
162 IF column_name is null then
163 NULL;
164 ELSIF UPPER(column_name) = 'DAI_SEQUENCE_NUMBER ' Then
165 new_references.dai_sequence_number := igs_ge_number.to_num(column_value);
166 Elsif UPPER(column_name) = 'CI_SEQUENCE_NUMBER' Then
167 NEW_REFERENCES.ci_sequence_number:= igs_ge_number.to_num(column_value);
168 Elsif UPPER(column_name) = 'RELATED_DAI_SEQUENCE_NUMBER' Then
169 NEW_REFERENCES.related_dai_sequence_number := igs_ge_number.to_num(column_value);
170 Elsif UPPER(column_name) = 'RELATED_CI_SEQUENCE_NUMBER' Then
171 NEW_REFERENCES.related_ci_sequence_number:= igs_ge_number.to_num(column_value);
172 Elsif UPPER(column_name) = 'DT_ALIAS' Then
173 NEW_REFERENCES.dt_alias:= column_value;
174 Elsif UPPER(column_name) = 'CAL_TYPE' Then
175 NEW_REFERENCES.cal_type:= column_value;
176 Elsif UPPER(column_name) = 'RELATED_DT_ALIAS' Then
177 NEW_REFERENCES.related_dt_alias:= column_value;
178 Elsif UPPER(column_name) = 'RELATED_CAL_TYPE' Then
179 NEW_REFERENCES.related_cal_type:= column_value;
180 end if;
181 If upper(column_name) = 'DAI_SEQUENCE_NUMBER' or column_name is null Then
182 if NEW_REFERENCES.dai_sequence_number <1 OR
183 NEW_REFERENCES.dai_sequence_number > 999999 then
184 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
185 IGS_GE_MSG_STACK.ADD;
186 App_Exception.Raise_Exception;
187 end if;
188 end if;
189 if upper(column_name) = 'CI_SEQUENCE_NUMBER' or column_name is null Then
190 if NEW_REFERENCES.ci_sequence_number < 1 OR
191 NEW_REFERENCES.ci_sequence_number > 999999 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 if upper(column_name) = 'RELATED_DAI_SEQUENCE_NUMBER' or column_name is null Then
198 if NEW_REFERENCES.related_dai_sequence_number < 1 OR
199 NEW_REFERENCES.related_dai_sequence_number > 999999 then
200 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
201 IGS_GE_MSG_STACK.ADD;
202 App_Exception.Raise_Exception;
203 end if;
204 end if;
205 if upper(column_name) = 'RELATED_CI_SEQUENCE_NUMBER' or column_name is null Then
206 if NEW_REFERENCES.related_ci_sequence_number < 1 OR
207 NEW_REFERENCES.related_ci_sequence_number > 999999 then
208 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
209 IGS_GE_MSG_STACK.ADD;
210 App_Exception.Raise_Exception;
211 end if;
212 end if;
213 if upper(column_name) = 'DT_ALIAS' or column_name is null Then
214 if NEW_REFERENCES.dt_alias <> UPPER( NEW_REFERENCES.dt_alias) then
215 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
216 IGS_GE_MSG_STACK.ADD;
217 App_Exception.Raise_Exception;
218 end if;
219 end if;
220 if upper(column_name) = 'CAL_TYPE' or column_name is null Then
221 if NEW_REFERENCES.cal_type <> UPPER( NEW_REFERENCES.cal_type) 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 if upper(column_name) = 'RELATED_DT_ALIAS' or column_name is null Then
228 if NEW_REFERENCES.related_dt_alias <> UPPER( NEW_REFERENCES.related_dt_alias) then
229 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
230 IGS_GE_MSG_STACK.ADD;
231 App_Exception.Raise_Exception;
232 end if;
233 end if;
234 if upper(column_name) = 'RELATED_CAL_TYPE' or column_name is null Then
235 if NEW_REFERENCES.related_cal_type <> UPPER( NEW_REFERENCES.related_cal_type) then
236 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
237 IGS_GE_MSG_STACK.ADD;
238 App_Exception.Raise_Exception;
239 end if;
240 end if;
241 END Check_Constraints;
242
243 FUNCTION Get_UK_For_Validation (
244 x_dt_alias IN VARCHAR2,
245 x_dai_sequence_number IN NUMBER,
246 x_cal_type IN VARCHAR2,
247 x_ci_sequence_number IN NUMBER
248 )RETURN BOOLEAN AS
249
250 CURSOR cur_rowid IS
251 SELECT rowid
252 FROM IGS_CA_DA_INST_PAIR
253 WHERE dt_alias = x_dt_alias
254 AND dai_sequence_number = x_dai_sequence_number
255 AND cal_type = x_cal_type
256 AND ci_sequence_number = x_ci_sequence_number
257 AND ((l_rowid IS NULL) OR (rowid <> l_rowid))
258 FOR UPDATE NOWAIT;
259
260 lv_rowid cur_rowid%RowType;
261
262 BEGIN
263
264 Open cur_rowid;
265 Fetch cur_rowid INTO lv_rowid;
266 IF (cur_rowid%FOUND) THEN
267 Close cur_rowid;
268 Return (TRUE);
269 ELSE
270 Close cur_rowid;
271 Return (FALSE);
272 END IF;
273
274 END Get_UK_For_Validation;
275
276 PROCEDURE Check_Uniqueness AS
277 Begin
278 IF Get_UK_For_Validation (
279 new_references.dt_alias ,
280 new_references.dai_sequence_number ,
281 new_references.cal_type ,
282 new_references.ci_sequence_number
283 )THEN
284 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
285 IGS_GE_MSG_STACK.ADD;
286 App_Exception.Raise_Exception;
287 END IF;
288 End Check_Uniqueness;
289
290
291
292 PROCEDURE Check_Parent_Existance AS
293 BEGIN
294
295 IF (((old_references.dt_alias = new_references.dt_alias) AND
296 (old_references.dai_sequence_number = new_references.dai_sequence_number) AND
297 (old_references.cal_type = new_references.cal_type) AND
298 (old_references.ci_sequence_number = new_references.ci_sequence_number)) OR
299 ((new_references.dt_alias IS NULL) OR
300 (new_references.dai_sequence_number IS NULL) OR
301 (new_references.cal_type IS NULL) OR
302 (new_references.ci_sequence_number IS NULL))) THEN
303 NULL;
304 ELSE
305 IF NOT IGS_CA_DA_INST_PKG.Get_PK_For_Validation (
306 new_references.dt_alias,
307 new_references.dai_sequence_number,
308 new_references.cal_type,
309 new_references.ci_sequence_number)
310 THEN
311 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
312 IGS_GE_MSG_STACK.ADD;
313 App_Exception.Raise_Exception;
314 END IF;
315
316 END IF;
317
318 IF (((old_references.related_dt_alias = new_references.related_dt_alias) AND
319 (old_references.related_dai_sequence_number = new_references.related_dai_sequence_number) AND
320 (old_references.related_cal_type = new_references.related_cal_type) AND
324 (new_references.related_cal_type IS NULL) OR
321 (old_references.related_ci_sequence_number = new_references.related_ci_sequence_number)) OR
322 ((new_references.related_dt_alias IS NULL) OR
323 (new_references.related_dai_sequence_number IS NULL) OR
325 (new_references.related_ci_sequence_number IS NULL))) THEN
326 NULL;
327 ELSE
328 IF NOT IGS_CA_DA_INST_PKG.Get_PK_For_Validation (
329 new_references.related_dt_alias,
330 new_references.related_dai_sequence_number,
331 new_references.related_cal_type,
332 new_references.related_ci_sequence_number
333 ) THEN
334 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
335 IGS_GE_MSG_STACK.ADD;
336 App_Exception.Raise_Exception;
337 END IF;
338 END IF;
339
340 END Check_Parent_Existance;
341
342 FUNCTION Get_PK_For_Validation (
343 x_dt_alias IN VARCHAR2,
344 x_dai_sequence_number IN NUMBER,
345 x_cal_type IN VARCHAR2,
346 x_ci_sequence_number IN NUMBER,
347 x_related_dt_alias IN VARCHAR2,
348 x_related_dai_sequence_number IN NUMBER,
349 x_related_cal_type IN VARCHAR2,
350 x_related_ci_sequence_number IN NUMBER
351 )RETURN BOOLEAN AS
352
353 CURSOR cur_rowid IS
354 SELECT rowid
355 FROM IGS_CA_DA_INST_PAIR
356 WHERE dt_alias = x_dt_alias
357 AND dai_sequence_number = x_dai_sequence_number
358 AND cal_type = x_cal_type
359 AND ci_sequence_number = x_ci_sequence_number
360 AND related_dt_alias = x_related_dt_alias
361 AND related_dai_sequence_number = x_related_dai_sequence_number
362 AND related_cal_type = x_related_cal_type
363 AND related_ci_sequence_number = x_related_ci_sequence_number
364 FOR UPDATE NOWAIT;
365
366 lv_rowid cur_rowid%RowType;
367
368 BEGIN
369
370 Open cur_rowid;
371 Fetch cur_rowid INTO lv_rowid;
372 IF (cur_rowid%FOUND) THEN
373 Close cur_rowid;
374 Return (TRUE);
375 ELSE
376 Close cur_rowid;
377 Return (FALSE);
378 END IF;
379
380 END Get_PK_For_Validation;
381
382 PROCEDURE GET_FK_IGS_CA_DA_INST (
383 x_dt_alias IN VARCHAR2,
384 x_sequence_number IN NUMBER,
385 x_cal_type IN VARCHAR2,
386 x_ci_sequence_number IN NUMBER
387 ) AS
388
389 CURSOR cur_rowid IS
390 SELECT rowid
391 FROM IGS_CA_DA_INST_PAIR
392 WHERE (dt_alias = x_dt_alias
393 AND dai_sequence_number = x_sequence_number
394 AND cal_type = x_cal_type
395 AND ci_sequence_number = x_ci_sequence_number)
396 OR (related_dt_alias = x_dt_alias
397 AND related_dai_sequence_number = x_sequence_number
398 AND related_cal_type = x_cal_type
399 AND related_ci_sequence_number = x_ci_sequence_number);
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_DAIP_DAI_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_INST;
417
418 PROCEDURE Before_DML (
419 p_action IN VARCHAR2,
420 x_rowid IN VARCHAR2 DEFAULT NULL,
421 x_dt_alias IN VARCHAR2 DEFAULT NULL,
422 x_dai_sequence_number IN NUMBER DEFAULT NULL,
423 x_cal_type IN VARCHAR2 DEFAULT NULL,
424 x_ci_sequence_number IN NUMBER DEFAULT NULL,
425 x_related_dt_alias IN VARCHAR2 DEFAULT NULL,
426 x_related_dai_sequence_number IN NUMBER DEFAULT NULL,
427 x_related_cal_type IN VARCHAR2 DEFAULT NULL,
428 x_related_ci_sequence_number IN NUMBER DEFAULT NULL,
429 x_creation_date IN DATE DEFAULT NULL,
430 x_created_by IN NUMBER DEFAULT NULL,
431 x_last_update_date IN DATE DEFAULT NULL,
432 x_last_updated_by IN NUMBER DEFAULT NULL,
433 x_last_update_login IN NUMBER DEFAULT NULL
434 ) AS
435 BEGIN
436
437 Set_Column_Values (
438 p_action,
439 x_rowid,
440 x_dt_alias,
441 x_dai_sequence_number,
442 x_cal_type,
443 x_ci_sequence_number,
444 x_related_dt_alias,
445 x_related_dai_sequence_number,
446 x_related_cal_type,
447 x_related_ci_sequence_number,
448 x_creation_date,
449 x_created_by,
450 x_last_update_date,
451 x_last_updated_by,
452 x_last_update_login
453 );
454
455 IF (p_action = 'INSERT') THEN
456 -- Call all the procedures related to Before Insert.
457 BeforeRowInsertUpdateDelete1 ( p_inserting => TRUE );
458 IF Get_PK_For_Validation (
462 new_references.ci_sequence_number ,
459 new_references.dt_alias ,
460 new_references.dai_sequence_number ,
461 new_references.cal_type ,
463 new_references.related_dt_alias ,
464 new_references.related_dai_sequence_number ,
465 new_references.related_cal_type ,
466 new_references.related_ci_sequence_number )THEN
467 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
468 IGS_GE_MSG_STACK.ADD;
469 App_Exception.Raise_Exception;
470 END IF;
471 Check_Uniqueness;
472 CHECK_CONSTRAINTS;
473 Check_Parent_Existance;
474 ELSIF (p_action = 'VALIDATE_INSERT') THEN
475 IF Get_PK_For_Validation (
476 new_references.dt_alias ,
477 new_references.dai_sequence_number ,
478 new_references.cal_type ,
479 new_references.ci_sequence_number ,
480 new_references.related_dt_alias ,
481 new_references.related_dai_sequence_number ,
482 new_references.related_cal_type ,
483 new_references.related_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_Uniqueness;
489 CHECK_CONSTRAINTS;
490 ELSIF (p_action = 'UPDATE') THEN
491 -- Call all the procedures related to Before Update.
492 BeforeRowInsertUpdateDelete1 ( p_updating => TRUE );
493 Check_Uniqueness;
494 Check_Constraints;
495 Check_Parent_Existance;
496 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
497 Check_Uniqueness;
498 Check_Constraints;
499 ELSIF (p_action = 'DELETE') THEN
500 -- Call all the procedures related to Before Delete.
501 BeforeRowInsertUpdateDelete1 ( p_deleting => TRUE );
502 ELSIF (p_action = 'VALIDATE_DELETE') THEN
503 NULL;
504 END IF;
505
506
507 END Before_DML;
508
509 PROCEDURE After_DML (
510 p_action IN VARCHAR2,
511 x_rowid IN VARCHAR2
512 ) AS
513 BEGIN
514
515 l_rowid := x_rowid;
516
517 IF (p_action = 'INSERT') THEN
518 -- Call all the procedures related to After Insert.
519 AfterStmtInsertUpdate3 ( p_inserting => TRUE );
520 ELSIF (p_action = 'UPDATE') THEN
521 -- Call all the procedures related to After Update.
522 AfterStmtInsertUpdate3 ( p_updating => TRUE );
523 ELSIF (p_action = 'DELETE') THEN
524 -- Call all the procedures related to After Delete.
525 Null;
526 END IF;
527
528 END After_DML;
529
530 procedure INSERT_ROW (
531 X_ROWID in out NOCOPY VARCHAR2,
532 X_DT_ALIAS in VARCHAR2,
533 X_DAI_SEQUENCE_NUMBER in NUMBER,
534 X_CAL_TYPE in VARCHAR2,
535 X_CI_SEQUENCE_NUMBER in NUMBER,
536 X_RELATED_DT_ALIAS in VARCHAR2,
537 X_RELATED_DAI_SEQUENCE_NUMBER in NUMBER,
538 X_RELATED_CAL_TYPE in VARCHAR2,
539 X_RELATED_CI_SEQUENCE_NUMBER in NUMBER,
540 X_MODE in VARCHAR2 default 'R'
541 ) AS
542 cursor C is select ROWID from IGS_CA_DA_INST_PAIR
543 where DT_ALIAS = X_DT_ALIAS
544 and DAI_SEQUENCE_NUMBER = X_DAI_SEQUENCE_NUMBER
545 and CAL_TYPE = X_CAL_TYPE
546 and CI_SEQUENCE_NUMBER = X_CI_SEQUENCE_NUMBER
547 and RELATED_DT_ALIAS = X_RELATED_DT_ALIAS
548 and RELATED_DAI_SEQUENCE_NUMBER = X_RELATED_DAI_SEQUENCE_NUMBER
549 and RELATED_CAL_TYPE = X_RELATED_CAL_TYPE
550 and RELATED_CI_SEQUENCE_NUMBER = X_RELATED_CI_SEQUENCE_NUMBER;
551 X_LAST_UPDATE_DATE DATE;
552 X_LAST_UPDATED_BY NUMBER;
553 X_LAST_UPDATE_LOGIN NUMBER;
554 begin
555 X_LAST_UPDATE_DATE := SYSDATE;
556 if(X_MODE = 'I') then
557 X_LAST_UPDATED_BY := 1;
558 X_LAST_UPDATE_LOGIN := 0;
559 elsif (X_MODE = 'R') then
560 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
561 if X_LAST_UPDATED_BY is NULL then
562 X_LAST_UPDATED_BY := -1;
563 end if;
564 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
565 if X_LAST_UPDATE_LOGIN is NULL then
566 X_LAST_UPDATE_LOGIN := -1;
567 end if;
568 else
569 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
570 IGS_GE_MSG_STACK.ADD;
571 app_exception.raise_exception;
572 end if;
573 Before_DML (
574 p_action =>'INSERT',
575 x_rowid =>X_ROWID,
576 x_dt_alias =>X_DT_ALIAS,
577 x_dai_sequence_number =>X_DAI_SEQUENCE_NUMBER,
578 x_cal_type =>X_CAL_TYPE,
579 x_ci_sequence_number =>X_CI_SEQUENCE_NUMBER,
580 x_related_dt_alias =>X_RELATED_DT_ALIAS,
581 x_related_dai_sequence_number =>X_RELATED_DAI_SEQUENCE_NUMBER,
582 x_related_cal_type =>X_RELATED_CAL_TYPE,
583 x_related_ci_sequence_number =>X_RELATED_CI_SEQUENCE_NUMBER,
584 x_creation_date =>X_LAST_UPDATE_DATE,
585 x_created_by =>X_LAST_UPDATED_BY,
586 x_last_update_date =>X_LAST_UPDATE_DATE,
587 x_last_updated_by =>X_LAST_UPDATED_BY,
588 x_last_update_login =>X_LAST_UPDATE_LOGIN
589 );
590 insert into IGS_CA_DA_INST_PAIR (
591 DT_ALIAS,
592 DAI_SEQUENCE_NUMBER,
593 CAL_TYPE,
594 CI_SEQUENCE_NUMBER,
595 RELATED_DT_ALIAS,
596 RELATED_DAI_SEQUENCE_NUMBER,
597 RELATED_CAL_TYPE,
598 RELATED_CI_SEQUENCE_NUMBER,
599 CREATION_DATE,
600 CREATED_BY,
601 LAST_UPDATE_DATE,
602 LAST_UPDATED_BY,
603 LAST_UPDATE_LOGIN
604 ) values (
605 NEW_REFERENCES.DT_ALIAS,
606 NEW_REFERENCES.DAI_SEQUENCE_NUMBER,
607 NEW_REFERENCES.CAL_TYPE,
608 NEW_REFERENCES.CI_SEQUENCE_NUMBER,
609 NEW_REFERENCES.RELATED_DT_ALIAS,
610 NEW_REFERENCES.RELATED_DAI_SEQUENCE_NUMBER,
611 NEW_REFERENCES.RELATED_CAL_TYPE,
612 NEW_REFERENCES.RELATED_CI_SEQUENCE_NUMBER,
613 X_LAST_UPDATE_DATE,
614 X_LAST_UPDATED_BY,
615 X_LAST_UPDATE_DATE,
616 X_LAST_UPDATED_BY,
617 X_LAST_UPDATE_LOGIN
618 );
619
620 open c;
621 fetch c into X_ROWID;
622 if (c%notfound) then
623 close c;
624 raise no_data_found;
625 end if;
626 close c;
627 After_DML (
628 p_action =>'INSERT',
629 x_rowid =>X_ROWID
630 );
631
632 end INSERT_ROW;
633
634 procedure LOCK_ROW (
635 X_ROWID in VARCHAR2,
636 X_DT_ALIAS in VARCHAR2,
637 X_DAI_SEQUENCE_NUMBER in NUMBER,
638 X_CAL_TYPE in VARCHAR2,
639 X_CI_SEQUENCE_NUMBER in NUMBER,
640 X_RELATED_DT_ALIAS in VARCHAR2,
641 X_RELATED_DAI_SEQUENCE_NUMBER in NUMBER,
642 X_RELATED_CAL_TYPE in VARCHAR2,
643 X_RELATED_CI_SEQUENCE_NUMBER in NUMBER
644 ) AS
645 cursor c1 is select *
646 from IGS_CA_DA_INST_PAIR
647 where ROWID = X_ROWID
648 for update nowait;
649 tlinfo c1%rowtype;
650
651 begin
652 open c1;
653 fetch c1 into tlinfo;
654 if (c1%notfound) then
655 close c1;
656 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
657 IGS_GE_MSG_STACK.ADD;
658 app_exception.raise_exception;
659 return;
660 end if;
661 close c1;
662 return;
663 end LOCK_ROW;
664
665 procedure DELETE_ROW (
666 X_ROWID in VARCHAR2
667 ) AS
668 begin
669 Before_DML (
670 p_action =>'DELETE',
671 x_rowid =>X_ROWID
672 );
673
674 delete from IGS_CA_DA_INST_PAIR
675 where ROWID=X_ROWID;
676 if (sql%notfound) then
677 raise no_data_found;
678 end if;
679 After_DML (
680 p_action =>'DELETE',
681 x_rowid =>X_ROWID
682 );
683
684 end DELETE_ROW;
685
686 end IGS_CA_DA_INST_PAIR_PKG;