AAA

create or replace procedure PBH_PKT_MA_NCT_LKE(
    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_tu number; b_den number; b_tim nvarchar2(100);
    b_dong number:=0; cs_lke clob:=''; b_i1 number;
begin
-- Dan
b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'BH','MA','');
if b_loi is not null then raise PROGRAM_ERROR; end if;
b_lenh:=FKH_JS_LENH('tim,tu,den');
EXECUTE IMMEDIATE b_lenh into b_tim,b_tu,b_den using b_oraIn;
if b_tim is null then
    select count(*) into b_dong from bh_pkt_ma_nct;
    PKH_LKE_TRANG(b_dong,b_tu,b_den);
    select JSON_ARRAYAGG(json_object(ma,ten,nsd,xep)) into cs_lke from
        (select b.*,rownum sott from
            (select a.*,rpad(lpad('-',2*(level-1),'-')||ma,20) xep from
            (select * from bh_pkt_ma_nct order by ma) a
            start with ma_ct=' ' CONNECT BY prior ma=ma_ct) b)
        where sott between b_tu and b_den;
else
    -- chuclh: tao phan cap cha con theo b_tim
    delete from temp_1; commit;
    insert into temp_1(c1,c2,c3,c4) 
           select distinct a.ma,a.ten,a.nsd,a.ma_ct from bh_pkt_ma_nct a 
           start with a.ma in (select ma from bh_pkt_ma_nct  where upper(ten) like b_tim) CONNECT BY prior a.ma_ct=a.ma;
    select count(*) into b_i1 from temp_1;
    if b_i1 > 0 then
        select count(*) into b_dong from temp_1;
        PKH_LKE_TRANG(b_dong,b_tu,b_den);
        select JSON_ARRAYAGG(json_object('ma' value c1,'ten' value c2,'nsd' value c3,xep)) into cs_lke from
        (select b.*,rownum sott from
            (select a.*,rpad(lpad('-',2*(level-1),'-')||a.c1,20) xep from temp_1 a
             start with a.c4=' ' CONNECT BY prior a.c1=a.c4) b)
        where sott between b_tu and b_den;
    end if;
end if;
select json_object('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;