成本卷积报错：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.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 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 = 2UPDATE 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;