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

RRW_JOURNAL

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

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

Описание

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

Определение

CREATE PROCEDURE RRW_JOURNAL(
    BEGINDATE DATE,
    ENDDATE DATE,
    ASESSION INTEGER,
    AOPERATIONID INTEGER,
    ASTORAGEID INTEGER,
    SHOWDELETED INTEGER,
    SHOWNOTWEIGHTED INTEGER,
    SHOWREMOTERECORDS INTEGER)
RETURNS (
    INVENTORYID INTEGER,
    REGDATE DATE,
    ORDERLOADID INTEGER,
    ORDERLOADITEMID INTEGER,
    TRANSPORTNUMBER VARCHAR(25),
    CAPACITYWEIGHT INTEGER,
    CAPACITYVOLUME NUMERIC(9,2),
    INVOICEID INTEGER,
    STORAGEID INTEGER,
    GROSSTIME TIMESTAMP,
    TARETIME TIMESTAMP,
    GROSS INTEGER,
    CARGROSS INTEGER,
    TARE INTEGER,
    CARTARE INTEGER,
    NET INTEGER,
    CARNET INTEGER,
    INVOICEDATE DATE,
    INVOICENUMBER VARCHAR(31),
    INVOICEGROSS INTEGER,
    INVOICETARE INTEGER,
    INVOICENET INTEGER,
    CONTRACTID INTEGER,
    WEIGHTNUMBER INTEGER,
    NETDEFICITE BIGINT,
    SAMPLERQUALITYID INTEGER,
    LABQUALITYID INTEGER,
    TRANSPORTTYPEID INTEGER,
    STATIONID INTEGER,
    STATIONNAME VARCHAR(30),
    OPERATIONID INTEGER,
    CROPID INTEGER,
    CLASSID INTEGER,
    SORTID INTEGER,
    CROPFULLNAME VARCHAR(300),
    SAMPLERINDATE DATE,
    OWNERID INTEGER,
    OWNERNAME VARCHAR(30),
    PROVIDERID INTEGER,
    PROVIDERNAME VARCHAR(30),
    SENDERID INTEGER,
    SENDERNAME VARCHAR(30),
    EXPORTERID INTEGER,
    AGREEMENTNUMBER VARCHAR(10),
    STATE INTEGER,
    STORINGID INTEGER,
    WEIGHTSESSION INTEGER,
    GROSSBYHAND SMALLINT,
    TAREBYHAND SMALLINT,
    SAMPLEROUTDATE DATE,
    REGIONID INTEGER)
AS
begin
  for
    select
      IT.INVENTORYID, IT.REGDATE, IT.OPERATIONID, IT.ORDERLOADITEMID,
      IT.INVOICEID, IT.STORAGEID, IT.OWNERID, IT.PROVIDERID, IT.GROSS, IT.TARE,
      IT.NET, IT.GROSSTIME, IT.TARETIME, IT.SAMPLERQUALITYID, IT.LABQUALITYID,
      IT.CONTRACTID, IT.WEIGHTNUMBER, IT.STATE, IT.STORINGID,
      (IT.NET - ITTRANSPORT.INVOICENET) NETDEFICITE,
      ITTRANSPORT.TRANSPORTNUMBER, ITTRANSPORT.CAPACITYWEIGHT, ITTRANSPORT.CAPACITYVOLUME,
      ITTRANSPORT.INVOICEGROSS, ITTRANSPORT.INVOICETARE, ITTRANSPORT.INVOICENET,
      ITTRANSPORT.TRANSPORTTYPEID, ITTRANSPORT.SAMPLERINDATE, ITTRANSPORT.WEIGHTSESSION,
      ITTRANSPORT.GROSSBYHAND, ITTRANSPORT.TAREBYHAND, ITTRANSPORT.SAMPLEROUTDATE
    from ITTRANSPORT
      left join IT on (IT.INVENTORYID = ITTRANSPORT.INVENTORYID)
    where ((coalesce(ITTRANSPORT.SAMPLERINDATE,ITTRANSPORT.SAMPLEROUTDATE) between (:BEGINDATE - 10) and :ENDDATE)
           and (((((IT.STATE >= 0) or (IT.STATE = -100) or (IT.STATE = -104))
                  and ((IT.NET <= 0) or (IT.NET is null)) and (:SHOWNOTWEIGHTED = 1)
                 )
                 or ((IT.STATE = -100) and (IT.NET >= 0))
                )
                or ((IT.REGDATE between :BEGINDATE and :ENDDATE)
                    and (((IT.NET > 0) and (IT.STATE >= 0))
                         or ((:SHOWDELETED = 1) and (IT.STATE = -1))
                         or ((:SHOWREMOTERECORDS = 1) and (IT.STATE in (-3,-2)))
                        )
                   )

--                or (((IT.NET > 0) and ((IT.REGDATE between :BEGINDATE and :ENDDATE) and (IT.STATE >= 0))))
                or ((ITTRANSPORT.SAMPLERINDATE between :BEGINDATE and :ENDDATE)
                    and (((IT.STATE = -1) and (:SHOWDELETED = 1))
                         or ((IT.STATE = -3) and (:SHOWREMOTERECORDS = 1))
                        )
                   )
               )
          )
      and (ITTRANSPORT.TRANSPORTTYPEID = 2) and (IT.PARENTID <= 0)
      and (IT.OPERATIONID = :AOPERATIONID)
      and ((IT.STORAGEID = :ASTORAGEID) or (:ASTORAGEID = -1))
      and ((ITTRANSPORT.WEIGHTSESSION = :ASESSION) or (:ASESSION = -1) or ((IT.NET <= 0) or (IT.NET is null)))
    order by WEIGHTNUMBER
    into :INVENTORYID, :REGDATE, :OPERATIONID, :ORDERLOADITEMID,
         :INVOICEID, :STORAGEID, :OWNERID, :PROVIDERID, :CARGROSS, :CARTARE,
         :CARNET, :GROSSTIME, :TARETIME, :SAMPLERQUALITYID, :LABQUALITYID,
         :CONTRACTID, :WEIGHTNUMBER, :STATE, :STORINGID,
         :NETDEFICITE, :TRANSPORTNUMBER, :CAPACITYWEIGHT, :CAPACITYVOLUME,
         :INVOICEGROSS, :INVOICETARE, :INVOICENET,
         :TRANSPORTTYPEID, :SAMPLERINDATE, :WEIGHTSESSION,
         :GROSSBYHAND, :TAREBYHAND, :SAMPLEROUTDATE
  do begin
    GROSS = null;
    TARE = null;
    NET = null;

    if (STATE >= 0) then begin
      GROSS = :CARGROSS;
      TARE = :CARTARE;
      NET = :CARNET;
    end

    select INVOICES.INVOICEDATE, INVOICES.INVOICENUMBER, INVOICES.SENDERID,
        INVOICES.EXPORTERID, INVOICES.AGREEMENTNUMBER,
        INVOICES.STATIONID, STATIONS.STATIONNAME,
        QUALITY_SELECT.CROPID, QUALITY_SELECT.CLASSID, QUALITY_SELECT.SORTID,
        QUALITY_CROPFULLNAME_BYID.CROPFULLNAME,
        OWNERS.CLIENTNAME, PROVIDERS.CLIENTNAME, SENDERS.CLIENTNAME,
        ORDERLOADITEMS.ORDERLOADITEMID, QUALITY_SELECT.REGIONID
      from INVOICES
        left join STATIONS on (STATIONS.STATIONID = INVOICES.STATIONID)
        left join QUALITY_SELECT(:SAMPLERQUALITYID) on (0=0)
        left join QUALITY_CROPFULLNAME_BYID(:SAMPLERQUALITYID, 1) on (1=1)
        left join CLIENTS OWNERS on (OWNERS.CLIENTID = :OWNERID)
        left join CLIENTS PROVIDERS on (PROVIDERS.CLIENTID = :PROVIDERID)
        left join CLIENTS SENDERS on (SENDERS.CLIENTID = INVOICES.SENDERID)
        left join ORDERLOADITEMS on (ORDERLOADITEMS.ORDERLOADITEMID = :ORDERLOADITEMID)
      where (INVOICES.INVOICEID = :INVOICEID)
    into :INVOICEDATE, :INVOICENUMBER, :SENDERID, :EXPORTERID, :AGREEMENTNUMBER,
         :STATIONID, :STATIONNAME, :CROPID, :CLASSID, :SORTID, :CROPFULLNAME,
         :OWNERNAME, :PROVIDERNAME, :SENDERNAME, :ORDERLOADITEMID, :REGIONID;

    suspend;
  end
end