/* SELECT invoice_interface_status_code */
/* FROM oe_order_lines Line */
/* WHERE Line.line_id = 2690231 */
/* AND invoice_interface_status_code = 'RFR-PENDING' */
/*
檢查還有哪些異常的資料
SELECT h.order_number,l.line_number||'.'||l.shipment_number line_no,l.ship_from_org_id,l.flow_status_code, l.*
FROM wsh_delivery_details a,
oe_order_lines_all l,
oe_order_headers_all h
WHERE h.header_id=l.header_id
AND a.source_line_id=l.line_id
AND a.RELEASED_STATUS='C'
AND l.FLOW_STATUS_CODE<>'CLOSED'
AND l.org_id=801
AND l.LAST_UPDATE_DATE
*/
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=6611817;
--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 = 1174,
LAST_UPDATE_DATE = SYSDATE
WHERE LINE_ID =:line_id;
DELETE FROM RA_INTERFACE_LINES_ALL
WHERE INTERFACE_LINE_ATTRIBUTE6=TO_CHAR(:line_id);
DELETE FROM RA_INTERFACE_SALESCREDITS_ALL
WHERE INTERFACE_LINE_ATTRIBUTE6=TO_CHAR(: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);
Oe_Invoice_Pub.Interface_Line(6611817,'OEOL',l_result_out,l_return_status); --代LINE_ID
DBMS_OUTPUT.PUT_LINE(TO_CHAR(6611817) ||'+'||l_result_out || '+' || l_return_status); --代LINE_ID
UPDATE OE_ORDER_LINES_ALL
SET OPEN_FLAG='N',
FLOW_STATUS_CODE='CLOSED'
WHERE LINE_ID = :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 (801,543)
AND RI.INTERFACE_LINE_ATTRIBUTE6=:line_id
AND TRX_NUMBER IS NULL;
UPDATE OE_ORDER_LINES_ALL ol
SET ol.shipped_quantity=ol.ordered_quantity
WHERE ol.line_id=:line_id
AND ol.shipped_quantity IS NULL;
COMMIT;
END LOOP;
END;
DELETE MTL_RESERVATIONS
WHERE
RESERVATION_ID=18431033
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/92289/viewspace-626941/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/92289/viewspace-626941/