Find SQL override repository query

1. To know the SQL overrides in mapping Source Qualifier transformation or
Lookup Overrides or Pre SQL or 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

2. In a mapping with multiple expression transformations and multiple
unconnected lookups, it is difficult for you to identify the expressions calling
these unconnected lookups. Use the below SQL to identify the same

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

3. In a workflow that contains multiple levels, if we need to know the sessions/
commands that are present at each level then the below query would give the same.

SELECT LEVEL lvl_x,

    INST.WORKFLOW_ID,
    inst.Task_id,
    sys_connect_by_path( inst.Instance_name ,'-->') ,
    LPAD (' ', 8 * LEVEL) || inst.Task_Id || ' ' ||
    inst.Instance_name descr
    FROM infa_repository.opb_task_inst inst
    START WITH inst.task_id in (select task.task_id From
    infa_repository.rep_workflows wf
    inner join infa_repository.rep_task_inst task on wf.workflow_id
    = task.workflow_id
    WHERE
    workflow_name = '<WORKFLOW_NAME>'
    and task.task_type_name <> 'Start')
    CONNECT BY PRIOR inst.task_id = INST.workflow_id
    order by lvl_x desc , INST.WORKFLOW_ID