AAA

create or replace function FBH_DTAC_MA_CMT(b_ma varchar2) return varchar2
AS
    b_kq varchar2(20):=' ';
begin
-- Dan
select nvl(min(cmt),' ') into b_kq from bh_dtac_ma where ma=b_ma;
return b_kq;
exception when others then return ' ';
end;
/
create or replace procedure PBH_HD_TIMJ(
    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(2000); cs_lke clob:=''; b_tu number; b_den number;
    b_nv varchar2(5); b_ma_kh varchar2(20);
    b_cmt varchar2(20); b_mobi varchar2(20); b_email varchar2(100);
    b_dong number; b_ngay number:=PKH_NG_CSO(add_months(sysdate,-36));
    b_ngayD number; b_ngayC number; b_phong varchar2(10);
begin
-- Dan - Tim hop dong qua CMT, mobi, email
delete temp_1; commit;
b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'BH','HD','X');
if b_loi is not null then raise PROGRAM_ERROR; end if;
b_lenh:=FKH_JS_LENH('nv,ngayd,ngayc,cmt,mobi,email,tu,den');
EXECUTE IMMEDIATE b_lenh into b_nv,b_ngayD,b_ngayC,b_cmt,b_mobi,b_email,b_tu,b_den using b_oraIn;
if b_ngayC=0 then b_ngayC:=30000101; end if;
if b_ngayD in (0,30000101) then b_ngayD:=PKH_NG_CSO(add_months(sysdate,-36)); end if;
b_phong:=FHT_MA_NSD_PHONG(b_ma_dvi,b_nsd);
b_nv:=nvl(trim(b_nv),'*');
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;   
    select count(*) into b_dong from bh_hd_goc where ma_kh=b_ma_kh and ngay_ht between b_ngayD and b_ngayC and phong=b_phong and so_id_g=0 and b_nv in('*',nv);
    if b_dong<>0 then
        PKH_LKE_TRANG(b_dong,b_tu,b_den);
        insert into temp_1(c1,c2,c3,c4,c5,c6,n1,n2,n3,n4) select nv,so_hd,ttrang,ten,ma_dvi,FBH_DTAC_MA_CMT(ma_kh),so_id,
               ngay_hl,ngay_kt,ngay_ht from (select nv,so_hd,ttrang,ten,ma_dvi,so_id,ma_kh,ngay_hl,ngay_kt,ngay_ht,rownum sott 
                    from bh_hd_goc where
            ma_kh=b_ma_kh and ngay_ht between b_ngayD and b_ngayC and phong=b_phong and so_id_g=0 and b_nv in('*',nv) order by ngay_ht desc,nv,so_hd)
            where sott between b_tu and b_den;
    end if;
elsif b_ngayD between b_ngay and 30000101 then
    select count(*) into b_dong from bh_hd_goc where ma_dvi=b_ma_dvi and ngay_ht between b_ngayD and b_ngayC and phong=b_phong and so_id_g=0 and b_nv in('*',nv);
    if b_dong<>0 then
        PKH_LKE_TRANG(b_dong,b_tu,b_den);
        insert into temp_1(c1,c2,c3,c4,c5,c6,n1,n2,n3,n4) select nv,so_hd,ttrang,ten,ma_dvi,FBH_DTAC_MA_CMT(ma_kh),so_id,
               ngay_hl,ngay_kt,ngay_ht from (select nv,so_hd,ttrang,ten,ma_dvi,so_id,ma_kh,ngay_hl,ngay_kt,ngay_ht,rownum sott 
                    from bh_hd_goc where
            ma_dvi=b_ma_dvi and ngay_ht between b_ngayD and b_ngayC and phong=b_phong and so_id_g=0 and b_nv in('*',nv) order by ngay_ht desc,nv,so_hd)
            where sott between b_tu and b_den;
    end if;
end if;
select JSON_ARRAYAGG(json_object('nv' value c1,'so_hd' value c2,'ttrang' value c3,'ten' value c4,'ma_dvi' value c5,
       'cmt' value c6,'so_id' value n1,'ngay_hl' value n2,'ngay_kt' value n3,'ngay_ht' value n4) 
       order by n5 desc,c1,c2 returning clob) into cs_lke from temp_1;
select json_object('dong' value b_dong,'cs_lke' value cs_lke returning clob) into b_oraOut from dual;
delete temp_1; commit;
exception when others then if b_loi is null then raise PROGRAM_ERROR; else raise_application_error(-20105,b_loi); end if;
end;