mtl_reserves,wsh_delivery_detail/查看fnd_file.put_line或request的log/output

Oracle库存交易与接口日志检查

最新推荐文章于 2024-01-30 10:39:53 发布

原创

最新推荐文章于 2024-01-30 10:39:53 发布 · 1.8k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#transactions #header #interface #system #date #null

该查询涉及Oracle库存管理中未完成的交易和接口状态。第一部分查询了要求日期前一天的MTL_RESERVATIONS，与WSH_DELIVERY_DETAILS关联，查找已发货但未在MTL_TRANSACTIONS_INTERFACE中记录的交易。第二部分检查仍有预留且未关闭的OE_ORDER_LINES_ALL，并排除已在WSH_DELIVERY_DETAILS中接口过的记录。最后，通过请求ID查找fnd_file.put_line或request的日志输出。

SELECT OOH.ORDER_NUMBER
, MSI.SEGMENT1
, MR.RESERVATION_QUANTITY
, MR.LOT_NUMBER
, WDD.ORGANIZATION_ID
FROM OE_ORDER_HEADERS_ALL OOH
, MTL_SYSTEM_ITEMS_B MSI
, WSH_DELIVERY_DETAILS WDD
, MTL_RESERVATIONS MR
WHERE MR.REQUIREMENT_DATE < (SYSDATE - 1)
AND WDD.SOURCE_HEADER_ID = OOH.HEADER_ID
AND MSI.ORGANIZATION_ID = <<master item organization id>>
AND MR.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND WDD.SHIPPED_QUANTITY IS NOT NULL
AND WDD.SOURCE_LINE_ID = MR.DEMAND_SOURCE_LINE_ID
AND NOT EXISTS (SELECT INVENTORY_ITEM_ID FROM MTL_TRANSACTIONS_INTERFACE
WHERE
TRX_SOURCE_LINE_ID = WDD.SOURCE_LINE_ID)
GROUP BY OOH.ORDER_NUMBER
, MSI.SEGMENT1
, MR.RESERVATION_QUANTITY
, MR.LOT_NUMBER
, WDD.ORGANIZATION_ID
ORDER BY OOH.ORDER_NUMBER, MSI.SEGMENT1