Skip to content

Instantly share code, notes, and snippets.

@wqweto

wqweto/usp_nom_Gener_PriceListsChildren.sql

Created November 16, 2018 10:50
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save wqweto/3c28edcb4ffb4cb8a773b4c6005d616a to your computer and use it in GitHub Desktop.
Save wqweto/3c28edcb4ffb4cb8a773b4c6005d616a to your computer and use it in GitHub Desktop.
Download ZIP
This stored procedure generates client price lists upon parent price list modification
Raw
usp_nom_Gener_PriceListsChildren.sql
IF OBJECT_ID('usp_nom_Gener_PriceListsChildren') IS NOT NULL DROP PROC usp_nom_Gener_PriceListsChildren
GO
-- exec usp_sys_StartOperation @User='ucs'
/*
BEGIN TRAN
exec dbo.usp_nom_Gener_PriceListsChildren 1,NULL,'{B0CEBE23-AA32-42D3-9FEC-082CA8B977F8}',NULL,NULL,'F715F915-4D3F-4710-A257-DD34BD2442B1'
-- exec dbo.usp_nom_Gener_PriceListsChildren 3, 7, '{F0736D5D-0004-43BE-859D-731DDD2EC5A8}', NULL, NULL, '6F0D3E0C-7CEE-4A2B-985B-C3C40ED6015C'
SELECT * FROM nom_priceLists WHERE PriceList_ID = 'B0CEBE23-AA32-42D3-9FEC-082CA8B977F8'
ROLLBACK
*/
CREATE PROC usp_nom_Gener_PriceListsChildren (
@PriceFormType INT = NULL
, @OperType INT = NULL
, @PlTypeIDs TEXT = NULL
, @Doc_ID UNIQUEIDENTIFIER = NULL
, @CgStore_ID UNIQUEIDENTIFIER = NULL
, @ParentPlType_ID UNIQUEIDENTIFIER = NULL
, @Itm_ID UNIQUEIDENTIFIER = NULL
, @PriceMakeItems BIT = 0
) AS
/*------------------------------------------------------------------------
Dreem Enterprise Project
Copyright (c) 2003-2018 Unicontsoft
Generira tseni w podchineni tsenowi listi
Modifications:
20111102 WQW Initial implementation
20120503 WQW REF: polzwa item vatprice scale pri RoundingType = 2
20120504 WQW ADD: params @PlTypeIDs i @_OperType
20130312 WQW ADD: Measure_ID na items
20130802 WQW REF: polzwa dimensii
20140220 WQW REF: wzima tseni na parent s wkl. promotsii
20140326 WQW REF: retval na usp_nom_Get_ItemsPriceList
20140409 WQW REF: polzwa fn_nom_Get_RoundPrice
20140523 WQW REF: tip danni na BasePriceWithVat
20140626 WQW REF: syobrazqwa se s Dimension_ID na pl type
20150630 WQW REF: syobrazqwa heirarchy na dim valies za danni ot v_nom_PriceListsDimValues
20151014 WQW REF: params, generira wsichki tipowe price form type
20151104 WQW ADD: param @Itm_ID
20151211 WQW REF: syobrazqwa IsDefCurrency na plt
20151214 WQW REF: suppl price ot suppl tsenowa lista, ne ot pokupka
20160405 WQW REF: cursor ako ne e podadena parent pl type
20180411 WQW REF: calc NewPrice
*/------------------------------------------------------------------------
SET NOCOUNT ON
CREATE TABLE #TmpIDs (
ID UNIQUEIDENTIFIER
)
CREATE TABLE #TmpNewPrices (
ID UNIQUEIDENTIFIER NOT NULL
, NewPrice DECIMAL(19, 8) NOT NULL
, ItemVatRate DECIMAL(19, 8) NOT NULL
, ItemVatPriceScale INT
, Modulo INT
, Reminder INT
, Direction INT
, IsVatIncluded BIT
, Currency_ID UNIQUEIDENTIFIER
, Rate DECIMAL(19, 8)
)
CREATE TABLE #TmpPrc (
ID UNIQUEIDENTIFIER
, Itm_ID UNIQUEIDENTIFIER
, IsVat BIT
, BasePrice DECIMAL(19, 8)
, BasePriceWithVat DECIMAL(20, 8)
, Measure_ID UNIQUEIDENTIFIER
, Currency_ID UNIQUEIDENTIFIER
, Rate DECIMAL(19, 8)
, MeasureCode VARCHAR(50) COLLATE DATABASE_DEFAULT
, CurrencyCode VARCHAR(50) COLLATE DATABASE_DEFAULT
, PromoPlType_ID UNIQUEIDENTIFIER
)
CREATE TABLE #TmpPlDimValues (
PlType_ID UNIQUEIDENTIFIER
, ListValue_ID UNIQUEIDENTIFIER
, CoefficientA DECIMAL(19, 8)
, CoefficientB DECIMAL(19, 8)
, UNIQUE CLUSTERED (PlType_ID, ListValue_ID)
)
CREATE TABLE #TmpStoreSaldo (
Itm_ID UNIQUEIDENTIFIER
, Currency_ID UNIQUEIDENTIFIER
, Measure_ID UNIQUEIDENTIFIER
, Quantity DECIMAL(19, 8)
, QuantitySecMeasure DECIMAL(19, 8)
, QuantityReserved DECIMAL(19, 8)
, TotalSum DECIMAL(19, 2)
, MinQuantity DECIMAL(19, 8)
, LD DATETIME
)
DECLARE @_PriceFormType INT
, @_OperType INT
, @_Doc_ID UNIQUEIDENTIFIER
, @_CgStore_ID UNIQUEIDENTIFIER
, @_ParentPlType_ID UNIQUEIDENTIFIER
, @_Itm_ID UNIQUEIDENTIFIER
, @_Itm_IDs VARCHAR(50)
--- setup
, @DefItmDimension_ID UNIQUEIDENTIFIER
, @CgOwner_ID UNIQUEIDENTIFIER
, @DefVatPriceScale INT
, @SupplPriceList_ID UNIQUEIDENTIFIER
, @DefCurrency_ID UNIQUEIDENTIFIER
, @User VARCHAR(40)
, @Today DATETIME
, @CrsParent CURSOR
--- prevent param sniffing
SELECT @_PriceFormType = COALESCE(@PriceFormType, 1)
, @_OperType = COALESCE(@OperType, CASE WHEN @Doc_ID IS NOT NULL THEN 1 ELSE 7 END)
, @_Doc_ID = @Doc_ID
, @_CgStore_ID = @CgStore_ID
, @_ParentPlType_ID = @ParentPlType_ID
, @_Itm_ID = @Itm_ID
, @_Itm_IDs = @Itm_ID
--- fetch setup info
SELECT @DefItmDimension_ID = stp.DefItmDimension_ID
, @CgOwner_ID = stp.CgOwner_ID
, @DefVatPriceScale = stp.DefVatPriceScale
, @SupplPriceList_ID = stp.SupplPriceList_ID
, @DefCurrency_ID = stp.DefCurrency_ID
, @User = dbo.fn_sys_GetCurrentUser()
, @Today = dbo.fn_sys_RoundDate('d', GETDATE())
FROM dbo.v_Setup stp
--- za ucsPftParentPl cursor ako ne e podadena parent pl type
IF @_PriceFormType = 3 AND @_ParentPlType_ID IS NULL
BEGIN
SET @CrsParent = CURSOR FAST_FORWARD FOR
SELECT DISTINCT ParentPlType_ID
FROM dbo.v_ref_PriceListsTypesInt
WHERE PriceFormType = @_PriceFormType
AND (ID IN (SELECT ID FROM dbo.fn_sys_SplitGuids(@PlTypeIDs))
OR IsAutoGener = 1 AND @PlTypeIDs IS NULL)
AND ParentPlType_ID IS NOT NULL
OPEN @CrsParent
WHILE 1=1
BEGIN
FETCH NEXT
FROM @CrsParent
INTO @_ParentPlType_ID
IF @@FETCH_STATUS <> 0 BREAK
EXEC dbo.usp_nom_Gener_PriceListsChildren @PriceFormType, @OperType, @PlTypeIDs
, @Doc_ID, @CgStore_ID, @_ParentPlType_ID, @Itm_ID, @PriceMakeItems
END
CLOSE @CrsParent
DEALLOCATE @CrsParent
GOTO QH
END
IF @_PriceFormType = 1 -- ucsPftSuppl
BEGIN
--- prepare suppl price lists
INSERT #TmpIDs
SELECT ID
FROM dbo.v_ref_PriceListsTypesInt
WHERE PriceFormType = @_PriceFormType
AND (ID IN (SELECT ID FROM dbo.fn_sys_SplitGuids(@PlTypeIDs))
OR IsAutoGener = 1 AND @PlTypeIDs IS NULL)
AND (EXISTS(SELECT 0
FROM dbo.v_nom_PriceLists prc
JOIN dbo.v_sup_DocsDetailsInt row
ON prc.Itm_ID = row.Itm_ID
AND prc.Measure_ID = row.Measure_ID
WHERE prc.PriceList_ID = v_ref_PriceListsTypesInt.ID
AND row.Doc_ID = @Doc_ID)
OR @Doc_ID IS NULL)
IF @@ROWCOUNT = 0 GOTO QH
--- prepare last suppl prices
INSERT #TmpPrc(ID
, Itm_ID
, IsVat
, BasePrice
, Measure_ID
, Currency_ID
, Rate)
SELECT prc.ID
, prc.Itm_ID
, prc.IsVat
, prc.BasePrice
, prc.Measure_ID
, prc.Currency_ID
, prc.Rate
FROM (
SELECT ( SELECT TOP 1 prc.ID
FROM dbo.v_nom_PriceListsInt prc
LEFT JOIN ( dbo.v_sup_DocsDetailsInt row
JOIN dbo.v_sup_DocsInt doc
ON row.Doc_ID = doc.ID
AND row.DocDate = doc.DocDate
)
ON prc.AutoUpdSupRow_ID = row.ID
AND prc.AutoUpdDate = row.DocDate
WHERE prc.Itm_ID = itm.ID
AND prc.PriceList_ID = @SupplPriceList_ID
ORDER BY COALESCE(prc.AutoUpdDate, prc.LD) DESC
, doc.OperNo DESC
, row.RowNo DESC ) AS Prc_ID
FROM dbo.v_nom_ItemsUpdate itm
WHERE itm.IsActive = 1
) s
JOIN dbo.v_nom_PriceListsInt prc
ON s.Prc_ID = prc.ID
WHERE (EXISTS(SELECT 0
FROM dbo.v_sup_DocsDetailsInt suprow
WHERE suprow.Doc_ID = @_Doc_ID
AND suprow.Itm_ID = prc.Itm_ID
AND suprow.Measure_ID = prc.Measure_ID) OR @_Doc_ID IS NULL)
AND (prc.Itm_ID = @_Itm_ID OR @_Itm_ID IS NULL)
IF @@ROWCOUNT = 0 GOTO QH
END
ELSE IF @_PriceFormType = 2 -- ucsPftAvgPrice
BEGIN
--- prepare avg store price lists
INSERT #TmpIDs
SELECT ID
FROM dbo.v_ref_PriceListsTypesInt
WHERE PriceFormType = @_PriceFormType
AND CgStore_ID = @_CgStore_ID
AND (ID IN (SELECT ID FROM dbo.fn_sys_SplitGuids(@PlTypeIDs))
OR IsAutoGener = 1 AND @PlTypeIDs IS NULL)
AND (EXISTS(SELECT 0
FROM dbo.v_nom_PriceLists prc
JOIN dbo.v_sto_DocsDetailsInt row
ON prc.Itm_ID = row.Itm_ID
AND prc.Measure_ID = row.Measure_ID
WHERE prc.PriceList_ID = v_ref_PriceListsTypesInt.ID
AND row.Doc_ID = @Doc_ID)
OR @Doc_ID IS NULL)
IF @@ROWCOUNT = 0 GOTO QH
INSERT #TmpStoreSaldo
EXEC dbo.usp_nom_Get_ItemsStoreSaldo @_CgStore_ID
INSERT #TmpPrc(ID
, Itm_ID
, IsVat
, BasePrice
, Measure_ID
, Currency_ID
, Rate)
SELECT NEWID() AS ID
, t.Itm_ID
, 0 AS IsVat
, COALESCE(
CONVERT(DECIMAL(19, 8), t.TotalSum
/ NULLIF(t.Quantity, 0))
, 0) AS BasePrice
, t.Measure_ID
, t.Currency_ID
, COALESCE(rat.Rate, 1) AS Rate
FROM #TmpStoreSaldo t
LEFT JOIN dbo.fn_nom_Get_CurrencyRates(@Today, DEFAULT) rat
ON t.Currency_ID = rat.ID
WHERE (EXISTS(SELECT 0
FROM dbo.v_sto_DocsDetailsInt row
WHERE row.Doc_ID = @_Doc_ID
AND row.Itm_ID = t.Itm_ID
AND row.Measure_ID = t.Measure_ID) OR @_Doc_ID IS NULL)
AND (t.Itm_ID = @_Itm_ID OR @_Itm_ID IS NULL)
IF @@ROWCOUNT = 0 GOTO QH
END
ELSE IF @_PriceFormType = 3 -- ucsPftParentPl
BEGIN
--- prepare children price lists
INSERT #TmpIDs
SELECT ID
FROM dbo.v_ref_PriceListsTypesInt
WHERE PriceFormType = @_PriceFormType
AND ParentPlType_ID = @_ParentPlType_ID
AND (ID IN (SELECT ID FROM dbo.fn_sys_SplitGuids(@PlTypeIDs))
OR IsAutoGener = 1 AND @PlTypeIDs IS NULL)
IF @@ROWCOUNT = 0 GOTO QH
--- prepare parent prices
INSERT #TmpPrc
EXEC dbo.usp_nom_Get_ItemsPriceList @Itm_IDs = @_Itm_IDs, @Cg_ID = @CgOwner_ID, @PriceListType_ID = @_ParentPlType_ID
UPDATE t
SET Rate = COALESCE(rat.Rate, 1)
FROM #TmpPrc t
LEFT JOIN dbo.fn_nom_Get_CurrencyRates(@Today, DEFAULT) rat
ON t.Currency_ID = rat.ID
IF @@ROWCOUNT = 0 GOTO QH
END
ELSE BEGIN
GOTO QH
END
-- SELECT * FROM #TmpPrc
--- prepare dim values heirarchy
INSERT #TmpPlDimValues
SELECT PriceListType_ID, ListValue_ID, CoefficientA, CoefficientB
FROM dbo.v_nom_PriceListsDimValues
WHILE @@ROWCOUNT <> 0
BEGIN
INSERT #TmpPlDimValues
SELECT t.PlType_ID, val.ID, t.CoefficientA, t.CoefficientB
FROM dbo.v_nom_DimListsValues val
JOIN #TmpPlDimValues t
ON val.P_ID = t.ListValue_ID
WHERE NOT EXISTS (SELECT s.ListValue_ID FROM #TmpPlDimValues s
WHERE s.PlType_ID = t.PlType_ID AND s.ListValue_ID = val.ID)
END
--- insert new
IF (@_OperType & 2) <> 0
BEGIN
--- tsenoobrazuwane
IF @PriceMakeItems = 1
BEGIN
INSERT dbo.v_nom_PriceListsItems(ID
, PriceListType_ID
, Itm_ID
, Measure_ID
, CoefficientA
, CoefficientB
, CU
, CD
, LU
, LD)
SELECT NEWID() AS ID
, t.ID AS PriceListType_ID
, src.Itm_ID
, src.Measure_ID
, 0 AS CoefficientA
, 0 AS CoefficientB
, @User AS CU
, GETDATE() AS CD
, @User AS LU
, GETDATE() AS LD
FROM #TmpPrc src
CROSS JOIN #TmpIDs t
WHERE NOT EXISTS (SELECT 0 FROM dbo.v_nom_PriceListsItems
WHERE PriceListType_ID = t.ID
AND Itm_ID = src.Itm_ID
AND Measure_ID = src.Measure_ID)
END
--- tseni
INSERT dbo.v_nom_PriceLists(ID
, PriceList_ID
, Cg_ID
, Itm_ID
, Currency_ID
, Measure_ID
, BasePrice
, IsVat
, CU
, CD
, LU
, LD
, Note
, Rate)
SELECT NEWID() AS ID
, plt.ID AS PriceList_ID
, @CgOwner_ID AS Cg_ID
, src.Itm_ID
, CASE WHEN plt.IsDefCurrency = 1 THEN
@DefCurrency_ID
ELSE
src.Currency_ID
END AS Currency_ID
, src.Measure_ID
, CASE WHEN plt.IsDefCurrency = 1 THEN
CONVERT(DECIMAL(19, 8),
CONVERT(DECIMAL(24, 12), src.BasePrice)
* CONVERT(DECIMAL(24, 12), src.Rate))
ELSE
src.BasePrice
END AS BasePrice
, src.IsVat
, @User AS CU
, GETDATE() AS CD
, @User AS LU
, GETDATE() AS LD
, '' AS Note
, CASE WHEN plt.IsDefCurrency = 1 THEN
1
ELSE
src.Rate
END AS Rate
FROM #TmpPrc src
CROSS JOIN #TmpIDs t
JOIN dbo.v_ref_PriceListsTypesInt plt
ON t.ID = plt.ID
LEFT JOIN dbo.v_nom_ItemsDimensions idi
ON idi.Itm_ID = src.Itm_ID
AND idi.Dimension_ID = COALESCE(plt.Dimension_ID, @DefItmDimension_ID)
LEFT JOIN dbo.v_nom_PriceListsItems pli
ON pli.PriceListType_ID = plt.ID
AND pli.Itm_ID = src.Itm_ID
AND pli.Measure_ID = src.Measure_ID
LEFT JOIN #TmpPlDimValues pdv
ON pdv.PlType_ID = plt.ID
AND pdv.ListValue_ID = idi.ListValue_ID
LEFT JOIN dbo.v_nom_PriceListsDimValues pdn
ON pdn.PriceListType_ID = plt.ID
AND pdn.ListValue_ID IS NULL
AND idi.ListValue_ID IS NOT NULL
WHERE COALESCE(pli.CoefficientA, pdv.CoefficientA, pdn.CoefficientA) IS NOT NULL
AND NOT EXISTS (SELECT 0 FROM dbo.v_nom_PriceLists
WHERE PriceList_ID = plt.ID
AND Cg_ID = @CgOwner_ID
AND Itm_ID = src.Itm_ID
AND Measure_ID = src.Measure_ID)
END
--- update
IF (@_OperType & 1) <> 0
BEGIN
INSERT #TmpNewPrices
SELECT prc.ID
, CONVERT(DECIMAL(19, 8),
CONVERT(DECIMAL(24, 12),
CONVERT(DECIMAL(24, 12),
COALESCE(
CONVERT(DECIMAL(24, 12),
CONVERT(DECIMAL(24, 12),
CONVERT(DECIMAL(24, 12),
CONVERT(DECIMAL(24, 12), src.BasePrice
* CONVERT(DECIMAL(24, 12), src.Rate))
/ CONVERT(DECIMAL(24, 12), CASE WHEN plt.IsDefCurrency = 1 THEN 1 ELSE prc.Rate END)))
* CONVERT(DECIMAL(24, 12), 1.0 + plt.ContainerInsurancePrc / 100.0))
+ CASE WHEN plt.ContainerMeasure_ID = prc.Measure_ID OR plt.ContainerMeasure_ID IS NULL THEN
CONVERT(DECIMAL(24, 12), plt.ContainerOcean + plt.ContainerLand)
ELSE
CONVERT(DECIMAL(24, 12),
CONVERT(DECIMAL(24, 12),
CONVERT(DECIMAL(24, 12), plt.ContainerOcean + plt.ContainerLand)
* CASE WHEN prc.Measure_ID = itm.Measure_ID THEN 1 ELSE prcmea.Ratio END)
/ NULLIF(cntmea.Ratio, 0))
END, 0))
* CONVERT(DECIMAL(24, 12), 1.0 + plt.ContainerApplyDutyTariff * COALESCE(dtf.TariffPrc, 0) / 100.0))
* CONVERT(DECIMAL(24, 12), 1.0 + COALESCE(pli.CoefficientA, pdv.CoefficientA, pdn.CoefficientA) / 100.0))
+
CONVERT(DECIMAL(19, 8),
COALESCE(pli.CoefficientB, pdv.CoefficientB, pdn.CoefficientB, 0)) AS NewPrice
, itm.Vat AS ItemVatRate
, itm.VatPriceScale AS ItemVatPriceScale
, rnd.Modulo
, rnd.Reminder
, rnd.Direction
, rnd.IsVatIncluded
, CASE WHEN plt.IsDefCurrency = 1 THEN
@DefCurrency_ID
ELSE
src.Currency_ID
END AS Currency_ID
, CASE WHEN plt.IsDefCurrency = 1 THEN
1
ELSE
src.Rate
END AS Rate
FROM dbo.v_nom_PriceLists prc
JOIN dbo.v_ref_PriceListsTypesInt plt
ON prc.PriceList_ID = plt.ID
JOIN #TmpPrc src
ON prc.Itm_ID = src.Itm_ID
AND prc.Measure_ID = src.Measure_ID
JOIN dbo.v_nom_ItemsUpdate itm
ON prc.Itm_ID = itm.ID
LEFT JOIN dbo.v_nom_ItemsDimensions idi
ON idi.Itm_ID = prc.Itm_ID
AND idi.Dimension_ID = COALESCE(plt.Dimension_ID, @DefItmDimension_ID)
LEFT JOIN dbo.v_nom_PriceListsItems pli
ON pli.PriceListType_ID = prc.PriceList_ID
AND pli.Itm_ID = prc.Itm_ID
AND pli.Measure_ID = prc.Measure_ID
LEFT JOIN #TmpPlDimValues pdv
ON pdv.PlType_ID = prc.PriceList_ID
AND pdv.ListValue_ID = idi.ListValue_ID
LEFT JOIN dbo.v_nom_PriceListsDimValues pdn
ON pdn.PriceListType_ID = prc.PriceList_ID
AND pdn.ListValue_ID IS NULL
AND idi.ListValue_ID IS NOT NULL
LEFT JOIN dbo.v_nom_PriceRoundTypes rnd
ON rnd.ID = plt.RoundType_ID
LEFT JOIN dbo.v_nom_ItemsMeasures prcmea
ON prcmea.Itm_ID = prc.Itm_ID
AND prcmea.Measure_ID = prc.Measure_ID
LEFT JOIN dbo.v_nom_ItemsMeasures cntmea
ON cntmea.Itm_ID = prc.Itm_ID
AND cntmea.Measure_ID = plt.ContainerMeasure_ID
LEFT JOIN dbo.v_nom_DutyTariffs dtf
ON itm.DutyTariff_ID = dtf.ID
WHERE prc.PriceList_ID IN (SELECT ID FROM #TmpIDs)
AND prc.Cg_ID = @CgOwner_ID
AND prc.Rate <> 0 --- inache division by zero
AND COALESCE(pli.CoefficientA, pdv.CoefficientA, pdn.CoefficientA) IS NOT NULL
--SELECT * FROM #TmpNewPrices
--- apply coefficients + round type
UPDATE dbo.v_nom_PriceLists
SET BasePrice = ( SELECT dbo.fn_nom_Get_RoundPrice(t.NewPrice
, t.ItemVatRate
, COALESCE(t.ItemVatPriceScale, @DefVatPriceScale)
, t.Modulo
, t.Reminder
, t.Direction
, t.IsVatIncluded)
FROM #TmpNewPrices t
WHERE t.ID = v_nom_PriceLists.ID )
, Currency_ID = ( SELECT t.Currency_ID
FROM #TmpNewPrices t
WHERE t.ID = v_nom_PriceLists.ID )
, Rate = ( SELECT t.Rate
FROM #TmpNewPrices t
WHERE t.ID = v_nom_PriceLists.ID )
, LU = @User
, LD = GETDATE()
WHERE EXISTS (SELECT 0
FROM #TmpNewPrices t
WHERE t.ID = v_nom_PriceLists.ID
AND (dbo.fn_nom_Get_RoundPrice(t.NewPrice
, t.ItemVatRate
, COALESCE(t.ItemVatPriceScale, @DefVatPriceScale)
, t.Modulo
, t.Reminder
, t.Direction
, t.IsVatIncluded) <> v_nom_PriceLists.BasePrice
OR t.Currency_ID <> v_nom_PriceLists.Currency_ID
OR t.Rate <> v_nom_PriceLists.Rate))
END
--- remove missing
IF (@_OperType & 4) <> 0
BEGIN
--- tsenoobrazuwane
IF @PriceMakeItems = 1
BEGIN
DELETE dbo.v_nom_PriceListsItems
WHERE PriceListType_ID IN (SELECT ID FROM #TmpIDs)
AND NOT EXISTS (SELECT 0 FROM #TmpPrc src
WHERE src.Itm_ID = v_nom_PriceListsItems.Itm_ID
AND src.Measure_ID = v_nom_PriceListsItems.Measure_ID)
END
--- tseni
DELETE dbo.v_nom_PriceLists
WHERE PriceList_ID IN (SELECT ID FROM #TmpIDs)
AND (Itm_ID = @_Itm_ID OR @_Itm_ID IS NULL)
AND (
NOT EXISTS( SELECT 0 FROM #TmpPrc src
WHERE src.Itm_ID = v_nom_PriceLists.Itm_ID
AND src.Measure_ID = v_nom_PriceLists.Measure_ID)
OR NOT EXISTS( SELECT 0
FROM dbo.v_nom_PriceLists src
JOIN dbo.v_ref_PriceListsTypesInt plt
ON src.PriceList_ID = plt.ID
LEFT JOIN dbo.v_nom_ItemsDimensions idi
ON idi.Itm_ID = src.Itm_ID
AND idi.Dimension_ID = COALESCE(plt.Dimension_ID, @DefItmDimension_ID)
LEFT JOIN dbo.v_nom_PriceListsItems pli
ON pli.PriceListType_ID = plt.ID
AND pli.Itm_ID = src.Itm_ID
AND pli.Measure_ID = src.Measure_ID
LEFT JOIN #TmpPlDimValues pdv
ON pdv.PlType_ID = plt.ID
AND pdv.ListValue_ID = idi.ListValue_ID
LEFT JOIN dbo.v_nom_PriceListsDimValues pdn
ON pdn.PriceListType_ID = plt.ID
AND pdn.ListValue_ID IS NULL
AND idi.ListValue_ID IS NOT NULL
WHERE src.ID = v_nom_PriceLists.ID
AND COALESCE(pli.CoefficientA, pdv.CoefficientA, pdn.CoefficientA) IS NOT NULL) )
END
QH:
GO
GRANT EXEC ON usp_nom_Gener_PriceListsChildren TO Dreem
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment