[Home] [Help]
PACKAGE BODY: APPS.QA_AUDIT_PKG
Source
1 PACKAGE BODY qa_audit_pkg AS
2 /* $Header: qaauditb.pls 120.1 2005/07/14 03:41 srhariha noship $ */
3
4 -- Globals
5 g_qb_result_columns DBMS_SQL.VARCHAR2_TABLE;
6 g_que_result_columns DBMS_SQL.VARCHAR2_TABLE;
7 g_master_result_columns DBMS_SQL.VARCHAR2_TABLE;
8 g_pkg_name CONSTANT VARCHAR2(30) := 'QA_AUDIT_PKG';
9
10
11 PROCEDURE init_globals(p_audit_bank_plan_id NUMBER,
12 p_audit_master_plan_id NUMBER,
13 p_audit_que_plan_id NUMBER) IS
14
15
16 CURSOR c(x_plan_id NUMBER) IS
17 SELECT char_id,result_column_name
18 FROM qa_plan_chars
19 WHERE plan_id = x_plan_id;
20
21 BEGIN
22
23 -- Bug 4345779. Audits Copy UI project.
24 -- Code Review feedback incorporation. CR Ref 4.9.2
25 -- Initialize global plsql tables.
26 -- srhariha. Tue Jul 12 02:12:17 PDT 2005.
27 g_qb_result_columns.DELETE;
28 g_que_result_columns.DELETE;
29 g_master_result_columns.DELETE;
30
31 -- fill question bank
32 FOR qb_rec IN c(p_audit_bank_plan_id) LOOP
33 g_qb_result_columns(qb_rec.char_id) := qb_rec.result_column_name;
34 END LOOP;
35
36 -- fill audit master
37 FOR qb_rec IN c(p_audit_master_plan_id) LOOP
38 g_master_result_columns(qb_rec.char_id) := qb_rec.result_column_name;
39 END LOOP;
40
41 -- fill questions
42 FOR qb_rec IN c(p_audit_que_plan_id) LOOP
43 g_que_result_columns(qb_rec.char_id) := qb_rec.result_column_name;
44 END LOOP;
45
46 END init_globals;
47
48
49 FUNCTION get_collection_id RETURN NUMBER IS
50
51 CURSOR c1 IS SELECT QA_COLLECTION_ID_S.NEXTVAL FROM DUAL;
52 l_collection_id NUMBER;
53
54 BEGIN
55
56 OPEN C1;
57 FETCH C1 INTO l_collection_id;
58 CLOSE C1;
59
60 return l_collection_id;
61
62 END get_collection_id;
63
64 FUNCTION get_txn_header_id RETURN NUMBER IS
65
66 l_txn_header_id NUMBER;
67 CURSOR c2 IS SELECT MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL FROM DUAL;
68
69 BEGIN
70
71 OPEN C2;
72 FETCH C2 INTO l_txn_header_id;
73 CLOSE C2;
74
75 return l_txn_header_id;
76
77 END get_txn_header_id;
78
79 FUNCTION common_insert_sql
80 RETURN VARCHAR2 IS
81
82
83 l_sql_string VARCHAR2(1000);
84
85 BEGIN
86 l_sql_string := 'INSERT INTO qa_results ( collection_id, ' ||
87 ' occurrence, ' ||
88 ' last_update_date, ' ||
89 ' qa_last_update_date, '||
90 ' last_updated_by, ' ||
91 ' qa_last_updated_by, ' ||
92 ' creation_date, ' ||
93 ' qa_creation_date, ' ||
94 ' created_by, ' ||
95 ' last_update_login, ' ||
96 ' qa_created_by, ' ||
97 ' status, ' ||
98 ' transaction_number, ' ||
99 ' organization_id, ' ||
100 ' plan_id, ' ||
101 ' txn_header_id, ' ||
102 g_que_result_columns(qa_ss_const.standard_violated) || ', ' ||
103 g_que_result_columns(qa_ss_const.section_violated) || ', ' ||
104 g_que_result_columns(qa_ss_const.audit_area) || ', ' ||
105 g_que_result_columns(qa_ss_const.question_category) || ', ' ||
106 g_que_result_columns(qa_ss_const.question_code) || ', ' ||
107 g_que_result_columns(qa_ss_const.audit_question) || ') ' ||
108 ' SELECT :1, ' ||
109 ' QA_OCCURRENCE_S.NEXTVAL, ' ||
110 ' sysdate, ' ||
111 ' sysdate, ' ||
112 ' fnd_global.user_id, ' ||
113 ' fnd_global.user_id, ' ||
114 ' sysdate, ' ||
115 ' sysdate, ' ||
116 ' fnd_global.user_id, ' ||
117 ' fnd_global.user_id, ' ||
118 ' fnd_global.user_id, ' ||
119 ' 2, ' ||
120 ' -1, ' ||
121 ' :2, ' || -- question and response org_id
122 ' :3, ' || -- questions and resp plan_id
123 ' :4, ' || -- x_txn_header_id,
124 g_qb_result_columns(qa_ss_const.standard_violated) || ', ' ||
125 g_qb_result_columns(qa_ss_const.section_violated) || ', ' ||
126 g_qb_result_columns(qa_ss_const.audit_area) || ', ' ||
127 g_qb_result_columns(qa_ss_const.question_category) || ', ' ||
128 g_qb_result_columns(qa_ss_const.question_code) || ', ' ||
129 g_qb_result_columns(qa_ss_const.audit_question) || ' ' ||
130 ' FROM QA_RESULTS QR ' ||
131 ' WHERE QR.PLAN_ID = :5 ' ||-- qb_plan_id
132 ' AND QR.ORGANIZATION_ID = :6 '; -- qb_org_id
133
134
135
136
137 RETURN l_sql_string;
138
139 END common_insert_sql;
140
141
142 PROCEDURE copy_question_rows(
143 p_audit_bank_plan_id NUMBER,
144 p_audit_bank_org_id NUMBER,
145 p_summary_params qa_audit_pkg.SummaryParamArray ,
146 p_audit_question_plan_id NUMBER,
147 p_audit_question_org_id NUMBER,
148 p_collection_id NUMBER,
149 p_txn_header_id NUMBER) IS
150
151 l_summ_params DBMS_SQL.VARCHAR2_TABLE;
152 l_sql_string VARCHAR2(1000);
153 l_perf_key VARCHAR2(20);
154
155 -- Bug 4345779. Audits Copy UI project.
156 -- Code Review feedback incorporation. CR Ref 4.9.1
157 -- l_api_name must be declared as constant.
158 -- srhariha. Tue Jul 12 02:12:17 PDT 2005.
159
160 l_api_name CONSTANT VARCHAR2(40) := 'COPY_QUESTIONS(..summParam..)';
161 BEGIN
162 if(p_summary_params IS NULL OR
163 p_summary_params.FIRST IS NULL) THEN
164 return;
165 end if;
166
167
168 FOR i IN p_summary_params.FIRST .. p_summary_params.LAST LOOP
169 l_summ_params(i) := p_summary_params(i).standard;
170 END LOOP;
171 l_perf_key := 'AUDIT_STANDARD';
172 qa_performance_temp_pkg.purge_and_add_names(l_perf_key,l_summ_params);
173
174
175 l_sql_string := common_insert_sql ||
176 ' AND ' || g_qb_result_columns(qa_ss_const.standard_violated) ||
177 ' IN (SELECT NAME ' ||
178 ' FROM QA_PERFORMANCE_TEMP ' ||
179 ' WHERE KEY = :7) ';
180
181 EXECUTE IMMEDIATE l_sql_string USING p_collection_id,
182 p_audit_question_org_id,
183 p_audit_question_plan_id,
184 p_txn_header_id,
185 p_audit_bank_plan_id,
186 p_audit_bank_org_id,
187 l_perf_key;
188
189 EXCEPTION
190
191 WHEN OTHERS THEN
192 IF ( FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) ) THEN
193 FND_MSG_PUB.Add_Exc_Msg
194 (
195 p_pkg_name => g_pkg_name,
196 p_procedure_name => l_api_name,
197 p_error_text => SUBSTR(SQLERRM,1,240)
198 );
199 END IF;
200
201 IF ( FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level ) THEN
202 FND_LOG.string
203 (
204 FND_LOG.level_procedure,
205 g_pkg_name || '.' || l_api_name,
206 'EXITING PROCEDURE: ERROR'
207 );
208 END IF;
209
210 RAISE;
211 END copy_question_rows;
212
213
214 PROCEDURE copy_question_rows(
215 p_audit_bank_plan_id NUMBER,
216 p_audit_bank_org_id NUMBER,
217 p_cat_summary_params qa_audit_pkg.CatSummaryParamArray ,
218 p_audit_question_plan_id NUMBER,
219 p_audit_question_org_id NUMBER,
220 p_collection_id NUMBER,
221 p_txn_header_id NUMBER) IS
222
223 l_summ_param DBMS_SQL.VARCHAR2_TABLE;
224 l_sql_string VARCHAR2(1000);
225
226 -- Bug 4345779. Audits Copy UI project.
227 -- Code Review feedback incorporation. CR Ref 4.9.1
228 -- l_api_name must be declared as constant.
229 -- srhariha. Tue Jul 12 02:12:17 PDT 2005.
230
231 l_api_name CONSTANT VARCHAR2(40) := 'COPY_QUESTIONS(..CatsummParam..)';
232 BEGIN
233 if(p_cat_summary_params IS NULL OR
234 p_cat_summary_params.FIRST IS NULL) THEN
235 return;
236 end if;
237
238
239 FOR i IN p_cat_summary_params.FIRST .. p_cat_summary_params.LAST LOOP
240
241 l_sql_string := common_insert_sql ||
242 ' AND ' || g_qb_result_columns(qa_ss_const.standard_violated) || ' = :7 '||
243 ' AND ' || g_qb_result_columns(qa_ss_const.section_violated) || ' = :8 '||
244 ' AND ' || g_qb_result_columns(qa_ss_const.audit_area) || ' = :9 '||
245 ' AND ' || g_qb_result_columns(qa_ss_const.question_category) || ' = :10 ';
246
247 EXECUTE IMMEDIATE l_sql_string USING p_collection_id,
248 p_audit_question_org_id,
249 p_audit_question_plan_id,
250 p_txn_header_id,
251 p_audit_bank_plan_id,
252 p_audit_bank_org_id,
253 p_cat_summary_params(i).standard,
254 p_cat_summary_params(i).section,
255 p_cat_summary_params(i).area,
256 p_cat_summary_params(i).category;
257
258 END LOOP;
259
260 EXCEPTION
261
262 WHEN OTHERS THEN
263 IF ( FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) ) THEN
264 FND_MSG_PUB.Add_Exc_Msg
265 (
266 p_pkg_name => g_pkg_name,
267 p_procedure_name => l_api_name,
268 p_error_text => SUBSTR(SQLERRM,1,240)
269 );
270 END IF;
271
272 IF ( FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level ) THEN
273 FND_LOG.string
274 (
275 FND_LOG.level_procedure,
276 g_pkg_name || '.' || l_api_name,
277 'EXITING PROCEDURE: ERROR'
278 );
279 END IF;
280
281 RAISE;
282
283 END copy_question_rows;
284
285 PROCEDURE get_audit_master_keys (p_audit_question_plan_id NUMBER,
286 p_audit_question_org_id NUMBER,
287 p_audit_num VARCHAR2,
288 x_plan_id OUT NOCOPY NUMBER,
289 x_collection_id OUT NOCOPY NUMBER,
290 x_occurrence OUT NOCOPY NUMBER) IS
291
292 CURSOR C(x_id NUMBER, x_org_id NUMBER, x_audit_num VARCHAR2) IS
293 SELECT qr.plan_id,
294 qr.collection_id,
295 qr.occurrence
296 FROM qa_results qr, qa_pc_plan_relationship qppr
297 WHERE qr.plan_id = qppr.parent_plan_id
298 AND qppr.child_plan_id = x_id
299 AND qr.organization_id = x_org_id
300 AND (qr.status = 2 OR qr.status IS NULL)
301 AND qr.sequence6 = x_audit_num;
302
303 BEGIN
304
305 OPEN C(p_audit_question_plan_id,p_audit_question_org_id,p_audit_num);
306 FETCH C into x_plan_id,x_collection_id,x_occurrence;
307 CLOSE C;
308
309 END get_audit_master_keys;
310
311
312 FUNCTION get_copied_row_count (p_audit_que_plan_id NUMBER,
313 p_collection_id NUMBER) RETURN NUMBER IS
314
315 CURSOR C IS
316 SELECT count(OCCURRENCE)
317 FROM QA_RESULTS
318 WHERE plan_id = p_audit_que_plan_id
319 AND collection_id = p_collection_id;
320
321 l_rows NUMBER;
322
323 BEGIN
324 OPEN C;
325 FETCH C INTO l_rows;
326 CLOSE C;
327
328 return l_rows;
329
330 END get_copied_row_count;
331
332 PROCEDURE copy_questions(
333 p_audit_bank_plan_id NUMBER,
334 p_audit_bank_org_id NUMBER,
335 p_summary_params qa_audit_pkg.SummaryParamArray,
336 p_cat_summary_params qa_audit_pkg.CatSummaryParamArray,
337 p_audit_question_plan_id NUMBER,
338 p_audit_question_org_id NUMBER,
339 p_audit_num VARCHAR2,
340 x_count OUT NOCOPY NUMBER,
341 x_msg_count OUT NOCOPY NUMBER,
342 x_msg_data OUT NOCOPY VARCHAR2,
343 x_return_status OUT NOCOPY VARCHAR2) IS
344
345
346 l_collection_id NUMBER;
347 l_txn_header_id NUMBER;
348 l_master_plan_id NUMBER;
349 l_master_collection_id NUMBER;
350 l_master_occurrence NUMBER;
351 l_ret_status VARCHAR2(10);
352 -- Bug 4345779. Audits Copy UI project.
353 -- Code Review feedback incorporation. CR Ref 4.9.1
354 -- l_api_name must be declared as constant.
355 -- srhariha. Tue Jul 12 02:12:17 PDT 2005.
356
357 l_api_name CONSTANT VARCHAR2(40) := 'COPY_QUESTIONS()';
358
359
360 BEGIN
361
362
363 SAVEPOINT copy_questions_SP;
364
365 x_return_status := FND_API.G_RET_STS_SUCCESS;
366
367
368 -- (1) verify whether user has create privillege on audit questions plan
369
370 l_ret_status := qa_web_txn_api.allowed_for_plan (
371 p_function_name => 'QA_RESULTS_ENTER',
372 p_plan_id => p_audit_question_plan_id);
373
374 if(l_ret_status = 'F') then
375 x_return_status := FND_API.G_RET_STS_ERROR;
376 return;
377 end if;
378
379
380 -- create id's
381 l_collection_id := get_collection_id;
382 l_txn_header_id := get_txn_header_id;
383 -- get master details
384 get_audit_master_keys(p_audit_question_plan_id => p_audit_question_plan_id,
385 p_audit_question_org_id => p_audit_question_org_id,
386 p_audit_num => p_audit_num,
387 x_plan_id => l_master_plan_id,
388 x_collection_id => l_master_collection_id,
389 x_occurrence => l_master_occurrence);
390
391
392 -- init globals
393 init_globals(p_audit_bank_plan_id => p_audit_bank_plan_id,
394 p_audit_master_plan_id => l_master_plan_id,
395 p_audit_que_plan_id => p_audit_question_plan_id);
396
397
398 -- (2) copy questions from qb to questions plan
399
400 if(p_summary_params is not null) then
401 copy_question_rows(p_audit_bank_plan_id => p_audit_bank_plan_id,
402 p_audit_bank_org_id => p_audit_bank_org_id,
403 p_summary_params => p_summary_params,
404 p_audit_question_plan_id => p_audit_question_plan_id,
405 p_audit_question_org_id => p_audit_question_org_id,
406 p_collection_id => l_collection_id,
407 p_txn_header_id => l_txn_header_id);
408 end if;
409
410
411 if(p_cat_summary_params is not null) then
412 copy_question_rows(p_audit_bank_plan_id => p_audit_bank_plan_id,
413 p_audit_bank_org_id => p_audit_bank_org_id,
414 p_cat_summary_params => p_cat_summary_params,
415 p_audit_question_plan_id => p_audit_question_plan_id,
416 p_audit_question_org_id => p_audit_question_org_id,
417 p_collection_id => l_collection_id,
418 p_txn_header_id => l_txn_header_id);
419 end if;
420
421 -- Bug 4345779. Audits Copy UI project.
422 -- Code Review feedback incorporation. CR Ref 4.9.5, 4.9.6 and 4.9.7
423 -- Modularization. Moved the following procedures to parent child pkg.
424 -- . create_relationship
425 -- . copy_from_parent
426 -- . create_history
427 -- srhariha. Tue Jul 12 02:12:17 PDT 2005.
428
429 -- (3) create entry in qa_pc_results_relationship
430 qa_parent_child_pkg.create_relationship_for_coll
431 (p_parent_plan_id => l_master_plan_id,
432 p_parent_collection_id => l_master_collection_id,
433 p_parent_occurrence => l_master_occurrence,
434 p_child_plan_id => p_audit_question_plan_id,
435 p_child_collection_id => l_collection_id,
436 p_org_id => p_audit_question_org_id);
437
438
439 -- (4) copy elements from audit master
440 qa_parent_child_pkg.copy_from_parent_for_coll
441 (p_parent_plan_id => l_master_plan_id,
442 p_parent_collection_id => l_master_collection_id,
443 p_parent_occurrence => l_master_occurrence,
444 p_child_plan_id => p_audit_question_plan_id,
445 p_child_collection_id => l_collection_id,
446 p_org_id => p_audit_question_org_id);
447
448
449 -- (5) create history for audit questions
450 qa_parent_child_pkg.create_history_for_coll
451 ( p_plan_id => p_audit_question_plan_id,
452 p_collection_id => l_collection_id,
453 p_org_id => p_audit_question_org_id,
454 p_txn_header_id => l_txn_header_id);
455 -- (6) return number of rows copied
456 x_count := get_copied_row_count(p_audit_question_plan_id,l_collection_id);
457
458
459
460
461 -- Standard call to get message count and if count is 1, get message info.
462 FND_MSG_PUB.Count_And_Get
463 (
464 p_count => x_msg_count,
465 p_data => x_msg_data
466 );
467
468 IF ( FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level ) THEN
469 FND_LOG.string
470 (
471 FND_LOG.level_procedure,
472 g_pkg_name || '.' || l_api_name,
473 'EXITING PROCEDURE: SUCCESS'
474 );
475 END IF;
476
477 EXCEPTION
478
479 WHEN OTHERS THEN
480 ROLLBACK TO copy_questions_SP;
481 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
482 IF ( FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) ) THEN
483 FND_MSG_PUB.Add_Exc_Msg
484 (
485 p_pkg_name => g_pkg_name,
486 p_procedure_name => l_api_name,
487 p_error_text => SUBSTR(SQLERRM,1,240)
488 );
489 END IF;
490
491 FND_MSG_PUB.Count_And_Get
492 (
493 p_count => x_msg_count,
494 p_data => x_msg_data
495 );
496
497 IF ( FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level ) THEN
498 FND_LOG.string
499 (
500 FND_LOG.level_procedure,
501 g_pkg_name || '.' || l_api_name,
502 'EXITING PROCEDURE: ERROR'
503 );
504 END IF;
505
506 END copy_questions;
507
508
509 END qa_audit_pkg;