AAA

drop table ht_tso_hd;
create table ht_tso_hd
    (ma varchar2(10),
    ten nvarchar2(500),
    tgian number);
/
create or replace procedure FHT_TSO_HD_TG_NH
    (b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_oraIn clob)
AS
    b_loi varchar2(100); b_lenh varchar2(1000);
    dt_tg clob;
    tg_ma pht_type.a_var; tg_ten pht_type.a_nvar; tg_tgian pht_type.a_num;
begin
-- viet anh
b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'BH','MA','M');
if b_loi is not null then raise PROGRAM_ERROR; end if;
b_lenh:=FKH_JS_LENH('dt_tg');
EXECUTE IMMEDIATE b_lenh into dt_tg using b_oraIn;
b_lenh:=FKH_JS_LENH('ma,ten,tgian');
EXECUTE IMMEDIATE b_lenh bulk collect into tg_ma,tg_ten,tg_tgian using dt_tg;
if tg_ma.count<>0 then
    for b_lp in 1..tg_ma.count loop
        delete ht_tso_hd where ma=tg_ma(b_lp);
        insert into ht_tso_hd values(tg_ma(b_lp),tg_ten(b_lp),tg_tgian(b_lp));
    end loop;
end if;
commit;
exception when others then if b_loi is null then raise PROGRAM_ERROR; else raise_application_error(-20105,b_loi); end if;
end;
/
create or replace procedure FHT_TSO_HD_TG_LKE
    (b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_oraOut out clob)
AS
    b_loi varchar2(100); cs_lke clob:='';
begin
-- viet anh
b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'','','');
if b_loi is not null then raise PROGRAM_ERROR; end if;
select JSON_ARRAYAGG(json_object(ma,ten,tgian) order by ma) into cs_lke from ht_tso_hd;
select json_object('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;