[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT rule_statement_id
FROM ahl_mc_rule_statements stmt
WHERE rule_id = p_rule_id
AND top_rule_stmt_flag = 'T';
SELECT rs.operator, rs.subject_id, rs.object_id,
fnd.meaning, rs.object_version_number
FROM ahl_mc_rule_statements rs, fnd_lookups fnd
WHERE rs.rule_statement_id = p_rulestmt_id
AND rs.operator = fnd.lookup_code
AND fnd.lookup_type = 'AHL_MC_RULE_ALL_OPERATORS';
SELECT rs.RULE_STATEMENT_ID,
rs.subject_ID POSITION_ID,
AHL_MC_PATH_POSITION_PVT.get_posref_by_id(rs.subject_id, FND_API.G_FALSE) POSITION_MEANING,
rs.negation_flag,
rs.operator,
rs.object_version_number,
fnd.meaning operator_meaning,
rs.OBJECT_ID ,
rs.OBJECT_TYPE ,
rs.OBJECT_ATTRIBUTE1 ,
rs.OBJECT_ATTRIBUTE2 ,
rs.OBJECT_ATTRIBUTE3 ,
rs.OBJECT_ATTRIBUTE4 ,
rs.OBJECT_ATTRIBUTE5 ,
rs.ATTRIBUTE_CATEGORY ,
rs.ATTRIBUTE1 ,
rs.ATTRIBUTE2 ,
rs.ATTRIBUTE3 ,
rs.ATTRIBUTE4 ,
rs.ATTRIBUTE5 ,
rs.ATTRIBUTE6 ,
rs.ATTRIBUTE7 ,
rs.ATTRIBUTE8 ,
rs.ATTRIBUTE9 ,
rs.ATTRIBUTE10 ,
rs.ATTRIBUTE11 ,
rs.ATTRIBUTE12 ,
rs.ATTRIBUTE13 ,
rs.ATTRIBUTE14 ,
rs.ATTRIBUTE15
FROM ahl_mc_rule_statements rs, fnd_lookups fnd
WHERE rs.rule_statement_id = p_rulestmt_id
AND fnd.lookup_code = decode (rs.negation_flag, 'T', rs.operator||'_NOT', rs.operator)
AND fnd.lookup_type = 'AHL_MC_RULE_ALL_OPERATORS';
SELECT meaning
FROM fnd_lookups
WHERE lookup_code = p_object_type
AND lookup_type = 'AHL_MC_RULE_OBJECT_TYPES';
SELECT distinct concatenated_segments
FROM MTL_SYSTEM_ITEMS_KFV
WHERE INVENTORY_ITEM_ID = p_inv_item_id;
SELECT AHL_MC_PATH_POSITION_PVT.get_posref_by_id(p_position_id)
FROM DUAL;
SELECT distinct name
FROM ahl_mc_headers_b
WHERE mc_id = p_mc_id;
SELECT ahl_mc_rule_statements_s.nextval FROM DUAL;
PROCEDURE Insert_Rule (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_module IN VARCHAR2 := 'JSP',
p_rule_stmt_tbl IN AHL_MC_RULE_PVT.UI_Rule_Stmt_Tbl_Type,
p_x_rule_rec IN OUT NOCOPY AHL_MC_RULE_PVT.Rule_Rec_Type)
IS
--
CURSOR check_mc_status_csr (p_header_id IN NUMBER) IS
SELECT config_status_code, config_status_meaning
FROM ahl_mc_headers_v header
WHERE header.mc_header_id = p_header_id;
SELECT 'X'
FROM FND_LOOKUPS
WHERE lookup_code = p_type
AND lookup_type = 'AHL_MC_RULE_TYPES';
SELECT 'X'
FROM AHL_MC_RULES_B
WHERE mc_header_id = p_mc_header_id
AND rule_name = p_name;
l_api_name CONSTANT VARCHAR2(30) := 'Insert_Rule';
SAVEPOINT Insert_Rule_pvt;
SELECT AHL_MC_RULES_B_S.nextval
INTO p_x_rule_rec.rule_id
FROM dual;
AHL_MC_RULES_PKG.INSERT_ROW (
X_ROWID => l_row_id,
X_RULE_ID => p_x_rule_rec.rule_id,
X_OBJECT_VERSION_NUMBER => 1,
X_RULE_NAME => p_x_rule_rec.rule_name,
X_MC_HEADER_ID => p_x_rule_rec.mc_header_id,
X_RULE_TYPE_CODE => p_x_rule_rec.rule_type_code,
X_ACTIVE_START_DATE => p_x_rule_rec.active_start_date,
X_ACTIVE_END_DATE => p_x_rule_rec.active_end_date,
X_ATTRIBUTE_CATEGORY => p_x_rule_rec.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => p_x_rule_rec.ATTRIBUTE1,
X_ATTRIBUTE2 => p_x_rule_rec.ATTRIBUTE2,
X_ATTRIBUTE3 => p_x_rule_rec.ATTRIBUTE3,
X_ATTRIBUTE4 => p_x_rule_rec.ATTRIBUTE4,
X_ATTRIBUTE5 => p_x_rule_rec.ATTRIBUTE5,
X_ATTRIBUTE6 => p_x_rule_rec.ATTRIBUTE6,
X_ATTRIBUTE7 => p_x_rule_rec.ATTRIBUTE7,
X_ATTRIBUTE8 => p_x_rule_rec.ATTRIBUTE8,
X_ATTRIBUTE9 => p_x_rule_rec.ATTRIBUTE9,
X_ATTRIBUTE10 => p_x_rule_rec.ATTRIBUTE10,
X_ATTRIBUTE11 => p_x_rule_rec.ATTRIBUTE11,
X_ATTRIBUTE12 => p_x_rule_rec.ATTRIBUTE12,
X_ATTRIBUTE13 => p_x_rule_rec.ATTRIBUTE13,
X_ATTRIBUTE14 => p_x_rule_rec.ATTRIBUTE14,
X_ATTRIBUTE15 => p_x_rule_rec.ATTRIBUTE15,
X_DESCRIPTION => p_x_rule_rec.description,
X_CREATION_DATE => SYSDATE,
X_CREATED_BY => Fnd_Global.USER_ID,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => Fnd_Global.USER_ID,
X_LAST_UPDATE_LOGIN => Fnd_Global.LOGIN_ID);
AHL_MC_RULE_STMT_PVT.Insert_Rule_Stmt (
p_api_version => 1.0,
p_commit => FND_API.G_FALSE,
p_module => p_module,
p_x_rule_stmt_rec => l_rule_stmt_tbl(i),
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
UPDATE ahl_mc_headers_b
SET config_status_code = 'DRAFT'
WHERE mc_header_id = p_x_rule_rec.mc_header_id;
Rollback to Insert_Rule_pvt;
Rollback to Insert_Rule_pvt;
Rollback to Insert_Rule_pvt;
END Insert_Rule;
PROCEDURE Update_Rule (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_module IN VARCHAR2 := 'JSP',
p_rule_rec IN AHL_MC_RULE_PVT.Rule_Rec_Type,
p_rule_stmt_tbl IN AHL_MC_RULE_PVT.UI_Rule_Stmt_Tbl_Type)
IS
--
CURSOR get_rule_rec_csr (p_rule_id IN NUMBER) IS
SELECT *
FROM AHL_MC_RULES_VL
WHERE rule_id = p_rule_id;
SELECT 'X'
FROM FND_LOOKUPS
WHERE lookup_code = p_type
AND lookup_type = 'AHL_MC_RULE_TYPES';
SELECT 'X'
FROM AHL_MC_RULES_B
WHERE mc_header_id = p_mc_header_id
AND rule_name = p_name
AND rule_id <> p_rule_id;
SELECT rule_statement_id
FROM ahl_mc_rule_statements
WHERE rule_id = p_rule_id;
SELECT config_status_code, config_status_meaning
FROM ahl_mc_rules_b rule, ahl_mc_headers_v header
WHERE rule.mc_header_id = header.mc_header_id
AND rule.rule_id = p_rule_id;
l_api_name CONSTANT VARCHAR2(30) := 'Update_Rule';
SAVEPOINT Update_Rule_pvt;
AHL_MC_RULES_PKG.UPDATE_ROW (
X_RULE_ID => l_rule_rec.rule_id,
X_OBJECT_VERSION_NUMBER => l_rule_rec.object_version_number +1,
X_MC_HEADER_ID => l_rule_rec.mc_header_id,
X_RULE_NAME => l_rule_rec.rule_name,
X_RULE_TYPE_CODE => l_rule_rec.rule_type_code,
X_ACTIVE_START_DATE => l_rule_rec.active_start_date,
X_ACTIVE_END_DATE => l_rule_rec.active_end_date,
X_ATTRIBUTE_CATEGORY => l_rule_rec.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => l_rule_rec.ATTRIBUTE1,
X_ATTRIBUTE2 => l_rule_rec.ATTRIBUTE2,
X_ATTRIBUTE3 => l_rule_rec.ATTRIBUTE3,
X_ATTRIBUTE4 => l_rule_rec.ATTRIBUTE4,
X_ATTRIBUTE5 => l_rule_rec.ATTRIBUTE5,
X_ATTRIBUTE6 => l_rule_rec.ATTRIBUTE6,
X_ATTRIBUTE7 => l_rule_rec.ATTRIBUTE7,
X_ATTRIBUTE8 => l_rule_rec.ATTRIBUTE8,
X_ATTRIBUTE9 => l_rule_rec.ATTRIBUTE9,
X_ATTRIBUTE10 => l_rule_rec.ATTRIBUTE10,
X_ATTRIBUTE11 => l_rule_rec.ATTRIBUTE11,
X_ATTRIBUTE12 => l_rule_rec.ATTRIBUTE12,
X_ATTRIBUTE13 => l_rule_rec.ATTRIBUTE13,
X_ATTRIBUTE14 => l_rule_rec.ATTRIBUTE14,
X_ATTRIBUTE15 => l_rule_rec.ATTRIBUTE15,
X_DESCRIPTION => l_rule_rec.description,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => Fnd_Global.USER_ID,
X_LAST_UPDATE_LOGIN => Fnd_Global.LOGIN_ID);
DELETE FROM AHL_MC_RULE_STATEMENTS
WHERE rule_statement_id = l_rule_stmt_id;
AHL_MC_RULE_STMT_PVT.Insert_Rule_Stmt (
p_api_version => 1.0,
p_commit => FND_API.G_FALSE,
p_x_rule_stmt_rec => l_rule_stmt_tbl(i),
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
AHL_MC_RULE_STMT_PVT.Update_Rule_Stmt (
p_api_version => 1.0,
p_commit => FND_API.G_FALSE,
p_rule_stmt_rec => l_rule_stmt_tbl(i),
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
UPDATE ahl_mc_headers_b
SET config_status_code = 'DRAFT'
WHERE mc_header_id = l_rule_rec.mc_header_id;
Rollback to Update_Rule_pvt;
Rollback to Update_Rule_pvt;
Rollback to Update_Rule_pvt;
END Update_Rule;
PROCEDURE Delete_Rule (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_rule_rec IN RULE_REC_TYPE)
IS
--
CURSOR check_obj_ver_csr (p_rule_id IN NUMBER, p_obj_ver IN NUMBER) IS
SELECT 'X'
FROM ahl_mc_rules_b rule
WHERE rule.object_version_number = p_obj_ver
AND rule.rule_id = p_rule_id;
SELECT config_status_code, config_status_meaning, header.mc_header_id
FROM ahl_mc_rules_b rule, ahl_mc_headers_v header
WHERE rule.mc_header_id = header.mc_header_id
AND rule.rule_id = p_rule_id;
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Rule';
SAVEPOINT Delete_Rule_pvt;
AHL_MC_RULE_STMT_PVT.Delete_Rule_Stmts (
p_api_version => 1.0,
p_commit => FND_API.G_FALSE,
p_rule_id => p_rule_rec.rule_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
AHL_MC_RULES_PKG.DELETE_ROW ( X_RULE_ID => p_rule_rec.rule_id);
UPDATE ahl_mc_headers_b
SET config_status_code = 'DRAFT'
WHERE mc_header_id = l_mc_header_id;
Rollback to Delete_Rule_pvt;
Rollback to Delete_Rule_pvt;
Rollback to Delete_Rule_pvt;
END Delete_Rule;
SELECT *
FROM AHL_MC_RULES_VL
WHERE MC_HEADER_ID = p_mc_header_id;
SELECT rule_statement_id
FROM AHL_MC_RULE_STATEMENTS
WHERE rule_id = p_rule_id
AND top_rule_stmt_flag = 'T';
SELECT AHL_MC_RULES_B_S.nextval
INTO l_new_rule_id
FROM dual;
AHL_MC_RULES_PKG.INSERT_ROW (
X_ROWID => l_row_id,
X_RULE_ID => l_new_rule_id,
X_OBJECT_VERSION_NUMBER => 1,
X_RULE_NAME => l_rule_rec.rule_name,
X_MC_HEADER_ID => p_to_mc_header_id,
X_RULE_TYPE_CODE => l_rule_rec.rule_type_code,
X_ACTIVE_START_DATE => l_rule_rec.active_start_date,
X_ACTIVE_END_DATE => l_rule_rec.active_end_date,
X_ATTRIBUTE_CATEGORY => l_rule_rec.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => l_rule_rec.ATTRIBUTE1,
X_ATTRIBUTE2 => l_rule_rec.ATTRIBUTE2,
X_ATTRIBUTE3 => l_rule_rec.ATTRIBUTE3,
X_ATTRIBUTE4 => l_rule_rec.ATTRIBUTE4,
X_ATTRIBUTE5 => l_rule_rec.ATTRIBUTE5,
X_ATTRIBUTE6 => l_rule_rec.ATTRIBUTE6,
X_ATTRIBUTE7 => l_rule_rec.ATTRIBUTE7,
X_ATTRIBUTE8 => l_rule_rec.ATTRIBUTE8,
X_ATTRIBUTE9 => l_rule_rec.ATTRIBUTE9,
X_ATTRIBUTE10 => l_rule_rec.ATTRIBUTE10,
X_ATTRIBUTE11 => l_rule_rec.ATTRIBUTE11,
X_ATTRIBUTE12 => l_rule_rec.ATTRIBUTE12,
X_ATTRIBUTE13 => l_rule_rec.ATTRIBUTE13,
X_ATTRIBUTE14 => l_rule_rec.ATTRIBUTE14,
X_ATTRIBUTE15 => l_rule_rec.ATTRIBUTE15,
X_DESCRIPTION => l_rule_rec.description,
X_CREATION_DATE => SYSDATE,
X_CREATED_BY => Fnd_Global.USER_ID,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => Fnd_Global.USER_ID,
X_LAST_UPDATE_LOGIN => Fnd_Global.LOGIN_ID);
UPDATE AHL_MC_RULE_STATEMENTS
SET top_rule_stmt_flag = 'T'
WHERE rule_statement_id = l_new_stmt_id;
PROCEDURE Delete_Rules_For_MC (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_mc_header_id IN NUMBER)
IS
--
CURSOR get_rule_ids_csr (p_mc_header_id IN NUMBER) IS
SELECT rule_id, object_version_number
FROM AHL_MC_RULES_B
WHERE MC_HEADER_ID = p_mc_header_id;
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Rules_For_Mc';
SAVEPOINT Delete_Rules_For_Mc_pvt;
AHL_MC_RULE_PVT.Delete_Rule (
p_api_version => 1.0,
p_commit => FND_API.G_FALSE,
p_rule_rec => l_rule_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
Rollback to Delete_Rules_For_Mc_pvt;
Rollback to Delete_Rules_For_Mc_pvt;
Rollback to Delete_Rules_For_Mc_pvt;
END Delete_Rules_For_MC;
SELECT rul.rule_id,
rul.object_version_number,
rul.mc_header_id,
rul.rule_name,
rul.rule_type_code,
lookup.meaning rule_type_meaning,
rul.active_start_date,
rul.active_end_date,
rul.description,
rul.attribute_category,
rul.attribute1,
rul.attribute2,
rul.attribute3,
rul.attribute4,
rul.attribute5,
rul.attribute6,
rul.attribute7,
rul.attribute8,
rul.attribute9,
rul.attribute10,
rul.attribute11,
rul.attribute12,
rul.attribute13,
rul.attribute14,
rul.attribute15
FROM AHL_MC_RULES_VL rul, FND_LOOKUPS lookup
WHERE rul.rule_type_code = lookup.lookup_code
AND lookup.lookup_type = 'AHL_MC_RULE_TYPES'
AND rul.mc_header_id = p_mc_header_id
AND rul.rule_id IN (
SELECT rst.rule_id
FROM AHL_MC_RULE_STATEMENTS rst, AHL_MC_PATH_POSITIONS pst
WHERE rst.subject_type = 'POSITION'
AND rst.subject_id = pst.path_position_id
AND p_encoded_path LIKE pst.encoded_path_position
AND p_size = (select COUNT(path_position_node_id) FROM
AHL_MC_PATH_POSITION_NODES where path_position_id=pst.path_position_id)
UNION ALL
SELECT rst.rule_id
FROM AHL_MC_RULE_STATEMENTS rst, AHL_MC_PATH_POSITIONS pst
WHERE (rst.object_type = 'ITEM_AS_POSITION'
OR rst.object_type = 'CONFIG_AS_POSITION')
AND rst.object_id = pst.path_position_id
AND p_encoded_path LIKE pst.encoded_path_position
AND p_size = (select COUNT(path_position_node_id) FROM
AHL_MC_PATH_POSITION_NODES where path_position_id=pst.path_position_id));