[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_OS_SEC_EDU_PKG
Source
1 package body IGS_AD_OS_SEC_EDU_PKG as
2 /* $Header: IGSAI42B.pls 115.4 2003/10/30 13:20:32 rghosh ship $ */
3 l_rowid VARCHAR2(25);
4 old_references IGS_AD_OS_SEC_EDU%RowType;
5 new_references IGS_AD_OS_SEC_EDU%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_person_id IN NUMBER DEFAULT NULL,
11 x_sequence_number IN NUMBER DEFAULT NULL,
12 x_result_obtained_yr IN NUMBER DEFAULT NULL,
13 x_os_scndry_edu_qualification IN VARCHAR2 DEFAULT NULL,
14 x_result IN VARCHAR2 DEFAULT NULL,
15 x_candidate_number IN NUMBER DEFAULT NULL,
16 x_school_name IN VARCHAR2 DEFAULT NULL,
17 x_country_cd IN VARCHAR2 DEFAULT NULL,
18 x_comments 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 ) AS
25
26 CURSOR cur_old_ref_values IS
27 SELECT *
28 FROM IGS_AD_OS_SEC_EDU
29 WHERE rowid = x_rowid;
30
31 BEGIN
32
33 l_rowid := x_rowid;
34
35 -- Code for setting the Old and New Reference Values.
36 -- Populate Old Values.
37 Open cur_old_ref_values;
38 Fetch cur_old_ref_values INTO old_references;
39 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
40 Close cur_old_ref_values;
41 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
42 IGS_GE_MSG_STACK.ADD;
43 App_Exception.Raise_Exception;
44 Return;
45 END IF;
46 Close cur_old_ref_values;
47
48 -- Populate New Values.
49 new_references.person_id := x_person_id;
50 new_references.sequence_number := x_sequence_number;
51 new_references.result_obtained_yr := x_result_obtained_yr;
52 new_references.os_scndry_edu_qualification := x_os_scndry_edu_qualification;
53 new_references.result := x_result;
54 new_references.candidate_number := x_candidate_number;
55 new_references.school_name := x_school_name;
56 new_references.country_cd := x_country_cd;
57 new_references.comments := x_comments;
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
69 END Set_Column_Values;
70
71 PROCEDURE BeforeRowInsertUpdate1(
72 p_inserting IN BOOLEAN DEFAULT FALSE,
73 p_updating IN BOOLEAN DEFAULT FALSE,
74 p_deleting IN BOOLEAN DEFAULT FALSE
75 ) AS
76 v_message_name varchar2(30);
77 BEGIN
78 -- Validate Overseas Secondary Education Qualification closed ind.
79 IF p_inserting
80 OR ((old_references.os_scndry_edu_qualification <> new_references.os_scndry_edu_qualification)
81 OR (old_references.os_scndry_edu_qualification IS NULL AND
82 new_references.os_scndry_edu_qualification IS NOT NULL)) THEN
83 IF IGS_AD_VAL_OSE.admp_val_oseq_closed(
84 new_references.os_scndry_edu_qualification,
85 v_message_name) = FALSE THEN
86 Fnd_Message.Set_Name('IGS',v_message_name);
87 IGS_GE_MSG_STACK.ADD;
88 App_Exception.Raise_Exception;
89 END IF;
90 END IF;
91 -- Validate Overseas Secondary Education Qualification IGS_PE_COUNTRY_CD.
92 IF p_inserting
93 OR ((old_references.os_scndry_edu_qualification <> new_references.os_scndry_edu_qualification)
94 OR (old_references.os_scndry_edu_qualification IS NULL AND
95 new_references.os_scndry_edu_qualification IS NOT NULL)
96 OR (old_references.country_cd <> new_references.country_cd)) THEN
97 IF IGS_AD_VAL_OSE.admp_val_ose_qcntry(
98 new_references.os_scndry_edu_qualification,
99 new_references.country_cd,
100 v_message_name) = FALSE THEN
101 Fnd_Message.Set_Name('IGS',v_message_name);
102 IGS_GE_MSG_STACK.ADD;
103 App_Exception.Raise_Exception;
104 END IF;
105 END IF;
106
107
108 END BeforeRowInsertUpdate1;
109
110 procedure Check_Constraints (
111 Column_Name IN VARCHAR2 DEFAULT NULL,
112 Column_Value IN VARCHAR2 DEFAULT NULL
113 )
114 AS
115 BEGIN
116 IF Column_Name is null then
117 NULL;
118 ELSIF upper(Column_Name) = 'OS_SCNDRY_EDU_QUALIFICATION' then
119 new_references.os_scndry_edu_qualification := column_value;
120 ELSIF upper(Column_Name) = 'RESULT' then
121 new_references.result := column_value;
122 ELSIF upper(Column_Name) = 'SCHOOL_NAME' then
123 new_references.school_name := column_value;
124 ELSIF upper(Column_Name) = 'COUNTRY_CD' then
125 new_references.country_cd := column_value;
126 ELSIF upper(Column_Name) = 'SEQUENCE_NUMBER' then
127 new_references.sequence_number := igs_ge_number.to_num(column_value);
128 ELSIF upper(Column_Name) = 'RESULT_OBTAINED_YR' then
129 new_references.result_obtained_yr := igs_ge_number.to_num(column_value);
130 END IF;
131
132 IF upper(Column_Name) = 'OS_SCNDRY_EDU_QUALIFICATION' OR Column_Name IS NULL THEN
133 IF new_references.os_scndry_edu_qualification <> UPPER(new_references.os_scndry_edu_qualification) 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 IF upper(Column_Name) = 'RESULT' OR Column_Name IS NULL THEN
140 IF new_references.result <> UPPER(new_references.result) THEN
141 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
142 IGS_GE_MSG_STACK.ADD;
143 App_Exception.Raise_Exception;
144 END IF;
145 END IF;
146 IF upper(Column_Name) = 'SCHOOL_NAME' OR Column_Name IS NULL THEN
147 IF new_references.school_name <> UPPER(new_references.school_name) THEN
148 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
149 IGS_GE_MSG_STACK.ADD;
150 App_Exception.Raise_Exception;
151 END IF;
152 END IF;
153 IF upper(Column_Name) = 'COUNTRY_CD' OR Column_Name IS NULL THEN
154 IF new_references.country_cd <> UPPER(new_references.country_cd) THEN
155 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
156 IGS_GE_MSG_STACK.ADD;
157 App_Exception.Raise_Exception;
158 END IF;
159 END IF;
160 IF upper(Column_Name) = 'SEQUENCE_NUMBER' OR Column_Name IS NULL THEN
161 IF new_references.sequence_number < 1 OR new_references.sequence_number > 999999 THEN
162 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
163 IGS_GE_MSG_STACK.ADD;
164 App_Exception.Raise_Exception;
165 END IF;
166 END IF;
167 IF upper(Column_Name) = 'RESULT_OBTAINED_YR' OR Column_Name IS NULL THEN
168 IF new_references.result_obtained_yr < 1900 OR new_references.result_obtained_yr > 2050 THEN
169 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
170 IGS_GE_MSG_STACK.ADD;
171 App_Exception.Raise_Exception;
172 END IF;
173 END IF;
174
175 END Check_Constraints;
176
177 PROCEDURE Check_Parent_Existance AS
178 BEGIN
179
180 IF (((old_references.country_cd = new_references.country_cd)) OR
181 ((new_references.country_cd IS NULL))) THEN
182 NULL;
183 ELSE
184 IF NOT IGS_PE_COUNTRY_CD_PKG.Get_PK_For_Validation (
185 new_references.country_cd
186 ) THEN
187 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
188 IGS_GE_MSG_STACK.ADD;
189 App_Exception.Raise_Exception;
190 END IF;
191 END IF;
192
193 IF (((old_references.os_scndry_edu_qualification = new_references.os_scndry_edu_qualification)) OR
194 ((new_references.os_scndry_edu_qualification IS NULL))) THEN
195 NULL;
196 ELSE
197 IF NOT IGS_AD_OS_SEC_EDU_QF_PKG.Get_PK_For_Validation (
198 new_references.os_scndry_edu_qualification,
199 'N'
200 ) THEN
201 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
202 IGS_GE_MSG_STACK.ADD;
203 App_Exception.Raise_Exception;
204 END IF;
205 END IF;
206
207 IF (((old_references.person_id = new_references.person_id)) OR
208 ((new_references.person_id IS NULL))) THEN
209 NULL;
210 ELSE
211 IF NOT IGS_PE_PERSON_PKG.Get_PK_For_Validation (
212 new_references.person_id
213 ) THEN
214 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
215 IGS_GE_MSG_STACK.ADD;
216 App_Exception.Raise_Exception;
217 END IF;
218 END IF;
219
220 END Check_Parent_Existance;
221
222 PROCEDURE Check_Child_Existance AS
223 BEGIN
224
225 IGS_AD_OS_SEC_ED_SUB_PKG.GET_FK_IGS_AD_OS_SEC_EDU (
226 old_references.person_id,
227 old_references.sequence_number
228 );
229
230 END Check_Child_Existance;
231
232 FUNCTION Get_PK_For_Validation (
233 x_person_id IN NUMBER,
234 x_sequence_number IN NUMBER
235 )return BOOLEAN AS
236
237 CURSOR cur_rowid IS
238 SELECT rowid
239 FROM IGS_AD_OS_SEC_EDU
240 WHERE person_id = x_person_id
241 AND sequence_number = x_sequence_number
242 FOR UPDATE NOWAIT;
243
244 lv_rowid cur_rowid%RowType;
245
246 BEGIN
247
248 Open cur_rowid;
249 Fetch cur_rowid INTO lv_rowid;
250 IF (cur_rowid%FOUND) THEN
251 Close cur_rowid;
252 Return(TRUE);
253 ELSE
254 Close cur_rowid;
255 Return(FALSE);
256 END IF;
257
258 END Get_PK_For_Validation;
259
260 PROCEDURE GET_FK_IGS_PE_COUNTRY_CD (
261 x_country_cd IN VARCHAR2
262 ) AS
263
264 CURSOR cur_rowid IS
265 SELECT rowid
266 FROM IGS_AD_OS_SEC_EDU
267 WHERE country_cd = x_country_cd ;
268
269 lv_rowid cur_rowid%RowType;
270
271 BEGIN
272
273 Open cur_rowid;
274 Fetch cur_rowid INTO lv_rowid;
275 IF (cur_rowid%FOUND) THEN
276 Close cur_rowid;
277 Fnd_Message.Set_Name ('IGS', 'IGS_AD_OSE_CNC_FK');
278 IGS_GE_MSG_STACK.ADD;
279 App_Exception.Raise_Exception;
280 Return;
281 END IF;
282 Close cur_rowid;
283
284 END GET_FK_IGS_PE_COUNTRY_CD;
285
286 PROCEDURE GET_FK_IGS_AD_OS_SEC_EDU_QF (
287 x_os_scndry_edu_qualification IN VARCHAR2
288 ) AS
289
290 CURSOR cur_rowid IS
291 SELECT rowid
292 FROM IGS_AD_OS_SEC_EDU
293 WHERE os_scndry_edu_qualification = x_os_scndry_edu_qualification ;
294
295 lv_rowid cur_rowid%RowType;
296
297 BEGIN
298
299 Open cur_rowid;
300 Fetch cur_rowid INTO lv_rowid;
301 IF (cur_rowid%FOUND) THEN
302 Close cur_rowid;
303 Fnd_Message.Set_Name ('IGS', 'IGS_AD_OSE_OSEQ_FK');
304 IGS_GE_MSG_STACK.ADD;
305 App_Exception.Raise_Exception;
306 Return;
307 END IF;
308 Close cur_rowid;
309
310 END GET_FK_IGS_AD_OS_SEC_EDU_QF;
311
312 PROCEDURE GET_FK_IGS_PE_PERSON (
313 x_person_id IN NUMBER
314 ) AS
315
316 CURSOR cur_rowid IS
317 SELECT rowid
318 FROM IGS_AD_OS_SEC_EDU
319 WHERE person_id = x_person_id ;
320
321 lv_rowid cur_rowid%RowType;
322
323 BEGIN
324
325 Open cur_rowid;
326 Fetch cur_rowid INTO lv_rowid;
327 IF (cur_rowid%FOUND) THEN
328 Close cur_rowid;
329 Fnd_Message.Set_Name ('IGS', 'IGS_AD_OSE_PE_FK');
330 IGS_GE_MSG_STACK.ADD;
331 App_Exception.Raise_Exception;
332 Return;
333 END IF;
334 Close cur_rowid;
335
336 END GET_FK_IGS_PE_PERSON;
337
338 PROCEDURE Before_DML (
339 p_action IN VARCHAR2,
340 x_rowid IN VARCHAR2 DEFAULT NULL,
341 x_person_id IN NUMBER DEFAULT NULL,
342 x_sequence_number IN NUMBER DEFAULT NULL,
343 x_result_obtained_yr IN NUMBER DEFAULT NULL,
344 x_os_scndry_edu_qualification IN VARCHAR2 DEFAULT NULL,
345 x_result IN VARCHAR2 DEFAULT NULL,
346 x_candidate_number IN NUMBER DEFAULT NULL,
347 x_school_name IN VARCHAR2 DEFAULT NULL,
348 x_country_cd IN VARCHAR2 DEFAULT NULL,
349 x_comments IN VARCHAR2 DEFAULT NULL,
350 x_creation_date IN DATE DEFAULT NULL,
351 x_created_by IN NUMBER DEFAULT NULL,
352 x_last_update_date IN DATE DEFAULT NULL,
353 x_last_updated_by IN NUMBER DEFAULT NULL,
354 x_last_update_login IN NUMBER DEFAULT NULL
355 ) AS
356 BEGIN
357
358 Set_Column_Values (
359 p_action,
360 x_rowid,
361 x_person_id,
362 x_sequence_number,
363 x_result_obtained_yr,
364 x_os_scndry_edu_qualification,
365 x_result,
366 x_candidate_number,
367 x_school_name,
368 x_country_cd,
369 x_comments,
370 x_creation_date,
371 x_created_by,
372 x_last_update_date,
373 x_last_updated_by,
374 x_last_update_login
375 );
376
377 IF (p_action = 'INSERT') THEN
378 BeforeRowInsertUpdate1 ( p_inserting => TRUE );
379 IF Get_PK_For_Validation (
380 new_references.person_id,
381 new_references.sequence_number
382 ) THEN
383 Fnd_Message.Set_Name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
384 IGS_GE_MSG_STACK.ADD;
385 App_Exception.Raise_Exception;
386 END IF;
387 Check_Constraints;
388 Check_Parent_Existance;
389 ELSIF (p_action = 'UPDATE') THEN
390 BeforeRowInsertUpdate1 ( p_updating => TRUE );
391 Check_Constraints;
392 Check_Parent_Existance;
393 Check_Child_Existance;
394 ELSIF (p_action = 'VALIDATE_INSERT') THEN
395 IF Get_PK_For_Validation (
396 new_references.person_id,
397 new_references.sequence_number
398 ) THEN
399 Fnd_Message.Set_Name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
400 IGS_GE_MSG_STACK.ADD;
401 App_Exception.Raise_Exception;
402 END IF;
403 Check_Constraints;
404 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
405 Check_Constraints;
406 ELSIF (p_action = 'VALIDATE_DELETE') THEN
407 Check_Child_Existance;
408 END IF;
409
410 END Before_DML;
411
412 PROCEDURE After_DML (
413 p_action IN VARCHAR2,
414 x_rowid IN VARCHAR2
415 ) AS
416 BEGIN
417
418 l_rowid := x_rowid;
419
420 END After_DML;
421
422
423 procedure INSERT_ROW (
424 X_ROWID in out NOCOPY VARCHAR2,
425 X_PERSON_ID in NUMBER,
426 X_SEQUENCE_NUMBER in NUMBER,
427 X_RESULT_OBTAINED_YR in NUMBER,
428 X_OS_SCNDRY_EDU_QUALIFICATION in VARCHAR2,
429 X_RESULT in VARCHAR2,
430 X_CANDIDATE_NUMBER in NUMBER,
431 X_SCHOOL_NAME in VARCHAR2,
432 X_COUNTRY_CD in VARCHAR2,
433 X_COMMENTS in VARCHAR2,
434 X_MODE in VARCHAR2 default 'R'
435 ) AS
436 cursor C is select ROWID from IGS_AD_OS_SEC_EDU
437 where PERSON_ID = X_PERSON_ID
438 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER;
439 X_LAST_UPDATE_DATE DATE;
440 X_LAST_UPDATED_BY NUMBER;
441 X_LAST_UPDATE_LOGIN NUMBER;
442 begin
443 X_LAST_UPDATE_DATE := SYSDATE;
444 if(X_MODE = 'I') then
445 X_LAST_UPDATED_BY := 1;
446 X_LAST_UPDATE_LOGIN := 0;
447 elsif (X_MODE = 'R') then
448 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
449 if X_LAST_UPDATED_BY is NULL then
450 X_LAST_UPDATED_BY := -1;
451 end if;
452 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
453 if X_LAST_UPDATE_LOGIN is NULL then
454 X_LAST_UPDATE_LOGIN := -1;
455 end if;
456 else
457 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
458 IGS_GE_MSG_STACK.ADD;
459 app_exception.raise_exception;
460 end if;
461
462
463 Before_DML(
464 p_action=>'INSERT' ,
465 x_rowid=>X_ROWID ,
466 x_person_id => x_person_id ,
467 x_sequence_number => x_sequence_number ,
468 x_result_obtained_yr => x_result_obtained_yr ,
469 x_os_scndry_edu_qualification => x_os_scndry_edu_qualification ,
470 x_result => x_result ,
471 x_candidate_number => x_candidate_number ,
472 x_school_name => x_school_name ,
473 x_country_cd => x_country_cd ,
474 x_comments => x_comments ,
475 x_creation_date=>X_LAST_UPDATE_DATE ,
476 x_created_by=>X_LAST_UPDATED_BY ,
477 x_last_update_date=>X_LAST_UPDATE_DATE ,
478 x_last_updated_by=>X_LAST_UPDATED_BY ,
479 x_last_update_login=> X_LAST_UPDATE_LOGIN
480 );
481
482 insert into IGS_AD_OS_SEC_EDU (
483 PERSON_ID,
484 SEQUENCE_NUMBER,
485 RESULT_OBTAINED_YR,
486 OS_SCNDRY_EDU_QUALIFICATION,
487 RESULT,
488 CANDIDATE_NUMBER,
489 SCHOOL_NAME,
490 COUNTRY_CD,
491 COMMENTS,
492 CREATION_DATE,
493 CREATED_BY,
494 LAST_UPDATE_DATE,
495 LAST_UPDATED_BY,
496 LAST_UPDATE_LOGIN
497 ) values (
498 NEW_REFERENCES.PERSON_ID,
499 NEW_REFERENCES.SEQUENCE_NUMBER,
500 NEW_REFERENCES.RESULT_OBTAINED_YR,
501 NEW_REFERENCES.OS_SCNDRY_EDU_QUALIFICATION,
502 NEW_REFERENCES.RESULT,
503 NEW_REFERENCES.CANDIDATE_NUMBER,
504 NEW_REFERENCES.SCHOOL_NAME,
505 NEW_REFERENCES.COUNTRY_CD,
506 NEW_REFERENCES.COMMENTS,
507 X_LAST_UPDATE_DATE,
508 X_LAST_UPDATED_BY,
509 X_LAST_UPDATE_DATE,
510 X_LAST_UPDATED_BY,
511 X_LAST_UPDATE_LOGIN
512 );
513
514 open c;
515 fetch c into X_ROWID;
516 if (c%notfound) then
517 close c;
518 raise no_data_found;
519 end if;
520 close c;
521
522 After_DML(
523 p_action=>'INSERT',
524 x_rowid=> X_ROWID
525 );
526
527 end INSERT_ROW;
528
529 procedure LOCK_ROW (
530 X_ROWID in VARCHAR2 ,
531 X_PERSON_ID in NUMBER,
532 X_SEQUENCE_NUMBER in NUMBER,
533 X_RESULT_OBTAINED_YR in NUMBER,
534 X_OS_SCNDRY_EDU_QUALIFICATION in VARCHAR2,
535 X_RESULT in VARCHAR2,
536 X_CANDIDATE_NUMBER in NUMBER,
537 X_SCHOOL_NAME in VARCHAR2,
538 X_COUNTRY_CD in VARCHAR2,
539 X_COMMENTS in VARCHAR2
540 ) AS
541 cursor c1 is select
542 RESULT_OBTAINED_YR,
543 OS_SCNDRY_EDU_QUALIFICATION,
544 RESULT,
545 CANDIDATE_NUMBER,
546 SCHOOL_NAME,
547 COUNTRY_CD,
548 COMMENTS
549 from IGS_AD_OS_SEC_EDU
550 WHERE ROWID = X_ROWID for update nowait ;
551 tlinfo c1%rowtype;
552
553 begin
554 open c1;
555 fetch c1 into tlinfo;
556 if (c1%notfound) then
557 close c1;
558 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
559 IGS_GE_MSG_STACK.ADD;
560 app_exception.raise_exception;
561 return;
562 end if;
563 close c1;
564
565 if ( ((tlinfo.RESULT_OBTAINED_YR = X_RESULT_OBTAINED_YR)
566 OR ((tlinfo.RESULT_OBTAINED_YR is null)
567 AND (X_RESULT_OBTAINED_YR is null)))
568 AND ((tlinfo.OS_SCNDRY_EDU_QUALIFICATION = X_OS_SCNDRY_EDU_QUALIFICATION)
569 OR ((tlinfo.OS_SCNDRY_EDU_QUALIFICATION is null)
570 AND (X_OS_SCNDRY_EDU_QUALIFICATION is null)))
571 AND ((tlinfo.RESULT = X_RESULT)
572 OR ((tlinfo.RESULT is null)
573 AND (X_RESULT is null)))
574 AND ((tlinfo.CANDIDATE_NUMBER = X_CANDIDATE_NUMBER)
575 OR ((tlinfo.CANDIDATE_NUMBER is null)
576 AND (X_CANDIDATE_NUMBER is null)))
577 AND ((tlinfo.SCHOOL_NAME = X_SCHOOL_NAME)
578 OR ((tlinfo.SCHOOL_NAME is null)
579 AND (X_SCHOOL_NAME is null)))
580 AND (tlinfo.COUNTRY_CD = X_COUNTRY_CD)
581 AND ((tlinfo.COMMENTS = X_COMMENTS)
582 OR ((tlinfo.COMMENTS is null)
583 AND (X_COMMENTS is null)))
584 ) then
585 null;
586 else
587 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
588 IGS_GE_MSG_STACK.ADD;
589 app_exception.raise_exception;
590 end if;
591 return;
592 end LOCK_ROW;
593
594 procedure UPDATE_ROW (
595 X_ROWID in VARCHAR2 ,
596 X_PERSON_ID in NUMBER,
597 X_SEQUENCE_NUMBER in NUMBER,
598 X_RESULT_OBTAINED_YR in NUMBER,
599 X_OS_SCNDRY_EDU_QUALIFICATION in VARCHAR2,
600 X_RESULT in VARCHAR2,
601 X_CANDIDATE_NUMBER in NUMBER,
602 X_SCHOOL_NAME in VARCHAR2,
603 X_COUNTRY_CD in VARCHAR2,
604 X_COMMENTS in VARCHAR2,
605 X_MODE in VARCHAR2 default 'R'
606 ) As
607 X_LAST_UPDATE_DATE DATE;
608 X_LAST_UPDATED_BY NUMBER;
609 X_LAST_UPDATE_LOGIN NUMBER;
610 begin
611 X_LAST_UPDATE_DATE := SYSDATE;
612 if(X_MODE = 'I') then
613 X_LAST_UPDATED_BY := 1;
614 X_LAST_UPDATE_LOGIN := 0;
615 elsif (X_MODE = 'R') then
616 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
617 if X_LAST_UPDATED_BY is NULL then
618 X_LAST_UPDATED_BY := -1;
619 end if;
620 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
621 if X_LAST_UPDATE_LOGIN is NULL then
622 X_LAST_UPDATE_LOGIN := -1;
623 end if;
624 else
625 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
626 IGS_GE_MSG_STACK.ADD;
627 app_exception.raise_exception;
628 end if;
629
630 Before_DML(
631 p_action=>'UPDATE' ,
632 x_rowid=>X_ROWID ,
633 x_person_id => x_person_id ,
634 x_sequence_number => x_sequence_number ,
635 x_result_obtained_yr => x_result_obtained_yr ,
636 x_os_scndry_edu_qualification => x_os_scndry_edu_qualification ,
637 x_result => x_result ,
638 x_candidate_number => x_candidate_number ,
639 x_school_name => x_school_name ,
640 x_country_cd => x_country_cd ,
641 x_comments => x_comments ,
642 x_creation_date=>X_LAST_UPDATE_DATE ,
643 x_created_by=>X_LAST_UPDATED_BY ,
644 x_last_update_date=>X_LAST_UPDATE_DATE ,
645 x_last_updated_by=>X_LAST_UPDATED_BY ,
646 x_last_update_login=> X_LAST_UPDATE_LOGIN
647 );
648
649 update IGS_AD_OS_SEC_EDU set
650 RESULT_OBTAINED_YR = NEW_REFERENCES.RESULT_OBTAINED_YR,
651 OS_SCNDRY_EDU_QUALIFICATION = NEW_REFERENCES.OS_SCNDRY_EDU_QUALIFICATION,
652 RESULT = NEW_REFERENCES.RESULT,
653 CANDIDATE_NUMBER = NEW_REFERENCES.CANDIDATE_NUMBER,
654 SCHOOL_NAME = NEW_REFERENCES.SCHOOL_NAME,
655 COUNTRY_CD = NEW_REFERENCES.COUNTRY_CD,
656 COMMENTS = NEW_REFERENCES.COMMENTS,
657 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
658 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
659 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
660 where ROWID = X_ROWID ;
661 if (sql%notfound) then
662 raise no_data_found;
663 end if;
664
665 After_DML(
666 p_action=>'UPDATE',
667 x_rowid=> X_ROWID
668 );
669
670 end UPDATE_ROW;
671
672 procedure ADD_ROW (
673 X_ROWID in out NOCOPY VARCHAR2,
674 X_PERSON_ID in NUMBER,
675 X_SEQUENCE_NUMBER in NUMBER,
676 X_RESULT_OBTAINED_YR in NUMBER,
677 X_OS_SCNDRY_EDU_QUALIFICATION in VARCHAR2,
678 X_RESULT in VARCHAR2,
679 X_CANDIDATE_NUMBER in NUMBER,
680 X_SCHOOL_NAME in VARCHAR2,
681 X_COUNTRY_CD in VARCHAR2,
682 X_COMMENTS in VARCHAR2,
683 X_MODE in VARCHAR2 default 'R'
684 ) AS
685 cursor c1 is select rowid from IGS_AD_OS_SEC_EDU
686 where PERSON_ID = X_PERSON_ID
687 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER
688 ;
689
690 begin
691 open c1;
692 fetch c1 into X_ROWID;
693 if (c1%notfound) then
694 close c1;
695 INSERT_ROW (
696 X_ROWID,
697 X_PERSON_ID,
698 X_SEQUENCE_NUMBER,
699 X_RESULT_OBTAINED_YR,
700 X_OS_SCNDRY_EDU_QUALIFICATION,
701 X_RESULT,
702 X_CANDIDATE_NUMBER,
703 X_SCHOOL_NAME,
704 X_COUNTRY_CD,
705 X_COMMENTS,
706 X_MODE);
707 return;
708 end if;
709 close c1;
710 UPDATE_ROW (
711 X_ROWID ,
712 X_PERSON_ID,
713 X_SEQUENCE_NUMBER,
714 X_RESULT_OBTAINED_YR,
715 X_OS_SCNDRY_EDU_QUALIFICATION,
716 X_RESULT,
717 X_CANDIDATE_NUMBER,
718 X_SCHOOL_NAME,
719 X_COUNTRY_CD,
720 X_COMMENTS,
721 X_MODE);
722 end ADD_ROW;
723
724 procedure DELETE_ROW (
725 X_ROWID in VARCHAR2
726 ) AS
727 begin
728
729 Before_DML(
730 p_action=>'DELETE',
731 x_rowid=> X_ROWID
732 );
733
734 delete from IGS_AD_OS_SEC_EDU
735 where ROWID = X_ROWID;
736 if (sql%notfound) then
737 raise no_data_found;
738 end if;
739
740
741 After_DML(
742 p_action=>'DELETE',
743 x_rowid=> X_ROWID
744 );
745
746
747 end DELETE_ROW;
748
749 end IGS_AD_OS_SEC_EDU_PKG;