AAA

create or replace procedure PBH_2BG_CT(
    b_ma_dviN varchar2,b_nsd varchar2,b_pas varchar2,b_oraIn clob,b_oraOut out clob)
AS
    b_loi varchar2(100); b_lenh varchar2(1000); b_i1 number;
    b_ma_dvi varchar2(10); b_so_id number; b_so_idD number; b_so_idP number;
    dt_ct clob; dt_dk clob; dt_lt clob:=''; dt_kbt clob:=''; dt_kytt clob:=''; dt_ttt clob:=''; dt_txt clob;
begin
-- Dan - Xem
b_loi:=FHT_MA_NSD_KTRA(b_ma_dviN,b_nsd,b_pas,'BH','2B','X');
if b_loi is not null then raise PROGRAM_ERROR; end if;
b_lenh:=FKH_JS_LENH('ma_dvi,so_id');
EXECUTE IMMEDIATE b_lenh into b_ma_dvi,b_so_id using b_oraIn;
if b_so_id=0 then b_loi:='loi:GCN da xoa:loi'; raise PROGRAM_ERROR; end if;
b_ma_dvi:=nvl(trim(b_ma_dvi),b_ma_dviN);
b_so_idD:=FBH_2B_SO_IDd(b_ma_dvi,b_so_id);
b_so_idP:=FBH_2B_SO_IDp(b_ma_dvi,b_so_id,b_so_idD);
select json_object(a.so_hd,a.ma_kh,'hang' value FBH_2B_HANG_TENl(b.hang),
    'hieu' value b.hieu||'|'||b.hieu,'loai_xe' value FBH_2B_LOAI_TENl(b.loai_xe))
    into dt_ct from bh_2b a,bh_2b_ds b where
    a.ma_dvi=b_ma_dvi and a.so_id=b_so_id and b.ma_dvi=b_ma_dvi and b.so_id=b_so_id;
select JSON_ARRAYAGG(json_object(ngay,tien) order by ngay) into dt_kytt from bh_2b_tt where ma_dvi=b_ma_dvi and so_id=b_so_id;
select JSON_ARRAYAGG(json_object(ma) order by bt returning clob) into dt_dk from bh_2b_dk where ma_dvi=b_ma_dvi and so_id=b_so_id;
select count(*) into b_i1 from bh_2b_txt where ma_dvi=b_ma_dvi and so_id=b_so_id and loai='dt_lt';
if b_i1<>0 then
    select txt into dt_lt from bh_2b_txt where ma_dvi=b_ma_dvi and so_id=b_so_id and loai='dt_lt';
end if;
select count(*) into b_i1 from bh_2b_txt where ma_dvi=b_ma_dvi and so_id=b_so_id and loai='dt_kbt';
if b_i1<>0 then
    select txt into dt_kbt from bh_2b_txt where ma_dvi=b_ma_dvi and so_id=b_so_id and loai='dt_kbt';
end if;
select count(*) into b_i1 from bh_2b_txt where ma_dvi=b_ma_dvi and so_id=b_so_id and loai='dt_ttt';
if b_i1<>0 then
    select txt into dt_ttt from bh_2b_txt where ma_dvi=b_ma_dvi and so_id=b_so_id and loai='dt_ttt';
end if;
select JSON_ARRAYAGG(json_object(loai,txt returning clob) returning clob) into dt_txt
    from bh_2b_txt where ma_dvi=b_ma_dvi and so_id=b_so_id and loai in('dt_ct','dt_dk');
select json_object('ma_dvi' value b_ma_dvi,'so_id' value b_so_id,'so_idp' value b_so_idP,
    'dt_lt' value dt_lt,'dt_kbt' value dt_kbt,'dt_kytt' value dt_kytt,'dt_ttt' value dt_ttt,'dt_ct' value dt_ct,'dt_ct' value dt_ct,
    'dt_dk' value dt_dk,'txt' value dt_txt returning clob) into b_oraOut from dual;
exception when others then if b_loi is null then raise PROGRAM_ERROR; else raise_application_error(-20105,b_loi); end if;
end;
/
create or replace procedure PBH_XE_BPHI_CTd(
    b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_oraIn clob,b_oraOut out clob)
AS
    b_loi varchar2(100); b_lenh varchar2(1000); b_i1 number;
    b_nv varchar2(1); b_so_id number;
    cs_dk clob; cs_dkbs clob; dt_khd clob:=''; cs_kbt clob:=''; cs_lt clob; cs_txt clob;
begin
-- Dan - Tra bieu phi theo so_id
delete temp_1; commit;
b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'BH','XE','');
if b_loi is not null then raise PROGRAM_ERROR; end if;
b_lenh:=FKH_JS_LENH('nv,so_id');
EXECUTE IMMEDIATE b_lenh into b_nv,b_so_id using b_oraIn;
insert into temp_1(c1,n1) select ma,min(bt) from bh_xe_phi_dk where so_id=b_so_id and lh_bh<>'M' group by ma;
select JSON_ARRAYAGG(json_object(
    a.ma,ten,tien,'ptb' value pt,'pt' value '',phi,cap,tc,ma_ct,ma_dk,ma_dkC,kieu,lh_nv,t_suat,lkeM,lkeP,lkeB,luy,lh_bh,'nv' value b_nv)
    order by a.bt returning clob) into cs_dk
    from bh_xe_phi_dk a,temp_1 b where a.so_id=b_so_id and b.n1=a.bt order by a.bt;
select JSON_ARRAYAGG(json_object(
    ma,ten,tien,'ptb' value pt,'pt' value '',phi,cap,tc,ma_ct,ma_dk,ma_dkC,kieu,lh_nv,t_suat,lkeM,lkeP,lkeB,luy,lh_bh,'nv' value b_nv)
    order by bt returning clob) into cs_dkbs from bh_xe_phi_dk where so_id=b_so_id and lh_bh='M' order by bt;
select JSON_ARRAYAGG(json_object(ma_lt,ten) order by bt returning clob) into cs_lt from bh_xe_phi_lt where so_id=b_so_id;
select JSON_ARRAYAGG(json_object(loai,txt returning clob) returning clob) into cs_txt
    from bh_xe_phi_txt where so_id=b_so_id and loai in('dt_dk','dt_dkbs');
select count(*) into b_i1 from bh_xe_phi_txt where so_id=b_so_id and loai='dt_khd';
if b_i1<>0 then
    select txt into dt_khd from bh_xe_phi_txt where so_id=b_so_id and loai='dt_khd';
end if;
select count(*) into b_i1 from bh_xe_phi_txt where so_id=b_so_id and loai='dt_kbt';
if b_i1<>0 then
    select txt into cs_kbt from bh_xe_phi_txt where so_id=b_so_id and loai='dt_kbt';
end if;
select json_object('nv' value b_nv,'dt_dk' value cs_dk,'dt_dkbs' value cs_dkbs, 'dt_khd' value dt_khd,
    'dt_kbt' value cs_kbt,'dt_lt' value cs_lt,'txt' value cs_txt returning clob) into b_oraOut from dual;
delete temp_1; commit;
exception when others then if b_loi is null then raise PROGRAM_ERROR; else raise_application_error(-20105,b_loi); end if;
end;
/
create or replace procedure PBH_2BG_NH_NH(
    b_ma_dvi varchar2,b_nsd varchar2,b_so_id number,
    dt_ct in out clob,dt_dk clob,dt_lt clob,dt_kbt 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_ma_sp varchar2,b_cdich varchar2,b_goi varchar2,
    b_tenC nvarchar2,b_cmtC varchar2,b_mobiC varchar2,
    b_emailC varchar2,b_dchiC nvarchar2,b_ng_huong nvarchar2,
    b_bien_xe varchar2,b_so_khung varchar2,b_so_may varchar2,
    b_hang varchar2,b_hieu varchar2,b_loai_xe varchar2,b_nam_sx number,b_gia number,
    b_nv_bh varchar2,b_so_idP varchar2,b_xe_id number,

    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_ma_bs 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,b_loi out varchar2)
AS
    b_i1 number; b_so_id_kt number:=-1; b_txt clob; b_tien number:=0;
begin
-- Dan - Nhap
b_loi:='loi:Loi Table bh_2b: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_2b_dk values(b_ma_dvi,b_so_id,b_so_idD,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_ma_bs(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));
end loop;
insert into bh_2b 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_2b_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_bien_xe,b_so_khung,b_so_may,b_hang,b_hieu,b_loai_xe,b_nam_sx,b_gia,
    b_nv_bh,b_ma_sp,b_cdich,b_goi,
    b_gio_hl,b_ngay_hl,b_gio_kt,b_ngay_kt,b_ngay_cap,b_giam,b_phi,b_thue,b_ttoan,b_so_idP,b_xe_id);
for b_lp in 1..tt_ngay.count loop
    insert into bh_2b_tt values(b_ma_dvi,b_so_id,tt_ngay(b_lp),tt_tien(b_lp));
end loop;
delete bh_2b_ID where xe_id=b_xe_id;
insert into bh_2b_ID values(b_xe_id,b_tenC,b_cmtC,b_mobiC,b_emailC,b_dchiC,
    b_bien_xe,b_so_khung,b_so_may,b_hang,b_hieu,b_loai_xe,b_nam_sx,b_gia);
PKH_JS_THAYa(dt_ct,'so_hd,ma_kh',b_so_hd||','||b_ma_kh);
insert into bh_2b_txt values(b_ma_dvi,b_so_id,'dt_ct','"'||dt_ct||'"');
insert into bh_2b_txt values(b_ma_dvi,b_so_id,'dt_dk','"'||dt_dk||'"');
insert into bh_2b_txt values(b_ma_dvi,b_so_id,'dt_lt','"'||dt_lt||'"');
insert into bh_2b_txt values(b_ma_dvi,b_so_id,'dt_kbt','"'||dt_kbt||'"');
if b_ttrang in('T','D') then
    insert into bh_2b_kbt values(b_ma_dvi,b_so_id,b_so_idD,'"'||dt_dk||'"','"'||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 '2B','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,'b_phong' value b_ma_cb,
        '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_2b',
        '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;
    if b_ttrang='D' and b_so_hdL='P' then
        PBH_2B_DON(b_ma_dvi,b_so_id,'N',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_XEG_NH_NH(
    b_ma_dvi varchar2,b_nsd varchar2,b_so_id number,
    dt_ct in out clob,dt_dk clob,dt_dkbs clob,dt_lt clob,dt_kbt in out 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_ma_sp varchar2,b_cdich varchar2,b_goi varchar2,
    b_tenC nvarchar2,b_cmtC varchar2,b_mobiC varchar2,
    b_emailC varchar2,b_dchiC nvarchar2,b_ng_huong nvarchar2,
    b_bien_xe varchar2,b_so_khung varchar2,b_so_may varchar2,
    b_hang varchar2,b_hieu varchar2,b_pban varchar2,
    b_loai_xe varchar2,b_nhom_xe varchar2,b_dong varchar2,
    b_dco varchar2,b_ttai number,b_so_cn number,b_nam_sx number,b_gia number,
    b_md_sd varchar2,b_nv_bh varchar2,b_bh_tbo varchar2,b_ktru number,b_so_idP varchar2,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,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_xe: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_xe_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_lh_bh(b_lp));
end loop;
insert into bh_xe 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_xe_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_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_ma_sp,b_cdich,b_goi,
    b_gio_hl,b_ngay_hl,b_gio_kt,b_ngay_kt,b_ngay_cap,b_giam,b_phi,b_thue,b_ttoan,b_so_idP,b_xe_id);
delete bh_xe_ID where xe_id=b_xe_id;
insert into bh_xe_ID values(b_xe_id,b_tenC,b_cmtC,b_mobiC,b_emailC,b_dchiC,
    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);
for b_lp in 1..tt_ngay.count loop
    insert into bh_xe_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_xe_txt values(b_ma_dvi,b_so_id,'dt_ct','"'||dt_ct||'"');
insert into bh_xe_txt values(b_ma_dvi,b_so_id,'dt_dk','"'||dt_dk||'"');
insert into bh_xe_txt values(b_ma_dvi,b_so_id,'dt_lt','"'||dt_lt||'"');
insert into bh_xe_txt values(b_ma_dvi,b_so_id,'dt_kbt','"'||dt_kbt||'"');
if trim(dt_dkbs) is not null then
    insert into bh_xe_txt values(b_ma_dvi,b_so_id,'dt_dkbs','"'||dt_dkbs||'"');
end if;
if dt_ttt is not null then
    insert into bh_xe_txt values(b_ma_dvi,b_so_id,'dt_ttt','"'||dt_ttt||'"');
end if;
if b_ttrang in('T','D') then
  if b_ktru<>0 then
    PBH_XEG_KBT(b_nv_bh,b_so_idP,b_ktru,dt_kbt,b_loi);
      if b_loi is not null then return; end if;
  end if;
    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_xe_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 'XE','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,'b_phong' value b_ma_cb,
        '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_xe',
        '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;
    if b_ttrang='D' and b_so_hdL='P' then
        PBH_XE_DON(b_ma_dvi,b_so_id,'N',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_XEG_CT(
    b_ma_dviN varchar2,b_nsd varchar2,b_pas varchar2,b_oraIn clob,b_oraOut out clob)
AS
    b_loi varchar2(100); b_lenh varchar2(1000); b_i1 number;
    b_ma_dvi varchar2(10); b_so_id number;
    dt_ct clob; dt_dk clob; dt_dkbs clob; dt_lt clob:=''; dt_kbt clob:='';
    dt_kytt clob:=''; dt_ttt clob:=''; dt_txt clob;
begin
-- Dan - Xem
b_loi:=FHT_MA_NSD_KTRA(b_ma_dviN,b_nsd,b_pas,'BH','XE','X');
if b_loi is not null then raise PROGRAM_ERROR; end if;
b_lenh:=FKH_JS_LENH('ma_dvi,so_id');
EXECUTE IMMEDIATE b_lenh into b_ma_dvi,b_so_id using b_oraIn;
if b_so_id=0 then b_loi:='loi:Chon GCN:loi'; raise PROGRAM_ERROR; end if;
b_ma_dvi:=nvl(trim(b_ma_dvi),b_ma_dviN);
select json_object(
    'nhom_xe' value FBH_XE_NHOM_TENl(nhom_xe),'loai_xe' value FBH_XE_LOAI_TENl(loai_xe),
    'hang' value FBH_XE_HANG_TENl(hang), 'hieu' value hieu||'|'||hieu,'pban' value pban||'|'||pban,
    'dong' value FBH_XE_DONG_TENl(dong),'md_sd' value FBH_XE_MDSD_TENl(md_sd) returning clob)
    into dt_ct from bh_xe_ds where so_id=b_so_id;
select JSON_ARRAYAGG(json_object(ngay,tien) order by ngay) into dt_kytt from bh_xe_tt where ma_dvi=b_ma_dvi and so_id=b_so_id;
select JSON_ARRAYAGG(json_object(ma,ten) order by bt returning clob) into dt_dk from bh_xe_dk
    where ma_dvi=b_ma_dvi and so_id=b_so_id and lh_bh <> 'M';
select JSON_ARRAYAGG(json_object(ma,ten) order by bt returning clob) into dt_dkbs from bh_xe_dk
    where ma_dvi=b_ma_dvi and so_id=b_so_id and lh_bh = 'M';
select count(*) into b_i1 from bh_xe_txt where ma_dvi=b_ma_dvi and so_id=b_so_id and loai='dt_lt';
if b_i1<>0 then
    select txt into dt_lt from bh_xe_txt where ma_dvi=b_ma_dvi and so_id=b_so_id and loai='dt_lt';
end if;
select count(*) into b_i1 from bh_xe_txt where ma_dvi=b_ma_dvi and so_id=b_so_id and loai='dt_kbt';
if b_i1<>0 then
    select txt into dt_kbt from bh_xe_txt where ma_dvi=b_ma_dvi and so_id=b_so_id and loai='dt_kbt';
end if;
select count(*) into b_i1 from bh_xe_txt where ma_dvi=b_ma_dvi and so_id=b_so_id and loai='dt_ttt';
if b_i1<>0 then
    select txt into dt_ttt from bh_xe_txt where ma_dvi=b_ma_dvi and so_id=b_so_id and loai='dt_ttt';
end if;
select JSON_ARRAYAGG(json_object(loai,txt returning clob) returning clob) into dt_txt
    from bh_xe_txt where ma_dvi=b_ma_dvi and so_id=b_so_id and loai in('dt_ct','dt_dk','dt_dkbs');
select json_object('ma_dvi' value b_ma_dvi,'so_id' value b_so_id,
    'dt_lt' value dt_lt,'dt_kbt' value dt_kbt,'dt_kytt' value dt_kytt,'dt_ttt' value dt_ttt,
    'dt_ct' value dt_ct,'dt_dk' value dt_dk,'dt_dkbs' value dt_dkbs,'txt' value dt_txt 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;