AAA

create or replace procedure PHD_MA_HDJ_NH
    (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); b_vp varchar2(1);
    b_ma varchar2(20);b_mau varchar2(20);
    b_nhom varchar2(10);b_nv varchar2(20);b_do_dai number;b_ten nvarchar2(50);b_lien number;b_to number;
begin
-- viet anh - Nhap ma hoa don
b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'HD','MA','N');
if b_loi is not null then raise PROGRAM_ERROR; end if;
b_lenh:=FKH_JS_LENH('ma,mau,ma_nhom,nv,ten,do_dai,so_lien,so_to');
EXECUTE IMMEDIATE b_lenh into b_ma,b_mau,b_nhom,b_nv,b_ten,b_do_dai,b_lien,b_to using b_oraIn;
if b_ma is null then b_loi:='loi:Nhap ma hoa don, an chi:loi'; raise PROGRAM_ERROR; end if;
b_loi:='loi:Sai nhom hoa don, an chi:loi';
if b_nhom is null then raise PROGRAM_ERROR;
else select 0 into b_i1 from hd_ma_nhom where ma_dvi=b_ma_dvi and ma=b_nhom;
end if;
if b_do_dai is null or b_do_dai=0 then
    b_loi:='loi:Nhap do dai phan ky hieu va so:loi'; raise PROGRAM_ERROR;
end if;
b_loi:='loi:Chon nghiep vu:loi';
if b_nv is null then raise PROGRAM_ERROR; end if;
select vp into b_vp from ht_ma_dvi where ma=b_ma_dvi;
select count(*) into b_i1 from hd_ma_hd where ma_dvi=b_ma_dvi and ma=b_ma;
if b_vp='C' then
     delete hd_ma_hd where ma_dvi=b_ma_dvi and ma=b_ma and nv=b_nv;
     --insert into hd_ma_hd select ma,b_ma,b_mau,b_nhom,b_nv,b_ten,b_do_dai,b_lien,b_to,b_nsd from ht_ma_dvi;
     insert into hd_ma_hd values(b_ma_dvi,b_ma,b_mau,b_nhom,b_nv,b_ten,b_do_dai,b_lien,b_to,b_nsd);
else
    delete hd_ma_hd where ma_dvi=b_ma_dvi and ma=b_ma and mau=b_mau and nv=b_nv;
    insert into hd_ma_hd values(b_ma_dvi,b_ma,b_mau,b_nhom,b_nv,b_ten,b_do_dai,b_lien,b_to,b_nsd);
end if;
/*if b_i1>0 then
    if b_vp='C' then
        update hd_ma_hd set mau=b_mau,ma_nhom=b_nhom,ten=b_ten,do_dai=b_do_dai,so_lien=b_lien,
            so_to=b_to,nsd=b_nsd where ma=b_ma;
    else
        update hd_ma_hd set mau=b_mau,ma_nhom=b_nhom,ten=b_ten,do_dai=b_do_dai,so_lien=b_lien,
            so_to=b_to,nsd=b_nsd where ma_dvi=b_ma_dvi and ma=b_ma;
    end if;
else
    if b_vp='C' then
        insert into hd_ma_hd select ma,b_ma,b_mau,b_nhom,b_ten,b_do_dai,b_lien,b_to,b_nsd from ht_ma_dvi;
    else insert into hd_ma_hd values(b_ma_dvi,b_ma,b_mau,b_nhom,b_ten,b_do_dai,b_lien,b_to,b_nsd);
    end if;
end if;*/
commit;
exception when others then rollback; if b_loi is null then raise PROGRAM_ERROR; else raise_application_error(-20105,b_loi); end if;
end;
/
create or replace procedure PKH_MA_HANJ_NH
    (b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_oraIn clob)
AS
    b_loi varchar2(200); b_i1 number; b_ma_ct varchar2(10); b_idvung number;  b_lenh varchar2(1000);
    b_ngay_log date:=sysdate; b_ma_nsd varchar2(20):=b_nsd; b_nv_bh varchar2(20); b_ngay_d number;
    b_md varchar2(10):='BH';b_ma_cd varchar2(10);b_nv varchar2(10);b_ngay number;
begin
-- Dan - Nhap han thay doi so lieu
PHT_MA_NSD_KTRA_VU(b_ma_dvi,b_nsd,b_pas,b_idvung,b_loi,'HT','HT','');
if b_loi is not null then raise PROGRAM_ERROR; end if;
b_lenh:=FKH_JS_LENH('ma_cd,nv,ngay');
EXECUTE IMMEDIATE b_lenh into b_ma_cd,b_nv,b_ngay using b_oraIn;
if trim(b_ma_nsd) is not null then
    if b_md<>'HD' then
        if instr(b_ma_nsd,'$A$')=0 then
            select count(*) into b_i1 from ht_ma_nsd where ma_dvi=b_ma_dvi and ma=b_ma_nsd;
            if b_i1=0 then b_loi:='loi:Ma NSD '||b_ma_nsd||' chua khai bao:loi'; raise PROGRAM_ERROR; end if;
            if b_ma_cd='AL' then
                b_loi:='loi:Phai chon don vi va nghiep vu:loi';raise PROGRAM_ERROR;
            end if;
        else
            select count(*) into b_i1 from hta_ma_nsd where ma=substr(b_ma_nsd,4);
            if b_i1=0 then b_loi:='loi:Ma NSD '||b_ma_nsd||' chua khai bao:loi'; raise PROGRAM_ERROR; end if;
            if b_ma_cd='AL' then
                b_loi:='loi:Phai chon don vi va nghiep vu:loi';raise PROGRAM_ERROR;
            end if;
        end if;
    else
        select count(*) into b_i1 from ht_ma_nsd where ma_dvi=b_ma_dvi and ma=b_ma_nsd;
        if b_i1=0 then b_loi:='loi:Ma NSD '||b_ma_nsd||' chua khai bao:loi'; raise PROGRAM_ERROR; end if;
        if b_ma_cd='AL' then
            b_loi:='loi:Phai chon don vi va nghiep vu:loi';raise PROGRAM_ERROR;
        end if;
    end if;
else
    b_ma_nsd:=' ';
end if;

b_loi:='loi:Sai don vi:loi';
if b_ma_cd is null then raise PROGRAM_ERROR; end if;
b_loi:='loi:Sai loai nghiep vu '||b_nv||':loi';
if b_nv is null then raise PROGRAM_ERROR; end if;
if b_nv<>'AL' then
    if b_nv like 'KT_%' then b_nv_bh:=substr(b_nv,instr(b_nv,'_')+1); else b_nv_bh:=b_nv; end if;
    PHT_MA_NSD_KTRA_VU(b_ma_dvi,b_nsd,b_pas,b_idvung,b_loi,b_md,b_nv_bh,'H');
else
    PHT_MA_NSD_KTRA_VU(b_ma_dvi,b_nsd,b_pas,b_idvung,b_loi,b_md,'MA','H');
end if;
if b_loi is not null then raise PROGRAM_ERROR; end if;

if b_ma_cd not in(b_ma_dvi,'AL') then
    b_loi:='loi:Khong phai don vi truc thuoc:loi';
    select ma_ct into b_ma_ct from ht_ma_dvi where ma=b_ma_cd;
    --if b_ma_ct is null or b_ma_ct<>b_ma_dvi then raise PROGRAM_ERROR; end if;
end if;
select ma_ct into b_ma_ct from ht_ma_dvi where ma=b_ma_dvi;
b_ngay_d:=b_ngay;
if b_ma_nsd=' ' or b_nsd in ('HH','XCG','TSKT','CN') then
    if trim(b_ma_ct) is not null then
        select count(*) into b_i1 from kh_ma_han where ma_dvi=b_ma_dvi and md=b_md
            and ma_cd=b_ma_ct and nv in('AL',b_nv) and ngay>b_ngay_d;
        if b_i1<>0 then b_loi:='loi:Vuot han cap tren:loi'; raise PROGRAM_ERROR; end if;
    end if;
end if;
if b_nv<>'AL' then
    if b_ma_nsd=' ' then
        if b_ma_cd='AL' then
            select count(*) into b_i1 from kh_ma_han where ma_dvi=b_ma_dvi and md=b_md and nv='AL' and ngay>=b_ngay_d;
        else
            select count(*) into b_i1 from kh_ma_han where ma_dvi=b_ma_dvi and md=b_md and ma_cd=b_ma_cd and nv='AL' and ngay>=b_ngay_d;
        end if;
        if b_i1<>0 and b_nv<>'NSLD' then b_loi:='loi:Vuot han tat ca:loi'; raise PROGRAM_ERROR; end if;
    end if;
end if;
b_loi:='loi:Loi Table kh_ma_han:loi';
if b_ma_cd='AL' then
    delete kh_ma_han where ma_dvi=b_ma_dvi and md=b_md and (nv=b_nv or (b_nv='AL' and ngay<b_ngay_d))
        and ma_cd in(select ma from ht_ma_dvi where b_ma_dvi in (ma,ma_ct));
    delete kh_ma_han where ma_dvi in(select ma from ht_ma_dvi where ma_ct=b_ma_dvi)
        and md=b_md and ma_cd=b_ma_dvi and (nv=b_nv or (b_nv='AL' and ngay<b_ngay_d));
    insert into kh_ma_han select b_ma_dvi,b_md,ma,b_nv,' ',b_ngay_d,'',sysdate,b_nsd,b_idvung from ht_ma_dvi where b_ma_dvi in (ma,ma_ct);
    insert into kh_ma_han select ma,b_md,b_ma_dvi,b_nv,' ',b_ngay_d,'',sysdate,b_nsd,b_idvung from ht_ma_dvi where ma_ct=b_ma_dvi;

    insert into kh_ma_han_log select b_ngay_log,b_ma_dvi,b_md,ma,b_nv,b_ma_nsd,b_ngay_d,'',sysdate,b_nsd,b_idvung from ht_ma_dvi where b_ma_dvi in (ma,ma_ct);
    insert into kh_ma_han_log select b_ngay_log,ma,b_md,b_ma_dvi,b_nv,b_ma_nsd,b_ngay_d,'',sysdate,b_nsd,b_idvung from ht_ma_dvi where ma_ct=b_ma_dvi;
else
    if b_nv='AL' then
        delete kh_ma_han where ma_dvi=b_ma_dvi and md=b_md and ma_cd=b_ma_cd and ma_nsd=b_ma_nsd and nv=b_nv;
    else
        if b_nsd<>'TIN' then
            delete kh_ma_han where ma_dvi=b_ma_dvi and nsd=b_nsd and md=b_md and ma_cd=b_ma_cd and ma_nsd=b_ma_nsd and (nv=b_nv or (b_nv='AL' and ngay<b_ngay_d));
        else
            delete kh_ma_han where ma_dvi=b_ma_dvi and md=b_md and ma_cd=b_ma_cd and ma_nsd=b_ma_nsd and (nv=b_nv or (b_nv='AL' and ngay<b_ngay_d));
        end if;
    end if;
    insert into kh_ma_han values (b_ma_dvi,b_md,b_ma_cd,b_nv,b_ma_nsd,b_ngay_d,'',sysdate,b_nsd,b_idvung);
    insert into kh_ma_han_log values (b_ngay_log,b_ma_dvi,b_md,b_ma_cd,b_nv,b_ma_nsd,b_ngay_d,'',sysdate,b_nsd,b_idvung);
end if;
commit;
exception when others then rollback; if b_loi is null then raise PROGRAM_ERROR; else raise_application_error(-20105,b_loi); end if;
end;
/
create or replace procedure PBH_TAU_BPHI_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); cs_lke clob; b_dong number;
    b_nhom varchar2(200); b_loai varchar2(200); b_md_sd varchar2(1); b_nv_bh varchar2(1);
    b_tu number; b_den number;
begin
-- Dan
b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'BH','TAU','');
if b_loi is not null then raise PROGRAM_ERROR; end if;
b_lenh:=FKH_JS_LENH('nhom,loai,md_sd,nv_bh,tu,den');
EXECUTE IMMEDIATE b_lenh into b_nhom,b_loai,b_md_sd,b_nv_bh,b_tu,b_den using b_oraIn;
b_nhom:=PKH_MA_TENl(b_nhom); b_loai:=PKH_MA_TENl(b_loai);
b_nhom:=nvl(trim(b_nhom),' '); b_loai:=nvl(trim(b_loai),' '); b_md_sd:=nvl(trim(b_md_sd),' '); b_nv_bh:=nvl(trim(b_nv_bh),' '); 
select count(*) into b_dong from bh_tau_phi where b_nhom in(' ',nhom) and b_loai in(' ',loai) and b_md_sd in(' ',md_sd) and b_nv_bh in(' ',nv_bh);
PKH_LKE_TRANG(b_dong,b_tu,b_den);
select JSON_ARRAYAGG(json_object(so_id,
    'nhom' value FBH_TAU_NHOM_TEN(nhom),'loai' value FBH_TAU_LOAI_TEN(loai),
    'cap' value FBH_TAU_CAP_TEN(cap),'vlieu' value FBH_TAU_VLIEU_TEN(vlieu),
    ttai,so_cn,dtich,csuat,gia,tuoi,'ma_sp' value FBH_TAU_SP_TEN(ma_sp),
    'dkien' value FBH_TAU_DKC_TEN(dkien),'md_sd' value FBH_TAU_MDSD_TEN(md_sd),
    'nv_bh' value decode(nv_bh,'V','Vat chat','T','Trach nhiem','N','Tai nan thuyen vien','Mo rong'))
    returning clob) into cs_lke from 
    (select nhom,loai,cap,vlieu,ttai,so_cn,dtich,csuat,gia,tuoi,
    ma_sp,dkien,md_sd,nv_bh,so_id,rownum sott from bh_tau_phi
    where b_nhom in(' ',nhom) and b_loai in(' ',loai) and b_md_sd in(' ',md_sd) and b_nv_bh in(' ',nv_bh)
    order by nhom,loai,cap,vlieu,ttai,so_cn,dtich,csuat,gia,tuoi,ma_sp,dkien,md_sd,nv_bh)
    where sott between b_tu and b_den;
select json_object('dong' value b_dong,'cs_lke' value cs_lke 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;
/
create or replace noneditionable procedure PBH_TAU_BPHId_THEM(
    b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_oraIn clob,b_oraOut out clob)
AS
    b_loi varchar2(1000); b_lenh varchar2(1000); b_nv varchar2(10); b_so_idS varchar2(500); b_pphiS varchar2(500);
    b_ngay number:=PKH_NG_CSO(sysdate); b_tgT number:=1; b_tgP number:=1; b_i1 number;
    b_vu varchar2(10); b_nt_tien varchar2(5); b_nt_phi varchar2(5); b_dvi_ta varchar2(10):=FTBH_DVI_TA();
    b_dk_lt clob; a_ma_lt pht_type.a_var; a_ma_dk_lt pht_type.a_var; a_ten_lt pht_type.a_nvar; a_chon_lt pht_type.a_var; a_so_idS pht_type.a_num;
begin
-- Dan - Tra so ID
delete temp_1; commit;
b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'BH','TAU','');
if b_loi is not null then raise PROGRAM_ERROR; end if;
b_lenh:=FKH_JS_LENH('vu,nt_tien,nt_phi');
EXECUTE IMMEDIATE b_lenh into b_vu,b_nt_tien,b_nt_phi using b_oraIn;
if b_nt_tien<>'VND' then b_tgT:=FTT_TRA_TGTT(b_dvi_ta,b_ngay,b_nt_tien); end if;
if b_nt_phi<>'VND' then b_tgP:=FTT_TRA_TGTT(b_dvi_ta,b_ngay,b_nt_phi); end if;
if b_vu='dkbs' then
    FBH_TAU_BPHI_CTbs(b_oraIn,b_nv,b_so_idS,b_pphiS,b_loi);
    insert into temp_1(c1,c2,n1,c3) select ma,ten,pt,lh_bh from bh_tau_phi_dk where so_id=b_so_idS;
    for r_lp in (select ma,ten,lh_nv from bh_ma_dkbs where FBH_MA_NV_CO(nv,'TAU')='C') loop
        select count(*) into b_i1 from temp_1 where c1=r_lp.ma;
        if b_i1=0 then insert into temp_1(c1,c2,c3,n1) values(r_lp.ma,r_lp.ten,r_lp.lh_nv,0); end if;
    end loop;
    select JSON_ARRAYAGG(json_object('ma' value c1,'ten' value c2,'ptB' value n1,'pt' value 0,'lh_nv' value c3,'lkep' value 'G')
        order by c1,c2 returning clob) into b_oraOut from temp_1;
elsif b_vu='dk' then
    FBH_TAU_BPHI_CTs(b_oraIn,b_nv,b_so_idS,b_pphiS,b_loi);
    select JSON_ARRAYAGG(json_object('nv' value b_nv,ma,ten,cap,tc,ma_ct,ma_dk,
    kieu,lh_nv,t_suat,lkeP,lkeB,luy,gvu,'pt' value '',
    'tien' value case when b_tgT=1 then tien else round(tien/b_tgT,0) end,
    'ptB' value case when pt<100 then pt else round(pt/b_tgP,2) end)returning clob) into b_oraOut
    from bh_tau_phi_dk where so_id=b_so_idS;
elsif b_vu='lt' then
    FBH_TAU_BPHI_CTs(b_oraIn,b_nv,b_so_idS,b_pphiS,b_loi);
    PKH_CH_ARR_N(b_so_idS,a_so_idS,b_loi);
    for b_lp in 1..a_so_idS.count loop
        select count(*) into b_i1 from bh_tau_phi_txt where so_id =a_so_idS(b_lp) and loai='dt_lt';
        if b_i1 >0 then
           select txt into b_dk_lt from bh_tau_phi_txt where so_id=b_so_idS and loai='dt_lt';
           b_lenh:=FKH_JS_LENH('ma_lt,ma_dk,ten,chon');
           EXECUTE IMMEDIATE b_lenh bulk collect into a_ma_lt,a_ma_dk_lt,a_ten_lt,a_chon_lt using b_dk_lt;
           if a_ma_lt.count > 0 then
             for b_i1 in 1..a_ma_lt.count loop
                  insert into temp_1(c1,c2,c3,c4) VALUES (a_ma_lt(b_i1),a_ma_dk_lt(b_i1),a_ten_lt(b_i1),a_chon_lt(b_i1));
             end loop;
           end if;
        end if;
    end loop;
    --select count(*) into b_i1 from bh_tau_phi_txt where so_id in (a_so_idS) and loai='dt_lt';

    
    for r_lp in (select ma,ten,ma_dk from bh_ma_dklt where FBH_MA_NV_CO(nv,'TAU')='C') loop
      select count(*) into b_i1 from temp_1 where c1=r_lp.ma;
      if b_i1=0 then insert into temp_1(c1,c2,c3,c4) values(r_lp.ma,r_lp.ten,' ',r_lp.ma_dk); end if;
    end loop;
    select JSON_ARRAYAGG(json_object('ma_lt' value c1,'ten' value c2,'ma_dk' value c3)
        order by c1,c2 returning clob) into b_oraOut from temp_1;
end if;
if b_loi is not null then raise PROGRAM_ERROR; end if;

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_TAUG_TESTr(
    b_ma_dvi varchar2,b_nsd varchar2,b_so_idD number,dt_ct clob,dt_dk clob,dt_dkbs clob,
    b_so_hd in out varchar2,
    b_tenC out nvarchar2,b_cmtC out varchar2,b_mobiC out varchar2,
    b_emailC out varchar2,b_dchiC out nvarchar2,b_ng_huong out nvarchar2,

    b_nhom out varchar2,b_loai out varchar2,b_cap out varchar2,b_vlieu out varchar2,
    b_ttai out number,b_so_cn out number,b_dtich out number,b_csuat out number,b_gia out number,b_tuoi out number,
    b_ma_sp out varchar2,b_dkien out varchar2,b_md_sd out varchar2,b_nv_bh out varchar2,
    b_so_dk out varchar2,b_ten_tau out nvarchar2,b_nam_sx out number,
    b_hoi out varchar2,b_hoi_tien out number,b_hoi_tyle out number,b_hoi_hh out number,
    b_tl_mgiu out number,b_tau_id out number,b_tb out varchar2,

    dk_bt out pht_type.a_num,dk_ma out pht_type.a_var,dk_ten out pht_type.a_nvar,
    dk_tc out pht_type.a_var,dk_ma_ct out pht_type.a_var,dk_kieu out pht_type.a_var,
    dk_tien out pht_type.a_num,dk_pt out pht_type.a_num,dk_phi out pht_type.a_num,
    dk_thue out pht_type.a_num,dk_ttoan out pht_type.a_num,
    dk_cap out pht_type.a_num,dk_ma_dk out pht_type.a_var,
    dk_lh_nv out pht_type.a_var,dk_t_suat out pht_type.a_num,dk_ptB out pht_type.a_num,
    dk_ptG out pht_type.a_num,dk_phiG out pht_type.a_num,
    dk_lkeP out pht_type.a_var,dk_lkeB out pht_type.a_var,dk_luy out pht_type.a_var,b_loi out varchar2)
AS
    b_i1 number; b_i2 number; b_kt number; b_ktG number;
    b_lenh varchar2(2000); dt_khd clob; b_txt clob;
    b_dvi_ta varchar2(10):=FTBH_DVI_TA(); b_tgT number:=1;
    b_nt_tien varchar2(5); b_nt_phi varchar2(5); b_c_thue varchar2(1);
    b_thueH number; b_ttoanH number; b_tygia number; b_tp number:=0;
    b_ttrang varchar2(1); b_ngay_hl number; b_ngay_kt number; b_ps varchar2(1); b_qdoi number;
    b_loai_khH varchar2(1); b_ma_khH varchar2(20); b_tenH nvarchar2(500);
    b_cmtH varchar2(20); b_mobiH varchar2(20); b_emailH varchar2(20); b_dchiH nvarchar2(400);
    b_ten nvarchar2(500); b_so_idP number; b_ma_dk varchar2(10); b_loai_khC varchar2(1);
    dk_nv pht_type.a_var; dk_phiB pht_type.a_num; dk_gvu pht_type.a_var;
    a_nv pht_type.a_var; b_nv_bhC varchar2(10);

    dk_maX pht_type.a_var; dk_tenX pht_type.a_nvar; dk_tcX pht_type.a_var;
    dk_ma_ctX pht_type.a_var; dk_kieuX pht_type.a_var; 
    dk_ma_dkX pht_type.a_var; dk_tienX pht_type.a_num; dk_ptX pht_type.a_num;
    dk_phiX pht_type.a_num; dk_thueX pht_type.a_num; dk_lkePX pht_type.a_var; dk_lkeBX pht_type.a_var; dk_luyX pht_type.a_var;
    dk_lh_nvX pht_type.a_var; dk_t_suatX pht_type.a_num; dk_nvX pht_type.a_var; dk_capX pht_type.a_num;
    dk_ptBX pht_type.a_num; dk_phiBX pht_type.a_num; dk_ghv pht_type.a_var; 
    kbt_ma pht_type.a_var; kbt_kbt pht_type.a_clob;
begin
-- Dan - Nhap
b_lenh:=FKH_JS_LENH('ttrang,nt_tien,nt_phi,tygia,c_thue,thue,ttoan,ten,loai_khc,tenc,cmtc,mobic,emailc,dchic,nvv,nvt,nvn');
EXECUTE IMMEDIATE b_lenh into
    b_ttrang,b_nt_tien,b_nt_phi,b_tygia,b_c_thue,b_thueH,b_ttoanH,
    b_ten,b_loai_khC,b_tenC,b_cmtC,b_mobiC,b_emailC,b_dchiC,
    a_nv(1),a_nv(2),a_nv(3) using dt_ct;
PBH_TAU_TSO(dt_ct,
    b_nhom,b_loai,b_cap,b_vlieu,b_ttai,b_so_cn,b_dtich,b_csuat,b_gia,b_tuoi,
    b_ma_sp,b_dkien,b_md_sd,b_nv_bh,b_ngay_hl,b_ngay_kt,
    b_so_dk,b_ten_tau,b_nam_sx,
    b_hoi,b_hoi_tien,b_hoi_tyle,b_hoi_hh,b_tl_mgiu,b_loi);
if b_loi is not null then return; end if;
if trim(b_tenC) is null then
    b_lenh:=FKH_JS_LENH('ten,cmt,mobi,email,dchi');
    EXECUTE IMMEDIATE b_lenh into b_tenC,b_cmtC,b_mobiC,b_emailC,b_dchiC using dt_ct;
end if;
b_loai_khC:=nvl(trim(b_loai_khC),'C'); b_tenC:=nvl(trim(b_tenC),' '); b_tenC:=nvl(trim(b_tenC),' '); b_cmtC:=nvl(trim(b_cmtC),' ');
b_mobiC:=nvl(trim(b_mobiC),' '); b_emailC:=nvl(trim(b_emailC),' '); b_dchiC:=nvl(trim(b_dchiC),' '); 
if b_so_dk=' ' and b_ten_tau=' ' then
    b_loi:='loi:Nhap so dang ky, ten tau:loi'; return;
end if;
if b_nam_sx=0 then
    b_loi:='loi:Nhap nam dong tau:loi'; return;
end if;
if b_nhom<>' ' and FBH_TAU_NHOM_HAN(b_nhom)<>'C' then
    b_loi:='loi:Sai nhom tau:loi'; return;
end if;
if b_loai<>' ' and FBH_TAU_LOAI_HAN(b_loai)<>'C' then
    b_loi:='loi:Sai loai tau:loi'; return;
end if;
if b_cap<>' ' and FBH_TAU_CAP_HAN(b_cap)<>'C' then
    b_loi:='loi:Sai cap tau:loi'; return;
end if;
if b_vlieu<>' ' and FBH_TAU_VLIEU_HAN(b_vlieu)<>'C' then
    b_loi:='loi:Sai vat lieu dong tau:loi'; return;
end if;
if b_ma_sp<>' ' and FBH_TAU_SP_HAN(b_ma_sp)<>'C' then
    b_loi:='loi:Sai ma san pham:loi'; return;
end if;
if b_md_sd not in(' ','H','N','C') then
    b_loi:='loi:Sai muc dich su dung:loi'; return;
end if;
if b_hoi<>' ' and FBH_TAU_HOI_HAN(b_hoi)<>'C' then
    b_loi:='loi:Sai ma hoi:loi'; return;
end if;
b_nt_tien:=nvl(trim(b_nt_tien),'VND'); b_nt_phi:=nvl(trim(b_nt_phi),'VND');
if b_nt_tien<>'VND' then b_tgT:=FTT_TRA_TGTT(b_dvi_ta,b_ngay_hl,b_nt_tien); end if;
if b_nt_phi<>'VND' then b_tp:=2; end if;
b_lenh:=FKH_JS_LENH('nv,ma,ten,tc,ma_ct,kieu,tien,pt,phi,thue,cap,ma_dk,lh_nv,t_suat,ptb,phib,lkep,lkeb,luy');
EXECUTE IMMEDIATE b_lenh bulk collect into
    dk_nv,dk_ma,dk_ten,dk_tc,dk_ma_ct,dk_kieu,dk_tien,dk_pt,dk_phi,dk_thue,dk_cap,
    dk_ma_dk,dk_lh_nv,dk_t_suat,dk_ptB,dk_phiB,dk_lkeP,dk_lkeB,dk_luy using dt_dk;
b_kt:=dk_ma.count; b_ktG:=b_kt;
if b_kt=0 then b_loi:='loi:Nhap dieu khoan:loi'; return; end if;
for b_lp in 1..dk_ma.count loop
    dk_bt(b_lp):=b_lp;
end loop;
EXECUTE IMMEDIATE b_lenh bulk collect into
    dk_nvX,dk_maX,dk_tenX,dk_tcX,dk_ma_ctX,dk_kieuX,dk_tienX,dk_ptX,dk_phiX,dk_thueX,dk_capX,
    dk_ma_dkX,dk_lh_nvX,dk_t_suatX,dk_ptBX,dk_phiBX,dk_lkePX,dk_lkeBX,dk_luyX using dt_dkbs;
for b_lp in 1..dk_maX.count loop
    b_kt:=b_kt+1;
    dk_bt(b_kt):=b_lp+10000;
    dk_nv(b_kt):='M'; dk_ma(b_kt):=dk_maX(b_lp); dk_ten(b_kt):=dk_tenX(b_lp);
    dk_tc(b_kt):=dk_tcX(b_lp); dk_ma_ct(b_kt):=dk_ma_ctX(b_lp); dk_kieu(b_kt):=dk_kieuX(b_lp);
    dk_tien(b_kt):=dk_tienX(b_lp); dk_pt(b_kt):=dk_ptX(b_lp);
    dk_phi(b_kt):=dk_phiX(b_lp); dk_thue(b_kt):=dk_thueX(b_lp);
    dk_cap(b_kt):=dk_capX(b_lp); dk_ma_dk(b_kt):=dk_ma_dkX(b_lp); dk_lh_nv(b_kt):=dk_lh_nvX(b_lp);
    dk_t_suat(b_kt):=dk_t_suatX(b_lp); dk_ptB(b_kt):=dk_ptBX(b_lp); dk_phiB(b_kt):=dk_phiBX(b_lp);
    dk_lkeP(b_kt):=dk_lkePX(b_lp); dk_lkeB(b_kt):=dk_lkeBX(b_lp); dk_luy(b_kt):=dk_luyX(b_lp);
end loop;
for b_lp in 1..dk_ma.count loop
    dk_nv(b_lp):=nvl(trim(dk_nv(b_lp)),' '); dk_ma(b_lp):=trim(dk_ma(b_lp));
    if dk_ma(b_lp) is null then b_loi:='loi:Loi nhap chi tiet nghiep vu dong '||to_char(b_lp)||':loi'; return; end if;
    dk_lh_nv(b_lp):=nvl(trim(dk_lh_nv(b_lp)),' '); dk_tien(b_lp):=nvl(dk_tien(b_lp),0);
    dk_phi(b_lp):=nvl(dk_phi(b_lp),0); dk_phiB(b_lp):=nvl(dk_phiB(b_lp),0);
    if dk_phi(b_lp)<0 then dk_phi(b_lp):=0; end if;
    if b_c_thue='K' then dk_thue(b_lp):=0; else dk_thue(b_lp):=nvl(dk_thue(b_lp),0); end if;
    dk_ttoan(b_lp):=dk_phi(b_lp)+dk_thue(b_lp);
end loop;
for b_lp in 1..dk_maX.count loop
    b_ma_dk:=FBH_MA_DKBS_MA_DK(dk_maX(b_lp));
    if b_ma_dk<>' ' then
        b_i1:=FKH_ARR_VTRI(dk_ma_dk,b_ma_dk);
        if b_i1 not between 1 and b_ktG then
            b_loi:='loi:Dieu khoan bo sung '||dk_ma_dkX(b_lp)||' phai gan kem dieu khoan chinh '||b_ma_dk||':loi'; return;
        end if;
    end if;
end loop;
if dk_maX.count=0 then b_nv_bh:=b_nv_bh||'M'; end if;
b_nv_bh:='';
for b_lp in 1..3 loop
    a_nv(b_lp):=nvl(trim(a_nv(b_lp)),' ');
    if a_nv(b_lp)='C' then
        a_nv(b_lp):=substr('VTN',b_lp,1);
        PKH_GHEP(b_nv_bh,a_nv(b_lp),'');
    else
        a_nv(b_lp):=' ';
    end if;
end loop;
if b_nv_bh is null then b_loi:='loi:Chua chon loai phi mua:loi'; return; end if;
PBH_HD_THAY_PHIg(b_nt_tien,b_nt_tien,b_tygia,b_thueH,b_ttoanH,dk_lh_nv,dk_tien,dk_phi,dk_thue,dk_ttoan,b_loi);
if b_loi is not null then return; end if;
for b_lp in 1..dk_ma.count loop
    if dk_phiB(b_lp)>dk_phi(b_lp) then
        dk_phiG(b_lp):=dk_phiB(b_lp)-dk_phi(b_lp);
        dk_ptG(b_lp):=round(dk_phiG(b_lp)*100/dk_tien(b_lp),2);
    else
        dk_ptG(b_lp):=0; dk_phiG(b_lp):=0;
    end if;
end loop;
for b_lp1 in 1..3 loop
    if a_nv(b_lp1)<>' ' then
      b_tau_id:=FBH_TAUTSO_SO_ID(b_so_dk,a_nv(b_lp1));
      if b_tau_id<>0 then
          for r_lp in(select distinct nv_bh from bh_tau_ds where tau_id=b_tau_id and so_id_dt<>b_so_idD and
              FKH_GIAO(b_ngay_hl,b_ngay_kt,ngay_hl,ngay_kt)='C' and FBH_TAU_TTRANG(ma_dvi,so_id,'C')='D') loop
              b_nv_bhC:=r_lp.nv_bh; b_i1:=length(b_nv_bhC);
              for b_lp in 1..b_i1 loop
                  if instr(b_nv_bh,substr(b_nv_bhC,b_lp,1))<>0 then b_tb:='Trung thoi gian bao hiem'; end if;
              end loop;
          end loop;
      else
          PHT_ID_MOI(b_tau_id,b_loi);
          if b_loi is not null then return; end if;
      end if;
    end if;
end loop;

b_ng_huong:='';
if b_ttrang in('T','D') then
    b_lenh:=FKH_JS_LENH('loai_khh,tenh,dchih,cmth,mobih,emailh');
    EXECUTE IMMEDIATE b_lenh into b_loai_khH,b_tenH,b_dchiH,b_cmtH,b_mobiH,b_emailH using dt_ct;
    if trim(b_tenH) is not null then
        select json_object('loai' value b_loai_khH,'ten' value b_tenH,'cmt' value b_cmtH,
            'dchi' value b_dchiH,'mobi' value b_mobiH,'email' value b_emailH) into b_txt from dual;
        PBH_DTAC_MA_NH(b_txt,b_ma_khH,b_loi,b_ma_dvi,b_nsd);
        b_ng_huong:=b_tenH;
        if trim(b_cmtH) is not null then
            if b_loai_khH='C' then
                b_ng_huong:=b_tenH||', so CMT/CCCD: '||b_cmtH;
            else
                b_ng_huong:=b_tenH||', ma thue : '||b_cmtH;
            end if;
        end if;
        if trim(b_dchiH) is not null then
            b_ng_huong:=b_tenH||', dia chi: '||b_dchiH;
        end if;
    end if;
    if b_ten<>b_tenC then
        select json_object('loai' value b_loai_khC,'ten' value b_tenC,'cmt' value b_cmtC,
            'dchi' value b_dchiH,'mobi' value b_mobiC,'email' value b_emailC) into b_txt from dual;
        PBH_DTAC_MA_NH(b_txt,b_ma_khH,b_loi,b_ma_dvi,b_nsd);
    end if;
    select count(*) into b_i1 from bh_tau_phi_txt where so_id=b_so_idP and loai='dt_khd';
    if b_i1<>0 then
        select txt into dt_khd from bh_tau_phi_txt where so_id=b_so_idP and loai='dt_khd';
        b_i1:=length(dt_khd)-2;
        dt_khd:=substr(dt_khd,2,b_i1);
        PBH_TAUG_KHD(dt_ct,dt_dk,dt_khd,b_loi);
        if b_loi is not null then return; end if;
    end if;
end if;
b_loi:='';
exception when others then if b_loi is null then raise PROGRAM_ERROR; else null; end if;
end;
/
create or replace procedure PBH_TAUH_TESTd(
    dt_ctH clob,dt_ct in out clob,dt_dk clob,dt_dkbs clob,
    b_ma_dvi varchar2,b_nsd varchar2,b_ttrang varchar2,b_kieu_hd varchar2,
    b_nt_tien varchar2,b_nt_phi varchar2,b_tygia number,b_c_thue varchar2,
    b_ngay_hlH number,b_ngay_ktH number,b_ngay_capH number,

    b_so_id_dt out number,b_kieu_gcn out varchar2,b_gcn out varchar2,b_gcnG out varchar2,
    b_tenC out nvarchar2,b_cmtC out varchar2,b_mobiC out varchar2,
    b_emailC out varchar2,b_dchiC out nvarchar2,b_ng_huong out nvarchar2,

    b_nhom out varchar2,b_loai out varchar2,b_cap out varchar2,b_vlieu out varchar2,
    b_ttai out number,b_so_cn out number,b_dtich out number,b_csuat out number,b_gia out number,b_tuoi out number,
    b_ma_sp out varchar2,b_dkien out varchar2,b_md_sd out varchar2,b_nv_bh out varchar2,
    b_so_dk out varchar2,b_ten_tau out nvarchar2,b_nam_sx out number,
    b_hoi out varchar2,b_hoi_tien out number,b_hoi_tyle out number,b_hoi_hh out number,b_tl_mgiu out number,
    b_ngay_hl out number,b_ngay_kt out number,b_ngay_cap out number,b_so_idP out number, b_tau_id out number,

    dk_bt out pht_type.a_num,dk_ma out pht_type.a_var,dk_ten out pht_type.a_nvar,
    dk_tc out pht_type.a_var,dk_ma_ct out pht_type.a_var,dk_kieu out pht_type.a_var,
    dk_tien out pht_type.a_num,dk_pt out pht_type.a_num,dk_phi out pht_type.a_num,
    dk_thue out pht_type.a_num,dk_ttoan out pht_type.a_num,
    dk_cap out pht_type.a_num,dk_ma_dk out pht_type.a_var,
    dk_lh_nv out pht_type.a_var,dk_t_suat out pht_type.a_num,
    dk_ptB out pht_type.a_num,dk_phiB out pht_type.a_num,
    dk_lkeP out pht_type.a_var,dk_lkeB out pht_type.a_var,dk_luy out pht_type.a_var,b_loi out varchar2)
AS
    b_i1 number; b_i2 number; b_kt number; b_ktG number;
    b_lenh varchar2(2000); dt_khd clob; b_txt clob;
    b_tp number:=0; b_nv_bhC varchar2(10);
    b_dvi_ta varchar2(10):=FTBH_DVI_TA(); b_tgT number:=1;
    b_ps varchar2(1); b_qdoi number; b_ma_dk varchar2(10); b_loai_khC varchar2(1);
    dk_nv pht_type.a_var; a_nv pht_type.a_var; dk_gvu pht_type.a_var;

    dk_nvX pht_type.a_var; dk_maX pht_type.a_var; dk_tenX pht_type.a_nvar; dk_tcX pht_type.a_var;
    dk_ma_ctX pht_type.a_var; dk_kieuX pht_type.a_var;
    dk_ma_dkX pht_type.a_var; dk_tienX pht_type.a_num; dk_ptX pht_type.a_num;
    dk_phiX pht_type.a_num; dk_thueX pht_type.a_num; dk_lkePX pht_type.a_var; dk_lkeBX pht_type.a_var; dk_luyX pht_type.a_var;
    dk_lh_nvX pht_type.a_var; dk_t_suatX pht_type.a_num; dk_capX pht_type.a_num;
    dk_ptGX pht_type.a_num; dk_phiGX pht_type.a_num; dk_ptBX pht_type.a_num; dk_phiBX pht_type.a_num; a_thay pht_type.a_num;

    kbt_ma pht_type.a_var; kbt_kbt pht_type.a_clob;
begin
-- Dan - Nhap
b_loi:='loi:Loi xu ly PBH_TAUH_TESTd:loi';
b_lenh:=FKH_JS_LENH('so_id_dt,gcn,gcn_g,loai_khc,tenc,cmtc,mobic,emailc,dchic,nvv,nvt,nvn');
EXECUTE IMMEDIATE b_lenh into b_so_id_dt,b_gcn,b_gcnG,b_loai_khC,b_tenC,b_cmtC,b_mobiC,b_emailC,b_dchiC,a_nv(1),a_nv(2),a_nv(3) using dt_ct;
PBH_TAU_TSO(dt_ct,
    b_nhom,b_loai,b_cap,b_vlieu,b_ttai,b_so_cn,b_dtich,b_csuat,b_gia,b_tuoi,
    b_ma_sp,b_dkien,b_md_sd,b_nv_bh,b_ngay_hl,b_ngay_kt,
    b_so_dk,b_ten_tau,b_nam_sx,b_hoi,b_hoi_tien,b_hoi_tyle,b_hoi_hh,b_tl_mgiu,b_loi);
if b_loi is not null then return; end if;
if trim(b_tenC) is null then
    b_lenh:=FKH_JS_LENH('ten,cmt,mobi,email,dchi');
    EXECUTE IMMEDIATE b_lenh into b_tenC,b_cmtC,b_mobiC,b_emailC,b_dchiC using dt_ctH;
end if;
b_loai_khC:=nvl(trim(b_loai_khC),'C'); b_tenC:=nvl(trim(b_tenC),' '); b_tenC:=nvl(trim(b_tenC),' '); b_cmtC:=nvl(trim(b_cmtC),' ');
b_mobiC:=nvl(trim(b_mobiC),' '); b_emailC:=nvl(trim(b_emailC),' '); b_dchiC:=nvl(trim(b_dchiC),' ');
if b_so_dk=' ' and b_ten_tau=' ' then
    b_loi:='loi:Nhap so dang ky, ten tau:loi'; return;
end if;
if b_nam_sx=0 then
    b_loi:='loi:Nhap nam dong tau:loi'; return;
end if;
if b_nhom<>' ' and FBH_TAU_NHOM_HAN(b_nhom)<>'C' then
    b_loi:='loi:Sai nhom tau:loi'; return;
end if;
if b_loai<>' ' and FBH_TAU_LOAI_HAN(b_loai)<>'C' then
    b_loi:='loi:Sai loai tau:loi'; return;
end if;
if b_cap<>' ' and FBH_TAU_CAP_HAN(b_cap)<>'C' then
    b_loi:='loi:Sai cap tau:loi'; return;
end if;
if b_vlieu<>' ' and FBH_TAU_VLIEU_HAN(b_vlieu)<>'C' then
    b_loi:='loi:Sai vat lieu dong tau:loi'; return;
end if;
if b_ma_sp<>' ' and FBH_TAU_SP_HAN(b_ma_sp)<>'C' then
    b_loi:='loi:Sai ma san pham:loi'; return;
end if;
if b_md_sd not in(' ','H','N','C') then
    b_loi:='loi:Sai muc dich su dung:loi'; return;
end if;
if b_hoi<>' ' and FBH_TAU_HOI_HAN(b_hoi)<>'C' then
    b_loi:='loi:Sai ma hoi:loi'; return;
end if;
b_tau_id:=nvl(b_tau_id,0);
if b_ngay_kt<b_ngay_hl or b_ngay_kt>b_ngay_ktH then b_ngay_kt:=b_ngay_ktH; end if;
if b_ngay_hl<b_ngay_hlH or b_ngay_hl>b_ngay_kt then b_ngay_hl:=b_ngay_hlH; end if;
b_kieu_gcn:='G'; b_ngay_cap:=b_ngay_capH;
b_gcn:=nvl(trim(b_gcn),' '); b_gcnG:=nvl(trim(b_gcnG),' ');
if b_so_id_dt<100000 then
    PHT_ID_MOI(b_so_id_dt,b_loi);
    if b_loi is not null then return; end if;
    b_gcn:=substr(to_char(b_so_id_dt),3); b_gcnG:=' ';
else
    if b_kieu_hd in('S','B') and b_gcnG<>' ' then
        select count(*) into b_i1 from bh_tau_ds where ma_dvi=b_ma_dvi and so_id_dt=b_so_id_dt and gcn=b_gcnG;
        if b_i1=0 then b_loi:='loi:GCN '||b_gcnG||' da xoa:loi'; return; end if;
        b_kieu_gcn:=b_kieu_hd; b_gcn:=' ';
    end if;
    if b_gcn=' ' or instr(b_gcn,'.')=2 then
        b_gcn:=substr(to_char(b_so_id_dt),3);
        if b_kieu_gcn<>'G' then
            select count(*) into b_i1 from bh_tau_ds where ma_dvi=b_ma_dvi and so_id_dt=b_so_id_dt and kieu_gcn=b_kieu_hd;
            b_gcn:=b_gcn||'/'||b_kieu_hd||to_char(b_i1+1);
        end if;
    else
        select nvl(max(ngay_cap),b_ngay_capH) into b_ngay_cap from bh_tau_ds where ma_dvi=b_ma_dvi and so_id_dt=b_so_id_dt and gcn=b_gcn;
    end if;
end if;
PKH_JS_THAYa(dt_ct,'gcn,gcn_g',b_gcn||','||b_gcnG);
a_thay(1):=b_so_id_dt; a_thay(2):=b_ngay_hl; a_thay(3):=b_ngay_kt; a_thay(4):=b_ngay_cap;
PKH_JS_THAYan(dt_ct,'so_id_dt,ngay_hl,ngay_kt,ngay_cap',a_thay);
b_lenh:=FKH_JS_LENH('nv,ma,ten,tc,ma_ct,kieu,tien,pt,phi,thue,cap,ma_dk,lh_nv,t_suat,ptb,phib,lkep,lkeb,luy');
EXECUTE IMMEDIATE b_lenh bulk collect into
    dk_nv,dk_ma,dk_ten,dk_tc,dk_ma_ct,dk_kieu,dk_tien,dk_pt,dk_phi,dk_thue,dk_cap,
    dk_ma_dk,dk_lh_nv,dk_t_suat,dk_ptB,dk_phiB,dk_lkeP,dk_lkeB,dk_luy using dt_dk;
b_kt:=dk_ma.count; b_ktG:=b_kt;
if b_kt=0 then b_loi:='loi:Nhap dieu khoan:loi'; return; end if;
for b_lp in 1..dk_ma.count loop
    dk_bt(b_lp):=b_lp;
end loop;
EXECUTE IMMEDIATE b_lenh bulk collect into
    dk_nvX,dk_maX,dk_tenX,dk_tcX,dk_ma_ctX,dk_kieuX,dk_tienX,dk_ptX,dk_phiX,dk_thueX,dk_capX,
    dk_ma_dkX,dk_lh_nvX,dk_t_suatX,dk_ptBX,dk_phiBX,dk_lkePX,dk_lkeBX,dk_luyX using dt_dkbs;
for b_lp in 1..dk_maX.count loop
    b_kt:=b_kt+1;
    dk_bt(b_kt):=b_lp+10000;
    dk_nv(b_kt):='M'; dk_ma(b_kt):=dk_maX(b_lp); dk_ten(b_kt):=dk_tenX(b_lp);
    dk_tc(b_kt):=dk_tcX(b_lp); dk_ma_ct(b_kt):=dk_ma_ctX(b_lp);
    dk_kieu(b_kt):=dk_kieuX(b_lp); dk_tien(b_kt):=dk_tienX(b_lp);
    dk_pt(b_kt):=dk_ptX(b_lp); dk_phi(b_kt):=dk_phiX(b_lp);
    dk_ptB(b_kt):=dk_ptBX(b_lp); dk_phiB(b_kt):=dk_phiBX(b_lp);
    dk_thue(b_kt):=dk_thueX(b_lp); dk_cap(b_kt):=dk_capX(b_lp);
    dk_ma_dk(b_kt):=dk_ma_dkX(b_lp); dk_lh_nv(b_kt):=dk_lh_nvX(b_lp);
    dk_t_suat(b_kt):=dk_t_suatX(b_lp); dk_lkeP(b_kt):=dk_lkePX(b_lp);
    dk_lkeB(b_kt):=dk_lkeB(b_lp); dk_luy(b_kt):=dk_luyX(b_lp);
end loop;
for b_lp in 1..dk_ma.count loop
    dk_nv(b_lp):=nvl(trim(dk_nv(b_lp)),' '); dk_ma(b_lp):=trim(dk_ma(b_lp));
    if dk_ma(b_lp) is null then b_loi:='loi:Loi nhap chi tiet nghiep vu dong '||to_char(b_lp)||':loi'; return; end if;
    dk_lh_nv(b_lp):=nvl(trim(dk_lh_nv(b_lp)),' '); dk_tien(b_lp):=nvl(dk_tien(b_lp),0);
    dk_phi(b_lp):=nvl(dk_phi(b_lp),0); dk_phiB(b_lp):=nvl(dk_phiB(b_lp),0);
    if b_c_thue='K' then dk_thue(b_lp):=0; else dk_thue(b_lp):=nvl(dk_thue(b_lp),0); end if;
    dk_ttoan(b_lp):=dk_phi(b_lp)+dk_thue(b_lp);
end loop;
for b_lp in 1..dk_maX.count loop
    b_ma_dk:=FBH_MA_DKBS_MA_DK(dk_maX(b_lp));
    if b_ma_dk<>' ' then
        b_i1:=FKH_ARR_VTRI(dk_ma_dk,b_ma_dk);
        if b_i1 not between 1 and b_ktG then
            b_loi:='loi:Dieu khoan bo sung '||dk_ma_dkX(b_lp)||' phai gan kem dieu khoan chinh '||b_ma_dk||':loi'; return;
        end if;
    end if;
end loop;
for b_lp in 1..3 loop
    a_nv(b_lp):=nvl(trim(a_nv(b_lp)),' ');
    if a_nv(b_lp)='C' then
        a_nv(b_lp):=substr('VTN',b_lp,1);
        PKH_GHEP(b_nv_bh,a_nv(b_lp),'');
    else
        a_nv(b_lp):=' ';
    end if;
end loop;
b_loi:='';
exception when others then if b_loi is null then raise PROGRAM_ERROR; else null; end if;
end;