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

CONTRACT_TRACE

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

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

Описание

Отслеживание выполнения договоров

Определение

CREATE PROCEDURE CONTRACT_TRACE(
    CONTRACTID INTEGER)
RETURNS (
    CROPCLASS VARCHAR(100),
    DECLARED INTEGER,
    INCOME INTEGER,
    OUTCOME INTEGER,
    PERCENT NUMERIC(5,2))
AS
declare variable classid integer;
begin
 for
 SELECT coalesce(sum(CONTRACTCOSTS.weight),0) weight,
 qn.cropfullname, quality.classid
 FROM CONTRACTCOSTS
   LEFT JOIN QUALITY ON (CONTRACTCOSTS.QUALITYID = QUALITY.QUALITYID)
   left join quality_cropfullname(quality.cropid, quality.classid, null, 1) qn on (0=0)
--   left join crop on (quality.cropid = crop.cropid)
--   left join class on (quality.classid = class.classid)
  where contractcosts.serviceid = 1 and contractcosts.contractid = :contractid
  group by
   qn.cropfullname, quality.classid
 into
 :DECLARED,
 :CROPCLASS,
 :CLASSID

 do
  begin
  select coalesce(sum(it.net),0) from it
  left join operation on (it.operationid = operation.operationid)
  LEFT JOIN QUALITY ON (it.buhqualityid = QUALITY.QUALITYID)
  where it.contractid = :contractid
  and it.net is not null
  and operation.operationsubtype = 0
  and it.storingid = 1
  and quality.classid = :classid
  into
  :INCOME;

  select coalesce(sum(it.net),0) from it
  left join operation on (it.operationid = operation.operationid)
  LEFT JOIN QUALITY ON (it.buhqualityid = QUALITY.QUALITYID)
  where it.contractid = :contractid
   and it.net is not null
   and operation.operationsubtype = 1
   and quality.classid = :classid
  into
  :OUTCOME;
  if (:declared = 0) then
    percent = 0;
  else
   PERCENT = cast(:INCOME as numeric(13,2))*100/cast(:DECLARED as numeric(13,2));
   suspend;
 end
end