SALES Documents housekeeping routines | Collection of Useful Microsoft Dynamics GP Sql Commands, Tips and Tricks

	GP-DYNAMICS
	Custom Programming

Collection of Useful Microsoft Dynamics GP Sql Scripts, Tips and Tricks

SALES Documents housekeeping routines

Long Description:
Here are some more housekeeping sql routines that you can use to clean up your sales document tables in dynamics database of unwanted records. These routines contain basic SELECT statements first to aid you in seeing first if there are actual rows to delete.

IMPORTANT:
Commands with UPDATE, DELETE or DROP can ruin your data! Please use these queries with caution. We highly recommend that you backup your database before trying out these commands and to load them up first on a test server. Use at your own risk.

Associated SQL Command:
-- ///////////////////////////////// -- SOME MORE HOUSEKEEPING ROUTINES -- ///////////////////////////////// -- ------------------------------------ -- Display first what you are about to delete -- ------------------------------------ -- Display History Sales Line Items that do not have a corresponding Master record, or customer record select * from sop30300 where sopnumbe not in (select sopnumbe from sop30200 inner join rm00101 on rm00101.custnmbr = sop30200.custnmbr) -- Display History Master Sales Document Records that do not have a corresponding customer record select sopnumbe from sop30200 where custnmbr not in (select custnmbr from rm00101) -- ------------------------------------ -- Actual delete commands -- ------------------------------------ -- Delete History Sales Line Items that do not have a corresponding Master record, or customer record delete from sop30300 where sopnumbe not in (select sopnumbe from sop30200 inner join rm00101 on rm00101.custnmbr = sop30200.custnmbr) -- Delete History Master Sales Document Records that do not have a corresponding customer record delete from sop30200 where custnmbr not in (select custnmbr from rm00101) -- Note: To work on WORKORDER DOCUMENTS just substitute SOP30200 with SOP10100, and SOP30300 with SOP10200
Viewed: 10692 times

Submitted by: L. Gates

About L. Gates

View complete list

previous script

next script

Did you find this script useful?
Please reciprocate by submitting your own script
or any proven advice regarding Dynamics GP use. Click here.

Send the script above to a friend

Send to EMAIL: *
Short MESSAGE: *
Sender NAME: *
Sender EMAIL:
Validation code: *	Enter 794

Trademarks: Microsoft™, Microsoft Dynamics™ and/or Microsoft logos, products, and services referenced in this site are either trademarks or registered trademarks of Microsoft Corporation.

Disclaimer: This product is not officially endorsed by Microsoft. Furthermore, this website does not imply representation of Dynamics™ or relationship with Microsoft in any form.