SELECT
    itd.test_program_name,
    itd.test_program_version,
    itd.probecard_setup,
    itd.flat_orientation,
    itd.handler_driver,
    handler_driver,
    itd.rtwm_template,
    itd.probecard_orientation,
    itd.test_specification_name,
    itd.test_condition,
    itd.test_software_type,
    itd.test_specification_version,
    itd.project,
    job_resource.resource_code,
    CASE
            WHEN job_resource.resource_code LIKE 'P-%' THEN 'Prober Infromation'
            WHEN job_resource.resource_code LIKE 'T-%' THEN 'Tester Infromation'
            ELSE job_resource.resource_code
        END
    resource_type,
    CASE SUBSTR(job_resource.test_cond_num,-1)
            WHEN '1'    THEN itd.probe_setup_file_1
            WHEN '2 '   THEN itd.probe_setup_file_2
            WHEN '3'    THEN itd.probe_setup_file_3
            WHEN '4 '   THEN itd.probe_setup_file_4
            WHEN '5'    THEN itd.probe_setup_file_5
            WHEN '6 '   THEN itd.probe_setup_file_6
            WHEN '7'    THEN itd.probe_setup_file_7
            WHEN '8'    THEN itd.probe_setup_file_8
            WHEN '9'    THEN itd.probe_setup_file_9
        END
    setup_file,
    CASE SUBSTR(job_resource.test_cond_num,-1)
            WHEN '1'    THEN itd.pr_temp_1
            WHEN '2'    THEN itd.pr_temp_2
            WHEN '3 '   THEN itd.pr_temp_3
            WHEN '4 '   THEN itd.pr_temp_4
            WHEN '5 '   THEN itd.pr_temp_5
            WHEN '6'    THEN itd.pr_temp_6
            WHEN '7'    THEN itd.pr_temp_7
            WHEN '8'    THEN itd.pr_temp_8
            WHEN '9'    THEN itd.pr_temp_9
        END
    temp,
    pics.mime_type,
    pics.picture
FROM
    xxmlx_item_testprog_details_v itd,
    xxmlx_item_top_bottom_pics pics,
    (
        SELECT
            msi1.inventory_item_id,
            msi1.organization_id,
            msi1.segment1,
            res1.resource_code,
            we.wip_entity_name,
            bop.operation_code,
            wo.operation_seq_num,
            wo.description,
            'Test Condition '
            || ROW_NUMBER() OVER(
                PARTITION BY msi1.segment1,
                we.wip_entity_name,
                bop.operation_code
                ORDER BY
                    wo.operation_seq_num
            ) AS test_cond_num
        FROM
            apps.mtl_system_items msi1,
            apps.wip_entities we,
            apps.wip_operations wo,
            apps.wip_discrete_jobs wdj1,
            apps.bom_standard_operations bop,
            wip_operation_resources wor,
            bom_resources res1
        WHERE
            msi1.inventory_item_id = we.primary_item_id
            AND   msi1.organization_id = we.organization_id
            AND   we.wip_entity_id = wo.wip_entity_id
            AND   we.organization_id = wo.organization_id
            AND   we.wip_entity_id = wdj1.wip_entity_id
            AND   we.organization_id = wdj1.organization_id
            AND   bop.standard_operation_id = wo.standard_operation_id
            AND   wo.wip_entity_id = wdj1.wip_entity_id
            AND   wo.organization_id = wdj1.organization_id
            AND   wor.wip_entity_id = wdj1.wip_entity_id
            AND   wor.operation_seq_num = wo.operation_seq_num
            AND   res1.resource_id = wor.resource_id
            AND   res1.attribute4 IN (
                'PROBER',
                'TESTER'
            )
            AND   bop.operation_code = 'B000'
    ) job_resource
WHERE
    itd.test_specification_name = job_resource.test_cond_num
    AND   itd.organization_id = 114
    AND   itd.item_id = job_resource.inventory_item_id
    AND   itd.organization_id = job_resource.organization_id
    AND   job_resource.wip_entity_name = 'dezTestR'
    AND   pics.file_name = itd.probecard_setup;