[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
AND l_eam_failure_entry_record.transaction_type <> Eam_Process_Failure_Entry_PUB.G_FE_UPDATE
)
THEN
/* Invalid Transaction Type */
x_reason_failed := 'EAM_FA_INVALID_TXN_TYPE';
SELECT COUNT(1)
INTO l_valid_object_type
FROM MFG_LOOKUPS
WHERE lookup_type = 'EAM_ASSET_FAIL_SRC_TYPE'
AND lookup_code = l_eam_failure_entry_record.source_type;
SELECT COUNT(1)
INTO l_valid_object_type
FROM MFG_LOOKUPS
WHERE lookup_type = 'WIP_MAINTENANCE_OBJECT_TYPE'
AND lookup_code = l_eam_failure_entry_record.object_type;
SELECT COUNT(1)
INTO l_valid_object_id
FROM CSI_ITEM_INSTANCES
WHERE instance_id = l_eam_failure_entry_record.object_id;
SELECT COUNT(1)
INTO l_valid_source_id
FROM wip_discrete_jobs wdj, wip_entities we
WHERE wdj.wip_entity_id = we.wip_entity_id
AND wdj.wip_entity_id = l_eam_failure_entry_record.source_id
AND we.entity_type IN (6,7);
SELECT COUNT(1)
INTO l_failure_exists
FROM eam_asset_failures
WHERE source_type = l_eam_failure_entry_record.source_type
AND source_id = l_eam_failure_entry_record.source_id;
ELSIF l_eam_failure_entry_record.transaction_type = Eam_Process_Failure_Entry_PUB.G_FE_UPDATE THEN
/* Failure Date is Already Validated on top of the API for both Create/ Update */
SELECT COUNT(1)
INTO l_failure_exists
FROM eam_asset_failures
WHERE failure_id = l_eam_failure_entry_record.failure_id;
IF l_eam_failure_codes_record.transaction_type IN (Eam_Process_Failure_Entry_PUB.G_FE_CREATE, Eam_Process_Failure_Entry_PUB.G_FE_UPDATE) THEN
SELECT count(1)
INTO l_valid_header_record
FROM eam_asset_failures eaf
WHERE eaf.failure_id = l_eam_failure_codes_record.failure_id;
IF l_eam_failure_codes_record.transaction_type = Eam_Process_Failure_Entry_PUB.G_FE_UPDATE THEN
BEGIN
SELECT failure_code, cause_code, resolution_code
INTO l_old_failure_code, l_old_cause_code, l_old_resolution_code
FROM eam_asset_failure_codes
WHERE failure_entry_id = l_eam_failure_codes_record.failure_entry_id;
SELECT count(1)
INTO l_failure_codes_exists
FROM eam_asset_failure_codes eafc
WHERE eafc.failure_id = l_eam_failure_codes_record.failure_id
AND nvl(eafc.failure_code,'NULL VALUE') = nvl(l_eam_failure_codes_record.failure_code,'NULL VALUE')
AND nvl(eafc.cause_code,'NULL VALUE') = nvl(l_eam_failure_codes_record.cause_code,'NULL VALUE')
AND nvl(eafc.resolution_code,'NULL VALUE') = nvl(l_eam_failure_codes_record.resolution_code,'NULL VALUE');
SELECT COUNT(1)
INTO l_failure_codes_exists
FROM eam_asset_failure_codes eafc
WHERE eafc.failure_id = l_eam_failure_codes_record.failure_id;
SELECT count(1)
INTO l_failure_codes_exists
FROM eam_asset_failure_codes eafc
WHERE eafc.failure_id = l_eam_failure_codes_record.failure_id
AND nvl(eafc.failure_code,'NULL VALUE') = nvl(l_eam_failure_codes_record.failure_code,'NULL VALUE')
AND nvl(eafc.cause_code,'NULL VALUE') = nvl(l_eam_failure_codes_record.cause_code,'NULL VALUE')
AND nvl(eafc.resolution_code,'NULL VALUE') = nvl(l_eam_failure_codes_record.resolution_code,'NULL VALUE');
SELECT count(1)
INTO l_failure_codes_exists
FROM eam_asset_failure_codes eafc
WHERE eafc.failure_id = l_eam_failure_codes_record.failure_id
*************/
END IF;
END IF; /* Create/ Update transaction type validation of failure codes */
SELECT inventory_item_id
INTO l_inventory_item_id
FROM CSI_ITEM_INSTANCES
WHERE instance_id = ( SELECT object_id FROM eam_asset_failures
WHERE failure_id = l_eam_failure_codes_record.failure_id
);
SELECT COUNT(1)
INTO l_valid_failure_code
FROM eam_failure_combinations EFC,
eam_failure_set_associations EFSA
WHERE EFC.failure_code = l_eam_failure_codes_record.failure_code
AND EFSA.inventory_item_id = l_inventory_item_id
AND EFSA.effective_end_date is null
AND EFSA.set_id = EFC.set_id
AND sysdate <= ( SELECT min(nvl(EFC2.EFFECTIVE_END_DATE, SYSDATE))
FROM EAM_FAILURE_COMBINATIONS EFC2
WHERE nvl(EFC2.EFFECTIVE_END_DATE, SYSDATE) >= sysdate
AND EFC2.SET_ID = EFSA.SET_ID
)
AND sysdate <= ( SELECT min(nvl(EFS.EFFECTIVE_END_DATE, SYSDATE))
FROM EAM_FAILURE_SETS EFS
WHERE nvl(EFS.EFFECTIVE_END_DATE, SYSDATE) >= sysdate
AND EFS.SET_ID = EFSA.SET_ID
);
SELECT count(1)
INTO l_valid_cause_code
FROM eam_failure_combinations EFC,
eam_failure_set_associations EFSA
WHERE EFC.cause_code = l_eam_failure_codes_record.cause_code
AND EFSA.inventory_item_id = l_inventory_item_id
AND EFSA.effective_end_date IS NULL
AND EFSA.set_id = efc.set_id
AND efc.failure_code = l_eam_failure_codes_record.failure_code
AND SYSDATE <= ( SELECT min(nvl(EFC2.effective_end_date, sysdate))
FROM eam_failure_combinations EFC2
WHERE nvl(EFC2.effective_end_date, sysdate) >= sysdate
AND EFC2.set_id = EFSA.set_id
)
AND SYSDATE <= ( SELECT min(nvl(EFS.EFFECTIVE_END_DATE, SYSDATE))
FROM EAM_FAILURE_SETS EFS
WHERE nvl(EFS.EFFECTIVE_END_DATE, SYSDATE) >= sysdate
AND EFS.SET_ID = EFSA.SET_ID
);
SELECT COUNT(1)
INTO l_valid_resolution_code
FROM eam_failure_combinations EFC,
eam_failure_set_associations EFSA
WHERE EFC.resolution_code = l_eam_failure_codes_record.resolution_code
AND EFSA.inventory_item_id = l_inventory_item_id
AND EFSA.EFFECTIVE_END_DATE IS NULL
AND EFSA.set_id = efc.set_id
AND EFC.failure_code = l_eam_failure_codes_record.failure_code
AND EFC.cause_code = l_eam_failure_codes_record.cause_code
AND SYSDATE <= ( SELECT min(nvl(EFC2.effective_end_date, sysdate))
FROM eam_failure_combinations EFC2
WHERE nvl(EFC2.effective_end_date, sysdate) >= sysdate
AND EFC2.set_id = EFSA.set_id
)
AND SYSDATE <= ( SELECT min(nvl(EFS.EFFECTIVE_END_DATE, SYSDATE))
FROM EAM_FAILURE_SETS EFS
WHERE nvl(EFS.EFFECTIVE_END_DATE, SYSDATE) >= sysdate
AND EFS.SET_ID = EFSA.SET_ID
);
ELSIF l_eam_failure_entry_record.transaction_type IN (Eam_Process_Failure_Entry_PUB.G_FE_CREATE, Eam_Process_Failure_Entry_PUB.G_FE_UPDATE) THEN
/* dbms_output.put_line('Befor Validate_failure_entry_record'); */
--2: Failure Information Update
FND_MESSAGE.SET_NAME ('EAM', 'EAM_FA_INVALID_TXN_TYPE');
/* dbms_output.put_line('Just Before Inserting the data into eam_asset_failures00'); */
/* dbms_output.put_line('Just Before Inserting the data into eam_asset_failures0'); */
SELECT eam_asset_failures_s.nextval
INTO l_failure_id
FROM DUAL;
/* dbms_output.put_line('Just Before Inserting the data into eam_asset_failures'); */
INSERT INTO eam_asset_failures
(
FAILURE_ID,
FAILURE_DATE,
SOURCE_TYPE,
SOURCE_ID,
OBJECT_TYPE,
OBJECT_ID,
MAINT_ORGANIZATION_ID,
CURRENT_ORGANIZATION_ID,
DEPARTMENT_ID,
AREA_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
VALUES
(
l_eam_failure_entry_record.failure_id,
l_eam_failure_entry_record.failure_date,
l_eam_failure_entry_record.source_type,
l_eam_failure_entry_record.source_id,
l_eam_failure_entry_record.object_type,
l_eam_failure_entry_record.object_id,
l_eam_failure_entry_record.maint_organization_id,
l_eam_failure_entry_record.current_organization_id,
l_eam_failure_entry_record.department_id,
l_eam_failure_entry_record.area_id,
FND_GLOBAL.user_id,
SYSDATE,
SYSDATE,
FND_GLOBAL.user_id,
FND_GLOBAL.user_id
);
/* dbms_output.put_line('Just After Inserting the data into eam_asset_failures'); */
ELSIF l_eam_failure_entry_record.transaction_type = Eam_Process_Failure_Entry_PUB.G_FE_UPDATE THEN
/* dbms_output.put_line('Just Before Updating the data into eam_asset_failures'); */
UPDATE eam_asset_failures
SET failure_date = l_eam_failure_entry_record.failure_date
/* ,source_typ = l_eam_failure_entry_record.source_typ
,source_id = l_eam_failure_entry_record.source_id
,object_typ = l_eam_failure_entry_record.object_typ
,object_id = l_eam_failure_entry_record.object_id
,maint_organization_id = l_eam_failure_entry_record.maint_organization_id
,current_organization_id = l_eam_failure_entry_record.current_organization_id */
,department_id = l_eam_failure_entry_record.department_id
,area_id = l_eam_failure_entry_record.area_id
,last_update_date = SYSDATE
,last_updated_by = FND_GLOBAL.user_id
,last_update_login= FND_GLOBAL.user_id
WHERE failure_id = l_eam_failure_entry_record.failure_id;
SELECT inventory_item_id
INTO l_inventory_item_id
FROM CSI_ITEM_INSTANCES
WHERE instance_id = ( SELECT object_id
FROM eam_asset_failures
WHERE failure_id = l_eam_failure_codes_record.failure_id
);
SELECT COMBINATION_ID
INTO l_combination_id
FROM eam_failure_combinations EFC,
eam_failure_set_associations EFSA
WHERE EFC.failure_code = l_eam_failure_codes_record.failure_code
AND EFC.cause_code = l_eam_failure_codes_record.cause_code
AND EFC.resolution_code = l_eam_failure_codes_record.resolution_code
AND EFSA.inventory_item_id = l_inventory_item_id
AND EFSA.effective_end_date is null
AND EFSA.set_id = EFC.set_id
AND SYSDATE <= ( SELECT min(nvl(EFC2.effective_end_date, sysdate))
FROM eam_failure_combinations EFC2
WHERE nvl(EFC2.effective_end_date, sysdate) >= sysdate
AND EFC2.set_id = EFSA.set_id
)
AND SYSDATE <= ( SELECT min(nvl(EFS.EFFECTIVE_END_DATE, SYSDATE))
FROM EAM_FAILURE_SETS EFS
WHERE nvl(EFS.EFFECTIVE_END_DATE, SYSDATE) >= sysdate
AND EFS.SET_ID = EFSA.SET_ID
);
/* dbms_output.put_line('Just Before Inserting the data into eam_asset_failure_codes'); */
SELECT eam_asset_failure_codes_s.nextval
INTO l_failure_entry_id
FROM DUAL;
INSERT INTO eam_asset_failure_codes
(
failure_id,
failure_entry_id,
combination_id,
failure_code,
cause_code,
resolution_code,
comments,
created_by,
creation_date,
last_update_date,
last_updated_by,
last_update_login
)
VALUES
(
l_eam_failure_codes_record.failure_id,
l_eam_failure_codes_record.failure_entry_id,
l_eam_failure_codes_record.combination_id,
l_eam_failure_codes_record.failure_code,
l_eam_failure_codes_record.cause_code,
l_eam_failure_codes_record.resolution_code,
l_eam_failure_codes_record.comments,
FND_GLOBAL.user_id,
SYSDATE,
SYSDATE,
FND_GLOBAL.user_id,
FND_GLOBAL.user_id
);
/* dbms_output.put_line('Just After Inserting the data into eam_asset_failure_codes'); */
ELSIF l_eam_failure_entry_record.transaction_type = Eam_Process_Failure_Entry_PUB.G_FE_UPDATE THEN
UPDATE eam_asset_failure_codes
SET failure_code = l_eam_failure_codes_record.failure_code
,cause_code = l_eam_failure_codes_record.cause_code
,resolution_code = l_eam_failure_codes_record.resolution_code
,combination_id = nvl( l_eam_failure_codes_record.combination_id, combination_id)
,comments = l_eam_failure_codes_record.comments
,last_update_date = SYSDATE
,last_updated_by = FND_GLOBAL.user_id
,last_update_login = FND_GLOBAL.user_id
WHERE failure_id = l_eam_failure_codes_record.failure_id
AND failure_entry_id = l_eam_failure_codes_record.failure_entry_id;