Wiki

This procedure can be used as the data source in a Versago look-up report to find the lowest price for a business partner (typically a customer) based on price list, special pricing, date sensitive pricing, and quantity break pricing. The procedure is placed in the target SAP Business One database.

All price records for the defined input parameters are returned with the code as shown below. To return only the lowest price, replace “select *” with “select min(a.price) in the top SELECT statement.

Note that this logic does not include Discount pricing. This is only for “special” pricing.

ALTER PROCEDURE [dbo].[vgo_sp_CheckSBOPricing]  @currency VARCHAR(5), @ItemCode nvarchar(50), @PriceList INT, @CardCode nvarchar(50), @Qty DECIMAL(18,6) AS
/*
21/APR/2019 (RUnger) - Initial version
*/
SELECT *
FROM 
(
-- Price List
SELECT
-- *
'9_PL1' AS [SOURCE], t0.ItemCode, '' AS [CardCode], t0.PriceList, t0.Price, 0 AS [DiscountPct],  t0.Currency, '' AS [FromDate], '' AS [ToDate], 0 AS [Amount], 'Y' AS [Active], 'Y' AS [Valid] 
FROM ITM1 t0
-- where t0.PriceList = 5 AND t0.ItemCode = 'a00001'
UNION ALL
-- Special Pricing
SELECT
-- *
'8_SPP', t0.ItemCode, t0.CardCode, t0.ListNum, t0.Price, t0.Discount, t0.Currency, '', '', 0, 'Y', 'Y'
FROM OSPP t0
UNION ALL
-- Date Range
SELECT
-- *
'7_SPP1', t0.ItemCode, t0.CardCode, t0.ListNum, t0.Price, t0.Discount, t0.Currency, t0.FromDate, t0.ToDate, 0, 'Y',
CASE
WHEN CONVERT(VARCHAR,getdate(),112) BETWEEN CONVERT(VARCHAR,t0.FromDate,112) AND CONVERT(VARCHAR,t0.ToDate,112) THEN 'Y'
ELSE 'N'
END
FROM SPP1 t0
-- -- where t0.ItemCode = 'a00001'
UNION ALL
-- Quantity
SELECT
-- *
'6_SPP2', t0.ItemCode, t0.CardCode, 0, t0.Price, t0.Discount, t0.Currency,'', '', t0.Amount, 'Y',
'Y'
FROM SPP2 t0
WHERE t0.ItemCode = 'A00001'
) a
WHERE a.Valid = 'Y'
AND a.Currency = @currency
AND a.ItemCode = @ItemCode
AND a.PriceList IN (@PriceList, 0)
AND a.CardCode IN (@CardCode, '*5', '')
AND a.Amount <= @qty

In many places, SAP Business One (and other applications) store the date and time in two separate columns. At the same time, it may be necessary to have the two values combined into a single DateTime value. An example of this is for use in a Versago calendar report. The following SQL statement (SQL Server only) is used to create the single DateTime value.

CAST(
CAST(datepart(YEAR,YourDateValue) AS VARCHAR)
+ '-'
+ RIGHT('00'+CAST(datepart(MONTH,YourDateValue) AS VARCHAR),2)
+ '-'
+ RIGHT('00'+CAST(datepart(DAY,worked_date) AS VARCHAR),2)
+ ' '
+ LEFT(RIGHT('0000'+REPLACE(isnull(YourTimeValue,'0000'),':',''),4),2)
+ ':'
+ LEFT(RIGHT('0000'+REPLACE(isnull(YourTimeValue,'0000'),':',''),4),2)
AS datetime) AS [CombinedDateTime]