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