AAA

create or replace procedure PBH_PKT_BPHI_TSOt(
    b_ma_sp varchar2,b_ma_cct varchar2,b_ma_nct varchar2,b_ma_dkdl varchar2,
  b_ma_dktc varchar2,b_rru varchar2,b_ma_ntb nvarchar2,b_loi out varchar2)
AS
begin
-- Dan - Ktra tso
if b_ma_sp<>' ' and  FBH_PKT_SP_HAN(b_ma_sp)<>'C' then
    b_loi:='loi:Ma san pham '||b_ma_sp||'da het su dung:loi'; return;
end if;
if b_ma_cct<>' ' and  FBH_PKT_MA_CCT_HAN(b_ma_cct)<>'C' then
    b_loi:='loi:Ma cap C.trinh '||b_ma_cct||'da het su dung:loi'; return;
end if;
if b_ma_nct<>' ' and  FBH_PKT_MA_NCT_HAN(b_ma_nct)<>'C' then
    b_loi:='loi:Ma nhom C.trinh '||b_ma_nct||'da het su dung:loi'; return;
end if;
if b_ma_ntb<>' ' and  FBH_PKT_MA_NTB_HAN(b_ma_ntb)<>'C' then
    b_loi:='loi:Ma loai thiet bi '||b_ma_ntb||'da het su dung:loi'; return;
end if;
if b_ma_dkdl<>' ' and  FBH_PKT_MA_DKDL_HAN(b_ma_dkdl)<>'C' then
    b_loi:='loi:Ma dieu kien dia ly '||b_ma_dkdl||'da het su dung:loi'; return;
end if;
if b_ma_dktc<>' ' and  FBH_PKT_MA_DKTC_HAN(b_ma_dktc)<>'C' then
    b_loi:='loi:Ma dieu kien thi cong '||b_ma_dktc||'da het su dung:loi'; return;
end if;
b_loi:='';
end;
/

create or replace procedure PBH_PKT_BPHI_TSO(
    b_oraIn clob,b_nhom out varchar2,b_ma_sp out varchar2,b_ma_nct out varchar2,
    b_rru out varchar2,b_ma_ntb out nvarchar2,b_ngay_hl out number)
AS
    b_lenh varchar2(1000);
begin
-- Dan - Tra tso
b_lenh:=FKH_JS_LENH('nhom,ma_sp,ma_nct,rru,ma_ntb,ngay_hl');
EXECUTE IMMEDIATE b_lenh into b_nhom,b_ma_sp,b_ma_nct,b_rru,b_ma_ntb,b_ngay_hl using b_oraIn;
b_nhom:=nvl(trim(b_nhom),' '); b_ma_sp:=PKH_MA_TENl(b_ma_sp);
if trim(b_ma_nct) = '|' then b_ma_nct := ''; end if;
b_ma_nct:=PKH_MA_TENl(b_ma_nct); b_rru:=nvl(trim(b_rru),' '); b_ma_ntb:=nvl(trim(b_ma_ntb),' ');
b_ngay_hl:=nvl(b_ngay_hl,0);
end;

/
create or replace procedure PBH_PKT_BPHI_CTs(
    b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_oraIn clob,b_oraOut out clob)
AS
    b_loi varchar2(100); b_so_id number; b_ngay_hl number;
    b_nhom varchar2(1); b_ma_sp varchar2(10); b_ma_nct varchar2(500);
    b_rru varchar2(1); b_ma_ntb nvarchar2(500);
begin
-- Dan - Tra so_id bieu phi theo dieu kien
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;
PBH_PKT_BPHI_TSO(b_oraIn,b_nhom,b_ma_sp,b_ma_nct,b_rru,b_ma_ntb,b_ngay_hl);
b_so_id:=FBH_PKT_BPHI_SO_ID(b_nhom,b_ma_sp,b_ma_nct,b_rru,b_ma_ntb,b_ngay_hl);
select json_object('so_id' value b_so_id) 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_PKT_BPHI_CTd(
    b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_oraIn clob,b_oraOut out clob)
AS
    b_loi varchar2(100); b_i1 number; b_so_id number:=FKH_JS_GTRIn(b_oraIn,'so_id');
    cs_dk clob; cs_dkbs clob; cs_pvi clob; dt_khd clob:=''; cs_kbt clob:=''; cs_lt clob; cs_txt clob;
begin
-- Dan - Tra bieu phi theo so_id
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;
select JSON_ARRAYAGG(json_object(ma,ma_dk,ten,cap,ma_ct,lkeM,'ptB' value pt,'pt' value '') order by bt returning clob)
    into cs_dk from bh_pkt_phi_dk where so_id=b_so_id and nv<>'M' order by bt;
select JSON_ARRAYAGG(json_object(ma,ten,lbh,'ptB' value pt,'pt' value '',ma_dkc) order by bt returning clob)
    into cs_dkbs from bh_pkt_phi_dk where so_id=b_so_id and nv='M' order by bt;
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 cs_pvi from bh_pkt_phiP_dk where so_id=b_so_id order by bt;
select JSON_ARRAYAGG(json_object(ma_lt,ten) order by bt returning clob) into cs_lt from bh_pkt_phi_lt where so_id=b_so_id;
--select JSON_ARRAYAGG(json_object(loai,txt returning clob) returning clob) into cs_txt
--    from bh_pkt_phi_txt where so_id=b_so_id and loai='dt_lt';
select count(*) into b_i1 from bh_pkt_phi_txt where so_id=b_so_id and loai='dt_khd';
if b_i1<>0 then
    select txt into dt_khd from bh_pkt_phi_txt where so_id=b_so_id and loai='dt_khd';
end if;
select count(*) into b_i1 from bh_pkt_phi_txt where so_id=b_so_id and loai='dt_kbt';
if b_i1<>0 then
    select txt into cs_kbt from bh_pkt_phi_txt where so_id=b_so_id and loai='dt_kbt';
end if;
select json_object('dt_dk' value cs_dk,'dt_dkbs' value cs_dkbs,'dt_pvi' value cs_pvi,
    'dt_khd' value dt_khd,'dt_kbt' value cs_kbt,'dt_lt' value cs_lt 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;

/