Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE EMP (ID NUMBER, SALE_COM NUMBER, UPDATEDATE DATE, SALES_DT DATE)
CREATE TABLE EMP_HIST (EMP_ID NUMBER, FIN_ID  NUMBER, PRD_SEQ  NUMBER, FIELD_VALUE NUMBER, FIN_DATE DATE)
CREATE TABLE EMP_DETAIL (EMP_ID NUMBER, ID NUMBER, FIELD_VALUE NUMBER, SOME_DATE DATE, SOME_COLUMN NUMBER)
INSERT INTO EMP VALUES(4173, 40, DATE '2016-04-09', NULL)
1 rows affected

INSERT INTO EMP VALUES(4173, 40, DATE '2016-09-20', NULL)
1 rows affected

INSERT INTO EMP VALUES(4173, 30, DATE '2016-10-06', NULL)
1 rows affected

INSERT INTO EMP VALUES(4173, 30, DATE '2016-10-14', NULL)
1 rows affected

INSERT INTO EMP VALUES(4073, 50, DATE '2017-12-23', NULL)
1 rows affected

INSERT INTO EMP VALUES(4073, 40, DATE '2018-01-01', NULL)
1 rows affected

INSERT INTO EMP VALUES(4073, 40, DATE '2016-10-14', NULL)
1 rows affected

INSERT INTO EMP_HIST VALUES(2167, 13,4,30, DATE '2014-09-28')
1 rows affected

INSERT INTO EMP_HIST VALUES(2055, 11,3,30, DATE '2015-09-01')
1 rows affected

INSERT INTO EMP_DETAIL VALUES(4173, 2167, 30, DATE '2016-10-06', NULL)
1 rows affected

INSERT INTO EMP_DETAIL VALUES(4073, 2055, 30, DATE '2018-01-01', NULL)
1 rows affected

SELECT * FROM EMP


ID
SALE_COM
UPDATEDATE
SALES_DT


4173
40
09-APR-16
null

4173
40
20-SEP-16
null

4173
30
06-OCT-16
null

4173
30
14-OCT-16
null

4073
50
23-DEC-17
null

4073
40
01-JAN-18
null

4073
40
14-OCT-16
null


SELECT * FROM EMP_HIST


EMP_ID
FIN_ID
PRD_SEQ
FIELD_VALUE
FIN_DATE


2167
13
4
30
28-SEP-14

2055
11
3
30
01-SEP-15


SELECT * FROM EMP_DETAIL


EMP_ID
ID
FIELD_VALUE
SOME_DATE
SOME_COLUMN


4173
2167
30
06-OCT-16
null

4073
2055
30
01-JAN-18
null


SELECT
    E.ID,
    SALE_COM,
    UPDATEDATE,
    FIN_DATE
FROM
    EMP E
    LEFT JOIN EMP_DETAIL ED ON E.ID = ED.EMP_ID
                               AND E.UPDATEDATE = SOME_DATE
    LEFT JOIN EMP_HIST EH ON ED.ID = EH.EMP_ID
                             AND ED.FIELD_VALUE = EH.FIELD_VALUE


ID
SALE_COM
UPDATEDATE
FIN_DATE


4173
30
06-OCT-16
28-SEP-14

4073
40
01-JAN-18
01-SEP-15

4173
40
20-SEP-16
null

4073
40
14-OCT-16
null

4073
50
23-DEC-17
null

4173
40
09-APR-16
null

4173
30
14-OCT-16
null

ID	SALE_COM	UPDATEDATE	SALES_DT
4173	40	09-APR-16	null
4173	40	20-SEP-16	null
4173	30	06-OCT-16	null
4173	30	14-OCT-16	null
4073	50	23-DEC-17	null
4073	40	01-JAN-18	null
4073	40	14-OCT-16	null

ID	SALE_COM	UPDATEDATE	FIN_DATE
4173	30	06-OCT-16	28-SEP-14
4073	40	01-JAN-18	01-SEP-15
4173	40	20-SEP-16	null
4073	40	14-OCT-16	null
4073	50	23-DEC-17	null
4173	40	09-APR-16	null
4173	30	14-OCT-16	null