AAA

create or replace procedure PBH_HANGB_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; b_lan 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_so_hdL varchar2(1); 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_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
    PHT_ID_MOI(b_so_id,b_loi);
    if b_loi is not null then raise PROGRAM_ERROR; end if;
end if;
if b_loi is not null then raise PROGRAM_ERROR; end if;
PBH_BG_JS_TEST(
    b_ma_dvi,b_nsd,b_so_id,'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_phong,b_ma_kh,
    tt_ngay,tt_tien,b_loi,'HANG');
if b_loi is not null then raise PROGRAM_ERROR; 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_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);
   PBH_HANGB_NH_NH(
      b_ma_dvi,b_nsd,b_so_id,b_lan,
      dt_ct,dt_ds,dt_dk,dt_kytt,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_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;
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;
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,'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;