AAA

create or replace function FBH_BAO_BGHD_PTN(b_ma_dvi varchar2,b_so_id number) return varchar2
AS
    b_kq varchar2(2):=' '; b_i1 number;
begin
-- Nam - Tra da chuyen HD
select count(*) into b_i1 from bh_ptncc where ma_dvi=b_ma_dvi and so_id=b_so_id;
if b_i1<>0 then
  b_kq:='CC';
else
  select count(*) into b_i1 from bh_ptnnn where ma_dvi=b_ma_dvi and so_id=b_so_id;
  if b_i1<>0 then
    b_kq:='NN';
  else
    select count(*) into b_i1 from bh_ptnHang where ma_dvi=b_ma_dvi and so_id=b_so_id;
    if b_i1<>0 then b_kq:='HH'; end if;
  end if;
end if;
return b_kq;
end;

/

create or replace procedure PBH_XE_PTU_TEN
    (b_ma_dviN varchar2,b_nsd varchar2,b_pas varchar2,b_ma varchar2,b_ten out nvarchar2,b_dvi out nvarchar2)
AS
    b_loi varchar2(100);
begin
-- Dan -
b_loi:=FHT_MA_NSD_KTRA(b_ma_dviN,b_nsd,b_pas,'BH','XE','X');
select min(ten),min(dvi) into b_ten,b_dvi from bh_xe_ptu where ma=b_ma;
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_BT_GIA_TEST
    (b_ma_dvi varchar2,b_nsd varchar2,b_ma_dvi_hs in out varchar2,b_so_id_hs number,
    b_n_trinh in out varchar2,b_ng_trinh number,b_ng_duyet number,b_gara varchar2,b_ma_nt varchar2,
    b_nv varchar2,b_hang varchar2,b_hieu varchar2,b_dong varchar2,b_doi varchar2,b_nam varchar2,b_tien_c number,
    t_ptu pht_type.a_var,t_nd pht_type.a_nvar,t_luong pht_type.a_num,t_kieu pht_type.a_var,t_heso pht_type.a_num,
    t_khao pht_type.a_num,t_trinh pht_type.a_num,t_duyet pht_type.a_num,
    t_dvi out pht_type.a_nvar,t_gia out pht_type.a_num,t_bt pht_type.a_num,
    s_ptu pht_type.a_var,s_nd pht_type.a_nvar,s_tyle pht_type.a_num,s_kieu pht_type.a_var,s_heso pht_type.a_num,
    s_tkhao pht_type.a_var,s_hs_son pht_type.a_num,
    s_Glap pht_type.a_num,s_Gsua pht_type.a_num,s_Gson pht_type.a_num,
    s_Tlap pht_type.a_num,s_Tsua pht_type.a_num,s_Tson pht_type.a_num,
    s_dvi out pht_type.a_nvar,s_Clap out pht_type.a_num,s_Csua out pht_type.a_num,s_Cson out pht_type.a_num,s_bt pht_type.a_num,
    k_nd pht_type.a_nvar,k_gia pht_type.a_num,k_trinh pht_type.a_num,k_duyet pht_type.a_num,k_bt pht_type.a_num,b_loi out varchar2)
AS
    b_i1 number; b_ma_dvi_c varchar2(10):=FTBH_DVI_TA(); b_tp number:=0; b_ngay_qd number; b_tg number:=1; b_tkhao varchar2(20);
begin
b_loi:='loi:Nhap ho so boi thuong:loi';
if b_so_id_hs is null then return; end if;
if trim(b_ma_dvi_hs) is null then b_ma_dvi_hs:=b_ma_dvi; end if;
select count(*),min(ngay_qd) into b_i1,b_ngay_qd from bh_bt_chs where ma_dvi=b_ma_dvi_hs and so_id=b_so_id_hs;
if b_i1=0 then
    select count(*),min(ngay_qd) into b_i1,b_ngay_qd from bh_bt_hs where ma_dvi=b_ma_dvi_hs and so_id=b_so_id_hs;
end if;
if b_i1=0 then b_loi:='loi:Ho so boi thuong da xoa:loi'; return; end if;
--if b_ngay_qd<>'01-jan-3000' then b_loi:='loi:Ho so boi thuong da duyet:loi'; return; end if;
if b_ng_trinh is null then b_loi:='loi:Sai ngay trinh:loi'; return; end if;
if b_gara is null then b_loi:='loi:Nhap Gara:loi'; return;
elsif b_nv='TV' then
    b_loi:='loi:Sai Gara '||b_gara||':loi';
    select 0 into b_i1 from bh_ma_gara where ma_dvi=b_ma_dvi_c and ma=b_gara;
end if;
b_loi:='loi:Sai loai tien:loi';
if b_ma_nt is null then return;
elsif b_ma_nt<>'VND' then
    select 0 into b_i1 from tt_ma_nt where ma_dvi=b_ma_dvi and ma=b_ma_nt;
    b_tp:=2;
end if;
if trim(b_n_trinh) is null then
    b_n_trinh:=b_nsd;
else
    b_loi:='loi:Sai nguoi trinh:loi';
    select 0 into b_i1 from ht_ma_cb where ma_dvi=b_ma_dvi and ma=b_n_trinh;
end if;
if b_ng_duyet is null or b_ng_trinh>b_ng_duyet then b_loi:='loi:Sai ngay duyet:loi'; return;
/*elsif b_ng_duyet<30000101 then
    if b_ma_nt<>'VND' then b_tg:=FTT_TRA_TGTT(b_ma_dvi,b_ng_duyet,b_ma_nt); end if;
    if round(b_tien_c*b_tg,0)>FBH_MA_NSD_GIA(b_ma_dvi,b_n_trinh) then
        b_loi:='loi:Duyet qua phan cap:loi'; return;
    end if;*/
end if;
if b_nv is null or b_nv not in('BT','TT','TV') then b_loi:='loi:Sai nghiep vu:loi'; return; end if;
for b_lp in 1..t_nd.count loop
    b_loi:='loi:Loi phu tung thay the dong '||to_char(b_lp)||':loi';
    if t_ptu(b_lp) is null or trim(t_nd(b_lp)) is null or
        t_luong(b_lp) is null or t_luong(b_lp)=0 or t_kieu(b_lp) is null or t_kieu(b_lp) not in ('H','N') or
        t_heso(b_lp) is null or t_khao(b_lp) is null or t_trinh(b_lp) is null or t_duyet(b_lp) is null or t_bt(b_lp) is null then return;
    end if;
    t_dvi(b_lp):=' '; t_gia(b_lp):=0;
    if trim(t_ptu(b_lp)) is not null then
        select dvi into t_dvi(b_lp) from bh_xe_ptu where ma=t_ptu(b_lp);
        if trim(b_hang) is not null then
            select nvl(min(decode(t_kieu(b_lp),'H',Hptu,Nptu)),0) into t_gia(b_lp) from bh_xe_gia where
               hang=b_hang and hieu=b_hieu and dong=b_dong and doi=b_doi and ma=t_ptu(b_lp);
        end if;
    end if;
end loop;
for b_lp in 1..s_nd.count loop
    b_loi:='loi:Loi sua chua dong '||to_char(b_lp)||':loi';
    if s_ptu(b_lp) is null or trim(s_nd(b_lp)) is null or
        s_tyle(b_lp) is null or s_kieu(b_lp) is null or s_kieu(b_lp) not in ('H','N') or s_heso(b_lp) is null or
        s_tkhao(b_lp) is null or s_hs_son(b_lp) is null or
        s_Glap(b_lp) is null or s_Gsua(b_lp) is null or s_Gson(b_lp) is null or
        s_Tlap(b_lp) is null or s_Tsua(b_lp) is null or s_Tson(b_lp) is null or
        trim(s_nd(b_lp)) is null or s_bt(b_lp) is null then return;
    end if;
    if trim(s_tkhao(b_lp)) is not null then
        b_loi:='loi:Sai Gara tham khao'||s_tkhao(b_lp)||':loi';
        select 0 into b_i1 from bh_ma_gara where ma_dvi=b_ma_dvi_c and ma=s_tkhao(b_lp);
    end if;
    s_dvi(b_lp):=' '; s_Clap(b_lp):=0; s_Csua(b_lp):=0; s_Cson(b_lp):=0;
    if trim(s_ptu(b_lp)) is not null then
        b_loi:='loi:Sai phu tung'||s_ptu(b_lp)||':loi';
        select dvi into s_dvi(b_lp) from bh_xe_ptu where ma=s_ptu(b_lp);
        if trim(b_hang) is not null then
            if trim(s_ptu(b_lp)) is null then
                s_Clap(b_lp):=0; s_Csua(b_lp):=0; s_Cson(b_lp):=0;
            else
                if s_kieu(b_lp)='H' then
                    select nvl(min(Hlap),0),nvl(min(Hgo),0),nvl(min(Hson),0) into s_Clap(b_lp),s_Csua(b_lp),s_Cson(b_lp)
                        from bh_xe_gia where hang=b_hang and hieu=b_hieu and dong=b_dong and doi=b_doi and ma=s_ptu(b_lp);
                else
                    select nvl(min(Nlap),0),nvl(min(Ngo),0),nvl(min(Nson),0) into s_Clap(b_lp),s_Csua(b_lp),s_Cson(b_lp)
                        from bh_xe_gia where hang=b_hang and hieu=b_hieu and dong=b_dong and doi=b_doi and ma=s_ptu(b_lp);
                end if;
                if trim(s_tkhao(b_lp)) is not null then b_tkhao:=s_tkhao(b_lp); else b_tkhao:=b_gara; end if;
                select nvl(min(Hson),0) into b_i1 from bh_xe_son where  ma_dvi=b_ma_dvi_c and gara=b_tkhao and
                    hang=b_hang and hieu=b_hieu and dong=b_dong and doi=b_doi and ma=s_ptu(b_lp);
                if b_i1<>0 then s_Cson(b_lp):=b_i1; end if;
            end if;
        end if;
    end if;
end loop;
for b_lp in 1..k_nd.count loop
    if trim(k_nd(b_lp)) is null or k_gia(b_lp) is null or k_trinh(b_lp) is null or k_duyet(b_lp) is null or k_bt(b_lp) is null then
        b_loi:='loi:Loi khac dong '||to_char(b_lp)||':loi'; return;
    end if;
end loop;
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_XEG_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_lt clob; dt_kbt clob; dt_kytt clob; dt_dkbs 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); b_so_hdN varchar2(20); b_ng_huong nvarchar2(500);
    b_ma_sp varchar2(10); b_cdich varchar2(10); b_goi varchar2(10);
    b_tenC nvarchar2(500); b_cmtC varchar2(100); b_mobiC varchar2(20); b_emailC varchar2(100); b_dchiC nvarchar2(500);
    b_bien_xe varchar2(30); b_so_khung varchar2(30); b_so_may varchar2(30);
    b_hang varchar2(20); b_hieu varchar2(20); b_pban varchar2(20);
    b_loai_xe varchar2(500); b_nhom_xe varchar2(500); b_dong varchar2(500);
    b_dco varchar2(1); b_ttai number; b_so_cn number; b_thang_sx number; b_nam_sx number; b_gia number;
    b_md_sd varchar2(500); b_nv_bh varchar2(10); b_bh_tbo varchar2(1); b_ktru number; b_so_idP varchar2(100); b_xe_id 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_lh_bh 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','XE','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,dt_dk,dt_lt,dt_kbt,dt_kytt,dt_dkbs,dt_ttt');
EXECUTE IMMEDIATE b_lenh into dt_ct,dt_dk,dt_lt,dt_kbt,dt_kytt,dt_dkbs,dt_ttt using b_oraIn;
if b_so_id<>0 then
    select count(*) into b_i1 from bh_xe 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_xe
            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_XE_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;
b_lenh:=FKH_JS_LENH('so_hd,so_hdl');
EXECUTE IMMEDIATE b_lenh into b_so_hdN,b_so_hdL using dt_ct;
PBH_HD_JS_TEST(
    b_ma_dvi,b_nsd,b_so_id,b_ngay_htC,'bh_xe',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,'XE');
if b_loi is not null then raise PROGRAM_ERROR; end if;
if b_so_hdL<>'T' then b_so_hd:=b_so_hdN; end if;
-- viet anh
PBH_XEG_TESTr(
    b_ma_dvi,b_nsd,b_so_idD,dt_ct,dt_dk,dt_dkbs,
    b_so_hd,b_so_hdL,b_ma_sp,b_cdich,b_goi,b_tenC,b_cmtC,b_mobiC,b_emailC,b_dchiC,b_ng_huong,
    b_bien_xe,b_so_khung,b_so_may,b_hang,b_hieu,b_pban,b_loai_xe,b_nhom_xe,b_dong,
    b_dco,b_ttai,b_so_cn,b_thang_sx,b_nam_sx,b_gia,b_md_sd,b_nv_bh,b_bh_tbo,b_ktru,b_so_idP,b_xe_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_ptG,dk_phiG,dk_lkeP,dk_lkeB,dk_lh_bh,b_loi);
if b_loi is not null then raise PROGRAM_ERROR; end if;
PBH_XEG_NH_NH(
    b_ma_dvi,b_nsd,b_so_id,dt_ct,dt_dk,dt_dkbs,dt_lt,dt_kbt,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_ma_sp,b_cdich,b_goi,b_tenC,b_cmtC,b_mobiC,b_emailC,b_dchiC,b_ng_huong,
    b_bien_xe,b_so_khung,b_so_may,b_hang,b_hieu,b_pban,b_loai_xe,b_nhom_xe,
    b_dong,b_dco,b_ttai,b_so_cn,b_nam_sx,b_gia,b_md_sd,b_nv_bh,b_bh_tbo,b_ktru,b_so_idP,b_xe_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_ptG,dk_phiG,dk_lkeP,dk_lkeB,dk_lh_bh,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_HANGH_TESTr(
    b_ma_dvi varchar2,b_nsd varchar2,
    dt_ct clob,dt_ds clob,dt_dk clob,dt_pt clob,
    b_so_idP out number, b_so_idG number,
    b_ma_qtac out varchar2,b_ma_nhang out varchar2,b_hd_kem out varchar2,b_c_ctai out varchar2,b_ma_vchuyen out varchar2,
    b_khoang_cach out number,b_thoi_gian out number,b_gdinh out varchar2,b_cang_di out varchar2, b_cang_den out varchar2,
    b_noi_di out varchar2,b_noi_den out varchar2,b_ma_dkgh out varchar2, b_ma_pptinh out varchar2,
    b_tong_mtnH out number,b_tpH out number,b_pphiH out number,

    b_cmtH out varchar2, b_mobiH out varchar2, b_emailH out varchar2,
    b_loai_khH out varchar2, b_tenH out nvarchar2, b_dchiH out nvarchar2,
    -- thong tin hang hoa
    ds_ma_lhang out pht_type.a_var,ds_ten_hang out pht_type.a_var,ds_dvi_tinh out pht_type.a_var,
    ds_ma_dgoi out pht_type.a_var,ds_cphi out pht_type.a_num,ds_sluong out pht_type.a_num,
    ds_gia out pht_type.a_num,ds_gia_tri out pht_type.a_num,ds_mtn out pht_type.a_num,ds_pt out pht_type.a_num,
    ds_lh_nv out pht_type.a_var,ds_lkeB out pht_type.a_var,
    -- thong tin phuong tien
    ds_ma_ptien out pht_type.a_var, ds_ten_ptien out pht_type.a_var, ds_so_imo out pht_type.a_var, ds_so_vdon out pht_type.a_var,
    -- dieu khoan bao hiem
    dk_ma out pht_type.a_var,dk_ma_hang 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_ma_dkC 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,dk_lh_bh out pht_type.a_var,
    b_loi out varchar2)
AS
    b_lenh varchar2(2000); b_i1 number; b_txt clob; b_kieu_hdH varchar2(1); b_ttrang varchar2(1);
    b_ma_khH varchar2(20); b_c_thueH varchar2(1); b_nt_tienH varchar2(5);b_nt_phiH varchar2(5);
    b_thueH number; b_ttoanH number;b_tygia number; b_hd_kemG varchar2(1);
    dk_phiB pht_type.a_num;
    dt_khd clob;
begin
-- Nam - Nhap
b_lenh:=FKH_JS_LENH('kieu_hd,ttrang,ma_qtac,ma_nhang,vchuyen,khoang_cach,thoi_gian,cang_di,cang_den,
    noi_di,noi_den,ma_pptinh,hd_kem,gdinh,ma_dkgh,c_ctai,loai_khd,cmtd,mobid,emaild,tend,dchid,c_thue,nt_tien,nt_phi,tp,tong_mtnh,pphi,thue,ttoan,tygia');
EXECUTE IMMEDIATE b_lenh into b_kieu_hdH,b_ttrang,b_ma_qtac,b_ma_nhang,b_ma_vchuyen,b_khoang_cach,b_thoi_gian,
    b_cang_di,b_cang_den,b_noi_di,b_noi_den,b_ma_pptinh,b_hd_kem,b_gdinh,b_ma_dkgh,b_c_ctai,b_loai_khH,b_cmtH,b_mobiH,b_emailH,b_tenH,b_dchiH,
    b_c_thueH,b_nt_tienH,b_nt_phiH,b_tpH,b_tong_mtnH,b_pphiH,b_thueH,b_ttoanH,b_tygia using dt_ct;
b_c_thueH:=nvl(trim(b_c_thueH),'C');
b_cang_di:=PKH_MA_TENl(b_cang_di); b_cang_den:=PKH_MA_TENl(b_cang_den);
b_ma_qtac:=PKH_MA_TENl(b_ma_qtac); b_ma_nhang:=PKH_MA_TENl(b_ma_nhang);
b_gdinh:=PKH_MA_TENl(b_gdinh); b_ma_vchuyen:=PKH_MA_TENl(b_ma_vchuyen);
b_ma_dkgh:=PKH_MA_TENl(b_ma_dkgh); b_ma_pptinh:=PKH_MA_TENl(b_ma_pptinh);
b_thoi_gian:=nvl(b_thoi_gian,0); b_khoang_cach:=nvl(b_khoang_cach,0);
if nvl(trim(b_nt_phiH),'VND')<>'VND' then b_tpH:=2; end if;
b_lenh:=FKH_JS_LENH('ma_pt,ten_pt,so_imo,so_vdon');
EXECUTE IMMEDIATE b_lenh bulk collect into ds_ma_ptien,ds_ten_ptien,ds_so_imo,ds_so_vdon using dt_pt;
if ds_ma_ptien.count = 0 then b_loi:='loi:Nhap thong tin phuong tien cho hang hoa:loi'; return; end if;
b_lenh:=FKH_JS_LENH('ma_hang,ten,dvi_tinh,dgoi,cphi,sluong,gia,gia_tri,mtn,pt,lkeB,lh_nv');
EXECUTE IMMEDIATE b_lenh bulk collect into ds_ma_lhang,ds_ten_hang,ds_dvi_tinh,
        ds_ma_dgoi,ds_cphi,ds_sluong,ds_gia,ds_gia_tri,ds_mtn,ds_pt,ds_lkeB,ds_lh_nv using dt_ds;
if ds_ma_lhang.count=0 then b_loi:='loi:Nhap danh sach hang hoa bao hiem:loi'; return; end if;
for b_lp in 1..ds_ma_lhang.count loop
    if trim(ds_ten_hang(b_lp)) is null then b_loi:='loi:ten hang bao hiem dong '||to_char(b_lp)||':loi'; return; end if;
    b_loi:='loi:Sai ma loai hang '||ds_ten_hang(b_lp)||':loi';
    if ds_ma_lhang(b_lp) is null then return; end if;
    ds_ma_lhang(b_lp):=PKH_MA_TENl(ds_ma_lhang(b_lp));
    if FBH_HANG_LHANG_HAN(ds_ma_lhang(b_lp))<>'C' then return; end if;
    b_loi:='loi:Them gia tri hang '||ds_ten_hang(b_lp)||':loi';
    if ds_gia_tri(b_lp) is null or ds_gia_tri(b_lp) <=0 then return; end if;
end loop;
if b_kieu_hdH='K' then 
   select hd_kem into b_hd_kemG from bh_hang where ma_dvi=b_ma_dvi and so_id=b_so_idG;
   if b_hd_kemG<>'C' then b_loi:='loi:Khong tao hop dong kem tu hop dong goc mot chuyen:loi';return; end if;
end if;
FBH_HANG_PHI(dt_ct,dt_dk,dt_ds,b_so_idP,
    dk_ma,dk_ma_hang,dk_ten,dk_tc,dk_ma_ct,dk_kieu,dk_lkeP,dk_lkeB,dk_luy,dk_ma_dk,dk_ma_dkC,
    dk_lh_nv,dk_t_suat,dk_cap,dk_lh_bh,dk_tien,dk_pt,dk_phi,dk_thue,dk_ttoan,dk_ptB,dk_ptG,dk_phiG,b_loi);
if b_loi is not null then return; end if;
for b_lp in 1..dk_ma.count loop
    dk_phiB(b_lp):=dk_phi(b_lp)+dk_phiG(b_lp);
end loop;
PBH_HD_THAY_PHI(b_nt_tienH,b_nt_phiH,b_tygia,b_thueH,b_ttoanH,
    dk_lh_nv,dk_tien,dk_ptB,dk_phiB,dk_phi,dk_thue,dk_ttoan,dk_ptG,dk_phiG,b_loi);
if b_loi is not null then return; end if;
for b_lp in 1..dk_ma.count loop
    dk_ptG(b_lp):=0; dk_phiG(b_lp):=0;
    if dk_ptB(b_lp)<>0 then
        dk_ptG(b_lp):=dk_ptB(b_lp)-dk_pt(b_lp);
        if dk_ptB(b_lp)>=100 then dk_ptG(b_lp):=round(dk_ptG(b_lp)*100/dk_ptB(b_lp),2); end if;
    elsif dk_phiB(b_lp)<>0 then
        dk_ptG(b_lp):=dk_phiB(b_lp)-dk_phi(b_lp);
        dk_ptG(b_lp):=round(dk_ptG(b_lp)*100/dk_phiB(b_lp),2);
    end if;
end loop;
if b_ttrang in ('T','D') then
    b_lenh:=FKH_JS_LENH('ma_khd,loai_khd,tend,dchid,cmtd,mobid,emaild');
    EXECUTE IMMEDIATE b_lenh into b_ma_khH,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);
    end if;
    select count(*) into b_i1 from bh_hang_phi_txt where so_id=b_so_idP and loai='dt_khd';
    if b_i1<>0 then
        select txt into dt_khd from bh_hang_phi_txt where so_id=b_so_idP and loai='dt_khd';
        dt_khd:=FKH_JS_BONH(b_txt);
        PBH_HANGH_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_HANGH_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_ds clob; dt_pt clob; dt_dk 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_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_so_idP number;
    b_ma_qtac varchar2(500); b_ma_nhang varchar2(500); b_hd_kem varchar2(1); b_c_ctai varchar2(1); b_ma_vchuyen varchar2(500);
    b_khoang_cach number; b_thoi_gian number; b_gdinh varchar2(500); b_cang_di varchar2(500); b_cang_den varchar2(500); b_noi_di varchar2(500);
    b_noi_den varchar2(500); b_ma_dkgh varchar2(500); b_ma_pptinh varchar2(500); b_tong_mtn number; b_tp number; b_pphi number;
    b_cmtH varchar2(20); b_mobiH varchar2(20); b_emailH varchar2(100); b_loai_khH varchar2(1); b_tenH nvarchar2(400); b_dchiH nvarchar2(1000);
    -- danh sach hang hoa
    ds_ma_lhang pht_type.a_var; ds_ten_hang pht_type.a_var; ds_dvi_tinh pht_type.a_var;
    ds_ma_dgoi pht_type.a_var; ds_cphi pht_type.a_num; ds_sluong pht_type.a_num; ds_gia pht_type.a_num;
    ds_gia_tri pht_type.a_num; ds_mtn pht_type.a_num; ds_pt pht_type.a_num;
    ds_lh_nv pht_type.a_var;ds_lkeB pht_type.a_var;
    -- thong tin phuong tien
    ds_ma_ptien pht_type.a_var; ds_ten_ptien pht_type.a_var; ds_so_imo pht_type.a_var; ds_so_vdon pht_type.a_var;
    ---- dieu khoan
    dk_ma pht_type.a_var; dk_ma_hang 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_ma_dkC 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; dk_lh_bh 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','HANG','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,dt_ds,dt_pt,dt_dk,dt_lt,dt_kbt,dt_kytt,dt_ttt');
   EXECUTE IMMEDIATE b_lenh into dt_ct,dt_ds,dt_pt,dt_dk,dt_lt,dt_kbt,dt_kytt,dt_ttt using b_oraIn;
if b_so_id<>0 then
    select count(*) into b_i1 from bh_hang 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_hang
            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_HANGH_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);
end if;
if b_loi is not null then raise PROGRAM_ERROR; end if;
PBH_HD_JS_TEST(
    b_ma_dvi,b_nsd,b_so_id,b_ngay_htC,'bh_hang',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,'HANG');
if b_loi is not null then raise PROGRAM_ERROR; end if;
if b_ngay_hl in(0,30000101) then b_ngay_hl:=b_ngay_cap; end if;
if b_ngay_kt in(0,30000101) then b_ngay_kt:=PKH_NG_CSO(add_months(PKH_SO_CDT(b_ngay_hl),12)); end if;
if(b_kieu_hd = 'U') then
    PBH_HANGH_NH_U(
      b_ma_dvi,b_nsd,b_so_id,dt_ct,
      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_so_idG,b_so_idD,b_ngayD,b_phong,b_hhong,tt_ngay,tt_tien,b_loi);
    if b_loi is not null then raise PROGRAM_ERROR; end if;
else
    PBH_HANGH_TESTr(
      b_ma_dvi,b_nsd,
      dt_ct,dt_ds,dt_dk,dt_pt,b_so_idP,b_so_idG,
      b_ma_qtac,b_ma_nhang,b_hd_kem,b_c_ctai,b_ma_vchuyen,
      b_khoang_cach,b_thoi_gian,b_gdinh,b_cang_di,b_cang_den,b_noi_di,
      b_noi_den,b_ma_dkgh,b_ma_pptinh,b_tong_mtn,b_tp,b_pphi,
      b_cmtH,b_mobiH,b_emailH,b_loai_khH,b_tenH,b_dchiH,
      -- danh sach hang
      ds_ma_lhang,ds_ten_hang,ds_dvi_tinh,ds_ma_dgoi,ds_cphi,ds_sluong,ds_gia,ds_gia_tri,ds_mtn,ds_pt,
      ds_lh_nv,ds_lkeB,
      -- thong tin phuong tien
      ds_ma_ptien, ds_ten_ptien, ds_so_imo, ds_so_vdon,
      -- dieu khoan bao hiem
      dk_ma,dk_ma_hang,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_ma_dkC,
      dk_lh_nv,dk_t_suat,dk_ptB,dk_ptG,dk_phiG,dk_lkeP,dk_lkeB,dk_luy,dk_lh_bh,b_loi);
   if b_loi is not null then raise PROGRAM_ERROR; end if;
   PBH_HANGH_NH_NH(
      b_ma_dvi,b_nsd,b_so_id,
      dt_ct,dt_ds,dt_dk,dt_lt,dt_kbt,dt_pt,dt_ttt,
      b_so_hd,b_ngay_ht,b_ttrang,b_kieu_hd,b_so_hd_g,b_so_idG,b_so_idD,
      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_ma_qtac,b_ma_nhang,b_hd_kem,b_c_ctai,b_ma_vchuyen,b_khoang_cach,b_thoi_gian,b_cang_di,
      b_cang_den,b_ngay_cap,b_c_thue,b_tong_mtn,b_phi,b_giam,b_thue,b_ttoan,b_nt_tien,b_nt_phi,b_hhong,
      tt_ngay,tt_tien,
      -- danh sach hang
      ds_ma_lhang,ds_ten_hang,ds_dvi_tinh,ds_ma_dgoi,ds_cphi,ds_sluong,ds_gia,ds_gia_tri,ds_mtn,ds_pt,
      ds_lh_nv,ds_lkeB,
      -- thong tin phuong tien
      ds_ma_ptien, ds_ten_ptien, ds_so_imo, ds_so_vdon,
      -- dieu khoan bao hiem
      dk_ma,dk_ma_hang,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_ma_dkC,
      dk_lh_nv,dk_t_suat,dk_ptB,dk_ptG,dk_phiG,dk_lkeP,dk_lkeB,dk_luy,dk_lh_bh,b_loi);
   if b_loi is not null then raise PROGRAM_ERROR; end if;
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_HANG_BPHI_NH(
    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_i1 number; b_ma_ct varchar2(10);
    b_kt number; b_i2 number;
    -- thong tin chung
    b_ma_pt varchar2(10); b_ma_nhang varchar2(10); b_ma_qtac varchar2(10);
    b_khoang_cach number; b_thoi_gian number; b_ngay_bd number; b_ngay_kt number;
    -- danh sach hang
    ds_ma pht_type.a_var; ds_ten pht_type.a_nvar; ds_dgoi pht_type.a_var; ds_pt pht_type.a_num;
    -- dieu khoan chinh
    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_ma_dk 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_lkeP pht_type.a_var; dk_lkeB pht_type.a_var; dk_luy pht_type.a_var;
    dk_lh_nv pht_type.a_var; dk_t_suat pht_type.a_num; dk_lh_bh pht_type.a_var;
    -- dieu khoan bo sung
    dkB_ma pht_type.a_var; dkB_ten pht_type.a_nvar; dkB_tc pht_type.a_var; dkB_ma_ct pht_type.a_var;
    dkB_kieu pht_type.a_var; dkB_tien pht_type.a_num; dkB_pt pht_type.a_num;
    dkB_phi pht_type.a_num; dkB_lkeP pht_type.a_var; dkB_lkeB pht_type.a_var; dkB_luy pht_type.a_var;
    dkB_ma_dk pht_type.a_var;

    lt_ma_dk pht_type.a_var; lt_ma_lt pht_type.a_var; lt_ten pht_type.a_nvar;
    b_dt_ct clob; b_dt_ds clob; b_dt_dk clob:=''; b_dt_dkbs clob:=''; b_dt_lt clob:=''; b_dt_khd clob:=''; b_dt_kbt clob:=''; b_so_id number;
begin
-- Dan - Nhap
b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'BH','HANG','M');
if b_loi is not null then raise PROGRAM_ERROR; end if;
b_lenh:=FKH_JS_LENHc('dt_ct,dt_ds,dt_dk,dt_dkbs,dt_lt,dt_khd,dt_kbt');
EXECUTE IMMEDIATE b_lenh into b_dt_ct,b_dt_ds,b_dt_dk,b_dt_dkbs,b_dt_lt,b_dt_khd,b_dt_kbt using b_oraIn;
b_lenh:=FKH_JS_LENH('pt,nhang,qtac,khoang_cach,thoi_gian,ngay_bd,ngay_kt');
EXECUTE IMMEDIATE b_lenh into b_ma_pt,b_ma_nhang,b_ma_qtac,b_khoang_cach,b_thoi_gian,b_ngay_bd,b_ngay_kt using b_dt_ct;
if trim(b_ma_pt) is null then
    b_loi:='loi:Nhap van chuyen:loi'; raise PROGRAM_ERROR;
end if;
if trim(b_ma_nhang) is null then
    b_loi:='loi:Nhap nhom hang:loi'; raise PROGRAM_ERROR;
end if;
if trim(b_ma_qtac) is null then
    b_loi:='loi:Nhap quy tac:loi'; raise PROGRAM_ERROR;
end if;
if b_khoang_cach > 0  and b_thoi_gian > 0 then
    b_loi:='loi:Chi nhap khoang cach hoac thoi gian:loi'; raise PROGRAM_ERROR;
end if;
if b_khoang_cach is null then b_khoang_cach:=0; end if;
if b_thoi_gian is null then b_thoi_gian:=0; end if;
if b_ngay_bd is null or b_ngay_bd in(0,30000101) then b_ngay_bd:=PKH_NG_CSO(sysdate); end if;
if b_ngay_kt is null or b_ngay_kt=0 then b_ngay_kt:=30000101; end if;
b_lenh:=FKH_JS_LENH('ma,ten,dgoi,pt');
EXECUTE IMMEDIATE b_lenh bulk collect into ds_ma,ds_ten,ds_dgoi,ds_pt using b_dt_ds;
b_kt:=ds_ma.count;
if b_kt=0 then b_loi:='loi:Nhap danh sach:loi'; raise PROGRAM_ERROR; end if;
for b_lp in 1..ds_ma.count loop
    b_loi:='loi:Sai chi tiet dong '||to_char(b_lp)||':loi';
    ds_ma(b_lp):=nvl(trim(ds_ma(b_lp)),' ');
    ds_dgoi(b_lp):=nvl(trim(ds_dgoi(b_lp)),' ');
    if ds_ma(b_lp)=' ' then return; end if;
    ds_ten(b_lp):=FBH_HANG_LOAI_TEN(ds_ma(b_lp));
    b_i1:=b_lp+1;
    for b_lp1 in b_i1..ds_ma.count loop
        if ds_ma(b_lp)=ds_ma(b_lp1) and ds_dgoi(b_lp)=ds_dgoi(b_lp1) then b_loi:='loi:Trung ma hang: '||ds_ma(b_lp)||':loi'; raise PROGRAM_ERROR;
        end if;
    end loop;
end loop;
b_lenh:=FKH_JS_LENH('ma,ten,tc,ma_ct,ma_dk,kieu,tien,pt,phi,lkep,lkeb,luy');
EXECUTE IMMEDIATE b_lenh bulk collect into
    dk_ma,dk_ten,dk_tc,dk_ma_ct,dk_ma_dk,dk_kieu,dk_tien,dk_pt,dk_phi,dk_lkeP,dk_lkeB,dk_luy using b_dt_dk;
b_kt:=dk_ma.count;
if b_kt=0 then b_loi:='loi:Nhap dieu khoan:loi'; raise PROGRAM_ERROR; end if;
for b_lp in 1..b_kt loop dk_lh_bh(b_lp):='C'; end loop;
if trim(b_dt_dkbs) is not null then
    EXECUTE IMMEDIATE b_lenh bulk collect into
        dkB_ma,dkB_ten,dkB_tc,dkB_ma_ct,dkB_ma_dk,dkB_kieu,dkB_tien,dkB_pt,dkB_phi,
        dkB_lkeP,dkB_lkeB,dkB_luy using b_dt_dkbs;
    for b_lp in 1..dkB_ma.count loop
        b_kt:=b_kt+1; dk_lh_bh(b_kt):='M';
        dk_ma(b_kt):=dkB_ma(b_lp); dk_ten(b_kt):=dkB_ten(b_lp); dk_tc(b_kt):=dkB_tc(b_lp);
        dk_ma_ct(b_kt):=dkB_ma_ct(b_lp); dk_ma_dk(b_kt):=dkB_ma_dk(b_lp); dk_kieu(b_kt):=dkB_kieu(b_lp);
        dk_tien(b_kt):=dkB_tien(b_lp); dk_pt(b_kt):=dkB_pt(b_lp); dk_phi(b_kt):=dkB_phi(b_lp);
        dk_lkeP(b_kt):=dkB_lkeP(b_lp); dk_lkeB(b_kt):=dkB_lkeB(b_lp); dk_luy(b_kt):=dkB_luy(b_lp);
    end loop;
end if;
for b_lp in 1..dk_ma.count loop
    b_loi:='loi:Sai chi tiet dong '||to_char(b_lp)||':loi';
    dk_ma(b_lp):=nvl(trim(dk_ma(b_lp)),' ');
    if dk_ma(b_lp)=' ' then return; end if;
    if trim(dk_ten(b_lp)) is null then
        b_i1:=b_lp-1; b_i2:=0;
        for b_lp1 in 1..b_i1 loop
            if dk_ma(b_lp1)=dk_ma(b_lp) then b_i2:=1; exit; end if;
        end loop;
        if b_i2=0 then return; end if;
    end if;
    dk_tc(b_lp):=nvl(trim(dk_tc(b_lp)),'C');
    dk_ma_ct(b_lp):=nvl(trim(dk_ma_ct(b_lp)),' ');
    if dk_ma(b_lp)=dk_ma_ct(b_lp) then b_loi:='loi:Trung ma cap tren ma: '||dk_ma(b_lp)||':loi'; raise PROGRAM_ERROR; end if;
    if dk_tc(b_lp)='C' then
        dk_lkeP(b_lp):=nvl(trim(dk_lkeP(b_lp)),'G'); dk_lkeB(b_lp):=nvl(trim(dk_lkeB(b_lp)),'G');
    else
        dk_lkeP(b_lp):=nvl(trim(dk_lkeP(b_lp)),'T'); dk_lkeB(b_lp):=nvl(trim(dk_lkeB(b_lp)),'T');
    end if;
    dk_luy(b_lp):=nvl(trim(dk_luy(b_lp)),'C'); dk_kieu(b_lp):=nvl(trim(dk_kieu(b_lp)),'T');
    dk_ma_dk(b_lp):=nvl(trim(dk_ma_dk(b_lp)),' '); dk_lh_nv(b_lp):=' '; dk_t_suat(b_lp):=0;
    if dk_ma_dk(b_lp)<>' ' then
        if dk_lh_bh(b_lp)='C' then
            if FBH_MA_DK_HAN(dk_ma_dk(b_lp))='K' then
                b_loi:='loi:Dieu khoan: '||dk_ma_dk(b_lp)||' da het su dung:loi'; raise PROGRAM_ERROR;
            end if;
            select nvl(min(lh_nv),' ') into dk_lh_nv(b_lp) from bh_ma_dk where ma=dk_ma_dk(b_lp);
        else
            if FBH_MA_DKBS_HAN(dk_ma_dk(b_lp))='K' then
                b_loi:='loi:Dieu khoan bo sung: '||dk_ma_dk(b_lp)||' da het su dung:loi'; raise PROGRAM_ERROR;
            end if;
            select nvl(min(lh_nv),' ') into dk_lh_nv(b_lp) from bh_ma_dkbs where ma=dk_ma_dk(b_lp);
        end if;
        if dk_lh_nv(b_lp)<>' ' then dk_t_suat(b_lp):=FBH_MA_LHNV_THUE(dk_lh_nv(b_lp)); end if;
    end if;
end loop;
for b_lp in 1..dk_ma.count loop
    b_i1:=b_lp+1;
    for b_lp1 in b_i1..dk_ma.count loop
        if dk_ma(b_lp)=dk_ma(b_lp1) and dk_tien(b_lp)=dk_tien(b_lp1) then b_loi:='loi:Trung ma: '||dk_ma(b_lp)||':loi'; raise PROGRAM_ERROR;
        end if;
    end loop;
    if dk_tc(b_lp)='T' then
        b_i1:=0;
        for b_lp1 in 1..dk_ma.count loop
            if dk_ma(b_lp)=dk_ma_ct(b_lp1) and dk_tc(b_lp1)<>'K' then b_i1:=1; exit; end if;
        end loop;
        if b_i1=0 then b_loi:='loi:Nhap ma chi tiet ma: '||dk_ma(b_lp)||':loi'; raise PROGRAM_ERROR; end if;
    end if;
end loop;
for b_lp in 1..dk_ma.count loop
    if dk_tc(b_lp)='C' and dk_ma_ct(b_lp)!=' ' then
        b_i1:=0;
        for b_lp1 in 1..dk_ma.count loop
            if dk_ma(b_lp1)=dk_ma_ct(b_lp) and dk_tc(b_lp1)='T' then b_i1:=1; exit; end if;
        end loop;
        if b_i1=0 then b_loi:='loi:Nhap ma cap tren ma: '||dk_ma(b_lp)||':loi'; raise PROGRAM_ERROR; end if;
    end if;
end loop;
for b_lp in 1..dk_ma.count loop
    if dk_tc(b_lp)='C' and dk_ma_ct(b_lp)!=' ' then
        b_lenh:=dk_ma(b_lp); b_ma_ct:=dk_ma_ct(b_lp);
        while b_ma_ct<>' ' loop
            b_i1:=0;
            for b_lp1 in 1..dk_ma.count loop
                if dk_ma(b_lp1)=b_ma_ct then b_i1:=b_lp1; exit; end if;
            end loop;
            if b_i1=0 then
                b_ma_ct:='';
            else
                b_ma_ct:=dk_ma_ct(b_i1);
                if PKH_MA_MA(b_lenh,b_ma_ct) then b_loi:='loi:Vong lap ma cap tren ma: '||dk_ma(b_lp)||':loi'; raise PROGRAM_ERROR; end if;
                b_lenh:=b_lenh||','||dk_ma(b_i1);
            end if;
        end loop;
    end if;
end loop;
if trim(b_dt_lt) is not null then
    b_lenh:=FKH_JS_LENH('ma_dk,ma_lt,ten');
    EXECUTE IMMEDIATE b_lenh bulk collect into lt_ma_dk,lt_ma_lt,lt_ten using b_dt_lt;
    for b_lp in 1..lt_ma_lt.count loop
        if trim(lt_ma_lt(b_lp)) is null then
            b_loi:='loi:Nhap ma loai tru dong '||to_char(b_lp)||':loi'; raise PROGRAM_ERROR;
        end if;
        if trim(lt_ma_dk(b_lp)) is null then lt_ma_dk(b_lp):=' '; end if;
        if FBH_MA_DKLT_HAN(lt_ma_dk(b_lp),lt_ma_lt(b_lp))='K' then
            b_loi:='loi:Dieu khoan loai tru: '||lt_ma_lt(b_lp)||' da het su dung:loi'; raise PROGRAM_ERROR;
        end if;
    end loop;
end if;
b_so_id:=FBH_HANGH_BPHI_SO_ID(b_ma_pt,b_ma_nhang,b_ma_qtac,b_khoang_cach,b_thoi_gian);
if b_so_id<>0 then
    delete bh_hang_phi_txt where so_id=b_so_id;
    delete bh_hang_phi_lt where so_id=b_so_id;
    delete bh_hang_phi_dk where so_id=b_so_id;
    delete bh_hang_phi_ds where so_id=b_so_id;
    delete bh_hang_phi where so_id=b_so_id;
else
    PHT_ID_MOI(b_so_id,b_loi);
    if b_loi is not null then raise PROGRAM_ERROR; end if;
end if;
b_loi:='loi:Loi Table bh_hang_phi:loi';
insert into bh_hang_phi values(b_ma_dvi,b_so_id,b_ma_pt,b_ma_nhang,b_ma_qtac,b_khoang_cach,b_thoi_gian,b_ngay_bd,b_ngay_kt,b_nsd);
insert into bh_hang_phi_txt values(b_ma_dvi,b_so_id,'dt_ct','"'||b_dt_ct||'"');
insert into bh_hang_phi_txt values(b_ma_dvi,b_so_id,'dt_dk','"'||b_dt_dk||'"');
insert into bh_hang_phi_txt values(b_ma_dvi,b_so_id,'dt_dkbs','"'||b_dt_dkbs||'"');
insert into bh_hang_phi_txt values(b_ma_dvi,b_so_id,'dt_ds','"'||b_dt_ds||'"');
for b_lp in 1..dk_ma.count loop
    insert into bh_hang_phi_dk values(b_ma_dvi,b_so_id,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_lkeP(b_lp),dk_lkeB(b_lp),dk_luy(b_lp),
        dk_ma_dk(b_lp),dk_lh_nv(b_lp),dk_t_suat(b_lp),0,dk_lh_bh(b_lp));
end loop;
for b_lp in 1..ds_ma.count loop
    insert into bh_hang_phi_ds values(b_ma_dvi,b_so_id,b_lp,ds_ma(b_lp),ds_ten(b_lp),ds_dgoi(b_lp),ds_pt(b_lp));
end loop;
for r_lp in(select ma,ma_ct,so_id,bt,level from (select t.so_id,t.bt,t.ma,t.ma_ct from bh_hang_phi_dk t where t.so_id = b_so_id) t start with t.ma_ct=' ' CONNECT BY prior t.ma=t.ma_ct) loop
    update bh_hang_phi_dk set cap=r_lp.level where so_id=b_so_id and bt=r_lp.bt;
end loop;
if lt_ma_lt.count<>0 then
    for b_lp in 1..lt_ma_lt.count loop
        insert into bh_hang_phi_lt values(b_ma_dvi,b_so_id,b_lp,lt_ma_dk(b_lp),lt_ma_lt(b_lp),lt_ten(b_lp));
    end loop;
    insert into bh_hang_phi_txt values(b_ma_dvi,b_so_id,'dt_lt','"'||b_dt_lt||'"');
end if;
if trim(b_dt_khd) is not null then
    insert into bh_hang_phi_txt values(b_ma_dvi,b_so_id,'dt_khd','"'||b_dt_khd||'"');
end if;
if trim(b_dt_kbt) is not null then
    insert into bh_hang_phi_txt values(b_ma_dvi,b_so_id,'dt_kbt','"'||b_dt_kbt||'"');
end if;
commit;
select json_object('so_id' value b_so_id) 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_MA_QTAC_LKE(
    b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_oraIn clob,b_oraOut out clob)
AS
    b_loi varchar2(100); b_dong number:=0; cs_lke clob:=''; b_tim nvarchar2(100);
    b_lenh varchar2(1000); b_tu number; b_den number;
begin
-- Nam
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;
b_lenh:=FKH_JS_LENH('tim,tu,den');
EXECUTE IMMEDIATE b_lenh into b_tim,b_tu,b_den using b_oraIn;
if b_tim is null then  -- nam them tim kiem
  select count(*) into b_dong from bh_ma_qtac;
  PKH_LKE_TRANG(b_dong,b_tu,b_den);
  select JSON_ARRAYAGG(obj) into cs_lke from
    (select json_object(ma,ten,nsd) obj,rownum sott from bh_ma_qtac order by ten)
            where sott between b_tu and b_den;
else 
  select count(*) into b_dong from bh_ma_qtac where upper(ten) like b_tim;
    PKH_LKE_TRANG(b_dong,b_tu,b_den);
    select JSON_ARRAYAGG(json_object(ma,ten,nsd,'xep' value ma) returning clob) into cs_lke from
        (select a.*,rownum sott from bh_ma_qtac a where upper(ten) like b_tim order by ma)
    where sott between b_tu and b_den;
end if;
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;