[Home] [Help]
PACKAGE BODY: APPS.GMD_QC_TESTS_PUB
Source
1 PACKAGE BODY gmd_qc_tests_pub AS
2 /* $Header: GMDPTSTB.pls 115.13 2004/05/05 09:51:17 rboddu noship $
3 *****************************************************************
4 * *
5 * Package GMD_QC_TESTS_PUB *
6 * *
7 * Contents CREATE_TESTS *
8 * DELETE_TEST_HEADERS *
9 * DELETE_TEST_VALUES *
10 * DELETE_CUSTOMER_TESTS *
11 * *
12 * Use This is the public layer for the QC TESTS API *
13 * *
14 * History *
15 * Written by H Verdding, OPM Development (EMEA) *
16 * *
17 * HVerddin B2711643: Added call to set user_context *
18 * *
19 * *
20 *****************************************************************
21 */
22
23 /* Global variables */
24
25 G_PKG_NAME CONSTANT VARCHAR2(30):='GMD_QC_TESTS_PUB';
26
27 G_PROCESS_TESTS BOOLEAN := FALSE;
28 G_PROCESS_VALUES BOOLEAN := FALSE;
29 G_PROCESS_CUSTOMERS BOOLEAN := FALSE;
30
31 /* Private Routines */
32
33 PROCEDURE VALIDATE_INPUT_PARAMS
34 (
35 p_qc_cust_tests_tbl IN GMD_QC_TESTS_PUB.qc_cust_tests_tbl,
36 p_qc_test_values_tbl IN GMD_QC_TESTS_PUB.qc_test_values_tbl
37
38 )
39 IS
40
41 BEGIN
42
43 IF p_qc_test_values_tbl.COUNT <> 0 THEN
44 G_PROCESS_VALUES := TRUE;
45 ELSE /* BUG 3506233 - Added following ELSE condition */
46 G_PROCESS_VALUES := FALSE;
47 END IF;
48
49 IF p_qc_cust_tests_tbl.COUNT <> 0 THEN
50 G_PROCESS_CUSTOMERS := TRUE;
51 ELSE /* BUG 3506233 - Added following ELSE condition */
52 G_PROCESS_CUSTOMERS := FALSE;
53 END IF;
54
55
56
57 END VALIDATE_INPUT_PARAMS;
58
59
60
61 PROCEDURE CREATE_TESTS
62 ( p_api_version IN NUMBER
63 , p_init_msg_list IN VARCHAR2
64 , p_commit IN VARCHAR2
65 , p_validation_level IN NUMBER
66 , p_qc_tests_rec IN GMD_QC_TESTS%ROWTYPE
67 , p_qc_test_values_tbl IN GMD_QC_TESTS_PUB.qc_test_values_tbl
68 , p_qc_cust_tests_tbl IN GMD_QC_TESTS_PUB.qc_cust_tests_tbl
69 , p_user_name IN VARCHAR2
70 , x_qc_tests_rec OUT NOCOPY GMD_QC_TESTS%ROWTYPE
71 , x_qc_test_values_tbl OUT NOCOPY GMD_QC_TESTS_PUB.qc_test_values_tbl
72 , x_qc_cust_tests_tbl OUT NOCOPY GMD_QC_TESTS_PUB.qc_cust_tests_tbl
73 , x_return_status OUT NOCOPY VARCHAR2
74 , x_msg_count OUT NOCOPY NUMBER
75 , x_msg_data OUT NOCOPY VARCHAR2
76 )
77 IS
78 l_api_name CONSTANT VARCHAR2 (30) := 'CREATE_TESTS';
79 l_api_version CONSTANT NUMBER := 1.0;
80 l_msg_count NUMBER :=0;
81 l_msg_data VARCHAR2(2000);
82 l_return_status VARCHAR2(1):=FND_API.G_RET_STS_SUCCESS;
83 l_qc_tests_rec GMD_QC_TESTS%ROWTYPE;
84 l_qc_tests_rec_in GMD_QC_TESTS%ROWTYPE;
85 l_qc_cust_tests_rec GMD_CUSTOMER_TESTS%ROWTYPE;
86 l_qc_test_values_rec_in GMD_QC_TEST_VALUES%ROWTYPE;
87 l_qc_test_values_rec GMD_QC_TEST_VALUES%ROWTYPE;
88 l_qc_test_values_tbl GMD_QC_TESTS_PUB.qc_test_values_tbl;
89 l_qc_cust_tests_tbl GMD_QC_TESTS_PUB.qc_cust_tests_tbl;
90 l_rowid ROWID;
91 l_user_id NUMBER(15);
92
93 BEGIN
94
95
96 -- Standard Start OF API savepoint
97
98 SAVEPOINT Create_Tests;
99
100 /* Standard call to check for call compatibility. */
101
102 IF NOT FND_API.Compatible_API_CALL
103 (l_api_version , p_api_version , l_api_name , G_PKG_NAME)
104 THEN
105 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
106 END IF;
107
108 /* Initialize message list if p_int_msg_list is set TRUE. */
109 IF FND_API.to_boolean(p_init_msg_list)
110 THEN
111 FND_MSG_PUB.Initialize;
112 END IF;
113
114 -- Initialize API return Parameters
115
116 l_return_status := FND_API.G_RET_STS_SUCCESS;
117
118 -- Validate User Name Parameter
119
120 GMA_GLOBAL_GRP.Get_Who ( p_user_name => p_user_name
121 ,x_user_id => l_user_id);
122
123 IF NVL(l_user_id, -1) < 0
124 THEN
125 GMD_API_PUB.Log_Message('GMD_INVALID_USER_NAME','l_user_name', p_user_name);
126 RAISE FND_API.G_EXC_ERROR;
127 ELSE
128 -- Added below for BUG 2711643. Hverddin
129 GMD_API_PUB.SET_USER_CONTEXT(p_user_id => l_user_id,
130 x_return_status => l_return_status);
131
132 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
133 RAISE FND_API.G_EXC_ERROR;
134 END IF;
135
136 END IF;
137
138 IF ( p_qc_tests_rec.test_code IS NOT NULL ) THEN
139 G_PROCESS_TESTS := TRUE;
140 END IF;
141
142 /* Try And detemine Exactly Attributes Required For Creation */
143
144 VALIDATE_INPUT_PARAMS
145 (
146 p_qc_cust_tests_tbl => p_qc_cust_tests_tbl,
147 p_qc_test_values_tbl => p_qc_test_values_tbl
148 );
149
150
151 IF ( NOT G_PROCESS_TESTS ) AND ( NOT G_PROCESS_VALUES )
152 AND ( NOT G_PROCESS_CUSTOMERS) THEN
153
154 -- Raise Error No Validate Parameters Defined
155 GMD_API_PUB.LOG_MESSAGE('GMD_API_NO_ACTION_REQUIRED');
156 RAISE FND_API.G_EXC_ERROR;
157 END IF;
158
159
160 -- Process Test Record If Present
161
162 IF G_PROCESS_TESTS THEN
163
164 -- test record is populated - Now Process This Record
165
166 l_qc_tests_rec_in := p_qc_tests_rec;
167
168 -- Adding the following validation to check if the test group order passed is unique or not.
169 -- Ravi Boddu Test Groups Enhancement Bug no: 3447472
170 IF (p_qc_tests_rec.test_class IS NOT NULL and p_qc_tests_rec.test_group_order IS NOT NULL) THEN
171 IF gmd_qc_tests_grp.test_group_order_exist(
172 p_init_msg_list,
173 p_qc_tests_rec.test_class,
174 p_qc_tests_rec.test_group_order) THEN
175 GMD_API_PUB.log_message('SY_WFDUPLICATE');
176 RAISE FND_API.G_EXC_ERROR;
177 END IF;
178 END IF;
179
180 GMD_QC_TESTS_GRP.VALIDATE_BEFORE_INSERT(
181 p_gmd_qc_tests_rec => l_qc_tests_rec_in,
182 x_gmd_qc_tests_rec => l_qc_tests_rec,
183 x_return_status => l_return_status,
184 x_message_data => l_msg_data);
185
186 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
187 RAISE FND_API.G_EXC_ERROR;
188 END IF;
189
190
191 -- Insert Test Record Into Table.
192 -- Set the Who column definitions
193 -- And set test_id to NULL;
194 l_qc_tests_rec.created_by := l_user_id;
195 l_qc_tests_rec.last_updated_by := l_user_id;
196 l_qc_tests_rec.test_id := NULL;
197
198 IF NOT GMD_QC_TESTS_PVT.INSERT_ROW(
199 p_qc_tests_rec => l_qc_tests_rec) THEN
200 RAISE FND_API.G_EXC_ERROR;
201 END IF;
202
203 -- Call Validate After Insert
204
205 GMD_QC_TESTS_GRP.PROCESS_AFTER_INSERT(
206 p_init_msg_list => p_init_msg_list,
207 p_gmd_qc_tests_rec => l_qc_tests_rec,
208 x_return_status => x_return_status,
209 x_message_data => x_msg_data
210 );
211
212 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
213 RAISE FND_API.G_EXC_ERROR;
214 END IF;
215
216 END IF;
217
218 /* Process Values Tbl If Present */
219
220 IF G_PROCESS_VALUES THEN
221
222 -- First Check If We have Processed A Test Record
223
224 IF NOT G_PROCESS_TESTS THEN
225 -- Only Processing Values
226
227 -- Get The Test Record For The Values Specified
228 IF ( p_qc_test_values_tbl(1).test_id IS NULL ) THEN
229 GMD_API_PUB.log_message('GMD_TEST_ID_CODE_NULL');
230 RAISE FND_API.G_EXC_ERROR;
231 END IF;
232
233 l_qc_tests_rec_in.test_id := p_qc_test_values_tbl(1).test_id;
234
235 IF NOT GMD_QC_TESTS_PVT.fetch_row
236 ( p_gmd_qc_tests => l_qc_tests_rec_in,
237 x_gmd_qc_tests => l_qc_tests_rec) THEN
238 RAISE FND_API.G_EXC_ERROR;
239 END IF;
240
241 END IF;
242
243 -- Validate Test Type For Test Record
244
245 IF l_qc_tests_rec.test_type in ('U','N','E') THEN
246 -- GIVE AN ERROR No Test Values Allowed For These Test Types
247 GMD_API_PUB.log_message('GMD_INVALID_TEST_TYPE',
248 'TEST_TYPE',l_qc_tests_rec.test_type); /* Bug 350233 - Use of rec variable rather than l_test_type local variable*/
249 RAISE FND_API.G_EXC_ERROR;
250 END IF;
251
252
253 FOR i in 1..p_qc_test_values_tbl.COUNT LOOP
254
255 l_qc_test_values_rec_in:= p_qc_test_values_tbl(i);
256
257 IF G_PROCESS_TESTS THEN
258 -- Assign the test_id to the Value record
259 l_qc_test_values_rec_in.test_id := l_qc_tests_rec.test_id;
260 END IF;
261
262 IF l_qc_test_values_rec_in.test_id IS NULL THEN
263 GMD_API_PUB.log_message('GMD_TEST_ID_CODE_NULL');
264 RAISE FND_API.G_EXC_ERROR;
265 END IF;
266
267 -- Validate that the test_id's are all the same, we can only
268 -- process values for the same test.
269
270 IF l_qc_test_values_rec_in.test_id <> l_qc_tests_rec.test_id THEN
271 -- Set error message
272 GMD_API_PUB.log_message('GMD_INVALID_VALUES_TEST', 'TEST_CODE',
273 l_qc_tests_rec.test_code );
274 RAISE FND_API.G_EXC_ERROR;
275 END IF;
276
277
278 -- Validate Values definition
279
280 GMD_QC_TEST_VALUES_GRP.VALIDATE_BEFORE_INSERT(
281 p_qc_test_values_rec => l_qc_test_values_rec_in,
282 x_qc_test_values_rec => l_qc_test_values_rec,
283 x_return_status => l_return_status,
284 x_message_data => l_msg_data
285 );
286
287 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
288 RAISE FND_API.G_EXC_ERROR;
289 END IF;
290
291 -- Insert Values PVT Routine.
292
293 -- Set the Who column definitions
294 l_qc_test_values_rec.created_by := l_user_id;
295 l_qc_test_values_rec.last_updated_by := l_user_id;
296 l_qc_test_values_rec.test_value_id := NULL;
297
298 IF NOT GMD_QC_TEST_VALUES_PVT.INSERT_ROW(
299 p_qc_test_values_rec => l_qc_test_values_rec) THEN
300 RAISE FND_API.G_EXC_ERROR;
301 END IF;
302
303 -- set Return Parameter Tbl
304
305 l_qc_test_values_tbl(i) := l_qc_test_values_rec;
306
307 END LOOP;
308
309 -- Now we have valid Test Record Call Validate after Insert.
310
311 GMD_QC_TEST_VALUES_GRP.VALIDATE_AFTER_INSERT_ALL(
312 p_gmd_qc_tests_rec => l_qc_tests_rec,
313 x_gmd_qc_tests_rec => l_qc_tests_rec_in,
314 x_return_status => l_return_status,
315 x_message_data => l_msg_data);
316
317 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
318 RAISE FND_API.G_EXC_ERROR;
319 END IF;
320
321 END IF;
322
323 /* Process Customer Values Tbl If Present */
324
325 IF G_PROCESS_CUSTOMERS THEN
326 -- First Check If We have Processed A Test Record
327
328 IF NOT G_PROCESS_TESTS THEN
329
330 -- Get The Test Record For The Values Specified
331 IF ( p_qc_cust_tests_tbl(1).test_id IS NULL ) THEN
332 -- Error Message No Test Id Specified for Values Rec
333 GMD_API_PUB.log_message('GMD_TEST_ID_CODE_NULL');
334 RAISE FND_API.G_EXC_ERROR;
335 END IF;
336
337 l_qc_tests_rec_in.test_id := p_qc_cust_tests_tbl(1).test_id;
338
339 IF NOT GMD_QC_TESTS_PVT.fetch_row
340 (
341 p_gmd_qc_tests => l_qc_tests_rec_in,
342 x_gmd_qc_tests => l_qc_tests_rec
343 ) THEN
344
345 RAISE FND_API.G_EXC_ERROR;
346
347 END IF;
348
349 END IF;
350
351 FOR i in 1..p_qc_cust_tests_tbl.COUNT LOOP
352
353 l_qc_cust_tests_rec := p_qc_cust_tests_tbl(i);
354
355 IF ( l_qc_cust_tests_rec.cust_id IS NULL ) THEN
356 GMD_API_PUB.log_message('GMD_API_CUST_ID_NULL');
357 RAISE FND_API.G_EXC_ERROR;
358 END IF;
359
360 IF G_PROCESS_TESTS THEN
361 l_qc_cust_tests_rec.test_id := l_qc_tests_rec.test_id;
362 END IF;
363
364 IF l_qc_cust_tests_rec.test_id IS NULL THEN
365 GMD_API_PUB.log_message('GMD_TEST_ID_CODE_NULL');
366 RAISE FND_API.G_EXC_ERROR;
367 END IF;
368
369 -- Validate that the test_id's are all the same,
370 -- we can only update values for the same test.
371
372 IF l_qc_cust_tests_rec.test_id <> l_qc_tests_rec.test_id THEN
373 GMD_API_PUB.log_message('GMD_INVALID_VALUES_TEST', 'TEST_CODE',
374 l_qc_tests_rec.test_code );
375 RAISE FND_API.G_EXC_ERROR;
376
377 END IF;
378
379 -- Validate Values definition
380
381 GMD_CUSTOMER_TESTS_GRP.VALIDATE_BEFORE_INSERT(
382 p_init_msg_list => p_init_msg_list,
383 p_customer_tests_rec => l_qc_cust_tests_rec,
384 x_return_status => l_return_status,
385 x_message_data => l_msg_data);
386
387
388 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
389 RAISE FND_API.G_EXC_ERROR;
390 END IF;
391
392 -- Insert Values PVT Routine.
393 -- Set the Who column definitions
394 l_qc_cust_tests_rec.created_by := l_user_id;
395 l_qc_cust_tests_rec.last_updated_by := l_user_id;
396
397 IF NOT GMD_CUSTOMER_TESTS_PVT.INSERT_ROW(
398 p_customer_tests_rec => l_qc_cust_tests_rec ) THEN
399 RAISE FND_API.G_EXC_ERROR;
400 END IF;
401
402 -- set Return Paremeter Tbl
403
404 l_qc_cust_tests_tbl(i) := l_qc_cust_tests_rec;
405
406 END LOOP;
407
408 END IF;
409
410 -- Standard Check of p_commit.
411 IF FND_API.to_boolean(p_commit)
412 THEN
413 COMMIT WORK;
414 END IF;
415
416
417 x_return_status := l_return_status;
418 x_qc_tests_rec := l_qc_tests_rec;
419 x_qc_test_values_tbl := l_qc_test_values_tbl;
420 x_qc_cust_tests_tbl := l_qc_cust_tests_tbl;
421
422 EXCEPTION
423 WHEN FND_API.G_EXC_ERROR THEN
424 ROLLBACK TO Create_Tests;
425 x_return_status := FND_API.G_RET_STS_ERROR;
426 FND_MSG_PUB.Count_AND_GET ( p_encoded => FND_API.G_FALSE
427 , p_count => x_msg_count
428 , p_data => x_msg_data
429 );
430
431 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
432 ROLLBACK TO Create_Tests;
433 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
434 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
435 , p_count => x_msg_count
436 , p_data => x_msg_data
437 );
438
439
440
441 WHEN OTHERS THEN
442 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
443 ROLLBACK TO Create_Tests;
444 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
445 , l_api_name
446 );
447
448 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
449 , p_count => x_msg_count
450 , p_data => x_msg_data
451 );
452
453 END CREATE_TESTS;
454
455 PROCEDURE DELETE_TEST_HEADERS
456 ( p_api_version IN NUMBER
457 , p_init_msg_list IN VARCHAR2
458 , p_commit IN VARCHAR2
459 , p_validation_level IN NUMBER
460 , p_qc_tests_rec IN GMD_QC_TESTS%ROWTYPE
461 , p_user_name IN VARCHAR2
462 , x_return_status OUT NOCOPY VARCHAR2
463 , x_msg_count OUT NOCOPY NUMBER
464 , x_msg_data OUT NOCOPY VARCHAR2
465 )
466 IS
467 l_api_name CONSTANT VARCHAR2 (30) := 'DELETE_TEST_HEADERS';
468 l_api_version CONSTANT NUMBER := 1.0;
469 l_msg_count NUMBER :=0;
470 l_msg_data VARCHAR2(2000);
471 l_return_status VARCHAR2(1):=FND_API.G_RET_STS_SUCCESS;
472 l_qc_tests_rec_in GMD_QC_TESTS%ROWTYPE;
473 l_qc_tests_rec GMD_QC_TESTS%ROWTYPE;
474 l_rowid VARCHAR2(10);
475 l_test_id NUMBER(10);
476 l_user_id NUMBER(15);
477
478 BEGIN
479
480
481 -- Standard Start OF API savepoint
482
483 SAVEPOINT DELETE_TEST_HEADERS;
484
485 -- Standard call to check for call compatibility.
486
487 IF NOT FND_API.Compatible_API_CALL
488 (l_api_version , p_api_version , l_api_name , G_PKG_NAME)
489 THEN
490 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
491 END IF;
492
493 -- Initialize message list if p_int_msg_list is set TRUE.
494 IF FND_API.to_boolean(p_init_msg_list)
495 THEN
496 FND_MSG_PUB.Initialize;
497 END IF;
498
499 -- Initialize API return Parameters
500
501 l_return_status := FND_API.G_RET_STS_SUCCESS;
502 l_qc_tests_rec_in := p_qc_tests_rec;
503
504 -- Validate User Name Parameter
505
506 GMA_GLOBAL_GRP.Get_Who ( p_user_name => p_user_name
507 ,x_user_id => l_user_id);
508
509 IF NVL(l_user_id, -1) < 0
510 THEN
511 GMD_API_PUB.Log_Message('GMD_INVALID_USER_NAME','l_user_name', p_user_name);
512 RAISE FND_API.G_EXC_ERROR;
513 ELSE
514
515 -- Added below for BUG 2711643. Hverddin
516 GMD_API_PUB.SET_USER_CONTEXT(p_user_id => l_user_id,
517 x_return_status => l_return_status);
518
519 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
520 RAISE FND_API.G_EXC_ERROR;
521 END IF;
522
523 END IF;
524
525 -- Check Required Fields Present
526
527 IF ( l_qc_tests_rec_in.test_code IS NULL ) AND
528 ( l_qc_tests_rec_in.test_id IS NULL ) THEN
529 GMD_API_PUB.log_message('GMD_TEST_ID_CODE_NULL');
530 RAISE FND_API.G_EXC_ERROR;
531 END IF;
532
533 -- Fetch the Test Header Row.
534
535 IF NOT GMD_QC_TESTS_PVT.fetch_row (
536 p_gmd_qc_tests => l_qc_tests_rec_in,
537 x_gmd_qc_tests => l_qc_tests_rec) THEN
538 RAISE FND_API.G_EXC_ERROR;
539 END IF;
540
541 -- Validate that the Test Header is Not Already Marked For Purge
542
543 IF l_qc_tests_rec.delete_mark = 1 THEN
544 GMD_API_PUB.Log_Message('GMD_RECORD_DELETE_MARKED',
545 'l_table_name', 'GMD_QC_TESTS',
546 'l_column_name', 'TEST_ID',
547 'l_key_value', l_qc_tests_rec.test_id);
548
549 RAISE FND_API.G_EXC_ERROR;
550 END IF;
551
552
553 -- LOCK Header Row
554
555 IF NOT GMD_QC_TESTS_PVT.lock_row
556 ( p_test_id => l_qc_tests_rec.test_id,
557 p_test_code => l_qc_tests_rec.test_code
558 ) THEN
559 GMD_API_PUB.Log_Message('GMD_LOCKING_FAILURE',
560 'l_table_name', 'GMD_QC_TESTS',
561 'l_column_name','TEST_ID',
562 'l_key_value', l_qc_tests_rec.test_id);
563
564 RAISE FND_API.G_EXC_ERROR;
565 ELSE
566
567 -- Mark this record for Purge
568 IF NOT GMD_QC_TESTS_PVT.mark_for_delete(
569 p_test_id => l_qc_tests_rec.test_id,
570 p_test_code => l_qc_tests_rec.test_code,
571 p_last_update_date => SYSDATE,
572 p_last_updated_by => l_user_id,
573 p_last_update_login => l_qc_tests_rec.last_update_login
574 ) THEN
575 GMD_API_PUB.Log_Message('GMD_FAILED_TO_DELETE_ROW',
576 'l_table_name', 'GMD_QC_TESTS',
577 'l_column_name','TEST_ID',
578 'l_key_value', l_qc_tests_rec.test_id);
579
580
581 RAISE FND_API.G_EXC_ERROR;
582
583 END IF;
584 END IF;
585
586 -- Standard Check of p_commit.
587 IF FND_API.to_boolean(p_commit)
588 THEN
589 COMMIT WORK;
590 END IF;
591
592
593 x_return_status := l_return_status;
594
595 EXCEPTION
596 WHEN FND_API.G_EXC_ERROR THEN
597 ROLLBACK TO DELETE_TEST_HEADERS;
598 x_return_status := FND_API.G_RET_STS_ERROR;
599 FND_MSG_PUB.Count_AND_GET ( p_encoded => FND_API.G_FALSE
600 , p_count => x_msg_count
601 , p_data => x_msg_data
602 );
603
604 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
605 ROLLBACK TO DELETE_TEST_HEADERS;
606 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
607 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
608 , p_count => x_msg_count
609 , p_data => x_msg_data
610 );
611
612
613
614 WHEN OTHERS THEN
615 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
616 ROLLBACK TO DELETE_TEST_HEADERS;
617 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
618 , l_api_name
619 );
620
621 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
622 , p_count => x_msg_count
623 , p_data => x_msg_data
624 );
625
626 END DELETE_TEST_HEADERS;
627
628
629 PROCEDURE DELETE_TEST_VALUES
630 ( p_api_version IN NUMBER
631 , p_init_msg_list IN VARCHAR2
632 , p_commit IN VARCHAR2
633 , p_validation_level IN NUMBER
634 , p_qc_test_values_tbl IN GMD_QC_TESTS_PUB.qc_test_values_tbl
635 , x_deleted_rows OUT NOCOPY NUMBER
636 , x_return_status OUT NOCOPY VARCHAR2
637 , x_msg_count OUT NOCOPY NUMBER
638 , x_msg_data OUT NOCOPY VARCHAR2
639 )
640 IS
641 l_api_name CONSTANT VARCHAR2 (30) := 'DELETE_TEST_VALUES';
642 l_api_version CONSTANT NUMBER := 1.0;
643 l_msg_count NUMBER :=0;
644 l_msg_data VARCHAR2(2000);
645 l_return_status VARCHAR2(1):=FND_API.G_RET_STS_SUCCESS;
646 l_qc_tests_rec GMD_QC_TESTS%ROWTYPE;
647 l_qc_tests_rec_in GMD_QC_TESTS%ROWTYPE;
648 l_qc_test_values_rec GMD_QC_TEST_VALUES%ROWTYPE;
649 l_qc_test_values_tbl GMD_QC_TESTS_PUB.qc_test_values_tbl;
650 l_deleted_rows NUMBER(10);
651 l_test_id NUMBER(10);
652
653 BEGIN
654
655
656 -- Standard Start OF API savepoint
657
658 SAVEPOINT DELETE_TEST_VALUES;
659
660 -- Standard call to check for call compatibility.
661
662 IF NOT FND_API.Compatible_API_CALL
663 (l_api_version , p_api_version , l_api_name , G_PKG_NAME)
664 THEN
665 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
666 END IF;
667
668 -- Initialize message list if p_int_msg_list is set TRUE.
669
670 IF FND_API.to_boolean(p_init_msg_list)
671 THEN
672 FND_MSG_PUB.Initialize;
673 END IF;
674
675 -- Initialize API return Parameters
676
677 l_return_status := FND_API.G_RET_STS_SUCCESS;
678
679
680 -- Check That input Table is Populated
681
682 IF p_qc_test_values_tbl.COUNT = 0 THEN
683 GMD_API_PUB.LOG_MESSAGE('GMD_API_NO_ACTION_REQUIRED');
684 RAISE FND_API.G_EXC_ERROR;
685 END IF;
686
687
688 -- Since we can only delete values associated to one test
689 -- Get the test record based on first value record.
690
691 IF p_qc_test_values_tbl(1).test_id IS NULL THEN
692 GMD_API_PUB.log_message('GMD_TEST_ID_CODE_NULL');
693 RAISE FND_API.G_EXC_ERROR;
694 ELSE
695 l_qc_tests_rec_in.test_id := p_qc_test_values_tbl(1).test_id;
696 END IF;
697
698 -- Fetch test Header Row.
699
700 IF NOT GMD_QC_TESTS_PVT.fetch_row(
701 p_gmd_qc_tests => l_qc_tests_rec_in,
702 x_gmd_qc_tests => l_qc_tests_rec) THEN
703 RAISE FND_API.G_EXC_ERROR;
704 END IF;
705
706 -- NOW LOCK THIS HEADER ROW !!!!!
707
708 IF NOT GMD_QC_TESTS_PVT.lock_row(
709 p_test_id => l_qc_tests_rec.test_id,
710 p_test_code => l_qc_tests_rec.test_code) THEN
711 GMD_API_PUB.Log_Message('GMD_LOCKING_FAILURE',
712 'l_table_name', 'GMD_QC_TESTS',
713 'l_column_name','TEST_ID',
714 'l_key_value', l_qc_tests_rec.test_id);
715
716 RAISE FND_API.G_EXC_ERROR;
717 END IF;
718
719
720 -- Loop Through Values Tbl Validate input and Process.
721
722 FOR i in 1..p_qc_test_values_tbl.COUNT LOOP
723
724 l_qc_test_values_rec := p_qc_test_values_tbl(i);
725
726 IF l_qc_test_values_rec.test_value_id is NULL THEN
727 -- Raise Error No Validate Parameters Defined
728 GMD_API_PUB.log_message('GMD_TEST_VALUE_ID_REQ');
729 RAISE FND_API.G_EXC_ERROR;
730 END IF;
731
732 IF l_qc_test_values_rec.test_id is NULL THEN
733 -- Raise Error No Validate Parameters Defined
734 GMD_API_PUB.log_message('GMD_TEST_ID_CODE_NULL');
735 RAISE FND_API.G_EXC_ERROR;
736 END IF;
737
738 -- Check that the test_id's being processed are consistant
739 IF l_qc_tests_rec.test_id <> l_qc_test_values_rec.test_id THEN
740 GMD_API_PUB.log_message('GMD_INVALID_VALUES_TEST', 'TEST_CODE',
741 l_qc_tests_rec.test_code );
742 RAISE FND_API.G_EXC_ERROR;
743 END IF;
744
745 -- Call Validate Routine to validate Header
746
747 GMD_QC_TEST_VALUES_GRP.VALIDATE_BEFORE_DELETE(
748 p_test_value_id => l_qc_test_values_rec.test_value_id,
749 x_return_status => l_return_status,
750 x_message_data => l_msg_data);
751
752 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
753 RAISE FND_API.G_EXC_ERROR;
754 END IF;
755
756 -- Lock the Value Row.
757
758 IF NOT GMD_QC_TEST_VALUES_PVT.LOCK_ROW(
759 p_test_value_id => l_qc_test_values_rec.test_value_id) THEN
760 GMD_API_PUB.Log_Message('GMD_LOCKING_FAILURE',
761 'l_table_name', 'GMD_QC_TEST_VALUES',
762 'l_column_name','TEST_VALUE_ID',
763 'l_key_value', l_qc_test_values_rec.test_value_id);
764
765 RAISE FND_API.G_EXC_ERROR;
766 END IF;
767
768
769 -- Delete the Value Row.
770
771 IF NOT GMD_QC_TEST_VALUES_PVT.DELETE_ROW(
772 p_test_value_id => l_qc_test_values_rec.test_value_id) THEN
773 GMD_API_PUB.Log_Message('GMD_FAILED_TO_DELETE_ROW',
774 'l_table_name', 'GMD_QC_TEST_VALUES',
775 'l_column_name', 'TEST_VALUE_ID',
776 'l_key_value', l_qc_test_values_rec.test_value_id);
777
778
779 RAISE FND_API.G_EXC_ERROR;
780 END IF;
781
782 -- set Return Paremeter Tbl
783 l_deleted_rows := l_deleted_rows + i;
784
785 END LOOP;
786
787 -- Now Process All VAlues After Deletion
788
789 GMD_QC_TEST_VALUES_GRP.VALIDATE_AFTER_DELETE_ALL(
790 p_gmd_qc_tests_rec => l_qc_tests_rec,
791 x_gmd_qc_tests_rec => l_qc_tests_rec_in,
792 x_return_status => l_return_status,
793 x_message_data => l_msg_data);
794
795 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
796 RAISE FND_API.G_EXC_ERROR;
797 END IF;
798
799
800
801 -- Standard Check of p_commit.
802 IF FND_API.to_boolean(p_commit)
803 THEN
804 COMMIT WORK;
805 END IF;
806
807
808 x_return_status := l_return_status;
809 x_deleted_rows := l_deleted_rows;
810
811 EXCEPTION
812 WHEN FND_API.G_EXC_ERROR THEN
813 ROLLBACK TO DELETE_TEST_VALUES;
814 x_return_status := FND_API.G_RET_STS_ERROR;
815 FND_MSG_PUB.Count_AND_GET ( p_encoded => FND_API.G_FALSE
816 , p_count => x_msg_count
817 , p_data => x_msg_data
818 );
819
820 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
821 ROLLBACK TO DELETE_TEST_VALUES;
822 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
823 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
824 , p_count => x_msg_count
825 , p_data => x_msg_data
826 );
827
828
829
830 WHEN OTHERS THEN
831 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
832 ROLLBACK TO DELETE_TEST_VALUES;
833 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
834 , l_api_name
835 );
836
837 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
838 , p_count => x_msg_count
839 , p_data => x_msg_data
840 );
841
842 END DELETE_TEST_VALUES;
843
844 PROCEDURE DELETE_CUSTOMER_TESTS
845 ( p_api_version IN NUMBER
846 , p_init_msg_list IN VARCHAR2
847 , p_commit IN VARCHAR2
848 , p_validation_level IN NUMBER
849 , p_qc_cust_tests_tbl IN GMD_QC_TESTS_PUB.qc_cust_tests_tbl
850 , x_deleted_rows OUT NOCOPY NUMBER
851 , x_return_status OUT NOCOPY VARCHAR2
852 , x_msg_count OUT NOCOPY NUMBER
853 , x_msg_data OUT NOCOPY VARCHAR2
854 )
855 IS
856 l_api_name CONSTANT VARCHAR2 (30) := 'DELETE_CUSTOMER_TESTS';
857 l_api_version CONSTANT NUMBER := 1.0;
858 l_msg_count NUMBER :=0;
859 l_msg_data VARCHAR2(2000);
860 l_return_status VARCHAR2(1):=FND_API.G_RET_STS_SUCCESS;
861 l_qc_tests_rec GMD_QC_TESTS%ROWTYPE;
862 l_qc_tests_rec_in GMD_QC_TESTS%ROWTYPE;
863 l_qc_cust_tests_rec GMD_CUSTOMER_TESTS%ROWTYPE;
864 l_deleted_rows NUMBER;
865 l_test_id NUMBER(10);
866
867 BEGIN
868
869
870 -- Standard Start OF API savepoint
871
872 SAVEPOINT DELETE_CUSTOMER_TESTS;
873
874 -- Standard call to check for call compatibility.
875
876 IF NOT FND_API.Compatible_API_CALL
877 (l_api_version , p_api_version , l_api_name , G_PKG_NAME)
878 THEN
879 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
880 END IF;
881
882 -- Initialize message list if p_int_msg_list is set TRUE.
883
884 IF FND_API.to_boolean(p_init_msg_list)
885 THEN
886 FND_MSG_PUB.Initialize;
887 END IF;
888
889 -- Initialize API return Parameters
890
891 l_return_status := FND_API.G_RET_STS_SUCCESS;
892
893 -- Since we can only delete customer values associated to one test
894 -- Get the test record based on first customer value record.
895
896 IF p_qc_cust_tests_tbl(1).test_id IS NULL THEN
897 GMD_API_PUB.log_message('GMD_TEST_ID_CODE_NULL');
898 RAISE FND_API.G_EXC_ERROR;
899 ELSE
900 l_qc_tests_rec_in.test_id := p_qc_cust_tests_tbl(1).test_id;
901 END IF;
902
903 -- Fetch test Header Row.
904
905 IF NOT GMD_QC_TESTS_PVT.fetch_row(
906 p_gmd_qc_tests => l_qc_tests_rec_in,
907 x_gmd_qc_tests => l_qc_tests_rec) THEN
908 RAISE FND_API.G_EXC_ERROR;
909 END IF;
910
911 -- NOW LOCK THIS HEADER ROW !!!!!
912
913 IF NOT GMD_QC_TESTS_PVT.lock_row(
914 p_test_id => l_qc_tests_rec.test_id,
915 p_test_code => l_qc_tests_rec.test_code) THEN
916 GMD_API_PUB.Log_Message('GMD_LOCKING_FAILURE',
917 'l_table_name', 'GMD_QC_TESTS',
918 'l_column_name','TEST_ID',
919 'l_key_value', l_qc_tests_rec.test_id);
920
921 RAISE FND_API.G_EXC_ERROR;
922 END IF;
923
924
925 FOR i in 1..p_qc_cust_tests_tbl.COUNT LOOP
926
927 l_qc_cust_tests_rec := p_qc_cust_tests_tbl(i);
928
929 IF l_qc_cust_tests_rec.test_id is NULL THEN
930 GMD_API_PUB.log_message('GMD_TEST_ID_CODE_NULL');
931 RAISE FND_API.G_EXC_ERROR;
932 END IF;
933
934 IF l_qc_cust_tests_rec.cust_id is NULL THEN
935 GMD_API_PUB.log_message('GMD_API_CUST_ID_NULL');
936 RAISE FND_API.G_EXC_ERROR;
937 END IF;
938
939 -- Check that the test_id's being processed are consistant
940
941 IF l_qc_tests_rec.test_id <> l_qc_cust_tests_rec.test_id THEN
942 GMD_API_PUB.log_message('GMD_INVALID_VALUES_TEST', 'TEST_CODE',
943 l_qc_tests_rec.test_code );
944 RAISE FND_API.G_EXC_ERROR;
945 END IF;
946
947 -- Call Validate Routine to validate Header
948
949 GMD_CUSTOMER_TESTS_GRP.VALIDATE_BEFORE_DELETE(
950 p_init_msg_list => p_init_msg_list,
951 p_test_id => l_qc_cust_tests_rec.test_id,
952 p_cust_id => l_qc_cust_tests_rec.cust_id,
953 x_return_status => l_return_status,
954 x_message_data => l_msg_data);
955
956 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
957 RAISE FND_API.G_EXC_ERROR;
958 END IF;
959
960
961 -- LOCK THE ROW
962
963 IF NOT GMD_CUSTOMER_TESTS_PVT.LOCK_ROW(
964 p_test_id => l_qc_cust_tests_rec.test_id,
965 p_cust_id => l_qc_cust_tests_rec.cust_id) THEN
966 GMD_API_PUB.Log_Message('GMD_LOCKING_FAILURE',
967 'l_table_name', 'GMD_CUSTOMER_TESTS',
968 'l_column_name','CUST_ID',
969 'l_key_value', l_qc_cust_tests_rec.cust_id);
970 RAISE FND_API.G_EXC_ERROR;
971 END IF;
972
973 -- Now Delete Row
974
975 IF NOT GMD_CUSTOMER_TESTS_PVT.DELETE_ROW(
976 p_test_id => l_qc_cust_tests_rec.test_id,
977 p_cust_id => l_qc_cust_tests_rec.cust_id) THEN
978
979 GMD_API_PUB.Log_Message('GMD_FAILED_TO_DELETE_ROW',
980 'l_table_name', 'GMD_CUSTOMER_TESTS',
981 'l_column_name','CUST_ID',
982 'l_key_value', l_qc_cust_tests_rec.cust_id);
983
984 RAISE FND_API.G_EXC_ERROR;
985 END IF;
986
987
988 -- set Return Paremeter Tbl
989 l_deleted_rows := l_deleted_rows + i;
990
991 END LOOP;
992
993
994 -- Standard Check of p_commit.
995 IF FND_API.to_boolean(p_commit)
996 THEN
997 COMMIT WORK;
998 END IF;
999
1000
1001 x_return_status := l_return_status;
1002 x_deleted_rows := l_deleted_rows;
1003
1004 EXCEPTION
1005 WHEN FND_API.G_EXC_ERROR THEN
1006 ROLLBACK TO DELETE_CUSTOMER_TESTS;
1007 x_return_status := FND_API.G_RET_STS_ERROR;
1008 FND_MSG_PUB.Count_AND_GET ( p_encoded => FND_API.G_FALSE
1009 , p_count => x_msg_count
1010 , p_data => x_msg_data
1011 );
1012
1013 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1014 ROLLBACK TO DELETE_CUSTOMER_TESTS;
1015 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1016 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
1017 , p_count => x_msg_count
1018 , p_data => x_msg_data
1019 );
1020
1021
1022
1023 WHEN OTHERS THEN
1024 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1025 ROLLBACK TO DELETE_CUSTOMER_TESTS;
1026 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
1027 , l_api_name
1028 );
1029
1030 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
1031 , p_count => x_msg_count
1032 , p_data => x_msg_data
1033 );
1034
1035 END delete_customer_tests;
1036
1037 END gmd_qc_tests_pub;