AAA

create or replace procedure PBH_HANG_MA_CANG_CT
    (b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_oraIn clob,b_oraOut out clob)
AS
    b_loi varchar2(100); b_kq clob; b_ma varchar2(10):=FKH_JS_GTRIs(b_oraIn,'ma');
begin
-- Nam
b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'BH','MA','');
if b_loi is not null then raise PROGRAM_ERROR; end if;
select json_object(ma,ten,'nuoc' value FBH_MA_NUOC_TENl(nuoc),txt returning clob) into b_kq from bh_hang_cang where ma=b_ma;
select json_object('cs_ct' value b_kq) 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;
/
create or replace procedure PBH_HANG_MA_CANG_NH
    (b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_oraIn clob)
AS
    b_loi varchar2(100); b_lenh varchar2(1000);
    b_ma varchar2(10); b_ten nvarchar2(500); b_nuoc varchar(200);
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,nuoc');
EXECUTE IMMEDIATE b_lenh into b_ma,b_ten,b_nuoc using b_oraIn;
b_nuoc:=PKH_MA_TENl(b_nuoc);
if trim(b_ma) is null then b_loi:='loi:Nhap ma:loi'; raise PROGRAM_ERROR; end if;
if trim(b_ten) is null then b_loi:='loi:Nhap ten:loi'; raise PROGRAM_ERROR; end if;
b_loi:='';
delete bh_hang_cang where ma=b_ma;
insert into bh_hang_cang values(b_ma_dvi,b_ma,b_ten,b_nuoc,b_nsd,b_oraIn);
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_HANG_CANG(
    b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_oraIn clob,b_oraOut out clob)
AS
    b_loi varchar2(100); b_noi_di varchar2(20); cs_lke clob:='';
begin
-- Nam
b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'BH','HANG','');
if b_loi is not null then raise PROGRAM_ERROR; end if;
b_noi_di:=trim(b_oraIn);
if b_noi_di is not null then
    select JSON_ARRAYAGG(json_object(ma,'ten' value ma) order by ma returning clob) into cs_lke from bh_hang_cang where nuoc=b_noi_di;
end if;
select json_object('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;
/
create or replace function FBH_HANG_CANG_TENl(b_ma varchar2) return nvarchar2
AS
    b_kq nvarchar2(500);
begin
-- Nam
select min(ten) into b_kq from bh_hang_cang where ma=b_ma;
return b_kq;
end;
/
create or replace procedure PBH_HANG_NOI (
    b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_oraIn clob,b_oraOut out clob)
AS
    b_loi varchar2(100); b_ten varchar2(1000); b_ma varchar2(20):=b_oraIn;
begin
-- Nam
b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'BH','MA','');
if b_loi is not null then raise PROGRAM_ERROR; end if;
if trim(b_ma) is not null then b_ten:=FBH_HANG_CANG_TENl(b_ma); end if;
select json_object('b_noi' value b_ten) 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;
/
create or replace function FBH_MA_NUOC_TENl(b_ma varchar2) return nvarchar2
AS
    b_kq nvarchar2(500);
begin
-- Nam
select min(ma||'|'||ten) into b_kq from bh_ma_nuoc where ma=b_ma;
return b_kq;
end;
/
create or replace procedure PBH_HANG_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);
    b_pt varchar2(500); b_nhom varchar2(500); b_qtac varchar2(500);
    b_tu number; b_den number; b_dong number; cs_lke clob;
begin
-- Dan
b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'BH','HANG','');
if b_loi is not null then raise PROGRAM_ERROR; end if;
b_lenh:=FKH_JS_LENH('pt,nhang,qtac,tu,den');
EXECUTE IMMEDIATE b_lenh into b_pt,b_nhom,b_qtac,b_tu,b_den using b_oraIn;
b_pt:=PKH_MA_TENl(b_pt);
b_nhom:=PKH_MA_TENl(b_nhom); b_qtac:=PKH_MA_TENl(b_qtac);
b_pt:=nvl(trim(b_pt),' '); b_nhom:=nvl(trim(b_nhom),' '); b_qtac:=nvl(trim(b_qtac),' ');
select count(*) into b_dong from bh_hang_phi where b_nhom in (' ',nhang) and b_pt in (' ',pt) and b_qtac in (' ',qtac);
PKH_LKE_TRANG(b_dong,b_tu,b_den);
select JSON_ARRAYAGG(json_object(so_id,'nhang' value FBH_HANG_NHANG_TEN(nhang),
       'pt' value FBH_HANG_PT_TEN(pt),'qtac' value FBH_HANG_QTAC_TEN(qtac),khoang_cach,thoi_gian) returning clob) into cs_lke from
      (select so_id,nhang,pt,qtac,khoang_cach,thoi_gian,rownum sott from bh_hang_phi
      where b_nhom in(' ',nhang) and b_pt in(' ',pt) and b_qtac in(' ',qtac)
      order by nhang,pt,qtac)
      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;
/
create or replace procedure PBH_HANG_MA_GD (
    b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_oraIn clob,b_oraOut out clob)
AS
    b_loi varchar2(2000); bil number;  b_ma varchar2(20):=FKH_JS_GTRIs(b_oraIn,'ma');
    cs_ct clob:='';
begin
-- Nam - Tra ttin ma
b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'BH','','');
if b_loi is not null then raise PROGRAM_ERROR; end if;
b_ma:=PKH_MA_TENL(b_ma);
select count(*) into bil from bh_ma_gdinh where ma=b_ma;
if bil<>0 then
  select min(ma||'|'||ten) into cs_ct from bh_ma_gdinh where ma=b_ma;
  end if;
select json_object('cs_ct' value cs_ct) 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;