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