AAA

CREATE OR REPLACE procedure PBH_HD_UP_XOA(
    b_ma_dvi varchar2,b_so_id number,b_dvi_ksoatN varchar2,b_ksoatN varchar2,b_loi out varchar2,b_du varchar2:='K')
AS
    b_lenh varchar2(1000); b_i1 number; b_i2 number; b_so_hd varchar2(20);
    b_nv varchar2(10); b_kieu_hd varchar2(1); b_so_idD number; b_ngay_ht number;
    b_ttrang varchar2(1); b_dvi_ksoat varchar2(10); b_ksoat varchar2(10);
    a_bang pht_type.a_var;
begin
-- Dan - Xoa hop dong goc
b_loi:='loi:Loi Up xoa:loi';
select count(*) into b_i1 from bh_hd_goc where ma_dvi=b_ma_dvi and so_id=b_so_id;
if b_i1=0 then b_loi:=''; return; end if;
select nv,kieu_hd,so_id_d,ttrang,dvi_ksoat,ksoat,so_hd,ngay_ht into
    b_nv,b_kieu_hd,b_so_idD,b_ttrang,b_dvi_ksoat,b_ksoat,b_so_hd,b_ngay_ht
    from bh_hd_goc where ma_dvi=b_ma_dvi and so_id=b_so_id;
if b_ttrang='H' then b_loi:='loi:Hop dong/GCN da huy:loi'; return; end if;
if b_ttrang='D' then b_loi:='loi:Khong sua, xoa hop dong/GCN da duyet:loi'; return; end if;
if b_ttrang not in('T','D') then b_loi:=''; return; end if;
if trim(b_ksoat) is not null and (b_dvi_ksoatN<>b_dvi_ksoat or b_ksoatN<>b_ksoat) then
    b_loi:='loi:Nguoi khac duyet:loi'; return;
end if;
if b_ttrang='D' then
    select nvl(max(ngay_ht),0) into b_i1 from bh_hd_goc_tthd where ma_dvi=b_ma_dvi and so_id=b_so_idD;
    if b_i1>b_ngay_ht then b_loi:='loi:Hop dong/GCN da thanh toan phi:loi'; return; end if;
    select count(*) into b_i1 from tbh_ghep_hd where ma_dvi_hd=b_ma_dvi and so_id_hd=b_so_id;
    if b_i1=0 then
        select count(*) into b_i1 from tbh_tm_hd where ma_dvi_hd=b_ma_dvi and so_id_hd=b_so_id;
    end if;
    if b_i1<>0 then b_loi:='loi:Hop dong/GCN da xu ly tai:loi'; return; end if;
end if;
PBH_GCNE_XOA(b_ma_dvi,b_so_id,b_loi);
if b_loi is not null then return; end if;
PTBH_TMB_CBI_XOA(b_ma_dvi,b_so_id,b_loi);
PTBH_CBI_XOA(b_ma_dvi,b_so_id,b_loi);
if b_loi is not null then return; end if;
PBH_HD_GOC_XOA_NV(b_ma_dvi,b_so_id,b_loi);
if b_loi is not null then return; end if;
if b_du='C' then
    PBH_HD_DU_NV(b_nv,b_ma_dvi,b_so_id,'','','T',b_so_hd,b_loi);
    if b_loi is not null then return; end if;
end if;
update bh_hd_goc set so_hd=b_so_hd,ksoat='',dvi_ksoat='',ttrang='T',so_id_kt=-1 where ma_dvi=b_ma_dvi and so_id=b_so_id;
if b_kieu_hd='U' then return; end if;
if b_kieu_hd='K' then
    PBH_HD_GOC_THL_KEM(b_ma_dvi,b_so_idD,b_loi);
else
    PBH_HD_GOC_THL_CT(b_ma_dvi,b_so_idD,b_loi,b_so_id);
end if;
if b_loi is not null then return; 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_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_TIM
    (b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_lct varchar2,
    b_ngayd number,b_ngayc number,b_tiend number,b_tienc number,b_so_hd varchar2,
    b_ma_kh varchar2,b_ten_kh nvarchar2,b_so_ct varchar2,b_lechsl varchar2,
    b_tu_n number,b_den_n number,b_dong out number,cs_tim out pht_type.cs_type)
as
     b_tu number:=b_tu_n; b_den number:=b_den_n; b_loi varchar2(100);
     b_ngay_ht number; b_nha varchar2(20); b_tk_nha varchar2(20); b_i1 number; a_so_id pht_type.a_num; a_so_hd pht_type.a_var;
begin
b_loi:=fht_ma_nsd_ktra(b_ma_dvi,b_nsd,b_pas,'KT','BH','NX');
if b_loi is not null then raise program_error; end if;
delete temp_1;delete temp_2;delete temp_3;delete temp_4;commit;
insert into temp_3 (n1,n2,c1) select distinct a.so_id,a.ngay_ht,a.so_ct from bh_kt a where a.ma_dvi=b_ma_dvi
    and a.l_ct=b_lct and a.ngay_ht between b_ngayd and b_ngayc
    and (b_so_ct is null  or upper(a.so_ct) like '%'||upper(b_so_ct)||'%');
if b_lct='HD_NB' then
    insert into temp_2(c1,c2,n1,n2,n3,n4,n5) select distinct c1,' ',n1,n2,tien,so_id,so_id from bh_hd_goc_nb,temp_3
        where ma_dvi=b_ma_dvi and (b_so_hd is null  or upper(so_hd) like '%'||upper(b_so_hd)||'%') and so_id_kt=n1;
    update temp_2 set c2=(select ma_kh from bh_hd_goc where ma_dvi=b_ma_dvi and so_id=n4);
elsif b_lct='HD_TT' then
    insert into temp_2(c1,c2,n1,n2,n3,n4,n5) select c1,a.ma_kh,n1,n2,sum(b.ttoan),a.so_id_tt,b.so_id
        from bh_hd_goc_ttps a,bh_hd_goc_ttpt b,temp_3
        where a.ma_dvi=b_ma_dvi and (b_ma_kh is null or a.ma_kh=b_ma_kh) and a.so_id_kt=n1
        and b.ma_dvi=b_ma_dvi and a.so_id_tt=b.so_id_tt
        group by c1,a.ma_kh,n1,n2,a.so_id_tt,b.so_id;
    if b_so_hd is not null then
        delete temp_2 where not exists (select so_id from bh_hd_goc where ma_dvi=b_ma_dvi and so_id=n5 and upper(so_hd) like '%'||upper(b_so_hd)||'%');
    end if;
elsif b_lct='KH_CN' then
    insert into temp_2(c1,c2,n1,n2,n3,n4) select distinct c1,ma_kh,n1,n2,tien_qd,so_id from bh_kh_cn_tu,temp_3
        where ma_dvi=b_ma_dvi
        and (b_so_hd is null or upper(c2) like '%'||upper(b_so_hd)||'%')
        and (b_ma_kh is null or upper(ma_kh) like '%'||upper(b_ma_kh)||'%') and so_id_kt=n1;
elsif b_lct='BT_DU' then
    insert into temp_2(c1,c2,n1,n2,n3,n4,n5)
        select distinct c1,' ',n1,n2,b.tien_qd,a.so_id,a.so_id_hd from bh_bt_hs a,bh_bt_hs_ps b,temp_3 c
        where a.ma_dvi=b_ma_dvi and b.ma_dvi=b_ma_dvi and a.so_id=b.so_id and a.so_id_kt=c.n1
        and (b_so_hd is null or upper(a.so_hs) like '%'||b_so_hd||'%');
    update temp_2 set (c2)=(select ma_kh from bh_hd_goc where ma_dvi=b_ma_dvi and so_id=n5);
elsif b_lct='BT_CH' then
    insert into temp_2(c1,c2,n1,n2,n3,n4) select distinct c1,a.ma_kh,c.n1,c.n2,b.tien_qd,b.so_id_tt
        from bh_bt_tt a,bh_bt_tt_ct b,temp_3 c
        where a.ma_dvi=b_ma_dvi and b.ma_dvi=b_ma_dvi and a.so_id_tt=b.so_id_tt and a.so_id_kt=c.n1
        and (b_ma_kh is null or a.ma_kh=b_ma_kh);
    if b_so_hd is not null then
        update temp_2 set n6=(select so_id from bh_bt_tt_ps where ma_dvi=b_ma_dvi and so_id_tt=n4);
        delete temp_2 where not exists (select * from bh_bt_hs where ma_dvi=b_ma_dvi and so_id=n6 and upper(so_hs) like '%'||upper(b_so_hd)||'%');
    end if;
elsif b_lct='DL_HH' then
    insert into temp_2(c1,c2,n1,n2,n5,n4) select distinct c.c1,a.ma_dl,n1,n2,b.so_id,a.so_id_hh
        from bh_hd_goc_hh a,bh_hd_goc_hh_pt b,temp_3 c
        where a.ma_dvi=b_ma_dvi and b.ma_dvi=b_ma_dvi and (b_ma_kh is null or a.ma_dl=b_ma_kh) and a.so_id_hh=b.so_id_hh and a.so_id_kt=c.n1;
    update temp_2 set n3=(select sum(tien_qd) from bh_hd_goc_hh_tt where ma_dvi=b_ma_dvi and so_id_hh=n4);
    if b_so_hd is not null then
        delete temp_2 where not exists (select so_id from bh_hd_goc where ma_dvi=b_ma_dvi and so_id=n5 and upper(so_hd) like '%'||upper(b_so_hd)||'%');
    end if;
elsif b_lct='DL_CN' then
    insert into temp_2(c1,c2,n1,n2,n3,n4) select distinct c1,ma_kh,n1,n2,tien_qd,so_id from bh_dl_cn_tu,temp_3
        where ma_dvi=b_ma_dvi
        and (b_ma_kh is null or upper(ma_kh) like '%'||upper(b_ma_kh)||'%') and so_id_kt=n1;
elsif b_lct='HD_HU' then
    insert into temp_2(c1,c2,n1,n2,n3,n4) select distinct c1,' ',n1,n2,0 tien,so_id from bh_hd_goc_hu,temp_3
        where ma_dvi=b_ma_dvi and so_id_kt=n1;
    update temp_2 set c2=(select ma_kh from bh_hd_goc where ma_dvi=b_ma_dvi and so_id=n5);
    if b_so_hd is not null then
        delete temp_2 where not exists(select * from bh_hd_goc where ma_dvi=b_ma_dvi and so_id=n5 and upper(so_hd) like '%'||upper(b_so_hd)||'%');
    end if;
else
    insert into temp_2(c1,c2,n1,n2,n3,n4) select distinct c1,' ',n1,n2,0,0 from temp_3;
end if;
if b_ma_kh is not null then delete temp_2 where upper(nvl(c2,' ')) not like '%'||b_ma_kh||'%' ;end if;
update temp_2 set c3=KH_HOI_TEN(b_ma_dvi,'bh_hd_ma_kh','ma',c2,'ten');
update temp_2 set c3=(select ten from bh_dl_ma_kh where ma=c2) where c3 is null;
if b_ten_kh is not null then delete temp_2 where upper(nvl(c3,' ')) not like '%'||b_ten_kh||'%' ;end if;
delete temp_2 where (b_tiend>0 and n3<b_tiend) or (b_tienc>0 and n3>b_tienc);
if b_lechsl='C' then
    for lp in (select distinct n1 from temp_2) loop
        select ngay_ht,nha,tk_nha into b_ngay_ht,b_nha,b_tk_nha from bh_kt where ma_dvi=b_ma_dvi and so_id=lp.n1;
        b_i1:=0; PKH_MANG_XOA_N(a_so_id); PKH_MANG_XOA(a_so_hd);
        for lp1 in (select distinct n4 from temp_2 where n1=lp.n1) loop
            b_i1:=b_i1+1; a_so_id(b_i1):=lp1.n4;
        end loop;
        delete ket_qua;
        if b_lct='HD_NB' then PBH_KT_HTOAN_HD_BS(b_ma_dvi,b_ngay_ht,a_so_id,b_loi);
        elsif b_lct='HD_TT' then PBH_KT_HTOAN_HD_TT(b_ma_dvi,b_ngay_ht,b_nha,b_tk_nha,a_so_id,b_loi);
        elsif b_lct='HD_HU' then PBH_KT_HTOAN_HD_HU(b_ma_dvi,b_ngay_ht,b_nha,b_tk_nha,a_so_id,b_loi);
        elsif b_lct='PB_TT' then PBH_KT_HTOAN_PB_TT(b_ma_dvi,b_ngay_ht,a_so_id,b_loi);
        elsif b_lct='PB_HU' then PBH_KT_HTOAN_PB_HU(b_ma_dvi,b_ngay_ht,a_so_id,b_loi);
        elsif b_lct='KH_CN' then PBH_KT_HTOAN_KH_CN(b_ma_dvi,b_ngay_ht,b_nha,b_tk_nha,a_so_id,b_loi);
        elsif b_lct='DL_CN' then PBH_KT_HTOAN_DL_CN(b_ma_dvi,b_ngay_ht,b_nha,b_tk_nha,a_so_id,b_loi);
        elsif b_lct='DL_HH' then PBH_KT_HTOAN_DL_HH(b_ma_dvi,b_ngay_ht,b_nha,b_tk_nha,a_so_id,a_so_hd,b_loi);
        elsif b_lct='BT_CN' then PBH_KT_HTOAN_BTU(b_ma_dvi,b_ngay_ht,b_nha,b_tk_nha,a_so_id,b_loi);
        elsif b_lct='BT_DU' then PBH_KT_HTOAN_BT_DU(b_ma_dvi,b_ngay_ht,b_nha,b_tk_nha,a_so_id,b_loi);
        elsif b_lct='PB_BT' then PBH_KT_HTOAN_PB_BT(b_ma_dvi,b_ngay_ht,a_so_id,b_loi);
        elsif b_lct='BT_CH' then PBH_KT_HTOAN_BT_CH(b_ma_dvi,b_ngay_ht,b_nha,b_tk_nha,a_so_id,b_loi);
        elsif b_lct='GD_CN' then PBH_KT_HTOAN_GD_CN(b_ma_dvi,b_ngay_ht,b_nha,b_tk_nha,a_so_id,b_loi);
        elsif b_lct='GD_HT' then PBH_KT_HTOAN_GD_HT(b_ma_dvi,b_ngay_ht,b_nha,b_tk_nha,a_so_id,b_loi);
        elsif b_lct='GD_TT' then PBH_KT_HTOAN_GD_TT(b_ma_dvi,b_ngay_ht,b_nha,b_tk_nha,a_so_id,b_loi);
        elsif b_lct='PB_GD' then PBH_KT_HTOAN_PB_GD(b_ma_dvi,b_ngay_ht,a_so_id,b_loi);
        elsif b_lct='HK_CN' then PBH_KT_HTOAN_HK_CN(b_ma_dvi,b_ngay_ht,b_nha,b_tk_nha,a_so_id,b_loi);
        elsif b_lct='TB_TH' then PBH_KT_HTOAN_TB_TH(b_ma_dvi,b_ngay_ht,b_nha,b_tk_nha,a_so_id,b_loi);
        elsif b_lct='PB_TB' then PBH_KT_HTOAN_PB_TB(b_ma_dvi,b_ngay_ht,a_so_id,b_loi);
        elsif b_lct='BT_TH' then PBH_KT_HTOAN_BT_TH(b_ma_dvi,b_ngay_ht,b_nha,b_tk_nha,a_so_id,b_loi);
        elsif b_lct='PB_TH' then PBH_KT_HTOAN_PB_TH(b_ma_dvi,b_ngay_ht,a_so_id,b_loi);
        elsif b_lct='KHAC' then PBH_KT_HTOAN_KHAC(b_ma_dvi,b_ngay_ht,b_nha,b_tk_nha,a_so_id,b_loi);
        elsif b_lct='DO_CN' then PBH_KT_HTOAN_DO_CN(b_ma_dvi,b_ngay_ht,b_nha,b_tk_nha,a_so_id,b_loi);
        elsif b_lct='DO_TT' then PBH_KT_HTOAN_DO_TT(b_ma_dvi,b_ngay_ht,b_nha,b_tk_nha,a_so_id,b_loi);
        elsif b_lct='TA_CN' then PBH_KT_HTOAN_TA_CN(b_ma_dvi,b_ngay_ht,b_nha,b_tk_nha,a_so_id,b_loi);
        elsif b_lct='TA_XL' then PBH_KT_HTOAN_TA_XL(b_ma_dvi,b_ngay_ht,b_nha,b_tk_nha,a_so_id,b_loi);
        elsif b_lct='TA_DC' then PBH_KT_HTOAN_TA_DC(b_ma_dvi,b_ngay_ht,b_nha,b_tk_nha,a_so_id,b_loi);
        elsif b_lct='TA_TT' then PBH_KT_HTOAN_TA_TT(b_ma_dvi,b_ngay_ht,b_nha,b_tk_nha,a_so_id,b_loi);
        elsif b_lct='TA_KH' then PBH_KT_HTOAN_TA_KH(b_ma_dvi,b_ngay_ht,b_nha,b_tk_nha,a_so_id,b_loi);
        elsif b_lct='TA_MU' then PBH_KT_HTOAN_TA_MU(b_ma_dvi,b_ngay_ht,a_so_id,b_loi);
        --elsif b_lct='TA_XOL' then PBH_KT_HTOAN_TA_XOL(b_ma_dvi,b_ngay_ht,a_so_id,b_loi);
        end if;
        if b_loi is not null then raise PROGRAM_ERROR; end if;
        delete temp_1;
        insert into temp_1(n1,c1,c2,n2) select lp.n1,c1,c2,sum(n1) from ket_qua group by c1,c2;
        insert into temp_1(n1,c1,c2,n2) select lp.n1,nv,ma_tk,-sum(tien) from kt_2 where ma_dvi=b_ma_dvi and so_id=lp.n1 group by nv,ma_tk;
        select count(*) into b_i1 from (select c1,c2,sum(n2) from temp_1 group by c1,c2 having sum(n2)<>0);
        if b_i1<>0 then insert into temp_4(n1) values(lp.n1); end if;
    end loop;
    delete temp_2 where n1 not in (select n1 from temp_4);
end if;
insert into temp_2(c1,c2,c3,n1,n2,n20) select distinct c1,c2,c3,n1,n2,2 from temp_2;
delete temp_2 where nvl(n20,0)<>2;

select count(*) into b_dong from temp_2;
PKH_LKE_TRANG(b_dong,b_tu,b_den);
open cs_tim for select * from (select n1 so_id,PKH_SO_CNG(n2) ngay_ht, c3 ten_kh,c1 so_ct,row_number() over (order by n2,n1) sott
    from temp_2 order by n2,n1) where sott between b_tu and b_den;
exception when others then raise_application_error(-20105,b_loi);
end;
/