AAA

CREATE OR REPLACE PROCEDURE PBH_PHH_LBH_CT
    (b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_oraIn clob,b_oraOut out clob)
AS
    b_loi varchar2(100); b_kq clob; b_ma varchar2(10):=FKH_JS_GTRIs(b_oraIn,'ma');
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;
select json_object(ma,txt) into b_kq from bh_phh_lbh where ma=b_ma;
select json_object('cs_ct' value b_kq) 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_PKT_LBH_CT
    (b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_oraIn clob,b_oraOut out clob)
AS
    b_loi varchar2(100); b_kq clob; b_ma varchar2(10):=FKH_JS_GTRIs(b_oraIn,'ma');
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;
select json_object(ma,txt) into b_kq from bh_pkt_lbh where ma=b_ma;
select json_object('cs_ct' value b_kq) 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_PHH_DTUONG_NHf
    (b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_oraIn clob)
AS
    b_loi varchar2(100); b_i1 number; b_lenh varchar2(1000);
    a_ma pht_type.a_var; a_ten pht_type.a_nvar; a_tc pht_type.a_var;
    a_ma_ct pht_type.a_var; a_nhom pht_type.a_var; a_ngay_bd pht_type.a_num; a_ngay_kt pht_type.a_num;
    b_txt clob:='"{}"';
begin
-- Dan
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('ma,ten,tc,ma_ct,nhom,ngay_bd,ngay_kt');
EXECUTE IMMEDIATE b_lenh bulk collect into a_ma,a_ten,a_tc,a_ma_ct,a_nhom,a_ngay_bd,a_ngay_kt using b_oraIn;
for b_lp in 1..a_ma.count loop
    if trim(a_ma(b_lp)) is null then b_loi:='loi:Nhap ma:loi'; raise PROGRAM_ERROR; end if;
    if trim(a_ten(b_lp)) is null then b_loi:='loi:Nhap ten:loi'; raise PROGRAM_ERROR; end if;
    if a_tc(b_lp) is null or a_tc(b_lp)<>'T' then a_tc(b_lp):='C'; end if;
    if trim(a_ma_ct(b_lp)) is null then
        a_ma_ct(b_lp):=' ';
    else
        b_loi:='loi:Sai ma cap tren:loi';
        if a_ma(b_lp)=a_ma_ct(b_lp) then raise PROGRAM_ERROR; end if;
        select 0 into b_i1 from bh_phh_dtuong where ma=a_ma_ct(b_lp) and tc='T';
    end if;
    if a_tc(b_lp)='T' then a_nhom(b_lp):=' ';
    elsif trim(a_nhom(b_lp)) is null or FBH_PHH_NHOM_HAN(a_nhom(b_lp))<>'C' then
        b_loi:='loi:Sai nhom:loi'; raise PROGRAM_ERROR;
    end if;
    if a_ngay_bd(b_lp) in(0,30000101) then a_ngay_bd(b_lp):=PKH_NG_CSO(sysdate); end if;
    a_ngay_kt(b_lp):=nvl(a_ngay_kt(b_lp),0);
    if a_ngay_kt(b_lp)=0 then a_ngay_kt(b_lp):=30000101; end if;
    b_loi:='';
    delete bh_phh_dtuong where ma=a_ma(b_lp);
    PKH_JS_THAYa(b_txt,'ma,ten,tc,ma_ct,nhom,ngay_bd,ngay_kt',a_ma(b_lp) || ',' || a_ten(b_lp) || ',' || a_tc(b_lp) || ',' || a_ma_ct(b_lp) || ',' || a_nhom(b_lp)
        || ',' || a_ngay_bd(b_lp) || ',' || a_ngay_kt(b_lp));
    insert into bh_phh_dtuong values(b_ma_dvi,a_ma(b_lp),a_ten(b_lp),a_tc(b_lp),a_ma_ct(b_lp),a_nhom(b_lp),a_ngay_bd(b_lp),a_ngay_kt(b_lp),b_nsd,'"'||b_txt||'"');
end loop;
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 PBH_PHH_PVI_NHf
    (b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_oraIn clob)
AS
    b_loi varchar2(100); b_i1 number; b_lenh varchar2(1000);
    a_ma pht_type.a_var; a_tc pht_type.a_var; a_ten pht_type.a_nvar; a_ngay_kt pht_type.a_num;
    a_loai pht_type.a_var; a_ma_ct pht_type.a_var; a_ma_dk pht_type.a_nvar; a_ma_qtac pht_type.a_var;
    a_ma_qtac_xl pht_type.a_var;a_ma_dk_xl pht_type.a_var;
    b_ten_qtac nvarchar2(500);b_ten_ma_qtac nvarchar2(500);b_ten_dk nvarchar2(500);b_ten_ma_dk nvarchar2(500);
    b_txt clob:='"{}"';
begin
-- Dan
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('ma,ten,tc,loai,ma_ct,ma_dk,ngay_kt');
EXECUTE IMMEDIATE b_lenh bulk collect into a_ma,a_ten,a_tc,a_loai,a_ma_ct,a_ma_dk,a_ngay_kt using b_oraIn;
for b_lp in 1..a_ma.count loop
  a_ma_qtac(b_lp):= FKH_JS_GTRIc(b_oraIn,'ma_qtac');
  select ten into b_ten_qtac from bh_ma_qtac where ma = a_ma_qtac(b_lp);
  b_ten_ma_qtac := a_ma_qtac(b_lp) || '|' || b_ten_qtac;
  -- viet anh
  select ten into b_ten_dk from bh_ma_dk where ma = a_ma_dk(b_lp);
  b_ten_ma_dk := a_ma_dk(b_lp) || '|' || b_ten_dk;
  
  a_ma_dk_xl(b_lp):=PKH_MA_TENl(b_ten_ma_dk);
  a_ma_qtac_xl(b_lp):=SUBSTR(b_ten_ma_qtac, 1, INSTR(b_ten_ma_qtac, '|') - 1);
  
  a_ma(b_lp):=nvl(trim(a_ma(b_lp)),' '); a_ten(b_lp):=nvl(trim(a_ten(b_lp)),' ');
  if a_ma(b_lp)=' ' or a_ten(b_lp)=' ' then b_loi:='loi:Nhap ma,ten:loi'; raise PROGRAM_ERROR; end if;
  a_tc(b_lp):=nvl(trim(a_tc(b_lp)),' '); a_loai(b_lp):=nvl(trim(a_loai(b_lp)),' ');
  if a_tc(b_lp) not in('C','D','B','M') then b_loi:='loi:Sai tinh chat:loi'; raise PROGRAM_ERROR; end if;
  if a_loai(b_lp) not in('C','T') then b_loi:='loi:Sai loai:loi'; raise PROGRAM_ERROR; end if;
  a_ma_ct(b_lp):=nvl(trim(a_ma_ct(b_lp)),' ');
  if a_ma_ct(b_lp)<>' ' then
    if a_ma_ct(b_lp)=a_ma(b_lp) then b_loi:='loi:Trung ma:loi'; raise PROGRAM_ERROR; end if;
    select count(*) into b_i1 from bh_phh_pvi where ma=a_ma_ct(b_lp);
    if b_i1=0 then  b_loi:='loi:Ma cap tren chua dang ky:loi'; raise PROGRAM_ERROR; end if;
  end if;
  a_ngay_kt(b_lp):=nvl(a_ngay_kt(b_lp),0);
  if a_ngay_kt(b_lp)=0 then a_ngay_kt(b_lp):=30000101; end if;
  b_loi:='';
  delete bh_phh_pvi where ma=a_ma(b_lp);
  PKH_JS_THAYa(b_txt,'ma,ten,tc,loai,ma_ct,ma_dk,ma_qtac,ngay_kt',a_ma(b_lp) || ',' || a_ten(b_lp) || ',' || a_tc(b_lp) || ',' || a_loai(b_lp) || ',' ||
  a_ma_ct(b_lp) || ',' || b_ten_ma_dk || ',' || b_ten_ma_qtac || ',' || a_ngay_kt(b_lp));
  insert into bh_phh_pvi values(b_ma_dvi,a_ma(b_lp),a_tc(b_lp),a_ten(b_lp),a_loai(b_lp),a_ma_ct(b_lp),a_ma_dk_xl(b_lp),a_ma_qtac_xl(b_lp),a_ngay_kt(b_lp),b_nsd,b_txt);
end loop;
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 PBH_PHH_LBH_NHf
    (b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_oraIn clob)
AS
    b_loi varchar2(100); b_i1 number; b_lenh varchar2(1000);
    a_ma pht_type.a_var; a_loai pht_type.a_var; a_ten pht_type.a_nvar;
    a_tc pht_type.a_var; a_ma_ct pht_type.a_var; a_ma_dk pht_type.a_var; a_ngay_kt pht_type.a_num;
    b_txt clob:='"{}"';
begin
-- Dan
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('ma,ten,tc,ma_ct,loai,ma_dk,ngay_kt');
EXECUTE IMMEDIATE b_lenh bulk collect into a_ma,a_ten,a_tc,a_ma_ct,a_loai,a_ma_dk,a_ngay_kt using b_oraIn;
for b_lp in 1..a_ma.count loop
    if trim(a_ma(b_lp)) is null then b_loi:='loi:Nhap ma:loi'; raise PROGRAM_ERROR; end if;
    if trim(a_ten(b_lp)) is null then b_loi:='loi:Nhap ten:loi'; raise PROGRAM_ERROR; end if;
    a_loai(b_lp):=nvl(trim(a_loai(b_lp)),' '); a_tc(b_lp):=nvl(trim(a_tc(b_lp)),'C'); a_ma_ct(b_lp):=nvl(trim(a_ma_ct(b_lp)),' ');
    if a_loai(b_lp) not in('TS','TB','HH','KH') then b_loi:='loi:Sai loai:loi'; raise PROGRAM_ERROR; end if;
    if a_ma_ct(b_lp)<>' ' then
        select count(*) into b_i1 from bh_phh_lbh where ma=a_ma_ct(b_lp);
        if b_i1=0 then b_loi:='loi:Sai ma bac cao:loi'; raise PROGRAM_ERROR; return; end if;
    end if;
    a_ma_dk(b_lp):=nvl(trim(a_ma_dk(b_lp)),' ');
    if a_ma_dk(b_lp)<>' ' and FBH_MA_DK_HAN(a_ma_dk(b_lp))='K' then
        b_loi:='loi:Dieu khoan da het su dung:loi'; raise PROGRAM_ERROR;
    end if;
    a_ngay_kt(b_lp):=nvl(a_ngay_kt(b_lp),0);
    if a_ngay_kt(b_lp)=0 then a_ngay_kt(b_lp):=30000101; end if;
    b_loi:='';
    delete bh_phh_lbh where ma=a_ma(b_lp);
    PKH_JS_THAYa(b_txt,'ma,ten,tc,ma_ct,loai,ma_dk,ngay_kt',a_ma(b_lp) || ',' || a_ten(b_lp) || ',' || a_tc(b_lp) || ',' || a_ma_ct(b_lp) || ',' || a_loai(b_lp)
        || ',' || a_ma_dk(b_lp) || ',' || a_ngay_kt(b_lp));
    insert into bh_phh_lbh values(b_ma_dvi,a_ma(b_lp),a_loai(b_lp),a_ten(b_lp),a_tc(b_lp),a_ma_ct(b_lp),a_ma_dk(b_lp),a_ngay_kt(b_lp),b_nsd,b_txt);
end loop;
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 PBH_PHH_NNTT_NHf
    (b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_oraIn clob)
AS
    b_loi varchar2(100); b_i1 number; b_lenh varchar2(1000);
    a_ma pht_type.a_var; a_ten pht_type.a_nvar; a_tc pht_type.a_var;
    a_ma_ct pht_type.a_var; a_ngay_kt pht_type.a_num;
    b_txt clob:='"{}"';
begin
-- Dan
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('ma,ten,tc,ma_ct,ngay_kt');
EXECUTE IMMEDIATE b_lenh bulk collect into a_ma,a_ten,a_tc,a_ma_ct,a_ngay_kt using b_oraIn;
for b_lp in 1..a_ma.count loop
    if trim(a_ma(b_lp)) is null then b_loi:='loi:Nhap ma:loi'; raise PROGRAM_ERROR; end if;
    if trim(a_ten(b_lp)) is null then b_loi:='loi:Nhap ten:loi'; raise PROGRAM_ERROR; end if;
    if a_tc(b_lp) is null or a_tc(b_lp)<>'T' then a_tc(b_lp):='C'; end if;
    if trim(a_ma_ct(b_lp)) is null then
        a_ma_ct(b_lp):=' ';
    else
        b_loi:='loi:Sai ma cap tren:loi';
        if a_ma(b_lp)=a_ma_ct(b_lp) then raise PROGRAM_ERROR; end if;
        select 0 into b_i1 from bh_phh_nntt where ma=a_ma_ct(b_lp) and tc='T';
    end if;
    a_ngay_kt(b_lp):=nvl(a_ngay_kt(b_lp),0);
    if a_ngay_kt(b_lp)=0 then a_ngay_kt(b_lp):=30000101; end if;
    b_loi:='';
    delete bh_phh_nntt where ma=a_ma(b_lp);
    PKH_JS_THAYa(b_txt,'ma,ten,tc,ma_ct,ngay_kt',a_ma(b_lp) || ',' || a_ten(b_lp) || ',' || a_tc(b_lp) || ',' || a_ma_ct(b_lp) || ',' || a_ngay_kt(b_lp));
    insert into bh_phh_nntt values(b_ma_dvi,a_ma(b_lp),a_ten(b_lp),a_tc(b_lp),a_ma_ct(b_lp),a_ngay_kt(b_lp),b_nsd,'"'||b_txt||'"');
end loop;
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 PBH_PKT_NNTT_NHf
    (b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_oraIn clob)
AS
    b_loi varchar2(100); b_i1 number; b_lenh varchar2(1000);
    a_ma pht_type.a_var; a_ten pht_type.a_nvar; a_tc pht_type.a_var;
    a_ma_ct pht_type.a_var; a_ngay_kt pht_type.a_num;
    b_txt clob:='"{}"';
begin
-- Dan
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('ma,ten,tc,ma_ct,ngay_kt');
EXECUTE IMMEDIATE b_lenh bulk collect into a_ma,a_ten,a_tc,a_ma_ct,a_ngay_kt using b_oraIn;
for b_lp in 1..a_ma.count loop
    if trim(a_ma(b_lp)) is null then b_loi:='loi:Nhap ma:loi'; raise PROGRAM_ERROR; end if;
    if trim(a_ten(b_lp)) is null then b_loi:='loi:Nhap ten:loi'; raise PROGRAM_ERROR; end if;
    if a_tc(b_lp) is null or a_tc(b_lp)<>'T' then a_tc(b_lp):='C'; end if;
    if trim(a_ma_ct(b_lp)) is null then
        a_ma_ct(b_lp):=' ';
    else
        b_loi:='loi:Sai ma cap tren:loi';
        if a_ma(b_lp)=a_ma_ct(b_lp) then raise PROGRAM_ERROR; end if;
        select 0 into b_i1 from bh_pkt_nntt where ma=a_ma_ct(b_lp) and tc='T';
    end if;
    a_ngay_kt(b_lp):=nvl(a_ngay_kt(b_lp),0);
    if a_ngay_kt(b_lp)=0 then a_ngay_kt(b_lp):=30000101; end if;
    b_loi:='';
    delete bh_pkt_nntt where ma=a_ma(b_lp);
    PKH_JS_THAYa(b_txt,'ma,ten,tc,ma_ct,ngay_kt',a_ma(b_lp) || ',' || a_ten(b_lp) || ',' || a_tc(b_lp) || ',' || a_ma_ct(b_lp) || ',' || a_ngay_kt(b_lp));
    insert into bh_pkt_nntt values(b_ma_dvi,a_ma(b_lp),a_ten(b_lp),a_tc(b_lp),a_ma_ct(b_lp),a_ngay_kt(b_lp),b_nsd,'"'||b_txt||'"');
end loop;
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 PBH_PKT_PVI_NHf
    (b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_oraIn clob)
AS
    b_loi varchar2(100); b_i1 number; b_lenh varchar2(1000);
    a_ma pht_type.a_var; a_tc pht_type.a_var; a_ten pht_type.a_nvar; a_ngay_kt pht_type.a_num;
    b_txt clob:='"{}"';
begin
-- Dan
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('ma,ten,tc,ngay_kt');
EXECUTE IMMEDIATE b_lenh bulk collect into a_ma,a_ten,a_tc,a_ngay_kt using b_oraIn;
for b_lp in 1..a_ma.count loop
    if trim(a_ma(b_lp)) is null then b_loi:='loi:Nhap ma:loi'; raise PROGRAM_ERROR; end if;
    if trim(a_ten(b_lp)) is null then b_loi:='loi:Nhap ten:loi'; raise PROGRAM_ERROR; end if;
    a_tc(b_lp):=nvl(trim(a_tc(b_lp)),' ');
    if a_tc(b_lp) not in('C','D','B','M') then b_loi:='loi:Sai tinh chat:loi'; raise PROGRAM_ERROR; end if;
    a_ngay_kt(b_lp):=nvl(a_ngay_kt(b_lp),0);
    if a_ngay_kt(b_lp)=0 then a_ngay_kt(b_lp):=30000101; end if;
    b_loi:='';
    delete bh_pkt_pvi where ma=a_ma(b_lp);
    PKH_JS_THAYa(b_txt,'ma,ten,tc,ngay_kt',a_ma(b_lp) || ',' || a_ten(b_lp) || ',' || a_tc(b_lp) || ',' || a_ngay_kt(b_lp));
    insert into bh_pkt_pvi values(b_ma_dvi,a_ma(b_lp),a_tc(b_lp),a_ten(b_lp),a_ngay_kt(b_lp),b_nsd,b_txt);
end loop;
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 PBH_PKT_MA_NCT_NHf
    (b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_oraIn clob)
AS
    b_loi varchar2(100); b_i1 number; b_lenh varchar2(1000);
    a_ma pht_type.a_var; a_ten pht_type.a_nvar; a_tc pht_type.a_var;
    a_ma_ct pht_type.a_var; a_ngay_kt pht_type.a_num;
    b_txt clob:='"{}"';
begin
-- Dan
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('ma,ten,tc,ma_ct,ngay_kt');
EXECUTE IMMEDIATE b_lenh bulk collect into a_ma,a_ten,a_tc,a_ma_ct,a_ngay_kt using b_oraIn;
for b_lp in 1..a_ma.count loop
    if trim(a_ma(b_lp)) is null then b_loi:='loi:Nhap ma:loi'; raise PROGRAM_ERROR; end if;
    if trim(a_ten(b_lp)) is null then b_loi:='loi:Nhap ten:loi'; raise PROGRAM_ERROR; end if;
    if a_tc(b_lp) is null or a_tc(b_lp)<>'T' then a_tc(b_lp):='C'; end if;
    if trim(a_ma_ct(b_lp)) is null then
        a_ma_ct(b_lp):=' ';
    else
        b_loi:='loi:Sai ma cap tren:loi';
        if a_ma(b_lp)=a_ma_ct(b_lp) then raise PROGRAM_ERROR; end if;
        select 0 into b_i1 from bh_pkt_ma_nct where ma=a_ma_ct(b_lp) and tc='T';
    end if;
    a_ngay_kt(b_lp):=nvl(a_ngay_kt(b_lp),0);
    if a_ngay_kt(b_lp)=0 then a_ngay_kt(b_lp):=30000101; end if;
    b_loi:='';
    delete bh_pkt_ma_nct where ma=a_ma(b_lp);
    PKH_JS_THAYa(b_txt,'ma,ten,tc,ma_ct,ngay_kt',a_ma(b_lp) || ',' || a_ten(b_lp) || ',' || a_tc(b_lp) || ',' || a_ma_ct(b_lp) || ',' || a_ngay_kt(b_lp));
    insert into bh_pkt_ma_nct values(b_ma_dvi,a_ma(b_lp),a_ten(b_lp),a_tc(b_lp),a_ma_ct(b_lp),a_ngay_kt(b_lp),b_nsd,'"'||b_txt||'"');
end loop;
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 PBH_PKT_LBH_NHf
    (b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_oraIn clob)
AS
    b_loi varchar2(100); b_i1 number; b_lenh varchar2(1000);
    a_ma pht_type.a_var; a_loai pht_type.a_var; a_ten pht_type.a_nvar;b_ma_ct varchar2(10); a_ngay_kt pht_type.a_num;
    a_ten_e pht_type.a_nvar;
    b_txt clob:='"{}"';
begin
-- Dan
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('ma,ten,ten_e,loai,ngay_kt');
EXECUTE IMMEDIATE b_lenh bulk collect into a_ma,a_ten,a_ten_e,a_loai,a_ngay_kt using b_oraIn;
for b_lp in 1..a_ma.count loop
  if trim(a_ma(b_lp)) is null then b_loi:='loi:Nhap ma:loi'; raise PROGRAM_ERROR; end if;
  if trim(a_ten(b_lp)) is null then b_loi:='loi:Nhap ten:loi'; raise PROGRAM_ERROR; end if;
  a_loai(b_lp):=nvl(trim(a_loai(b_lp)),' '); b_ma_ct:=nvl(trim(b_ma_ct),' ');
  if a_loai(b_lp) not in('TS','KH') then b_loi:='loi:Sai loai:loi'; raise PROGRAM_ERROR; end if;
  if b_ma_ct<>' ' then
    select count(*) into b_i1 from bh_pkt_lbh where ma=a_ma(b_lp);
    if b_i1=0 then b_loi:='loi:Sai ma bac cao:loi'; raise PROGRAM_ERROR; return; end if;
  end if;
  a_ngay_kt(b_lp):=nvl(a_ngay_kt(b_lp),0);
  if a_ngay_kt(b_lp)=0 then a_ngay_kt(b_lp):=30000101; end if;
  b_loi:='';
  delete bh_pkt_lbh where ma=a_ma(b_lp);
  PKH_JS_THAYa(b_txt,'ma,ten,ten_e,loai,ngay_kt',a_ma(b_lp) || ',' || a_ten(b_lp) || ',' || a_ten_e(b_lp) || ',' || a_loai(b_lp) || ',' || a_ngay_kt(b_lp));
  insert into bh_pkt_lbh values(b_ma_dvi,a_ma(b_lp),a_loai(b_lp),a_ten(b_lp),b_ma_ct,a_ngay_kt(b_lp),b_nsd,b_txt);
end loop;
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 PBH_PTN_NNTT_NHf
    (b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_oraIn clob)
AS
    b_loi varchar2(100); b_i1 number; b_lenh varchar2(1000);
    a_ma pht_type.a_var; a_ten pht_type.a_nvar; a_tc pht_type.a_var;
    a_ma_ct pht_type.a_var; a_ngay_kt pht_type.a_num;
    b_txt clob:='"{}"';
begin
-- Dan
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('ma,ten,tc,ma_ct,ngay_kt');
EXECUTE IMMEDIATE b_lenh bulk collect into a_ma,a_ten,a_tc,a_ma_ct,a_ngay_kt using b_oraIn;
for b_lp in 1..a_ma.count loop
    if trim(a_ma(b_lp)) is null then b_loi:='loi:Nhap ma:loi'; raise PROGRAM_ERROR; end if;
    if trim(a_ten(b_lp)) is null then b_loi:='loi:Nhap ten:loi'; raise PROGRAM_ERROR; end if;
    if a_tc(b_lp) is null or a_tc(b_lp)<>'T' then a_tc(b_lp):='C'; end if;
    if trim(a_ma_ct(b_lp)) is null then
        a_ma_ct(b_lp):=' ';
    else
        b_loi:='loi:Sai ma cap tren:loi';
        if a_ma(b_lp)=a_ma_ct(b_lp) then raise PROGRAM_ERROR; end if;
        select 0 into b_i1 from bh_ptn_nntt where ma=a_ma_ct(b_lp) and tc='T';
    end if;
    a_ngay_kt(b_lp):=nvl(a_ngay_kt(b_lp),0);
    if a_ngay_kt(b_lp)=0 then a_ngay_kt(b_lp):=30000101; end if;
    b_loi:='';
    delete bh_ptn_nntt where ma=a_ma(b_lp);
    PKH_JS_THAYa(b_txt,'ma,ten,tc,ma_ct,ngay_kt',a_ma(b_lp) || ',' || a_ten(b_lp) || ',' || a_tc(b_lp) || ',' || a_ma_ct(b_lp) || ',' || a_ngay_kt(b_lp));
    insert into bh_ptn_nntt values(b_ma_dvi,a_ma(b_lp),a_ten(b_lp),a_tc(b_lp),a_ma_ct(b_lp),a_ngay_kt(b_lp),b_nsd,'"'||b_txt||'"');
end loop;
commit;
exception when others then if b_loi is null then raise PROGRAM_ERROR; else raise_application_error(-20105,b_loi); end if;
end;