Customer Item and Customer Item Cross Reference Interface Or Conversion in Oracle Apps

Overview:

This document help you to work on interface or conversion of Customer Item and Customer Item Cross Reference.
           I have used below script for conversion of Customer Item and Customer Item Cross reference, migrate data from 11i to R12.2.7

1) Customer Item Interface:    

    Interface Table:

      1) MTL_CI_INTERFACE

    Oracle import standard program:

                            Import Customer Items
 

    Base Tables are:

      1) MTL_CUSTOMER_ITEMS
 

    Mandatory and Important Columns

  * MTL_CI_INTERFACE :

        1)  process_flag
        2)  process_mode
        3)  lock_flag
         4)  last_updated_by
 5)  last_update_date
        6)  created_by
         7)  creation_date
         8)  transaction_type
          9)  customer_id
        10) customer_category_code
        11) address_id
        12) customer_item_number
        13) item_definition_level
        14) commodity_code_id
        15) master_container
        16) inactive_flag
        17) customer_item_desc
18) container_item_org_id
        19) master_container_item_id
        20) detail_container_item_id

Validate all mandatory fields and insert valid records in Interface tables:

--1) Customer Number:

    SELECT cust_account_id customer_id
            INTO ln_customer_id
            FROM hz_parties hp, hz_cust_accounts hca
            WHERE hca.party_id = hp.party_id
                  AND hca.account_number = '334470'
                  AND hca.status = 'A';

    --2) Address or Location:

SELECT hcsua.cust_acct_site_id address_id
        INTO ln_address_id
        FROM hz_parties hp,
             hz_party_sites hps,
             hz_locations hl,
             hz_cust_accounts hca,
             hz_cust_acct_sites_all hcasa,
             hz_cust_site_uses_all hcsua
       WHERE hcasa.cust_account_id = hca.cust_account_id
            AND hcasa.party_site_id = hps.party_site_id
            AND hca.party_id = hps.party_id
            AND hps.party_id = hp.party_id
            AND hps.location_id = hl.location_id
            AND hcasa.cust_acct_site_id = hcsua.cust_acct_site_id
            AND hca.status = 'A'
            AND hcasa.status = 'A'
            AND hp.status = 'A'
            AND hps.status = 'A'
            AND hcsua.status = 'A'
            AND hcsua.site_use_code = 'SHIP_TO'
            AND hcasa.cust_account_id = 1324 --this is ln_customer_id
            AND hcsua.location = 'NS5921';

    --3) Master Container:

        SELECT inventory_item_id
        INTO ln_master_container_item_id
        FROM mtl_system_items_b
        WHERE enabled_flag = 'Y'
              AND (end_date_active IS NULL OR end_date_active > SYSDATE)
              AND segment1 = 'COMP00712'
      AND organization_id = 234;

    --4) Detail Container:

SELECT inventory_item_id
        INTO ln_detail_container_item_id
        FROM mtl_system_items_b
        WHERE enabled_flag = 'Y'
             AND (end_date_active IS NULL OR end_date_active > SYSDATE)
             AND segment1 = 'LAP00912'
     AND organization_id = 234;

    * Insert data in Interface table: MTL_CI_INTERFACE

        INSERT INTO mtl_ci_interface
                     (process_flag,
                    process_mode,
                    lock_flag,
                      last_updated_by,
                    last_update_date,
                    created_by,
                      creation_date,
                    transaction_type,
                      customer_id,
                      customer_category_code,
                      address_id,
                      customer_item_number,
                      item_definition_level,
                      commodity_code_id,
                      master_container,
                      inactive_flag,
                      customer_item_desc,
                    container_item_org_id,
                      master_container_item_id,
                      detail_container_item_id )
    VALUES ('1' --process_flag
                   , 1   --process_mode
                   , 'N'  --lock_flag
                   , fnd_global.user_id --last_updated_by
   , SYSDATE --last_update_date
   , fnd_global.user_id --created_by
                   , SYSDATE  --creation_date
                   , 'CREATE' --transaction_type
                   , ln_customer_id --customer_id
                   , lc_customer_category_code  --customer_category_code
                   , ln_address_id --address_id
                   , lc_customer_item_number --customer_item_number
                   , lc_item_definition_level --item_definition_level
                   , ln_commodity_code_id --commodity_code
                   , lc_master_container --master_container
                   , lc_inactive_flag --inactive_flag
                   , lc_customer_item_desc --customer_item_desc
   , lc_master_org --container_item_org_id
                   , ln_master_container_item_id --master_container_item_id
                   , ln_detail_container_item_id --detail_container_item_id
                  );

    --Check the values in interface tables:
  SELECT * FROM mtl_ci_interface
      WHERE last_update_date LIKE sysdate
           AND process_flag = '1';

    -- Run Standard program for insert data in Base tables from interface tables:

          Responsibility: Order Management Super User
          Standard Program Name: Import Customer Items
          Navigation : Order Management Super User → Reports → Run → Import Customer Items

        The parameters for the concurrent program are
          1. Abort On Error →  Mandatory.
          2. Delete Record  →  Mandatory.

    -- If the import program completes as normal then check values in Base table:
       SELECT * FROM mtl_customer_items
       WHERE last_update_date LIKE sysdate;
 

** Typical errors:

   a) I got error "fetch more than one records" at Address value level because I didn't take site_use_code = 'SHIP_TO' condition in validation code.

--------------------------------------------------------------------------------------------------------------

2) Customer Item Cross References

     

    Interface Table:

      1) MTL_CI_XREFS_INTERFACE

     Oracle import standard program:   

                              Import Customer Item Cross References
 

    Base Tables are:

      1) MTL_CUSTOMER_ITEM_XREFS
 

    Mandatory and Important Columns

  * MTL_CI_XREFS_INTERFACE :
           1)  Process_flag
            2)  Process_mode
            3)  Lock_flag
            4)  Last_updated_by
            5)  Last_update_date
            6)  Created_by
            7)  Creation_date
            8)  Transaction_type
            9)  Customer_id
          10) Customer_category_code
          11) Address_id
          12) Customer_item_number
          13) Item_definition_level
          14) Inventory_item_id
          15) Master_organization_id
          17) Preference_number
          18) Inactive_flag

Validate all mandatory fields and insert valid records in Interface tables:

--1) Customer Number:

         SELECT cust_account_id customer_id
          INTO ln_customer_id
          FROM hz_parties hp, hz_cust_accounts hca
          WHERE hca.party_id = hp.party_id
                AND hca.account_number = '334470'
                AND hca.status = 'A';

    --2) Address or Location:

         SELECT hcsua.cust_acct_site_id address_id
          INTO ln_address_id
          FROM hz_parties hp,
                       hz_party_sites hps,
                       hz_locations hl,
                       hz_cust_accounts hca,
                       hz_cust_acct_sites_all hcasa,
                       hz_cust_site_uses_all hcsua
       WHERE hcasa.cust_account_id = hca.cust_account_id
             AND hcasa.party_site_id = hps.party_site_id
             AND hca.party_id = hps.party_id
            AND hps.party_id = hp.party_id
            AND hps.location_id = hl.location_id
            AND hcasa.cust_acct_site_id = hcsua.cust_acct_site_id
            AND hca.status = 'A'
            AND hcasa.status = 'A'
            AND hp.status = 'A'
            AND hps.status = 'A'
            AND hcsua.status = 'A'
            AND hcsua.site_use_code = 'SHIP_TO'
            AND hcasa.cust_account_id = 1324 --this is ln_customer_id
            AND hcsua.location = 'NS5921';

    --3) Master Container:

  SELECT inventory_item_id
          INTO ln_master_container_item_id
          FROM mtl_system_items_b
          WHERE enabled_flag = 'Y'
               AND (end_date_active IS NULL OR end_date_active > SYSDATE)
               AND segment1 = 'COMP00712'
              AND organization_id = 234;

    --4) Detail Container:

         SELECT inventory_item_id
          INTO ln_detail_container_item_id
          FROM mtl_system_items_b
         WHERE enabled_flag = 'Y'
              AND (end_date_active IS NULL OR end_date_active > SYSDATE)
              AND segment1 = 'LAP00912'
             AND organization_id = 234;

    --5) Customer Item Number:

          SELECT customer_item_number
         INTO lc_customer_item_number
         FROM mtl_customer_items
         WHERE customer_item_number = 'AS0343'
               AND customer_id = 1234
AND inactive_flag = 'N';

    --6) Inventory Item (This is cross reference item or internal item)

          SELECT inventory_item_id
          INTO ln_inventory_item_id
          FROM mtl_system_items_b
          WHERE segment1 = 'PEN00989'
               AND organization_id = 235
               AND enabled_flag = 'Y'
               AND (end_date_active IS NULL OR end_date_active >SYSDATE)

    * Insert data in Interface table: MTL_CUSTOMER_ITEM_XREFS

        INSERT INTO mtl_ci_xrefs_interface
                                  (Process_flag
                               ,Process_mode
                              ,Lock_flag
                              ,Last_updated_by
                              ,Last_update_date
                              ,Created_by
                              ,Creation_date
                             ,Transaction_type
                             ,Customer_id
                              ,Customer_category_code
                             ,Address_id
                              ,Customer_item_number
                             ,Item_definition_level
                             ,Inventory_item_id
                             ,Master_organization_id
                             ,Preference_number
                             ,Inactive_flag )
                VALUES ('1'   --process_flag
                                , 1     --process_mode
                                , 'N'   --Lock_flag
                                , fnd_global.user_id   --Last_updated_by
, SYSDATE              --Last_update_date
, fnd_global.user_id  --Created_by
                                , SYSDATE              --Creation_date
, 'CREATE'        --transaction_type
                                , p_customer_id --customer_id
                                , p_customer_category_code  --customer_category_code
                                , p_address_id             --address_id
                                , p_customer_item_number   --customer_item_number
                                , p_item_definition_level  --item_definition_level
                                , p_inventory_item_id      --inventory_item_id
                                , p_master_organization_id --master_organization_id
                                , p_preference_number      --preference_number (RANK)
                                , p_inactive_flag          --inactive_flag
                                );

    --Check the values in interface tables:

            SELECT * FROM mtl_ci_xrefs_interface
            WHERE last_update_date LIKE sysdate
                  AND process_flag = '1';

    -- Run Standard program for insert data in Base tables from interface tables:

          Responsibility: Order Management Super User
          Standard Program Name: Import Customer Items
          Navigation : Order Management Super User → Reports → Run → Import Customer Item Cross References

        The parameters for the concurrent program are
          1. Abort On Error →  Mandatory.
          2. Delete Record  →  Mandatory.

    -- If the import program completes as normal then check values in Base table:

   SELECT * FROM mtl_customer_item_xrefs
   WHERE last_update_date LIKE sysdate;
 

** Typical errors: