AAA

create or replace procedure PBH_HD_HH_NH_NH
    (b_ma_dvi varchar2,b_nsd varchar2,dt_ct clob,
    b_so_id_hh number,b_ngay_ht number,b_ma_dl varchar2,b_pt_tra varchar2,b_ttoan_qd number,b_thue_qd number,b_phong varchar2,
    a_so_id pht_type.a_num,a_so_id_tt pht_type.a_num,a_so_hd pht_type.a_var,a_pt_tt pht_type.a_var,a_ma_nt_tt pht_type.a_var,
    a_hhong pht_type.a_num,a_htro pht_type.a_num,a_dvu pht_type.a_num,
    a_hhong_qd pht_type.a_num,a_thue_hh pht_type.a_num,a_thue_hh_qd pht_type.a_num,
    a_htro_qd pht_type.a_num,a_thue_ht pht_type.a_num,a_thue_ht_qd pht_type.a_num,
    a_dvu_qd pht_type.a_num,a_thue_dv pht_type.a_num,a_thue_dv_qd pht_type.a_num,
    a_dvi_xl pht_type.a_var,a_ngay_tt pht_type.a_num,a_pt pht_type.a_var,
    a_ma_nt pht_type.a_var,a_tien pht_type.a_num,a_tien_qd pht_type.a_num,b_loi out varchar2)
AS
    b_i1 number; b_i2 number; b_noite varchar2(5):='VND'; b_tp number; b_kt number; b_tl number; b_pt varchar2(1);
    b_hhong number; b_hhong_c number; b_hhong_t number; b_hhong_m number; b_hhong_i number; b_hhong_qd number; b_hhong_qd_c number;
    b_thue_hh number; b_thue_hh_c number; b_thue_hh_t number; b_thue_hh_m number; b_thue_hh_i number; b_thue_hh_qd number; b_thue_hh_qd_c number;
    b_htro number; b_htro_c number; b_htro_t number; b_htro_m number; b_htro_i number; b_htro_qd number; b_htro_qd_c number;
    b_thue_ht number; b_thue_ht_c number; b_thue_ht_t number; b_thue_ht_m number; b_thue_ht_i number; b_thue_ht_qd number; b_thue_ht_qd_c number;
    b_dvu number; b_dvu_c number; b_dvu_t number; b_dvu_m number; b_dvu_i number; b_dvu_qd number; b_dvu_qd_c number;
    b_thue_dv number; b_thue_dv_c number; b_thue_dv_t number; b_thue_dv_m number; b_thue_dv_i number; b_thue_dv_qd number; b_thue_dv_qd_c number;
    b_ten nvarchar2(500):=FBH_DL_MA_KH_TEN(b_ma_dl);
begin
-- Dan - Nhap duyet hoa hong
b_loi:=PKH_MA_HAN_TEST(b_ma_dvi,b_ngay_ht,'BH','HH');
if b_loi is not null then return; end if;
b_loi:='loi:Loi Table BH_HD_GOC_HH:loi';
insert into bh_hd_goc_hh values(b_ma_dvi,b_so_id_hh,b_ngay_ht,b_phong,b_ma_dl,b_ten,b_pt_tra,b_ttoan_qd,b_thue_qd,b_nsd,0);
b_loi:='loi:Loi Table BH_HD_GOC_HH_CT:loi';
for b_lp in 1..a_so_id.count loop
    insert into bh_hd_goc_hh_ct values(b_ma_dvi,b_so_id_hh,b_lp,a_so_id(b_lp),a_so_id_tt(b_lp),a_so_hd(b_lp),a_pt_tt(b_lp),a_dvi_xl(b_lp),
        b_phong,b_ma_dl,a_ngay_tt(b_lp),a_ma_nt_tt(b_lp),a_hhong(b_lp),a_htro(b_lp),a_dvu(b_lp),
        a_hhong_qd(b_lp),a_htro_qd(b_lp),a_dvu_qd(b_lp),a_thue_hh(b_lp)+a_thue_ht(b_lp)+a_thue_dv(b_lp),
        a_thue_hh_qd(b_lp)+a_thue_ht_qd(b_lp)+a_thue_dv_qd(b_lp));
end loop;
b_loi:='loi:Loi Table BH_HD_GOC_HH_TT:loi';
b_i1:=0;
for b_lp in 1..a_pt.count loop
    insert into bh_hd_goc_hh_tt values(b_ma_dvi,b_so_id_hh,b_lp,a_pt(b_lp),a_ma_nt(b_lp),a_tien(b_lp),a_tien_qd(b_lp));
    if a_pt(b_lp)='C' then
        PBH_DL_CN_TU_THOP(b_ma_dvi,'T',b_ngay_ht,b_ma_dl,'H',a_ma_nt(b_lp),a_tien(b_lp),a_tien_qd(b_lp),b_loi);
        if b_loi is not null then return; end if;
        b_i1:=1;
    elsif a_pt(b_lp)='H' then
        PBH_DL_CN_TU_THOP(b_ma_dvi,'T',b_ngay_ht,b_ma_dl,'P',a_ma_nt(b_lp),a_tien(b_lp),a_tien_qd(b_lp),b_loi);
        if b_loi is not null then return; end if;
        b_i1:=1;
    end if;
end loop;
if b_i1<>0 then
    PBH_DL_CN_TU_KTRA(b_ma_dvi,b_loi);
    if b_loi is not null then return; end if;
end if;
b_loi:='loi:Loi Table BH_HD_GOC_HH_PT:loi'; b_kt:=0;
for b_lp in 1..a_so_id.count loop
    if a_ma_nt_tt(b_lp)<>b_noite then b_tp:=2; else b_tp:=0; end if;
    b_hhong_c:=a_hhong(b_lp); b_hhong_qd_c:=a_hhong_qd(b_lp);
    b_thue_hh_c:=a_thue_hh(b_lp); b_thue_hh_qd_c:=a_thue_hh_qd(b_lp);
    b_hhong_m:=0; b_hhong_i:=b_kt; b_thue_hh_m:=0; b_thue_hh_i:=b_kt;
    b_htro_c:=a_htro(b_lp); b_htro_qd_c:=a_htro_qd(b_lp);
    b_thue_ht_c:=a_thue_ht(b_lp); b_thue_ht_qd_c:=a_thue_ht_qd(b_lp);
    b_htro_m:=0; b_htro_i:=b_kt; b_thue_ht_m:=0; b_thue_ht_i:=b_kt;
    b_dvu_c:=a_dvu(b_lp); b_dvu_qd_c:=a_dvu_qd(b_lp);
    b_thue_dv_c:=a_thue_dv(b_lp); b_thue_dv_qd_c:=a_thue_dv_qd(b_lp);
    b_dvu_m:=0; b_dvu_i:=b_kt; b_thue_dv_m:=0; b_thue_dv_i:=b_kt;
    if a_so_id(b_lp)=a_so_id_tt(b_lp) and FBH_HD_HU(b_ma_dvi,a_so_id(b_lp))='C' then
        b_pt:='H';
    else
        b_pt:='*';
    end if;
    select nvl(sum(hhong),0),nvl(sum(htro),0),nvl(sum(dvu),0) into b_hhong_t,b_htro_t,b_dvu_t
        from bh_hd_goc_ttpb where ma_dvi=b_ma_dvi and so_id=a_so_id(b_lp) and
        so_id_tt=a_so_id_tt(b_lp) and b_pt in('*',pt) and ma_nt=a_ma_nt_tt(b_lp);
    for r_lp in (select lh_nv,sum(hhong) hhong,sum(htro) htro,sum(dvu) dvu from bh_hd_goc_ttpb where
        ma_dvi=b_ma_dvi and so_id=a_so_id(b_lp) and so_id_tt=a_so_id_tt(b_lp) and
        b_pt in('*',pt) and ma_nt=a_ma_nt_tt(b_lp) group by lh_nv) loop
        if r_lp.hhong<>0 or r_lp.htro<>0 or r_lp.dvu<>0 then
            b_hhong:=0; b_hhong_qd:=0; b_thue_hh:=0; b_thue_hh_qd:=0;
            if r_lp.hhong<>0 and b_hhong_t<>0 then
                b_tl:=r_lp.hhong/b_hhong_t;
                b_hhong:=round(a_hhong(b_lp)*b_tl,b_tp); b_hhong_qd:=round(a_hhong_qd(b_lp)*b_tl,0);
                b_thue_hh:=round(a_thue_hh(b_lp)*b_tl,b_tp); b_thue_hh_qd:=round(a_thue_hh_qd(b_lp)*b_tl,0);
                b_hhong_c:=b_hhong_c-b_hhong; b_hhong_qd_c:=b_hhong_qd_c-b_hhong_qd;
                b_thue_hh_c:=b_thue_hh_c-b_thue_hh; b_thue_hh_qd_c:=b_thue_hh_qd_c-b_thue_hh_qd;
            end if;
            b_htro:=0; b_htro_qd:=0; b_thue_ht:=0; b_thue_ht_qd:=0;
            if r_lp.htro<>0 and b_htro_t<>0 then
                b_tl:=r_lp.htro/b_htro_t;
                b_htro:=round(a_htro(b_lp)*b_tl,b_tp); b_htro_qd:=round(a_htro_qd(b_lp)*b_tl,0);
                b_thue_ht:=round(a_thue_ht(b_lp)*b_tl,b_tp); b_thue_ht_qd:=round(a_thue_ht_qd(b_lp)*b_tl,0);
                b_htro_c:=b_htro_c-b_htro; b_htro_qd_c:=b_htro_qd_c-b_htro_qd;
                b_thue_ht_c:=b_thue_ht_c-b_thue_ht; b_thue_ht_qd_c:=b_thue_ht_qd_c-b_thue_ht_qd;
            end if;
            b_dvu:=0; b_dvu_qd:=0; b_thue_dv:=0; b_thue_dv_qd:=0;
            if r_lp.dvu<>0 and b_dvu_t<>0 then
                b_tl:=r_lp.dvu/b_dvu_t;
                b_dvu:=round(a_dvu(b_lp)*b_tl,b_tp); b_dvu_qd:=round(a_dvu_qd(b_lp)*b_tl,0);
                b_thue_dv:=round(a_thue_dv(b_lp)*b_tl,b_tp); b_thue_dv_qd:=round(a_thue_dv_qd(b_lp)*b_tl,0);
                b_dvu_c:=b_dvu_c-b_dvu; b_dvu_qd_c:=b_dvu_qd_c-b_dvu_qd;
                b_thue_dv_c:=b_thue_dv_c-b_thue_dv; b_thue_dv_qd_c:=b_thue_dv_qd_c-b_thue_dv_qd;
            end if;
            b_kt:=b_kt+1;
            insert into bh_hd_goc_hh_pt values(b_ma_dvi,b_so_id_hh,b_kt,a_so_id(b_lp),a_so_id_tt(b_lp),a_pt_tt(b_lp),
                a_ma_nt_tt(b_lp),r_lp.lh_nv,b_hhong,b_hhong_qd,b_htro,b_htro_qd,b_dvu,b_dvu_qd,
                b_thue_hh,b_thue_hh_qd,b_thue_ht,b_thue_ht_qd,b_thue_dv,b_thue_dv_qd);
            if b_hhong_m<b_hhong then b_hhong_m:=b_hhong; b_hhong_i:=b_kt; end if;
            if b_thue_hh_m<b_thue_hh then b_thue_hh_m:=b_thue_hh; b_thue_hh_i:=b_kt; end if;
            if b_htro_m<b_htro then b_htro_m:=b_htro; b_htro_i:=b_kt; end if;
            if b_thue_ht_m<b_thue_ht then b_thue_ht_m:=b_thue_ht; b_thue_ht_i:=b_kt; end if;
            if b_dvu_m<b_dvu then b_dvu_m:=b_dvu; b_dvu_i:=b_kt; end if;
            if b_thue_dv_m<b_thue_dv then b_thue_dv_m:=b_thue_dv; b_thue_dv_i:=b_kt; end if;
        end if;
    end loop;
    if b_hhong_c<>0 or b_hhong_qd_c<>0 then
        update bh_hd_goc_hh_pt set hhong=hhong+b_hhong_c,hhong_qd=hhong_qd+b_hhong_qd_c
            where ma_dvi=b_ma_dvi and so_id_hh=b_so_id_hh and bt=b_hhong_i;
    end if;
    if b_thue_hh_c<>0 or b_thue_hh_qd_c<>0 then
        update bh_hd_goc_hh_pt set thue_hh=thue_hh+b_thue_hh_c,thue_hh_qd=thue_hh_qd+b_thue_hh_qd_c
            where ma_dvi=b_ma_dvi and so_id_hh=b_so_id_hh and bt=b_thue_hh_i;
    end if;
    if b_htro_c<>0 or b_htro_qd_c<>0 then
        update bh_hd_goc_hh_pt set htro=htro+b_htro_c,htro_qd=htro_qd+b_htro_qd_c
            where ma_dvi=b_ma_dvi and so_id_hh=b_so_id_hh and bt=b_htro_i;
    end if;
    if b_thue_ht_c<>0 or b_thue_ht_qd_c<>0 then
        update bh_hd_goc_hh_pt set thue_ht=thue_ht+b_thue_ht_c,thue_ht_qd=thue_ht_qd+b_thue_ht_qd_c
            where ma_dvi=b_ma_dvi and so_id_hh=b_so_id_hh and bt=b_thue_ht_i;
    end if;
    if b_dvu_c<>0 or b_dvu_qd_c<>0 then
        update bh_hd_goc_hh_pt set dvu=dvu+b_dvu_c,dvu_qd=dvu_qd+b_dvu_qd_c
            where ma_dvi=b_ma_dvi and so_id_hh=b_so_id_hh and bt=b_dvu_i;
    end if;
    if b_thue_dv_c<>0 or b_thue_dv_qd_c<>0 then
        update bh_hd_goc_hh_pt set thue_dv=thue_dv+b_thue_dv_c,thue_dv_qd=thue_dv_qd+b_thue_dv_qd_c
            where ma_dvi=b_ma_dvi and so_id_hh=b_so_id_hh and bt=b_thue_dv_i;
    end if;
end loop;
for b_lp in 1..a_so_id.count loop
    PBH_TH_HH(b_ma_dvi,a_so_id(b_lp),a_so_id_tt(b_lp),b_loi);
    if b_loi is not null then return; end if;
end loop;
insert into bh_hd_goc_hh_txt values(b_ma_dvi,b_so_id_hh,'dt_ct','"'||dt_ct||'"');
b_loi:='';
exception when others then if b_loi is null then raise PROGRAM_ERROR; else null; end if;
end;