Notes

SELECT DISTINCT
dcr.route_name,
dcr.route_step_name,
dcr.op_name,
dcr.user_name,
dcr.object_name AS serial_number,
u.part_number,
u.part_revision,
u.serial_number AS unit_serial_number,
dcr.object_type,
dcr.plan_key,
dcr.line_number,
dcp.input_prompt,
dcr.data_string AS input_value,
TO_CHAR(u.creation_time, 'dd-MON-yyyy hh24:mi:ss') AS creation_time,
TO_CHAR(dcr.complete_time, 'dd-MON-yyyy hh24:mi:ss') AS complete_time,
ud.udi_gs1_s AS udi,
FIRST_VALUE(ERM.defect_id) OVER (PARTITION BY u.serial_number ORDER BY ERM.defect_id) AS defect_id,
ERM.current_edms_status,
ERC.resource_condition_name,
EPS.hierarchy1_field,
ERC.local_hierarcy_selection_1_L0,
ERC.local_hierarcy_selection_1_L1,
ERC.local_hierarcy_selection_1_L2,
ERC.long_note
FROM
dc_gehc_dcrruntimelinedata dcr
INNER JOIN
dc_gehc_dcplanline dcp ON dcr.line_number = dcp.line_number AND dcp.plan_key = dcr.plan_key
INNER JOIN
unit u ON dcr.object_key = u.unit_key
INNER JOIN
uda_unit ud ON ud.object_key = u.unit_key
INNER JOIN
dc_edms_runtimemaster ERM ON ERM.dhr_id = u.serial_number
INNER JOIN
dc_edms_runtimecycle ERC ON ERM.dc_instance_key = ERC.defect_dci_key
INNER JOIN
dc_edms_planstep EPS ON (ERC.local_plan_key = EPS.plan_step_key OR
ERC.global_plan_key = EPS.plan_step_key OR
ERC.product_plan_key = EPS.plan_step_key)
WHERE
dcr.op_name = 'CMD_ALPHA_FAR'
AND ERC.resource_condition_name = 'Disposition'
ORDER BY
dcr.op_name,
u.serial_number,
dcr.line_number;