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