[Home] [Help]
PACKAGE BODY: APPS.IGS_UC_EXP_QUAL_SUM_IMP_PKG
Source
1 PACKAGE BODY igs_uc_exp_qual_sum_imp_pkg AS
2 /* $Header: IGSUC29B.pls 120.1 2006/02/08 19:54:49 anwest noship $ */
3
4
5 PROCEDURE igs_uc_exp_qual_sum_imp(Errbuf OUT NOCOPY Varchar2 , Retcode OUT NOCOPY Varchar2) IS
6 /*************************************************************
7 Created By : vbandaru
8 Date Created By : 23-JAN-2002
9 Purpose : to insert or to update the records into igs_uc_exp_qual_sum table
10
11 Know limitations, enhancements or remarks
12 Change History
13 Who When What
14
15 (reverse chronological order - newest change first)
16 | anwest 18-JAN-2006 Bug# 4950285 R12 Disable OSS Mandate
17 | jchin 8-Mar-2005 Modified for bug #3944420/4215194 - Removed trunc in date
18 | comparisons
19 | rgangara 04-ARP-2002 Changes as per CCR UCCR002 - UCAS 2002 Year of
20 | Entry Requirements. Bug# 2278817
21 | Importing VCE, GCE,SQA, keyskills, pervoeq etc
22 |
23 ***************************************************************/
24
25 --smaddali modified this cursor to add new field ucap.previous for bug 2430178
26 CURSOR c1 IS
27 SELECT ucap.exam_change_date, ucap.a_levels, ucap.as_levels, ucap.winter,
28 ucap.btec, ucap.ilc, ucap.ailc, ucap.ib, ucap.manual, ucap.oeq,
29 ucap.roa, ucap.oss_person_id, ucap.GCE, ucap.VCE, ucap.SQA,ucap.previous,
30 ucap.previousas, ucap.keyskills, ucap.vocational, ucap.prevoeq
31 FROM igs_uc_applicants ucap
32 WHERE ucap.oss_person_id IS NOT NULL;
33
34 CURSOR c2(l_oss_person_id igs_uc_applicants.oss_person_id%TYPE) IS
35 SELECT uexq.exp_qual_sum_id, uexq.person_id, uexq.seq_updated_date,
36 uexq.rowid, uexq.exp_gce, uexq.exp_vce, uexq.winter_a_levels,
37 uexq.prev_a_levels, uexq.prev_as_levels, uexq.sqa, uexq.btec,
38 uexq.ib, uexq.ilc, uexq.ailc, uexq.ksi, uexq.roa, uexq.manual,
39 uexq.oeq, uexq.prev_oeq, uexq.vqi
40 FROM igs_uc_exp_qual_sum uexq
41 WHERE uexq.person_id = l_oss_person_id;
42
43 l_row1 c1%ROWTYPE;
44 l_row2 c2%ROWTYPE;
45 l_rowid ROWID DEFAULT NULL;
46 l_exp_qual_sum_id igs_uc_exp_qual_sum.exp_qual_sum_id%TYPE DEFAULT NULL;
47
48 BEGIN
49
50 --anwest 18-JAN-2006 Bug# 4950285 R12 Disable OSS Mandate
51 IGS_GE_GEN_003.SET_ORG_ID;
52
53 OPEN c1;
54 LOOP
55 FETCH c1 INTO l_row1;
56 EXIT WHEN c1%notfound;
57
58 OPEN c2(l_row1.oss_person_id);
59 FETCH c2 INTO l_row2;
60
61 IF(c2%FOUND) THEN --(1)
62
63 IF NVL(l_row1.exam_change_date, SYSDATE) >
64 l_row2.seq_updated_date THEN --(2)
65
66 --smaddali passing lrow1.previous instead of lrow2.prev_a_levels to field x_prev_a_levels for bug 2430178
67 igs_uc_exp_qual_sum_pkg.update_row(
68 X_ROWID =>l_row2.rowid,
69 X_EXP_QUAL_SUM_ID =>l_row2.exp_qual_sum_id,
70 X_PERSON_ID =>l_row2.person_id,
71 X_EXP_GCE =>l_row1.gce,
72 X_EXP_VCE =>l_row1.vce,
73 X_WINTER_A_LEVELS =>l_row1.winter,
74 X_PREV_A_LEVELS =>l_row1.previous,
75 X_PREV_AS_LEVELS =>l_row1.previousas,
76 X_SQA =>l_row1.sqa,
77 X_BTEC =>l_row1.btec,
78 X_IB =>l_row1.ib,
79 X_ILC =>l_row1.ilc,
80 X_AILC =>l_row1.ailc,
81 X_KSI =>l_row1.keyskills,
82 X_ROA =>l_row1.roa,
83 X_MANUAL =>l_row1.manual,
84 X_OEQ =>l_row1.oeq,
85 X_PREV_OEQ =>l_row1.prevoeq,
86 X_VQI =>l_row1.vocational,
87 X_SEQ_UPDATED_DATE => NVL(l_row1.exam_change_date, SYSDATE),
88 X_MODE =>'R'
89 );
90 END IF; -- end of (2)
91
92 CLOSE c2;
93
94 ELSIF(c2%NOTFOUND) THEN
95 --smaddali passing lrow1.previous instead of NULL to field x_prev_a_levels for bug 2430178
96 igs_uc_exp_qual_sum_pkg.insert_row(
97 X_ROWID =>l_rowid,
98 X_EXP_QUAL_SUM_ID =>l_exp_qual_sum_id,
99 X_PERSON_ID =>l_row1.oss_person_id,
100 X_EXP_GCE =>l_row1.GCE,
101 X_EXP_VCE =>l_row1.VCE,
102 X_WINTER_A_LEVELS =>l_row1.winter,
103 X_PREV_A_LEVELS =>l_row1.previous,
104 X_PREV_AS_LEVELS =>l_row1.previousas,
105 X_SQA =>l_row1.sqa,
106 X_BTEC =>l_row1.btec,
107 X_IB =>l_row1.ib,
108 X_ILC =>l_row1.ilc,
109 X_AILC =>l_row1.ailc,
110 X_KSI =>l_row1.keyskills,
111 X_ROA =>l_row1.roa,
112 X_MANUAL =>l_row1.manual,
113 X_OEQ =>l_row1.oeq,
114 X_PREV_OEQ =>l_row1.prevoeq,
115 X_VQI =>l_row1.vocational,
116 X_SEQ_UPDATED_DATE => NVL(l_row1.exam_change_date,SYSDATE),
117 X_MODE =>'R'
118 );
119
120 END IF; --end of(1)
121
122 IF C2%ISOPEN THEN
123 CLOSE c2;
124 END IF;
125
126 Commit;
127 END LOOP;
128
129 CLOSE c1;
130
131 EXCEPTION
132 WHEN OTHERS THEN
133 Retcode := 2 ;
134 Rollback;
135 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
136 fnd_message.set_token('NAME','IGS_UC_EXP_QUAL_SUM_IMP_PKG.IGS_UC_EXP_QUAL_SUM_IMP');
137 fnd_message.retrieve (Errbuf);
138 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
139 END igs_uc_exp_qual_sum_imp;
140
141 END igs_uc_exp_qual_sum_imp_pkg;