
create or replace view ke014t3.bcd_ict_s0171_receipt_with_inv
as
select invoice_no, receipt, receipt_date,received_amt,wht from
(select ARM_T_NUMBER invoice_no,arm_m_number Receipt,arm_m_date Receipt_date,arm_m_amount Received_AMT, (arm_t_balance-arm_m_amount) WHT from ke014t3.id_ar_match_details where arm_m_ar='S0171' and arm_m_type='BRV' 
union all
select ARM_T_NUMBER invoice_no,arm_m_number Receipt,arm_m_date Receipt_date,arm_m_amount Received_AMT, (arm_t_balance-arm_m_amount) WHT from ke074t3.id_ar_match_details where arm_m_ar='S0171' and arm_m_type='BRV' )

create or replace view ke074t3_support.bcd_ict_Safaricom_WHT_data
as
select arm_t_number ,arm_m_number  from ke074t3.id_ar_match_details where arm_m_type='SWHT' and arm_m_ar='S0171' and arm_t_number='TINS19030495'

select invoice, receipt, receipt_date,received_amt,wht from ke014t3.bcd_ict_s0171_receipt_with_inv
left join
ke074t3_support.bcd_ict_Safaricom_WHT_data
on invoice=arm_t_number
