Notifications
Announcements
No record found.
Hi all -
Is there a way to view all unposted sales and GL batches across all the GP companies? Maybe a sql script?
Thanks!
*This post is locked for comments
Hi,
You can create a custom smartlist using smartlist builder and select multi company. Unposted sales transactions SOP10200 and GL10001 unposted work line details.
Try the following script that uses (i) Cursors (ii) Dynamics SQL and (iii) Temporary tables
CREATE TABLE [tag:TEMP] (BCHSOURC VARCHAR(15), BACHNUMB VARCHAR(15))
DECLARE @INTERID VARCHAR(15), @SQL VARCHAR(1000)
DECLARE COMPANY_CURSOR CURSOR FOR
SELECT RTRIM(INTERID) FROM SY01500
OPEN COMPANY_CURSOR
FETCH NEXT FROM COMPANY_CURSOR INTO @INTERID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'INSERT INTO [tag:TEMP] SELECT BCHSOURC, BACHNUMB FROM ' + @INTERID + '..SY00500 WHERE BCHSOURC IN (''Sales Entry'', ''GL_Normal'')'
EXEC(@SQL)
END
CLOSE COMPANY_CURSOR
DEALLOCATE COMPANY_CURSOR
SELECT * FROM [tag:TEMP]
DROP TABLE [tag:TEMP]
Regards
Andrew Dean
Envisage Software Solutions
run this in each company: SELECT DB_NAME()db, BCHSOURC,BACHNUMB FROM sy00500 WHERE BCHSOURC IN ('Sales Entry','GL_Normal') AND NUMOFTRX > 0 AND BCHSTTUS = 0
Under review
Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.
As AI tools become more common, we’re introducing a Responsible AI Use…
We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…
These are the community rock stars!
Stay up to date on forum activity by subscribing.
Community Member 2
mtabor 1
Victoria Yudin 1
