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

GRAIN_REST_ACCOUNT_AND_SILAGE

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

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

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

Описание

Зерно по бух учету и замерам

Определение

CREATE PROCEDURE GRAIN_REST_ACCOUNT_AND_SILAGE
RETURNS (
    CROPNAME VARCHAR(20),
    CLASSNAME VARCHAR(10),
    SORTNAME VARCHAR(20),
    REST_WEIGHT NUMERIC(15,3),
    METERING_WEIGHT INTEGER,
    CROPID INTEGER)
AS
DECLARE VARIABLE PARTYID INTEGER;
begin
  select partyid from parties where state = 1 into :partyid;
  for
    select sum(grain_rest_weight.rweight), cast(null as double precision),
      min(CROP.cropname), min(class.classname), min(cropsort.sortname), min(QUALITY.cropid)
    from IT
      left join storage on (it.storageid = storage.storageid)
      left join operation on (IT.operationid = operation.operationid)
      left join QUALITY on (IT.samplerqualityid = Quality.QUALITYID)
      left join grain_rest_weight(it.weight, it.operationid, operation.operationsubtype) on (0 = 0)
      inner join CLASS on (QUALITY.CLASSID = CLASS.CLASSID)
      left join cropsort on (QUALITY.sortid = cropsort.sortid)
      inner join CROP on (QUALITY.CROPID = CROP.CROPID)
    where It.partyid = :partyid
      and IT.State >= 0
      and IT.Net is not null and it.parentid <> -1
      and storage.storagetypeid = 1
      and operation.operationsubtype  in (0,1)
      and QUALITY.CROPID is not null
    group by QUALITY.classid, QUALITY.sortid

    union

    select cast(null as double precision), cast(SUM(silagequality.weight) as double precision),
      min(crop.cropname), min(class.classname), min(cropsort.sortname), min(QUALITY.cropid)
    from silagepacks
      inner join silagequality on (silagequality.silagequalityid = silagepacks.silagequalityid)
      inner join quality on (silagequality.qualityid = quality.qualityid)
      inner join crop on (quality.cropid = crop.cropid)
      inner join class on (quality.classid = class.classid)
      left join cropsort on (quality.sortid = cropsort.sortid)
    group by quality.classid, quality.sortid
    into
      :rest_weight,
      :metering_weight,                         
      :cropname,
      :classname,
      :sortname,
      :cropid
  do suspend;
END