AAA

create or replace procedure PBH_PTN_HD_DU(
    b_ma_dvi varchar2,b_so_id number,b_ma_dvi_ks varchar2,b_nsd_ks varchar2,
    b_ttrang varchar2,b_so_hd out varchar2,b_loi out varchar2)
AS
    b_i1 number; b_nv varchar2(10); b_txt clob;
begin
-- Nam - Update sau duyet
b_loi:='loi:Loi xu ly PBH_PTN_DU:loi';
select so_hd,nv into b_so_hd,b_nv from bh_ptn where ma_dvi=b_ma_dvi and so_id=b_so_id;
b_i1:=instr(b_so_hd,'.');
if b_ttrang='D' then
    if b_i1=2 then b_so_hd:=substr(b_so_hd,3); end if;
else
    if b_i1<>2 then b_so_hd:=b_ttrang||'.'||b_so_hd; end if;
end if;
b_nv:=substr(b_nv,3,4);
if b_nv='NN' then
    select txt into b_txt from bh_ptnnn_txt where ma_dvi=b_ma_dvi and so_id=b_so_id and loai='dt_ct';
    PKH_JS_THAYa(b_txt,'so_hd,ksoat,dvi_ksoat,ttrang',b_so_hd||','||b_nsd_ks||','||b_ma_dvi_ks||','||b_ttrang);
    update bh_ptnnn_txt set txt='"'||b_txt||'"' where ma_dvi=b_ma_dvi and so_id=b_so_id and loai='dt_ct';
    update bh_ptnnn set so_hd=b_so_hd,ttrang=b_ttrang,dvi_ksoat=b_ma_dvi_ks,ksoat=b_nsd_ks where ma_dvi=b_ma_dvi and so_id=b_so_id;
elsif b_nv='CC' then
    select txt into b_txt from bh_ptncc_txt where ma_dvi=b_ma_dvi and so_id=b_so_id and loai='dt_ct';
    PKH_JS_THAYa(b_txt,'so_hd,ksoat,dvi_ksoat,ttrang',b_so_hd||','||b_nsd_ks||','||b_ma_dvi_ks||','||b_ttrang);
    update bh_ptncc_txt set txt='"'||b_txt||'"' where ma_dvi=b_ma_dvi and so_id=b_so_id and loai='dt_ct';
    update bh_ptncc set so_hd=b_so_hd,ttrang=b_ttrang,dvi_ksoat=b_ma_dvi_ks,ksoat=b_nsd_ks where ma_dvi=b_ma_dvi and so_id=b_so_id;
else
    select txt into b_txt from bh_ptnHang_txt where ma_dvi=b_ma_dvi and so_id=b_so_id and loai='dt_ct';
    PKH_JS_THAYa(b_txt,'so_hd,ksoat,dvi_ksoat,ttrang',b_so_hd||','||b_nsd_ks||','||b_ma_dvi_ks||','||b_ttrang);
    update bh_ptnHang_txt set txt='"'||b_txt||'"' where ma_dvi=b_ma_dvi and so_id=b_so_id and loai='dt_ct';
    update bh_ptnHang set so_hd=b_so_hd,ttrang=b_ttrang,dvi_ksoat=b_ma_dvi_ks,ksoat=b_nsd_ks where ma_dvi=b_ma_dvi and so_id=b_so_id;
end if;
update bh_ng set so_hd=b_so_hd,ttrang=b_ttrang,dvi_ksoat=b_ma_dvi_ks,ksoat=b_nsd_ks where ma_dvi=b_ma_dvi and so_id=b_so_id;
b_loi:='';
exception when others then if b_loi is null then raise PROGRAM_ERROR; else null; end if;
end;

/

create or replace function FBH_BAO_BGHD_PTN(b_ma_dvi varchar2,b_so_id number) return varchar2
AS
    b_kq varchar2(1):=' '; b_i1 number;
begin
-- Nam - Tra da chuyen HD
select count(*) into b_i1 from bh_ptncc where ma_dvi=b_ma_dvi and so_id=b_so_id;
if b_i1<>0 then
  b_kq:='CC';
else
  select count(*) into b_i1 from bh_ptnnn where ma_dvi=b_ma_dvi and so_id=b_so_id;
  if b_i1<>0 then
    b_kq:='NN';
  else
    select count(*) into b_i1 from bh_ptnHang where ma_dvi=b_ma_dvi and so_id=b_so_id;
    if b_i1<>0 then b_kq:='HH'; end if;
  end if;
end if;
return b_kq;
end;

/

create or replace function FBH_BAO_BGHD_PTN(b_ma_dvi varchar2,b_so_id number) return varchar2
AS
    b_kq varchar2(1):=' '; b_i1 number;
begin
-- Nam - Tra da chuyen HD
select count(*) into b_i1 from bh_ptncc where ma_dvi=b_ma_dvi and so_id=b_so_id;
if b_i1<>0 then
  b_kq:='CC';
else
  select count(*) into b_i1 from bh_ptnnn where ma_dvi=b_ma_dvi and so_id=b_so_id;
  if b_i1<>0 then
    b_kq:='NN';
  else
    select count(*) into b_i1 from bh_ptnHang where ma_dvi=b_ma_dvi and so_id=b_so_id;
    if b_i1<>0 then b_kq:='HH'; end if;
  end if;
end if;
return b_kq;
end;
/
create or replace procedure PBH_BAO_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
-- Dan - Liet ke nghiep vu theo hop dong
b_loi:='loi:Loi xu ly PBH_BAO_DK_PTN:loi';
delete bh_hd_nv_temp;
b_ch:=FBH_BAO_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_ptnB 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_ptnB_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 PTBH_BAO_NV_PTNd(b_ma_dvi varchar2,b_so_id number,b_loi out varchar2)
AS
    b_i1 number; b_ch varchar(10);
    b_ngay_ht number; b_ngay_hl number; b_ngay_kt number;
    b_nt_tien varchar2(5); b_nt_phi varchar2(5);
begin
-- Dan - Ghep nghiep vu bao => nghiep vu tai cho 1 doi tuong
b_loi:='loi:Loi xu ly PTBH_BAO_NV_PTNd:loi';
delete tbh_ghep_nv_temp1; delete tbh_ghep_nv_temp3;
b_ch:=FBH_BAO_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_ptnB where ma_dvi=b_ma_dvi and so_id=b_so_id;
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,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_BAO_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 PTBH_BAO_NV_PTN(
    b_so_idT number,b_ma_dvi varchar2,b_so_id number,b_loi out varchar2)
AS
    b_i1 number; b_ch varchar(10);
    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);
    a_ma_taT pht_type.a_var; a_ptT pht_type.a_num;
begin
-- Dan - Ghep nghiep vu ptn
b_loi:='loi:Loi xu ly PTBH_BAO_NV_PTN:loi';
delete tbh_ghep_nv_temp; delete tbh_ghep_nv_temp2;
b_ch:=FBH_BAO_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_ptnB where ma_dvi=b_ma_dvi and so_id=b_so_id;
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,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;
if b_nt_tien<>'VND' then b_tp:=2; end if;
PTBH_BAO_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_BAO_NV_PTNc(
    b_ma_dvi varchar2,b_so_id number,b_loi out varchar2)
AS
    b_i1 number; b_ch varchar(10);
    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_kieu varchar2(1);
begin
-- Dan - Ghep nghiep vu ptn
b_loi:='loi:Loi xu ly PTBH_BAO_NV_PTN:loi';
delete tbh_ghep_nv_temp; delete tbh_ghep_nv_temp2;
b_ch:=FBH_BAO_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_ptnB where ma_dvi=b_ma_dvi and so_id=b_so_id;
    PTBH_BAO_NV_PTNd(b_ma_dvi,b_so_id,b_loi);
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,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;
        PTBH_GHEP_NV_1(b_ma_dvi,b_ngay_ht,b_ngay_hl,0,'',b_nt_tien,b_nt_phi,b_ma_dvi,b_so_id,0,b_loi,'C');
    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;
        PTBH_GHEP_NV_1(b_ma_dvi,b_ngay_ht,b_ngay_hl,0,'',b_nt_tien,b_nt_phi,b_ma_dvi,b_so_id,0,b_loi,'C');
    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;
        PTBH_GHEP_NV_1(b_ma_dvi,b_ngay_ht,b_ngay_hl,0,'',b_nt_tien,b_nt_phi,b_ma_dvi,b_so_id,0,b_loi,'C');
    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;
        PTBH_GHEP_NV_1(b_ma_dvi,b_ngay_ht,b_ngay_hl,0,'',b_nt_tien,b_nt_phi,b_ma_dvi,b_so_id,0,b_loi,'C');
    end if;
end if;
if b_loi is not null then return; end if;
if b_nt_tien<>'VND' then b_tp:=2; 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;
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 FBH_HANG_PHI(
    dt_ct clob,dt_dk clob,dt_ds clob,
    b_so_idP out number,
    a_ma out pht_type.a_var,a_ma_hang out pht_type.a_var,a_ten out pht_type.a_nvar,a_tc out pht_type.a_var,
    a_ma_ct out pht_type.a_var,a_kieu out pht_type.a_var,
    a_lkeP out pht_type.a_var,a_lkeB out pht_type.a_var,a_luy out pht_type.a_var,
    a_ma_dk out pht_type.a_var,a_ma_dkC out pht_type.a_var,a_lh_nv out pht_type.a_var,a_t_suat out pht_type.a_num,
    a_cap out pht_type.a_num,a_lh_bh out pht_type.a_var,
    a_tien out pht_type.a_num,a_pt out pht_type.a_num,a_phi out pht_type.a_num,
    a_thue out pht_type.a_num,a_ttoan out pht_type.a_num,
    a_ptB out pht_type.a_num,a_ptG out pht_type.a_num,a_phiG out pht_type.a_num,
    b_loi out varchar2)
AS
    b_lenh varchar2(1000); b_kt number:=0; b_ktL number:=0; b_ngay number:=PKH_NG_CSO(sysdate);
    b_ma_qtac varchar2(500); b_ma_nhang varchar2(500); b_ma_vchuyen varchar2(500); b_kieu_hd varchar2(1);
    b_khoang_cach number; b_thoi_gian number; b_c_thue varchar2(1); b_tgP number:=1; b_tgT number:=1;
    b_tp number:=0; b_nt_tien varchar2(5); b_nt_phi varchar2(5); b_dvi_ta varchar2(10):=FTBH_DVI_TA(); dt_txt clob:= ' ';
    ds_ma pht_type.a_var; ds_ma_hang pht_type.a_var; ds_ten pht_type.a_nvar; ds_dgoi pht_type.a_var; ds_cphi pht_type.a_num; 
    ds_mtn pht_type.a_num; ds_pt pht_type.a_num; ds_ptB pht_type.a_num; ds_t_suat pht_type.a_num; ds_ma_dk pht_type.a_var; ds_lh_nv pht_type.a_var;
    
    dk_ma pht_type.a_var; dk_ten pht_type.a_nvar; dk_pt pht_type.a_num; dk_tc pht_type.a_var; 
    dk_ma_ct pht_type.a_var; dk_kieu 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_lh_nv pht_type.a_var; dk_t_suat pht_type.a_num; dk_cap pht_type.a_num; dk_lh_bh pht_type.a_var;
begin
-- Dan - Tinh phi
b_loi:='loi:Loi xu ly FBH_HANG_PHI:loi';
b_lenh:=FKH_JS_LENH('ma_qtac,ma_nhang,vchuyen,nt_tien,nt_phi,c_thue,kieu_hd');
EXECUTE IMMEDIATE b_lenh into b_ma_qtac,b_ma_nhang,b_ma_vchuyen,b_nt_tien,b_nt_phi,b_c_thue,b_kieu_hd using dt_ct;
b_ma_qtac:= PKH_MA_TENl(b_ma_qtac); b_ma_nhang:= PKH_MA_TENl(b_ma_nhang);
b_ma_vchuyen:= PKH_MA_TENl(b_ma_vchuyen);
b_khoang_cach:=0; b_thoi_gian:=0;
b_so_idP:=FBH_HANG_BPHI_SO_IDd(b_ma_vchuyen,b_ma_nhang,b_ma_qtac,b_khoang_cach,b_thoi_gian);
if b_so_idP=0 then b_loi:='loi:Khong tim duoc bieu phi:loi'; return; end if;
if b_nt_tien<>'VND' then b_tp:=2; end if;
if b_nt_tien<>'VND' and b_nt_tien<>b_nt_phi then b_tgT:=FTT_TRA_TGTT(b_dvi_ta,b_ngay,b_nt_tien); end if;
if b_nt_phi<>'VND' then b_tgP:=FTT_TRA_TGTT(b_dvi_ta,b_ngay,b_nt_phi); end if;
b_lenh:=FKH_JS_LENH('ma,ten,tc,ma_ct,pt,kieu,lkeP,lkeB,luy,ma_dk,lh_nv,t_suat,cap,lh_bh');
EXECUTE IMMEDIATE b_lenh bulk collect into dk_ma,dk_ten,dk_tc,dk_ma_ct,dk_pt,dk_kieu,dk_lkeP,dk_lkeB,dk_luy,
        dk_ma_dk,dk_lh_nv,dk_t_suat,dk_cap,dk_lh_bh using dt_dk;
b_lenh:=FKH_JS_LENH('ma,ma_hang,ten,dgoi,cphi,mtn,pt,ptb,t_suat,ma_dk,lh_nv');
EXECUTE IMMEDIATE b_lenh bulk collect into ds_ma,ds_ma_hang,ds_ten,ds_dgoi,ds_cphi,ds_mtn,ds_pt,ds_ptB,ds_t_suat,ds_ma_dk,ds_lh_nv using dt_ds;
if ds_ma_hang.count=0 and b_kieu_hd <> 'U' then b_loi:='loi:Nhap danh sach hang hoa bao hiem:loi'; return; end if;
b_kt:=0;
for b_lp_ds in 1..ds_ma_hang.count loop
    b_kt:=b_kt+1; b_ktL:=b_kt;
    a_ma(b_kt):=ds_ma(b_lp_ds); a_ma_hang(b_kt):=ds_ma_hang(b_lp_ds); a_ten(b_kt):=ds_ten(b_lp_ds); a_tien(b_kt):= round(ds_mtn(b_lp_ds) * b_tgT,0);
    a_pt(b_kt):=ds_pt(b_lp_ds); a_phi(b_kt):=round(a_tien(b_kt)*a_pt(b_kt)/100,b_tp); a_ptB(b_kt):=ds_ptB(b_lp_ds);
    a_ptG(b_kt):=0; a_phiG(b_kt):=0; a_ma_dk(b_kt):=ds_ma_dk(b_lp_ds); a_ma_dkC(b_kt):=' '; a_lh_bh(b_kt):='C';
    a_t_suat(b_kt):=ds_t_suat(b_lp_ds); a_cap(b_kt):=1; a_ma_ct(b_kt):=' '; a_lkeP(b_kt):=' '; a_lkeB(b_kt):=' ';
    a_kieu(b_kt):='T'; a_luy(b_kt):='K'; a_lh_nv(b_kt):=ds_lh_nv(b_lp_ds); 
    FBH_HANG_BPHI_DSp(b_so_idP,ds_ma_hang(b_lp_ds),ds_dgoi(b_lp_ds),dt_txt,b_loi);
    if b_loi is not null then raise PROGRAM_ERROR; end if;
        for b_lp_dk in 1..dk_ma.count loop
          b_kt:=b_kt+1;
          a_ma(b_kt):=dk_ma(b_lp_dk);a_ma_hang(b_kt):=a_ma_hang(b_ktL)||'>'||dk_ma(b_lp_dk); a_kieu(b_kt):=dk_kieu(b_lp_dk);
          a_ten(b_kt):='- '||dk_ten(b_lp_dk); a_tc(b_kt):='C'; a_ma_ct(b_kt):=a_ma(b_lp_ds);
          a_lkeP(b_kt):='G'; a_lkeB(b_kt):='G'; a_luy(b_kt):=dk_luy(b_lp_dk);
          a_tien(b_kt):=a_tien(b_ktL); a_ma_dk(b_kt):=dk_ma_dk(b_lp_dk); a_ma_dkC(b_kt):=dk_ma_dk(b_lp_dk); a_lh_nv(b_kt):=ds_lh_nv(b_lp_ds);
          a_t_suat(b_kt):=a_t_suat(b_lp_ds); a_cap(b_kt):=dk_cap(b_lp_dk)+1; a_lh_bh(b_kt):=dk_lh_bh(b_lp_dk);
          a_pt(b_kt):=dk_pt(b_lp_dk); a_ptB(b_kt):=dk_pt(b_lp_dk);
          a_phi(b_kt):= round(a_tien(b_kt)*a_pt(b_kt)/100,b_tp);
          a_ptG(b_kt):=a_ptB(b_kt)-a_pt(b_kt);
          if a_ptG(b_kt)=0 then a_phiG(b_kt):=0; else a_phiG(b_kt):=round(a_tien(b_ktL)*a_ptG(b_kt)/100,b_tp); end if;
        end loop;
        a_tc(b_ktL):='T'; a_lkeP(b_ktL):='T'; a_lkeB(b_ktL):='T';
end loop;
if b_c_thue<>'C' then
    for b_lp in 1..a_ma.count loop a_thue(b_lp):=0; end loop;
else
    for b_lp in 1..a_ma.count loop a_thue(b_lp):=round(a_phi(b_lp)*a_t_suat(b_lp)/100,b_tp); end loop;
end if;
FBH_HANG_PHIb(b_tp,a_ma,a_ma_ct,a_lkeP,a_cap,a_phi,a_thue,a_ttoan,b_loi);
if b_loi is not null then raise PROGRAM_ERROR; end if;
exception when others then if b_loi is null then raise PROGRAM_ERROR; else null; end if;
end;