-- Provided by www.DynDeveloper.com
-- Free to use, please leave the line above in your code
-- use at your own risk, never put code into production without thorough testing.
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'ddf_FiscalPeriodCloseDate')
DROP FUNCTION ddf_FiscalPeriodCloseDate
GO
CREATE FUNCTION ddf_FiscalPeriodCloseDate (@inputDate AS DATE, @series int)
--to test this, run this script:
-- SELECT dbo.ddf_FiscalPeriodCloseDate('12/15/2017',3) as MyFunction
/*
Field values:
1 = All
2 = Financial
3 = Sales
4 = Purchasing
5 = Inventory
6 = Payroll - USA
7 = Project
*/
Returns date
AS
begin
declare @out date
SELECT TOP 1 @out = PERDENDT
FROM sy40100
WHERE PERDENDT >= @inputDate
AND series = @series
AND periodid <> 0 --avoid Beginning Balance entries
ORDER BY PERDENDT
return (@out)
end
go
grant exec on ddf_FiscalPeriodCloseDate to public