[Home] [Help]
PACKAGE BODY: APPS.OKL_JTOT_CONTACT_EXTRACT_PVT
Source
1 Package Body okl_jtot_contact_extract_pvt as
2 /* $Header: OKLRJCXB.pls 120.7 2007/08/09 09:41:55 zrehman noship $ */
3 --------------------------
4 --HERE jtot extract BEGIN
5 --Start of Comments
6 --Procedure : Get Party
7 --Description : Returns the SQL string for LOV of a party Role
8 --End of Comments
9
10 --Added by kthiruva 23-Sep-2003 Bug No.3156265
11
12 --For Object Code 'OKX_SALEPERS'
13
14 CURSOR okx_salepers_csr(p_name VARCHAR2 ,p_id1 VARCHAR2 , p_id2 VARCHAR2) IS
15 SELECT srv.ID1,
16 srv.ID2,
17 srv.NAME,
18 srv.DESCRIPTION
19 FROM OKX_SALESREPS_V srv
20 WHERE ((nvl(srv.ORG_ID, -99) = nvl(mo_global.get_current_org_id, -99)) or (nvl(mo_global.get_current_org_id, -99) = -99))
21 AND srv.name = NVL(p_name,srv.name)
22 AND srv.ID1 = NVL(p_id1,srv.ID1)
23 AND srv.ID2 = NVL(p_id2,srv.ID2)
24 ORDER BY srv.NAME;
25
26 -- For Object Code 'OKX_PCONTACT'
27
28 CURSOR okx_pcontact_csr(p_name VARCHAR2 ,p_id1 VARCHAR2 , p_id2 VARCHAR2) IS
29 SELECT pcv.ID1,
30 pcv.ID2,
31 pcv.NAME,
32 pcv.DESCRIPTION
33 FROM okx_party_contacts_v pcv
34 WHERE pcv.name = NVL(p_name,pcv.name)
35 AND pcv.ID1 = NVL(p_id1,pcv.ID1)
36 AND pcv.ID2 = NVL(p_id2,pcv.ID2)
37 ORDER BY pcv.NAME;
38
39
40 --For Object Code 'OKX_PARTY'
41 CURSOR okx_party_csr(p_name VARCHAR2 , p_id1 VARCHAR2 , p_id2 VARCHAR2) IS
42 SELECT prv.id1,
43 prv.id2,
44 prv.name,
45 prv.description
46 FROM okx_parties_v prv
47 WHERE prv.name = NVL(p_name,prv.name)
48 AND prv.id1 = NVL(p_id1,prv.id1)
49 AND prv.id2 = NVL(p_id2,prv.id2)
50 ORDER BY prv.name;
51
52 --For Object Code 'OKX_OPERUNIT'
53 CURSOR okx_operunit_csr(p_name VARCHAR2 , p_id1 VARCHAR2 , p_id2 VARCHAR2) IS
54 SELECT ord.id1,
55 ord.id2,
56 ord.name,
57 ord.description
58 FROM okx_organization_defs_v ord
59 WHERE ord.organization_type = 'OPERATING_UNIT'
60 AND ord.information_type = 'Operating Unit Information'
61 AND ord.name = NVL(p_name,ord.name)
62 AND ord.id1 = NVL(p_id1,ord.id1)
63 AND ord.id2 = NVL(p_id2,ord.id2)
64 ORDER BY ord.NAME;
65
66
67 --For Object Code 'OKX_VENDOR'
68 CURSOR okx_vendor_csr(p_name VARCHAR2 , p_id1 VARCHAR2 , p_id2 VARCHAR2) IS
69 SELECT vev.id1,
70 vev.id2,
71 vev.name,
72 vev.description
73 FROM okx_vendors_v vev
74 WHERE vev.name = NVL(p_name,vev.name)
75 AND vev.id1 = NVL(p_id1,vev.id1)
76 AND vev.id2 = NVL(p_id2,vev.id2)
77 ORDER BY vev.NAME;
78
79
80 --Start of Comments
81 --Procedure : Get Party
82 --Description : Returns Name, Description for a given role or all the roles
83 -- attached to a contract
84 --End of Comments
85 Procedure Get_Party (p_api_version IN NUMBER,
86 p_init_msg_list IN VARCHAR2 default OKC_API.G_FALSE,
87 x_return_status OUT NOCOPY VARCHAR2,
88 x_msg_count OUT NOCOPY NUMBER,
89 x_msg_data OUT NOCOPY VARCHAR2,
90 p_chr_id IN VARCHAR2,
91 p_cle_id IN VARCHAR2,
92 p_role_code IN OKC_K_PARTY_ROLES_V.rle_code%Type,
93 p_intent IN VARCHAR2 default 'S',
94 x_party_tab out nocopy party_tab_type) is
95
96 CURSOR party_role_curs(p_chr_id IN NUMBER,
97 p_cle_id IN NUMBER,
98 p_dnz_chr_id IN NUMBER,
99 p_role_code IN VARCHAR2) is
100 select object1_id1,
101 object1_id2,
102 jtot_object1_code,
103 rle_code
104 from OKC_K_PARTY_ROLES_V
105 where rle_code = nvl(p_role_code,rle_code)
106 and nvl(cle_id,-99999) = p_cle_id
107 and nvl(chr_id,-99999) = p_chr_id
108 and dnz_chr_id = decode(p_chr_id,null,dnz_chr_id,p_dnz_chr_id)
109 order by rle_code;
110 party_role_rec party_role_curs%RowType;
111 l_select_clause varchar2(2000) default null;
112 l_from_clause varchar2(2000) default null;
113 l_where_clause varchar2(2000) default null;
114 l_order_by_clause varchar2(2000) default null;
115 l_query_string varchar2(2000) default null;
116 l_id1 OKC_K_PARTY_ROLES_V.OBJECT1_ID1%TYPE default Null;
117 l_id2 OKC_K_PARTY_ROLES_V.OBJECT1_ID2%TYPE default Null;
118 l_name VARCHAR2(250) Default Null;
119 l_Description VARCHAR2(250) Default Null;
120 l_object_code VARCHAR2(30) Default Null;
121 type party_curs_type is REF CURSOR;
122 party_curs party_curs_type;
123 i Number default 0;
124 l_chr_id Number;
125 l_dnz_chr_id Number;
126 l_cle_id Number;
127
128 Begin
129 If p_chr_id is not null and p_cle_id is null
130 Then
131 l_chr_id := p_chr_id;
132 l_cle_id := -99999;
133 l_dnz_chr_id := p_chr_id;
134 ElsIf p_chr_id is null and p_cle_id is not null
135 Then
136 l_chr_id := -99999;
137 l_cle_id := p_cle_id;
138 l_dnz_chr_id := -99999;
139 ElsIf p_chr_id is not null and p_cle_id is not null
140 Then
141 l_chr_id := -99999;
142 l_cle_id := p_cle_id;
143 l_dnz_chr_id := p_chr_id;
144 Elsif p_chr_id is null and p_cle_id is null
145 Then
146 null; --raise appropriate exception here
147 End If;
148 Open party_role_curs(l_chr_id,l_cle_id,l_dnz_chr_id,p_role_code);
149 Loop
150 Fetch party_role_curs into party_role_rec;
151 Exit When party_role_curs%NotFound;
152 i := party_role_curs%rowcount;
153
154 --Added by kthiruva 23-Sep-2003 Bug No.3156265
155
156 -- For Object Code is 'OKX_PARTY'
157
158 IF ( (party_role_rec.rle_code IN ('BROKER', 'DEALER','GUARANTOR','LESSEE' , 'MANUFACTURER' , 'PRIVATE_LABEL') AND p_intent ='S')
159 OR (party_role_rec.rle_code = 'INVESTOR' AND p_intent='B'))
160 THEN
161
162 OPEN okx_party_csr(p_name => null,
163 p_id1 => party_role_rec.object1_id1,
164 p_id2 => party_role_rec.object1_id2);
165
166
167 l_id1 := Null;
168 l_id2 := Null;
169 l_name := Null;
170 l_description := Null;
171 Fetch okx_party_csr into l_id1,
172 l_id2,
173 l_name,
174 l_description;
175 If okx_party_csr%NotFound Then
176 Null;--raise appropriate exception here
177 End If;
178 x_party_tab(i).rle_code := party_role_rec.rle_code;
179 x_party_tab(i).id1 := l_id1;
180 x_party_tab(i).id2 := l_id2;
181 x_party_tab(i).name := l_name;
182 x_party_tab(i).description := l_description;
183 x_party_tab(i).object_code := l_object_code;
184 Close okx_party_csr;
185 END IF;
186
187 -- For Object Code 'OKX_OPERUNIT'
188 IF ( (party_role_rec.rle_code ='LESSOR' AND p_intent ='S') OR (party_role_rec.rle_code ='SYNDICATOR' AND p_intent ='B'))
189 THEN
190 OPEN okx_operunit_csr(p_name => null,
191 p_id1 => party_role_rec.object1_id1,
192 p_id2 => party_role_rec.object1_id2);
193
194
195 l_id1 := Null;
196 l_id2 := Null;
197 l_name := Null;
198 l_description := Null;
199 Fetch okx_operunit_csr into l_id1,
200 l_id2,
201 l_name,
202 l_description;
203 If okx_operunit_csr%NotFound Then
204 Null;--raise appropriate exception here
205 End If;
206 x_party_tab(i).rle_code := party_role_rec.rle_code;
207 x_party_tab(i).id1 := l_id1;
208 x_party_tab(i).id2 := l_id2;
209 x_party_tab(i).name := l_name;
210 x_party_tab(i).description := l_description;
211 x_party_tab(i).object_code := l_object_code;
212 Close okx_operunit_csr;
213 END IF;
214
215 -- For Object Code 'OKX_VENDOR'
216
217 IF ( party_role_rec.rle_code ='OKL_VENDOR' AND p_intent='S')
218 THEN
219 OPEN okx_vendor_csr(p_name => null,
220 p_id1 => party_role_rec.object1_id1,
221 p_id2 => party_role_rec.object1_id2);
222
223
224 l_id1 := Null;
225 l_id2 := Null;
226 l_name := Null;
227 l_description := Null;
228 Fetch okx_vendor_csr into l_id1,
229 l_id2,
230 l_name,
231 l_description;
232 If okx_vendor_csr%NotFound Then
233 Null;--raise appropriate exception here
234 End If;
235 x_party_tab(i).rle_code := party_role_rec.rle_code;
236 x_party_tab(i).id1 := l_id1;
237 x_party_tab(i).id2 := l_id2;
238 x_party_tab(i).name := l_name;
239 x_party_tab(i).description := l_description;
240 x_party_tab(i).object_code := l_object_code;
241 Close okx_vendor_csr;
242 END IF;
243
244 End Loop;
245 Close party_role_curs;
246 End Get_Party;
247 --Start of Comments
248 --Procedure : Get_Party
249 --Description : Fetches Name, Description of a Party role for a given
250 -- object1_id1 and object2_id2
251 --End of comments
252 Procedure Get_Party (p_api_version IN NUMBER,
253 p_init_msg_list IN VARCHAR2 default OKC_API.G_FALSE,
254 x_return_status OUT NOCOPY VARCHAR2,
255 x_msg_count OUT NOCOPY NUMBER,
256 x_msg_data OUT NOCOPY VARCHAR2,
257 p_role_code IN VARCHAR2,
258 p_intent IN VARCHAR2,
259 p_id1 IN VARCHAR2,
260 p_id2 IN VARCHAR2,
261 x_id1 OUT NOCOPY VARCHAR2,
262 x_id2 OUT NOCOPY VARCHAR2,
263 x_name OUT NOCOPY VARCHAR2,
264 x_description OUT NOCOPY VARCHAR2) is
265
266 l_select_clause varchar2(2000) default null;
267 l_from_clause varchar2(2000) default null;
268 l_where_clause varchar2(2000) default null;
269 l_order_by_clause varchar2(2000) default null;
270 l_query_string varchar2(2000) default null;
271 l_id1 OKC_K_PARTY_ROLES_V.OBJECT1_ID1%TYPE default Null;
272 l_id2 OKC_K_PARTY_ROLES_V.OBJECT1_ID2%TYPE default Null;
273 l_name VARCHAR2(250) Default Null;
274 l_Description VARCHAR2(250) Default Null;
275 l_object_code VARCHAR2(30) Default Null;
276 type party_curs_type is REF CURSOR;
277 party_curs party_curs_type;
278
279 Begin
280
281
282 --Added by kthiruva 23-Sep-2003 Bug No.3156265
283
284 IF ( (p_role_code IN ('BROKER', 'DEALER','GUARANTOR','LESSEE' , 'MANUFACTURER' , 'PRIVATE_LABEL') AND p_intent ='S')
285 OR (p_role_code = 'INVESTOR' AND p_intent='B'))
286 THEN
287 OPEN okx_party_csr(p_name => null,
288 p_id1 => p_id1,
289 p_id2 => p_id2);
290
291 l_id1 := Null;
292 l_id2 := Null;
293 l_name := Null;
294 l_description := Null;
295 Fetch okx_party_csr into l_id1,
296 l_id2,
297 l_name,
298 l_description;
299 If okx_party_csr%NotFound Then
300 Null;--raise appropriate exception here
301 End If;
302 x_id1 := l_id1;
303 x_id2 := l_id2;
304 x_name := l_name;
305 x_description := l_description;
306 Close okx_party_csr;
307 END IF;
308
309 IF ( (p_role_code ='LESSOR' AND p_intent ='S') OR (p_role_code ='SYNDICATOR' AND p_intent ='B')) THEN
310
311 OPEN okx_operunit_csr(p_name => null,
312 p_id1 => p_id1,
313 p_id2 => p_id2);
314
315 l_id1 := Null;
316 l_id2 := Null;
317 l_name := Null;
318 l_description := Null;
319 Fetch okx_operunit_csr into l_id1,
320 l_id2,
321 l_name,
322 l_description;
323 If okx_operunit_csr%NotFound Then
324 Null;--raise appropriate exception here
325 End If;
326 x_id1 := l_id1;
327 x_id2 := l_id2;
328 x_name := l_name;
329 x_description := l_description;
330 Close okx_operunit_csr;
331 END IF;
332
333
334 IF ( p_role_code ='OKL_VENDOR' AND p_intent='S') THEN
335
336 OPEN okx_vendor_csr(p_name => null,
337 p_id1 => p_id1,
338 p_id2 => p_id2);
339
340 l_id1 := Null;
341 l_id2 := Null;
342 l_name := Null;
343 l_description := Null;
344 Fetch okx_vendor_csr into l_id1,
345 l_id2,
346 l_name,
347 l_description;
348 If okx_vendor_csr%NotFound Then
349 Null;--raise appropriate exception here
350 End If;
351 x_id1 := l_id1;
352 x_id2 := l_id2;
353 x_name := l_name;
354 x_description := l_description;
355 Close okx_vendor_csr;
356 END IF;
357
358 End Get_Party;
359 --Start of Comments
360 --Procedure : Get_Subclass_Roles
361 --Description : fetches Party Roles for a Subclass
362 --End of Comments
363 Procedure Get_SubClass_Def_Roles
364 (p_scs_code IN OKC_SUBCLASSES_V.CODE%TYPE,
365 x_rle_code_tbl OUT NOCOPY rle_code_tbl_type) is
366 CURSOR scs_rle_curs is
367 select scs_code,
368 rle_code
369 from okc_subclass_roles
370 where scs_code = p_scs_code
371 and nvl(start_date,sysdate) <= sysdate
372 and nvl(end_date,sysdate+1) > sysdate;
373 scs_rle_rec scs_rle_curs%rowType;
374 i Number;
375 Begin
376 Open scs_rle_curs;
377 Loop
378 Fetch scs_rle_curs into scs_rle_rec;
379 Exit When scs_rle_curs%NotFound;
380 i := scs_rle_curs%RowCount;
381 x_rle_code_tbl(i).scs_code := scs_rle_rec.scs_code;
382 x_rle_code_tbl(i).rle_code := scs_rle_rec.rle_code;
383 End Loop;
384 Close scs_rle_curs;
385 End Get_SubClass_Def_Roles;
386 --Start of Comments
387 --Procedure : Get_Subclass_Roles
388 --Description : fetches Party Roles for a Subclass
389 --End of Comments
390 Procedure Get_Contract_Def_Roles
391 (p_chr_id IN VARCHAR2,
392 x_rle_code_tbl OUT NOCOPY rle_code_tbl_type) is
393 Cursor chr_scs_curs is
394 select scs_code
395 from OKC_K_HEADERS_V
396 where id = p_chr_id;
397 l_scs_code OKC_K_HEADERS_V.SCS_CODE%TYPE;
398 Begin
399 Open chr_scs_curs;
400 Fetch chr_scs_curs into l_scs_code;
401 If chr_scs_curs%NotFound Then
402 null; --handle appropriate exception
403 Else
404 Get_Subclass_Def_Roles(p_scs_code => l_scs_code,
405 x_rle_code_tbl => x_rle_code_tbl);
406 End If;
407 End Get_Contract_Def_Roles;
408 ------------------------------
409 --HERE jtot extract END
410 -------------------------
411 --Start of Comments
412 --Procedure : Get contact
413 --Description : Returns the SQL string for LOV of a contact
414 --End of Comments
415 Procedure Get_Contact (p_rle_code IN VARCHAR2,
416 p_cro_code IN VARCHAR2,
417 p_intent IN VARCHAR2 DEFAULT 'S',
418 p_id1 IN VARCHAR2,
419 p_id2 IN VARCHAR2,
420 p_name IN VARCHAR2,
421 x_select_clause OUT NOCOPY VARCHAR2,
422 x_from_clause OUT NOCOPY VARCHAR2,
423 x_where_clause OUT NOCOPY VARCHAR2,
424 x_order_by_clause OUT NOCOPY VARCHAR2,
425 x_object_code OUT NOCOPY VARCHAR2) is
426 CURSOR jtf_contact_cur (p_cro_code VARCHAR2, p_intent VARCHAR2) is
427 select job.object_code OBJECT_CODE,
428 job.object_code||'.ID1, '||
429 job.object_code||'.ID2, '||
430 job.object_code||'.NAME, '||
431 job.object_code||'.DESCRIPTION ' SELECT_CLAUSE,
432 from_table FROM_CLAUSE,
433 where_clause WHERE_CLAUSE,
434 order_by_clause ORDER_BY_CLAUSE
435 from jtf_objects_b job,
436 okc_contact_sources rs
437 where job.object_code = rs.jtot_object_code
438 and nvl(job.start_date_active,sysdate) <= sysdate
439 and nvl(job.end_date_active,sysdate + 1) > sysdate
440 and rs.rle_code = p_rle_code
441 and rs.cro_code = p_cro_code
442 and rs.start_date <= sysdate
443 and nvl(rs.end_date,sysdate+1) > sysdate
444 and rs.buy_or_sell = p_intent;
445 jtf_contact_rec jtf_contact_cur%rowtype;
446 l_query_string VARCHAR2(2000) default Null;
447 l_where_clause VARCHAR2(2000) default Null;
448 Begin
449 If okc_context.get_okc_org_id is null then
450 okc_context.set_okc_org_context(204,204);
451 End If;
452 Open jtf_contact_cur(p_cro_code, p_intent);
453 Fetch jtf_contact_cur into jtf_contact_rec;
454 If jtf_contact_cur%NOTFOUND Then
455 --handle exception appropriately
456 x_object_code := 'NOT FOUND';
457 x_select_clause := 'NOT FOUND';
458 x_from_clause := 'NOT FOUND';
459 x_where_clause := 'NOT FOUND';
460 x_order_by_clause := 'NOT FOUND';
461 Else
462 x_object_code := jtf_contact_rec.object_code;
463 x_select_clause := jtf_contact_rec.select_clause;
464 x_from_clause := jtf_contact_rec.from_clause;
465 x_where_clause := jtf_contact_rec.where_clause;
466 x_order_by_clause := jtf_contact_rec.order_by_clause;
467 If p_id1 is not null and p_id2 is not null and p_name is null then
468 select x_where_clause || decode(x_where_clause,null,null,' AND ')||
469 ' ID1 = '||''''||p_id1||''''||' AND '||' ID2 = '||''''||p_id2||''''
470 into l_where_clause
471 from dual;
472 x_where_clause := l_where_clause;
473 Elsif p_name is not null then
474 select x_where_clause || decode(x_where_clause,null,null,' AND ')||
475 ' NAME like '||''''||p_name||'%'||''''
476 into l_where_clause
477 from dual;
478 End If;
479 End If;
480 Close jtf_contact_cur;
481 End get_contact;
482 --Start of Comments
483 --Procedure : Get contact
484 --Description : Returns Name, Description for a given contact or all the contacts
485 -- attached to a contract party role.
486 --End of Comments
487 Procedure Get_Contact(p_api_version IN NUMBER,
488 p_init_msg_list IN VARCHAR2 default OKC_API.G_FALSE,
489 x_return_status OUT NOCOPY VARCHAR2,
490 x_msg_count OUT NOCOPY NUMBER,
491 x_msg_data OUT NOCOPY VARCHAR2,
492 p_rle_code IN VARCHAR2,
493 p_cro_code IN VARCHAR2,
494 p_intent IN VARCHAR2,
495 p_id1 IN VARCHAR2,
496 p_id2 IN VARCHAR2,
497 x_id1 OUT NOCOPY VARCHAR2,
498 x_id2 OUT NOCOPY VARCHAR2,
499 x_name OUT NOCOPY VARCHAR2,
500 x_description OUT NOCOPY VARCHAR2) is
501 l_select_clause varchar2(2000) default null;
502 l_from_clause varchar2(2000) default null;
503 l_where_clause varchar2(2000) default null;
504 l_order_by_clause varchar2(2000) default null;
505 l_query_string varchar2(2000) default null;
506 l_id1 OKC_CONTACTS_V.OBJECT1_ID1%TYPE default Null;
507 l_id2 OKC_CONTACTS_V.OBJECT1_ID2%TYPE default Null;
508 l_name VARCHAR2(250) Default Null;
509 l_Description VARCHAR2(250) Default Null;
510 l_object_code VARCHAR2(30) Default Null;
511 type CONTACT_curs_type is REF CURSOR;
512 contact_curs contact_curs_type;
513
514
515 Begin
516
517
518 --Added by kthiruva 23-Sep-2003 Bug No.3156265
519
520 IF (p_rle_code = 'LESSOR' and p_cro_code = 'SALESPERSON' and p_intent = 'S')
521 THEN
522
523 OPEN okx_salepers_csr(p_name => null,
524 p_id1 => p_id1,
525 p_id2 => p_id2);
526
527 l_id1 := Null;
528 l_id2 := Null;
529 l_name := Null;
530 l_description := Null;
531 Fetch okx_salepers_csr into l_id1,
532 l_id2,
533 l_name,
534 l_description;
535 If okx_salepers_csr%NotFound Then
536 Null;--raise appropriate exception here
537 End If;
538 x_id1 := l_id1;
539 x_id2 := l_id2;
540 x_name := l_name;
541 x_description := l_description;
542 Close okx_salepers_csr;
543
544 ELSE
545
546 OPEN okx_pcontact_csr(p_name => null,
547 p_id1 => p_id1,
548 p_id2 => p_id2);
549
550 l_id1 := Null;
551 l_id2 := Null;
552 l_name := Null;
553 l_description := Null;
554 Fetch okx_pcontact_csr into l_id1,
555 l_id2,
556 l_name,
557 l_description;
558 If okx_pcontact_csr%NotFound Then
559 Null;--raise appropriate exception here
560 End If;
561 x_id1 := l_id1;
562 x_id2 := l_id2;
563 x_name := l_name;
564 x_description := l_description;
565 Close okx_pcontact_csr;
566
567 END IF;
568
569 End Get_contact;
570 END OKL_JTOT_CONTACT_EXTRACT_PVT;