[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE VALIDATE_PC_HEADER_UPDATE
(
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_pc_header_rec IN OUT NOCOPY AHL_PC_HEADER_PUB.PC_HEADER_REC,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
);
PROCEDURE DELETE_NODES_REMOVE_LINK (p_x_node_rec IN AHL_PC_NODE_PUB.PC_NODE_REC);
PROCEDURE DELETE_PC_AND_TREE (p_pc_header_id IN NUMBER);
SELECT AHL_PC_HEADERS_B_S.NEXTVAL INTO l_header_id FROM DUAL;
AHL_PC_HEADERS_PKG.INSERT_ROW
(
X_ROWID => l_rowid,
X_PC_HEADER_ID => l_header_id,
X_PRODUCT_TYPE_CODE => p_x_pc_header_rec.PRODUCT_TYPE_CODE,
X_STATUS => 'DRAFT',
X_PRIMARY_FLAG => p_x_pc_header_rec.PRIMARY_FLAG,
X_ASSOCIATION_TYPE_FLAG => p_x_pc_header_rec.ASSOCIATION_TYPE_FLAG,
X_OBJECT_VERSION_NUMBER => 1,
X_ATTRIBUTE_CATEGORY => p_x_pc_header_rec.ATTRIBUTE_CATEGORY,
X_SECURITY_GROUP_ID => null,
X_ATTRIBUTE1 => p_x_pc_header_rec.ATTRIBUTE1,
X_ATTRIBUTE2 => p_x_pc_header_rec.ATTRIBUTE2,
X_ATTRIBUTE3 => p_x_pc_header_rec.ATTRIBUTE3,
X_ATTRIBUTE4 => p_x_pc_header_rec.ATTRIBUTE4,
X_ATTRIBUTE5 => p_x_pc_header_rec.ATTRIBUTE5,
X_ATTRIBUTE6 => p_x_pc_header_rec.ATTRIBUTE6,
X_ATTRIBUTE7 => p_x_pc_header_rec.ATTRIBUTE7,
X_ATTRIBUTE8 => p_x_pc_header_rec.ATTRIBUTE8,
X_ATTRIBUTE9 => p_x_pc_header_rec.ATTRIBUTE9,
X_ATTRIBUTE10 => p_x_pc_header_rec.ATTRIBUTE10,
X_ATTRIBUTE11 => p_x_pc_header_rec.ATTRIBUTE11,
X_ATTRIBUTE12 => p_x_pc_header_rec.ATTRIBUTE12,
X_ATTRIBUTE13 => p_x_pc_header_rec.ATTRIBUTE13,
X_ATTRIBUTE14 => p_x_pc_header_rec.ATTRIBUTE14,
X_ATTRIBUTE15 => p_x_pc_header_rec.ATTRIBUTE15,
X_NAME => p_x_pc_header_rec.NAME,
X_DESCRIPTION => p_x_pc_header_rec.DESCRIPTION,
X_DRAFT_FLAG => 'N',
X_LINK_TO_PC_ID => nvl(l_link_id,0),
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
);
PROCEDURE UPDATE_PC_HEADER
(
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_pc_header_rec IN OUT NOCOPY AHL_PC_HEADER_PUB.PC_HEADER_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_PC_HEADER';
select primary_flag
from ahl_pc_headers_b
where pc_header_id = p_pc_header_id;
SAVEPOINT UPDATE_PC_HEADER_PVT;
AHL_DEBUG_PUB.debug('PCH -- PVT -- UPDATE_PC_HEADER -- Can update PC, No duplicate unit/part associations');
SELECT LINK_TO_PC_ID
INTO p_x_pc_header_rec.LINK_TO_PC_ID
FROM AHL_PC_HEADERS_VL
WHERE PC_HEADER_ID = p_x_pc_header_rec.PC_HEADER_ID;
VALIDATE_PC_HEADER_UPDATE
(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
p_x_pc_header_rec => p_x_pc_header_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
AHL_DEBUG_PUB.debug('PCH -- PVT -- UPDATE_PC_HEADER -- Operation Flag after VALIDATE_PC_HEADER_UPDATE = '||p_x_pc_header_rec.OPERATION_FLAG);
AHL_PC_HEADERS_PKG.UPDATE_ROW
(
X_PC_HEADER_ID => p_x_pc_header_rec.PC_HEADER_ID,
X_PRODUCT_TYPE_CODE => p_x_pc_header_rec.PRODUCT_TYPE_CODE,
X_STATUS => p_x_pc_header_rec.STATUS,
X_PRIMARY_FLAG => p_x_pc_header_rec.PRIMARY_FLAG,
X_ASSOCIATION_TYPE_FLAG => p_x_pc_header_rec.ASSOCIATION_TYPE_FLAG,
X_OBJECT_VERSION_NUMBER => p_x_pc_header_rec.OBJECT_VERSION_NUMBER + 1,
X_SECURITY_GROUP_ID => null,
X_ATTRIBUTE_CATEGORY => p_x_pc_header_rec.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => p_x_pc_header_rec.ATTRIBUTE1,
X_ATTRIBUTE2 => p_x_pc_header_rec.ATTRIBUTE2,
X_ATTRIBUTE3 => p_x_pc_header_rec.ATTRIBUTE3,
X_ATTRIBUTE4 => p_x_pc_header_rec.ATTRIBUTE4,
X_ATTRIBUTE5 => p_x_pc_header_rec.ATTRIBUTE5,
X_ATTRIBUTE6 => p_x_pc_header_rec.ATTRIBUTE6,
X_ATTRIBUTE7 => p_x_pc_header_rec.ATTRIBUTE7,
X_ATTRIBUTE8 => p_x_pc_header_rec.ATTRIBUTE8,
X_ATTRIBUTE9 => p_x_pc_header_rec.ATTRIBUTE9,
X_ATTRIBUTE10 => p_x_pc_header_rec.ATTRIBUTE10,
X_ATTRIBUTE11 => p_x_pc_header_rec.ATTRIBUTE11,
X_ATTRIBUTE12 => p_x_pc_header_rec.ATTRIBUTE12,
X_ATTRIBUTE13 => p_x_pc_header_rec.ATTRIBUTE13,
X_ATTRIBUTE14 => p_x_pc_header_rec.ATTRIBUTE14,
X_ATTRIBUTE15 => p_x_pc_header_rec.ATTRIBUTE15,
X_NAME => p_x_pc_header_rec.NAME,
X_DESCRIPTION => p_x_pc_header_rec.DESCRIPTION,
X_DRAFT_FLAG => 'N',
X_LINK_TO_PC_ID => p_x_pc_header_rec.LINK_TO_PC_ID,
X_LAST_UPDATE_DATE => l_sysdate,
X_LAST_UPDATED_BY => G_USER_ID,
X_LAST_UPDATE_LOGIN => G_USER_ID
);
AHL_DEBUG_PUB.debug('PCH -- PVT -- UPDATE_PC_HEADER -- After DB Update');
Rollback to UPDATE_PC_HEADER_PVT;
Rollback to UPDATE_PC_HEADER_PVT;
Rollback to UPDATE_PC_HEADER_PVT;
p_procedure_name => 'UPDATE_PC_HEADER',
p_error_text => SUBSTR(SQLERRM,1,240) );
END UPDATE_PC_HEADER;
PROCEDURE DELETE_PC_HEADER
(
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_pc_header_rec IN OUT NOCOPY AHL_PC_HEADER_PUB.PC_HEADER_REC,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
TYPE T_ID_TBL IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
SELECT STATUS
FROM AHL_PC_HEADERS_B
WHERE PC_HEADER_ID = p_pc_header_id;
CURSOR delete_node (p_pc_header_id varchar2)
IS
SELECT PC_NODE_ID
FROM AHL_PC_NODES_B
WHERE PC_HEADER_ID = p_pc_header_id AND
PARENT_NODE_ID = 0;
CURSOR delete_linked_header (p_pc_header_id varchar2)
IS
SELECT LINK_TO_PC_ID
FROM AHL_PC_HEADERS_B
WHERE PC_HEADER_ID = p_pc_header_id;
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_PC_HEADER';
SAVEPOINT DELETE_PC_HEADER_PVT;
FND_MESSAGE.SET_NAME('AHL','AHL_PC_DRAFT_DELETE');
OPEN delete_linked_header(p_x_pc_header_rec.PC_HEADER_ID);
FETCH delete_linked_header INTO l_link_to_header_id;
CLOSE delete_linked_header;
AHL_DEBUG_PUB.debug('PCH -- PVT -- DELETE_PC_HEADER -- Retrieving linked PC ID='||l_link_to_header_id);
OPEN delete_node(p_x_pc_header_rec.PC_HEADER_ID);
FETCH delete_node INTO l_node_id;
IF(delete_node%FOUND)
THEN
l_node_rec.PC_HEADER_ID := p_x_pc_header_rec.PC_HEADER_ID;
AHL_DEBUG_PUB.debug('PCH -- PVT -- DELETE_PC_HEADER -- Deleting Node Tree from ID='||l_node_rec.PC_NODE_ID);
AHL_PC_NODE_PVT.DELETE_NODES
(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
p_x_node_rec => l_node_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
CLOSE delete_node;
-- Added error handling after the Delete_Nodes API is called.
-- Check Error Message stack.
IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
THEN
x_msg_count := FND_MSG_PUB.count_msg;
AHL_DEBUG_PUB.debug('PCH -- PVT -- DELETE_PC_HEADER -- Deleting PC with ID='||p_x_pc_header_rec.PC_HEADER_ID);
AHL_PC_HEADERS_PKG.DELETE_ROW (p_x_pc_header_rec.PC_HEADER_ID);
AHL_DEBUG_PUB.debug('PCH -- PVT -- DELETE_PC_HEADER -- Changing DRAFT_FLAG=Y for linked-to PC with ID='||l_link_to_header_id);
AHL_DEBUG_PUB.debug('PCH -- PVT -- DELETE_PC_HEADER -- Done for Header ID='||l_link_to_header_id);
UPDATE AHL_PC_HEADERS_B
SET DRAFT_FLAG='N'
WHERE PC_HEADER_ID = l_link_to_header_id;
OPEN delete_node(l_link_to_header_id);
FETCH delete_node INTO l_node_id;
CLOSE delete_node;
SELECT pc_node_id
BULK COLLECT INTO l_node_tbl
FROM ahl_pc_nodes_b
WHERE pc_header_id = l_link_to_header_id
CONNECT BY parent_node_id = PRIOR pc_node_id
START WITH pc_node_id = l_node_id;
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 = l_link_to_header_id
CONNECT BY parent_node_id = PRIOR pc_node_id
START WITH pc_node_id = l_node_id
);
AHL_DEBUG_PUB.debug('PCH -- PVT -- DELETE_PC_HEADER -- Done for Node ID='||l_node_tbl(i));
UPDATE AHL_PC_NODES_B
SET DRAFT_FLAG = 'N'
WHERE PC_NODE_ID = l_node_tbl(i);
AHL_DEBUG_PUB.debug('PCH -- PVT -- DELETE_PC_HEADER -- Done for Association ID='||l_assos_tbl(j));
UPDATE AHL_PC_ASSOCIATIONS
SET DRAFT_FLAG = 'N'
WHERE PC_ASSOCIATION_ID = l_assos_tbl(j);
AHL_DEBUG_PUB.debug('PCH -- PVT -- DELETE_PC_HEADER -- After DB Delete');
Rollback to DELETE_PC_HEADER_PVT;
Rollback to DELETE_PC_HEADER_PVT;
Rollback to DELETE_PC_HEADER_PVT;
p_procedure_name => 'DELETE_PC_HEADER',
p_error_text => SUBSTR(SQLERRM,1,240) );
END DELETE_PC_HEADER;
SELECT *
FROM AHL_PC_TREE_V
WHERE PC_HEADER_ID = p_header_id AND
( NODE_TYPE = G_NODE OR
( p_copy_assos_flag = 'Y' AND NODE_TYPE IN (G_PART, G_UNIT) )
)
ORDER BY PARENT_NODE_ID;
Select * from
(SELECT AHNO.ROW_ID,
AHNO.PC_NODE_ID,
AHNO.OBJECT_VERSION_NUMBER,
AHNO.LAST_UPDATE_DATE,
AHNO.LAST_UPDATED_BY,
AHNO.CREATION_DATE,
AHNO.CREATED_BY,
AHNO.LAST_UPDATE_LOGIN,
AHNO.PC_HEADER_ID,
AHNO.NAME,
AHNO.PARENT_NODE_ID,
AHNO.CHILD_COUNT,
AHNO.LINK_TO_NODE_ID,
AHNO.DRAFT_FLAG,
AHNO.DESCRIPTION,
'N' NODE_TYPE,
0 UNIT_ITEM_ID,
0 INVENTORY_ORG_ID,
AHNO.OPERATION_STATUS_FLAG,
AHNO.SECURITY_GROUP_ID,
AHNO.ATTRIBUTE_CATEGORY,
AHNO.ATTRIBUTE1,
AHNO.ATTRIBUTE2,
AHNO.ATTRIBUTE3,
AHNO.ATTRIBUTE4,
AHNO.ATTRIBUTE5,
AHNO.ATTRIBUTE6,
AHNO.ATTRIBUTE7,
AHNO.ATTRIBUTE8,
AHNO.ATTRIBUTE9,
AHNO.ATTRIBUTE10,
AHNO.ATTRIBUTE11,
AHNO.ATTRIBUTE12,
AHNO.ATTRIBUTE13,
AHNO.ATTRIBUTE14,
AHNO.ATTRIBUTE15
FROM AHL_PC_NODES_VL AHNO
WHERE AHNO.PC_HEADER_ID = p_header_id
UNION
SELECT DISTINCT AHS.ROWID ROW_ID,
AHS.PC_ASSOCIATION_ID PC_NODE_ID,
AHS.OBJECT_VERSION_NUMBER,
AHS.LAST_UPDATE_DATE,
AHS.LAST_UPDATED_BY,
AHS.CREATION_DATE,
AHS.CREATED_BY,
AHS.LAST_UPDATE_LOGIN,
NODE.PC_HEADER_ID,
DECODE(AHS.ASSOCIATION_TYPE_FLAG,'U',UNIT.NAME,MTL.CONCATENATED_SEGMENTS) NAME,
AHS.PC_NODE_ID PARENT_NODE_ID,
0 CHILD_COUNT,
AHS.LINK_TO_ASSOCIATION_ID LINK_TO_NODE_ID,
AHS.DRAFT_FLAG,
MTL.DESCRIPTION,
AHS.ASSOCIATION_TYPE_FLAG NODE_TYPE,
AHS.UNIT_ITEM_ID,
AHS.INVENTORY_ORG_ID,
AHS.OPERATION_STATUS_FLAG,
AHS.SECURITY_GROUP_ID,
AHS.ATTRIBUTE_CATEGORY,
AHS.ATTRIBUTE1,
AHS.ATTRIBUTE2,
AHS.ATTRIBUTE3,
AHS.ATTRIBUTE4,
AHS.ATTRIBUTE5,
AHS.ATTRIBUTE6,
AHS.ATTRIBUTE7,
AHS.ATTRIBUTE8,
AHS.ATTRIBUTE9,
AHS.ATTRIBUTE10,
AHS.ATTRIBUTE11,
AHS.ATTRIBUTE12,
AHS.ATTRIBUTE13,
AHS.ATTRIBUTE14,
AHS.ATTRIBUTE15
FROM AHL_PC_ASSOCIATIONS AHS, AHL_UNIT_CONFIG_HEADERS UNIT,
CSI_ITEM_INSTANCES CSI, MTL_SYSTEM_ITEMS_KFV MTL,
AHL_PC_NODES_B NODE, AHL_PC_HEADERS_B HEADER
WHERE p_copy_assos_flag = 'Y'
AND NODE.PC_HEADER_ID = HEADER.PC_HEADER_ID
AND NODE.PC_NODE_ID = AHS.PC_NODE_ID
AND HEADER.PC_HEADER_ID = p_header_id
AND UNIT.UNIT_CONFIG_HEADER_ID(+) = AHS.UNIT_ITEM_ID
AND UNIT.CSI_ITEM_INSTANCE_ID = CSI.INSTANCE_ID(+)
AND DECODE(AHS.ASSOCIATION_TYPE_FLAG,'I',AHS.UNIT_ITEM_ID,
'U',CSI.INVENTORY_ITEM_ID) = MTL.INVENTORY_ITEM_ID
-- SATHAPLI::Bug# 5576835, 20-Aug-2007
/*
AND DECODE(AHS.ASSOCIATION_TYPE_FLAG,'I',FND_PROFILE.VALUE('ORG_ID'),
'U',CSI.INV_MASTER_ORGANIZATION_ID) = MTL.ORGANIZATION_ID
*/
AND DECODE(AHS.ASSOCIATION_TYPE_FLAG,'I',AHS.INVENTORY_ORG_ID,
'U',CSI.INV_MASTER_ORGANIZATION_ID) = MTL.ORGANIZATION_ID
AND DECODE(AHS.ASSOCIATION_TYPE_FLAG,'I',MTL.ITEM_TYPE,
'U',HEADER.PRODUCT_TYPE_CODE) = MTL.ITEM_TYPE)
ORDER BY PARENT_NODE_ID;
SELECT *
FROM AHL_DOC_TITLE_ASSOS_VL
WHERE ASO_OBJECT_TYPE_CODE ='PC' AND
ASO_OBJECT_ID = p_node_id;
select primary_flag
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;
SELECT 'x'
FROM AHL_PC_HEADERS_B
WHERE NAME = p_pc_name;
SELECT STATUS, NAME
FROM AHL_PC_HEADERS_B
WHERE PC_HEADER_ID = l_pc_header_id AND
OBJECT_VERSION_NUMBER = l_object_version_number;
UPDATE AHL_PC_HEADERS_B
SET STATUS = 'APPROVAL_PENDING', OBJECT_VERSION_NUMBER = l_object_version_number
WHERE PC_HEADER_ID = p_x_pc_header_rec.PC_HEADER_ID AND
OBJECT_VERSION_NUMBER = p_x_pc_header_rec.OBJECT_VERSION_NUMBER;
UPDATE AHL_PC_HEADERS_B
SET STATUS = 'COMPLETE',
OBJECT_VERSION_NUMBER = l_object_version_number,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
WHERE PC_HEADER_ID = p_x_pc_header_rec.PC_HEADER_ID AND
OBJECT_VERSION_NUMBER = p_x_pc_header_rec.OBJECT_VERSION_NUMBER;
SELECT 'X'
FROM AHL_PC_HEADERS_B
WHERE PC_HEADER_ID <> nvl(p_pc_header_id,0) AND
-- UPPER(NAME) = UPPER(p_name) AND
NAME = p_name AND
--STATUS = p_status AND
DRAFT_FLAG <> 'Y';
SELECT 'X'
FROM AHL_PC_HEADERS_B
WHERE PC_HEADER_ID <> p_pc_header_id AND
PRIMARY_FLAG = 'Y' AND
PRODUCT_TYPE_CODE like p_prod_type AND
DRAFT_FLAG = 'N';
SELECT 'X'
FROM AHL_PC_TREE_V
WHERE PC_HEADER_ID = p_header_id AND
NODE_TYPE IN (G_PART, G_UNIT);
SELECT 'X'
FROM AHL_PC_ASSOCIATIONS AHS,
AHL_PC_NODES_B NODE
WHERE NODE.PC_NODE_ID = AHS.PC_NODE_ID
AND NODE.PC_HEADER_ID = p_header_id;
IF p_x_pc_header_rec.OPERATION_FLAG NOT IN (AHL_PC_HEADER_PVT.G_DML_DELETE) THEN
-- CHECK NAME UNIQUE
OPEN check_name(p_pc_header_id => p_x_pc_header_rec.PC_HEADER_ID,
p_name => p_x_pc_header_rec.NAME,
p_status => p_x_pc_header_rec.STATUS );
select ahass.unit_item_id, count(ahass.pc_node_id)
from ahl_pc_headers_b head, ahl_pc_nodes_b node, ahl_pc_associations ahass
where ahass.pc_node_id = node.pc_node_id and
node.pc_header_id = head.pc_header_id and
head.pc_header_id = p_pc_header_id
group by ahass.unit_item_id
having count(ahass.pc_node_id) > 1;
PROCEDURE DELETE_PC_AND_TREE (p_pc_header_id IN NUMBER)
IS
TYPE T_ID_TBL IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
AHL_DEBUG_PUB.debug('PCH -- PVT -- DELETE_PC_AND_TREE -- Reading PC Tree (Nodes)');
SELECT PC_NODE_ID
BULK COLLECT INTO l_node_tbl
FROM AHL_PC_NODES_B
WHERE PC_HEADER_ID = p_pc_header_id
ORDER BY PC_NODE_ID DESC;
AHL_DEBUG_PUB.debug('PCH -- PVT -- DELETE_PC_AND_TREE -- Handling force delete for Node ID='||l_node_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(i)
);
DELETE FROM AHL_DOC_TITLE_ASSOS_B
WHERE ASO_OBJECT_TYPE_CODE = 'PC' and
ASO_OBJECT_ID = l_node_tbl(i);
DELETE FROM AHL_PC_ASSOCIATIONS
WHERE PC_NODE_ID = l_node_tbl(i);
AHL_PC_NODES_PKG.DELETE_ROW(l_node_tbl(i));
AHL_DEBUG_PUB.debug('PCH -- PVT -- DELETE_PC_AND_TREE -- Handling force delete for PC');
AHL_PC_HEADERS_PKG.DELETE_ROW(p_pc_header_id);
END DELETE_PC_AND_TREE;
SELECT *
FROM AHL_PC_HEADERS_B
WHERE PC_HEADER_ID = p_header_id;
SELECT *
FROM AHL_PC_TREE_V
WHERE PC_HEADER_ID = p_header_id
ORDER BY PARENT_NODE_ID;
SELECT *
FROM (SELECT AHNO.ROW_ID,
AHNO.PC_NODE_ID,
AHNO.OBJECT_VERSION_NUMBER,
AHNO.LAST_UPDATE_DATE,
AHNO.LAST_UPDATED_BY,
AHNO.CREATION_DATE,
AHNO.CREATED_BY,
AHNO.LAST_UPDATE_LOGIN,
AHNO.PC_HEADER_ID,
AHNO.NAME,
AHNO.PARENT_NODE_ID,
AHNO.CHILD_COUNT,
AHNO.LINK_TO_NODE_ID,
AHNO.DRAFT_FLAG,
AHNO.DESCRIPTION,
'N' NODE_TYPE,
0 UNIT_ITEM_ID,
0 INVENTORY_ORG_ID,
AHNO.OPERATION_STATUS_FLAG,
AHNO.SECURITY_GROUP_ID,
AHNO.ATTRIBUTE_CATEGORY,
AHNO.ATTRIBUTE1,
AHNO.ATTRIBUTE2,
AHNO.ATTRIBUTE3,
AHNO.ATTRIBUTE4,
AHNO.ATTRIBUTE5,
AHNO.ATTRIBUTE6,
AHNO.ATTRIBUTE7,
AHNO.ATTRIBUTE8,
AHNO.ATTRIBUTE9,
AHNO.ATTRIBUTE10,
AHNO.ATTRIBUTE11,
AHNO.ATTRIBUTE12,
AHNO.ATTRIBUTE13,
AHNO.ATTRIBUTE14,
AHNO.ATTRIBUTE15
FROM AHL_PC_NODES_VL AHNO
WHERE AHNO.PC_HEADER_ID = p_header_id
UNION
SELECT DISTINCT AHS.ROWID ROW_ID,
AHS.PC_ASSOCIATION_ID PC_NODE_ID,
AHS.OBJECT_VERSION_NUMBER,
AHS.LAST_UPDATE_DATE,
AHS.LAST_UPDATED_BY,
AHS.CREATION_DATE,
AHS.CREATED_BY,
AHS.LAST_UPDATE_LOGIN,
NODE.PC_HEADER_ID,
DECODE(AHS.ASSOCIATION_TYPE_FLAG,'U',UNIT.NAME,MTL.CONCATENATED_SEGMENTS) NAME,
AHS.PC_NODE_ID PARENT_NODE_ID,
0 CHILD_COUNT,
AHS.LINK_TO_ASSOCIATION_ID LINK_TO_NODE_ID,
AHS.DRAFT_FLAG,
MTL.DESCRIPTION,
AHS.ASSOCIATION_TYPE_FLAG NODE_TYPE,
AHS.UNIT_ITEM_ID,
AHS.INVENTORY_ORG_ID,
AHS.OPERATION_STATUS_FLAG,
AHS.SECURITY_GROUP_ID,
AHS.ATTRIBUTE_CATEGORY,
AHS.ATTRIBUTE1,
AHS.ATTRIBUTE2,
AHS.ATTRIBUTE3,
AHS.ATTRIBUTE4,
AHS.ATTRIBUTE5,
AHS.ATTRIBUTE6,
AHS.ATTRIBUTE7,
AHS.ATTRIBUTE8,
AHS.ATTRIBUTE9,
AHS.ATTRIBUTE10,
AHS.ATTRIBUTE11,
AHS.ATTRIBUTE12,
AHS.ATTRIBUTE13,
AHS.ATTRIBUTE14,
AHS.ATTRIBUTE15
FROM AHL_PC_ASSOCIATIONS AHS, AHL_UNIT_CONFIG_HEADERS UNIT,
CSI_ITEM_INSTANCES CSI, MTL_SYSTEM_ITEMS_KFV MTL,
AHL_PC_NODES_B NODE, AHL_PC_HEADERS_B HEADER
WHERE NODE.PC_HEADER_ID = HEADER.PC_HEADER_ID
AND NODE.PC_NODE_ID = AHS.PC_NODE_ID
AND HEADER.PC_HEADER_ID = p_header_id
AND UNIT.UNIT_CONFIG_HEADER_ID(+) = AHS.UNIT_ITEM_ID
AND UNIT.CSI_ITEM_INSTANCE_ID = CSI.INSTANCE_ID(+)
AND DECODE(AHS.ASSOCIATION_TYPE_FLAG,'I',AHS.UNIT_ITEM_ID,
'U',CSI.INVENTORY_ITEM_ID) = MTL.INVENTORY_ITEM_ID
-- SATHAPLI::Bug# 5576835, 20-Aug-2007
/*
AND DECODE(AHS.ASSOCIATION_TYPE_FLAG,'I',FND_PROFILE.VALUE('ORG_ID'),
'U',CSI.INV_MASTER_ORGANIZATION_ID) = MTL.ORGANIZATION_ID
*/
AND DECODE(AHS.ASSOCIATION_TYPE_FLAG,'I',AHS.INVENTORY_ORG_ID,
'U',CSI.INV_MASTER_ORGANIZATION_ID) = MTL.ORGANIZATION_ID
AND DECODE(AHS.ASSOCIATION_TYPE_FLAG,'I',MTL.ITEM_TYPE,
'U',HEADER.PRODUCT_TYPE_CODE) = MTL.ITEM_TYPE)
ORDER BY PARENT_NODE_ID;
SELECT *
FROM AHL_DOC_TITLE_ASSOS_VL
WHERE ASO_OBJECT_TYPE_CODE ='PC' AND
ASO_OBJECT_ID = p_node_id;
UPDATE AHL_PC_HEADERS_B
SET DRAFT_FLAG = 'Y'
WHERE PC_HEADER_ID = l_old_header_id;
UPDATE AHL_PC_NODES_B
SET DRAFT_FLAG = 'Y'
WHERE PC_NODE_ID = l_node_data_rec.PC_NODE_ID;
UPDATE AHL_PC_ASSOCIATIONS
SET DRAFT_FLAG = 'Y'
WHERE PC_ASSOCIATION_ID = l_node_data_rec.PC_NODE_ID;
select link_to_node_id
from ahl_pc_nodes_b
where pc_node_id = p_pc_node_id;
PROCEDURE DELETE_NODES_REMOVE_LINK (p_x_node_rec IN AHL_PC_NODE_PUB.PC_NODE_REC)
IS
TYPE T_ID_TBL IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
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;
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;
AHL_DEBUG_PUB.debug('PCH -- PVT -- DELETE_NODES_REMOVE_LINK reading child-node-tree');
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;
AHL_DEBUG_PUB.debug('PCH -- PVT -- DELETE_NODES_REMOVE_LINK reading association-tree');
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
);
AHL_DEBUG_PUB.debug('PCH -- PVT -- DELETE_NODES_REMOVE_LINK retrieving linked_node_id which will have FMP, UMP and Doc associations');
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_linked_node_id
);
DELETE
FROM AHL_DOC_TITLE_ASSOS_B
WHERE aso_object_type_code = 'PC' and
aso_object_id = l_linked_node_id;
AHL_PC_NODES_PKG.DELETE_ROW(l_node_tbl(j));
AHL_DEBUG_PUB.debug('PCH -- PVT -- DELETE_NODES_REMOVE_LINK for pc_node_id='||p_x_node_rec.PC_NODE_ID);
UPDATE ahl_pc_nodes_b
SET child_count = NVL(child_count,1) - 1
WHERE pc_node_id = p_x_node_rec.parent_node_id;
END DELETE_NODES_REMOVE_LINK;
DELETE FROM AHL_PC_ASSOCIATIONS
WHERE PC_ASSOCIATION_ID = p_x_assos_rec.PC_ASSOCIATION_ID;
UPDATE ahl_pc_nodes_b
SET child_count = NVL(child_count, 1) - 1
WHERE pc_node_id = p_x_assos_rec.pc_node_id;
DELETE FROM AHL_PC_ASSOCIATIONS
WHERE PC_ASSOCIATION_ID = p_x_assos_rec.PC_ASSOCIATION_ID;
UPDATE ahl_pc_nodes_b
SET child_count = NVL(child_count, 1) - 1
WHERE pc_node_id = p_x_assos_rec.pc_node_id;
SELECT *
FROM AHL_PC_TREE_V TREE
WHERE TREE.PC_HEADER_ID = p_header_id AND
(
TREE.NODE_TYPE='N' OR
(
TREE.NODE_TYPE <> 'N' AND
TREE.LINK_TO_NODE_ID = 0 AND
-- PARENT NODE ID IS NOT IN THE LIST OF NEWLY ATTACHED NODES
TREE.PARENT_NODE_ID NOT IN
(
SELECT NODE.PC_NODE_ID
FROM AHL_PC_NODES_B NODE
WHERE NODE.PC_NODE_ID = TREE.PARENT_NODE_ID AND
NODE.LINK_TO_NODE_ID = 0
)
)
)
ORDER BY TREE.PARENT_NODE_ID ;
SELECT *
FROM (
SELECT AHNO.PC_NODE_ID,
AHNO.PC_HEADER_ID,
AHNO.NAME,
AHNO.PARENT_NODE_ID,
AHNO.CHILD_COUNT,
AHNO.LINK_TO_NODE_ID,
AHNO.DESCRIPTION,
'N' NODE_TYPE,
0 UNIT_ITEM_ID,
0 INVENTORY_ORG_ID,
AHNO.ATTRIBUTE_CATEGORY,
AHNO.ATTRIBUTE1,
AHNO.ATTRIBUTE2,
AHNO.ATTRIBUTE3,
AHNO.ATTRIBUTE4,
AHNO.ATTRIBUTE5,
AHNO.ATTRIBUTE6,
AHNO.ATTRIBUTE7,
AHNO.ATTRIBUTE8,
AHNO.ATTRIBUTE9,
AHNO.ATTRIBUTE10,
AHNO.ATTRIBUTE11,
AHNO.ATTRIBUTE12,
AHNO.ATTRIBUTE13,
AHNO.ATTRIBUTE14,
AHNO.ATTRIBUTE15
FROM AHL_PC_NODES_VL AHNO
WHERE AHNO.PC_HEADER_ID = p_header_id
UNION
SELECT AHS.PC_ASSOCIATION_ID PC_NODE_ID,
NODE.PC_HEADER_ID,
DECODE(AHS.ASSOCIATION_TYPE_FLAG,'U',UNIT.NAME,MTL.CONCATENATED_SEGMENTS) NAME,
AHS.PC_NODE_ID PARENT_NODE_ID,
0 CHILD_COUNT,
AHS.LINK_TO_ASSOCIATION_ID LINK_TO_NODE_ID,
MTL.DESCRIPTION,
AHS.ASSOCIATION_TYPE_FLAG NODE_TYPE,
AHS.UNIT_ITEM_ID,
AHS.INVENTORY_ORG_ID,
AHS.ATTRIBUTE_CATEGORY,
AHS.ATTRIBUTE1,
AHS.ATTRIBUTE2,
AHS.ATTRIBUTE3,
AHS.ATTRIBUTE4,
AHS.ATTRIBUTE5,
AHS.ATTRIBUTE6,
AHS.ATTRIBUTE7,
AHS.ATTRIBUTE8,
AHS.ATTRIBUTE9,
AHS.ATTRIBUTE10,
AHS.ATTRIBUTE11,
AHS.ATTRIBUTE12,
AHS.ATTRIBUTE13,
AHS.ATTRIBUTE14,
AHS.ATTRIBUTE15
FROM AHL_PC_ASSOCIATIONS AHS, AHL_UNIT_CONFIG_HEADERS UNIT,
CSI_ITEM_INSTANCES CSI, MTL_SYSTEM_ITEMS_KFV MTL,
AHL_PC_NODES_B NODE, AHL_PC_HEADERS_B HEADER
WHERE NODE.PC_NODE_ID = AHS.PC_NODE_ID
AND HEADER.PC_HEADER_ID = NODE.PC_HEADER_ID
AND NODE.PC_HEADER_ID = p_header_id
AND UNIT.UNIT_CONFIG_HEADER_ID(+) = AHS.UNIT_ITEM_ID
AND UNIT.CSI_ITEM_INSTANCE_ID = CSI.INSTANCE_ID(+)
AND DECODE(AHS.ASSOCIATION_TYPE_FLAG,'I',AHS.UNIT_ITEM_ID,
'U',CSI.INVENTORY_ITEM_ID) = MTL.INVENTORY_ITEM_ID
-- SATHAPLI::Bug# 5576835, 20-Aug-2007
/*
AND DECODE(AHS.ASSOCIATION_TYPE_FLAG,'I',FND_PROFILE.VALUE('ORG_ID'),
'U',CSI.INV_MASTER_ORGANIZATION_ID) = MTL.ORGANIZATION_ID
*/
AND DECODE(AHS.ASSOCIATION_TYPE_FLAG,'I',AHS.INVENTORY_ORG_ID,
'U',CSI.INV_MASTER_ORGANIZATION_ID) = MTL.ORGANIZATION_ID
AND DECODE(AHS.ASSOCIATION_TYPE_FLAG,'I',MTL.ITEM_TYPE,
'U',HEADER.PRODUCT_TYPE_CODE) = MTL.ITEM_TYPE
) TREE
WHERE (
TREE.NODE_TYPE='N' OR
(
TREE.NODE_TYPE <> 'N' AND
TREE.LINK_TO_NODE_ID = 0 AND
-- PARENT NODE ID IS NOT IN THE LIST OF NEWLY ATTACHED NODES
NOT EXISTS
(
SELECT 'X'
FROM AHL_PC_NODES_B NODE
WHERE NODE.PC_NODE_ID = TREE.PARENT_NODE_ID AND
NODE.LINK_TO_NODE_ID = 0
)
)
)
ORDER BY TREE.PARENT_NODE_ID;
SELECT *
FROM AHL_PC_TREE_V TREE
WHERE TREE.PC_HEADER_ID = p_link_header_id AND
-- NODE ID NOT FOUND IN LINKED PC - i.e. NODE HAS BEEN DELETED
TREE.PC_NODE_ID NOT IN
(
SELECT TREE1.LINK_TO_NODE_ID
FROM AHL_PC_TREE_V TREE1
WHERE TREE1.PC_HEADER_ID = p_header_id
)
-- OR( TREE.PC_NODE_ID = p_link_header_id AND PARENT_NODE_ID <> 0) --
-- ) --
AND
(
-- NODE IS ROOT NODE
TREE.PARENT_NODE_ID = 0 OR
-- PARENT NODE ID IS NOT IN THE LIST OF DELETED NODES
-- AS IF PARENT IS BEING DELETED THE CHILD WILL AUTOMATICALLY GETS DELETED
TREE.PARENT_NODE_ID IN
(
SELECT TREE1.LINK_TO_NODE_ID
FROM AHL_PC_TREE_V TREE1
WHERE TREE1.PC_HEADER_ID = p_header_id
)
)
ORDER BY TREE.PARENT_NODE_ID;
SELECT *
FROM (
SELECT AHNO.PC_NODE_ID,
AHNO.OBJECT_VERSION_NUMBER,
AHNO.PC_HEADER_ID,
AHNO.NAME,
AHNO.PARENT_NODE_ID,
AHNO.CHILD_COUNT,
AHNO.DESCRIPTION,
'N' NODE_TYPE,
0 UNIT_ITEM_ID,
0 INVENTORY_ORG_ID
FROM AHL_PC_NODES_VL AHNO
UNION
SELECT AHS.PC_ASSOCIATION_ID PC_NODE_ID,
AHS.OBJECT_VERSION_NUMBER,
NODE.PC_HEADER_ID,
DECODE(AHS.ASSOCIATION_TYPE_FLAG,'U',UNIT.NAME,MTL.CONCATENATED_SEGMENTS) NAME,
AHS.PC_NODE_ID PARENT_NODE_ID,
0 CHILD_COUNT,
MTL.DESCRIPTION,
AHS.ASSOCIATION_TYPE_FLAG NODE_TYPE,
AHS.UNIT_ITEM_ID,
AHS.INVENTORY_ORG_ID
FROM AHL_PC_ASSOCIATIONS AHS, AHL_UNIT_CONFIG_HEADERS UNIT,
CSI_ITEM_INSTANCES CSI, MTL_SYSTEM_ITEMS_KFV MTL,
AHL_PC_NODES_B NODE, AHL_PC_HEADERS_B HEADER
WHERE NODE.PC_NODE_ID = AHS.PC_NODE_ID
AND HEADER.PC_HEADER_ID = NODE.PC_HEADER_ID
AND UNIT.UNIT_CONFIG_HEADER_ID(+) = AHS.UNIT_ITEM_ID
AND UNIT.CSI_ITEM_INSTANCE_ID = CSI.INSTANCE_ID(+)
AND DECODE(AHS.ASSOCIATION_TYPE_FLAG,'I',AHS.UNIT_ITEM_ID,
'U',CSI.INVENTORY_ITEM_ID) = MTL.INVENTORY_ITEM_ID
-- SATHAPLI::Bug# 5576835, 20-Aug-2007
/*
AND DECODE(AHS.ASSOCIATION_TYPE_FLAG,'I',FND_PROFILE.VALUE('ORG_ID'),
'U',CSI.INV_MASTER_ORGANIZATION_ID) = MTL.ORGANIZATION_ID
*/
AND DECODE(AHS.ASSOCIATION_TYPE_FLAG,'I',AHS.INVENTORY_ORG_ID,
'U',CSI.INV_MASTER_ORGANIZATION_ID) = MTL.ORGANIZATION_ID
AND DECODE(AHS.ASSOCIATION_TYPE_FLAG,'I',MTL.ITEM_TYPE,
'U',HEADER.PRODUCT_TYPE_CODE) = MTL.ITEM_TYPE
) TREE
WHERE TREE.PC_HEADER_ID = p_link_header_id AND
-- NODE ID NOT FOUND IN LINKED PC - i.e. NODE HAS BEEN DELETED
NOT EXISTS
(
-- Changes by skpathak on 27-NOV-2008 for bug 7512088
-- If the table AHL_PC_ASSOCIATIONS does not have any records,
-- the following this sub query does not bring any rows even
-- if all other conditions are met. Hence changing this into a union
-- of two queries
/*
SELECT 'X'
FROM AHL_PC_ASSOCIATIONS ASSOC,AHL_PC_NODES_B NODE
WHERE NODE.PC_HEADER_ID = p_header_id
AND (
(TREE.NODE_TYPE = 'N' AND
TREE.PC_NODE_ID = NODE.LINK_TO_NODE_ID)
OR
(TREE.NODE_TYPE IN ('I', 'U') AND
ASSOC.PC_NODE_ID = NODE.PC_NODE_ID AND
TREE.PC_NODE_ID = ASSOC.LINK_TO_ASSOCIATION_ID)
)
*/
SELECT 'X'
FROM AHL_PC_NODES_B NODE
WHERE NODE.PC_HEADER_ID = p_header_id
AND TREE.NODE_TYPE = 'N'
AND TREE.PC_NODE_ID = NODE.LINK_TO_NODE_ID
UNION ALL
SELECT 'X'
FROM AHL_PC_NODES_B NODE, AHL_PC_ASSOCIATIONS ASSOC
WHERE NODE.PC_HEADER_ID = p_header_id
AND TREE.NODE_TYPE IN ('I', 'U')
AND ASSOC.PC_NODE_ID = NODE.PC_NODE_ID
AND TREE.PC_NODE_ID = ASSOC.LINK_TO_ASSOCIATION_ID
)
AND
(
-- NODE IS ROOT NODE
TREE.PARENT_NODE_ID = 0 OR
-- PARENT NODE ID IS NOT IN THE LIST OF DELETED NODES
-- AS IF PARENT IS BEING DELETED THE CHILD WILL AUTOMATICALLY GETS DELETED
EXISTS
(
SELECT 'X'
FROM AHL_PC_NODES_B NODE
WHERE NODE.PC_HEADER_ID = p_header_id
AND TREE.PARENT_NODE_ID = NODE.LINK_TO_NODE_ID
)
)
ORDER BY TREE.PARENT_NODE_ID;
SELECT * FROM ahl_pc_associations
WHERE pc_node_id in ( SELECT PC_NODE_ID
FROM ahl_pc_nodes_b
WHERE PC_HEADER_ID = p_link_header_id)
AND pc_association_id NOT IN (
SELECT LINK_TO_ASSOCIATION_ID
FROM ahl_pc_associations
WHERE pc_node_id IN ( SELECT PC_NODE_ID
FROM ahl_pc_nodes_b
WHERE PC_HEADER_ID = p_header_id));
CURSOR delete_header(p_link_header_id in number)
IS
SELECT 'X'
FROM AHL_PC_HEADERS_B
WHERE PC_HEADER_ID = p_link_header_id;
select mrh.mr_header_id, mrh.title, mrh.version_number
from ahl_mr_headers_app_v mrh, ahl_mr_effectivities mre, ahl_pc_nodes_b pcn
where trunc(sysdate) < trunc(nvl(mrh.effective_to, sysdate+1)) and
mrh.mr_header_id = mre.mr_header_id and
mre.pc_node_id = pcn.pc_node_id and
pcn.pc_header_id = c_pc_header_id;
select fmp.mr_header_id, fmp.title, fmp.version_number
from ahl_mr_pc_nodes_v fmp, ahl_pc_nodes_b node
where fmp.pc_node_id = node.pc_node_id and
node.pc_header_id = c_pc_header_id;
SELECT mrh.mr_header_id, mre.mr_effectivity_id
FROM ahl_mr_headers_b mrh, ahl_mr_effectivities mre,
ahl_pc_nodes_b pcn
WHERE mrh.mr_header_id = mre.mr_header_id
AND mre.pc_node_id = pcn.pc_node_id
AND pcn.pc_header_id = p_pc_header_id
AND TRUNC(NVL(mrh.effective_to, SYSDATE+1)) > TRUNC(SYSDATE);
OPEN delete_header(p_x_pc_header_rec.LINK_TO_PC_ID);
FETCH delete_header INTO l_dummy;
IF (delete_header%NOTFOUND)
THEN
IF G_DEBUG='Y' THEN
AHL_DEBUG_PUB.debug('PCH -- PVT -- REMOVE_LINK -- Aborting because not found linked-to PC');
CLOSE delete_header;
CLOSE delete_header;
SELECT OBJECT_VERSION_NUMBER INTO p_x_pc_header_rec.OBJECT_VERSION_NUMBER
FROM AHL_PC_HEADERS_B
WHERE PC_HEADER_ID = l_pc_header_rec.LINK_TO_PC_ID;
'About to call UPDATE_PC_HEADER with p_x_pc_header_rec.PC_HEADER_ID = ' || p_x_pc_header_rec.PC_HEADER_ID);
UPDATE_PC_HEADER
(
p_api_version => p_api_version,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => p_validation_level,
p_x_pc_header_rec => p_x_pc_header_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
'Returned from UPDATE_PC_HEADER with x_return_status = ' || x_return_status);
' Updated linked-to PC');
' Starting DELETE-'||l_pc_header_rec.PC_HEADER_ID);
' Starting DELETEING record -- '||l_node_data_rec_det.PC_NODE_ID);
l_node_rec.OPERATION_FLAG := AHL_PC_HEADER_PVT.G_DML_DELETE;
l_assos_rec.OPERATION_FLAG := AHL_PC_HEADER_PVT.G_DML_DELETE;
DELETE_NODES_REMOVE_LINK (l_node_rec);
l_assos_rec.OPERATION_FLAG := AHL_PC_HEADER_PVT.G_DML_DELETE;
' Ending DELETE msg_count='||x_msg_count);
SELECT OBJECT_VERSION_NUMBER INTO l_node_rec.OBJECT_VERSION_NUMBER
FROM AHL_PC_NODES_B
WHERE PC_NODE_ID = l_node_rec.PC_NODE_ID;
SELECT OBJECT_VERSION_NUMBER INTO l_assos_rec.OBJECT_VERSION_NUMBER
FROM AHL_PC_ASSOCIATIONS
WHERE PC_ASSOCIATION_ID = l_assos_rec.PC_ASSOCIATION_ID;
AHL_PC_NODE_PVT.UPDATE_NODE
(
p_api_version => p_api_version,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => p_validation_level,
p_x_node_rec => l_node_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
SELECT pc_node_id,
link_to_node_id
BULK COLLECT INTO
l_draft_nodeId_tbl,
l_comp_nodeId_tbl
FROM ahl_pc_nodes_b
WHERE PC_HEADER_ID = l_pc_header_rec.pc_header_id
AND NVL(LINK_TO_NODE_ID,0) <> 0
START WITH PARENT_NODE_ID = 0
CONNECT BY PRIOR PC_NODE_ID = PARENT_NODE_ID;
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_comp_nodeId_tbl(I)
);
DELETE
FROM AHL_DOC_TITLE_ASSOS_B
WHERE aso_object_type_code = 'PC' and
aso_object_id = l_comp_nodeId_tbl(I);
UPDATE AHL_DOC_TITLE_ASSOS_B
SET ASO_OBJECT_ID = l_comp_nodeId_tbl(I),
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER +1
WHERE ASO_OBJECT_ID = l_draft_nodeId_tbl(I)
AND ASO_OBJECT_TYPE_CODE = 'PC';
l_pc_header_rec.OPERATION_FLAG := AHL_PC_HEADER_PVT.G_DML_DELETE;
DELETE_PC_AND_TREE(l_pc_header_rec.pc_header_id);
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,l_full_name,' Deleted Header Record');
SELECT 'X'
FROM AHL_PC_HEADERS_B
WHERE PC_HEADER_ID = p_pc_header_id AND
PRODUCT_TYPE_CODE = p_prod_type AND
ASSOCIATION_TYPE_FLAG = p_assos_type;
SELECT 'X'
FROM AHL_PC_TREE_V
WHERE PC_HEADER_ID = p_pc_header_id AND
NODE_TYPE IN (G_PART, G_UNIT);
SELECT 'X'
FROM AHL_PC_ASSOCIATIONS AHS,
AHL_PC_NODES_B NODE
WHERE NODE.PC_NODE_ID = AHS.PC_NODE_ID
AND NODE.PC_HEADER_ID = p_pc_header_id;
PROCEDURE VALIDATE_PC_HEADER_UPDATE
(
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_pc_header_rec IN OUT NOCOPY AHL_PC_HEADER_PUB.PC_HEADER_REC,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
CURSOR check_header_data (p_header_id IN NUMBER)
IS
SELECT OBJECT_VERSION_NUMBER,
PRODUCT_TYPE_CODE,
STATUS,
PRIMARY_FLAG,
ASSOCIATION_TYPE_FLAG,
LINK_TO_PC_ID
FROM AHL_PC_HEADERS_B
WHERE PC_HEADER_ID = p_header_id;
SELECT 'X'
FROM AHL_PC_TREE_V
WHERE PC_HEADER_ID = p_header_id AND
NODE_TYPE IN (G_PART, G_UNIT);
SELECT 'X'
FROM AHL_PC_ASSOCIATIONS AHS,
AHL_PC_NODES_B NODE
WHERE NODE.PC_NODE_ID = AHS.PC_NODE_ID
AND NODE.PC_HEADER_ID = p_header_id;
AHL_DEBUG_PUB.debug('PCH -- PVT -- VALIDATE_PC_HEADER_UPDATE -- Old Status = '||l_old_status||' -- New Status = '||p_x_pc_header_rec.STATUS);
END VALIDATE_PC_HEADER_UPDATE;