Description:
The eAM Asset Number Open Interface enables you to import Asset Numbers into eAM, using a batch process. Optionally import Asset Number attributes. You can create new Asset Numbers and attributes, or update existing Asset Numbers and attributes.
Asset Number:
An asset number uniquely identifies each asset.
Possible Validations:
- Asset Number Mandatory.
- Asset Serial Number Mandatory.
- Asset Group Mandatory.
- Asset Category Mandatory.
- Owning Department Mandatory.
- Criticality Code Mandatory.
- Area Mandatory.
- WIP Accounting Class Mandatory.
Code for reference:
PROCEDURE XXX_validate
IS
CURSOR cur_main
IS
SELECT *
FROM xxx_stage —-Select all the records to be processed
l_organization_id NUMBER;
l_inventory_item_id NUMBER;
l_asset_count NUMBER;
l_category_id NUMBER;
l_department_id NUMBER;
l_area_id NUMBER;
l_criticality_code VARCHAR2 (10);
l_class_code VARCHAR2 (100);
l_user_id NUMBER;
lc_cat_seg VARCHAR2 (245);
BEGIN
FOR I IN cur_main
LOOP
—Validate Category Set
BEGIN
SELECT category_concat_segs
INTO lc_cat_seg
FROM mtl_item_categories_v mic, mtl_system_items_b msi
WHERE mic.category_set_name = :set_name
AND mic.inventory_item_id = msi.inventory_item_id
AND mic.organization_id = msi.organization_id
AND msi.segment1 = i.asset_group
AND msi.organization_id =i.organization_code);
END;
–To Validate Asset Group
BEGIN
SELECT inventory_item_id
INTO l_inventory_item_id
FROM mtl_system_items_b
WHERE segment1 = i.asset_group
AND organization_id = l_organization_id;
END;
–Validate Asset category
IF i.asset_category IS NOT NULL
THEN
BEGIN
SELECT category_id
INTO l_category_id
FROM mtl_categories_v
WHERE UPPER (category_concat_segs)= (i.asset_category)
AND structure_name = i.name
AND enabled_flag = ‘Y’;
END;
END IF;
–Validate owning department
BEGIN
SELECT department_id
INTO l_department_id
FROM bom_departments
WHERE UPPER (department_code) = (i.owning_department)
AND organization_id = l_organization_id
AND disable_date IS NULL;
END;
IF i.criticality IS NOT NULL
THEN
BEGIN
SELECT lookup_code
INTO l_criticality_code
FROM fnd_lookup_values
WHERE lookup_type = ‘MTL_EAM_ASSET_CRITICALITY’
AND meaning = i.criticality
AND enabled_flag = ‘Y’;
End;
END IF;
–Validate Area
IF i.area IS NOT NULL
THEN
BEGIN
SELECT location_id
INTO l_area_id
FROM mtl_eam_locations
WHERE location_codes = i.area
AND organization_id = l_organization_id
AND end_date IS NULL;
END;
END IF;
–Validate WIP Accounting Classe
BEGIN
SELECT class_code
INTO l_class_code
FROM wip_accounting_classes
WHERE class_code = i.wip_accounting_class
AND organization_id = l_organization_id;
END;
IF l_error_message IS NULL
— UPDATE stage table with process flag as ‘V.’
END IF;
END LOOP;
COMMIT;
END validate_asset_data;
PROCEDURE XXX_import (
errbuff OUT VARCHAR2,
retcode OUT NUMBER,
p_batch_no IN NUMBER
)
IS
CURSOR cur_main
IS
SELECT *
FROM XXX_STG
WHERE process_flag = ‘V’ AND batch_no = p_batch_no;
l_error VARCHAR2 (1000);
l_user_id NUMBER;
BEGIN
FOR i IN cur_main
LOOP
l_error := NULL;
BEGIN
INSERT INTO mtl_eam_asset_num_interface
(inventory_item_id, serial_number, last_update_date,
last_updated_by, creation_date, created_by, descriptive_text,
wip_accounting_class_code, maintainable_flag,
owning_department_id, fa_asset_id,
eam_location_id, asset_criticality_code,
category_id, interface_header_id, batch_id, organization_code,
fa_asset_number, location_codes, process_flag, import_mode, import_scope, owning_department_code, asset_criticality_id, instance_number, operational_log_flag)
VALUES (i.inventory_item_id, i.asset_serial_number, SYSDATE,
l_user_id, SYSDATE, l_user_id, i.asset_description,
i.class_code, ‘Y’, i.department_id, i.category_id,
mtl_eam_asset_num_interface_s.NEXTVAL, i.organization_code,
i.finance_asset_number, i.area, ‘P’, 1,
NULL, i.criticality_code, i.asset_number, ‘Y’
);
END;
END LOOP;
END;
Run the Standard Program “ Import Asset Number.”
Base Table :
- MTL_SERIAL_NUMBERS
- CSI_ITEM_INSTANCES
Sample Query to get the Asset Number Details:
SELECT (SELECT organization_code
FROM org_organization_definitions
WHERE organization_id = last_vld_organization_id) “Inventory Org”,
(SELECT DISTINCT segment1
FROM mtl_system_items_b
WHERE inventory_item_id =
c.inventory_item_id)
“Asset Groups”,
instance_number “Asset Number”,
instance_description “Asset Number Description”,
c.serial_number “Asset Serial Number”,
(SELECT department_code
FROM bom_departments
WHERE department_id =
(SELECT owning_department_id
FROM eam_org_maint_defaults
WHERE object_id = c.instance_id))
“owning department,”
(SELECT meaning
FROM fnd_lookup_values
WHERE lookup_type = ‘MTL_EAM_ASSET_CRITICALITY’
AND enabled_flag = ‘Y’
AND lookup_code = c.asset_criticality_code) “criticality”,
(SELECT accounting_class_code
FROM eam_org_maint_defaults
WHERE object_id = c.instance_id) “accounting_class_code”,
(SELECT location_codes
FROM mtl_eam_locations
WHERE location_id = (SELECT area_id
FROM eam_org_maint_defaults
WHERE object_id = c.instance_id)) “area”
FROM csi_item_instances c, mtl_serial_numbers msn
WHERE c.serial_number = msn.serial_number
AND c.inventory_item_id = msn.inventory_item_id
ORDER BY instance_number.
Summary:
In the above classification steps to generate asset number conversion in oracle.
Queries?
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions.
