create or replace view ke014t3.BCD_ICT_SLS_RFND_RCT_SUMMARY as
(
select 
*
from
 (
select * from (
select 'HO' Branch,'SALES' TRN_MODE, Year, Month, art_code,(select ar_name from id_ar_master where ar_code=art_code and rownum=1) AR_Name,
(select ar_status from id_ar_master where ar_code=art_code and rownum=1) AR_Status, (select ar_analysis_6 from id_ar_master where ar_code=art_code and rownum=1) AR_Group,
sum(Sales) TOT_SALES
from 
(
select
extract(year from art_doc_date) Year,
upper(to_char(art_doc_date,'Mon')) Month,
art_code,
(case
when CCM_DESCRIPTION='TIN' then art_local_amount
end) Sales
from ke014t3.id_ar_trans,ke014t3.id_common_code_master
where
ccm_type='ARDOCMAP'
and art_type=ccm_code

)
group by year, month,art_code
having sum(sales)<>0
order by art_code,year,month)
pivot (
sum(tot_sales)
for month
in ('JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC')
)
union all
select * from (select 'HO' Branch, 'REFUNDS' TRN_MODE,Year, Month, art_code,(select ar_name from id_ar_master where ar_code=art_code and rownum=1) AR_Name,
(select ar_status from id_ar_master where ar_code=art_code and rownum=1) AR_Status, (select ar_analysis_6 from id_ar_master where ar_code=art_code and rownum=1) AR_Group,
sum(Refunds) TOT_REFUNDS
from 
(
select
extract(year from art_doc_date) Year,
upper(to_char(art_doc_date,'Mon')) Month,
art_code,
(case
when CCM_DESCRIPTION='TCN' then art_local_amount
end) Refunds
from ke014t3.id_ar_trans,ke014t3.id_common_code_master
where
ccm_type='ARDOCMAP'
and art_type=ccm_code
)
group by year, month,art_code
having sum(Refunds)<>0
order by art_code,year,month)
pivot (
sum(tot_Refunds)
for month
in ('JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC')
)
union all
select * from (
select 'HO' Branch,'RECEIPTS' TRN_MODE, Year, Month, art_code,(select ar_name from id_ar_master where ar_code=art_code and rownum=1) AR_Name,
(select ar_status from id_ar_master where ar_code=art_code and rownum=1) AR_Status, (select ar_analysis_6 from id_ar_master where ar_code=art_code and rownum=1) AR_Group,
sum(receipts) TOT_RECEIPTS
from 
(
select
extract(year from art_doc_date) Year,
upper(to_char(art_doc_date,'Mon')) Month,
art_code,
(case
when CCM_DESCRIPTION='RCT' then art_local_amount
end) ReceiptS
from ke014t3.id_ar_trans,ke014t3.id_common_code_master
where
ccm_type='ARDOCMAP'
and art_type=ccm_code
)
group by year, month,art_code
having sum(RECEIPTS)<>0
order by art_code,year,month)
pivot (
sum(TOT_RECEIPTS)
for month
in ('JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC')
)
)
)
-- -----------------------------------------------------------------UN Branch ------------------------------------------------------
union all
(
select 
*
from
 (
select * from (
select 'UN' Branch,'SALES' TRN_MODE, Year, Month, art_code,(select ar_name from ke019t3.id_ar_master where ar_code=art_code and rownum=1) AR_Name,
(select ar_status from ke019t3.id_ar_master where ar_code=art_code and rownum=1) AR_Status, (select ar_analysis_6 from ke019t3.id_ar_master where ar_code=art_code and rownum=1) AR_Group,
sum(Sales) TOT_SALES
from 
(
select
extract(year from art_doc_date) Year,
upper(to_char(art_doc_date,'Mon')) Month,
art_code,
(case
when CCM_DESCRIPTION='TIN' then art_local_amount
end) Sales
from ke019t3.id_ar_trans,ke019t3.id_common_code_master
where
ccm_type='ARDOCMAP'
and art_type=ccm_code

)
group by year, month,art_code
having sum(sales)<>0
order by art_code,year,month)
pivot (
sum(tot_sales)
for month
in ('JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC')
)
union all
select * from (select 'UN' Branch, 'REFUNDS' TRN_MODE,Year, Month, art_code,(select ar_name from ke019t3.id_ar_master where ar_code=art_code and rownum=1) AR_Name,
(select ar_status from ke019t3.id_ar_master where ar_code=art_code and rownum=1) AR_Status, (select ar_analysis_6 from ke019t3.id_ar_master where ar_code=art_code and rownum=1) AR_Group,
sum(Refunds) TOT_REFUNDS
from 
(
select
extract(year from art_doc_date) Year,
upper(to_char(art_doc_date,'Mon')) Month,
art_code,
(case
when CCM_DESCRIPTION='TCN' then art_local_amount
end) Refunds
from ke019t3.id_ar_trans,ke019t3.id_common_code_master
where
ccm_type='ARDOCMAP'
and art_type=ccm_code
)
group by year, month,art_code
having sum(Refunds)<>0
order by art_code,year,month)
pivot (
sum(tot_Refunds)
for month
in ('JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC')
)
union all
select * from (
select 'UN' Branch,'RECEIPTS' TRN_MODE, Year, Month, art_code,(select ar_name from ke019t3.id_ar_master where ar_code=art_code and rownum=1) AR_Name,
(select ar_status from ke019t3.id_ar_master where ar_code=art_code and rownum=1) AR_Status, (select ar_analysis_6 from ke019t3.id_ar_master where ar_code=art_code and rownum=1) AR_Group,
sum(receipts) TOT_RECEIPTS
from 
(
select
extract(year from art_doc_date) Year,
upper(to_char(art_doc_date,'Mon')) Month,
art_code,
(case
when CCM_DESCRIPTION='RCT' then art_local_amount
end) ReceiptS
from ke019t3.id_ar_trans,ke019t3.id_common_code_master
where
ccm_type='ARDOCMAP'
and art_type=ccm_code
)
group by year, month,art_code
having sum(RECEIPTS)<>0
order by art_code,year,month)
pivot (
sum(TOT_RECEIPTS)
for month
in ('JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC')
)
)
)
--- Safaricom Branch ----------------------------------------------------------------------------------------------------------------
union all
(
select 
*
from
 (
select * from (
select 'SC' Branch,'SALES' TRN_MODE, Year, Month, art_code,(select ar_name from ke074t3.id_ar_master where ar_code=art_code and rownum=1) AR_Name,
(select ar_status from ke074t3.id_ar_master where ar_code=art_code and rownum=1) AR_Status, (select ar_analysis_6 from ke074t3.id_ar_master where ar_code=art_code and rownum=1) AR_Group,
sum(Sales) TOT_SALES
from 
(
select
extract(year from art_doc_date) Year,
upper(to_char(art_doc_date,'Mon')) Month,
art_code,
(case
when CCM_DESCRIPTION='TIN' then art_local_amount
end) Sales
from ke074t3.id_ar_trans,ke074t3.id_common_code_master
where
ccm_type='ARDOCMAP'
and art_type=ccm_code

)
group by year, month,art_code
having sum(sales)<>0
order by art_code,year,month)
pivot (
sum(tot_sales)
for month
in ('JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC')
)
union all
select * from (select 'SC' Branch, 'REFUNDS' TRN_MODE,Year, Month, art_code,(select ar_name from ke074t3.id_ar_master where ar_code=art_code and rownum=1) AR_Name,
(select ar_status from ke074t3.id_ar_master where ar_code=art_code and rownum=1) AR_Status, (select ar_analysis_6 from ke074t3.id_ar_master where ar_code=art_code and rownum=1) AR_Group,
sum(Refunds) TOT_REFUNDS
from 
(
select
extract(year from art_doc_date) Year,
upper(to_char(art_doc_date,'Mon')) Month,
art_code,
(case
when CCM_DESCRIPTION='TCN' then art_local_amount
end) Refunds
from ke074t3.id_ar_trans,ke074t3.id_common_code_master
where
ccm_type='ARDOCMAP'
and art_type=ccm_code
)
group by year, month,art_code
having sum(Refunds)<>0
order by art_code,year,month)
pivot (
sum(tot_Refunds)
for month
in ('JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC')
)
union all
select * from (
select 'SC' Branch,'RECEIPTS' TRN_MODE, Year, Month, art_code,(select ar_name from ke074t3.id_ar_master where ar_code=art_code and rownum=1) AR_Name,
(select ar_status from ke074t3.id_ar_master where ar_code=art_code and rownum=1) AR_Status, (select ar_analysis_6 from ke074t3.id_ar_master where ar_code=art_code and rownum=1) AR_Group,
sum(receipts) TOT_RECEIPTS
from 
(
select
extract(year from art_doc_date) Year,
upper(to_char(art_doc_date,'Mon')) Month,
art_code,
(case
when CCM_DESCRIPTION='RCT' then art_local_amount
end) ReceiptS
from ke074t3.id_ar_trans,ke074t3.id_common_code_master
where
ccm_type='ARDOCMAP'
and art_type=ccm_code
)
group by year, month,art_code
having sum(RECEIPTS)<>0
order by art_code,year,month)
pivot (
sum(TOT_RECEIPTS)
for month
in ('JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC')
)
)
)
