SELECT 'KESELURUHAN' AS STATE,

'KESELURUHAN' AS BRANCH,

dbo.uf_get_koddana(h.funding_scheme) AS Dana,

Count(*) AS cnt_all,

Sum(h.approved_limit) AS sum_all,

Sum(isnull(w.wo_amt,0)) AS sum_wo,

Sum(isnull(RECOVERED_AMOUNT,0)) AS sum_wb,

Sum(case when bulantgkbil >= 7 and h.account_status not in (1,9,15) then 1 else 0 END) AS cnt_npl,

Sum(case when bulantgkbil >= 7 and h.account_status not in (1,9,15) then isnull(instal_arrears,0) - isnull(income_arrears,0)

Else 0 END) AS npl_costarr,

Sum(case when bulantgkbil >= 7 and h.account_status not in (1,9,15) then isnull(income_arrears,0) else 0 END) AS npl_prftarr,

Sum(case when account_status = 9 then 1 else 0 END )as bil_wo

FROM dbo.account_history h,

dbo.Write_off_report w

WHERE ssma_oracle.trunc(h.report_date) = convert(DATETIME, '2016-06-30')

AND h.account_no = w.new_account (+)

AND h.account_status not in (2,16)

GROUP by dbo.uf_get_koddana(h.funding_scheme)