銷貨後訂單LINE還是BOOKED的處理

最近新組織上線後OM模組好多異常.

1: 開單人員沒有SCHEDULE(或SCHEDULE SHIP DATE在當前日期前的),造成銷貨人員無法撿料與銷貨的

做UNSCHEDULE/SCHEDULE即可.

2: IR-ISO的,WARKHOURSE沒ASSIGNMENT對分廠別或撿料時沒有選對庫別,造成撿料不成功或銷貨後一直是SHIPPED無法變成INTERFACED的.

處理方法一般為查INV INTERFACE,TRANSACTION PENDDING,MOVE ORDER TRANSACTION;還有就是查ORDER LINE有沒有OPEN MESSAGE等.本廠或入庫庫別不對的,在INV INTERFACE中選對庫別與廠別,重RUN OM的INTERFACE即可.

3: 銷貨後訂單LINE還是BOOKED的,處理方法列於後面

[@more@]

/* 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);

DBMS_OUTPUT.PUT_LINE(TO_CHAR(6611817) ||'+'||l_result_out || '+' || l_return_status);

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 --6611541 6611817
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;