[Home] [Help]
PACKAGE BODY: APPS.OKC_TERMS_UTIL_GRP
Source
1 PACKAGE BODY OKC_TERMS_UTIL_GRP AS
2 /* $Header: OKCGDUTB.pls 120.6.12020000.8 2013/04/12 14:13:09 serukull ship $ */
3
4
5 ---------------------------------------------------------------------------
6 -- GLOBAL MESSAGE CONSTANTS
7 ---------------------------------------------------------------------------
8 G_FND_APP CONSTANT VARCHAR2(200) := OKC_API.G_FND_APP;
9 ---------------------------------------------------------------------------
10 -- GLOBAL VARIABLES
11 ---------------------------------------------------------------------------
12 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKC_TERMS_UTIL_GRP';
13 G_MODULE CONSTANT VARCHAR2(200) := 'okc.plsql.'||G_PKG_NAME||'.';
14 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
15
16 ------------------------------------------------------------------------------
17 -- GLOBAL CONSTANTS
18 ------------------------------------------------------------------------------
19 G_FALSE CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
20 G_TRUE CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
21
22 G_AMEND_CODE_DELETED CONSTANT VARCHAR2(30) := 'DELETED';
23 G_ATTACHED_CONTRACT_SOURCE CONSTANT VARCHAR2(30) := 'ATTACHED';
24 G_STRUCT_CONTRACT_SOURCE CONSTANT VARCHAR2(30) := 'STRUCTURED';
25 G_INTERNAL_PARTY_CODE CONSTANT VARCHAR2(30) := 'INTERNAL_ORG';
26
27 -- Validation string for repository.
28 G_REP_CHECK_STATUS CONSTANT VARCHAR2(30) := 'OKC_REP_CHECK_STATUS';
29
30 G_RET_STS_SUCCESS CONSTANT varchar2(1) := FND_API.G_RET_STS_SUCCESS;
31 G_RET_STS_ERROR CONSTANT varchar2(1) := FND_API.G_RET_STS_ERROR;
32 G_RET_STS_UNEXP_ERROR CONSTANT varchar2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
33
34 G_UNEXPECTED_ERROR CONSTANT varchar2(200) := 'OKC_UNEXPECTED_ERROR';
35 G_SQLERRM_TOKEN CONSTANT varchar2(200) := 'ERROR_MESSAGE';
36 G_SQLCODE_TOKEN CONSTANT varchar2(200) := 'ERROR_CODE';
37
38
39 /*
40 -- To be used to delete Terms whenever a document is deleted.
41 */
42 PROCEDURE Delete_Doc (
43 p_api_version IN NUMBER,
44 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
45 p_commit IN VARCHAR2 := FND_API.G_FALSE,
46
47 x_return_status OUT NOCOPY VARCHAR2,
48 x_msg_data OUT NOCOPY VARCHAR2,
49 x_msg_count OUT NOCOPY NUMBER,
50
51 p_validate_commit IN VARCHAR2 := FND_API.G_FALSE,
52 p_validation_string IN VARCHAR2 := NULL,
53
54 p_doc_type IN VARCHAR2,
55 p_doc_id IN NUMBER
56
57 ,p_retain_lock_terms_yn IN VARCHAR2 := 'N'
58 ,p_retain_lock_xprt_yn IN VARCHAR2 := 'N'
59 ,p_retain_lock_deliverables_yn IN VARCHAR2 := 'N'
60
61 -- For backward compatability
62 ,p_retain_deliverables_yn IN VARCHAR2 := 'N'
63 -- For backward compatability
64
65 ,P_RELEASE_LOCKS_YN IN VARCHAR2 := 'N'
66 , p_retain_exhibits IN VARCHAR2
67
68 ) IS
69 l_api_version CONSTANT NUMBER := 1;
70 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Doc';
71 l_dummy VARCHAR2(10);
72 BEGIN
73 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
74 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Entered Delete_Doc');
75 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'200: Parameter List ');
76 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'300: p_api_version : '||p_api_version);
77 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'400: p_init_msg_list : '||p_init_msg_list);
78 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'500: p_commit : '||p_commit);
79 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'600: p_validate_commit : '||p_validate_commit);
80 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'700: p_validation_string : '||p_validation_string );
81 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'800: p_doc_type : '||p_doc_type);
82 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'900: p_doc_id : '||p_doc_id);
83 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'910: p_retain_lock_terms_yn : '||p_retain_lock_terms_yn);
84 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'920: p_retain_lock_xprt_yn : '||p_retain_lock_xprt_yn);
85 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'920: p_retain_lock_deliverables_yn : '||p_retain_lock_deliverables_yn);
86 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'930: p_retain_deliverables_yn : '||p_retain_deliverables_yn);
87 END IF;
88 -- Standard Start of API savepoint
89 SAVEPOINT g_Delete_Doc;
90 -- Standard call to check for call compatibility.
91 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
92 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
93 END IF;
94 -- Initialize message list if p_init_msg_list is set to TRUE.
95 IF FND_API.to_Boolean( p_init_msg_list ) THEN
96 FND_MSG_PUB.initialize;
97 END IF;
98 -- Initialize API return status to success
99 x_return_status := G_RET_STS_SUCCESS;
100
101 IF p_validate_commit = G_TRUE THEN
102 l_dummy := OKC_TERMS_UTIL_GRP.Ok_To_Commit(
103 p_api_version => p_api_version ,
104 p_init_msg_list => p_init_msg_list,
105 x_msg_data => x_msg_data ,
106 x_msg_count => x_msg_count ,
107 x_return_status => x_return_status,
108
109 p_validation_string => p_validation_string,
110 p_tmpl_change => 'D',
111 p_doc_id => p_doc_id,
112 p_doc_type => p_doc_type
113 );
114 --------------------------------------------
115 IF (l_dummy = G_FALSE OR x_return_status = G_RET_STS_UNEXP_ERROR) THEN
116 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
117 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
118 RAISE FND_API.G_EXC_ERROR ;
119 END IF;
120 --------------------------------------------
121 END IF;
122
123 -- Calling Ptivate API to Delete the doc.
124 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
125 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1000: Calling Ptivate API to Delete the doc');
126 END IF;
127 OKC_TERMS_UTIL_PVT.Delete_Doc(
128 x_return_status => x_return_status,
129
130 p_doc_type => p_doc_type,
131 p_doc_id => p_doc_id
132
133 ,p_retain_lock_terms_yn => p_retain_lock_terms_yn
134 ,p_retain_lock_xprt_yn => p_retain_lock_xprt_yn
135 );
136 --------------------------------------------
137 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
138 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
139 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
140 RAISE FND_API.G_EXC_ERROR ;
141 END IF;
142 --------------------------------------------
143
144 --IF p_retain_deliverables_yn = 'N' THEN
145 -- Call Deliverable API to delete delevirable from the document.
146 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
147 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1100: Delete delevirable for the doc');
148 END IF;
149 OKC_DELIVERABLE_PROCESS_PVT.Delete_Deliverables(
150 p_api_version => l_api_version,
151 p_init_msg_list => FND_API.G_FALSE,
152 p_doc_type => p_doc_type,
153 p_doc_id => p_doc_id,
154 p_doc_version => -99,
155 x_msg_data => x_msg_data,
156 x_msg_count => x_msg_count,
157 x_return_status => x_return_status
158 , p_retain_lock_deliverables_yn => p_retain_lock_deliverables_yn
159 , p_retain_exhibits => p_retain_exhibits
160 );
161 --------------------------------------------
162 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
163 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
164 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
165 RAISE FND_API.G_EXC_ERROR ;
166 END IF;
167 --------------------------------------------
168 --END IF; -- p_retain_deliverables_yn = 'N'
169
170 -- Call attachement API to delete attachements from the document.
171 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
172 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1200: Delete attachments for the doc');
173 END IF;
174 OKC_CONTRACT_DOCS_GRP.Delete_Ver_Attachments(
175 p_api_version => l_api_version,
176 p_init_msg_list => FND_API.G_FALSE,
177 p_validation_level=> FND_API.G_VALID_LEVEL_FULL,
178 p_commit => FND_API.G_FALSE,
179 p_business_document_type => p_doc_type,
180 p_business_document_id => p_doc_id,
181 p_business_document_version=> -99,
182 x_msg_data => x_msg_data,
183 x_msg_count => x_msg_count,
184 x_return_status => x_return_status
185 );
186 --------------------------------------------
187 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
188 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
189 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
190 RAISE FND_API.G_EXC_ERROR ;
191 END IF;
192 --------------------------------------------
193
194 IF P_RELEASE_LOCKS_YN = 'Y' THEN
195 okc_k_entity_locks_grp.release_locks( p_api_version => 1,
196 p_doc_type => p_doc_type,
197 p_doc_id => p_doc_id,
198 X_RETURN_STATUS => X_RETURN_STATUS,
199 X_MSG_COUNT => X_MSG_COUNT,
200 X_MSG_DATA => X_MSG_DATA
201 );
202 --------------------------------------------
203 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
204 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
205 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
206 RAISE FND_API.G_EXC_ERROR ;
207 END IF;
208 --------------------------------------------
209 END IF;
210
211 IF FND_API.To_Boolean( p_commit ) THEN
212 COMMIT WORK;
213 END IF;
214 -- Standard call to get message count and if count is 1, get message info.
215 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
216 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
217 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1300: Leaving Delete_Doc');
218 END IF;
219
220 EXCEPTION
221 WHEN FND_API.G_EXC_ERROR THEN
222 ROLLBACK TO g_Delete_Doc;
223 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
224 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1400: Leaving Delete_Doc : OKC_API.G_EXCEPTION_ERROR Exception');
225 END IF;
226 x_return_status := G_RET_STS_ERROR ;
227 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
228
229 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
230 ROLLBACK TO g_Delete_Doc;
231 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
232 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1500: Leaving Delete_Doc : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
233 END IF;
234 x_return_status := G_RET_STS_UNEXP_ERROR ;
235 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
236
237 WHEN OTHERS THEN
238 ROLLBACK TO g_Delete_Doc;
239
240 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
241 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1600: Leaving Delete_Doc because of EXCEPTION: '||sqlerrm);
242 END IF;
243
244 x_return_status := G_RET_STS_UNEXP_ERROR ;
245 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
246 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
247 END IF;
248 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
249 END Delete_Doc ;
250
251 /*
252 -- To be used when doing bulk deletes of document.A very PO specific scenario.
253 -- This API will delete both current and all previous versions of document.
254 */
255
256 PROCEDURE Purge_Doc (
257 p_api_version IN NUMBER,
258 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
259 p_commit IN VARCHAR2 := FND_API.G_FALSE,
260
261 x_return_status OUT NOCOPY VARCHAR2,
262 x_msg_data OUT NOCOPY VARCHAR2,
263 x_msg_count OUT NOCOPY NUMBER,
264
265 p_doc_tbl IN doc_tbl_type
266 ) IS
267 l_api_version CONSTANT NUMBER := 1;
268 l_api_name CONSTANT VARCHAR2(30) := 'Purge_Doc';
269
270 TYPE doc_type_tbl_type IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
271 TYPE doc_id_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
272
273 l_doc_type_tbl doc_type_tbl_type ;
274 l_doc_id_tbl doc_id_tbl_type ;
275 BEGIN
276 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
277 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1700: Entered Purge_Doc');
278 END IF;
279 -- Standard Start of API savepoint
280 SAVEPOINT g_Purge_Doc;
281 -- Standard call to check for call compatibility.
282 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
283 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
284 END IF;
285 -- Initialize message list if p_init_msg_list is set to TRUE.
286 IF FND_API.to_Boolean( p_init_msg_list ) THEN
287 FND_MSG_PUB.initialize;
288 END IF;
289 -- Initialize API return status to success
290 x_return_status := G_RET_STS_SUCCESS;
291
292 IF p_doc_tbl.COUNT > 0 THEN
293 FOR i IN p_doc_tbl.FIRST..p_doc_tbl.LAST LOOP
294 l_doc_type_tbl(i):=p_doc_tbl(i).doc_type;
295 l_doc_id_tbl(i):=p_doc_tbl(i).doc_id;
296 END LOOP;
297 END IF;
298
299 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
300 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1800: Bulk deleting Records ');
301 END IF;
302
303 IF l_doc_type_tbl.COUNT > 0 THEN
304 FORALL i IN l_doc_type_tbl.FIRST..l_doc_type_tbl.LAST
305 DELETE FROM OKC_K_ART_VAR_EXT_TL
306 WHERE cat_id IN
307 ( SELECT KART.ID FROM OKC_K_ARTICLES_B KART, OKC_K_ART_VARIABLES ARTVAR, OKC_BUS_VARIABLES_B BUSVAR
308 WHERE document_type=l_doc_type_tbl(i)
309 and document_id=l_doc_id_tbl(i)
310 AND BUSVAR.mrv_flag='Y'
311 AND ARTVAR.VARIABLE_CODE=BUSVAR.VARIABLE_CODE
312 AND ARTVAR.CAT_ID=KART.ID);
313
314 FORALL i IN l_doc_type_tbl.FIRST..l_doc_type_tbl.LAST
315 DELETE FROM OKC_K_ART_VAR_EXT_B
316 WHERE cat_id IN
317 ( SELECT KART.ID FROM OKC_K_ARTICLES_B KART, OKC_K_ART_VARIABLES ARTVAR, OKC_BUS_VARIABLES_B BUSVAR
318 WHERE document_type=l_doc_type_tbl(i)
319 and document_id=l_doc_id_tbl(i)
320 AND BUSVAR.mrv_flag='Y'
321 AND ARTVAR.VARIABLE_CODE=BUSVAR.VARIABLE_CODE
322 AND ARTVAR.CAT_ID=KART.ID);
323
324 FORALL i IN l_doc_type_tbl.FIRST..l_doc_type_tbl.LAST
325 DELETE FROM OKC_K_ART_VARIABLES
326 WHERE cat_id IN
327 ( SELECT ID FROM OKC_K_ARTICLES_B
328 WHERE document_type=l_doc_type_tbl(i)
329 and document_id=l_doc_id_tbl(i));
330
331 FORALL i IN l_doc_type_tbl.FIRST..l_doc_type_tbl.LAST
332 DELETE FROM OKC_K_ART_VAR_EXT_TLH
333 WHERE cat_id IN
334 ( SELECT KART.ID FROM OKC_K_ARTICLES_BH KART, OKC_K_ART_VARIABLES_H ARTVAR, OKC_BUS_VARIABLES_B BUSVAR
335 WHERE document_type=l_doc_type_tbl(i)
336 and document_id=l_doc_id_tbl(i)
337 AND BUSVAR.mrv_flag='Y'
338 AND ARTVAR.VARIABLE_CODE=BUSVAR.VARIABLE_CODE
339 AND ARTVAR.CAT_ID=KART.ID);
340
341
342 FORALL i IN l_doc_type_tbl.FIRST..l_doc_type_tbl.LAST
343 DELETE FROM OKC_K_ART_VAR_EXT_BH
344 WHERE cat_id IN
345 ( SELECT KART.ID FROM OKC_K_ARTICLES_BH KART, OKC_K_ART_VARIABLES_H ARTVAR, OKC_BUS_VARIABLES_B BUSVAR
346 WHERE document_type=l_doc_type_tbl(i)
347 and document_id=l_doc_id_tbl(i)
348 AND BUSVAR.mrv_flag='Y'
349 AND ARTVAR.VARIABLE_CODE=BUSVAR.VARIABLE_CODE
350 AND ARTVAR.CAT_ID=KART.ID);
351
352
353 FORALL i IN l_doc_type_tbl.FIRST..l_doc_type_tbl.LAST
354 DELETE FROM OKC_K_ART_VARIABLES_H
355 WHERE cat_id IN
356 ( SELECT ID FROM OKC_K_ARTICLES_BH
357 WHERE document_type=l_doc_type_tbl(i)
358 and document_id=l_doc_id_tbl(i));
359
360 FORALL i IN l_doc_type_tbl.FIRST..l_doc_type_tbl.LAST
361 DELETE FROM OKC_K_ARTICLES_B
362 WHERE document_type=l_doc_type_tbl(i)
363 and document_id=l_doc_id_tbl(i);
364
365 FORALL i IN l_doc_type_tbl.FIRST..l_doc_type_tbl.LAST
366 DELETE FROM OKC_K_ARTICLES_BH
367 WHERE document_type=l_doc_type_tbl(i)
368 and document_id=l_doc_id_tbl(i);
369
370 FORALL i IN l_doc_type_tbl.FIRST..l_doc_type_tbl.LAST
371 DELETE FROM OKC_SECTIONS_B
372 WHERE document_type=l_doc_type_tbl(i)
373 and document_id=l_doc_id_tbl(i);
374
375 FORALL i IN l_doc_type_tbl.FIRST..l_doc_type_tbl.LAST
376 DELETE FROM OKC_SECTIONS_BH
377 WHERE document_type=l_doc_type_tbl(i)
378 and document_id=l_doc_id_tbl(i);
379
380 FORALL i IN l_doc_type_tbl.FIRST..l_doc_type_tbl.LAST
381 DELETE FROM OKC_TEMPLATE_USAGES
382 WHERE document_type=l_doc_type_tbl(i)
383 and document_id=l_doc_id_tbl(i);
384
385 FORALL i IN l_doc_type_tbl.FIRST..l_doc_type_tbl.LAST
386 DELETE FROM OKC_TEMPLATE_USAGES_H
387 WHERE document_type=l_doc_type_tbl(i)
388 and document_id=l_doc_id_tbl(i);
389
390 IF nvl(fnd_profile.value('OKC_USE_CONTRACTS_RULES_ENGINE'), 'N') = 'Y' THEN
391 FORALL i IN l_doc_type_tbl.FIRST..l_doc_type_tbl.LAST
392 DELETE FROM OKC_XPRT_DOC_QUES_RESPONSE
393 WHERE doc_type=l_doc_type_tbl(i)
394 and doc_id=l_doc_id_tbl(i);
395 END IF;
396
397 END IF;
398
399 -- Call Deliverable API to delete delevirable from the document.
400 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
401 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1900: Purge delevirable for the doc');
402 END IF;
403
404 OKC_DELIVERABLE_PROCESS_PVT.Purge_Doc_Deliverables(
405 p_api_version => l_api_version,
406 p_init_msg_list => FND_API.G_FALSE,
407 p_doc_table => p_doc_tbl,
408 x_msg_data => x_msg_data,
409 x_msg_count => x_msg_count,
410 x_return_status => x_return_status
411 );
412 --------------------------------------------
413 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
414 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
415 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
416 RAISE FND_API.G_EXC_ERROR ;
417 END IF;
418 --------------------------------------------
419 -- Call attachement API to delete attachements from the document.
420 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
421 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2000: Delete attachments for the doc');
422 END IF;
423 IF p_doc_tbl.COUNT > 0 THEN
424 FOR i in p_doc_tbl.FIRST..p_doc_tbl.LAST LOOP
425
426 OKC_CONTRACT_DOCS_GRP.Delete_doc_Attachments(
427 p_api_version => l_api_version,
428 p_init_msg_list => FND_API.G_FALSE,
429 p_validation_level=> FND_API.G_VALID_LEVEL_FULL,
430 p_commit => FND_API.G_FALSE,
431 p_business_document_type => p_doc_tbl(i).doc_type,
432 p_business_document_id => p_doc_tbl(i).doc_id,
433 x_msg_data => x_msg_data,
434 x_msg_count => x_msg_count,
435 x_return_status => x_return_status
436 );
437 --------------------------------------------
438 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
439 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
440 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
441 RAISE FND_API.G_EXC_ERROR ;
442 END IF;
443 END LOOP;
444 END IF;
445
446 IF FND_API.To_Boolean( p_commit ) THEN
447 COMMIT WORK;
448 END IF;
449
450 -- Standard call to get message count and if count is 1, get message info.
451 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
452 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
453 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2100: Leaving Purge_Doc');
454 END IF;
455
456 EXCEPTION
457 WHEN FND_API.G_EXC_ERROR THEN
458 ROLLBACK TO g_Purge_Doc;
459 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
460 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'2200: Leaving Purge_Doc : OKC_API.G_EXCEPTION_ERROR Exception');
461 END IF;
462 x_return_status := G_RET_STS_ERROR ;
463 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
464
465 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
466 ROLLBACK TO g_Purge_Doc;
467 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
468 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'2300: Leaving Purge_Doc : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
469 END IF;
470 x_return_status := G_RET_STS_UNEXP_ERROR ;
471 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
472
473 WHEN OTHERS THEN
474 ROLLBACK TO g_Purge_Doc;
475
476 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
477 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'2400: Leaving Purge_Doc because of EXCEPTION: '||sqlerrm);
478 END IF;
479
480 x_return_status := G_RET_STS_UNEXP_ERROR ;
481 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
482 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
483 END IF;
484 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
485 END Purge_Doc ;
486
487 /*
488 -- To be used in amend flow to mark articles as amended if any of system
489 -- variables used in article has been changed in source document during amendment.
490 */
491 PROCEDURE Mark_Variable_Based_Amendment (
492 p_api_version IN NUMBER,
493 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
494 p_commit IN VARCHAR2 := FND_API.G_FALSE,
495
496 x_return_status OUT NOCOPY VARCHAR2,
497 x_msg_data OUT NOCOPY VARCHAR2,
498 x_msg_count OUT NOCOPY NUMBER,
499
500 p_doc_type IN VARCHAR2,
501 p_doc_id IN NUMBER
502 ) IS
503 l_api_version CONSTANT NUMBER := 1;
504 l_api_name CONSTANT VARCHAR2(30) := 'Mark_Variable_Based_Amendment';
505 l_doc_class VARCHAR2(30) := '?';
506 l_var_codes_tbl variable_code_tbl_type;
507 i BINARY_INTEGER;
508
509 CURSOR var_codes_crs IS
510 select distinct var.variable_code
511 from okc_k_art_variables var, okc_k_articles_b kart
512 where kart.document_type = p_doc_type
513 and kart.document_id = p_doc_id
514 and kart.amendment_Operation_code is null
515 and kart.id=var.cat_id
516 and var.variable_type='S';
517
518 CURSOR doc_cls_lst_crs IS
519 SELECT document_type_class
520 FROM okc_bus_doc_types_b
521 WHERE document_type=p_doc_type;
522
523 BEGIN
524 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
525 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2500: Entered Mark_Variable_Based_Amendment');
526 END IF;
527 -- Standard Start of API savepoint
528 SAVEPOINT g_Get_System_Variables;
529 -- Standard call to check for call compatibility.
530 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
531 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
532 END IF;
533 -- Initialize message list if p_init_msg_list is set to TRUE.
534 IF FND_API.to_Boolean( p_init_msg_list ) THEN
535 FND_MSG_PUB.initialize;
536 END IF;
537 -- Initialize API return status to success
538 x_return_status := G_RET_STS_SUCCESS;
539
540
541 OPEN doc_cls_lst_crs;
542 FETCH doc_cls_lst_crs INTO l_doc_class;
543 CLOSE doc_cls_lst_crs;
544 /*
545 ???
546 -- Based on Doc type call API's provided by different integrating system and pass the pl/sql table prepared ins tep 2.These API will check which variable has changed and return list of only those variables which have changed.
547 -- For example: If document type is 'BPO' then call API provided by PO team.
548 -- If document type is 'BSA' then call API provided by OM team.
549 -- Parameter to these API's will be p_doc_id IN Number, p_var_tbl IN/OUT pl/sql table having one column variable_code
550 */
551
552 IF l_doc_class in ('BSA','SO') THEN
553 -- IF l_doc_class = 'OM' THEN
554 Null;
555
556 ELSIF l_doc_class = 'PO' THEN
557
558 OPEN var_codes_crs;
559 FETCH var_codes_crs BULK COLLECT INTO l_var_codes_tbl;
560 CLOSE var_codes_crs;
561
562 OKC_PO_INT_GRP.Get_Changed_Variables(
563 p_api_version => p_api_version ,
564 p_init_msg_list => p_init_msg_list,
565 x_msg_data => x_msg_data ,
566 x_msg_count => x_msg_count ,
567 x_return_status => x_return_status,
568 p_doc_type => p_doc_type,
569 p_doc_id => p_doc_id,
570 p_sys_var_tbl => l_var_codes_tbl );
571
572 --------------------------------------------
573 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
574 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
575 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
576 RAISE FND_API.G_EXC_ERROR ;
577 END IF;
578 --------------------------------------------
579 ELSIF l_doc_class = 'SOURCING' THEN
580
581 OPEN var_codes_crs;
582 FETCH var_codes_crs BULK COLLECT INTO l_var_codes_tbl;
583 CLOSE var_codes_crs;
584
585 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
586 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2510: Calling OKC_PON_INT_GRP.Get_Changed_Variables ');
587 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2510: l_var_codes_tbl.count : '||l_var_codes_tbl.count);
588 END IF;
589
590 OKC_PON_INT_GRP.Get_Changed_Variables(
591 p_api_version => p_api_version ,
592 p_init_msg_list => p_init_msg_list,
593 x_msg_data => x_msg_data ,
594 x_msg_count => x_msg_count ,
595 x_return_status => x_return_status,
596 p_doc_id => p_doc_id,
597 p_doc_type => p_doc_type,
598 p_sys_var_tbl => l_var_codes_tbl );
599
600 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
601 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2520: After Calling OKC_PON_INT_GRP.Get_Changed_Variables ');
602 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2520: l_var_codes_tbl.count : '||l_var_codes_tbl.count);
603 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2520: x_return_status : '||x_return_status);
604 END IF;
605
606
607 --------------------------------------------
608 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
609 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
610 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
611 RAISE FND_API.G_EXC_ERROR ;
612 END IF;
613 --------------------------------------------
614 ELSE
615 NULL;
616 END IF;
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,'2600: Calling OKC_TERMS_UTIL_PVT.Mark_Amendment in a loop');
620 END IF;
621
622
623 IF l_var_codes_tbl.count > 0 THEN
624 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
625 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2525: l_var_codes_tbl.First : '||l_var_codes_tbl.First);
626 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2525: l_var_codes_tbl.Last : '||NVL(l_var_codes_tbl.Last,-99));
627 END IF;
628
629 i := l_var_codes_tbl.FIRST;
630
631 WHILE i IS NOT NULL LOOP
632
633 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
634 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2530: l_var_codes_tbl(i) '||l_var_codes_tbl(i));
635 END IF;
636
637 OKC_TERMS_UTIL_PVT.Mark_Amendment(
638 p_api_version => l_api_version,
639 p_init_msg_list => FND_API.G_FALSE,
640 x_return_status => x_return_status,
641 x_msg_data => x_msg_data,
642 x_msg_count => x_msg_count,
643 p_doc_type => p_doc_type,
644 p_doc_id => p_doc_id,
645 p_variable_code => l_var_codes_tbl(i)
646 );
647
648 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
649 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2530: After calling OKC_TERMS_UTIL_PVT.Mark_Amendment');
650 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2530: x_return_status : '||x_return_status);
651 END IF;
652
653 --------------------------------------------
654 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
655 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
656 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
657 RAISE FND_API.G_EXC_ERROR ;
658 END IF;
659 --------------------------------------------
660
661 i := l_var_codes_tbl.NEXT(i);
662
663 END LOOP;
664 END IF;
665
666 -- Standard call to get message count and if count is 1, get message info.
667 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
668 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
669 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2700: Leaving Mark_Variable_Based_Amendment');
670 END IF;
671
672 EXCEPTION
673 WHEN FND_API.G_EXC_ERROR THEN
674 ROLLBACK TO g_Get_System_Variables;
675 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
676 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'2800: Leaving Mark_Variable_Based_Amendment : OKC_API.G_EXCEPTION_ERROR Exception');
677 END IF;
678 x_return_status := G_RET_STS_ERROR ;
679 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
680
681 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
682 ROLLBACK TO g_Get_System_Variables;
683 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
684 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'2900: Leaving Mark_Variable_Based_Amendment : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
685 END IF;
686 x_return_status := G_RET_STS_UNEXP_ERROR ;
687 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
688
689 WHEN OTHERS THEN
690 ROLLBACK TO g_Get_System_Variables;
691 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
692 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'3000: Leaving Mark_Variable_Based_Amendment because of EXCEPTION: '||sqlerrm);
693 END IF;
694
695 x_return_status := G_RET_STS_UNEXP_ERROR ;
696 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
697 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
698 END IF;
699 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
700
701 END Mark_Variable_Based_Amendment;
702
703 /*
704 --To be used to find out if a document is using articles.If yes then what type.
705 --Possible return values NONE, ONLY_STANDARD_EXIST, NON_STANDARD_EXIST .
706 */
707
708 FUNCTION Is_Article_Exist(
709 p_api_version IN NUMBER,
710 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
711
712 x_return_status OUT NOCOPY VARCHAR2,
713 x_msg_data OUT NOCOPY VARCHAR2,
714 x_msg_count OUT NOCOPY NUMBER,
715
716 p_doc_type IN VARCHAR2,
717 p_doc_id IN NUMBER
718 ) RETURN VARCHAR2 IS
719 l_api_version CONSTANT NUMBER := 1;
720 l_api_name CONSTANT VARCHAR2(30) := 'Is_Article_exist';
721 l_dummy VARCHAR2(1) := '?';
722 l_return_value VARCHAR2(100) := G_NO_ARTICLE_EXIST;
723
724 CURSOR find_art_crs IS
725 SELECT a.standard_yn
726 FROM okc_k_articles_b kart, okc_articles_all a
727 WHERE kart.document_type=p_doc_type
728 AND kart.document_id=p_doc_id
729 AND nvl(kart.amendment_operation_code,'?')<>G_AMEND_CODE_DELETED
730 AND nvl(kart.summary_amend_operation_code,'?')<>G_AMEND_CODE_DELETED
731 AND a.article_id = kart.sav_sae_id
732 ORDER BY Decode(a.standard_yn,'N',1) ASC ;
733 BEGIN
734 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
735 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3100: Entered Is_Article_exist');
736 END IF;
737 -- Standard call to check for call compatibility.
738 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
739 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
740 END IF;
741 -- Initialize message list if p_init_msg_list is set to TRUE.
742 IF FND_API.to_Boolean( p_init_msg_list ) THEN
743 FND_MSG_PUB.initialize;
744 END IF;
745 -- Initialize API return status to success
746 x_return_status := G_RET_STS_SUCCESS;
747
748 IF Get_Contract_Source_Code(p_document_type => p_doc_type,p_document_id => p_doc_id) = 'ATTACHED' THEN
749 RETURN G_NON_STANDARD_ART_EXIST;
750 END IF;
751
752 OPEN find_art_crs;
753 FETCH find_art_crs INTO l_dummy;
754 CLOSE find_art_crs;
755 IF l_dummy='Y' THEN
756 l_return_value := G_ONLY_STANDARD_ART_EXIST;
757 ELSIF l_dummy='N' THEN
758 l_return_value := G_NON_STANDARD_ART_EXIST;
759 END IF;
760
761 -- Standard call to get message count and if count is 1, get message info.
762 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
763 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
764 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3200: Result Is_Article_exist? : ['||l_return_value||']');
765 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3300: Leaving Is_Article_exist');
766 END IF;
767 RETURN l_return_value ;
768
769 EXCEPTION
770 WHEN FND_API.G_EXC_ERROR THEN
771 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
772 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'3400: Leaving Is_Article_exist : OKC_API.G_EXCEPTION_ERROR Exception');
773 END IF;
774 x_return_status := G_RET_STS_ERROR ;
775 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
776 RETURN FND_API.G_FALSE ;
777
778 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
779 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
780 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'3500: Leaving Is_Article_exist : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
781 END IF;
782 x_return_status := G_RET_STS_UNEXP_ERROR ;
783 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
784 RETURN FND_API.G_FALSE ;
785
786 WHEN OTHERS THEN
787 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
788 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'3600: Leaving Is_Article_exist because of EXCEPTION: '||sqlerrm);
789 END IF;
790
791 IF find_art_crs%ISOPEN THEN
792 CLOSE find_art_crs;
793 END IF;
794 x_return_status := G_RET_STS_UNEXP_ERROR ;
795 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
796 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
797 END IF;
798 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
799 RETURN FND_API.G_FALSE ;
800 END Is_Article_exist ;
801
802 FUNCTION Is_Article_Exist(
803
804
805 p_doc_type IN VARCHAR2,
806 p_doc_id IN NUMBER
807 ) RETURN VARCHAR2 IS
808 l_return_status Varchar2(1);
809 l_msg_data FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
810 l_msg_count NUMBER;
811 l_return_value Varchar2(30);
812 BEGIN
813 l_return_value:=Is_Article_Exist(P_api_version => 1,
814 p_init_msg_list => FND_API.G_FALSE,
815 p_doc_type => p_doc_type,
816 p_doc_id => p_doc_id,
817 x_return_status => l_return_status,
818 x_msg_data => l_msg_data,
819 x_msg_count => l_msg_count);
820
821 IF l_return_status <> G_RET_STS_SUCCESS THEN
822 l_return_value := NULL;
823 END IF;
824 return l_return_value;
825 END Is_Article_Exist;
826
827
828
829 /* 11.5.10+ code changes for COntract Repository Start
830 11-OCT-2004 pnayani updated Is_Document_Updatable FUNCTION
831 Added logic to check for REPOSITORY class documents
832 14-MAR-2005 andixit Updated Repository logic.
833 */
834
835 FUNCTION Is_Document_Updatable(
836 p_doc_type IN VARCHAR2,
837 p_doc_id IN NUMBER,
838 p_validation_string IN VARCHAR2
839 ) RETURN VARCHAR2 IS -- 'T' - updatable, 'F'- nonupdatable, 'E' - doesn't exist
840 l_api_version CONSTANT NUMBER := 1;
841 l_api_name CONSTANT VARCHAR2(30) := 'Is_Document_Updatable';
842 l_return_status Varchar2(1);
843 l_msg_data FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
844 l_msg_count NUMBER;
845 l_return_value Varchar2(1) := 'E';
846 l_org_id NUMBER := (-99);
847 l_init_msg_list VARCHAR2(2000);
848
849 CURSOR tmpl_crs(p_org_id NUMBER) IS
850 SELECT Decode(STATUS_CODE,'PENDING_APPROVAL',G_FALSE,'EXPIRED',G_FALSE,
851 Decode(NVL(HIDE_YN,'N'), 'N',
852 Decode(c.cnt,0,
853 Decode( NVL(t.ORG_ID,p_org_id), p_org_id,G_TRUE, G_FALSE)
854 ,G_FALSE)
855 ,G_FALSE)
856 ) upd
857 FROM okc_terms_templates_all t,
858 (SELECT Count(*) cnt FROM OKC_TERMS_TEMPLATES_ALL i WHERE i.PARENT_TEMPLATE_ID = p_doc_id) c
859 WHERE template_id = p_doc_id;
860
861 --11.5.10+ code changes for COntract Repository Start
862 CURSOR doc_class_cur IS
863 SELECT document_type_class
864 FROM okc_bus_doc_types_b
865 WHERE document_type = p_doc_type;
866
867 l_doc_class okc_bus_doc_types_b.document_type_class%TYPE;
868 --11.5.10+ code changes for COntract Repository end
869
870 CURSOR tu_csr IS
871 SELECT G_TRUE
872 FROM OKC_TEMPLATE_USAGES
873 WHERE DOCUMENT_TYPE = p_doc_type
874 AND DOCUMENT_ID = p_doc_id
875 UNION ALL
876 SELECT G_TRUE
877 FROM dual
878 WHERE NOT EXISTS (SELECT 'Y' FROM OKC_TEMPLATE_USAGES
879 WHERE DOCUMENT_TYPE = p_doc_type
880 AND DOCUMENT_ID = p_doc_id
881 )
882 AND EXISTS ( SELECT 'Y' FROM okc_deliverables
883 WHERE business_document_type=p_doc_type
884 AND business_DOCUMENT_ID=p_doc_id
885
886 );
887
888 BEGIN
889
890 IF p_doc_type='TEMPLATE' THEN
891
892 l_org_id := mo_global.get_current_org_id();
893
894 OPEN tmpl_crs(l_org_id);
895 FETCH tmpl_crs INTO l_return_value;
896 CLOSE tmpl_crs;
897 ELSE
898 --11.5.10+ code changes for COntract Repository Start
899 OPEN doc_class_cur;
900 FETCH doc_class_cur INTO l_doc_class;
901 CLOSE doc_class_cur;
902 IF l_doc_class = 'REPOSITORY' THEN
903 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
904 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3650: Calling OKC_REP_UTIL_PVT.ok_to_commit');
905 END IF;
906 l_return_value := OKC_REP_UTIL_PVT.ok_to_commit(
907 p_api_version => 1,
908 p_init_msg_list => l_init_msg_list,
909 p_validation_string => G_REP_CHECK_STATUS,
910 p_doc_id => p_doc_id,
911 x_return_status => l_return_status,
912 x_msg_count => l_msg_count,
913 x_msg_data => l_msg_data);
914
915 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
916 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3651: AFter Calling OKC_REP_UTIL_PVT.ok_to_commit ');
917 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3652: l_return_value : '||l_return_value);
918 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3653: l_return_status : '||l_return_status);
919 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3654: l_msg_count : '||l_msg_count);
920 END IF;
921
922 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
923 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
924 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
925 RAISE FND_API.G_EXC_ERROR ;
926 END IF;
927 --11.5.10+ code changes for COntract Repository end
928
929 ELSE
930
931 OPEN tu_csr;
932 FETCH tu_csr INTO l_return_value;
933 CLOSE tu_csr;
934
935 END IF; -- l_doc_class = 'REPOSITORY'
936 END IF; -- p_doc_type='TEMPLATE'
937 return l_return_value;
938
939 EXCEPTION
940 WHEN OTHERS THEN
941 IF tu_csr%ISOPEN THEN
942 CLOSE tu_csr;
943 END IF;
944 IF doc_class_cur%ISOPEN THEN
945 CLOSE doc_class_cur;
946 END IF;
947 return l_return_value;
948
949 END Is_Document_Updatable;
950
951 FUNCTION Deviation_From_Standard(
952 p_doc_type IN VARCHAR2,
953 p_doc_id IN NUMBER
954 ) RETURN VARCHAR2 IS
955 l_return_status Varchar2(1);
956 l_msg_data FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
957 l_msg_count NUMBER;
958 l_return_value Varchar2(30);
959 BEGIN
960
961 l_return_value:=Deviation_From_Standard(p_api_version => 1,
962 p_init_msg_list => FND_API.G_FALSE,
963 p_doc_type => p_doc_type,
964 p_doc_id => p_doc_id,
965 x_return_status => l_return_status,
966 x_msg_data => l_msg_data,
967 x_msg_count => l_msg_count);
968
969 IF l_return_status <> G_RET_STS_SUCCESS THEN
970 l_return_value := NULL;
971 END IF;
972 return l_return_value;
973 END;
974
975 /*
976 -- To be used to find out if Terms and deliverable has deviate any deviation as
977 -- compared to template that was used in the document.ocument has used.
978 -- Possible return values NO_CHANGE,ARTICLES_CHANGED,DELIVERABLES_CHANGED,
979 -- ARTICLES_AND_DELIVERABLES_CHANGED
980 */
981 FUNCTION Deviation_From_Standard(
982 p_api_version IN NUMBER,
983 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
984
985 x_return_status OUT NOCOPY VARCHAR2,
986 x_msg_data OUT NOCOPY VARCHAR2,
987 x_msg_count OUT NOCOPY NUMBER,
988
989 p_doc_type IN VARCHAR2,
990 p_doc_id IN NUMBER
991 ) RETURN VARCHAR2 IS
992 l_api_version CONSTANT NUMBER := 1;
993 l_api_name CONSTANT VARCHAR2(30) := 'Deviation_From_Standard';
994 l_return_value VARCHAR2(100) := G_NO_CHANGE;
995 l_article_changed VARCHAR2(1) := 'N';
996 l_deliverable_changed VARCHAR2(1) := 'N';
997
998 CURSOR find_art_crs IS
999 SELECT 'Y'
1000 FROM okc_k_articles_b a
1001 WHERE ( document_type=p_doc_type AND document_id=p_doc_id AND source_flag
1002 IS NULL )
1003
1004 OR (
1005 document_type='TEMPLATE' AND document_id IN
1006 (SELECT template_id FROM okc_template_usages
1007 WHERE document_type=p_doc_type AND document_id=p_doc_id )
1008 AND NOT EXISTS
1009 (SELECT 'x' from okc_k_articles_b b
1010 WHERE b.document_type=p_doc_type AND b.document_id=p_doc_id
1011 AND ( b.sav_sae_id=a.sav_sae_id /*or b.ref_article_id=a.sav_sae_id*/) )
1012 )
1013 ;
1014
1015 BEGIN
1016 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1017 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3700: Entered Deviation_From_Standard');
1018 END IF;
1019 -- Standard call to check for call compatibility.
1020 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1021 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1022 END IF;
1023 -- Initialize message list if p_init_msg_list is set to TRUE.
1024 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1025 FND_MSG_PUB.initialize;
1026 END IF;
1027 -- Initialize API return status to success
1028 x_return_status := G_RET_STS_SUCCESS;
1029
1030 OPEN find_art_crs;
1031 FETCH find_art_crs INTO l_article_changed;
1032 CLOSE find_art_crs;
1033
1034 IF l_deliverable_changed='Y' THEN
1035 IF l_article_changed='Y' THEN
1036 l_return_value := G_ART_AND_DELIV_CHANGED;
1037 ELSE
1038 l_return_value := G_DELIVERABLES_CHANGED;
1039 END IF;
1040 ELSE
1041 IF l_article_changed='Y' THEN
1042 l_return_value := G_ARTICLES_CHANGED;
1043 ELSE
1044 l_return_value := G_NO_CHANGE;
1045 END IF;
1046 END IF;
1047
1048 -- Standard call to get message count and if count is 1, get message info.
1049 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1050 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1051 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3800: Result Deviation_From_Standard? : ['||l_return_value||']');
1052 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3900: Leaving Deviation_From_Standard');
1053 END IF;
1054 RETURN l_return_value ;
1055 EXCEPTION
1056 WHEN FND_API.G_EXC_ERROR THEN
1057 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1058 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'4000: Leaving Deviation_From_Standard : OKC_API.G_EXCEPTION_ERROR Exception');
1059 END IF;
1060 x_return_status := G_RET_STS_ERROR ;
1061 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1062 RETURN NULL ;
1063
1064 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1065 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1066 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'4100: Leaving Deviation_From_Standard : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
1067 END IF;
1068 x_return_status := G_RET_STS_UNEXP_ERROR ;
1069 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1070 RETURN NULL ;
1071
1072 WHEN OTHERS THEN
1073 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1074 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'4200: Leaving Deviation_From_Standard because of EXCEPTION: '||sqlerrm);
1075 END IF;
1076
1077 IF find_art_crs%ISOPEN THEN
1078 CLOSE find_art_crs;
1079 END IF;
1080 x_return_status := G_RET_STS_UNEXP_ERROR ;
1081 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1082 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1083 END IF;
1084 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1085 RETURN NULL ;
1086 END Deviation_From_Standard ;
1087
1088 /*
1089 --To be used to find out if template used in document has expired.Possible return values Y,N.
1090 -- Possible return values are
1091 -- FND_API.G_TRUE = Template expired
1092 -- FND_API.G_FALSE = Template not expired.
1093 */
1094 FUNCTION Is_Template_Expired(
1095 p_api_version IN NUMBER,
1096 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1097
1098 x_return_status OUT NOCOPY VARCHAR2,
1099 x_msg_data OUT NOCOPY VARCHAR2,
1100 x_msg_count OUT NOCOPY NUMBER,
1101
1102 p_doc_type IN VARCHAR2,
1103 p_doc_id IN NUMBER
1104 ) RETURN VARCHAR2 IS
1105
1106
1107 l_api_version CONSTANT NUMBER := 1;
1108 l_api_name CONSTANT VARCHAR2(30) := 'Is_Template_Expired';
1109 l_return_value VARCHAR2(1) := 'N';
1110
1111 CURSOR find_tmpl_crs IS
1112 SELECT 'Y'
1113 FROM okc_template_usages_v tu,
1114 okc_terms_templates_all t
1115 WHERE tu.document_type = p_doc_type AND tu.document_id = p_doc_id
1116 AND t.template_id = tu.template_id AND t.status_code = 'APPROVED'
1117 AND nvl(t.end_date,sysdate+1) <= sysdate;
1118
1119 BEGIN
1120
1121 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1122 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4300: Entered Is_Template_Expired');
1123 END IF;
1124
1125 -- Standard call to check for call compatibility.
1126 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1127 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1128 END IF;
1129
1130 -- Initialize message list if p_init_msg_list is set to TRUE.
1131 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1132 FND_MSG_PUB.initialize;
1133 END IF;
1134
1135 -- Initialize API return status to success
1136 x_return_status := G_RET_STS_SUCCESS;
1137
1138 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1139 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4400: Looking for Template');
1140 END IF;
1141
1142 OPEN find_tmpl_crs;
1143 FETCH find_tmpl_crs INTO l_return_value;
1144 CLOSE find_tmpl_crs ;
1145
1146 -- Standard call to get message count and if count is 1, get message info.
1147 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1148
1149 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1150 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4500: Result Is_Template_Expired? : ['||l_return_value||']');
1151 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4600: Leaving Is_Template_Expired');
1152 END IF;
1153
1154 RETURN l_return_value ;
1155
1156 EXCEPTION
1157 WHEN FND_API.G_EXC_ERROR THEN
1158 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1159 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'4700: Leaving Is_Template_Expired : OKC_API.G_EXCEPTION_ERROR Exception');
1160 END IF;
1161
1162 x_return_status := G_RET_STS_ERROR ;
1163 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1164 RETURN NULL ;
1165
1166 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1167 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1168 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'4800: Leaving Is_Template_Expired : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
1169 END IF;
1170
1171 x_return_status := G_RET_STS_UNEXP_ERROR ;
1172 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1173 RETURN NULL ;
1174
1175 WHEN OTHERS THEN
1176 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1177 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'4900: Leaving Is_Template_Expired because of EXCEPTION: '||sqlerrm);
1178 END IF;
1179
1180 IF find_tmpl_crs%ISOPEN THEN
1181 CLOSE find_tmpl_crs;
1182 END IF;
1183
1184 x_return_status := G_RET_STS_UNEXP_ERROR ;
1185
1186 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1187 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1188 END IF;
1189
1190 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1191 RETURN NULL ;
1192 END Is_Template_Expired;
1193
1194 /*
1195 --To be used to find out if any deliverable exists on document.If Yes then what type.
1196 -- Possible values: NONE, ONLY_CONTRACTUAL, ONLY_INTERNAL, CONTRACTUAL_AND_INTERNAL
1197 */
1198
1199
1200 FUNCTION Is_Deliverable_Exist(
1201 p_api_version IN NUMBER,
1202 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1203
1204 x_return_status OUT NOCOPY VARCHAR2,
1205 x_msg_data OUT NOCOPY VARCHAR2,
1206 x_msg_count OUT NOCOPY NUMBER,
1207
1208 p_doc_type IN VARCHAR2,
1209 p_doc_id IN NUMBER
1210 ) RETURN VARCHAR2 IS
1211 l_api_name CONSTANT VARCHAR2(30) := 'Is_Deliverable_Exist';
1212 l_return_value varchar2(100);
1213 BEGIN
1214 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1215 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5000: Entering Is_Deliverable_Exist');
1216 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5100: Parameters ');
1217 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5200: p_doc_type : '||p_doc_type);
1218 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5300: p_doc_id : '||p_doc_id);
1219 END IF;
1220
1221 x_return_status := G_RET_STS_SUCCESS;
1222 l_return_value :=OKC_DELIVERABLE_PROCESS_PVT.deliverables_exist(
1223 p_api_version => p_api_version,
1224 p_init_msg_list => p_init_msg_list,
1225 x_msg_data => x_msg_data,
1226 x_msg_count => x_msg_count,
1227 x_return_status => x_return_status,
1228 p_docid => p_doc_id,
1229 p_doctype => p_doc_type
1230 );
1231
1232 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1233 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5400: After Call to OKC_DELIVERABLE_PROCESS_PVT.deliverables_exist');
1234 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5500: x_return_status : '||x_return_status);
1235 END IF;
1236
1237 IF l_return_value IS NULL THEN
1238 x_return_status := G_RET_STS_ERROR ;
1239 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1240 END IF;
1241
1242 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1243 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5600: Leaving Is_Deliverable_Exist');
1244 END IF;
1245
1246 Return l_return_value;
1247 END;
1248
1249
1250 /*
1251 --To be used in amend flow to find out if any article is amended.If Yes then what
1252 -- type of article is amended.Possible values NO_ARTICLE_AMENDED,ONLY_STANDARD_AMENDED ,NON_STANDARD_AMENDED
1253 */
1254
1255 FUNCTION Is_Article_Amended(
1256 p_api_version IN NUMBER,
1257 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1258
1259 x_return_status OUT NOCOPY VARCHAR2,
1260 x_msg_data OUT NOCOPY VARCHAR2,
1261 x_msg_count OUT NOCOPY NUMBER,
1262
1263 p_doc_type IN VARCHAR2,
1264 p_doc_id IN NUMBER
1265 ) RETURN VARCHAR2 IS
1266 l_api_version CONSTANT NUMBER := 1;
1267 l_api_name CONSTANT VARCHAR2(30) := 'Is_Article_AMENDED';
1268 l_dummy VARCHAR2(1) := '?';
1269 l_return_value VARCHAR2(100) := G_NO_ARTICLE_AMENDED;
1270 CURSOR find_art_crs IS
1271 SELECT a.standard_yn
1272 FROM okc_k_articles_b kart, okc_articles_all a
1273 WHERE kart.document_type=p_doc_type
1274 AND kart.document_id=p_doc_id
1275 AND kart.summary_amend_operation_code IS NOT NULL
1276 AND a.article_id = kart.sav_sae_id
1277 ORDER BY Decode(a.standard_yn,'N',1) ASC ;
1278 BEGIN
1279 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1280 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5700: Entered Is_Article_AMENDED');
1281 END IF;
1282 -- Standard call to check for call compatibility.
1283 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1284 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1285 END IF;
1286 -- Initialize message list if p_init_msg_list is set to TRUE.
1287 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1288 FND_MSG_PUB.initialize;
1289 END IF;
1290 -- Initialize API return status to success
1291 x_return_status := G_RET_STS_SUCCESS;
1292
1293 OPEN find_art_crs;
1294 FETCH find_art_crs INTO l_dummy;
1295 CLOSE find_art_crs;
1296
1297 IF l_dummy='Y' THEN
1298 l_return_value := G_ONLY_STANDARD_ART_AMENDED;
1299 ELSIF l_dummy='N' THEN
1300 l_return_value := G_NON_STANDARD_ART_AMENDED;
1301 END IF;
1302
1303 -- Standard call to get message count and if count is 1, get message info.
1304
1305 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1306
1307 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1308 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5800: Result Is_Article_AMENDED? : ['||l_return_value||']');
1309 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5900: Leaving Is_Article_AMENDED');
1310 END IF;
1311
1312 RETURN l_return_value ;
1313
1314 EXCEPTION
1315 WHEN FND_API.G_EXC_ERROR THEN
1316 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1317 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'6000: Leaving Is_Article_AMENDED : OKC_API.G_EXCEPTION_ERROR Exception');
1318 END IF;
1319 x_return_status := G_RET_STS_ERROR ;
1320 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1321 RETURN NULL ;
1322
1323 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1324
1325 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1326 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'6100: Leaving Is_Article_AMENDED : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
1327 END IF;
1328 x_return_status := G_RET_STS_UNEXP_ERROR ;
1329 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1330 RETURN NULL ;
1331
1332 WHEN OTHERS THEN
1333 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1334 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'6200: Leaving Is_Article_AMENDED because of EXCEPTION: '||sqlerrm);
1335 END IF;
1336
1337 IF find_art_crs%ISOPEN THEN
1338 CLOSE find_art_crs;
1339 END IF;
1340
1341 x_return_status := G_RET_STS_UNEXP_ERROR ;
1342
1343 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1344 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1345 END IF;
1346 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1347 RETURN NULL ;
1348
1349 END Is_Article_AMENDED;
1350
1351 /*
1352 -- To be used in amend flow to find out if any deliverable is amended.
1353 -- If Yes then what type.Possible values
1354 -- NONE,ONLY_CONTRACTUAL,ONLY_INTERNAL,CONTRACTUAL_AND_INTERNAL
1355 */
1356
1357 FUNCTION Is_Deliverable_Amended(
1358 p_api_version IN NUMBER,
1359 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1360
1361 x_return_status OUT NOCOPY VARCHAR2,
1362 x_msg_data OUT NOCOPY VARCHAR2,
1363 x_msg_count OUT NOCOPY NUMBER,
1364
1365 p_doc_type IN VARCHAR2,
1366 p_doc_id IN NUMBER
1367 ) RETURN VARCHAR2 IS
1368 l_api_name CONSTANT VARCHAR2(30) := 'Is_Deliverable_Amended';
1369 l_return_value varchar2(100);
1370 BEGIN
1371 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1372 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'6300: Entering Is_Deliverable_Amended');
1373 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'6400: Parameters ');
1374 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'6500: p_doc_type : '||p_doc_type);
1375 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'6600: p_doc_id : '||p_doc_id);
1376 END IF;
1377 x_return_status := G_RET_STS_SUCCESS;
1378 l_return_value :=OKC_DELIVERABLE_PROCESS_PVT.deliverables_amended (
1379 p_api_version => p_api_version,
1380 p_init_msg_list => p_init_msg_list,
1381 x_msg_data => x_msg_data,
1382 x_msg_count => x_msg_count,
1383 x_return_status => x_return_status,
1384 p_docid => p_doc_id,
1385 p_doctype => p_doc_type
1386 );
1387
1388 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1389 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'6700: After Call to OKC_DELIVERABLE_PROCESS_PVT.deliverables_amended');
1390 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'6800: x_return_status : '||x_return_status);
1391 END IF;
1392
1393 IF l_return_value IS NULL THEN
1394 x_return_status := G_RET_STS_ERROR ;
1395 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1396 END IF;
1397 Return l_return_value;
1398 END;
1399
1400
1401 --This API is deprecated. Use GET_CONTRACT_DETAILS() instead.
1402 PROCEDURE Get_Terms_Template(
1403 p_api_version IN NUMBER,
1404 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1405
1406 x_return_status OUT NOCOPY VARCHAR2,
1407 x_msg_data OUT NOCOPY VARCHAR2,
1408 x_msg_count OUT NOCOPY NUMBER,
1409
1410 p_doc_type IN VARCHAR2,
1411 p_doc_id IN NUMBER,
1412 x_template_id OUT NOCOPY NUMBER,
1413 x_template_name OUT NOCOPY VARCHAR2
1414 ) IS
1415
1416 l_api_version CONSTANT NUMBER := 1;
1417 l_api_name CONSTANT VARCHAR2(30) := 'Get_Terms_Template';
1418 l_dummy VARCHAR2(1) := '?';
1419 l_return_value VARCHAR2(100) := G_NO_ARTICLE_AMENDED;
1420
1421 CURSOR find_tmpl_crs IS
1422 SELECT t.template_id, t.template_name
1423 FROM okc_template_usages_v tu, okc_terms_templates_all t
1424 WHERE tu.document_type = p_doc_type AND tu.document_id = p_doc_id
1425 AND t.template_id = tu.template_id;
1426
1427 BEGIN
1428
1429 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1430 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'6900: Entered Get_Terms_Template');
1431 END IF;
1432
1433 -- Standard call to check for call compatibility.
1434 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1435 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1436 END IF;
1437
1438 -- Initialize message list if p_init_msg_list is set to TRUE.
1439 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1440 FND_MSG_PUB.initialize;
1441 END IF;
1442
1443 -- Initialize API return status to success
1444 x_return_status := G_RET_STS_SUCCESS;
1445
1446 OPEN find_tmpl_crs;
1447 FETCH find_tmpl_crs INTO x_template_id, x_template_name;
1448 CLOSE find_tmpl_crs;
1449
1450 -- Standard call to get message count and if count is 1, get message info.
1451 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1452
1453 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1454 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7000: Result Get_Terms_Template? : ['||l_return_value||']');
1455 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7100: Leaving Get_Terms_Template');
1456 END IF;
1457
1458 EXCEPTION
1459 WHEN FND_API.G_EXC_ERROR THEN
1460
1461 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1462 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7200: Leaving Get_Terms_Template : OKC_API.G_EXCEPTION_ERROR Exception');
1463 END IF;
1464
1465 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1466 x_return_status := G_RET_STS_ERROR ;
1467
1468 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1469 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1470 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7300: Leaving Get_Terms_Template : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
1471 END IF;
1472
1473 x_return_status := G_RET_STS_UNEXP_ERROR ;
1474 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1475
1476 WHEN OTHERS THEN
1477
1478 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1479 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7400: Leaving Get_Terms_Template because of EXCEPTION:'||sqlerrm);
1480 END IF;
1481 IF find_tmpl_crs%ISOPEN THEN
1482 CLOSE find_tmpl_crs;
1483 END IF;
1484 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1485 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1486 END IF;
1487 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1488 x_return_status := G_RET_STS_UNEXP_ERROR ;
1489
1490 END Get_Terms_Template;
1491
1492 /*
1493 -- To be used to find out document type when document is of contract family.
1494 */
1495 FUNCTION Get_Contract_Document_Type(
1496 p_api_version IN NUMBER,
1497 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1498
1499 x_return_status OUT NOCOPY VARCHAR2,
1500 x_msg_data OUT NOCOPY VARCHAR2,
1501 x_msg_count OUT NOCOPY NUMBER,
1502
1503 p_chr_id IN NUMBER
1504 ) RETURN VARCHAR2 IS
1505 l_api_version CONSTANT NUMBER := 1;
1506 l_api_name CONSTANT VARCHAR2(30) := 'Get_Contract_Document_Type';
1507 l_return_value VARCHAR2(10);
1508 CURSOR find_chrtype_crs IS
1509 SELECT
1510 decode(application_id,515,'OKS',510,decode(buy_or_sell,'S','OKC_SELL','B','OKC_BUY','OKC_SELL'),871,'OKO',777,decode(buy_or_sell,'S','OKE_SELL','B','OKE_BUY','OKE_SELL'),540,'OKL',Null)
1511 FROM okc_k_headers_b
1512 WHERE id=p_chr_id;
1513 BEGIN
1514 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1515 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7500: Entered Get_Contract_Document_Type');
1516 END IF;
1517 -- Standard call to check for call compatibility.
1518 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1519 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1520 END IF;
1521 -- Initialize message list if p_init_msg_list is set to TRUE.
1522 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1523 FND_MSG_PUB.initialize;
1524 END IF;
1525 -- Initialize API return status to success
1526 x_return_status := G_RET_STS_SUCCESS;
1527
1528 OPEN find_chrtype_crs;
1529 FETCH find_chrtype_crs INTO l_return_value;
1530 CLOSE find_chrtype_crs;
1531
1532 IF l_return_value IS NULL THEN
1533 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1534 END IF;
1535
1536 -- Standard call to get message count and if count is 1, get message info.
1537 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1538 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1539 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7600: Result Get_Contract_Document_Type? : ['||l_return_value||']');
1540 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7700: Leaving Get_Contract_Document_Type');
1541 END IF;
1542 RETURN l_return_value ;
1543 EXCEPTION
1544 WHEN FND_API.G_EXC_ERROR THEN
1545 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1546 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7800: Leaving Get_Contract_Document_Type : OKC_API.G_EXCEPTION_ERROR Exception');
1547 END IF;
1548 x_return_status := G_RET_STS_ERROR ;
1549 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1550 RETURN NULL ;
1551
1552 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1553 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1554 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7900: Leaving Get_Contract_Document_Type : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
1555 END IF;
1556 x_return_status := G_RET_STS_UNEXP_ERROR ;
1557 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1558 RETURN NULL ;
1559
1560 WHEN OTHERS THEN
1561 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1562 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'8000: Leaving Get_Contract_Document_Type because of EXCEPTION: '||sqlerrm);
1563 END IF;
1564
1565 IF find_chrtype_crs%ISOPEN THEN
1566 CLOSE find_chrtype_crs;
1567 END IF;
1568 x_return_status := G_RET_STS_UNEXP_ERROR ;
1569 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1570 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1571 END IF;
1572 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1573 RETURN NULL ;
1574 END Get_Contract_Document_Type;
1575 /*
1576 -- To be used to find out document type/ID when document is of contract family.
1577 */
1578 PROCEDURE Get_Contract_Document_Type_ID(
1579 p_api_version IN NUMBER,
1580 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1581
1582 x_return_status OUT NOCOPY VARCHAR2,
1583 x_msg_data OUT NOCOPY VARCHAR2,
1584 x_msg_count OUT NOCOPY NUMBER,
1585
1586 p_chr_id IN NUMBER,
1587 x_doc_id OUT NOCOPY NUMBER,
1588 x_doc_type OUT NOCOPY VARCHAR2
1589 ) IS
1590 l_api_version CONSTANT NUMBER := 1;
1591 l_api_name CONSTANT VARCHAR2(30) := 'Get_Contract_Document_Type_Id';
1592 l_notfound BOOLEAN;
1593 CURSOR find_chrtype_crs IS
1594 SELECT
1595 decode(application_id,515,'OKS',510,decode(buy_or_sell,'S','OKC_SELL','B','OKC_BUY','OKC_SELL'),871,'OKO',777,decode(buy_or_sell,'S','OKE_SELL','B','OKE_BUY','OKE_SELL'),540,'OKL',Null),
1596 document_id
1597 FROM okc_k_headers_b
1598 WHERE id=p_chr_id;
1599 BEGIN
1600 x_doc_type := NULL;
1601 x_doc_id := NULL;
1602
1603 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1604 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'8100: Entered Get_Contract_Document_Type_id');
1605 END IF;
1606 -- Standard call to check for call compatibility.
1607 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1608 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1609 END IF;
1610 -- Initialize message list if p_init_msg_list is set to TRUE.
1611 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1612 FND_MSG_PUB.initialize;
1613 END IF;
1614 -- Initialize API return status to success
1615 x_return_status := G_RET_STS_SUCCESS;
1616
1617 OPEN find_chrtype_crs;
1618 FETCH find_chrtype_crs INTO x_doc_type, x_doc_id;
1619 l_notfound := find_chrtype_crs%NOTFOUND;
1620 CLOSE find_chrtype_crs;
1621
1622 IF l_notfound THEN
1623 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1624 END IF;
1625
1626 -- Standard call to get message count and if count is 1, get message info.
1627 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1628 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1629 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'8200: Result Document_Type : ['||x_doc_type||']');
1630 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'8300: Result Document_ID : ['||x_doc_id||']');
1631 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'8400: Leaving Get_Contract_Document_Type');
1632 END IF;
1633 EXCEPTION
1634 WHEN FND_API.G_EXC_ERROR THEN
1635 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1636 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'8500: Leaving Get_Contract_Document_Type_id : OKC_API.G_EXCEPTION_ERROR Exception');
1637 END IF;
1638 x_return_status := G_RET_STS_ERROR ;
1639 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1640
1641 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1642 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1643 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'8600: Leaving Get_Contract_Document_Type_id : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
1644 END IF;
1645 x_return_status := G_RET_STS_UNEXP_ERROR ;
1646 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1647
1648 WHEN OTHERS THEN
1649 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1650 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'8700: Leaving Get_Contract_Document_Type_id because of EXCEPTION: '||sqlerrm);
1651 END IF;
1652
1653 IF find_chrtype_crs%ISOPEN THEN
1654 CLOSE find_chrtype_crs;
1655 END IF;
1656 x_return_status := G_RET_STS_UNEXP_ERROR ;
1657 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1658 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1659 END IF;
1660 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1661 END Get_Contract_Document_Type_Id;
1662 /*
1663 -- To be used to find out document type when document is of contract family.
1664 */
1665 PROCEDURE Get_Last_Update_Date (
1666 p_api_version IN NUMBER,
1667 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1668
1669 x_return_status OUT NOCOPY VARCHAR2,
1670 x_msg_data OUT NOCOPY VARCHAR2,
1671 x_msg_count OUT NOCOPY NUMBER,
1672
1673 p_doc_type IN VARCHAR2,
1674 p_doc_id IN NUMBER,
1675
1676 x_deliverable_changed_date OUT NOCOPY DATE,
1677 x_terms_changed_date OUT NOCOPY DATE
1678 ) IS
1679
1680 l_api_version CONSTANT NUMBER := 1;
1681 l_api_name CONSTANT VARCHAR2(30) := 'Get_Last_Update_Date';
1682 l_article_change_date date;
1683 l_section_change_date date;
1684 l_article_h_change_date date;
1685 l_section_h_change_date date;
1686 l_contract_source_code okc_template_usages.contract_source_code%TYPE := 'STRUCTURED';
1687
1688 Cursor l_get_max_art_date_csr IS
1689 SELECT max(Nvl(LAST_AMENDMENT_DATE,CREATION_DATE))
1690 FROM OKC_K_ARTICLES_B
1691 WHERE DOCUMENT_TYPE=p_doc_type
1692 AND DOCUMENT_ID=p_doc_id;
1693
1694 Cursor l_get_max_scn_date_csr IS
1695 SELECT max(Nvl(LAST_AMENDMENT_DATE,CREATION_DATE))
1696 FROM OKC_SECTIONS_B
1697 WHERE DOCUMENT_TYPE=p_doc_type
1698 AND DOCUMENT_ID=p_doc_id;
1699
1700 Cursor l_get_max_art_hist_date_csr IS
1701 SELECT max(Nvl(LAST_AMENDMENT_DATE,CREATION_DATE))
1702 FROM OKC_K_ARTICLES_BH
1703 WHERE DOCUMENT_TYPE=p_doc_type
1704 AND DOCUMENT_ID=p_doc_id;
1705
1706 Cursor l_get_max_scn_hist_date_csr IS
1707 SELECT max(Nvl(LAST_AMENDMENT_DATE,CREATION_DATE))
1708 FROM OKC_SECTIONS_BH
1709 WHERE DOCUMENT_TYPE=p_doc_type
1710 AND DOCUMENT_ID=p_doc_id;
1711
1712 Cursor l_get_contract_source_csr IS
1713 SELECT contract_source_code
1714 FROM okc_template_usages
1715 WHERE document_type = p_doc_type
1716 AND document_id = p_doc_id;
1717
1718 Cursor l_get_max_usg_upd_date_csr IS
1719 SELECT MAX(last_update_date)
1720 FROM okc_template_usages
1721 WHERE document_type = p_doc_type
1722 AND document_id = p_doc_id;
1723
1724 BEGIN
1725 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1726 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'8800: Entered get_last_update_date');
1727 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'8900: Parameters');
1728 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'9000: p_api_version : '||p_api_version);
1729 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'9100: p_init_msg_list : '||p_init_msg_list);
1730 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'9200: p_doc_type : '||p_doc_type);
1731 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'9300: p_doc_id : '||p_doc_id);
1732 END IF;
1733
1734 -- Standard call to check for call compatibility.
1735 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name , G_PKG_NAME) THEN
1736 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1737 END IF;
1738
1739 -- Initialize message list if p_init_msg_list is set to TRUE.
1740 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1741 FND_MSG_PUB.initialize;
1742 END IF;
1743 -- Initialize API return status to success
1744 x_return_status := G_RET_STS_SUCCESS;
1745
1746 OPEN l_get_contract_source_csr;
1747 FETCH l_get_contract_source_csr INTO l_contract_source_code;
1748 CLOSE l_get_contract_source_csr;
1749
1750
1751 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1752 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'9310: After fetching l_get_contract_source_csr');
1753 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'9320: Contract Source Code :'||l_contract_source_code);
1754 END IF;
1755
1756 IF l_contract_source_code = G_ATTACHED_CONTRACT_SOURCE THEN
1757 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1758 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'9330: Before opening l_get_max_usg_upd_date_csr');
1759 END IF;
1760
1761 OPEN l_get_max_usg_upd_date_csr;
1762 FETCH l_get_max_usg_upd_date_csr INTO x_terms_changed_date;
1763 CLOSE l_get_max_usg_upd_date_csr;
1764
1765 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1766 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'9340: After fetching l_get_max_usg_upd_date_csr');
1767 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'9350: l_terms_changed_date :'||x_terms_changed_date);
1768 END IF;
1769
1770 ELSE
1771
1772
1773 OPEN l_get_max_art_date_csr;
1774 FETCH l_get_max_art_date_csr INTO l_article_change_date;
1775 CLOSE l_get_max_art_date_csr;
1776
1777 OPEN l_get_max_scn_date_csr;
1778 FETCH l_get_max_scn_date_csr INTO l_section_change_date;
1779 CLOSE l_get_max_scn_date_csr;
1780
1781 OPEN l_get_max_art_hist_date_csr;
1782 FETCH l_get_max_art_hist_date_csr INTO l_article_h_change_date;
1783 CLOSE l_get_max_art_hist_date_csr;
1784
1785 OPEN l_get_max_scn_hist_date_csr;
1786 FETCH l_get_max_scn_hist_date_csr INTO l_section_h_change_date;
1787 CLOSE l_get_max_scn_hist_date_csr;
1788
1789 --Bug 3659714
1790 l_article_change_date := nvl(l_article_change_date,okc_api.g_miss_date);
1791 l_section_change_date := nvl(l_section_change_date,okc_api.g_miss_date);
1792
1793 x_terms_changed_date := Greatest(l_article_change_date, l_section_change_date,
1794 NVL(l_article_h_change_date,l_article_change_date),
1795 NVL(l_section_h_change_date,l_section_change_date));
1796
1797 END IF;
1798
1799 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1800 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'9400: x_terms_changed_date : '||x_terms_changed_date);
1801 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'9500: Before Calling okc_deliverable_process_pvt.get_last_amendment_date');
1802 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'9600: p_busdoc_id : '||p_doc_id);
1803 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'9700: p_busdoc_type : '||p_doc_type);
1804 END IF;
1805
1806 x_deliverable_changed_date := okc_deliverable_process_pvt.get_last_amendment_date (
1807 p_api_version => p_api_version,
1808 p_init_msg_list => p_init_msg_list,
1809 x_msg_data => x_msg_data,
1810 x_msg_count => x_msg_count,
1811 x_return_status => x_return_status,
1812 p_busdoc_id => p_doc_id,
1813 p_busdoc_type => p_doc_type,
1814 p_busdoc_version => -99);
1815
1816
1817 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1818 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'9800: After Calling okc_deliverable_process_pvt.get_last_amendment_date');
1819 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'9900: x_return_status : '||x_return_status);
1820 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'10000: x_deliverable_changed_date : '||x_deliverable_changed_date);
1821 END IF;
1822
1823
1824 EXCEPTION
1825 WHEN FND_API.G_EXC_ERROR THEN
1826 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1827 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'10100: Leaving get_last_update_date : OKC_API.G_EXCEPTION_ERROR Exception');
1828 END IF;
1829
1830 IF l_get_max_art_date_csr%ISOPEN THEN
1831 CLOSE l_get_max_art_date_csr;
1832 END IF;
1833
1834 x_return_status := G_RET_STS_ERROR ;
1835 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1836
1837 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1838 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1839 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'10200: Leaving get_last_update_date : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
1840 END IF;
1841
1842 IF l_get_max_art_date_csr%ISOPEN THEN
1843 CLOSE l_get_max_art_date_csr;
1844 END IF;
1845
1846 x_return_status := G_RET_STS_UNEXP_ERROR ;
1847 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1848
1849 WHEN OTHERS THEN
1850 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1851 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'10300: Leaving get_last_update_date because of EXCEPTION: '||sqlerrm);
1852 END IF;
1853
1854 IF l_get_max_art_date_csr%ISOPEN THEN
1855 CLOSE l_get_max_art_date_csr;
1856 END IF;
1857
1858
1859 x_return_status := G_RET_STS_UNEXP_ERROR ;
1860 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1861 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1862 END IF;
1863 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1864 END get_last_update_date;
1865
1866 FUNCTION Ok_To_Commit (
1867 p_api_version IN NUMBER,
1868 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1869
1870 x_return_status OUT NOCOPY VARCHAR2,
1871 x_msg_data OUT NOCOPY VARCHAR2,
1872 x_msg_count OUT NOCOPY NUMBER,
1873 p_tmpl_change IN VARCHAR2,
1874 p_validation_string IN VARCHAR2,
1875 p_doc_type IN VARCHAR2,
1876 p_doc_id IN NUMBER
1877 ,p_del_commit_mode IN VARCHAR2 DEFAULT NULL
1878
1879 ) RETURN Varchar2 IS
1880 l_api_version CONSTANT NUMBER := 1;
1881 l_api_name CONSTANT VARCHAR2(30) := 'ok_to_commit';
1882 l_ok_to_commit Varchar2(1) := G_FALSE;
1883 l_doc_class VARCHAR2(30) := '?';
1884 l_tmpl_status VARCHAR2(30);
1885 l_end_date DATE;
1886
1887 CURSOR doc_cls_lst_crs IS
1888 SELECT document_type_class
1889 FROM okc_bus_doc_types_b
1890 WHERE document_type=p_doc_type;
1891 CURSOR l_tmpl_csr IS
1892 SELECT status_code,nvl(end_date,sysdate+1) end_date
1893 FROM okc_terms_templates_all
1894 WHERE template_id = p_doc_id;
1895 BEGIN
1896
1897 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1898 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'10400: Entering Ok_To_Commit');
1899 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'10500: Parameter List ');
1900 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'10600: p_api_version : '||p_api_version);
1901 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'10700: p_init_msg_list : '||p_init_msg_list);
1902 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'10800: p_tmpl_change : '||p_tmpl_change);
1903 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'10900: p_validation_string : '||p_validation_string );
1904 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'11000: p_doc_type : '||p_doc_type);
1905 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'11100: p_doc_id : '||p_doc_id);
1906 END IF;
1907
1908 -- Standard call to check for call compatibility.
1909 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1910 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1911 END IF;
1912
1913 -- Initialize message list if p_init_msg_list is set to TRUE.
1914 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1915 FND_MSG_PUB.initialize;
1916 END IF;
1917
1918 IF p_doc_type = G_TMPL_DOC_TYPE THEN
1919 OPEN l_tmpl_csr;
1920 FETCH l_tmpl_csr INTO l_tmpl_status,l_end_date;
1921 CLOSE l_tmpl_csr;
1922
1923 IF l_tmpl_status IN ('DRAFT','REJECTED','REVISION') THEN
1924 l_ok_to_commit := G_TRUE;
1925 ELSE
1926 l_ok_to_commit := G_FALSE;
1927 END IF;
1928 ELSE
1929 IF nvl(p_validation_string,'?') <> 'OKC_TEST_UI' THEN
1930 OPEN doc_cls_lst_crs;
1931 FETCH doc_cls_lst_crs INTO l_doc_class;
1932 CLOSE doc_cls_lst_crs;
1933
1934 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1935 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'11200: l_doc_class : '||l_doc_class);
1936 END IF;
1937
1938 IF l_doc_class = 'PO' THEN
1939
1940 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1941 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'11300: Calling OKC_PO_INT_GRP.ok_to_commit ');
1942 END IF;
1943
1944 l_ok_to_commit := OKC_PO_INT_GRP.ok_to_commit(
1945 p_api_version => 1,
1946 p_init_msg_list => p_init_msg_list,
1947 p_tmpl_change => p_tmpl_change,
1948 p_validation_string => p_validation_string,
1949 p_doc_type => p_doc_type,
1950 p_doc_id => p_doc_id,
1951 x_return_status => x_return_status,
1952 x_msg_count => x_msg_count,
1953 x_msg_data => x_msg_data
1954 ,p_del_commit_mode => p_del_commit_mode
1955
1956 );
1957
1958 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1959 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'11400: AFter Calling OKC_PO_INT_GRP.ok_to_commit ');
1960 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'11500: l_ok_to_commit : '||l_ok_to_commit);
1961 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'11600: x_return_status : '||x_return_status);
1962 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'11700: x_msg_count : '||x_msg_count);
1963 END IF;
1964
1965 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1966 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1967 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1968 RAISE FND_API.G_EXC_ERROR ;
1969 END IF;
1970
1971 ELSIF l_doc_class = 'SOURCING' THEN
1972
1973 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1974 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'11800: Calling OKC_PON_INT_GRP.ok_to_commit');
1975 END IF;
1976
1977 l_ok_to_commit := OKC_PON_INT_GRP.ok_to_commit(
1978 p_api_version => 1,
1979 p_init_msg_list => p_init_msg_list,
1980 p_validation_string => p_validation_string,
1981 p_doc_id => p_doc_id,
1982 p_doc_type => p_doc_type,
1983 x_return_status => x_return_status,
1984 x_msg_count => x_msg_count,
1985 x_msg_data => x_msg_data
1986 );
1987
1988 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1989 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'11900: AFter Calling OKC_PON_INT_GRP.ok_to_commit ');
1990 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'12000: l_ok_to_commit : '||l_ok_to_commit);
1991 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'12100: x_return_status : '||x_return_status);
1992 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'12200: x_msg_count : '||x_msg_count);
1993 END IF;
1994
1995 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1996 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1997 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1998 RAISE FND_API.G_EXC_ERROR ;
1999 END IF;
2000
2001 ELSIF l_doc_class in ('BSA','SO') THEN
2002 -- ELSIF l_doc_class = 'OM' THEN
2003
2004 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2005 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'12300: Calling OKC_OM_INT_GRP.ok_to_commit');
2006 END IF;
2007
2008 l_ok_to_commit := OKC_OM_INT_GRP.ok_to_commit(
2009 p_api_version => 1,
2010 p_init_msg_list => p_init_msg_list,
2011 p_tmpl_change => p_tmpl_change,
2012 p_validation_string => p_validation_string,
2013 p_doc_id => p_doc_id,
2014 x_return_status => x_return_status,
2015 x_msg_count => x_msg_count,
2016 x_msg_data => x_msg_data
2017 );
2018
2019 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2020 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'12400: AFter Calling OKC_OM_INT_GRP.ok_to_commit ');
2021 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'12500: l_ok_to_commit : '||l_ok_to_commit);
2022 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'12600: x_return_status : '||x_return_status);
2023 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'12700: x_msg_count : '||x_msg_count);
2024 END IF;
2025
2026 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2027 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2028 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2029 RAISE FND_API.G_EXC_ERROR ;
2030 END IF;
2031
2032 ELSIF l_doc_class = 'OKS' THEN
2033
2034 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2035 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'12800: Calling OKC_OKS_INT_GRP.ok_to_commit');
2036 END IF;
2037
2038 l_ok_to_commit := OKC_OKS_INT_GRP.ok_to_commit(
2039 p_api_version => 1,
2040 p_init_msg_list => p_init_msg_list,
2041 p_validation_string => p_validation_string,
2042 p_doc_id => p_doc_id,
2043 x_return_status => x_return_status,
2044 x_msg_count => x_msg_count,
2045 x_msg_data => x_msg_data
2046 );
2047
2048 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2049 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'12900: AFter Calling OKC_OKS_INT_GRP.ok_to_commit ');
2050 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'13000: l_ok_to_commit : '||l_ok_to_commit);
2051 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'13100: x_return_status : '||x_return_status);
2052 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'13200: x_msg_count : '||x_msg_count);
2053 END IF;
2054
2055 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2056 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2057 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2058 RAISE FND_API.G_EXC_ERROR ;
2059 END IF;
2060 --
2061 ELSIF l_doc_class = 'QUOTE' THEN
2062
2063 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2064 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'13300: Calling OKC_ASO_INT_GRP.ok_to_commit');
2065 END IF;
2066
2067 l_ok_to_commit := OKC_ASO_INT_GRP.ok_to_commit(
2068 p_api_version => 1,
2069 p_init_msg_list => p_init_msg_list,
2070 p_validation_string => p_validation_string,
2071 p_doc_id => p_doc_id,
2072 p_doc_type => p_doc_type,
2073 x_return_status => x_return_status,
2074 x_msg_count => x_msg_count,
2075 x_msg_data => x_msg_data
2076 );
2077
2078 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2079 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'13400: AFter Calling OKC_ASO_INT_GRP.ok_to_commit ');
2080 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'13500: l_ok_to_commit : '||l_ok_to_commit);
2081 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'13600: x_return_status : '||x_return_status);
2082 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'13700: x_msg_count : '||x_msg_count);
2083 END IF;
2084
2085 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2086 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2087 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2088 RAISE FND_API.G_EXC_ERROR ;
2089 END IF;
2090
2091 --
2092
2093 ELSIF l_doc_class = 'REPOSITORY' THEN
2094
2095 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2096 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'13800: Calling OKC_REP_UTIL_PVT.ok_to_commit');
2097 END IF;
2098
2099 l_ok_to_commit := OKC_REP_UTIL_PVT.ok_to_commit(
2100 p_api_version => 1,
2101 p_init_msg_list => p_init_msg_list,
2102 p_validation_string => p_validation_string,
2103 p_doc_id => p_doc_id,
2104 x_return_status => x_return_status,
2105 x_msg_count => x_msg_count,
2106 x_msg_data => x_msg_data);
2107
2108 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2109 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'13900: AFter Calling OKC_REP_UTIL_PVT.ok_to_commit ');
2110 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'14000: l_ok_to_commit : '||l_ok_to_commit);
2111 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'14100: x_return_status : '||x_return_status);
2112 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'14200: x_msg_count : '||x_msg_count);
2113 END IF;
2114
2115 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2116 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2117 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2118 RAISE FND_API.G_EXC_ERROR ;
2119 END IF;
2120
2121 ELSE
2122 l_ok_to_commit := G_TRUE;
2123 END IF;
2124 ELSE
2125 l_ok_to_commit := G_TRUE;
2126 END IF;
2127 END IF;
2128
2129 IF l_ok_to_commit=FND_API.G_FALSE THEN
2130
2131 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2132 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'13800: Issue with document header Record.Cannot commit');
2133 END IF;
2134 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2135 p_msg_name => 'OKC_OK_TO_COMMIT'
2136 );
2137 END IF;
2138
2139 RETURN l_ok_to_commit;
2140
2141 EXCEPTION
2142 WHEN FND_API.G_EXC_ERROR THEN
2143 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2144 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'13900: Leaving ok_to_commit: OKC_API.G_EXCEPTION_ERROR Exception');
2145 END IF;
2146 x_return_status := G_RET_STS_ERROR ;
2147 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2148 RETURN FND_API.G_FALSE;
2149
2150 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2151 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2152 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'14000: Leaving ok_to_commit: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
2153 END IF;
2154 x_return_status := G_RET_STS_UNEXP_ERROR ;
2155 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2156 RETURN FND_API.G_FALSE;
2157
2158 WHEN OTHERS THEN
2159 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2160 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'14100: Leaving ok_to_commit because of EXCEPTION: '||sqlerrm);
2161 END IF;
2162
2163 x_return_status := G_RET_STS_UNEXP_ERROR ;
2164 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2165 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2166 END IF;
2167 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2168 RETURN FND_API.G_FALSE;
2169
2170 END ok_to_commit;
2171
2172 /*
2173 --To be used to find out if a document has any manually added articles.
2174 Returns FND_API.G_TRUE => If atleast 1 article is manually added.
2175 FND_API.G_FALSE => If no manually added article exists.
2176 */
2177
2178 FUNCTION is_manual_article_exist(
2179 p_api_version IN NUMBER,
2180 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2181
2182 x_return_status OUT NOCOPY VARCHAR2,
2183 x_msg_data OUT NOCOPY VARCHAR2,
2184 x_msg_count OUT NOCOPY NUMBER,
2185
2186 p_doc_type IN VARCHAR2,
2187 p_doc_id IN NUMBER
2188 ) RETURN VARCHAR2 IS
2189 l_api_version CONSTANT NUMBER := 1;
2190 l_api_name CONSTANT VARCHAR2(30) := 'is_manual_article_exist';
2191 l_dummy VARCHAR2(1) := '?';
2192 l_return_value VARCHAR2(1) := FND_API.G_FALSE;
2193
2194 CURSOR find_art_crs IS
2195 SELECT '!'
2196 FROM okc_k_articles_b kart
2197 WHERE kart.document_type=p_doc_type
2198 AND kart.document_id=p_doc_id
2199 AND kart.source_flag IS NULL
2200 AND nvl(kart.amendment_operation_code,'?')<>G_AMEND_CODE_DELETED
2201 AND nvl(kart.summary_amend_operation_code,'?')<>G_AMEND_CODE_DELETED;
2202
2203 BEGIN
2204 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2205 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'14200: Entered is_manual_article_exist');
2206 END IF;
2207 -- Standard call to check for call compatibility.
2208 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2209 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2210 END IF;
2211 -- Initialize message list if p_init_msg_list is set to TRUE.
2212 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2213 FND_MSG_PUB.initialize;
2214 END IF;
2215 -- Initialize API return status to success
2216 x_return_status := G_RET_STS_SUCCESS;
2217
2218 OPEN find_art_crs;
2219 FETCH find_art_crs INTO l_dummy;
2220 CLOSE find_art_crs;
2221
2222 IF l_dummy='?' THEN
2223 l_return_value := FND_API.G_FALSE;
2224 ELSE
2225 l_return_value := FND_API.G_TRUE;
2226 END IF;
2227
2228 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2229 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'14300: Leaving is_manual_article_exist');
2230 END IF;
2231
2232 -- Standard call to get message count and if count is 1, get message info.
2233 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2234 RETURN l_return_value ;
2235
2236 EXCEPTION
2237 WHEN FND_API.G_EXC_ERROR THEN
2238 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2239 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'14400: Leaving is_manual_article_exist : OKC_API.G_EXCEPTION_ERROR Exception');
2240 END IF;
2241 x_return_status := G_RET_STS_ERROR ;
2242 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2243 RETURN NULL ;
2244
2245 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2246 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2247 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'14500: Leaving is_manual_article_exist : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
2248 END IF;
2249 x_return_status := G_RET_STS_UNEXP_ERROR ;
2250 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2251 RETURN NULL ;
2252
2253 WHEN OTHERS THEN
2254 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2255 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'14600: Leaving is_manual_article_exist because of EXCEPTION: '||sqlerrm);
2256 END IF;
2257
2258 IF find_art_crs%ISOPEN THEN
2259 CLOSE find_art_crs;
2260 END IF;
2261 x_return_status := G_RET_STS_UNEXP_ERROR ;
2262 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2263 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2264 END IF;
2265 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2266 RETURN NULL ;
2267 END is_manual_article_exist ;
2268
2269
2270 FUNCTION Get_Template_Name(
2271 p_api_version IN NUMBER,
2272 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2273 p_template_id IN NUMBER,
2274
2275 x_return_status OUT NOCOPY VARCHAR2,
2276 x_msg_data OUT NOCOPY VARCHAR2,
2277 x_msg_count OUT NOCOPY NUMBER
2278 ) RETURN VARCHAR2 IS
2279
2280 l_api_version CONSTANT NUMBER := 1;
2281 l_api_name CONSTANT VARCHAR2(30) := 'Get_Template_Name';
2282 l_return_value VARCHAR2(500);
2283
2284 CURSOR c_get_template_name IS
2285 SELECT template_name
2286 FROM okc_terms_templates_all
2287 WHERE template_id = p_template_id;
2288 BEGIN
2289 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2290 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'14700: Get_Template_Name');
2291 END IF;
2292
2293 -- Standard call to check for call compatibility.
2294 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2295 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2296 END IF;
2297
2298 -- Initialize message list if p_init_msg_list is set to TRUE.
2299 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2300 FND_MSG_PUB.initialize;
2301 END IF;
2302
2303 -- Initialize API return status to success
2304 x_return_status := G_RET_STS_SUCCESS;
2305
2306
2307 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2308 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'14800: Opening cursor c_get_template_name');
2309 END IF;
2310
2311 IF c_get_template_name%ISOPEN THEN
2312 CLOSE c_get_template_name;
2313 END IF;
2314 OPEN c_get_template_name;
2315 FETCH c_get_template_name INTO l_return_value;
2316 CLOSE c_get_template_name;
2317
2318
2319 -- Standard call to get message count and if count is 1, get message info.
2320 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2321
2322 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2323 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'14900: Result Get_Template_Name : ['||l_return_value||']');
2324 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'15000: Leaving Get_Template_Name');
2325 END IF;
2326
2327 RETURN l_return_value ;
2328
2329 EXCEPTION
2330 WHEN FND_API.G_EXC_ERROR THEN
2331 IF c_get_template_name%ISOPEN THEN
2332 CLOSE c_get_template_name;
2333 END IF;
2334 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2335 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'15100: Leaving Get_Template_Name : OKC_API.G_EXCEPTION_ERROR Exception');
2336 END IF;
2337 x_return_status := G_RET_STS_ERROR ;
2338 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2339 RETURN NULL ;
2340
2341 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2342 IF c_get_template_name%ISOPEN THEN
2343 CLOSE c_get_template_name;
2344 END IF;
2345 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2346 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'15200: Leaving Get_Template_Name : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
2347 END IF;
2348 x_return_status := G_RET_STS_UNEXP_ERROR ;
2349 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2350 RETURN NULL ;
2351
2352 WHEN OTHERS THEN
2353 IF c_get_template_name%ISOPEN THEN
2354 CLOSE c_get_template_name;
2355 END IF;
2356 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2357 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'15300: Leaving Get_Template_Name because of EXCEPTION: '||sqlerrm);
2358 END IF;
2359
2360 x_return_status := G_RET_STS_UNEXP_ERROR ;
2361 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2362 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2363 END IF;
2364 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2365 RETURN NULL ;
2366
2367
2368 END Get_Template_Name;
2369
2370
2371 --This API is deprecated. Use GET_CONTRACT_DETAILS() instead.
2372 Function Get_Terms_Template(
2373 p_doc_type IN VARCHAR2,
2374 p_doc_id IN NUMBER
2375 ) Return varchar2 IS
2376 l_template_name OKC_TERMS_TEMPLATES_ALL.TEMPLATE_NAME%TYPE;
2377 l_template_ID OKC_TERMS_TEMPLATES_ALL.TEMPLATE_ID%TYPE;
2378 l_return_status Varchar2(1);
2379 l_msg_count NUMBER;
2380 l_msg_data FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
2381 BEGIN
2382 Get_Terms_Template(p_api_version =>1,
2383 p_init_msg_list => FND_API.G_FALSE,
2384 x_return_status => l_return_status,
2385 x_msg_count => l_msg_count,
2386 x_msg_data => l_msg_data,
2387 p_doc_type => p_doc_type,
2388 p_doc_id => p_doc_id,
2389 x_template_id => l_template_id,
2390 x_template_name => l_template_name);
2391
2392 If l_return_status <>G_RET_STS_SUCCESS THEN
2393 l_template_name := NULL;
2394 END IF;
2395 return l_template_name;
2396 END Get_Terms_Template;
2397
2398 PROCEDURE get_item_dtl_for_expert(
2399 p_api_version IN NUMBER,
2400 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2401
2402 x_return_status OUT NOCOPY VARCHAR2,
2403 x_msg_data OUT NOCOPY VARCHAR2,
2404 x_msg_count OUT NOCOPY NUMBER,
2405
2406 p_doc_type IN VARCHAR2,
2407 p_doc_id IN NUMBER,
2408 x_category_tbl OUT NOCOPY item_tbl_type,
2409 x_item_tbl OUT NOCOPY item_tbl_type
2410 ) IS
2411 l_api_version CONSTANT NUMBER := 1;
2412 l_api_name CONSTANT VARCHAR2(30) := 'get_item_dtl_for_expert';
2413 l_doc_class VARCHAR2(30) := '?';
2414 CURSOR doc_cls_lst_crs IS
2415 SELECT document_type_class
2416 FROM okc_bus_doc_types_b
2417 WHERE document_type=p_doc_type;
2418 BEGIN
2419 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2420 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'15400: Entered get_item_dtl_for_expert');
2421 END IF;
2422
2423 -- Standard call to check for call compatibility.
2424 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2425 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2426 END IF;
2427 -- Initialize message list if p_init_msg_list is set to TRUE.
2428 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2429 FND_MSG_PUB.initialize;
2430 END IF;
2431 -- Initialize API return status to success
2432 x_return_status := G_RET_STS_SUCCESS;
2433
2434
2435 OPEN doc_cls_lst_crs;
2436 FETCH doc_cls_lst_crs INTO l_doc_class;
2437 CLOSE doc_cls_lst_crs;
2438
2439 -- Based on Doc type call API's provided by different integrating system and pass the pl/sql table prepared ins tep 2.These API will check which variable has changed and return list of only those variables which have changed.
2440 -- For example: If document type is 'BPO' then call API provided by PO team.
2441 -- If document type is 'BSA' then call API provided by OM team.
2442 -- Parameter to these API's will be p_doc_id IN Number, p_var_tbl IN/OUT pl/sql table having one column variable_code
2443
2444 IF l_doc_class in ('BSA','SO') THEN
2445 -- IF l_doc_class = 'OM' THEN
2446
2447 OKC_OM_INT_GRP.get_item_dtl_for_expert (
2448 p_api_version => p_api_version ,
2449 p_init_msg_list => p_init_msg_list,
2450 x_msg_data => x_msg_data ,
2451 x_msg_count => x_msg_count ,
2452 x_return_status => x_return_status,
2453 p_doc_type => p_doc_type,
2454 p_doc_id => p_doc_id,
2455 x_category_tbl => x_category_tbl,
2456 x_item_tbl => x_item_tbl
2457 );
2458
2459 --------------------------------------------
2460 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2461 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2462 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2463 RAISE FND_API.G_EXC_ERROR ;
2464 END IF;
2465 --------------------------------------------
2466
2467 ELSIF l_doc_class = 'PO' THEN
2468
2469
2470 OKC_PO_INT_GRP.get_item_dtl_for_expert(
2471 p_api_version => p_api_version ,
2472 p_init_msg_list => p_init_msg_list,
2473 x_msg_data => x_msg_data ,
2474 x_msg_count => x_msg_count ,
2475 x_return_status => x_return_status,
2476 p_doc_type => p_doc_type,
2477 p_doc_id => p_doc_id,
2478 x_item_tbl => x_item_tbl,
2479 x_category_tbl => x_category_tbl);
2480
2481 --------------------------------------------
2482 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2483 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2484 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2485 RAISE FND_API.G_EXC_ERROR ;
2486 END IF;
2487 --------------------------------------------
2488
2489 ELSIF l_doc_class = 'SOURCING' THEN
2490
2491 OKC_PON_INT_GRP.get_item_dtl_for_expert(
2492 p_api_version => p_api_version,
2493 p_init_msg_list => p_init_msg_list,
2494 x_msg_data => x_msg_data ,
2495 x_msg_count => x_msg_count ,
2496 x_return_status => x_return_status,
2497 p_doc_type => p_doc_type,
2498 p_doc_id => p_doc_id,
2499 x_item_tbl => x_item_tbl,
2500 x_category_tbl => x_category_tbl);
2501
2502 --------------------------------------------
2503 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2504 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2505 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2506 RAISE FND_API.G_EXC_ERROR ;
2507 END IF;
2508 --------------------------------------------
2509 ELSIF l_doc_class = 'QUOTE' THEN
2510
2511 OKC_ASO_INT_GRP.get_item_dtl_for_expert(
2512 p_api_version => p_api_version,
2513 p_init_msg_list => p_init_msg_list,
2514 x_msg_data => x_msg_data ,
2515 x_msg_count => x_msg_count ,
2516 x_return_status => x_return_status,
2517 -- p_doc_type => p_doc_type,
2518 p_doc_id => p_doc_id,
2519 x_item_tbl => x_item_tbl,
2520 x_category_tbl => x_category_tbl);
2521
2522 --------------------------------------------
2523 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2524 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2525 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2526 RAISE FND_API.G_EXC_ERROR ;
2527 END IF;
2528 --------------------------------------------
2529 ELSE
2530 NULL;
2531 END IF;
2532
2533 -- Standard call to get message count and if count is 1, get message info.
2534 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2535 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2536 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'15500: Leaving get_item_dtl_for_expert');
2537 END IF;
2538 EXCEPTION
2539 WHEN FND_API.G_EXC_ERROR THEN
2540
2541 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2542 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'15600: Leaving get_item_dtl_for_expert : OKC_API.G_EXCEPTION_ERROR Exception');
2543 END IF;
2544 x_return_status := G_RET_STS_ERROR ;
2545 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2546
2547 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2548
2549 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2550 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'15700: Leaving get_item_dtl_for_expert : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
2551 END IF;
2552 x_return_status := G_RET_STS_UNEXP_ERROR ;
2553 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2554
2555 WHEN OTHERS THEN
2556
2557 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2558 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'15800: Leaving get_item_dtl_for_expert because of EXCEPTION: '||sqlerrm);
2559 END IF;
2560
2561 x_return_status := G_RET_STS_UNEXP_ERROR ;
2562 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2563 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2564 END IF;
2565 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2566
2567 END get_item_dtl_for_expert;
2568
2569 FUNCTION get_last_signed_revision(
2570 p_doc_type IN VARCHAR2,
2571 p_doc_id IN NUMBER,
2572 p_revision_num IN NUMBER
2573 ) RETURN NUMBER
2574 IS
2575 l_api_version CONSTANT NUMBER := 1;
2576 l_api_name CONSTANT VARCHAR2(30) := 'get_last_signed_revision';
2577 l_doc_class VARCHAR2(30) := '?';
2578 l_signed_version NUMBER;
2579 l_msg_count NUMBER;
2580 l_msg_data FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
2581 l_return_status VARCHAR2(1);
2582
2583 CURSOR doc_cls_lst_crs IS
2584 SELECT document_type_class
2585 FROM okc_bus_doc_types_b
2586 WHERE document_type=p_doc_type;
2587 BEGIN
2588 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2589 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'15900: Entered get_last_signed_revision');
2590 END IF;
2591
2592
2593 OPEN doc_cls_lst_crs;
2594 FETCH doc_cls_lst_crs INTO l_doc_class;
2595 CLOSE doc_cls_lst_crs;
2596
2597 -- Based on Doc type call API's provided by different integrating system and pass the pl/sql table prepared ins tep 2.These API will check which variable has changed and return list of only those variables which have changed.
2598 -- For example: If document type is 'BPO' then call API provided by PO team.
2599 -- If document type is 'BSA' then call API provided by OM team.
2600 -- Parameter to these API's will be p_doc_id IN Number, p_var_tbl IN/OUT pl/sql table having one column variable_code
2601
2602 IF l_doc_class = 'PO' THEN
2603
2604
2605 OKC_PO_INT_GRP.get_last_signed_revision(
2606 p_api_version => 1 ,
2607 p_init_msg_list => FND_API.G_FALSE,
2608 x_msg_data => l_msg_data ,
2609 x_msg_count => l_msg_count ,
2610 x_return_status => l_return_status,
2611
2612 p_doc_type => p_doc_type,
2613 p_doc_id => p_doc_id,
2614 p_revision_num => p_revision_num,
2615 x_signed_revision_num => l_signed_version);
2616
2617 --------------------------------------------
2618 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2619 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2620 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2621 RAISE FND_API.G_EXC_ERROR ;
2622 END IF;
2623 --------------------------------------------
2624
2625 ELSE
2626 NULL;
2627 END IF;
2628
2629 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2630 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'16000: Leaving get_last_signed_revision');
2631 END IF;
2632 RETURN l_signed_version;
2633
2634 EXCEPTION
2635 WHEN FND_API.G_EXC_ERROR THEN
2636
2637 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2638 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'16100: Leaving get_last_signed_revision : OKC_API.G_EXCEPTION_ERROR Exception');
2639 END IF;
2640 return NULL;
2641
2642 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2643
2644 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2645 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'16200: Leaving get_last_signed_revision : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
2646 END IF;
2647 return NULL;
2648
2649 WHEN OTHERS THEN
2650
2651 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2652 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'16300: Leaving get_last_signed_revision because of EXCEPTION: '||sqlerrm);
2653 END IF;
2654 return NULL;
2655 END get_last_signed_revision;
2656
2657
2658 Procedure Get_Terms_Template_dtl(
2659 p_template_id IN NUMBER,
2660 p_template_rec OUT NOCOPY template_rec_type,
2661 x_return_status OUT NOCOPY VARCHAR2,
2662 x_msg_data OUT NOCOPY VARCHAR2,
2663 x_msg_count OUT NOCOPY NUMBER
2664 ) IS
2665
2666 l_api_version CONSTANT NUMBER := 1;
2667 l_api_name CONSTANT VARCHAR2(30) := 'Get_Terms_Template_dtl';
2668 CURSOR l_get_template_dtl(b_template_id NUMBER) IS
2669 SELECT template_name,
2670 intent,
2671 status_code,
2672 start_date,
2673 end_date ,
2674 instruction_text ,
2675 description ,
2676 global_flag ,
2677 contract_expert_enabled,
2678 org_id
2679 FROM okc_terms_templates_all
2680 where template_id=b_template_id;
2681 BEGIN
2682 x_return_status := G_RET_STS_SUCCESS;
2683 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2684 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'16400: Entered Get_Terms_Template_dtl');
2685 END IF;
2686 OPEN l_get_template_dtl(p_template_id);
2687 FETCH l_get_template_dtl into p_template_rec.template_name,
2688 p_template_rec.intent,
2689 p_template_rec.status_code,
2690 p_template_rec.start_date,
2691 p_template_rec.end_date,
2692 p_template_rec.instruction_text,
2693 p_template_rec.description,
2694 p_template_rec.global_flag,
2695 p_template_rec.contract_expert_enabled,
2696 p_template_rec.org_id;
2697 IF l_get_template_dtl%NOTFOUND THEN
2698 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2699 p_msg_name => 'OKC_WRONG_TEMPLATE'
2700 );
2701 Raise FND_API.G_EXC_ERROR;
2702 END IF;
2703 CLOSE l_get_template_dtl;
2704
2705 -- Standard call to get message count and if count is 1, get message info.
2706 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2707 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2708 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'16500: Leaving Get_Terms_Template_dtl');
2709 END IF;
2710 EXCEPTION
2711 WHEN FND_API.G_EXC_ERROR THEN
2712
2713 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2714 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'16600: Leaving Get_Terms_Template_dtl : OKC_API.G_EXCEPTION_ERROR Exception');
2715 END IF;
2716 IF l_get_template_dtl%ISOPEN THEN
2717 CLOSE l_get_template_dtl;
2718 END IF;
2719 x_return_status := G_RET_STS_ERROR ;
2720 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2721
2722 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2723
2724 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2725 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'16700: Leaving Get_Terms_Template_dtl : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
2726 END IF;
2727 IF l_get_template_dtl%ISOPEN THEN
2728 CLOSE l_get_template_dtl;
2729 END IF;
2730 x_return_status := G_RET_STS_UNEXP_ERROR ;
2731 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2732
2733 WHEN OTHERS THEN
2734
2735 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2736 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'16800: Leaving Get_Terms_Template_dtl because of EXCEPTION: '||sqlerrm);
2737 END IF;
2738 IF l_get_template_dtl%ISOPEN THEN
2739 CLOSE l_get_template_dtl;
2740 END IF;
2741 x_return_status := G_RET_STS_UNEXP_ERROR ;
2742 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2743 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2744 END IF;
2745 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2746
2747 END Get_Terms_Template_dtl;
2748
2749 FUNCTION empclob
2750 RETURN CLOB IS
2751 c1 CLOB;
2752 BEGIN
2753 DBMS_LOB.CREATETEMPORARY(c1,true);
2754 DBMS_LOB.OPEN(c1,dbms_lob.lob_readwrite);
2755 DBMS_LOB.WRITE(c1,1,1,' ');
2756 RETURN c1;
2757 END empclob;
2758
2759 FUNCTION tempblob
2760 RETURN BLOB IS
2761 c1 BLOB;
2762 rawbuf RAW(10);
2763 BEGIN
2764 rawbuf := '1234567890123456789';
2765
2766 DBMS_LOB.CREATETEMPORARY(c1,true);
2767 DBMS_LOB.OPEN(c1,dbms_lob.lob_readwrite);
2768 DBMS_LOB.WRITE(c1,1,10,rawbuf);
2769 RETURN c1;
2770 END tempblob;
2771
2772 --This API is deprecated. Use GET_CONTRACT_DETAILS_ALL() instead.
2773 Procedure Get_Terms_Template_dtl(
2774 p_doc_id IN NUMBER,
2775 p_doc_type IN VARCHAR,
2776 x_template_id OUT NOCOPY NUMBER,
2777 x_template_name OUT NOCOPY VARCHAR2,
2778 x_template_description OUT NOCOPY VARCHAR2,
2779 x_template_instruction OUT NOCOPY VARCHAR2,
2780 x_return_status OUT NOCOPY VARCHAR2,
2781 x_msg_data OUT NOCOPY VARCHAR2,
2782 x_msg_count OUT NOCOPY NUMBER
2783 ) IS
2784
2785 l_api_version CONSTANT NUMBER := 1;
2786 l_api_name CONSTANT VARCHAR2(30) := 'Get_Terms_Template_dtl';
2787
2788 CURSOR terms_tmpl_csr IS
2789 SELECT t.template_id,
2790 t.template_name,
2791 t.instruction_text ,
2792 t.description
2793 FROM okc_template_usages_v tu, okc_terms_templates_all t
2794 WHERE tu.document_type = p_doc_type AND tu.document_id = p_doc_id
2795 AND t.template_id = tu.template_id;
2796
2797 BEGIN
2798 x_return_status := G_RET_STS_SUCCESS;
2799 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2800 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'16900: Entered Get_Terms_Template_dtl');
2801 END IF;
2802 OPEN terms_tmpl_csr;
2803 FETCH terms_tmpl_csr into x_template_id,
2804 x_template_name,
2805 x_template_instruction,
2806 x_template_description ;
2807
2808 CLOSE terms_tmpl_csr;
2809
2810 -- Standard call to get message count and if count is 1, get message info.
2811 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2812 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2813 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'17000: Leaving Get_Terms_Template_dtl');
2814 END IF;
2815 EXCEPTION
2816 WHEN FND_API.G_EXC_ERROR THEN
2817
2818 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2819 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'17100: Leaving Get_Terms_Template_dtl : OKC_API.G_EXCEPTION_ERROR Exception');
2820 END IF;
2821 IF terms_tmpl_csr%ISOPEN THEN
2822 CLOSE terms_tmpl_csr;
2823 END IF;
2824 x_return_status := G_RET_STS_ERROR ;
2825 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2826
2827 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2828
2829 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2830 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'17200: Leaving Get_Terms_Template_dtl : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
2831 END IF;
2832 IF terms_tmpl_csr%ISOPEN THEN
2833 CLOSE terms_tmpl_csr;
2834 END IF;
2835 x_return_status := G_RET_STS_UNEXP_ERROR ;
2836 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2837
2838 WHEN OTHERS THEN
2839
2840 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2841 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'17300: Leaving Get_Terms_Template_dtl because of EXCEPTION: '||sqlerrm);
2842 END IF;
2843 IF terms_tmpl_csr%ISOPEN THEN
2844 CLOSE terms_tmpl_csr;
2845 END IF;
2846 x_return_status := G_RET_STS_UNEXP_ERROR ;
2847 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2848 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2849 END IF;
2850 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2851
2852 END Get_Terms_Template_dtl;
2853
2854 FUNCTION enable_update(
2855 p_object_type IN VARCHAR2,
2856 p_document_type IN VARCHAR2,
2857 p_standard_yn IN VARCHAR2
2858 ) RETURN VARCHAR2 IS
2859 l_api_name CONSTANT VARCHAR2(30) := 'enable_update';
2860 BEGIN
2861 IF (p_object_type <> 'SECTION' AND p_object_type <> 'ARTICLE') THEN
2862 -- top most document node , always disable
2863 RETURN 'OkcTermsStructDtlsUpdateDisabled' ;
2864 ELSIF p_object_type = 'SECTION' THEN
2865 -- update always enabled for Sections
2866 RETURN 'OkcTermsStructDtlsUpdateEnabled' ;
2867 -- Article Cases
2868 ELSIF p_document_type = 'TEMPLATE' THEN
2869 -- always disable for template as the logic is based on template status and is in the controller code
2870 RETURN 'OkcTermsStructDtlsUpdateDisabled';
2871 ELSIF NVL(p_standard_yn,'N') = 'Y' THEN
2872 -- update always enabled for standard articles
2873 RETURN 'OkcTermsStructDtlsUpdateEnabled' ;
2874 -- non std articles
2875 ELSIF fnd_function.test('OKC_TERMS_AUTHOR_NON_STD','N') THEN
2876 -- user has access to fn and doc not template
2877 RETURN 'OkcTermsStructDtlsUpdateEnabled' ;
2878 ELSE
2879 -- user does NOT have access to function OKC_TERMS_AUTHOR_NON_STD
2880 RETURN 'OkcTermsStructDtlsUpdateDisabled' ;
2881 END IF;
2882 EXCEPTION
2883 WHEN OTHERS THEN
2884 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2885 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'2000: Leaving enable_update because of EXCEPTION: '||sqlerrm);
2886 END IF;
2887 RETURN NULL;
2888 END enable_update;
2889
2890 FUNCTION enable_delete(
2891 p_object_type IN VARCHAR2,
2892 p_mandatory_yn IN VARCHAR2,
2893 p_standard_yn IN VARCHAR2,
2894 p_document_type IN VARCHAR2
2895 ) RETURN VARCHAR2 IS
2896 l_api_name CONSTANT VARCHAR2(30) := 'enable_delete';
2897 BEGIN
2898 IF (p_object_type <> 'SECTION' AND p_object_type <> 'ARTICLE') THEN
2899 -- topmost document node, so disable delete
2900 RETURN 'OkcTermsStructDtlsRemoveDisabled';
2901 ELSIF p_object_type = 'SECTION' THEN
2902 -- Delete always enabled for sections as the API validates for mandatory articles check
2903 RETURN 'OkcTermsStructDtlsRemoveEnabled' ;
2904 -- ARTICLES LOGIC
2905 -- Case 1: MANDATORY ARTICLES
2906 ELSIF NVL(p_mandatory_yn,'N') = 'Y' THEN
2907 -- article is mandatory
2908 --Bug 4123003 If doc_type is template, delete button should be enabled
2909 IF p_document_type = 'TEMPLATE' THEN
2910 RETURN 'OkcTermsStructDtlsRemoveEnabled';
2911 ELSE
2912 IF (fnd_function.test('OKC_TERMS_AUTHOR_NON_STD','N') AND
2913 fnd_function.test('OKC_TERMS_AUTHOR_SUPERUSER','N')) THEN
2914 -- user has override controls, allow delete mandatory
2915 RETURN 'OkcTermsStructDtlsRemoveEnabled';
2916 ELSE
2917 RETURN 'OkcTermsStructDtlsRemoveDisabled';
2918 END IF;
2919 END IF;
2920 -- Case 2: STANDARD ARTICLES (non-mandatory)
2921 ELSIF NVL(p_standard_yn,'N') = 'Y' THEN
2922 -- for standard articles delete is always allowed
2923 RETURN 'OkcTermsStructDtlsRemoveEnabled' ;
2924 -- Case 3: NON-STANDARD ARTICLES (non-mandatory)
2925 ELSIF fnd_function.test('OKC_TERMS_AUTHOR_NON_STD','N') THEN
2926 -- for non-std articles check for function security
2927 -- user has access , so check allow delete for non-std articles
2928 RETURN 'OkcTermsStructDtlsRemoveEnabled' ;
2929 ELSE
2930 -- user does not have access to delete non-std articles
2931 RETURN 'OkcTermsStructDtlsRemoveDisabled';
2932 END IF;
2933
2934 EXCEPTION
2935 WHEN OTHERS THEN
2936 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2937 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'2000: Leaving enable_delete because of EXCEPTION: '||sqlerrm);
2938 END IF;
2939 RETURN NULL;
2940 END enable_delete;
2941
2942
2943
2944 /* Following API's are added for 11.5.10+ projects*/
2945
2946
2947 -- Returns 'Y' - Attached document is oracle generated and mergeable.
2948 -- 'N' - Non recognised format, non mergeable.
2949 -- 'E' - Error.
2950 FUNCTION Is_Primary_Terms_Doc_Mergeable(
2951 p_document_type IN VARCHAR2,
2952 p_document_id IN NUMBER
2953 ) RETURN VARCHAR2 IS
2954 l_api_name CONSTANT VARCHAR2(30) := 'Is_Primary_Terms_Doc_Mergeable';
2955 BEGIN
2956 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2957 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1800: Entering Is_Primary_Terms_Doc_Mergeable');
2958 END IF;
2959
2960 RETURN OKC_CONTRACT_DOCS_GRP.is_primary_terms_doc_mergeable(
2961 p_document_type => p_document_type,
2962 p_document_id => p_document_id );
2963
2964 EXCEPTION
2965 WHEN OTHERS THEN
2966 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2967 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'2000: Leaving Is_Primary_Terms_Doc_Mergeable because of EXCEPTION: '||sqlerrm);
2968 END IF;
2969 RETURN 'E';
2970 END Is_Primary_Terms_Doc_Mergeable;
2971
2972 -- Returns FND_DOCUMENTS_TL.media_id of the Primary contract file for the current version of the document if it is non mergeable.
2973 -- 0 if document is mergeable.
2974 -- -1 if no primary document exists.
2975 FUNCTION Get_Primary_Terms_Doc_File_Id(
2976 p_document_type IN VARCHAR2,
2977 p_document_id IN NUMBER
2978 ) RETURN NUMBER IS
2979 l_api_name CONSTANT VARCHAR2(30) := 'Get_Primary_Terms_Doc_File_Id';
2980 BEGIN
2981 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2982 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1800: Entering Get_Primary_Terms_Doc_File_Id');
2983 END IF;
2984
2985 RETURN OKC_CONTRACT_DOCS_GRP.get_primary_terms_doc_file_id(
2986 p_document_type => p_document_type,
2987 p_document_id => p_document_id );
2988
2989 EXCEPTION
2990 WHEN OTHERS THEN
2991 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2992 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'2000: Leaving Get_Primary_Terms_Doc_File_Id because of EXCEPTION: '||sqlerrm);
2993 END IF;
2994 RETURN -1;
2995 END Get_Primary_Terms_Doc_File_Id;
2996
2997 -- Returns 'Y' - Document has terms.
2998 -- 'N' - No template instantiated. Primary contract file not attached.
2999 FUNCTION Has_Terms(
3000 p_document_type IN VARCHAR2,
3001 p_document_id IN NUMBER
3002 ) RETURN VARCHAR2 IS
3003 l_api_name CONSTANT VARCHAR2(30) := 'Has_Terms';
3004 CURSOR tmpl_usages_csr IS
3005 SELECT 'Y'
3006 FROM okc_template_usages
3007 WHERE document_type = p_document_type
3008 AND document_id = p_document_id;
3009
3010 l_value VARCHAR2(1);
3011
3012 BEGIN
3013 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3014 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1800: Entering Has_Terms');
3015 END IF;
3016 OPEN tmpl_usages_csr ;
3017 FETCH tmpl_usages_csr into l_value;
3018 CLOSE tmpl_usages_csr ;
3019 IF l_value = 'Y' THEN
3020 RETURN 'Y';
3021 ELSE
3022 RETURN 'N';
3023 END IF;
3024 EXCEPTION
3025 WHEN OTHERS THEN
3026 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3027 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'2000: Leaving Has_Terms of EXCEPTION: '||sqlerrm);
3028 END IF;
3029 RETURN 'E';
3030 END Has_Terms;
3031
3032
3033 -- Returns 'Y' - Document has a template instantiated, or terms are in attached primary contract file.
3034 -- 'N' - No template instantiated. Primary contract file not attached.
3035 FUNCTION Has_Valid_Terms(
3036 p_document_type IN VARCHAR2,
3037 p_document_id IN NUMBER
3038 ) RETURN VARCHAR2 IS
3039 l_api_name CONSTANT VARCHAR2(30) := 'Has_Valid_Terms';
3040 CURSOR tmpl_usages_csr IS
3041 SELECT contract_source_code
3042 FROM okc_template_usages
3043 WHERE document_type = p_document_type
3044 AND document_id = p_document_id;
3045
3046 l_contract_source_code OKC_TEMPLATE_USAGES.CONTRACT_SOURCE_CODE%TYPE;
3047 l_rownotfound BOOLEAN := FALSE;
3048 BEGIN
3049 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3050 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1800: Entering Has_Valid_Terms');
3051 END IF;
3052 OPEN tmpl_usages_csr ;
3053 FETCH tmpl_usages_csr into l_contract_source_code;
3054 l_rownotfound := tmpl_usages_csr%NOTFOUND;
3055 CLOSE tmpl_usages_csr ;
3056
3057 IF l_rownotfound THEN
3058 RETURN 'N';
3059 ELSIF l_contract_source_code = 'STRUCTURED' THEN
3060 RETURN 'Y';
3061 ELSIF l_contract_source_code = 'ATTACHED' THEN
3062 RETURN OKC_CONTRACT_DOCS_GRP.has_primary_contract_doc(
3063 p_document_type => p_document_type,
3064 p_document_id => p_document_id);
3065 ELSE
3066 RETURN 'N';
3067 END IF;
3068
3069 EXCEPTION
3070 WHEN OTHERS THEN
3071 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3072 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'2000: Leaving Has_Valid_Terms of EXCEPTION: '||sqlerrm);
3073 END IF;
3074 RETURN 'E';
3075 END Has_Valid_Terms;
3076
3077
3078 --Returns name of the authoring party, source type of the contract and the template name if a template has been instantiated.
3079 Procedure Get_Contract_Details(
3080 p_api_version IN NUMBER,
3081 p_init_msg_list IN VARCHAR2 ,
3082
3083 x_return_status OUT NOCOPY VARCHAR2,
3084 x_msg_data OUT NOCOPY VARCHAR2,
3085 x_msg_count OUT NOCOPY NUMBER,
3086
3087 p_document_type IN VARCHAR2,
3088 p_document_id IN NUMBER,
3089
3090 x_authoring_party OUT NOCOPY VARCHAR2,
3091 x_contract_source OUT NOCOPY VARCHAR2,
3092 x_template_name OUT NOCOPY VARCHAR2,
3093 x_template_description OUT NOCOPY VARCHAR2
3094 ) IS
3095
3096 l_api_version CONSTANT NUMBER := 1;
3097 l_api_name CONSTANT VARCHAR2(30) := 'Get_Contract_Details';
3098 l_tmpl_name OKC_TERMS_TEMPLATES_ALL.TEMPLATE_NAME%TYPE;
3099 l_template_desc OKC_TERMS_TEMPLATES_ALL.DESCRIPTION%TYPE;
3100 l_template_id OKC_TERMS_TEMPLATES_ALL.TEMPLATE_ID%TYPE;
3101 l_document_id OKC_TEMPLATE_USAGES.DOCUMENT_ID%TYPE;
3102 l_authoring_party_code OKC_TEMPLATE_USAGES.AUTHORING_PARTY_CODE%TYPE;
3103 l_contract_source_code OKC_TEMPLATE_USAGES.CONTRACT_SOURCE_CODE%TYPE;
3104 l_authoring_party OKC_RESP_PARTIES_TL.ALTERNATE_NAME%TYPE;
3105 l_contract_source FND_LOOKUPS.MEANING%TYPE;
3106
3107 CURSOR terms_tmpl_csr IS
3108 SELECT tu.document_id,
3109 tu.authoring_party_code,
3110 tu.contract_source_code,
3111 tu.template_id,
3112 t.template_name,
3113 t.description,
3114 party.alternate_name authoring_party,
3115 src.meaning contract_source
3116 FROM OKC_TEMPLATE_USAGES tu,
3117 OKC_TERMS_TEMPLATES_ALL t,
3118 okc_resp_parties_vl party,
3119 okc_bus_doc_types_b doc,
3120 fnd_lookups src
3121 WHERE t.template_id(+) = tu.template_id
3122 AND tu.authoring_party_code = party.resp_party_code
3123 AND tu.document_type = doc.document_type
3124 AND doc.document_type_class = party.document_type_class
3125 AND NVL(doc.intent,'zzz') = NVL(party.intent,'zzz')
3126 AND src.lookup_type = 'OKC_CONTRACT_TERMS_SOURCES'
3127 AND src.lookup_code = tu.contract_source_code
3128 AND tu.document_type = p_document_type
3129 AND tu.document_id = p_document_id;
3130
3131 BEGIN
3132 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3133 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'15400: Entered Get_Contract_Details');
3134 END IF;
3135
3136 -- Standard call to check for call compatibility.
3137 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
3138 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3139 END IF;
3140 -- Initialize message list if p_init_msg_list is set to TRUE.
3141 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3142 FND_MSG_PUB.initialize;
3143 END IF;
3144 -- Initialize API return status to success
3145 x_return_status := G_RET_STS_SUCCESS;
3146
3147 OPEN terms_tmpl_csr;
3148 FETCH terms_tmpl_csr INTO l_document_id,l_authoring_party_code,l_contract_source_code,l_template_id,l_tmpl_name,l_template_desc,l_authoring_party,l_contract_source;
3149 CLOSE terms_tmpl_csr;
3150
3151 IF l_document_id IS NOT NULL THEN
3152 x_authoring_party := l_authoring_party;
3153 x_contract_source := l_contract_source;
3154 IF l_template_id IS NOT NULL THEN
3155 x_template_name := l_tmpl_name;
3156 x_template_description := l_template_desc;
3157 ELSE
3158 fnd_message.set_name('OKC','OKC_TERMS_TEMPLATE_NAME_NONE');
3159 x_template_name:= fnd_message.get;
3160 x_template_description := NULL;
3161 END IF;
3162 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3163 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'16200: Return success Get_Contract_Details');
3164 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'16300: x_authoring_party:'||x_authoring_party);
3165 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'16400: x_contract_source:'||x_contract_source);
3166 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'16500: x_template_name:'||x_template_name);
3167 END IF;
3168 ELSE
3169 fnd_message.set_name('OKC','OKC_TERMS_AUTH_PARTY_NONE');
3170 x_authoring_party := fnd_message.get;
3171 fnd_message.set_name('OKC','OKC_TERMS_CONTRACT_SOURCE_NONE');
3172 x_contract_source := fnd_message.get;
3173 fnd_message.set_name('OKC','OKC_TERMS_TEMPLATE_NAME_NONE');
3174 x_template_name:= fnd_message.get;
3175 x_template_description := NULL;
3176 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3177 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'16600: Return Get_Contract_Details,no terms exist');
3178 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'16700: x_authoring_party:'||x_authoring_party);
3179 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'16800: x_contract_source:'||x_contract_source);
3180 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'16900: x_template_name:'||x_template_name);
3181 END IF;
3182 END IF;
3183
3184 -- Standard call to get message count and if count is 1, get message info.
3185 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3186 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3187 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'17000: Leaving Get_Contract_Details');
3188 END IF;
3189 EXCEPTION
3190 WHEN FND_API.G_EXC_ERROR THEN
3191
3192 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3193 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'17100: Leaving Get_Contract_Details : OKC_API.G_EXCEPTION_ERROR Exception');
3194 END IF;
3195 x_return_status := G_RET_STS_ERROR ;
3196 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3197
3198 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3199
3200 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3201 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'17200: Leaving Get_Contract_Details : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
3202 END IF;
3203 x_return_status := G_RET_STS_UNEXP_ERROR ;
3204 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3205
3206 WHEN OTHERS THEN
3207
3208 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3209 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'17300: Leaving Get_Contract_Details because of EXCEPTION: '||sqlerrm);
3210 END IF;
3211 x_return_status := G_RET_STS_UNEXP_ERROR ;
3212 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
3213 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
3214 END IF;
3215 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3216
3217 END Get_Contract_Details;
3218
3219 --Returns terms details for the document.
3220 Procedure Get_Contract_Details_All(
3221 p_api_version IN NUMBER,
3222 p_init_msg_list IN VARCHAR2 ,
3223
3224 x_return_status OUT NOCOPY VARCHAR2,
3225 x_msg_data OUT NOCOPY VARCHAR2,
3226 x_msg_count OUT NOCOPY NUMBER,
3227
3228 p_document_type IN VARCHAR2,
3229 p_document_id IN NUMBER,
3230 p_document_version IN NUMBER := NULL,
3231
3232 x_has_terms OUT NOCOPY VARCHAR2,
3233 x_authoring_party_code OUT NOCOPY VARCHAR2,
3234 x_authoring_party OUT NOCOPY VARCHAR2,
3235 x_contract_source_code OUT NOCOPY VARCHAR2,
3236 x_contract_source OUT NOCOPY VARCHAR2,
3237 x_template_id OUT NOCOPY NUMBER,
3238 x_template_name OUT NOCOPY VARCHAR2,
3239 x_template_description OUT NOCOPY VARCHAR2,
3240 x_template_instruction OUT NOCOPY VARCHAR2,
3241 x_has_primary_doc OUT NOCOPY VARCHAR2,
3242 x_is_primary_doc_mergeable OUT NOCOPY VARCHAR2,
3243 x_primary_doc_file_id OUT NOCOPY VARCHAR2
3244 ) IS
3245
3246 l_api_version CONSTANT NUMBER := 1;
3247 l_api_name CONSTANT VARCHAR2(30) := 'Get_Contract_Details_All';
3248 l_tmpl_name OKC_TERMS_TEMPLATES_ALL.TEMPLATE_NAME%TYPE;
3249 l_template_desc OKC_TERMS_TEMPLATES_ALL.DESCRIPTION%TYPE;
3250 l_instruction OKC_TERMS_TEMPLATES_ALL.INSTRUCTION_TEXT%TYPE;
3251 l_template_id OKC_TEMPLATE_USAGES.TEMPLATE_ID%TYPE;
3252 l_document_id OKC_TEMPLATE_USAGES.DOCUMENT_ID%TYPE := NULL;
3253 l_authoring_party_code OKC_TEMPLATE_USAGES.AUTHORING_PARTY_CODE%TYPE;
3254 l_contract_source_code OKC_TEMPLATE_USAGES.CONTRACT_SOURCE_CODE%TYPE;
3255 l_generated_flag VARCHAR2(1) := '?';
3256 l_media_id FND_DOCUMENTS_TL.MEDIA_ID%TYPE := -1;
3257 l_authoring_party OKC_RESP_PARTIES_TL.ALTERNATE_NAME%TYPE;
3258 l_contract_source FND_LOOKUPS.MEANING%TYPE;
3259
3260 CURSOR terms_tmpl_csr IS
3261 SELECT tu.document_id,
3262 tu.authoring_party_code,
3263 tu.contract_source_code,
3264 tu.template_id,
3265 t.template_name,
3266 t.description,
3267 t.instruction_text,
3268 party.alternate_name authoring_party,
3269 src.meaning contract_source
3270 FROM OKC_TEMPLATE_USAGES tu,
3271 OKC_TERMS_TEMPLATES_ALL t,
3272 okc_resp_parties_vl party,
3273 okc_bus_doc_types_b doc,
3274 fnd_lookups src
3275 WHERE t.template_id(+) = tu.template_id
3276 AND tu.authoring_party_code = party.resp_party_code
3277 AND tu.document_type = doc.document_type
3278 AND doc.document_type_class = party.document_type_class
3279 AND NVL(doc.intent,'zzz') = NVL(party.intent,'zzz')
3280 AND src.lookup_type = 'OKC_CONTRACT_TERMS_SOURCES'
3281 AND src.lookup_code = tu.contract_source_code
3282 AND tu.document_type = p_document_type
3283 AND tu.document_id = p_document_id;
3284
3285 CURSOR contract_doc_csr IS
3286 SELECT tl.media_id ,
3287 docs.generated_flag
3288 FROM OKC_CONTRACT_DOCS docs, FND_ATTACHED_DOCUMENTS fnd,FND_DOCUMENTS_TL tl
3289 WHERE docs.primary_contract_doc_flag = 'Y'
3290 AND docs.business_document_version=-99
3291 AND docs.business_document_type = p_document_type
3292 AND docs.business_document_id = p_document_id
3293 AND docs.attached_document_id = fnd.attached_document_id
3294 AND fnd.document_id = tl.document_id
3295 AND tl.language = USERENV('LANG');
3296
3297 --Bug 4131467 Added cursor to fetch contract details from history table if document_version is passed
3298 CURSOR terms_tmpl_ver_csr IS
3299 SELECT tu.document_id,
3300 tu.authoring_party_code,
3301 tu.contract_source_code,
3302 tu.template_id,
3303 t.template_name,
3304 t.description,
3305 t.instruction_text,
3306 party.alternate_name authoring_party,
3307 src.meaning contract_source
3308 FROM OKC_TEMPLATE_USAGES_H tu,
3309 OKC_TERMS_TEMPLATES_ALL t,
3310 okc_resp_parties_vl party,
3311 okc_bus_doc_types_b doc,
3312 fnd_lookups src
3313 WHERE t.template_id(+) = tu.template_id
3314 AND tu.authoring_party_code = party.resp_party_code
3315 AND tu.document_type = doc.document_type
3316 AND doc.document_type_class = party.document_type_class
3317 AND NVL(doc.intent,'zzz') = NVL(party.intent,'zzz')
3318 AND src.lookup_type = 'OKC_CONTRACT_TERMS_SOURCES'
3319 AND src.lookup_code = tu.contract_source_code
3320 AND tu.document_type = p_document_type
3321 AND tu.document_id = p_document_id
3322 AND tu.major_version = p_document_version;
3323
3324 --Bug 4131467 Added cursor to fetch attachment details from history table if document_version is passed
3325 CURSOR contract_doc_ver_csr IS
3326 SELECT tl.media_id ,
3327 docs.generated_flag
3328 FROM OKC_CONTRACT_DOCS docs, FND_ATTACHED_DOCUMENTS fnd,FND_DOCUMENTS_TL tl
3329 WHERE docs.primary_contract_doc_flag = 'Y'
3330 AND docs.business_document_version = p_document_version
3331 AND docs.business_document_type = p_document_type
3332 AND docs.business_document_id = p_document_id
3333 AND docs.attached_document_id = fnd.attached_document_id
3334 AND fnd.document_id = tl.document_id
3335 AND tl.language = USERENV('LANG');
3336
3337 BEGIN
3338 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3339 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'15400: Entered Get_Contract_Details_All');
3340 END IF;
3341
3342 -- Standard call to check for call compatibility.
3343 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
3344 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3345 END IF;
3346 -- Initialize message list if p_init_msg_list is set to TRUE.
3347 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3348 FND_MSG_PUB.initialize;
3349 END IF;
3350 -- Initialize API return status to success
3351 x_return_status := G_RET_STS_SUCCESS;
3352
3353
3354 --Initialising values to false/notfound.
3355 x_primary_doc_file_id := -1;
3356 x_has_primary_doc := 'N';
3357 x_is_primary_doc_mergeable := 'N';
3358
3359 IF p_document_version is not NULL THEN
3360 --Doc version has been specified, fetch from history tables.
3361 OPEN terms_tmpl_ver_csr;
3362 FETCH terms_tmpl_ver_csr INTO l_document_id,l_authoring_party_code,
3363 l_contract_source_code,l_template_id,l_tmpl_name,l_template_desc,l_instruction,l_authoring_party,l_contract_source;
3364 IF terms_tmpl_ver_csr%NOTFOUND THEN
3365 --fallback to latest version if not found.
3366 OPEN terms_tmpl_csr;
3367 FETCH terms_tmpl_csr INTO l_document_id,l_authoring_party_code,
3368 l_contract_source_code,l_template_id,l_tmpl_name,l_template_desc,l_instruction,l_authoring_party,l_contract_source;
3369 CLOSE terms_tmpl_csr;
3370 END IF;
3371 CLOSE terms_tmpl_ver_csr;
3372 ELSE
3373 --Fetch data from latest version.
3374 OPEN terms_tmpl_csr;
3375 FETCH terms_tmpl_csr INTO l_document_id,l_authoring_party_code,
3376 l_contract_source_code,l_template_id,l_tmpl_name,l_template_desc,l_instruction,l_authoring_party,l_contract_source;
3377 CLOSE terms_tmpl_csr;
3378 END IF;
3379
3380 IF l_document_id IS NOT NULL THEN --template_usages record exists
3381 x_has_terms := 'Y';
3382 ELSE
3383 x_has_terms := 'N';
3384 END IF;
3385
3386 x_authoring_party_code := l_authoring_party_code;
3387 x_contract_source_code := l_contract_source_code;
3388 x_template_id := l_template_id;
3389
3390 IF x_has_terms = 'Y' THEN
3391 --OKC_CONTRACT_DOCS_GRP.Get_Primary_Terms_Doc_File_Id logic has been substituted inline to improve performance.
3392 IF p_document_version is not NULL THEN
3393 --Doc version has been specified, fetch from history tables.
3394 -- Fix for bug# 4282242. The following block of code was missing in previous checkin 115.42.11510.11 .
3395 OPEN contract_doc_ver_csr;
3396 FETCH contract_doc_ver_csr INTO x_primary_doc_file_id, l_generated_flag;
3397 IF contract_doc_ver_csr%NOTFOUND THEN
3398 --fallback to latest version if not found.
3399 OPEN contract_doc_csr;
3400 FETCH contract_doc_csr INTO x_primary_doc_file_id,l_generated_flag;
3401 CLOSE contract_doc_csr;
3402 END IF;
3403 CLOSE contract_doc_ver_csr;
3404 ELSE
3405 --Fetch data from latest version.
3406 OPEN contract_doc_csr;
3407 FETCH contract_doc_csr INTO x_primary_doc_file_id, l_generated_flag;
3408 CLOSE contract_doc_csr;
3409 END IF;
3410 -- End of fix for bug# 4282242.
3411
3412 IF x_primary_doc_file_id = -1 THEN --if row not found
3413 x_has_primary_doc := 'N';
3414 ELSE
3415 x_has_primary_doc := 'Y';
3416 END IF;
3417
3418 IF l_generated_flag = 'Y' THEN
3419 x_primary_doc_file_id := 0;
3420 x_is_primary_doc_mergeable := 'Y';
3421 END IF;
3422
3423 x_authoring_party := l_authoring_party;
3424 x_contract_source := l_contract_source;
3425 IF l_template_id IS NOT NULL THEN
3426 x_template_name := l_tmpl_name;
3427 x_template_description := l_template_desc;
3428 x_template_instruction := l_instruction;
3429 ELSE
3430 fnd_message.set_name('OKC','OKC_TERMS_TEMPLATE_NAME_NONE');
3431 x_template_name:= fnd_message.get;
3432 x_template_description := NULL;
3433 x_template_instruction := NULL;
3434 END IF;
3435 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3436 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'16200: Return success Get_Contract_Details');
3437 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'16300: x_authoring_party:'||x_authoring_party);
3438 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'16400: x_contract_source:'||x_contract_source);
3439 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'16500: x_template_name:'||x_template_name);
3440 END IF;
3441
3442 ELSE
3443 fnd_message.set_name('OKC','OKC_TERMS_AUTH_PARTY_NONE');
3444 x_authoring_party := fnd_message.get;
3445 fnd_message.set_name('OKC','OKC_TERMS_CONTRACT_SOURCE_NONE');
3446 x_contract_source := fnd_message.get;
3447 fnd_message.set_name('OKC','OKC_TERMS_TEMPLATE_NAME_NONE');
3448 x_template_name:= fnd_message.get;
3449 x_template_description := NULL;
3450 x_template_instruction := NULL;
3451 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3452 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'16600: Return Get_Contract_Details,no terms exist');
3453 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'16700: x_authoring_party:'||x_authoring_party);
3454 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'16800: x_contract_source:'||x_contract_source);
3455 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'16900: x_template_name:'||x_template_name);
3456 END IF;
3457 END IF;
3458
3459 -- Standard call to get message count and if count is 1, get message info.
3460 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3461 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3462 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'17000: Leaving Get_Contract_Details_All');
3463 END IF;
3464 EXCEPTION
3465 WHEN FND_API.G_EXC_ERROR THEN
3466
3467 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3468 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'17100: Leaving Get_Contract_Details_All : OKC_API.G_EXCEPTION_ERROR Exception');
3469 END IF;
3470 x_return_status := G_RET_STS_ERROR ;
3471 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3472
3473 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3474
3475 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3476 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'17200: Leaving Get_Contract_Details_All : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
3477 END IF;
3478 x_return_status := G_RET_STS_UNEXP_ERROR ;
3479 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3480
3481 WHEN OTHERS THEN
3482
3483 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3484 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'17300: Leaving Get_Contract_Details_All because of EXCEPTION: '||sqlerrm);
3485 END IF;
3486 x_return_status := G_RET_STS_UNEXP_ERROR ;
3487 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
3488 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
3489 END IF;
3490 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3491
3492 END Get_Contract_Details_All;
3493
3494 -- Returns AUTHORING_PARTY_CODE for the document
3495 -- 'E' - for error
3496 FUNCTION Get_Authoring_Party_Code(
3497 p_document_type IN VARCHAR2,
3498 p_document_id IN NUMBER
3499 ) RETURN VARCHAR2 IS
3500 l_api_name CONSTANT VARCHAR2(30) := 'Get_Authoring_Party_Code';
3501 CURSOR tmpl_usages_csr IS
3502 SELECT authoring_party_code
3503 FROM okc_template_usages
3504 WHERE document_type = p_document_type
3505 AND document_id = p_document_id;
3506
3507 l_authoring_party_code OKC_TEMPLATE_USAGES.AUTHORING_PARTY_CODE%TYPE;
3508
3509 BEGIN
3510 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3511 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1800: Entering Get_Authoring_Party_Code ');
3512 END IF;
3513 OPEN tmpl_usages_csr ;
3514 FETCH tmpl_usages_csr into l_authoring_party_code;
3515 CLOSE tmpl_usages_csr ;
3516
3517 RETURN l_authoring_party_code;
3518
3519 EXCEPTION
3520 WHEN OTHERS THEN
3521 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3522 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'2000: Leaving Get_Authoring_Party_Code of EXCEPTION: '||sqlerrm);
3523 END IF;
3524 RETURN 'E';
3525 END Get_Authoring_Party_Code;
3526
3527
3528 -- Returns CONTRACT_SOURCE_CODE for the document
3529 -- 'E' - for error.
3530 FUNCTION Get_Contract_Source_Code(
3531 p_document_type IN VARCHAR2,
3532 p_document_id IN NUMBER
3533 ) RETURN VARCHAR2 IS
3534 l_api_name CONSTANT VARCHAR2(30) := 'Get_Contract_Source_Code';
3535 CURSOR tmpl_usages_csr IS
3536 SELECT contract_source_code
3537 FROM okc_template_usages
3538 WHERE document_type = p_document_type
3539 AND document_id = p_document_id;
3540
3541 l_contract_source_code OKC_TEMPLATE_USAGES.CONTRACT_SOURCE_CODE%TYPE;
3542
3543 BEGIN
3544 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3545 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1800: Entering Get_Contract_Source_Code ');
3546 END IF;
3547 OPEN tmpl_usages_csr ;
3548 FETCH tmpl_usages_csr into l_contract_source_code;
3549 CLOSE tmpl_usages_csr ;
3550
3551 RETURN l_contract_source_code;
3552
3553 EXCEPTION
3554 WHEN OTHERS THEN
3555 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3556 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'2000: Leaving Get_Contract_Source_Code of EXCEPTION: '||sqlerrm);
3557 END IF;
3558 RETURN 'E';
3559 END Get_Contract_Source_Code;
3560
3561 --Returns 'Y' if the template is approved, within validity range, and is applicable to the given
3562 --document type and org id.
3563 --Else returns 'N'.
3564 FUNCTION Is_Terms_Template_Valid(
3565 p_api_version IN NUMBER,
3566 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3567
3568 x_return_status OUT NOCOPY VARCHAR2,
3569 x_msg_data OUT NOCOPY VARCHAR2,
3570 x_msg_count OUT NOCOPY NUMBER,
3571
3572 p_template_id IN NUMBER,
3573 p_doc_type IN VARCHAR2,
3574 p_org_id IN NUMBER,
3575 p_valid_date IN DATE DEFAULT SYSDATE
3576 ) RETURN VARCHAR2 IS
3577
3578 CURSOR terms_tmpl_csr IS
3579 SELECT 'Y'
3580 FROM OKC_TERMS_TEMPLATES_ALL tmpl,
3581 OKC_ALLOWED_TMPL_USAGES usg,
3582 OKC_BUS_DOC_TYPES_B doc
3583 WHERE tmpl.template_id = p_template_id
3584 AND doc.document_type = p_doc_type
3585 AND doc.intent = tmpl.intent
3586 AND usg.template_id = tmpl.template_id
3587 AND usg.document_type = p_doc_type
3588 AND tmpl.status_code = 'APPROVED'
3589 AND tmpl.org_id = p_org_id
3590 AND p_valid_date between tmpl.start_date and nvl(tmpl.end_date,p_valid_date);
3591
3592 l_api_version CONSTANT NUMBER := 1;
3593 l_api_name CONSTANT VARCHAR2(30) := 'Is_Terms_Template_Valid';
3594 l_result VARCHAR2(1) := 'N';
3595
3596 BEGIN
3597
3598 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3599 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'6900: Entered Is_Terms_Template_Valid');
3600 END IF;
3601
3602 -- Standard call to check for call compatibility.
3603 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
3604 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3605 END IF;
3606
3607 -- Initialize message list if p_init_msg_list is set to TRUE.
3608 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3609 FND_MSG_PUB.initialize;
3610 END IF;
3611
3612 x_return_status := G_RET_STS_SUCCESS;
3613
3614 OPEN terms_tmpl_csr;
3615 FETCH terms_tmpl_csr INTO l_result;
3616 CLOSE terms_tmpl_csr;
3617
3618 -- Standard call to get message count and if count is 1, get message info.
3619 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3620
3621 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3622 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7000: Result Is_Terms_Template_Valid? : ['||l_result||']');
3623 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7100: Leaving Is_Terms_Template_Valid');
3624 END IF;
3625
3626 IF l_result = 'Y' THEN
3627 RETURN 'Y';
3628 ELSE
3629 RETURN 'N';
3630 END IF;
3631
3632 EXCEPTION
3633 WHEN FND_API.G_EXC_ERROR THEN
3634
3635 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3636 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7200: Leaving Is_Terms_Template_Valid : OKC_API.G_EXCEPTION_ERROR Exception');
3637 END IF;
3638
3639 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3640 x_return_status := G_RET_STS_ERROR ;
3641
3642 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3643 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3644 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7300: Leaving Is_Terms_Template_Valid : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
3645 END IF;
3646
3647 x_return_status := G_RET_STS_UNEXP_ERROR ;
3648 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3649
3650 WHEN OTHERS THEN
3651
3652 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3653 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7400: Leaving Is_Terms_Template_Valid because of EXCEPTION:'||sqlerrm);
3654 END IF;
3655 IF terms_tmpl_csr%ISOPEN THEN
3656 CLOSE terms_tmpl_csr;
3657 END IF;
3658 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
3659 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
3660 END IF;
3661 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3662 x_return_status := G_RET_STS_UNEXP_ERROR ;
3663 END Is_Terms_Template_Valid;
3664
3665
3666 --Returns values used for defaulting Contract Terms Details for authoring_party,contract_source,
3667 --template_name,template_description in OM.
3668 PROCEDURE Get_Contract_Defaults(
3669 p_api_version IN NUMBER,
3670 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3671
3672 x_return_status OUT NOCOPY VARCHAR2,
3673 x_msg_data OUT NOCOPY VARCHAR2,
3674 x_msg_count OUT NOCOPY NUMBER,
3675
3676 p_template_id IN VARCHAR2,
3677 p_document_type IN VARCHAR2,
3678
3679 x_authoring_party OUT NOCOPY VARCHAR2,
3680 x_contract_source OUT NOCOPY VARCHAR2,
3681 x_template_name OUT NOCOPY VARCHAR2,
3682 x_template_description OUT NOCOPY VARCHAR2
3683 ) IS
3684
3685 CURSOR terms_tmpl_csr IS
3686 SELECT tmpl.template_name,
3687 tmpl.description,
3688 okc_util.decode_lookup('OKC_CONTRACT_TERMS_SOURCES','STRUCTURED'),
3689 party.alternate_name
3690 FROM okc_terms_templates_all tmpl,
3691 okc_resp_parties_vl party,
3692 okc_bus_doc_types_b doc
3693 WHERE tmpl.template_id = p_template_id
3694 and party.document_type_class = doc.document_type_class
3695 and party.intent = doc.intent
3696 and doc.document_type= p_document_type
3697 and party.resp_party_code = 'INTERNAL_ORG';
3698
3699 l_api_version CONSTANT NUMBER := 1;
3700 l_api_name CONSTANT VARCHAR2(30) := 'Get_Contract_Defaults';
3701
3702 BEGIN
3703
3704 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3705 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'6900: Entered Get_Contract_Defaults');
3706 END IF;
3707
3708 -- Standard call to check for call compatibility.
3709 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
3710 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3711 END IF;
3712
3713 -- Initialize message list if p_init_msg_list is set to TRUE.
3714 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3715 FND_MSG_PUB.initialize;
3716 END IF;
3717
3718 x_return_status := G_RET_STS_SUCCESS;
3719
3720 IF p_template_id IS NOT NULL THEN
3721 OPEN terms_tmpl_csr;
3722 FETCH terms_tmpl_csr INTO x_template_name,x_template_description,x_contract_source,x_authoring_party;
3723 IF terms_tmpl_csr%NOTFOUND THEN
3724 fnd_message.set_name('OKC','OKC_TERMS_AUTH_PARTY_NONE');
3725 x_authoring_party := fnd_message.get;
3726 fnd_message.set_name('OKC','OKC_TERMS_CONTRACT_SOURCE_NONE');
3727 x_contract_source := fnd_message.get;
3728 fnd_message.set_name('OKC','OKC_TERMS_TEMPLATE_NAME_NONE');
3729 x_template_name:= fnd_message.get;
3730 x_template_description := NULL;
3731 END IF;
3732 CLOSE terms_tmpl_csr;
3733
3734 ELSE
3735 fnd_message.set_name('OKC','OKC_TERMS_AUTH_PARTY_NONE');
3736 x_authoring_party := fnd_message.get;
3737 fnd_message.set_name('OKC','OKC_TERMS_CONTRACT_SOURCE_NONE');
3738 x_contract_source := fnd_message.get;
3739 fnd_message.set_name('OKC','OKC_TERMS_TEMPLATE_NAME_NONE');
3740 x_template_name:= fnd_message.get;
3741 x_template_description := NULL;
3742 END IF;
3743
3744 -- Standard call to get message count and if count is 1, get message info.
3745 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3746
3747 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3748 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7000: Result Get_Contract_Defaults? : ['||x_return_status||']');
3749 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7100: Leaving Get_Contract_Defaults');
3750 END IF;
3751
3752
3753 EXCEPTION
3754 WHEN FND_API.G_EXC_ERROR THEN
3755
3756 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3757 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7200: Leaving Get_Contract_Defaults : OKC_API.G_EXCEPTION_ERROR Exception');
3758 END IF;
3759
3760 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3761 x_return_status := G_RET_STS_ERROR ;
3762
3763 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3764 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3765 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7300: Leaving Get_Contract_Defaults : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
3766 END IF;
3767
3768 x_return_status := G_RET_STS_UNEXP_ERROR ;
3769 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3770
3771 WHEN OTHERS THEN
3772
3773 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3774 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7400: Leaving Get_Contract_Defaults because of EXCEPTION:'||sqlerrm);
3775 END IF;
3776 IF terms_tmpl_csr%ISOPEN THEN
3777 CLOSE terms_tmpl_csr;
3778 END IF;
3779 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
3780 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
3781 END IF;
3782 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3783 x_return_status := G_RET_STS_UNEXP_ERROR ;
3784 END Get_Contract_Defaults;
3785
3786 PROCEDURE Get_Default_Template(
3787 p_api_version IN NUMBER,
3788 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3789
3790 x_return_status OUT NOCOPY VARCHAR2,
3791 x_msg_data OUT NOCOPY VARCHAR2,
3792 x_msg_count OUT NOCOPY NUMBER,
3793
3794 p_document_type IN VARCHAR2,
3795 p_org_id IN NUMBER,
3796 p_valid_date IN DATE,
3797
3798 x_template_id OUT NOCOPY NUMBER,
3799 x_template_name OUT NOCOPY VARCHAR2,
3800 x_template_description OUT NOCOPY VARCHAR2) IS
3801
3802 CURSOR terms_tmpl_csr IS
3803 SELECT tmpl.template_id,
3804 tmpl.template_name,
3805 tmpl.description
3806 FROM OKC_TERMS_TEMPLATES_ALL tmpl,
3807 OKC_ALLOWED_TMPL_USAGES usg,
3808 OKC_BUS_DOC_TYPES_B doc
3809 WHERE doc.document_type = p_document_type
3810 AND doc.intent = tmpl.intent
3811 AND usg.template_id = tmpl.template_id
3812 AND usg.document_type = p_document_type
3813 AND usg.document_type = doc.document_type
3814 AND usg.default_yn = 'Y'
3815 AND tmpl.status_code = 'APPROVED'
3816 AND tmpl.org_id = p_org_id
3817 AND p_valid_date between tmpl.start_date and nvl(tmpl.end_date,p_valid_date);
3818
3819 l_api_version CONSTANT NUMBER := 1;
3820 l_api_name CONSTANT VARCHAR2(30) := 'Get_Default_Template';
3821
3822 BEGIN
3823
3824 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3825 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'6900: Entered Get_Default_Template');
3826 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'6910: p_document_type='||p_document_type);
3827 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'6920: p_org_id='||p_org_id);
3828 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'6930: p_valid_date='||p_valid_date);
3829 END IF;
3830
3831 -- Standard call to check for call compatibility.
3832 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
3833 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3834 END IF;
3835
3836 -- Initialize message list if p_init_msg_list is set to TRUE.
3837 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3838 FND_MSG_PUB.initialize;
3839 END IF;
3840
3841 x_return_status := G_RET_STS_SUCCESS;
3842
3843 OPEN terms_tmpl_csr;
3844 FETCH terms_tmpl_csr INTO x_template_id,x_template_name,x_template_description;
3845 CLOSE terms_tmpl_csr;
3846
3847 -- Standard call to get message count and if count is 1, get message info.
3848 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3849
3850 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3851 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7000: Result x_template_id : ['||x_template_id||']');
3852 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7010: Result x_template_name : ['||x_template_name||']');
3853 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7020: Result x_template_desription : ['||x_template_description||']');
3854 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7130: Leaving Get_Default_Template');
3855 END IF;
3856
3857
3858 EXCEPTION
3859 WHEN FND_API.G_EXC_ERROR THEN
3860
3861 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3862 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7200: Leaving Get_Default_Template : OKC_API.G_EXCEPTION_ERROR Exception');
3863 END IF;
3864 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3865 x_return_status := G_RET_STS_ERROR ;
3866
3867 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3868 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3869 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7300: Leaving Get_Default_Template : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
3870 END IF;
3871
3872 x_return_status := G_RET_STS_UNEXP_ERROR ;
3873 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3874
3875 WHEN OTHERS THEN
3876
3877 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3878 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7400: Leaving Get_Default_Template because of EXCEPTION:'||sqlerrm);
3879 END IF;
3880 IF terms_tmpl_csr%ISOPEN THEN
3881 CLOSE terms_tmpl_csr;
3882 END IF;
3883 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
3884 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
3885 END IF;
3886 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3887 x_return_status := G_RET_STS_UNEXP_ERROR ;
3888 END Get_Default_Template;
3889
3890 --Returns 'Y' or 'N' depending on wether the API is able to generate deviation report.
3891 FUNCTION Auto_Generate_Deviations(
3892 p_document_type IN VARCHAR2,
3893 p_document_id IN NUMBER
3894 ) RETURN VARCHAR2 IS
3895 l_api_name CONSTANT VARCHAR2(30) := 'Auto_Generate_Deviations';
3896 CURSOR tmpl_usages_csr IS
3897 SELECT usg.autogen_deviations_flag,types.application_id
3898 FROM OKC_TEMPLATE_USAGES usg, OKC_BUS_DOC_TYPES_B types
3899 WHERE usg.document_type = p_document_type
3900 AND usg.document_id = p_document_id
3901 AND types.document_type = usg.document_type;
3902
3903 l_autogen_deviations_flag OKC_TEMPLATE_USAGES.AUTOGEN_DEVIATIONS_FLAG%TYPE;
3904 l_application_id OKC_BUS_DOC_TYPES_B.APPLICATION_ID%TYPE;
3905
3906 BEGIN
3907 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3908 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1800: Entering Auto_Generate_Deviations ');
3909 END IF;
3910
3911
3912 OPEN tmpl_usages_csr ;
3913 FETCH tmpl_usages_csr into l_autogen_deviations_flag,l_application_id;
3914 CLOSE tmpl_usages_csr ;
3915
3916 IF l_autogen_deviations_flag IS NOT NULL THEN
3917 RETURN l_autogen_deviations_flag;
3918 ELSE
3919 RETURN nvl(fnd_profile.VALUE_SPECIFIC(name => 'OKC_RUN_DEVREP_ON_APPROVAL',application_id => l_application_id),'N');
3920 END IF;
3921
3922 EXCEPTION
3923 WHEN OTHERS THEN
3924 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3925 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'2000: Leaving Auto_Generate_Deviations of EXCEPTION: '||sqlerrm);
3926 END IF;
3927 RETURN 'E';
3928 END Auto_Generate_Deviations;
3929
3930 --Returns ID(s) of Abstract category Contract Attachments. ID's are comma seperated in case of multiple value, NULL if not present.
3931 FUNCTION Get_Deviations_File_Id(
3932 p_document_type IN VARCHAR2,
3933 p_document_id IN NUMBER
3934 ) RETURN VARCHAR2 IS
3935 l_api_name CONSTANT VARCHAR2(30) := 'Get_Deviations_File_Id';
3936
3937 CURSOR doc_details_csr IS
3938 select media_id from okc_contract_docs_details_vl
3939 where business_document_id = p_document_id
3940 and business_document_type = p_document_type
3941 and category_code = 'OKC_REPO_APPROVAL_ABSTRACT'
3942 and datatype_id = 6;
3943
3944 l_file_id VARCHAR2(2000):= NULL;
3945 l_media_id FND_DOCUMENTS_TL.MEDIA_ID%TYPE := -1;
3946
3947 BEGIN
3948 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3949 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1800: Entering Get_Deviations_File_Id ');
3950 END IF;
3951
3952 IF Okc_terms_util_grp.get_contract_source_code(p_document_type,p_document_id) <> 'ATTACHED' THEN
3953 OPEN doc_details_csr;
3954 LOOP
3955 FETCH doc_details_csr INTO l_media_id;
3956 IF doc_details_csr%NOTFOUND THEN
3957 exit;
3958 END IF;
3959 IF l_file_id IS NULL THEN
3960 l_file_id := l_media_id;
3961 ELSE
3962 l_file_id := l_file_id || ',' || l_media_id;
3963 END IF;
3964 END LOOP;
3965 CLOSE doc_details_csr;
3966 END IF;
3967
3968 RETURN l_file_id;
3969
3970 EXCEPTION
3971 WHEN OTHERS THEN
3972 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3973 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'2000: Leaving Get_Deviations_File_Id of EXCEPTION: '||sqlerrm);
3974 END IF;
3975 IF doc_details_csr%ISOPEN THEN
3976 CLOSE doc_details_csr;
3977 END IF;
3978
3979 RETURN NULL;
3980 END Get_Deviations_File_Id;
3981
3982
3983 PROCEDURE Has_Uploaded_Deviations_Doc(
3984 p_api_version IN NUMBER,
3985 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3986
3987 x_return_status OUT NOCOPY VARCHAR2,
3988 x_msg_data OUT NOCOPY VARCHAR2,
3989 x_msg_count OUT NOCOPY NUMBER,
3990
3991 p_document_type IN VARCHAR2,
3992 p_document_Id IN NUMBER,
3993 x_contract_source OUT NOCOPY VARCHAR2,
3994 x_has_deviation_report OUT NOCOPY VARCHAR2
3995 ) IS
3996
3997 l_api_version CONSTANT NUMBER := 1;
3998 l_api_name CONSTANT VARCHAR2(30) := 'Has_Uploaded_Deviations_Doc';
3999
4000 BEGIN
4001
4002 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4003 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'6900: Entered Has_Uploaded_Deviations_Doc');
4004 END IF;
4005
4006 -- Standard call to check for call compatibility.
4007 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
4008 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4009 END IF;
4010
4011 -- Initialize message list if p_init_msg_list is set to TRUE.
4012 IF FND_API.to_Boolean( p_init_msg_list ) THEN
4013 FND_MSG_PUB.initialize;
4014 END IF;
4015
4016 x_return_status := G_RET_STS_SUCCESS;
4017
4018 x_contract_source := Get_Contract_Source_Code(
4019 p_document_type => p_document_type,
4020 p_document_id => p_document_id
4021 );
4022 x_has_deviation_report := OKC_TERMS_DEVIATIONS_PVT.has_deviation_report (
4023 p_document_type => p_document_type,
4024 p_document_id => p_document_id
4025 );
4026
4027
4028 -- Standard call to get message count and if count is 1, get message info.
4029 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
4030
4031 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4032 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7000: Result Has_Uploaded_Deviations_Doc? : ['||x_return_status||']');
4033 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'x_contract_source:'||x_contract_source);
4034 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'x_has_deviation_report:'||x_has_deviation_report);
4035 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7100: Leaving Has_Uploaded_Deviations_Doc');
4036 END IF;
4037
4038 EXCEPTION
4039 WHEN FND_API.G_EXC_ERROR THEN
4040
4041 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4042 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7200: Leaving Has_Uploaded_Deviations_Doc : OKC_API.G_EXCEPTION_ERROR Exception');
4043 END IF;
4044
4045 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
4046 x_return_status := G_RET_STS_ERROR ;
4047
4048 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4049 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4050 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7300: Leaving Has_Uploaded_Deviations_Doc : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
4051 END IF;
4052
4053 x_return_status := G_RET_STS_UNEXP_ERROR ;
4054 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
4055
4056 WHEN OTHERS THEN
4057
4058 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4059 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7400: Leaving Has_Uploaded_Deviations_Doc because of EXCEPTION:'||sqlerrm);
4060 END IF;
4061 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
4062 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
4063 END IF;
4064 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
4065 x_return_status := G_RET_STS_UNEXP_ERROR ;
4066 END Has_Uploaded_Deviations_Doc;
4067
4068 -- Returns 'Y' if Deviations report is implemented and enabled for the particular document, else 'N' .
4069 FUNCTION is_Deviations_enabled(
4070 p_document_type IN VARCHAR2,
4071 p_document_id IN NUMBER
4072 ) RETURN VARCHAR2 IS
4073
4074 l_api_name CONSTANT VARCHAR2(30) := 'is_Deviations_enabled';
4075 l_result VARCHAR2(1) := '?';
4076
4077 CURSOR deviations_lookup_csr IS
4078 SELECT 'Y'
4079 FROM FND_LOOKUP_VALUES
4080 WHERE lookup_code = 'REVIEW_DEV_REP'
4081 AND lookup_type = 'OKC_TERMS_AUTH_ACTIONS_VIEW';
4082
4083 BEGIN
4084 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4085 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1800: Entering is_Deviations_enabled ');
4086 END IF;
4087
4088 OPEN deviations_lookup_csr;
4089 FETCH deviations_lookup_csr INTO l_result;
4090 CLOSE deviations_lookup_csr;
4091
4092 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4093 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1900: Result is_Deviations_enabled :'||l_result);
4094 END IF;
4095
4096 IF l_result <> 'Y' THEN
4097 RETURN 'N';
4098 ELSE
4099 IF get_contract_source_code(p_document_type=>p_document_type,p_document_id=>p_document_id) = 'STRUCTURED' THEN
4100 RETURN 'Y';
4101 ELSE
4102 RETURN 'N';
4103 END IF;
4104 END IF;
4105
4106 EXCEPTION
4107 WHEN OTHERS THEN
4108 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4109 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'2000: Leaving is_Deviations_Implemented EXCEPTION: '||sqlerrm);
4110 END IF;
4111 IF deviations_lookup_csr%ISOPEN THEN
4112 CLOSE deviations_lookup_csr;
4113 END IF;
4114
4115 RETURN 'N';
4116 END is_Deviations_enabled;
4117
4118 FUNCTION Contract_Terms_Amended(
4119 p_api_version IN NUMBER,
4120 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4121
4122 x_return_status OUT NOCOPY VARCHAR2,
4123 x_msg_data OUT NOCOPY VARCHAR2,
4124 x_msg_count OUT NOCOPY NUMBER,
4125
4126 p_doc_type IN VARCHAR2,
4127 p_doc_id IN NUMBER
4128 ) RETURN VARCHAR2 IS
4129 l_api_version CONSTANT NUMBER := 1;
4130 l_api_name CONSTANT VARCHAR2(30) := 'Contract_Terms_Amended';
4131 l_dummy VARCHAR2(1) := '?';
4132 l_return_value VARCHAR2(100) := G_NO_ARTICLE_AMENDED;
4133 l_contract_source_code okc_template_usages.contract_source_code%TYPE;
4134 CURSOR contract_source_csr IS
4135 SELECT contract_source_code
4136 FROM okc_template_usages
4137 WHERE document_id = p_doc_id
4138 AND document_type = p_doc_type;
4139
4140 CURSOR primary_kdoc_csr IS
4141 SELECT 'Y'
4142 FROM okc_contract_docs
4143 WHERE business_document_id = p_doc_id
4144 AND business_document_type = p_doc_type
4145 AND business_document_version = -99
4146 AND effective_from_id = business_document_id
4147 AND effective_from_type = business_document_type
4148 AND effective_from_version = business_document_version
4149 AND primary_contract_doc_flag = 'Y'
4150 UNION ALL
4151 SELECT 'Y'
4152 FROM okc_contract_docs
4153 WHERE business_document_id = p_doc_id
4154 AND business_document_type = p_doc_type
4155 AND business_document_version = -99
4156 AND delete_flag = 'Y'
4157 AND primary_contract_doc_flag = 'Y';
4158 BEGIN
4159 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4160 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Entered Contract_Terms_Amended');
4161 END IF;
4162 -- Standard call to check for call compatibility.
4163 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
4164 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4165 END IF;
4166 -- Initialize message list if p_init_msg_list is set to TRUE.
4167 IF FND_API.to_Boolean( p_init_msg_list ) THEN
4168 FND_MSG_PUB.initialize;
4169 END IF;
4170 -- Initialize API return status to success
4171 x_return_status := G_RET_STS_SUCCESS;
4172
4173 OPEN contract_source_csr;
4174 FETCH contract_source_csr INTO l_contract_source_code;
4175 CLOSE contract_source_csr;
4176
4177 IF l_contract_source_code = G_ATTACHED_CONTRACT_SOURCE THEN
4178 OPEN primary_kdoc_csr;
4179 FETCH primary_kdoc_csr INTO l_dummy;
4180 CLOSE primary_kdoc_csr;
4181
4182 IF l_dummy='Y' THEN
4183 l_return_value := G_PRIMARY_KDOC_AMENDED;
4184 END IF;
4185 ELSIF l_contract_source_code = G_STRUCT_CONTRACT_SOURCE THEN
4186 l_return_value := Is_Article_Amended(
4187 p_api_version => p_api_version,
4188 p_init_msg_list => p_init_msg_list,
4189 x_return_status => x_return_status,
4190 x_msg_data => x_msg_data,
4191 x_msg_count => x_msg_count,
4192 p_doc_type => p_doc_type,
4193 p_doc_id => p_doc_id);
4194
4195 END IF;
4196
4197 -- Standard call to get message count and if count is 1, get message info.
4198
4199 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
4200
4201 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4202 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'200: Result Contract_Terms_Amended? : ['||l_return_value||']');
4203 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'300: Leaving Contract_Terms_Amended');
4204 END IF;
4205
4206 RETURN l_return_value ;
4207
4208 EXCEPTION
4209 WHEN FND_API.G_EXC_ERROR THEN
4210 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4211 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'400: Leaving Contract_Terms_Amended : OKC_API.G_EXCEPTION_ERROR Exception');
4212 END IF;
4213 x_return_status := G_RET_STS_ERROR ;
4214 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
4215 RETURN NULL ;
4216
4217 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4218
4219 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4220 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'500: Leaving Contract_Terms_Amended : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
4221 END IF;
4222 x_return_status := G_RET_STS_UNEXP_ERROR ;
4223 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
4224 RETURN NULL ;
4225
4226 WHEN OTHERS THEN
4227 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4228 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'600: Leaving Contract_Terms_Amended because of EXCEPTION: '||sqlerrm);
4229 END IF;
4230
4231 IF contract_source_csr%ISOPEN THEN
4232 CLOSE contract_source_csr;
4233 END IF;
4234
4235 IF primary_kdoc_csr%ISOPEN THEN
4236 CLOSE primary_kdoc_csr;
4237 END IF;
4238
4239 x_return_status := G_RET_STS_UNEXP_ERROR ;
4240
4241 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
4242 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
4243 END IF;
4244 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
4245 RETURN NULL ;
4246
4247 END Contract_Terms_Amended;
4248
4249 ----MLS for templates
4250 PROCEDURE get_translated_template(
4251 p_api_version IN NUMBER,
4252 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4253
4254 p_template_id IN NUMBER,
4255 p_language IN VARCHAR2,
4256 p_document_type IN VARCHAR2,
4257 p_validity_date IN DATE := SYSDATE,
4258
4259 x_return_status OUT NOCOPY VARCHAR2,
4260 x_msg_data OUT NOCOPY VARCHAR2,
4261 x_msg_count OUT NOCOPY NUMBER,
4262
4263 x_template_id OUT NOCOPY NUMBER
4264 ) IS
4265
4266 l_api_version CONSTANT NUMBER := 1;
4267 l_api_name CONSTANT VARCHAR2(30) := 'get_translated_template';
4268 l_translated_from_tmpl_id OKC_TERMS_TEMPLATES_ALL.translated_from_tmpl_id%TYPE;
4269 l_language OKC_TERMS_TEMPLATES_ALL.language%TYPE;
4270 l_org_id OKC_TERMS_TEMPLATES_ALL.org_id%TYPE;
4271 l_parent_template_id OKC_TERMS_TEMPLATES_ALL.parent_template_id%TYPE;
4272
4273 CURSOR l_tmpl_csr IS
4274 SELECT translated_from_tmpl_id, language, org_id, parent_template_id
4275 FROM okc_terms_templates_all
4276 WHERE template_id = p_template_id;
4277
4278 CURSOR l_translated_csr( l_tmpl_id in number,l_org_id in number) IS
4279 SELECT template_id
4280 FROM okc_terms_templates_all
4281 WHERE language = p_language
4282 AND org_id = l_org_id
4283 AND template_id = l_tmpl_id
4284 UNION ALL
4285 SELECT template_id
4286 FROM okc_terms_templates_all
4287 WHERE language = p_language
4288 AND org_id = l_org_id
4289 AND translated_from_tmpl_id = l_tmpl_id;
4290
4291
4292 BEGIN
4293
4294 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4295 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'6900: Entered get_translated_template');
4296 END IF;
4297
4298 -- Standard call to check for call compatibility.
4299 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
4300 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4301 END IF;
4302
4303 -- Initialize message list if p_init_msg_list is set to TRUE.
4304 IF FND_API.to_Boolean( p_init_msg_list ) THEN
4305 FND_MSG_PUB.initialize;
4306 END IF;
4307
4308 x_return_status := G_RET_STS_SUCCESS;
4309
4310
4311 OPEN l_tmpl_csr;
4312 FETCH l_tmpl_csr INTO l_translated_from_tmpl_id, l_language, l_org_id, l_parent_template_id;
4313 IF l_tmpl_csr%NOTFOUND THEN
4314 CLOSE l_tmpl_csr;
4315 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4316 END IF;
4317 CLOSE l_tmpl_csr;
4318
4319 IF l_language = p_language THEN
4320 x_template_id := p_template_id; --Input template itself is in the required language.
4321 RETURN;
4322 END IF;
4323
4324
4325 IF l_translated_from_tmpl_id is null THEN --p_template_id is a parent template.
4326
4327 OPEN l_translated_csr(p_template_id, l_org_id);
4328 FETCH l_translated_csr INTO x_template_id; --Fetch translated template.
4329
4330 ELSE --p_template_id is a translated template
4331
4332 OPEN l_translated_csr(l_translated_from_tmpl_id, l_org_id);
4333 FETCH l_translated_csr INTO x_template_id; --Fetch translated template.
4334
4335 END IF;
4336
4337 IF l_translated_csr%FOUND THEN
4338 IF ( OKC_TERMS_UTIL_GRP.Is_Terms_Template_Valid(
4339 p_api_version => 1.0,
4340 p_template_id => x_template_id,
4341 p_doc_type => p_document_type,
4342 p_org_id => l_org_id,
4343 p_valid_date => p_validity_date,
4344 x_return_status => x_return_status,
4345 x_msg_data => x_msg_data,
4346 x_msg_count => x_msg_count ) <> 'Y' ) THEN
4347
4348 x_template_id := p_template_id; --Template x_template_id status is invalid.
4349 END IF;
4350 ELSE
4351
4352 x_template_id := p_template_id; -- matching translated template not found.
4353 END IF;
4354
4355 CLOSE l_translated_csr;
4356
4357 -- Standard call to get message count and if count is 1, get message info.
4358 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
4359
4360 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4361 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7300: Leaving get_translated_template');
4362 END IF;
4363
4364 EXCEPTION
4365 WHEN FND_API.G_EXC_ERROR THEN
4366 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4367 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7400: Leaving Contract_Terms_Amended : OKC_API.G_EXCEPTION_ERROR Exception');
4368 END IF;
4369 x_return_status := G_RET_STS_ERROR ;
4370 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
4371 RETURN ;
4372
4373 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4374
4375 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4376 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7500: Leaving Contract_Terms_Amended : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
4377 END IF;
4378 x_return_status := G_RET_STS_UNEXP_ERROR ;
4379 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
4380 RETURN ;
4381 WHEN OTHERS THEN
4382
4383 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4384 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7700: Leaving get_translated_template because of EXCEPTION:'||sqlerrm);
4385 END IF;
4386 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
4387 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
4388 END IF;
4389 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
4390 x_return_status := G_RET_STS_UNEXP_ERROR ;
4391 END get_translated_template;
4392 --MLS for templates
4393
4394
4395 -- Concurrent Mod Changes Start
4396
4397 FUNCTION check_lock_exists ( p_object_type IN VARCHAR2, p_kart_sec_id IN VARCHAR2)
4398 RETURN VARCHAR2
4399 IS
4400
4401 CURSOR cur_art_details (cp_kart_sec_id IN NUMBER)
4402 IS
4403 SELECT orig_system_reference_code, orig_system_reference_id1, document_type,document_id
4404 From okc_k_articles_b
4405 Where id= cp_kart_sec_id ;
4406 l_tgt_art_details_rec cur_art_details%ROWTYPE;
4407 l_src_art_details_rec cur_art_details%ROWTYPE;
4408
4409
4410 CURSOR cur_sec_details (cp_kart_sec_id IN NUMBER)
4411 IS
4412 SELECT orig_system_reference_code, orig_system_reference_id1, document_type,document_id
4413 From okc_sections_b
4414 Where id= cp_kart_sec_id ;
4415 l_tgt_sec_details_rec cur_sec_details%ROWTYPE;
4416 l_src_sec_details_rec cur_sec_details%ROWTYPE;
4417
4418 CURSOR cur_lock_exists (cp_entity_name VARCHAR2,cp_entity_pk1 VARCHAR2, cp_lock_by_doc_type VARCHAR2,cp_lock_by_doc_id VARCHAR2)
4419 IS
4420 SELECT 'Y'
4421 FROM okc_k_entity_locks
4422 WHERE entity_name= cp_entity_name
4423 AND entity_pk1= cp_entity_pk1
4424 AND lock_by_document_type <> cp_lock_by_doc_type
4425 AND lock_by_document_id <> cp_lock_by_doc_id;
4426
4427
4428 l_lock_exists_flag VARCHAR2(1):= NULL;
4429
4430
4431 BEGIN
4432 IF p_object_type = 'ARTICLE' THEN
4433 OPEN cur_art_details(p_kart_sec_id);
4434 FETCH cur_art_details INTO l_tgt_art_details_rec;
4435 CLOSE cur_art_details;
4436 IF Nvl(l_tgt_art_details_rec.orig_system_reference_code,'?') = 'COPY' THEN
4437 OPEN cur_art_details(l_tgt_art_details_rec.orig_system_reference_id1);
4438 FETCH cur_art_details INTO l_src_art_details_rec;
4439 CLOSE cur_art_details;
4440 -- Base document and the current document or not of same type then check for locks.
4441 IF l_src_art_details_rec.document_type <> l_tgt_art_details_rec.document_type
4442 THEN
4443 OPEN cur_lock_exists('CLAUSE',l_tgt_art_details_rec.orig_system_reference_id1,l_tgt_art_details_rec.document_type,l_tgt_art_details_rec.document_id);
4444 FETCH cur_lock_exists INTO l_lock_exists_flag;
4445 CLOSE cur_lock_exists;
4446 RETURN Nvl(l_lock_exists_flag,'N');
4447 END IF;
4448 END IF;
4449 END IF;
4450
4451 IF p_object_type = 'SECTION' THEN
4452 OPEN cur_sec_details(p_kart_sec_id);
4453 FETCH cur_sec_details INTO l_tgt_sec_details_rec;
4454 CLOSE cur_sec_details;
4455 IF Nvl(l_tgt_sec_details_rec.orig_system_reference_code,'?') = 'COPY' THEN
4456 OPEN cur_sec_details(l_tgt_sec_details_rec.orig_system_reference_id1);
4457 FETCH cur_sec_details INTO l_src_sec_details_rec;
4458 CLOSE cur_sec_details;
4459 -- Base document and the current document or not of same type then check for locks.
4460 IF l_src_sec_details_rec.document_type <> l_tgt_sec_details_rec.document_type
4461 THEN
4462 OPEN cur_lock_exists('SECTION',l_tgt_art_details_rec.orig_system_reference_id1,l_tgt_art_details_rec.document_type,l_tgt_art_details_rec.document_id);
4463 FETCH cur_lock_exists INTO l_lock_exists_flag;
4464 CLOSE cur_lock_exists;
4465 RETURN Nvl(l_lock_exists_flag,'N');
4466 END IF;
4467 END IF;
4468 END IF;
4469 RETURN 'N';
4470 END check_lock_exists;
4471
4472 FUNCTION enable_update(
4473 p_object_type IN VARCHAR2,
4474 p_document_type IN VARCHAR2,
4475 p_standard_yn IN VARCHAR2,
4476 p_kart_sec_id in NUMBER
4477 ) RETURN VARCHAR2
4478 IS
4479
4480 l_lock_exists VARCHAR2(1);
4481 BEGIN
4482
4483 IF (p_object_type = 'SECTION' OR p_object_type = 'ARTICLE') THEN
4484 l_lock_exists := check_lock_exists(p_object_type,To_Char(p_kart_sec_id));
4485 IF l_lock_exists = 'Y' THEN
4486 RETURN 'OkcTermsStructDtlsUpdateDisabled' ;
4487 END IF;
4488 END IF;
4489
4490 IF (p_object_type <> 'SECTION' AND p_object_type <> 'ARTICLE') THEN
4491 -- top most document node , always disable
4492 RETURN 'OkcTermsStructDtlsUpdateDisabled' ;
4493 ELSIF p_object_type = 'SECTION' THEN
4494 -- update always enabled for Sections
4495 RETURN 'OkcTermsStructDtlsUpdateEnabled' ;
4496 -- Article Cases
4497 ELSIF p_document_type = 'TEMPLATE' THEN
4498 -- always disable for template as the logic is based on template status and is in the controller code
4499 RETURN 'OkcTermsStructDtlsUpdateDisabled';
4500 ELSIF NVL(p_standard_yn,'N') = 'Y' THEN
4501 -- update always enabled for standard articles
4502 RETURN 'OkcTermsStructDtlsUpdateEnabled' ;
4503 -- non std articles
4504 ELSIF fnd_function.test('OKC_TERMS_AUTHOR_NON_STD','N') THEN
4505 -- user has access to fn and doc not template
4506 RETURN 'OkcTermsStructDtlsUpdateEnabled' ;
4507 ELSE
4508 -- user does NOT have access to function OKC_TERMS_AUTHOR_NON_STD
4509 RETURN 'OkcTermsStructDtlsUpdateDisabled' ;
4510 END IF;
4511 END enable_update;
4512
4513
4514 FUNCTION enable_delete(
4515 p_object_type IN VARCHAR2,
4516 p_mandatory_yn IN VARCHAR2,
4517 p_standard_yn IN VARCHAR2,
4518 p_document_type IN VARCHAR2 := NULL,
4519 p_kart_sec_id in number
4520 ) RETURN VARCHAR2
4521 IS
4522
4523 CURSOR cur_art_details
4524 IS
4525 SELECT orig_system_reference_code, orig_system_reference_id1
4526 From okc_k_articles_b
4527 Where id= p_kart_sec_id ;
4528 l_art_details_rec cur_art_details%ROWTYPE;
4529 l_lock_exists VARCHAR2(1);
4530 BEGIN
4531
4532 IF (p_object_type = 'SECTION' OR p_object_type = 'ARTICLE') THEN
4533 l_lock_exists := check_lock_exists(p_object_type,To_Char(p_kart_sec_id));
4534 IF l_lock_exists = 'Y' THEN
4535 RETURN 'OkcTermsStructDtlsRemoveDisabled' ;
4536 END IF;
4537 END IF;
4538
4539 IF (p_object_type <> 'SECTION' AND p_object_type <> 'ARTICLE') THEN
4540 -- topmost document node, so disable delete
4541 RETURN 'OkcTermsStructDtlsRemoveDisabled';
4542 ELSIF p_object_type = 'SECTION' THEN
4543 -- Delete always enabled for sections as the API validates for mandatory articles check
4544 RETURN 'OkcTermsStructDtlsRemoveEnabled' ;
4545 -- ARTICLES LOGIC
4546 -- Case 1: MANDATORY ARTICLES
4547 ELSIF NVL(p_mandatory_yn,'N') = 'Y' THEN
4548 -- article is mandatory
4549 --Bug 4123003 If doc_type is template, delete button should be enabled
4550 IF p_document_type = 'TEMPLATE' THEN
4551 RETURN 'OkcTermsStructDtlsRemoveEnabled';
4552 ELSE
4553 IF (fnd_function.test('OKC_TERMS_AUTHOR_NON_STD','N') AND
4554 fnd_function.test('OKC_TERMS_AUTHOR_SUPERUSER','N')) THEN
4555 -- user has override controls, allow delete mandatory
4556 RETURN 'OkcTermsStructDtlsRemoveEnabled';
4557 ELSE
4558 RETURN 'OkcTermsStructDtlsRemoveDisabled';
4559 END IF;
4560 END IF;
4561 -- Case 2: STANDARD ARTICLES (non-mandatory)
4562 ELSIF NVL(p_standard_yn,'N') = 'Y' THEN
4563 -- for standard articles delete is always allowed
4564 RETURN 'OkcTermsStructDtlsRemoveEnabled' ;
4565 -- Case 3: NON-STANDARD ARTICLES (non-mandatory)
4566 ELSIF fnd_function.test('OKC_TERMS_AUTHOR_NON_STD','N') THEN
4567 -- for non-std articles check for function security
4568 -- user has access , so check allow delete for non-std articles
4569 RETURN 'OkcTermsStructDtlsRemoveEnabled' ;
4570 ELSE
4571 -- user does not have access to delete non-std articles
4572 RETURN 'OkcTermsStructDtlsRemoveDisabled';
4573 END IF;
4574
4575
4576
4577 OPEN cur_art_details;
4578 FETCH cur_art_details INTO l_art_details_rec;
4579 CLOSE cur_art_details;
4580
4581 IF Nvl(l_art_details_rec.orig_system_reference_code,'?')='COPY' THEN
4582 NULL;
4583 ELSE
4584 RETURN enable_delete(
4585 p_object_type ,
4586 p_mandatory_yn ,
4587 p_standard_yn ,
4588 p_document_type );
4589 END IF;
4590 END enable_delete;
4591
4592 -- Concurrent Mod Changes End
4593
4594 FUNCTION enable_delete(
4595 p_object_type IN VARCHAR2,
4596 p_mandatory_yn IN VARCHAR2,
4597 p_standard_yn IN VARCHAR2,
4598 p_document_type IN VARCHAR2 := NULL ,
4599 p_kart_sec_id in NUMBER,
4600 p_lockingEnabledYn IN VARCHAR2
4601 ) RETURN VARCHAR2
4602 IS
4603 BEGIN
4604 RETURN enable_delete(
4605 p_object_type ,
4606 p_mandatory_yn ,
4607 p_standard_yn ,
4608 p_document_type );
4609 END enable_delete;
4610
4611 FUNCTION enable_update(
4612 p_object_type IN VARCHAR2,
4613 p_document_type IN VARCHAR2,
4614 p_standard_yn IN VARCHAR2,
4615 p_kart_sec_id in NUMBER,
4616 p_lockingEnabledYn IN VARCHAR2
4617 ) RETURN VARCHAR2
4618 IS
4619 BEGIN
4620 RETURN enable_update(
4621 p_object_type ,
4622 p_document_type ,
4623 p_standard_yn
4624 );
4625
4626 END enable_update;
4627
4628
4629 --PROCEDURE
4630
4631 FUNCTION Has_deliverables(
4632 p_document_type IN VARCHAR2,
4633 p_document_id IN NUMBER,
4634 p_doc_version IN NUMBER DEFAULT NULL
4635 ) RETURN VARCHAR2
4636 IS
4637 CURSOR cur_get_del
4638 IS
4639 SELECT 'Y'
4640 FROM okc_deliverables
4641 WHERE business_document_type = p_document_type
4642 AND business_document_id = p_document_id
4643 AND business_document_version=Nvl(p_doc_version,-99);
4644
4645
4646 l_value VARCHAR2(1);
4647
4648 BEGIN
4649 OPEN cur_get_del;
4650 FETCH cur_get_del INTO l_value;
4651 IF (cur_get_del%NOTFOUND) THEN
4652 CLOSE cur_get_del;
4653 RETURN 'N';
4654 END IF;
4655 CLOSE cur_get_del;
4656
4657 Return 'Y';
4658
4659 END Has_deliverables;
4660
4661
4662 END OKC_TERMS_UTIL_GRP;