1 PACKAGE BODY OKC_REP_SEARCH_UTIL_PVT AS
2 /* $Header: OKCVREPSRCHUTILB.pls 120.4 2012/01/06 06:27:14 vechittu 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 -- Bug 12660114
280 -- get the current user resource Number from JTF objects and
281 -- add the u<user_rec.resource_id> to the acl keys
282
283 x_keys := x_keys || 'u' || to_char(l_resource_id) || ' ';
284
285 FOR group_rec IN group_csr(l_resource_id)
286 LOOP
287 x_keys := x_keys || 'g' || to_char(group_rec.group_id) || ' ';
288 END LOOP;
289 END LOOP;
290
291 END IF;
292
293 -- Do logging.
294 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
295 THEN
296 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,
297 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
298 'OKC_REP_UTIL_PVT.get_current_user_acl_keys
299 returns x_keys as : '
300 || x_keys);
301 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,
302 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
303 'Leaving get_current_user_acl_keys');
304 END IF;
305
306 EXCEPTION
307 WHEN OTHERS
308 THEN
309 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
310 THEN
311 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION ,
312 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'
313 ||l_api_name,
314 'Leaving PROCEDURE get_current_user_acl_keys
315 because of EXCEPTION: '
316 ||sqlerrm);
317 END IF;
318 Okc_Api.Set_Message(p_app_name => 'OKC',
319 p_msg_name => OKC_REP_UTIL_PVT.G_UNEXPECTED_ERROR,
320 p_token1 => OKC_REP_UTIL_PVT.G_SQLCODE_TOKEN,
321 p_token1_value => sqlcode,
322 p_token2 => OKC_REP_UTIL_PVT.G_SQLERRM_TOKEN,
323 p_token2_value => sqlerrm);
324 END;
325
326
327 -- API name : get_current_user_moac_keys
328 -- Type : Private.
329 -- Function : Called in the BusDocSearchPlugIn class by the AppSearch
330 -- Framework at query-time to get the list of operating
331 -- units that the current user may access. This list of
332 -- operating unit IDs is then used to check each search hit
333 -- and verify that the user is permitted to view that
334 -- specific hit. If a user is not permitted to view a given
335 -- hit, then that hit is removed from the query result set.
336 -- Pre-reqs : None.
337 -- Parameters :
338 -- IN : None.
339 -- OUT : x_keys OUT VARCHAR2
340 -- A space-delimited string of the IDs of the operating
341 -- units to which the current user has access.
342 -- Example: 134 325 4384
343 -- Note :
344 PROCEDURE get_current_user_moac_keys
345 ( x_keys OUT NOCOPY VARCHAR2)
346 IS
347 l_api_name VARCHAR2(30);
348 l_ou_tab MO_GLOBAL.OrgIdTab;
349
350 l_user_id NUMBER;
351 l_resp_id NUMBER;
352 l_resp_appl_id NUMBER;
353 l_security_grp_id NUMBER;
354
355 CURSOR get_moac_org_id IS
356 SELECT ou.organization_id org_id
357 FROM hr_operating_units ou
358 WHERE mo_global.check_access(ou.organization_id) = 'Y';
359
360
361 BEGIN
362
363 l_api_name := 'get_current_user_moac_keys';
364
365 -- Do logging.
366 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
367 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,
368 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
369 '200: Entered OKC_REP_SEARCH_UTIL_PVT.get_current_user_moac_keys');
370 END IF;
371
372 l_user_id := FND_GLOBAL.user_id;
373 l_resp_id := FND_GLOBAL.RESP_ID;
374 l_security_grp_id := FND_GLOBAL.SECURITY_GROUP_ID;
375 l_resp_appl_id := 510;
376
377 fnd_global.apps_initialize(l_user_id,l_resp_id, l_resp_appl_id);
378 MO_GLOBAL.init('OKC');
379
380
381
382 -- 1. Call MO_GLOBAL.get_ou_tab to get the current user's accessible
383 -- operating units in a temporary table.
384 --
385 -- (Uncomment the following for testing purposes)
386 -- MO_GLOBAL.init('OKC');
387 -- MO_GLOBAL.set_policy_context('M',204);
388 --l_ou_tab := MO_GLOBAL.get_ou_tab;//Commented this.Using the cursor instead
389
390 OPEN get_moac_org_id;
391 FETCH get_moac_org_id BULK COLLECT INTO l_ou_tab;
392 CLOSE get_moac_org_id;
393
394
395 -- 2. Create a space-separated string of operating unit IDs using the
396 -- temp table from step 1.
397 IF (l_ou_tab.COUNT > 0)
398 THEN
399 FOR i IN l_ou_tab.FIRST .. l_ou_tab.LAST
400 LOOP
401 x_keys := x_keys || ' ' || l_ou_tab(i);
402 END LOOP;
403 END IF;
404
405 -- Do logging.
406 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
407 THEN
408 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,
409 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
410 'OKC_REP_UTIL_PVT.get_current_user_moac_keys
411 returns x_keys as : '
412 || x_keys);
413 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,
414 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
415 'Leaving get_current_user_moac_keys');
416 END IF;
417
418 EXCEPTION
419 WHEN OTHERS
420 THEN
421 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
422 THEN
423 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION ,
424 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
425 'Leaving PROCEDURE get_current_user_moac_keys because of
426 EXCEPTION: '||sqlerrm);
427 END IF;
428 Okc_Api.Set_Message(p_app_name => 'OKC',
429 p_msg_name => OKC_REP_UTIL_PVT.G_UNEXPECTED_ERROR,
430 p_token1 =>OKC_REP_UTIL_PVT. G_SQLCODE_TOKEN,
431 p_token1_value => sqlcode,
432 p_token2 => OKC_REP_UTIL_PVT.G_SQLERRM_TOKEN,
433 p_token2_value => sqlerrm);
434 END;
435
436
437 -- API name : get_intent_profile_keys
438 -- Type : Private.
439 -- Function : Called in the BusDocSearchPlugIn class by the AppSearch
440 -- Framework at query-time to get the list of intent codes
441 -- that represent the types of contracts (e.g. Buy, Sell)
442 -- that the current user may access. This list of
443 -- intent codes is then used to check each search hit
444 -- and verify that the user is permitted to view that
445 -- specific hit. If a user is not permitted to view a given
446 -- hit, then that hit is removed from the query result set.
447 -- Pre-reqs : None.
448 -- Parameters :
449 -- IN : None.
450 -- OUT : x_keys OUT VARCHAR2
451 -- A string of single-character intent codes that
452 -- represent the types of contracts (e.g. Buy, Sell) to
453 -- which the current user has access. This string is
454 -- parse into another string containing the single
455 -- characters separated by spaces in the BusDocSearchPlugIn
456 -- class (e.g. "SA" -> "S A").
457 -- Example: BA
458 -- Example: BSOA
459 -- Note :
460 PROCEDURE get_intent_profile_keys
461 ( x_keys OUT NOCOPY VARCHAR2)
462 IS
463 l_api_name VARCHAR2(30);
464
465 BEGIN
466
467 l_api_name := 'get_intent_profile_keys';
468
469 -- Do logging.
470 IF ( FND_LOG.LEVEL_PROCEDURE>= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
471 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,
472 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
473 '200: Entered OKC_REP_SEARCH_UTIL_PVT.get_intent_profile_keys');
474 END IF;
475
476 -- 1. Call FND_PROFILE.value('OKC_REP_INTENTS') to get the intent
477 -- codes from the FND profile.
478 x_keys := FND_PROFILE.value('OKC_REP_INTENTS');
479
480 x_keys := x_keys;
481
482 -- 2. Create a space-separated string of intent codes from step 1.
483 -- Will do this in the Java level.
484
485 -- Do logging.
486 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
487 THEN
488 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,
489 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
490 'OKC_REP_UTIL_PVT.get_intent_profile_keys
491 returns x_keys as : '
492 || x_keys);
493 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,
494 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
495 'Leaving get_intent_profile_keys');
496 END IF;
497
498 EXCEPTION
499 WHEN OTHERS
500 THEN
501 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
502 THEN
503 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION,
504 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
505 'Leaving PROCEDURE get_current_intent_profile_keys
506 because of EXCEPTION: '||sqlerrm);
507 END IF;
508 Okc_Api.Set_Message(p_app_name => 'OKC',
509 p_msg_name => OKC_REP_UTIL_PVT.G_UNEXPECTED_ERROR,
510 p_token1 => OKC_REP_UTIL_PVT.G_SQLCODE_TOKEN,
511 p_token1_value => sqlcode,
512 p_token2 => OKC_REP_UTIL_PVT.G_SQLERRM_TOKEN,
513 p_token2_value => sqlerrm);
514
515 END;
516
517 -- API name : get_current_user_quote_access
518 -- Type : Private.
519 -- Function : Called in the BusDocSearchPlugIn class in the
520 -- queryPostProcess() method at query-time to discover the
521 -- current user's level of access for a given Sales Quote
522 -- document. The possible return values are UPDATE, READ,
523 -- and NONE. If a user is not permitted to view a given
524 -- quote (meaning that this procedure returns NONE), then
525 -- that quote document is removed from the query result set.
526 -- Pre-reqs : None.
527 -- Parameters :
528 -- IN : p_quote_number IN NUMBER
529 -- The ID number of the sales quote document
530 -- currently being processed.
531 -- OUT : x_access OUT VARCHAR2
532 -- A string that represents the current user's level of
533 -- access for the given Sales Quote document.
534 -- Note :
535 PROCEDURE get_current_user_quote_access
536 ( p_quote_number IN NUMBER,
537 x_access OUT NOCOPY VARCHAR2)
538 IS
539 l_api_name VARCHAR2(30);
540 l_resource_id VARCHAR2(30);
541
542 BEGIN
543
544 l_api_name := 'get_current_user_quote_access';
545
546 -- Do logging.
547 IF ( FND_LOG.LEVEL_PROCEDURE>= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
548 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,
549 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
550 '200: Entered OKC_REP_SEARCH_UTIL_PVT.get_current_user_quote_access');
551 END IF;
552 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
553 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,
554 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
555 'Input parameter is: p_quote_number = ' || p_quote_number||
556 '.');
557 END IF;
558
559 -- 1. Get the resource_id associated with the current user.
560 SELECT resource_id
561 INTO l_resource_id
562 FROM jtf_rs_resource_extns
563 WHERE user_id = FND_GLOBAL.user_id;
564
565 -- 2. If the resource_id is null, return 'NONE'.
566 IF (l_resource_id = NULL)
567 THEN
568 x_access := 'NONE';
569 ELSE
570 -- Else, use the ASO_SECURITY_INT API get_quote_access to
571 -- retrieve the access level. Possible values are
572 -- UPDATE, READ, and NONE. If null is returned, then
573 -- set x_access to NONE.
574 x_access := aso_security_int.get_quote_access(
575 l_resource_id, p_quote_number);
576 IF (x_access = NULL)
577 THEN
578 x_access := 'NONE';
579 END IF;
580 END IF;
581
582 -- Do logging.
583 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
584 THEN
585 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,
586 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
587 'OKC_REP_UTIL_PVT.get_current_user_quote_access
588 returns x_access as : '
589 || x_access);
590 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,
591 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
592 'Leaving get_current_user_quote_access');
593 END IF;
594
595 EXCEPTION
596 WHEN OTHERS
597 THEN
598 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
599 THEN
600 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION ,
601 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
602 'Leaving PROCEDURE get_current_user_quote_access
603 because of EXCEPTION: '||sqlerrm);
604 END IF;
605 Okc_Api.Set_Message(p_app_name => 'OKC',
606 p_msg_name => OKC_REP_UTIL_PVT.G_UNEXPECTED_ERROR,
607 p_token1 => OKC_REP_UTIL_PVT.G_SQLCODE_TOKEN,
608 p_token1_value => sqlcode,
609 p_token2 => OKC_REP_UTIL_PVT.G_SQLERRM_TOKEN,
610 p_token2_value => sqlerrm);
611
612 END;
613
614 -- API name : get_local_language_attributes
615 -- Type : Private.
616 -- Function : Called in the BusDocSearchPlugIn class in the
617 -- queryPostProcess() method at query-time to fetch
618 -- three language-dependent attributes: document type,
619 -- intent, and status. The local language values of
620 -- these three attributes are returned in a comma-
621 -- delimited string in the order docType, intent, status.
622 -- Parameters :
623 -- IN : p_doc_type_code IN NUMBER
624 -- The document type code of the contract document
625 -- currently being processed by queryPostProcess().
626 -- IN : p_intent_code IN NUMBER
627 -- The intent code of the contract document currently
628 -- being processed by queryPostProcess().
629 -- IN : p_status_code IN NUMBER
630 -- The status code of the contract document currently
631 -- being processed by queryPostProcess().
632 -- OUT : x_attrs OUT VARCHAR2
633 -- A string containing three sub-strings separated by
634 -- commas. These three sub-strings represent the
635 -- local-language value of document type, intent,
636 -- and status.
637 -- Note :
638 PROCEDURE get_local_language_attributes
639 ( p_doc_type_code IN VARCHAR,
640 p_intent_code IN VARCHAR,
641 p_status_code IN VARCHAR,
642 x_attrs OUT NOCOPY VARCHAR2)
643 IS
644 l_api_name VARCHAR2(30);
645 l_doc_type VARCHAR2(500);
646 l_intent VARCHAR2(500);
647 l_status VARCHAR2(500);
648 l_lookup_type VARCHAR2(50);
649
650 BEGIN
651
652 l_api_name := 'get_local_language_attributes';
653
654 -- Do logging.
655 IF ( FND_LOG.LEVEL_PROCEDURE>= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
656 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,
657 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
658 '200: Entered OKC_REP_SEARCH_UTIL_PVT.get_local_language_attributes');
659 END IF;
660 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
661 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,
662 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
663 'Input parameters are: p_doc_type_code = ' || p_doc_type_code ||
664 ', p_intent_code = ' || p_intent_code ||
665 ', p_status_code = ' || p_status_code ||
666 '.');
667 END IF;
668
669 -- 1. Get the document type. If the type is null (meaning that
670 -- there is no entry in okc_bus_doc_types_vl for that doc type code),
671 -- set the document type to be the document type code. We nest the
672 -- query in its own block in order to catch No Data Found
673 -- exceptions.
674 SELECT name
675 INTO l_doc_type
676 FROM okc_bus_doc_types_vl
677 WHERE document_type = p_doc_type_code;
678
679
680 -- Append the document type to the attribute list.
681 x_attrs := l_doc_type || ',';
682
683 -- 2. Get the document intent. If the intent is null (meaning that
684 -- there is no entry in fnd_lookups for that intent code and lookup
685 -- type), set the intent to be the intent code. We nest the
686 -- query in its own block in order to catch No Data Found
687 -- exceptions.
688 SELECT meaning
689 INTO l_intent
690 FROM fnd_lookups
691 WHERE lookup_type = 'OKC_REP_CONTRACT_INTENTS'
692 AND lookup_code = p_intent_code;
693
694
695 -- Append the intent to the attribute list.
696 x_attrs := x_attrs || l_intent || ',';
697
698 -- 3. Get the document status and append it to x_attrs.
699 --
700 -- If the document type code is QUOTE, the status needs to be fetched
701 -- with this SQL statement.
702 IF (p_doc_type_code = 'QUOTE')
703 THEN
704 SELECT aqsvl.meaning
705 INTO l_status
706 FROM aso_quote_statuses_b aqsb,
707 aso_quote_statuses_vl aqsvl
708 WHERE aqsb.status_code = p_status_code
709 AND aqsb.quote_status_id = aqsvl.quote_status_id;
710
711 -- If the document type code is RFQ or RFI or AUCTION,
712 -- the status needs to be fetched with
713 -- this SQL statement.
714 ELSE
715 IF ((p_doc_type_code = 'RFQ') OR
716 (p_doc_type_code = 'RFI') OR
717 (p_doc_type_code = 'AUCTION'))
718 THEN
719
720 -- AUCTION_CLOSED status is a derived status
721 -- which is dependent on the user logged in. So
722 -- displaying a message.
723
724 IF( p_status_code = 'AUCTION_CLOSED')
725 THEN
726 SELECT MESSAGE_TEXT
727 INTO l_status
728 FROM FND_NEW_MESSAGES
729 WHERE language_code = USERENV('LANG')
730 AND application_id = 510
731 AND message_name = 'OKC_SES_AUC_STATUS_EXP';
732
733 ELSE
734
735 SELECT meaning
736 INTO l_status
737 FROM fnd_lookup_values
738 WHERE language = USERENV('LANG')
739 AND lookup_type = 'PON_AUCTION_STATUS'
740 AND lookup_code = p_status_code;
741 END IF;
742
743 -- Else, we need to set the lookup type and fetch the status with
744 -- this SQL statement.
745 ELSE
746 -- If the document type code is O or B, then the lookup type
747 -- is FLOW_STATUS
748 IF ((p_doc_type_code = 'B') OR
749 (p_doc_type_code = 'O'))
750 THEN
751 l_lookup_type := 'FLOW_STATUS';
752
753 -- Get the status using the lookup type.
754 SELECT meaning
755 INTO l_status
756 FROM oe_lookups
757 WHERE lookup_type = l_lookup_type
758 AND lookup_code = p_status_code;
759 ELSE
760 -- If the document type code is PA_BLANKET or PA_CONTRACT
761 -- or PO_STANDARD, then the lookup type is
762 -- AUTHORIZATION_STATUS
763 IF ((p_doc_type_code = 'PA_BLANKET') OR
764 (p_doc_type_code = 'PA_CONTRACT') OR
765 (p_doc_type_code = 'PO_STANDARD'))
766 THEN
767 l_lookup_type := 'AUTHORIZATION STATUS';
768 -- Else, the document type code is REP_% and the lookup
769 -- type is OKC_REP_CONTRACT_STATUSES.
770 ELSE
771 l_lookup_type := 'OKC_REP_CONTRACT_STATUSES';
772 END IF;
773
774 -- Get the status using the lookup type.
775 SELECT meaning
776 INTO l_status
777 FROM fnd_lookup_values
778 WHERE lookup_type = l_lookup_type
779 AND language = USERENV('LANG')
780 AND lookup_code = p_status_code;
781 END IF;
782 END IF;
783 END IF;
784
785 -- Append the status to the attribute list.
786 x_attrs := x_attrs || l_status;
787
788 -- Do logging.
789 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
790 THEN
791 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,
792 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
793 'OKC_REP_UTIL_PVT.get_local_language_attributes
794 returns x_attrs as : '
795 || x_attrs);
796 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,
797 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
798 'Leaving get_local_language_attributes');
799 END IF;
800
801 EXCEPTION
802 WHEN OTHERS
803 THEN
804 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
805 THEN
806 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED,
807 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
808 'Leaving PROCEDURE get_local_language_attributes
809 because of EXCEPTION: '||sqlerrm);
810 END IF;
811 Okc_Api.Set_Message(p_app_name => 'OKC',
812 p_msg_name => OKC_REP_UTIL_PVT.G_UNEXPECTED_ERROR,
813 p_token1 => OKC_REP_UTIL_PVT.G_SQLCODE_TOKEN,
814 p_token1_value => sqlcode,
815 p_token2 => OKC_REP_UTIL_PVT.G_SQLERRM_TOKEN,
816 p_token2_value => sqlerrm);
817 END;
818
819
820 -- API name : get_rep_parties
821 -- Type : Private.
822 -- Function : This function fetches the party names for a given
823 -- repository contract. It is called in the SQL statements
824 -- of RepHeaderSearchExpVO and RepArchiveSearchExpVO in the
825 -- oracle.apps.okc.repository.search.server package.
826 -- Pre-reqs : None.
827 -- Parameters :
828 -- IN : p_contract_id IN NUMBER
829 -- The contract ID of the contract document currently
830 -- being crawled.
831 -- OUT : x_parties OUT VARCHAR2
832 -- A string of party names that define
833 -- the parties of this repository contract. The party
834 -- names are separated by spaces.
835 -- Example: Vision, Inc. AT&T Informologics
836 -- Note :
837 FUNCTION get_rep_parties
838 ( p_contract_id IN NUMBER)
839 RETURN VARCHAR2
840 IS
841 l_api_name VARCHAR2(30);
842 l_party_name VARCHAR2(500);
843 l_parties VARCHAR2(2000);
844
845 -- Get the party role code associated with the given contract.
846 CURSOR party_csr IS
847 SELECT party_id, party_role_code
848 FROM okc_rep_contract_parties
849 WHERE contract_id = p_contract_id;
850
851 party_rec party_csr%ROWTYPE;
852
853 BEGIN
854
855 l_api_name := 'get_rep_parties';
856
857 -- Do logging.
858 IF ( FND_LOG.LEVEL_PROCEDURE>= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
859 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,
860 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
861 '200: Entered OKC_REP_SEARCH_UTIL_PVT.get_rep_parties');
862 END IF;
863 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
864 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,
865 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
866 'Input parameter is: p_contract_id = ' || p_contract_id||
867 '.');
868 END IF;
869
870 -- 1. Get party_role_code from okc_rep_contract_parties
871 -- for the given contract_id.
872 -- 2. For each record from step 1:
873 FOR party_rec IN party_csr
874 LOOP
875 -- a. If the party_role_code==INTERNAL_ORG, get the party name
876 -- from hr_all_organizational_units using
877 -- party_id=organization_id join
878 IF (party_rec.party_role_code = 'INTERNAL_ORG')
879 THEN
880 SELECT name
881 INTO l_party_name
882 FROM hr_all_organization_units
883 WHERE organization_id = party_rec.party_id;
884
885 -- b. Else If the party_role_code==SUPPLIER_ORG
886 -- get the party name from po_vendors using party_id join.
887 ELSIF (party_rec.party_role_code = 'SUPPLIER_ORG')
888 THEN
889 SELECT vendor_name
890 INTO l_party_name
891 FROM po_vendors
892 WHERE vendor_id = party_rec.party_id;
893 -- c. Else get the party name from hz_parties using party_id join.
894 ELSE
895 SELECT party_name
896 INTO l_party_name
897 FROM hz_parties
898 WHERE party_id = party_rec.party_id;
899 END IF;
900
901 --c. Append party name from step a or b to x_parties.
902 l_parties := l_parties || l_party_name || ' ';
903
904 END LOOP;
905
906 -- Do logging.
907 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
908 THEN
909 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,
910 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
911 'OKC_REP_UTIL_PVT.get_rep_parties returns l_parties as : '
912 || l_parties);
913 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,
914 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
915 'Leaving get_rep_parties');
916 END IF;
917
918 RETURN l_parties;
919
920 EXCEPTION
921 WHEN OTHERS
922 THEN
923 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
924 THEN
925 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION ,
926 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
927 'Leaving PROCEDURE get_rep_parties
928 because of EXCEPTION: '||sqlerrm);
929 END IF;
930 Okc_Api.Set_Message(p_app_name => 'OKC',
931 p_msg_name => OKC_REP_UTIL_PVT.G_UNEXPECTED_ERROR,
932 p_token1 => OKC_REP_UTIL_PVT.G_SQLCODE_TOKEN,
933 p_token1_value => sqlcode,
934 p_token2 => OKC_REP_UTIL_PVT.G_SQLERRM_TOKEN,
935 p_token2_value => sqlerrm);
936 END;
937
938
939 -- API name : get_terms_last_update_date.
940 -- Type : Private.
941 -- Function : This function returns the last_update_date value of the the business
942 -- document's contract terms.
943 -- Pre-reqs : None.
944 -- Parameters :
945 -- IN : p_document_type IN VARCHAR2 Required
946 -- Type of the document that is being checked
947 -- : p_document_id IN VARCHAR2 Required
948 -- Id of the document that is being checked
949 -- OUT : Returns the last_update_date value of the the business
950 -- document's contract terms.
951 FUNCTION get_terms_last_update_date(
952 p_document_type IN VARCHAR2,
953 p_document_id IN NUMBER
954 ) RETURN DATE
955 IS
956 l_api_name VARCHAR2(30);
957 l_has_access VARCHAR2(1);
958 l_return_status VARCHAR2(1);
959 l_msg_count NUMBER;
960 l_msg_data VARCHAR2(2000);
961 l_deliverable_changed_date DATE;
962 l_terms_changed_date DATE;
963
964 BEGIN
965
966 l_api_name := 'get_terms_last_update_date';
967 l_has_access := 'N';
968
969 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
970 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
971 'Entered Function OKC_REP_SEARCH_UTIL_PVT.get_terms_last_update_date');
972 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
973 'Document Id is: ' || p_document_id);
974 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
975 'Document Type is: ' || p_document_type);
976 END IF;
977 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
978 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
979 'Calling OKC_TERMS_UTIL_GRP.Get_Last_Update_Date');
980 END IF;
981 --- Call OKC_TERMS_UTIL_GRP.Get_Last_Update_Date procedure.
982
983 OKC_TERMS_UTIL_GRP.Get_Last_Update_Date(
984 p_api_version => 1.0,
985 p_init_msg_list => FND_API.G_FALSE,
986 x_msg_data => l_msg_data,
987 x_msg_count => l_msg_count,
988 x_return_status => l_return_status,
989 p_doc_type => p_document_type,
990 p_doc_id => p_document_id,
991 x_deliverable_changed_date => l_deliverable_changed_date,
992 x_terms_changed_date => l_terms_changed_date
993 );
994 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
995 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
996 'OKC_TERMS_UTIL_GRP.Get_Last_Update_Date return status is: '
997 || l_return_status);
998 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
999 'OKC_REP_UTIL_PVT.Get_Last_Update_Date returns x_terms_changed_date as : '
1000 || l_terms_changed_date);
1001 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1002 'Leaving Function get_terms_last_update_date');
1003 END IF;
1004 RETURN l_terms_changed_date ;
1005
1006 EXCEPTION
1007 WHEN OTHERS THEN
1008 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1009 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION ,G_MODULE||l_api_name,
1010 'Leaving Function get_terms_last_update_date because of EXCEPTION: '||sqlerrm);
1011 END IF;
1012 Okc_Api.Set_Message(p_app_name => 'OKC',
1013 p_msg_name => OKC_REP_UTIL_PVT.G_UNEXPECTED_ERROR,
1014 p_token1 => OKC_REP_UTIL_PVT.G_SQLCODE_TOKEN,
1015 p_token1_value => sqlcode,
1016 p_token2 => OKC_REP_UTIL_PVT.G_SQLERRM_TOKEN,
1017 p_token2_value => sqlerrm);
1018 RETURN l_has_access ;
1019 END get_terms_last_update_date;
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029 -- API name : draft_attachment_exists.
1030 -- Type : Private.
1031 -- Function : This function returns Y if the generated draft attachment exists for
1032 -- the business document passed as input. It will also check that
1033 -- attachment's last_update_date is later than the business document's
1034 -- Term's last_update_date.
1035 -- Pre-reqs : None.
1036 -- Parameters :
1037 -- IN : p_document_type IN VARCHAR2 Required
1038 -- Type of the document that is being checked
1039 -- : p_document_id IN VARCHAR2 Required
1040 -- Id of the document that is being checked
1041 -- OUT : Return Y if the latest generated draft attachment exists for the
1042 -- business document, else returns N
1043 FUNCTION draft_attachment_exists(
1044 p_document_type IN VARCHAR2,
1045 p_document_id IN NUMBER
1046 ) RETURN VARCHAR2
1047
1048 IS
1049 l_api_name VARCHAR2(30);
1050 l_has_access VARCHAR2(1);
1051 l_return_status VARCHAR2(1);
1052 l_msg_count NUMBER;
1053 l_msg_data VARCHAR2(2000);
1054 l_terms_changed_date DATE;
1055 l_attachment_last_update_date DATE;
1056 l_attachment_exists VARCHAR2(1);
1057 l_results VARCHAR2(1);
1058
1059 CURSOR draft_attachment_csr IS
1060 SELECT last_update_date
1061 FROM okc_contract_docs_details_vl
1062 WHERE business_document_type = p_document_type
1063 AND business_document_id = p_document_id
1064 AND file_name like 'Text_Search_Gen_Attach%';
1065
1066 BEGIN
1067
1068 l_api_name := 'draft_attachment_exists';
1069 l_results := 'N';
1070 l_attachment_exists := 'N';
1071
1072 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1073 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1074 'Entered Function OKC_REP_SEARCH_UTIL_PVT.draft_attachment_exists');
1075 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1076 'Document Id is: ' || p_document_id);
1077 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1078 'Document Type is: ' || p_document_type);
1079 END IF;
1080 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1081 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1082 'Calling OKC_REP_SEARCH_UTIL_PVT.get_terms_last_update_date');
1083 END IF;
1084 --- Call OKC_REP_SEARCH_UTIL_PVT.Get_Last_Update_Date procedure.
1085
1086 l_terms_changed_date := OKC_REP_SEARCH_UTIL_PVT.get_terms_last_update_date(
1087 p_document_type => p_document_type,
1088 p_document_id => p_document_id
1089 );
1090
1091 OPEN draft_attachment_csr;
1092 FETCH draft_attachment_csr INTO l_attachment_last_update_date;
1093
1094 IF (draft_attachment_csr%rowcount > 0) THEN
1095 l_attachment_exists := 'Y';
1096 END IF;
1097
1098 CLOSE draft_attachment_csr;
1099
1100 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1101 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1102 'l_terms_changed_date: ' || l_terms_changed_date);
1103 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1104 'l_attachment_last_update_date: ' || l_attachment_last_update_date);
1105 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1106 'l_attachment_exists: ' || l_attachment_exists);
1107 END IF;
1108
1109 IF ((l_attachment_exists = 'N') OR (l_attachment_last_update_date < l_terms_changed_date))THEN
1110 l_results := 'N';
1111 ELSE
1112 l_results := 'Y';
1113 END IF;
1114
1115 RETURN l_results;
1116
1117 EXCEPTION
1118 WHEN OTHERS THEN
1119 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1120 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION ,G_MODULE||l_api_name,
1121 'Leaving Function draft_attachment_exists because of EXCEPTION: '||sqlerrm);
1122 END IF;
1123 IF (draft_attachment_csr%ISOPEN) THEN
1124 CLOSE draft_attachment_csr ;
1125 END IF;
1126 Okc_Api.Set_Message(p_app_name => 'OKC',
1127 p_msg_name => OKC_REP_UTIL_PVT.G_UNEXPECTED_ERROR,
1128 p_token1 => OKC_REP_UTIL_PVT.G_SQLCODE_TOKEN,
1129 p_token1_value => sqlcode,
1130 p_token2 => OKC_REP_UTIL_PVT.G_SQLERRM_TOKEN,
1131 p_token2_value => sqlerrm);
1132 RETURN l_results ;
1133 END draft_attachment_exists;
1134
1135
1136
1137 -- API name : is_contract_status_draft.
1138 -- Type : Private.
1139 -- Function : This function returns Y if the business document status is
1140 -- draft.
1141 -- Pre-reqs : None.
1142 -- Parameters :
1143 -- IN : p_document_type IN VARCHAR2 Required
1144 -- Type of the document that is being checked
1145 -- : p_document_id IN VARCHAR2 Required
1146 -- Id of the document that is being checked
1147 -- OUT : Returns Y if the business document status is
1148 -- draft, else returns N
1149 FUNCTION is_contract_status_draft(
1150 p_document_type IN VARCHAR2,
1151 p_document_id IN NUMBER
1152 ) RETURN VARCHAR2
1153
1154 IS
1155 l_api_name VARCHAR2(30);
1156 l_has_access VARCHAR2(1);
1157 l_return_status VARCHAR2(1);
1158 l_msg_count NUMBER;
1159 l_msg_data VARCHAR2(2000);
1160 l_contract_status_code VARCHAR2(30);
1161 l_results VARCHAR2(1);
1162
1163 CURSOR rep_status_csr IS
1164 SELECT contract_status_code
1165 FROM okc_rep_contracts_all
1166 WHERE contract_id = p_document_id;
1167
1168 CURSOR po_status_csr IS
1169 SELECT NVL(authorization_status, 'INCOMPLETE') AS contract_status_code
1170 FROM po_headers_all
1171 WHERE po_header_id = p_document_id;
1172
1173 CURSOR neg_status_csr IS
1174 SELECT auction_status AS contract_status_code
1175 FROM pon_auction_headers_all
1176 WHERE auction_header_id = p_document_id;
1177
1178 CURSOR quote_status_csr IS
1179 SELECT sb.status_code AS contract_status_code
1180 FROM
1181 aso_quote_headers_all h
1182 ,aso_quote_statuses_b sb
1183 WHERE h.quote_header_id = p_document_id
1184 AND h.quote_status_id = sb.quote_status_id;
1185
1186 CURSOR so_status_csr IS
1187 SELECT flow_status_code AS contract_status_code
1188 FROM oe_order_headers_all
1189 WHERE header_id = p_document_id;
1190
1191 CURSOR bsa_status_csr IS
1192 SELECT flow_status_code AS contract_status_code
1193 FROM oe_blanket_headers_all
1194 WHERE header_id = p_document_id;
1195
1196 BEGIN
1197
1198 l_api_name := 'is_contract_status_draft';
1199 l_has_access := 'N';
1200 l_results := 'N';
1201
1202 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1203 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1204 'Entered Function OKC_REP_SEARCH_UTIL_PVT.is_contract_status_draft');
1205 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1206 'Document Id is: ' || p_document_id);
1207 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1208 'Document Type is: ' || p_document_type);
1209 END IF;
1210 IF (SubStr(p_document_type,1,3) = 'REP') THEN
1211 OPEN rep_status_csr;
1212 FETCH rep_status_csr INTO l_contract_status_code;
1213 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1214 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1215 'Repository doc type, l_contract_status_code: ' || l_contract_status_code);
1216 END IF;
1217 IF ( (l_contract_status_code = 'DRAFT') OR
1218 (l_contract_status_code = 'REJECTED') OR
1219 (l_contract_status_code = 'PENDING_APPROVAL')) THEN
1220 l_results := 'Y';
1221 ELSE
1222 l_results := 'N';
1223 END IF;
1224 CLOSE rep_status_csr;
1225
1226 ELSIF ((p_document_type='PA_BLANKET') OR (p_document_type='PA_CONTRACT')
1227 OR (p_document_type='PO_STANDARD')) THEN
1228 OPEN po_status_csr;
1229 FETCH po_status_csr INTO l_contract_status_code;
1230 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1231 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1232 'PO doc type, l_contract_status_code: ' || l_contract_status_code);
1233 END IF;
1234 IF ( (l_contract_status_code = 'IN PROCESS') OR
1235 (l_contract_status_code = 'INCOMPLETE') OR
1236 (l_contract_status_code = 'PRE-APPROVED') OR
1237 (l_contract_status_code = 'REJECTED') OR
1238 (l_contract_status_code = 'REQUIRES REAPPROVAL')) THEN
1239 l_results := 'Y';
1240 ELSE
1241 l_results := 'N';
1242 END IF;
1243 CLOSE po_status_csr;
1244
1245 ELSIF ((p_document_type='RFI') OR (p_document_type='RFQ')
1246 OR (p_document_type='AUCTION')) THEN
1247 OPEN neg_status_csr;
1248 FETCH neg_status_csr INTO l_contract_status_code;
1249 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1250 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1251 'Negotiation doc type, l_contract_status_code: ' || l_contract_status_code);
1252 END IF;
1253 IF ( (l_contract_status_code = 'ACTIVE') OR
1254 (l_contract_status_code = 'AWARD_APPROVAL_INPROCESS') OR
1255 (l_contract_status_code = 'AWARD_REJECTED') OR
1256 (l_contract_status_code = 'AWARD_IN_PROG') OR
1257 (l_contract_status_code = 'CLOSED') OR
1258 (l_contract_status_code = 'DRAFT') OR
1259 (l_contract_status_code = 'OPEN_FOR_BIDDING') OR
1260 (l_contract_status_code = 'PAUSED') OR
1261 (l_contract_status_code = 'PREVIEW') OR
1262 (l_contract_status_code = 'SUBMITTED')) THEN
1263 l_results := 'Y';
1264 ELSE
1265 l_results := 'N';
1266 END IF;
1267 CLOSE neg_status_csr;
1268 ELSIF (p_document_type='QUOTE') THEN
1269 /* OPEN quote_status_csr;
1270 FETCH quote_status_csr INTO l_contract_status_code;
1271 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1272 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1273 'Sales Quote doc type, l_contract_status_code: ' || l_contract_status_code);
1274 END IF;
1275 IF ( (l_contract_status_code = 'APPROVAL CANCELED') OR
1276 (l_contract_status_code = 'APPROVAL PENDING') OR
1277 (l_contract_status_code = 'APPROVAL REJECTED') OR
1278 (l_contract_status_code = 'DRAFT') OR
1279 (l_contract_status_code = 'ENTERED') OR
1280 (l_contract_status_code = 'INACTIVE') OR
1281 (l_contract_status_code = 'QUOTE GENERATED BY UW') OR
1282 (l_contract_status_code = 'REVIEWED') OR
1283 (l_contract_status_code = 'SUBMIT TO UNDERWRITING')) THEN
1284 l_results := 'Y';
1285 ELSE
1286 l_results := 'N';
1287 END IF;
1288 CLOSE quote_status_csr;
1289 --Attachments not getting archived on approval in okc_contract_docs in Sales Orders and Quotes. Hence, generating irrespective of status */
1290 l_results := 'Y';
1291 ELSIF (p_document_type='B') THEN
1292 /* OPEN bsa_status_csr;
1293 FETCH bsa_status_csr INTO l_contract_status_code;
1294 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1295 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1296 'Sales Agreement doc type, l_contract_status_code: ' || l_contract_status_code);
1297 END IF;
1298
1299 IF ( (l_contract_status_code= 'DRAFT') OR
1300 (l_contract_status_code= 'DRAFT_CUSTOMER_REJECTED') OR
1301 (l_contract_status_code= 'DRAFT_INTERNAL_REJECTED') OR
1302 (l_contract_status_code= 'DRAFT_SUBMITTED') OR
1303 (l_contract_status_code= 'ENTERED') OR
1304 (l_contract_status_code = 'INTERNAL_REJECTED') OR
1305 (l_contract_status_code = 'PENDING_INTERNAL_APPROVAL')) THEN
1306 l_results := 'Y';
1307 ELSE
1308 l_results := 'N';
1309 END IF;
1310 CLOSE bsa_status_csr;
1311 --Attachments not getting archived on approval in okc_contract_docs in Sales Orders and Quotes. Hence, generating irrespective of status */
1312 l_results := 'Y';
1313 ELSIF (p_document_type='O') THEN
1314 /* OPEN so_status_csr;
1315 FETCH so_status_csr INTO l_contract_status_code;
1316 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1317 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1318 'Sale Order doc type, l_contract_status_code: ' || l_contract_status_code);
1319 END IF;
1320 IF ( (l_contract_status_code = 'DRAFT') OR
1321 (l_contract_status_code = 'DRAFT_CUSTOMER_REJECTED') OR
1322 (l_contract_status_code = 'DRAFT_INTERNAL_REJECTED') OR
1323 (l_contract_status_code = 'DRAFT_SUBMITTED') OR
1324 (l_contract_status_code = 'ENTERED') OR
1325 (l_contract_status_code = 'PENDING_INTERNAL_APPROVAL')) THEN
1326 l_results := 'Y';
1327 ELSE
1328 l_results := 'N';
1329 END IF;
1330 CLOSE so_status_csr;
1331 --Attachments not getting archived on approval in okc_contract_docs in Sales Orders and Quotes. Hence, generating irrespective of status */
1332 l_results := 'Y';
1333 END IF; -- p_document_type like '%tmp_txt_search_draft'
1334
1335
1336 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1337 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1338 'l_results: ' || l_results);
1339 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1340 'Exiting is_contract_status_draft');
1341 END IF;
1342
1343 RETURN l_results;
1344
1345 EXCEPTION
1346 WHEN OTHERS THEN
1347 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1348 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION ,G_MODULE||l_api_name,
1349 'Leaving Function is_contract_status_draft because of EXCEPTION: '||sqlerrm);
1350 END IF;
1351 IF (rep_status_csr%ISOPEN) THEN
1352 CLOSE rep_status_csr ;
1353 END IF;
1354 IF (po_status_csr%ISOPEN) THEN
1355 CLOSE po_status_csr ;
1356 END IF;
1357 IF (neg_status_csr%ISOPEN) THEN
1358 CLOSE neg_status_csr ;
1359 END IF;
1360 IF (quote_status_csr%ISOPEN) THEN
1361 CLOSE quote_status_csr ;
1362 END IF;
1363 IF (so_status_csr%ISOPEN) THEN
1364 CLOSE so_status_csr ;
1365 END IF;
1366 IF (bsa_status_csr%ISOPEN) THEN
1367 CLOSE bsa_status_csr ;
1368 END IF;
1369 Okc_Api.Set_Message(p_app_name => 'OKC',
1370 p_msg_name => OKC_REP_UTIL_PVT.G_UNEXPECTED_ERROR,
1371 p_token1 => OKC_REP_UTIL_PVT.G_SQLCODE_TOKEN,
1372 p_token1_value => sqlcode,
1373 p_token2 => OKC_REP_UTIL_PVT.G_SQLERRM_TOKEN,
1374 p_token2_value => sqlerrm);
1375 RETURN l_results ;
1376 END is_contract_status_draft;
1377
1378 -- API name : get_neg_parties
1379 -- Type : Private.
1380 -- Function : This function fetches the party names for a given
1381 -- negotiation contract. It is called in the SQL statements
1382 -- of NegSearchExpVO.xml in the
1383 -- oracle.apps.okc.repository.search.server package.
1384 -- Pre-reqs : None.
1385 -- Parameters :
1386 -- IN : p_auction_header_id IN NUMBER
1387 -- The auction header ID of the contract document currently
1388 -- being crawled.
1389 -- OUT : x_parties OUT VARCHAR2
1390 -- A string of party names that define
1391 -- the parties of this negotiation contract. The party
1392 -- names are separated by spaces.
1393 -- Example: Vision, Inc. AT&T Informologics
1394 -- Note :
1395 FUNCTION get_neg_parties(
1396 p_auction_header_id IN NUMBER
1397 ) RETURN VARCHAR2
1398 IS
1399 l_api_name VARCHAR2(30);
1400 l_party_name VARCHAR2(500);
1401 l_parties VARCHAR2(2000);
1402
1403 -- Get the parties associated with this contract
1404 CURSOR party_csr IS
1405 SELECT v.vendor_name
1406 FROM pon_bid_headers b
1407 ,po_vendors v
1408 WHERE b.auction_header_id = p_auction_header_id
1409 AND b.bid_status IN ('ACTIVE','DISQUALIFIED')
1410 AND b.vendor_id = v.vendor_id;
1411
1412 party_rec party_csr%ROWTYPE;
1413
1414 BEGIN
1415
1416 l_api_name := 'get_neg_parties';
1417
1418 -- Do logging.
1419 IF ( FND_LOG.LEVEL_PROCEDURE>= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1420 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,
1421 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
1422 '200: Entered OKC_REP_SEARCH_UTIL_PVT.get_neg_parties');
1423 END IF;
1424 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1425 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,
1426 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
1427 'Input parameter is: p_auction_header_id = ' || p_auction_header_id||
1428 '.');
1429 END IF;
1430 FOR party_rec IN party_csr
1431 LOOP
1432 l_parties := l_parties || party_rec.vendor_name || ' ';
1433 END LOOP;
1434
1435 -- Do logging.
1436 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1437 THEN
1438 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,
1439 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
1440 'OKC_REP_UTIL_PVT.get_neg_parties returns l_parties as : '
1441 || l_parties);
1442 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,
1443 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
1444 'Leaving get_neg_parties');
1445 END IF;
1446
1447 RETURN l_parties;
1448
1449 EXCEPTION
1450 WHEN OTHERS
1451 THEN
1452 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1453 THEN
1454 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION ,
1455 'okc.plsql.OKC_REP_SEARCH_UTIL_PVT.'||l_api_name,
1456 'Leaving PROCEDURE get_neg_parties
1457 because of EXCEPTION: '||sqlerrm);
1458 END IF;
1459 Okc_Api.Set_Message(p_app_name => 'OKC',
1460 p_msg_name => OKC_REP_UTIL_PVT.G_UNEXPECTED_ERROR,
1461 p_token1 => OKC_REP_UTIL_PVT.G_SQLCODE_TOKEN,
1462 p_token1_value => sqlcode,
1463 p_token2 => OKC_REP_UTIL_PVT.G_SQLERRM_TOKEN,
1464 p_token2_value => sqlerrm);
1465 END get_neg_parties;
1466
1467 -- API name : get_contract_org
1468 -- Type : Private.
1469 -- Function : This function returns the org_id of a given contract.
1470 -- : Called from the Generate Attachments CP for the parameter check on org_id
1471 -- : at the time of deleting expired attachments.
1472 -- Pre-reqs : None.
1473 -- Parameters :
1474 -- IN : p_document_type IN VARCHAR2 Required
1475 -- Type of the document that is being checked
1476 -- : p_document_id IN VARCHAR2 Required
1477 -- Id of the document that is being checked
1478 -- OUT : Returns org_id of the given contract
1479 FUNCTION get_contract_org(
1480 p_document_type IN VARCHAR2,
1481 p_document_id IN NUMBER
1482 ) RETURN NUMBER
1483
1484 IS
1485 l_api_name VARCHAR2(30);
1486 l_msg_count NUMBER;
1487 l_msg_data VARCHAR2(2000);
1488 l_org_id NUMBER := -1;
1489
1490 CURSOR rep_org_csr IS
1491 SELECT org_id
1492 FROM okc_rep_contracts_all
1493 WHERE contract_id = p_document_id;
1494
1495 CURSOR po_org_csr IS
1496 SELECT org_id
1497 FROM po_headers_all
1498 WHERE po_header_id = p_document_id;
1499
1500 CURSOR neg_org_csr IS
1501 SELECT org_id
1502 FROM pon_auction_headers_all
1503 WHERE auction_header_id = p_document_id;
1504
1505 CURSOR quote_org_csr IS
1506 SELECT org_id
1507 FROM aso_quote_headers_all
1508 WHERE quote_header_id = p_document_id;
1509
1510 CURSOR so_org_csr IS
1511 SELECT org_id
1512 FROM oe_order_headers_all
1513 WHERE header_id = p_document_id;
1514
1515 CURSOR bsa_org_csr IS
1516 SELECT org_id
1517 FROM oe_blanket_headers_all
1518 WHERE header_id = p_document_id;
1519
1520 BEGIN
1521
1522 l_api_name := 'get_contract_org';
1523
1524 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1525 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1526 'Entered Function OKC_REP_SEARCH_UTIL_PVT.get_contract_org');
1527 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1528 'Document Id is: ' || p_document_id);
1529 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1530 'Document Type is: ' || p_document_type);
1531 END IF;
1532 IF (SubStr(p_document_type,1,3) = 'REP') THEN
1533 OPEN rep_org_csr;
1534 FETCH rep_org_csr INTO l_org_id;
1535 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1536 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1537 'Repository doc type, l_org_id: ' || l_org_id);
1538 END IF;
1539 CLOSE rep_org_csr;
1540
1541 ELSIF ((p_document_type='PA_BLANKET') OR (p_document_type='PA_CONTRACT')
1542 OR (p_document_type='PO_STANDARD')) THEN
1543 OPEN po_org_csr;
1544 FETCH po_org_csr INTO l_org_id;
1545 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1546 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1547 'PO doc type, l_org_id: ' || l_org_id);
1548 END IF;
1549 CLOSE po_org_csr;
1550
1551 ELSIF ((p_document_type='RFI') OR (p_document_type='RFQ')
1552 OR (p_document_type='AUCTION')) THEN
1553 OPEN neg_org_csr;
1554 FETCH neg_org_csr INTO l_org_id;
1555 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1556 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1557 'Negotiation doc type, l_org_id: ' || l_org_id);
1558 END IF;
1559 CLOSE neg_org_csr;
1560
1561 ELSIF (p_document_type='QUOTE') THEN
1562 OPEN quote_org_csr;
1563 FETCH quote_org_csr INTO l_org_id;
1564 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1565 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1566 'Sales Quote doc type, l_org_id: ' || l_org_id);
1567 END IF;
1568 CLOSE quote_org_csr;
1569
1570 ELSIF (p_document_type='B') THEN
1571 OPEN bsa_org_csr;
1572 FETCH bsa_org_csr INTO l_org_id;
1573 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1574 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1575 'Sales Agreement doc type, l_org_id: ' || l_org_id);
1576 END IF;
1577 CLOSE bsa_org_csr;
1578 ELSIF (p_document_type='O') THEN
1579 OPEN so_org_csr;
1580 FETCH so_org_csr INTO l_org_id;
1581 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1582 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1583 'Sale Order doc type, l_org_id: ' || l_org_id);
1584 END IF;
1585 CLOSE so_org_csr;
1586 END IF;
1587
1588
1589 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1590 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1591 'l_org_id: ' || l_org_id);
1592 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1593 'Exiting get_contract_org');
1594 END IF;
1595
1596 RETURN l_org_id;
1597
1598 EXCEPTION
1599 WHEN OTHERS THEN
1600 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1601 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION ,G_MODULE||l_api_name,
1602 'Leaving Function get_contract_org because of EXCEPTION: '||sqlerrm);
1603 END IF;
1604 IF (rep_org_csr%ISOPEN) THEN
1605 CLOSE rep_org_csr ;
1606 END IF;
1607 IF (po_org_csr%ISOPEN) THEN
1608 CLOSE po_org_csr ;
1609 END IF;
1610 IF (neg_org_csr%ISOPEN) THEN
1611 CLOSE neg_org_csr ;
1612 END IF;
1613 IF (quote_org_csr%ISOPEN) THEN
1614 CLOSE quote_org_csr ;
1615 END IF;
1616 IF (so_org_csr%ISOPEN) THEN
1617 CLOSE so_org_csr ;
1618 END IF;
1619 IF (bsa_org_csr%ISOPEN) THEN
1620 CLOSE bsa_org_csr ;
1621 END IF;
1622 Okc_Api.Set_Message(p_app_name => 'OKC',
1623 p_msg_name => OKC_REP_UTIL_PVT.G_UNEXPECTED_ERROR,
1624 p_token1 => OKC_REP_UTIL_PVT.G_SQLCODE_TOKEN,
1625 p_token1_value => sqlcode,
1626 p_token2 => OKC_REP_UTIL_PVT.G_SQLERRM_TOKEN,
1627 p_token2_value => sqlerrm);
1628 RETURN -1;
1629 END get_contract_org;
1630
1631
1632 END OKC_REP_SEARCH_UTIL_PVT;
[Home] [Help]