[Home] [Help]
PACKAGE BODY: APPS.OKC_QA_CHECK_PVT
Source
1 PACKAGE BODY OKC_QA_CHECK_PVT AS
2 /* $Header: OKCRQACB.pls 120.2 2006/11/02 00:46:55 anjkumar noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 -- Start of comments
7 --
8 -- Procedure Name : validate_qcl_id
9 -- Description :
10 -- Business Rules :
11 -- Parameters :
12 -- Version : 1.0
13 -- End of comments
14
15 PROCEDURE validate_qcl_id(
16 x_return_status OUT NOCOPY VARCHAR2,
17 p_qcl_id IN NUMBER
18 ) IS
19 l_dummy_var VARCHAR2(1) := '?';
20 CURSOR l_qclv_csr IS
21 SELECT 'x'
22 FROM OKC_QA_CHECK_LISTS_B qclv
23 WHERE qclv.ID = p_qcl_id;
24 BEGIN
25 -- initialize return status
26 x_return_status := OKC_API.G_RET_STS_SUCCESS;
27
28 -- data is required
29 IF (p_qcl_id = OKC_API.G_MISS_NUM OR
30 p_qcl_id IS NULL) THEN
31 OKC_API.set_message(
32 p_app_name => G_APP_NAME,
33 p_msg_name => G_REQUIRED_VALUE,
34 p_token1 => G_COL_NAME_TOKEN,
35 p_token1_value => 'qcl_id');
36
37 -- notify caller of an error
38 x_return_status := OKC_API.G_RET_STS_ERROR;
39
40 -- halt validation
41 RAISE G_EXCEPTION_HALT_VALIDATION;
42 END IF;
43
44 -- enforce foreign key
45 OPEN l_qclv_csr;
46 FETCH l_qclv_csr INTO l_dummy_var;
47 CLOSE l_qclv_csr;
48
49 -- if l_dummy_var still set to default, data was not found
50 IF (l_dummy_var = '?') THEN
51 OKC_API.set_message(
52 p_app_name => G_APP_NAME,
53 p_msg_name => G_INVALID_VALUE,
54 p_token1 => G_COL_NAME_TOKEN,
55 p_token1_value => 'qcl_id');
56 -- notify caller of an error
57 x_return_status := OKC_API.G_RET_STS_ERROR;
58 END IF;
59 EXCEPTION
60 WHEN G_EXCEPTION_HALT_VALIDATION THEN
61 -- no processing necessary; validation can continue with next column
62 NULL;
63 WHEN OTHERS THEN
64 -- store SQL error message on message stack
65 OKC_API.SET_MESSAGE(
66 p_app_name => G_APP_NAME,
67 p_msg_name => G_UNEXPECTED_ERROR,
68 p_token1 => G_SQLCODE_TOKEN,
69 p_token1_value => SQLCODE,
70 p_token2 => G_SQLERRM_TOKEN,
71 p_token2_value => SQLERRM);
72 -- notify caller of an error as UNEXPETED error
73 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
74 -- verify that cursor was closed
75 IF l_qclv_csr%ISOPEN THEN
76 CLOSE l_qclv_csr;
77 END IF;
78 END validate_qcl_id;
79 --
80 -- Start of comments
81 --
82 -- Procedure Name : execute_qa_check_list
83 -- Description :
84 -- Business Rules :
85 -- Parameters :
86 -- Version : 1.0
87 -- End of comments
88 PROCEDURE execute_qa_check_list(
89 p_api_version IN NUMBER,
90 p_init_msg_list IN VARCHAR2 ,
91 x_return_status OUT NOCOPY VARCHAR2,
92 x_msg_count OUT NOCOPY NUMBER,
93 x_msg_data OUT NOCOPY VARCHAR2,
94 p_qcl_id IN NUMBER,
95 p_chr_id IN NUMBER,
96 p_override_flag IN VARCHAR2 ,
97 x_msg_tbl OUT NOCOPY msg_tbl_type) IS
98
99 l_api_name CONSTANT VARCHAR2(30) := 'execute_qa_check_list';
100
101 e_msg_tbl msg_tbl_type; -- ERROR table
102 w_msg_tbl msg_tbl_type; -- WARNING table
103 s_msg_tbl msg_tbl_type; -- SUCCESS table
104 tot_msg_count NUMBER := 0;
105 l_tot_err_count NUMBER := 0;
106 l_tot_wrn_count NUMBER := 0;
107 l_tot_suc_count NUMBER := 0;
108 i pls_integer;
109 j pls_integer;
110 k pls_integer;
111 -- New variable introduces for backward compatibility.
112 -- Used in the 2nd select statement below to retain the
113 -- logic of OKS qa checks. Should be removed later when
114 -- oks seed their processes for qa checks.
115 qa_phase2_release Varchar2(1) := 'N';
116
117 CURSOR l_scs_csr IS
118 SELECT scs.cls_code,
119 chr.application_id
120 FROM OKC_SUBCLASSES_B scs,
121 OKC_K_HEADERS_B chr
122 WHERE scs.code = chr.scs_code
123 AND chr.id = p_chr_id;
124 l_cls_code OKC_SUBCLASSES_B.CLS_CODE%TYPE;
125 l_appl_id OKC_K_HEADERS_B.APPLICATION_ID%TYPE;
126
127 -- cursor for the list of processes to be executed
128 CURSOR l_qlp_csr IS
129 select 1, pdf.name, pdf.description,
130 pdf.package_name, pdf.procedure_name, qlp.pdf_id,
131 qlp.run_sequence, qlp.severity
132 from OKC_PROCESS_DEFS_V pdf,
133 OKC_QA_LIST_PROCESSES qlp
134 -- OKC_QA_CHECK_LISTS_TL qcl
135 where pdf.id = qlp.pdf_id
136 and sysdate between pdf.begin_date and nvl(pdf.end_date,sysdate)
137 and qlp.active_yn = 'Y'
138 and qlp.qcl_id = 1
139 -- and qlp.qcl_id = qcl.id
140 -- and qcl.name = G_DEFAULT_QA_CHECK_LIST
141 -- and qcl.language = userenv('LANG')
142
143 /* ******** For Bug# 3009832 ******************************
144 union all
145 select 2, pdf.name, pdf.description,
146 pdf.package_name, pdf.procedure_name,
147 -1, rownum, 'S'
148 from OKC_PROCESS_DEFS_V pdf
149 where pdf.package_name = 'OKS_QA_DATA_INTEGRITY'
150 and l_cls_code = 'SERVICE'
151 and sysdate between pdf.begin_date and nvl(pdf.end_date,sysdate)
152 and p_override_flag = 'N'
153 and qa_phase2_release = 'N'
154 *********************************************************** */
155 union all
156 select 3, pdf.name, pdf.description,
157 pdf.package_name, pdf.procedure_name, qlp.pdf_id,
158 qlp.run_sequence, qlp.severity
159 from OKC_PROCESS_DEFS_V pdf,
160 OKC_QA_LIST_PROCESSES qlp,
161 OKC_QA_CHECK_LISTS_B qcl
162 -- OKC_QA_CHECK_LISTS_v qcl
163 where pdf.id = qlp.pdf_id
164 and sysdate between pdf.begin_date and nvl(pdf.end_date,sysdate)
165 and qlp.active_yn = 'Y'
166 and qlp.qcl_id = qcl.id
167 and qcl.id not in (1, p_qcl_id)
168 and qcl.default_yn = 'Y'
169 and qcl.application_id = l_appl_id
170 and p_override_flag = 'N'
171 -- and qcl.name <> G_DEFAULT_QA_CHECK_LIST
172 union all
173 select 4, pdf.name, pdf.description,
174 pdf.package_name, pdf.procedure_name, qlp.pdf_id,
175 qlp.run_sequence, qlp.severity
176 from OKC_PROCESS_DEFS_V pdf,
177 OKC_QA_LIST_PROCESSES qlp
178 -- OKC_QA_CHECK_LISTS_TL qcl
179 where pdf.id = qlp.pdf_id
180 and sysdate between pdf.begin_date and nvl(pdf.end_date,sysdate)
181 and qlp.active_yn = 'Y'
182 and qlp.qcl_id = p_qcl_id
183 and qlp.qcl_id <> 1
184 -- and qlp.qcl_id = qcl.id
185 -- and qcl.id = p_qcl_id
186 -- and qcl.name <> G_DEFAULT_QA_CHECK_LIST
187 -- and qcl.language = userenv('LANG')
188 -- Bug 2170973 ,This following query added to execute qa_pdf_id of okc_class_operations if
189 -- contract category has any operation of purpose 'INTEGRATION' defined in it.
190 union all
191 select 5, pdf.name, pdf.description, pdf.package_name, pdf.procedure_name,
192 cop.qa_pdf_id, OKC_API.G_MISS_NUM, 'S'
193 from OKC_PROCESS_DEFS_V pdf,
194 OKC_K_HEADERS_B khr,
195 OKC_SUBCLASSES_B scs,
196 OKC_CLASS_OPERATIONS cop,
197 OKC_OPERATIONS_B op,
198 okc_assents_v ass
199 where pdf.id = cop.qa_pdf_id
200 and sysdate between pdf.begin_date and nvl(pdf.end_date,sysdate)
201 and scs.code = khr.scs_code
202 and khr.id = p_chr_id
203 and ass.scs_code = scs.code
204 and ass.sts_code = khr.sts_code
205 and cop.opn_code = op.code
206 and cop.opn_code = ass.opn_code
207 and cop.cls_code = scs.cls_code
208 and op.purpose = 'INTEGRATION'
209 and p_override_flag = 'N'
210 and ass.allowed_YN = 'Y' --added for bug 2386576 abkumar
211 order by 1, 7;
212 -- order by 1, 5;
213
214 l_qlp_rec l_qlp_csr%ROWTYPE;
215
216 -- cursor for the parameter list for a processes
217 CURSOR l_qpp_csr IS
218 select pdp.name, pdp.data_type,
219 REPLACE(qpp.parm_value, '''','''''') "PARM_VALUE"
220 from OKC_PROCESS_DEF_PARAMETERS_V pdp,
221 OKC_QA_PROCESS_PARMS_V qpp
222 where pdp.id = qpp.pdp_id
223 and pdp.pdf_id = qpp.qlp_pdf_id
224 and qpp.qlp_pdf_id = l_qlp_rec.pdf_id
225 -- skekkar
226 and qpp.qlp_run_sequence = l_qlp_rec.run_sequence
227 -- skekkar
228 and qpp.qlp_qcl_id = p_qcl_id;
229
230 l_qpp_rec l_qpp_csr%ROWTYPE;
231
232 plsql_block VARCHAR2(30000);
233
234 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
235 l_msg_count NUMBER;
236 l_msg_data VARCHAR2(2000);
237 l_tot_msg_count NUMBER := 0;
238 -- Bug 2934909
239 x number(1) := 1;
240 l_parameter_tbl parameter_tbl_type;
241 l_cursor_id number;
242 m number;
243 l_dummy number;
244 -- End Bug 2934909
245 BEGIN
246
247 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
248 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name,'Entering '|| G_PKG_NAME || '.' || l_api_name);
249 END IF;
250
251 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
252 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'p_qcl_id: ' || p_qcl_id);
253 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'p_chr_id: ' || p_chr_id);
254 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'p_override_flag: ' || p_override_flag);
255 END IF;
256
257 -- initialize return status
258 x_return_status := OKC_API.G_RET_STS_SUCCESS;
259
260 validate_qcl_id(
261 x_return_status => x_return_status,
262 p_qcl_id => p_qcl_id);
263
264 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
265 -- return status to caller, can not continue to process
266 RETURN;
267 END IF;
268 --
269 OPEN l_scs_csr;
270 FETCH l_scs_csr INTO l_cls_code, l_appl_id;
271 CLOSE l_scs_csr;
272
273 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
274 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_cls_code: ' || l_cls_code);
275 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_appl_id: ' || l_appl_id);
276 END IF;
277
278 --anjkumar, save the context first Bug 5609807
279 okc_context.save_current_contexts;
280
281 -- Set the org_id
282 okc_context.set_okc_org_context(p_chr_id => p_chr_id);
283 --
284
285 OPEN l_qlp_csr;
286 LOOP
287 FETCH l_qlp_csr INTO l_qlp_rec;
288 EXIT WHEN l_qlp_csr%NOTFOUND;
289 l_cursor_id := DBMS_SQL.OPEN_CURSOR; --Bug2934909
290 plsql_block := 'BEGIN ' ||
291 l_qlp_rec.package_name || '.' ||
292 l_qlp_rec.procedure_name || '( ' ||
293 'x_return_status => :l_return_status ' ||
294 ',p_chr_id =>:p_chr_id ' ;
295
296
297 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
298 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_qlp_rec.package_name: ' || l_qlp_rec.package_name);
299 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_qlp_rec.procedure_name: ' || l_qlp_rec.procedure_name);
300 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_qlp_rec.pdf_id: ' || l_qlp_rec.pdf_id);
301 END IF;
302
303
304 -- -1 means no parameters
305 -- Bug 2934909
306 IF l_qlp_rec.pdf_id <> -1 THEN
307 OPEN l_qpp_csr;
308 l_parameter_tbl.delete;
309 m :=1;
310 LOOP
311 FETCH l_qpp_csr INTO l_qpp_rec;
312 EXIT WHEN l_qpp_csr%NOTFOUND;
313 plsql_block := plsql_block || ',' || l_qpp_rec.name || ' => :'||to_char(m) ;
314 l_parameter_tbl(m).param_value := l_qpp_rec.parm_value;
315 m := m+1;
316 END LOOP;
317 CLOSE l_qpp_csr;
318 END IF;
319 -- End Bug 2934909
320
321 plsql_block := plsql_block || ') ; END;';
322 -- Bug 2934909
323 DBMS_SQL.PARSE(l_cursor_id,plsql_block , 2);
324 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':l_return_status',l_return_status);
325 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':p_chr_id',p_chr_id);
326
327 If l_parameter_tbl.count > 0 then
328 FOR j in l_parameter_tbl.FIRST..l_parameter_tbl.LAST LOOP
329 DBMS_SQL.BIND_VARIABLE(l_cursor_id, to_char(j),l_parameter_tbl(j).param_value);
330 END LOOP;
331 end if;
332
333 -- End Bug 2934909
334 -- clean up the message list
335 fnd_msg_pub.initialize;
336
337 l_dummy := DBMS_SQL.EXECUTE(l_cursor_id);
338 dbms_Sql.variable_Value(l_cursor_id, ':l_return_status', l_return_status);
339
340 DBMS_SQL.CLOSE_CURSOR(l_cursor_id); --Bug 3385459
341
342 /* Bug2934909
343 BEGIN
344 EXECUTE IMMEDIATE plsql_block
345 USING IN OUT l_return_status,IN p_chr_id;
346 EXCEPTION
347 -- WHEN OTHERS THEN
348 -- -- store SQL error message on message stack
349 -- OKC_API.SET_MESSAGE(
350 -- p_app_name => G_APP_NAME,
351 -- p_msg_name => G_QA_PROCESS_ERROR);
352 -- -- notify caller of an error as UNEXPETED error
353 -- l_return_status := OKC_API.G_RET_STS_ERROR;
354 -- END;
355 --
356 -- -- Get error messages to return
357 -- -- assign message values to return
358 */ -- End Bug2934909
359
360 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
361 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'pub_qa_msg_tbl.count: ' || pub_qa_msg_tbl.count);
362 END IF;
363
364 IF pub_qa_msg_tbl.count > 0 THEN -- if QA check has populated the table, message stack is empty
365
366 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
367 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'QA check has populated the table, message stack is empty');
368 END IF;
369
370 FOR i IN pub_qa_msg_tbl.first..pub_qa_msg_tbl.last LOOP
371 IF pub_qa_msg_tbl(i).error_status = 'E' THEN
372 -- look at the severity
373 IF l_qlp_rec.severity = 'S' THEN
374 l_return_status := 'E';
375 ELSE
376 l_return_status := 'W';
377 END IF;
378 END IF; -- l_return_status = 'E'
379 IF l_return_status = 'E' THEN
380 l_tot_err_count := l_tot_err_count + 1;
381 e_msg_tbl(l_tot_err_count).name := l_qlp_rec.name;
382 e_msg_tbl(l_tot_err_count).description := l_qlp_rec.description;
383 e_msg_tbl(l_tot_err_count).package_name := l_qlp_rec.package_name;
384 e_msg_tbl(l_tot_err_count).procedure_name := l_qlp_rec.procedure_name;
385 e_msg_tbl(l_tot_err_count).severity := l_qlp_rec.severity;
386 e_msg_tbl(l_tot_err_count).error_status := l_return_status;
387 e_msg_tbl(l_tot_err_count).data := pub_qa_msg_tbl(i).data;
388 ELSIF l_return_status = 'W' THEN
389 l_tot_wrn_count := l_tot_wrn_count + 1;
390 w_msg_tbl(l_tot_wrn_count).name := l_qlp_rec.name;
391 w_msg_tbl(l_tot_wrn_count).description := l_qlp_rec.description;
392 w_msg_tbl(l_tot_wrn_count).package_name := l_qlp_rec.package_name;
393 w_msg_tbl(l_tot_wrn_count).procedure_name := l_qlp_rec.procedure_name;
394 w_msg_tbl(l_tot_wrn_count).severity := l_qlp_rec.severity;
395 w_msg_tbl(l_tot_wrn_count).error_status := l_return_status;
396 w_msg_tbl(l_tot_wrn_count).data := pub_qa_msg_tbl(i).data;
397 ELSE
398 l_tot_suc_count := l_tot_suc_count + 1;
399 s_msg_tbl(l_tot_suc_count).name := l_qlp_rec.name;
400 s_msg_tbl(l_tot_suc_count).description := l_qlp_rec.description;
401 s_msg_tbl(l_tot_suc_count).package_name := l_qlp_rec.package_name;
402 s_msg_tbl(l_tot_suc_count).procedure_name := l_qlp_rec.procedure_name;
403 s_msg_tbl(l_tot_suc_count).severity := l_qlp_rec.severity;
404 s_msg_tbl(l_tot_suc_count).error_status := l_return_status;
405 s_msg_tbl(l_tot_suc_count).data := pub_qa_msg_tbl(i).data;
406 END IF;
407
408 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
409 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'pub_qa_msg_tbl(' || i ||').error_status: ' || pub_qa_msg_tbl(i).error_status);
410 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_return_status: ' || l_return_status);
411 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_qlp_rec.name: ' || l_qlp_rec.name);
412 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_qlp_rec.description: ' || l_qlp_rec.description);
413 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_qlp_rec.package_name: ' || l_qlp_rec.package_name);
414 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_qlp_rec.procedure_name: ' || l_qlp_rec.procedure_name);
415 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_qlp_rec.severity: ' || l_qlp_rec.severity);
416 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'pub_qa_msg_tbl(' || i ||').data: ' || pub_qa_msg_tbl(i).data);
417 END IF;
418
419 END LOOP;
420 pub_qa_msg_tbl.delete;
421 ELSE -- else - QA check has populated the message stack, even if success
422 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
423 p_encoded => fnd_api.g_false);
424
425 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
426 IF l_msg_data IS NOT NULL THEN
427 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'QA check has populated the message stack');
428 ELSE
429 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'QA check has populated the message stack with no messages');
430 END IF;
431 END IF;
432
433 -- Even if succesful, the QA program will load a message for
434 -- us to retrieve
435 WHILE l_msg_data IS NOT NULL
436 LOOP
437 l_tot_msg_count := l_tot_msg_count + 1;
438
439 -- skekkar ####
440 -- Return status will depend on severity defined by the user if the qa check fails
441
442 IF l_return_status = 'E' THEN
443 -- look at the severity
444 IF l_qlp_rec.severity = 'S' THEN
445 l_return_status := 'E';
446 ELSE
447 l_return_status := 'W';
448 END IF;
449 END IF; -- l_return_status = 'E'
450 -- skekkar ####
451
452 -- store the program results
453 -- skekkar
454 IF l_return_status = 'E' THEN
455 l_tot_err_count := l_tot_err_count + 1;
456 e_msg_tbl(l_tot_err_count).name := l_qlp_rec.name;
457 e_msg_tbl(l_tot_err_count).description := l_qlp_rec.description;
458 e_msg_tbl(l_tot_err_count).package_name := l_qlp_rec.package_name;
459 e_msg_tbl(l_tot_err_count).procedure_name := l_qlp_rec.procedure_name;
460 e_msg_tbl(l_tot_err_count).severity := l_qlp_rec.severity;
461 e_msg_tbl(l_tot_err_count).error_status := l_return_status;
462 e_msg_tbl(l_tot_err_count).data := l_msg_data;
463 ELSIF l_return_status = 'W' THEN
464 l_tot_wrn_count := l_tot_wrn_count + 1;
465 w_msg_tbl(l_tot_wrn_count).name := l_qlp_rec.name;
466 w_msg_tbl(l_tot_wrn_count).description := l_qlp_rec.description;
467 w_msg_tbl(l_tot_wrn_count).package_name := l_qlp_rec.package_name;
468 w_msg_tbl(l_tot_wrn_count).procedure_name := l_qlp_rec.procedure_name;
469 w_msg_tbl(l_tot_wrn_count).severity := l_qlp_rec.severity;
470 w_msg_tbl(l_tot_wrn_count).error_status := l_return_status;
471 w_msg_tbl(l_tot_wrn_count).data := l_msg_data;
472 ELSE
473 l_tot_suc_count := l_tot_suc_count + 1;
474 s_msg_tbl(l_tot_suc_count).name := l_qlp_rec.name;
475 s_msg_tbl(l_tot_suc_count).description := l_qlp_rec.description;
476 s_msg_tbl(l_tot_suc_count).package_name := l_qlp_rec.package_name;
477 s_msg_tbl(l_tot_suc_count).procedure_name := l_qlp_rec.procedure_name;
478 s_msg_tbl(l_tot_suc_count).severity := l_qlp_rec.severity;
479 s_msg_tbl(l_tot_suc_count).error_status := l_return_status;
480 s_msg_tbl(l_tot_suc_count).data := l_msg_data;
481 END IF;
482
483 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
484 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_return_status: ' || l_return_status);
485 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_qlp_rec.name: ' || l_qlp_rec.name);
486 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_qlp_rec.description: ' || l_qlp_rec.description);
487 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_qlp_rec.package_name: ' || l_qlp_rec.package_name);
488 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_qlp_rec.procedure_name: ' || l_qlp_rec.procedure_name);
489 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_qlp_rec.severity: ' || l_qlp_rec.severity);
490 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_msg_data: ' || l_msg_data);
491 END IF;
492
493 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_next,
494 p_encoded => fnd_api.g_false);
495 END LOOP;
496 END IF;
497
498 END LOOP;
499 CLOSE l_qlp_csr;
500
501 -- assign to out table in sort order E , W and S
502
503 FOR i IN NVL(e_msg_tbl.FIRST,0)..NVL(e_msg_tbl.LAST,-1)
504 LOOP
505
506 tot_msg_count := tot_msg_count + 1;
507
508 x_msg_tbl(tot_msg_count).name := e_msg_tbl(i).name;
509 x_msg_tbl(tot_msg_count).description := e_msg_tbl(i).description;
510 x_msg_tbl(tot_msg_count).package_name := e_msg_tbl(i).package_name;
511 x_msg_tbl(tot_msg_count).procedure_name := e_msg_tbl(i).procedure_name;
512 x_msg_tbl(tot_msg_count).severity := e_msg_tbl(i).severity;
513 x_msg_tbl(tot_msg_count).error_status := e_msg_tbl(i).error_status;
514 x_msg_tbl(tot_msg_count).data := e_msg_tbl(i).data;
515
516 END LOOP;
517
518 FOR j IN NVL(w_msg_tbl.FIRST,0)..NVL(w_msg_tbl.LAST,-1)
519 LOOP
520
521 tot_msg_count := tot_msg_count + 1;
522
523 x_msg_tbl(tot_msg_count).name := w_msg_tbl(j).name;
524 x_msg_tbl(tot_msg_count).description := w_msg_tbl(j).description;
525 x_msg_tbl(tot_msg_count).package_name := w_msg_tbl(j).package_name;
526 x_msg_tbl(tot_msg_count).procedure_name := w_msg_tbl(j).procedure_name;
527 x_msg_tbl(tot_msg_count).severity := w_msg_tbl(j).severity;
528 x_msg_tbl(tot_msg_count).error_status := w_msg_tbl(j).error_status;
529 x_msg_tbl(tot_msg_count).data := w_msg_tbl(j).data;
530
531 END LOOP;
532
533 FOR k IN NVL(s_msg_tbl.FIRST,0)..NVL(s_msg_tbl.LAST,-1)
534 LOOP
535
536 tot_msg_count := tot_msg_count + 1;
537
538 x_msg_tbl(tot_msg_count).name := s_msg_tbl(k).name;
539 x_msg_tbl(tot_msg_count).description := s_msg_tbl(k).description;
540 x_msg_tbl(tot_msg_count).package_name := s_msg_tbl(k).package_name;
541 x_msg_tbl(tot_msg_count).procedure_name := s_msg_tbl(k).procedure_name;
542 x_msg_tbl(tot_msg_count).severity := s_msg_tbl(k).severity;
543 x_msg_tbl(tot_msg_count).error_status := s_msg_tbl(k).error_status;
544 x_msg_tbl(tot_msg_count).data := s_msg_tbl(k).data;
545
546 END LOOP;
547
548
549 --anjkumar, restore the context Bug 5609807
550 okc_context.restore_contexts;
551
552 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
553 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name,'Leaving '|| G_PKG_NAME || '.' || l_api_name);
554 END IF;
555
556
557 EXCEPTION
558 WHEN G_EXCEPTION_HALT_VALIDATION THEN
559
560 --anjkumar, restore the context Bug 5609807
561 okc_context.restore_contexts;
562
563 --Bug 3300707
564 if (l_cursor_id is not null) then
565 DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
566 end if;
567 IF l_qlp_csr%ISOPEN THEN
568 CLOSE l_qlp_csr;
569 END IF;
570 IF l_qpp_csr%ISOPEN THEN
571 CLOSE l_qpp_csr;
572 END IF;
573 IF l_scs_csr%ISOPEN THEN
574 CLOSE l_scs_csr;
575 END IF;
576
577
578 WHEN OTHERS THEN
579
580 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
581 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'WHEN OTHERS: setting message after encountering error: ' || SQLCODE || ' ' || SQLERRM);
582 END IF;
583
584 --anjkumar, restore the context Bug 5609807
585 okc_context.restore_contexts;
586
587 -- close cursor
588 if (l_cursor_id is not null) then --Bug 3378989
589 DBMS_SQL.CLOSE_CURSOR(l_cursor_id); --Bug2934909
590 end if;
591 -- store SQL error message on message stack
592 OKC_API.SET_MESSAGE(
593 p_app_name => G_APP_NAME,
594 p_msg_name => G_UNEXPECTED_ERROR,
595 p_token1 => G_SQLCODE_TOKEN,
596 p_token1_value => SQLCODE,
597 p_token2 => G_SQLERRM_TOKEN,
598 p_token2_value => SQLERRM);
599 -- notify caller of an error as UNEXPETED error
600 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
601 IF l_qlp_csr%ISOPEN THEN
602 CLOSE l_qlp_csr;
603 END IF;
604 IF l_qpp_csr%ISOPEN THEN
605 CLOSE l_qpp_csr;
606 END IF;
607 IF l_scs_csr%ISOPEN THEN
608 CLOSE l_scs_csr;
609 END IF;
610 END execute_qa_check_list;
611
612
613 END OKC_QA_CHECK_PVT;