成本卷积报错：CSTPSCEX.explode_sc_cost_flags():40:ORA-01476: 除数为 0

成本卷积请求：供应链成本累计 - 打印报表

运行后报一下错误：

MSG-00000: Rollup ID = 236403
MSG-00000: Before CSTPSCEX.supply_chain_rollup 2014/10/23 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: 除数为 0
REP-1825: 报表前触发器返回 FALSE。
REP-0069: 内部错误
REP-57054: In-process job terminated:Terminated with error:
REP-1825: MSG-00000: Rollup ID = 236403
MSG-00000: Before CSTPSCEX.supply_chain_rollup 2014/10/23 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: 除数为 0
REP-1825: 报表前触发器返回 FALSE。

-----------

查找metalink 后分析，给出的解决方案：

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

Supply Chain Indented Bills Of Material Cost Report Displays CSTPSCEX.explode_sc_cost_flags():40:ORA-01476 (文档 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.12010000.14
CSTSCEXS.pls 120.0.12010000.4

You can use the commands like the following:
strings -a $BOM_TOP/patch/115/sql/CSTSCEXS.pls |grep '$Header'

5. Retest the issue.

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

目前系统的版本为：

[apptest@vis ZHS]$ strings -a $BOM_TOP/patch/115/sql/CSTSCEXS.pls |grep '$Header'
/* $Header: CSTSCEXS.pls 120.0.12010000.1 2008/07/24 17:25:17 appldev ship $ */
[apptest@vis ZHS]$ strings -a $BOM_TOP/patch/115/sql/CSTSCEXB.pls |grep '$Header'
/* $Header: CSTSCEXB.pls 120.12.12010000.6 2010/10/01 07:31:26 pbasrani ship $ */
[apptest@vis ZHS]$

显然版本过低

想到现状，打补丁麻烦，且。。。言不多说

看看有没有其他办法，再查 metalink，有一个给出数据修复的办法：

Additional debug message in log for error: CSTPSCEX.explode_sc_cost_flags():40:ORA-01476: divisor is equal to zero (文档 ID 1602932.1)

其中一段是这么说的

@@The following datafix (data fix script will only just uncheck the components having zero qty not included in cost roll up

@@ for phantom components.

下载了这个数据修复sql,内容如下：

/*
* 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 table
DROP TABLE BOM_COMPONENTS_B_bak;
CREATE TABLE BOM_COMPONENTS_B_bak
AS
SELECT bic.*
  FROM bom_parameters bp, BOM_COMPONENTS_B bic , BOM_BILL_OF_MATERIALS bom, mtl_system_items msi
WHERE 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 item
SELECT 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 msi1
WHERE 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 = 2

UPDATE BOM_COMPONENTS_B
 SET include_in_cost_rollup = 2
WHERE (BILL_SEQUENCE_ID, COMPONENT_SEQUENCE_ID) IN (
   SELECT BILL_SEQUENCE_ID, COMPONENT_SEQUENCE_ID FROM  BOM_COMPONENTS_B_bak
) ;
COMMIT;