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

FORWARDER_ACTIMTEMS

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

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

Описание

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

Определение

CREATE PROCEDURE FORWARDER_ACTIMTEMS(
    AREGDATE DATE,
    AEXPORTACTID INTEGER,
    AOWNERID INTEGER,
    APROVIDERID INTEGER,
    ASTATIONID INTEGER,
    ACROPID INTEGER,
    ATRANSPORTSUBTYPE INTEGER,
    ACLASSID INTEGER,
    ASTORAGEID INTEGER)
RETURNS (
    EXPORTACTID INTEGER,
    OPERATIONID INTEGER,
    REGDATE DATE,
    OWNERID INTEGER,
    CROPID INTEGER,
    PROVIDERID INTEGER,
    INVOICEID INTEGER,
    INVENTORYID INTEGER,
    STATIONID INTEGER,
    OWNERSFULLNAME VARCHAR(100),
    PROVIDERSFULLNAME VARCHAR(100),
    STATIONFULLNAME VARCHAR(128),
    INVOICENUMBER VARCHAR(31),
    CROPFULLNAME VARCHAR(128),
    TRANSPORTNUMBER VARCHAR(25),
    WEIGHT INTEGER,
    NET INTEGER,
    RESTNET INTEGER,
    ISCHECKED INTEGER,
    FLAG INTEGER,
    EXPORTACTPARENTID INTEGER,
    PARENTITEMID INTEGER,
    EXPORTACTITEMID INTEGER,
    PIDP INTEGER)
AS
declare variable parinv integer;
declare variable pid integer;
declare variable fl_tmp integer; /* предыдущ. флаг */
declare variable invoice_tmp integer; /* пред. накладная */
declare variable eaid_tmp integer;
BEGIN
  eaid_tmp = aexportactid;
  FOR
    select
      eai.operationid,
      ea.regdate,
      ea.ownerid,
      ea.providerid,
      quality.cropid,
      itm.invoiceid,
      itm.inventoryid,
      null,
      owners.clientfullname as qwnersfullname,
      providers.clientfullname as providersfullname,
      null,
      case
        when eai.operationid = 1  then 1
        when eai.operationid = 22  then 3
        when eai.operationid = 70  then 5
        when eai.operationid = 64  then 8
        when eai.operationid = 13  then 8
        when eai.operationid = 65  then 8
        when eai.operationid = 5  then 7
        when eai.operationid = 17  then
        case
          when eaisec.operationid = 17 then 6
          when eaisec.operationid = 1 then 2
        end
     end pid,
     case
        when eai.operationid = 1 and not(eai.weight is null) then eai.inventoryid
        when eai.operationid = 1 and (eai.weight is null) then
          (select eaip.inventoryid from exportactitems eaip left join it on (eaip.inventoryid = it.inventoryid)
           where eaip.exportactid = eai.exportactid  and it.invoiceid = itm.invoiceid
           and not(eaip.weight is null) and eaip.operationid = 1)
        when eai.operationid <> 1 then eaisec.inventoryid
     end parinv,
      case
        when eai.operationid = 1
        then invoices.invoicenumber
        when eai.operationid in (17,5)
        then operation.operationname
      end,
      case
        when eai.operationid in (1,17)
             and eai.exportactid = :aexportactid
             and ((eai.weight > 0) or (eai.weight is null))
          then quality_cropfullname.cropfullname
        when eai.operationid in (22,70,64,13,65) then operation.operationname
        when eai.operationid in (5,17)
         then '*' || (select distinct
                      case when eait.operationid = 1
                        then quality_cropfullname.cropfullname
                        else operation.operationname
                      end
             from exportactitems eait
               left join operation on (eait.operationid = operation.operationid)
             where eait.inventoryid = eai.inventoryid and eait.operationid in (1,22,70,13,65))
      end cropfullname,
      case
        when eai.operationid = 1
        then t1.transportnumber else t2.transportnumber
      end transportnumber,
      case
        when eai.operationid in (5,17) and eai.exportactid <> :aexportactid
        then -eai.weight else eai.weight
      end weight,
      eai.exportactitemid,
      ea.exportactparentid,
      eai.parentitemid,
      case
        when eai.operationid = 1
         then itm.net
      end net,
      case
        when eai.operationid = 1
        then  (select sum (case when eai1.operationid in (5,17)
                                    and (eai1.parentitemid = eai.exportactitemid)
                             then -eai1.weight
                             when eai1.operationid in (1,22,70,13,65)
                             then eai1.weight
                           end)
              from exportactitems_view eai1
              where eai1.state <> 10
                and ((eai1.exportactitemid = eai.exportactitemid) or (eai1.parentitemid = eai.exportactitemid)))
        when eai.operationid  = 17 and eai.exportactid = :aexportactid
        then (select sum (weight)
              from exportactitems_view eai17
              where eai17.exportactitemid = eai.exportactitemid and eai17.state <> 10)
        when eai.operationid in (22,70,13)
        then (select sum (case when eai22.operationid = 5 then -weight  else weight
                          end)
              from exportactitems_view eai22
                left join exportacts on (eai22.exportactid = exportacts.exportactid)
              where eai22.inventoryid = eai.inventoryid and eai22.state <> 10
                and ((eai22.exportactitemid = eai.exportactitemid)
                      or (exportacts.exportactparentid = eai.exportactid)))
      end restnet,
      eai.exportactid,
      1 as checked,
      t1.transporttypeid as pidp
    from exportacts  ea
      left join exportactitems_view eai on (ea.exportactid = eai.exportactid)
      left join exportactitems_view eaisec on (eai.parentitemid = eaisec.exportactitemid)
      left join exportacts easec on (eaisec.exportactid = easec.exportactid)
      left join it itm on (eai.inventoryid = itm.inventoryid)
      left join it itsec on (eaisec.inventoryid = itsec.inventoryid)
      left join ittransport t1 on (eai.inventoryid = t1.inventoryid)
      left join ittransport t2 on (eaisec.inventoryid = t2.inventoryid)
      left join clients owners on (ea.ownerid = owners.clientid)
      left join clients providers on (ea.providerid = providers.clientid)
      left join quality on (ea.qualityid = quality.qualityid)
      left join quality_cropfullname (quality.cropid, quality.classid, null, 1) on (0=0)
      left join invoices  on (itm.invoiceid = invoices.invoiceid)
      left join operation on (eai.operationid = operation.operationid)
    where (eai.state <> 10)
      and ((ea.exportactid = :aexportactid)
           or (ea.exportactparentid = :aexportactid))
            and ((ea.exportactid = :aexportactid
            and itm.operationid in (1,17,22,64,70,45,13,65)
            and itm.state >= 0)
           or ((ea.exportactparentid = :aexportactid) and (eai.operationid = 17))
          or ((eai.operationid = 5)  and (eaisec.exportactid = :aexportactid) ))
    order by
     13, 12
    INTO
      :operationid,
      :regdate,
      :ownerid,
      :providerid,
      :cropid,
      :invoiceid,
      :inventoryid,
      :stationid,
      :ownersfullname,
      :providersfullname,
      :stationfullname,
      :pid,
      :parinv,
      :invoicenumber,
      :cropfullname,
      :transportnumber,
      :weight,
      :exportactitemid,
      :exportactparentid,
      :parentitemid,
      :net,
      :restnet,
      :exportactid,
      :ischecked,
      :pidp
  DO
  BEGIN
    fl_tmp = 1;
    invoice_tmp = invoiceid;
    flag = 1;
    SUSPEND;
  END

---//---------------
  FOR
    select
      it.operationid, --TMP
      it.regdate,
      it.ownerid,
      it.providerid,
      q1.cropid,
      it.invoiceid,
      it.inventoryid,
      invoices.stationid,
      owners.clientfullname as qwnersfullname,
      providers.clientfullname as providersfullname,
      stations.stationfullname,
      invoices.invoicenumber,
      quality_cropfullname.cropfullname,
      ittransport.transportnumber,
      eai.weight,
      eai.exportactitemid,
      ea.exportactparentid,
      eai.parentitemid,
      it.net,
      it.net - (select coalesce(sum(weight), 0) from exportactitems_view
                where inventoryid = it.inventoryid and state <> 10) restnet,
      0,
      case
        when :aexportactid is null
        then 0
        else 1
      end ischecked,
      transporttype.transporttypeid as pidp
    from it
      left join clients owners on (it.ownerid = owners.clientid)
      left join clients providers on (it.providerid = providers.clientid)
      left join quality q1 on (it.samplerqualityid = q1.qualityid)
      left join quality_cropfullname (q1.cropid, q1.classid, null, 1) on (0=0)
      left join invoices  on (it.invoiceid = invoices.invoiceid)
      left join stations on (invoices.stationid = stations.stationid)
      left join ittransport on (it.inventoryid = ittransport.inventoryid)
      left join transporttype on (ittransport.transporttypeid = transporttype.transporttypeid) --
      left join exportactitems_view eai on (it.inventoryid = eai.inventoryid and
                ((:aexportactid is not null) and
                (eai.exportactid = :aexportactid) or
                ((:aexportactid is null) and (it.net is null))))
      left join exportacts ea on (eai.exportactid = ea.exportactid)
    where  it.operationid = 1
      and ((ea.exportactid = :aexportactid)
             or ( (:aexportactid is null) and
                 (select coalesce(sum(weight),-1)
                     from exportactitems_view where inventoryid = it.inventoryid
                     and state <> 10) <> it.net)--)
             or ( (it.net is null) and (ea.exportactid is null) and (:aexportactid is null)))
      and it.state >= 0
      and it.regdate = :aregdate
      and ((it.ownerid = :aownerid) or (:aownerid is null and it.ownerid is null))
      and ((it.providerid = :aproviderid) or ((:aproviderid is null) and (it.providerid is null)))
      and ((invoices.stationid = :astationid) or (:astationid is null and invoices.stationid is null))
      and ((q1.cropid = :acropid) or (:acropid is null and q1.cropid is null))
      and ((q1.classid = :aclassid) or (:aclassid is null and q1.classid is null))
      and ((it.storageid = :astorageid) or (:astorageid is null and it.storageid is null))
      and ((transporttype.transportsubtype = :atransportsubtype) or (:atransportsubtype is null and transporttype.transportsubtype is null))
      and ea.exportactid is null   --
    order by
      it.regdate,
      invoices.invoiceid,
      providers.clientfullname
    INTO
      :operationid,
      :regdate,
      :ownerid,
      :providerid,
      :cropid,
      :invoiceid,
      :inventoryid,
      :stationid,
      :ownersfullname,
      :providersfullname,
      :stationfullname,
      :invoicenumber,
      :cropfullname,
      :transportnumber,
      :weight,
      :exportactitemid,
      :exportactparentid,
      :parentitemid,
      :net,
      :restnet,
      :exportactid,
      :ischecked,
      :pidp
  DO
  BEGIN
    if ((net is not null) or ((coalesce(invoice_tmp,0) = invoiceid)
          and (coalesce(fl_tmp,0) =1))) then
      flag = 1;
    else
      flag = 0;
    fl_tmp = flag;
    invoice_tmp = invoiceid;
    SUSPEND;
  END
END