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

ACN_305

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

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

Описание

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

Определение

CREATE PROCEDURE ACN_305(
    CLASSID INTEGER,
    CROPID INTEGER,
    DOCROP INTEGER,
    SORTID INTEGER,
    PARTYID INTEGER,
    PARTYDATE DATE,
    STORAGEID INTEGER,
    DATE1 DATE,
    DATE2 DATE,
    AOWNERID INTEGER,
    CONTRACTID INTEGER)
RETURNS (
    NET BIGINT,
    CDIRT BIGINT,
    CHUM BIGINT,
    DIRT NUMERIC(9,2),
    HUM NUMERIC(9,2),
    REGDATE DATE,
    ANALIS_ID INTEGER,
    NUMDOC VARCHAR(31),
    OWNERID INTEGER)
AS
BEGIN
  FOR
    select
      it.regdate,
      case when qd.qualitynumber is null
        then it.inventoryid
        else qd.qualitynumber
      end as analis_id,
      it.ownerid,
      case
        when not(it.operationid in (45,46)) then  case
          when (ittransport.transporttypeid = 2) then ittransport.transportnumber
          when (ittransport.transporttypeid in (0,1) and it.operationid = 5 and it.orderloaditemid is not null)
          then it.orderloaditemid
          when ((operation.operationtype in (1,3,4,5) or (operation.operationsubtype = 1)))
          then invoices.invoicenumber
         else it.registerid
        end
      end as numdoc,
     coalesce(Sum(form36aexpense.outnet),0) net,

    cast(sum( form36aexpense.outnet *
     cast(form36aexpense.outdirt as numeric (3,1)))/100 as integer) as cdirt,

    cast(sum( form36aexpense.outnet *
     cast(form36aexpense.outhumidity as numeric (3,1)))/100 as integer) as chum,
    
     case coalesce(sum(it.net),0) when 0 then 0 else
      cast(sum( form36aexpense.outnet *
       form36aexpense.outdirt)/sum(form36aexpense.outnet) as numeric(3,1))
    end dirt,

    case coalesce(sum(it.net),0) when 0 then 0 else
      cast(sum( form36aexpense.outnet *
       form36aexpense.outhumidity)/sum(form36aexpense.outnet) as numeric(3,1))
    end hum
    
    from it
      join getworkparties(:partyid,:partydate, :cropid, :classid, :storageid)
      on (it.partyid = getworkparties.partyid)
      left join quality q on (it.BuhQualityid = q.qualityid)
      left join qualitydata_select_min(it.BuhQualityid) qd on (0 = 0)
      left join ittransport on it.inventoryid = ittransport.inventoryid
      left join operation on it.operationid = operation.operationid
      left join invoices on it.invoiceid = invoices.invoiceid
      left join form36aexpense(it.operationid, dirt, humidity,
         it.net) on (0=0)
     where
      ((q.classid = :classid and :docrop = 0) or (q.cropid = :cropid and :docrop = 1))
      and it.regdate between :date1 and :date2
      and it.storageid = :storageid
      and operation.operationsubtype = 1
      and operation.operationtype not in (2,1,4,5)
      and it.state >= 0
      and it.parentid >= 0
      and ((q.sortid = :sortid) or (:sortid = 0))
      and ((it.ownerid = :aownerid) or (coalesce(:aownerid,0) = 0))
      and ((it.contractid = :contractid) or (coalesce(:contractid,0) = 0))
    group by
      it.regdate,
      analis_id,
      it.ownerid,
      numdoc , q.classid
    INTO :regdate,
         :analis_id,
         :ownerid,
         :numdoc,
         :net,
         :cdirt,
         :chum,
         :dirt,
         :hum
  DO
  BEGIN
    SUSPEND;
  END
END