AAA

drop table tbh_tmb_phi;
create table tbh_tmb_phi(
    so_id number,
    nbh varchar2(20),
    nbhC varchar2(20),
    ma_ta varchar2(20),
    pt number,
    tien number,
    phi number,
    tl_thue number,
    thue number,
    pt_hh number,
    hhong number,
    bt number,
    constraint tbh_tmb_phi_p primary key (ma_dvi,so_id,bt));

/

create or replace procedure PBH_KT_HTOAN_KHAC
    (b_ma_dvi varchar2,b_ngay_ht number,b_nha varchar2,b_tk_nha varchar2,a_so_id pht_type.a_num,b_loi out varchar2)
AS
    b_i1 number; b_gchu nvarchar2(200); b_l_ct varchar2(20); b_nv varchar2(1);
    b_tk_tien varchar2(20); b_ma_tk varchar2(20); b_tk_thue varchar2(20); b_noite varchar2(5);
    b_bt number; b_tien number; b_thue number; b_ttoan number; b_ma_nt varchar2(5);
begin
-- Dan - Chi, thu khac
b_bt:=0; b_noite:=FTT_TRA_NOITE(b_ma_dvi);
for b_lp in 1..a_so_id.count loop
    b_loi:='loi:Loi xu ly dong '||trim(to_char(b_lp))||':loi';
    select l_ct,ma_nt,tien_qd,ttoan_qd,thue_qd,nd into b_l_ct,b_ma_nt,b_tien,b_ttoan,b_thue,b_gchu
        from bh_cp where ma_dvi=b_ma_dvi and so_id=a_so_id(b_lp);
    if b_tk_nha is null then
        if b_ma_nt=b_noite then
            b_tk_tien:=PKH_MA_LCT_TRA_TK(b_ma_dvi,'TT','TMV',b_ngay_ht,'N');
        else    b_tk_tien:=PKH_MA_LCT_TRA_TK(b_ma_dvi,'TT','TMN',b_ngay_ht,'N');
        end if;
    else
        if b_ma_nt=b_noite then
            b_tk_tien:=PKH_MA_LCT_TRA_TK(b_ma_dvi,'TT','TGV',b_ngay_ht,'N');
        else    b_tk_tien:=PKH_MA_LCT_TRA_TK(b_ma_dvi,'TT','TGN',b_ngay_ht,'N');
        end if;
    end if;
    PBH_KT_TK_TRA(b_ma_dvi,b_ngay_ht,'G',b_l_ct,b_ma_tk,b_tk_thue);
    if b_nv<>'C' then
        b_bt:=b_bt+1; insert into ket_qua(n20,c1,c2,n1,c4,n10) values(1,'N',b_tk_tien,b_ttoan,b_gchu,b_bt);
        b_bt:=b_bt+1; insert into ket_qua(n20,c1,c2,n1,n10) values(2,'C',b_ma_tk,b_tien,b_bt);
        if b_thue<>0 then
            b_bt:=b_bt+1; insert into ket_qua(n20,c1,c2,n1,n10) values(3,'C',b_tk_thue,b_thue,b_bt);
        end if;
    else
        b_bt:=b_bt+1; insert into ket_qua(n20,c1,c2,n1,c4,n10) values(4,'N',b_ma_tk,b_tien,b_gchu,b_bt);
        if b_thue<>0 then
            b_bt:=b_bt+1; insert into ket_qua(n20,c1,c2,n1,n10) values(5,'N',b_tk_thue,b_thue,b_bt);
        end if;
        b_bt:=b_bt+1; insert into ket_qua(n20,c1,c2,n1,n10) values(6,'C',b_tk_tien,b_ttoan,b_bt);
    end if;
end loop;
b_loi:='';
exception when others then if b_loi is null then raise PROGRAM_ERROR; else null; end if;
end;

/

create or replace procedure PBH_KT_HTOAN_HD_BS_CT
    (b_ma_dvi varchar2,b_ngay_ht number,a_so_id pht_type.a_num,b_loi out varchar2)
AS
    b_i1 number; b_i2 number; b_noite varchar2(5); b_so_id number; b_pt varchar2(1); b_ma_nt varchar2(5); b_tien_qd number;
    b_ma_kh varchar2(20); b_gchu nvarchar2(200); b_ma_tk varchar2(20);
    b_phi_g number; b_phi_t number; b_phi_s number; b_thue number; b_chenh number; b_no number; b_co number; b_so_id_hd number;
    b_kieu_phv varchar2(1); b_chia_nb number; b_ngay_tt number; b_hhong_qd number; b_htro_qd number;
    b_bt number; b_bt_d number; b_ngay_hl number; b_nam_hl number; b_nam_tt number; b_nam_ng number; b_phong varchar2(10);
    b_tk_hhong varchar2(20); b_tk_htro varchar2(20); b_tk_cndl varchar2(20); b_tke_ph varchar2(1);
    b_tk_hhongT varchar2(20); b_tk_htroT varchar2(20);
begin
-- Dan - Thanh toan phi
b_bt:=0; b_noite:=FTT_TRA_NOITE(b_ma_dvi);
b_tk_hhong:=FBH_KT_TK_TRA(b_ma_dvi,b_ngay_ht,'G','DC_HH_DL');
b_tk_htro:=FBH_KT_TK_TRA(b_ma_dvi,b_ngay_ht,'G','DC_HT_DL');
b_tk_cndl:=FBH_KT_TK_TRA(b_ma_dvi,b_ngay_ht,'G','CN_HH_DL');
b_tke_ph:=FKH_NV_TSO(b_ma_dvi,'KT','BH','phong');
b_tk_hhongT:=FBH_KT_TK_TRA(b_ma_dvi,b_ngay_ht,'G','DC_HH_DLT');
b_tk_htroT:=FBH_KT_TK_TRA(b_ma_dvi,b_ngay_ht,'G','DC_HT_DLT');
for b_lp in 1..a_so_id.count loop
    b_loi:='loi:Loi xu ly dong '||trim(to_char(b_lp))||':loi';
    b_bt_d:=b_bt+1;
    b_ngay_tt:=FBH_HD_NGAY(b_ma_dvi,a_so_id(b_lp)); b_nam_tt:=PKH_SO_NAM(b_ngay_tt);
    b_phi_t:=0; b_phi_s:=0; b_phi_g:=0; b_thue:=0;
    delete bh_hd_goc_htct_temp1; delete bh_hd_goc_htct_temp;
    insert into bh_hd_goc_htct_temp1 select FBH_HD_MA_BP(b_ma_dvi,so_id),FBH_TKE_KT_NV(b_ma_dvi,lh_nv),ttoan_qd,phi_qd,hhong_qd,htro_qd,0
        from bh_hd_goc_ttpt where ma_dvi=b_ma_dvi and so_id_tt=a_so_id(b_lp) and pt not in('N','H');
    insert into bh_hd_goc_htct_temp select phong,lh_nv,sum(ttoan_qd),sum(phi_qd),sum(hhong_qd),sum(htro_qd),0 from bh_hd_goc_htct_temp1 group by phong,lh_nv;
    for r_lp1 in (select so_id,sum(phi_qd) phi_dt_qd,sum(thue_qd) thue_qd from bh_hd_goc_ttpt
        where ma_dvi=b_ma_dvi and so_id_tt=a_so_id(b_lp) and pt not in('N','H') group by so_id) loop
        b_thue:=b_thue+r_lp1.thue_qd;
        if r_lp1.phi_dt_qd<0 then
            b_phi_g:=b_phi_g+r_lp1.phi_dt_qd;
        else
            b_so_id:=r_lp1.so_id;
            b_ngay_hl:=FBH_HD_NGAY_HL(b_ma_dvi,b_so_id,b_ngay_tt); b_nam_hl:=PKH_SO_NAM(b_ngay_hl);
            for r_lp in (select ngay,sum(phi_qd) phi_qd,sum(thue_qd) thue_qd from bh_hd_goc_ttpt
                where ma_dvi=b_ma_dvi and so_id_tt=a_so_id(b_lp) and so_id=b_so_id and pt not in('N','H') group by ngay) loop
                b_nam_ng:=PKH_SO_NAM(r_lp.ngay);
                if (b_nam_tt<b_nam_ng and add_months(PKH_SO_CDT(b_ngay_hl),12)<PKH_SO_CDT(r_lp.ngay)) or (b_nam_hl<3000 and b_nam_hl>b_nam_tt) then
                    b_phi_s:=b_phi_s+r_lp.phi_qd;
                else
                    b_phi_t:=b_phi_t+r_lp.phi_qd;
                end if;
            end loop;
        end if;
    end loop;
    select nvl(sum(phi_qd),0) into b_chia_nb from bh_hd_goc_ttpb where dvi_xl=b_ma_dvi and so_id_tt=a_so_id(b_lp) and ma_dvi<>b_ma_dvi;
    b_chenh:=b_phi_t+b_phi_s+b_phi_g+b_thue;
    select min(ma_kh) into b_ma_kh from bh_hd_goc where ma_dvi=b_ma_dvi and so_id=a_so_id(b_lp);
    if trim(b_ma_kh) is not null and b_ma_kh<>'VANGLAI' then
        b_gchu:=KH_HOI_TEN(b_ma_dvi,'bh_hd_ma_kh','ma',b_ma_kh,'ten');
    end if;
    if b_phi_g<>0 then
        b_gchu:=substr('Giam phi khach hang: '||trim(b_gchu),1,200);
        b_bt:=b_bt+1;
        b_ma_tk:=FBH_KT_TK_TRA(b_ma_dvi,b_ngay_ht,'G','CH_PH_TL');
        insert into ket_qua(c1,c2,n1,c4,n10) values('N',b_ma_tk,-b_phi_g,b_gchu,b_bt); b_gchu:='';
    end if;
    if b_chenh>0 then
        b_ma_tk:=PKH_MA_LCT_TRA_TK(b_ma_dvi,'TT','D',b_ngay_ht,'P',' ','C');
        b_bt:=b_bt+1; insert into ket_qua(c1,c2,n1,n10) values('N',b_ma_tk,b_chenh,b_bt);
    end if;
    if b_phi_t>0 then
        b_ma_tk:=FBH_KT_TK_TRA(b_ma_dvi,b_ngay_ht,'G','DT_PH_BH');
        for r_lp_tke in (select * from bh_hd_goc_htct_temp order by phong,lh_nv) loop
            b_bt:=b_bt+1;
            if b_tke_ph='C' then b_gchu:=r_lp_tke.lh_nv||'.'||r_lp_tke.phong; else b_gchu:=r_lp_tke.lh_nv; end if;
            insert into ket_qua(c1,c2,c3,n1,n10) values('C',b_ma_tk,b_gchu,r_lp_tke.phi_qd,b_bt);
        end loop;
        b_gchu:='';
    end if;
    if b_phi_s>0 then
        b_bt:=b_bt+1;
        b_ma_tk:=FBH_KT_TK_TRA(b_ma_dvi,b_ngay_ht,'G','DT_PH_TR');
        insert into ket_qua(c1,c2,n1,n10) values('C',b_ma_tk,b_phi_s,b_bt);
    end if;
    if b_thue<>0 then
        b_bt:=b_bt+1;
        if b_thue>0 then
            b_ma_tk:=FBH_KT_TK_THUE(b_ma_dvi,b_ngay_ht,'G','DT_PH_BH');
            insert into ket_qua(c1,c2,n1,n10) values('C',b_ma_tk,b_thue,b_bt);
        else
            b_ma_tk:=FBH_KT_TK_THUE(b_ma_dvi,b_ngay_ht,'G','CH_PH_TL');
            insert into ket_qua(c1,c2,n1,n10) values('N',b_ma_tk,-b_thue,b_bt);
        end if;
    end if;
    if b_chenh<0 then
        b_ma_tk:=PKH_MA_LCT_TRA_TK(b_ma_dvi,'TT','D',b_ngay_ht,'D',' ','C');
        b_bt:=b_bt+1; insert into ket_qua(c1,c2,n1,n10) values('C',b_ma_tk,-b_chenh,b_bt);
    end if;
    if b_chia_nb<>0 then
        if b_chia_nb>0 then
            if b_phi_s<>0 then
                b_phi_s:=round(b_chia_nb*b_phi_s/(b_phi_t+b_phi_s),0);
                b_phi_t:=b_chia_nb-b_phi_s;
            else
                b_phi_s:=0; b_phi_t:=b_chia_nb;
            end if;
            b_ma_tk:=FBH_KT_TK_TRA(b_ma_dvi,b_ngay_ht,'G','DT_PH_BH');
            b_bt:=b_bt+1; insert into ket_qua(c1,c2,n1,c4,n10) values('N',b_ma_tk,b_phi_t,'Phi phan chia dong BH noi bo',b_bt);
            if b_phi_s<>0 then
                b_ma_tk:=FBH_KT_TK_TRA(b_ma_dvi,b_ngay_ht,'G','DT_PH_TR');
                b_bt:=b_bt+1; insert into ket_qua(c1,c2,n1,n10) values('N',b_ma_tk,b_phi_s,b_bt);
            end if;
            b_ma_tk:=FBH_KT_TK_TRA(b_ma_dvi,b_ngay_ht,'D','CN_LE_DV');
            b_bt:=b_bt+1; insert into ket_qua(c1,c2,n1,n10) values('C',b_ma_tk,b_chia_nb,b_bt);
        else
            b_ma_tk:=FBH_KT_TK_TRA(b_ma_dvi,b_ngay_ht,'D','CN_LE_DV');
            b_bt:=b_bt+1; insert into ket_qua(c1,c2,n1,c4,n10) values('N',b_ma_tk,-b_chia_nb,'Hoan phi phan chia dong BH noi bo',b_bt);
            b_ma_tk:=FBH_KT_TK_TRA(b_ma_dvi,b_ngay_ht,'G','CH_PH_TL');
            b_bt:=b_bt+1; insert into ket_qua(c1,c2,n1,n10) values('N',b_ma_tk,-b_chia_nb,b_bt);
        end if;
    end if;
    if trim(b_tk_hhong) is not null or trim(b_tk_htro) is not null then
        select nvl(sum(hhong_qd),0),nvl(sum(htro_qd),0) into b_hhong_qd,b_htro_qd from
            (select pt,so_id,hhong_qd,htro_qd from bh_hd_goc_ttpb where ma_dvi=b_ma_dvi and so_id_tt=a_so_id(b_lp))
            where pt not in('N','H') and FBH_DONG_PTHUC(b_ma_dvi,so_id)='C';
        if b_hhong_qd<>0 and trim(b_tk_hhong) is not null then
            if b_hhong_qd>0 then
                b_bt:=b_bt+1; insert into ket_qua(c1,c2,n1,c4,n10) values('N',b_tk_hhong,b_hhong_qd,'Du chi hoa hong dong BH',b_bt);
                b_bt:=b_bt+1; insert into ket_qua(c1,c2,n1,n10) values('C',b_tk_cndl,b_hhong_qd,b_bt);
            else
                b_bt:=b_bt+1; insert into ket_qua(c1,c2,n1,c4,n10) values('N',b_tk_cndl,-b_hhong_qd,'Du thu doi hoa hong dong BH',b_bt);
                b_bt:=b_bt+1; insert into ket_qua(c1,c2,n1,n10) values('C',b_tk_hhong,-b_hhong_qd,b_bt);
            end if;
        end if;
        if b_htro_qd<>0 and trim(b_tk_htro) is not null then
            if b_htro_qd>0 then
                b_bt:=b_bt+1; insert into ket_qua(c1,c2,n1,c4,n10) values('N',b_tk_htro,b_htro_qd,'Du chi ho tro dong BH',b_bt);
                b_bt:=b_bt+1; insert into ket_qua(c1,c2,n1,n10) values('C',b_tk_cndl,b_htro_qd,b_bt);
            else
                b_bt:=b_bt+1; insert into ket_qua(c1,c2,n1,c4,n10) values('N',b_tk_cndl,-b_htro_qd,'Du thu doi ho tro dong BH',b_bt);
                b_bt:=b_bt+1; insert into ket_qua(c1,c2,n1,n10) values('C',b_tk_htro,-b_htro_qd,b_bt);
            end if;
        end if;
    end if;
    if trim(b_tk_hhongT) is not null or trim(b_tk_htroT) is not null then
        select nvl(sum(hhong_qd),0),nvl(sum(htro_qd),0) into b_hhong_qd,b_htro_qd from
            (select pt,so_id,hhong_qd,htro_qd from bh_hd_goc_ttpb where ma_dvi=b_ma_dvi and so_id_tt=a_so_id(b_lp))
            where pt not in('N','H') and FBH_DONG_PTHUC(b_ma_dvi,so_id)='T';
        if b_hhong_qd<>0 and trim(b_tk_hhongT) is not null then
            if b_hhong_qd>0 then
                b_bt:=b_bt+1; insert into ket_qua(c1,c2,n1,c4,n10) values('N',b_tk_hhongT,b_hhong_qd,'Du chi hoa hong tai tam thoi',b_bt);
                b_bt:=b_bt+1; insert into ket_qua(c1,c2,n1,n10) values('C',b_tk_cndl,b_hhong_qd,b_bt);
            else
                b_bt:=b_bt+1; insert into ket_qua(c1,c2,n1,c4,n10) values('N',b_tk_cndl,-b_hhong_qd,'Du thu doi hoa hong tai tam thoi',b_bt);
                b_bt:=b_bt+1; insert into ket_qua(c1,c2,n1,n10) values('C',b_tk_hhongT,-b_hhong_qd,b_bt);
            end if;
        end if;
        if b_htro_qd<>0 and trim(b_tk_htroT) is not null then
            if b_htro_qd>0 then
                b_bt:=b_bt+1; insert into ket_qua(c1,c2,n1,c4,n10) values('N',b_tk_htroT,b_htro_qd,'Du chi ho tro tai tam thoi',b_bt);
                b_bt:=b_bt+1; insert into ket_qua(c1,c2,n1,n10) values('C',b_tk_cndl,b_htro_qd,b_bt);
            else
                b_bt:=b_bt+1; insert into ket_qua(c1,c2,n1,c4,n10) values('N',b_tk_cndl,-b_htro_qd,'Du thu doi ho tro tai tam thoi',b_bt);
                b_bt:=b_bt+1; insert into ket_qua(c1,c2,n1,n10) values('C',b_tk_htroT,-b_htro_qd,b_bt);
            end if;
        end if;
    end if;
end loop;
b_loi:='';
exception when others then if b_loi is null then raise PROGRAM_ERROR; else null; end if;
end;

/

create or replace procedure PBH_KT_HTOAN_TA_MU
    (b_ma_dvi varchar2,b_ngay_ht number,a_so_id pht_type.a_num,b_loi out varchar2)
AS
    b_bt number:=0; b_chenh number:=0; b_nv varchar2(1); b_gchu nvarchar2(200);
    b_ma_tk varchar2(20); b_ma_tk_ve varchar2(20);
    b_so_hd varchar2(20); b_so_bk varchar2(20); b_so_dc varchar2(20); b_nha_bh varchar2(20);
    a_ma_nt pht_type.a_var; a_ma pht_type.a_var; a_tien pht_type.a_num; a_tien_qd pht_type.a_num;
begin
-- Dan - Phat sinh tai mu
b_ma_tk_ve:=FBH_KT_TK_TRA(b_ma_dvi,b_ngay_ht,'T','CN_FL');
for b_lp in 1..a_so_id.count loop
    b_loi:='loi:Loi xu ly dong '||trim(to_char(b_lp))||':loi';
    select so_hd,so_bk,so_dc into b_so_hd,b_so_bk,b_so_dc from tbh_mu_ps where ma_dvi=b_ma_dvi and so_id_ps=a_so_id(b_lp);
    b_nha_bh:=FTBH_VE_HD_NBH(b_ma_dvi,b_so_hd);
    select ma_ps,ma_nt,sum(tien),sum(tien_qd) bulk collect into a_ma,a_ma_nt,a_tien,a_tien_qd from tbh_mu_ps_ct
        where ma_dvi=b_ma_dvi and so_id_ps=a_so_id(b_lp) having sum(tien)<>0 group by ma_nt,ma_ps order by ma_ps,ma_nt;
    for b_lp1 in 1..a_ma.count loop
        b_nv:=FTBH_MU_MA_LOAI(b_ma_dvi,a_ma(b_lp1));
        b_ma_tk:=FBH_KT_TK_TRA(b_ma_dvi,b_ngay_ht,'M',a_ma(b_lp1));
        b_gchu:='[BHM,'||a_ma(b_lp1)||']';
        if a_tien(b_lp1)<0 then
            if b_nv='N' then b_nv:='C'; else b_nv:='N'; end if;
            a_tien(b_lp1):=-a_tien(b_lp1); a_tien_qd(b_lp1):=-a_tien_qd(b_lp1);
        end if;
        b_bt:=b_bt+1; insert into ket_qua(c1,c2,n1,c4,n10) values(b_nv,b_ma_tk,a_tien_qd(b_lp),b_gchu,b_bt);
        if b_nv='N' then b_chenh:=b_chenh+a_tien_qd(b_lp1); else b_chenh:=b_chenh-a_tien_qd(b_lp1); end if;
    end loop;
    select ma_nt,sum(tien),sum(tien_qd) bulk collect into a_ma_nt,a_tien,a_tien_qd from tbh_mu_ps_btr
        where ma_dvi=b_ma_dvi and so_id_ps=a_so_id(b_lp) having sum(tien)<>0 group by ma_nt order by ma_nt;
    for b_lp1 in 1..a_ma_nt.count loop
        if a_tien(b_lp1)>0 then
            b_nv:='C';
        else
            b_nv:='N';
            a_tien(b_lp1):=-a_tien(b_lp1); a_tien_qd(b_lp1):=-a_tien_qd(b_lp1);
        end if;
        b_gchu:='[BHM,'||b_nha_bh||','||b_so_hd||','||b_so_bk||','||b_so_dc||','||a_ma_nt(b_lp1)||','||to_char(a_tien(b_lp1))||']';
        b_bt:=b_bt+1; insert into ket_qua(c1,c2,n1,c4,n10) values(b_nv,b_ma_tk_ve,a_tien_qd(b_lp1),b_gchu,b_bt);      
        if b_nv='C' then b_chenh:=b_chenh-a_tien_qd(b_lp1); else b_chenh:=b_chenh+a_tien_qd(b_lp1); end if;
    end loop;
end loop;
if b_chenh<>0 then
    if b_chenh>0 then
        b_nv:='C';
        b_ma_tk:=PKH_MA_LCT_TRA_TK(b_ma_dvi,'TT','D',b_ngay_ht,'D',' ','C');
        if trim(b_ma_tk) is null then b_ma_tk:=PKH_MA_LCT_TRA_TK(b_ma_dvi,'TT','D',b_ngay_ht,'G',' ','C'); end if;
    else
        b_nv:='N'; b_chenh:=-b_chenh;
        b_ma_tk:=PKH_MA_LCT_TRA_TK(b_ma_dvi,'TT','D',b_ngay_ht,'P',' ','C');
        if trim(b_ma_tk) is null then b_ma_tk:=PKH_MA_LCT_TRA_TK(b_ma_dvi,'TT','D',b_ngay_ht,'G',' ','C'); end if;
    end if;
    b_bt:=b_bt+1; insert into ket_qua(c1,c2,n1,c4,n10) values(b_nv,b_ma_tk,b_chenh,'Chenh lech ty gia',b_bt);
end if;
b_loi:='';
exception when others then if b_loi is null then raise PROGRAM_ERROR; else null; end if;
end;

/

create or replace procedure PBH_KT_CT
    (b_ma_dviN varchar2,b_nsd varchar2,b_pas varchar2,b_so_id number,cs_bh out pht_type.cs_type,
    cs_kt out pht_type.cs_type,cs_hd out pht_type.cs_type,cs_ht out pht_type.cs_type,b_dvi varchar2:='')
AS
    b_loi varchar2(100); b_i1 number; b_l_ct varchar2(5); b_ngay_ht number; b_tien_nb number:=0; b_ma_dvi varchar2(10);
begin
-- Dan - Liet ke hach toan nghiep vu bao hiem
delete temp_1; commit;
PBH_CT_XEM_KTRA(b_ma_dviN,b_nsd,b_pas,'KT','bh_kt',b_so_id,b_dvi,b_loi);
if b_loi is not null then raise PROGRAM_ERROR; end if;
if b_dvi is null then b_ma_dvi:=b_ma_dviN; else b_ma_dvi:=b_dvi; end if;
b_loi:='loi:Chung tu hach toan da xoa:loi';
select l_ct,ngay_ht into b_l_ct,b_ngay_ht from bh_kt where ma_dvi=b_ma_dvi and so_id=b_so_id;
if b_l_ct in('HD_NB','HD_TT','HD_HU') then
    select nvl(min(tien),0) into b_tien_nb from nb_1 where ma_dvi=b_ma_dvi and so_id=b_so_id;
end if;
if b_l_ct='HD_NB' then
    insert into temp_1(n1,n2,c1,c4,n3,n4)
        select so_id,ngay_ht,so_hd,'VND',tien,so_id_d from bh_hd_goc_nb where ma_dvi=b_ma_dvi and so_id_kt=b_so_id;
    update temp_1 set c2=(select ten from bh_hd_goc where ma_dvi=b_ma_dvi and so_id=n4);
elsif b_l_ct='HD_TT' then
    insert into temp_1(n1,n2,c1,c4,n3,c5)
        select a.so_id_tt,a.ngay_ht,FBH_HD_GOC_SO_HD(b_ma_dvi,b.so_id),b.ma_nt,b.tien,a.ma_kh
        from bh_hd_goc_ttps a,bh_hd_goc_tthd b where
        a.ma_dvi=b_ma_dvi and a.so_id_kt=b_so_id and b.ma_dvi=b_ma_dvi and b.so_id_tt=a.so_id_tt and a.ma_kh<>'VANGLAIDRM';
    update temp_1 set c2=KH_HOI_TEN(b_ma_dvi,'bh_hd_ma_kh','ma',c5,'ten');
elsif b_l_ct='HD_HU' then
    insert into temp_1(n1,n2,c1,c4,n3)
        select a.so_id,a.ngay_ht,FBH_HD_GOC_SO_HD(b_ma_dvi,a.so_id),b.ma_nt,b.tra
        from bh_hd_goc_hu a,bh_hd_goc_hups b where
        a.ma_dvi=b_ma_dvi and a.so_id_kt=b_so_id and b.ma_dvi=b_ma_dvi and b.so_id=a.so_id;
    update temp_1 set c2=(select ten from bh_hd_goc where ma_dvi=b_ma_dvi and so_id=n1);
elsif b_l_ct='PB_TT' then
    insert into temp_1(n1,n2,c1,c4,n3,c5,c6)
        select a.so_id_tt,a.ngay_ht,FBH_HD_GOC_SO_HD(a.ma_dvi,b.so_id),b.ma_nt,b.phi_qd,a.ma_kh,b.dvi_xl
        from bh_hd_goc_ttps a,bh_hd_goc_ttpb b where
        a.ma_dvi=b.dvi_xl and a.so_id_tt=b.so_id_tt and b.ma_dvi=b_ma_dvi and b.so_id_tt=b_so_id;
    update temp_1 set c2=KH_HOI_TEN(b_ma_dvi,'bh_hd_ma_kh','ma',c5,'ten');
elsif b_l_ct='PB_HU' then
    insert into temp_1(n1,n2,c1,c4,n3,c6)
        select a.so_id,a.ngay_ht,FBH_HD_GOC_SO_HD(a.ma_dvi,b.so_id),b.ma_nt,b.phi_qd,b.dvi_xl
        from bh_hd_goc_hu a,bh_hd_goc_ttpb b where
        a.ma_dvi=b.dvi_xl and a.so_id=b.so_id and b.ma_dvi=b_ma_dvi and b.so_id_kt=b_so_id;
    update temp_1 set c2=(select ten from bh_hd_goc where ma_dvi=c6 and so_id=n1);
elsif b_l_ct='KH_CN' then
    insert into temp_1(n1,n2,c3,c4,n3,c5)
        select so_id,ngay_ht,l_ct,ma_nt,tien,ma_kh from bh_kh_cn_tu where ma_dvi=b_ma_dvi and so_id_kt=b_so_id;
    update temp_1 set c2=KH_HOI_TEN(b_ma_dvi,'bh_hd_ma_kh','ma',c5,'ten');
elsif b_l_ct='DL_CN' then
    insert into temp_1(n1,n2,c3,c4,n3,c5)
        select so_id,ngay_ht,l_ct,ma_nt,tien,ma_kh from bh_dl_cn_tu where ma_dvi=b_ma_dvi and so_id_kt=b_so_id;
    update temp_1 set c2=(select nvl(min(ten),'Ma da bi xoa') from bh_dl_ma_kh where ma=c5);
elsif b_l_ct='DL_HH' then
    insert into temp_1(n1,n2,c1,c4,n3,c5)
        select a.so_id_hh,a.ngay_ht,FBH_HD_GOC_SO_HD(b_ma_dvi,b.so_id),b.ma_nt,b.hhong+b.htro tien,a.ma_dl
        from bh_hd_goc_hh a,bh_hd_goc_hh_ct b where
        a.ma_dvi=b_ma_dvi and a.so_id_kt=b_so_id and b.ma_dvi=b_ma_dvi and b.so_id_hh=a.so_id_hh;
    update temp_1 set c2=(select nvl(min(ten),'Ma da bi xoa') from bh_dl_ma_kh where ma=c5);
elsif b_l_ct='BT_CN' then
    insert into temp_1(n1,n2,c1,c2,c3,c4,n3)
        select so_id,ngay_ht,FBH_BT_HS_SOHS(b_ma_dvi,so_id_hs),FBH_BT_HS_HD_TEN(b_ma_dvi,so_id_hs),l_ct,ma_nt,tien
        from bh_bt_tu where ma_dvi=b_ma_dvi and so_id_kt=b_so_id;
elsif b_l_ct='BT_DU' then
    insert into temp_1(n1,n2,c4,n3)
        select a.so_id,a.ngay_qd,b.ma_nt,sum(b.tien)+sum(b.thue) from bh_bt_hs a,bh_bt_hs_nv b where
        a.ma_dvi=b_ma_dvi and a.so_id_kt=b_so_id and b.ma_dvi=b_ma_dvi and b.so_id=a.so_id group by a.so_id,a.ngay_qd,b.ma_nt;
    update temp_1 set c1=FBH_BT_HS_SOHS(b_ma_dvi,n1),c2=FBH_BT_HS_HD_TEN(b_ma_dvi,n1),n2=PKH_NG_CSO(d1);
elsif b_l_ct='PB_BT' then
    insert into temp_1(n1,n2,c4,n3,c6)
        select so_id,ngay_ht,ma_nt,sum(tien),dvi_xl from bh_bt_hs_pb where
        ma_dvi=b_ma_dvi and so_id_kt=b_so_id group by so_id,ngay_ht,ma_nt,dvi_xl;
    update temp_1 set c1=FBH_BT_HS_SOHS(c6,n1),c2=FBH_BT_HS_HD_TEN(c6,n1);
elsif b_l_ct='BT_CH' then
    insert into temp_1(n1,n2,n5,c4,n3)
        select a.so_id_tt,a.ngay_ht,min(b.so_id),b.ma_nt,sum(b.tien) from bh_bt_tt a,bh_bt_tt_ps b where
        a.ma_dvi=b_ma_dvi and a.so_id_kt=b_so_id and b.ma_dvi=b_ma_dvi and b.so_id_tt=a.so_id_tt group by a.so_id_tt,a.ngay_ht,b.ma_nt;
    update temp_1 set c1=FBH_BT_HS_SOHS(b_ma_dvi,n5),c2=FBH_BT_HS_HD_TEN(b_ma_dvi,n5);
elsif b_l_ct='GD_CN' then
    insert into temp_1(n1,n2,c1,c2,c3,c4,n3)
        select so_id,ngay_ht,FBH_BT_GD_SO_HS(b_ma_dvi,so_id_hs),FBH_BT_GD_TEN(b_ma_dvi,so_id_hs),l_ct,ma_nt,tien
        from bh_bt_gd_tu where ma_dvi=b_ma_dvi and so_id_kt=b_so_id;
elsif b_l_ct='GD_HT' then
    insert into temp_1(n1,n2,c1,c4,n3)
        select so_id,ngay_qd,so_hs,ma_nt,ttoan from bh_bt_gd_hs where ma_dvi=b_ma_dvi and so_id_kt=b_so_id;
elsif b_l_ct='GD_TT' then
    insert into temp_1(n1,n2,n5,c4,n3)
        select a.so_id_tt,a.ngay_ht,min(b.so_id),b.ma_nt,sum(b.tien) from bh_bt_gd_tt a,bh_bt_gd_tt_ps b where
        a.ma_dvi=b_ma_dvi and a.so_id_kt=b_so_id and b.ma_dvi=b_ma_dvi and b.so_id_tt=a.so_id_tt group by a.so_id_tt,a.ngay_ht,b.ma_nt;
    update temp_1 set c1=FBH_BT_GD_SO_HS(b_ma_dvi,n5),c2=FBH_BT_GD_TEN(b_ma_dvi,n5);
elsif b_l_ct='PB_GD' then
    insert into temp_1(n1,n2,c4,n3,c6)
        select so_id,ngay_ht,ma_nt,sum(tien),dvi_xl from bh_bt_gd_pb where
            ma_dvi=b_ma_dvi and so_id_kt=b_so_id group by so_id,ngay_ht,ma_nt,dvi_xl;
    update temp_1 set (c1,c7,n10)=(select so_hs,ma_dvi_ql,so_id_hd from bh_bt_gd_hs where ma_dvi=c6 and so_id=n1);
    update temp_1 set c2=(select ten from bh_hd_goc where ma_dvi=c7 and so_id=n10);
elsif b_l_ct='HK_CN' then
    insert into temp_1(n1,n2,c1,c2,c3,c4,n3)
        select so_id,ngay_ht,FBH_BT_HS_SOHS(b_ma_dvi,so_id_hs),FBH_BT_HS_HD_TEN(b_ma_dvi,so_id_hs),l_ct,ma_nt,tien
        from bh_bt_hk_tu where ma_dvi=b_ma_dvi and so_id_kt=b_so_id;
elsif b_l_ct='TB_TH' then
    insert into temp_1(n1,n2,c1,c2,c4,n3)
        select so_id,ngay_ht,FBH_BT_HS_SOHS(b_ma_dvi,so_id_hs),FBH_BT_HS_HD_TEN(b_ma_dvi,so_id_hs),ma_nt,tien
        from bh_bt_ntba_tt where ma_dvi=b_ma_dvi and so_id_kt=b_so_id;
elsif b_l_ct='TB_PB' then
    insert into temp_1(n1,n2,c4,n3,c6)
        select so_id,ngay_ht,ma_nt,sum(tien),dvi_xl from bh_bt_ntba_pb where
            ma_dvi=b_ma_dvi and so_id_kt=b_so_id group by so_id,ngay_ht,ma_nt,dvi_xl;
    update temp_1 set n10=(select so_id_hs from bh_bt_ntba_tt where ma_dvi=c6 and so_id=n1);
    update temp_1 set c1=FBH_BT_HS_SOHS(c6,n10),c2=FBH_BT_HS_HD_TEN(c6,n10);
elsif b_l_ct='BT_TH' then
    insert into temp_1(n1,n2,c1,c2,c4,n3)
        select so_id,ngay_ht,FBH_BT_HS_SOHS(b_ma_dvi,so_id_hs),FBH_BT_HS_HD_TEN(b_ma_dvi,so_id_hs),ma_nt,ttoan
        from bh_bt_thoi where ma_dvi=b_ma_dvi and so_id_kt=b_so_id;
elsif b_l_ct='TH_PB' then
    insert into temp_1(n1,n2,c4,n3,c6)
        select so_id,ngay_ht,ma_nt,sum(tien),dvi_xl from bh_bt_thoi_pb where
            ma_dvi=b_ma_dvi and so_id_kt=b_so_id group by so_id,ngay_ht,ma_nt,dvi_xl;
    update temp_1 set n10=(select so_id_hs from bh_bt_ntba_tt where ma_dvi=c6 and so_id=n1);
    update temp_1 set c1=FBH_BT_HS_SOHS(c6,n10),c2=FBH_BT_HS_HD_TEN(c6,n10);
elsif b_l_ct='KHAC' then
    insert into temp_1(n1,n2,c1,c2,c3,c4,n3) select so_id,ngay_ht,so_ct,nd,l_ct,ma_nt,tien
        from bh_cp where ma_dvi=b_ma_dvi and so_id_kt=b_so_id;
elsif b_l_ct='DO_CN' then
    insert into temp_1(n1,n2,c3,c4,n3,c5)
        select so_id,ngay_ht,l_ct,ma_nt,tien,nha_bh from bh_do_bh_cn where ma_dvi=b_ma_dvi and so_id_kt=b_so_id;
    update temp_1 set c2=FBH_MA_NBH_TEN(c5);
elsif b_l_ct='DO_TT' then
    insert into temp_1(n1,n2,c1,c3,c4,n3,c5)
        select a.so_id_tt,a.ngay_ht,FBH_HD_GOC_SO_HD(b_ma_dvi,b.so_id),b.loai,b.ma_nt,b.tien tien,a.nha_bh
        from bh_hd_do_tt a,bh_hd_do_ct b where
        a.ma_dvi=b_ma_dvi and a.so_id_kt=b_so_id and b.ma_dvi=b_ma_dvi and b.so_id_tt=a.so_id_tt;
    update temp_1 set c2=FBH_MA_NBH_TEN(c5);
elsif b_l_ct='TA_CN' then
    insert into temp_1(n1,n2,c3,c4,n3,c5)
        select so_id,ngay_ht,l_ct,ma_nt,tien,nha_bh from tbh_nha_bh_cn where ma_dvi=b_ma_dvi and so_id_kt=b_so_id;
    update temp_1 set c2=(select nvl(min(ten),'Ma da bi xoa') from bh_ma_nbh where ma=c5);
elsif b_l_ct='TA_XL' then
    insert into temp_1(n1,n2,c1,c4,n3,c5) select so_id_dc,ngay_ht,so_ct,nt_tra,tra,nha_bh
        from tbh_xl_dc where ma_dvi=b_ma_dvi and so_id_kt=b_so_id;
    update temp_1 set c2=FBH_MA_NBH_TEN(c5);
elsif b_l_ct='TA_DC' then
    insert into temp_1(n1,n2,c1,c4,n3,c5) select so_id_dc,ngay_ht,so_bk,nt_tra,tra,nha_bh
        from tbh_dc where ma_dvi=b_ma_dvi and so_id_kt=b_so_id;
    update temp_1 set c2=FBH_MA_NBH_TEN(c5);
elsif b_l_ct='TA_TT' then
    insert into temp_1(n1,n2,c1,c4,n3,c5) select so_id_tt,ngay_ht,so_ct,nt_tra,tra,nha_bh
        from tbh_tt where ma_dvi=b_ma_dvi and so_id_kt=b_so_id;
    update temp_1 set c2=FBH_MA_NBH_TEN(c5);
elsif b_l_ct='TA_KH' then
    insert into temp_1(n1,n2,c1,c2,c3,c4,n3) select so_id,ngay_ht,so_ct,' ',l_ct,ma_nt,0
        from tbh_cp where ma_dvi=b_ma_dvi and so_id_kt=b_so_id;
elsif b_l_ct='TA_MU' then
    insert into temp_1(n1,n2,c1,c4,n3,c5) select so_id_ps,ngay_ht,so_bk,'VND',tien_qd,FTBH_VE_HD_NBH(b_ma_dvi,so_hd)
        from tbh_mu_ps where ma_dvi=b_ma_dvi and so_id_kt=b_so_id;
    update temp_1 set c2=FBH_MA_NBH_TEN(c5);
end if;
open cs_bh for select a.*,b_tien_nb tien_nb from bh_kt a where ma_dvi=b_ma_dvi and so_id=b_so_id;
open cs_kt for select htoan,lk,so_ct,ngay_ct,nd from kt_1 where ma_dvi=b_ma_dvi and so_id=b_so_id;
open cs_hd for select n1 so_id,n2 ngay,c1 so_hd,c2 ten,c3 pt,c4 ma_nt,n3 tien,'' tt from temp_1 order by n2,c1,c4;
open cs_ht for select * from kt_2 where ma_dvi=b_ma_dvi and so_id=b_so_id;
exception when others then if b_loi is null then raise PROGRAM_ERROR; else  raise_application_error(-20105,b_loi); end if;
end;

/

drop table tbh_tmB_hd;
create table tbh_tmB_hd
    (ma_dvi varchar2(10),
    so_id number,
    kieu varchar2(5),        -- H:Hop dong, B-Bao gia
    ma_dvi_hd varchar2(10),
    so_hd varchar2(20),
    so_id_hd number,
    so_id_dt number,
    ten nvarchar2(500),
    bt number);
CREATE INDEX tbh_tmB_hd_i1 on tbh_tmB_hd(so_id);