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