


CREATE OR REPLACE VIEW ke014t3.bcd_ict_ar_ageing_of_os_staff
AS
select
art_code,
substr(trim(ar_name),0,1) CUST_START_LETTER,
trim(AR_NAME) AR_NAME, CUST_STATUS,AR_Group,
usd_gt180, usd_gt90, usd_gt60, usd_gt30,
usd_gt15, USD_GTCURRENT,
(usd_gt180+ usd_gt90+ usd_gt60+ usd_gt30+
usd_gt15+ USD_GTCURRENT) USD_TOT_OS,
KES_gt180,KES_gt90,KES_gt60, KES_gt30,
KES_gt15, KES_GTCURRENT,
(KES_gt180+KES_gt90+KES_gt60+ KES_gt30+
KES_gt15+ KES_GTCURRENT) KES_TOT_OS,
(select DCURR_CORPORATE_RATE from
(select DCURR_CORPORATE_RATE from id_daily_currency_master order by DCURR_DATE desc) where rownum=1) Current_USD_Rate,
(usd_gt180+ usd_gt90+ usd_gt60+ usd_gt30+usd_gt15+ USD_GTCURRENT)*
(select DCURR_CORPORATE_RATE from
(select DCURR_CORPORATE_RATE from id_daily_currency_master order by DCURR_DATE desc) where rownum=1)+
(KES_gt180+KES_gt90+KES_gt60+ KES_gt30+
KES_gt15+ KES_GTCURRENT)
TOTAL_OS_IN_KES,
(CASE
WHEN((usd_gt180+ usd_gt90+ usd_gt60+ usd_gt30)*
(select DCURR_CORPORATE_RATE from
(select DCURR_CORPORATE_RATE from id_daily_currency_master order by DCURR_DATE desc) where rownum=1)+
(KES_gt180+KES_gt90+KES_gt60))>0 THEN
((usd_gt180+ usd_gt90+ usd_gt60+ usd_gt30)*
(select DCURR_CORPORATE_RATE from
(select DCURR_CORPORATE_RATE from id_daily_currency_master order by DCURR_DATE desc) where rownum=1)+
(KES_gt180+KES_gt90+KES_gt60)) ELSE 0
END) OVERDUE_IN_KES,
--==**== ==**== ==**== ==**== ==**== ==**== ==**== ==**== ==**== ==**== ==**== ==**== ==**== ==**== ==**== ==**==
(CASE
WHEN((usd_gt180)*
(select DCURR_CORPORATE_RATE from
(select DCURR_CORPORATE_RATE from id_daily_currency_master order by DCURR_DATE desc) where rownum=1)+
(KES_gt180+KES_gt90+KES_gt60))>0 THEN
((usd_gt180)*
(select DCURR_CORPORATE_RATE from
(select DCURR_CORPORATE_RATE from id_daily_currency_master order by DCURR_DATE desc) where rownum=1)+
(KES_gt180)) ELSE 0
END) GT180_IN_KES

--==**== ==**== ==**== ==**== ==**== ==**== ==**== ==**== ==**== ==**== ==**== ==**== ==**== ==**== ==**== ==**==
from
(
select
art_code,AR_NAME, CUST_STATUS,AR_Group,
nvl(sum(usd_gt180),0) usd_gt180, nvl(sum(usd_gt90),0) usd_gt90, nvl(sum(usd_gt60),0) usd_gt60, nvl(sum(usd_gt30),0) usd_gt30,
nvl(sum(usd_gt15),0) usd_gt15, nvl(sum(USD_GTCURRENT),0) USD_GTCURRENT,
nvl(sum(KES_gt180),0) KES_gt180, nvl(sum(KES_gt90),0) KES_gt90, nvl(sum(KES_gt60),0) KES_gt60, nvl(sum(KES_gt30),0) KES_gt30,
nvl(sum(KES_gt15),0) KES_gt15, nvl(sum(KES_GTCURRENT),0) KES_GTCURRENT
from
(
select
art_code,
(select AR_NAME from id_ar_master where ar_code=art_code and rownum=1) AR_NAME,
decode((select ar_status from id_ar_master where ar_code=art_code and rownum=1),'R','RECEIPTONLY','B','BLACKLISTED','N','OPERATIONAL','OTHER') CUST_STATUS,
(select AR_ANALYSIS_6 from id_ar_master where ar_code=art_code and rownum=1) AR_Group,
-- Build Ageing  -------------------------------------------------------------------------------------------- USD
(case
when (to_date(sysdate,'DD-MON-YY')-art_doc_date)>179 and art_currency='USD' then nvl(art_foreign_balance,0)
end) USD_GT180,
(case
when (to_date(sysdate,'DD-MON-YY')-art_doc_date) between 90 and 179 and art_currency='USD' then nvl(art_foreign_balance,0)
end) USD_GT90,
(case
when (to_date(sysdate,'DD-MON-YY')-art_doc_date) between 60 and 89 and art_currency='USD' then nvl(art_foreign_balance,0)
end) USD_GT60,
(case
when (to_date(sysdate,'DD-MON-YY')-art_doc_date) between 30 and 59 and art_currency='USD' then nvl(art_foreign_balance,0)
end) USD_GT30,
(case
when (to_date(sysdate,'DD-MON-YY')-art_doc_date) between 15 and 29 and art_currency='USD' then nvl(art_foreign_balance,0)
end) USD_GT15,
(case
when (to_date(sysdate,'DD-MON-YY')-art_doc_date)<15 and art_currency='USD' then nvl(art_foreign_balance,0)
end) USD_GTCURRENT,
-- Build Ageing  -------------------------------------------------------------------------------------------- KES
(case
when (to_date(sysdate,'DD-MON-YY')-art_doc_date)>179 and art_currency='KES' then nvl(art_foreign_balance,0)
end) KES_GT180,
(case
when (to_date(sysdate,'DD-MON-YY')-art_doc_date) between 90 and 179 and art_currency='KES' then nvl(art_foreign_balance,0)
end) KES_GT90,
(case
when (to_date(sysdate,'DD-MON-YY')-art_doc_date) between 60 and 89 and art_currency='KES' then nvl(art_foreign_balance,0)
end) KES_GT60,
(case
when (to_date(sysdate,'DD-MON-YY')-art_doc_date) between 30 and 59 and art_currency='KES' then nvl(art_foreign_balance,0)
end) KES_GT30,
(case
when (to_date(sysdate,'DD-MON-YY')-art_doc_date) between 15 and 29 and art_currency='KES' then nvl(art_foreign_balance,0)
end) KES_GT15,
(case
when (to_date(sysdate,'DD-MON-YY')-art_doc_date)<15 and art_currency='KES' then nvl(art_foreign_balance,0)
end) KES_GTCURRENT
-- ---------------------------------------------------------------------------------------------------------------------- Data Sources & Conditions
from id_ar_trans
where ART_BALANCE_STATUS<>'C' and
(select AR_ANALYSIS_6 from KE014T3.id_ar_master where ar_code=art_code and rownum=1)
 in ('STAFF-1','STAFF-2')
and art_foreign_balance<>0
)
--WHERE substr(trim(ar_name),0,1)='A'
group by art_code,AR_NAME, CUST_STATUS,AR_Group
)
order by art_code
