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

DECLARATIONSTORING

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

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

Описание

Бухгалтерия - Декларация (расчетная часть)

Определение

CREATE PROCEDURE DECLARATIONSTORING(
    STORINGID INTEGER,
    DATE1 DATE,
    DATE2 DATE,
    PARTYID INTEGER,
    CROPID INTEGER,
    CLASSID INTEGER,
    SORTID INTEGER,
    PARTYDATE DATE)
RETURNS (
    STARTREMINDER NUMERIC(15,3),
    ENDREMINDER NUMERIC(15,3),
    INCOME NUMERIC(15,3),
    OUTCOME NUMERIC(15,3))
AS
declare variable REGDATE date;
declare variable OPERATIONSUBTYPE smallint;
declare variable NET numeric(15,3);
declare variable INCOMEBEFORE numeric(15,3);
declare variable OUTCOMEBEFORE numeric(15,3);
declare variable INVENTNET numeric(15,3);
declare variable INVENTNETPER numeric(15,3);
declare variable OPERATIONID integer;
begin
  STARTREMINDER = 0;
  ENDREMINDER = 0;
  INCOMEBEFORE = 0;
  OUTCOMEBEFORE = 0;
  INCOME = 0;
  OUTCOME = 0;
  NET = 0;
  INVENTNET = 0;
  INVENTNETPER = 0;
  if (coalesce(:CLASSID, -1) = -1) then
    CLASSID = null;
  for
    select
        IT.NET, OPERATION.OPERATIONSUBTYPE, IT.REGDATE, IT.OPERATIONID
      from IT
        join GETWORKPARTIES(:PARTYID, :PARTYDATE, :CROPID, :CLASSID, null) on (IT.PARTYID = GETWORKPARTIES.PARTYID)
        left join QUALITY on (QUALITY.QUALITYID = IT.SAMPLERQUALITYID)
        left join CROP on (CROP.CROPID = QUALITY.CROPID)
        left join STORAGE on (STORAGE.STORAGEID = IT.STORAGEID)
        left join OPERATION on (OPERATION.OPERATIONID = IT.OPERATIONID)
        left join INVOICES on (INVOICES.INVOICEID = IT.INVOICEID)
        left join CLIENTS on (CLIENTS.CLIENTID = INVOICES.SENDERID)
      where (IT.REGDATE <= :DATE2)
        and (IT.STATE >= 0)
        and (IT.PARENTID >= 0)
        and ((IT.STORINGID = :STORINGID) or (coalesce(:STORINGID, 0) = 0))
        and ((IT.NET is not null) and (IT.NET <> 0))
        and (IT.OPERATIONID <> 17)
        and ((OPERATION.OPERATIONSUBTYPE in (0, 1)) and (IT.OPERATIONID <> 65))
        and (STORAGE.STORAGETYPEID = 1)
        and (QUALITY.CROPID = :CROPID)
        and ((QUALITY.CLASSID = :CLASSID) or (coalesce(:CLASSID, -1) = -1))
        and ((((QUALITY.SORTID = :SORTID) or (coalesce(:SORTID, -1) = -1)) and (:SORTID <> -3))
             or (((QUALITY.SORTID <> 3) or (QUALITY.SORTID is null)) and (:SORTID = -3)))
        and (CLIENTS.STORAGEID is null)
    into :NET, :OPERATIONSUBTYPE, :REGDATE, :OPERATIONID
  do begin
    NET = cast((:NET / 1000) as numeric (15, 3));

    if ((:OPERATIONSUBTYPE = 0) and (:OPERATIONID <> 45)) then begin
      if (:REGDATE < :DATE1) then
        INCOMEBEFORE = :INCOMEBEFORE + :NET;
      if (:REGDATE between :DATE1 and :DATE2) then
        INCOME = :INCOME + :NET;
    end

    if ((:OPERATIONSUBTYPE = 1) and (:OPERATIONID <> 46)) then begin
      if (:NET < 0 ) then
        NET = :NET * -1;
      if (:REGDATE < :DATE1) then
        OUTCOMEBEFORE = :OUTCOMEBEFORE + :NET;
      if (REGDATE between :DATE1 and :DATE2) then
        OUTCOME = :OUTCOME + :NET;
    end

    if (:OPERATIONID in (45, 46)) then begin
      if (:operationid = 45) then begin
        INVENTNET = INVENTNET + :NET;
        if (:REGDATE between :DATE1 and :DATE2) then
          INVENTNETPER = INVENTNETPER + :NET;
      end
      if (:operationid = 46) then begin
        INVENTNET = INVENTNET - :NET;
        if (:REGDATE between :DATE1 and :DATE2) then
          INVENTNETPER = INVENTNETPER - :NET;
      end
    end
  end

  if (INVENTNETPER > 0) then
    INCOME = :INCOME + INVENTNETPER;
  else OUTCOME = :OUTCOME - INVENTNETPER;

  STARTREMINDER = :INCOMEBEFORE - :OUTCOMEBEFORE + :INVENTNET;
  ENDREMINDER = :STARTREMINDER + :INCOME - :OUTCOME;

  STARTREMINDER = cast((:STARTREMINDER) as numeric (12, 1));
  ENDREMINDER = cast((:ENDREMINDER) as numeric (12, 1));
  OUTCOME = cast((:OUTCOME) as numeric (12, 1));
  INCOME = cast((:INCOME) as numeric (12, 1));
  if (:ENDREMINDER - :STARTREMINDER - :INCOME + :OUTCOME <> 0) then
    if (:INCOME <> 0) then
      INCOME = :INCOME + (:ENDREMINDER - :STARTREMINDER - :INCOME + :OUTCOME);
    else OUTCOME = :OUTCOME + (:STARTREMINDER - :ENDREMINDER - :OUTCOME);

  suspend;
END