[Home] [Help]
PACKAGE BODY: APPS.OKC_TERMS_QA_GRP
Source
1 PACKAGE BODY OKC_TERMS_QA_GRP AS
2 /* $Header: OKCGDQAB.pls 120.5.12020000.3 2013/04/19 13:57:15 serukull ship $ */
3
4
5 ---------------------------------------------------------------------------
6 -- GLOBAL MESSAGE CONSTANTS
7 ---------------------------------------------------------------------------
8 G_FND_APP CONSTANT VARCHAR2(200) := OKC_API.G_FND_APP;
9 G_UNABLE_TO_RESERVE_REC CONSTANT VARCHAR2(200) := OKC_API.G_UNABLE_TO_RESERVE_REC;
10 G_RECORD_DELETED CONSTANT VARCHAR2(200) := OKC_API.G_RECORD_DELETED;
11 G_RECORD_CHANGED CONSTANT VARCHAR2(200) := OKC_API.G_RECORD_CHANGED;
12 G_RECORD_LOGICALLY_DELETED CONSTANT VARCHAR2(200) := OKC_API.G_RECORD_LOGICALLY_DELETED;
13 G_REQUIRED_VALUE CONSTANT VARCHAR2(200) := OKC_API.G_REQUIRED_VALUE;
14 G_INVALID_VALUE CONSTANT VARCHAR2(200) := OKC_API.G_INVALID_VALUE;
15 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_COL_NAME_TOKEN;
16 G_PARENT_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_PARENT_TABLE_TOKEN;
17 G_CHILD_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_CHILD_TABLE_TOKEN;
18 ---------------------------------------------------------------------------
19 -- GLOBAL CONSTANTS
20 ---------------------------------------------------------------------------
21 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKC_TERMS_QA_GRP';
22 G_MODULE CONSTANT VARCHAR2(200) := 'okc.plsql.'||G_PKG_NAME||'.';
23 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
24 ------------------------------------------------------------------------------
25 -- GLOBAL CONSTANTS
26 ------------------------------------------------------------------------------
27 G_FALSE CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
28 G_TRUE CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
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_QA_SUCCESS CONSTANT VARCHAR2(200) := 'OKC_CONTRACTS_QA_SUCCESS';
35 G_UNEXPECTED_ERROR CONSTANT varchar2(200) := 'OKC_UNEXPECTED_ERROR';
36 G_SQLERRM_TOKEN CONSTANT varchar2(200) := 'ERROR_MESSAGE';
37 G_SQLCODE_TOKEN CONSTANT varchar2(200) := 'ERROR_CODE';
38
39 G_QA_LOOKUP CONSTANT VARCHAR2(30) := 'OKC_TERM_QA_LIST';
40 G_TMPL_QA_TYPE CONSTANT VARCHAR2(30) := 'TEMPLATE';
41 G_TMPL_DOC_TYPE CONSTANT VARCHAR2(30) := OKC_TERMS_UTIL_GRP.G_TMPL_DOC_TYPE;
42
43 ---------------------------------------------------------------------------
44 -- Procedure QA_Doc version 1
45 ---------------------------------------------------------------------------
46 /* version 1, logs valiation messages in OKC_QA_ERRORS_T table
47 returns x_sequence_id as out parameter
48 11.5.10+ : Modified to accept addtional in parameter p_validation_level
49 */
50 PROCEDURE qa_doc (
51 p_api_version IN NUMBER ,
52 p_init_msg_list IN VARCHAR2 ,
53 x_return_status OUT NOCOPY VARCHAR2 ,
54 x_msg_data OUT NOCOPY VARCHAR2 ,
55 x_msg_count OUT NOCOPY NUMBER ,
56
57 p_qa_mode IN VARCHAR2 ,
58 p_doc_type IN VARCHAR2 ,
59 p_doc_id IN NUMBER ,
60
61 x_sequence_id OUT NOCOPY NUMBER ,
62 x_qa_return_status OUT NOCOPY VARCHAR2 ,
63 p_qa_terms_only IN VARCHAR2 ,
64 p_validation_level IN VARCHAR2,
65 p_commit IN VARCHAR2,
66 p_run_expert_flag IN VARCHAR2 DEFAULT 'Y')
67 IS
68
69 l_api_version CONSTANT NUMBER := 1;
70 l_api_name CONSTANT VARCHAR2(30) := 'QA_Doc';
71 l_qa_result_tbl qa_result_tbl_type;
72 l_bus_doc_date_events_tbl BUSDOCDATES_TBL_TYPE;
73 l_error_found Boolean := FALSE;
74 l_warning_found Boolean := FALSE;
75 l_contract_source OKC_TEMPLATE_USAGES.CONTRACT_SOURCE_CODE%TYPE;
76 l_now DATE;
77
78 l_qa_code OKC_QA_ERRORS_T.QA_CODE%TYPE;
79 l_severity OKC_QA_ERRORS_T.ERROR_SEVERITY%TYPE;
80 l_desc OKC_QA_ERRORS_T.PROBLEM_SHORT_DESC%TYPE;
81 l_perform_layout_tmpl_qa VARCHAR2(1);
82 l_template_id OKC_TEMPLATE_USAGES.TEMPLATE_ID%TYPE;
83
84 CURSOR l_get_qa_detail_csr IS
85 SELECT fnd.lookup_code qa_code,
86 fnd.meaning qa_name,
87 nvl(qa.severity_flag,G_QA_STS_WARNING) severity_flag ,
88 decode(fnd.enabled_flag,'N','N','Y',decode(qa.enable_qa_yn,'N','N','Y'),'Y') perform_qa
89 FROM FND_LOOKUPS FND, OKC_DOC_QA_LISTS QA
90 WHERE QA.DOCUMENT_TYPE(+)=p_doc_type
91 AND QA.QA_CODE(+) = FND.LOOKUP_CODE
92 AND FND.LOOKUP_CODE = 'CHECK_TERMS_EXIST'
93 AND Fnd.LOOKUP_TYPE=G_QA_LOOKUP;
94
95 CURSOR l_get_template_id IS
96 SELECT template_id
97 FROM OKC_TEMPLATE_USAGES
98 WHERE document_type = p_doc_type
99 AND document_id = p_doc_id;
100
101
102 CURSOR del_exists IS
103 SELECT 'Y'
104 FROM okc_deliverables
105 WHERE business_document_type= p_doc_type
106 AND business_document_id= p_doc_id
107 AND business_document_version = -99;
108
109 l_del_exists VARCHAR2 (1) :='N';
110
111 BEGIN
112 l_contract_source := 'NONE';
113 l_now := SYSDATE;
114 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
115 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Entered QA_Doc');
116 END IF;
117
118 -- Standard Start of API savepoint
119 SAVEPOINT g_QA_Doc;
120
121 -- Standard call to check for call compatibility.
122 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
123 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
124 END IF;
125
126 -- Initialize message list if p_init_msg_list is set to TRUE.
127 IF FND_API.to_Boolean( p_init_msg_list ) THEN
128 FND_MSG_PUB.initialize;
129 END IF;
130 -- Initialize API return status to success
131 x_return_status := G_RET_STS_SUCCESS;
132
133 IF p_doc_type <> G_TMPL_QA_TYPE THEN
134
135 l_contract_source := OKC_TERMS_UTIL_GRP.Get_Contract_Source_Code(
136 p_document_type => p_doc_type,
137 p_document_id => p_doc_id
138 );
139 IF (l_contract_source = 'E') THEN
140 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
141 END IF;
142 END IF;
143
144 --Added for 10+ word integration
145 IF l_contract_source = 'ATTACHED' THEN
146 --------------------------------------------
147 -- Contract is in attached document from
148 --------------------------------------------
149 --Added for 10+ word integration
150 OKC_CONTRACT_DOCS_GRP.qa_doc(
151 p_api_version => l_api_version,
152 x_return_status => x_return_status,
153 x_msg_count => x_msg_count,
154 x_msg_data => x_msg_data,
155
156 p_doc_type => p_doc_type,
157 p_doc_id => p_doc_id,
158
159 x_qa_result_tbl => l_qa_result_tbl,
160 x_qa_return_status => x_qa_return_status
161 );
162
163 --------------------------------------------
164 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
165 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
166 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
167 RAISE FND_API.G_EXC_ERROR ;
168 END IF;
169 --------------------------------------------
170
171 ELSIF (l_contract_source = 'STRUCTURED' OR p_doc_type = G_TMPL_QA_TYPE) THEN
172 --------------------------------------------
173 -- Contract is in structured terms format OR Document is Template
174 --------------------------------------------
175 IF p_qa_mode='AMEND' THEN
176 --------------------------------------------
177 -- Calling API to mark any article amended if system variable used in that API has been changed
178 --------------------------------------------
179 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
180 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'200: Call API OKC_TERMS_UTIL_GRP.Mark_Variable_Based_Amendment ');
181 END IF;
182
183 OKC_TERMS_UTIL_GRP.Mark_Variable_Based_Amendment(
184 p_api_version =>1,
185 p_init_msg_list => FND_API.G_FALSE,
186 p_commit => FND_API.G_TRUE,
187 p_doc_type => p_doc_type,
188 p_doc_id => p_doc_id,
189 x_return_status => x_return_status,
190 x_msg_count => x_msg_count,
191 x_msg_data => x_msg_data
192 );
193 --------------------------------------------
194 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
195 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
196 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
197 RAISE FND_API.G_EXC_ERROR ;
198 END IF;
199 --------------------------------------------
200 END IF;
201
202 --------------------------------------------
203 -- Call internal QA_Doc and put result into PLSQL table and save it in DB
204 --------------------------------------------
205 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
206 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'200: Call Private QA_Doc and put result into PLSQL table and save it in DB');
207 END IF;
208
209 OKC_TERMS_QA_PVT.QA_Doc(
210 x_return_status => x_return_status,
211
212 ---- p_save => 'Y',
213 p_qa_mode => p_qa_mode,
214 p_doc_type => p_doc_type,
215 p_doc_id => p_doc_id,
216 x_msg_count => x_msg_count,
217 x_msg_data => x_msg_data,
218
219 x_sequence_id => x_sequence_id,
220 x_qa_result_tbl => l_qa_result_tbl,
221 x_qa_return_status => x_qa_return_status,
222 p_run_expert_flag => p_run_expert_flag
223 );
224 --------------------------------------------
225 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
226 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
227 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
228 RAISE FND_API.G_EXC_ERROR ;
229 END IF;
230 --------------------------------------------
231
232 ELSE --l_contract_source = NULL
233 --Template has not been applied on the document, no qa needs to be done.
234 open del_exists;
235 FETCH del_exists INTO l_del_exists;
236 IF (del_exists%NOTFOUND) THEN
237 CLOSE del_exists;
238 x_qa_return_status := G_QA_STS_SUCCESS;
239 RETURN;
240 END if;
241 CLOSE del_exists;
242 END IF;
243
244
245 ----------------------------------------------------------------------
246 -- QA Check for Lock Contract - to be run for p_doc_type <> 'TEMPLATE'
247 ----------------------------------------------------------------------
248 IF p_doc_type<>G_TMPL_DOC_TYPE AND l_contract_source IS NOT NULL THEN
249 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
250 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2210: calling check_lock_contract');
251 END IF;
252 OKC_TERMS_QA_PVT.Check_lock_contract(
253 p_qa_mode => p_qa_mode,
254 p_doc_type => p_doc_type,
255 p_doc_id => p_doc_id,
256 x_qa_result_tbl => l_qa_result_tbl,
257 x_qa_return_status => x_qa_return_status,
258 x_return_status => x_return_status);
259
260 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
261 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
262 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
263 RAISE FND_API.G_EXC_ERROR ;
264 END IF;
265 END IF;
266
267 ----------------------------------------------------------------------
268 -- QA Check for Contract Admin - to be run for p_doc_type <> 'TEMPLATE'
269 ----------------------------------------------------------------------
270 IF p_doc_type<>G_TMPL_DOC_TYPE AND l_contract_source IS NOT NULL THEN
271 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
272 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2210: calling check_contract_admin');
273 END IF;
274 OKC_TERMS_QA_PVT.check_contract_admin(
275 p_qa_mode => p_qa_mode,
276 p_doc_type => p_doc_type,
277 p_doc_id => p_doc_id,
278 x_qa_result_tbl => l_qa_result_tbl,
279 x_qa_return_status => x_qa_return_status,
280 x_return_status => x_return_status
281 );
282 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
283 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
284 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
285 RAISE FND_API.G_EXC_ERROR ;
286 END IF;
287 END IF;
288
289
290
291 OPEN l_get_template_id;
292 FETCH l_get_template_id INTO l_template_id;
293 CLOSE l_get_template_id;
294
295 ------------------------------------------------------------
296 -- QA Check for deliverables, if p_qa_terms_only is 'N' and a template is instantiated
297 ------------------------------------------------------------
298
299 -- Deliverables can be created without applying a template.
300
301 IF ( p_qa_terms_only = 'N'
302 AND
303 (l_template_id IS NOT NULL OR l_del_exists = 'Y')
304 )
305 THEN
306
307
308 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
309 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'201: Call Private QA_Doc for deilverables and add result into PLSQL table and save it in DB');
310 END IF;
311
312 OKC_DELIVERABLE_PROCESS_PVT.validate_deliverable_for_qa (
313 p_api_version => 1,
314 p_init_msg_list => FND_API.G_FALSE,
315 p_doc_type => p_doc_type,
316 p_doc_id => p_doc_id,
317 p_mode => p_qa_mode,
318 p_bus_doc_date_events_tbl => l_bus_doc_date_events_tbl,
319 p_qa_result_tbl => l_qa_result_tbl,
320 x_msg_data => x_msg_data,
321 x_msg_count => x_msg_count,
322 x_return_status => x_return_status,
323 x_qa_return_status => x_qa_return_status);
324 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
325 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
326 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
327 RAISE FND_API.G_EXC_ERROR ;
328 END IF;
329
330 END IF;
331
332 --------------------------------------------
333 -- VALIDATIONS are done for Terms and Deliverables.
334 -- Now insert into Temp table.
335 --------------------------------------------
336 -- Save result from PLSQL table into DB table
337 --------------------------------------------
338 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
339 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2200: Save result from PLSQL table into DB table');
340 END IF;
341
342 x_qa_return_status := G_QA_STS_SUCCESS;
343
344 --Bug fix for bug#3290738
345 --After calling the validation APIs we need to find out about the x_qa_return_status
346 IF l_qa_result_tbl.COUNT > 0 THEN
347 FOR i IN l_qa_result_tbl.FIRST..l_qa_result_tbl.LAST LOOP
348 --Bug 3302652 takintoy
349 --Populate columns to be updated in okc_qa_errors_t
350 l_qa_result_tbl(i).error_record_type_name := okc_util.decode_lookup('OKC_ERROR_RECORD_TYPE',l_qa_result_tbl(i).error_record_type);
351 l_qa_result_tbl(i).error_severity_name := okc_util.decode_lookup('OKC_QA_SEVERITY',l_qa_result_tbl(i).error_severity);
352
353 IF l_qa_result_tbl(i).Error_severity = G_QA_STS_ERROR THEN
354 l_error_found := true;
355 END IF;
356 IF l_qa_result_tbl(i).Error_severity = G_QA_STS_WARNING THEN
357 l_warning_found := true;
358 END IF;
359
360 END LOOP;
361 IF l_error_found THEN
362 x_qa_return_status := G_QA_STS_ERROR;
363 ELSIF l_warning_found THEN
364 x_qa_return_status := G_QA_STS_WARNING;
365 END IF;
366 END IF;
367
368
369
370 OKC_TERMS_QA_PVT.log_qa_messages(
371 x_return_status => x_return_status,
372
373 p_qa_result_tbl => l_qa_result_tbl,
374 x_sequence_id => x_sequence_id
375 );
376 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
377 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
378 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
379 RAISE FND_API.G_EXC_ERROR ;
380 END IF;
381
382 -- do a commit only if p_commit = 'T' (default value)
383 IF FND_API.To_Boolean( p_commit ) THEN
384 COMMIT WORK;
385 END IF;
386
387 -- Standard call to get message count and if count is 1, get message info.
388 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
389 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
390 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'300: Leaving QA_Doc');
391 END IF;
392
393 EXCEPTION
394 WHEN FND_API.G_EXC_ERROR THEN
395 ROLLBACK TO g_QA_Doc;
396 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
397 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'400: Leaving QA_Doc : OKC_API.G_EXCEPTION_ERROR Exception');
398 END IF;
399 x_return_status := G_RET_STS_ERROR ;
400 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
401
402 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
403 ROLLBACK TO g_QA_Doc;
404 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
405 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'500: Leaving QA_Doc : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
406 END IF;
407 x_return_status := G_RET_STS_UNEXP_ERROR ;
408 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
409
410 WHEN OTHERS THEN
411 ROLLBACK TO g_QA_Doc;
412 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
413 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'600: Leaving QA_Doc because of EXCEPTION: '||sqlerrm);
414 END IF;
415
416 x_return_status := G_RET_STS_UNEXP_ERROR ;
417 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
418 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
419 END IF;
420 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
421 END qa_doc;
422
423 ---------------------------------------------------------------------------
424 -- Procedure QA_Doc version 2
425 ---------------------------------------------------------------------------
426 /* version 2, does not log valiation messages in OKC_QA_ERRORS_T table
427 returns x_qa_result_tbl as out parameter
428 11.5.10+: No modification
429 */
430 PROCEDURE QA_Doc (
431 p_api_version IN NUMBER ,
432 p_init_msg_list IN VARCHAR2 ,
433 x_return_status OUT NOCOPY VARCHAR2 ,
434 x_msg_data OUT NOCOPY VARCHAR2 ,
435 x_msg_count OUT NOCOPY NUMBER ,
436
437 p_qa_mode IN VARCHAR2 ,
438 p_doc_type IN VARCHAR2 ,
439 p_doc_id IN NUMBER ,
440
441 x_qa_result_tbl OUT NOCOPY qa_result_tbl_type,
442 x_qa_return_status OUT NOCOPY VARCHAR2,
443
444 p_qa_terms_only IN VARCHAR2 ,
445 p_run_expert_flag IN VARCHAR2 DEFAULT 'Y') -- Bug 5186245
446 IS
447
448 l_api_version CONSTANT NUMBER := 1;
449 l_api_name CONSTANT VARCHAR2(30) := 'QA_Doc';
450 l_sequence_id NUMBER;
451 l_bus_doc_date_events_tbl BUSDOCDATES_TBL_TYPE;
452 l_error_found Boolean := FALSE;
453 l_warning_found Boolean := FALSE;
454 l_contract_source OKC_TEMPLATE_USAGES.CONTRACT_SOURCE_CODE%TYPE;
455 l_template_id OKC_TEMPLATE_USAGES.TEMPLATE_ID%TYPE;
456 l_clm_doc_flag varchar2(1) := 'N';
457
458 CURSOR l_get_template_id IS
459 SELECT template_id
460 FROM OKC_TEMPLATE_USAGES
461 WHERE document_type = p_doc_type
462 AND document_id = p_doc_id;
463
464
465 CURSOR del_exists IS
466 SELECT 'Y'
467 FROM okc_deliverables
468 WHERE business_document_type= p_doc_type
469 AND business_document_id= p_doc_id
470 AND business_document_version = -99;
471
472 l_del_exists VARCHAR2 (1) :='N';
473
474
475 BEGIN
476 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
477 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'700: Entered QA_Doc');
478 END IF;
479
480 -- Standard Start of API savepoint
481 SAVEPOINT g_QA_Doc;
482
483 -- Standard call to check for call compatibility.
484 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
485 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
486 END IF;
487
488 -- Initialize message list if p_init_msg_list is set to TRUE.
489 IF FND_API.to_Boolean( p_init_msg_list ) THEN
490 FND_MSG_PUB.initialize;
491 END IF;
492 -- Initialize API return status to success
493 x_return_status := G_RET_STS_SUCCESS;
494
495 IF NVL(FND_PROFILE.VALUE('PO_CLM_INSTALLED'),'N') = 'Y' and
496 NVL(FND_PROFILE.VALUE('PO_CLM_ENABLED'),'N') = 'Y' THEN
497 l_clm_doc_flag := 'Y';
498 END IF;
499
500 --Added for 10+ word integration
501 IF p_doc_type <> G_TMPL_QA_TYPE THEN
502 l_contract_source := OKC_TERMS_UTIL_GRP.Get_Contract_Source_Code(
503 p_document_type => p_doc_type,
504 p_document_id => p_doc_id
505 );
506 IF (l_contract_source = 'E') THEN
507 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
508 END IF;
509 END IF;
510
511 IF l_contract_source = 'ATTACHED' THEN
512 --------------------------------------------
513 -- Contract is in attached document from
514 --------------------------------------------
515 --Added for 10+ word integration
516 OKC_CONTRACT_DOCS_GRP.qa_doc(
517 p_api_version => l_api_version,
518 x_return_status => x_return_status,
519 x_msg_count => x_msg_count,
520 x_msg_data => x_msg_data,
521
522 p_doc_type => p_doc_type,
523 p_doc_id => p_doc_id,
524
525 x_qa_result_tbl => x_qa_result_tbl,
526 x_qa_return_status => x_qa_return_status
527 );
528
529
530 --------------------------------------------
531 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
532 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
533 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
534 RAISE FND_API.G_EXC_ERROR ;
535 END IF;
536 --------------------------------------------
537
538 ELSIF (l_contract_source = 'STRUCTURED' OR p_doc_type = G_TMPL_QA_TYPE) THEN
539 --------------------------------------------
540 -- Contract is in structured terms format OR Document is Template
541 --------------------------------------------
542
543 IF p_qa_mode='AMEND' THEN
544 --------------------------------------------
545 -- Calling API to mark any article amended if system variable used in that API has been changed
546 --------------------------------------------
547 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
548 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'200: Call API OKC_TERMS_UTIL_GRP.Mark_Variable_Based_Amendment ');
549 END IF;
550
551 OKC_TERMS_UTIL_GRP.Mark_Variable_Based_Amendment(
552 p_api_version =>1,
553 p_init_msg_list => FND_API.G_FALSE,
554 p_commit => FND_API.G_TRUE,
555 p_doc_type => p_doc_type,
556 p_doc_id => p_doc_id,
557 x_return_status => x_return_status,
558 x_msg_count => x_msg_count,
559 x_msg_data => x_msg_data
560 );
561 --------------------------------------------
562 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
563 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
564 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
565 RAISE FND_API.G_EXC_ERROR ;
566 END IF;
567 --------------------------------------------
568 END IF;
569 --------------------------------------------
570 -- Call internal QA_Doc and put result into PLSQL table
571 --------------------------------------------
572 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
573 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'800: Call Private QA_Doc and put result into PLSQL table');
574 END IF;
575 OKC_TERMS_QA_PVT.QA_Doc(
576 x_return_status => x_return_status,
577
578 ---- p_save => 'N',
579 p_qa_mode => p_qa_mode,
580 p_doc_type => p_doc_type,
581 p_doc_id => p_doc_id,
582
583 x_msg_count => x_msg_count,
584 x_msg_data => x_msg_data,
585 x_sequence_id => l_sequence_id,
586 x_qa_result_tbl => x_qa_result_tbl,
587 x_qa_return_status => x_qa_return_status,
588 p_run_expert_flag => p_run_expert_flag
589 );
590 --------------------------------------------
591 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
592 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
593 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
594 RAISE FND_API.G_EXC_ERROR ;
595 END IF;
596 --------------------------------------------
597
598 ELSE --l_contract_source = NULL
599 --Template has not been applied on the document, no qa needs to be done.
600 open del_exists;
601 FETCH del_exists INTO l_del_exists;
602 IF (del_exists%NOTFOUND) THEN
603 CLOSE del_exists;
604 x_qa_return_status := G_QA_STS_SUCCESS;
605 RETURN;
606 END if;
607 CLOSE del_exists;
608 END IF;
609
610
611 -- Repository Enhancement (For Validate Action)
612 IF SubStr(p_doc_type,1,3) <> 'REP' THEN -- If the Document Type is not Repository Contract'
613
614
615 ----------------------------------------------------------------------
616 -- QA Check for Lock Contract - to be run for p_doc_type <> 'TEMPLATE'
617 ----------------------------------------------------------------------
618 IF p_doc_type<>G_TMPL_DOC_TYPE AND l_contract_source IS NOT NULL THEN
619 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
620 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2210: calling check_lock_contract');
621 END IF;
622 OKC_TERMS_QA_PVT.Check_lock_contract(
623 p_qa_mode => p_qa_mode,
624 p_doc_type => p_doc_type,
625 p_doc_id => p_doc_id,
626 x_qa_result_tbl => x_qa_result_tbl,
627 x_qa_return_status => x_qa_return_status,
628 x_return_status => x_return_status);
629
630 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
631 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
632 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
633 RAISE FND_API.G_EXC_ERROR ;
634 END IF;
635 END IF;
636
637 ----------------------------------------------------------------------
638 -- QA Check for Contract Admin - to be run for p_doc_type <> 'TEMPLATE'
639 ----------------------------------------------------------------------
640 IF p_doc_type<>G_TMPL_DOC_TYPE AND l_contract_source IS NOT NULL THEN
641 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
642 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2210: calling check_contract_admin');
643 END IF;
644
645 IF l_clm_doc_flag = 'N' THEN
646 OKC_TERMS_QA_PVT.check_contract_admin(
647 p_qa_mode => p_qa_mode,
648 p_doc_type => p_doc_type,
649 p_doc_id => p_doc_id,
650 x_qa_result_tbl => x_qa_result_tbl,
651 x_qa_return_status => x_qa_return_status,
652 x_return_status => x_return_status
653 );
654 END IF;
655 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
656 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
657 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
658 RAISE FND_API.G_EXC_ERROR ;
659 END IF;
660 END IF;
661 OPEN l_get_template_id;
662 FETCH l_get_template_id INTO l_template_id;
663 CLOSE l_get_template_id;
664 ------------------------------------------------------------
665 -- QA Check for deliverables, if p_qa_terms_only is 'N' and a template is instantiated
666 ------------------------------------------------------------
667
668 IF ( p_qa_terms_only = 'N'
669 AND (l_template_id IS NOT NULL OR l_del_exists='Y')
670 )
671 THEN
672
673
674 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
675 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'201: Call Private QA_Doc for deilverables and add result into PLSQL table and save it in DB');
676 END IF;
677
678 OKC_DELIVERABLE_PROCESS_PVT.validate_deliverable_for_qa (
679 p_api_version => 1,
680 p_init_msg_list => FND_API.G_FALSE,
681 p_doc_type => p_doc_type,
682 p_doc_id => p_doc_id,
683 p_mode => p_qa_mode,
684 p_bus_doc_date_events_tbl => l_bus_doc_date_events_tbl,
685 p_qa_result_tbl => x_qa_result_tbl,
686 x_msg_data => x_msg_data,
687 x_msg_count => x_msg_count,
688 x_return_status => x_return_status,
689 x_qa_return_status => x_qa_return_status);
690 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
691 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
692 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
693 RAISE FND_API.G_EXC_ERROR ;
694 END IF;
695
696 END IF;
697
698 x_qa_return_status := G_QA_STS_SUCCESS;
699 IF x_qa_result_tbl.COUNT > 0 THEN
700 FOR i in x_qa_result_tbl.FIRST..x_qa_result_tbl.LAST LOOP
701
702 x_qa_result_tbl(i).error_record_type_name := okc_util.decode_lookup('OKC_ERROR_RECORD_TYPE',x_qa_result_tbl(i).error_record_type);
703 x_qa_result_tbl(i).error_severity_name := okc_util.decode_lookup('OKC_QA_SEVERITY',x_qa_result_tbl(i).error_severity);
704
705 IF x_qa_result_tbl(i).Error_severity = G_QA_STS_ERROR THEN
706 l_error_found := true;
707 END IF;
708 IF x_qa_result_tbl(i).Error_severity = G_QA_STS_WARNING THEN
709 l_warning_found := true;
710 END IF;
711
712 END LOOP;
713 --fix for bug 3290738
714 IF l_error_found THEN
715 x_qa_return_status := G_QA_STS_ERROR;
716 ELSIF l_warning_found THEN
717 x_qa_return_status := G_QA_STS_WARNING;
718 END IF;
719
720 END IF;
721
722 -- Standard call to get message count and if count is 1, get message info.
723 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
724 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
725 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'900: Leaving QA_Doc');
726 END IF;
727 END IF; -- Repository Enhancement (For Validate Action)
728
729
730 EXCEPTION
731 WHEN FND_API.G_EXC_ERROR THEN
732 ROLLBACK TO g_QA_Doc;
733 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
734 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1000: Leaving QA_Doc : OKC_API.G_EXCEPTION_ERROR Exception');
735 END IF;
736 x_return_status := G_RET_STS_ERROR ;
737 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
738
739 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
740 ROLLBACK TO g_QA_Doc;
741 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
742 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1100: Leaving QA_Doc : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
743 END IF;
744 x_return_status := G_RET_STS_UNEXP_ERROR ;
745 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
746
747 WHEN OTHERS THEN
748 ROLLBACK TO g_QA_Doc;
749 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
750 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1200: Leaving QA_Doc because of EXCEPTION: '||sqlerrm);
751 END IF;
752
753 x_return_status := G_RET_STS_UNEXP_ERROR ;
754 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
755 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
756 END IF;
757 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
758 END qa_doc;
759
760 ---------------------------------------------------------------------------
761 -- Procedure Check_Terms calls QA_doc version 2
762 ---------------------------------------------------------------------------
763 PROCEDURE Check_Terms(
764 x_return_status OUT NOCOPY VARCHAR2,
765 p_chr_id IN NUMBER)
766 IS
767
768 l_msg_data FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE; --Fix for bug# 4019909
769 l_msg_count NUMBER;
770 l_api_version CONSTANT NUMBER := 1;
771 l_api_name CONSTANT VARCHAR2(30) := 'Check_Terms';
772 l_qa_result_tbl qa_result_tbl_type;
773 l_doc_type OKC_BUS_DOC_TYPES_B.DOCUMENT_TYPE%TYPE;
774 l_doc_id NUMBER;
775 l_qa_return_status VARCHAR2(50);
776
777 BEGIN
778
779 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
780 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'700: Entered '||l_api_name);
781 END IF;
782 x_return_status := G_RET_STS_SUCCESS;
783
784 OKC_TERMS_UTIL_GRP.Get_Contract_Document_Type_ID(
785 p_api_version => l_api_version,
786 x_return_status => x_return_status,
787 x_msg_data => l_msg_data,
788 x_msg_count => l_msg_count,
789 p_chr_id => p_chr_id,
790 x_doc_type => l_doc_type,
791 x_doc_id => l_doc_id
792 );
793 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
794 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
795 END IF;
796
797 QA_Doc(
798 p_api_version => l_api_version,
799 x_return_status => x_return_status,
800 x_msg_count => l_msg_count,
801 x_msg_data => l_msg_data,
802
803 p_qa_mode => G_NORMAL_QA,
804 p_doc_type => l_doc_type,
805 p_doc_id => l_doc_id,
806
807 x_qa_result_tbl => l_qa_result_tbl,
808 x_qa_return_status => l_qa_return_status
809 );
810 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
811 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
812 END IF;
813
814
815 IF l_qa_return_status = G_QA_STS_SUCCESS THEN
816 OKC_API.set_message(
817 p_app_name => G_APP_NAME,
818 p_msg_name => G_QA_SUCCESS);
819 ELSE
820 --Bug 4019909 Added following IF condition to prevent PL/SQL numeric error is l_qa_result_tbl is null.
821 IF l_qa_result_tbl.COUNT > 0 THEN
822 FOR i IN l_qa_result_tbl.first..l_qa_result_tbl.last LOOP
823 Okc_Qa_Check_Pvt.pub_qa_msg_tbl(i).data := l_qa_result_tbl(i).Problem_details||Fnd_Global.Newline||l_qa_result_tbl(i).Suggestion;
824 Okc_Qa_Check_Pvt.pub_qa_msg_tbl(i).error_status := l_qa_result_tbl(i).Error_severity;
825 END LOOP;
826 END IF;
827 END IF;
828 x_return_status := l_qa_return_status;
829
830 -- Standard call to get message count and if count is 1, get message info.
831 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
832 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'500: Leaving '||l_api_name);
833 END IF;
834
835 EXCEPTION
836 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
837 ROLLBACK TO g_QA_Doc;
838 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
839 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1100: Leaving '||l_api_name||' : G_EXCEPTION_UNEXPECTED_ERROR Exception');
840 END IF;
841 x_return_status := G_QA_STS_ERROR ;
842
843 WHEN OTHERS THEN
844 ROLLBACK TO g_QA_Doc;
845 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
846 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1200: Leaving '||l_api_name||' because of EXCEPTION: '||sqlerrm);
847 END IF;
848
849 x_return_status := G_QA_STS_ERROR ;
850 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
851 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
852 END IF;
853 END Check_Terms;
854
855 ---------------------------------------------------------------------------
856 -- Procedure QA_Doc version 3 - calls qa_doc version 2
857 ---------------------------------------------------------------------------
858 /* version 3, does not log valiation messages in OKC_QA_ERRORS_T table
859 returns x_qa_result_tbl as out parameter, takes in additional parameter
860 p_bus_doc_date_events_tbl
861 11.5.10+: No modification
862 */
863 Procedure QA_Doc (
864 p_api_version IN NUMBER ,
865 p_init_msg_list IN VARCHAR2 ,
866 x_return_status OUT NOCOPY VARCHAR2 ,
867 x_msg_data OUT NOCOPY VARCHAR2 ,
868 x_msg_count OUT NOCOPY NUMBER ,
869
870 p_qa_mode IN VARCHAR2 ,
871 p_doc_type IN VARCHAR2 ,
872 p_doc_id IN NUMBER ,
873
874 x_qa_result_tbl OUT NOCOPY qa_result_tbl_type,
875 x_qa_return_status OUT NOCOPY VARCHAR2,
876
877 p_bus_doc_date_events_tbl IN BUSDOCDATES_TBL_TYPE,
878 p_run_expert_flag IN VARCHAR2 DEFAULT 'Y')
879 IS
880
881 l_api_version CONSTANT NUMBER := 1;
882 l_api_name CONSTANT VARCHAR2(30) := 'QA_Doc';
883 l_qa_result_tbl qa_result_tbl_type;
884 l_error_found Boolean := FALSE;
885 l_warning_found Boolean := FALSE;
886
887 BEGIN
888
889 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
890 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Entered QA_Doc - OVERLOADED ONE');
891 END IF;
892
893 -- Standard Start of API savepoint
894 SAVEPOINT g_QA_Doc;
895
896 -- Standard call to check for call compatibility.
897 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
898 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
899 END IF;
900
901 -- Initialize message list if p_init_msg_list is set to TRUE.
902 IF FND_API.to_Boolean( p_init_msg_list ) THEN
903 FND_MSG_PUB.initialize;
904 END IF;
905
906 -- Initialize API return status to success
907 x_return_status := G_RET_STS_SUCCESS;
908
909
910 --------------------------------------------
911 -- Call internal QA_Doc and put result into PLSQL table
912 --------------------------------------------
913 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
914 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'800: Call Private QA_Doc and put result into PLSQL table');
915 END IF;
916
917 QA_Doc(
918 p_api_version => l_api_version,
919 x_return_status => x_return_status,
920 x_msg_count => x_msg_count,
921 x_msg_data => x_msg_data,
922
923 p_qa_mode => p_qa_mode,
924 p_doc_type => p_doc_type,
925 p_doc_id => p_doc_id,
926
927 x_qa_result_tbl => x_qa_result_tbl,
928 x_qa_return_status => x_qa_return_status,
929
930 p_qa_terms_only => 'Y',
931 p_run_expert_flag => p_run_expert_flag
932 );
933 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
934 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
935 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
936 RAISE FND_API.G_EXC_ERROR ;
937 END IF;
938
939 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
940 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'201: Call Private QA_Doc for deilverables and add result into PLSQL table and save it in DB');
941 END IF;
942
943 OKC_DELIVERABLE_PROCESS_PVT.validate_deliverable_for_qa (
944 p_api_version => 1,
945 p_init_msg_list => FND_API.G_FALSE,
946 p_doc_type => p_doc_type,
947 p_doc_id => p_doc_id,
948 p_mode => p_qa_mode,
949 p_bus_doc_date_events_tbl => p_bus_doc_date_events_tbl,
950 p_qa_result_tbl => x_qa_result_tbl,
951 x_msg_data => x_msg_data,
952 x_msg_count => x_msg_count,
953 x_return_status => x_return_status,
954 x_qa_return_status => x_qa_return_status);
955 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
956 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
957 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
958 RAISE FND_API.G_EXC_ERROR ;
959 END IF;
960
961 IF x_qa_result_tbl.COUNT > 0 THEN
962 FOR i in x_qa_result_tbl.FIRST..x_qa_result_tbl.LAST LOOP
963
964 x_qa_result_tbl(i).error_record_type_name := okc_util.decode_lookup('OKC_ERROR_RECORD_TYPE',x_qa_result_tbl(i).error_record_type);
965 x_qa_result_tbl(i).error_severity_name := okc_util.decode_lookup('OKC_QA_SEVERITY',x_qa_result_tbl(i).error_severity);
966
967 IF x_qa_result_tbl(i).Error_severity = G_QA_STS_ERROR THEN
968 l_error_found := true;
969 END IF;
970 IF x_qa_result_tbl(i).Error_severity = G_QA_STS_WARNING THEN
971 l_warning_found := true;
972 END IF;
973
974 END LOOP;
975
976 --fix for bug 3290738
977 IF l_error_found THEN
978 x_qa_return_status := G_QA_STS_ERROR;
979 ELSIF l_warning_found THEN
980 x_qa_return_status := G_QA_STS_WARNING;
981 END IF;
982 END IF;
983
984 -- Standard call to get message count and if count is 1, get message info.
985 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
986 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
987 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'300: Leaving QA_Doc - OVERLOADED ONE ');
988 END IF;
989
990 EXCEPTION
991 WHEN FND_API.G_EXC_ERROR THEN
992 ROLLBACK TO g_QA_Doc;
993 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
994 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'400: Leaving QA_Doc : OKC_API.G_EXCEPTION_ERROR Exception');
995 END IF;
996 x_return_status := G_RET_STS_ERROR ;
997 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
998
999 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1000 ROLLBACK TO g_QA_Doc;
1001 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1002 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'500: Leaving QA_Doc : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
1003 END IF;
1004 x_return_status := G_RET_STS_UNEXP_ERROR ;
1005 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1006
1007 WHEN OTHERS THEN
1008 ROLLBACK TO g_QA_Doc;
1009 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1010 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'600: Leaving QA_Doc because of EXCEPTION: '||sqlerrm);
1011 END IF;
1012
1013 x_return_status := G_RET_STS_UNEXP_ERROR ;
1014 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1015 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1016 END IF;
1017 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1018 END QA_Doc;
1019
1020
1021 END OKC_TERMS_QA_GRP;