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

UPDATE_HARVESTING_YEAR

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

(Нет входных параметров)

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

Описание

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

Определение

CREATE PROCEDURE UPDATE_HARVESTING_YEAR
RETURNS (
    SAMPLERQUALITYID INTEGER,
    SAM_YEARGR INTEGER,
    LABQUALITYID INTEGER,
    LAB_YEARGR INTEGER,
    BUHQUALITYID INTEGER,
    BUH_YEARGR INTEGER)
AS
begin
  for
    select IT.SAMPLERQUALITYID, SAM_YEARGR.QUALITYVALUE,
        IT.LABQUALITYID, LAB_YEARGR.QUALITYVALUE,
        IT.BUHQUALITYID, BUH_YEARGR.QUALITYVALUE
      from IT
        left join QUALITYDATA SAM_YEARGR on ((SAM_YEARGR.QUALITYID = IT.SAMPLERQUALITYID) and (SAM_YEARGR.QUALITYTYPEID = 6))
        left join QUALITYDATA LAB_YEARGR on ((LAB_YEARGR.QUALITYID = IT.LABQUALITYID) and (LAB_YEARGR.QUALITYTYPEID = 6))
        left join QUALITYDATA BUH_YEARGR on ((BUH_YEARGR.QUALITYID = IT.BUHQUALITYID) and (BUH_YEARGR.QUALITYTYPEID = 6))
      where ((IT.PARTYID = 62) and (IT.OWNERID = 3881))
        and ((IT.SAMPLERQUALITYID is not null) or (IT.LABQUALITYID is not null) or (IT.BUHQUALITYID is not null))
        and ((SAM_YEARGR.QUALITYVALUE <> 2011) or (LAB_YEARGR.QUALITYVALUE <> 2011) or (BUH_YEARGR.QUALITYVALUE <> 2011))
    into :SAMPLERQUALITYID, :SAM_YEARGR, :LABQUALITYID, :LAB_YEARGR,
         :BUHQUALITYID, :BUH_YEARGR

  do begin
    if (SAM_YEARGR is not null) then
      update QUALITYDATA
      set QUALITYVALUE = 2011
      where ((QUALITYID = :SAMPLERQUALITYID) and (QUALITYTYPEID = 6));

    if (LAB_YEARGR is not null) then
      update QUALITYDATA
      set QUALITYVALUE = 2011
      where ((QUALITYID = :LABQUALITYID) and (QUALITYTYPEID = 6));

    if ((BUH_YEARGR is not null) and (BUHQUALITYID <> LABQUALITYID)) then
      update QUALITYDATA
      set QUALITYVALUE = 2011
      where ((QUALITYID = :BUHQUALITYID) and (QUALITYTYPEID = 6));

    suspend;
  end
end