ORACLE MASTERMINDS

                                    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