AAA

create table bh_tau_ID(
    tau_id number,
    ten nvarchar2(500),
    cmt varchar2(20),
    mobi varchar2(20),
    email varchar2(100),
    dchi nvarchar2(500),
    nhom varchar2(10),
    loai varchar2(10),
    cap varchar2(10),
    vlieu varchar2(10),
    ttai number,
    so_cn number,
    dtich number,
    csuat number,
    gia number,
    tuoi number,
    ma_sp varchar2(10),
    dkien varchar2(10),             -- Dieu kien: A,B,K
    md_sd varchar2(10),             -- MDSD: H-Cho hang,N-Cho nguoi,C-Ca hai
    nv_bh varchar2(10),             -- V-Vat chat, T-trach nhiem, N-nguoi
    so_dk varchar2(20),
    ten_tau nvarchar2(500),
    nam_sx number,
    hoi varchar2(10));
CREATE INDEX bh_tau_ID_i1 on bh_tau_ID(tau_id);
CREATE INDEX bh_tau_ID_i2 on bh_tau_ID(nv_bh);
CREATE INDEX bh_tau_ID_i3 on bh_tau_ID(so_dk);

drop table bh_tau_ds;
create table bh_tau_ds
    (ma_dvi varchar2(10),
    so_id number,
    so_id_dt number,
    bt number,
    kieu_gcn varchar2(1),
    gcn varchar2(20),
    gcn_g varchar2(20),
    tenC nvarchar2(500),
    cmtC varchar2(20),
    mobiC varchar2(20),
    emailC varchar2(100),--duchq update
    dchiC nvarchar2(500),
    ng_huong nvarchar2(1000),
    nhom varchar2(10),
    loai varchar2(10),
    cap varchar2(10),
    vlieu varchar2(10),
    ttai number,
    so_cn number,
    dtich number,
    csuat number,
    gia number,
    tuoi number,
    ma_sp varchar2(10),
    dkien varchar2(10),             -- Dieu kien: A,B,K
    md_sd varchar2(10),             -- MDSD: H-Cho hang,N-Cho nguoi,C-Ca hai
    nv_bh varchar2(10),             -- V-Vat chat, T-trach nhiem, N-nguoi
    so_dk varchar2(20),
    ten_tau nvarchar2(500),
    nam_sx number,
    hoi varchar2(10),
    hoi_tien number,
    hoi_tyle number,
    hoi_hh number,
    tl_mgiu number,
    gio_hl varchar2(10),
    ngay_hl number,
    gio_kt varchar2(10),
    ngay_kt number,
    ngay_cap number,
    giam number,
    phi number,
    thue number,
    ttoan number,
    tau_id number)
    PARTITION BY LIST (ma_dvi) (
        PARTITION bh_tau_ds_001 values ('001'),
        PARTITION bh_tau_ds_050 values ('050','052'),
        PARTITION bh_tau_ds_DEFA values (DEFAULT));
CREATE INDEX bh_tau_ds_i0 on bh_tau_ds(tau_id);
CREATE INDEX bh_tau_ds_i1 on bh_tau_ds(so_id) local;
CREATE INDEX bh_tau_ds_i2 on bh_tau_ds(so_id_dt);
CREATE INDEX bh_tau_ds_i3 on bh_tau_ds(gcn);
/
create or replace function FBH_TAUTSO_SO_ID(b_so_dk varchar2,b_nv_bh varchar2) return number
AS
    b_kq number:=0;
begin
-- Nam - Tra nghiep vu
if trim(b_so_dk) is not null and trim(b_nv_bh) is not null then
    select nvl(min(tau_id),0) into b_kq from bh_tau_ID where so_dk=b_so_dk and nv_bh=b_nv_bh;
end if;
return b_kq;
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_ma_dkX(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_TAUG_NH(
    b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_oraIn clob,b_oraOut out clob,b_comm varchar2:='C')
AS
    b_loi varchar2(3000); b_lenh varchar2(2000); b_i1 number;
    dt_ct clob; dt_dk clob; dt_dkbs clob; dt_lt clob; dt_kbt clob; dt_kytt clob; dt_ttt clob;
-- Chung
    b_so_id number; b_so_hd varchar2(20); b_ngay_ht number; b_ttrang varchar2(1); b_kieu_hd varchar2(1); b_so_hd_g varchar2(20);
    b_kieu_kt varchar2(1); b_ma_kt varchar2(20); b_kieu_gt varchar2(1); b_ma_gt varchar2(20); b_ma_cb varchar2(20);
    b_loai_kh varchar2(1); b_ma_kh varchar2(20); b_ten nvarchar2(500); b_dchi nvarchar2(500);
    b_cmt varchar2(20); b_mobi varchar2(20); b_email varchar2(100);
    b_gio_hl varchar2(20); b_ngay_hl number; b_gio_kt varchar2(20); b_ngay_kt number; b_ngay_cap number;
    b_nt_tien varchar2(5); b_nt_phi varchar2(5); b_c_thue varchar2(1);
    b_tl_giam number; b_phi number; b_giam number; b_thue number; b_ttoan number; b_hhong number;
    b_so_idG number; b_so_idD number; b_ngayD number; b_phong varchar2(10);
    tt_ngay pht_type.a_num; tt_tien pht_type.a_num;
-- Rieng
    b_so_hdL varchar2(1):='T'; b_tenC nvarchar2(500); b_cmtC varchar2(20); b_mobiC varchar2(20);
    b_emailC varchar2(100); b_dchiC nvarchar2(500); b_ng_huong nvarchar2(500);

    b_nhom varchar2(10); b_loai varchar2(10); b_cap varchar2(10); b_vlieu varchar2(10);
    b_ttai number; b_so_cn number; b_dtich number; b_csuat number; b_gia number; b_tuoi number;
    b_ma_sp varchar2(10); b_dkien varchar2(10); b_md_sd varchar2(10); b_nv_bh varchar2(10);
    b_so_dk varchar2(20); b_ten_tau nvarchar2(500); b_nam_sx number;
    b_hoi varchar2(20); b_hoi_tien number; b_hoi_tyle number; b_hoi_hh number; b_tl_mgiu number; b_tau_id number;
    b_tb varchar2(200);

    dk_bt pht_type.a_num; dk_ma pht_type.a_var; dk_ten pht_type.a_nvar; dk_tc pht_type.a_var;
    dk_ma_ct pht_type.a_var; dk_kieu pht_type.a_var;
    dk_tien pht_type.a_num; dk_pt pht_type.a_num; dk_phi pht_type.a_num;
    dk_thue pht_type.a_num; dk_ttoan pht_type.a_num; dk_cap pht_type.a_num; dk_ma_dk pht_type.a_var;
    dk_lh_nv pht_type.a_var; dk_t_suat pht_type.a_num; dk_ptB pht_type.a_num;
    dk_ptG pht_type.a_num; dk_phiG pht_type.a_num; dk_lkeP pht_type.a_var; dk_lkeB pht_type.a_var; dk_luy pht_type.a_var;
-- Xu ly
    b_ngay_htC number;
begin
-- Dan - Nhap
if b_comm='C' then
    b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'BH','TAU','N',b_comm);
    if b_loi is not null then raise PROGRAM_ERROR; end if;
end if;
b_so_id:=FKH_JS_GTRIn(b_oraIn,'so_id'); dt_ct:=FKH_JS_GTRIc(b_oraIn,'dt_ct');
dt_dk:=FKH_JS_GTRIc(b_oraIn,'dt_dk'); dt_dkbs:=FKH_JS_GTRIc(b_oraIn,'dt_dkbs');
b_lenh:=FKH_JS_LENHc('dt_lt,dt_kbt,dt_kytt,dt_ttt');
EXECUTE IMMEDIATE b_lenh into dt_lt,dt_kbt,dt_kytt,dt_ttt using b_oraIn;
if b_so_id<>0 then
    select count(*) into b_i1 from bh_tau where ma_dvi=b_ma_dvi and so_id=b_so_id;
    if b_i1<>0 then
        select ngay_ht,kieu_hd,ttrang into b_ngay_htC,b_kieu_hd,b_ttrang from bh_tau
            where ma_dvi=b_ma_dvi and so_id=b_so_id for update nowait;
        if sql%rowcount=0 then b_loi:='loi:Va cham NSD:loi'; raise PROGRAM_ERROR; end if;
        if b_ttrang not in('T','D') then b_kieu_hd:='X'; else b_kieu_hd:='N'; end if;
        PBH_TAU_XOA_XOA(b_ma_dvi,b_nsd,b_so_id,b_kieu_hd,b_loi);
        if b_loi is not null then raise PROGRAM_ERROR; end if;
    end if;
else
    PHT_ID_MOI(b_so_id,b_loi);
    if b_loi is not null then raise PROGRAM_ERROR; end if;
end if;
PBH_HD_JS_TEST(
    b_ma_dvi,b_nsd,b_so_id,b_ngay_htC,'bh_tau',dt_ct,dt_kytt,
    b_so_hd,b_ngay_ht,b_ttrang,b_kieu_hd,b_so_hd_g,b_kieu_kt,b_ma_kt,b_loai_kh,b_ten,b_dchi,b_cmt,b_mobi,b_email,
    b_gio_hl,b_ngay_hl,b_gio_kt,b_ngay_kt,b_ngay_cap,b_nt_tien,b_nt_phi,
    b_c_thue,b_phi,b_giam,b_thue,b_ttoan,b_hhong,b_so_idG,b_so_idD,b_ngayD,b_phong,b_ma_cb,b_ma_kh,
    tt_ngay,tt_tien,b_loi,'TAU');
if b_loi is not null then raise PROGRAM_ERROR; end if;
PBH_TAUG_TESTr(
    b_ma_dvi,b_nsd,b_so_idD,dt_ct,dt_dk,dt_dkbs,
    b_so_hd,b_tenC,b_cmtC,b_mobiC,b_emailC,b_dchiC,b_ng_huong,
    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_so_dk,b_ten_tau,b_nam_sx,b_hoi,b_hoi_tien,b_hoi_tyle,b_hoi_hh,b_tl_mgiu,b_tau_id,b_tb,
    dk_bt,dk_ma,dk_ten,dk_tc,dk_ma_ct,dk_kieu,dk_tien,dk_pt,dk_phi,dk_thue,dk_ttoan,
    dk_cap,dk_ma_dk,dk_lh_nv,dk_t_suat,dk_ptB,dk_ptG,dk_phiG,dk_lkeP,dk_lkeB,dk_luy,b_loi);
if b_loi is not null then raise PROGRAM_ERROR; end if;
PBH_TAUG_NH_NH(
    b_ma_dvi,b_nsd,b_so_id,b_tau_id,
    dt_ct,dt_dk,dt_dkbs,dt_lt,dt_kbt,dt_kytt,dt_ttt,
-- Chung
    b_so_hd,b_ngay_ht,b_ttrang,b_kieu_hd,b_so_hd_g,b_kieu_kt,b_ma_kt,b_kieu_gt,b_ma_gt,b_ma_cb,
    b_so_hdL,b_loai_kh,b_ma_kh,b_ten,b_dchi,b_cmt,b_mobi,b_email,
    b_gio_hl,b_ngay_hl,b_gio_kt,b_ngay_kt,b_ngay_cap,b_nt_tien,b_nt_phi,b_c_thue,
    b_phi,b_giam,b_thue,b_ttoan,b_hhong,b_so_idG,b_so_idD,b_ngayD,b_phong,
    tt_ngay,tt_tien,
-- Rieng
    b_tenC,b_cmtC,b_mobiC,b_emailC,b_dchiC,b_ng_huong,
    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_so_dk,b_ten_tau,b_nam_sx,
    b_hoi,b_hoi_tien,b_hoi_tyle,b_hoi_hh,b_tl_mgiu,
    dk_bt,dk_ma,dk_ten,dk_tc,dk_ma_ct,dk_kieu,dk_tien,dk_pt,dk_phi,dk_thue,dk_ttoan,
    dk_cap,dk_ma_dk,dk_lh_nv,dk_t_suat,dk_ptB,dk_ptG,dk_phiG,dk_lkeP,dk_lkeB,dk_luy,b_loi);
if b_loi is not null then raise PROGRAM_ERROR; end if;
if b_comm='C' then commit; end if;
select json_object('ma_dvi' value b_ma_dvi,'so_id' value b_so_id,'so_hd' value b_so_hd, 'ma_kh' value b_ma_kh, 'tb' value b_tb) into b_oraOut from dual;
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_TAUG_NH_NH(
    b_ma_dvi varchar2,b_nsd varchar2,b_so_id number, b_tau_id number,
    dt_ct in out clob,dt_dk clob,dt_dkbs clob,dt_lt clob,dt_kbt clob,dt_kytt clob,dt_ttt clob,
-- Chung
    b_so_hd in out varchar2,b_ngay_ht number,b_ttrang varchar2,b_kieu_hd varchar2,b_so_hd_g varchar2,
    b_kieu_kt varchar2,b_ma_kt varchar2,b_kieu_gt varchar2,b_ma_gt varchar2,b_ma_cb varchar2,
    b_so_hdL varchar2,b_loai_kh varchar2,b_ma_kh varchar2,b_ten nvarchar2,b_dchi nvarchar2, 
    b_cmt varchar2,b_mobi varchar2,b_email varchar2,
    b_gio_hl varchar2,b_ngay_hl number,b_gio_kt varchar2,b_ngay_kt number,b_ngay_cap number,
    b_nt_tien varchar2,b_nt_phi varchar2,b_c_thue varchar2,
    b_phi number,b_giam number,b_thue number,b_ttoan number,b_hhong number,
    b_so_idG number,b_so_idD number,b_ngayD number,b_phong varchar2,
    tt_ngay pht_type.a_num,tt_tien pht_type.a_num,
-- Rieng
    b_tenC nvarchar2,b_cmtC varchar2,b_mobiC varchar2,
    b_emailC varchar2,b_dchiC nvarchar2,b_ng_huong nvarchar2,
    b_nhom varchar2,b_loai varchar2,b_cap varchar2,b_vlieu varchar2,
    b_ttai number,b_so_cn number,b_dtich number,b_csuat number,b_gia number,b_tuoi number,
    b_ma_sp varchar2,b_dkien varchar2,b_md_sd varchar2,b_nv_bh varchar2,
    b_so_dk varchar2,b_ten_tau nvarchar2,b_nam_sx number,
    b_hoi varchar2,b_hoi_tien number,b_hoi_tyle number,b_hoi_hh number,b_tl_mgiu number,

    dk_bt pht_type.a_num,dk_ma pht_type.a_var,dk_ten pht_type.a_nvar,dk_tc pht_type.a_var,
    dk_ma_ct pht_type.a_var,dk_kieu pht_type.a_var,
    dk_tien pht_type.a_num,dk_pt pht_type.a_num,dk_phi pht_type.a_num,
    dk_thue pht_type.a_num,dk_ttoan pht_type.a_num,dk_cap pht_type.a_num,dk_ma_dk pht_type.a_var,
    dk_lh_nv pht_type.a_var,dk_t_suat pht_type.a_num,dk_ptB pht_type.a_num,
    dk_ptG pht_type.a_num,dk_phiG pht_type.a_num,
    dk_lkeP pht_type.a_var,dk_lkeB pht_type.a_var,dk_luy pht_type.a_var,b_loi out varchar2)
AS
    b_i1 number; b_so_id_kt number:=-1; b_tien number:=0; b_txt clob;
begin
-- Dan - Nhap
b_loi:='loi:Loi Table bh_tau:loi';
if b_ttrang='D' then b_so_id_kt:=0; end if;
for b_lp in 1..dk_lh_nv.count loop
    if dk_lh_nv(b_lp)<>' ' and dk_kieu(b_lp)='T' then b_tien:=b_tien+dk_tien(b_lp); end if;
    insert into bh_tau_dk values(b_ma_dvi,b_so_id,b_so_idD,dk_bt(b_lp),
        dk_ma(b_lp),dk_ten(b_lp),dk_tc(b_lp),dk_ma_ct(b_lp),dk_kieu(b_lp),dk_tien(b_lp),dk_pt(b_lp),dk_phi(b_lp),
        dk_cap(b_lp),dk_ma_dk(b_lp),dk_lh_nv(b_lp),dk_t_suat(b_lp),dk_thue(b_lp),dk_ttoan(b_lp),
        dk_ptB(b_lp),dk_ptG(b_lp),dk_phiG(b_lp),dk_lkeP(b_lp),dk_lkeB(b_lp),dk_luy(b_lp));
end loop;
insert into bh_tau values(
    b_ma_dvi,b_so_id,b_so_hd,b_ngay_ht,'G',b_ttrang,b_kieu_hd,b_so_hd_g,b_kieu_kt,b_ma_kt,b_kieu_gt,b_ma_gt,b_ma_cb,
    b_phong,b_so_hdL,b_loai_kh,b_ma_kh,b_ten,b_dchi,b_cmt,b_mobi,b_email,b_gio_hl,b_ngay_hl,b_gio_kt,b_ngay_kt,b_ngay_cap,
    b_nt_tien,b_nt_phi,b_c_thue,1,b_tien,
    b_phi,b_giam,b_thue,b_ttoan,b_hhong,b_so_idG,b_so_idD,'','',b_so_id_kt,b_nsd,sysdate);
insert into bh_tau_ds values(b_ma_dvi,b_so_id,b_so_idD,0,b_kieu_hd,b_so_hd,b_so_hd_g,
    b_tenC,b_cmtC,b_mobiC,b_emailC,b_dchiC,b_ng_huong,
    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_so_dk,b_ten_tau,b_nam_sx,b_hoi,b_hoi_tien,b_hoi_tyle,b_hoi_hh,b_tl_mgiu,
    b_gio_hl,b_ngay_hl,b_gio_kt,b_ngay_kt,b_ngay_cap,b_giam,b_phi,b_thue,b_ttoan,b_tau_id);
delete bh_tau_ID where tau_id=b_tau_id;
insert into bh_tau_ID values(b_tau_id,b_tenC,b_cmtC,b_mobiC,b_emailC,b_dchiC,
    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_so_dk,b_ten_tau,b_nam_sx,b_hoi);
for b_lp in 1..tt_ngay.count loop
    insert into bh_tau_tt values(b_ma_dvi,b_so_id,tt_ngay(b_lp),tt_tien(b_lp));
end loop;
PKH_JS_THAYa(dt_ct,'so_hd,ma_kh',b_so_hd||','||b_ma_kh);
insert into bh_tau_txt values(b_ma_dvi,b_so_id,'dt_ct','"'||dt_ct||'"');
insert into bh_tau_txt values(b_ma_dvi,b_so_id,'dt_dk','"'||dt_dk||'"');
if trim(dt_dkbs) is not null and dt_dkbs<>'[""]'  then
    insert into bh_tau_txt values(b_ma_dvi,b_so_id,'dt_dkbs','"'||dt_dkbs||'"');
end if;
if trim(dt_lt) is not null and dt_lt<>'[""]' then
    insert into bh_tau_txt values(b_ma_dvi,b_so_id,'dt_lt','"'||dt_lt||'"');
end if;
if trim(dt_kbt) is not null and dt_kbt<>'[""]' then
    insert into bh_tau_txt values(b_ma_dvi,b_so_id,'dt_kbt','"'||dt_kbt||'"');
end if;
if trim(dt_kytt) is not null and dt_kytt<>'[""]' then
    insert into bh_tau_txt values(b_ma_dvi,b_so_id,'dt_kytt','"'||dt_kytt||'"');
end if;
if trim(dt_ttt) is not null and dt_ttt<>'[""]' then
    insert into bh_tau_txt values(b_ma_dvi,b_so_id,'dt_ttt','"'||dt_ttt||'"');
end if;
if b_ttrang in('T','D') then
    if dt_dkbs is null then
        b_txt:=dt_dk;
    else
        b_i1:=length(dt_dk)-1;
        b_txt:=substr(dt_dk,1,b_i1)||','||substr(dt_dkbs,2);
    end if;
    insert into bh_tau_kbt values(b_ma_dvi,b_so_id,b_so_idD,'"'||b_txt||'"','"'||dt_lt||'"','"'||dt_kbt||'"');
    select json_object('ma_dvi' value b_ma_dvi,'nsd' value b_nsd,'so_id' value b_so_id,'so_hd' value b_so_hd,
        'kieu_hd' value b_kieu_hd,'nv' value 'TAU','ngay_ht' value b_ngay_ht,'ngay_cap' value b_ngay_cap,
        'ngay_hl' value b_ngay_hl,'ngay_kt' value b_ngay_kt,'cb_ql' value b_ma_cb,'phong' value b_phong,
        'kieu_kt' value b_kieu_kt,'ma_kt' value b_ma_kt,'dly_tke' value ' ','hhong' value b_hhong,
        'pt_hhong' value 'D','ma_kh' value b_ma_kh,'ten' value b_ten,'kieu_gt' value b_kieu_gt,
        'ma_gt' value b_ma_gt,'so_id_d' value b_so_idD,'so_id_g' value b_so_idG,'ttrang' value b_ttrang,
        'dvi_ksoat' value b_ma_dvi,'ksoat' value b_nsd,'bangg' value 'bh_tau',
        'c_thue' value b_c_thue,'nt_tien' value b_nt_tien,'nt_phi' value b_nt_phi,'tien' value b_tien,
        'phi' value b_ttoan-b_thue,'thue' value b_thue returning clob) into b_txt from dual;
    PBH_HD_GOC_NH(b_txt,b_loi);
    if b_loi is not null then return; end if;
end if;
b_loi:='';
exception when others then if b_loi is null then raise PROGRAM_ERROR; else null; end if;
end;