[Home] [Help]
PACKAGE BODY: APPS.IGS_PS_FLD_OF_STUDY_PKG
Source
1 package body IGS_PS_FLD_OF_STUDY_PKG as
2 /* $Header: IGSPI54B.pls 120.1 2006/07/25 15:08:01 sommukhe noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_PS_FLD_OF_STUDY_ALL%RowType;
6 new_references IGS_PS_FLD_OF_STUDY_ALL%RowType;
7
8 PROCEDURE Set_Column_Values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_fos_type_code IN VARCHAR2 DEFAULT NULL,
12 x_field_of_study IN VARCHAR2 DEFAULT NULL,
13 x_description IN VARCHAR2 DEFAULT NULL,
14 x_govt_field_of_study IN VARCHAR2 DEFAULT NULL,
15 x_closed_ind IN VARCHAR2 DEFAULT NULL,
16 x_creation_date IN DATE DEFAULT NULL,
17 x_created_by IN NUMBER DEFAULT NULL,
18 x_last_update_date IN DATE DEFAULT NULL,
19 x_last_updated_by IN NUMBER DEFAULT NULL,
20 x_last_update_login IN NUMBER DEFAULT NULL,
21 x_org_id IN NUMBER DEFAULT NULL
22 ) AS
23
24 CURSOR cur_old_ref_values IS
25 SELECT *
26 FROM IGS_PS_FLD_OF_STUDY_ALL
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.fos_type_code := x_fos_type_code;
48 new_references.field_of_study := x_field_of_study;
49 new_references.description := x_description;
50 new_references.govt_field_of_study := x_govt_field_of_study;
51 new_references.closed_ind := x_closed_ind;
52 new_references.org_id := x_org_id;
53 IF (p_action = 'UPDATE') THEN
54 new_references.creation_date := old_references.creation_date;
55 new_references.created_by := old_references.created_by;
56 ELSE
57 new_references.creation_date := x_creation_date;
58 new_references.created_by := x_created_by;
59 END IF;
60 new_references.last_update_date := x_last_update_date;
61 new_references.last_updated_by := x_last_updated_by;
62 new_references.last_update_login := x_last_update_login;
63
64 END Set_Column_Values;
65
66 PROCEDURE BeforeRowInsertUpdate(
67 p_inserting IN BOOLEAN DEFAULT FALSE,
68 p_updating IN BOOLEAN DEFAULT FALSE
69 ) AS
70 v_message_name VARCHAR2(30);
71 v_description IGS_PS_FLD_OF_STUDY_ALL.description%TYPE;
72 v_govt_field_of_study IGS_PS_FLD_OF_STUDY_ALL.govt_field_of_study%TYPE;
73 v_closed_ind IGS_PS_FLD_OF_STUDY_ALL.closed_ind%TYPE;
74 x_rowid varchar2(25);
75 l_org_id NUMBER(15);
76 CURSOR SPFSH_CUR IS
77 SELECT Rowid
78 FROM IGS_PS_FLD_STDY_HIST_ALL
79 WHERE field_of_study = old_references.field_of_study;
80
81 BEGIN
82 -- Validate government field of study.
83 IF p_inserting OR
84 (p_updating AND
85 ((old_references.govt_field_of_study <> new_references.govt_field_of_study) OR
86 (old_references.closed_ind = 'Y' AND new_references.closed_ind = 'N'))) THEN
87 IF IGS_PS_VAL_FOS.crsp_val_fos_govt (
88 new_references.govt_field_of_study,
89 v_message_name) = FALSE THEN
90 FND_MESSAGE.SET_NAME('IGS',v_message_name);
91 IGS_GE_MSG_STACK.ADD;
92 APP_EXCEPTION.RAISE_EXCEPTION;
93 END IF;
94 END IF;
95 -- Create history record.
96 IF p_updating THEN
97 IF old_references.description <> new_references.description OR
98 old_references.govt_field_of_study <> new_references.govt_field_of_study OR
99 old_references.closed_ind <> new_references.closed_ind THEN
100 SELECT DECODE (old_references.description, new_references.description, NULL,
101 old_references.description),
102 DECODE (old_references.govt_field_of_study, new_references.govt_field_of_study, NULL,
103 old_references.govt_field_of_study),
104 DECODE (old_references.closed_ind, new_references.closed_ind, NULL,
105 old_references.closed_ind)
106 INTO v_description,
107 v_govt_field_of_study,
108 v_closed_ind
109 FROM dual;
110 l_org_id := igs_ge_gen_003.get_org_id;
111 IGS_PS_FLD_STDY_HIST_PKG.Insert_Row(
112 X_ROWID => x_rowid,
113 X_FIELD_OF_STUDY => old_references.field_of_study,
114 X_HIST_START_DT =>old_references.last_update_date,
115 X_HIST_END_DT =>new_references.last_update_date,
116 X_HIST_WHO =>old_references.last_updated_by,
117 X_DESCRIPTION =>v_description,
118 X_GOVT_FIELD_OF_STUDY =>v_govt_field_of_study,
119 X_CLOSED_IND =>v_closed_ind,
120 X_MODE =>'R',
121 X_ORG_ID => l_org_id,
122 X_FOS_TYPE_CODE => old_references.fos_type_code);
123 END IF;
124 END IF;
125
126 END BeforeRowInsertUpdate;
127
128 PROCEDURE Check_Constraints (
129 Column_Name IN VARCHAR2 DEFAULT NULL,
130 Column_Value IN VARCHAR2 DEFAULT NULL
131 )
132 AS
133 BEGIN
134 IF column_name is null then
135 NULL;
136 ELSIF upper(Column_name) = 'CLOSED_IND' then
137 new_references.closed_ind := column_value;
138 ELSIF upper(Column_name) = 'FIELD_OF_STUDY' then
139 new_references.field_of_study := column_value;
140 ELSIF upper(Column_name) = 'GOVT_FIELD_OF_STUDY' then
141 new_references.govt_field_of_study := column_value;
142 END IF;
143
144 END check_constraints;
145
146 PROCEDURE Check_Parent_Existance AS
147 BEGIN
148
149 IF (((old_references.govt_field_of_study = new_references.govt_field_of_study)) OR
150 ((new_references.govt_field_of_study IS NULL))) THEN
151 NULL;
152 ELSE
153 IF NOT IGS_RE_GV_FLD_OF_SDY_PKG.Get_PK_For_Validation (
154 new_references.govt_field_of_study
155 ) THEN
156 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
157 IGS_GE_MSG_STACK.ADD;
158 App_Exception.Raise_Exception;
159 END IF;
160 END IF;
161
162 END Check_Parent_Existance;
163
164 FUNCTION Get_PK_For_Validation (
165 x_field_of_study IN VARCHAR2
166 ) RETURN BOOLEAN AS
167
168 CURSOR cur_rowid IS
169 SELECT rowid
170 FROM IGS_PS_FLD_OF_STUDY_ALL
171 WHERE field_of_study = x_field_of_study;
172
173 lv_rowid cur_rowid%RowType;
174
175 BEGIN
176
177 Open cur_rowid;
178 Fetch cur_rowid INTO lv_rowid;
179 IF (cur_rowid%FOUND) THEN
180 Close cur_rowid;
181 Return (TRUE);
182 ELSE
183 Close cur_rowid;
184 Return (FALSE);
185 END IF;
186 END Get_PK_For_Validation;
187
188 PROCEDURE GET_FK_IGS_RE_GV_FLD_OF_SDY (
189 x_govt_field_of_study IN VARCHAR2
190 ) AS
191
192 CURSOR cur_rowid IS
193 SELECT rowid
194 FROM IGS_PS_FLD_OF_STUDY_ALL
195 WHERE govt_field_of_study = x_govt_field_of_study ;
196
197 lv_rowid cur_rowid%RowType;
198
199 BEGIN
200
201 Open cur_rowid;
202 Fetch cur_rowid INTO lv_rowid;
203 IF (cur_rowid%FOUND) THEN
204 Close cur_rowid;
205 Fnd_Message.Set_Name ('IGS', 'IGS_PS_FOS_GFOS_FK');
206 IGS_GE_MSG_STACK.ADD;
207 App_Exception.Raise_Exception;
208 Return;
209 END IF;
210 Close cur_rowid;
211
212 END GET_FK_IGS_RE_GV_FLD_OF_SDY;
213
214 PROCEDURE Before_DML (
215 p_action IN VARCHAR2,
216 x_rowid IN VARCHAR2 DEFAULT NULL,
217 x_fos_type_code IN VARCHAR2 DEFAULT NULL,
218 x_field_of_study IN VARCHAR2 DEFAULT NULL,
219 x_description IN VARCHAR2 DEFAULT NULL,
220 x_govt_field_of_study IN VARCHAR2 DEFAULT NULL,
221 x_closed_ind IN VARCHAR2 DEFAULT NULL,
222 x_creation_date IN DATE DEFAULT NULL,
223 x_created_by IN NUMBER DEFAULT NULL,
224 x_last_update_date IN DATE DEFAULT NULL,
225 x_last_updated_by IN NUMBER DEFAULT NULL,
226 x_last_update_login IN NUMBER DEFAULT NULL,
227 x_org_id IN NUMBER DEFAULT NULL
228 ) AS
229 BEGIN
230
231 Set_Column_Values (
232 p_action,
233 x_rowid,
234 x_fos_type_code,
235 x_field_of_study,
236 x_description,
237 x_govt_field_of_study,
238 x_closed_ind,
239 x_creation_date,
240 x_created_by,
241 x_last_update_date,
242 x_last_updated_by,
243 x_last_update_login,
244 x_org_id
245 );
246
247 IF (p_action = 'INSERT') THEN
248 -- Call all the procedures related to Before Insert.
249 BeforeRowInsertUpdate( p_inserting => TRUE, p_updating => FALSE );
250 IF Get_PK_For_Validation (
251 new_references.field_of_study
252 ) THEN
253 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
254 IGS_GE_MSG_STACK.ADD;
255 App_Exception.Raise_Exception;
256 END IF;
257 Check_Constraints;
258 Check_Parent_Existance;
259 ELSIF (p_action = 'UPDATE') THEN
260 -- Call all the procedures related to Before Update.
261 BeforeRowInsertUpdate( p_inserting => FALSE, p_updating => TRUE );
262 Check_Constraints;
263 Check_Parent_Existance;
264 ELSIF (p_action = 'VALIDATE_INSERT') THEN
265 IF Get_PK_For_Validation (
266 new_references.field_of_study
267 ) THEN
268 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
269 IGS_GE_MSG_STACK.ADD;
270 App_Exception.Raise_Exception;
271 END IF;
272 Check_Constraints;
273 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
274 Check_Constraints;
275 END IF;
276
277 END Before_DML;
278
279 PROCEDURE After_DML (
280 p_action IN VARCHAR2,
281 x_rowid IN VARCHAR2
282 ) AS
283 BEGIN
284
285 l_rowid := x_rowid;
286
287
288 END After_DML;
289
290 PROCEDURE INSERT_ROW (
291 X_ROWID in out NOCOPY VARCHAR2,
292 X_FOS_TYPE_CODE in VARCHAR2,
293 X_FIELD_OF_STUDY in VARCHAR2,
294 X_DESCRIPTION in VARCHAR2,
295 X_GOVT_FIELD_OF_STUDY in VARCHAR2,
296 X_CLOSED_IND in VARCHAR2,
297 X_MODE in VARCHAR2 default 'R',
298 X_ORG_ID IN NUMBER
299 ) as
300 /***********************************************************************************
301 sbaliga 13-feb-2002 Assigned igs_ge_gen_003.get_org_id to x_org_id
302 in call to before_dml as part of SWCR006 build.
303 *************************************************************************/
304 cursor C is select ROWID from IGS_PS_FLD_OF_STUDY_ALL
305 where FIELD_OF_STUDY = X_FIELD_OF_STUDY;
306 X_LAST_UPDATE_DATE DATE;
307 X_LAST_UPDATED_BY NUMBER;
308 X_LAST_UPDATE_LOGIN NUMBER;
309 begin
310 X_LAST_UPDATE_DATE := SYSDATE;
311 if(X_MODE = 'I') then
312 X_LAST_UPDATED_BY := 1;
313 X_LAST_UPDATE_LOGIN := 0;
314 elsif (X_MODE = 'R') then
315 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
316 if X_LAST_UPDATED_BY is NULL then
317 X_LAST_UPDATED_BY := -1;
318 end if;
319 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
320 if X_LAST_UPDATE_LOGIN is NULL then
321 X_LAST_UPDATE_LOGIN := -1;
322 end if;
323 else
324 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
325 IGS_GE_MSG_STACK.ADD;
326 app_exception.raise_exception;
327 end if;
328
329 Before_DML( p_action => 'INSERT',
330 x_rowid => X_ROWID,
331 x_fos_type_code =>X_FOS_TYPE_CODE,
332 x_field_of_study => X_FIELD_OF_STUDY,
333 x_description => X_DESCRIPTION,
334 x_govt_field_of_study => X_GOVT_FIELD_OF_STUDY,
335 x_closed_ind => NVL(X_CLOSED_IND,'N'),
336 x_creation_date => X_LAST_UPDATE_DATE,
337 x_created_by => X_LAST_UPDATED_BY,
338 x_last_update_date => X_LAST_UPDATE_DATE,
339 x_last_updated_by => X_LAST_UPDATED_BY,
340 x_last_update_login => X_LAST_UPDATE_LOGIN,
341 x_org_id => igs_ge_gen_003.get_org_id
342 );
343
344 insert into IGS_PS_FLD_OF_STUDY_ALL (
345 FOS_TYPE_CODE,
346 FIELD_OF_STUDY,
347 DESCRIPTION,
348 GOVT_FIELD_OF_STUDY,
349 CLOSED_IND,
350 CREATION_DATE,
351 CREATED_BY,
352 LAST_UPDATE_DATE,
353 LAST_UPDATED_BY,
354 LAST_UPDATE_LOGIN,
355 ORG_ID
356 ) values (
357 NEW_REFERENCES.FOS_TYPE_CODE,
358 NEW_REFERENCES.FIELD_OF_STUDY,
359 NEW_REFERENCES.DESCRIPTION,
360 NEW_REFERENCES.GOVT_FIELD_OF_STUDY,
361 NEW_REFERENCES.CLOSED_IND,
362 X_LAST_UPDATE_DATE,
363 X_LAST_UPDATED_BY,
364 X_LAST_UPDATE_DATE,
365 X_LAST_UPDATED_BY,
366 X_LAST_UPDATE_LOGIN,
367 NEW_REFERENCES.ORG_ID
368 );
369
370 open c;
371 fetch c into X_ROWID;
372 if (c%notfound) then
373 close c;
374 raise no_data_found;
375 end if;
376 close c;
377 After_DML(
378 p_action => 'INSERT',
379 x_rowid => X_ROWID
380 );
381 end INSERT_ROW;
382
383 PROCEDURE LOCK_ROW (
384 X_ROWID in VARCHAR2,
385 X_FOS_TYPE_CODE in VARCHAR2,
386 X_FIELD_OF_STUDY in VARCHAR2,
387 X_DESCRIPTION in VARCHAR2,
388 X_GOVT_FIELD_OF_STUDY in VARCHAR2,
389 X_CLOSED_IND in VARCHAR2
390 ) as
391 cursor c1 is select
392 DESCRIPTION,
393 GOVT_FIELD_OF_STUDY,
394 CLOSED_IND
395 from IGS_PS_FLD_OF_STUDY_ALL
396 where ROWID = X_ROWID for update nowait;
397 tlinfo c1%rowtype;
398
399 begin
400 open c1;
401 fetch c1 into tlinfo;
402 if (c1%notfound) then
403 close c1;
404 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
405 IGS_GE_MSG_STACK.ADD;
406 app_exception.raise_exception;
407 return;
408 end if;
409 close c1;
410
411 if ( (tlinfo.DESCRIPTION = X_DESCRIPTION)
412 AND (tlinfo.GOVT_FIELD_OF_STUDY IS NULL OR tlinfo.GOVT_FIELD_OF_STUDY = X_GOVT_FIELD_OF_STUDY)
413 AND (tlinfo.CLOSED_IND = X_CLOSED_IND)
414 ) then
415 null;
416 else
417 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
418 IGS_GE_MSG_STACK.ADD;
419 app_exception.raise_exception;
420 end if;
421 return;
422 end LOCK_ROW;
423
424 PROCEDURE UPDATE_ROW (
425 X_ROWID in VARCHAR2,
426 X_FOS_TYPE_CODE in VARCHAR2,
427 X_FIELD_OF_STUDY in VARCHAR2,
428 X_DESCRIPTION in VARCHAR2,
429 X_GOVT_FIELD_OF_STUDY in VARCHAR2,
430 X_CLOSED_IND in VARCHAR2,
431 X_MODE in VARCHAR2 default 'R'
432 ) as
433 X_LAST_UPDATE_DATE DATE;
434 X_LAST_UPDATED_BY NUMBER;
435 X_LAST_UPDATE_LOGIN NUMBER;
436 begin
437 X_LAST_UPDATE_DATE := SYSDATE;
438 if(X_MODE = 'I') then
439 X_LAST_UPDATED_BY := 1;
440 X_LAST_UPDATE_LOGIN := 0;
441 elsif (X_MODE = 'R') then
442 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
443 if X_LAST_UPDATED_BY is NULL then
444 X_LAST_UPDATED_BY := -1;
445 end if;
446 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
447 if X_LAST_UPDATE_LOGIN is NULL then
448 X_LAST_UPDATE_LOGIN := -1;
449 end if;
450 else
451 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
452 IGS_GE_MSG_STACK.ADD;
453 app_exception.raise_exception;
454 end if;
455
456 Before_DML( p_action => 'UPDATE',
457 x_rowid => X_ROWID,
458 x_fos_type_code =>X_FOS_TYPE_CODE,
459 x_field_of_study => X_FIELD_OF_STUDY,
460 x_description => X_DESCRIPTION,
461 x_govt_field_of_study => X_GOVT_FIELD_OF_STUDY,
462 x_closed_ind => X_CLOSED_IND,
463 x_creation_date => X_LAST_UPDATE_DATE,
464 x_created_by => X_LAST_UPDATED_BY,
465 x_last_update_date => X_LAST_UPDATE_DATE,
466 x_last_updated_by => X_LAST_UPDATED_BY,
467 x_last_update_login => X_LAST_UPDATE_LOGIN
468 );
469 update IGS_PS_FLD_OF_STUDY_ALL set
470 FOS_TYPE_CODE = NEW_REFERENCES.FOS_TYPE_CODE,
471 DESCRIPTION = NEW_REFERENCES.DESCRIPTION,
472 GOVT_FIELD_OF_STUDY = NEW_REFERENCES.GOVT_FIELD_OF_STUDY,
473 CLOSED_IND = NEW_REFERENCES.CLOSED_IND,
474 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
475 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
476 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
477 where ROWID = X_ROWID
478 ;
479 if (sql%notfound) then
480 raise no_data_found;
481 end if;
482 After_DML(
483 p_action => 'UPDATE',
484 x_rowid => X_ROWID
485 );
486 end UPDATE_ROW;
487
488 PROCEDURE ADD_ROW (
489 X_ROWID in out NOCOPY VARCHAR2,
490 X_FOS_TYPE_CODE in VARCHAR2,
491 X_FIELD_OF_STUDY in VARCHAR2,
492 X_DESCRIPTION in VARCHAR2,
493 X_GOVT_FIELD_OF_STUDY in VARCHAR2,
494 X_CLOSED_IND in VARCHAR2,
495 X_MODE in VARCHAR2 default 'R',
496 X_ORG_ID IN NUMBER
497 ) as
498 cursor c1 is select rowid from IGS_PS_FLD_OF_STUDY_ALL
499 where FIELD_OF_STUDY = X_FIELD_OF_STUDY
500 ;
501 begin
502 open c1;
503 fetch c1 into X_ROWID;
504 if (c1%notfound) then
505 close c1;
506 INSERT_ROW (
507 X_ROWID,
508 X_FOS_TYPE_CODE,
509 X_FIELD_OF_STUDY,
510 X_DESCRIPTION,
511 X_GOVT_FIELD_OF_STUDY,
512 X_CLOSED_IND,
513 X_MODE,
514 X_ORG_ID);
515 return;
516 end if;
517 close c1;
518 UPDATE_ROW (
519 X_ROWID,
520 X_FOS_TYPE_CODE,
521 X_FIELD_OF_STUDY,
522 X_DESCRIPTION,
523 X_GOVT_FIELD_OF_STUDY,
524 X_CLOSED_IND,
525 X_MODE);
526 end ADD_ROW;
527
528 end IGS_PS_FLD_OF_STUDY_PKG;