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