AAA

create or replace procedure PTBH_SOAN_NV_PTN(
    b_so_idT number,b_ma_dvi varchar2,b_so_id number,b_loi out varchar2)
AS
    b_ngay_ht number; b_ngay_hl number; b_ngay_kt number; b_tp number:=0;
    b_nt_tien varchar2(5); b_nt_phi varchar2(5); b_i1 number; b_ch varchar2(10);
    a_ma_taT pht_type.a_var; a_ptT pht_type.a_num;
begin
-- Nam - Ghep nghiep vu ptn
b_loi:='loi:Loi xu ly PTBH_SOAN_NV_PTN:loi';
delete tbh_ghep_nv_temp; delete tbh_ghep_nv_temp2;
b_ch:=FBH_SOAN_BGHD_PTN(b_ma_dvi,b_so_id);
if b_ch=' ' then
    select ngay_ht,ngay_hl,ngay_kt,nt_tien,nt_phi into b_ngay_ht,b_ngay_hl,b_ngay_kt,b_nt_tien,b_nt_phi
        from bh_ptn where ma_dvi=b_ma_dvi and so_id=b_so_id;
else
    select count(*) into b_i1 from bh_ng where ma_dvi=b_ma_dvi and so_id=b_so_id;
    if b_i1<>0 then
        select ngay_ht,ngay_hl,ngay_kt,nt_tien,nt_phi into b_ngay_ht,b_ngay_hl,b_ngay_kt,b_nt_tien,b_nt_phi
            from bh_ptn where ma_dvi=b_ma_dvi and so_id=b_so_id;
    elsif b_ch='CC' then
        select ngay_ht,ngay_hl,ngay_kt,nt_tien,nt_phi into b_ngay_ht,b_ngay_hl,b_ngay_kt,b_nt_tien,b_nt_phi
            from bh_ptncc where ma_dvi=b_ma_dvi and so_id=b_so_id;
    elsif b_ch='NN' then
        select ngay_ht,ngay_hl,ngay_kt,nt_tien,nt_phi into b_ngay_ht,b_ngay_hl,b_ngay_kt,b_nt_tien,b_nt_phi
            from bh_ptnnn where ma_dvi=b_ma_dvi and so_id=b_so_id;
    else
        select ngay_ht,ngay_hl,ngay_kt,nt_tien,nt_phi into b_ngay_ht,b_ngay_hl,b_ngay_kt,b_nt_tien,b_nt_phi
            from bh_ptnHang where ma_dvi=b_ma_dvi and so_id=b_so_id;
    end if;
end if;
b_loi:='loi:Loi xu ly PTBH_SOAN_NV_NG 1:loi';
if b_nt_tien<>'VND' then b_tp:=2; end if;
PTBH_SOAN_NV_PTNd(b_ma_dvi,b_so_id,b_loi);
if b_loi is not null then return; end if;
insert into tbh_ghep_nv_temp
    select ma_ta,b_nt_tien,sum(tien),b_nt_phi,sum(phi),0,sum(do_tien),0,sum(ta_tien),0,sum(ve_tien)
    from tbh_ghep_nv_temp2 group by ma_ta having sum(tien)<>0 or sum(phi)<>0;
select b.ma_ta,sum(b.pt) bulk collect into a_ma_taT,a_ptT from tbh_tmB a,tbh_tmB_phi b where
    a.ma_dviP=b_ma_dvi and a.so_idP=b_so_id and a.so_id<>b_so_idT and b.so_id=a.so_id
    group by b.ma_ta;
for b_lp in 1..a_ma_taT.count loop
    update tbh_ghep_nv_temp set ta_tien=ta_tien+round(tien*a_ptT(b_lp)/100,b_tp) where ma_ta=a_ma_taT(b_lp);
end loop;
update tbh_ghep_nv_temp set do_tl=round(do_tien*100/tien,2),ta_tl=round(ta_tien*100/tien,2),ve_tl=round(ve_tien*100/tien,2) where tien<>0;
delete tbh_ghep_nv_temp2;
b_loi:='';
end;
/
create or replace procedure PTBH_SOAN_NV_PTNd(b_ma_dvi varchar2,b_so_id number,b_loi out varchar2)
AS
    b_ngay_ht number; b_ngay_hl number; b_ngay_kt number; b_ch varchar2(10); b_i1 number;
    b_nt_tien varchar2(5); b_nt_phi varchar2(5); b_dvi_ta varchar2(10):=FTBH_DVI_TA();
begin
-- Dan - Ghep nghiep vu SOAN => nghiep vu tai cho 1 doi tuong
b_loi:='loi:Loi xu ly PTBH_SOAN_NV_PTNd:loi';
delete tbh_ghep_nv_temp1; delete tbh_ghep_nv_temp3;
b_ch:=FBH_SOAN_BGHD_PTN(b_ma_dvi,b_so_id);
if b_ch=' ' then
    select ngay_ht,ngay_hl,ngay_kt,nt_tien,nt_phi into b_ngay_ht,b_ngay_hl,b_ngay_kt,b_nt_tien,b_nt_phi
        from bh_ptn where ma_dvi=b_ma_dvi and so_id=b_so_id;
else
    select count(*) into b_i1 from bh_ng where ma_dvi=b_ma_dvi and so_id=b_so_id;
    if b_i1<>0 then
        select ngay_ht,ngay_hl,ngay_kt,nt_tien,nt_phi into b_ngay_ht,b_ngay_hl,b_ngay_kt,b_nt_tien,b_nt_phi
            from bh_ptn where ma_dvi=b_ma_dvi and so_id=b_so_id;
    elsif b_ch='CC' then
        select ngay_ht,ngay_hl,ngay_kt,nt_tien,nt_phi into b_ngay_ht,b_ngay_hl,b_ngay_kt,b_nt_tien,b_nt_phi
            from bh_ptncc where ma_dvi=b_ma_dvi and so_id=b_so_id;
    elsif b_ch='NN' then
        select ngay_ht,ngay_hl,ngay_kt,nt_tien,nt_phi into b_ngay_ht,b_ngay_hl,b_ngay_kt,b_nt_tien,b_nt_phi
            from bh_ptnnn where ma_dvi=b_ma_dvi and so_id=b_so_id;
    else
        select ngay_ht,ngay_hl,ngay_kt,nt_tien,nt_phi into b_ngay_ht,b_ngay_hl,b_ngay_kt,b_nt_tien,b_nt_phi
            from bh_ptnHang where ma_dvi=b_ma_dvi and so_id=b_so_id;
    end if;
end if;
PBH_SOAN_DK_PTN(b_ma_dvi,b_so_id,b_loi);
if b_loi is not null then return; end if;
insert into tbh_ghep_nv_temp1 select 0,'',a.lh_nv,a.nt_tien,0,a.nt_phi,0,0,
    sum(FBH_DONG_TL_TIEN(b_ma_dvi,b_so_id,0,a.lh_nv,a.tien,'C')),0,0,0,
    sum(FBH_DONG_TL_TIEN(b_ma_dvi,b_so_id,0,a.lh_nv,a.tien,'T'))
    from bh_hd_nv_temp a group by a.lh_nv,a.nt_tien,a.nt_phi;
delete tbh_ghep_nv_temp1 where do_tien=0 and ve_tien=0;
insert into tbh_ghep_nv_temp1 select 0,'',lh_nv,nt_tien,tien,nt_phi,phi,0,0,0,0,0,0 from bh_hd_nv_temp;
update tbh_ghep_nv_temp1 set ma_ta=FBH_MA_LHNV_TAI(lh_nv);
delete tbh_ghep_nv_temp1 where trim(ma_ta) is null;
update tbh_ghep_nv_temp1 set
    tien=FBH_TT_TUNG_QD(b_ngay_ht,nt_tien,tien,b_nt_tien),
    do_tien=FBH_TT_TUNG_QD(b_ngay_ht,nt_tien,do_tien,b_nt_tien),
    ve_tien=FBH_TT_TUNG_QD(b_ngay_ht,nt_tien,ve_tien,b_nt_tien),
    ta_tien=FBH_TT_TUNG_QD(b_ngay_ht,nt_tien,ta_tien,b_nt_tien) where nt_tien<>b_nt_tien;
update tbh_ghep_nv_temp1 set phi=FBH_TT_TUNG_QD(b_ngay_ht,nt_phi,phi,b_nt_phi) where nt_phi<>b_nt_phi;
insert into tbh_ghep_nv_temp3
    select so_id,ma_ta,b_nt_tien,sum(tien),b_nt_phi,sum(phi),0,sum(do_tien),0,sum(ta_tien),0,sum(ve_tien)
    from tbh_ghep_nv_temp1 group by so_id,ma_ta;
insert into tbh_ghep_nv_temp2
    select ma_ta,b_nt_tien,sum(tien),b_nt_phi,sum(phi),0,sum(do_tien),0,sum(ta_tien),0,sum(ve_tien)
    from tbh_ghep_nv_temp3 group by ma_ta having sum(tien)<>0 or sum(phi)<>0;
delete tbh_ghep_nv_temp1; delete tbh_ghep_nv_temp3;
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_SOAN_DK_PTN(
    b_ma_dvi varchar2,b_so_id number,b_loi out varchar2)
AS
    b_i1 number; b_ch varchar(10);
    a_so_id_dtK pht_type.a_num; a_dviK pht_type.a_var; a_ma_dtK pht_type.a_var;
    a_lh_nvK pht_type.a_var; a_nt_tienK pht_type.a_var; a_tienK pht_type.a_num;
    a_nt_phiK pht_type.a_var; a_phiK pht_type.a_num;
    b_ngay_ht number; b_nt_tien varchar2(5); b_nt_phi varchar2(5);
begin
-- Nam - Liet ke nghiep vu theo hop dong
b_loi:='loi:Loi xu ly PBH_SOAN_DK_PTN:loi';
delete bh_hd_nv_temp;
b_ch:=FBH_SOAN_BGHD_PTN(b_ma_dvi,b_so_id);
if b_ch=' ' then
    select ngay_ht,nt_tien,nt_phi into b_ngay_ht,b_nt_tien,b_nt_phi from bh_ptn where ma_dvi=b_ma_dvi and so_id=b_so_id;
    insert into bh_hd_nv_temp select 0,' ',' ', lh_nv,b_nt_tien,sum(tien),0,b_nt_phi,sum(phi)
        from bh_ptn_dk where ma_dvi=b_ma_dvi and so_id=b_so_id group by lh_nv;
else
    select count(*) into b_i1 from bh_ptn where ma_dvi=b_ma_dvi and so_id=b_so_id;
    if b_i1<>0 then
        select ngay_ht,nt_tien,nt_phi into b_ngay_ht,b_nt_tien,b_nt_phi from bh_ptn where ma_dvi=b_ma_dvi and so_id=b_so_id;
        insert into bh_hd_nv_temp select 0,' ',' ', lh_nv,b_nt_tien,sum(tien),0,b_nt_phi,sum(phi)
            from bh_ptn_dk where ma_dvi=b_ma_dvi and so_id=b_so_id group by lh_nv;
    elsif b_ch='CC' then
        select ngay_ht,nt_tien,nt_phi into b_ngay_ht,b_nt_tien,b_nt_phi from bh_ptncc where ma_dvi=b_ma_dvi and so_id=b_so_id;
        insert into bh_hd_nv_temp select 0,' ',' ', lh_nv,b_nt_tien,sum(tien),0,b_nt_phi,sum(phi)
            from bh_ptncc_dk where ma_dvi=b_ma_dvi and so_id=b_so_id group by lh_nv;
    elsif b_ch='NN' then
        select ngay_ht,nt_tien,nt_phi into b_ngay_ht,b_nt_tien,b_nt_phi from bh_ptnnn where ma_dvi=b_ma_dvi and so_id=b_so_id;
        insert into bh_hd_nv_temp select 0,' ',' ', lh_nv,b_nt_tien,sum(tien),0,b_nt_phi,sum(phi)
            from bh_ptnnn_dk where ma_dvi=b_ma_dvi and so_id=b_so_id group by lh_nv;
    else
        select ngay_ht,nt_tien,nt_phi into b_ngay_ht,b_nt_tien,b_nt_phi from bh_ptnHang where ma_dvi=b_ma_dvi and so_id=b_so_id;
        insert into bh_hd_nv_temp select 0,' ',' ', lh_nv,b_nt_tien,sum(tien),0,b_nt_phi,sum(phi)
            from bh_ptnHang_dk where ma_dvi=b_ma_dvi and so_id=b_so_id group by lh_nv;
    end if;
end if;
update bh_hd_nv_temp set tien_vnd=tien;
b_loi:='';
end;
/
create or replace procedure PBH_PTNNN_BPHI_LKE
    (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_nhom varchar2(1);b_ma_sp varchar2(200); b_nghe varchar2(200);
    b_tu number; b_den number; b_dong number; cs_lke clob;
begin
-- Nam
b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'BH','PTN','');
if b_loi is not null then raise PROGRAM_ERROR; end if;
b_lenh:=FKH_JS_LENH('nhom,ma_sp,b_nghe,tu,den');
EXECUTE IMMEDIATE b_lenh into b_nhom,b_ma_sp,b_nghe,b_tu,b_den using b_oraIn;
b_ma_sp:=PKH_MA_TENl(b_ma_sp); b_nghe:=PKH_MA_TENl(b_nghe);
b_nhom:= nvl(b_nhom,' '); b_ma_sp:= nvl(b_ma_sp,' '); b_nghe:= nvl(b_nghe,' ');
select count(*) into b_dong from bh_ptnnn_phi where b_nhom in(' ',nhom) and b_ma_sp in(' ',ma_sp) and b_nghe in(' ',nghe);
PKH_LKE_TRANG(b_dong,b_tu,b_den);
select JSON_ARRAYAGG(json_object(so_id,nhom,ghan,'ma_sp' value FBH_PTNNN_SP_TEN(ma_sp),
    'cdich' value FBH_MA_CDICH_TEN(cdich),'nghe' value FBH_MA_NGHE_TEN(nghe),
    'pvi' value FBH_PTNNN_PVI_TEN(pvi),gct,gtv) returning clob) into cs_lke from
    (select so_id,nhom,ghan,ma_sp,cdich,nghe,pvi,gct,gtv,rownum sott from bh_ptnnn_phi
    where b_nhom in (' ',nhom) and b_ma_sp in(' ',ma_sp) and b_nghe in(' ',nghe)
    order by nhom,ma_sp,nghe)
    where sott between b_tu and b_den;
select json_object('dong' value b_dong,'cs_lke' value cs_lke 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_PTNCC_BPHI_LKE
    (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_ma_sp varchar2(500); b_lvuc varchar2(500);
    b_tu number; b_den number; b_dong number; cs_lke clob;
begin
-- Nam
b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'BH','PTN','');
if b_loi is not null then raise PROGRAM_ERROR; end if;
b_lenh:=FKH_JS_LENH('ma_sp,lvuc,tu,den');
EXECUTE IMMEDIATE b_lenh into b_ma_sp,b_lvuc,b_tu,b_den using b_oraIn;
b_ma_sp:=PKH_MA_TENl(b_ma_sp); b_lvuc:=PKH_MA_TENl(b_lvuc);
b_ma_sp:=nvl(b_ma_sp,' '); b_lvuc:=nvl(b_lvuc,' ');
select count(*) into b_dong from bh_ptncc_phi where b_ma_sp in(' ',ma_sp) and b_lvuc in(' ',lvuc);
PKH_LKE_TRANG(b_dong,b_tu,b_den);
select JSON_ARRAYAGG(json_object(so_id,'ma_sp' value FBH_PTNCC_SP_TEN(ma_sp),'cdich' value FBH_MA_CDICH_TEN(cdich),
       'lvuc' value FBH_MA_LVUC_TEN(lvuc),tlph) returning clob) into cs_lke from 
    (select so_id,ma_sp,cdich,lvuc,tlph,rownum sott from bh_ptncc_phi
    where b_ma_sp in(' ',ma_sp) and b_lvuc in(' ',lvuc)
    order by ma_sp,lvuc)
    where sott between b_tu and b_den;
select json_object('dong' value b_dong,'cs_lke' value cs_lke 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_PTNHANG_BPHI_LKE
    (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_ma_sp varchar2(500);
    b_tu number; b_den number; b_dong number; cs_lke clob;
begin
-- Nam
b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'BH','PTN','');
if b_loi is not null then raise PROGRAM_ERROR; end if;
b_lenh:=FKH_JS_LENH('ma_sp,tu,den');
EXECUTE IMMEDIATE b_lenh into b_ma_sp,b_tu,b_den using b_oraIn;
b_ma_sp:=PKH_MA_TENl(b_ma_sp);
b_ma_sp:=nvl(b_ma_sp,' ');
select count(*) into b_dong from bh_ptnHang_phi where b_ma_sp in(' ',ma_sp);
PKH_LKE_TRANG(b_dong,b_tu,b_den);
select JSON_ARRAYAGG(json_object(so_id,'ma_sp' value FBH_PTNCC_SP_TEN(ma_sp),'cdich' value FBH_MA_CDICH_TEN(cdich),
       'lvuc' value FBH_MA_LVUC_TEN(lvuc),tlph) returning clob) into cs_lke from
    (select so_id,ma_sp,cdich,lvuc,tlph,rownum sott from bh_ptnHang_phi
    where b_ma_sp in(' ',ma_sp) order by ma_sp)
    where sott between b_tu and b_den;
select json_object('dong' value b_dong,'cs_lke' value cs_lke 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 function FBH_PTNNN_BPHI_SO_IDn(
    b_nhom varchar2,b_ma_sp varchar2,b_cdich varchar2,b_nghe varchar2,b_pvi varchar2,
    b_ghan varchar2,b_ngay_hl number:=0,b_gia_ctrinh number,b_gia_hdtv number ) return number

AS
    b_so_id number:=0; b_ngay number:=b_ngay_hl; b_il number; b_gctM number; gtvM number;
begin
-- Nam - Tra so ID phi gan nhat
if b_ngay in(0,30000101) then b_ngay:=PKH_NG_CSO(sysdate); end if;
select count(*),nvl(min(gct),0),nvl(min(gtv),0) into b_il,b_gctM,gtvM
              from bh_ptnnn_phi where nhom=b_nhom and ma_sp=b_ma_sp and cdich in(' ',b_cdich) and nghe=b_nghe and
       pvi in(' ',b_pvi) and ghan in(' ',b_ghan) and b_ngay between ngay_bd and ngay_kt and gct>=b_gia_ctrinh and gtv>=b_gia_hdtv;
   if b_il > 0 then
     select nvl(max(so_id),0) into  b_so_id
              from (select * from bh_ptnnn_phi where nhom=b_nhom and ma_sp=b_ma_sp and cdich in(' ',b_cdich) and nghe=b_nghe and
       pvi in(' ',b_pvi) and ghan in(' ',b_ghan) and b_ngay between ngay_bd and ngay_kt and gct=b_gctM and gtv=gtvM);
   end if;
return b_so_id;
end;
/
create or replace procedure PBH_PTNNNG_BPHI_CTs(
    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); dt_ct clob:=FKH_JS_GTRIs(b_oraIn,'dt_ct');
    b_ma_sp varchar2(10); b_cdich varchar2(10); b_nghe varchar2(10);b_pvi varchar2(10); b_ghan varchar2(1);
    b_ngay_hl number; b_so_id number; b_gia_ct number; b_hdtv number;
begin
-- Nam - Tra so_id bieu phi theo dieu kien
b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'BH','PTN','');
if b_loi is not null then raise PROGRAM_ERROR; end if;
b_lenh:=FKH_JS_LENH('ma_sp,cdich,nghe,pvi,ghan,gct,gtv,ngay_hl');
EXECUTE IMMEDIATE b_lenh into b_ma_sp,b_cdich,b_nghe,b_pvi,b_ghan,b_gia_ct,b_hdtv,b_ngay_hl using dt_ct;
b_ma_sp:=nvl(b_ma_sp,' '); b_cdich:=nvl(b_cdich,' '); b_nghe:=nvl(b_nghe,' ');b_pvi:=nvl(b_pvi,' '); b_gia_ct:=nvl(b_gia_ct,0); b_hdtv:=nvl(b_hdtv,0);
b_so_id:=FBH_PTNNN_BPHI_SO_IDn('G',b_ma_sp,b_cdich,b_nghe,b_pvi,b_ghan,b_ngay_hl,b_gia_ct,b_hdtv);
select json_object('so_id' value b_so_id) 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_PTNNN_BPHI_NH(
    b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_oraIn clob,b_oraOut out clob)
AS
    b_loi varchar2(1000); b_lenh varchar2(1000); b_i1 number; b_i2 number; b_ma_ct varchar2(10);
    b_kt number;
    b_nhom varchar2(1); b_ma_sp varchar2(500); b_cdich varchar2(500); b_pvi varchar2(500);
    b_nghe varchar2(500); b_ghan varchar2(1); b_ngay_bd number; b_ngay_kt number; b_gia_ctrinh number; b_gia_hdtv 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_lkeM pht_type.a_var; dk_lkeP pht_type.a_var; dk_lkeB pht_type.a_var; dk_luy pht_type.a_var;
    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_lh_bh pht_type.a_var;

    dkB_ma pht_type.a_var; dkB_ten pht_type.a_nvar; dkB_tc pht_type.a_var; dkB_ma_ct pht_type.a_var;
    dkB_kieu pht_type.a_var; dkB_tien pht_type.a_num; dkB_pt pht_type.a_num;
    dkB_phi pht_type.a_num; dkB_lkeM pht_type.a_var; dkB_lkeP pht_type.a_var; dkB_lkeB pht_type.a_var; dkB_luy pht_type.a_var;
    dkB_ma_dk pht_type.a_var;

    lt_ma_dk pht_type.a_var; lt_ma_lt pht_type.a_var; lt_ten pht_type.a_var;
    b_dt_ct clob; b_dt_dk clob; b_dt_dkbs clob; b_dt_lt clob; b_dt_khd clob; b_dt_kbt clob; b_so_id number;
begin
-- Nam - Nhap
b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'BH','PTN','M');
if b_loi is not null then raise PROGRAM_ERROR; end if;
b_lenh:=FKH_JS_LENHc('dt_ct,dt_dk,dt_dkbs,dt_lt,dt_khd,dt_kbt');
EXECUTE IMMEDIATE b_lenh into b_dt_ct,b_dt_dk,b_dt_dkbs,b_dt_lt,b_dt_khd,b_dt_kbt using b_oraIn;
b_lenh:=FKH_JS_LENH('nhom,ma_sp,cdich,pvi,nghe,ghan,ngay_bd,ngay_kt,gct,gtv');
EXECUTE IMMEDIATE b_lenh into b_nhom,b_ma_sp,b_cdich,b_pvi,b_nghe,b_ghan,b_ngay_bd,b_ngay_kt,b_gia_ctrinh,b_gia_hdtv using b_dt_ct;
if trim(b_ma_sp) is null then
    b_loi:='loi:Nhap san pham:loi'; raise PROGRAM_ERROR;
elsif trim(b_ma_sp) is not null then b_ma_sp:=PKH_MA_TENl(b_ma_sp);
elsif FBH_PTNNN_SP_HAN(b_ma_sp)<>'C' then
    b_loi:='loi:Ma san pham '||b_ma_sp||'da het su dung:loi'; raise PROGRAM_ERROR;
end if;
if trim(b_cdich) is null then
    b_cdich:=' ';
elsif trim(b_cdich) is not null then b_cdich:=PKH_MA_TENl(b_cdich);
elsif FBH_MA_CDICH_HAN(b_cdich)<>'C' then
    b_loi:='loi:Da het chien dich:loi'; raise PROGRAM_ERROR;
end if;
if trim(b_pvi) is null then b_pvi:=' ';
else b_pvi:=PKH_MA_TENl(b_pvi);
end if;
if trim(b_nghe) is null then b_loi:='loi:Nhap nghe nghiep:loi'; raise PROGRAM_ERROR; end if;
b_nghe:=PKH_MA_TENl(b_nghe);
b_nhom:=nvl(b_nhom,' '); b_ma_sp:=nvl(b_ma_sp,' '); b_cdich:=nvl(b_cdich,' ');
b_pvi:=nvl(b_pvi,' '); b_nghe:=nvl(b_nghe,' '); b_ghan:=nvl(b_ghan,' ');
if b_gia_ctrinh is null then b_gia_ctrinh:=0;end if;
if b_gia_hdtv is null then b_gia_hdtv:=0;end if;
if b_ngay_bd is null or b_ngay_bd in(0,30000101) then b_ngay_bd:=PKH_NG_CSO(sysdate); end if;
if b_ngay_kt is null or b_ngay_kt=0 then b_ngay_kt:=30000101; end if;
b_lenh:=FKH_JS_LENH('ma,ten,tc,ma_ct,ma_dk,kieu,tien,pt,phi,lkem,lkep,lkeb,luy');
EXECUTE IMMEDIATE b_lenh bulk collect into
    dk_ma,dk_ten,dk_tc,dk_ma_ct,dk_ma_dk,dk_kieu,dk_tien,dk_pt,dk_phi,dk_lkeM,dk_lkeP,dk_lkeB,dk_luy using b_dt_dk;
b_kt:=dk_ma.count;
if b_kt=0 then b_loi:='loi:Nhap dieu khoan:loi'; raise PROGRAM_ERROR; end if;
for b_lp in 1..b_kt loop dk_lh_bh(b_lp):='C'; end loop;
if trim(b_dt_dkbs) is not null then
    EXECUTE IMMEDIATE b_lenh bulk collect into
        dkB_ma,dkB_ten,dkB_tc,dkB_ma_ct,dkB_ma_dk,dkB_kieu,dkB_tien,dkB_pt,dkB_phi,
        dkB_lkeM,dkB_lkeP,dkB_lkeB,dkB_luy using b_dt_dkbs;
    for b_lp in 1..dkB_ma.count loop
        b_kt:=b_kt+1; dk_lh_bh(b_kt):='M';
        dk_ma(b_kt):=dkB_ma(b_lp); dk_ten(b_kt):=dkB_ten(b_lp); dk_tc(b_kt):=dkB_tc(b_lp);
        dk_ma_ct(b_kt):=dkB_ma_ct(b_lp); dk_ma_dk(b_kt):=dkB_ma_dk(b_lp); dk_kieu(b_kt):=dkB_kieu(b_lp);
        dk_tien(b_kt):=dkB_tien(b_lp); dk_pt(b_kt):=dkB_pt(b_lp); dk_phi(b_kt):=dkB_phi(b_lp);
        dk_lkeM(b_kt):=dkB_lkeM(b_lp); dk_lkeP(b_kt):=dkB_lkeP(b_lp); dk_lkeB(b_kt):=dkB_lkeB(b_lp); dk_luy(b_kt):=dkB_luy(b_lp);
    end loop;
end if;
for b_lp in 1..dk_ma.count loop
    b_loi:='loi:Sai chi tiet dong '||to_char(b_lp)||':loi';
    dk_ma(b_lp):=nvl(trim(dk_ma(b_lp)),' ');
    if dk_ma(b_lp)=' ' then return; end if;
    if trim(dk_ten(b_lp)) is null then
        b_i1:=b_lp-1; b_i2:=0;
        for b_lp1 in 1..b_i1 loop
            if dk_ma(b_lp1)=dk_ma(b_lp) then b_i2:=1; exit; end if;
        end loop;
        if b_i2=0 then return; end if;
    end if;
    dk_tc(b_lp):=nvl(trim(dk_tc(b_lp)),'C');
    dk_ma_ct(b_lp):=nvl(trim(dk_ma_ct(b_lp)),' ');
    if dk_ma(b_lp)=dk_ma_ct(b_lp) then b_loi:='loi:Trung ma cap tren ma: '||dk_ma(b_lp)||':loi'; raise PROGRAM_ERROR; end if;
    if dk_tc(b_lp)='C' then
        dk_lkeP(b_lp):=nvl(trim(dk_lkeP(b_lp)),'G'); dk_lkeB(b_lp):=nvl(trim(dk_lkeB(b_lp)),'G');
    else
        dk_lkeP(b_lp):=nvl(trim(dk_lkeP(b_lp)),'T'); dk_lkeB(b_lp):=nvl(trim(dk_lkeB(b_lp)),'T');
    end if;
    dk_luy(b_lp):=nvl(trim(dk_luy(b_lp)),'C'); dk_kieu(b_lp):=nvl(trim(dk_kieu(b_lp)),'T');
    dk_ma_dk(b_lp):=nvl(trim(dk_ma_dk(b_lp)),' '); dk_lh_nv(b_lp):=' '; dk_t_suat(b_lp):=0;
    if dk_ma_dk(b_lp)<>' ' then
        if dk_lh_bh(b_lp)='C' then
            if FBH_MA_DK_HAN(dk_ma_dk(b_lp))='K' then
                b_loi:='loi:Dieu khoan: '||dk_ma_dk(b_lp)||' da het su dung:loi'; raise PROGRAM_ERROR;
            end if;
            select nvl(min(lh_nv),' ') into dk_lh_nv(b_lp) from bh_ma_dk where ma=dk_ma_dk(b_lp);
        else
            if FBH_MA_DKBS_HAN(dk_ma_dk(b_lp))='K' then
                b_loi:='loi:Dieu khoan bo sung: '||dk_ma_dk(b_lp)||' da het su dung:loi'; raise PROGRAM_ERROR;
            end if;
            select nvl(min(lh_nv),' ') into dk_lh_nv(b_lp) from bh_ma_dkbs where ma=dk_ma_dk(b_lp);
        end if;
        if dk_lh_nv(b_lp)<>' ' then dk_t_suat(b_lp):=FBH_MA_LHNV_THUE(dk_lh_nv(b_lp)); end if;
    end if;
end loop;
for b_lp in 1..dk_ma.count loop
    b_i1:=b_lp+1;
    for b_lp1 in b_i1..dk_ma.count loop
        if dk_ma(b_lp)=dk_ma(b_lp1) and dk_tien(b_lp)=dk_tien(b_lp1) then b_loi:='loi:Trung ma: '||dk_ma(b_lp)||':loi'; raise PROGRAM_ERROR;
        end if;
    end loop;
    if dk_tc(b_lp)='T' then
        b_i1:=0;
        for b_lp1 in 1..dk_ma.count loop
            if dk_ma(b_lp)=dk_ma_ct(b_lp1) and dk_tc(b_lp1)<>'K' then b_i1:=1; exit; end if;
        end loop;
        if b_i1=0 then b_loi:='loi:Nhap ma chi tiet ma: '||dk_ma(b_lp)||':loi'; raise PROGRAM_ERROR; end if;
    end if;
end loop;
for b_lp in 1..dk_ma.count loop
    if dk_tc(b_lp)='C' and dk_ma_ct(b_lp)!=' ' then
        b_i1:=0;
        for b_lp1 in 1..dk_ma.count loop
            if dk_ma(b_lp1)=dk_ma_ct(b_lp) and dk_tc(b_lp1)='T' then b_i1:=1; exit; end if;
        end loop;
        if b_i1=0 then b_loi:='loi:Nhap ma cap tren ma: '||dk_ma(b_lp)||':loi'; raise PROGRAM_ERROR; end if;
    end if;
end loop;
for b_lp in 1..dk_ma.count loop
    if dk_tc(b_lp)='C' and dk_ma_ct(b_lp)!=' ' then
        b_lenh:=dk_ma(b_lp); b_ma_ct:=dk_ma_ct(b_lp);
        while b_ma_ct<>' ' loop
            b_i1:=0;
            for b_lp1 in 1..dk_ma.count loop
                if dk_ma(b_lp1)=b_ma_ct then b_i1:=b_lp1; exit; end if;
            end loop;
            if b_i1=0 then
                b_ma_ct:='';
            else
                b_ma_ct:=dk_ma_ct(b_i1);
                if PKH_MA_MA(b_lenh,b_ma_ct) then b_loi:='loi:Vong lap ma cap tren ma: '||dk_ma(b_lp)||':loi'; raise PROGRAM_ERROR; end if;
                b_lenh:=b_lenh||','||dk_ma(b_i1);
            end if;
        end loop;
    end if;
end loop;
for b_lp in 1..dk_ma.count loop
    if dk_lkeM(b_lp)<>'C' then
        dk_ma_dkC(b_lp):=' ';
    elsif dk_lh_bh(b_lp)='C' then
        dk_ma_dkC(b_lp):=dk_ma_ct(b_lp);
    else
        dk_ma_dkC(b_lp):=FBH_MA_DKBS_MA_DK(dk_ma_dk(b_lp));
    end if;
end loop;
if trim(b_dt_lt) is not null then
    b_lenh:=FKH_JS_LENH('ma_dk,ma_lt,ten');
    EXECUTE IMMEDIATE b_lenh bulk collect into lt_ma_dk,lt_ma_lt,lt_ten using b_dt_lt;
    for b_lp in 1..lt_ma_lt.count loop
        if trim(lt_ma_lt(b_lp)) is null then
            b_loi:='loi:Nhap ma loai tru dong '||to_char(b_lp)||':loi'; raise PROGRAM_ERROR;
        end if;
        if trim(lt_ma_dk(b_lp)) is null then lt_ma_dk(b_lp):=' '; end if;
        if FBH_MA_DKLT_HAN(lt_ma_dk(b_lp),lt_ma_lt(b_lp))='K' then
            b_loi:='loi:Dieu khoan loai tru: '||lt_ma_lt(b_lp)||' da het su dung:loi'; raise PROGRAM_ERROR;
        end if;
    end loop;
end if;
b_so_id:=FBH_PTNNN_BPHI_SO_IDn(b_nhom,b_ma_sp,b_cdich,b_nghe,b_pvi,b_ghan,b_ngay_bd,b_gia_ctrinh,b_gia_hdtv);
if b_so_id<>0 then
    delete bh_ptnnn_phi_txt where so_id=b_so_id;
    delete bh_ptnnn_phi_lt where so_id=b_so_id;
    delete bh_ptnnn_phi_dk where so_id=b_so_id;
    delete bh_ptnnn_phi where so_id=b_so_id;
end if;
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;
b_loi:='loi:Loi Table bh_ptnnn_phi:loi';
insert into bh_ptnnn_phi values(b_ma_dvi,b_so_id,b_ma_sp,b_cdich,b_nghe,b_pvi,b_ghan,b_nhom,b_gia_ctrinh,b_gia_hdtv,b_ngay_bd,b_ngay_kt,b_nsd);
insert into bh_ptnnn_phi_txt values(b_ma_dvi,b_so_id,'dt_ct','"'||b_dt_ct||'"');
insert into bh_ptnnn_phi_txt values(b_ma_dvi,b_so_id,'dt_dk','"'||b_dt_dk||'"');
if trim(b_dt_dkbs) is not null then
    insert into bh_ptnnn_phi_txt values(b_ma_dvi,b_so_id,'dt_dkbs','"'||b_dt_dkbs||'"');
end if;
for b_lp in 1..dk_ma.count loop
    insert into bh_ptnnn_phi_dk values(b_ma_dvi,b_so_id,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_lkeM(b_lp),dk_lkeP(b_lp),dk_lkeB(b_lp),dk_luy(b_lp),
        dk_ma_dk(b_lp),dk_ma_dkC(b_lp),dk_lh_nv(b_lp),dk_t_suat(b_lp),0,dk_lh_bh(b_lp));
end loop;
for r_lp in(select ma,ma_ct,so_id,bt,level from (select t.so_id,t.bt,t.ma,t.ma_ct from bh_ptnnn_phi_dk t where t.so_id = b_so_id) t start with t.ma_ct=' '  CONNECT BY prior t.ma=t.ma_ct) loop
    update bh_ptnnn_phi_dk set cap=r_lp.level where so_id=r_lp.so_id and bt=r_lp.bt;
end loop;
if lt_ma_lt.count<>0 then
    for b_lp in 1..lt_ma_lt.count loop
        insert into bh_ptnnn_phi_lt values(b_ma_dvi,b_so_id,b_lp,lt_ma_lt(b_lp),lt_ten(b_lp));
    end loop;
    insert into bh_ptnnn_phi_txt values(b_ma_dvi,b_so_id,'dt_lt','"'||b_dt_lt||'"');
end if;
if trim(b_dt_khd) is not null then
    insert into bh_ptnnn_phi_txt values(b_ma_dvi,b_so_id,'dt_khd','"'||b_dt_khd||'"');
end if;
if trim(b_dt_kbt) is not null then
    insert into bh_ptnnn_phi_txt values(b_ma_dvi,b_so_id,'dt_kbt','"'||b_dt_kbt||'"');
end if;
commit;
select json_object('so_id' value b_so_id) 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_PTNCC_BPHI_NH(
    b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_oraIn clob,b_oraOut out clob)
AS
    b_loi varchar2(1000); b_lenh varchar2(1000); b_i1 number; b_ma_ct varchar2(10);
    b_kt number; b_i2 number;
    b_lvuc varchar2(100); b_ma_sp varchar2(100); b_cdich varchar2(100);
    b_tl_phi number; b_ngay_bd number; b_ngay_kt 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_lkeM pht_type.a_var; dk_lkeP pht_type.a_var; dk_lkeB pht_type.a_var; dk_luy pht_type.a_var;
    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_lh_bh pht_type.a_var;

    dkB_ma pht_type.a_var; dkB_ten pht_type.a_nvar; dkB_tc pht_type.a_var; dkB_ma_ct pht_type.a_var;
    dkB_kieu pht_type.a_var; dkB_tien pht_type.a_num; dkB_pt pht_type.a_num;
    dkB_phi pht_type.a_num; dkB_lkeM pht_type.a_var; dkB_lkeP pht_type.a_var; dkB_lkeB pht_type.a_var; dkB_luy pht_type.a_var;
    dkB_ma_dk pht_type.a_var;

    lt_ma_dk pht_type.a_var; lt_ma_lt pht_type.a_var; lt_ten pht_type.a_var;
    b_dt_ct clob; b_dt_dk clob; b_dt_dkbs clob; b_dt_lt clob; b_dt_khd clob; b_dt_kbt clob; b_so_id number;
begin
-- Dan - Nhap
b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'BH','TNCC','');
if b_loi is not null then raise PROGRAM_ERROR; end if;
b_lenh:=FKH_JS_LENHc('dt_ct,dt_dk,dt_dkbs,dt_lt,dt_khd,dt_kbt');
EXECUTE IMMEDIATE b_lenh into b_dt_ct,b_dt_dk,b_dt_dkbs,b_dt_lt,b_dt_khd,b_dt_kbt using b_oraIn;
b_lenh:=FKH_JS_LENH('ma_sp,cdich,lvuc,tlph,ngay_bd,ngay_kt');
EXECUTE IMMEDIATE b_lenh into b_ma_sp,b_cdich,b_lvuc,b_tl_phi,b_ngay_bd,b_ngay_kt using b_dt_ct;
if trim(b_ma_sp) is null then
    b_loi:='loi:Nhap san pham:loi'; raise PROGRAM_ERROR;
elsif FBH_PTNCC_SP_HAN(b_ma_sp)<>'C' then
    b_loi:='loi:Ma san pham '||b_ma_sp||'da het su dung:loi'; raise PROGRAM_ERROR;
end if;
b_lvuc:=PKH_MA_TENl(b_lvuc);
if trim(b_cdich) is not null then
   if FBH_MA_CDICH_HAN(b_cdich)<>'C' then
    b_loi:='loi:Da het chien dich:loi'; raise PROGRAM_ERROR;
    end if;
    else b_cdich:= ' ';
end if;
b_ma_sp:=nvl(b_ma_sp,' '); b_cdich:=nvl(b_cdich,' '); b_lvuc:=nvl(b_lvuc,' '); b_tl_phi:=nvl(b_tl_phi,0); 
if b_ngay_bd is null or b_ngay_bd in(0,30000101) then b_ngay_bd:=PKH_NG_CSO(sysdate); end if;
if b_ngay_kt is null or b_ngay_kt=0 then b_ngay_kt:=30000101; end if;
b_lenh:=FKH_JS_LENH('ma,ten,tc,ma_ct,ma_dk,kieu,tien,pt,phi,lkem,lkep,lkeb,luy');
EXECUTE IMMEDIATE b_lenh bulk collect into
    dk_ma,dk_ten,dk_tc,dk_ma_ct,dk_ma_dk,dk_kieu,dk_tien,dk_pt,dk_phi,dk_lkeM,dk_lkeP,dk_lkeB,dk_luy using b_dt_dk;
b_kt:=dk_ma.count;
if b_kt=0 then b_loi:='loi:Nhap dieu khoan:loi'; raise PROGRAM_ERROR; end if;
for b_lp in 1..b_kt loop dk_lh_bh(b_lp):='C'; end loop;
if trim(b_dt_dkbs) is not null then
    EXECUTE IMMEDIATE b_lenh bulk collect into
        dkB_ma,dkB_ten,dkB_tc,dkB_ma_ct,dkB_ma_dk,dkB_kieu,dkB_tien,dkB_pt,dkB_phi,
        dkB_lkeM,dkB_lkeP,dkB_lkeB,dkB_luy using b_dt_dkbs;
    for b_lp in 1..dkB_ma.count loop
        b_kt:=b_kt+1; dk_lh_bh(b_kt):='M';
        dk_ma(b_kt):=dkB_ma(b_lp); dk_ten(b_kt):=dkB_ten(b_lp); dk_tc(b_kt):=dkB_tc(b_lp);
        dk_ma_ct(b_kt):=dkB_ma_ct(b_lp); dk_ma_dk(b_kt):=dkB_ma_dk(b_lp); dk_kieu(b_kt):=dkB_kieu(b_lp);
        dk_tien(b_kt):=dkB_tien(b_lp); dk_pt(b_kt):=dkB_pt(b_lp); dk_phi(b_kt):=dkB_phi(b_lp);
        dk_lkeM(b_kt):=dkB_lkeM(b_lp); dk_lkeP(b_kt):=dkB_lkeP(b_lp); dk_lkeB(b_kt):=dkB_lkeB(b_lp); dk_luy(b_kt):=dkB_luy(b_lp);
    end loop;
end if;
for b_lp in 1..dk_ma.count loop
    b_loi:='loi:Sai chi tiet dong '||to_char(b_lp)||':loi';
    dk_ma(b_lp):=nvl(trim(dk_ma(b_lp)),' ');
    if dk_ma(b_lp)=' ' then return; end if;
    if trim(dk_ten(b_lp)) is null then
        b_i1:=b_lp-1; b_i2:=0;
        for b_lp1 in 1..b_i1 loop
            if dk_ma(b_lp1)=dk_ma(b_lp) then b_i2:=1; exit; end if;
        end loop;
        if b_i2=0 then return; end if;
    end if;
    dk_tc(b_lp):=nvl(trim(dk_tc(b_lp)),'C');
    dk_ma_ct(b_lp):=nvl(trim(dk_ma_ct(b_lp)),' ');
    if dk_ma(b_lp)=dk_ma_ct(b_lp) then b_loi:='loi:Trung ma cap tren ma: '||dk_ma(b_lp)||':loi'; raise PROGRAM_ERROR; end if;
    if dk_tc(b_lp)='C' then
        dk_lkeP(b_lp):=nvl(trim(dk_lkeP(b_lp)),'G'); dk_lkeB(b_lp):=nvl(trim(dk_lkeB(b_lp)),'G');
    else
        dk_lkeP(b_lp):=nvl(trim(dk_lkeP(b_lp)),'T'); dk_lkeB(b_lp):=nvl(trim(dk_lkeB(b_lp)),'T');
    end if;
    dk_luy(b_lp):=nvl(trim(dk_luy(b_lp)),'C'); dk_kieu(b_lp):=nvl(trim(dk_kieu(b_lp)),'T');
    dk_ma_dk(b_lp):=nvl(trim(dk_ma_dk(b_lp)),' '); dk_lh_nv(b_lp):=' '; dk_t_suat(b_lp):=0;
    if dk_ma_dk(b_lp)<>' ' then
        if dk_lh_bh(b_lp)='C' then
            if FBH_MA_DK_HAN(dk_ma_dk(b_lp))='K' then
                b_loi:='loi:Dieu khoan: '||dk_ma_dk(b_lp)||' da het su dung:loi'; raise PROGRAM_ERROR;
            end if;
            select nvl(min(lh_nv),' ') into dk_lh_nv(b_lp) from bh_ma_dk where ma=dk_ma_dk(b_lp);
        else
            if FBH_MA_DKBS_HAN(dk_ma_dk(b_lp))='K' then
                b_loi:='loi:Dieu khoan bo sung: '||dk_ma_dk(b_lp)||' da het su dung:loi'; raise PROGRAM_ERROR;
            end if;
            select nvl(min(lh_nv),' ') into dk_lh_nv(b_lp) from bh_ma_dkbs where ma=dk_ma_dk(b_lp);
        end if;
        if dk_lh_nv(b_lp)<>' ' then dk_t_suat(b_lp):=FBH_MA_LHNV_THUE(dk_lh_nv(b_lp)); end if;
    end if;
end loop;
for b_lp in 1..dk_ma.count loop
    b_i1:=b_lp+1;
    for b_lp1 in b_i1..dk_ma.count loop
        if dk_ma(b_lp)=dk_ma(b_lp1) and dk_tien(b_lp)=dk_tien(b_lp1) then b_loi:='loi:Trung ma: '||dk_ma(b_lp)||':loi'; raise PROGRAM_ERROR;
        end if;
    end loop;
    if dk_tc(b_lp)='T' then
        b_i1:=0;
        for b_lp1 in 1..dk_ma.count loop
            if dk_ma(b_lp)=dk_ma_ct(b_lp1) and dk_tc(b_lp1)<>'K' then b_i1:=1; exit; end if;
        end loop;
        if b_i1=0 then b_loi:='loi:Nhap ma chi tiet ma: '||dk_ma(b_lp)||':loi'; raise PROGRAM_ERROR; end if;
    end if;
end loop;
for b_lp in 1..dk_ma.count loop
    if dk_tc(b_lp)='C' and dk_ma_ct(b_lp)!=' ' then
        b_i1:=0;
        for b_lp1 in 1..dk_ma.count loop
            if dk_ma(b_lp1)=dk_ma_ct(b_lp) and dk_tc(b_lp1)='T' then b_i1:=1; exit; end if;
        end loop;
        if b_i1=0 then b_loi:='loi:Nhap ma cap tren ma: '||dk_ma(b_lp)||':loi'; raise PROGRAM_ERROR; end if;
    end if;
end loop;
for b_lp in 1..dk_ma.count loop
    if dk_tc(b_lp)='C' and dk_ma_ct(b_lp)!=' ' then
        b_lenh:=dk_ma(b_lp); b_ma_ct:=dk_ma_ct(b_lp);
        while b_ma_ct<>' ' loop
            b_i1:=0;
            for b_lp1 in 1..dk_ma.count loop
                if dk_ma(b_lp1)=b_ma_ct then b_i1:=b_lp1; exit; end if;
            end loop;
            if b_i1=0 then
                b_ma_ct:='';
            else
                b_ma_ct:=dk_ma_ct(b_i1);
                if PKH_MA_MA(b_lenh,b_ma_ct) then b_loi:='loi:Vong lap ma cap tren ma: '||dk_ma(b_lp)||':loi'; raise PROGRAM_ERROR; end if;
                b_lenh:=b_lenh||','||dk_ma(b_i1);
            end if;
        end loop;
    end if;
end loop;
for b_lp in 1..dk_ma.count loop
    if dk_lkeM(b_lp)<>'C' then
        dk_ma_dkC(b_lp):=' ';
    elsif dk_lh_bh(b_lp)='C' then
        dk_ma_dkC(b_lp):=dk_ma_ct(b_lp);
    else
        dk_ma_dkC(b_lp):=FBH_MA_DKBS_MA_DK(dk_ma_dk(b_lp));
    end if;
end loop;
if trim(b_dt_lt) is not null then
    b_lenh:=FKH_JS_LENH('ma_dk,ma_lt,ten');
    EXECUTE IMMEDIATE b_lenh bulk collect into lt_ma_dk,lt_ma_lt,lt_ten using b_dt_lt;
    for b_lp in 1..lt_ma_lt.count loop
        if trim(lt_ma_lt(b_lp)) is null then
            b_loi:='loi:Nhap ma loai tru dong '||to_char(b_lp)||':loi'; raise PROGRAM_ERROR;
        end if;
        if trim(lt_ma_dk(b_lp)) is null then lt_ma_dk(b_lp):=' '; end if;
        if FBH_MA_DKLT_HAN(lt_ma_dk(b_lp),lt_ma_lt(b_lp))='K' then
            b_loi:='loi:Dieu khoan loai tru: '||lt_ma_lt(b_lp)||' da het su dung:loi'; raise PROGRAM_ERROR;
        end if;
    end loop;
end if;
b_so_id:=FBH_PTNCC_BPHI_SO_IDh(b_ma_sp,b_cdich,b_lvuc,b_ngay_bd);
if b_so_id<>0 then
    delete bh_ptncc_phi_txt where so_id=b_so_id;
    delete bh_ptncc_phi_lt where so_id=b_so_id;
    delete bh_ptncc_phi_dk where so_id=b_so_id;
    delete bh_ptncc_phi where so_id=b_so_id;
end if;
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;
b_loi:='loi:Loi Table bh_ptncc_phi:loi';
insert into bh_ptncc_phi values(b_ma_dvi,b_so_id,b_ma_sp,b_lvuc,b_cdich,b_tl_phi,b_ngay_bd,b_ngay_kt,b_nsd);
insert into bh_ptncc_phi_txt values(b_ma_dvi,b_so_id,'dt_ct','"'||b_dt_ct||'"');
insert into bh_ptncc_phi_txt values(b_ma_dvi,b_so_id,'dt_dk','"'||b_dt_dk||'"');
if trim(b_dt_dkbs) is not null then
    insert into bh_ptncc_phi_txt values(b_ma_dvi,b_so_id,'dt_dkbs','"'||b_dt_dkbs||'"');
end if;
for b_lp in 1..dk_ma.count loop
   insert into bh_ptncc_phi_dk values(b_ma_dvi,b_so_id,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_lkeM(b_lp),dk_lkeP(b_lp),dk_lkeB(b_lp),dk_luy(b_lp),
        dk_ma_dk(b_lp),dk_ma_dkC(b_lp),dk_lh_nv(b_lp),dk_t_suat(b_lp),0,dk_lh_bh(b_lp));
end loop;
for r_lp in(select ma,ma_ct,so_id,bt,level from  (select t.so_id,t.bt,t.ma,t.ma_ct from bh_ptncc_phi_dk t where t.so_id = b_so_id) t start with t.ma_ct=' '  CONNECT BY prior t.ma=t.ma_ct) loop
    update bh_ptncc_phi_dk set cap=r_lp.level where so_id=r_lp.so_id and bt=r_lp.bt;
end loop;
if lt_ma_lt.count<>0 then
    for b_lp in 1..lt_ma_lt.count loop
        insert into bh_ptncc_phi_lt values(b_ma_dvi,b_so_id,b_lp,lt_ma_lt(b_lp),lt_ten(b_lp));
    end loop;
    insert into bh_ptncc_phi_txt values(b_ma_dvi,b_so_id,'dt_lt','"'||b_dt_lt||'"');
end if;
if trim(b_dt_khd) is not null then
    insert into bh_ptncc_phi_txt values(b_ma_dvi,b_so_id,'dt_khd','"'||b_dt_khd||'"');
end if;
if trim(b_dt_kbt) is not null then
    insert into bh_ptncc_phi_txt values(b_ma_dvi,b_so_id,'dt_kbt','"'||b_dt_kbt||'"');
end if;
commit;
select json_object('so_id' value b_so_id) 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_PTNHANG_BPHI_NH(
    b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_oraIn clob,b_oraOut out clob)
AS
    b_loi varchar2(1000); b_lenh varchar2(1000); b_i1 number; b_ma_ct varchar2(10);
    b_kt number; b_i2 number;
    b_lvuc varchar2(100); b_ma_sp varchar2(100); b_cdich varchar2(100);
    b_tl_phi number; b_ngay_bd number; b_ngay_kt 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_tientt pht_type.a_num;
    dk_lkeM pht_type.a_var; dk_lkeP pht_type.a_var; dk_lkeB pht_type.a_var; dk_luy pht_type.a_var;
    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_lh_bh pht_type.a_var;

    dkB_ma pht_type.a_var; dkB_ten pht_type.a_nvar; dkB_tc pht_type.a_var; dkB_ma_ct pht_type.a_var;
    dkB_kieu pht_type.a_var; dkB_tien pht_type.a_num; dkB_pt pht_type.a_num; dkB_tientt pht_type.a_num;
    dkB_lkeM pht_type.a_var; dkB_lkeP pht_type.a_var; dkB_lkeB pht_type.a_var; dkB_luy pht_type.a_var;
    dkB_ma_dk pht_type.a_var;

    lt_ma_dk pht_type.a_var; lt_ma_lt pht_type.a_var; lt_ten pht_type.a_var;
    b_dt_ct clob; b_dt_dk clob; b_dt_dkbs clob; b_dt_lt clob; b_dt_khd clob; b_dt_kbt clob; b_so_id number;
begin
-- Dan - Nhap
b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'BH','TNCC','');
if b_loi is not null then raise PROGRAM_ERROR; end if;
b_lenh:=FKH_JS_LENHc('dt_ct,dt_dk,dt_dkbs,dt_lt,dt_khd,dt_kbt');
EXECUTE IMMEDIATE b_lenh into b_dt_ct,b_dt_dk,b_dt_dkbs,b_dt_lt,b_dt_khd,b_dt_kbt using b_oraIn;
b_lenh:=FKH_JS_LENH('ma_sp,cdich,lvuc,tlph,ngay_bd,ngay_kt');
EXECUTE IMMEDIATE b_lenh into b_ma_sp,b_cdich,b_lvuc,b_tl_phi,b_ngay_bd,b_ngay_kt using b_dt_ct;
if trim(b_ma_sp) is null then
    b_loi:='loi:Nhap san pham:loi'; raise PROGRAM_ERROR;
elsif FBH_PTNCC_SP_HAN(b_ma_sp)<>'C' then
    b_loi:='loi:Ma san pham '||b_ma_sp||'da het su dung:loi'; raise PROGRAM_ERROR;
end if;
b_lvuc:=PKH_MA_TENl(b_lvuc);
if trim(b_cdich) is not null then
   if FBH_MA_CDICH_HAN(b_cdich)<>'C' then
    b_loi:='loi:Da het chien dich:loi'; raise PROGRAM_ERROR;
    end if;
    else b_cdich:= ' ';
end if;
b_ma_sp:=nvl(b_ma_sp,' '); b_cdich:=nvl(b_cdich,' '); b_lvuc:=nvl(b_lvuc,' '); b_tl_phi:=nvl(b_tl_phi,0); 
if b_ngay_bd is null or b_ngay_bd in(0,30000101) then b_ngay_bd:=PKH_NG_CSO(sysdate); end if;
if b_ngay_kt is null or b_ngay_kt=0 then b_ngay_kt:=30000101; end if;
b_lenh:=FKH_JS_LENH('ma,ten,tc,ma_ct,ma_dk,kieu,tien,pt,tientt,lkem,lkep,lkeb,luy');
EXECUTE IMMEDIATE b_lenh bulk collect into
    dk_ma,dk_ten,dk_tc,dk_ma_ct,dk_ma_dk,dk_kieu,dk_tien,dk_pt,dk_tientt,dk_lkeM,dk_lkeP,dk_lkeB,dk_luy using b_dt_dk;
b_kt:=dk_ma.count;
if b_kt=0 then b_loi:='loi:Nhap dieu khoan:loi'; raise PROGRAM_ERROR; end if;
for b_lp in 1..b_kt loop dk_lh_bh(b_lp):='C'; end loop;
if trim(b_dt_dkbs) is not null then
    EXECUTE IMMEDIATE b_lenh bulk collect into
        dkB_ma,dkB_ten,dkB_tc,dkB_ma_ct,dkB_ma_dk,dkB_kieu,dkB_tien,dkB_pt,
        dkB_tientt,dkB_lkeM,dkB_lkeP,dkB_lkeB,dkB_luy using b_dt_dkbs;
    for b_lp in 1..dkB_ma.count loop
        b_kt:=b_kt+1; dk_lh_bh(b_kt):='M';
        dk_ma(b_kt):=dkB_ma(b_lp); dk_ten(b_kt):=dkB_ten(b_lp); dk_tc(b_kt):=dkB_tc(b_lp);
        dk_ma_ct(b_kt):=dkB_ma_ct(b_lp); dk_ma_dk(b_kt):=dkB_ma_dk(b_lp); dk_kieu(b_kt):=dkB_kieu(b_lp);
        dk_tien(b_kt):=dkB_tien(b_lp); dk_pt(b_kt):=dkB_pt(b_lp); dk_tientt(b_kt):=dkB_tientt(b_lp);
        dk_lkeM(b_kt):=dkB_lkeM(b_lp); dk_lkeP(b_kt):=dkB_lkeP(b_lp); dk_lkeB(b_kt):=dkB_lkeB(b_lp); dk_luy(b_kt):=dkB_luy(b_lp);
    end loop;
end if;
for b_lp in 1..dk_ma.count loop
    b_loi:='loi:Sai chi tiet dong '||to_char(b_lp)||':loi';
    dk_ma(b_lp):=nvl(trim(dk_ma(b_lp)),' ');
    if dk_ma(b_lp)=' ' then return; end if;
    if trim(dk_ten(b_lp)) is null then
        b_i1:=b_lp-1; b_i2:=0;
        for b_lp1 in 1..b_i1 loop
            if dk_ma(b_lp1)=dk_ma(b_lp) then b_i2:=1; exit; end if;
        end loop;
        if b_i2=0 then return; end if;
    end if;
    dk_tc(b_lp):=nvl(trim(dk_tc(b_lp)),'C');
    dk_ma_ct(b_lp):=nvl(trim(dk_ma_ct(b_lp)),' ');
    if dk_ma(b_lp)=dk_ma_ct(b_lp) then b_loi:='loi:Trung ma cap tren ma: '||dk_ma(b_lp)||':loi'; raise PROGRAM_ERROR; end if;
    if dk_tc(b_lp)='C' then
        dk_lkeP(b_lp):=nvl(trim(dk_lkeP(b_lp)),'G'); dk_lkeB(b_lp):=nvl(trim(dk_lkeB(b_lp)),'G');
    else
        dk_lkeP(b_lp):=nvl(trim(dk_lkeP(b_lp)),'T'); dk_lkeB(b_lp):=nvl(trim(dk_lkeB(b_lp)),'T');
    end if;
    dk_luy(b_lp):=nvl(trim(dk_luy(b_lp)),'C'); dk_kieu(b_lp):=nvl(trim(dk_kieu(b_lp)),'T');
    dk_ma_dk(b_lp):=nvl(trim(dk_ma_dk(b_lp)),' '); dk_lh_nv(b_lp):=' '; dk_t_suat(b_lp):=0;
    if dk_ma_dk(b_lp)<>' ' then
        if dk_lh_bh(b_lp)='C' then
            if FBH_MA_DK_HAN(dk_ma_dk(b_lp))='K' then
                b_loi:='loi:Dieu khoan: '||dk_ma_dk(b_lp)||' da het su dung:loi'; raise PROGRAM_ERROR;
            end if;
            select nvl(min(lh_nv),' ') into dk_lh_nv(b_lp) from bh_ma_dk where ma=dk_ma_dk(b_lp);
        else
            if FBH_MA_DKBS_HAN(dk_ma_dk(b_lp))='K' then
                b_loi:='loi:Dieu khoan bo sung: '||dk_ma_dk(b_lp)||' da het su dung:loi'; raise PROGRAM_ERROR;
            end if;
            select nvl(min(lh_nv),' ') into dk_lh_nv(b_lp) from bh_ma_dkbs where ma=dk_ma_dk(b_lp);
        end if;
        if dk_lh_nv(b_lp)<>' ' then dk_t_suat(b_lp):=FBH_MA_LHNV_THUE(dk_lh_nv(b_lp)); end if;
    end if;
end loop;
for b_lp in 1..dk_ma.count loop
    b_i1:=b_lp+1;
    for b_lp1 in b_i1..dk_ma.count loop
        if dk_ma(b_lp)=dk_ma(b_lp1) and dk_tien(b_lp)=dk_tien(b_lp1) then b_loi:='loi:Trung ma: '||dk_ma(b_lp)||':loi'; raise PROGRAM_ERROR;
        end if;
    end loop;
    if dk_tc(b_lp)='T' then
        b_i1:=0;
        for b_lp1 in 1..dk_ma.count loop
            if dk_ma(b_lp)=dk_ma_ct(b_lp1) and dk_tc(b_lp1)<>'K' then b_i1:=1; exit; end if;
        end loop;
        if b_i1=0 then b_loi:='loi:Nhap ma chi tiet ma: '||dk_ma(b_lp)||':loi'; raise PROGRAM_ERROR; end if;
    end if;
end loop;
for b_lp in 1..dk_ma.count loop
    if dk_tc(b_lp)='C' and dk_ma_ct(b_lp)!=' ' then
        b_i1:=0;
        for b_lp1 in 1..dk_ma.count loop
            if dk_ma(b_lp1)=dk_ma_ct(b_lp) and dk_tc(b_lp1)='T' then b_i1:=1; exit; end if;
        end loop;
        if b_i1=0 then b_loi:='loi:Nhap ma cap tren ma: '||dk_ma(b_lp)||':loi'; raise PROGRAM_ERROR; end if;
    end if;
end loop;
for b_lp in 1..dk_ma.count loop
    if dk_tc(b_lp)='C' and dk_ma_ct(b_lp)!=' ' then
        b_lenh:=dk_ma(b_lp); b_ma_ct:=dk_ma_ct(b_lp);
        while b_ma_ct<>' ' loop
            b_i1:=0;
            for b_lp1 in 1..dk_ma.count loop
                if dk_ma(b_lp1)=b_ma_ct then b_i1:=b_lp1; exit; end if;
            end loop;
            if b_i1=0 then
                b_ma_ct:='';
            else
                b_ma_ct:=dk_ma_ct(b_i1);
                if PKH_MA_MA(b_lenh,b_ma_ct) then b_loi:='loi:Vong lap ma cap tren ma: '||dk_ma(b_lp)||':loi'; raise PROGRAM_ERROR; end if;
                b_lenh:=b_lenh||','||dk_ma(b_i1);
            end if;
        end loop;
    end if;
end loop;
for b_lp in 1..dk_ma.count loop
    if dk_lkeM(b_lp)<>'C' then
        dk_ma_dkC(b_lp):=' ';
    elsif dk_lh_bh(b_lp)='C' then
        dk_ma_dkC(b_lp):=dk_ma_ct(b_lp);
    else
        dk_ma_dkC(b_lp):=FBH_MA_DKBS_MA_DK(dk_ma_dk(b_lp));
    end if;
end loop;
if trim(b_dt_lt) is not null then
    b_lenh:=FKH_JS_LENH('ma_dk,ma_lt,ten');
    EXECUTE IMMEDIATE b_lenh bulk collect into lt_ma_dk,lt_ma_lt,lt_ten using b_dt_lt;
    for b_lp in 1..lt_ma_lt.count loop
        if trim(lt_ma_lt(b_lp)) is null then
            b_loi:='loi:Nhap ma loai tru dong '||to_char(b_lp)||':loi'; raise PROGRAM_ERROR;
        end if;
        if trim(lt_ma_dk(b_lp)) is null then lt_ma_dk(b_lp):=' '; end if;
        if FBH_MA_DKLT_HAN(lt_ma_dk(b_lp),lt_ma_lt(b_lp))='K' then
            b_loi:='loi:Dieu khoan loai tru: '||lt_ma_lt(b_lp)||' da het su dung:loi'; raise PROGRAM_ERROR;
        end if;
    end loop;
end if;
b_so_id:=FBH_PTNHANG_BPHI_SO_IDh(b_ma_sp,b_cdich,b_ngay_bd);
if b_so_id<>0 then
    delete bh_ptnHang_phi_txt where so_id=b_so_id;
    delete bh_ptnHang_phi_lt where so_id=b_so_id;
    delete bh_ptnHang_phi_dk where so_id=b_so_id;
    delete bh_ptnHang_phi where so_id=b_so_id;
end if;
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;
b_loi:='loi:Loi Table bh_ptnHang_phi:loi';
insert into bh_ptnHang_phi values(b_ma_dvi,b_so_id,b_ma_sp,b_lvuc,b_cdich,b_tl_phi,b_ngay_bd,b_ngay_kt,b_nsd);
insert into bh_ptnHang_phi_txt values(b_ma_dvi,b_so_id,'dt_ct','"'||b_dt_ct||'"');
insert into bh_ptnHang_phi_txt values(b_ma_dvi,b_so_id,'dt_dk','"'||b_dt_dk||'"');
if trim(b_dt_dkbs) is not null then
    insert into bh_ptnHang_phi_txt values(b_ma_dvi,b_so_id,'dt_dkbs','"'||b_dt_dkbs||'"');
end if;
for b_lp in 1..dk_ma.count loop
   insert into bh_ptnHang_phi_dk values(b_ma_dvi,b_so_id,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_tientt(b_lp),dk_lkeM(b_lp),dk_lkeP(b_lp),dk_lkeB(b_lp),dk_luy(b_lp),
        dk_ma_dk(b_lp),dk_ma_dkC(b_lp),dk_lh_nv(b_lp),dk_t_suat(b_lp),0,dk_lh_bh(b_lp));
end loop;
for r_lp in(select ma,ma_ct,so_id,bt,level from  (select t.so_id,t.bt,t.ma,t.ma_ct from bh_ptnHang_phi_dk t where t.so_id = b_so_id) t start with t.ma_ct=' '  CONNECT BY prior t.ma=t.ma_ct) loop
    update bh_ptnHang_phi_dk set cap=r_lp.level where so_id=r_lp.so_id and bt=r_lp.bt;
end loop;
if lt_ma_lt.count<>0 then
    for b_lp in 1..lt_ma_lt.count loop
        insert into bh_ptnHang_phi_lt values(b_ma_dvi,b_so_id,b_lp,lt_ma_lt(b_lp),lt_ten(b_lp));
    end loop;
    insert into bh_ptnHang_phi_txt values(b_ma_dvi,b_so_id,'dt_lt','"'||b_dt_lt||'"');
end if;
if trim(b_dt_khd) is not null then
    insert into bh_ptnHang_phi_txt values(b_ma_dvi,b_so_id,'dt_khd','"'||b_dt_khd||'"');
end if;
if trim(b_dt_kbt) is not null then
    insert into bh_ptnHang_phi_txt values(b_ma_dvi,b_so_id,'dt_kbt','"'||b_dt_kbt||'"');
end if;
commit;
select json_object('so_id' value b_so_id) 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;