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