AAA

---- loi dklt nhieu so_id #3108
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,c4) select ma,ten,pt,lh_bh,ma_dk from bh_tau_phi_dk where so_id=b_so_idS;
    for r_lp in (select ma,ten,ma_dk 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,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_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;
/