USE [SBODemoMx]
GO
/****** Object:  StoredProcedure [dbo].[wspSBOPriceCalcV3]    Script Date: 2023/11/14 12:49:49 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[wspSBOPriceCalcV3] 
/*parametros*/
@parmDocKey int,
@parmOrigin Varchar(3),
@parmUser Varchar(30),
@parmCardCode Varchar(30),
@parmBPCat Varchar(30),
@parmItemCode Varchar(50),
@parmItemCat1 Varchar(30),
@parmItemCat2 Varchar(30),
@parmItemCat3 Varchar(30),
@parmItemCat4 Varchar(30),
@parmQuantity numeric(19,6),
@parmDate Date,
@parmExchageRate numeric(19,6),/**/
@parmRetDecimalPrecision int,/**/
@parmWhsCode varchar(10),/**/
@parmLinePriceList  int ,/**/
@parmDirectDisc numeric(19,6) ,/**/
@parmDirectPrice numeric(19,6),/**/
@parmDirectPriceCurr varchar(3),/**/
@varItemPrice numeric(19,6) OUTPUT,
@varItemPriceAftDisc numeric(19,6) OUTPUT,
@varDiscount numeric(19,6) OUTPUT,
@varCurrency varchar(3) OUTPUT,
@varPriceRule varchar(20) OUTPUT,
@varExchageRate numeric(19,6) OUTPUT,
@varFCPrice numeric(19,6) OUTPUT,
@varFCPriceAftDisc numeric(19,6) OUTPUT,
@varPriceList as int OUTPUT,
@varUnitCost numeric(19,6) OUTPUT, /*Costo*/
@varProfitMargin numeric(19,6) OUTPUT,/*Margen por Unidad*/
@varDiscL1 numeric(19,6) OUTPUT, /*Decuento cascada Nivel 1*/
@varDiscL2 numeric(19,6) OUTPUT, /*Decuento cascada Nivel 2*/
@varDiscL3 numeric(19,6) OUTPUT,/*Decuento cascada Nivel 3*/
@varDiscL4 numeric(19,6) OUTPUT,/*Decuento cascada Nivel 4*/
@varDiscL5 numeric(19,6) OUTPUT,/*Decuento cascada Nivel 5*/
@varDiscL6 numeric(19,6) OUTPUT,/*Decuento cascada Nivel 6*/
@varRetCode int OUTPUT,/**/
@varRetMsg varchar(100) OUTPUT



	
AS
DECLARE @parmPriceList as int
DECLARE @varPriceListSpecial as varchar(10)
DECLARE @parmDB as varchar(20)
DECLARE @varExpand as varchar(1)
DECLARE @varCurrentDiscount as numeric(19,6)
DECLARE @varCurrentRule as varchar(10)
DECLARE @varCurrRuleLevel as varchar(10)
DECLARE @varLineNum as int 
DECLARE @varItemGroupCode as int
DECLARE	@varManufactureCode as int
DECLARE @varBPGroupCode as Int
DECLARE @VarDiscountRatio as varchar(1)
DECLARE @varNoDiscount as varchar(1)
DECLARE @varPriceAux as numeric(19,6)
DECLARE @varOriginDisc  as numeric(19,6)
DECLARE @varListPrice as int
DECLARE @varLastPurPrc as numeric(19,6)
DECLARE @varLstEvlPric as numeric(19,6)
/*Agregar a Procedimiento en tractodiesel*/
DECLARE @varLocalCurrency varchar(3)


--DECLARE @varDiscount as numeric(19,6)
--DECLARE @varItemPrice as numeric(19,6)
--DECLARE @varItemPriceAftDisc as numeric(19,6)
--DECLARE @varCurrency as varchar(3)
--SET @parmCardCode = 'C30000'
--SET @parmItemCode = 'I00013'
--SET @parmDate = '20160428'
--SET @parmQuantity = 101

--if @parmOrigin = 'WCP' --Portal de Clientes
--SET  @varOriginDisc = 3.6


set @varLocalCurrency = 'MXP'
SET @varRetMsg = 'TC ' + cast(@parmExchageRate as varchar(20)) 

DECLARE @varExchangeRatePrecision as int = 4

set @varDiscount = 0
set @varUnitCost = 0
set @varProfitMargin = 1
set @varRetCode = 0

--Get BP Info
select @varBPGroupCode = GroupCode, 
@varPriceList =  ListNum, 
@parmPriceList = ListNum,  
@VarDiscountRatio = DiscRel, 
@varNoDiscount = NoDiscount 
FROM ocrd 
WHERE CardCode = @parmCardCode



--En caso de que se pase como parámetro una lista de precios (Diferente de cero) se toma en cuenta para los calculos
IF @parmLinePriceList <> 0
	SET @parmPriceList = @parmLinePriceList

/**/
--SELECT @varNoDiscount AS NoDiscount 
--SELECT @parmCardCode AS CardCode 

--Get Item Info
select  
@varItemGroupCode = ItmsGrpCod, 
@varManufactureCode = FirmCode, 
@varItemPrice = T1.Price, 

@varCurrency = t1.Currency,
@varPriceRule = 'ListPrice',
@varLastPurPrc = t0.LastPurPrc,
@varLstEvlPric = t0.LstEvlPric
from oitm t0 
left outer join itm1 t1 on t0.ItemCode = t1.ItemCode and t1.PriceList = @parmPriceList
where t0.ItemCode = @parmItemCode 




/*Forzar Precio en moneda de documento: Inicio*/
if @parmDirectPriceCurr <> @varCurrency --Revisamos que la moneda del documento sea diferente a la del precio de la lista
BEGIN

	if @varLocalCurrency = @parmDirectPriceCurr
	begin
		select @varExchageRate = Rate from ORTT
		where RateDate = @parmDate
		and Currency = @varCurrency	
		SET @varItemPrice = (@varItemPrice * @varExchageRate)
		SET @varCurrency = @parmDirectPriceCurr
		SET @varPriceRule = 'ListDocCur'
	end

	if @varLocalCurrency <> @parmDirectPriceCurr
	begin
		select @varExchageRate = Rate from ORTT
		where RateDate = @parmDate
		and Currency = @parmDirectPriceCurr	
		if @varExchageRate > 0 
			SET @varItemPrice = (@varItemPrice / @varExchageRate)
		SET @varCurrency = @parmDirectPriceCurr
		SET @varPriceRule = 'ListDocCur'

	end

END
/*Forzar Precio en moneda de documento: FIN*/


/*Inicio: Determinación del Costo*/

--Modo 1 Tomar el costo de Ultima compra o ultimo precio determinado
--SET @varUnitCost = @varLastPurPrc

--Modo 2 El costo lo vamos a sacar de Una lista de precios que determinemos =Ejemplo Lista 5
select  
@varUnitCost = t1.Price
from itm1 t1 
where t1.ItemCode = @parmItemCode and t1.PriceList = 5

--Modo 3. El costo lo sacaríamos de el costo promedio a nivel almacén
--select @varUnitCost = t1.AvgPrice from OITW t1  where t1.ItemCode = @parmItemCode  and t1.WhsCode = @parmWhsCode

/*Fin: Determinación del Costo*/



SET @varExchageRate = 1

--Revisar si la moneda local es diferente a la de la lista de precios
--En caso de ser diferente, debemos de buscar tipo de campo y calcular el valor en moneda local
if @varLocalCurrency <> @varCurrency  
	BEGIN
	
	IF @parmExchageRate = 0
		BEGIN			
		--Get Exchage Rate
		select @varExchageRate = Rate from ORTT
		where RateDate = @parmDate
		and Currency = @varCurrency	
		IF @@ROWCOUNT = 0
			BEGIN 
			set @varFCPrice = 0
			set @varItemPrice = 0
			SET @varExchageRate = 0
			SET @varPriceRule = 'TCNotDef'
			return
			END
		END
	--Tomar como TC el valor que se pasa por parámetro
	ELSE 	
		BEGIN
		SET @varExchageRate = @parmExchageRate
		END
	 				
		set @varFCPrice = @varItemPrice
		set @varFCPriceAftDisc = @varFCPrice
		set @varItemPrice = round(@varFCPrice * @varExchageRate, @varExchangeRatePrecision)		
		set @varItemPriceAftDisc = @varItemPrice

	END
ELSE
	BEGIN --En caso de que el articulo este en la misma moneda loca, no es necesario calcular el valor en moneda extransjera
		SET @varItemPriceAftDisc = @varItemPrice	
		SET @varFCPrice = 0
		SET @varFCPriceAftDisc = 0
		SET @varExchageRate = 0
	END

IF @varOriginDisc > 0 
BEGIN
	SET @varDiscount = ISNULL(@varOriginDisc,0)
	SET @varCurrentDiscount = isnull(@varDiscount,0) 
	set @varItemPriceAftDisc = round(@varItemPrice - (@varItemPrice * @varCurrentDiscount * .01 ), @varExchangeRatePrecision)
	set @varPriceRule = 'OriginWCP'
	

	/*Modificación para soporte de Multimoneda en linea*/
	if @varLocalCurrency <> @varCurrency 
	BEGIN	  
	  set @varFCPriceAftDisc = round((@varFCPrice - (@varFCPrice * @varCurrentDiscount * .01 )), @varExchangeRatePrecision)  
	END
	
END



--/*SECCION: Descuentos directos*/
IF @parmDirectPrice > 0 
	BEGIN

	/*Cambios para Soportar Precio sin calculo*/
	IF @parmLinePriceList = 9999
		set @varDiscount = 0
		set @varCurrency = @parmDirectPriceCurr

		BEGIN
		IF @parmDirectPriceCurr = @varLocalCurrency  --Moneda Local
			BEGIN
			set @varFCPrice = 0 
			set @varItemPrice = @parmDirectPrice
			END
		IF @parmDirectPriceCurr <> @varLocalCurrency 
			BEGIN
			set @varExchageRate = @parmExchageRate
			set @varFCPrice = @parmDirectPrice 			
			set @varItemPrice =  round(@parmDirectPrice * @parmExchageRate, @varExchangeRatePrecision)
			END
		END


	/*FIN:Cambios para Soportar Precio sin calculo*/
	SET @varPriceRule = 'DirectPrice'
	
	--Cuando el precio de lista esta en moneda extranjera
	IF @varFCPrice > 0 
		BEGIN		
		SET @varFCPriceAftDisc = @parmDirectPrice	
		set @varItemPriceAftDisc = round(@varFCPriceAftDisc * @varExchageRate, @varExchangeRatePrecision)
		SET @varDiscount = ( 1 - round( ( @parmDirectPrice / @varFCPrice ) , @varExchangeRatePrecision ) ) * 100
		END
	ELSE
		BEGIN
			SET @varItemPriceAftDisc = @parmDirectPrice 
			SET @varDiscount = ( 1 - round( ( @parmDirectPrice / @varItemPrice )  , @varExchangeRatePrecision ) )	* 100	
		END

	
	SET @varRetCode = 0 
	SET @varRetMsg   = 'Precio Directo ' + @varCurrency
	RETURN
	END


--/*SECCION: Descuentos directos*/
IF @parmDirectDisc <> 0 
	BEGIN
	SET @varPriceRule = 'DirectDisc'
	SET @varDiscount = @parmDirectDisc
	--Cuando el precio de lista esta en moneda extranjera
	IF @varFCPrice > 0 
		BEGIN		
		SET @varFCPriceAftDisc = round((@varFCPrice - (@varFCPrice * @varDiscount * .01 )), @varExchangeRatePrecision)  		
		set @varItemPriceAftDisc = round(@varFCPriceAftDisc * @varExchageRate, @varExchangeRatePrecision)

		END
	ELSE
		BEGIN
			SET @varItemPriceAftDisc = round((@varItemPrice - (@varItemPrice * @varDiscount * .01 )), @varExchangeRatePrecision)  		
		END

		
		SET @varRetCode = 0 
		SET @varRetMsg   = 'Descuento Directo**'
		RETURN
	END

--checks whether a special price has been defined for the particular UoM of the item for this particular business partner
select 
@varDiscount = Discount, 
@varExpand = EXPAND, 
@parmPriceList = t0.ListNum, 
@varCurrency = T1.Currency, 
@varPriceAux = T1.Price
from OSPP t0 inner join itm1 t1 on t0.ItemCode = t1.ItemCode and t0.ListNum = t1.PriceList
where t0.ItemCode = @parmItemCode
and CardCode = @parmCardCode


--iF THERE ARE RECORDS IN SPECIAL PRICES FOR BP AND ITEM
IF @@ROWCOUNT > 0
BEGIN 
	SET @varCurrentRule = 'BpSpPri'
	SET @varCurrRuleLevel = '0'
	SET @varCurrentDiscount = @varDiscount
	
	IF @varExpand = 'Y' 
	BEGIN

		select 
		@varDiscount = Discount, 
		@varExpand = EXPAND , 
		@varLineNum  = LINENUM, 
		@parmPriceList = t0.ListNum, 
		@varCurrency = T1.Currency, 
		@varPriceAux = T1.Price
		from SPP1 t0 
		inner join itm1 t1 on t0.ItemCode = t1.ItemCode and t0.ListNum = t1.PriceList
		where t0.ItemCode = @parmItemCode
		and CardCode = @parmCardCode
		and @parmDate >= FromDate and @parmDate <= isnull(ToDate, '29991231')

		IF @@ROWCOUNT > 0
		BEGIN 
			SET @varCurrRuleLevel = '1'
			SET @varCurrentDiscount = @varDiscount

			IF @varExpand = 'Y' 
			BEGIN
				select top 1 @varDiscount = Discount 
				from SPP2 
				where ItemCode = @parmItemCode
				and CardCode = @parmCardCode
				and SPP1LNum = @varLineNum 
				and @parmQuantity >= Amount 
				order by Amount Desc

				IF @@ROWCOUNT > 0
				BEGIN 
					SET @varCurrRuleLevel = '2'
					SET @varCurrentDiscount = @varDiscount
				END
			END			
		END
	END
END


IF @varCurrentRule = 'BpSpPri'
BEGIN
	--Asign Disccount	

	SET @varDiscount = ISNULL(@varDiscount,0) + ISNULL(@varOriginDisc,0)
	SET @varCurrentDiscount = isnull(@varCurrentDiscount,0) + ISNULL(@varOriginDisc,0)
	set @varItemPriceAftDisc = round(@varItemPrice - (@varItemPrice * @varCurrentDiscount * .01 ), @varExchangeRatePrecision)
	set @varPriceRule = @varPriceRule + '|' +  @varCurrentRule + '|' + @varCurrRuleLevel + '|' + @VarDiscountRatio

	/*Modificación para soporte de Multimoneda en linea*/
	if @varLocalCurrency <> @varCurrency 
	BEGIN	  
	  set @varFCPriceAftDisc = round((@varFCPrice - (@varFCPrice * @varCurrentDiscount * .01 )), @varExchangeRatePrecision)  
	END
	--SELECT @parmItemCode ItemCode, @varItemGroupCode as GroupCode, @varManufactureCode as Manufacture, @parmCardCode as CardCode, @varItemPrice as Price,  @VarDiscountRatio as DiscRatio, @parmDate, @parmQuantity AS Quantity,   @varCurrentDiscount as Discount, @varItemPriceAftDisc as PriceAfterDics,  @varCurrentRule as CurrentRule , @varCurrRuleLevel AS CurrRuleLevel, @VarDiscountRatio  as DiscountRatio, @varPriceListSpecial PriceListSpecial
	Return 	
END 
ELSE
BEGIN --Check for Discount Groups
	IF @varNoDiscount = 'N'
	BEGIN  
		--L Lowest Discount
		--H Highest Discount 
		--A Average Disc. 
		--S Discount Totals 
		--M Discount Multiples

		--Not Suported : Multiple Discounts  and Properties

		select @varCurrentDiscount = 
			case @VarDiscountRatio 
			when 'L' then Min(Discount)
			when 'H' then Max(Discount)
			when 'A' then Avg(Discount)
			when 'S' then Sum(Discount)
			end
		from (
		select Discount, AbsEntry from EDG1 where ObjType = 4 and ObjKey = @parmItemCode 
		AND AbsEntry in (
			select AbsEntry from OEDG where Type = 'A' and ValidFor = 'Y' and @parmDate >= isnull(ValidForm,'19800101') and @parmDate <= isnull(ValidForm,'29991231') 
			union
			select AbsEntry from OEDG where Type = 'C' and ObjCode = @varBPGroupCode and ValidFor = 'Y' and @parmDate >= isnull(ValidForm,'19800101') and @parmDate <= isnull(ValidForm,'29991231')
			union
			select AbsEntry from OEDG where Type = 'S' and ObjCode = @parmCardCode and ValidFor = 'Y' and @parmDate >= isnull(ValidForm,'19800101')and @parmDate <= isnull(ValidForm,'29991231')
			)
				
		union all
		select Discount, AbsEntry  from EDG1 where ObjType = 52 and ObjKey = @varItemGroupCode
		AND AbsEntry in (
			select AbsEntry from OEDG where Type = 'A' and ValidFor = 'Y' and @parmDate >= isnull(ValidForm,'19800101') and @parmDate <= isnull(ValidForm,'29991231') 
			union
			select AbsEntry from OEDG where Type = 'C' and ObjCode = @varBPGroupCode and ValidFor = 'Y' and @parmDate >= isnull(ValidForm,'19800101') and @parmDate <= isnull(ValidForm,'29991231')
			union
			select AbsEntry from OEDG where Type = 'S' and ObjCode = @parmCardCode and ValidFor = 'Y' and @parmDate >= isnull(ValidForm,'19800101')and @parmDate <= isnull(ValidForm,'29991231')
			)

		union ALL
		select Discount, AbsEntry  from EDG1 where ObjType = 43 and ObjKey = @varManufactureCode
		AND AbsEntry in (
			select AbsEntry from OEDG where Type = 'A' and ValidFor = 'Y' and @parmDate >= isnull(ValidForm,'19800101') and @parmDate <= isnull(ValidForm,'29991231') 
			union
			select AbsEntry from OEDG where Type = 'C' and ObjCode = @varBPGroupCode and ValidFor = 'Y' and @parmDate >= isnull(ValidForm,'19800101') and @parmDate <= isnull(ValidForm,'29991231')
			union
			select AbsEntry from OEDG where Type = 'S' and ObjCode = @parmCardCode and ValidFor = 'Y' and @parmDate >= isnull(ValidForm,'19800101')and @parmDate <= isnull(ValidForm,'29991231')
			)
		union ALL
		select Discount, AbsEntry  from EDG1 where  dbo.wspGetItemQryGroupValue(@parmItemCode, ObjKey)  = 'Y'
		AND AbsEntry in (
			select AbsEntry from OEDG where Type = 'A' and ValidFor = 'Y' and @parmDate >= isnull(ValidForm,'19800101') and @parmDate <= isnull(ValidForm,'29991231') 
			union
			select AbsEntry from OEDG where Type = 'C' and ObjCode = @varBPGroupCode or  ObjCode = 0 and ValidFor = 'Y' and @parmDate >= isnull(ValidForm,'19800101') and @parmDate <= isnull(ValidForm,'29991231')
			union
			select AbsEntry from OEDG where Type = 'S' and ObjCode = @parmCardCode and ValidFor = 'Y' and @parmDate >= isnull(ValidForm,'19800101')and @parmDate <= isnull(ValidForm,'29991231')
			)

		) T0 --ME DEVUELVE EL DESCUENTO DE ACUERDO A LA POLITICA ELEGIDA
		

		IF ISNULL(@varCurrentDiscount,0) > 0
			SET @varCurrentRule = 'DicGroup'
	END 
	--End if @varNoDiscount = 'N'
	
END
--END Check for Discount Groups

--special price has been defined for the item and the price list 


IF @varCurrentRule = 'DicGroup'
BEGIN
	--Asign Disccount
	SET @varCurrentDiscount = isnull(@varCurrentDiscount,0) + ISNULL(@varOriginDisc,0)	
	SET @varDiscount = ISNULL(@varCurrentDiscount,0)  + ISNULL(@varOriginDisc,0)	
	set @varItemPriceAftDisc = round((@varItemPrice - (@varItemPrice * @varCurrentDiscount * .01 )),2)
	set @varPriceRule = @varPriceRule + '|' +  @varCurrentRule + '|' + isnull(@varCurrRuleLevel,'') + '|' + isnull(@VarDiscountRatio,'')
	/**/
	--SELECT @parmItemCode ItemCode, @varItemGroupCode as GroupCode, @varManufactureCode as Manufacture, @parmCardCode as CardCode, @varItemPrice as Price,  @VarDiscountRatio as DiscRatio, @parmDate, @parmQuantity AS Quantity,   @varCurrentDiscount as Discount, @varItemPriceAftDisc as PriceAfterDics,  @varCurrentRule as CurrentRule , @varCurrRuleLevel AS CurrRuleLevel, @VarDiscountRatio  as DiscountRatio, @varPriceListSpecial PriceListSpecial

	if @varLocalCurrency <> @varCurrency 
	BEGIN
	  set @varFCPriceAftDisc = round((@varFCPrice - (@varFCPrice * @varCurrentDiscount * .01 )), @varExchangeRatePrecision)  	  		
	END
	
	Return 
	
END 
ELSE
BEGIN -- Manejo de Precios por Periodo y Cantidad
	SET @varPriceListSpecial = '*'+RTRIM(CAST(@parmPriceList AS varchar))

	select @varDiscount = Discount, 
	@varExpand = EXPAND , 
	@varLineNum  = LINENUM 
	from SPP1 
	where ItemCode = @parmItemCode
	and CardCode = @varPriceListSpecial
	and @parmDate >= FromDate and @parmDate <= isnull(ToDate, '29991231')		

		IF @@ROWCOUNT > 0
		BEGIN 
			SET @varCurrentRule = 'PeriodQty'
			SET @varCurrRuleLevel = '1'
			SET @varCurrentDiscount = @varDiscount

			IF @varExpand = 'Y' 
			BEGIN
				select top 1 @varDiscount = Discount from SPP2 
				where ItemCode = @parmItemCode
				and CardCode = @varPriceListSpecial
				and SPP1LNum = @varLineNum 
				and @parmQuantity >= Amount 
				order by Amount Desc

				IF @@ROWCOUNT > 0
				BEGIN 
					SET @varCurrRuleLevel = '2'
					SET @varCurrentDiscount = @varDiscount
				END
			END
		END
END


IF @varCurrentRule =  'PeriodQty' and @varDiscount <> 0
BEGIN

	--SELECT @varItemPrice as InitListSPvarItemPrice, @varCurrency as ListSPvarCurrency

	--Asign Disccount
	SET @varDiscount = ISNULL(@varDiscount,0)  + ISNULL(@varOriginDisc,0)
	SET @varCurrentDiscount = isnull(@varCurrentDiscount,0)  + ISNULL(@varOriginDisc,0)
	set @varItemPriceAftDisc = round((@varItemPrice - (@varItemPrice * @varCurrentDiscount * .01 )), @varExchangeRatePrecision)
	set @varPriceRule = @varPriceRule  + '|' + @varCurrentRule + '|' + @varCurrRuleLevel + '|' + @VarDiscountRatio
	
	if @varLocalCurrency <> @varCurrency 
	BEGIN	  
	  set @varFCPriceAftDisc = round((@varFCPrice - (@varFCPrice * @varCurrentDiscount * .01 )), @varExchangeRatePrecision)  
	END
	Return 
	
END