[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT AHL_PC_NODES_B_S.NEXTVAL INTO l_pc_node_id FROM DUAL;
AHL_PC_NODES_PKG.INSERT_ROW
(
X_ROWID => l_row_id,
X_PC_NODE_ID => l_pc_node_id,
X_PC_HEADER_ID => p_x_node_rec.pc_header_id,
X_NAME => p_x_node_rec.name,
X_DESCRIPTION => p_x_node_rec.description,
X_PARENT_NODE_ID => nvl(p_x_node_rec.parent_node_id, 0),
X_CHILD_COUNT => 0,
X_OPERATION_STATUS_FLAG => p_x_node_rec.operation_status_flag,
X_DRAFT_FLAG => p_x_node_rec.draft_flag,
X_LINK_TO_NODE_ID => nvl(l_link_id, 0),
X_OBJECT_VERSION_NUMBER => 1,
X_SECURITY_GROUP_ID => null,
X_ATTRIBUTE_CATEGORY => p_x_node_rec.attribute_category,
X_ATTRIBUTE1 => p_x_node_rec.attribute1,
X_ATTRIBUTE2 => p_x_node_rec.attribute2,
X_ATTRIBUTE3 => p_x_node_rec.attribute3,
X_ATTRIBUTE4 => p_x_node_rec.attribute4,
X_ATTRIBUTE5 => p_x_node_rec.attribute5,
X_ATTRIBUTE6 => p_x_node_rec.attribute6,
X_ATTRIBUTE7 => p_x_node_rec.attribute7,
X_ATTRIBUTE8 => p_x_node_rec.attribute8,
X_ATTRIBUTE9 => p_x_node_rec.attribute9,
X_ATTRIBUTE10 => p_x_node_rec.attribute10,
X_ATTRIBUTE11 => p_x_node_rec.attribute11,
X_ATTRIBUTE12 => p_x_node_rec.attribute12,
X_ATTRIBUTE13 => p_x_node_rec.attribute13,
X_ATTRIBUTE14 => p_x_node_rec.attribute14,
X_ATTRIBUTE15 => p_x_node_rec.attribute15,
X_CREATION_DATE => l_sysdate,
X_CREATED_BY => g_user_id,
X_LAST_UPDATE_DATE => l_sysdate,
X_LAST_UPDATED_BY => g_user_id,
X_LAST_UPDATE_LOGIN => g_user_id
);
UPDATE ahl_pc_nodes_b
SET child_count = NVL(child_count,0) + 1
WHERE pc_node_id = p_x_node_rec.parent_node_id;
PROCEDURE UPDATE_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,
p_x_node_rec IN OUT NOCOPY AHL_PC_NODE_PUB.PC_NODE_REC,
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) := 'UPDATE_NODE';
SELECT node.OBJECT_VERSION_NUMBER, head.STATUS
FROM AHL_PC_NODES_B node, AHL_PC_HEADERS_B head
WHERE head.PC_HEADER_ID = node.PC_HEADER_ID and
node.PC_NODE_ID = p_pc_node_id and
node.DRAFT_FLAG = 'N';
SAVEPOINT UPDATE_NODE_PVT;
IF (p_x_node_rec.operation_flag = G_DML_UPDATE)
THEN
VALIDATE_NODE (p_x_node_rec);
SELECT LINK_TO_NODE_ID
INTO l_link_id
FROM AHL_PC_NODES_B
WHERE PC_NODE_ID = p_x_node_rec.pc_node_id;
AHL_PC_NODES_PKG.UPDATE_ROW
(
X_PC_NODE_ID => p_x_node_rec.pc_node_id,
X_PC_HEADER_ID => p_x_node_rec.pc_header_id,
X_NAME => p_x_node_rec.name,
X_DESCRIPTION => p_x_node_rec.description,
X_PARENT_NODE_ID => p_x_node_rec.parent_node_id,
X_CHILD_COUNT => p_x_node_rec.child_count,
X_OPERATION_STATUS_FLAG => p_x_node_rec.operation_status_flag,
X_DRAFT_FLAG => p_x_node_rec.draft_flag,
X_LINK_TO_NODE_ID => l_link_id,
X_SECURITY_GROUP_ID => null,
X_OBJECT_VERSION_NUMBER => p_x_node_rec.object_version_number + 1,
X_ATTRIBUTE_CATEGORY => p_x_node_rec.attribute_category,
X_ATTRIBUTE1 => p_x_node_rec.attribute1,
X_ATTRIBUTE2 => p_x_node_rec.attribute2,
X_ATTRIBUTE3 => p_x_node_rec.attribute3,
X_ATTRIBUTE4 => p_x_node_rec.attribute4,
X_ATTRIBUTE5 => p_x_node_rec.attribute5,
X_ATTRIBUTE6 => p_x_node_rec.attribute6,
X_ATTRIBUTE7 => p_x_node_rec.attribute7,
X_ATTRIBUTE8 => p_x_node_rec.attribute8,
X_ATTRIBUTE9 => p_x_node_rec.attribute9,
X_ATTRIBUTE10 => p_x_node_rec.attribute10,
X_ATTRIBUTE11 => p_x_node_rec.attribute11,
X_ATTRIBUTE12 => p_x_node_rec.attribute12,
X_ATTRIBUTE13 => p_x_node_rec.attribute13,
X_ATTRIBUTE14 => p_x_node_rec.attribute14,
X_ATTRIBUTE15 => p_x_node_rec.attribute15,
X_LAST_UPDATE_DATE => l_sysdate,
X_LAST_UPDATED_BY => g_user_id,
X_LAST_UPDATE_LOGIN => g_user_id
);
Rollback to UPDATE_NODE_PVT;
Rollback to UPDATE_NODE_PVT;
Rollback to UPDATE_NODE_PVT;
p_procedure_name => 'UPDATE_NODE',
p_error_text => SUBSTR(SQLERRM,1,240) );
END UPDATE_NODE;
PROCEDURE DELETE_NODES (
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,
p_x_node_rec IN OUT NOCOPY AHL_PC_NODE_PUB.PC_NODE_REC,
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_NODES';
SELECT HEAD.PRIMARY_FLAG, HEAD.ASSOCIATION_TYPE_FLAG
FROM AHL_PC_HEADERS_B HEAD, AHL_PC_NODES_B NODE
WHERE NODE.PC_HEADER_ID = HEAD.PC_HEADER_ID AND
NODE.PC_NODE_ID = p_pc_node_id;
SAVEPOINT DELETE_NODES_PVT;
SELECT pc_node_id INTO l_node_id
FROM ahl_pc_nodes_b
WHERE pc_header_id = p_x_node_rec.pc_header_id and
parent_node_id = 0;
SELECT pc_node_id
BULK COLLECT
INTO l_node_tbl
FROM ahl_pc_nodes_b
WHERE pc_header_id = p_x_node_rec.pc_header_id
CONNECT BY parent_node_id = PRIOR pc_node_id
START WITH pc_node_id = l_node_id
ORDER BY pc_node_id DESC;
SELECT pc_association_id
BULK COLLECT INTO l_assos_tbl
FROM ahl_pc_associations ahass
WHERE pc_node_id IN
(
SELECT pc_node_id
FROM ahl_pc_nodes_b
WHERE pc_header_id = p_x_node_rec.pc_header_id
CONNECT BY parent_node_id = PRIOR pc_node_id
START WITH pc_node_id = l_node_id
);
SELECT distinct 'X'
INTO l_exist
FROM ahl_mr_headers_app_v mrh, ahl_mr_effectivities mre
WHERE
-- R12 [priyan MEL/CDL]
-- to prevent foreign key violations checking for any MR effectivity associated (instead of just active ones)
-- trunc(sysdate) < trunc(nvl(mrh.effective_to, sysdate+1)) and
mrh.mr_header_id = mre.mr_header_id and
mre.pc_node_id IN
(
SELECT pc_node_id
FROM ahl_pc_nodes_b
CONNECT BY parent_node_id = PRIOR pc_node_id
START WITH pc_node_id = l_linked_node_id
);
SELECT distinct 'X'
INTO l_exist
FROM ahl_mel_cdl_headers
WHERE pc_node_id IN
(
SELECT pc_node_id
FROM ahl_pc_nodes_b
CONNECT BY parent_node_id = PRIOR pc_node_id
START WITH pc_node_id = l_linked_node_id
);
SELECT distinct 'X'
INTO l_exist
FROM ahl_utilization_forecast_v
WHERE pc_node_id IN
(
SELECT pc_node_id
FROM ahl_pc_nodes_b
CONNECT BY parent_node_id = PRIOR pc_node_id
START WITH pc_node_id = l_linked_node_id
);
SELECT distinct 'X'
INTO l_exist
FROM ahl_utilization_forecast_v uf, ahl_pc_associations assos, ahl_pc_nodes_b node
WHERE uf.unit_config_header_id = assos.unit_item_id and
assos.pc_node_id = node.pc_node_id and
node.pc_node_id IN (
SELECT pc_node_id
FROM ahl_pc_nodes_b
CONNECT BY parent_node_id = PRIOR pc_node_id
START WITH pc_node_id = l_linked_node_id
);
SELECT distinct 'X'
INTO l_exist
FROM ahl_utilization_forecast_v uf, ahl_pc_associations assos, ahl_pc_nodes_b node
WHERE uf.inventory_item_id = assos.unit_item_id and
uf.inventory_org_id = assos.inventory_org_id and
assos.pc_node_id = node.pc_node_id and
node.pc_node_id IN (
SELECT pc_node_id
FROM ahl_pc_nodes_b
CONNECT BY parent_node_id = PRIOR pc_node_id
START WITH pc_node_id = l_linked_node_id
);
DELETE
FROM ahl_pc_associations
WHERE pc_association_id = l_assos_tbl(i);
DELETE
FROM AHL_DOC_TITLE_ASSOS_TL
WHERE DOC_TITLE_ASSO_ID IN (
SELECT DOC_TITLE_ASSO_ID
FROM AHL_DOC_TITLE_ASSOS_B
WHERE aso_object_type_code = 'PC' and
aso_object_id = l_node_tbl(j)
);
DELETE
FROM AHL_DOC_TITLE_ASSOS_B
WHERE aso_object_type_code = 'PC' and
aso_object_id = l_node_tbl(j);
AHL_PC_NODES_PKG.DELETE_ROW(l_node_tbl(j));
UPDATE ahl_pc_nodes_b
SET child_count = NVL(child_count,1) - 1
WHERE pc_node_id = p_x_node_rec.parent_node_id;
-- When the PC header is being deleted and if open NRs exists, add an error
-- to the message stack which will be later caught by the calling procedure (AHLVPCHB.pls-> Delete_pc_header)
IF (p_x_node_rec.pc_node_id IS NOT NULL)
THEN
SELECT name
INTO p_x_node_rec.name
FROM ahl_pc_nodes_b
WHERE pc_node_id = p_x_node_rec.pc_node_id;
-- hence cannot delete the draft version.
FND_MESSAGE.Set_Name('AHL','AHL_PC_HEADER_DEL_OPEN_NR');
Rollback to DELETE_NODES_PVT;
Rollback to DELETE_NODES_PVT;
Rollback to DELETE_NODES_PVT;
p_procedure_name => 'DELETE_NODES',
p_error_text => SUBSTR(SQLERRM,1,240) );
END DELETE_NODES;
select link_to_node_id
from ahl_pc_nodes_b
where pc_node_id = p_pc_node_id;
select pc_header_id, status
from ahl_pc_headers_b
where pc_header_id = p_pc_header_id;
select 'X'
from ahl_pc_nodes_b
where pc_node_id = p_node_id and
pc_header_id = p_pc_header_id;
select 'X'
from ahl_pc_nodes_b
where pc_node_id = p_parent_node_id and
pc_header_id = p_pc_header_id;
select 'X'
from ahl_pc_nodes_b
where pc_header_id = p_pc_header_id and
NVL(parent_node_id,0) = 0;
select 'X'
from ahl_pc_nodes_b
where name = p_name and
-- upper(name) = upper(p_name) and
parent_node_id = p_node_parent_id and
pc_node_id <> NVL(p_pc_node_id, 0) and
NVL(p_node_parent_id,0) <> 0 and
draft_flag ='N';
select status
from ahl_pc_headers_b
where pc_header_id = p_pc_header_id;
select object_version_number
from ahl_pc_nodes_b
where pc_node_id = p_pc_node_id;
select 'X'
from ahl_pc_nodes_b node, ahl_pc_associations ahass
where node.pc_node_id = p_parent_node_id and
ahass.pc_node_id = p_parent_node_id;
IF (p_node_rec.operation_flag <> G_DML_DELETE)
THEN
IF (p_node_rec.parent_node_id IS NOT NULL AND p_node_rec.parent_node_id <> 0)
THEN
OPEN check_name_exists ( p_node_rec.parent_node_id, p_node_rec.pc_node_id, p_node_rec.name);
IF (p_node_rec.operation_flag = G_DML_UPDATE) OR ( (p_node_rec.operation_flag = G_DML_DELETE) AND (p_node_rec.pc_node_id IS NOT NULL) )
THEN
OPEN check_id_exists (p_node_rec.pc_header_id, p_node_rec.pc_node_id);
select status
from ahl_pc_headers_b
where pc_header_id = p_pc_header_id;
update ahl_pc_headers_b
set status = 'DRAFT'
where pc_header_id = p_pc_header_id;