AAA

create or replace procedure PBH_HD_DU_GOC(
    b_ma_dviN varchar2,b_nsdN varchar2,b_pas varchar2,b_oraIn clob,b_oraOut out clob)
AS
    b_loi varchar2(100); b_lenh varchar2(2000); b_i1 number;
    b_ma_dvi varchar2(10); b_so_id number; b_nv varchar2(10); b_nvG varchar2(10);
Begin
b_loi:=FHT_MA_NSD_KTRA(b_ma_dviN,b_nsdN,b_pas,'BH','','');
if b_loi is not null then raise PROGRAM_ERROR; end if;
b_loi:='loi:Loi xu ly PBH_HD_DU_GOC:loi';
b_lenh:=FKH_JS_LENH('ma_dvi,so_id');
EXECUTE IMMEDIATE b_lenh into b_ma_dvi,b_so_id using b_oraIn;
-- viet anh
b_nv:=FBH_HD_NV(b_ma_dvi,b_so_id);
if b_nv is null then b_loi:='loi:Hop dong da xoa:loi'; raise PROGRAM_ERROR; end if;
if b_nv<>'HANG' then
  b_lenh:='select nv from '||FBH_HD_GOC_BANG(b_nv)||' where ma_dvi= :ma_dvi and so_id= :so_id';
  EXECUTE IMMEDIATE b_lenh into b_nvG using b_ma_dvi,b_so_id;
  b_nv:=b_nv||b_nvG;
end if;
select json_object('nv' value b_nv) 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;
/
create or replace procedure PBH_BG_DU_GOC(
    b_ma_dviN varchar2,b_nsdN varchar2,b_pas varchar2,b_oraIn clob,b_oraOut out clob)
AS
    b_loi varchar2(100); b_lenh varchar2(2000); b_i1 number;
    b_ma_dvi varchar2(10); b_so_id number; b_nv varchar2(10); b_nvG varchar2(10);
Begin
b_loi:=FHT_MA_NSD_KTRA(b_ma_dviN,b_nsdN,b_pas,'BH','','');
if b_loi is not null then raise PROGRAM_ERROR; end if;
b_loi:='loi:Loi xu ly PBH_BG_DU_GOC:loi';
b_lenh:=FKH_JS_LENH('ma_dvi,so_id');
EXECUTE IMMEDIATE b_lenh into b_ma_dvi,b_so_id using b_oraIn;
select nvl(min(nv),' ') into b_nv from bh_bao where ma_dvi=b_ma_dvi and so_id=b_so_id;
if b_nv=' ' then b_loi:='loi:Bao gia da xoa:loi'; raise PROGRAM_ERROR; end if;
-- viet anh -- BG hang khong co cot nv
if b_nv<>'HANG' then
  if b_nv<>'NG' then
    b_lenh:='select nv from '||FBH_BAO_BANG(b_nv)||' where ma_dvi= :ma_dvi and so_id= :so_id';
  else
    b_lenh:='select nhom||nv from '||FBH_BAO_BANG(b_nv)||' where ma_dvi= :ma_dvi and so_id= :so_id';
  end if;
  EXECUTE IMMEDIATE b_lenh into b_nvG using b_ma_dvi,b_so_id;    
  if b_nv='NG' then 
    b_nv:=b_nvG;
  else 
    b_nv:=b_nv||b_nvG; 
  end if;
end if;
select json_object('nv' value b_nv) 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;