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