销售发票参照累计出库数量条件过滤出库单是否审核的数量

maxiwen

sale_DispToSaleVouchJS_B

1. 
   
2. select N'' as selcol,SA_FHD_WReCount.autoid, SA_FHD_WReCount.dlid, SA_FHD_WReCount.cwhcode, SA_FHD_WReCount.cinvcode
   
3. --, case when isnull(itb,0)=0 then SA_FHD_WReCount.iquantity else isnull(tbquantity,0) end as iquantity
   
4. --修改参照累计出库数量条件过滤出库单是否审核的数量 2017年2月28日 马昔文
   
5. , case when isnull(itb,0)=0 then shck.qty  else isnull(tbquantity,0) end as iquantity
   
6. , case when isnull(itb,0)=0 then SA_FHD_WReCount.inum else isnull(tbnum,0) end as inum, SA_FHD_WReCount.iquotedprice,
   
7.         SA_FHD_WReCount.iunitprice, SA_FHD_WReCount.itaxunitprice,SA_FHD_WReCount.imoney, SA_FHD_WReCount.itax, SA_FHD_WReCount.isum,SA_FHD_WReCount.idiscount, SA_FHD_WReCount.inatunitprice,SA_FHD_WReCount.inatmoney, SA_FHD_WReCount.inattax, SA_FHD_WReCount.inatsum,SA_FHD_WReCount.fsalecost,SA_FHD_WReCount.fsaleprice,
   
8.         SA_FHD_WReCount.inatdiscount, SA_FHD_WReCount.isettlenum,SA_FHD_WReCount.isettlequantity, SA_FHD_WReCount.ibatch,SA_FHD_WReCount.cinvouchtype,SA_FHD_WReCount.cbatch, SA_FHD_WReCount.bsettleall, SA_FHD_WReCount.cmemo,SA_FHD_WReCount.cfree1, SA_FHD_WReCount.cfree2,case when isnull(SA_FHD_WReCount.itb,0)=0 then N'正常' else N'退补' end as itb,
   
9.         convert(varchar(10),SA_FHD_WReCount.dvdate,121) as dvdate, convert(varchar(10),SA_FHD_WReCount.dmdate,121) as dmdate, SA_FHD_WReCount.tbquantity, SA_FHD_WReCount.tbnum,SA_FHD_WReCount.isosid,SA_FHD_WReCount.csocode, SA_FHD_WReCount.idlsid, SA_FHD_WReCount.kl,SA_FHD_WReCount.kl2, SA_FHD_WReCount.itaxrate, SA_FHD_WReCount.cdefine22,SA_FHD_WReCount.cdefine23, SA_FHD_WReCount.cdefine24,
   
10.         SA_FHD_WReCount.cdefine25, SA_FHD_WReCount.cdefine26,SA_FHD_WReCount.cdefine27, SA_FHD_WReCount.foutquantity,case when inventory.igrouptype=1 then SA_FHD_WReCount.foutquantity/iinvexchrate else case when igrouptype=0 then null else SA_FHD_WReCount.foutnum end end as foutnum, SA_FHD_WReCount.citemcode,SA_FHD_WReCount.citem_class, SA_FHD_WReCount.cvenabbname,
    
11.         SA_FHD_WReCount.citemname, SA_FHD_WReCount.citem_cname,SA_FHD_WReCount.cfree3, SA_FHD_WReCount.cfree4, SA_FHD_WReCount.cfree5,SA_FHD_WReCount.cfree6, SA_FHD_WReCount.cfree7, SA_FHD_WReCount.cfree8,SA_FHD_WReCount.cfree9, SA_FHD_WReCount.cfree10,bsalepricefree1,bsalepricefree2,bsalepricefree3,bsalepricefree4,bsalepricefree5,bsalepricefree6,bsalepricefree7,bsalepricefree8,bsalepricefree9,bsalepricefree10,
    
12.         case when igrouptype=1 then ISNULL(Unit2.iChangRate,0) else (case when isnull(fOutNum,0)=0 then 0 else fOutQuantity/foutnum end) end as iinvexchrate, SA_FHD_WReCount.cunitid,case when isnull(bsignover,0)=1 then '1' else '0' end as bsignover,
    
13.         cast(SA_FHD_WReCount.iretquantity as float) as iretquantity, cast(SA_FHD_WReCount.iretquantity as float) as fretqtywkp, isnull(fretsum,0) as fretsum,cast(SA_FHD_WReCount.fensettlequan as float ) as fensettlequan,SA_FHD_WReCount.fensettlesum, SA_FHD_WReCount.isettleprice,SA_FHD_WReCount.cdefine28, SA_FHD_WReCount.cdefine29,SA_FHD_WReCount.cdefine30, SA_FHD_WReCount.cdefine31,SA_FHD_WReCount.cdefine32, SA_FHD_WReCount.cdefine33,
    
14.         case when iquantity=0 then isnull(tbquantity,0)-isnull(isettlequantity,0) else iquantity-isettlequantity end as iwkpquantity,case when iquantity=0 then case when itb=1 then itaxunitprice*(isnull(tbquantity,0)-isnull(isettlequantity,0)) else isum-isettlenum end else itaxunitprice*(iquantity-isettlequantity) end as iwkpsum,
    
15.         SA_FHD_WReCount.cdefine34, SA_FHD_WReCount.cdefine35,convert(varchar(10),SA_FHD_WReCount.cdefine36,121) as cdefine36 , convert(varchar(10),SA_FHD_WReCount.cdefine37,121) as cdefine37, convert(char,convert(money,dispatchlist.ufts),2)   as corufts,   
    
16.         SA_FHD_WReCount.cordercode,SA_FHD_WReCount.iorderrowno,SA_FHD_WReCount.cvmivencode,vendor.cvenname as cvmivenname,csrpolicy,case when isnull(bneedsign,0)=1 then '1' else '0' end as bneedsign,case when isnull(bneedloss,0)=1 then '1' else '0' end as bneedloss,clossmaker,
    
17.         inventory.cinvaddcode,inventory.cinvname ,inventory.cinvstd,inventory.cinvdefine1,inventory.cinvdefine2,inventory.cinvdefine3,inventory.igrouptype,inventory.cgroupcode,warehouse.cwhname,
    
18.         inventory.binvtype, inventory.cinvdefine4, inventory.cinvdefine5,inventory.cinvdefine6, inventory.cinvdefine7, inventory.cinvdefine8,inventory.cinvdefine9, inventory.cinvdefine10, inventory.cinvdefine11,
    
19.         inventory.cinvdefine12, inventory.cinvdefine13, inventory.cinvdefine14,convert(varchar(10),inventory.cinvdefine15,121) as cinvdefine15, convert(varchar(10),inventory.cinvdefine16,121) as cinvdefine16, inventory.binvbatch, inventory.bservice,
    
20.         inventory.iinvlscost,inventory.iinvsalecost,   inventory.ccomunitcode,inventory.iid as Iiid,bfree1,bfree2,bfree3,bfree4,bfree5,bfree6,bfree7,bfree8,bfree9,bfree10,
    
21.         SA_FHD_WReCount.imassdate,  binvquality,btrack,SA_FHD_WReCount.ccode,case SA_FHD_WReCount.cmassunit when N'0' then N'' when N'1' then N'年' when N'2' then N'月' when N'3' then N'日' end as cmassunit,
    
22.         SA_FHD_WReCount.ccontractid,SA_FHD_WReCount.ccontracttagcode,SA_FHD_WReCount.ccontractrowguid,ccusinvcode,ccusinvname,SA_FHD_WReCount.fcusminprice ,case when bgift=1 then 1 else 0 end as bgift,case when bsaleprice=1 then 1 else 0 end as bsaleprice , unit1.ccomunitname as cinvm_unit,ccorcode,null as ispecialtype,idemandtype,cdemandcode,cdemandmemo,cdemandid,idemandseq,case when isnull(btracksalebill,0)=1 then 1 else 0 end as btracksalebill,
    
23.         case when isnull(inventory.igrouptype,0)=0 then null else unit2.ccomunitname end as cinva_unit,  sa_fhd_wrecount.cexpirationdate,sa_fhd_wrecount.iexpiratdatecalcu ,sa_fhd_wrecount.dexpirationdate,
    
24.         cbatchproperty1,cbatchproperty2,cbatchproperty3,cbatchproperty4,cbatchproperty5,cbatchproperty6,cbatchproperty7,cbatchproperty8,cbatchproperty9,cbatchproperty10,
    
25.         bbatchcreate,bbatchproperty1,bbatchproperty2,bbatchproperty3,bbatchproperty4,bbatchproperty5,bbatchproperty6,bbatchproperty7,bbatchproperty8,bbatchproperty9,bbatchproperty10,
    
26.         100-SA_FHD_WReCount.kl as dkl1,100-SA_FHD_WReCount.kl2 as dkl2,icorid,iinvweight,bproxywh,creasoncode,creasonname,SA_FHD_WReCount.cvencode,irowno ,cparentcode,cchildcode,case when isnull(inventory.bptomodel,0)=0 then N'否' else N'是' end as bptomodel,icalctype,fchildqty,fchildrate,case when isnull(inventory.binvmodel,0)=0 then N'否' else N'是' end as binvmodel
    
27.         from SA_FHD_WReCount left join inventory with (nolock) on SA_FHD_WReCount.cinvcode = inventory.cinvcode left join warehouse on SA_FHD_WReCount.cwhcode = warehouse.cwhcode
    
28.         inner join dispatchlist on SA_FHD_WReCount.dlid=dispatchlist.dlid
    
29.         left join vendor on SA_FHD_WReCount.cvmivencode=vendor.cvencode
    
30.         left join computationunit as unit1 on inventory.ccomunitcode=unit1.ccomunitcode
    
31.         left join ComputationUnit as Unit2 on SA_FHD_WReCount.cunitid=Unit2.cComUnitCode  
    
32.         left join inventory_sub on inventory_sub.cinvsubcode=SA_FHD_WReCount.cinvcode
    
33.         left join (SELECT cInvCode ,sum(iQuantity) qty,iDLsID   
    
34.                                         FROM rdrecord32 rdh inner join rdrecords32  rdd on rdh.ID =rdd.ID
    
35.                                 where  isnull(rdh.cHandler,'')<>''
    
36.                                 group by cInvCode ,iDLsID ) shck on shck.iDLsID=SA_FHD_WReCount.iDLsID
    

复制代码