[Home] [Help]
PACKAGE BODY: APPS.OKL_PROCESS_BUCKETS_PVT
Source
1 PACKAGE BODY OKL_PROCESS_BUCKETS_PVT AS
2 /* $Header: OKLRBUKB.pls 120.3 2005/10/30 03:38:23 appldev noship $ */
3
7 PROCEDURE get_rec (
4 ---------------------------------------------------------------------------
5 -- PROCEDURE get_rec for: OKL_BUCKETS_V
6 ---------------------------------------------------------------------------
8 p_bktv_rec IN bktv_rec_type,
9 p_changes_only IN BOOLEAN DEFAULT FALSE,
10 x_return_status OUT NOCOPY VARCHAR2,
11 x_no_data_found OUT NOCOPY BOOLEAN,
12 x_bktv_rec OUT NOCOPY bktv_rec_type
13 ) IS
14 CURSOR okl_bktv_pk_csr (p_id IN NUMBER) IS
15 SELECT
16 ID,
17 OBJECT_VERSION_NUMBER,
18 IBC_ID,
19 VERSION,
20 LOSS_RATE,
21 START_DATE,
22 NVL(END_DATE,OKL_API.G_MISS_DATE) END_DATE,
23 NVL(COMMENTS,OKL_API.G_MISS_CHAR) COMMENTS,
24 NVL(PROGRAM_ID,OKL_API.G_MISS_NUM) PROGRAM_ID,
25 NVL(REQUEST_ID,OKL_API.G_MISS_NUM) REQUEST_ID,
26 NVL(PROGRAM_APPLICATION_ID,OKL_API.G_MISS_NUM) PROGRAM_APPLICATION_ID,
27 NVL(PROGRAM_UPDATE_DATE,OKL_API.G_MISS_DATE) PROGRAM_UPDATE_DATE,
28 CREATED_BY,
29 LAST_UPDATED_BY,
30 CREATION_DATE,
31 LAST_UPDATE_DATE,
32 NVL(LAST_UPDATE_LOGIN,OKL_API.G_MISS_NUM) LAST_UPDATE_LOGIN
33 FROM OKL_BUCKETS_V
34 WHERE OKL_BUCKETS_V.id = p_id;
35
36 l_okl_bktv_pk okl_bktv_pk_csr%ROWTYPE;
37 l_bktv_rec bktv_rec_type;
38 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
39 BEGIN
40 l_return_status := OKL_API.G_RET_STS_SUCCESS;
41 x_no_data_found := TRUE;
42
43 -- Get current database values
44 OPEN okl_bktv_pk_csr (p_bktv_rec.id);
45 FETCH okl_bktv_pk_csr INTO
46 l_bktv_rec.ID,
47 l_bktv_rec.OBJECT_VERSION_NUMBER,
48 l_bktv_rec.IBC_ID,
49 l_bktv_rec.VERSION,
50 l_bktv_rec.LOSS_RATE,
51 l_bktv_rec.START_DATE,
52 l_bktv_rec.END_DATE,
53 l_bktv_rec.COMMENTS,
54 l_bktv_rec.PROGRAM_ID,
55 l_bktv_rec.REQUEST_ID,
56 l_bktv_rec.PROGRAM_APPLICATION_ID,
57 l_bktv_rec.PROGRAM_UPDATE_DATE,
58 l_bktv_rec.CREATED_BY,
59 l_bktv_rec.LAST_UPDATED_BY,
60 l_bktv_rec.CREATION_DATE,
61 l_bktv_rec.LAST_UPDATE_DATE,
62 l_bktv_rec.LAST_UPDATE_LOGIN;
63 x_no_data_found := okl_bktv_pk_csr%NOTFOUND;
64 CLOSE okl_bktv_pk_csr;
65 /* To take care of the assumption that Everything except the Changed Fields have G_MISS values in them*/
66 /* IF (p_changes_only) THEN
67
68 x_bktv_rec := p_bktv_rec;
69
70 IF l_bktv_rec.IBC_ID = p_bktv_rec.IBC_ID THEN
71 x_bktv_rec.IBC_ID := OKL_API.G_MISS_NUM;
72 END IF;
73
74 IF l_bktv_rec.VERSION = p_bktv_rec.VERSION THEN
75 x_bktv_rec.IBC_ID := OKL_API.G_MISS_NUM;
76 END IF;
77
78 IF l_bktv_rec.LOSS_RATE = p_bktv_rec.LOSS_RATE THEN
79 x_bktv_rec.LOSS_RATE := OKL_API.G_MISS_NUM;
80 END IF;
81
82 IF l_bktv_rec.START_DATE = p_bktv_rec.START_DATE THEN
83 x_bktv_rec.START_DATE := OKL_API.G_MISS_DATE;
84 END IF;
85
86 IF l_bktv_rec.END_DATE = p_bktv_rec.END_DATE THEN
87 x_bktv_rec.END_DATE := OKL_API.G_MISS_DATE;
88 END IF;
89
90 IF l_bktv_rec.COMMENTS = p_bktv_rec.COMMENTS THEN
91 x_bktv_rec.COMMENTS := OKL_API.G_MISS_CHAR;
92 END IF;
93
94 ELSE
95 x_bktv_rec := l_bktv_rec;
96 END IF;
97 */
98 x_bktv_rec := l_bktv_rec;
99 x_return_status := l_return_status;
100 EXCEPTION
101 WHEN OTHERS THEN
102 -- store SQL error message on message stack
103 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
104 p_msg_name => G_UNEXPECTED_ERROR,
105 p_token1 => G_SQLCODE_TOKEN,
106 p_token1_value => sqlcode,
107 p_token2 => G_SQLERRM_TOKEN,
108 p_token2_value => sqlerrm);
109 -- notify UNEXPECTED error for calling API.
110 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
111
112 IF (okl_bktv_pk_csr%ISOPEN) THEN
113 CLOSE okl_bktv_pk_csr;
114 END IF;
115
116 END get_rec;
117
118 ---------------------------------------------------------------------------
119 -- FUNCTION defaults_to_actuals
120 -- This function creates an output record with changed information from the
121 -- input structure and unchanged details from the database
122 ---------------------------------------------------------------------------
123 FUNCTION defaults_to_actuals (
124 p_upd_bktv_rec IN bktv_rec_type,
125 p_db_bktv_rec IN bktv_rec_type
126 ) RETURN bktv_rec_type IS
127 l_bktv_rec bktv_rec_type;
128 BEGIN
129 /* create a temporary record with all relevant details from db and upd records */
130 l_bktv_rec := p_db_bktv_rec;
131
132 IF p_upd_bktv_rec.ibc_id <> OKL_API.G_MISS_NUM THEN
133 l_bktv_rec.ibc_id := p_upd_bktv_rec.ibc_id;
134 END IF;
135
136 IF p_upd_bktv_rec.version <> OKL_API.G_MISS_CHAR THEN
137 l_bktv_rec.version := p_upd_bktv_rec.version;
138 END IF;
139
140 IF p_upd_bktv_rec.loss_rate <> OKL_API.G_MISS_NUM THEN
141 l_bktv_rec.loss_rate := p_upd_bktv_rec.loss_rate;
142 END IF;
143
144 IF p_upd_bktv_rec.start_date <> OKL_API.G_MISS_DATE THEN
145 l_bktv_rec.start_date := p_upd_bktv_rec.start_date;
146 END IF;
147
148 IF p_upd_bktv_rec.end_date IS NULL OR
149 p_upd_bktv_rec.end_date <> OKL_API.G_MISS_DATE
150 THEN
151 l_bktv_rec.end_date := p_upd_bktv_rec.end_date;
152 END IF;
153
154 IF p_upd_bktv_rec.comments <> OKL_API.G_MISS_CHAR THEN
155 l_bktv_rec.comments := p_upd_bktv_rec.comments;
156 END IF;
157
158 RETURN l_bktv_rec;
159 END defaults_to_actuals;
160
161 ---------------------------------------------------------------------------
162 -- PROCEDURE check_updates
163 -- To verify whether the requested changes from the screen are valid or not
164 ---------------------------------------------------------------------------
165 PROCEDURE check_updates (
166 p_upd_bktv_rec IN bktv_rec_type,
167 p_db_bktv_rec IN bktv_rec_type,
168 p_bktv_rec IN bktv_rec_type,
169 x_return_status OUT NOCOPY VARCHAR2,
170 x_msg_data OUT NOCOPY VARCHAR2
171 ) IS
172 l_bktv_rec bktv_rec_type;
173 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
174 l_valid BOOLEAN;
175 l_attrib_tbl okl_accounting_util.overlap_attrib_tbl_type;
176 BEGIN
177 l_return_status := OKL_API.G_RET_STS_SUCCESS;
178 l_bktv_rec := p_bktv_rec;
179
180 IF p_upd_bktv_rec.start_date <> OKL_API.G_MISS_DATE OR
181 p_upd_bktv_rec.end_date <> OKL_API.G_MISS_DATE THEN
182
183 /* call check_overlaps */
184 l_attrib_tbl(1).attribute := 'ibc_id';
185 l_attrib_tbl(1).attrib_type := okl_accounting_util.G_NUMBER;
186 l_attrib_tbl(1).value := l_bktv_rec.ibc_id;
187
188 okl_accounting_util.check_overlaps(p_id => l_bktv_rec.id,
189 p_attrib_tbl => l_attrib_tbl,
190 p_start_date_attribute_name => 'START_DATE',
191 p_start_date => l_bktv_rec.start_date,
192 p_end_date_attribute_name => 'END_DATE',
193 p_end_date => l_bktv_rec.end_date,
194 p_view => 'OKL_BUCKETS_V',
195 x_return_status => l_return_status,
196 x_valid => l_valid);
197
198 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
199 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
200 RAISE G_EXCEPTION_HALT_PROCESSING;
201 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) OR
202 (l_return_status = OKL_API.G_RET_STS_SUCCESS AND
203 l_valid <> TRUE) THEN
204 x_return_status := OKL_API.G_RET_STS_ERROR;
205 RAISE G_EXCEPTION_HALT_PROCESSING;
206 END IF;
207 END IF;
208 x_return_status := l_return_status;
209 EXCEPTION
210 WHEN G_EXCEPTION_HALT_PROCESSING THEN
211 -- no processing necessary; validation can continue
212 -- with the next column
213 NULL;
214
215 WHEN OTHERS THEN
216 -- store SQL error message on message stack for caller
217 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
218 p_msg_name => G_UNEXPECTED_ERROR,
219 p_token1 => G_SQLCODE_TOKEN,
220 p_token1_value => sqlcode,
221 p_token2 => G_SQLERRM_TOKEN,
222 p_token2_value => sqlerrm );
223 x_msg_data := 'Unexpected Database Error';
224 -- notify caller of an UNEXPECTED error
225 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
226
227 END check_updates;
228
229 ---------------------------------------------------------------------------
230 -- PROCEDURE create_buckets for: OKL_BUCKETS_V
231 ---------------------------------------------------------------------------
232 PROCEDURE create_buckets(p_api_version IN NUMBER,
233 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
234 x_return_status OUT NOCOPY VARCHAR2,
235 x_msg_count OUT NOCOPY NUMBER,
236 x_msg_data OUT NOCOPY VARCHAR2,
237 p_bktv_rec IN bktv_rec_type,
238 x_bktv_rec OUT NOCOPY bktv_rec_type ) IS
239
240 l_api_version CONSTANT NUMBER := 1;
241 l_api_name CONSTANT VARCHAR2(30) := 'create_buckets';
242 -- viselvar fixed Bug 4016263. Changed to_date() to trunc()
243 l_sysdate DATE := trunc(SYSDATE);
244 -- Bug 4016263 Bug Fix end.
245 l_db_bktv_rec bktv_rec_type; /* database copy */
246 l_upd_bktv_rec bktv_rec_type; /* input copy */
247 l_bktv_rec bktv_rec_type; /* latest with the retained changes */
248 l_tmp_bktv_rec bktv_rec_type; /* for any other purposes */
249 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
250 l_new_version VARCHAR2(100);
251 l_attrib_tbl okl_accounting_util.overlap_attrib_tbl_type;
252 BEGIN
253 l_return_status := OKL_API.G_RET_STS_SUCCESS;
254 l_upd_bktv_rec := p_bktv_rec;
255
256 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
257 p_pkg_name => G_PKG_NAME,
258 p_init_msg_list => p_init_msg_list,
259 l_api_version => l_api_version,
260 p_api_version => p_api_version,
261 p_api_type => '_PVT',
262 x_return_status => l_return_status);
263 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
264 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
265 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
266 RAISE OKL_API.G_EXCEPTION_ERROR;
267 END IF;
268
269 -- for new version
270 -- create a temporary record with all relevant details from db and upd records
271 l_bktv_rec := defaults_to_actuals(p_upd_bktv_rec => l_upd_bktv_rec,
272 p_db_bktv_rec => l_db_bktv_rec);
273
274 IF l_upd_bktv_rec.start_date = OKL_API.G_MISS_DATE THEN
275 l_bktv_rec.start_date := l_sysdate;
276 END IF;
277
278 l_attrib_tbl(1).attribute := 'ibc_id';
279 l_attrib_tbl(1).attrib_type := okl_accounting_util.G_NUMBER;
280 l_attrib_tbl(1).value := l_bktv_rec.ibc_id;
281
282 okl_accounting_util.get_version(
283 p_attrib_tbl => l_attrib_tbl,
284 p_cur_version => l_bktv_rec.version,
285 p_end_date_attribute_name => 'END_DATE',
286 p_end_date => l_bktv_rec.end_date,
287 p_view => 'OKL_BUCKETS_V',
288 x_return_status => l_return_status,
289 x_new_version => l_new_version);
290
291 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
292 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
293 ELSE
294 l_bktv_rec.version := l_new_version;
295 END IF;
296
297 l_bktv_rec.id := OKL_API.G_MISS_NUM;
298
299 -- call verify changes to update the database
300 IF l_bktv_rec.end_date > l_db_bktv_rec.end_date THEN
301 check_updates(p_upd_bktv_rec => l_upd_bktv_rec,
302 p_db_bktv_rec => l_db_bktv_rec,
303 p_bktv_rec => l_bktv_rec,
304 x_return_status => l_return_status,
305 x_msg_data => x_msg_data);
306 IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
307 RAISE OKL_API.G_EXCEPTION_ERROR;
308 ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
309 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
310 END IF;
311 END IF;
312
313 /* public api to insert streamtype */
314 OKL_BUCKETS_PUB.INSERT_BUCKETS(p_api_version => l_api_version,
315 p_init_msg_list => p_init_msg_list,
316 x_return_status => l_return_status,
317 x_msg_count => x_msg_count,
318 x_msg_data => x_msg_data,
319 p_bktv_rec => l_bktv_rec,
320 x_bktv_rec => x_bktv_rec);
321
322 IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
323 RAISE OKL_API.G_EXCEPTION_ERROR;
324 ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
325 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
326 END IF;
327
328 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
329 x_msg_data => x_msg_data);
330 x_return_status := l_return_status;
331 EXCEPTION
332 WHEN OKL_API.G_EXCEPTION_ERROR THEN
333 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
334 p_pkg_name => G_PKG_NAME,
335 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
336 x_msg_count => x_msg_count,
337 x_msg_data => x_msg_data,
338 p_api_type => '_PVT');
342 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
339 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
340 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
341 p_pkg_name => G_PKG_NAME,
343 x_msg_count => x_msg_count,
344 x_msg_data => x_msg_data,
345 p_api_type => '_PVT');
346 WHEN OTHERS THEN
347 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
348 p_pkg_name => G_PKG_NAME,
349 p_exc_name => 'OTHERS',
350 x_msg_count => x_msg_count,
351 x_msg_data => x_msg_data,
352 p_api_type => '_PVT');
353
354 END create_buckets;
355
356 ---------------------------------------------------------------------------
357 -- PROCEDURE update_buckets for: OKL_BUCKETS_V
358 ---------------------------------------------------------------------------
359 PROCEDURE update_buckets(p_api_version IN NUMBER,
360 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
361 x_return_status OUT NOCOPY VARCHAR2,
362 x_msg_count OUT NOCOPY NUMBER,
363 x_msg_data OUT NOCOPY VARCHAR2,
364 p_bktv_rec IN bktv_rec_type,
365 x_bktv_rec OUT NOCOPY bktv_rec_type
366 ) IS
367 l_api_version CONSTANT NUMBER := 1;
368 l_api_name CONSTANT VARCHAR2(30) := 'update_buckets';
369 l_no_data_found BOOLEAN := TRUE;
370 l_valid BOOLEAN := TRUE;
371 -- viselvar fixed Bug 4016263. Changed to_date() to trunc(). Bug Fix Start
372 l_oldversion_enddate DATE := trunc(SYSDATE);
373 l_sysdate DATE := trunc(SYSDATE);
374 --Bug 4016263 Bug Fix end.
375 l_db_bktv_rec bktv_rec_type; /* database copy */
376 l_upd_bktv_rec bktv_rec_type; /* input copy */
377 l_bktv_rec bktv_rec_type; /* latest with the retained changes */
378 l_tmp_bktv_rec bktv_rec_type; /* for any other purposes */
379 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
380 l_action VARCHAR2(1);
381 l_new_version VARCHAR2(100);
382 l_attrib_tbl okl_accounting_util.overlap_attrib_tbl_type;
383 BEGIN
384 l_return_status := OKL_API.G_RET_STS_SUCCESS;
385 l_upd_bktv_rec := p_bktv_rec;
386
387 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
388 p_pkg_name => G_PKG_NAME,
389 p_init_msg_list => p_init_msg_list,
390 l_api_version => l_api_version,
391 p_api_version => p_api_version,
392 p_api_type => '_PVT',
393 x_return_status => l_return_status);
394 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
395 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
396 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
397 RAISE OKL_API.G_EXCEPTION_ERROR;
398 END IF;
399 /*
400 -- retain the details that has been changed only
401 get_rec(p_bktv_rec => p_bktv_rec,
402 p_changes_only => TRUE,
403 x_return_status => l_return_status,
404 x_no_data_found => l_no_data_found,
405 x_bktv_rec => l_upd_bktv_rec);
406
407 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
408 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
409 END IF;
410
411 IF NOT l_no_data_found THEN
412 -- check for start date greater than sysdate
413 IF to_date(l_upd_bktv_rec.start_date, 'DD/MM/YYYY') <> to_date(OKL_API.G_MISS_DATE, 'DD/MM/YYYY') AND
414 to_date(l_upd_bktv_rec.start_date,'DD/MM/YYYY') < l_sysdate THEN
415 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
416 p_msg_name => G_START_DATE);
417 RAISE OKL_API.G_EXCEPTION_ERROR;
418 END IF;
419
420 -- check for end date greater than sysdate
421 IF to_date(l_upd_bktv_rec.end_date, 'DD/MM/YYYY') <> to_date(OKL_API.G_MISS_DATE, 'DD/MM/YYYY') AND
422 to_date(l_upd_bktv_rec.end_date,'DD/MM/YYYY') < l_sysdate THEN
423 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
424 p_msg_name => G_END_DATE);
425 RAISE OKL_API.G_EXCEPTION_ERROR;
426 END IF;
427 */
428 -- fetch old details from the database
429 get_rec(p_bktv_rec => l_upd_bktv_rec,
430 x_return_status => l_return_status,
431 x_no_data_found => l_no_data_found,
432 x_bktv_rec => l_db_bktv_rec);
433
434 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
435 -- l_no_data_found = TRUE THEN
436 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
437 END IF;
438
439 IF NOT l_no_data_found THEN
440 -- check for the records if start and end dates are in the past
441 /* IF to_date(l_db_bktv_rec.start_date,'DD/MM/YYYY') < l_sysdate AND
442 to_date(l_db_bktv_rec.end_date,'DD/MM/YYYY') < l_sysdate THEN
443 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
444 p_msg_name => G_PAST_RECORDS);
445 RAISE OKL_API.G_EXCEPTION_ERROR;
446 END IF;
447 */
448 -- for old version
449 IF l_upd_bktv_rec.start_date <> OKL_API.G_MISS_DATE THEN
450 l_oldversion_enddate := l_upd_bktv_rec.start_date - 1;
451 ELSE
452 l_oldversion_enddate := l_sysdate - 1;
453 END IF;
454
455 l_bktv_rec := l_db_bktv_rec;
459 l_bktv_rec.end_date := l_bktv_rec.start_date;
456 -- if end date of old version is less than start date of old version, set end date to start date
457 -- this is to take care of records created and updated on the same start date.
458 IF l_oldversion_enddate < l_bktv_rec.start_date THEN
460 ELSE
461 l_bktv_rec.end_date := l_oldversion_enddate;
462 END IF;
463
464 -- call verify changes to update the database
465 IF l_oldversion_enddate > l_db_bktv_rec.end_date THEN
466 check_updates(p_upd_bktv_rec => l_upd_bktv_rec,
467 p_db_bktv_rec => l_db_bktv_rec,
468 p_bktv_rec => l_bktv_rec,
469 x_return_status => l_return_status,
470 x_msg_data => x_msg_data);
471
472 IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
473 RAISE OKL_API.G_EXCEPTION_ERROR;
474 ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
475 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
476 END IF;
477 END IF;
478
479 -- public api to update buckets
480 OKL_BUCKETS_PUB.UPDATE_BUCKETS(p_api_version => l_api_version,
481 p_init_msg_list => p_init_msg_list,
482 x_return_status => l_return_status,
483 x_msg_count => x_msg_count,
484 x_msg_data => x_msg_data,
485 p_bktv_rec => l_bktv_rec,
486 x_bktv_rec => x_bktv_rec);
487
488 IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
489 RAISE OKL_API.G_EXCEPTION_ERROR;
490 ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
491 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
492 END IF;
493
494 -- for new version
495 -- create a temporary record with all relevant details from db and upd records
496 l_bktv_rec := defaults_to_actuals(p_upd_bktv_rec => l_upd_bktv_rec,
497 p_db_bktv_rec => l_db_bktv_rec);
498
499 IF l_upd_bktv_rec.start_date = OKL_API.G_MISS_DATE THEN
500 l_bktv_rec.start_date := l_sysdate;
501 END IF;
502
503 l_attrib_tbl(1).attribute := 'ibc_id';
504 l_attrib_tbl(1).attrib_type := okl_accounting_util.G_NUMBER;
505 l_attrib_tbl(1).value := l_bktv_rec.ibc_id;
506
507 okl_accounting_util.get_version(
508 p_attrib_tbl => l_attrib_tbl,
509 p_cur_version => l_bktv_rec.version,
510 p_end_date_attribute_name => 'END_DATE',
511 p_end_date => l_bktv_rec.end_date,
512 p_view => 'OKL_BUCKETS_V',
513 x_return_status => l_return_status,
514 x_new_version => l_new_version);
515
516 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
517 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
518 ELSE
519 l_bktv_rec.version := l_new_version;
520 END IF;
521
522 l_bktv_rec.id := OKL_API.G_MISS_NUM;
523
524 -- call verify changes to update the database
525 IF l_bktv_rec.end_date > l_db_bktv_rec.end_date THEN
526 check_updates(p_upd_bktv_rec => l_upd_bktv_rec,
527 p_db_bktv_rec => l_db_bktv_rec,
528 p_bktv_rec => l_bktv_rec,
529 x_return_status => l_return_status,
530 x_msg_data => x_msg_data);
531 IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
532 RAISE OKL_API.G_EXCEPTION_ERROR;
533 ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
534 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
535 END IF;
536 END IF;
537
538 -- public api to insert buckets
539 OKL_BUCKETS_PUB.INSERT_BUCKETS(p_api_version => l_api_version,
540 p_init_msg_list => p_init_msg_list,
541 x_return_status => l_return_status,
542 x_msg_count => x_msg_count,
543 x_msg_data => x_msg_data,
544 p_bktv_rec => l_bktv_rec,
545 x_bktv_rec => x_bktv_rec);
546
547 IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
548 RAISE OKL_API.G_EXCEPTION_ERROR;
549 ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
550 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
551 END IF;
552
553 -- copy output to input structure to get the id
554 l_bktv_rec := x_bktv_rec;
555
556 ELSE
557 l_bktv_rec := p_bktv_rec;
558 l_bktv_rec.id := OKL_API.G_MISS_NUM;
559 l_bktv_rec.start_date := l_sysdate;
560 l_bktv_rec.comments := OKL_API.G_MISS_CHAR;
561 -- Get Version number
562 l_attrib_tbl(1).attribute := 'ibc_id';
563 l_attrib_tbl(1).attrib_type := okl_accounting_util.G_NUMBER;
564 l_attrib_tbl(1).value := l_bktv_rec.ibc_id;
565
566 okl_accounting_util.get_version(
567 p_attrib_tbl => l_attrib_tbl,
568 p_cur_version => l_bktv_rec.version,
569 p_end_date_attribute_name => 'END_DATE',
570 p_end_date => l_bktv_rec.end_date,
571 p_view => 'OKL_BUCKETS_V',
572 x_return_status => l_return_status,
573 x_new_version => l_new_version);
574
575 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
576 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
577 ELSE
578 l_bktv_rec.version := l_new_version;
579 END IF;
583 p_init_msg_list => p_init_msg_list,
580
581 -- public api to insert buckets
582 OKL_BUCKETS_PUB.INSERT_BUCKETS(p_api_version => l_api_version,
584 x_return_status => l_return_status,
585 x_msg_count => x_msg_count,
586 x_msg_data => x_msg_data,
587 p_bktv_rec => l_bktv_rec,
588 x_bktv_rec => x_bktv_rec);
589
590 IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
591 RAISE OKL_API.G_EXCEPTION_ERROR;
592 ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
593 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
594 END IF;
595
596 -- copy output to input structure to get the id
597 l_bktv_rec := x_bktv_rec;
598
599 END IF;
600
601 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
602 x_msg_data => x_msg_data);
603 x_return_status := l_return_status;
604 EXCEPTION
605 WHEN OKL_API.G_EXCEPTION_ERROR THEN
606 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
607 p_pkg_name => G_PKG_NAME,
608 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
609 x_msg_count => x_msg_count,
610 x_msg_data => x_msg_data,
611 p_api_type => '_PVT');
612 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
613 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
614 p_pkg_name => G_PKG_NAME,
615 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
616 x_msg_count => x_msg_count,
617 x_msg_data => x_msg_data,
618 p_api_type => '_PVT');
619 WHEN OTHERS THEN
620 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
621 p_pkg_name => G_PKG_NAME,
622 p_exc_name => 'OTHERS',
623 x_msg_count => x_msg_count,
624 x_msg_data => x_msg_data,
625 p_api_type => '_PVT');
626
627 END update_buckets;
628
629 PROCEDURE create_buckets(
630 p_api_version IN NUMBER,
631 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
632 x_return_status OUT NOCOPY VARCHAR2,
633 x_msg_count OUT NOCOPY NUMBER,
634 x_msg_data OUT NOCOPY VARCHAR2,
635 p_bktv_tbl IN bktv_tbl_type,
636 x_bktv_tbl OUT NOCOPY bktv_tbl_type)
637
638 IS
639
640 l_api_version NUMBER := 1.0;
641
642 BEGIN
643
644 OKL_BUCKETS_PUB.INSERT_BUCKETS(p_api_version => l_api_version,
645 p_init_msg_list => p_init_msg_list,
646 x_return_status => x_return_Status,
647 x_msg_count => x_msg_count,
648 x_msg_data => x_msg_data,
649 p_bktv_tbl => p_bktv_tbl,
650 x_bktv_tbl => x_bktv_tbl);
651
652 END create_buckets;
653
654 PROCEDURE update_buckets(
655 p_api_version IN NUMBER,
656 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
657 x_return_status OUT NOCOPY VARCHAR2,
658 x_msg_count OUT NOCOPY NUMBER,
659 x_msg_data OUT NOCOPY VARCHAR2,
660 p_bktv_tbl IN bktv_tbl_type,
661 x_bktv_tbl OUT NOCOPY bktv_tbl_type)
662
663 IS
664 l_api_version NUMBER := 1.0;
665
666 BEGIN
667
668 OKL_BUCKETS_PUB.UPDATE_BUCKETS(p_api_version => l_api_version,
669 p_init_msg_list => p_init_msg_list,
670 x_return_status => x_return_Status,
671 x_msg_count => x_msg_count,
672 x_msg_data => x_msg_data,
673 p_bktv_tbl => p_bktv_tbl,
674 x_bktv_tbl => x_bktv_tbl);
675
676 END update_buckets;
677
678
679 PROCEDURE delete_buckets(
680 p_api_version IN NUMBER,
681 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
682 x_return_status OUT NOCOPY VARCHAR2,
683 x_msg_count OUT NOCOPY NUMBER,
684 x_msg_data OUT NOCOPY VARCHAR2,
685 p_bktv_rec IN bktv_rec_type)
686
687 IS
688 l_api_version NUMBER := 1.0;
689
690 BEGIN
691
692 OKL_BUCKETS_PUB.DELETE_BUCKETS(p_api_version => l_api_version,
693 p_init_msg_list => p_init_msg_list,
694 x_return_status => x_return_Status,
695 x_msg_count => x_msg_count,
696 x_msg_data => x_msg_data,
697 p_bktv_rec => p_bktv_rec);
698
699
700 END delete_buckets;
701
702 PROCEDURE delete_buckets(
703 p_api_version IN NUMBER,
704 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
705 x_return_status OUT NOCOPY VARCHAR2,
706 x_msg_count OUT NOCOPY NUMBER,
707 x_msg_data OUT NOCOPY VARCHAR2,
708 p_bktv_tbl IN bktv_tbl_type)
709
710 IS
711
712 l_api_version NUMBER := 1.0;
713
714 BEGIN
715
716 OKL_BUCKETS_PUB.DELETE_BUCKETS(p_api_version => l_api_version,
717 p_init_msg_list => p_init_msg_list,
718 x_return_status => x_return_Status,
719 x_msg_count => x_msg_count,
720 x_msg_data => x_msg_data,
721 p_bktv_tbl => p_bktv_tbl);
722
723 END delete_buckets;
724
725
726 END OKL_PROCESS_BUCKETS_PVT;