eAM - Asset
Number Conversion
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
