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

FORWARDER_WEIGH64WASTE

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

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

Описание

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

Определение

CREATE PROCEDURE FORWARDER_WEIGH64WASTE(
    AORDERITEMID INTEGER,
    AINVENTORYID INTEGER,
    AOPERATIONID INTEGER,
    AEXPORTACTITEMID INTEGER,
    ISWEIGHT SMALLINT,
    EA_OPERATIONID SMALLINT)
RETURNS (
    TRANSPORTWEIGHT BIGINT,
    SUMWASTENET BIGINT,
    CUSTOMWASTE BIGINT,
    READ_REC INTEGER,
    CHILD_EAI INTEGER)
AS
begin
  for
    select
        sum(case
              when (((EAIMAIN.EXPORTACTITEMID = :AEXPORTACTITEMID) and (EAIMAIN.OPERATIONID in (1,17)))
                    or ((EAIMAIN.OPERATIONID in (13,22,64,65,70)) and (EAIMAIN.PARENTITEMID = :AEXPORTACTITEMID))) then
                case
                  when (:ISWEIGHT = 1) then
                    case
                      when (:EA_OPERATIONID in (22)) then
                        EAIMAIN.WEIGHT - EAIMAIN.TESTWEIGHT
                      else EAIMAIN.TESTWEIGHT
                    end
                  else EAIMAIN.WEIGHT
                end
              when ((EAIMAIN.OPERATIONID in (5,17)) and (EAIMAIN.WEIGHT > 0) and (EAIMAIN.PARENTITEMID = :AEXPORTACTITEMID)) then
                case
                  when (:ISWEIGHT = 1) then
                    -(EAIMAIN.WEIGHT - EAIMAIN.TESTWEIGHT)
                  else -EAIMAIN.WEIGHT
                end
            end) TRANSPORTWEIGHT,
        sum(case
              when ((EAIMAIN.OPERATIONID in (13,22,64,65,70)) and (EAIMAIN.PARENTITEMID = :AEXPORTACTITEMID)) then
                -EAIMAIN.WEIGHT
            end) SUMWASTENET,
        sum(case
              when ((EAIMAIN.INVENTORYID = :AINVENTORYID)
                    and ((EAIMAIN.EXPORTACTITEMID = :AEXPORTACTITEMID)
                         or (EAIMAIN.PARENTITEMID = :AEXPORTACTITEMID))) then
                -EAIMAIN.WEIGHT
            end) CUSTOMWASTE,
        sum(case
              when (((EAIV.PARENTITEMID = :AEXPORTACTITEMID) and (EAIMAIN.INVENTORYID = :AINVENTORYID)
                     and (EAIMAIN.OPERATIONID = 5) and (EA.STATE = 1) and (EAIMAIN.WEIGHT < 0))
                    or ((EAIMAIN.INVENTORYID = :AINVENTORYID) and (:AOPERATIONID = 64)
                        and (EAIMAIN.OPERATIONID = 64) and (EAIV.EXPORTACTITEMID = :AEXPORTACTITEMID)
                        and (EAIMAIN.WEIGHT < 0) and (EA.OPERATIONID = 5) and (EA.STATE = 1))) then
                1
              else 0
            end),
        sum(case
              when ((EAIMAIN.INVENTORYID = :AINVENTORYID) and (EAIMAIN.OPERATIONID = :AOPERATIONID)
                    and (EAIMAIN.PARENTITEMID = :AEXPORTACTITEMID)) then
                EAIMAIN.EXPORTACTITEMID
            end) CHILD_EAI
      from EXPORTACTITEMS EAIMAIN
        left join EXPORTACTS EA on (EA.EXPORTACTID = EAIMAIN.EXPORTACTID)
        left join EXPORTACTITEMS_VIEW EAIV on (EAIMAIN.PARENTITEMID = EAIV.EXPORTACTITEMID)
      where ((EAIMAIN.EXPORTACTITEMID = :AEXPORTACTITEMID) -- сама запись
             or (EAIMAIN.PARENTITEMID = :AEXPORTACTITEMID)
             or (EAIMAIN.INVENTORYID = :AINVENTORYID)) -- child
        and (EA.STATE >= 0) -- учесть удаленные
        and ((EA.STATE <> 10) -- учитывать предварительные
             or ((EA.STATE = 10) and (EA.ORDERITEMID = :AORDERITEMID) and (:AOPERATIONID = 64))
             or ((EA.STATE = 10) and (EA.ORDERITEMID is null) and (:AOPERATIONID = 64)
                 and (EA.EXPORTACTID = (select EXPORTACTITEMS.EXPORTACTID
                                          from EXPORTACTITEMS
                                          where (EXPORTACTITEMS.INVENTORYID = :AINVENTORYID)
                                            and (EXPORTACTITEMS.OPERATIONID = -64)))))
        and (EAIMAIN.OPERATIONID <> -64) -- не учит. общие пред. списания
    --      and not (EAIMAIN.OPERATIONID = 5 and EAIMAIN.WEIGHT < 0) -- не учит. отгруж. отходы
    into :TRANSPORTWEIGHT, :SUMWASTENET, :CUSTOMWASTE, :READ_REC, :CHILD_EAI

  do suspend;

end