[Home] [Help]
PACKAGE BODY: APPS.IGS_CA_DA_INST_OFCNT_PKG
Source
1 package body IGS_CA_DA_INST_OFCNT_PKG AS
2 /* $Header: IGSCI05B.pls 120.1 2006/01/25 09:17:10 skpandey noship $ */
3 l_rowid VARCHAR2(25);
4 old_references IGS_CA_DA_INST_OFCNT%RowType;
5 new_references IGS_CA_DA_INST_OFCNT%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_offset_dt_alias IN VARCHAR2 DEFAULT NULL,
15 x_offset_dai_sequence_number IN NUMBER DEFAULT NULL,
16 x_offset_cal_type IN VARCHAR2 DEFAULT NULL,
17 x_offset_ci_sequence_number IN NUMBER DEFAULT NULL,
18 x_s_dt_offset_constraint_type IN VARCHAR2 DEFAULT NULL,
19 x_constraint_condition IN VARCHAR2 DEFAULT NULL,
20 x_constraint_resolution IN NUMBER DEFAULT NULL,
21 x_creation_date IN DATE DEFAULT NULL,
22 x_created_by IN NUMBER DEFAULT NULL,
23 x_last_update_date IN DATE DEFAULT NULL,
24 x_last_updated_by IN NUMBER DEFAULT NULL,
25 x_last_update_login IN NUMBER DEFAULT NULL
26 ) AS
27
28 CURSOR cur_old_ref_values IS
29 SELECT *
30 FROM IGS_CA_DA_INST_OFCNT
31 WHERE rowid = x_rowid;
32
33 BEGIN
34
35 l_rowid := x_rowid;
36
37 -- Code for setting the Old and New Reference Values.
38 -- Populate Old Values.
39 Open cur_old_ref_values;
40 Fetch cur_old_ref_values INTO old_references;
41 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT')) THEN
42 Close cur_old_ref_values;
43 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
44 IGS_GE_MSG_STACK.ADD;
45 App_Exception.Raise_Exception;
46 Return;
47 END IF;
48 Close cur_old_ref_values;
49
50 -- Populate New Values.
51 new_references.dt_alias := x_dt_alias;
52 new_references.dai_sequence_number := x_dai_sequence_number;
53 new_references.cal_type := x_cal_type;
54 new_references.ci_sequence_number := x_ci_sequence_number;
55 new_references.offset_dt_alias := x_offset_dt_alias;
56 new_references.offset_dai_sequence_number := x_offset_dai_sequence_number;
57 new_references.offset_cal_type := x_offset_cal_type;
58 new_references.offset_ci_sequence_number := x_offset_ci_sequence_number;
59 new_references.s_dt_offset_constraint_type := x_s_dt_offset_constraint_type;
60 new_references.constraint_condition := x_constraint_condition;
61 new_references.constraint_resolution := x_constraint_resolution;
62 IF (p_action = 'UPDATE') THEN
63 new_references.creation_date := old_references.creation_date;
64 new_references.created_by := old_references.created_by;
65 ELSE
66 new_references.creation_date := x_creation_date;
67 new_references.created_by := x_created_by;
68 END IF;
69 new_references.last_update_date := x_last_update_date;
70 new_references.last_updated_by := x_last_updated_by;
71 new_references.last_update_login := x_last_update_login;
72
73 END Set_Column_Values;
74
75 -- Trigger description :-
76 -- "OSS_TST".trg_daioc_br_iud
77 -- BEFORE INSERT OR DELETE OR UPDATE
78 -- ON IGS_CA_DA_INST_OFCNT
79 -- FOR EACH ROW
80
81 PROCEDURE BeforeRowInsertUpdateDelete1(
82 p_inserting IN BOOLEAN DEFAULT FALSE,
83 p_updating IN BOOLEAN DEFAULT FALSE,
84 p_deleting IN BOOLEAN DEFAULT FALSE
85 ) AS
86 v_message_name varchar2(30);
87 BEGIN
88 IF p_inserting
89 THEN
90 -- Validate constraint type is not closed.
91 IF IGS_CA_VAL_DAIOC.calp_val_sdoct_clsd(
92 new_references.s_dt_offset_constraint_type,
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
100 END IF;
101 END BeforeRowInsertUpdateDelete1;
102
103 -- Trigger description :-
104 -- "OSS_TST".trg_daioc_as_iu
105 -- AFTER INSERT OR UPDATE
106 -- ON IGS_CA_DA_INST_OFCNT
107
108 PROCEDURE AfterStmtInsertUpdate3(
109 p_inserting IN BOOLEAN DEFAULT FALSE,
110 p_updating IN BOOLEAN DEFAULT FALSE,
111 p_deleting IN BOOLEAN DEFAULT FALSE
112 ) AS
113 v_message_name varchar2(30);
114 BEGIN
115 -- Validate the dt alias instance offset constraint.
116 IF p_inserting or p_updating THEN
117 IF IGS_CA_VAL_DAIOC.calp_val_sdoct_clash
118 (new_references.dt_alias,
119 new_references.offset_dt_alias,
120 new_references.dai_sequence_number,
121 new_references.offset_dai_sequence_number,
122 new_references.cal_type,
123 new_references.offset_cal_type,
124 new_references.ci_sequence_number,
125 new_references.offset_ci_sequence_number,
126 new_references.s_dt_offset_constraint_type,
127 new_references.constraint_condition,
128 new_references.constraint_resolution,
129 v_message_name) = FALSE THEN
130 Fnd_Message.Set_Name('IGS',v_message_name);
131 IGS_GE_MSG_STACK.ADD;
132 APP_EXCEPTION.RAISE_EXCEPTION;
133 END IF;
134 END IF;
135 END AfterStmtInsertUpdate3;
136
137 PROCEDURE Check_Constraints (
138 Column_Name IN VARCHAR2 DEFAULT NULL,
139 Column_Value IN VARCHAR2 DEFAULT NULL
140 )
141 AS
142 BEGIN
143 IF column_name is null then
144 NULL;
145 ELSIF UPPER(column_name) = 'DAI_SEQUENCE_NUMBER ' Then
146 new_references.dai_sequence_number := igs_ge_number.to_num(column_value);
147 Elsif UPPER(column_name) = 'CAL_TYPE' Then
148 NEW_REFERENCES.cal_type:= column_value;
149 Elsif UPPER(column_name) = 'DT_ALIAS' Then
150 NEW_REFERENCES.dt_alias := column_value;
151 Elsif UPPER(column_name) = 'OFFSET_CAL_TYPE' Then
152 NEW_REFERENCES.offset_cal_type:= column_value;
153 Elsif UPPER(column_name) = 'OFFSET_DT_ALIAS' Then
154 NEW_REFERENCES.offset_dt_alias:= column_value;
155 Elsif UPPER(column_name) = 'S_DT_OFFSET_CONSTRAINT_TYPE' Then
156 NEW_REFERENCES.s_dt_offset_constraint_type:= column_value;
157 Elsif UPPER(column_name) = 'CONSTRAINT_CONDITION' Then
158 NEW_REFERENCES.constraint_condition:= column_value;
159 Elsif UPPER(column_name) = 'CONSTRAINT_RESOLUTION' Then
160 NEW_REFERENCES.constraint_resolution:= igs_ge_number.to_num(column_value);
161 ELSIF UPPER(column_name) = 'OFFSET_CI_SEQUENCE_NUMBER' Then
162 new_references.offset_ci_sequence_number := igs_ge_number.to_num(column_value);
163 Elsif UPPER(column_name) = 'OFFSET_DAI_SEQUENCE_NUMBER' Then
164 NEW_REFERENCES.offset_dai_sequence_number := igs_ge_number.to_num(column_value);
165 Elsif UPPER(column_name) = 'CI_SEQUENCE_NUMBER' Then
166 NEW_REFERENCES.ci_sequence_number :=igs_ge_number.to_num(column_value);
167 end if;
168 If upper(column_name) = 'DAI_SEQUENCE_NUMBER' or column_name is null Then
169 if NEW_REFERENCES.dai_sequence_number <1 OR
170 NEW_REFERENCES.dai_sequence_number > 999999 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 if upper(column_name) = 'CAL_TYPE' or column_name is null Then
177 if NEW_REFERENCES.cal_type <> UPPER(NEW_REFERENCES.cal_type )THEN
178 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
179 IGS_GE_MSG_STACK.ADD;
180 App_Exception.Raise_Exception;
181 end if;
182 end if;
183 if upper(column_name) = 'DT_ALIAS' or column_name is null Then
184 if NEW_REFERENCES.dt_alias <> UPPER(NEW_REFERENCES.dt_alias)THEN
185 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
186 IGS_GE_MSG_STACK.ADD;
187 App_Exception.Raise_Exception;
188 end if;
189 end if;
190
191 if upper(column_name) = 'OFFSET_CAL_TYPE' or column_name is null Then
192 if NEW_REFERENCES.offset_cal_type <> UPPER(NEW_REFERENCES.offset_cal_type)THEN
193 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
194 IGS_GE_MSG_STACK.ADD;
195 App_Exception.Raise_Exception;
196 end if;
197 end if;
198 if upper(column_name) = 'OFFSET_DT_ALIAS' or column_name is null Then
199 if NEW_REFERENCES.offset_dt_alias <> UPPER( NEW_REFERENCES.offset_dt_alias) 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) = 'S_DT_OFFSET_CONSTRAINT_TYPE' or column_name is null Then
206 if NEW_REFERENCES.s_dt_offset_constraint_type <> UPPER( NEW_REFERENCES.s_dt_offset_constraint_type ) 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 if upper(column_name) = 'CONSTRAINT_CONDITION' or column_name is null Then
213 if NEW_REFERENCES.constraint_condition NOT IN ('MUST' , 'MUST NOT') then
214 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
215 IGS_GE_MSG_STACK.ADD;
216 App_Exception.Raise_Exception;
217 end if;
218 end if;
219 If upper(column_name) = 'CONSTRAINT_RESOLUTION' or column_name is null Then
220 if NEW_REFERENCES.constraint_resolution < -9 OR
221 NEW_REFERENCES.constraint_resolution > 9 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) = 'OFFSET_CI_SEQUENCE_NUMBER ' or column_name is null Then
228 if NEW_REFERENCES.offset_ci_sequence_number < 1 OR
229 NEW_REFERENCES.offset_ci_sequence_number > 999999 then
230 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
231 IGS_GE_MSG_STACK.ADD;
232 App_Exception.Raise_Exception;
233 end if;
234 end if;
235 If upper(column_name) = 'OFFSET_DAI_SEQUENCE_NUMBER' or column_name is null Then
236 if NEW_REFERENCES.offset_dai_sequence_number < 1 OR
237 NEW_REFERENCES.offset_dai_sequence_number > 999999 then
238 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
239 IGS_GE_MSG_STACK.ADD;
240 App_Exception.Raise_Exception;
241 end if;
242 end if;
243 If upper(column_name) = 'CI_SEQUENCE_NUMBER' or column_name is null Then
244 if NEW_REFERENCES.ci_sequence_number < 1 OR
245 NEW_REFERENCES.ci_sequence_number > 999999 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 end if;
251
252 END Check_Constraints;
253
254
255 PROCEDURE Check_Parent_Existance AS
256 BEGIN
257
258 IF (((old_references.dt_alias = new_references.dt_alias) AND
259 (old_references.dai_sequence_number = new_references.dai_sequence_number) AND
260 (old_references.cal_type = new_references.cal_type) AND
261 (old_references.ci_sequence_number = new_references.ci_sequence_number) AND
262 (old_references.offset_dt_alias = new_references.offset_dt_alias) AND
263 (old_references.offset_dai_sequence_number = new_references.offset_dai_sequence_number) AND
264 (old_references.offset_cal_type = new_references.offset_cal_type) AND
265 (old_references.offset_ci_sequence_number = new_references.offset_ci_sequence_number)) OR
266 ((new_references.dt_alias IS NULL) OR
267 (new_references.dai_sequence_number IS NULL) OR
268 (new_references.cal_type IS NULL) OR
269 (new_references.ci_sequence_number IS NULL) OR
270 (new_references.offset_dt_alias IS NULL) OR
271 (new_references.offset_dai_sequence_number IS NULL) OR
272 (new_references.offset_cal_type IS NULL) OR
273 (new_references.offset_ci_sequence_number IS NULL))) THEN
274 NULL;
275 ELSE
276 IF NOT IGS_CA_DA_INST_OFST_PKG.Get_PK_For_Validation (
277 new_references.dt_alias,
278 new_references.dai_sequence_number,
279 new_references.cal_type,
280 new_references.ci_sequence_number,
281 new_references.offset_dt_alias,
282 new_references.offset_dai_sequence_number,
283 new_references.offset_cal_type,
284 new_references.offset_ci_sequence_number
285 ) THEN
286 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
287 IGS_GE_MSG_STACK.ADD;
288 App_Exception.Raise_Exception;
289 END IF;
290
291 END IF;
292
293 IF (((old_references.s_dt_offset_constraint_type = new_references.s_dt_offset_constraint_type)) OR
294 ((new_references.s_dt_offset_constraint_type IS NULL))) THEN
295 NULL;
296 ELSE
297 NULL;
298 END IF;
299
300 END Check_Parent_Existance;
301
302 FUNCTION Get_PK_For_Validation (
303 x_cal_type IN VARCHAR2,
304 x_ci_sequence_number IN NUMBER,
305 x_dt_alias IN VARCHAR2,
306 x_dai_sequence_number IN NUMBER,
307 x_offset_cal_type IN VARCHAR2,
308 x_offset_ci_sequence_number IN NUMBER,
309 x_offset_dt_alias IN VARCHAR2,
310 x_offset_dai_sequence_number IN NUMBER,
311 x_s_dt_offset_constraint_type IN VARCHAR2
312 )RETURN BOOLEAN AS
313
314 CURSOR cur_rowid IS
315 SELECT rowid
316 FROM IGS_CA_DA_INST_OFCNT
317 WHERE cal_type = x_cal_type
318 AND ci_sequence_number = x_ci_sequence_number
319 AND dt_alias = x_dt_alias
320 AND dai_sequence_number = x_dai_sequence_number
321 AND offset_cal_type = x_offset_cal_type
322 AND offset_ci_sequence_number = x_offset_ci_sequence_number
323 AND offset_dt_alias = x_offset_dt_alias
324 AND offset_dai_sequence_number = x_offset_dai_sequence_number
325 AND s_dt_offset_constraint_type = x_s_dt_offset_constraint_type
326 FOR UPDATE NOWAIT;
327
328 lv_rowid cur_rowid%RowType;
329
330 BEGIN
331
332 Open cur_rowid;
333 Fetch cur_rowid INTO lv_rowid;
334 IF (cur_rowid%FOUND) THEN
335 Close cur_rowid;
336 Return (TRUE);
337 ELSE
338 Close cur_rowid;
339 Return (FALSE);
340 END IF;
341
342 END Get_PK_For_Validation;
343
344 PROCEDURE GET_FK_IGS_CA_DA_INST_OFST (
345 x_dt_alias IN VARCHAR2,
346 x_dai_sequence_number IN NUMBER,
347 x_cal_type IN VARCHAR2,
348 x_ci_sequence_number IN NUMBER,
349 x_offset_dt_alias IN VARCHAR2,
350 x_offset_dai_sequence_number IN NUMBER,
351 x_offset_cal_type IN VARCHAR2,
355 CURSOR cur_rowid IS
352 x_offset_ci_sequence_number IN NUMBER
353 ) AS
354
356 SELECT rowid
357 FROM IGS_CA_DA_INST_OFCNT
358 WHERE dt_alias = x_dt_alias
359 AND dai_sequence_number = x_dai_sequence_number
360 AND cal_type = x_cal_type
361 AND ci_sequence_number = x_ci_sequence_number
362 AND offset_dt_alias = x_offset_dt_alias
363 AND offset_dai_sequence_number = x_offset_dai_sequence_number
364 AND offset_cal_type = x_offset_cal_type
365 AND offset_ci_sequence_number = x_offset_ci_sequence_number ;
366
367 lv_rowid cur_rowid%RowType;
368
369 BEGIN
370
371 Open cur_rowid;
372 Fetch cur_rowid INTO lv_rowid;
373 IF (cur_rowid%FOUND) THEN
374 Close cur_rowid;
375 Fnd_Message.Set_Name ('IGS', 'IGS_CA_DAIOC_DAIO_FK');
376 IGS_GE_MSG_STACK.ADD;
377 App_Exception.Raise_Exception;
378 Return;
379 END IF;
380 Close cur_rowid;
381
382 END GET_FK_IGS_CA_DA_INST_OFST;
383
384 --skpandey; Bug#3686538: Stubbed as a part of query optimization
385 PROCEDURE GET_FK_IGS_LOOKUPS_VIEW (
386 x_s_dt_offset_constraint_type IN VARCHAR2
387 ) AS
388 BEGIN
389 NULL;
390 END GET_FK_IGS_LOOKUPS_VIEW;
391
392 PROCEDURE Before_DML (
393 p_action IN VARCHAR2,
394 x_rowid IN VARCHAR2 DEFAULT NULL,
395 x_dt_alias IN VARCHAR2 DEFAULT NULL,
396 x_dai_sequence_number IN NUMBER DEFAULT NULL,
397 x_cal_type IN VARCHAR2 DEFAULT NULL,
398 x_ci_sequence_number IN NUMBER DEFAULT NULL,
399 x_offset_dt_alias IN VARCHAR2 DEFAULT NULL,
400 x_offset_dai_sequence_number IN NUMBER DEFAULT NULL,
401 x_offset_cal_type IN VARCHAR2 DEFAULT NULL,
402 x_offset_ci_sequence_number IN NUMBER DEFAULT NULL,
403 x_s_dt_offset_constraint_type IN VARCHAR2 DEFAULT NULL,
404 x_constraint_condition IN VARCHAR2 DEFAULT NULL,
405 x_constraint_resolution IN NUMBER DEFAULT NULL,
406 x_creation_date IN DATE DEFAULT NULL,
407 x_created_by IN NUMBER DEFAULT NULL,
408 x_last_update_date IN DATE DEFAULT NULL,
409 x_last_updated_by IN NUMBER DEFAULT NULL,
410 x_last_update_login IN NUMBER DEFAULT NULL
411 ) AS
412 BEGIN
413
414 Set_Column_Values (
415 p_action,
416 x_rowid,
417 x_dt_alias,
418 x_dai_sequence_number,
419 x_cal_type,
420 x_ci_sequence_number,
421 x_offset_dt_alias,
422 x_offset_dai_sequence_number,
423 x_offset_cal_type,
424 x_offset_ci_sequence_number,
425 x_s_dt_offset_constraint_type,
426 x_constraint_condition,
427 x_constraint_resolution,
428 x_creation_date,
429 x_created_by,
430 x_last_update_date,
431 x_last_updated_by,
432 x_last_update_login
433 );
434
435 IF (p_action = 'INSERT') THEN
436 -- Call all the procedures related to Before Insert.
437 BeforeRowInsertUpdateDelete1 ( p_inserting => TRUE );
438 IF Get_PK_For_Validation (
439 new_references.cal_type ,
440 new_references.ci_sequence_number ,
441 new_references.dt_alias ,
442 new_references.dai_sequence_number ,
443 new_references.offset_cal_type ,
444 new_references.offset_ci_sequence_number ,
445 new_references.offset_dt_alias ,
446 new_references.offset_dai_sequence_number ,
447 new_references.s_dt_offset_constraint_type ) Then
448 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
449 IGS_GE_MSG_STACK.ADD;
450 App_Exception.Raise_Exception;
451 END IF;
452 CHECK_CONSTRAINTS;
453 Check_Parent_Existance;
454 ELSIF (p_action = 'UPDATE') THEN
455 -- Call all the procedures related to Before Update.
456 BeforeRowInsertUpdateDelete1 ( p_updating => TRUE );
457 CHECK_CONSTRAINTS;
458 Check_Parent_Existance;
459 ELSIF (p_action = 'DELETE') THEN
460 -- Call all the procedures related to Before Delete.
461 BeforeRowInsertUpdateDelete1 ( p_deleting => TRUE );
462 ELSIF (p_action = 'VALIDATE_INSERT') THEN
463 IF Get_PK_For_Validation (
464 new_references.cal_type ,
465 new_references.ci_sequence_number ,
466 new_references.dt_alias ,
467 new_references.dai_sequence_number ,
468 new_references.offset_cal_type ,
469 new_references.offset_ci_sequence_number ,
470 new_references.offset_dt_alias ,
471 new_references.offset_dai_sequence_number ,
472 new_references.s_dt_offset_constraint_type ) Then
473 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
474 IGS_GE_MSG_STACK.ADD;
475 App_Exception.Raise_Exception;
476 END IF;
477 CHECK_CONSTRAINTS;
478 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
479 Check_Constraints;
480 ELSIF (p_action = 'VALIDATE_DELETE') THEN
481 NULL;
482 END IF;
483 END Before_DML;
484
485 PROCEDURE After_DML (
486 p_action IN VARCHAR2,
487 x_rowid IN VARCHAR2
488 ) AS
489 BEGIN
490
491 l_rowid := x_rowid;
492
493 IF (p_action = 'INSERT') THEN
494 -- Call all the procedures related to After Insert.
498 AfterStmtInsertUpdate3 ( p_updating => TRUE );
495 AfterStmtInsertUpdate3 ( p_inserting => TRUE );
496 ELSIF (p_action = 'UPDATE') THEN
497 -- Call all the procedures related to After Update.
499 ELSIF (p_action = 'DELETE') THEN
500 -- Call all the procedures related to After Delete.
501 Null;
502 END IF;
503
504 END After_DML;
505
506 procedure INSERT_ROW (
507 X_ROWID in out NOCOPY VARCHAR2,
508 X_CAL_TYPE in VARCHAR2,
509 X_CI_SEQUENCE_NUMBER in NUMBER,
510 X_DT_ALIAS in VARCHAR2,
511 X_DAI_SEQUENCE_NUMBER in NUMBER,
512 X_OFFSET_CAL_TYPE in VARCHAR2,
513 X_OFFSET_CI_SEQUENCE_NUMBER in NUMBER,
514 X_OFFSET_DT_ALIAS in VARCHAR2,
515 X_OFFSET_DAI_SEQUENCE_NUMBER in NUMBER,
516 X_S_DT_OFFSET_CONSTRAINT_TYPE in VARCHAR2,
517 X_CONSTRAINT_CONDITION in VARCHAR2,
518 X_CONSTRAINT_RESOLUTION in NUMBER,
519 X_MODE in VARCHAR2 default 'R'
520 ) AS
521 cursor C is select ROWID from IGS_CA_DA_INST_OFCNT
522 where CAL_TYPE = X_CAL_TYPE
523 and CI_SEQUENCE_NUMBER = X_CI_SEQUENCE_NUMBER
524 and DT_ALIAS = X_DT_ALIAS
525 and DAI_SEQUENCE_NUMBER = X_DAI_SEQUENCE_NUMBER
526 and OFFSET_CAL_TYPE = X_OFFSET_CAL_TYPE
527 and OFFSET_CI_SEQUENCE_NUMBER = X_OFFSET_CI_SEQUENCE_NUMBER
528 and OFFSET_DT_ALIAS = X_OFFSET_DT_ALIAS
529 and OFFSET_DAI_SEQUENCE_NUMBER = X_OFFSET_DAI_SEQUENCE_NUMBER
530 and S_DT_OFFSET_CONSTRAINT_TYPE = X_S_DT_OFFSET_CONSTRAINT_TYPE;
531 X_LAST_UPDATE_DATE DATE;
532 X_LAST_UPDATED_BY NUMBER;
533 X_LAST_UPDATE_LOGIN NUMBER;
534 begin
535 X_LAST_UPDATE_DATE := SYSDATE;
536 if(X_MODE = 'I') then
537 X_LAST_UPDATED_BY := 1;
538 X_LAST_UPDATE_LOGIN := 0;
539 elsif (X_MODE = 'R') then
540 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
541 if X_LAST_UPDATED_BY is NULL then
542 X_LAST_UPDATED_BY := -1;
543 end if;
544 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
545 if X_LAST_UPDATE_LOGIN is NULL then
546 X_LAST_UPDATE_LOGIN := -1;
547 end if;
548 else
549 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
550 IGS_GE_MSG_STACK.ADD;
551 app_exception.raise_exception;
552 end if;
553 Before_DML (
554 p_action =>'INSERT',
555 x_rowid =>X_ROWID,
556 x_dt_alias =>X_DT_ALIAS,
557 x_dai_sequence_number =>X_DAI_SEQUENCE_NUMBER,
558 x_cal_type =>X_CAL_TYPE,
559 x_ci_sequence_number =>X_CI_SEQUENCE_NUMBER,
560 x_offset_dt_alias =>X_OFFSET_DT_ALIAS,
561 x_offset_dai_sequence_number =>X_OFFSET_DAI_SEQUENCE_NUMBER,
562 x_offset_cal_type =>X_OFFSET_CAL_TYPE,
563 x_offset_ci_sequence_number =>X_OFFSET_CI_SEQUENCE_NUMBER,
564 x_s_dt_offset_constraint_type =>X_S_DT_OFFSET_CONSTRAINT_TYPE,
565 x_constraint_condition =>X_CONSTRAINT_CONDITION,
566 x_constraint_resolution =>X_CONSTRAINT_RESOLUTION,
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 insert into IGS_CA_DA_INST_OFCNT (
574 DT_ALIAS,
575 DAI_SEQUENCE_NUMBER,
576 CAL_TYPE,
577 CI_SEQUENCE_NUMBER,
578 OFFSET_DT_ALIAS,
579 OFFSET_DAI_SEQUENCE_NUMBER,
580 OFFSET_CAL_TYPE,
581 OFFSET_CI_SEQUENCE_NUMBER,
582 S_DT_OFFSET_CONSTRAINT_TYPE,
583 CONSTRAINT_CONDITION,
584 CONSTRAINT_RESOLUTION,
585 CREATION_DATE,
586 CREATED_BY,
587 LAST_UPDATE_DATE,
588 LAST_UPDATED_BY,
589 LAST_UPDATE_LOGIN
590 ) values (
591 NEW_REFERENCES.DT_ALIAS,
592 NEW_REFERENCES.DAI_SEQUENCE_NUMBER,
593 NEW_REFERENCES.CAL_TYPE,
594 NEW_REFERENCES.CI_SEQUENCE_NUMBER,
595 NEW_REFERENCES.OFFSET_DT_ALIAS,
596 NEW_REFERENCES.OFFSET_DAI_SEQUENCE_NUMBER,
597 NEW_REFERENCES.OFFSET_CAL_TYPE,
598 NEW_REFERENCES.OFFSET_CI_SEQUENCE_NUMBER,
599 NEW_REFERENCES.S_DT_OFFSET_CONSTRAINT_TYPE,
600 NEW_REFERENCES.CONSTRAINT_CONDITION,
601 NEW_REFERENCES.CONSTRAINT_RESOLUTION,
602 X_LAST_UPDATE_DATE,
603 X_LAST_UPDATED_BY,
604 X_LAST_UPDATE_DATE,
605 X_LAST_UPDATED_BY,
606 X_LAST_UPDATE_LOGIN
607 );
608
609 open c;
610 fetch c into X_ROWID;
611 if (c%notfound) then
612 close c;
613 raise no_data_found;
614 end if;
615 close c;
616 After_DML (
617 p_action =>'INSERT',
618 x_rowid =>X_ROWID
619 );
620 end INSERT_ROW;
621
622 procedure LOCK_ROW (
623 X_ROWID in VARCHAR2,
624 X_CAL_TYPE in VARCHAR2,
625 X_CI_SEQUENCE_NUMBER in NUMBER,
626 X_DT_ALIAS in VARCHAR2,
627 X_DAI_SEQUENCE_NUMBER in NUMBER,
628 X_OFFSET_CAL_TYPE in VARCHAR2,
629 X_OFFSET_CI_SEQUENCE_NUMBER in NUMBER,
630 X_OFFSET_DT_ALIAS in VARCHAR2,
631 X_OFFSET_DAI_SEQUENCE_NUMBER in NUMBER,
632 X_S_DT_OFFSET_CONSTRAINT_TYPE in VARCHAR2,
633 X_CONSTRAINT_CONDITION in VARCHAR2,
634 X_CONSTRAINT_RESOLUTION in NUMBER
635 ) AS
636 cursor c1 is select
637 CONSTRAINT_CONDITION,
638 CONSTRAINT_RESOLUTION
639 from IGS_CA_DA_INST_OFCNT
640 where ROWID = X_ROWID
641 for update nowait;
642 tlinfo c1%rowtype;
643
644 begin
645 open c1;
646 fetch c1 into tlinfo;
647 if (c1%notfound) then
648 close c1;
652 return;
649 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
650 IGS_GE_MSG_STACK.ADD;
651 app_exception.raise_exception;
653 end if;
654 close c1;
655
656 if ( (tlinfo.CONSTRAINT_CONDITION = X_CONSTRAINT_CONDITION)
657 AND (tlinfo.CONSTRAINT_RESOLUTION = X_CONSTRAINT_RESOLUTION)
658 ) then
659 null;
660 else
661 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
662 IGS_GE_MSG_STACK.ADD;
663 app_exception.raise_exception;
664 end if;
665 return;
666 end LOCK_ROW;
667
668 procedure UPDATE_ROW (
669 X_ROWID in VARCHAR2,
670 X_CAL_TYPE in VARCHAR2,
671 X_CI_SEQUENCE_NUMBER in NUMBER,
672 X_DT_ALIAS in VARCHAR2,
673 X_DAI_SEQUENCE_NUMBER in NUMBER,
674 X_OFFSET_CAL_TYPE in VARCHAR2,
675 X_OFFSET_CI_SEQUENCE_NUMBER in NUMBER,
676 X_OFFSET_DT_ALIAS in VARCHAR2,
677 X_OFFSET_DAI_SEQUENCE_NUMBER in NUMBER,
678 X_S_DT_OFFSET_CONSTRAINT_TYPE in VARCHAR2,
679 X_CONSTRAINT_CONDITION in VARCHAR2,
680 X_CONSTRAINT_RESOLUTION in NUMBER,
681 X_MODE in VARCHAR2 default 'R'
682 ) AS
683 X_LAST_UPDATE_DATE DATE;
684 X_LAST_UPDATED_BY NUMBER;
685 X_LAST_UPDATE_LOGIN NUMBER;
686 begin
687 X_LAST_UPDATE_DATE := SYSDATE;
688 if(X_MODE = 'I') then
689 X_LAST_UPDATED_BY := 1;
690 X_LAST_UPDATE_LOGIN := 0;
691 elsif (X_MODE = 'R') then
692 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
693 if X_LAST_UPDATED_BY is NULL then
694 X_LAST_UPDATED_BY := -1;
695 end if;
696 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
697 if X_LAST_UPDATE_LOGIN is NULL then
698 X_LAST_UPDATE_LOGIN := -1;
699 end if;
700 else
701 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
702 IGS_GE_MSG_STACK.ADD;
703 app_exception.raise_exception;
704 end if;
705 Before_DML (
706 p_action =>'UPDATE',
707 x_rowid =>X_ROWID,
708 x_dt_alias =>X_DT_ALIAS,
709 x_dai_sequence_number =>X_DAI_SEQUENCE_NUMBER,
710 x_cal_type =>X_CAL_TYPE,
711 x_ci_sequence_number =>X_CI_SEQUENCE_NUMBER,
712 x_offset_dt_alias =>X_OFFSET_DT_ALIAS,
713 x_offset_dai_sequence_number =>X_OFFSET_DAI_SEQUENCE_NUMBER,
714 x_offset_cal_type =>X_OFFSET_CAL_TYPE,
715 x_offset_ci_sequence_number =>X_OFFSET_CI_SEQUENCE_NUMBER,
716 x_s_dt_offset_constraint_type =>X_S_DT_OFFSET_CONSTRAINT_TYPE,
717 x_constraint_condition =>X_CONSTRAINT_CONDITION,
718 x_constraint_resolution =>X_CONSTRAINT_RESOLUTION,
719 x_creation_date =>X_LAST_UPDATE_DATE,
720 x_created_by =>X_LAST_UPDATED_BY,
721 x_last_update_date =>X_LAST_UPDATE_DATE,
722 x_last_updated_by =>X_LAST_UPDATED_BY,
723 x_last_update_login =>X_LAST_UPDATE_LOGIN
724 );
725 update IGS_CA_DA_INST_OFCNT set
726 CONSTRAINT_CONDITION = NEW_REFERENCES.CONSTRAINT_CONDITION,
727 CONSTRAINT_RESOLUTION = NEW_REFERENCES.CONSTRAINT_RESOLUTION,
728 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
729 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
730 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
731 where ROWID = X_ROWID
732 ;
733 if (sql%notfound) then
734 raise no_data_found;
735 end if;
736 After_DML (
737 p_action =>'UPDATE',
738 x_rowid =>X_ROWID
739 );
740 end UPDATE_ROW;
741
742 procedure ADD_ROW (
743 X_ROWID in out NOCOPY VARCHAR2,
744 X_CAL_TYPE in VARCHAR2,
745 X_CI_SEQUENCE_NUMBER in NUMBER,
746 X_DT_ALIAS in VARCHAR2,
747 X_DAI_SEQUENCE_NUMBER in NUMBER,
748 X_OFFSET_CAL_TYPE in VARCHAR2,
749 X_OFFSET_CI_SEQUENCE_NUMBER in NUMBER,
750 X_OFFSET_DT_ALIAS in VARCHAR2,
751 X_OFFSET_DAI_SEQUENCE_NUMBER in NUMBER,
752 X_S_DT_OFFSET_CONSTRAINT_TYPE in VARCHAR2,
753 X_CONSTRAINT_CONDITION in VARCHAR2,
754 X_CONSTRAINT_RESOLUTION in NUMBER,
755 X_MODE in VARCHAR2 default 'R'
756 ) AS
757 cursor c1 is select rowid from IGS_CA_DA_INST_OFCNT
758 where CAL_TYPE = X_CAL_TYPE
759 and CI_SEQUENCE_NUMBER = X_CI_SEQUENCE_NUMBER
760 and DT_ALIAS = X_DT_ALIAS
761 and DAI_SEQUENCE_NUMBER = X_DAI_SEQUENCE_NUMBER
762 and OFFSET_CAL_TYPE = X_OFFSET_CAL_TYPE
763 and OFFSET_CI_SEQUENCE_NUMBER = X_OFFSET_CI_SEQUENCE_NUMBER
764 and OFFSET_DT_ALIAS = X_OFFSET_DT_ALIAS
765 and OFFSET_DAI_SEQUENCE_NUMBER = X_OFFSET_DAI_SEQUENCE_NUMBER
766 and S_DT_OFFSET_CONSTRAINT_TYPE = X_S_DT_OFFSET_CONSTRAINT_TYPE
767 ;
768 begin
769 open c1;
770 fetch c1 into X_ROWID;
771 if (c1%notfound) then
772 close c1;
773 INSERT_ROW (
774 X_ROWID,
775 X_CAL_TYPE,
776 X_CI_SEQUENCE_NUMBER,
777 X_DT_ALIAS,
778 X_DAI_SEQUENCE_NUMBER,
779 X_OFFSET_CAL_TYPE,
780 X_OFFSET_CI_SEQUENCE_NUMBER,
781 X_OFFSET_DT_ALIAS,
782 X_OFFSET_DAI_SEQUENCE_NUMBER,
783 X_S_DT_OFFSET_CONSTRAINT_TYPE,
784 X_CONSTRAINT_CONDITION,
785 X_CONSTRAINT_RESOLUTION,
786 X_MODE);
787 return;
788 end if;
789 close c1;
790 UPDATE_ROW (
791 X_ROWID,
792 X_CAL_TYPE,
793 X_CI_SEQUENCE_NUMBER,
794 X_DT_ALIAS,
795 X_DAI_SEQUENCE_NUMBER,
796 X_OFFSET_CAL_TYPE,
797 X_OFFSET_CI_SEQUENCE_NUMBER,
798 X_OFFSET_DT_ALIAS,
799 X_OFFSET_DAI_SEQUENCE_NUMBER,
800 X_S_DT_OFFSET_CONSTRAINT_TYPE,
801 X_CONSTRAINT_CONDITION,
802 X_CONSTRAINT_RESOLUTION,
803 X_MODE);
804 end ADD_ROW;
805
806 procedure DELETE_ROW (X_ROWID in VARCHAR2
807 ) AS
808 begin
809 Before_DML (
810 p_action =>'DELETE',
811 x_rowid =>X_ROWID
812 );
813 delete from IGS_CA_DA_INST_OFCNT
814 where ROWID = X_ROWID;
815 if (sql%notfound) then
816 raise no_data_found;
817 end if;
818 After_DML (
819 p_action =>'DELETE',
820 x_rowid =>X_ROWID
821 );
822 end DELETE_ROW;
823
824 end IGS_CA_DA_INST_OFCNT_PKG;