[Home] [Help]
PACKAGE BODY: APPS.OKC_QA_CHECK_PVT
Source
1 PACKAGE BODY OKC_QA_CHECK_PVT AS
2 /* $Header: OKCRQACB.pls 120.2.12010000.2 2009/05/07 05:51:07 spingali ship $ */
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 ((application_id = l_appl_id AND PDF.PROCEDURE_NAME <> 'CHECK_ADDRESS') OR
140 application_id <> l_appl_id ) /*Bug 7447222*/
141
142 -- and qlp.qcl_id = qcl.id
143 -- and qcl.name = G_DEFAULT_QA_CHECK_LIST
144 -- and qcl.language = userenv('LANG')
145
146 /* ******** For Bug# 3009832 ******************************
147 union all
148 select 2, pdf.name, pdf.description,
149 pdf.package_name, pdf.procedure_name,
150 -1, rownum, 'S'
151 from OKC_PROCESS_DEFS_V pdf
152 where pdf.package_name = 'OKS_QA_DATA_INTEGRITY'
153 and l_cls_code = 'SERVICE'
154 and sysdate between pdf.begin_date and nvl(pdf.end_date,sysdate)
155 and p_override_flag = 'N'
156 and qa_phase2_release = 'N'
157 *********************************************************** */
158 union all
159 select 3, pdf.name, pdf.description,
160 pdf.package_name, pdf.procedure_name, qlp.pdf_id,
161 qlp.run_sequence, qlp.severity
162 from OKC_PROCESS_DEFS_V pdf,
163 OKC_QA_LIST_PROCESSES qlp,
164 OKC_QA_CHECK_LISTS_B qcl
165 -- OKC_QA_CHECK_LISTS_v qcl
166 where pdf.id = qlp.pdf_id
167 and sysdate between pdf.begin_date and nvl(pdf.end_date,sysdate)
168 and qlp.active_yn = 'Y'
169 and qlp.qcl_id = qcl.id
170 and qcl.id not in (1, p_qcl_id)
171 and qcl.default_yn = 'Y'
172 and qcl.application_id = l_appl_id
173 and p_override_flag = 'N'
174 -- and qcl.name <> G_DEFAULT_QA_CHECK_LIST
175 union all
176 select 4, pdf.name, pdf.description,
177 pdf.package_name, pdf.procedure_name, qlp.pdf_id,
178 qlp.run_sequence, qlp.severity
179 from OKC_PROCESS_DEFS_V pdf,
180 OKC_QA_LIST_PROCESSES qlp
181 -- OKC_QA_CHECK_LISTS_TL qcl
182 where pdf.id = qlp.pdf_id
183 and sysdate between pdf.begin_date and nvl(pdf.end_date,sysdate)
184 and qlp.active_yn = 'Y'
185 and qlp.qcl_id = p_qcl_id
186 and qlp.qcl_id <> 1
187 -- and qlp.qcl_id = qcl.id
188 -- and qcl.id = p_qcl_id
189 -- and qcl.name <> G_DEFAULT_QA_CHECK_LIST
190 -- and qcl.language = userenv('LANG')
191 -- Bug 2170973 ,This following query added to execute qa_pdf_id of okc_class_operations if
192 -- contract category has any operation of purpose 'INTEGRATION' defined in it.
193 union all
194 select 5, pdf.name, pdf.description, pdf.package_name, pdf.procedure_name,
195 cop.qa_pdf_id, OKC_API.G_MISS_NUM, 'S'
196 from OKC_PROCESS_DEFS_V pdf,
197 OKC_K_HEADERS_B khr,
198 OKC_SUBCLASSES_B scs,
199 OKC_CLASS_OPERATIONS cop,
200 OKC_OPERATIONS_B op,
201 okc_assents_v ass
202 where pdf.id = cop.qa_pdf_id
203 and sysdate between pdf.begin_date and nvl(pdf.end_date,sysdate)
204 and scs.code = khr.scs_code
205 and khr.id = p_chr_id
206 and ass.scs_code = scs.code
207 and ass.sts_code = khr.sts_code
208 and cop.opn_code = op.code
209 and cop.opn_code = ass.opn_code
210 and cop.cls_code = scs.cls_code
211 and op.purpose = 'INTEGRATION'
212 and p_override_flag = 'N'
213 and ass.allowed_YN = 'Y' --added for bug 2386576 abkumar
214 order by 1, 7;
215 -- order by 1, 5;
216
217 l_qlp_rec l_qlp_csr%ROWTYPE;
218
219 -- cursor for the parameter list for a processes
220 CURSOR l_qpp_csr IS
221 select pdp.name, pdp.data_type,
222 REPLACE(qpp.parm_value, '''','''''') "PARM_VALUE"
223 from OKC_PROCESS_DEF_PARAMETERS_V pdp,
224 OKC_QA_PROCESS_PARMS_V qpp
225 where pdp.id = qpp.pdp_id
226 and pdp.pdf_id = qpp.qlp_pdf_id
227 and qpp.qlp_pdf_id = l_qlp_rec.pdf_id
228 -- skekkar
229 and qpp.qlp_run_sequence = l_qlp_rec.run_sequence
230 -- skekkar
231 and qpp.qlp_qcl_id = p_qcl_id;
232
233 l_qpp_rec l_qpp_csr%ROWTYPE;
234
235 plsql_block VARCHAR2(30000);
236
237 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
238 l_msg_count NUMBER;
239 l_msg_data VARCHAR2(2000);
240 l_tot_msg_count NUMBER := 0;
241 -- Bug 2934909
242 x number(1) := 1;
243 l_parameter_tbl parameter_tbl_type;
244 l_cursor_id number;
245 m number;
246 l_dummy number;
247 -- End Bug 2934909
248 BEGIN
249
250 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
251 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name,'Entering '|| G_PKG_NAME || '.' || l_api_name);
252 END IF;
253
254 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
255 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'p_qcl_id: ' || p_qcl_id);
256 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'p_chr_id: ' || p_chr_id);
257 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'p_override_flag: ' || p_override_flag);
258 END IF;
259
260 -- initialize return status
261 x_return_status := OKC_API.G_RET_STS_SUCCESS;
262
263 validate_qcl_id(
264 x_return_status => x_return_status,
265 p_qcl_id => p_qcl_id);
266
267 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
268 -- return status to caller, can not continue to process
269 RETURN;
270 END IF;
271 --
272 OPEN l_scs_csr;
273 FETCH l_scs_csr INTO l_cls_code, l_appl_id;
274 CLOSE l_scs_csr;
275
276 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
277 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_cls_code: ' || l_cls_code);
278 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_appl_id: ' || l_appl_id);
279 END IF;
280
281 --anjkumar, save the context first Bug 5609807
282 okc_context.save_current_contexts;
283
284 -- Set the org_id
285 okc_context.set_okc_org_context(p_chr_id => p_chr_id);
286 --
287
288 OPEN l_qlp_csr;
289 LOOP
290 FETCH l_qlp_csr INTO l_qlp_rec;
291 EXIT WHEN l_qlp_csr%NOTFOUND;
292 l_cursor_id := DBMS_SQL.OPEN_CURSOR; --Bug2934909
293 plsql_block := 'BEGIN ' ||
294 l_qlp_rec.package_name || '.' ||
295 l_qlp_rec.procedure_name || '( ' ||
296 'x_return_status => :l_return_status ' ||
297 ',p_chr_id =>:p_chr_id ' ;
298
299
300 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
301 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_qlp_rec.package_name: ' || l_qlp_rec.package_name);
302 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_qlp_rec.procedure_name: ' || l_qlp_rec.procedure_name);
303 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_qlp_rec.pdf_id: ' || l_qlp_rec.pdf_id);
304 END IF;
305
306
307 -- -1 means no parameters
308 -- Bug 2934909
309 IF l_qlp_rec.pdf_id <> -1 THEN
310 OPEN l_qpp_csr;
311 l_parameter_tbl.delete;
312 m :=1;
313 LOOP
314 FETCH l_qpp_csr INTO l_qpp_rec;
315 EXIT WHEN l_qpp_csr%NOTFOUND;
316 plsql_block := plsql_block || ',' || l_qpp_rec.name || ' => :'||to_char(m) ;
317 l_parameter_tbl(m).param_value := l_qpp_rec.parm_value;
318 m := m+1;
319 END LOOP;
320 CLOSE l_qpp_csr;
321 END IF;
322 -- End Bug 2934909
323
324 plsql_block := plsql_block || ') ; END;';
325 -- Bug 2934909
326 DBMS_SQL.PARSE(l_cursor_id,plsql_block , 2);
327 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':l_return_status',l_return_status);
328 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':p_chr_id',p_chr_id);
329
330 If l_parameter_tbl.count > 0 then
331 FOR j in l_parameter_tbl.FIRST..l_parameter_tbl.LAST LOOP
332 DBMS_SQL.BIND_VARIABLE(l_cursor_id, to_char(j),l_parameter_tbl(j).param_value);
333 END LOOP;
334 end if;
335
336 -- End Bug 2934909
337 -- clean up the message list
338 fnd_msg_pub.initialize;
339
340 l_dummy := DBMS_SQL.EXECUTE(l_cursor_id);
341 dbms_Sql.variable_Value(l_cursor_id, ':l_return_status', l_return_status);
342
343 DBMS_SQL.CLOSE_CURSOR(l_cursor_id); --Bug 3385459
344
345 /* Bug2934909
346 BEGIN
347 EXECUTE IMMEDIATE plsql_block
348 USING IN OUT l_return_status,IN p_chr_id;
349 EXCEPTION
350 -- WHEN OTHERS THEN
351 -- -- store SQL error message on message stack
352 -- OKC_API.SET_MESSAGE(
353 -- p_app_name => G_APP_NAME,
354 -- p_msg_name => G_QA_PROCESS_ERROR);
355 -- -- notify caller of an error as UNEXPETED error
356 -- l_return_status := OKC_API.G_RET_STS_ERROR;
357 -- END;
358 --
359 -- -- Get error messages to return
360 -- -- assign message values to return
361 */ -- End Bug2934909
362
363 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
364 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'pub_qa_msg_tbl.count: ' || pub_qa_msg_tbl.count);
365 END IF;
366
367 IF pub_qa_msg_tbl.count > 0 THEN -- if QA check has populated the table, message stack is empty
368
369 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
370 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'QA check has populated the table, message stack is empty');
371 END IF;
372
373 FOR i IN pub_qa_msg_tbl.first..pub_qa_msg_tbl.last LOOP
374 IF pub_qa_msg_tbl(i).error_status = 'E' THEN
375 -- look at the severity
376 IF l_qlp_rec.severity = 'S' THEN
377 l_return_status := 'E';
378 ELSE
379 l_return_status := 'W';
380 END IF;
381 END IF; -- l_return_status = 'E'
382 IF l_return_status = 'E' THEN
383 l_tot_err_count := l_tot_err_count + 1;
384 e_msg_tbl(l_tot_err_count).name := l_qlp_rec.name;
385 e_msg_tbl(l_tot_err_count).description := l_qlp_rec.description;
386 e_msg_tbl(l_tot_err_count).package_name := l_qlp_rec.package_name;
387 e_msg_tbl(l_tot_err_count).procedure_name := l_qlp_rec.procedure_name;
388 e_msg_tbl(l_tot_err_count).severity := l_qlp_rec.severity;
389 e_msg_tbl(l_tot_err_count).error_status := l_return_status;
390 e_msg_tbl(l_tot_err_count).data := pub_qa_msg_tbl(i).data;
391 ELSIF l_return_status = 'W' THEN
392 l_tot_wrn_count := l_tot_wrn_count + 1;
393 w_msg_tbl(l_tot_wrn_count).name := l_qlp_rec.name;
394 w_msg_tbl(l_tot_wrn_count).description := l_qlp_rec.description;
395 w_msg_tbl(l_tot_wrn_count).package_name := l_qlp_rec.package_name;
396 w_msg_tbl(l_tot_wrn_count).procedure_name := l_qlp_rec.procedure_name;
397 w_msg_tbl(l_tot_wrn_count).severity := l_qlp_rec.severity;
398 w_msg_tbl(l_tot_wrn_count).error_status := l_return_status;
399 w_msg_tbl(l_tot_wrn_count).data := pub_qa_msg_tbl(i).data;
400 ELSE
401 l_tot_suc_count := l_tot_suc_count + 1;
402 s_msg_tbl(l_tot_suc_count).name := l_qlp_rec.name;
403 s_msg_tbl(l_tot_suc_count).description := l_qlp_rec.description;
404 s_msg_tbl(l_tot_suc_count).package_name := l_qlp_rec.package_name;
405 s_msg_tbl(l_tot_suc_count).procedure_name := l_qlp_rec.procedure_name;
406 s_msg_tbl(l_tot_suc_count).severity := l_qlp_rec.severity;
407 s_msg_tbl(l_tot_suc_count).error_status := l_return_status;
408 s_msg_tbl(l_tot_suc_count).data := pub_qa_msg_tbl(i).data;
409 END IF;
410
411 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
412 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);
413 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_return_status: ' || l_return_status);
414 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_qlp_rec.name: ' || l_qlp_rec.name);
415 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_qlp_rec.description: ' || l_qlp_rec.description);
416 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_qlp_rec.package_name: ' || l_qlp_rec.package_name);
417 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_qlp_rec.procedure_name: ' || l_qlp_rec.procedure_name);
418 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_qlp_rec.severity: ' || l_qlp_rec.severity);
419 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);
420 END IF;
421
422 END LOOP;
423 pub_qa_msg_tbl.delete;
424 ELSE -- else - QA check has populated the message stack, even if success
425 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first,
426 p_encoded => fnd_api.g_false);
427
428 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
429 IF l_msg_data IS NOT NULL THEN
430 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'QA check has populated the message stack');
431 ELSE
432 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'QA check has populated the message stack with no messages');
433 END IF;
434 END IF;
435
436 -- Even if succesful, the QA program will load a message for
437 -- us to retrieve
438 WHILE l_msg_data IS NOT NULL
439 LOOP
440 l_tot_msg_count := l_tot_msg_count + 1;
441
442 -- skekkar ####
443 -- Return status will depend on severity defined by the user if the qa check fails
444
445 IF l_return_status = 'E' THEN
446 -- look at the severity
447 IF l_qlp_rec.severity = 'S' THEN
448 l_return_status := 'E';
449 ELSE
450 l_return_status := 'W';
451 END IF;
452 END IF; -- l_return_status = 'E'
453 -- skekkar ####
454
455 -- store the program results
456 -- skekkar
457 IF l_return_status = 'E' THEN
458 l_tot_err_count := l_tot_err_count + 1;
459 e_msg_tbl(l_tot_err_count).name := l_qlp_rec.name;
460 e_msg_tbl(l_tot_err_count).description := l_qlp_rec.description;
461 e_msg_tbl(l_tot_err_count).package_name := l_qlp_rec.package_name;
462 e_msg_tbl(l_tot_err_count).procedure_name := l_qlp_rec.procedure_name;
463 e_msg_tbl(l_tot_err_count).severity := l_qlp_rec.severity;
464 e_msg_tbl(l_tot_err_count).error_status := l_return_status;
465 e_msg_tbl(l_tot_err_count).data := l_msg_data;
466 ELSIF l_return_status = 'W' THEN
467 l_tot_wrn_count := l_tot_wrn_count + 1;
468 w_msg_tbl(l_tot_wrn_count).name := l_qlp_rec.name;
469 w_msg_tbl(l_tot_wrn_count).description := l_qlp_rec.description;
470 w_msg_tbl(l_tot_wrn_count).package_name := l_qlp_rec.package_name;
471 w_msg_tbl(l_tot_wrn_count).procedure_name := l_qlp_rec.procedure_name;
472 w_msg_tbl(l_tot_wrn_count).severity := l_qlp_rec.severity;
473 w_msg_tbl(l_tot_wrn_count).error_status := l_return_status;
474 w_msg_tbl(l_tot_wrn_count).data := l_msg_data;
475 ELSE
476 l_tot_suc_count := l_tot_suc_count + 1;
477 s_msg_tbl(l_tot_suc_count).name := l_qlp_rec.name;
478 s_msg_tbl(l_tot_suc_count).description := l_qlp_rec.description;
479 s_msg_tbl(l_tot_suc_count).package_name := l_qlp_rec.package_name;
480 s_msg_tbl(l_tot_suc_count).procedure_name := l_qlp_rec.procedure_name;
481 s_msg_tbl(l_tot_suc_count).severity := l_qlp_rec.severity;
482 s_msg_tbl(l_tot_suc_count).error_status := l_return_status;
483 s_msg_tbl(l_tot_suc_count).data := l_msg_data;
484 END IF;
485
486 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
487 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_return_status: ' || l_return_status);
488 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_qlp_rec.name: ' || l_qlp_rec.name);
489 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_qlp_rec.description: ' || l_qlp_rec.description);
490 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_qlp_rec.package_name: ' || l_qlp_rec.package_name);
491 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_qlp_rec.procedure_name: ' || l_qlp_rec.procedure_name);
492 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_qlp_rec.severity: ' || l_qlp_rec.severity);
493 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_msg_data: ' || l_msg_data);
494 END IF;
495
496 l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_next,
497 p_encoded => fnd_api.g_false);
498 END LOOP;
499 END IF;
500
501 END LOOP;
502 CLOSE l_qlp_csr;
503
504 -- assign to out table in sort order E , W and S
505
506 FOR i IN NVL(e_msg_tbl.FIRST,0)..NVL(e_msg_tbl.LAST,-1)
507 LOOP
508
509 tot_msg_count := tot_msg_count + 1;
510
511 x_msg_tbl(tot_msg_count).name := e_msg_tbl(i).name;
512 x_msg_tbl(tot_msg_count).description := e_msg_tbl(i).description;
513 x_msg_tbl(tot_msg_count).package_name := e_msg_tbl(i).package_name;
514 x_msg_tbl(tot_msg_count).procedure_name := e_msg_tbl(i).procedure_name;
515 x_msg_tbl(tot_msg_count).severity := e_msg_tbl(i).severity;
516 x_msg_tbl(tot_msg_count).error_status := e_msg_tbl(i).error_status;
517 x_msg_tbl(tot_msg_count).data := e_msg_tbl(i).data;
518
519 END LOOP;
520
521 FOR j IN NVL(w_msg_tbl.FIRST,0)..NVL(w_msg_tbl.LAST,-1)
522 LOOP
523
524 tot_msg_count := tot_msg_count + 1;
525
526 x_msg_tbl(tot_msg_count).name := w_msg_tbl(j).name;
527 x_msg_tbl(tot_msg_count).description := w_msg_tbl(j).description;
528 x_msg_tbl(tot_msg_count).package_name := w_msg_tbl(j).package_name;
529 x_msg_tbl(tot_msg_count).procedure_name := w_msg_tbl(j).procedure_name;
530 x_msg_tbl(tot_msg_count).severity := w_msg_tbl(j).severity;
531 x_msg_tbl(tot_msg_count).error_status := w_msg_tbl(j).error_status;
532 x_msg_tbl(tot_msg_count).data := w_msg_tbl(j).data;
533
534 END LOOP;
535
536 FOR k IN NVL(s_msg_tbl.FIRST,0)..NVL(s_msg_tbl.LAST,-1)
537 LOOP
538
539 tot_msg_count := tot_msg_count + 1;
540
541 x_msg_tbl(tot_msg_count).name := s_msg_tbl(k).name;
542 x_msg_tbl(tot_msg_count).description := s_msg_tbl(k).description;
543 x_msg_tbl(tot_msg_count).package_name := s_msg_tbl(k).package_name;
544 x_msg_tbl(tot_msg_count).procedure_name := s_msg_tbl(k).procedure_name;
545 x_msg_tbl(tot_msg_count).severity := s_msg_tbl(k).severity;
546 x_msg_tbl(tot_msg_count).error_status := s_msg_tbl(k).error_status;
547 x_msg_tbl(tot_msg_count).data := s_msg_tbl(k).data;
548
549 END LOOP;
550
551
552 --anjkumar, restore the context Bug 5609807
553 okc_context.restore_contexts;
554
555 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
556 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name,'Leaving '|| G_PKG_NAME || '.' || l_api_name);
557 END IF;
558
559
560 EXCEPTION
561 WHEN G_EXCEPTION_HALT_VALIDATION THEN
562
563 --anjkumar, restore the context Bug 5609807
564 okc_context.restore_contexts;
565
566 --Bug 3300707
567 if (l_cursor_id is not null) then
568 DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
569 end if;
570 IF l_qlp_csr%ISOPEN THEN
571 CLOSE l_qlp_csr;
572 END IF;
573 IF l_qpp_csr%ISOPEN THEN
574 CLOSE l_qpp_csr;
575 END IF;
576 IF l_scs_csr%ISOPEN THEN
577 CLOSE l_scs_csr;
578 END IF;
579
580
581 WHEN OTHERS THEN
582
583 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
584 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'WHEN OTHERS: setting message after encountering error: ' || SQLCODE || ' ' || SQLERRM);
585 END IF;
586
587 --anjkumar, restore the context Bug 5609807
588 okc_context.restore_contexts;
589
590 -- close cursor
591 if (l_cursor_id is not null) then --Bug 3378989
592 DBMS_SQL.CLOSE_CURSOR(l_cursor_id); --Bug2934909
593 end if;
594 -- store SQL error message on message stack
595 OKC_API.SET_MESSAGE(
596 p_app_name => G_APP_NAME,
597 p_msg_name => G_UNEXPECTED_ERROR,
598 p_token1 => G_SQLCODE_TOKEN,
599 p_token1_value => SQLCODE,
600 p_token2 => G_SQLERRM_TOKEN,
601 p_token2_value => SQLERRM);
602 -- notify caller of an error as UNEXPETED error
603 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
604 IF l_qlp_csr%ISOPEN THEN
605 CLOSE l_qlp_csr;
606 END IF;
607 IF l_qpp_csr%ISOPEN THEN
608 CLOSE l_qpp_csr;
609 END IF;
610 IF l_scs_csr%ISOPEN THEN
611 CLOSE l_scs_csr;
612 END IF;
613 END execute_qa_check_list;
614
615
616 END OKC_QA_CHECK_PVT;