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

FORWARDER_FIRMITEM

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

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

Описание

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

Определение

CREATE PROCEDURE FORWARDER_FIRMITEM(
    AORDERITEMID INTEGER,
    AEXPORTACTID INTEGER,
    AINVENTORYID INTEGER,
    AOWNERID INTEGER,
    ISCURRENT INTEGER,
    APARTYDATE DATE)
RETURNS (
    EXPORTACTID INTEGER,
    TRANSPORTNUMBER VARCHAR(31),
    OPERATIONID INTEGER,
    INVOICENUMBER VARCHAR(31),
    INVOICENET INTEGER,
    NET INTEGER,
    DEFICIT BIGINT,
    REGDATE DATE,
    INVOICEID INTEGER,
    ACTDATE DATE,
    PID INTEGER,
    OWNERID INTEGER,
    PARTYDATE DATE)
AS
declare variable texportactitemid integer;
BEGIN
  partydate = :apartydate;
  if (:ainventoryid is null) then
  begin
    FOR                         
      select
        exportactitems.exportactitemid
      from exportacts
       left join exportactitems on (exportacts.exportactid = exportactitems.exportactid)
      where exportacts.exportactid = :aexportactid
        and exportacts.operationid <> 5
        and (exportactitems.operationid in (1,17,45)
             or (exportactitems.operationid = 64))
      into :texportactitemid
      DO
      BEGIN
        FOR
          select
            cast(ea.exportactid as integer) exportactid,  --1
            case
              when eaiv.weight < 0 then tr2.transportnumber  else tr1.transportnumber
            end transportnumber, --2
            case
              when ea.operationid in (1,70,22) then ea.actdate
              when ea.operationid = 17 then ea.regdate
              when ea.operationid = 5
                and eaiv.weight > 0 and eaiv.operationid <> 64 then ea.actdate
              when ea.operationid = 5
                and eaiv.weight < 0 and eaiv.operationid <> 64 then eathird.actdate
               when ea.operationid = 5
                and eaiv.weight > 0 and easec.operationid = 17 then easec.regdate
              else  easec.actdate
            end actdate,
           eaiv.operationid,
           case
             when (eaiv.operationid = 1)  then invoices.invoicenumber
             when (eaiv.operationid = 17)  then easec.actnumber||'\переоф.'
             when (eaiv.operationid = 5 and eaiv.weight < 0 )
             then eathird.actnumber||'\'|| operation.operationname
             when (eaiv.operationid = 5 and (eaiv.parentitemid = :texportactitemid)
               and (eaiv.weight > 0 or (eaiv.weight is null)))
               then  ea.actnumber||'(сд.)'
             when (eaiv.operationid = 5) and (eaiv.exportactitemid = :texportactitemid)
               and (eaiv.weight > 0 or (eaiv.weight is null))
             then  easec.actnumber
             when (eaiv.operationid = 64) then ea.actnumber||'\'||operation.operationname
             when (eaiv.operationid = 22) then 'Отходы 3 кат.'
             else operation.operationname
           end as invoicenumber,
           case
             when (eaiv.operationid = 1) then tr1.invoicenet
           end as invoicenet,
           case
             when (eaiv.operationid = 1) then it1.net
             when (eaiv.operationid = 17 and ea.ownerid <> :aownerid)
                  or (eaiv.weight < 0 and eaiv.operationid = 5 and eaiv.exportactitemid = :texportactitemid)
                  or (eaiv.weight < 0 and eaiv.operationid = 5 and eaiv.parentitemid = :texportactitemid
                     and it1.operationid in (22,70))
                  or (eaiv.weight > 0 and eaiv.operationid = 5 and eaiv.parentitemid = :texportactitemid)
                  or (eaiv.operationid = 64 and eaiv.exportactitemid = :texportactitemid)
              then -eaiv.weight
             else eaiv.weight
           end as net,
           case
             when eaiv.operationid in (1,17) then it1.invoiceid
             when eaiv.operationid = 5 and eaiv.weight < 0 then it3.invoiceid
             when eaiv.operationid in (22,70,64) then it2.invoiceid
             when eaiv.operationid = 5 and
               (eaiv.weight > 0 or (eaiv.weight is null)) then it2.invoiceid
           end,
           case
             when eaiv.operationid = 1  then 1
             when eaiv.operationid = 22  then 3
             when eaiv.operationid = 70  then 5
             when eaiv.operationid = 65  then 9
             when eaiv.operationid = 64  then 9
             when eaiv.operationid = 5  then
             case
               when it1.operationid = 1 then 7  else 8
             end
             when eaiv.operationid = 17  then
               case
                 when eaivsec.operationid = 17 then 6
                 when eaivsec.operationid = 1 then 2
               end
           end pid,
           case
             when (eaiv.operationid = 1) then it1.net - tr1.invoicenet
           end as deficit,
           ea.ownerid,
           it1.regdate as regdate
         from exportactitems eaiv
           left join exportacts ea  on (eaiv.exportactid =ea.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 it it1 on (eaiv.inventoryid = it1.inventoryid)
           left join it it2 on (eaivsec.inventoryid = it2.inventoryid)
           left join it it3 on (eaivthird.inventoryid = it3.inventoryid)
           left join ittransport tr1 on (eaiv.inventoryid = tr1.inventoryid)
           left join ittransport tr2 on (eaivsec.inventoryid = tr2.inventoryid)
           left join invoices on (it1.invoiceid = invoices.invoiceid)
           left join operation on (it1.operationid = operation.operationid)
        where
          (eaiv.exportactitemid = :texportactitemid
           or (eaiv.parentitemid = :texportactitemid ))
           and (it1.state >= 0 or (it1.state = -10 and it1.operationid in (22,70)))
           and (eaiv.operationid in (1,17,22,64,65,70) or (eaiv.operationid = 5 and ea.state = 1))
           and not(:aexportactid is null)
        order by
          9,2,3,8,7
        INTO :exportactid,
             :transportnumber,
             :actdate,
             :operationid,
             :invoicenumber,
             :invoicenet,
             :net,
             :invoiceid,
             :pid,
             :deficit,
             :ownerid,
             :regdate
      DO    
      BEGIN
        SUSPEND;
      end
    END
 --// 5
    FOR
      select
         ea.exportactid,  --1
         case --??
           when eaiv.weight > 0 and tr1.transporttypeid in (0,1) then 'авто'
           when eaiv.weight < 0 and tr2.transporttypeid in (0,1) then 'авто'
           when eaiv.weight > 0 and tr1.transporttypeid = 2 then 'ж\д'
           when eaiv.weight < 0 and tr2.transporttypeid = 2 then 'ж\д'
          end transportnumber, --2
            case
              when eaiv.weight < 0 and eaiv.operationid <> 64 then eathird.actdate
              when eaiv.weight < 0 and eaiv.operationid = 64 then easec.actdate
              when eaiv.weight > 0 and easec.operationid = 17 then easec.regdate
              else  easec.actdate
            end actdate,
           ea.operationid,
           case
             when (eaiv.operationid = 5 and eaiv.weight < 0 )
             then eathird.actnumber
             else easec.actnumber
           end as invoicenumber,
           sum (case when eaiv.weight > 0 then eaiv.weight end) as invoicenet,
           sum (case when eaiv.weight < 0
                  then -eaiv.weight else eaiv.weight
                end) as net,
           case
             when (eaiv.operationid = 5 and eaiv.weight < 0  and eaiv.operationid <> 64)
             then eathird.exportactid
             else  easec.exportactid
           end   as invoiceid,
           cast(null as integer),
           cast(null as integer) as deficit,
           ea.ownerid,
           cast(null as date) 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 exportactitems_view eaivthird on (eaivsec.parentitemid = eaivthird.exportactitemid)
           left join exportacts eathird on (eaivthird.exportactid = eathird.exportactid)
           left join it it1 on (eaiv.inventoryid = it1.inventoryid)
           left join ittransport tr1 on (eaiv.inventoryid = tr1.inventoryid)
           left join ittransport tr2 on ((case when eaivsec.operationid = 5 then eaivthird.inventoryid else
           eaivsec.inventoryid end) = tr2.inventoryid)
        where
           ea.exportactid = :aexportactid
           and ea.operationid = 5
           and eaiv.operationid <> -64
        group by
         ea.exportactid,  --1
         transportnumber, --2
         actdate,
           ea.operationid,
         invoicenumber,
         invoiceid,
           ea.ownerid
        INTO :exportactid,
             :transportnumber,
             :actdate,
             :operationid,
             :invoicenumber,
             :invoicenet,
             :net,
             :invoiceid,
             :pid,
             :deficit,
             :ownerid,
             :regdate
      DO    
      BEGIN
        SUSPEND;
      end
  end
  else
  begin
  if (not :ainventoryid is null) then
  begin
--/--/ 22, 64, 70
  FOR
    select
       cast(null as integer),
      tr2.transportnumber,
      ea.actdate,
      eaiv.operationid,
      case
        when eaiv.operationid = 64
        then easec.actnumber ||' ('||ea.actnumber||')'
        else ea.actnumber
      end,
      cast(null as integer) as invoicenet,
      -eaiv.weight as net,
      it2.invoiceid,
      case
        when eaiv.operationid = 22  then 1
        when eaiv.operationid = 70  then 2
        when eaiv.operationid = 64  then 3
        when eaiv.operationid = 65  then 3
        when eaiv.operationid = 5  then 5
        when eaiv.operationid = 17  then 4
     end pid,
     cast(null as integer) as deficit,
     ea.ownerid,
     it1.regdate as regdate
    from exportactitems_view eaiv
      left join exportacts ea on (eaiv.exportactid = ea.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 it it2 on (eaivsec.inventoryid = it2.inventoryid)
      left join ittransport tr2 on (eaivsec.inventoryid = tr2.inventoryid)
    where
       (eaiv.inventoryid = :ainventoryid)
      and (it1.state >= 0  or (it1.state = -10))
      and eaiv.operationid in (22,64,70,17,65)
    order by
      8,9,3
    INTO :exportactid,
         :transportnumber,
         :actdate,
         :operationid,
         :invoicenumber,
         :invoicenet,
         :net,
         :invoiceid,
         :pid,
         :deficit,
         :ownerid,
         :regdate
  DO
  BEGIN
    SUSPEND;
  END
  End  End
--//- 17
FOR
  select
    ea.exportactid as exportactid,
    case
      when eai.operationid=17 and tr1.transporttypeid  in (0,1) then 'авто'
      when eai.operationid=17 and tr1.transporttypeid  in (2) then 'ж\д'
      when eai.operationid<>17 and tr2.transporttypeid  in (0,1) then 'авто'
      when eai.operationid<>17 and tr2.transporttypeid  in (2) then 'ж\д'
    end transportnumber,
    case
      when :aownerid = ea.ownerid then ea.actdate
      else easec.actdate
    end actdate,
    case
      when :aownerid = ea.ownerid then ea.exportactid
      else easec.exportactid
    end invoiceid,
    ea.operationid,
    case
      when :aownerid = ea.ownerid then ea.actnumber
      else easec.actnumber
    end actnumber,
    sum(case
      when :aownerid = ea.ownerid then eai.weight
      when :aownerid = -1 then eai.weight
      else -eai.weight
    end),
    cast(null as integer) as pid,
    ea.ownerid,
    case
      when :aownerid = ea.ownerid then ea.regdate
      else easec.regdate
    end regdate
  from exportacts ea
   left join exportactitems eai on (ea.exportactid = eai.exportactid)
   left join exportactitems eaisec on (eai.parentitemid = eaisec.exportactitemid)
   left join exportacts easec on (eaisec.exportactid = easec.exportactid)
   left join ittransport  tr1 on (eai.inventoryid = tr1.inventoryid)
   left join ittransport tr2 on (eaisec.inventoryid = tr2.inventoryid)
  where ea.operationid = 17
    and ea.orderitemid = :aorderitemid
    and (eai.operationid = 17 or (:aownerid = ea.ownerid))
  group by
    ea.exportactid,
    transportnumber,
    actdate,
    invoiceid,
    ea.operationid,
    actnumber,
    ea.ownerid,
    regdate
    INTO
         :exportactid,
         :transportnumber, --4
         :actdate,
         :invoiceid, --2
         :operationid,
         :invoicenumber,  --1
         :net,
         :pid,       --3
         :ownerid,
         :regdate
    DO
    FOR
      select
        case
          when :ownerid <> :aownerid and :aownerid <> -1
          then sum(coalesce(forwarder_weightitem.transportweight,0)) - coalesce(:net,0)
        end,
        case
          when :ownerid <> :aownerid and :aownerid <> -1
          then sum(forwarder_weightitem.transportweight)
        end,
       case
         when :aownerid = -1 then -1
         else :ownerid
       end
     from exportactitems eai17
       left join forwarder_weightitem (null, null,eai17.parentitemid,:iscurrent,:apartydate ) on (0=0)
      where (eai17.exportactid = :exportactid)
    INTO :invoicenet,
         :deficit,
         :ownerid
     DO
      BEGIN
       SUSPEND;
      END--*/
END