[Home] [Help]
PACKAGE BODY: APPS.OKL_JTOT_EXTRACT
Source
1 Package Body OKL_JTOT_EXTRACT as
2 /* $Header: OKLRJEXB.pls 120.4 2006/09/22 12:04:36 zrehman noship $ */
3 --------------------------------------------------------------------------------
4 --GLOBAL MESSAGE VARIABLES
5 --------------------------------------------------------------------------------
6 G_JTF_OBJECT_QUERY_FAILED CONSTANT Varchar2(200) := 'OKL_LLA_JTF_OBJ_QUERY';
7 G_RLE_CODE_TOKEN CONSTANT Varchar2(30) := 'JTOT_OBJECT_CODE';
8 G_MISS_CHR_CLE CONSTANT Varchar2(200) := 'OKL_LLA_MISSING_PARMETERS';
9 G_MISS_PARA_TOKEN CONSTANT Varchar2(30) := 'P1';
10 G_UNABLE_TO_FIND_PARTY_ROLE CONSTANT Varchar2(200) := 'OKL_LLA_JTF_OBJ_QUERY_FAIL';
11 G_PARTY_ROLE_TOKEN CONSTANT Varchar2(30) := 'ENTITY';
12 G_PARTY_ROLE_CODE_TOKEN CONSTANT Varchar2(30) := 'ENTITY_CODE';
13 G_MISSING_CONTRACT CONSTANT Varchar2(200) := 'OKL_LLA_CONTRACT_NOT_FOUND';
14 G_CONTRACT_ID_TOKEN CONSTANT Varchar2(30) := 'CONTRACT_ID';
15
16
17 --Added by kthiruva 23-Sep-2003 Bug No.3156265
18
19 --For Object Code 'OKX_PARTY'
20 CURSOR okx_party_csr(p_name VARCHAR2 , p_id1 VARCHAR2 , p_id2 VARCHAR2) IS
21 SELECT prv.id1,
22 prv.id2,
23 prv.name,
24 prv.description
25 FROM okx_parties_v prv
26 WHERE prv.name = NVL(p_name,prv.name)
27 AND prv.id1 = NVL(p_id1,prv.id1)
28 AND prv.id2 = NVL(p_id2,prv.id2)
29 ORDER BY prv.name;
30
31 --For Object Code 'OKX_OPERUNIT'
32 CURSOR okx_operunit_csr(p_name VARCHAR2 , p_id1 VARCHAR2 , p_id2 VARCHAR2) IS
33 SELECT ord.id1,
34 ord.id2,
35 ord.name,
36 ord.description
37 FROM okx_organization_defs_v ord
38 WHERE ord.organization_type = 'OPERATING_UNIT'
39 AND ord.information_type = 'Operating Unit Information'
40 AND ord.name = NVL(p_name,ord.name)
41 AND ord.id1 = NVL(p_id1,ord.id1)
42 AND ord.id2 = NVL(p_id2,ord.id2)
43 ORDER BY ord.NAME;
44
45
46 --For Object Code 'OKX_VENDOR'
47 CURSOR okx_vendor_csr(p_name VARCHAR2 , p_id1 VARCHAR2 , p_id2 VARCHAR2) IS
48 SELECT vev.id1,
49 vev.id2,
50 vev.name,
51 vev.description
52 FROM okx_vendors_v vev
53 WHERE vev.name = NVL(p_name,vev.name)
54 AND vev.id1 = NVL(p_id1,vev.id1)
55 AND vev.id2 = NVL(p_id2,vev.id2)
56 ORDER BY vev.NAME;
57
58
59
60
61
62 --Start of Comments
63 --Procedure : Get Party
64 --Description : Returns Name, Description for a given role or all the roles
65 -- attached to a contract
66 --End of Comments
67 Procedure Get_Party (
68 p_api_version IN NUMBER,
69 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
70 x_return_status OUT NOCOPY VARCHAR2,
71 x_msg_count OUT NOCOPY NUMBER,
72 x_msg_data OUT NOCOPY VARCHAR2,
73 p_chr_id IN VARCHAR2,
74 p_cle_id IN VARCHAR2,
75 p_role_code IN OKC_K_PARTY_ROLES_V.rle_code%Type,
76 p_intent IN VARCHAR2 default 'S',
77 x_party_tab OUT NOCOPY party_tab_type
78 ) is
79 CURSOR party_role_curs(p_chr_id IN NUMBER,
80 p_cle_id IN NUMBER,
81 p_dnz_chr_id IN NUMBER,
82 p_role_code IN VARCHAR2) is
83 select object1_id1,
84 object1_id2,
85 jtot_object1_code,
86 rle_code
87 from OKC_K_PARTY_ROLES_V
88 where rle_code = nvl(p_role_code,rle_code)
89 and nvl(cle_id,-99999) = p_cle_id
90 and nvl(chr_id,-99999) = p_chr_id
91 and dnz_chr_id = decode(p_chr_id,null,dnz_chr_id,p_dnz_chr_id)
92 order by rle_code;
93 party_role_rec party_role_curs%RowType;
94 l_select_clause varchar2(2000) default null;
95 l_from_clause varchar2(2000) default null;
96 l_where_clause varchar2(2000) default null;
97 l_order_by_clause varchar2(2000) default null;
98 l_query_string varchar2(2000) default null;
99 l_id1 OKC_K_PARTY_ROLES_V.OBJECT1_ID1%TYPE default Null;
100 l_id2 OKC_K_PARTY_ROLES_V.OBJECT1_ID2%TYPE default Null;
101 l_name VARCHAR2(250) Default Null;
102 l_Description VARCHAR2(250) Default Null;
103 l_object_code VARCHAR2(30) Default Null;
104 type party_curs_type is REF CURSOR;
105 party_curs party_curs_type;
106 i Number default 0;
107 l_chr_id Number;
108 l_dnz_chr_id Number;
109 l_cle_id Number;
110
111 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
112 l_api_name CONSTANT VARCHAR2(30) := 'GET_PARTY2';
113 l_api_version CONSTANT NUMBER := 1.0;
114
115 Begin
116 l_return_status := OKL_API.START_ACTIVITY( substr(l_api_name,1,26),
117 G_PKG_NAME,
118 p_init_msg_list,
119 l_api_version,
120 p_api_version,
121 '_PVT',
122 x_return_status);
123
124 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
125 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
126 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
127 RAISE OKL_API.G_EXCEPTION_ERROR;
128 END IF;
129
130 If p_chr_id is not null and p_cle_id is null
131 Then
132 l_chr_id := p_chr_id;
133 l_cle_id := -99999;
134 l_dnz_chr_id := p_chr_id;
135 ElsIf p_chr_id is null and p_cle_id is not null
136 Then
137 l_chr_id := -99999;
138 l_cle_id := p_cle_id;
139 l_dnz_chr_id := -99999;
140 ElsIf p_chr_id is not null and p_cle_id is not null
141 Then
142 l_chr_id := -99999;
143 l_cle_id := p_cle_id;
144 l_dnz_chr_id := p_chr_id;
145 Elsif p_chr_id is null and p_cle_id is null
146 Then
147 --dbms_output.put_line('Error : Either line_id or header id required to find party_role');
148 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
149 p_msg_name => G_MISS_CHR_CLE,
150 p_token1 => G_MISS_PARA_TOKEN,
151 p_token1_value => 'p_chr_id or p_cle_id');
152
153 RAISE OKL_API.G_EXCEPTION_ERROR; --raise appropriate exception here
154 End If;
155 Open party_role_curs(l_chr_id,l_cle_id,l_dnz_chr_id,p_role_code);
156 Loop
157 Fetch party_role_curs into party_role_rec;
158 Exit When party_role_curs%NotFound;
159 i := party_role_curs%rowcount;
160
161 --Added by kthiruva 23-Sep-2003 Bug No.3156265
162
163 --For object_code 'OKX_PARTY'
164
165 IF ( (party_role_rec.rle_code IN ('BROKER', 'DEALER','GUARANTOR','LESSEE' , 'MANUFACTURER' , 'PRIVATE_LABEL') AND p_intent ='S')
166 OR (party_role_rec.rle_code = 'INVESTOR' AND p_intent='B'))
167 THEN
168 OPEN okx_party_csr(p_name => null,
169 p_id1 => party_role_rec.object1_id1,
170 p_id2 => party_role_rec.object1_id2 );
171
172 l_id1 := Null;
173 l_id2 := Null;
174 l_name := Null;
175 l_description := Null;
176
177 Fetch okx_party_csr into l_id1,
178 l_id2,
179 l_name,
180 l_description;
181
182 If okx_party_csr%NotFound Then
183 --dbms_output.put_line('Not able to find data for role "'||party_role_rec.rle_code||'"');
184 --Null;--raise appropriate exception here
185 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
186 p_msg_name => G_UNABLE_TO_FIND_PARTY_ROLE,
187 p_token1 => G_PARTY_ROLE_TOKEN,
188 p_token1_value => 'party role',
189 p_token2 => G_PARTY_ROLE_CODE_TOKEN,
190 p_token2_value => party_role_rec.rle_code);
191 RAISE OKL_API.G_EXCEPTION_ERROR;
192 End If;
193 x_party_tab(i).rle_code := party_role_rec.rle_code;
194 x_party_tab(i).id1 := l_id1;
195 x_party_tab(i).id2 := l_id2;
196 x_party_tab(i).name := l_name;
197 x_party_tab(i).description := l_description;
198 x_party_tab(i).object_code := l_object_code;
199 Close okx_party_csr;
200 END IF;
201
202 --For object_code 'OKX_OPERUNIT'
203
204 IF ( (party_role_rec.rle_code ='LESSOR' AND p_intent ='S') OR (party_role_rec.rle_code ='SYNDICATOR' AND p_intent ='B'))
205 THEN
206 OPEN okx_operunit_csr(p_name => null,
207 p_id1 => party_role_rec.object1_id1,
208 p_id2 => party_role_rec.object1_id2 );
209
210 l_id1 := Null;
211 l_id2 := Null;
212 l_name := Null;
213 l_description := Null;
214
215 Fetch okx_operunit_csr into l_id1,
216 l_id2,
217 l_name,
218 l_description;
219
220 If okx_operunit_csr%NotFound Then
221 --dbms_output.put_line('Not able to find data for role "'||party_role_rec.rle_code||'"');
222 --Null;--raise appropriate exception here
223 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
224 p_msg_name => G_UNABLE_TO_FIND_PARTY_ROLE,
225 p_token1 => G_PARTY_ROLE_TOKEN,
226 p_token1_value => 'party role',
227 p_token2 => G_PARTY_ROLE_CODE_TOKEN,
228 p_token2_value => party_role_rec.rle_code);
229 RAISE OKL_API.G_EXCEPTION_ERROR;
230 End If;
231 x_party_tab(i).rle_code := party_role_rec.rle_code;
232 x_party_tab(i).id1 := l_id1;
233 x_party_tab(i).id2 := l_id2;
234 x_party_tab(i).name := l_name;
235 x_party_tab(i).description := l_description;
236 x_party_tab(i).object_code := l_object_code;
237 Close okx_operunit_csr;
238 END IF;
239
240 --For object_code 'OKX_VENDOR'
241
242 IF ( party_role_rec.rle_code ='OKL_VENDOR' AND p_intent='S')
243 THEN
244 OPEN okx_vendor_csr(p_name => null,
245 p_id1 => party_role_rec.object1_id1,
246 p_id2 => party_role_rec.object1_id2 );
247
248 l_id1 := Null;
249 l_id2 := Null;
250 l_name := Null;
251 l_description := Null;
252
253 Fetch okx_vendor_csr into l_id1,
254 l_id2,
255 l_name,
256 l_description;
257
258 If okx_vendor_csr%NotFound Then
259 --dbms_output.put_line('Not able to find data for role "'||party_role_rec.rle_code||'"');
260 --Null;--raise appropriate exception here
261 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
262 p_msg_name => G_UNABLE_TO_FIND_PARTY_ROLE,
263 p_token1 => G_PARTY_ROLE_TOKEN,
264 p_token1_value => 'party role',
265 p_token2 => G_PARTY_ROLE_CODE_TOKEN,
266 p_token2_value => party_role_rec.rle_code);
267 RAISE OKL_API.G_EXCEPTION_ERROR;
268 End If;
269 x_party_tab(i).rle_code := party_role_rec.rle_code;
270 x_party_tab(i).id1 := l_id1;
271 x_party_tab(i).id2 := l_id2;
272 x_party_tab(i).name := l_name;
273 x_party_tab(i).description := l_description;
274 x_party_tab(i).object_code := l_object_code;
275 Close okx_vendor_csr;
276 END IF;
277
278
279 End Loop;
280 Close party_role_curs;
281 --Call End Activity
282 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
283 x_msg_data => x_msg_data);
284 EXCEPTION
285 when OKL_API.G_EXCEPTION_ERROR then
286 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
287 p_api_name => l_api_name,
288 p_pkg_name => g_pkg_name,
289 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
290 x_msg_count => x_msg_count,
291 x_msg_data => x_msg_data,
292 p_api_type => g_api_type);
293 IF okx_party_csr%ISOPEN THEN
294 CLOSE okx_party_csr;
295 END IF;
296 IF okx_operunit_csr%ISOPEN THEN
297 CLOSE okx_operunit_csr;
298 END IF;
299 IF okx_vendor_csr%ISOPEN THEN
300 CLOSE okx_vendor_csr;
301 END IF;
302
303
304 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
305 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
306 p_api_name => l_api_name,
307 p_pkg_name => g_pkg_name,
308 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
309 x_msg_count => x_msg_count,
310 x_msg_data => x_msg_data,
311 p_api_type => g_api_type);
312
313 IF okx_party_csr%ISOPEN THEN
314 CLOSE okx_party_csr;
315 END IF;
316 IF okx_operunit_csr%ISOPEN THEN
317 CLOSE okx_operunit_csr;
318 END IF;
319 IF okx_vendor_csr%ISOPEN THEN
320 CLOSE okx_vendor_csr;
321 END IF;
322
323
324 when OTHERS then
325 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
326 p_api_name => l_api_name,
327 p_pkg_name => g_pkg_name,
328 p_exc_name => 'OTHERS',
329 x_msg_count => x_msg_count,
330 x_msg_data => x_msg_data,
331 p_api_type => g_api_type);
332
333 IF okx_party_csr%ISOPEN THEN
334 CLOSE okx_party_csr;
335 END IF;
336 IF okx_operunit_csr%ISOPEN THEN
337 CLOSE okx_operunit_csr;
338 END IF;
339 IF okx_vendor_csr%ISOPEN THEN
340 CLOSE okx_vendor_csr;
341 END IF;
342
343 End Get_Party;
344 --Start of Comments
345 --Procedure : Get_Party
346 --Description : Fetches Name, Description of a Party role for a given
347 -- object1_id1 and object2_id2
348 --End of comments
349 Procedure Get_Party (p_api_version IN NUMBER,
350 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
351 x_return_status OUT NOCOPY VARCHAR2,
352 x_msg_count OUT NOCOPY NUMBER,
353 x_msg_data OUT NOCOPY VARCHAR2,
354 p_role_code IN VARCHAR2,
355 p_intent IN VARCHAR2,
356 p_id1 IN VARCHAR2,
357 p_id2 IN VARCHAR2,
358 x_id1 OUT NOCOPY VARCHAR2,
359 x_id2 OUT NOCOPY VARCHAR2,
360 x_name OUT NOCOPY VARCHAR2,
361 x_description OUT NOCOPY VARCHAR2) is
362
363 l_select_clause varchar2(2000) default null;
364 l_from_clause varchar2(2000) default null;
365 l_where_clause varchar2(2000) default null;
366 l_order_by_clause varchar2(2000) default null;
367 l_query_string varchar2(2000) default null;
368 l_id1 OKC_K_PARTY_ROLES_V.OBJECT1_ID1%TYPE default Null;
369 l_id2 OKC_K_PARTY_ROLES_V.OBJECT1_ID2%TYPE default Null;
370 l_name VARCHAR2(250) Default Null;
371 l_Description VARCHAR2(250) Default Null;
372 l_object_code VARCHAR2(30) Default Null;
373 type party_curs_type is REF CURSOR;
374 party_curs party_curs_type;
375
376 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
377 l_api_name CONSTANT VARCHAR2(30) := 'GET_PARTY3';
378 l_api_version CONSTANT NUMBER := 1.0;
379
380 Begin
381 --Call OKL_API.START_ACTIVITY
382 l_return_status := OKL_API.START_ACTIVITY( substr(l_api_name,1,26),
383 G_PKG_NAME,
384 p_init_msg_list,
385 l_api_version,
386 p_api_version,
387 '_PVT',
388 x_return_status);
389
390 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
391 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
392 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
393 RAISE OKL_API.G_EXCEPTION_ERROR;
394 END IF;
395
396
397 --Added by kthiruva 23-Sep-2003 Bug No.3156265
398
399 -- For Object Code 'OKX_PARTY'
400
401 IF ( (p_role_code IN ('BROKER', 'DEALER','GUARANTOR','LESSEE' , 'MANUFACTURER' , 'PRIVATE_LABEL') AND p_intent ='S')
402 OR (p_role_code = 'INVESTOR' AND p_intent='B'))
403 THEN
404
405
406 OPEN okx_party_csr(p_name => null,
407 p_id1 => p_id1,
408 p_id2 => p_id2);
409
410
411 l_id1 := Null;
412 l_id2 := Null;
413 l_name := Null;
414 l_description := Null;
415 Fetch okx_party_csr into l_id1,
416 l_id2,
417 l_name,
418 l_description;
419
420 If okx_party_csr%NotFound Then
421 --dbms_output.put_line('Not able to find data for role "'||p_role_code||'"');
422 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
423 p_msg_name => G_UNABLE_TO_FIND_PARTY_ROLE,
424 p_token1 => G_PARTY_ROLE_TOKEN,
425 p_token1_value => 'party role',
426 p_token2 => G_PARTY_ROLE_CODE_TOKEN,
427 p_token2_value => p_role_code);
428
429 RAISE OKL_API.G_EXCEPTION_ERROR;
430 End If;
431 x_id1 := l_id1;
432 x_id2 := l_id2;
433 x_name := l_name;
434 x_description := l_description;
435 Close okx_party_csr;
436 END IF;
437
438 -- For Object Code 'OKX_OPERUNIT'
439
440 IF ( (p_role_code ='LESSOR' AND p_intent ='S') OR (p_role_code ='SYNDICATOR' AND p_intent ='B')) THEN
441
442
443 OPEN okx_operunit_csr(p_name => null,
444 p_id1 => p_id1,
445 p_id2 => p_id2);
446
447
448 l_id1 := Null;
449 l_id2 := Null;
450 l_name := Null;
451 l_description := Null;
452 Fetch okx_operunit_csr into l_id1,
453 l_id2,
454 l_name,
455 l_description;
456
457 If okx_operunit_csr%NotFound Then
458 --dbms_output.put_line('Not able to find data for role "'||p_role_code||'"');
459 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
460 p_msg_name => G_UNABLE_TO_FIND_PARTY_ROLE,
461 p_token1 => G_PARTY_ROLE_TOKEN,
462 p_token1_value => 'party role',
463 p_token2 => G_PARTY_ROLE_CODE_TOKEN,
464 p_token2_value => p_role_code);
465
466 RAISE OKL_API.G_EXCEPTION_ERROR;
467 End If;
468 x_id1 := l_id1;
469 x_id2 := l_id2;
470 x_name := l_name;
471 x_description := l_description;
472 Close okx_operunit_csr;
473 END IF;
474
475 -- For Object Code 'OKX_VENDOR'
476
477 IF ( p_role_code ='OKL_VENDOR' AND p_intent='S') THEN
478
479
480 OPEN okx_vendor_csr(p_name => null,
481 p_id1 => p_id1,
482 p_id2 => p_id2);
483
484
485 l_id1 := Null;
486 l_id2 := Null;
487 l_name := Null;
488 l_description := Null;
489 Fetch okx_vendor_csr into l_id1,
490 l_id2,
491 l_name,
492 l_description;
493
494 If okx_vendor_csr%NotFound Then
495 --dbms_output.put_line('Not able to find data for role "'||p_role_code||'"');
496 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
497 p_msg_name => G_UNABLE_TO_FIND_PARTY_ROLE,
498 p_token1 => G_PARTY_ROLE_TOKEN,
499 p_token1_value => 'party role',
500 p_token2 => G_PARTY_ROLE_CODE_TOKEN,
501 p_token2_value => p_role_code);
502
503 RAISE OKL_API.G_EXCEPTION_ERROR;
504 End If;
505 x_id1 := l_id1;
506 x_id2 := l_id2;
507 x_name := l_name;
508 x_description := l_description;
509 Close okx_vendor_csr;
510 END IF;
511
512 --Call End Activity
513 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
514 x_msg_data => x_msg_data);
515 EXCEPTION
516 when OKL_API.G_EXCEPTION_ERROR then
517 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
518 p_api_name => l_api_name,
519 p_pkg_name => g_pkg_name,
520 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
521 x_msg_count => x_msg_count,
522 x_msg_data => x_msg_data,
523 p_api_type => g_api_type);
524 IF okx_party_csr%ISOPEN THEN
525 CLOSE okx_party_csr;
526 END IF;
527 IF okx_operunit_csr%ISOPEN THEN
528 CLOSE okx_operunit_csr;
529 END IF;
530 IF okx_vendor_csr%ISOPEN THEN
531 CLOSE okx_vendor_csr;
532 END IF;
533
534
535 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
536 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
537 p_api_name => l_api_name,
538 p_pkg_name => g_pkg_name,
539 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
540 x_msg_count => x_msg_count,
541 x_msg_data => x_msg_data,
542 p_api_type => g_api_type);
543
544 IF okx_party_csr%ISOPEN THEN
545 CLOSE okx_party_csr;
546 END IF;
547 IF okx_operunit_csr%ISOPEN THEN
548 CLOSE okx_operunit_csr;
549 END IF;
550 IF okx_vendor_csr%ISOPEN THEN
551 CLOSE okx_vendor_csr;
552 END IF;
553
554
555 when OTHERS then
556 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
557 p_api_name => l_api_name,
558 p_pkg_name => g_pkg_name,
559 p_exc_name => 'OTHERS',
560 x_msg_count => x_msg_count,
561 x_msg_data => x_msg_data,
562 p_api_type => g_api_type);
563 IF okx_party_csr%ISOPEN THEN
564 CLOSE okx_party_csr;
565 END IF;
566 IF okx_operunit_csr%ISOPEN THEN
567 CLOSE okx_operunit_csr;
568 END IF;
569 IF okx_vendor_csr%ISOPEN THEN
570 CLOSE okx_vendor_csr;
571 END IF;
572
573
574 End Get_Party;
575 --Start of Comments
576 --Procedure : Get_Subclass_Roles
577 --Description : fetches Party Roles for a Subclass
578 --End of Comments
579 Procedure Get_SubClass_Def_Roles
580 (p_api_version IN NUMBER,
581 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
582 x_return_status OUT NOCOPY VARCHAR2,
583 x_msg_count OUT NOCOPY NUMBER,
584 x_msg_data OUT NOCOPY VARCHAR2,
585 p_scs_code IN OKC_SUBCLASSES_V.CODE%TYPE,
586 x_rle_code_tbl OUT NOCOPY rle_code_tbl_type) is
587 CURSOR scs_rle_curs is
588 select scs_code,
589 rle_code
590 from okc_subclass_roles
591 where scs_code = p_scs_code
592 and nvl(start_date,sysdate) <= sysdate
593 and nvl(end_date,sysdate+1) > sysdate;
594 scs_rle_rec scs_rle_curs%rowType;
595 i Number;
596
597 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
598 l_api_name CONSTANT VARCHAR2(30) := 'GET_SUBCLASS_ROLES';
599 l_api_version CONSTANT NUMBER := 1.0;
600
601 Begin
602 --Call OKL_API.START_ACTIVITY
603 l_return_status := OKL_API.START_ACTIVITY( substr(l_api_name,1,26),
604 G_PKG_NAME,
605 p_init_msg_list,
606 l_api_version,
607 p_api_version,
608 '_PVT',
609 x_return_status);
610
611 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
612 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
613 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
614 RAISE OKL_API.G_EXCEPTION_ERROR;
615 END IF;
616
617 Open scs_rle_curs;
618 Loop
619 Fetch scs_rle_curs into scs_rle_rec;
620 Exit When scs_rle_curs%NotFound;
621 i := scs_rle_curs%RowCount;
622 x_rle_code_tbl(i).scs_code := scs_rle_rec.scs_code;
623 x_rle_code_tbl(i).rle_code := scs_rle_rec.rle_code;
624 End Loop;
625 Close scs_rle_curs;
626 --Call End Activity
627 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
628 x_msg_data => x_msg_data);
629 EXCEPTION
630 when OKL_API.G_EXCEPTION_ERROR then
631 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
632 p_api_name => l_api_name,
633 p_pkg_name => g_pkg_name,
634 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
635 x_msg_count => x_msg_count,
636 x_msg_data => x_msg_data,
637 p_api_type => g_api_type);
638
639 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
640 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
641 p_api_name => l_api_name,
642 p_pkg_name => g_pkg_name,
643 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
644 x_msg_count => x_msg_count,
645 x_msg_data => x_msg_data,
646 p_api_type => g_api_type);
647
648 when OTHERS then
649 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
650 p_api_name => l_api_name,
651 p_pkg_name => g_pkg_name,
652 p_exc_name => 'OTHERS',
653 x_msg_count => x_msg_count,
654 x_msg_data => x_msg_data,
655 p_api_type => g_api_type);
656
657 End Get_SubClass_Def_Roles;
658 --Start of Comments
659 --Procedure : Get_Contract_Def
660 --Description : fetches Party Roles for a contract
661 --End of Comments
662 Procedure Get_Contract_Def_Roles
663 (p_api_version IN NUMBER,
664 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
665 x_return_status OUT NOCOPY VARCHAR2,
666 x_msg_count OUT NOCOPY NUMBER,
667 x_msg_data OUT NOCOPY VARCHAR2,
668 p_chr_id IN VARCHAR2,
669 x_rle_code_tbl OUT NOCOPY rle_code_tbl_type) is
670 Cursor chr_scs_curs is
671 select scs_code
672 from OKC_K_HEADERS_B
673 where id = p_chr_id;
674 l_scs_code OKC_K_HEADERS_B.SCS_CODE%TYPE;
675
676 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
677 l_api_name CONSTANT VARCHAR2(30) := 'GET_CONTRACT_DEF_ROLES';
678 l_api_version CONSTANT NUMBER := 1.0;
679
680 Begin
681 --Call OKL_API.START_ACTIVITY
682 l_return_status := OKL_API.START_ACTIVITY( substr(l_api_name,1,26),
683 G_PKG_NAME,
684 p_init_msg_list,
685 l_api_version,
686 p_api_version,
687 '_PVT',
688 x_return_status);
689
690 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
691 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
692 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
693 RAISE OKL_API.G_EXCEPTION_ERROR;
694 END IF;
695
696 Open chr_scs_curs;
697 Fetch chr_scs_curs into l_scs_code;
698 If chr_scs_curs%NotFound Then
699 --dbms_output.put_line('Subclass not found for chr id "'||p_chr_id||'"');
700 --null; --handle appropriate exception
701 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
702 p_msg_name => G_MISSING_CONTRACT,
703 p_token1 => G_CONTRACT_ID_TOKEN,
704 p_token1_value => p_chr_id);
705 RAISE OKL_API.G_EXCEPTION_ERROR;
706 Else
707 Get_Subclass_Def_Roles(p_api_version => p_api_version,
708 p_init_msg_list => p_init_msg_list,
709 x_return_status => x_return_status,
710 x_msg_count => x_msg_count,
711 x_msg_data => x_msg_data,
712 p_scs_code => l_scs_code,
713 x_rle_code_tbl => x_rle_code_tbl);
714 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
715 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
716 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
717 RAISE OKL_API.G_EXCEPTION_ERROR;
718 END IF;
719
720 End If;
721 --Call End Activity
722 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
723 x_msg_data => x_msg_data);
724 EXCEPTION
725 when OKL_API.G_EXCEPTION_ERROR then
726 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
727 p_api_name => l_api_name,
728 p_pkg_name => g_pkg_name,
729 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
730 x_msg_count => x_msg_count,
731 x_msg_data => x_msg_data,
732 p_api_type => g_api_type);
733
734 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
735 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
736 p_api_name => l_api_name,
737 p_pkg_name => g_pkg_name,
738 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
739 x_msg_count => x_msg_count,
740 x_msg_data => x_msg_data,
741 p_api_type => g_api_type);
742
743 when OTHERS then
744 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
745 p_api_name => l_api_name,
746 p_pkg_name => g_pkg_name,
747 p_exc_name => 'OTHERS',
748 x_msg_count => x_msg_count,
749 x_msg_data => x_msg_data,
750 p_api_type => g_api_type);
751
752 End Get_Contract_Def_Roles;
753 ------------------------------
754 Procedure Get_Contact(
755 p_api_version IN NUMBER,
756 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
757 x_return_status OUT NOCOPY VARCHAR2,
758 x_msg_count OUT NOCOPY NUMBER,
759 x_msg_data OUT NOCOPY VARCHAR2,
760 p_role_code IN VARCHAR2,
761 p_contact_code IN VARCHAR2,
762 p_intent IN VARCHAR2 DEFAULT 'S',
763 p_id1 IN VARCHAR2,
764 p_id2 IN VARCHAR2,
765 p_name IN VARCHAR2,
766 x_select_clause OUT NOCOPY VARCHAR2,
767 x_from_clause OUT NOCOPY VARCHAR2,
768 x_where_clause OUT NOCOPY VARCHAR2,
769 x_order_by_clause OUT NOCOPY VARCHAR2,
770 x_object_code OUT NOCOPY VARCHAR2) is
771 CURSOR jtf_contacts_cur(p_contact_code VARCHAR2, p_role_code VARCHAR2, p_intent VARCHAR2) is
772 select job.object_code OBJECT_CODE,
773 job.object_code||'.ID1, '||
774 job.object_code||'.ID2, '||
775 job.object_code||'.NAME, '||
776 job.object_code||'.DESCRIPTION ' SELECT_CLAUSE,
777 from_table FROM_CLAUSE,
778 where_clause WHERE_CLAUSE,
779 order_by_clause ORDER_BY_CLAUSE
780 from jtf_objects_b job,
781 okc_contact_sources cs
782 where job.object_code = cs.jtot_object_code
783 and nvl(job.start_date_active,sysdate) <= sysdate
784 and nvl(job.end_date_active,sysdate + 1) > sysdate
785 and cs.cro_code = p_contact_code
786 and cs.rle_code = p_role_code
787 and cs.start_date <= sysdate
788 and nvl(cs.end_date,sysdate+1) > sysdate
789 and cs.buy_or_sell = p_intent;
790 jtf_contacts_rec jtf_contacts_cur%rowtype;
791 l_query_string VARCHAR2(2000) default Null;
792 l_where_clause VARCHAR2(2000) default Null;
793 type contact_curs_type is REF CURSOR;
794 contact_count_curs contact_curs_type;
795 l_rec_count NUMBER default 0;
796
797 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
798 l_api_name CONSTANT VARCHAR2(30) := 'GET_CONTACT';
799 l_api_version CONSTANT NUMBER := 1.0;
800
801 Begin
802 --Call OKL_API.START_ACTIVITY
803 l_return_status := OKL_API.START_ACTIVITY( substr(l_api_name,1,26),
804 G_PKG_NAME,
805 p_init_msg_list,
806 l_api_version,
807 p_api_version,
808 '_PVT',
809 x_return_status);
810
811 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
812 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
813 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
814 RAISE OKL_API.G_EXCEPTION_ERROR;
815 END IF;
816
817 If okc_context.get_okc_org_id is null then
818 okc_context.set_okc_org_context(null,null);
819 End If;
820
821 Open jtf_contacts_cur(p_contact_code,p_role_code,p_intent);
822 Fetch jtf_contacts_cur into jtf_contacts_rec;
823 If jtf_contacts_cur%NOTFOUND Then
824 --dbms_output.put_line('falied in getting jtot query for contact : "'||p_contact_code||'"');
825 --handle exception appropriately
826 --dbms_output.put_line('Not able to find data for role "'||p_role_code||'"');
827 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
828 p_msg_name => G_UNABLE_TO_FIND_PARTY_ROLE,
829 p_token1 => G_PARTY_ROLE_TOKEN,
830 p_token1_value => 'party contract',
831 p_token2 => G_PARTY_ROLE_CODE_TOKEN,
832 p_token2_value => p_contact_code);
833
834 RAISE OKL_API.G_EXCEPTION_ERROR;
835 Else
836 x_object_code := jtf_contacts_rec.object_code;
837 x_select_clause := jtf_contacts_rec.select_clause;
838 x_from_clause := jtf_contacts_rec.from_clause;
839 x_where_clause := jtf_contacts_rec.where_clause;
840 x_order_by_clause := jtf_contacts_rec.order_by_clause;
841 If p_id1 is not null and p_id2 is not null and p_name is null then
842 select x_where_clause || decode(x_where_clause,null,null,' AND ')||
843 ' ID1 = '||''''||p_id1||''''||' AND '||' ID2 = '||''''||p_id2||''''
844 into l_where_clause
845 from dual;
846 x_where_clause := l_where_clause;
847 Elsif p_name is not null then
848 select x_where_clause || decode(x_where_clause,null,null,' AND ')||
849 ' NAME = '||''''||p_name||''''
850 into l_where_clause
851 from dual;
852 End If;
853 /*
854 select ' SELECT count(*) rec_count'||
855 ' FROM '||x_from_clause||
856 decode(x_where_clause,null,' ',' WHERE ')||x_where_clause||
857 decode(x_order_by_clause,null,null,' ORDER BY ')||x_order_by_clause
858 into l_query_string from dual;
859 l_rec_count := 0;
860 Open contact_count_curs for l_query_string;
861 Fetch contact_count_curs into l_rec_count;
862 If l_rec_count = 0
863 Then
864 Null; -- trying to avoid internal error
865 End If;
866 Close contact_count_curs;
867 x_record_count := l_rec_count;
868 */
869 End If;
870 Close jtf_contacts_cur;
871 --Call End Activity
872 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
873 x_msg_data => x_msg_data);
874 EXCEPTION
875 when OKL_API.G_EXCEPTION_ERROR then
876 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
877 p_api_name => l_api_name,
878 p_pkg_name => g_pkg_name,
879 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
880 x_msg_count => x_msg_count,
881 x_msg_data => x_msg_data,
882 p_api_type => g_api_type);
883
884 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
885 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
886 p_api_name => l_api_name,
887 p_pkg_name => g_pkg_name,
888 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
889 x_msg_count => x_msg_count,
890 x_msg_data => x_msg_data,
891 p_api_type => g_api_type);
892
893 when OTHERS then
894 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
895 p_api_name => l_api_name,
896 p_pkg_name => g_pkg_name,
897 p_exc_name => 'OTHERS',
898 x_msg_count => x_msg_count,
899 x_msg_data => x_msg_data,
900 p_api_type => g_api_type);
901
902 END Get_Contact;
903 END OKL_JTOT_EXTRACT;