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