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]

If the DISTINCT operator is needed, it must be done within a sub-query. For example, assume you have 12 records that have three distinct values of “Name”. You want to display only the three unique names. If you use the following statement, Versago will reject it.

SELECT DISTINCT
t0.Name
FROM TableName t0

However, if this statement is used as a sub-query, it can be used in Versago. The statement will look like this.

SELECT a0.* FROM
(
SELECT DISTINCT
t0.Name
FROM TableName t0
) a0

In this case, “a0” is an alias for the results of the SELECT DISTINCT statement. There is no significance to the alias name “a0”. It is simply used here for convenience.