Cost convolution error: cstpscex. explode_ SC _cost_flags (): 40: ORA-01476: Division 0

Cost convolution request: supply chain cost accumulation-print report

An error is reported after running:

MSG-00000: rollup id = 236403
MSG-00000: Before cstpscex. supply_chain_rollup 10:35:53
MSG-00000: After cstpscex. supply_chain_rollup 2014/10/23 10:35:53
MSG-00000: no loop found
MSG-00000: cstpscex. explode_ SC _cost_flags (): 40: ORA-01476: Division 0
Rep-1825: the trigger before the report returns false.
Rep-0069: Internal error
Rep-57054: In-process job terminated: terminated with error:
Rep-1825: MSG-00000: rollup id = 236403
MSG-00000: Before cstpscex. supply_chain_rollup 10:35:53
MSG-00000: After cstpscex. supply_chain_rollup 2014/10/23 10:35:53
MSG-00000: no loop found
MSG-00000: cstpscex. explode_ SC _cost_flags (): 40: ORA-01476: Division 0
Rep-1825: the trigger before the report returns false.

 

-----------

Find the Metalink and analyze the solution:

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

Supply Chain indented bills of material cost report displays cstpscex. explode_ SC _cost_flags (): 40: ORA-01476 (Documentation ID 1904489.1)

 

To implement the solution, Please execute the following steps:

1. Download and review the README and pre-requisites for Patch 18632885: r12.bom. c

2. Ensure that you have taken a backup of your system before applying the Recommended Patch.

3. Apply the patch in a test environment.

4. Confirm the following file versions:
Cstscexb. Pls 120.12.1201511.14
Cstscexs. Pls 120.0.1201511.4

You can use the commands like the following:
Strings-A $ bom_top/patch/115/SQL/cstscexs. Pls | grep '$ header'

5. retest the issue.

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

The current system version is:

 

[[Email protected] ZHS] $ strings-A $ bom_top/patch/115/SQL/cstscexs. Pls | grep '$ header'
/* $ Header: cstscexs. Pls 120.0.1201511.1 2008/07/24 17:25:17 appldev ship $ */
[[Email protected] ZHS] $ strings-A $ bom_top/patch/115/SQL/cstscexb. Pls | grep '$ header'
/* $ Header: cstscexb. Pls 120.12.1201511.6 2010/10/01 07:31:26 pbasrani ship $ */
[[Email protected] ZHS] $

 

Apparently the version is too low

Think of the status quo, patching is troublesome, and... Not to mention

Check if there is any other way. Check Metalink again. There is a way to fix the data:

Additional debug message in log for error: cstpscex. explode_ SC _cost_flags (): 40: ORA-01476: divisor is equal to zero (Document ID 1602932.1)

 

This is the case in one section.

@ The following datafix (data_fix_script. SQL) will address the existing phantom components that can't be manually adrressed in case the number is huge. the data fix script will only just uncheck the components having zero qty not supported ded in cost roll up

@ For phantom components.

 

After downloading the data to fix the SQL statement, the content is as follows:

/** Description :*             This script is for 2 purpose*             1. Check phantom item used in bom with quantity =0*             2. Set such kind of bom component as NOT included in cost rollup.*/--step 1 CREATE back up tableDROP TABLE BOM_COMPONENTS_B_bak;CREATE TABLE BOM_COMPONENTS_B_bakASSELECT bic.*  FROM bom_parameters bp, BOM_COMPONENTS_B bic , BOM_BILL_OF_MATERIALS bom, mtl_system_items msiWHERE bp.organization_id = &p_org_id AND  bp.use_phantom_routings = 1 AND  bp.organization_id = bom.organization_id AND  bom.common_bill_sequence_id = bic.bill_sequence_id AND  msi.organization_id = bom.organization_id AND  msi.inventory_item_id = bic.component_item_id AND  decode(nvl(BIC.wip_supply_type, nvl( MSI.wip_supply_type, 1)), 6, 1, 2) = 1 AND  bic.component_quantity = 0 AND  bic.include_in_cost_rollup = 1 ;--below sql is used to find out each phantom item with 0 quantity ‘s assembly item and phantom itemSELECT bom.organization_id, msi1.inventory_item_id, msi1.segment1 assembly_item_name, msi.inventory_item_id, msi.segment1 phantom_item_name  FROM bom_parameters bp, BOM_COMPONENTS_B bic , BOM_BILL_OF_MATERIALS bom, mtl_system_items msi, mtl_system_items msi1WHERE bp.organization_id = &p_org_id AND  bp.use_phantom_routings = 1 AND  bp.organization_id = bom.organization_id AND  bom.common_bill_sequence_id = bic.bill_sequence_id AND  msi.organization_id = bom.organization_id AND  msi.inventory_item_id = bic.component_item_id AND  msi1.organization_id = bom.organization_id AND  msi1.inventory_item_id = bom.assembly_item_id AND  decode(nvl(BIC.wip_supply_type, nvl( MSI.wip_supply_type, 1)), 6, 1, 2) = 1 AND  bic.component_quantity = 0 AND  bic.include_in_cost_rollup = 1 ;--step 2, update include_in_cost_rollup = 2UPDATE BOM_COMPONENTS_B SET include_in_cost_rollup = 2WHERE (BILL_SEQUENCE_ID, COMPONENT_SEQUENCE_ID) IN (   SELECT BILL_SEQUENCE_ID, COMPONENT_SEQUENCE_ID FROM  BOM_COMPONENTS_B_bak) ;COMMIT;

After checking the above Code and analyzing it, the number of components in the repair list is 0, and the components are also included in the list (sub-assembly ), you need to remove the "included in the accumulative cost. This can also be completed through the interface, without the need for this code.

 

The convolution request is submitted again!

 

Cost convolution error: cstpscex. explode_ SC _cost_flags (): 40: ORA-01476: Division 0