[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_AUS_SEC_EDU_PKG
Source
1 package body IGS_AD_AUS_SEC_EDU_PKG AS
2 /* $Header: IGSAI39B.pls 115.5 2003/10/30 13:20:24 rghosh ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_AD_AUS_SEC_EDU%RowType;
6 new_references IGS_AD_AUS_SEC_EDU%RowType;
7
8 PROCEDURE Set_Column_Values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_person_id IN NUMBER DEFAULT NULL,
12 x_sequence_number IN NUMBER DEFAULT NULL,
13 x_state_cd IN VARCHAR2 DEFAULT NULL,
14 x_result_obtained_yr IN NUMBER DEFAULT NULL,
15 x_score IN NUMBER DEFAULT NULL,
16 x_aus_scndry_edu_ass_type IN VARCHAR2 DEFAULT NULL,
17 x_candidate_number IN NUMBER DEFAULT NULL,
18 x_secondary_school_cd 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_AUS_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 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
41 IGS_GE_MSG_STACK.ADD;
42 App_Exception.Raise_Exception;
43 Close cur_old_ref_values;
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.state_cd := x_state_cd;
52 new_references.result_obtained_yr := x_result_obtained_yr;
53 new_references.score := x_score;
54 new_references.aus_scndry_edu_ass_type := x_aus_scndry_edu_ass_type;
55 new_references.candidate_number := x_candidate_number;
56 new_references.secondary_school_cd := x_secondary_school_cd;
57 IF (p_action = 'UPDATE') THEN
58 new_references.creation_date := old_references.creation_date;
59 new_references.created_by := old_references.created_by;
60 ELSE
61 new_references.creation_date := x_creation_date;
62 new_references.created_by := x_created_by;
63 END IF;
64 new_references.last_update_date := x_last_update_date;
65 new_references.last_updated_by := x_last_updated_by;
66 new_references.last_update_login := x_last_update_login;
67
68 END Set_Column_Values;
69
70 PROCEDURE BeforeRowInsertUpdate1(
71 p_inserting IN BOOLEAN DEFAULT FALSE,
72 p_updating IN BOOLEAN DEFAULT FALSE,
73 p_deleting IN BOOLEAN DEFAULT FALSE
74 ) AS
75 v_message_name VARCHAR2(30);
76 BEGIN
77 -- Validate if result_obtained_yr is specified then score and
78 -- IGS_AS_ASSESSMNT_TYP must be specified.
79 IF p_inserting OR p_updating THEN
80 IF IGS_AD_VAL_ASE.admp_val_ase_scoreat(
81 new_references.result_obtained_yr,
82 new_references.score,
83 new_references.aus_scndry_edu_ass_type,
84 v_message_name) = FALSE THEN
85 Fnd_Message.Set_Name('IGS',v_message_name);
86 IGS_GE_MSG_STACK.ADD;
87 App_Exception.Raise_Exception;
88
89
90
91
92
93
94 END IF;
95 END IF;
96 -- Validate that the state_cd is the same as the state_cd of
97 -- the aus_scndry_edu_ass_type state_cd.
98 IF p_inserting
99 OR (old_references.state_cd <> new_references.state_cd)
100 OR (old_references.aus_scndry_edu_ass_type <> new_references.aus_scndry_edu_ass_type)
101 OR (old_references.aus_scndry_edu_ass_type IS NULL AND
102 new_references.aus_scndry_edu_ass_type IS NOT NULL) THEN
103 IF IGS_AD_VAL_ASE.admp_val_ase_atstate(
104 new_references.state_cd,
105 new_references.aus_scndry_edu_ass_type,
106 v_message_name) = FALSE THEN
107 Fnd_Message.Set_Name('IGS',v_message_name);
108 IGS_GE_MSG_STACK.ADD;
109 App_Exception.Raise_Exception;
110 END IF;
111 END IF;
112 -- Validate that the state_cd is the same as the state_cd of
113 -- the secondary_school_cd state_cd.
114 IF p_inserting
115 OR (old_references.state_cd <> new_references.state_cd)
116 OR (old_references.secondary_school_cd <> new_references.secondary_school_cd)
117 OR (old_references.secondary_school_cd IS NULL AND
118 new_references.secondary_school_cd IS NOT NULL) THEN
119 IF IGS_AD_VAL_ASE.admp_val_ase_scstate(
120 new_references.state_cd,
121 new_references.secondary_school_cd,
122 v_message_name) = FALSE THEN
123 Fnd_Message.Set_Name('IGS',v_message_name);
124 IGS_GE_MSG_STACK.ADD;
125
126
127
128
129
130 App_Exception.Raise_Exception;
131
132
133
134
135
136
137 END IF;
138 END IF;
139 -- Validate that the aus_scndry_edu_ass_type in not closed.
140 IF p_inserting
141 OR (old_references.aus_scndry_edu_ass_type <> new_references.aus_scndry_edu_ass_type)
142 OR (old_references.aus_scndry_edu_ass_type IS NULL AND
143 new_references.aus_scndry_edu_ass_type IS NOT NULL) THEN
144 IF IGS_AD_VAL_ASE.admp_val_aseatclosed(
145 new_references.aus_scndry_edu_ass_type,
146 v_message_name) = FALSE THEN
147 Fnd_Message.Set_Name('IGS',v_message_name);
148 IGS_GE_MSG_STACK.ADD;
149 App_Exception.Raise_Exception;
150 END IF;
151 END IF;
152 END BeforeRowInsertUpdate1;
153
154 PROCEDURE Check_Constraints (
155 Column_Name IN VARCHAR2 DEFAULT NULL,
156 Column_Value IN VARCHAR2 DEFAULT NULL
157 ) AS
158 Begin
159 IF column_name is null then
160 NULL;
161 ELSIF upper(Column_name) = 'SEQUENCE_NUMBER' Then
162 new_references.sequence_number := igs_ge_number.to_num(column_value);
163 ELSIF upper(Column_name) = 'RESULT_OBTAINED_YR' Then
164 new_references.result_obtained_yr := igs_ge_number.to_num(column_value);
165 ELSIF upper(Column_name) = 'STATE_CD' Then
166 new_references.state_cd := column_value;
167 ELSIF upper(Column_name) = 'AUS_SCNDRY_EDU_ASS_TYPE' Then
168 new_references.aus_scndry_edu_ass_type := column_value;
169 ELSIF upper(Column_name) = 'SECONDARY_SCHOOL_CD' Then
170 new_references.secondary_school_cd := column_value;
171 ELSIF upper(Column_name) = 'SCORE' Then
172 new_references.score := igs_ge_number.to_num(column_value);
173 ELSIF upper(Column_name) = 'CANDIDATE_NUMBER' Then
174 new_references.candidate_number := igs_ge_number.to_num(column_value);
175 END IF;
176 IF ((UPPER (column_name) = 'SEQUENCE_NUMBER') OR (column_name IS NULL)) THEN
177 IF ((new_references.sequence_number < 1) OR (new_references.sequence_number > 9999999999)) THEN
178 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
179 IGS_GE_MSG_STACK.ADD;
180 App_Exception.Raise_Exception;
181 END IF;
182 END IF;
183 IF ((UPPER (column_name) = 'RESULT_OBTAINED_YR ') OR (column_name IS NULL)) THEN
184 IF ((new_references.result_obtained_yr < 1900) OR (new_references.result_obtained_yr > 2050)) THEN
185 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
186 IGS_GE_MSG_STACK.ADD;
187 App_Exception.Raise_Exception;
188 END IF;
189 END IF;
190 IF ((UPPER (column_name) = 'AUS_SCNDRY_EDU_ASS_TYPE') OR (column_name IS NULL)) THEN
191 IF (new_references.aus_scndry_edu_ass_type <> UPPER (new_references.aus_scndry_edu_ass_type)) THEN
192 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
193 IGS_GE_MSG_STACK.ADD;
194 App_Exception.Raise_Exception;
195 END IF;
196 END IF;
197 IF ((UPPER (column_name) = 'SECONDARY_SCHOOL_CD') OR (column_name IS NULL)) THEN
198 IF (new_references.secondary_school_cd <> UPPER (new_references.secondary_school_cd)) THEN
199 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
200 IGS_GE_MSG_STACK.ADD;
201 App_Exception.Raise_Exception;
202 END IF;
203 END IF;
204 IF ((UPPER (column_name) = 'STATE_CD') OR (column_name IS NULL)) THEN
205 IF (new_references.state_cd <> UPPER (new_references.state_cd)) THEN
206 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
207 IGS_GE_MSG_STACK.ADD;
208 App_Exception.Raise_Exception;
209 END IF;
210 END IF;
211 IF ((UPPER (column_name) = 'SCORE') OR (column_name IS NULL)) THEN
212 IF ((new_references.score < 0) OR (new_references.score > 999.999)) THEN
213 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
214 IGS_GE_MSG_STACK.ADD;
215 App_Exception.Raise_Exception;
216 END IF;
217 END IF;
218 IF ((UPPER (column_name) = 'CANDIDATE_NUMBER') OR (column_name IS NULL)) THEN
219 IF ((new_references.candidate_number < 1) OR (new_references.candidate_number > 999999999999)) THEN
220 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
221 IGS_GE_MSG_STACK.ADD;
222 App_Exception.Raise_Exception;
223 END IF;
224 END IF;
225 End Check_Constraints;
226
227
228 PROCEDURE Check_Parent_Existance AS
229 BEGIN
230
231 IF (((old_references.aus_scndry_edu_ass_type = new_references.aus_scndry_edu_ass_type)) OR
232 ((new_references.aus_scndry_edu_ass_type IS NULL))) THEN
233 NULL;
234 ELSE
235 IF NOT IGS_AD_AUSE_ED_AS_TY_PKG.Get_PK_For_Validation (
236 new_references.aus_scndry_edu_ass_type,
237 'N'
238 ) THEN
239 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
240 IGS_GE_MSG_STACK.ADD;
241 App_Exception.Raise_Exception;
242 END IF;
243 END IF;
244 IF (((old_references.secondary_school_cd = new_references.secondary_school_cd)) OR
245 ((new_references.secondary_school_cd IS NULL))) THEN
246 NULL;
247 ELSE
248 IF NOT IGS_AD_AUS_SEC_ED_SC_PKG.Get_PK_For_Validation (
249 new_references.secondary_school_cd,
250 'N'
251 ) THEN
252 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
253 IGS_GE_MSG_STACK.ADD;
254 App_Exception.Raise_Exception;
255 END IF;
256 END IF;
257 IF (((old_references.person_id = new_references.person_id)) OR
258 ((new_references.person_id IS NULL))) THEN
259 NULL;
260 ELSE
261 IF NOT IGS_PE_PERSON_PKG.Get_PK_For_Validation (
262 new_references.person_id
263 ) THEN
264 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
265 IGS_GE_MSG_STACK.ADD;
266 App_Exception.Raise_Exception;
267 END IF;
268 END IF;
269
270 END Check_Parent_Existance;
271
272 PROCEDURE Check_Child_Existance AS
273 BEGIN
274
275 IGS_AD_AUSE_ED_OT_SC_PKG.GET_FK_IGS_AD_AUS_SEC_EDU (
276 old_references.person_id,
277 old_references.sequence_number
278 );
279
280 IGS_AD_AUS_SEC_ED_SU_PKG.GET_FK_IGS_AD_AUS_SEC_EDU (
281 old_references.person_id,
282 old_references.sequence_number
283 );
284
285 END Check_Child_Existance;
286
287 Function Get_PK_For_Validation (
288
289
290
291
292
293
294
295 x_person_id IN NUMBER,
296
297
298
299
300
301
302
303 x_sequence_number IN NUMBER)
304
305
306
307
308
309
310
311 RETURN BOOLEAN AS
312
313
314
315
316
317
318
319 CURSOR cur_rowid IS
320
321
322
323
324
325
326
327 SELECT rowid
328
329
330
331
332
333
334
335 FROM IGS_AD_AUS_SEC_EDU
336
337
338
339
340
341
342
343 WHERE person_id = x_person_id
344
345
346
347
348
349
350
351 AND sequence_number = x_sequence_number
352
353
354
355
356
357
358
359 FOR UPDATE NOWAIT;
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375 lv_rowid cur_rowid%RowType;
376
377
378
379
380
381
382
383 BEGIN -- Get_PK_For_Validation
384
385
386
387
388
389
390
391 Open cur_rowid;
392
393
394
395
396
397
398
399 Fetch cur_rowid INTO lv_rowid;
400
401
402
403
404
405
406
407 IF (cur_rowid%FOUND) THEN
408
409
410
411
412
413
414
415 Close cur_rowid;
416
417
418
419
420
421
422
423 Return (TRUE);
424
425
426
427
428
429
430
431 ELSE
432
433
434
435
436
437
438
439 Close cur_rowid;
440
441
442
443
444
445
446
447 Return (FALSE);
448
449
450
451
452
453
454
455 END IF;
456
457
458
459
460
461
462
463 END Get_PK_For_Validation;
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479 PROCEDURE GET_FK_IGS_AD_AUSE_ED_AS_TY (
480 x_aus_scndry_edu_ass_type IN VARCHAR2
481 ) AS
482
483 CURSOR cur_rowid IS
484 SELECT rowid
485 FROM IGS_AD_AUS_SEC_EDU
486 WHERE aus_scndry_edu_ass_type = x_aus_scndry_edu_ass_type ;
487
488 lv_rowid cur_rowid%RowType;
489
490 BEGIN
491
492 Open cur_rowid;
493 Fetch cur_rowid INTO lv_rowid;
494 IF (cur_rowid%FOUND) THEN
495 Close cur_rowid;
496 Fnd_Message.Set_Name ('IGS', 'IGS_AD_ASE_ASEAT_FK');
497 IGS_GE_MSG_STACK.ADD;
498 App_Exception.Raise_Exception;
499 Return;
500 END IF;
501 Close cur_rowid;
502
503 END GET_FK_IGS_AD_AUSE_ED_AS_TY;
504
505 PROCEDURE GET_FK_IGS_AD_AUS_SEC_ED_SC (
506 x_secondary_school_cd IN VARCHAR2
507 ) AS
508
509 CURSOR cur_rowid IS
510 SELECT rowid
511 FROM IGS_AD_AUS_SEC_EDU
512 WHERE secondary_school_cd = x_secondary_school_cd ;
513
514 lv_rowid cur_rowid%RowType;
515
516 BEGIN
517
518 Open cur_rowid;
519 Fetch cur_rowid INTO lv_rowid;
520 IF (cur_rowid%FOUND) THEN
521 Close cur_rowid;
522 Fnd_Message.Set_Name ('IGS', 'IGS_AD_ASE_ASES_FK');
523 IGS_GE_MSG_STACK.ADD;
524 App_Exception.Raise_Exception;
525 Return;
526 END IF;
527 Close cur_rowid;
528
529 END GET_FK_IGS_AD_AUS_SEC_ED_SC;
530
531 PROCEDURE GET_FK_IGS_PE_PERSON (
532 x_person_id IN NUMBER
533 ) AS
534
535 CURSOR cur_rowid IS
536 SELECT rowid
537 FROM IGS_AD_AUS_SEC_EDU
538 WHERE person_id = x_person_id ;
539
540 lv_rowid cur_rowid%RowType;
541
542 BEGIN
543
544 Open cur_rowid;
545 Fetch cur_rowid INTO lv_rowid;
546 IF (cur_rowid%FOUND) THEN
547 Close cur_rowid;
548 Fnd_Message.Set_Name ('IGS', 'IGS_AD_ASE_PE_FK');
549 IGS_GE_MSG_STACK.ADD;
550 App_Exception.Raise_Exception;
551 Return;
552 END IF;
553 Close cur_rowid;
554
555 END GET_FK_IGS_PE_PERSON;
556
557 PROCEDURE Before_DML (
558 p_action IN VARCHAR2,
559 x_rowid IN VARCHAR2 DEFAULT NULL,
560 x_person_id IN NUMBER DEFAULT NULL,
561 x_sequence_number IN NUMBER DEFAULT NULL,
562 x_state_cd IN VARCHAR2 DEFAULT NULL,
563 x_result_obtained_yr IN NUMBER DEFAULT NULL,
564 x_score IN NUMBER DEFAULT NULL,
565 x_aus_scndry_edu_ass_type IN VARCHAR2 DEFAULT NULL,
566 x_candidate_number IN NUMBER DEFAULT NULL,
567 x_secondary_school_cd IN VARCHAR2 DEFAULT NULL,
568 x_creation_date IN DATE DEFAULT NULL,
569 x_created_by IN NUMBER DEFAULT NULL,
570 x_last_update_date IN DATE DEFAULT NULL,
571 x_last_updated_by IN NUMBER DEFAULT NULL,
572 x_last_update_login IN NUMBER DEFAULT NULL
573 ) AS
574 BEGIN
575 Set_Column_Values (
576 p_action,
577 x_rowid,
578 x_person_id,
579 x_sequence_number,
580 x_state_cd,
581 x_result_obtained_yr,
582 x_score,
583 x_aus_scndry_edu_ass_type,
584 x_candidate_number,
585 x_secondary_school_cd,
586 x_creation_date,
587 x_created_by,
588 x_last_update_date,
589 x_last_updated_by,
590 x_last_update_login
591 );
592
593 IF (p_action = 'INSERT') THEN
594 -- Call all the procedures related to Before Insert.
595 BeforeRowInsertUpdate1 ( p_inserting => TRUE );
596 IF Get_PK_For_Validation (
597 new_references.person_id,
598 new_references.sequence_number
599 ) THEN
600 Fnd_Message.Set_Name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
601 IGS_GE_MSG_STACK.ADD;
602 App_Exception.Raise_Exception;
603 END IF;
604 Check_Constraints;
605 Check_Parent_Existance;
606 ELSIF (p_action = 'UPDATE') THEN
607 -- Call all the procedures related to Before Update.
608 BeforeRowInsertUpdate1 ( p_updating => TRUE );
609 Check_Constraints;
610 Check_Parent_Existance;
611 ELSIF (p_action = 'DELETE') THEN
612 -- Call all the procedures related to Before Delete.
613 Check_Child_Existance;
614 ELSIF (p_action = 'VALIDATE_INSERT') THEN
615 IF Get_PK_For_Validation (
616 new_references.person_id,
617 new_references.sequence_number
618 ) THEN
619 Fnd_Message.Set_Name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
620 IGS_GE_MSG_STACK.ADD;
621 App_Exception.Raise_Exception;
622 END IF;
623 Check_Constraints;
624 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
625 Check_Constraints;
626 ELSIF (p_action = 'VALIDATE_DELETE') THEN
627 Check_Child_Existance;
628 END IF;
629 END Before_DML;
630
631 PROCEDURE After_DML (
632 p_action IN VARCHAR2,
633 x_rowid IN VARCHAR2
634 ) AS
635 BEGIN
636
637 l_rowid := x_rowid;
638
639 END After_DML;
640
641 procedure INSERT_ROW (
642 X_ROWID in out NOCOPY VARCHAR2,
643 X_PERSON_ID in NUMBER,
644 X_SEQUENCE_NUMBER in NUMBER,
645 X_STATE_CD in VARCHAR2,
646 X_RESULT_OBTAINED_YR in NUMBER,
647 X_SCORE in NUMBER,
648 X_AUS_SCNDRY_EDU_ASS_TYPE in VARCHAR2,
649 X_CANDIDATE_NUMBER in NUMBER,
650 X_SECONDARY_SCHOOL_CD in VARCHAR2,
651 X_MODE in VARCHAR2 default 'R'
652 ) AS
653 cursor C is select ROWID from IGS_AD_AUS_SEC_EDU
654 where PERSON_ID = X_PERSON_ID
655 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER;
656 X_LAST_UPDATE_DATE DATE;
657 X_LAST_UPDATED_BY NUMBER;
658 X_LAST_UPDATE_LOGIN NUMBER;
659 X_REQUEST_ID NUMBER;
660 X_PROGRAM_ID NUMBER;
661 X_PROGRAM_APPLICATION_ID NUMBER;
662 X_PROGRAM_UPDATE_DATE DATE;
663 begin
664 X_LAST_UPDATE_DATE := SYSDATE;
665 if(X_MODE = 'I') then
666 X_LAST_UPDATED_BY := 1;
667 X_LAST_UPDATE_LOGIN := 0;
668 elsif (X_MODE = 'R') then
669 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
670 if X_LAST_UPDATED_BY is NULL then
671 X_LAST_UPDATED_BY := -1;
672 end if;
673 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
674 if X_LAST_UPDATE_LOGIN is NULL then
675 X_LAST_UPDATE_LOGIN := -1;
676 end if;
677 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
678 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
679 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
680 if (X_REQUEST_ID = -1) then
681 X_REQUEST_ID := NULL;
682 X_PROGRAM_ID := NULL;
683 X_PROGRAM_APPLICATION_ID := NULL;
684 X_PROGRAM_UPDATE_DATE := NULL;
685 else
686 X_PROGRAM_UPDATE_DATE := SYSDATE;
687 end if;
688 else
689 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
690 IGS_GE_MSG_STACK.ADD;
691 app_exception.raise_exception;
692 end if;
693
694 Before_DML(
695 p_action=>'INSERT',
696 x_rowid=>X_ROWID,
697 x_aus_scndry_edu_ass_type=>X_AUS_SCNDRY_EDU_ASS_TYPE,
698 x_candidate_number=>X_CANDIDATE_NUMBER,
699 x_person_id=>X_PERSON_ID,
700 x_result_obtained_yr=>X_RESULT_OBTAINED_YR,
701 x_score=>X_SCORE,
702 x_secondary_school_cd=>X_SECONDARY_SCHOOL_CD,
703 x_sequence_number=>X_SEQUENCE_NUMBER,
704 x_state_cd=>X_STATE_CD,
705 x_creation_date=>X_LAST_UPDATE_DATE,
706 x_created_by=>X_LAST_UPDATED_BY,
707 x_last_update_date=>X_LAST_UPDATE_DATE,
708 x_last_updated_by=>X_LAST_UPDATED_BY,
709 x_last_update_login=>X_LAST_UPDATE_LOGIN
710 );
711
712 insert into IGS_AD_AUS_SEC_EDU (
713 PERSON_ID,
714 SEQUENCE_NUMBER,
715 STATE_CD,
716 RESULT_OBTAINED_YR,
717 SCORE,
718 AUS_SCNDRY_EDU_ASS_TYPE,
719 CANDIDATE_NUMBER,
720 SECONDARY_SCHOOL_CD,
721 CREATION_DATE,
722 CREATED_BY,
723 LAST_UPDATE_DATE,
724 LAST_UPDATED_BY,
725 LAST_UPDATE_LOGIN,
726 REQUEST_ID,
727 PROGRAM_ID,
728 PROGRAM_APPLICATION_ID,
729 PROGRAM_UPDATE_DATE
730 ) values (
731 NEW_REFERENCES.PERSON_ID,
732 NEW_REFERENCES.SEQUENCE_NUMBER,
733 NEW_REFERENCES.STATE_CD,
734 NEW_REFERENCES.RESULT_OBTAINED_YR,
735 NEW_REFERENCES.SCORE,
736 NEW_REFERENCES.AUS_SCNDRY_EDU_ASS_TYPE,
737 NEW_REFERENCES.CANDIDATE_NUMBER,
738 NEW_REFERENCES.SECONDARY_SCHOOL_CD,
739 X_LAST_UPDATE_DATE,
740 X_LAST_UPDATED_BY,
741 X_LAST_UPDATE_DATE,
742 X_LAST_UPDATED_BY,
743 X_LAST_UPDATE_LOGIN,
744 X_REQUEST_ID,
745 X_PROGRAM_ID,
746 X_PROGRAM_APPLICATION_ID,
747 X_PROGRAM_UPDATE_DATE
748 );
749
750 open c;
751 fetch c into X_ROWID;
752 if (c%notfound) then
753 close c;
754 raise no_data_found;
755 end if;
756 close c;
757
758 After_DML (
759 p_action => 'DELETE',
760 x_rowid => X_ROWID);
761
762 end INSERT_ROW;
763
764 procedure LOCK_ROW (
765 X_ROWID in VARCHAR2,
766 X_PERSON_ID in NUMBER,
767 X_SEQUENCE_NUMBER in NUMBER,
768 X_STATE_CD in VARCHAR2,
769 X_RESULT_OBTAINED_YR in NUMBER,
770 X_SCORE in NUMBER,
771 X_AUS_SCNDRY_EDU_ASS_TYPE in VARCHAR2,
772 X_CANDIDATE_NUMBER in NUMBER,
773 X_SECONDARY_SCHOOL_CD in VARCHAR2
774 ) AS
775 cursor c1 is select
776 STATE_CD,
777 RESULT_OBTAINED_YR,
778 SCORE,
779 AUS_SCNDRY_EDU_ASS_TYPE,
780 CANDIDATE_NUMBER,
781 SECONDARY_SCHOOL_CD
782 from IGS_AD_AUS_SEC_EDU
783 where ROWID = X_ROWID for update nowait;
784
785 tlinfo c1%rowtype;
786
787 begin
788 open c1;
789 fetch c1 into tlinfo;
790 if (c1%notfound) then
791 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
792 IGS_GE_MSG_STACK.ADD;
793 app_exception.raise_exception;
794 close c1;
795 return;
796 end if;
797 close c1;
798
799 if ( (tlinfo.STATE_CD = X_STATE_CD)
800 AND ((tlinfo.RESULT_OBTAINED_YR = X_RESULT_OBTAINED_YR)
801 OR ((tlinfo.RESULT_OBTAINED_YR is null)
802 AND (X_RESULT_OBTAINED_YR is null)))
803 AND ((tlinfo.SCORE = X_SCORE)
804 OR ((tlinfo.SCORE is null)
805 AND (X_SCORE is null)))
806 AND ((tlinfo.AUS_SCNDRY_EDU_ASS_TYPE = X_AUS_SCNDRY_EDU_ASS_TYPE)
807 OR ((tlinfo.AUS_SCNDRY_EDU_ASS_TYPE is null)
808 AND (X_AUS_SCNDRY_EDU_ASS_TYPE is null)))
809 AND ((tlinfo.CANDIDATE_NUMBER = X_CANDIDATE_NUMBER)
810 OR ((tlinfo.CANDIDATE_NUMBER is null)
811 AND (X_CANDIDATE_NUMBER is null)))
812 AND ((tlinfo.SECONDARY_SCHOOL_CD = X_SECONDARY_SCHOOL_CD)
813 OR ((tlinfo.SECONDARY_SCHOOL_CD is null)
814 AND (X_SECONDARY_SCHOOL_CD is null)))
815 ) then
816 null;
817 else
818 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
819 IGS_GE_MSG_STACK.ADD;
820 app_exception.raise_exception;
821 end if;
822 return;
823 end LOCK_ROW;
824
825 procedure UPDATE_ROW (
826 X_ROWID in VARCHAR2,
827 X_PERSON_ID in NUMBER,
828 X_SEQUENCE_NUMBER in NUMBER,
829 X_STATE_CD in VARCHAR2,
830 X_RESULT_OBTAINED_YR in NUMBER,
831 X_SCORE in NUMBER,
832 X_AUS_SCNDRY_EDU_ASS_TYPE in VARCHAR2,
833 X_CANDIDATE_NUMBER in NUMBER,
834 X_SECONDARY_SCHOOL_CD in VARCHAR2,
835 X_MODE in VARCHAR2 default 'R'
836 ) AS
837 X_LAST_UPDATE_DATE DATE;
838 X_LAST_UPDATED_BY NUMBER;
839 X_LAST_UPDATE_LOGIN NUMBER;
840 X_REQUEST_ID NUMBER;
841 X_PROGRAM_ID NUMBER;
842 X_PROGRAM_APPLICATION_ID NUMBER;
843 X_PROGRAM_UPDATE_DATE DATE;
844 begin
845 X_LAST_UPDATE_DATE := SYSDATE;
846 if(X_MODE = 'I') then
847 X_LAST_UPDATED_BY := 1;
848 X_LAST_UPDATE_LOGIN := 0;
849 elsif (X_MODE = 'R') then
850 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
851 if X_LAST_UPDATED_BY is NULL then
852 X_LAST_UPDATED_BY := -1;
853 end if;
854 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
855 if X_LAST_UPDATE_LOGIN is NULL then
856 X_LAST_UPDATE_LOGIN := -1;
857 end if;
858 else
859 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
860 IGS_GE_MSG_STACK.ADD;
861 app_exception.raise_exception;
862 end if;
863 if (X_MODE = 'R') then
864 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
865 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
866 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
867 if (X_REQUEST_ID = -1) then
868 X_REQUEST_ID := OLD_REFERENCES.REQUEST_ID;
869 X_PROGRAM_ID := OLD_REFERENCES. PROGRAM_ID;
870 X_PROGRAM_APPLICATION_ID := OLD_REFERENCES.PROGRAM_APPLICATION_ID;
871 X_PROGRAM_UPDATE_DATE := OLD_REFERENCES.PROGRAM_UPDATE_DATE;
872 else
873 X_PROGRAM_UPDATE_DATE := SYSDATE;
874 end if;
875 end if;
876
877 Before_DML(
878 p_action=>'UPDATE',
879 x_rowid=>X_ROWID,
880 x_aus_scndry_edu_ass_type=>X_AUS_SCNDRY_EDU_ASS_TYPE,
881 x_candidate_number=>X_CANDIDATE_NUMBER,
882 x_person_id=>X_PERSON_ID,
883 x_result_obtained_yr=>X_RESULT_OBTAINED_YR,
884 x_score=>X_SCORE,
885 x_secondary_school_cd=>X_SECONDARY_SCHOOL_CD,
886 x_sequence_number=>X_SEQUENCE_NUMBER,
887 x_state_cd=>X_STATE_CD,
888 x_creation_date=>X_LAST_UPDATE_DATE,
889 x_created_by=>X_LAST_UPDATED_BY,
890 x_last_update_date=>X_LAST_UPDATE_DATE,
891 x_last_updated_by=>X_LAST_UPDATED_BY,
892 x_last_update_login=>X_LAST_UPDATE_LOGIN
893 );
894
895 update IGS_AD_AUS_SEC_EDU set
896 STATE_CD = NEW_REFERENCES.STATE_CD,
897 RESULT_OBTAINED_YR = NEW_REFERENCES.RESULT_OBTAINED_YR,
898 SCORE = NEW_REFERENCES.SCORE,
899 AUS_SCNDRY_EDU_ASS_TYPE = NEW_REFERENCES.AUS_SCNDRY_EDU_ASS_TYPE,
900 CANDIDATE_NUMBER = NEW_REFERENCES.CANDIDATE_NUMBER,
901 SECONDARY_SCHOOL_CD = NEW_REFERENCES.SECONDARY_SCHOOL_CD,
902 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
903 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
904 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
905 REQUEST_ID = X_REQUEST_ID,
906 PROGRAM_ID = X_PROGRAM_ID,
907 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
908 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE
909 where ROWID = X_ROWID
910 ;
911 if (sql%notfound) then
912 raise no_data_found;
913 end if;
914
915 After_DML (
916 p_action => 'UPDATE',
917 x_rowid => X_ROWID);
918
919 end UPDATE_ROW;
920
921 procedure ADD_ROW (
922 X_ROWID in out NOCOPY VARCHAR2,
923 X_PERSON_ID in NUMBER,
924 X_SEQUENCE_NUMBER in NUMBER,
925 X_STATE_CD in VARCHAR2,
926 X_RESULT_OBTAINED_YR in NUMBER,
927 X_SCORE in NUMBER,
928 X_AUS_SCNDRY_EDU_ASS_TYPE in VARCHAR2,
929 X_CANDIDATE_NUMBER in NUMBER,
930 X_SECONDARY_SCHOOL_CD in VARCHAR2,
931 X_MODE in VARCHAR2 default 'R'
932 ) AS
933 cursor c1 is select rowid from IGS_AD_AUS_SEC_EDU
934 where PERSON_ID = X_PERSON_ID
935 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER
936 ;
937 begin
938 open c1;
939 fetch c1 into X_ROWID;
940 if (c1%notfound) then
941 close c1;
942 INSERT_ROW (
943 X_ROWID,
944 X_PERSON_ID,
945 X_SEQUENCE_NUMBER,
946 X_STATE_CD,
947 X_RESULT_OBTAINED_YR,
948 X_SCORE,
949 X_AUS_SCNDRY_EDU_ASS_TYPE,
950 X_CANDIDATE_NUMBER,
951 X_SECONDARY_SCHOOL_CD,
952 X_MODE);
953 return;
954 end if;
955 close c1;
956 UPDATE_ROW (
957 X_ROWID,
958 X_PERSON_ID,
959 X_SEQUENCE_NUMBER,
960 X_STATE_CD,
961 X_RESULT_OBTAINED_YR,
962 X_SCORE,
963 X_AUS_SCNDRY_EDU_ASS_TYPE,
964 X_CANDIDATE_NUMBER,
965 X_SECONDARY_SCHOOL_CD,
966 X_MODE);
967 end ADD_ROW;
968
969 procedure DELETE_ROW (
970 X_ROWID in VARCHAR2
971 ) AS
972 begin
973
974 Before_DML (
975 p_action => 'DELETE',
976 x_rowid => X_ROWID);
977
978 delete from IGS_AD_AUS_SEC_EDU
979 where ROWID = X_ROWID;
980 if (sql%notfound) then
981 raise no_data_found;
982 end if;
983
984 After_DML (
985 p_action => 'DELETE',
986 x_rowid => X_ROWID);
987
988 end DELETE_ROW;
989
990 end IGS_AD_AUS_SEC_EDU_PKG;