1 PACKAGE BODY OKC_TERMS_UTIL_PVT AS
2 /* $Header: OKCVDUTB.pls 120.26.12010000.2 2008/11/14 08:03:45 harchand ship $ */
3 g_concat_art_no VARCHAR2(1) := NVL(FND_PROFILE.VALUE('OKC_CONCAT_ART_NO'),'N');
4
5 ---------------------------------------------------------------------------
6 -- GLOBAL MESSAGE CONSTANTS
7 ---------------------------------------------------------------------------
8 G_FND_APP CONSTANT VARCHAR2(200) := OKC_API.G_FND_APP;
9 G_UNABLE_TO_RESERVE_REC CONSTANT VARCHAR2(200) := OKC_API.G_UNABLE_TO_RESERVE_REC;
10 G_RECORD_DELETED CONSTANT VARCHAR2(200) := OKC_API.G_RECORD_DELETED;
11 G_RECORD_CHANGED CONSTANT VARCHAR2(200) := OKC_API.G_RECORD_CHANGED;
12 G_RECORD_LOGICALLY_DELETED CONSTANT VARCHAR2(200) := OKC_API.G_RECORD_LOGICALLY_DELETED;
13 G_REQUIRED_VALUE CONSTANT VARCHAR2(200) := OKC_API.G_REQUIRED_VALUE;
14 G_INVALID_VALUE CONSTANT VARCHAR2(200) := OKC_API.G_INVALID_VALUE;
15 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_COL_NAME_TOKEN;
16 G_PARENT_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_PARENT_TABLE_TOKEN;
17 G_CHILD_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_CHILD_TABLE_TOKEN;
18 ---------------------------------------------------------------------------
19 -- GLOBAL CONSTANTS
20 ---------------------------------------------------------------------------
21 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKC_TERMS_UTIL_PVT';
22 G_MODULE CONSTANT VARCHAR2(250) := 'okc.plsql.'||G_PKG_NAME||'.';
23 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
24
25 G_TMPL_DOC_TYPE CONSTANT VARCHAR2(30) := OKC_TERMS_UTIL_GRP.G_TMPL_DOC_TYPE;
26 G_UNASSIGNED_SECTION_CODE CONSTANT VARCHAR2(30) := 'UNASSIGNED';
27 G_AMEND_CODE_UPDATED CONSTANT VARCHAR2(30) := 'UPDATED';
28 G_ATTACHED_CONTRACT_SOURCE CONSTANT VARCHAR2(30) := 'ATTACHED';
29 ------------------------------------------------------------------------------
30 -- GLOBAL CONSTANTS
31 ------------------------------------------------------------------------------
32 G_FALSE CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
33 G_TRUE CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
34
35 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
36 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
37 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
38
39 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_UNEXPECTED_ERROR';
40 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_MESSAGE';
41 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_CODE';
42 ------------------------------------------------------------------------------
43 -- GLOBAL EXCEPTIONS
44 ------------------------------------------------------------------------------
45 E_Resource_Busy EXCEPTION;
46 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
47
48 -- 11.5.10+ change
49 -- Global variable set by the original Merge_Template_Working_Copy procedure
50 -- Returned by the overloaded Merge_Template_Working_Copy
51 g_parent_template_id NUMBER;
52
53
54 --==================== INTERNAL PROCEDURES ============================
55 PROCEDURE ALLOWED_TMPL_USAGES_Delete_Set(
56 x_return_status OUT NOCOPY VARCHAR2,
57 x_msg_data OUT NOCOPY VARCHAR2,
58 x_msg_count OUT NOCOPY NUMBER,
59 p_template_id IN NUMBER
60 ) IS
61 l_api_name CONSTANT VARCHAR2(30) := 'ALLOWED_TMPL_USAGES_Delete_Set';
62
63
64 CURSOR l_get_rec IS
65 SELECT allowed_tmpl_usages_id,object_version_number
66 FROM OKC_ALLOWED_TMPL_USAGES
67 WHERE TEMPLATE_ID = p_template_id;
68 BEGIN
69
70 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
71 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Entered ALLOWED_TMPL_USAGES_Delete_Set');
72 END IF;
73
74 FOR cr IN l_get_rec LOOP
75
76 OKC_ALLOWED_TMPL_USAGES_GRP.Delete_Allowed_Tmpl_Usages(
77 p_api_version => 1,
78 p_init_msg_list => FND_API.G_FALSE,
79 p_commit => FND_API.G_FALSE,
80 x_return_status => x_return_status,
81 x_msg_count => x_msg_count,
82 x_msg_data => x_msg_data,
83
84 p_allowed_tmpl_usages_id => cr.allowed_tmpl_usages_id,
85
86 p_object_version_number => cr.object_version_number
87 );
88
89 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
90 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
91 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
92 RAISE FND_API.G_EXC_ERROR ;
93 END IF;
94 END LOOP;
95
96 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
97 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'400: Leaving ALLOWED_TMPL_USAGES_Delete_Set');
98 END IF;
99 EXCEPTION
100 WHEN FND_API.G_EXC_ERROR THEN
101 IF (l_get_rec%ISOPEN) THEN
102 CLOSE l_get_rec;
103 END IF;
104
105 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
106 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'500: Leaving ALLOWED_TMPL_USAGES_Delete_Set:FND_API.G_EXC_ERROR Exception');
107 END IF;
108 x_return_status := G_RET_STS_ERROR ;
109
110 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
111 IF (l_get_rec%ISOPEN) THEN
112 CLOSE l_get_rec;
113 END IF;
114
115 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
116 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'500: Leaving ALLOWED_TMPL_USAGES_Delete_Set:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
117 END IF;
118 x_return_status := G_RET_STS_UNEXP_ERROR ;
119
120
121 WHEN OTHERS THEN
122 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
123 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'600: Leaving ALLOWED_TMPL_USAGES_Delete_Set because of EXCEPTION: '||sqlerrm);
124 END IF;
125
126 IF (l_get_rec%ISOPEN) THEN
127 CLOSE l_get_rec;
128 END IF;
129
130 x_return_status := G_RET_STS_UNEXP_ERROR ;
131 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
132 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
133 END IF;
134 END ALLOWED_TMPL_USAGES_Delete_Set;
135 ---
136
137 PROCEDURE Update_Allowed_Tmpl_Usages_Id(
138 x_return_status OUT NOCOPY VARCHAR2,
139 x_msg_data OUT NOCOPY VARCHAR2,
140 x_msg_count OUT NOCOPY NUMBER,
141 p_old_template_id IN NUMBER,
142 p_new_template_id IN NUMBER
143 ) IS
144 l_api_name CONSTANT VARCHAR2(30) := 'Update_Allowed_Tmpl_Usages_Id';
145 CURSOR l_get_rec IS
146 SELECT allowed_tmpl_usages_id,object_version_number,document_type,default_yn
147 FROM OKC_ALLOWED_TMPL_USAGES
148 WHERE TEMPLATE_ID = p_old_template_id;
149 BEGIN
150 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
151 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1100: Entered Update_Allowed_Tmpl_Usages_Id');
152
153 END IF;
154
155 FOR cr in l_get_rec LOOP
156
157 OKC_ALLOWED_TMPL_USAGES_GRP.update_Allowed_Tmpl_Usages(
158 p_api_version => 1,
159 p_init_msg_list => FND_API.G_FALSE,
160 p_commit => FND_API.G_FALSE,
161 x_return_status => x_return_status,
162 x_msg_count => x_msg_count,
163 x_msg_data => x_msg_data,
164 p_template_id => p_new_template_id,
165 p_document_type => cr.document_type,
166 p_default_yn => cr.default_yn,
167 p_allowed_tmpl_usages_id => cr.allowed_tmpl_usages_id,
168
169 p_object_version_number => cr.object_version_number
170 );
171
172 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
173 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
174 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
175 RAISE FND_API.G_EXC_ERROR ;
176 END IF;
177
178 END LOOP;
179
180 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
181 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1400: Update_Allowed_Tmpl_Usages_Id');
182 END IF;
183
184 EXCEPTION
185 WHEN FND_API.G_EXC_ERROR THEN
186 IF (l_get_rec%ISOPEN) THEN
187 CLOSE l_get_rec;
188 END IF;
189
190 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
191 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'500: Leaving Update_Allowed_Tmpl_Usages_Id :FND_API.G_EXC_ERROR');
192 END IF;
193 x_return_status := G_RET_STS_ERROR ;
194
195 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
196 IF (l_get_rec%ISOPEN) THEN
197 CLOSE l_get_rec;
198 END IF;
199
200 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
201 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'500: Leaving Update_Allowed_Tmpl_Usages_Id : FND_API.G_EXC_UNEXPECTED_ERROR');
202 END IF;
203 x_return_status := G_RET_STS_UNEXP_ERROR ;
204
205
206 WHEN OTHERS THEN
207 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
208 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1600: Leaving Update_Allowed_Tmpl_Usages_Id because of EXCEPTION: '||sqlerrm);
209 END IF;
210
211 IF (l_get_rec%ISOPEN) THEN
212 CLOSE l_get_rec;
213 END IF;
214
215 x_return_status := G_RET_STS_UNEXP_ERROR ;
216 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
217 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
218 END IF;
219
220
221 END Update_Allowed_Tmpl_Usages_Id;
222
223 --==================== INTERNAL PROCEDURES ============================
224
225
226 /*
227 -- PROCEDURE Delete_Doc
228 -- To be used to delete Terms whenever a document is deleted.
229 */
230 PROCEDURE Delete_Doc (
231 x_return_status OUT NOCOPY VARCHAR2,
232
233 p_doc_type IN VARCHAR2,
234 p_doc_id IN NUMBER
235
236 ) IS
237 l_api_version CONSTANT NUMBER := 1;
238 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Doc';
239 l_found BOOLEAN;
240 l_status OKC_TERMS_TEMPLATES.STATUS_CODE%TYPE;
241 l_flag VARCHAR2(1);
242 l_objnum NUMBER;
243 l_msg_count NUMBER;
244 l_msg_data VARCHAR2(2000);
245
246 CURSOR tmpl_sts_crs IS
247 SELECT STATUS_CODE FROM okc_terms_templates
248 WHERE TEMPLATE_ID = p_doc_id;
249
250 CURSOR tmpl_prnt_crs IS
251 SELECT '!' FROM okc_terms_templates
252 WHERE parent_template_id = p_doc_id;
253
254 CURSOR tmpl_usd_crs IS
255 SELECT '!' FROM okc_template_usages_v
256 WHERE TEMPLATE_ID = p_doc_id AND ROWNUM=1;
257
258 CURSOR tt_csr IS
259 SELECT TEMPLATE_ID, object_version_number,template_model_id
260 FROM okc_terms_templates
261 WHERE template_id=p_doc_id;
262
263 CURSOR objnum_tu_csr IS
264 SELECT object_version_number
265 FROM OKC_TEMPLATE_USAGES
266 WHERE DOCUMENT_TYPE = p_doc_type AND DOCUMENT_ID = p_doc_id;
267
268 BEGIN
269 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
270 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2900: Entered Delete_Doc');
271 END IF;
272 -- Initialize API return status to success
273 x_return_status := G_RET_STS_SUCCESS;
274
275 IF p_doc_type=G_TMPL_DOC_TYPE THEN
276
277 OPEN tmpl_sts_crs;
278 FETCH tmpl_sts_crs INTO l_status;
279 l_found := tmpl_sts_crs%FOUND;
280 CLOSE tmpl_sts_crs;
281
282 IF NOT l_found THEN
283 --?? Put some Message output
284 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
285 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3000: Template has not been found');
286 END IF;
287 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
288 END IF;
289
290 OPEN tmpl_prnt_crs;
291 FETCH tmpl_prnt_crs INTO l_status;
292 l_found := tmpl_prnt_crs%FOUND;
293 CLOSE tmpl_prnt_crs;
294
295 IF l_status NOT IN ('DRAFT', 'REJECTED','REVISION')
296 OR l_found THEN -- true if there's a revision for the template
297 --?? Put some Message output
298 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
299 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3100: Template Status is not valid to delete it');
300 END IF;
301 RAISE FND_API.G_EXC_ERROR;
302 END IF;
303
304 OPEN tmpl_usd_crs;
305 FETCH tmpl_usd_crs INTO l_flag;
306 l_found := tmpl_usd_crs%FOUND;
307 CLOSE tmpl_usd_crs;
308
309 IF l_found THEN
310 --?? Put some Message output
311 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
312 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3200: Template is already used - so can not be deleted');
313 END IF;
314 RAISE FND_API.G_EXC_ERROR;
315 END IF;
316
317 --------------------------------------------
318 -- Delete the record from OKC_ALLOWED_TMPL_USAGES_V
319 --------------------------------------------
320 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
321 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3300: Delete each record from OKC_ALLOWED_TMPL_USAGES_V in a loop');
322 END IF;
323 --------------------------------------------
324 ALLOWED_TMPL_USAGES_Delete_Set(
325 x_return_status => x_return_status,
326 x_msg_data => l_msg_data,
327 x_msg_count => l_msg_count,
328 p_template_id => p_doc_id
329 );
330 --------------------------------------------
331 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
332 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
333 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
334 RAISE FND_API.G_EXC_ERROR ;
335 END IF;
336 --------------------------------------------
337
338 -- Delete record from okc_terms_templates
339 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
340 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3400: Delete each record from okc_terms_templates in a loop');
341 END IF;
342 FOR cr IN tt_csr LOOP
343 --------------------------------------------
344 -- Delete each record from okc_terms_templates for
345 --------------------------------------------
346 OKC_TERMS_TEMPLATES_PVT.Delete_Row(
347 x_return_status => x_return_status,
348 p_template_id => cr.template_id,
349 p_object_version_number => cr.object_version_number
350 );
351 --------------------------------------------
352 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
353 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
354 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
355 RAISE FND_API.G_EXC_ERROR ;
356 END IF;
357 --------------------------------------------
358 /*
359 * Removed call to OKC_EXPRT_UTIL_GRP for 11.5.10+: Contract Expert Changes
360 */
361 --------------------------------------------
362
363 --Added 11.5.10+ CE
364 --------------------------------------------
365 OKC_XPRT_TMPL_RULE_ASSNS_PVT.delete_template_rule_assns(
366 p_api_version => 1,
367 p_init_msg_list => OKC_API.G_FALSE,
368 p_commit => OKC_API.G_FALSE,
369 p_template_id => cr.template_id,
370 x_return_status => x_return_status,
371 x_msg_data => l_msg_data,
372 x_msg_count => l_msg_count);
373 --------------------------------------------
374 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
375 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
376 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
377 RAISE FND_API.G_EXC_ERROR ;
378 END IF;
379 --------------------------------------------
380
381 END LOOP;
382
383 END IF;
384
385 --------------------------------------------
386 -- Delete record from okc_k_art_varaibles
387 --------------------------------------------
388 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
389 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3500: Delete records from okc_k_art_varaibles for the doc');
390 END IF;
391 OKC_K_ART_VARIABLES_PVT.delete_set(
392 x_return_status => x_return_status,
393 p_doc_type => p_doc_type,
394 p_doc_id => p_doc_id
395 );
396 --------------------------------------------
397 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
398 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
399 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
400 RAISE FND_API.G_EXC_ERROR ;
401 END IF;
402 --------------------------------------------
403
404 --------------------------------------------
405 -- Delete record from okc_k_articles_v
406 --------------------------------------------
407 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
408 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3600: Delete records from okc_k_articles_v for the doc');
409 END IF;
410 OKC_K_ARTICLES_PVT.delete_set(
411 x_return_status => x_return_status,
412 p_doc_type => p_doc_type,
413 p_doc_id => p_doc_id
414 );
415 --------------------------------------------
416 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
417 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
418 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
419 RAISE FND_API.G_EXC_ERROR ;
420 END IF;
421 --------------------------------------------
422
423 --------------------------------------------
424 -- Delete record from okc_sections_v
425 --------------------------------------------
426 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
427 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3700: Delete records from okc_sections_v for the doc');
428 END IF;
429 OKC_TERMS_SECTIONS_PVT.delete_set(
430 x_return_status => x_return_status,
431 p_doc_type => p_doc_type,
432 p_doc_id => p_doc_id
433 );
434 --------------------------------------------
435 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
436 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
437 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
438 RAISE FND_API.G_EXC_ERROR ;
439 END IF;
440 --------------------------------------------
441
442 -------------------------------------------------
443 -- Remove any uploaded terms under review
444 ------------------------------------------------
445
446 OKC_REVIEW_UPLD_TERMS_PVT.delete_uploaded_terms(
447 p_api_version => l_api_version,
448 p_document_type => p_doc_type,
449 p_document_id => p_doc_id,
450 x_return_status => x_return_status,
451 x_msg_data => l_msg_data,
452 x_msg_count => l_msg_count);
453 --------------------------------------------
454 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
455 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
456 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
457 RAISE FND_API.G_EXC_ERROR ;
458 END IF;
459 --------------------------------------------
460
461 ------------------------------------------------
462
463 --------------------------------------------
464 -- Delete record form okc_template_usages
465 --------------------------------------------
466 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
467 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3800: Delete a record from okc_template_usages for the doc');
468 END IF;
469 l_objnum := -1;
470 OPEN objnum_tu_csr;
471 FETCH objnum_tu_csr INTO l_objnum;
472 l_found := objnum_tu_csr%FOUND;
473 CLOSE objnum_tu_csr;
474 IF l_found THEN
475 OKC_TEMPLATE_USAGES_PVT.delete_row(
476 x_return_status => x_return_status,
477 p_document_type => p_doc_type,
478 p_document_id => p_doc_id,
479 p_object_version_number => l_objnum
480 );
481 --------------------------------------------
482 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
483 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
484 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
485 RAISE FND_API.G_EXC_ERROR ;
486 END IF;
487 --------------------------------------------
488 END IF;
489
490
491
492 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
493 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4000: Leaving Delete_Doc');
494 END IF;
495 EXCEPTION
496 WHEN FND_API.G_EXC_ERROR THEN
497 IF tmpl_sts_crs%ISOPEN THEN
498 CLOSE tmpl_sts_crs;
499 END IF;
500
501 IF tmpl_usd_crs%ISOPEN THEN
502 CLOSE tmpl_usd_crs;
503 END IF;
504
505 IF tmpl_prnt_crs%ISOPEN THEN
506 CLOSE tmpl_prnt_crs;
507 END IF;
508
509 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
510 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'4100: Leaving Delete_Doc : OKC_API.G_EXCEPTION_ERROR Exception');
511 END IF;
512 x_return_status := G_RET_STS_ERROR ;
513
514 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
515 IF tmpl_sts_crs%ISOPEN THEN
516 CLOSE tmpl_sts_crs;
517 END IF;
518
519 IF tmpl_usd_crs%ISOPEN THEN
520 CLOSE tmpl_usd_crs;
521 END IF;
522
523 IF tmpl_prnt_crs%ISOPEN THEN
524 CLOSE tmpl_prnt_crs;
525 END IF;
526
527 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
528 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'4200: Leaving Delete_Doc : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
529 END IF;
530 x_return_status := G_RET_STS_UNEXP_ERROR ;
531
532 WHEN OTHERS THEN
533 IF tmpl_sts_crs%ISOPEN THEN
534 CLOSE tmpl_sts_crs;
535 END IF;
536
537 IF tmpl_usd_crs%ISOPEN THEN
538 CLOSE tmpl_usd_crs;
539 END IF;
540
541 IF tmpl_prnt_crs%ISOPEN THEN
542 CLOSE tmpl_prnt_crs;
543 END IF;
544
545 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
546 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'4300: Leaving Delete_Doc because of EXCEPTION: '||sqlerrm);
547 END IF;
548
549 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
550 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
551 END IF;
552 x_return_status := G_RET_STS_UNEXP_ERROR ;
553 END Delete_Doc ;
554 /*
555 -- PROCEDURE delete_doc_version
556 -- To be used to delete Terms whenever a document is deleted.
557 */
558 PROCEDURE delete_doc_version (
559 x_return_status OUT NOCOPY VARCHAR2,
560
561 p_doc_type IN VARCHAR2,
562 p_doc_id IN NUMBER,
563 p_version_number IN NUMBER
564
565 ) IS
566 l_api_version CONSTANT NUMBER := 1;
567 l_api_name CONSTANT VARCHAR2(30) := 'delete_doc_version';
568 l_found BOOLEAN;
569 l_status OKC_TERMS_TEMPLATES.STATUS_CODE%TYPE;
570 l_flag VARCHAR2(1);
571 l_objnum NUMBER;
572 l_msg_count NUMBER;
573 l_msg_data VARCHAR2(2000);
574
575 BEGIN
576
577 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
578 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2900: Entered delete_doc_version');
579 END IF;
580 -- Initialize API return status to success
581 x_return_status := G_RET_STS_SUCCESS;
582
583
584 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
585 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3500: Delete records from okc_k_art_varaibles_h for the doc');
586 END IF;
587
588 x_return_status:=OKC_K_ART_VARIABLES_PVT.delete_version(
589 p_doc_type => p_doc_type,
590 p_doc_id => p_doc_id,
591 p_major_version => p_version_number
592 );
593 --------------------------------------------
594 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
595 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
596 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
597 RAISE FND_API.G_EXC_ERROR ;
598 END IF;
599 --------------------------------------------
600
601 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
602 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3600: Delete records from okc_k_articles_bh for the doc');
603 END IF;
604
605 x_return_status:=OKC_K_ARTICLES_PVT.delete_version(
606 p_doc_type => p_doc_type,
607 p_doc_id => p_doc_id,
608 p_major_version => p_version_number
609 );
610 --------------------------------------------
611 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
612 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
613 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
614 RAISE FND_API.G_EXC_ERROR ;
615 END IF;
616 --------------------------------------------
617
618 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
619 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3700: Delete records from okc_sections_h for the doc');
620 END IF;
621 x_return_status:=OKC_TERMS_SECTIONS_PVT.delete_version(
622 p_doc_type => p_doc_type,
623 p_doc_id => p_doc_id,
624 p_major_version => p_version_number
625 );
626 --------------------------------------------
627 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
628 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
629 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
630 RAISE FND_API.G_EXC_ERROR ;
631 END IF;
632 --------------------------------------------
633
634 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
635 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3800: Delete a record from okc_template_usages_h for the doc');
636 END IF;
637
638 x_return_status:=OKC_TEMPLATE_USAGES_PVT.delete_version(
639 p_doc_type => p_doc_type,
640 p_doc_id => p_doc_id,
641 p_major_version => p_version_number
642 );
643 --------------------------------------------
644 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
645 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
646 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
647 RAISE FND_API.G_EXC_ERROR ;
648 END IF;
649 --------------------------------------------
650
651 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
652 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4000: Leaving delete_doc_version');
653 END IF;
654
655 EXCEPTION
656 WHEN FND_API.G_EXC_ERROR THEN
657 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
658 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'4100: Leaving delete_doc_version : OKC_API.G_EXCEPTION_ERROR Exception');
659 END IF;
660 x_return_status := G_RET_STS_ERROR ;
661
662 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
663 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
664 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'4200: Leaving delete_doc_version : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
665 END IF;
666 x_return_status := G_RET_STS_UNEXP_ERROR ;
667
668 WHEN OTHERS THEN
669
670 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
671 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'4300: Leaving delete_doc_version because of EXCEPTION: '||sqlerrm);
672 END IF;
673
674 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
675 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
676 END IF;
677 x_return_status := G_RET_STS_UNEXP_ERROR ;
678 END Delete_Doc_version ;
679
680 /*
681 -- PROCEDURE Mark_Amendment
682 -- This API will be used to mark any article as amended if any of variables have been changed.
683 */
684 PROCEDURE Mark_Amendment (
685 p_api_version IN NUMBER,
686 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
687
688 x_return_status OUT NOCOPY VARCHAR2,
689 x_msg_data OUT NOCOPY VARCHAR2,
690 x_msg_count OUT NOCOPY NUMBER,
691
692 p_doc_type IN VARCHAR2,
693 p_doc_id IN NUMBER,
694 p_variable_code IN VARCHAR2
695 ) IS
696 l_api_version CONSTANT NUMBER := 1;
697 l_api_name CONSTANT VARCHAR2(30) := 'Mark_Amendment';
698 CURSOR idlist_crs IS
699 SELECT distinct id, kart.object_version_number
700 FROM okc_k_articles_b kart, okc_k_art_variables var
701 WHERE document_type = p_doc_type
702 AND document_id = p_doc_id
703 AND var.cat_id = kart.id
704 AND kart.amendment_operation_code IS NULL
705 and var.variable_code = p_variable_code;
706 BEGIN
707 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
708 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4400: Entered Mark_Amendment');
709 END IF;
710 -- Standard Start of API savepoint
711 SAVEPOINT g_Mark_Amendment;
712 -- Standard call to check for call compatibility.
713 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
714 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
715 END IF;
716 -- Initialize message list if p_init_msg_list is set to TRUE.
717 IF FND_API.to_Boolean( p_init_msg_list ) THEN
718 FND_MSG_PUB.initialize;
719 END IF;
720 -- Initialize API return status to success
721 x_return_status := G_RET_STS_SUCCESS;
722
723 -- Delete record from okc_K_ARTICLES
724 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
725 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4500: Update each record from okc_K_ARTICLES in a loop');
726 END IF;
727 FOR cr IN idlist_crs LOOP
728 --------------------------------------------
729 -- Update each record from okc_terms_templates for
730 --------------------------------------------
731 OKC_K_ARTICLES_GRP.Update_article(
732 p_api_version => 1,
733 p_init_msg_list => FND_API.G_FALSE ,
734 x_return_status => x_return_status,
735 x_msg_data => x_msg_data,
736 x_msg_count => x_msg_count,
737 p_mode => 'AMEND',
738 p_id => cr.id,
739 p_object_version_number => cr.object_version_number
740 );
741 --------------------------------------------
742 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
743 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
744 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
745 RAISE FND_API.G_EXC_ERROR ;
746 END IF;
747 --------------------------------------------
748 END LOOP;
749
750 -- Standard call to get message count and if count is 1, get message info.
751 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
752 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
753 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4600: Leaving Mark_Amendment');
754 END IF;
755 EXCEPTION
756 WHEN FND_API.G_EXC_ERROR THEN
757 ROLLBACK TO g_Mark_Amendment;
758 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
759 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'4700: Leaving Mark_Amendment : OKC_API.G_EXCEPTION_ERROR Exception');
760 END IF;
761 x_return_status := G_RET_STS_ERROR ;
762 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
763
764 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
765 ROLLBACK TO g_Mark_Amendment;
766 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
767 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'4800: Leaving Mark_Amendment : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
768 END IF;
769 x_return_status := G_RET_STS_UNEXP_ERROR ;
770 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
771
772 WHEN OTHERS THEN
773 ROLLBACK TO g_Mark_Amendment;
774 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
775 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'4900: Leaving Mark_Amendment because of EXCEPTION: '||sqlerrm);
776 END IF;
777
778 x_return_status := G_RET_STS_UNEXP_ERROR ;
779 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
780 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
781 END IF;
782 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
783 END Mark_Amendment;
784
785 /*
786 -- PROCEDURE Merge_Template_Working_Copy
787 -- To be used to merge a working copy of a template is approved and old copy
788 -- and working copy
789 -- 11.5.10+ changes
790 1. Store the parent template id in a package global variable. This will retrieved
791 and returned by the overaloaded procedure.
792 2. Update the table OKC_TMPL_DRAFT_CLAUSES with the merged/parent template id.
793 */
794 PROCEDURE Merge_Template_Working_Copy (
795 p_api_version IN NUMBER,
796 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
797 p_commit IN VARCHAR2 := FND_API.G_FALSE,
798
799 x_return_status OUT NOCOPY VARCHAR2,
800 x_msg_data OUT NOCOPY VARCHAR2,
801 x_msg_count OUT NOCOPY NUMBER,
802
803 p_template_id IN NUMBER
804 ) IS
805 l_api_version CONSTANT NUMBER := 1;
806 l_api_name CONSTANT VARCHAR2(30) := 'Merge_Template_Working_Copy';
807
808 l_base_template_id NUMBER;
809 l_found BOOLEAN;
810
811 CURSOR get_par_id_csr IS
812 SELECT parent_template_id
813 FROM okc_terms_templates_all
814 WHERE template_id=p_template_id;
815
816 CURSOR atu_csr IS
817 SELECT template_id, document_type
818 FROM OKC_ALLOWED_TMPL_USAGES
819 WHERE TEMPLATE_ID = l_base_template_id;
820
821 CURSOR kart_csr IS
822 SELECT id, object_version_number
823 FROM okc_k_articles_b
824 WHERE document_type=G_TMPL_DOC_TYPE
825 AND document_id = p_template_id;
826
827 CURSOR sect_csr IS
828 SELECT id, object_version_number
829 FROM okc_sections_b
830 WHERE document_type=G_TMPL_DOC_TYPE
831 AND document_id = p_template_id;
832
833 BEGIN
834 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
835 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5000: Entered Merge_Template_Working_Copy');
836 END IF;
837 -- Standard Start of API savepoint
838 SAVEPOINT g_Merge_Template_Working_Copy;
839 -- Standard call to check for call compatibility.
840 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
841 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
842 END IF;
843 -- Initialize message list if p_init_msg_list is set to TRUE.
844 IF FND_API.to_Boolean( p_init_msg_list ) THEN
845 FND_MSG_PUB.initialize;
846 END IF;
847 -- Initialize API return status to success
848 x_return_status := G_RET_STS_SUCCESS;
849
850 -- ================ Actual Procedure Code Start =======================
851
852 --------------------------------------------
853 -- Get template id of original template
854 --------------------------------------------
855 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
856 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5100: - get template id of original template');
857 END IF;
858 --------------------------------------------
859 OPEN get_par_id_csr;
860 FETCH get_par_id_csr INTO l_base_template_id;
861 l_found := get_par_id_csr%FOUND;
862 CLOSE get_par_id_csr;
863 IF not l_found THEN
864 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'p_template_id');
865 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
866 END IF;
867
868 --------------------------------------------
869 -- Delete Base Template
870 --------------------------------------------
871 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
872 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5200: - Delete Base Template');
873 END IF;
874 --------------------------------------------
875 OKC_TERMS_TEMPLATES_PVT.Delete_Row(
876 x_return_status => x_return_status,
877 p_template_id => l_base_template_id,
878 p_object_version_number => NULL ,
879 p_delete_parent_yn => 'Y'
880 );
881 --------------------------------------------
882 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
883 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
884 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
885 RAISE FND_API.G_EXC_ERROR ;
886 END IF;
887 --------------------------------------------
888
889 --------------------------------------------
890 -- Delete Allowed template usage record for base template:
891 --------------------------------------------
892 ALLOWED_TMPL_USAGES_Delete_Set(
893 x_return_status => x_return_status,
894 x_msg_data => x_msg_data,
895 x_msg_count => x_msg_count,
896 p_template_id => l_base_template_id
897 );
898 --------------------------------------------
899 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
900 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
901 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
902 RAISE FND_API.G_EXC_ERROR ;
903 END IF;
904 --------------------------------------------
905
906 --------------------------------------------
907 -- Delete record from okc_k_art_varaibles
908 --------------------------------------------
909 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
910 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5300: Delete records from okc_k_art_varaibles for the doc');
911 END IF;
912 --------------------------------------------
913 OKC_K_ART_VARIABLES_PVT.delete_set(
914 x_return_status => x_return_status,
915 p_doc_type => G_TMPL_DOC_TYPE,
916 p_doc_id => l_base_template_id
917 );
918 --------------------------------------------
919 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
920 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
921 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
922 RAISE FND_API.G_EXC_ERROR ;
923 END IF;
924 --------------------------------------------
925
926 --------------------------------------------
927 -- Delete record from okc_k_articles_v
928 --------------------------------------------
929 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
930 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5400: Delete records from okc_k_articles_v for the doc');
931 END IF;
932 --------------------------------------------
933 OKC_K_ARTICLES_PVT.delete_set(
934 x_return_status => x_return_status,
935 p_doc_type => G_TMPL_DOC_TYPE,
936 p_doc_id => l_base_template_id
937 );
938 --------------------------------------------
939 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
940 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
941 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
942 RAISE FND_API.G_EXC_ERROR ;
943 END IF;
944 --------------------------------------------
945
946 --------------------------------------------
947 -- Delete record from okc_sections_v
948 --------------------------------------------
949 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
950 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5500: Delete records from okc_sections_v for the doc');
951 END IF;
952 OKC_TERMS_SECTIONS_PVT.delete_set(
953 x_return_status => x_return_status,
954 p_doc_type => G_TMPL_DOC_TYPE,
955 p_doc_id => l_base_template_id
956 );
957 --------------------------------------------
958 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
959 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
960 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
961 RAISE FND_API.G_EXC_ERROR ;
962 END IF;
963 --------------------------------------------
964
965 --------------------------------------------
966 -- Delete base template's Deliverables: Call Deliverable API to delete delevirable from the base template.
967 --------------------------------------------
968 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
969 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5600: Delete delevirable from the base template');
970 END IF;
971 Okc_Deliverable_Process_Pvt.Delete_Deliverables(
972 p_api_version => p_api_version,
973 p_init_msg_list => p_init_msg_list,
974 p_doc_type => G_TMPL_DOC_TYPE,
975 p_doc_id => l_base_template_id,
976 p_doc_version => -99,
977 x_msg_data => x_msg_data,
978 x_msg_count => x_msg_count,
979 x_return_status => x_return_status
980 );
981 --------------------------------------------
982 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
983 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
984 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
985 RAISE FND_API.G_EXC_ERROR ;
986 END IF;
987 --------------------------------------------
988
989
990 --------------------------------------------
991 -- Update Template Id of working template:
992 --------------------------------------------
993 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
994 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5700: Update Template Id of working template to old template');
995 END IF;
996
997 OKC_TERMS_TEMPLATES_PVT.Update_Template_Id(
998 x_return_status => x_return_status,
999 p_old_template_id => p_template_id,
1000 p_new_template_id => l_base_template_id
1001 );
1002 --------------------------------------------
1003 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1004 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1005 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1006 RAISE FND_API.G_EXC_ERROR ;
1007 END IF;
1008 --------------------------------------------
1009
1010 --------------------------------------------
1011 -- Update Allowed template Usages Record:
1012 --------------------------------------------
1013 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1014 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5800: Update Template Id of working template to old template for the template Usages ');
1015 END IF;
1016 Update_Allowed_Tmpl_Usages_Id(
1017 x_return_status => x_return_status,
1018 x_msg_data => x_msg_data,
1019 x_msg_count => x_msg_count,
1020 p_old_template_id => p_template_id,
1021 p_new_template_id => l_base_template_id
1022 );
1023 --------------------------------------------
1024 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1025 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1026 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1027 RAISE FND_API.G_EXC_ERROR ;
1028 END IF;
1029 --------------------------------------------
1030
1031 --------------------------------------------
1032 -- Update sections of working template:
1033 --------------------------------------------
1034 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1035 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5900: Update Template Id of working template to old template for the sections');
1036 END IF;
1037 FOR cr IN sect_csr LOOP
1038 --------------------------------------------
1039 -- Update each record from okc_K_ARTICLES for
1040 --------------------------------------------
1041 OKC_TERMS_SECTIONS_PVT.Update_Row(
1042 x_return_status => x_return_status,
1043 p_id => cr.id,
1044 p_document_id => l_base_template_id,
1045 p_object_version_number => cr.object_version_number
1046 );
1047 --------------------------------------------
1048 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1049 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1050 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1051 RAISE FND_API.G_EXC_ERROR ;
1052 END IF;
1053 --------------------------------------------
1054 END LOOP;
1055
1056 --------------------------------------------
1057 -- Update articles of working template:
1058 --------------------------------------------
1059 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1060 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'6000: Update Template Id of working template to old template for the articles');
1061 END IF;
1062 FOR cr IN kart_csr LOOP
1063 --------------------------------------------
1064 -- Update each record from okc_K_ARTICLES for
1065 --------------------------------------------
1066 OKC_K_ARTICLES_PVT.Update_Row(
1067 x_return_status => x_return_status,
1068 p_id => cr.id,
1069 p_document_id => l_base_template_id,
1070 p_object_version_number => cr.object_version_number
1071 );
1072 --------------------------------------------
1073 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1074 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1075 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1076 RAISE FND_API.G_EXC_ERROR ;
1077 END IF;
1078 --------------------------------------------
1079 END LOOP;
1080
1081 --------------------------------------------
1082 -- Call deliverable APIs to update deliverable records with l_base_template_id
1083 --------------------------------------------
1084 OKC_DELIVERABLE_PROCESS_PVT.update_del_for_template_merge (
1085 p_api_version => p_api_version ,
1086 p_init_msg_list => p_init_msg_list,
1087 x_msg_data => x_msg_data ,
1088 x_msg_count => x_msg_count ,
1089 x_return_status => x_return_status,
1090
1091 p_base_template_id => l_base_template_id,
1092 p_working_template_id => p_template_id
1093 );
1094 --------------------------------------------
1095 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1096 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1097 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1098 RAISE FND_API.G_EXC_ERROR ;
1099 END IF;
1100 --------------------------------------------
1101
1102 --Added 11.5.10+ CE
1103 --------------------------------------------
1104 -- Call merge_template_rule_assns to merge CE rules
1105 --------------------------------------------
1106 OKC_XPRT_TMPL_RULE_ASSNS_PVT.merge_template_rule_assns (
1107 p_api_version => p_api_version ,
1108 p_init_msg_list => p_init_msg_list,
1109 p_commit => FND_API.G_FALSE,
1110 x_msg_data => x_msg_data ,
1111 x_msg_count => x_msg_count ,
1112 x_return_status => x_return_status,
1113
1114 p_template_id => p_template_id,
1115 p_parent_template_id => l_base_template_id
1116 );
1117 --------------------------------------------
1118 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1119 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1120 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1121 RAISE FND_API.G_EXC_ERROR ;
1122 END IF;
1123 --------------------------------------------
1124
1125 --------------------------------------------
1126 -- Update OKC_TMPL_DRAFT_CLAUSES
1127 --------------------------------------------
1128 UPDATE OKC_TMPL_DRAFT_CLAUSES
1129 SET template_id = l_base_template_id
1130 WHERE template_id = p_template_id;
1131
1132 -- Store the l_base_template_id in the package global variable for
1133 -- retrieval by the overloaded procedure.
1134 g_parent_template_id := l_base_template_id;
1135
1136 -- ================ Actual Procedure Code end =======================
1137
1138 -- Standard call to get message count and if count is 1, get message info.
1139 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1140 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1141 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'6100: Leaving Merge_Template_Working_Copy');
1142 END IF;
1143
1144 EXCEPTION
1145 WHEN FND_API.G_EXC_ERROR THEN
1146 ROLLBACK TO g_Merge_Template_Working_Copy;
1147 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1148 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'6200: Leaving Merge_Template_Working_Copy : OKC_API.G_EXCEPTION_ERROR Exception');
1149 END IF;
1150 x_return_status := G_RET_STS_ERROR ;
1151 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1152
1153 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1154 ROLLBACK TO g_Merge_Template_Working_Copy;
1155 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1156 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'6300: Leaving Merge_Template_Working_Copy : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
1157 END IF;
1158 x_return_status := G_RET_STS_UNEXP_ERROR ;
1159 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1160
1161 WHEN OTHERS THEN
1162 ROLLBACK TO g_Merge_Template_Working_Copy;
1163 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1164 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'6400: Leaving Merge_Template_Working_Copy because of EXCEPTION: '||sqlerrm);
1165 END IF;
1166
1167 x_return_status := G_RET_STS_UNEXP_ERROR ;
1168 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1169 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1170 END IF;
1171 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1172 END Merge_Template_Working_Copy ;
1173
1174 /*
1175 -- PROCEDURE Get_System_Variables
1176 -- Based on doc type this API will call different integrating API and will
1177 -- get values of all variables being used in Terms and Conditions of a document
1178 */
1179
1180 PROCEDURE Get_System_Variables (
1181 p_api_version IN NUMBER,
1182 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1183
1184 x_return_status OUT NOCOPY VARCHAR2,
1185 x_msg_data OUT NOCOPY VARCHAR2,
1186 x_msg_count OUT NOCOPY NUMBER,
1187
1188 p_doc_type IN VARCHAR2,
1189 p_doc_id IN NUMBER,
1190 p_only_doc_variables IN VARCHAR2 := FND_API.G_TRUE,
1191
1192 x_sys_var_value_tbl OUT NOCOPY OKC_TERMS_UTIL_GRP.sys_var_value_tbl_type
1193 ) IS
1194 l_api_version CONSTANT NUMBER := 1;
1195 l_api_name CONSTANT VARCHAR2(30) := 'Get_System_Variables';
1196 i NUMBER := 1;
1197 l_doc_class VARCHAR2(30) := '?';
1198 CURSOR var_doc_lst_crs IS
1199 SELECT distinct var.variable_code
1200 FROM okc_k_articles_b kart, okc_k_art_variables var
1201 WHERE kart.document_type=p_doc_type AND kart.document_id=p_doc_id
1202 and var.cat_id=kart.id AND variable_type='S';
1203
1204 CURSOR var_def_lst_crs IS
1205 SELECT busvar.variable_code
1206 FROM OKC_BUS_DOC_TYPES_B vo, OKC_BUS_VARIABLES_B busvar
1207 WHERE vo.document_type=p_doc_type AND busvar.contract_expert_yn='Y'
1208 AND busvar.variable_intent=vo.intent;
1209 CURSOR doc_cls_lst_crs IS
1210 SELECT document_type_class
1211 FROM okc_bus_doc_types_v
1212 WHERE document_type=p_doc_type;
1213 BEGIN
1214 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1215 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'6500: Entered Get_System_Variables');
1216 END IF;
1217 -- Standard Start of API savepoint
1218 SAVEPOINT g_Get_System_Variables;
1219 -- Standard call to check for call compatibility.
1220 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1221 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1222 END IF;
1223 -- Initialize message list if p_init_msg_list is set to TRUE.
1224 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1225 FND_MSG_PUB.initialize;
1226 END IF;
1227 -- Initialize API return status to success
1228 x_return_status := G_RET_STS_SUCCESS;
1229
1230 IF p_only_doc_variables = FND_API.G_TRUE THEN
1231 FOR cr IN var_doc_lst_crs LOOP
1232 x_sys_var_value_tbl(i).Variable_code := cr.variable_code;
1233 i := i+1;
1234 END LOOP;
1235 ELSE
1236 FOR cr IN var_def_lst_crs LOOP
1237 x_sys_var_value_tbl(i).Variable_code := cr.variable_code;
1238 i := i+1;
1239 END LOOP;
1240 END IF;
1241
1242 OPEN doc_cls_lst_crs;
1243 FETCH doc_cls_lst_crs INTO l_doc_class;
1244 CLOSE doc_cls_lst_crs;
1245
1246 IF l_doc_class in ('BSA','SO') THEN
1247 --IF l_doc_class = 'OM' THEN
1248 OKC_OM_INT_GRP.get_article_variable_values(
1249 p_api_version => p_api_version ,
1250 p_init_msg_list => p_init_msg_list,
1251 x_msg_data => x_msg_data ,
1252 x_msg_count => x_msg_count ,
1253 x_return_status => x_return_status,
1254
1255 p_doc_type => p_doc_type,
1256 p_doc_id => p_doc_id,
1257 p_sys_var_value_tbl => x_sys_var_value_tbl);
1258
1259 --------------------------------------------
1260 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1261 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1262 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1263 RAISE FND_API.G_EXC_ERROR ;
1264 END IF;
1265 --------------------------------------------
1266 ELSIF l_doc_class = 'PO' THEN
1267 OKC_PO_INT_GRP.get_article_variable_values(
1268 p_api_version => p_api_version ,
1269 p_init_msg_list => p_init_msg_list,
1270 x_msg_data => x_msg_data ,
1271 x_msg_count => x_msg_count ,
1272 x_return_status => x_return_status,
1273
1274 p_doc_id => p_doc_id,
1275 p_sys_var_value_tbl => x_sys_var_value_tbl);
1276 --------------------------------------------
1277 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1278 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1279 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1280 RAISE FND_API.G_EXC_ERROR ;
1281 END IF;
1282 --------------------------------------------
1283 ELSIF l_doc_class = 'SOURCING' THEN
1284
1285 OKC_PON_INT_GRP.get_article_variable_values(
1286 p_api_version => p_api_version ,
1287 p_init_msg_list => p_init_msg_list,
1288 x_msg_data => x_msg_data ,
1289 x_msg_count => x_msg_count ,
1290 x_return_status => x_return_status,
1291
1292 p_doc_type => p_doc_type,
1293 p_doc_id => p_doc_id,
1294 p_sys_var_value_tbl => x_sys_var_value_tbl);
1295 --------------------------------------------
1296 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1297 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1298 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1299 RAISE FND_API.G_EXC_ERROR ;
1300 END IF;
1301 --------------------------------------------
1302 ELSIF l_doc_class = 'QUOTE' THEN
1303
1304 OKC_ASO_INT_GRP.get_article_variable_values(
1305 p_api_version => p_api_version ,
1306 p_init_msg_list => p_init_msg_list,
1307 x_msg_data => x_msg_data ,
1308 x_msg_count => x_msg_count ,
1309 x_return_status => x_return_status,
1310 -- p_doc_type => p_doc_type,
1311 p_doc_id => p_doc_id,
1312 p_sys_var_value_tbl => x_sys_var_value_tbl);
1313
1314 --------------------------------------------
1315 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1316 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1317 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1318 RAISE FND_API.G_EXC_ERROR ;
1319 END IF;
1320 --------------------------------------------
1321 --kkolukul:bug 6924032 Modified for Repository Enhancement
1322 ELSIF l_doc_class = 'REPOSITORY' THEN
1323
1324 OKC_XPRT_REP_INT_PVT.get_clause_variable_values(
1325 p_api_version => p_api_version,
1326 p_init_msg_list => p_init_msg_list,
1327 p_doc_type => p_doc_type,
1328 p_doc_id => p_doc_id,
1329 p_sys_var_value_tbl => x_sys_var_value_tbl,
1330 x_return_status => x_return_status,
1331 x_msg_data => x_msg_data,
1332 x_msg_count => x_msg_count );
1333
1334 --------------------------------------------
1335 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1336 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1337 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1338 RAISE FND_API.G_EXC_ERROR ;
1339 END IF;
1340 --------------------------------------------
1341
1342 ELSE
1343 NULL;
1344 END IF;
1345
1346 -- Standard call to get message count and if count is 1, get message info.
1347 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1348 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1349 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'6600: Leaving Get_System_Variables');
1350 END IF;
1351 EXCEPTION
1352 WHEN FND_API.G_EXC_ERROR THEN
1353 ROLLBACK TO g_Get_System_Variables;
1354 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1355 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'6700: Leaving Get_System_Variables : OKC_API.G_EXCEPTION_ERROR Exception');
1356 END IF;
1357 x_return_status := G_RET_STS_ERROR ;
1358 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1359
1360 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1361 ROLLBACK TO g_Get_System_Variables;
1362 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1363 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'6800: Leaving Get_System_Variables : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
1364 END IF;
1365 x_return_status := G_RET_STS_UNEXP_ERROR ;
1366 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1367
1368 WHEN OTHERS THEN
1369 ROLLBACK TO g_Get_System_Variables;
1370 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1371 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'6900: Leaving Get_System_Variables because of EXCEPTION: '||sqlerrm);
1372 END IF;
1373
1374 x_return_status := G_RET_STS_UNEXP_ERROR ;
1375 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1376 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1377 END IF;
1378 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1379 END Get_System_Variables ;
1380
1381 /*
1382 -- PROCEDURE Substitute_Var_Value_Globally
1383 -- to be called from T and C authoring UI to substitute variable value of any value
1384 -- for every occurance of variable on document
1385 */
1386 PROCEDURE Substitute_Var_Value_Globally (
1387 p_api_version IN NUMBER,
1388 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1389
1390 x_return_status OUT NOCOPY VARCHAR2,
1391 x_msg_data OUT NOCOPY VARCHAR2,
1392 x_msg_count OUT NOCOPY NUMBER,
1393
1394 p_doc_type IN VARCHAR2,
1395 p_doc_id IN NUMBER,
1396 p_variable_code IN VARCHAR2,
1397 p_variable_value IN VARCHAR2,
1398 p_variable_value_id IN VARCHAR2,
1399 p_mode IN VARCHAR2,
1400 p_validate_commit IN VARCHAR2 := FND_API.G_TRUE,
1401 p_validation_string IN VARCHAR2 := NULL
1402 ) IS
1403 l_api_version CONSTANT NUMBER := 1;
1404 l_api_name CONSTANT VARCHAR2(30) := 'Substitute_Var_Value_Globally';
1405 l_dummy VARCHAR2(10);
1406 CURSOR var_lst_crs IS
1407 SELECT cat_id, object_version_number
1408 FROM okc_k_art_variables
1409 WHERE variable_code=p_variable_code
1410 AND cat_id IN (SELECT id FROM okc_k_articles_b
1411 WHERE document_type=p_doc_type AND document_id=p_doc_id);
1412 CURSOR art_lst_crs IS
1413 SELECT id, object_version_number
1414 FROM okc_k_articles_b a
1415 WHERE document_type=p_doc_type AND document_id=p_doc_id
1416 and EXISTS (SELECT '!' FROM okc_k_art_variables v
1417 WHERE v.variable_code=p_variable_code AND v.cat_id = a.cat_id );
1418 BEGIN
1419 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1420 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7000: Entered Substitute_Var_Value_Globally');
1421 END IF;
1422 -- Standard Start of API savepoint
1423 SAVEPOINT g_Subst_Var_Value_Globally;
1424 -- Standard call to check for call compatibility.
1425 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1426 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1427 END IF;
1428 -- Initialize message list if p_init_msg_list is set to TRUE.
1429 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1430 FND_MSG_PUB.initialize;
1431 END IF;
1432 -- Initialize API return status to success
1433 x_return_status := G_RET_STS_SUCCESS;
1434
1435 IF p_validate_commit = G_TRUE THEN
1436 l_dummy := OKC_TERMS_UTIL_GRP.Ok_To_Commit(
1437 p_api_version => p_api_version ,
1438 p_init_msg_list => p_init_msg_list,
1439 x_msg_data => x_msg_data ,
1440 x_msg_count => x_msg_count ,
1441 x_return_status => x_return_status,
1442
1443 p_validation_string => p_validation_string,
1444 -- p_tmpl_change => 'D',
1445
1446 p_doc_id => p_doc_id,
1447 p_doc_type => p_doc_type
1448 );
1449 --------------------------------------------
1450 IF (l_dummy = 'N' OR x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1451 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1452 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1453 RAISE FND_API.G_EXC_ERROR ;
1454 END IF;
1455 --------------------------------------------
1456 END IF;
1457
1458 FOR cr IN var_lst_crs LOOP
1459 OKC_K_ART_VARIABLES_PVT.update_row(
1460 x_return_status => x_return_status,
1461 p_cat_id => cr.cat_id,
1462 p_variable_code => p_variable_code,
1463 p_variable_type => NULL,
1464 p_external_yn => NULL,
1465 p_variable_value_id => p_variable_value_id,
1466 p_variable_value => p_variable_value,
1467 p_attribute_value_set_id => NULL,
1468 p_object_version_number => cr.object_version_number
1469 );
1470 --------------------------------------------
1471 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1472 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1473 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1474 RAISE FND_API.G_EXC_ERROR ;
1475 END IF;
1476 --------------------------------------------
1477 END LOOP;
1478
1479 IF p_mode = 'AMEND' THEN
1480 FOR cr IN art_lst_crs LOOP
1481 OKC_K_ARTICLES_PVT.update_row(
1482 x_return_status => x_return_status,
1483 p_id => cr.id,
1484 p_sav_sae_id => NULL,
1485 p_document_type => NULL,
1486 p_document_id => NULL,
1487 p_source_flag => NULL,
1488 p_mandatory_yn => NULL,
1489 p_scn_id => NULL,
1490 p_label => NULL,
1491 p_amendment_description => NULL,
1492 p_amendment_operation_code => G_AMEND_CODE_UPDATED,
1493 p_article_version_id => NULL,
1494 p_change_nonstd_yn => NULL,
1495 p_orig_system_reference_code => NULL,
1496 p_orig_system_reference_id1 => NULL,
1497 p_orig_system_reference_id2 => NULL,
1498 p_display_sequence => NULL,
1499 p_print_text_yn => NULL,
1500 p_object_version_number => cr.object_version_number
1501 );
1502 --------------------------------------------
1503 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1504 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1505 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1506 RAISE FND_API.G_EXC_ERROR ;
1507 END IF;
1508 --------------------------------------------
1509 END LOOP;
1510 END IF;
1511
1512 -- Standard call to get message count and if count is 1, get message info.
1513 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1514 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1515 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7100: Leaving Substitute_Var_Value_Globally');
1516 END IF;
1517 EXCEPTION
1518 WHEN FND_API.G_EXC_ERROR THEN
1519 ROLLBACK TO g_Subst_Var_Value_Globally;
1520 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1521 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7200: Leaving Substitute_Var_Value_Globally : OKC_API.G_EXCEPTION_ERROR Exception');
1522 END IF;
1523 x_return_status := G_RET_STS_ERROR ;
1524 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1525
1526 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1527 ROLLBACK TO g_Subst_Var_Value_Globally;
1528 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1529 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7300: Leaving Substitute_Var_Value_Globally : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
1530 END IF;
1531 x_return_status := G_RET_STS_UNEXP_ERROR ;
1532 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1533
1534 WHEN OTHERS THEN
1535 ROLLBACK TO g_Subst_Var_Value_Globally;
1536 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1537 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7400: Leaving Substitute_Var_Value_Globally because of EXCEPTION: '||sqlerrm);
1538 END IF;
1539
1540 x_return_status := G_RET_STS_UNEXP_ERROR ;
1541 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1542 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1543 END IF;
1544 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1545 END Substitute_Var_Value_Globally ;
1546 /*
1547 -- PROCEDURE Create_Unassigned_Section
1548 -- creating un-assigned sections in a document
1549 */
1550 PROCEDURE Create_Unassigned_Section (
1551 p_api_version IN NUMBER,
1552 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1553 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1554
1555 x_return_status OUT NOCOPY VARCHAR2,
1556 x_msg_data OUT NOCOPY VARCHAR2,
1557 x_msg_count OUT NOCOPY NUMBER,
1558
1559 p_doc_type IN VARCHAR2,
1560 p_doc_id IN NUMBER,
1561
1562 x_scn_id OUT NOCOPY NUMBER
1563 ) IS
1564 l_api_version CONSTANT NUMBER := 1;
1565 l_api_name CONSTANT VARCHAR2(30) := 'Create_Unassigned_Section';
1566 l_meaning VARCHAR2(100);
1567 l_sequence NUMBER;
1568
1569 Cursor l_get_max_seq_csr IS
1570 SELECT nvl(max(section_sequence),0)+10
1571 FROM OKC_SECTIONS_B
1572 WHERE DOCUMENT_TYPE= p_doc_type
1573 AND DOCUMENT_ID = p_doc_id
1574 AND SCN_ID IS NULL;
1575
1576 BEGIN
1577 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1578 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7500: Entered Create_Unassigned_Section');
1579 END IF;
1580 -- Standard Start of API savepoint
1581 SAVEPOINT g_Create_Unassigned_Section;
1582 -- Standard call to check for call compatibility.
1583 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1584 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1585 END IF;
1586 -- Initialize message list if p_init_msg_list is set to TRUE.
1587 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1588 FND_MSG_PUB.initialize;
1589 END IF;
1590 -- Initialize API return status to success
1591 x_return_status := G_RET_STS_SUCCESS;
1592
1593 --------------------------------------------
1594 -- Call Simple API of okc_sections_b with following input
1595 -- doc_type=p_doc_type, doc_id=p_doc_id, scn_code=G_UNASSIGNED_SECTION_CODE,
1596 -- heading = < get meaning of G_UNASSIGNED_SECTION_CODE by quering fnd_lookups>.
1597 -- Set x_scn_id to id returned by simpel API.
1598 --------------------------------------------
1599 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1600 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7600: Calling Simple API to Create a Section');
1601 END IF;
1602 --------------------------------------------
1603 l_meaning := Okc_Util.Decode_Lookup('OKC_ARTICLE_SECTION',G_UNASSIGNED_SECTION_CODE);
1604
1605 --Bug 3669528 Unassigned section should always come at the bottom, so use a 'high' value
1606 /*
1607 OPEN l_get_max_seq_csr;
1608 FETCH l_get_max_seq_csr INTO l_sequence;
1609 CLOSE l_get_max_seq_csr;
1610 */
1611 l_sequence:= 9999;
1612
1613 OKC_TERMS_SECTIONS_PVT.insert_row(
1614 x_return_status => x_return_status,
1615 p_id => NULL,
1616 p_section_sequence => l_sequence,
1617 p_label => NULL,
1618 p_scn_id => NULL,
1619 p_heading => l_meaning,
1620 p_description => l_meaning,
1621 p_document_type => p_doc_type,
1622 p_document_id => p_doc_id,
1623 p_scn_code => G_UNASSIGNED_SECTION_CODE,
1624 p_amendment_description => NULL,
1625 p_amendment_operation_code => NULL,
1626 p_orig_system_reference_code => NULL,
1627 p_orig_system_reference_id1 => NULL,
1628 p_orig_system_reference_id2 => NULL,
1629 p_print_yn => 'N',
1630 x_id => x_scn_id
1631 );
1632 --------------------------------------------
1633 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1634 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1635 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1636 RAISE FND_API.G_EXC_ERROR ;
1637 END IF;
1638 --------------------------------------------
1639
1640 IF FND_API.To_Boolean( p_commit ) THEN
1641 COMMIT WORK;
1642 END IF;
1643 -- Standard call to get message count and if count is 1, get message info.
1644 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1645 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1646 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7700: Leaving Create_Unassigned_Section');
1647 END IF;
1648 EXCEPTION
1649 WHEN FND_API.G_EXC_ERROR THEN
1650 ROLLBACK TO g_Create_Unassigned_Section;
1651 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1652 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7800: Leaving Create_Unassigned_Section : OKC_API.G_EXCEPTION_ERROR Exception');
1653 END IF;
1654
1655 IF l_get_max_seq_csr%ISOPEN THEN
1656 CLOSE l_get_max_seq_csr;
1657 END IF;
1658
1659 x_return_status := G_RET_STS_ERROR ;
1660 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1661
1662 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1663 ROLLBACK TO g_Create_Unassigned_Section;
1664 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1665 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7900: Leaving Create_Unassigned_Section : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
1666 END IF;
1667
1668 IF l_get_max_seq_csr%ISOPEN THEN
1669 CLOSE l_get_max_seq_csr;
1670 END IF;
1671
1672 x_return_status := G_RET_STS_UNEXP_ERROR ;
1673 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1674
1675 WHEN OTHERS THEN
1676 ROLLBACK TO g_Create_Unassigned_Section;
1677 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1678 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'8000: Leaving Create_Unassigned_Section because of EXCEPTION: '||sqlerrm);
1679 END IF;
1680
1681 IF l_get_max_seq_csr%ISOPEN THEN
1682 CLOSE l_get_max_seq_csr;
1683 END IF;
1684
1685 x_return_status := G_RET_STS_UNEXP_ERROR ;
1686 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1687 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1688 END IF;
1689 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1690 END Create_Unassigned_Section ;
1691 /*
1692 -- To Check if document type is valid
1693 */
1694 FUNCTION is_doc_type_valid(
1695 p_doc_type IN VARCHAR2,
1696 x_return_status OUT NOCOPY VARCHAR2
1697 ) RETURN VARCHAR2 IS
1698 l_dummy VARCHAR2(1) := '?';
1699 l_return VARCHAR2(1) := FND_API.G_TRUE;
1700 cursor l_check_doc_type_crs IS
1701 SELECT 'X' FROM OKC_BUS_DOC_TYPES_B
1702 WHERE document_type=p_doc_type;
1703 BEGIN
1704
1705 OPEN l_check_doc_type_crs;
1706 FETCH l_check_doc_type_crs INTO l_dummy;
1707 IF l_check_doc_type_crs%NOTFOUND THEN
1708 return FND_API.G_FALSE;
1709 ELSE
1710 return FnD_API.G_TRUE;
1711 END IF;
1712
1713 CLOSE l_check_doc_type_crs;
1714
1715 return l_return;
1716 x_return_status := G_RET_STS_SUCCESS ;
1717
1718 EXCEPTION
1719 WHEN OTHERS THEN
1720
1721 IF l_check_doc_type_crs%ISOPEN THEN
1722 CLOSE l_check_doc_type_crs;
1723 END IF;
1724 return FND_API.G_FALSE;
1725 x_return_status := G_RET_STS_UNEXP_ERROR ;
1726
1727 END;
1728 /*
1729 -- FUNCTION Get_Message
1730 -- to be used to put tokens in messages code and return translated messaged.
1731 -- It will be mainly used by QA API. |
1732 */
1733 FUNCTION Get_Message (
1734 p_app_name IN VARCHAR2,
1735 p_msg_name IN VARCHAR2,
1736 p_token1 IN VARCHAR2,
1737 p_token1_value IN VARCHAR2,
1738 p_token2 IN VARCHAR2,
1739 p_token2_value IN VARCHAR2,
1740 p_token3 IN VARCHAR2,
1741 p_token3_value IN VARCHAR2,
1742 p_token4 IN VARCHAR2,
1743 p_token4_value IN VARCHAR2,
1744 p_token5 IN VARCHAR2,
1745 p_token5_value IN VARCHAR2,
1746 p_token6 IN VARCHAR2,
1747 p_token6_value IN VARCHAR2,
1748 p_token7 IN VARCHAR2,
1749 p_token7_value IN VARCHAR2,
1750 p_token8 IN VARCHAR2,
1751 p_token8_value IN VARCHAR2,
1752 p_token9 IN VARCHAR2,
1753 p_token9_value IN VARCHAR2,
1754 p_token10 IN VARCHAR2,
1755 p_token10_value IN VARCHAR2
1756 ) RETURN VARCHAR2 IS
1757 BEGIN
1758 Fnd_Message.Set_Name( p_app_name, p_msg_name );
1759 IF (p_token1 IS NOT NULL) AND (p_token1_value IS NOT NULL) THEN
1760 Fnd_Message.Set_Token( token => p_token1, value => p_token1_value);
1761 END IF;
1762 IF (p_token2 IS NOT NULL) AND (p_token2_value IS NOT NULL) THEN
1763 Fnd_Message.Set_Token( token => p_token2, value => p_token2_value);
1764 END IF;
1765 IF (p_token3 IS NOT NULL) AND (p_token3_value IS NOT NULL) THEN
1766 Fnd_Message.Set_Token( token => p_token3, value => p_token3_value);
1767 END IF;
1768 IF (p_token4 IS NOT NULL) AND (p_token4_value IS NOT NULL) THEN
1769 Fnd_Message.Set_Token( token => p_token4, value => p_token4_value);
1770 END IF;
1771 IF (p_token5 IS NOT NULL) AND (p_token5_value IS NOT NULL) THEN
1772 Fnd_Message.Set_Token( token => p_token5, value => p_token5_value);
1773 END IF;
1774 IF (p_token6 IS NOT NULL) AND (p_token6_value IS NOT NULL) THEN
1775 Fnd_Message.Set_Token( token => p_token6, value => p_token6_value);
1776 END IF;
1777 IF (p_token7 IS NOT NULL) AND (p_token7_value IS NOT NULL) THEN
1778 Fnd_Message.Set_Token( token => p_token7, value => p_token7_value);
1779 END IF;
1780 IF (p_token8 IS NOT NULL) AND (p_token8_value IS NOT NULL) THEN
1781 Fnd_Message.Set_Token( token => p_token8, value => p_token8_value);
1782 END IF;
1783 IF (p_token9 IS NOT NULL) AND (p_token9_value IS NOT NULL) THEN
1784 Fnd_Message.Set_Token( token => p_token9, value => p_token9_value);
1785 END IF;
1786 IF (p_token10 IS NOT NULL) AND (p_token10_value IS NOT NULL) THEN
1787 Fnd_Message.Set_Token( token => p_token10, value => p_token10_value);
1788 END IF;
1789 RETURN Fnd_Message.Get;
1790
1791 END Get_Message;
1792
1793 /* Modified Cursor for Bug 4956969 */
1794 Function Get_latest_art_version(p_article_id IN NUMBER,
1795 p_org_id IN NUMBER,
1796 p_eff_date IN DATE)
1797 RETURN Varchar2 IS
1798 l_display_name okc_article_versions.display_name%TYPE;
1799 l_global_org_id number;
1800 CURSOR ver_csr IS
1801 SELECT nvl(ver.display_name,art.article_title) name
1802 FROM okc_articles_all art,
1803 okc_article_versions ver
1804 WHERE art.org_id = p_org_id
1805 AND art.article_id = p_article_id
1806 AND art.article_id = ver.article_id
1807 AND ver.start_date <= nvl(p_eff_date,sysdate)
1808 AND ver.start_date = (select max(start_date)
1809 from okc_article_versions ver1
1810 where ver1.article_id = ver.article_id
1811 and ver1.start_date <= nvl(p_eff_date,sysdate)
1812 and ver1.article_status = ver.article_status)
1813 AND (ver.article_status = 'APPROVED' OR
1814 not exists (select 1
1815 from okc_article_versions ver2
1816 where ver2.article_id = art.article_id
1817 and ver2.start_date <= nvl(p_eff_date,sysdate)
1818 and ver2.article_status = 'APPROVED'));
1819
1820 BEGIN
1821 Open ver_csr;
1822 Fetch ver_csr Into l_display_name;
1823 Close ver_csr;
1824 Return l_display_name;
1825 End Get_latest_art_version;
1826
1827 /* Modified Cursor for Bug 4956969 */
1828 Function Get_latest_tmpl_art_version_id(p_article_id IN NUMBER,
1829 p_eff_date IN DATE)
1830 RETURN NUMBER IS
1831 l_article_version_id okc_article_versions.article_version_id%TYPE;
1832 CURSOR ver_csr IS
1833 SELECT ver.article_version_id
1834 FROM okc_articles_all art,
1835 okc_article_versions ver
1836 WHERE art.article_id = p_article_id
1837 AND art.article_id = ver.article_id
1838 AND ver.start_date <= nvl(p_eff_date,sysdate)
1839 AND ver.start_date = (select max(start_date)
1840 from okc_article_versions ver1
1841 where ver1.article_id = ver.article_id
1842 and ver1.start_date <= nvl(p_eff_date,sysdate)
1843 and ver1.article_status = ver.article_status)
1844 AND (ver.article_status = 'APPROVED' OR
1845 not exists (select 1
1846 from okc_article_versions ver2
1847 where ver2.article_id = art.article_id
1848 and ver2.start_date <= nvl(p_eff_date,sysdate)
1849 and ver2.article_status = 'APPROVED'));
1850 BEGIN
1851 Open ver_csr;
1852 Fetch ver_csr Into l_article_version_id;
1853 Close ver_csr;
1854 Return l_article_version_id;
1855 End Get_latest_tmpl_art_version_id;
1856
1857
1858 /*********************************
1859 -- FUNCTION Get_alternate_yn
1860 --
1861 *********************************/
1862 Function Get_alternate_yn(p_article_id IN NUMBER,
1863 p_org_id IN NUMBER)
1864 RETURN Varchar2 IS
1865
1866 l_alternate_yn varchar(1) := 'N';
1867 l_global_org_id number;
1868
1869 CURSOR alt_csr IS
1870 SELECT 1
1871 FROM dual
1872 WHERE exists (select 1
1873 from OKC_ARTICLE_RELATNS_ALL
1874 where org_id = p_org_id
1875 and source_article_id = p_article_id
1876 and relationship_type = 'ALTERNATE');
1877 BEGIN
1878 Open alt_csr;
1879 Fetch alt_csr Into l_alternate_yn;
1880 if alt_csr%found then
1881 l_alternate_yn := 'Y';
1882 else
1883 l_alternate_yn := 'N';
1884 end if;
1885 Close alt_csr;
1886 Return l_alternate_yn;
1887
1888 End Get_alternate_yn;
1889
1890
1891 /*********************************
1892 -- FUNCTION Tmpl_Intent_Editable
1893 --
1894 *********************************/
1895 Function Tmpl_Intent_Editable(p_template_id IN NUMBER)
1896 RETURN Varchar2 IS
1897
1898 l_editable_yn varchar(1) := 'N';
1899 l_deliverables_exist varchar2(250);
1900 x_return_status varchar2(150);
1901 x_msg_data varchar2(2000);
1902 x_msg_count number;
1903
1904 CURSOR editable_csr IS
1905 SELECT 1
1906 FROM okc_terms_templates_all
1907 WHERE template_id = p_template_id
1908 AND working_copy_flag = 'Y'
1909 UNION ALL
1910 SELECT 1
1911 FROM okc_allowed_tmpl_usages
1912 WHERE template_id = p_template_id
1913 UNION ALL
1914 SELECT 1
1915 FROM okc_k_articles_b
1916 WHERE document_type = 'TEMPLATE'
1917 AND document_id = p_template_id;
1918
1919 BEGIN
1920 Open editable_csr;
1921 Fetch editable_csr Into l_editable_yn;
1922 if editable_csr%found then
1923 l_editable_yn := 'N';
1924 else
1925 l_editable_yn := 'Y';
1926 end if;
1927 Close editable_csr;
1928 IF l_editable_yn = 'Y' THEN
1929 l_deliverables_exist := okc_terms_util_grp.Is_Deliverable_Exist(
1930 p_api_version => 1,
1931 p_init_msg_list => FND_API.G_FALSE,
1932 x_return_status => x_return_status,
1933 x_msg_data => x_msg_data,
1934 x_msg_count => x_msg_count,
1935 p_doc_type => 'TEMPLATE',
1936 p_doc_id => p_template_id);
1937 IF UPPER(nvl(l_deliverables_exist,'NONE')) <> 'NONE' THEN
1938 l_editable_yn := 'N';
1939 END IF;
1940 END IF;
1941 Return l_editable_yn;
1942
1943 End Tmpl_Intent_Editable;
1944
1945
1946
1947 /*********************************
1948 -- FUNCTION Has_Alternates
1949 --
1950 *********************************/
1951 Function Has_Alternates(p_article_id IN NUMBER,
1952 p_eff_date IN DATE,
1953 p_document_type IN VARCHAR2)
1954 RETURN Varchar2 IS
1955
1956 l_alternate_yn varchar(1) := 'N';
1957
1958 CURSOR alt_csr IS
1959 SELECT 1
1960 FROM okc_article_relatns_all reln,
1961 okc_article_versions ver
1962 WHERE reln.source_article_id = p_article_id
1963 AND reln.relationship_type = 'ALTERNATE'
1964 AND reln.target_article_id = ver.article_id
1965 AND NVL(p_eff_date,SYSDATE) BETWEEN ver.start_date AND NVL(ver.end_date, nvl(p_eff_date,SYSDATE))
1966 AND ver.article_status = 'APPROVED'
1967 AND reln.org_id = mo_global.get_current_org_id()
1968 AND ( ver.provision_yn = 'N' OR
1969 ( p_document_type IN (select document_type
1970 from okc_bus_doc_types_b
1971 where provision_allowed_yn = 'Y'
1972 )
1973 )
1974 );
1975
1976 BEGIN
1977 Open alt_csr;
1978 Fetch alt_csr Into l_alternate_yn;
1979 if alt_csr%found then
1980 l_alternate_yn := 'Y';
1981 else
1982 l_alternate_yn := 'N';
1983 end if;
1984 Close alt_csr;
1985 Return l_alternate_yn;
1986
1987 End Has_alternates;
1988
1989 FUNCTION Has_Alternates(p_article_id IN NUMBER,
1990 p_start_date IN DATE,
1991 p_end_date IN DATE,
1992 p_org_id IN NUMBER,
1993 p_document_type IN VARCHAR2)
1994 RETURN Varchar2 IS
1995
1996 l_effective_date DATE;
1997 l_alternate_yn varchar(1) := 'N';
1998
1999 CURSOR alt_csr(cp_effective_date DATE) IS
2000 SELECT 1
2001 FROM okc_article_relatns_all reln,
2002 okc_article_versions ver,
2003 okc_articles_all art
2004 WHERE reln.source_article_id = p_article_id
2005 AND reln.relationship_type = 'ALTERNATE'
2006 AND reln.target_article_id = art.article_id
2007 AND art.article_id = ver.article_id
2008 AND cp_effective_date BETWEEN ver.start_date AND NVL(ver.end_date, cp_effective_date)
2009 AND ( ( p_document_type = 'TEMPLATE') OR ( ver.article_status IN ('APPROVED','ON_HOLD')) )
2010 AND reln.org_id = p_org_id
2011 AND ( (p_org_id = art.org_id
2012 )
2013 OR
2014 ( exists ( SELECT 1
2015 FROM okc_article_ADOPTIONS ADP
2016 WHERE adp.global_article_version_id = ver.article_version_id
2017 AND adp.adoption_type = 'ADOPTED'
2018 AND adp.local_org_id = p_org_id
2019 AND adp.adoption_status IN ( 'APPROVED', 'ON_HOLD')
2020 )
2021 )
2022 )
2023 AND ( ver.provision_yn = 'N' OR
2024 ( p_document_type
2025 IN ( SELECT document_type
2026 FROM okc_bus_doc_types_b
2027 WHERE provision_allowed_yn = 'Y'
2028 )
2029 )
2030 );
2031
2032 BEGIN
2033 IF p_document_type = 'TEMPLATE' THEN
2034 IF ((p_end_date IS NULL) OR (TRUNC(p_end_date) >= TRUNC(SYSDATE))) THEN
2035 IF TRUNC(p_start_date) < TRUNC(SYSDATE) THEN
2036 l_effective_date := TRUNC(SYSDATE);
2037 ELSE
2038 l_effective_date := TRUNC(p_start_date);
2039 END IF;
2040 ELSIF TRUNC(p_end_date) < TRUNC(SYSDATE) THEN
2041 l_effective_date := TRUNC(p_end_date);
2042 END IF;
2043 ELSE
2044 l_effective_date := NVL(p_start_date,TRUNC(SYSDATE));
2045 END IF;
2046
2047 OPEN alt_csr(l_effective_date);
2048 FETCH alt_csr Into l_alternate_yn;
2049 IF alt_csr%found then
2050 l_alternate_yn := 'Y';
2051 ELSE
2052 l_alternate_yn := 'N';
2053 END IF;
2054 CLOSE alt_csr;
2055 RETURN l_alternate_yn;
2056
2057 END Has_alternates;
2058
2059 FUNCTION Has_amendments(p_document_id IN NUMBER,
2060 p_document_type IN VARCHAR2,
2061 p_document_version IN NUMBER)
2062 RETURN Varchar2 IS
2063 l_amendment VARCHAR2(150);
2064 l_return_status VARCHAR2(150);
2065 l_msg_data VARCHAR2(2000);
2066 l_msg_count NUMBER;
2067
2068 CURSOR l_art_amendment_csr IS
2069 SELECT 1
2070 FROM okc_k_articles_b kart,
2071 okc_template_usages usg
2072 WHERE usg.document_type = p_document_type
2073 AND usg.document_id = p_document_id
2074 AND usg.document_type = kart.document_type
2075 AND usg.document_id = kart.document_id
2076 AND NVL(usg.contract_source_code,'STRUCTURED') = 'STRUCTURED'
2077 AND (kart.amendment_operation_code IS NOT NULL OR
2078 kart.summary_amend_operation_code IS NOT NULL)
2079 UNION ALL
2080 SELECT 1
2081 FROM okc_sections_b scn,
2082 okc_template_usages usg
2083 WHERE usg.document_type = p_document_type
2084 AND usg.document_id = p_document_id
2085 AND NVL(usg.contract_source_code,'STRUCTURED') = 'STRUCTURED'
2086 AND usg.document_type = scn.document_type
2087 AND usg.document_id = scn.document_id
2088 AND (scn.amendment_operation_code IS NOT NULL OR
2089 scn.summary_amend_operation_code IS NOT NULL)
2090 UNION ALL
2091 SELECT 1
2092 FROM okc_contract_docs kdoc,
2093 okc_template_usages usg
2094 WHERE usg.document_type = p_document_type
2095 AND usg.document_id = p_document_id
2096 AND usg.document_type = kdoc.business_document_type
2097 AND usg.document_id = kdoc.business_document_id
2098 AND NVL(usg.contract_source_code,'STRUCTURED') = 'ATTACHED'
2099 AND kdoc.primary_contract_doc_flag = 'Y'
2100 AND kdoc.delete_flag = 'Y'
2101 AND kdoc.effective_from_version = p_document_version
2102 AND ((NVL(p_document_version, -99) = -99)
2103 OR
2104 (
2105 exists (SELECT 1
2106 FROM
2107 OKC_TEMPLATE_USAGES_H usgH
2108 WHERE
2109 usgH.document_type = p_document_type
2110 AND usgH.document_id = p_document_id
2111 AND usgH.major_version < p_document_version
2112 )
2113 )
2114 )
2115 UNION ALL
2116 SELECT 1
2117 FROM okc_contract_docs kdoc,
2118 okc_template_usages usg
2119 WHERE usg.document_type = p_document_type
2120 AND usg.document_id = p_document_id
2121 AND usg.document_type = kdoc.business_document_type
2122 AND usg.document_id = kdoc.business_document_id
2123 AND NVL(usg.contract_source_code,'STRUCTURED') = 'ATTACHED'
2124 AND kdoc.primary_contract_doc_flag = 'Y'
2125 AND kdoc.business_document_type = kdoc.effective_from_type
2126 AND kdoc.business_document_id = kdoc.effective_from_id
2127 AND kdoc.business_document_version = kdoc.effective_from_version
2128 AND ((kdoc.effective_from_version > 0 and
2129 kdoc.effective_from_version = p_document_version)OR
2130 kdoc.effective_from_version = -99)
2131 AND ((NVL(p_document_version, -99) = -99)
2132 OR
2133 (
2134 exists (SELECT 1
2135 FROM
2136 OKC_TEMPLATE_USAGES_H usgH
2137 WHERE
2138 usgH.document_type = p_document_type
2139 AND usgH.document_id = p_document_id
2140 AND usgH.major_version < p_document_version
2141 )
2142 )
2143 )
2144 ;
2145 BEGIN
2146 OPEN l_art_amendment_csr;
2147 FETCH l_art_amendment_csr INTO l_amendment;
2148 IF l_art_amendment_csr%FOUND THEN
2149 RETURN 'Y';
2150 END IF;
2151 CLOSE l_art_amendment_csr;
2152
2153
2154 l_amendment :=
2155 OKC_DELIVERABLE_PROCESS_PVT.deliverables_amended(
2156 p_api_version => 1,
2157 p_init_msg_list => FND_API.G_FALSE,
2158
2159 x_return_status => l_return_status,
2160 x_msg_data => l_msg_data,
2161 x_msg_count => l_msg_count,
2162
2163 p_doctype => p_document_type,
2164 p_docid => p_document_id);
2165
2166
2167 IF NVL(l_amendment,'NONE') = 'NONE' THEN
2168 RETURN 'N';
2169 ELSE
2170 RETURN 'Y';
2171 END IF;
2172 EXCEPTION
2173 WHEN OTHERS THEN
2174 RETURN 'N';
2175 END Has_amendments;
2176
2177
2178
2179
2180
2181 /*********************************
2182 -- FUNCTION get_summary_amend_code
2183 --
2184 *********************************/
2185 FUNCTION get_summary_amend_code(
2186 p_existing_summary_code IN VARCHAR2,
2187 p_existing_operation_code IN VARCHAR2, -- we don't need the parameter, but keep it for compatibility
2188 p_amend_operation_code IN VARCHAR2
2189 ) return VARCHAR2 IS
2190 l_new_summary_code OKC_K_ARTICLES_B.SUMMARY_AMEND_OPERATION_CODE%TYPE;
2191
2192 BEGIN
2193
2194 IF p_existing_summary_code='ADDED' AND p_amend_operation_code ='DELETED' THEN
2195 l_new_summary_code:=FND_API.G_MISS_CHAR; -- Summary should be set to NULL
2196 ELSIF p_existing_summary_code='ADDED' AND p_amend_operation_code = 'UPDATED' THEN
2197 l_new_summary_code:=p_existing_summary_code;
2198 ELSIF p_existing_summary_code='DELETED' AND p_amend_operation_code = 'UPDATED' THEN
2199 l_new_summary_code:=p_existing_summary_code;
2200 ELSIF p_existing_summary_code='DELETED' AND p_amend_operation_code = 'ADDED' THEN
2201 l_new_summary_code:=FND_API.G_MISS_CHAR;
2202 ELSE
2203 l_new_summary_code := p_amend_operation_code;
2204 END IF;
2205
2206 return l_new_summary_code;
2207
2208 END get_summary_amend_code;
2209
2210 /*********************************
2211 -- FUNCTION get_actual_summary_amend_code
2212 -- Wraps get_summary_amend_code and replaces G_MISS_CHAR with null
2213 *********************************/
2214 FUNCTION get_actual_summary_amend_code(
2215 p_existing_summary_code IN VARCHAR2,
2216 p_existing_operation_code IN VARCHAR2,
2217 p_amend_operation_code IN VARCHAR2
2218 ) return VARCHAR2 IS
2219 l_new_summary_code OKC_K_ARTICLES_B.SUMMARY_AMEND_OPERATION_CODE%TYPE;
2220
2221 BEGIN
2222 l_new_summary_code:=get_summary_amend_code(p_existing_summary_code=>p_existing_summary_code,
2223 p_existing_operation_code=>p_existing_operation_code,
2224 p_amend_operation_code=>p_amend_operation_code);
2225
2226 if l_new_summary_code = FND_API.G_MISS_CHAR then
2227 return NULL;
2228 end if;
2229
2230 return l_new_summary_code;
2231 END get_actual_summary_amend_code;
2232
2233
2234
2235 /*********************************
2236 -- FUNCTION get_article_version_number
2237 --
2238 *********************************/
2239 FUNCTION get_article_version_number(p_art_version_id IN NUMBER)
2240 RETURN Varchar2 IS
2241 CURSOR csr_art_ver IS
2242 SELECT article_version_number
2243 FROM okc_article_versions
2244 WHERE article_version_id = p_art_version_id;
2245
2246 l_article_version_number VARCHAR2(240);
2247
2248 BEGIN
2249 OPEN csr_art_ver;
2250 FETCH csr_art_ver INTO l_article_version_number;
2251 CLOSE csr_art_ver;
2252 RETURN l_article_version_number;
2253 END get_article_version_number;
2254
2255 /*********************************
2256 -- FUNCTION get_section_label
2257 --
2258 *********************************/
2259 FUNCTION get_section_label(p_scn_id IN NUMBER)
2260 RETURN Varchar2 IS
2261 CURSOR csr_section_label IS
2262 SELECT heading
2263 FROM okc_sections_b
2264 WHERE id = p_scn_id;
2265
2266 l_label VARCHAR2(240);
2267
2268 BEGIN
2269 OPEN csr_section_label;
2270 FETCH csr_section_label INTO l_label;
2271 CLOSE csr_section_label;
2272 RETURN l_label;
2273 END get_section_label;
2274
2275 /*********************************
2276 -- FUNCTION get_latest_art_version_no
2277 --
2278 *********************************/
2279 FUNCTION get_latest_art_version_no(
2280 p_article_id IN NUMBER,
2281 p_document_type IN VARCHAR2,
2282 p_document_id IN NUMBER )
2283 RETURN Varchar2 IS
2284
2285 l_article_version_number VARCHAR2(240);
2286 l_article_version_id NUMBER;
2287 l_local_article_id NUMBER;
2288 l_adoption_type VARCHAR2(100);
2289
2290 BEGIN
2291
2292 get_latest_article_details
2293 (
2294 p_article_id => p_article_id,
2295 p_document_type => p_document_type,
2296 p_document_id => p_document_id,
2297 x_article_version_id => l_article_version_id,
2298 x_article_version_number => l_article_version_number,
2299 x_local_article_id => l_local_article_id,
2300 x_adoption_type => l_adoption_type
2301 );
2302
2303 RETURN l_article_version_number;
2304
2305 END get_latest_art_version_no;
2306
2307 /*********************************
2308 -- FUNCTION get_latest_art_version_id
2309 --
2310 *********************************/
2311 FUNCTION get_latest_art_version_id(
2312 p_article_id IN NUMBER,
2313 p_document_type IN VARCHAR2,
2314 p_document_id IN NUMBER )
2315 RETURN NUMBER IS
2316
2317 l_article_version_number VARCHAR2(240);
2318 l_article_version_id NUMBER;
2319 l_local_article_id NUMBER;
2320 l_adoption_type VARCHAR2(100);
2321
2322 BEGIN
2323
2324 get_latest_article_details
2325 (
2326 p_article_id => p_article_id,
2327 p_document_type => p_document_type,
2328 p_document_id => p_document_id,
2329 x_article_version_id => l_article_version_id,
2330 x_article_version_number => l_article_version_number,
2331 x_local_article_id => l_local_article_id,
2332 x_adoption_type => l_adoption_type
2333 );
2334
2335 RETURN l_article_version_id;
2336
2337 END get_latest_art_version_id;
2338
2339 /*********************************
2340 -- FUNCTION get_article_name
2341 --
2342 *********************************/
2343 FUNCTION get_article_name(
2344 p_article_id IN NUMBER,
2345 p_article_version_id IN NUMBER)
2346 RETURN Varchar2 IS
2347
2348 CURSOR csr_article_title IS
2349 SELECT a.article_title, a.article_number
2350 FROM okc_articles_all a
2351 WHERE a.article_id = p_article_id;
2352
2353 CURSOR csr_article_display_name IS
2354 SELECT a.display_name
2355 FROM okc_article_versions a
2356 WHERE a.article_version_id = p_article_version_id;
2357
2358 l_article_title VARCHAR2(450) :='';
2359 l_article_number VARCHAR2(450) :='';
2360 l_display_name VARCHAR2(450) :='';
2361 l_article_name VARCHAR2(1000) :='';
2362
2363 BEGIN
2364
2365 OPEN csr_article_title;
2366 FETCH csr_article_title INTO l_article_title, l_article_number;
2367 CLOSE csr_article_title;
2368
2369 OPEN csr_article_display_name;
2370 FETCH csr_article_display_name INTO l_display_name;
2371 CLOSE csr_article_display_name;
2372
2373 IF g_concat_art_no = 'Y' THEN
2374 IF l_article_number IS NOT NULL THEN
2375 l_article_number := l_article_number||':';
2376 END IF;
2377 l_article_name := NVL(l_article_number,'')||NVL(l_display_name,l_article_title);
2378 ELSE
2379 l_article_name := NVL(l_display_name,l_article_title);
2380 END IF;
2381
2382 RETURN l_article_name;
2383 END get_article_name;
2384
2385 /*********************************
2386 -- FUNCTION GET_SECTION_NAME
2387 --
2388 *********************************/
2389 FUNCTION GET_SECTION_NAME(
2390 p_CONTEXT IN VARCHAR2,
2391 p_ID IN NUMBER
2392 )
2393 RETURN VARCHAR2 IS
2394
2395 -- Fix for Bug 5377982
2396 -- l_name varchar2(80) := null;
2397 l_name OKC_SECTIONS_B.HEADING%TYPE := null;
2398
2399
2400
2401 cursor section_cur(l_section_id NUMBER) is
2402 select HEADING FROM OKC_SECTIONS_B WHERE ID = l_section_id ;
2403
2404 cursor section_for_article_id(l_article_id NUMBER) is
2405 select HEADING FROM OKC_SECTIONS_B WHERE ID = (select scn_id from okc_k_articles_b where id = l_article_id) ;
2406
2407
2408 BEGIN
2409
2410 if p_CONTEXT = 'SECTION' THEN
2411 Open section_cur(p_ID);
2412 fetch section_cur into l_name;
2413 close section_cur;
2414 elsif p_CONTEXT = 'ARTICLE' THEN
2415 Open section_for_article_id(p_ID);
2416 fetch section_for_article_id into l_name;
2417 close section_for_article_id;
2418 else
2419 l_name := null;
2420 end if;
2421
2422 return(l_name);
2423
2424
2425 END GET_SECTION_NAME;
2426
2427 /*********************************
2428 -- FUNCTION GET_SECTION_NAME - given article_version_id returns the default section or the Unassinged Section name
2429 --
2430 *********************************/
2431
2432
2433 FUNCTION GET_SECTION_NAME(p_article_version_id NUMBER)
2434 RETURN VARCHAR2 IS
2435
2436 --l_name varchar2(80) := null;
2437 l_name FND_LOOKUPS.MEANING%TYPE := null;
2438 l_article_version_id NUMBER;
2439 cursor get_default_section_name(l_article_version_id NUMBER) is
2440 select meaning from fnd_lookups where lookup_type = 'OKC_ARTICLE_SECTION' and lookup_code = (
2441 select default_section from okc_article_versions where article_version_id = l_article_version_id);
2442
2443 cursor get_unassigned_section_name is
2444 select meaning from fnd_lookups where lookup_type = 'OKC_ARTICLE_SECTION' and
2445 lookup_code = 'UNASSIGNED';
2446
2447
2448 BEGIN
2449
2450 open get_default_section_name(p_article_version_id);
2451 fetch get_default_section_name into l_name;
2452 if get_default_section_name%NOTFOUND or l_name is null THEN
2453 open get_unassigned_section_name;
2454 fetch get_unassigned_section_name into l_name;
2455 close get_unassigned_section_name;
2456 end if;
2457 close get_default_section_name;
2458 return(l_name);
2459 END GET_SECTION_NAME;
2460
2461 /*********************************
2462 FUNCTION GET_SECTION_NAME - returns Default section for article_version
2463 or one defined in expert enabled template.
2464 Parameters: p_article_version_id
2465 p_template_id
2466 **********************************/
2467
2468 function get_section_name(p_article_version_id IN Number,
2469 p_template_id in Number)
2470 return varchar2 is
2471 --l_name varchar2(80);
2472 l_name FND_LOOKUPS.MEANING%TYPE;
2473
2474 cursor get_article_section_name(l_article_version_id NUMBER) is
2475 select meaning from fnd_lookups
2476 where lookup_type = 'OKC_ARTICLE_SECTION'
2477 and lookup_code = (select default_section from okc_article_versions
2478 where article_version_id = l_article_version_id);
2479
2480 cursor get_expert_section_name(l_template_id NUMBER) is
2481 select meaning from fnd_lookups
2482 where lookup_type = 'OKC_ARTICLE_SECTION'
2483 and lookup_code = (select xprt_scn_code from okc_terms_templates_all
2484 where template_id = l_template_id);
2485
2486 cursor get_unassigned_section_name is
2487 select meaning from fnd_lookups where lookup_type = 'OKC_ARTICLE_SECTION'
2488 and lookup_code = 'UNASSIGNED';
2489
2490 Begin
2491
2492 Open get_article_section_name(p_article_version_id);
2493 Fetch get_article_section_name into l_name;
2494
2495 If get_article_section_name%FOUND and l_name is NOT NULL then
2496 close get_article_section_name;
2497 return l_name;
2498 end if;
2499
2500 Open get_expert_section_name (p_template_id);
2501 Fetch get_expert_section_name into l_name;
2502
2503 If get_expert_section_name%FOUND and l_name is NOT NULL then
2504 close get_expert_section_name;
2505 return l_name;
2506 end if;
2507
2508 Open get_unassigned_section_name;
2509 Fetch get_unassigned_section_name into l_name;
2510
2511 If get_unassigned_section_name%FOUND and l_name is NOT NULL then
2512 close get_unassigned_section_name;
2513 return l_name;
2514 end if;
2515
2516 Exception
2517 WHEN OTHERS then
2518 if get_article_section_name%ISOPEN then
2519 close get_article_section_name;
2520 end if;
2521 if get_expert_section_name%ISOPEN then
2522 close get_expert_section_name;
2523 end if;
2524 if get_unassigned_section_name%ISOPEN then
2525 close get_unassigned_section_name;
2526 end if;
2527
2528 end get_section_name;
2529
2530
2531
2532 /*********************************
2533 -- FUNCTION GET_VALUE_SET_VARIABLE_VALUE
2534 --
2535 *********************************/
2536 FUNCTION GET_VALUE_SET_VARIABLE_VALUE (
2537 p_CONTEXT IN VARCHAR2,
2538 p_VALUE_SET_ID IN NUMBER,
2539 p_FLEX_VALUE_ID IN VARCHAR2 )
2540 RETURN VARCHAR2 IS
2541
2542 --l_name varchar2(80) := null;
2543 l_name FND_FLEX_VALUES_VL.FLEX_VALUE%TYPE := null;
2544
2545 cursor flex_value_vl_csr(l_value_set_id NUMBER, l_flex_value_id VARCHAR2) is
2546 select value.flex_value
2547 from fnd_flex_values_vl value,
2548 fnd_flex_value_sets val_set
2549 where
2550 value.FLEX_VALUE_SET_ID = val_set.FLEX_VALUE_SET_ID
2551 and val_set.flex_value_set_id = l_value_set_id
2552 and to_char(value.flex_value_id)= l_flex_value_id;
2553
2554 BEGIN
2555
2556 if p_CONTEXT = 'I' THEN
2557 Open flex_value_vl_csr(p_VALUE_SET_ID, p_FLEX_VALUE_ID);
2558 fetch flex_value_vl_csr into l_name;
2559 close flex_value_vl_csr;
2560 else
2561 l_name := null;
2562 end if;
2563
2564 return(l_name);
2565 END GET_VALUE_SET_VARIABLE_VALUE;
2566
2567
2568 /*********************************
2569 -- PROCEDURE get_latest_article_details
2570 --
2571 *********************************/
2572 PROCEDURE get_latest_article_details(
2573 p_article_id IN NUMBER,
2574 p_document_type IN VARCHAR2,
2575 p_document_id IN NUMBER,
2576 x_article_version_id OUT NOCOPY NUMBER,
2577 x_article_version_number OUT NOCOPY VARCHAR2,
2578 x_local_article_id OUT NOCOPY NUMBER,
2579 x_adoption_type OUT NOCOPY VARCHAR2 ) IS
2580
2581 CURSOR csr_effective_date IS
2582 SELECT tu.article_effective_date
2583 FROM okc_template_usages tu
2584 WHERE tu.document_type = p_document_type
2585 AND tu.document_id = p_document_id;
2586
2587 -- effectivity date for templates
2588 CURSOR csr_template_effective_date IS
2589 SELECT start_date, end_date
2590 FROM OKC_TERMS_TEMPLATES_ALL
2591 WHERE template_id=p_document_id;
2592
2593 CURSOR l_get_latest_article_csr(p_article_effective_date IN DATE) IS
2594 SELECT article_version_id ,
2595 article_version_number
2596 FROM okc_article_versions
2597 WHERE article_id= p_article_id
2598 AND article_status in ('ON_HOLD','APPROVED')
2599 AND nvl(p_article_effective_date,sysdate) >= Start_date
2600 AND nvl(p_article_effective_date,sysdate) <= nvl(end_date, nvl(p_article_effective_date,sysdate) +1)
2601 AND p_document_type <> 'TEMPLATE'
2602 UNION ALL
2603 SELECT article_version_id ,
2604 article_version_number
2605 FROM okc_article_versions
2606 WHERE article_id= p_article_id
2607 AND nvl(p_article_effective_date,sysdate) >= Start_date
2608 AND nvl(p_article_effective_date,sysdate) <= nvl(end_date, nvl(p_article_effective_date,sysdate) +1)
2609 AND p_document_type = 'TEMPLATE'
2610 ;
2611
2612 CURSOR l_get_local_article_csr(p_article_effective_date IN DATE, b_local_org_id IN NUMBER) IS
2613 SELECT ADP.LOCAL_ARTICLE_VERSION_ID LOCAL_ARTICLE_VERSION_ID,
2614 ADP.ADOPTION_TYPE,
2615 VERS1.ARTICLE_ID
2616 FROM OKC_ARTICLE_VERSIONS VERS,
2617 OKC_ARTICLE_ADOPTIONS ADP,
2618 OKC_ARTICLE_VERSIONS VERS1
2619 WHERE ADP.GLOBAL_ARTICLE_VERSION_ID = VERS.ARTICLE_VERSION_ID
2620 AND VERS.ARTICLE_ID = p_article_id
2621 AND nvl(p_article_effective_date,sysdate) >= VERS.START_DATE
2622 AND nvl(p_article_effective_date,sysdate) <= nvl(VERS.end_date, nvl(p_article_effective_date,sysdate) +1)
2623 AND VERS.ARTICLE_STATUS IN ('ON_HOLD','APPROVED')
2624 AND VERS1.ARTICLE_VERSION_ID =ADP.LOCAL_ARTICLE_VERSION_ID
2625 AND ADP.ADOPTION_TYPE = 'LOCALIZED'
2626 AND ADP.LOCAL_ORG_ID = b_local_org_id
2627 AND ADP.adoption_status IN ( 'APPROVED', 'ON_HOLD')
2628 AND p_document_type <> 'TEMPLATE'
2629 UNION ALL
2630 SELECT ADP.GLOBAL_ARTICLE_VERSION_ID LOCAL_ARTICLE_VERSION_ID,
2631 ADP.ADOPTION_TYPE,
2632 VERS.ARTICLE_ID
2633 FROM OKC_ARTICLE_VERSIONS VERS,
2634 OKC_ARTICLE_ADOPTIONS ADP
2635 WHERE ADP.GLOBAL_ARTICLE_VERSION_ID = VERS.ARTICLE_VERSION_ID
2636 AND VERS.ARTICLE_ID = p_article_id
2637 AND nvl(p_article_effective_date,sysdate) >= VERS.START_DATE
2638 AND nvl(p_article_effective_date,sysdate) <= nvl(VERS.end_date, nvl(p_article_effective_date,sysdate) +1)
2639 AND VERS.ARTICLE_STATUS IN ('ON_HOLD','APPROVED')
2640 AND ADP.ADOPTION_TYPE = 'ADOPTED'
2641 AND ADP.LOCAL_ORG_ID = b_local_org_id
2642 AND ADP.adoption_status IN ( 'APPROVED', 'ON_HOLD')
2643 AND p_document_type <> 'TEMPLATE'
2644 UNION ALL
2645 SELECT ADP.LOCAL_ARTICLE_VERSION_ID LOCAL_ARTICLE_VERSION_ID,
2646 ADP.ADOPTION_TYPE,
2647 VERS1.ARTICLE_ID
2648 FROM OKC_ARTICLE_VERSIONS VERS,
2649 OKC_ARTICLE_ADOPTIONS ADP,
2650 OKC_ARTICLE_VERSIONS VERS1
2651 WHERE ADP.GLOBAL_ARTICLE_VERSION_ID = VERS.ARTICLE_VERSION_ID
2652 AND VERS.ARTICLE_ID = p_article_id
2653 AND nvl(p_article_effective_date,sysdate) >= VERS.START_DATE
2654 AND nvl(p_article_effective_date,sysdate) <= nvl(VERS.end_date, nvl(p_article_effective_date,sysdate) +1)
2655 AND VERS1.ARTICLE_VERSION_ID =ADP.LOCAL_ARTICLE_VERSION_ID
2656 AND ADP.ADOPTION_TYPE = 'LOCALIZED'
2657 AND ADP.LOCAL_ORG_ID = b_local_org_id
2658 AND ADP.adoption_status IN ( 'APPROVED', 'ON_HOLD')
2659 AND p_document_type = 'TEMPLATE'
2660 UNION ALL
2661 SELECT ADP.GLOBAL_ARTICLE_VERSION_ID LOCAL_ARTICLE_VERSION_ID,
2662 ADP.ADOPTION_TYPE,
2663 VERS.ARTICLE_ID
2664 FROM OKC_ARTICLE_VERSIONS VERS,
2665 OKC_ARTICLE_ADOPTIONS ADP
2666 WHERE ADP.GLOBAL_ARTICLE_VERSION_ID = VERS.ARTICLE_VERSION_ID
2667 AND VERS.ARTICLE_ID = p_article_id
2668 AND nvl(p_article_effective_date,sysdate) >= VERS.START_DATE
2669 AND nvl(p_article_effective_date,sysdate) <= nvl(VERS.end_date, nvl(p_article_effective_date,sysdate) +1)
2670 AND ADP.ADOPTION_TYPE = 'ADOPTED'
2671 AND ADP.LOCAL_ORG_ID = b_local_org_id
2672 AND ADP.adoption_status IN ( 'APPROVED', 'ON_HOLD')
2673 AND p_document_type = 'TEMPLATE'
2674 ;
2675
2676 CURSOR l_get_article_org_csr IS
2677 SELECT org_id
2678 FROM OKC_ARTICLES_ALL
2679 WHERE article_id = p_article_id;
2680
2681 CURSOR l_article_number (b_article__version_id IN NUMBER) IS
2682 SELECT article_version_number
2683 FROM okc_article_versions
2684 WHERE article_version_id= b_article__version_id;
2685 l_api_name CONSTANT VARCHAR2(30) := 'get_latest_article_details';
2686 l_effective_date DATE := null;
2687 l_article_version_id NUMBER;
2688 l_article_version_number VARCHAR2(240);
2689 l_adoption_type VARCHAR2(100);
2690 l_local_article_id NUMBER;
2691 l_current_org_id VARCHAR2(100);
2692 l_article_org_id NUMBER;
2693 l_template_start_date DATE :=null;
2694 l_template_end_date DATE :=null;
2695
2696 BEGIN
2697
2698 IF p_document_type = 'TEMPLATE' THEN
2699 OPEN csr_template_effective_date;
2700 FETCH csr_template_effective_date INTO l_template_start_date, l_template_end_date;
2701 CLOSE csr_template_effective_date;
2702
2703 IF NVL(l_template_end_date,sysdate) >= sysdate THEN
2704 IF l_template_start_date > sysdate THEN
2705 l_effective_date := l_template_start_date;
2706 ELSE
2707 l_effective_date := sysdate;
2708 END IF;
2709 ELSE
2710 l_effective_date := l_template_end_date;
2711 END IF;
2712
2713 ELSE
2714 -- document type not TEMPLATE
2715 OPEN csr_effective_date;
2716 FETCH csr_effective_date INTO l_effective_date;
2717 CLOSE csr_effective_date;
2718 END IF;
2719
2720 -- check if Article is global or local
2721 OPEN l_get_article_org_csr;
2722 FETCH l_get_article_org_csr INTO l_article_org_id;
2723 CLOSE l_get_article_org_csr;
2724
2725 -- current Org Id
2726 -- fnd_profile.get('ORG_ID',l_current_org_id);
2727 l_current_org_id := OKC_TERMS_UTIL_PVT.get_current_org_id(p_document_type, p_document_id);
2728
2729 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2730 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: l_article_org_id : '||l_article_org_id);
2731 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: l_current_org_id : '||l_current_org_id);
2732 END IF;
2733
2734 IF nvl(l_current_org_id,'?') <> l_article_org_id THEN
2735 -- this is a ADOPTED OR LOCALIZED ARTICLE
2736 OPEN l_get_local_article_csr(l_effective_date , l_current_org_id);
2737 FETCH l_get_local_article_csr INTO l_article_version_id, l_adoption_type, l_local_article_id;
2738 CLOSE l_get_local_article_csr;
2739 -- get article version number
2740 OPEN l_article_number(l_article_version_id);
2741 FETCH l_article_number INTO l_article_version_number;
2742 CLOSE l_article_number;
2743
2744 x_article_version_id := l_article_version_id;
2745 x_article_version_number := l_article_version_number;
2746 x_local_article_id := l_local_article_id;
2747 x_adoption_type := l_adoption_type;
2748
2749 ELSE
2750 -- normal case
2751 OPEN l_get_latest_article_csr (l_effective_date);
2752 FETCH l_get_latest_article_csr INTO l_article_version_id,l_article_version_number;
2753 CLOSE l_get_latest_article_csr;
2754
2755 x_article_version_id := l_article_version_id;
2756 x_article_version_number := l_article_version_number;
2757 x_local_article_id := p_article_id;
2758 x_adoption_type := NULL;
2759
2760 END IF;
2761
2762 END get_latest_article_details;
2763
2764 /*********************************
2765 -- FUNCTION get_local_article_id
2766 --
2767 *********************************/
2768 FUNCTION get_local_article_id(
2769 p_article_id IN NUMBER,
2770 p_document_type IN VARCHAR2,
2771 p_document_id IN NUMBER )
2772 RETURN NUMBER IS
2773 l_api_name CONSTANT VARCHAR2(30) := 'get_local_article_id';
2774 l_article_version_number VARCHAR2(240);
2775 l_article_version_id NUMBER;
2776 l_local_article_id NUMBER;
2777 l_adoption_type VARCHAR2(100);
2778
2779 BEGIN
2780
2781 get_latest_article_details
2782 (
2783 p_article_id => p_article_id,
2784 p_document_type => p_document_type,
2785 p_document_id => p_document_id,
2786 x_article_version_id => l_article_version_id,
2787 x_article_version_number => l_article_version_number,
2788 x_local_article_id => l_local_article_id,
2789 x_adoption_type => l_adoption_type
2790 );
2791
2792 RETURN l_local_article_id;
2793
2794 END get_local_article_id;
2795
2796 /*********************************
2797 -- FUNCTION get_adoption_type
2798 --
2799 *********************************/
2800
2801 FUNCTION get_adoption_type(
2802 p_article_id IN NUMBER,
2803 p_document_type IN VARCHAR2,
2804 p_document_id IN NUMBER )
2805 RETURN Varchar2 IS
2806 l_api_name CONSTANT VARCHAR2(30) := 'get_adoption_type';
2807 l_article_version_number VARCHAR2(240);
2808 l_article_version_id NUMBER;
2809 l_local_article_id NUMBER;
2810 l_adoption_type VARCHAR2(100);
2811
2812 BEGIN
2813
2814 get_latest_article_details
2815 (
2816 p_article_id => p_article_id,
2817 p_document_type => p_document_type,
2818 p_document_id => p_document_id,
2819 x_article_version_id => l_article_version_id,
2820 x_article_version_number => l_article_version_number,
2821 x_local_article_id => l_local_article_id,
2822 x_adoption_type => l_adoption_type
2823 );
2824
2825 RETURN l_adoption_type;
2826
2827 END get_adoption_type;
2828
2829 /*********************************
2830 -- FUNCTION get_print_template_name
2831 --
2832 *********************************/
2833 FUNCTION get_print_template_name(p_print_template_id IN NUMBER)
2834 RETURN VARCHAR2 IS
2835 l_api_name CONSTANT VARCHAR2(30) := 'get_print_template_name';
2836 l_sql_stmt VARCHAR2(4000);
2837 l_print_template_name VARCHAR2(255);
2838 l_dummy VARCHAR2(1);
2839 TYPE name_csr IS REF CURSOR;
2840 l_tmpl_csr NAME_CSR;
2841 l_apps_user VARCHAR2(150);
2842
2843 CURSOR l_apps_user_csr IS
2844 SELECT oracle_username
2845 FROM fnd_oracle_userid
2846 WHERE read_only_flag = 'U';
2847
2848 CURSOR l_xdo_view_csr(pc_user VARCHAR2) IS
2849 SELECT 1
2850 FROM all_views
2851 WHERE view_name like 'XDO_TEMPLATES_VL'
2852 AND owner = pc_user;
2853
2854 BEGIN
2855
2856 OPEN l_apps_user_csr;
2857 FETCH l_apps_user_csr INTO l_apps_user;
2858 CLOSE l_apps_user_csr;
2859
2860 OPEN l_xdo_view_csr(l_apps_user);
2861 FETCH l_xdo_view_csr INTO l_dummy;
2862 IF l_xdo_view_csr%FOUND THEN
2863 l_sql_stmt := 'SELECT SUBSTR(TEMPLATE_NAME,1,255) FROM XDO_TEMPLATES_VL WHERE TEMPLATE_ID = :1';
2864
2865 OPEN l_tmpl_csr FOR l_sql_stmt USING p_print_template_id;
2866 FETCH l_tmpl_csr INTO l_print_template_name;
2867 CLOSE l_tmpl_csr;
2868 END IF;
2869 CLOSE l_xdo_view_csr;
2870
2871 RETURN l_print_template_name;
2872
2873 END get_print_template_name;
2874
2875
2876 /*********************************
2877 -- FUNCTION get_current_org_id
2878 --
2879 *********************************/
2880 FUNCTION get_current_org_id
2881 (
2882 p_doc_type IN VARCHAR2,
2883 p_doc_id IN NUMBER
2884 ) RETURN NUMBER IS
2885
2886 CURSOR l_org_id_csr IS
2887 SELECT t.org_id
2888 FROM okc_terms_templates_all t,
2889 okc_template_usages u
2890 WHERE t.template_id = u.template_id
2891 AND u.document_type = p_doc_type
2892 AND u.document_id = p_doc_id ;
2893
2894 CURSOR l_tmpl_org_id_csr IS
2895 SELECT t.org_id
2896 FROM okc_terms_templates_all t
2897 WHERE t.template_id = p_doc_id ;
2898
2899 l_current_org_id NUMBER;
2900 l_api_name CONSTANT VARCHAR2(30) := 'get_current_org_id';
2901 BEGIN
2902 IF p_doc_type = G_TMPL_DOC_TYPE THEN
2903 OPEN l_tmpl_org_id_csr;
2904 FETCH l_tmpl_org_id_csr INTO l_current_org_id;
2905 CLOSE l_tmpl_org_id_csr;
2906 ELSE
2907 -- doc is not template
2908 OPEN l_org_id_csr;
2909 FETCH l_org_id_csr INTO l_current_org_id;
2910 CLOSE l_org_id_csr;
2911 END IF;
2912
2913 RETURN l_current_org_id;
2914
2915 END get_current_org_id;
2916
2917
2918 FUNCTION get_template_model_name
2919 (
2920 p_template_id IN NUMBER,
2921 p_template_model_id IN NUMBER
2922 ) RETURN VARCHAR2 IS
2923 x_return_status VARCHAR2(50);
2924 x_msg_count NUMBER;
2925 x_msg_data VARCHAR2(4000);
2926 x_template_model_name VARCHAR2(255) NULL;
2927 x_published_by VARCHAR2(255) := NULL;
2928 x_publish_date DATE := NULL;
2929 x_publication_id NUMBER := NULL;
2930 l_api_name CONSTANT VARCHAR2(30) := 'get_template_model_name';
2931 BEGIN
2932 BEGIN
2933 /*
2934 * Removed call to OKC_EXPRT_UTIL_GRP for 11.5.10+: Contract Expert Changes
2935 */
2936 x_template_model_name := NULL;
2937 EXCEPTION
2938 WHEN OTHERS THEN
2939 x_template_model_name := NULL;
2940 END;
2941
2942 RETURN x_template_model_name;
2943 END;
2944
2945 FUNCTION get_tmpl_model_published_by
2946 (
2947 p_template_id IN NUMBER,
2948 p_template_model_id IN NUMBER
2949 ) RETURN VARCHAR2 IS
2950 x_return_status VARCHAR2(50);
2951 x_msg_count NUMBER;
2952 x_msg_data VARCHAR2(4000);
2953 x_template_model_name VARCHAR2(255) NULL;
2954 x_published_by VARCHAR2(255) := NULL;
2955 x_publish_date DATE := NULL;
2956 x_publication_id NUMBER := NULL;
2957
2958 BEGIN
2959 BEGIN
2960
2961 /*
2962 * Removed call to OKC_EXPRT_UTIL_GRP for 11.5.10+: Contract Expert Changes
2963 */
2964 x_published_by := NULL;
2965 EXCEPTION
2966 WHEN OTHERS THEN
2967 x_published_by := NULL;
2968 END;
2969
2970 RETURN x_published_by;
2971 END;
2972
2973 FUNCTION get_tmpl_model_publish_date
2974 (
2975 p_template_id IN NUMBER,
2976 p_template_model_id IN NUMBER
2977 ) RETURN DATE IS
2978 x_return_status VARCHAR2(50);
2979 x_msg_count NUMBER;
2980 x_msg_data VARCHAR2(4000);
2981 x_template_model_name VARCHAR2(255) NULL;
2982 x_published_by VARCHAR2(255) := NULL;
2983 x_publish_date DATE := NULL;
2984 x_publication_id NUMBER := NULL;
2985 l_api_name CONSTANT VARCHAR2(30) := 'get_tmpl_model_publish_date';
2986 BEGIN
2987 BEGIN
2988
2989 /*
2990 * Removed call to OKC_EXPRT_UTIL_GRP for 11.5.10+: Contract Expert Changes
2991 */
2992 x_publish_date := NULL;
2993 EXCEPTION
2994 WHEN OTHERS THEN
2995 x_publish_date := NULL;
2996 END;
2997
2998 RETURN x_publish_date;
2999 END;
3000 FUNCTION get_chr_id_for_doc_id
3001 (
3002 p_document_id IN NUMBER
3003 ) RETURN NUMBER IS
3004 l_api_name CONSTANT VARCHAR2(30) := 'get_chr_id_for_doc_id';
3005 CURSOR l_get_id IS
3006 SELECT id
3007 FROM okc_k_headers_b
3008 WHERE document_id = p_document_id ;
3009 l_chr_id NUMBER;
3010 BEGIN
3011 open l_get_id;
3012 fetch l_get_id into l_chr_id;
3013 close l_get_id;
3014 return l_chr_id;
3015 END;
3016
3017 --Checks if the given function is accessible to the user and returns 'Y' if accessible else 'N'
3018 FUNCTION is_Function_Accessible(
3019 p_function_name IN VARCHAR2
3020 ) RETURN VARCHAR2 IS
3021 l_api_name CONSTANT VARCHAR2(30) := 'is_Function_Accessible';
3022 BEGIN
3023 IF (p_function_name is null) THEN
3024 RETURN 'N' ;
3025 ELSIF fnd_function.test(p_function_name,'N') THEN
3026 RETURN 'Y' ;
3027 ELSE
3028 RETURN 'N' ;
3029 END IF;
3030 EXCEPTION
3031 WHEN OTHERS THEN
3032 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3033 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'2000: Leaving is_Function_Accessible because of EXCEPTION: '||sqlerrm);
3034 END IF;
3035 RETURN NULL;
3036 END is_Function_Accessible;
3037
3038 /************************************************************************************
3039 --Procedure that checks for template information for a given documentId, documentType
3040 Input: p_document_type,
3041 p_document_id,
3042 p_mode
3043 p_eff_date
3044 p_org_id
3045
3046 Returns: x_template_exists
3047 x_template_id
3048 x_template_name
3049 x_enable_expert_button
3050 x_template_org_id
3051 x_doc_numbering_scheme
3052 x_config_header_id
3053 x_config_revision_number
3054 x_valid_config_yn
3055 Purpose: For the given document_type, document_id,
3056 a.if there is no record in
3057 template_usages, returns the default template details if one exists
3058 b.else it returns the template information as listed in the Out variables
3059 If the mode is not 'VIEW', it updates the template usages record based on
3060 business rules.
3061
3062 Where Used: In Authoring page: Structure page invokes this procedure
3063 *************************************************************************************/
3064 PROCEDURE get_template_details (
3065 p_api_version IN NUMBER,
3066 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3067 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3068
3069 x_return_status OUT NOCOPY VARCHAR2,
3070 x_msg_data OUT NOCOPY VARCHAR2,
3071 x_msg_count OUT NOCOPY NUMBER,
3072
3073 p_document_type IN VARCHAR2,
3074 p_document_id IN NUMBER,
3075 p_mode in VARCHAR2,
3076 p_eff_date IN DATE,
3077 p_org_id IN NUMBER,
3078 x_template_exists OUT NOCOPY VARCHAR2,
3079 x_template_id OUT NOCOPY NUMBER,
3080 x_template_name OUT NOCOPY VARCHAR2,
3081 x_enable_expert_button OUT NOCOPY VARCHAR2,
3082 x_template_org_id OUT NOCOPY NUMBER,
3083 x_doc_numbering_scheme OUT NOCOPY VARCHAR2,
3084 x_config_header_id OUT NOCOPY NUMBER,
3085 x_config_revision_number OUT NOCOPY NUMBER,
3086 x_valid_config_yn OUT NOCOPY VARCHAR2
3087 ) IS
3088 l_api_version CONSTANT NUMBER := 1;
3089 l_api_name CONSTANT VARCHAR2(30) := 'get_template_details';
3090 l_meaning VARCHAR2(100);
3091 l_sequence NUMBER;
3092
3093 l_template_exists VARCHAR2(5) := 'false';
3094 l_template_id NUMBER := 0;
3095 l_template_name VARCHAR2(2000);
3096 l_enable_expert_button VARCHAR2(5) := 'false';
3097 l_template_org_id NUMBER;
3098
3099 l_doc_numbering_scheme VARCHAR(2000);
3100 l_config_header_id NUMBER;
3101 l_config_revision_number NUMBER;
3102 l_valid_config_yn VARCHAR2(2000);
3103 l_article_effective_date DATE;
3104 l_update_date BOOLEAN := false;
3105 l_update_date_with DATE := FND_API.G_MISS_DATE;
3106
3107
3108 Cursor l_get_template_details_csr IS
3109 select a.template_id, b.template_name, a.article_effective_date,
3110 a.doc_numbering_scheme,
3111 a.config_header_id, a.config_revision_number,
3112 a.valid_config_yn, b.org_id
3113 from okc_template_usages a ,okc_terms_templates_all b
3114 where a.template_id = b.template_id
3115 and a.document_id = p_document_id and a.document_type = p_document_type;
3116
3117 cursor l_get_dflt_tmpl_dtls_csr is
3118 select a.template_id, b.template_name
3119 from
3120 okc_allowed_tmpl_usages a, okc_terms_templates_all b
3121 where a.template_id = b.template_id
3122 and a.default_yn = 'Y'
3123 and b.status_code = 'APPROVED'
3124 and a.document_type = p_document_type
3125 and b.org_id = p_org_id
3126 and nvl(p_eff_date,trunc(sysdate)) between start_date and nvl(end_date, trunc(sysdate));
3127
3128 BEGIN
3129 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3130 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7500: Entered get_template_details');
3131 END IF;
3132
3133 -- Standard call to check for call compatibility.
3134 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
3135 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3136 END IF;
3137 -- Initialize message list if p_init_msg_list is set to TRUE.
3138 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3139 FND_MSG_PUB.initialize;
3140 END IF;
3141 -- Initialize API return status to success
3142 x_return_status := G_RET_STS_SUCCESS;
3143
3144 x_template_exists := 'false';
3145 x_template_id := 0;
3146 x_enable_expert_button := 'false';
3147 x_template_org_id := 0;
3148
3149
3150 --------------------------------------------
3151 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3152 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7600: get_template_details');
3153 END IF;
3154 --------------------------------------------
3155
3156
3157
3158 open l_get_template_details_csr;
3159 fetch l_get_template_details_csr into l_template_id, l_template_name,
3160 l_article_effective_date,
3161 x_doc_numbering_scheme,
3162 l_config_header_id, l_config_revision_number,
3163 l_valid_config_yn, l_template_org_id;
3164 IF l_get_template_details_csr%NOTFOUND THEN
3165 if(p_mode <> 'UPDATE') THEN
3166 close l_get_template_details_csr;
3167 else
3168 open l_get_dflt_tmpl_dtls_csr;
3169 fetch l_get_dflt_tmpl_dtls_csr into l_template_id, l_template_name;
3170 if l_get_dflt_tmpl_dtls_csr%NOTFOUND THEN
3171 x_template_exists := 'false';
3172 else
3173 x_template_exists :='false';
3174 x_enable_expert_button := 'false';
3175 x_template_id := l_template_id;
3176 x_template_name := l_template_name;
3177 end if;
3178 close l_get_dflt_tmpl_dtls_csr;
3179 end if;
3180 else
3181 x_template_exists := 'true';
3182 x_template_id := l_template_id;
3183 x_template_name := l_template_name;
3184 x_template_org_id := l_template_org_id;
3185
3186 x_config_header_id := l_config_header_id;
3187 x_config_revision_number := l_config_revision_number;
3188 x_doc_numbering_scheme :=l_doc_numbering_scheme;
3189 x_valid_config_yn := l_valid_config_yn;
3190
3191 if( p_mode is not null and p_mode <> 'VIEW') THEN
3192 OKC_XPRT_UTIL_PVT.enable_expert_button(
3193 p_api_version => p_api_version,
3194 p_init_msg_list => NULL,
3195 p_template_id => x_template_id,
3196 p_document_id => p_document_id,
3197 p_document_type => p_document_type,
3198 x_enable_expert_button => x_enable_expert_button,
3199 x_return_status => x_return_status,
3200 x_msg_count => x_msg_count,
3201 x_msg_data => x_msg_data );
3202 l_update_date := false;
3203 if(l_article_effective_date is NULL) then
3204 l_update_date := true;
3205 l_update_date_with := p_eff_date;
3206 elsif(l_article_effective_date <> p_eff_date and p_eff_date is NULL) then
3207 l_update_date := true;
3208 l_update_date_with := FND_API.G_MISS_DATE;
3209 elsif(l_article_effective_date <> p_eff_date ) then
3210 l_update_date := true;
3211 l_update_date_with := p_eff_date;
3212 end if;
3213 if(l_update_date) then
3214 UPDATE okc_template_usages
3215 SET article_effective_date = l_update_date_with
3216 WHERE document_type = p_document_type
3217 AND document_id = p_document_id;
3218 end if;
3219 end if;
3220 end if;
3221 IF l_get_template_details_csr%ISOPEN THEN
3222 CLOSE l_get_template_details_csr;
3223 END IF;
3224
3225
3226
3227
3228 --------------------------------------------
3229 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3230 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3231 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3232 RAISE FND_API.G_EXC_ERROR ;
3233 END IF;
3234 --------------------------------------------
3235
3236 IF FND_API.To_Boolean( p_commit ) THEN
3237 COMMIT WORK;
3238 END IF;
3239 -- Standard call to get message count and if count is 1, get message info.
3240 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3241 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3242 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7700: Leaving get_template_details');
3243 END IF;
3244 EXCEPTION
3245 WHEN FND_API.G_EXC_ERROR THEN
3246
3247 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3248 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7800: Leaving get_template_details : OKC_API.G_EXCEPTION_ERROR Exception');
3249 END IF;
3250
3251 IF l_get_template_details_csr%ISOPEN THEN
3252 CLOSE l_get_template_details_csr;
3253 END IF;
3254
3255 IF l_get_dflt_tmpl_dtls_csr%ISOPEN THEN
3256 CLOSE l_get_dflt_tmpl_dtls_csr;
3257 END IF;
3258
3259
3260 x_return_status := G_RET_STS_ERROR ;
3261 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3262
3263 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3264 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3265 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7900: Leaving get_template_details : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
3266 END IF;
3267
3268 IF l_get_template_details_csr%ISOPEN THEN
3269 CLOSE l_get_template_details_csr;
3270 END IF;
3271 IF l_get_dflt_tmpl_dtls_csr%ISOPEN THEN
3272 CLOSE l_get_dflt_tmpl_dtls_csr;
3273 END IF;
3274
3275 x_return_status := G_RET_STS_UNEXP_ERROR ;
3276 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3277
3278 WHEN OTHERS THEN
3279 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3280 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'8000: Leaving get_template_details because of EXCEPTION: '||sqlerrm);
3281 END IF;
3282
3283 IF l_get_template_details_csr%ISOPEN THEN
3284 CLOSE l_get_template_details_csr;
3285 END IF;
3286 IF l_get_dflt_tmpl_dtls_csr%ISOPEN THEN
3287 CLOSE l_get_dflt_tmpl_dtls_csr;
3288 END IF;
3289
3290 x_return_status := G_RET_STS_UNEXP_ERROR ;
3291 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
3292 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
3293 END IF;
3294 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3295 END get_template_details ;
3296
3297
3298 --Checks if the given section is deleted
3299 FUNCTION is_section_deleted(
3300 p_scn_id IN NUMBER
3301 ) RETURN VARCHAR2 IS
3302 /*
3303 This function will be called from Update Section Page
3304 Given a scn_id it will return the following:
3305 'D' If the section is deleted i.e AMENDMENT_OPERATION_CODE or SUMMARY_AMEND_OPERATION_CODE is 'DELETED'
3306 'E' If the scn_id does not exists which will result in Stale Data Error
3307 'S' If the scn_id is NOT deleted and exists
3308 */
3309 CURSOR csr_check_section IS
3310 SELECT amendment_operation_code,summary_amend_operation_code
3311 FROM okc_sections_b
3312 WHERE id = p_scn_id;
3313 l_api_name CONSTANT VARCHAR2(30) := 'is_section_deleted';
3314 l_amendment_operation_code VARCHAR2(30);
3315 l_summary_amend_operation_code VARCHAR2(30);
3316 l_return VARCHAR2(1);
3317
3318 BEGIN
3319 OPEN csr_check_section;
3320 FETCH csr_check_section INTO l_amendment_operation_code, l_summary_amend_operation_code;
3321 IF csr_check_section%NOTFOUND THEN
3322 l_return := 'E';
3323 ELSE
3324 IF (NVL(l_amendment_operation_code,'x') = 'DELETED' OR
3325 NVL(l_summary_amend_operation_code,'x') = 'DELETED') THEN
3326 l_return := 'D';
3327 ELSE
3328 l_return := 'S';
3329 END IF;
3330 END IF;
3331 CLOSE csr_check_section;
3332
3333 RETURN l_return;
3334
3335 EXCEPTION
3336 WHEN OTHERS THEN
3337 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3338 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'2000: Leaving is_section_deleted because of EXCEPTION: '||sqlerrm);
3339 END IF;
3340 RETURN NULL;
3341
3342 END is_section_deleted;
3343
3344 --Checks if the given article is deleted
3345 FUNCTION is_article_deleted(
3346 p_cat_id IN NUMBER,
3347 p_article_id IN NUMBER
3348 ) RETURN VARCHAR2 IS
3349 /*
3350 This function will be called from Update Article Page
3351 Given a cat_id and sav_sae_id it will return the following:
3352 'D' If the article is deleted i.e AMENDMENT_OPERATION_CODE or SUMMARY_AMEND_OPERATION_CODE is 'DELETED'
3353 'E' If the sav_sae_id does not match the sav_sae_id in record
3354 'S' If the article record is NOT deleted and exists
3355 */
3356 CURSOR csr_check_article IS
3357 SELECT amendment_operation_code,summary_amend_operation_code
3358 FROM okc_k_articles_b
3359 WHERE id = p_cat_id
3360 AND sav_sae_id = p_article_id ;
3361 l_api_name CONSTANT VARCHAR2(30) := 'is_article_deleted';
3362 l_amendment_operation_code VARCHAR2(30);
3363 l_summary_amend_operation_code VARCHAR2(30);
3364 l_return VARCHAR2(1);
3365
3366 BEGIN
3367 OPEN csr_check_article;
3368 FETCH csr_check_article INTO l_amendment_operation_code, l_summary_amend_operation_code;
3369 IF csr_check_article%NOTFOUND THEN
3370 l_return := 'E';
3371 ELSE
3372 IF (NVL(l_amendment_operation_code,'x') = 'DELETED' OR
3373 NVL(l_summary_amend_operation_code,'x') = 'DELETED') THEN
3374 l_return := 'D';
3375 ELSE
3376 l_return := 'S';
3377 END IF;
3378 END IF;
3379
3380 EXCEPTION
3381 WHEN OTHERS THEN
3382 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3383 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'2000: Leaving is_article_deleted because of EXCEPTION: '||sqlerrm);
3384 END IF;
3385 RETURN NULL;
3386
3387 END is_article_deleted;
3388
3389
3390 --Checks if the given article has deliverable type variables and the deliverable is amended
3391 --To be used by the Printing program
3392 FUNCTION deliverable_amendment_exists(
3393 p_cat_id IN NUMBER,
3394 p_document_id IN NUMBER,
3395 p_document_type IN VARCHAR2
3396 ) RETURN VARCHAR2 IS
3397 /*
3398 This function will be called from Printing Program
3399 Given a cat_id, document_type and document_id it will return the following:
3400 'Y' If the clause has deliverable type variables and deliverable amendments exist
3401 'N' If (the clause has no deliverable type variables) OR
3402 (has deliverable type variables AND deliverable amendments do not exist)
3403 */
3404
3405 CURSOR l_doc_variable_csr IS
3406 SELECT variable_code
3407 FROM okc_k_art_variables
3408 WHERE cat_id = p_cat_id
3409 AND variable_type = 'D';
3410 l_api_name CONSTANT VARCHAR2(30) := 'deliverable_amendment_exists';
3411 l_variable_type okc_k_art_variables.variable_type%TYPE;
3412 l_return VARCHAR2(1) := 'N';
3413 l_msg_data VARCHAR2(2000);
3414 l_msg_count NUMBER;
3415 l_return_status VARCHAR2(30);
3416
3417 BEGIN
3418 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3419 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1000: Entered OKC_TERMS_UTIL_PVT.deliverable_amendment_exists');
3420 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1010: p_cat_id='||p_cat_id);
3421 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1020: p_document_id='||p_document_id);
3422 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1030: p_document_type='||p_document_type);
3423 END IF;
3424 FOR rec IN l_doc_variable_csr LOOP
3425 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3426 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1040: Before calling OKC_DELIVERABLE_PROCESS_PVT.deliverable_amendment_exists');
3427 END IF;
3428 l_return := OKC_DELIVERABLE_PROCESS_PVT.deliverable_amendment_exists
3429 ( p_api_version => 1,
3430 p_init_msg_list => FND_API.G_FALSE,
3431 p_bus_doc_type => p_document_type,
3432 p_bus_doc_id => p_document_id,
3433 p_variable_code => rec.variable_code,
3434 x_msg_data => l_msg_data,
3435 x_msg_count => l_msg_count,
3436 x_return_status => l_return_status);
3437 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3438 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1050: After calling OKC_DELIVERABLE_PROCESS_PVT.deliverable_amendment_exists');
3439 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1060: x_return_status='||l_return_status);
3440 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1070: l_return='||l_return);
3441 END IF;
3442 IF l_return = 'Y' THEN
3443 RETURN l_return;
3444 END IF;
3445 END LOOP;
3446 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3447 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1090: l_return='||l_return);
3448 END IF;
3449
3450 RETURN l_return;
3451
3452 EXCEPTION
3453 WHEN OTHERS THEN
3454 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3455 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1090: Leaving OKC_TERMS_UTIL_PVT.deliverable_amendment_exists because of EXCEPTION: '||sqlerrm);
3456 END IF;
3457 RETURN 'N';
3458
3459 END deliverable_amendment_exists;
3460
3461 /*
3462 -- PROCEDURE purge_qa_results
3463 -- Called by concurrent program to purge old QA error data.
3464 -- Parameter p_num_days is how far in the past to start the purge
3465 */
3466 PROCEDURE purge_qa_results (
3467 errbuf OUT NOCOPY VARCHAR2,
3468 retcode OUT NOCOPY VARCHAR2,
3469 p_num_days IN NUMBER default 3)
3470 IS
3471 l_api_name CONSTANT VARCHAR2(30) :='purge_qa_validation_results';
3472 l_api_version CONSTANT VARCHAR2(30) := 1.0;
3473 l_init_msg_list VARCHAR2(3) := 'T';
3474 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3475 l_msg_count NUMBER;
3476 l_msg_data VARCHAR2(1000);
3477 l_num_days NUMBER;
3478
3479 E_Resource_Busy EXCEPTION;
3480 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
3481
3482 BEGIN
3483 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3484 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,l_api_name,'100: Inside OKC_TERMS_UTIL_PVT.PURGE_QA_RESULTS');
3485 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,l_api_name,'Parameters: p_num_days='||p_num_days);
3486 END IF;
3487
3488 FND_FILE.PUT_LINE(FND_FILE.LOG,'Parameters: p_num_days='||p_num_days);
3489 if p_num_days < 1 then
3490 l_num_days := 1;
3491 FND_FILE.PUT_LINE(FND_FILE.LOG,'Setting p_num_days to 1 to prevent any current data from being deleted');
3492 else
3493 l_num_days := p_num_days;
3494 end if;
3495
3496 --Initialize the return code
3497 retcode := 0;
3498
3499 delete from OKC_QA_ERRORS_T qa
3500 where creation_date <= sysdate - l_num_days;
3501
3502 commit;
3503
3504 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3505 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,l_api_name,'100: leaving OKC_TERMS_UTIL_PVT.PURGE_QA_RESULTS');
3506 END IF;
3507 EXCEPTION
3508 WHEN E_Resource_Busy THEN
3509 l_return_status := fnd_api.g_ret_sts_error;
3510 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3511 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,l_api_name,'200: Resource busy exception');
3512 END IF;
3513 IF FND_MSG_PUB.Count_Msg > 0 Then
3514 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
3515 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MSG_PUB.Get(i,p_encoded =>FND_API.G_FALSE ));
3516 END LOOP;
3517
3518 END IF;
3519 FND_MSG_PUB.initialize;
3520 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
3521 WHEN OTHERS THEN
3522 retcode := 2;
3523 errbuf := substr(sqlerrm,1,200);
3524 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3525 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,l_api_name,'200: Other exception');
3526 END IF;
3527 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3528 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
3529 END IF;
3530 IF FND_MSG_PUB.Count_Msg > 0 Then
3531 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
3532 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MSG_PUB.Get(i,p_encoded =>FND_API.G_FALSE ));
3533 END LOOP;
3534 END IF;
3535 FND_MSG_PUB.initialize;
3536
3537 END PURGE_QA_RESULTS;
3538
3539 ------------------------------------------------------
3540 -- FUNCTION get_latest_tmpl_art_version_id
3541 -------------------------------------------------------
3542 /*
3543 -- 11.5.10+
3544 -- 2004/8/20 ANJKUMAR: overloaded function with additional params
3545 -- p_doc_type and p_doc_id, changes logic only for p_doc_type = 'TEMPLATE'
3546 -- looks in first in new table OKC_TMPL_DRAFT_CLAUSES if status
3547 -- DRAFT/REJECTED/PENDING_APPROVAL for article versions
3548 --
3549 */
3550 FUNCTION get_latest_tmpl_art_version_id(
3551 p_article_id IN NUMBER,
3552 p_start_date IN DATE,
3553 p_end_date IN DATE,
3554 p_status_code IN VARCHAR2,
3555 p_doc_type IN VARCHAR2 DEFAULT NULL,
3556 p_doc_id IN NUMBER DEFAULT NULL) RETURN NUMBER
3557 IS
3558 l_article_version_id okc_article_versions.article_version_id%TYPE;
3559 l_effective_date DATE;
3560 l_article_org_id NUMBER;
3561 l_current_org_id NUMBER;
3562 l_api_name CONSTANT VARCHAR2(30) := 'get_latest_tmpl_art_version_id';
3563 -- new variable
3564 l_stop BOOLEAN := TRUE;
3565
3566 -- new cursor to get draft/rejected versions from table OKC_TMPL_DRAFT_CLAUSES
3567 CURSOR l_draft_selected_ver_csr IS
3568 SELECT ARTV.article_version_id
3569 FROM OKC_TMPL_DRAFT_CLAUSES TMPLC,
3570 OKC_ARTICLE_VERSIONS ARTV
3571 WHERE TMPLC.template_id = p_doc_id
3572 AND TMPLC.article_id = p_article_id
3573 AND TMPLC.selected_yn = 'Y'
3574 AND ARTV.article_id = TMPLC.article_id
3575 AND ARTV.article_version_id = TMPLC.article_version_id
3576 AND ARTV.article_status in ('DRAFT', 'REJECTED')
3577 AND EXISTS (SELECT 1 FROM OKC_K_ARTICLES_B KART
3578 WHERE KART.document_type = p_doc_type
3579 AND KART.document_id = p_doc_id
3580 AND KART.sav_sae_id = TMPLC.article_id);
3581
3582 -- modify this cursor to exclude versions from the the table OKC_DRAFT_CLAUSES
3583 CURSOR l_draft_ver_csr(cp_effective_date DATE) IS
3584 SELECT ver.article_version_id
3585 FROM okc_articles_all art,
3586 okc_article_versions ver
3587 WHERE art.article_id = p_article_id
3588 AND art.article_id = ver.article_id
3589 AND cp_effective_date BETWEEN ver.start_date AND NVL(ver.end_date,cp_effective_date+1)
3590 -- begin change
3591 -- Bug 4021182, we cannot include pending approval, on hold or expired clauses here
3592 AND VER.article_status IN ('APPROVED', 'DRAFT', 'REJECTED')
3593 AND NOT EXISTS (SELECT 1 from OKC_TMPL_DRAFT_CLAUSES TMPLC
3594 WHERE TMPLC.template_id = p_doc_id
3595 AND TMPLC.article_id = p_article_id
3596 AND TMPLC.article_version_id = VER.article_version_id)
3597 -- end change
3598 ORDER BY ver.article_version_number DESC;
3599
3600 -- last effort to get a clause for template in draft status
3601 CURSOR l_draft_latest_ver_csr(cp_effective_date DATE) IS
3602 SELECT ver.article_version_id
3603 FROM okc_articles_all art,
3604 okc_article_versions ver
3605 WHERE art.article_id = p_article_id
3606 AND art.article_id = ver.article_id
3607 -- Bugs 4018610, 4018467, the start date of draft clause can be
3608 -- changed to a future date, making this cursor return nothing
3609 -- The draft clause status can also change to pending approval
3610 -- or an approved clause can be put on hold after including in the template
3611 -- AND ver.start_date <= cp_effective_date
3612 AND ver.start_date = (SELECT max(start_date)
3613 FROM okc_article_versions ver1
3614 WHERE ver1.article_id = ver.article_id
3615 --AND ver1.start_date <= cp_effective_date
3616 --AND ver1.article_status = 'APPROVED'
3617 );
3618
3619 -- new cursor to get draft/rejected versions from table OKC_TMPL_DRAFT_CLAUSES
3620 CURSOR l_pen_app_selected_ver_csr IS
3621 SELECT ARTV.article_version_id
3622 FROM OKC_TMPL_DRAFT_CLAUSES TMPLC,
3623 OKC_ARTICLE_VERSIONS ARTV
3624 WHERE TMPLC.template_id = p_doc_id
3625 AND TMPLC.article_id = p_article_id
3626 AND TMPLC.selected_yn = 'Y'
3627 AND ARTV.article_id = TMPLC.article_id
3628 AND ARTV.article_version_id = TMPLC.article_version_id
3629 AND ARTV.article_status = 'PENDING_APPROVAL'
3630 AND EXISTS (SELECT 1 FROM OKC_K_ARTICLES_B KART
3631 WHERE KART.document_type = p_doc_type
3632 AND KART.document_id = p_doc_id
3633 AND KART.sav_sae_id = TMPLC.article_id);
3634
3635
3636 CURSOR l_approved_ver_csr(cp_effective_date DATE) IS
3637 SELECT ver.article_version_id
3638 FROM okc_articles_all art,
3639 okc_article_versions ver
3640 WHERE art.article_id = p_article_id
3641 AND art.article_id = ver.article_id
3642 AND ver.article_status IN ('APPROVED','EXPIRED','ON_HOLD')
3643 AND cp_effective_date BETWEEN ver.start_date AND NVL(ver.end_date,cp_effective_date+1);
3644
3645 CURSOR l_approved_latest_ver_csr(cp_effective_date DATE) IS
3646 SELECT ver.article_version_id
3647 FROM okc_articles_all art,
3648 okc_article_versions ver
3649 WHERE art.article_id = p_article_id
3650 AND art.article_id = ver.article_id
3651 AND ver.start_date <= cp_effective_date
3652 AND ver.article_status IN ('APPROVED','EXPIRED','ON_HOLD')
3653 AND ver.start_date = (SELECT max(start_date)
3654 FROM okc_article_versions ver1
3655 WHERE ver1.article_id = ver.article_id
3656 AND ver1.start_date <= cp_effective_date
3657 AND ver1.article_status IN ('APPROVED','EXPIRED','ON_HOLD'));
3658
3659 -- cursor to get article org id and local org id
3660 CURSOR l_get_article_org_csr(b_article_id NUMBER) IS
3661 SELECT org_id,
3662 mo_global.get_current_org_id() current_org_id
3663 FROM OKC_ARTICLES_ALL
3664 WHERE article_id = b_article_id;
3665
3666 -- cursor to get latest adopted article version id for global article
3667 CURSOR l_get_max_adopted_article_csr(b_article_id IN NUMBER, b_current_org_id IN NUMBER) IS
3668 SELECT ADP.GLOBAL_ARTICLE_VERSION_ID
3669 FROM OKC_ARTICLE_ADOPTIONS ADP,
3670 OKC_ARTICLE_VERSIONS VER
3671 WHERE ADP.GLOBAL_ARTICLE_VERSION_ID = VER.article_version_id
3672 AND VER.article_id = b_article_id
3673 AND ADP.LOCAL_ORG_ID = b_current_org_id
3674 --AND ADP.adoption_status IN ( 'APPROVED', 'ON_HOLD')
3675 AND ADP.ADOPTION_TYPE IN ('ADOPTED','AVAILABLE')
3676 ORDER BY VER.article_version_number desc,
3677 DECODE(ADP.adoption_status,'APPROVED','001','ON_HOLD','001','002') desc;
3678
3679
3680 BEGIN
3681 -- determine the effective date
3682 IF ((p_end_date IS NULL) OR (TRUNC(p_end_date) >= TRUNC(SYSDATE))) THEN
3683 IF TRUNC(p_start_date) < TRUNC(SYSDATE) THEN
3684 l_effective_date := TRUNC(sysdate);
3685 ELSE
3686 l_effective_date := TRUNC(p_start_date);
3687 END IF;
3688
3689 ELSIF TRUNC(p_end_date) < TRUNC(SYSDATE) THEN
3690 l_effective_date := TRUNC(p_end_date);
3691 END IF;
3692
3693 -- check if this is a global article in local template
3694 OPEN l_get_article_org_csr(p_article_id);
3695 FETCH l_get_article_org_csr INTO l_article_org_id, l_current_org_id;
3696 CLOSE l_get_article_org_csr;
3697
3698 -- if the org ids are different then display the lastest adopted version
3699 IF l_article_org_id <> l_current_org_id THEN
3700 OPEN l_get_max_adopted_article_csr(p_article_id, l_current_org_id);
3701 FETCH l_get_max_adopted_article_csr INTO l_article_version_id;
3702 CLOSE l_get_max_adopted_article_csr;
3703
3704 RETURN l_article_version_id;
3705 END IF; -- global article in local template
3706
3707
3708 IF p_status_code IN ('DRAFT','REJECTED','REVISION') THEN
3709
3710 -- begin changes
3711 IF (NVL(p_doc_type,'*') = 'TEMPLATE' AND p_doc_id is not null) THEN
3712 OPEN l_draft_selected_ver_csr;
3713 FETCH l_draft_selected_ver_csr INTO l_article_version_id;
3714
3715 IF l_draft_selected_ver_csr%NOTFOUND THEN
3716 l_stop := FALSE;
3717 END IF;
3718 CLOSE l_draft_selected_ver_csr;
3719 ELSE
3720 l_stop := FALSE;
3721 END IF;
3722
3723 IF NOT l_stop THEN
3724 -- end changes
3725
3726 OPEN l_draft_ver_csr(l_effective_date);
3727 FETCH l_draft_ver_csr INTO l_article_version_id;
3728 IF l_draft_ver_csr%NOTFOUND THEN
3729
3730 OPEN l_draft_latest_ver_csr(l_effective_date);
3731 FETCH l_draft_latest_ver_csr INTO l_article_version_id;
3732 CLOSE l_draft_latest_ver_csr;
3733
3734 END IF;
3735 CLOSE l_draft_ver_csr;
3736
3737 END IF;
3738
3739 ELSIF p_status_code IN ('APPROVED','ON_HOLD','PENDING_APPROVAL') THEN
3740 -- begin changes
3741 IF (p_status_code = 'PENDING_APPROVAL' AND
3742 NVL(p_doc_type,'*') = 'TEMPLATE' AND
3743 p_doc_id is not null) THEN
3744
3745 OPEN l_pen_app_selected_ver_csr;
3746 FETCH l_pen_app_selected_ver_csr INTO l_article_version_id;
3747
3748 IF l_pen_app_selected_ver_csr%NOTFOUND THEN
3749 l_stop := FALSE;
3750 END IF;
3751 CLOSE l_pen_app_selected_ver_csr;
3752 ELSE
3753 l_stop := FALSE;
3754 END IF;
3755
3756 IF NOT l_stop THEN
3757 -- end changes
3758
3759 OPEN l_approved_ver_csr(l_effective_date);
3760 FETCH l_approved_ver_csr INTO l_article_version_id;
3761 IF l_approved_ver_csr%NOTFOUND THEN
3762
3763 OPEN l_approved_latest_ver_csr(l_effective_date);
3764 FETCH l_approved_latest_ver_csr INTO l_article_version_id;
3765 CLOSE l_approved_latest_ver_csr;
3766 END IF;
3767 CLOSE l_approved_ver_csr;
3768 END IF;
3769
3770 END IF;
3771
3772 RETURN l_article_version_id;
3773
3774 END Get_latest_tmpl_art_version_id;
3775
3776
3777 ------------------------------------------------------
3778 -- PROCEDURE create_tmpl_clauses_to_submit
3779 -------------------------------------------------------
3780 /*
3781 --11.5.10+
3782 --finds draft clauses to be submitted with template and creates rows in OKC_TMPL_DRAFT_CLAUSES
3783 --returns whether there is a draft clause through x_drafts_present
3784 */
3785 PROCEDURE create_tmpl_clauses_to_submit (
3786 p_api_version IN NUMBER,
3787 p_init_msg_list IN VARCHAR2,
3788 p_template_id IN VARCHAR2,
3789 p_template_start_date IN DATE DEFAULT NULL,
3790 p_template_end_date IN DATE DEFAULT NULL,
3791 p_org_id IN NUMBER,
3792 x_drafts_present OUT NOCOPY VARCHAR2,
3793 x_return_status OUT NOCOPY VARCHAR2,
3794 x_msg_count OUT NOCOPY NUMBER,
3795 x_msg_data OUT NOCOPY VARCHAR2)
3796 IS
3797 l_api_name CONSTANT VARCHAR2(30) := 'create_tmpl_clauses_to_submit';
3798 l_effective_date DATE;
3799 l_article_id OKC.OKC_ARTICLES_ALL.ARTICLE_ID%TYPE;
3800 l_section_name OKC.OKC_SECTIONS_B.HEADING%TYPE;
3801 l_article_label OKC.OKC_SECTIONS_B.LABEL%TYPE;
3802 l_multiple_sections VARCHAR2(1);
3803 l_return_status VARCHAR2(1);
3804 l_start_date OKC.OKC_TERMS_TEMPLATES_ALL.START_DATE%TYPE;
3805 l_end_date OKC.OKC_TERMS_TEMPLATES_ALL.END_DATE%TYPE;
3806 l_row_notfound BOOLEAN;
3807 l_user_id NUMBER;
3808 l_login_id NUMBER;
3809
3810
3811 CURSOR template_csr (cp_template_id NUMBER) is
3812 SELECT START_DATE, END_DATE
3813 FROM OKC_TERMS_TEMPLATES_ALL
3814 WHERE TEMPLATE_ID = cp_template_id;
3815
3816 /* no longer used
3817 CURSOR expert_clauses_csr (cp_org_id NUMBER, cp_effective_date DATE,
3818 cp_template_id NUMBER) IS
3819 SELECT
3820 oav.article_version_id,
3821 oav.article_id,
3822 oav.start_date,
3823 oav.end_date
3824 FROM okc_article_Versions oav,
3825 okc_articles_all oaa
3826 WHERE oav.article_id = oaa.article_id
3827 AND oaa.org_id = cp_org_id
3828 AND oav.article_status IN ('DRAFT','REJECTED')
3829 AND oav.start_date <= cp_effective_date
3830 AND nvl(oav.end_date, nvl(cp_effective_date,sysdate) +1) >= nvl(cp_effective_date,sysdate)
3831 AND oaa.article_id in
3832 (SELECT clause_id from okc_xprt_clauses_v oxc
3833 WHERE oxc.template_id = cp_template_id);
3834 */
3835
3836 CURSOR sec_name_csr (cp_template_id NUMBER, cp_article_id NUMBER)IS
3837 SELECT osb.heading, nvl(oka.label, '-98766554433.77'),osb.label section_label
3838 FROM okc_sections_b osb,okc_k_articles_b oka
3839 WHERE oka.document_id = cp_template_id
3840 AND oka.sav_sae_id = cp_article_id
3841 AND oka.scn_id = osb.id
3842 AND rownum < 3;
3843
3844 --this cursor returns distinct article_ids
3845 CURSOR draft_articles_csr (cp_org_id NUMBER, cp_effective_date DATE,
3846 cp_template_id NUMBER)IS
3847 SELECT oav.article_version_id,
3848 oav.article_id
3849 FROM okc_article_Versions oav,
3850 okc_articles_all oaa
3851 WHERE oav.article_id = oaa.article_id
3852 AND oaa.org_id = cp_org_id
3853 AND oav.article_status IN ('DRAFT','REJECTED')
3854 AND oaa.standard_yn = 'Y'
3855 AND oav.start_date <= cp_effective_date
3856 AND nvl(oav.end_date, nvl(cp_effective_date,sysdate) +1) >= nvl(cp_effective_date,sysdate)
3857 AND oaa.article_id in
3858 (SELECT sav_sae_id from okc_k_articles_b oka
3859 WHERE oka.document_id = cp_template_id
3860 AND oka.document_type='TEMPLATE');
3861
3862 CURSOR valid_ver_csr (cp_article_id NUMBER, cp_article_version_id NUMBER,
3863 cp_template_effective_date DATE) IS
3864 SELECT 'Y' from okc_Article_versions
3865 WHERE article_id = cp_article_id
3866 AND article_version_id <> cp_article_version_id
3867 AND article_status = 'APPROVED'
3868 AND start_date <= cp_template_effective_date
3869 AND nvl(end_date, nvl(cp_template_effective_date,sysdate) +1) >= nvl(cp_template_effective_date,sysdate)
3870 AND rownum < 2;
3871
3872 CURSOR fnd_section_name_csr is
3873 SELECT meaning
3874 FROM fnd_lookups
3875 WHERE lookup_code = 'UNASSIGNED' and lookup_type = 'OKC_ARTICLE_SECTION';
3876
3877 -- muteshev bug#4327485 begin
3878 -- created new cursor selected_yn_csr
3879 -- it takes selected_yn flag value from table OKC_TMPL_DRAFT_CLAUSES
3880 cursor selected_yn_csr( cp_template_id number,
3881 cp_article_id number,
3882 cp_article_version_id number) is
3883 select selected_yn
3884 from OKC_TMPL_DRAFT_CLAUSES
3885 where template_id = cp_template_id
3886 and article_id = cp_article_id
3887 and article_version_id = cp_article_version_id;
3888 -- muteshev bug#4327485 end
3889
3890 TYPE draft_articles_tbl_type is TABLE of draft_articles_csr%ROWTYPE INDEX BY BINARY_INTEGER;
3891 TYPE sec_details_tbl_type is TABLE of sec_name_csr%ROWTYPE INDEX BY BINARY_INTEGER ;
3892 TYPE section_label_tbl_type IS TABLE of OKC_SECTIONS_B.LABEL%TYPE INDEX BY BINARY_INTEGER ;
3893 TYPE article_id_tbl_type IS TABLE of OKC_TMPL_DRAFT_CLAUSES.ARTICLE_ID%TYPE INDEX BY BINARY_INTEGER ;
3894 TYPE article_version_id_tbl_type IS TABLE of OKC_TMPL_DRAFT_CLAUSES.ARTICLE_VERSION_ID%TYPE INDEX BY BINARY_INTEGER ;
3895 TYPE section_name_tbl_type IS TABLE of OKC_TMPL_DRAFT_CLAUSES.SECTION_NAME%TYPE INDEX BY BINARY_INTEGER ;
3896 TYPE article_label_tbl_type IS TABLE of OKC_TMPL_DRAFT_CLAUSES.ARTICLE_LABEL%TYPE INDEX BY BINARY_INTEGER ;
3897 TYPE multiple_scns_yn_tbl_type IS TABLE of OKC_TMPL_DRAFT_CLAUSES.MULTIPLE_SCNS_YN%TYPE INDEX BY BINARY_INTEGER ;
3898 TYPE prev_val_version_yn_tbl_type IS TABLE of OKC_TMPL_DRAFT_CLAUSES.PREV_VAL_VERSION_YN%TYPE INDEX BY BINARY_INTEGER ;
3899 TYPE selected_yn_tbl_type IS TABLE of OKC_TMPL_DRAFT_CLAUSES.SELECTED_YN%TYPE INDEX BY BINARY_INTEGER ;
3900
3901 draft_articles_tbl draft_articles_tbl_type;
3902 sec_details_tbl sec_details_tbl_type;
3903
3904 article_id_tbl article_id_tbl_type;
3905 article_version_id_tbl article_version_id_tbl_type;
3906 section_name_tbl section_name_tbl_type;
3907 article_label_tbl article_label_tbl_type;
3908 t_section_name_tbl section_name_tbl_type;
3909 t_article_label_tbl article_label_tbl_type;
3910 multiple_scns_yn_tbl multiple_scns_yn_tbl_type;
3911 prev_val_version_yn_tbl prev_val_version_yn_tbl_type;
3912 selected_yn_tbl selected_yn_tbl_type;
3913 t_section_label_tbl section_label_tbl_type;
3914 BEGIN
3915
3916 x_return_status := G_RET_STS_SUCCESS;
3917 x_drafts_present := 'N';
3918 l_row_notfound := false;
3919
3920 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3921 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Entering create_tmpl_clauses_to_submit');
3922 END IF;
3923
3924
3925 IF p_template_start_date is NULL THEN
3926
3927 OPEN template_csr (p_template_id);
3928 FETCH template_csr into l_start_date, l_end_date;
3929 l_row_notfound := template_csr%NOTFOUND;
3930 CLOSE template_csr;
3931
3932 IF l_row_notfound THEN
3933 Okc_Api.Set_Message(
3934 p_app_name => G_APP_NAME,
3935 p_msg_name => 'OKC_TERM_INVALID_TEMPLATE_ID');
3936 RAISE FND_API.G_EXC_ERROR;
3937 END IF;
3938
3939 ELSE
3940 l_start_date := p_template_start_date;
3941 l_end_date := p_template_end_date;
3942 END IF;
3943
3944 IF NVL(l_end_date,sysdate) >= sysdate THEN
3945 IF l_start_date > sysdate THEN
3946 l_effective_date := l_start_date;
3947 ELSE
3948 l_effective_date := sysdate;
3949 END IF;
3950 ELSE
3951 l_effective_date := l_end_date;
3952 END IF;
3953
3954 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3955 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'200: Effective date is '||l_effective_date);
3956 END IF;
3957
3958
3959 OPEN draft_articles_csr(p_org_id, l_effective_date, p_template_id);
3960 FETCH draft_articles_csr BULK COLLECT INTO article_version_id_tbl, article_id_tbl;
3961 CLOSE draft_articles_csr;
3962
3963 IF article_id_tbl.COUNT = 0 THEN
3964 x_drafts_present := 'N';
3965 ELSIF article_id_tbl.COUNT > 0 THEN
3966 x_drafts_present := 'Y';
3967 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3968 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'300: Draft clauses exist. Number of Draft clauses: '||draft_articles_tbl.COUNT);
3969 END IF;
3970
3971
3972 FOR i IN article_id_tbl.FIRST..article_id_tbl.LAST LOOP
3973
3974 -- initiliaze all tables so that subscripts are always in sync
3975 section_name_tbl(i) := NULL;
3976 article_label_tbl(i) := NULL;
3977 t_section_name_tbl(i) := NULL;
3978 t_article_label_tbl(i) := NULL;
3979 multiple_scns_yn_tbl(i) := 'N';
3980 prev_val_version_yn_tbl(i) := NULL;
3981 selected_yn_tbl(i) := NULL;
3982
3983 OPEN sec_name_csr(p_template_id , article_id_tbl(i));
3984 FETCH sec_name_csr BULK COLLECT INTO t_section_name_tbl, t_article_label_tbl, t_section_label_tbl;
3985 CLOSE sec_name_csr;
3986
3987 IF t_section_name_tbl.COUNT > 0 Then
3988 IF t_section_name_tbl.COUNT = 2 then
3989 multiple_scns_yn_tbl(i) := 'Y';
3990 ELSE
3991 multiple_scns_yn_tbl(i) := 'N';
3992 END IF;
3993 article_label_tbl(i) := t_article_label_tbl(1);
3994 section_name_tbl(i) := t_section_label_tbl(1) || ' ' || t_section_name_tbl(1);
3995 ELSE
3996 --retrieve 'Unassigned' if there is no section name
3997 OPEN fnd_section_name_csr;
3998 FETCH fnd_section_name_csr INTO section_name_tbl(i);
3999 CLOSE fnd_section_name_csr;
4000 END IF;
4001
4002 OPEN valid_ver_csr(article_id_tbl(i),
4003 article_version_id_tbl(i), l_effective_date);
4004 FETCH valid_ver_csr into prev_val_version_yn_tbl(i);
4005 l_row_notfound := valid_ver_csr%NOTFOUND;
4006 CLOSE valid_ver_csr;
4007
4008 IF l_row_notfound THEN
4009 prev_val_version_yn_tbl(i) := 'N';
4010 ELSE
4011 prev_val_version_yn_tbl(i) := 'Y';
4012 END IF;
4013
4014 -- muteshev bug#4327485 begin
4015 -- instead of unconditionally setting selected_yn := 'Y'
4016 -- use selected_yn_csr and set selected_yn flag accordingly:
4017 -- if %found use cursor result value for selected_yn,
4018 -- if %notfound use selected_yn := 'Y' (by default)
4019 open selected_yn_csr( p_template_id,
4020 article_id_tbl(i),
4021 article_version_id_tbl(i));
4022 fetch selected_yn_csr into selected_yn_tbl(i);
4023 if selected_yn_csr%NOTFOUND then
4024 selected_yn_tbl(i) := 'Y';
4025 end if;
4026 close selected_yn_csr;
4027 -- muteshev bug#4327485 end
4028
4029 END LOOP;
4030
4031 END IF ;
4032
4033 draft_articles_tbl.DELETE;
4034
4035
4036 --delete old records associated with the template id
4037 DELETE FROM OKC_TMPL_DRAFT_CLAUSES
4038 WHERE template_id = p_template_id;
4039
4040 IF article_id_tbl.COUNT > 0 THEN
4041 l_user_id := Fnd_Global.user_id;
4042 l_login_id := Fnd_Global.login_id;
4043
4044 FORALL i IN article_id_tbl.FIRST .. article_id_tbl.LAST
4045
4046 INSERT INTO OKC_TMPL_DRAFT_CLAUSES
4047 (
4048 TEMPLATE_ID,
4049 ARTICLE_ID,
4050 ARTICLE_VERSION_ID,
4051 SECTION_NAME,
4052 ARTICLE_LABEL,
4053 MULTIPLE_SCNS_YN,
4054 PREV_VAL_VERSION_YN,
4055 SELECTED_YN,
4056 WF_SEQ_ID,
4057 OBJECT_VERSION_NUMBER,
4058 CREATED_BY,
4059 CREATION_DATE,
4060 LAST_UPDATE_DATE,
4061 LAST_UPDATED_BY,
4062 LAST_UPDATE_LOGIN
4063 )
4064 VALUES
4065 (
4066 p_template_id,
4067 article_id_tbl(i),
4068 article_version_id_tbl(i),
4069 section_name_tbl(i),
4070 decode(article_label_tbl(i),'-98766554433.77',NULL,article_label_tbl(i)),
4071 multiple_scns_yn_tbl(i),
4072 prev_val_version_yn_tbl(i),
4073 selected_yn_tbl(i),
4074 null,
4075 1,
4076 l_user_id,
4077 sysdate,
4078 sysdate,
4079 l_user_id,
4080 l_login_id);
4081 --COMMIT;
4082 END IF;
4083
4084 EXCEPTION
4085 WHEN FND_API.G_EXC_ERROR THEN
4086 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4087 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'300: Leaving create_tmpl_clauses_to_submit: OKC_API.G_EXCEPTION_ERROR Exception');
4088 END IF;
4089 x_return_status := G_RET_STS_ERROR;
4090
4091 WHEN OTHERS THEN
4092 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4093 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'500: Leaving create_tmpl_clauses_to_submit because of EXCEPTION: '||sqlerrm);
4094 END IF;
4095 Okc_Api.Set_Message(
4096 p_app_name => G_APP_NAME,
4097 p_msg_name => G_UNEXPECTED_ERROR,
4098 p_token1 => G_SQLCODE_TOKEN,
4099 p_token1_value => sqlcode,
4100 p_token2 => G_SQLERRM_TOKEN,
4101 p_token2_value => sqlerrm);
4102 x_return_status := G_RET_STS_ERROR;
4103
4104 END create_tmpl_clauses_to_submit;
4105
4106
4107 ---------------------------------------------------------------------------
4108 -- Overloaded Procedure merge_template_working_copy
4109 ---------------------------------------------------------------------------
4110 /*
4111 -- PROCEDURE merge_template_working_copy, 11.5.10+ overloaded version
4112 -- To be used to merge a working copy of a template is approved and old copy
4113 -- and working copy
4114 -- new out parameter x_parent_template_id returns the template id of the merged template
4115 */
4116 PROCEDURE merge_template_working_copy (
4117 p_api_version IN NUMBER,
4118 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
4119 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
4120 x_return_status OUT NOCOPY VARCHAR2,
4121 x_msg_data OUT NOCOPY VARCHAR2,
4122 x_msg_count OUT NOCOPY NUMBER,
4123
4124 p_template_id IN NUMBER,
4125 x_parent_template_id OUT NOCOPY NUMBER)
4126 IS
4127 l_api_name CONSTANT VARCHAR2(30) := 'merge_template_working_copy';
4128 BEGIN
4129
4130 -- call the existing procedure
4131 merge_template_working_copy (
4132 p_api_version => p_api_version,
4133 p_init_msg_list => p_init_msg_list,
4134 p_commit => p_commit,
4135
4136 x_return_status => x_return_status,
4137 x_msg_data => x_msg_data,
4138 x_msg_count => x_msg_count,
4139
4140 p_template_id => p_template_id);
4141
4142 -- set the out param from the global variable set by the
4143 -- existing procedure
4144 x_parent_template_id := g_parent_template_id;
4145
4146 END merge_template_working_copy;
4147
4148 FUNCTION unadopted_art_exist_on_tmpl(
4149 p_template_id IN NUMBER,
4150 p_org_id IN NUMBER )
4151 RETURN VARCHAR2 IS
4152 l_dummy VARCHAR2(1) := 'N';
4153 l_api_name CONSTANT VARCHAR2(30) := 'unadopted_art_exist_on_tmpl';
4154
4155 -- Fix for the bug# 5011432, reframed the cursor query
4156 CURSOR unadopted_art_chk_csr(lc_tmpl_id NUMBER,
4157 lc_org_id NUMBER) IS
4158 SELECT 'Y'
4159 FROM okc_terms_templates_all tmpl,
4160 okc_k_articles_b kart,
4161 okc_article_versions ver
4162 WHERE tmpl.template_id = lc_tmpl_id
4163 AND kart.document_id = tmpl.template_id
4164 AND kart.document_type = 'TEMPLATE'
4165 AND ver.article_id = kart.sav_sae_id
4166 AND ver.global_yn = 'Y'
4167 AND NOT EXISTS (SELECT 1
4168 FROM okc_article_adoptions adp,
4169 okc_article_versions ver1
4170 WHERE adp.global_article_version_id = ver1.article_version_id
4171 AND ver1.article_id = ver.article_id
4172 AND adp.local_org_id = lc_org_id
4173 AND adp.adoption_status IN ( 'APPROVED', 'ON_HOLD')
4174 AND adp.adoption_type = 'ADOPTED');
4175 -- End of Fix for the bug# 5011432
4176 BEGIN
4177
4178 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4179 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Entered unadopted_art_exist_on_tmpl');
4180 END IF;
4181
4182 OPEN unadopted_art_chk_csr(p_template_id,p_org_id);
4183 FETCH unadopted_art_chk_csr INTO l_dummy;
4184 CLOSE unadopted_art_chk_csr;
4185
4186 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4187 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'200: Fetched unadopted_art_chk_csr');
4188 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'300: l_dummy='||l_dummy);
4189 END IF;
4190
4191 RETURN l_dummy;
4192
4193 EXCEPTION
4194 WHEN OTHERS THEN
4195 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4196 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'400: Leaving unadopted_art_exist_on_tmpl because of EXCEPTION: '||sqlerrm);
4197 END IF;
4198
4199 IF (unadopted_art_chk_csr%ISOPEN) THEN
4200 CLOSE unadopted_art_chk_csr;
4201 END IF;
4202 RETURN l_dummy;
4203
4204 END unadopted_art_exist_on_tmpl;
4205
4206
4207 -- Start of comments
4208 --API name : update_contract_admin
4209 --Type : Private.
4210 --Function : API to update Contract Administrator of Blanket Sales
4211 -- Agreements, Sales Orders and Sales Quotes
4212 --Pre-reqs : None.
4213 --Parameters :
4214 --IN : p_api_version IN NUMBER Required
4215 -- : p_init_msg_list IN VARCHAR2 Optional
4216 -- Default = FND_API.G_FALSE
4217 -- : p_commit IN VARCHAR2 Optional
4218 -- Default = FND_API.G_FALSE
4219 -- : p_doc_ids_tbl IN doc_ids_tbl Required
4220 -- List of document ids whose Contract Administrator to be changed
4221 -- : p_doc_types_tbl IN doc_types_tbl Required
4222 -- List of document types whose Contract Administrator to be changed
4223 -- : p_new_con_admin_user_ids_tbl IN new_con_admin_user_ids_tbl Required
4224 -- List of new Contract Administrator ids
4225 --OUT : x_return_status OUT VARCHAR2(1)
4226 -- : x_msg_count OUT NUMBER
4227 -- : x_msg_data OUT VARCHAR2(2000)
4228 --Note :
4229 -- End of comments
4230 PROCEDURE update_contract_admin(
4231 p_api_version IN NUMBER,
4232 p_init_msg_list IN VARCHAR2,
4233 p_commit IN VARCHAR2,
4234 p_doc_ids_tbl IN doc_ids_tbl,
4235 p_doc_types_tbl IN doc_types_tbl,
4236 p_new_con_admin_user_ids_tbl IN new_con_admin_user_ids_tbl,
4237 x_return_status OUT NOCOPY VARCHAR2,
4238 x_msg_count OUT NOCOPY NUMBER,
4239 x_msg_data OUT NOCOPY VARCHAR2)
4240 IS
4241
4242 l_api_name VARCHAR2(30);
4243 l_api_version NUMBER;
4244 dml_errors exception;
4245
4246 BEGIN
4247
4248 l_api_name := 'update_contract_admin';
4249 l_api_version := 1.0;
4250
4251 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4252 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
4253 'Entered OKC_TERMS_UTIL_PVT.update_contract_admin');
4254 END IF;
4255
4256 -- Standard Start of API savepoint
4257 SAVEPOINT update_contract_admin;
4258
4259 -- Standard call to check for call compatibility.
4260 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
4261 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4262 END IF;
4263
4264 -- Initialize message list if p_init_msg_list is set to TRUE.
4265 IF FND_API.to_Boolean( p_init_msg_list ) THEN
4266 FND_MSG_PUB.initialize;
4267 END IF;
4268
4269 -- Initialize API return status to success
4270 x_return_status := FND_API.G_RET_STS_SUCCESS;
4271
4272 FORALL i IN p_doc_ids_tbl.FIRST..p_doc_ids_tbl.LAST
4273 UPDATE okc_template_usages
4274 SET contract_admin_id = p_new_con_admin_user_ids_tbl(i)
4275 WHERE document_id = p_doc_ids_tbl(i)
4276 AND document_type = p_doc_types_tbl(i);
4277
4278 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4279 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
4280 'Leaving OKC_TERMS_UTIL_PVT.update_contract_admin');
4281 END IF;
4282
4283 EXCEPTION
4284
4285 WHEN OTHERS THEN
4286 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4287 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
4288 g_module || l_api_name,
4289 'Leaving OKC_TERMS_UTIL_PVT.update_contract_admin because of EXCEPTION: ' || sqlerrm);
4290 END IF;
4291
4292 ROLLBACK TO update_contract_admin;
4293
4294 x_return_status := FND_API.G_RET_STS_ERROR;
4295 FND_MSG_PUB.Count_And_Get(
4296 p_count => x_msg_count,
4297 p_data => x_msg_data);
4298
4299
4300 END update_contract_admin;
4301
4302
4303 -- Start of comments
4304 --API name : get_sales_group_con_admin
4305 --Type : Private.
4306 --Function : API to get Contract Administrator of a business document
4307 -- according to Sales Group Assignment
4308 --Pre-reqs : None.
4309 --Parameters :
4310 --IN : p_api_version IN NUMBER Required
4311 -- : p_init_msg_list IN VARCHAR2 Optional
4312 -- Default = FND_API.G_FALSE
4313 -- : p_doc_id IN NUMBER Required
4314 -- Id of document whose Contract Administrator is required
4315 -- : p_doc_type IN VARCHAR2 Required
4316 -- Type of document whose Contract Administrator is required
4317 --OUT : x_new_con_admin_user_id OUT NUMBER
4318 -- New Contract Administrator id
4319 -- : x_return_status OUT VARCHAR2(1)
4320 -- : x_msg_count OUT NUMBER
4321 -- : x_msg_data OUT VARCHAR2(2000)
4322 --Note :
4323 -- End of comments
4324 PROCEDURE get_sales_group_con_admin(
4325 p_api_version IN NUMBER,
4326 p_init_msg_list IN VARCHAR2,
4327 p_doc_id IN NUMBER,
4328 p_doc_type IN VARCHAR2,
4329 x_new_con_admin_user_id OUT NOCOPY NUMBER,
4330 x_return_status OUT NOCOPY VARCHAR2,
4331 x_msg_count OUT NOCOPY NUMBER,
4332 x_msg_data OUT NOCOPY VARCHAR2)
4333 IS
4334
4335 l_api_name VARCHAR2(30);
4336 l_api_version NUMBER;
4337 l_primary_salesperson_id aso_quote_headers_all.resource_id%TYPE;
4338 l_sales_group_id aso_quote_headers_all.resource_grp_id%TYPE;
4339 l_quote_org_id aso_quote_headers_all.org_id%TYPE;
4340 l_quote_number aso_quote_headers_all.quote_number%TYPE;
4341
4342 CURSOR quote_details_csr IS
4343 SELECT resource_id,
4344 resource_grp_id,
4345 quote_number
4346 FROM aso_quote_headers_all
4347 WHERE quote_header_id = p_doc_id;
4348
4349 CURSOR quote_sales_team_csr(p_quote_number IN aso_quote_headers_all.quote_number%TYPE) IS
4350 SELECT sre.user_id
4351 FROM aso_quote_accesses sales_team,
4352 jtf_rs_role_relations rr,
4353 jtf_rs_roles_b rl,
4354 jtf_rs_resource_extns sre
4355 WHERE sales_team.quote_number = p_quote_number
4356 AND rr.ROLE_ID = rl.ROLE_ID
4357 AND NVL(rr.delete_flag,'N') <> 'Y'
4358 AND rr.Role_resource_type = 'RS_INDIVIDUAL'
4359 -- For Bug# 6343627 AND (rr.end_date_active IS NULL OR rr.end_date_active >= SYSDATE)
4360 AND (rr.end_date_active IS NULL OR rr.end_date_active > SYSDATE)
4361 AND (
4362 (rl.role_type_code = 'CONTRACTS' AND rl.role_code = 'CONTRACTS_ADMIN')
4363 OR
4364 (rl.role_type_code = 'SALES' AND rl.role_code = 'CONTRACTS_ADMIN')
4365 OR
4366 (rl.role_code = 'CONTRACTS_ADMIN')
4367 )
4368 AND rr.role_resource_id = sales_team.resource_id
4369 AND sre.resource_id = sales_team.resource_id;
4370
4371 CURSOR con_admin_role_member_csr(p_sales_group_id IN aso_quote_headers_all.resource_grp_id%TYPE) IS
4372 SELECT sre.user_id
4373 FROM jtf_rs_group_members srg,
4374 jtf_rs_resource_extns sre,
4375 jtf_rs_role_relations rr,
4376 jtf_rs_roles_b rl
4377 WHERE srg.group_id = p_sales_group_id
4378 AND srg.resource_id = sre.resource_id
4379 AND NVL(srg.delete_flag,'N') <> 'Y'
4380 AND rr.ROLE_ID = rl.ROLE_ID
4381 AND NVL(rr.delete_flag,'N') <> 'Y'
4382 -- For Bug# 6343627 AND AND rr.Role_resource_type = 'RS_INDIVIDUAL'
4383 AND rr.Role_resource_type = 'RS_GROUP_MEMBER'
4384 -- For Bug# 6343627 AND (rr.end_date_active IS NULL OR rr.end_date_active >= SYSDATE)
4385 AND (rr.end_date_active IS NULL OR rr.end_date_active > SYSDATE)
4386 AND rl.role_type_code = 'CONTRACTS'
4387 AND rl.role_code = 'CONTRACTS_ADMIN'
4388 -- For Bug# 6343627 AND rr.role_resource_id = sre.resource_id;
4389 AND rr.role_resource_id = srg.GROUP_MEMBER_ID;
4390
4391 CURSOR parent_group_csr(p_sales_group_id IN aso_quote_headers_all.resource_grp_id%TYPE) IS
4392 SELECT related_group_id
4393 FROM jtf_rs_grp_relations
4394 WHERE group_id = p_sales_group_id
4395 AND relation_type = 'PARENT_GROUP';
4396
4397 BEGIN
4398
4399 l_api_name := 'get_sales_group_con_admin';
4400 l_api_version := 1.0;
4401
4402 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4403 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
4404 'Entered OKC_TERMS_UTIL_PVT.get_sales_group_con_admin');
4405 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
4406 'p_doc_id: ' || p_doc_id);
4407 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
4408 'p_doc_type: ' || p_doc_type);
4409 END IF;
4410
4411 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4412 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
4413 'Getting current Sales Quote details');
4414 END IF;
4415
4416 -- Get details of the current Sales Quote
4417 OPEN quote_details_csr;
4418 FETCH quote_details_csr INTO l_primary_salesperson_id, l_sales_group_id, l_quote_number;
4419 CLOSE quote_details_csr;
4420
4421 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4422 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
4423 'Getting Contract Administrator from Sales Quote''s Sales Team');
4424 END IF;
4425
4426 -- From Sales Team of the Quote, pick the user with a role type of Contracts and
4427 -- a role of Contract Administrator, if this isn't available, pick the user with
4428 -- a role type of Sales and a role of Contract Administrator, and if this isn't
4429 -- available, pick the user with any role type and a role of Contract Administrator
4430 OPEN quote_sales_team_csr(l_quote_number);
4431
4432 -- Even though the cursor returns multiple rows we'll consider only the first row in
4433 -- the resultset, so we're not looping through the resultset
4434 FETCH quote_sales_team_csr INTO x_new_con_admin_user_id;
4435 CLOSE quote_sales_team_csr;
4436
4437
4438 -- If no Contract Administrator defined on the sales team,
4439 IF(x_new_con_admin_user_id IS NULL) THEN
4440
4441 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4442 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
4443 'No Contract Administrator found in Sales Quote''s Sales Team');
4444 END IF;
4445
4446 -- If current Quote has a Primary Sales Group selected
4447 IF(l_sales_group_id IS NOT NULL) THEN
4448
4449 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4450 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
4451 'Getting Contract Administrator from current Quote''s Primary Salesperson''s Primary Sales Group');
4452 END IF;
4453
4454 -- Look at the primary sales group in the Quote for a Contract Administrator
4455 OPEN con_admin_role_member_csr(l_sales_group_id);
4456 FETCH con_admin_role_member_csr INTO x_new_con_admin_user_id;
4457 CLOSE con_admin_role_member_csr;
4458
4459 -- In the case where neither the sales team nor the primary sales group have a contract administrator defined,
4460 IF(x_new_con_admin_user_id IS NULL) THEN
4461
4462 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4463 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
4464 'No Contract Administrator found in the current Quote''s Primary Salesperson''s Primary Sales Group');
4465 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
4466 'Getting parent group of the current Quote''s Primary Salesperson''s Primary Sales Group');
4467 END IF;
4468
4469 -- Get parent of the primary Salesperson's primary sales group in the Quote
4470 OPEN parent_group_csr(l_sales_group_id);
4471 FETCH parent_group_csr INTO l_sales_group_id;
4472 CLOSE parent_group_csr;
4473
4474 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4475 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
4476 'Getting Contract Administrator from parent of current Quote''s Primary Salesperson''s Primary Sales Group');
4477 END IF;
4478
4479 -- Check the parent sales group for a Contract Administrator
4480 OPEN con_admin_role_member_csr(l_sales_group_id);
4481 FETCH con_admin_role_member_csr INTO x_new_con_admin_user_id;
4482 CLOSE con_admin_role_member_csr;
4483
4484 END IF; -- End of (x_new_con_admin_user_id IS NULL)
4485
4486 END IF; -- End of (l_sales_group_id IS NOT NULL)
4487
4488 END IF; -- End of (x_new_con_admin_user_id IS NULL)
4489
4490 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4491 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
4492 'New Contract Administrator Id ' || x_new_con_admin_user_id);
4493 END IF;
4494
4495
4496 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4497 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
4498 'Leaving OKC_TERMS_UTIL_PVT.get_sales_group_con_admin');
4499 END IF;
4500
4501 EXCEPTION
4502
4503 WHEN OTHERS THEN
4504 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4505 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
4506 g_module || l_api_name,
4507 'Leaving OKC_TERMS_UTIL_PVT.get_sales_group_con_admin because of EXCEPTION: ' || sqlerrm);
4508 END IF;
4509
4510 x_return_status := FND_API.G_RET_STS_ERROR;
4511 FND_MSG_PUB.Count_And_Get(
4512 p_count => x_msg_count,
4513 p_data => x_msg_data);
4514
4515 --close cursors
4516 IF (quote_details_csr%ISOPEN) THEN
4517 CLOSE quote_details_csr ;
4518 END IF;
4519 IF (quote_sales_team_csr%ISOPEN) THEN
4520 CLOSE quote_sales_team_csr ;
4521 END IF;
4522 IF (con_admin_role_member_csr%ISOPEN) THEN
4523 CLOSE con_admin_role_member_csr ;
4524 END IF;
4525 IF (parent_group_csr%ISOPEN) THEN
4526 CLOSE parent_group_csr ;
4527 END IF;
4528
4529 END get_sales_group_con_admin;
4530
4531 FUNCTION has_uploaded_terms(p_document_type IN VARCHAR2,
4532 p_document_id IN NUMBER
4533 )
4534 RETURN Varchar2 IS
4535 l_has_uploaded_terms VARCHAR2(1);
4536
4537 CURSOR l_review_uploaded_terms_csr IS
4538 SELECT 'Y'
4539 FROM okc_review_upld_terms rev
4540 WHERE rev.document_type = p_document_type
4541 AND rev.document_id = p_document_id
4542 ;
4543 BEGIN
4544 OPEN l_review_uploaded_terms_csr;
4545 FETCH l_review_uploaded_terms_csr INTO l_has_uploaded_terms;
4546 IF l_has_uploaded_terms = 'Y' THEN
4547 RETURN 'Y';
4548 else
4549 RETURN 'N';
4550 END IF;
4551 CLOSE l_review_uploaded_terms_csr;
4552
4553 EXCEPTION
4554 WHEN OTHERS THEN
4555 RETURN 'N';
4556 END has_uploaded_terms;
4557
4558
4559 FUNCTION is_terms_locked(p_document_type IN VARCHAR2,
4560 p_document_id IN NUMBER
4561 )
4562 RETURN Varchar2 IS
4563 l_terms_locked VARCHAR2(1);
4564
4565 CURSOR l_terms_locked_csr IS
4566 SELECT lock_terms_flag
4567 FROM okc_template_usages usg
4568 WHERE usg.document_type = p_document_type
4569 AND usg.document_id = p_document_id
4570 ;
4571 BEGIN
4572 OPEN l_terms_locked_csr;
4573 FETCH l_terms_locked_csr INTO l_terms_locked;
4574 IF l_terms_locked = 'Y' THEN
4575 RETURN 'Y';
4576 else
4577 RETURN 'N';
4578 END IF;
4579 CLOSE l_terms_locked_csr;
4580
4581 EXCEPTION
4582 WHEN OTHERS THEN
4583 RETURN 'N';
4584 END is_terms_locked;
4585
4586
4587
4588 FUNCTION get_default_contract_admin_id(p_document_type IN VARCHAR2,
4589 p_document_id IN NUMBER)
4590 RETURN NUMBER IS
4591 l_api_version CONSTANT NUMBER := 1;
4592 l_default_ctrt_admin_id NUMBER;
4593 l_return_status VARCHAR2(150);
4594 l_msg_data VARCHAR2(2000);
4595 l_msg_count NUMBER;
4596 BEGIN
4597
4598 IF (p_document_type <> 'QUOTE') THEN
4599 RETURN NULL;
4600 END IF;
4601 get_sales_group_con_admin(
4602 p_api_version => l_api_version,
4603 p_init_msg_list => FND_API.G_FALSE,
4604 p_doc_id => p_document_id,
4605 p_doc_type => p_document_type,
4606 x_new_con_admin_user_id => l_default_ctrt_admin_id,
4607 x_return_status => l_return_status,
4608 x_msg_count => l_msg_count,
4609 x_msg_data => l_msg_data);
4610
4611 RETURN l_default_ctrt_admin_id;
4612 EXCEPTION
4613 WHEN OTHERS THEN
4614 RETURN NULL;
4615 END get_default_contract_admin_id;
4616
4617 FUNCTION get_contract_admin_name(p_contract_admin_id NUMBER)
4618 RETURN VARCHAR2 IS
4619 l_contract_admin_name PER_ALL_PEOPLE_F.FULL_NAME%TYPE := NULL;
4620
4621 CURSOR get_ctrt_admin_name IS
4622 select adminppl.full_name
4623 from fnd_user ctrtadm, PER_ALL_PEOPLE_F adminppl
4624 where p_contract_admin_id = ctrtadm.user_id(+)
4625 and ctrtadm.employee_id = adminppl.person_id(+)
4626 and adminppl.effective_start_date = adminppl.start_date;
4627 BEGIN
4628
4629 IF (p_contract_admin_id is NULL) THEN
4630 RETURN NULL;
4631 END IF;
4632
4633 OPEN get_ctrt_admin_name;
4634 FETCH get_ctrt_admin_name into l_contract_admin_name;
4635 CLOSE get_ctrt_admin_name;
4636 RETURN l_contract_admin_name;
4637 EXCEPTION
4638 WHEN OTHERS THEN
4639 RETURN NULL;
4640 END get_contract_admin_name;
4641
4642 PROCEDURE get_default_contract_admin(
4643 p_api_version IN NUMBER,
4644 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4645
4646 p_document_type IN VARCHAR2,
4647 p_document_id IN NUMBER,
4648 x_has_default_contract_admin OUT NOCOPY VARCHAR2,
4649 x_def_contract_admin_name OUT NOCOPY VARCHAR2,
4650 x_def_contract_admin_id OUT NOCOPY NUMBER,
4651 x_return_status OUT NOCOPY VARCHAR2,
4652 x_msg_data OUT NOCOPY VARCHAR2,
4653 x_msg_count OUT NOCOPY NUMBER
4654 )
4655
4656 IS
4657 l_api_version CONSTANT NUMBER := 1;
4658 l_api_name CONSTANT VARCHAR2(30) := 'get_default_contract_admin';
4659 BEGIN
4660
4661 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4662 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'8000: Entered get_default_contract_admin');
4663 END IF;
4664
4665 -- Standard call to check for call compatibility.
4666 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
4667 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4668 END IF;
4669
4670 -- Initialize message list if p_init_msg_list is set to TRUE.
4671 IF FND_API.to_Boolean( p_init_msg_list ) THEN
4672 FND_MSG_PUB.initialize;
4673 END IF;
4674
4675 x_return_status := G_RET_STS_SUCCESS;
4676 x_def_contract_admin_id := NULL;
4677 x_def_contract_admin_name := NULL;
4678 x_has_default_contract_admin := 'N';
4679 x_def_contract_admin_id := get_default_contract_admin_id(p_document_type, p_document_id );
4680 if(x_def_contract_admin_id is not null) then
4681 x_has_default_contract_admin := 'Y';
4682 x_def_contract_admin_name := get_contract_admin_name(x_def_contract_admin_id);
4683 end if;
4684
4685
4686 -- Standard call to get message count and if count is 1, get message info.
4687 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
4688
4689 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4690 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'8100: get_default_contract_admin');
4691 END IF;
4692
4693 EXCEPTION
4694 WHEN OTHERS THEN
4695
4696 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4697 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'8200: Leaving get_default_contract_admin because of EXCEPTION:'||sqlerrm);
4698 END IF;
4699 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
4700 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
4701 END IF;
4702 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
4703 x_return_status := G_RET_STS_UNEXP_ERROR ;
4704 END get_default_contract_admin;
4705
4706 -- This is a temporary api that has been created to be
4707 -- used only when the layout template code is not present in session
4708 -- The layout template code is fetched based on the
4709 -- Business Document Type
4710 -- Business Document Type class
4711 -- Business Document Id
4712 -- Operating Unit
4713 FUNCTION get_layout_template_code(
4714 p_doc_type IN VARCHAR2,
4715 p_doc_type_class IN VARCHAR2,
4716 p_doc_id IN NUMBER,
4717 p_org_id IN NUMBER)
4718 RETURN Varchar2 IS
4719 l_doc_type varchar2(10);
4720 l_doc_sub_type varchar2(30);
4721 l_layout_template_code varchar2(30) := 'E';
4722
4723 cursor get_po_lay_templ_csr(l_doc_type VARCHAR2,l_doc_sub_type VARCHAR2) is
4724 -- Fix for bug# 5010387, replaced po_document_types_v with po_document_types_all_b and added org_id condition
4725 SELECT
4726 contract_template_code
4727 FROM po_document_types_all_b
4728 WHERE org_id = p_org_id
4729 AND document_type_code = l_doc_type
4730 AND document_subtype= l_doc_sub_type;
4731
4732 cursor get_souring_lay_templ_csr is
4733 SELECT
4734 podoctypes.contract_template_code
4735 FROM pon_auction_headers_all pah,
4736 po_document_types_all_b podoctypes,
4737 pon_auc_doctypes pac
4738 WHERE auction_header_id = p_doc_id
4739 AND pah.doctype_id = pac.doctype_id
4740 AND pah.org_id = podoctypes.org_id
4741 AND pac.document_type_code = podoctypes.document_type_code;
4742
4743 cursor get_bsa_lay_templ_csr is
4744 SELECT
4745 xdb.template_code
4746 FROM oe_blanket_headers_all oeb,
4747 oe_transaction_types_all otl,
4748 xdo_templates_b xdb
4749 WHERE oeb.order_type_id = otl.transaction_type_id
4750 AND oeb.header_id = p_doc_id
4751 AND otl.layout_template_id = xdb.template_id;
4752
4753 cursor get_order_lay_templ_csr is
4754 SELECT
4755 xdb.template_code
4756 FROM oe_order_headers_all oeb,
4757 oe_transaction_types_vl otl,
4758 xdo_templates_b xdb
4759 WHERE oeb.order_type_id = otl.transaction_type_id
4760 AND oeb.header_id = p_doc_id
4761 AND otl.layout_template_id = xdb.template_id;
4762
4763 --ER Structured Terms Authoring in Repository - strivedi
4764 CURSOR get_rep_lay_templ_csr IS
4765 SELECT
4766 xdb.template_code
4767 FROM
4768 okc_terms_templates_all otta,
4769 okc_template_usages_v otuv,
4770 xdo_templates_b xdb
4771 WHERE otuv.document_id = p_doc_id
4772 AND otuv.document_type = p_doc_type
4773 AND otuv.template_id = otta.template_id
4774 AND otta.print_template_id = xdb.template_id;
4775
4776 BEGIN
4777 IF p_doc_type_class = 'PO' THEN
4778 MO_GLOBAL.INIT('PO');
4779 mo_global.set_policy_context('S',p_org_id);
4780 l_doc_type := substr(p_doc_type,1,instr(p_doc_type,'_')-1);
4781 l_doc_sub_type := substr(p_doc_type,instr(p_doc_type,'_')+1,length(p_doc_type)- instr(p_doc_type,'_'));
4782 open get_po_lay_templ_csr(l_doc_type,l_doc_sub_type);
4783 fetch get_po_lay_templ_csr into l_layout_template_code;
4784 close get_po_lay_templ_csr;
4785 ELSIF p_doc_type_class = 'SOURCING' THEN
4786 MO_GLOBAL.INIT('PON');
4787 mo_global.set_policy_context('S',p_org_id);
4788 open get_souring_lay_templ_csr;
4789 fetch get_souring_lay_templ_csr into l_layout_template_code;
4790 close get_souring_lay_templ_csr;
4791 ELSIF p_doc_type_class = 'SO' or p_doc_type_class = 'BSA' THEN
4792 MO_GLOBAL.INIT('ONT');
4793 mo_global.set_policy_context('S',204);
4794 IF p_doc_type = 'O' THEN
4795 open get_order_lay_templ_csr;
4796 fetch get_order_lay_templ_csr into l_layout_template_code;
4797 close get_order_lay_templ_csr;
4798 ELSIF p_doc_type = 'B' THEN
4799 open get_bsa_lay_templ_csr;
4800 fetch get_bsa_lay_templ_csr into l_layout_template_code;
4801 close get_bsa_lay_templ_csr;
4802 END IF;
4803 ELSIF p_doc_type_class = 'QUOTE' THEN
4804 select fnd_profile.value('ASO_DEFAULT_LAYOUT_TEMPLATE') into l_layout_template_code from dual;
4805
4806 --ER Structured Terms Authoring in Repository - strivedi
4807 ELSIF p_doc_type_class = 'REPOSITORY' THEN
4808 OPEN get_rep_lay_templ_csr;
4809 fetch get_rep_lay_templ_csr into l_layout_template_code;
4810 CLOSE get_rep_lay_templ_csr;
4811 ELSE
4812 l_layout_template_code := 'E';
4813 END IF;
4814 return l_layout_template_code;
4815
4816 EXCEPTION
4817 WHEN OTHERS THEN
4818 RETURN 'E';
4819 END get_layout_template_code;
4820
4821
4822 --For R12 MSWord@WaySync
4823
4824 PROCEDURE lock_contract(
4825 p_api_version IN NUMBER,
4826 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4827
4828 p_commit IN Varchar2,
4829 p_document_type IN VARCHAR2,
4830 p_document_id IN NUMBER,
4831 x_return_status OUT NOCOPY VARCHAR2,
4832 x_msg_data OUT NOCOPY VARCHAR2,
4833 x_msg_count OUT NOCOPY NUMBER)
4834 IS
4835 l_api_version CONSTANT NUMBER := 1;
4836 l_api_name CONSTANT VARCHAR2(30) := 'lock_contract';
4837
4838 BEGIN
4839
4840 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4841 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'8000: Entered lock_contract');
4842 END IF;
4843
4844 -- Standard call to check for call compatibility.
4845 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
4846 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4847 END IF;
4848
4849 -- Initialize message list if p_init_msg_list is set to TRUE.
4850 IF FND_API.to_Boolean( p_init_msg_list ) THEN
4851 FND_MSG_PUB.initialize;
4852 END IF;
4853
4854 x_return_status := G_RET_STS_SUCCESS;
4855 OKC_TEMPLATE_USAGES_GRP.update_template_usages(
4856 p_api_version => l_api_version,
4857 p_init_msg_list => p_init_msg_list ,
4858 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4859 p_commit => FND_API.G_FALSE,
4860 x_return_status => x_return_status,
4861 x_msg_count => x_msg_count,
4862 x_msg_data => x_msg_data,
4863 p_document_type => p_document_type,
4864 p_document_id => p_document_id,
4865 p_lock_terms_flag => 'Y',
4866 p_locked_by_user_id => FND_GLOBAL.user_id);
4867
4868
4869 -- Standard call to get message count and if count is 1, get message info.
4870 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
4871
4872 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4873 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'8100: Leaving lock_contract');
4874 END IF;
4875
4876 EXCEPTION
4877 WHEN OTHERS THEN
4878
4879 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4880 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'8200: Leaving lock_contract because of EXCEPTION:'||sqlerrm);
4881 END IF;
4882 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
4883 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
4884 END IF;
4885 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
4886 x_return_status := G_RET_STS_UNEXP_ERROR ;
4887 END lock_contract;
4888
4889 --For R12: MSWord2WaySync
4890 PROCEDURE unlock_contract(
4891 p_api_version IN NUMBER,
4892 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4893
4894 p_commit IN Varchar2,
4895 p_document_type IN VARCHAR2,
4896 p_document_id IN NUMBER,
4897 x_return_status OUT NOCOPY VARCHAR2,
4898 x_msg_data OUT NOCOPY VARCHAR2,
4899 x_msg_count OUT NOCOPY NUMBER)
4900 IS
4901 l_api_version CONSTANT NUMBER := 1;
4902 l_api_name CONSTANT VARCHAR2(30) := 'unlock_contract';
4903 BEGIN
4904
4905 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4906 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'8000: Entered lock_contract');
4907 END IF;
4908
4909 -- Standard call to check for call compatibility.
4910 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
4911 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4912 END IF;
4913
4914 -- Initialize message list if p_init_msg_list is set to TRUE.
4915 IF FND_API.to_Boolean( p_init_msg_list ) THEN
4916 FND_MSG_PUB.initialize;
4917 END IF;
4918
4919 x_return_status := G_RET_STS_SUCCESS;
4920 OKC_TEMPLATE_USAGES_GRP.update_template_usages(
4921 p_api_version => l_api_version,
4922 p_init_msg_list => p_init_msg_list ,
4923 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4924 p_commit => FND_API.G_FALSE,
4925 x_return_status => x_return_status,
4926 x_msg_count => x_msg_count,
4927 x_msg_data => x_msg_data,
4928 p_document_type => p_document_type,
4929 p_document_id => p_document_id,
4930 p_lock_terms_flag => 'N');
4931
4932
4933 -- Standard call to get message count and if count is 1, get message info.
4934 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
4935
4936 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4937 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'8100: Leaving lock_contract');
4938 END IF;
4939
4940 EXCEPTION
4941 WHEN OTHERS THEN
4942
4943 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4944 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'8200: Leaving lock_contract because of EXCEPTION:'||sqlerrm);
4945 END IF;
4946 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
4947 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
4948 END IF;
4949 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
4950 x_return_status := G_RET_STS_UNEXP_ERROR ;
4951 END unlock_contract;
4952
4953 FUNCTION get_sys_last_upd_date (
4954 p_document_type IN VARCHAR2,
4955 p_document_id IN NUMBER)
4956 RETURN DATE IS
4957
4958 l_article_change_date date;
4959 l_section_change_date date;
4960 l_variable_change_date date;
4961 l_sys_last_update_date date;
4962 l_contract_source_code OKC_TEMPLATE_USAGES.CONTRACT_SOURCE_CODE%TYPE;
4963
4964 Cursor l_get_max_art_date_csr IS
4965 SELECT max(Nvl(LAST_UPDATE_DATE,CREATION_DATE))
4966 FROM OKC_K_ARTICLES_B
4967 WHERE DOCUMENT_TYPE=p_document_type
4968 AND DOCUMENT_ID=p_document_id;
4969
4970 Cursor l_get_max_scn_date_csr IS
4971 SELECT max(Nvl(LAST_UPDATE_DATE,CREATION_DATE))
4972 FROM OKC_SECTIONS_B
4973 WHERE DOCUMENT_TYPE=p_document_type
4974 AND DOCUMENT_ID=p_document_id;
4975
4976 Cursor l_get_max_var_date_csr IS
4977 SELECT MAX(NVL(LAST_UPDATE_DATE,CREATION_DATE))
4978 FROM OKC_K_ART_VARIABLES WHERE CAT_ID IN (
4979 SELECT ID FROM OKC_K_ARTICLES_B
4980 WHERE DOCUMENT_TYPE = p_document_type
4981 AND DOCUMENT_ID = document_id);
4982
4983 Cursor l_get_max_usg_upd_date_csr IS
4984 SELECT MAX(LAST_UPDATE_DATE)
4985 FROM okc_template_usages
4986 WHERE document_type = p_document_type
4987 AND document_id = p_document_id;
4988
4989 Cursor l_get_contract_source_csr IS
4990 SELECT contract_source_code
4991 FROM okc_template_usages
4992 WHERE document_type = p_document_type
4993 AND document_id = p_document_id;
4994
4995 BEGIN
4996 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4997 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE,'9200: get_sys_last_upd_date p_doc_type : '||p_document_type);
4998 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE,'9300: get_sys_last_upd_date p_doc_id : '||p_document_id);
4999 END IF;
5000
5001
5002 OPEN l_get_contract_source_csr;
5003 FETCH l_get_contract_source_csr INTO l_contract_source_code;
5004 CLOSE l_get_contract_source_csr;
5005
5006
5007 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5008 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE,'9310: After fetching l_get_contract_source_csr');
5009 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE,'9320: Contract Source Code :'||l_contract_source_code);
5010 END IF;
5011
5012 IF l_contract_source_code = G_ATTACHED_CONTRACT_SOURCE THEN
5013 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5014 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE,'9330: Before opening l_get_max_usg_upd_date_csr');
5015 END IF;
5016
5017 OPEN l_get_max_usg_upd_date_csr;
5018 FETCH l_get_max_usg_upd_date_csr INTO l_sys_last_update_date;
5019 CLOSE l_get_max_usg_upd_date_csr;
5020
5021 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5022 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE,'9340: After fetching l_get_max_usg_upd_date_csr');
5023 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE,'9350: l_terms_changed_date :'||l_sys_last_update_date);
5024 END IF;
5025
5026 ELSE
5027 OPEN l_get_max_art_date_csr;
5028 FETCH l_get_max_art_date_csr INTO l_article_change_date;
5029 CLOSE l_get_max_art_date_csr;
5030
5031 OPEN l_get_max_scn_date_csr;
5032 FETCH l_get_max_scn_date_csr INTO l_section_change_date;
5033 CLOSE l_get_max_scn_date_csr;
5034
5035 OPEN l_get_max_var_date_csr;
5036 FETCH l_get_max_var_date_csr INTO l_variable_change_date;
5037 CLOSE l_get_max_var_date_csr;
5038
5039 OPEN l_get_max_usg_upd_date_csr;
5040 FETCH l_get_max_usg_upd_date_csr INTO l_sys_last_update_date;
5041 CLOSE l_get_max_usg_upd_date_csr;
5042
5043 --Begin:Fix for bug# 4909079. Added nvl check for article, section, variable, usages dates
5044 l_article_change_date := nvl(l_article_change_date ,okc_api.g_miss_date);
5045 l_section_change_date := nvl(l_section_change_date ,okc_api.g_miss_date);
5046 l_variable_change_date := nvl(l_variable_change_date,okc_api.g_miss_date);
5047 l_sys_last_update_date := nvl(l_sys_last_update_date,okc_api.g_miss_date);
5048 --End:Fix for bug# 4909079
5049
5050 l_sys_last_update_date := Greatest(l_article_change_date, l_section_change_date,l_variable_change_date,l_sys_last_update_date);
5051 if(l_sys_last_update_date = OKC_API.G_MISS_DATE) THEN
5052 l_sys_last_update_date := sysdate;
5053 end if;
5054 END IF;
5055
5056 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5057 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE,'9700: l_sys_last_update_date : '||l_sys_last_update_date);
5058 END IF;
5059 return l_sys_last_update_date;
5060 EXCEPTION
5061 WHEN OTHERS THEN
5062 RETURN NULL;
5063 END get_sys_last_upd_date;
5064
5065 /*
5066 Function Name : get_revert_art_version_id
5067 Purpose : This function returns the latest article_version_id of the ref_article_id queried using id
5068 of OKC_K_ARTICLES_B.
5069 Usage : This function is used in the Revert to Standard UI to query the latest article_version_id
5070 for the ref_article_id of the article in OKC_K_ARTICLES_B queried using the id (primary_key).
5071 */
5072
5073 FUNCTION get_revert_art_version_id(
5074 p_id IN NUMBER,
5075 p_document_type IN VARCHAR2,
5076 p_document_id IN NUMBER ) RETURN NUMBER
5077 IS
5078
5079 l_article_version_number OKC_ARTICLES_ALL.ARTICLE_NUMBER%TYPE;
5080 l_article_version_id OKC_ARTICLE_VERSIONS.ARTICLE_VERSION_ID%TYPE;
5081 l_local_article_id OKC_K_ARTICLES_B.ORIG_ARTICLE_ID%TYPE;
5082 l_adoption_type OKC_ARTICLE_VERSIONS.ADOPTION_TYPE%TYPE;
5083 l_ref_article_id OKC_K_ARTICLES_B.REF_ARTICLE_ID%TYPE;
5084
5085
5086 -- Fix for bug# 5235082. Changed the query to use id instead of sav_sae_id
5087 cursor get_ref_article_id is
5088 select ref_article_id from okc_k_articles_b
5089 where id = p_id;
5090
5091 BEGIN
5092 open get_ref_article_id;
5093 fetch get_ref_article_id into l_ref_article_id;
5094 close get_ref_article_id;
5095
5096 get_latest_article_details
5097 (
5098 p_article_id => l_ref_article_id,
5099 p_document_type => p_document_type,
5100 p_document_id => p_document_id,
5101 x_article_version_id => l_article_version_id,
5102 x_article_version_number => l_article_version_number,
5103 x_local_article_id => l_local_article_id,
5104 x_adoption_type => l_adoption_type);
5105
5106 RETURN l_article_version_id;
5107 END get_revert_art_version_id;
5108
5109
5110 -- Start of comments
5111 --API name : set_udv_with_procedures
5112 --Type : Private.
5113 --Function : API to set the user defined variables with procedures,
5114 -- with values and insert them in a temporary table used in printing terms and review messages when the modified terms are uploaded
5115 --Pre-reqs : None.
5116 --Parameters :
5117 --IN : p_api_version IN NUMBER Required
5118 -- : p_init_msg_list IN VARCHAR2 Optional
5119 -- Default = FND_API.G_FALSE
5120 -- : p_document_id IN NUMBER Required
5121 -- Id of document whose udv with procs are to be set
5122 -- : p_doc_type IN VARCHAR2 Required
5123 -- Type of document whose udv with procs are to be set
5124 -- : p_output_error IN VARCHAR2 Optional
5125 -- : x_return_status OUT VARCHAR2(1)
5126 -- : x_msg_count OUT NUMBER
5127 -- : x_msg_data OUT VARCHAR2(2000)
5128 --Note :
5129 -- End of comments
5130
5131
5132 PROCEDURE set_udv_with_procedures (
5133 p_api_version IN NUMBER,
5134 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
5135
5136 p_document_type IN VARCHAR2,
5137 p_document_id IN NUMBER,
5138 p_output_error IN VARCHAR2 := FND_API.G_TRUE,
5139
5140 x_return_status OUT NOCOPY VARCHAR2,
5141 x_msg_data OUT NOCOPY VARCHAR2,
5142 x_msg_count OUT NOCOPY NUMBER
5143
5144 ) IS
5145 l_api_version CONSTANT NUMBER := 1.0;
5146 l_api_name CONSTANT VARCHAR2(30) := 'set_udv_with_procedures';
5147
5148 l_variable_value VARCHAR2(2500) := NULL;
5149 l_previous_var_code okc_bus_variables_b.variable_code%TYPE := '-99';
5150 l_return_status VARCHAR2(10) := NULL;
5151
5152 CURSOR csr_get_udv_with_procs IS
5153 SELECT VB.variable_code,
5154 KA.id,
5155 KA.article_version_id
5156 FROM okc_k_articles_b KA,
5157 okc_k_art_variables KV,
5158 okc_bus_variables_b VB
5159 WHERE VB.variable_code = KV.variable_code
5160 AND KA.id = KV.cat_id
5161 AND VB.variable_source = 'P'
5162 AND KA.document_type = p_document_type
5163 AND KA.document_id = p_document_id
5164 ORDER BY VB.variable_code;
5165
5166 BEGIN
5167 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5168 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Entered set_udv_with_procedures');
5169 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: p_document_type:'||p_document_type);
5170 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: p_document_id:'||p_document_id);
5171 END IF;
5172
5173 /* Standard call to check for call compatibility */
5174 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
5175 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5176 END IF;
5177
5178 /* Initialize message list if p_init_msg_list is set to TRUE */
5179 IF FND_API.to_Boolean( p_init_msg_list ) THEN
5180 FND_MSG_PUB.initialize;
5181 END IF;
5182
5183 /* Initialize API return status to success */
5184 x_return_status := G_RET_STS_SUCCESS;
5185
5186 /* Clear the temp table */
5187 DELETE FROM OKC_TERMS_UDV_WITH_PROCEDURE_T;
5188
5189 FOR csr_udv_with_procs_rec IN csr_get_udv_with_procs LOOP
5190
5191 /* Get the variable value */
5192 IF l_previous_var_code <> csr_udv_with_procs_rec.variable_code THEN
5193
5194 l_variable_value := NULL;
5195
5196 get_udv_with_proc_value (
5197 p_document_type => p_document_type,
5198 p_document_id => p_document_id,
5199 p_variable_code => csr_udv_with_procs_rec.variable_code,
5200 p_output_error => p_output_error,
5201 x_variable_value => l_variable_value,
5202 x_return_status => l_return_status,
5203 x_msg_data => x_msg_data,
5204 x_msg_count => x_msg_count );
5205
5206 END IF;
5207
5208 /* Insert data into the temp table */
5209 IF l_variable_value IS NOT NULL THEN
5210
5211 INSERT INTO OKC_TERMS_UDV_WITH_PROCEDURE_T
5212 (
5213 VARIABLE_CODE,
5214 VARIABLE_VALUE,
5215 DOC_TYPE,
5216 DOC_ID,
5217 ARTICLE_VERSION_ID,
5218 CAT_ID
5219 )
5220 VALUES
5221 (
5222 csr_udv_with_procs_rec.variable_code, -- VARIABLE_CODE
5223 l_variable_value, -- VARIABLE_VALUE
5224 p_document_type, -- DOCUMENT_TYPE
5225 p_document_id, -- DOCUMENT_ID
5226 csr_udv_with_procs_rec.article_version_id, -- ARTICLE_VERSION_ID
5227 csr_udv_with_procs_rec.id -- CAT_ID
5228 );
5229 END IF;
5230
5231 l_previous_var_code := csr_udv_with_procs_rec.variable_code;
5232
5233 END LOOP;
5234
5235 IF p_output_error = FND_API.G_TRUE AND FND_MSG_PUB.Count_Msg > 0 THEN
5236
5237 x_return_status := G_RET_STS_ERROR;
5238 RAISE FND_API.G_EXC_ERROR;
5239 END IF;
5240
5241 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5242 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'200: Leaving set_udv_with_procedures');
5243 END IF;
5244
5245 EXCEPTION
5246 WHEN FND_API.G_EXC_ERROR THEN
5247
5248 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5249 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1000: Leaving set_udv_with_procedures : OKC_API.G_EXCEPTION_ERROR Exception');
5250 END IF;
5251
5252 IF csr_get_udv_with_procs%ISOPEN THEN
5253 CLOSE csr_get_udv_with_procs;
5254 END IF;
5255
5256 x_return_status := G_RET_STS_ERROR ;
5257 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
5258
5259 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5260
5261 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5262 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'2000: Leaving set_udv_with_procedures : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
5263 END IF;
5264
5265 IF csr_get_udv_with_procs%ISOPEN THEN
5266 CLOSE csr_get_udv_with_procs;
5267 END IF;
5268
5269 x_return_status := G_RET_STS_UNEXP_ERROR ;
5270 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
5271
5272 WHEN OTHERS THEN
5273
5274 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5275 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'3000: Leaving set_udv_with_procedures because of EXCEPTION: '||sqlerrm);
5276 END IF;
5277
5278 IF csr_get_udv_with_procs%ISOPEN THEN
5279 CLOSE csr_get_udv_with_procs;
5280 END IF;
5281
5282 x_return_status := G_RET_STS_UNEXP_ERROR ;
5283 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
5284 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
5285 END IF;
5286 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
5287
5288 END set_udv_with_procedures ;
5289
5290 -- Start of comments
5291 --API name : get_udv_with_proc_value
5292 --Function : This API returns the value for a user defined variable with procedure as source.
5293 -- This API is used in print terms and validate terms
5294 --Pre-reqs : None.
5295 --Parameters :
5296 --IN : p_document_id IN NUMBER Required
5297 -- : p_doc_type IN VARCHAR2 Required
5298 -- : p_variable_code IN VARCHAR2 Required
5299 -- : p_output_error IN VARCHAR2 Optional
5300 -- : x_variable_value OUT VARCHAR2
5301 -- : x_return_status OUT VARCHAR2(1)
5302 -- : x_msg_count OUT NUMBER
5303 -- : x_msg_data OUT VARCHAR2(2000)
5304 --Note :
5305 -- End of comments
5306
5307 PROCEDURE get_udv_with_proc_value (
5308 p_document_type IN VARCHAR2,
5309 p_document_id IN NUMBER,
5310 p_variable_code IN VARCHAR2,
5311 p_output_error IN VARCHAR2 := FND_API.G_FALSE,
5312 x_variable_value OUT NOCOPY VARCHAR2,
5313 x_return_status OUT NOCOPY VARCHAR2,
5314 x_msg_data OUT NOCOPY VARCHAR2,
5315 x_msg_count OUT NOCOPY NUMBER
5316
5317 ) IS
5318
5319
5320 l_api_name CONSTANT VARCHAR2(30) := 'get_udv_with_proc_value';
5321
5322 l_variable_value VARCHAR2(2500) := NULL;
5323
5324 l_procedure_name okc_bus_variables_b.procedure_name%TYPE;
5325 l_value_set_id okc_bus_variables_b.value_set_id%TYPE;
5326 l_variable_name okc_bus_variables_tl.variable_name%TYPE;
5327 l_variable_value_id VARCHAR2(2500) := NULL;
5328 l_return_status VARCHAR2(10);
5329 l_msg_count NUMBER;
5330 l_msg_data VARCHAR2(2500);
5331 l_dynamic_sql_stmt LONG;
5332
5333 l_procedure_spec_status ALL_OBJECTS.status%TYPE;
5334 l_procedure_body_status ALL_OBJECTS.status%TYPE;
5335 l_dummy VARCHAR2(1);
5336
5337 l_validation_type fnd_flex_value_sets.validation_type%TYPE;
5338 l_table_name fnd_flex_validation_tables.application_table_name%TYPE;
5339 l_name_col fnd_flex_validation_tables.value_column_name%TYPE;
5340 l_id_col fnd_flex_validation_tables.id_column_name%TYPE;
5341 l_additional_where_clause fnd_flex_validation_tables.additional_where_clause%TYPE;
5342 l_sql_stmt LONG;
5343 TYPE cur_typ IS REF CURSOR;
5344 c_cursor cur_typ;
5345
5346 CURSOR csr_get_udv_with_proc_dtls IS
5347 SELECT VB.procedure_name,
5348 VB.value_set_id,
5349 VT.variable_name
5350 FROM okc_bus_variables_b VB,
5351 okc_bus_variables_tl VT
5352 WHERE VB.variable_code = VT.variable_code
5353 AND VT.language = USERENV('LANG')
5354 AND VB.variable_code = p_variable_code
5355 AND VB.variable_source = 'P';
5356
5357 CURSOR csr_get_validation_type(p_value_set_id IN NUMBER) IS
5358 SELECT validation_type
5359 FROM FND_FLEX_VALUE_SETS
5360 WHERE flex_value_set_id = p_value_set_id;
5361
5362 CURSOR csr_value_set_table(p_value_set_id IN NUMBER) IS
5363 SELECT application_table_name,
5364 value_column_name,
5365 id_column_name,
5366 additional_where_clause
5367 FROM fnd_flex_validation_tables
5368 WHERE flex_value_set_id = p_value_set_id;
5369
5370 --Expected procedure name is SCHEMA.PACKAGENAME.PROCEDURENAME
5371
5372 CURSOR csr_check_proc_spec_status (p_procedure_name VARCHAR2) IS
5373 SELECT status
5374 FROM all_objects
5375 WHERE object_name = SUBSTR(p_procedure_name,
5376 INSTR(p_procedure_name,'.')+1,
5377 (INSTR(p_procedure_name,'.',1,2) -
5378 INSTR(p_procedure_name,'.') - 1))
5379 AND object_type = 'PACKAGE'
5380 AND owner = SUBSTR(p_procedure_name,1,INSTR(p_procedure_name,'.')-1);
5381
5382
5383 CURSOR csr_check_proc_body_status (p_procedure_name VARCHAR2) IS
5384 SELECT status
5385 FROM all_objects
5386 WHERE object_name = SUBSTR(p_procedure_name,
5387 INSTR(p_procedure_name,'.')+1,
5388 (INSTR(p_procedure_name,'.',1,2) -
5389 INSTR(p_procedure_name,'.') - 1))
5390 AND object_type = 'PACKAGE BODY'
5391 AND owner = SUBSTR(p_procedure_name,1,INSTR(p_procedure_name,'.')-1);
5392
5393 CURSOR csr_check_proc_exists (p_procedure_name VARCHAR2) IS
5394 SELECT 'X'
5395 FROM all_source
5396 WHERE name = SUBSTR(p_procedure_name,
5397 INSTR(p_procedure_name,'.')+1,
5398 (INSTR(p_procedure_name,'.',1,2) -
5399 INSTR(p_procedure_name,'.') - 1))
5400 AND type = 'PACKAGE'
5401 AND owner = SUBSTR(p_procedure_name,1,INSTR(p_procedure_name,'.')-1)
5402 AND text LIKE '%' || SUBSTR(p_procedure_name,INSTR(p_procedure_name,'.',1,2)+1) || '%';
5403
5404
5405 BEGIN
5406 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5407 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Entered get_udv_with_proc_value');
5408 END IF;
5409
5410 /* Initialize API return status to success */
5411 x_return_status := G_RET_STS_SUCCESS;
5412
5413 BEGIN
5414 OPEN csr_get_udv_with_proc_dtls;
5415 FETCH csr_get_udv_with_proc_dtls INTO l_procedure_name, l_value_set_id, l_variable_name;
5416 CLOSE csr_get_udv_with_proc_dtls;
5417
5418
5419 /* Execute the procedure */
5420 l_dynamic_sql_stmt := 'BEGIN '||l_procedure_name || '(' ||
5421 'x_return_status => '|| ':1' || ',' ||
5422 'x_msg_data => '|| ':2' || ',' ||
5423 'x_msg_count => '|| ':3' || ',' ||
5424 'p_doc_type => '|| ':4' || ',' ||
5425 'p_doc_id => '|| ':5' || ',' ||
5426 'p_variable_code => '|| ':6' || ',' ||
5427 'x_variable_value_id => '|| ':7' || '); END;';
5428
5429 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5430 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,'200: l_dynamic_sql_stmt '|| l_dynamic_sql_stmt);
5431 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'200: variable_code:'||p_variable_code);
5432 END IF;
5433
5434 l_variable_value_id := NULL;
5435
5436 EXECUTE IMMEDIATE l_dynamic_sql_stmt
5437 USING OUT l_return_status, OUT l_msg_data, OUT l_msg_count,
5438 p_document_type, p_document_id, p_variable_code,
5439 IN OUT l_variable_value_id;
5440
5441 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5442 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'300: Procedure return status:'||l_return_status);
5443 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'300: Variable value id:'||l_variable_value_id);
5444 END IF;
5445
5446 IF (l_return_status = G_RET_STS_ERROR) THEN
5447
5448 l_variable_value_id := NULL;
5449
5450 IF p_output_error = FND_API.G_TRUE THEN
5451 FND_MESSAGE.set_name('OKC','OKC_UDV_PROC_EXEC');
5452 FND_MESSAGE.set_token('VAR_NAME', l_variable_name);
5453 FND_MESSAGE.set_token('PROC_NAME', l_procedure_name);
5454 FND_MSG_PUB.ADD;
5455 END IF;
5456 ELSIF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
5457
5458 l_variable_value_id := NULL;
5459
5460 IF p_output_error = FND_API.G_TRUE THEN
5461 FND_MESSAGE.set_name('OKC','OKC_UDV_PROC_UNEXP');
5462 FND_MESSAGE.set_token('VAR_NAME', l_variable_name);
5463 FND_MESSAGE.set_token('PROC_NAME', l_procedure_name);
5464 FND_MSG_PUB.ADD;
5465 END IF;
5466 END IF;
5467
5468 EXCEPTION
5469 WHEN OTHERS THEN
5470
5471 l_variable_value_id := NULL;
5472
5473 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5474 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'400: Error in procedure execution:'||sqlerrm);
5475 END IF;
5476
5477 IF p_output_error = FND_API.G_TRUE THEN
5478
5479 l_dummy := '?';
5480 OPEN csr_check_proc_exists(p_procedure_name => l_procedure_name);
5481 FETCH csr_check_proc_exists INTO l_dummy;
5482
5483 OPEN csr_check_proc_spec_status(p_procedure_name => l_procedure_name);
5484 FETCH csr_check_proc_spec_status INTO l_procedure_spec_status;
5485
5486 OPEN csr_check_proc_body_status(p_procedure_name => l_procedure_name);
5487 FETCH csr_check_proc_body_status INTO l_procedure_body_status;
5488
5489 CLOSE csr_check_proc_exists;
5490 CLOSE csr_check_proc_spec_status;
5491 CLOSE csr_check_proc_body_status;
5492
5493 IF l_dummy <> 'X' THEN
5494 FND_MESSAGE.set_name('OKC','OKC_UDV_PROC_NOT_EXIST');
5495 FND_MESSAGE.set_token('VAR_NAME', l_variable_name);
5496 FND_MESSAGE.set_token('PROC_NAME', l_procedure_name);
5497 FND_MSG_PUB.ADD;
5498 ELSIF l_procedure_spec_status = 'INVALID' OR l_procedure_body_status = 'INVALID' THEN
5499 FND_MESSAGE.set_name('OKC','OKC_UDV_PROC_INVALID');
5500 FND_MESSAGE.set_token('VAR_NAME', l_variable_name);
5501 FND_MESSAGE.set_token('PROC_NAME', l_procedure_name);
5502 FND_MSG_PUB.ADD;
5503 END IF;
5504 END IF;
5505
5506 END;
5507
5508 /* Get the variable value from the variable value id using the value set */
5509
5510 IF l_variable_value_id IS NOT NULL THEN
5511
5512 BEGIN
5513
5514 l_variable_value := NULL;
5515
5516 OPEN csr_get_validation_type(p_value_set_id => l_value_set_id);
5517 FETCH csr_get_validation_type INTO l_validation_type;
5518 CLOSE csr_get_validation_type;
5519
5520 /* Valueset is Table type, execute the dynamic sql to get the variable value */
5521 IF l_validation_type = 'F' THEN
5522
5523 OPEN csr_value_set_table(p_value_set_id => l_value_set_id);
5524 FETCH csr_value_set_table INTO l_table_name, l_name_col, l_id_col, l_additional_where_clause;
5525 CLOSE csr_value_set_table;
5526
5527 l_sql_stmt := 'SELECT '||l_name_col||
5528 ' FROM ('||
5529 ' SELECT '||l_name_col||' , '||l_id_col||
5530 ' FROM '||l_table_name||' ';
5531
5532 IF TRIM(l_additional_where_clause) IS NOT NULL THEN
5533 IF INSTR(UPPER(l_additional_where_clause),'WHERE') > 0 THEN
5534 l_sql_stmt := l_sql_stmt || l_additional_where_clause;
5535 ELSE
5536 l_sql_stmt := l_sql_stmt || 'WHERE '||l_additional_where_clause;
5537 END IF;
5538 END IF;
5539
5540 l_sql_stmt := l_sql_stmt || ' ) WHERE to_char('||l_id_col|| ') = '''|| l_variable_value_id || '''';
5541
5542 OPEN c_cursor FOR l_sql_stmt;
5543 FETCH c_cursor INTO l_variable_value;
5544 CLOSE c_cursor;
5545
5546 /* Valueset is Independent type */
5547 ELSIF l_validation_type = 'I' THEN
5548
5549 l_variable_value := OKC_TERMS_UTIL_PVT.GET_VALUE_SET_VARIABLE_VALUE (
5550 p_CONTEXT => l_validation_type,
5551 p_VALUE_SET_ID => l_value_set_id,
5552 p_FLEX_VALUE_ID => l_variable_value_id);
5553
5554 /* Valueset is None type */
5555 ELSE
5556
5557 l_variable_value := l_variable_value_id;
5558 END IF;
5559
5560 EXCEPTION
5561 WHEN OTHERS THEN
5562
5563 l_variable_value := NULL;
5564
5565 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5566 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'500: Error while fetching value from valueset:'||sqlerrm);
5567 END IF;
5568
5569 IF p_output_error = FND_API.G_TRUE THEN
5570 FND_MESSAGE.set_name('OKC','OKC_UDV_VSET_INVALID');
5571 FND_MESSAGE.set_token('VAR_NAME', l_variable_name);
5572 FND_MSG_PUB.ADD;
5573 END IF;
5574 END;
5575
5576 END IF; /* IF l_variable_value_id IS NOT NULL*/
5577
5578 x_variable_value := l_variable_value;
5579
5580 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5581 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'600: Variable value:'||l_variable_value);
5582 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'700: Leaving get_udv_with_proc_value');
5583 END IF;
5584
5585 EXCEPTION
5586 WHEN FND_API.G_EXC_ERROR THEN
5587
5588 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5589 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1000: Leaving get_udv_with_proc_value : OKC_API.G_EXCEPTION_ERROR Exception');
5590 END IF;
5591
5592 IF csr_get_udv_with_proc_dtls%ISOPEN THEN
5593 CLOSE csr_get_udv_with_proc_dtls;
5594 END IF;
5595
5596 x_return_status := G_RET_STS_ERROR ;
5597 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
5598
5599 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5600
5601 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5602 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'2000: Leaving get_udv_with_proc_value : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
5603 END IF;
5604
5605 IF csr_get_udv_with_proc_dtls%ISOPEN THEN
5606 CLOSE csr_get_udv_with_proc_dtls;
5607 END IF;
5608
5609 x_return_status := G_RET_STS_UNEXP_ERROR ;
5610 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
5611
5612 WHEN OTHERS THEN
5613
5614 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5615 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'3000: Leaving get_udv_with_proc_value because of EXCEPTION: '||sqlerrm);
5616 END IF;
5617
5618 IF csr_get_udv_with_proc_dtls%ISOPEN THEN
5619 CLOSE csr_get_udv_with_proc_dtls;
5620 END IF;
5621
5622 x_return_status := G_RET_STS_UNEXP_ERROR ;
5623 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
5624 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
5625 END IF;
5626 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
5627
5628 END get_udv_with_proc_value;
5629
5630
5631 END OKC_TERMS_UTIL_PVT;
[Home] [Help]