eAM – Asset Number Conversion

                                    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
Recent Posts