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;
进行数据修复。
总结:
在做接口导入的时候 可以直接将这两个脚本写成两个存储过程 放在采购订单导入的包里面 订单创建成功后 调用第一个脚本的存储过程 然后查询 符合条件 在调用第二个脚本的存储过程修复数据。