[Home] [Help]
PACKAGE BODY: APPS.OKL_SETUP_ACCRUALS_PVT
Source
1 PACKAGE BODY OKL_SETUP_ACCRUALS_PVT AS
2 /* $Header: OKLRARUB.pls 120.3 2007/02/06 11:14:00 gkhuntet noship $ */
3
4 ---------------------------------------------------------------------------
5 -- PROCEDURE get_rec for: OKL_ACCRUAL_GNRTNS_V
6 ---------------------------------------------------------------------------
7 PROCEDURE get_rec (p_agnv_rec IN agnv_rec_type,
8 x_return_status OUT NOCOPY VARCHAR2,
9 x_no_data_found OUT NOCOPY BOOLEAN,
10 x_agnv_rec OUT NOCOPY agnv_rec_type
11 ) IS
12 CURSOR okl_agnv_pk_csr (p_id IN NUMBER) IS
13 SELECT
14 ID,
15 OBJECT_VERSION_NUMBER,
16 LINE_NUMBER,
17 VERSION,
18 FROM_DATE,
19 ARO_CODE,
20 RIGHT_OPERAND_LITERAL,
21 ACRO_CODE,
22 NVL(ARLO_CODE, G_MISS_CHAR) ARLO_CODE,
23 NVL(LEFT_PARENTHESES, G_MISS_CHAR) LEFT_PARENTHESES,
24 NVL(RIGHT_PARENTHESES, G_MISS_CHAR) RIGHT_PARENTHESES,
25 NVL(TO_DATE, G_MISS_DATE) TO_DATE,
26 NVL(ORG_ID, G_MISS_NUM) ORG_ID,
27 CREATED_BY,
28 LAST_UPDATED_BY,
29 CREATION_DATE,
30 LAST_UPDATE_DATE,
31 NVL(LAST_UPDATE_LOGIN,G_MISS_NUM) LAST_UPDATE_LOGIN
32
33 FROM OKL_ACCRUAL_GNRTNS
34 WHERE id = p_id;
35
36 l_okl_agnv_pk okl_agnv_pk_csr%ROWTYPE;
37 l_agnv_rec agnv_rec_type;
38 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
39 BEGIN
40 x_no_data_found := TRUE;
41
42 -- Get current database values
43 OPEN okl_agnv_pk_csr (p_agnv_rec.id);
44 FETCH okl_agnv_pk_csr INTO
45 l_agnv_rec.ID,
46 l_agnv_rec.OBJECT_VERSION_NUMBER,
47 l_agnv_rec.LINE_NUMBER,
48 l_agnv_rec.VERSION,
49 l_agnv_rec.FROM_DATE,
50 l_agnv_rec.ARO_CODE,
51 l_agnv_rec.RIGHT_OPERAND_LITERAL,
52 l_agnv_rec.ACRO_CODE,
53 l_agnv_rec.ARLO_CODE,
54 l_agnv_rec.LEFT_PARENTHESES,
55 l_agnv_rec.RIGHT_PARENTHESES,
56 l_agnv_rec.TO_DATE,
57 l_agnv_rec.ORG_ID,
58 l_agnv_rec.CREATED_BY,
59 l_agnv_rec.LAST_UPDATED_BY,
60 l_agnv_rec.CREATION_DATE,
61 l_agnv_rec.LAST_UPDATE_DATE,
62 l_agnv_rec.LAST_UPDATE_LOGIN;
63 x_no_data_found := okl_agnv_pk_csr%NOTFOUND;
64 CLOSE okl_agnv_pk_csr;
65
66 x_agnv_rec := l_agnv_rec;
67 x_return_status := l_return_status;
68 EXCEPTION
69 WHEN OTHERS THEN
70
71 -- store SQL error message on message stack
72 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
73 p_msg_name => G_UNEXPECTED_ERROR,
74 p_token1 => G_SQLCODE_TOKEN,
75 p_token1_value => sqlcode,
76 p_token2 => G_SQLERRM_TOKEN,
77 p_token2_value => sqlerrm);
78 -- notify UNEXPECTED error for calling API.
79 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
80
81 IF (okl_agnv_pk_csr%ISOPEN) THEN
82 CLOSE okl_agnv_pk_csr;
83 END IF;
84
85 END get_rec;
86
87 ---------------------------------------------------------------------------
88 -- PROCEDURE get_changes_only for: OKL_ACCRUAL_GNRTNS_V
89 -- To take care of the assumption that Everything except the Changed Fields
90 -- have G_MISS values in them
91 ---------------------------------------------------------------------------
92 PROCEDURE get_changes_only (p_agnv_rec IN agnv_rec_type,
93 p_db_rec IN agnv_rec_type,
94 x_agnv_rec OUT NOCOPY agnv_rec_type )
95 IS
96 l_agnv_rec agnv_rec_type;
97 BEGIN
98 l_agnv_rec := p_agnv_rec;
99
100 IF p_db_rec.LINE_NUMBER = p_agnv_rec.LINE_NUMBER THEN
101 l_agnv_rec.LINE_NUMBER := G_MISS_NUM;
102 END IF;
103
104 IF p_db_rec.VERSION = p_agnv_rec.VERSION THEN
105 l_agnv_rec.VERSION := G_MISS_CHAR;
106 END IF;
107
108 IF p_db_rec.ARO_CODE = p_agnv_rec.ARO_CODE THEN
109 l_agnv_rec.ARO_CODE := G_MISS_CHAR;
110 END IF;
111
112 IF p_db_rec.ACRO_CODE = p_agnv_rec.ACRO_CODE THEN
113 l_agnv_rec.ACRO_CODE := G_MISS_CHAR;
114 END IF;
115
116 IF p_db_rec.RIGHT_OPERAND_LITERAL = p_agnv_rec.RIGHT_OPERAND_LITERAL THEN
117 l_agnv_rec.RIGHT_OPERAND_LITERAL := G_MISS_CHAR;
118 END IF;
119
120 IF p_db_rec.FROM_DATE = p_agnv_rec.FROM_DATE THEN
121 l_agnv_rec.FROM_DATE := G_MISS_DATE;
122 END IF;
123
124 IF p_db_rec.TO_DATE IS NULL THEN
125 IF p_agnv_rec.TO_DATE IS NULL THEN
126 l_agnv_rec.TO_DATE := G_MISS_DATE;
127 END IF;
128 ELSIF p_db_rec.TO_DATE = p_agnv_rec.TO_DATE THEN
129 l_agnv_rec.TO_DATE := G_MISS_DATE;
130 END IF;
131
132 IF p_db_rec.ARLO_CODE IS NULL THEN
133 IF p_agnv_rec.ARLO_CODE IS NULL THEN
134 l_agnv_rec.ARLO_CODE := G_MISS_CHAR;
135 END IF;
136 ELSIF p_db_rec.ARLO_CODE = p_agnv_rec.ARLO_CODE THEN
137 l_agnv_rec.ARLO_CODE := G_MISS_CHAR;
138 END IF;
139
140 IF p_db_rec.LEFT_PARENTHESES IS NULL THEN
141 IF p_agnv_rec.LEFT_PARENTHESES IS NULL THEN
142 l_agnv_rec.LEFT_PARENTHESES := G_MISS_CHAR;
143 END IF;
144 ELSIF p_db_rec.LEFT_PARENTHESES = p_agnv_rec.LEFT_PARENTHESES THEN
145 l_agnv_rec.LEFT_PARENTHESES := G_MISS_CHAR;
146 END IF;
147
148 IF p_db_rec.RIGHT_PARENTHESES IS NULL THEN
149 IF p_agnv_rec.RIGHT_PARENTHESES IS NULL THEN
150 l_agnv_rec.RIGHT_PARENTHESES := G_MISS_CHAR;
151 END IF;
152 ELSIF p_db_rec.RIGHT_PARENTHESES = p_agnv_rec.RIGHT_PARENTHESES THEN
153 l_agnv_rec.RIGHT_PARENTHESES := G_MISS_CHAR;
154 END IF;
155
156 IF p_db_rec.ORG_ID IS NULL THEN
157 IF p_agnv_rec.ORG_ID IS NULL THEN
158 l_agnv_rec.ORG_ID := G_MISS_NUM;
159 END IF;
160 ELSIF p_db_rec.ORG_ID = p_agnv_rec.ORG_ID THEN
161 l_agnv_rec.ORG_ID := G_MISS_NUM;
162 END IF;
163
164 x_agnv_rec := l_agnv_rec;
165 END get_changes_only;
166
167
168
169 ---------------------------------------------------------------------------
170 -- PROCEDURE create_accrual_rules for: OKL_ACCRUAL_GNRTNS_V
171 ---------------------------------------------------------------------------
172 PROCEDURE create_accrual_rules(p_api_version IN NUMBER,
173 p_init_msg_list IN VARCHAR2,
174 x_return_status OUT NOCOPY VARCHAR2,
175 x_msg_count OUT NOCOPY NUMBER,
176 x_msg_data OUT NOCOPY VARCHAR2,
177 p_agnv_rec IN agnv_rec_type,
178 x_agnv_rec OUT NOCOPY agnv_rec_type ) IS
179
180 l_api_version CONSTANT NUMBER := 1;
181 l_api_name CONSTANT VARCHAR2(30) := 'create_accrual_rules';
182 l_no_data_found BOOLEAN := TRUE;
183 l_valid BOOLEAN := TRUE;
184 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
185 l_agnv_rec agnv_rec_type;
186 l_sysdate DATE := to_date(SYSDATE, 'DD-MM-RRRR');
187 l_line_number NUMBER := 1;
188 l_tot_version NUMBER :=1 ;
189 CURSOR line_num_csr(p_version VARCHAR2) IS
190 SELECT MAX(line_number)
191 FROM OKL_ACCRUAL_GNRTNS
192 WHERE ORG_ID = p_agnv_rec.ORG_ID AND version = p_version;
193
194 /*TO FIND NO OF VERSION ON THE BASIS OF ID. */
195 CURSOR ver_count_csr(p_orgId NUMBER) IS
196 SELECT MAX(TO_NUMBER(VERSION))
197 FROM OKL_ACCRUAL_GNRTNS
198 WHERE ORG_ID = p_orgID;
199 BEGIN
200 l_agnv_rec := p_agnv_rec;
201
202 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
203 p_pkg_name => G_PKG_NAME,
204 p_init_msg_list => p_init_msg_list,
205 l_api_version => l_api_version,
206 p_api_version => p_api_version,
207 p_api_type => '_PVT',
208 x_return_status => l_return_status);
209 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
210 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
211 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
212 RAISE OKL_API.G_EXCEPTION_ERROR;
213 END IF;
214
215 /* validate aro_code */
216 IF (l_agnv_rec.aro_code IS NULL OR l_agnv_rec.aro_code = G_MISS_CHAR) THEN
217 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
218 p_msg_name => 'OKL_AGN_ARO_CODE_ERROR');
219 RAISE OKL_API.G_EXCEPTION_ERROR;
220 END IF;
221
222 /* validate on acro_code */
223 IF (l_agnv_rec.acro_code IS NULL OR l_agnv_rec.acro_code = G_MISS_CHAR) THEN
224 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
225 p_msg_name => 'OKL_AGN_ACRO_CODE_ERROR');
226 RAISE OKL_API.G_EXCEPTION_ERROR;
227 END IF;
228
229 /* validate on right operand literal */
230 IF (l_agnv_rec.right_operand_literal IS NULL OR l_agnv_rec.right_operand_literal = G_MISS_CHAR) THEN
231 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
232 p_msg_name => 'OKL_RIGHT_OPD_LITERAL_ERROR');
233 RAISE OKL_API.G_EXCEPTION_ERROR;
234 END IF;
235
236 /* Assign Start Date for the record */
237 l_agnv_rec.from_date := l_sysdate;
238
239
240
241
242
243
244
245 /* Validate Version for increment in case of update */
246 IF l_agnv_rec.version IS NOT NULL THEN
247 --IF l_agnv_rec.version <> '1' THEN
248 l_agnv_rec.version := l_agnv_rec.version + 1;
249 /* Line Number assignment -- sgiyer 03-05-02 */
250 OPEN line_num_csr(l_agnv_rec.version);
251 FETCH line_num_csr INTO l_line_number;
252 IF l_line_number IS NOT NULL THEN
253 l_agnv_rec.line_number := l_line_number + 1;
254 END IF;
255 CLOSE line_num_csr;
256 --END IF;
257 END IF;
258
259 /* public api to insert accrual rules */
260 OKL_ACCRUAL_RULES_PUB.insert_accrual_rules(p_api_version => l_api_version,
261 p_init_msg_list => p_init_msg_list,
262 x_return_status => l_return_status,
263 x_msg_count => x_msg_count,
264 x_msg_data => x_msg_data,
265 p_agnv_rec => l_agnv_rec,
266 x_agnv_rec => x_agnv_rec);
267
268 IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
269 RAISE OKL_API.G_EXCEPTION_ERROR;
270 ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
271 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
272 END IF;
273
274 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
275 x_msg_data => x_msg_data);
276 x_return_status := l_return_status;
277 EXCEPTION
278 WHEN OKL_API.G_EXCEPTION_ERROR THEN
279 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
280 p_pkg_name => G_PKG_NAME,
281 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
282 x_msg_count => x_msg_count,
283 x_msg_data => x_msg_data,
284 p_api_type => '_PVT');
285 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
286 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
287 p_pkg_name => G_PKG_NAME,
288 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
289 x_msg_count => x_msg_count,
290 x_msg_data => x_msg_data,
291 p_api_type => '_PVT');
292 WHEN OTHERS THEN
293 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
294 p_pkg_name => G_PKG_NAME,
295 p_exc_name => 'OTHERS',
296 x_msg_count => x_msg_count,
297 x_msg_data => x_msg_data,
298 p_api_type => '_PVT');
299
300 END create_accrual_rules;
301
302 ---------------------------------------------------------------------------
303 -- PROCEDURE update_accrual_rules for: OKL_ACCRUAL_GNRTNS_V
304 ---------------------------------------------------------------------------
305 PROCEDURE update_accrual_rules(p_api_version IN NUMBER,
306 p_init_msg_list IN VARCHAR2,
307 x_return_status OUT NOCOPY VARCHAR2,
308 x_msg_count OUT NOCOPY NUMBER,
309 x_msg_data OUT NOCOPY VARCHAR2,
310 p_agnv_rec IN agnv_rec_type,
311 x_agnv_rec OUT NOCOPY agnv_rec_type
312 ) IS
313 l_api_version CONSTANT NUMBER := 1;
314 l_api_name CONSTANT VARCHAR2(30) := 'update_accrual_rules';
315 l_no_data_found BOOLEAN := TRUE;
316 l_valid BOOLEAN := TRUE;
317 l_oldversion_enddate DATE := to_date(SYSDATE, 'DD-MM-RRRR');
318 l_sysdate DATE := to_date(SYSDATE, 'DD-MM-RRRR');
319 l_db_agnv_rec agnv_rec_type; /* database copy */
320 l_upd_agnv_rec agnv_rec_type; /* input copy */
321 l_agnv_rec agnv_rec_type := p_agnv_rec; /* latest with the retained changes */
322 l_tmp_agnv_rec agnv_rec_type; /* for any other purposes */
323 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
324 l_action VARCHAR2(1);
325 l_new_version VARCHAR2(100);
326 l_attrib_tbl okl_accounting_util.overlap_attrib_tbl_type;
327 l_line_number NUMBER := 1;
328
329 CURSOR line_num_csr(p_version VARCHAR2 , p_orgID Number) IS
330 SELECT MAX(line_number)
331 FROM OKL_ACCRUAL_GNRTNS
332 WHERE version = p_version and ORG_ID = p_orgID;
333
334 BEGIN
335
336 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
337 p_pkg_name => G_PKG_NAME,
338 p_init_msg_list => p_init_msg_list,
339 l_api_version => l_api_version,
340 p_api_version => p_api_version,
341 p_api_type => '_PVT',
342 x_return_status => l_return_status);
343 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
344 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
345 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
346 RAISE OKL_API.G_EXCEPTION_ERROR;
347 END IF;
348
349
350 /* fetch old details from the database */
351 get_rec(p_agnv_rec => p_agnv_rec,
352 x_return_status => l_return_status,
353 x_no_data_found => l_no_data_found,
354 x_agnv_rec => l_db_agnv_rec);
355
356 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS OR
357 l_no_data_found = TRUE THEN
358 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
359 END IF;
360
361
362 /* retain the details that has been changed only */
363 get_changes_only(p_agnv_rec => p_agnv_rec,
364 p_db_rec => l_db_agnv_rec,
365 x_agnv_rec => l_upd_agnv_rec);
366
367
368 /* for old version */
369 IF l_upd_agnv_rec.from_date <> G_MISS_DATE THEN
370 l_oldversion_enddate := l_upd_agnv_rec.from_date - 1;
371 ELSE
372 IF to_date(l_db_agnv_rec.from_date, 'DD-MM-RRRR') = l_sysdate THEN
373 l_oldversion_enddate := l_sysdate;
374 ELSE
375 l_oldversion_enddate := l_sysdate - 1;
376 END IF;
377 END IF;
378
379 l_agnv_rec := l_db_agnv_rec;
380 l_agnv_rec.to_date := l_oldversion_enddate;
381
382 /* public api to update provisions */
383 OKL_ACCRUAL_RULES_PUB.update_accrual_rules(p_api_version => l_api_version,
384 p_init_msg_list => p_init_msg_list,
385 x_return_status => l_return_status,
386 x_msg_count => x_msg_count,
387 x_msg_data => x_msg_data,
388 p_agnv_rec => l_agnv_rec,
389 x_agnv_rec => x_agnv_rec);
390
391 IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
392 RAISE OKL_API.G_EXCEPTION_ERROR;
393 ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
394 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
395 END IF;
396
397 /* for new version */
398 /* create a temporary record with all relevant details from db and upd records */
399 /* removed call to default_to_actuals sgiyer 02-06-02 */
400 l_agnv_rec := p_agnv_rec;
401
402 /* IF l_upd_agnv_rec.from_date = G_MISS_DATE THEN
403 l_agnv_rec.from_date := l_sysdate;
404 END IF; */
405
406 l_agnv_rec.from_date := l_sysdate;
407 l_agnv_rec.to_date := G_MISS_DATE;
408 l_agnv_rec.version := p_agnv_rec.version + 1;
409 l_agnv_rec.id := G_MISS_NUM;
410
411 /* Line Number assignment -- sgiyer 03-05-02 */
412 OPEN line_num_csr(l_agnv_rec.version ,l_agnv_rec.ORG_ID);
413 FETCH line_num_csr INTO l_line_number;
414 IF l_line_number IS NULL THEN
415 l_agnv_rec.line_number := 1;
416 ELSE
417 l_agnv_rec.line_number := l_line_number + 1;
418 END IF;
419 CLOSE line_num_csr;
420
421 /* public api to insert provisions */
422 OKL_ACCRUAL_RULES_PUB.insert_accrual_rules(p_api_version => l_api_version,
423 p_init_msg_list => p_init_msg_list,
424 x_return_status => l_return_status,
425 x_msg_count => x_msg_count,
426 x_msg_data => x_msg_data,
427 p_agnv_rec => l_agnv_rec,
428 x_agnv_rec => x_agnv_rec);
429
430 IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
431 RAISE OKL_API.G_EXCEPTION_ERROR;
432 ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
433 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
434 END IF;
435
436 /* copy output to input structure to get the id */
437 l_agnv_rec := x_agnv_rec;
438
439
440 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
441 x_msg_data => x_msg_data);
442 x_return_status := l_return_status;
443 EXCEPTION
444 WHEN OKL_API.G_EXCEPTION_ERROR THEN
445 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
446 p_pkg_name => G_PKG_NAME,
447 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
448 x_msg_count => x_msg_count,
449 x_msg_data => x_msg_data,
450 p_api_type => '_PVT');
451 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
452 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
453 p_pkg_name => G_PKG_NAME,
454 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
455 x_msg_count => x_msg_count,
456 x_msg_data => x_msg_data,
457 p_api_type => '_PVT');
458 WHEN OTHERS THEN
459 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
460 p_pkg_name => G_PKG_NAME,
461 p_exc_name => 'OTHERS',
462 x_msg_count => x_msg_count,
463 x_msg_data => x_msg_data,
464 p_api_type => '_PVT');
465
466 END update_accrual_rules;
467
468 PROCEDURE create_accrual_rules(
469 p_api_version IN NUMBER,
470 p_init_msg_list IN VARCHAR2,
471 x_return_status OUT NOCOPY VARCHAR2,
472 x_msg_count OUT NOCOPY NUMBER,
473 x_msg_data OUT NOCOPY VARCHAR2,
474 p_agnv_tbl IN agnv_tbl_type,
475 x_agnv_tbl OUT NOCOPY agnv_tbl_type)
476
477 IS
478
479 l_api_version NUMBER := 1.0;
480
481 BEGIN
482
483 OKL_ACCRUAL_RULES_PUB.insert_accrual_rules(p_api_version => l_api_version,
484 p_init_msg_list => p_init_msg_list,
485 x_return_status => x_return_Status,
486 x_msg_count => x_msg_count,
487 x_msg_data => x_msg_data,
488 p_agnv_tbl => p_agnv_tbl,
489 x_agnv_tbl => x_agnv_tbl);
490
491 END create_accrual_rules;
492
493 PROCEDURE update_accrual_rules(
494 p_api_version IN NUMBER,
495 p_init_msg_list IN VARCHAR2,
496 x_return_status OUT NOCOPY VARCHAR2,
497 x_msg_count OUT NOCOPY NUMBER,
498 x_msg_data OUT NOCOPY VARCHAR2,
499 p_agnv_tbl IN agnv_tbl_type,
500 x_agnv_tbl OUT NOCOPY agnv_tbl_type)
501
502 IS
503 l_api_version NUMBER := 1.0;
504
505 BEGIN
506
507 OKL_ACCRUAL_RULES_PUB.update_accrual_rules(p_api_version => l_api_version,
508 p_init_msg_list => p_init_msg_list,
509 x_return_status => x_return_Status,
510 x_msg_count => x_msg_count,
511 x_msg_data => x_msg_data,
512 p_agnv_tbl => p_agnv_tbl,
513 x_agnv_tbl => x_agnv_tbl);
514
515 END update_accrual_rules;
516
517
518 PROCEDURE delete_accrual_rules(
519 p_api_version IN NUMBER,
520 p_init_msg_list IN VARCHAR2,
521 x_return_status OUT NOCOPY VARCHAR2,
522 x_msg_count OUT NOCOPY NUMBER,
523 x_msg_data OUT NOCOPY VARCHAR2,
524 p_agnv_rec IN agnv_rec_type)
525
526 IS
527
528
529 l_api_version NUMBER := 1;
530 l_api_name CONSTANT VARCHAR2(30) := 'delete_accrual_rules';
531 l_no_data_found BOOLEAN := TRUE;
532 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
533 l_dummy NUMBER;
534 l_oldversion_enddate DATE := to_date(SYSDATE, 'DD-MM-RRRR');
535 l_sysdate DATE := to_date(SYSDATE, 'DD-MM-RRRR');
536 l_db_agnv_rec agnv_rec_type; /* database copy */
537 l_upd_agnv_rec agnv_rec_type; /* input copy */
538 l_agnv_rec agnv_rec_type := p_agnv_rec; /* latest with the retained changes */
539 x_agnv_rec agnv_rec_type; /*update return copy */
540
541 CURSOR check_rule_csr(p_id NUMBER) IS
542 SELECT COUNT(*)
543 FROM OKL_ACCRUAL_GNRTNS
544 WHERE version = (SELECT version
545 FROM OKL_ACCRUAL_GNRTNS
546 WHERE id = p_id)
547 AND to_date IS NULL;
548
549 BEGIN
550
551 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
552 p_pkg_name => G_PKG_NAME,
553 p_init_msg_list => p_init_msg_list,
554 l_api_version => l_api_version,
555 p_api_version => p_api_version,
556 p_api_type => '_PVT',
557 x_return_status => l_return_status);
558 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
559 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
560 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
561 RAISE OKL_API.G_EXCEPTION_ERROR;
562 END IF;
563 /* check if record being deleted is the last */
564 -- OPEN check_rule_csr(p_agnv_rec.id);
565 --FETCH check_rule_csr INTO l_dummy;
566 -- IF l_dummy = 1 THEN
567 -- OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
568 -- p_msg_name => 'OKL_AGN_RULE_DEL_ERROR');
569 -- RAISE OKL_API.G_EXCEPTION_ERROR;
570 -- ELSE
571 /* update records with end date */
572 /* fetch old details from the database */
573 get_rec(p_agnv_rec => p_agnv_rec,
574 x_return_status => l_return_status,
575 x_no_data_found => l_no_data_found,
576 x_agnv_rec => l_db_agnv_rec);
577
578 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS OR
579 l_no_data_found = TRUE THEN
580 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
581 END IF;
582
583 /* retain the details that has been changed only */
584 get_changes_only(p_agnv_rec => p_agnv_rec,
585 p_db_rec => l_db_agnv_rec,
586 x_agnv_rec => l_upd_agnv_rec);
587
588 /* for old version */
589 IF l_upd_agnv_rec.from_date <> G_MISS_DATE THEN
590 IF to_date(l_upd_agnv_rec.from_date, 'DD-MM-RRRR') = l_sysdate THEN
591 l_oldversion_enddate := l_sysdate;
592 ELSE
593 l_oldversion_enddate := l_upd_agnv_rec.from_date - 1;
594 END IF;
595 ELSE
596 IF to_date(l_db_agnv_rec.from_date, 'DD-MM-RRRR') = l_sysdate THEN
597 l_oldversion_enddate := l_sysdate;
598 ELSE
599 l_oldversion_enddate := l_sysdate - 1;
600 END IF;
601 -- END IF;
602
603 l_agnv_rec := l_db_agnv_rec;
604 l_agnv_rec.to_date := l_oldversion_enddate;
605
606 /* public api to update provisions */
607 OKL_ACCRUAL_RULES_PUB.update_accrual_rules(p_api_version => l_api_version,
608 p_init_msg_list => p_init_msg_list,
609 x_return_status => l_return_status,
610 x_msg_count => x_msg_count,
611 x_msg_data => x_msg_data,
612 p_agnv_rec => l_agnv_rec,
613 x_agnv_rec => x_agnv_rec);
614
615 IF l_return_status = OKL_API.G_RET_STS_ERROR THEN RAISE
616 OKL_API.G_EXCEPTION_ERROR; ELSIF l_return_status =
617 OKL_API.G_RET_STS_UNEXP_ERROR THEN RAISE
618 OKL_API.G_EXCEPTION_UNEXPECTED_ERROR; END IF; x_return_status :=
619 l_return_status; END IF; EXCEPTION WHEN OKL_API.G_EXCEPTION_ERROR THEN
620 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
621 p_pkg_name => G_PKG_NAME, p_exc_name =>
622 'OKL_API.G_RET_STS_ERROR', x_msg_count => x_msg_count, x_msg_data
623 => x_msg_data, p_api_type => '_PVT'); WHEN
624 OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN x_return_status :=
625 OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name, p_pkg_name
626 => G_PKG_NAME, p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
627 x_msg_count => x_msg_count, x_msg_data => x_msg_data,
628 p_api_type => '_PVT'); WHEN OTHERS THEN x_return_status :=
629 OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name, p_pkg_name
630 => G_PKG_NAME, p_exc_name => 'OTHERS', x_msg_count => x_msg_count,
631 x_msg_data => x_msg_data, p_api_type => '_PVT'); END
632 delete_accrual_rules;
633
634 PROCEDURE delete_accrual_rules(
635 p_api_version IN NUMBER,
636 p_init_msg_list IN VARCHAR2,
637 x_return_status OUT NOCOPY VARCHAR2,
638 x_msg_count OUT NOCOPY NUMBER,
639 x_msg_data OUT NOCOPY VARCHAR2,
640 p_agnv_tbl IN agnv_tbl_type)
641
642 IS
643
644 l_api_version NUMBER := 1.0;
645
646 BEGIN
647
648 OKL_ACCRUAL_RULES_PUB.delete_accrual_rules(p_api_version => l_api_version,
649 p_init_msg_list => p_init_msg_list,
650 x_return_status => x_return_Status,
651 x_msg_count => x_msg_count,
652 x_msg_data => x_msg_data,
653 p_agnv_tbl => p_agnv_tbl);
654
655 END delete_accrual_rules;
656
657
658 END OKL_SETUP_ACCRUALS_PVT;