[Home] [Help]
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;
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;
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)
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:
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
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);
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;
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:
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
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(
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:
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.
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:
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;
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:
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:
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:
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
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.
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
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.
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:
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:
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;
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:
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:
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;
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
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.
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
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:
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:
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
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: );
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:
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
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: );
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:
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: );
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:
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);
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;
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)
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:
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:
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,
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;
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:
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:
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:
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
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(
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:
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
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: );
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:
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
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: );
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:
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: );
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:
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;
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;
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:
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:
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:
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:
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:
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.
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
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
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:
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.
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:
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.
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;
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:
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:
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:
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
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: );
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:
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
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: );
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:
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: );
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:
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;
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;
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:
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:
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:
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:
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:
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
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');
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:
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:
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:
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
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:
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
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:
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
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: );
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:
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
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: );
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:
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: );
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: