[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'x'
FROM MTL_UNITS_OF_MEASURE
WHERE uom_code = p_uom_code;
PROCEDURE Delete_Counter_Rule
(
p_ctr_update_rule_id IN NUMBER,
p_object_ver_num IN NUMBER
);
SELECT 'x'
FROM ahl_mc_relationships
WHERE mc_header_id = p_x_node_rec.mc_header_id AND
position_key = p_x_node_rec.position_key;
SELECT ahl_mc_rel_pos_key_s.nextval INTO p_x_node_rec.position_key FROM DUAL;
SELECT ahl_mc_relationships_s.nextval INTO p_x_node_rec.relationship_id FROM DUAL;
-- 4. Insert the node record into AHL_MC_RELATIONSHIPS table
INSERT INTO AHL_MC_RELATIONSHIPS
(
RELATIONSHIP_ID,
POSITION_REF_CODE,
PARENT_RELATIONSHIP_ID,
ITEM_GROUP_ID,
UOM_CODE,
QUANTITY,
DISPLAY_ORDER,
POSITION_NECESSITY_CODE,
POSITION_KEY,
MC_HEADER_ID,
ACTIVE_START_DATE,
ACTIVE_END_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER,
SECURITY_GROUP_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
--R12
--priyan MEL-CDL
ATA_CODE
)
VALUES
(
p_x_node_rec.relationship_id,
p_x_node_rec.position_ref_code,
p_x_node_rec.parent_relationship_id,
p_x_node_rec.item_group_id,
p_x_node_rec.uom_code,
p_x_node_rec.quantity,
p_x_node_rec.display_order,
p_x_node_rec.position_necessity_code,
p_x_node_rec.position_key,
p_x_node_rec.mc_header_id,
TRUNC(p_x_node_rec.active_start_date),
TRUNC(p_x_node_rec.active_end_date),
G_SYSDATE,
G_USER_ID,
G_SYSDATE,
G_USER_ID,
G_LOGIN_ID,
p_x_node_rec.object_version_number,
p_x_node_rec.security_group_id,
p_x_node_rec.attribute_category,
p_x_node_rec.attribute1,
p_x_node_rec.attribute2,
p_x_node_rec.attribute3,
p_x_node_rec.attribute4,
p_x_node_rec.attribute5,
p_x_node_rec.attribute6,
p_x_node_rec.attribute7,
p_x_node_rec.attribute8,
p_x_node_rec.attribute9,
p_x_node_rec.attribute10,
p_x_node_rec.attribute11,
p_x_node_rec.attribute12,
p_x_node_rec.attribute13,
p_x_node_rec.attribute14,
p_x_node_rec.attribute15,
--R12
--priyan MEL-CDL
p_x_node_rec.ata_code
);
'Counter rule ['||p_x_counter_rules_tbl(i).ctr_update_rule_id||'] created'
);
-- Define cursor check_poskey_update to verify whether the position key for a MC node is updated
CURSOR check_poskey_update
IS
SELECT 'x'
FROM ahl_mc_relationships
WHERE relationship_id = p_x_node_rec.relationship_id AND
position_key <> p_x_node_rec.position_key;
SELECT active_end_date
FROM ahl_mc_relationships
WHERE relationship_id = p_x_node_rec.relationship_id;
SELECT rel.ata_code, rel.position_ref_meaning , mch.name
FROM ahl_mc_relationships_v rel, ahl_mc_headers_b mch
WHERE rel.relationship_id IN
(
SELECT relationship_id
FROM ahl_mc_config_relations
WHERE mc_header_id IN
(
SELECT mc_header_id
FROM ahl_mc_relationships
WHERE relationship_id = p_rel_id
)
)
AND rel.mc_header_id = mch.mc_header_id;
SELECT rel.ata_code , mch.name, rel.position_ref_meaning
FROM ahl_mc_relationships_v rel, ahl_mc_headers_b mch
WHERE rel.mc_header_id IN
(
SELECT mc_header_id
FROM ahl_mc_config_relations
WHERE relationship_id = p_rel_id
)
AND parent_relationship_id IS NULL
AND mch.mc_header_id = rel.mc_header_id;
SELECT 'x'
FROM ahl_mc_relationships
WHERE parent_relationship_id is null AND
relationship_id = p_x_node_rec.relationship_id;
SELECT 'x'
FROM ahl_mc_relationships
WHERE parent_relationship_id = p_x_node_rec.relationship_id AND
G_TRUNC_DATE < trunc(nvl(active_end_date, G_SYSDATE + 1));
IF (p_x_node_rec.operation_flag = G_DML_UPDATE)
THEN
-- [node is also being modified]
-- 1a.i. Validate config_status_code of the MC is 'DRAFT' or 'APPROVAL_REJECTED'
l_header_status := Get_MC_Status(null, p_x_node_rec.mc_header_id);
SELECT ahl_mc_rel_pos_key_s.nextval INTO p_x_node_rec.position_key FROM DUAL;
OPEN check_poskey_update;
FETCH check_poskey_update INTO l_dummy_varchar;
IF (check_poskey_update%FOUND)
THEN
FND_MESSAGE.Set_Name('AHL', 'AHL_MC_POSKEY_NOUPD');
CLOSE check_poskey_update;
-- 1d. Update the node record in AHL_MC_RELATIONSHIPS table
UPDATE AHL_MC_RELATIONSHIPS
SET POSITION_REF_CODE = p_x_node_rec.position_ref_code,
ITEM_GROUP_ID = p_x_node_rec.item_group_id,
UOM_CODE = p_x_node_rec.uom_code,
QUANTITY = p_x_node_rec.quantity,
DISPLAY_ORDER = p_x_node_rec.display_order,
POSITION_NECESSITY_CODE = p_x_node_rec.position_necessity_code,
POSITION_KEY = p_x_node_rec.position_key,
ACTIVE_START_DATE = p_x_node_rec.active_start_date,
ACTIVE_END_DATE = p_x_node_rec.active_end_date,
LAST_UPDATE_DATE = G_SYSDATE,
LAST_UPDATED_BY = G_USER_ID,
LAST_UPDATE_LOGIN = G_LOGIN_ID,
OBJECT_VERSION_NUMBER = p_x_node_rec.object_version_number,
SECURITY_GROUP_ID = p_x_node_rec.security_group_id,
ATTRIBUTE_CATEGORY = p_x_node_rec.attribute_category,
ATTRIBUTE1 = p_x_node_rec.attribute1,
ATTRIBUTE2 = p_x_node_rec.attribute2,
ATTRIBUTE3 = p_x_node_rec.attribute3,
ATTRIBUTE4 = p_x_node_rec.attribute4,
ATTRIBUTE5 = p_x_node_rec.attribute5,
ATTRIBUTE6 = p_x_node_rec.attribute6,
ATTRIBUTE7 = p_x_node_rec.attribute7,
ATTRIBUTE8 = p_x_node_rec.attribute8,
ATTRIBUTE9 = p_x_node_rec.attribute9,
ATTRIBUTE10 = p_x_node_rec.attribute10,
ATTRIBUTE11 = p_x_node_rec.attribute11,
ATTRIBUTE12 = p_x_node_rec.attribute12,
ATTRIBUTE13 = p_x_node_rec.attribute13,
ATTRIBUTE14 = p_x_node_rec.attribute14,
ATTRIBUTE15 = p_x_node_rec.attribute15,
--R12
--priyan MEL-CDL
ATA_CODE = p_x_node_rec.ata_code
WHERE RELATIONSHIP_ID = p_x_node_rec.relationship_id;
'Node ['||p_x_node_rec.relationship_id||'] updated'
);
-- [implies the node is not being updated, instead either subconfig associations or position ratios
-- are being updated; User may only update the subconfig table in the UI and leave the Node details
ELSIF (p_x_counter_rules_tbl(i).operation_flag = G_DML_DELETE)
THEN
Delete_Counter_Rule
(
p_x_counter_rules_tbl(i).ctr_update_rule_id,
p_x_counter_rules_tbl(i).object_version_number
);
ELSIF (p_x_subconfig_tbl(i).operation_flag = G_DML_DELETE)
THEN
Detach_Subconfig
(
p_x_subconfig_tbl(i).mc_config_relation_id,
p_x_subconfig_tbl(i).object_version_number
);
IF (p_x_node_rec.operation_flag = G_DML_UPDATE)
THEN
OPEN check_root_node;
END IF; -- condition for DML Update
PROCEDURE Delete_Node
(
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_node_id IN NUMBER,
p_object_ver_num IN NUMBER
)
IS
-- 1. Define cursor get_mc_tree_csr to read all nodes that are children to a particular MC node
CURSOR get_mc_tree_csr
(
p_rel_id in number
)
IS
SELECT *
FROM ahl_mc_relationships
CONNECT BY parent_relationship_id = PRIOR relationship_id
START WITH relationship_id = p_rel_id
ORDER BY relationship_id DESC;
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Node';
SAVEPOINT Delete_Node_SP;
-- 9d. Delete all subconfiguration associations with the current node
DELETE FROM ahl_mc_config_relations
WHERE relationship_id = l_node_csr_rec.relationship_id;
-- 9g. Delete all counter rule associations with the current node
DELETE FROM ahl_ctr_update_rules
WHERE relationship_id = l_node_csr_rec.relationship_id;
-- 9h. Delete all document associations to this particular node
AHL_DI_ASSO_DOC_GEN_PVT.DELETE_ALL_ASSOCIATIONS
(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validate_only => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_aso_object_type_code => 'MC',
p_aso_object_id => l_node_csr_rec.relationship_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
'Deleted document associations for node ['||l_node_csr_rec.relationship_id||']'
);
-- 9i. Delete the MC node
DELETE FROM ahl_mc_relationships
WHERE relationship_id = l_node_csr_rec.relationship_id;
Rollback to Delete_Node_SP;
Rollback to Delete_Node_SP;
Rollback to Delete_Node_SP;
p_procedure_name => 'Delete_Node',
p_error_text => SUBSTR(SQLERRM,1,240)
);
END Delete_Node;
SELECT new.relationship_id, new.object_version_number
INTO p_x_node_id, p_x_node_obj_ver_num
FROM ahl_mc_relationships new, ahl_mc_relationships old
WHERE new.position_ref_code = old.position_ref_code AND
old.relationship_id = p_x_node_id AND
new.parent_relationship_id = p_parent_rel_id;
SELECT *
FROM ahl_mc_relationships
WHERE relationship_id <> p_topnode_id
-- Expired nodes also to be copied or else position path copy will fail
-- AND G_TRUNC_DATE < trunc(nvl(active_end_date, G_SYSDATE + 1))
CONNECT BY parent_relationship_id = PRIOR relationship_id
START WITH relationship_id = p_topnode_id
ORDER BY parent_relationship_id, display_order;
select POSITION_REF_MEANING,POSITION_NECESSITY_MEANING,GROUP_NAME,ATA_MEANING -- R12 priyan MEL-CDL
from ahl_mc_relationships_v
where relationship_id = p_relationship_id;
-- 2. Define cursor get_ctr_rule_update_csr to read all counter update rules for a particular MC node
CURSOR get_ctr_rule_update_csr
(
p_rel_id in number
)
IS
SELECT *
FROM ahl_ctr_update_rules
WHERE relationship_id = p_rel_id;
SELECT mc_header_id
FROM ahl_mc_relationships
WHERE relationship_id = p_dest_rel_id;
SELECT max(display_order)
FROM ahl_mc_relationships
WHERE parent_relationship_id = p_rel_id;
SELECT *
FROM ahl_mc_relationships_v
WHERE relationship_id = p_topnode_id;
l_ctr_rule_csr_rec get_ctr_rule_update_csr%rowtype;
OPEN get_ctr_rule_update_csr (l_node_csr_rec.relationship_id);
FETCH get_ctr_rule_update_csr INTO l_ctr_rule_csr_rec;
EXIT WHEN get_ctr_rule_update_csr%NOTFOUND;
l_ctr_rule_rec.ctr_update_rule_id := l_ctr_rule_csr_rec.ctr_update_rule_id;
CLOSE get_ctr_rule_update_csr;
'Creating new counter rule ['||l_node_ctr_rules_tbl(l_ctr_iterator).ctr_update_rule_id||']'
);
FND_MESSAGE.Set_Name('AHL', 'AHL_DI_ASSO_UPDATE_ERROR');
IF (p_x_documents_tbl(i).REVISION_NO IS NULL AND p_x_documents_tbl(i).dml_operation <> G_DML_DELETE)
THEN
FND_MESSAGE.Set_Name('AHL', 'AHL_MC_DOC_NO_REV');
SELECT type_code, name
FROM ahl_item_groups_b
WHERE item_group_id = p_ig_id;
SELECT item_group_id, type_code, name
FROM ahl_item_groups_b
WHERE name = p_ig_name AND
source_item_group_id IS NULL;
UPDATE ahl_mc_relationships
SET item_group_id = l_item_group_id
WHERE relationship_id = p_nodes_tbl(i).relationship_id;
'Updated MC node ['||p_nodes_tbl(i).relationship_id||'] with new item group id ['||l_item_group_id||']'
);
SELECT object_version_number
FROM ahl_mc_relationships
WHERE relationship_id = p_rel_id;
SELECT quantity,
active_end_date
FROM ahl_mc_relationships
WHERE relationship_id = p_x_node_rec.parent_relationship_id;
SELECT 'x'
FROM ahl_mc_config_relations
WHERE relationship_id = p_x_node_rec.parent_relationship_id;
SELECT 'x'
FROM ahl_mc_relationships
WHERE position_ref_code = p_x_node_rec.position_ref_code AND
parent_relationship_id = p_x_node_rec.parent_relationship_id AND
G_TRUNC_DATE < trunc(nvl(active_end_date, G_SYSDATE + 1)) AND
relationship_id <> nvl(p_x_node_rec.relationship_id, -1);
SELECT 'x'
FROM ahl_mc_relationships
WHERE parent_relationship_id is null AND
mc_header_id = p_x_node_rec.mc_header_id;
SELECT item_group_id, type_code, status_code
FROM ahl_item_groups_b
WHERE name = p_x_node_rec.item_group_name AND
source_item_group_id IS NULL;
SELECT 'x'
FROM ahl_item_associations_b
WHERE item_group_id = p_x_node_rec.item_group_id AND
quantity <> 1;
SELECT 'x'
FROM ahl_mc_relationships
WHERE parent_relationship_id = p_x_node_rec.relationship_id
AND G_TRUNC_DATE < trunc(nvl(active_end_date, G_SYSDATE + 1));
SELECT 'x'
FROM ahl_mc_relationships
WHERE display_order = p_x_node_rec.display_order AND
parent_relationship_id = p_x_node_rec.parent_relationship_id AND
G_TRUNC_DATE < trunc(nvl(active_end_date, G_SYSDATE + 1)) AND
relationship_id <> nvl(p_x_node_rec.relationship_id, -1);
SELECT active_start_date, active_end_date
FROM ahl_mc_relationships
WHERE relationship_id = p_x_node_rec.relationship_id;
IF (p_x_node_rec.operation_flag = G_DML_UPDATE)
THEN
OPEN get_node_dates;
SELECT object_version_number
FROM ahl_ctr_update_rules
WHERE ctr_update_rule_id = p_ctr_rule_id;
SELECT position_ref_meaning
FROM ahl_mc_relationships_v
WHERE relationship_id = p_counter_rule_rec.relationship_id;
SELECT 'x'
FROM ahl_ctr_update_rules
WHERE relationship_id = p_counter_rule_rec.relationship_id AND
rule_code = p_counter_rule_rec.rule_code AND
uom_code = p_counter_rule_rec.uom_code AND
ctr_update_rule_id <> nvl(p_counter_rule_rec.ctr_update_rule_id, -1);
SELECT object_version_number
FROM ahl_mc_config_relations
WHERE mc_config_relation_id = p_submc_assos_id;
SELECT priority
FROM ahl_mc_config_relations
WHERE relationship_id = p_subconfig_tbl(1).relationship_id
group by priority
having count(mc_config_relation_id) > 1;
SELECT 'x'
FROM
(
-- Establish parent-child relationship between subconfiguration associations
-- and the MC to which they are associated
SELECT submc.mc_header_id child, node.mc_header_id parent
FROM ahl_mc_config_relations submc, ahl_mc_relationships node
WHERE submc.relationship_id = node.relationship_id
CONNECT BY node.mc_header_id = PRIOR submc.mc_header_id
START WITH node.mc_header_id = p_subconfig_id
) submc_tree, ahl_mc_relationships mc_node
WHERE submc_tree.child = mc_node.mc_header_id AND
mc_node.relationship_id = p_rel_id;
SELECT mch.name
FROM ahl_mc_headers_b mch, ahl_mc_relationships mcr
WHERE mch.mc_header_id = mcr.mc_header_id AND
mcr.relationship_id = p_rel_id;
SELECT name
FROM ahl_mc_headers_b
WHERE mc_header_id = p_subconfig_id;
SELECT config_status_code
FROM ahl_mc_headers_v
WHERE mc_header_id = p_mc_header_id;
SELECT mch.config_status_code
FROM ahl_mc_headers_v mch, ahl_mc_relationships mcr
WHERE mch.mc_header_id = mcr.mc_header_id AND
mcr.relationship_id = p_rel_id;
/* SELECT 'X' FROM ahl_mc_config_relations
WHERE mc_header_id = p_dest_config_id
AND relationship_id IN
( SELECT relationship_id from ahl_mc_relationships
WHERE mc_header_id = p_subconfig_id
START WITH parent_relationship_id IS NULL
CONNECT BY parent_relationship_id = prior relationship_id);
SELECT 'X'
FROM ahl_mc_config_relations cnr
WHERE cnr.mc_header_id = p_dest_config_id
AND EXISTS
( SELECT 'X'
FROM ahl_mc_relationships mcr
WHERE mcr.mc_header_id = p_subconfig_id
AND mcr.relationship_id = cnr.relationship_id );
/* SELECT mc_header_id bulk collect
INTO l_subconfigs_table
FROM ahl_mc_config_relations WHERE relationship_id IN
( SELECT relationship_id FROM ahl_mc_relationships
WHERE mc_header_id = p_subconfig_id
START WITH parent_relationship_id IS NULL
CONNECT BY parent_relationship_id = prior relationship_id );
SELECT mc_header_id BULK COLLECT
INTO l_subconfigs_table
FROM ahl_mc_config_relations cnr
WHERE EXISTS
( SELECT 'X'
FROM ahl_mc_relationships mcr
WHERE mcr.mc_header_id = p_subconfig_id
AND mcr.relationship_id = cnr.relationship_id );
SELECT mch.mc_header_id, mch.config_status_code
FROM ahl_mc_headers_b mch, ahl_mc_relationships mcr
WHERE mch.mc_header_id = mcr.mc_header_id AND
mcr.relationship_id = p_rel_id;
UPDATE ahl_mc_headers_b
SET config_status_code = 'DRAFT'
WHERE mc_header_id = l_mc_header_id;
SELECT ahl_ctr_update_rules_s.nextval INTO p_x_counter_rule_rec.ctr_update_rule_id FROM DUAL;
INSERT INTO AHL_CTR_UPDATE_RULES
(
CTR_UPDATE_RULE_ID,
RELATIONSHIP_ID,
UOM_CODE,
RULE_CODE,
RATIO,
OBJECT_VERSION_NUMBER,
SECURITY_GROUP_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)
VALUES
(
p_x_counter_rule_rec.ctr_update_rule_id,
p_x_counter_rule_rec.relationship_id,
p_x_counter_rule_rec.uom_code,
p_x_counter_rule_rec.rule_code,
p_x_counter_rule_rec.ratio,
p_x_counter_rule_rec.object_version_number,
p_x_counter_rule_rec.security_group_id,
p_x_counter_rule_rec.ATTRIBUTE_CATEGORY,
p_x_counter_rule_rec.ATTRIBUTE1,
p_x_counter_rule_rec.ATTRIBUTE2,
p_x_counter_rule_rec.ATTRIBUTE3,
p_x_counter_rule_rec.ATTRIBUTE4,
p_x_counter_rule_rec.ATTRIBUTE5,
p_x_counter_rule_rec.ATTRIBUTE6,
p_x_counter_rule_rec.ATTRIBUTE7,
p_x_counter_rule_rec.ATTRIBUTE8,
p_x_counter_rule_rec.ATTRIBUTE9,
p_x_counter_rule_rec.ATTRIBUTE10,
p_x_counter_rule_rec.ATTRIBUTE11,
p_x_counter_rule_rec.ATTRIBUTE12,
p_x_counter_rule_rec.ATTRIBUTE13,
p_x_counter_rule_rec.ATTRIBUTE14,
p_x_counter_rule_rec.ATTRIBUTE15,
G_SYSDATE,
G_USER_ID,
G_SYSDATE,
G_USER_ID,
G_LOGIN_ID
);
SELECT position_ref_meaning
FROM ahl_mc_relationships_v
WHERE relationship_id = p_x_counter_rule_rec.relationship_id;
SELECT 'x'
FROM ahl_ctr_update_rules
WHERE relationship_id = p_x_counter_rule_rec.relationship_id AND
rule_code = p_x_counter_rule_rec.rule_code AND
uom_code = p_x_counter_rule_rec.uom_code AND
ctr_update_rule_id <> p_x_counter_rule_rec.ctr_update_rule_id;
-- Validate p_x_counter_rule_rec.ctr_update_rule_id exists
Validate_Counter_Exists(p_x_counter_rule_rec.ctr_update_rule_id, nvl(p_x_counter_rule_rec.object_version_number, 0));
UPDATE AHL_CTR_UPDATE_RULES
SET RATIO = p_x_counter_rule_rec.RATIO,
RULE_CODE = p_x_counter_rule_rec.RULE_CODE,
OBJECT_VERSION_NUMBER = p_x_counter_rule_rec.OBJECT_VERSION_NUMBER,
SECURITY_GROUP_ID = p_x_counter_rule_rec.SECURITY_GROUP_ID,
ATTRIBUTE_CATEGORY = p_x_counter_rule_rec.ATTRIBUTE_CATEGORY,
ATTRIBUTE1 = p_x_counter_rule_rec.ATTRIBUTE1,
ATTRIBUTE2 = p_x_counter_rule_rec.ATTRIBUTE2,
ATTRIBUTE3 = p_x_counter_rule_rec.ATTRIBUTE3,
ATTRIBUTE4 = p_x_counter_rule_rec.ATTRIBUTE4,
ATTRIBUTE5 = p_x_counter_rule_rec.ATTRIBUTE5,
ATTRIBUTE6 = p_x_counter_rule_rec.ATTRIBUTE6,
ATTRIBUTE7 = p_x_counter_rule_rec.ATTRIBUTE7,
ATTRIBUTE8 = p_x_counter_rule_rec.ATTRIBUTE8,
ATTRIBUTE9 = p_x_counter_rule_rec.ATTRIBUTE9,
ATTRIBUTE10 = p_x_counter_rule_rec.ATTRIBUTE10,
ATTRIBUTE11 = p_x_counter_rule_rec.ATTRIBUTE11,
ATTRIBUTE12 = p_x_counter_rule_rec.ATTRIBUTE12,
ATTRIBUTE13 = p_x_counter_rule_rec.ATTRIBUTE13,
ATTRIBUTE14 = p_x_counter_rule_rec.ATTRIBUTE14,
ATTRIBUTE15 = p_x_counter_rule_rec.ATTRIBUTE15,
LAST_UPDATE_DATE = G_SYSDATE,
LAST_UPDATED_BY = G_USER_ID,
LAST_UPDATE_LOGIN = G_LOGIN_ID
WHERE CTR_UPDATE_RULE_ID = p_x_counter_rule_rec.CTR_UPDATE_RULE_ID;
PROCEDURE Delete_Counter_Rule
(
p_ctr_update_rule_id IN NUMBER,
p_object_ver_num IN NUMBER
)
IS
-- Declare local variables
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Counter_Rule';
-- Validate p_ctr_update_rule_id exists
Validate_Counter_Exists(p_ctr_update_rule_id, nvl(p_object_ver_num, 0));
DELETE FROM ahl_ctr_update_rules
WHERE ctr_update_rule_id = p_ctr_update_rule_id;
END Delete_Counter_Rule;
SELECT name
FROM ahl_mc_config_relations submc, ahl_mc_headers_b mch
WHERE submc.mc_header_id = mch.mc_header_id AND
submc.relationship_id = p_x_subconfig_rec.relationship_id AND
submc.mc_header_id = p_x_subconfig_rec.mc_header_id;
SELECT 'x'
FROM ahl_mc_relationships
WHERE parent_relationship_id is null AND
relationship_id = p_x_subconfig_rec.relationship_id;
SELECT 'x'
FROM ahl_mc_relationships
WHERE parent_relationship_id = p_x_subconfig_rec.relationship_id AND
G_TRUNC_DATE < trunc(nvl(active_end_date, G_SYSDATE + 1));
SELECT mc_header_id
FROM ahl_mc_relationships
WHERE relationship_id = p_dest_rel_id;
SELECT mch.name
FROM ahl_mc_headers_b mch, ahl_mc_relationships mcr
WHERE mch.mc_header_id = mcr.mc_header_id AND
mcr.relationship_id = p_dest_rel_id;
SELECT name
FROM ahl_mc_headers_b
WHERE mc_header_id = p_subconfig_id;
SELECT ahl_mc_config_rel_s.nextval INTO p_x_subconfig_rec.mc_config_relation_id FROM DUAL;
INSERT INTO AHL_MC_CONFIG_RELATIONS
(
MC_CONFIG_RELATION_ID,
RELATIONSHIP_ID,
MC_HEADER_ID,
ACTIVE_START_DATE,
ACTIVE_END_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER,
SECURITY_GROUP_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
PRIORITY
)
VALUES
(
p_x_subconfig_rec.MC_CONFIG_RELATION_ID,
p_x_subconfig_rec.RELATIONSHIP_ID,
p_x_subconfig_rec.MC_HEADER_ID,
TRUNC(p_x_subconfig_rec.ACTIVE_START_DATE),
TRUNC(p_x_subconfig_rec.ACTIVE_END_DATE),
G_SYSDATE,
G_USER_ID,
G_SYSDATE,
G_USER_ID,
G_LOGIN_ID,
p_x_subconfig_rec.OBJECT_VERSION_NUMBER,
p_x_subconfig_rec.SECURITY_GROUP_ID,
p_x_subconfig_rec.ATTRIBUTE_CATEGORY,
p_x_subconfig_rec.ATTRIBUTE1,
p_x_subconfig_rec.ATTRIBUTE2,
p_x_subconfig_rec.ATTRIBUTE3,
p_x_subconfig_rec.ATTRIBUTE4,
p_x_subconfig_rec.ATTRIBUTE5,
p_x_subconfig_rec.ATTRIBUTE6,
p_x_subconfig_rec.ATTRIBUTE7,
p_x_subconfig_rec.ATTRIBUTE8,
p_x_subconfig_rec.ATTRIBUTE9,
p_x_subconfig_rec.ATTRIBUTE10,
p_x_subconfig_rec.ATTRIBUTE11,
p_x_subconfig_rec.ATTRIBUTE12,
p_x_subconfig_rec.ATTRIBUTE13,
p_x_subconfig_rec.ATTRIBUTE14,
p_x_subconfig_rec.ATTRIBUTE15,
p_x_subconfig_rec.priority
);
SELECT active_start_date, active_end_date
FROM ahl_mc_config_relations
WHERE mc_config_relation_id = p_mc_config_rel_id;
UPDATE AHL_MC_CONFIG_RELATIONS
SET PRIORITY = p_x_subconfig_rec.priority,
ACTIVE_START_DATE = p_x_subconfig_rec.ACTIVE_START_DATE,
ACTIVE_END_DATE = p_x_subconfig_rec.ACTIVE_END_DATE,
LAST_UPDATE_DATE = G_SYSDATE,
LAST_UPDATED_BY = G_USER_ID,
LAST_UPDATE_LOGIN = G_LOGIN_ID,
OBJECT_VERSION_NUMBER = p_x_subconfig_rec.OBJECT_VERSION_NUMBER,
SECURITY_GROUP_ID = p_x_subconfig_rec.SECURITY_GROUP_ID,
ATTRIBUTE_CATEGORY = p_x_subconfig_rec.ATTRIBUTE_CATEGORY,
ATTRIBUTE1 = p_x_subconfig_rec.ATTRIBUTE1,
ATTRIBUTE2 = p_x_subconfig_rec.ATTRIBUTE2,
ATTRIBUTE3 = p_x_subconfig_rec.ATTRIBUTE3,
ATTRIBUTE4 = p_x_subconfig_rec.ATTRIBUTE4,
ATTRIBUTE5 = p_x_subconfig_rec.ATTRIBUTE5,
ATTRIBUTE6 = p_x_subconfig_rec.ATTRIBUTE6,
ATTRIBUTE7 = p_x_subconfig_rec.ATTRIBUTE7,
ATTRIBUTE8 = p_x_subconfig_rec.ATTRIBUTE8,
ATTRIBUTE9 = p_x_subconfig_rec.ATTRIBUTE9,
ATTRIBUTE10 = p_x_subconfig_rec.ATTRIBUTE10,
ATTRIBUTE11 = p_x_subconfig_rec.ATTRIBUTE11,
ATTRIBUTE12 = p_x_subconfig_rec.ATTRIBUTE12,
ATTRIBUTE13 = p_x_subconfig_rec.ATTRIBUTE13,
ATTRIBUTE14 = p_x_subconfig_rec.ATTRIBUTE14,
ATTRIBUTE15 = p_x_subconfig_rec.ATTRIBUTE15
WHERE MC_CONFIG_RELATION_ID = p_x_subconfig_rec.MC_CONFIG_RELATION_ID;
DELETE FROM ahl_mc_config_relations
WHERE mc_config_relation_id = p_mc_config_relation_id;
SELECT mc_header_id
FROM ahl_mc_relationships
WHERE relationship_id = p_dest_rel_id;
SELECT mch.name
FROM ahl_mc_headers_b mch, ahl_mc_relationships mcr
WHERE mch.mc_header_id = mcr.mc_header_id AND
mcr.relationship_id = p_dest_rel_id;
SELECT name
FROM ahl_mc_headers_b
WHERE mc_header_id = p_subconfig_id;
SELECT *
FROM ahl_mc_config_relations
WHERE relationship_id = p_source_rel_id;
SELECT 'x'
FROM ahl_mc_relationships
WHERE parent_relationship_id = p_rel_id AND
G_TRUNC_DATE < trunc(nvl(active_end_date, G_SYSDATE + 1));
SELECT ahl_mc_config_rel_s.nextval INTO l_mc_config_rel_id FROM DUAL;
INSERT INTO AHL_MC_CONFIG_RELATIONS
(
MC_CONFIG_RELATION_ID,
RELATIONSHIP_ID,
MC_HEADER_ID,
ACTIVE_START_DATE,
ACTIVE_END_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER,
SECURITY_GROUP_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
PRIORITY
)
VALUES
(
l_mc_config_rel_id,
p_dest_rel_id,
l_subconfig_csr_rec.MC_HEADER_ID,
TRUNC(l_subconfig_csr_rec.ACTIVE_START_DATE),
TRUNC(l_subconfig_csr_rec.ACTIVE_END_DATE),
G_SYSDATE,
G_USER_ID,
G_SYSDATE,
G_USER_ID,
G_LOGIN_ID,
1,
l_subconfig_csr_rec.SECURITY_GROUP_ID,
l_subconfig_csr_rec.ATTRIBUTE_CATEGORY,
l_subconfig_csr_rec.ATTRIBUTE1,
l_subconfig_csr_rec.ATTRIBUTE2,
l_subconfig_csr_rec.ATTRIBUTE3,
l_subconfig_csr_rec.ATTRIBUTE4,
l_subconfig_csr_rec.ATTRIBUTE5,
l_subconfig_csr_rec.ATTRIBUTE6,
l_subconfig_csr_rec.ATTRIBUTE7,
l_subconfig_csr_rec.ATTRIBUTE8,
l_subconfig_csr_rec.ATTRIBUTE9,
l_subconfig_csr_rec.ATTRIBUTE10,
l_subconfig_csr_rec.ATTRIBUTE11,
l_subconfig_csr_rec.ATTRIBUTE12,
l_subconfig_csr_rec.ATTRIBUTE13,
l_subconfig_csr_rec.ATTRIBUTE14,
l_subconfig_csr_rec.ATTRIBUTE15,
l_subconfig_csr_rec.PRIORITY
);