AAA

create or replace procedure PBH_XE_TIM(
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(200); cs_lke clob:='';
b_so_id number; b_so_idC number; b_ma_kh varchar2(20); b_so_hd varchar2(20);
b_cmt varchar2(20); b_mobi varchar2(20); b_email varchar2(20);
b_dong number; b_ngay number:=PKH_NG_CSO(add_months(sysdate,-36));
b_ngayD number; b_ngayC number; b_ttrang varchar2(1);
begin
-- Dan - Tim hop dong qua CMT, mobi, email
delete ket_qua; commit;
b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'BH','PHH','NX');
if b_loi is not null then raise PROGRAM_ERROR; end if;
b_lenh:=FKH_JS_LENH('ngayd,ngayc,ttrang,cmt,mobi,email');
EXECUTE IMMEDIATE b_lenh into b_ngayD,b_ngayC,b_ttrang,b_cmt,b_mobi,b_email using b_oraIn;
b_ttrang:=nvl(trim(b_ttrang),' ');
if b_ngayD in (0,30000101) then b_ngayD:=PKH_NG_CSO(add_months(sysdate,-36)); end if;
if b_ngayC=0 then b_ngayC:=30000101; end if;
if trim(b_cmt||b_mobi||b_email) is not null then
b_ma_kh:=FBH_DTAC_MAt('',b_cmt,b_mobi,b_email);
if b_ma_kh is null then b_loi:='loi:Khong tim duoc khach hang:loi'; raise PROGRAM_ERROR; end if;
for r_lp in (select distinct so_id_d from bh_xe where ma_dvi=b_ma_dvi and ma_kh=b_ma_kh and
ngay_ht between b_ngayD and b_ngayC and nsd=b_nsd and b_ttrang in (' ',ttrang)) loop
b_so_idC:=FBH_XE_SO_IDc(b_ma_dvi,r_lp.so_id_d);
select max(so_hd) into b_so_hd from bh_xe where ma_dvi=b_ma_dvi and so_id=b_so_idC;
insert into ket_qua(c1,c2,c3,c4,n1,n2,n3,n10,c10)
select b_so_hd,FBH_XE_TTRANG(b_ma_dvi,b_so_idC),ten,cmt,ngay_hl,ngay_kt,ngay_cap,b_so_idC,nv
from bh_xe where ma_dvi=b_ma_dvi and so_id=b_so_idC;
end loop;
elsif b_ngayD between b_ngay and 30000101 then
for r_lp in (select distinct so_id_d from bh_xe where ma_dvi=b_ma_dvi and
ngay_ht between b_ngayD and b_ngayC and nsd=b_nsd and b_ttrang in (' ',ttrang)) loop
b_so_idC:=FBH_XE_SO_IDc(b_ma_dvi,r_lp.so_id_d);
select max(so_hd) into b_so_hd from bh_xe where ma_dvi=b_ma_dvi and so_id=b_so_idC;
insert into ket_qua(c1,c2,c3,c4,n1,n2,n3,n10,c10)
select b_so_hd,FBH_XE_TTRANG(b_ma_dvi,b_so_idC),ten,cmt,ngay_hl,ngay_kt,ngay_cap,b_so_idC,nv
from bh_xe where ma_dvi=b_ma_dvi and so_id=b_so_idC;
end loop;
end if;
select count(*) into b_dong from ket_qua;
select JSON_ARRAYAGG(json_object('so_hd' value c1,'ttrang' value c2,'ten' value c3,'cmt' value c4,
'ngay_hl' value n1,'ngay_kt' value n2,'ngay_cap' value n3,
'ma_dvi' value b_ma_dvi,'so_id' value n10,'nv' value c10 returning clob)
order by c1 desc returning clob) into cs_lke from ket_qua;
select json_object('dong' value b_dong,'cs_lke' value cs_lke returning clob) into b_oraOut from dual;
delete ket_qua; commit;
exception when others then if b_loi is null then raise PROGRAM_ERROR; else raise_application_error(-20105,b_loi); end if;
end;