ODI BIAPPS find Forward and Backward Dependancy

In ODI, many times we want to know where 
i) a table is used as source (Forward)
ii) tables used to populate the target table (Backward)

The following procedure recursively scans the usage of yellow interfaces as source tables as well as lookups and determine the Forward and Backward Dependancy. There is a column available in output known as Lineage Path which shows the path to determine the dependancy.

The Path column shows the lineage of dependency in following format

InterfaceName : POP_ID(Target Table) -> Next Lineage

For eg as below:

SIL_GroupAccountReverse_GetReversalIntID.W_REVERSAL_INT_ID_TMP_SQ_W_GL_OTHER_F:45089002(Temp)»SIL_GroupAccountReverse_GetReversalIntID.W_REVERSAL_INT_ID_TMP:34912002(W_REVERSAL_INT_ID_TMP)

For forward dependency,    it shows the various targets populated by the table mentioned

For backward dependency, it shows the various sources used to populated the table mentioned

           Step 1- Compile the following objects

drop type dependancy_tab;

drop type dependancy_row;

create type dependancy_row as object ( trace_type varchar2(400), table_name varchar2(400), parent_folder varchar2(400), folder varchar2(400), scenario varchar2(400), dependant_table varchar2(400), lineage_path varchar2(4000) );

/

create type dependancy_tab is table of dependancy_row;

/

Step 2 - Compile the following function

CREATE OR REPLACE FUNCTION find_dependancy(
    p_table_list VARCHAR2,
    p_load_plan  NUMBER DEFAULT 1)
  RETURN dependancy_tab
IS
  l_result_tab dependancy_tab := dependancy_tab();
  l_path_string VARCHAR2(32627);
  l_tab_list    VARCHAR2(32627);
  l_scen_name   VARCHAR2(4000);
  l_table_name  VARCHAR2(30);
  o_table_name  VARCHAR2(30);
type l_tab_type
IS
  TABLE OF VARCHAR2(4000) INDEX BY VARCHAR2(4000);
  l_scen l_tab_type;
  --Backward Trace
PROCEDURE backward_trace(
    l_pop   NUMBER,
    l_level NUMBER)
IS
  CURSOR c1
  IS
    SELECT DISTINCT src.res_name source_table_name,
      snp_source_tab.i_pop_sub,
      snp_pop.pop_name AS interface_name,
      nvl2(snp_pop.i_pop, nvl2(snp_pop.i_table, snp_table.res_name, 'Temp'), NULL) target_name,
      snp_pop.i_pop
    FROM snp_source_tab,
      snp_data_set,
      snp_pop,
      snp_table,
      snp_table src
    WHERE snp_pop.i_pop         = l_pop
    AND snp_data_set.i_data_set = snp_source_tab.i_data_set (+)
    AND snp_pop.i_pop           = snp_data_set.i_pop (+)
    AND snp_table.i_table(+)    = snp_pop.i_table
    AND src.i_table (+)         = snp_source_tab.i_table;
BEGIN
  FOR r1 IN c1
  LOOP
    IF r1.source_table_name IS NOT NULL THEN
      l_path_string         := l_path_string||'»'||r1.interface_name||':'||r1.i_pop||'('||r1.target_name||')';
      l_result_tab.extend;
      l_result_tab(l_result_tab.last) := dependancy_row('Backward',o_table_name,SUBSTR(l_scen_name,1,instr(l_scen_name,'|')-1), SUBSTR(l_scen_name,instr(l_scen_name,'|')+1,instr(l_scen_name,'|',1,2)-instr(l_scen_name,'|')-1), SUBSTR(l_scen_name,instr(l_scen_name,'|',1,2)+1),r1.source_table_name,l_path_string);
      --dbms_output.put_line(o_table_name||'|'||l_scen_name||'|'||r1.source_table_name||'|'||l_path_string);
    ELSE
      backward_trace(r1.i_pop_sub, l_level+1);
    END IF;
  END LOOP;
END;
--Trace Backward Main
PROCEDURE trace_backward
IS
  CURSOR c_main
  IS
    SELECT DISTINCT snp_folder_parent.folder_name
      ||'|'
      ||snp_folder.folder_name
      ||'|'
      ||snp_scen.scen_name scen_name,
      src.res_name source_table_name,
      snp_source_tab.i_pop_sub,
      snp_folder_parent.folder_name parent_folder_name,
      snp_folder.folder_name,
      snp_pop.pop_name interface_name,
      snp_table.res_name target_name,
      snp_pop.i_pop
    FROM snp_source_tab,
      snp_table src,
      snp_data_set,
      snp_pop,
      snp_table,
      snp_package,
      snp_scen,
      snp_step,
      snp_folder,
      snp_folder snp_folder_parent
    WHERE src.i_table (+)          = snp_source_tab.i_table
    AND snp_folder.i_folder        = snp_pop.i_folder
    AND snp_folder_parent.i_folder = snp_folder.par_i_folder
      --AND snp_folder_parent.folder_name in ('SILOS','PLP','CUSTOM_SILOS','CUSTOM_SDE_ORAR1213_Adaptor','CUSTOM_PLP','SDE_ORAR1213_Adaptor','Mappings')
    AND snp_data_set.i_data_set = snp_source_tab.i_data_set
    AND snp_pop.i_pop           = snp_data_set.i_pop
    AND snp_table.i_table       = snp_pop.i_table
    AND snp_table.res_name      = o_table_name
    AND snp_package.i_package   = snp_step.i_package
    AND snp_step.i_pop          = snp_pop.i_pop
    AND snp_package.i_package   = snp_scen.i_package
    AND ((p_load_plan           = 1
    AND snp_scen.scen_version  IN
      (SELECT MAX(snp_scen_ver.scen_version)
      FROM snp_lpi_step,
        snp_scen snp_scen_ver
      WHERE snp_lpi_step.scen_name = snp_scen_ver.scen_name
      AND snp_scen.scen_name       = snp_scen_ver.scen_name
      ))
    OR p_load_plan <> 1);
  BEGIN
    FOR r1 IN c_main
    LOOP
      IF r1.source_table_name IS NOT NULL THEN
        l_path_string         := r1.interface_name||':'||r1.i_pop||'('||r1.target_name||')';
        l_scen_name           := r1.scen_name;
        l_result_tab.extend;
        l_result_tab(l_result_tab.last) := dependancy_row('Backward',o_table_name,SUBSTR(l_scen_name,1,instr(l_scen_name,'|')-1), SUBSTR(l_scen_name,instr(l_scen_name,'|')+1,instr(l_scen_name,'|',1,2)-instr(l_scen_name,'|')-1), SUBSTR(l_scen_name,instr(l_scen_name,'|',1,2)+1),r1.source_table_name,l_path_string);
        --dbms_output.put_line(o_table_name||'|'||l_scen_name||'|'||r1.source_table_name||'|'||l_path_string);
      ELSE
        backward_trace(r1.i_pop_sub, 1);
      END IF;
    END LOOP;
  END;
  --Forward Trace
PROCEDURE forward_trace(
    l_pop   NUMBER,
    l_level NUMBER)
IS
  CURSOR c1
  IS
    SELECT DISTINCT snp_pop.pop_name AS interface_name,
      nvl2(snp_pop.i_pop, nvl2(snp_pop.i_table, snp_table.res_name, 'Temp'), NULL) target_name,
      snp_pop.i_pop
    FROM snp_source_tab,
      snp_data_set,
      snp_pop,
      snp_table
    WHERE l_pop                     = snp_source_tab.i_pop_sub
    AND snp_data_set.i_data_set (+) = snp_source_tab.i_data_set
    AND snp_pop.i_pop (+)           = snp_data_set.i_pop
    AND snp_table.i_table(+)        = snp_pop.i_table;
  CURSOR c2(c_pop NUMBER)
  IS
    SELECT DISTINCT snp_folder_parent.folder_name
      ||'|'
      ||snp_folder.folder_name
      ||'|'
      ||snp_scen.scen_name scen_name
    FROM snp_scen,
      snp_package,
      snp_step,
      snp_pop,
      snp_folder,
      snp_folder snp_folder_parent
    WHERE snp_package.i_package    = snp_step.i_package
    AND snp_step.i_pop             = snp_pop.i_pop
    AND snp_pop.i_pop              = c_pop
    AND snp_folder.i_folder        = snp_pop.i_folder
    AND snp_folder_parent.i_folder = snp_folder.par_i_folder
    AND snp_package.i_package      = snp_scen.i_package
    AND ((p_load_plan              = 1
    AND snp_scen.scen_version     IN
      (SELECT MAX(snp_scen_ver.scen_version)
      FROM snp_lpi_step,
        snp_scen snp_scen_ver
      WHERE snp_lpi_step.scen_name = snp_scen_ver.scen_name
      AND snp_scen.scen_name       = snp_scen_ver.scen_name
      ))
    OR p_load_plan <> 1);
  BEGIN
    FOR r1 IN c1
    LOOP
      l_path_string := l_path_string ||'»' ||r1.interface_name ||':' ||r1.i_pop ||'(' ||r1.target_name ||')';
      FOR r2 IN c2(r1.i_pop)
      LOOP
        l_scen(r2.scen_name) := r1.target_name;
      END LOOP;
      forward_trace(r1.i_pop, l_level +1);
    END LOOP;
  END;
  --Trace Forward Main
PROCEDURE trace_forward
IS
  l_tab l_tab_type;
  CURSOR c_main
  IS
    SELECT DISTINCT src.res_name source_table_name,
      snp_folder_parent.folder_name
      ||'|'
      ||snp_folder.folder_name
      ||'|'
      ||snp_scen.scen_name scen_name,
      snp_pop.pop_name interface_name,
      nvl2(snp_pop.i_pop, nvl2(snp_pop.i_table, snp_table.res_name, 'Temp'), NULL) target_name,
      snp_pop.i_pop
    FROM snp_source_tab,
      snp_table src,
      snp_folder,
      snp_folder snp_folder_parent,
      snp_data_set,
      snp_pop,
      snp_table,
      snp_step,
      snp_package,
      snp_scen
    WHERE src.i_table                  = snp_source_tab.i_table
    AND src.res_name                   = o_table_name
    AND snp_folder.i_folder            = snp_pop.i_folder
    AND snp_folder_parent.i_folder     = snp_folder.par_i_folder
    --AND snp_folder_parent.folder_name IN ('SILOS','PLP','CUSTOM_SILOS','CUSTOM_SDE_ORAR1213_Adaptor','CUSTOM_PLP','SDE_ORAR1213_Adaptor','Mappings')
    AND snp_data_set.i_data_set        = snp_source_tab.i_data_set
    AND snp_pop.i_pop                  = snp_data_set.i_pop
    AND snp_table.i_table(+)           = snp_pop.i_table
    AND snp_step.i_pop (+)             = snp_pop.i_pop
    AND snp_package.i_package (+)      = snp_step.i_package
    AND snp_package.i_package          = snp_scen.i_package (+)
    AND ((p_load_plan                  = 1
    AND snp_scen.scen_version         IN
      (SELECT MAX(snp_scen_ver.scen_version)
      FROM snp_lpi_step,
        snp_scen snp_scen_ver
      WHERE snp_lpi_step.scen_name = snp_scen_ver.scen_name
      AND snp_scen.scen_name       = snp_scen_ver.scen_name
      ))
    OR p_load_plan           <> 1
    OR snp_scen.scen_version IS NULL);
  BEGIN
    l_tab.delete;
    FOR r1 IN c_main
    LOOP
      l_path_string := r1.interface_name||':'||r1.i_pop||'('||r1.target_name||')';
      l_scen.delete;
      forward_trace(r1.i_pop, 2);
      l_scen_name         := NULL;
      l_scen_name         := l_scen.first;
      IF l_scen_name      IS NOT NULL THEN
        WHILE l_scen_name IS NOT NULL
        LOOP
          l_table_name := l_scen(l_scen_name);
          l_result_tab.extend;
          l_result_tab(l_result_tab.last) := dependancy_row('Forward',o_table_name,SUBSTR(l_scen_name,1,instr(l_scen_name,'|')-1), SUBSTR(l_scen_name,instr(l_scen_name,'|')+1,instr(l_scen_name,'|',1,2)-instr(l_scen_name,'|')-1), SUBSTR(l_scen_name,instr(l_scen_name,'|',1,2)+1),l_table_name,l_path_string);
          --dbms_output.put_line(o_table_name||'|'||l_scen_name||'|'||l_table_name||'|'||l_path_string);
          l_tab(l_table_name) := l_table_name;
          l_scen_name         := l_scen.next(l_scen_name);
        END LOOP;
      ELSIF r1.target_name <> 'Temp' THEN
        l_result_tab.extend;
        l_scen_name                     := r1.scen_name;
        l_result_tab(l_result_tab.last) := dependancy_row('Forward',o_table_name,SUBSTR(l_scen_name,1,instr(l_scen_name,'|')-1), SUBSTR(l_scen_name,instr(l_scen_name,'|')+1,instr(l_scen_name,'|',1,2)-instr(l_scen_name,'|')-1), SUBSTR(l_scen_name,instr(l_scen_name,'|',1,2)+1),r1.target_name,l_path_string);
        --dbms_output.put_line(o_table_name||'|||||'||l_path_string);
      END IF;
    END LOOP;
  END;
  BEGIN
    l_tab_list := p_table_list;
    --dbms_output.put_line('Target Table|Parent Folder|Folder|Scenario|Source Table|Backward Path');
    WHILE rtrim(l_tab_list,',') IS NOT NULL
    LOOP
      o_table_name    := rtrim(SUBSTR(l_tab_list, instr(l_tab_list, ',')+1),',');
      IF o_table_name IS NOT NULL THEN
        trace_backward;
        trace_forward;
      END IF;
      l_tab_list := rtrim(REPLACE(l_tab_list,o_table_name),',');
    END LOOP;
    RETURN l_result_tab;
  END;
  /

Step 3- Run the following query to view output

select * 

from table(find_dependancy('W_GL_ACCOUNT_D,W_NATURAL_ACCOUNT_D'))

where scenario is not null; -- Filter the orphan lineages