[Home] [Help]
PACKAGE BODY: APPS.OKL_CASH_APPLN_RULE_PVT
Source
1 PACKAGE BODY OKL_CASH_APPLN_RULE_PVT AS
2 /* $Header: OKLRCSLB.pls 120.6.12010000.2 2010/02/19 11:11:05 nikshah ship $ */
3
4 ---------------------------------------------------------------------------
5 -- Procedures and Functions
6 ---------------------------------------------------------------------------
7 PROCEDURE manipulate_rule(
8 p_api_version IN NUMBER,
9 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
10 p_catv_rec IN catv_rec_type,
11 x_catv_rec OUT NOCOPY catv_rec_type,
12 x_return_status OUT NOCOPY VARCHAR2,
13 x_msg_count OUT NOCOPY NUMBER,
14 x_msg_data OUT NOCOPY VARCHAR2) IS
15
16 l_api_name CONSTANT VARCHAR2(30) := 'manipulate_rule';
17 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
18
19 date_invalid_err CONSTANT VARCHAR2(30) := 'INVALID';
20 date_gap_err CONSTANT VARCHAR2(30) := 'GAP';
21 date_overlap_err CONSTANT VARCHAR2(30) := 'OVERLAP';
22 date_enddate_err CONSTANT VARCHAR2(30) := 'ENDDATE';
23
24 lx_date_err VARCHAR2(30) := NULL;
25
26 l_catv_rec catv_rec_type;
27 lx_catv_rec catv_rec_type;
28
29 l_prev_catv_rec catv_rec_type;
30 lx_prev_catv_rec catv_rec_type;
31
32 l_cauv_rec cauv_rec_type;
33 lx_cauv_rec cauv_rec_type;
34
35 l_default_cau_id OKL_CASH_ALLCTN_RLS.cau_id%type := NULL;
36 l_end_date OKL_CASH_ALLCTN_RLS.end_date%type := NULL;
37 l_rule_name OKL_CASH_ALLCTN_RLS.name%type := NULL;
38 l_req_field_miss BOOLEAN := FALSE;
39
40 --Bug 8633025 - NIKSHAH
41 l_def_catv_tbl catv_tbl_type;
42 x_def_catv_tbl catv_tbl_type;
43 l_counter NUMBER;
44
45 CURSOR l_cau_obj_ver_csr(cp_id IN l_cauv_rec.id%TYPE) IS
46 SELECT object_version_number
47 , name
48 FROM OKL_CSH_ALLCTN_RL_HDR
49 WHERE id = cp_id;
50
51 CURSOR l_cau_name_csr(cp_name IN l_cauv_rec.name%TYPE) IS
52 SELECT name
53 FROM OKL_CSH_ALLCTN_RL_HDR
54 WHERE name = cp_name;
55
56
57 CURSOR l_end_date_csr(cp_id IN OKL_CASH_ALLCTN_RLS.id%TYPE) IS
58 SELECT end_date
59 FROM OKL_CASH_ALLCTN_RLS
60 WHERE id = cp_id;
61
62 CURSOR l_prev_line_csr(cp_cau_id IN OKL_CASH_ALLCTN_RLS.cau_id%TYPE) IS
63 SELECT id
64 , object_version_number
65 , start_date
66 , end_date
67 FROM OKL_CASH_ALLCTN_RLS
68 WHERE cau_id = cp_cau_id
69 ORDER BY id DESC;
70
71 --Added below cursor for getting default rules of an active date
72 --Bug 8633025 - NIKSHAH
73 CURSOR l_def_rule_csr IS
74 SELECT id
75 FROM OKL_CASH_ALLCTN_RLS
76 WHERE DEFAULT_RULE = 'YES'
77 AND TRUNC(NVL(END_DATE, SYSDATE)) >= TRUNC(SYSDATE);
78
79 --Validate start and end dates
80 FUNCTION validate_dates(p_catv_rec IN catv_rec_type
81 ,x_err_type OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
82 l_catv_rec catv_rec_type := p_catv_rec;
83 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
84
85 /*TYPE date_range_type IS RECORD (start_date DATE := NULL
86 ,end_date DATE := NULL);
87
88 TYPE date_range_tbl_type IS TABLE OF date_range_type
89 INDEX BY BINARY_INTEGER;
90
91 date_tab date_range_tbl_type;*/
92
93 --check for overlap in dates
94 CURSOR l_date_overlap_csr(cp_id IN NUMBER
95 ,cp_cau_id IN NUMBER
96 ,cp_start_date IN DATE
97 ,cp_end_date IN DATE) IS
98 SELECT start_date, end_date
99 FROM OKL_CASH_ALLCTN_RLS
100 WHERE cau_id = nvl(cp_cau_id, -99)
101 AND id <> nvl(cp_id, -99)
102 AND (trunc(cp_start_date) between trunc(start_date) and trunc(nvl(end_date, cp_start_date))
103 OR trunc(nvl(cp_end_date, cp_start_date)) between trunc(start_date) and trunc(end_date));
104
105 --check for gaps in date ranges
106 CURSOR l_date_bef_gap_csr(cp_id IN NUMBER
107 ,cp_cau_id IN NUMBER
108 ,cp_start_date IN DATE
109 ,cp_end_date IN DATE) IS
110 SELECT start_date, end_date
111 FROM OKL_CASH_ALLCTN_RLS
112 WHERE cau_id = nvl(cp_cau_id, -99)
113 AND id <> nvl(cp_id, -99)
114 AND (trunc(end_date) < trunc(cp_start_date))
115 --OR (nvl(cp_end_date, cp_start_date) < start_date))
116 ORDER BY start_date desc;
117
118
119 CURSOR l_date_aft_gap_csr(cp_id IN NUMBER
120 ,cp_cau_id IN NUMBER
121 ,cp_start_date IN DATE
122 ,cp_end_date IN DATE) IS
123 SELECT start_date, end_date
124 FROM OKL_CASH_ALLCTN_RLS
125 WHERE cau_id = nvl(cp_cau_id, -99)
126 AND id <> nvl(cp_id, -99)
127 AND (trunc(nvl(cp_end_date, cp_start_date)) < trunc(start_date))
128 ORDER BY start_date asc;
129 BEGIN
130 --pgomes 01/13/2003 commented out code
131 --check for mandatory end date is removed
132 /*
133 IF(nvl(l_catv_rec.default_rule, okl_api.g_miss_char) = okl_api.g_miss_char) THEN
134 IF(NVL(l_catv_rec.end_date, okl_api.g_miss_date) = okl_api.g_miss_date) THEN
135 --dbms_output.put_line('end date null');
136 x_err_type := date_enddate_err;
137 l_return_status := Okl_Api.G_RET_STS_ERROR;
138 END IF;
139 END IF;
140 */
141
142 IF (l_return_status = Okl_Api.G_RET_STS_SUCCESS) THEN
143 IF(l_catv_rec.start_date > NVL(l_catv_rec.end_date, l_catv_rec.start_date)) THEN
144 --dbms_output.put_line('start date > end date');
145 x_err_type := date_invalid_err;
146 l_return_status := Okl_Api.G_RET_STS_ERROR;
147 END IF;
148 END IF;
149
150 --check for overlaps
151 IF (l_return_status = Okl_Api.G_RET_STS_SUCCESS) THEN
152 FOR cur IN l_date_overlap_csr(l_catv_rec.id, l_catv_rec.cau_id, l_catv_rec.start_date, l_catv_rec.end_date) LOOP
153 --dbms_output.put_line('overlap in dates');
154 x_err_type := date_overlap_err;
155 l_return_status := Okl_Api.G_RET_STS_ERROR;
156 END LOOP;
157 END IF;
158
159 --check for gaps in start date
160 IF (l_return_status = Okl_Api.G_RET_STS_SUCCESS) THEN
161 FOR cur IN l_date_bef_gap_csr(l_catv_rec.id, l_catv_rec.cau_id, l_catv_rec.start_date, l_catv_rec.end_date) LOOP
162 IF ((trunc(l_catv_rec.start_date) - trunc(cur.end_date)) > 1) THEN
163 --dbms_output.put_line('gap between dates : before');
164 x_err_type := date_gap_err;
165 l_return_status := Okl_Api.G_RET_STS_ERROR;
166 END IF;
167 EXIT;
168 END LOOP;
169 END IF;
170
171 --check for gaps in end date
172 IF (l_return_status = Okl_Api.G_RET_STS_SUCCESS) THEN
173 FOR cur IN l_date_aft_gap_csr(l_catv_rec.id, l_catv_rec.cau_id, l_catv_rec.start_date, l_catv_rec.end_date) LOOP
174 IF ((trunc(cur.start_date) - trunc(nvl(l_catv_rec.end_date, l_catv_rec.start_date))) > 1) THEN
175 --dbms_output.put_line('gap between dates : after');
176 x_err_type := date_gap_err;
177 l_return_status := Okl_Api.G_RET_STS_ERROR;
178 END IF;
179 EXIT;
180 END LOOP;
181 END IF;
182
183 IF(x_err_type = date_enddate_err) THEN
184 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
185 p_msg_name => 'OKL_BPD_CASH_APPLN_DATE_END');
186 ELSIF(x_err_type = date_invalid_err) THEN
187 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
188 p_msg_name => 'OKL_BPD_CASH_APPLN_DATE_ERROR');
189 ELSIF(x_err_type = date_overlap_err) THEN
190 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
191 p_msg_name => 'OKL_BPD_CASH_APPLN_DATE_OVER');
192 ELSIF(x_err_type = date_gap_err) THEN
193 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
194 p_msg_name => 'OKL_BPD_CASH_APPLN_DATE_GAP');
195 END IF;
196
197 RETURN l_return_status;
198 END validate_dates;
199
200 --Validate default rule
201 --if a default rule exists, cau_id of the default rule is returned
202 --Commented by NIKSHAH, bug 8633025
203 /*
204 FUNCTION validate_default_rule(p_catv_rec IN catv_rec_type) RETURN NUMBER IS
205 l_catv_rec catv_rec_type := p_catv_rec;
206 l_default_cau_id OKL_CASH_ALLCTN_RLS.cau_id%type := NULL;
207
208 CURSOR l_def_csr(cp_cau_id IN NUMBER) IS
209 SELECT cau_id
210 FROM OKL_CASH_ALLCTN_RLS
211 WHERE ((NVL(l_catv_rec.cau_id, okl_api.g_miss_num) = okl_api.g_miss_num) OR
212 (l_catv_rec.cau_id IS NOT NULL AND cau_id = cp_cau_id))
213 AND default_rule = 'YES';
214 BEGIN
215 FOR cur IN l_def_csr(l_catv_rec.cau_id) LOOP
216 --dbms_output.put_line('default rule');
217 l_default_cau_id := cur.cau_id;
218 EXIT;
219 END LOOP;
220 RETURN l_default_cau_id;
221 END validate_default_rule;
222 */
223
224 BEGIN
225 l_catv_rec := p_catv_rec;
226
227 --check for required fields
228 IF (l_catv_rec.name = Okl_Api.G_MISS_CHAR or l_catv_rec.name IS NULL) THEN
229 l_req_field_miss := TRUE;
230 END IF;
231
232 IF (l_catv_rec.description = Okl_Api.G_MISS_CHAR or l_catv_rec.description IS NULL) THEN
233 l_req_field_miss := TRUE;
234 END IF;
235
236 IF (l_catv_rec.start_date = Okl_Api.G_MISS_DATE or l_catv_rec.start_date IS NULL) THEN
237 l_req_field_miss := TRUE;
238 END IF;
239
240 /*IF (l_catv_rec.end_date = Okl_Api.G_MISS_DATE or l_catv_rec.end_date IS NULL) THEN
241 l_req_field_miss := TRUE;
242 END IF;*/
243
244 IF (l_catv_rec.amount_tolerance_percent = Okl_Api.G_MISS_NUM or l_catv_rec.amount_tolerance_percent IS NULL) THEN
245 l_req_field_miss := TRUE;
246 END IF;
247
248 IF (l_catv_rec.days_past_quote_valid_toleranc = Okl_Api.G_MISS_NUM or l_catv_rec.days_past_quote_valid_toleranc IS NULL) THEN
249 l_req_field_miss := TRUE;
250 END IF;
251 -- sjalasut. removed validation as part of user defined streams build.
252 /*IF (l_catv_rec.months_to_bill_ahead = Okl_Api.G_MISS_NUM or l_catv_rec.months_to_bill_ahead IS NULL) THEN
253 l_req_field_miss := TRUE;
254 END IF;*/
255
256 IF(l_req_field_miss) THEN
257 OKC_API.set_message( p_app_name => G_APP_NAME,
258 p_msg_name =>'OKL_BPD_MISSING_FIELDS');
259
260 l_return_status := OKC_API.G_RET_STS_ERROR;
261 RAISE G_EXCEPTION_HALT_VALIDATION;
262 END IF;
263
264
265 l_cauv_rec.id := l_catv_rec.cau_id;
266 l_cauv_rec.name := l_catv_rec.name;
267 l_cauv_rec.description := l_catv_rec.description;
268
269 IF ((l_catv_rec.id IS NULL or l_catv_rec.id = okl_api.g_miss_num)
270 and (l_catv_rec.cau_id IS NULL or l_catv_rec.cau_id = okl_api.g_miss_num)) THEN
271 --CREATE HDR AND LINE
272
273 --check for unique rule name
274 FOR cur IN l_cau_name_csr(l_cauv_rec.name) LOOP
275 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
276 p_msg_name => 'OKL_BPD_CASH_APPLN_NAME_ERROR',
277 p_token1 => 'RULE_NAME',
278 p_token1_value => cur.name);
279 l_return_status := okl_api.G_RET_STS_ERROR;
280 raise G_EXCEPTION_HALT_VALIDATION;
281 END LOOP;
282
283 --check if line is a default line
284 --Modified as part of bug 8633025 by NIKSHAH
285 /*l_default_cau_id := validate_default_rule(l_catv_rec);
286 IF (l_default_cau_id IS NULL) THEN
287 l_catv_rec.default_rule := 'YES';
288 l_catv_rec.end_date := null;
289 END IF; */
290
291 --If default rule checkbox was checked then make other rules as
292 --non default
293 IF l_catv_rec.default_rule = 'YES' THEN
294 l_counter := 1;
295 FOR l_def_rec IN l_def_rule_csr
296 LOOP
297 l_def_catv_tbl(l_counter).id := l_def_rec.id;
298 l_def_catv_tbl(l_counter).default_rule := 'NO';
299 l_counter := l_counter + 1;
300 END LOOP;
301
302 IF l_counter > 1 THEN
303 Okl_Cash_Allctn_Rls_Pub.update_cash_allctn_rls
304 (p_api_version => p_api_version
305 ,p_init_msg_list => p_init_msg_list
306 ,x_return_status => l_return_status
307 ,x_msg_count => x_msg_count
308 ,x_msg_data => x_msg_data
309 ,p_catv_tbl => l_def_catv_tbl
310 ,x_catv_tbl => x_def_catv_tbl);
311
312 IF(l_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
313 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
314 p_msg_name => 'OKL_BPD_CASH_APPLN_LN_ERROR');
315 raise okl_api.G_EXCEPTION_ERROR;
316 END IF;
317 END IF;
318 END IF;
319
320 IF((trunc(l_catv_rec.start_date) < trunc(sysdate)) OR
321 --(l_catv_rec.end_date = okl_api.g_miss_date) OR
322 (nvl(l_catv_rec.end_date, l_catv_rec.start_date) < trunc(sysdate))
323 ) THEN
324 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
325 p_msg_name => 'OKL_BPD_CASH_APPLN_DATE_PAST');
326
327 l_return_status := Okl_Api.G_RET_STS_ERROR;
328 raise G_EXCEPTION_HALT_VALIDATION;
329 END IF;
330
331 --Validate dates
332 l_return_status := validate_dates(l_catv_rec, lx_date_err);
333 IF(l_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
334 /*OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
335 p_msg_name => 'OKL_BPD_CASH_APPLN_DATE_ERROR');*/
336 raise G_EXCEPTION_HALT_VALIDATION;
337 END IF;
338
339 --create rule hdr and line
340 IF (l_return_status = Okl_Api.G_RET_STS_SUCCESS) THEN
341 --create rule hdr
342 okl_csh_allctn_rl_hdr_pub.insert_csh_allctn_rl_hdr(
343 p_api_version => p_api_version
344 ,p_init_msg_list => p_init_msg_list
345 ,x_return_status => l_return_status
346 ,x_msg_count => x_msg_count
347 ,x_msg_data => x_msg_data
348 ,p_cauv_rec => l_cauv_rec
349 ,x_cauv_rec => lx_cauv_rec);
350
351 IF(l_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
352 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
353 p_msg_name => 'OKL_BPD_CASH_APPLN_HDR_ERROR');
354 raise okl_api.G_EXCEPTION_ERROR;
355 END IF;
356
357 l_catv_rec.cau_id := lx_cauv_rec.id;
358 IF (l_catv_rec.under_payment_allocation_code IS NULL or l_catv_rec.under_payment_allocation_code = okl_api.g_miss_char) THEN
359 l_catv_rec.under_payment_allocation_code := 'T';
360 END IF;
361
362 IF (l_catv_rec.over_payment_allocation_code IS NULL or l_catv_rec.over_payment_allocation_code = okl_api.g_miss_char) THEN
363 l_catv_rec.over_payment_allocation_code := 'M';
364 END IF;
365
366 IF (l_catv_rec.receipt_msmtch_allocation_code IS NULL or l_catv_rec.receipt_msmtch_allocation_code = okl_api.g_miss_char) THEN
367 l_catv_rec.receipt_msmtch_allocation_code := 'A';
368 END IF;
369
370 --create rule line
371 Okl_Cash_Allctn_Rls_Pub.insert_cash_allctn_rls(
372 p_api_version => p_api_version
373 ,p_init_msg_list => p_init_msg_list
374 ,x_return_status => l_return_status
375 ,x_msg_count => x_msg_count
376 ,x_msg_data => x_msg_data
377 ,p_catv_rec => l_catv_rec
378 ,x_catv_rec => lx_catv_rec);
379
380 IF(l_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
381 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
382 p_msg_name => 'OKL_BPD_CASH_APPLN_LN_ERROR');
383 raise okl_api.G_EXCEPTION_ERROR;
384 END IF;
385 END IF;
386 ELSIF ((l_catv_rec.id IS NOT NULL) and (l_catv_rec.id <> okl_api.g_miss_num)
387 and l_catv_rec.cau_id IS NOT NULL) THEN
388 --UPDATE HDR AND LINE
389
390 --check if line is a default line
391 --If default rule checkbox was checked then make other rules as
392 --non default
393
394 /* l_default_cau_id := validate_default_rule(l_catv_rec);
395 IF (l_catv_rec.cau_id = l_default_cau_id) THEN
396 l_catv_rec.default_rule := 'YES';
397
398 --check if the line had an end date
399 --if it did not, then update the end date to null
400 l_end_date := null;
401 FOR cur IN l_end_date_csr(l_catv_rec.id) LOOP
402 l_end_date := cur.end_date;
403 END LOOP;
404 l_catv_rec.end_date := l_end_date;
405 END IF; */
406 IF l_catv_rec.default_rule = 'YES' THEN
407 l_counter := 1;
408 FOR l_def_rec IN l_def_rule_csr
409 LOOP
410 l_def_catv_tbl(l_counter).id := l_def_rec.id;
411 l_def_catv_tbl(l_counter).default_rule := 'NO';
412 l_counter := l_counter + 1;
413 END LOOP;
414
415 IF l_counter > 1 THEN
416 Okl_Cash_Allctn_Rls_Pub.update_cash_allctn_rls
417 (p_api_version => p_api_version
418 ,p_init_msg_list => p_init_msg_list
419 ,x_return_status => l_return_status
420 ,x_msg_count => x_msg_count
421 ,x_msg_data => x_msg_data
422 ,p_catv_tbl => l_def_catv_tbl
423 ,x_catv_tbl => x_def_catv_tbl);
424
425 IF(l_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
426 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
427 p_msg_name => 'OKL_BPD_CASH_APPLN_LN_ERROR');
428 raise okl_api.G_EXCEPTION_ERROR;
429 END IF;
430 END IF;
431 END IF;
432
433 --Validate dates
434 l_return_status := validate_dates(l_catv_rec, lx_date_err);
435 IF(l_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
436 /*OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
437 p_msg_name => 'OKL_BPD_CASH_APPLN_DATE_ERROR');*/
438 raise G_EXCEPTION_HALT_VALIDATION;
439 END IF;
440
441 --update rule hdr and line
442 IF (l_return_status = Okl_Api.G_RET_STS_SUCCESS) THEN
443 --get hdr version
444 FOR cur IN l_cau_obj_ver_csr(l_cauv_rec.id) LOOP
445 l_cauv_rec.object_version_number := cur.object_version_number;
446 END LOOP;
447
448 --update rule hdr
449 okl_csh_allctn_rl_hdr_pub.update_csh_allctn_rl_hdr(
450 p_api_version => p_api_version
451 ,p_init_msg_list => p_init_msg_list
452 ,x_return_status => l_return_status
453 ,x_msg_count => x_msg_count
454 ,x_msg_data => x_msg_data
455 ,p_cauv_rec => l_cauv_rec
456 ,x_cauv_rec => lx_cauv_rec);
457
458 IF(l_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
459 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
460 p_msg_name => 'OKL_BPD_CASH_APPLN_HDR_ERROR');
461 raise okl_api.G_EXCEPTION_ERROR;
462 END IF;
463
464
465 --update rule line
466 Okl_Cash_Allctn_Rls_Pub.update_cash_allctn_rls(
467 p_api_version => p_api_version
468 ,p_init_msg_list => p_init_msg_list
469 ,x_return_status => l_return_status
470 ,x_msg_count => x_msg_count
471 ,x_msg_data => x_msg_data
472 ,p_catv_rec => l_catv_rec
473 ,x_catv_rec => lx_catv_rec);
474
475 IF(l_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
476 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
477 p_msg_name => 'OKL_BPD_CASH_APPLN_LN_ERROR');
478 raise okl_api.G_EXCEPTION_ERROR;
479 END IF;
480 END IF;
481 -- sjalasut. case of creating a new rule version
482 ELSIF ((l_catv_rec.id IS NULL or l_catv_rec.id = okl_api.g_miss_num) and l_catv_rec.cau_id IS NOT NULL) THEN
483 --UPDATE HEADER CREATE LINE ONLY
484 --check to see if the version name passed is the same as the original rule
485 FOR cur IN l_cau_obj_ver_csr(l_catv_rec.cau_id) LOOP
486 l_rule_name := cur.name;
487
488 IF (l_rule_name <> l_catv_rec.name) THEN
489 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
490 p_msg_name => 'OKL_BPD_CASH_APPLN_NAME_VER');
491 l_return_status := Okl_Api.G_RET_STS_ERROR;
492 raise G_EXCEPTION_HALT_VALIDATION;
493 END IF;
494 END LOOP;
495
496 --check if dates of new version are in the past
497 IF((trunc(l_catv_rec.start_date) < trunc(sysdate)) OR
498 --(l_catv_rec.end_date = okl_api.g_miss_date) OR
499 (nvl(l_catv_rec.end_date, l_catv_rec.start_date) < trunc(sysdate))
500 ) THEN
501 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
502 p_msg_name => 'OKL_BPD_CASH_APPLN_DATE_PAST');
503
504 l_return_status := Okl_Api.G_RET_STS_ERROR;
505 raise G_EXCEPTION_HALT_VALIDATION;
506 END IF;
507
508 --Update the end date of previous line to (start date minus 1) of the new line
509 FOR cur IN l_prev_line_csr(l_catv_rec.cau_id) LOOP
510 l_prev_catv_rec.id := cur.id;
511 l_prev_catv_rec.object_version_number := cur.object_version_number;
512 l_prev_catv_rec.start_date := cur.start_date;
513 l_prev_catv_rec.end_date := trunc(l_catv_rec.start_date) - 1;
514 EXIT;
515 END LOOP;
516
517 --Validate dates for prev line
518 l_return_status := validate_dates(l_prev_catv_rec, lx_date_err);
519 IF(l_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
520 /*OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
521 p_msg_name => 'OKL_BPD_CASH_APPLN_DATE_ERROR');*/
522 raise G_EXCEPTION_HALT_VALIDATION;
523 END IF;
524
525 --update prev rule line
526 Okl_Cash_Allctn_Rls_Pub.update_cash_allctn_rls(
527 p_api_version => p_api_version
528 ,p_init_msg_list => p_init_msg_list
529 ,x_return_status => l_return_status
530 ,x_msg_count => x_msg_count
531 ,x_msg_data => x_msg_data
532 ,p_catv_rec => l_prev_catv_rec
533 ,x_catv_rec => lx_prev_catv_rec);
534
535 IF(l_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
536 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
537 p_msg_name => 'OKL_BPD_CASH_APPLN_LN_ERROR');
538 raise okl_api.G_EXCEPTION_ERROR;
539 END IF;
540 --check if line is a default line
541 --If default rule checkbox was checked then make other rules as
542 --non default
543 /*
544 l_default_cau_id := validate_default_rule(l_catv_rec);
545 IF(l_catv_rec.cau_id = l_default_cau_id)THEN
546 l_catv_rec.default_rule := 'YES';
547 l_catv_rec.end_date := null;
548 ELSE
549 l_catv_Rec.default_rule := null;
550 END IF; */
551 IF l_catv_rec.default_rule = 'YES' THEN
552 l_counter := 1;
553 FOR l_def_rec IN l_def_rule_csr
554 LOOP
555 l_def_catv_tbl(l_counter).id := l_def_rec.id;
556 l_def_catv_tbl(l_counter).default_rule := 'NO';
557 l_counter := l_counter + 1;
558 END LOOP;
559
560 IF l_counter > 1 THEN
561 Okl_Cash_Allctn_Rls_Pub.update_cash_allctn_rls
562 (p_api_version => p_api_version
563 ,p_init_msg_list => p_init_msg_list
564 ,x_return_status => l_return_status
565 ,x_msg_count => x_msg_count
566 ,x_msg_data => x_msg_data
567 ,p_catv_tbl => l_def_catv_tbl
568 ,x_catv_tbl => x_def_catv_tbl);
569
570 IF(l_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
571 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
572 p_msg_name => 'OKL_BPD_CASH_APPLN_LN_ERROR');
573 raise okl_api.G_EXCEPTION_ERROR;
574 END IF;
575 END IF;
576 END IF;
577
578 --Validate dates
579 l_return_status := validate_dates(l_catv_rec, lx_date_err);
580 IF(l_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
581 /*OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
582 p_msg_name => 'OKL_BPD_CASH_APPLN_DATE_ERROR');*/
583 raise G_EXCEPTION_HALT_VALIDATION;
584 END IF;
585
586 IF (l_return_status = Okl_Api.G_RET_STS_SUCCESS) THEN
587 --get hdr version
588 FOR cur IN l_cau_obj_ver_csr(l_cauv_rec.id) LOOP
589 l_cauv_rec.object_version_number := cur.object_version_number;
590 END LOOP;
591
592 --update rule hdr
593 okl_csh_allctn_rl_hdr_pub.update_csh_allctn_rl_hdr(
594 p_api_version => p_api_version
595 ,p_init_msg_list => p_init_msg_list
596 ,x_return_status => l_return_status
597 ,x_msg_count => x_msg_count
598 ,x_msg_data => x_msg_data
599 ,p_cauv_rec => l_cauv_rec
600 ,x_cauv_rec => lx_cauv_rec);
601
602 IF(l_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
603 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
604 p_msg_name => 'OKL_BPD_CASH_APPLN_HDR_ERROR');
605 raise okl_api.G_EXCEPTION_ERROR;
606 END IF;
607
608
609 --create rule line
610 Okl_Cash_Allctn_Rls_Pub.insert_cash_allctn_rls(
611 p_api_version => p_api_version
612 ,p_init_msg_list => p_init_msg_list
613 ,x_return_status => l_return_status
614 ,x_msg_count => x_msg_count
615 ,x_msg_data => x_msg_data
616 ,p_catv_rec => l_catv_rec
617 ,x_catv_rec => lx_catv_rec);
618
619 IF(l_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
620 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
621 p_msg_name => 'OKL_BPD_CASH_APPLN_LN_ERROR');
622 raise okl_api.G_EXCEPTION_ERROR;
623 END IF;
624 END IF;
625 ELSE
626 null;
627 END IF;
628
629 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
630 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
631 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
632 RAISE okl_api.G_EXCEPTION_ERROR;
633 END IF;
634
635 x_catv_rec := lx_catv_rec;
636 x_return_status := l_return_status;
637 EXCEPTION
638 WHEN G_EXCEPTION_HALT_VALIDATION THEN
639 x_return_status := l_return_status;
640 WHEN OTHERS THEN
641 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
642 ,p_msg_name => G_UNEXPECTED_ERROR
643 ,p_token1 => G_SQLCODE_TOKEN
644 ,p_token1_value => SQLCODE
645 ,p_token2 => G_SQLERRM_TOKEN
646 ,p_token2_value => SQLERRM
647 ,p_token3 => 'Package'
648 ,p_token3_value => G_PKG_NAME
649 ,p_token4 => 'Procedure'
650 ,p_token4_value => l_api_name
651 );
652 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
653 END manipulate_rule;
654
655 ---------------------------------------------------------------------------
656 -- PROCEDURE maint_cash_appln_rule
657 ---------------------------------------------------------------------------
658 PROCEDURE maint_cash_appln_rule(
659 p_api_version IN NUMBER,
660 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
661 p_catv_tbl IN catv_tbl_type,
662 x_catv_tbl OUT NOCOPY catv_tbl_type,
663 x_return_status OUT NOCOPY VARCHAR2,
664 x_msg_count OUT NOCOPY NUMBER,
665 x_msg_data OUT NOCOPY VARCHAR2) IS
666
667 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
668 l_api_version CONSTANT NUMBER := 1;
669 l_api_name CONSTANT VARCHAR2(30) := 'maint_cash_appln_rule';
670
671 l_catv_rec catv_rec_type;
672 l_catv_tbl catv_tbl_type;
673
674 lx_catv_rec catv_rec_type;
675 lx_catv_tbl catv_tbl_type;
676
677 cnt NUMBER;
678 BEGIN
679 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
680 G_PKG_NAME,
681 p_init_msg_list,
682 l_api_version,
683 p_api_version,
684 '_PVT',
685 l_return_status);
686
687 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
688 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
689 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
690 RAISE OKC_API.G_EXCEPTION_ERROR;
691 END IF;
692
693 -- Processing starts
694 l_catv_tbl := p_catv_tbl;
695
696 cnt := l_catv_tbl.FIRST;
697 WHILE (cnt IS NOT NULL)
698 LOOP
699 l_catv_rec := l_catv_tbl(cnt);
700 --dbms_output.put_line(l_catv_rec.id || ' : ' || l_catv_rec.name);
701
702 --call to api that does record level manipulation
703 manipulate_rule(
704 p_api_version => l_api_version,
705 p_init_msg_list => p_init_msg_list,
706 p_catv_rec => l_catv_rec,
707 x_catv_rec => lx_catv_rec,
708 x_return_status => l_return_status,
709 x_msg_count => x_msg_count,
710 x_msg_data => x_msg_data);
711
712 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
713 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
714 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
715 RAISE okl_api.G_EXCEPTION_ERROR;
716 END IF;
717
718 lx_catv_tbl(cnt) := lx_catv_rec;
719 cnt := l_catv_tbl.NEXT(cnt);
720 END LOOP;
721
722 -- Processing ends
723 x_catv_tbl := lx_catv_tbl;
724 x_return_status := l_return_status;
725 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
726 EXCEPTION
727 WHEN OKL_API.G_EXCEPTION_ERROR THEN
728 x_return_status := OKL_API.G_RET_STS_ERROR;
729 WHEN OTHERS THEN
730 x_return_status := OKC_API.HANDLE_EXCEPTIONS
731 (
732 l_api_name,
733 G_PKG_NAME,
734 'OTHERS',
735 x_msg_count,
736 x_msg_data,
737 '_PVT'
738 );
739 END maint_cash_appln_rule;
740
741 ---------------------------------------------------------------------------
742 -- FUNCTION get_strm_typ_allocs
743 ---------------------------------------------------------------------------
744 FUNCTION get_strm_typ_allocs(
745 p_cat_id IN NUMBER,
746 p_sty_id IN NUMBER,
747 p_stream_allc_type IN VARCHAR2,
748 p_out_field IN VARCHAR2 DEFAULT 'ALL') RETURN VARCHAR2 IS
749
750 lx_retval VARCHAR2(100) := NULL;
751 BEGIN
752 SELECT DECODE(p_out_field, 'SEQ', TO_CHAR(sequence_number), 'SAT', stream_allc_type, '$' || sequence_number || '$' || stream_allc_type || '$')
753 INTO lx_retval from OKL_STRM_TYP_ALLOCS
754 WHERE cat_id = p_cat_id
755 AND sty_id = p_sty_id
756 AND stream_allc_type = p_stream_allc_type;
757
758 RETURN lx_retval;
759 EXCEPTION
760 WHEN OTHERS THEN
761 RETURN lx_retval;
762 END get_strm_typ_allocs;
763
764 END OKL_CASH_APPLN_RULE_PVT;