Узнать актуальность данных в глобальнике по сети — различия между версиями

Версия 13:20, 29 августа 2016

Отправьте по g-tasks запрос:

SET TERM ^ ;
create or alter procedure PR_GET_LAST_MGN_UPDATE
as
declare variable CODE_PROFILE DM_ID;
declare variable MGN_CONNSTR DM_TEXT;
declare variable INSERTDT DM_DATETIME;
begin
  select P.PARAM_VALUE
  from PARAMS P
  where P.PARAM_ID = 'CODE_PROFILE'
  into :CODE_PROFILE;

  select P.PARAM_VALUE
  from PARAMS P
  where P.PARAM_ID = 'MGN_CONNSTR'
  into :MGN_CONNSTR;

  if (:CODE_PROFILE is not null) then
  if (:CODE_PROFILE>0) then
  begin
    code_profile=(-1)*(100+:code_profile);
    execute statement 'select first 1 z.insertdt from zmain_dict z order by z.insertdt desc'
        on external :MGN_CONNSTR
        as user 'SYSDBA' password 'masterkey'
        into :INSERTDT;
    update or insert into REPORTS (ID, PARENT_ID, STATUS, REPORTTYPE, SORTING, CAPTION, PARAMS)
    values (:code_profile, 0, -1, -1, 0, 'mgn_last_update', :INSERTDT)
    matching (ID);
    suspend;
  end
end^

SET TERM ; ^

/* Following GRANT statetements are generated automatically */

GRANT SELECT ON PARAMS TO PROCEDURE PR_GET_LAST_MGN_UPDATE;
GRANT SELECT,INSERT,UPDATE ON REPORTS TO PROCEDURE PR_GET_LAST_MGN_UPDATE;

/* Existing privileges on this procedure */

GRANT EXECUTE ON PROCEDURE PR_GET_LAST_MGN_UPDATE TO SYSDBA;

execute procedure PR_GET_LAST_MGN_UPDATE;

drop procedure PR_GET_LAST_MGN_UPDATE;

После того как он выполнится, выполните запрос:

select
P.ID,
P.CAPTION,
P.DESCRIPTION,
(select r.params from reports r where r.g$profile_id=p.id and r.id between -200 and -100)
from G$PROFILES P
order by p.id