/*------------------------------------------------
创建时间:2007-09-13
创 建 人:szj
参 考:
功 能: 门店订货单执行明细表(仓库调拨单和加盟配送单)
修 改:
具 体:
备 注:
*/-------------------------------------------------
select a.BillNo,a.TheDate,e.*,b.TheName StorageName,b.ShopName,b.StorPlaceName,c.TheName ProductName,c.ConfigName,c.UnitName,
d.ProductCode,d.ProductSortCode,d.Number OrderNumber,d.DeliverDate
from tbSelfOrder a
left outer join tbSelfOrderItem d on a.BillNo=d.BillNo
left outer join
(
--仓库调拨单
select cc.ProductCode,cc.ProductSortCode,SrcBillNo,dd.BillNo RedeployNo,dd.ConfirmDate,dd.TheDate RedeployDate,sum(cc.Number) SureNumber,cc.RedeployInStorage,cc.RedeployInPlace
from tbStorRedeployItem cc
left outer join tbStorRedeploy dd on cc.BillNo = dd.BillNo
where dbo.GetByteToBoolean(dd.TheState,0x0001)=1 and dd.SrcBillSort = 1
group by cc.ProductCode,cc.ProductSortCode,cc.SrcBillNo,dd.BillNo,dd.ConfirmDate,dd.TheDate,cc.RedeployInStorage,cc.RedeployInPlace
union all
--加盟配送单
select cc.ProductCode,cc.ProductSortCode,SrcBillNo,dd.BillNo RedeployNo,dd.ConfirmDate,dd.TheDate RedeployDate,sum(cc.Number) SureNumber,cc.RedeployInStorage,cc.RedeployInPlace
from tbStorRedeployLeagueItem cc
left outer join tbStorRedeployLeague dd on cc.BillNo = dd.BillNo
where dbo.GetByteToBoolean(dd.TheState,0x0001)=1 and dd.SrcBillSort = 1
group by cc.ProductCode,cc.ProductSortCode,cc.SrcBillNo,dd.BillNo,dd.ConfirmDate,dd.TheDate,cc.RedeployInStorage,cc.RedeployInPlace
)e on a.BillNo=e.SrcBillNo and d.ProductCode=e.ProductCode and d.ProductSortCode=e.ProductSortCode
--辅助信息
left outer join vDatumStorPlace b on e.RedeployInStorage = b.TheCode and e.RedeployInPlace = b.StorPlaceCode
left outer join vDatumProduct c on d.ProductCode = c.TheCode and d.ProductSortCode = c.ProductSort
where dbo.GetByteToBoolean(a.TheState,0x0001)=1 and e.RedeployInStorage in {{StorageGroup}} {0}
创建时间:2007-09-13
创 建 人:szj
参 考:
功 能: 门店订货单执行明细表(仓库调拨单和加盟配送单)
修 改:
具 体:
备 注:
*/-------------------------------------------------
select a.BillNo,a.TheDate,e.*,b.TheName StorageName,b.ShopName,b.StorPlaceName,c.TheName ProductName,c.ConfigName,c.UnitName,
d.ProductCode,d.ProductSortCode,d.Number OrderNumber,d.DeliverDate
from tbSelfOrder a
left outer join tbSelfOrderItem d on a.BillNo=d.BillNo
left outer join
(
--仓库调拨单
select cc.ProductCode,cc.ProductSortCode,SrcBillNo,dd.BillNo RedeployNo,dd.ConfirmDate,dd.TheDate RedeployDate,sum(cc.Number) SureNumber,cc.RedeployInStorage,cc.RedeployInPlace
from tbStorRedeployItem cc
left outer join tbStorRedeploy dd on cc.BillNo = dd.BillNo
where dbo.GetByteToBoolean(dd.TheState,0x0001)=1 and dd.SrcBillSort = 1
group by cc.ProductCode,cc.ProductSortCode,cc.SrcBillNo,dd.BillNo,dd.ConfirmDate,dd.TheDate,cc.RedeployInStorage,cc.RedeployInPlace
union all
--加盟配送单
select cc.ProductCode,cc.ProductSortCode,SrcBillNo,dd.BillNo RedeployNo,dd.ConfirmDate,dd.TheDate RedeployDate,sum(cc.Number) SureNumber,cc.RedeployInStorage,cc.RedeployInPlace
from tbStorRedeployLeagueItem cc
left outer join tbStorRedeployLeague dd on cc.BillNo = dd.BillNo
where dbo.GetByteToBoolean(dd.TheState,0x0001)=1 and dd.SrcBillSort = 1
group by cc.ProductCode,cc.ProductSortCode,cc.SrcBillNo,dd.BillNo,dd.ConfirmDate,dd.TheDate,cc.RedeployInStorage,cc.RedeployInPlace
)e on a.BillNo=e.SrcBillNo and d.ProductCode=e.ProductCode and d.ProductSortCode=e.ProductSortCode
--辅助信息
left outer join vDatumStorPlace b on e.RedeployInStorage = b.TheCode and e.RedeployInPlace = b.StorPlaceCode
left outer join vDatumProduct c on d.ProductCode = c.TheCode and d.ProductSortCode = c.ProductSort
where dbo.GetByteToBoolean(a.TheState,0x0001)=1 and e.RedeployInStorage in {{StorageGroup}} {0}