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_kt is null or b_ngay_kt in(0,30000101) or b_ngay_kt<b_ngay_hl or b_ngay_kt>b_ngay_ktH then
    b_ngay_kt:=b_ngay_ktH;
end if;
if b_ngay_hl is null or b_ngay_hl in(0,30000101) or b_ngay_hl<b_ngay_hlH or b_ngay_hl>b_ngay_kt then
    b_ngay_hl:=b_ngay_hlH;
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_NH_NH(
    b_ma_dvi varchar2,b_nsd varchar2,b_so_id number,
    dt_ct in out clob,dt_dk clob,dt_dkbs clob,dt_pvi clob,dt_lt clob,dt_kbt clob,dt_ttt clob,
-- Chung
    b_so_hd varchar2,b_ngay_ht number,b_ttrang varchar2,b_kieu_hd varchar2,b_so_hd_g varchar2,
    b_kieu_kt varchar2,b_ma_kt varchar2,b_kieu_gt varchar2,b_ma_gt varchar2,b_ma_cb varchar2,
    b_so_hdL varchar2,b_loai_kh varchar2,b_ma_kh varchar2,b_ten nvarchar2,b_dchi nvarchar2, 
    b_cmt varchar2,b_mobi varchar2,b_email varchar2,
    b_gio_hl varchar2,b_ngay_hl number,b_gio_kt varchar2,b_ngay_kt number,b_ngay_cap number,
    b_nt_tien varchar2,b_nt_phi varchar2,b_c_thue varchar2,
    b_phi number,b_giam number,b_thue number,b_ttoan number,b_hhong number,
    b_so_idG number,b_so_idD number,b_ngayD number,b_phong varchar2,
    tt_ngay pht_type.a_num,tt_tien pht_type.a_num,
-- Rieng
    b_ng_huong nvarchar2,b_ma_sp varchar2,b_cdich varchar2,b_goi varchar2,b_so_idP number,
    b_dvi nvarchar2,b_ddiem nvarchar2,b_kvuc varchar2,b_tdx number,b_tdy number,b_bk number,
    b_ma_dt varchar2,b_lvuc varchar2,b_mrr varchar2,b_dk_lut varchar2,b_hs_lut number,b_tlbt number,
    dk_ma pht_type.a_var,dk_ten pht_type.a_nvar,dk_tc pht_type.a_var,
    dk_ma_ct pht_type.a_var,dk_kieu pht_type.a_var,
    dk_lkeM pht_type.a_var,dk_lkeP pht_type.a_var,dk_lkeB pht_type.a_var,dk_luy pht_type.a_var,
    dk_ma_dk pht_type.a_var,dk_ma_dkC pht_type.a_var,dk_lh_nv pht_type.a_var,dk_t_suat pht_type.a_num,
    dk_cap pht_type.a_num,dk_lbh pht_type.a_var,dk_nv pht_type.a_var,
    dk_tien pht_type.a_num,dk_pt pht_type.a_num,dk_phi pht_type.a_num,
    dk_thue pht_type.a_num,dk_ttoan pht_type.a_num,
    dk_ptB pht_type.a_num,dk_phiB pht_type.a_num,
    dk_ptG pht_type.a_num,dk_phiG pht_type.a_num,
    dk_pvi_ma pht_type.a_var,dk_pvi_tc pht_type.a_var,dk_pvi_ktru pht_type.a_var,
    cdt_pthuc pht_type.a_var,b_loi out varchar2)
AS
    b_i1 number; b_so_id_kt number:=-1; b_tien number:=0; b_txt clob;
begin
-- Dan - Nhap
b_loi:='loi:Loi Table bh_phh:loi';
if b_ttrang='D' then b_so_id_kt:=0; end if;
b_loi:='loi:Loi Table bh_phh_dk:loi';
for b_lp in 1..dk_lh_nv.count loop
    if dk_lh_nv(b_lp)<>' ' and dk_kieu(b_lp)='T' then b_tien:=b_tien+dk_tien(b_lp); end if;
    insert into bh_phh_dk values(b_ma_dvi,b_so_id,b_so_idD,b_lp,
        dk_ma(b_lp),dk_ten(b_lp),dk_tc(b_lp),dk_ma_ct(b_lp),dk_kieu(b_lp),dk_tien(b_lp),dk_pt(b_lp),dk_phi(b_lp),dk_cap(b_lp),
        dk_ma_dk(b_lp),dk_ma_dkC(b_lp),dk_lh_nv(b_lp),dk_t_suat(b_lp),dk_thue(b_lp),dk_ttoan(b_lp),
        dk_ptB(b_lp),dk_phiB(b_lp),dk_ptG(b_lp),dk_phiG(b_lp),dk_lkeM(b_lp),dk_lkeP(b_lp),dk_lkeB(b_lp),
        dk_luy(b_lp),dk_pvi_ma(b_lp),dk_pvi_tc(b_lp),dk_pvi_ktru(b_lp));
end loop;
insert into bh_phh values(
    b_ma_dvi,b_so_id,b_so_hd,b_ngay_ht,'G',b_ttrang,b_kieu_hd,b_so_hd_g,b_kieu_kt,b_ma_kt,b_kieu_gt,b_ma_gt,b_ma_cb,
    b_phong,b_so_hdL,b_loai_kh,b_ma_kh,b_ten,b_dchi,b_cmt,b_mobi,b_email,b_gio_hl,b_ngay_hl,b_gio_kt,b_ngay_kt,b_ngay_cap,
    b_nt_tien,b_nt_phi,b_c_thue,b_ma_sp,b_cdich,b_goi,1,b_tien,
    b_phi,b_giam,b_thue,b_ttoan,b_hhong,b_so_idG,b_so_idD,'','',b_so_id_kt,b_nsd,sysdate);
b_loi:='loi:Loi Table bh_phh_dvi:loi';
insert into bh_phh_dvi values(b_ma_dvi,b_so_id,b_so_idD,0,b_kieu_hd,b_so_hd,b_so_hd_g,
    b_dvi,b_ma_dt,b_kvuc,b_lvuc,b_ddiem,b_dk_lut,b_hs_lut,b_mrr,b_tdx,b_tdy,b_bk,
    b_gio_hl,b_ngay_hl,b_gio_kt,b_ngay_kt,b_ngay_cap,
    b_so_idP,b_giam,b_phi,b_thue,b_ttoan,b_tlbt);
insert into bh_hd_goc_ttdt values(b_ma_dvi,b_so_id,b_so_idD,'PHH',b_dvi,b_ma_kh,b_ngay_kt,b_ddiem);
for b_lp in 1..tt_ngay.count loop
    insert into bh_phh_tt values(b_ma_dvi,b_so_id,tt_ngay(b_lp),tt_tien(b_lp));
end loop;
for b_lp in 1..cdt_pthuc.count loop
    insert into bh_phh_cdt values(b_ma_dvi,b_so_id,cdt_pthuc(b_lp),b_lp);
end loop;
b_loi:='loi:Loi Table bh_phh_txt:loi';
PKH_JS_THAYa(dt_ct,'so_hd,ma_kh',b_so_hd||','||b_ma_kh);
insert into bh_phh_txt values(b_ma_dvi,b_so_id,'dt_ct','"'||dt_ct||'"');
insert into bh_phh_txt values(b_ma_dvi,b_so_id,'dt_dk','"'||dt_dk||'"');
insert into bh_phh_txt values(b_ma_dvi,b_so_id,'dt_pvi','"'||dt_pvi||'"');
if dt_dkbs is not null then
    insert into bh_phh_txt values(b_ma_dvi,b_so_id,'dt_dkbs','"'||dt_dkbs||'"');
end if;
if dt_ttt is not null then
    insert into bh_phh_txt values(b_ma_dvi,b_so_id,'dt_ttt','"'||dt_ttt||'"');
end if;
if dt_lt is not null then
    insert into bh_phh_txt values(b_ma_dvi,b_so_id,'dt_lt','"'||dt_lt||'"');
end if;
if dt_kbt is not null then
    insert into bh_phh_txt values(b_ma_dvi,b_so_id,'dt_kbt','"'||dt_kbt||'"');
end if;
if b_ttrang in('T','D') then
    select JSON_ARRAYAGG(json_object(
        ma,ten,tc,ma_ct,tien,pt,phi,cap,ma_dk,ma_dkC,lh_nv,t_suat,thue,ttoan,ptB,ptG,phiG,lkeM,lkeP,lkeB,luy,pvi_ma,pvi_ktru,pvi_tc)
        order by bt returning clob) into b_txt
        from bh_phh_dk where ma_dvi=b_ma_dvi and so_id=b_so_id;
    insert into bh_phh_kbt values(b_ma_dvi,b_so_id,b_so_idD,'"'||b_txt||'"','"'||dt_lt||'"','"'||dt_kbt||'"');
    PBH_PHH_TTU(b_ma_dvi,b_so_id,b_loi);
    select json_object('ma_dvi' value b_ma_dvi,'nsd' value b_nsd,'so_id' value b_so_id,'so_hd' value b_so_hd,
        'kieu_hd' value b_kieu_hd,'nv' value 'PHH','ngay_ht' value b_ngay_ht,'ngay_cap' value b_ngay_cap,
        'ngay_hl' value b_ngay_hl,'ngay_kt' value b_ngay_kt,'cb_ql' value b_ma_cb,'phong' value b_phong,
        'kieu_kt' value b_kieu_kt,'ma_kt' value b_ma_kt,'dly_tke' value ' ','hhong' value b_hhong,
        'pt_hhong' value 'D','ma_kh' value b_ma_kh,'ten' value b_ten,'kieu_gt' value b_kieu_gt,
        'ma_gt' value b_ma_gt,'so_id_d' value b_so_idD,'so_id_g' value b_so_idG,'ttrang' value b_ttrang,
        'dvi_ksoat' value b_ma_dvi,'ksoat' value b_nsd,'bangg' value 'bh_phh',
        'c_thue' value b_c_thue,'nt_tien' value b_nt_tien,'nt_phi' value b_nt_phi,'tien' value b_tien,
        'phi' value b_ttoan-b_thue,'thue' value b_thue returning clob) into b_txt from dual;
    PBH_HD_GOC_NH(b_txt,b_loi);
    if b_loi is not null then return; end if;
end if;
b_loi:='';
exception when others then if b_loi is null then raise PROGRAM_ERROR; else null; end if;
end;
/