sql_22_05

User documentation
22/05/2025

create or replace procedure PBH_HANG_MA_HANG_NHf
(b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_oraIn clob)
AS
b_loi varchar2(1000); b_i1 number; b_lenh varchar2(1000);
a_ma pht_type.a_var; a_ten pht_type.a_nvar; a_tc pht_type.a_var; a_ma_ct pht_type.a_var;
a_ngay_bd pht_type.a_num; a_ngay_kt pht_type.a_num; a_mrr pht_type.a_var;b_txt clob:='"{}"';
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,tc,ma_ct,ngay_bd,ngay_kt,mrr');
EXECUTE IMMEDIATE b_lenh bulk collect into a_ma,a_ten,a_tc,a_ma_ct,a_ngay_bd,a_ngay_kt,a_mrr 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_ma_ct(b_lp):=nvl(trim(a_ma_ct(b_lp)),' ');
if a_ma_ct(b_lp) = ' ' then a_tc(b_lp):= 'T';
else a_tc(b_lp):= 'C';
end if;
a_tc(b_lp):=nvl(trim(a_tc(b_lp)),'C');
if a_ma_ct(b_lp)<>' ' then
b_loi:='loi:Sai ma cap tren:loi';
if a_ma(b_lp)=a_ma_ct(b_lp) then raise PROGRAM_ERROR; end if;
select 0 into b_i1 from bh_hang_loai where ma=a_ma_ct(b_lp) and tc='T';
end if;
a_mrr(b_lp):=nvl(trim(a_mrr(b_lp)),'1');
if a_ngay_bd(b_lp) in(0,30000101) then a_ngay_bd(b_lp):=PKH_NG_CSO(sysdate); 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_hang_loai where ma=a_ma(b_lp);
PKH_JS_THAYa(b_txt,'ma,ten,ma_ct,tc,mrr,ngay_bd,ngay_kt',a_ma(b_lp) || ',' || a_ten(b_lp) || ',' || a_ma_ct(b_lp) || ',' || a_tc(b_lp) || ',' || a_mrr(b_lp)
|| ',' || a_ngay_bd(b_lp) || ',' || a_ngay_kt(b_lp));
insert into bh_hang_loai values(b_ma_dvi,a_ma(b_lp),a_ten(b_lp),a_ma_ct(b_lp),a_tc(b_lp),a_mrr(b_lp),a_ngay_bd(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;
/
create or replace procedure PBH_SK_BENH_NHf
(b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_oraIn clob)
AS
b_loi varchar2(1000); b_i1 number; b_lenh varchar2(1000);
a_ma pht_type.a_var; a_ten pht_type.a_nvar; a_tc pht_type.a_var; a_ma_ct pht_type.a_var;
a_ngay_bd pht_type.a_num; a_ngay_kt pht_type.a_num; a_muc pht_type.a_num;b_txt clob:='"{}"';
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,tc,ma_ct,ngay_bd,ngay_kt,muc');
EXECUTE IMMEDIATE b_lenh bulk collect into a_ma,a_ten,a_tc,a_ma_ct,a_ngay_bd,a_ngay_kt,a_muc 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_tc(b_lp):=nvl(trim(a_tc(b_lp)),'C'); a_ma_ct(b_lp):=nvl(trim(a_ma_ct(b_lp)),' ');
   if a_ma_ct(b_lp)<>' ' then
       b_loi:='loi:Sai ma cap tren:loi';
       if a_ma(b_lp)=a_ma_ct(b_lp) then raise PROGRAM_ERROR; end if;
       select 0 into b_i1 from bh_sk_benh where ma=a_ma_ct(b_lp) and tc='T';
   end if;
   if a_tc(b_lp)='C' then
       a_muc(b_lp):=nvl(trim(a_muc(b_lp)),1);
   else
       a_muc(b_lp):=0;
   end if;
   if a_ngay_bd(b_lp) in(0,30000101) then a_ngay_bd(b_lp):=PKH_NG_CSO(sysdate); 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:='';
   if a_tc(b_lp)='C' then
       delete bh_sk_benhL where ma=a_ma(b_lp) and ngay_bd=a_ngay_bd(b_lp);
       insert into bh_sk_benhL select ma,ngay_bd,muc
           from bh_sk_benh where ma=a_ma(b_lp) and ngay_bd<>a_ngay_bd(b_lp);
   end if;
   delete bh_sk_benh where ma=a_ma(b_lp);
   PKH_JS_THAYa(b_txt,'ma,ten,ma_ct,tc,mrr,ngay_bd,ngay_kt',a_ma(b_lp) || ',' || a_ten(b_lp) || ',' || a_ma_ct(b_lp) || ',' || a_tc(b_lp) || ',' || a_muc(b_lp)
       || ',' || a_ngay_bd(b_lp) || ',' || a_ngay_kt(b_lp));
   insert into bh_sk_benh values(b_ma_dvi,a_ma(b_lp),a_ten(b_lp),a_tc(b_lp),a_ma_ct(b_lp),a_muc(b_lp),a_ngay_bd(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;
/
create or replace procedure PBH_SK_SP_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_tc pht_type.a_var;
a_ma_ct pht_type.a_var; a_ngay_bd pht_type.a_num; a_ngay_kt pht_type.a_num;b_txt clob:='"{}"';
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,tc,ma_ct,ngay_bd,ngay_kt');
EXECUTE IMMEDIATE b_lenh bulk collect into a_ma,a_ten,a_tc,a_ma_ct,a_ngay_bd,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;
if a_tc(b_lp) is null or a_tc(b_lp)<>'T' then a_tc(b_lp):='C'; end if;
if trim(a_ma_ct(b_lp)) is null then
a_ma_ct(b_lp):=' ';
else
b_loi:='loi:Sai ma cap tren:loi';
if a_ma(b_lp)=a_ma_ct(b_lp) then raise PROGRAM_ERROR; end if;
select 0 into b_i1 from bh_sk_sp where ma=a_ma_ct(b_lp) and tc='T';
end if;
if a_ngay_bd(b_lp) in(0,30000101) then a_ngay_bd(b_lp):=PKH_NG_CSO(sysdate); 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_sk_sp where ma=a_ma(b_lp);
PKH_JS_THAYa(b_txt,'ma,ten,ma_ct,tc,ngay_bd,ngay_kt',a_ma(b_lp) || ',' || a_ten(b_lp) || ',' || a_ma_ct(b_lp) || ',' || a_tc(b_lp)
|| ',' || a_ngay_bd(b_lp) || ',' || a_ngay_kt(b_lp));
insert into bh_sk_sp values(b_ma_dvi,a_ma(b_lp),a_ten(b_lp),a_tc(b_lp),a_ma_ct(b_lp),a_ngay_bd(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;
/
create or replace procedure PBH_SK_GOI_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;b_txt clob:='"{}"';
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)) 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_sk_goi where ma=a_ma(b_lp);
   PKH_JS_THAYa(b_txt,'ma,ten,ngay_kt',a_ma(b_lp) || ',' || a_ten(b_lp) || ',' || a_ngay_kt(b_lp));
   insert into bh_sk_goi 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;
/
create or replace procedure PBH_SK_PTHUAT_NHf
(b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_oraIn clob)
AS
b_loi varchar2(1000); b_i1 number; b_lenh varchar2(1000);
a_ma pht_type.a_var; a_ten pht_type.a_nvar; a_tc pht_type.a_var; a_ma_ct pht_type.a_var;
a_ngay_kt pht_type.a_num; a_tlbtM pht_type.a_num; a_tlbtX pht_type.a_num;b_txt clob:='"{}"';
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,tc,ma_ct,ngay_kt,tlbtm,tlbtx');
EXECUTE IMMEDIATE b_lenh bulk collect into a_ma,a_ten,a_tc,a_ma_ct,a_ngay_kt,a_tlbtM,a_tlbtX 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_tc(b_lp):=nvl(trim(a_tc(b_lp)),'C'); a_ma_ct(b_lp):=nvl(trim(a_ma_ct(b_lp)),' ');
   if a_ma_ct(b_lp)<>' ' then
       b_loi:='loi:Sai ma cap tren:loi';
       if a_ma(b_lp)=a_ma_ct(b_lp) then raise PROGRAM_ERROR; end if;
       select 0 into b_i1 from bh_sk_pthuat where ma=a_ma_ct(b_lp) and tc='T';
   end if;
   a_tlbtM(b_lp):=nvl(a_tlbtM(b_lp),0); a_tlbtX(b_lp):=nvl(a_tlbtX(b_lp),0);
   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_sk_pthuat where ma=a_ma(b_lp);
   PKH_JS_THAYa(b_txt,'ma,ten,tc,ma_ct,ngay_kt,tlbtm,tlbtx',a_ma(b_lp) || ',' || a_ten(b_lp) || ',' || a_tc(b_lp) || ',' || a_ma_ct(b_lp) || ',' ||
   a_ngay_kt(b_lp) || ',' || a_tlbtM(b_lp) || ',' || a_tlbtX(b_lp));
   insert into bh_sk_pthuat values(b_ma_dvi,a_ma(b_lp),a_ten(b_lp),a_tc(b_lp),a_ma_ct(b_lp),a_tlbtM(b_lp),a_tlbtX(b_lp),a_ngay_kt(b_lp),b_nsd,'"'||b_txt||'"');
end loop;
commit;
b_loi:='';
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_SK_TTTL_NHf
(b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_oraIn clob)
AS
b_loi varchar2(1000); b_i1 number; b_lenh varchar2(1000);
a_ma pht_type.a_var; a_ten pht_type.a_nvar; a_tc pht_type.a_var; a_ma_ct pht_type.a_var;
a_ngay_kt pht_type.a_num; a_tlbtM pht_type.a_num; a_tlbtX pht_type.a_num;b_txt clob:='"{}"';
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,tc,ma_ct,ngay_kt,tlbtm,tlbtx');
EXECUTE IMMEDIATE b_lenh bulk collect into a_ma,a_ten,a_tc,a_ma_ct,a_ngay_kt,a_tlbtM,a_tlbtX 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_tc(b_lp):=nvl(trim(a_tc(b_lp)),'C'); a_ma_ct(b_lp):=nvl(trim(a_ma_ct(b_lp)),' ');
   if a_ma_ct(b_lp)<>' ' then
       b_loi:='loi:Sai ma cap tren:loi';
       if a_ma(b_lp)=a_ma_ct(b_lp) then raise PROGRAM_ERROR; end if;
       select 0 into b_i1 from bh_sk_tttl where ma=a_ma_ct(b_lp) and tc='T';
   end if;
   a_tlbtM(b_lp):=nvl(a_tlbtM(b_lp),0); a_tlbtX(b_lp):=nvl(a_tlbtX(b_lp),0);
   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_sk_tttl where ma=a_ma(b_lp);
   PKH_JS_THAYa(b_txt,'ma,ten,tc,ma_ct,ngay_kt,tlbtm,tlbtx',a_ma(b_lp) || ',' || a_ten(b_lp) || ',' || a_tc(b_lp) || ',' || a_ma_ct(b_lp) || ',' ||
   a_ngay_kt(b_lp) || ',' || a_tlbtM(b_lp) || ',' || a_tlbtX(b_lp));
   insert into bh_sk_tttl values(b_ma_dvi,a_ma(b_lp),a_ten(b_lp),a_tc(b_lp),a_ma_ct(b_lp),a_tlbtM(b_lp),a_tlbtX(b_lp),a_ngay_kt(b_lp),b_nsd,'"'||b_txt||'"');
end loop;
commit;
b_loi:='';
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_NGTD_SP_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_tc pht_type.a_var;
a_ma_ct pht_type.a_var; a_ngay_bd pht_type.a_num; a_ngay_kt pht_type.a_num;b_txt clob:='"{}"';
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,tc,ma_ct,ngay_bd,ngay_kt');
EXECUTE IMMEDIATE b_lenh bulk collect into a_ma,a_ten,a_tc,a_ma_ct,a_ngay_bd,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;
   if a_tc(b_lp) is null or a_tc(b_lp)<>'T' then a_tc(b_lp):='C'; end if;
   if trim(a_ma_ct(b_lp)) is null then
       a_ma_ct(b_lp):=' ';
   else
       b_loi:='loi:Sai ma cap tren:loi';
       if a_ma(b_lp)=a_ma_ct(b_lp) then raise PROGRAM_ERROR; end if;
       select 0 into b_i1 from bh_ngtd_sp where ma=a_ma_ct(b_lp) and tc='T';
   end if;
   if a_ngay_bd(b_lp) in(0,30000101) then a_ngay_bd(b_lp):=PKH_NG_CSO(sysdate); 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_ngtd_sp where ma=a_ma(b_lp);
   PKH_JS_THAYa(b_txt,'ma,ten,tc,ma_ct,ngay_bd,ngay_kt',a_ma(b_lp) || ',' || a_ten(b_lp) || ',' || a_tc(b_lp) || ',' || a_ma_ct(b_lp)
|| ',' || a_ngay_bd(b_lp) || ',' || a_ngay_kt(b_lp));
   insert into bh_ngtd_sp values(b_ma_dvi,a_ma(b_lp),a_ten(b_lp),a_tc(b_lp),a_ma_ct(b_lp),a_ngay_bd(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;
/
create or replace procedure PBH_NGTD_GOI_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;b_txt clob:='"{}"';
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)) 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_ngtd_goi where ma=a_ma(b_lp);
   PKH_JS_THAYa(b_txt,'ma,ten,ngay_kt',a_ma(b_lp) || ',' || a_ten(b_lp) || ',' || a_ngay_kt(b_lp));
   insert into bh_ngtd_goi 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;
/
create or replace procedure PBH_NGDL_SP_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_tc pht_type.a_var;
a_ma_ct pht_type.a_var; a_ngay_bd pht_type.a_num; a_ngay_kt pht_type.a_num;b_txt clob:='"{}"';
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,tc,ma_ct,ngay_bd,ngay_kt');
EXECUTE IMMEDIATE b_lenh bulk collect into a_ma,a_ten,a_tc,a_ma_ct,a_ngay_bd,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;
   if a_tc(b_lp) is null or a_tc(b_lp)<>'T' then a_tc(b_lp):='C'; end if;
   if trim(a_ma_ct(b_lp)) is null then
       a_ma_ct(b_lp):=' ';
   else
       b_loi:='loi:Sai ma cap tren:loi';
       if a_ma(b_lp)=a_ma_ct(b_lp) then raise PROGRAM_ERROR; end if;
       select 0 into b_i1 from bh_ngdl_sp where ma=a_ma_ct(b_lp) and tc='T';
   end if;
   if a_ngay_bd(b_lp) in(0,30000101) then a_ngay_bd(b_lp):=PKH_NG_CSO(sysdate); 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_ngdl_sp where ma=a_ma(b_lp);
   PKH_JS_THAYa(b_txt,'ma,ten,tc,ma_ct,ngay_bd,ngay_kt',a_ma(b_lp) || ',' || a_ten(b_lp) || ',' || a_tc(b_lp) || ',' || a_ma_ct(b_lp)
|| ',' || a_ngay_bd(b_lp) || ',' || a_ngay_kt(b_lp));
   insert into bh_ngdl_sp values(b_ma_dvi,a_ma(b_lp),a_ten(b_lp),a_tc(b_lp),a_ma_ct(b_lp),a_ngay_bd(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;
/
create or replace procedure PBH_NGDL_KVUC_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;b_txt clob:='"{}"';
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)) 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_ngdl_kvuc where ma=a_ma(b_lp);
   PKH_JS_THAYa(b_txt,'ma,ten,ngay_kt',a_ma(b_lp) || ',' || a_ten(b_lp) || ',' || a_ngay_kt(b_lp));
   insert into bh_ngdl_kvuc 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;
/
create or replace procedure PBH_NGDL_GOI_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;b_txt clob:='"{}"';
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)) 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_ngdl_goi where ma=a_ma(b_lp);
   PKH_JS_THAYa(b_txt,'ma,ten,ngay_kt',a_ma(b_lp) || ',' || a_ten(b_lp) || ',' || a_ngay_kt(b_lp));
   insert into bh_ngdl_goi 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;

Project

General

Profile

AAA

sql_22_05

Files