AAA

create or replace procedure PBH_BT_DU_LKE(
    b_ma_dviN varchar2,b_nsdN varchar2,b_pas varchar2,b_oraIn clob,b_oraOut out clob)
AS
    b_loi varchar2(1000); b_lenh varchar2(2000); b_i1 number;
    b_nv varchar2(10); b_ma_dvi varchar2(10); b_phong varchar2(10); b_ttrang varchar2(1); b_loc varchar2(10);
    b_ngayD number; b_ngayC number; b_so_hs varchar2(20);
    b_tienD number; b_tienC number; b_tu number; b_den number;
    b_dong number; dt_lke clob;
Begin
delete temp_1; delete temp_2; commit;
b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsdN,b_pas,'BH','','');
if b_loi is not null then raise PROGRAM_ERROR; end if;
b_loi:='loi:Loi xu ly PBH_BT_DU_LKE:loi';
b_lenh:=FKH_JS_LENH('nv,dvi,phong,ttrang,loc,ngayd,ngayc,so_hs,tien_d,tien_c,tu,den');
EXECUTE IMMEDIATE b_lenh into b_nv,b_ma_dvi,b_phong,b_ttrang,b_loc,
    b_ngayD,b_ngayC,b_so_hs,b_tienD,b_tienC,b_tu,b_den using b_oraIn;
b_nv:=nvl(trim(b_nv),'0'); b_ma_dvi:=nvl(trim(b_ma_dvi),' ');
b_phong:=nvl(trim(b_phong),' '); b_ttrang:=nvl(trim(b_ttrang),'0');
b_loc:=nvl(trim(b_loc),'0'); b_so_hs:=nvl(trim(b_so_hs),' ');
if b_so_hs<>' ' then b_so_hs:='%'||b_so_hs||'%'; end if;
b_ngayD:=nvl(b_ngayd,0); b_ngayC:=nvl(b_ngayC,0);
if b_ngayD=30000101 then b_ngayD:=0; end if;
if b_ngayC=30000101 then b_ngayC:=0; end if;
insert into temp_1(c1,n1,c2,n3,c3,c5,c10,c11,n2,c6,c9,c12) 
    select ma_dvi,so_id,nv,so_id_hd,so_hs,ttrang,ten,ksoat,ngay_ht,nsd,phong,ma_dvi_ql from bh_bt_hs where
    b_ma_dvi in(' ',ma_dvi) and (b_so_hs=' ' or so_hs like b_so_hs) and 
    (b_ngayD=0 or ngay_ht>=b_ngayd) and (b_ngayC=0 or ngay_ht<=b_ngayC) and
    b_nv in('0',nv) and b_phong in(' ',phong) and b_ttrang in('0',ttrang) and
    (b_loc='0' or FBH_PQU_BT(nv,b_ma_dviN,b_nsdN,ma_dvi,so_id)='C') and
    (ma_dvi=b_ma_dviN or FHT_MA_NSD_DVI(b_ma_dviN,b_nsdN,'BH','BT','N',b_ma_dvi)='C');
update temp_1 set (c7,n7)=(select min(ma_nt),sum(tien) from temp_1,bh_bt_hs_nv where ma_dvi=c1 and so_id=n1);
delete temp_1 where (b_tienD<>0 and n7<b_tienD) or (b_tienC<>0 and n7>b_tienC);
select count(*) into b_dong from temp_1;
PKH_LKE_TRANG(b_dong,b_tu,b_den);
insert into temp_2(c1,n1,c2,n3,c3,c5,c7,n7,c10,c11,n2,c6,c9,c12,n11) select * from
    (select c1,n1,c2,n3,c3,c5,c7,n7,c10,c11,n2,c6,c9,c12,rownum sott from temp_1 order by c1,c9,c2,n2,c3)
    where sott between b_tu and b_den;
for r_lp in (select * from temp_2 where c5='T') loop
    select nvl(max(bt),0) into b_i1 from bh_hd_goc_ch where ma_dvi=r_lp.c1 and so_id=r_lp.n1;
    if b_i1<>0 then
        update temp_2 set c6=(select min(nsd_tr) from bh_hd_goc_ch where ma_dvi=r_lp.c1 and so_id=r_lp.n1 and bt=b_i1);
    end if;
end loop;
-- viet anh -- them c5='S' -> Dang Soan
update temp_2 set c5=decode(c5,'S','Dang Soan','D','Da duyet','H','Huy bo','Dang trinh');
select JSON_ARRAYAGG(json_object(
    'ma_dvi' value c1,'so_id' value n1,'so_hs' value c3,'ngay_ht' value n2,
    'phong' value c9,'ma_bh' value c2,'ten' value c10,'nt_tien' value c7,'tien' value n7,
    'ttrang' value c5,'nsd' value c6,'ksoat' value c11 returning clob) order by n11 returning clob) into dt_lke from temp_2;
select json_object('dong' value b_dong,'dt_lke' value dt_lke returning clob) into b_oraOut from dual;
delete temp_1; delete temp_2; commit;
exception when others then if b_loi is null then raise PROGRAM_ERROR; else raise_application_error(-20105,b_loi); end if;
end;