--create or replace view bcd_ict_cust_dealt_last_day as
select art_code cust_number, TO_DATE(max(LAST_RCT_DATE),'DD-MON-YY') last_rct_day, TO_DATE(max(LAST_INV_DATE),'DD_MON_YY') last_INV_DAY,
max(last_RCTD_B4) LAST_RCTD_B4, max(last_INVD_B4) LAST_INVD_B4,
(max(last_INVD_B4)-max(last_RCTD_B4)) DATEDIFF
from
(select art_code, 
(case
when art_type in ('BRV') then TO_DATE(max(art_doc_date),'DD-MON-YY')
end) last_RCT_DATE,
(case
when art_type in ('BRV') then (to_date(sysdate,'DD-MON-YY')-TO_DATE(max(art_doc_date),'DD-MON-YY'))
end) last_RCTD_B4,
(case
when art_type in ('TIN') then TO_DATE(max(art_doc_date),'DD-MON-YY')
end) last_INV_DATE,
(case
when art_type in ('TIN') then (to_date(sysdate,'DD-MON-YY')-TO_DATE(max(art_doc_date),'DD-MON-YY'))
end) last_INVD_B4
from id_ar_trans where art_type in ('BRV','TIN')
group by art_code,art_type
order by art_code,art_type)
group by art_code
order by art_code
