[Home] [Help]
PACKAGE BODY: APPS.GMD_SPEC_PUB
Source
1 PACKAGE BODY GMD_SPEC_PUB AS
2 /* $Header: GMDPSPCB.pls 120.1 2006/05/31 13:43:08 ragsriva noship $
3 +=========================================================================+
4 | Copyright (c) 2000 Oracle Corporation |
5 | TVP, Reading, England |
6 | All rights reserved |
7 +=========================================================================+
8 | File Name : GMDPSPCB.pls |
9 | Package Name : GMD_Spec_PUB |
10 | Type : PUBLIC |
11 | |
12 | Contents CREATE_SPEC |
13 | DELETE_SPEC |
14 | DELETE_SPEC_TESTS |
15 | |
16 | DESCRIPTION |
17 | This package contains public definitions for processing |
18 | QC Specifications |
19 | |
20 | |
21 | HISTORY |
22 | 03-AUG-2002 K.Y.Hunt |
23 | |
24 | HVerddin B2711643: Added call to set user_context |
25 | |
26 | |
27 +=========================================================================+
28 API Name : GMD_Spec_PUB
29 Type : Public
30 Function : This package contains public procedures used to process
31 specifications.
32 Pre-reqs : N/A
33 Parameters: Per function
34
35
36 Current Vers : 2.0
37
38 Previous Vers : 1.0
39
40 Initial Vers : 1.0
41 Notes
42 END of Notes */
43
44
45 /* Global variables */
46
47 G_PKG_NAME CONSTANT VARCHAR2(30):='GMD_SPEC_PUB';
48
49 /*
50 +=========================================================================+
51 | Name : CREATE_SPEC |
52 | Type : PUBLIC |
53 | |
54 | |
55 | DESCRIPTION |
56 | Accepts a single spec definition plus a table of spec_tests |
57 | definitions. |
58 | The owning spec data must always be supplied. If a spec_id is |
59 | supplied, it will be assumed that the corresponding row already |
60 | exists. Where spec_id is NULL, an attempt will be made to insert |
61 | the row. |
62 | An attempt will be made to validate and then insert each of the |
63 | spec_tests supplied which must belong the the supplied owning spec. |
64 | In the case of any failure a rollback is instigated. |
65 | |
66 | HISTORY |
67 | 03-AUG-2002 K.Y.Hunt |
68 | 02-MAY-2005 saikiran vankadari As part of Convergence changes, |
69 | call to GMA_GLOBAL_GRP.get_who() is replaced with |
70 | GMD_SPEC_GRP.get_who() procedure |
71 | |
72 +=========================================================================+
73 */
74
75 PROCEDURE CREATE_SPEC
76 ( p_api_version IN NUMBER
77 , p_init_msg_list IN VARCHAR2
78 , p_commit IN VARCHAR2
79 , p_validation_level IN VARCHAR2
80 , p_spec IN GMD_SPECIFICATIONS%ROWTYPE
81 , p_spec_tests_tbl IN GMD_SPEC_PUB.spec_tests_tbl
82 , p_user_name IN VARCHAR2
83 , x_spec OUT NOCOPY GMD_SPECIFICATIONS%ROWTYPE
84 , x_spec_tests_tbl OUT NOCOPY GMD_SPEC_PUB.spec_tests_tbl
85 , x_return_status OUT NOCOPY VARCHAR2
86 , x_msg_count OUT NOCOPY NUMBER
87 , x_msg_data OUT NOCOPY VARCHAR2
88 )
89 IS
90 l_api_name CONSTANT VARCHAR2 (30) := 'CREATE_SPEC';
91 l_api_version CONSTANT NUMBER := 2.0;
92 l_msg_count NUMBER :=0;
93 l_msg_data VARCHAR2(2000);
94 l_return_status VARCHAR2(1):=FND_API.G_RET_STS_SUCCESS;
95 l_spec GMD_SPECIFICATIONS%ROWTYPE;
96 l_spec_out GMD_SPECIFICATIONS%ROWTYPE;
97 l_spec_tests GMD_SPEC_TESTS%ROWTYPE;
98 l_spec_tests_out GMD_SPEC_TESTS%ROWTYPE;
99 l_spec_tests_tbl GMD_SPEC_PUB.spec_tests_tbl;
100 l_spec_id NUMBER;
101 l_rowid ROWID;
102 l_user_id NUMBER(15);
103 pp_spec_tests_tbl GMD_SPEC_PUB.spec_tests_tbl ;
104
105 CURSOR Cur_get_dtl_base (p_basespec_id NUMBER) IS
106 SELECT *
107 FROM gmd_spec_tests
108 WHERE spec_id = p_basespec_id;
109 TYPE detail_tab_base IS TABLE OF Cur_get_dtl_base%ROWTYPE INDEX BY BINARY_INTEGER;
110 X_dtl_tbl_base detail_tab_base;
111
112 X_row NUMBER := 1;
113 Max_sequence NUMBER := 0;
114
115 BEGIN
116
117
118 -- Standard Start OF API savepoint
119 -- ===============================
120 SAVEPOINT Create_Spec;
121
122 -- Standard call to check for call compatibility
123 -- =============================================
124 IF NOT FND_API.Compatible_API_CALL
125 (l_api_version , p_api_version , l_api_name , G_PKG_NAME)
126 THEN
127 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
128 END IF;
129
130 -- Initialize message list if p_int_msg_list is set TRUE.
131 -- ======================================================
132 IF FND_API.to_boolean(p_init_msg_list)
133 THEN
134 FND_MSG_PUB.Initialize;
135 END IF;
136
137 -- Initialize API return Parameters
138 -- ================================
139 l_return_status := FND_API.G_RET_STS_SUCCESS;
140
141 -- Validate User Name Parameter
142 -- ============================
143 GMD_SPEC_GRP.Get_Who ( p_user_name => p_user_name
144 ,x_user_id => l_user_id);
145
146 IF NVL(l_user_id, -1) < 0
147 THEN
148 GMD_API_PUB.Log_Message('GMD_INVALID_USER_NAME',
149 'l_user_name', p_user_name);
150 RAISE FND_API.G_EXC_ERROR;
151
152 ELSE
153 -- Added below for BUG 2711643. Hverddin
154 GMD_API_PUB.SET_USER_CONTEXT(p_user_id => l_user_id,
155 x_return_status => l_return_status);
156
157 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
158 RAISE FND_API.G_EXC_ERROR;
159 END IF;
160
161 END IF;
162
163 -- Create scenario -- may be creating
164 -- a) header plus childen (standalone header not allowed)
165 -- b) additional children for existing header
166 -- =========================================================================
167 l_spec := p_spec;
168
169 -- Bug# 5251222
170 -- Added code to nullify the following fields for monitoring specs
171 IF p_spec.spec_type = 'M' THEN
172 l_spec.inventory_item_id := NULL;
173 l_spec.revision := NULL;
174 l_spec.grade_code := NULL;
175 END IF;
176
177 -- If a spec_name is supplied we must be creating a new spec
178 -- ===========================================================
179 IF l_spec.spec_name is NOT NULL
180 THEN
181 -- Ensure spec_id is null
182 -- ======================
183 l_spec.spec_id := NULL;
184
185 -- Need to create the header (gmd_specifications) so validate the spec data
186 -- ========================================================================
187 GMD_SPEC_GRP.Validate_Spec_Header (
188 p_spec_header => l_spec
189 , p_called_from => 'API'
190 , p_operation => 'INSERT'
191 , x_return_status => l_return_status
192 );
193
194 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
195 RAISE FND_API.G_EXC_ERROR;
196 END IF;
197
198 -- Set the Who column definitions ahead of creating SPEC header
199 -- =============================================================
200 l_spec.created_by := l_user_id;
201 l_spec.last_updated_by := l_user_id;
202
203 -- Insert SPEC
204 -- ===========
205 IF NOT GMD_Specifications_PVT.INSERT_ROW(p_spec => l_spec)
206 THEN
207 GMD_API_PUB.Log_Message('GMD_FAILED_TO_INSERT_ROW',
208 'l_table_name', 'GMD_SPECIFICATIONS',
209 'l_column_name', 'SPEC_ID',
210 'l_key_value', l_spec.spec_id);
211 RAISE FND_API.G_EXC_ERROR;
212 END IF;
213 -- All test specs must conform to newly created spec_id
214 -- ====================================================
215 l_spec_id := l_spec.spec_id;
216 ELSE
217 -- SPEC create NOT required; we are adding spec_tests to an exisiting spec
218 -- All spec_id's must relate to the same spec and therefore be the same
219 -- =======================================================================
220 l_spec_id := p_spec_tests_tbl(1).spec_id;
221 IF l_spec_id is NULL
222 THEN
223 GMD_API_PUB.Log_Message('GMD_SPEC_ID_REQUIRED');
224 GMD_API_PUB.Log_Message('GMD_SPEC_TEST_REQUIRES_SPEC_ID');
225 RAISE FND_API.G_EXC_ERROR;
226 END IF;
227
228 END IF;
229
230
231 -- ===================================================================================
232 -- == Check if there is a Base Spec defined. If so, need to add
233 -- == all those spec tests (Bug 3401368)
234 -- ====================================================================================
235 if (p_spec.base_spec_id is not NULL) then
236 FOR get_rec IN Cur_get_dtl_base(p_spec.base_spec_id) LOOP
237 get_rec.spec_id := l_spec_id ;
238 get_rec.from_base_ind := 'Y' ;
239 get_rec.exclude_ind := NULL ;
240 get_rec.modified_ind := NULL ;
241 pp_spec_tests_tbl(X_row) := get_rec;
242
243 if (max_sequence < pp_spec_tests_tbl(x_row).seq) then
244 Max_sequence := pp_spec_tests_tbl(x_row).seq ;
245 end if ;
246 X_row := X_row + 1;
247 END LOOP;
248 end if ;
249
250
251 -- Need to add all the spec tests
252 -- Make sure sequence does not duplicate
253 FOR i in 1..p_spec_tests_tbl.COUNT LOOP
254 pp_spec_tests_tbl(X_row) := p_spec_tests_tbl(i);
255 pp_spec_tests_tbl(X_row).spec_id := l_spec_id ;
256 pp_spec_tests_tbl(X_row).from_base_ind := NULL ;
257 pp_spec_tests_tbl(X_row).exclude_ind := NULL ;
258 pp_spec_tests_tbl(X_row).modified_ind := NULL ;
259
260 /*Bug 3465014*/
261 pp_spec_tests_tbl(X_row).seq := p_spec_tests_tbl(i).seq + Max_sequence ;
262
263 X_row := X_row + 1;
264 END LOOP;
265
266
267 -- Loop through the spec tests validating and creating
268 -- ===================================================
269 FOR i in 1..pp_spec_tests_tbl.COUNT LOOP
270
271 l_spec_tests := pp_spec_tests_tbl(i);
272
273 -- Validate that the spec_id's are all consistent. Must all belong to the same header
274 -- ===================================================================================
275 IF ( NVL(l_spec_tests.spec_id, l_spec.spec_id ) <> l_spec_id)
276 THEN
277 GMD_API_PUB.Log_Message('GMD_INCONSISTENT_SPEC_ID',
278 'SPEC_ID', l_spec_tests.spec_id);
279 RAISE FND_API.G_EXC_ERROR;
280 END IF;
281
282 -- Ensure SPEC_ID is assigned
283 -- ==========================
284 l_spec_tests.spec_id := l_spec_id;
285
286 -- Validate SPEC_TEST
287 -- ==================
288 GMD_SPEC_GRP.Validate_Spec_Test(
289 p_spec_test => l_spec_tests
290 , p_called_from => 'API'
291 , p_operation => 'INSERT'
292 , x_spec_test => l_spec_tests_out
293 , x_return_status => l_return_status
294 );
295
296 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
297 -- Message detailing cause of validation failure is already on
298 -- the stack. But ensure the precise record is identified
299 -- ==========================================================
300 GMD_API_PUB.Log_Message('GMD_API_RECORD_IDENTIFIER',
301 'l_table_name', 'GMD_SPEC_TESTS',
302 'l_column_name', 'TEST_ID',
303 'l_key_value', l_spec_tests.test_id);
304 RAISE FND_API.G_EXC_ERROR;
305 END IF;
306
307 l_spec_tests := l_spec_tests_out;
308
309 -- Set Who columns ahead of Insert Row
310 -- ===================================
311 l_spec_tests.created_by := l_user_id;
312 l_spec_tests.last_updated_by := l_user_id;
313
314 IF NOT GMD_SPEC_TESTS_PVT.INSERT_ROW(p_spec_tests => l_spec_tests)
315 THEN
316 GMD_API_PUB.Log_Message('GMD_FAILED_TO_INSERT_ROW',
317 'l_table_name', 'GMD_SPEC_TESTS',
318 'l_column_name', 'TEST_ID',
319 'l_key_value', l_spec_tests.test_id);
320 RAISE FND_API.G_EXC_ERROR;
321 END IF;
322
323 -- Update Return Parameter Tbl
324 -- ===========================
325 l_spec_tests_tbl(i) := l_spec_tests;
326
327 END LOOP;
328
329
330 -- Post insert validation:
331 -- a) a spec must have at least one spec test
332 -- b) expression based tests must have associated reference tests
333 -- ==============================================================
334 GMD_SPEC_GRP.Validate_After_Insert_All(
335 p_spec_id => l_spec_id
336 , x_return_status => l_return_status
337 );
338
339 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
340 -- Message detailing cause of validation failure is already on
341 -- the stack. But ensure the precise record is identified
342 -- ==========================================================
343 GMD_API_PUB.Log_Message('GMD_API_RECORD_IDENTIFIER',
344 'l_table_name', 'GMD_SPECIFICATIONS',
345 'l_column_name', 'SPEC_NAME',
346 'l_key_value', l_spec.spec_name);
347 RAISE FND_API.G_EXC_ERROR;
348 END IF;
349
350 -- Standard Check of p_commit.
351 -- ==========================
352 IF FND_API.to_boolean(p_commit)
353 THEN
354 COMMIT WORK;
355 END IF;
356
357 x_return_status := l_return_status;
358 x_spec := l_spec;
359 x_spec_tests_tbl := l_spec_tests_tbl;
360
361 EXCEPTION
362 WHEN FND_API.G_EXC_ERROR THEN
363 ROLLBACK TO Create_Spec;
364 x_return_status := FND_API.G_RET_STS_ERROR;
365 FND_MSG_PUB.Count_AND_GET ( p_encoded => FND_API.G_FALSE
366 , p_count => x_msg_count
367 , p_data => x_msg_data
368 );
369
370 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
371 ROLLBACK TO Create_Spec;
372 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
373 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
374 , p_count => x_msg_count
375 , p_data => x_msg_data
376 );
377
378 WHEN OTHERS THEN
379 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
380 ROLLBACK TO Create_Spec;
381 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
382 , l_api_name
383 );
384
385 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
386 , p_count => x_msg_count
387 , p_data => x_msg_data
388 );
389
390 END CREATE_SPEC;
391
392 /*
393 +=========================================================================+
394 | Name : DELETE_SPEC |
395 | Type : PUBLIC |
396 | |
397 | |
398 | DESCRIPTION |
399 | Accepts a single specification definition. Validates to ensure |
400 | that there is a corresponding row which is not already |
401 | delete marked. Where validation is successful, a logical delete |
402 | is performed setting delete_mark=1 |
403 | In the case of any failure a rollback is instigated. |
404 | |
405 | HISTORY |
406 | 03-AUG-2002 K.Y.Hunt |
407 | 02-MAY-2005 saikiran vankadari As part of Convergence changes, |
408 | call to GMA_GLOBAL_GRP.get_who() is replaced with |
409 | GMD_SPEC_GRP.get_who() procedure |
410 | |
411 +=========================================================================+
412 */
413 PROCEDURE DELETE_SPEC
414 ( p_api_version IN NUMBER
415 , p_init_msg_list IN VARCHAR2
416 , p_commit IN VARCHAR2
417 , p_validation_level IN VARCHAR2
418 , p_spec IN GMD_SPECIFICATIONS%ROWTYPE
419 , p_user_name IN VARCHAR2
420 , x_deleted_rows OUT NOCOPY NUMBER
421 , x_return_status OUT NOCOPY VARCHAR2
422 , x_msg_count OUT NOCOPY NUMBER
423 , x_msg_data OUT NOCOPY VARCHAR2
424 )
425 IS
426 l_api_name CONSTANT VARCHAR2 (30) := 'DELETE_SPEC';
427 l_api_version CONSTANT NUMBER := 1.0;
428 l_msg_count NUMBER :=0;
429 l_msg_data VARCHAR2(2000);
430 l_return_status VARCHAR2(1):=FND_API.G_RET_STS_SUCCESS;
431 l_spec GMD_SPECIFICATIONS%ROWTYPE;
432 l_deleted_rows NUMBER :=0;
433
434 BEGIN
435
436
437 -- Standard Start OF API savepoint
438 -- ===============================
439 SAVEPOINT Delete_Spec;
440
441 -- Standard call to check for call compatibility.
442 -- ==============================================
443 IF NOT FND_API.Compatible_API_CALL
444 (l_api_version , p_api_version , l_api_name , G_PKG_NAME)
445 THEN
446 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
447 END IF;
448
449 -- Initialize message list if p_int_msg_list is set TRUE.
450 -- ======================================================
451 IF FND_API.to_boolean(p_init_msg_list)
452 THEN
453 FND_MSG_PUB.Initialize;
454 END IF;
455
456 -- Initialize API return Parameters
457 -- ================================
458 x_return_status := FND_API.G_RET_STS_SUCCESS;
459
460 -- Validate user_name
461 -- ==================
462 GMD_SPEC_GRP.Get_Who ( p_user_name => p_user_name
463 ,x_user_id => l_spec.last_updated_by);
464
465 IF NVL(l_spec.last_updated_by, -1) < 0
466 THEN
467 GMD_API_PUB.Log_Message('GMD_INVALID_USER_NAME',
468 'l_user_name', p_user_name);
469 RAISE FND_API.G_EXC_ERROR;
470 ELSE
471 -- Added below for BUG 2711643. Hverddin
472 GMD_API_PUB.SET_USER_CONTEXT(p_user_id => l_spec.last_updated_by,
473 x_return_status => l_return_status);
474
475 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
476 RAISE FND_API.G_EXC_ERROR;
477 END IF;
478
479 END IF;
480
481 -- Validate to ensure spec is in a suitable state to delete mark
482 -- ==============================================================
483 GMD_SPEC_GRP.Validate_Before_Delete( p_spec_id => p_spec.spec_id
484 , x_return_status => l_return_status
485 , x_message_data => l_msg_data
486 );
487 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
488 -- Diagnostic messages already on stack from group level
489 RAISE FND_API.G_EXC_ERROR;
490 END IF;
491
492 -- Lock the row ahead of delete marking
493 -- ====================================
494 IF NOT GMD_Specifications_PVT.Lock_Row(p_spec.spec_id)
495 THEN
496 -- Report Failure to obtain locks
497 -- ==============================
498 GMD_API_PUB.Log_Message('GMD_LOCKING_FAILURE',
499 'l_table_name', 'GMD_SPECIFICATIONS',
500 'l_column_name', 'SPEC_ID',
501 'l_key_value', p_spec.spec_id);
502 RAISE FND_API.G_EXC_ERROR;
503 END IF;
504
505 IF NOT GMD_Specifications_PVT.Mark_for_Delete ( p_spec_id => p_spec.spec_id
506 , p_last_update_date => sysdate
507 , p_last_updated_by => l_spec.last_updated_by
508 )
509 THEN
510 GMD_API_PUB.Log_Message('GMD_FAILED_TO_DELETE_ROW',
511 'l_table_name', 'GMD_SPECIFICATIONS',
512 'l_column_name', 'SPEC_ID',
513 'l_key_value', p_spec.spec_id);
514 ELSE -- Report one row successfully delete marked
515 x_deleted_rows := 1;
516 END IF;
517
518 -- Standard Check of p_commit.
519 IF FND_API.to_boolean(p_commit)
520 THEN
521 COMMIT WORK;
522 END IF;
523
524 x_return_status := l_return_status;
525
526 EXCEPTION
527 WHEN FND_API.G_EXC_ERROR THEN
528 ROLLBACK TO Delete_Spec;
529 x_deleted_rows := 0;
530 x_return_status := FND_API.G_RET_STS_ERROR;
531 FND_MSG_PUB.Count_AND_GET ( p_encoded => FND_API.G_FALSE
532 , p_count => x_msg_count
533 , p_data => x_msg_data
534 );
535
536 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
537 ROLLBACK TO Delete_Spec;
538 x_deleted_rows := 0;
539 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
540 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
541 , p_count => x_msg_count
542 , p_data => x_msg_data
543 );
544
545
546
547 WHEN OTHERS THEN
548 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
549 x_deleted_rows := 0;
550 ROLLBACK TO Delete_Spec;
551 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
552 , l_api_name
553 );
554
555 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
556 , p_count => x_msg_count
557 , p_data => x_msg_data
558 );
559
560 END DELETE_SPEC;
561
562 /*
563 +=========================================================================+
564 | Name : DELETE_SPEC_TESTS |
565 | Type : PUBLIC |
566 | |
567 | |
568 | DESCRIPTION |
569 | Accepts a table of spec_tests definitions. |
570 | All the spec_tests must relate to a single specification (spec_id) |
571 For each spec_test supplied, validates to ensure that the |
572 | designated row exists and then physically deletes it. |
573 | In the case of any failure a rollback is instigated. |
574 | |
575 | HISTORY |
576 | 03-AUG-2002 K.Y.Hunt |
577 | |
578 +=========================================================================+
579 */
580 PROCEDURE DELETE_SPEC_TESTS
581 ( p_api_version IN NUMBER
582 , p_init_msg_list IN VARCHAR2
583 , p_commit IN VARCHAR2
584 , p_validation_level IN VARCHAR2
585 , p_spec_tests_tbl IN GMD_SPEC_PUB.spec_tests_tbl
586 , x_deleted_rows OUT NOCOPY NUMBER
587 , x_return_status OUT NOCOPY VARCHAR2
588 , x_msg_count OUT NOCOPY NUMBER
589 , x_msg_data OUT NOCOPY VARCHAR2
590 )
591 IS
592 l_api_name CONSTANT VARCHAR2 (30) := 'DELETE_SPEC_TESTS';
593 l_api_version CONSTANT NUMBER := 1.0;
594 l_msg_count NUMBER :=0;
595 l_msg_data VARCHAR2(2000);
596 l_return_status VARCHAR2(1):=FND_API.G_RET_STS_SUCCESS;
597 l_spec_id NUMBER :=0;
598 l_spec_tests GMD_SPEC_TESTS%ROWTYPE;
599 l_spec_tests_out GMD_SPEC_TESTS%ROWTYPE;
600 l_deleted_rows NUMBER :=0;
601
602 BEGIN
603
604
605 -- Standard Start OF API savepoint
606 -- ===============================
607 SAVEPOINT Delete_Spec_Tests;
608
609 -- Standard call to check for call compatibility.
610 -- ==============================================
611 IF NOT FND_API.Compatible_API_CALL
612 (l_api_version , p_api_version , l_api_name , G_PKG_NAME)
613 THEN
614 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
615 END IF;
616
617 -- Initialize message list if p_int_msg_list is set TRUE.
618 -- ======================================================
619 IF FND_API.to_boolean(p_init_msg_list)
620 THEN
621 FND_MSG_PUB.Initialize;
622 END IF;
623
624 -- Initialize API return Parameters
625 -- ================================
626 x_return_status := FND_API.G_RET_STS_SUCCESS;
627
628 -- Process each of the spec tests
629 -- ===============================
630 FOR i in 1..p_spec_tests_tbl.COUNT LOOP
631 l_spec_tests := p_spec_tests_tbl(i);
632 -- Ensure the owning spec_id is supplied
633 -- =====================================
634 IF ( l_spec_tests.spec_id IS NULL )
635 THEN
636 -- raise validation error
637 GMD_API_PUB.Log_Message('GMD_SPEC_ID_REQUIRED');
638 RAISE FND_API.G_EXC_ERROR;
639 END IF;
640
641 -- First loop only ,validate the owning SPEC
642 -- ==========================================
643 IF i=1
644 THEN
645 -- Validate to ensure spec is a)not delete marked b)has a status which permits updates
646 -- ===================================================================================
647 GMD_SPEC_GRP.Validate_Before_Delete( p_spec_id => l_spec_tests.spec_id
648 , x_return_status => l_return_status
649 , x_message_data => l_msg_data
650 );
651 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
652 -- Diagnostic messages already on stack from group level
653 RAISE FND_API.G_EXC_ERROR;
654 END IF;
655
656 -- All spec_tests processed, must relate to this spec_id
657 -- =====================================================
658 l_spec_id := l_spec_tests.spec_id;
659
660 -- Lock the SPEC ahead of manipulating SPEC_TESTS
661 -- ===============================================
662 IF NOT GMD_Specifications_PVT.Lock_Row(l_spec_tests.spec_id)
663 THEN
664 -- Report Failure to obtain locks
665 -- ==============================
666 GMD_API_PUB.Log_Message('GMD_LOCKING_FAILURE',
667 'l_table_name', 'GMD_SPECIFICATIONS',
668 'l_column_name', 'SPEC_ID',
669 'l_key_value', l_spec_tests.spec_id);
670 RAISE FND_API.G_EXC_ERROR;
671 END IF;
672
673 -- All spec_tests must relate to the same owning spec
674 -- ==================================================
675 ELSIF l_spec_id <> l_spec_tests.spec_id
676 THEN
677 GMD_API_PUB.Log_Message('GMD_SUPPLY_CONSISTENT_SPEC_IDS',
678 'SPEC_ID1', l_spec_id,
679 'SPEC_ID2', l_spec_tests.spec_id);
680 RAISE FND_API.G_EXC_ERROR;
681 END IF; -- end of spec validation
682
683 -- Fetch to ensure spec_test exists
684 -- ================================
685 -- KYH 05/NOV/02 use separate params for fetch_row input and output
686 IF NOT GMD_SPEC_TESTS_PVT.fetch_row ( l_spec_tests,l_spec_tests_out)
687 THEN
688 GMD_API_PUB.Log_Message('GMD_FAILED_TO_FETCH_ROW',
689 'l_table_name', 'GMD_SPEC_TESTS',
690 'l_column_name', 'TEST_ID',
691 'l_key_value', l_spec_tests.test_id);
692 RAISE FND_API.G_EXC_ERROR;
693 END IF;
694 l_spec_tests := l_spec_tests_out;
695
696 -- Lock the spec_test ahead of deleting
697 -- ====================================
698 IF NOT GMD_SPEC_TESTS_PVT.Lock_Row(l_spec_tests.spec_id,l_spec_tests.test_id)
699 THEN
700 -- Report Failure to obtain locks
701 -- ==============================
702 GMD_API_PUB.Log_Message('GMD_LOCKING_FAILURE',
703 'l_table_name', 'GMD_SPEC_TESTS',
704 'l_column_name', 'TEST_ID',
705 'l_key_value', l_spec_tests.test_id);
706 RAISE FND_API.G_EXC_ERROR;
707 END IF;
708
709 IF NOT GMD_SPEC_TESTS_PVT.Delete_Row ( p_spec_id => l_spec_tests.spec_id
710 , p_test_id => l_spec_tests.test_id
711 )
712 THEN
713 GMD_API_PUB.Log_Message('GMD_FAILED_TO_DELETE_ROW',
714 'l_table_name', 'GMD_SPEC_TESTS',
715 'l_column_name', 'TEST_ID',
716 'l_key_value', l_spec_tests.test_id);
717 RAISE FND_API.G_EXC_ERROR;
718 END IF;
719
720 -- Ensure that at least one test remains under the specification
721 -- =============================================================
722 GMD_SPEC_GRP.Validate_After_Delete_Test ( p_spec_id => l_spec_tests.spec_id
723 , x_return_status => l_return_status
724 );
725 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
726 -- Diagnostic messages already on stack from group level
727 RAISE FND_API.G_EXC_ERROR;
728 END IF;
729
730 x_deleted_rows := i;
731
732 END LOOP;
733
734 -- Standard Check of p_commit.
735 IF FND_API.to_boolean(p_commit)
736 THEN
737 COMMIT WORK;
738 END IF;
739
740 x_return_status := l_return_status;
741
742 EXCEPTION
743 WHEN FND_API.G_EXC_ERROR THEN
744 ROLLBACK TO Delete_Spec_Tests;
745 x_return_status := FND_API.G_RET_STS_ERROR;
746 x_deleted_rows := 0;
747 FND_MSG_PUB.Count_AND_GET ( p_encoded => FND_API.G_FALSE
748 , p_count => x_msg_count
749 , p_data => x_msg_data
750 );
751
752 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
753 ROLLBACK TO Delete_Spec_Tests;
754 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
755 x_deleted_rows := 0;
756 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
757 , p_count => x_msg_count
758 , p_data => x_msg_data
759 );
760
761
762
763 WHEN OTHERS THEN
764 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
765 x_deleted_rows := 0;
766 ROLLBACK TO Delete_Spec_Tests;
767 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
768 , l_api_name
769 );
770
771 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
772 , p_count => x_msg_count
773 , p_data => x_msg_data
774 );
775
776 END DELETE_SPEC_TESTS;
777
778 END GMD_SPEC_PUB;