Hi Team,
I am facing challenge while to avoid negative amount at header level and line level. Below is the sample data
Record header/Line Invoice Number Header Amount / Line Amount
H CCI-0707 -100
L -100
H CCI-0808 50
L 25
L 25
H CCI-0909 80
L 100
L -20
To avoid negative amount i have design the sql query as per below mentioned.
select DECODE(LEVELS,'H',header_amount,'L',line_amount,null) as Header Amount/Line Amount
from
(Select 'H' Levels, sum(header_amount),vendor_id,market,vendor_id ref_vendor_id,market ref_market
from a
group by vendor_id,market
having sum(header_amount) > 0
Union
select 'L' Levels,sum(a.amount),a.code_combination
from a, b
where a.invoice_id = b.invoice_id
group by code_combination
having sum(amount) > 0)
ORDER BY ref_market,ref_vendor_id, DECODE(LEVELS,'H',1,'L',2).
but i am getting output like below
Record header/Line Invoice Number Header Amount / Line Amount
H CCI-0808 50
L 25
L 25
H CCI-0909 80
L 100
But i want output like below mentioned
Record header/Line Invoice Number Header Amount / Line Amount
H CCI-0808 50
L 25
L 25
H CCI-0909 80
L 100
L -20
Thanks in Advance