REM leafcoal.sql
REM (c)David Kurtz 2016
REM 06.04.2016 detect overlapping ranges on other tree nodes
REM 07.04.2016 added update leaf count

rollback;
spool leafcoal
-------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------
set serveroutput on termout off trimspool on
DECLARE
k_testmode     CONSTANT BOOLEAN := TRUE; /*set this false to perform update*/
k_dateformat   CONSTANT VARCHAR2(10) := 'yymmdd';
k_oprid        CONSTANT VARCHAR2(20) := 'david.kurtz';
l_module       VARCHAR2(48);
l_action       VARCHAR2(32);
l_debug_level  INTEGER := 4;
l_debug_indent INTEGER := 0;
-------------------------------------------------------------------------------------------------------
no_structure_found EXCEPTION;
PRAGMA EXCEPTION_INIT(no_structure_found,-20001);
-------------------------------------------------------------------------------------------------------
PROCEDURE debug_msg(p_text VARCHAR2 DEFAULT ''
                   ,p_debug_level INTEGER DEFAULT 5) IS
BEGIN
  IF p_debug_level <= l_debug_level AND p_text IS NOT NULL THEN
    sys.dbms_output.put_line(LPAD('.',l_debug_indent,'.')||'('||p_debug_level||')'||p_text);
  END IF;
END debug_msg;
-------------------------------------------------------------------------------------------------------
PROCEDURE set_action(p_action_name VARCHAR2 DEFAULT ''
                    ,p_debug_level INTEGER DEFAULT 5) IS
BEGIN
  l_debug_indent := l_debug_indent + 1;
  dbms_application_info.set_action(action_name=>p_action_name);
  debug_msg(p_text=>'Setting action to: '||p_action_name,p_debug_level=>p_debug_level);
END set_action;        
-------------------------------------------------------------------------------------------------------
PROCEDURE unset_action(p_action_name VARCHAR2 DEFAULT ''
                      ,p_debug_level INTEGER DEFAULT 7) IS
BEGIN
  IF l_debug_indent > 0 THEN
    l_debug_indent := l_debug_indent - 1;
  END IF;
  dbms_application_info.set_action(action_name=>p_action_name);
  debug_msg(p_text=>'Resetting action to: '||p_action_name,p_debug_level=>p_debug_level);                
END unset_action;
-------------------------------------------------------------------------------------------------------
FUNCTION show_bool(p_bool BOOLEAN) RETURN VARCHAR IS
BEGIN
  IF p_bool THEN
    RETURN 'TRUE';
  ELSE
    RETURN 'FALSE';
  END IF;
END show_bool;
-------------------------------------------------------------------------------------------------------
PROCEDURE get_structure_field
(p_setid         IN VARCHAR2 DEFAULT ' ' 
,p_setcntrlvalue IN VARCHAR2 DEFAULT ' '
,p_tree_name     IN VARCHAR2 DEFAULT ' '
,p_effdt         IN DATE
,p_dtl_tabname   OUT VARCHAR2
,p_dtl_recname   OUT VARCHAR2
,p_dtl_fieldname OUT VARCHAR2
) IS 
  l_module VARCHAR2(48);
  l_action VARCHAR2(32);
BEGIN
  dbms_application_info.read_module(module_name=>l_module, action_name=>l_action);
  set_action(p_action_name=>'get_structure_field('||p_tree_name||'/'||TO_CHAR(p_effdt,k_dateformat)||')');
  debug_msg('get_structure_field('||p_setid||'/'||p_setcntrlvalue||'/'||p_tree_name
                                   ||'/'||TO_CHAR(p_effdt,k_dateformat)||')',6);
  SELECT DECODE(r.sqltablename,' ','PS_'||r.recname,r.sqltablename) dtl_tabname
                      , s.dtl_recname, s.dtl_fieldname
  INTO   p_dtl_tabname, p_dtl_recname, p_dtl_fieldname
  FROM   pstreedefn d
  ,      pstreestrct s
  ,      psrecdefn r
  WHERE  d.setid = p_setid
  AND    d.setcntrlvalue = p_setcntrlvalue
  AND    d.tree_name = p_tree_name
  AND    d.effdt = p_effdt
  AND    d.tree_strct_id = s.tree_strct_id
  AND    s.node_fieldname = 'TREE_NODE'
  AND    r.recname = s.dtl_recname
  ;
  debug_msg('structure field: '||p_dtl_tabname||'.'||p_dtl_fieldname,5);
  unset_action(p_action_name=>l_action);

EXCEPTION
  WHEN no_data_found THEN 
    debug_msg('No tree structure record found',3);
    unset_action(p_action_name=>l_action);
    RAISE_APPLICATION_ERROR(-20001,'No structure record found for tree '
           ||p_setid||'/'||p_setcntrlvalue||'/'||p_tree_name||'/'||TO_CHAR(p_effdt,k_dateformat));
END get_structure_field;
-------------------------------------------------------------------------------------------------------
PROCEDURE one_tree
(p_setid         IN VARCHAR2 DEFAULT ' ' 
,p_setcntrlvalue IN VARCHAR2 DEFAULT ' '
,p_tree_name     IN VARCHAR2 DEFAULT ' '
,p_effdt         IN DATE
) IS 
  l_dtl_tabname   psrecdefn.sqltablename%TYPE;
  l_dtl_recname   pstreestrct.dtl_recname%TYPE;
  l_dtl_fieldname pstreestrct.dtl_fieldname%TYPE;
  l_sql CLOB;

  TYPE refcur IS REF CURSOR;
  c_treeleaf refcur;
  
  l_tree_node_num   pstreeleaf.tree_node_num%TYPE;
  l_range_from      pstreeleaf.range_from%TYPE;
  l_range_to        pstreeleaf.range_to%TYPE;
  l_next_range_from pstreeleaf.range_from%TYPE;
  l_next_range_to   pstreeleaf.range_to%TYPE;
  l_coal_range_from pstreeleaf.range_from%TYPE := '';
  l_coal_range_to   pstreeleaf.range_to%TYPE   := '';
  l_tree_branch     pstreeleaf.tree_branch%TYPE;

  l_leaf_count INTEGER;
  l_coal_count INTEGER := 0;
  l_node_count INTEGER;

  l_module VARCHAR2(48);
  l_action VARCHAR2(32);
BEGIN
  set_action(p_action_name=>'one_tree('||p_tree_name||'/'||TO_CHAR(p_effdt,k_dateformat)||')');
  debug_msg('Processing '||p_setid||'/'||p_setcntrlvalue||'/'||p_tree_name
                                             ||'/'||TO_CHAR(p_effdt,k_dateformat),2);
  get_structure_field(p_setid, p_setcntrlvalue, p_tree_name, p_effdt, l_dtl_tabname, l_dtl_recname, l_dtl_fieldname);

  SELECT COUNT(*)
  INTO   l_node_count
  FROM   pstreenode
  WHERE  setid = p_setid
  AND    setcntrlvalue = p_setcntrlvalue
  AND    tree_name = p_tree_name
  AND    effdt = p_effdt;

  SELECT COUNT(*)
  INTO   l_leaf_count
  FROM   pstreeleaf
  WHERE  setid = p_setid
  AND    setcntrlvalue = p_setcntrlvalue
  AND    tree_name = p_tree_name
  AND    effdt = p_effdt;

  debug_msg(l_node_count||' nodes, '||l_leaf_count||' leaves',4);
  --dynamic sql to find coalescible leaves.  No ref values between ranges or single values
  --do not process branched trees
  --Dynamic because structure field is configurable
  --assume SETID in key field
  --ignore EFFDT processing.  Do not coalesce even if not effective as at tree date
  l_sql := 'WITH x AS (
SELECT l.*
,      LEAD(range_from,1) OVER (PARTITION BY setid, setcntrlvalue, effdt, tree_node_num, tree_branch ORDER BY range_from) next_range_from
,      LEAD(range_to,1) OVER (PARTITION BY setid, setcntrlvalue, effdt, tree_node_num, tree_branch ORDER BY range_from) next_range_to
FROM   pstreeleaf l
WHERE  l.setid = :p1
AND    l.setcntrlvalue = :p2
AND    l.tree_name = :p3
AND    l.effdt = :p4
), y AS (
SELECT  x.*
,       (SELECT count(*)
        FROM    '||l_dtl_tabname||' d
        WHERE   ';

  FOR i IN (
    SELECT fieldname
    FROM   psrecfielddb
    WHERE  recname = l_dtl_recname
    AND    MOD(useedit,2) = 1
    AND    fieldname = 'SETID'
  ) LOOP
    l_sql := l_sql||'d.'||i.fieldname||' = x.'||i.fieldname||' AND ';
  END LOOP;

  l_sql := l_sql||'d.'||l_dtl_fieldname||' > x.range_to 
             AND   d.'||l_dtl_fieldname||' < x.next_range_from) intermediates
,       (SELECT count(*)
        FROM    PSTREELEAF f
        WHERE   f.setid = x.setid
        AND     f.setcntrlvalue = x.setcntrlvalue
        AND     f.tree_name = x.tree_name
        AND     f.effdt = x.effdt
        AND     f.tree_node_num != x.tree_node_num
        AND     f.range_from <= x.next_range_to
        AND     f.range_to >= x.range_from
        ) overlapers FROM    x
WHERE   next_range_From is not null
)
select tree_node_num, range_from, range_to, next_range_from, next_range_to, tree_branch
from y
where intermediates=0
and   overlapers=0
order by 1,2';
  debug_msg('SQL:'||l_sql,8);

  OPEN c_treeleaf FOR l_sql USING p_setid, p_setcntrlvalue, p_tree_name, p_effdt;
  LOOP
    FETCH c_treeleaf INTO l_tree_node_num, l_range_from, l_range_to, l_next_range_from, l_next_range_to, l_tree_branch;
    EXIT WHEN c_treeleaf%NOTFOUND;
    IF l_coal_count = 0 THEN
      IF k_testmode THEN --update tree version prior to first coalesce, but not in test mode
        debug_msg('Running in Test Mode - No Update',1);
      ELSE
        UPDATE psversion 
        SET    version = version+1
        WHERE  objecttypename IN('SYS','TDM');

        UPDATE pslock
        SET    version = version+1
        WHERE  objecttypename IN('SYS','TDM');
      END IF;
    END IF;
    l_coal_count := l_coal_count + 1;

    IF l_range_to = l_coal_range_to THEN
      l_coal_range_to := l_next_range_to;
    ELSE
      l_coal_range_from := l_range_from;
      l_coal_range_to := l_next_range_to;
    END IF;

    --coalesce two leaves - update range on one leaf
    IF k_testmode THEN
      debug_msg(l_range_from||'-'||l_range_to||' + '||l_next_range_from||'-'||l_next_range_to||' => '||l_coal_range_from||'-'||l_coal_range_to,4);
    ELSE
      debug_msg(l_range_from||'-'||l_range_to||' + '||l_next_range_from||'-'||l_next_range_to||' => '||l_coal_range_from||'-'||l_coal_range_to,6);

      UPDATE pstreeleaf
      SET    range_from = l_coal_range_from
      ,      range_to = l_coal_range_to
      WHERE  setid = p_setid
      AND    setcntrlvalue = p_setcntrlvalue
      AND    tree_name = p_tree_name
      AND    effdt = p_effdt
      AND    tree_node_num = l_tree_node_num
      AND    range_from = l_next_range_from
      AND    range_to = l_next_range_to
      AND    tree_branch = l_tree_branch;
      debug_msg(SQL%ROWCOUNT||' rows updated',7);

      --delete the other leaf
      DELETE pstreeleaf
      WHERE  setid = p_setid
      AND    setcntrlvalue = p_setcntrlvalue
      AND    tree_name = p_tree_name
      AND    effdt = p_effdt
      AND    tree_node_num = l_tree_node_num
      AND    range_from = l_coal_range_from
      AND    range_to = l_range_to
      AND    tree_branch = l_tree_branch;
      debug_msg(SQL%ROWCOUNT||' rows deleted',7);
    END IF;
  END LOOP;

  IF NOT k_testmode AND l_coal_count > 0 THEN
    UPDATE pstreedefn d
    SET    d.version = (SELECT version FROM pslock WHERE objecttypename = 'TDM')
    ,      d.lastupdoprid = k_oprid
    ,      d.lastupddttm = SYSTIMESTAMP
    ,      d.leaf_count = (SELECT COUNT(*)
                           FROM   pstreeleaf l
                           WHERE  l.setid = p_setid
                           AND    l.setcntrlvalue = p_setcntrlvalue
                           AND    l.tree_name = p_tree_name
                           AND    l.effdt = p_effdt)
    WHERE  d.setid = p_setid
    AND    d.setcntrlvalue = p_setcntrlvalue
    AND    d.tree_name = p_tree_name
    AND    d.effdt = p_effdt;
  END IF;

  CLOSE c_treeleaf;
  debug_msg(l_coal_count||' leaves coalesced ('||LTRIM(TO_CHAR(100*l_coal_count/NULLIF(l_leaf_count,0),'90'))||'%)',3);
  unset_action(p_action_name=>l_action);
EXCEPTION 
  WHEN no_structure_found THEN
    debug_msg('Skipping tree',3);
    unset_action(p_action_name=>l_action);
END one_tree;
-------------------------------------------------------------------------------------------------------
BEGIN
  dbms_application_info.read_module(module_name=>l_module, action_name=>l_action);
  dbms_application_info.set_module(module_name=>'LEAFCOALESCE', action_name=>'MAIN');
  FOR i IN (
    SELECT DISTINCT d.setid, d.setcntrlvalue, d.tree_name, d.effdt
    FROM   pstreeselctl d
/*  FROM   pstreedefn d    
    ,      pstreestrct s
    ,      psrecfielddb f
    WHERE  d.tree_strct_id = s.tree_strct_id
    AND    s.node_fieldname = 'TREE_NODE'
--  AND    d.TREE_ACC_METHOD = 'L' --literal values
    AND    s.dtl_recname = f.recname 
    AND    s.dtl_fieldname = f.fieldname*/
--  AND    tree_name = 'QBE_LE_OPROLLUP'
  ) LOOP
    one_tree(i.setid, i.setcntrlvalue, i.tree_name, i.effdt);
    debug_msg(' ',4);
  END LOOP;
  unset_action(p_action_name=>l_action);
  IF k_testmode THEN
    debug_msg('Running in Test Mode - No Update',1);
  ELSE
    debug_msg('Commit changes or rollback',1);
  END IF;
END;
/
spool off
set termout on
