日常往往會碰到訂單line已closed與shipped.但沒有立ar的.
這時我們可以用sql重拋.
[@more@]declare
l_result_out VARCHAR2(30);
l_return_status VARCHAR2(30);
cursor c1 is
SELECT line_id
FROM OE_ORDER_LINES_ALL
where line_id=2637320;
--line_id in (2704842,2750637);
--WHERE FLOW_STATUS_CODE = 'SHIPPED';
begin
for ii in c1 loop
UPDATE OE_ORDER_LINES_ALL
SET INVOICED_QUANTITY = NULL,
INVOICE_INTERFACE_STATUS_CODE = NULL,
OPEN_FLAG='N',
FLOW_STATUS_CODE = 'CANCELLED',
LAST_UPDATED_BY = xxxx,
LAST_UPDATE_DATE = SYSDATE
WHERE LINE_ID =ii.line_id;
DELETE FROM RA_INTERFACE_LINES_ALL
WHERE INTERFACE_LINE_ATTRIBUTE6=TO_CHAR(ii.line_id);
DELETE FROM RA_INTERFACE_SALESCREDITS_ALL
WHERE INTERFACE_LINE_ATTRIBUTE6=TO_CHAR(ii.line_id);
commit;
Oe_Invoice_Pub.Interface_Line(ii.line_id,'OEOL',l_result_out,l_return_status);
dbms_output.put_line(to_char(ii.line_id) ||'+'||l_result_out || '+' || l_return_status);
UPDATE OE_ORDER_LINES_ALL
SET OPEN_FLAG='N',
FLOW_STATUS_CODE='CLOSED'
WHERE LINE_ID = ii.line_id;
UPDATE RA_INTERFACE_LINES_ALL RI
SET TRX_NUMBER = (SELECT DISTINCT DELIVERY_ID
FROM WSH_DELIVERABLES_V WSH
WHERE WSH.SOURCE_HEADER_NUMBER = RI.SALES_ORDER
AND WSH.SOURCE_LINE_ID = RI.INTERFACE_LINE_ATTRIBUTE6
)
WHERE ORG_ID in (xxxxxxx)
AND TRX_NUMBER IS NULL;
Update OE_ORDER_LINES_ALL ol
set ol.shipped_quantity=ol.ordered_quantity
where ol.line_id=ii.line_id
and ol.shipped_quantity is null;
commit;
end loop;
end;
--造成order line沒有自動拋ar的原因有好多,比如手動改了workflow.
手工拋到ar的interfaced後,需ar人員撈成ar後,在order line的additional line information中的invoices/credit memos就能看到ar no了.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/92289/viewspace-908167/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/92289/viewspace-908167/