[Home] [Help]
PACKAGE BODY: APPS.OKL_JTOT_CONTACT_EXTRACT_PUB
Source
1 package body okl_jtot_contact_extract_pub as
2 /* $Header: OKLPJCXB.pls 120.9 2007/08/21 07:30:13 pagarg noship $ */
3 G_APP_NAME CONSTANT VARCHAR2(3) := OKL_API.G_APP_NAME;
4 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKL_JTOT_CONTACT_EXTRACT_PUB';
5
6 /*
7 -- mvasudev, 09/09/2004
8 -- Added Constants to enable Business Event
9 */
10 G_WF_EVT_KHR_PARTY_REMOVE CONSTANT VARCHAR2(50) := 'oracle.apps.okl.la.lease_contract.remove_party';
11
12 G_WF_ITM_CONTRACT_ID CONSTANT VARCHAR2(20) := 'CONTRACT_ID';
13 G_WF_ITM_PARTY_ID CONSTANT VARCHAR2(15) := 'PARTY_ID';
14 G_WF_ITM_CONTRACT_PROCESS CONSTANT VARCHAR2(20) := 'CONTRACT_PROCESS';
15 G_WF_ITM_PARTY_ROLE_ID CONSTANT VARCHAR2(15) := 'PARTY_ROLE_ID';
16
17 --For Object Code 'OKX_PARTY'
18 CURSOR okx_party_csr(p_name VARCHAR2 , p_id1 VARCHAR2 , p_id2 VARCHAR2) IS
19 SELECT prv.id1,
20 prv.id2,
21 prv.name,
22 prv.description
23 FROM okx_parties_v prv
24 WHERE prv.name = NVL(p_name,prv.name)
25 AND prv.id1 = p_id1
26 AND prv.id2 = NVL(p_id2,prv.id2)
27 ORDER BY prv.name;
28
29 --For Object Code 'OKX_OPERUNIT'
30 CURSOR okx_operunit_csr(p_name VARCHAR2 , p_id1 VARCHAR2 , p_id2 VARCHAR2) IS
31 SELECT ord.id1,
32 ord.id2,
33 ord.name,
34 ord.description
35 FROM okx_organization_defs_v ord
36 WHERE ord.organization_type = 'OPERATING_UNIT'
37 AND ord.information_type = 'Operating Unit Information'
38 AND ord.name = NVL(p_name,ord.name)
39 AND ord.id1 = NVL(p_id1,ord.id1)
40 AND ord.id2 = NVL(p_id2,ord.id2)
41 ORDER BY ord.NAME;
42
43
44 --For Object Code 'OKX_VENDOR'
45 CURSOR okx_vendor_csr(p_name VARCHAR2 , p_id1 VARCHAR2 , p_id2 VARCHAR2) IS
46 SELECT vev.id1,
47 vev.id2,
48 vev.name,
49 vev.description
50 FROM okx_vendors_v vev
51 WHERE vev.name = NVL(p_name,vev.name)
52 AND vev.id1 = NVL(p_id1,vev.id1)
53 AND vev.id2 = NVL(p_id2,vev.id2)
54 ORDER BY vev.NAME;
55
56
57 FUNCTION GET_AK_PROMPT(p_ak_region IN VARCHAR2, p_ak_attribute IN VARCHAR2)
58 RETURN VARCHAR2 IS
59
60 CURSOR ak_prompt_csr(p_ak_region VARCHAR2, p_ak_attribute VARCHAR2) IS
61 --start modified abhsaxen for performance SQLID 20562543
62 select a.attribute_label_long
63 from ak_region_items ri, ak_regions r, ak_attributes_vl a
64 where ri.region_code = r.region_code
65 and ri.region_application_id = r.region_application_id
66 and ri.attribute_code = a.attribute_code
67 and ri.attribute_application_id = a.attribute_application_id
68 and ri.region_code = p_ak_region
69 and ri.attribute_code = p_ak_attribute
70 --end modified abhsaxen for performance SQLID 20562543
71 ;
72 l_ak_prompt AK_ATTRIBUTES_VL.attribute_label_long%TYPE;
73 BEGIN
74 OPEN ak_prompt_csr(p_ak_region, p_ak_attribute);
75 FETCH ak_prompt_csr INTO l_ak_prompt;
76 CLOSE ak_prompt_csr;
77 return(l_ak_prompt);
78 END;
79
80 FUNCTION GET_RLE_CODE_MEANING(p_rle_code IN VARCHAR2, p_chr_id NUMBER)
81 RETURN VARCHAR2 IS
82
83 CURSOR l_rle_code_meaning_csr
84 IS
85 select fnd.meaning
86 from okc_subclass_roles sur,
87 okc_k_headers_b chr,
88 fnd_lookup_values fnd
89 where fnd.lookup_code = sur.rle_code
90 and sur.rle_code = p_rle_code
91 and fnd.lookup_type = 'OKC_ROLE'
92 and fnd.language = userenv('LANG')
93 and sur.scs_code = chr.scs_code
94 and chr.id = p_chr_id
95 and nvl(sur.start_date,sysdate) <= sysdate
96 and nvl(sur.end_date,sysdate+1) > sysdate;
97
98 l_rle_code_meaning fnd_lookup_values.meaning%TYPE;
99 BEGIN
100 OPEN l_rle_code_meaning_csr;
101 FETCH l_rle_code_meaning_csr INTO l_rle_code_meaning;
102 CLOSE l_rle_code_meaning_csr;
103 return(l_rle_code_meaning);
104 END;
105
106 --Bug# 2761680
107 --Start of Comments
108 --Procedure : Get Party
109 --Description : Returns the SQL string for LOV of a party Role
110 --End of Comments
111 Procedure Get_Party (p_api_version IN NUMBER,
112 p_init_msg_list IN VARCHAR2 default OKC_API.G_FALSE,
113 x_return_status OUT NOCOPY VARCHAR2,
114 x_msg_count OUT NOCOPY NUMBER,
115 x_msg_data OUT NOCOPY VARCHAR2,
116 p_role_code IN VARCHAR2,
117 p_intent IN VARCHAR2,
118 p_id1 IN VARCHAR2,
119 p_id2 IN VARCHAR2,
120 p_name IN VARCHAR2,
121 x_select_clause OUT NOCOPY VARCHAR2,
122 x_from_clause OUT NOCOPY VARCHAR2,
123 x_where_clause OUT NOCOPY VARCHAR2,
124 x_order_by_clause OUT NOCOPY VARCHAR2,
125 x_object_code OUT NOCOPY VARCHAR2) is
126 CURSOR jtf_party_role_cur (p_role_code VARCHAR2, p_intent VARCHAR2) is
127 select job.object_code OBJECT_CODE,
128 job.object_code||'.ID1, '||
129 job.object_code||'.ID2, '||
130 job.object_code||'.NAME, '||
131 job.object_code||'.DESCRIPTION ' SELECT_CLAUSE,
132 from_table FROM_CLAUSE,
133 where_clause WHERE_CLAUSE,
134 order_by_clause ORDER_BY_CLAUSE
135 from jtf_objects_b job,
136 okc_role_sources rs
137 where job.object_code = rs.jtot_object_code
138 and nvl(job.start_date_active,sysdate) <= sysdate
139 and nvl(job.end_date_active,sysdate + 1) > sysdate
140 and rs.rle_code = p_role_code
141 and rs.start_date <= sysdate
142 and nvl(rs.end_date,sysdate+1) > sysdate
143 and rs.buy_or_sell = p_intent;
144 jtf_party_role_rec jtf_party_role_cur%rowtype;
145 l_query_string VARCHAR2(2000) default Null;
146 l_where_clause VARCHAR2(2000) default Null;
147 Begin
148 Open jtf_party_role_cur(p_role_code, p_intent);
149 Fetch jtf_party_role_cur into jtf_party_role_rec;
150 If jtf_party_role_cur%NOTFOUND Then
151 --handle exception appropriately
152 x_object_code := 'NOT FOUND';
153 x_select_clause := 'NOT FOUND';
154 x_from_clause := 'NOT FOUND';
155 x_where_clause := 'NOT FOUND';
156 x_order_by_clause := 'NOT FOUND';
157 Else
158 x_object_code := jtf_party_role_rec.object_code;
159 x_select_clause := jtf_party_role_rec.select_clause;
160 x_from_clause := jtf_party_role_rec.from_clause;
161 x_where_clause := jtf_party_role_rec.where_clause;
162 x_order_by_clause := jtf_party_role_rec.order_by_clause;
163 If p_id1 is not null and p_id2 is not null and p_name is null then
164 select x_where_clause || decode(x_where_clause,null,null,' AND ')||
165 ' ID1 = '||''''||p_id1||''''||' AND '||' ID2 = '||''''||p_id2||''''
166 into l_where_clause
167 from dual;
168 x_where_clause := l_where_clause;
169 Elsif p_name is not null then
170 select x_where_clause || decode(x_where_clause,null,null,' AND ')||
171 ' NAME like '||''''||p_name||'%'||''''
172 into l_where_clause
173 from dual;
174 End If;
175 End If;
176 Close jtf_party_role_cur;
177 End Get_Party;
178 --End Bug# 2761680
179
180 --Start of Comments
181 --Procedure : Get Party
182 --Description : Returns Name, Description for a given role or all the roles
183 -- attached to a contract
184 --End of Comments
185 Procedure Get_Party (p_api_version IN NUMBER,
186 p_init_msg_list IN VARCHAR2 default OKC_API.G_FALSE,
187 x_return_status OUT NOCOPY VARCHAR2,
188 x_msg_count OUT NOCOPY NUMBER,
189 x_msg_data OUT NOCOPY VARCHAR2,
190 p_chr_id IN VARCHAR2,
191 p_cle_id IN VARCHAR2,
192 p_role_code IN OKC_K_PARTY_ROLES_V.rle_code%Type,
193 p_intent IN VARCHAR2 default 'S',
194 x_party_tab out NOCOPY party_tab_type) is
195
196 CURSOR party_role_curs(p_chr_id IN NUMBER,
197 p_cle_id IN NUMBER,
198 p_dnz_chr_id IN NUMBER,
199 p_role_code IN VARCHAR2) is
200 select object1_id1,
201 object1_id2,
202 jtot_object1_code,
203 rle_code
204 from OKC_K_PARTY_ROLES_V
205 where rle_code = nvl(p_role_code,rle_code)
206 and nvl(cle_id,-99999) = p_cle_id
207 and nvl(chr_id,-99999) = p_chr_id
208 and dnz_chr_id = decode(p_chr_id,null,dnz_chr_id,p_dnz_chr_id)
209 order by rle_code;
210 party_role_rec party_role_curs%RowType;
211 l_select_clause varchar2(2000) default null;
212 l_from_clause varchar2(2000) default null;
213 l_where_clause varchar2(2000) default null;
214 l_order_by_clause varchar2(2000) default null;
215 l_query_string varchar2(2000) default null;
216 l_id1 OKC_K_PARTY_ROLES_V.OBJECT1_ID1%TYPE default Null;
217 l_id2 OKC_K_PARTY_ROLES_V.OBJECT1_ID2%TYPE default Null;
218 l_name VARCHAR2(250) Default Null;
219 l_Description VARCHAR2(250) Default Null;
220 l_object_code VARCHAR2(30) Default Null;
221 type party_curs_type is REF CURSOR;
222 party_curs party_curs_type;
223 i Number default 0;
224 l_chr_id Number;
225 l_dnz_chr_id Number;
226 l_cle_id Number;
227 Begin
228
229 If okl_context.get_okc_org_id is null then
230 okl_context.set_okc_org_context(p_chr_id => l_chr_id );
231 End If;
232
233
234 If p_chr_id is not null and p_cle_id is null
235 Then
236 l_chr_id := p_chr_id;
237 l_cle_id := -99999;
238 l_dnz_chr_id := p_chr_id;
239 ElsIf p_chr_id is null and p_cle_id is not null
240 Then
241 l_chr_id := -99999;
242 l_cle_id := p_cle_id;
243 l_dnz_chr_id := -99999;
244 ElsIf p_chr_id is not null and p_cle_id is not null
245 Then
246 l_chr_id := -99999;
247 l_cle_id := p_cle_id;
248 l_dnz_chr_id := p_chr_id;
249 Elsif p_chr_id is null and p_cle_id is null
250 Then
251 null; --raise appropriate exception here
252 End If;
253 Open party_role_curs(l_chr_id,l_cle_id,l_dnz_chr_id,p_role_code);
254 Loop
255 Fetch party_role_curs into party_role_rec;
256 Exit When party_role_curs%NotFound;
257 i := party_role_curs%rowcount;
258
259 -- introduced by suresh
260 l_object_code := party_role_rec.jtot_object1_code;
261
262 --Added by kthiruva 23-Sep-2003 Bug No.3156265
263
264 -- For Object Code is 'OKX_PARTY'
265
266
267 IF ( (party_role_rec.rle_code IN ('BROKER', 'DEALER','GUARANTOR','LESSEE' , 'MANUFACTURER' , 'PRIVATE_LABEL') AND p_intent ='S')
268 OR (party_role_rec.rle_code = 'INVESTOR' AND p_intent='B')) THEN
269
270 OPEN okx_party_csr(p_name => null,
271 p_id1 => party_role_rec.object1_id1,
272 p_id2 => party_role_rec.object1_id2);
273
274
275 l_id1 := Null;
276 l_id2 := Null;
277 l_name := Null;
278 l_description := Null;
279 Fetch okx_party_csr into l_id1,
280 l_id2,
281 l_name,
282 l_description;
283
284 If okx_party_csr%NotFound Then
285 Null;--raise appropriate exception here
286 End If;
287
288 x_party_tab(i).rle_code := party_role_rec.rle_code;
289 x_party_tab(i).id1 := l_id1;
290 x_party_tab(i).id2 := l_id2;
291 x_party_tab(i).name := l_name;
292 x_party_tab(i).description := l_description;
293 x_party_tab(i).object_code := l_object_code;
294 CLOSE okx_party_csr;
295 --bug# 2761680
296 --END IF;
297
298 -- For Object Code 'OKX_OPERUNIT'
299 ELSIF ( (party_role_rec.rle_code ='LESSOR' AND p_intent ='S') OR (party_role_rec.rle_code ='SYNDICATOR' AND p_intent ='B'))
300 THEN
301 OPEN okx_operunit_csr(p_name => null,
302 p_id1 => party_role_rec.object1_id1,
303 p_id2 => party_role_rec.object1_id2);
304
305
306 l_id1 := Null;
307 l_id2 := Null;
308 l_name := Null;
309 l_description := Null;
310 Fetch okx_operunit_csr into l_id1,
311 l_id2,
312 l_name,
313 l_description;
314
315 If okx_operunit_csr%NotFound Then
316 Null;--raise appropriate exception here
317 End If;
318
319 x_party_tab(i).rle_code := party_role_rec.rle_code;
320 x_party_tab(i).id1 := l_id1;
321 x_party_tab(i).id2 := l_id2;
322 x_party_tab(i).name := l_name;
323 x_party_tab(i).description := l_description;
324 x_party_tab(i).object_code := l_object_code;
325 CLOSE okx_operunit_csr;
326 --bug# 2761680
327 --END IF;
328
329 -- For Object Code 'OKX_VENDOR'
330
331 ELSIF ( party_role_rec.rle_code ='OKL_VENDOR' AND p_intent='S')
332 THEN
333 OPEN okx_vendor_csr(p_name => null,
334 p_id1 => party_role_rec.object1_id1,
335 p_id2 => party_role_rec.object1_id2);
336
337
338 l_id1 := Null;
339 l_id2 := Null;
340 l_name := Null;
341 l_description := Null;
342 Fetch okx_vendor_csr into l_id1,
343 l_id2,
344 l_name,
345 l_description;
346
347 If okx_vendor_csr%NotFound Then
348 Null;--raise appropriate exception here
349 End If;
350
351 x_party_tab(i).rle_code := party_role_rec.rle_code;
352 x_party_tab(i).id1 := l_id1;
353 x_party_tab(i).id2 := l_id2;
354 x_party_tab(i).name := l_name;
355 x_party_tab(i).description := l_description;
356 x_party_tab(i).object_code := l_object_code;
357 CLOSE okx_vendor_csr;
358 ELSE
359 --bug# 2761680 : User definable party roles will be fetched by dynamic sql as before
360 -- (taking care of OKC indirection)
361 Get_Party (p_api_version => '1.0',
362 p_init_msg_list => 'T' ,
363 x_return_status => x_return_status,
364 x_msg_count => x_msg_count,
365 x_msg_data => x_msg_data,
366 p_role_code => party_role_rec.rle_code,
367 p_intent => p_intent,
368 p_id1 => party_role_rec.object1_id1,
369 p_id2 => party_role_rec.object1_id2,
370 p_name => null,
371 x_select_clause => l_select_clause,
372 x_from_clause => l_from_clause ,
373 x_where_clause => l_where_clause,
374 x_order_by_clause => l_order_by_clause,
375 x_object_code => l_object_code);
376 l_query_string := 'SELECT '||ltrim(rtrim(l_select_clause,' '),' ')||' '||
377 'FROM '||ltrim(rtrim(l_from_clause,' '),' ')||' '||
378 'WHERE '||ltrim(rtrim(l_where_clause,' '),' ')||' '||
379 'ORDER BY '||ltrim(rtrim(l_order_by_clause,' '),' ');
380 Open party_curs for l_query_string;
381 l_id1 := Null;
382 l_id2 := Null;
383 l_name := Null;
384 l_description := Null;
385 Fetch party_curs into l_id1,
386 l_id2,
387 l_name,
388 l_description;
389 If party_curs%NotFound Then
390 Null;--raise appropriate exception here
391 End If;
392 x_party_tab(i).rle_code := party_role_rec.rle_code;
393 x_party_tab(i).id1 := l_id1;
394 x_party_tab(i).id2 := l_id2;
395 x_party_tab(i).name := l_name;
396 x_party_tab(i).description := l_description;
397 x_party_tab(i).object_code := l_object_code;
398
399 --bug# 2761680 : User definable party roles will be fetched by dynamic sql as before
400 -- (taking care of OKC indirection)
401 END IF;
402
403 l_object_code := null;
404
405 End Loop;
406 Close party_role_curs;
407 End Get_Party;
408 --Start of Comments
409 --Procedure : Get_Party
410 --Description : Fetches Name, Description of a Party role for a given
411 -- object1_id1 and object2_id2
412 --End of comments
413 Procedure Get_Party (p_api_version IN NUMBER,
414 p_init_msg_list IN VARCHAR2 default OKC_API.G_FALSE,
415 x_return_status OUT NOCOPY VARCHAR2,
416 x_msg_count OUT NOCOPY NUMBER,
417 x_msg_data OUT NOCOPY VARCHAR2,
418 p_role_code IN VARCHAR2,
419 p_intent IN VARCHAR2,
420 p_id1 IN VARCHAR2,
421 p_id2 IN VARCHAR2,
422 x_id1 OUT NOCOPY VARCHAR2,
423 x_id2 OUT NOCOPY VARCHAR2,
424 x_name OUT NOCOPY VARCHAR2,
425 x_description OUT NOCOPY VARCHAR2) is
426
427 l_select_clause varchar2(2000) default null;
428 l_from_clause varchar2(2000) default null;
429 l_where_clause varchar2(2000) default null;
430 l_order_by_clause varchar2(2000) default null;
431 l_query_string varchar2(2000) default null;
432 l_id1 OKC_K_PARTY_ROLES_V.OBJECT1_ID1%TYPE default Null;
433 l_id2 OKC_K_PARTY_ROLES_V.OBJECT1_ID2%TYPE default Null;
434 l_name VARCHAR2(250) Default Null;
435 l_Description VARCHAR2(250) Default Null;
436 l_object_code VARCHAR2(30) Default Null;
437 type party_curs_type is REF CURSOR;
438 party_curs party_curs_type;
439 Begin
440
441
442 --Added by kthiruva 23-Sep-2003 Bug No.3156265
443 -- For Object Code is 'OKX_PARTY'
444
445 IF ( (p_role_code IN ('BROKER', 'DEALER','GUARANTOR','LESSEE' , 'MANUFACTURER' , 'PRIVATE_LABEL') AND p_intent ='S')
446 OR (p_role_code = 'INVESTOR' AND p_intent='B'))
447 THEN
448
449 OPEN okx_party_csr(p_name => null,
450 p_id1 => p_id1,
451 p_id2 => p_id2);
452
453
454
455 l_id1 := Null;
456 l_id2 := Null;
457 l_name := Null;
458 l_description := Null;
459
460 FETCH okx_party_csr into l_id1,
461 l_id2,
462 l_name,
463 l_description;
464
465 If okx_party_csr%NotFound Then
466 Null;--raise appropriate exception here
467 End If;
468
469 x_id1 := l_id1;
470 x_id2 := l_id2;
471 x_name := l_name;
472 x_description := l_description;
473
474 Close okx_party_csr;
475 --Bug# 2761680
476 --END IF;
477
478 -- For Object Code 'OKX_OPERUNIT'
479 ELSIF ( (p_role_code ='LESSOR' AND p_intent ='S') OR (p_role_code ='SYNDICATOR' AND p_intent ='B'))
480 THEN
481
482 OPEN okx_operunit_csr(p_name => null,
483 p_id1 => p_id1,
484 p_id2 => p_id2);
485
486
487
488 l_id1 := Null;
489 l_id2 := Null;
490 l_name := Null;
491 l_description := Null;
492
493 FETCH okx_operunit_csr into l_id1,
494 l_id2,
495 l_name,
496 l_description;
497
498 If okx_operunit_csr%NotFound Then
499 Null;--raise appropriate exception here
500 End If;
501
502 x_id1 := l_id1;
503 x_id2 := l_id2;
504 x_name := l_name;
505 x_description := l_description;
506
507 Close okx_operunit_csr;
508 --Bug# 2761680
509 --END IF;
510
511 -- For Object Code 'OKX_VENDOR'
512
513 ELSIF ( p_role_code ='OKL_VENDOR' AND p_intent='S')
514 THEN
515
516 OPEN okx_vendor_csr(p_name => null,
517 p_id1 => p_id1,
518 p_id2 => p_id2);
519
520
521
522 l_id1 := Null;
523 l_id2 := Null;
524 l_name := Null;
525 l_description := Null;
526
527 FETCH okx_vendor_csr into l_id1,
528 l_id2,
529 l_name,
530 l_description;
531
532 If okx_vendor_csr%NotFound Then
533 Null;--raise appropriate exception here
534 End If;
535
536 x_id1 := l_id1;
537 x_id2 := l_id2;
538 x_name := l_name;
539 x_description := l_description;
540
541 Close okx_vendor_csr;
542 ELSE
543 --Bug# 2761680 : User definable party roles will have to be fetched the old-way(using OKC indirection)
544 Get_Party (p_api_version => '1.0',
545 p_init_msg_list => 'T' ,
546 x_return_status => x_return_status,
547 x_msg_count => x_msg_count,
548 x_msg_data => x_msg_data,
549 p_role_code => p_role_code,
550 p_intent => p_intent,
551 p_id1 => p_id1,
552 p_id2 => p_id2,
553 p_name => null,
554 x_select_clause => l_select_clause,
555 x_from_clause => l_from_clause ,
556 x_where_clause => l_where_clause,
557 x_order_by_clause => l_order_by_clause,
558 x_object_code => l_object_code);
559 l_query_string := 'SELECT '||ltrim(rtrim(l_select_clause,' '),' ')||' '||
560 'FROM '||ltrim(rtrim(l_from_clause,' '),' ')||' '||
561 'WHERE '||ltrim(rtrim(l_where_clause,' '),' ')||' '||
562 'ORDER BY '||ltrim(rtrim(l_order_by_clause,' '),' ');
563 Open party_curs for l_query_string;
564 l_id1 := Null;
565 l_id2 := Null;
566 l_name := Null;
567 l_description := Null;
568 Fetch party_curs into l_id1,
569 l_id2,
570 l_name,
571 l_description;
572 If party_curs%NotFound Then
573 Null;--raise appropriate exception here
574 End If;
575 x_id1 := l_id1;
576 x_id2 := l_id2;
577 x_name := l_name;
578 x_description := l_description;
579 Close party_curs;
580 --Bug# 2761680 : User definable party roles will have to be fetched the old-way(using OKC indirection)
581
582 END IF;
583 End Get_Party;
584
585
586
587 Procedure Get_Contact(p_api_version IN NUMBER,
588 p_init_msg_list IN VARCHAR2 default OKC_API.G_FALSE,
589 x_return_status OUT NOCOPY VARCHAR2,
590 x_msg_count OUT NOCOPY NUMBER,
591 x_msg_data OUT NOCOPY VARCHAR2,
592 p_role_code IN VARCHAR2,
593 p_contact_code IN VARCHAR2,
594 p_intent IN VARCHAR2 DEFAULT 'S',
595 p_id1 IN VARCHAR2,
596 p_id2 IN VARCHAR2,
597 p_name IN VARCHAR2,
598 x_select_clause OUT NOCOPY VARCHAR2,
599 x_from_clause OUT NOCOPY VARCHAR2,
600 x_where_clause OUT NOCOPY VARCHAR2,
601 x_order_by_clause OUT NOCOPY VARCHAR2,
602 x_object_code OUT NOCOPY VARCHAR2) is
603 CURSOR jtf_contacts_cur(p_contact_code VARCHAR2, p_role_code VARCHAR2, p_intent VARCHAR2) is
604 select job.object_code OBJECT_CODE,
605 job.object_code||'.ID1, '||
606 job.object_code||'.ID2, '||
607 job.object_code||'.NAME, '||
608 job.object_code||'.DESCRIPTION ' SELECT_CLAUSE,
609 from_table FROM_CLAUSE,
610 where_clause WHERE_CLAUSE,
611 order_by_clause ORDER_BY_CLAUSE
612 from jtf_objects_b job,
613 okc_contact_sources cs
614 where job.object_code = cs.jtot_object_code
615 and nvl(job.start_date_active,sysdate) <= sysdate
616 and nvl(job.end_date_active,sysdate + 1) > sysdate
617 and cs.cro_code = p_contact_code
618 and cs.rle_code = p_role_code
619 and cs.start_date <= sysdate
620 and nvl(cs.end_date,sysdate+1) > sysdate
621 and cs.buy_or_sell = p_intent;
622 jtf_contacts_rec jtf_contacts_cur%rowtype;
623 l_query_string VARCHAR2(2000) default Null;
624 l_where_clause VARCHAR2(2000) default Null;
625 type contact_curs_type is REF CURSOR;
626 contact_count_curs contact_curs_type;
627 l_rec_count NUMBER default 0;
628 Begin
629 If okc_context.get_okc_org_id is null then
630 okc_context.set_okc_org_context(204,204);
631 End If;
632 Open jtf_contacts_cur(p_contact_code,p_role_code,p_intent);
633 Fetch jtf_contacts_cur into jtf_contacts_rec;
634 If jtf_contacts_cur%NOTFOUND Then
635 --handle exception appropriately
636 x_object_code := 'NOT FOUND';
637 x_select_clause := 'NOT FOUND';
638 x_from_clause := 'NOT FOUND';
639 x_where_clause := 'NOT FOUND';
640 x_order_by_clause := 'NOT FOUND';
641 Else
642 x_object_code := jtf_contacts_rec.object_code;
643 x_select_clause := jtf_contacts_rec.select_clause;
644 x_from_clause := jtf_contacts_rec.from_clause;
645 x_where_clause := jtf_contacts_rec.where_clause;
646 x_order_by_clause := jtf_contacts_rec.order_by_clause;
647 If p_id1 is not null and p_id2 is not null and p_name is null then
648 select x_where_clause || decode(x_where_clause,null,null,' AND ')||
649 ' ID1 = '||''''||p_id1||''''||' AND '||' ID2 = '||''''||p_id2||''''
650 into l_where_clause
651 from dual;
652 x_where_clause := l_where_clause;
653 Elsif p_name is not null then
654 select x_where_clause || decode(x_where_clause,null,null,' AND ')||
655 ' NAME = '||''''||p_name||''''
656 into l_where_clause
657 from dual;
658 End If;
659 /*
660 select ' SELECT count(*) rec_count'||
661 ' FROM '||x_from_clause||
662 decode(x_where_clause,null,' ',' WHERE ')||x_where_clause||
663 decode(x_order_by_clause,null,null,' ORDER BY ')||x_order_by_clause
664 into l_query_string from dual;
665 l_rec_count := 0;
666 Open contact_count_curs for l_query_string;
667 Fetch contact_count_curs into l_rec_count;
668 If l_rec_count = 0
669 Then
670 Null; -- trying to avoid internal error
671 End If;
672 Close contact_count_curs;
673 x_record_count := l_rec_count;
674 */
675 End If;
676 Close jtf_contacts_cur;
677 END Get_Contact;
678
679
680 -- Start of comments
681 --
682 -- Procedure Name : get_contact
683 -- Description :
684 -- Business Rules :
685 -- Parameters :
686 -- Version : 1.0
687 -- End of comments
688 procedure get_contact(p_api_version IN NUMBER,
689 p_init_msg_list IN VARCHAR2 default OKC_API.G_FALSE,
690 x_return_status OUT NOCOPY VARCHAR2,
691 x_msg_count OUT NOCOPY NUMBER,
692 x_msg_data OUT NOCOPY VARCHAR2,
693 p_rle_code IN VARCHAR2,
694 p_cro_code IN VARCHAR2,
695 p_intent IN VARCHAR2,
696 p_id1 IN VARCHAR2,
697 p_id2 IN VARCHAR2,
698 x_id1 OUT NOCOPY VARCHAR2,
699 x_id2 OUT NOCOPY VARCHAR2,
700 x_name OUT NOCOPY VARCHAR2,
701 x_description OUT NOCOPY VARCHAR2) is
702 l_api_name CONSTANT VARCHAR2(30) := 'get_contact';
703 l_api_version CONSTANT NUMBER := 1;
704 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
705 begin
706 l_return_status := OKC_API.START_ACTIVITY(substr(l_api_name,1,26),
707 G_PKG_NAME,
708 p_init_msg_list,
709 l_api_version,
710 p_api_version,
711 '_PUB',
712 x_return_status);
713
714 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
715 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
716 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
717 RAISE OKC_API.G_EXCEPTION_ERROR;
718 END IF;
719 --
720 -- Call Before Logic Hook
721 --
722 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'B');
723 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
724 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
725 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
726 raise OKC_API.G_EXCEPTION_ERROR;
727 END IF;
728
729 OKL_JTOT_CONTACT_EXTRACT_PVT.get_contact(p_api_version,
730 p_init_msg_list,
731 x_return_status,
732 x_msg_count,
733 x_msg_data,
734 p_rle_code,
735 p_cro_code,
736 p_intent,
737 p_id1,
738 p_id2,
739 x_id1,
740 x_id2,
741 x_name,
742 x_description);
743
744 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
745 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
746 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
747 RAISE OKC_API.G_EXCEPTION_ERROR;
748 END IF;
749 --
750 -- Call After Logic Hook
751 --
752
753 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'A');
754 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
755 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
756 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
757 raise OKC_API.G_EXCEPTION_ERROR;
758 END IF;
759 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
760 EXCEPTION
761 WHEN OKC_API.G_EXCEPTION_ERROR THEN
762 x_return_status := OKC_API.HANDLE_EXCEPTIONS
763 (substr(l_api_name,1,26),
764 G_PKG_NAME,
765 'OKC_API.G_RET_STS_ERROR',
766 x_msg_count,
767 x_msg_data,
768 '_PUB');
769 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
770 x_return_status := OKC_API.HANDLE_EXCEPTIONS
771 (substr(l_api_name,1,26),
772 G_PKG_NAME,
773 'OKC_API.G_RET_STS_UNEXP_ERROR',
774 x_msg_count,
775 x_msg_data,
776 '_PUB');
777 WHEN OTHERS THEN
778 x_return_status := OKC_API.HANDLE_EXCEPTIONS
779 (substr(l_api_name,1,26),
780 G_PKG_NAME,
781 'OTHERS',
782 x_msg_count,
783 x_msg_data,
784 '_PUB');
785 end get_contact;
786
787 Procedure Validate_Party (p_api_version IN NUMBER,
788 p_init_msg_list IN VARCHAR2 default OKC_API.G_FALSE,
789 x_return_status OUT NOCOPY VARCHAR2,
790 x_msg_count OUT NOCOPY NUMBER,
791 x_msg_data OUT NOCOPY VARCHAR2,
792 p_chr_id IN NUMBER,
793 p_cle_id IN NUMBER,
794 p_cpl_id IN NUMBER,
795 p_lty_code IN VARCHAR2,
796 p_rle_code IN VARCHAR2,
797 p_id1 IN OUT NOCOPY VARCHAR2,
798 p_id2 IN OUT NOCOPY VARCHAR2,
799 p_name IN VARCHAR2,
800 p_object_code IN VARCHAR2
801 ) is
802 l_select_clause varchar2(2000) default null;
803 l_from_clause varchar2(2000) default null;
804 l_where_clause varchar2(2000) default null;
805 l_order_by_clause varchar2(2000) default null;
806 l_query_string varchar2(2000) default null;
807
808 l_id1 OKC_K_PARTY_ROLES_V.OBJECT1_ID1%TYPE default Null;
809 l_id2 OKC_K_PARTY_ROLES_V.OBJECT1_ID2%TYPE default Null;
810 l_name VARCHAR2(250) Default Null;
811 l_description VARCHAR2(250) Default Null;
812 l_object_code VARCHAR2(30) Default Null;
813
814 l_id11 OKC_K_PARTY_ROLES_V.OBJECT1_ID1%TYPE default Null;
815 l_id22 OKC_K_PARTY_ROLES_V.OBJECT1_ID2%TYPE default Null;
816
817 type party_curs_type is REF CURSOR;
818 party_curs party_curs_type;
819
820 row_count Number default 0;
821
822 l_chr_id okl_k_headers.id%type;
823 l_rle_code okc_k_party_roles_v.rle_code%type;
824 l_cle_id okl_k_lines.id%type;
825 l_lty_code okc_line_styles_b.lty_code%type;
826
827 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Party';
828 l_api_version CONSTANT NUMBER := 1.0;
829
830 -- x_return_status := OKC_API.G_RET_STS_SUCCESS;
831
832 ERR_MSG VARCHAR2(50) := 'DEFAULT';
833
834 l_amt_ak_prompt AK_ATTRIBUTES_VL.attribute_label_long%type;
835
836
837 CURSOR check_party_csr(p_chr_id NUMBER, p_rle_code VARCHAR2,p_id1 VARCHAR2, p_id2 VARCHAR2) IS
838 --Start modified abhsaxen for performance SQLID 20562561
839 select count(1)
840 from okc_k_party_roles_b
841 where dnz_chr_id = p_chr_id
842 and chr_id = p_chr_id
843 and rle_code = p_rle_code
844 and object1_id1 = p_id1
845 and object1_id2 = p_id2
846 --end modified abhsaxen for performance SQLID 20562561
847 ;
848 CURSOR get_party_csr(p_cpl_id NUMBER) IS
849 SELECT rle_code, object1_id1, object1_id2
850 FROM okc_k_party_roles_v
851 WHERE id = p_cpl_id;
852
853 Begin
854
855 If okl_context.get_okc_org_id is null then
856 l_chr_id := p_chr_id;
857 okl_context.set_okc_org_context(p_chr_id => l_chr_id );
858 End If;
859
860 -- call START_ACTIVITY to create savepoint, check compatibility
861 -- and initialize message list
862 x_return_status := OKC_API.START_ACTIVITY(
863 p_api_name => l_api_name,
864 p_pkg_name => g_pkg_name,
865 p_init_msg_list => p_init_msg_list,
866 l_api_version => l_api_version,
867 p_api_version => p_api_version,
868 p_api_type => '_PUB',
869 x_return_status => x_return_status);
870
871 If ( p_chr_id is null or p_chr_id = OKC_API.G_MISS_NUM)
872 Then
873 OKC_API.SET_MESSAGE(p_app_name => g_app_name, p_msg_name => 'Missing_chr_id');
874 raise OKC_API.G_EXCEPTION_ERROR;
875 ElsIf ( p_rle_code is null or p_rle_code = OKC_API.G_MISS_CHAR)
876 Then
877 OKC_API.SET_MESSAGE(p_app_name => g_app_name, p_msg_name => 'Missing_rle_code');
878 raise OKC_API.G_EXCEPTION_ERROR;
879 ElsIf ( p_name is null or p_name = OKC_API.G_MISS_CHAR)
880 Then
881 OKC_API.SET_MESSAGE(p_app_name => g_app_name, p_msg_name => 'Missing_name');
882 raise OKC_API.G_EXCEPTION_ERROR;
883 -- raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
884 End If;
885
886 l_amt_ak_prompt := GET_AK_PROMPT('OKL_LA_CONTRACT_PRTS', 'OKL_LA_KPRTS_NAME');
887
888
889
890
891 --Added by kthiruva 23-Sep-2003 Bug No.3156265
892
893 -- For Object Code is 'OKX_PARTY'
894
895 IF (p_rle_code IN ('BROKER', 'DEALER','GUARANTOR','LESSEE' , 'MANUFACTURER' , 'PRIVATE_LABEL','INVESTOR','EXTERNAL_PARTY')) -- added 'EXTERNAL_PARTY for bug 4893490
896
897 THEN
898
899 OPEN okx_party_csr(p_name => p_name,
900 p_id1 => p_id1,
901 p_id2 => p_id2);
902
903
904 l_id1 := Null;
905 l_id2 := Null;
906 l_name := Null;
907 l_description := Null;
908
909 FETCH okx_party_csr into l_id1,l_id2,l_name,l_description;
910
911 If okx_party_csr%NotFound Then
912 x_return_status := OKC_API.g_ret_sts_error;
913 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
914 p_msg_name => 'OKL_REQUIRED_VALUE',
915 p_token1 => 'COL_NAME',
916 p_token1_value => l_amt_ak_prompt); --please_select_a_value_from_lov
917 raise OKC_API.G_EXCEPTION_ERROR;
918 End If;
919
920 l_id11 := l_id1;
921 l_id22 := l_id2;
922
923 Fetch okx_party_csr into l_id1,l_id2,l_name,l_description;
924 If okx_party_csr%Found Then
925
926 If( p_id1 is null or p_id1 = OKC_API.G_MISS_CHAR) then
927 x_return_status := OKC_API.g_ret_sts_error;
928 OKC_API.SET_MESSAGE(p_app_name => g_app_name, p_msg_name => 'OKL_REQUIRED_VALUE',
929 p_token1 => 'COL_NAME',
930 p_token1_value => l_amt_ak_prompt); --please_select_a_value_from_lov
931 raise OKC_API.G_EXCEPTION_ERROR;
932 End If;
933
934 If( p_id2 is null or p_id2 = OKC_API.G_MISS_CHAR) then
935 x_return_status := OKC_API.g_ret_sts_error;
936 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
937 p_msg_name => 'OKL_REQUIRED_VALUE',
938 p_token1 => 'COL_NAME',
939 p_token1_value => l_amt_ak_prompt); --please_select_a_value_from_lov
940 raise OKC_API.G_EXCEPTION_ERROR;
941
942
943 End If;
944
945 If(l_id1 = p_id1 and l_id2 = p_id2) Then
946 l_id11 := l_id1;
947 l_id22 := l_id2;
948 row_count := 1;
949 Else
950
951 Loop
952
953 Fetch okx_party_csr into l_id1,l_id2,l_name,l_description;
954 If(l_id1 = p_id1 and l_id2 = p_id2) Then
955 l_id11 := l_id1;
956 l_id22 := l_id2;
957 row_count := 1;
958 Exit;
959 End If;
960 Exit When okx_party_csr%NotFound;
961
962 End Loop;
963
964 End If;
965
966 If row_count <> 1 Then
967 x_return_status := OKC_API.g_ret_sts_error;
968 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
969 p_msg_name => 'OKL_REQUIRED_VALUE',
970 p_token1 => 'COL_NAME',
971 p_token1_value => l_amt_ak_prompt); --please_select_a_value_from_lov
972 raise OKC_API.G_EXCEPTION_ERROR;
973 End If;
974
975 End If;
976
977 p_id1 := l_id11;
978 p_id2 := l_id22;
979
980 Close okx_party_csr;
981 END IF;
982
983 -- For Object Code 'OKX_OPERUNIT'
984 IF (p_rle_code IN ('LESSOR','SYNDICATOR') )
985 THEN
986 OPEN okx_operunit_csr(p_name => p_name,
987 p_id1 => l_id1,
988 p_id2 => p_id2);
989
990
991 l_id1 := Null;
992 l_id2 := Null;
993 l_name := Null;
994 l_description := Null;
995
996 FETCH okx_operunit_csr into l_id1,l_id2,l_name,l_description;
997
998 If okx_operunit_csr%NotFound Then
999 x_return_status := OKC_API.g_ret_sts_error;
1000 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1001 p_msg_name => 'OKL_REQUIRED_VALUE',
1002 p_token1 => 'COL_NAME',
1003 p_token1_value => l_amt_ak_prompt); --please_select_a_value_from_lov
1004 raise OKC_API.G_EXCEPTION_ERROR;
1005 End If;
1006
1007 l_id11 := l_id1;
1008 l_id22 := l_id2;
1009
1010 Fetch okx_operunit_csr into l_id1,l_id2,l_name,l_description;
1011 If okx_operunit_csr%Found Then
1012
1013 If( p_id1 is null or p_id1 = OKC_API.G_MISS_CHAR) then
1014 x_return_status := OKC_API.g_ret_sts_error;
1015 OKC_API.SET_MESSAGE(p_app_name => g_app_name, p_msg_name => 'OKL_REQUIRED_VALUE',
1016 p_token1 => 'COL_NAME',
1017 p_token1_value => l_amt_ak_prompt); --please_select_a_value_from_lov
1018 raise OKC_API.G_EXCEPTION_ERROR;
1019 End If;
1020
1021 If( p_id2 is null or p_id2 = OKC_API.G_MISS_CHAR) then
1022 x_return_status := OKC_API.g_ret_sts_error;
1023 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1024 p_msg_name => 'OKL_REQUIRED_VALUE',
1025 p_token1 => 'COL_NAME',
1026 p_token1_value => l_amt_ak_prompt); --please_select_a_value_from_lov
1027 raise OKC_API.G_EXCEPTION_ERROR;
1028
1029
1030 End If;
1031
1032 Loop
1033 If(l_id1 = p_id1 and l_id2 = p_id2) Then
1034 l_id11 := l_id1;
1035 l_id22 := l_id2;
1036 row_count := 1;
1037 Exit;
1038 End If;
1039 Fetch okx_operunit_csr into l_id1,l_id2,l_name,l_description;
1040 Exit When okx_operunit_csr%NotFound;
1041 End Loop;
1042
1043 If row_count <> 1 Then
1044 x_return_status := OKC_API.g_ret_sts_error;
1045 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1046 p_msg_name => 'OKL_REQUIRED_VALUE',
1047 p_token1 => 'COL_NAME',
1048 p_token1_value => l_amt_ak_prompt); --please_select_a_value_from_lov
1049 raise OKC_API.G_EXCEPTION_ERROR;
1050 End If;
1051
1052 End If;
1053
1054 p_id1 := l_id11;
1055 p_id2 := l_id22;
1056
1057 Close okx_operunit_csr;
1058 END IF;
1059
1060 -- For Object Code 'OKX_VENDOR'
1061
1062 IF ( p_rle_code ='OKL_VENDOR' )
1063 THEN
1064 OPEN okx_vendor_csr(p_name => p_name,
1065 p_id1 => l_id1,
1066 p_id2 => p_id2);
1067
1068
1069 l_id1 := Null;
1070 l_id2 := Null;
1071 l_name := Null;
1072 l_description := Null;
1073
1074 FETCH okx_vendor_csr into l_id1,l_id2,l_name,l_description;
1075
1076 If okx_vendor_csr%NotFound Then
1077 x_return_status := OKC_API.g_ret_sts_error;
1078 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1079 p_msg_name => 'OKL_REQUIRED_VALUE',
1080 p_token1 => 'COL_NAME',
1081 p_token1_value => l_amt_ak_prompt); --please_select_a_value_from_lov
1082 raise OKC_API.G_EXCEPTION_ERROR;
1083 End If;
1084
1085 l_id11 := l_id1;
1086 l_id22 := l_id2;
1087
1088 Fetch okx_vendor_csr into l_id1,l_id2,l_name,l_description;
1089 If okx_vendor_csr%Found Then
1090
1091 If( p_id1 is null or p_id1 = OKC_API.G_MISS_CHAR) then
1092 x_return_status := OKC_API.g_ret_sts_error;
1093 OKC_API.SET_MESSAGE(p_app_name => g_app_name, p_msg_name => 'OKL_REQUIRED_VALUE',
1094 p_token1 => 'COL_NAME',
1095 p_token1_value => l_amt_ak_prompt); --please_select_a_value_from_lov
1096 raise OKC_API.G_EXCEPTION_ERROR;
1097 End If;
1098
1099 If( p_id2 is null or p_id2 = OKC_API.G_MISS_CHAR) then
1100 x_return_status := OKC_API.g_ret_sts_error;
1101 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1102 p_msg_name => 'OKL_REQUIRED_VALUE',
1103 p_token1 => 'COL_NAME',
1104 p_token1_value => l_amt_ak_prompt); --please_select_a_value_from_lov
1105 raise OKC_API.G_EXCEPTION_ERROR;
1106
1107
1108 End If;
1109
1110 Loop
1111 If(l_id1 = p_id1 and l_id2 = p_id2) Then
1112 l_id11 := l_id1;
1113 l_id22 := l_id2;
1114 row_count := 1;
1115 Exit;
1116 End If;
1117 Fetch okx_vendor_csr into l_id1,l_id2,l_name,l_description;
1118 Exit When okx_vendor_csr%NotFound;
1119 End Loop;
1120
1121 If row_count <> 1 Then
1122 x_return_status := OKC_API.g_ret_sts_error;
1123 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1124 p_msg_name => 'OKL_REQUIRED_VALUE',
1125 p_token1 => 'COL_NAME',
1126 p_token1_value => l_amt_ak_prompt); --please_select_a_value_from_lov
1127 raise OKC_API.G_EXCEPTION_ERROR;
1128 End If;
1129
1130 End If;
1131
1132 p_id1 := l_id11;
1133 p_id2 := l_id22;
1134
1135 Close okx_vendor_csr;
1136 END IF;
1137 If p_lty_code is null or p_lty_code = OKC_API.G_MISS_CHAR Then
1138
1139 If p_cpl_id is null or p_cpl_id = OKC_API.G_MISS_NUM Then
1140 OPEN check_party_csr(p_chr_id, p_rle_code, p_id1, p_id2 );
1141 FETCH check_party_csr INTO row_count;
1142 CLOSE check_party_csr;
1143 If row_count = 1 Then
1144 x_return_status := OKC_API.g_ret_sts_error;
1145 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1146 p_msg_name => 'OKL_LLA_DUP_SELECTION',
1147 p_token1 => 'TOKEN',
1148 p_token1_value => l_amt_ak_prompt); --Party_name_already_exists
1149 raise OKC_API.G_EXCEPTION_ERROR;
1150 End If;
1151 Else
1152 OPEN get_party_csr(p_cpl_id );
1153 FETCH get_party_csr INTO l_rle_code, l_id1, l_id2;
1154 CLOSE get_party_csr;
1155
1156 If l_rle_code = p_rle_code and l_id1 <> p_id1 Then
1157 OPEN check_party_csr(p_chr_id, p_rle_code, p_id1, p_id2);
1158 FETCH check_party_csr INTO row_count;
1159 CLOSE check_party_csr;
1160 If row_count = 1 Then
1161 x_return_status := OKC_API.g_ret_sts_error;
1162 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1163 p_msg_name => 'OKL_LLA_DUP_SELECTION',
1164 p_token1 => 'TOKEN',
1165 p_token1_value => l_amt_ak_prompt); --Party_name_already_exists
1166 raise OKC_API.G_EXCEPTION_ERROR;
1167 End If;
1168 End If;
1169 End If;
1170
1171 End If;
1172
1173 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1174
1175 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
1176
1177 EXCEPTION
1178 when OKC_API.G_EXCEPTION_ERROR then
1179 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1180 p_api_name => l_api_name,
1181 p_pkg_name => g_pkg_name,
1182 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
1183 x_msg_count => x_msg_count,
1184 x_msg_data => x_msg_data,
1185 p_api_type => '_PUB');
1186 IF okx_party_csr%ISOPEN THEN
1187 CLOSE okx_party_csr;
1188 END IF;
1189 IF okx_operunit_csr%ISOPEN THEN
1190 CLOSE okx_operunit_csr;
1191 END IF;
1192 IF okx_vendor_csr%ISOPEN THEN
1193 CLOSE okx_vendor_csr;
1194 END IF;
1195
1196
1197 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
1198 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1199 p_api_name => l_api_name,
1200 p_pkg_name => g_pkg_name,
1201 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
1202 x_msg_count => x_msg_count,
1203 x_msg_data => x_msg_data,
1204 p_api_type => '_PUB');
1205 IF okx_party_csr%ISOPEN THEN
1206 CLOSE okx_party_csr;
1207 END IF;
1208 IF okx_operunit_csr%ISOPEN THEN
1209 CLOSE okx_operunit_csr;
1210 END IF;
1211 IF okx_vendor_csr%ISOPEN THEN
1212 CLOSE okx_vendor_csr;
1213 END IF;
1214
1215 when OTHERS then
1216 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1217 p_api_name => l_api_name,
1218 p_pkg_name => g_pkg_name,
1219 p_exc_name => 'OTHERS',
1220 x_msg_count => x_msg_count,
1221 x_msg_data => x_msg_data,
1222 p_api_type => '_PUB');
1223 IF okx_party_csr%ISOPEN THEN
1224 CLOSE okx_party_csr;
1225 END IF;
1226 IF okx_operunit_csr%ISOPEN THEN
1227 CLOSE okx_operunit_csr;
1228 END IF;
1229 IF okx_vendor_csr%ISOPEN THEN
1230 CLOSE okx_vendor_csr;
1231 END IF;
1232
1233
1234 End Validate_Party;
1235
1236
1237
1238 Procedure Delete_Party (p_api_version IN NUMBER,
1239 p_init_msg_list IN VARCHAR2 default OKC_API.G_FALSE,
1240 x_return_status OUT NOCOPY VARCHAR2,
1241 x_msg_count OUT NOCOPY NUMBER,
1242 x_msg_data OUT NOCOPY VARCHAR2,
1243 p_chr_id IN NUMBER,
1244 p_cpl_id IN NUMBER
1245 ) AS
1246
1247 l_api_name VARCHAR2(30) := 'Validate_Party';
1248 l_api_version CONSTANT NUMBER := 1.0;
1249 l_chr_id NUMBER;
1250 l_rle_code_meaning fnd_lookup_values.meaning%type;
1251
1252 row_count number;
1253
1254 l_k_vendor_id1 okc_k_party_roles_v.object1_id1%type;
1255 l_rle_code okc_k_party_roles_v.rle_code%type;
1256 l_id1 okx_vendor_sites_v.vendor_id%type;
1257
1258 l_id okc_rg_party_roles_v.id%type;
1259 l_rgp_id okc_rg_party_roles_v.rgp_id%type;
1260 l_rul_id okc_rules_v.id%type;
1261
1262 lp_cplv_rec OKL_OKC_MIGRATION_PVT.cplv_rec_type;
1263 lx_cplv_rec OKL_OKC_MIGRATION_PVT.cplv_rec_type;
1264
1265 lp_rgpv_rec OKL_OKC_MIGRATION_PVT.rgpv_rec_type;
1266 lx_rgpv_rec OKL_OKC_MIGRATION_PVT.rgpv_rec_type;
1267 lp_rulv_rec Okl_Rule_Pub.rulv_rec_type;
1268 lx_rulv_rec Okl_Rule_Pub.rulv_rec_type;
1269
1270 lp_rmpv_rec OKL_OKC_MIGRATION_PVT.rmpv_rec_type;
1271 lx_rmpv_rec OKL_OKC_MIGRATION_PVT.rmpv_rec_type;
1272
1273 lp_rulv_tbl Okl_Rule_Pub.rulv_tbl_type;
1274
1275 cursor l_vendor_csr is
1276 select object1_id1,rle_code from okc_k_party_roles_v
1277 where id = p_cpl_id;
1278
1279 -- sjalasut, modified the cursor to include okl_txl_ap_inv_lns_all_b and khr_id
1280 -- be referred from this table instead of okl_trx_ap_invoices_b. changes made
1281 -- as part of OKLR12B disbursements project
1282 cursor l_funding_chk_csr(p_id1 varchar2) IS
1283 select count(*) from okx_vendor_sites_v
1284 where exists (select 1 from okl_trx_ap_invoices_b a
1285 ,okl_txl_ap_inv_lns_all_b b
1286 where a.id = b.tap_id
1287 and a.ipvs_id = okx_vendor_sites_v.id1
1288 and b.khr_id = p_chr_id)
1289 and vendor_id = p_id1;
1290
1291 cursor l_line_csr(p_id1 varchar2) IS
1292 --Start modified abhsaxen for performance SQLID 20562568
1293 select count(*)
1294 from okc_k_party_roles_b
1295 where chr_id is null
1296 and cle_id is not null
1297 and rle_code = 'OKL_VENDOR'
1298 and dnz_chr_id = p_chr_id
1299 and object1_id1 = p_id1
1300 --end modified abhsaxen for performance SQLID 20562568
1301 ;
1302 cursor l_rg_party_csr is
1303 select rgpr.id ,rgpr.rgp_id
1304 from okc_rg_party_roles_v rgpr, okc_rule_groups_v rgp
1305 where rgpr.dnz_chr_id = p_chr_id
1306 and rgpr.cpl_id = p_cpl_id
1307 and rgpr.dnz_chr_id = rgp.dnz_chr_id
1308 and rgpr.dnz_chr_id = rgp.chr_id
1309 and rgpr.rgp_id = rgp.id;
1310
1311 cursor l_r_party_csr is
1312 select rul.id
1313 from okc_rg_party_roles_v rgpr, okc_rule_groups_v rgp, okc_rules_v rul
1314 where rgpr.dnz_chr_id = p_chr_id
1315 and rgpr.cpl_id = p_cpl_id
1316 and rgpr.dnz_chr_id = rgp.dnz_chr_id
1317 and rgpr.dnz_chr_id = rgp.chr_id
1318 and rgpr.dnz_chr_id = rul.dnz_chr_id
1319 and rgpr.rgp_id = rgp.id
1320 and rgp.id = rul.rgp_id;
1321
1322 i Number default 0;
1323
1324 --Bug# 4558486
1325 lp_kplv_rec OKL_K_PARTY_ROLES_PVT.kplv_rec_type;
1326 lx_kplv_rec OKL_K_PARTY_ROLES_PVT.kplv_rec_type;
1327
1328 /*
1329 -- mvasudev, 09/09/2004
1330 -- Added PROCEDURE to enable Business Event
1331 */
1332 PROCEDURE raise_business_event(
1333 x_return_status OUT NOCOPY VARCHAR2
1334 )
1335 IS
1336 l_process VARCHAR2(20);
1337 l_parameter_list wf_parameter_list_t;
1338 BEGIN
1339 IF ( okl_lla_util_pvt.is_lease_contract(p_chr_id) = OKL_API.G_TRUE)
1340 THEN
1341
1342 l_process := Okl_Lla_Util_Pvt.get_contract_process(p_chr_id);
1343
1344 wf_event.AddParameterToList(G_WF_ITM_CONTRACT_ID,p_chr_id,l_parameter_list);
1345 --vthiruva..04-jan-2004.. Modified to pass object1_id1 as party id and
1346 --added party_role_id to list of paramters passed to raise business event.
1347 wf_event.AddParameterToList(G_WF_ITM_PARTY_ID,l_k_vendor_id1,l_parameter_list);
1348 wf_event.AddParameterToList(G_WF_ITM_PARTY_ROLE_ID,p_cpl_id,l_parameter_list);
1349 wf_event.AddParameterToList(G_WF_ITM_CONTRACT_PROCESS,l_process,l_parameter_list);
1350
1351 OKL_WF_PVT.raise_event (p_api_version => p_api_version,
1352 p_init_msg_list => p_init_msg_list,
1353 x_return_status => x_return_status,
1354 x_msg_count => x_msg_count,
1355 x_msg_data => x_msg_data,
1356 p_event_name => G_WF_EVT_KHR_PARTY_REMOVE,
1357 p_parameters => l_parameter_list);
1358 END IF;
1359
1360 EXCEPTION
1361 WHEN OTHERS THEN
1362 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1363 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1364 END raise_business_event;
1365
1366
1367 /*
1368 -- mvasudev, 09/09/2004
1369 -- END, PROCEDURE to enable Business Event
1370 */
1371
1372 BEGIN
1373
1374
1375 If okl_context.get_okc_org_id is null then
1376 l_chr_id := p_chr_id;
1377 okl_context.set_okc_org_context(p_chr_id => l_chr_id );
1378 End If;
1379
1380 x_return_status := OKC_API.START_ACTIVITY(
1381 p_api_name => l_api_name,
1382 p_pkg_name => g_pkg_name,
1383 p_init_msg_list => p_init_msg_list,
1384 l_api_version => l_api_version,
1385 p_api_version => p_api_version,
1386 p_api_type => g_api_type,
1387 x_return_status => x_return_status);
1388
1389 -- check if activity started successfully
1390 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1391 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1392 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
1393 raise OKC_API.G_EXCEPTION_ERROR;
1394 End If;
1395
1396 If ( p_chr_id is null or p_chr_id = OKC_API.G_MISS_NUM)
1397 Then
1398 OKC_API.SET_MESSAGE(p_app_name => g_app_name, p_msg_name => 'Missing_chr_id');
1399 raise OKC_API.G_EXCEPTION_ERROR;
1400 ElsIf ( p_cpl_id is null or p_cpl_id = OKC_API.G_MISS_NUM)
1401 Then
1402 OKC_API.SET_MESSAGE(p_app_name => g_app_name, p_msg_name => 'Missing_cpl_id');
1403 raise OKC_API.G_EXCEPTION_ERROR;
1404 End If;
1405
1406 open l_vendor_csr;
1407 fetch l_vendor_csr into l_k_vendor_id1,l_rle_code;
1408 close l_vendor_csr;
1409
1410 If l_rle_code = 'LESSEE' or l_rle_code = 'LESSOR' Then
1411 x_return_status := OKC_API.g_ret_sts_error;
1412 l_rle_code_meaning := GET_RLE_CODE_MEANING(l_rle_code,p_chr_id);
1413 OKC_API.SET_MESSAGE( p_app_name => g_app_name
1414 , p_msg_name => 'OKL_LLA_DELETE_PARTY1'
1415 , p_token1 => 'COL_NAME'
1416 , p_token1_value => l_rle_code_meaning
1417 );
1418 raise OKC_API.G_EXCEPTION_ERROR;
1419 End If;
1420
1421 --Bug# 3340949:
1422 --If l_rle_code = 'GUARANTOR' or l_rle_code = 'PRIVATE_LABEL' or l_rle_code = 'OKL_VENDOR' Then
1423
1424 If l_rle_code = 'OKL_VENDOR' Then
1425
1426 open l_funding_chk_csr(l_k_vendor_id1);
1427 fetch l_funding_chk_csr into row_count;
1428 close l_funding_chk_csr;
1429
1430 If row_count <> 0 Then
1431 x_return_status := OKC_API.g_ret_sts_error;
1432 l_rle_code_meaning := GET_RLE_CODE_MEANING(l_rle_code,p_chr_id);
1433 OKC_API.SET_MESSAGE( p_app_name => g_app_name
1434 , p_msg_name => 'OKL_LLA_DELETE_PARTY'
1435 , p_token1 => 'COL_NAME'
1436 , p_token1_value => l_rle_code_meaning
1437 );
1438 raise OKC_API.G_EXCEPTION_ERROR;
1439 End If;
1440
1441 open l_line_csr(l_k_vendor_id1);
1442 fetch l_line_csr into row_count;
1443 close l_line_csr;
1444
1445 If row_count <> 0 Then
1446 x_return_status := OKC_API.g_ret_sts_error;
1447 l_rle_code_meaning := GET_RLE_CODE_MEANING(l_rle_code,p_chr_id);
1448 OKC_API.SET_MESSAGE( p_app_name => g_app_name
1449 , p_msg_name => 'OKL_LLA_DELETE_PARTY'
1450 , p_token1 => 'COL_NAME'
1451 , p_token1_value => l_rle_code_meaning
1452 );
1453 raise OKC_API.G_EXCEPTION_ERROR;
1454 End If;
1455 End If;
1456
1457 l_id := null;
1458 l_rgp_id := null;
1459 l_rul_id := null;
1460
1461 /*
1462 OPEN l_r_party_csr;
1463 Loop
1464 Fetch l_r_party_csr into l_rul_id;
1465 Exit When l_r_party_csr%NotFound;
1466 i := l_r_party_csr%rowcount;
1467 lp_rulv_tbl(i).id := l_rul_id;
1468 End Loop;
1469 CLOSE l_r_party_csr;
1470
1471 OKL_RULE_PUB.delete_rule(
1472 p_api_version => p_api_version,
1473 p_init_msg_list => p_init_msg_list,
1474 x_return_status => x_return_status,
1475 x_msg_count => x_msg_count,
1476 x_msg_data => x_msg_data,
1477 p_rulv_tbl => lp_rulv_tbl);
1478
1479 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1480 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1481 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
1482 raise OKC_API.G_EXCEPTION_ERROR;
1483 End If;
1484 */
1485
1486 open l_rg_party_csr;
1487 fetch l_rg_party_csr into l_id, l_rgp_id;
1488 close l_rg_party_csr;
1489
1490 lp_rgpv_rec.id := l_rgp_id;
1491 OKL_RULE_PUB.delete_rule_group(
1492 p_api_version => p_api_version,
1493 p_init_msg_list => p_init_msg_list,
1494 x_return_status => x_return_status,
1495 x_msg_count => x_msg_count,
1496 x_msg_data => x_msg_data,
1497 p_rgpv_rec => lp_rgpv_rec);
1498
1499 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1500 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1501 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
1502 raise OKC_API.G_EXCEPTION_ERROR;
1503 End If;
1504
1505 lp_rmpv_rec.id := l_id;
1506 OKL_RULE_PUB.delete_rg_mode_pty_role(
1507 p_api_version => p_api_version,
1508 p_init_msg_list => p_init_msg_list,
1509 x_return_status => x_return_status,
1510 x_msg_count => x_msg_count,
1511 x_msg_data => x_msg_data,
1512 p_rmpv_rec => lp_rmpv_rec);
1513
1514 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1515 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1516 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
1517 raise OKC_API.G_EXCEPTION_ERROR;
1518 End If;
1519
1520 lp_cplv_rec.id := p_cpl_id;
1521 --Bug# 4558486: Changed call to okl_k_party_roles_pvt api
1522 -- to delete records in tables
1523 -- okc_k_party_roles_b and okl_k_party_roles
1524 /*
1525 OKL_OKC_MIGRATION_PVT.delete_k_party_role(
1526 p_api_version => p_api_version,
1527 p_init_msg_list => p_init_msg_list,
1528 x_return_status => x_return_status,
1529 x_msg_count => x_msg_count,
1530 x_msg_data => x_msg_data,
1531 p_cplv_rec => lp_cplv_rec);
1532 */
1533
1534 lp_kplv_rec.id := lp_cplv_rec.id;
1535 OKL_K_PARTY_ROLES_PVT.delete_k_party_role(
1536 p_api_version => p_api_version,
1537 p_init_msg_list => p_init_msg_list,
1538 x_return_status => x_return_status,
1539 x_msg_count => x_msg_count,
1540 x_msg_data => x_msg_data,
1541 p_cplv_rec => lp_cplv_rec,
1542 p_kplv_rec => lp_kplv_rec);
1543
1544 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1545 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1546 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
1547 raise OKC_API.G_EXCEPTION_ERROR;
1548 End If;
1549
1550 --Bug# 3340949:
1551 --End If;
1552
1553 /*
1554 -- mvasudev, 09/09/2004
1555 -- Code change to enable Business Event
1556 */
1557 raise_business_event(x_return_status => x_return_status);
1558
1559 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1560 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1561 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1562 RAISE OKL_API.G_EXCEPTION_ERROR;
1563 END IF;
1564
1565 /*
1566 -- mvasudev, 09/09/2004
1567 -- END, Code change to enable Business Event
1568 */
1569
1570 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
1571
1572 EXCEPTION
1573 when OKC_API.G_EXCEPTION_ERROR then
1574 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1575 p_api_name => l_api_name,
1576 p_pkg_name => g_pkg_name,
1577 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
1578 x_msg_count => x_msg_count,
1579 x_msg_data => x_msg_data,
1580 p_api_type => g_api_type);
1581
1582 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
1583 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1584 p_api_name => l_api_name,
1585 p_pkg_name => g_pkg_name,
1586 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
1587 x_msg_count => x_msg_count,
1588 x_msg_data => x_msg_data,
1589 p_api_type => g_api_type);
1590
1591 when OTHERS then
1592 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1593 p_api_name => l_api_name,
1594 p_pkg_name => g_pkg_name,
1595 p_exc_name => 'OTHERS',
1596 x_msg_count => x_msg_count,
1597 x_msg_data => x_msg_data,
1598 p_api_type => g_api_type);
1599 END Delete_Party;
1600
1601
1602 end OKL_JTOT_CONTACT_EXTRACT_PUB;
1603