SQL Overrides in Lookups, Source Qualifier etc

I have seen in many instances, you would want to know the SQL overrides written in a mapping Source Qualifier transformation or Lookup SQL override or Pre-SQL & Post-SQL etc. Below is the query that takes the workflow name & Folder name as input and gives you all the SQL overrides wherever they are in the workflow.

select folder, wf_name, 
       sess_name, mapping_name, 
       transformation_name, attr_name, 
       line_no, sql_value
from (select f.subj_name folder, 
             wf.task_name wf_name, 
             sess.instance_name sess_name, 
             m.mapping_name mapping_name, 
             w_inst.instance_name transformation_name, 
             attr.line_no, attr.attr_value sql_value, 
             attr_type.attr_name attr_name,
             row_number() over (partition by wf.task_name, 
                                             sess.instance_name, 
                                             m.mapping_name, 
                                             w_inst.instance_name, 
                                             attr.line_no, 
                                             attr.attr_value
                                order by attr.session_task_id desc
                               ) rn
      from opb_task_inst wf_inst
           ,opb_task_inst sess
           ,opb_session s
           ,opb_mapping m
           ,opb_subject f
           ,opb_widget_attr attr
           ,opb_widget_inst w_inst
           ,opb_task wf
           ,(select o.object_type_id object_type_id, 
                    o_attr.attr_id attr_id, 
                    o.object_type_name||': '||o_attr.attr_name attr_name
             from opb_attr o_attr, 
                  opb_object_type o
             where o.object_type_id = o_attr.object_type_id
             and o_attr.attr_datatype = 2
             and o_attr.attr_value is null
             and upper(o_attr.attr_name) like '%SQL%'
            ) attr_type
      where wf_inst.task_id = sess.task_id
      and sess.task_type = 68
      and sess.task_id = s.session_id
      and wf.subject_id = f.subj_id
      and s.mapping_id = m.mapping_id
      and attr.widget_id = w_inst.widget_id
      and w_inst.mapping_id = m.mapping_id
      and w_inst.widget_type = attr_type.object_type_id
      and wf_inst.workflow_id = wf.task_id
      and wf.task_type = 71
      and (attr.session_task_id = s.session_id
           or attr.session_task_id = 0)
      and attr.attr_id = attr_type.attr_id
      and attr.attr_value is not null
      and attr.attr_value <> '0'
      and wf.task_name = 'WORKFLOW_NAME'
      and f.subj_name = 'FOLDER_NAME'
      )
where rn = 1
order by 1, 2, 3, 4, 5, 6, 7

Shortcut Object and its parent folder

Have you always wondered how to get the shortcut object & it's parent folder. This requirement is imminent in folders where you have tens of shortcut transformations & mappings. Below query can get you what you want.

select shortcut_name, 
       case s.object_type
         when 21 then 'Mapping'
         when 23 then (select o.object_type_name
                       from opb_object_type o
                           ,opb_widget w
                       where w.widget_id = s.object_id
                       and w.widget_type = o.object_type
                      )
         when 24 then 'Target'
         when 25 then 'Source'
         when 44 then 'Mapplet'
         else 'Nothing'
       end Object_Type,
       decode(s.object_type, 21, (select f.subj_name
                                  from opb_subject f,
                                       opb_mapping m
                                  where m.mapping_id = s.object_id
                                  and m.subject_id = f.subj_id
                                  ),
                             23, (select f.subj_name
                                  from opb_subject f,
                                       opb_widget w
                                  where w.widget_id = s.object_id
                                  and w.subject_id = f.subj_id
                                  ),
                             25, (select f.subj_name
                                  from opb_subject f,
                                       opb_src src
                                  where src.src_id = s.object_id
                                  and src.subj_id = f.subj_id
                                  ),
                             24, (select f.subj_name
                                  from opb_subject f,
                                       opb_targ t
                                  where t.target_id = s.object_id
                                  and t.subj_id = f.subj_id
                                  ),
                             44, (select f.subj_name
                                  from opb_subject f,
                                       opb_widget w
                                  where w.widget_id = s.object_id
                                  and w.subject_id = f.subj_id
                                  )
              ) obj_original_folder
from opb_shortcut s, 
     opb_subject f
where s.subject_id = f.subj_id
and f.subj_name = 'FOLDER_NAME'

Repository tables Expression query

In a mapping with multiple expression transformations and multiple unconnected lookups, it is difficult for you to identify the expressions calling these unconnected lookups. This SQL will give you what you are looking for.

select w.instance_name,
       g.expression
from opb_widget_expr f,
     opb_expression g,
     opb_widget_inst w,
     opb_mapping m
where f.expr_id = g.expr_id
and f.widget_id = w.widget_id
and w.widget_type = 5
and w.mapping_id = m.mapping_id
and m.mapping_name = 'm_Open_Trades_Target_load'