[Home] [Help]
PACKAGE BODY: APPS.QA_RESULTS_PUB
Source
1 PACKAGE BODY qa_results_pub AS
2 /* $Header: qltpresb.plb 120.3.12010000.2 2008/11/12 11:49:41 rvalsan ship $ */
3
4
5 g_pkg_name CONSTANT VARCHAR2(30):= 'qa_results_pub';
6 g_message_table mesg_table;
7
8 -- Start of comments
9 --
10 -- API name : insert_row
11 -- Type : Public
12 -- Function : insert_row
13 -- Pre-reqs : None.
14 --
15 -- End of comments
16
17
18 PROCEDURE populate_message_table IS
19
20 BEGIN
21
22 g_message_table(qa_validation_api.not_enabled_error) :=
23 'QA_API_NOT_ENABLED';
24 g_message_table(qa_validation_api.no_values_error) := 'QA_API_NO_VALUES';
25 g_message_table(qa_validation_api.mandatory_error) := 'QA_API_MANDATORY';
26 g_message_table(qa_validation_api.not_revision_controlled_error) :=
27 'QA_API_REVISION_CONTROLLED';
28 g_message_table(qa_validation_api.mandatory_revision_error) :=
29 'QA_API_MANDATORY_REVISION';
30 g_message_table(qa_validation_api.no_values_error) := 'QA_API_NO_VALUES';
31 g_message_table(qa_validation_api.keyflex_error) := 'QA_API_KEYFLEX';
32 g_message_table(qa_validation_api.id_not_found_error) :=
33 'QA_API_ID_NOT_FOUND';
34 g_message_table(qa_validation_api.spec_limit_error) := 'QA_API_SPEC_LIMIT';
35 g_message_table(qa_validation_api.immediate_action_error) :=
36 'QA_API_IMMEDIATE_ACTION';
37 g_message_table(qa_validation_api.lower_limit_error) :=
38 'QA_API_LOWER_LIMIT';
39 g_message_table(qa_validation_api.upper_limit_error) :=
40 'QA_API_UPPER_LIMIT';
41 g_message_table(qa_validation_api.value_not_in_sql_error) :=
42 'QA_API_VALUE_NOT_IN_SQL';
43 g_message_table(qa_validation_api.sql_validation_error) :=
44 'QA_API_SQL_VALIDATION';
45 g_message_table(qa_validation_api.date_conversion_error) :=
46 'QA_API_INVALID_DATE';
47 g_message_table(qa_validation_api.data_type_error) := 'QA_API_DATA_TYPE';
48 g_message_table(qa_validation_api.number_conversion_error) :=
49 'QA_API_INVALID_NUMBER';
50 g_message_table(qa_validation_api.no_data_found_error) :=
51 'QA_API_NO_DATA_FOUND';
52 g_message_table(qa_validation_api.not_locator_controlled_error) :=
53 'QA_API_NOT_LOCATOR_CONTROLLED';
54 g_message_table(qa_validation_api.item_keyflex_error) :=
55 'QA_API_ITEM_KEYFLEX';
56 g_message_table(qa_validation_api.comp_item_keyflex_error) :=
57 'QA_API_COMP_ITEM_KEYFLEX';
58 g_message_table(qa_validation_api.locator_keyflex_error) :=
59 'QA_API_LOCATOR_KEYFLEX';
60 g_message_table(qa_validation_api.comp_locator_keyflex_error) :=
61 'QA_API_COMP_LOCATOR_KEYFLEX';
62 g_message_table(qa_validation_api.invalid_number_error) :=
63 'QA_API_INVALID_NUMBER';
64 g_message_table(qa_validation_api.invalid_date_error) :=
65 'QA_API_INVALID_DATE';
66 g_message_table(qa_validation_api.spec_error) := 'QA_API_SPEC';
67 g_message_table(qa_validation_api.ok) := 'QA_API_NO_ERROR';
68 g_message_table(qa_validation_api.unknown_error) := 'QA_API_UNKNOWN';
69 g_message_table(qa_validation_api.reject_an_entry_error) :=
70 'QA_API_REJECT_AN_ENTRY';
71
72
73 -- Bug 3679762.Initialising the message array for the "missing assign a value target
74 -- column" error message.
75 -- srhariha.Wed Jun 16 06:54:06 PDT 2004
76
77 g_message_table(qa_validation_api.missing_assign_column) :=
78 'QA_MISSING_ASSIGN_COLUMN';
79
80 END populate_message_table;
81
82 -- Added an argument plan_id for the Procedure post_error_messages
83 -- which would be used to get the collection element prompt from
84 -- qa_plan_element_api.get_prompt and which in turn will be used
85 -- in the error messages shown to the user.
86 -- Bug 2910202.suramasw.Wed May 14 23:29:55 PDT 2003.
87
88 PROCEDURE post_error_messages (p_errors IN qa_validation_api.ErrorArray,plan_id NUMBER)
89 IS
90
91 l_message_name VARCHAR2(30);
92 l_char_prompt VARCHAR2(100);
93
94 BEGIN
95
96 FOR i IN p_errors.FIRST .. p_errors.LAST LOOP
97 l_message_name := g_message_table(p_errors(i).error_code);
98 l_char_prompt := qa_plan_element_api.get_prompt(plan_id,p_errors(i).element_id);
99
100 fnd_message.set_name('QA', l_message_name);
101 fnd_message.set_token('CHAR_ID', p_errors(i).element_id);
102 fnd_message.set_token('CHAR_PROMPT',l_char_prompt);
103 fnd_msg_pub.add();
104 END LOOP;
105
106 END post_error_messages;
107
108 --
109 -- 12.1 QWB Usablility improvement
110 -- Added the parameter p_ssqr_operation to make
111 -- sure that the validation is not done againg while
112 -- inserting the data through QWB application
113 --
114 PROCEDURE insert_row (
115 p_api_version IN NUMBER,
116 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
117 p_commit IN VARCHAR2 := FND_API.G_FALSE,
118 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
119 p_plan_id IN NUMBER,
120 p_spec_id IN NUMBER DEFAULT NULL,
121 p_org_id IN NUMBER,
122 p_transaction_number IN NUMBER DEFAULT NULL,
123 p_transaction_id IN NUMBER DEFAULT NULL,
124 p_who_last_updated_by IN NUMBER := fnd_global.user_id,
125 p_who_created_by IN NUMBER := fnd_global.user_id,
126 p_who_last_update_login IN NUMBER := fnd_global.user_id,
127 p_enabled_flag IN NUMBER,
128 x_collection_id IN OUT NOCOPY NUMBER,
129 x_row_elements IN OUT NOCOPY qa_validation_api.ElementsArray,
130 x_return_status OUT NOCOPY VARCHAR2,
131 x_msg_count OUT NOCOPY NUMBER,
132 x_msg_data OUT NOCOPY VARCHAR2,
133 x_occurrence IN OUT NOCOPY NUMBER,
134 x_action_result OUT NOCOPY VARCHAR2,
135 x_message_array OUT NOCOPY qa_validation_api.MessageArray,
136 x_error_array OUT NOCOPY qa_validation_api.ErrorArray,
137 p_txn_header_id IN NUMBER DEFAULT NULL,
138 p_ssqr_operation IN NUMBER DEFAULT NULL,
139 p_last_update_date IN DATE DEFAULT SYSDATE) IS
140
141 l_api_name CONSTANT VARCHAR2(30) := 'insert_row';
142 l_api_version CONSTANT NUMBER := 1.0;
143 l_action_return BOOLEAN;
144 l_error_found BOOLEAN;
145
146
147 BEGIN
148 -- Standard Start of API savepoint
149
150 SAVEPOINT insert_row_pub;
151
152 -- Standard call to check for call compatibility.
153 IF NOT FND_API.Compatible_API_Call (l_api_version,
154 p_api_version,
155 l_api_name,
156 G_PKG_NAME ) THEN
157 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
158 END IF;
159
160 -- Initialize message list if p_init_msg_list is set to TRUE.
161 IF FND_API.to_Boolean( p_init_msg_list ) THEN
162 FND_MSG_PUB.initialize;
163 END IF;
164
165
166 -- Initialize API return status to success
167 x_return_status := FND_API.G_RET_STS_SUCCESS;
168
169 -- Bug 2290747.Added parameter p_txn_header_id to enable
170 -- history plan record when parent plan gets updated
171 -- rponnusa Mon Apr 1 22:25:49 PST 2002
172 -- 12.1 QWB Usability Improvements
173 -- Passing the value for the p_ssqr_operation parameter
174 -- so that the validation is not called while inserting
175 -- rows through the QWB application.
176 --
177 x_error_array := qa_results_api.insert_row(p_plan_id,
178 p_spec_id,
179 p_org_id,
180 p_transaction_number,
181 p_transaction_id,
182 x_collection_id,
183 p_who_last_updated_by,
184 p_who_created_by,
185 p_who_last_update_login,
186 p_enabled_flag,
187 FND_API.To_Boolean(p_commit),
188 l_error_found,
189 x_occurrence,
190 l_action_return,
191 x_message_array,
192 x_row_elements,
193 p_txn_header_id,
194 p_ssqr_operation,
195 p_last_update_date
196 );
197
198 IF (l_error_found = TRUE) THEN
199 x_return_status := FND_API.G_RET_STS_ERROR;
200
201 -- Added the argument p_plan_id.
202 -- Bug 2910202.suramasw.Wed May 14 23:29:55 PDT 2003.
203
204 --
205 -- Bug 7552630
206 -- Making a call to the post_error_messages API in
207 -- qa_ss_results package since that API does appropriate
208 -- handling of the error messages raised by the reject
209 -- an input action which was fixed in bug 5307450
210 --
211 -- post_error_messages(x_error_array,p_plan_id);
212 qa_ss_results.post_error_messages(p_errors => x_error_array,
213 plan_id => p_plan_id);
214 -- Bug 5355933. Do not call commit if above returns error
215 -- saugupta Wed, 26 Jul 2006 03:57:30 -0700 PDT
216 ELSE
217 -- Standard check of p_commit.
218 IF FND_API.To_Boolean( p_commit ) THEN
219 COMMIT;
220 END IF;
221 END IF;
222
223
224 EXCEPTION
225
226 WHEN FND_API.G_EXC_ERROR THEN
227 ROLLBACK TO insert_row_pub;
228 x_return_status := FND_API.G_RET_STS_ERROR ;
229 FND_MSG_PUB.Count_And_Get
230 (p_count => x_msg_count,
231 p_data => x_msg_data
232 );
233
234 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
235 ROLLBACK TO insert_row_pub;
236 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
237 FND_MSG_PUB.Count_And_Get
238 (p_count => x_msg_count,
239 p_data => x_msg_data
240 );
241
242 WHEN OTHERS THEN
243 ROLLBACK TO insert_row_pub;
244 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
245 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
246 FND_MSG_PUB.Add_Exc_Msg
247 (G_PKG_NAME,
248 l_api_name
249 );
250 END IF;
251 FND_MSG_PUB.Count_And_Get
252 (p_count => x_msg_count,
253 p_data => x_msg_data
254 );
255
256 END insert_row;
257
258
259 -- anagarwa Sep 30 2003
260 -- SSQR project relies upon txn_header_id to enable and fire actions
261 -- update_row is hence modified to take in txn_header_id
262 --
263 -- 12.1 QWB Usability Improvements
264 -- Added a new parameter p_ssqr_operation
265 -- so that the validation is not called while updating
266 -- rows through the QWB application.
267 --
268 PROCEDURE update_row (
269 p_api_version IN NUMBER,
270 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
271 p_commit IN VARCHAR2 := FND_API.G_FALSE,
272 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
273 p_plan_id IN NUMBER,
274 p_spec_id IN NUMBER DEFAULT NULL,
275 p_org_id IN NUMBER,
276 p_transaction_number IN NUMBER DEFAULT NULL,
277 p_transaction_id IN NUMBER DEFAULT NULL,
278 p_who_last_updated_by IN NUMBER := fnd_global.user_id,
279 p_who_created_by IN NUMBER := fnd_global.user_id,
280 p_who_last_update_login IN NUMBER := fnd_global.user_id,
281 p_enabled_flag IN NUMBER,
282 p_collection_id IN NUMBER,
283 p_occurrence IN NUMBER,
284 x_row_elements IN OUT NOCOPY qa_validation_api.ElementsArray,
285 x_return_status OUT NOCOPY VARCHAR2,
286 x_msg_count OUT NOCOPY NUMBER,
287 x_msg_data OUT NOCOPY VARCHAR2,
288 x_action_result OUT NOCOPY VARCHAR2,
289 x_message_array OUT NOCOPY qa_validation_api.MessageArray,
290 x_error_array OUT NOCOPY qa_validation_api.ErrorArray,
291 p_txn_header_id IN NUMBER DEFAULT NULL,
292 p_ssqr_operation IN NUMBER DEFAULT NULL,
293 p_last_update_date IN DATE DEFAULT SYSDATE) IS
294
295 l_api_name CONSTANT VARCHAR2(30) := 'update_row';
296 l_api_version CONSTANT NUMBER := 1.0;
297 l_action_return BOOLEAN;
298 l_error_found BOOLEAN;
299
300
301 BEGIN
302
303 -- Standard Start of API savepoint
304
305 SAVEPOINT update_row_pub;
306
307 -- Standard call to check for call compatibility.
308 IF NOT FND_API.Compatible_API_Call (l_api_version,
309 p_api_version,
310 l_api_name,
311 G_PKG_NAME ) THEN
312 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
313 END IF;
314
315 -- Initialize message list if p_init_msg_list is set to TRUE.
316 IF FND_API.to_Boolean( p_init_msg_list ) THEN
317 FND_MSG_PUB.initialize;
318 END IF;
319
320
321 -- Initialize API return status to success
322 x_return_status := FND_API.G_RET_STS_SUCCESS;
323
324 --
325 -- 12.1 QWB Usability Improvements
326 -- Passing the value for the p_ssqr_operation parameter
327 -- so that the validation is not called while updating
328 -- rows through the QWB application.
329 --
330 x_error_array := qa_results_api.update_row(p_plan_id,
331 p_spec_id,
332 p_org_id,
333 p_transaction_number,
334 p_transaction_id,
335 p_collection_id,
336 p_who_last_updated_by,
337 p_who_created_by,
338 p_who_last_update_login,
339 p_enabled_flag,
340 FND_API.To_Boolean(p_commit),
341 l_error_found,
342 p_occurrence,
343 l_action_return,
344 x_message_array,
345 x_row_elements,
346 p_txn_header_id,
347 p_ssqr_operation,
348 p_last_update_date
349 );
350
351
352 IF (l_error_found = TRUE) THEN
353 x_return_status := FND_API.G_RET_STS_ERROR;
354 END IF;
355
356 -- Standard check of p_commit.
357 IF FND_API.To_Boolean( p_commit ) THEN
358 COMMIT;
359 END IF;
360
361
362 EXCEPTION
363
364 WHEN FND_API.G_EXC_ERROR THEN
365 ROLLBACK TO update_row_pub;
366 x_return_status := FND_API.G_RET_STS_ERROR ;
367 FND_MSG_PUB.Count_And_Get
368 (p_count => x_msg_count,
369 p_data => x_msg_data
370 );
371
372 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
373 ROLLBACK TO update_row_pub;
374 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
375 FND_MSG_PUB.Count_And_Get
376 (p_count => x_msg_count,
377 p_data => x_msg_data
378 );
379
380 WHEN OTHERS THEN
381 ROLLBACK TO update_row_pub;
382 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
383 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
384 FND_MSG_PUB.Add_Exc_Msg
385 (G_PKG_NAME,
386 l_api_name
387 );
388 END IF;
389 FND_MSG_PUB.Count_And_Get
390 (p_count => x_msg_count,
391 p_data => x_msg_data
392 );
393
394 END update_row;
395
396
397 PROCEDURE enable_and_fire_action (
398 p_api_version IN NUMBER,
399 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
400 p_commit IN VARCHAR2 := FND_API.G_FALSE,
401 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
402 p_collection_id IN NUMBER,
403 x_return_status OUT NOCOPY VARCHAR2,
404 x_msg_count OUT NOCOPY NUMBER,
405 x_msg_data OUT NOCOPY VARCHAR2) IS
406
407 l_api_name CONSTANT VARCHAR2(30) := 'enable_and_fire_action';
408 l_api_version CONSTANT NUMBER := 1.0;
409 l_error_found BOOLEAN;
410
411
412 BEGIN
413
414 -- Standard Start of API savepoint
415
416 SAVEPOINT enable_and_fire_actions_pub;
417
418 -- Standard call to check for call compatibility.
419 IF NOT FND_API.Compatible_API_Call (l_api_version,
420 p_api_version,
421 l_api_name,
422 G_PKG_NAME ) THEN
423 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
424 END IF;
425
426 -- Initialize message list if p_init_msg_list is set to TRUE.
427 IF FND_API.to_Boolean( p_init_msg_list ) THEN
428 FND_MSG_PUB.initialize;
429 END IF;
430
431
432 -- Initialize API return status to error
433 x_return_status := FND_API.G_RET_STS_ERROR;
434
435 qa_results_api.enable_and_fire_action(p_collection_id);
436
437 x_return_status := FND_API.G_RET_STS_SUCCESS;
438
439 -- Standard check of p_commit.
440 IF FND_API.To_Boolean( p_commit ) THEN
441 COMMIT;
442 END IF;
443
444
445 EXCEPTION
446
447 WHEN FND_API.G_EXC_ERROR THEN
448 ROLLBACK TO enable_fire_actions_pub;
449 x_return_status := FND_API.G_RET_STS_ERROR ;
450 FND_MSG_PUB.Count_And_Get
451 (p_count => x_msg_count,
452 p_data => x_msg_data
453 );
454
455 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
456 ROLLBACK TO enable_fire_actions_pub;
457 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
458 FND_MSG_PUB.Count_And_Get
459 (p_count => x_msg_count,
460 p_data => x_msg_data
461 );
462
463 WHEN OTHERS THEN
464 ROLLBACK TO enable_fire_actions_pub;
465 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
466 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
467 FND_MSG_PUB.Add_Exc_Msg
468 (G_PKG_NAME,
469 l_api_name
470 );
471 END IF;
472 FND_MSG_PUB.Count_And_Get
473 (p_count => x_msg_count,
474 p_data => x_msg_data
475 );
476
477 END enable_and_fire_action;
478
479
480 PROCEDURE commit_qa_results (
481 p_api_version IN NUMBER,
482 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
483 p_commit IN VARCHAR2 := FND_API.G_FALSE,
484 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
485 p_collection_id IN NUMBER,
486 x_return_status OUT NOCOPY VARCHAR2,
487 x_msg_count OUT NOCOPY NUMBER,
488 x_msg_data OUT NOCOPY VARCHAR2) IS
489
490 l_api_name CONSTANT VARCHAR2(30) := 'enable_and_fire_actions';
491 l_api_version CONSTANT NUMBER := 1.0;
492 l_error_found BOOLEAN;
493
494
495 BEGIN
496
497 -- Standard Start of API savepoint
498
499 SAVEPOINT commit_qa_results_pub;
500
501 -- Standard call to check for call compatibility.
502 IF NOT FND_API.Compatible_API_Call (l_api_version,
503 p_api_version,
504 l_api_name,
505 G_PKG_NAME ) THEN
506 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
507 END IF;
508
509 -- Initialize message list if p_init_msg_list is set to TRUE.
510 IF FND_API.to_Boolean( p_init_msg_list ) THEN
511 FND_MSG_PUB.initialize;
512 END IF;
513
514
515 -- Initialize API return status to error
516 x_return_status := FND_API.G_RET_STS_ERROR;
517
518 qa_results_api.commit_qa_results(p_collection_id);
519
520 x_return_status := FND_API.G_RET_STS_SUCCESS;
521
522 -- Standard check of p_commit.
523 IF FND_API.To_Boolean( p_commit ) THEN
524 COMMIT;
525 END IF;
526
527
528 EXCEPTION
529
530 WHEN FND_API.G_EXC_ERROR THEN
531 ROLLBACK TO commit_qa_results_pub;
532 x_return_status := FND_API.G_RET_STS_ERROR ;
533 FND_MSG_PUB.Count_And_Get
534 (p_count => x_msg_count,
535 p_data => x_msg_data
536 );
537
538 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
539 ROLLBACK TO qa_results_pub;
540 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
541 FND_MSG_PUB.Count_And_Get
542 (p_count => x_msg_count,
543 p_data => x_msg_data
544 );
545
546 WHEN OTHERS THEN
547 ROLLBACK TO commit_qa_results_pub;
548 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
549 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
550 FND_MSG_PUB.Add_Exc_Msg
551 (G_PKG_NAME,
552 l_api_name
553 );
554 END IF;
555 FND_MSG_PUB.Count_And_Get
556 (p_count => x_msg_count,
557 p_data => x_msg_data
558 );
559
560 END commit_qa_results;
561
562 BEGIN
563
564 populate_message_table;
565
566 END qa_results_pub;
567