AAA

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_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';

--b_loi:='loi:Sai linh vuc:loi'; -- chuclh - thong bao dung 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_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;

/

create or replace procedure PBH_PHHG_TESTr(

    b_ma_dvi varchar2,b_nsd varchar2,dt_ct clob,dt_dk clob,dt_dkbs clob,dt_pvi clob,dt_cdt clob,

    b_ng_huong out nvarchar2,b_ma_sp out varchar2,b_cdich out varchar2,b_goi 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_nt_tien out varchar2,b_nt_phi out varchar2,b_c_thue out varchar2,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_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_ptG out pht_type.a_num,dk_phiG 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,

    cdt_pthuc out pht_type.a_var,b_loi out varchar2)

AS

    b_i1 number; b_i2 number; b_lenh varchar2(2000); dt_khd clob; b_txt clob;

    b_thueH number; b_ttoanH number; b_tygia number; b_tp number:=0; b_pt_hang number; b_so_idPn number;

    b_ttrang varchar2(1); b_ngay_hl number; b_ngay_kt number; b_ps varchar2(1); b_qdoi number;

    b_loai_khH varchar2(1); b_ma_khH varchar2(20); b_tenH nvarchar2(500);

    b_cmtH varchar2(20); b_mobiH varchar2(20); b_emailH varchar2(100); b_dchiH nvarchar2(400);

    a_loaiL pht_type.a_var; a_maL pht_type.a_var; a_loi pht_type.a_var;

begin

-- Dan - Nhap

b_lenh:='ttrang,ngay_hl,ngay_kt,ma_sp,cdich,goi,';

b_lenh:=b_lenh||'dvi,ddiem,tdx,tdy,ma_dt,lvuc,mrr,dk_lut,hs_lut,';

b_lenh:=b_lenh||'nt_tien,nt_phi,tygia,c_thue,thue,ttoan,tlbt,pt_hang,so_idp';

b_lenh:=FKH_JS_LENH(b_lenh);

EXECUTE IMMEDIATE b_lenh into

    b_ttrang,b_ngay_hl,b_ngay_kt,b_ma_sp,b_cdich,b_goi,

    b_dvi,b_ddiem,b_tdx,b_tdy,b_ma_dt,b_lvuc,b_mrr,b_dk_lut,b_hs_lut,

    b_nt_tien,b_nt_phi,b_tygia,b_c_thue,b_thueH,b_ttoanH,b_tlbt,b_pt_hang,b_so_idPn using dt_ct;

if trim(dt_cdt) is null then

    PKH_MANG_KD(cdt_pthuc);

else

    b_lenh:=FKH_JS_LENH('pthuc');

    EXECUTE IMMEDIATE b_lenh bulk collect into cdt_pthuc using dt_cdt;

end if;

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;

b_kvuc:=' '; b_bk:=25; b_dk_lut:=nvl(trim(b_dk_lut),'K'); b_hs_lut:=nvl(b_hs_lut,0);

b_ma_sp:=nvl(trim(b_ma_sp),' '); b_cdich:=nvl(trim(b_cdich),' '); b_goi:=nvl(trim(b_goi),' ');

if b_ma_sp<>' ' and FBH_PHH_SP_HAN(b_ma_sp)<>'C' then b_loi:='loi:Sai ma san pham:loi'; return; end if;

if b_cdich<>' ' and FBH_MA_CDICH_HAN(b_cdich)<>'C' then b_loi:='loi:Het chien dich:loi'; return; end if;

if b_goi<>' ' and FBH_PHH_GOI_HAN(b_goi)<>'C' then b_loi:='loi:Ma goi het han:loi'; return; end if;

b_loi:='loi:Sai ma doi tuong:loi';

if b_ma_dt is null then return; end if;

b_ma_dt:=PKH_MA_TENl(b_ma_dt);

if FBH_PHH_DTUONG_HAN(b_ma_dt)<>'C' then return; end if;

b_loi:='loi:Sai muc rui ro:loi';

if b_mrr is null then return; end if;

b_mrr:=PKH_MA_TENl(b_mrr);

if FBH_PHH_MRR_HAN(b_mrr)<>'C' then return; end if;

b_nt_tien:=nvl(trim(b_nt_tien),'VND'); b_nt_phi:=nvl(trim(b_nt_phi),'VND');

if b_nt_phi<>'VND' then b_tp:=2; end if;

FBH_PHHG_PHI(dt_ct,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_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 bi thay doi:loi'; return;

end if;

PBH_HD_THAY_PHIg(b_nt_tien,b_nt_tien,b_tygia,b_thueH,b_ttoanH,dk_lh_nv,dk_tien,dk_phi,dk_thue,dk_ttoan,b_loi);

if b_loi is not null then return; end if;

for b_lp in 1..dk_ma.count loop

    if dk_phiB(b_lp)>dk_phi(b_lp) then

        dk_phiG(b_lp):=dk_phiB(b_lp)-dk_phi(b_lp);

        dk_ptG(b_lp):=round(dk_phiG(b_lp)*100/dk_phiB(b_lp),2);

    else

        dk_ptG(b_lp):=0; dk_phiG(b_lp):=0;

    end if;

end loop;

b_ng_huong:='';

if b_ttrang in('T','D') then

    b_lenh:=FKH_JS_LENH('loai_khh,tenh,dchih,cmth,mobih,emailh');

    EXECUTE IMMEDIATE b_lenh into b_loai_khH,b_tenH,b_dchiH,b_cmtH,b_mobiH,b_emailH using dt_ct;

    if trim(b_tenH) is not null then

        select json_object('loai' value b_loai_khH,'ten' value b_tenH,'cmt' value b_cmtH,

            'dchi' value b_dchiH,'mobi' value b_mobiH,'email' value b_emailH) into b_txt from dual;

        PBH_DTAC_MA_NH(b_txt,b_ma_khH,b_loi,b_ma_dvi,b_nsd);

        b_ng_huong:=b_tenH;

        if trim(b_cmtH) is not null then

            if b_loai_khH='C' then

                b_ng_huong:=b_tenH||', so CMT/CCCD: '||b_cmtH;

            else

                b_ng_huong:=b_tenH||', ma thue : '||b_cmtH;

            end if;

        end if;

        if trim(b_dchiH) is not null then

            b_ng_huong:=b_tenH||', dia chi: '||b_dchiH;

        end if;

    end if;

    select count(*) into b_i1 from bh_phh_phi_txt where so_id=b_so_idP and loai='dt_khd';

    if b_i1<>0 then

        select txt into b_txt from bh_phh_phi_txt where so_id=b_so_idP and loai='dt_khd';

        dt_khd:=FKH_JS_BONH(b_txt);

        PBH_PHHG_KHD(dt_ct,dt_dk,dt_khd,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;

/