Oracle 18c

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

create table test_tab1(
seq_id NUMBER(10),
e_id NUMBER(10),
jira_key VARCHAR2(20),
stage_code NUMBER(10)
);
create table test_tab2(
seq_id NUMBER(10),
e_id NUMBER(10),
jira_key VARCHAR2(20),
stage_code NUMBER(10)
);
BEGIN
INSERT INTO test_tab1 VALUES(1,11,'JIRA_A',2);
INSERT INTO test_tab1 VALUES(2,12,'JIRA_B',3);
END;
/
1 rows affected

WITH  got_new_code (e_id, jira_key, new_code, code) AS (
  SELECT m.e_id,m.jira_key
  ,      m.stage_code
  ,      c.code
  FROM  test_tab1 m
        CROSS APPLY (
          SELECT LEVEL - 1 AS code
          FROM   dual
          CONNECT BY LEVEL <= m.stage_code + 1
        ) c
   WHERE m.stage_code IS NOT NULL
   ORDER BY m.e_id,m.stage_code
)
SELECT *
FROM   got_new_code n
       FULL JOIN test_tab2 t
       ON (   t.e_id       = n.e_id
          AND t.jira_key   = n.jira_key
          AND t.stage_code = n.new_code );


E_ID
JIRA_KEY
NEW_CODE
CODE
SEQ_ID
E_ID
JIRA_KEY
STAGE_CODE


11
JIRA_A
2
2
null
null
null
null

11
JIRA_A
2
1
null
null
null
null

11
JIRA_A
2
0
null
null
null
null

12
JIRA_B
3
2
null
null
null
null

12
JIRA_B
3
1
null
null
null
null

12
JIRA_B
3
3
null
null
null
null

12
JIRA_B
3
0
null
null
null
null


WITH  got_new_code (e_id, jira_key, stage_code, code) AS (
  SELECT m.e_id,m.jira_key
  ,      m.stage_code
  ,      c.code
  FROM  test_tab1 m
        CROSS APPLY (
          SELECT LEVEL - 1 AS code
          FROM   dual
          CONNECT BY LEVEL <= m.stage_code + 1
        ) c
   WHERE m.stage_code IS NOT NULL
   ORDER BY m.e_id,m.stage_code
)
SELECT *
FROM   got_new_code n
       FULL JOIN test_tab2 t USING (e_id,jira_key,stage_code);


E_ID
JIRA_KEY
STAGE_CODE
CODE
SEQ_ID


11
JIRA_A
2
2
null

11
JIRA_A
2
1
null

11
JIRA_A
2
0
null

12
JIRA_B
3
2
null

12
JIRA_B
3
1
null

12
JIRA_B
3
3
null

12
JIRA_B
3
0
null

E_ID	JIRA_KEY	NEW_CODE	CODE	SEQ_ID	E_ID	JIRA_KEY	STAGE_CODE
11	JIRA_A	2	2	null	null	null	null
11	JIRA_A	2	1	null	null	null	null
11	JIRA_A	2	0	null	null	null	null
12	JIRA_B	3	2	null	null	null	null
12	JIRA_B	3	1	null	null	null	null
12	JIRA_B	3	3	null	null	null	null
12	JIRA_B	3	0	null	null	null	null