Типовые запросы к реляционным таблицам Planning

Данные запросы создавались для версии 11.1.1.3 на БД Oracle 10g

/* Список групп */ 
SELECT g.object_name as group_nane
FROM hsp_group ug, hsp_object g
WHERE ug.group_id = g.OBJECT_ID
ORDER by ug.group_id; 

/* Список пользователей и групп */ 
SELECT g.object_name as group_nane, u.object_name as user_name
FROM hsp_usersingroup ug
   , hsp_object g
   , hsp_object u
WHERE ug.group_id = g.OBJECT_ID
AND ug.user_id = u.OBJECT_ID
ORDER by ug.group_id;

/* Статусы сценариев */
SELECT scenario.object_name Scenario
     , start_y.object_name Start_Year
     , end_y.object_name End_Year
     , start_p.object_name Start_Period
     , end_p.object_name End_Period
FROM HSP_SCENARIO s
   , hsp_object scenario
   , hsp_object start_y
   , hsp_object end_y
   , hsp_object start_p
   , hsp_object end_p
WHERE 1=1
AND s.scenario_id = scenario.object_id
AND s.start_yr_id = start_y.object_id
AND s.end_yr_id = end_y.object_id
AND s.start_tp_id = start_p.object_id
AND s.end_tp_id = end_p.object_id;

/* Настройка безопасности */
SELECT user_name, o_name memeber,
case when ACCESS_MODE=-1 then 'None'
when ACCESS_MODE=3 then 'Write'
when ACCESS_MODE=1 then 'Read'
else 'UnKnown'
end access_type,
case when FLAGS=0 Then 'MEMBER'
when FLAGS=9 Then '@IDESCENDANTS'
when FLAGS=8 Then '@DESCENDANTS'
when FLAGS=6 Then '@ICHILDREN'
when FLAGS=5 Then '@CHILDREN'
else 'UnKnown'
end member_type
FROM HSP_ACCESS_CONTROL
inner join (SELECT object_name as user_name, object_id as u_id 
            FROM hsp_object) u 
      on hsp_access_control.user_id=u_id
inner join (SELECT object_name as o_name, object_id as o_id 
            FROM hsp_object WHERE hsp_object.object_type !=7 ) o 
      on OBJECT_ID=o_id;

/* Пример построение parent-child запроса */
SELECT object_name
FROM hsp_object
WHERE 1=1
--and has_children = 0 -- только уровень 0
START WITH UPPER(object_name) = UPPER('Account') -- все потомки Account
CONNECT BY PRIOR object_id = parent_id;

/* Пример запроса для выгрузки настроек измерения Account */
SELECT o2.object_name PARENT ,
    o.object_name CHILD ,
    o.generation ,
    o.position ,
    DECODE(m.data_storage, 2, 'O', 5, 'X', 'N') data_storage, -- N Never, O - Label, X - Dynamic ,
    DECODE(m.twopass_calc, 1, 'T', '') twopass_calc ,
    m.data_type ,
    DECODE(m.consol_op1, 6, '^', 5, '~', '+') consol_op ,
    DECODE(a.time_balance, 2, 'L', '') time_balance ,
    a.account_type ,
    DECODE(a.variance_rep, 1, 'E', '') variance_rep ,
    path
  FROM hsp_member m ,
    (SELECT parent_ID,
      OBJECT_ID,
      object_name,
      generation,
      position,
      sys_connect_by_path( acc.object_name, '/') path
    FROM hsp_object acc
    WHERE 1 =1
      START WITH acc.object_name     = 'Account'
      CONNECT BY PRIOR acc.object_id = acc.parent_id
    ) o ,
    HSP_OBJECT o2 ,
    hsp_account a
  WHERE 1         =1
  AND O.OBJECT_ID = m.member_id
  AND O.parent_ID = O2.OBJECT_ID
  AND m.member_id = a.account_id
  AND a.used_in   = '1' -- For 1-st plan;

/* Список измененных объектов в Planning, которые должны обновиться в Essabse */ 
 SELECT   'PL' AS application,
          ot.type_name object_type,
          t.object_id,
          t.plan_type,
          t.xact_type,
          t.object_name,
          t.old_name,
          t.xact_date,
          t.src_mbr,
          o.modified_by
   FROM   hp_PL.hsp_pending_xacts t,
          hp_PL.hsp_object o,
          hp_PL.hsp_object_type ot
  WHERE   t.object_id = o.object_id(+)
          AND o.object_type = ot.object_type(+)