AAA

create or replace procedure PBH_DTAC_MA_NHnf(
    b_oraIn clob,b_ma in out varchar2,b_ps out varchar2,b_loi out varchar2,b_ma_dviN varchar2:='',b_nsdN varchar2:='')
AS
    b_lenh varchar2(1000); b_i1 number; b_tenH varchar2(500); b_tenL varchar2(100):=' ';
    b_ma_dvi varchar2(10); b_nsd varchar2(20);
    b_maT varchar2(20); b_vp varchar(1);
    r_hd bh_dtac_ma%rowtype; b_txt clob:='';

    a_ma pht_type.a_var;a_loai pht_type.a_var;a_ten pht_type.a_nvar;a_cmt pht_type.a_var;
    a_dchi pht_type.a_var;a_mobi pht_type.a_var;a_email pht_type.a_var;a_gioi pht_type.a_num;
    a_ng_sinh pht_type.a_num;a_c_thue pht_type.a_var;a_ma_ct pht_type.a_var;a_ngay_kt pht_type.a_num;
    a_nghe pht_type.a_var;a_nhang pht_type.a_var;a_ma_tk pht_type.a_var;a_kvuc pht_type.a_var;
    a_pas pht_type.a_var;
begin
-- Dan - Nhap tu khai bao ma
b_loi:='loi:Loi xu ly PBH_DTAC_MA_NHn:loi';
--FBH_DTAC_MA_NHnf(
    --b_oraIn,a_ma,a_loai,a_ten,a_cmt,a_dchi,a_mobi,a_email,a_gioi,a_ng_sinh,
    --a_c_thue,a_ma_ct,a_ngay_kt,a_nghe,a_nhang,a_ma_tk,a_kvuc,a_pas,b_loi);
for b_lp in 1..a_ma.count loop
  a_ma(b_lp):=nvl(trim(a_ma(b_lp)),' '); a_loai(b_lp):=nvl(trim(a_loai(b_lp)),' ');
  a_ten(b_lp):=nvl(trim(a_ten(b_lp)),' '); a_cmt(b_lp):=nvl(trim(a_cmt(b_lp)),' '); a_dchi(b_lp):=nvl(trim(a_dchi(b_lp)),' ');
  a_mobi(b_lp):=nvl(trim(a_mobi(b_lp)),' '); a_email(b_lp):=LOWER(nvl(trim(a_email(b_lp)),' '));
  a_gioi(b_lp):=nvl(trim(a_gioi(b_lp)),' '); a_ng_sinh(b_lp):=nvl(a_ng_sinh(b_lp),0); a_c_thue(b_lp):=nvl(trim(a_c_thue(b_lp)),'C');
  --duchq
  a_ma_ct(b_lp):=nvl(trim(a_ma_ct(b_lp)),' '); a_ngay_kt(b_lp):=nvl(a_ngay_kt(b_lp),0);
  a_nghe(b_lp):=PKH_MA_TENl(a_nghe(b_lp));
  a_nghe(b_lp):=nvl(trim(a_nghe(b_lp)),' '); a_nhang(b_lp):=nvl(trim(a_nhang(b_lp)),' '); a_ma_tk(b_lp):=nvl(trim(a_ma_tk(b_lp)),' ');
  a_kvuc(b_lp):=nvl(trim(a_kvuc(b_lp)),' '); a_pas(b_lp):=nvl(trim(a_pas(b_lp)),' ');
  if a_c_thue(b_lp) not in('C','K') then a_c_thue(b_lp):='C'; end if;
  if a_ngay_kt(b_lp)=0 then a_ngay_kt(b_lp):=30000101; end if;
  if a_loai(b_lp) not in('C','T') then b_loi:='loi:Sai loai:loi'; return; end if;
  if a_ten(b_lp)=' ' then b_loi:='loi:Nhap ten:loi'; return; end if;
  -- to chuc moi kiem tra
  if a_loai(b_lp)='T' and a_cmt(b_lp)=' ' then
    b_loi:='loi:Nhap ma so thue cho doanh nghiep, so giay phep thanh lap cho HCSN:loi'; return;
  end if;
  if a_ma_ct(b_lp)<>' ' then
    select count(*) into b_i1 from bh_dtac_ma where ma=a_ma_ct(b_lp);
    if b_i1=0 then b_loi:='loi:Sai ma cap tren:loi'; return; end if;
  end if;
  if b_loi is not null then return; end if;
  if FBH_DTAC_MA_DK(a_loai(b_lp),a_ten(b_lp),a_cmt(b_lp),a_mobi(b_lp),a_email(b_lp),a_gioi(b_lp),a_ng_sinh(b_lp))<>'C' then
     b_loi:='loi:Khong du thong tin tao ma khach hang, doi tac:loi'; return;
     end if;
  if trim(a_ma(b_lp)) is null then
     FBH_DTAC_MA_XIN(a_loai(b_lp),a_ten(b_lp),a_cmt(b_lp),a_mobi(b_lp),a_email(b_lp),a_gioi(b_lp),a_ng_sinh(b_lp),a_ma(b_lp),b_ps,b_loi);
  if b_loi is not null then return; end if;
     b_ps:='K'; b_ma_dvi:=nvl(trim(b_ma_dviN),' '); b_nsd:=nvl(trim(b_nsdN),' ');
  else
     b_maT:=FBH_DTAC_MAf(a_loai(b_lp),a_ten(b_lp),a_cmt(b_lp),a_mobi(b_lp),a_email(b_lp),a_gioi(b_lp),a_ng_sinh(b_lp));
     if b_maT is not null and b_maT<>a_ma(b_lp) then
        b_loi:='loi:Thong tin trung ma '||b_maT||':loi'; return;
     end if;
         b_ps:='C';
         select ma_dvi,nsd into b_ma_dvi,b_nsd from bh_dtac_ma where ma=a_ma(b_lp);
  end if;
  select vp into b_vp from ht_ma_dvi where ma=b_ma_dviN;
  if b_vp <> 'C' and FBH_DTAC_MA_DVI(b_nsdN) not in(' ',b_ma_dviN) then
     b_loi:='loi:Khong vuot quyen khai thac don vi:loi';
  end if;
  if b_loi is not null then raise PROGRAM_ERROR; end if;
  b_tenH:=FKH_BO_UNICODE(a_ten(b_lp),'C','C');
  if a_loai(b_lp)='C' and a_ng_sinh(b_lp)<>0 and a_gioi(b_lp)<>' ' then b_tenL:=b_tenH||a_ng_sinh(b_lp)||a_gioi(b_lp); end if;
  if a_loai(b_lp)='C' then
    if b_ps='M' then
      if a_mobi(b_lp)<>' ' then
        insert into bh_dtac_ma_mobi values(a_ma(b_lp),a_mobi(b_lp),b_tenH);
      end if;
      if a_email(b_lp)<>' ' then
        insert into bh_dtac_ma_email values(a_ma(b_lp),a_email(b_lp),b_tenH);
      end if;
      if b_tenL<>' ' then
        insert into bh_dtac_ma_ten values(a_ma(b_lp),b_tenL);
      end if;
    else
      if a_mobi(b_lp)<>' ' then
        select count(*) into b_i1 from bh_dtac_ma_mobi where ma=a_ma(b_lp) and mobi=a_mobi(b_lp);
        if b_i1=0 then
          insert into bh_dtac_ma_mobi values(a_ma(b_lp),a_mobi(b_lp),b_tenH);
        end if;
      end if;
      if a_email(b_lp)<>' ' then
        select count(*) into b_i1 from bh_dtac_ma_email where ma=a_ma(b_lp) and email=a_email(b_lp);
        if b_i1=0 then
          insert into bh_dtac_ma_email values(a_ma(b_lp),a_email(b_lp),b_tenH);
        end if;
      end if;
      if b_tenL<>' ' then
        select count(*) into b_i1 from bh_dtac_ma_ten where ma=a_ma(b_lp) and ten=b_tenL;
        if b_i1=0 then
          insert into bh_dtac_ma_ten values(a_ma(b_lp),b_tenL);
        end if;
      end if;
     end if;
    end if;
    if a_cmt(b_lp)<>' ' then
      if b_ps='M' then
        b_i1:=0;
      else
        select count(*) into b_i1 from bh_dtac_ma_cmt where ma=a_ma(b_lp) and cmt=a_cmt(b_lp);
      end if;
      if b_i1=0 then
        insert into bh_dtac_ma_cmt values(a_ma(b_lp),a_cmt(b_lp),b_tenH);
      end if;
    end if;
    if a_pas(b_lp)<>' ' then
      update bh_dtac_ma_pas set pas=a_pas(b_lp) where ma=a_ma(b_lp);
      insert into bh_dtac_ma_pas values(a_ma(b_lp),a_pas(b_lp));
    end if;
    if b_ps='C' then
      insert into bh_dtac_maL select a.*,b_oraIn,sysdate from bh_dtac_ma a;
      -- truong hop khong co ma trong bh_dtac_ma loi
      select count(*) into b_i1 from bh_dtac_ma where ma=a_ma(b_lp);
      if b_i1=1 then
        select *  into r_hd from bh_dtac_ma where ma=a_ma(b_lp);
        if a_ng_sinh(b_lp)=0 then a_ng_sinh(b_lp):=r_hd.ng_sinh; end if;
        if a_gioi(b_lp)=' ' then a_gioi(b_lp):=r_hd.gioi; end if;
        if a_cmt(b_lp)=' ' then a_cmt(b_lp):=r_hd.cmt; end if;
        if a_dchi(b_lp)=' ' then a_dchi(b_lp):=r_hd.dchi; end if;
        if a_mobi(b_lp)=' ' then a_mobi(b_lp):=r_hd.mobi; end if;
        if a_email(b_lp)=' ' then a_email(b_lp):=r_hd.email; end if;
        if a_nghe(b_lp)=' ' then a_nghe(b_lp):=r_hd.nghe; end if;
        if a_c_thue(b_lp)=' ' then a_c_thue(b_lp):=r_hd.c_thue; end if;
        if a_nhang(b_lp)=' ' then a_nhang(b_lp):=r_hd.nhang; end if;
        if a_ma_tk(b_lp)=' ' then a_ma_tk(b_lp):=r_hd.ma_tk; end if;
        if a_kvuc(b_lp)=' ' then a_kvuc(b_lp):=r_hd.kvuc; end if;
        if a_ma_ct(b_lp)=' ' then a_ma_ct(b_lp):=r_hd.ma_ct; end if;
        if a_ngay_kt(b_lp)=0 then a_ngay_kt(b_lp):=r_hd.ngay_kt; end if;
        select count(*) into b_i1 from bh_dtac_ma_txt where ma=a_ma(b_lp);
        if b_i1=1 then
          select txt into b_txt from bh_dtac_ma_txt where ma=a_ma(b_lp);
        end if;
      end if;
    else
      if a_ngay_kt(b_lp)=0 then a_ngay_kt(b_lp):=30000101; end if;
    end if;
    b_loi:='loi:Loi Table BH_HD_MA_KH:loi';
    delete bh_dtac_ma_txt where ma=a_ma(b_lp);
    delete bh_dtac_ma where ma=a_ma(b_lp);
    insert into bh_dtac_ma values(b_ma_dvi,a_ma(b_lp),a_ten(b_lp),a_ng_sinh(b_lp),a_gioi(b_lp),a_cmt(b_lp),a_dchi(b_lp),a_mobi(b_lp),a_email(b_lp),
        a_loai(b_lp),a_nghe(b_lp),a_c_thue(b_lp),a_nhang(b_lp),a_ma_tk(b_lp),a_kvuc(b_lp),a_ma_ct(b_lp),a_ngay_kt(b_lp),b_nsd);
    insert into bh_dtac_ma_txt values(a_ma(b_lp),b_oraIn);
    delete bh_hd_ma_kh where ma=a_ma(b_lp);
    insert into bh_hd_ma_kh values(b_ma_dvi,a_ma(b_lp),a_ten(b_lp),a_ng_sinh(b_lp),a_gioi(b_lp),a_cmt(b_lp),a_dchi(b_lp),a_mobi(b_lp),a_email(b_lp),
        a_loai(b_lp),a_nghe(b_lp),a_c_thue(b_lp),a_nhang(b_lp),a_ma_tk(b_lp),a_kvuc(b_lp),a_ma_ct(b_lp),a_ngay_kt(b_lp),b_nsd);
    b_loi:='';
end loop;
exception when others then if b_loi is null then raise PROGRAM_ERROR; else null; end if;
end;
/
create or replace procedure PBH_HD_MA_KH_NHf(
    b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_oraIn clob)
AS
    b_loi varchar2(100); b_ps varchar2(1); b_ma varchar2(20);
begin
-- Dan - Nhap ma khach hang
b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'BH','MA','MN');
if b_loi is not null then raise PROGRAM_ERROR; end if;
PBH_DTAC_MA_NHnf(b_oraIn,b_ma,b_ps,b_loi,b_ma_dvi,b_nsd);
if b_loi is not null then raise PROGRAM_ERROR; end if;
commit;
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_2B_LOAI_NHf
    (b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_oraIn clob)
AS
    b_loi varchar2(100); b_i1 number; b_lenh varchar2(1000);
    a_ma pht_type.a_var; a_ten pht_type.a_nvar;a_ngay_kt pht_type.a_num;
begin
-- Dan
b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'BH','2B','M');
if b_loi is not null then raise PROGRAM_ERROR; end if;
b_lenh:=FKH_JS_LENH('ma,ten,ngay_kt');
EXECUTE IMMEDIATE b_lenh bulk collect into a_ma,a_ten,a_ngay_kt using b_oraIn;
for b_lp in 1..a_ma.count loop
  if trim(a_ma(b_lp)) is null then b_loi:='loi:Nhap ma:loi'; raise PROGRAM_ERROR; end if;
  if trim(a_ten(b_lp)) is null then b_loi:='loi:Nhap ten:loi'; raise PROGRAM_ERROR; end if;
  a_ngay_kt(b_lp):=nvl(a_ngay_kt(b_lp),0);
  if a_ngay_kt(b_lp)=0 then a_ngay_kt(b_lp):=30000101; end if;
  b_loi:='';
  delete bh_2b_loai where ma=a_ma(b_lp);
  insert into bh_2b_loai values(b_ma_dvi,a_ma(b_lp),a_ten(b_lp),a_ngay_kt(b_lp),b_nsd);
end loop;
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;
/
create or replace procedure PBH_2B_HANG_NHf
    (b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_oraIn clob)
AS
    b_loi varchar2(100); b_i1 number; b_lenh varchar2(1000);
    a_ma pht_type.a_var; a_ten pht_type.a_nvar;a_ngay_kt pht_type.a_num;
begin
-- Dan
b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'BH','MA','M');
if b_loi is not null then raise PROGRAM_ERROR; end if;
b_lenh:=FKH_JS_LENH('ma,ten,ngay_kt');
EXECUTE IMMEDIATE b_lenh bulk collect into a_ma,a_ten,a_ngay_kt using b_oraIn;
for b_lp in 1..a_ma.count loop
  if trim(a_ma(b_lp)||a_ten(b_lp)) is null then b_loi:='loi:Nhap ma, ten:loi'; raise PROGRAM_ERROR; end if;
  a_ngay_kt(b_lp):=nvl(a_ngay_kt(b_lp),0);
  if a_ngay_kt(b_lp)=0 then a_ngay_kt(b_lp):=30000101; end if;
  delete bh_2b_hang where ma=a_ma(b_lp);
  insert into bh_2b_hang values(b_ma_dvi,a_ma(b_lp),a_ten(b_lp),a_ngay_kt(b_lp),b_nsd);
end loop;
commit;
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_2B_HIEU_NHf
    (b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_oraIn clob)
AS
    b_loi varchar2(100); b_lenh varchar2(1000);
    a_ma pht_type.a_var; a_hang pht_type.a_var; a_ngay_kt pht_type.a_num;
begin
-- Dan
b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'BH','MA','M');
if b_loi is not null then raise PROGRAM_ERROR; end if;
b_lenh:=FKH_JS_LENH('hang,ma,ngay_kt');
EXECUTE IMMEDIATE b_lenh bulk collect into a_hang,a_ma,a_ngay_kt using b_oraIn;
for b_lp in 1..a_hang.count loop
  a_hang(b_lp):=trim(a_hang(b_lp));
  a_ma(b_lp):=trim(a_ma(b_lp));
  if a_hang(b_lp) is null or a_ma(b_lp) is null then b_loi:='loi:Nhap hang, hieu:loi'; raise PROGRAM_ERROR; end if;
  if FBH_2B_HANG_HAN(a_hang(b_lp))<>'C' then b_loi:='loi:Ma hang '||a_hang(b_lp)||' da xoa:loi'; raise PROGRAM_ERROR; end if;
  a_ngay_kt(b_lp):=nvl(a_ngay_kt(b_lp),0);
  if a_ngay_kt(b_lp)=0 then a_ngay_kt(b_lp):=30000101; end if;
  delete bh_2b_hieu where hang=a_hang(b_lp) and ma=a_ma(b_lp);
  insert into bh_2b_hieu values(b_ma_dvi,a_hang(b_lp),a_ma(b_lp),a_ngay_kt(b_lp),b_nsd);
end loop;
commit;
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_XE_LOAI_NHf
    (b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_oraIn clob)
AS
    b_loi varchar2(100); b_i1 number; b_lenh varchar2(1000);
    a_ma pht_type.a_var; a_ten pht_type.a_nvar;a_ngay_kt pht_type.a_num;
begin
-- Dan
b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'BH','XE','M');
if b_loi is not null then raise PROGRAM_ERROR; end if;
b_lenh:=FKH_JS_LENH('ma,ten,ngay_kt');
EXECUTE IMMEDIATE b_lenh bulk collect into a_ma,a_ten,a_ngay_kt using b_oraIn;
for b_lp in 1..a_ma.count loop
  if trim(a_ma(b_lp)) is null then b_loi:='loi:Nhap ma:loi'; raise PROGRAM_ERROR; end if;
  if trim(a_ten(b_lp)) is null then b_loi:='loi:Nhap ten:loi'; raise PROGRAM_ERROR; end if;
  a_ngay_kt(b_lp):=nvl(a_ngay_kt(b_lp),0);
  if a_ngay_kt(b_lp)=0 then a_ngay_kt(b_lp):=30000101; end if;
  b_loi:='';
  delete bh_xe_loai where ma=a_ma(b_lp);
  insert into bh_xe_loai values(b_ma_dvi,a_ma(b_lp),a_ten(b_lp),a_ngay_kt(b_lp),b_nsd,b_oraIn);
end loop;
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;
/
create or replace procedure PBH_XE_NHOM_NHf
    (b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_oraIn clob)
AS
    b_loi varchar2(100); b_i1 number; b_lenh varchar2(1000);
    a_ma pht_type.a_var; a_ten pht_type.a_nvar;a_ngay_kt pht_type.a_num;
begin
-- Dan
b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'BH','XE','M');
if b_loi is not null then raise PROGRAM_ERROR; end if;
b_lenh:=FKH_JS_LENH('ma,ten,ngay_kt');
EXECUTE IMMEDIATE b_lenh bulk collect into a_ma,a_ten,a_ngay_kt using b_oraIn;
for b_lp in 1..a_ma.count loop
  if trim(a_ma(b_lp)) is null then b_loi:='loi:Nhap ma:loi'; raise PROGRAM_ERROR; end if;
  if trim(a_ten(b_lp)) is null then b_loi:='loi:Nhap ten:loi'; raise PROGRAM_ERROR; end if;
  a_ngay_kt(b_lp):=nvl(a_ngay_kt(b_lp),0);
  if a_ngay_kt(b_lp)=0 then a_ngay_kt(b_lp):=30000101; end if;
  b_loi:='';
  delete bh_xe_nhom where ma=a_ma(b_lp);
  insert into bh_xe_nhom values(b_ma_dvi,a_ma(b_lp),a_ten(b_lp),a_ngay_kt(b_lp),b_nsd,b_oraIn);
end loop;
commit;
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_XE_DONG_NHf
    (b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_oraIn clob)
AS
    b_loi varchar2(100); b_i1 number; b_lenh varchar2(1000);
    a_ma pht_type.a_var; a_ten pht_type.a_nvar;a_ngay_kt pht_type.a_num;
begin
-- Dan
b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'BH','XE','M');
if b_loi is not null then raise PROGRAM_ERROR; end if;
b_lenh:=FKH_JS_LENH('ma,ten,ngay_kt');
EXECUTE IMMEDIATE b_lenh bulk collect into a_ma,a_ten,a_ngay_kt using b_oraIn;
for b_lp in 1..a_ma.count loop
    if trim(a_ma(b_lp)) is null then b_loi:='loi:Nhap ma:loi'; raise PROGRAM_ERROR; end if;
    if trim(a_ten(b_lp)) is null then b_loi:='loi:Nhap ten:loi'; raise PROGRAM_ERROR; end if;
    a_ngay_kt(b_lp):=nvl(a_ngay_kt(b_lp),0);
    if a_ngay_kt(b_lp)=0 then a_ngay_kt(b_lp):=30000101; end if;
    b_loi:='';
    delete bh_xe_dong where ma=a_ma(b_lp);
    insert into bh_xe_dong values(b_ma_dvi,a_ma(b_lp),a_ten(b_lp),a_ngay_kt(b_lp),b_nsd,b_oraIn);
end loop;
commit;
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_XE_HANG_NHf
    (b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_oraIn clob)
AS
    b_loi varchar2(100); b_i1 number; b_lenh varchar2(1000);
    a_ma pht_type.a_var; a_ten pht_type.a_nvar;a_ngay_kt pht_type.a_num;
begin
-- Dan
b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'BH','XE','M');
if b_loi is not null then raise PROGRAM_ERROR; end if;
b_lenh:=FKH_JS_LENH('ma,ten,ngay_kt');
EXECUTE IMMEDIATE b_lenh bulk collect into a_ma,a_ten,a_ngay_kt using b_oraIn;
for b_lp in 1..a_ma.count loop
    if trim(a_ma(b_lp)||a_ten(b_lp)) is null then b_loi:='loi:Nhap ma, ten:loi'; raise PROGRAM_ERROR; end if;
    a_ngay_kt(b_lp):=nvl(a_ngay_kt(b_lp),0);
    if a_ngay_kt(b_lp)=0 then a_ngay_kt(b_lp):=30000101; end if;
    delete bh_xe_hang where ma=a_ma(b_lp);
    insert into bh_xe_hang values(b_ma_dvi,a_ma(b_lp),a_ten(b_lp),a_ngay_kt(b_lp),b_nsd);
end loop;
commit;
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_XE_HIEU_NHf
    (b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_oraIn clob)
AS
    b_loi varchar2(100); b_lenh varchar2(1000);
    a_ma pht_type.a_var; a_hang pht_type.a_var;a_ngay_kt pht_type.a_num;
begin
-- Dan
b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'BH','XE','M');
if b_loi is not null then raise PROGRAM_ERROR; end if;
b_lenh:=FKH_JS_LENH('hang,ma,ngay_kt');
EXECUTE IMMEDIATE b_lenh bulk collect into a_hang,a_ma,a_ngay_kt using b_oraIn;
for b_lp in 1..a_hang.count loop
    a_hang(b_lp):=trim(a_hang(b_lp)); a_ma(b_lp):=trim(a_ma(b_lp));
    if a_hang(b_lp) is null or a_ma(b_lp) is null then b_loi:='loi:Nhap hang, hieu:loi'; raise PROGRAM_ERROR; end if;
    if FBH_XE_HANG_HAN(a_hang(b_lp))<>'C' then b_loi:='loi:Ma hang '||a_hang(b_lp)||' da xoa:loi'; raise PROGRAM_ERROR; end if;
    a_ngay_kt(b_lp):=nvl(a_ngay_kt(b_lp),0);
    if a_ngay_kt(b_lp)=0 then a_ngay_kt(b_lp):=30000101; end if;
    delete bh_xe_hieu where hang=a_hang(b_lp) and ma=a_ma(b_lp);
    insert into bh_xe_hieu values(b_ma_dvi,a_hang(b_lp),a_ma(b_lp),a_ngay_kt(b_lp),b_nsd);
end loop;
commit;
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_XE_PB_NHf
  (b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_oraIn clob)
AS
    b_loi varchar2(100); b_lenh varchar2(1000);
  a_ma pht_type.a_var;a_hang pht_type.a_var;a_hieu pht_type.a_var;a_loai_xe pht_type.a_var;a_dong pht_type.a_var;
  a_ttai pht_type.a_num;a_so_cn pht_type.a_num;a_csuat pht_type.a_num;a_dco pht_type.a_var;a_ngay_kt pht_type.a_num;
begin
-- Dan
b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'BH','XE','N');
if b_loi is not null then raise PROGRAM_ERROR; end if;
b_lenh:=FKH_JS_LENH('ma,hang,hieu,loai_xe,dong,ttai,so_cn,csuat,dco,ngay_kt');
EXECUTE IMMEDIATE b_lenh bulk collect into a_ma,a_hang,a_hieu,a_loai_xe,a_dong,a_ttai,a_so_cn,a_csuat,a_dco,a_ngay_kt using b_oraIn;
for b_lp in 1..a_hang.count loop
  a_hang(b_lp):=trim(a_hang(b_lp)); a_hieu(b_lp):=trim(a_hieu(b_lp)); a_ma(b_lp):=trim(a_ma(b_lp));
  a_loai_xe(b_lp):=trim(a_loai_xe(b_lp)); a_dong(b_lp):=nvl(trim(a_dong(b_lp)),' ');
  if a_hang(b_lp) is null or a_hieu(b_lp) is null or a_ma(b_lp) is null then
    b_loi:='loi:Nhap hang, hieu, phien ban:loi'; raise PROGRAM_ERROR;
  end if;
  if FBH_XE_HANG_HAN(a_hang(b_lp))<>'C' then b_loi:='loi:Sai hang:loi'; raise PROGRAM_ERROR; end if;
  if FBH_XE_HIEU_HAN(a_hang(b_lp),a_hieu(b_lp))<>'C' then b_loi:='loi:Sai hieu:loi'; raise PROGRAM_ERROR; end if;
  -- viet anh xoa chan dco, loai xe
  if a_dong(b_lp)!=' ' and FBH_XE_DONG_HAN(a_dong(b_lp))<>'C' then b_loi:='loi:Sai dong:loi'; raise PROGRAM_ERROR; end if;
  a_ttai(b_lp):=nvl(a_ttai(b_lp),0); a_so_cn(b_lp):=nvl(a_so_cn(b_lp),0); a_csuat(b_lp):=nvl(a_csuat(b_lp),0);
  a_ngay_kt(b_lp):=nvl(a_ngay_kt(b_lp),0);
  if a_ngay_kt(b_lp) is null or a_ngay_kt(b_lp)=0 then a_ngay_kt(b_lp):=30000101; end if;
  delete bh_xe_pb where hang=a_hang(b_lp) and hieu=a_hieu(b_lp) and ma=a_ma(b_lp);
  insert into bh_xe_pb values(b_ma_dvi,a_hang(b_lp),a_hieu(b_lp),a_ma(b_lp),a_loai_xe(b_lp),a_dong(b_lp),a_ttai(b_lp),
  a_so_cn(b_lp),a_csuat(b_lp),a_dco(b_lp),a_ngay_kt(b_lp),b_nsd);
end loop;
commit;
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_XE_GTK_NHf(
    b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_oraIn clob)
AS
    b_loi varchar2(100); b_lenh varchar2(1000);
    a_ma pht_type.a_var; a_hang pht_type.a_var;a_hieu pht_type.a_var; a_nam_sx pht_type.a_num; a_gia pht_type.a_num;
    a_bien_do pht_type.a_num;
begin
-- Dan
b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'BH','XE','N');
if b_loi is not null then raise PROGRAM_ERROR; end if;
b_lenh:=FKH_JS_LENH('ma,hang,hieu,nam_sx,gia,bien_do');
EXECUTE IMMEDIATE b_lenh bulk collect into a_ma,a_hang,a_hieu,a_nam_sx,a_gia,a_bien_do using b_oraIn;
for b_lp in 1..a_hang.count loop
  a_hang(b_lp):=trim(a_hang(b_lp)); a_hieu(b_lp):=trim(a_hieu(b_lp)); a_ma(b_lp):=trim(a_ma(b_lp)); a_nam_sx(b_lp):=nvl(a_nam_sx(b_lp),0);
  if a_hang(b_lp) is null or a_hieu(b_lp) is null or a_ma(b_lp) is null or a_nam_sx(b_lp)=0 then
    b_loi:='loi:Nhap hang, hieu, phien ban, nam SX:loi'; raise PROGRAM_ERROR;
  end if;
  if FBH_XE_HANG_HAN(a_hang(b_lp))<>'C' then b_loi:='loi:Sai hang:loi'; raise PROGRAM_ERROR; end if;
  if FBH_XE_HIEU_HAN(a_hang(b_lp),a_hieu(b_lp))<>'C' then b_loi:='loi:Sai hieu:loi'; raise PROGRAM_ERROR; end if;
  if a_bien_do(b_lp) > 100 then
    b_loi:='loi: bien do tham chieu phai nho hon 100:loi'; raise PROGRAM_ERROR;
  end if;
  a_gia(b_lp):=nvl(a_gia(b_lp),0);
  delete bh_xe_gtk where hang=a_hang(b_lp) and hieu=a_hieu(b_lp) and ma=a_ma(b_lp) and nam_sx=a_nam_sx(b_lp);
  insert into bh_xe_gtk values(b_ma_dvi,a_hang(b_lp),a_hieu(b_lp),a_ma(b_lp),a_nam_sx(b_lp),a_gia(b_lp),a_bien_do(b_lp),b_nsd);
end loop;
commit;
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_XE_MDSD_NHf
    (b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_txt clob)
AS
    b_loi varchar2(100); b_lenh varchar2(1000);
    a_ma pht_type.a_var; a_ten pht_type.a_nvar;a_ngay_kt pht_type.a_num;
begin
-- Dan
b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'BH','XE','M');
if b_loi is not null then raise PROGRAM_ERROR; end if;
b_lenh:=FKH_JS_LENH('ma,ten,ngay_kt');
EXECUTE IMMEDIATE b_lenh bulk collect into a_ma,a_ten,a_ngay_kt using b_txt;
for b_lp in 1..a_ma.count loop
  if trim(a_ma(b_lp)) is null then b_loi:='loi:Nhap ma:loi'; raise PROGRAM_ERROR; end if;
  if trim(a_ten(b_lp)) is null then b_loi:='loi:Nhap ten:loi'; raise PROGRAM_ERROR; end if;
  a_ngay_kt(b_lp):=nvl(a_ngay_kt(b_lp),0);
  if a_ngay_kt(b_lp)=0 then a_ngay_kt(b_lp):=30000101; end if;
  b_loi:='';
  delete bh_xe_mdsd where ma=a_ma(b_lp);
  insert into bh_xe_mdsd values(b_ma_dvi,a_ma(b_lp),a_ten(b_lp),a_ngay_kt(b_lp),b_nsd,b_txt);
end loop;
commit;
exception when others then if b_loi is null then raise PROGRAM_ERROR; else raise_application_error(-20105,b_loi); end if;
end;