[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| DELETE_SPEC |
| DELETE_SPEC_TESTS |
| |
| DESCRIPTION |
| This package contains public definitions for processing |
| QC Specifications |
| |
| |
| HISTORY |
| 03-AUG-2002 K.Y.Hunt |
| |
| HVerddin B2711643: Added call to set user_context |
| |
| |
+=========================================================================+
API Name : GMD_Spec_PUB
Type : Public
Function : This package contains public procedures used to process
specifications.
Pre-reqs : N/A
Parameters: Per function
Current Vers : 2.0
Previous Vers : 1.0
Initial Vers : 1.0
Notes
END of Notes */
/* Global variables */
G_PKG_NAME CONSTANT VARCHAR2(30):='GMD_SPEC_PUB';
| exists. Where spec_id is NULL, an attempt will be made to insert |
| the row. |
| An attempt will be made to validate and then insert each of the |
| spec_tests supplied which must belong the the supplied owning spec. |
| In the case of any failure a rollback is instigated. |
| |
| HISTORY |
| 03-AUG-2002 K.Y.Hunt |
| 02-MAY-2005 saikiran vankadari As part of Convergence changes, |
| call to GMA_GLOBAL_GRP.get_who() is replaced with |
| GMD_SPEC_GRP.get_who() procedure |
| 31-JUL-2012 PLOWE -- 14364429 fp of 11i bug 8942264 added |
| check to cursor Cur_get_dtl_base to see if test has been deleted . |
| Also Overlay specification of table type for the spec_tests is |
| loaded with same details twice. Therefore the API errors as test_id |
| for the specification already existing, Thus add an ELSE part that |
| is missing in the API code. |
+=========================================================================+
*/
PROCEDURE CREATE_SPEC
( p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2
, p_commit IN VARCHAR2
, p_validation_level IN VARCHAR2
, p_spec IN GMD_SPECIFICATIONS%ROWTYPE
, p_spec_tests_tbl IN GMD_SPEC_PUB.spec_tests_tbl
, p_user_name IN VARCHAR2
, x_spec OUT NOCOPY GMD_SPECIFICATIONS%ROWTYPE
, x_spec_tests_tbl OUT NOCOPY GMD_SPEC_PUB.spec_tests_tbl
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'CREATE_SPEC';
SELECT st.* -- 14364429 fp of 11i bug 8942264 added check to see if test has been deleted
FROM gmd_spec_tests st , gmd_qc_tests_b t
WHERE st.spec_id = p_basespec_id and
t.test_id = st.test_id
AND t.delete_mark = 0;
, p_operation => 'INSERT'
, x_return_status => l_return_status
);
l_spec.last_updated_by := l_user_id;
IF NOT GMD_Specifications_PVT.INSERT_ROW(p_spec => l_spec)
THEN
GMD_API_PUB.Log_Message('GMD_FAILED_TO_INSERT_ROW',
'l_table_name', 'GMD_SPECIFICATIONS',
'l_column_name', 'SPEC_ID',
'l_key_value', l_spec.spec_id);
, p_operation => 'INSERT'
, x_spec_test => l_spec_tests_out
, x_return_status => l_return_status
);
l_spec_tests.last_updated_by := l_user_id;
IF NOT GMD_SPEC_TESTS_PVT.INSERT_ROW(p_spec_tests => l_spec_tests)
THEN
GMD_API_PUB.Log_Message('GMD_FAILED_TO_INSERT_ROW',
'l_table_name', 'GMD_SPEC_TESTS',
'l_column_name', 'TEST_ID',
'l_key_value', l_spec_tests.test_id);
GMD_SPEC_GRP.Validate_After_Insert_All(
p_spec_id => l_spec_id
, x_return_status => l_return_status
);
| Name : DELETE_SPEC |
| Type : PUBLIC |
| |
| |
| DESCRIPTION |
| Accepts a single specification definition. Validates to ensure |
| that there is a corresponding row which is not already |
| delete marked. Where validation is successful, a logical delete |
| is performed setting delete_mark=1 |
| In the case of any failure a rollback is instigated. |
| |
| HISTORY |
| 03-AUG-2002 K.Y.Hunt |
| 02-MAY-2005 saikiran vankadari As part of Convergence changes, |
| call to GMA_GLOBAL_GRP.get_who() is replaced with |
| GMD_SPEC_GRP.get_who() procedure |
| |
+=========================================================================+
*/
PROCEDURE DELETE_SPEC
( p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2
, p_commit IN VARCHAR2
, p_validation_level IN VARCHAR2
, p_spec IN GMD_SPECIFICATIONS%ROWTYPE
, p_user_name IN VARCHAR2
, x_deleted_rows OUT NOCOPY NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'DELETE_SPEC';
l_deleted_rows NUMBER :=0;
SAVEPOINT Delete_Spec;
,x_user_id => l_spec.last_updated_by);
IF NVL(l_spec.last_updated_by, -1) < 0
THEN
GMD_API_PUB.Log_Message('GMD_INVALID_USER_NAME',
'l_user_name', p_user_name);
GMD_API_PUB.SET_USER_CONTEXT(p_user_id => l_spec.last_updated_by,
x_return_status => l_return_status);
GMD_SPEC_GRP.Validate_Before_Delete( p_spec_id => p_spec.spec_id
, x_return_status => l_return_status
, x_message_data => l_msg_data
);
IF NOT GMD_Specifications_PVT.Mark_for_Delete ( p_spec_id => p_spec.spec_id
, p_last_update_date => sysdate
, p_last_updated_by => l_spec.last_updated_by
)
THEN
GMD_API_PUB.Log_Message('GMD_FAILED_TO_DELETE_ROW',
'l_table_name', 'GMD_SPECIFICATIONS',
'l_column_name', 'SPEC_ID',
'l_key_value', p_spec.spec_id);
ELSE -- Report one row successfully delete marked
x_deleted_rows := 1;
ROLLBACK TO Delete_Spec;
x_deleted_rows := 0;
ROLLBACK TO Delete_Spec;
x_deleted_rows := 0;
x_deleted_rows := 0;
ROLLBACK TO Delete_Spec;
END DELETE_SPEC;
| Name : DELETE_SPEC_TESTS |
| Type : PUBLIC |
| |
| |
| DESCRIPTION |
| Accepts a table of spec_tests definitions. |
| All the spec_tests must relate to a single specification (spec_id) |
For each spec_test supplied, validates to ensure that the |
| designated row exists and then physically deletes it. |
| In the case of any failure a rollback is instigated. |
| |
| HISTORY |
| 03-AUG-2002 K.Y.Hunt |
| |
+=========================================================================+
*/
PROCEDURE DELETE_SPEC_TESTS
( p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2
, p_commit IN VARCHAR2
, p_validation_level IN VARCHAR2
, p_spec_tests_tbl IN GMD_SPEC_PUB.spec_tests_tbl
, x_deleted_rows OUT NOCOPY NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'DELETE_SPEC_TESTS';
l_deleted_rows NUMBER :=0;
SAVEPOINT Delete_Spec_Tests;
GMD_SPEC_GRP.Validate_Before_Delete( p_spec_id => l_spec_tests.spec_id
, x_return_status => l_return_status
, x_message_data => l_msg_data
);
IF NOT GMD_SPEC_TESTS_PVT.Delete_Row ( p_spec_id => l_spec_tests.spec_id
, p_test_id => l_spec_tests.test_id
)
THEN
GMD_API_PUB.Log_Message('GMD_FAILED_TO_DELETE_ROW',
'l_table_name', 'GMD_SPEC_TESTS',
'l_column_name', 'TEST_ID',
'l_key_value', l_spec_tests.test_id);
GMD_SPEC_GRP.Validate_After_Delete_Test ( p_spec_id => l_spec_tests.spec_id
, x_return_status => l_return_status
);
x_deleted_rows := i;
ROLLBACK TO Delete_Spec_Tests;
x_deleted_rows := 0;
ROLLBACK TO Delete_Spec_Tests;
x_deleted_rows := 0;
x_deleted_rows := 0;
ROLLBACK TO Delete_Spec_Tests;
END DELETE_SPEC_TESTS;