Dynamics Community Forum Thread Details

Here you go: A few things though

We have our own custom tables to figure out which company is what to run cursors on, and we also have a table with our internal email addresses for the email cursor below. If you don't need to cursor through a table you can set the email address and forget about it...

Also, this uses SQL send email features that might be disabled by default...so you would need it turned on. And you should have a script that clears out the email data in the master db because it will grow to be huge.

DECLARE @EmailSubject NVARCHAR(MAX);
    SET @EmailSubject = 'Weekly Great Plains Period/Year Open Report v01-1';

    DECLARE @xml NVARCHAR(MAX)
       ,@body NVARCHAR(MAX)
       ,@dbname sysname
       ,@SQL NVARCHAR(MAX)
       ,@EmailListAdd NVARCHAR(MAX)
       ,@UserEmail NVARCHAR(50);

       SET @EmailListAdd = ''

    DECLARE rs_cursor CURSOR
    FOR
        SELECT EmailAddress


    OPEN rs_cursor;
    FETCH NEXT FROM rs_cursor INTO @UserEmail;

    WHILE @@FETCH_STATUS = 0
        BEGIN

            SET @EmailListAdd = @EmailListAdd + @UserEmail + ';';


            FETCH NEXT FROM rs_cursor INTO @UserEmail;
        END;

    CLOSE rs_cursor;
    DEALLOCATE rs_cursor;

SELECT @EmailListAdd

    IF OBJECT_ID('tempdb..#TempPeriods','u') IS NOT NULL
        DROP TABLE #TempPeriods;

    CREATE TABLE #TempPeriods
        (
         DatabaseName NVARCHAR(MAX)
        ,GPYear SMALLINT
        ,PeriodName VARCHAR(10)
        ,GPStatus VARCHAR(12)
        ,UpdatedDate VARCHAR(12)
        );

    SET @SQL = 'INSERT INTO #TempPeriods
           (
           DatabaseName
           ,GPYear
            ,PeriodName
            ,GPStatus
            ,UpdatedDate
       )';
    DECLARE rs_cursor CURSOR
    FOR
        SELECT INTERID FROM DYNAMICS..SY01500
    OPEN rs_cursor;
    FETCH NEXT FROM rs_cursor INTO @dbname;

    WHILE @@FETCH_STATUS = 0
        BEGIN
            SELECT @SQL = @SQL + '
       SELECT ''' + @dbname + ''' AS DataBaseName
               ,YEAR1 AS GPYear
               ,PERNAME AS PeriodName
               ,CASE CLOSED
                  WHEN 1 THEN ''CLOSED''
                  ELSE ''OPEN''
               END AS GPStatus
               ,CAST(DEX_ROW_TS AS VARCHAR(12)) AS UpdatedDate
       FROM    ' + @dbname + '.dbo.SY40100
       WHERE   SERIES = 2
               AND ODESCTN = ''General Entry''
               AND PERIODID <> 0
               AND CLOSED = 0
       --ORDER BY YEAR1, PERIODID, Series
       UNION ALL
       SELECT ''' + @dbname + ''' AS DataBaseName
               ,YEAR1 AS GPYear
               ,''YearEnd'' AS PeriodName
               ,CASE HISTORYR
                  WHEN 1 THEN ''CLOSED''
                  ELSE ''OPEN''
               END AS GPStatus
               ,CAST(DEX_ROW_TS AS VARCHAR(12)) AS UpdatedDate
       FROM    ' + @dbname + '.dbo.SY40101
       WHERE   HISTORYR = 0
       UNION ALL
       SELECT ''Note:YouNeed'' DataBaseName
               ,0 AS GPYear
               ,''AtLeastOne'' AS PeriodName
               ,''PeriodOpen'' AS GPStatus
               ,''PerOpenYear'' AS UpdatedDate
--       ORDER BY YEAR1';

            FETCH NEXT FROM rs_cursor INTO @dbname;

            IF @@FETCH_STATUS = 0
                SELECT @SQL = @SQL + '
               UNION ALL ';

        END;

    CLOSE rs_cursor;
    DEALLOCATE rs_cursor;
    EXEC sp_executesql @SQL;

    SELECT @SQL;       --used for diagnosis

    SELECT *
    FROM    #TempPeriods;

    SET @xml = CAST((SELECT DatabaseName AS 'td'
                           ,''
                           ,GPYear AS 'td'
                           ,''
                           ,PeriodName AS 'td'
                           ,''
                           ,GPStatus AS 'td'
                           ,''
                           ,UpdatedDate AS 'td'
                     FROM   #TempPeriods
                    FOR
                     XML PATH('tr')
                        ,ELEMENTS
                    ) AS NVARCHAR(MAX));

    SET @body = '<html><body><h1>Weekly Period and Year Status Report</h1>
<H3>The following Years and Periods are Open in Great Plains:</H3>
<p>
Verify & Inspect periodically for accuracy and reminder to close.<br>
Controllers: Please close periods for the companies you are responsible for to prevent erroneous entries. (Jo, Jose, Dominic)<p>

STIC - System Test Integrity Control <br>
To Close Months: Microsoft Dynamics GG > Tools > SetUp > Company > Fiscal Periods <br>
<br>
<table border = 1>
<tr>
<th>DatabaseName</th><th>GPYear</th><th>PeriodName</th><th>GPStatus</th><th>UpdatedDate</th>';

    SET @body = @body + @xml + '</table></body></html>';

    EXEC msdb.dbo.sp_send_dbmail @profile_name = 'STIC',@body = @body,
        @body_format = 'HTML',@recipients = @EmailListAdd,
        @subject = @EmailSubject;

    PRINT 'Email Sent';

    IF OBJECT_ID('tempdb..#TempPeriods','u') IS NOT NULL
        DROP TABLE #TempPeriods;

Alert or Reminder when a fiscal period is opened or closed

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Top 10 leaders for November!

Tips for Writing Effective Suggested Answers

Leaderboard

Featured topics

Product updates