tim kiem an chi 250514
SQL Sprint
14/05/2025
create or replace procedure PHD_CT_TIM_J
(b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_oraIn clob,b_oraOut out clob)
AS
b_lenh varchar2(1000);b_loi varchar2(100); b_n1 number; b_n2 number; b_dc0 varchar2(4); b_dc1 varchar2(4);b_lct varchar2(1);
b_ngay_d number;b_ngay_c number;b_loai_nh varchar2(10);b_ma_nh varchar2(10);b_ma_tke varchar2(10);
b_ma varchar2(20);b_nd nvarchar2(10);b_so number;b_so_c number;b_tu number; b_den number;
b_dong number;cs_lke clob;
begin
-- Tim kiem chung tu hoa don Da sua theo JS
b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'HD','NX','N');
if b_loi is not null then raise PROGRAM_ERROR; end if;
b_lenh:=FKH_JS_LENH('l_ct,ngay_d,ngay_c,loai_n,ma_n,tke,ma,nd,dau,cuoi,tu,den');
EXECUTE IMMEDIATE b_lenh into b_lct,b_ngay_d,b_ngay_c,b_loai_nh,b_ma_nh,b_ma_tke,b_ma,b_nd,b_so,b_so_c,b_tu,b_den using b_oraIn;
delete temp_1; commit;
if b_lct='T' then
insert into temp_1(n1) select so_id from hd_1 where ma_dvi=b_ma_dvi and
(ngay_ht between b_ngay_d and b_ngay_c)
and (b_nd is null or upper(nd) like b_nd)
and (b_ma_nh is null or (loai_n=b_loai_nh and ma_n=b_ma_nh));
else
insert into temp_1(n1) select so_id from hd_1 where ma_dvi=b_ma_dvi and
(ngay_ht between b_ngay_d and b_ngay_c) and l_ct=b_lct and (b_nd is null or upper(nd) like b_nd)
and (b_ma_nh is null or (loai_n=b_loai_nh and ma_n=b_ma_nh));
end if;
(b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_oraIn clob,b_oraOut out clob)
AS
b_lenh varchar2(1000);b_loi varchar2(100); b_n1 number; b_n2 number; b_dc0 varchar2(4); b_dc1 varchar2(4);b_lct varchar2(1);
b_ngay_d number;b_ngay_c number;b_loai_nh varchar2(10);b_ma_nh varchar2(10);b_ma_tke varchar2(10);
b_ma varchar2(20);b_nd nvarchar2(10);b_so number;b_so_c number;b_tu number; b_den number;
b_dong number;cs_lke clob;
begin
-- Tim kiem chung tu hoa don Da sua theo JS
b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'HD','NX','N');
if b_loi is not null then raise PROGRAM_ERROR; end if;
b_lenh:=FKH_JS_LENH('l_ct,ngay_d,ngay_c,loai_n,ma_n,tke,ma,nd,dau,cuoi,tu,den');
EXECUTE IMMEDIATE b_lenh into b_lct,b_ngay_d,b_ngay_c,b_loai_nh,b_ma_nh,b_ma_tke,b_ma,b_nd,b_so,b_so_c,b_tu,b_den using b_oraIn;
delete temp_1; commit;
if b_lct='T' then
insert into temp_1(n1) select so_id from hd_1 where ma_dvi=b_ma_dvi and
(ngay_ht between b_ngay_d and b_ngay_c)
and (b_nd is null or upper(nd) like b_nd)
and (b_ma_nh is null or (loai_n=b_loai_nh and ma_n=b_ma_nh));
else
insert into temp_1(n1) select so_id from hd_1 where ma_dvi=b_ma_dvi and
(ngay_ht between b_ngay_d and b_ngay_c) and l_ct=b_lct and (b_nd is null or upper(nd) like b_nd)
and (b_ma_nh is null or (loai_n=b_loai_nh and ma_n=b_ma_nh));
end if;
if b_ma is not null then
delete temp_3;
if nvl(b_so,0)=0 then
delete temp_1 where not exists
(select * from hd_2 where ma_dvi=b_ma_dvi and so_id=n1 and ma=b_ma);
else
if b_so_c=0 then
insert into temp_3(n1) values (b_so);
else
for b_lp in b_so..b_so_c loop
insert into temp_3(n1) values (b_lp);
end loop;
end if;
delete temp_2;
for b_lpp in (select n1 from temp_3 order by n1) loop
b_n1:=b_lpp.n1;
insert into temp_2(n1) select so_id from hd_2 where ma_dvi=b_ma_dvi and ma=b_ma and b_n1 between dau and cuoi;
end loop;
delete temp_1 where n1 not in (select n1 from temp_2);
end if;
delete temp_3;
if nvl(b_so,0)=0 then
delete temp_1 where not exists
(select * from hd_2 where ma_dvi=b_ma_dvi and so_id=n1 and ma=b_ma);
else
if b_so_c=0 then
insert into temp_3(n1) values (b_so);
else
for b_lp in b_so..b_so_c loop
insert into temp_3(n1) values (b_lp);
end loop;
end if;
delete temp_2;
for b_lpp in (select n1 from temp_3 order by n1) loop
b_n1:=b_lpp.n1;
insert into temp_2(n1) select so_id from hd_2 where ma_dvi=b_ma_dvi and ma=b_ma and b_n1 between dau and cuoi;
end loop;
delete temp_1 where n1 not in (select n1 from temp_2);
end if;
end if;
delete temp_2;
insert into temp_2(n1,c1,c2,c3,c4,c5,c6,c7,n2) select distinct b.so_id,b.l_ct,so_ct,loai_n,ma_n,nd,ma,PKH_SO_CNG(b.ngay_ht),b.ngay_ht
from hd_1 b,hd_2 c where b.ma_dvi=b_ma_dvi and b.so_id=c.so_id and b.so_id in (select n1 from temp_1) and (c.ma=b_ma or b_ma is null) and
c.ma_dvi=b_ma_dvi and (b_ma_tke='A' or c.ma_tke=b_ma_tke);
delete temp_2;
insert into temp_2(n1,c1,c2,c3,c4,c5,c6,c7,n2) select distinct b.so_id,b.l_ct,so_ct,loai_n,ma_n,nd,ma,PKH_SO_CNG(b.ngay_ht),b.ngay_ht
from hd_1 b,hd_2 c where b.ma_dvi=b_ma_dvi and b.so_id=c.so_id and b.so_id in (select n1 from temp_1) and (c.ma=b_ma or b_ma is null) and
c.ma_dvi=b_ma_dvi and (b_ma_tke='A' or c.ma_tke=b_ma_tke);
select count(*) into b_dong from temp_2;
select JSON_ARRAYAGG(obj returning clob) into cs_lke from
(select JSON_OBJECT(so_id,l_ct,so_ct,loai_n,ma_n,nd,ma,ngay_htc) obj from (select n1 so_id, c1 l_ct,c2 so_ct,c3 loai_n,c4 ma_n,c5 nd,c6 ma,c7 ngay_htc,
row_number() over (order by n2,c2) sott from temp_2 order by n1) where sott between b_tu and b_den);
select json_object('dong' value b_dong, 'cs_lke' value cs_lke) into b_oraOut from dual;
exception when others then raise_application_error(-20105,b_loi);
end;
select JSON_ARRAYAGG(obj returning clob) into cs_lke from
(select JSON_OBJECT(so_id,l_ct,so_ct,loai_n,ma_n,nd,ma,ngay_htc) obj from (select n1 so_id, c1 l_ct,c2 so_ct,c3 loai_n,c4 ma_n,c5 nd,c6 ma,c7 ngay_htc,
row_number() over (order by n2,c2) sott from temp_2 order by n1) where sott between b_tu and b_den);
select json_object('dong' value b_dong, 'cs_lke' value cs_lke) into b_oraOut from dual;
exception when others then raise_application_error(-20105,b_loi);
end;
/
create or replace procedure PHD_CT_NH_J
(b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_oraIn clob,b_oraOut out clob)
AS
b_lenh varchar2(1000);b_loi varchar2(150);b_c2 varchar2(2);b_ngay_m number;b_dau number;b_cuoi number;
b_i1 number;b_i2 number;b_c1 varchar2(1);b_c10 varchar2(10);b_dl varchar2(10);
b_ngay_ht number;b_htoan varchar2(1);b_so_id number;
b_l_ct varchar2(1);b_so_ct varchar2(20);b_ngay_ct varchar2(20);d_ngay_ct date; b_ma_cc varchar2(10);b_loai_nh varchar2(2);b_ma_nh varchar2(10);b_nd nvarchar2(400);
dt_ct clob; dt_dk_ct clob; a_ma pht_type.a_var;a_seri pht_type.a_var;a_quyen pht_type.a_var;a_dau pht_type.a_num;
a_cuoi pht_type.a_num;a_tke pht_type.a_var;a_gia pht_type.a_num;b_so_id_bh number:=0;
begin
---Lan--Nhap ctu hoa don---
b_lenh:=FKH_JS_LENH('so_id');
EXECUTE IMMEDIATE b_lenh into b_so_id using b_oraIn;
b_lenh:=FKH_JS_LENHc('dt_ct,dk_ct');
EXECUTE IMMEDIATE b_lenh into dt_ct,dt_dk_ct using b_oraIn;
b_lenh:=FKH_JS_LENH('ngay_ht,htoan,l_ct,so_ct,ngay_ct,ma_cc,loai_n,ma_n,nd');
EXECUTE IMMEDIATE b_lenh into b_ngay_ht,b_htoan,b_l_ct,b_so_ct,b_ngay_ct,b_ma_cc,b_loai_nh,b_ma_nh,b_nd using dt_ct;
if b_ngay_ct is not null then
d_ngay_ct := to_date(b_ngay_ct,'yyyymmdd');
end if;
b_lenh:=FKH_JS_LENH('ma,seri,quyen,dau,cuoi,tke,gia');
EXECUTE IMMEDIATE b_lenh bulk collect into a_ma,a_seri,a_quyen,a_dau,a_cuoi,a_tke,a_gia using dt_dk_ct;
if a_ma.count=0 then b_loi:='loi:Nhap dieu khoan:loi'; return; end if;
(b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_oraIn clob,b_oraOut out clob)
AS
b_lenh varchar2(1000);b_loi varchar2(150);b_c2 varchar2(2);b_ngay_m number;b_dau number;b_cuoi number;
b_i1 number;b_i2 number;b_c1 varchar2(1);b_c10 varchar2(10);b_dl varchar2(10);
b_ngay_ht number;b_htoan varchar2(1);b_so_id number;
b_l_ct varchar2(1);b_so_ct varchar2(20);b_ngay_ct varchar2(20);d_ngay_ct date; b_ma_cc varchar2(10);b_loai_nh varchar2(2);b_ma_nh varchar2(10);b_nd nvarchar2(400);
dt_ct clob; dt_dk_ct clob; a_ma pht_type.a_var;a_seri pht_type.a_var;a_quyen pht_type.a_var;a_dau pht_type.a_num;
a_cuoi pht_type.a_num;a_tke pht_type.a_var;a_gia pht_type.a_num;b_so_id_bh number:=0;
begin
---Lan--Nhap ctu hoa don---
b_lenh:=FKH_JS_LENH('so_id');
EXECUTE IMMEDIATE b_lenh into b_so_id using b_oraIn;
b_lenh:=FKH_JS_LENHc('dt_ct,dk_ct');
EXECUTE IMMEDIATE b_lenh into dt_ct,dt_dk_ct using b_oraIn;
b_lenh:=FKH_JS_LENH('ngay_ht,htoan,l_ct,so_ct,ngay_ct,ma_cc,loai_n,ma_n,nd');
EXECUTE IMMEDIATE b_lenh into b_ngay_ht,b_htoan,b_l_ct,b_so_ct,b_ngay_ct,b_ma_cc,b_loai_nh,b_ma_nh,b_nd using dt_ct;
if b_ngay_ct is not null then
d_ngay_ct := to_date(b_ngay_ct,'yyyymmdd');
end if;
b_lenh:=FKH_JS_LENH('ma,seri,quyen,dau,cuoi,tke,gia');
EXECUTE IMMEDIATE b_lenh bulk collect into a_ma,a_seri,a_quyen,a_dau,a_cuoi,a_tke,a_gia using dt_dk_ct;
if a_ma.count=0 then b_loi:='loi:Nhap dieu khoan:loi'; return; end if;
if b_nsd is not null then
if b_l_ct='C' then
if b_loai_nh is null or b_loai_nh=' ' or b_ma_nh is null or b_ma_nh=' ' then
b_loi:='loi:Chua nhap don vi nhan:loi';raise PROGRAM_ERROR;
end if;
if b_loai_nh='D' then b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'HD','DC','Q'); end if;
if b_loai_nh='B' then b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'HD','DC','X'); end if;
if b_loai_nh in ('C','L') then
b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'HD','DC','N');
select phong into b_c10 from ht_ma_nsd where ma_dvi=b_ma_dvi and ma=b_nsd;
if b_loai_nh='C' then
select phong into b_dl from ht_ma_cb where ma_dvi=b_ma_dvi and ma=b_ma_nh;
--else
-- select phong into b_dl from bh_dl_ma_kh where ma_dvi=b_ma_dvi and ma=b_ma_nh;
end if;
if b_c10<>b_dl then
b_loi:='loi:Chi duoc dieu chuyen cho can bo, dai ly cung phong:loi';raise PROGRAM_ERROR;
end if;
end if;
elsif b_l_ct='N' then
b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'HD','NX','N');
if b_loai_nh is null or b_loai_nh=' ' or b_ma_nh is null or b_ma_nh=' ' then
b_loi:='loi:Chua nhap don vi nhan:loi'; end if;
elsif b_l_ct='X' then b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'HD','NX','N');
end if;
end if;
if b_loi is not null then raise PROGRAM_ERROR; end if;
b_loi:=PKH_MA_HAN_TEST(b_ma_dvi,b_ngay_ht,'HD','AL');
if b_loi is not null then raise PROGRAM_ERROR; end if;
PHD_TEST (b_ma_dvi,b_nsd,b_pas,b_loi);
if b_loi is not null then raise PROGRAM_ERROR; end if;
if b_l_ct='N' then
select vp into b_c1 from ht_ma_dvi where ma=b_ma_dvi;
if b_loai_nh='D' then select nvl(min(vp),'') into b_c1 from ht_ma_dvi where ma=b_ma_nh; end if;
if b_c1<>'C' and b_nsd is not null then
b_loi:='loi:Van phong moi duoc nhap tu nha cung cap:loi';
raise PROGRAM_ERROR;
end if;
elsif b_l_ct='X' then
if b_nsd not in ('ADMIN','QLDL','QLHD') then
for b_lp in 1..a_tke.count loop
if a_tke(b_lp)='D' then
b_loi:='loi:Chi nhap hoa don, an chi mat, hong, huy:loi';
raise PROGRAM_ERROR;
end if;
end loop;
end if;
end if;
PHD_CT_NH_NH(b_ma_dvi,b_nsd,b_ngay_ht,b_htoan,b_so_id,b_so_id_bh,b_l_ct,b_so_ct,d_ngay_ct,b_ma_cc,b_loai_nh,b_ma_nh,b_nd,a_ma,a_seri,a_quyen,a_dau,a_cuoi,a_tke,a_gia,b_loi);
if b_loi is not null then raise PROGRAM_ERROR; end if;
commit;
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;
if b_l_ct='C' then
if b_loai_nh is null or b_loai_nh=' ' or b_ma_nh is null or b_ma_nh=' ' then
b_loi:='loi:Chua nhap don vi nhan:loi';raise PROGRAM_ERROR;
end if;
if b_loai_nh='D' then b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'HD','DC','Q'); end if;
if b_loai_nh='B' then b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'HD','DC','X'); end if;
if b_loai_nh in ('C','L') then
b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'HD','DC','N');
select phong into b_c10 from ht_ma_nsd where ma_dvi=b_ma_dvi and ma=b_nsd;
if b_loai_nh='C' then
select phong into b_dl from ht_ma_cb where ma_dvi=b_ma_dvi and ma=b_ma_nh;
--else
-- select phong into b_dl from bh_dl_ma_kh where ma_dvi=b_ma_dvi and ma=b_ma_nh;
end if;
if b_c10<>b_dl then
b_loi:='loi:Chi duoc dieu chuyen cho can bo, dai ly cung phong:loi';raise PROGRAM_ERROR;
end if;
end if;
elsif b_l_ct='N' then
b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'HD','NX','N');
if b_loai_nh is null or b_loai_nh=' ' or b_ma_nh is null or b_ma_nh=' ' then
b_loi:='loi:Chua nhap don vi nhan:loi'; end if;
elsif b_l_ct='X' then b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'HD','NX','N');
end if;
end if;
if b_loi is not null then raise PROGRAM_ERROR; end if;
b_loi:=PKH_MA_HAN_TEST(b_ma_dvi,b_ngay_ht,'HD','AL');
if b_loi is not null then raise PROGRAM_ERROR; end if;
PHD_TEST (b_ma_dvi,b_nsd,b_pas,b_loi);
if b_loi is not null then raise PROGRAM_ERROR; end if;
if b_l_ct='N' then
select vp into b_c1 from ht_ma_dvi where ma=b_ma_dvi;
if b_loai_nh='D' then select nvl(min(vp),'') into b_c1 from ht_ma_dvi where ma=b_ma_nh; end if;
if b_c1<>'C' and b_nsd is not null then
b_loi:='loi:Van phong moi duoc nhap tu nha cung cap:loi';
raise PROGRAM_ERROR;
end if;
elsif b_l_ct='X' then
if b_nsd not in ('ADMIN','QLDL','QLHD') then
for b_lp in 1..a_tke.count loop
if a_tke(b_lp)='D' then
b_loi:='loi:Chi nhap hoa don, an chi mat, hong, huy:loi';
raise PROGRAM_ERROR;
end if;
end loop;
end if;
end if;
PHD_CT_NH_NH(b_ma_dvi,b_nsd,b_ngay_ht,b_htoan,b_so_id,b_so_id_bh,b_l_ct,b_so_ct,d_ngay_ct,b_ma_cc,b_loai_nh,b_ma_nh,b_nd,a_ma,a_seri,a_quyen,a_dau,a_cuoi,a_tke,a_gia,b_loi);
if b_loi is not null then raise PROGRAM_ERROR; end if;
commit;
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;