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

FORWARDER_WEIGHTITEM

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

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

Описание

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

Определение

CREATE PROCEDURE FORWARDER_WEIGHTITEM(
    ORDERLOADITEMID INTEGER,
    AINVENTORYID INTEGER,
    AEXPORTACTITEMID INTEGER,
    ISCURRENT INTEGER,
    APARTYDATE DATE)
RETURNS (
    TRANSPORTOUTWEIGHT BIGINT,
    TRANSPORTWEIGHT BIGINT,
    CUSTOMRENET BIGINT,
    CUSTOMWASTE BIGINT,
    CUSTOMOUTNET BIGINT)
AS
BEGIN
  FOR
    select
      (select sum(case
                    when ((eaiv.operationid = 17  and  eaiv.parentitemid = eaimain.exportactitemid)
                         or (eaiv.operationid = 5))
                         and ((eaiv.inventoryid <> :ainventoryid) or (:ainventoryid is null)
                         and (
                         eas.orderitemid <> :orderloaditemid or (:orderloaditemid is null)
                           or (eas.orderitemid is null) )  ) --??
                    then -weight
                    when (eaiv.inventoryid <> :ainventoryid or (:ainventoryid is null))
                          and (eas.orderitemid <> :orderloaditemid or (eas.orderitemid is null)
                              or (:orderloaditemid is null))

                    then weight
                  end)
           from exportactitems_view eaiv
           left join exportacts eas on (eaiv.exportactid = eas.exportactid)
           where eaiv.state <> 10
           and ((eaiv.parentitemid = eaimain.exportactitemid)
              or (eaiv.exportactitemid = eaimain.exportactitemid)))
              as transportweight,
      (select sum(case
                    when ((eaivv.operationid = 17
                             and  eaivv.parentitemid = eaimain.exportactitemid)
                           or (eaivv.operationid = 5))
                         and  not(:orderloaditemid is null)
                    then -weight
                    when (eaivv.inventoryid <> :ainventoryid or (:ainventoryid is null))
                           and  not(:orderloaditemid is null)
                    then weight
                  end)
           from exportactitems_view eaivv
           left join exportacts eas on (eaivv.exportactid = eas.exportactid)
           where
                (eaivv.state <> 10 or (eas.orderitemid = :orderloaditemid ))
           and ((eaivv.parentitemid = eaimain.exportactitemid)
              or (eaivv.exportactitemid = eaimain.exportactitemid)))
              as transportoutweight,

      (select sum(weight)
           from exportactitems eai17
           left join exportacts easec on (eai17.exportactid = easec.exportactid)
           where eai17.operationid = 17 and
             eai17.parentitemid = eaimain.exportactitemid
             and easec.state = 0
             and easec.orderitemid = :orderloaditemid)
            as customrenet,
      (select sum(weight)
           from exportactitems eai5
           left join exportacts easec on (eai5.exportactid = easec.exportactid)
           where eai5.operationid = 5 and
             eai5.parentitemid = eaimain.exportactitemid
             and easec.orderitemid = :orderloaditemid)
            as customoutnet,
      (select -sum(weight)
           from exportactitems eai22
           left join exportacts ea22 on (eai22.exportactid = ea22.exportactid)
           where eai22.operationid in (22,70,64,65) and
             eai22.parentitemid = eaimain.exportactitemid
             and eai22.inventoryid = :ainventoryid
             and ea22.state = 0
          )  as customwaste
    from exportactitems_view eaimain
      left join exportacts ea on (eaimain.exportactid = ea.exportactid)
      left join ittransport on (eaimain.inventoryid = ittransport.inventoryid)
      left join quality on (ea.qualityid = quality.qualityid)
      left join it on (eaimain.inventoryid = it.inventoryid)
      join getworkparties(:iscurrent,:apartydate,
         null, null,1)
          on (it.partyid = getworkparties.partyid)
    where eaimain.exportactitemid = :aexportactitemid
      and eaimain.operationid in (1,17)
      and eaimain.weight > 0
      and ea.state >=0
      and eaimain.state <> 10
    --  and Parties.State = 1
    INTO :transportweight,
         :transportoutweight,
         :customrenet,
         :customoutnet,
         :customwaste
  DO
  BEGIN
    SUSPEND;
  END
END