Hi All,
I have tables like below
1. Order table
2. Order line table
3. Reservation Detail table
4. Customer table
5. Onhand details table
Logic for qty is like SUM of onhand - sum of reserved quantity in one row for all WH#
Output desired:
Order# Line# Item# WH01 WH02 WH03
1 1 Itm01 5 36 0
WITH o (org#, order#, customer#, WH# ) AS (
SELECT 1, 1, 'c01', 'wh01' FROM DUAL
UNION
SELECT 1, 2, 'c02', 'wh02' FROM DUAL
UNION
SELECT 1, 3, 'c03', 'wh03' FROM DUAL)
, l (order#, line#, SWH#, Item# ) AS (
SELECT 1, 1, 'wh01', 'Itm01' FROM DUAL
UNION
SELECT 2, 1, 'wh01', 'Itm02' FROM DUAL
UNION
SELECT 3, 1, 'wh01', 'Itm03' FROM DUAL)
SELECT o.customer#, l.order#, l.line#, l.Item#, o.WH#, l.SWH# FROM o, l
WHERE o.order# = l.order#;
WITH rq (Item#, WH#, Qty#, Date#) AS
(
SELECT 'Itm01', 'wh01', 2, SYSDATE FROM DUAL
UNION
SELECT 'Itm01', 'wh01', 3, SYSDATE-1 FROM DUAL
UNION
SELECT 'Itm02', 'wh01', 2, SYSDATE FROM DUAL
UNION
SELECT 'Itm02', 'wh01', 10, SYSDATE FROM DUAL
UNION
SELECT 'Itm02', 'wh01', 2, SYSDATE FROM DUAL
)
SELECT * FROM rq;
WITH c (customer#, Chain#, Details) AS
(SELECT 'c01', 'cn01', 'wh01, '|| 'wh02, ' || 'wh03' FROM DUAL
UNION
SELECT 'c02', 'cn02', 'wh01, '|| 'wh02, ' || 'wh03' FROM DUAL
UNION
SELECT 'c03', 'cn03', 'wh01, '|| 'wh02, ' || 'wh03' FROM DUAL)
SELECT * FROM c;
WITH oq (Item#, WH#, Qty#, Date#) AS
(
SELECT 'Itm01', 'wh01', 10, SYSDATE FROM DUAL
UNION
SELECT 'Itm01', 'wh02', 10, SYSDATE-1 FROM DUAL
UNION
SELECT 'Itm02', 'wh01', 10, SYSDATE FROM DUAL
UNION
SELECT 'Itm02', 'wh02', 20, SYSDATE FROM DUAL
UNION
SELECT 'Itm02', 'wh03', 20, SYSDATE FROM DUAL
)
SELECT * FROM oq;