Revision history  [back]

   with x as (
    select distinct h.entityType,h.id,h.code,wl.warehouse_id from StockTransfer h
     left join StockTransferLine l on h.id = l.stockTransfer_id
     left join WareLocator wl on wl.warehouse_id = l.warehouse_id
    where wl.id is not null and l.locator_id is null
    union all
    select distinct h.entityType,h.id,h.code,wl.warehouse_id from StockTransfer h
     left join StockTransferLine l on h.id = l.stockTransfer_id
     left join WareLocator wl on wl.warehouse_id = l.toWarehouse_id
    where wl.id is not null and l.toLocator_id is null
    union all
    select distinct h.entityType,h.id,h.code,wl.warehouse_id from StockIssue h
     left join StockIssueLine l on h.id = l.stockIssue_id
     left join WareLocator wl on wl.warehouse_id = l.warehouse_id
    where wl.id is not null and l.locator_id is null
    union all
    select distinct h.entityType,h.id,h.code,wl.warehouse_id from StockReceipt h
     left join StockReceiptLine l on h.id = l.stockReceipt_id
     left join WareLocator wl on wl.warehouse_id = l.warehouse_id
    where wl.id is not null and l.locator_id is null
    union all
    select distinct l.originType,l.originId,l.originCode,wl.warehouse_id from ReservationTransLine l 
     left join WareLocator wl on wl.warehouse_id = l.warehouse_id
    where wl.id is not null and l.locator_id is null)
    select distinct h.entityType,h.id,h.code from x as h

   with x as (
    select distinct h.entityType,h.id,h.code,wl.warehouse_id from StockTransfer h
     left join StockTransferLine l on h.id = l.stockTransfer_id
     left join WareLocator wl on wl.warehouse_id = l.warehouse_id
    where wl.id is not null and l.locator_id is null
    union all
    select distinct h.entityType,h.id,h.code,wl.warehouse_id from StockTransfer h
     left join StockTransferLine l on h.id = l.stockTransfer_id
     left join WareLocator wl on wl.warehouse_id = l.toWarehouse_id
    where wl.id is not null and l.toLocator_id is null
    union all
    select distinct h.entityType,h.id,h.code,wl.warehouse_id from StockIssue h
     left join StockIssueLine l on h.id = l.stockIssue_id
     left join WareLocator wl on wl.warehouse_id = l.warehouse_id
    where wl.id is not null and l.locator_id is null
    union all
    select distinct h.entityType,h.id,h.code,wl.warehouse_id from StockReceipt h
     left join StockReceiptLine l on h.id = l.stockReceipt_id
     left join WareLocator wl on wl.warehouse_id = l.warehouse_id
    where wl.id is not null and l.locator_id is null
    union all
    select distinct l.originType,l.originId,l.originCode,wl.warehouse_id from ReservationTransLine l 
     left join WareLocator wl on wl.warehouse_id = l.warehouse_id
    where wl.id is not null and l.locator_id is null)
    select distinct h.entityType,h.id,h.code from x as h

   with x as (
    select distinct h.entityType,h.id,h.code,wl.warehouse_id from StockTransfer h
     left join StockTransferLine l on h.id = l.stockTransfer_id
     left join WareLocator wl on wl.warehouse_id = l.warehouse_id
    where wl.id is not null and l.locator_id is null
    union all
    select distinct h.entityType,h.id,h.code,wl.warehouse_id from StockTransfer h
     left join StockTransferLine l on h.id = l.stockTransfer_id
     left join WareLocator wl on wl.warehouse_id = l.toWarehouse_id
    where wl.id is not null and l.toLocator_id is null
    union all
    select distinct h.entityType,h.id,h.code,wl.warehouse_id from StockIssue h
     left join StockIssueLine l on h.id = l.stockIssue_id
     left join WareLocator wl on wl.warehouse_id = l.warehouse_id
    where wl.id is not null and l.locator_id is null
    union all
    select distinct h.entityType,h.id,h.code,wl.warehouse_id from StockReceipt h
     left join StockReceiptLine l on h.id = l.stockReceipt_id
     left join WareLocator wl on wl.warehouse_id = l.warehouse_id
    where wl.id is not null and l.locator_id is null
    union all
    select distinct l.originType,l.originId,l.originCode,wl.warehouse_id from ReservationTransLine l 
     left join WareLocator wl on wl.warehouse_id = l.warehouse_id
    where wl.id is not null and l.locator_id is null)
    select distinct h.entityType,h.id,h.code from x as h

   with x as (
    select distinct h.entityType,h.id,h.code,wl.warehouse_id from StockTransfer h
     left join StockTransferLine l on h.id = l.stockTransfer_id
     left join WareLocator wl on wl.warehouse_id = l.warehouse_id
    where wl.id is not null and l.locator_id is null
    union all
    select distinct h.entityType,h.id,h.code,wl.warehouse_id from StockTransfer h
     left join StockTransferLine l on h.id = l.stockTransfer_id
     left join WareLocator wl on wl.warehouse_id = l.toWarehouse_id
    where wl.id is not null and l.toLocator_id is null
    union all
    select distinct h.entityType,h.id,h.code,wl.warehouse_id from StockIssue h
     left join StockIssueLine l on h.id = l.stockIssue_id
     left join WareLocator wl on wl.warehouse_id = l.warehouse_id
    where wl.id is not null and l.locator_id is null
    union all
    select distinct h.entityType,h.id,h.code,wl.warehouse_id from StockReceipt h
     left join StockReceiptLine l on h.id = l.stockReceipt_id
     left join WareLocator wl on wl.warehouse_id = l.warehouse_id
    where wl.id is not null and l.locator_id is null
    union all
    select distinct l.originType,l.originId,l.originCode,wl.warehouse_id from ReservationTransLine l 
     left join WareLocator wl on wl.warehouse_id = l.warehouse_id
    where wl.id is not null and l.locator_id is null)
    select distinct h.entityType,h.id,h.code from x as h

Refer to Reprocessing Transactions guide

Use the following query

   with x as (
    select distinct h.entityType,h.id,h.code,wl.warehouse_id from StockTransfer h
     left join StockTransferLine l on h.id = l.stockTransfer_id
     left join WareLocator wl on wl.warehouse_id = l.warehouse_id
    where wl.id is not null and l.locator_id is null
    union all
    select distinct h.entityType,h.id,h.code,wl.warehouse_id from StockTransfer h
     left join StockTransferLine l on h.id = l.stockTransfer_id
     left join WareLocator wl on wl.warehouse_id = l.toWarehouse_id
    where wl.id is not null and l.toLocator_id is null
    union all
    select distinct h.entityType,h.id,h.code,wl.warehouse_id from StockIssue h
     left join StockIssueLine l on h.id = l.stockIssue_id
     left join WareLocator wl on wl.warehouse_id = l.warehouse_id
    where wl.id is not null and l.locator_id is null
    union all
    select distinct h.entityType,h.id,h.code,wl.warehouse_id from StockReceipt h
     left join StockReceiptLine l on h.id = l.stockReceipt_id
     left join WareLocator wl on wl.warehouse_id = l.warehouse_id
    where wl.id is not null and l.locator_id is null
    union all
    select distinct l.originType,l.originId,l.originCode,wl.warehouse_id from ReservationTransLine l 
     left join WareLocator wl on wl.warehouse_id = l.warehouse_id
    where wl.id is not null and l.locator_id is null)
    select distinct h.entityType,h.id,h.code from x as h