This may not help you because (I think) you have to have the Manufacturing for GP module installed, but I was faced with a similar issue. Here is the approach I took to create a SQL table, view and ultimately a report to determine scheduled Down Days in GP.
GP>Tools>Setup>Manufacturing>System Defaults>Calendar will open a Shop Calendar (this requires system password). At first I selected the radio button next to Saturdays and Sundays as a Down Time setting, but the query I wrote ignores this (besides the setting only changes one value in the DD020000 table and does not list dates). The useful thing about this calendar is that when you click on a day (such as a holiday), a table gets populated with all of the down days for that month - very useful.
Since the GP tables don't list ALL dates, I needed to build a Calendar table. I borrowed some SQL code that I found somewhere online and modified it to fit my needs:
Code:
--Create Calendar Table
CREATE TABLE Calendar
(DayNumber INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, CalendarDate datetime)
WHILE COALESCE(SCOPE_IDENTITY(), 0) <= 18263 --This number allows for a 51yr calendar
BEGIN
INSERT Calendar DEFAULT VALUES
END
Code:
--Insert Dates in to Calendar Table
Declare @dt varchar(20)
Set @dt='12311999' --Using this inserts dates from 1/1/2000 through 1/1/2050
Update Calendar
Set CalendarDate = convert(datetime,substring(@dt,5,4)+substring(@dt,1,2)+substring(@dt,3,2),112)+DayNumber
Now that I have a calendar table, I need to pull out only scheduled work days:
Code:
--Create Calendar_DaysOff View
Create View Calendar_DaysOff as
SELECT DayNumber, CalendarDate
FROM Calendar
WHERE (((DATEPART(dw, CalendarDate) + @@DATEFIRST) % 7) NOT IN (0, 1)) AND (CalendarDate NOT IN (SELECT DOWNDAYS_I FROM DD010000))
This view/statement looks at the Calendar table I built, removes weekends, and then looks at the DD01000 table to remove any additional dates that have been defined in the Shop Calendar in GP.</p> <p>I can now create an exception report that alerts me if a specific date falls on a scheduled Day Off. In my case, I am using the ReqShipDate on SOP orders, but you could just as easily use any date in the system.
