select art_code,
(select ar_name from id_ar_master where ar_code=art_code and rownum=1) AR_Name,
(select ARG_DESCRIPTION from id_ar_group_link where art_code=arg_code and rownum=1) Cust_Group,
Decode((select AR_STATUS from id_ar_master where ar_code=art_code and rownum=1),'N','ACTIVE','B','Blacklisted','R','Receipt Only') AR_Status,
Replace(replace(replace(replace(replace(replace((select AR_TELEPHONE from id_ar_master where ar_code=art_code and rownum=1),',','-'),'/','-'),chr(9),'-'),chr(10),'-'),chr(13),'-') ,'.','-') AR_Telephone,
Replace(replace(replace(replace(replace(replace((select AR_CONTACT_PERSON from id_ar_master where ar_code=art_code and rownum=1),',','-'),'/','-'),chr(9),'-'),chr(10),'-'),chr(13),'-') ,'.','-') AR_Contact,
(select AR_FINAL_LIMIT from id_ar_master where ar_code=art_code and rownum=1) AR_Limit,
(select AR_CREDIT_DAYS from id_ar_master where ar_code=art_code and rownum=1) AR_CR_Days,
Replace(replace(replace(replace(replace(replace((select AR_EMAIL from id_ar_master where ar_code=art_code and rownum=1),',','-'),'/','-'),chr(9),'-'),chr(10),'-'),chr(13),'-') ,'.','-') Email_Address,
(select AR_CREATED_ON from id_ar_master where ar_code=art_code and rownum=1) AR_CREATED_ON,
(select AR_CREATED_BY from id_ar_master where ar_code=art_code and rownum=1) AR_CREATED_BY,
(select AR_MODIFIED_ON from id_ar_master where ar_code=art_code and rownum=1) AR_MODIFIED_ON,
(select AR_MODIFIED_BY from id_ar_master where ar_code=art_code and rownum=1) AR_MODIFIED_BY,
(select max(art_doc_date) from id_ar_trans_view where artv_code=art_code and rownum=1) Last_trans_date,
round(sum(op_dr),2) Opening_DR,
round(sum(op_cr),2) Opening_CR,
round(sum(this_year_dr),2)This_Year_DR,
round(sum(this_year_cr),2) This_Year_CR,
--
round(sum(Sales_Inv),2) SALES_INV,
round(sum(Refunds),2) REFUNDS,
round(sum(Receipts),2) RECEIPTS,
round(sum(WHT),2) WHT,
round(sum(JVS),2) JVS,
round(sum(OTHER),2) OTHER
--
from
(
select * from 
(
(select art_code,sum(dr) OP_DR, sum(CR) OP_CR ,(sum(dr)-sum(dr)) THIS_YEAR_DR, (sum(dr)-sum(dr)) THIS_YEAR_CR,
0 Sales_Inv,
0 Refunds,
0 Receipts, 
0 WHT,
0 JVS,
0 OTHER
from  --  
(select art_code, 
(case
when art_local_amount<0 then art_local_amount
end) CR,
(case
when art_local_amount>0 then art_local_amount
end) DR,
0 THIS_YEAR_dr, 0 THIS_YEAR_cr,
0 Sales_Inv,
0 Refunds,
0 Receipts,
0 WHT,
0 JVS,
0 OTHER
from id_ar_trans
where art_gl_date<'01-JAN-2018'
)
group by art_code
)
union all
(select art_code,sum(dr)-sum(dr) OP_DR, sum(CR)-sum(cr) OP_CR ,(sum(dr)) THIS_YEAR_DR, (sum(cr)) THIS_YEAR_CR,
sum(Sales_Inv) SALES_Inv,
sum(Refunds) Refunds,
sum(Receipts) Receipts,
sum(WHT) WHT,
sum(JVS) JVS,
Sum(OTHER) OTHER
from  -- 
(select art_code, 
(case
when art_local_amount<0 then art_local_amount
end) CR,
(case
when art_local_amount>0 then art_local_amount
end) DR,
-- Change
(case
when art_type in ('TIN','RTIN','RIN','RRIN') then art_local_amount
end) Sales_INV,
(case
when art_type in ('TCN','RTCN') then art_local_amount
end) Refunds,
(case
when art_type in ('BRV','RBRV','CRV','RCRV') then art_local_amount
end) Receipts,
(case
when art_type in ('WHT') then art_local_amount
end) WHT,
(case
when art_type in ('GLJV', 'GLJVA') then art_local_amount
end) JVS,
(case
when art_type Not in ('TIN','RTIN','RIN','RRIN','TCN','RTCN','BRV','RBRV','CRV','RCRV','WHT','GLJV','GLJVA') then art_local_amount
end) OTHER,
-- End
0 THIS_YEAR_dr, 0 THIS_YEAR_cr
from id_ar_trans
where art_gl_date between '01-JAN-2018' and '31-DEC-2018'
)
group by art_code
)
)
)
where (Op_dr+op_cr+this_year_dr+this_year_cr)<>0
group by art_code
order by Cust_Group, art_code

