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