关闭

相关联的表,除了主键外其它相关处理(商品信息).a.BillNo=e.SrcBillNo and d.ProductCode=e.ProductCode and d.ProductSortCode=e.ProductSortCode

316人阅读 评论(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} 
0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:474973次
    • 积分:6328
    • 等级:
    • 排名:第4019名
    • 原创:154篇
    • 转载:178篇
    • 译文:0篇
    • 评论:91条
    文章分类
    最新评论