I am trying to find the whole lineage and loading process of a certain table column. To begin with i am locating the mapping used to populate the column. To do this i use:
select m.name mapping_name, mr.qualified_name, mc.name datastore_alias, t.table_name target_table, mdl.cod_mod model_code from snp_mapping m inner join snp_map_comp mc on m.i_mapping = mc.i_owner_mapping inner join snp_map_cp cp on mc.i_map_comp = cp.i_owner_map_comp inner join snp_map_ref mr on mc.i_map_ref = mr.i_map_ref inner join snp_table t on mr.i_ref_id = t.i_table inner join snp_model mdl on t.i_mod = mdl.i_mod where cp.direction = 'O' and --output connection point cp.i_map_cp not in (select i_start_map_cp from snp_map_conn) --not a starting connection point;
which works very nice. (Thread found here : https://stackoverflow.com/questions/60566000/odi-column-lineage-query/60566807#60566807).
Now i need to find the package which uses this mapping, so that i will be able to investigate the whole loading process. So the question is: is there a query which can return the packages that use the mapping defined?. I did find an SNP_PACKAGES table but i am not sure if i can get anything from there.