create table table_1
(cust_no varchar2(11) not null,
 cust_first_name  varchar2(40),
 cust_last_name   varchar2(40),
 cust_aid_catg_cd varchar2(10),
 beg_elig_dt    date not null,
 end_elig_dt   date);

create table table_2
(
person_no                 varchar2(11) not null,
ELIG_CD                   varchar2(10),
VALID_FR_DT               DATE,
VALID_TO_DT               DATE );

 

insert into table_1
(cust_no, cust_first_name, cust_last_name   , cust_aid_catg_cd, beg_elig_dt, end_elig_dt)
select '0000000001','Robert','Johnson','52',to_date('09/01/2002','mm/dd/yyyy'),to_date('04/30/2011','mm/dd/yyyy')  from dual;

insert into table_1
(cust_no, cust_first_name, cust_last_name   , cust_aid_catg_cd , beg_elig_dt, end_elig_dt)
select '0000000001','Robert','Johnson','26',to_date('05/01/2011','mm/dd/yyyy'),to_date('04/30/2014','mm/dd/yyyy')  from dual;  


insert into table_1
(cust_no, cst_first_name, cust_last_name   , cust_aid_catg_cd, beg_elig_dt, end_elig_dt)
select '0000000001','Robert','Johnson','52',to_date('05/01/2014','mm/dd/yyyy'),to_date('03/31/2015','mm/dd/yyyy') from dual; 

insert into table_2 (person_no, elig_cd, VALID_FR_DT   , VALID_TO_DT)
select '0000000001','99',to_date('02/07/2008','mm/dd/yyyy'),to_date('03/05/2008','mm/dd/yyyy') from dual;

insert into table_2(person_no, elig_cd, VALID_FR_DT   , VALID_TO_DT)
select '0000000001','95',to_date('05/28/2011','mm/dd/yyyy'),to_date('11/19/2011','mm/dd/yyyy') from dual;

commit;


Objective:

Using information from Table_1 and Table_2 for a given Cust_NO value I am trying to report on distinct time periods in which the Cust_No has either values for table_2.ELIG_CD or table_1.cust_aid_catg_cd  The CATG column in the resultset is populated
from the Table_1.cust_aid_catg_cd and table_2.ELIG_CD columns.

Approach taken so far

Select from Table_1 and perform an Outer_Join on Table_2 using the Cust_no column as the joining column  

Notes:

There is a possibility that the VALID_FR_DT and VALID_TO_DT column values in Table_2 overlap for a given cust_no value.  
The overall boundaries of the BEG_ELIG_DT, END_ELIG_DT columns in the resultset
are set by the values of the BEG_ELIG_DT and END_ELIG_DT column values of Table_1.




For the sample data the expected resultset should be:

cust_no		cst_fisrt_name  	cust_last_name	Beg_ELIG_DT		End_ELIG_DT	CATG		
000000001	Robert			Johson		9/1/2002		2/6/2008	52
000000001	Robert			Johson		2/7/2008		3/5/2008	99
000000001	Robert			Johson		3/6/2008		4/30/2011	52
000000001	Robert			Johson		5/1/2011		5/27/2011	26
000000001	Robert			Johson		5/28/2011		11/19/2011	95
000000001	Robert			Johson		11/20/2011		4/30/2019	26
000000001	Robert			Johson		5/1/2014		3/31/2015	52
 

What I have so far:



With Join_Query_1 as (
select a.cust_no,a.cust_first_name,a.cust_last_name,a.cust_aid_catg_cd,a.beg_elig_dt,a.end_elig_dt,b.elig_cd,b.valid_fr_dt,b.valid_to_dt
from table_1 a,table_2 b
where a.cust_no = b.person_no(+) 
and    a.End_Elig_Dt >= b.valid_fr_dt(+) and
       a.BEG_Elig_Dt <=   nvl(b.valid_to_dt(+),to_date('12/31/9999','mm/dd/yyyy'))
       )
select * from Join_Query_1;

/*
CUST_NO     CUST_FIRST_NAME                          CUST_LAST_NAME                           CUST_AID_CATG_CD BEG_ELIG_DT          END_ELIG_DT          ELIG_CD    VALID_FR_DT          VALID_TO_DT        
----------- ---------------------------------------- ---------------------------------------- ---------------- -------------------- -------------------- ---------- -------------------- --------------------
0000000001  Robert                                   Johnson                                  52               01-SEP-2002 00:00:00 30-APR-2011 00:00:00 99          07-FEB-2008 00:00:00 05-MAR-2008 00:00:00 
0000000001  Robert                                   Johnson                                  26               01-MAY-2011 00:00:00 30-APR-2014 00:00:00 95          28-MAY-2011 00:00:00 19-NOV-2011 00:00:00 
0000000001  Robert                                   Johnson                                  52               01-MAY-2014 00:00:00 31-MAR-2015 00:00:00                                                      

*/

I need to further break it down by individual time periods and possibly use unipivot to generate the rows with the further broken down time periods.