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

ACC_MOVE_REPORT_ILLICHEVSK

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

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

Описание

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

Определение

CREATE PROCEDURE ACC_MOVE_REPORT_ILLICHEVSK(
    BEGINDATE DATE,
    ENDDATE DATE,
    PARTYID INTEGER,
    PARTYDATE DATE,
    STORAGEID INTEGER,
    OWNERID INTEGER)
RETURNS (
    INCOMENETAVTO INTEGER,
    INCOMENETRW INTEGER,
    OUTCOMENET INTEGER,
    CROPFULLNAME VARCHAR(100),
    INITIAL_NET INTEGER,
    AVALIABLE_NET INTEGER,
    ACROPID INTEGER,
    CROPNAME VARCHAR(20))
AS
declare variable ACLASSID integer;
begin
  ACLASSID = null;
  ACROPID = null;
  for
    select 
      CROPNAME, CLASS.CROPID, CLASS.CLASSID, QUALITY_CROPFULLNAME.CROPFULLNAME
    from CROP
      left join CLASS on (CLASS.CROPID = CROP.CROPID)
      left join QUALITY_CROPFULLNAME(CROP.CROPID, CLASS.CLASSID, null, 0) on (0 = 0)
    where (Crop.Croptypeid in (1, 4, 5))
      and (CROP.STATE = 1)
    order by QUALITY_CROPFULLNAME.CROPFULLNAME
    into :CROPNAME, :ACROPID, :ACLASSID, :CROPFULLNAME
  do begin
    INCOMENETAVTO = 0;
    INCOMENETRW = 0;
    OUTCOMENET = 0;
    INITIAL_NET = 0;
    Avaliable_NET = 0;
    for
      select
        coalesce(sum(case
          when ((IT.OPERATIONID = 1) and (ITTRANSPORT.transporttypeid in (0, 1)) and (IT.REGDATE >= :BEGINDATE)) then
            IT.NET
        end), 0) as INCOMENETAVTO,
        coalesce(sum(case
          when ((IT.OPERATIONID = 1) and (ITTRANSPORT.transporttypeid = 2) and (IT.REGDATE >= :BEGINDATE)) then
            IT.NET
        end), 0) as INCOMENETRW,
        coalesce(sum(case
          when ((IT.OPERATIONID = 5) and (IT.REGDATE >= :BEGINDATE)) then
            IT.NET
        end), 0) as OUTCOMENET,
        coalesce(sum(case
          when ((IT.OPERATIONID = 1) and (IT.REGDATE < :BEGINDATE)) then
            IT.NET
          when ((IT.OPERATIONID = 5) and (IT.REGDATE < :BEGINDATE)) then
            -IT.NET
        end), 0) as INITIAL_NET
      from IT
        join GETWORKPARTIES(:PARTYID, :PARTYDATE, :ACROPID, :ACLASSID, :STORAGEID) on (GETWORKPARTIES.PARTYID = IT.PARTYID)
        left join QUALITY on (QUALITY.QUALITYID = IT.SAMPLERQUALITYID)
        left join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = IT.INVENTORYID)
      where (IT.REGDATE <= :ENDDATE)
        and ((QUALITY.CROPID = :ACROPID) and (QUALITY.CLASSID = :ACLASSID))
        and ((IT.STATE >= 0) and (IT.PARENTID >= 0))
        and ((not IT.NET is null) and (IT.NET <> 0))
        and (IT.OPERATIONID in (1, 5))
        and ((IT.STORAGEID = :STORAGEID) or (coalesce(:STORAGEID, -1) = -1))
        and ((IT.OWNERID = :OWNERID) or (coalesce(:OWNERID, -1) = -1))
      into :INCOMENETAVTO, :INCOMENETRW, :OUTCOMENET, :INITIAL_NET
    do begin
      AVALIABLE_NET = :INITIAL_NET + :INCOMENETAVTO + :INCOMENETRW - :OUTCOMENET;
      if ((:INCOMENETAVTO <> 0) or (:INCOMENETRW <> 0) or (:OUTCOMENET <> 0) or (:INITIAL_NET <> 0)) then
        suspend;
    end
  end
end