ITPub博客

首页 > 数据库 > Oracle > 銷貨後其狀態是SHIPPED,到不了INTERFACE的。

銷貨後其狀態是SHIPPED,到不了INTERFACE的。

原创 Oracle 作者:longwansheng 时间:2011-12-30 10:14:01 0 删除 编辑

這次CL1廠遇到一筆銷貨 後在銷貨畫面一直是SHIPPED的,查INTERFACE與TRANSACTION MOVE ORDER均無問題,ITEM屬性也沒變動。

Order Management Interface - SRS,這個REQUEST的VIEW LOG中提示:

Action: Contact your Oracle support representative.
Error msg: Your change invalidates the Customer Item Number on the Line.
Failed to interface Batch 1495700 to Order Management because API interface_he
ader_to_OM failed

[@more@]

判斷可能跟CUST ITEM與INV ITEM的對應關系有異常

用下面SQL檢核發發現有兩筆的CUST ITEM為NULL。

select wa.delivery_id,oh.order_number,ol.ordered_item,
GOBO_GET_CUSTOMER_ITEM(ol.inventory_ITEM_ID,oh.sold_to_org_id) sku_no,
mi.segment1 item,line_number||'.'||shipment_number line_no,
ol.ordered_quantity,ol.shipped_quantity,ol.flow_status_code,
wd.*
from wsh_delivery_assignments wa,
wsh_delivery_details wd,
mtl_system_items_b mi,
oe_order_headers_all oh,
oe_order_lines_all ol
where wa.delivery_id=9300190
and oh.header_id=ol.header_id
and mi.inventory_item_id=ol.inventory_item_id
and mi.organization_id=ol.ship_from_org_id
and wd.delivery_detail_id=wa.delivery_detail_id
and ol.line_id=wd.source_line_id

查這兩筆的CUST ITEM與INV ITEM的對應關系,有建但ACTIVE中的V沒有打上。重新V上,再跑下面三個REQUEST即可。

Order Management Interface - SRS

Inventory Interface - SRS

Interface Trip Stop - SRS

附上:FUNCTION:GOBO_GET_CUSTOMER_ITEM

CREATE OR REPLACE FUNCTION GOBO_GET_CUSTOMER_ITEM(v_item_id in number,v_customer_id in number) return varchar2 is
cursor c1 is (select distinct b.CUSTOMER_ITEM_NUMBER
from MTL_CUSTOMER_ITEM_XREFS_V b
where 1=1
--AND b.CUSTOMER_ITEM_ID=V_CUST_ITEM_ID
AND b.INVENTORY_ITEM_ID=v_ITEM_ID
and b.CUSTOMER_ID=v_CUSTOMER_ID
and b.INACTIVE_FLAG='N'
and b.RANK = ( SELECT MIN(a1.RANK) FROM MTL_CUSTOMER_ITEM_XREFS_V a1
WHERE a1.CUSTOMER_ID =b.CUSTOMER_ID
AND a1.INVENTORY_ITEM_ID = b.INVENTORY_ITEM_ID
AND a1.INACTIVE_FLAG = 'N')
);
prno varchar2(50);
result varchar2(200);
begin
open c1;
loop
fetch c1 into prno;
exit when c1%notfound ;
if result is null then
result:=prno;
else
result:=result||','||prno;
end if;
end loop;
close c1;

IF result IS NULL THEN
BEGIN
SELECT SEGMENT1
INTO result
FROM MTL_SYSTEM_ITEMS_B
WHERE ORGANIZATION_ID=14
AND INVENTORY_ITEM_ID= v_ITEM_ID;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END IF;

return result;
exception
when no_data_found then
return '';
when others then
return '';
end;
/

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/92289/viewspace-1056992/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2007-12-12

  • 博文量
    162
  • 访问量
    741195