[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_program_update_date date;
l_last_update_date date := sysdate;
l_last_updated_by number := FND_GLOBAL.USER_ID;
l_last_update_login number := FND_GLOBAL.LOGIN_ID;
SELECT instance_number
FROM MTL_EAM_ASSET_NUM_INTERFACE meani
WHERE meani.interface_header_id = p_interface_header_id
;
SELECT DISTINCT meavi.application_id,
meavi.descriptive_flexfield_name,
meavi.attribute_category,
meavi.association_id
FROM MTL_EAM_ATTR_VAL_INTERFACE meavi
WHERE meavi.interface_header_id = p_interface_header_id
AND meavi.process_status = 'P';
SELECT meavi.application_column_name,
meavi.line_type,
meavi.attribute_varchar2_value,
meavi.attribute_number_value,
meavi.attribute_date_value
FROM MTL_EAM_ATTR_VAL_INTERFACE meavi
WHERE meavi.interface_header_id = p_interface_header_id
AND meavi.application_id = l_application_id
AND meavi.descriptive_flexfield_name = l_descr_flexfield_name
AND meavi.attribute_category = l_descr_flex_context_code
AND meavi.process_status = 'P';
SELECT fdfcu.application_column_name
FROM fnd_descr_flex_column_usages fdfcu
WHERE fdfcu.descriptive_flexfield_name = l_descr_flexfield_name
AND fdfcu.descriptive_flex_context_code = l_descr_flex_context_code
AND fdfcu.application_id = l_application_id
AND fdfcu.application_column_name
NOT IN
(SELECT meavi.application_column_name
FROM mtl_eam_attr_val_interface meavi
WHERE meavi.interface_header_id = p_interface_header_id
AND meavi.application_id = l_application_id
AND meavi.descriptive_flexfield_name = l_descr_flexfield_name
AND meavi.attribute_category = l_descr_flex_context_code
AND meavi.process_status = 'P');
UPDATE MTL_EAM_ATTR_VAL_INTERFACE meavi
SET meavi.error_number = 9999,
meavi.process_status = 'E',
meavi.error_message = 'Derived column APPLICATION_ID should be NULL'
WHERE meavi.interface_header_id = p_interface_header_id
AND meavi.process_status = 'P'
AND meavi.application_id IS NOT NULL;
UPDATE MTL_EAM_ATTR_VAL_INTERFACE meavi
SET meavi.error_number = 9999,
meavi.process_status = 'E',
meavi.error_message = 'Derived column DESCRITIVE_FLEXFIELD_NAME should be NULL'
WHERE meavi.interface_header_id = p_interface_header_id
AND meavi.process_status = 'P'
AND meavi.descriptive_flexfield_name IS NOT NULL;
UPDATE MTL_EAM_ATTR_VAL_INTERFACE meavi
SET meavi.error_number = 9999,
meavi.process_status = 'E',
meavi.error_message = 'Derived column APPLICATION_COLUMN_NAME should be NULL'
WHERE meavi.interface_header_id = p_interface_header_id
AND meavi.process_status = 'P'
AND meavi.application_column_name IS NOT NULL;
UPDATE MTL_EAM_ATTR_VAL_INTERFACE meavi
SET meavi.error_number = 9999,
meavi.process_status = 'E',
meavi.error_message = 'Derived column ASSOCIATION_ID should be NULL'
WHERE meavi.interface_header_id = p_interface_header_id
AND meavi.process_status = 'P'
AND meavi.association_id IS NOT NULL;
UPDATE MTL_EAM_ATTR_VAL_INTERFACE meavi
SET meavi.application_id = l_application_id,
meavi.descriptive_flexfield_name = l_descriptive_flexfield_name
WHERE meavi.interface_header_id = p_interface_header_id
AND meavi.process_status = 'P';
UPDATE MTL_EAM_ATTR_VAL_INTERFACE meavi
SET meavi.error_number = 9999,
meavi.process_status = 'E',
meavi.error_message = 'Decriptive Flexfield Details are Invalid'
WHERE meavi.interface_header_id = p_interface_header_id
AND meavi.process_status = 'P'
AND NOT EXISTS
(SELECT 'S'
FROM FND_DESCR_FLEX_COLUMN_USAGES mdfcu
WHERE meavi.application_id = mdfcu.application_id
AND meavi.descriptive_flexfield_name = mdfcu.descriptive_flexfield_name
AND meavi.attribute_category = mdfcu.descriptive_flex_context_code
AND meavi.end_user_column_name = mdfcu.end_user_column_name
AND meavi.application_id = l_application_id
AND meavi.descriptive_flexfield_name = l_descriptive_flexfield_name
AND meavi.interface_header_id = p_interface_header_id);
UPDATE MTL_EAM_ATTR_VAL_INTERFACE meavi
SET meavi.application_column_name = EAM_ASSET_SEARCH_PVT.GET_ATTRIBUTE_COLUMN_NAME
(meavi.application_id,
meavi.descriptive_flexfield_name,
meavi.attribute_category,
meavi.end_user_column_name
)
WHERE meavi.interface_header_id = p_interface_header_id
AND meavi.process_status = 'P';
UPDATE MTL_EAM_ATTR_VAL_INTERFACE meavi
SET meavi.error_number = 9999,
meavi.process_status = 'E',
meavi.error_message = 'Application Column Name not found'
WHERE meavi.interface_header_id = p_interface_header_id
AND meavi.process_status = 'P'
AND meavi.application_column_name IS NULL;
UPDATE MTL_EAM_ATTR_VAL_INTERFACE meavi
SET (meavi.association_id) =
(SELECT meaag.association_id
FROM MTL_EAM_ASSET_ATTR_GROUPS meaag,
MTL_EAM_ASSET_NUM_INTERFACE meani
WHERE meani.inventory_item_id = meaag.inventory_item_id
AND meani.interface_header_id = meavi.interface_header_id
AND meavi.application_id = meaag.application_id
AND meavi.descriptive_flexfield_name = meaag.descriptive_flexfield_name
AND meavi.attribute_category = meaag.descriptive_flex_context_code
AND UPPER(NVL(meaag.enabled_flag,'Y')) = 'Y')
WHERE meavi.interface_header_id = p_interface_header_id
AND meavi.process_status = 'P';
UPDATE MTL_EAM_ATTR_VAL_INTERFACE meavi
SET meavi.error_number = 9999,
meavi.process_status = 'E',
meavi.error_message = 'Attribute Group not associated with Asset Group'
WHERE meavi.interface_header_id = p_interface_header_id
AND meavi.process_status = 'P'
AND meavi.association_id IS NULL;
UPDATE MTL_EAM_ATTR_VAL_INTERFACE meavi
SET meavi.error_number = 9999,
meavi.process_status = 'E',
meavi.error_message = 'line_type must be 1 (VARCHAR2), 2 (NUMBER), or 3 (DATE)'
WHERE meavi.interface_header_id = p_interface_header_id
AND meavi.process_status = 'P'
AND meavi.line_type NOT IN (1, 2, 3);
select instance_id into l_instance_id from csi_item_instances where instance_number = l_instance_number;
-- Check if Attribute has not existed (for Create Mode), or exists (for Update Mode)
IF (p_import_mode = 0) THEN -- Create Mode
-- before executing create, make sure that duplicate do not exists.
SELECT count(*)
INTO l_exists_count
FROM MTL_EAM_ASSET_ATTR_VALUES meaav
WHERE meaav.maintenance_object_type = 3
AND meaav.maintenance_object_id = l_instance_id
AND meaav.application_id = attr_group.application_id
AND meaav.descriptive_flexfield_name = attr_group.descriptive_flexfield_name
AND meaav.attribute_category = attr_group.attribute_category;
UPDATE mtl_eam_attr_val_interface meavi
SET meavi.process_status = 'E',
meavi.error_number = 9999,
meavi.error_message = 'Attribute Group Already exists'
WHERE meavi.interface_header_id = p_interface_header_id
AND meavi.application_id = attr_group.application_id
AND meavi.descriptive_flexfield_name = attr_group.descriptive_flexfield_name
AND meavi.attribute_category = attr_group.attribute_category
AND meavi.process_status = 'P';
ELSIF (p_import_mode = 1) THEN -- Update Mode
BEGIN
SELECT meaav.rowid
INTO l_rowid
FROM MTL_EAM_ASSET_ATTR_VALUES meaav
WHERE meaav.maintenance_object_type = 3
AND meaav.maintenance_object_id = l_instance_id
AND meaav.application_id = attr_group.application_id
AND meaav.descriptive_flexfield_name = attr_group.descriptive_flexfield_name
AND meaav.attribute_category = attr_group.attribute_category;
UPDATE mtl_eam_attr_val_interface meavi
SET meavi.process_status = 'E',
meavi.error_number = 9999,
meavi.error_message = 'Attribute Group does not exist'
WHERE meavi.interface_header_id = p_interface_header_id
AND meavi.application_id = attr_group.application_id
AND meavi.descriptive_flexfield_name = attr_group.descriptive_flexfield_name
AND meavi.attribute_category = attr_group.attribute_category
AND meavi.process_status = 'P';
IF ( p_import_mode = 1) THEN -- Update Mode
-- Populate flex validation API with segment values that are missing
-- in the interface table from the base table mtl_eam_asset_attr_values
FOR missing_attr IN missing_attr_cur
(
attr_group.application_id,
attr_group.descriptive_flexfield_name,
attr_group.attribute_category
) LOOP
-- Bug: 2094907, added the following to remove DBMS_SQL
BEGIN
-- Bug # 3373134
IF (SUBSTR(missing_attr.application_column_name, 1, 1) = 'D') THEN
l_sql_stmt1 := 'SELECT to_char(meaav.'|| missing_attr.application_column_name ||', ''yyyy-mm-dd'')' ;
l_sql_stmt1 := 'SELECT meaav.'|| missing_attr.application_column_name ;
END IF; -- End if UPDATE mode
UPDATE mtl_eam_attr_val_interface meavi
SET meavi.process_status = 'E',
meavi.error_number = 9999,
meavi.error_message = FND_FLEX_DESCVAL.error_message
WHERE meavi.interface_header_id = p_interface_header_id
AND meavi.application_id = attr_group.application_id
AND meavi.descriptive_flexfield_name = attr_group.descriptive_flexfield_name
AND meavi.attribute_category = attr_group.attribute_category
AND meavi.process_status = 'P';
SELECT meavi.interface_line_id
FROM mtl_eam_attr_val_interface meavi
WHERE meavi.interface_header_id = p_interface_header_id
AND meavi.application_id = attr_group.application_id
AND meavi.descriptive_flexfield_name = attr_group.descriptive_flexfield_name
AND meavi.attribute_category = attr_group.attribute_category
AND meavi.process_status = 'E'
AND meavi.error_number IS NOT NULL;
EAM_ASSET_ATTR_PVT.INSERT_ROW(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
p_commit => l_commit ,
p_validation_level => l_validation_level,
p_rowid => l_rowid ,
p_association_id => l_association_id ,
p_application_id => l_application_id,
p_descriptive_flexfield_name => l_descriptive_flexfield_name ,
p_inventory_item_id => l_inventory_item_id,
p_serial_number => l_serial_number ,
p_organization_id => l_organization_id,
p_attribute_category => l_attribute_category ,
p_c_attribute1 => l_c_attribute(1 ),
p_c_attribute2 => l_c_attribute(2 ),
p_c_attribute3 => l_c_attribute(3),
p_c_attribute4 => l_c_attribute(4 ),
p_c_attribute5 => l_c_attribute(5 ),
p_c_attribute6 => l_c_attribute(6 ),
p_c_attribute7 => l_c_attribute(7 ),
p_c_attribute8 => l_c_attribute(8 ),
p_c_attribute9 => l_c_attribute(9),
p_c_attribute10 => l_c_attribute(10),
p_c_attribute11 => l_c_attribute(11),
p_c_attribute12 => l_c_attribute(12),
p_c_attribute13 => l_c_attribute(13 ),
p_c_attribute14 => l_c_attribute(14),
p_c_attribute15 => l_c_attribute(15 ),
p_c_attribute16 => l_c_attribute(16),
p_c_attribute17 => l_c_attribute(17),
p_c_attribute18 => l_c_attribute(18),
p_c_attribute19 => l_c_attribute(19 ),
p_c_attribute20 => l_c_attribute(20 ),
p_d_attribute1 => l_d_attribute(1),
p_d_attribute2 => l_d_attribute(2),
p_d_attribute3 => l_d_attribute(3),
p_d_attribute4 => l_d_attribute(4),
p_d_attribute5 => l_d_attribute(5 ),
p_d_attribute6 => l_d_attribute(6),
p_d_attribute7 => l_d_attribute(7),
p_d_attribute8 => l_d_attribute(8 ),
p_d_attribute9 => l_d_attribute(9),
p_d_attribute10 => l_d_attribute(10),
p_n_attribute1 => l_n_attribute(1),
p_n_attribute2 => l_n_attribute(2 ),
p_n_attribute3 => l_n_attribute(3),
p_n_attribute4 => l_n_attribute(4),
p_n_attribute5 => l_n_attribute(5),
p_n_attribute6 => l_n_attribute(6 ),
p_n_attribute7 => l_n_attribute(7),
p_n_attribute8 => l_n_attribute(8),
p_n_attribute9 => l_n_attribute(9 ),
p_n_attribute10 => l_n_attribute(10),
p_last_update_date => l_last_update_date,
p_last_updated_by => l_last_updated_by ,
p_maintenance_object_type => 3,
p_maintenance_object_id => l_instance_id,
p_creation_organization_id => l_organization_id,
p_creation_date => l_creation_date,
p_created_by => l_created_by,
p_last_update_login => l_last_update_login ,
x_return_status => l_return_status,
x_msg_count => l_msg_count ,
x_msg_data => l_msg_data
);
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module, 'EAM_ASSET_ATTR_PVT.Insert_Row return status=' || l_return_status);
EAM_ASSET_ATTR_PVT.UPDATE_ROW(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
p_commit => l_commit,
p_validation_level => l_validation_level,
p_rowid => l_rowid,
p_c_attribute1 => l_c_attribute(1),
p_c_attribute2 => l_c_attribute(2),
p_c_attribute3 => l_c_attribute(3),
p_c_attribute4 => l_c_attribute(4),
p_c_attribute5 => l_c_attribute(5),
p_c_attribute6 => l_c_attribute(6),
p_c_attribute7 => l_c_attribute(7),
p_c_attribute8 => l_c_attribute(8),
p_c_attribute9 => l_c_attribute(9),
p_c_attribute10 => l_c_attribute(10),
p_c_attribute11 => l_c_attribute(11),
p_c_attribute12 => l_c_attribute(12),
p_c_attribute13 => l_c_attribute(13),
p_c_attribute14 => l_c_attribute(14),
p_c_attribute15 => l_c_attribute(15),
p_c_attribute16 => l_c_attribute(16),
p_c_attribute17 => l_c_attribute(17),
p_c_attribute18 => l_c_attribute(18),
p_c_attribute19 => l_c_attribute(19),
p_c_attribute20 => l_c_attribute(20),
p_d_attribute1 => l_d_attribute(1),
p_d_attribute2 => l_d_attribute(2),
p_d_attribute3 => l_d_attribute(3),
p_d_attribute4 => l_d_attribute(4),
p_d_attribute5 => l_d_attribute(5 ),
p_d_attribute6 => l_d_attribute(6),
p_d_attribute7 => l_d_attribute(7),
p_d_attribute8 => l_d_attribute(8 ),
p_d_attribute9 => l_d_attribute(9),
p_d_attribute10 => l_d_attribute(10),
p_n_attribute1 => l_n_attribute(1),
p_n_attribute2 => l_n_attribute(2 ),
p_n_attribute3 => l_n_attribute(3),
p_n_attribute4 => l_n_attribute(4),
p_n_attribute5 => l_n_attribute(5),
p_n_attribute6 => l_n_attribute(6 ),
p_n_attribute7 => l_n_attribute(7),
p_n_attribute8 => l_n_attribute(8),
p_n_attribute9 => l_n_attribute(9 ),
p_n_attribute10 => l_n_attribute(10),
p_maintenance_object_type => 3,
p_maintenance_object_id => l_instance_id,
p_last_update_date => l_last_update_date,
p_last_updated_by => l_last_updated_by ,
p_last_update_login => l_last_update_login ,
x_return_status => l_return_status,
x_msg_count => l_msg_count ,
x_msg_data => l_msg_data
);
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module, 'EAM_ASSET_ATTR_PVT.Update_Row return status=' || l_return_status);
UPDATE mtl_eam_attr_val_interface meavi
SET meavi.process_status = 'S',
meavi.error_number = NULL,
meavi.error_message = 'Success'
WHERE meavi.interface_header_id = p_interface_header_id
AND meavi.application_id = attr_group.application_id
AND meavi.descriptive_flexfield_name = attr_group.descriptive_flexfield_name
AND meavi.attribute_category = attr_group.attribute_category
AND meavi.process_status = 'P';
FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, l_module, 'Insert/Update call to EAM_ASSET_ATTR_PVT failed. Raising exception.');
SELECT meavi.interface_line_id
FROM mtl_eam_attr_val_interface meavi
WHERE meavi.interface_header_id = p_interface_header_id
-- Since the following fields can have errors
-- AND meavi.application_id = l_application_id
-- AND meavi.descriptive_flexfield_name = l_descriptive_flexfield_name
AND meavi.process_status = 'E'
AND meavi.error_number IS NOT NULL;
DELETE MTL_EAM_ATTR_VAL_INTERFACE meavi
WHERE meavi.interface_header_id = p_interface_header_id
AND ERROR_NUMBER IS NULL
AND PROCESS_STATUS = 'S';
UPDATE mtl_eam_attr_val_interface meavi
SET meavi.process_status = 'E',
meavi.error_number = 9999,
meavi.error_message = 'Failed as another Attribute of this Asset Number has failed validation'
WHERE meavi.interface_header_id = p_interface_header_id
-- AND meavi.application_id = l_application_id
-- AND meavi.descriptive_flexfield_name = l_descriptive_flexfield_name
-- AND meavi.attribute_category = attr_group.attribute_category
AND meavi.process_status <> 'E' -- could be 'S' or 'P'
-- AND meavi.process_status = 'P'
AND meavi.error_number IS NULL
AND EXISTS(
SELECT meavi.process_status
FROM mtl_eam_attr_val_interface meavi
WHERE meavi.interface_header_id = p_interface_header_id
AND meavi.application_id = l_application_id
AND meavi.descriptive_flexfield_name = l_descriptive_flexfield_name
-- AND meavi.attribute_category = attr_group.attribute_category
AND meavi.process_status = 'E'
AND meavi.error_number IS NOT NULL);