oracle po订单变更,Oracle EBS PO 采购订单接口 创建后 点击发运后 未修改任何数据 需要保存...

Oracle EBS PO 采购订单接口 创建后 点击发运后 未修改任何数据 需要保存

现象：

Oracle EBS PO 采购订单接口创建的订单 点击发运后 未修改任何数据 需要保存

测试环境：

Oracle EBS R12.1.3

oracle 官方BUG 不能进行修复 只能进行数据修复

oracle 官方提供两个数据修复脚本 matelink: (Doc ID 943681.1)

先执行下面脚本：

DECLARE

CURSOR non_releases_data IS

SELECT pla.item_id,pos.ship_to_organization_id,pha.vendor_id,pha.vendor_site_id,pos.line_location_id

FROM po_line_locations_all pos,po_lines_all pla,po_headers_all pha

WHERE pos.po_line_id=pla.po_line_id

AND pos.po_header_id=pha.po_header_id

AND pos.po_release_id is NULL

AND pos.country_of_origin_code IS NULL

AND pos.shipment_type IN ('STANDARD','PLANNED')

AND pha.DOCUMENT_CREATION_METHOD='PDOI';

x_item_id number;

x_ship_to_org_id number;

x_vendor_id number;

x_vendor_site_id number;

x_shipment_type VARCHAR2(25);

x_country_of_origin_code varchar2(2);

x_line_location_id NUMBER;

rowsupdated NUMBER;

BEGIN

DELETE po_session_gt;

OPEN non_releases_data;

rowsupdated:=0;

LOOP

FETCH non_releases_data INTO

x_item_id,x_ship_to_org_id,x_vendor_id,x_vendor_site_id,x_line_location_id;

EXIT WHEN non_releases_data%NOTFOUND;

rowsupdated:=rowsupdated+1;

po_coo_s.get_default_country_of_origin

(x_item_id,x_country_of_origin_code);

IF (x_country_of_origin_code IS NOT NULL) the

INSERT INTO po_session_gt(num1,char1,num2) values

(x_line_location_id,x_country_of_origin_code,7516875);

END IF;

END LOOP;

Close non_releases_data;

EXCEPTION

WHEN OTHERS THEN

ROLLBACK;

dbms_output.put_line('An exception occurred.');

dbms_output.put_line('Please contact Oracle support.');

END;

再查询下面脚本：

SELECT Count(*) FROM po_session_gt WHERE num2=7516875;

再执行下面脚本：

DECLARE

CURSOR non_releases_data IS

SELECT pla.item_id,pos.line_location_id FROM po_line_locations_all pos,po_headers_all pha WHERE pos.po_line_id=pla.po_line_id AND pos.po_header_id=pha.po_header_id AND pos.po_release_id is NULL AND pos.country_of_origin_code IS NULL AND pos.shipment_type IN ('STANDARD','PLANNED') AND pha.DOCUMENT_CREATION_METHOD='PDOI';

x_item_id number;

x_ship_to_org_id number;

x_vendor_id number;

x_vendor_site_id number;

x_shipment_type VARCHAR2(25);

x_country_of_origin_code varchar2(2);

x_line_location_id NUMBER;

rowsupdated NUMBER;

BEGIN DELETE po_session_gt;

OPEN non_releases_data;

rowsupdated:=0;

LOOP

FETCH non_releases_data INTO

x_item_id,x_line_location_id;

EXIT WHEN non_releases_data%NOTFOUND;

rowsupdated:=rowsupdated+1;

po_coo_s.get_default_country_of_origin

(x_item_id,x_country_of_origin_code);

UPDATE po_line_locations_all SET country_of_origin_code= x_country_of_origin_code WHERE line_location_id=x_line_location_id;

END LOOP;

Close non_releases_data;

EXCEPTION

WHEN OTHERS THEN

ROLLBACK;

dbms_output.put_line('An exception occurred.');

dbms_output.put_line('Please contact Oracle support.');

END;

进行数据修复。

总结：

在做接口导入的时候 可以直接将这两个脚本写成两个存储过程 放在采购订单导入的包里面 订单创建成功后 调用第一个脚本的存储过程 然后查询 符合条件 在调用第二个脚本的存储过程修复数据。