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

FORWARDER_SEL_REBUILD

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

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

Описание

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

Определение

CREATE PROCEDURE FORWARDER_SEL_REBUILD(
    AEXPORTACTID INTEGER)
RETURNS (
    INVOICEID INTEGER,
    ACTDATE DATE,
    OWNERID INTEGER,
    EXPORTACTITEMID INTEGER,
    INVENTORYID INTEGER,
    EXPORTACTID INTEGER,
    ACTNUMBER VARCHAR(10),
    TRANSPORTSUBTYPEID INTEGER,
    OOPERATIONID INTEGER,
    ALLWEIGHT INTEGER,
    REGDATE DATE,
    TRANSPORTNUMBER VARCHAR(31),
    ORDERITEMID INTEGER,
    CROPID INTEGER,
    CLASSID INTEGER,
    TRNUMBER VARCHAR(31),
    PARENTITEMID INTEGER)
AS
begin
  for
    select
      exportacts.exportactid,
      exportacts.actnumber,
      exportacts.ownerid,
      exportacts.actdate,
      exportacts.regdate,
      exportacts.orderitemid,
      exportacts.transportsubtypeid,
      t1.transportnumber,
      eai.operationid,
      eai.inventoryid,
      quality.cropid,
      quality.classid,
      eai.exportactitemid,
      eai.parentitemid,
      t2.transportnumber as trnumber,
      it.invoiceid,
      case
        when eai.operationid = 1
        then (select sum (case when eaiv1.operationid in (5,17)   and (eaiv1.parentitemid = eai.exportactitemid)
                            then -weight else weight
                          end)
              from exportactitems_view eaiv1
              where eaiv1.state <> 10 and
                ((eaiv1.exportactitemid = eai.exportactitemid) or (eaiv1.parentitemid = eai.exportactitemid)))
        when eai.operationid  = 17
        then (select sum (weight)
              from exportactitems_view eaiv17
              where eaiv17.exportactitemid = eai.exportactitemid and eaiv17.state <> 10)
      end allweight
    from exportacts
      left join exportactitems_view eai on (exportacts.exportactid = eai.exportactid)
      left join exportactitems_view eaisec on (eai.parentitemid = eaisec.exportactitemid)
      left join it on (eai.inventoryid = it.inventoryid)
      left join quality on (exportacts.qualityid = quality.qualityid)
      left join ittransport t1 on (eai.inventoryid = t1.inventoryid)
      left join ittransport t2 on (eaisec.inventoryid = t2.inventoryid)
    where 
      exportacts.exportactid = :aexportactid
      and ((eai.operationid = 1)
           or ((eai.operationid = 17) and ((eai.weight > 0 ) or (eai.weight is null))))
    into
         :exportactid,
         :actnumber,
         :ownerid,
         :actdate,
         :regdate,
         :orderitemid,
         :transportsubtypeid,
         :transportnumber,
         :ooperationid,
         :inventoryid,
         :cropid,
         :classid,
         :exportactitemid,
         :parentitemid,
         :trnumber,
         :invoiceid,
         :allweight
  do
  begin
    suspend;
  end
end