1 PACKAGE BODY OKC_REP_SEARCH_UTIL_PVT AS
2 /* $Header: OKCVREPSRCHUTILB.pls 120.0.12010000.3 2009/01/08 13:56:27 kkolukul noship $ */
3
4
5 ---------------------------------------------------------------------------
6 -- PROCEDURES
7 ---------------------------------------------------------------------------
8
9 -- API name : get_rep_doc_acl
10 -- Type : Private.
11 -- Function : Called in the BusDocSearchPlugIn class by the AppSearch
12 -- Framework at crawl-time to get the ACL list for the given
13 -- contract document (for repository contracts) or for the
14 -- given contract document and version (for archived
15 -- contracts). The list of ACLs is then indexed with the
16 -- document to be used at query-time to determine whether
17 -- a user has access to the document.
18 -- Pre-reqs : None.
19 -- Parameters :
20 -- IN : p_bus_doc_id IN NUMBER
21 -- The document ID of the contract document currently
22 -- being crawled.
23 -- IN : p_bus_doc_version IN NUMBER
24 -- The version of the contract document currently being
25 -- crawled.
26 -- IN p_driving_table IN VARCHAR2
27 -- The table that drives the VO associated with the
28 -- contract document currently being crawled. There are
29 -- two possible values: 'okc_rep_contracts_all' (for
30 -- repository contracts) and 'okc_rep_contract_vers'
31 -- (for archived contracts).
32 -- OUT : x_acl OUT VARCHAR2
33 -- A space-delimited string of ACL keys that define
34 -- access to this document.
35 -- Example: g32324 g4434 u88932 u72223 admin_acl o23452
36 -- Note :
37 PROCEDURE get_rep_doc_acl
38 ( p_bus_doc_id IN NUMBER,
39 p_bus_doc_version IN NUMBER,
40 p_driving_table IN VARCHAR2,
41 x_acl OUT NOCOPY VARCHAR2)
42 IS
43 l_api_name VARCHAR2(30);
44 l_use_acl_flag VARCHAR2(1);
45
46 -- Get group IDs for a given contract
47 CURSOR group_csr
48 IS
49 SELECT g.parameter2 group_id
50 FROM
51 fnd_grants g,
52 fnd_objects o
53 WHERE
54 g.object_id = o.object_id
55 AND o.obj_name = 'OKC_REP_CONTRACT'
56 AND g.instance_pk1_value = p_bus_doc_id
57 AND g.parameter1 = 'GROUP';
58
59 -- Get user IDs for a given contract
60 CURSOR user_csr
61 IS
62 SELECT g.parameter2 user_id
63 FROM
64 fnd_grants g,
65 fnd_objects o
66 WHERE
67 g.object_id = o.object_id
68 AND o.obj_name = 'OKC_REP_CONTRACT'
69 AND g.instance_pk1_value = p_bus_doc_id
70 AND g.parameter1 <> 'GROUP';
71
72 -- Get owner IDs for a given repository contract
73 CURSOR owner_rep_csr
74 IS
75 SELECT owner_id
76 FROM okc_rep_contracts_all
77 WHERE contract_id = p_bus_doc_id;
78
79 -- Get owner IDs for a given archive contract
80 CURSOR owner_archive_csr
81 IS
82 SELECT owner_id
83 FROM okc_rep_contract_vers
84 WHERE
85 contract_id = p_bus_doc_id
86 AND contract_version_num = p_bus_doc_version;
87
88 group_rec group_csr%ROWTYPE;
89 user_rec user_csr%ROWTYPE;
90 owner_rep_rec owner_rep_csr%ROWTYPE;
91 owner_archive_rec owner_archive_csr%ROWTYPE;
92
93 BEGIN
94
95 l_api_name := 'get_rep_doc_acl';
96
97 -- Do logging.
98 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
99 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,
100 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
101 '200: Entered OKC_REP_SEARCH_UTIL_PVT.get_rep_doc_acl');
102 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,
103 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
104 '201: Contract Id is: ' || to_char(p_bus_doc_id));
105 END IF;
106 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
107 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,
108 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
109 'Input parameters are: p_bus_doc_id = ' || p_bus_doc_id ||
110 ', p_bus_doc_version = ' || p_bus_doc_version ||
111 ', p_driving_table = ' || p_driving_table ||
112 '.');
113 END IF;
114
115 -- 1. Get contract.use_acl_flag for the given p_bus_doc_id.
116 -- nvl: if the value returned is null, set it to 'N'.
117 SELECT nvl(use_acl_flag,'N')
118 INTO l_use_acl_flag
119 FROM okc_rep_contracts_all
120 WHERE contract_id = p_bus_doc_id;
121
122 -- 2. If (contract.use_acl_flag == false), then add the string
123 -- 'no_acl' to the ACL.
124 IF (l_use_acl_flag = 'N')
125 THEN
126 x_acl := x_acl || 'no_acl' || ' ';
127
128 -- 3. Else:
129 ELSE
130 -- a. Get all the groups having access to this contract. Add
131 -- the concatenated string g<group_id> g<group_id>.. to the
132 -- ACL.
133 FOR group_rec IN group_csr
134 LOOP
135 x_acl := x_acl || 'g' || to_char(group_rec.group_id) || ' ';
136 END LOOP;
137
138 -- b. Get all the users having access to this contract. Add
139 -- the concatenated string u<user_id> u<user_id> to the ACL.
140 FOR user_rec IN user_csr
141 LOOP
142 x_acl := x_acl || 'u' || to_char(user_rec.user_id) || ' ';
143 END LOOP;
144 END IF;
145
146 -- 4. Add the string 'admin_acl' to the ACL.
147 x_acl := x_acl || 'admin_acl' || ' ';
148
149 -- 5. Add the contract owner's user id o<user_id> to the ACL.
150 -- If the driving table is 'okc_rep_contracts_all' (repository
151 -- contracts), we need to match only the document ID and not the
152 -- document version, so we use the owner_rep_csr cursor.
153 IF (p_driving_table = 'okc_rep_contracts_all')
154 THEN
155 FOR owner_rep_rec IN owner_rep_csr
156 LOOP
157 x_acl := x_acl || 'o' || to_char(owner_rep_rec.owner_id) || ' ';
158 END LOOP;
159 -- Else, the driving table is 'okc_rep_contract_vers' (the archive of
160 -- contracts) and we need to match the version as well as the ID. See
161 -- the owner_archive_csr cursor.
162 ELSE
163 FOR owner_archive_rec IN owner_archive_csr
164 LOOP
165 x_acl := x_acl || 'o' || to_char(owner_archive_rec.owner_id)
166 || ' ';
167 END LOOP;
168 END IF;
169
170 -- Do logging.
171 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
172 THEN
173 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,
174 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
175 'OKC_REP_UTIL_PVT.get_rep_doc_acl
176 returns x_acl as : '
177 || x_acl);
178 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,
179 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
180 'Leaving get_rep_doc_acl');
181 END IF;
182
183 EXCEPTION
184 WHEN OTHERS
185 THEN
186 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
187 THEN
188 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION ,
189 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'
190 ||l_api_name,
191 'Leaving PROCEDURE get_rep_doc_acl
192 because of EXCEPTION: '
193 ||sqlerrm);
194 END IF;
195 Okc_Api.Set_Message(p_app_name => 'OKC',
196 p_msg_name => OKC_REP_UTIL_PVT.G_UNEXPECTED_ERROR,
197 p_token1 => OKC_REP_UTIL_PVT.G_SQLCODE_TOKEN,
198 p_token1_value => sqlcode,
199 p_token2 => OKC_REP_UTIL_PVT.G_SQLERRM_TOKEN,
200 p_token2_value => sqlerrm);
201
202 END;
203
204
205 -- API name : get_current_user_acl_keys
206 -- Type : Private.
207 -- Function : Called in the BusDocSearchPlugIn class by the AppSearch
208 -- Framework at query-time to get the ACL keys for the
209 -- current user. This list of ACL keys is then used to
210 -- check each search hit and verify that the user is
211 -- permitted to view that specific hit. If a user is
212 -- not permitted to view a given hit, then that hit is
213 -- removed from the query result set.
214 -- Pre-reqs : None.
215 -- Parameters :
216 -- IN : None.
217 -- OUT : x_keys OUT VARCHAR2
218 -- A space-delimited string of ACL keys that define
219 -- the user's access rights.
220 -- Example: no_acl o23452 u23452 g2241 g2308
221 -- Note :
222 PROCEDURE get_current_user_acl_keys
223 ( x_keys OUT NOCOPY VARCHAR2)
224 IS
225 l_api_name VARCHAR2(30);
226 l_resource_id NUMBER;
227 l_user_id NUMBER;
228
229 -- Get the resource ids of the current user.
230 CURSOR user_csr IS
231 SELECT resource_id
232 FROM jtf_rs_resource_extns
233 WHERE user_id = FND_GLOBAL.user_id();
234
235
236 -- Get all the groups for a resource id.
237 CURSOR group_csr(l_resource_id NUMBER) IS
238 SELECT group_id
239 FROM jtf_rs_group_members
240 WHERE resource_id=l_resource_id;
241
242 user_rec user_csr%ROWTYPE;
243 group_rec group_csr%ROWTYPE;
244
245 BEGIN
246 l_api_name := 'get_current_user_acl_keys';
247
248 -- Do logging.
249 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
250 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,
251 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
252 '200: Entered OKC_REP_SEARCH_UTIL_PVT.get_current_user_acl_keys');
253 END IF;
254
255 -- 1. Add the string 'no_acl' to the ACL keys.
256 x_keys := 'no_acl' || ' ';
257
258 -- 2. Add the current user o<user_id> to the ACL keys.
259 l_user_id := FND_GLOBAL.user_id();
260 x_keys := x_keys || 'o' || to_char(l_user_id) || ' ';
261
262 -- 3. If (the current user has admin responsibility), then add
263 -- 'admin_acl' and return the list of ACL keys.
264 IF (FND_FUNCTION.TEST(OKC_REP_UTIL_PVT.G_FUNC_OKC_REP_ADMINISTRATOR,'Y'))
265 THEN
266 x_keys := x_keys || 'admin_acl';
267
268 -- 4. Else:
269 ELSE
270
271 -- a. Add the current user u<user_id> to the ACL keys.
272 x_keys := x_keys || 'u' || to_char(l_user_id) || ' ';
273
274 -- b. Get all the groups of the current user. Add the
275 -- concatenated string g<group_id> g<group_id>.. to the ACL keys.
276 FOR user_rec IN user_csr
277 LOOP
278 l_resource_id := user_rec.resource_id;
279 FOR group_rec IN group_csr(l_resource_id)
280 LOOP
281 x_keys := x_keys || 'g' || to_char(group_rec.group_id) || ' ';
282 END LOOP;
283 END LOOP;
284
285 END IF;
286
287 -- Do logging.
288 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
289 THEN
290 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,
291 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
292 'OKC_REP_UTIL_PVT.get_current_user_acl_keys
293 returns x_keys as : '
294 || x_keys);
295 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,
296 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
297 'Leaving get_current_user_acl_keys');
298 END IF;
299
300 EXCEPTION
301 WHEN OTHERS
302 THEN
303 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
304 THEN
305 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION ,
306 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'
307 ||l_api_name,
308 'Leaving PROCEDURE get_current_user_acl_keys
309 because of EXCEPTION: '
310 ||sqlerrm);
311 END IF;
312 Okc_Api.Set_Message(p_app_name => 'OKC',
313 p_msg_name => OKC_REP_UTIL_PVT.G_UNEXPECTED_ERROR,
314 p_token1 => OKC_REP_UTIL_PVT.G_SQLCODE_TOKEN,
315 p_token1_value => sqlcode,
316 p_token2 => OKC_REP_UTIL_PVT.G_SQLERRM_TOKEN,
317 p_token2_value => sqlerrm);
318 END;
319
320
321 -- API name : get_current_user_moac_keys
322 -- Type : Private.
323 -- Function : Called in the BusDocSearchPlugIn class by the AppSearch
324 -- Framework at query-time to get the list of operating
325 -- units that the current user may access. This list of
326 -- operating unit IDs is then used to check each search hit
327 -- and verify that the user is permitted to view that
328 -- specific hit. If a user is not permitted to view a given
329 -- hit, then that hit is removed from the query result set.
330 -- Pre-reqs : None.
331 -- Parameters :
332 -- IN : None.
333 -- OUT : x_keys OUT VARCHAR2
334 -- A space-delimited string of the IDs of the operating
335 -- units to which the current user has access.
336 -- Example: 134 325 4384
337 -- Note :
338 PROCEDURE get_current_user_moac_keys
339 ( x_keys OUT NOCOPY VARCHAR2)
340 IS
341 l_api_name VARCHAR2(30);
342 l_ou_tab MO_GLOBAL.OrgIdTab;
343
344 l_user_id NUMBER;
345 l_resp_id NUMBER;
346 l_resp_appl_id NUMBER;
347 l_security_grp_id NUMBER;
348
349 CURSOR get_moac_org_id IS
350 SELECT ou.organization_id org_id
351 FROM hr_operating_units ou
352 WHERE mo_global.check_access(ou.organization_id) = 'Y';
353
354
355 BEGIN
356
357 l_api_name := 'get_current_user_moac_keys';
358
359 -- Do logging.
360 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
361 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,
362 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
363 '200: Entered OKC_REP_SEARCH_UTIL_PVT.get_current_user_moac_keys');
364 END IF;
365
366 l_user_id := FND_GLOBAL.user_id;
367 l_resp_id := FND_GLOBAL.RESP_ID;
368 l_security_grp_id := FND_GLOBAL.SECURITY_GROUP_ID;
369 l_resp_appl_id := 510;
370
371 fnd_global.apps_initialize(l_user_id,l_resp_id, l_resp_appl_id);
372 MO_GLOBAL.init('OKC');
373
374
375
376 -- 1. Call MO_GLOBAL.get_ou_tab to get the current user's accessible
377 -- operating units in a temporary table.
378 --
379 -- (Uncomment the following for testing purposes)
380 -- MO_GLOBAL.init('OKC');
381 -- MO_GLOBAL.set_policy_context('M',204);
382 --l_ou_tab := MO_GLOBAL.get_ou_tab;//Commented this.Using the cursor instead
383
384 OPEN get_moac_org_id;
385 FETCH get_moac_org_id BULK COLLECT INTO l_ou_tab;
386 CLOSE get_moac_org_id;
387
388
389 -- 2. Create a space-separated string of operating unit IDs using the
390 -- temp table from step 1.
391 IF (l_ou_tab.COUNT > 0)
392 THEN
393 FOR i IN l_ou_tab.FIRST .. l_ou_tab.LAST
394 LOOP
395 x_keys := x_keys || ' ' || l_ou_tab(i);
396 END LOOP;
397 END IF;
398
399 -- Do logging.
400 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
401 THEN
402 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,
403 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
404 'OKC_REP_UTIL_PVT.get_current_user_moac_keys
405 returns x_keys as : '
406 || x_keys);
407 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,
408 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
409 'Leaving get_current_user_moac_keys');
410 END IF;
411
412 EXCEPTION
413 WHEN OTHERS
414 THEN
415 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
416 THEN
417 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION ,
418 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
419 'Leaving PROCEDURE get_current_user_moac_keys because of
420 EXCEPTION: '||sqlerrm);
421 END IF;
422 Okc_Api.Set_Message(p_app_name => 'OKC',
423 p_msg_name => OKC_REP_UTIL_PVT.G_UNEXPECTED_ERROR,
424 p_token1 =>OKC_REP_UTIL_PVT. G_SQLCODE_TOKEN,
425 p_token1_value => sqlcode,
426 p_token2 => OKC_REP_UTIL_PVT.G_SQLERRM_TOKEN,
427 p_token2_value => sqlerrm);
428 END;
429
430
431 -- API name : get_intent_profile_keys
432 -- Type : Private.
433 -- Function : Called in the BusDocSearchPlugIn class by the AppSearch
434 -- Framework at query-time to get the list of intent codes
435 -- that represent the types of contracts (e.g. Buy, Sell)
436 -- that the current user may access. This list of
437 -- intent codes is then used to check each search hit
438 -- and verify that the user is permitted to view that
439 -- specific hit. If a user is not permitted to view a given
440 -- hit, then that hit is removed from the query result set.
441 -- Pre-reqs : None.
442 -- Parameters :
443 -- IN : None.
444 -- OUT : x_keys OUT VARCHAR2
445 -- A string of single-character intent codes that
446 -- represent the types of contracts (e.g. Buy, Sell) to
447 -- which the current user has access. This string is
448 -- parse into another string containing the single
449 -- characters separated by spaces in the BusDocSearchPlugIn
450 -- class (e.g. "SA" -> "S A").
451 -- Example: BA
452 -- Example: BSOA
453 -- Note :
454 PROCEDURE get_intent_profile_keys
455 ( x_keys OUT NOCOPY VARCHAR2)
456 IS
457 l_api_name VARCHAR2(30);
458
459 BEGIN
460
461 l_api_name := 'get_intent_profile_keys';
462
463 -- Do logging.
464 IF ( FND_LOG.LEVEL_PROCEDURE>= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
465 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,
466 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
467 '200: Entered OKC_REP_SEARCH_UTIL_PVT.get_intent_profile_keys');
468 END IF;
469
470 -- 1. Call FND_PROFILE.value('OKC_REP_INTENTS') to get the intent
471 -- codes from the FND profile.
472 x_keys := FND_PROFILE.value('OKC_REP_INTENTS');
473
474 x_keys := x_keys;
475
476 -- 2. Create a space-separated string of intent codes from step 1.
477 -- Will do this in the Java level.
478
479 -- Do logging.
480 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
481 THEN
482 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,
483 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
484 'OKC_REP_UTIL_PVT.get_intent_profile_keys
485 returns x_keys as : '
486 || x_keys);
487 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,
488 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
489 'Leaving get_intent_profile_keys');
490 END IF;
491
492 EXCEPTION
493 WHEN OTHERS
494 THEN
495 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
496 THEN
497 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION,
498 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
499 'Leaving PROCEDURE get_current_intent_profile_keys
500 because of EXCEPTION: '||sqlerrm);
501 END IF;
502 Okc_Api.Set_Message(p_app_name => 'OKC',
503 p_msg_name => OKC_REP_UTIL_PVT.G_UNEXPECTED_ERROR,
504 p_token1 => OKC_REP_UTIL_PVT.G_SQLCODE_TOKEN,
505 p_token1_value => sqlcode,
506 p_token2 => OKC_REP_UTIL_PVT.G_SQLERRM_TOKEN,
507 p_token2_value => sqlerrm);
508
509 END;
510
511 -- API name : get_current_user_quote_access
512 -- Type : Private.
513 -- Function : Called in the BusDocSearchPlugIn class in the
514 -- queryPostProcess() method at query-time to discover the
515 -- current user's level of access for a given Sales Quote
516 -- document. The possible return values are UPDATE, READ,
517 -- and NONE. If a user is not permitted to view a given
518 -- quote (meaning that this procedure returns NONE), then
519 -- that quote document is removed from the query result set.
520 -- Pre-reqs : None.
521 -- Parameters :
522 -- IN : p_quote_number IN NUMBER
523 -- The ID number of the sales quote document
524 -- currently being processed.
525 -- OUT : x_access OUT VARCHAR2
526 -- A string that represents the current user's level of
527 -- access for the given Sales Quote document.
528 -- Note :
529 PROCEDURE get_current_user_quote_access
530 ( p_quote_number IN NUMBER,
531 x_access OUT NOCOPY VARCHAR2)
532 IS
533 l_api_name VARCHAR2(30);
534 l_resource_id VARCHAR2(30);
535
536 BEGIN
537
538 l_api_name := 'get_current_user_quote_access';
539
540 -- Do logging.
541 IF ( FND_LOG.LEVEL_PROCEDURE>= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
542 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,
543 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
544 '200: Entered OKC_REP_SEARCH_UTIL_PVT.get_current_user_quote_access');
545 END IF;
546 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
547 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,
548 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
549 'Input parameter is: p_quote_number = ' || p_quote_number||
550 '.');
551 END IF;
552
553 -- 1. Get the resource_id associated with the current user.
554 SELECT resource_id
555 INTO l_resource_id
556 FROM jtf_rs_resource_extns
557 WHERE user_id = FND_GLOBAL.user_id;
558
559 -- 2. If the resource_id is null, return 'NONE'.
560 IF (l_resource_id = NULL)
561 THEN
562 x_access := 'NONE';
563 ELSE
564 -- Else, use the ASO_SECURITY_INT API get_quote_access to
565 -- retrieve the access level. Possible values are
566 -- UPDATE, READ, and NONE. If null is returned, then
567 -- set x_access to NONE.
568 x_access := aso_security_int.get_quote_access(
569 l_resource_id, p_quote_number);
570 IF (x_access = NULL)
571 THEN
572 x_access := 'NONE';
573 END IF;
574 END IF;
575
576 -- Do logging.
577 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
578 THEN
579 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,
580 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
581 'OKC_REP_UTIL_PVT.get_current_user_quote_access
582 returns x_access as : '
583 || x_access);
584 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,
585 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
586 'Leaving get_current_user_quote_access');
587 END IF;
588
589 EXCEPTION
590 WHEN OTHERS
591 THEN
592 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
593 THEN
594 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION ,
595 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
596 'Leaving PROCEDURE get_current_user_quote_access
597 because of EXCEPTION: '||sqlerrm);
598 END IF;
599 Okc_Api.Set_Message(p_app_name => 'OKC',
600 p_msg_name => OKC_REP_UTIL_PVT.G_UNEXPECTED_ERROR,
601 p_token1 => OKC_REP_UTIL_PVT.G_SQLCODE_TOKEN,
602 p_token1_value => sqlcode,
603 p_token2 => OKC_REP_UTIL_PVT.G_SQLERRM_TOKEN,
604 p_token2_value => sqlerrm);
605
606 END;
607
608 -- API name : get_local_language_attributes
609 -- Type : Private.
610 -- Function : Called in the BusDocSearchPlugIn class in the
611 -- queryPostProcess() method at query-time to fetch
612 -- three language-dependent attributes: document type,
613 -- intent, and status. The local language values of
614 -- these three attributes are returned in a comma-
615 -- delimited string in the order docType, intent, status.
616 -- Parameters :
617 -- IN : p_doc_type_code IN NUMBER
618 -- The document type code of the contract document
619 -- currently being processed by queryPostProcess().
620 -- IN : p_intent_code IN NUMBER
621 -- The intent code of the contract document currently
622 -- being processed by queryPostProcess().
623 -- IN : p_status_code IN NUMBER
624 -- The status code of the contract document currently
625 -- being processed by queryPostProcess().
626 -- OUT : x_attrs OUT VARCHAR2
627 -- A string containing three sub-strings separated by
628 -- commas. These three sub-strings represent the
629 -- local-language value of document type, intent,
630 -- and status.
631 -- Note :
632 PROCEDURE get_local_language_attributes
633 ( p_doc_type_code IN VARCHAR,
634 p_intent_code IN VARCHAR,
635 p_status_code IN VARCHAR,
636 x_attrs OUT NOCOPY VARCHAR2)
637 IS
638 l_api_name VARCHAR2(30);
639 l_doc_type VARCHAR2(500);
640 l_intent VARCHAR2(500);
641 l_status VARCHAR2(500);
642 l_lookup_type VARCHAR2(50);
643
644 BEGIN
645
646 l_api_name := 'get_local_language_attributes';
647
648 -- Do logging.
649 IF ( FND_LOG.LEVEL_PROCEDURE>= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
650 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,
651 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
652 '200: Entered OKC_REP_SEARCH_UTIL_PVT.get_local_language_attributes');
653 END IF;
654 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
655 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,
656 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
657 'Input parameters are: p_doc_type_code = ' || p_doc_type_code ||
658 ', p_intent_code = ' || p_intent_code ||
659 ', p_status_code = ' || p_status_code ||
660 '.');
661 END IF;
662
663 -- 1. Get the document type. If the type is null (meaning that
664 -- there is no entry in okc_bus_doc_types_vl for that doc type code),
665 -- set the document type to be the document type code. We nest the
666 -- query in its own block in order to catch No Data Found
667 -- exceptions.
668 SELECT name
669 INTO l_doc_type
670 FROM okc_bus_doc_types_vl
671 WHERE document_type = p_doc_type_code;
672
673
674 -- Append the document type to the attribute list.
675 x_attrs := l_doc_type || ',';
676
677 -- 2. Get the document intent. If the intent is null (meaning that
678 -- there is no entry in fnd_lookups for that intent code and lookup
679 -- type), set the intent to be the intent code. We nest the
680 -- query in its own block in order to catch No Data Found
681 -- exceptions.
682 SELECT meaning
683 INTO l_intent
684 FROM fnd_lookups
685 WHERE lookup_type = 'OKC_REP_CONTRACT_INTENTS'
686 AND lookup_code = p_intent_code;
687
688
689 -- Append the intent to the attribute list.
690 x_attrs := x_attrs || l_intent || ',';
691
692 -- 3. Get the document status and append it to x_attrs.
693 --
694 -- If the document type code is QUOTE, the status needs to be fetched
695 -- with this SQL statement.
696 IF (p_doc_type_code = 'QUOTE')
697 THEN
698 SELECT aqsvl.meaning
699 INTO l_status
700 FROM aso_quote_statuses_b aqsb,
701 aso_quote_statuses_vl aqsvl
702 WHERE aqsb.status_code = p_status_code
703 AND aqsb.quote_status_id = aqsvl.quote_status_id;
704
705 -- If the document type code is RFQ or RFI or AUCTION,
706 -- the status needs to be fetched with
707 -- this SQL statement.
708 ELSE
709 IF ((p_doc_type_code = 'RFQ') OR
710 (p_doc_type_code = 'RFI') OR
711 (p_doc_type_code = 'AUCTION'))
712 THEN
713
714 -- AUCTION_CLOSED status is a derived status
715 -- which is dependent on the user logged in. So
716 -- displaying a message.
717
718 IF( p_status_code = 'AUCTION_CLOSED')
719 THEN
720 SELECT MESSAGE_TEXT
721 INTO l_status
722 FROM FND_NEW_MESSAGES
723 WHERE language_code = USERENV('LANG')
724 AND application_id = 510
725 AND message_name = 'OKC_SES_AUC_STATUS_EXP';
726
727 ELSE
728
729 SELECT meaning
730 INTO l_status
731 FROM fnd_lookup_values
732 WHERE language = USERENV('LANG')
733 AND lookup_type = 'PON_AUCTION_STATUS'
734 AND lookup_code = p_status_code;
735 END IF;
736
737 -- Else, we need to set the lookup type and fetch the status with
738 -- this SQL statement.
739 ELSE
740 -- If the document type code is O or B, then the lookup type
741 -- is FLOW_STATUS
742 IF ((p_doc_type_code = 'B') OR
743 (p_doc_type_code = 'O'))
744 THEN
745 l_lookup_type := 'FLOW_STATUS';
746
747 -- Get the status using the lookup type.
748 SELECT meaning
749 INTO l_status
750 FROM oe_lookups
751 WHERE lookup_type = l_lookup_type
752 AND lookup_code = p_status_code;
753 ELSE
754 -- If the document type code is PA_BLANKET or PA_CONTRACT
755 -- or PO_STANDARD, then the lookup type is
756 -- AUTHORIZATION_STATUS
757 IF ((p_doc_type_code = 'PA_BLANKET') OR
758 (p_doc_type_code = 'PA_CONTRACT') OR
759 (p_doc_type_code = 'PO_STANDARD'))
760 THEN
761 l_lookup_type := 'AUTHORIZATION STATUS';
762 -- Else, the document type code is REP_% and the lookup
763 -- type is OKC_REP_CONTRACT_STATUSES.
764 ELSE
765 l_lookup_type := 'OKC_REP_CONTRACT_STATUSES';
766 END IF;
767
768 -- Get the status using the lookup type.
769 SELECT meaning
770 INTO l_status
771 FROM fnd_lookup_values
772 WHERE lookup_type = l_lookup_type
773 AND language = USERENV('LANG')
774 AND lookup_code = p_status_code;
775 END IF;
776 END IF;
777 END IF;
778
779 -- Append the status to the attribute list.
780 x_attrs := x_attrs || l_status;
781
782 -- Do logging.
783 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
784 THEN
785 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,
786 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
787 'OKC_REP_UTIL_PVT.get_local_language_attributes
788 returns x_attrs as : '
789 || x_attrs);
790 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,
791 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
792 'Leaving get_local_language_attributes');
793 END IF;
794
795 EXCEPTION
796 WHEN OTHERS
797 THEN
798 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
799 THEN
800 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED,
801 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
802 'Leaving PROCEDURE get_local_language_attributes
803 because of EXCEPTION: '||sqlerrm);
804 END IF;
805 Okc_Api.Set_Message(p_app_name => 'OKC',
806 p_msg_name => OKC_REP_UTIL_PVT.G_UNEXPECTED_ERROR,
807 p_token1 => OKC_REP_UTIL_PVT.G_SQLCODE_TOKEN,
808 p_token1_value => sqlcode,
809 p_token2 => OKC_REP_UTIL_PVT.G_SQLERRM_TOKEN,
810 p_token2_value => sqlerrm);
811 END;
812
813
814 -- API name : get_rep_parties
815 -- Type : Private.
816 -- Function : This function fetches the party names for a given
817 -- repository contract. It is called in the SQL statements
818 -- of RepHeaderSearchExpVO and RepArchiveSearchExpVO in the
819 -- oracle.apps.okc.repository.search.server package.
820 -- Pre-reqs : None.
821 -- Parameters :
822 -- IN : p_contract_id IN NUMBER
823 -- The contract ID of the contract document currently
824 -- being crawled.
825 -- OUT : x_parties OUT VARCHAR2
826 -- A string of party names that define
827 -- the parties of this repository contract. The party
828 -- names are separated by spaces.
829 -- Example: Vision, Inc. AT&T Informologics
830 -- Note :
831 FUNCTION get_rep_parties
832 ( p_contract_id IN NUMBER)
833 RETURN VARCHAR2
834 IS
835 l_api_name VARCHAR2(30);
836 l_party_name VARCHAR2(500);
837 l_parties VARCHAR2(2000);
838
839 -- Get the party role code associated with the given contract.
840 CURSOR party_csr IS
841 SELECT party_id, party_role_code
842 FROM okc_rep_contract_parties
843 WHERE contract_id = p_contract_id;
844
845 party_rec party_csr%ROWTYPE;
846
847 BEGIN
848
849 l_api_name := 'get_rep_parties';
850
851 -- Do logging.
852 IF ( FND_LOG.LEVEL_PROCEDURE>= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
853 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,
854 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
855 '200: Entered OKC_REP_SEARCH_UTIL_PVT.get_rep_parties');
856 END IF;
857 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
858 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,
859 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
860 'Input parameter is: p_contract_id = ' || p_contract_id||
861 '.');
862 END IF;
863
864 -- 1. Get party_role_code from okc_rep_contract_parties
865 -- for the given contract_id.
866 -- 2. For each record from step 1:
867 FOR party_rec IN party_csr
868 LOOP
869 -- a. If the party_role_code==INTERNAL_ORG, get the party name
870 -- from hr_all_organizational_units using
871 -- party_id=organization_id join
872 IF (party_rec.party_role_code = 'INTERNAL_ORG')
873 THEN
874 SELECT name
875 INTO l_party_name
876 FROM hr_all_organization_units
877 WHERE organization_id = party_rec.party_id;
878
879 -- b. Else If the party_role_code==SUPPLIER_ORG
880 -- get the party name from po_vendors using party_id join.
881 ELSIF (party_rec.party_role_code = 'SUPPLIER_ORG')
882 THEN
883 SELECT vendor_name
884 INTO l_party_name
885 FROM po_vendors
886 WHERE vendor_id = party_rec.party_id;
887 -- c. Else get the party name from hz_parties using party_id join.
888 ELSE
889 SELECT party_name
890 INTO l_party_name
891 FROM hz_parties
892 WHERE party_id = party_rec.party_id;
893 END IF;
894
895 --c. Append party name from step a or b to x_parties.
896 l_parties := l_parties || l_party_name || ' ';
897
898 END LOOP;
899
900 -- Do logging.
901 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
902 THEN
903 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,
904 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
905 'OKC_REP_UTIL_PVT.get_rep_parties returns l_parties as : '
906 || l_parties);
907 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,
908 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
909 'Leaving get_rep_parties');
910 END IF;
911
912 RETURN l_parties;
913
914 EXCEPTION
915 WHEN OTHERS
916 THEN
917 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
918 THEN
919 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION ,
920 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
921 'Leaving PROCEDURE get_rep_parties
922 because of EXCEPTION: '||sqlerrm);
923 END IF;
924 Okc_Api.Set_Message(p_app_name => 'OKC',
925 p_msg_name => OKC_REP_UTIL_PVT.G_UNEXPECTED_ERROR,
926 p_token1 => OKC_REP_UTIL_PVT.G_SQLCODE_TOKEN,
927 p_token1_value => sqlcode,
928 p_token2 => OKC_REP_UTIL_PVT.G_SQLERRM_TOKEN,
929 p_token2_value => sqlerrm);
930 END;
931
932
933 -- API name : get_terms_last_update_date.
934 -- Type : Private.
935 -- Function : This function returns the last_update_date value of the the business
936 -- document's contract terms.
937 -- Pre-reqs : None.
938 -- Parameters :
939 -- IN : p_document_type IN VARCHAR2 Required
940 -- Type of the document that is being checked
941 -- : p_document_id IN VARCHAR2 Required
942 -- Id of the document that is being checked
943 -- OUT : Returns the last_update_date value of the the business
944 -- document's contract terms.
945 FUNCTION get_terms_last_update_date(
946 p_document_type IN VARCHAR2,
947 p_document_id IN NUMBER
948 ) RETURN DATE
949 IS
950 l_api_name VARCHAR2(30);
951 l_has_access VARCHAR2(1);
952 l_return_status VARCHAR2(1);
953 l_msg_count NUMBER;
954 l_msg_data VARCHAR2(2000);
955 l_deliverable_changed_date DATE;
956 l_terms_changed_date DATE;
957
958 BEGIN
959
960 l_api_name := 'get_terms_last_update_date';
961 l_has_access := 'N';
962
963 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
964 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
965 'Entered Function OKC_REP_SEARCH_UTIL_PVT.get_terms_last_update_date');
966 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
967 'Document Id is: ' || p_document_id);
968 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
969 'Document Type is: ' || p_document_type);
970 END IF;
971 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
972 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
973 'Calling OKC_TERMS_UTIL_GRP.Get_Last_Update_Date');
974 END IF;
975 --- Call OKC_TERMS_UTIL_GRP.Get_Last_Update_Date procedure.
976
977 OKC_TERMS_UTIL_GRP.Get_Last_Update_Date(
978 p_api_version => 1.0,
979 p_init_msg_list => FND_API.G_FALSE,
980 x_msg_data => l_msg_data,
981 x_msg_count => l_msg_count,
982 x_return_status => l_return_status,
983 p_doc_type => p_document_type,
984 p_doc_id => p_document_id,
985 x_deliverable_changed_date => l_deliverable_changed_date,
986 x_terms_changed_date => l_terms_changed_date
987 );
988 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
989 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
990 'OKC_TERMS_UTIL_GRP.Get_Last_Update_Date return status is: '
991 || l_return_status);
992 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
993 'OKC_REP_UTIL_PVT.Get_Last_Update_Date returns x_terms_changed_date as : '
994 || l_terms_changed_date);
995 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
996 'Leaving Function get_terms_last_update_date');
997 END IF;
998 RETURN l_terms_changed_date ;
999
1000 EXCEPTION
1001 WHEN OTHERS THEN
1002 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1003 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION ,G_MODULE||l_api_name,
1004 'Leaving Function get_terms_last_update_date because of EXCEPTION: '||sqlerrm);
1005 END IF;
1006 Okc_Api.Set_Message(p_app_name => 'OKC',
1007 p_msg_name => OKC_REP_UTIL_PVT.G_UNEXPECTED_ERROR,
1008 p_token1 => OKC_REP_UTIL_PVT.G_SQLCODE_TOKEN,
1009 p_token1_value => sqlcode,
1010 p_token2 => OKC_REP_UTIL_PVT.G_SQLERRM_TOKEN,
1011 p_token2_value => sqlerrm);
1012 RETURN l_has_access ;
1013 END get_terms_last_update_date;
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023 -- API name : draft_attachment_exists.
1024 -- Type : Private.
1025 -- Function : This function returns Y if the generated draft attachment exists for
1026 -- the business document passed as input. It will also check that
1027 -- attachment's last_update_date is later than the business document's
1028 -- Term's last_update_date.
1029 -- Pre-reqs : None.
1030 -- Parameters :
1031 -- IN : p_document_type IN VARCHAR2 Required
1032 -- Type of the document that is being checked
1033 -- : p_document_id IN VARCHAR2 Required
1034 -- Id of the document that is being checked
1035 -- OUT : Return Y if the latest generated draft attachment exists for the
1036 -- business document, else returns N
1037 FUNCTION draft_attachment_exists(
1038 p_document_type IN VARCHAR2,
1039 p_document_id IN NUMBER
1040 ) RETURN VARCHAR2
1041
1042 IS
1043 l_api_name VARCHAR2(30);
1044 l_has_access VARCHAR2(1);
1045 l_return_status VARCHAR2(1);
1046 l_msg_count NUMBER;
1047 l_msg_data VARCHAR2(2000);
1048 l_terms_changed_date DATE;
1049 l_attachment_last_update_date DATE;
1050 l_attachment_exists VARCHAR2(1);
1051 l_results VARCHAR2(1);
1052
1053 CURSOR draft_attachment_csr IS
1054 SELECT last_update_date
1055 FROM okc_contract_docs_details_vl
1056 WHERE business_document_type = p_document_type
1057 AND business_document_id = p_document_id
1058 AND file_name like 'Text_Search_Gen_Attach%';
1059
1060 BEGIN
1061
1062 l_api_name := 'draft_attachment_exists';
1063 l_results := 'N';
1064 l_attachment_exists := 'N';
1065
1066 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1067 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1068 'Entered Function OKC_REP_SEARCH_UTIL_PVT.draft_attachment_exists');
1069 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1070 'Document Id is: ' || p_document_id);
1071 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1072 'Document Type is: ' || p_document_type);
1073 END IF;
1074 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1075 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1076 'Calling OKC_REP_SEARCH_UTIL_PVT.get_terms_last_update_date');
1077 END IF;
1078 --- Call OKC_REP_SEARCH_UTIL_PVT.Get_Last_Update_Date procedure.
1079
1080 l_terms_changed_date := OKC_REP_SEARCH_UTIL_PVT.get_terms_last_update_date(
1081 p_document_type => p_document_type,
1082 p_document_id => p_document_id
1083 );
1084
1085 OPEN draft_attachment_csr;
1086 FETCH draft_attachment_csr INTO l_attachment_last_update_date;
1087
1088 IF (draft_attachment_csr%rowcount > 0) THEN
1089 l_attachment_exists := 'Y';
1090 END IF;
1091
1092 CLOSE draft_attachment_csr;
1093
1094 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1095 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1096 'l_terms_changed_date: ' || l_terms_changed_date);
1097 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1098 'l_attachment_last_update_date: ' || l_attachment_last_update_date);
1099 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1100 'l_attachment_exists: ' || l_attachment_exists);
1101 END IF;
1102
1103 IF ((l_attachment_exists = 'N') OR (l_attachment_last_update_date < l_terms_changed_date))THEN
1104 l_results := 'N';
1105 ELSE
1106 l_results := 'Y';
1107 END IF;
1108
1109 RETURN l_results;
1110
1111 EXCEPTION
1112 WHEN OTHERS THEN
1113 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1114 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION ,G_MODULE||l_api_name,
1115 'Leaving Function draft_attachment_exists because of EXCEPTION: '||sqlerrm);
1116 END IF;
1117 IF (draft_attachment_csr%ISOPEN) THEN
1118 CLOSE draft_attachment_csr ;
1119 END IF;
1120 Okc_Api.Set_Message(p_app_name => 'OKC',
1121 p_msg_name => OKC_REP_UTIL_PVT.G_UNEXPECTED_ERROR,
1122 p_token1 => OKC_REP_UTIL_PVT.G_SQLCODE_TOKEN,
1123 p_token1_value => sqlcode,
1124 p_token2 => OKC_REP_UTIL_PVT.G_SQLERRM_TOKEN,
1125 p_token2_value => sqlerrm);
1126 RETURN l_results ;
1127 END draft_attachment_exists;
1128
1129
1130
1131 -- API name : is_contract_status_draft.
1132 -- Type : Private.
1133 -- Function : This function returns Y if the business document status is
1134 -- draft.
1135 -- Pre-reqs : None.
1136 -- Parameters :
1137 -- IN : p_document_type IN VARCHAR2 Required
1138 -- Type of the document that is being checked
1139 -- : p_document_id IN VARCHAR2 Required
1140 -- Id of the document that is being checked
1141 -- OUT : Returns Y if the business document status is
1142 -- draft, else returns N
1143 FUNCTION is_contract_status_draft(
1144 p_document_type IN VARCHAR2,
1145 p_document_id IN NUMBER
1146 ) RETURN VARCHAR2
1147
1148 IS
1149 l_api_name VARCHAR2(30);
1150 l_has_access VARCHAR2(1);
1151 l_return_status VARCHAR2(1);
1152 l_msg_count NUMBER;
1153 l_msg_data VARCHAR2(2000);
1154 l_contract_status_code VARCHAR2(30);
1155 l_results VARCHAR2(1);
1156
1157 CURSOR rep_status_csr IS
1158 SELECT contract_status_code
1159 FROM okc_rep_contracts_all
1160 WHERE contract_id = p_document_id;
1161
1162 CURSOR po_status_csr IS
1163 SELECT NVL(authorization_status, 'INCOMPLETE') AS contract_status_code
1164 FROM po_headers_all
1165 WHERE po_header_id = p_document_id;
1166
1167 CURSOR neg_status_csr IS
1168 SELECT auction_status AS contract_status_code
1169 FROM pon_auction_headers_all
1170 WHERE auction_header_id = p_document_id;
1171
1172 CURSOR quote_status_csr IS
1173 SELECT sb.status_code AS contract_status_code
1174 FROM
1175 aso_quote_headers_all h
1176 ,aso_quote_statuses_b sb
1177 WHERE h.quote_header_id = p_document_id
1178 AND h.quote_status_id = sb.quote_status_id;
1179
1180 CURSOR so_status_csr IS
1181 SELECT flow_status_code AS contract_status_code
1182 FROM oe_order_headers_all
1183 WHERE header_id = p_document_id;
1184
1185 CURSOR bsa_status_csr IS
1186 SELECT flow_status_code AS contract_status_code
1187 FROM oe_blanket_headers_all
1188 WHERE header_id = p_document_id;
1189
1190 BEGIN
1191
1192 l_api_name := 'is_contract_status_draft';
1193 l_has_access := 'N';
1194 l_results := 'N';
1195
1196 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1197 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1198 'Entered Function OKC_REP_SEARCH_UTIL_PVT.is_contract_status_draft');
1199 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1200 'Document Id is: ' || p_document_id);
1201 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1202 'Document Type is: ' || p_document_type);
1203 END IF;
1204 IF (SubStr(p_document_type,1,3) = 'REP') THEN
1205 OPEN rep_status_csr;
1206 FETCH rep_status_csr INTO l_contract_status_code;
1207 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1208 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1209 'Repository doc type, l_contract_status_code: ' || l_contract_status_code);
1210 END IF;
1211 IF ( (l_contract_status_code = 'DRAFT') OR
1212 (l_contract_status_code = 'REJECTED') OR
1213 (l_contract_status_code = 'PENDING_APPROVAL')) THEN
1214 l_results := 'Y';
1215 ELSE
1216 l_results := 'N';
1217 END IF;
1218 CLOSE rep_status_csr;
1219
1220 ELSIF ((p_document_type='PA_BLANKET') OR (p_document_type='PA_CONTRACT')
1221 OR (p_document_type='PO_STANDARD')) THEN
1222 OPEN po_status_csr;
1223 FETCH po_status_csr INTO l_contract_status_code;
1224 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1225 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1226 'PO doc type, l_contract_status_code: ' || l_contract_status_code);
1227 END IF;
1228 IF ( (l_contract_status_code = 'IN PROCESS') OR
1229 (l_contract_status_code = 'INCOMPLETE') OR
1230 (l_contract_status_code = 'PRE-APPROVED') OR
1231 (l_contract_status_code = 'REJECTED') OR
1232 (l_contract_status_code = 'REQUIRES REAPPROVAL')) THEN
1233 l_results := 'Y';
1234 ELSE
1235 l_results := 'N';
1236 END IF;
1237 CLOSE po_status_csr;
1238
1239 ELSIF ((p_document_type='RFI') OR (p_document_type='RFQ')
1240 OR (p_document_type='AUCTION')) THEN
1241 OPEN neg_status_csr;
1242 FETCH neg_status_csr INTO l_contract_status_code;
1243 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1244 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1245 'Negotiation doc type, l_contract_status_code: ' || l_contract_status_code);
1246 END IF;
1247 IF ( (l_contract_status_code = 'ACTIVE') OR
1248 (l_contract_status_code = 'AWARD_APPROVAL_INPROCESS') OR
1249 (l_contract_status_code = 'AWARD_REJECTED') OR
1250 (l_contract_status_code = 'AWARD_IN_PROG') OR
1251 (l_contract_status_code = 'CLOSED') OR
1252 (l_contract_status_code = 'DRAFT') OR
1253 (l_contract_status_code = 'OPEN_FOR_BIDDING') OR
1254 (l_contract_status_code = 'PAUSED') OR
1255 (l_contract_status_code = 'PREVIEW') OR
1256 (l_contract_status_code = 'SUBMITTED')) THEN
1257 l_results := 'Y';
1258 ELSE
1259 l_results := 'N';
1260 END IF;
1261 CLOSE neg_status_csr;
1262 ELSIF (p_document_type='QUOTE') THEN
1263 OPEN quote_status_csr;
1264 FETCH quote_status_csr INTO l_contract_status_code;
1265 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1266 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1267 'Sales Quote doc type, l_contract_status_code: ' || l_contract_status_code);
1268 END IF;
1269 IF ( (l_contract_status_code = 'APPROVAL CANCELED') OR
1270 (l_contract_status_code = 'APPROVAL PENDING') OR
1271 (l_contract_status_code = 'APPROVAL REJECTED') OR
1272 (l_contract_status_code = 'DRAFT') OR
1273 (l_contract_status_code = 'ENTERED') OR
1274 (l_contract_status_code = 'INACTIVE') OR
1275 (l_contract_status_code = 'QUOTE GENERATED BY UW') OR
1276 (l_contract_status_code = 'REVIEWED') OR
1277 (l_contract_status_code = 'SUBMIT TO UNDERWRITING')) THEN
1278 l_results := 'Y';
1279 ELSE
1280 l_results := 'N';
1281 END IF;
1282 CLOSE quote_status_csr;
1283 ELSIF (p_document_type='B') THEN
1284 OPEN bsa_status_csr;
1285 FETCH bsa_status_csr INTO l_contract_status_code;
1286 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1287 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1288 'Sales Agreement doc type, l_contract_status_code: ' || l_contract_status_code);
1289 END IF;
1290
1291 IF ( (l_contract_status_code= 'DRAFT') OR
1292 (l_contract_status_code= 'DRAFT_CUSTOMER_REJECTED') OR
1293 (l_contract_status_code= 'DRAFT_INTERNAL_REJECTED') OR
1294 (l_contract_status_code= 'DRAFT_SUBMITTED') OR
1295 (l_contract_status_code= 'ENTERED') OR
1296 (l_contract_status_code = 'INTERNAL_REJECTED') OR
1297 (l_contract_status_code = 'PENDING_INTERNAL_APPROVAL')) THEN
1298 l_results := 'Y';
1299 ELSE
1300 l_results := 'N';
1301 END IF;
1302 CLOSE bsa_status_csr;
1303 ELSIF (p_document_type='O') THEN
1304 OPEN so_status_csr;
1305 FETCH so_status_csr INTO l_contract_status_code;
1306 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1307 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1308 'Sale Order doc type, l_contract_status_code: ' || l_contract_status_code);
1309 END IF;
1310 IF ( (l_contract_status_code = 'DRAFT') OR
1311 (l_contract_status_code = 'DRAFT_CUSTOMER_REJECTED') OR
1312 (l_contract_status_code = 'DRAFT_INTERNAL_REJECTED') OR
1313 (l_contract_status_code = 'DRAFT_SUBMITTED') OR
1314 (l_contract_status_code = 'ENTERED') OR
1315 (l_contract_status_code = 'PENDING_INTERNAL_APPROVAL')) THEN
1316 l_results := 'Y';
1317 ELSE
1318 l_results := 'N';
1319 END IF;
1320 CLOSE so_status_csr;
1321 END IF; -- p_document_type like '%tmp_txt_search_draft'
1322
1323
1324 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1325 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1326 'l_results: ' || l_results);
1327 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1328 'Exiting is_contract_status_draft');
1329 END IF;
1330
1331 RETURN l_results;
1332
1333 EXCEPTION
1334 WHEN OTHERS THEN
1335 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1336 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION ,G_MODULE||l_api_name,
1337 'Leaving Function is_contract_status_draft because of EXCEPTION: '||sqlerrm);
1338 END IF;
1339 IF (rep_status_csr%ISOPEN) THEN
1340 CLOSE rep_status_csr ;
1341 END IF;
1342 IF (po_status_csr%ISOPEN) THEN
1343 CLOSE po_status_csr ;
1344 END IF;
1345 IF (neg_status_csr%ISOPEN) THEN
1346 CLOSE neg_status_csr ;
1347 END IF;
1348 IF (quote_status_csr%ISOPEN) THEN
1349 CLOSE quote_status_csr ;
1350 END IF;
1351 IF (so_status_csr%ISOPEN) THEN
1352 CLOSE so_status_csr ;
1353 END IF;
1354 IF (bsa_status_csr%ISOPEN) THEN
1355 CLOSE bsa_status_csr ;
1356 END IF;
1357 Okc_Api.Set_Message(p_app_name => 'OKC',
1358 p_msg_name => OKC_REP_UTIL_PVT.G_UNEXPECTED_ERROR,
1359 p_token1 => OKC_REP_UTIL_PVT.G_SQLCODE_TOKEN,
1360 p_token1_value => sqlcode,
1361 p_token2 => OKC_REP_UTIL_PVT.G_SQLERRM_TOKEN,
1362 p_token2_value => sqlerrm);
1363 RETURN l_results ;
1364 END is_contract_status_draft;
1365
1366 -- API name : get_neg_parties
1367 -- Type : Private.
1368 -- Function : This function fetches the party names for a given
1369 -- negotiation contract. It is called in the SQL statements
1370 -- of NegSearchExpVO.xml in the
1371 -- oracle.apps.okc.repository.search.server package.
1372 -- Pre-reqs : None.
1373 -- Parameters :
1374 -- IN : p_auction_header_id IN NUMBER
1375 -- The auction header ID of the contract document currently
1376 -- being crawled.
1377 -- OUT : x_parties OUT VARCHAR2
1378 -- A string of party names that define
1379 -- the parties of this negotiation contract. The party
1380 -- names are separated by spaces.
1381 -- Example: Vision, Inc. AT&T Informologics
1382 -- Note :
1383 FUNCTION get_neg_parties(
1384 p_auction_header_id IN NUMBER
1385 ) RETURN VARCHAR2
1386 IS
1387 l_api_name VARCHAR2(30);
1388 l_party_name VARCHAR2(500);
1389 l_parties VARCHAR2(2000);
1390
1391 -- Get the parties associated with this contract
1392 CURSOR party_csr IS
1393 SELECT v.vendor_name
1394 FROM pon_bid_headers b
1395 ,po_vendors v
1396 WHERE b.auction_header_id = p_auction_header_id
1397 AND b.bid_status IN ('ACTIVE','DISQUALIFIED')
1398 AND b.vendor_id = v.vendor_id;
1399
1400 party_rec party_csr%ROWTYPE;
1401
1402 BEGIN
1403
1404 l_api_name := 'get_neg_parties';
1405
1406 -- Do logging.
1407 IF ( FND_LOG.LEVEL_PROCEDURE>= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1408 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,
1409 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
1410 '200: Entered OKC_REP_SEARCH_UTIL_PVT.get_neg_parties');
1411 END IF;
1412 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1413 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,
1414 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
1415 'Input parameter is: p_auction_header_id = ' || p_auction_header_id||
1416 '.');
1417 END IF;
1418 FOR party_rec IN party_csr
1419 LOOP
1420 l_parties := l_parties || party_rec.vendor_name || ' ';
1421 END LOOP;
1422
1423 -- Do logging.
1424 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1425 THEN
1426 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,
1427 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
1428 'OKC_REP_UTIL_PVT.get_neg_parties returns l_parties as : '
1429 || l_parties);
1430 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,
1431 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
1432 'Leaving get_neg_parties');
1433 END IF;
1434
1435 RETURN l_parties;
1436
1437 EXCEPTION
1438 WHEN OTHERS
1439 THEN
1440 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1441 THEN
1442 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION ,
1443 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
1444 'Leaving PROCEDURE get_neg_parties
1445 because of EXCEPTION: '||sqlerrm);
1446 END IF;
1447 Okc_Api.Set_Message(p_app_name => 'OKC',
1448 p_msg_name => OKC_REP_UTIL_PVT.G_UNEXPECTED_ERROR,
1449 p_token1 => OKC_REP_UTIL_PVT.G_SQLCODE_TOKEN,
1450 p_token1_value => sqlcode,
1451 p_token2 => OKC_REP_UTIL_PVT.G_SQLERRM_TOKEN,
1452 p_token2_value => sqlerrm);
1453 END get_neg_parties;
1454
1455
1456 END OKC_REP_SEARCH_UTIL_PVT;
[Home] [Help]