Buen dia a todos
La consulta que muestro lineas abajo usa varios campos para calculos que
son el resultado de consultas que se van formando con diferentes withs.
por ejemplo si en un primer with asigno el valor 'SI' al campo
asignacion_familiar y mas abajo en otro with el calculo depende de si este
campos es 'SI' o 'NO' al momento de efectuar el select * from query todos
los campos que depende de otro campo en un with anterior se muestran NULL
sin embargo si tiro la query con un insert ahi si me graba los valores
correctos.
de que forma puedo hacer para que al efectuar el select ya muestre el
resultado de los calculos y no tenga que efectuar el insert para saber si
estoy calculando correctamente.
with query2 as (
select '".[periodo4]."' as periodo, row_number() over() as
item,e.per_documento,e.per_apepaterno||' '||e.per_apematerno||'
'||e.per_nombre as apellidos_nombres, dt1.deta_tip_id as
cargo_id,tcomision_id, e.empresa_id,
CASE
WHEN ((select count(*) from rrhh_conyuge where per_id = e.per_id) >0 )
THEN
'SI'
ELSE
'NO'
END as asig_familiar,
(select CASE WHEN (tipo_val_id = 2113) THEN
tasa ELSE tasa/100 END from rrhh_param_aport_porcen_periodo where
aportacion_id = 2116
and periodo = '".[periodo4]."') as rmv,
(select CASE WHEN (tipo_val_id = 2113) THEN
tasa ELSE tasa/100 END from rrhh_param_aport_porcen_periodo where
aportacion_id = 2112
and periodo = '".[periodo4]."') as porc_asig_familiar,
(select valor_inicial::numeric(8,2)
from rrhh_deta_contrato dc join rrhh_contrato c on dc.contrato_id =
c.contrato_id
join deta_tipos dt on dt.deta_tip_id = dc.condicion_id and dt.fijo_char =
'remun_basi'
where c.activo = 1 and
c.dni_empleado = per_documento) as remun_basica,
(select valor_inicial::numeric(8,2)
from rrhh_deta_contrato dc join rrhh_contrato c on dc.contrato_id =
c.contrato_id
join deta_tipos dt on dt.deta_tip_id = dc.condicion_id and dt.fijo_char =
'alimentaci'
where c.activo = 1 and
c.dni_empleado = per_documento) as alimentacion,
(select valor_inicial::numeric(8,2)
from rrhh_deta_contrato dc join rrhh_contrato c on dc.contrato_id =
c.contrato_id
join deta_tipos dt on dt.deta_tip_id = dc.condicion_id and dt.fijo_char =
'movilidad'
where c.activo = 1 and
c.dni_empleado = per_documento) as movilidad,
CASE
WHEN (e.afp_id = 2034) THEN
'SI'
ELSE
'NO'END as snp,
CASE
WHEN (e.afp_id = 2034) THEN
NULL
ELSE
e.afp_id END as afp
from rrhh_personal e
left join
deta_tipos dt1 on dt1.deta_tip_id = e.cargo_id),
query3 as ( select *,
CASE WHEN (asig_familiar = 'SI') THEN
rmv * porc_asig_familiar ELSE NULL END as val_asig_familiar,
(select tot_min_tarde from rrhh_asistencia_total_persona a where a.dni
= per_documento and a.periodo = periodo limit 1 ) as desc_tardanz,
CASE WHEN (asig_familiar = 'SI') THEN
remun_basica + (rmv * porc_asig_familiar)
ELSE remun_basica END as remun_bruta
from query2
), query4 as (
select *,
(CASE WHEN (snp = 'SI') THEN
round(remun_bruta * (select total/100 from rrhh_param_afp_periodo where
periodo = '".[periodo4]."' and afp_id = 2034),2) ELSE NULL END) as
val_snp,
(remun_bruta *
(select aporte_obligatorio/100 from rrhh_param_afp_periodo where periodo =
'".[periodo4]."' and afp_id = afp)) as aport_oblig,
CASE WHEN (tcomision_id= 2029) --comision por flujo
THEN
remun_bruta *
(select comision_flujo/100 from rrhh_param_afp_periodo where periodo =
'".[periodo4]."' and afp_id = afp )
WHEN (tcomision_id= 2028) --comision mixta
THEN
remun_bruta *
(select mixta_flujo/100 from rrhh_param_afp_periodo where periodo =
'".[periodo4]."' and afp_id = afp ) ELSE NULL END as comision_afp,
remun_bruta *
(select prima/100 from rrhh_param_afp_periodo where periodo =
'".[periodo4]."' and afp_id = afp ) as prima_afp,
round((remun_basica/30/8/60) * desc_tardanz,2 ) as desc_tardanza
from query3 ), query5 as (
select *,round(coalesce(desc_tardanza,0) + coalesce(val_snp,0) +
coalesce(aport_oblig,0) +
coalesce(comision_afp,0) + coalesce(prima_afp,0),2) as tot_descuentos
from query4 ), query6 as (
select *,remun_bruta - tot_descuentos as remun_neta,
(remun_basica - desc_tardanza) *
(select tasa/100 from rrhh_param_aport_porcen_periodo where periodo =
'".[periodo4]."' and aportacion_id = 2110 ) as aport_essalud,
remun_bruta *
(select tasa/100 from rrhh_param_aport_porcen_periodo where periodo =
'".[periodo4]."' and aportacion_id = 2111 ) as aport_sctr,
(select tasa from rrhh_param_aport_porcen_periodo where periodo =
'".[periodo4]."' and aportacion_id = 2125) as cts,
(select tasa/100 from rrhh_param_aport_porcen_periodo where periodo =
'".[periodo4]."' and aportacion_id = 2110) as param_essalud
from query5 ),
query7 as (select *,aport_essalud + aport_sctr as tot_aport from query6 ),
query8 as (
select *,
(remun_bruta * 14 ) + (remun_bruta * coalesce(cts,0)) as remun_anual,
remun_bruta * 2 * param_essalud as renta_essalud
from query7 ),
query9 as (
select *,remun_anual + renta_essalud as ingreso_anual,
(select hasta from rrhh_param_renta5_periodo where periodo =
'".[periodo4]."' and orden = 1 )
as deduccion
from query8),
query10 as (
select *,ingreso_anual - deduccion as renta_neta5 from query9),
query11 as (
select *,
CASE WHEN (renta_neta5 >= (select entre from rrhh_param_renta5_periodo
where periodo = '".[periodo4]."' and orden = 2)) THEN
CASE WHEN (renta_neta5 <= (select hasta from
rrhh_param_renta5_periodo where periodo = '".[periodo4]."' and orden = 2))
THEN
renta_neta5 * (select tasa/100 from rrhh_param_renta5_periodo where
periodo = '".[periodo4]."' and orden = 2)
ELSE
(select hasta * tasa/100 from rrhh_param_renta5_periodo where periodo
= '".[periodo4]."' and orden = 2)
END
ELSE NULL END as renta_tramo_1,
CASE WHEN (renta_neta5 >= (select entre from rrhh_param_renta5_periodo
where periodo = '".[periodo4]."' and orden = 3)) THEN
CASE WHEN (renta_neta5 <= (select hasta from rrhh_param_renta5_periodo
where periodo = '".[periodo4]."' and orden = 3)) THEN
(renta_neta5 -(select hasta from rrhh_param_renta5_periodo where
periodo = '".[periodo4]."' and orden = 2)) *
(select tasa/100 from rrhh_param_renta5_periodo where periodo =
'".[periodo4]."' and orden = 3)
ELSE
(select (hasta - (select hasta from rrhh_param_renta5_periodo where
periodo = '".[periodo4]."' and orden = 2))* tasa/100 from
rrhh_param_renta5_periodo where periodo = '".[periodo4]."' and orden = 3)
END
ELSE NULL END as renta_tramo_2,
CASE WHEN (renta_neta5 >= (select entre from rrhh_param_renta5_periodo
where periodo = '".[periodo4]."' and orden = 4)) THEN
CASE WHEN (renta_neta5 <= (select hasta from rrhh_param_renta5_periodo
where periodo = '".[periodo4]."' and orden = 4)) THEN
(renta_neta5 -(select hasta from rrhh_param_renta5_periodo where
periodo = '".[periodo4]."' and orden = 3)) *
(select tasa/100 from rrhh_param_renta5_periodo where periodo =
'".[periodo4]."' and orden = 4)
ELSE
(select (hasta - (select hasta from rrhh_param_renta5_periodo where
periodo = '".[periodo4]."' and orden = 4))* tasa/100 from
rrhh_param_renta5_periodo where periodo = '".[periodo4]."' and orden = 3)
END
ELSE NULL END as renta_tramo_3,
CASE WHEN (renta_neta5 >= (select entre from rrhh_param_renta5_periodo
where periodo = '".[periodo4]."' and orden = 5)) THEN
CASE WHEN (renta_neta5 <= (select hasta from rrhh_param_renta5_periodo
where periodo = '".[periodo4]."' and orden = 5)) THEN
(renta_neta5 -(select hasta from rrhh_param_renta5_periodo where
periodo = '".[periodo4]."' and orden = 4)) *
(select tasa/100 from rrhh_param_renta5_periodo where periodo =
'".[periodo4]."' and orden = 5)
ELSE
(select (hasta - (select hasta from rrhh_param_renta5_periodo where
periodo = '".[periodo4]."' and orden = 5))* tasa/100 from
rrhh_param_renta5_periodo where periodo = '".[periodo4]."' and orden = 3)
END
ELSE NULL END as renta_tramo_4
from query10),
query12 as (
select *,coalesce(renta_tramo_1,0)+ coalesce(renta_tramo_2,0) +
coalesce(renta_tramo_3,0) + coalesce(renta_tramo_4,0) as total_renta_5
from query11),
query13 as (
select *,round(total_renta_5/12,2) as renta_mensual,1 as planilla_id from
query12 )
select * from query13
--
José Mercedes Venegas Acevedo
cel Mov RPC 964185205
