[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_IMP_ACIDX_PKG
Source
1 PACKAGE BODY igs_ad_imp_acidx_pkg AS
2 /* $Header: IGSADA9B.pls 120.0 2005/06/01 14:16:23 appldev noship $ */
3
4 PROCEDURE prgp_imp_acad_indx(
5 errbuf OUT NOCOPY VARCHAR2,
6 retcode OUT NOCOPY NUMBER,
7 p_acadindex_batch_id IN NUMBER,
8 p_org_id IN NUMBER )
9 AS
10 /**********************************************************
11 Created By : jdeekoll
12
13 Date Created By : 06-AUG-2001
14
15 Purpose : For Import Process Academic Index
16
17 Know limitations, enhancements or remarks
18
19 Change History
20
21 Who When What
22 samaresh 02-DEC-2001 Bug # 2097333 : Impact of addition of the waitlist_status field to igs_ad_ps_appl_inst_all
23 (reverse chronological order - newest change first)
24 cdcruz 18-feb-2002 bug 2217104 Admit to future term Enhancement,updated tbh call for
25 new columns being added to IGS_AD_PS_APPL_INST
26 nshee 29-Aug-2002 Bug 2395510 added 6 columns as part of deferments build
27 ***************************************************************/
28
29 -- All records selected from Interface Tables
30
31 CURSOR c_acai_int (p_acadindex_batch_id NUMBER) IS
32 SELECT aii.acadindex_id,
33 aii.person_id,
34 aii.admission_appl_number,
35 aii.nominated_course_cd,
36 aii.sequence_number,
37 aii.admission_index,
38 aii.calculation_date
39 FROM igs_ad_acad_idx_int_all aii,
40 igs_ad_acidx_hdr aih
41 WHERE aii.record_status ='2'
42 AND aih.acadindex_batch_id = p_acadindex_batch_id
43 AND aii.acadindex_batch_id = aih.acadindex_batch_id;
44
45
46 CURSOR c_purge (p_acadindex_batch_id NUMBER) IS
47 SELECT 'x'
48 FROM igs_ad_acad_idx_int_all
49 WHERE acadindex_batch_id = p_acadindex_batch_id;
50
51 -- l_appl_inst_rec c_acai%ROWTYPE;
52 e_resource_busy_exception EXCEPTION;
53 PRAGMA EXCEPTION_INIT(e_resource_busy_exception, -54);
54 -- l_doc_status c_doc_status%ROWTYPE;
55 l_purge c_purge%ROWTYPE;
56
57 l_gather_status VARCHAR2(5);
58 l_industry VARCHAR2(5);
59 l_schema VARCHAR2(30);
60 l_gather_return BOOLEAN;
61 l_owner VARCHAR2(30);
62
63
64
65 l_api_version NUMBER ;
66 l_init_msg_list VARCHAR2(1);
67 l_commit VARCHAR2(1);
68 l_validation_level NUMBER;
69 l_return_status VARCHAR2(10);
70 l_msg_count NUMBER;
71 l_msg_data VARCHAR2(2000);
72
73 -- Local procedure for updating status and error code of interface table
74
75 PROCEDURE update_int_table(p_rec_status NUMBER,
76 p_error_code VARCHAR2,
77 p_acadindex_id NUMBER,
78 p_log_text VARCHAR2) AS
79 BEGIN
80 UPDATE igs_ad_acad_idx_int_all
81 SET record_status = p_rec_status,
82 error_code = p_error_code,
83 error_Text = p_log_text
84 WHERE acadindex_id = p_acadindex_id;
85
86 -- FND_MESSAGE.SET_NAME('IGS',p_log_text);
87 FND_FILE.PUT_LINE(FND_FILE.LOG,p_log_text||' - '||p_acadindex_id);
88
89 END update_int_table;
90
91 BEGIN
92
93 l_api_version := 1.0;
94 l_init_msg_list := FND_API.G_FALSE;
95 l_commit := FND_API.G_FALSE;
96 l_validation_level := FND_API.G_VALID_LEVEL_FULL;
97
98
99 -- set the multi org id
100 igs_ge_gen_003.set_org_id (p_org_id);
101
102 -- Gather statistics for interface table
103 -- by rrengara on 20-jan-2003 bug 2711176
104 --FND_FILE.PUT_LINE(FND_FILE.LOG, '1: Start of procedure');
105 BEGIN
106 l_gather_return := fnd_installation.get_app_info('IGS', l_gather_status, l_industry, l_schema);
107
108 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema, tabname => 'IGS_AD_ACAD_IDX_INT_ALL', cascade => TRUE);
109 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema, tabname => 'IGS_AD_ACIDX_HDR_ALL', cascade => TRUE);
110 EXCEPTION WHEN OTHERS THEN
111 NULL;
112 END;
113 --FND_FILE.PUT_LINE(FND_FILE.LOG, '2: After gathering statistics');
114 -- For each record in the Interface Table do the following
115
116 FOR c_acai_inst_rec IN c_acai_int(p_acadindex_batch_id)
117 LOOP
118 -- Fetch the appropriate record from Production Table(Admission Instance)
119
120 BEGIN
121 -- FND_FILE.PUT_LINE(FND_FILE.LOG, '3: Before calling API');
122 igs_admapplication_pub.record_academic_index(
123 p_api_version => l_api_version,
124 p_init_msg_list => l_init_msg_list,
125 p_commit => l_commit,
126 p_validation_level => l_validation_level,
127 x_return_status => l_return_status,
128 x_msg_count => l_msg_count,
129 x_msg_data => l_msg_data,
130 p_person_id => c_acai_inst_rec.person_id,
131 p_admission_appl_number => c_acai_inst_rec.admission_appl_number,
132 p_nominated_program_cd => c_acai_inst_rec.nominated_course_cd,
133 p_sequence_number => c_acai_inst_rec.sequence_number,
134 p_academic_index => c_acai_inst_rec.admission_index,
135 p_calculation_date => c_acai_inst_rec.calculation_date
136 );
137 -- FND_FILE.PUT_LINE(FND_FILE.LOG, '4: just after calling API.Return status :='||l_return_status);
138 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
139 -- error out the record
140 -- FND_FILE.PUT_LINE(FND_FILE.LOG, '5: If Return status ='||FND_API.G_RET_STS_ERROR||l_msg_data);
141 update_int_table('3','E005',c_acai_inst_rec.acadindex_id,l_msg_data);
142 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
143 -- FND_FILE.PUT_LINE(FND_FILE.LOG, '6: If Return status ='||FND_API.G_RET_STS_UNEXP_ERROR);
144 update_int_table('3','E004',c_acai_inst_rec.acadindex_id,'IGS_GE_UNHANDLED_EXCEPTION');
145 ELSE -- Delete the successful record from Interface Table
146 -- FND_FILE.PUT_LINE(FND_FILE.LOG, '7: If Return status = success then delete the record');
147 DELETE igs_ad_acad_idx_int_all
148 WHERE acadindex_id=c_acai_inst_rec.acadindex_id;
149 END IF;
150
151 EXCEPTION
152 WHEN OTHERS THEN
153 -- FND_FILE.PUT_LINE(FND_FILE.LOG, '8: When any exception from API block');
154 update_int_table('3','E004',c_acai_inst_rec.acadindex_id,'IGS_GE_UNHANDLED_EXCEPTION');
155 END;
156 END LOOP;
157
158 -- Purge the data from Interface Header table whose child records are successfully deleted
159 --FND_FILE.PUT_LINE(FND_FILE.LOG, '9: Before purging the parent record');
160 OPEN c_purge(p_acadindex_batch_id);
161 FETCH c_purge INTO l_purge;
162 IF c_purge%NOTFOUND THEN
163 DELETE igs_ad_acidx_hdr_all
164 WHERE acadindex_batch_id = p_acadindex_batch_id;
165 FND_MESSAGE.SET_NAME('IGS','IGS_AD_ACDX_REC_DELETED');
166 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET||' - '||p_acadindex_batch_id);
167 END IF;
168 --FND_FILE.PUT_LINE(FND_FILE.LOG, '9: After purging the parent record');
169 CLOSE c_purge;
170
171 -- End of Procedure
172 retcode := 0;
173
174 EXCEPTION
175 WHEN OTHERS THEN
176 -- FND_FILE.PUT_LINE(FND_FILE.LOG, '10: When any exception of program');
177 retcode:=2;
178 ERRBUF := FND_MESSAGE.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION')|| sqlerrm;
179 -- FND_FILE.PUT_LINE(FND_FILE.LOG, '11: Before Igs_Ge_Msg_Stack.CONC_EXCEPTION_HNDL');
180 -- Igs_Ge_Msg_Stack.CONC_EXCEPTION_HNDL;
181 -- FND_FILE.PUT_LINE(FND_FILE.LOG, '12: After Igs_Ge_Msg_Stack.CONC_EXCEPTION_HNDL');
182 ROLLBACK;
183 END prgp_imp_acad_indx;
184 END igs_ad_imp_acidx_pkg;