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

FORWARDER_WEIGHDATE

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

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

Описание

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

Определение

CREATE PROCEDURE FORWARDER_WEIGHDATE(
    AEXPORTACTITEMID INTEGER,
    ENDDATE DATE,
    ISCURRENT INTEGER,
    APARTYDATE DATE)
RETURNS (
    TRANSPORTNUMBER VARCHAR(31),
    TRANSPORTWEIGHT BIGINT,
    TRANSPORTTESTWEIGHT BIGINT)
AS
begin
  for
    select ITTRANSPORT.TRANSPORTNUMBER,
        sum (case
               when (((EXPORTACTITEMS.EXPORTACTITEMID = :AEXPORTACTITEMID)
                      and (EXPORTACTITEMS.OPERATIONID in (1,17)))
                     or ((EXPORTACTITEMS.OPERATIONID in (22,64,70,65))
                         and (EXPORTACTITEMS.PARENTITEMID = :AEXPORTACTITEMID))) then
                 EXPORTACTITEMS.WEIGHT
               when ((EXPORTACTITEMS.OPERATIONID in (17,5)) and (EXPORTACTITEMS.WEIGHT > 0)
                     and (EXPORTACTITEMS.PARENTITEMID = :AEXPORTACTITEMID)) then
                 -EXPORTACTITEMS.WEIGHT
             end) TRANSPORTWEIGHT,
        sum (case
               when (((EXPORTACTITEMS.EXPORTACTITEMID = :AEXPORTACTITEMID)
                      and (EXPORTACTITEMS.OPERATIONID in (1,17)))
                     or ((EXPORTACTITEMS.OPERATIONID in (22,64,70,65))
                         and (EXPORTACTITEMS.PARENTITEMID = :AEXPORTACTITEMID))) then
                 EXPORTACTITEMS.TESTWEIGHT
               when ((EXPORTACTITEMS.OPERATIONID in (17,5)) and (EXPORTACTITEMS.TESTWEIGHT > 0)
                     and (EXPORTACTITEMS.PARENTITEMID = :AEXPORTACTITEMID)) then
                 -EXPORTACTITEMS.TESTWEIGHT
             end) TRANSPORTTESTWEIGHT
      from EXPORTACTITEMS
        left join EXPORTACTS EA on (EA.EXPORTACTID = EXPORTACTITEMS.EXPORTACTID)
        left join EXPORTACTITEMS_VIEW EAIV on (EAIV.EXPORTACTITEMID = EXPORTACTITEMS.PARENTITEMID)
        left join EXPORTACTS EAS on (EAS.EXPORTACTID = EAIV.EXPORTACTID)
        left join IT on (IT.INVENTORYID = EXPORTACTITEMS.INVENTORYID)
        join GETWORKPARTIES(:ISCURRENT, :APARTYDATE, null, null, null) on (GETWORKPARTIES.PARTYID = IT.PARTYID)
        left join ITTRANSPORT on ((case
                                     when (EXPORTACTITEMS.OPERATIONID in (1,17)) then
                                       EXPORTACTITEMS.INVENTORYID
                                     else EAIV.INVENTORYID
                                   end) = ITTRANSPORT.INVENTORYID)
      where ((EXPORTACTITEMS.EXPORTACTITEMID = :AEXPORTACTITEMID) -- сама запись
             or (EXPORTACTITEMS.parentITemid = :AEXPORTACTITEMID)) -- child
        and (EA.REGDATE <= :ENDDATE) and (IT.REGDATE <= :ENDDATE)
        --  and PArties.STATE = 1
        and (((EAS.REGDATE <= :ENDDATE) and (EAS.STATE <> 10)) or (EAS.EXPORTACTID is null))
        and (EA.STATE >= 0) and (EA.STATE <> 10) -- не учитываем удаленные и предварительные
        and (EXPORTACTITEMS.OPERATIONID <> -64) -- не учит. общие пред. списания
    group by ITTRANSPORT.TRANSPORTNUMBER
    into :TRANSPORTNUMBER, :TRANSPORTWEIGHT, :TRANSPORTTESTWEIGHT

  do suspend;
end