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

运行后报一下错误:

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;

查看了以上代码,分析了一下,无非就是修复清单中组件数量是0,且组件也是有清单的物料(子装配件),需要把“包括在累计成本中”的勾去掉即可。这个也完全可以通过界面来操作完成,无需通过这段代码也可以完成。

 

再次提交卷积请求,成功!