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