[Home] [Help]
PACKAGE BODY: APPS.OKL_RULE_APIS_PVT
Source
1 Package Body OKL_RULE_APIS_PVT As
2 /* $Header: OKLRRAPB.pls 120.5 2005/10/30 03:41:19 appldev noship $ */
3 --Start of Comments
4 --Procedure Name : Get_Contract_Rgs
5 --Description : Get Contract Rule Groups for a chr_id, cle_id
6 -- if chr_id is given gets data for header
7 -- if only cle_id or cle_id and chr_id(dnz_chr_id) are given
8 -- fetches data for line
9 --End of comments
10 --GLOBAL MESSAGES
11 G_DFF_FETCH_FAILED CONSTANT Varchar2(200) := 'OKL_LLA_DFF_FETCH';
12 G_DFF_TABLE_QUERY_FAILED CONSTANT Varchar2(200) := 'OKL_LLA_DFF_TABLE_QUERY';
13 G_DFF_VSET_QUERY_FAILED CONSTANT Varchar2(200) := 'OKL_LLA_DFF_VSET_QUERY';
14 G_JTF_OBJECT_QUERY_FAILED CONSTANT Varchar2(200) := 'OKL_LLA_JTF_OBJ_QUERY';
15 G_JTF_OBJECT_TOKEN CONSTANT Varchar2(200) := 'JTOT_OBJECT_CODE';
16 G_APPLICATION_COL_TOKEN CONSTANT Varchar2(200) := 'APPLICATION_COLUMN';
17 G_RULE_CODE_TOKEN CONSTANT Varchar2(200) := 'RULE_CODE';
18 Procedure Get_Contract_Rgs(p_api_version IN NUMBER,
19 p_init_msg_list IN VARCHAR2,
20 p_chr_id IN NUMBER,
21 p_cle_id IN NUMBER,
22 p_rgd_code IN VARCHAR2,
23 x_return_status OUT NOCOPY VARCHAR2,
24 x_msg_count OUT NOCOPY NUMBER,
25 x_msg_data OUT NOCOPY VARCHAR2,
26 x_rgpv_tbl OUT NOCOPY rgpv_tbl_type,
27 x_rg_count OUT NOCOPY NUMBER) is
28 l_No_RG_Found BOOLEAN default True;
29 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
30 l_api_name CONSTANT VARCHAR2(30) := 'GET_CONTRACT_RGS';
31 l_api_version CONSTANT NUMBER := 1.0;
32 ---------------------------------------------------------------------------
33 -- FUNCTION get_rec for: OKC_RULE_GROUPS_V
34 ---------------------------------------------------------------------------
35 FUNCTION get_rgpv_tab (
36 p_chr_id IN NUMBER,
37 p_cle_id IN NUMBER,
38 p_rgd_code IN VARCHAR2,
39 x_rg_count OUT NOCOPY NUMBER
40 ) RETURN rgpv_tbl_type IS
41 --BUG# 3562881:
42 CURSOR okc_chr_rgpv_csr (p_chr_id IN NUMBER,
43 p_rgd_code IN VARCHAR2) IS
44 SELECT
45 rgpv.ID,
46 rgpv.OBJECT_VERSION_NUMBER,
47 rgpv.SFWT_FLAG,
48 rgpv.RGD_CODE,
49 rgpv.SAT_CODE,
50 rgpv.RGP_TYPE,
51 rgpv.CLE_ID,
52 rgpv.CHR_ID,
53 rgpv.DNZ_CHR_ID,
54 rgpv.PARENT_RGP_ID,
55 rgpv.COMMENTS,
56 rgpv.ATTRIBUTE_CATEGORY,
57 rgpv.ATTRIBUTE1,
58 rgpv.ATTRIBUTE2,
59 rgpv.ATTRIBUTE3,
60 rgpv.ATTRIBUTE4,
61 rgpv.ATTRIBUTE5,
62 rgpv.ATTRIBUTE6,
63 rgpv.ATTRIBUTE7,
64 rgpv.ATTRIBUTE8,
65 rgpv.ATTRIBUTE9,
66 rgpv.ATTRIBUTE10,
67 rgpv.ATTRIBUTE11,
68 rgpv.ATTRIBUTE12,
69 rgpv.ATTRIBUTE13,
70 rgpv.ATTRIBUTE14,
71 rgpv.ATTRIBUTE15,
72 rgpv.CREATED_BY,
73 rgpv.CREATION_DATE,
74 rgpv.LAST_UPDATED_BY,
75 rgpv.LAST_UPDATE_DATE,
76 rgpv.LAST_UPDATE_LOGIN
77 FROM Okc_Rule_Groups_V rgpv
78 WHERE rgpv.chr_id = p_chr_id
79 AND rgpv.dnz_chr_id = p_chr_id
80 AND rgpv.cle_id is NULL
81 AND rgpv.RGD_CODE = decode(p_rgd_code,null,rgpv.RGD_CODE,p_rgd_code);
82
83 CURSOR okc_cle_rgpv_csr (p_chr_id IN NUMBER,
84 p_cle_id IN NUMBER,
85 p_rgd_code IN VARCHAR2) IS
86 SELECT
87 rgpv.ID,
88 rgpv.OBJECT_VERSION_NUMBER,
89 rgpv.SFWT_FLAG,
90 rgpv.RGD_CODE,
91 rgpv.SAT_CODE,
92 rgpv.RGP_TYPE,
93 rgpv.CLE_ID,
94 rgpv.CHR_ID,
95 rgpv.DNZ_CHR_ID,
96 rgpv.PARENT_RGP_ID,
97 rgpv.COMMENTS,
98 rgpv.ATTRIBUTE_CATEGORY,
99 rgpv.ATTRIBUTE1,
100 rgpv.ATTRIBUTE2,
101 rgpv.ATTRIBUTE3,
102 rgpv.ATTRIBUTE4,
103 rgpv.ATTRIBUTE5,
104 rgpv.ATTRIBUTE6,
105 rgpv.ATTRIBUTE7,
106 rgpv.ATTRIBUTE8,
107 rgpv.ATTRIBUTE9,
108 rgpv.ATTRIBUTE10,
109 rgpv.ATTRIBUTE11,
110 rgpv.ATTRIBUTE12,
111 rgpv.ATTRIBUTE13,
112 rgpv.ATTRIBUTE14,
113 rgpv.ATTRIBUTE15,
114 rgpv.CREATED_BY,
115 rgpv.CREATION_DATE,
116 rgpv.LAST_UPDATED_BY,
117 rgpv.LAST_UPDATE_DATE,
118 rgpv.LAST_UPDATE_LOGIN
119 FROM Okc_Rule_Groups_V rgpv
120 WHERE rgpv.cle_id = p_cle_id
121 AND rgpv.dnz_chr_id = p_chr_id
122 AND rgpv.chr_id is NULL
123 AND rgpv.RGD_CODE = decode(p_rgd_code,null,rgpv.RGD_CODE,p_rgd_code);
124
125 --CURSOR to fetch contract header id
126 CURSOR l_cle_csr (p_cle_id IN NUMBER) is
127 select cleb.dnz_chr_id
128 from okc_k_lines_b cleb
129 where cleb.id = p_cle_id;
130 --BUG# 3562881 (END)
131
132
133 l_rgpv_rec rgpv_rec_type;
134 l_rgpv_tab rgpv_tbl_type;
135 i NUMBER ;
136 l_chr_id NUMBER;
137 l_cle_id NUMBER;
138 l_dnz_chr_id NUMBER;
139
140 BEGIN
141 i := 0;
142 If p_chr_id is null and p_cle_id is not null Then
143
144 --BUG# 3562881:
145 l_dnz_chr_id := null;
146 --get the contract header id
147 open l_cle_csr (p_cle_id => p_cle_id);
148 fetch l_cle_csr into l_dnz_chr_id;
149 If l_cle_csr%NOTFOUND then
150 NULL; --will not raise error as this api was not raising error prior to this fix
151 End If;
152 close l_cle_csr;
153 l_cle_id := p_cle_id;
154
155 If l_dnz_chr_id is NOT NULL then
156 -- Get current database values
157 OPEN okc_cle_rgpv_csr (p_chr_id => l_dnz_chr_id,
158 p_cle_id => l_cle_id,
159 p_rgd_code => p_rgd_code);
160 Loop
161 FETCH okc_cle_rgpv_csr INTO
162 l_rgpv_rec.ID,
163 l_rgpv_rec.OBJECT_VERSION_NUMBER,
164 l_rgpv_rec.SFWT_FLAG,
165 l_rgpv_rec.RGD_CODE,
166 l_rgpv_rec.SAT_CODE,
167 l_rgpv_rec.RGP_TYPE,
168 l_rgpv_rec.CLE_ID,
169 l_rgpv_rec.CHR_ID,
170 l_rgpv_rec.DNZ_CHR_ID,
171 l_rgpv_rec.PARENT_RGP_ID,
172 l_rgpv_rec.COMMENTS,
173 l_rgpv_rec.ATTRIBUTE_CATEGORY,
174 l_rgpv_rec.ATTRIBUTE1,
175 l_rgpv_rec.ATTRIBUTE2,
176 l_rgpv_rec.ATTRIBUTE3,
177 l_rgpv_rec.ATTRIBUTE4,
178 l_rgpv_rec.ATTRIBUTE5,
179 l_rgpv_rec.ATTRIBUTE6,
180 l_rgpv_rec.ATTRIBUTE7,
181 l_rgpv_rec.ATTRIBUTE8,
182 l_rgpv_rec.ATTRIBUTE9,
183 l_rgpv_rec.ATTRIBUTE10,
184 l_rgpv_rec.ATTRIBUTE11,
185 l_rgpv_rec.ATTRIBUTE12,
186 l_rgpv_rec.ATTRIBUTE13,
187 l_rgpv_rec.ATTRIBUTE14,
188 l_rgpv_rec.ATTRIBUTE15,
189 l_rgpv_rec.CREATED_BY,
190 l_rgpv_rec.CREATION_DATE,
191 l_rgpv_rec.LAST_UPDATED_BY,
192 l_rgpv_rec.LAST_UPDATE_DATE,
193 l_rgpv_rec.LAST_UPDATE_LOGIN;
194 Exit When okc_cle_rgpv_csr%NotFound;
195 i := okc_cle_rgpv_csr%RowCount;
196 l_rgpv_tab(i) := l_rgpv_rec;
197 END Loop;
198 CLOSE okc_cle_rgpv_csr;
199 End If;
200
201 Elsif p_chr_id is null and p_cle_id is null Then
202 --BUG : 3562881
203 -- error : blind query not allowed
204 NULL; --not raising error here as this API does not raise error
205 Elsif p_chr_id is not null and p_cle_id is null Then
206 --BUG# 3562881
207 l_chr_id := p_chr_id;
208 -- Get current database values
209 OPEN okc_chr_rgpv_csr (p_chr_id => l_chr_id,
210 p_rgd_code => p_rgd_code);
211 Loop
212 FETCH okc_chr_rgpv_csr INTO
213 l_rgpv_rec.ID,
214 l_rgpv_rec.OBJECT_VERSION_NUMBER,
215 l_rgpv_rec.SFWT_FLAG,
216 l_rgpv_rec.RGD_CODE,
217 l_rgpv_rec.SAT_CODE,
218 l_rgpv_rec.RGP_TYPE,
219 l_rgpv_rec.CLE_ID,
220 l_rgpv_rec.CHR_ID,
221 l_rgpv_rec.DNZ_CHR_ID,
222 l_rgpv_rec.PARENT_RGP_ID,
223 l_rgpv_rec.COMMENTS,
224 l_rgpv_rec.ATTRIBUTE_CATEGORY,
225 l_rgpv_rec.ATTRIBUTE1,
226 l_rgpv_rec.ATTRIBUTE2,
227 l_rgpv_rec.ATTRIBUTE3,
228 l_rgpv_rec.ATTRIBUTE4,
229 l_rgpv_rec.ATTRIBUTE5,
230 l_rgpv_rec.ATTRIBUTE6,
231 l_rgpv_rec.ATTRIBUTE7,
232 l_rgpv_rec.ATTRIBUTE8,
233 l_rgpv_rec.ATTRIBUTE9,
234 l_rgpv_rec.ATTRIBUTE10,
235 l_rgpv_rec.ATTRIBUTE11,
236 l_rgpv_rec.ATTRIBUTE12,
237 l_rgpv_rec.ATTRIBUTE13,
238 l_rgpv_rec.ATTRIBUTE14,
239 l_rgpv_rec.ATTRIBUTE15,
240 l_rgpv_rec.CREATED_BY,
241 l_rgpv_rec.CREATION_DATE,
242 l_rgpv_rec.LAST_UPDATED_BY,
243 l_rgpv_rec.LAST_UPDATE_DATE,
244 l_rgpv_rec.LAST_UPDATE_LOGIN;
245 Exit When okc_chr_rgpv_csr%NotFound;
246 i := okc_chr_rgpv_csr%RowCount;
247 l_rgpv_tab(i) := l_rgpv_rec;
248 END Loop;
249 CLOSE okc_chr_rgpv_csr;
250
251 Elsif p_chr_id is not null and p_cle_id is not null Then
252 --BUG# 3562881
253 l_cle_id := p_cle_id;
254 l_dnz_chr_id := p_chr_id;
255 -- Get current database values
256 OPEN okc_cle_rgpv_csr (p_chr_id => l_dnz_chr_id,
257 p_cle_id => l_cle_id,
258 p_rgd_code => p_rgd_code);
259 Loop
260 FETCH okc_cle_rgpv_csr INTO
261 l_rgpv_rec.ID,
262 l_rgpv_rec.OBJECT_VERSION_NUMBER,
263 l_rgpv_rec.SFWT_FLAG,
264 l_rgpv_rec.RGD_CODE,
265 l_rgpv_rec.SAT_CODE,
266 l_rgpv_rec.RGP_TYPE,
267 l_rgpv_rec.CLE_ID,
268 l_rgpv_rec.CHR_ID,
269 l_rgpv_rec.DNZ_CHR_ID,
270 l_rgpv_rec.PARENT_RGP_ID,
271 l_rgpv_rec.COMMENTS,
272 l_rgpv_rec.ATTRIBUTE_CATEGORY,
273 l_rgpv_rec.ATTRIBUTE1,
274 l_rgpv_rec.ATTRIBUTE2,
275 l_rgpv_rec.ATTRIBUTE3,
276 l_rgpv_rec.ATTRIBUTE4,
277 l_rgpv_rec.ATTRIBUTE5,
278 l_rgpv_rec.ATTRIBUTE6,
279 l_rgpv_rec.ATTRIBUTE7,
280 l_rgpv_rec.ATTRIBUTE8,
281 l_rgpv_rec.ATTRIBUTE9,
282 l_rgpv_rec.ATTRIBUTE10,
283 l_rgpv_rec.ATTRIBUTE11,
284 l_rgpv_rec.ATTRIBUTE12,
285 l_rgpv_rec.ATTRIBUTE13,
286 l_rgpv_rec.ATTRIBUTE14,
287 l_rgpv_rec.ATTRIBUTE15,
288 l_rgpv_rec.CREATED_BY,
289 l_rgpv_rec.CREATION_DATE,
290 l_rgpv_rec.LAST_UPDATED_BY,
291 l_rgpv_rec.LAST_UPDATE_DATE,
292 l_rgpv_rec.LAST_UPDATE_LOGIN;
293 Exit When okc_cle_rgpv_csr%NotFound;
294 i := okc_cle_rgpv_csr%RowCount;
295 l_rgpv_tab(i) := l_rgpv_rec;
296 END Loop;
297 CLOSE okc_cle_rgpv_csr;
298 End If;
299
300 x_rg_count := i;
301 RETURN(l_rgpv_tab);
302 END get_rgpv_tab;
303
304 begin
305 --BUG# 3562881:
306 x_return_status := OKL_API.G_RET_STS_SUCCESS;
307 --Call OKL_API.START_ACTIVITY
308 l_return_status := OKL_API.START_ACTIVITY( substr(l_api_name,1,26),
309 G_PKG_NAME,
310 p_init_msg_list,
311 l_api_version,
312 p_api_version,
313 '_PVT',
314 x_return_status);
315
316 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
317 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
318 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
319 RAISE OKL_API.G_EXCEPTION_ERROR;
320 END IF;
321
322 x_rgpv_tbl := get_rgpv_tab(p_chr_id => p_chr_id,
323 p_cle_id => p_cle_id,
324 p_rgd_code => p_rgd_code,
325 x_rg_count => x_rg_count);
326 --Call End Activity
327 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
328 x_msg_data => x_msg_data);
329 EXCEPTION
330 when OKL_API.G_EXCEPTION_ERROR then
331 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
332 p_api_name => l_api_name,
333 p_pkg_name => g_pkg_name,
334 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
335 x_msg_count => x_msg_count,
336 x_msg_data => x_msg_data,
337 p_api_type => g_api_type);
338
339 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
340 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
341 p_api_name => l_api_name,
342 p_pkg_name => g_pkg_name,
343 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
344 x_msg_count => x_msg_count,
345 x_msg_data => x_msg_data,
346 p_api_type => g_api_type);
347
348 when OTHERS then
349 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
350 p_api_name => l_api_name,
351 p_pkg_name => g_pkg_name,
352 p_exc_name => 'OTHERS',
353 x_msg_count => x_msg_count,
354 x_msg_data => x_msg_data,
355 p_api_type => g_api_type);
356
357 end Get_Contract_Rgs;
358 --Start of Comments
359 --Procedure : Get Contract Rules
360 --Description : Gets all or specific rules for a rule group id
361 -- End of Comments
362 Procedure Get_Contract_Rules(p_api_version IN NUMBER,
363 p_init_msg_list IN VARCHAR2,
364 p_rgpv_rec IN rgpv_rec_type,
365 p_rdf_code IN VARCHAR2,
366 x_return_status OUT NOCOPY VARCHAR2,
367 x_msg_count OUT NOCOPY NUMBER,
368 x_msg_data OUT NOCOPY VARCHAR2,
369 x_rulv_tbl OUT NOCOPY rulv_tbl_type,
370 x_rule_count OUT NOCOPY NUMBER ) is
371
372
373 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
374 l_api_name CONSTANT VARCHAR2(30) := 'GET_CONTRACT_RULES';
375 l_api_version CONSTANT NUMBER := 1.0;
376 ---------------------------------------------------------------------------
377 -- FUNCTION get_rec for: OKC_RULES_V
378 ---------------------------------------------------------------------------
379 FUNCTION get_rulv_tab (
380 p_rgpv_rec IN rgpv_rec_type,
381 p_rdf_code IN VARCHAR2,
382 x_Rule_Count OUT NOCOPY NUMBER
383 ) RETURN rulv_tbl_type IS
384 CURSOR okc_rulv_csr (p_rgp_id IN NUMBER,
385 p_rdf_code IN VARCHAR2) IS
386 SELECT
387 ID,
388 OBJECT_VERSION_NUMBER,
389 SFWT_FLAG,
390 OBJECT1_ID1,
391 OBJECT2_ID1,
392 OBJECT3_ID1,
393 OBJECT1_ID2,
394 OBJECT2_ID2,
395 OBJECT3_ID2,
396 JTOT_OBJECT1_CODE,
397 JTOT_OBJECT2_CODE,
398 JTOT_OBJECT3_CODE,
399 DNZ_CHR_ID,
400 RGP_ID,
401 PRIORITY,
402 STD_TEMPLATE_YN,
403 COMMENTS,
404 WARN_YN,
405 ATTRIBUTE_CATEGORY,
406 ATTRIBUTE1,
407 ATTRIBUTE2,
408 ATTRIBUTE3,
409 ATTRIBUTE4,
410 ATTRIBUTE5,
411 ATTRIBUTE6,
412 ATTRIBUTE7,
413 ATTRIBUTE8,
414 ATTRIBUTE9,
415 ATTRIBUTE10,
416 ATTRIBUTE11,
417 ATTRIBUTE12,
418 ATTRIBUTE13,
419 ATTRIBUTE14,
420 ATTRIBUTE15,
421 CREATED_BY,
422 CREATION_DATE,
423 LAST_UPDATED_BY,
424 LAST_UPDATE_DATE,
425 LAST_UPDATE_LOGIN,
426 --TEXT,
427 RULE_INFORMATION_CATEGORY,
428 RULE_INFORMATION1,
429 RULE_INFORMATION2,
430 RULE_INFORMATION3,
431 RULE_INFORMATION4,
432 RULE_INFORMATION5,
433 RULE_INFORMATION6,
434 RULE_INFORMATION7,
435 RULE_INFORMATION8,
436 RULE_INFORMATION9,
437 RULE_INFORMATION10,
438 RULE_INFORMATION11,
439 RULE_INFORMATION12,
440 RULE_INFORMATION13,
441 RULE_INFORMATION14,
442 RULE_INFORMATION15,
443 TEMPLATE_YN,
444 ans_set_jtot_object_code,
445 ans_set_jtot_object_id1,
446 ans_set_jtot_object_id2,
447 DISPLAY_SEQUENCE
448 FROM Okc_Rules_V
449 WHERE okc_rules_v.rgp_id = p_rgp_id
450 AND RULE_INFORMATION_CATEGORY = decode(p_rdf_code,null,RULE_INFORMATION_CATEGORY,p_rdf_code);
451 l_rulv_rec rulv_rec_type;
452 l_rulv_tab rulv_tbl_type;
453 i NUMBER default 0;
454 BEGIN
455
456 -- Get current database values
457 OPEN okc_rulv_csr (p_rgpv_rec.id,p_rdf_code);
458 LOOP
459 FETCH okc_rulv_csr INTO
460 l_rulv_rec.ID,
461 l_rulv_rec.OBJECT_VERSION_NUMBER,
462 l_rulv_rec.SFWT_FLAG,
463 l_rulv_rec.OBJECT1_ID1,
464 l_rulv_rec.OBJECT2_ID1,
465 l_rulv_rec.OBJECT3_ID1,
466 l_rulv_rec.OBJECT1_ID2,
467 l_rulv_rec.OBJECT2_ID2,
468 l_rulv_rec.OBJECT3_ID2,
469 l_rulv_rec.JTOT_OBJECT1_CODE,
470 l_rulv_rec.JTOT_OBJECT2_CODE,
471 l_rulv_rec.JTOT_OBJECT3_CODE,
472 l_rulv_rec.DNZ_CHR_ID,
473 l_rulv_rec.RGP_ID,
474 l_rulv_rec.PRIORITY,
475 l_rulv_rec.STD_TEMPLATE_YN,
476 l_rulv_rec.COMMENTS,
477 l_rulv_rec.WARN_YN,
478 l_rulv_rec.ATTRIBUTE_CATEGORY,
479 l_rulv_rec.ATTRIBUTE1,
480 l_rulv_rec.ATTRIBUTE2,
481 l_rulv_rec.ATTRIBUTE3,
482 l_rulv_rec.ATTRIBUTE4,
483 l_rulv_rec.ATTRIBUTE5,
484 l_rulv_rec.ATTRIBUTE6,
485 l_rulv_rec.ATTRIBUTE7,
486 l_rulv_rec.ATTRIBUTE8,
487 l_rulv_rec.ATTRIBUTE9,
488 l_rulv_rec.ATTRIBUTE10,
489 l_rulv_rec.ATTRIBUTE11,
490 l_rulv_rec.ATTRIBUTE12,
491 l_rulv_rec.ATTRIBUTE13,
492 l_rulv_rec.ATTRIBUTE14,
493 l_rulv_rec.ATTRIBUTE15,
494 l_rulv_rec.CREATED_BY,
495 l_rulv_rec.CREATION_DATE,
496 l_rulv_rec.LAST_UPDATED_BY,
497 l_rulv_rec.LAST_UPDATE_DATE,
498 l_rulv_rec.LAST_UPDATE_LOGIN,
499 --l_rulv_rec.TEXT,
500 l_rulv_rec.RULE_INFORMATION_CATEGORY,
501 l_rulv_rec.RULE_INFORMATION1,
502 l_rulv_rec.RULE_INFORMATION2,
503 l_rulv_rec.RULE_INFORMATION3,
504 l_rulv_rec.RULE_INFORMATION4,
505 l_rulv_rec.RULE_INFORMATION5,
506 l_rulv_rec.RULE_INFORMATION6,
507 l_rulv_rec.RULE_INFORMATION7,
508 l_rulv_rec.RULE_INFORMATION8,
509 l_rulv_rec.RULE_INFORMATION9,
510 l_rulv_rec.RULE_INFORMATION10,
511 l_rulv_rec.RULE_INFORMATION11,
512 l_rulv_rec.RULE_INFORMATION12,
513 l_rulv_rec.RULE_INFORMATION13,
514 l_rulv_rec.RULE_INFORMATION14,
515 l_rulv_rec.RULE_INFORMATION15,
516 l_rulv_rec.TEMPLATE_YN,
517 l_rulv_rec.ans_set_jtot_object_code,
518 l_rulv_rec.ans_set_jtot_object_id1,
519 l_rulv_rec.ans_set_jtot_object_id2,
520 l_rulv_rec.DISPLAY_SEQUENCE ;
521 EXIT When okc_rulv_csr%NOTFOUND;
522 i := okc_rulv_csr%RowCount;
523 l_rulv_tab(i) := l_rulv_rec;
524 END LOOP;
525 CLOSE okc_rulv_csr;
526 x_rule_count := i;
527 RETURN(l_rulv_tab);
528 END get_rulv_tab;
529 BEGIN
530 --Call OKL_API.START_ACTIVITY
531 l_return_status := OKL_API.START_ACTIVITY( substr(l_api_name,1,26),
532 G_PKG_NAME,
533 p_init_msg_list,
534 l_api_version,
535 p_api_version,
536 '_PVT',
537 x_return_status);
538
539 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
540 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
541 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
542 RAISE OKL_API.G_EXCEPTION_ERROR;
543 END IF;
544
545 x_rulv_tbl := get_rulv_tab(p_rgpv_rec => p_rgpv_rec,
546 p_rdf_code => p_rdf_code,
547 x_Rule_Count => x_rule_Count);
548 --Call End Activity
549 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
550 x_msg_data => x_msg_data);
551 EXCEPTION
552 when OKL_API.G_EXCEPTION_ERROR then
553 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
554 p_api_name => l_api_name,
555 p_pkg_name => g_pkg_name,
556 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
557 x_msg_count => x_msg_count,
558 x_msg_data => x_msg_data,
559 p_api_type => g_api_type);
560
561 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
562 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
563 p_api_name => l_api_name,
564 p_pkg_name => g_pkg_name,
565 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
566 x_msg_count => x_msg_count,
567 x_msg_data => x_msg_data,
568 p_api_type => g_api_type);
569
570 when OTHERS then
571 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
572 p_api_name => l_api_name,
573 p_pkg_name => g_pkg_name,
574 p_exc_name => 'OTHERS',
575 x_msg_count => x_msg_count,
576 x_msg_data => x_msg_data,
577 p_api_type => g_api_type);
578
579 END Get_Contract_Rules;
580 -- Start of comments
581 --Procedure : Get Rule Information
582 --Description : Fetches the display value (name) and select clause of the
583 -- rule information column in a rule if stored value(p_rule_info)
584 -- is provided else just returns the select clause
585 -- IN p_rdf_code : rule_code
586 -- p_appl_col_name : segment column name ('RULE_INFORMATION1',...)
587 -- p_rule_info : segment column value default Null
588 -- End of Comments
589 Procedure Get_rule_Information (p_api_version IN NUMBER,
590 p_init_msg_list IN VARCHAR2,
591 p_rdf_code IN VARCHAR2,
592 p_appl_col_name IN VARCHAR2,
593 p_rule_info IN VARCHAR2,
594 x_return_status OUT NOCOPY VARCHAR2,
595 x_msg_count OUT NOCOPY NUMBER,
596 x_msg_data OUT NOCOPY VARCHAR2,
597 x_name OUT NOCOPY VARCHAR2,
598 x_select OUT NOCOPY VARCHAR2) is
599
600 Cursor rule_dff_cur is
601 -- select dflex.descriptive_flex_context_code
602 -- , dflex.flex_value_set_id
603 -- from fnd_descr_flex_col_usage_vl dflex
604 -- where dflex.application_id=510
605 -- and dflex.descriptive_flexfield_name='OKC Rule Developer DF'
606 -- and dflex.descriptive_flex_context_code = p_rdf_code
607 -- and dflex.application_column_name = p_appl_col_name
608 --code added for rule striping (remove top union when finalized)
609 --union
610 select dflex.descriptive_flex_context_code
611 , dflex.flex_value_set_id
612 from fnd_descr_flex_col_usage_vl dflex,
613 okc_rule_defs_v rdefv
614 where dflex.application_id = rdefv.application_id
615 and dflex.descriptive_flexfield_name = rdefv.descriptive_flexfield_name
616 and dflex.descriptive_flex_context_code = rdefv.rule_code
617 and dflex.application_column_name = p_appl_col_name
618 and rdefv.rule_code = p_rdf_code
619 order by dflex.descriptive_flex_context_code;
620 -- order by 1;
621 rule_dff_rec rule_dff_cur%rowtype;
622 l_object_code varchar2(30);
623 l_flex_value_set_id Number;
624
625 Cursor flex_value_set_cur(p_flex_value_set_id IN Number) is
626 Select validation_type
627 from fnd_flex_value_sets
628 where flex_value_set_id = p_flex_value_set_id;
629 flex_value_set_rec flex_value_set_cur%RowType;
630
631 Cursor flex_query_t_cur(p_flex_value_set_id NUMBER) is
632 SELECT fvt.id_column_name,
633 fvt.value_column_name,
634 fvt.meaning_column_name,
635 fvt.application_table_name,
636 fvt.additional_where_clause,
637 fvt.enabled_column_name,
638 fvt.start_date_column_name,
639 fvt.end_date_column_name
640 FROM fnd_flex_validation_tables fvt
641 WHERE fvt.flex_value_set_id = p_flex_value_set_id;
642 flex_query_t_rec flex_query_t_cur%rowtype;
643
644 type flex_val_curs_type is REF CURSOR;
645 flex_val_curs flex_val_curs_type;
646 type flex_val_rec_type is record (val Varchar2(100) := OKL_API.G_MISS_CHAR,
647 meaning Varchar2(2000) := OKL_API.G_MISS_CHAR
648 );
649 flex_val_rec flex_val_rec_type;
650 l_query_string varchar2(2000) default Null;
651 l_success number;
652 l_mapping_code Varchar2(10) default null;
653 l_inc_user_where_clause varchar2(1) default 'N';
654 l_user_where_clause varchar2(1000) default null;
655 l_select_clause varchar2(2000) default null;
656 l_from_clause varchar2(2000) default null;
657 l_where_clause varchar2(2000) default null;
658 l_add_where_clause varchar2(2000) default null;
659 l_order_by_clause varchar2(2000) default null;
660
661 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
662 l_api_name CONSTANT VARCHAR2(30) := 'GET_CONTRACT_RULES';
663 l_api_version CONSTANT NUMBER := 1.0;
664 Begin
665 ----
666 --Call OKL_API.START_ACTIVITY
667 l_return_status := OKL_API.START_ACTIVITY( substr(l_api_name,1,26),
668 G_PKG_NAME,
669 p_init_msg_list,
670 l_api_version,
671 p_api_version,
672 '_PVT',
673 x_return_status);
674 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
675 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
676 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
677 RAISE OKL_API.G_EXCEPTION_ERROR;
678 END IF;
679 --Get metadata from fnd_flex_column_usages
680 Open rule_dff_cur;
681 Fetch rule_dff_cur into rule_dff_rec;
682 If rule_dff_cur%NotFound Then
683 --dbms_output.put_line('failed in select from fnd_descr_flex_col_usages');
684 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
685 p_msg_name => G_DFF_FETCH_FAILED,
686 p_token1 => G_APPLICATION_COL_TOKEN,
687 p_token1_value => p_appl_col_name,
688 p_token2 => G_RULE_CODE_TOKEN,
689 p_token2_value => p_rdf_code
690 );
691 RAISE OKL_API.G_EXCEPTION_ERROR;
692 Elsif rule_dff_rec.flex_value_set_id is null Then
693 x_name := p_rule_info; -- no validation
694 Elsif rule_dff_rec.flex_value_set_id is not null Then
695 Open flex_value_set_cur(rule_dff_rec.flex_value_set_id);
696 Fetch flex_value_set_cur into flex_value_set_rec;
697 If flex_value_set_rec.validation_type = 'N' Then --No Validation
698 x_name := p_rule_info;
699 Elsif flex_value_set_rec.validation_type in ('I') Then --Independent
700 If p_rule_info is Not Null Then
701 l_inc_user_where_clause := 'Y';
702 l_user_where_clause := ' flex_value = '||''''||p_rule_info||'''';
703 End If;
704 fnd_flex_val_api.get_independent_vset_select(p_value_set_id => rule_dff_rec.flex_value_set_id,
705 p_inc_id_col => 'N',
706 p_inc_user_where_clause => l_inc_user_where_clause,
707 p_user_where_clause => l_user_where_clause,
708 x_select => l_query_string,
709 x_mapping_code => l_mapping_code,
710 x_success => l_success);
711 Elsif flex_value_set_rec.validation_type in ('D') Then --Dependent
712 If p_rule_info is Not Null Then
713 l_inc_user_where_clause := 'Y';
714 l_user_where_clause := ' flex_value = '||''''||p_rule_info||'''';
715 End If;
716 fnd_flex_val_api.get_dependent_vset_select( p_value_set_id => rule_dff_rec.flex_value_set_id,
717 p_inc_id_col => 'N',
718 p_inc_user_where_clause => l_inc_user_where_clause,
719 p_user_where_clause => l_user_where_clause,
720 x_select => l_query_string,
721 x_mapping_code => l_mapping_code,
722 x_success => l_success);
723 Elsif flex_value_set_rec.validation_type in ('F') Then --Table
724 Open flex_query_t_cur(rule_dff_rec.flex_value_set_id);
725 Fetch flex_query_t_cur into flex_query_t_rec;
726 If flex_query_t_cur%NotFound Then
727 --dbms_output.put_line('DFF type : failed to fetch table validated query');
728 --Null;--raise appropriate exception
729 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
730 p_msg_name => G_DFF_TABLE_QUERY_FAILED,
731 p_token1 => G_APPLICATION_COL_TOKEN,
732 p_token1_value => p_appl_col_name,
733 p_token2 => G_RULE_CODE_TOKEN,
734 p_token2_value => p_rdf_code);
735 Else
736 --For Rules always use id col
737 If flex_query_t_rec.id_column_name is null Then
738 l_select_clause := ' SELECT '||l_select_clause||' '||flex_query_t_rec.value_column_name||' , ';
739 Else
740 l_select_clause := ' SELECT '||l_select_clause||' '||flex_query_t_rec.id_column_name||' , ';
741 End If;
742 --For Rules always use id col and value column
743 l_select_clause := l_select_clause||' '||flex_query_t_rec.value_column_name;
744 l_from_clause := ' FROM '||l_from_clause||flex_query_t_rec.application_table_name||' ';
745 l_where_clause := ' WHERE '||l_where_clause||' '||flex_query_t_rec.enabled_column_name||' = ';
746 l_where_clause := l_where_clause||' '||''''||'Y'||'''';
747 l_where_clause := l_where_clause||' AND ';
748 l_where_clause := l_where_clause||' nvl('||flex_query_t_rec.start_date_column_name||',sysdate) <= sysdate';
749 l_where_clause := l_where_clause||' AND ';
750 l_where_clause := l_where_clause||' nvl('||flex_query_t_rec.end_date_column_name||',sysdate+1) > sysdate';
751 --add user where clause
752 If p_rule_info is not null then
753 l_where_clause := l_where_clause||' AND '||flex_query_t_rec.id_column_name||' = '||''''||p_rule_info||'''';
754 End If;
755
756 If flex_query_t_rec.additional_where_clause is null Then
757 Null;
758 Else
759 flex_query_t_rec.additional_where_clause:= REPLACE(upper(flex_query_t_rec.additional_where_clause),'WHERE',' ');
760 --dbms_output.put_line('additional where :'||flex_query_t_rec.additional_where_clause);
761 l_add_where_clause := null;
762 select l_where_clause||' '||decode(l_where_clause,null,' ',decode(instr(ltrim(flex_query_t_rec.additional_where_clause,' '),'ORDER BY'),1,' ',' AND '))||flex_query_t_rec.additional_where_clause
763 into l_add_where_clause from dual;
764 l_where_clause := l_add_where_clause;
765 End If;
766 l_query_string := rtrim(ltrim(l_select_clause,' '),' ')||' '||
767 rtrim(ltrim(l_from_clause,' '),' ')||' '||
768 rtrim(ltrim(l_where_clause,' '),' ')||' '||
769 rtrim(ltrim(l_order_by_clause,' '),' ');
770 End If;
771 Close flex_query_t_cur;
772 End If;
773 Close flex_value_set_cur;
774 End If;
775 If l_query_string is not null and
776 p_rule_info is not null then
777 --dbms_output.put_line(l_query_string);
778 Open flex_val_curs for l_query_string;
779 Fetch flex_val_curs into flex_val_rec;
780 If flex_val_curs%NotFound Then
781 --dbms_output.put_line('Flex Value not Found for column name "'||p_appl_col_name||'"');
782 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
783 p_msg_name => G_DFF_VSET_QUERY_FAILED,
784 p_token1 => G_APPLICATION_COL_TOKEN,
785 p_token1_value => p_appl_col_name,
786 p_token2 => G_RULE_CODE_TOKEN,
787 p_token2_value => p_rdf_code);
788 RAISE OKL_API.G_EXCEPTION_ERROR;
789 Else
790 x_name := flex_val_rec.meaning;
791 End If;
792 Close flex_val_curs;
793 End If;
794 x_select := l_query_string;
795 --Call End Activity
796 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
797 x_msg_data => x_msg_data);
798 EXCEPTION
799 when OKL_API.G_EXCEPTION_ERROR then
800 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
801 p_api_name => l_api_name,
802 p_pkg_name => g_pkg_name,
803 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
804 x_msg_count => x_msg_count,
805 x_msg_data => x_msg_data,
806 p_api_type => g_api_type);
807
808 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
809 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
810 p_api_name => l_api_name,
811 p_pkg_name => g_pkg_name,
812 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
813 x_msg_count => x_msg_count,
814 x_msg_data => x_msg_data,
815 p_api_type => g_api_type);
816
817 when OTHERS then
818 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
819 p_api_name => l_api_name,
820 p_pkg_name => g_pkg_name,
821 p_exc_name => 'OTHERS',
822 x_msg_count => x_msg_count,
823 x_msg_data => x_msg_data,
824 p_api_type => g_api_type);
825
826 End Get_Rule_Information;
827 -- Start of comments
828 --Procedure : Get_jtot_object
829 --Description : Fetches the display values (name,description) and additional
830 -- columns status, start_date, end_date, org_id, inv_org_id,
831 -- book_type_code, if present if id1 and id2 are given
832 -- Also returns the select clause associated with the jtf_object
833 -- End of Comments
834 Procedure Get_jtot_object(p_api_version IN NUMBER,
835 p_init_msg_list IN VARCHAR2,
836 p_object_code IN VARCHAR2,
837 p_id1 IN VARCHAR2,
838 p_id2 IN VARCHAR2,
839 x_return_status OUT NOCOPY VARCHAR2,
840 x_msg_count OUT NOCOPY NUMBER,
841 x_msg_data OUT NOCOPY VARCHAR2,
842 x_id1 OUT NOCOPY VARCHAR2,
843 x_id2 OUT NOCOPY VARCHAR2,
844 x_name OUT NOCOPY VARCHAR2,
845 x_description OUT NOCOPY VARCHAR2,
846 x_status OUT NOCOPY VARCHAR2,
847 x_start_date OUT NOCOPY DATE,
848 x_end_date OUT NOCOPY DATE,
849 x_org_id OUT NOCOPY NUMBER,
850 x_inv_org_id OUT NOCOPY NUMBER,
851 x_book_type_code OUT NOCOPY VARCHAR2,
852 x_select OUT NOCOPY VARCHAR2) is
853
854 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
855 l_api_name CONSTANT VARCHAR2(30) := 'GET_JTOT_OBJECT';
856 l_api_version CONSTANT NUMBER := 1.0;
857
858 l_select_clause Varchar2(2000) default Null;
859 l_from_clause Varchar2(2000) default Null;
860 l_where_clause Varchar2(2000) default Null;
861 l_order_by_clause Varchar2(2000) default Null;
862 l_query_string VARCHAR2(2000) default Null;
863 l_from_table VARCHAR2(200) default Null;
864
865 Cursor jtf_obj_curs is
866 Select job.from_table from_table,
867 ' FROM '||job.from_table from_clause,
868 decode(where_clause,null,null,' WHERE ')||job.where_clause where_clause,
869 decode(order_by_clause,null,null,' ORDER BY ')||job.order_by_clause order_by_clause
870 From jtf_objects_vl job
871 Where job.object_code = p_object_code
872 And nvl(job.start_date_active,sysdate) <= sysdate
873 And nvl(job.end_date_active,sysdate+1) > sysdate;
874
875 Cursor check_col_curs(p_table_name IN Varchar2,p_col_name IN Varchar2) is
876 Select 'Y'
877 From dba_tab_columns
878 Where table_name = p_table_name
879 And column_name = p_col_name
880 -----------------
881 --Bug# 3431854 :
882 -----------------
883 And owner = USER;
884
885 l_col_exists Varchar2(1) default 'N';
886 l_table_name Varchar2(30) default Null;
887 l_col_name Varchar2(30) default Null;
888
889 Type jtot_ref_curs_type is REF CURSOR;
890 jtot_ref_curs jtot_ref_curs_type;
891
892 Begin
893 --Bug# 3024752:
894 --If okc_context.get_okc_organization_id is null then
895 --okc_context.set_okc_org_context(204,204);
896 --End If;
897 --Call OKL_API.START_ACTIVITY
898 l_return_status := OKL_API.START_ACTIVITY( substr(l_api_name,1,26),
899 G_PKG_NAME,
900 p_init_msg_list,
901 l_api_version,
902 p_api_version,
903 '_PVT',
904 x_return_status);
905 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
906 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
907 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
908 RAISE OKL_API.G_EXCEPTION_ERROR;
909 END IF;
910
911 Open jtf_obj_curs;
912 Fetch jtf_obj_curs into l_from_table, l_from_clause, l_where_clause, l_order_by_clause;
913 If jtf_obj_curs%NotFound Then
914 --dbms_output.put_line('jtf object not found for object code "'||p_object_code||'"');
915 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
916 p_msg_name =>G_JTF_OBJECT_QUERY_FAILED,
917 p_token1 =>G_JTF_OBJECT_TOKEN ,
918 p_token1_value => p_object_code);
919 RAISE OKL_API.G_EXCEPTION_ERROR;
920 Else
921 -- Bug# 3838403 - Remove table alias from select clause to handle
922 -- objects not having table alias.
923 l_select_clause := 'SELECT ID1,ID2,NAME,DESCRIPTION';
924 --get okx view table name
925
926 -- Bug# 3838403 - Append space to l_from_table in instr call
927 -- to handle table names with no alias
928 l_table_name := upper(substr(l_from_table,1,instr(l_from_table||' ',' ',1)));
929
930 --Bug# 3431854 :
931 l_table_name := ltrim(rtrim(l_table_name,' '),' ');
932 --chek for presense of columns
933 -- as all columns may not exist in OKX View
934 for i in 1..6
935 loop
936 l_col_exists := 'N';
937 l_col_name := null;
938 If i = 1 Then
939 l_col_name := 'STATUS';
940 Elsif i = 2 Then
941 l_col_name := 'START_DATE_ACTIVE';
942 Elsif i = 3 Then
943 l_col_name := 'END_DATE_ACTIVE';
944 Elsif i = 4 Then
945 l_col_name := 'ORG_ID';
946 Elsif i = 5 Then
947 l_col_name := 'INV_ORG_ID';
948 Elsif i = 6 Then
949 l_col_name := 'BOOK_TYPE_CODE';
950 End If;
951 open check_col_curs(l_table_name,l_col_name);
952 Fetch check_col_curs into l_col_exists;
953 If check_col_curs%NotFound --l_col_exist is 'N' column does not exist
954 Then
955 If i in (1,2,3,6) Then
956 l_select_clause := l_select_clause||','||'null';
957 elsif i in (4,5) Then
958 l_select_clause := l_select_clause||','||'to_number(null)';
959 end if;
960 Else
961 l_select_clause := l_select_clause||','||l_col_name;
962 End If;
963 Close check_col_curs;
964 End Loop;
965 -- Add p_id1 and p_id2 to the where clause
966 If p_id1 is not null and p_id2 is not null Then
967
968 -- Bug# 3838403 - Remove table alias from where clause to handle
969 -- objects not having table alias.
970 If l_where_clause is null Then
971 l_where_clause := ' WHERE ID1 ='||''''||p_id1||''''||
972 ' AND ID2 ='||''''||p_id2||'''';
973 Else
974 l_where_clause := l_where_clause||
975 ' AND ID1 ='||''''||p_id1||''''||
976 ' AND ID2 ='||''''||p_id2||'''';
977 End If;
978 End If;
979 -- compose sql query for jtot object
980 l_query_string := ltrim(rtrim(l_select_clause,' '),' ')||' '||
981 ltrim(rtrim(l_from_clause,' '),' ')||' '||
982 ltrim(rtrim(l_where_clause,' '),' ')||' '||
983 ltrim(rtrim(l_order_by_clause,' '),' ');
984 --dbms_output.put_line(l_where_clause);
985 x_select := l_query_string;
986 --execute sql query to get values
987 If p_id1 is not null and p_id2 is not null Then
988 open jtot_ref_curs for l_query_string;
989 Fetch jtot_ref_curs into x_id1,
990 x_id2,
991 x_name,
992 x_description,
993 x_status,
994 x_start_date,
995 x_end_date,
996 x_org_id,
997 x_inv_org_id,
998 x_book_type_code;
999 If jtot_ref_curs%notfound Then
1000 --dbms_output.put_line('Not able to fetch ref cursor record using jtot query string');
1001 null;--handle appropriate exception here
1002 End If;
1003 Close jtot_ref_curs;
1004 End If;
1005 End If;
1006 Close jtf_obj_curs;
1007 --Call End Activity
1008 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
1009 x_msg_data => x_msg_data);
1010 EXCEPTION
1011 when OKL_API.G_EXCEPTION_ERROR then
1012 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1013 p_api_name => l_api_name,
1014 p_pkg_name => g_pkg_name,
1015 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1016 x_msg_count => x_msg_count,
1017 x_msg_data => x_msg_data,
1018 p_api_type => g_api_type);
1019
1020 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
1021 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1022 p_api_name => l_api_name,
1023 p_pkg_name => g_pkg_name,
1024 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1025 x_msg_count => x_msg_count,
1026 x_msg_data => x_msg_data,
1027 p_api_type => g_api_type);
1028
1029 when OTHERS then
1030 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1031 p_api_name => l_api_name,
1032 p_pkg_name => g_pkg_name,
1033 p_exc_name => 'OTHERS',
1034 x_msg_count => x_msg_count,
1035 x_msg_data => x_msg_data,
1036 p_api_type => g_api_type);
1037
1038 End Get_jtot_object;
1039
1040 --Start of Comments
1041 --Procedure : Get_Rule_disp_value
1042 --Description : Fetches the displayed values of rule segments
1043 --End of Comments
1044 Procedure Get_Rule_disp_value (p_api_version IN NUMBER,
1045 p_init_msg_list IN VARCHAR2,
1046 p_rulv_rec IN Rulv_rec_type,
1047 x_return_status OUT NOCOPY VARCHAR2,
1048 x_msg_count OUT NOCOPY NUMBER,
1049 x_msg_data OUT NOCOPY VARCHAR2,
1050 x_rulv_disp_rec OUT NOCOPY rulv_disp_rec_type) is
1051
1052 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1053 l_api_name CONSTANT VARCHAR2(30) := 'GET_RULE_DISP_VALUE';
1054 l_api_version CONSTANT NUMBER := 1.0;
1055
1056 l_rulv_disp_rec rulv_disp_rec_type;
1057 l_name Varchar2(500) default null;
1058 l_description Varchar2(2000) default null;
1059 l_status Varchar2(30) default null;
1060 l_start_date date default null;
1061 l_end_date date default null;
1062 l_org_id Number;
1063 l_inv_org_id Number;
1064 l_book_type_code Varchar2(30) default null;
1065 l_select Varchar2(2000) default null;
1066 l_id1 Varchar2(40) default Null;
1067 l_id2 Varchar2(200) default Null;
1068
1069 begin
1070 --Call OKL_API.START_ACTIVITY
1071 l_return_status := OKL_API.START_ACTIVITY( substr(l_api_name,1,26),
1072 G_PKG_NAME,
1073 p_init_msg_list,
1074 l_api_version,
1075 p_api_version,
1076 '_PVT',
1077 x_return_status);
1078 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1079 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1080 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1081 RAISE OKL_API.G_EXCEPTION_ERROR;
1082 END IF;
1083
1084 l_rulv_disp_rec.id := p_rulv_rec.id;
1085 l_rulv_disp_rec.rdf_code := p_rulv_rec.rule_information_category;
1086
1087 If p_rulv_rec.jtot_object1_code is not null then
1088 l_id1 := null;
1089 l_id2 := null;
1090 l_name := null;
1091 l_description := null;
1092 l_status := null;
1093 l_start_date := null;
1094 l_end_date := null;
1095 l_org_id := to_number(null);
1096 l_inv_org_id := to_number(null);
1097 l_book_type_code := null;
1098 l_select := null;
1099
1100 Get_jtot_object(p_api_version => p_api_version,
1101 p_init_msg_list => p_init_msg_list,
1102 p_object_code => p_rulv_rec.jtot_object1_code,
1103 p_id1 => p_rulv_rec.object1_id1,
1104 p_id2 => p_rulv_rec.object1_id2,
1105 x_return_status => x_return_status,
1106 x_msg_count => x_msg_count,
1107 x_msg_data => x_msg_data,
1108 x_id1 => l_id1,
1109 x_id2 => l_id2,
1110 x_name => l_name,
1111 x_description => l_description,
1112 x_status => l_status,
1113 x_start_date => l_start_date,
1114 x_end_date => l_end_date,
1115 x_org_id => l_org_id,
1116 x_inv_org_id => l_inv_org_id,
1117 x_book_type_code => l_book_type_code,
1118 x_select => l_select);
1119 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1120 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1121 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1122 RAISE OKL_API.G_EXCEPTION_ERROR;
1123 END IF;
1124
1125 l_rulv_disp_rec.obj1_name := l_name;
1126 l_rulv_disp_rec.obj1_descr := l_description;
1127 l_rulv_disp_rec.obj1_status := l_status;
1128 l_rulv_disp_rec.obj1_start_date := l_start_date;
1129 l_rulv_disp_rec.obj1_end_date := l_end_date;
1130 l_rulv_disp_rec.obj1_org_id := l_org_id;
1131 l_rulv_disp_rec.obj1_inv_org_id := l_inv_org_id;
1132 l_rulv_disp_rec.obj1_book_type_code := l_book_type_code;
1133 l_rulv_disp_rec.obj1_select := l_select;
1134 --dbms_output.put_line('Name '||l_name);
1135 End If;
1136
1137 If p_rulv_rec.jtot_object2_code is not null then
1138 l_id1 := null;
1139 l_id2 := null;
1140 l_name := null;
1141 l_description := null;
1142 l_status := null;
1143 l_start_date := null;
1144 l_end_date := null;
1145 l_org_id := to_number(null);
1146 l_inv_org_id := to_number(null);
1147 l_book_type_code := null;
1148 l_select := null;
1149
1150 Get_jtot_object(p_api_version => p_api_version,
1151 p_init_msg_list => p_init_msg_list,
1152 p_object_code => p_rulv_rec.jtot_object2_code,
1153 p_id1 => p_rulv_rec.object2_id1,
1154 p_id2 => p_rulv_rec.object2_id2,
1155 x_return_status => x_return_status,
1156 x_msg_count => x_msg_count,
1157 x_msg_data => x_msg_data,
1158 x_id1 => l_id1,
1159 x_id2 => l_id2,
1160 x_name => l_name,
1161 x_description => l_description,
1162 x_status => l_status,
1163 x_start_date => l_start_date,
1164 x_end_date => l_end_date,
1165 x_org_id => l_org_id,
1166 x_inv_org_id => l_inv_org_id,
1167 x_book_type_code => l_book_type_code,
1168 x_select => l_select);
1169
1170 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1171 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1172 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1173 RAISE OKL_API.G_EXCEPTION_ERROR;
1174 END IF;
1175
1176 l_rulv_disp_rec.obj2_name := l_name;
1177 l_rulv_disp_rec.obj2_descr := l_description;
1178 l_rulv_disp_rec.obj2_status := l_status;
1179 l_rulv_disp_rec.obj2_start_date := l_start_date;
1180 l_rulv_disp_rec.obj2_end_date := l_end_date;
1181 l_rulv_disp_rec.obj2_org_id := l_org_id;
1182 l_rulv_disp_rec.obj2_inv_org_id := l_inv_org_id;
1183 l_rulv_disp_rec.obj2_book_type_code := l_book_type_code;
1184 l_rulv_disp_rec.obj2_select := l_select;
1185 --dbms_output.put_line('Name '||l_name);
1186 End If;
1187
1188 If p_rulv_rec.jtot_object3_code is not null then
1189 l_id1 := null;
1190 l_id2 := null;
1191 l_name := null;
1192 l_description := null;
1193 l_status := null;
1194 l_start_date := null;
1195 l_end_date := null;
1196 l_org_id := to_number(null);
1197 l_inv_org_id := to_number(null);
1198 l_book_type_code := null;
1199 l_select := null;
1200
1201 Get_jtot_object(p_api_version => p_api_version,
1202 p_init_msg_list => p_init_msg_list,
1203 p_object_code => p_rulv_rec.jtot_object3_code,
1204 p_id1 => p_rulv_rec.object3_id1,
1205 p_id2 => p_rulv_rec.object3_id2,
1206 x_return_status => x_return_status,
1207 x_msg_count => x_msg_count,
1208 x_msg_data => x_msg_data,
1209 x_id1 => l_id1,
1210 x_id2 => l_id2,
1211 x_name => l_name,
1212 x_description => l_description,
1213 x_status => l_status,
1214 x_start_date => l_start_date,
1215 x_end_date => l_end_date,
1216 x_org_id => l_org_id,
1217 x_inv_org_id => l_inv_org_id,
1218 x_book_type_code => l_book_type_code,
1219 x_select => l_select);
1220
1221 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1222 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1223 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1224 RAISE OKL_API.G_EXCEPTION_ERROR;
1225 END IF;
1226
1227 l_rulv_disp_rec.obj3_name := l_name;
1228 l_rulv_disp_rec.obj3_descr := l_description;
1229 l_rulv_disp_rec.obj3_status := l_status;
1230 l_rulv_disp_rec.obj3_start_date := l_start_date;
1231 l_rulv_disp_rec.obj3_end_date := l_end_date;
1232 l_rulv_disp_rec.obj3_org_id := l_org_id;
1233 l_rulv_disp_rec.obj3_inv_org_id := l_inv_org_id;
1234 l_rulv_disp_rec.obj3_book_type_code := l_book_type_code;
1235 l_rulv_disp_rec.obj3_select := l_select;
1236 --dbms_output.put_line('Name '||l_name);
1237 --all the jtots done now reinitialize the columns
1238 l_id1 := null;
1239 l_id2 := null;
1240 l_name := null;
1241 l_description := null;
1242 l_status := null;
1243 l_start_date := null;
1244 l_end_date := null;
1245 l_org_id := to_number(null);
1246 l_inv_org_id := to_number(null);
1247 l_book_type_code := null;
1248 l_select := null;
1249 End If;
1250
1251 If p_rulv_rec.rule_information1 is not null then
1252 l_name := null;
1253 l_select := null;
1254 Get_rule_Information (p_api_version => p_api_version,
1255 p_init_msg_list => p_init_msg_list,
1256 p_rdf_code => p_rulv_rec.rule_information_category,
1257 p_appl_col_name => 'RULE_INFORMATION1',
1258 p_rule_info => p_rulv_rec.rule_information1,
1259 x_return_status => x_return_status,
1260 x_msg_count => x_msg_count,
1261 x_msg_data => x_msg_data,
1262 x_name => l_name,
1263 x_select => l_select);
1264 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1265 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1266 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1267 RAISE OKL_API.G_EXCEPTION_ERROR;
1268 END IF;
1269
1270 l_rulv_disp_rec.rul_info1_name := l_name;
1271 l_rulv_disp_rec.rul_info1_select := l_select;
1272 End If;
1273
1274 If p_rulv_rec.rule_information2 is not null then
1275 l_name := null;
1276 l_select := null;
1277 Get_rule_Information (p_api_version => p_api_version,
1278 p_init_msg_list => p_init_msg_list,
1279 p_rdf_code => p_rulv_rec.rule_information_category,
1280 p_appl_col_name => 'RULE_INFORMATION2',
1281 p_rule_info => p_rulv_rec.rule_information2,
1282 x_return_status => x_return_status,
1283 x_msg_count => x_msg_count,
1284 x_msg_data => x_msg_data,
1285 x_name => l_name,
1286 x_select => l_select);
1287 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1288 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1289 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1290 RAISE OKL_API.G_EXCEPTION_ERROR;
1291 END IF;
1292
1293 l_rulv_disp_rec.rul_info2_name := l_name;
1294 l_rulv_disp_rec.rul_info2_select := l_select;
1295 End If;
1296
1297 If p_rulv_rec.rule_information3 is not null then
1298 l_name := null;
1299 l_select := null;
1300 Get_rule_Information (p_api_version => p_api_version,
1301 p_init_msg_list => p_init_msg_list,
1302 p_rdf_code => p_rulv_rec.rule_information_category,
1303 p_appl_col_name => 'RULE_INFORMATION3',
1304 p_rule_info => p_rulv_rec.rule_information3,
1305 x_return_status => x_return_status,
1306 x_msg_count => x_msg_count,
1307 x_msg_data => x_msg_data,
1308 x_name => l_name,
1309 x_select => l_select);
1310 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1311 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1312 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1313 RAISE OKL_API.G_EXCEPTION_ERROR;
1314 END IF;
1315
1316 l_rulv_disp_rec.rul_info3_name := l_name;
1317 l_rulv_disp_rec.rul_info3_select := l_select;
1318 End If;
1319 If p_rulv_rec.rule_information4 is not null then
1320 l_name := null;
1321 l_select := null;
1322 Get_rule_Information (p_api_version => p_api_version,
1323 p_init_msg_list => p_init_msg_list,
1324 p_rdf_code => p_rulv_rec.rule_information_category,
1325 p_appl_col_name => 'RULE_INFORMATION4',
1326 p_rule_info => p_rulv_rec.rule_information4,
1327 x_return_status => x_return_status,
1328 x_msg_count => x_msg_count,
1329 x_msg_data => x_msg_data,
1330 x_name => l_name,
1331 x_select => l_select);
1332 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1333 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1334 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1335 RAISE OKL_API.G_EXCEPTION_ERROR;
1336 END IF;
1337
1338 l_rulv_disp_rec.rul_info4_name := l_name;
1339 l_rulv_disp_rec.rul_info4_select := l_select;
1340 End If;
1341 If p_rulv_rec.rule_information5 is not null then
1342 l_name := null;
1343 l_select := null;
1344 Get_rule_Information (p_api_version => p_api_version,
1345 p_init_msg_list => p_init_msg_list,
1346 p_rdf_code => p_rulv_rec.rule_information_category,
1347 p_appl_col_name => 'RULE_INFORMATION5',
1348 p_rule_info => p_rulv_rec.rule_information5,
1349 x_return_status => x_return_status,
1350 x_msg_count => x_msg_count,
1351 x_msg_data => x_msg_data,
1352 x_name => l_name,
1353 x_select => l_select);
1354 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1355 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1356 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1357 RAISE OKL_API.G_EXCEPTION_ERROR;
1358 END IF;
1359
1360 l_rulv_disp_rec.rul_info5_name := l_name;
1361 l_rulv_disp_rec.rul_info5_select := l_select;
1362 End If;
1363 If p_rulv_rec.rule_information6 is not null then
1364 l_name := null;
1365 l_select := null;
1366 Get_rule_Information (p_api_version => p_api_version,
1367 p_init_msg_list => p_init_msg_list,
1368 p_rdf_code => p_rulv_rec.rule_information_category,
1369 p_appl_col_name => 'RULE_INFORMATION6',
1370 p_rule_info => p_rulv_rec.rule_information6,
1371 x_return_status => x_return_status,
1372 x_msg_count => x_msg_count,
1373 x_msg_data => x_msg_data,
1374 x_name => l_name,
1375 x_select => l_select);
1376 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1377 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1378 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1379 RAISE OKL_API.G_EXCEPTION_ERROR;
1380 END IF;
1381
1382 l_rulv_disp_rec.rul_info6_name := l_name;
1383 l_rulv_disp_rec.rul_info6_select := l_select;
1384 End If;
1385 If p_rulv_rec.rule_information7 is not null then
1386 l_name := null;
1387 l_select := null;
1388 Get_rule_Information (p_api_version => p_api_version,
1389 p_init_msg_list => p_init_msg_list,
1390 p_rdf_code => p_rulv_rec.rule_information_category,
1391 p_appl_col_name => 'RULE_INFORMATION7',
1392 p_rule_info => p_rulv_rec.rule_information7,
1393 x_return_status => x_return_status,
1394 x_msg_count => x_msg_count,
1395 x_msg_data => x_msg_data,
1396 x_name => l_name,
1397 x_select => l_select);
1398 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1399 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1400 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1401 RAISE OKL_API.G_EXCEPTION_ERROR;
1402 END IF;
1403
1404 l_rulv_disp_rec.rul_info7_name := l_name;
1405 l_rulv_disp_rec.rul_info7_select := l_select;
1406 End If;
1407 If p_rulv_rec.rule_information8 is not null then
1408 l_name := null;
1409 l_select := null;
1410 Get_rule_Information (p_api_version => p_api_version,
1411 p_init_msg_list => p_init_msg_list,
1412 p_rdf_code => p_rulv_rec.rule_information_category,
1413 p_appl_col_name => 'RULE_INFORMATION8',
1414 p_rule_info => p_rulv_rec.rule_information8,
1415 x_return_status => x_return_status,
1416 x_msg_count => x_msg_count,
1417 x_msg_data => x_msg_data,
1418 x_name => l_name,
1419 x_select => l_select);
1420 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1421 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1422 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1423 RAISE OKL_API.G_EXCEPTION_ERROR;
1424 END IF;
1425
1426 l_rulv_disp_rec.rul_info8_name := l_name;
1427 l_rulv_disp_rec.rul_info8_select := l_select;
1428 End If;
1429 If p_rulv_rec.rule_information9 is not null then
1430 l_name := null;
1431 l_select := null;
1432 Get_rule_Information (p_api_version => p_api_version,
1433 p_init_msg_list => p_init_msg_list,
1434 p_rdf_code => p_rulv_rec.rule_information_category,
1435 p_appl_col_name => 'RULE_INFORMATION9',
1436 p_rule_info => p_rulv_rec.rule_information9,
1437 x_return_status => x_return_status,
1438 x_msg_count => x_msg_count,
1439 x_msg_data => x_msg_data,
1440 x_name => l_name,
1441 x_select => l_select);
1442 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1443 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1444 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1445 RAISE OKL_API.G_EXCEPTION_ERROR;
1446 END IF;
1447
1448 l_rulv_disp_rec.rul_info9_name := l_name;
1449 l_rulv_disp_rec.rul_info9_select := l_select;
1450 End If;
1451 If p_rulv_rec.rule_information10 is not null then
1452 l_name := null;
1453 l_select := null;
1454 Get_rule_Information (p_api_version => p_api_version,
1455 p_init_msg_list => p_init_msg_list,
1456 p_rdf_code => p_rulv_rec.rule_information_category,
1457 p_appl_col_name => 'RULE_INFORMATION10',
1458 p_rule_info => p_rulv_rec.rule_information10,
1459 x_return_status => x_return_status,
1460 x_msg_count => x_msg_count,
1461 x_msg_data => x_msg_data,
1462 x_name => l_name,
1463 x_select => l_select);
1464 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1465 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1466 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1467 RAISE OKL_API.G_EXCEPTION_ERROR;
1468 END IF;
1469
1470 l_rulv_disp_rec.rul_info10_name := l_name;
1471 l_rulv_disp_rec.rul_info10_select := l_select;
1472 End If;
1473 If p_rulv_rec.rule_information11 is not null then
1474 l_name := null;
1475 l_select := null;
1476 Get_rule_Information (p_api_version => p_api_version,
1477 p_init_msg_list => p_init_msg_list,
1478 p_rdf_code => p_rulv_rec.rule_information_category,
1479 p_appl_col_name => 'RULE_INFORMATION11',
1480 p_rule_info => p_rulv_rec.rule_information11,
1481 x_return_status => x_return_status,
1482 x_msg_count => x_msg_count,
1483 x_msg_data => x_msg_data,
1484 x_name => l_name,
1485 x_select => l_select);
1486 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1487 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1488 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1489 RAISE OKL_API.G_EXCEPTION_ERROR;
1490 END IF;
1491
1492 l_rulv_disp_rec.rul_info11_name := l_name;
1493 l_rulv_disp_rec.rul_info11_select := l_select;
1494 End If;
1495 If p_rulv_rec.rule_information12 is not null then
1496 l_name := null;
1497 l_select := null;
1498 Get_rule_Information (p_api_version => p_api_version,
1499 p_init_msg_list => p_init_msg_list,
1500 p_rdf_code => p_rulv_rec.rule_information_category,
1501 p_appl_col_name => 'RULE_INFORMATION12',
1502 p_rule_info => p_rulv_rec.rule_information12,
1503 x_return_status => x_return_status,
1504 x_msg_count => x_msg_count,
1505 x_msg_data => x_msg_data,
1506 x_name => l_name,
1507 x_select => l_select);
1508 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1509 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1510 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1511 RAISE OKL_API.G_EXCEPTION_ERROR;
1512 END IF;
1513
1514 l_rulv_disp_rec.rul_info12_name := l_name;
1515 l_rulv_disp_rec.rul_info12_select := l_select;
1516
1517 End If;
1518 If p_rulv_rec.rule_information13 is not null then
1519 l_name := null;
1520 l_select := null;
1521 Get_rule_Information (p_api_version => p_api_version,
1522 p_init_msg_list => p_init_msg_list,
1523 p_rdf_code => p_rulv_rec.rule_information_category,
1524 p_appl_col_name => 'RULE_INFORMATION13',
1525 p_rule_info => p_rulv_rec.rule_information13,
1526 x_return_status => x_return_status,
1527 x_msg_count => x_msg_count,
1528 x_msg_data => x_msg_data,
1529 x_name => l_name,
1530 x_select => l_select);
1531 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1532 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1533 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1534 RAISE OKL_API.G_EXCEPTION_ERROR;
1535 END IF;
1536
1537 l_rulv_disp_rec.rul_info13_name := l_name;
1538 l_rulv_disp_rec.rul_info13_select := l_select;
1539 End If;
1540 If p_rulv_rec.rule_information14 is not null then
1541 l_name := null;
1542 l_select := null;
1543 Get_rule_Information (p_api_version => p_api_version,
1544 p_init_msg_list => p_init_msg_list,
1545 p_rdf_code => p_rulv_rec.rule_information_category,
1546 p_appl_col_name => 'RULE_INFORMATION14',
1547 p_rule_info => p_rulv_rec.rule_information14,
1548 x_return_status => x_return_status,
1549 x_msg_count => x_msg_count,
1550 x_msg_data => x_msg_data,
1551 x_name => l_name,
1552 x_select => l_select);
1553 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1554 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1555 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1556 RAISE OKL_API.G_EXCEPTION_ERROR;
1557 END IF;
1558
1559 l_rulv_disp_rec.rul_info14_name := l_name;
1560 l_rulv_disp_rec.rul_info14_select := l_select;
1561 End If;
1562 If p_rulv_rec.rule_information15 is not null then
1563 l_name := null;
1564 l_select := null;
1565 Get_rule_Information (p_api_version => p_api_version,
1566 p_init_msg_list => p_init_msg_list,
1567 p_rdf_code => p_rulv_rec.rule_information_category,
1568 p_appl_col_name => 'RULE_INFORMATION15',
1569 p_rule_info => p_rulv_rec.rule_information15,
1570 x_return_status => x_return_status,
1571 x_msg_count => x_msg_count,
1572 x_msg_data => x_msg_data,
1573 x_name => l_name,
1574 x_select => l_select);
1575 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1576 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1577 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1578 RAISE OKL_API.G_EXCEPTION_ERROR;
1579 END IF;
1580
1581 l_rulv_disp_rec.rul_info15_name := l_name;
1582 l_rulv_disp_rec.rul_info15_select := l_select;
1583 End If;
1584 x_rulv_disp_rec := l_rulv_disp_rec;
1585
1586 --Call End Activity
1587 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
1588 x_msg_data => x_msg_data);
1589 EXCEPTION
1590 when OKL_API.G_EXCEPTION_ERROR then
1591 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1592 p_api_name => l_api_name,
1593 p_pkg_name => g_pkg_name,
1594 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1595 x_msg_count => x_msg_count,
1596 x_msg_data => x_msg_data,
1597 p_api_type => g_api_type);
1598
1599 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
1600 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1601 p_api_name => l_api_name,
1602 p_pkg_name => g_pkg_name,
1603 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1604 x_msg_count => x_msg_count,
1605 x_msg_data => x_msg_data,
1606 p_api_type => g_api_type);
1607
1608 when OTHERS then
1609 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1610 p_api_name => l_api_name,
1611 p_pkg_name => g_pkg_name,
1612 p_exc_name => 'OTHERS',
1613 x_msg_count => x_msg_count,
1614 x_msg_data => x_msg_data,
1615 p_api_type => g_api_type);
1616
1617 End Get_Rule_Disp_Value;
1618 --Start of Comments
1619 --Procedure : Get_Rule_Segment_value
1620 --Description : Fetches the displayed value and select clauses of
1621 -- of specific rule segment.
1622 --Note : This API requires exact screen prompt label of the segment
1623 -- to be passed as p_rdf_name
1624 --End of Comments
1625 Procedure Get_rule_Segment_Value(p_api_version IN NUMBER,
1626 p_init_msg_list IN VARCHAR2,
1627 x_return_status OUT NOCOPY VARCHAR2,
1628 x_msg_count OUT NOCOPY NUMBER,
1629 x_msg_data OUT NOCOPY VARCHAR2,
1630 p_chr_id IN NUMBER,
1631 p_cle_id IN NUMBER,
1632 p_rgd_code IN VARCHAR2,
1633 p_rdf_code IN VARCHAR2,
1634 p_rdf_name IN VARCHAR2,
1635 x_id1 OUT NOCOPY VARCHAR2,
1636 x_id2 OUT NOCOPY VARCHAR2,
1637 x_name OUT NOCOPY VARCHAR2,
1638 x_description OUT NOCOPY VARCHAR2,
1639 x_status OUT NOCOPY VARCHAR2,
1640 x_start_date OUT NOCOPY DATE,
1641 x_end_date OUT NOCOPY DATE,
1642 x_org_id OUT NOCOPY NUMBER,
1643 x_inv_org_id OUT NOCOPY NUMBER,
1644 x_book_type_code OUT NOCOPY VARCHAR2,
1645 x_select OUT NOCOPY VARCHAR2) is
1646
1647 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1648 l_api_name CONSTANT VARCHAR2(30) := 'GET_RULE_SEGMENT_VALUE';
1649 l_api_version CONSTANT NUMBER := 1.0;
1650
1651 l_rgpv_tbl rgpv_tbl_type;
1652 l_rulv_tbl rulv_tbl_type;
1653 l_rgpv_rec rgpv_rec_type;
1654 l_rulv_rec rulv_rec_type;
1655
1656 l_rg_count NUMBER;
1657 l_rule_count NUMBER;
1658
1659 Cursor rule_dff_app_col_cur is
1660 -- select dflex.descriptive_flex_context_code
1661 -- , dflex.application_column_name
1662 -- from fnd_descr_flex_col_usage_vl dflex
1663 -- where dflex.application_id=510
1664 -- and dflex.descriptive_flexfield_name='OKC Rule Developer DF'
1665 -- and dflex.descriptive_flex_context_code = p_rdf_code
1666 -- and dflex.form_left_prompt = p_rdf_name
1667 --union
1668 --added for rule striping (once finalized remove the top portion of union)
1669 Select dflex.descriptive_flex_context_code
1670 , dflex.application_column_name
1671 from fnd_descr_flex_col_usage_vl dflex,
1672 okc_rule_defs_v rdefv
1673 where dflex.application_id = rdefv.application_id
1674 and dflex.descriptive_flexfield_name = rdefv.descriptive_flexfield_name
1675 and dflex.descriptive_flex_context_code = rdefv.rule_code
1676 and dflex.form_left_prompt = p_rdf_name
1677 and rdefv.rule_code = p_rdf_code
1678 -- order by 1;
1679 order by dflex.descriptive_flex_context_code;
1680
1681 rule_dff_app_col_rec rule_dff_app_col_cur%rowtype;
1682 Begin
1683 --Call OKL_API.START_ACTIVITY
1684 l_return_status := OKL_API.START_ACTIVITY( substr(l_api_name,1,26),
1685 G_PKG_NAME,
1686 p_init_msg_list,
1687 l_api_version,
1688 p_api_version,
1689 '_PVT',
1690 x_return_status);
1691 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1692 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1693 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1694 RAISE OKL_API.G_EXCEPTION_ERROR;
1695 END IF;
1696 --get rule group record for the rgd code
1697 Get_Contract_Rgs(p_api_version => p_api_version,
1698 p_init_msg_list => p_init_msg_list,
1699 p_chr_id => p_chr_id,
1700 p_cle_id => p_cle_id,
1701 p_rgd_code => p_rgd_code,
1702 x_return_status => x_return_status,
1703 x_msg_count => x_msg_count,
1704 x_msg_data => x_msg_data,
1705 x_rgpv_tbl => l_rgpv_tbl,
1706 x_rg_count => l_rg_count);
1707 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1708 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1709 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1710 RAISE OKL_API.G_EXCEPTION_ERROR;
1711 END IF;
1712 if l_rgpv_tbl.FIRST is null Then
1713 x_name := NULL;
1714 Else
1715 l_rgpv_rec := l_rgpv_tbl(1);
1716
1717 --get rule record for rdf_code
1718 Get_Contract_Rules(p_api_version => p_api_version,
1719 p_init_msg_list => p_init_msg_list,
1720 p_rgpv_rec => l_rgpv_rec,
1721 p_rdf_code => p_rdf_code,
1722 x_return_status => x_return_status,
1723 x_msg_count => x_msg_count,
1724 x_msg_data => x_msg_data,
1725 x_rulv_tbl => l_rulv_tbl,
1726 x_rule_count => l_rule_count);
1727 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1728 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1729 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1730 RAISE OKL_API.G_EXCEPTION_ERROR;
1731 END IF;
1732 If l_rulv_tbl.FIRST is null Then
1733 x_name := Null;
1734 Else
1735 l_rulv_rec := l_rulv_tbl(1);
1736
1737 --bug# 3024752 :
1738 If okl_context.get_okc_organization_id is null then
1739 okl_context.set_okc_org_context(p_chr_id => l_rulv_rec.dnz_chr_id);
1740 End If;
1741
1742 Open rule_dff_app_col_cur;
1743 Fetch rule_dff_app_col_cur into rule_dff_app_col_rec;
1744 If rule_dff_app_col_cur%NOTFOUND Then
1745 --dbms_output.put_line('failed in select from fnd_descr_flex_col_usages');
1746 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
1747 p_msg_name => G_DFF_FETCH_FAILED,
1748 p_token1 => G_APPLICATION_COL_TOKEN,
1749 p_token1_value => p_rdf_name,
1750 p_token2 => G_RULE_CODE_TOKEN,
1751 p_token2_value => p_rdf_code
1752 );
1753 RAISE OKL_API.G_EXCEPTION_ERROR;
1754 Elsif rule_dff_app_col_rec.application_column_name = 'RULE_INFORMATION1'
1755 and l_rulv_rec.RULE_INFORMATION1 is not Null Then
1756 Get_rule_Information (p_api_version => p_api_version,
1757 p_init_msg_list => p_init_msg_list,
1758 p_rdf_code => p_rdf_code,
1759 p_appl_col_name => rule_dff_app_col_rec.application_column_name,
1760 p_rule_info => l_rulv_rec.RULE_INFORMATION1,
1761 x_return_status => x_return_status,
1762 x_msg_count => x_msg_count,
1763 x_msg_data => x_msg_data,
1764 x_name => x_name,
1765 x_select => x_select);
1766 Elsif rule_dff_app_col_rec.application_column_name = 'RULE_INFORMATION2'
1767 and l_rulv_rec.RULE_INFORMATION2 is not null Then
1768 Get_rule_Information (p_api_version => p_api_version,
1769 p_init_msg_list => p_init_msg_list,
1770 p_rdf_code => p_rdf_code,
1771 p_appl_col_name => rule_dff_app_col_rec.application_column_name,
1772 p_rule_info => l_rulv_rec.RULE_INFORMATION2,
1773 x_return_status => x_return_status,
1774 x_msg_count => x_msg_count,
1775 x_msg_data => x_msg_data,
1776 x_name => x_name,
1777 x_select => x_select);
1778 Elsif rule_dff_app_col_rec.application_column_name = 'RULE_INFORMATION3'
1779 and l_rulv_rec.RULE_INFORMATION3 is not null Then
1780 Get_rule_Information (p_api_version => p_api_version,
1781 p_init_msg_list => p_init_msg_list,
1782 p_rdf_code => p_rdf_code,
1783 p_appl_col_name => rule_dff_app_col_rec.application_column_name,
1784 p_rule_info => l_rulv_rec.RULE_INFORMATION3,
1785 x_return_status => x_return_status,
1786 x_msg_count => x_msg_count,
1787 x_msg_data => x_msg_data,
1788 x_name => x_name,
1789 x_select => x_select);
1790 Elsif rule_dff_app_col_rec.application_column_name = 'RULE_INFORMATION4'
1791 and l_rulv_rec.RULE_INFORMATION4 is not null Then
1792 Get_rule_Information (p_api_version => p_api_version,
1793 p_init_msg_list => p_init_msg_list,
1794 p_rdf_code => p_rdf_code,
1795 p_appl_col_name => rule_dff_app_col_rec.application_column_name,
1796 p_rule_info => l_rulv_rec.RULE_INFORMATION4,
1797 x_return_status => x_return_status,
1798 x_msg_count => x_msg_count,
1799 x_msg_data => x_msg_data,
1800 x_name => x_name,
1801 x_select => x_select);
1802 Elsif rule_dff_app_col_rec.application_column_name = 'RULE_INFORMATION5'
1803 and l_rulv_rec.RULE_INFORMATION5 is not null Then
1804 Get_rule_Information (p_api_version => p_api_version,
1805 p_init_msg_list => p_init_msg_list,
1806 p_rdf_code => p_rdf_code,
1807 p_appl_col_name => rule_dff_app_col_rec.application_column_name,
1808 p_rule_info => l_rulv_rec.RULE_INFORMATION5,
1809 x_return_status => x_return_status,
1810 x_msg_count => x_msg_count,
1811 x_msg_data => x_msg_data,
1812 x_name => x_name,
1813 x_select => x_select);
1814 Elsif rule_dff_app_col_rec.application_column_name = 'RULE_INFORMATION6'
1815 and l_rulv_rec.RULE_INFORMATION6 is not null Then
1816 Get_rule_Information (p_api_version => p_api_version,
1817 p_init_msg_list => p_init_msg_list,
1818 p_rdf_code => p_rdf_code,
1819 p_appl_col_name => rule_dff_app_col_rec.application_column_name,
1820 p_rule_info => l_rulv_rec.RULE_INFORMATION6,
1821 x_return_status => x_return_status,
1822 x_msg_count => x_msg_count,
1823 x_msg_data => x_msg_data,
1824 x_name => x_name,
1825 x_select => x_select);
1826 Elsif rule_dff_app_col_rec.application_column_name = 'RULE_INFORMATION7'
1827 and l_rulv_rec.RULE_INFORMATION7 is not null Then
1828 Get_rule_Information (p_api_version => p_api_version,
1829 p_init_msg_list => p_init_msg_list,
1830 p_rdf_code => p_rdf_code,
1831 p_appl_col_name => rule_dff_app_col_rec.application_column_name,
1832 p_rule_info => l_rulv_rec.RULE_INFORMATION7,
1833 x_return_status => x_return_status,
1834 x_msg_count => x_msg_count,
1835 x_msg_data => x_msg_data,
1836 x_name => x_name,
1837 x_select => x_select);
1838 Elsif rule_dff_app_col_rec.application_column_name = 'RULE_INFORMATION8'
1839 and l_rulv_rec.RULE_INFORMATION8 is not null Then
1840 Get_rule_Information (p_api_version => p_api_version,
1841 p_init_msg_list => p_init_msg_list,
1842 p_rdf_code => p_rdf_code,
1843 p_appl_col_name => rule_dff_app_col_rec.application_column_name,
1844 p_rule_info => l_rulv_rec.RULE_INFORMATION8,
1845 x_return_status => x_return_status,
1846 x_msg_count => x_msg_count,
1847 x_msg_data => x_msg_data,
1848 x_name => x_name,
1849 x_select => x_select);
1850 Elsif rule_dff_app_col_rec.application_column_name = 'RULE_INFORMATION9'
1851 and l_rulv_rec.RULE_INFORMATION9 is not null Then
1852 Get_rule_Information (p_api_version => p_api_version,
1853 p_init_msg_list => p_init_msg_list,
1854 p_rdf_code => p_rdf_code,
1855 p_appl_col_name => rule_dff_app_col_rec.application_column_name,
1856 p_rule_info => l_rulv_rec.RULE_INFORMATION9,
1857 x_return_status => x_return_status,
1858 x_msg_count => x_msg_count,
1859 x_msg_data => x_msg_data,
1860 x_name => x_name,
1861 x_select => x_select);
1862 Elsif rule_dff_app_col_rec.application_column_name = 'RULE_INFORMATION10'
1863 and l_rulv_rec.RULE_INFORMATION10 is not null Then
1864 Get_rule_Information (p_api_version => p_api_version,
1865 p_init_msg_list => p_init_msg_list,
1866 p_rdf_code => p_rdf_code,
1867 p_appl_col_name => rule_dff_app_col_rec.application_column_name,
1868 p_rule_info => l_rulv_rec.RULE_INFORMATION10,
1869 x_return_status => x_return_status,
1870 x_msg_count => x_msg_count,
1871 x_msg_data => x_msg_data,
1872 x_name => x_name,
1873 x_select => x_select);
1874 Elsif rule_dff_app_col_rec.application_column_name = 'RULE_INFORMATION11'
1875 and l_rulv_rec.RULE_INFORMATION11 is not Null Then
1876 Get_rule_Information (p_api_version => p_api_version,
1877 p_init_msg_list => p_init_msg_list,
1878 p_rdf_code => p_rdf_code,
1879 p_appl_col_name => rule_dff_app_col_rec.application_column_name,
1880 p_rule_info => l_rulv_rec.RULE_INFORMATION11,
1881 x_return_status => x_return_status,
1882 x_msg_count => x_msg_count,
1883 x_msg_data => x_msg_data,
1884 x_name => x_name,
1885 x_select => x_select);
1886 Elsif rule_dff_app_col_rec.application_column_name = 'RULE_INFORMATION12'
1887 and l_rulv_rec.RULE_INFORMATION12 is not null Then
1888 Get_rule_Information (p_api_version => p_api_version,
1889 p_init_msg_list => p_init_msg_list,
1890 p_rdf_code => p_rdf_code,
1891 p_appl_col_name => rule_dff_app_col_rec.application_column_name,
1892 p_rule_info => l_rulv_rec.RULE_INFORMATION12,
1893 x_return_status => x_return_status,
1894 x_msg_count => x_msg_count,
1895 x_msg_data => x_msg_data,
1896 x_name => x_name,
1897 x_select => x_select);
1898 Elsif rule_dff_app_col_rec.application_column_name = 'RULE_INFORMATION13'
1899 and l_rulv_rec.RULE_INFORMATION13 is not null Then
1900 Get_rule_Information (p_api_version => p_api_version,
1901 p_init_msg_list => p_init_msg_list,
1902 p_rdf_code => p_rdf_code,
1903 p_appl_col_name => rule_dff_app_col_rec.application_column_name,
1904 p_rule_info => l_rulv_rec.RULE_INFORMATION13,
1905 x_return_status => x_return_status,
1906 x_msg_count => x_msg_count,
1907 x_msg_data => x_msg_data,
1908 x_name => x_name,
1909 x_select => x_select);
1910 Elsif rule_dff_app_col_rec.application_column_name = 'RULE_INFORMATION14'
1911 and l_rulv_rec.RULE_INFORMATION14 is not null Then
1912 Get_rule_Information (p_api_version => p_api_version,
1913 p_init_msg_list => p_init_msg_list,
1914 p_rdf_code => p_rdf_code,
1915 p_appl_col_name => rule_dff_app_col_rec.application_column_name,
1916 p_rule_info => l_rulv_rec.RULE_INFORMATION14,
1917 x_return_status => x_return_status,
1918 x_msg_count => x_msg_count,
1919 x_msg_data => x_msg_data,
1920 x_name => x_name,
1921 x_select => x_select);
1922 Elsif rule_dff_app_col_rec.application_column_name = 'RULE_INFORMATION15'
1923 and l_rulv_rec.RULE_INFORMATION15 is not null Then
1924 Get_rule_Information (p_api_version => p_api_version,
1925 p_init_msg_list => p_init_msg_list,
1926 p_rdf_code => p_rdf_code,
1927 p_appl_col_name => rule_dff_app_col_rec.application_column_name,
1928 p_rule_info => l_rulv_rec.RULE_INFORMATION15,
1929 x_return_status => x_return_status,
1930 x_msg_count => x_msg_count,
1931 x_msg_data => x_msg_data,
1932 x_name => x_name,
1933 x_select => x_select);
1934 Elsif rule_dff_app_col_rec.application_column_name = 'JTOT_OBJECT1_CODE'
1935 and l_rulv_rec.object1_id1 is not null Then
1936 Get_jtot_object(p_api_version => p_api_version,
1937 p_init_msg_list => p_init_msg_list,
1938 p_object_code => l_rulv_rec.jtot_object1_code,
1939 p_id1 => l_rulv_rec.object1_id1,
1940 p_id2 => l_rulv_rec.object1_id2,
1941 x_return_status => x_return_status,
1942 x_msg_count => x_msg_count,
1943 x_msg_data => x_msg_data,
1944 x_id1 => x_id1,
1945 x_id2 => x_id2,
1946 x_name => x_name,
1947 x_description => x_description,
1948 x_status => x_status,
1949 x_start_date => x_start_date,
1950 x_end_date => x_end_date,
1951 x_org_id => x_org_id,
1952 x_inv_org_id => x_inv_org_id,
1953 x_book_type_code => x_book_type_code,
1954 x_select => x_select);
1955 Elsif rule_dff_app_col_rec.application_column_name = 'JTOT_OBJECT2_CODE'
1956 and l_rulv_rec.object2_id1 is not null Then
1957 Get_jtot_object(p_api_version => p_api_version,
1958 p_init_msg_list => p_init_msg_list,
1959 p_object_code => l_rulv_rec.jtot_object2_code,
1960 p_id1 => l_rulv_rec.object2_id1,
1961 p_id2 => l_rulv_rec.object2_id2,
1962 x_return_status => x_return_status,
1963 x_msg_count => x_msg_count,
1964 x_msg_data => x_msg_data,
1965 x_id1 => x_id1,
1966 x_id2 => x_id2,
1967 x_name => x_name,
1968 x_description => x_description,
1969 x_status => x_status,
1970 x_start_date => x_start_date,
1971 x_end_date => x_end_date,
1972 x_org_id => x_org_id,
1973 x_inv_org_id => x_inv_org_id,
1974 x_book_type_code => x_book_type_code,
1975 x_select => x_select);
1976 Elsif rule_dff_app_col_rec.application_column_name = 'JTOT_OBJECT3_CODE'
1977 and l_rulv_rec.object3_id1 is not null Then
1978 Get_jtot_object(p_api_version => p_api_version,
1979 p_init_msg_list => p_init_msg_list,
1980 p_object_code => l_rulv_rec.jtot_object3_code,
1981 p_id1 => l_rulv_rec.object3_id1,
1982 p_id2 => l_rulv_rec.object3_id2,
1983 x_return_status => x_return_status,
1984 x_msg_count => x_msg_count,
1985 x_msg_data => x_msg_data,
1986 x_id1 => x_id1,
1987 x_id2 => x_id2,
1988 x_name => x_name,
1989 x_description => x_description,
1990 x_status => x_status,
1991 x_start_date => x_start_date,
1992 x_end_date => x_end_date,
1993 x_org_id => x_org_id,
1994 x_inv_org_id => x_inv_org_id,
1995 x_book_type_code => x_book_type_code,
1996 x_select => x_select);
1997 End If;
1998 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1999 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2000 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2001 RAISE OKL_API.G_EXCEPTION_ERROR;
2002 END IF;
2003 Close rule_dff_app_col_cur;
2004 End If;
2005 End If;
2006 --Call End Activity
2007 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
2008 x_msg_data => x_msg_data);
2009 EXCEPTION
2010 when OKL_API.G_EXCEPTION_ERROR then
2011 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2012 p_api_name => l_api_name,
2013 p_pkg_name => g_pkg_name,
2014 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
2015 x_msg_count => x_msg_count,
2016 x_msg_data => x_msg_data,
2017 p_api_type => g_api_type);
2018
2019 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
2020 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2021 p_api_name => l_api_name,
2022 p_pkg_name => g_pkg_name,
2023 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
2024 x_msg_count => x_msg_count,
2025 x_msg_data => x_msg_data,
2026 p_api_type => g_api_type);
2027
2028 when OTHERS then
2029 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2030 p_api_name => l_api_name,
2031 p_pkg_name => g_pkg_name,
2032 p_exc_name => 'OTHERS',
2033 x_msg_count => x_msg_count,
2034 x_msg_data => x_msg_data,
2035 p_api_type => g_api_type);
2036 End Get_Rule_Segment_Value;
2037 --Start of Comments
2038 --Bug#2525946 : overloaded to take rule segment numbers as input
2039 --Procedure : Get_Rule_Segment_value
2040 --Description : Fetches the displayed value and select clauses of
2041 -- of specific rule segment.
2042 --Note : This API requires segment number
2043 -- Segment number 1 to 15 are mapped to RULE_INFORMATION1 to
2044 -- RULE_INFORMATION15. Segment Numbers 16, 17 and 18 are mapped
2045 -- to jtot_object1, jtot_object2 and jtot_object3 respectively
2046 --End of Comments
2047 Procedure Get_rule_Segment_Value(p_api_version IN NUMBER,
2048 p_init_msg_list IN VARCHAR2,
2049 x_return_status OUT NOCOPY VARCHAR2,
2050 x_msg_count OUT NOCOPY NUMBER,
2051 x_msg_data OUT NOCOPY VARCHAR2,
2052 p_chr_id IN NUMBER,
2053 p_cle_id IN NUMBER,
2054 p_rgd_code IN VARCHAR2,
2055 p_rdf_code IN VARCHAR2,
2056 p_segment_number IN NUMBER,
2057 x_id1 OUT NOCOPY VARCHAR2,
2058 x_id2 OUT NOCOPY VARCHAR2,
2059 x_name OUT NOCOPY VARCHAR2,
2060 x_description OUT NOCOPY VARCHAR2,
2061 x_status OUT NOCOPY VARCHAR2,
2062 x_start_date OUT NOCOPY DATE,
2063 x_end_date OUT NOCOPY DATE,
2064 x_org_id OUT NOCOPY NUMBER,
2065 x_inv_org_id OUT NOCOPY NUMBER,
2066 x_book_type_code OUT NOCOPY VARCHAR2,
2067 x_select OUT NOCOPY VARCHAR2) is
2068
2069 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2070 l_api_name CONSTANT VARCHAR2(30) := 'GET_RULE_SEGMENT_VALUE';
2071 l_api_version CONSTANT NUMBER := 1.0;
2072
2073 l_rgpv_tbl rgpv_tbl_type;
2074 l_rulv_tbl rulv_tbl_type;
2075 l_rgpv_rec rgpv_rec_type;
2076 l_rulv_rec rulv_rec_type;
2077
2078 l_rg_count NUMBER;
2079 l_rule_count NUMBER;
2080
2081 Begin
2082 --Call OKL_API.START_ACTIVITY
2083 l_return_status := OKL_API.START_ACTIVITY( substr(l_api_name,1,26),
2084 G_PKG_NAME,
2085 p_init_msg_list,
2086 l_api_version,
2087 p_api_version,
2088 '_PVT',
2089 x_return_status);
2090 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2091 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2092 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2093 RAISE OKL_API.G_EXCEPTION_ERROR;
2094 END IF;
2095 --get rule group record for the rgd code
2096 Get_Contract_Rgs(p_api_version => p_api_version,
2097 p_init_msg_list => p_init_msg_list,
2098 p_chr_id => p_chr_id,
2099 p_cle_id => p_cle_id,
2100 p_rgd_code => p_rgd_code,
2101 x_return_status => x_return_status,
2102 x_msg_count => x_msg_count,
2103 x_msg_data => x_msg_data,
2104 x_rgpv_tbl => l_rgpv_tbl,
2105 x_rg_count => l_rg_count);
2106 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2107 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2108 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2109 RAISE OKL_API.G_EXCEPTION_ERROR;
2110 END IF;
2111 if l_rgpv_tbl.FIRST is null Then
2112 x_name := NULL;
2113 Else
2114 l_rgpv_rec := l_rgpv_tbl(1);
2115
2116 --get rule record for rdf_code
2117 Get_Contract_Rules(p_api_version => p_api_version,
2118 p_init_msg_list => p_init_msg_list,
2119 p_rgpv_rec => l_rgpv_rec,
2120 p_rdf_code => p_rdf_code,
2121 x_return_status => x_return_status,
2122 x_msg_count => x_msg_count,
2123 x_msg_data => x_msg_data,
2124 x_rulv_tbl => l_rulv_tbl,
2125 x_rule_count => l_rule_count);
2126 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2127 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2128 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2129 RAISE OKL_API.G_EXCEPTION_ERROR;
2130 END IF;
2131 If l_rulv_tbl.FIRST is null Then
2132 x_name := Null;
2133 Else
2134 l_rulv_rec := l_rulv_tbl(1);
2135
2136 --bug# 3024752 :
2137 If okl_context.get_okc_organization_id is null then
2138 okl_context.set_okc_org_context(p_chr_id => l_rulv_rec.dnz_chr_id);
2139 End If;
2140
2141
2142 if p_segment_number = 1
2143 and l_rulv_rec.RULE_INFORMATION1 is not Null Then
2144 Get_rule_Information (p_api_version => p_api_version,
2145 p_init_msg_list => p_init_msg_list,
2146 p_rdf_code => p_rdf_code,
2147 p_appl_col_name => 'RULE_INFORMATION1',
2148 p_rule_info => l_rulv_rec.RULE_INFORMATION1,
2149 x_return_status => x_return_status,
2150 x_msg_count => x_msg_count,
2151 x_msg_data => x_msg_data,
2152 x_name => x_name,
2153 x_select => x_select);
2154 Elsif p_segment_number = 2
2155 and l_rulv_rec.RULE_INFORMATION2 is not null Then
2156 Get_rule_Information (p_api_version => p_api_version,
2157 p_init_msg_list => p_init_msg_list,
2158 p_rdf_code => p_rdf_code,
2159 p_appl_col_name => 'RULE_INFORMATION2',
2160 p_rule_info => l_rulv_rec.RULE_INFORMATION2,
2161 x_return_status => x_return_status,
2162 x_msg_count => x_msg_count,
2163 x_msg_data => x_msg_data,
2164 x_name => x_name,
2165 x_select => x_select);
2166 Elsif p_segment_number = 3
2167 and l_rulv_rec.RULE_INFORMATION3 is not null Then
2168 Get_rule_Information (p_api_version => p_api_version,
2169 p_init_msg_list => p_init_msg_list,
2170 p_rdf_code => p_rdf_code,
2171 p_appl_col_name => 'RULE_INFORMATION3',
2172 p_rule_info => l_rulv_rec.RULE_INFORMATION3,
2173 x_return_status => x_return_status,
2174 x_msg_count => x_msg_count,
2175 x_msg_data => x_msg_data,
2176 x_name => x_name,
2177 x_select => x_select);
2178 Elsif p_segment_number = 4
2179 and l_rulv_rec.RULE_INFORMATION4 is not null Then
2180 Get_rule_Information (p_api_version => p_api_version,
2181 p_init_msg_list => p_init_msg_list,
2182 p_rdf_code => p_rdf_code,
2183 p_appl_col_name => 'RULE_INFORMATION4',
2184 p_rule_info => l_rulv_rec.RULE_INFORMATION4,
2185 x_return_status => x_return_status,
2186 x_msg_count => x_msg_count,
2187 x_msg_data => x_msg_data,
2188 x_name => x_name,
2189 x_select => x_select);
2190 Elsif p_segment_number = 5
2191 and l_rulv_rec.RULE_INFORMATION5 is not null Then
2192 Get_rule_Information (p_api_version => p_api_version,
2193 p_init_msg_list => p_init_msg_list,
2194 p_rdf_code => p_rdf_code,
2195 p_appl_col_name => 'RULE_INFORMATION5',
2196 p_rule_info => l_rulv_rec.RULE_INFORMATION5,
2197 x_return_status => x_return_status,
2198 x_msg_count => x_msg_count,
2199 x_msg_data => x_msg_data,
2200 x_name => x_name,
2201 x_select => x_select);
2202 Elsif p_segment_number = 6
2203 and l_rulv_rec.RULE_INFORMATION6 is not null Then
2204 Get_rule_Information (p_api_version => p_api_version,
2205 p_init_msg_list => p_init_msg_list,
2206 p_rdf_code => p_rdf_code,
2207 p_appl_col_name => 'RULE_INFORMATION6',
2208 p_rule_info => l_rulv_rec.RULE_INFORMATION6,
2209 x_return_status => x_return_status,
2210 x_msg_count => x_msg_count,
2211 x_msg_data => x_msg_data,
2212 x_name => x_name,
2213 x_select => x_select);
2214 Elsif p_segment_number = 7
2215 and l_rulv_rec.RULE_INFORMATION7 is not null Then
2216 Get_rule_Information (p_api_version => p_api_version,
2217 p_init_msg_list => p_init_msg_list,
2218 p_rdf_code => p_rdf_code,
2219 p_appl_col_name => 'RULE_INFORMATION7',
2220 p_rule_info => l_rulv_rec.RULE_INFORMATION7,
2221 x_return_status => x_return_status,
2222 x_msg_count => x_msg_count,
2223 x_msg_data => x_msg_data,
2224 x_name => x_name,
2225 x_select => x_select);
2226 Elsif p_segment_number = 8
2227 and l_rulv_rec.RULE_INFORMATION8 is not null Then
2228 Get_rule_Information (p_api_version => p_api_version,
2229 p_init_msg_list => p_init_msg_list,
2230 p_rdf_code => p_rdf_code,
2231 p_appl_col_name => 'RULE_INFORMATION8',
2232 p_rule_info => l_rulv_rec.RULE_INFORMATION8,
2233 x_return_status => x_return_status,
2234 x_msg_count => x_msg_count,
2235 x_msg_data => x_msg_data,
2236 x_name => x_name,
2237 x_select => x_select);
2238 Elsif p_segment_number = 9
2239 and l_rulv_rec.RULE_INFORMATION9 is not null Then
2240 Get_rule_Information (p_api_version => p_api_version,
2241 p_init_msg_list => p_init_msg_list,
2242 p_rdf_code => p_rdf_code,
2243 p_appl_col_name => 'RULE_INFORMATION9',
2244 p_rule_info => l_rulv_rec.RULE_INFORMATION9,
2245 x_return_status => x_return_status,
2246 x_msg_count => x_msg_count,
2247 x_msg_data => x_msg_data,
2248 x_name => x_name,
2249 x_select => x_select);
2250 Elsif p_segment_number = 10
2251 and l_rulv_rec.RULE_INFORMATION10 is not null Then
2252 Get_rule_Information (p_api_version => p_api_version,
2253 p_init_msg_list => p_init_msg_list,
2254 p_rdf_code => p_rdf_code,
2255 p_appl_col_name => 'RULE_INFORMATION10',
2256 p_rule_info => l_rulv_rec.RULE_INFORMATION10,
2257 x_return_status => x_return_status,
2258 x_msg_count => x_msg_count,
2259 x_msg_data => x_msg_data,
2260 x_name => x_name,
2261 x_select => x_select);
2262 Elsif p_segment_number = 11
2263 and l_rulv_rec.RULE_INFORMATION11 is not Null Then
2264 Get_rule_Information (p_api_version => p_api_version,
2265 p_init_msg_list => p_init_msg_list,
2266 p_rdf_code => p_rdf_code,
2267 p_appl_col_name => 'RULE_INFORMATION11',
2268 p_rule_info => l_rulv_rec.RULE_INFORMATION11,
2269 x_return_status => x_return_status,
2270 x_msg_count => x_msg_count,
2271 x_msg_data => x_msg_data,
2272 x_name => x_name,
2273 x_select => x_select);
2274 Elsif p_segment_number = 12
2275 and l_rulv_rec.RULE_INFORMATION12 is not null Then
2276 Get_rule_Information (p_api_version => p_api_version,
2277 p_init_msg_list => p_init_msg_list,
2278 p_rdf_code => p_rdf_code,
2279 p_appl_col_name => 'RULE_INFORMATION12',
2280 p_rule_info => l_rulv_rec.RULE_INFORMATION12,
2281 x_return_status => x_return_status,
2282 x_msg_count => x_msg_count,
2283 x_msg_data => x_msg_data,
2284 x_name => x_name,
2285 x_select => x_select);
2286 Elsif p_segment_number = 13
2287 and l_rulv_rec.RULE_INFORMATION13 is not null Then
2288 Get_rule_Information (p_api_version => p_api_version,
2289 p_init_msg_list => p_init_msg_list,
2290 p_rdf_code => p_rdf_code,
2291 p_appl_col_name => 'RULE_INFORMATION13',
2292 p_rule_info => l_rulv_rec.RULE_INFORMATION13,
2293 x_return_status => x_return_status,
2294 x_msg_count => x_msg_count,
2295 x_msg_data => x_msg_data,
2296 x_name => x_name,
2297 x_select => x_select);
2298 Elsif p_segment_number = 14
2299 and l_rulv_rec.RULE_INFORMATION14 is not null Then
2300 Get_rule_Information (p_api_version => p_api_version,
2301 p_init_msg_list => p_init_msg_list,
2302 p_rdf_code => p_rdf_code,
2303 p_appl_col_name => 'RULE_INFORMATION14',
2304 p_rule_info => l_rulv_rec.RULE_INFORMATION14,
2305 x_return_status => x_return_status,
2306 x_msg_count => x_msg_count,
2307 x_msg_data => x_msg_data,
2308 x_name => x_name,
2309 x_select => x_select);
2310 Elsif p_segment_number = 15
2311 and l_rulv_rec.RULE_INFORMATION15 is not null Then
2312 Get_rule_Information (p_api_version => p_api_version,
2313 p_init_msg_list => p_init_msg_list,
2314 p_rdf_code => p_rdf_code,
2315 p_appl_col_name => 'RULE_INFORMATION15',
2316 p_rule_info => l_rulv_rec.RULE_INFORMATION15,
2317 x_return_status => x_return_status,
2318 x_msg_count => x_msg_count,
2319 x_msg_data => x_msg_data,
2320 x_name => x_name,
2321 x_select => x_select);
2322 Elsif p_segment_number = 16
2323 and l_rulv_rec.object1_id1 is not null Then
2324 Get_jtot_object(p_api_version => p_api_version,
2325 p_init_msg_list => p_init_msg_list,
2326 p_object_code => l_rulv_rec.jtot_object1_code,
2327 p_id1 => l_rulv_rec.object1_id1,
2328 p_id2 => l_rulv_rec.object1_id2,
2329 x_return_status => x_return_status,
2330 x_msg_count => x_msg_count,
2331 x_msg_data => x_msg_data,
2332 x_id1 => x_id1,
2333 x_id2 => x_id2,
2334 x_name => x_name,
2335 x_description => x_description,
2336 x_status => x_status,
2337 x_start_date => x_start_date,
2338 x_end_date => x_end_date,
2339 x_org_id => x_org_id,
2340 x_inv_org_id => x_inv_org_id,
2341 x_book_type_code => x_book_type_code,
2342 x_select => x_select);
2343 Elsif p_segment_number = 17
2344 and l_rulv_rec.object2_id1 is not null Then
2345 Get_jtot_object(p_api_version => p_api_version,
2346 p_init_msg_list => p_init_msg_list,
2347 p_object_code => l_rulv_rec.jtot_object2_code,
2348 p_id1 => l_rulv_rec.object2_id1,
2349 p_id2 => l_rulv_rec.object2_id2,
2350 x_return_status => x_return_status,
2351 x_msg_count => x_msg_count,
2352 x_msg_data => x_msg_data,
2353 x_id1 => x_id1,
2354 x_id2 => x_id2,
2355 x_name => x_name,
2356 x_description => x_description,
2357 x_status => x_status,
2358 x_start_date => x_start_date,
2359 x_end_date => x_end_date,
2360 x_org_id => x_org_id,
2361 x_inv_org_id => x_inv_org_id,
2362 x_book_type_code => x_book_type_code,
2363 x_select => x_select);
2364 Elsif p_segment_number = 18
2365 and l_rulv_rec.object3_id1 is not null Then
2366 Get_jtot_object(p_api_version => p_api_version,
2367 p_init_msg_list => p_init_msg_list,
2368 p_object_code => l_rulv_rec.jtot_object3_code,
2369 p_id1 => l_rulv_rec.object3_id1,
2370 p_id2 => l_rulv_rec.object3_id2,
2371 x_return_status => x_return_status,
2372 x_msg_count => x_msg_count,
2373 x_msg_data => x_msg_data,
2374 x_id1 => x_id1,
2375 x_id2 => x_id2,
2376 x_name => x_name,
2377 x_description => x_description,
2378 x_status => x_status,
2379 x_start_date => x_start_date,
2380 x_end_date => x_end_date,
2381 x_org_id => x_org_id,
2382 x_inv_org_id => x_inv_org_id,
2383 x_book_type_code => x_book_type_code,
2384 x_select => x_select);
2385 End If;
2386 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2387 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2388 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2389 RAISE OKL_API.G_EXCEPTION_ERROR;
2390 END IF;
2391
2392 End If;
2393 End If;
2394 --Call End Activity
2395 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
2396 x_msg_data => x_msg_data);
2397 EXCEPTION
2398 when OKL_API.G_EXCEPTION_ERROR then
2399 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2400 p_api_name => l_api_name,
2401 p_pkg_name => g_pkg_name,
2402 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
2403 x_msg_count => x_msg_count,
2404 x_msg_data => x_msg_data,
2405 p_api_type => g_api_type);
2406
2407 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
2408 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2409 p_api_name => l_api_name,
2410 p_pkg_name => g_pkg_name,
2411 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
2412 x_msg_count => x_msg_count,
2413 x_msg_data => x_msg_data,
2414 p_api_type => g_api_type);
2415
2416 when OTHERS then
2417 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2418 p_api_name => l_api_name,
2419 p_pkg_name => g_pkg_name,
2420 p_exc_name => 'OTHERS',
2421 x_msg_count => x_msg_count,
2422 x_msg_data => x_msg_data,
2423 p_api_type => g_api_type);
2424 End Get_Rule_Segment_Value;
2425 End OKL_RULE_APIS_PVT;