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

ACN_FORMA30_UWACT

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

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

Описание

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

Определение

CREATE PROCEDURE ACN_FORMA30_UWACT(
    DATE1 DATE,
    DATE2 DATE,
    CROPID INTEGER,
    CLASSID INTEGER,
    SORTID INTEGER,
    STORAGEID INTEGER,
    STORINGID INTEGER,
    NOTSHOWDRYING INTEGER,
    ALWAYSSHOWUWACTS INTEGER,
    PARTYID INTEGER,
    NEWEDITION INTEGER)
RETURNS (
    UWACTDATE DATE,
    UWACTN VARCHAR(31),
    CLEARINGKIND VARCHAR(254),
    NET INTEGER,
    BEFORECLEANWEIGHT INTEGER,
    BEFOREDRYINGWEIGHT INTEGER,
    POB INTEGER,
    FORAGE3C INTEGER,
    DRYINGWEIGHT INTEGER,
    WORKTYPE INTEGER,
    LINENUMBER INTEGER)
AS
declare variable MAXLINENUMBER integer = 5;
begin

  LINENUMBER = 0;

  if (:NEWEDITION = 1) then
    MAXLINENUMBER = 3;

  for

    select
        UWACTS.UWACTDATE, cast(UWACTS.UWACTN as varchar(31)),
        case
          when (position('_', PATH.UWFULLNAME) > 0) then
            substring(PATH.UWFULLNAME from 1 for (position('_', PATH.UWFULLNAME) - 1))
          else PATH.UWFULLNAME
        end,
        UWORDERS.BEFORECLEANINGWEIGHT, UWORDERS.BEFORECLEANINGWEIGHT, 0,
        cast(coalesce(UWACTS.FORAGE_BY_PRODUCT_A,0) + coalesce(UWACTS.FORAGE_BY_PRODUCT_B,0) +
          coalesce(UWACTS.FORAGE1C_A,0) + coalesce(UWACTS.FORAGE1C_B,0) +
          coalesce(UWACTS.FORAGE2C,0) as integer),
        UWACTS.FORAGE3C, 0, 1
      from UWORDERS
        left join UWACTS on (UWACTS.UWORDERID = UWORDERS.UWORDERID)
        left join UWTYPES on (UWTYPES.UWTYPEID = UWORDERS.UWTYPEID)
        left join UWTYPES PATH on (PATH.UWTYPEID = UWORDERS.PATHID)
        left join QUALITY Q on (UWORDERS.BEFOREQUALITYID = Q.QUALITYID)
      where (UWORDERS.UWTYPEID in (1,2,3,34,90,91))
        and (UWACTS.UWACTDATE between :DATE1 and :DATE2)
        and (coalesce(UWORDERS.PARTYID,:PARTYID) = :PARTYID)
        and (UWORDERS.STATE >= 0)
        and (UWACTS.CLEAN > 0)
        and ((Q.CROPID = :CROPID) and ((Q.CLASSID = :CLASSID) or (coalesce(:CLASSID,-1) = -1)))
        and ((Q.SORTID = :SORTID) or (coalesce(:SORTID,-1) = -1))
        and (UWORDERS.STORAGEID = :STORAGEID)
        and ((UWORDERS.STORINGID = :STORINGID) or (coalesce(:STORINGID,-1) = -1))
    union
      select
          UWACTS.UWACTDATE, cast(UWACTS.UWACTN as varchar(31)),
          case
            when (position('_', PATH.UWFULLNAME) > 0) then
              substring(PATH.UWFULLNAME from (position('_', PATH.UWFULLNAME) + 1))
            else PATH.UWFULLNAME
          end,
          UWORDERS.BEFOREDRYINGWEIGHT, 0, UWORDERS.BEFOREDRYINGWEIGHT,
          0, 0, (UWORDERS.BEFOREDRYINGWEIGHT - UWACTS.AFTERDRYINGWEIGHT), 2
        from UWORDERS
          left join UWACTS on (UWACTS.UWORDERID = UWORDERS.UWORDERID)
          left join UWTYPES on (UWTYPES.UWTYPEID = UWORDERS.UWTYPEID)
          left join UWTYPES PATH on (PATH.UWTYPEID = UWORDERS.PATHID)
          left join QUALITY Q on (UWORDERS.BEFOREQUALITYID = Q.QUALITYID)
        where (UWORDERS.UWTYPEID in (1,2,3,5,34,90))
          and (UWACTS.UWACTDATE between :DATE1 and :DATE2)
          and (coalesce(UWORDERS.PARTYID,:PARTYID) = :PARTYID)
          and (UWORDERS.STATE >= 0)
          and ((UWACTS.DRYING > 0) and (:NOTSHOWDRYING = 0))
          and ((Q.CROPID = :CROPID) and ((Q.CLASSID = :CLASSID) or (coalesce(:CLASSID,-1) = -1)))
          and ((Q.SORTID = :SORTID) or (coalesce(:SORTID,-1) = -1))
          and (UWORDERS.STORAGEID = :STORAGEID)
          and ((UWORDERS.STORINGID = :STORINGID) or (coalesce(:STORINGID,-1) = -1))
     order by 1, 2, 10
    into :UWACTDATE, :UWACTN, :CLEARINGKIND,
          :NET, :BEFORECLEANWEIGHT, :BEFOREDRYINGWEIGHT,
          :POB, :FORAGE3C, :DRYINGWEIGHT, :WORKTYPE

  do begin
    LINENUMBER = :LINENUMBER + 1;
    suspend;
  end

  if ((:LINENUMBER < MAXLINENUMBER) and (:ALWAYSSHOWUWACTS < 2)) then begin
    UWACTDATE = null;
    UWACTN = null;
    CLEARINGKIND = null;
    NET = null;
    BEFORECLEANWEIGHT = null;
    BEFOREDRYINGWEIGHT = null;
    POB = null;
    FORAGE3C = null;
    DRYINGWEIGHT = null;
    WORKTYPE = null;

    while (:LINENUMBER < MAXLINENUMBER) do begin
      LINENUMBER = :LINENUMBER + 1;
      suspend;
    end
  end

end