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

LABOUT_JOURNAL

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

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

Описание

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

Определение

CREATE PROCEDURE LABOUT_JOURNAL(
    BEGDATE DATE,
    ENDDATE DATE,
    STORAGEID INTEGER)
RETURNS (
    REGDATE DATE,
    INVENTORYID INTEGER,
    SUPLIERNAME VARCHAR(30),
    LABQUALITYID INTEGER,
    CROPCLASS VARCHAR(40),
    TRANSPORTNUMBER VARCHAR(55),
    NET INTEGER,
    USERNAME VARCHAR(20),
    CLASSID INTEGER,
    SAMPLERQUALITYID INTEGER,
    STORINGID INTEGER,
    RELATIONID INTEGER,
    NUMBER INTEGER,
    WORKINGDATE DATE,
    HUMIDITY NUMERIC(4,2),
    ASH NUMERIC(4,2),
    WHITES NUMERIC(4,2),
    GLUTEN NUMERIC(4,1),
    GLUTENQUALITY INTEGER,
    V_VOLUME INTEGER,
    H_D NUMERIC(4,2),
    DIRT NUMERIC(4,2),
    TRANSPORTTYPEID INTEGER,
    ROUTEID INTEGER)
AS
begin
  for
    SELECT IT.REGDATE, IT.InventoryID, clients.clientname, IT.LabQualityID, transporttype.transporttypename || ' ' || ittransport.TRANSPORTNUMBER,
      IT.NET, IT.storageid, it.samplerqualityid, it.storingid, it.relationid, ittransport.sampleroutdate, ittransport.transporttypeid, it.routeid,
      quality.classid, users.username, coalesce(CROP.shortcropname, '') || ' ' || coalesce(CLASS.shortclassname, '')
    FROM IT
      left join quality on quality.qualityid = coalesce(it.labqualityid, it.samplerqualityid)
      left join class on class.classid = quality.classid
      left join crop on crop.cropid = class.cropid
      left join clients on clients.clientid = it.providerid
      left join ittransport on it.inventoryid = ittransport.inventoryid
      left join transporttype on transporttype.transporttypeid = ittransport.transporttypeid
      left join users on (it.labqualityid is not null and quality.userid = users.userid)
    where (it.regdate between :begdate and :enddate) and it.operationid = 5 and it.parentid <= 0 and it.state > -1
      and it.storageid = :storageid and crop.croptypeid = 2
    order by IT.REGDATE, IT.InventoryID
    into :regdate, :inventoryid, :supliername, :labqualityid, :transportnumber,
      :net, :storageid, :samplerqualityid, :storingid, :relationid, :workingdate, :transporttypeid, :routeid,
      :classid, :username, :cropclass
  do begin
    select humidity, ash, whiteness, gluten, glutenquality, volumerecovery, relation_h_d, dirt, qualitynumber from quality_select(:labqualityid)
    into :humidity, :ash, :whites, :gluten, :glutenquality, :v_volume, :h_d, :dirt, :number;
    suspend;
  end
END