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

AC_CONTRACT_GETLOSS

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

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

Описание

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

Определение

CREATE PROCEDURE AC_CONTRACT_GETLOSS(
    CONTRACTID INTEGER,
    STORAGEID INTEGER,
    STOREDAYS INTEGER,
    CROPID INTEGER,
    CLASSID INTEGER)
RETURNS (
    DAYS INTEGER,
    LOSSESRATE NUMERIC(9,2),
    COST NUMERIC(18,2))
AS
BEGIN
  FOR
    select first 1
      Clmain.days,
      Clmain.lossesrate,
      Cmain.cost
    from contractcosts  Cmain
     left join contractlosses Clmain on (Cmain.contractcostid = Clmain.contractcostid)
     left join quality Qmain on (Cmain.qualityid = Qmain.qualityid)
    where Cmain.contractid = :contractid
     and Clmain.storageid = :storageid
     and Clmain.days <= :storedays
     and Qmain.cropid = :cropid
     and (Qmain.classid = :classid
       or (coalesce(:classid,-1) = -1 and Qmain.classid is null)
       or (Qmain.classid is null
           and not exists (select first 1 CLsec.contractloseid
               from contractcosts Csec
                 left join contractlosses CLsec on (Csec.contractcostid = CLsec.contractcostid)
                 left join quality Qsec on (Csec.qualityid = Qsec.qualityid)
               where Csec.contractid = :contractid
             and Qsec.cropid = :cropid
             and CLsec.storageid = :storageid
             and (Qsec.classid = :classid ))))
    order by days desc
    INTO :DAYS,
         :LOSSESRATE,
         :COST
  DO
  BEGIN
    SUSPEND;
  END
END