AAA

create or replace procedure PHT_MA_NSDJ_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_md varchar2(10):='BH'; b_dvi varchar2(500); b_tim varchar2(500);
    b_dong number:=0; dt_lke clob:=''; dt_nv clob:=''; dt_nhom clob:=''; dt_dvi clob:=''; b_vp varchar(1);
begin
-- Dan
b_lenh:=FKH_JS_LENH('tu,den,md,dvi,tim');
EXECUTE IMMEDIATE b_lenh into b_tu,b_den,b_md,b_dvi,b_tim using b_oraIn;
b_loi:=FHTG_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,b_md,'','');
if b_loi is not null then raise PROGRAM_ERROR; end if;
delete temp_1; delete temp_2; commit;
-- chuclh kiem tra ten khong hop le
b_dvi := pkh_ma_tenl(b_dvi);
if nvl(trim(b_dvi),' ') = ' ' then return; end if;
select vp into b_vp from ht_ma_dvi where ma=b_dvi;
if b_tim is null then
  if FHTG_MA_NSD_QU(b_ma_dvi,b_nsd,'HT','ND','X')<>'C' then
      b_dong:=1;
      select JSON_ARRAYAGG(json_object(phong,ma,ten)) into dt_lke from ht_ma_nsd where ma_dvi=b_dvi and ma=b_nsd;
  elsif trim(b_md) is null then
      select count(*) into b_dong from ht_ma_nsd where ma_dvi=b_dvi;
      PKH_LKE_TRANG(b_dong,b_tu,b_den);
      select JSON_ARRAYAGG(json_object(phong,ma,ten) order by ma returning clob) into dt_lke from
          (select a.*,rownum sott from ht_ma_nsd a where a.ma_dvi=b_dvi order by ma)
          where sott between b_tu and b_den;
  else
      insert into temp_1(c1) select distinct ma from ht_ma_nsd_nv where ma_dvi=b_dvi and md in(b_md,'HT');
      insert into temp_1(c1) select distinct ma from ht_ma_nsd_nhom where ma_dvi=b_dvi and md in(b_md,'HT');
      insert into temp_2(c1) select distinct c1 from temp_1;
      select JSON_ARRAYAGG(json_object(phong,ma,ten) order by ma returning clob) into dt_lke from
      (select a.*,rownum sott from ht_ma_nsd a where a.ma_dvi=b_dvi and a.ma in(select c1 from temp_2) order by ma)
      where sott between b_tu and b_den;
  end if;
else 
  if FHTG_MA_NSD_QU(b_ma_dvi,b_nsd,'HT','ND','X')<>'C' then
      b_dong:=1;
      select JSON_ARRAYAGG(json_object(phong,ma,ten)) into dt_lke from ht_ma_nsd where ma_dvi=b_dvi and ma=b_nsd and upper(ten) like b_tim;
  elsif trim(b_md) is null then
      select count(*) into b_dong from ht_ma_nsd where ma_dvi=b_dvi and upper(ten) like b_tim;
      PKH_LKE_TRANG(b_dong,b_tu,b_den);
      select JSON_ARRAYAGG(json_object(phong,ma,ten) order by ma returning clob) into dt_lke from
          (select a.*,rownum sott from ht_ma_nsd a where a.ma_dvi=b_dvi and upper(ten) like b_tim order by upper(ten))
          where sott between b_tu and b_den;
  else
      insert into temp_1(c1) select distinct ma from ht_ma_nsd_nv where ma_dvi=b_dvi and md in(b_md,'HT');
      insert into temp_1(c1) select distinct ma from ht_ma_nsd_nhom where ma_dvi=b_dvi and md in(b_md,'HT');
      insert into temp_2(c1) select distinct c1 from temp_1;
      select JSON_ARRAYAGG(json_object(phong,ma,ten) order by ma returning clob) into dt_lke from
      (select a.*,rownum sott from ht_ma_nsd a where a.ma_dvi=b_dvi and a.ma in(select c1 from temp_2) and upper(ten) like b_tim order by upper(ten))
      where sott between b_tu and b_den;
  end if;
end if;
select JSON_ARRAYAGG(json_object(ma,ten,'NHOM' value ma) returning clob) into dt_nhom from ht_ma_nhom where md=b_md order by ma;
select JSON_ARRAYAGG(json_object(ma,ten,'DVI' value ma) returning clob) into dt_dvi from ht_ma_dvi where b_dvi in(ma,ma_ct) order by ma;
select JSON_ARRAYAGG(json_object(*)) into dt_nv from ht_manv;
select json_object('dong' value b_dong,'vp' value b_vp,'dt_lke' value dt_lke,'dt_nv' value dt_nv,'dt_nhom' value dt_nhom,'dt_dvi' value dt_dvi returning clob) 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;