AAA

PBH_MA_MRR_XLY hàm xử lý
tổng hợp lại số liệu
create or replace procedure PBH_SLI_LAI_VATk(b_ngayD number,b_ngayC number)
AS
b_i1 number; b_ngay_ht number; b_ngayB date; b_ngayK date;
begin
-- Dan - kiem ngay sai
delete temp_1; commit;
if b_ngayD>b_ngayC then return; end if;
b_ngayB:=PKH_SO_CDT(b_ngayD); b_ngayK:=PKH_SO_CDT(b_ngayC);
loop
b_ngay_ht:=PKH_NG_CSO(b_ngayB);
insert into temp_1(c1) select to_char(b_ngayB,'dd/mm/yyyy') from bh_hd_goc_ttpt where
ngay_ht=b_ngay_ht and (thue<>0 or (thue=0 and phi<>ttoan)) and round((phi*1.1-ttoan)1000/ttoan,0)>0;
exit when b_ngayB=b_ngayK;
b_ngayB:=b_ngayB+1;
end loop;
end;
/
create or replace procedure PBH_SLI_LAI_VATs(b_ngayD number,b_ngayC number)
AS
b_i1 number; b_ma_dvi varchar2(10); b_tp number;
b_ngayB date; b_ngayK date; b_ngay_ht number;
a_so_id_tt pht_type.a_num; a_bt pht_type.a_num; a_ttoan pht_type.a_num; a_ttoan_qd pht_type.a_num;
a_phi pht_type.a_num; a_thue pht_type.a_num; a_phi_qd pht_type.a_num; a_thue_qd pht_type.a_num;
a_hhong_tl pht_type.a_num; a_htro_tl pht_type.a_num; a_dvu_tl pht_type.a_num;
a_hhong pht_type.a_num; a_htro pht_type.a_num; a_dvu pht_type.a_num;
a_hhong_qd pht_type.a_num; a_htro_qd pht_type.a_num; a_dvu_qd pht_type.a_num;
begin
-- Dan - Tong hop lai VAT
if b_ngayD>b_ngayC then return; end if;
b_ngayB:=PKH_SO_CDT(b_ngayD); b_ngayK:=PKH_SO_CDT(b_ngayC);
loop
b_ngay_ht:=PKH_NG_CSO(b_ngayB);
for r_lp in(select ma from ht_ma_dvi) loop
b_ma_dvi:=r_lp.ma;
select so_id_tt,bt,thue,ttoan,ttoan_qd,hhong_tl,htro_tl,dvu_tl bulk collect
into a_so_id_tt,a_bt,a_thue,a_ttoan,a_ttoan_qd,a_hhong_tl,a_htro_tl,a_dvu_tl
from bh_hd_goc_ttpt where ma_dvi=b_ma_dvi and ngay_ht=b_ngay_ht and
(thue<>0 or (thue=0 and phi<>ttoan)) and round((phi1.1-ttoan)*1000/ttoan,0)>0;
for b_lp in 1..a_so_id_tt.count loop
if a_thue(b_lp)=0 then
a_phi(b_lp):=a_ttoan(b_lp); a_phi_qd(b_lp):=a_ttoan_qd(b_lp);
else
if a_ttoan_qd(b_lp)=a_ttoan(b_lp) then
b_tp:=0;
a_phi(b_lp):=round(a_ttoan(b_lp)/1.1,0);
a_phi_qd(b_lp):=a_phi(b_lp);
else
b_tp:=2;
a_phi(b_lp):=round(a_ttoan(b_lp)/1.1,2);
a_phi_qd(b_lp):=round(a_ttoan_qd(b_lp)/1.1,0);
end if;
a_thue(b_lp):=a_ttoan(b_lp)-a_phi(b_lp);
a_thue_qd(b_lp):=a_ttoan_qd(b_lp)-a_phi_qd(b_lp);
end if;
a_hhong(b_lp):=round(a_phi(b_lp)*a_hhong_tl(b_lp)/100,b_tp);
a_htro(b_lp):=round(a_phi(b_lp)*a_htro_tl(b_lp)/100,b_tp);
a_dvu(b_lp):=round(a_phi(b_lp)*a_dvu_tl(b_lp)/100,b_tp);
a_hhong_qd(b_lp):=round(a_phi_qd(b_lp)*a_hhong_tl(b_lp)/100,0);
a_htro_qd(b_lp):=round(a_phi_qd(b_lp)*a_htro_tl(b_lp)/100,0);
a_dvu_qd(b_lp):=round(a_phi_qd(b_lp)a_dvu_tl(b_lp)/100,0);
end loop;
forall b_lp in 1..a_so_id_tt.count
update bh_hd_goc_ttpt set phi=a_phi(b_lp),thue=a_thue(b_lp),phi_qd=a_phi_qd(b_lp),thue_qd=a_thue_qd(b_lp),
hhong=a_hhong(b_lp),htro=a_htro(b_lp),dvu=a_dvu(b_lp),
hhong_qd=a_hhong_qd(b_lp),htro_qd=a_htro_qd(b_lp),dvu_qd=a_dvu_qd(b_lp)
where ma_dvi=b_ma_dvi and so_id_tt=a_so_id_tt(b_lp) and bt=a_bt(b_lp);
--
select so_id_tt,bt,thue,ttoan,ttoan_qd,hhong_tl,htro_tl,dvu_tl bulk collect
into a_so_id_tt,a_bt,a_thue,a_ttoan,a_ttoan_qd,a_hhong_tl,a_htro_tl,a_dvu_tl
from bh_hd_goc_ttptdt where ma_dvi=b_ma_dvi and ngay_ht=b_ngay_ht and
(thue<>0 or (thue=0 and phi<>ttoan)) and round((phi1.1-ttoan)*1000/ttoan,0)>0;
for b_lp in 1..a_so_id_tt.count loop
if a_thue(b_lp)=0 then
a_phi(b_lp):=a_ttoan(b_lp); a_phi_qd(b_lp):=a_ttoan_qd(b_lp);
else
if a_ttoan_qd(b_lp)=a_ttoan(b_lp) then
b_tp:=0;
a_phi(b_lp):=round(a_ttoan(b_lp)/1.1,0);
a_phi_qd(b_lp):=a_phi(b_lp);
else
b_tp:=2;
a_phi(b_lp):=round(a_ttoan(b_lp)/1.1,2);
a_phi_qd(b_lp):=round(a_ttoan_qd(b_lp)/1.1,0);
end if;
a_thue(b_lp):=a_ttoan(b_lp)-a_phi(b_lp);
a_thue_qd(b_lp):=a_ttoan_qd(b_lp)-a_phi_qd(b_lp);
end if;
a_hhong(b_lp):=round(a_phi(b_lp)*a_hhong_tl(b_lp)/100,b_tp);
a_htro(b_lp):=round(a_phi(b_lp)*a_htro_tl(b_lp)/100,b_tp);
a_dvu(b_lp):=round(a_phi(b_lp)*a_dvu_tl(b_lp)/100,b_tp);
a_hhong_qd(b_lp):=round(a_phi_qd(b_lp)*a_hhong_tl(b_lp)/100,0);
a_htro_qd(b_lp):=round(a_phi_qd(b_lp)*a_htro_tl(b_lp)/100,0);
a_dvu_qd(b_lp):=round(a_phi_qd(b_lp)*a_dvu_tl(b_lp)/100,0);
end loop;
forall b_lp in 1..a_so_id_tt.count
update bh_hd_goc_ttptdt set phi=a_phi(b_lp),thue=a_thue(b_lp),phi_qd=a_phi_qd(b_lp),thue_qd=a_thue_qd(b_lp),
hhong=a_hhong(b_lp),htro=a_htro(b_lp),dvu=a_dvu(b_lp),
hhong_qd=a_hhong_qd(b_lp),htro_qd=a_htro_qd(b_lp),dvu_qd=a_dvu_qd(b_lp)
where ma_dvi=b_ma_dvi and so_id_tt=a_so_id_tt(b_lp) and bt=a_bt(b_lp);
end loop;
commit;
exit when b_ngayB=b_ngayK;
b_ngayB:=b_ngayB+1;
end loop;
exception when others then rollback;
end;
/