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

CHECKPOINT_JOURNAL

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

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

Описание

(Нет описания для процедуры CHECKPOINT_JOURNAL)

Определение

CREATE PROCEDURE CHECKPOINT_JOURNAL(
    BEGDATE DATE,
    ENDDATE DATE,
    OPERATIONID INTEGER)
RETURNS (
    REGDATE DATE,
    INVOICEID INTEGER,
    INVOICENUMBER VARCHAR(31),
    CLASSID INTEGER,
    CROPID INTEGER,
    SORTID INTEGER,
    CROPCLASSNAME VARCHAR(150),
    CLIENTID INTEGER,
    CLIENTFULLNAME VARCHAR(150),
    NET INTEGER,
    CHECKPOINTINTIME TIMESTAMP,
    CHECKPOINTOUTTIME TIMESTAMP,
    TRANSPORTTYPEID INTEGER,
    TRANSPORTNUMBER VARCHAR(30),
    DRIVERS VARCHAR(30),
    BARCODE VARCHAR(30),
    INVENTORYID INTEGER,
    STATE INTEGER,
    LABQUALITYID INTEGER,
    SESSION INTEGER,
    SAMPLERQUALITYID INTEGER,
    WEIGHTNUMBER INTEGER)
AS
declare variable AINVOICEID integer;
begin
  for
    select IT.INVOICEID, INVOICES.INVOICENUMBER, INVOICES.DRIVERS,
        QUALITY.CROPID, QUALITY.CLASSID, QUALITY.SORTID,
        CLIENTS.CLIENTNAME, CLIENTS.CLIENTID, IT.NET,
        coalesce(ITTRANSPORT.CHECKPOINTINTIME, IT.GROSSTIME),
        coalesce(ITTRANSPORT.CHECKPOINTOUTTIME, IT.TARETIME),
        IT.INVENTORYID, ITTRANSPORT.TRANSPORTTYPEID,
        ITTRANSPORT.TRANSPORTNUMBER, ITTRANSPORT.BARCODE, IT.STATE,
        QUALITY_CROPFULLNAME.CROPFULLNAME, IT.LABQUALITYID, IT.REGDATE,
        ITTRANSPORT.CHECKPOINTSESSION, IT.SAMPLERQUALITYID, IT.WEIGHTNUMBER
      from IT
        left join QUALITY on (QUALITY.QUALITYID = IT.SAMPLERQUALITYID)
        left join QUALITY_CROPFULLNAME(QUALITY.CROPID, QUALITY.CLASSID, QUALITY.SORTID, 1) on (0 = 0)
        left join CROPSORT on (CROPSORT.SORTID = QUALITY.SORTID)
        left join INVOICES on (INVOICES.INVOICEID = IT.INVOICEID)
        left join CLIENTS on (CLIENTS.CLIENTID = IT.PROVIDERID)
        left join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = IT.INVENTORYID)
      where (((((IT.REGDATE between :BEGDATE and :ENDDATE) and (coalesce(ITTRANSPORT.CHECKPOINTOUTTIME, IT.TARETIME) is not null))
               or ((IT.REGDATE between :BEGDATE - 10 and :ENDDATE) and (coalesce(ITTRANSPORT.CHECKPOINTOUTTIME, IT.TARETIME) is null)))
              and (IT.STATE >= 0))
             or ((((IT.REGDATE between :BEGDATE and :ENDDATE) and (coalesce(ITTRANSPORT.CHECKPOINTOUTTIME, IT.TARETIME) is not null))
               or ((IT.REGDATE between :BEGDATE - 10 and :ENDDATE) and (coalesce(ITTRANSPORT.CHECKPOINTOUTTIME, IT.TARETIME) is null)))
              and (IT.STATE = -5))
             or ((IT.REGDATE between :BEGDATE and :ENDDATE) and (IT.STATE = -3)))
        and (IT.OPERATIONID = :OPERATIONID) and (ITTRANSPORT.TRANSPORTTYPEID < 2) and (IT.PARENTID <= 0)
    order by IT.REGDATE, IT.INVOICEID, ITTRANSPORT.TRANSPORTTYPEID, IT.INVENTORYID
    into :INVOICEID, :INVOICENUMBER, :DRIVERS, :CROPID, :CLASSID, :SORTID,
        :CLIENTFULLNAME, :CLIENTID, :NET, :CHECKPOINTINTIME, :CHECKPOINTOUTTIME,
        :INVENTORYID, :TRANSPORTTYPEID, :TRANSPORTNUMBER, :BARCODE, :STATE,
        :CROPCLASSNAME, :LABQUALITYID, :REGDATE, :SESSION, :SAMPLERQUALITYID,
        :WEIGHTNUMBER
  do
    if (STATE = -5) then begin
      select IT.INVOICEID
        from IT
          left join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = IT.INVENTORYID)
        where ((((IT.REGDATE between :BEGDATE and :ENDDATE) and (coalesce(ITTRANSPORT.CHECKPOINTOUTTIME, IT.TARETIME) is not null))
                or ((IT.REGDATE between :BEGDATE - 10 and :ENDDATE) and (coalesce(ITTRANSPORT.CHECKPOINTOUTTIME, IT.TARETIME) is null)))
               and (IT.STATE >= 0))
          and (IT.INVOICEID = :INVOICEID)
      into :AINVOICEID;
      if (AINVOICEID is not null) then
        suspend;
    end
    else suspend;
end