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