AAA

CREATE OR REPLACE procedure PTBH_HD_DI_NH(
    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(2000); b_i1 number; b_i2 number; b_pt number:=0;
    b_so_id number; b_so_hd varchar2(20); b_nv varchar2(10); b_pthuc varchar2(10);
    b_ma_nt varchar2(5); b_ty_gia number; b_pbo_cp varchar2(1); b_ngay_bd number; b_ngay_kt number;

    bh_nha_bh pht_type.a_var; bh_pt pht_type.a_num; bh_hh pht_type.a_num; bh_hh_ll pht_type.a_num;
    bh_kieu pht_type.a_var; bh_nha_bhC pht_type.a_var;
    nv_lh_nv pht_type.a_var; nv_ma_dt pht_type.a_var;
    nv_nguong pht_type.a_num; nv_glai pht_type.a_num; nv_glaiM pht_type.a_num; nv_ghan pht_type.a_num;
    nv_hh pht_type.a_num; nv_hh_ll pht_type.a_num; nv_tlp pht_type.a_num;
    do_pt pht_type.a_num; do_hs_ng pht_type.a_num; do_hs_gl pht_type.a_num; do_hs_gh pht_type.a_num;
    ta_pt pht_type.a_num; ta_hs_ng pht_type.a_num; ta_hs_gl pht_type.a_num; ta_hs_gh pht_type.a_num;
    ng_ma_dt pht_type.a_var; ng_tien pht_type.a_num; ng_bth pht_type.a_num; ng_ptG pht_type.a_num; ng_hs pht_type.a_num;
    nv_nguongX pht_type.a_num; nv_glaiX pht_type.a_num; nv_glaiMX pht_type.a_num; nv_ghanX pht_type.a_num;

    dt_ct clob; dt_bh clob; dt_nv clob; dt_do clob; dt_ta clob; dt_ng clob;
begin
-- Dan - Nhap
b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'BH','TA','M');
if b_loi is not null then raise PROGRAM_ERROR; end if;
b_so_id:=FKH_JS_GTRIn(b_oraIn,'so_id');
if b_so_id<>0 then
    PTBH_HD_DI_XOA_XOA(b_nsd,b_so_id,b_loi);
else
    PHT_ID_MOI(b_so_id,b_loi);
end if;
if b_loi is not null then raise PROGRAM_ERROR; end if;
b_lenh:=FKH_JS_LENHc('dt_ct,dt_bh,dt_nv,dt_do,dt_ta');
EXECUTE IMMEDIATE b_lenh into dt_ct,dt_bh,dt_nv,dt_do,dt_ta using b_oraIn;
b_lenh:=FKH_JS_LENH('so_hd,nv,pthuc,ma_nt,ty_gia,pbo_cp,ngay_bd,ngay_kt');
EXECUTE IMMEDIATE b_lenh into b_so_hd,b_nv,b_pthuc,b_ma_nt,b_ty_gia,b_pbo_cp,b_ngay_bd,b_ngay_kt using dt_ct;
if b_ngay_bd is null or b_ngay_bd in(0,30000101) then
    b_loi:='loi:Sai ngay ap dung:loi'; raise PROGRAM_ERROR;
end if;
if b_ngay_kt is null then b_ngay_kt:=30000101; end if;
if b_nv is null or b_nv not in('XE','2B','HANG','NG','TAU','PHH','PKT','PTN') then
    b_loi:='loi:Sai nghiep vu:loi'; raise PROGRAM_ERROR;
end if;
b_loi:='loi:Sai phuong thuc:loi';
if b_pthuc is null then raise PROGRAM_ERROR; end if;
select 0 into b_i1 from tbh_ma_pthuc where ma=b_pthuc;
b_loi:='loi:Sai ma nguyen te:loi';
if b_ma_nt is null then raise PROGRAM_ERROR; end if;
if b_ma_nt<>'VND' then
    select 0 into b_i1 from tt_ma_nt where ma=b_ma_nt;
end if;
if FTBH_HD_DI_SO_IDp(b_nv,b_pthuc,b_ngay_bd)<>0 then
    b_loi:='loi:Da khai bao hop dong tai co dinh:loi'; raise PROGRAM_ERROR;
end if;
b_lenh:=FKH_JS_LENH('nha_bh,pt,hh,hh_ll,kieu');
EXECUTE IMMEDIATE b_lenh bulk collect into bh_nha_bh,bh_pt,bh_hh,bh_hh_ll,bh_kieu using dt_bh;
b_loi:='loi:Sai so lieu nha bao hiem:loi';
if bh_nha_bh.count=0 then raise PROGRAM_ERROR; end if;
for b_lp in 1..bh_nha_bh.count loop
    if bh_nha_bh(b_lp) is null or bh_pt(b_lp) is null or bh_pt(b_lp)<0 or
        bh_hh(b_lp) is null or bh_hh(b_lp)<0 or bh_hh_ll(b_lp) is null or bh_hh_ll(b_lp)<0 or
        bh_kieu(b_lp) is null or bh_kieu(b_lp) not in('C','P','M') then raise PROGRAM_ERROR;
    end if;
end loop;
for b_lp in 1..bh_nha_bh.count loop
    if bh_kieu(b_lp)='C' then
        bh_nha_bhC(b_lp):=bh_nha_bh(b_lp);
    else
        b_i1:=b_lp-1; b_i2:=0;
        for b_lp1 in REVERSE 1..b_i1 loop
            if bh_kieu(b_lp1)='C' then b_i2:=b_lp1; exit; end if;
        end loop;
        if b_i2=0 then b_loi:='loi:Sai kieu nha bao hiem '||bh_nha_bh(b_lp)||':loi'; return; end if;
        bh_nha_bhC(b_lp):=bh_nha_bh(b_i2);
    end if;
    b_pt:=b_pt+bh_pt(b_lp);
end loop;
if b_pt>100 then b_loi:='loi:Tong cac nha tai: '||to_char(b_pt)||':loi'; return; end if;
b_lenh:=FKH_JS_LENH('lh_nv,ma_dt,nguong,glai,glaim,ghan,hh,hh_ll,tlp');
EXECUTE IMMEDIATE b_lenh bulk collect into
    nv_lh_nv,nv_ma_dt,nv_nguong,nv_glai,nv_glaiM,nv_ghan,nv_hh,nv_hh_ll,nv_tlp using dt_nv;
if nv_lh_nv.count=0 then
    b_loi:='loi:Nhap muc giu lai:loi'; raise PROGRAM_ERROR;
end if;
for b_lp in 1..nv_lh_nv.count loop
    b_loi:='loi:Sai chi tiet nghiep vu dong '||to_char(b_lp)||':loi';
    if nv_lh_nv(b_lp) is null or nv_nguong(b_lp) is null or
        nv_glai(b_lp) is null or nv_ghan(b_lp) is null or
        nv_hh(b_lp) is null or nv_hh_ll(b_lp) is null or nv_tlp(b_lp) is null then raise PROGRAM_ERROR;
    end if;
    if FBH_MA_LHNV_TAI_NV(nv_lh_nv(b_lp),b_nv)<>'C' then raise PROGRAM_ERROR; end if;
    nv_ma_dt(b_lp):=nvl(trim(nv_ma_dt(b_lp)),' ');
    if nv_ma_dt(b_lp)<> ' ' and FTBH_DTUONG_TEN(b_nv,nv_ma_dt(b_lp)) is null then
        raise PROGRAM_ERROR;
    end if;
    if nv_glaiM(b_lp) is null or nv_glaiM(b_lp)=0 then nv_glaiM(b_lp):=nv_glai(b_lp); end if;
end loop;
if trim(dt_do) is not null then
    b_lenh:=FKH_JS_LENH('pt,hs_ng,hs_gl,hs_gh');
    EXECUTE IMMEDIATE b_lenh bulk collect into do_pt,do_hs_ng,do_hs_gl,do_hs_gh using dt_do;
    for b_lp in 1..do_pt.count loop
        if do_pt(b_lp) is null or do_pt(b_lp)<0 or do_pt(b_lp)>100 or
            do_hs_ng(b_lp) is null or do_hs_ng(b_lp)<0 or do_hs_ng(b_lp)>100 or
            do_hs_gl(b_lp) is null or do_hs_gl(b_lp)<0 or do_hs_gl(b_lp)>100 or
            do_hs_gh(b_lp) is null or do_hs_gh(b_lp)<0 or do_hs_gh(b_lp)>100 then
            b_loi:='loi:Sai he so dong BH dong '||to_char(b_lp)||':loi';
            raise PROGRAM_ERROR;
        end if;
    end loop;
end if;
if trim(dt_ta) is not null then
    EXECUTE IMMEDIATE b_lenh bulk collect into ta_pt,ta_hs_ng,ta_hs_gl,ta_hs_gh using dt_ta;
    for b_lp in 1..ta_pt.count loop
        if ta_pt(b_lp) is null or ta_pt(b_lp)<0 or ta_pt(b_lp)>100 or
            ta_hs_ng(b_lp) is null or ta_hs_ng(b_lp)<0 or ta_hs_ng(b_lp)>100 or
            ta_hs_gl(b_lp) is null or ta_hs_gl(b_lp)<0 or ta_hs_gl(b_lp)>100 or
            ta_hs_gh(b_lp) is null or ta_hs_gh(b_lp)<0 or ta_hs_gh(b_lp)>100 then
            b_loi:='loi:Sai he so tai BH dong '||to_char(b_lp)||':loi';
            raise PROGRAM_ERROR;
        end if;
    end loop;
end if;
if trim(dt_ng) is not null then
    EXECUTE IMMEDIATE b_lenh bulk collect into ng_ma_dt,ng_tien,ng_bth,ng_ptG,ng_hs using dt_ng;
    for b_lp in 1..ng_ma_dt.count loop
        ng_ma_dt(b_lp):=nvl(trim(ng_ma_dt(b_lp)),' ');
        ng_tien(b_lp):=nvl(ng_tien(b_lp),0);
        ng_bth(b_lp):=nvl(ng_bth(b_lp),0); ng_ptG(b_lp):=nvl(ng_ptG(b_lp),0); ng_hs(b_lp):=nvl(ng_hs(b_lp),0);
    end loop;
end if;
-- chuclh kiem tra so_hd duy nhat
select count(*) into b_i1 from tbh_hd_di where so_hd=b_so_hd;
if b_i1 > 0 then b_loi:='loi:So hop dong da ton tai:loi'; raise PROGRAM_ERROR; end if;
b_loi:='loi:Loi Table tbh_hd_di:loi';
insert into tbh_hd_di values(b_ma_dvi,b_so_id,b_so_hd,b_nv,b_pthuc,b_ma_nt,b_ty_gia,b_pbo_cp,b_ngay_bd,b_ngay_kt,b_nsd);
for b_lp in 1..bh_nha_bh.count loop
    insert into tbh_hd_di_nha_bh values(b_ma_dvi,b_so_id,b_lp,bh_nha_bh(b_lp),bh_pt(b_lp),bh_hh(b_lp),bh_hh_ll(b_lp),bh_kieu(b_lp),bh_nha_bhC(b_lp));
end loop;
if b_ma_nt='VND' then
    for b_lp in 1..nv_lh_nv.count loop
        nv_nguongX(b_lp):=nv_nguong(b_lp); nv_ghanX(b_lp):=nv_ghan(b_lp);
        nv_glaiX(b_lp):=nv_glai(b_lp); nv_glaiMX(b_lp):=nv_glaiM(b_lp);
    end loop;
else
    if b_ty_gia<>0 then b_i1:=1;
    else
        b_i1:=FTT_TRA_TGTT(b_ma_dvi,b_ngay_bd,b_ma_nt);
    end if;
    for b_lp in 1..nv_lh_nv.count loop
        nv_nguongX(b_lp):=round(nv_nguong(b_lp)*b_i1,0);
        nv_ghanX(b_lp):=round(nv_ghan(b_lp)*b_i1,0);
        nv_glaiX(b_lp):=round(nv_glai(b_lp)*b_i1,0);
        nv_glaiMX(b_lp):=round(nv_glaiM(b_lp)*b_i1,0);
    end loop;
end if;
forall b_lp in 1..nv_lh_nv.count
    insert into tbh_hd_di_nv values(b_ma_dvi,b_so_id,nv_lh_nv(b_lp),nv_ma_dt(b_lp),
        nv_nguongX(b_lp),nv_glaiX(b_lp),nv_glaiMX(b_lp),nv_ghanX(b_lp),
        nv_hh(b_lp),nv_hh_ll(b_lp),nv_tlp(b_lp),nv_nguong(b_lp),nv_glai(b_lp),nv_glaiM(b_lp),nv_ghan(b_lp));
if trim(dt_do) is not null then
    forall b_lp in 1..do_pt.count
        insert into tbh_hd_di_do values(b_ma_dvi,b_so_id,do_pt(b_lp),do_hs_ng(b_lp),do_hs_gl(b_lp),do_hs_gh(b_lp));
end if;
if trim(dt_ta) is not null then
    forall b_lp in 1..ta_pt.count
        insert into tbh_hd_di_ta values(b_ma_dvi,b_so_id,ta_pt(b_lp),ta_hs_ng(b_lp),ta_hs_gl(b_lp),ta_hs_gh(b_lp));
end if;
if trim(dt_ng) is not null then
    forall b_lp in 1..ng_ma_dt.count
        insert into tbh_hd_di_hsng values(b_ma_dvi,b_so_id,ng_ma_dt(b_lp),ng_tien(b_lp),ng_bth(b_lp),ng_ptG(b_lp),ng_hs(b_lp));
end if;
insert into tbh_hd_di_txt values(b_ma_dvi,b_so_id,'dt_ct','"'||dt_ct||'"');
insert into tbh_hd_di_txt values(b_ma_dvi,b_so_id,'dt_bh','"'||dt_bh||'"');
select json_object('so_id' value b_so_id) into b_oraOut from dual;
commit;
exception when others then rollback; if b_loi is null then raise PROGRAM_ERROR; else raise_application_error(-20105,b_loi); end if;
end;
/