"localhost:/firebird/data/ILICHEVSK2.FDB". Процедуры

LOGISTICS_UPDATE

Входные параметры

Выходные параметры

(Нет выходных параметров)

Описание

Запись данных модулем "Логистик"

Определение

CREATE PROCEDURE LOGISTICS_UPDATE(
    INVENTORYID INTEGER,
    REGDATE DATE,
    INVOICEID INTEGER,
    OWNERID INTEGER,
    PROVIDERID INTEGER,
    SAMPLERQUALITYID INTEGER,
    STORAGEID INTEGER,
    STATE INTEGER,
    SAMPLEROUTDATE DATE,
    TRANSPORTTYPEID INTEGER,
    TRANSPORTNUMBER VARCHAR(31),
    INVOICEGROSS INTEGER,
    INVOICETARE INTEGER,
    INVOICENET INTEGER,
    INVOICENUMBER VARCHAR(30),
    INVOICEDATE DATE,
    STATIONID INTEGER,
    SENDERID INTEGER,
    AGREEMENTNUMBER VARCHAR(10),
    SCALESTYPEID INTEGER,
    CONFIRMATIONTIME TIMESTAMP,
    CROPID INTEGER,
    CLASSID INTEGER,
    SORTID INTEGER,
    GMOPRESENCEID INTEGER)
AS
begin
  if (not exists (select INVENTORYID from ITTRANSPORT where INVENTORYID = :INVENTORYID)) then begin
    INVOICEID = gen_id(INVOICEIDGEN,1);
    insert into INVOICES (INVOICEID, INVOICENUMBER, INVOICEDATE, STATIONID, SENDERID,
                          AGREEMENTNUMBER, SCALESTYPEID, CONFIRMATIONTIME)
      values (:INVOICEID, :INVOICENUMBER, :INVOICEDATE, :STATIONID, :SENDERID,
              :AGREEMENTNUMBER, :SCALESTYPEID, :CONFIRMATIONTIME);

    SAMPLERQUALITYID = gen_id(QUALITYIDGEN,1);
    insert into QUALITY (QUALITYID, CROPID, CLASSID, SORTID)
      values (:SAMPLERQUALITYID, :CROPID, :CLASSID, :SORTID);
    insert into QUALITYDATA (QUALITYID, QUALITYTYPEID, QUALITYVALUE)
      values (:SAMPLERQUALITYID, 3252, :GMOPRESENCEID);

    insert into ITTRANSPORT (INVENTORYID, SAMPLEROUTDATE, TRANSPORTTYPEID,
                             TRANSPORTNUMBER, INVOICEGROSS, INVOICETARE, INVOICENET)
      values (:INVENTORYID, :SAMPLEROUTDATE, :TRANSPORTTYPEID,
              :TRANSPORTNUMBER, :INVOICEGROSS, :INVOICETARE, :INVOICENET);

    insert into IT (INVENTORYID, REGDATE, OPERATIONID, INVOICEID, OWNERID,
                    PROVIDERID, SAMPLERQUALITYID, STORAGEID, STATE)
      values (:INVENTORYID, :SAMPLEROUTDATE, 1, :INVOICEID, :OWNERID,
              :PROVIDERID, :SAMPLERQUALITYID, :STORAGEID, :STATE);
  end
  else begin
    update INVOICES
    set INVOICENUMBER = :INVOICENUMBER,
        INVOICEDATE = :INVOICEDATE,
        STATIONID = :STATIONID,
        SENDERID = :SENDERID,
        AGREEMENTNUMBER = :AGREEMENTNUMBER,
        SCALESTYPEID = :SCALESTYPEID,
        CONFIRMATIONTIME = :CONFIRMATIONTIME
    where (INVOICEID = :INVOICEID);

    update QUALITY
    set CROPID = :CROPID,
        CLASSID = :CLASSID,
        SORTID = :SORTID
    where (QUALITYID = :SAMPLERQUALITYID);

    update or insert into QUALITYDATA (QUALITYID, QUALITYTYPEID, QUALITYVALUE)
      values (:SAMPLERQUALITYID, 3252, :GMOPRESENCEID)
    matching (QUALITYID, QUALITYTYPEID);

    update ITTRANSPORT QUALITY
    set SAMPLEROUTDATE = :SAMPLEROUTDATE,
        TRANSPORTTYPEID = :TRANSPORTTYPEID,
        TRANSPORTNUMBER = :TRANSPORTNUMBER,
        INVOICEGROSS = :INVOICEGROSS,
        INVOICETARE = :INVOICETARE,
        INVOICENET = :INVOICENET
    where (INVENTORYID = :INVENTORYID);

    update IT
    set REGDATE = :SAMPLEROUTDATE,
        OWNERID = :OWNERID,
        PROVIDERID = :PROVIDERID,
        STORAGEID = :STORAGEID,
        STATE = :STATE
    where (INVENTORYID = :INVENTORYID);
  end

  suspend;
end