Item Supply/Demand Fom Includes Purchase Orders Which Have Already Been Fully Received and Delivered (Datafix)
适用于：
•Oracle 库存管理版本：11.5.10.2 及以上版本 释放：11.5.10 及以上
•文档中的信息适用于其他平台
•形式：INVIDITM.FMB - 项目信息的定义
•形式：RCVRCERC.FMB—在图形用户界面进入接受交易。
•形式：INVDVDSD.FMB - 查看供应/需求的信息
症状：
供应/需求的物料项目形式包含已经完全接受和交付的采购订单。这导致了规划模块上数据的失真和阻止了项目属性被更改。
目标：
拒绝或接收小部分的供应量
解决方案：
如果是这样的问题先决定运行可以识别的脚本然后再进行数据修改
可识别脚本：
SELECT ms.* FROM MTL_SUPPLY MS,
PO_HEADERS_ALL PH,
PO_LINE_LOCATIONS_ALL PLL
where ph.po_header_id = ms.po_header_id
and pll.line_location_id = ms.po_line_location_id
and ms.quantity < 0.00001
AND nvl(pll.closed_code, 'OPEN') IN ('FINALLY CLOSED', 'CLOSED');
SELECT rs.*
FROM RCV_SUPPLY RS,
PO_HEADERS_ALL PH,
PO_LINE_LOCATIONS_ALL PLL
where ph.po_header_id = rs.po_header_id
and pll.line_location_id = rs.po_line_location_id
and rs.quantity < 0.00001
AND nvl(pll.closed_code, 'OPEN') IN ('FINALLY CLOSED', 'CLOSED');
在数据修改之前将数据备份：
CREATE TABLE ms_backup_table as
SELECT ms.*
FROM MTL_SUPPLY MS,
PO_HEADERS_ALL PH,
PO_LINE_LOCATIONS_ALL PLL
where ph.po_header_id = ms.po_header_id
and pll.line_location_id = ms.po_line_location_id
and ms.quantity < 0.00001
AND nvl(pll.closed_code, 'OPEN') IN ('FINALLY CLOSED', 'CLOSED');
CREATE TABLE rs_backup_table as
SELECT rs.*
FROM RCV_SUPPLY RS,
PO_HEADERS_ALL PH,
PO_LINE_LOCATIONS_ALL PLL
where ph.po_header_id = rs.po_header_id
and pll.line_location_id = rs.po_line_location_id
and rs.quantity < 0.00001
AND nvl(pll.closed_code, 'OPEN') IN ('FINALLY CLOSED', 'CLOSED');
数据修改 删除 无关的/不需要的 供应：
DELETE from mtl_supply
WHERE supply_type_code = 'RECEIVING'
AND supply_source_id IN
( SELECT supply_source_id
FROM ms_backup_table );
DELETE from rcv_supply
WHERE supply_type_code = 'RECEIVING'
AND supply_source_id IN
( SELECT supply_source_id
FROM rs_backup_table );
commit;
如果数据更改运行不止一次（或者如果数据不需要了，备份的表可能被删除，然后再重复使用）这个备份的表的名称(ms_backup_table and rs_backup_table)将被更改。
当前位置：
论坛首页 »
自由讨论区 »
Hadoop，ERP及大数据讨论区
»
发起投票
技术讨论
加好友
发消息
2012-3-9 16:46:43 |
赞(
操作
