[Home] [Help]
PACKAGE BODY: APPS.OKL_PROCESS_PROVISIONS_PVT
Source
1 PACKAGE BODY OKL_PROCESS_PROVISIONS_PVT AS
2 /* $Header: OKLRPRVB.pls 115.6 2002/02/18 20:12:38 pkm ship $ */
3
4 ---------------------------------------------------------------------------
5 -- PROCEDURE get_rec for: OKL_PROVISIONS_V
6 ---------------------------------------------------------------------------
7 PROCEDURE get_rec (p_pvnv_rec IN pvnv_rec_type,
8 x_return_status OUT NOCOPY VARCHAR2,
9 x_no_data_found OUT NOCOPY BOOLEAN,
10 x_pvnv_rec OUT NOCOPY pvnv_rec_type
11 ) IS
12 CURSOR okl_pvnv_pk_csr (p_id IN NUMBER) IS
13 SELECT
14 ID,
15 OBJECT_VERSION_NUMBER,
16 NAME,
17 VERSION,
18 FROM_DATE,
19 NVL(TO_DATE, G_MISS_DATE) TO_DATE,
20 NVL(DESCRIPTION, G_MISS_CHAR) DESCRIPTION,
21 APP_DEBIT_CCID,
22 APP_CREDIT_CCID,
23 REV_DEBIT_CCID,
24 REV_CREDIT_CCID,
25 NVL(ATTRIBUTE_CATEGORY,G_MISS_CHAR) ATTRIBUTE_CATEGORY,
26 NVL(ATTRIBUTE1,G_MISS_CHAR) ATTRIBUTE1,
27 NVL(ATTRIBUTE2,G_MISS_CHAR) ATTRIBUTE2,
28 NVL(ATTRIBUTE3,G_MISS_CHAR) ATTRIBUTE3,
29 NVL(ATTRIBUTE4,G_MISS_CHAR) ATTRIBUTE4,
30 NVL(ATTRIBUTE5,G_MISS_CHAR) ATTRIBUTE5,
31 NVL(ATTRIBUTE6,G_MISS_CHAR) ATTRIBUTE6,
32 NVL(ATTRIBUTE7,G_MISS_CHAR) ATTRIBUTE7,
33 NVL(ATTRIBUTE8,G_MISS_CHAR) ATTRIBUTE8,
34 NVL(ATTRIBUTE9,G_MISS_CHAR) ATTRIBUTE9,
35 NVL(ATTRIBUTE10,G_MISS_CHAR) ATTRIBUTE10,
36 NVL(ATTRIBUTE11,G_MISS_CHAR) ATTRIBUTE11,
37 NVL(ATTRIBUTE12,G_MISS_CHAR) ATTRIBUTE12,
38 NVL(ATTRIBUTE13,G_MISS_CHAR) ATTRIBUTE13,
39 NVL(ATTRIBUTE14,G_MISS_CHAR) ATTRIBUTE14,
40 NVL(ATTRIBUTE15,G_MISS_CHAR) ATTRIBUTE15,
41 CREATED_BY,
42 LAST_UPDATED_BY,
43 CREATION_DATE,
44 LAST_UPDATE_DATE,
45 NVL(LAST_UPDATE_LOGIN,G_MISS_NUM) LAST_UPDATE_LOGIN
46
47 FROM OKL_PROVISIONS
48 WHERE id = p_id;
49
50 l_okl_pvnv_pk okl_pvnv_pk_csr%ROWTYPE;
51 l_pvnv_rec pvnv_rec_type;
52 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
53 BEGIN
54 x_no_data_found := TRUE;
55
56 -- Get current database values
57 OPEN okl_pvnv_pk_csr (p_pvnv_rec.id);
58 FETCH okl_pvnv_pk_csr INTO
59 l_pvnv_rec.ID,
60 l_pvnv_rec.OBJECT_VERSION_NUMBER,
61 l_pvnv_rec.NAME,
62 l_pvnv_rec.VERSION,
63 l_pvnv_rec.FROM_DATE,
64 l_pvnv_rec.TO_DATE,
65 l_pvnv_rec.DESCRIPTION,
66 l_pvnv_rec.APP_DEBIT_CCID,
67 l_pvnv_rec.APP_CREDIT_CCID,
68 l_pvnv_rec.REV_DEBIT_CCID,
69 l_pvnv_rec.REV_CREDIT_CCID,
70 l_pvnv_rec.ATTRIBUTE_CATEGORY,
71 l_pvnv_rec.ATTRIBUTE1,
72 l_pvnv_rec.ATTRIBUTE2,
73 l_pvnv_rec.ATTRIBUTE3,
74 l_pvnv_rec.ATTRIBUTE4,
75 l_pvnv_rec.ATTRIBUTE5,
76 l_pvnv_rec.ATTRIBUTE6,
77 l_pvnv_rec.ATTRIBUTE7,
78 l_pvnv_rec.ATTRIBUTE8,
79 l_pvnv_rec.ATTRIBUTE9,
80 l_pvnv_rec.ATTRIBUTE10,
81 l_pvnv_rec.ATTRIBUTE11,
82 l_pvnv_rec.ATTRIBUTE12,
83 l_pvnv_rec.ATTRIBUTE13,
84 l_pvnv_rec.ATTRIBUTE14,
85 l_pvnv_rec.ATTRIBUTE15,
86 l_pvnv_rec.CREATED_BY,
87 l_pvnv_rec.LAST_UPDATED_BY,
88 l_pvnv_rec.CREATION_DATE,
89 l_pvnv_rec.LAST_UPDATE_DATE,
90 l_pvnv_rec.LAST_UPDATE_LOGIN;
91 x_no_data_found := okl_pvnv_pk_csr%NOTFOUND;
92 CLOSE okl_pvnv_pk_csr;
93
94 x_pvnv_rec := l_pvnv_rec;
95 x_return_status := l_return_status;
96 EXCEPTION
97 WHEN OTHERS THEN
98
99 -- store SQL error message on message stack
100 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
101 p_msg_name => G_UNEXPECTED_ERROR,
102 p_token1 => G_SQLCODE_TOKEN,
103 p_token1_value => sqlcode,
104 p_token2 => G_SQLERRM_TOKEN,
105 p_token2_value => sqlerrm);
106 -- notify UNEXPECTED error for calling API.
107 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
108
109 IF (okl_pvnv_pk_csr%ISOPEN) THEN
110 CLOSE okl_pvnv_pk_csr;
111 END IF;
112
113 END get_rec;
114
115 ---------------------------------------------------------------------------
116 -- PROCEDURE get_changes_only for: OKL_PROVISIONS_V
117 -- To take care of the assumption that Everything except the Changed Fields
118 -- have G_MISS values in them
119 ---------------------------------------------------------------------------
120 PROCEDURE get_changes_only (p_pvnv_rec IN pvnv_rec_type,
121 p_db_rec IN pvnv_rec_type,
122 x_pvnv_rec OUT NOCOPY pvnv_rec_type )
123 IS
124 l_pvnv_rec pvnv_rec_type;
125 BEGIN
126 l_pvnv_rec := p_pvnv_rec;
127
128 IF p_db_rec.NAME = p_pvnv_rec.NAME THEN
129 l_pvnv_rec.NAME := G_MISS_CHAR;
130 END IF;
131
132 IF p_db_rec.VERSION = p_pvnv_rec.VERSION THEN
133 l_pvnv_rec.VERSION := G_MISS_CHAR;
134 END IF;
135
136 IF p_db_rec.FROM_DATE = p_pvnv_rec.FROM_DATE THEN
137 l_pvnv_rec.FROM_DATE := G_MISS_DATE;
138 END IF;
139
140 IF p_db_rec.TO_DATE IS NULL THEN
141 IF p_pvnv_rec.TO_DATE IS NULL THEN
142 l_pvnv_rec.TO_DATE := G_MISS_DATE;
143 END IF;
144 ELSIF p_db_rec.TO_DATE = p_pvnv_rec.TO_DATE THEN
145 l_pvnv_rec.TO_DATE := G_MISS_DATE;
146 END IF;
147
148 IF p_db_rec.DESCRIPTION IS NULL THEN
149 IF p_pvnv_rec.DESCRIPTION IS NULL THEN
150 l_pvnv_rec.DESCRIPTION := G_MISS_CHAR;
151 END IF;
152 ELSIF p_db_rec.DESCRIPTION = p_pvnv_rec.DESCRIPTION THEN
153 l_pvnv_rec.DESCRIPTION := G_MISS_CHAR;
154 END IF;
155
156 IF p_db_rec.APP_DEBIT_CCID = p_pvnv_rec.APP_DEBIT_CCID THEN
157 l_pvnv_rec.APP_DEBIT_CCID := G_MISS_NUM;
158 END IF;
159
160 IF p_db_rec.APP_CREDIT_CCID = p_pvnv_rec.APP_CREDIT_CCID THEN
161 l_pvnv_rec.APP_CREDIT_CCID := G_MISS_NUM;
162 END IF;
163
164 IF p_db_rec.REV_DEBIT_CCID = p_pvnv_rec.REV_DEBIT_CCID THEN
165 l_pvnv_rec.REV_DEBIT_CCID := G_MISS_NUM;
166 END IF;
167
168 IF p_db_rec.REV_CREDIT_CCID = p_pvnv_rec.REV_CREDIT_CCID THEN
169 l_pvnv_rec.REV_CREDIT_CCID := G_MISS_NUM;
170 END IF;
171
172 IF p_db_rec.ATTRIBUTE_CATEGORY IS NULL THEN
173 IF p_pvnv_rec.ATTRIBUTE_CATEGORY IS NULL THEN
174 l_pvnv_rec.ATTRIBUTE_CATEGORY := G_MISS_CHAR;
175 END IF;
176 ELSIF p_db_rec.ATTRIBUTE_CATEGORY = p_pvnv_rec.ATTRIBUTE_CATEGORY THEN
177 l_pvnv_rec.ATTRIBUTE_CATEGORY := G_MISS_CHAR;
178 END IF;
179
180 IF p_db_rec.ATTRIBUTE1 IS NULL THEN
181 IF p_pvnv_rec.ATTRIBUTE1 IS NULL THEN
182 l_pvnv_rec.ATTRIBUTE1 := G_MISS_CHAR;
183 END IF;
184 ELSIF p_db_rec.ATTRIBUTE1 = p_pvnv_rec.ATTRIBUTE1 THEN
185 l_pvnv_rec.ATTRIBUTE1 := G_MISS_CHAR;
186 END IF;
187
188 IF p_db_rec.ATTRIBUTE2 IS NULL THEN
189 IF p_pvnv_rec.ATTRIBUTE2 IS NULL THEN
190 l_pvnv_rec.ATTRIBUTE2 := G_MISS_CHAR;
191 END IF;
192 ELSIF p_db_rec.ATTRIBUTE2 = p_pvnv_rec.ATTRIBUTE2 THEN
193 l_pvnv_rec.ATTRIBUTE2 := G_MISS_CHAR;
194 END IF;
195
196 IF p_db_rec.ATTRIBUTE3 IS NULL THEN
197 IF p_pvnv_rec.ATTRIBUTE3 IS NULL THEN
198 l_pvnv_rec.ATTRIBUTE3 := G_MISS_CHAR;
199 END IF;
200 ELSIF p_db_rec.ATTRIBUTE3 = p_pvnv_rec.ATTRIBUTE3 THEN
201 l_pvnv_rec.ATTRIBUTE3 := G_MISS_CHAR;
202 END IF;
203
204 IF p_db_rec.ATTRIBUTE4 IS NULL THEN
205 IF p_pvnv_rec.ATTRIBUTE4 IS NULL THEN
206 l_pvnv_rec.ATTRIBUTE4 := G_MISS_CHAR;
207 END IF;
208 ELSIF p_db_rec.ATTRIBUTE4 = p_pvnv_rec.ATTRIBUTE4 THEN
209 l_pvnv_rec.ATTRIBUTE4 := G_MISS_CHAR;
210 END IF;
211
212 IF p_db_rec.ATTRIBUTE5 IS NULL THEN
213 IF p_pvnv_rec.ATTRIBUTE5 IS NULL THEN
214 l_pvnv_rec.ATTRIBUTE5 := G_MISS_CHAR;
215 END IF;
216 ELSIF p_db_rec.ATTRIBUTE5 = p_pvnv_rec.ATTRIBUTE5 THEN
217 l_pvnv_rec.ATTRIBUTE5 := G_MISS_CHAR;
218 END IF;
219
220 IF p_db_rec.ATTRIBUTE6 IS NULL THEN
221 IF p_pvnv_rec.ATTRIBUTE6 IS NULL THEN
222 l_pvnv_rec.ATTRIBUTE6 := G_MISS_CHAR;
223 END IF;
224 ELSIF p_db_rec.ATTRIBUTE6 = p_pvnv_rec.ATTRIBUTE6 THEN
225 l_pvnv_rec.ATTRIBUTE6 := G_MISS_CHAR;
226 END IF;
227
228 IF p_db_rec.ATTRIBUTE7 IS NULL THEN
229 IF p_pvnv_rec.ATTRIBUTE7 IS NULL THEN
230 l_pvnv_rec.ATTRIBUTE7 := G_MISS_CHAR;
231 END IF;
232 ELSIF p_db_rec.ATTRIBUTE7 = p_pvnv_rec.ATTRIBUTE7 THEN
233 l_pvnv_rec.ATTRIBUTE7 := G_MISS_CHAR;
234 END IF;
235
236 IF p_db_rec.ATTRIBUTE8 IS NULL THEN
237 IF p_pvnv_rec.ATTRIBUTE8 IS NULL THEN
238 l_pvnv_rec.ATTRIBUTE8 := G_MISS_CHAR;
239 END IF;
240 ELSIF p_db_rec.ATTRIBUTE8 = p_pvnv_rec.ATTRIBUTE8 THEN
241 l_pvnv_rec.ATTRIBUTE8 := G_MISS_CHAR;
242 END IF;
243
244 IF p_db_rec.ATTRIBUTE9 IS NULL THEN
245 IF p_pvnv_rec.ATTRIBUTE9 IS NULL THEN
246 l_pvnv_rec.ATTRIBUTE9 := G_MISS_CHAR;
247 END IF;
248 ELSIF p_db_rec.ATTRIBUTE9 = p_pvnv_rec.ATTRIBUTE9 THEN
249 l_pvnv_rec.ATTRIBUTE9 := G_MISS_CHAR;
250 END IF;
251
252 IF p_db_rec.ATTRIBUTE10 IS NULL THEN
253 IF p_pvnv_rec.ATTRIBUTE10 IS NULL THEN
254 l_pvnv_rec.ATTRIBUTE10 := G_MISS_CHAR;
255 END IF;
256 ELSIF p_db_rec.ATTRIBUTE10 = p_pvnv_rec.ATTRIBUTE10 THEN
257 l_pvnv_rec.ATTRIBUTE10 := G_MISS_CHAR;
258 END IF;
259
260 IF p_db_rec.ATTRIBUTE11 IS NULL THEN
261 IF p_pvnv_rec.ATTRIBUTE11 IS NULL THEN
262 l_pvnv_rec.ATTRIBUTE11 := G_MISS_CHAR;
263 END IF;
264 ELSIF p_db_rec.ATTRIBUTE11 = p_pvnv_rec.ATTRIBUTE11 THEN
265 l_pvnv_rec.ATTRIBUTE11 := G_MISS_CHAR;
266 END IF;
267
268 IF p_db_rec.ATTRIBUTE12 IS NULL THEN
269 IF p_pvnv_rec.ATTRIBUTE12 IS NULL THEN
270 l_pvnv_rec.ATTRIBUTE12 := G_MISS_CHAR;
271 END IF;
272 ELSIF p_db_rec.ATTRIBUTE12 = p_pvnv_rec.ATTRIBUTE12 THEN
273 l_pvnv_rec.ATTRIBUTE12 := G_MISS_CHAR;
274 END IF;
275
276 IF p_db_rec.ATTRIBUTE13 IS NULL THEN
277 IF p_pvnv_rec.ATTRIBUTE13 IS NULL THEN
278 l_pvnv_rec.ATTRIBUTE13 := G_MISS_CHAR;
279 END IF;
280 ELSIF p_db_rec.ATTRIBUTE13 = p_pvnv_rec.ATTRIBUTE13 THEN
281 l_pvnv_rec.ATTRIBUTE13 := G_MISS_CHAR;
282 END IF;
283
284 IF p_db_rec.ATTRIBUTE14 IS NULL THEN
285 IF p_pvnv_rec.ATTRIBUTE14 IS NULL THEN
286 l_pvnv_rec.ATTRIBUTE5 := G_MISS_CHAR;
287 END IF;
288 ELSIF p_db_rec.ATTRIBUTE14 = p_pvnv_rec.ATTRIBUTE5 THEN
289 l_pvnv_rec.ATTRIBUTE14 := G_MISS_CHAR;
290 END IF;
291
292 IF p_db_rec.ATTRIBUTE15 IS NULL THEN
293 IF p_pvnv_rec.ATTRIBUTE15 IS NULL THEN
294 l_pvnv_rec.ATTRIBUTE15 := G_MISS_CHAR;
295 END IF;
296 ELSIF p_db_rec.ATTRIBUTE15 = p_pvnv_rec.ATTRIBUTE15 THEN
297 l_pvnv_rec.ATTRIBUTE15 := G_MISS_CHAR;
298 END IF;
299
300 x_pvnv_rec := l_pvnv_rec;
301 END get_changes_only;
302
303 ---------------------------------------------------------------------------
304 -- PROCEDURE determine_action for: OKL_PROVISIONS_V
305 -- This function helps in determining the various checks to be performed
306 -- for the new/updated record and also helps in determining whether a new
307 -- version is required or not
308 ---------------------------------------------------------------------------
309 FUNCTION determine_action (
310 p_upd_pvnv_rec IN pvnv_rec_type,
311 p_db_pvnv_rec IN pvnv_rec_type,
312 p_date IN DATE
313 ) RETURN VARCHAR2 IS
314 l_action VARCHAR2(1);
315 l_sysdate DATE := trunc(SYSDATE);
316 BEGIN
317
318 /* Scenario 1: The Changed Field-Values can by-pass Validation */
319 IF p_upd_pvnv_rec.from_date = G_MISS_DATE AND
320 p_upd_pvnv_rec.to_date = G_MISS_DATE AND
321 p_upd_pvnv_rec.app_debit_ccid = G_MISS_NUM AND
322 p_upd_pvnv_rec.app_credit_ccid = G_MISS_NUM AND
323 p_upd_pvnv_rec.rev_debit_ccid = G_MISS_NUM AND
324 p_upd_pvnv_rec.rev_credit_ccid = G_MISS_NUM THEN
325 l_action := '1';
326
327 /* Scenario 2: The Changed Field-Values include that needs Validation and Update
328 but does not require a new vresion to be created
329 */
330 -- 1) Only End_Date is Changed
331 ELSIF (p_upd_pvnv_rec.from_date = G_MISS_DATE AND
332 (p_upd_pvnv_rec.to_date <> G_MISS_DATE OR
333 -- IS NULL Condition has been added in case end_date was updated to NULL
334 p_upd_pvnv_rec.to_date IS NULL ) AND
335 p_upd_pvnv_rec.app_debit_ccid = G_MISS_NUM AND
336 p_upd_pvnv_rec.app_credit_ccid = G_MISS_NUM AND
337 p_upd_pvnv_rec.rev_debit_ccid = G_MISS_NUM AND
338 p_upd_pvnv_rec.rev_credit_ccid = G_MISS_NUM) OR
339 -- 2) Critical Attributes are Changed but Start_Date is Today or Future
340 (p_upd_pvnv_rec.from_date = G_MISS_DATE AND
341 p_db_pvnv_rec.from_date >= p_date AND
342 (p_upd_pvnv_rec.app_debit_ccid <> G_MISS_NUM OR
343 p_upd_pvnv_rec.app_credit_ccid <> G_MISS_NUM OR
344 p_upd_pvnv_rec.rev_debit_ccid <> G_MISS_NUM OR
345 p_upd_pvnv_rec.rev_credit_ccid <> G_MISS_NUM)) OR
346 -- 3) Start_Date is Changed , but in Future
347 (p_upd_pvnv_rec.from_date <> G_MISS_DATE AND
348 p_db_pvnv_rec.from_date > p_date AND
349 p_upd_pvnv_rec.from_date >= p_date) THEN
350 l_action := '2';
351
352 ELSE
353 /* Scenario 3: The Changed Field-Values mandate Creation of a New Version/Record */
354 l_action := '3';
355 END IF;
356 RETURN(l_action);
357 END determine_action;
358
359 ---------------------------------------------------------------------------
360 -- PROCEDURE check_updates
361 -- To verify whether the requested changes from the screen are valid or not
362 ---------------------------------------------------------------------------
363 PROCEDURE check_updates (p_pvnv_rec IN pvnv_rec_type,
364 x_return_status OUT NOCOPY VARCHAR2,
365 x_msg_data OUT NOCOPY VARCHAR2
366 ) IS
367 l_pvnv_rec pvnv_rec_type;
368 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
369 l_valid BOOLEAN;
370 l_attrib_tbl okl_accounting_util.overlap_attrib_tbl_type;
371 BEGIN
372 l_pvnv_rec := p_pvnv_rec;
373
374 /* call check_overlaps */
375 l_attrib_tbl(1).attribute := 'name';
376 l_attrib_tbl(1).attrib_type := okl_accounting_util.G_VARCHAR2;
377 l_attrib_tbl(1).value := l_pvnv_rec.name;
378
379 okl_accounting_util.check_overlaps(p_id => l_pvnv_rec.id,
380 p_attrib_tbl => l_attrib_tbl,
381 p_start_date_attribute_name => 'FROM_DATE',
382 p_start_date => l_pvnv_rec.from_date,
383 p_end_date_attribute_name => 'TO_DATE',
384 p_end_date => l_pvnv_rec.to_date,
385 p_view => 'OKL_PROVISIONS_V',
386 x_return_status => l_return_status,
387 x_valid => l_valid);
388
389
390 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
391 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
392 RAISE G_EXCEPTION_HALT_PROCESSING;
393 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) OR
394 (l_return_status = OKL_API.G_RET_STS_SUCCESS AND
395 l_valid <> TRUE) THEN
396 x_return_status := OKL_API.G_RET_STS_ERROR;
397 RAISE G_EXCEPTION_HALT_PROCESSING;
398 END IF;
399 x_return_status := l_return_status;
400 EXCEPTION
401 WHEN G_EXCEPTION_HALT_PROCESSING THEN
402 -- no processing necessary; validation can continue
403 -- with the next column
404 NULL;
405
406 WHEN OTHERS THEN
407 -- store SQL error message on message stack for caller
408 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
409 p_msg_name => G_UNEXPECTED_ERROR,
410 p_token1 => G_SQLCODE_TOKEN,
411 p_token1_value => sqlcode,
412 p_token2 => G_SQLERRM_TOKEN,
413 p_token2_value => sqlerrm );
414 x_msg_data := 'Unexpected Database Error';
415 -- notify caller of an UNEXPECTED error
416 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
417
418 END check_updates;
419
420
421 ---------------------------------------------------------------------------
422 -- PROCEDURE create_provisions for: OKL_PROVISIONS_V
423 ---------------------------------------------------------------------------
424 PROCEDURE create_provisions(p_api_version IN NUMBER,
425 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
426 x_return_status OUT NOCOPY VARCHAR2,
427 x_msg_count OUT NOCOPY NUMBER,
428 x_msg_data OUT NOCOPY VARCHAR2,
429 p_pvnv_rec IN pvnv_rec_type,
430 x_pvnv_rec OUT NOCOPY pvnv_rec_type ) IS
431
432 l_api_version CONSTANT NUMBER := 1;
433 l_api_name CONSTANT VARCHAR2(30) := 'create_provisions';
434 l_no_data_found BOOLEAN := TRUE;
435 l_valid BOOLEAN := TRUE;
436 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
437 l_pvnv_rec pvnv_rec_type;
438 l_sysdate DATE := to_date(SYSDATE, 'DD/MM/YYYY');
439 BEGIN
440 l_return_status := OKL_API.G_RET_STS_SUCCESS;
441 l_pvnv_rec := p_pvnv_rec;
442
443 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
444 p_pkg_name => G_PKG_NAME,
445 p_init_msg_list => p_init_msg_list,
446 l_api_version => l_api_version,
447 p_api_version => p_api_version,
448 p_api_type => '_PVT',
449 x_return_status => l_return_status);
450 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
451 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
452 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
453 RAISE OKL_API.G_EXCEPTION_ERROR;
454 END IF;
455
456 /* validate name */
457 IF (l_pvnv_rec.name IS NULL OR l_pvnv_rec.name = G_MISS_CHAR) THEN
458 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
459 p_msg_name => 'OKL_PVN_NAME_ERROR');
460 RAISE OKL_API.G_EXCEPTION_ERROR;
461 END IF;
462
463 /* validate on application debit account */
464 IF (l_pvnv_rec.app_debit_ccid IS NULL OR l_pvnv_rec.app_debit_ccid = G_MISS_NUM) THEN
465 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
466 p_msg_name => 'OKL_PVN_CCID_ERROR');
467 RAISE OKL_API.G_EXCEPTION_ERROR;
468 END IF;
469
470 /* validate on application credit account */
471 IF (l_pvnv_rec.app_credit_ccid IS NULL OR l_pvnv_rec.app_credit_ccid = G_MISS_NUM) THEN
472 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
473 p_msg_name => 'OKL_PVN_CCID_ERROR');
474 RAISE OKL_API.G_EXCEPTION_ERROR;
475 END IF;
476
477 /* validate on reversal debit account */
478 IF (l_pvnv_rec.rev_debit_ccid IS NULL OR l_pvnv_rec.rev_debit_ccid = G_MISS_NUM) THEN
479 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
480 p_msg_name => 'OKL_PVN_CCID_ERROR');
481 RAISE OKL_API.G_EXCEPTION_ERROR;
482 END IF;
483
484 /* validate on reversal credit account */
485 IF (l_pvnv_rec.rev_credit_ccid IS NULL OR l_pvnv_rec.rev_credit_ccid = G_MISS_NUM) THEN
486 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
487 p_msg_name => 'OKL_PVN_CCID_ERROR');
488 RAISE OKL_API.G_EXCEPTION_ERROR;
489 END IF;
490
491 /* validate effective start date */
492 IF (l_pvnv_rec.from_date IS NULL OR l_pvnv_rec.from_date = G_MISS_DATE) THEN
493 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
494 p_msg_name => 'OKL_PVN_DATE_ERROR');
495 RAISE OKL_API.G_EXCEPTION_ERROR;
496 END IF;
497
498 /* check for the records with start and end dates less than sysdate */
499 IF to_date(l_pvnv_rec.from_date, 'DD/MM/YYYY') < l_sysdate OR
500 to_date(l_pvnv_rec.to_date, 'DD/MM/YYYY') < l_sysdate THEN
501 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
502 p_msg_name => G_PAST_RECORDS);
503 RAISE OKL_API.G_EXCEPTION_ERROR;
504 END IF;
505
506
507 /* public api to insert provision type */
508 OKL_PROVISIONS_PUB.INSERT_PROVISIONS(p_api_version => l_api_version,
509 p_init_msg_list => p_init_msg_list,
510 x_return_status => l_return_status,
511 x_msg_count => x_msg_count,
512 x_msg_data => x_msg_data,
513 p_pvnv_rec => l_pvnv_rec,
514 x_pvnv_rec => x_pvnv_rec);
515
516 IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
517 RAISE OKL_API.G_EXCEPTION_ERROR;
518 ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
519 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
520 END IF;
521
522 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
523 x_msg_data => x_msg_data);
524 x_return_status := l_return_status;
525 EXCEPTION
526 WHEN OKL_API.G_EXCEPTION_ERROR THEN
527 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
528 p_pkg_name => G_PKG_NAME,
529 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
530 x_msg_count => x_msg_count,
531 x_msg_data => x_msg_data,
532 p_api_type => '_PVT');
533 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
534 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
535 p_pkg_name => G_PKG_NAME,
536 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
537 x_msg_count => x_msg_count,
538 x_msg_data => x_msg_data,
539 p_api_type => '_PVT');
540 WHEN OTHERS THEN
541 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
542 p_pkg_name => G_PKG_NAME,
543 p_exc_name => 'OTHERS',
544 x_msg_count => x_msg_count,
545 x_msg_data => x_msg_data,
546 p_api_type => '_PVT');
547
548 END create_provisions;
549
550 ---------------------------------------------------------------------------
551 -- PROCEDURE update_provisions for: OKL_PROVISIONS_V
552 ---------------------------------------------------------------------------
553 PROCEDURE update_provisions(p_api_version IN NUMBER,
554 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
555 x_return_status OUT NOCOPY VARCHAR2,
556 x_msg_count OUT NOCOPY NUMBER,
557 x_msg_data OUT NOCOPY VARCHAR2,
558 p_pvnv_rec IN pvnv_rec_type,
559 x_pvnv_rec OUT NOCOPY pvnv_rec_type
560 ) IS
561 l_api_version CONSTANT NUMBER := 1;
562 l_api_name CONSTANT VARCHAR2(30) := 'update_provisions';
563 l_no_data_found BOOLEAN := TRUE;
564 l_valid BOOLEAN := TRUE;
565 l_oldversion_enddate DATE := to_date(SYSDATE, 'DD/MM/YYYY');
566 l_sysdate DATE := to_date(SYSDATE, 'DD/MM/YYYY');
567 l_db_pvnv_rec pvnv_rec_type; /* database copy */
568 l_upd_pvnv_rec pvnv_rec_type; /* input copy */
569 l_pvnv_rec pvnv_rec_type := p_pvnv_rec; /* latest with the retained changes */
570 l_tmp_pvnv_rec pvnv_rec_type; /* for any other purposes */
571 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
572 l_action VARCHAR2(1);
573 l_new_version VARCHAR2(100);
574 l_attrib_tbl okl_accounting_util.overlap_attrib_tbl_type;
575 BEGIN
576 l_return_status := OKL_API.G_RET_STS_SUCCESS;
577 --l_upd_pvnv_rec := p_pvnv_rec;
578
579 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
580 p_pkg_name => G_PKG_NAME,
581 p_init_msg_list => p_init_msg_list,
582 l_api_version => l_api_version,
583 p_api_version => p_api_version,
584 p_api_type => '_PVT',
585 x_return_status => l_return_status);
586 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
587 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
588 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
589 RAISE OKL_API.G_EXCEPTION_ERROR;
590 END IF;
591
592 /* fetch old details from the database */
593 get_rec(p_pvnv_rec => p_pvnv_rec,
594 x_return_status => l_return_status,
595 x_no_data_found => l_no_data_found,
596 x_pvnv_rec => l_db_pvnv_rec);
597
598 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS OR
599 l_no_data_found = TRUE THEN
600 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
601 END IF;
602
603 /* check for the records if start and end dates are in the past */
604 IF to_date(l_db_pvnv_rec.from_date,'DD/MM/YYYY') < l_sysdate AND
605 to_date(l_db_pvnv_rec.to_date,'DD/MM/YYYY') < l_sysdate THEN
606 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
607 p_msg_name => G_PAST_RECORDS);
608 RAISE OKL_API.G_EXCEPTION_ERROR;
609 END IF;
610
611 /* retain the details that has been changed only */
612 get_changes_only(p_pvnv_rec => p_pvnv_rec,
613 p_db_rec => l_db_pvnv_rec,
614 x_pvnv_rec => l_upd_pvnv_rec);
615
616 /* check for start date lesser than sysdate */
617 IF to_date(l_upd_pvnv_rec.from_date, 'DD/MM/YYYY') <> to_date(G_MISS_DATE, 'DD/MM/YYYY') AND
618 to_date(l_upd_pvnv_rec.from_date,'DD/MM/YYYY') < l_sysdate THEN
619 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
620 p_msg_name => G_START_DATE);
621 RAISE OKL_API.G_EXCEPTION_ERROR;
622 END IF;
623
624 /* check for end date lesser than sysdate */
625 IF to_date(l_upd_pvnv_rec.to_date, 'DD/MM/YYYY') <> to_date(G_MISS_DATE, 'DD/MM/YYYY') AND
626 to_date(l_upd_pvnv_rec.to_date,'DD/MM/YYYY') < l_sysdate THEN
627 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
628 p_msg_name => G_END_DATE);
629 RAISE OKL_API.G_EXCEPTION_ERROR;
630 END IF;
631
632 /* determine how the processing to be done */
633 l_action := determine_action(p_upd_pvnv_rec => l_upd_pvnv_rec,
634 p_db_pvnv_rec => l_db_pvnv_rec,
635 p_date => l_sysdate);
636
637
638 /* Scenario 1: The Changed Field-Values can by-pass Validation */
639 IF l_action = '1' THEN
640 /* public api to update provisions*/
641 OKL_PROVISIONS_PUB.UPDATE_PROVISIONS(p_api_version => l_api_version,
642 p_init_msg_list => p_init_msg_list,
643 x_return_status => l_return_status,
644 x_msg_count => x_msg_count,
645 x_msg_data => x_msg_data,
646 p_pvnv_rec => l_upd_pvnv_rec,
647 x_pvnv_rec => x_pvnv_rec);
648 IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
649 RAISE OKL_API.G_EXCEPTION_ERROR;
650 ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
651 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
652 END IF;
653
654 /* Scenario 2: The Changed Field-Values include that needs Validation and Update */
655 ELSIF l_action = '2' THEN
656 /* create a temporary record with all relevant details from db and upd records */
657 l_pvnv_rec := p_pvnv_rec;
658
659 check_updates(p_pvnv_rec => l_pvnv_rec,
660 x_return_status => l_return_status,
661 x_msg_data => x_msg_data);
662 IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
663 RAISE OKL_API.G_EXCEPTION_ERROR;
664 ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
665 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
666 END IF;
667
668 /* public api to update provisions */
669 OKL_PROVISIONS_PUB.UPDATE_PROVISIONS(p_api_version => l_api_version,
670 p_init_msg_list => p_init_msg_list,
671 x_return_status => l_return_status,
672 x_msg_count => x_msg_count,
673 x_msg_data => x_msg_data,
674 p_pvnv_rec => l_upd_pvnv_rec,
675 x_pvnv_rec => x_pvnv_rec);
676 IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
677 RAISE OKL_API.G_EXCEPTION_ERROR;
678 ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
679 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
680 END IF;
681
682 /* Scenario 3: The Changed Field-Values mandate Creation of a New Version/Record */
683 ELSIF l_action = '3' THEN
684
685 /* for old version */
686 IF l_upd_pvnv_rec.from_date <> G_MISS_DATE THEN
687 l_oldversion_enddate := l_upd_pvnv_rec.from_date - 1;
688 ELSE
689 l_oldversion_enddate := l_sysdate - 1;
690 END IF;
691
692 l_pvnv_rec := l_db_pvnv_rec;
693 l_pvnv_rec.to_date := l_oldversion_enddate;
694
695 /* call verify changes to update the database */
696 IF l_oldversion_enddate > l_db_pvnv_rec.to_date THEN
697 check_updates(p_pvnv_rec => l_pvnv_rec,
698 x_return_status => l_return_status,
699 x_msg_data => x_msg_data);
700
701 IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
702 RAISE OKL_API.G_EXCEPTION_ERROR;
703 ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
704 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
705 END IF;
706 END IF;
707
708 /* public api to update provisions */
709 OKL_PROVISIONS_PUB.UPDATE_PROVISIONS(p_api_version => l_api_version,
710 p_init_msg_list => p_init_msg_list,
711 x_return_status => l_return_status,
712 x_msg_count => x_msg_count,
713 x_msg_data => x_msg_data,
714 p_pvnv_rec => l_pvnv_rec,
715 x_pvnv_rec => x_pvnv_rec);
716
717 IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
718 RAISE OKL_API.G_EXCEPTION_ERROR;
719 ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
720 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
721 END IF;
722
723 /* for new version */
724 /* create a temporary record with all relevant details from db and upd records */
725 /* removed call to default_to_actuals sgiyer 02-06-02 */
726 l_pvnv_rec := p_pvnv_rec;
727
728 IF l_upd_pvnv_rec.from_date = G_MISS_DATE THEN
729 l_pvnv_rec.from_date := l_sysdate;
730 END IF;
731
732 l_attrib_tbl(1).attribute := 'name';
733 l_attrib_tbl(1).attrib_type := okl_accounting_util.G_VARCHAR2;
734 l_attrib_tbl(1).value := l_pvnv_rec.name;
735
736 okl_accounting_util.get_version(
737 p_attrib_tbl => l_attrib_tbl,
738 p_cur_version => l_pvnv_rec.version,
739 p_end_date_attribute_name => 'TO_DATE',
740 p_end_date => l_pvnv_rec.to_date,
741 p_view => 'OKL_PROVISIONS_V',
742 x_return_status => l_return_status,
743 x_new_version => l_new_version);
744
745 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
746 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
747 ELSE
748 l_pvnv_rec.version := l_new_version;
749 END IF;
750
751 l_pvnv_rec.id := G_MISS_NUM;
752
753 /* call verify changes to update the database */
754 /* call verify changes to update the database */
755 IF l_pvnv_rec.from_date > l_db_pvnv_rec.to_date THEN
756 check_updates(p_pvnv_rec => l_pvnv_rec,
757 x_return_status => l_return_status,
758 x_msg_data => x_msg_data);
759 IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
760 RAISE OKL_API.G_EXCEPTION_ERROR;
761 ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
762 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
763 END IF;
764 END IF;
765
766 /* public api to insert provisions */
767 OKL_PROVISIONS_PUB.INSERT_PROVISIONS(p_api_version => l_api_version,
768 p_init_msg_list => p_init_msg_list,
769 x_return_status => l_return_status,
770 x_msg_count => x_msg_count,
771 x_msg_data => x_msg_data,
772 p_pvnv_rec => l_pvnv_rec,
773 x_pvnv_rec => x_pvnv_rec);
774
775 IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
776 RAISE OKL_API.G_EXCEPTION_ERROR;
777 ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
778 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
779 END IF;
780
781 /* copy output to input structure to get the id */
782 l_pvnv_rec := x_pvnv_rec;
783
784 END IF;
785
786 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
787 x_msg_data => x_msg_data);
788 x_return_status := l_return_status;
789 EXCEPTION
790 WHEN OKL_API.G_EXCEPTION_ERROR THEN
791 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
792 p_pkg_name => G_PKG_NAME,
793 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
794 x_msg_count => x_msg_count,
795 x_msg_data => x_msg_data,
796 p_api_type => '_PVT');
797 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
798 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
799 p_pkg_name => G_PKG_NAME,
800 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
801 x_msg_count => x_msg_count,
802 x_msg_data => x_msg_data,
803 p_api_type => '_PVT');
804 WHEN OTHERS THEN
805 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
806 p_pkg_name => G_PKG_NAME,
807 p_exc_name => 'OTHERS',
808 x_msg_count => x_msg_count,
809 x_msg_data => x_msg_data,
810 p_api_type => '_PVT');
811
812 END update_provisions;
813
814 PROCEDURE create_provisions(
815 p_api_version IN NUMBER,
816 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
817 x_return_status OUT NOCOPY VARCHAR2,
818 x_msg_count OUT NOCOPY NUMBER,
819 x_msg_data OUT NOCOPY VARCHAR2,
820 p_pvnv_tbl IN pvnv_tbl_type,
821 x_pvnv_tbl OUT NOCOPY pvnv_tbl_type)
822
823 IS
824
825 l_api_version NUMBER := 1.0;
826
827 BEGIN
828
829 OKL_PROVISIONS_PUB.INSERT_PROVISIONS(p_api_version => l_api_version,
830 p_init_msg_list => p_init_msg_list,
831 x_return_status => x_return_Status,
832 x_msg_count => x_msg_count,
833 x_msg_data => x_msg_data,
834 p_pvnv_tbl => p_pvnv_tbl,
835 x_pvnv_tbl => x_pvnv_tbl);
836
837 END create_provisions;
838
839 PROCEDURE update_provisions(
840 p_api_version IN NUMBER,
841 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
842 x_return_status OUT NOCOPY VARCHAR2,
843 x_msg_count OUT NOCOPY NUMBER,
844 x_msg_data OUT NOCOPY VARCHAR2,
845 p_pvnv_tbl IN pvnv_tbl_type,
846 x_pvnv_tbl OUT NOCOPY pvnv_tbl_type)
847
848 IS
849 l_api_version NUMBER := 1.0;
850
851 BEGIN
852
853 OKL_PROVISIONS_PUB.UPDATE_PROVISIONS(p_api_version => l_api_version,
854 p_init_msg_list => p_init_msg_list,
855 x_return_status => x_return_Status,
856 x_msg_count => x_msg_count,
857 x_msg_data => x_msg_data,
858 p_pvnv_tbl => p_pvnv_tbl,
859 x_pvnv_tbl => x_pvnv_tbl);
860
861 END update_provisions;
862
863
864 PROCEDURE delete_provisions(
865 p_api_version IN NUMBER,
866 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
867 x_return_status OUT NOCOPY VARCHAR2,
868 x_msg_count OUT NOCOPY NUMBER,
869 x_msg_data OUT NOCOPY VARCHAR2,
870 p_pvnv_rec IN pvnv_rec_type)
871
872 IS
873 l_api_version NUMBER := 1.0;
874
875 BEGIN
876
877 OKL_PROVISIONS_PUB.DELETE_PROVISIONS(p_api_version => l_api_version,
878 p_init_msg_list => p_init_msg_list,
879 x_return_status => x_return_Status,
880 x_msg_count => x_msg_count,
881 x_msg_data => x_msg_data,
882 p_pvnv_rec => p_pvnv_rec);
883
884
885 END delete_provisions;
886
887 PROCEDURE delete_provisions(
888 p_api_version IN NUMBER,
889 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
890 x_return_status OUT NOCOPY VARCHAR2,
891 x_msg_count OUT NOCOPY NUMBER,
892 x_msg_data OUT NOCOPY VARCHAR2,
893 p_pvnv_tbl IN pvnv_tbl_type)
894
895 IS
896
897 l_api_version NUMBER := 1.0;
898
899 BEGIN
900
901 OKL_PROVISIONS_PUB.DELETE_PROVISIONS(p_api_version => l_api_version,
902 p_init_msg_list => p_init_msg_list,
903 x_return_status => x_return_Status,
904 x_msg_count => x_msg_count,
905 x_msg_data => x_msg_data,
906 p_pvnv_tbl => p_pvnv_tbl);
907
908 END delete_provisions;
909
910
911 END OKL_PROCESS_PROVISIONS_PVT;