AAA

drop procedure PBH_2B_BPHI_LKE;
create or replace procedure PBH_2B_BPHI_LKE(
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(1000); cs_lke clob; b_dong number;
b_ma_sp varchar2(10); b_nhom varchar2(10); b_nv_bh varchar2(10); b_md_sd varchar2(10);
b_tu number; b_den number;
begin
-- Dan
b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'BH','2B','');
if b_loi is not null then raise PROGRAM_ERROR; end if;
b_lenh:=FKH_JS_LENH('ma_sp,nhom,nv_bh,md_sd,tu,den');
EXECUTE IMMEDIATE b_lenh into b_ma_sp,b_nhom,b_nv_bh,b_md_sd,b_tu,b_den using b_oraIn;
b_ma_sp:=nvl(trim(b_ma_sp),' '); b_nhom:=nvl(trim(b_nhom),' ');
b_nv_bh:=nvl(trim(b_nv_bh),' '); b_md_sd:=nvl(trim(b_md_sd),' ');
select count(*) into b_dong from bh_2b_phi;
PKH_LKE_TRANG(b_dong,b_tu,b_den);
--ma_sp,cdich,goi,md_sd,nhom,nv_bh,bh_tbo,loai_xe,nhom_xe,dong,dco,ttai,so_cn,tuoi,gia,ngay_bd,ngay_kt,so_id
select JSON_ARRAYAGG(json_object(
'ma_sp' value FBH_2B_SP_TEN(ma_sp),'cdich' value FBH_MA_CDICH_TEN(cdich),'goi' value FBH_2B_GOI_TEN(goi),
nhom,nv_bh,bh_tbo,'md_sd' value FBH_2B_MDSD_TEN(md_sd),
'loai_xe' value FBH_2B_LOAI_TEN(loai_xe),'nhom_xe' value FBH_2B_NHOM_TEN(nhom_xe),
'dong' value FBH_2B_DONG_TEN(dong),dco,tuoi,gia,ngay_bd,ngay_kt,so_id returning clob)
order by ma_sp,cdich,goi,nhom,nv_bh,bh_tbo,md_sd,loai_xe,nhom_xe,dong,dco,
tuoi,gia returning clob) into cs_lke from
(select ma_sp,cdich,goi,nhom,nv_bh,bh_tbo,md_sd,loai_xe,nhom_xe,dong,
dco,tuoi,gia,ngay_bd,ngay_kt,so_id,rownum sott from bh_2b_phi
where b_ma_sp in(' ',ma_sp) and b_md_sd in(' ',md_sd) and b_nhom in(' ',nhom) and b_nv_bh in(' ',nv_bh)
order by ma_sp,cdich,goi,nhom,nv_bh,bh_tbo,md_sd,loai_xe,nhom_xe,dong,dco,tuoi,gia)
where sott between b_tu and b_den;
select json_object('dong' value b_dong,'cs_lke' value cs_lke returning clob) 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;