AAA

create or replace procedure PBH_HD_DU_GOC(
    b_ma_dviN varchar2,b_nsdN varchar2,b_pas varchar2,b_oraIn clob,b_oraOut out clob)
AS
    b_loi varchar2(100); b_lenh varchar2(2000); b_i1 number;
    b_ma_dvi varchar2(10); b_so_id number; b_nv varchar2(10); b_nvG varchar2(10);
Begin
b_loi:=FHT_MA_NSD_KTRA(b_ma_dviN,b_nsdN,b_pas,'BH','','');
if b_loi is not null then raise PROGRAM_ERROR; end if;
b_loi:='loi:Loi xu ly PBH_HD_DU_GOC:loi';
b_lenh:=FKH_JS_LENH('ma_dvi,so_id');
EXECUTE IMMEDIATE b_lenh into b_ma_dvi,b_so_id using b_oraIn;
-- viet anh
b_nv:=FBH_HD_NV(b_ma_dvi,b_so_id);
if b_nv is null then b_loi:='loi:Hop dong da xoa:loi'; raise PROGRAM_ERROR; end if;
if b_nv<>'HANG' then
  if b_nv<>'NG' then
    b_lenh:='select nv from '||FBH_HD_GOC_BANG(b_nv)||' where ma_dvi= :ma_dvi and so_id= :so_id';
  else
    b_lenh:='select nv||nhom from '||FBH_HD_GOC_BANG(b_nv)||' where ma_dvi= :ma_dvi and so_id= :so_id';
  end if;
  EXECUTE IMMEDIATE b_lenh into b_nvG using b_ma_dvi,b_so_id;
  if b_nv<>'NG' then b_nv:=b_nv||b_nvG; end if;
end if;
select json_object('nv' value b_nv) 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_MA_QTAC_LISTt(b_nv varchar2,b_nhom varchar2,b_lay_all varchar2)
AS
    b_ngay number:=PKH_NG_CSO(sysdate);
begin
  -- viet anh
insert into temp_1(c1,c2,c3)
  select '1',ma,ten from bh_ma_qtac where ngay_kt>b_ngay and FBH_MA_NV_CO(nv,b_nv)='C';
end;
/
/* ma nguyen nhan */
create or replace procedure PBH_BT_MA_NN_LISTt(b_nv varchar2,b_nhom varchar2,b_lay_all varchar2)
AS
    b_ngay number:=PKH_NG_CSO(sysdate);
begin
  -- viet anh
insert into temp_1(c1,c2,c3)
  select '1',ma,ten from bh_ma_nntt a where tc='C' and ngay_kt>b_ngay and FBH_MA_NV_CO(nv,b_nv)='C';
end;

/* DR_LKE theo nv */
create or replace procedure PBH_MA_DMUC_HOI_LIST(
    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_gtri nvarchar2(500); b_nv varchar2(10);
    b_nhom varchar2(1);  b_proc_name varchar2(100); b_lay_all varchar2(1):='K';
    b_kieu varchar2(1); b_dong number; b_tu number:=1; cs_lke clob;
begin
-- viet anh
delete temp_1; commit;
b_lenh:=FKH_JS_LENH('kieu,gtri,nv,nhom,proc_name,lay_all');
EXECUTE IMMEDIATE b_lenh into b_kieu,b_gtri,b_nv,b_nhom,b_proc_name,b_lay_all using b_oraIn;
-- truyen prod dong
b_lenh := 'BEGIN ' || b_proc_name || '(:nv, :nhom, :lay_all); END;';
EXECUTE IMMEDIATE b_lenh using b_nv, b_nhom, b_lay_all;
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;
if b_kieu<>'C' then
    PBH_MA_DMUC_LIST(b_oraIn,b_dong,cs_lke,b_loi);
elsif trim(b_gtri) is not null then
    PBH_MA_DMUC_LIST_MA(b_oraIn,b_dong,cs_lke,b_loi);
else
    PBH_MA_DMUC_LIST_SL(b_oraIn,b_tu,b_dong,cs_lke,b_loi);
end if;
if b_loi is not null then raise PROGRAM_ERROR; end if;
select json_object('kieu' value b_kieu,'dong' value b_dong,'tu' value b_tu,'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;
/
create or replace procedure PBH_MA_DMUC_LIST(
    b_oraIn clob,b_dong out number,cs1 out clob,b_loi out varchar2)
AS
    b_lenh varchar2(1000); b_gtri nvarchar2(500); b_ten nvarchar2(100); b_min nvarchar2(100);
    b_trangKt number; b_nv varchar2(10); b_ngay number:=PKH_NG_CSO(sysdate);
begin
-- viet anh
cs1:='';
b_lenh:=FKH_JS_LENH('gtri,trangKt,nv');
EXECUTE IMMEDIATE b_lenh into b_gtri,b_trangKt,b_nv using b_oraIn;
if trim(b_gtri) is null then
    select count(*) into b_dong from temp_1;
    if b_dong>1 and b_dong<=b_trangKt then
        select JSON_ARRAYAGG(json_object(c2,c3) order by c1,c2 returning clob) into cs1 from temp_1;
    end if;
else
    b_ten:='%'||b_gtri||'%'; b_gtri:=upper(b_gtri);
    select count(*),min(c3) into b_dong,b_min from temp_1 where c2>=b_gtri or upper(c3) like b_ten;
    if b_dong<=b_trangKt and (b_dong>1 or upper(b_min)<>b_gtri) then
        select JSON_ARRAYAGG(json_object(c2,c3) order by c1,c2 returning clob) into cs1 from temp_1 where c2>=b_gtri or upper(c3) like b_ten;
    end if;
end if;
end;
/
create or replace procedure PBH_MA_DMUC_LIST_MA(
    b_oraIn clob,b_dong out number,cs1 out clob,b_loi out varchar2)
AS
    b_lenh varchar2(1000); a_ch pht_type.a_var; b_tu number:=1; b_den number; b_trang number;
    b_gtri nvarchar2(500); b_ten nvarchar2(500); b_trangkt number;
begin
-- viet anh - Liet ke dong tu, den
b_lenh:=FKH_JS_LENH('gtri,trangKt');
EXECUTE IMMEDIATE b_lenh into b_gtri,b_trangKt using b_oraIn;
if b_gtri is null then b_loi:='loi:PBH_MA_SP_LIST_MA:loi'; return; end if;
b_ten:=upper(unistr('\0025')||b_gtri||'%');
select count(*) into b_dong from temp_1 where c2>=b_gtri or upper(c3) like b_ten;
if b_dong>b_trangKt then b_den:=b_trangKt; else b_den:=b_dong; end if;
select JSON_ARRAYAGG(json_object(ma,ten) order by xep,ma returning clob) into cs1 from
    (select c1 xep,c2 ma,c3 ten,rownum sott from temp_1 where c2>=b_gtri or upper(c3) like b_ten order by c1,c2)
    where sott between b_tu and b_den;
end;
/
create or replace procedure PBH_MA_DMUC_LIST_SL(
    b_oraIn clob,b_tu out number,b_dong out number,cs1 out clob,b_loi out varchar2)
AS
    b_lenh varchar2(1000); b_den number;
begin
-- viet anh - Liet ke dong tu, den
b_lenh:=FKH_JS_LENH('tu,den');
EXECUTE IMMEDIATE b_lenh into b_tu,b_den using b_oraIn;
select count(*) into b_dong from temp_1;
PKH_LKE_TRANG(b_dong,b_tu,b_den);
select JSON_ARRAYAGG(json_object(ma,ten) returning clob) into cs1 from
    (select c2 ma,c3 ten,rownum sott from temp_1 order by c1,c2)
    where sott between b_tu and b_den;
end;
/

create or replace procedure PBH_MA_TPA_NGSK_LISTt(b_nv varchar2,b_nhom varchar2,b_lay_all varchar2)
AS
    b_ngay number:=PKH_NG_CSO(sysdate);
begin
  -- viet anh
insert into temp_1(c1,c2,c3)
  select '1',ma,ten from bh_ma_gdinh where FBH_MA_GDINH_NV(ma,'NG')='C' and FBH_MA_GDINH_HAN(ma)='C';
end;
/
create or replace procedure PBH_MA_SP_NGTD_LISTt(b_nv varchar2,b_nhom varchar2,b_lay_all varchar2)
AS
    b_ngay number:=PKH_NG_CSO(sysdate);
begin
  -- viet anh
insert into temp_1(c1,c2,c3) select '1',ma,ten from bh_ngtd_sp where FBH_NGTD_SP_HAN(ma)='C';
end;
/
create or replace procedure PBH_MA_GOI_NGTD_LISTt(b_nv varchar2,b_nhom varchar2,b_lay_all varchar2)
AS
    b_ngay number:=PKH_NG_CSO(sysdate);
begin
  -- viet anh
insert into temp_1(c1,c2,c3) select '1',ma,ten from bh_ngtd_goi where FBH_NGTD_GOI_HAN(ma)='C';
end;
/
create or replace procedure PBH_MA_SP_NGSK_LISTt(b_nv varchar2,b_nhom varchar2,b_lay_all varchar2)
AS
    b_ngay number:=PKH_NG_CSO(sysdate);
begin
  -- viet anh
if (b_lay_all='C') then
  insert into temp_1(c1,c2,c3) select '1',ma,ten from bh_sk_sp where FBH_SK_SP_HAN(ma)='C';
else
  insert into temp_1(c1,c2,c3)
    select '1',ma,ten from bh_sk_sp a,(select distinct ma_sp from bh_sk_phi where nhom=b_nhom and ngay_bd<=b_ngay and ngay_kt>b_ngay) b
      where a.ma=b.ma_sp and FBH_SK_SP_HAN(a.ma)='C';
end if;
end;
/
create or replace procedure PBH_MA_GOI_NGSK_LISTt(b_nv varchar2,b_nhom varchar2,b_lay_all varchar2)
AS
    b_ngay number:=PKH_NG_CSO(sysdate);
begin
  -- viet anh
if b_lay_all='C' then
  insert into temp_1(c1,c2,c3) select '1',ma,ten from bh_sk_goi where FBH_SK_GOI_HAN(ma)='C';
else
  insert into temp_1(c1,c2,c3) select '1',ma,ten from
           bh_sk_goi a,(select distinct goi from bh_sk_phi where nhom=b_nhom and ngay_bd<=b_ngay and ngay_kt>b_ngay) b
      where a.ma=b.goi and FBH_SK_GOI_HAN(a.ma)='C';
end if;
end;
/
create or replace procedure PBH_MA_SP_NGDL_LISTt(b_nv varchar2,b_nhom varchar2,b_lay_all varchar2)
AS
    b_ngay number:=PKH_NG_CSO(sysdate);
begin
  -- viet anh
if b_lay_all='C' then
  insert into temp_1(c1,c2,c3) select '1',ma,ten from bh_ngdl_sp where FBH_NGDL_SP_HAN(ma)='C';
else
  insert into temp_1(c1,c2,c3) select '1',ma,ten from
      bh_ngdl_sp a,(select distinct ma_sp from bh_ngdl_phi where nhom=b_nhom and ngay_bd<=b_ngay and ngay_kt>b_ngay) b
      where a.ma=b.ma_sp and FBH_NGDL_SP_HAN(a.ma)='C';
end if;
end;
/
create or replace procedure PBH_MA_GOI_NGDL_LISTt(b_nv varchar2,b_nhom varchar2,b_lay_all varchar2)
AS
    b_ngay number:=PKH_NG_CSO(sysdate);
begin
  -- viet anh
if b_lay_all='C' then 
  insert into temp_1(c1,c2,c3) select '1',ma,ten from bh_ngdl_goi where FBH_NGDL_GOI_HAN(ma)='C';
else
  insert into temp_1(c1,c2,c3) select '1',ma,ten from
      bh_ngdl_goi a,(select distinct goi from bh_ngdl_phi where nhom=b_nhom and FBH_NGDL_SP_HAN(ma_sp)='C' and b_ngay between ngay_bd and ngay_kt) b
      where a.ma=b.goi;
end if;
end;