SELECT
 --   RESOURCES.condition_number,
    itd.test_program_name,
    itd.test_program_version,
    itd.probecard_setup,
    itd.flat_orientation,
    itd.handler_driver,
    itd.rtwm_template,
    itd.probecard_orientation,
        --
    itd.test_condition,
    itd.test_software_type,
    itd.test_specification_name,
    itd.test_specification_version,
    itd.project,
   -- resources.resource_type resource_type,
                CASE  1--RESOURCES.condition_number
                    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  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,
                 CASE
                    WHEN res.resource_code LIKE 'P-%' THEN 'Prober Infromation'
                    WHEN res.resource_code LIKE 'T-%' THEN 'Tester Infromation'
                    ELSE res.resource_code
                END resource_type
FROM
    apps.xxmlx_item_testprog_details_v itd,
     apps.bom_operational_routings brout,
            apps.bom_operation_resources bor,
            apps.bom_operation_sequences bos,
            apps.bom_standard_operations ops,
            apps.bom_resources res

WHERE
    itd.item_id = 31
    AND   itd.organization_id = 114
     AND   brout.assembly_item_id = itd.item_id
            AND   bos.routing_sequence_id = brout.routing_sequence_id
            AND   ops.standard_operation_id = bos.standard_operation_id
            AND   bor.operation_sequence_id = bos.operation_sequence_id
            AND   res.resource_id = bor.resource_id
            AND   res.attribute4 IN (
                'PROBER',
                'TESTER'
            );