[Home] [Help]
PACKAGE BODY: APPS.OKL_VP_JTF_PARTY_NAME_PVT
Source
1 PACKAGE BODY OKL_VP_JTF_PARTY_NAME_PVT AS
2 /* $Header: OKLRCTSB.pls 120.7.12010000.2 2009/02/09 15:00:04 nikshah ship $ */
3 --Start of Comments
4 --Procedure : Get Party
5 --Description : Returns the SQL string for LOV of a party Role
6 --End of Comments
7
8
9 --Added by kthiruva 23-Sep-2003 Bug No.3156265
10
11 --For Object Code 'OKX_PARTY'
12 CURSOR okx_party_csr(p_name VARCHAR2 , p_id1 VARCHAR2 , p_id2 VARCHAR2) IS
13 SELECT prv.id1,
14 prv.id2,
15 prv.name,
16 prv.description
17 FROM okx_parties_v prv
18 WHERE prv.name = NVL(p_name,prv.name)
19 AND prv.id1 = NVL(p_id1,prv.id1)
20 AND prv.id2 = NVL(p_id2,prv.id2)
21 ORDER BY prv.name;
22
23 --For Object Code 'OKX_OPERUNIT'
24 CURSOR okx_operunit_csr(p_name VARCHAR2 , p_id1 VARCHAR2 , p_id2 VARCHAR2) IS
25 SELECT ord.id1,
26 ord.id2,
27 ord.name,
28 ord.description
29 FROM okx_organization_defs_v ord
30 WHERE ord.organization_type = 'OPERATING_UNIT'
31 AND ord.information_type = 'Operating Unit Information'
32 AND ord.name = NVL(p_name,ord.name)
33 AND ord.id1 = NVL(p_id1,ord.id1)
34 AND ord.id2 = NVL(p_id2,ord.id2)
35 ORDER BY ord.NAME;
36
37
38 --For Object Code 'OKX_VENDOR'
39 -- Updated the cursor In parameter and Sql statement, as confirmed by VP team, to improve the performance
40 -- Performance bug#5484903 -- varangan - 28-9-06
41 CURSOR okx_vendor_csr(p_id1 VARCHAR2 , p_id2 VARCHAR2) IS
42 SELECT vev.id1,
43 vev.id2,
44 vev.name,
45 vev.description
46 FROM okx_vendors_v vev
47 WHERE vev.id1 = p_id1
48 AND vev.id2 = NVL(p_id2,vev.id2)
49 ORDER BY vev.NAME;
50
51 --For Object Code 'OKX_SALEPERS'
52 -- Updated the cursor In parameter and Sql statement,as confirmed by VP team, to improve the performance
53 -- Performance bug#5484903 -- varangan - 28-9-06
54 CURSOR okx_salepers_csr(p_id1 VARCHAR2 , p_id2 VARCHAR2) IS
55 SELECT srv.ID1,
56 srv.ID2,
57 srv.NAME,
58 srv.DESCRIPTION
59 FROM OKX_SALESREPS_V srv
60 WHERE ((nvl(srv.ORG_ID, -99) = nvl(mo_global.get_current_org_id, -99)) or (nvl(mo_global.get_current_org_id, -99) = -99))
61 AND srv.ID1 = p_id1
62 AND srv.ID2 = NVL(p_id2,srv.ID2)
63 ORDER BY srv.NAME;
64
65 -- For Object Code 'OKX_PCONTACT'
66 --removed the cursor okx_pcontact_csr - since it is not used in the code
67 -- performance issue -bug#5484903 - varangan - 25-9-06
68
69
70 --Bug# 3336870
71 --Start of Comments
72 --Procedure : Get Party (Introduced for user defined party roles, with JTF indirection)
73 --Description : Returns the SQL string for LOV of a party Role
74 --End of Comments
75 Procedure Get_Party (p_api_version IN NUMBER,
76 p_init_msg_list IN VARCHAR2 default OKC_API.G_FALSE,
77 x_return_status OUT NOCOPY VARCHAR2,
78 x_msg_count OUT NOCOPY NUMBER,
79 x_msg_data OUT NOCOPY VARCHAR2,
80 p_role_code IN VARCHAR2,
81 p_intent IN VARCHAR2,
82 p_id1 IN VARCHAR2,
83 p_id2 IN VARCHAR2,
84 p_name IN VARCHAR2,
85 x_select_clause OUT NOCOPY VARCHAR2,
86 x_from_clause OUT NOCOPY VARCHAR2,
87 x_where_clause OUT NOCOPY VARCHAR2,
88 x_order_by_clause OUT NOCOPY VARCHAR2,
89 x_object_code OUT NOCOPY VARCHAR2) is
90 CURSOR jtf_party_role_cur (p_role_code VARCHAR2, p_intent VARCHAR2) is
91 select job.object_code OBJECT_CODE,
92 job.object_code||'.ID1, '||
93 job.object_code||'.ID2, '||
94 job.object_code||'.NAME, '||
95 job.object_code||'.DESCRIPTION ' SELECT_CLAUSE,
96 from_table FROM_CLAUSE,
97 where_clause WHERE_CLAUSE,
98 order_by_clause ORDER_BY_CLAUSE
99 from jtf_objects_b job,
100 okc_role_sources rs
101 where job.object_code = rs.jtot_object_code
102 and nvl(job.start_date_active,sysdate) <= sysdate
103 and nvl(job.end_date_active,sysdate + 1) > sysdate
104 and rs.rle_code = p_role_code
105 and rs.start_date <= sysdate
106 and nvl(rs.end_date,sysdate+1) > sysdate
107 and rs.buy_or_sell = p_intent;
108 jtf_party_role_rec jtf_party_role_cur%rowtype;
109 l_query_string VARCHAR2(2000) default Null;
110 l_where_clause VARCHAR2(2000) default Null;
111 Begin
112 Open jtf_party_role_cur(p_role_code, p_intent);
113 Fetch jtf_party_role_cur into jtf_party_role_rec;
114 If jtf_party_role_cur%NOTFOUND Then
115 --handle exception appropriately
116 x_object_code := 'NOT FOUND';
117 x_select_clause := 'NOT FOUND';
118 x_from_clause := 'NOT FOUND';
119 x_where_clause := 'NOT FOUND';
120 x_order_by_clause := 'NOT FOUND';
121 Else
122 x_object_code := jtf_party_role_rec.object_code;
123 x_select_clause := jtf_party_role_rec.select_clause;
124 x_from_clause := jtf_party_role_rec.from_clause;
125 --Fix for bug 3613832. Added the NVL for the where_clause
126 --by rvaduri
127 x_where_clause := nvl(jtf_party_role_rec.where_clause,'1=1');
128 x_order_by_clause := jtf_party_role_rec.order_by_clause;
129 If p_id1 is not null and p_id2 is not null and p_name is null then
130 select x_where_clause || decode(x_where_clause,null,null,' AND ')||
131 ' ID1 = '||''''||p_id1||''''||' AND '||' ID2 = '||''''||p_id2||''''
132 into l_where_clause
133 from dual;
134 x_where_clause := l_where_clause;
135 Elsif p_name is not null then
136 select x_where_clause || decode(x_where_clause,null,null,' AND ')||
137 ' NAME like '||''''||p_name||'%'||''''
138 into l_where_clause
139 from dual;
140 End If;
141 End If;
142 Close jtf_party_role_cur;
143 End Get_Party;
144
145
146 --Start of Comments
147 --Procedure : Get Party
148 --Description : Returns Name, Description for a given role or all the roles
149 -- attached to a contract
150 --End of Comments
151 Procedure Get_Party (p_api_version IN NUMBER,
152 p_init_msg_list IN VARCHAR2 default OKC_API.G_FALSE,
153 x_return_status OUT NOCOPY VARCHAR2,
154 x_msg_count OUT NOCOPY NUMBER,
155 x_msg_data OUT NOCOPY VARCHAR2,
156 p_chr_id IN VARCHAR2,
157 p_cle_id IN VARCHAR2,
158 p_role_code IN OKC_K_PARTY_ROLES_V.rle_code%Type,
159 p_intent IN VARCHAR2 default 'S',
160 x_party_tab out nocopy party_tab_type) is
161 CURSOR party_role_curs(p_chr_id IN NUMBER,
162 p_cle_id IN NUMBER,
163 p_dnz_chr_id IN NUMBER,
164 p_role_code IN VARCHAR2) is
165
166 -- Updated the sql for performance fix -bug#5484903 - varangan - 25-9-06
167 SELECT
168 CPLB.OBJECT1_ID1 object1_id1,
169 CPLB.OBJECT1_ID2 object1_id2,
170 CPLB.JTOT_OBJECT1_CODE jtot_object1_code,
171 CPLB.RLE_CODE rle_code
172 FROM OKC_K_PARTY_ROLES_B CPLB,
173 FND_LOOKUPS FNDV
174 WHERE CPLB.RLE_CODE = FNDV.lookup_code
175 AND FNDV.lookup_type = 'OKC_ROLE'
176 AND CPLB.rle_code = nvl(p_role_code,CPLB.rle_code)
177 and nvl(CPLB.cle_id,-99999) = p_cle_id
178 and nvl(CPLB.chr_id,-99999) = p_chr_id
179 and CPLB.dnz_chr_id = decode(p_chr_id,null,CPLB.dnz_chr_id,p_dnz_chr_id)
180 order by rle_code;
181
182 /* -- commented for improving the performance - bug#5484903
183 select object1_id1,
184 object1_id2,
185 jtot_object1_code,
186 rle_code
187 from OKC_K_PARTY_ROLES_V
188 where rle_code = nvl(p_role_code,rle_code)
189 and nvl(cle_id,-99999) = p_cle_id
190 and nvl(chr_id,-99999) = p_chr_id
191 and dnz_chr_id = decode(p_chr_id,null,dnz_chr_id,p_dnz_chr_id)
192 order by rle_code; */
193
194 party_role_rec party_role_curs%RowType;
195 l_select_clause varchar2(2000) default null;
196 l_from_clause varchar2(2000) default null;
197 l_where_clause varchar2(2000) default null;
198 l_order_by_clause varchar2(2000) default null;
199 l_query_string varchar2(2000) default null;
200 l_id1 OKC_K_PARTY_ROLES_V.OBJECT1_ID1%TYPE default Null;
201 l_id2 OKC_K_PARTY_ROLES_V.OBJECT1_ID2%TYPE default Null;
202 l_name VARCHAR2(250) Default Null;
203 l_Description VARCHAR2(250) Default Null;
204 l_object_code VARCHAR2(30) Default Null;
205 type party_curs_type is REF CURSOR;
206 party_curs party_curs_type;
207 i Number default 0;
208 l_chr_id Number;
209 l_dnz_chr_id Number;
210 l_cle_id Number;
211 Begin
212 If p_chr_id is not null and p_cle_id is null
213 Then
214 l_chr_id := p_chr_id;
215 l_cle_id := -99999;
216 l_dnz_chr_id := p_chr_id;
217 ElsIf p_chr_id is null and p_cle_id is not null
218 Then
219 l_chr_id := -99999;
220 l_cle_id := p_cle_id;
221 l_dnz_chr_id := -99999;
222 ElsIf p_chr_id is not null and p_cle_id is not null
223 Then
224 l_chr_id := -99999;
225 l_cle_id := p_cle_id;
226 l_dnz_chr_id := p_chr_id;
227 Elsif p_chr_id is null and p_cle_id is null
228 Then
229 null; --raise appropriate exception here
230 End If;
231 Open party_role_curs(l_chr_id,l_cle_id,l_dnz_chr_id,p_role_code);
232 Loop
233 Fetch party_role_curs into party_role_rec;
234 Exit When party_role_curs%NotFound;
235 i := party_role_curs%rowcount;
236
237 --Added by kthiruva 23-Sep-2003 Bug No.3156265
238
239 -- For Object Code 'OKX_PARTY'
240
241 -- Changed by manu 02-Oct-2003
242
243 -- IF ( (party_role_rec.rle_code IN ('BROKER', 'DEALER','GUARANTOR','LESSEE' , 'MANUFACTURER' , 'PRIVATE_LABEL') AND p_intent ='S')
244 -- OR (party_role_rec.rle_code = 'INVESTOR' AND p_intent='B')) THEN
245 IF ( (party_role_rec.rle_code IN ('BROKER', 'DEALER','GUARANTOR', 'INVESTOR' , 'MANUFACTURER') AND p_intent ='S')) THEN
246
247 OPEN okx_party_csr(p_name => null,
248 p_id1 => party_role_rec.object1_id1,
249 p_id2 => party_role_rec.object1_id2);
250
251
252
253 l_id1 := Null;
254 l_id2 := Null;
255 l_name := Null;
256 l_description := Null;
257
258 FETCH okx_party_csr into l_id1,
259 l_id2,
260 l_name,
261 l_description;
262
263 IF okx_party_csr%NotFound THEN
264 Null;--raise appropriate exception here
265 END IF;
266
267 x_party_tab(i).rle_code := party_role_rec.rle_code;
268 x_party_tab(i).id1 := l_id1;
269 x_party_tab(i).id2 := l_id2;
270 x_party_tab(i).name := l_name;
271 x_party_tab(i).description := l_description;
272 x_party_tab(i).object_code := l_object_code;
273 CLOSE okx_party_csr;
274 --END IF;
275
276
277 --For Object Code 'OKX_OPERUNIT'
278
279 ELSIF ( (party_role_rec.rle_code ='LESSOR' AND p_intent ='S') OR (party_role_rec.rle_code ='SYNDICATOR' AND p_intent ='B')) THEN
280
281 OPEN okx_operunit_csr(p_name => null,
282 p_id1 => party_role_rec.object1_id1,
283 p_id2 => party_role_rec.object1_id2);
284
285
286
287 l_id1 := Null;
288 l_id2 := Null;
289 l_name := Null;
290 l_description := Null;
291
292 FETCH okx_operunit_csr into l_id1,
293 l_id2,
294 l_name,
295 l_description;
296
297 IF okx_operunit_csr%NotFound THEN
298 Null;--raise appropriate exception here
299 END IF;
300
301 x_party_tab(i).rle_code := party_role_rec.rle_code;
302 x_party_tab(i).id1 := l_id1;
303 x_party_tab(i).id2 := l_id2;
304 x_party_tab(i).name := l_name;
305 x_party_tab(i).description := l_description;
306 x_party_tab(i).object_code := l_object_code;
307 CLOSE okx_operunit_csr;
308 --END IF;
309
310 -- For Object COde 'OKX_VENDOR'
311
312 ELSIF ( p_role_code ='OKL_VENDOR' AND p_intent='S') THEN
313 OPEN okx_vendor_csr(p_id1 => party_role_rec.object1_id1,
314 p_id2 => party_role_rec.object1_id2);
315
316
317
318 l_id1 := Null;
319 l_id2 := Null;
320 l_name := Null;
321 l_description := Null;
322
323 FETCH okx_vendor_csr into l_id1,
324 l_id2,
325 l_name,
326 l_description;
327
328 IF okx_vendor_csr%NotFound THEN
329 Null;--raise appropriate exception here
330 END IF;
331
332 x_party_tab(i).rle_code := party_role_rec.rle_code;
333 x_party_tab(i).id1 := l_id1;
334 x_party_tab(i).id2 := l_id2;
335 x_party_tab(i).name := l_name;
336 x_party_tab(i).description := l_description;
337 x_party_tab(i).object_code := l_object_code;
338 CLOSE okx_vendor_csr;
339 ELSE
340 --bug# 3336870 : User definable party roles will be fetched by dynamic sql as before
341 -- (taking care of OKC indirection)
342 Get_Party (p_api_version => '1.0',
343 p_init_msg_list => 'T' ,
344 x_return_status => x_return_status,
345 x_msg_count => x_msg_count,
346 x_msg_data => x_msg_data,
347 p_role_code => party_role_rec.rle_code,
348 p_intent => p_intent,
349 p_id1 => party_role_rec.object1_id1,
350 p_id2 => party_role_rec.object1_id2,
351 p_name => null,
352 x_select_clause => l_select_clause,
353 x_from_clause => l_from_clause ,
354 x_where_clause => l_where_clause,
355 x_order_by_clause => l_order_by_clause,
356 x_object_code => l_object_code);
357 l_query_string := 'SELECT '||ltrim(rtrim(l_select_clause,' '),' ')||' '||
358 'FROM '||ltrim(rtrim(l_from_clause,' '),' ')||' '||
359 'WHERE '||ltrim(rtrim(l_where_clause,' '),' ')||' '||
360 'ORDER BY '||ltrim(rtrim(l_order_by_clause,' '),' ');
361 Open party_curs for l_query_string;
362 l_id1 := Null;
363 l_id2 := Null;
364 l_name := Null;
365 l_description := Null;
366 Fetch party_curs into l_id1,
367 l_id2,
368 l_name,
369 l_description;
370 If party_curs%NotFound Then
371 Null;--raise appropriate exception here
372 End If;
373 x_party_tab(i).rle_code := party_role_rec.rle_code;
374 x_party_tab(i).id1 := l_id1;
375 x_party_tab(i).id2 := l_id2;
376 x_party_tab(i).name := l_name;
377 x_party_tab(i).description := l_description;
378 x_party_tab(i).object_code := l_object_code;
379 END IF;
380
381 End Loop;
382 Close party_role_curs;
383 End Get_Party;
384 --Start of Comments
385 --Procedure : Get_Party
386 --Description : Fetches Name, Description of a Party role for a given
387 -- object1_id1 and object2_id2
388 --End of comments
389 Procedure Get_Party (p_api_version IN NUMBER,
390 p_init_msg_list IN VARCHAR2 default OKC_API.G_FALSE,
391 x_return_status OUT NOCOPY VARCHAR2,
392 x_msg_count OUT NOCOPY NUMBER,
393 x_msg_data OUT NOCOPY VARCHAR2,
394 p_role_code IN VARCHAR2,
395 p_intent IN VARCHAR2,
396 p_id1 IN VARCHAR2,
397 p_id2 IN VARCHAR2,
398 x_id1 OUT NOCOPY VARCHAR2,
399 x_id2 OUT NOCOPY VARCHAR2,
400 x_name OUT NOCOPY VARCHAR2,
401 x_description OUT NOCOPY VARCHAR2) is
402 l_select_clause varchar2(2000) default null;
403 l_from_clause varchar2(2000) default null;
404 l_where_clause varchar2(2000) default null;
405 l_order_by_clause varchar2(2000) default null;
406 l_query_string varchar2(2000) default null;
407 l_id1 OKC_K_PARTY_ROLES_V.OBJECT1_ID1%TYPE default Null;
408 l_id2 OKC_K_PARTY_ROLES_V.OBJECT1_ID2%TYPE default Null;
409 l_name VARCHAR2(250) Default Null;
410 l_Description VARCHAR2(250) Default Null;
411 l_object_code VARCHAR2(30) Default Null;
412 type party_curs_type is REF CURSOR;
413 party_curs party_curs_type;
414 Begin
415
416
417 --Added by kthiruva 23-Sep-2003 Bug No.3156265
418
419 -- For Object Code 'OKX_PARTY'
420
421 -- Changed by manu 02-Oct-2003
422
423 -- IF ( (p_role_code IN ('BROKER', 'DEALER','GUARANTOR','LESSEE' , 'MANUFACTURER' , 'PRIVATE_LABEL') AND p_intent ='S')
424 -- OR (p_role_code = 'INVESTOR' AND p_intent='B')) THEN
425 IF ( (p_role_code IN ('BROKER', 'DEALER','GUARANTOR','INVESTOR' , 'MANUFACTURER') AND p_intent ='S')) THEN
426
427 OPEN okx_party_csr(p_name => null,
428 p_id1 => p_id1,
429 p_id2 => p_id2);
430
431 l_id1 := Null;
432 l_id2 := Null;
433 l_name := Null;
434 l_description := Null;
435
436 Fetch okx_party_csr into l_id1,
437 l_id2,
438 l_name,
439 l_description;
440
441 If okx_party_csr%NotFound Then
442 Null;--raise appropriate exception here
443 End If;
444
445 x_id1 := l_id1;
446 x_id2 := l_id2;
447 x_name := l_name;
448 x_description := l_description;
449
450 Close okx_party_csr;
451 --END IF;
452
453 -- For Object Code 'OKX_OPERUNIT'
454
455 ELSIF ( (p_role_code ='LESSOR' AND p_intent ='S') OR (p_role_code ='SYNDICATOR' AND p_intent ='B')) THEN
456
457 OPEN okx_operunit_csr(p_name => null,
458 p_id1 => p_id1,
459 p_id2 => p_id2);
460
461 l_id1 := Null;
462 l_id2 := Null;
463 l_name := Null;
464 l_description := Null;
465
466 FETCH okx_operunit_csr into l_id1,
467 l_id2,
468 l_name,
469 l_description;
470
471 If okx_operunit_csr%NotFound Then
472 Null;--raise appropriate exception here
473 End If;
474
475 x_id1 := l_id1;
476 x_id2 := l_id2;
477 x_name := l_name;
478 x_description := l_description;
479
480 CLOSE okx_operunit_csr;
481 --END IF;
482
483 -- For Object Code 'OKX_VENDOR'
484
485 ELSIF ( p_role_code ='OKL_VENDOR' AND p_intent='S') THEN
486
487 OPEN okx_vendor_csr(p_id1 => p_id1,
488 p_id2 => p_id2);
489
490 l_id1 := Null;
491 l_id2 := Null;
492 l_name := Null;
493 l_description := Null;
494
495 FETCH okx_vendor_csr into l_id1,
496 l_id2,
497 l_name,
498 l_description;
499
500 If okx_vendor_csr%NotFound Then
501 Null;--raise appropriate exception here
502 End If;
503
504 x_id1 := l_id1;
505 x_id2 := l_id2;
506 x_name := l_name;
507 x_description := l_description;
508
509 CLOSE okx_vendor_csr;
510 ELSE
511 --Bug# 3336870 : User definable party roles will have to be fetched the old-way(using OKC indirection)
512 Get_Party (p_api_version => 1.0,
513 p_init_msg_list => 'T' ,
514 x_return_status => x_return_status,
515 x_msg_count => x_msg_count,
516 x_msg_data => x_msg_data,
517 p_role_code => p_role_code,
518 p_intent => p_intent,
519 p_id1 => p_id1,
520 p_id2 => p_id2,
521 p_name => null,
522 x_select_clause => l_select_clause,
523 x_from_clause => l_from_clause ,
524 x_where_clause => l_where_clause,
525 x_order_by_clause => l_order_by_clause,
526 x_object_code => l_object_code);
527 l_query_string := 'SELECT '||ltrim(rtrim(l_select_clause,' '),' ')||' '||
528 'FROM '||ltrim(rtrim(l_from_clause,' '),' ')||' '||
529 'WHERE '||ltrim(rtrim(l_where_clause,' '),' ')||' '||
530 'ORDER BY '||ltrim(rtrim(l_order_by_clause,' '),' ');
531 Open party_curs for l_query_string;
532 l_id1 := Null;
533 l_id2 := Null;
534 l_name := Null;
535 l_description := Null;
536 Fetch party_curs into l_id1,
537 l_id2,
538 l_name,
539 l_description;
540 If party_curs%NotFound Then
541 Null;--raise appropriate exception here
542 End If;
543 x_id1 := l_id1;
544 x_id2 := l_id2;
545 x_name := l_name;
546 x_description := l_description;
547 Close party_curs;
548 END IF;
549
550 End Get_Party;
551 --Start of Comments
552 --Procedure : Get_Subclass_Roles
553 --Description : fetches Party Roles for a Subclass
554 --End of Comments
555 Procedure Get_SubClass_Def_Roles
556 (p_scs_code IN OKC_SUBCLASSES_V.CODE%TYPE,
557 x_rle_code_tbl OUT NOCOPY rle_code_tbl_type) is
558 CURSOR scs_rle_curs is
559 select scs_code,
560 rle_code
561 from okc_subclass_roles
562 where scs_code = p_scs_code
563 and nvl(start_date,sysdate) <= sysdate
564 and nvl(end_date,sysdate+1) > sysdate;
565 scs_rle_rec scs_rle_curs%rowType;
566 i Number;
567 Begin
568 Open scs_rle_curs;
569 Loop
570 Fetch scs_rle_curs into scs_rle_rec;
571 Exit When scs_rle_curs%NotFound;
572 i := scs_rle_curs%RowCount;
573 x_rle_code_tbl(i).scs_code := scs_rle_rec.scs_code;
574 x_rle_code_tbl(i).rle_code := scs_rle_rec.rle_code;
575 End Loop;
576 Close scs_rle_curs;
577 End Get_SubClass_Def_Roles;
578 --Start of Comments
579 --Procedure : Get_Subclass_Roles
580 --Description : fetches Party Roles for a Subclass
581 --End of Comments
582 Procedure Get_Contract_Def_Roles
583 (p_chr_id IN VARCHAR2,
584 x_rle_code_tbl OUT NOCOPY rle_code_tbl_type) is
585 Cursor chr_scs_curs is
586 select scs_code
587 from OKC_K_HEADERS_V
588 where id = p_chr_id;
589 l_scs_code OKC_K_HEADERS_V.SCS_CODE%TYPE;
590 Begin
591 Open chr_scs_curs;
592 Fetch chr_scs_curs into l_scs_code;
593 If chr_scs_curs%NotFound Then
594 null; --handle appropriate exception
595 Else
596 Get_Subclass_Def_Roles(p_scs_code => l_scs_code,
597 x_rle_code_tbl => x_rle_code_tbl);
598 End If;
599 End Get_Contract_Def_Roles;
600
601 --Bug# 3325281
602 --Start of Comments
603 --Procedure : Get contact
604 --Description : Returns the SQL string for LOV of a contact
605 -- (Introduced user defined party roles, with JTF indirection)
606 --End of Comments
607 Procedure Get_Contact (p_rle_code IN VARCHAR2,
608 p_cro_code IN VARCHAR2,
609 p_intent IN VARCHAR2 DEFAULT 'S',
610 p_id1 IN VARCHAR2,
611 p_id2 IN VARCHAR2,
612 p_name IN VARCHAR2,
613 x_select_clause OUT NOCOPY VARCHAR2,
614 x_from_clause OUT NOCOPY VARCHAR2,
615 x_where_clause OUT NOCOPY VARCHAR2,
616 x_order_by_clause OUT NOCOPY VARCHAR2,
617 x_object_code OUT NOCOPY VARCHAR2) is
618 CURSOR jtf_contact_cur (p_cro_code VARCHAR2, p_intent VARCHAR2) is
619 select job.object_code OBJECT_CODE,
620 job.object_code||'.ID1, '||
621 job.object_code||'.ID2, '||
622 job.object_code||'.NAME, '||
623 job.object_code||'.DESCRIPTION ' SELECT_CLAUSE,
624 from_table FROM_CLAUSE,
625 where_clause WHERE_CLAUSE,
626 order_by_clause ORDER_BY_CLAUSE
627 from jtf_objects_b job,
628 okc_contact_sources rs
629 where job.object_code = rs.jtot_object_code
630 and nvl(job.start_date_active,sysdate) <= sysdate
631 and nvl(job.end_date_active,sysdate + 1) > sysdate
632 and rs.rle_code = p_rle_code
633 and rs.cro_code = p_cro_code
634 and rs.start_date <= sysdate
635 and nvl(rs.end_date,sysdate+1) > sysdate
636 and rs.buy_or_sell = p_intent;
637 jtf_contact_rec jtf_contact_cur%rowtype;
638 l_query_string VARCHAR2(2000) default Null;
639 l_where_clause VARCHAR2(2000) default Null;
640 Begin
641 -- sjalasut, modified for bug 4755238, earlier it was hard coded to 204
642 If okc_context.get_okc_org_id is null then
643 okc_context.set_okc_org_context(null,null);
644 End If;
645
646 Open jtf_contact_cur(p_cro_code, p_intent);
647 Fetch jtf_contact_cur into jtf_contact_rec;
648 If jtf_contact_cur%NOTFOUND Then
649 --handle exception appropriately
650 x_object_code := 'NOT FOUND';
651 x_select_clause := 'NOT FOUND';
652 x_from_clause := 'NOT FOUND';
653 x_where_clause := 'NOT FOUND';
654 x_order_by_clause := 'NOT FOUND';
655 Else
656 x_object_code := jtf_contact_rec.object_code;
657 x_select_clause := jtf_contact_rec.select_clause;
658 x_from_clause := jtf_contact_rec.from_clause;
659 --Fix for bug 3613832. Added the NVL for the where_clause
660 --by rvaduri
661 x_where_clause := nvl(jtf_contact_rec.where_clause,'1=1');
662 x_order_by_clause := jtf_contact_rec.order_by_clause;
663 If p_id1 is not null and p_id2 is not null and p_name is null then
664 select '(' || x_where_clause || ')' || decode(x_where_clause,null,null,' AND ')||
665 ' ID1 = '||''''||p_id1||''''||' AND '||' ID2 = '||''''||p_id2||''''
666 into l_where_clause
667 from dual;
668 x_where_clause := l_where_clause;
669 Elsif p_name is not null then
670 select '(' || x_where_clause || ')' || decode(x_where_clause,null,null,' AND ')||
671 ' NAME like '||''''||p_name||'%'||''''
672 into l_where_clause
673 from dual;
674 End If;
675 End If;
676 Close jtf_contact_cur;
677 End get_contact;
678
679
680 --Start of Comments
681 --Procedure : Get contact
682 --Description : Returns Name, Description for a given contact or all the contacts
683 -- attached to a contract party role.
684 --End of Comments
685 Procedure Get_Contact(p_api_version IN NUMBER,
686 p_init_msg_list IN VARCHAR2 default OKC_API.G_FALSE,
687 x_return_status OUT NOCOPY VARCHAR2,
688 x_msg_count OUT NOCOPY NUMBER,
689 x_msg_data OUT NOCOPY VARCHAR2,
690 p_rle_code IN VARCHAR2,
691 p_cro_code IN VARCHAR2,
692 p_intent IN VARCHAR2,
693 p_id1 IN VARCHAR2,
694 p_id2 IN VARCHAR2,
695 x_id1 OUT NOCOPY VARCHAR2,
696 x_id2 OUT NOCOPY VARCHAR2,
697 x_name OUT NOCOPY VARCHAR2,
698 x_description OUT NOCOPY VARCHAR2) is
699 l_select_clause varchar2(2000) default null;
700 l_from_clause varchar2(2000) default null;
701 l_where_clause varchar2(2000) default null;
702 l_order_by_clause varchar2(2000) default null;
703 l_query_string varchar2(2000) default null;
704 l_id1 OKC_CONTACTS_V.OBJECT1_ID1%TYPE default Null;
705 l_id2 OKC_CONTACTS_V.OBJECT1_ID2%TYPE default Null;
706 l_name VARCHAR2(250) Default Null;
707 l_Description VARCHAR2(250) Default Null;
708 l_object_code VARCHAR2(30) Default Null;
709 type CONTACT_curs_type is REF CURSOR;
710 contact_curs contact_curs_type;
711 Begin
712
713
714 --Added by kthiruva 23-Sep-2003 Bug No.3156265
715
716 IF (p_rle_code = 'LESSOR' and p_cro_code = 'SALESPERSON' and p_intent = 'S') THEN
717
718 OPEN okx_salepers_csr(p_id1 => p_id1,
719 p_id2 => p_id2);
720
721 l_id1 := Null;
722 l_id2 := Null;
723 l_name := Null;
724 l_description := Null;
725
726 Fetch okx_salepers_csr into l_id1,
727 l_id2,
728 l_name,
729 l_description;
730
731 If okx_salepers_csr%NotFound Then
732 Null;--raise appropriate exception here
733 End If;
734
735 x_id1 := l_id1;
736 x_id2 := l_id2;
737 x_name := l_name;
738 x_description := l_description;
739
740 CLOSE okx_salepers_csr;
741
742 ELSE
743 --Bug# 3325281 Introduced user defined party roles, with JTF indirection
744
745 /*******
746
747 OPEN okx_pcontact_csr(p_name => null,
748 p_id1 => p_id1,
749 p_id2 => p_id2);
750
751 l_id1 := Null;
752 l_id2 := Null;
753 l_name := Null;
754 l_description := Null;
755
756 Fetch okx_pcontact_csr into l_id1,
757 l_id2,
758 l_name,
759 l_description;
760
761 If okx_pcontact_csr%NotFound Then
762 Null;--raise appropriate exception here
763 End If;
764
765 x_id1 := l_id1;
766 x_id2 := l_id2;
767 x_name := l_name;
768 x_description := l_description;
769
770 CLOSE okx_pcontact_csr;
771 ********/
772 Get_contact (p_rle_code => p_rle_code,
773 p_cro_code => p_cro_code,
774 p_intent => p_intent,
775 p_id1 => p_id1,
776 p_id2 => p_id2,
777 p_name => null,
778 x_select_clause => l_select_clause,
779 x_from_clause => l_from_clause ,
780 x_where_clause => l_where_clause,
781 x_order_by_clause => l_order_by_clause,
782 x_object_code => l_object_code);
783 l_query_string := 'SELECT '||ltrim(rtrim(l_select_clause,' '),' ')||' '||
784 'FROM '||ltrim(rtrim(l_from_clause,' '),' ')||' '||
785 'WHERE '||ltrim(rtrim(l_where_clause,' '),' ')||' '||
786 'ORDER BY '||ltrim(rtrim(l_order_by_clause,' '),' ');
787 Open contact_curs for l_query_string;
788 l_id1 := Null;
789 l_id2 := Null;
790 l_name := Null;
791 l_description := Null;
792 Fetch contact_curs into l_id1,
793 l_id2,
794 l_name,
795 l_description;
796 If contact_curs%NotFound Then
797 Null;--raise appropriate exception here
798 End If;
799 x_id1 := l_id1;
800 x_id2 := l_id2;
801 x_name := l_name;
802 x_description := l_description;
803 Close contact_curs;
804
805 END IF;
806
807 End Get_contact;
808
809 --Start of Comments
810 --Procedure :Get Party_name
811 --Description : Returns Name of the party. This will be called from the VO for
812 -- VP.
813 --End of Comments
814
815 FUNCTION get_party_name (p_role_code IN VARCHAR2
816 ,p_intent IN VARCHAR2
817 ,p_id1 IN VARCHAR2
818 ,p_id2 IN VARCHAR2) RETURN VARCHAR2
819 AS
820
821 x_return_status VARCHAR2(100);
822 x_msg_count NUMBER;
823 x_msg_data VARCHAR2(100);
824 x_id1 VARCHAR2(100);
825 x_id2 VARCHAR2(200);
826 x_name VARCHAR2(500);
827 x_description VARCHAR2(1000);
828
829 BEGIN
830
831 OKL_VP_JTF_PARTY_NAME_PVT.Get_Party(p_api_version => 1.0,
832 p_init_msg_list => OKC_API.G_FALSE,
833 x_return_status => x_return_status,
834 x_msg_count => x_msg_count,
835 x_msg_data => x_msg_data,
836 p_role_code => p_role_code,
837 p_intent => p_intent,
838 p_id1 => p_id1,
839 p_id2 => p_id2,
840 x_id1 => x_id1,
841 x_id2 => x_id2,
842 x_name => x_name,
843 x_description => x_description);
844
845 RETURN x_name;
846
847 END get_party_name;
848
849 --Start of Comments
850 --Procedure :Get Party contact name
851 --Description : Returns Name of the party contact. This will be called from the VO for
852 -- VP.
853 --End of Comments
854
855 FUNCTION get_party_contact_name (p_rle_code IN VARCHAR2
856 ,p_cro_code IN VARCHAR2
857 ,p_intent IN VARCHAR2
858 ,p_id1 IN VARCHAR2
859 ,p_id2 IN VARCHAR2) RETURN VARCHAR2
860 AS
861
862 x_return_status VARCHAR2(100);
863 x_msg_count NUMBER;
864 x_msg_data VARCHAR2(100);
865 x_id1 VARCHAR2(100);
866 x_id2 VARCHAR2(200);
867 x_name VARCHAR2(500);
868 x_description VARCHAR2(1000);
869
870 BEGIN
871
872 OKL_VP_JTF_PARTY_NAME_PVT.Get_Contact(p_api_version => 1.0,
873 p_init_msg_list => OKC_API.G_FALSE,
874 x_return_status => x_return_status,
875 x_msg_count => x_msg_count,
876 x_msg_data => x_msg_data,
877 p_rle_code => p_rle_code,
878 p_cro_code => p_cro_code,
879 p_intent => p_intent,
880 p_id1 => p_id1,
881 p_id2 => p_id2,
882 x_id1 => x_id1,
883 x_id2 => x_id2,
884 x_name => x_name,
885 x_description => x_description);
886
887 RETURN x_name;
888
889 END get_party_contact_name;
890
891
892 PROCEDURE get_party_lov_sql (p_role_code IN VARCHAR2
893 ,p_intent IN VARCHAR2
894 ,x_jtot_object_code OUT NOCOPY VARCHAR2
895 ,x_lov_sql OUT NOCOPY VARCHAR2)
896 AS
897
898 l_object_code VARCHAR2(1000);
899 l_select_clause VARCHAR2(1000);
900 l_from_clause VARCHAR2(1000);
901 l_where_clause VARCHAR2(1000);
902 l_order_by_clause VARCHAR2(1000);
903
904 l_sql_str VARCHAR2(4000);
905
906
907 CURSOR jtf_party_role_cur (p_role_code VARCHAR2, p_intent VARCHAR2) is
908 select job.object_code OBJECT_CODE,
909 job.object_code||'.ID1 ID1, '||
910 job.object_code||'.ID2 ID2, '||
911 job.object_code||'.NAME PARTY_NAME, '||
912 job.object_code||'.DESCRIPTION DESCRIPTION, ' ||
913 ''''|| job.object_code ||''''|| ' JTOT_OBJECT_CODE' SELECT_CLAUSE,
914 from_table FROM_CLAUSE,
915 where_clause WHERE_CLAUSE,
916 order_by_clause ORDER_BY_CLAUSE
917 from jtf_objects_b job,
918 okc_role_sources rs
919 where job.object_code = rs.jtot_object_code
920 and nvl(job.start_date_active,sysdate) <= sysdate
921 and nvl(job.end_date_active,sysdate + 1) > sysdate
922 and rs.rle_code = p_role_code
923 and rs.start_date <= sysdate
924 and nvl(rs.end_date,sysdate+1) > sysdate
925 and rs.buy_or_sell = p_intent;
926 jtf_party_role_rec jtf_party_role_cur%rowtype;
927
928 BEGIN
929
930 Open jtf_party_role_cur(p_role_code, p_intent);
931 Fetch jtf_party_role_cur into jtf_party_role_rec;
932 If jtf_party_role_cur%NOTFOUND Then
933 --handle exception appropriately
934 l_object_code := 'NOT FOUND';
935 l_select_clause := 'NOT FOUND';
936 l_from_clause := 'NOT FOUND';
937 l_where_clause := 'NOT FOUND';
938 l_order_by_clause := 'NOT FOUND';
939 Else
940 l_object_code := jtf_party_role_rec.object_code;
941 l_select_clause := jtf_party_role_rec.select_clause;
942 l_from_clause := jtf_party_role_rec.from_clause;
943 l_where_clause := jtf_party_role_rec.where_clause;
944 l_order_by_clause := jtf_party_role_rec.order_by_clause;
945
946 END IF;
947 l_sql_str := 'SELECT ' || l_select_clause ||' FROM '|| l_from_clause
948 ||' WHERE '|| NVL(l_where_clause,'1=1 ')
949 ||'ORDER BY '|| l_order_by_clause;
950 CLOSE jtf_party_role_cur;
951 x_lov_sql := l_sql_str;
952 x_jtot_object_code := l_object_code;
953
954 END get_party_lov_sql;
955
956
957 PROCEDURE get_party_contact_lov_sql (p_rle_code IN VARCHAR2
958 ,p_cro_code IN VARCHAR2
959 ,p_intent IN VARCHAR2
960 ,x_jtot_object_code OUT NOCOPY VARCHAR2
961 ,x_lov_sql OUT NOCOPY VARCHAR2)
962 AS
963
964 l_object_code VARCHAR2(1000);
965 l_select_clause VARCHAR2(1000);
966 l_from_clause VARCHAR2(1000);
967 l_where_clause VARCHAR2(1000);
968 l_order_by_clause VARCHAR2(1000);
969
970 l_sql_str VARCHAR2(4000);
971
972 CURSOR jtf_contact_cur (c_rle_code VARCHAR2,c_cro_code VARCHAR2
973 , c_intent VARCHAR2) is
974 select job.object_code OBJECT_CODE,
975 job.object_code||'.ID1, '||
976 job.object_code||'.ID2, '||
977 job.object_code||'.NAME, '||
978 job.object_code||'.DESCRIPTION, ' ||
979 ''''|| job.object_code ||''''|| ' JTOT_OBJECT_CODE' SELECT_CLAUSE,
980 from_table FROM_CLAUSE,
981 where_clause WHERE_CLAUSE,
982 order_by_clause ORDER_BY_CLAUSE
983 from jtf_objects_b job,
984 okc_contact_sources rs
985 where job.object_code = rs.jtot_object_code
986 and nvl(job.start_date_active,sysdate) <= sysdate
987 and nvl(job.end_date_active,sysdate + 1) > sysdate
988 and rs.rle_code = c_rle_code
989 and rs.cro_code = c_cro_code
990 and rs.start_date <= sysdate
991 and nvl(rs.end_date,sysdate+1) > sysdate
992 and rs.buy_or_sell = c_intent;
993
994
995 jtf_contact_rec jtf_contact_cur%rowtype;
996
997
998 BEGIN
999 -- sjalasut, added for bug 4755238
1000 IF okc_context.get_okc_org_id IS NULL THEN
1001 okc_context.set_okc_org_context(NULL,NULL);
1002 END IF;
1003
1004 Open jtf_contact_cur(p_rle_code,p_cro_code, p_intent);
1005 Fetch jtf_contact_cur into jtf_contact_rec;
1006 If jtf_contact_cur%NOTFOUND Then
1007 --handle exception appropriately
1008 l_object_code := 'NOT FOUND';
1009 l_select_clause := 'NOT FOUND';
1010 l_from_clause := 'NOT FOUND';
1011 l_where_clause := 'NOT FOUND';
1012 l_order_by_clause := 'NOT FOUND';
1013 Else
1014 l_object_code := jtf_contact_rec.object_code;
1015 l_select_clause := jtf_contact_rec.select_clause;
1016 l_from_clause := jtf_contact_rec.from_clause;
1017 l_where_clause := jtf_contact_rec.where_clause;
1018 l_order_by_clause := jtf_contact_rec.order_by_clause;
1019
1020 END IF;
1021 l_sql_str := 'SELECT ' || l_select_clause ||' FROM '|| l_from_clause
1022 ||' WHERE '|| NVL(l_where_clause,'1=1 ')
1023 ||' ORDER BY '|| l_order_by_clause;
1024 CLOSE jtf_contact_cur;
1025 x_lov_sql := l_sql_str;
1026 x_jtot_object_code := l_object_code;
1027
1028 END get_party_contact_lov_sql;
1029
1030 END; -- Package Body OKL_VP_JTF_PARTY_NAME_PVT