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

ACC_FORMRN3_DETAILS

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

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

Описание

Бухгалтерия - форма РН3 (расчетная часть)

Определение

CREATE PROCEDURE ACC_FORMRN3_DETAILS(
    TODATE DATE,
    CROPID INTEGER,
    CLASSID INTEGER,
    SORTID INTEGER,
    ST INTEGER,
    HARVESTYEAR_1 INTEGER,
    HARVESTYEAR_2 INTEGER,
    HARVESTYEAR_3 INTEGER,
    HARVESTYEAR_4 INTEGER,
    PARTYID SMALLINT,
    PARTYDATE DATE,
    FORMTYPE INTEGER)
RETURNS (
    REM_H1 DOUBLE PRECISION,
    REM_H2 DOUBLE PRECISION,
    REM_H3 INTEGER,
    REM_H4 INTEGER,
    REM DOUBLE PRECISION)
AS
declare variable outcome double precision;
declare variable optype smallint;
declare variable income double precision;
declare variable temp double precision;
declare variable curyear integer;
declare variable income_h1 double precision;
declare variable income_h2 double precision;
declare variable income_h3 double precision;
declare variable income_h4 double precision;
declare variable outcome_h1 double precision;
declare variable outcome_h2 double precision;
declare variable outcome_h3 double precision;
declare variable outcome_h4 double precision;
BEGIN
  income_h1 = 0;
  outcome_h1 = 0;
  income_h2 = 0;
  outcome_h2 = 0;
  income_h3 = 0;
  outcome_h3 = 0;
  income_h4 = 0;
  outcome_h4 = 0;
  income = 0;
  outcome = 0;
  rem = 0;
  for
    select
      coalesce(it.net, 0) ,
      operation.operationsubtype,
      harvestyear.qualityvalue
    from it
      join getworkparties(:partyid,:partydate,null,null,null)
        on (it.partyid = getworkparties.partyid)
      left join quality on (it.samplerqualityid = quality.qualityid)
      left join qualitydata harvestyear on (it.BuhQualityid = harvestyear.qualityid and harvestyear.qualitytypeid = 6)
      left join operation on (it.operationid = operation.operationid)
      left join clients on (it.ownerid = clients.clientid)
      left join invoices on (it.invoiceid = invoices.invoiceid)
      left join clients cs on (invoices.senderid = cs.clientid)
    where it.regdate <= :todate
      and it.parentid >= 0
      and it.state >= 0
      and  ((quality.cropid = :cropid) or ((:cropid = -1) and (quality.cropid in (1,3,50,73,4,2,51,48,74,49,71))))
      and ((quality.classid = :classid) or (:classid < 0))
      and ((((quality.sortid = :sortid) and (:sortid > 0))
          or   (((quality.sortid <> :sortid) or (quality.sortid is null)) and (:sortid < 0))) or (:sortid = 0))
      and ((:formtype = 1) or (it.operationid not in (18,19,45,46)))
      and it.operationid <> 17
      and (operationsubtype = 0 or (operationsubtype = 1 and :formtype = 1))
      and it.net is not null
      and it.net <> 0
      and ((((it.storingid not in (2,7)) or (it.storingid is null)) and (:st in (1,3)))
           or ((it.storingid = 7) and (:st= 2)))
      and ((clients.clienttypeid = 5) or (:st <> 3))
      and cs.storageid is null
    INTO
         :temp,
         :optype,
         :curyear
  do
  begin
    if (:optype = 0) then
    begin
      income = :income + :temp;
      if (:curyear = :harvestyear_1) then income_h1 = :income_h1 + :temp;
      if (:curyear = :harvestyear_2) then income_h2 = :income_h2 + :temp;
      if (:curyear = :harvestyear_3) then income_h3 = :income_h3 + :temp;
      if (:curyear = :harvestyear_4) then income_h4 = :income_h4 + :temp;
    end
    else if (:optype = 1) then
    begin
      outcome = :outcome + :temp;
      if (:curyear = :harvestyear_1) then outcome_h1 = :outcome_h1 + :temp;
      if (:curyear = :harvestyear_2) then outcome_h2 = :outcome_h2 + :temp;
      if (:curyear = :harvestyear_3) then outcome_h3 = :outcome_h3 + :temp;
      if (:curyear = :harvestyear_4) then outcome_h4 = :outcome_h4 + :temp;
    end
  end

    if (:formtype = 1) then
    begin
      rem = cast((:income - :outcome) / 1000 as double precision);
      rem_h1 = cast((:income_h1 - :outcome_h1) / 1000 as double precision);
      rem_h2 = cast((:income_h2 - :outcome_h2) / 1000 as double precision);
      rem_h3 = cast((:income_h3 - :outcome_h3) / 1000 as double precision);
      rem_h4 = cast((:income_h4 - :outcome_h4) / 1000 as double precision);
    end
    else
    begin
      rem = cast(:income / 1000 as double precision);
      rem_h1 = cast(:income_h1 / 1000 as double precision);
      rem_h2 = cast(:income_h2 / 1000 as double precision);
      rem_h3 = cast(:income_h3 / 1000 as double precision);
      rem_h4 = cast(:income_h4 / 1000 as double precision);
    end
  SUSPEND;
END