Oracle 18c

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

create table NEED_TABLE
    as
        (select 'A' NEED_ID, 123 partno, to_date('01/02/2019', 'MM/DD/YYYY') DATE_NEEDED, 4 NEED_QTY from dual
         union all
         select 'B' NEED_ID, 123 partno, to_date('06/02/2019', 'MM/DD/YYYY') DATE_NEEDED, 2 NEED_QTY from dual) ;
2 rows affected

create table FILL_TABLE
    as
        (select 'X' FILL_ID, 123 partno, to_date('01/01/2019', 'MM/DD/YYYY') DATE_AVAILABLE, 2 FILL_QTY from dual
         union all
         select 'Y' FILL_ID, 123 partno, to_date('06/01/2019', 'MM/DD/YYYY') DATE_AVAILABLE, 4 FILL_QTY from dual) ;
2 rows affected

-- NEED: required items, broken down into "atoms" ie smallest available quantities
create or replace view nvw
as
select
  need_id as nid
, partno
, need_qty as nqty
, date_needed as ndate
, single_item
, row_number() over ( order by need_id, partno, date_needed ) row_
from need_table N cross apply (
    select 1 as single_item from dual connect by level <= N.need_qty
) SN
;
-- FILL: available items, broken down into "atoms" ie smallest available quantities
create or replace view fvw
as
select
  fill_id as fid
, partno
, fill_qty as fqty
, date_available as fdate
, single_item
, row_number() over ( order by fill_id, partno, date_available ) row_
from fill_table F cross apply (
    select 1 as single_item from dual connect by level <= F.fill_qty
) SF
;
-- join the views
select *
from (
  select fid, partno, fqty, single_item, row_ from fvw
) FV join (
  select nid, partno, nqty, single_item, row_ from nvw
) NV on FV.row_ = NV.row_ ;


FID
PARTNO
FQTY
SINGLE_ITEM
ROW_
NID
PARTNO
NQTY
SINGLE_ITEM
ROW_


X
123
2
1
1
A
123
4
1
1

X
123
2
1
2
A
123
4
1
2

Y
123
4
1
3
A
123
4
1
3

Y
123
4
1
4
A
123
4
1
4

Y
123
4
1
5
B
123
2
1
5

Y
123
4
1
6
B
123
2
1
6


select  NV.nid, FV.fid, sum( FV.single_item ) real_fill
from (
  select fid, partno, fqty, single_item, row_ from fvw
) FV join (
  select nid, partno, nqty, single_item, row_ from nvw
) NV on FV.row_ = NV.row_
group by rollup ( NV.nid, FV.fid );


NID
FID
REAL_FILL


A
X
2

A
Y
2

A
null
4

B
Y
2

B
null
2

null
null
6


select  NV.nid, NV.ndate, FV.fid, FV.fdate, sum( FV.single_item ) real_fill
from (
  select fid, partno, fqty, fdate, single_item, row_ from fvw
) FV join (
  select nid, partno, nqty, ndate, single_item, row_ from nvw
) NV on FV.row_ = NV.row_
group by rollup ( NV.nid, FV.fid, NV.ndate, FV.fdate );


NID
NDATE
FID
FDATE
REAL_FILL


A
02-JAN-19
X
01-JAN-19
2

A
02-JAN-19
X
null
2

A
null
X
null
2

A
02-JAN-19
Y
01-JUN-19
2

A
02-JAN-19
Y
null
2

A
null
Y
null
2

A
null
null
null
4

B
02-JUN-19
Y
01-JUN-19
2

B
02-JUN-19
Y
null
2

B
null
Y
null
2

B
null
null
null
2

null
null
null
null
6

NID	FID	REAL_FILL
A	X	2
A	Y	2
A	null	4
B	Y	2
B	null	2
null	null	6

NID	NDATE	FID	FDATE	REAL_FILL
A	02-JAN-19	X	01-JAN-19	2
A	02-JAN-19	X	null	2
A	null	X	null	2
A	02-JAN-19	Y	01-JUN-19	2
A	02-JAN-19	Y	null	2
A	null	Y	null	2
A	null	null	null	4
B	02-JUN-19	Y	01-JUN-19	2
B	02-JUN-19	Y	null	2
B	null	Y	null	2
B	null	null	null	2
null	null	null	null	6

FID	PARTNO	FQTY	SINGLE_ITEM	ROW_	NID	PARTNO	NQTY	SINGLE_ITEM	ROW_
X	123	2	1	1	A	123	4	1	1
X	123	2	1	2	A	123	4	1	2
Y	123	4	1	3	A	123	4	1	3
Y	123	4	1	4	A	123	4	1	4
Y	123	4	1	5	B	123	2	1	5
Y	123	4	1	6	B	123	2	1	6

FID	PARTNO	FQTY	SINGLE_ITEM	ROW_	NID	PARTNO	NQTY	SINGLE_ITEM	ROW_
X	123	2	1	1	A	123	4	1	1
X	123	2	1	2	A	123	4	1	2
Y	123	4	1	3	A	123	4	1	3
Y	123	4	1	4	A	123	4	1	4
Y	123	4	1	5	B	123	2	1	5
Y	123	4	1	6	B	123	2	1	6

FID	PARTNO	FQTY	SINGLE_ITEM	ROW_	NID	PARTNO	NQTY	SINGLE_ITEM	ROW_
X	123	2	1	1	A	123	4	1	1
X	123	2	1	2	A	123	4	1	2
Y	123	4	1	3	A	123	4	1	3
Y	123	4	1	4	A	123	4	1	4
Y	123	4	1	5	B	123	2	1	5
Y	123	4	1	6	B	123	2	1	6