[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_DML_UPDATE CONSTANT VARCHAR2(1) := 'U';
G_DML_DELETE CONSTANT VARCHAR2(1) := 'D';
SELECT 'X'
INTO l_dummy
FROM DUAL
WHERE EXISTS (SELECT 1
FROM AHL_RA_SETUPS
WHERE SETUP_CODE = l_setup_data_rec.SETUP_CODE
AND nvl(STATUS_ID,'-1') = nvl(DECODE(l_setup_data_rec.SETUP_CODE,'ITEM_STATUS',l_setup_data_rec.STATUS_ID,STATUS_ID),'-1')
AND nvl(REMOVAL_CODE,'-1') = nvl(DECODE(l_setup_data_rec.SETUP_CODE,'REMOVAL_CODE',l_setup_data_rec.REMOVAL_CODE,REMOVAL_CODE),'-1'));
SELECT STATUS_CODE
INTO l_code
FROM MTL_MATERIAL_STATUSES_VL
WHERE STATUS_ID = l_setup_data_rec.STATUS_ID;
SELECT MEANING
INTO l_code
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'AHL_REMOVAL_CODE'
AND LOOKUP_CODE = l_setup_data_rec.REMOVAL_CODE;
SELECT 'X'
INTO l_dummy
FROM DUAL
WHERE EXISTS (SELECT 1
FROM MTL_MATERIAL_STATUSES_VL
WHERE STATUS_ID = l_setup_data_rec.STATUS_ID);
SELECT 'X'
INTO l_dummy
FROM DUAL
WHERE EXISTS (SELECT 1
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'AHL_REMOVAL_CODE'
AND LOOKUP_CODE = l_setup_data_rec.REMOVAL_CODE);
SELECT AHL_RA_SETUPS_S.NEXTVAL INTO l_setup_data_rec.RA_SETUP_ID FROM DUAL;
l_setup_data_rec.LAST_UPDATED_BY := fnd_global.USER_ID;
l_setup_data_rec.LAST_UPDATE_LOGIN := fnd_global.LOGIN_ID;
l_setup_data_rec.LAST_UPDATE_DATE := sysdate;
INSERT INTO AHL_RA_SETUPS(RA_SETUP_ID,SETUP_CODE,STATUS_ID,REMOVAL_CODE,OBJECT_VERSION_NUMBER,SECURITY_GROUP_ID,
CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN,ATTRIBUTE_CATEGORY,
ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3,ATTRIBUTE4,ATTRIBUTE5,ATTRIBUTE6,ATTRIBUTE7,
ATTRIBUTE8,ATTRIBUTE9,ATTRIBUTE10,ATTRIBUTE11,ATTRIBUTE12,ATTRIBUTE13,ATTRIBUTE14,ATTRIBUTE15)
VALUES(
l_setup_data_rec.RA_SETUP_ID -- RA_SETUP_ID
,l_setup_data_rec.SETUP_CODE -- SETUP_CODE
,l_setup_data_rec.STATUS_ID -- STATUS_ID
,l_setup_data_rec.REMOVAL_CODE -- REMOVAL_CODE
,l_setup_data_rec.OBJECT_VERSION_NUMBER-- OBJECT_VERSION_NUMBER
,l_setup_data_rec.SECURITY_GROUP_ID -- SECURITY_GROUP_ID
,l_setup_data_rec.CREATION_DATE -- CREATION_DATE
,l_setup_data_rec.CREATED_BY -- CREATED_BY
,l_setup_data_rec.LAST_UPDATE_DATE -- LAST_UPDATE_DATE
,l_setup_data_rec.LAST_UPDATED_BY -- LAST_UPDATED_BY
,l_setup_data_rec.LAST_UPDATE_LOGIN -- LAST_UPDATE_LOGIN
,l_setup_data_rec.ATTRIBUTE_CATEGORY -- ATTRIBUTE_CATEGORY
,l_setup_data_rec.ATTRIBUTE1 -- ATTRIBUTE1
,l_setup_data_rec.ATTRIBUTE2 -- ATTRIBUTE2
,l_setup_data_rec.ATTRIBUTE3 -- ATTRIBUTE3
,l_setup_data_rec.ATTRIBUTE4 -- ATTRIBUTE4
,l_setup_data_rec.ATTRIBUTE5 -- ATTRIBUTE5
,l_setup_data_rec.ATTRIBUTE6 -- ATTRIBUTE6
,l_setup_data_rec.ATTRIBUTE7 -- ATTRIBUTE7
,l_setup_data_rec.ATTRIBUTE8 -- ATTRIBUTE8
,l_setup_data_rec.ATTRIBUTE9 -- ATTRIBUTE9
,l_setup_data_rec.ATTRIBUTE10 -- ATTRIBUTE10
,l_setup_data_rec.ATTRIBUTE11 -- ATTRIBUTE11
,l_setup_data_rec.ATTRIBUTE12 -- ATTRIBUTE12
,l_setup_data_rec.ATTRIBUTE13 -- ATTRIBUTE13
,l_setup_data_rec.ATTRIBUTE14 -- ATTRIBUTE14
,l_setup_data_rec.ATTRIBUTE15 -- ATTRIBUTE15
);
PROCEDURE DELETE_SETUP_DATA (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_validation_level IN NUMBER,
p_module_type IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_setup_data_rec IN AHL_RA_SETUPS_PVT.RA_SETUP_DATA_REC_TYPE) IS
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_SETUP_DATA';
SAVEPOINT DELETE_SETUP_DATA_SP;
fnd_log.string(fnd_log.level_statement,l_full_name,'DS -- PVT -- DELETE_SETUP_DATA -------BEGIN-----------');
((p_setup_data_rec.OPERATION_FLAG IS NULL) OR (p_setup_data_rec.OPERATION_FLAG <> G_DML_DELETE)) OR
(p_setup_data_rec.OBJECT_VERSION_NUMBER IS NULL))THEN
IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed -- RA SETUP ID :' || p_setup_data_rec.RA_SETUP_ID);
SELECT OBJECT_VERSION_NUMBER
INTO l_obj_version_num
FROM AHL_RA_SETUPS
WHERE RA_SETUP_ID = p_setup_data_rec.RA_SETUP_ID
FOR UPDATE OF object_version_number NOWAIT;
FND_MESSAGE.Set_Name('AHL','AHL_COM_RECORD_DELETED');
DELETE AHL_RA_SETUPS
WHERE RA_SETUP_ID = p_setup_data_rec.RA_SETUP_ID;
fnd_log.string(fnd_log.level_statement,l_full_name,'DS -- PVT -- DELETE_SETUP_DATA -------END-----------');
ROLLBACK TO DELETE_SETUP_DATA_SP;
ROLLBACK TO DELETE_SETUP_DATA_SP;
ROLLBACK TO DELETE_SETUP_DATA_SP;
p_procedure_name => 'DELETE_SETUP_DATA',
p_error_text => SUBSTR(SQLERRM,1,240));
END DELETE_SETUP_DATA;
SELECT 'Y'
INTO l_dummy
FROM dual
WHERE EXISTS( SELECT 'X'
FROM ahl_mc_headers_b
WHERE mc_header_id = l_ra_def_hdr_rec.MC_HEADER_ID);
SELECT 'Y'
INTO l_dummy
FROM dual
WHERE EXISTS(SELECT 'X'
FROM ahl_mc_relationships
WHERE mc_header_id = l_ra_def_hdr_rec.MC_HEADER_ID
AND relationship_id = l_ra_def_hdr_rec.RELATIONSHIP_ID);
SELECT 'Y'
INTO l_dummy
FROM dual
WHERE EXISTS(SELECT 'X'
FROM ahl_mc_relationships mcr, ahl_item_associations_b ia
WHERE mcr.relationship_id = l_ra_def_hdr_rec.RELATIONSHIP_ID
AND mcr.item_group_id = ia.item_group_id
AND ia.inventory_item_id = l_ra_def_hdr_rec.INVENTORY_ITEM_ID
AND ia.inventory_org_id = l_ra_def_hdr_rec.INVENTORY_ORG_ID
AND nvl(ia.revision,FND_API.G_MISS_CHAR) = nvl(l_ra_def_hdr_rec.ITEM_REVISION,FND_API.G_MISS_CHAR));
SELECT 'Y'
INTO l_dummy
FROM dual
WHERE EXISTS(SELECT 'X'
FROM ahl_ra_definition_hdr
WHERE mc_header_id = l_ra_def_hdr_rec.MC_HEADER_ID
AND relationship_id = l_ra_def_hdr_rec.RELATIONSHIP_ID
AND inventory_item_id = l_ra_def_hdr_rec.INVENTORY_ITEM_ID
AND inventory_org_id = l_ra_def_hdr_rec.INVENTORY_ORG_ID
AND nvl(item_revision,FND_API.G_MISS_CHAR) = nvl(l_ra_def_hdr_rec.ITEM_REVISION,FND_API.G_MISS_CHAR));
SELECT AHL_RA_DEFINITION_HDR_S.NEXTVAL into l_ra_def_hdr_rec.RA_DEFINITION_HDR_ID from dual;
l_ra_def_hdr_rec.LAST_UPDATED_BY := fnd_global.USER_ID;
l_ra_def_hdr_rec.LAST_UPDATE_LOGIN := fnd_global.LOGIN_ID;
l_ra_def_hdr_rec.LAST_UPDATE_DATE := sysdate;
INSERT INTO AHL_RA_DEFINITION_HDR
(
RA_DEFINITION_HDR_ID,
MC_HEADER_ID,
INVENTORY_ITEM_ID,
INVENTORY_ORG_ID,
ITEM_REVISION,
RELATIONSHIP_ID,
OBJECT_VERSION_NUMBER,
SECURITY_GROUP_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
VALUES
(
l_ra_def_hdr_rec.RA_DEFINITION_HDR_ID, -- RA_DEFINITION_HDR_ID
l_ra_def_hdr_rec.MC_HEADER_ID, -- MC_HEADER_ID
l_ra_def_hdr_rec.INVENTORY_ITEM_ID, -- INVENTORY_ITEM_ID
l_ra_def_hdr_rec.INVENTORY_ORG_ID, -- INVENTORY_ORG_ID
l_ra_def_hdr_rec.ITEM_REVISION, -- ITEM_REVISION
l_ra_def_hdr_rec.RELATIONSHIP_ID, -- RELATIONSHIP_ID
l_ra_def_hdr_rec.OBJECT_VERSION_NUMBER, -- OBJECT_VERSION_NUMBER
l_ra_def_hdr_rec.SECURITY_GROUP_ID, -- SECURITY_GROUP_ID
l_ra_def_hdr_rec.ATTRIBUTE_CATEGORY, -- ATTRIBUTE_CATEGORY
l_ra_def_hdr_rec.ATTRIBUTE1, -- ATTRIBUTE1
l_ra_def_hdr_rec.ATTRIBUTE2, -- ATTRIBUTE2
l_ra_def_hdr_rec.ATTRIBUTE3, -- ATTRIBUTE3
l_ra_def_hdr_rec.ATTRIBUTE4, -- ATTRIBUTE4
l_ra_def_hdr_rec.ATTRIBUTE5, -- ATTRIBUTE5
l_ra_def_hdr_rec.ATTRIBUTE6, -- ATTRIBUTE6
l_ra_def_hdr_rec.ATTRIBUTE7, -- ATTRIBUTE7
l_ra_def_hdr_rec.ATTRIBUTE8, -- ATTRIBUTE8
l_ra_def_hdr_rec.ATTRIBUTE9, -- ATTRIBUTE9
l_ra_def_hdr_rec.ATTRIBUTE10, -- ATTRIBUTE10
l_ra_def_hdr_rec.ATTRIBUTE11, -- ATTRIBUTE11
l_ra_def_hdr_rec.ATTRIBUTE12, -- ATTRIBUTE12
l_ra_def_hdr_rec.ATTRIBUTE13, -- ATTRIBUTE13
l_ra_def_hdr_rec.ATTRIBUTE14, -- ATTRIBUTE14
l_ra_def_hdr_rec.ATTRIBUTE15, -- ATTRIBUTE15
l_ra_def_hdr_rec.CREATION_DATE, -- CREATION_DATE
l_ra_def_hdr_rec.CREATED_BY, -- CREATED_BY
l_ra_def_hdr_rec.LAST_UPDATE_DATE, -- LAST_UPDATE_DATE
l_ra_def_hdr_rec.LAST_UPDATED_BY, -- LAST_UPDATED_BY
l_ra_def_hdr_rec.LAST_UPDATE_LOGIN -- LAST_UPDATE_LOGIN
);
PROCEDURE DELETE_RELIABILITY_DATA (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_validation_level IN NUMBER,
p_module_type IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_reliability_data_rec IN AHL_RA_SETUPS_PVT.RA_DEFINITION_HDR_REC_TYPE) IS
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_RELIABILITY_DATA';
SAVEPOINT DELETE_RELIABILITY_DATA_SP;
fnd_log.string(fnd_log.level_statement,l_full_name,'DS -- PVT -- DELETE_RELIABILITY_DATA -------BEGIN-----------');
IF ((p_reliability_data_rec.OPERATION_FLAG IS NULL) OR (p_reliability_data_rec.OPERATION_FLAG <> G_DML_DELETE)) THEN
IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed --');
fnd_log.string(fnd_log.level_statement,l_full_name,'-- Delete counter-MTBF records for RA_DEFINITION_HDR_ID -- ' || p_reliability_data_rec.RA_DEFINITION_HDR_ID);
SELECT OBJECT_VERSION_NUMBER
INTO l_obj_version_num
FROM AHL_RA_DEFINITION_HDR
WHERE RA_DEFINITION_HDR_ID = p_reliability_data_rec.RA_DEFINITION_HDR_ID
FOR UPDATE OF object_version_number NOWAIT;
FND_MESSAGE.Set_Name('AHL','AHL_COM_RECORD_DELETED');
DELETE AHL_RA_DEFINITION_DTLS
WHERE RA_DEFINITION_HDR_ID = p_reliability_data_rec.RA_DEFINITION_HDR_ID;
fnd_log.string(fnd_log.level_statement,l_full_name,'-- Child Records have been deleted --');
DELETE AHL_RA_DEFINITION_HDR
WHERE RA_DEFINITION_HDR_ID = p_reliability_data_rec.RA_DEFINITION_HDR_ID;
fnd_log.string(fnd_log.level_statement,l_full_name,'-- Master Record has been deleted --');
fnd_log.string(fnd_log.level_statement,l_full_name,'DS -- PVT -- DELETE_RELIABILITY_DATA -------END-----------');
ROLLBACK TO DELETE_RELIABILITY_DATA_SP;
ROLLBACK TO DELETE_RELIABILITY_DATA_SP;
ROLLBACK TO DELETE_RELIABILITY_DATA_SP;
p_procedure_name => 'DELETE_RELIABILITY_DATA',
p_error_text => SUBSTR(SQLERRM,1,240));
END DELETE_RELIABILITY_DATA;
SELECT templates.name
INTO l_cou_name
FROM cs_csi_counter_groups cg,
csi_counter_template_vl templates,
csi_ctr_item_associations csia,
ahl_ra_definition_hdr rdh
WHERE templates.counter_id = l_ra_def_dtl_rec.COUNTER_ID
AND cg.template_flag = 'Y'
AND templates.group_id = cg.counter_group_id
AND csia.group_id = cg.counter_group_id
AND csia.inventory_item_id = rdh.inventory_item_id
--Added the following on 13-sep-2005 after the feedback from csi team
AND nvl(csia.associated_to_group,'N') = 'Y'
AND csia.counter_id is null
--mpothuku end
AND rdh.RA_DEFINITION_HDR_ID = l_ra_def_dtl_rec.RA_DEFINITION_HDR_ID
AND trunc(sysdate) < trunc(nvl(templates.end_date_active,sysdate+1))
AND trunc(sysdate) < trunc(nvl(csia.end_date_active,sysdate+1));
SELECT 'Y'
INTO l_dummy
FROM dual
WHERE EXISTS(SELECT 'X'
FROM AHL_RA_DEFINITION_DTLS
WHERE COUNTER_ID = l_ra_def_dtl_rec.COUNTER_ID
AND RA_DEFINITION_HDR_ID = l_ra_def_dtl_rec.RA_DEFINITION_HDR_ID);
SELECT OBJECT_VERSION_NUMBER
INTO l_m_obj_version_num
FROM AHL_RA_DEFINITION_HDR
WHERE RA_DEFINITION_HDR_ID = l_ra_def_dtl_rec.RA_DEFINITION_HDR_ID
FOR UPDATE OF object_version_number NOWAIT;
FND_MESSAGE.Set_Name('AHL','AHL_COM_RECORD_DELETED');
SELECT AHL_RA_DEFINITION_DTLS_S.NEXTVAL into l_ra_def_dtl_rec.RA_DEFINITION_DTL_ID from dual;
l_ra_def_dtl_rec.LAST_UPDATED_BY := fnd_global.USER_ID;
l_ra_def_dtl_rec.LAST_UPDATE_LOGIN := fnd_global.LOGIN_ID;
l_ra_def_dtl_rec.LAST_UPDATE_DATE := sysdate;
INSERT INTO AHL_RA_DEFINITION_DTLS
(
RA_DEFINITION_DTL_ID,
RA_DEFINITION_HDR_ID,
COUNTER_ID,
MTBF_VALUE,
OBJECT_VERSION_NUMBER,
SECURITY_GROUP_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
VALUES
(
l_ra_def_dtl_rec.RA_DEFINITION_DTL_ID,
l_ra_def_dtl_rec.RA_DEFINITION_HDR_ID,
l_ra_def_dtl_rec.COUNTER_ID,
l_ra_def_dtl_rec.MTBF_VALUE,
l_ra_def_dtl_rec.OBJECT_VERSION_NUMBER,
l_ra_def_dtl_rec.SECURITY_GROUP_ID,
l_ra_def_dtl_rec.ATTRIBUTE_CATEGORY,
l_ra_def_dtl_rec.ATTRIBUTE1,
l_ra_def_dtl_rec.ATTRIBUTE2,
l_ra_def_dtl_rec.ATTRIBUTE3,
l_ra_def_dtl_rec.ATTRIBUTE4,
l_ra_def_dtl_rec.ATTRIBUTE5,
l_ra_def_dtl_rec.ATTRIBUTE6,
l_ra_def_dtl_rec.ATTRIBUTE7,
l_ra_def_dtl_rec.ATTRIBUTE8,
l_ra_def_dtl_rec.ATTRIBUTE9,
l_ra_def_dtl_rec.ATTRIBUTE10,
l_ra_def_dtl_rec.ATTRIBUTE11,
l_ra_def_dtl_rec.ATTRIBUTE12,
l_ra_def_dtl_rec.ATTRIBUTE13,
l_ra_def_dtl_rec.ATTRIBUTE14,
l_ra_def_dtl_rec.ATTRIBUTE15,
l_ra_def_dtl_rec.CREATION_DATE,
l_ra_def_dtl_rec.CREATED_BY,
l_ra_def_dtl_rec.LAST_UPDATE_DATE,
l_ra_def_dtl_rec.LAST_UPDATED_BY,
l_ra_def_dtl_rec.LAST_UPDATE_LOGIN
);
UPDATE AHL_RA_DEFINITION_HDR
SET OBJECT_VERSION_NUMBER = l_ra_def_hdr_rec.OBJECT_VERSION_NUMBER
WHERE RA_DEFINITION_HDR_ID = l_ra_def_dtl_rec.RA_DEFINITION_HDR_ID;
PROCEDURE UPDATE_MTBF_DATA (
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_module_type IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_x_reliability_data_rec IN OUT NOCOPY AHL_RA_SETUPS_PVT.RA_DEFINITION_HDR_REC_TYPE,
p_x_mtbf_data_rec IN OUT NOCOPY AHL_RA_SETUPS_PVT.RA_DEFINITION_DTLS_REC_TYPE) IS
CURSOR get_mtbf_data_cur(p_ra_definition_dtl_id IN NUMBER) IS
SELECT RA_DEFINITION_DTL_ID,
RA_DEFINITION_HDR_ID,
COUNTER_ID,
MTBF_VALUE,
OBJECT_VERSION_NUMBER
FROM AHL_RA_DEFINITION_DTLS
WHERE RA_DEFINITION_DTL_ID = p_ra_definition_dtl_id
FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_MTBF_DATA';
SAVEPOINT UPDATE_MTBF_DATA_SP;
fnd_log.string(fnd_log.level_statement,l_full_name,'DS -- PVT -- UPDATE_MTBF_DATA -------BEGIN-----------');
IF ((l_ra_def_dtl_rec.OPERATION_FLAG IS NULL) OR (l_ra_def_dtl_rec.OPERATION_FLAG <> G_DML_UPDATE)) THEN
IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed --');
FND_MESSAGE.Set_Name('AHL','AHL_COM_RECORD_DELETED');
fnd_log.string(fnd_log.level_statement, l_full_name, 'default_unchanged_attributes for update operation. Module type is '||p_module_type);
SELECT templates.name
INTO l_cou_name
FROM cs_csi_counter_groups cg,
csi_counter_template_vl templates,
csi_ctr_item_associations csia,
ahl_ra_definition_hdr rdh
WHERE templates.counter_id = l_ra_def_dtl_rec.COUNTER_ID
AND cg.template_flag = 'Y'
AND templates.group_id = cg.counter_group_id
AND csia.group_id = cg.counter_group_id
AND csia.inventory_item_id = rdh.inventory_item_id
--Added the following on 13-sep-2005 after the feedback from csi team
AND nvl(csia.associated_to_group,'N') = 'Y'
AND csia.counter_id is null
--mpothuku end
AND rdh.RA_DEFINITION_HDR_ID = l_ra_def_dtl_rec.RA_DEFINITION_HDR_ID
AND trunc(sysdate) < trunc(nvl(templates.end_date_active,sysdate+1))
AND trunc(sysdate) < trunc(nvl(csia.end_date_active,sysdate+1));
SELECT OBJECT_VERSION_NUMBER
INTO l_m_obj_version_num
FROM AHL_RA_DEFINITION_HDR
WHERE RA_DEFINITION_HDR_ID = l_ra_def_dtl_rec.RA_DEFINITION_HDR_ID
FOR UPDATE OF object_version_number NOWAIT;
FND_MESSAGE.Set_Name('AHL','AHL_COM_RECORD_DELETED');
l_ra_def_dtl_rec.LAST_UPDATED_BY := fnd_global.USER_ID;
l_ra_def_dtl_rec.LAST_UPDATE_LOGIN := fnd_global.LOGIN_ID;
l_ra_def_dtl_rec.LAST_UPDATE_DATE := sysdate;
UPDATE AHL_RA_DEFINITION_DTLS
SET
RA_DEFINITION_HDR_ID = l_ra_def_dtl_rec.RA_DEFINITION_HDR_ID,
COUNTER_ID = l_ra_def_dtl_rec.COUNTER_ID,
MTBF_VALUE = l_ra_def_dtl_rec.MTBF_VALUE,
OBJECT_VERSION_NUMBER = l_ra_def_dtl_rec.OBJECT_VERSION_NUMBER,
SECURITY_GROUP_ID = l_ra_def_dtl_rec.SECURITY_GROUP_ID,
ATTRIBUTE_CATEGORY = l_ra_def_dtl_rec.ATTRIBUTE_CATEGORY,
ATTRIBUTE1 = l_ra_def_dtl_rec.ATTRIBUTE1,
ATTRIBUTE2 = l_ra_def_dtl_rec.ATTRIBUTE2,
ATTRIBUTE3 = l_ra_def_dtl_rec.ATTRIBUTE3,
ATTRIBUTE4 = l_ra_def_dtl_rec.ATTRIBUTE4,
ATTRIBUTE5 = l_ra_def_dtl_rec.ATTRIBUTE5,
ATTRIBUTE6 = l_ra_def_dtl_rec.ATTRIBUTE6,
ATTRIBUTE7 = l_ra_def_dtl_rec.ATTRIBUTE7,
ATTRIBUTE8 = l_ra_def_dtl_rec.ATTRIBUTE8,
ATTRIBUTE9 = l_ra_def_dtl_rec.ATTRIBUTE9,
ATTRIBUTE10 = l_ra_def_dtl_rec.ATTRIBUTE10,
ATTRIBUTE11 = l_ra_def_dtl_rec.ATTRIBUTE11,
ATTRIBUTE12 = l_ra_def_dtl_rec.ATTRIBUTE12,
ATTRIBUTE13 = l_ra_def_dtl_rec.ATTRIBUTE13,
ATTRIBUTE14 = l_ra_def_dtl_rec.ATTRIBUTE14,
ATTRIBUTE15 = l_ra_def_dtl_rec.ATTRIBUTE15,
CREATION_DATE = l_ra_def_dtl_rec.CREATION_DATE,
CREATED_BY = l_ra_def_dtl_rec.CREATED_BY,
LAST_UPDATE_DATE = l_ra_def_dtl_rec.LAST_UPDATE_DATE,
LAST_UPDATED_BY = l_ra_def_dtl_rec.LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = l_ra_def_dtl_rec.LAST_UPDATE_LOGIN
WHERE
RA_DEFINITION_DTL_ID = l_ra_def_dtl_rec.RA_DEFINITION_DTL_ID;
UPDATE AHL_RA_DEFINITION_HDR
SET OBJECT_VERSION_NUMBER = l_ra_def_hdr_rec.OBJECT_VERSION_NUMBER
WHERE RA_DEFINITION_HDR_ID = l_ra_def_dtl_rec.RA_DEFINITION_HDR_ID;
fnd_log.string(fnd_log.level_statement,l_full_name,'DS -- PVT -- UPDATE_MTBF_DATA -------END-----------');
Rollback to UPDATE_MTBF_DATA_SP;
Rollback to UPDATE_MTBF_DATA_SP;
Rollback to UPDATE_MTBF_DATA_SP;
p_procedure_name => 'UPDATE_MTBF_DATA',
p_error_text => SUBSTR(SQLERRM,1,240));
END UPDATE_MTBF_DATA;
PROCEDURE DELETE_MTBF_DATA (
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_module_type IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_x_reliability_data_rec IN OUT NOCOPY AHL_RA_SETUPS_PVT.RA_DEFINITION_HDR_REC_TYPE,
p_mtbf_data_rec IN AHL_RA_SETUPS_PVT.RA_DEFINITION_DTLS_REC_TYPE) IS
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_MTBF_DATA';
SAVEPOINT DELETE_MTBF_DATA_SP;
fnd_log.string(fnd_log.level_statement,l_full_name,'DS -- PVT -- DELETE_MTBF_DATA -------BEGIN-----------');
IF ((l_ra_def_dtl_rec.OPERATION_FLAG IS NULL) OR (l_ra_def_dtl_rec.OPERATION_FLAG <> G_DML_DELETE)) THEN
IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed --');
SELECT OBJECT_VERSION_NUMBER
INTO l_obj_version_num
FROM AHL_RA_DEFINITION_DTLS
WHERE RA_DEFINITION_DTL_ID = l_ra_def_dtl_rec.RA_DEFINITION_DTL_ID
FOR UPDATE OF object_version_number NOWAIT;
FND_MESSAGE.Set_Name('AHL','AHL_COM_RECORD_DELETED');
SELECT OBJECT_VERSION_NUMBER
INTO l_m_obj_version_num
FROM AHL_RA_DEFINITION_HDR
WHERE RA_DEFINITION_HDR_ID = l_ra_def_dtl_rec.RA_DEFINITION_HDR_ID
FOR UPDATE OF object_version_number nowait;
FND_MESSAGE.Set_Name('AHL','AHL_COM_RECORD_DELETED');
DELETE AHL_RA_DEFINITION_DTLS
WHERE RA_DEFINITION_DTL_ID = l_ra_def_dtl_rec.RA_DEFINITION_DTL_ID;
UPDATE AHL_RA_DEFINITION_HDR
SET OBJECT_VERSION_NUMBER = l_ra_def_hdr_rec.OBJECT_VERSION_NUMBER
WHERE RA_DEFINITION_HDR_ID = l_ra_def_dtl_rec.RA_DEFINITION_HDR_ID;
fnd_log.string(fnd_log.level_statement,l_full_name,'DS -- PVT -- DELETE_MTBF_DATA -------END-----------');
Rollback to DELETE_MTBF_DATA_SP;
Rollback to DELETE_MTBF_DATA_SP;
Rollback to DELETE_MTBF_DATA_SP;
p_procedure_name => 'DELETE_MTBF_DATA',
p_error_text => SUBSTR(SQLERRM,1,240));
END DELETE_MTBF_DATA;
SELECT templates.name,
templates.uom_code
INTO l_new_cou_name,
l_new_cou_uom
FROM cs_csi_counter_groups cg,
csi_counter_template_vl templates
WHERE templates.counter_id = l_counter_assoc_rec.SINCE_NEW_COUNTER_ID
AND cg.template_flag = 'Y'
AND templates.group_id = cg.counter_group_id
AND trunc(sysdate) < trunc(nvl(templates.end_date_active,sysdate+1));
SELECT templates.name,
templates.uom_code
INTO l_overhaul_cou_name,
l_overhaul_cou_uom
FROM cs_csi_counter_groups cg,
csi_counter_template_vl templates
WHERE templates.counter_id = l_counter_assoc_rec.SINCE_OVERHAUL_COUNTER_ID
AND cg.template_flag = 'Y'
AND templates.group_id = cg.counter_group_id
AND trunc(sysdate) < trunc(nvl(templates.end_date_active,sysdate+1));
SELECT 'Y'
INTO l_dummy
FROM dual
WHERE EXISTS(SELECT 'X'
FROM AHL_RA_CTR_ASSOCIATIONS
WHERE since_overhaul_counter_id = l_counter_assoc_rec.SINCE_NEW_COUNTER_ID);
SELECT 'Y'
INTO l_dummy
FROM dual
WHERE EXISTS(SELECT 'X'
FROM AHL_RA_CTR_ASSOCIATIONS
WHERE since_new_counter_id = l_counter_assoc_rec.SINCE_OVERHAUL_COUNTER_ID);
SELECT 'Y'
INTO l_dummy
FROM dual
WHERE EXISTS(SELECT 'X'
FROM AHL_RA_CTR_ASSOCIATIONS counters
WHERE SINCE_NEW_COUNTER_ID = l_counter_assoc_rec.SINCE_NEW_COUNTER_ID
AND nvl(SINCE_OVERHAUL_COUNTER_ID,-1) = nvl(l_counter_assoc_rec.SINCE_OVERHAUL_COUNTER_ID,-1));
SELECT AHL_RA_CTR_ASSOCIATIONS_S.NEXTVAL into l_counter_assoc_rec.RA_COUNTER_ASSOCIATION_ID from dual;
l_counter_assoc_rec.LAST_UPDATED_BY := fnd_global.USER_ID;
l_counter_assoc_rec.LAST_UPDATE_LOGIN := fnd_global.LOGIN_ID;
l_counter_assoc_rec.LAST_UPDATE_DATE := sysdate;
INSERT INTO AHL_RA_CTR_ASSOCIATIONS
(
RA_COUNTER_ASSOCIATION_ID,
SINCE_NEW_COUNTER_ID,
SINCE_OVERHAUL_COUNTER_ID,
DESCRIPTION,
OBJECT_VERSION_NUMBER,
SECURITY_GROUP_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15
)
VALUES
(
l_counter_assoc_rec.RA_COUNTER_ASSOCIATION_ID,
l_counter_assoc_rec.SINCE_NEW_COUNTER_ID,
l_counter_assoc_rec.SINCE_OVERHAUL_COUNTER_ID,
l_counter_assoc_rec.DESCRIPTION,
l_counter_assoc_rec.OBJECT_VERSION_NUMBER,
l_counter_assoc_rec.SECURITY_GROUP_ID,
l_counter_assoc_rec.CREATION_DATE,
l_counter_assoc_rec.CREATED_BY,
l_counter_assoc_rec.LAST_UPDATE_DATE,
l_counter_assoc_rec.LAST_UPDATED_BY,
l_counter_assoc_rec.LAST_UPDATE_LOGIN,
l_counter_assoc_rec.ATTRIBUTE_CATEGORY,
l_counter_assoc_rec.ATTRIBUTE1,
l_counter_assoc_rec.ATTRIBUTE2,
l_counter_assoc_rec.ATTRIBUTE3,
l_counter_assoc_rec.ATTRIBUTE4,
l_counter_assoc_rec.ATTRIBUTE5,
l_counter_assoc_rec.ATTRIBUTE6,
l_counter_assoc_rec.ATTRIBUTE7,
l_counter_assoc_rec.ATTRIBUTE8,
l_counter_assoc_rec.ATTRIBUTE9,
l_counter_assoc_rec.ATTRIBUTE10,
l_counter_assoc_rec.ATTRIBUTE11,
l_counter_assoc_rec.ATTRIBUTE12,
l_counter_assoc_rec.ATTRIBUTE13,
l_counter_assoc_rec.ATTRIBUTE14,
l_counter_assoc_rec.ATTRIBUTE15
);
PROCEDURE DELETE_COUNTER_ASSOC (
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_module_type IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_counter_assoc_rec IN AHL_RA_SETUPS_PVT.RA_COUNTER_ASSOC_REC_TYPE) IS
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_COUNTER_ASSOC';
SAVEPOINT DELETE_COUNTER_ASSOC_SP;
fnd_log.string(fnd_log.level_statement,l_full_name,'DS -- PVT -- DELETE_COUNTER_ASSOC -------BEGIN-----------');
IF ((p_counter_assoc_rec.OPERATION_FLAG IS NULL) OR (p_counter_assoc_rec.OPERATION_FLAG <> G_DML_DELETE)) THEN
IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed --');
SELECT OBJECT_VERSION_NUMBER
INTO l_obj_version_num
FROM AHL_RA_CTR_ASSOCIATIONS
WHERE RA_COUNTER_ASSOCIATION_ID = p_counter_assoc_rec.RA_COUNTER_ASSOCIATION_ID
FOR UPDATE OF object_version_number NOWAIT;
FND_MESSAGE.Set_Name('AHL','AHL_COM_RECORD_DELETED');
DELETE AHL_RA_CTR_ASSOCIATIONS
WHERE RA_COUNTER_ASSOCIATION_ID = p_counter_assoc_rec.RA_COUNTER_ASSOCIATION_ID;
fnd_log.string(fnd_log.level_statement,l_full_name,'DS -- PVT -- DELETE_COUNTER_ASSOC -------END-----------');
Rollback to DELETE_COUNTER_ASSOC_SP;
Rollback to DELETE_COUNTER_ASSOC_SP;
Rollback to DELETE_COUNTER_ASSOC_SP;
p_procedure_name => 'DELETE_COUNTER_ASSOC',
p_error_text => SUBSTR(SQLERRM,1,240));
END DELETE_COUNTER_ASSOC;
SELECT 'Y'
INTO l_dummy
FROM DUAL
WHERE EXISTS(SELECT 'X'
FROM MTL_PARAMETERS MP
WHERE MP.ORGANIZATION_ID = l_fct_assoc_rec.ORGANIZATION_ID
AND MP.EAM_ENABLED_FLAG='Y');
SELECT 'Y'
INTO l_dummy
FROM DUAL
WHERE EXISTS(SELECT 'X'
FROM mrp_forecast_designators_v MRP
WHERE MRP.FORECAST_DESIGNATOR = l_fct_assoc_rec.FORECAST_DESIGNATOR
AND MRP.ORGANIZATION_ID = l_fct_assoc_rec.ORGANIZATION_ID
AND MRP.FORECAST_SET IS NOT NULL);
SELECT 'Y'
INTO l_dummy
FROM DUAL
WHERE EXISTS (SELECT 'X'
FROM AHL_RA_FCT_ASSOCIATIONS
WHERE ORGANIZATION_ID = l_fct_assoc_rec.ORGANIZATION_ID
-- Bug 4998568 :: Probability Values Should not overlap irrespective of the Fct Designator
-- AND FORECAST_DESIGNATOR = l_fct_assoc_rec.FORECAST_DESIGNATOR
AND ASSOCIATION_TYPE_CODE = l_fct_assoc_rec.ASSOCIATION_TYPE_CODE
AND ((PROBABILITY_FROM = l_fct_assoc_rec.PROBABILITY_FROM) OR
(PROBABILITY_FROM > l_fct_assoc_rec.PROBABILITY_FROM AND PROBABILITY_FROM < l_fct_assoc_rec.PROBABILITY_TO) OR
(PROBABILITY_FROM <= l_fct_assoc_rec.PROBABILITY_FROM AND PROBABILITY_TO >= l_fct_assoc_rec.PROBABILITY_TO) OR
(PROBABILITY_TO > l_fct_assoc_rec.PROBABILITY_FROM AND PROBABILITY_TO < l_fct_assoc_rec.PROBABILITY_TO) OR
(PROBABILITY_FROM = 100 AND l_fct_assoc_rec.PROBABILITY_TO = 100) OR -- if <> to 100 is defined .. then 100 to 100 is not allowed
(PROBABILITY_TO = 100 AND l_fct_assoc_rec.PROBABILITY_FROM = 100))); -- if 100 to 100 is defined .. then <> to 100 is not allowed
SELECT 'Y'
INTO l_dummy
FROM DUAL
WHERE EXISTS (SELECT 'X'
FROM AHL_RA_FCT_ASSOCIATIONS
WHERE ORGANIZATION_ID = l_fct_assoc_rec.ORGANIZATION_ID
AND ASSOCIATION_TYPE_CODE = l_fct_assoc_rec.ASSOCIATION_TYPE_CODE);
SELECT AHL_RA_FCT_ASSOCIATIONS_S.NEXTVAL INTO l_fct_assoc_rec.RA_FCT_ASSOCIATION_ID FROM DUAL;
l_fct_assoc_rec.LAST_UPDATED_BY := fnd_global.USER_ID;
l_fct_assoc_rec.LAST_UPDATE_LOGIN := fnd_global.LOGIN_ID;
l_fct_assoc_rec.LAST_UPDATE_DATE := sysdate;
INSERT INTO AHL_RA_FCT_ASSOCIATIONS(RA_FCT_ASSOCIATION_ID,FORECAST_DESIGNATOR,ASSOCIATION_TYPE_CODE,ORGANIZATION_ID,PROBABILITY_FROM, PROBABILITY_TO,
OBJECT_VERSION_NUMBER,SECURITY_GROUP_ID,CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,ATTRIBUTE_CATEGORY,ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3,ATTRIBUTE4,ATTRIBUTE5,ATTRIBUTE6,ATTRIBUTE7,
ATTRIBUTE8,ATTRIBUTE9,ATTRIBUTE10,ATTRIBUTE11,ATTRIBUTE12,ATTRIBUTE13,ATTRIBUTE14,ATTRIBUTE15)
VALUES(
l_fct_assoc_rec.RA_FCT_ASSOCIATION_ID -- RA_FCT_ASSOCIATION_ID
,l_fct_assoc_rec.FORECAST_DESIGNATOR -- FORECAST_DESIGNATOR
,l_fct_assoc_rec.ASSOCIATION_TYPE_CODE -- ASSOCIATION_TYPE_CODE
,l_fct_assoc_rec.ORGANIZATION_ID -- ORGANIZATION_ID
,l_fct_assoc_rec.PROBABILITY_FROM -- PROBABILITY_FROM
,l_fct_assoc_rec.PROBABILITY_TO -- PROBABILITY_TO
,l_fct_assoc_rec.OBJECT_VERSION_NUMBER -- OBJECT_VERSION_NUMBER
,l_fct_assoc_rec.SECURITY_GROUP_ID -- SECURITY_GROUP_ID
,l_fct_assoc_rec.CREATION_DATE -- CREATION_DATE
,l_fct_assoc_rec.CREATED_BY -- CREATED_BY
,l_fct_assoc_rec.LAST_UPDATE_DATE -- LAST_UPDATE_DATE
,l_fct_assoc_rec.LAST_UPDATED_BY -- LAST_UPDATED_BY
,l_fct_assoc_rec.LAST_UPDATE_LOGIN -- LAST_UPDATE_LOGIN
,l_fct_assoc_rec.ATTRIBUTE_CATEGORY -- ATTRIBUTE_CATEGORY
,l_fct_assoc_rec.ATTRIBUTE1 -- ATTRIBUTE1
,l_fct_assoc_rec.ATTRIBUTE2 -- ATTRIBUTE2
,l_fct_assoc_rec.ATTRIBUTE3 -- ATTRIBUTE3
,l_fct_assoc_rec.ATTRIBUTE4 -- ATTRIBUTE4
,l_fct_assoc_rec.ATTRIBUTE5 -- ATTRIBUTE5
,l_fct_assoc_rec.ATTRIBUTE6 -- ATTRIBUTE6
,l_fct_assoc_rec.ATTRIBUTE7 -- ATTRIBUTE7
,l_fct_assoc_rec.ATTRIBUTE8 -- ATTRIBUTE8
,l_fct_assoc_rec.ATTRIBUTE9 -- ATTRIBUTE9
,l_fct_assoc_rec.ATTRIBUTE10 -- ATTRIBUTE10
,l_fct_assoc_rec.ATTRIBUTE11 -- ATTRIBUTE11
,l_fct_assoc_rec.ATTRIBUTE12 -- ATTRIBUTE12
,l_fct_assoc_rec.ATTRIBUTE13 -- ATTRIBUTE13
,l_fct_assoc_rec.ATTRIBUTE14 -- ATTRIBUTE14
,l_fct_assoc_rec.ATTRIBUTE15 -- ATTRIBUTE15
);
PROCEDURE UPDATE_FCT_ASSOC_DATA (
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_module_type IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_x_fct_assoc_rec IN OUT NOCOPY AHL_RA_SETUPS_PVT.RA_FCT_ASSOC_REC_TYPE) IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_FCT_ASSOC_DATA';
SAVEPOINT UPDATE_FCT_ASSOC_DATA_SP;
fnd_log.string(fnd_log.level_statement,l_full_name,'DS -- PVT -- UPDATE_FCT_ASSOC_DATA -------BEGIN-----------');
((l_fct_assoc_rec.OPERATION_FLAG IS NULL) OR (l_fct_assoc_rec.OPERATION_FLAG <> G_DML_UPDATE)) OR
(l_fct_assoc_rec.OBJECT_VERSION_NUMBER IS NULL OR l_fct_assoc_rec.OBJECT_VERSION_NUMBER = FND_API.G_MISS_NUM))THEN
IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed -- RA_FCT_ASSOCIATION_ID :' || l_fct_assoc_rec.RA_FCT_ASSOCIATION_ID);
SELECT OBJECT_VERSION_NUMBER
,DECODE(l_fct_assoc_rec.FORECAST_DESIGNATOR,FND_API.G_MISS_CHAR,NULL
,NULL,FORECAST_DESIGNATOR
,l_fct_assoc_rec.FORECAST_DESIGNATOR)
,DECODE(l_fct_assoc_rec.ASSOCIATION_TYPE_CODE,FND_API.G_MISS_CHAR,NULL
,NULL,ASSOCIATION_TYPE_CODE
,l_fct_assoc_rec.ASSOCIATION_TYPE_CODE)
,DECODE(l_fct_assoc_rec.ORGANIZATION_ID,FND_API.G_MISS_NUM,NULL
,NULL,ORGANIZATION_ID
,l_fct_assoc_rec.ORGANIZATION_ID)
,DECODE(l_fct_assoc_rec.PROBABILITY_FROM,FND_API.G_MISS_NUM,NULL
,NULL,PROBABILITY_FROM
,l_fct_assoc_rec.PROBABILITY_FROM)
,DECODE(l_fct_assoc_rec.PROBABILITY_TO,FND_API.G_MISS_NUM,NULL
,NULL,PROBABILITY_TO
,l_fct_assoc_rec.PROBABILITY_TO)
INTO l_obj_version_num
,l_fct_assoc_rec.FORECAST_DESIGNATOR
,l_fct_assoc_rec.ASSOCIATION_TYPE_CODE
,l_fct_assoc_rec.ORGANIZATION_ID
,l_fct_assoc_rec.PROBABILITY_FROM
,l_fct_assoc_rec.PROBABILITY_TO
FROM AHL_RA_FCT_ASSOCIATIONS
WHERE RA_FCT_ASSOCIATION_ID = l_fct_assoc_rec.RA_FCT_ASSOCIATION_ID
FOR UPDATE OF object_version_number NOWAIT;
FND_MESSAGE.Set_Name('AHL','AHL_COM_RECORD_DELETED');
((l_fct_assoc_rec.OPERATION_FLAG IS NULL) OR (l_fct_assoc_rec.OPERATION_FLAG <> G_DML_UPDATE))) THEN
IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed --');
SELECT 'Y'
INTO l_dummy
FROM DUAL
WHERE EXISTS(SELECT 'X'
FROM MTL_PARAMETERS MP
WHERE MP.ORGANIZATION_ID = l_fct_assoc_rec.ORGANIZATION_ID
AND MP.EAM_ENABLED_FLAG='Y');
SELECT 'Y'
INTO l_dummy
FROM DUAL
WHERE EXISTS(SELECT 'X'
FROM mrp_forecast_designators_v MRP
WHERE MRP.FORECAST_DESIGNATOR = l_fct_assoc_rec.FORECAST_DESIGNATOR
AND MRP.ORGANIZATION_ID = l_fct_assoc_rec.ORGANIZATION_ID
AND MRP.FORECAST_SET IS NOT NULL);
SELECT 'Y'
INTO l_dummy
FROM DUAL
WHERE EXISTS (SELECT 'X'
FROM AHL_RA_FCT_ASSOCIATIONS
WHERE ORGANIZATION_ID = l_fct_assoc_rec.ORGANIZATION_ID
-- Bug 4998568 :: Probability Values Should not overlap irrespective of the Fct Designator
-- AND FORECAST_DESIGNATOR = l_fct_assoc_rec.FORECAST_DESIGNATOR
AND ASSOCIATION_TYPE_CODE = l_fct_assoc_rec.ASSOCIATION_TYPE_CODE
AND ((PROBABILITY_FROM = l_fct_assoc_rec.PROBABILITY_FROM) OR
(PROBABILITY_FROM > l_fct_assoc_rec.PROBABILITY_FROM AND PROBABILITY_FROM < l_fct_assoc_rec.PROBABILITY_TO) OR
(PROBABILITY_FROM <= l_fct_assoc_rec.PROBABILITY_FROM AND PROBABILITY_TO >= l_fct_assoc_rec.PROBABILITY_TO) OR
(PROBABILITY_TO > l_fct_assoc_rec.PROBABILITY_FROM AND PROBABILITY_TO < l_fct_assoc_rec.PROBABILITY_TO) OR
(PROBABILITY_FROM = 100 AND l_fct_assoc_rec.PROBABILITY_TO = 100) OR-- if <> to 100 is defined .. then 100 to 100 is not allowed
(PROBABILITY_TO = 100 AND l_fct_assoc_rec.PROBABILITY_FROM = 100)) -- if 100 to 100 is defined .. then <> to 100 is not allowed
AND RA_FCT_ASSOCIATION_ID <> l_fct_assoc_rec.RA_FCT_ASSOCIATION_ID); -- Update of the ame record to bump OVN is allowed
SELECT 'Y'
INTO l_dummy
FROM DUAL
WHERE EXISTS (SELECT 'X'
FROM AHL_RA_FCT_ASSOCIATIONS
WHERE ORGANIZATION_ID = l_fct_assoc_rec.ORGANIZATION_ID
AND ASSOCIATION_TYPE_CODE = l_fct_assoc_rec.ASSOCIATION_TYPE_CODE
AND RA_FCT_ASSOCIATION_ID <> l_fct_assoc_rec.RA_FCT_ASSOCIATION_ID); -- Update of the ame record to bump OVN is allowed
l_fct_assoc_rec.LAST_UPDATED_BY := fnd_global.USER_ID;
l_fct_assoc_rec.LAST_UPDATE_LOGIN := fnd_global.LOGIN_ID;
l_fct_assoc_rec.LAST_UPDATE_DATE := sysdate;
UPDATE AHL_RA_FCT_ASSOCIATIONS
SET FORECAST_DESIGNATOR = l_fct_assoc_rec.FORECAST_DESIGNATOR -- FORECAST_DESIGNATOR
,ASSOCIATION_TYPE_CODE = l_fct_assoc_rec.ASSOCIATION_TYPE_CODE -- ASSOCIATION_TYPE_CODE
,ORGANIZATION_ID = l_fct_assoc_rec.ORGANIZATION_ID -- ORGANIZATION_ID
,PROBABILITY_FROM = l_fct_assoc_rec.PROBABILITY_FROM -- PROBABILITY_FROM
,PROBABILITY_TO = l_fct_assoc_rec.PROBABILITY_TO -- PROBABILITY_TO
,OBJECT_VERSION_NUMBER = l_fct_assoc_rec.OBJECT_VERSION_NUMBER -- OBJECT_VERSION_NUMBER
,SECURITY_GROUP_ID = l_fct_assoc_rec.SECURITY_GROUP_ID -- SECURITY_GROUP_ID
,LAST_UPDATE_DATE = l_fct_assoc_rec.LAST_UPDATE_DATE -- LAST_UPDATE_DATE
,LAST_UPDATED_BY = l_fct_assoc_rec.LAST_UPDATED_BY -- LAST_UPDATED_BY
,LAST_UPDATE_LOGIN = l_fct_assoc_rec.LAST_UPDATE_LOGIN -- LAST_UPDATE_LOGIN
,ATTRIBUTE_CATEGORY = l_fct_assoc_rec.ATTRIBUTE_CATEGORY -- ATTRIBUTE_CATEGORY
,ATTRIBUTE1 = l_fct_assoc_rec.ATTRIBUTE1 -- ATTRIBUTE1
,ATTRIBUTE2 = l_fct_assoc_rec.ATTRIBUTE2 -- ATTRIBUTE2
,ATTRIBUTE3 = l_fct_assoc_rec.ATTRIBUTE3 -- ATTRIBUTE3
,ATTRIBUTE4 = l_fct_assoc_rec.ATTRIBUTE4 -- ATTRIBUTE4
,ATTRIBUTE5 = l_fct_assoc_rec.ATTRIBUTE5 -- ATTRIBUTE5
,ATTRIBUTE6 = l_fct_assoc_rec.ATTRIBUTE6 -- ATTRIBUTE6
,ATTRIBUTE7 = l_fct_assoc_rec.ATTRIBUTE7 -- ATTRIBUTE7
,ATTRIBUTE8 = l_fct_assoc_rec.ATTRIBUTE8 -- ATTRIBUTE8
,ATTRIBUTE9 = l_fct_assoc_rec.ATTRIBUTE9 -- ATTRIBUTE9
,ATTRIBUTE10 = l_fct_assoc_rec.ATTRIBUTE10 -- ATTRIBUTE10
,ATTRIBUTE11 = l_fct_assoc_rec.ATTRIBUTE11 -- ATTRIBUTE11
,ATTRIBUTE12 = l_fct_assoc_rec.ATTRIBUTE12 -- ATTRIBUTE12
,ATTRIBUTE13 = l_fct_assoc_rec.ATTRIBUTE13 -- ATTRIBUTE13
,ATTRIBUTE14 = l_fct_assoc_rec.ATTRIBUTE14 -- ATTRIBUTE14
,ATTRIBUTE15 = l_fct_assoc_rec.ATTRIBUTE15 -- ATTRIBUTE15
WHERE RA_FCT_ASSOCIATION_ID = l_fct_assoc_rec.RA_FCT_ASSOCIATION_ID;
fnd_log.string(fnd_log.level_statement,l_full_name,'DS -- PVT -- UPDATE_FCT_ASSOC_DATA -------END-----------');
Rollback to UPDATE_FCT_ASSOC_DATA_SP;
Rollback to UPDATE_FCT_ASSOC_DATA_SP;
Rollback to UPDATE_FCT_ASSOC_DATA_SP;
p_procedure_name => 'UPDATE_FCT_ASSOC_DATA',
p_error_text => SUBSTR(SQLERRM,1,240));
END UPDATE_FCT_ASSOC_DATA;
PROCEDURE DELETE_FCT_ASSOC_DATA (
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_module_type IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_fct_assoc_rec IN AHL_RA_SETUPS_PVT.RA_FCT_ASSOC_REC_TYPE) IS
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_FCT_ASSOC_DATA';
SAVEPOINT DELETE_FCT_ASSOC_DATA_SP;
fnd_log.string(fnd_log.level_statement,l_full_name,'DS -- PVT -- DELETE_FCT_ASSOC_DATA -------BEGIN-----------');
((p_fct_assoc_rec.OPERATION_FLAG IS NULL) OR (p_fct_assoc_rec.OPERATION_FLAG <> G_DML_DELETE)) OR
(p_fct_assoc_rec.OBJECT_VERSION_NUMBER IS NULL))THEN
IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed -- RA_FCT_ASSOCIATION_ID :' || p_fct_assoc_rec.RA_FCT_ASSOCIATION_ID);
SELECT OBJECT_VERSION_NUMBER
INTO l_obj_version_num
FROM AHL_RA_FCT_ASSOCIATIONS
WHERE RA_FCT_ASSOCIATION_ID = p_fct_assoc_rec.RA_FCT_ASSOCIATION_ID
FOR UPDATE OF object_version_number NOWAIT;
FND_MESSAGE.Set_Name('AHL','AHL_COM_RECORD_DELETED');
DELETE AHL_RA_FCT_ASSOCIATIONS
WHERE RA_FCT_ASSOCIATION_ID = p_fct_assoc_rec.RA_FCT_ASSOCIATION_ID;
fnd_log.string(fnd_log.level_statement,l_full_name,'DS -- PVT -- DELETE_FCT_ASSOC_DATA -------END-----------');
ROLLBACK TO DELETE_FCT_ASSOC_DATA_SP;
ROLLBACK TO DELETE_FCT_ASSOC_DATA_SP;
ROLLBACK TO DELETE_FCT_ASSOC_DATA_SP;
p_procedure_name => 'DELETE_FCT_ASSOC_DATA',
p_error_text => SUBSTR(SQLERRM,1,240));
END DELETE_FCT_ASSOC_DATA;