By May 8, 2012 Read More →

SQL view for unpaid SOP Transaction with No. of overdue days

Recently one of my customer asked me to create a script to shows all open (unpaid) sales transaction with the total number of days overdue based on the current system date and customer’s payment terms. Customer also wanted to see a column that shows the estimated payment date based on the actual payment terms and the posting/actual ship date.

The view below links SOP30200 (SOP _HDR_HIST – Sales Transaction  History) and RM20101 (RM_OPEN – RM Open File) and SY03300 (SY_Payment_Terms_MSTR – Payment Terms Master). The overdue days is the difference between the current date and the GLPOSTDT (GL Posting Date) or ACTLSHIP (Actual Ship Date). The “Estimated Payment Date” is calculated by adding the DUEDTDS (Due Date in Days) from SY03300 table with the GL Posting date or actual ship date.

[hana-code-insert name=’UnpaidSOPTransaction1′ /]

About the Author:

Software developer acknowledged for talents conceptualizing and directing the development and continuous refinement of advanced technology solutions to meet clients’ needs. Sophisticated programming knowledge combines with well-honed problem solving strengths to meet the challenges of business economics, client needs and technical innovation. Microsoft Certified Professional with over 10+ years of experience in client/Server and web technologies and Microsoft Dynamics GP.