Mira este es el que uso yo para el pago de las comisiones , solo quitale las facturas de contado, y mi factor de comision. y adaptalo como quieras .
Slds
USE [SELPRUEBA]
GO
/****** Objeto: StoredProcedure [dbo].[COMISION_VENDEDORES_DETALLE30] Fecha de la secuencia de comandos: 07/21/2016 16:24:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[COMISION_VENDEDORES_DETALLE30] @Fecha_Ini Datetime, @Fecha_Fin Datetime
as
SET NOCOUNT OFF
/**************INICIO CONTADO*******************/
/*FACTURAS DE CONTADO Y NOTAS DE CREDITO*/
SELECT DISTINCT
T2.[SlpName] AS 'Vendedor',
T0.DocNum,
T0.CardCode,
T0.CardName,
T0.[DocTotal]-T0.VatSum AS 'VentaCon',
T0.[GrosProfit]/(T0.[DocTotal]- T0.[VatSum])*100 AS 'UtilidadCon'
--isnull(T0.[U_Colaboracion],'') AS 'Colaborador'
INTO
#TCon1
FROM
OINV T0 INNER JOIN
INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN
OSLP T2 ON T0.SLPCODE=T2.SLPCODE
WHERE
T0.DocDate >=@Fecha_Ini AND
T0.DocDate <=@Fecha_Fin AND
T0.[CardCode] IN ('Cl001','Cl002','Ch001','Ch002') AND
--(T2.[SlpName] LIKE 'CCH%' OR T2.[SlpName] LIKE 'SPS1%' OR T2.[SlpName] LIKE 'SPS2%' OR T2.[SlpName] LIKE 'TGA%' OR T2.[SlpName] LIKE 'CBA%' OR T2.[SlpName] LIKE 'COP%' OR T2.[SlpName] LIKE 'ROA%') AND
T0.DOCTOTAL<>'0'
UNION ALL
SELECT DISTINCT
T2.[SlpName] AS 'Vendedor',
T0.DocNum,
T0.CardCode,
T0.CardName,
(T0.[DocTotal]-T0.VatSum)*-1 AS 'VentaCon',
T0.[GrosProfit]/(T0.[DocTotal]- T0.[VatSum])*100*-1 AS 'UtilidadCon'
--isnull(T0.[U_Colaboracion],'') AS 'Colaborador'
FROM
ORIN T0 INNER JOIN
RIN1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN
OSLP T2 ON T0.SLPCODE=T2.SLPCODE
WHERE
T0.DocDate >=@Fecha_Ini AND
T0.DocDate <=@Fecha_Fin AND
T0.[CardCode] IN ('CL001','CL002','CH001','CH002') AND
--(T2.[SlpName] LIKE 'CCH%' OR T2.[SlpName] LIKE 'SPS1%' OR T2.[SlpName] LIKE 'SPS2%' OR T2.[SlpName] LIKE 'TGA%' OR T2.[SlpName] LIKE 'cba%' OR T2.[SlpName] LIKE 'COP%' OR T2.[SlpName] LIKE 'ROA%') AND
T0.DOCTOTAL<>'0'
ORDER BY
T2.[SlpName]
/*CALCULO DE UTILIDAD BRUTA (UB)*/
SELECT
T0.Vendedor,
T0.DocNum,
T0.CardCode,
T0.CardName,
T0.VentaCon,
T0.UtilidadCon,
T0.VentaCon*(T0.UtilidadCon/100) AS 'UB',
--T0.Colaborador,
T0.VentaCon*(T0.UtilidadCon/100) AS 'Utilidad',
1.3 as 'COM'
INTO
#TCon2
FROM
#TCon1 T0
/*CALCULO DE FACTOR COMISION*/
SELECT
T0.Vendedor,
T0.DocNum,
T0.CardCode,
T0.CardName,
T0.VentaCon,
T0.UtilidadCon,
--T0.Colaborador,
T0.Utilidad,
T0.UB,
T0.COM,
CASE WHEN (T0.UB/T0.VentaCon)<0 THEN (((T0.UB/T0.VentaCon)/0.3)*-1) ELSE ((T0.UB/T0.VentaCon)/0.3) END AS 'FCOM'
INTO
#TCon3
FROM
#TCon2 T0
/*CALCULO DE COMISION POR FACTURA*/
SELECT
T0.Vendedor,
T0.DocNum,
T0.CardCode,
T0.CardName,
T0.VentaCon,
T0.UtilidadCon,
--T0.Colaborador,
T0.Utilidad,
T0.UB,
T0.COM,
T0.FCOM,
CASE WHEN T0.FCOM>=1 THEN T0.VentaCon*(T0.COM/100) ELSE ((T0.VentaCon*(T0.COM/100))*(T0.FCOM))END AS 'Comision'
INTO
#TCon4
FROM
#TCon3 T0
ORDER BY
T0.Vendedor
/*CALCULO DE COMISION POR VENDEDOR*/
SELECT
T0.Vendedor,
sum(T0.Comision) AS 'Comision'
INTO
#TFCon
FROM
#TCon4 T0
GROUP BY
T0.Vendedor
ORDER BY
T0.Vendedor
/************FIN CONTADO*************/
/*****************INICIO CREDITO****************/
/***********FACTURAS CREDITO RECUPERADAS***********/
SELECT
T3.[SlpName] AS 'Vendedor',
T2.DocNum,
T2.CardCode,
T2.CardName,
CASE WHEN T2.Vatsum>0 THEN T0.SumApplied/1.12 ELSE T0.SumApplied END AS 'VentaCre',
T2.[GrosProfit]/(T2.[DocTotal]- T2.[VatSum])*100 AS 'UtilidadCre'
--isnull(T2.U_Colaboracion,'') AS 'Colaborador'
INTO
#TCre1
FROM
RCT2 T0 INNER JOIN
ORCT T1 ON T0.DocNum = T1.DocNum INNER JOIN
OINV T2 ON T0.DocEntry = T2.DocEntry INNER JOIN
OSLP T3 ON T2.SlpCode = T3.SlpCode
WHERE
T1.CardCode NOT IN ('CL001','CL002','CH001','CH002') AND
T1.DocDate >=@Fecha_Ini AND
T1.DocDate <=@Fecha_Fin AND
T1.JrnlMemo NOT LIKE 'Canc%' AND
T0.InvType='13' AND
T2.[DocType]<>'s'
ORDER BY
T3.[SlpName]
--COMISION A TERCEROS
SELECT
[Name] AS 'Vendedor',
isnull([U_Comision],0) AS 'Total'
INTO
#TComi1
FROM
dbo.[@COMISIONT]
/*******CALCULO DE UTILIDAD BRUTA (UB) Y ASIGNACION DE % COMISION*******/
SELECT
T0.Vendedor,
T0.DocNum,
T0.CardCode,
T0.CardName,
T0.VentaCre AS 'VentaCre',
T0.UtilidadCre,
--T0.Colaborador,
T0.VentaCre*(T0.UtilidadCre/100) AS 'Utilidad',
1.3 as 'COM'
INTO
#TCre2
FROM
#TCre1 T0
ORDER BY
T0.Vendedor
/*******CALCULO DE TOTAL DE VENDIDO********/
SELECT
T0.Vendedor,
sum(T0.VentaCre) AS 'VentaCre',
avg(T0.UtilidadCre) AS 'UtilidadCre'
INTO
#TCre3
FROM
#TCre2 T0
GROUP BY
T0.Vendedor
ORDER BY
T0.Vendedor
/***********CALCULO DE UTILIDAD BRUTA NETA************/
SELECT
T0.Vendedor,
T0.DocNum,
T0.CardCode,
T0.CardName,
T0.VentaCre AS 'VentaCre',
T0.UtilidadCre,
--T0.Colaborador,
T0.Utilidad,
T0.Utilidad-((T0.VentaCre/(SELECT T2.VentaCre FROM #TCre3 T2 WHERE T2.Vendedor = T0.Vendedor))*T1.Total) AS 'UB',
T0.COM
INTO
#TCre4
FROM
#TCre2 T0 INNER JOIN #TComi1 T1 ON T0.Vendedor = T1.Vendedor
/***********CALCULO DE FACTOR DE COMISION (FCOM)************/
SELECT
T0.Vendedor,
T0.DocNum,
T0.CardCode,
T0.CardName,
T0.VentaCre AS 'VentaCre',
T0.UtilidadCre,
--T0.Colaborador,
T0.Utilidad,
T0.UB,
T0.COM,
CASE WHEN (T0.UB/T0.VentaCre)<0 THEN (((T0.UB/T0.VentaCre)/0.3)*-1) ELSE ((T0.UB/T0.VentaCre)/0.3) END AS 'FCOM'
INTO
#TCre5
FROM
#TCre4 T0
/**********CALCULO DE COMISION A PAGAR POR FACTURA**********/
SELECT
T0.Vendedor,
T0.DocNum,
T0.CardCode,
T0.CardName,
T0.VentaCre AS 'VentaCre',
T0.UtilidadCre,
--T0.Colaborador,
T0.Utilidad,
T0.UB,
T0.COM,
T0.FCOM,
CASE WHEN T0.FCOM>=1 THEN T0.VentaCre*(convert(NUMERIC(11,2),T0.COM)/100) ELSE (T0.VentaCre*(convert(NUMERIC(11,2),T0.COM)/100)*T0.FCOM) END AS 'Comision'
INTO
#TCre6
FROM
#TCre5 T0
ORDER BY
T0.Vendedor
/*********SUMA TOTAL DE COMSION A PAGAR CREDITO**********/
SELECT
T0.Vendedor,
sum(T0.Comision) AS 'Comision'
INTO
#TFCre
FROM
#TCre6 T0
GROUP BY
T0.Vendedor
ORDER BY
T0.Vendedor
/**********DETALLE*************/
SELECT
T0.Vendedor,
T0.DocNum,
T0.CardCode,
T0.CardName,
T0.VentaCon as 'Venta',
--T0.Colaborador,
T0.UtilidadCon as '%Utilidad',
T0.Utilidad,
T0.UB,
T0.COM,
T0.FCOM,
T0.Comision,
CASE
WHEN
T0.VentaCon>0
THEN
'CON'
ELSE
'NC'
END AS 'Tipo'
INTO
#TDCom
FROM
#TCon4 T0
UNION ALL
SELECT
T1.Vendedor,
T1.DocNum,
T1.CardCode,
T1.CardName,
T1.VentaCre as 'Venta',
--T1.Colaborador,
T1.UtilidadCre,
T1.Utilidad,
T1.UB,
T1.COM,
T1.FCOM,
T1.Comision,
'CRE' AS 'Tipo'
FROM
#TCre6 T1
-------------------FIN Credito--------------------------
/*VISUALIZAR TABLAS TEMPORALES*/
--SELECT * FROM #TCon1
--SELECT * FROM #TCon2
--SELECT * FROM #TCon3
--SELECT * FROM #TCon4
--SELECT * FROM #TFCon
--SELECT * FROM #TComi1
--SELECT * FROM #TCre1
--SELECT * FROM #TCre2
--SELECT * FROM #TCre3
--SELECT * FROM #TCre4
--SELECT * FROM #TCre5
--SELECT * FROM #TCre6
SELECT * FROM #TDCom T0 ORDER BY T0.Vendedor
--SELECT * FROM #TFCre
/******COMISION FINAL CREDITO - CONTADO******/
--SELECT
-- T0.Vendedor,
-- isnull(T0.Comision,0)+isnull(T1.Comision,0) AS 'Comision'
--FROM
-- #TFCon T0 LEFT JOIN
-- #TFCre T1 ON T0.Vendedor=T1.Vendedor
/*ELIMINAR TABLAS TEMPORALES*/
DROP TABLE #TCon1
DROP TABLE #TCon2
DROP TABLE #TCon3
DROP TABLE #TCon4
DROP TABLE #TFCon
DROP TABLE #TComi1
DROP TABLE #TCre1
DROP TABLE #TCre2
DROP TABLE #TCre3
DROP TABLE #TCre4
DROP TABLE #TCre5
DROP TABLE #TCre6
DROP TABLE #TFCre
DROP TABLE #TDCom
SET NOCOUNT ON
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.