日期:2014-05-17 浏览次数:20557 次
create PROCEDURE [dbo].[p] @version bigint , @InvCode nvarchar(100) AS BEGIN SET NOCOUNT ON; with tb1 as ( SELECT d.cInvCode,d.cInvName,a.Version , a.VersionDesc,a.VersionEffDate, c.free1 ,d.cInvStd,d.cInvAddCode , e.cComUnitName,a.CreateUser,a.CreateDate,a.ModifyUser,a.ModifyDate FROM bom_bom a inner join bom_parent b on a.bomid =b.bomid INNER JOIN bas_part c on b.parentId=c.partId INNER JOIN inventory d on c.invcode=d.cinvcode INNER JOIN ComputationUnit e on d.cComUnitCode=e.cComunitCode WHERE 1=1 and d.cInvcode >= 'A05050053' and d.cInvcode <= 'A05050053' ) ,tb2 as ( Select a.bomid ,c.InvCode, a.version from bom_bom a inner join bom_parent b on a.bomid =b.bomid inner join bas_part c on b.parentId=c.partId ) , tb3 as ( Select a.SortSeq,a.OpSeq,c.cInvcode,c.cInvName,c.cInvStd, c.cInvAddCode,d.cComUnitName,a.BaseQtyN,a.BaseQtyD , a.CompScrap,Qty=a.BaseQtyN/a.BaseQtyD ,a.OpComponentId ,b.Free1,a.bomid FROM bom_opcomponent a inner join bas_part b on a.componentId=b.partId INNER JOIN inventory c on b.invcode=c.cinvcode INNER JOIN ComputationUnit d on c.cComUnitCode=d.cComunitCode ) select * from tb3 inner join tb2 on tb3.bomid=tb2.bomid inner join tb1 on tb2.version=tb1.version and tb2.InvCode=tb1.InvCode where tb1.version=@version and tb1.InvCode=@InvCode end go