AAA

create or replace noneditionable procedure PBH_BT_PHH_GCN(
    b_ma_dviN varchar2,b_nsd varchar2,b_pas varchar2,b_oraIn clob,b_oraOut out clob)
AS
    b_loi varchar2(2000); b_lenh varchar2(2000);
    b_so_id_dt number; b_ngay_xr number; b_nt_tien varchar2(5);
    b_ma_dvi varchar2(10); b_so_idG number; b_so_id number;
    b_dvi_ta varchar2(10):=FTBH_DVI_TA(); b_tg number:=1;
    dt_ct clob; dt_kbt clob; dt_btlke clob; dt_dk clob; dt_lt clob;
begin
-- Dan - Liet ke theo ngay
b_loi:=FHT_MA_NSD_KTRA(b_ma_dviN,b_nsd,b_pas,'BH','BT','X');
if b_loi is not null then raise PROGRAM_ERROR; end if;
b_lenh:=FKH_JS_LENH('so_id_dt,ngay_xr');
EXECUTE IMMEDIATE b_lenh into b_so_id_dt,b_ngay_xr using b_oraIn;
b_loi:='loi:Dia diem bao hiem da xoa:loi';
FBH_PHH_SO_ID_DTf(b_so_id_dt,b_ma_dvi,b_so_idG);
if b_so_idG=0 then raise PROGRAM_ERROR; end if;
b_so_id:=FBH_PHH_SO_IDb(b_ma_dvi,b_so_idG,b_ngay_xr);
select nt_tien into b_nt_tien from bh_phh where ma_dvi=b_ma_dvi and so_id=b_so_id;
if b_nt_tien<>'VND' then b_tg:=FTT_TRA_TGTT(b_dvi_ta,b_ngay_xr,b_nt_tien); end if;
select json_object(a.so_hd,a.ten,'ma_dvi_ql' value a.ma_dvi,b.dvi,b.ngay_hl,b.ngay_kt,a.nt_tien,a.c_thue,
    'lhe_ten' value a.ten,'lhe_mobi' value a.mobi,'lhe_mail' value a.email,'tygia' value b_tg) into dt_ct
    from bh_phh a,bh_phh_dvi b where a.ma_dvi=b_ma_dvi and a.so_id=b_so_id and
    b.ma_dvi=b_ma_dvi and b.so_id=b_so_id and b.so_id_dt=b_so_id_dt;
select JSON_ARRAYAGG(json_object('chon' value '',MA,ten,'tien_bh' value tien,
    'pt_bt' value 0,'ycau' value 0,'t_that' value 0,'tien' value 0,'nd' value '','ktru' value 0,
    't_hoi' value 0,'bt_con' value 0,'bt_lke' value 0,
    cap,tc,ma_ct,ma_dk,ma_dkC,kieu,lh_nv,t_suat,ptB,lkeP,lkeB,luy,'ktruk' value ktru,pvi_ktru)
    order by bt returning clob) into dt_dk from bh_phh_dk where
    ma_dvi=b_ma_dvi and so_id=b_so_id and so_id_dt=b_so_id_dt and pvi_ma=' '; 
dt_btlke:=FBH_BT_PHH_BTH_LKE(b_so_id_dt);
select json_object('dt_ct' value dt_ct,'dt_lt' value dt_lt,'dt_kbt' value dt_kbt,
    'dt_btlke' value dt_btlke,'dt_dk' value dt_dk 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_PHHH_TESTd(
    dt_ctH clob,dt_ct in out clob,dt_dk clob,dt_dkbs clob,dt_pvi clob,
    b_ma_dvi varchar2,b_nsd varchar2,b_ttrang varchar2,b_kieu_hd varchar2,b_ma_sp varchar2,b_cdich varchar2,b_goi varchar2,
    b_nt_tien varchar2,b_nt_phi varchar2,b_tygia number,b_c_thue varchar2,
    b_ngay_hlH number,b_ngay_ktH number,b_ngay_capH number,
    b_so_id_dt out number,b_kieu_gcn out varchar2,b_gcn out varchar2,b_gcnG out varchar2,
    b_dvi out nvarchar2,b_ddiem out nvarchar2,b_kvuc out varchar2,b_tdx out number,b_tdy out number,b_bk out number,
    b_ma_dt out varchar2,b_lvuc out varchar2,b_mrr out varchar2,b_dk_lut out varchar2,b_hs_lut out number,
    b_ngay_hl out number,b_ngay_kt out number,b_ngay_cap out number,b_tlbt out number,b_so_idP out number,
    dk_ma out pht_type.a_var,dk_ten out pht_type.a_nvar,dk_tc out pht_type.a_var,
    dk_ma_ct out pht_type.a_var,dk_kieu out pht_type.a_var,
    dk_lkeM out pht_type.a_var,dk_lkeP out pht_type.a_var,dk_lkeB out pht_type.a_var,
    dk_luy out pht_type.a_var,dk_ktru out pht_type.a_var,
    dk_ma_dk out pht_type.a_var,dk_ma_dkC out pht_type.a_var,dk_lh_nv out pht_type.a_var,dk_t_suat out pht_type.a_num,
    dk_cap out pht_type.a_num,dk_lbh out pht_type.a_var,dk_nv out pht_type.a_var,
    dk_tien out pht_type.a_num,dk_pt out pht_type.a_num,dk_phi out pht_type.a_num,
    dk_thue out pht_type.a_num,dk_ttoan out pht_type.a_num,
    dk_ptB out pht_type.a_num,dk_phiB out pht_type.a_num,
    dk_pvi_ma out pht_type.a_var,dk_pvi_tc out pht_type.a_var,dk_pvi_ktru out pht_type.a_var,b_loi out varchar2)
AS
    b_i1 number; b_tp number:=0; b_lenh varchar2(2000); b_pt_hang number; b_so_idPn number;
    dt_khd clob; b_txt clob;
    a_thay pht_type.a_num; a_loaiL pht_type.a_var; a_maL pht_type.a_var; a_loi pht_type.a_var;
begin
-- Dan - Nhap
b_loi:='loi:Loi xu ly PBH_PHHH_TESTd:loi';
b_lenh:=FKH_JS_LENH('so_id_dt,gcn,gcn_g,ngay_hl,ngay_kt,dvi,ddiem,tdx,tdy,ma_dt,lvuc,mrr,dk_lut,hs_lut,tlbt,pt_hang,so_idp');
EXECUTE IMMEDIATE b_lenh into
    b_so_id_dt,b_gcn,b_gcnG,b_ngay_hl,b_ngay_kt,b_dvi,b_ddiem,b_tdx,b_tdy,
    b_ma_dt,b_lvuc,b_mrr,b_dk_lut,b_hs_lut,b_tlbt,b_pt_hang,b_so_idPn using dt_ct;
b_dvi:=trim(b_dvi); b_ddiem:=trim(b_ddiem); b_tlbt:=nvl(b_tlbt,0);
if b_dvi is null or b_ddiem is null then b_loi:='loi:Nhap dia diem, dia chi bao hiem:loi'; return; end if;
if b_tdx=0 or b_tdy=0 then b_loi:='loi:Chua xac dinh toa do dia diem '||b_dvi||':loi'; return; end if;
b_kvuc:=' '; b_bk:=nvl(b_bk,25); b_dk_lut:=nvl(trim(b_dk_lut),'K'); b_hs_lut:=nvl(b_hs_lut,0);
b_loi:='loi:Sai ma doi tuong:loi';
b_ma_dt:=PKH_MA_TENl(b_ma_dt);
if FBH_PHH_DTUONG_HAN(b_ma_dt)<>'C' then return; end if;
--b_lvuc:=PKH_MA_TENl(b_lvuc);
--b_loi:='loi:Sai linh vuc: '||b_lvuc||':loi';
--if FBH_MA_LVUC_HAN(b_lvuc)<>'C' then return; end if;
b_loi:='loi:Sai muc rui ro:loi';
b_mrr:=PKH_MA_TENl(b_mrr);
if FBH_PHH_MRR_HAN(b_mrr)<>'C' then return; end if;
if b_nt_phi<>'VND' then b_tp:=2; end if;
if b_ngay_hl is null or b_ngay_hl in(0,30000101) or b_ngay_kt is null or b_ngay_kt in(0,30000101) or
    b_ngay_hl<b_ngay_hlH or b_ngay_hl>b_ngay_kt or b_ngay_kt<b_ngay_hl or b_ngay_kt>b_ngay_ktH then
    b_loi:='loi:Sai ngay hieu luc don vi rui ro: '||b_dvi||':loi'; return;
end if;
b_kieu_gcn:='G'; b_ngay_cap:=b_ngay_capH;
b_gcn:=nvl(trim(b_gcn),' '); b_gcnG:=nvl(trim(b_gcnG),' ');
if b_so_id_dt<100000 then
    PHT_ID_MOI(b_so_id_dt,b_loi);
    if b_loi is not null then return; end if;
    b_gcn:=substr(to_char(b_so_id_dt),3); b_gcnG:=' ';
else
    if b_kieu_hd in('S','B') and b_gcnG<>' ' then
        select count(*) into b_i1 from bh_phh_dvi where ma_dvi=b_ma_dvi and so_id_dt=b_so_id_dt and gcn=b_gcnG;
        if b_i1=0 then b_loi:='loi:GCN '||b_gcnG||' da xoa:loi'; return; end if;
        b_kieu_gcn:=b_kieu_hd; b_gcn:=' ';
    end if;
    if b_gcn=' ' or instr(b_gcn,'.')=2 then
        b_gcn:=substr(to_char(b_so_id_dt),3);
        if b_kieu_gcn<>'G' then
            select count(*) into b_i1 from bh_phh_dvi where ma_dvi=b_ma_dvi and so_id_dt=b_so_id_dt and kieu_gcn=b_kieu_hd;
            b_gcn:=b_gcn||'/'||b_kieu_hd||to_char(b_i1+1);
        end if;
    else
        select nvl(max(ngay_cap),b_ngay_capH) into b_ngay_cap from bh_phh_dvi where ma_dvi=b_ma_dvi and so_id_dt=b_so_id_dt and gcn=b_gcn;      
    end if;
end if;
PKH_JS_THAYa(dt_ct,'gcn,gcn_g',b_gcn||','||b_gcnG);
a_thay(1):=b_so_id_dt; a_thay(2):=b_ngay_hl; a_thay(3):=b_ngay_kt; a_thay(4):=b_ngay_cap;
PKH_JS_THAYan(dt_ct,'so_id_dt,ngay_hl,ngay_kt,ngay_cap',a_thay);
b_txt:=dt_ct;
b_lenh:='H,'||b_ma_sp||','||b_cdich||','||b_goi||','||b_nt_tien||','||b_nt_phi||','||b_c_thue;
PKH_JS_THAYa(b_txt,'nhom,ma_sp,cdich,goi,nt_tien,nt_phi,c_thue',b_lenh);
PKH_JS_THAYn(b_txt,'tygia',b_tygia);
FBH_PHHG_PHI(b_txt,dt_dk,dt_dkbs,dt_pvi,b_so_idP,b_pt_hang,
    dk_ma,dk_ten,dk_tc,dk_ma_ct,dk_kieu,dk_lkeM,dk_lkeP,dk_lkeB,dk_luy,dk_ktru,dk_ma_dk,dk_ma_dkC,
    dk_lh_nv,dk_t_suat,dk_cap,dk_lbh,dk_nv,dk_tien,dk_pt,dk_phi,dk_thue,dk_ttoan,dk_ptB,dk_phiB,
    dk_pvi_ma,dk_pvi_tc,dk_pvi_ktru,b_loi);
if b_loi is not null then return; end if;
if b_so_idP<>b_so_idPn then
    b_loi:='loi:Thong tin xac dinh bieu phi '||b_dvi||' bi thay doi:loi'; return;
end if;
if b_ttrang in('T','D') then
    select count(*) into b_i1 from bh_phh_phi_txt where so_id=b_so_idP and loai='dt_khd';
    if b_i1=1 then
        select txt into b_txt from bh_phh_phi_txt where so_id=b_so_idP and loai='dt_khd';
        b_txt:=FKH_JS_BONH(b_txt);
        PBH_PHHH_KHD(dt_ctH,dt_ct,dt_dk,dt_dkbs,dt_pvi,b_txt,b_loi);
        if b_loi is not null then return; end if;
    end if;
end if;
b_loi:='';
exception when others then if b_loi is null then raise PROGRAM_ERROR; else null; end if;
end;