AAA

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;

    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,
    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,
    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) 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_TAUBG_NH(
    b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_oraIn clob,b_oraOut out clob)
AS
    b_loi varchar2(3000); b_lenh varchar2(2000); b_i1 number; b_lan 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(500); b_loai varchar2(500); b_cap varchar2(500); b_vlieu varchar2(500);
    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(500); 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(500); 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;
-- Xu ly
    b_ngay_htC number;
begin
-- Dan - Nhap
b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'BH','TAU','N');
if b_loi is not null then raise PROGRAM_ERROR; 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
    PHT_ID_MOI(b_so_id,b_loi);
    if b_loi is not null then raise PROGRAM_ERROR; end if;
end if;
PBH_BG_JS_TEST(
    b_ma_dvi,b_nsd,b_so_id,'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_phong,b_ma_kh,
    tt_ngay,tt_tien,b_loi,'KH');
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,
    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_TAUBG_NH_NH(
    b_ma_dvi,b_nsd,b_so_id,b_lan,
    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;
FBH_BAO_TTRANGn(b_ma_dvi,b_so_id,b_ttrang,b_loi);
if b_loi is not null then raise PROGRAM_ERROR; end if;
commit;
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,'lan' value b_lan) 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_TAUH_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(1000); b_lenh varchar2(2000); b_i1 number;
    dt_ct clob; ds_ct clob; ds_dk clob; ds_dkbs clob; ds_lt clob; ds_kbt clob; ds_ttt clob; dt_kytt 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
    ds_so_id pht_type.a_num; ds_kieu_gcn pht_type.a_var; ds_gcn pht_type.a_var; ds_gcnG pht_type.a_var;
    ds_tenC pht_type.a_nvar; ds_cmtC pht_type.a_var; ds_mobiC pht_type.a_var;
    ds_emailC pht_type.a_var; ds_dchiC pht_type.a_nvar; ds_ng_huong pht_type.a_nvar;

    ds_nhom pht_type.a_var; ds_loai pht_type.a_var; ds_cap pht_type.a_var; ds_vlieu pht_type.a_var;
    ds_ttai pht_type.a_num; ds_so_cn pht_type.a_num; ds_dtich pht_type.a_num;
    ds_csuat pht_type.a_num; ds_gia pht_type.a_num; ds_tuoi pht_type.a_num;
    ds_ma_sp pht_type.a_var; ds_dkien pht_type.a_var; ds_md_sd pht_type.a_var; ds_nv_bh pht_type.a_var;
    ds_so_dk pht_type.a_var; ds_ten_tau pht_type.a_nvar; ds_nam_sx pht_type.a_num;
    ds_hoi pht_type.a_var; ds_hoi_tien pht_type.a_num; ds_hoi_tyle pht_type.a_num;
    ds_hoi_hh pht_type.a_num; ds_tl_mgiu pht_type.a_num;
    ds_gio_hl pht_type.a_var; ds_ngay_hl pht_type.a_num;
    ds_gio_kt pht_type.a_var; ds_ngay_kt pht_type.a_num;
    ds_ngay_cap pht_type.a_num; ds_so_idP pht_type.a_var;
    ds_giam pht_type.a_num; ds_phi pht_type.a_num;
    ds_thue pht_type.a_num; ds_ttoan pht_type.a_num;

    dk_so_id pht_type.a_num; 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_phiB 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;

    lt_so_id pht_type.a_num; lt_dk pht_type.a_clob; lt_lt pht_type.a_clob; lt_kbt pht_type.a_clob;
-- 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');
b_lenh:=FKH_JS_LENHc('dt_ct,ds_ct,ds_dk,ds_dkbs,ds_lt,ds_kbt,ds_ttt,dt_kytt');
EXECUTE IMMEDIATE b_lenh into dt_ct,ds_ct,ds_dk,ds_dkbs,ds_lt,ds_kbt,ds_ttt,dt_kytt 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_TAUH_TESTr(
    b_ma_dvi,b_nsd,b_so_idG,
    dt_ct, ds_ct,ds_dk,ds_dkbs,ds_lt,ds_kbt,
    ds_so_id,ds_kieu_gcn,ds_gcn,ds_gcnG,ds_tenC,ds_cmtC,ds_mobiC,ds_emailC,ds_dchiC,ds_ng_huong,
    ds_nhom,ds_loai,ds_cap,ds_vlieu,ds_ttai,ds_so_cn,ds_dtich,ds_csuat,ds_gia,ds_tuoi,
  ds_ma_sp,ds_dkien,ds_md_sd,ds_nv_bh,ds_so_dk,ds_ten_tau,ds_nam_sx,ds_hoi,ds_hoi_tien,
  ds_hoi_tyle,ds_hoi_hh,ds_tl_mgiu,ds_gio_hl,ds_ngay_hl,ds_gio_kt,ds_ngay_kt,
    ds_ngay_cap,ds_so_idP,ds_giam,ds_phi,ds_thue,ds_ttoan,
    dk_so_id,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_phiB,dk_ptG,dk_phiG,dk_lkeP,dk_lkeB,dk_luy,
    lt_so_id,lt_dk,lt_lt,lt_kbt,b_loi);
if b_loi is not null then raise PROGRAM_ERROR; end if;
PBH_TAUH_NH_NH(
    b_ma_dvi,b_nsd,b_so_id,
    dt_ct,ds_ct,ds_dk,ds_dkbs,ds_lt,ds_kbt,ds_ttt,
    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_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,
    ds_so_id,ds_kieu_gcn,ds_gcn,ds_gcnG,ds_tenC,ds_cmtC,ds_mobiC,ds_emailC,ds_dchiC,ds_ng_huong,
    ds_nhom,ds_loai,ds_cap,ds_vlieu,ds_ttai,ds_so_cn,ds_dtich,ds_csuat,ds_gia,ds_tuoi,
    ds_ma_sp,ds_dkien,ds_md_sd,ds_nv_bh,ds_so_dk,ds_ten_tau,ds_nam_sx,ds_hoi,ds_hoi_tien,ds_hoi_tyle,ds_hoi_hh,ds_tl_mgiu,
    ds_gio_hl,ds_ngay_hl,ds_gio_kt,ds_ngay_kt,ds_ngay_cap,ds_so_idP,ds_giam,ds_phi,ds_thue,ds_ttoan,
    dk_so_id,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_phiB,dk_ptG,dk_phiG,dk_lkeP,dk_lkeB,dk_luy,
    lt_so_id,lt_dk,lt_lt,lt_kbt,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) 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_TAUBH_NH(
    b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_oraIn clob,b_oraOut out clob)
AS
    b_loi varchar2(3000); b_lenh varchar2(2000); b_i1 number; b_lan number;
    dt_ct clob; ds_ct clob; ds_dk clob; ds_dkbs clob; ds_lt clob; ds_kbt clob; ds_ttt clob; dt_kytt 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_so_hdL 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
    ds_so_id pht_type.a_num; ds_kieu_gcn pht_type.a_var; ds_gcn pht_type.a_var; ds_gcnG pht_type.a_var; 
    ds_tenC pht_type.a_nvar; ds_cmtC pht_type.a_var; ds_mobiC pht_type.a_var; 
    ds_emailC pht_type.a_var; ds_dchiC pht_type.a_nvar; ds_ng_huong pht_type.a_nvar; 

    ds_nhom pht_type.a_var; ds_loai pht_type.a_var; ds_cap pht_type.a_var; ds_vlieu pht_type.a_var; 
    ds_ttai pht_type.a_num; ds_so_cn pht_type.a_num; ds_dtich pht_type.a_num; 
    ds_csuat pht_type.a_num; ds_gia pht_type.a_num; ds_tuoi pht_type.a_num; 
    ds_ma_sp pht_type.a_var; ds_dkien pht_type.a_var; ds_md_sd pht_type.a_var; ds_nv_bh pht_type.a_var; 
    ds_so_dk pht_type.a_var; ds_ten_tau pht_type.a_nvar; ds_nam_sx pht_type.a_num; 
    ds_hoi pht_type.a_var; ds_hoi_tien pht_type.a_num; ds_hoi_tyle pht_type.a_num; 
    ds_hoi_hh pht_type.a_num; ds_tl_mgiu pht_type.a_num; 

    ds_gio_hl pht_type.a_var; ds_ngay_hl pht_type.a_num; 
    ds_gio_kt pht_type.a_var; ds_ngay_kt pht_type.a_num; 
    ds_ngay_cap pht_type.a_num; ds_so_idP pht_type.a_var; 
    ds_giam pht_type.a_num; ds_phi pht_type.a_num; 
    ds_thue pht_type.a_num; ds_ttoan pht_type.a_num; 

    dk_so_id pht_type.a_num; 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_phiB 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;

    lt_so_id pht_type.a_num; lt_dk pht_type.a_clob; lt_lt pht_type.a_clob; lt_kbt pht_type.a_clob;
-- Xu ly
    b_ngay_htC number;
begin
-- Dan - Nhap
b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'BH','TAU','N');
if b_loi is not null then raise PROGRAM_ERROR; end if;
b_so_id:=FKH_JS_GTRIn(b_oraIn,'so_id');
b_lenh:=FKH_JS_LENHc('dt_ct,ds_ct,ds_dk,ds_dkbs,ds_lt,ds_kbt,ds_ttt,dt_kytt');
EXECUTE IMMEDIATE b_lenh into dt_ct,ds_ct,ds_dk,ds_dkbs,ds_lt,ds_kbt,ds_ttt,dt_kytt using b_oraIn;
if b_so_id=0 then
    PHT_ID_MOI(b_so_id,b_loi);
    if b_loi is not null then raise PROGRAM_ERROR; end if;
end if;
PBH_BG_JS_TEST(
    b_ma_dvi,b_nsd,b_so_id,'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_phong,b_ma_kh,
    tt_ngay,tt_tien,b_loi,'KH');
if b_loi is not null then raise PROGRAM_ERROR; end if;
PBH_TAUH_TESTr(
    b_ma_dvi,b_nsd,b_so_idG,
    dt_ct,ds_ct,ds_dk,ds_dkbs,ds_lt,ds_kbt,
    ds_so_id,ds_kieu_gcn,ds_gcn,ds_gcnG,ds_tenC,ds_cmtC,ds_mobiC,ds_emailC,ds_dchiC,ds_ng_huong,
    ds_nhom,ds_loai,ds_cap,ds_vlieu,ds_ttai,ds_so_cn,ds_dtich,ds_csuat,ds_gia,ds_tuoi,
    ds_ma_sp,ds_dkien,ds_md_sd,ds_nv_bh,ds_so_dk,ds_ten_tau,ds_nam_sx,ds_hoi,ds_hoi_tien,ds_hoi_tyle,ds_hoi_hh,ds_tl_mgiu,
    ds_gio_hl,ds_ngay_hl,ds_gio_kt,ds_ngay_kt,ds_ngay_cap,ds_so_idP,ds_giam,ds_phi,ds_thue,ds_ttoan,
    dk_so_id,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_phiB,dk_ptG,dk_phiG,dk_lkeP,dk_lkeB,dk_luy,
    lt_so_id,lt_dk,lt_lt,lt_kbt,b_loi);
if b_loi is not null then raise PROGRAM_ERROR; end if;
PBH_TAUBH_NH_NH(
    b_ma_dvi,b_nsd,b_so_id,b_lan,
    dt_ct,ds_ct,ds_dk,ds_dkbs,ds_lt,ds_kbt,ds_ttt,dt_kytt,
    b_so_hd,b_ngay_ht,b_ttrang,b_kieu_hd,b_so_hd_g,
    b_phong,b_ma_kh,b_ten,
    b_ngay_hl,b_ngay_kt,b_nt_tien,b_nt_phi,b_phi,
    ds_so_id,ds_so_dk,ds_ten_tau,ds_nam_sx,ds_nhom,ds_ma_sp, 
    dk_so_id,dk_ma,dk_ten,dk_kieu,
    dk_lh_nv,dk_tien,dk_phi,dk_ptG,b_loi);
if b_loi is not null then raise PROGRAM_ERROR; end if;
commit;
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,'lan' value b_lan) 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_HD_TT_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(100); b_lenh varchar2(2000);
    b_i1 number; b_i2 number; b_t_suat number; b_kieu_do varchar2(1);
    b_c2 varchar2(50); b_kt1 number; b_ma_nt varchar2(5):='VND';
    b_phong varchar2(10); b_don varchar2(50); b_nha_bh varchar2(20);

    b_lan number; b_so_hdon varchar2(30); b_ps varchar2(30); b_ttrang varchar2(30);b_so_id_hde number;
    b_so_hdon_tr varchar2(50); b_so_id_hdon number;
    b_ma_thue_tr varchar2(50); b_so_seri varchar2(50); b_ngay_nh number:=PKH_NG_CSO(sysdate);

    b_so_id_tt number; b_ngay_ht number; b_htoan varchar2(1);
    b_ma_kh varchar2(20); b_ten nvarchar2(500); b_dchi nvarchar2(500); b_ma_thue varchar2(20); b_email varchar2(100);
    b_so_ct varchar2(10); b_pt_tra varchar2(1); b_kvat varchar2(1); b_kieuHD varchar2(1); b_layHD varchar2(1);
    b_so_don varchar2(20); b_ngay_bc number; b_ng_hd number;
    b_vochD varchar2(20); b_nd nvarchar2(500); b_ttoan_qd number:=0; b_thue_qd number:=0;
    b_dvi_tt varchar2(1); b_ma_dl varchar2(20); b_vochK varchar2(1);
    b_nt_tra varchar2(5); b_tra number;

    a_so_id pht_type.a_num; a_ngay pht_type.a_num; a_pt pht_type.a_var;
    a_ma_nt pht_type.a_var; a_tien pht_type.a_num; a_tien_qd pht_type.a_num;

    a_pt_tt pht_type.a_var; a_ma_nt_tt pht_type.a_var; a_phi pht_type.a_num;
    a_tien_tt pht_type.a_num; a_tien_tt_qd pht_type.a_num;
    a_thue_tt pht_type.a_num; a_thue_tt_qd pht_type.a_num;

    dt_ct clob; dt_dk clob;
begin
-- Dan - Nhap thanh toan phi
if b_comm='C' then
    b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'BH','TT','N');
    if b_loi is not null then raise PROGRAM_ERROR; end if;
end if;
b_so_id_tt:=FKH_JS_GTRIn(b_oraIn,'so_id_tt');
b_lenh:=FKH_JS_LENHc('dt_ct,dt_dk');
EXECUTE IMMEDIATE b_lenh into dt_ct,dt_dk using b_oraIn;
b_lenh:=FKH_JS_LENH('ngay_ht,htoan,ma_kh,ten,dchi,ma_thue,email,so_ct,pt_tra,
    kvat,kieuhd,layhd,so_don,ngay_bc,ng_hd,vochd,nd,nt_tra,tra,nha_bh');
EXECUTE IMMEDIATE b_lenh into b_ngay_ht,b_htoan,b_ma_kh,b_ten,b_dchi,b_ma_thue,b_email,
    b_so_ct,b_pt_tra,b_kvat,b_kieuHD,b_layHD,b_so_don,b_ngay_bc,b_ng_hd,
    b_vochD,b_nd,b_nt_tra,b_tra,b_nha_bh using dt_ct;
b_nt_tra:=nvl(trim(b_nt_tra),' '); b_tra:=nvl(b_tra,0);
b_nha_bh:=nvl(trim(b_nha_bh),' ');
if b_nt_tra=' ' or FTT_MA_NT_KTRA(b_nt_tra)='K' then
    b_loi:='loi:Sai loai tien tra:loi'; return;
end if;
a_pt(1):=b_pt_tra; a_ma_nt(1):=b_nt_tra; a_tien(1):=b_tra;
b_lenh:=FKH_JS_LENH('so_id,ngay,pt,ma_nt,phi,tien');
EXECUTE IMMEDIATE b_lenh bulk collect into a_so_id,a_ngay,a_pt_tt,a_ma_nt_tt,a_phi,a_tien_tt using dt_dk;
if b_so_id_tt<>0 then
    PBH_HD_TT_XOA_XOA(b_ma_dvi,b_nsd,b_so_id_tt,true,b_loi);
    if b_loi is not null then raise PROGRAM_ERROR; end if;
else
    PHT_ID_MOI(b_so_id_tt,b_loi);
end if;
if b_loi is not null then raise PROGRAM_ERROR; end if;
b_phong:=FHT_MA_NSD_PHONG(b_ma_dvi,b_nsd);
PBH_HD_TT_TEST(b_ma_dvi,b_ngay_ht,b_htoan,b_kvat,b_ma_kh,b_pt_tra,b_nha_bh,b_dvi_tt,b_ma_dl,b_vochD,b_vochK,
    b_phong,b_ttoan_qd,b_thue_qd,b_kieuHD,b_layHD,b_so_don,b_dchi,b_email,
    a_so_id,a_ngay,a_pt_tt,a_ma_nt_tt,a_tien_tt,a_tien_tt_qd,a_thue_tt,a_thue_tt_qd,a_pt,a_ma_nt,a_tien,a_tien_qd,b_loi);
if b_loi is not null then raise PROGRAM_ERROR; end if;
PBH_HD_TT_NH_NH(b_ma_dvi,b_nsd,b_ngay_ht,b_kvat,b_so_id_tt,b_ma_kh,b_pt_tra,b_nha_bh,b_dvi_tt,b_ma_dl,b_vochD,b_vochK,
    b_so_ct,b_phong,b_ten,b_dchi,b_ma_thue,b_ttoan_qd,b_thue_qd,b_kieuHD,b_layHD,b_htoan,b_so_don,b_ngay_bc,b_ng_hd,b_nd,
    a_so_id,a_ngay,a_pt_tt,a_ma_nt_tt,a_phi,a_tien_tt,
    a_tien_tt_qd,a_thue_tt,a_thue_tt_qd,a_pt,a_ma_nt,a_tien,a_tien_qd,dt_ct,b_loi);
if b_loi is not null then raise PROGRAM_ERROR; end if;
select json_object('ma_dvi' value b_ma_dvi,'so_id' value b_so_id_tt) into b_oraOut from dual;
if b_comm='C' then commit; end if;
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 function FBH_TAU_SO_IDn(b_ma_dvi varchar2,b_so_hd varchar2,b_nv varchar2) return number
as
    b_kq number;
begin
-- Nam - Tra so id theo nghiep vu G hoac H
select nvl(min(so_id),0) into b_kq from bh_tau where ma_dvi=b_ma_dvi and so_hd=b_so_hd and nv=b_nv;
return b_kq;
end;

/

create or replace procedure PBH_TAU_SO_ID(
    b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_oraIn clob,b_oraOut out clob)
AS
    b_loi varchar2(100); b_so_id number; b_so_hd varchar2(20):=FKH_JS_GTRIs(b_oraIn,'so_hd');
    b_nv varchar2(1):=FKH_JS_GTRIs(b_oraIn,'nv');
begin
-- Dan - Hoi so ID qua so hop dong
b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'BH','TAU','NX');
if b_loi is not null then raise PROGRAM_ERROR; end if;
b_so_id:=FBH_TAU_SO_IDn(b_ma_dvi,b_so_hd,b_nv);
if b_so_id=0 then b_loi:='loi:So hop dong da xoa:loi'; raise PROGRAM_ERROR; end if;
select json_object('ma_dvi' value b_ma_dvi,'so_id' value b_so_id) 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;