AAA

create or replace procedure PTBH_XOL_LKE_ID(
    b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_oraIn clob,b_oraOut out clob)
AS
    b_loi varchar2(100); b_lenh varchar2(1000); b_so_hd varchar2(20);
    b_so_id number; b_trangKt number; b_tu number; b_den number;
    b_trang number; b_dong number; cs_lke clob;
begin
-- Dan - Liet ke tu so ID
b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'BH','TA','X');
if b_loi is not null then raise PROGRAM_ERROR; end if;
b_lenh:=FKH_JS_LENH('so_id,trangkt');
EXECUTE IMMEDIATE b_lenh into b_so_id,b_trangKt using b_oraIn;
b_so_hd:=FTBH_XOL_ID_SO_HD(b_so_id);
if b_so_hd is null then b_loi:='loi:Hop dong da xoa:loi'; raise PROGRAM_ERROR; end if;
select count(*) into b_dong from tbh_xol;
select nvl(min(sott),b_dong) into b_tu from
    (select ngay_bd,so_hd,rownum sott from tbh_xol order by ngay_bd desc,so_hd) where so_hd>=b_so_hd;
PKH_LKE_VTRI(b_trangKt,b_tu,b_den,b_trang);
select JSON_ARRAYAGG(json_object(ngay_bd,so_hd,so_id) returning clob) into cs_lke from
    (select ngay_bd,so_hd,so_id,rownum sott from tbh_xol order by ngay_bd,so_hd)
    where sott between b_tu and b_den;
select json_object('trang' value b_trang,'dong' value b_dong,'cs_lke' value cs_lke) into b_oraOut from dual;
exception when others then if b_loi is null then raise PROGRAM_ERROR; else raise_application_error(-20105,b_loi); end if;
end;