By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE /*sfmfg.*/sfwid_order_desc (
program VARCHAR2(20),
order_id VARCHAR2(20),
part_no VARCHAR2(20),
order_no VARCHAR2(20),
actual_start_date DATE
);
CREATE TABLE /*sfmfg.*/sfwid_serial_desc (
order_id VARCHAR2(20),
serial_no VARCHAR2(20)
);
CREATE TABLE /*sfmfg.*/sfwid_oper_desc (
order_id VARCHAR2(20),
oper_no VARCHAR2(20),
asgnd_machine_id VARCHAR2(20),
time_stamp TIMESTAMP,
updt_userid VARCHAR2(20),
oper_status VARCHAR2(20),
step_key VARCHAR2(20)
);
SELECT ordr.program,
ordr.order_id,
ordr.part_no,
ordr.order_no,
ordr.actual_start_date,
ser.serial_no,
oper.oper_no,
-- pivot
oper.asgnd_machine_id,
oper.time_stamp,
oper.updt_userid,
oper.oper_status,
CASE
WHEN oper.oper_no IN ('1234')
THEN 'paint_bike'
END AS oper_type
FROM /*sfmfg.*/sfwid_order_desc ordr
LEFT JOIN /*sfmfg.*/sfwid_serial_desc ser
ON ordr.order_id = ser.order_id
LEFT JOIN /*sfmfg.*/sfwid_oper_desc oper
ON ordr.order_id = oper.order_id
AND oper.step_key = -1
AND ordr.program IN ('bike')
AND oper.oper_no IN ('1234')
WHERE ordr.actual_start_date > TO_DATE('08/01/2023', 'MM/DD/YYYY')
AND ordr.part_no LIKE '123'
AND ser.serial_no LIKE '123'
SELECT a1.program,
a1.order_id,
-- a1.customer_description,
-- a1.stiffener_type,
-- a1.mold_tool_no,
--a1.plan_title,
a1.part_no,
a1.order_no,
a1.actual_start_date,
a1.serial_no,
a1.bike_cutting_asgnd_machine_id,
a1.bike_cutting_time_stamp,
a1.bike_cutting_updt_userid,
a1.bike_cutting_oper_status,
a1.bike_prep_asgnd_machine_id,
a1.bike_prep_time_stamp,
a1.bike_prep_updt_userid,
a1.bike_prep_oper_status
FROM (
SELECT ordr.program,
ordr.order_id,
ordr.part_no,
ordr.order_no,
ordr.actual_start_date,
ser.serial_no,
oper.oper_no,
-- pivot
oper.asgnd_machine_id,
oper.time_stamp,
oper.updt_userid,
oper.oper_status,
CASE
WHEN oper.oper_no IN ('1234') THEN 'paint_bike'
WHEN oper.oper_no IN ('5678') THEN 'bike_Cutting'
WHEN oper.oper_no IN ('9999') THEN 'bike_Prep'
END AS oper_type