[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE do_update_rule_object(
p_update_instance IN VARCHAR2 DEFAULT 'N',
p_rule_object_rec IN OUT NOCOPY RULE_OBJECTS_REC_TYPE,
p_object_version_number IN OUT NOCOPY NUMBER,
x_return_status IN OUT NOCOPY VARCHAR2
);
FUN_RULE_OBJECTS_PKG.Insert_Row (
X_ROWID =>l_rowid,
X_RULE_OBJECT_ID =>p_rule_object_rec.rule_object_id,
X_APPLICATION_ID =>p_rule_object_rec.application_id,
X_RULE_OBJECT_NAME =>p_rule_object_rec.rule_object_name,
X_RESULT_TYPE =>p_rule_object_rec.result_type,
X_REQUIRED_FLAG =>p_rule_object_rec.required_flag,
X_USE_DEFAULT_VALUE_FLAG =>p_rule_object_rec.use_default_value_flag,
X_DEFAULT_APPLICATION_ID =>p_rule_object_rec.default_application_id,
X_DEFAULT_VALUE =>p_rule_object_rec.default_value,
X_FLEX_VALUE_SET_ID =>p_rule_object_rec.flex_value_set_id,
X_FLEXFIELD_NAME =>p_rule_object_rec.flexfield_name,
X_FLEXFIELD_APP_SHORT_NAME =>p_rule_object_rec.flexfield_app_short_name,
X_MULTI_RULE_RESULT_FLAG =>p_rule_object_rec.multi_rule_result_flag,
X_CREATED_BY_MODULE =>p_rule_object_rec.created_by_module,
X_USER_RULE_OBJECT_NAME =>p_rule_object_rec.user_rule_object_name,
X_DESCRIPTION =>p_rule_object_rec.description,
X_USE_INSTANCE_FLAG =>p_rule_object_rec.use_instance_flag
);
FUN_RULE_OBJECTS_PKG.Insert_Row (
X_ROWID =>l_rowid,
X_RULE_OBJECT_ID =>NULL,
X_APPLICATION_ID =>l_rule_object_instance_rec.application_id,
X_RULE_OBJECT_NAME =>l_rule_object_instance_rec.rule_object_name,
X_RESULT_TYPE =>l_rule_object_instance_rec.result_type,
X_REQUIRED_FLAG =>l_rule_object_instance_rec.required_flag,
X_USE_DEFAULT_VALUE_FLAG =>l_rule_object_instance_rec.use_default_value_flag,
X_DEFAULT_APPLICATION_ID =>l_rule_object_instance_rec.default_application_id,
X_DEFAULT_VALUE =>l_rule_object_instance_rec.default_value,
X_FLEX_VALUE_SET_ID =>l_rule_object_instance_rec.flex_value_set_id,
X_FLEXFIELD_NAME =>l_rule_object_instance_rec.flexfield_name,
X_FLEXFIELD_APP_SHORT_NAME =>l_rule_object_instance_rec.flexfield_app_short_name,
X_MULTI_RULE_RESULT_FLAG =>l_rule_object_instance_rec.multi_rule_result_flag,
X_CREATED_BY_MODULE =>l_rule_object_instance_rec.created_by_module,
X_USER_RULE_OBJECT_NAME =>l_rule_object_instance_rec.user_rule_object_name,
X_DESCRIPTION =>l_rule_object_instance_rec.description,
X_USE_INSTANCE_FLAG =>l_rule_object_instance_rec.use_instance_flag,
X_INSTANCE_LABEL =>l_rule_object_instance_rec.instance_label,
X_PARENT_RULE_OBJECT_ID =>l_rule_object_instance_rec.parent_rule_object_id,
X_ORG_ID =>l_rule_object_instance_rec.org_id
);
SELECT RULE_OBJECT_ID INTO X_RULE_OBJECT_ID
FROM FUN_RULE_OBJECTS_B WHERE RULE_OBJECT_NAME = p_rule_object_name
AND APPLICATION_ID = p_application_id
AND ( (INSTANCE_LABEL IS NULL AND p_instance_label IS NULL) OR
(INSTANCE_LABEL IS NOT NULL AND p_instance_label IS NOT NULL AND INSTANCE_LABEL = p_instance_label))
AND ( (ORG_ID IS NULL AND p_org_id IS NULL) OR
( ORG_ID IS NOT NULL AND p_org_id IS NOT NULL AND ORG_ID = p_org_id))
AND PARENT_RULE_OBJECT_ID IS NOT NULL;
| do_update_rule_object
|
| DESCRIPTION
| Updates user customizable objects
|
| SCOPE - PRIVATE
|
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN:
| OUT:
| IN/ OUT:
| p_rule_object_rec
| p_object_version_number
| x_return_status
|
| RETURNS : NONE
|
| NOTES
|
| MODIFICATION HISTORY
|
+===========================================================================*/
PROCEDURE do_update_rule_object(
p_update_instance IN VARCHAR2 DEFAULT 'N',
p_rule_object_rec IN OUT NOCOPY RULE_OBJECTS_REC_TYPE,
p_object_version_number IN OUT NOCOPY NUMBER,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_object_version_number NUMBER;
SELECT OBJECT_VERSION_NUMBER,
ROWID
INTO l_object_version_number,
l_rowid
FROM FUN_RULE_OBJECTS_B
WHERE RULE_OBJECT_ID = p_rule_object_rec.rule_object_id
FOR UPDATE OF rule_object_id NOWAIT;
IF( p_update_instance = 'Y' ) THEN
-- call for validations of Rule Object Instance.
FUN_RULE_VALIDATE_PKG.validate_rule_object_instance(
'U',
p_rule_object_rec,
l_rowid,
x_return_status
);
FUN_RULE_OBJECTS_PKG.Update_Row (
X_RULE_OBJECT_ID =>p_rule_object_rec.rule_object_id,
X_APPLICATION_ID =>p_rule_object_rec.application_id,
X_RULE_OBJECT_NAME =>p_rule_object_rec.rule_object_name,
X_RESULT_TYPE =>p_rule_object_rec.result_type,
X_REQUIRED_FLAG =>p_rule_object_rec.required_flag,
X_USE_DEFAULT_VALUE_FLAG =>p_rule_object_rec.use_default_value_flag,
X_DEFAULT_APPLICATION_ID =>p_rule_object_rec.default_application_id,
X_DEFAULT_VALUE =>p_rule_object_rec.default_value,
X_FLEX_VALUE_SET_ID =>p_rule_object_rec.flex_value_set_id,
X_FLEXFIELD_NAME =>p_rule_object_rec.flexfield_name,
X_FLEXFIELD_APP_SHORT_NAME =>p_rule_object_rec.flexfield_app_short_name,
X_MULTI_RULE_RESULT_FLAG =>p_rule_object_rec.multi_rule_result_flag,
X_OBJECT_VERSION_NUMBER =>p_object_version_number,
X_CREATED_BY_MODULE =>p_rule_object_rec.created_by_module,
X_USER_RULE_OBJECT_NAME =>p_rule_object_rec.user_rule_object_name,
X_DESCRIPTION =>p_rule_object_rec.description,
X_USE_INSTANCE_FLAG =>p_rule_object_rec.use_instance_flag,
X_INSTANCE_LABEL =>p_rule_object_rec.instance_label,
X_PARENT_RULE_OBJECT_ID =>p_rule_object_rec.parent_rule_object_id,
X_ORG_ID =>p_rule_object_rec.org_id
);
SELECT RULE_OBJECT_ID INTO X_RULE_OBJECT_ID
FROM FUN_RULE_OBJECTS_B
WHERE RULE_OBJECT_NAME = p_rule_object_name
AND APPLICATION_ID = p_application_id
AND ( (INSTANCE_LABEL IS NULL AND p_instance_label IS NULL) OR
(INSTANCE_LABEL IS NOT NULL AND p_instance_label IS NOT NULL AND INSTANCE_LABEL = p_instance_label))
AND ( (ORG_ID IS NULL AND p_org_id IS NULL) OR
( ORG_ID IS NOT NULL AND p_org_id IS NOT NULL AND ORG_ID = p_org_id))
AND PARENT_RULE_OBJECT_ID IS NOT NULL;
* PROCEDURE update_rule_object
*
* DESCRIPTION
* Updates User customizable objects
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* ARGUMENTS
* IN:
* p_init_msg_list Initialize message stack if it is set to
* FND_API.G_TRUE. Default is FND_API.G_FALSE.
* p_rules_object_rec User Customizable Object record.
* IN/OUT:
* p_object_version_number Used for locking the being updated record.
* OUT:
* x_return_status Return status after the call. The status can
* be FND_API.G_RET_STS_SUCCESS (success),
* FND_API.G_RET_STS_ERROR (error),
* FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
* x_msg_count Number of messages in message stack.
* x_msg_data Message text if x_msg_count is 1.
*
* NOTES
*
* MODIFICATION HISTORY
*
* 10-Sep-2004 Amulya Mishra Created.
*
*/
PROCEDURE update_rule_object (
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_rule_object_rec IN RULE_OBJECTS_REC_TYPE,
p_object_version_number IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_rule_object_rec RULE_OBJECTS_REC_TYPE := p_rule_object_rec;
SELECT
B.RULE_OBJECT_ID,
B.APPLICATION_ID,
B.RULE_OBJECT_NAME,
B.RESULT_TYPE,
B.REQUIRED_FLAG,
B.USE_DEFAULT_VALUE_FLAG,
ATTR.DEFAULT_APPLICATION_ID,
ATTR.DEFAULT_VALUE,
B.FLEX_VALUE_SET_ID,
B.FLEXFIELD_NAME,
B.FLEXFIELD_APP_SHORT_NAME,
B.MULTI_RULE_RESULT_FLAG,
B.CREATED_BY_MODULE,
B.OBJECT_VERSION_NUMBER,
TL.USER_RULE_OBJECT_NAME,
TL.DESCRIPTION,
B.USE_INSTANCE_FLAG,
B.INSTANCE_LABEL,
B.PARENT_RULE_OBJECT_ID,
B.ORG_ID
FROM FUN_RULE_OBJECTS_B B, FUN_RULE_OBJECTS_TL TL, FUN_RULE_OBJ_ATTRIBUTES ATTR
WHERE B.PARENT_RULE_OBJECT_ID = p_rule_object_id
AND B.RULE_OBJECT_ID = ATTR.RULE_OBJECT_ID
AND B.RULE_OBJECT_ID = TL.RULE_OBJECT_ID
AND TL.LANGUAGE = 'US';
SAVEPOINT update_rule_objects;
do_update_rule_object(
'N', --Update for Rule Object Instance Parameter
l_rule_object_rec,
p_object_version_number,
x_return_status);
--This validation is done in do_update_rule_object().
l_rule_object_rec.parent_rule_object_id := C_REC.parent_rule_object_id;
do_update_rule_object(
'Y', --Update for Rule Object Instance Parameter
l_rule_object_rec,
C_REC.OBJECT_VERSION_NUMBER,
x_return_status);
ROLLBACK TO update_rule_objects;
ROLLBACK TO update_rule_objects;
ROLLBACK TO update_rule_objects;
END update_rule_object;
* FUN_RULES_PKG.Select_Row
*
* ARGUMENTS
* IN:
* p_init_msg_list Initialize message stack if it is set to
* FND_API.G_TRUE. Default is FND_API.G_FALSE.
* p_rule_objects User customizable objects name.
* IN/OUT:
* OUT:
* x_rule_object_rec Returned class category record.
* x_return_status Return status after the call. The status can
* be FND_API.G_RET_STS_SUCCESS (success),
* FND_API.G_RET_STS_ERROR (error),
* FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
* x_msg_count Number of messages in message stack.
* x_msg_data Message text if x_msg_count is 1.
*
* NOTES
*
* MODIFICATION HISTORY
*
* 10-Sep-2004 Amulya Mishra Created.
*
*/
PROCEDURE get_rule_object_rec (
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_rule_object_name IN VARCHAR2,
p_application_id IN NUMBER,
p_instance_label IN VARCHAR2,
p_org_id IN NUMBER,
x_rule_object_rec OUT NOCOPY RULE_OBJECTS_REC_TYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
BEGIN
--Initialize message list if p_init_msg_list is set to TRUE.
IF FND_API.to_Boolean(p_init_msg_list) THEN
FND_MSG_PUB.initialize;
FUN_RULE_OBJECTS_PKG.Select_Row (
X_RULE_OBJECT_NAME =>x_rule_object_rec.rule_object_name,
X_RULE_OBJECT_ID =>x_rule_object_rec.rule_object_id,
X_APPLICATION_ID =>x_rule_object_rec.application_id,
X_USER_RULE_OBJECT_NAME =>x_rule_object_rec.user_rule_object_name,
X_DESCRIPTION =>x_rule_object_rec.description,
X_RESULT_TYPE =>x_rule_object_rec.result_type,
X_REQUIRED_FLAG =>x_rule_object_rec.required_flag,
X_USE_DEFAULT_VALUE_FLAG =>x_rule_object_rec.use_default_value_flag,
X_DEFAULT_APPLICATION_ID =>x_rule_object_rec.default_application_id,
X_DEFAULT_VALUE =>x_rule_object_rec.default_value,
X_FLEX_VALUE_SET_ID =>x_rule_object_rec.flex_value_set_id,
X_FLEXFIELD_NAME =>x_rule_object_rec.flexfield_name,
X_FLEXFIELD_APP_SHORT_NAME =>x_rule_object_rec.flexfield_app_short_name,
X_MULTI_RULE_RESULT_FLAG =>x_rule_object_rec.multi_rule_result_flag,
X_CREATED_BY_MODULE =>x_rule_object_rec.created_by_module,
X_USE_INSTANCE_FLAG =>x_rule_object_rec.use_instance_flag,
X_INSTANCE_LABEL =>x_rule_object_rec.instance_label,
X_PARENT_RULE_OBJECT_ID =>x_rule_object_rec.parent_rule_object_id,
X_ORG_ID =>x_rule_object_rec.org_id
);
* FUN_RULES_PKG.Select_Row
*
* ARGUMENTS
* IN:
* p_init_msg_list Initialize message stack if it is set to
* FND_API.G_TRUE. Default is FND_API.G_FALSE.
* p_rule_objects User customizable objects name.
* IN/OUT:
* OUT:
* x_rule_object_rec Returned class category record.
* x_return_status Return status after the call. The status can
* be FND_API.G_RET_STS_SUCCESS (success),
* FND_API.G_RET_STS_ERROR (error),
* FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
* x_msg_count Number of messages in message stack.
* x_msg_data Message text if x_msg_count is 1.
*
* NOTES
*
* MODIFICATION HISTORY
*
* 10-Sep-2004 Amulya Mishra Created.
*
*/
PROCEDURE get_rule_object_rec (
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_rule_object_id IN NUMBER,
x_rule_object_rec OUT NOCOPY RULE_OBJECTS_REC_TYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
BEGIN
--Initialize message list if p_init_msg_list is set to TRUE.
IF FND_API.to_Boolean(p_init_msg_list) THEN
FND_MSG_PUB.initialize;
FUN_RULE_OBJECTS_PKG.Select_Row_Rob_Id (
X_RULE_OBJECT_NAME =>x_rule_object_rec.rule_object_name,
X_RULE_OBJECT_ID =>x_rule_object_rec.rule_object_id,
X_APPLICATION_ID =>x_rule_object_rec.application_id,
X_USER_RULE_OBJECT_NAME =>x_rule_object_rec.user_rule_object_name,
X_DESCRIPTION =>x_rule_object_rec.description,
X_RESULT_TYPE =>x_rule_object_rec.result_type,
X_REQUIRED_FLAG =>x_rule_object_rec.required_flag,
X_USE_DEFAULT_VALUE_FLAG =>x_rule_object_rec.use_default_value_flag,
X_DEFAULT_APPLICATION_ID =>x_rule_object_rec.default_application_id,
X_DEFAULT_VALUE =>x_rule_object_rec.default_value,
X_FLEX_VALUE_SET_ID =>x_rule_object_rec.flex_value_set_id,
X_FLEXFIELD_NAME =>x_rule_object_rec.flexfield_name,
X_FLEXFIELD_APP_SHORT_NAME =>x_rule_object_rec.flexfield_app_short_name,
X_MULTI_RULE_RESULT_FLAG =>x_rule_object_rec.multi_rule_result_flag,
X_CREATED_BY_MODULE =>x_rule_object_rec.created_by_module,
X_USE_INSTANCE_FLAG =>x_rule_object_rec.use_instance_flag,
X_INSTANCE_LABEL =>x_rule_object_rec.instance_label,
X_PARENT_RULE_OBJECT_ID =>x_rule_object_rec.parent_rule_object_id,
X_ORG_ID =>x_rule_object_rec.org_id
);
* PROCEDURE delete_rule_object
*
* DESCRIPTION
* Deletes User customizable objects.
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
*
* ARGUMENTS
* IN:
* p_init_msg_list Initialize message stack if it is set to
* FND_API.G_TRUE. Default is FND_API.G_FALSE.
* p_rule_object_name User customizable object Name.
* p_application_id Application Id
* IN/OUT:
* OUT:
* x_return_status Return status after the call. The status can
* be FND_API.G_RET_STS_SUCCESS (success),
* FND_API.G_RET_STS_ERROR (error),
* FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
* x_msg_count Number of messages in message stack.
* x_msg_data Message text if x_msg_count is 1.
*
* NOTES
*
* MODIFICATION HISTORY
*
* 10-Sep-2004 Amulya Mishra Created.
*
*/
PROCEDURE delete_rule_object(
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_rule_object_name IN VARCHAR2,
p_application_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_rule_object_id number;
SELECT
B.APPLICATION_ID,
B.RULE_OBJECT_NAME,
B.INSTANCE_LABEL,
B.ORG_ID
FROM FUN_RULE_OBJECTS_B B, FUN_RULE_OBJECTS_TL TL, FUN_RULE_OBJ_ATTRIBUTES ATTR
WHERE B.PARENT_RULE_OBJECT_ID = p_rule_object_id
AND B.RULE_OBJECT_ID = ATTR.RULE_OBJECT_ID
AND B.RULE_OBJECT_ID = TL.RULE_OBJECT_ID
AND TL.LANGUAGE = 'US';
SAVEPOINT delete_rule_object;
SELECT RULE_OBJECT_ID INTO l_rule_object_id FROM FUN_RULE_OBJECTS_B
WHERE RULE_OBJECT_NAME = p_rule_object_name
AND APPLICATION_ID = p_application_id
AND INSTANCE_LABEL IS NULL
AND ORG_ID IS NULL
AND PARENT_RULE_OBJECT_ID IS NULL;
DELETE FROM FUN_RULE_PARAM_VALUES FRPV
WHERE RULE_DETAIL_ID IN (SELECT RULE_DETAIL_ID
FROM FUN_RULE_DETAILS FRD , FUN_RULE_OBJECTS_B FROB
WHERE FROB.RULE_OBJECT_ID = FRD.RULE_OBJECT_ID
AND FROB.RULE_OBJECT_NAME = P_RULE_OBJECT_NAME);
DELETE FROM FUN_RULE_CRIT_PARAMS_TL FRCP
WHERE CRITERIA_PARAM_ID IN (SELECT CRITERIA_PARAM_ID
FROM FUN_RULE_OBJECTS_B FROB, FUN_RULE_CRIT_PARAMS_B FRCPB
WHERE FROB.RULE_OBJECT_ID = FRCPB.RULE_OBJECT_ID
AND RULE_OBJECT_NAME = P_RULE_OBJECT_NAME);
DELETE FROM FUN_RULE_CRIT_PARAMS_B FRCP
WHERE RULE_OBJECT_ID IN (SELECT RULE_OBJECT_ID
FROM FUN_RULE_OBJECTS_B WHERE
RULE_OBJECT_NAME = P_RULE_OBJECT_NAME);
DELETE FROM FUN_RULE_DETAILS FRD
WHERE RULE_OBJECT_ID IN (SELECT RULE_OBJECT_ID
FROM FUN_RULE_OBJECTS_B WHERE
RULE_OBJECT_NAME = P_RULE_OBJECT_NAME);
DELETE FROM FUN_RULE_CRITERIA FRC
WHERE RULE_DETAIL_ID IN (SELECT RULE_DETAIL_ID
FROM FUN_RULE_DETAILS FRD , FUN_RULE_OBJECTS_B FROB
WHERE FROB.RULE_OBJECT_ID = FRD.RULE_OBJECT_ID
AND FROB.RULE_OBJECT_NAME = P_RULE_OBJECT_NAME);
DELETE FROM FUN_RULE_TEST_MULTIRULE FRTP
WHERE TEST_ID IN (SELECT TEST_ID
FROM FUN_RULE_TESTS FRT , FUN_RULE_OBJECTS_B FROB
WHERE FROB.RULE_OBJECT_ID = FRT.RULE_OBJECT_ID
AND FROB.RULE_OBJECT_NAME = P_RULE_OBJECT_NAME);
DELETE FROM FUN_RULE_TEST_RESULTS FRTR
WHERE TEST_ID IN (SELECT TEST_ID
FROM FUN_RULE_TESTS FRT , FUN_RULE_OBJECTS_B FROB
WHERE FROB.RULE_OBJECT_ID = FRT.RULE_OBJECT_ID
AND FROB.RULE_OBJECT_NAME = P_RULE_OBJECT_NAME);
DELETE FROM FUN_RULE_TEST_MVAL_RES FRTMR
WHERE TEST_ID IN (SELECT TEST_ID
FROM FUN_RULE_TESTS FRT , FUN_RULE_OBJECTS_B FROB
WHERE FROB.RULE_OBJECT_ID = FRT.RULE_OBJECT_ID
AND FROB.RULE_OBJECT_NAME = P_RULE_OBJECT_NAME);
DELETE FROM FUN_RULE_TEST_PARAMS FRTP
WHERE TEST_ID IN (SELECT TEST_ID
FROM FUN_RULE_TESTS FRT , FUN_RULE_OBJECTS_B FROB
WHERE FROB.RULE_OBJECT_ID = FRT.RULE_OBJECT_ID
AND FROB.RULE_OBJECT_NAME = P_RULE_OBJECT_NAME);
DELETE FROM FUN_RULE_TESTS FRT
WHERE RULE_OBJECT_ID IN (SELECT RULE_OBJECT_ID
FROM FUN_RULE_OBJECTS_B WHERE
RULE_OBJECT_NAME = P_RULE_OBJECT_NAME);
FUN_RULE_OBJECTS_PKG.Delete_Row (
X_RULE_OBJECT_NAME =>p_rule_object_name,
X_APPLICATION_ID =>p_application_id
);
delete_rule_object_instance(
p_application_id => C_REC.application_id,
p_rule_object_name => C_REC.rule_object_name,
p_instance_label => C_REC.instance_label,
p_org_id => C_REC.org_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
ROLLBACK TO delete_rule_object;
ROLLBACK TO delete_rule_object;
ROLLBACK TO delete_rule_object;
END delete_rule_object;
* Use this routine to delete a rule object instance. The API deletes a record in the
* FUN_RULE_OBJECTS_B table after checking if the USE_INSTANCE_FLAG is Y or not.
*
*
* p_init_msg_list Initialize message stack if it is set to FND_API.G_TRUE. Default is FND_API.G_FALSE.
* p_application_id Application identifier
* p_rule_object_name Name of Rule Object
* p_instance_label Name of the Instance.
* x_return_status Return status after the call.
* x_msg_count Number of messages in message stack.
* x_msg_data Message text if x_msg_count is 1.
*
* 27-Dec-2005 Amulya Mishra Created
*/
PROCEDURE delete_rule_object_instance(
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_application_id IN NUMBER,
p_rule_object_name IN VARCHAR2,
p_instance_label IN VARCHAR2,
p_org_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_rule_object_id NUMBER;
SAVEPOINT delete_rule_object_instance;
SELECT RULE_OBJECT_ID INTO l_rule_object_id FROM FUN_RULE_OBJECTS_B
WHERE RULE_OBJECT_NAME = p_rule_object_name
AND APPLICATION_ID = p_application_id
AND ( (INSTANCE_LABEL IS NULL AND p_instance_label IS NULL) OR
(INSTANCE_LABEL IS NOT NULL AND p_instance_label IS NOT NULL AND INSTANCE_LABEL = p_instance_label))
AND ( (ORG_ID IS NULL AND p_org_id IS NULL) OR
( ORG_ID IS NOT NULL AND p_org_id IS NOT NULL AND ORG_ID = p_org_id))
AND PARENT_RULE_OBJECT_ID IS NOT NULL;
DELETE FROM FUN_RULE_PARAM_VALUES FRPV
WHERE RULE_DETAIL_ID IN (SELECT RULE_DETAIL_ID
FROM FUN_RULE_DETAILS FRD , FUN_RULE_OBJECTS_B FROB
WHERE FROB.RULE_OBJECT_ID = FRD.RULE_OBJECT_ID
AND FROB.RULE_OBJECT_NAME = P_RULE_OBJECT_NAME
AND FROB.APPLICATION_ID = P_APPLICATION_ID
AND ( (INSTANCE_LABEL IS NULL AND p_instance_label IS NULL) OR
(INSTANCE_LABEL IS NOT NULL AND p_instance_label IS NOT NULL AND INSTANCE_LABEL = p_instance_label))
AND ( (ORG_ID IS NULL AND p_org_id IS NULL) OR
( ORG_ID IS NOT NULL AND p_org_id IS NOT NULL AND ORG_ID = p_org_id))
AND PARENT_RULE_OBJECT_ID IS NOT NULL);
DELETE FROM FUN_RULE_CRIT_PARAMS_TL FRCP
WHERE CRITERIA_PARAM_ID IN (SELECT CRITERIA_PARAM_ID
FROM FUN_RULE_OBJECTS_B FROB, FUN_RULE_CRIT_PARAMS_B FRCPB
WHERE FROB.RULE_OBJECT_ID = FRCPB.RULE_OBJECT_ID
AND RULE_OBJECT_NAME = P_RULE_OBJECT_NAME
AND FROB.APPLICATION_ID = P_APPLICATION_ID
AND ( (INSTANCE_LABEL IS NULL AND p_instance_label IS NULL) OR
(INSTANCE_LABEL IS NOT NULL AND p_instance_label IS NOT NULL AND INSTANCE_LABEL = p_instance_label))
AND ( (ORG_ID IS NULL AND p_org_id IS NULL) OR
( ORG_ID IS NOT NULL AND p_org_id IS NOT NULL AND ORG_ID = p_org_id))
AND PARENT_RULE_OBJECT_ID IS NOT NULL);
DELETE FROM FUN_RULE_CRIT_PARAMS_B FRCP
WHERE RULE_OBJECT_ID IN (SELECT RULE_OBJECT_ID
FROM FUN_RULE_OBJECTS_B WHERE
RULE_OBJECT_NAME = P_RULE_OBJECT_NAME
AND APPLICATION_ID = P_APPLICATION_ID
AND ( (INSTANCE_LABEL IS NULL AND p_instance_label IS NULL) OR
(INSTANCE_LABEL IS NOT NULL AND p_instance_label IS NOT NULL AND INSTANCE_LABEL = p_instance_label))
AND ( (ORG_ID IS NULL AND p_org_id IS NULL) OR
( ORG_ID IS NOT NULL AND p_org_id IS NOT NULL AND ORG_ID = p_org_id))
AND PARENT_RULE_OBJECT_ID IS NOT NULL);
DELETE FROM FUN_RULE_DETAILS FRD
WHERE RULE_OBJECT_ID IN (SELECT RULE_OBJECT_ID
FROM FUN_RULE_OBJECTS_B WHERE
RULE_OBJECT_NAME = P_RULE_OBJECT_NAME
AND APPLICATION_ID = P_APPLICATION_ID
AND ( (INSTANCE_LABEL IS NULL AND p_instance_label IS NULL) OR
(INSTANCE_LABEL IS NOT NULL AND p_instance_label IS NOT NULL AND INSTANCE_LABEL = p_instance_label))
AND ( (ORG_ID IS NULL AND p_org_id IS NULL) OR
( ORG_ID IS NOT NULL AND p_org_id IS NOT NULL AND ORG_ID = p_org_id))
AND PARENT_RULE_OBJECT_ID IS NOT NULL);
DELETE FROM FUN_RULE_CRITERIA FRC
WHERE RULE_DETAIL_ID IN (SELECT RULE_DETAIL_ID
FROM FUN_RULE_DETAILS FRD , FUN_RULE_OBJECTS_B FROB
WHERE FROB.RULE_OBJECT_ID = FRD.RULE_OBJECT_ID
AND RULE_OBJECT_NAME = P_RULE_OBJECT_NAME
AND FROB.APPLICATION_ID = P_APPLICATION_ID
AND ( (INSTANCE_LABEL IS NULL AND p_instance_label IS NULL) OR
(INSTANCE_LABEL IS NOT NULL AND p_instance_label IS NOT NULL AND INSTANCE_LABEL = p_instance_label))
AND ( (ORG_ID IS NULL AND p_org_id IS NULL) OR
( ORG_ID IS NOT NULL AND p_org_id IS NOT NULL AND ORG_ID = p_org_id))
AND PARENT_RULE_OBJECT_ID IS NOT NULL);
DELETE FROM FUN_RULE_TEST_MULTIRULE FRTP
WHERE TEST_ID IN (SELECT TEST_ID
FROM FUN_RULE_TESTS FRT , FUN_RULE_OBJECTS_B FROB
WHERE FROB.RULE_OBJECT_ID = FRT.RULE_OBJECT_ID
AND RULE_OBJECT_NAME = P_RULE_OBJECT_NAME
AND FROB.APPLICATION_ID = P_APPLICATION_ID
AND ( (INSTANCE_LABEL IS NULL AND p_instance_label IS NULL) OR
(INSTANCE_LABEL IS NOT NULL AND p_instance_label IS NOT NULL AND INSTANCE_LABEL = p_instance_label))
AND ( (ORG_ID IS NULL AND p_org_id IS NULL) OR
( ORG_ID IS NOT NULL AND p_org_id IS NOT NULL AND ORG_ID = p_org_id))
AND PARENT_RULE_OBJECT_ID IS NOT NULL);
DELETE FROM FUN_RULE_TEST_RESULTS FRTR
WHERE TEST_ID IN (SELECT TEST_ID
FROM FUN_RULE_TESTS FRT , FUN_RULE_OBJECTS_B FROB
WHERE FROB.RULE_OBJECT_ID = FRT.RULE_OBJECT_ID
AND RULE_OBJECT_NAME = P_RULE_OBJECT_NAME
AND FROB.APPLICATION_ID = P_APPLICATION_ID
AND ( (INSTANCE_LABEL IS NULL AND p_instance_label IS NULL) OR
(INSTANCE_LABEL IS NOT NULL AND p_instance_label IS NOT NULL AND INSTANCE_LABEL = p_instance_label))
AND ( (ORG_ID IS NULL AND p_org_id IS NULL) OR
( ORG_ID IS NOT NULL AND p_org_id IS NOT NULL AND ORG_ID = p_org_id))
AND PARENT_RULE_OBJECT_ID IS NOT NULL);
DELETE FROM FUN_RULE_TEST_MVAL_RES FRTMR
WHERE TEST_ID IN (SELECT TEST_ID
FROM FUN_RULE_TESTS FRT , FUN_RULE_OBJECTS_B FROB
WHERE FROB.RULE_OBJECT_ID = FRT.RULE_OBJECT_ID
AND RULE_OBJECT_NAME = P_RULE_OBJECT_NAME
AND FROB.APPLICATION_ID = P_APPLICATION_ID
AND ( (INSTANCE_LABEL IS NULL AND p_instance_label IS NULL) OR
(INSTANCE_LABEL IS NOT NULL AND p_instance_label IS NOT NULL AND INSTANCE_LABEL = p_instance_label))
AND ( (ORG_ID IS NULL AND p_org_id IS NULL) OR
( ORG_ID IS NOT NULL AND p_org_id IS NOT NULL AND ORG_ID = p_org_id))
AND PARENT_RULE_OBJECT_ID IS NOT NULL);
DELETE FROM FUN_RULE_TEST_PARAMS FRTP
WHERE TEST_ID IN (SELECT TEST_ID
FROM FUN_RULE_TESTS FRT , FUN_RULE_OBJECTS_B FROB
WHERE FROB.RULE_OBJECT_ID = FRT.RULE_OBJECT_ID
AND RULE_OBJECT_NAME = P_RULE_OBJECT_NAME
AND FROB.APPLICATION_ID = P_APPLICATION_ID
AND ( (INSTANCE_LABEL IS NULL AND p_instance_label IS NULL) OR
(INSTANCE_LABEL IS NOT NULL AND p_instance_label IS NOT NULL AND INSTANCE_LABEL = p_instance_label))
AND ( (ORG_ID IS NULL AND p_org_id IS NULL) OR
( ORG_ID IS NOT NULL AND p_org_id IS NOT NULL AND ORG_ID = p_org_id))
AND PARENT_RULE_OBJECT_ID IS NOT NULL);
DELETE FROM FUN_RULE_TESTS FRT
WHERE RULE_OBJECT_ID IN (SELECT RULE_OBJECT_ID
FROM FUN_RULE_OBJECTS_B WHERE
RULE_OBJECT_NAME = P_RULE_OBJECT_NAME
AND APPLICATION_ID = P_APPLICATION_ID
AND ( (INSTANCE_LABEL IS NULL AND p_instance_label IS NULL) OR
(INSTANCE_LABEL IS NOT NULL AND p_instance_label IS NOT NULL AND INSTANCE_LABEL = p_instance_label))
AND ( (ORG_ID IS NULL AND p_org_id IS NULL) OR
( ORG_ID IS NOT NULL AND p_org_id IS NOT NULL AND ORG_ID = p_org_id))
AND PARENT_RULE_OBJECT_ID IS NOT NULL);
FUN_RULE_OBJECTS_PKG.Delete_Row (
X_RULE_OBJECT_ID =>l_rule_object_id
);
ROLLBACK TO delete_rule_object_instance;
ROLLBACK TO delete_rule_object_instance;
ROLLBACK TO delete_rule_object_instance;
END delete_rule_object_instance;
SELECT 'Y' INTO l_dummy
FROM FUN_RULE_CRIT_PARAMS_B FRCP, FUN_RULE_OBJECTS_B FROB
WHERE FROB.RULE_OBJECT_ID = FRCP.RULE_OBJECT_ID
AND FROB.RULE_OBJECT_NAME = p_rule_object_name
AND FRCP.PARAM_NAME = p_parameter_name;
SELECT 'Y' INTO l_param_value
FROM FUN_RULE_OBJECTS_B
WHERE RULE_OBJECT_NAME = p_rule_object_name
AND APPLICATION_ID = p_application_id
AND ( (INSTANCE_LABEL IS NULL AND p_instance_label IS NULL) OR
(INSTANCE_LABEL IS NOT NULL AND p_instance_label IS NOT NULL AND INSTANCE_LABEL = p_instance_label))
AND ( (ORG_ID IS NULL AND p_org_id IS NULL) OR
( ORG_ID IS NOT NULL AND p_org_id IS NOT NULL AND ORG_ID = p_org_id))
AND PARENT_RULE_OBJECT_ID IS NOT NULL;
Delete all of the original rules and test cases.
End if
Else if P_INSTANCE_FLAG = 'N' then
Set USE_INSTANCE_FLAG to 'N' for the rule object
Delete all object instances
Else
Invalid P_INSTANCE_FLAG -- error
End if
*/
PROCEDURE convert_use_instance(
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_rule_object_id IN NUMBER,
p_use_instance_flag IN VARCHAR2,
p_instance_label IN VARCHAR2 DEFAULT NULL,
P_ORG_ID IN NUMBER DEFAULT NULL,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
CURSOR C_RULE_OBJECT_INSTANCES(p_rule_object_id NUMBER) IS
SELECT APPLICATION_ID,RULE_OBJECT_NAME,
RULE_OBJECT_ID, INSTANCE_LABEL, ORG_ID
FROM FUN_RULE_OBJECTS_B
WHERE PARENT_RULE_OBJECT_ID = p_rule_object_id;
SELECT USE_INSTANCE_FLAG , APPLICATION_ID, RULE_OBJECT_NAME
INTO l_use_instance_flag, l_application_id, l_rule_object_name
FROM FUN_RULE_OBJECTS_B
WHERE RULE_OBJECT_ID = p_rule_object_id;
UPDATE FUN_RULE_OBJECTS_B
SET USE_INSTANCE_FLAG ='Y'
WHERE RULE_OBJECT_ID = p_rule_object_id;
UPDATE FUN_RULE_DETAILS
SET RULE_OBJECT_ID = x_rule_object_id
WHERE RULE_OBJECT_ID = p_rule_object_id;
UPDATE FUN_RULE_TESTS
SET RULE_OBJECT_ID = x_rule_object_id
WHERE RULE_OBJECT_ID = p_rule_object_id;
delete_rule_object(
p_rule_object_name => l_rule_object_name,
p_application_id => l_application_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count ,
x_msg_data => x_msg_data
);
SELECT PARENT_RULE_OBJECT_ID
INTO l_parent_rule_object_id
FROM FUN_RULE_OBJECTS_B
WHERE RULE_OBJECT_ID = p_rule_object_id;
UPDATE FUN_RULE_OBJECTS_B
SET USE_INSTANCE_FLAG ='N',
INSTANCE_LABEL = null,
ORG_ID = null,
PARENT_RULE_OBJECT_ID = null
WHERE RULE_OBJECT_ID = p_rule_object_id;
delete_rule_object_instance(
p_application_id => c_rec.application_id,
p_rule_object_name => c_rec.rule_object_name,
p_instance_label => c_rec.instance_label,
p_org_id => c_rec.org_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);