AAA

create or replace procedure PBH_XE_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_so_id number; b_i1 number; b_i2 number;
    b_nhom varchar2(1);  b_nv_bh varchar2(10); b_lh_bh  varchar2(10);
    b_bh_tbo varchar2(1); b_md_sd varchar2(500);
    b_ma_sp varchar2(10); b_cdich varchar2(10); b_goi varchar2(10);
    b_loai_xe varchar2(500); b_nhom_xe varchar2(500); b_dong varchar2(500); b_dco varchar2(500);
    b_ttai number; b_so_cn number; b_tuoi number; b_gia number;
    b_ngay_bd number; b_ngay_kt number; b_ngay_hl number;
    a_nv pht_type.a_var; b_so_idS varchar2(100):=''; b_vu varchar2(10); b_so_idN pht_type.a_num;
    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;
begin
-- Dan - Tra so ID
b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'BH','XE','');
if b_loi is not null then raise PROGRAM_ERROR; end if;
delete from temp_1; commit;
PBH_XE_BPHI_TSOm(
    b_oraIn,b_nhom,b_bh_tbo,b_cdich,b_goi,b_ttai,b_so_cn,b_loai_xe,b_nhom_xe,b_gia,b_tuoi,
    b_ma_sp,b_md_sd,b_nv_bh,b_dong,b_dco,b_ngay_hl,b_lh_bh,b_loi);
if b_loi is not null then raise PROGRAM_ERROR; end if;
b_lenh:=FKH_JS_LENH('nvb,nvt,nvv,vu');
EXECUTE IMMEDIATE b_lenh into a_nv(1),a_nv(2),a_nv(3),b_vu using b_oraIn;
for b_lp in 1..3 loop
    if nvl(trim(a_nv(b_lp)),' ')='C' then
        if b_lp=1 then a_nv(b_lp):='B';
        elsif b_lp=2 then a_nv(b_lp):='T';
        elsif b_lp=3 then a_nv(b_lp):='V';
        end if;
        FBH_XE_BPHI_SO_ID(
            b_nhom,a_nv(b_lp),b_bh_tbo,b_md_sd,b_ma_sp,b_cdich,b_goi,b_loai_xe,
            b_nhom_xe,b_dong,b_dco,b_ttai,b_so_cn,b_tuoi,b_gia,b_ngay_hl,b_so_id,b_loi);
        if b_loi is not null then raise PROGRAM_ERROR; end if;
        if b_so_id=0 then
      b_so_idS:='0'; exit;
    else
      b_so_idS := LTRIM(b_so_idS || ',' || TO_CHAR(b_so_id), ',');
    end if;
    end if;
end loop;
b_oraOut:='';
PKH_CH_ARR_N(b_so_idS,b_so_idN,',');
if b_so_idN.count > 0 then
  if b_vu='dk' then
        for b_i1 in 1..b_so_idN.count loop
          insert into temp_1(n1) values (b_so_idN(b_i1));
        end loop;
        select JSON_ARRAYAGG(json_object(ma,ten,tc,ma_ct,kieu,tien,pt,phi,
            lkem,lkep,lkeb,luy,ma_dk,ma_dkC,lh_nv,t_suat,cap,lh_bh) order by bt returning clob)
            into b_oraOut from bh_xe_phi_dk where so_id in (select n1 from temp_1) and lh_bh<>'M' order by bt;
  elsif b_vu='dkbs' then
        for b_i1 in 1..b_so_idN.count loop
          insert into temp_1(n1) values (b_so_idN(b_i1));
        end loop;
   -- viet anh - sua pt -> ptb and pt=''
        select JSON_ARRAYAGG(json_object(ma,ten,tien,'ptb' value pt,'pt' value '',phi,
               cap,tc,ma_ct,ma_dk,ma_dkC,kieu,lh_nv,t_suat,lkeM,lkeP,lkeB,luy,lh_bh) order by bt returning clob) 
               into b_oraOut from bh_xe_phi_dk where so_id in (select n1 from temp_1) and lh_bh='M' order by bt;
  elsif b_vu='lt' then
        for b_i1 in 1..b_so_idN.count loop
          select txt into b_dk_lt from bh_xe_phi_txt where so_id=b_so_idN(b_i1) 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_i2 in 1..a_ma_lt.count loop
                 insert into temp_1(c1,c2,c3,c4) VALUES (a_ma_lt(b_i2),a_ma_dk_lt(b_i2),a_ten_lt(b_i2),a_chon_lt(b_i2));
            end loop;
          end if;
        end loop;

        for r_lp in (select ma,ten,ma_dk from bh_ma_dklt where FBH_MA_NV_CO(nv,'XE')='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;