select art_code, ar_name,
sum(sales) sales from
(select art_code,
(select ar_name from ke014t3_support.COMBINED_AR_MASTER where branch='HO' and ar_code=art_code and rownum=1) AR_name,
sum((ART_FOREIGN_AMOUNT*ART_EX_RATE)) sales from ke014t3.id_ar_trans
where ART_TYPE in ('TIN','TINS','TCN','TCNS','RIN','RRIN','RINS','RRINS', 'RTIN','RTINS','RTCN','RTCNS')
and art_year='2019'
group by art_code, (select ar_name from ke014t3_support.COMBINED_AR_MASTER where branch='HO' and ar_code=art_code and rownum=1)
union all
select art_code,
(select ar_name from ke014t3_support.COMBINED_AR_MASTER where branch='SC' and ar_code=art_code and rownum=1) AR_name,
sum((ART_FOREIGN_AMOUNT*ART_EX_RATE)) sales from ke074t3.id_ar_trans
where ART_TYPE in ('TIN','TINS','TCN','TCNS','RIN','RRIN','RINS','RRINS', 'RTIN','RTINS','RTCN','RTCNS')
and art_year='2019'
group by art_code, (select ar_name from ke014t3_support.COMBINED_AR_MASTER where branch='SC' and ar_code=art_code and rownum=1)
union all
select art_code,
(select ar_name from ke014t3_support.COMBINED_AR_MASTER where branch='UN' and ar_code=art_code and rownum=1) AR_name,
sum((ART_FOREIGN_AMOUNT*ART_EX_RATE)) sales from ke019t3.id_ar_trans
where ART_TYPE in ('TIN','TINS','TCN','TCNS','RIN','RRIN','RINS','RRINS', 'RTIN','RTINS','RTCN','RTCNS')
and art_year='2019'
group by art_code, (select ar_name from ke014t3_support.COMBINED_AR_MASTER where branch='UN' and ar_code=art_code and rownum=1))
group by art_code, ar_name
order by sales desc,art_code


--select * from ke014t3_support.COMBINED_AR_MASTER where branch not in ('UN','HO')
