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

GET_WORKPARTIES

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

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

Описание

Возвращает список робочих партий активных на данный момент или на указанную дату

Определение

CREATE PROCEDURE GET_WORKPARTIES(
    ISCURRENT INTEGER,
    PARTYDATE DATE,
    CROPID INTEGER,
    CLASSID INTEGER,
    STORAGEID INTEGER)
RETURNS (
    PARTYID INTEGER,
    BEGINDATE DATE)
AS
begin
  if (:ISCURRENT = 1) then
    for
      select PARTIES.PARTYID, PARTIES.BEGINDATE
        from PARTIES
        where (((PARTIES.STATE = 1)
                and ((:CROPID is null)
                     or ((PARTIES.CROPID = :CROPID)
                         and ((PARTIES.CLASSID = :CLASSID) or (:CLASSID is null) or (PARTIES.CLASSID is null)))))
               and ((PARTIES.STORAGEID = :STORAGEID) or (:STORAGEID is null)))
              or (PARTIES.PARTYID = -1)
      into :PARTYID, :BEGINDATE
    do suspend;
  else
    for
      select PARTIES.PARTYID, PARTIES.BEGINDATE
        from PARTIES
        where ((:PARTYDATE between PARTIES.BEGINDATE
                             and coalesce(PARTIES.ENDDATE,
                                          case
                                            when (:PARTYDATE > current_date) then
                                              :PARTYDATE
                                            else current_date
                                          end))
               and ((:CROPID is null) or (PARTIES.CROPID = :CROPID))
               and ((PARTIES.CLASSID = :CLASSID) or (:CLASSID is null) or (PARTIES.CLASSID is null))
               and ((PARTIES.STORAGEID = :STORAGEID) or (:STORAGEID is null)))
            or (PARTIES.PARTYID = -1)
      into :PARTYID, :BEGINDATE
    do suspend;
end