Epicor 调拨方式平负数库存 直接生成DMT格式
SELECT pb.Company,pb.PartNum--,p.PartDescription [DESC],t1.WarehouseCode FromWarehouseCode,t1.BinNum FromBinNum,pb.WarehouseCode ToWarehouseCode,pb.BinNum ToBinNum,IIF(CEILING(abs(pb.OnhandQty))>t1.OnhandQty,t1.OnhandQty,CEILING(abs(pb.OnhandQty))) TransferQty ,t1.LotNum FromLotNumber,pb.LotNum ToLotNumber,pb.DimCode TransferQtyUOM,'MfgSys' Plant
FROM erp.PartBin pbLEFT JOIN (SELECT * FROM (
SELECT pb0.Company,pb0.PartNum,pb0.WarehouseCode,pb0.BinNum,pb0.OnhandQty-pb0.AllocatedQty OnhandQty,pb0.LotNum ,pb0.DimCode ,ROW_NUMBER() OVER (PARTITION BY pb0.PartNum ORDER BY pb0.OnhandQty desc) SeqFROM erp.PartBin pb0 WHERE pb0.Company='1005' AND pb0.OnhandQty-pb0.AllocatedQty>0
) t1 WHERE seq=1) t1 ON pb.Company=t1.Company AND pb.PartNum=t1.PartNumLEFT JOIN erp.Part p ON pb.Company = p.Company AND pb.PartNum = p.PartNumWHERE pb.Company='1005' AND pb.OnhandQty<0 AND t1.WarehouseCode IS NOT NULL;