[Home] [Help]
PACKAGE BODY: APPS.OKC_REP_CONTRACT_TEXT_IDX_PVT
Source
1 PACKAGE BODY okc_rep_contract_text_idx_pvt AS
2 /* $Header: OKCVREPSRMDB.pls 120.4 2011/05/18 17:44:38 harchand ship $ */
3
4 ---------------------------------------------------------------------------
5 -- GLOBAL CONSTANTS
6 ---------------------------------------------------------------------------
7 G_PARTY_TYPE_INTERNAL CONSTANT VARCHAR2(12) := 'INTERNAL_ORG';
8 G_PARTY_TYPE_CUSTOMER CONSTANT VARCHAR2(12) := 'CUSTOMER_ORG';
9 G_PARTY_TYPE_SUPPLIER CONSTANT VARCHAR2(12) := 'SUPPLIER_ORG';
10 G_PARTY_TYPE_PARTNER CONSTANT VARCHAR2(11) := 'PARTNER_ORG';
11
12 G_APP_NAME CONSTANT VARCHAR2(3) := 'OKC';
13 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKC_REP_CONTRACT_TEXT_IDX_PVT';
14 G_MODULE CONSTANT VARCHAR2(250) := 'okc.plsql.'||G_PKG_NAME||'.';
15
16 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_UNEXPECTED_ERROR';
17 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_MESSAGE';
18 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_CODE';
19
20 -- Start of comments
21 --API name : okc_rep_ver_md
22 --Type : Private.
23 --Function : Procedure to collect metadata for Repository contract
24 --Pre-reqs : None.
25 --Parameters :
26 --IN : r_id IN ROWID Required
27 -- : md_lob IN OUT NOCOPY CLOB Required
28 --Note :
29 -- End of comments
30 PROCEDURE okc_rep_con_md(
31 r_id IN ROWID,
32 md_lob IN OUT NOCOPY CLOB)
33 IS
34 l_api_name VARCHAR2(32);
35
36 TYPE CurTyp IS REF CURSOR; -- define weak REF CURSOR type
37 con_cur CurTyp; -- declare cursor variable
38
39 sql_stmt VARCHAR2(10000);
40
41 TYPE OrgNameList IS TABLE OF VARCHAR(200);
42 TYPE ConNumList IS TABLE OF VARCHAR(200);
43 TYPE ConNameList IS TABLE OF VARCHAR(450);
44 TYPE ConDescList IS TABLE OF VARCHAR(2000);
45 TYPE KeywordList IS TABLE OF VARCHAR(2000);
46 TYPE CommentList IS TABLE OF VARCHAR(2000);
47 TYPE PartyNameList IS TABLE OF VARCHAR(200);
48 TYPE ContactnameList IS TABLE OF VARCHAR(200);
49
50 org_name OrgNameList;
51 contract_number ConNumList;
52 contract_name ConNameList;
53 description ConDescList;
54 keywords KeywordList;
55 version_comments CommentList;
56 party_name PartyNameList;
57 contact_name ContactnameList;
58
59 BEGIN
60 --initialize local variables
61 l_api_name := 'okc_rep_con_md';
62
63 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
64 FND_LOG.STRING(
65 FND_LOG.LEVEL_PROCEDURE,
66 G_MODULE||l_api_name,
67 'Entering APPS.OKC_REP_CONTRACT_TEXT_IDX_PVT.okc_rep_ver_md');
68 END IF;
69
70 -----------------------------------------
71 -- Add contract metadata
72 -----------------------------------------
73
74 sql_stmt :=
75 'SELECT '||
76 ' NVL(o.name,'' '') as org_name '||
77 ' ,NVL(c.contract_number,'' '') as contract_number '||
78 ' ,NVL(c.contract_name,'' '') as contract_name '||
79 ' ,NVL(c.contract_desc,'' '') as description '||
80 ' ,NVL(c.keywords,'' '') as keywords '||
81 ' ,NVL(c.version_comments,'' '') as version_comments '||
82 'FROM '||
83 'okc_rep_contracts_all c, '||
84 'hr_all_organization_units_vl o '||
85 'WHERE c.org_id = o.organization_id '||
86 'AND c.rowid = :1 ';
87
88 OPEN con_cur FOR sql_stmt USING r_id;
89 FETCH con_cur BULK COLLECT INTO org_name, contract_number, contract_name, description, keywords, version_comments;
90
91 IF org_name.COUNT <> 0 THEN
92 FOR i IN org_name.FIRST..org_name.LAST LOOP
93 DBMS_LOB.WRITEAPPEND( md_lob, length(org_name(i))+1, org_name(i) || ' ' );
94 DBMS_LOB.WRITEAPPEND( md_lob, length(contract_number(i))+1, contract_number(i) || ' ' );
95 DBMS_LOB.WRITEAPPEND( md_lob, length(contract_name(i))+1, contract_name(i) || ' ' );
96 DBMS_LOB.WRITEAPPEND( md_lob, length(description(i))+1, description(i) || ' ' );
97 DBMS_LOB.WRITEAPPEND( md_lob, length(keywords(i))+1, keywords(i) || ' ' );
98 DBMS_LOB.WRITEAPPEND( md_lob, length(version_comments(i))+1, version_comments(i) || ' ' );
99 END LOOP;
100 END IF;
101
102 CLOSE con_cur;
103
104 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
105 FND_LOG.STRING(
106 FND_LOG.LEVEL_PROCEDURE,
107 G_MODULE||l_api_name,
108 'Added contract metadata');
109 END IF;
110
111 -----------------------------------------
112 -- Add party metadata
113 -----------------------------------------
114
115 sql_stmt :=
116 'SELECT '||
117 ' NVL(o.name,'' '') as party_name '||
118 'FROM '||
119 'okc_rep_contracts_all c, '||
120 'okc_rep_contract_parties p, '||
121 'hr_all_organization_units_vl o '||
122 'WHERE c.contract_id = p.contract_id '||
123 'AND p.party_id = o.organization_id '||
124 'AND p.party_role_code = '''|| G_PARTY_TYPE_INTERNAL || ''' ' ||
125 'AND c.rowid = :1 ';
126
127 OPEN con_cur FOR sql_stmt USING r_id;
128 FETCH con_cur BULK COLLECT INTO party_name;
129
130 IF party_name.COUNT <> 0 THEN
131 FOR i IN party_name.FIRST..party_name.LAST LOOP
132 DBMS_LOB.WRITEAPPEND( md_lob, length(party_name(i))+1, party_name(i) || ' ' );
133 END LOOP;
134 END IF;
135
136 CLOSE con_cur;
137
138 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
139 FND_LOG.STRING(
140 FND_LOG.LEVEL_PROCEDURE,
141 G_MODULE||l_api_name,
142 'Added internal party metadata');
143 END IF;
144
145
146 sql_stmt :=
147 'SELECT '||
148 ' NVL(v.vendor_name,'' '') as party_name '||
149 'FROM '||
150 'okc_rep_contracts_all c, '||
151 'okc_rep_contract_parties p, '||
152 'po_vendors v '||
153 'WHERE c.contract_id = p.contract_id '||
154 'AND p.party_id = v.vendor_id '||
155 'AND p.party_role_code = '''|| G_PARTY_TYPE_SUPPLIER || ''' ' ||
156 'AND c.rowid = :1 ';
157
158 OPEN con_cur FOR sql_stmt USING r_id;
159 FETCH con_cur BULK COLLECT INTO party_name;
160
161 IF party_name.COUNT <> 0 THEN
162 FOR i IN party_name.FIRST..party_name.LAST LOOP
163 DBMS_LOB.WRITEAPPEND( md_lob, length(party_name(i))+1, party_name(i) || ' ' );
164 END LOOP;
165 END IF;
166
167 CLOSE con_cur;
168
169 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
170 FND_LOG.STRING(
171 FND_LOG.LEVEL_PROCEDURE,
172 G_MODULE||l_api_name,
173 'Added supplier party metadata');
174 END IF;
175
176 sql_stmt :=
177 'SELECT '||
178 ' NVL(hz.party_name,'' '') as party_name '||
179 'FROM '||
180 'okc_rep_contracts_all c, '||
181 'okc_rep_contract_parties p, '||
182 'hz_parties hz '||
183 'WHERE c.contract_id = p.contract_id '||
184 'AND p.party_id = hz.party_id '||
185 'AND p.party_role_code IN (''' || G_PARTY_TYPE_CUSTOMER ||''', '''|| G_PARTY_TYPE_PARTNER || ''') '||
186 'AND c.rowid = :1 ';
187
188 OPEN con_cur FOR sql_stmt USING r_id;
189 FETCH con_cur BULK COLLECT INTO party_name;
190
191 IF party_name.COUNT <> 0 THEN
192 FOR i IN party_name.FIRST..party_name.LAST LOOP
193 DBMS_LOB.WRITEAPPEND( md_lob, length(party_name(i))+1, party_name(i) || ' ' );
194 END LOOP;
195 END IF;
196
197
198 CLOSE con_cur;
199
200 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
201 FND_LOG.STRING(
202 FND_LOG.LEVEL_PROCEDURE,
203 G_MODULE||l_api_name,
204 'Added partner party metadata');
205 END IF;
206
207 -----------------------------------------
208 -- Add contact metadata
209 -----------------------------------------
210
211 sql_stmt :=
212 'SELECT '||
213 ' NVL(per.full_name,'' '') as contact_name '||
214 'FROM '||
215 'okc_rep_contracts_all c, '||
216 'okc_rep_party_contacts ct, '||
217 'per_all_people_f per '||
218 'WHERE c.contract_id = ct.contract_id '||
219 'AND ct.party_role_code = ''' || G_PARTY_TYPE_INTERNAL || ''' ' ||
220 'AND ct.contact_id = per.person_id '||
221 'AND c.rowid = :1 ';
222
223 OPEN con_cur FOR sql_stmt USING r_id;
224 FETCH con_cur BULK COLLECT INTO contact_name;
225
226 IF contact_name.COUNT <> 0 THEN
227 FOR i IN contact_name.FIRST..contact_name.LAST LOOP
228 DBMS_LOB.WRITEAPPEND( md_lob, length(contact_name(i))+1, contact_name(i) || ' ');
229 END LOOP;
230 END IF;
231
232 CLOSE con_cur;
233
234 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
235 FND_LOG.STRING(
236 FND_LOG.LEVEL_PROCEDURE,
237 G_MODULE||l_api_name,
238 'Added internal party contact metadata');
239 END IF;
240
241 sql_stmt :=
242 'SELECT '||
243 ' NVL2(v.last_name, v.last_name||'' ''||v.first_name, '' '') as contact_name '||
244 'FROM '||
245 'okc_rep_contracts_all c, '||
246 'okc_rep_party_contacts ct, '||
247 'po_vendor_contacts v '||
248 'WHERE c.contract_id = ct.contract_id '||
249 'AND ct.contact_id = v.vendor_contact_id '||
250 'AND ct.PARTY_ROLE_CODE = '''|| G_PARTY_TYPE_SUPPLIER || ''' ' ||
251 'AND c.rowid = :1 ';
252
253 OPEN con_cur FOR sql_stmt USING r_id;
254 FETCH con_cur BULK COLLECT INTO contact_name;
255
256 IF contact_name.COUNT <> 0 THEN
257 FOR i IN contact_name.FIRST..contact_name.LAST LOOP
258 DBMS_LOB.WRITEAPPEND( md_lob, length(contact_name(i))+1, contact_name(i) || ' ' );
259 END LOOP;
260 END IF;
261
262 CLOSE con_cur;
263
264 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
265 FND_LOG.STRING(
266 FND_LOG.LEVEL_PROCEDURE,
267 G_MODULE||l_api_name,
268 'Added supplier party contact metadata');
269 END IF;
270
271 sql_stmt :=
272 'SELECT '||
273 ' hz.party_name AS contact_name '||
274 'FROM '||
275 'okc_rep_contracts_all c, '||
276 'okc_rep_party_contacts ct, '||
277 'hz_parties hz '||
278 'WHERE c.contract_id = ct.contract_id '||
279 'AND ct.party_role_code IN ( '''||G_PARTY_TYPE_CUSTOMER ||''', '''|| G_PARTY_TYPE_PARTNER || ''') '||
280 'AND ct.contact_id = hz.party_id '||
281 'AND c.rowid = :1 ';
282
283 OPEN con_cur FOR sql_stmt USING r_id;
284 FETCH con_cur BULK COLLECT INTO contact_name;
285
286 IF contact_name.COUNT <> 0 THEN
287 FOR i IN contact_name.FIRST..contact_name.LAST LOOP
288 DBMS_LOB.WRITEAPPEND( md_lob, length(contact_name(i))+1, contact_name(i) || ' ' );
289 END LOOP;
290 END IF;
291
292 CLOSE con_cur;
293
294 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
295 FND_LOG.STRING(
296 FND_LOG.LEVEL_PROCEDURE,
297 G_MODULE||l_api_name,
298 'Added customer and partner party contact metadata');
299 END IF;
300
301 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
302 FND_LOG.STRING(
303 FND_LOG.LEVEL_PROCEDURE,
304 G_MODULE||l_api_name,
305 'Leaving APPS.OKC_REP_CONTRACT_TEXT_IDX_PVT.okc_rep_ver_md');
306 END IF;
307 END;
308
309 -- Start of comments
310 --API name : okc_rep_ver_md
311 --Type : Private.
312 --Function : Procedure to collect metadata for Repository contract versions
313 --Pre-reqs : None.
314 --Parameters :
315 --IN : r_id IN ROWID Required
316 -- : md_lob IN OUT NOCOPY CLOB Required
317 --Note :
318 -- End of comments
319 PROCEDURE okc_rep_ver_md(
320 r_id IN ROWID,
321 md_lob IN OUT NOCOPY CLOB)
322 IS
323 l_api_name VARCHAR2(32);
324
325 TYPE CurTyp IS REF CURSOR; -- define weak REF CURSOR type
326 con_cur CurTyp; -- declare cursor variable
327
328 sql_stmt VARCHAR2(10000);
329
330 TYPE OrgNameList IS TABLE OF VARCHAR(200);
331 TYPE ConNumList IS TABLE OF VARCHAR(200);
332 TYPE ConNameList IS TABLE OF VARCHAR(450);
333 TYPE ConDescList IS TABLE OF VARCHAR(2000);
334 TYPE KeywordList IS TABLE OF VARCHAR(2000);
335 TYPE CommentList IS TABLE OF VARCHAR(2000);
336 TYPE PartyNameList IS TABLE OF VARCHAR(200);
337 TYPE ContactnameList IS TABLE OF VARCHAR(200);
338
339 org_name OrgNameList;
340 contract_number ConNumList;
341 contract_name ConNameList;
342 description ConDescList;
343 keywords KeywordList;
344 version_comments CommentList;
345 party_name PartyNameList;
346 contact_name ContactnameList;
347
348 BEGIN
349 --initialize local variables
350 l_api_name := 'okc_rep_ver_md';
351
352 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
353 FND_LOG.STRING(
354 FND_LOG.LEVEL_PROCEDURE,
355 G_MODULE||l_api_name,
356 'Entering APPS.OKC_REP_CONTRACT_TEXT_IDX_PVT.okc_rep_ver_md');
357 END IF;
358
359 -----------------------------------------
360 -- Add contract metadata
361 -----------------------------------------
362
363 sql_stmt :=
364 'SELECT '||
365 ' NVL(o.name,'' '') as org_name '||
366 ' ,NVL(c.contract_number,'' '') as contract_number '||
367 ' ,NVL(c.contract_name,'' '') as contract_name '||
368 ' ,NVL(c.contract_desc,'' '') as description '||
369 ' ,NVL(c.keywords,'' '') as keywords '||
370 ' ,NVL(c.version_comments,'' '') as version_comments '||
371 'FROM '||
372 'okc_rep_contract_vers c, '||
373 'hr_all_organization_units_vl o '||
374 'WHERE c.org_id = o.organization_id '||
375 'AND c.rowid = :1 ';
376
377 OPEN con_cur FOR sql_stmt USING r_id;
378 FETCH con_cur BULK COLLECT INTO org_name, contract_number, contract_name, description, keywords, version_comments;
379
380 IF org_name.COUNT <> 0 THEN
381 FOR i IN org_name.FIRST..org_name.LAST LOOP
382 DBMS_LOB.WRITEAPPEND( md_lob, length(org_name(i))+1, org_name(i) || ' ' );
383 DBMS_LOB.WRITEAPPEND( md_lob, length(contract_number(i))+1, contract_number(i) || ' ' );
384 DBMS_LOB.WRITEAPPEND( md_lob, length(contract_name(i))+1, contract_name(i) || ' ' );
385 DBMS_LOB.WRITEAPPEND( md_lob, length(description(i))+1, description(i) || ' ' );
386 DBMS_LOB.WRITEAPPEND( md_lob, length(keywords(i))+1, keywords(i) || ' ' );
387 DBMS_LOB.WRITEAPPEND( md_lob, length(version_comments(i))+1, version_comments(i) || ' ' );
388 END LOOP;
389 END IF;
390
391 CLOSE con_cur;
392
393 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
394 FND_LOG.STRING(
395 FND_LOG.LEVEL_PROCEDURE,
396 G_MODULE||l_api_name,
397 'Added contract metadata');
398 END IF;
399
400 -----------------------------------------
401 -- Add party metadata
402 -----------------------------------------
403
404 sql_stmt :=
405 'SELECT '||
406 ' NVL(o.name,'' '') as party_name '||
407 'FROM '||
408 'okc_rep_contract_vers c, '||
409 'okc_rep_contract_parties p, '||
410 'hr_all_organization_units_vl o '||
411 'WHERE c.contract_id = p.contract_id '||
415
412 'AND p.party_id = o.organization_id '||
413 'AND p.party_role_code = '''|| G_PARTY_TYPE_INTERNAL || ''' ' ||
414 'AND c.rowid = :1 ';
416 OPEN con_cur FOR sql_stmt USING r_id;
417 FETCH con_cur BULK COLLECT INTO party_name;
418
419 IF party_name.COUNT <> 0 THEN
420 FOR i IN party_name.FIRST..party_name.LAST LOOP
421 DBMS_LOB.WRITEAPPEND( md_lob, length(party_name(i))+1, party_name(i) || ' ' );
422 END LOOP;
423 END IF;
424
425 CLOSE con_cur;
426
427 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
428 FND_LOG.STRING(
429 FND_LOG.LEVEL_PROCEDURE,
430 G_MODULE||l_api_name,
431 'Added internal party metadata');
432 END IF;
433
434
435 sql_stmt :=
436 'SELECT '||
437 ' NVL(v.vendor_name,'' '') as party_name '||
438 'FROM '||
439 'okc_rep_contract_vers c, '||
440 'okc_rep_contract_parties p, '||
441 'po_vendors v '||
442 'WHERE c.contract_id = p.contract_id '||
443 'AND p.party_id = v.vendor_id '||
444 'AND p.party_role_code = '''|| G_PARTY_TYPE_SUPPLIER || ''' ' ||
445 'AND c.rowid = :1 ';
446
447 OPEN con_cur FOR sql_stmt USING r_id;
448 FETCH con_cur BULK COLLECT INTO party_name;
449
450 IF party_name.COUNT <> 0 THEN
451 FOR i IN party_name.FIRST..party_name.LAST LOOP
452 DBMS_LOB.WRITEAPPEND( md_lob, length(party_name(i))+1, party_name(i) || ' ' );
453 END LOOP;
454 END IF;
455
456 CLOSE con_cur;
457
458 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
459 FND_LOG.STRING(
460 FND_LOG.LEVEL_PROCEDURE,
461 G_MODULE||l_api_name,
462 'Added supplier party metadata');
463 END IF;
464
465 sql_stmt :=
466 'SELECT '||
467 ' NVL(hz.party_name,'' '') as party_name '||
468 'FROM '||
469 'okc_rep_contract_vers c, '||
470 'okc_rep_contract_parties p, '||
471 'hz_parties hz '||
472 'WHERE c.contract_id = p.contract_id '||
473 'AND p.party_id = hz.party_id '||
474 'AND p.party_role_code IN (''' || G_PARTY_TYPE_CUSTOMER ||''', '''|| G_PARTY_TYPE_PARTNER || ''') '||
475 'AND c.rowid = :1 ';
476
477 OPEN con_cur FOR sql_stmt USING r_id;
478 FETCH con_cur BULK COLLECT INTO party_name;
479
480 IF party_name.COUNT <> 0 THEN
481 FOR i IN party_name.FIRST..party_name.LAST LOOP
482 DBMS_LOB.WRITEAPPEND( md_lob, length(party_name(i))+1, party_name(i) || ' ' );
483 END LOOP;
484 END IF;
485
486
487 CLOSE con_cur;
488
489 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
490 FND_LOG.STRING(
491 FND_LOG.LEVEL_PROCEDURE,
492 G_MODULE||l_api_name,
493 'Added partner party metadata');
494 END IF;
495
496 -----------------------------------------
497 -- Add contact metadata
498 -----------------------------------------
499
500 sql_stmt :=
501 'SELECT '||
502 ' NVL(per.full_name,'' '') as contact_name '||
503 'FROM '||
504 'okc_rep_contract_vers c, '||
505 'okc_rep_party_contacts ct, '||
506 'per_all_people_f per '||
507 'WHERE c.contract_id = ct.contract_id '||
508 'AND ct.party_role_code = ''' || G_PARTY_TYPE_INTERNAL || ''' ' ||
509 'AND ct.contact_id = per.person_id '||
510 'AND c.rowid = :1 ';
511
512 OPEN con_cur FOR sql_stmt USING r_id;
513 FETCH con_cur BULK COLLECT INTO contact_name;
514
515 IF contact_name.COUNT <> 0 THEN
516 FOR i IN contact_name.FIRST..contact_name.LAST LOOP
517 DBMS_LOB.WRITEAPPEND( md_lob, length(contact_name(i))+1, contact_name(i) || ' ');
518 END LOOP;
522
519 END IF;
520
521 CLOSE con_cur;
523 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
524 FND_LOG.STRING(
525 FND_LOG.LEVEL_PROCEDURE,
526 G_MODULE||l_api_name,
527 'Added internal party contact metadata');
528 END IF;
529
530 sql_stmt :=
531 'SELECT '||
532 ' NVL2(v.last_name, v.last_name||'' ''||v.first_name, '' '') as contact_name '||
533 'FROM '||
534 'okc_rep_contract_vers c, '||
535 'okc_rep_party_contacts ct, '||
536 'po_vendor_contacts v '||
537 'WHERE c.contract_id = ct.contract_id '||
538 'AND ct.contact_id = v.vendor_contact_id '||
539 'AND ct.PARTY_ROLE_CODE = '''|| G_PARTY_TYPE_SUPPLIER || ''' ' ||
540 'AND c.rowid = :1 ';
541
542 OPEN con_cur FOR sql_stmt USING r_id;
543 FETCH con_cur BULK COLLECT INTO contact_name;
544
545 IF contact_name.COUNT <> 0 THEN
546 FOR i IN contact_name.FIRST..contact_name.LAST LOOP
547 DBMS_LOB.WRITEAPPEND( md_lob, length(contact_name(i))+1, contact_name(i) || ' ' );
548 END LOOP;
549 END IF;
550
551 CLOSE con_cur;
552
553 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
554 FND_LOG.STRING(
555 FND_LOG.LEVEL_PROCEDURE,
556 G_MODULE||l_api_name,
557 'Added supplier party contact metadata');
558 END IF;
559
560 sql_stmt :=
561 'SELECT '||
562 ' hz.party_name AS contact_name '||
563 'FROM '||
564 'okc_rep_contract_vers c, '||
565 'okc_rep_party_contacts ct, '||
566 'hz_parties hz '||
567 'WHERE c.contract_id = ct.contract_id '||
568 'AND ct.party_role_code IN ( '''||G_PARTY_TYPE_CUSTOMER ||''', '''|| G_PARTY_TYPE_PARTNER || ''') '||
569 'AND ct.contact_id = hz.party_id '||
570 'AND c.rowid = :1 ';
571
572 OPEN con_cur FOR sql_stmt USING r_id;
573 FETCH con_cur BULK COLLECT INTO contact_name;
574
575 IF contact_name.COUNT <> 0 THEN
576 FOR i IN contact_name.FIRST..contact_name.LAST LOOP
577 DBMS_LOB.WRITEAPPEND( md_lob, length(contact_name(i))+1, contact_name(i) || ' ' );
578 END LOOP;
579 END IF;
580
581 CLOSE con_cur;
582
583 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
584 FND_LOG.STRING(
585 FND_LOG.LEVEL_PROCEDURE,
586 G_MODULE||l_api_name,
587 'Added customer and partner party contact metadata');
588 END IF;
589
590 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
591 FND_LOG.STRING(
592 FND_LOG.LEVEL_PROCEDURE,
593 G_MODULE||l_api_name,
594 'Leaving APPS.OKC_REP_CONTRACT_TEXT_IDX_PVT.okc_rep_ver_md');
595 END IF;
596 END;
597
598 END;