BOM: Enable/Disable Bill Item Deletion Constraint

When we want to delete some times from BOM structure, we may get error that BOM is actively used in open orders and cannot be deleted. There are few constraints which restricts BOM item deletion. Once you get that constraint name, you can disable them temporarily from back-end using SQL queries and enable them again once deletion is complete. Of course it will create problems when the orders are referring them, but who cares when you are really in testing phase and want to delete few items from BOM before going live. This will be handy in such cases.

UPDATE  BOM_DELETE_SQL_STATEMENTS 
SET ACTIVE_FLAG=1
WHERE (SQL_STATEMENT_NAME LIKE 'CMP_CON5');

Set ACTIVE FLAG=1 for Enabling the constraint
Set ACTIVE FLAG=2 for Disabling the constraint
You can get sql_statement_name from the constraint error that you have received.

Configurator: What is CZGOLD instance?

In Oracle Configurator world, CZGOLD terminology is coined very frequently by Oracle or experienced Configurator implementors and new guys are usually confused by this term.

CZGOLD is an instance strategy which many implementors may already be following in their implemention. CZGOLD is an instance where you import models from one BOM instance, do Configuration model development and publish to multiple different instances.

Not a picture perfect, but something similar to --

         <---------- IMPORT BOM -----<------------BOM INSTANCE
        /                                           ^
       /                                            |
CZGOLD/------->----PUBLISH BOM MODEL(Optional)--->-->
      \
       \
        \----->----PUBLISH BOM MODEL--->--ANOTHER BOM INSTANCE1
         \
          \---->----PUBLISH BOM MODEL-->--ANOTHER BOM INSTANCE2
           \
            \-->----PUBLISH BOM MODEL-->--ANOTHER BOM INSTANCE3


Benefit of having a dedicated CZ instance (CZGOLD) verses maintaining BOM/CZ along with your transactional data (Quote/OM) within a single instance:

Its always good to have a separate dedicated instance for CZ, usually referred as CZGOLD.

Configurator model development is different and standalone process. It does not have any need of transactional environment when BOM is already imported to CZ and until testing of the functionality is required in order line.
Configurator development environment will require exposure to testing of Java CX - which has killer ability to do server crash when its not fully tested or due to malfunction of code or inappropriate model setup. Definitely you would not prefer your transactional processing to be affected by model development/testing.

For Configurator development, you may need to expose unix box access, database access to Configurator model/CX developer.

When you have SR/issue in Configurator and Oracle gives fix for it, you may want to test it first in CZ test env, and not directly on the instance where transaction data is getting processed to ensure fix will work for you, and if any problem, you can report back to Oracle.

You will not prefer to affect transaction data processing performance with model development process. Keeping both of them on separate instance will be better utilization of resource and will avoid performance issues due to hardware bottleneck (agree that you need additional CZ instance but its worth doing it).

Usually companies maintain CZGOLD PROD and CZGOLD TEST instance. First do changes on Test instance and then migrate change on Prod. Now R12 Configurator has functionality called model migration, which makes it very easy to migrate model from one instance to another instance. Multiple divisions can work on their own CZGOLD Test instance and then finally migrate the model on CZGOLD Prod instance for model release.

Configurator: Query to get list of published models

In Configurator publication target instance, we can use following query to get list of all models published to this instance which are currently active.

SELECT si.segment1 item_name, 
   mp.publication_id,
   mp.remote_publication_id,
   mp.model_id,
   mp.top_item_id,
   mp.organization_id,
   mp.last_update_date,
   mp.product_key,
   round((sysdate - mp.last_update_date), 2) as days_ago,
   round((sysdate - mp.last_update_date)*24, 2) as hours_ago,
   round((sysdate - mp.last_update_date)*24*60, 2) as mins_ago
FROM cz_model_publications mp,
   mtl_system_items_b si
WHERE 1 =1
   AND mp.top_item_id = si.inventory_item_id
   AND si.organization_id = mp.organization_id
   AND deleted_flag =0
   AND disabled_flag =0
   AND sysdate BETWEEN applicable_from AND applicable_until
   AND export_status ='OK'
   AND object_type ='PRJ'
   AND source_target_flag ='T'  --only target publications
   AND publication_mode ='p'  --list publications in Production model, replace with 't' for Test mode
   --and mp.server_id=1700  --you would need this if multiple sources were publishing to current instance in past
   --and round((sysdate - mp.last_update_date)*24, 2) < 5 --to restrict the result to last N days publications
   --and si.segment1 like 'Laptop55%' --uncomment to get details for specific models
ORDER BY last_update_date desc;

Rename Item and Reflect change in Configurator

In  implementation, there would be scenarios when some items (option class/standard item/model) are named wrongly and need to be corrected. Usually the problem will worsen if the model is already imported into Configurator and there are lot of rules for that particular item. If you delete the item and create new one with correct name, next Configurator Model Refresh will cause issues for existing configurator rules for item. Rule will become invalid because its participant node would have been deleted. But there is a better simple alternate. Check following settings that will be helpful in similar scenarios.

Inventory Profile INV: Updateable Item Name

Oracle documentation says: This profile option controls whether the system enables you to change the item name after you save it. Setting the value to No prevents you from altering the item name after the initial save.

Inventory predefines a value of No for this profile option for all levels upon installation.

This profile option is updateable at all levels.

One more hurdle could be while refreshing the model in Configurator. Usually Configurator does not import/refresh item name change in Configurator Model.

Check following query.

select noupdate 

from cz_xfr_fields
where xfr_group='IMPORT' and dst_field='REF_PART_NBR';

If above query returns 1 means item name change import is not enabled. You can enable it by changing this field to 0 using following query.

update cz_xfr_fields
set noupdate=0
where xfr_group='IMPORT' and dst_field='REF_PART_NBR';

There are more parameters in this table cz_xfr_fields which can be of your interest depending on issue/requirement you have. Check it out by looking at all entries in this table.