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

FORWARDER_FIRMACTITEM

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

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

Описание

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

Определение

CREATE PROCEDURE FORWARDER_FIRMACTITEM(
    AEXPORTACTID INTEGER,
    ASECEXPORTACTID INTEGER,
    AOPERATIONID INTEGER,
    ISCURRENT INTEGER,
    APARTYDATE DATE)
RETURNS (
    EXPORTACTID INTEGER,
    TRANSPORTNUMBER VARCHAR(31),
    OPERATIONID INTEGER,
    ACTNUMBER VARCHAR(10),
    NET INTEGER,
    RESTNET BIGINT,
    PROVIDERFULLNAME VARCHAR(100),
    INVOICENUMBER VARCHAR(31),
    REGDATE DATE)
AS
BEGIN
  if (:aoperationid = 5) then
  begin
  FOR
    select
      case
        when eaiv.operationid <> 64 and eaiv.weight < 0
        then eathird.exportactid
        else easec.exportactid
       end exportactid,  --1
       case
         when eaiv.weight > 0 then tr1.transportnumber
         when eaiv.weight < 0 then tr2.transportnumber
       end transportnumber, --2
       it1.operationid,
       case
         when (eaiv.operationid = 5 and eaiv.weight < 0 )
         then eathird.actnumber
         else  easec.actnumber
       end as actnumber,
       case
         when eaiv.weight < 0
         then -eaiv.weight else eaiv.weight
       end as net,
       case
         when it1.operationid = 1
         then (select sum(forwarder_weightitem_storage.restweight)
              from forwarder_weightitem_storage (null, eaivsec.exportactitemid, eaivsec.exportactid, 0))
       end as restnet,
       case
         when it1.operationid in (1,45)  then clients.clientfullname
         when it1.operationid = 22 then 'Отходы 3 кат.'
       end  as providerfullname,
       invoices.invoicenumber,
       case
         when eaiv.operationid = 17 then ea.regdate
         else it1.regdate
       end
       from exportacts ea
         left join exportactitems eaiv  on (ea.exportactid = eaiv.exportactid)
         left join exportactitems_view eaivsec on (eaiv.parentitemid = eaivsec.exportactitemid)
         left join exportacts easec on (eaivsec.exportactid = easec.exportactid)
         left join exportactitems_view eaivthird on (eaivsec.parentitemid = eaivthird.exportactitemid)
         left join exportacts eathird on (eaivthird.exportactid = eathird.exportactid)
         left join clients on ((case when eaiv.operationid = 5 and eaiv.weight < 0
                                  then eathird.providerid else easec.providerid end) = clients.clientid)
         left join it it1 on (eaiv.inventoryid = it1.inventoryid)
         left join ittransport tr1 on (eaiv.inventoryid = tr1.inventoryid)
         left join ittransport tr2 on (eaivsec.inventoryid = tr2.inventoryid)
         left join operation on (it1.operationid = operation.operationid)
         left join invoices on (it1.invoiceid = invoices.invoiceid)
       where ea.exportactid = :aexportactid
         and ((easec.exportactid = :asecexportactid
              and (eaivsec.weight > 0
                   or (eaivsec.weight is null)
                   or (eaiv.operationid = 64) ))
               or (eathird.exportactid = :asecexportactid and eaiv.weight < 0))
         and eaiv.operationid <> -64
    order by  4,2,3,5
    INTO :exportactid,
         :transportnumber,
         :operationid,
         :actnumber,
         :net,
         :restnet,
         :providerfullname,
         :invoicenumber,
         :regdate
  DO
  BEGIN
    SUSPEND;
  END
  end
  else
  FOR
  select
  case
     when (:asecexportactid is null or (:operationid = 1))
    then ea.exportactid
    else easec.exportactid
   end exportactid,
   case
     when eaiv.weight > 0 then tr1.transportnumber
     when eaiv.weight < 0 then tr2.transportnumber
   end transportnumber, --2
   it1.operationid,
   case
     when (:asecexportactid is null or (:aoperationid = 1))
     then ea.actnumber
     else  easec.actnumber
   end as actnumber,
   eaiv.weight as net,
   case          --???
     when :aoperationid = 1 --and coalesce(:asecexportactid,-1) = -1
         then (select sum(forwarder_weightitem_storage.restweight)
              from forwarder_weightitem_storage (null, eaiv.exportactitemid, eaiv.exportactid, 0))

     when it1.operationid = 1 and not coalesce(:asecexportactid,-1) = -1
         then (select sum(forwarder_weightitem_storage.restweight)
              from forwarder_weightitem_storage (null, eaivsec.exportactitemid, eaivsec.exportactid, 0))
   end as restnet,
   case
     when it1.operationid in (1,45)  then clients.clientfullname
      when it1.operationid = 22 then 'Отходы 3 кат.'
    else operation.operationname
   end  as providerfullname,
   invoices.invoicenumber,
   case
     when eaiv.operationid = 17 then easec.regdate
     else it1.regdate
   end regdate

   from exportacts ea
     left join exportactitems eaiv  on (ea.exportactid = eaiv.exportactid)
     left join exportactitems_view eaivsec on (eaiv.parentitemid = eaivsec.exportactitemid)
     left join exportacts easec on (eaivsec.exportactid = easec.exportactid)
     left join it it1 on (eaiv.inventoryid = it1.inventoryid)
     left join clients on (
        (case
           when :aoperationid = 1 then it1.providerid
           when  (:aoperationid <> 1) and :asecexportactid is null then ea.providerid
           else easec.providerid
         end) = clients.clientid)
     left join ittransport tr1 on (eaiv.inventoryid = tr1.inventoryid)
     left join ittransport tr2 on (eaivsec.inventoryid = tr2.inventoryid)
     left join operation on (it1.operationid = operation.operationid)
     left join invoices on (it1.invoiceid = invoices.invoiceid)
     where
       ea.exportactid = :aexportactid
    INTO :exportactid,
         :transportnumber,
         :operationid,
         :actnumber,
         :net,
         :restnet,
         :providerfullname,
         :invoicenumber,
         :regdate
  DO
  BEGIN
    SUSPEND;
  END
END