inv_consistency.pas

一个MRPII系统源代码版本

                   ' from '+
                   ' (select '+
                   '     c.ItemCode+'' ''+b.ItemName Itemflag, '+
                   '     c.whCode, '+
                   '     c.onCheckInv , '+
                   '     b.oncheckqty '+
                   '   from CurrentInv c '+
                   '   left join '+
                   '     (select bl.ItemCode,I.ItemName,b.whCode,sum(oncheckqty) oncheckqty '+
                   '        from oncheckBillline bl,oncheckBill b,Item I '+
                   '        where bl.oncheckstatus=0 and '+
                   '          bl.oncheckBillid=b.oncheckBillid and '+
                   '          I.ItemCode=bl.ItemCode '+
                   '        group by bl.ItemCode,b.whCode,I.ItemName '+
                   '     ) b on c.ItemCode=b.ItemCode and c.whCode=b.whCode '+
                   '   where c.whCode+c.WhPositionCode in'+
                   '         (select distinct whCode+WhPositionCode '+
                   '                     from WhPosition '+
                   '                    where WhPositionType=1)'+
                   ' ) a,Warehouse W'+
                   ' where a.onCheckInv-a.oncheckqty<>0 and w.whCode=a.whCode';
  with AdoQry_Main do
  begin
    Close;
    sql.clear;
    sql.Add(sql_txt02);
    open;
    if not IsEmpty then
    Result:=False
    else Result:=True;
  end;
end;

procedure TFrm_Inv_Consistency.BitBtn2Click(Sender: TObject);
begin
  Close;
end;

function TFrm_Inv_Consistency.Check03: Boolean;
begin
  Sql_Txt03:='DeclAre @SumMonth varchAr(12),@Month varchAr(12) '+
           '    set @Month=(select convert(decimal(8,2),Invstatus) from Invstatus where InvstatusName=''clsperiod'') '+
           '    set @Month=@Month+''.01'' '+
           '    set @SumMonth=(select convert(decimal(8,2),Invstatus+0.01) from Invstatus where InvstatusName=''clsperiod'') '+
           '    set @sumMonth=@sumMonth+''.01'' '+
           ' select a.VendorCode+'' ''+aaa.VendorName 供应商标识,'+
           '        a.ItemCode+'' ''+aa.ItemName 物料标识, '+
           '        opInv 委外库存,Billqty 单据数量,'+
           '        a.opInv-a.Billqty 差异 '+
           '  from '+
           '    (select '+
           '         c.ItemCode,'+
           '         c.VendorCode,'+
           '         c.opInv,'+
           '         case when ms.Invblncqty is null then 0 else ms.Invblncqty end+ '+
           '         case when ib.InvQty is null then 0 else ib.InvQty end- '+
           '         case when ob.InvQty is null then 0 else ob.InvQty end Billqty '+
           '    from opCurrentInv c '+
           '       left join OpMonthSum ms on c.ItemCode=ms.ItemCode and c.VendorCode=ms.VendorCode and InvMonth=@Month '+
           '       left join '+
           '         (select ItemCode,VendorCode,sum(InvBillqty) InvQty '+
           '            from InvOutBillline,InvOutBill '+
           '           where InvOutBill.InvBillid=InvOutBillline.InvBillid and '+
           '              InvOutBill.InvBilldate>=@sumMonth and '+
           '              InvOutBill.BillTypeCode=''0202'' '+
           '            group by ItemCode,VendorCode '+
           '         ) ib on c.ItemCode=ib.ItemCode and c.VendorCode=ib.VendorCode '+
           '       left join '+
           '         (select ItemCode,VendorCode,sum(opBillqty) InvQty '+
           '            from InvInBill,opBill '+
           '            where InvInBill.InvBillid=opBill.InvBillid and '+
           '              InvInBill.InvBilldate>=@sumMonth '+
           '            group by ItemCode,VendorCode '+
           '         ) ob on c.ItemCode=ob.ItemCode and c.VendorCode=ob.VendorCode '+
           '     ) a, '+
           '    Vendor aaa,Item aa'+
           '     where abs(a.opInv-a.Billqty)>0.1 and '+
           '    a.ItemCode=aa.ItemCode and '+
           '    a.VendorCode=aaa.VendorCode ';
  with AdoQry_Main do
  begin
    Close;
    sql.clear;
    sql.Add(sql_txt03);
    open;
    if Not IsEmpty then
    Result:=False
    else Result:=True;
  end;
end;

function TFrm_Inv_Consistency.check04: Boolean;
begin
  sql_txt04:='select '+
           '               pono 采购定单号, '+
           '               polineno 采购定单行号, '+
           '               poqty 采购数量,  '+
           '               porealinqty 采购实入量,  '+
           '               ponoFinishqty 采购未结数量'+
           '  from '+
           '       (select '+
           '               pono , '+
           '               polineno , '+
           '               poqty ,  '+
           '               porealinqty ,  '+
           '               ponoFinishqty '+
           '             from poline '+
           '           ) a '+
           '          where ponoFinishqty<>poqty-porealinqty';
  with AdoQry_Main do
  begin
  Close;
  sql.clear;
  sql.Add(sql_txt04);
  open;
  if not IsEmpty then
  Result:=False
  else Result:=True;
  end;
end;

function TFrm_Inv_Consistency.check05: Boolean;
begin
  sql_txt05:='select a.pono 订单号,a.polineno 订单行号,'+
           ' a.ItemCode+'' ''+I.ItemName 物料标识,'+
           ' a.InvBillqty 单据数量,a.porealinqty 订单实入数量 '+
           ' from '+
           ' (select '+
           '     bl.pono,'+
           '     bl.polineno,'+
           '     bl.ItemCode,'+
           '     bl.InvBillqty,'+
           '     pl.porealinqty'+
           '   from            '+
           '     (select b.pono,bl.polineno,ItemCode,sum(InvBillqty) InvBillqty'+
           '        from InvInBillline bl,InvInBill b'+
           '        where b.InvBillid=bl.InvBillid and b.pono is not null '+
           '        group by b.pono,bl.polineno,ItemCode) bl '+
           '   left join poline pl on bl.pono=pl.pono and bl.polineno=pl.polineno'+
           ' ) a,Item I '+
           ' where InvBillqty<>porealinqty and '+
           '   a.ItemCode=I.ItemCode '+
           '   select a.pono 订单号,a.polineno 订单行号,'+
           '          a.ItemCode+'' ''+I.ItemName 物料标识,'+
           '          a.InvBillqty 单据数量,a.porealinqty 订单实入数量 '+
           ' from '+
           '     (select   '+
           '         bl.pono, '+
           '         bl.polineno, '+
           '         bl.ItemCode, '+
           '         case when bl.InvBillqty is null then 0 else bl.InvBillqty end InvBillqty,'+
           '         pl.porealinqty '+
           '       from poline pl '+
           '       left join'+
           '         (select b.pono,bl.polineno,ItemCode,sum(InvBillqty) InvBillqty'+
           '            from InvInBillline bl,InvInBill b '+
           '            where b.InvBillid=bl.InvBillid and b.pono is not null '+
           '            group by b.pono,bl.polineno,ItemCode) bl '+
           '         on bl.pono=pl.pono and bl.polineno=pl.polineno '+
           '     ) a,Item I '+
           '     where InvBillqty<>porealinqty and '+
           '   a.ItemCode=I.ItemCode ';
  with AdoQry_Main do
  begin
    Close;
    sql.clear;
    sql.Add(sql_txt05);
    open;
    if not isempty then
    Result:=False
    else Result:=True;
  end;
end;

function TFrm_Inv_Consistency.check06: Boolean;
begin
  sql_txt06:='set noCount on '+
           ' create table #table '+
           ' (whCode varchAr(16),  '+
           ' ItemCode varchAr(16), '+
           ' WhPosition varchAr(16), '+
           ' qty decimal(20,6),'+
           ' InBillQty decimal(20,6),'+
           ' OutBillqty decimal(20,6),'+
           ' Billqty decimal(20,6), '+
           ' MonthQty decimal(20,6))  '+
           '     DeclAre @SumMonth varchAr(12) '+
           '     set @SumMonth=(select convert(decimal(8,2),Invstatus+0.01) from Invstatus where InvstatusName=''clsperiod'') '+
           '     set @sumMonth=@sumMonth+''.01'' '+
           '     insert into #table '+
           '       select whCode,ItemCode,WhPositionCode,'+
           '              case when onhandInv is not Null then onhandInv '+
           '                   when onCheckInv is not Null then onCheckInv '+
           '                   when freezeInv is not Null then freezeInv  '+
           '                   when wasterInv is not Null then wasterInv  '+
           '                   when onShipInv is not Null then onShipInv   '+
           '                   else 0 end Billqty,0,0,0,0 '+
           '         from CurrentInv  '+
           '     insert into #table(whCode,WhPosition,ItemCode) '+
           '       select whCode,WhPositionCode,ItemCode '+
           '         from whPMonthSum '+
           '        where whCode+WhPositionCode+ItemCode not in '+
           '              (select whCode+WhPosition+ItemCode '+
           '                 from #table) and '+
           '               InvMonth>=@sumMonth '+
           '     update #table '+
           '        set Monthqty=c.InvInqty-c.Invoutqty '+
           '       from (select InvInqty,Invoutqty,whCode+WhPositionCode+ItemCode as flag from whPMonthSum)c '+
           '      where c.flag=whCode+WhPosition+ItemCode '+
           '     insert into #table(whCode,WhPosition,ItemCode) '+
           '       select distinct whCode,WhPositionCode,ItemCode '+
           '         from (select distinct I.whCode,I.WhPositionCode,IB.ItemCode,I.whCode+I.WhPositionCode+IB.ItemCode as flag '+
           '                 from InvInBill I,InvInBillline IB '+
           '                where I.InvBillid=IB.InvBillId)c '+
           '        where  c.Flag not in '+
           '              (select whCode+WhPosition+ItemCode from #table) '+
           '     update #table '+
           '        set InBillqty=c.InvBillqty '+
           '       from (select sum(IB.InvBillqty)as InvBillqty,I.whCode+I.WhPositionCode+IB.ItemCode as flag'+
           '               from InvInBill I,InvInBillline IB '+
           '              where I.InvBillid=IB.InvBillid and '+
           '                    I.InvBilldate>=@sumMonth and '+
           '                    (I.BillTypeCode not in (''0104'',''0105'') or I.InvBillwhchck=1)'+
           '              group by I.whCode+I.WhPositionCode+IB.ItemCode)c  '+
           '      where c.flag=whCode+WhPosition+ItemCode '+
           '     insert into #table(whCode,WhPosition,ItemCode) '+
           '       select distinct whCode,WhPositionCode,ItemCode '+
           '         from (select distinct I.whCode,I.WhPositionCode,IB.ItemCode,I.whCode+I.WhPositionCode+IB.ItemCode as flag'+
           '                 from InvOutBill I,InvOutBillline IB'+
           '                where I.InvBillid=IB.InvBillId)c'+
           '        where  c.Flag not in'+
           '              (select whCode+WhPosition+ItemCode from #table)'+
           '     update #table'+
           '        set OutBillqty=c.InvBillqty'+
           '       from (select sum(IB.InvBillqty)as InvBillqty,I.whCode+I.WhPositionCode+IB.ItemCode as flag'+
           '               from InvOutBill I,InvOutBillline IB'+
           '              where I.InvBillid=IB.InvBillid and'+
           '                I.InvBilldate>=@sumMonth and'+
           '                (I.BillTypeCode not in (''0201'',''0203'',''0204'') or InvBillwhchck=1)'+
           '              group by I.whCode+I.WhPositionCode+IB.ItemCode )c'+
           '      where c.flag=whCode+WhPosition+ItemCode'+
           '     update #table'+
           '        set Billqty=InBillqty-OutBillqty'+
           '     select T.whCode+'' ''+W.whName 仓库标识,'+
           '            T.WhPosition+'' ''+P.WhPositionName 货位标识,'+
           '            T.ItemCode+'' ''+I.ItemName 物料标识,'+
           '            T.qty 当前库存,'+
           '            T.Billqty 单据数量, '+
           '            T.qty-T.BillQty 差异 '+
           ' from #table T,Item I,Warehouse W,WhPosition P where ((qty-Billqty<>0) or (qty-Monthqty<>0) or (Monthqty-Billqty<>0)) and '+
           '     I.ItemCode=T.ItemCode and '+
           '     W.whCode=T.whCode and '+
           '     P.whCode=T.whCode and P.WhPositionCode=T.WhPosition '+
           '     drop table #table';
  with AdoQry_Main do
  begin
    Close;
    sql.clear;
    sql.Add(sql_txt06);
    open;
    if not isempty then
    Result:=False
    else Result:=True;
  end;
end;

procedure TFrm_Inv_Consistency.BitBtn4Click(Sender: TObject);
var
  SQL_Txt,Title:String;
  id:integer;
begin
  case strtoint(ErrNum.Strings[LB_Err.ItemIndex]) of
  1: begin SQL_Txt:=SQL_Txt01; Title:='当前库存与物料主文件差异';id:=1; end;
  2: begin SQL_Txt:=SQL_Txt02; Title:='当前待检库存与待检单据差异表';id:=2; end;
  3: begin SQL_Txt:=SQL_Txt03; Title:='委外库存与出入库单据差异表';id:=3; end;
  4: begin SQL_Txt:=SQL_Txt04; Title:='PO完成情况分析表';id:=4; end;
  5: begin SQL_Txt:=SQL_Txt05; Title:='正式入库与PO差异表';id:=5; end;
  6: begin SQL_Txt:=SQL_Txt06; Title:='当前库存与出入库单据差异表';id:=6; end;
  7: begin SQL_Txt:=SQL_Txt07; Title:='生产领料单据与领料清单差异表';id:=7; end;
  end;
  Application.CreateForm(TFrm_Inv_Consistency_D,Frm_Inv_Consistency_D);
  Frm_Inv_Consistency_D.SetSysParam('0000','1','1','2001.01.01');
  Frm_Inv_Consistency_D.initform(AdoQry_Main.Connection,SQL_Txt,Title,id);
  Frm_Inv_Consistency_D.ShowModal;
end;

procedure TFrm_Inv_Consistency.BitBtn3Click(Sender: TObject);
begin
  AdoQry_Main.Cancel;
  REdt_Detail.Lines.Add('用户中止查询!');
end;

function TFrm_Inv_Consistency.check07: Boolean;
begin
  sql_txt07:='select a.mono 生产订单号,'+
             '       a.MoLineno 订单行号,'+
             '       a.ItemCode+'' ''+I.ItemName 物料标识,'+
             '       a.InvBillqty 领料清单数量,'+
             '       a.MoRealqty 单据数量, '+
             '       a.InvBillqty-a.MoRealqty 差异 '+
          ' from'+
          ' (select distinct '+
          '    bl.mono,'+
          '    bl.MoLineno,'+
          '    bl.ItemCode,'+
          '    bl.InvBillqty,'+
          '    ml.MoRealqty'+
          '  from'+
          '    (select b.mono,bl.MoLineno,ItemCode,sum(InvBillqty) InvBillqty'+
          '       from InvOutBillline bl,InvOutBill b'+
          '       where b.InvBillid=bl.InvBillid and'+
          '         b.mono is not null and'+
          '         b.InvBillwhchck=1'+
          '       group by b.mono,bl.MoLineno,ItemCode) bl'+
          '  left join mnItemList ml on bl.mono=ml.mono and bl.MoLineno=ml.MoLineno and bl.ItemCode=ml.ItemCode'+
          ' ) a,Item I'+
          ' where a.ItemCode=I.ItemCode and InvBillqty<>MoRealqty';
  with AdoQry_Main do
  begin
    Close;
    sql.clear;
    sql.Add(sql_txt07);
    open;
    if not isempty then
    Result:=False
    else Result:=True;
  end;

end;

procedure TFrm_Inv_Consistency.FormActivate(Sender: TObject);
begin
  BitBtn4.Enabled :=False;
end;

end.