AAA

CREATE TABLE bh_tau_ppcap
    (ma_dvi varchar2(10),
    nhom varchar2(10),
    loai varchar2(10),
    nv_bh varchar2(1),             -- V-Vat chat, T-trach nhiem, N-nguoi, M-Bo sung
    cap varchar2(10),
    pt number,
    ngay_kt number,
    nsd varchar2(10));
create unique index bh_tau_ppcap_i1 on bh_tau_ppcap(nhom,loai,nv_bh,cap);
/
create or replace procedure PBH_TAU_PPCAP_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_tu number; b_den number; b_dong number:=0; cs_lke clob:='';
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;
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 bh_tau_pphcai;
PKH_LKE_TRANG(b_dong,b_tu,b_den);
select JSON_ARRAYAGG(json_object(nhom,loai,cap,nv_bh,pt,
    'nhomT' value FBH_TAU_NHOM_TEN(nhom),'loaiT' value FBH_TAU_LOAI_TEN(loai),'capT' value FBH_TAU_CAP_TEN(cap),
    'nv_bhT' value decode(nv_bh,'V','Vat chat','T','Trach nhiem','N','Tai nan thuyen vien','Mo rong'))
    order by nhom,loai,cap,nv_bh returning clob) into cs_lke from
    (select nhom,loai,cap,nv_bh,pt,rownum sott from bh_tau_ppcap)
    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_TAU_PPCAP_MA(
    b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_oraIn clob,b_oraOut out clob)
AS
    b_loi varchar2(100); b_trang number:=1; b_dong number:=0; cs_lke clob:='';
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 count(*) into b_dong from bh_tau_ppcap;
select JSON_ARRAYAGG(json_object(*) order by nhom,loai,nv_bh,cap returning clob) into cs_lke from bh_tau_ppcap;
select json_object('trang' value b_trang,'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_TAU_PPCAP_CT(
    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_kq clob:='';
    b_nhom varchar2(10); b_loai varchar2(10); b_nv_bh varchar2(1); b_cap varchar2(10):=FKH_JS_GTRIs(b_oraIn,'cap');
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;
PBH_TAU_PPTSO(b_oraIn,b_nhom,b_loai,b_nv_bh,b_loi);
if b_loi is not null then raise PROGRAM_ERROR; end if;
select count(*) into b_i1 from bh_tau_ppcap where nhom=b_nhom and loai=b_loai and nv_bh=b_nv_bh and cap=b_cap;
if b_i1=1 then
    select json_object('nhom' value FBH_TAU_NHOM_TENl(nhom),'loai' value FBH_TAU_LOAI_TENl(loai),'cap' value FBH_TAU_CAP_TENl(cap),
  nv_bh,pt,ngay_kt) into b_kq from bh_tau_ppcap where nhom=b_nhom and loai=b_loai and nv_bh=b_nv_bh and cap=b_cap;
end if;
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_TAU_PPCAP_NH(
    b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_oraIn clob)
AS
    b_loi varchar2(100); b_lenh varchar2(1000);
    b_nhom varchar2(10); b_loai varchar2(10); b_nv_bh varchar2(1); b_cap varchar2(10);
    b_pt number; b_ngay_kt number;
begin
-- Nam
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;
PBH_TAU_PPTSO(b_oraIn,b_nhom,b_loai,b_nv_bh,b_loi,'C');
if b_loi is not null then raise PROGRAM_ERROR; end if;
b_lenh:=FKH_JS_LENH('cap,pt,ngay_kt');
EXECUTE IMMEDIATE b_lenh into b_cap,b_pt,b_ngay_kt using b_oraIn;
b_pt:=nvl(b_pt,0); b_ngay_kt:=nvl(b_ngay_kt,30000101);
delete bh_tau_ppcap where nhom=b_nhom and loai=b_loai and nv_bh=b_nv_bh and cap=b_cap;
insert into bh_tau_ppcap values(b_ma_dvi,b_nhom,b_loai,b_nv_bh,b_cap,b_pt,b_ngay_kt,b_nsd);
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_TAU_PPCAP_XOA(b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_oraIn clob)
AS
    b_loi varchar2(100);
    b_nhom varchar2(10); b_loai varchar2(10); b_nv_bh varchar2(1); b_cap varchar2(10):=FKH_JS_GTRIs(b_oraIn,'cap');
begin
-- Nam
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;
PBH_TAU_PPTSO(b_oraIn,b_nhom,b_loai,b_nv_bh,b_loi);
if b_loi is not null then raise PROGRAM_ERROR; end if;
delete bh_tau_ppcap where nhom=b_nhom and loai=b_loai and nv_bh=b_nv_bh and cap=b_cap;
commit;
exception when others then rollback; if b_loi is null then raise PROGRAM_ERROR; else raise_application_error(-20105,b_loi); end if;
end;
/
create or replace function FBH_TAU_PPCAP_PT(b_nhom varchar2,b_loai varchar2,b_nv_bh varchar2,b_cap varchar2) return number
AS
    b_kq number:=0; b_ngay number:=PKH_NG_CSO(sysdate);
begin
-- Nam - Kiem tra con hieu luc
select nvl(max(pt),0) into b_kq from bh_tau_ppcap where nhom=b_nhom and loai=b_loai and nv_bh=b_nv_bh and cap=b_cap;
return b_kq;
end;
/
create or replace procedure PBH_TAU_PPCAP_PT(
    b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_oraIn clob,b_oraOut out clob)
AS
    b_loi varchar2(100); b_pt number;
    b_nhom varchar2(10); b_loai varchar2(10); b_nv_bh varchar2(1); b_cap varchar2(10):=FKH_JS_GTRIs(b_oraIn,'cap');
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;
PBH_TAU_PPTSO(b_oraIn,b_nhom,b_loai,b_nv_bh,b_loi);
if b_loi is not null then raise PROGRAM_ERROR; end if;
b_pt:=Fbh_tau_ppcap_PT(b_nhom,b_loai,b_nv_bh,b_cap);
select json_object('pt' value b_pt) 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_TAU_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_nv varchar2(10); b_so_idS varchar2(500); b_pphiS varchar2(500);
    b_ngay number:=PKH_NG_CSO(sysdate); b_tgT number:=1; b_tgP number:=1; b_i1 number;
    b_vu varchar2(10); b_nt_tien varchar2(5); b_nt_phi varchar2(5); b_dvi_ta varchar2(10):=FTBH_DVI_TA();
    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; a_so_idS pht_type.a_num;
begin
-- Dan - Tra so ID
delete temp_1; commit;
b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'BH','TAU','');
if b_loi is not null then raise PROGRAM_ERROR; end if;
b_lenh:=FKH_JS_LENH('vu,nt_tien,nt_phi');
EXECUTE IMMEDIATE b_lenh into b_vu,b_nt_tien,b_nt_phi using b_oraIn;
if b_nt_tien<>'VND' then b_tgT:=FTT_TRA_TGTT(b_dvi_ta,b_ngay,b_nt_tien); end if;
if b_nt_phi<>'VND' then b_tgP:=FTT_TRA_TGTT(b_dvi_ta,b_ngay,b_nt_phi); end if;
if b_vu='dkbs' then
    FBH_TAU_BPHI_CTbs(b_oraIn,b_nv,b_so_idS,b_pphiS,b_loi);
    insert into temp_1(c1,c2,n1,c3) select ma,ten,pt,lh_bh from bh_tau_phi_dk where so_id=b_so_idS;
    for r_lp in (select ma,ten,lh_nv from bh_ma_dkbs where FBH_MA_NV_CO(nv,'TAU')='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) values(r_lp.ma,r_lp.ten,r_lp.lh_nv,0); end if;
    end loop;
    select JSON_ARRAYAGG(json_object('ma' value c1,'ten' value c2,'ptB' value n1,'pt' value 0,'lh_nv' value c3,'ma_dk' value c1,'lkep' value 'G')
        order by c1,c2 returning clob) into b_oraOut from temp_1;
elsif b_vu='dk' then
    FBH_TAU_BPHI_CTs(b_oraIn,b_nv,b_so_idS,b_pphiS,b_loi);
    select JSON_ARRAYAGG(json_object('nv' value b_nv,ma,ten,cap,tc,ma_ct,ma_dk,
    kieu,lh_nv,t_suat,lkeP,lkeB,luy,gvu,'pt' value '',
    'tien' value case when b_tgT=1 then tien else round(tien/b_tgT,0) end,
    'ptB' value case when pt<100 then pt else round(pt/b_tgP,2) end)returning clob) into b_oraOut
    from bh_tau_phi_dk where so_id=b_so_idS;
elsif b_vu='lt' then
    FBH_TAU_BPHI_CTs(b_oraIn,b_nv,b_so_idS,b_pphiS,b_loi);
    PKH_CH_ARR_N(b_so_idS,a_so_idS,b_loi);
    for b_lp in 1..a_so_idS.count loop
        select count(*) into b_i1 from bh_tau_phi_txt where so_id =a_so_idS(b_lp) and loai='dt_lt';
        if b_i1 >0 then
           select txt into b_dk_lt from bh_tau_phi_txt where so_id=b_so_idS 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;
    end loop;
    --select count(*) into b_i1 from bh_tau_phi_txt where so_id in (a_so_idS) and loai='dt_lt';
    for r_lp in (select ma,ten,ma_dk from bh_ma_dklt where FBH_MA_NV_CO(nv,'TAU')='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;
if b_loi is not null then raise PROGRAM_ERROR; 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;
/