[Home] [Help]
PACKAGE BODY: APPS.OKL_INTEREST_IMP_PVT
Source
1 PACKAGE BODY OKL_INTEREST_IMP_PVT AS
2 /* $Header: OKLRITFB.pls 115.1 2002/02/06 20:32:05 pkm ship $ */
3
4
5
6 PROCEDURE INT_RATE_IMPORT(p_api_version IN NUMBER,
7 p_init_msg_list IN VARCHAR2,
8 x_return_status OUT NOCOPY VARCHAR2,
9 x_msg_count OUT NOCOPY NUMBER,
10 x_msg_data OUT NOCOPY VARCHAR2)
11
12 IS
13
14 l_api_name CONSTANT VARCHAR2(40) := 'INT_RATE_IMPORT';
15 l_api_version CONSTANT NUMBER := 1.0;
16 l_row_count NUMBER;
17 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
18
19 l_idxv_rec_in idxv_rec_type;
20 l_idxv_rec_out idxv_rec_type;
21
22 l_ivev_rec_in ivev_rec_type;
23 l_ivev_rec_out ivev_rec_type;
24
25 l_idiv_tbl_in idiv_tbl_type;
26 l_idiv_tbl_out idiv_tbl_type;
27
28
29 l_init_msg_list VARCHAR2(1);
30 l_msg_count NUMBER;
31 l_msg_data VARCHAR2(2000);
32
33 i NUMBER := 0;
34 l_name OKL_INDICES.name%TYPE;
35 l_idx OKL_INDICES.id%TYPE;
36 l_error_flag VARCHAR2(1);
37
38
39 CURSOR interface_csr IS
40 SELECT id,
41 idi_type,
42 process_flag,
43 index_name,
44 description,
45 value,
46 datetime_valid,
47 datetime_invalid
48 FROM OKL_INDX_INTERFACES_V
49 WHERE process_flag IS NULL
50 ORDER BY idi_type, datetime_valid
51 FOR UPDATE;
52
53
54 CURSOR idx_csr(v_name okl_indices_v.name%TYPE,
55 v_idx_type OKL_INDICES_V.idx_type%TYPE) IS
56 SELECT name,
57 id
58 FROM okl_indices_v
59 WHERE name = v_name
60 AND idx_type = v_idx_type;
61
62
63
64
65
66 BEGIN
67
68
69 x_return_status := OKL_API.G_RET_STS_SUCCESS;
70
71 l_return_status := OKL_API.START_ACTIVITY( l_api_name
72 ,g_pkg_name
73 ,p_init_msg_list
74 ,l_api_version
75 ,p_api_version
76 ,'_PVT'
77 ,l_return_status);
78
79
80 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
81 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
82 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
83 RAISE OKL_API.G_EXCEPTION_ERROR;
84 END IF;
85
86
87
88 FOR interface_csr_rec IN interface_csr
89
90 LOOP
91
92 l_error_flag := 'N';
93
94
95 IF (interface_csr_rec.datetime_valid IS NOT NULL) THEN
96
97
98 IF (interface_csr_rec.datetime_invalid IS NOT NULL) THEN
99
100 IF (interface_csr_rec.datetime_invalid < interface_csr_rec.datetime_valid) THEN
101
102
103 l_error_flag := 'Y';
104
105 END IF;
106
107 END IF;
108
109 IF OKL_INTEREST_MAINT_PVT.overlap_exists( interface_csr_rec.idi_type,
110 interface_csr_rec.index_name,
111 interface_csr_rec.datetime_valid,
112 interface_csr_rec.datetime_invalid) = OKC_API.G_TRUE THEN
113
114 l_error_flag := 'Y';
115
116 END IF;
117
118 ELSE
119
120 l_error_flag := 'Y';
121
122 END IF;
123
124
125 IF (l_error_flag = 'N') THEN
126
127
128 i := i + 1;
129
130 l_idxv_rec_in.name := interface_csr_rec.index_name;
131 l_idxv_rec_in.idx_type := interface_csr_rec.idi_type;
132 l_idxv_rec_in.description := interface_csr_rec.description;
133
134 l_ivev_rec_in.value := interface_csr_rec.value;
135 l_ivev_rec_in.datetime_valid := interface_csr_rec.datetime_valid;
136 l_ivev_rec_in.datetime_invalid := interface_csr_rec.datetime_invalid;
137
138 l_idiv_tbl_in(i).id := interface_csr_rec.id;
139 l_idiv_tbl_in(i).process_flag := 1;
140
141
142 OPEN idx_csr(l_idxv_rec_in.name, l_idxv_rec_in.idx_type);
143 FETCH idx_csr INTO l_name, l_idx;
144 IF idx_csr%NOTFOUND THEN
145
146
147 OKL_INDICES_PUB.create_indices(p_api_version => 1.0,
148 p_init_msg_list => l_init_msg_list,
149 x_return_status => l_return_status,
150 x_msg_count => l_msg_count,
151 x_msg_data => l_msg_data,
152 p_idxv_rec => l_idxv_rec_in,
153 x_idxv_rec => l_idxv_rec_out);
154
155 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
156 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
157
158 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
159 RAISE OKL_API.G_EXCEPTION_ERROR;
160
161 END IF;
162
163 l_ivev_rec_in.idx_id := l_idxv_rec_out.id;
164
165 ELSE
166 l_ivev_rec_in.idx_id := l_idx;
167
168 END IF;
169
170 CLOSE idx_csr;
171
172
173 OKL_INDICES_PUB.create_index_values(p_api_version => 1.0,
174 p_init_msg_list => l_init_msg_list,
175 x_return_status => l_return_status,
176 x_msg_count => l_msg_count,
177 x_msg_data => l_msg_data,
178 p_ivev_rec => l_ivev_rec_in,
179 x_ivev_rec => l_ivev_rec_out);
180
181
182 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
183
184 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
185
186 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
187
188 RAISE OKL_API.G_EXCEPTION_ERROR;
189
190 END IF;
191
192 END IF;
193
194 END LOOP;
195
196
197 -- Update the Index Interface table with process flag = 1.
198
199
200 OKL_INDEX_INTERFACES_PUB.update_index_interfaces(p_api_version => 1.0,
201 p_init_msg_list => l_init_msg_list,
202 x_return_status => l_return_status,
203 x_msg_count => l_msg_count,
204 x_msg_data => l_msg_data,
205 p_idiv_tbl => l_idiv_tbl_in,
206 x_idiv_tbl => l_idiv_tbl_out);
207
208
209 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
210
211 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
212
213 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
214
215 RAISE OKL_API.G_EXCEPTION_ERROR;
216
217 END IF;
218
219
220 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
221
222 COMMIT WORK;
223
224
225 EXCEPTION
226
227 WHEN OKL_API.G_EXCEPTION_ERROR THEN
228
229 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name
230 ,g_pkg_name
231 ,'OKL_API.G_RET_STS_ERROR'
232 ,x_msg_count
233 ,x_msg_data
234 ,'_PVT'
235 );
236
237 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
238
239 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name
240 ,g_pkg_name
241 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
242 ,x_msg_count
243 ,x_msg_data
244 ,'_PVT'
245 );
246
247 WHEN OTHERS THEN
248
249 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name
250 ,g_pkg_name
251 ,'OTHERS'
252 ,x_msg_count
253 ,x_msg_data
254 ,'_PVT'
255 );
256
257 END INT_RATE_IMPORT;
258
259
260 END OKL_INTEREST_IMP_PVT;