2008年11月27日 星期四

Drop Ship Order - Requisition Import 失敗

最近遇到Drop Ship的訂單,在開出PR時會失敗,此前的模組負責人都是請user重新開立,這次模組換人維護後,才知道user一直有這個困擾,查了一下metalink後,找到了解決方法。

1.利用SQL查詢出Requisition Import的原因:

select pri.transaction_id req_tranx_id
, msi.segment1 item_number
, oeh.order_number sales_order
, oel.LINE_NUMBER
, pri.process_flag process_flag
, pri.request_id conc_request_id
, poi.error_message
, ipr.ORGANIZATION_CODE
from mtl_system_items_b msi,
mtl_parameters ipr,
po_interface_errors poi,
oe_drop_ship_sources oed,
po_requisitions_interface_all pri,
oe_order_headers_all oeh,
oe_order_lines_all oel
where oeh.header_id = oed.header_id and
oel.header_id = oed.header_id and
oel.line_id = oed.line_id and
pri.transaction_id = poi.interface_transaction_id and
pri.interface_source_line_id = oed.drop_ship_source_id and
pri.DESTINATION_ORGANIZATION_ID = msi.ORGANIZATION_ID and
pri.item_id = msi.inventory_item_id and
msi.organization_id = ipr.ORGANIZATION_ID and
oeh.order_number = :p_order_number

image

2.修正問題後(一般都是漏掉採購員等),update UPDATE PO_REQUISITIONS_INTERFACE_ALL

UPDATE PO_REQUISITIONS_INTERFACE_ALL
SET PROCESS_FLAG = NULL,
REQUISITION_HEADER_ID = NULL,
REQUISITION_LINE_ID = NULL,
REQ_DISTRIBUTION_ID = NULL
WHERE PROCESS_FLAG = 'ERROR'
AND INTERFACE_SOURCE_CODE = 'ORDER ENTRY'
and REQUISITION_LINE_ID = :P_REQUISITION_ID -- 條件依據個人習慣

3.重送Requisition Import,完成。