Calcul Venit SQL

Alter
procedure [dbo].[psCalcul_venit_net]
@DataJ datetime, @DataS datetime, @MarcaJos char(6), @LocmJos char(9), @Inversare int
As
Begin
declare @HostID char(8), @Salar_minim float, @Indref_somaj float, @Ore_luna int,
@Sind_proc int, @Proc_sind float, @pCAS_ind float, @pCASS_ind float, @pSomaj_ind float, @pSomajI float, @pCAS_gr3 float, @pCAS_gr2 float, @pCAS_gr1 float,
@pCCI float, @Coef_CAS float, @Coef_CCI float, @pCASSU float, @pSomajU float, @pFond_gar float, @pFambp float, @Calcul_ITM int, @pITM float,
@Buget int, @Inst_publ int, @CASS_colab int, @NuITM_colab int, @NuITM_pens int,
@CCI_colabP int, @CCI_colabO int, @Chind_pont int, @Chind_lunacrt int, @Chind_vnet int, @NuRoT_BI int,
@Comp_sal_net int, @Salar_net_valuta int,
@NuCAS_H int, @NuCASS_H int, @Imps_H int, @CASSimps_K int, @Somaj_K int, @NuASS_J int, @CAS_J int, @NuASS_N int, @NuASSA_N int, @CorU_RP int, @CAS_U int,
@Venit_brut_cu_ded float, @Venit_brut_fara_ded float, @Dafora int, @Pasmatex int, @Drumor int, @Plastidr int
Set @HostID=isnull((select convert(char(8), abs(convert(int, host_id())))),'')
Set @Salar_minim=dbo.iauParLN(@DataS,'PS','S-MIN-BR')
Set @Indref_somaj=dbo.iauParLN(@DataS,'PS','SOMAJ-ISR')
Set @Ore_luna=dbo.iauParLN(@DataS,'PS','ORE_LUNA')
Exec Luare_date_par 'PS', 'SIND%', @Sind_proc OUTPUT, @Proc_sind OUTPUT, ''
Set @pCAS_ind=dbo.iauParLN(@DataS,'PS','CASINDIV')
Set @pCASS_ind=dbo.iauParLN(@DataS,'PS','CASSIND')
Set @pSomaj_ind=dbo.iauParLN(@DataS,'PS','SOMAJIND')
Set @pCAS_gr3=dbo.iauParLN(@DataS,'PS','CASGRUPA3')-dbo.iauParLN(@DataS,'PS','CASINDIV')
Set @pCAS_gr2=dbo.iauParLN(@DataS,'PS','CASGRUPA2')-dbo.iauParLN(@DataS,'PS','CASINDIV')
Set @pCAS_gr1=dbo.iauParLN(@DataS,'PS','CASGRUPA1')-dbo.iauParLN(@DataS,'PS','CASINDIV')
Set @pCCI=dbo.iauParLN(@DataS,'PS','COTACCI')
Set @Coef_CAS=dbo.iauParN('PS','COEFCAS')
Set @Coef_CAS=@Coef_CAS/1000000
Set @Coef_CCI=dbo.iauParN('PS','COEFCCI')
Set @Coef_CCI=@Coef_CCI/1000000
Set @pCASSU=dbo.iauParLN(@DataS,'PS','CASSUNIT')
Set @pSomajU=dbo.iauParLN(@DataS,'PS','3.5%SOMAJ')
Set @pFond_gar=dbo.iauParLN(@DataS,'PS','FONDGAR')
Set @pFambp=dbo.iauParLN(@DataS,'PS','0.5%ACCM')
Set @Calcul_ITM=dbo.iauParL('PS','1%-CAMERA')
Set @pITM=dbo.iauParLN(@DataS,'PS','1%-CAMERA')
Set @Buget=dbo.iauParL('PS','UNITBUGET')
Set @Inst_publ=dbo.iauParL('PS','INSTPUBL')
Set @CASS_colab=dbo.iauParL('PS','CALFASC')
Set @NuITM_colab=dbo.iauParL('PS','NCALPCMC')
Set @NuITM_pens=dbo.iauParL('PS','NCALPCMPE')
Set @CCI_colabP=dbo.iauParL('PS','CCICOLAB')
Set @CCI_colabO=dbo.iauParL('PS','CCICOLABO')
Set @Chind_pont=dbo.iauParL('PS','CHINDPON')
Set @Chind_lunacrt=dbo.iauParL('PS','CHINDLCRT')
Set @Chind_vnet=dbo.iauParL('PS','CHINDVEN')
Set @NuRoT_BI=dbo.iauParL('PS','BAZANEROT')
Set @Comp_sal_net=dbo.iauParL('PS','COMPSALN')
Set @Salar_net_valuta=dbo.iauParL('PS','SALNETV')
Set @NuCAS_H=dbo.iauParL('PS','NUCAS-H')
Set @NuCASS_H=dbo.iauParL('PS','NUASS-H')
Set @Imps_H=dbo.iauParL('PS','IMPSEP-H')
Set @NuASS_J=dbo.iauParL('PS','NUASS-J')
Set @CAS_J=dbo.iauParL('PS','CAS-J')
Set @CASSimps_K=dbo.iauParL('PS','ASSIMPS-K')
Set @Somaj_K=dbo.iauParL('PS','SOMAJ-K')
Set @NuASS_N=dbo.iauParL('PS','NUASS-N')
Set @NuASSA_N=dbo.iauParL('PS','NUASSA-N')
Set @CorU_RP=dbo.iauParL('PS','ADRPL-U')
Set @CAS_U=dbo.iauParL('PS','CALCAS-U')
Set @Venit_brut_cu_ded=dbo.iauParN('PS','VBCUDEDP')
Set @Venit_brut_fara_ded=dbo.iauParN('PS','VBFDEDP')
Set @Dafora=dbo.iauParL('SP','DAFORA')
Set @Pasmatex=dbo.iauParL('SP','PASMATEX')
Set @Drumor=dbo.iauParL('SP','DRUMOR')
Set @Plastidr=dbo.iauParL('SP','PLASTIDR')
declare @Data datetime, @Marca char(6), @Ore_lucrate int, @Ore_noapte int,
@ore_RN int, @Ore_IT int, @Ore_obligatii int, @Ore_CFS int, @Ore_CO int, @Ore_CM int, @Invoiri int, @Nemotivate int, @Ore_supl int,
@Ore_justif int, @Baza_somaj_1 float, @Ind_FAMBP float, @CMunitate float, @CMcas float,
@Diurna float, @Suma_impoz float, @Cons_admin float, @Suma_imp_sep float,
@Aj_deces float, @Venit_total float, @Spor_specific float, @Spor_cond_1 float, @Spor_cond_2 float,
@Spor_cond_3 float, @Spor_cond_4 float, @Spor_cond_5 float, @Spor_cond_6 float,
@Salar_orar float, @RegimL float, @Locm char(9),
@CorQ float, @CorT float, @CorU float, @Ret_sindicat float,
@RegimL_pontaj float, @RegimL_brut float, @GrupaM_pontaj char(1), @Somaj_1P int, @As_sanatateP float,
@Tip_impozitareP char(1), @GrupaMP char(1), @Salar_de_incadrareP float,
@Salar_de_bazaP float, @Tip_salarizareP char(1), @Ind_condP float,
@Spor_vechimeP float, @Spor_de_noapteP float, @Spor_sist_prgP float,
@Spor_functie_suplP float, @Spor_specificP float, @Spor_conditii_1P float, @Spor_conditii_2P float,
@Spor_conditii_3P float, @Spor_conditii_4P float, @Spor_conditii_5P float, @Spor_conditii_6P float,
@Tip_colabP char(3), @Alte_surseP char(1), @Grad_invalidP char(1), @Tip_ded_somajP float,
@Data_angajarii datetime, @Plecat char(1), @Mod_angajareP char(1),
@Data_plecP datetime, @Sindicalist char(1),
@DataI_conv_somaj datetime, @DataE_conv_somaj datetime, @Nr_pers_intr int,
@Baza_cond_normale float, @Baza_cond_deosebite float, @Baza_cond_speciale float,
@Indcm_unit_19 float, @Indcm_cas_19 float, @Ore_luna_cm int, @Indcm float,
@Indcm_cas_18 float, @Zcm_18 int, @Zcm_18_ant int, @Baza_casi_ant float,
@Baza_cascm_ant float, @Zcm_2341011 int, @Indcm_234 float, @Indcm_unit_234 float,
@Zcm15 int, @Zcm_8915 int, @Indcm_8915 float, @Zcm_78 int, @Indcm_78 float, @Indcm_somaj float,
@Ingrijire_copil_sarcina int, @Actionar char(1),
@uMarca2CNP int, @uMarca2CNPCM int, @CNP char(13),
@Pensie_max_ded float, @Pensie_ded_lunar float, @Pensie_ded_ant float, @Pensie_luna float,
@Salarii_comp float, @Alocatii_hrana float, @Somaj_tehnic float, @Ore_somaj_tehnic int, @Suma_neimp float

declare @Baza_CASCM float, @Baza_CASCM_CN float, @Baza_CASCM_CD float, @Baza_CASCM_CS float, @CASCM float,
@Baza_CAS_CN float, @Baza_CAS_CD float, @Baza_CAS_CS float, @CCI_Fambp float,
@Ore_somaj int, @Zile_CM_susp int, @IndCM_susp float, @Baza_somajI float, @SomajI float, @Baza_CASSI float, @CASSI float, @CASS_Fambp_sal float, @CASS_Fambp_ang float,
@Baza_CAS_ind float, @CAS_ind float, @Venit_deducere float, @Ded_baza decimal(10), @Venit_net_in_imp float,
@Ded_pensie float, @Ded_sindicat float, @Venit_baza float, @Impozit float, @Baza_impO float, @Impozit_sep float,
@Baza_impH float, @Impozit_H float, @Baza_N float, @VENIT_NET float,
@CAS_unit float, @Baza_CASS_unit float, @CASS_unit float, @Baza_somajU float, @Somaj_unit float,
@Baza_CCI float, @CCI float, @Baza_Fambp float, @Fambp float, @Baza_FondGar float, @Fond_gar float, @ITM float,
@Rest_de_plata float, @Coef_tot_ded int, @Zile_ded_somaj int, @ZileSC_ded_somaj int, @Ded_somaj float,
@Baza_ITM float, @Baza_CAS_cond_normale float, @Baza_CAS_cond_deoseb float, @Baza_CAS_cond_spec float

exec psCreare_cursor_venit_net @DataJ, @DataS, @MarcaJos, @LocmJos

declare cursor_net cursor for
Select Data, Marca, Ore_lucrate, Ore_noapte, Ore_RN, Ore_IT, Ore_obligatii,
Ore_CFS, Ore_CO, Ore_CM, Invoiri, Nemotivate, Ore_supl, Ore_justif,
Baza_somaj_1, Ind_FAMBP, CMunitate, CMCAS, Diurna, Suma_impoz, Cons_admin,
Suma_imp_sep, Aj_deces, Venit_total, Spor_specific, Spor_cond_1, Spor_cond_2, Spor_cond_3, Spor_cond_4, Spor_cond_5, Spor_cond_6,
Salar_orar, RegimL, Locm, CorQ, CorT, CorU, Ret_sindicat, RegimL_pontaj, GrupaM_pontaj,
Somaj_1P, As_sanatateP, Tip_impozitareP, GrupaM_pers, Salar_de_incadrareP, Salar_de_bazaP,
Tip_salarizareP, Ind_condP, Spor_vechimeP, Spor_de_noapteP, Spor_sist_prgP, Spor_functie_suplP,
Spor_specificP, Spor_conditii_1P, Spor_conditii_2P, Spor_conditii_3P, Spor_conditii_4P,
Spor_conditii_5P, Spor_conditii_6P, Tip_colabP, Alte_surseP, Grad_invalidP, Tip_ded_somajP,
Data_angajarii, Plecat, Mod_angajareP, Data_plecP, Sindicalist,
DataI_conv_somaj, DataE_conv_somaj, Nr_pers_intr,
Baza_cond_normale, Baza_cond_deosebite, Baza_cond_speciale,
Indcm_unit_19, Indcm_cas_19, Ore_luna_cm, Indcm, Indcm_cas_18, Zcm_18, Zcm_18_ant, Baza_casi_ant,
Baza_cascm_ant, Zcm_2341011, Indcm_234, Indcm_unit_234, Zcm15, Zcm_8915, Indcm_8915,
Zcm_78, Indcm_78, Indcm_somaj, Ingrijire_copil_sarcina, Actionar,
uMarca2CNP, uMarca2CNPCM, CNP, Pensie_max_ded, Pensie_ded_lunar,
Pensie_ded_ant, Pensie_luna, Salarii_comp, Alocatii_hrana, Somaj_tehnic, Ore_somaj_tehnic, Suma_neimp
from ##cursor_net
where hostid=@HostID
open cursor_net
fetch next from cursor_net into @Data, @Marca, @Ore_lucrate, @Ore_noapte, @Ore_RN, @Ore_IT, @Ore_obligatii,
@Ore_CFS, @Ore_CO, @Ore_CM, @Invoiri, @Nemotivate, @Ore_supl, @Ore_justif,
@Baza_somaj_1, @Ind_FAMBP, @CMunitate, @CMCAS, @Diurna, @Suma_impoz, @Cons_admin,
@Suma_imp_sep, @Aj_deces, @Venit_total, @Spor_specific, @Spor_cond_1, @Spor_cond_2, @Spor_cond_3, @Spor_cond_4, @Spor_cond_5, @Spor_cond_6,
@Salar_orar, @RegimL, @Locm, @CorQ, @CorT, @CorU, @Ret_sindicat, @RegimL_pontaj, @GrupaM_pontaj,
@Somaj_1P, @As_sanatateP, @Tip_impozitareP, @GrupaMP, @Salar_de_incadrareP, @Salar_de_bazaP,
@Tip_salarizareP, @Ind_condP, @Spor_vechimeP, @Spor_de_noapteP, @Spor_sist_prgP, @Spor_functie_suplP,
@Spor_specificP, @Spor_conditii_1P, @Spor_conditii_2P, @Spor_conditii_3P, @Spor_conditii_4P,
@Spor_conditii_5P, @Spor_conditii_6P, @Tip_colabP, @Alte_surseP, @Grad_invalidP, @Tip_ded_somajP,
@Data_angajarii, @Plecat, @Mod_angajareP, @Data_plecP, @Sindicalist,
@DataI_conv_somaj, @DataE_conv_somaj, @Nr_pers_intr,
@Baza_cond_normale, @Baza_cond_deosebite, @Baza_cond_speciale,
@Indcm_unit_19, @Indcm_cas_19, @Ore_luna_cm, @Indcm, @Indcm_cas_18, @Zcm_18, @Zcm_18_ant, @Baza_casi_ant,
@Baza_cascm_ant, @Zcm_2341011, @Indcm_234, @Indcm_unit_234, @Zcm15, @Zcm_8915, @Indcm_8915,
@Zcm_78, @Indcm_78, @Indcm_somaj, @Ingrijire_copil_sarcina, @Actionar,
@uMarca2CNP, @uMarca2CNPCM, @CNP, @Pensie_max_ded, @Pensie_ded_lunar,
@Pensie_ded_ant, @Pensie_luna, @Salarii_comp, @Alocatii_hrana, @Somaj_tehnic, @Ore_somaj_tehnic, @Suma_neimp
While @@fetch_status = 0
Begin
update net set Venit_total=0, VENIT_NET=0, Impozit=0, Rest_de_plata=0, Asig_sanatate_din_net=0,
Pensie_suplimentara_3=0, Somaj_1=0, Asig_sanatate_din_impozit=0, Asig_sanatate_din_CAS=0, Coef_tot_ded=0,
VEN_NET_IN_IMP=0, Ded_baza=0, Ded_suplim=0, VENIT_BAZA=0, Chelt_prof=0,
Debite_externe=0, Rate=0, Debite_interne=0, Cont_curent=0, CAS=0, Somaj_5=0, Fond_de_risc_1=0,
Camera_de_munca_1=0, Asig_sanatate_pl_unitate=0, Baza_CAS=0
where Data=@Data and Marca=@Marca
update net set Somaj_5=0, Ded_baza=0, Ded_suplim=0
where Data=dbo.bom(@Data) and Marca=@Marca
Set @Baza_CASCM=(case when @uMarca2CNPCM>0 and charindex(@GrupaMP,'NDS')=0 then 0 else
(@Zcm_18+@Zcm15-@Zcm_18_ant)*@Salar_minim/((case when @Dafora=1 then @Ore_luna_cm else @Ore_luna end)/8)+@Baza_cascm_ant end)
Set @Baza_CASCM_CN=(case when @GrupaM_pontaj='N' or @GrupaMP in ('N','P') then @Baza_CASCM else 0 end)
Set @Baza_CASCM_CD=(case when @GrupaM_pontaj='D' or @GrupaMP='D' then @Baza_CASCM else 0 end)
Set @Baza_CASCM_CS=(case when @GrupaM_pontaj='S' or @GrupaMP='S' then @Baza_CASCM else 0 end)
Set @CASCM=round(@Baza_CASCM_CS*@pCAS_gr1/100+@Baza_CASCM_CD*@pCAS_gr2/100+@Baza_CASCM_CN*@pCAS_gr3/100,0)
Set @CCI_Fambp=round(@Ind_FAMBP*@pCCI/100,0)
Set @Baza_CAS_cond_normale=round(@Baza_cond_normale*@Coef_CAS,0)
Set @Baza_CAS_cond_deoseb=round(@Baza_cond_deosebite*@Coef_CAS,0)
Set @Baza_CAS_cond_spec=round(@Baza_cond_speciale*@Coef_CAS,0)
Select @Zile_CM_susp=0, @IndCM_susp=0
if @Indcm<>0 or @Zcm_18+@Zcm_2341011+@Zcm15<>0
Begin
exec calcul_ZileCM_suspendare @Marca, @DataJ, @DataS, @Zile_CM_susp output, @IndCM_susp output
End
if @Venit_total>0
Begin
Set @Ore_somaj=@Ore_justif-@Zcm_78*@RegimL-(case when @Zile_CM_susp>0 then @Zile_CM_susp*@RegimL else 0 end)
-@Ore_CFS-@Invoiri-@Nemotivate-@Ore_somaj_tehnic
Set @pSomajI=(case when @Somaj_1P=1 then @pSomaj_ind/100 else 0 end)
Set @Baza_somajI=@Venit_total-@Indcm_78-(case when @IndCM_susp=0 then @Indcm_somaj else @IndCM_susp end)-@Salarii_comp-(case when @NuCAS_H=1 then @Suma_impoz else 0 end)
-(case when @CASSimps_K=1 and @Somaj_K=0 then @Cons_admin else 0 end)-@Somaj_tehnic
Select @Baza_somajI=0 where @Ore_somaj=0
Set @SomajI=(case when @Baza_somajI*@pSomajI>0 and @Baza_somajI*@pSomajI<1 then 1 else round(@Baza_somajI*@pSomajI,0) end) End Set @Baza_CASSI=round(@Venit_total-(case when not((@Comp_sal_net=1 or @Salar_net_valuta=1) and @Inversare=1) then @Indcm_unit_19+@CMUnitate+@Indcm_cas_19+@CMCAS else 0 end) -(case when @NuASS_J=1 then @Diurna else 0 end)-(case when @NuCASS_H=1 then @Suma_impoz else 0 end)-(case when @NuASSA_N=1 then @Suma_neimp else 0 end)-@Somaj_tehnic,0) Set @CASSI=(case when @As_sanatateP<>0 then (case when @Baza_CASSI*@As_sanatateP/10/100>0 and @Baza_CASSI*@As_sanatateP/10/100<1 then 1 else round(@Baza_CASSI*@As_sanatateP/10/100,0) end)+@CorT end) Set @CASS_Fambp_sal=round(@Ind_Fambp*@pCASS_ind/100,0) Set @CASS_Fambp_ang=round(@Indcm_unit_234*@pCASS_ind/100,0) Select @Baza_CAS_ind=@Venit_total-(case when not((@Comp_sal_net=1 or @Salar_net_valuta=1) and @Inversare=1) then @Indcm_unit_19+@CMUnitate+@Indcm_cas_19+@CMCAS else 0 end) -(case when @CAS_J=1 or @Pasmatex=1 then 0 else (case when @Dafora=1 then -1 else 1 end)*@Diurna end) -(case when @CASSimps_K=1 and @GrupaMP<>'P' then @Cons_admin else 0 end)-(case when @NuCAS_H=1 then @Suma_impoz else 0 end)+
(case when @CAS_U=1 then @CorU else 0 end)-@Somaj_tehnic
where @Baza_CAS_cond_normale+@Baza_CAS_cond_deoseb+@Baza_CAS_cond_spec<>0
Set @Baza_CAS_ind=round(@Baza_CAS_ind+@Baza_CASCM,0)
Set @CAS_ind=(case when (@Baza_CAS_ind-@Baza_CASI_ant)*@pCAS_ind/100+@Baza_CASI_ant*@pCAS_ind/100>0 and
(@Baza_CAS_ind-@Baza_CASI_ant)*@pCAS_ind/100+@Baza_CASI_ant*@pCAS_ind/100<1 then 1 else round((@Baza_CAS_ind-@Baza_CASI_ant)*@pCAS_ind/100,0)+round(@Baza_CASI_ant*@pCAS_ind/100,0) end) -- Deducere personala Set @Ded_baza=0 if @GrupaMP not in ('O','P') and @Tip_colabP<>'FDP'
Begin
Set @Venit_deducere=@Venit_total-(case when @Imps_H=1 then @Suma_impoz else 0 end)-(case when @Buget=1 and @Actionar='1' then @Suma_imp_sep else 0 end)
exec calcul_deducere @Venit_deducere, @Nr_pers_intr, @Ded_baza output
If @Ore_justif<@Ore_luna and @Chind_pont=1 Begin Set @Ded_baza=@Ded_baza*@Ore_justif/(@Ore_luna*(case when @GrupaMP='C' then @RegimL_pontaj/8 else 1 end)) Select @Ded_baza=(round(@Ded_baza,0,1)+1)*10 where @Venit_deducere>@Venit_brut_cu_ded and @Venit_deducere<3000 and @Ded_baza>@Venit_brut_fara_ded and @Ded_baza % 10<>0
End
Select @Ded_baza=0 where day(@Data_angajarii)<>1 and month(@Data_angajarii)=month(@DataS) and year(@Data_angajarii)=year(@DataS) and @Drumor=0 and @Chind_lunacrt=0
and (@Dafora=0 or @Tip_ded_somajP=6)
End
Set @Baza_impO=(case when @Buget=1 and @Actionar='1' then round(@Suma_imp_sep*(1-@As_sanatateP/10/100-@pCAS_ind/100),0) else 0 end)
Set @Baza_impH=(case when @Imps_H=1 then round(@Suma_impoz*(case when @NuCASS_H=1 then 1 else 1-@As_sanatateP/10/100 end),0) else 0 end)
Set @Baza_N=round(@Suma_neimp*(1-(case when @NuASS_N=1 then 0 else @As_sanatateP/10/100+@pCAS_ind/100 end)),0)
Select @Venit_net_in_imp=@Venit_total-@SomajI-@CASSI-@CAS_ind-@Baza_N-@Baza_impH-@Baza_impO where @Venit_total>0
Select @Ded_baza=@Venit_net_in_imp where @Chind_vnet=1 and @Ded_baza>@Venit_net_in_imp and @Ded_baza<>0
-- Pensie facultativa
Set @Ded_sindicat=@Ret_sindicat*(case when @Sind_proc=1 and @Proc_sind>1 then 1/@Proc_sind else 1 end)
Set @Ded_pensie=0
if @Pensie_max_ded-@Pensie_ded_ant>0 and not(@Ded_baza>@Venit_net_in_imp)
Begin
Set @Ded_pensie=dbo.valoare_minima((case when @Pensie_ded_lunar=0 then @Pensie_luna else @Pensie_ded_lunar end),@Pensie_max_ded-@Pensie_ded_ant,@Ded_pensie)
Set @Ded_pensie=(case when @Venit_net_in_imp-@Ded_baza-@Ded_sindicat<@Ded_pensie then @Venit_net_in_imp-@Ded_baza-@Ded_sindicat else @Ded_pensie end) Select @Ded_pensie=0 where @Ded_pensie<0 End -- Venit baza de calcul Set @Venit_baza=@Venit_net_in_imp-@Ded_sindicat-@Ded_baza-@Ded_pensie-@Somaj_tehnic Select @Venit_baza=@Venit_baza-(@Indcm_8915-@Zcm_8915*@Salar_minim/((case when @Dafora=1 then @Ore_luna_cm else @Ore_luna end)/8)*@pCAS_ind/100) where @Indcm_8915<>0
Select @Venit_baza=0 where @Venit_baza<0 Select @Venit_baza=(case when @NuRoT_BI=1 then round(@Venit_baza,0) else round(@Venit_baza,0,1) end) -- Impozit Set @Impozit=0 Select @Impozit=dbo.fCalcul_impozit_salarii(@Venit_baza, 0, @Impozit) where @Venit_baza>0 and @Tip_impozitareP<>'3' and @Grad_invalidP not in ('1','2')
Select @Impozit=0 where @Tip_impozitareP='3' or (@Grad_invalidP in ('1','2'))
Set @Impozit_sep=0
If @Suma_imp_sep>0 and @Buget=1 and @Actionar='1' and @Tip_impozitareP<>'3' and @Grad_invalidP not in ('1','2')
Begin
Select @Impozit_sep=dbo.fCalcul_impozit_salarii(@Baza_impO,0,@Impozit_sep)
Set @Impozit=@Impozit+@Impozit_sep
Set @Venit_baza=@Venit_baza+@Baza_impO
End
If @Suma_impoz>0 and @Imps_H=1 and @Tip_impozitareP<>'3' and @Grad_invalidP not in ('1','2')
Begin
Select @Impozit_H=dbo.fCalcul_impozit_salarii(@Baza_impH,0,@Impozit_H)
Set @Impozit=@Impozit+@Impozit_H
Set @Venit_baza=@Venit_baza+@Baza_impH
End
-- Venit net
Select @Venit_net=0
Select @VENIT_NET=@Venit_net_in_imp-@Impozit+@Baza_N+@Baza_impH+@Baza_impO where @Venit_total>0
Select @VENIT_NET=@Suma_neimp where @Venit_total>0 and @NuASS_N=1
Select @VENIT_NET=@VENIT_NET+round(@CorU*(1-(case when @VENIT_NET=0 then @pCAS_ind/100 else 0 end)),0) where @CorU_RP=1 and @CAS_U=1 and @CorU<>0
Select @VENIT_NET=@Venit_net_in_imp+round(@Suma_imp_sep*(1-@As_sanatateP/10/100-@pCAS_ind/100),0) where @Suma_imp_sep>0 and @Buget=1 and @Actionar='1'
Select @VENIT_NET=0 where @Dafora=1 and @Venit_net_in_imp<0 -- Deducere somaj Set @Ded_somaj=0 if (@Mod_angajareP='N' and @Tip_ded_somajP in (1,2,3,4) or @Mod_angajareP='D' and @Tip_ded_somajP=7) and dbo.eom(@DataE_conv_somaj)>=@DataS
Begin
Select @Zile_ded_somaj=dbo.Zile_lucratoare((case when day(@DataI_conv_somaj)<>1 and month(@DataI_conv_somaj)=month(@DataS) and year(@DataI_conv_somaj)=year(@DataS) then @DataI_conv_somaj else @DataJ end),
(case when day(@DataE_conv_somaj)<>1 and month(@DataE_conv_somaj)=month(@DataS) and year(@DataE_conv_somaj)=year(@DataS) then @DataE_conv_somaj else (case when @Plecat=1 and month(@Data_plecP)=month(@DataS) and year(@Data_plecP)=year(@DataS) then @Data_plecP else @DataS end) end))
where (day(@DataI_conv_somaj)<>1 and month(@DataI_conv_somaj)=month(@DataS) and year(@DataI_conv_somaj)=year(@DataS) or day(@DataE_conv_somaj)<>1 and month(@DataE_conv_somaj)=month(@DataS) and year(@DataE_conv_somaj)=year(@DataS)
or @Plecat=1 and month(@Data_plecP)=month(@DataS) and year(@Data_plecP)=year(@DataS))
Select @ZileSC_ded_somaj=(@Ore_CM+@Ore_CFS+@Invoiri+@Nemotivate+@Ore_somaj_tehnic+(case when @Tip_ded_somajP=7 then @Ore_CO else 0 end))/(case when @RegimL=0 then 8 else @RegimL end)
Select @Ded_somaj=(case when @Tip_ded_somajP in (1,2) then 1 when @Tip_ded_somajP=3 then 1.2 when @Tip_ded_somajP=4 then 1.5 when @Tip_ded_somajP=7 then 0.5 else 0 end)*
(case when dbo.eom(@DataI_conv_somaj)=@DataS or @Tip_ded_somajP in (1,7) then
(case when @DataI_conv_somaj>='10/14/2008' and not(@Tip_ded_somajP in (2,3,4) and @Data_angajarii<'10/14/2008') then @Indref_somaj else @Salar_minim end) else dbo.iauParLN(@Data_angajarii,'PS',(case when @DataI_conv_somaj>='10/14/2008' and not(@Tip_ded_somajP in (2,3,4) and @Data_angajarii<'10/14/2008') then 'SOMAJ-ISR' else 'S-MIN-BR' end)) end)*(case when @GrupaMP='C' then @RegimL_pontaj/8 else 1 end)* (case when @Zile_ded_somaj<>0 or @ZileSC_ded_somaj<>0 then (case when @Zile_ded_somaj<>0 then (case when @Zile_ded_somaj-@ZileSC_ded_somaj>0 then @Zile_ded_somaj-@ZileSC_ded_somaj else 0 end) else @Ore_luna/8-@ZileSC_ded_somaj end)/(@Ore_luna/8) else 1 end)
End
-- Contributii angajator
Set @CAS_unit=round(@Baza_CAS_cond_spec*@pCAS_gr1/100+@Baza_CAS_cond_deoseb*@pCAS_gr2/100+@Baza_CAS_cond_normale*@pCAS_gr3/100,2)
Set @Baza_CASS_unit=@Venit_total-(@Indcm_cas_19+@CMCAS)-(case when @Plastidr=1 and @CASSimps_K=1 then @Cons_admin else 0 end)-(case when @NuCASS_H=1 then @Suma_impoz else 0 end)-@Somaj_tehnic
Select @Baza_CASS_unit=0 where (@CASS_colab=0 or @As_sanatateP=0) and @GrupaMP in ('O','P')
Set @CASS_unit=round(@Baza_CASS_unit*@pCASSU/100,2)
Set @Baza_somajU=@Baza_somajI
Select @Baza_somajU=0 where (@GrupaMP in ('O','P') or @Pasmatex=1 or @Tip_ded_somajP=5) and @Somaj_1P=0
Set @Somaj_unit=round(@Baza_somajU*@pSomajU/100,2)
Set @Baza_FondGar=@Baza_somajI
Select @Baza_FondGar=0 where @GrupaMP in ('O','P') or @Inst_publ=1
Set @Fond_gar=round(@Baza_FondGar*@pFond_gar/100,2)
Set @Baza_CCI=@Venit_total-(@Indcm_cas_19+@CMCAS)+@Ind_Fambp-(case when @NuCAS_H=1 then @Suma_impoz else 0 end)-
(case when @CASSimps_K=1 then @Cons_admin else 0 end)-@Alocatii_hrana-@Somaj_tehnic
Select @Baza_CCI=(case when @Baza_CCI>12*@Salar_minim then 12*@Salar_minim else @Baza_CCI end) where @GrupaMP='O' and @CCI_colabO=1 or @GrupaMP='P' and @CCI_colabP=1
Select @Baza_CCI=0 where @GrupaMP='O' and @CCI_colabO=0 or @GrupaMP='P' and @CCI_colabP=0
Set @CCI=round(@Baza_CCI*@pCCI/100,2)
Set @Baza_Fambp=@Baza_CAS_ind
Select @Baza_Fambp=0 where @GrupaMP='O'
Set @Fambp=round(@Baza_Fambp*@pFambp/100,2)
Set @Baza_ITM=@Venit_total-(@Indcm_cas_19+@CMCAS)-(case when @NuCAS_H=1 then @Suma_impoz else 0 end)-(case when @CASSimps_K=1 then @Cons_admin else 0 end)
Select @Baza_ITM=0 where @NuITM_colab=1 and @GrupaMP in ('O','P') or @NuITM_pens=1 and @Tip_ded_somajP=5
Set @ITM=round(@Baza_ITM*@pITM/100,2)
Set @Rest_de_plata=@Venit_net
Set @Coef_tot_ded=0

exec scriuNet_salarii @DataS, @DataS, @Marca, @Locm, @VENIT_TOTAL, 0, 0, 0, 0, 0, @Impozit, @CAS_ind, @SomajI, @CASS_Fambp_sal, @CASSI,
@Baza_somajI, @VENIT_NET, 0, 0, 0, 0, 0, 0, @REST_DE_PLATA, @CAS_unit, @Somaj_unit, @Fambp, @ITM, @CASS_unit, @Coef_tot_ded, @Grad_invalidP, @Tip_ded_somajP, @Alte_surseP,
@VENIT_NET_IN_IMP, @Ded_baza, @CCI, @VENIT_BAZA, @Ded_somaj, @Baza_CAS_ind, @Baza_CAS_cond_normale, @Baza_CAS_cond_deoseb, @Baza_CAS_cond_spec

exec scriuNet_salarii @DataJ, @DataJ, @Marca, @Locm, 0, 0, 0, 0, 0, 0, @Impozit_sep, 0, 0, @CASS_Fambp_ang, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, @CASCM, @Fond_gar, 0, 0, 0, 0, '', 0, 0,
0, @Ded_pensie, @CCI_Fambp, 0, 0, @Baza_CCI, @Baza_CASCM_CN, @Baza_CASCM_CD, @Baza_CASCM_CS
Select @Baza_CCI=@Baza_CCI*@Coef_CCI-@Ind_Fambp

fetch next from cursor_net into @Data, @Marca, @Ore_lucrate, @Ore_noapte, @Ore_RN, @Ore_IT, @Ore_obligatii,
@Ore_CFS, @Ore_CO, @Ore_CM, @Invoiri, @Nemotivate, @Ore_supl, @Ore_justif,
@Baza_somaj_1, @Ind_FAMBP, @CMunitate, @CMCAS, @Diurna, @Suma_impoz, @Cons_admin,
@Suma_imp_sep, @Aj_deces, @Venit_total, @Spor_specific, @Spor_cond_1, @Spor_cond_2, @Spor_cond_3, @Spor_cond_4, @Spor_cond_5, @Spor_cond_6,
@Salar_orar, @RegimL, @Locm, @CorQ, @CorT, @CorU, @Ret_sindicat, @RegimL_pontaj, @GrupaM_pontaj,
@Somaj_1P, @As_sanatateP, @Tip_impozitareP, @GrupaMP, @Salar_de_incadrareP, @Salar_de_bazaP,
@Tip_salarizareP, @Ind_condP, @Spor_vechimeP, @Spor_de_noapteP, @Spor_sist_prgP, @Spor_functie_suplP,
@Spor_specificP, @Spor_conditii_1P, @Spor_conditii_2P, @Spor_conditii_3P, @Spor_conditii_4P,
@Spor_conditii_5P, @Spor_conditii_6P, @Tip_colabP, @Alte_surseP, @Grad_invalidP, @Tip_ded_somajP,
@Data_angajarii, @Plecat, @Mod_angajareP, @Data_plecP, @Sindicalist,
@DataI_conv_somaj, @DataE_conv_somaj, @Nr_pers_intr,
@Baza_cond_normale, @Baza_cond_deosebite, @Baza_cond_speciale,
@Indcm_unit_19, @Indcm_cas_19, @Ore_luna_cm, @Indcm, @Indcm_cas_18, @Zcm_18, @Zcm_18_ant, @Baza_casi_ant,
@Baza_cascm_ant, @Zcm_2341011, @Indcm_234, @Indcm_unit_234, @Zcm15, @Zcm_8915, @Indcm_8915,
@Zcm_78, @Indcm_78, @Indcm_somaj, @Ingrijire_copil_sarcina, @Actionar,
@uMarca2CNP, @uMarca2CNPCM, @CNP, @Pensie_max_ded, @Pensie_ded_lunar,
@Pensie_ded_ant, @Pensie_luna, @Salarii_comp, @Alocatii_hrana, @Somaj_tehnic, @Ore_somaj_tehnic, @Suma_neimp
End
drop table ##cursor_net
close cursor_net
Deallocate cursor_net
End

--exec psCalcul_venit_net '12/01/2009', '12/31/2009', '', '', 0
--drop table ##cursor_net
/*
select a.marca, a.Baza_CAS_cond_norm, b.Baza_CAS_cond_norm from net a, net1209 b
where a.data='12/31/2009' and a.data=b.data and a.marca=b.marca
and a.Baza_CAS_cond_norm<>b.Baza_CAS_cond_norm
*/