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

DEVIATION_ALL

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

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

Описание

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

Определение

CREATE PROCEDURE DEVIATION_ALL(
    AOWNERID INTEGER,
    ATRANSPORTNUMBER VARCHAR(10),
    ASTATIONID INTEGER,
    ACROPID INTEGER)
RETURNS (
    OWNERNAME VARCHAR(30),
    PROVIDERNAME VARCHAR(30),
    TRANSPORTNUMBER VARCHAR(31),
    SAMPLEROUTDATE DATE,
    STATIONFULLNAME VARCHAR(128),
    CROPFULLNAME VARCHAR(150),
    DEVIATIONTYPENAME VARCHAR(50),
    DEVIATIONDATETIME TIMESTAMP,
    DEVIATIONSTATE SMALLINT,
    INVOICEID INTEGER,
    DEVIATIONTYPEID INTEGER,
    DISPATCHPRESENCE SMALLINT)
AS
begin
  for
    select OWNER.CLIENTNAME, PROVIDER.CLIENTNAME, ITTRANSPORT.TRANSPORTNUMBER,
        ITTRANSPORT.SAMPLEROUTDATE, STATIONS.STATIONFULLNAME,
        QUALITY_CROPFULLNAME.CROPFULLNAME, DEVIATIONTYPE.DEVIATIONTYPENAME,
        DEVIATION.DEVIATIONDATETIME, coalesce(DEVIATION.DEVIATIONSTATE,0),
        DEVIATION.INVOICEID, DEVIATION.DEVIATIONTYPEID,
        case
          when (bin_and(OWNER.DEVIATIONCONTROL, 131072) = 131072) then
            1
          else 0
        end DISPATCHPRESENCE
      from DEVIATION
        left join IT on (IT.INVOICEID = DEVIATION.INVOICEID)
        left join CLIENTS OWNER on (OWNER.CLIENTID = IT.OWNERID)
        left join CLIENTS PROVIDER on (PROVIDER.CLIENTID = IT.PROVIDERID)
        left join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = IT.INVENTORYID)
        left join INVOICES on (INVOICES.INVOICEID = DEVIATION.INVOICEID)
        left join STATIONS on (STATIONS.STATIONID = INVOICES.STATIONID)
        left join QUALITY on (QUALITY.QUALITYID = IT.SAMPLERQUALITYID)
        left join QUALITY_CROPFULLNAME(QUALITY.CROPID, QUALITY.CLASSID, QUALITY.SORTID, 1) on (0=0)
        left join DEVIATIONTYPE on (DEVIATIONTYPE.DEVIATIONTYPEID = DEVIATION.DEVIATIONTYPEID)
      where (coalesce(DEVIATION.DEVIATIONSTATE,0) < 2)
        and ((coalesce(:AOWNERID,-1) = -1) or (IT.OWNERID = :AOWNERID))
        and ((:ATRANSPORTNUMBER = '') or (ITTRANSPORT.TRANSPORTNUMBER containing :ATRANSPORTNUMBER))
        and ((coalesce(:ASTATIONID,-1) = -1) or (STATIONS.STATIONID = :ASTATIONID))
        and ((coalesce(:ACROPID,-1) = -1) or (QUALITY.CROPID = :ACROPID))
    order by DEVIATION.DEVIATIONDATETIME, DEVIATION.INVOICEID,
        DEVIATIONTYPE.DEVIATIONSUBTYPE, DEVIATION.DEVIATIONTYPEID

    into :OWNERNAME, :PROVIDERNAME, :TRANSPORTNUMBER, :SAMPLEROUTDATE,
        :STATIONFULLNAME, :CROPFULLNAME, :DEVIATIONTYPENAME, :DEVIATIONDATETIME,
        :DEVIATIONSTATE, :INVOICEID, :DEVIATIONTYPEID, :DISPATCHPRESENCE

  do suspend;
end