lấy danh mục phạm vi
User documentation
21/05/2025
create or replace procedure PBH_PHH_BPHId_THEM(
b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_oraIn clob,b_oraOut out clob)
AS
b_loi varchar2(1000); b_lenh varchar2(1000); b_i1 number;
b_nhom varchar2(1); b_ma_sp varchar2(10); b_cdich varchar2(10); b_goi varchar2(10);
b_mrr varchar2(500); b_ngay_hl number; b_so_id number; b_vu varchar2(10);
b_dk_lt clob; a_ma_lt pht_type.a_var; a_ma_dk_lt pht_type.a_var; a_ten_lt pht_type.a_nvar; a_chon_lt pht_type.a_var;
b_dt_pvi clob; a_ma_pvi pht_type.a_var; a_ten_pvi pht_type.a_nvar; a_chon_pvi pht_type.a_var;
a_ptts pht_type.a_num;a_ptkh pht_type.a_num;a_ktru pht_type.a_var;a_tc pht_type.a_var;a_loai pht_type.a_var;
a_ma_ct pht_type.a_var;a_ma_dk pht_type.a_var;
begin
-- Dan - Tra so_id bieu phi theo dieu kien
delete temp_1; commit;
b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'BH','PHH','');
if b_loi is not null then raise PROGRAM_ERROR; end if;
b_lenh:=FKH_JS_LENH('nhom,ma_sp,cdich,goi,mrr,ngay_hl,vu');
EXECUTE IMMEDIATE b_lenh into b_nhom,b_ma_sp,b_cdich,b_goi,b_mrr,b_ngay_hl,b_vu using b_oraIn;
b_cdich:=nvl(trim(b_cdich),' '); b_goi:=nvl(trim(b_goi),' '); b_mrr:=nvl(PKH_MA_TENl(b_mrr),' ');
b_so_id:=FBH_PHH_BPHId_SO_ID(b_nhom,b_ma_sp,b_cdich,b_goi,b_mrr,b_ngay_hl);
b_oraOut:='';
if b_vu='dkbs' then
insert into temp_1(c1,c2,n1,c3,c4) select ma,ten,pt,lbh,ma_dkc from bh_phh_phi_dk where so_id=b_so_id and nv='M';
for r_lp in (select ma,ten,ma_dk from bh_ma_dkbs where FBH_MA_NV_CO(nv,'PHH')='C') loop
select count(*) into b_i1 from temp_1 where c1=r_lp.ma;
if b_i1=0 then insert into temp_1(c1,c2,c3,n1,c4) values(r_lp.ma,r_lp.ten,' ',0,r_lp.ma_dk); end if;
end loop;
select JSON_ARRAYAGG(json_object('ma' value c1,'ten' value c2,'ptB' value n1,'pt' value 0,'lbh' value c3,'ma_dkc' value c4)
order by c1,c2 returning clob) into b_oraOut from temp_1;
elsif b_so_id<>0 then
if b_vu='dk' then
select JSON_ARRAYAGG(json_object(ma,ten,cap,ma_ct,lkeM,'ptB' value pt,'pt' value '') order by bt returning clob)
into b_oraOut from bh_phh_phi_dk where so_id=b_so_id and nv<>'M' order by bt;
elsif b_vu='pvi' then
select count(*) into b_i1 from bh_phh_phi_txt where so_id=b_so_id and loai='dt_pvi';
if b_i1 >0 then
select txt into b_dt_pvi from bh_phh_phi_txt where so_id=b_so_id and loai='dt_pvi';
b_lenh:=FKH_JS_LENH('ma,ma_dk,ten,chon,ptts,ptkh,ktru,tc,loai,ma_ct');
EXECUTE IMMEDIATE b_lenh bulk collect into a_ma_pvi,a_ma_dk,a_ten_pvi,a_chon_pvi,a_ptts,
a_ptkh,a_ktru,a_tc,a_loai,a_ma_ct using b_dt_pvi;
if a_ma_pvi.count > 0 then
for b_i1 in 1..a_ma_pvi.count loop
insert into temp_1(c1,c2,c3,c4,c5,c6,c7,c8,c9,c10) VALUES (a_ma_pvi(b_i1),a_ma_dk(b_i1),
a_ten_pvi(b_i1),a_chon_pvi(b_i1),a_ptts(b_i1),a_ptkh(b_i1),a_ktru(b_i1),
a_tc(b_i1),a_loai(b_i1),a_ma_ct(b_i1));
end loop;
end if;
end if;
for r_lp in (select ma,ten,ma_dk from bh_phh_pvi) loop
select count(*) into b_i1 from temp_1 where c1=r_lp.ma;
if b_i1=0 then insert into temp_1(c1,c2,c3,c4) values(r_lp.ma,r_lp.ten,' ',r_lp.ma_dk); end if;
end loop;
select JSON_ARRAYAGG(json_object('ma' value c1,'ten' value c2,'ma_dk' value c3,'ptTSB' value c5,'ptTS' value '',
'ptKHB' value c6,'ptKH' value '')
order by c1,c2 returning clob) into b_oraOut from temp_1;
--select JSON_ARRAYAGG(json_object(ma,ten,'ptTSB' value ptTS,'ptTS' value '',
--'ptKHB' value ptKH,'ptKH' value '',ktru,tc,loai,ma_ct) order by bt returning clob)
--into b_oraOut from bh_phh_phiP_dk where so_id=b_so_id order by bt;
elsif b_vu='lt' then
select count(*) into b_i1 from bh_phh_phi_txt where so_id=b_so_id and loai='dt_lt';
if b_i1 >0 then
select txt into b_dk_lt from bh_phh_phi_txt where so_id=b_so_id and loai='dt_lt';
b_lenh:=FKH_JS_LENH('ma_lt,ma_dk,ten,chon');
EXECUTE IMMEDIATE b_lenh bulk collect into a_ma_lt,a_ma_dk_lt,a_ten_lt,a_chon_lt using b_dk_lt;
if a_ma_lt.count > 0 then
for b_i1 in 1..a_ma_lt.count loop
insert into temp_1(c1,c2,c3,c4) VALUES (a_ma_lt(b_i1),a_ma_dk_lt(b_i1),a_ten_lt(b_i1),a_chon_lt(b_i1));
end loop;
end if;
end if;
for r_lp in (select ma,ten,ma_dk from bh_ma_dklt where FBH_MA_NV_CO(nv,'PHH')='C') loop
select count(*) into b_i1 from temp_1 where c1=r_lp.ma;
if b_i1=0 then insert into temp_1(c1,c2,c3,c4) values(r_lp.ma,r_lp.ten,' ',r_lp.ma_dk); end if;
end loop;
select JSON_ARRAYAGG(json_object('ma_lt' value c1,'ten' value c2,'ma_dk' value c3)
order by c1,c2 returning clob) into b_oraOut from temp_1;
end if;
end if;
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_PKT_BPHI_THEM(
b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_oraIn clob,b_oraOut out clob)
AS
b_loi varchar2(1000); b_lenh varchar2(1000); b_i1 number;
b_nhom varchar2(1); b_ma_sp varchar2(10); b_ma_nct varchar2(500);
b_rru varchar2(500); b_ma_ntb varchar2(500);b_ngay_hl number; b_so_id number; b_vu varchar2(10);
b_dk_lt clob; a_ma_lt pht_type.a_var; a_ma_dk_lt pht_type.a_var; a_ten_lt pht_type.a_nvar; a_chon_lt pht_type.a_var;
b_dt_pvi clob;a_ma_pvi pht_type.a_var;a_ten_pvi pht_type.a_var;a_ptts pht_type.a_num;a_ptkh pht_type.a_num;a_chon_pvi pht_type.a_var;
begin
-- Dan - Tra so_id bieu phi theo dieu kien
delete temp_1; commit;
b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'BH','PKT','');
if b_loi is not null then raise PROGRAM_ERROR; end if;
b_lenh:=FKH_JS_LENH('nhom,ma_sp,ma_nct,rru,ma_ntb,ngay_hl,vu');
EXECUTE IMMEDIATE b_lenh into b_nhom,b_ma_sp,b_ma_nct,b_rru,b_ma_ntb,b_ngay_hl,b_vu using b_oraIn;
b_ma_nct:=nvl(PKH_MA_TENl(b_ma_nct),' '); b_ma_ntb:=nvl(PKH_MA_TENl(b_ma_ntb),' '); b_rru:=nvl(PKH_MA_TENl(b_rru),' ');
b_so_id:=FBH_PKT_BPHI_SO_ID(b_nhom,b_ma_sp,b_ma_nct,b_rru,b_ma_ntb,b_ngay_hl);
b_oraOut:='';
if b_vu='dkbs' then
insert into temp_1(c1,c2,n1,c3,c4) select ma,ten,pt,lbh,ma_dkc from bh_pkt_phi_dk where so_id=b_so_id and nv='M';
for r_lp in (select ma,ten,ma_dk from bh_ma_dkbs where FBH_MA_NV_CO(nv,'PKT')='C') loop
select count(*) into b_i1 from temp_1 where c1=r_lp.ma;
if b_i1=0 then insert into temp_1(c1,c2,c3,n1,c4) values(r_lp.ma,r_lp.ten,' ',0,r_lp.ma_dk); end if;
end loop;
select JSON_ARRAYAGG(json_object('ma' value c1,'ten' value c2,'ptB' value n1,'pt' value 0,'lbh' value c3,'ma_dkc' value c4)
order by c1,c2 returning clob) into b_oraOut from temp_1;
elsif b_so_id<>0 then
if b_vu='dk' then
select JSON_ARRAYAGG(json_object(ma,ten,cap,ma_ct,lkeM,'ptB' value pt,'pt' value '') order by bt returning clob)
into b_oraOut from bh_pkt_phi_dk where so_id=b_so_id and nv<>'M' order by bt;
elsif b_vu='pvi' then
select count(*) into b_i1 from bh_pkt_phi_txt where so_id=b_so_id and loai='dt_pvi';
if b_i1 >0 then
select txt into b_dt_pvi from bh_pkt_phi_txt where so_id=b_so_id and loai='dt_pvi';
b_lenh:=FKH_JS_LENH('ma,ten,ptts,ptkh,chon');
EXECUTE IMMEDIATE b_lenh bulk collect into a_ma_pvi,a_ten_pvi,a_ptts,a_ptkh,a_chon_pvi using b_dt_pvi;
if a_ma_pvi.count > 0 then
for b_i1 in 1..a_ma_pvi.count loop
insert into temp_1(c1,c2,c3,c4,c5) VALUES (a_ma_pvi(b_i1),a_ten_pvi(b_i1),a_ptts(b_i1),a_ptkh(b_i1),a_chon_pvi(b_i1));
end loop;
end if;
end if;
for r_lp in (select ma,ten,tc,txt from bh_pkt_pvi) loop
select count(*) into b_i1 from temp_1 where c1=r_lp.ma;
if b_i1=0 then insert into temp_1(c1,c2,c3) values(r_lp.ma,r_lp.ten,r_lp.tc); end if;
end loop;
select JSON_ARRAYAGG(json_object('ma' value c1,'ten' value c2,'ptTSB' value c3,'ptTS' value '',
'ptKHB' value c4,'ptKH' value '')
order by c1,c2 returning clob) into b_oraOut from temp_1;
--select JSON_ARRAYAGG(json_object(ma,ten,'ptTSB' value ptTS,'ptTS' value '',
--'ptKHB' value ptKH,'ptKH' value '',ktru,tc) order by bt returning clob)
--into b_oraOut from bh_pkt_phiP_dk where so_id=b_so_id order by bt;
elsif b_vu='lt' then
select count(*) into b_i1 from bh_pkt_phi_txt where so_id=b_so_id and loai='dt_lt';
if b_i1 >0 then
select txt into b_dk_lt from bh_pkt_phi_txt where so_id=b_so_id and loai='dt_lt';
b_lenh:=FKH_JS_LENH('ma_lt,ma_dk,ten,chon');
EXECUTE IMMEDIATE b_lenh bulk collect into a_ma_lt,a_ma_dk_lt,a_ten_lt,a_chon_lt using b_dk_lt;
if a_ma_lt.count > 0 then
for b_i1 in 1..a_ma_lt.count loop
insert into temp_1(c1,c2,c3,c4) VALUES (a_ma_lt(b_i1),a_ma_dk_lt(b_i1),a_ten_lt(b_i1),a_chon_lt(b_i1));
end loop;
end if;
end if;
for r_lp in (select ma,ten,ma_dk from bh_ma_dklt where FBH_MA_NV_CO(nv,'PKT')='C') loop
select count(*) into b_i1 from temp_1 where c1=r_lp.ma;
if b_i1=0 then insert into temp_1(c1,c2,c3,c4) values(r_lp.ma,r_lp.ten,' ',r_lp.ma_dk); end if;
end loop;
select JSON_ARRAYAGG(json_object('ma_lt' value c1,'ten' value c2,'ma_dk' value c3)
order by c1,c2 returning clob) into b_oraOut from temp_1;
end if;
end if;
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_PKT_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_nhom varchar2(1); b_ma_sp varchar2(500); b_rru varchar2(1); b_ma_nct varchar2(500);
b_ma_ntb 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','PKT','');
if b_loi is not null then raise PROGRAM_ERROR; end if;
b_lenh:=FKH_JS_LENH('nhom,ma_sp,rru,ma_nct,ma_ntb,tu,den');
EXECUTE IMMEDIATE b_lenh into b_nhom,b_ma_sp,b_rru,b_ma_nct,b_ma_ntb,b_tu,b_den using b_oraIn;
b_ma_sp:=PKH_MA_TENl(b_ma_sp);
b_ma_nct:=PKH_MA_TENl(b_ma_nct);
b_ma_ntb:=PKH_MA_TENl(b_ma_ntb);
select count(*) into b_dong from bh_pkt_phi where b_nhom in (' ',nhom) and b_ma_sp in (' ',ma_sp) and b_rru in (' ',rru)
and b_ma_nct in (' ',ma_nct) and b_ma_ntb in (' ',ma_ntb);
PKH_LKE_TRANG(b_dong,b_tu,b_den);
select JSON_ARRAYAGG(json_object(nhom,'ma_sp' value FBH_PKT_MA_SP_TEN(ma_sp),
'ma_nct' value FBH_PKT_MA_NCT_TEN(ma_nct),'ma_ntb' value FBH_PKT_MA_NTB_TEN(b_ma_ntb),rru,nsd,so_id)
order by nhom,ma_sp,ma_nct,ma_ntb,rru returning clob) into cs_lke from
(select nhom,ma_sp,ma_nct,ma_ntb,rru,nsd,so_id,rownum sott
from bh_pkt_phi where b_nhom in (' ',nhom) and b_ma_sp in (' ',ma_sp) and b_rru in (' ',rru) and b_ma_nct in (' ',ma_nct) and b_ma_ntb in (' ',ma_ntb)
order by nhom,ma_sp,ma_nct,ma_ntb,rru)
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;
/
b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_oraIn clob,b_oraOut out clob)
AS
b_loi varchar2(1000); b_lenh varchar2(1000); b_i1 number;
b_nhom varchar2(1); b_ma_sp varchar2(10); b_cdich varchar2(10); b_goi varchar2(10);
b_mrr varchar2(500); b_ngay_hl number; b_so_id number; b_vu varchar2(10);
b_dk_lt clob; a_ma_lt pht_type.a_var; a_ma_dk_lt pht_type.a_var; a_ten_lt pht_type.a_nvar; a_chon_lt pht_type.a_var;
b_dt_pvi clob; a_ma_pvi pht_type.a_var; a_ten_pvi pht_type.a_nvar; a_chon_pvi pht_type.a_var;
a_ptts pht_type.a_num;a_ptkh pht_type.a_num;a_ktru pht_type.a_var;a_tc pht_type.a_var;a_loai pht_type.a_var;
a_ma_ct pht_type.a_var;a_ma_dk pht_type.a_var;
begin
-- Dan - Tra so_id bieu phi theo dieu kien
delete temp_1; commit;
b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'BH','PHH','');
if b_loi is not null then raise PROGRAM_ERROR; end if;
b_lenh:=FKH_JS_LENH('nhom,ma_sp,cdich,goi,mrr,ngay_hl,vu');
EXECUTE IMMEDIATE b_lenh into b_nhom,b_ma_sp,b_cdich,b_goi,b_mrr,b_ngay_hl,b_vu using b_oraIn;
b_cdich:=nvl(trim(b_cdich),' '); b_goi:=nvl(trim(b_goi),' '); b_mrr:=nvl(PKH_MA_TENl(b_mrr),' ');
b_so_id:=FBH_PHH_BPHId_SO_ID(b_nhom,b_ma_sp,b_cdich,b_goi,b_mrr,b_ngay_hl);
b_oraOut:='';
if b_vu='dkbs' then
insert into temp_1(c1,c2,n1,c3,c4) select ma,ten,pt,lbh,ma_dkc from bh_phh_phi_dk where so_id=b_so_id and nv='M';
for r_lp in (select ma,ten,ma_dk from bh_ma_dkbs where FBH_MA_NV_CO(nv,'PHH')='C') loop
select count(*) into b_i1 from temp_1 where c1=r_lp.ma;
if b_i1=0 then insert into temp_1(c1,c2,c3,n1,c4) values(r_lp.ma,r_lp.ten,' ',0,r_lp.ma_dk); end if;
end loop;
select JSON_ARRAYAGG(json_object('ma' value c1,'ten' value c2,'ptB' value n1,'pt' value 0,'lbh' value c3,'ma_dkc' value c4)
order by c1,c2 returning clob) into b_oraOut from temp_1;
elsif b_so_id<>0 then
if b_vu='dk' then
select JSON_ARRAYAGG(json_object(ma,ten,cap,ma_ct,lkeM,'ptB' value pt,'pt' value '') order by bt returning clob)
into b_oraOut from bh_phh_phi_dk where so_id=b_so_id and nv<>'M' order by bt;
elsif b_vu='pvi' then
select count(*) into b_i1 from bh_phh_phi_txt where so_id=b_so_id and loai='dt_pvi';
if b_i1 >0 then
select txt into b_dt_pvi from bh_phh_phi_txt where so_id=b_so_id and loai='dt_pvi';
b_lenh:=FKH_JS_LENH('ma,ma_dk,ten,chon,ptts,ptkh,ktru,tc,loai,ma_ct');
EXECUTE IMMEDIATE b_lenh bulk collect into a_ma_pvi,a_ma_dk,a_ten_pvi,a_chon_pvi,a_ptts,
a_ptkh,a_ktru,a_tc,a_loai,a_ma_ct using b_dt_pvi;
if a_ma_pvi.count > 0 then
for b_i1 in 1..a_ma_pvi.count loop
insert into temp_1(c1,c2,c3,c4,c5,c6,c7,c8,c9,c10) VALUES (a_ma_pvi(b_i1),a_ma_dk(b_i1),
a_ten_pvi(b_i1),a_chon_pvi(b_i1),a_ptts(b_i1),a_ptkh(b_i1),a_ktru(b_i1),
a_tc(b_i1),a_loai(b_i1),a_ma_ct(b_i1));
end loop;
end if;
end if;
for r_lp in (select ma,ten,ma_dk from bh_phh_pvi) loop
select count(*) into b_i1 from temp_1 where c1=r_lp.ma;
if b_i1=0 then insert into temp_1(c1,c2,c3,c4) values(r_lp.ma,r_lp.ten,' ',r_lp.ma_dk); end if;
end loop;
select JSON_ARRAYAGG(json_object('ma' value c1,'ten' value c2,'ma_dk' value c3,'ptTSB' value c5,'ptTS' value '',
'ptKHB' value c6,'ptKH' value '')
order by c1,c2 returning clob) into b_oraOut from temp_1;
--select JSON_ARRAYAGG(json_object(ma,ten,'ptTSB' value ptTS,'ptTS' value '',
--'ptKHB' value ptKH,'ptKH' value '',ktru,tc,loai,ma_ct) order by bt returning clob)
--into b_oraOut from bh_phh_phiP_dk where so_id=b_so_id order by bt;
elsif b_vu='lt' then
select count(*) into b_i1 from bh_phh_phi_txt where so_id=b_so_id and loai='dt_lt';
if b_i1 >0 then
select txt into b_dk_lt from bh_phh_phi_txt where so_id=b_so_id and loai='dt_lt';
b_lenh:=FKH_JS_LENH('ma_lt,ma_dk,ten,chon');
EXECUTE IMMEDIATE b_lenh bulk collect into a_ma_lt,a_ma_dk_lt,a_ten_lt,a_chon_lt using b_dk_lt;
if a_ma_lt.count > 0 then
for b_i1 in 1..a_ma_lt.count loop
insert into temp_1(c1,c2,c3,c4) VALUES (a_ma_lt(b_i1),a_ma_dk_lt(b_i1),a_ten_lt(b_i1),a_chon_lt(b_i1));
end loop;
end if;
end if;
for r_lp in (select ma,ten,ma_dk from bh_ma_dklt where FBH_MA_NV_CO(nv,'PHH')='C') loop
select count(*) into b_i1 from temp_1 where c1=r_lp.ma;
if b_i1=0 then insert into temp_1(c1,c2,c3,c4) values(r_lp.ma,r_lp.ten,' ',r_lp.ma_dk); end if;
end loop;
select JSON_ARRAYAGG(json_object('ma_lt' value c1,'ten' value c2,'ma_dk' value c3)
order by c1,c2 returning clob) into b_oraOut from temp_1;
end if;
end if;
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_PKT_BPHI_THEM(
b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_oraIn clob,b_oraOut out clob)
AS
b_loi varchar2(1000); b_lenh varchar2(1000); b_i1 number;
b_nhom varchar2(1); b_ma_sp varchar2(10); b_ma_nct varchar2(500);
b_rru varchar2(500); b_ma_ntb varchar2(500);b_ngay_hl number; b_so_id number; b_vu varchar2(10);
b_dk_lt clob; a_ma_lt pht_type.a_var; a_ma_dk_lt pht_type.a_var; a_ten_lt pht_type.a_nvar; a_chon_lt pht_type.a_var;
b_dt_pvi clob;a_ma_pvi pht_type.a_var;a_ten_pvi pht_type.a_var;a_ptts pht_type.a_num;a_ptkh pht_type.a_num;a_chon_pvi pht_type.a_var;
begin
-- Dan - Tra so_id bieu phi theo dieu kien
delete temp_1; commit;
b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'BH','PKT','');
if b_loi is not null then raise PROGRAM_ERROR; end if;
b_lenh:=FKH_JS_LENH('nhom,ma_sp,ma_nct,rru,ma_ntb,ngay_hl,vu');
EXECUTE IMMEDIATE b_lenh into b_nhom,b_ma_sp,b_ma_nct,b_rru,b_ma_ntb,b_ngay_hl,b_vu using b_oraIn;
b_ma_nct:=nvl(PKH_MA_TENl(b_ma_nct),' '); b_ma_ntb:=nvl(PKH_MA_TENl(b_ma_ntb),' '); b_rru:=nvl(PKH_MA_TENl(b_rru),' ');
b_so_id:=FBH_PKT_BPHI_SO_ID(b_nhom,b_ma_sp,b_ma_nct,b_rru,b_ma_ntb,b_ngay_hl);
b_oraOut:='';
if b_vu='dkbs' then
insert into temp_1(c1,c2,n1,c3,c4) select ma,ten,pt,lbh,ma_dkc from bh_pkt_phi_dk where so_id=b_so_id and nv='M';
for r_lp in (select ma,ten,ma_dk from bh_ma_dkbs where FBH_MA_NV_CO(nv,'PKT')='C') loop
select count(*) into b_i1 from temp_1 where c1=r_lp.ma;
if b_i1=0 then insert into temp_1(c1,c2,c3,n1,c4) values(r_lp.ma,r_lp.ten,' ',0,r_lp.ma_dk); end if;
end loop;
select JSON_ARRAYAGG(json_object('ma' value c1,'ten' value c2,'ptB' value n1,'pt' value 0,'lbh' value c3,'ma_dkc' value c4)
order by c1,c2 returning clob) into b_oraOut from temp_1;
elsif b_so_id<>0 then
if b_vu='dk' then
select JSON_ARRAYAGG(json_object(ma,ten,cap,ma_ct,lkeM,'ptB' value pt,'pt' value '') order by bt returning clob)
into b_oraOut from bh_pkt_phi_dk where so_id=b_so_id and nv<>'M' order by bt;
elsif b_vu='pvi' then
select count(*) into b_i1 from bh_pkt_phi_txt where so_id=b_so_id and loai='dt_pvi';
if b_i1 >0 then
select txt into b_dt_pvi from bh_pkt_phi_txt where so_id=b_so_id and loai='dt_pvi';
b_lenh:=FKH_JS_LENH('ma,ten,ptts,ptkh,chon');
EXECUTE IMMEDIATE b_lenh bulk collect into a_ma_pvi,a_ten_pvi,a_ptts,a_ptkh,a_chon_pvi using b_dt_pvi;
if a_ma_pvi.count > 0 then
for b_i1 in 1..a_ma_pvi.count loop
insert into temp_1(c1,c2,c3,c4,c5) VALUES (a_ma_pvi(b_i1),a_ten_pvi(b_i1),a_ptts(b_i1),a_ptkh(b_i1),a_chon_pvi(b_i1));
end loop;
end if;
end if;
for r_lp in (select ma,ten,tc,txt from bh_pkt_pvi) loop
select count(*) into b_i1 from temp_1 where c1=r_lp.ma;
if b_i1=0 then insert into temp_1(c1,c2,c3) values(r_lp.ma,r_lp.ten,r_lp.tc); end if;
end loop;
select JSON_ARRAYAGG(json_object('ma' value c1,'ten' value c2,'ptTSB' value c3,'ptTS' value '',
'ptKHB' value c4,'ptKH' value '')
order by c1,c2 returning clob) into b_oraOut from temp_1;
--select JSON_ARRAYAGG(json_object(ma,ten,'ptTSB' value ptTS,'ptTS' value '',
--'ptKHB' value ptKH,'ptKH' value '',ktru,tc) order by bt returning clob)
--into b_oraOut from bh_pkt_phiP_dk where so_id=b_so_id order by bt;
elsif b_vu='lt' then
select count(*) into b_i1 from bh_pkt_phi_txt where so_id=b_so_id and loai='dt_lt';
if b_i1 >0 then
select txt into b_dk_lt from bh_pkt_phi_txt where so_id=b_so_id and loai='dt_lt';
b_lenh:=FKH_JS_LENH('ma_lt,ma_dk,ten,chon');
EXECUTE IMMEDIATE b_lenh bulk collect into a_ma_lt,a_ma_dk_lt,a_ten_lt,a_chon_lt using b_dk_lt;
if a_ma_lt.count > 0 then
for b_i1 in 1..a_ma_lt.count loop
insert into temp_1(c1,c2,c3,c4) VALUES (a_ma_lt(b_i1),a_ma_dk_lt(b_i1),a_ten_lt(b_i1),a_chon_lt(b_i1));
end loop;
end if;
end if;
for r_lp in (select ma,ten,ma_dk from bh_ma_dklt where FBH_MA_NV_CO(nv,'PKT')='C') loop
select count(*) into b_i1 from temp_1 where c1=r_lp.ma;
if b_i1=0 then insert into temp_1(c1,c2,c3,c4) values(r_lp.ma,r_lp.ten,' ',r_lp.ma_dk); end if;
end loop;
select JSON_ARRAYAGG(json_object('ma_lt' value c1,'ten' value c2,'ma_dk' value c3)
order by c1,c2 returning clob) into b_oraOut from temp_1;
end if;
end if;
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_PKT_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_nhom varchar2(1); b_ma_sp varchar2(500); b_rru varchar2(1); b_ma_nct varchar2(500);
b_ma_ntb 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','PKT','');
if b_loi is not null then raise PROGRAM_ERROR; end if;
b_lenh:=FKH_JS_LENH('nhom,ma_sp,rru,ma_nct,ma_ntb,tu,den');
EXECUTE IMMEDIATE b_lenh into b_nhom,b_ma_sp,b_rru,b_ma_nct,b_ma_ntb,b_tu,b_den using b_oraIn;
b_ma_sp:=PKH_MA_TENl(b_ma_sp);
b_ma_nct:=PKH_MA_TENl(b_ma_nct);
b_ma_ntb:=PKH_MA_TENl(b_ma_ntb);
select count(*) into b_dong from bh_pkt_phi where b_nhom in (' ',nhom) and b_ma_sp in (' ',ma_sp) and b_rru in (' ',rru)
and b_ma_nct in (' ',ma_nct) and b_ma_ntb in (' ',ma_ntb);
PKH_LKE_TRANG(b_dong,b_tu,b_den);
select JSON_ARRAYAGG(json_object(nhom,'ma_sp' value FBH_PKT_MA_SP_TEN(ma_sp),
'ma_nct' value FBH_PKT_MA_NCT_TEN(ma_nct),'ma_ntb' value FBH_PKT_MA_NTB_TEN(b_ma_ntb),rru,nsd,so_id)
order by nhom,ma_sp,ma_nct,ma_ntb,rru returning clob) into cs_lke from
(select nhom,ma_sp,ma_nct,ma_ntb,rru,nsd,so_id,rownum sott
from bh_pkt_phi where b_nhom in (' ',nhom) and b_ma_sp in (' ',ma_sp) and b_rru in (' ',rru) and b_ma_nct in (' ',ma_nct) and b_ma_ntb in (' ',ma_ntb)
order by nhom,ma_sp,ma_nct,ma_ntb,rru)
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;
/