[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE INSERT_ROW(
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_INVENTORY_ITEM_ID NUMBER,
P_SERIAL_NUMBER VARCHAR2,
P_INSTANCE_NUMBER VARCHAR2,
P_INSTANCE_DESCRIPTION VARCHAR2,
P_ORGANIZATION_ID NUMBER,
P_CATEGORY_ID NUMBER,
P_PN_LOCATION_ID NUMBER,
P_FA_ASSET_ID NUMBER,
P_FA_SYNC_FLAG VARCHAR2,
P_ASSET_CRITICALITY_CODE VARCHAR2,
P_MAINTAINABLE_FLAG VARCHAR2,
P_NETWORK_ASSET_FLAG VARCHAR2,
P_ATTRIBUTE_CATEGORY VARCHAR2 DEFAULT NULL,
P_ATTRIBUTE1 VARCHAR2 DEFAULT NULL,
P_ATTRIBUTE2 VARCHAR2 DEFAULT NULL,
P_ATTRIBUTE3 VARCHAR2 DEFAULT NULL,
P_ATTRIBUTE4 VARCHAR2 DEFAULT NULL,
P_ATTRIBUTE5 VARCHAR2 DEFAULT NULL,
P_ATTRIBUTE6 VARCHAR2 DEFAULT NULL,
P_ATTRIBUTE7 VARCHAR2 DEFAULT NULL,
P_ATTRIBUTE8 VARCHAR2 DEFAULT NULL,
P_ATTRIBUTE9 VARCHAR2 DEFAULT NULL,
P_ATTRIBUTE10 VARCHAR2 DEFAULT NULL,
P_ATTRIBUTE11 VARCHAR2 DEFAULT NULL,
P_ATTRIBUTE12 VARCHAR2 DEFAULT NULL,
P_ATTRIBUTE13 VARCHAR2 DEFAULT NULL,
P_ATTRIBUTE14 VARCHAR2 DEFAULT NULL,
P_ATTRIBUTE15 VARCHAR2 DEFAULT NULL,
P_ATTRIBUTE16 VARCHAR2 DEFAULT NULL,
P_ATTRIBUTE17 VARCHAR2 DEFAULT NULL,
P_ATTRIBUTE18 VARCHAR2 DEFAULT NULL,
P_ATTRIBUTE19 VARCHAR2 DEFAULT NULL,
P_ATTRIBUTE20 VARCHAR2 DEFAULT NULL,
P_ATTRIBUTE21 VARCHAR2 DEFAULT NULL,
P_ATTRIBUTE22 VARCHAR2 DEFAULT NULL,
P_ATTRIBUTE23 VARCHAR2 DEFAULT NULL,
P_ATTRIBUTE24 VARCHAR2 DEFAULT NULL,
P_ATTRIBUTE25 VARCHAR2 DEFAULT NULL,
P_ATTRIBUTE26 VARCHAR2 DEFAULT NULL,
P_ATTRIBUTE27 VARCHAR2 DEFAULT NULL,
P_ATTRIBUTE28 VARCHAR2 DEFAULT NULL,
P_ATTRIBUTE29 VARCHAR2 DEFAULT NULL,
P_ATTRIBUTE30 VARCHAR2 DEFAULT NULL,
P_REQUEST_ID NUMBER DEFAULT NULL,
P_PROGRAM_APPLICATION_ID NUMBER DEFAULT NULL,
P_PROGRAM_ID NUMBER DEFAULT NULL,
P_PROGRAM_UPDATE_DATE DATE DEFAULT NULL,
P_LAST_UPDATE_DATE DATE,
P_LAST_UPDATED_BY NUMBER,
P_CREATION_DATE DATE,
P_CREATED_BY NUMBER,
P_LAST_UPDATE_LOGIN NUMBER,
p_active_start_date DATE DEFAULT NULL,
p_active_end_date DATE DEFAULT NULL,
p_location NUMBER DEFAULT NULL,
p_linear_location_id NUMBER DEFAULT NULL,
p_operational_log_flag VARCHAR2 DEFAULT NULL,
p_checkin_status NUMBER DEFAULT NULL,
p_supplier_warranty_exp_date DATE DEFAULT NULL,
p_equipment_gen_object_id NUMBER DEFAULT NULL,
p_mfg_serial_number_flag VARCHAR2,
X_OBJECT_ID OUT NOCOPY NUMBER,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2
)
is
l_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
l_api_name CONSTANT VARCHAR2(30) := 'insert_row';
SAVEPOINT insert_row;
select master_organization_id
into l_master_organization_id
from mtl_parameters
where organization_id = p_organization_id;
select primary_uom_code into l_primary_uom_code
from mtl_system_items
where organization_id = p_organization_id
and inventory_item_id = p_inventory_item_id;
select internal_party_id
into l_internal_party_id
from csi_install_parameters;
select fb.book_type_code
into l_asset_assignment_tbl(1).fa_book_type_code
from fa_books fb,
fa_book_controls fbc
where fb.asset_id = P_FA_ASSET_ID
and fb.book_type_code = fbc.book_type_code
and fbc.book_class = 'CORPORATE'
and rownum = 1;
select fdh.location_id
into l_asset_assignment_tbl(1).fa_location_id
from fa_distribution_history fdh
where asset_id = P_FA_ASSET_ID
and book_type_code = l_asset_assignment_tbl(1).fa_book_type_code
and date_ineffective is null
and rownum = 1;
l_asset_assignment_tbl(1).update_status := 'IN_SERVICE';
/* Bug # 5339642 : Call Text index procedure for inserting the row in EAT */
eam_text_util.process_asset_update_event
(
p_event => 'INSERT'
,p_instance_id => l_instance_rec.instance_id
);
ROLLBACK TO insert_row;
ROLLBACK TO insert_row;
ROLLBACK TO insert_row;
end insert_row;
PROCEDURE UPDATE_ROW(
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_instance_id IN NUMBER,
P_INSTANCE_DESCRIPTION VARCHAR2,
P_CATEGORY_ID NUMBER,
P_PN_LOCATION_ID NUMBER,
P_FA_ASSET_ID NUMBER,
P_FA_SYNC_FLAG VARCHAR2 DEFAULT NULL,
P_ASSET_CRITICALITY_CODE VARCHAR2,
P_MAINTAINABLE_FLAG VARCHAR2,
P_NETWORK_ASSET_FLAG VARCHAR2,
P_ATTRIBUTE_CATEGORY VARCHAR2,
P_ATTRIBUTE1 VARCHAR2 DEFAULT NULL,
P_ATTRIBUTE2 VARCHAR2 DEFAULT NULL,
P_ATTRIBUTE3 VARCHAR2 DEFAULT NULL,
P_ATTRIBUTE4 VARCHAR2 DEFAULT NULL,
P_ATTRIBUTE5 VARCHAR2 DEFAULT NULL,
P_ATTRIBUTE6 VARCHAR2 DEFAULT NULL,
P_ATTRIBUTE7 VARCHAR2 DEFAULT NULL,
P_ATTRIBUTE8 VARCHAR2 DEFAULT NULL,
P_ATTRIBUTE9 VARCHAR2 DEFAULT NULL,
P_ATTRIBUTE10 VARCHAR2 DEFAULT NULL,
P_ATTRIBUTE11 VARCHAR2 DEFAULT NULL,
P_ATTRIBUTE12 VARCHAR2 DEFAULT NULL,
P_ATTRIBUTE13 VARCHAR2 DEFAULT NULL,
P_ATTRIBUTE14 VARCHAR2 DEFAULT NULL,
P_ATTRIBUTE15 VARCHAR2 DEFAULT NULL,
P_ATTRIBUTE16 VARCHAR2 DEFAULT NULL,
P_ATTRIBUTE17 VARCHAR2 DEFAULT NULL,
P_ATTRIBUTE18 VARCHAR2 DEFAULT NULL,
P_ATTRIBUTE19 VARCHAR2 DEFAULT NULL,
P_ATTRIBUTE20 VARCHAR2 DEFAULT NULL,
P_ATTRIBUTE21 VARCHAR2 DEFAULT NULL,
P_ATTRIBUTE22 VARCHAR2 DEFAULT NULL,
P_ATTRIBUTE23 VARCHAR2 DEFAULT NULL,
P_ATTRIBUTE24 VARCHAR2 DEFAULT NULL,
P_ATTRIBUTE25 VARCHAR2 DEFAULT NULL,
P_ATTRIBUTE26 VARCHAR2 DEFAULT NULL,
P_ATTRIBUTE27 VARCHAR2 DEFAULT NULL,
P_ATTRIBUTE28 VARCHAR2 DEFAULT NULL,
P_ATTRIBUTE29 VARCHAR2 DEFAULT NULL,
P_ATTRIBUTE30 VARCHAR2 DEFAULT NULL,
P_REQUEST_ID NUMBER DEFAULT NULL,
P_PROGRAM_APPLICATION_ID NUMBER DEFAULT NULL,
P_PROGRAM_ID NUMBER DEFAULT NULL,
P_PROGRAM_UPDATE_DATE DATE DEFAULT NULL,
P_LAST_UPDATE_DATE DATE,
P_LAST_UPDATED_BY NUMBER,
P_LAST_UPDATE_LOGIN NUMBER,
P_FROM_PUBLIC_API VARCHAR2 DEFAULT 'Y',
P_INSTANCE_NUMBER VARCHAR2 DEFAULT NULL,
P_LOCATION_TYPE_CODE VARCHAR2 DEFAULT NULL,
P_LOCATION_ID NUMBER DEFAULT NULL,
p_active_end_date DATE DEFAULT NULL,
p_linear_location_id NUMBER DEFAULT NULL,
p_operational_log_flag VARCHAR2 DEFAULT NULL,
p_checkin_status NUMBER DEFAULT NULL,
p_supplier_warranty_exp_date DATE DEFAULT NULL,
p_equipment_gen_object_id NUMBER DEFAULT NULL,
p_reactivate_asset VARCHAR2 DEFAULT 'N',
p_disassociate_fa_flag VARCHAR2 DEFAULT 'N', --5474749
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2
)
is
l_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
l_api_name CONSTANT VARCHAR2(30) := 'update_row';
SAVEPOINT update_row;
--csi_gen_utility_pvt.put_line('EAM: Start of update_row, Active End date is : '||to_char(p_active_end_date,'dd-mon-yy hh24:mi:ss'));
select object_version_number
into l_object_version_number
from csi_item_instances
where instance_id = p_instance_id;
select instance_asset_id, object_version_number
into l_asset_assignment_tbl(1).instance_asset_id,l_asset_assignment_tbl(1).object_version_number
from csi_i_assets
where instance_id = p_instance_id
and fa_asset_id = fa_asset_id;
select fb.book_type_code
into l_asset_assignment_tbl(1).fa_book_type_code
from fa_books fb,
fa_book_controls fbc
where fb.asset_id = P_FA_ASSET_ID
and fb.book_type_code = fbc.book_type_code
and fbc.book_class = 'CORPORATE'
and rownum = 1;
select fdh.location_id
into l_asset_assignment_tbl(1).fa_location_id
from fa_distribution_history fdh
where asset_id = P_FA_ASSET_ID
and book_type_code = l_asset_assignment_tbl(1).fa_book_type_code
and date_ineffective is null
and rownum = 1;
l_asset_assignment_tbl(1).update_status := 'IN_SERVICE';
select fa_asset_id into l_asset_assignment_tbl(1).fa_asset_id
from csi_i_assets where instance_id = p_instance_id;
select instance_asset_id, object_version_number
into l_asset_assignment_tbl(1).instance_asset_id,l_asset_assignment_tbl(1).object_version_number
from csi_i_assets
where instance_id = p_instance_id
and fa_asset_id = l_asset_assignment_tbl(1).fa_asset_id;
select fb.book_type_code
into l_asset_assignment_tbl(1).fa_book_type_code
from fa_books fb,
fa_book_controls fbc
where fb.asset_id = l_asset_assignment_tbl(1).fa_asset_id
and fb.book_type_code = fbc.book_type_code
and fbc.book_class = 'CORPORATE'
and rownum = 1;
select fdh.location_id
into l_asset_assignment_tbl(1).fa_location_id
from fa_distribution_history fdh
where asset_id = l_asset_assignment_tbl(1).fa_asset_id
and book_type_code = l_asset_assignment_tbl(1).fa_book_type_code
and date_ineffective is null
and rownum = 1;
l_asset_assignment_tbl(1).update_status := 'IN_SERVICE';
select location_type_code
into l_old_location_type_code
from csi_item_instances
where instance_id = p_instance_id;
FND_MESSAGE.SET_NAME('EAM', 'EAM_INVALID_LOCATION_UPDATE');
csi_item_instance_pub.update_item_instance(
p_api_version => 1.0,
p_commit => p_commit,
p_init_msg_list => p_init_msg_list,
p_validation_level => 100,
p_instance_rec => l_instance_rec,
p_ext_attrib_values_tbl => l_ext_attrib_values_tbl,
p_party_tbl => l_party_tbl,
p_account_tbl => l_party_account_tbl,
p_pricing_attrib_tbl => l_pricing_attrib_tbl,
p_org_assignments_tbl => l_org_assignments_tbl,
p_asset_assignment_tbl => l_asset_assignment_tbl,
p_txn_rec => l_txn_rec,
x_instance_id_lst => l_x_instance_id_lst,
x_return_status => l_x_return_status,
x_msg_count => l_x_msg_count,
x_msg_data => l_x_msg_data
);
/* Bug # 5339642 : Call Text index procedure for inserting the row in EAT */
eam_text_util.process_asset_update_event
(
p_event => 'UPDATE'
,p_instance_id => l_instance_rec.instance_id
);
ROLLBACK TO update_row;
ROLLBACK TO update_row;
ROLLBACK TO update_row;
end update_row;
P_PROGRAM_UPDATE_DATE DATE DEFAULT NULL,
P_LAST_UPDATE_DATE DATE,
P_LAST_UPDATED_BY NUMBER,
P_LAST_UPDATE_LOGIN NUMBER,
P_LOCATION_TYPE_CODE VARCHAR2 DEFAULT NULL,
P_LOCATION_ID NUMBER DEFAULT NULL,
p_linear_location_id NUMBER DEFAULT NULL,
p_operational_log_flag VARCHAR2 DEFAULT NULL,
P_checkin_status NUMBER DEFAULT NULL,
p_supplier_warranty_exp_date DATE DEFAULT NULL,
p_equipment_gen_object_id NUMBER DEFAULT NULL,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2
)
IS
CURSOR C IS
SELECT *
FROM CSI_ITEM_INSTANCES
WHERE INSTANCE_ID = p_INSTANCE_ID
FOR UPDATE of INSTANCE_ID NOWAIT;
FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
AND ( ( Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY)
OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
AND ( p_LAST_UPDATED_BY IS NULL )))
AND ( ( Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE)
OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
AND ( p_LAST_UPDATE_DATE IS NULL )))
AND ( ( Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
AND ( p_LAST_UPDATE_LOGIN IS NULL )))
AND ( ( Recinfo.INSTANCE_DESCRIPTION = p_INSTANCE_DESCRIPTION)
OR ( ( Recinfo.INSTANCE_DESCRIPTION IS NULL )
AND ( p_INSTANCE_DESCRIPTION IS NULL )))
AND ( ( Recinfo.CATEGORY_ID = p_CATEGORY_ID)
OR ( ( Recinfo.CATEGORY_ID IS NULL )
AND ( p_CATEGORY_ID IS NULL )))
AND ( ( Recinfo.PN_LOCATION_ID = p_PN_LOCATION_ID)
OR ( ( Recinfo.PN_LOCATION_ID IS NULL )
AND ( p_PN_LOCATION_ID IS NULL )))
AND ( ( Recinfo.ASSET_CRITICALITY_CODE = p_ASSET_CRITICALITY_CODE)
OR ( ( Recinfo.ASSET_CRITICALITY_CODE IS NULL )
AND ( p_ASSET_CRITICALITY_CODE IS NULL )))
AND ( ( Recinfo.MAINTAINABLE_FLAG = p_MAINTAINABLE_FLAG)
OR ( ( Recinfo.MAINTAINABLE_FLAG IS NULL )
AND ( p_MAINTAINABLE_FLAG IS NULL )))
AND ( ( Recinfo.NETWORK_ASSET_FLAG = p_NETWORK_ASSET_FLAG)
OR ( ( Recinfo.NETWORK_ASSET_FLAG IS NULL )
AND ( p_NETWORK_ASSET_FLAG IS NULL )))
AND ( ( Recinfo.LINEAR_LOCATION_ID = p_LINEAR_LOCATION_ID)
OR ( ( Recinfo.LINEAR_LOCATION_ID IS NULL )
AND ( p_LINEAR_LOCATION_ID IS NULL )))
AND ( ( Recinfo.OPERATIONAL_LOG_FLAG = p_OPERATIONAL_LOG_FLAG)
OR ( ( Recinfo.OPERATIONAL_LOG_FLAG IS NULL )
AND ( p_OPERATIONAL_LOG_FLAG IS NULL )))
AND ( ( Recinfo.CHECKIN_STATUS = p_CHECKIN_STATUS)
OR ( ( Recinfo.CHECKIN_STATUS IS NULL )
AND ( p_CHECKIN_STATUS IS NULL )))
AND ( ( Recinfo.SUPPLIER_WARRANTY_EXP_DATE = p_SUPPLIER_WARRANTY_EXP_DATE)
OR ( ( Recinfo.SUPPLIER_WARRANTY_EXP_DATE IS NULL )
AND ( p_SUPPLIER_WARRANTY_EXP_DATE IS NULL )))
AND ( ( Recinfo.EQUIPMENT_GEN_OBJECT_ID = p_EQUIPMENT_GEN_OBJECT_ID)
OR ( ( Recinfo.EQUIPMENT_GEN_OBJECT_ID IS NULL )
AND ( p_EQUIPMENT_GEN_OBJECT_ID IS NULL )))
*/
) THEN
RETURN;
,P_PROGRAM_UPDATE_DATE DATE DEFAULT NULL
,P_LAST_UPDATE_DATE DATE
,P_LAST_UPDATED_BY NUMBER
,P_CREATION_DATE DATE
,P_CREATED_BY NUMBER
,P_LAST_UPDATE_LOGIN NUMBER
,p_active_start_date DATE DEFAULT NULL
,p_active_end_date DATE DEFAULT NULL
,p_location NUMBER DEFAULT NULL
,p_linear_location_id NUMBER DEFAULT NULL
,p_operational_log_flag VARCHAR2 DEFAULT NULL
,p_checkin_status NUMBER DEFAULT NULL
,p_supplier_warranty_exp_date DATE DEFAULT NULL
,p_equipment_gen_object_id NUMBER DEFAULT NULL
,p_owning_department_id NUMBER DEFAULT NULL
,p_accounting_class_code VARCHAR2 DEFAULT NULL
,p_area_id NUMBER DEFAULT NULL
,X_OBJECT_ID OUT NOCOPY NUMBER
,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) := 'create_asset';
select 1
into l_count
from mtl_serial_numbers
where inventory_item_id = p_inventory_item_id
and serial_number = p_serial_number
and rownum <= 1;
insert_row(
P_API_VERSION => P_API_VERSION,
P_INVENTORY_ITEM_ID => P_INVENTORY_ITEM_ID,
P_SERIAL_NUMBER => P_SERIAL_NUMBER,
P_INSTANCE_NUMBER => P_INSTANCE_NUMBER,
P_INSTANCE_DESCRIPTION => P_INSTANCE_DESCRIPTION,
P_ORGANIZATION_ID => P_ORGANIZATION_ID,
P_CATEGORY_ID => P_CATEGORY_ID,
P_PN_LOCATION_ID => P_PN_LOCATION_ID,
P_FA_ASSET_ID => P_FA_ASSET_ID,
P_FA_SYNC_FLAG => P_FA_SYNC_FLAG,
P_ASSET_CRITICALITY_CODE => P_ASSET_CRITICALITY_CODE,
P_MAINTAINABLE_FLAG => P_MAINTAINABLE_FLAG,
P_NETWORK_ASSET_FLAG => P_NETWORK_ASSET_FLAG,
P_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY,
P_ATTRIBUTE1 => P_ATTRIBUTE1,
P_ATTRIBUTE2 => P_ATTRIBUTE2,
P_ATTRIBUTE3 => P_ATTRIBUTE3,
P_ATTRIBUTE4 => P_ATTRIBUTE4,
P_ATTRIBUTE5 => P_ATTRIBUTE5,
P_ATTRIBUTE6 => P_ATTRIBUTE6,
P_ATTRIBUTE7 => P_ATTRIBUTE7,
P_ATTRIBUTE8 => P_ATTRIBUTE8,
P_ATTRIBUTE9 => P_ATTRIBUTE9,
P_ATTRIBUTE10 => P_ATTRIBUTE10,
P_ATTRIBUTE11 => P_ATTRIBUTE11,
P_ATTRIBUTE12 => P_ATTRIBUTE12,
P_ATTRIBUTE13 => P_ATTRIBUTE13,
P_ATTRIBUTE14 => P_ATTRIBUTE14,
P_ATTRIBUTE15 => P_ATTRIBUTE15,
P_ATTRIBUTE16 => P_ATTRIBUTE16,
P_ATTRIBUTE17 => P_ATTRIBUTE17,
P_ATTRIBUTE18 => P_ATTRIBUTE18,
P_ATTRIBUTE19 => P_ATTRIBUTE19,
P_ATTRIBUTE20 => P_ATTRIBUTE20,
P_ATTRIBUTE21 => P_ATTRIBUTE21,
P_ATTRIBUTE22 => P_ATTRIBUTE22,
P_ATTRIBUTE23 => P_ATTRIBUTE23,
P_ATTRIBUTE24 => P_ATTRIBUTE24,
P_ATTRIBUTE25 => P_ATTRIBUTE25,
P_ATTRIBUTE26 => P_ATTRIBUTE26,
P_ATTRIBUTE27 => P_ATTRIBUTE27,
P_ATTRIBUTE28 => P_ATTRIBUTE28,
P_ATTRIBUTE29 => P_ATTRIBUTE29,
P_ATTRIBUTE30 => P_ATTRIBUTE30,
P_REQUEST_ID => P_REQUEST_ID,
P_PROGRAM_APPLICATION_ID => P_PROGRAM_APPLICATION_ID,
P_PROGRAM_ID => P_PROGRAM_ID,
P_PROGRAM_UPDATE_DATE => P_PROGRAM_UPDATE_DATE,
P_LAST_UPDATE_DATE => P_LAST_UPDATE_DATE,
P_LAST_UPDATED_BY => P_LAST_UPDATED_BY,
P_CREATION_DATE => P_CREATION_DATE,
P_CREATED_BY => P_CREATED_BY,
P_LAST_UPDATE_LOGIN => P_LAST_UPDATE_LOGIN,
p_active_start_date => p_active_start_date,
p_active_end_date => p_active_end_date,
p_location => p_location,
p_linear_location_id => p_linear_location_id,
p_operational_log_flag => p_operational_log_flag,
p_checkin_status => p_checkin_status,
p_supplier_warranty_exp_date => p_supplier_warranty_exp_date,
p_equipment_gen_object_id => p_equipment_gen_object_id,
p_mfg_serial_number_flag => l_mfg_serial_number_flag,
X_OBJECT_ID => l_instance_id
,X_RETURN_STATUS => l_x_asset_return_status
,X_MSG_COUNT => l_x_asset_msg_count
,X_MSG_DATA => l_x_asset_msg_data
);
procedure update_asset(
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_instance_id IN NUMBER
,P_INSTANCE_DESCRIPTION VARCHAR2
,P_INVENTORY_ITEM_ID NUMBER
,P_SERIAL_NUMBER VARCHAR2
,P_ORGANIZATION_ID NUMBER
,P_CATEGORY_ID NUMBER
,P_PN_LOCATION_ID NUMBER
,P_FA_ASSET_ID NUMBER
,P_FA_SYNC_FLAG VARCHAR2 DEFAULT NULL
,P_ASSET_CRITICALITY_CODE VARCHAR2
,P_MAINTAINABLE_FLAG VARCHAR2
,P_NETWORK_ASSET_FLAG VARCHAR2
,P_ATTRIBUTE_CATEGORY VARCHAR2
,P_ATTRIBUTE1 VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE2 VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE3 VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE4 VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE5 VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE6 VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE7 VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE8 VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE9 VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE10 VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE11 VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE12 VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE13 VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE14 VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE15 VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE16 VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE17 VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE18 VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE19 VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE20 VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE21 VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE22 VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE23 VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE24 VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE25 VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE26 VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE27 VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE28 VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE29 VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE30 VARCHAR2 DEFAULT NULL
,P_REQUEST_ID NUMBER DEFAULT NULL
,P_PROGRAM_APPLICATION_ID NUMBER DEFAULT NULL
,P_PROGRAM_ID NUMBER DEFAULT NULL
,P_PROGRAM_UPDATE_DATE DATE DEFAULT NULL
,P_LAST_UPDATE_DATE DATE
,P_LAST_UPDATED_BY NUMBER
,P_LAST_UPDATE_LOGIN NUMBER
,P_FROM_PUBLIC_API VARCHAR2 DEFAULT 'Y'
,P_INSTANCE_NUMBER VARCHAR2 DEFAULT NULL
,P_LOCATION_TYPE_CODE VARCHAR2 DEFAULT NULL
,P_LOCATION_ID NUMBER DEFAULT NULL
,p_active_end_date DATE DEFAULT NULL
,p_linear_location_id NUMBER DEFAULT NULL
,p_operational_log_flag VARCHAR2 DEFAULT NULL
,p_checkin_status NUMBER DEFAULT NULL
,p_supplier_warranty_exp_date DATE DEFAULT NULL
,p_equipment_gen_object_id NUMBER DEFAULT NULL
,p_owning_department_id NUMBER DEFAULT NULL
,p_accounting_class_code VARCHAR2 DEFAULT NULL
,p_area_id NUMBER DEFAULT NULL
,p_reactivate_asset VARCHAR2 DEFAULT 'N'
,p_disassociate_fa_flag VARCHAR2 DEFAULT 'N'
,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) := 'create_asset';
SAVEPOINT update_asset;
select 1,current_status
into l_count,l_current_status
from mtl_serial_numbers
where inventory_item_id = p_inventory_item_id
and serial_number = p_serial_number
and rownum <= 1;
insert_row(
P_API_VERSION => P_API_VERSION,
P_INVENTORY_ITEM_ID => P_INVENTORY_ITEM_ID,
P_SERIAL_NUMBER => P_SERIAL_NUMBER,
P_INSTANCE_NUMBER => P_INSTANCE_NUMBER,
P_INSTANCE_DESCRIPTION => P_INSTANCE_DESCRIPTION,
P_ORGANIZATION_ID => P_ORGANIZATION_ID,
P_CATEGORY_ID => P_CATEGORY_ID,
P_PN_LOCATION_ID => P_PN_LOCATION_ID,
P_FA_ASSET_ID => P_FA_ASSET_ID,
P_FA_SYNC_FLAG => P_FA_SYNC_FLAG,
P_ASSET_CRITICALITY_CODE => P_ASSET_CRITICALITY_CODE,
P_MAINTAINABLE_FLAG => P_MAINTAINABLE_FLAG,
P_NETWORK_ASSET_FLAG => P_NETWORK_ASSET_FLAG,
P_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY,
P_ATTRIBUTE1 => P_ATTRIBUTE1,
P_ATTRIBUTE2 => P_ATTRIBUTE2,
P_ATTRIBUTE3 => P_ATTRIBUTE3,
P_ATTRIBUTE4 => P_ATTRIBUTE4,
P_ATTRIBUTE5 => P_ATTRIBUTE5,
P_ATTRIBUTE6 => P_ATTRIBUTE6,
P_ATTRIBUTE7 => P_ATTRIBUTE7,
P_ATTRIBUTE8 => P_ATTRIBUTE8,
P_ATTRIBUTE9 => P_ATTRIBUTE9,
P_ATTRIBUTE10 => P_ATTRIBUTE10,
P_ATTRIBUTE11 => P_ATTRIBUTE11,
P_ATTRIBUTE12 => P_ATTRIBUTE12,
P_ATTRIBUTE13 => P_ATTRIBUTE13,
P_ATTRIBUTE14 => P_ATTRIBUTE14,
P_ATTRIBUTE15 => P_ATTRIBUTE15,
P_ATTRIBUTE16 => P_ATTRIBUTE16,
P_ATTRIBUTE17 => P_ATTRIBUTE17,
P_ATTRIBUTE18 => P_ATTRIBUTE18,
P_ATTRIBUTE19 => P_ATTRIBUTE19,
P_ATTRIBUTE20 => P_ATTRIBUTE20,
P_ATTRIBUTE21 => P_ATTRIBUTE21,
P_ATTRIBUTE22 => P_ATTRIBUTE22,
P_ATTRIBUTE23 => P_ATTRIBUTE23,
P_ATTRIBUTE24 => P_ATTRIBUTE24,
P_ATTRIBUTE25 => P_ATTRIBUTE25,
P_ATTRIBUTE26 => P_ATTRIBUTE26,
P_ATTRIBUTE27 => P_ATTRIBUTE27,
P_ATTRIBUTE28 => P_ATTRIBUTE28,
P_ATTRIBUTE29 => P_ATTRIBUTE29,
P_ATTRIBUTE30 => P_ATTRIBUTE30,
P_REQUEST_ID => P_REQUEST_ID,
P_PROGRAM_APPLICATION_ID => P_PROGRAM_APPLICATION_ID,
P_PROGRAM_ID => P_PROGRAM_ID,
P_PROGRAM_UPDATE_DATE => P_PROGRAM_UPDATE_DATE,
P_LAST_UPDATE_DATE => P_LAST_UPDATE_DATE,
P_LAST_UPDATED_BY => P_LAST_UPDATED_BY,
P_CREATION_DATE => P_LAST_UPDATE_DATE,
P_CREATED_BY => P_LAST_UPDATED_BY,
P_LAST_UPDATE_LOGIN => P_LAST_UPDATE_LOGIN,
p_active_start_date => sysdate,
p_active_end_date => p_active_end_date,
p_location => p_location_id,
p_linear_location_id => p_linear_location_id,
p_operational_log_flag => p_operational_log_flag,
p_checkin_status => p_checkin_status,
p_supplier_warranty_exp_date => p_supplier_warranty_exp_date,
p_equipment_gen_object_id => p_equipment_gen_object_id,
p_mfg_serial_number_flag => l_mfg_serial_number_flag,
X_OBJECT_ID => l_instance_id
,X_RETURN_STATUS => l_x_asset_return_status
,X_MSG_COUNT => l_x_asset_msg_count
,X_MSG_DATA => l_x_asset_msg_data
);
update_row
(
P_API_VERSION => P_API_VERSION,
p_instance_id => P_INSTANCE_ID,
P_INSTANCE_DESCRIPTION => P_INSTANCE_DESCRIPTION,
P_CATEGORY_ID => P_CATEGORY_ID,
P_PN_LOCATION_ID => P_PN_LOCATION_ID,
P_FA_ASSET_ID => P_FA_ASSET_ID,
P_FA_SYNC_FLAG => P_FA_SYNC_FLAG,
P_ASSET_CRITICALITY_CODE => P_ASSET_CRITICALITY_CODE,
P_MAINTAINABLE_FLAG => P_MAINTAINABLE_FLAG,
P_NETWORK_ASSET_FLAG => P_NETWORK_ASSET_FLAG,
P_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY,
P_ATTRIBUTE1 => P_ATTRIBUTE1,
P_ATTRIBUTE2 => P_ATTRIBUTE2,
P_ATTRIBUTE3 => P_ATTRIBUTE3,
P_ATTRIBUTE4 => P_ATTRIBUTE4,
P_ATTRIBUTE5 => P_ATTRIBUTE5,
P_ATTRIBUTE6 => P_ATTRIBUTE6,
P_ATTRIBUTE7 => P_ATTRIBUTE7,
P_ATTRIBUTE8 => P_ATTRIBUTE8,
P_ATTRIBUTE9 => P_ATTRIBUTE9,
P_ATTRIBUTE10 => P_ATTRIBUTE10,
P_ATTRIBUTE11 => P_ATTRIBUTE11,
P_ATTRIBUTE12 => P_ATTRIBUTE12,
P_ATTRIBUTE13 => P_ATTRIBUTE13,
P_ATTRIBUTE14 => P_ATTRIBUTE14,
P_ATTRIBUTE15 => P_ATTRIBUTE15,
P_ATTRIBUTE16 => P_ATTRIBUTE16,
P_ATTRIBUTE17 => P_ATTRIBUTE17,
P_ATTRIBUTE18 => P_ATTRIBUTE18,
P_ATTRIBUTE19 => P_ATTRIBUTE19,
P_ATTRIBUTE20 => P_ATTRIBUTE20,
P_ATTRIBUTE21 => P_ATTRIBUTE21,
P_ATTRIBUTE22 => P_ATTRIBUTE22,
P_ATTRIBUTE23 => P_ATTRIBUTE23,
P_ATTRIBUTE24 => P_ATTRIBUTE24,
P_ATTRIBUTE25 => P_ATTRIBUTE25,
P_ATTRIBUTE26 => P_ATTRIBUTE26,
P_ATTRIBUTE27 => P_ATTRIBUTE27,
P_ATTRIBUTE28 => P_ATTRIBUTE28,
P_ATTRIBUTE29 => P_ATTRIBUTE29,
P_ATTRIBUTE30 => P_ATTRIBUTE30,
P_REQUEST_ID => P_REQUEST_ID,
P_PROGRAM_APPLICATION_ID => P_PROGRAM_APPLICATION_ID,
P_PROGRAM_ID => P_PROGRAM_ID,
P_PROGRAM_UPDATE_DATE => P_PROGRAM_UPDATE_DATE,
P_LAST_UPDATE_DATE => P_LAST_UPDATE_DATE,
P_LAST_UPDATED_BY => P_LAST_UPDATED_BY,
P_LAST_UPDATE_LOGIN => P_LAST_UPDATE_LOGIN,
P_FROM_PUBLIC_API => P_FROM_PUBLIC_API ,
P_INSTANCE_NUMBER => P_INSTANCE_NUMBER,
P_LOCATION_TYPE_CODE => P_LOCATION_TYPE_CODE,
P_LOCATION_ID => P_LOCATION_ID,
p_active_end_date => p_active_end_date,
p_linear_location_id => p_linear_location_id,
p_operational_log_flag => p_operational_log_flag,
p_checkin_status => p_checkin_status,
p_supplier_warranty_exp_date => p_supplier_warranty_exp_date,
p_equipment_gen_object_id => p_equipment_gen_object_id
,p_reactivate_asset => p_reactivate_asset
,p_disassociate_fa_flag => p_disassociate_fa_flag
,X_RETURN_STATUS => l_x_asset_return_status
,X_MSG_COUNT => l_x_asset_msg_count
,X_MSG_DATA => l_x_asset_msg_data
);
SELECT maint_organization_id INTO l_count
FROM mtl_parameters
WHERE organization_id = p_organization_id;
EAM_ORG_MAINT_DEFAULTS_PVT.update_insert_row(
p_api_version => p_api_version
,p_object_type => 50
,p_object_id => l_instance_id
,p_organization_id => l_count
,p_owning_department_id => l_owning_department_id
,p_accounting_class_code => l_accounting_class_code
,p_area_id => l_area_id
,x_return_status => l_x_maint_return_status
,x_msg_count => l_x_maint_msg_count
,x_msg_data => l_x_maint_msg_data
);
ROLLBACK TO update_asset;
ROLLBACK TO update_asset;
ROLLBACK TO update_asset;
end update_asset;
select serial_number_type
into l_serial_number_type
from mtl_parameters
where organization_id = p_organization_id;
select count(1) into l_count
from mtl_serial_numbers
where inventory_item_id = p_inventory_item_id
and serial_number = p_serial_number;
select cii.serial_number,
cii.instance_number,
cii.inventory_item_id,
msn.gen_object_id
from csi_item_instances cii,
mtl_serial_numbers msn,
fa_additions fa,
csi_i_assets cia,
mtl_system_items msi,
mtl_parameters mp,
eam_org_maint_defaults eomd,
mtl_system_items msi_prod,
mtl_serial_numbers msn_prod,
mtl_parameters mp_prod
where cii.last_vld_organization_id = msn.current_organization_id
and cii.inventory_item_id=msn.inventory_item_id
and cii.serial_number=msn.serial_number
and cii.last_vld_organization_id = p_organization_id
and nvl(cii.network_asset_flag,'N') = 'N'
and msi.eam_item_type=p_eam_item_type
and msi.inventory_item_id = cii.inventory_item_id
and msi.organization_id = cii.last_vld_organization_id
and cii.instance_id = cia.instance_id(+)
and cia.fa_asset_id = fa.asset_id(+)
and mp.organization_id = cii.last_vld_organization_id
and cii.instance_id = eomd.object_id (+)
and eomd.object_type(+) = 50
and eomd.organization_id(+) = cii.last_vld_organization_id --mp.maint_organization_id
and cii.equipment_gen_object_id = msn_prod.gen_object_id(+)
and msn_prod.current_organization_id = msi_prod.organization_id(+)
and msn_prod.inventory_item_id = msi_prod.inventory_item_id(+)
and msi_prod.organization_id = mp_prod.organization_id(+)
and msi_prod.equipment_type(+) = 1
and (p_inventory_item_id is null or p_inventory_item_id = cii.inventory_item_id)
and (p_instance_id is null or p_instance_id = cii.instance_id)
and (p_category_id is null or p_category_id = cii.category_id)
and (P_PN_LOCATION_ID is null or P_PN_LOCATION_ID = cii.pn_location_id)
and (P_EAM_LOCATION_ID is null or P_EAM_LOCATION_ID= eomd.area_id)
and (P_FA_ASSET_ID is null or P_FA_ASSET_ID = fa.asset_id)
and (P_ASSET_CRITICALITY_CODE is null or P_ASSET_CRITICALITY_CODE = cii.asset_criticality_code)
and (P_WIP_ACCOUNTING_CLASS_CODE is null or P_WIP_ACCOUNTING_CLASS_CODE = eomd.accounting_class_code)
and (P_MAINTAINABLE_FLAG is null or P_MAINTAINABLE_FLAG = cii.maintainable_flag)
and (P_OWNING_DEPARTMENT_ID is null or P_OWNING_DEPARTMENT_ID = eomd.OWNING_DEPARTMENT_ID)
and (P_PROD_ORGANIZATION_ID is null or P_PROD_ORGANIZATION_ID = msn_prod.CURRENT_ORGANIZATION_ID)
and (P_EQUIPMENT_ITEM_ID is null or P_EQUIPMENT_ITEM_ID = msi_prod.INVENTORY_ITEM_ID)
and (P_EQP_SERIAL_NUMBER is null or P_EQP_SERIAL_NUMBER = decode(msi_prod.equipment_type,null,null,1,msn_prod.serial_number,null))
and (P_ASSET_CATEGORY_ID is null or p_ASSET_CATEGORY_ID = fa.asset_category_id)
;
INSERT INTO EAM_ASSET_RESULTS_GTT(gen_object_id)
VALUES (asset.gen_object_id);
insert into EAM_ASSET_RESULTS_GTT (gen_object_id)
(select object_id from mtl_object_genealogy
where genealogy_type = 5
and (sysdate between NVL(start_date_active,sysdate-1) and NVL(end_date_active,sysdate+1))
start with parent_object_id = asset.gen_object_id
connect by parent_object_id = prior object_id
and prior genealogy_type = 5
and sysdate between NVL(prior start_date_active,sysdate-1) and NVL(prior end_date_active,sysdate+1)
);