AAA

create or replace procedure PTBH_TM_NH_TEST(
    b_so_id number,dt_ct clob,dt_hd clob,dt_bh clob,dt_phi clob,
    b_nv out varchar2,b_ngay_ht out number,b_so_ct out varchar2,b_kieu out varchar2,b_so_ctG out varchar2,
    b_ngay_hl out number,b_ngay_kt out number,b_nt_tien out varchar2,b_nt_phi out varchar2,
    b_nguon out varchar2,b_pthuc out varchar2,b_so_idD out number,b_so_idG out number,
    a_ma_dvi out pht_type.a_var,a_so_hd out pht_type.a_var,
    a_so_id_hd out pht_type.a_num,a_so_id_dt out pht_type.a_num,
    nbh_ma out pht_type.a_var,nbh_pt out pht_type.a_num,nbh_hh out pht_type.a_num,
    nbh_kieu out pht_type.a_var,nbh_maC out pht_type.a_var,
    phi_nbh out pht_type.a_var,phi_ma_ta out pht_type.a_var,
    phi_pt out pht_type.a_num,phi_tien out pht_type.a_num,
    phi_phi out pht_type.a_num,phi_tl_thue out pht_type.a_num,phi_thue out pht_type.a_num,
    phi_pt_hh out pht_type.a_num,phi_hhong out pht_type.a_num,b_loi out varchar2,b_ch varchar2:='K')
AS
    b_lenh varchar2(2000); b_i1 number; b_i2 number;
    b_pt number:=0; b_ma_dvi varchar2(10):=FTBH_DVI_TA();
begin
-- Dan - Kiem tra so lieu
b_loi:='loi:Loi xu ly PTBH_TM_NH_TEST:loi';
b_lenh:=FKH_JS_LENH('nv,ngay_ht,so_ct,kieu,so_ctg,ngay_hl,ngay_kt,nt_tien,nt_phi,nguon,pthuc');
EXECUTE IMMEDIATE b_lenh into b_nv,b_ngay_ht,b_so_ct,b_kieu,b_so_ctG,
    b_ngay_hl,b_ngay_kt,b_nt_tien,b_nt_phi,b_nguon,b_pthuc using dt_ct;
if b_nv is null or b_nv not in ('XE','2B','HANG','PHH','PKT','PTN','NG','TAU') or
    b_ngay_ht is null or b_nguon is null or b_nguon not in ('N','B','T') then
    b_loi:='loi:Nhap so lieu tai tam thoi sai:loi'; return;
end if;
b_ngay_ht:=nvl(b_ngay_ht,PKH_NG_CSO(sysdate));
b_kieu:=nvl(trim(b_kieu),'G'); b_so_ctG:=nvl(trim(b_so_ctG),' ');
b_nt_tien:=nvl(trim(b_nt_tien),'VND');
if b_nt_tien<>'VND' then
    select count(*) into b_i1 from tt_ma_nt where ma_dvi=b_ma_dvi and ma=b_nt_tien;
    if b_i1=0 then
        b_loi:='loi:Nhap sai loai tien muc trach nhiem:loi'; return;
    end if;
end if;
b_nt_phi:=nvl(trim(b_nt_tien),'VND');
if b_nt_phi<>'VND' then
    select count(*) into b_i1 from tt_ma_nt where ma_dvi=b_ma_dvi and ma=b_nt_phi;
    if b_i1=0 then b_loi:='loi:Nhap sai loai tien phi:loi'; return; end if;
end if;
b_lenh:=FKH_JS_LENH('ma_dvi_hd,so_hd,so_id_dt');
EXECUTE IMMEDIATE b_lenh bulk collect into a_ma_dvi,a_so_hd,a_so_id_dt using dt_hd;
if a_ma_dvi.count=0 then b_loi:='loi:Nhap hop dong tai:loi'; return; end if;
for b_lp in 1..a_ma_dvi.count loop
    b_loi:='loi:Sai so lieu hop dong dong '||trim(to_char(b_lp))||':loi';
    if a_ma_dvi(b_lp) is null or trim(a_so_hd(b_lp)) is null or a_so_id_dt(b_lp) is null then return; end if;
    if b_ch='C' then
        a_so_id_hd(b_lp):=to_number(a_so_hd(b_lp)); a_so_hd(b_lp):=substr(a_so_hd(b_lp),3);
    else
        -- chuclh: co truong hop loi o day. chua tao lai duoc case 
        a_so_id_hd(b_lp):=FBH_HD_GOC_SO_ID_DAU(a_ma_dvi(b_lp),a_so_hd(b_lp));
        if a_so_id_hd(b_lp)=0 then
            b_loi:='loi:Hop dong '||a_so_hd(b_lp)||' da xoa:loi'; return;
        end if;
        if b_nv<>FBH_HD_NV(a_ma_dvi(b_lp),a_so_id_hd(b_lp)) then
            b_loi:='loi:Hop dong '||a_so_hd(b_lp)||' khac nghiep vu:loi'; return;
        end if;
        if a_so_id_dt(b_lp)<>0 and FTBH_GHEP_NH_DT(b_nv,b_ngay_ht,a_ma_dvi(b_lp),a_so_id_hd(b_lp),a_so_id_dt(b_lp)) is null then return; end if;
    end if;
end loop;
for b_lp in 1..a_ma_dvi.count loop
    if b_lp<a_ma_dvi.count then
        b_i1:=b_lp+1;
        for b_lp1 in b_i1..a_ma_dvi.count loop
            if a_so_id_hd(b_lp1)=a_so_id_hd(b_lp) and a_so_id_dt(b_lp1)=a_so_id_dt(b_lp) then
                b_loi:='loi:Trung doi tuong tich tu dong '||to_char(b_lp)||':loi'; return;
            end if;
        end loop;
    end if;
end loop;
b_lenh:=FKH_JS_LENH('nbh,pt,hh,kieu');
EXECUTE IMMEDIATE b_lenh bulk collect into nbh_ma,nbh_pt,nbh_hh,nbh_kieu using dt_bh;
for b_lp in 1..nbh_ma.count loop
    nbh_ma(b_lp):=nvl(trim(nbh_ma(b_lp)),' '); nbh_kieu(b_lp):=nvl(trim(nbh_kieu(b_lp)),' ');
    nbh_pt(b_lp):=nvl(nbh_pt(b_lp),0); nbh_hh(b_lp):=nvl(nbh_hh(b_lp),0);
    if nbh_ma(b_lp)=' ' or FBH_MA_NBH_HAN(nbh_ma(b_lp))<>'C' then
        b_loi:='loi:Sai nha tai dong: '||to_char(b_lp)||':loi'; return;
    end if;
    if nbh_kieu(b_lp) not in ('C','P') then
        b_loi:='loi:Sai kieu nha tai: '||nbh_ma(b_lp)||':loi'; return;
    end if;
    if nbh_kieu(b_lp)='C' then
        nbh_maC(b_lp):=nbh_ma(b_lp);
    else
        b_i1:=b_lp-1; b_i2:=0;
        for b_lp1 in REVERSE 1..b_i1 loop
            if nbh_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 '||nbh_ma(b_lp)||':loi'; return; end if;
        nbh_maC(b_lp):=nbh_ma(b_i2);
    end if;
    b_pt:=b_pt+nbh_pt(b_lp);
end loop;
if b_pt=0 then b_loi:='loi:Nhap ty le tai:loi'; return; end if;
if b_pt>100 then b_loi:='loi:Tai vuot 100%:loi'; return; end if;
b_lenh:=FKH_JS_LENH('nbh,ma_ta,pt,tien,phi,tl_thue,thue,pt_hh,hhong');
EXECUTE IMMEDIATE b_lenh bulk collect into
    phi_nbh,phi_ma_ta,phi_pt,phi_tien,phi_phi,phi_tl_thue,phi_thue,phi_pt_hh,phi_hhong using dt_phi;
for b_lp in 1..phi_ma_ta.count loop
    phi_nbh(b_lp):=nvl(trim(phi_nbh(b_lp)),' '); phi_ma_ta(b_lp):=nvl(trim(phi_ma_ta(b_lp)),' ');
    phi_pt(b_lp):=nvl(phi_pt(b_lp),0); phi_tien(b_lp):=nvl(phi_tien(b_lp),0);
    phi_phi(b_lp):=nvl(phi_phi(b_lp),0);
    phi_tl_thue(b_lp):=nvl(phi_tl_thue(b_lp),0); phi_thue(b_lp):=nvl(phi_thue(b_lp),0);
    phi_pt_hh(b_lp):=nvl(phi_pt_hh(b_lp),0); phi_tien(b_lp):=nvl(phi_tien(b_lp),0);
    if phi_ma_ta(b_lp)=' ' or phi_pt(b_lp) not between 0 and 100 then
        b_loi:='loi:Sai so lieu phi tai dong: '||trim(to_char(b_lp))||':loi'; return;
    end if;
end loop;
if b_ngay_hl is null or b_ngay_hl in(0,30000101) or
    b_ngay_kt is null or b_ngay_kt in(0,30000101) then
    FBH_HD_NGAYh_ARR(a_ma_dvi,a_so_id_hd,b_i1,b_i2);
    if b_ngay_hl is null or b_ngay_hl in(0,30000101) then b_ngay_hl:=b_i1; end if;
    if b_ngay_kt is null or b_ngay_kt in(0,30000101) then b_ngay_kt:=b_i2; end if;
end if;
b_so_ct:=trim(b_so_ct); b_so_ctG:=nvl(trim(b_so_ctG),' ');
b_i1:=PKH_SO_NAM(b_ngay_ht);
if b_so_ctG=' ' then
    b_kieu:='G'; b_so_idD:=b_so_id; b_so_idG:=0;
    b_so_ct:=substr(to_char(b_so_id),3);
else
    if b_kieu<>'B' then b_loi:='loi:Sai kieu:loi'; return; end if;
    b_loi:='loi:So cu da xoa:loi';
    select ngay_ht,ngay_kt,so_id_d,so_id into b_i1,b_i2,b_so_idD,b_so_idG from tbh_tm where so_ct=b_so_ctG;
    if b_i1>b_ngay_ht then b_loi:='loi:Ngay sua doi phai sau ngay phat sinh goc:loi'; return; end if;
    select count(*) into b_i1 from tbh_tm where so_id_g=b_so_idG;
    if b_i1<>0 then b_loi:='loi:Chung tu goc da co sua doi bo sung:loi'; return; end if;
    if b_so_ct is null then b_so_ct:=FTBH_TM_SO_BS(b_so_idD); end if;
end if;
b_loi:='';
exception when others then if b_loi is null then raise PROGRAM_ERROR; else null; end if;
end;