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
No comments:
Post a Comment