jpablolino
18/02/09, 13:20:51
Hola a todos.. desearia me colaboren con lo siguiente:
tengo 3 tablas, una es la de datos maestros de empleados OHEM las otras dos fueron creadas mediante un addon q proveyo un partner de sap las cuales son @pldoc y @pl_tx, el siguiente query da como resultado un listado completo de todos los empleados y sus respectivos montos de sueldo y demas aportes q se hacen por ley. Resulta q dentro de la empresa se manejan planillas de agrupaciones de personas q realizan trabajo en otras enpresas (para identificar mejor, la empresa es un outsourcing), he adjuntado una imagen para q observen las tablas y relaciones q existen entre ellas, mi pregunta es: quiero q cuando se ejecute la consulta aparte de pedir la gestion me pida la planilla a mostrar para q los resultados sean de esa planilla en la gestion indicada y no asi de todos los empleados en total; en el query mostrado abajo en el where le indico q planilla quiero pero lo q deseo hacer es q el usuario indique que planilla y q gestion debe tomar como condicion para la ejecucion correcta de la consulta.
Espero me puedan colaborar.. gracias.
SELECT PL.U_Cod_Emp, isnull(PL.CodCI,'')+' '+ isnull(PL.CIext,'') CI, PL.Name, (PL.HrasTrab/8) DiasTrab,
PL.HaberMes SueldoNeto, PL.BonoAntiguedad, PL.OtrosBonos, PL.Comisiones, PL.OtrosIng, PL.HrsExtra, PL.HrsExtN,
PL.TotalGanado, PL.Anticipo, (CASE WHEN PL.CodAporte = 'FDBV' THEN PL.AporteFuturo ELSE PL.AportePrevision END) AFP,
PL.RCIVA, PL.Multas, PL.OtrosDesc, PL.Prestamos, PL.LiquidoPagable
FROM ( SELECT DISTINCT g.U_Cod_Emp, g.U_Gestion,g.PL, e.U_CA CodAporte, e.U_NUA CodPatronal,
ISNULL(e.[firstName],'')+' '+ ISNULL(e.[middleName],'')+' '+ ISNULL(e.[lastName],'')+' '+ ISNULL(e.[U_ApMaterno],'') AS Name, e.startDate, e.govID CodCI, e.u_ext CIext, e.jobtitle Descriptio,
sum(g.ColA) AS HaberMes, sum(g.ColT) AS HrasTrab, sum(g.ColU) AS OtrosBonos, sum(g.ColV) AS OtrosIng, sum(g.ColW) AS HrsExtN,
sum(g.ColB) AS BonoAntiguedad, sum(g.ColC) AS Comisiones, SUM(g.ColD) AS HrsExtra, sum(g.ColE) AS BonMov, sum(g.ColF) AS AportePatronal,
sum(g.ColG) AS TotalGanado, sum(g.ColD1) AS Othab1, sum(g.ColD2) AS Othab2, sum(g.ColD3) AS Othab3,
sum(g.ColG2) AS FonviPrevision, sum(g.ColG1) AS FonviFuturo,
sum(g.ColH) AS AportePrevision, SUM(g.ColI) AS AporteFuturo ,SUM(g.ColI1) AS SueldoNeto,
sum(g.ColJ) AS RCIVA, SUM(g.ColK) AS Anticipo, SUM(g.ColL) AS Telefono, SUM(g.ColM) AS Multas, SUM(g.ColN) AS Prestamos,
SUM(g.ColO) AS SancionesPecuniariAS, SUM(g.ColP) AS OtrosDesc, SUM(g.ColQ) AS TotalDesc, SUM(g.ColR) AS SueldoPagar,
SUM(g.ColS) AS LiquidoPagable
FROM [dbo].[@PLDOC] c, [dbo].[OHEM] e, ( SELECT T.U_Cod_Emp, T.U_lAStName AS lAStName, T.U_firstName AS firstName, T.U_branch AS branch, T.U_dept AS dept,
T.U_position AS position, T.U_manager AS manager, T.U_CA AS CA, T.U_jobTitle AS jobTitle, T.U_status AS status,
T.U_Planilla AS PL, T.U_Gestion , T .U_PLCode,
CASE right(T.U_Cod_Conc, 5) WHEN 'I-SBB' THEN [dbo].Decrypt(T.U_Monto,T.U_Cod_Emp) ELSE 0 END AS ColA, /*salario basico*/
CASE right(T.U_Cod_Conc, 5) WHEN 'I-ANT' THEN [dbo].Decrypt(T.U_Monto,T.U_Cod_Emp) ELSE 0 END AS ColB, /*bono antigue*/
CASE right(T.U_Cod_Conc, 5) WHEN 'I-COM' THEN [dbo].Decrypt(T.U_Monto,T.U_Cod_Emp) ELSE 0 END AS ColC, /*comisiones*/
CASE right(T.U_Cod_Conc, 5) WHEN 'I-HET' THEN [dbo].Decrypt(T.U_Monto,T.U_Cod_Emp) ELSE 0 END AS ColD, /*tot hras extra*/
CASE right(T.U_Cod_Conc, 5) WHEN 'I-OI1' THEN [dbo].Decrypt(T.U_Monto,T.U_Cod_Emp) ELSE 0 END AS ColD1, /*otros hab1*/
CASE right(T.U_Cod_Conc, 5) WHEN 'I-OI2' THEN [dbo].Decrypt(T.U_Monto,T.U_Cod_Emp) ELSE 0 END AS ColD2, /*otros hab2*/
CASE right(T.U_Cod_Conc, 5) WHEN 'I-OI3' THEN [dbo].Decrypt(T.U_Monto,T.U_Cod_Emp) ELSE 0 END AS ColD3, /*otros hab3*/
CASE right(T.U_Cod_Conc, 5) WHEN 'I-BMO' THEN [dbo].Decrypt(T.U_Monto,T.U_Cod_Emp) ELSE 0 END AS ColE, /*bono movilidad*/
CASE right(T.U_Cod_Conc, 5) WHEN 'R-APO' THEN [dbo].Decrypt(T.U_Monto,T.U_Cod_Emp) ELSE 0 END AS ColF, /*???*/
CASE right(T.U_Cod_Conc, 5) WHEN 'T-TGN' THEN [dbo].Decrypt(T.U_Monto,T.U_Cod_Emp) ELSE 0 END AS ColG , /*tot ganado*/
CASE right(T.U_Cod_Conc, 5) WHEN 'R-FFN' THEN [dbo].Decrypt(T.U_Monto,T.U_Cod_Emp) ELSE 0 END AS ColG1, /*FDBV PRO VIVIENDA*/
CASE right(T.U_Cod_Conc, 5) WHEN 'R-FON' THEN [dbo].Decrypt(T.U_Monto,T.U_Cod_Emp) ELSE 0 END AS ColG2, /*BBVA PRO VIVIENDA*/
CASE right(T.U_Cod_Conc, 5) WHEN 'A-AFP' THEN [dbo].Decrypt(T.U_Monto,T.U_Cod_Emp) ELSE 0 END AS ColH, /*BBVA Aporte Fondo de Pensiones*/
CASE right(T.U_Cod_Conc, 5) WHEN 'A-BFP' THEN [dbo].Decrypt(T.U_Monto,T.U_Cod_Emp) ELSE 0 END AS ColI, /*FDBV Aporte Fondo de Pensiones*/
CASE right(T.U_Cod_Conc, 5) WHEN 'M-ISN' THEN [dbo].Decrypt(T.U_Monto,T.U_Cod_Emp) ELSE 0 END AS ColI1, /*Sueldo neto*/
CASE right(T.U_Cod_Conc, 5) WHEN 'M-ISF' THEN [dbo].Decrypt(T.U_Monto,T.U_Cod_Emp) ELSE 0 END AS ColJ, /*Impuesto Retenido*/
CASE right(T.U_Cod_Conc, 5) WHEN 'D-ANT' THEN [dbo].Decrypt(T.U_Monto,T.U_Cod_Emp) ELSE 0 END AS ColK, /*Anticipo*/
CASE right(T.U_Cod_Conc, 5) WHEN 'D-LTL' THEN [dbo].Decrypt(T.U_Monto,T.U_Cod_Emp) ELSE 0 END AS ColL, /*???*/
CASE right(T.U_Cod_Conc, 5) WHEN 'D-MUL' THEN [dbo].Decrypt(T.U_Monto,T.U_Cod_Emp) ELSE 0 END AS ColM, /*Multas*/
CASE right(T.U_Cod_Conc, 5) WHEN 'D-PRE' THEN [dbo].Decrypt(T.U_Monto,T.U_Cod_Emp) ELSE 0 END AS ColN, /*Prestamos*/
CASE right(T.U_Cod_Conc, 5) WHEN 'D-SPC' THEN [dbo].Decrypt(T.U_Monto,T.U_Cod_Emp) ELSE 0 END AS ColO, /*???*/
CASE right(T.U_Cod_Conc, 5) WHEN 'D-OTR' THEN [dbo].Decrypt(T.U_Monto,T.U_Cod_Emp) ELSE 0 END AS ColP, /*Otros Desc*/
CASE right(T.U_Cod_Conc, 5) WHEN 'T-TDE' THEN [dbo].Decrypt(T.U_Monto,T.U_Cod_Emp) ELSE 0 END AS ColQ, /*Total Descuento*/
CASE right(T.U_Cod_Conc, 5) WHEN 'T-TSP' THEN [dbo].Decrypt(T.U_Monto,T.U_Cod_Emp) ELSE 0 END AS ColR, /*Tot sueldo pagar*/
CASE right(T.U_Cod_Conc, 5) WHEN 'T-TLP' THEN [dbo].Decrypt(T.U_Monto,T.U_Cod_Emp) ELSE 0 END AS ColS, /*liquido pagable*/
CASE right(T.U_Cod_Conc, 5) WHEN 'I-TDT' THEN [dbo].Decrypt(T.U_Monto,T.U_Cod_Emp) ELSE 0 END AS ColT, /*tot hras trabajadas*/
CASE right(T.U_Cod_Conc, 5) WHEN 'I-BON' THEN [dbo].Decrypt(T.U_Monto,T.U_Cod_Emp) ELSE 0 END AS ColU, /*Otros bonos*/
CASE right(T.U_Cod_Conc, 5) WHEN 'I-OIN' THEN [dbo].Decrypt(T.U_Monto,T.U_Cod_Emp) ELSE 0 END AS ColV, /*Otros ingresos*/
CASE right(T.U_Cod_Conc, 5) WHEN 'I-QNO' THEN [dbo].Decrypt(T.U_Monto,T.U_Cod_Emp) ELSE 0 END AS ColW /*horas extra noches*/
FROM [dbo].[@PL_TX] T INNER JOIN [dbo].[@PLDOC] D ON T.U_PLCode= D.Name
WHERE T.U_Planilla='AB' AND T.U_Gestion='[%0]') AS g
WHERE c.U_PL=g.PL AND c.Name= g.U_PLCode
AND e.empID= g.U_Cod_Emp
AND c.U_Gestion ='[%0]'
GROUP BY g.U_Cod_Emp, g.PL , g.lAStName, g.firstName, e.jobtitle, g.U_Gestion,e.startDate,
e.govID, e.u_ext, e.U_CA, e.U_NUA, e.[middleName], e.[U_ApMaterno], e.[firstName], e.[lastName] ) AS PL
ORDER BY 8 Desc, 5
tengo 3 tablas, una es la de datos maestros de empleados OHEM las otras dos fueron creadas mediante un addon q proveyo un partner de sap las cuales son @pldoc y @pl_tx, el siguiente query da como resultado un listado completo de todos los empleados y sus respectivos montos de sueldo y demas aportes q se hacen por ley. Resulta q dentro de la empresa se manejan planillas de agrupaciones de personas q realizan trabajo en otras enpresas (para identificar mejor, la empresa es un outsourcing), he adjuntado una imagen para q observen las tablas y relaciones q existen entre ellas, mi pregunta es: quiero q cuando se ejecute la consulta aparte de pedir la gestion me pida la planilla a mostrar para q los resultados sean de esa planilla en la gestion indicada y no asi de todos los empleados en total; en el query mostrado abajo en el where le indico q planilla quiero pero lo q deseo hacer es q el usuario indique que planilla y q gestion debe tomar como condicion para la ejecucion correcta de la consulta.
Espero me puedan colaborar.. gracias.
SELECT PL.U_Cod_Emp, isnull(PL.CodCI,'')+' '+ isnull(PL.CIext,'') CI, PL.Name, (PL.HrasTrab/8) DiasTrab,
PL.HaberMes SueldoNeto, PL.BonoAntiguedad, PL.OtrosBonos, PL.Comisiones, PL.OtrosIng, PL.HrsExtra, PL.HrsExtN,
PL.TotalGanado, PL.Anticipo, (CASE WHEN PL.CodAporte = 'FDBV' THEN PL.AporteFuturo ELSE PL.AportePrevision END) AFP,
PL.RCIVA, PL.Multas, PL.OtrosDesc, PL.Prestamos, PL.LiquidoPagable
FROM ( SELECT DISTINCT g.U_Cod_Emp, g.U_Gestion,g.PL, e.U_CA CodAporte, e.U_NUA CodPatronal,
ISNULL(e.[firstName],'')+' '+ ISNULL(e.[middleName],'')+' '+ ISNULL(e.[lastName],'')+' '+ ISNULL(e.[U_ApMaterno],'') AS Name, e.startDate, e.govID CodCI, e.u_ext CIext, e.jobtitle Descriptio,
sum(g.ColA) AS HaberMes, sum(g.ColT) AS HrasTrab, sum(g.ColU) AS OtrosBonos, sum(g.ColV) AS OtrosIng, sum(g.ColW) AS HrsExtN,
sum(g.ColB) AS BonoAntiguedad, sum(g.ColC) AS Comisiones, SUM(g.ColD) AS HrsExtra, sum(g.ColE) AS BonMov, sum(g.ColF) AS AportePatronal,
sum(g.ColG) AS TotalGanado, sum(g.ColD1) AS Othab1, sum(g.ColD2) AS Othab2, sum(g.ColD3) AS Othab3,
sum(g.ColG2) AS FonviPrevision, sum(g.ColG1) AS FonviFuturo,
sum(g.ColH) AS AportePrevision, SUM(g.ColI) AS AporteFuturo ,SUM(g.ColI1) AS SueldoNeto,
sum(g.ColJ) AS RCIVA, SUM(g.ColK) AS Anticipo, SUM(g.ColL) AS Telefono, SUM(g.ColM) AS Multas, SUM(g.ColN) AS Prestamos,
SUM(g.ColO) AS SancionesPecuniariAS, SUM(g.ColP) AS OtrosDesc, SUM(g.ColQ) AS TotalDesc, SUM(g.ColR) AS SueldoPagar,
SUM(g.ColS) AS LiquidoPagable
FROM [dbo].[@PLDOC] c, [dbo].[OHEM] e, ( SELECT T.U_Cod_Emp, T.U_lAStName AS lAStName, T.U_firstName AS firstName, T.U_branch AS branch, T.U_dept AS dept,
T.U_position AS position, T.U_manager AS manager, T.U_CA AS CA, T.U_jobTitle AS jobTitle, T.U_status AS status,
T.U_Planilla AS PL, T.U_Gestion , T .U_PLCode,
CASE right(T.U_Cod_Conc, 5) WHEN 'I-SBB' THEN [dbo].Decrypt(T.U_Monto,T.U_Cod_Emp) ELSE 0 END AS ColA, /*salario basico*/
CASE right(T.U_Cod_Conc, 5) WHEN 'I-ANT' THEN [dbo].Decrypt(T.U_Monto,T.U_Cod_Emp) ELSE 0 END AS ColB, /*bono antigue*/
CASE right(T.U_Cod_Conc, 5) WHEN 'I-COM' THEN [dbo].Decrypt(T.U_Monto,T.U_Cod_Emp) ELSE 0 END AS ColC, /*comisiones*/
CASE right(T.U_Cod_Conc, 5) WHEN 'I-HET' THEN [dbo].Decrypt(T.U_Monto,T.U_Cod_Emp) ELSE 0 END AS ColD, /*tot hras extra*/
CASE right(T.U_Cod_Conc, 5) WHEN 'I-OI1' THEN [dbo].Decrypt(T.U_Monto,T.U_Cod_Emp) ELSE 0 END AS ColD1, /*otros hab1*/
CASE right(T.U_Cod_Conc, 5) WHEN 'I-OI2' THEN [dbo].Decrypt(T.U_Monto,T.U_Cod_Emp) ELSE 0 END AS ColD2, /*otros hab2*/
CASE right(T.U_Cod_Conc, 5) WHEN 'I-OI3' THEN [dbo].Decrypt(T.U_Monto,T.U_Cod_Emp) ELSE 0 END AS ColD3, /*otros hab3*/
CASE right(T.U_Cod_Conc, 5) WHEN 'I-BMO' THEN [dbo].Decrypt(T.U_Monto,T.U_Cod_Emp) ELSE 0 END AS ColE, /*bono movilidad*/
CASE right(T.U_Cod_Conc, 5) WHEN 'R-APO' THEN [dbo].Decrypt(T.U_Monto,T.U_Cod_Emp) ELSE 0 END AS ColF, /*???*/
CASE right(T.U_Cod_Conc, 5) WHEN 'T-TGN' THEN [dbo].Decrypt(T.U_Monto,T.U_Cod_Emp) ELSE 0 END AS ColG , /*tot ganado*/
CASE right(T.U_Cod_Conc, 5) WHEN 'R-FFN' THEN [dbo].Decrypt(T.U_Monto,T.U_Cod_Emp) ELSE 0 END AS ColG1, /*FDBV PRO VIVIENDA*/
CASE right(T.U_Cod_Conc, 5) WHEN 'R-FON' THEN [dbo].Decrypt(T.U_Monto,T.U_Cod_Emp) ELSE 0 END AS ColG2, /*BBVA PRO VIVIENDA*/
CASE right(T.U_Cod_Conc, 5) WHEN 'A-AFP' THEN [dbo].Decrypt(T.U_Monto,T.U_Cod_Emp) ELSE 0 END AS ColH, /*BBVA Aporte Fondo de Pensiones*/
CASE right(T.U_Cod_Conc, 5) WHEN 'A-BFP' THEN [dbo].Decrypt(T.U_Monto,T.U_Cod_Emp) ELSE 0 END AS ColI, /*FDBV Aporte Fondo de Pensiones*/
CASE right(T.U_Cod_Conc, 5) WHEN 'M-ISN' THEN [dbo].Decrypt(T.U_Monto,T.U_Cod_Emp) ELSE 0 END AS ColI1, /*Sueldo neto*/
CASE right(T.U_Cod_Conc, 5) WHEN 'M-ISF' THEN [dbo].Decrypt(T.U_Monto,T.U_Cod_Emp) ELSE 0 END AS ColJ, /*Impuesto Retenido*/
CASE right(T.U_Cod_Conc, 5) WHEN 'D-ANT' THEN [dbo].Decrypt(T.U_Monto,T.U_Cod_Emp) ELSE 0 END AS ColK, /*Anticipo*/
CASE right(T.U_Cod_Conc, 5) WHEN 'D-LTL' THEN [dbo].Decrypt(T.U_Monto,T.U_Cod_Emp) ELSE 0 END AS ColL, /*???*/
CASE right(T.U_Cod_Conc, 5) WHEN 'D-MUL' THEN [dbo].Decrypt(T.U_Monto,T.U_Cod_Emp) ELSE 0 END AS ColM, /*Multas*/
CASE right(T.U_Cod_Conc, 5) WHEN 'D-PRE' THEN [dbo].Decrypt(T.U_Monto,T.U_Cod_Emp) ELSE 0 END AS ColN, /*Prestamos*/
CASE right(T.U_Cod_Conc, 5) WHEN 'D-SPC' THEN [dbo].Decrypt(T.U_Monto,T.U_Cod_Emp) ELSE 0 END AS ColO, /*???*/
CASE right(T.U_Cod_Conc, 5) WHEN 'D-OTR' THEN [dbo].Decrypt(T.U_Monto,T.U_Cod_Emp) ELSE 0 END AS ColP, /*Otros Desc*/
CASE right(T.U_Cod_Conc, 5) WHEN 'T-TDE' THEN [dbo].Decrypt(T.U_Monto,T.U_Cod_Emp) ELSE 0 END AS ColQ, /*Total Descuento*/
CASE right(T.U_Cod_Conc, 5) WHEN 'T-TSP' THEN [dbo].Decrypt(T.U_Monto,T.U_Cod_Emp) ELSE 0 END AS ColR, /*Tot sueldo pagar*/
CASE right(T.U_Cod_Conc, 5) WHEN 'T-TLP' THEN [dbo].Decrypt(T.U_Monto,T.U_Cod_Emp) ELSE 0 END AS ColS, /*liquido pagable*/
CASE right(T.U_Cod_Conc, 5) WHEN 'I-TDT' THEN [dbo].Decrypt(T.U_Monto,T.U_Cod_Emp) ELSE 0 END AS ColT, /*tot hras trabajadas*/
CASE right(T.U_Cod_Conc, 5) WHEN 'I-BON' THEN [dbo].Decrypt(T.U_Monto,T.U_Cod_Emp) ELSE 0 END AS ColU, /*Otros bonos*/
CASE right(T.U_Cod_Conc, 5) WHEN 'I-OIN' THEN [dbo].Decrypt(T.U_Monto,T.U_Cod_Emp) ELSE 0 END AS ColV, /*Otros ingresos*/
CASE right(T.U_Cod_Conc, 5) WHEN 'I-QNO' THEN [dbo].Decrypt(T.U_Monto,T.U_Cod_Emp) ELSE 0 END AS ColW /*horas extra noches*/
FROM [dbo].[@PL_TX] T INNER JOIN [dbo].[@PLDOC] D ON T.U_PLCode= D.Name
WHERE T.U_Planilla='AB' AND T.U_Gestion='[%0]') AS g
WHERE c.U_PL=g.PL AND c.Name= g.U_PLCode
AND e.empID= g.U_Cod_Emp
AND c.U_Gestion ='[%0]'
GROUP BY g.U_Cod_Emp, g.PL , g.lAStName, g.firstName, e.jobtitle, g.U_Gestion,e.startDate,
e.govID, e.u_ext, e.U_CA, e.U_NUA, e.[middleName], e.[U_ApMaterno], e.[firstName], e.[lastName] ) AS PL
ORDER BY 8 Desc, 5