有order line手工用sql拋ar的處理

日常往往會碰到訂單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了.