[Home] [Help]
PACKAGE BODY: APPS.OKL_RULE_APIS_PVT
Source
1 Package Body OKL_RULE_APIS_PVT As
2 /* $Header: OKLRRAPB.pls 120.5.12020000.2 2012/11/30 04:47:57 racheruv ship $ */
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 /* commenting the code below as the direct reference of dba_tab_columns is not allowed
876 --Cursor check_col_curs(p_table_name IN Varchar2,p_col_name IN Varchar2) is
877 --Select 'Y'
878 --From dba_tab_columns
879 --Where table_name = p_table_name
880 --And column_name = p_col_name
881 -----------------
882 --Bug# 3431854 :
883 -----------------
884 --And owner = USER;
885 */
886
887 Cursor check_col_curs(p_table_name IN Varchar2,p_col_name IN Varchar2) is
888 select 'Y'
889 from user_synonyms syn, dba_tab_columns col
890 where syn.synonym_name = p_table_name
891 and col.owner = syn.table_owner
892 and col.table_name = syn.table_name
893 and col.column_name = p_col_name
894 union
895 Select 'Y'
896 From user_tab_columns col
897 Where col.table_name = p_table_name
898 And column_name = p_col_name;
899
900 l_col_exists Varchar2(1) default 'N';
901 l_table_name Varchar2(30) default Null;
902 l_col_name Varchar2(30) default Null;
903
904 Type jtot_ref_curs_type is REF CURSOR;
905 jtot_ref_curs jtot_ref_curs_type;
906
907 Begin
908 --Bug# 3024752:
909 --If okc_context.get_okc_organization_id is null then
910 --okc_context.set_okc_org_context(204,204);
911 --End If;
912 --Call OKL_API.START_ACTIVITY
913 l_return_status := OKL_API.START_ACTIVITY( substr(l_api_name,1,26),
914 G_PKG_NAME,
915 p_init_msg_list,
916 l_api_version,
917 p_api_version,
918 '_PVT',
919 x_return_status);
920 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
921 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
922 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
923 RAISE OKL_API.G_EXCEPTION_ERROR;
924 END IF;
925
926 Open jtf_obj_curs;
927 Fetch jtf_obj_curs into l_from_table, l_from_clause, l_where_clause, l_order_by_clause;
928 If jtf_obj_curs%NotFound Then
929 --dbms_output.put_line('jtf object not found for object code "'||p_object_code||'"');
930 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
931 p_msg_name =>G_JTF_OBJECT_QUERY_FAILED,
932 p_token1 =>G_JTF_OBJECT_TOKEN ,
933 p_token1_value => p_object_code);
934 RAISE OKL_API.G_EXCEPTION_ERROR;
935 Else
936 -- Bug# 3838403 - Remove table alias from select clause to handle
937 -- objects not having table alias.
938 l_select_clause := 'SELECT ID1,ID2,NAME,DESCRIPTION';
939 --get okx view table name
940
941 -- Bug# 3838403 - Append space to l_from_table in instr call
942 -- to handle table names with no alias
943 l_table_name := upper(substr(l_from_table,1,instr(l_from_table||' ',' ',1)));
944
945 --Bug# 3431854 :
946 l_table_name := ltrim(rtrim(l_table_name,' '),' ');
947 --chek for presense of columns
948 -- as all columns may not exist in OKX View
949 for i in 1..6
950 loop
951 l_col_exists := 'N';
952 l_col_name := null;
953 If i = 1 Then
954 l_col_name := 'STATUS';
955 Elsif i = 2 Then
956 l_col_name := 'START_DATE_ACTIVE';
957 Elsif i = 3 Then
958 l_col_name := 'END_DATE_ACTIVE';
959 Elsif i = 4 Then
960 l_col_name := 'ORG_ID';
961 Elsif i = 5 Then
962 l_col_name := 'INV_ORG_ID';
963 Elsif i = 6 Then
964 l_col_name := 'BOOK_TYPE_CODE';
965 End If;
966 open check_col_curs(l_table_name,l_col_name);
967 Fetch check_col_curs into l_col_exists;
968 If check_col_curs%NotFound --l_col_exist is 'N' column does not exist
969 Then
970 If i in (1,2,3,6) Then
971 l_select_clause := l_select_clause||','||'null';
972 elsif i in (4,5) Then
973 l_select_clause := l_select_clause||','||'to_number(null)';
974 end if;
975 Else
976 l_select_clause := l_select_clause||','||l_col_name;
977 End If;
978 Close check_col_curs;
979 End Loop;
980 -- Add p_id1 and p_id2 to the where clause
981 If p_id1 is not null and p_id2 is not null Then
982
983 -- Bug# 3838403 - Remove table alias from where clause to handle
984 -- objects not having table alias.
985 If l_where_clause is null Then
986 l_where_clause := ' WHERE ID1 ='||''''||p_id1||''''||
987 ' AND ID2 ='||''''||p_id2||'''';
988 Else
989 l_where_clause := l_where_clause||
990 ' AND ID1 ='||''''||p_id1||''''||
991 ' AND ID2 ='||''''||p_id2||'''';
992 End If;
993 End If;
994 -- compose sql query for jtot object
995 l_query_string := ltrim(rtrim(l_select_clause,' '),' ')||' '||
996 ltrim(rtrim(l_from_clause,' '),' ')||' '||
997 ltrim(rtrim(l_where_clause,' '),' ')||' '||
998 ltrim(rtrim(l_order_by_clause,' '),' ');
999 --dbms_output.put_line(l_where_clause);
1000 x_select := l_query_string;
1001 --execute sql query to get values
1002 If p_id1 is not null and p_id2 is not null Then
1003 open jtot_ref_curs for l_query_string;
1004 Fetch jtot_ref_curs into x_id1,
1005 x_id2,
1006 x_name,
1007 x_description,
1008 x_status,
1009 x_start_date,
1010 x_end_date,
1011 x_org_id,
1012 x_inv_org_id,
1013 x_book_type_code;
1014 If jtot_ref_curs%notfound Then
1015 --dbms_output.put_line('Not able to fetch ref cursor record using jtot query string');
1016 null;--handle appropriate exception here
1017 End If;
1018 Close jtot_ref_curs;
1019 End If;
1020 End If;
1021 Close jtf_obj_curs;
1022 --Call End Activity
1023 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
1024 x_msg_data => x_msg_data);
1025 EXCEPTION
1026 when OKL_API.G_EXCEPTION_ERROR then
1027 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1028 p_api_name => l_api_name,
1029 p_pkg_name => g_pkg_name,
1030 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1031 x_msg_count => x_msg_count,
1032 x_msg_data => x_msg_data,
1033 p_api_type => g_api_type);
1034
1035 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
1036 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1037 p_api_name => l_api_name,
1038 p_pkg_name => g_pkg_name,
1039 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1040 x_msg_count => x_msg_count,
1041 x_msg_data => x_msg_data,
1042 p_api_type => g_api_type);
1043
1044 when OTHERS then
1045 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1046 p_api_name => l_api_name,
1047 p_pkg_name => g_pkg_name,
1048 p_exc_name => 'OTHERS',
1049 x_msg_count => x_msg_count,
1050 x_msg_data => x_msg_data,
1051 p_api_type => g_api_type);
1052
1053 End Get_jtot_object;
1054
1055 --Start of Comments
1056 --Procedure : Get_Rule_disp_value
1057 --Description : Fetches the displayed values of rule segments
1058 --End of Comments
1059 Procedure Get_Rule_disp_value (p_api_version IN NUMBER,
1060 p_init_msg_list IN VARCHAR2,
1061 p_rulv_rec IN Rulv_rec_type,
1062 x_return_status OUT NOCOPY VARCHAR2,
1063 x_msg_count OUT NOCOPY NUMBER,
1064 x_msg_data OUT NOCOPY VARCHAR2,
1065 x_rulv_disp_rec OUT NOCOPY rulv_disp_rec_type) is
1066
1067 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1068 l_api_name CONSTANT VARCHAR2(30) := 'GET_RULE_DISP_VALUE';
1069 l_api_version CONSTANT NUMBER := 1.0;
1070
1071 l_rulv_disp_rec rulv_disp_rec_type;
1072 l_name Varchar2(500) default null;
1073 l_description Varchar2(2000) default null;
1074 l_status Varchar2(30) default null;
1075 l_start_date date default null;
1076 l_end_date date default null;
1077 l_org_id Number;
1078 l_inv_org_id Number;
1079 l_book_type_code Varchar2(30) default null;
1080 l_select Varchar2(2000) default null;
1081 l_id1 Varchar2(40) default Null;
1082 l_id2 Varchar2(200) default Null;
1083
1084 begin
1085 --Call OKL_API.START_ACTIVITY
1086 l_return_status := OKL_API.START_ACTIVITY( substr(l_api_name,1,26),
1087 G_PKG_NAME,
1088 p_init_msg_list,
1089 l_api_version,
1090 p_api_version,
1091 '_PVT',
1092 x_return_status);
1093 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1094 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1095 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1096 RAISE OKL_API.G_EXCEPTION_ERROR;
1097 END IF;
1098
1099 l_rulv_disp_rec.id := p_rulv_rec.id;
1100 l_rulv_disp_rec.rdf_code := p_rulv_rec.rule_information_category;
1101
1102 If p_rulv_rec.jtot_object1_code is not null then
1103 l_id1 := null;
1104 l_id2 := null;
1105 l_name := null;
1106 l_description := null;
1107 l_status := null;
1108 l_start_date := null;
1109 l_end_date := null;
1110 l_org_id := to_number(null);
1111 l_inv_org_id := to_number(null);
1112 l_book_type_code := null;
1113 l_select := null;
1114
1115 Get_jtot_object(p_api_version => p_api_version,
1116 p_init_msg_list => p_init_msg_list,
1117 p_object_code => p_rulv_rec.jtot_object1_code,
1118 p_id1 => p_rulv_rec.object1_id1,
1119 p_id2 => p_rulv_rec.object1_id2,
1120 x_return_status => x_return_status,
1121 x_msg_count => x_msg_count,
1122 x_msg_data => x_msg_data,
1123 x_id1 => l_id1,
1124 x_id2 => l_id2,
1125 x_name => l_name,
1126 x_description => l_description,
1127 x_status => l_status,
1128 x_start_date => l_start_date,
1129 x_end_date => l_end_date,
1130 x_org_id => l_org_id,
1131 x_inv_org_id => l_inv_org_id,
1132 x_book_type_code => l_book_type_code,
1133 x_select => l_select);
1134 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1135 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1136 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1137 RAISE OKL_API.G_EXCEPTION_ERROR;
1138 END IF;
1139
1140 l_rulv_disp_rec.obj1_name := l_name;
1141 l_rulv_disp_rec.obj1_descr := l_description;
1142 l_rulv_disp_rec.obj1_status := l_status;
1143 l_rulv_disp_rec.obj1_start_date := l_start_date;
1144 l_rulv_disp_rec.obj1_end_date := l_end_date;
1145 l_rulv_disp_rec.obj1_org_id := l_org_id;
1146 l_rulv_disp_rec.obj1_inv_org_id := l_inv_org_id;
1147 l_rulv_disp_rec.obj1_book_type_code := l_book_type_code;
1148 l_rulv_disp_rec.obj1_select := l_select;
1149 --dbms_output.put_line('Name '||l_name);
1150 End If;
1151
1152 If p_rulv_rec.jtot_object2_code is not null then
1153 l_id1 := null;
1154 l_id2 := null;
1155 l_name := null;
1156 l_description := null;
1157 l_status := null;
1158 l_start_date := null;
1159 l_end_date := null;
1160 l_org_id := to_number(null);
1161 l_inv_org_id := to_number(null);
1162 l_book_type_code := null;
1163 l_select := null;
1164
1165 Get_jtot_object(p_api_version => p_api_version,
1166 p_init_msg_list => p_init_msg_list,
1167 p_object_code => p_rulv_rec.jtot_object2_code,
1168 p_id1 => p_rulv_rec.object2_id1,
1169 p_id2 => p_rulv_rec.object2_id2,
1170 x_return_status => x_return_status,
1171 x_msg_count => x_msg_count,
1172 x_msg_data => x_msg_data,
1173 x_id1 => l_id1,
1174 x_id2 => l_id2,
1175 x_name => l_name,
1176 x_description => l_description,
1177 x_status => l_status,
1178 x_start_date => l_start_date,
1179 x_end_date => l_end_date,
1180 x_org_id => l_org_id,
1181 x_inv_org_id => l_inv_org_id,
1182 x_book_type_code => l_book_type_code,
1183 x_select => l_select);
1184
1185 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1186 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1187 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1188 RAISE OKL_API.G_EXCEPTION_ERROR;
1189 END IF;
1190
1191 l_rulv_disp_rec.obj2_name := l_name;
1192 l_rulv_disp_rec.obj2_descr := l_description;
1193 l_rulv_disp_rec.obj2_status := l_status;
1194 l_rulv_disp_rec.obj2_start_date := l_start_date;
1195 l_rulv_disp_rec.obj2_end_date := l_end_date;
1196 l_rulv_disp_rec.obj2_org_id := l_org_id;
1197 l_rulv_disp_rec.obj2_inv_org_id := l_inv_org_id;
1198 l_rulv_disp_rec.obj2_book_type_code := l_book_type_code;
1199 l_rulv_disp_rec.obj2_select := l_select;
1200 --dbms_output.put_line('Name '||l_name);
1201 End If;
1202
1203 If p_rulv_rec.jtot_object3_code is not null then
1204 l_id1 := null;
1205 l_id2 := null;
1206 l_name := null;
1207 l_description := null;
1208 l_status := null;
1209 l_start_date := null;
1210 l_end_date := null;
1211 l_org_id := to_number(null);
1212 l_inv_org_id := to_number(null);
1213 l_book_type_code := null;
1214 l_select := null;
1215
1216 Get_jtot_object(p_api_version => p_api_version,
1217 p_init_msg_list => p_init_msg_list,
1218 p_object_code => p_rulv_rec.jtot_object3_code,
1219 p_id1 => p_rulv_rec.object3_id1,
1220 p_id2 => p_rulv_rec.object3_id2,
1221 x_return_status => x_return_status,
1222 x_msg_count => x_msg_count,
1223 x_msg_data => x_msg_data,
1224 x_id1 => l_id1,
1225 x_id2 => l_id2,
1226 x_name => l_name,
1227 x_description => l_description,
1228 x_status => l_status,
1229 x_start_date => l_start_date,
1230 x_end_date => l_end_date,
1231 x_org_id => l_org_id,
1232 x_inv_org_id => l_inv_org_id,
1233 x_book_type_code => l_book_type_code,
1234 x_select => l_select);
1235
1236 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1237 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1238 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1239 RAISE OKL_API.G_EXCEPTION_ERROR;
1240 END IF;
1241
1242 l_rulv_disp_rec.obj3_name := l_name;
1243 l_rulv_disp_rec.obj3_descr := l_description;
1244 l_rulv_disp_rec.obj3_status := l_status;
1245 l_rulv_disp_rec.obj3_start_date := l_start_date;
1246 l_rulv_disp_rec.obj3_end_date := l_end_date;
1247 l_rulv_disp_rec.obj3_org_id := l_org_id;
1248 l_rulv_disp_rec.obj3_inv_org_id := l_inv_org_id;
1249 l_rulv_disp_rec.obj3_book_type_code := l_book_type_code;
1250 l_rulv_disp_rec.obj3_select := l_select;
1251 --dbms_output.put_line('Name '||l_name);
1252 --all the jtots done now reinitialize the columns
1253 l_id1 := null;
1254 l_id2 := null;
1255 l_name := null;
1256 l_description := null;
1257 l_status := null;
1258 l_start_date := null;
1259 l_end_date := null;
1260 l_org_id := to_number(null);
1261 l_inv_org_id := to_number(null);
1262 l_book_type_code := null;
1263 l_select := null;
1264 End If;
1265
1266 If p_rulv_rec.rule_information1 is not null then
1267 l_name := null;
1268 l_select := null;
1269 Get_rule_Information (p_api_version => p_api_version,
1270 p_init_msg_list => p_init_msg_list,
1271 p_rdf_code => p_rulv_rec.rule_information_category,
1272 p_appl_col_name => 'RULE_INFORMATION1',
1273 p_rule_info => p_rulv_rec.rule_information1,
1274 x_return_status => x_return_status,
1275 x_msg_count => x_msg_count,
1276 x_msg_data => x_msg_data,
1277 x_name => l_name,
1278 x_select => l_select);
1279 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1280 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1281 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1282 RAISE OKL_API.G_EXCEPTION_ERROR;
1283 END IF;
1284
1285 l_rulv_disp_rec.rul_info1_name := l_name;
1286 l_rulv_disp_rec.rul_info1_select := l_select;
1287 End If;
1288
1289 If p_rulv_rec.rule_information2 is not null then
1290 l_name := null;
1291 l_select := null;
1292 Get_rule_Information (p_api_version => p_api_version,
1293 p_init_msg_list => p_init_msg_list,
1294 p_rdf_code => p_rulv_rec.rule_information_category,
1295 p_appl_col_name => 'RULE_INFORMATION2',
1296 p_rule_info => p_rulv_rec.rule_information2,
1297 x_return_status => x_return_status,
1298 x_msg_count => x_msg_count,
1299 x_msg_data => x_msg_data,
1300 x_name => l_name,
1301 x_select => l_select);
1302 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1303 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1304 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1305 RAISE OKL_API.G_EXCEPTION_ERROR;
1306 END IF;
1307
1308 l_rulv_disp_rec.rul_info2_name := l_name;
1309 l_rulv_disp_rec.rul_info2_select := l_select;
1310 End If;
1311
1312 If p_rulv_rec.rule_information3 is not null then
1313 l_name := null;
1314 l_select := null;
1315 Get_rule_Information (p_api_version => p_api_version,
1316 p_init_msg_list => p_init_msg_list,
1317 p_rdf_code => p_rulv_rec.rule_information_category,
1318 p_appl_col_name => 'RULE_INFORMATION3',
1319 p_rule_info => p_rulv_rec.rule_information3,
1320 x_return_status => x_return_status,
1321 x_msg_count => x_msg_count,
1322 x_msg_data => x_msg_data,
1323 x_name => l_name,
1324 x_select => l_select);
1325 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1326 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1327 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1328 RAISE OKL_API.G_EXCEPTION_ERROR;
1329 END IF;
1330
1331 l_rulv_disp_rec.rul_info3_name := l_name;
1332 l_rulv_disp_rec.rul_info3_select := l_select;
1333 End If;
1334 If p_rulv_rec.rule_information4 is not null then
1335 l_name := null;
1336 l_select := null;
1337 Get_rule_Information (p_api_version => p_api_version,
1338 p_init_msg_list => p_init_msg_list,
1339 p_rdf_code => p_rulv_rec.rule_information_category,
1340 p_appl_col_name => 'RULE_INFORMATION4',
1341 p_rule_info => p_rulv_rec.rule_information4,
1342 x_return_status => x_return_status,
1343 x_msg_count => x_msg_count,
1344 x_msg_data => x_msg_data,
1345 x_name => l_name,
1346 x_select => l_select);
1347 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1348 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1349 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1350 RAISE OKL_API.G_EXCEPTION_ERROR;
1351 END IF;
1352
1353 l_rulv_disp_rec.rul_info4_name := l_name;
1354 l_rulv_disp_rec.rul_info4_select := l_select;
1355 End If;
1356 If p_rulv_rec.rule_information5 is not null then
1357 l_name := null;
1358 l_select := null;
1359 Get_rule_Information (p_api_version => p_api_version,
1360 p_init_msg_list => p_init_msg_list,
1361 p_rdf_code => p_rulv_rec.rule_information_category,
1362 p_appl_col_name => 'RULE_INFORMATION5',
1363 p_rule_info => p_rulv_rec.rule_information5,
1364 x_return_status => x_return_status,
1365 x_msg_count => x_msg_count,
1366 x_msg_data => x_msg_data,
1367 x_name => l_name,
1368 x_select => l_select);
1369 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1370 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1371 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1372 RAISE OKL_API.G_EXCEPTION_ERROR;
1373 END IF;
1374
1375 l_rulv_disp_rec.rul_info5_name := l_name;
1376 l_rulv_disp_rec.rul_info5_select := l_select;
1377 End If;
1378 If p_rulv_rec.rule_information6 is not null then
1379 l_name := null;
1380 l_select := null;
1381 Get_rule_Information (p_api_version => p_api_version,
1382 p_init_msg_list => p_init_msg_list,
1383 p_rdf_code => p_rulv_rec.rule_information_category,
1384 p_appl_col_name => 'RULE_INFORMATION6',
1385 p_rule_info => p_rulv_rec.rule_information6,
1386 x_return_status => x_return_status,
1387 x_msg_count => x_msg_count,
1388 x_msg_data => x_msg_data,
1389 x_name => l_name,
1390 x_select => l_select);
1391 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1392 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1393 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1394 RAISE OKL_API.G_EXCEPTION_ERROR;
1395 END IF;
1396
1397 l_rulv_disp_rec.rul_info6_name := l_name;
1398 l_rulv_disp_rec.rul_info6_select := l_select;
1399 End If;
1400 If p_rulv_rec.rule_information7 is not null then
1401 l_name := null;
1402 l_select := null;
1403 Get_rule_Information (p_api_version => p_api_version,
1404 p_init_msg_list => p_init_msg_list,
1405 p_rdf_code => p_rulv_rec.rule_information_category,
1406 p_appl_col_name => 'RULE_INFORMATION7',
1407 p_rule_info => p_rulv_rec.rule_information7,
1408 x_return_status => x_return_status,
1409 x_msg_count => x_msg_count,
1410 x_msg_data => x_msg_data,
1411 x_name => l_name,
1412 x_select => l_select);
1413 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1414 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1415 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1416 RAISE OKL_API.G_EXCEPTION_ERROR;
1417 END IF;
1418
1419 l_rulv_disp_rec.rul_info7_name := l_name;
1420 l_rulv_disp_rec.rul_info7_select := l_select;
1421 End If;
1422 If p_rulv_rec.rule_information8 is not null then
1423 l_name := null;
1424 l_select := null;
1425 Get_rule_Information (p_api_version => p_api_version,
1426 p_init_msg_list => p_init_msg_list,
1427 p_rdf_code => p_rulv_rec.rule_information_category,
1428 p_appl_col_name => 'RULE_INFORMATION8',
1429 p_rule_info => p_rulv_rec.rule_information8,
1430 x_return_status => x_return_status,
1431 x_msg_count => x_msg_count,
1432 x_msg_data => x_msg_data,
1433 x_name => l_name,
1434 x_select => l_select);
1435 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1436 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1437 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1438 RAISE OKL_API.G_EXCEPTION_ERROR;
1439 END IF;
1440
1441 l_rulv_disp_rec.rul_info8_name := l_name;
1442 l_rulv_disp_rec.rul_info8_select := l_select;
1443 End If;
1444 If p_rulv_rec.rule_information9 is not null then
1445 l_name := null;
1446 l_select := null;
1447 Get_rule_Information (p_api_version => p_api_version,
1448 p_init_msg_list => p_init_msg_list,
1449 p_rdf_code => p_rulv_rec.rule_information_category,
1450 p_appl_col_name => 'RULE_INFORMATION9',
1451 p_rule_info => p_rulv_rec.rule_information9,
1452 x_return_status => x_return_status,
1453 x_msg_count => x_msg_count,
1454 x_msg_data => x_msg_data,
1455 x_name => l_name,
1456 x_select => l_select);
1457 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1458 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1459 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1460 RAISE OKL_API.G_EXCEPTION_ERROR;
1461 END IF;
1462
1463 l_rulv_disp_rec.rul_info9_name := l_name;
1464 l_rulv_disp_rec.rul_info9_select := l_select;
1465 End If;
1466 If p_rulv_rec.rule_information10 is not null then
1467 l_name := null;
1468 l_select := null;
1469 Get_rule_Information (p_api_version => p_api_version,
1470 p_init_msg_list => p_init_msg_list,
1471 p_rdf_code => p_rulv_rec.rule_information_category,
1472 p_appl_col_name => 'RULE_INFORMATION10',
1473 p_rule_info => p_rulv_rec.rule_information10,
1474 x_return_status => x_return_status,
1475 x_msg_count => x_msg_count,
1476 x_msg_data => x_msg_data,
1477 x_name => l_name,
1478 x_select => l_select);
1479 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1480 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1481 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1482 RAISE OKL_API.G_EXCEPTION_ERROR;
1483 END IF;
1484
1485 l_rulv_disp_rec.rul_info10_name := l_name;
1486 l_rulv_disp_rec.rul_info10_select := l_select;
1487 End If;
1488 If p_rulv_rec.rule_information11 is not null then
1489 l_name := null;
1490 l_select := null;
1491 Get_rule_Information (p_api_version => p_api_version,
1492 p_init_msg_list => p_init_msg_list,
1493 p_rdf_code => p_rulv_rec.rule_information_category,
1494 p_appl_col_name => 'RULE_INFORMATION11',
1495 p_rule_info => p_rulv_rec.rule_information11,
1496 x_return_status => x_return_status,
1497 x_msg_count => x_msg_count,
1498 x_msg_data => x_msg_data,
1499 x_name => l_name,
1500 x_select => l_select);
1501 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1502 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1503 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1504 RAISE OKL_API.G_EXCEPTION_ERROR;
1505 END IF;
1506
1507 l_rulv_disp_rec.rul_info11_name := l_name;
1508 l_rulv_disp_rec.rul_info11_select := l_select;
1509 End If;
1510 If p_rulv_rec.rule_information12 is not null then
1511 l_name := null;
1512 l_select := null;
1513 Get_rule_Information (p_api_version => p_api_version,
1514 p_init_msg_list => p_init_msg_list,
1515 p_rdf_code => p_rulv_rec.rule_information_category,
1516 p_appl_col_name => 'RULE_INFORMATION12',
1517 p_rule_info => p_rulv_rec.rule_information12,
1518 x_return_status => x_return_status,
1519 x_msg_count => x_msg_count,
1520 x_msg_data => x_msg_data,
1521 x_name => l_name,
1522 x_select => l_select);
1523 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1524 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1525 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1526 RAISE OKL_API.G_EXCEPTION_ERROR;
1527 END IF;
1528
1529 l_rulv_disp_rec.rul_info12_name := l_name;
1530 l_rulv_disp_rec.rul_info12_select := l_select;
1531
1532 End If;
1533 If p_rulv_rec.rule_information13 is not null then
1534 l_name := null;
1535 l_select := null;
1536 Get_rule_Information (p_api_version => p_api_version,
1537 p_init_msg_list => p_init_msg_list,
1538 p_rdf_code => p_rulv_rec.rule_information_category,
1539 p_appl_col_name => 'RULE_INFORMATION13',
1540 p_rule_info => p_rulv_rec.rule_information13,
1541 x_return_status => x_return_status,
1542 x_msg_count => x_msg_count,
1543 x_msg_data => x_msg_data,
1544 x_name => l_name,
1545 x_select => l_select);
1546 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1547 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1548 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1549 RAISE OKL_API.G_EXCEPTION_ERROR;
1550 END IF;
1551
1552 l_rulv_disp_rec.rul_info13_name := l_name;
1553 l_rulv_disp_rec.rul_info13_select := l_select;
1554 End If;
1555 If p_rulv_rec.rule_information14 is not null then
1556 l_name := null;
1557 l_select := null;
1558 Get_rule_Information (p_api_version => p_api_version,
1559 p_init_msg_list => p_init_msg_list,
1560 p_rdf_code => p_rulv_rec.rule_information_category,
1561 p_appl_col_name => 'RULE_INFORMATION14',
1562 p_rule_info => p_rulv_rec.rule_information14,
1563 x_return_status => x_return_status,
1564 x_msg_count => x_msg_count,
1565 x_msg_data => x_msg_data,
1566 x_name => l_name,
1567 x_select => l_select);
1568 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1569 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1570 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1571 RAISE OKL_API.G_EXCEPTION_ERROR;
1572 END IF;
1573
1574 l_rulv_disp_rec.rul_info14_name := l_name;
1575 l_rulv_disp_rec.rul_info14_select := l_select;
1576 End If;
1577 If p_rulv_rec.rule_information15 is not null then
1578 l_name := null;
1579 l_select := null;
1580 Get_rule_Information (p_api_version => p_api_version,
1581 p_init_msg_list => p_init_msg_list,
1582 p_rdf_code => p_rulv_rec.rule_information_category,
1583 p_appl_col_name => 'RULE_INFORMATION15',
1584 p_rule_info => p_rulv_rec.rule_information15,
1585 x_return_status => x_return_status,
1586 x_msg_count => x_msg_count,
1587 x_msg_data => x_msg_data,
1588 x_name => l_name,
1589 x_select => l_select);
1590 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1591 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1592 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1593 RAISE OKL_API.G_EXCEPTION_ERROR;
1594 END IF;
1595
1596 l_rulv_disp_rec.rul_info15_name := l_name;
1597 l_rulv_disp_rec.rul_info15_select := l_select;
1598 End If;
1599 x_rulv_disp_rec := l_rulv_disp_rec;
1600
1601 --Call End Activity
1602 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
1603 x_msg_data => x_msg_data);
1604 EXCEPTION
1605 when OKL_API.G_EXCEPTION_ERROR then
1606 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1607 p_api_name => l_api_name,
1608 p_pkg_name => g_pkg_name,
1609 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1610 x_msg_count => x_msg_count,
1611 x_msg_data => x_msg_data,
1612 p_api_type => g_api_type);
1613
1614 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
1615 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1616 p_api_name => l_api_name,
1617 p_pkg_name => g_pkg_name,
1618 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1619 x_msg_count => x_msg_count,
1620 x_msg_data => x_msg_data,
1621 p_api_type => g_api_type);
1622
1623 when OTHERS then
1624 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1625 p_api_name => l_api_name,
1626 p_pkg_name => g_pkg_name,
1627 p_exc_name => 'OTHERS',
1628 x_msg_count => x_msg_count,
1629 x_msg_data => x_msg_data,
1630 p_api_type => g_api_type);
1631
1632 End Get_Rule_Disp_Value;
1633 --Start of Comments
1634 --Procedure : Get_Rule_Segment_value
1635 --Description : Fetches the displayed value and select clauses of
1636 -- of specific rule segment.
1637 --Note : This API requires exact screen prompt label of the segment
1638 -- to be passed as p_rdf_name
1639 --End of Comments
1640 Procedure Get_rule_Segment_Value(p_api_version IN NUMBER,
1641 p_init_msg_list IN VARCHAR2,
1642 x_return_status OUT NOCOPY VARCHAR2,
1643 x_msg_count OUT NOCOPY NUMBER,
1644 x_msg_data OUT NOCOPY VARCHAR2,
1645 p_chr_id IN NUMBER,
1646 p_cle_id IN NUMBER,
1647 p_rgd_code IN VARCHAR2,
1648 p_rdf_code IN VARCHAR2,
1649 p_rdf_name IN VARCHAR2,
1650 x_id1 OUT NOCOPY VARCHAR2,
1651 x_id2 OUT NOCOPY VARCHAR2,
1652 x_name OUT NOCOPY VARCHAR2,
1653 x_description OUT NOCOPY VARCHAR2,
1654 x_status OUT NOCOPY VARCHAR2,
1655 x_start_date OUT NOCOPY DATE,
1656 x_end_date OUT NOCOPY DATE,
1657 x_org_id OUT NOCOPY NUMBER,
1658 x_inv_org_id OUT NOCOPY NUMBER,
1659 x_book_type_code OUT NOCOPY VARCHAR2,
1660 x_select OUT NOCOPY VARCHAR2) is
1661
1662 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1663 l_api_name CONSTANT VARCHAR2(30) := 'GET_RULE_SEGMENT_VALUE';
1664 l_api_version CONSTANT NUMBER := 1.0;
1665
1666 l_rgpv_tbl rgpv_tbl_type;
1667 l_rulv_tbl rulv_tbl_type;
1668 l_rgpv_rec rgpv_rec_type;
1669 l_rulv_rec rulv_rec_type;
1670
1671 l_rg_count NUMBER;
1672 l_rule_count NUMBER;
1673
1674 Cursor rule_dff_app_col_cur is
1675 -- select dflex.descriptive_flex_context_code
1676 -- , dflex.application_column_name
1677 -- from fnd_descr_flex_col_usage_vl dflex
1678 -- where dflex.application_id=510
1679 -- and dflex.descriptive_flexfield_name='OKC Rule Developer DF'
1680 -- and dflex.descriptive_flex_context_code = p_rdf_code
1681 -- and dflex.form_left_prompt = p_rdf_name
1682 --union
1683 --added for rule striping (once finalized remove the top portion of union)
1684 Select dflex.descriptive_flex_context_code
1685 , dflex.application_column_name
1686 from fnd_descr_flex_col_usage_vl dflex,
1687 okc_rule_defs_v rdefv
1688 where dflex.application_id = rdefv.application_id
1689 and dflex.descriptive_flexfield_name = rdefv.descriptive_flexfield_name
1690 and dflex.descriptive_flex_context_code = rdefv.rule_code
1691 and dflex.form_left_prompt = p_rdf_name
1692 and rdefv.rule_code = p_rdf_code
1693 -- order by 1;
1694 order by dflex.descriptive_flex_context_code;
1695
1696 rule_dff_app_col_rec rule_dff_app_col_cur%rowtype;
1697 Begin
1698 --Call OKL_API.START_ACTIVITY
1699 l_return_status := OKL_API.START_ACTIVITY( substr(l_api_name,1,26),
1700 G_PKG_NAME,
1701 p_init_msg_list,
1702 l_api_version,
1703 p_api_version,
1704 '_PVT',
1705 x_return_status);
1706 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1707 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1708 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1709 RAISE OKL_API.G_EXCEPTION_ERROR;
1710 END IF;
1711 --get rule group record for the rgd code
1712 Get_Contract_Rgs(p_api_version => p_api_version,
1713 p_init_msg_list => p_init_msg_list,
1714 p_chr_id => p_chr_id,
1715 p_cle_id => p_cle_id,
1716 p_rgd_code => p_rgd_code,
1717 x_return_status => x_return_status,
1718 x_msg_count => x_msg_count,
1719 x_msg_data => x_msg_data,
1720 x_rgpv_tbl => l_rgpv_tbl,
1721 x_rg_count => l_rg_count);
1722 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1723 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1724 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1725 RAISE OKL_API.G_EXCEPTION_ERROR;
1726 END IF;
1727 if l_rgpv_tbl.FIRST is null Then
1728 x_name := NULL;
1729 Else
1730 l_rgpv_rec := l_rgpv_tbl(1);
1731
1732 --get rule record for rdf_code
1733 Get_Contract_Rules(p_api_version => p_api_version,
1734 p_init_msg_list => p_init_msg_list,
1735 p_rgpv_rec => l_rgpv_rec,
1736 p_rdf_code => p_rdf_code,
1737 x_return_status => x_return_status,
1738 x_msg_count => x_msg_count,
1739 x_msg_data => x_msg_data,
1740 x_rulv_tbl => l_rulv_tbl,
1741 x_rule_count => l_rule_count);
1742 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1743 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1744 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1745 RAISE OKL_API.G_EXCEPTION_ERROR;
1746 END IF;
1747 If l_rulv_tbl.FIRST is null Then
1748 x_name := Null;
1749 Else
1750 l_rulv_rec := l_rulv_tbl(1);
1751
1752 --bug# 3024752 :
1753 If okl_context.get_okc_organization_id is null then
1754 okl_context.set_okc_org_context(p_chr_id => l_rulv_rec.dnz_chr_id);
1755 End If;
1756
1757 Open rule_dff_app_col_cur;
1758 Fetch rule_dff_app_col_cur into rule_dff_app_col_rec;
1759 If rule_dff_app_col_cur%NOTFOUND Then
1760 --dbms_output.put_line('failed in select from fnd_descr_flex_col_usages');
1761 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
1762 p_msg_name => G_DFF_FETCH_FAILED,
1763 p_token1 => G_APPLICATION_COL_TOKEN,
1764 p_token1_value => p_rdf_name,
1765 p_token2 => G_RULE_CODE_TOKEN,
1766 p_token2_value => p_rdf_code
1767 );
1768 RAISE OKL_API.G_EXCEPTION_ERROR;
1769 Elsif rule_dff_app_col_rec.application_column_name = 'RULE_INFORMATION1'
1770 and l_rulv_rec.RULE_INFORMATION1 is not Null Then
1771 Get_rule_Information (p_api_version => p_api_version,
1772 p_init_msg_list => p_init_msg_list,
1773 p_rdf_code => p_rdf_code,
1774 p_appl_col_name => rule_dff_app_col_rec.application_column_name,
1775 p_rule_info => l_rulv_rec.RULE_INFORMATION1,
1776 x_return_status => x_return_status,
1777 x_msg_count => x_msg_count,
1778 x_msg_data => x_msg_data,
1779 x_name => x_name,
1780 x_select => x_select);
1781 Elsif rule_dff_app_col_rec.application_column_name = 'RULE_INFORMATION2'
1782 and l_rulv_rec.RULE_INFORMATION2 is not null Then
1783 Get_rule_Information (p_api_version => p_api_version,
1784 p_init_msg_list => p_init_msg_list,
1785 p_rdf_code => p_rdf_code,
1786 p_appl_col_name => rule_dff_app_col_rec.application_column_name,
1787 p_rule_info => l_rulv_rec.RULE_INFORMATION2,
1788 x_return_status => x_return_status,
1789 x_msg_count => x_msg_count,
1790 x_msg_data => x_msg_data,
1791 x_name => x_name,
1792 x_select => x_select);
1793 Elsif rule_dff_app_col_rec.application_column_name = 'RULE_INFORMATION3'
1794 and l_rulv_rec.RULE_INFORMATION3 is not null Then
1795 Get_rule_Information (p_api_version => p_api_version,
1796 p_init_msg_list => p_init_msg_list,
1797 p_rdf_code => p_rdf_code,
1798 p_appl_col_name => rule_dff_app_col_rec.application_column_name,
1799 p_rule_info => l_rulv_rec.RULE_INFORMATION3,
1800 x_return_status => x_return_status,
1801 x_msg_count => x_msg_count,
1802 x_msg_data => x_msg_data,
1803 x_name => x_name,
1804 x_select => x_select);
1805 Elsif rule_dff_app_col_rec.application_column_name = 'RULE_INFORMATION4'
1806 and l_rulv_rec.RULE_INFORMATION4 is not null Then
1807 Get_rule_Information (p_api_version => p_api_version,
1808 p_init_msg_list => p_init_msg_list,
1809 p_rdf_code => p_rdf_code,
1810 p_appl_col_name => rule_dff_app_col_rec.application_column_name,
1811 p_rule_info => l_rulv_rec.RULE_INFORMATION4,
1812 x_return_status => x_return_status,
1813 x_msg_count => x_msg_count,
1814 x_msg_data => x_msg_data,
1815 x_name => x_name,
1816 x_select => x_select);
1817 Elsif rule_dff_app_col_rec.application_column_name = 'RULE_INFORMATION5'
1818 and l_rulv_rec.RULE_INFORMATION5 is not null Then
1819 Get_rule_Information (p_api_version => p_api_version,
1820 p_init_msg_list => p_init_msg_list,
1821 p_rdf_code => p_rdf_code,
1822 p_appl_col_name => rule_dff_app_col_rec.application_column_name,
1823 p_rule_info => l_rulv_rec.RULE_INFORMATION5,
1824 x_return_status => x_return_status,
1825 x_msg_count => x_msg_count,
1826 x_msg_data => x_msg_data,
1827 x_name => x_name,
1828 x_select => x_select);
1829 Elsif rule_dff_app_col_rec.application_column_name = 'RULE_INFORMATION6'
1830 and l_rulv_rec.RULE_INFORMATION6 is not null Then
1831 Get_rule_Information (p_api_version => p_api_version,
1832 p_init_msg_list => p_init_msg_list,
1833 p_rdf_code => p_rdf_code,
1834 p_appl_col_name => rule_dff_app_col_rec.application_column_name,
1835 p_rule_info => l_rulv_rec.RULE_INFORMATION6,
1836 x_return_status => x_return_status,
1837 x_msg_count => x_msg_count,
1838 x_msg_data => x_msg_data,
1839 x_name => x_name,
1840 x_select => x_select);
1841 Elsif rule_dff_app_col_rec.application_column_name = 'RULE_INFORMATION7'
1842 and l_rulv_rec.RULE_INFORMATION7 is not null Then
1843 Get_rule_Information (p_api_version => p_api_version,
1844 p_init_msg_list => p_init_msg_list,
1845 p_rdf_code => p_rdf_code,
1846 p_appl_col_name => rule_dff_app_col_rec.application_column_name,
1847 p_rule_info => l_rulv_rec.RULE_INFORMATION7,
1848 x_return_status => x_return_status,
1849 x_msg_count => x_msg_count,
1850 x_msg_data => x_msg_data,
1851 x_name => x_name,
1852 x_select => x_select);
1853 Elsif rule_dff_app_col_rec.application_column_name = 'RULE_INFORMATION8'
1854 and l_rulv_rec.RULE_INFORMATION8 is not null Then
1855 Get_rule_Information (p_api_version => p_api_version,
1856 p_init_msg_list => p_init_msg_list,
1857 p_rdf_code => p_rdf_code,
1858 p_appl_col_name => rule_dff_app_col_rec.application_column_name,
1859 p_rule_info => l_rulv_rec.RULE_INFORMATION8,
1860 x_return_status => x_return_status,
1861 x_msg_count => x_msg_count,
1862 x_msg_data => x_msg_data,
1863 x_name => x_name,
1864 x_select => x_select);
1865 Elsif rule_dff_app_col_rec.application_column_name = 'RULE_INFORMATION9'
1866 and l_rulv_rec.RULE_INFORMATION9 is not null Then
1867 Get_rule_Information (p_api_version => p_api_version,
1868 p_init_msg_list => p_init_msg_list,
1869 p_rdf_code => p_rdf_code,
1870 p_appl_col_name => rule_dff_app_col_rec.application_column_name,
1871 p_rule_info => l_rulv_rec.RULE_INFORMATION9,
1872 x_return_status => x_return_status,
1873 x_msg_count => x_msg_count,
1874 x_msg_data => x_msg_data,
1875 x_name => x_name,
1876 x_select => x_select);
1877 Elsif rule_dff_app_col_rec.application_column_name = 'RULE_INFORMATION10'
1878 and l_rulv_rec.RULE_INFORMATION10 is not null Then
1879 Get_rule_Information (p_api_version => p_api_version,
1880 p_init_msg_list => p_init_msg_list,
1881 p_rdf_code => p_rdf_code,
1882 p_appl_col_name => rule_dff_app_col_rec.application_column_name,
1883 p_rule_info => l_rulv_rec.RULE_INFORMATION10,
1884 x_return_status => x_return_status,
1885 x_msg_count => x_msg_count,
1886 x_msg_data => x_msg_data,
1887 x_name => x_name,
1888 x_select => x_select);
1889 Elsif rule_dff_app_col_rec.application_column_name = 'RULE_INFORMATION11'
1890 and l_rulv_rec.RULE_INFORMATION11 is not Null Then
1891 Get_rule_Information (p_api_version => p_api_version,
1892 p_init_msg_list => p_init_msg_list,
1893 p_rdf_code => p_rdf_code,
1894 p_appl_col_name => rule_dff_app_col_rec.application_column_name,
1895 p_rule_info => l_rulv_rec.RULE_INFORMATION11,
1896 x_return_status => x_return_status,
1897 x_msg_count => x_msg_count,
1898 x_msg_data => x_msg_data,
1899 x_name => x_name,
1900 x_select => x_select);
1901 Elsif rule_dff_app_col_rec.application_column_name = 'RULE_INFORMATION12'
1902 and l_rulv_rec.RULE_INFORMATION12 is not null Then
1903 Get_rule_Information (p_api_version => p_api_version,
1904 p_init_msg_list => p_init_msg_list,
1905 p_rdf_code => p_rdf_code,
1906 p_appl_col_name => rule_dff_app_col_rec.application_column_name,
1907 p_rule_info => l_rulv_rec.RULE_INFORMATION12,
1908 x_return_status => x_return_status,
1909 x_msg_count => x_msg_count,
1910 x_msg_data => x_msg_data,
1911 x_name => x_name,
1912 x_select => x_select);
1913 Elsif rule_dff_app_col_rec.application_column_name = 'RULE_INFORMATION13'
1914 and l_rulv_rec.RULE_INFORMATION13 is not null Then
1915 Get_rule_Information (p_api_version => p_api_version,
1916 p_init_msg_list => p_init_msg_list,
1917 p_rdf_code => p_rdf_code,
1918 p_appl_col_name => rule_dff_app_col_rec.application_column_name,
1919 p_rule_info => l_rulv_rec.RULE_INFORMATION13,
1920 x_return_status => x_return_status,
1921 x_msg_count => x_msg_count,
1922 x_msg_data => x_msg_data,
1923 x_name => x_name,
1924 x_select => x_select);
1925 Elsif rule_dff_app_col_rec.application_column_name = 'RULE_INFORMATION14'
1926 and l_rulv_rec.RULE_INFORMATION14 is not null Then
1927 Get_rule_Information (p_api_version => p_api_version,
1928 p_init_msg_list => p_init_msg_list,
1929 p_rdf_code => p_rdf_code,
1930 p_appl_col_name => rule_dff_app_col_rec.application_column_name,
1931 p_rule_info => l_rulv_rec.RULE_INFORMATION14,
1932 x_return_status => x_return_status,
1933 x_msg_count => x_msg_count,
1934 x_msg_data => x_msg_data,
1935 x_name => x_name,
1936 x_select => x_select);
1937 Elsif rule_dff_app_col_rec.application_column_name = 'RULE_INFORMATION15'
1938 and l_rulv_rec.RULE_INFORMATION15 is not null Then
1939 Get_rule_Information (p_api_version => p_api_version,
1940 p_init_msg_list => p_init_msg_list,
1941 p_rdf_code => p_rdf_code,
1942 p_appl_col_name => rule_dff_app_col_rec.application_column_name,
1943 p_rule_info => l_rulv_rec.RULE_INFORMATION15,
1944 x_return_status => x_return_status,
1945 x_msg_count => x_msg_count,
1946 x_msg_data => x_msg_data,
1947 x_name => x_name,
1948 x_select => x_select);
1949 Elsif rule_dff_app_col_rec.application_column_name = 'JTOT_OBJECT1_CODE'
1950 and l_rulv_rec.object1_id1 is not null Then
1951 Get_jtot_object(p_api_version => p_api_version,
1952 p_init_msg_list => p_init_msg_list,
1953 p_object_code => l_rulv_rec.jtot_object1_code,
1954 p_id1 => l_rulv_rec.object1_id1,
1955 p_id2 => l_rulv_rec.object1_id2,
1956 x_return_status => x_return_status,
1957 x_msg_count => x_msg_count,
1958 x_msg_data => x_msg_data,
1959 x_id1 => x_id1,
1960 x_id2 => x_id2,
1961 x_name => x_name,
1962 x_description => x_description,
1963 x_status => x_status,
1964 x_start_date => x_start_date,
1965 x_end_date => x_end_date,
1966 x_org_id => x_org_id,
1967 x_inv_org_id => x_inv_org_id,
1968 x_book_type_code => x_book_type_code,
1969 x_select => x_select);
1970 Elsif rule_dff_app_col_rec.application_column_name = 'JTOT_OBJECT2_CODE'
1971 and l_rulv_rec.object2_id1 is not null Then
1972 Get_jtot_object(p_api_version => p_api_version,
1973 p_init_msg_list => p_init_msg_list,
1974 p_object_code => l_rulv_rec.jtot_object2_code,
1975 p_id1 => l_rulv_rec.object2_id1,
1976 p_id2 => l_rulv_rec.object2_id2,
1977 x_return_status => x_return_status,
1978 x_msg_count => x_msg_count,
1979 x_msg_data => x_msg_data,
1980 x_id1 => x_id1,
1981 x_id2 => x_id2,
1982 x_name => x_name,
1983 x_description => x_description,
1984 x_status => x_status,
1985 x_start_date => x_start_date,
1986 x_end_date => x_end_date,
1987 x_org_id => x_org_id,
1988 x_inv_org_id => x_inv_org_id,
1989 x_book_type_code => x_book_type_code,
1990 x_select => x_select);
1991 Elsif rule_dff_app_col_rec.application_column_name = 'JTOT_OBJECT3_CODE'
1992 and l_rulv_rec.object3_id1 is not null Then
1993 Get_jtot_object(p_api_version => p_api_version,
1994 p_init_msg_list => p_init_msg_list,
1995 p_object_code => l_rulv_rec.jtot_object3_code,
1996 p_id1 => l_rulv_rec.object3_id1,
1997 p_id2 => l_rulv_rec.object3_id2,
1998 x_return_status => x_return_status,
1999 x_msg_count => x_msg_count,
2000 x_msg_data => x_msg_data,
2001 x_id1 => x_id1,
2002 x_id2 => x_id2,
2003 x_name => x_name,
2004 x_description => x_description,
2005 x_status => x_status,
2006 x_start_date => x_start_date,
2007 x_end_date => x_end_date,
2008 x_org_id => x_org_id,
2009 x_inv_org_id => x_inv_org_id,
2010 x_book_type_code => x_book_type_code,
2011 x_select => x_select);
2012 End If;
2013 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2014 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2015 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2016 RAISE OKL_API.G_EXCEPTION_ERROR;
2017 END IF;
2018 Close rule_dff_app_col_cur;
2019 End If;
2020 End If;
2021 --Call End Activity
2022 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
2023 x_msg_data => x_msg_data);
2024 EXCEPTION
2025 when OKL_API.G_EXCEPTION_ERROR then
2026 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2027 p_api_name => l_api_name,
2028 p_pkg_name => g_pkg_name,
2029 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
2030 x_msg_count => x_msg_count,
2031 x_msg_data => x_msg_data,
2032 p_api_type => g_api_type);
2033
2034 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
2035 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2036 p_api_name => l_api_name,
2037 p_pkg_name => g_pkg_name,
2038 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
2039 x_msg_count => x_msg_count,
2040 x_msg_data => x_msg_data,
2041 p_api_type => g_api_type);
2042
2043 when OTHERS then
2044 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2045 p_api_name => l_api_name,
2046 p_pkg_name => g_pkg_name,
2047 p_exc_name => 'OTHERS',
2048 x_msg_count => x_msg_count,
2049 x_msg_data => x_msg_data,
2050 p_api_type => g_api_type);
2051 End Get_Rule_Segment_Value;
2052 --Start of Comments
2053 --Bug#2525946 : overloaded to take rule segment numbers as input
2054 --Procedure : Get_Rule_Segment_value
2055 --Description : Fetches the displayed value and select clauses of
2056 -- of specific rule segment.
2057 --Note : This API requires segment number
2058 -- Segment number 1 to 15 are mapped to RULE_INFORMATION1 to
2059 -- RULE_INFORMATION15. Segment Numbers 16, 17 and 18 are mapped
2060 -- to jtot_object1, jtot_object2 and jtot_object3 respectively
2061 --End of Comments
2062 Procedure Get_rule_Segment_Value(p_api_version IN NUMBER,
2063 p_init_msg_list IN VARCHAR2,
2064 x_return_status OUT NOCOPY VARCHAR2,
2065 x_msg_count OUT NOCOPY NUMBER,
2066 x_msg_data OUT NOCOPY VARCHAR2,
2067 p_chr_id IN NUMBER,
2068 p_cle_id IN NUMBER,
2069 p_rgd_code IN VARCHAR2,
2070 p_rdf_code IN VARCHAR2,
2071 p_segment_number IN NUMBER,
2072 x_id1 OUT NOCOPY VARCHAR2,
2073 x_id2 OUT NOCOPY VARCHAR2,
2074 x_name OUT NOCOPY VARCHAR2,
2075 x_description OUT NOCOPY VARCHAR2,
2076 x_status OUT NOCOPY VARCHAR2,
2077 x_start_date OUT NOCOPY DATE,
2078 x_end_date OUT NOCOPY DATE,
2079 x_org_id OUT NOCOPY NUMBER,
2080 x_inv_org_id OUT NOCOPY NUMBER,
2081 x_book_type_code OUT NOCOPY VARCHAR2,
2082 x_select OUT NOCOPY VARCHAR2) is
2083
2084 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2085 l_api_name CONSTANT VARCHAR2(30) := 'GET_RULE_SEGMENT_VALUE';
2086 l_api_version CONSTANT NUMBER := 1.0;
2087
2088 l_rgpv_tbl rgpv_tbl_type;
2089 l_rulv_tbl rulv_tbl_type;
2090 l_rgpv_rec rgpv_rec_type;
2091 l_rulv_rec rulv_rec_type;
2092
2093 l_rg_count NUMBER;
2094 l_rule_count NUMBER;
2095
2096 Begin
2097 --Call OKL_API.START_ACTIVITY
2098 l_return_status := OKL_API.START_ACTIVITY( substr(l_api_name,1,26),
2099 G_PKG_NAME,
2100 p_init_msg_list,
2101 l_api_version,
2102 p_api_version,
2103 '_PVT',
2104 x_return_status);
2105 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2106 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2107 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2108 RAISE OKL_API.G_EXCEPTION_ERROR;
2109 END IF;
2110 --get rule group record for the rgd code
2111 Get_Contract_Rgs(p_api_version => p_api_version,
2112 p_init_msg_list => p_init_msg_list,
2113 p_chr_id => p_chr_id,
2114 p_cle_id => p_cle_id,
2115 p_rgd_code => p_rgd_code,
2116 x_return_status => x_return_status,
2117 x_msg_count => x_msg_count,
2118 x_msg_data => x_msg_data,
2119 x_rgpv_tbl => l_rgpv_tbl,
2120 x_rg_count => l_rg_count);
2121 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2122 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2123 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2124 RAISE OKL_API.G_EXCEPTION_ERROR;
2125 END IF;
2126 if l_rgpv_tbl.FIRST is null Then
2127 x_name := NULL;
2128 Else
2129 l_rgpv_rec := l_rgpv_tbl(1);
2130
2131 --get rule record for rdf_code
2132 Get_Contract_Rules(p_api_version => p_api_version,
2133 p_init_msg_list => p_init_msg_list,
2134 p_rgpv_rec => l_rgpv_rec,
2135 p_rdf_code => p_rdf_code,
2136 x_return_status => x_return_status,
2137 x_msg_count => x_msg_count,
2138 x_msg_data => x_msg_data,
2139 x_rulv_tbl => l_rulv_tbl,
2140 x_rule_count => l_rule_count);
2141 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2142 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2143 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2144 RAISE OKL_API.G_EXCEPTION_ERROR;
2145 END IF;
2146 If l_rulv_tbl.FIRST is null Then
2147 x_name := Null;
2148 Else
2149 l_rulv_rec := l_rulv_tbl(1);
2150
2151 --bug# 3024752 :
2152 If okl_context.get_okc_organization_id is null then
2153 okl_context.set_okc_org_context(p_chr_id => l_rulv_rec.dnz_chr_id);
2154 End If;
2155
2156
2157 if p_segment_number = 1
2158 and l_rulv_rec.RULE_INFORMATION1 is not Null Then
2159 Get_rule_Information (p_api_version => p_api_version,
2160 p_init_msg_list => p_init_msg_list,
2161 p_rdf_code => p_rdf_code,
2162 p_appl_col_name => 'RULE_INFORMATION1',
2163 p_rule_info => l_rulv_rec.RULE_INFORMATION1,
2164 x_return_status => x_return_status,
2165 x_msg_count => x_msg_count,
2166 x_msg_data => x_msg_data,
2167 x_name => x_name,
2168 x_select => x_select);
2169 Elsif p_segment_number = 2
2170 and l_rulv_rec.RULE_INFORMATION2 is not null Then
2171 Get_rule_Information (p_api_version => p_api_version,
2172 p_init_msg_list => p_init_msg_list,
2173 p_rdf_code => p_rdf_code,
2174 p_appl_col_name => 'RULE_INFORMATION2',
2175 p_rule_info => l_rulv_rec.RULE_INFORMATION2,
2176 x_return_status => x_return_status,
2177 x_msg_count => x_msg_count,
2178 x_msg_data => x_msg_data,
2179 x_name => x_name,
2180 x_select => x_select);
2181 Elsif p_segment_number = 3
2182 and l_rulv_rec.RULE_INFORMATION3 is not null Then
2183 Get_rule_Information (p_api_version => p_api_version,
2184 p_init_msg_list => p_init_msg_list,
2185 p_rdf_code => p_rdf_code,
2186 p_appl_col_name => 'RULE_INFORMATION3',
2187 p_rule_info => l_rulv_rec.RULE_INFORMATION3,
2188 x_return_status => x_return_status,
2189 x_msg_count => x_msg_count,
2190 x_msg_data => x_msg_data,
2191 x_name => x_name,
2192 x_select => x_select);
2193 Elsif p_segment_number = 4
2194 and l_rulv_rec.RULE_INFORMATION4 is not null Then
2195 Get_rule_Information (p_api_version => p_api_version,
2196 p_init_msg_list => p_init_msg_list,
2197 p_rdf_code => p_rdf_code,
2198 p_appl_col_name => 'RULE_INFORMATION4',
2199 p_rule_info => l_rulv_rec.RULE_INFORMATION4,
2200 x_return_status => x_return_status,
2201 x_msg_count => x_msg_count,
2202 x_msg_data => x_msg_data,
2203 x_name => x_name,
2204 x_select => x_select);
2205 Elsif p_segment_number = 5
2206 and l_rulv_rec.RULE_INFORMATION5 is not null Then
2207 Get_rule_Information (p_api_version => p_api_version,
2208 p_init_msg_list => p_init_msg_list,
2209 p_rdf_code => p_rdf_code,
2210 p_appl_col_name => 'RULE_INFORMATION5',
2211 p_rule_info => l_rulv_rec.RULE_INFORMATION5,
2212 x_return_status => x_return_status,
2213 x_msg_count => x_msg_count,
2214 x_msg_data => x_msg_data,
2215 x_name => x_name,
2216 x_select => x_select);
2217 Elsif p_segment_number = 6
2218 and l_rulv_rec.RULE_INFORMATION6 is not null Then
2219 Get_rule_Information (p_api_version => p_api_version,
2220 p_init_msg_list => p_init_msg_list,
2221 p_rdf_code => p_rdf_code,
2222 p_appl_col_name => 'RULE_INFORMATION6',
2223 p_rule_info => l_rulv_rec.RULE_INFORMATION6,
2224 x_return_status => x_return_status,
2225 x_msg_count => x_msg_count,
2226 x_msg_data => x_msg_data,
2227 x_name => x_name,
2228 x_select => x_select);
2229 Elsif p_segment_number = 7
2230 and l_rulv_rec.RULE_INFORMATION7 is not null Then
2231 Get_rule_Information (p_api_version => p_api_version,
2232 p_init_msg_list => p_init_msg_list,
2233 p_rdf_code => p_rdf_code,
2234 p_appl_col_name => 'RULE_INFORMATION7',
2235 p_rule_info => l_rulv_rec.RULE_INFORMATION7,
2236 x_return_status => x_return_status,
2237 x_msg_count => x_msg_count,
2238 x_msg_data => x_msg_data,
2239 x_name => x_name,
2240 x_select => x_select);
2241 Elsif p_segment_number = 8
2242 and l_rulv_rec.RULE_INFORMATION8 is not null Then
2243 Get_rule_Information (p_api_version => p_api_version,
2244 p_init_msg_list => p_init_msg_list,
2245 p_rdf_code => p_rdf_code,
2246 p_appl_col_name => 'RULE_INFORMATION8',
2247 p_rule_info => l_rulv_rec.RULE_INFORMATION8,
2248 x_return_status => x_return_status,
2249 x_msg_count => x_msg_count,
2250 x_msg_data => x_msg_data,
2251 x_name => x_name,
2252 x_select => x_select);
2253 Elsif p_segment_number = 9
2254 and l_rulv_rec.RULE_INFORMATION9 is not null Then
2255 Get_rule_Information (p_api_version => p_api_version,
2256 p_init_msg_list => p_init_msg_list,
2257 p_rdf_code => p_rdf_code,
2258 p_appl_col_name => 'RULE_INFORMATION9',
2259 p_rule_info => l_rulv_rec.RULE_INFORMATION9,
2260 x_return_status => x_return_status,
2261 x_msg_count => x_msg_count,
2262 x_msg_data => x_msg_data,
2263 x_name => x_name,
2264 x_select => x_select);
2265 Elsif p_segment_number = 10
2266 and l_rulv_rec.RULE_INFORMATION10 is not null Then
2267 Get_rule_Information (p_api_version => p_api_version,
2268 p_init_msg_list => p_init_msg_list,
2269 p_rdf_code => p_rdf_code,
2270 p_appl_col_name => 'RULE_INFORMATION10',
2271 p_rule_info => l_rulv_rec.RULE_INFORMATION10,
2272 x_return_status => x_return_status,
2273 x_msg_count => x_msg_count,
2274 x_msg_data => x_msg_data,
2275 x_name => x_name,
2276 x_select => x_select);
2277 Elsif p_segment_number = 11
2278 and l_rulv_rec.RULE_INFORMATION11 is not Null Then
2279 Get_rule_Information (p_api_version => p_api_version,
2280 p_init_msg_list => p_init_msg_list,
2281 p_rdf_code => p_rdf_code,
2282 p_appl_col_name => 'RULE_INFORMATION11',
2283 p_rule_info => l_rulv_rec.RULE_INFORMATION11,
2284 x_return_status => x_return_status,
2285 x_msg_count => x_msg_count,
2286 x_msg_data => x_msg_data,
2287 x_name => x_name,
2288 x_select => x_select);
2289 Elsif p_segment_number = 12
2290 and l_rulv_rec.RULE_INFORMATION12 is not null Then
2291 Get_rule_Information (p_api_version => p_api_version,
2292 p_init_msg_list => p_init_msg_list,
2293 p_rdf_code => p_rdf_code,
2294 p_appl_col_name => 'RULE_INFORMATION12',
2295 p_rule_info => l_rulv_rec.RULE_INFORMATION12,
2296 x_return_status => x_return_status,
2297 x_msg_count => x_msg_count,
2298 x_msg_data => x_msg_data,
2299 x_name => x_name,
2300 x_select => x_select);
2301 Elsif p_segment_number = 13
2302 and l_rulv_rec.RULE_INFORMATION13 is not null Then
2303 Get_rule_Information (p_api_version => p_api_version,
2304 p_init_msg_list => p_init_msg_list,
2305 p_rdf_code => p_rdf_code,
2306 p_appl_col_name => 'RULE_INFORMATION13',
2307 p_rule_info => l_rulv_rec.RULE_INFORMATION13,
2308 x_return_status => x_return_status,
2309 x_msg_count => x_msg_count,
2310 x_msg_data => x_msg_data,
2311 x_name => x_name,
2312 x_select => x_select);
2313 Elsif p_segment_number = 14
2314 and l_rulv_rec.RULE_INFORMATION14 is not null Then
2315 Get_rule_Information (p_api_version => p_api_version,
2316 p_init_msg_list => p_init_msg_list,
2317 p_rdf_code => p_rdf_code,
2318 p_appl_col_name => 'RULE_INFORMATION14',
2319 p_rule_info => l_rulv_rec.RULE_INFORMATION14,
2320 x_return_status => x_return_status,
2321 x_msg_count => x_msg_count,
2322 x_msg_data => x_msg_data,
2323 x_name => x_name,
2324 x_select => x_select);
2325 Elsif p_segment_number = 15
2326 and l_rulv_rec.RULE_INFORMATION15 is not null Then
2327 Get_rule_Information (p_api_version => p_api_version,
2328 p_init_msg_list => p_init_msg_list,
2329 p_rdf_code => p_rdf_code,
2330 p_appl_col_name => 'RULE_INFORMATION15',
2331 p_rule_info => l_rulv_rec.RULE_INFORMATION15,
2332 x_return_status => x_return_status,
2333 x_msg_count => x_msg_count,
2334 x_msg_data => x_msg_data,
2335 x_name => x_name,
2336 x_select => x_select);
2337 Elsif p_segment_number = 16
2338 and l_rulv_rec.object1_id1 is not null Then
2339 Get_jtot_object(p_api_version => p_api_version,
2340 p_init_msg_list => p_init_msg_list,
2341 p_object_code => l_rulv_rec.jtot_object1_code,
2342 p_id1 => l_rulv_rec.object1_id1,
2343 p_id2 => l_rulv_rec.object1_id2,
2344 x_return_status => x_return_status,
2345 x_msg_count => x_msg_count,
2346 x_msg_data => x_msg_data,
2347 x_id1 => x_id1,
2348 x_id2 => x_id2,
2349 x_name => x_name,
2350 x_description => x_description,
2351 x_status => x_status,
2352 x_start_date => x_start_date,
2353 x_end_date => x_end_date,
2354 x_org_id => x_org_id,
2355 x_inv_org_id => x_inv_org_id,
2356 x_book_type_code => x_book_type_code,
2357 x_select => x_select);
2358 Elsif p_segment_number = 17
2359 and l_rulv_rec.object2_id1 is not null Then
2360 Get_jtot_object(p_api_version => p_api_version,
2361 p_init_msg_list => p_init_msg_list,
2362 p_object_code => l_rulv_rec.jtot_object2_code,
2363 p_id1 => l_rulv_rec.object2_id1,
2364 p_id2 => l_rulv_rec.object2_id2,
2365 x_return_status => x_return_status,
2366 x_msg_count => x_msg_count,
2367 x_msg_data => x_msg_data,
2368 x_id1 => x_id1,
2369 x_id2 => x_id2,
2370 x_name => x_name,
2371 x_description => x_description,
2372 x_status => x_status,
2373 x_start_date => x_start_date,
2374 x_end_date => x_end_date,
2375 x_org_id => x_org_id,
2376 x_inv_org_id => x_inv_org_id,
2377 x_book_type_code => x_book_type_code,
2378 x_select => x_select);
2379 Elsif p_segment_number = 18
2380 and l_rulv_rec.object3_id1 is not null Then
2381 Get_jtot_object(p_api_version => p_api_version,
2382 p_init_msg_list => p_init_msg_list,
2383 p_object_code => l_rulv_rec.jtot_object3_code,
2384 p_id1 => l_rulv_rec.object3_id1,
2385 p_id2 => l_rulv_rec.object3_id2,
2386 x_return_status => x_return_status,
2387 x_msg_count => x_msg_count,
2388 x_msg_data => x_msg_data,
2389 x_id1 => x_id1,
2390 x_id2 => x_id2,
2391 x_name => x_name,
2392 x_description => x_description,
2393 x_status => x_status,
2394 x_start_date => x_start_date,
2395 x_end_date => x_end_date,
2396 x_org_id => x_org_id,
2397 x_inv_org_id => x_inv_org_id,
2398 x_book_type_code => x_book_type_code,
2399 x_select => x_select);
2400 End If;
2401 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2402 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2403 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2404 RAISE OKL_API.G_EXCEPTION_ERROR;
2405 END IF;
2406
2407 End If;
2408 End If;
2409 --Call End Activity
2410 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
2411 x_msg_data => x_msg_data);
2412 EXCEPTION
2413 when OKL_API.G_EXCEPTION_ERROR then
2414 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2415 p_api_name => l_api_name,
2416 p_pkg_name => g_pkg_name,
2417 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
2418 x_msg_count => x_msg_count,
2419 x_msg_data => x_msg_data,
2420 p_api_type => g_api_type);
2421
2422 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
2423 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2424 p_api_name => l_api_name,
2425 p_pkg_name => g_pkg_name,
2426 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
2427 x_msg_count => x_msg_count,
2428 x_msg_data => x_msg_data,
2429 p_api_type => g_api_type);
2430
2431 when OTHERS then
2432 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2433 p_api_name => l_api_name,
2434 p_pkg_name => g_pkg_name,
2435 p_exc_name => 'OTHERS',
2436 x_msg_count => x_msg_count,
2437 x_msg_data => x_msg_data,
2438 p_api_type => g_api_type);
2439 End Get_Rule_Segment_Value;
2440 End OKL_RULE_APIS_PVT;