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

ACN_SVODKA

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

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

Описание

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

Определение

CREATE PROCEDURE ACN_SVODKA(
    ISCURRENT INTEGER,
    APARTYDATE DATE,
    ASTORAGEID INTEGER,
    ADATEBEG DATE,
    ISWEIGHT INTEGER)
RETURNS (
    CROPID INTEGER,
    CROPNAME VARCHAR(20),
    CLIENTNAME VARCHAR(30),
    ADDRESS VARCHAR(100),
    OKPO VARCHAR(10),
    COMNET BIGINT,
    EXPNET BIGINT)
AS
declare variable tpartyid integer;
declare variable mindate date;
declare variable maxdate date;
BEGIN
  for
    select parties.partyid,
      min(it.regdate) mindate,
      max(it.regdate) maxdate
    from parties
      join it on (parties.partyid = it.partyid)
    where
      ((:apartydate between begindate and coalesce(enddate, Current_Date) and :iscurrent <> 1)
      or (:iscurrent = 1 and parties.state = 1))
      and it.regdate is not null
      and it.weight is not null
      and it.state <> -1
      and (parties.storageid = :astorageid or (:astorageid = -1))
    group by parties.partyid
    INTO :tpartyid,
         :mindate,
         :maxdate
    do
    begin
      if (:maxdate > :adatebeg) then maxdate = :adatebeg -1;
      FOR
        select
          crop.cropid,
          crop.cropname,
          --quality_cropfullname.cropfullname,
          owner.clientname,
          owner.address,
          owner.okpo,
          coalesce(sum(form36acoming.outnet),0) as comnet,
          coalesce(sum(form36aexpense.outnet
                       ),0) as expnet
        from it
          left join quality on (it.samplerqualityid = quality.qualityid)
          left join qualitydata vlagn on (it.BuhQualityid = vlagn.qualityid and vlagn.qualitytypeid = 2)
          left join qualitydata sorn on (it.BuhQualityid = sorn.qualityid and sorn.qualitytypeid = 13)
          left join crop on (quality.cropid = crop.cropid)
        --  left join quality_cropfullname(Quality.cropid,Quality.classid,null, 1) on (0=0)
          left join operation on (it.operationid = operation.operationid)
          left join form36aexpense(it.operationid, sorn.qualityvalue, vlagn.qualityvalue,
             case when (it.operationid = 64  or (coalesce(:IsWeight,0) = 1) )
             then it.weight else it.net end) on (0=0)
          left join form36acoming(sorn.qualityvalue, vlagn.qualityvalue,it.operationid,
             it.net, crop.cropid) on (0=0)
         left join clients owner on it.ownerid = owner.clientid
        where it.regdate between :mindate and (:maxdate )
          and   (it.regdate < :adatebeg)
          and it.partyid = :tpartyid
          and operation.operationsubtype in (0,1)
          and (it.state <>-1 and it.state <> -100 and it.state <> -20)
          and it.weight is not null
          and it.parentid <> -1
          and (it.storageid = :astorageid or (coalesce(:astorageid,-1)=-1) )
      --   and Crop.croptypeid in (1,5)
        group by crop.cropid,
          crop.cropname,
     --    quality_cropfullname.cropfullname,
          owner.clientname,
          owner.address,
          owner.okpo
        having (coalesce(sum(form36acoming.outnet),0) -
          coalesce(sum(form36aexpense.outnet),0)) <> 0
        order by 2,1
        INTO :cropid,
             :cropname,
             :clientname,
             :address,
             :okpo,
             :comnet,
             :expnet
      DO
      BEGIN
        SUSPEND;
      END
   END
END