AAA

create or replace procedure PBH_SKT_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_giam clob; dt_nh clob; dt_ds clob; dt_dvi 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
    b_ma_sp varchar2(10); b_cdich varchar2(10); b_tpaH varchar2(20);
    nh_so_id pht_type.a_num; nh_nhom pht_type.a_var; nh_ten pht_type.a_nvar;
    nh_goi pht_type.a_var; nh_tpa pht_type.a_var;
    nh_phi pht_type.a_num; nh_so_dt pht_type.a_num; nh_phiN pht_type.a_num;
    nh_tl_giam pht_type.a_num; nh_giam pht_type.a_num; nh_ttoan pht_type.a_num; nh_dt_ct pht_type.a_clob;

    gcn_so_id pht_type.a_num; gcn_kieu_gcn pht_type.a_var; gcn_gcn pht_type.a_var; gcn_gcnG pht_type.a_var;
    gcn_goi pht_type.a_var; gcn_so_idP pht_type.a_num; gcn_nhom pht_type.a_var; gcn_dvi pht_type.a_nvar; gcn_ma_kh pht_type.a_var;
    gcn_ten pht_type.a_nvar; gcn_ng_sinh pht_type.a_num; gcn_gioi pht_type.a_var;
    gcn_cmt pht_type.a_var; gcn_mobi pht_type.a_var; gcn_email pht_type.a_var;
    gcn_dchi pht_type.a_nvar; gcn_nghe pht_type.a_var; gcn_ng_huong pht_type.a_nvar;
    gcn_gio_hl pht_type.a_var; gcn_ngay_hl pht_type.a_num; gcn_gio_kt pht_type.a_var; gcn_ngay_kt pht_type.a_num; gcn_ngay_cap pht_type.a_num;
    gcn_phi pht_type.a_num; gcn_giam pht_type.a_num; gcn_ttoan pht_type.a_num;

    dk_so_id 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; dk_lh_bh pht_type.a_var;

    dvi_dvi pht_type.a_var; dvi_ca pht_type.a_num; dvi_bk pht_type.a_num; dvi_dchi pht_type.a_nvar;
    dvi_so_id pht_type.a_num; dvi_tdx pht_type.a_num; dvi_tdy pht_type.a_num;

    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; nh_so_idC pht_type.a_num; nh_nhomC pht_type.a_var;
begin
-- Dan - Nhap
if b_comm='C' then
    b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'BH','NG','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_giam,dt_nh,dt_ds,dt_dvi,dt_kytt');
EXECUTE IMMEDIATE b_lenh into dt_ct,dt_giam,dt_nh,dt_ds,dt_dvi,dt_kytt using b_oraIn;
if b_so_id<>0 then
    select count(*) into b_i1 from bh_sk where ma_dvi=b_ma_dvi and so_id=b_so_id;
    if b_i1<>0 then
        select ngay_ht,ttrang into b_ngay_htC,b_ttrang from bh_sk where ma_dvi=b_ma_dvi and so_id=b_so_id;
        if b_ttrang='D' then
            select so_id_nh,nhom bulk collect into nh_so_idC,nh_nhomC from bh_sk_nh where ma_dvi=b_ma_dvi and so_id=b_so_id;
        else
            PKH_MANG_KD(nh_nhomC);
        end if;
        if b_ttrang not in('T','D')  then b_kieu_hd:='X'; else b_kieu_hd:='N'; end if;
        PBH_SK_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_sk',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,'NG');
if b_loi is not null then raise PROGRAM_ERROR; end if;
if(b_kieu_hd = 'U') then
    PBH_SKT_TESTr_U(
        b_ma_dvi,b_nsd,b_so_idG,nh_so_idC,nh_nhomC,
        dt_ct,dt_giam,dt_nh,dt_ds,dt_dvi,
        b_so_hdL,b_ma_sp,b_cdich,b_tpaH,
        nh_so_id,nh_nhom,nh_ten,nh_goi,nh_tpa,nh_phi,nh_so_dt,nh_phiN,nh_tl_giam,nh_giam,nh_ttoan,
        gcn_so_id,gcn_kieu_gcn,gcn_gcn,gcn_gcnG,gcn_goi,gcn_so_idP,gcn_nhom,gcn_dvi,gcn_ma_kh,gcn_ten,gcn_ng_sinh,gcn_gioi,
        gcn_cmt,gcn_mobi,gcn_email,gcn_dchi,gcn_nghe,gcn_ng_huong,gcn_gio_hl,gcn_ngay_hl,gcn_gio_kt,gcn_ngay_kt,gcn_ngay_cap,
        gcn_phi,gcn_giam,gcn_ttoan,
        dk_so_id,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,dk_lh_bh,
        dvi_dvi,dvi_ca,dvi_bk,dvi_dchi,dvi_so_id,dvi_tdx,dvi_tdy,
        lt_so_id,lt_dk,lt_lt,lt_kbt,b_loi);
    if b_loi is not null then raise PROGRAM_ERROR; end if;
    PBH_SKT_NH_U(
        b_ma_dvi,b_nsd,b_so_id,
        dt_ct,dt_nh,
        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,
        b_ma_sp,b_cdich,b_tpaH,
        nh_so_id,nh_nhom,nh_ten,nh_goi,nh_tpa,nh_phi,nh_so_dt,nh_phiN,nh_tl_giam,nh_giam,nh_ttoan,b_loi);
    if b_loi is not null then raise PROGRAM_ERROR; end if;
else
    PBH_SKT_TESTr(
        b_ma_dvi,b_nsd,b_so_idG,nh_so_idC,nh_nhomC,
        dt_ct,dt_giam,dt_nh,dt_ds,dt_dvi,
        b_so_hdL,b_ma_sp,b_cdich,b_tpaH,
        nh_so_id,nh_nhom,nh_ten,nh_goi,nh_tpa,nh_phi,nh_so_dt,nh_phiN,nh_tl_giam,nh_giam,nh_ttoan,
        gcn_so_id,gcn_kieu_gcn,gcn_gcn,gcn_gcnG,gcn_goi,gcn_so_idP,gcn_nhom,gcn_dvi,gcn_ma_kh,gcn_ten,gcn_ng_sinh,gcn_gioi,
        gcn_cmt,gcn_mobi,gcn_email,gcn_dchi,gcn_nghe,gcn_ng_huong,gcn_gio_hl,gcn_ngay_hl,gcn_gio_kt,gcn_ngay_kt,gcn_ngay_cap,
        gcn_phi,gcn_giam,gcn_ttoan,
        dk_so_id,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,dk_lh_bh,
        dvi_dvi,dvi_ca,dvi_bk,dvi_dchi,dvi_so_id,dvi_tdx,dvi_tdy,
        lt_so_id,lt_dk,lt_lt,lt_kbt,b_loi);
    if b_loi is not null then raise PROGRAM_ERROR; end if;
    PBH_SKT_NH_NH(
        b_ma_dvi,b_nsd,b_so_id,
        dt_ct,dt_nh,dt_ds,dt_dvi,
        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,
        b_ma_sp,b_cdich,b_tpaH,
        nh_so_id,nh_nhom,nh_ten,nh_goi,nh_tpa,nh_phi,nh_so_dt,nh_phiN,nh_tl_giam,nh_giam,nh_ttoan,
        gcn_so_id,gcn_kieu_gcn,gcn_gcn,gcn_gcnG,gcn_goi,gcn_so_idP,gcn_nhom,gcn_dvi,gcn_ma_kh,gcn_ten,gcn_ng_sinh,gcn_gioi,
        gcn_cmt,gcn_mobi,gcn_email,gcn_dchi,gcn_nghe,gcn_ng_huong,gcn_gio_hl,gcn_ngay_hl,gcn_gio_kt,gcn_ngay_kt,gcn_ngay_cap,
        gcn_phi,gcn_giam,gcn_ttoan,
        dk_so_id,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,dk_lh_bh,
        dvi_dvi,dvi_ca,dvi_bk,dvi_dchi,dvi_so_id,dvi_tdx,dvi_tdy,
        lt_so_id,lt_dk,lt_lt,lt_kbt,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_SKT_TESTr_U(
    b_ma_dvi varchar2,b_nsd varchar2,b_so_idG number,
    nh_so_idC pht_type.a_num,nh_nhomC pht_type.a_var,
    dt_ct in out clob,dt_giam clob,dt_nh clob,dt_ds in out clob,dt_dvi in out clob,

    b_so_hdL out varchar2,b_ma_sp out varchar2,b_cdich out varchar2,b_tpaH out varchar2,
    nh_so_id out pht_type.a_num,nh_nhom out pht_type.a_var,nh_ten out pht_type.a_nvar,
    nh_goi out pht_type.a_var,nh_tpa out pht_type.a_var,
    nh_phi out pht_type.a_num,nh_so_dt out pht_type.a_num,nh_phiN out pht_type.a_num,
    nh_tl_giam out pht_type.a_num,nh_giam out pht_type.a_num,nh_ttoan out pht_type.a_num,

    gcn_so_id out pht_type.a_num,gcn_kieu_gcn out pht_type.a_var,gcn_gcn out pht_type.a_var,gcn_gcnG out pht_type.a_var,
    gcn_goi out pht_type.a_var,gcn_so_idP out pht_type.a_num,
    gcn_nhom out pht_type.a_var,gcn_dvi out pht_type.a_nvar,gcn_ma_kh out pht_type.a_var,
    gcn_ten out pht_type.a_nvar,gcn_ng_sinh out pht_type.a_num,gcn_gioi out pht_type.a_var,
    gcn_cmt out pht_type.a_var,gcn_mobi out pht_type.a_var,gcn_email out pht_type.a_var,
    gcn_dchi out pht_type.a_nvar,gcn_nghe out pht_type.a_var,gcn_ng_huong out pht_type.a_nvar,
    gcn_gio_hl out pht_type.a_var,gcn_ngay_hl out pht_type.a_num,
    gcn_gio_kt out pht_type.a_var,gcn_ngay_kt out pht_type.a_num,gcn_ngay_cap out pht_type.a_num,
    gcn_phi out pht_type.a_num,gcn_giam out pht_type.a_num,gcn_ttoan out pht_type.a_num,

    dk_so_id out pht_type.a_num,dk_ma out pht_type.a_var,dk_ten out pht_type.a_nvar,
    dk_tc out pht_type.a_var,dk_ma_ct out pht_type.a_var,dk_kieu out pht_type.a_var,
    dk_tien out pht_type.a_num,dk_pt out pht_type.a_num,dk_phi out pht_type.a_num,
    dk_thue out pht_type.a_num,dk_ttoan out pht_type.a_num,
    dk_cap out pht_type.a_num,dk_ma_dk out pht_type.a_var,
    dk_lh_nv out pht_type.a_var,dk_t_suat out pht_type.a_num,
    dk_ptB out pht_type.a_num,dk_ptG out pht_type.a_num,dk_phiG out pht_type.a_num,
    dk_lkeP out pht_type.a_var,dk_lkeB out pht_type.a_var,dk_luy out pht_type.a_var,dk_lh_bh out pht_type.a_var,

    dvi_dvi out pht_type.a_var,dvi_ca out pht_type.a_num,dvi_bk out pht_type.a_num,dvi_dchi out pht_type.a_nvar,
    dvi_so_id out pht_type.a_num,dvi_tdx out pht_type.a_num,dvi_tdy out pht_type.a_num,

    lt_so_id out pht_type.a_num,lt_dk out pht_type.a_clob,lt_lt out pht_type.a_clob,lt_kbt out pht_type.a_clob,b_loi out varchar2)
AS
    b_lenh varchar2(2000); b_i1 number; b_i2 number;b_so_idN number:=0;
    b_kieu_hd varchar2(1); b_ttrang varchar2(1);
    b_kt_dk number; b_kt_dkB number; b_kt_dkC number; b_so_dt number; b_txt clob;
    b_gio_hl nvarchar2(50); b_ngay_hl number; b_gio_kt nvarchar2(50); b_ngay_kt number; b_ngay_cap number;
    b_phiH number; b_ttoanH number; b_phiN number; b_giamN number; b_ktG number;

    nh_dt_ct pht_type.a_clob; nh_dt_dk pht_type.a_clob; nh_dt_dkbs pht_type.a_clob;
    nh_dt_lt pht_type.a_clob; nh_dt_khd pht_type.a_clob; nh_dt_kbt pht_type.a_clob;

    nh_nhomG pht_type.a_var; nh_tl_giamG pht_type.a_num; nh_giamG pht_type.a_num; nh_ttoanG pht_type.a_num;
    nh_maG pht_type.a_var; nh_tenG pht_type.a_nvar;
    nh_tcG pht_type.a_var; nh_ma_ctG pht_type.a_var; nh_kieuG pht_type.a_var;
    nh_tienG pht_type.a_num; nh_ptG pht_type.a_num; nh_phiG pht_type.a_num;
    nh_capG pht_type.a_num; nh_ma_dkG pht_type.a_var;
    nh_lh_nvG pht_type.a_var; nh_ptBG pht_type.a_var; nh_phiBG pht_type.a_var;
    nh_lkePG pht_type.a_var; nh_lkeBG pht_type.a_var; nh_luyG pht_type.a_var; nh_lh_bhG pht_type.a_var;

    nhB_maG pht_type.a_var; nhB_tenG pht_type.a_nvar;
    nhB_tcG pht_type.a_var; nhB_ma_ctG pht_type.a_var; nhB_kieuG pht_type.a_var;
    nhB_tienG pht_type.a_num; nhB_ptG pht_type.a_num; nhB_phiG pht_type.a_num;
    nhB_capG pht_type.a_num; nhB_ma_dkG pht_type.a_var;
    nhB_lh_nvG pht_type.a_var; nhB_ptBG pht_type.a_var; nhB_phiBG pht_type.a_var;
    nhB_lkePG pht_type.a_var; nhB_lkeBG pht_type.a_var; nhB_luyG pht_type.a_var;
    dk_phiB pht_type.a_num;

    nh_so_idP pht_type.a_num;

begin
-- Dan - Nhap
b_lenh:=FKH_JS_LENH('ttrang,kieu_hd,gio_hl,ngay_hl,gio_kt,ngay_kt,ngay_cap,ma_sp,cdich,tpa,ttoan,so_hdl');
EXECUTE IMMEDIATE b_lenh into b_ttrang,b_kieu_hd,b_gio_hl,b_ngay_hl,
    b_gio_kt,b_ngay_kt,b_ngay_cap,b_ma_sp,b_cdich,b_tpaH,b_ttoanH,b_so_hdL using dt_ct;
b_ma_sp:=nvl(trim(b_ma_sp),' '); b_cdich:=nvl(trim(b_cdich),' ');
if b_ma_sp=' ' or FBH_SK_SP_HAN(b_ma_sp)<>'C' then b_loi:='loi:Sai ma san pham:loi'; return; end if;
if b_cdich<>' ' and FBH_MA_CDICH_HAN(b_cdich)<>'C' then b_loi:='loi:Sai ma chien dich:loi'; return; end if;
b_tpaH:=NVL(trim(b_tpaH),' ');
if b_tpaH<>' ' and FBH_MA_GDINH_HAN(b_tpaH)<>'C' then b_loi:='loi:Sai ma TPA '||b_tpaH||':loi'; return; end if;
b_lenh:=FKH_JS_LENH('nhom,tl_giam,giam,ttoan');
EXECUTE IMMEDIATE b_lenh bulk collect into nh_nhomG,nh_tl_giamG,nh_giamG,nh_ttoanG using dt_giam;
b_lenh:=FKH_JS_LENHc('dt_nh_ct,dt_nh_dk,dt_nh_dkbs,dt_nh_lt,dt_nh_khd,dt_nh_kbt');
EXECUTE IMMEDIATE b_lenh bulk collect into nh_dt_ct,nh_dt_dk,nh_dt_dkbs,nh_dt_lt,nh_dt_khd,nh_dt_kbt using dt_nh;
if nh_dt_ct.count=0 then b_loi:='loi:Nhap quyen loi nhom:loi'; return; end if;
b_lenh:=FKH_JS_LENH('so_id_nh,nhom,ten,goi,so_idp,tpa,phi');
for b_lp in 1..nh_dt_ct.count loop
    EXECUTE IMMEDIATE b_lenh into nh_so_id(b_lp),nh_nhom(b_lp),nh_ten(b_lp),nh_goi(b_lp),
        nh_so_idP(b_lp),nh_tpa(b_lp),nh_phi(b_lp) using nh_dt_ct(b_lp);
    --if trim(nh_nhom(b_lp)) is null or trim(nh_ten(b_lp)) is null then
    -- chuclh: bo tam khong check ten
    if trim(nh_nhom(b_lp)) is null then
        b_loi:='loi:Nhap nhom va ten nhom dong '||to_char(b_lp)||':loi'; return;
    end if;
    if trim(nh_tpa(b_lp)) is null then
        nh_tpa(b_lp):=b_tpaH;
    elsif FBH_MA_GDINH_HAN(nh_tpa(b_lp))<>'C' then
        b_loi:='loi:Sai ma TPA '||nh_tpa(b_lp)||':loi'; return;
    end if;
    nh_goi(b_lp):=nvl(trim(nh_goi(b_lp)),' ');
    if nh_goi(b_lp)<>' ' and FBH_SK_GOI_HAN(nh_goi(b_lp))<>'C' then b_loi:='loi:Sai ma goi '||nh_goi(b_lp)||':loi'; return; end if;
    b_i1:=FKH_ARR_VTRI(nh_nhomG, nh_nhom(b_lp));
    if b_i1=0 then b_loi:='loi:Sai nhom '||nh_ten(b_lp)||':loi'; return; end if;
    nh_giam(b_lp):=nh_giamG(b_i1); nh_ttoan(b_lp):=nh_ttoanG(b_i1);
    nh_tl_giam(b_lp):=nh_tl_giamG(b_i1); nh_phiN(b_lp):=nh_giam(b_lp)+nh_ttoan(b_lp);
    b_i1:=FKH_ARR_VTRI(nh_nhomC, nh_nhom(b_lp));
    if b_i1>0 then nh_so_id(b_lp):=nh_so_idC(b_i1); else nh_so_id(b_lp):=b_lp; end if;
end loop;

b_kt_dk:=0; b_phiH:=0;
b_lenh:=FKH_JS_LENH('ma,ten,tien,pt,phi,cap,tc,ma_ct,kieu,ma_dk,lh_nv,ptb,phib,lkep,lkeb,luy');
for b_lp in 1..nh_nhom.count loop
    EXECUTE IMMEDIATE b_lenh bulk collect into
        nh_maG,nh_tenG,nh_tienG,nh_ptG,nh_phiG,nh_capG,nh_tcG,nh_ma_ctG,nh_kieuG,
        nh_ma_dkG,nh_lh_nvG,nh_ptBG,nh_phiBG,nh_lkePG,nh_lkeBG,nh_luyG using nh_dt_dk(b_lp);
    b_ktG:=nh_maG.count;
    if b_ktG=0 then
        b_loi:='loi:Chua nhap dieu khoan bao hiem nhom '||nh_ten(b_lp)||':loi'; return;
    end if;
    for b_lp2 in 1..b_ktG loop
        nh_lh_bhG(b_lp2):='C';
    end loop;
    if trim(nh_dt_dkbs(b_lp)) is not null then
        EXECUTE IMMEDIATE b_lenh bulk collect into
            nhB_maG,nhB_tenG,nhB_tienG,nhB_ptG,nhB_phiG,nhB_capG,nhB_tcG,nhB_ma_ctG,nhB_kieuG,
            nhB_ma_dkG,nhB_lh_nvG,nhB_ptBG,nhB_phiBG,nhB_lkePG,nhB_lkeBG,nhB_luyG using nh_dt_dkbs(b_lp);
        for b_lp2 in 1..nhB_maG.count loop
            b_ktG:=b_ktG+1;
            nh_lh_bhG(b_ktG):='M';
            nh_maG(b_ktG):=nhB_maG(b_lp2); nh_tenG(b_ktG):=nhB_tenG(b_lp2); nh_tienG(b_ktG):=nhB_tienG(b_lp2);
            nh_ptG(b_ktG):=nhB_ptG(b_lp2); nh_phiG(b_ktG):=nhB_phiG(b_lp2); nh_capG(b_ktG):=nhB_capG(b_lp2);
            nh_tcG(b_ktG):=nhB_tcG(b_lp2); nh_ma_ctG(b_ktG):=nhB_ma_ctG(b_lp2); nh_kieuG(b_ktG):=nhB_kieuG(b_lp2);
            nh_ma_dkG(b_ktG):=nhB_ma_dkG(b_lp2); nh_lh_nvG(b_ktG):=nhB_lh_nvG(b_lp2); nh_ptBG(b_ktG):=nhB_ptBG(b_lp2);
            nh_phiBG(b_ktG):=nhB_phiBG(b_lp2);
            nh_lkePG(b_ktG):=nhB_lkePG(b_lp2); nh_lkeBG(b_ktG):=nhB_lkeBG(b_lp2); nh_luyG(b_ktG):=nhB_luyG(b_lp2);
        end loop;
    end if;
    
    nh_so_dt(b_lp):=b_so_dt; b_giamN:=b_phiN-nh_ttoan(b_lp);
    if b_phiN<>0 and b_giamN<>0 then
        b_i1:=b_giamN/b_phiN; b_kt_dkC:=0;
        for b_lp1 in b_kt_dkB..b_kt_dk loop
            if dk_lh_nv(b_lp1)<>' ' then
                b_i2:=round(b_i1*dk_phi(b_lp1),0);
                dk_phiG(b_lp1):=b_i2;
                dk_phi(b_lp1):=dk_phi(b_lp1)-b_i2;
                dk_ttoan(b_lp1):=dk_phi(b_lp1);
                b_giamN:=b_giamN-b_i2; b_kt_dkC:=b_lp1;
            end if;
        end loop;
        if b_giamN<>0 and b_kt_dkC<>0 then
            dk_phiG(b_kt_dkC):=dk_phiG(b_kt_dkC)+b_giamN;
            dk_phi(b_kt_dkC):=dk_phi(b_kt_dkC)-b_giamN;
            dk_ttoan(b_kt_dkC):=dk_phi(b_kt_dkC);
        end if;
    end if;
    b_phiH:=b_phiH+nh_ttoan(b_lp);
end loop;
b_giamN:=b_phiH-b_ttoanH;
if b_phiH<>0 and b_giamN<>0 then
    b_i1:=b_giamN/b_phiH; b_kt_dkC:=0;
    for b_lp1 in 1..b_kt_dk loop
        if dk_lh_nv(b_lp1)<>' ' then
            b_i2:=round(b_i1*dk_phi(b_lp1),0);
            dk_phiG(b_lp1):=b_i2;
            dk_phi(b_lp1):=dk_phi(b_lp1)-b_i2;
            b_giamN:=b_giamN-b_i2; b_kt_dkC:=b_lp1;
            dk_ttoan(b_lp1):=dk_phi(b_lp1);
        end if;
    end loop;
    if b_giamN<>0 and b_kt_dkC<>0 then
        dk_phiG(b_kt_dkC):=dk_phiG(b_kt_dkC)+b_giamN;
        dk_phi(b_kt_dkC):=dk_phi(b_kt_dkC)-b_giamN;
        dk_ttoan(b_kt_dkC):=dk_phi(b_kt_dkC);
    end if;
end if;
for b_lp in 1..dk_ma.count loop
    if dk_phiB(b_lp)>dk_phi(b_lp) and dk_tien(b_lp) > 0 and dk_lh_nv(b_lp)<> ' ' then
        dk_phiG(b_lp):=dk_phiB(b_lp)-dk_phi(b_lp);
        dk_ptG(b_lp):=round(dk_phiG(b_lp)*100/dk_tien(b_lp),2);
    else
        dk_ptG(b_lp):=0; dk_phiG(b_lp):=0;
    end if;
end loop;
if b_ttrang in('T','D') then
    for b_lp in 1..dvi_dvi.count loop
        if dvi_so_id(b_lp) is null then
            dvi_so_id(b_lp):=0;
        elsif dvi_so_id(b_lp)>100000 then
            b_so_idN:=1;
        end if;
    end loop;
    if b_so_idN=0 then
        for b_lp in 1..nh_nhom.count loop
            if nh_so_id(b_lp) is null then
                nh_so_id(b_lp):=0;
            elsif nh_so_id(b_lp)>100000 then
                b_so_idN:=1;
            end if;
        end loop;
    end if;
    if b_so_idN=1 then
        b_i1:=dvi_dvi.count+nh_nhom.count+gcn_ten.count;
        PHT_ID_MOIn(b_i1,b_so_idN,b_loi);
        if b_loi is not null then return; end if;
    end if;
    for b_lp in 1..dvi_dvi.count loop
        b_i1:=dvi_so_id(b_lp);
        if b_i1<100000 then
            if b_so_idN<>0 then
                b_i2:=b_so_idN; b_so_idN:=b_so_idN+1;
            else
                PHT_ID_MOI(b_i2,b_loi);
                if b_loi is not null then return; end if;
            end if;
            dvi_so_id(b_lp):=b_i2;
            dt_dvi:=replace(dt_ds,'so_id_dvi\":'||to_char(b_i1)||'.','so_id_dvi":'||to_char(b_i2)||'.');
        end if;
    end loop;
    for b_lp in 1..nh_nhom.count loop
        if nh_so_id(b_lp)<100000 then
            if b_so_idN<>0 then
                nh_so_id(b_lp):=b_so_idN; b_so_idN:=b_so_idN+1;
            else
                PHT_ID_MOI(nh_so_id(b_lp),b_loi);
                if b_loi is not null then return; end if;
            end if;
        end if;
    end loop;
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_SKT_NH_U(
    b_ma_dvi varchar2,b_nsd varchar2,b_so_id number,
    dt_ct in out clob,dt_nh clob,
-- Chung
    b_so_hd 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,
-- Rieng
    b_ma_sp varchar2,b_cdich varchar2,b_tpaH varchar2,
    nh_so_id pht_type.a_num,nh_nhom pht_type.a_var,nh_ten pht_type.a_nvar,
    nh_goi pht_type.a_var,nh_tpa pht_type.a_var,
    nh_phi pht_type.a_num,nh_so_dt pht_type.a_num,nh_phiN pht_type.a_num,
    nh_tl_giam pht_type.a_num,nh_giam pht_type.a_num,nh_ttoan pht_type.a_num,
    b_loi out varchar2)
AS
    b_i1 number; b_so_dt number; b_so_id_kt number:=-1; b_tien number; a_bt pht_type.a_num ;
begin
-- Dan - Nhap
if b_ttrang='D' then b_so_id_kt:=0; end if;
b_loi:='loi:Loi Table bh_sk:loi';

for b_lp in 1..nh_so_id.count loop
    insert into bh_sk_nh values(b_ma_dvi,b_so_id,nh_so_id(b_lp),b_lp,nh_nhom(b_lp),nh_ten(b_lp),
        nh_goi(b_lp),nh_tpa(b_lp),nh_phi(b_lp),nh_so_dt(b_lp),nh_phiN(b_lp),nh_tl_giam(b_lp),nh_giam(b_lp),nh_ttoan(b_lp));
end loop;
insert into bh_sk values(
    b_ma_dvi,b_so_id,b_so_hd,b_ngay_ht,'T',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,b_ma_sp,b_cdich,b_tpaH,'K',b_so_dt,
    b_tien,b_phi,b_giam,b_thue,b_ttoan,b_hhong,b_so_idG,b_so_idD,'','',0,b_nsd,sysdate);
PKH_JS_THAYa(dt_ct,'so_hd,ma_kh',b_so_hd||','||b_ma_kh);
insert into bh_sk_txt values(b_ma_dvi,b_so_id,'dt_ct','"'||dt_ct||'"');
insert into bh_sk_txt values(b_ma_dvi,b_so_id,'dt_nh','"'||dt_nh||'"');
if b_ttrang in('D','T') then
    insert into bh_ng values(
        b_ma_dvi,b_so_id,b_so_hd,b_ngay_ht,'SKT',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_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,'K',b_so_dt,b_tien,b_phi,b_giam,b_thue,b_ttoan,b_hhong,b_so_idG,b_so_idD,0,'','',b_nsd);
end if;
b_loi:='';
exception when others then if b_loi is null then raise PROGRAM_ERROR; else null; end if;
end;
/