Page Comparison

select 
'call infacmd.bat isp getsessionlog -dn DOMAIN<INFORMATICA_PRODUCTIONDOMAIN> -hp <HOST>:<PORT> -un <SERVER USERNAME> -pd <SERVER PASSWORD> -is <SERVERNAME> -rs <REPO NAME> -ru <REPO USERNAME> -rp <REPO PASSWORD> -fm xml -fn ' || ws.subject_area || ' -wf ' || ws.workflow_name || ' -ss ' || CASE WHEN hierarchy_structure is null then ws.instance_name ELSE '"' || substr(hierarchy_structure, 2) || '"' END || ' -lo <C:\\output\\path\\for\\logs\\>' || ws.workflow_id || '_' || ws.task_id || '_' || ws.instance_id as cmd
from (
    SELECT ti.instance_name,
        ti.task_id,
        ti.version_number,
        wws.instance_id,
        wf.workflow_id,
        wf.workflow_name,
        wf.workflow_comments,
        wf.server_name,
        wf.subject_area,
        hierarchy_structure,
        path
FROM (
        select path 
        , TO_NUMBER(substr(path, 2, instr(path,'/',1, 2)-2)) as workflow_id
        , TO_NUMBER(substr(path, -instr(reverse(path),'/', 1, 2)+1, instr(reverse(path),'/', 1, 2)-2)) as task_id
        , hierarchy_structure
        , instance_id
        from (SELECT DISTINCT '/' || temp1.task_id AS path
                , temp1.task_name AS hierarchy_structure
                , 0 as instance_id
                FROM opb_task temp1, opb_subject temp2
                WHERE temp1.subject_id = temp2.subj_id
                AND temp1.task_type = 71 -- workflows
                UNION ALL
                SELECT DISTINCT temp1.path
                    , temp1.task_name AS hierarchy_structure
                    , instance_id
                FROM (SELECT opb_task_inst.workflow_id, opb_task_inst.task_id, opb_task_inst.instance_id, LEVEL depth,
                        SYS_CONNECT_BY_PATH(opb_task_inst.workflow_id ,'/') || '/' || opb_task_inst.task_id || '/' path,
                        SYS_CONNECT_BY_PATH(opb_task_inst.instance_name ,'/') task_name
                        FROM opb_task_inst
                        WHERE opb_task_inst.task_type IN (68,70)
                        START WITH workflow_id IN (select distinct w.workflow_id
                                                        from rep_workflows w
                                                        join rep_task_inst ti on w.workflow_id = ti.workflow_id
                                                        where ti.task_type_name = 'Worklet'
                                                        and w.subject_area not in ('<SUBJECT_AREAS_TO_EXCLUDE>')
                                                    )
                        CONNECT BY PRIOR opb_task_inst.task_id = opb_task_inst.workflow_id
                    ) temp1,
                    opb_task temp2,
                    opb_subject temp3
                WHERE temp2.subject_id = temp3.subj_id
                AND temp2.task_id = SUBSTR(temp1.path,2, INSTR(temp1.path,'/', 1, 2) -2 )
                ORDER BY path ASC )
        where instance_id <> 0
) wws
    JOIN rep_task_inst ti on ti.task_id = wws.task_id and ti.task_type = 68
    JOIN REP_WORKFLOWS wf on wws.workflow_id = wf.workflow_id
UNION
SELECT ti.instance_name,
    ti.task_id,
    ti.version_number,
    ti.instance_id,
    wf.workflow_id,
    wf.workflow_name,
    wf.workflow_comments,
    wf.server_name,
    wf.subject_area,
    '' as hierarchy_structure,
    '' as path
FROM REP_WORKFLOWS wf
    JOIN rep_task_inst ti on ti.workflow_id = wf.workflow_id and ti.task_type = 68
where wf.subject_area not in ('<SUBJECT_AREAS_TO_EXCLUDE>')
) ws
    join (select distinct workflow_id as workflow_id from rep_wflow_run) active_wflows on ws.workflow_id = active_wflows.workflow_id