"End of Month + Net Days" payment terms due date calculation

Folks across the pond use payment terms and due dates that are not traditionally close the ones we are familiar with on this side of the hemisphere. “End of Month + Net Days” (EOM+ND) is a typical case.

In EOM+ND payment terms, an invoice becomes due a number of net days after the last day of the month for the invoice date. For example, if an invoice date is July 16 and we are on a payment term of EOM plus 45 net days, the invoice will not be due until September 14 — or 45 days from July 31.

Of course, you cannot manage this type of payment term request in GP, not out-of-the-box anyways, which most of the time will require a customization to deal with the issue.

In this article, I will examine setting up two SQL Server triggers: one on the PM Transaction Open File (dbo.PM20000) and another on the RM Open File (dbo.RM20101) tables. The triggers will use the Net Days field in the Payment Terms Master (dbo.SY03300) table to calculate the net days after the end of month to assign the invoice due date.

So here are the triggers:

trigger pmEOMPlusNet

-- Created by Mariano Gomez, MVP-- No warranties expressed or impliedCREATE TRIGGER pmEOMPlusNet ON dbo.PM20000 AFTER INSERTASBEGIN TRANSACTION;

BEGIN TRY  UPDATE A SET A.DUEDATE =    DATEADD(dd, B.DUEDTDS, DATEADD(dd, -1, DATEADD(mm, DATEDIFF(mm, 0, I.DOCDATE) + 1, 0)))  FROM PM20000 A    INNER JOIN INSERTED I ON (A.VCHRNMBR = I.VCHRNMBR) AND (A.DOCTYPE = I.DOCTYPE)    LEFT OUTER JOIN SY03300 B ON (I.PYMTRMID = B.PYMTRMID)  WHERE (I.DOCTYPE = 1) AND (I.PYMTRMID LIKE 'EOMPLUSND%')END TRYBEGIN CATCH  SELECT ERROR_NUMBER() AS ErrorNumber    , ERROR_SEVERITY() AS ErrorSeverity    , ERROR_STATE() AS ErrorState    , ERROR_PROCEDURE() AS ErrorProcedure    , ERROR_LINE() AS ErrorLine    , ERROR_MESSAGE() AS ErrorMessage;

  IF @@TRANCOUNT > 0    ROLLBACK TRANSACTION;END CATCH;

IF @@TRANCOUNT > 0  COMMIT TRANSACTION;GO

trigger rmEOMPlusNet

-- Created by Mariano Gomez, MVP-- No warranties expressed or impliedCREATE TRIGGER rmEOMPlusNet ON dbo.RM20101 AFTER INSERTASBEGIN TRANSACTION;

BEGIN TRY  UPDATE A SET A.DUEDATE =    DATEADD(dd, B.DUEDTDS, DATEADD(dd, -1, DATEADD(mm, DATEDIFF(mm, 0, I.DOCDATE) + 1, 0)))  FROM RM20101 A    INNER JOIN INSERTED I ON (A.CUSTNMBR = I.CUSTNMBR) AND (A.DOCNUMBR = I.DOCNUMBR)      AND (A.RMDTYPAL = I.RMDTYPAL)    LEFT OUTER JOIN SY03300 B ON (I.PYMTRMID = B.PYMTRMID)  WHERE (I.RMDTYPAL= 1) AND (I.PYMTRMID LIKE 'EOMPLUSND%')END TRYBEGIN CATCH  SELECT ERROR_NUMBER() AS ErrorNumber    , ERROR_SEVERITY() AS ErrorSeverity    , ERROR_STATE() AS ErrorState    , ERROR_PROCEDURE() AS ErrorProcedure    , ERROR_LINE() AS ErrorLine    , ERROR_MESSAGE() AS ErrorMessage;

  IF @@TRANCOUNT > 0    ROLLBACK TRANSACTION;END CATCH;

IF @@TRANCOUNT > 0  COMMIT TRANSACTION;GO

Setting up the payment term in Dynamics GP

Open the Payment Terms Setup window (MSDGP > Tools > Setup > Company > Payment Terms) and configure the payment term as shown below:

NOTE: You can still setup discount and discount types for the payment term, but these will be calculated based on the document date. If you need these to apply based on EOM as well, you will need to change the above triggers to reflect the discount calculation based on EOM as well.

Finally, once setup, these payment terms can be used from SOP and POP. Just keep in mind that the due dates will not be calculated while the transactions are stored in a batch, but rather when posted.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

How to calculate the number of days a vendor check takes to clear?

I am always amazed at the things you can do with Microsoft Dynamics GP. Just recently, a user pointed out that the Days for Checks to Clear field on the Vendor Credit Summary window was always blank and that they needed this field to update automatically.

According to the help file, the field is defined as “View or change the average number of days that a check you issue to the vendor usually takes to clear your bank.“, in other words, you can manually update it if you need to track this data.

The user also pointed out that they performed daily bank reconciles and that the Cleared Date column in the CM Transaction (dbo.CM20200) table was reflective of the actual check cleared date in the bank and that they would like to use this field to update the Days for Checks to Clear field in the Vendor Credit Summary window. After all, the date value of the Cleared Date column only gets updated when the check is marked as cleared during a checkbook reconciliation process and does not necessarily reflect the actual date the check cleared the bank, but with daily bank reconciles this should not be an issue, because the would be one of the same.

So, if you perform daily bank reconciliations and would like to know the average days a vendor check takes to clear your bank, you could use the following T-SQL query to update this column value:

-- Created by Mariano Gomez, MVPWITH AVGCTE(VENDORID, AVGDAYS) AS (  SELECT A.CMLinkID, AVG(DATEDIFF(DD, A.TRXDATE, A.clearedate)) AS AVGDAYS  FROM CM20200 A  GROUP BY A.CMLinkID)UPDATE PM00201 SET DYCHTCLR = AVGDAYSFROM PM00201 INNER JOIN AVGCTE ON (PM00201.VENDORID = AVGCTE.VENDORID)

Now what to do with the script? You can schedule it as part of a SQL Server job that will run daily to update this value for you! This will allow you to have accurate average days for each vendor and in turn will allow you to make better purchasing decisions to preserve your hard earned cash.

Until next post!

MG.-
Mariano Gomez, MIS, MCP, MVP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

How are Payables transactions work error messages stored?

This question came up in the Dynamics GP Partner forum a few days ago, the specifics inquiried by the consultant were as follow:

“I was just looking at the PM10000 table in SQL Server Management Studio. I found a column named PMWRKMSG, with a data type of binary(4), which I cannot understand. In addition, I found entries like, 0×00019000, 0×00009000, 0×00000000 as column values. I know that they represent error messages, generated on the Batch Edit List and Posting Journals, but how can I check which message is being generated by just looking at this column? Do I need to convert this entry to some other integer and refer to some other table, which in turn would have a list of all the error messages?“

Answer

The ‘PM WORK Messages’ field (PM10000.PMWRKMSG column) is a Dexterity multi-select listbox control (so are the ‘PM WORK Messages 2′ and the ‘PM Distribution Messages’ fields) which contains static texts. The only way of storing such controls (with the listbox values checked or unchecked) on SQL Server is by using a binary data type. During the posting process, a number of failed validation rules will trigger any of the 32 static text values — added by the development team — to be checked in the multi-select listbox control.

The static texts (and possible errors you may receive during posting) for the ‘PM WORK Messages’ multi-select listbox control are:

No vendor record exists for this vendor ID.This vendor is inactive.This transaction already has been posted.Duplicate check numbers are not allowed.Duplicate invoice numbers are not allowed.No record exists for this credit card.No unique voucher numbers are available.This transaction has been posted and fully paid.Distributions for this transaction contain errors.Vendor summary records cannot be updated.This transaction is recurring; it cannot include a payment amount.This document should not be applied to other documents.The total applied amount is incorrect.Tax detail information is incorrect.Vendor ID is on holdWithholding Vendor ID is invalidApplied record is on holdBatch information is invalidGL posting date is invalidFiscal period for the posting date does not existFiscal period for the posting date is closedTaxes Incorrectly DistributedThis transaction contains multicurrency error(s).This transaction contains errors. It won't be posted.Transaction Analysis information for this transaction is incorrect or missing.Transaction contains intercompany distributions; mark as an IC transaction.Intercompany Processing is not registered;cannot post intercompany transactionsThe currency must be either the functional currency,The currency must be the same as the currency assigned to the checkbookthe Euro currency,an enabled denomination currency,or the same as the currency assigned to the checkbook

Assuming the values follow a binary storage pattern, 0×00009000, can be converted to its decimal equivalent of 36,864. In turn, this number can be represented as 2^15 + 2^12. If is the case, the error messages displayed on the batch edit list are: “Withholding Vendor ID is invalid” and “The total applied amount is incorrect”. In the case of 0×00019000, this is 2^16 + 2^15 + 2^12, this would result in the messages “Applied Record is on hold”, “Withholding Vendor ID is invalid” and “The total applied amount is incorrect”.

Of course, these errors can only be cleared via the interface, since resetting the value in SQL Server would have no direct effect on the validation rules applied by the posting process. A transaction will not move to the PM Transaction Open table (PM20000) if all the validation rules are not cleared, which in turn would render a value of 0×00000000 in the column.

The ‘PM WORK Messages 2′ multi-select listbox field stores the following static text values:

You cannot post to a tax date within a closed tax period.You cannot post to a tax period that has not been set up.The vendor's remit to address bank format is missing or inactive.The bank format for the vendor and the checkbook must be the same country.The bank format for the vendor and the checkbook must be different countries.The bank format assigned to the checkbook is missing.This check amount exceeds the maximum check amount for the checkbook.Remit-To address doesn't exist; please enter a different address.

The above errors are mostly used during the validation of tax computations and vendors setup as EFT vendors.

The ‘PM Distribution Messages’ multi-select listbox field stores the following static text values:

The accounts payable distribution(s) does not equal the actual amount.The purchases distribution(s) does not equal the actual amount.The discount available distribution(s) does not equal the actual amount.The trade discount distribution(s) does not equal the actual amount.The discount taken distribution(s) does not equal the actual amount.The misc distribution(s) does not equal the actual amount.The freight distribution(s) does not equal the actual amount.The tax distribution(s) does not equal the actual amount.The cash distribution(s) does not equal the actual amount.The write off distribution(s) does not equal the actual amount.The other distribution(s) does not equal the actual amount.The GST distribution(s) does not equal the actual amount.The withholding distribution(s) does not equal the actual amount.The debit distributions do not equal the credit distributions.No account has been specified for one or more distributions.The Realized Gain distribution(s) does not equal the actual amount.The Realized Loss distribution(s) does not equal the actual amount.The Round distribution(s) does not equal the actual amount.

Hope this helps in troubleshooting and understanding how Dynamics GP processes and manages errors when executing a Payables transaction posting validation.

Until next post!

MG.-
Mariano Gomez, MIS, MVP, MCP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

How to import the Vendor 1099 Box with Integration Manager

Business Situation

A few days aback I addressed an issue where the user needed to set the 1099 Box on the vendor import with Integration Manager. To my surprise, while there is a field to map the 1099 Type, there is no 1099 Box field in the Options collection in the Vendor import in IM. To make matters a bit more interesting, the user needed to have the 1099 Box filled based on some settings in the source system.

Solution

The 1099 Box is not accessible via Integration Manager, however we can create a small SQL Server stored procedure in the company database that will accept the vendor ID and the 1099 Box as parameters. The stored procedure in turn will update the vendor record with the value submitted.

dbo.uspUpdateVendor1099Box

-- Created by Mariano Gomez, MVP

IF OBJECT_ID ( N'dbo.uspUpdateVendor1099Box', N'P' ) IS NOT NULLDROP PROCEDURE uspUpdateVendor1099Box;GO


CREATE PROCEDURE dbo.uspUpdateVendor1099Box@VENDORID CHAR(21) = NULL,@TEN99BOXNUMBER SMALLINT = NULL,@RET_VAL INT OUTPUTAS


SET @RET_VAL = 0


IF (@VENDORID IS NULL) OR (@TEN99BOXNUMBER IS NULL)BEGIN  SELECT @RET_VAL = 0;  RETURNEND


BEGIN TRY  UPDATE PM00200 SET TEN99BOXNUMBER = @TEN99BOXNUMBER WHERE VENDORID =  @VENDORIDEND TRYBEGIN CATCH  SELECT @RET_VAL = ERROR_NUMBER()END CATCH;GO


GRANT EXECUTE ON dbo.uspUpdateVendor1099Box TO DYNGRPGO

Now, in the Vendor integration, we can open the After Document event script and add the following VBScript code:

After Document

' Created by Mariano Gomez, MVPConst adUseClient = 3Const adCmdStoredProc = 4Const adSmallInt = 2Const adInteger = 3Const adChar = 129Const adParamInput = 1Const adExecuteNoRecords = 128


Dim oCnDim oCmdDim pVendorID, pTen99BoxNumber, pRetVal


set oCn = CreateObject("ADODB.Connection")oCn.ConnectionString = "database=" & GPConnection.GPConnIntercompanyIDoCn.CursorLocation = adUseClientGPConnection.Open(oCn)


With oCmd  .ActiveConnection = oCn  .CommandType = adCmdStoredProc or adExecuteNoRecords  .CommandText = "dbo.uspUpdateVendor1099Box"


  'Set parameters  set pVendorID = .CreateParameter("VENDORID", adChar, adParamInput, 21, _     GetVariable("gVendorID"))  .Parameter.Append pVendorID


  set pTen99BoxNumber = .CreateParameter("TEN99BOXNUMBER", adSmallInt, _     adParamInput, 8, GetVariable("gBoxNumber"))  .Parameter.Append pTen99BoxNumber


  set pRetVal = .CreateParameter("RET_VAL", adInteger, adParamOutput)  .Parameter.Append pRetVal


  .ExecuteEnd With


If pRetVal.Value  0 Then' Error handling here, you can choose to write to IM's logEnd If

Since I am making use of global variables, gVendorID and gBoxNumber, we will need to capture both of these values in the vendor ID field script — we will no longer map the vendor ID since it’s necessary to use a field script.

Vendor ID field script

' Created by Mariano Gomez, MVPSetVariable "gVendorID", SourceFields("someSource.Vendor")SetVariable "gBoxNumber", SourceFields("someSource.Ten99Box")CurrentField.Value = SourceFields("someSource.Vendor")

This conclude our integration! Don’t get discourage if you don’t see a specific field mapping in IM. With little creativity and the right mindset you can always come around with a solution that is worth showcasing.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com

Mark Polino on Use Tax and Microsoft Dynamics GP

Use tax is one of those things that many companies using Microsoft Dynamics GP immediately resort to third-party solutions without really considering the systems out-of-the-box capabilities. The reasons may be various, among them, a lack of awareness of the GP’s capabilities to begin with.

I had the opportunity to actively participate in the review of a document on Use Tax written by fellow Dynamics GP MVP, Mark Polino. In his document, Mark extensively covers the methods for configuring, tracking, and accounting for use tax in Microsoft Dynamics GP.

Please make sure you download and read Mark’s document as he has dedicated a significant amount of time working through the techniques and methods with GP out of the box functionality.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com

Frank Hamelly on Entering Vendor Prepayments

Fellow MVP Frank Hamelly addresses a concrete day to day business issue: how to enter prepayments for vendors in GP. While it’s very simple to record prepayments for customers in the Sales series, it is not always as straight forward in the Purchasing series. Take a look at Frank’s article and let him know what you think.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

How to add trailing asterisks after check amount in words

This one came up in the Microsoft Dynamics GP newsgroup today and I thought it would make for a good blog article.

Business Situation

It is common business practice to suffix the check amount in words with asterisks to prevent words being extended to reflect larger check amounts than what the check was originally written for.

Challenges

The trailing asterisks need to be dynamic, not static, to accomodate for variable lengths in the amount in words. Asterisks should be able to change relative to the length of the amount in words.

Solution

Microsoft Dynamics GP check stubs reports generated in the Purchasing series use String A 255 and String B 255 directly from the PM_Payment_Work table. In order to add the asterisks at the end of the strings, two conditional calculated fields will need to be created. To add the asterisks we can use the Report Writer user-defined function RW_PadWithStars found in the financial series.

Steps

1) Open Report Writer.

v9. Go to Tools > Customize > Report Writer.
v10. Go to Microsoft Dynamics GP > Tools > Customize > Report Writer

Select Microsoft Dynamics GP and click on OK to continue.

2) Select the desired check stub form from the available reports. For our example we will use Check With Stub on Top and Bottom – Text. Click on Insert to continue.

3) Highlight the inserted stub and click the Open button.

4) Click on the Layout button to continue.

Adding the Calculated Fields

We will create two conditional calculated fields: (C) String A 255 and (C) String B 255. These fields will add the asterisks at the end of the amount in words based on whether PM_Payment_WORK.String B 255 is empty or not.

(C) String A 255

(C) String A 255Returns: StringCondition: PM_Payment_Work.String B 255  ""True Case: PM_Payment_Work.String A 255False Case: FUNCTION_SCRIPT(RW_PadWithStars PM_Payment_Work.String A 255 255)

Click OK to save the changes when finished

(C) String B 255

(C) String B 255Returns: StringCondition: PM_Payment_Work.String B 255  ""True Case: FUNCTION_SCRIPT(RW_PadWithStars PM_Payment_Work.String B 255 255)False Case: PM_Payment_Work.String B 255

Click OK to save the changes when finished.

Changing the Report Layout

1) Select String A 255 and press Delete on your keyboard to remove from the report layout.

2) Drag (C) String A 255 from the Calculated Fields list in the Toolbox window onto the report layout. Set in place of String A 255.

1) Select String B 255 and press Delete on your keyboard to remove from the report layout.

2) Drag (C) String B 255 from the Calculated Fields list in the Toolbox window onto the report layout. Set in place of String B 255.

Returning to GP and Setting Up Security

1) Exit the Report Layout window saving all changes.

2) Select Microsoft Dynamics GP from the File menu.

3) Grant security to the modified report

In Dynamics GP 9, open Advanced Security and located the Check With Stub on Top and Bottom – Text report in the Purchasing series. Click on the radio button for the modifed version.

In Dynamics GP 10, go to Microsoft Dynamics GP > Tools > Setup > System > Alternate/Modified Forms and Reports. Select the DEFAULTUSER role ID, select Microsoft Dynamics GP from the Products drop-down, then select Reports from the Type drop-down.

Click on the Purchasing folder and locate the Check With Stub on Top and Bottom – Text object. Click on the modified version.

4) Click Save to continue.

Testing it out!

In your test environment, create a new check batch and print the checks selecting the format you modified. Print to screen or printer and verify the outcome.

Related Articles

Developing for Dynamics GP – Cheque Amount in Words
Developing for Dynamics GP – Using the built-in Report Writer Functions

Downloads

Click here to download package file for this customization. Use the Customization Maintenance window to import the package file provided.

Warning: Importing this package file may destroy any previous modifications done to the Check With Stub on Top and Bottom – Text report.

Until next post!

MG.-
Mariano Gomez, MVP, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Implementing PayPal exports with EFT for Payables

Recently I had a chance to look into a post on the Microsoft Dynamics GP newsgroup where the consultant required the ability to upload hundreds of payments into PayPal as a business process using the EFT for Payables module.

At first, I thought the issue was more related to the interfacing abilities with PayPal, but soon discovered this was not API-related — PayPal also provides an API that can be called via web services.

It turns out, PayPal allows its customers, in its simplest form, to upload a mass payment tab-delimited text file containing the email of the payee or recipient, the amount to be paid, currency, a unique identifier for the recipient (which can easily be the vendor ID from GP), and a note or transaction description. For more information on PayPal’s formatted file click here.

Knowing this, the first limitation with EFT Payables is its ability to generate tab-delimited files. However, it does provide the ability to create fixed length files and XML files which can certainly be converted to tab-delimited with either Microsoft Excel. Nonetheless, I decided to explore what it would take to implement PayPal and here are the steps I came up with.

Setting Up the EFT Payables Options for PayPal

NOTE: the following steps are for Microsoft Dynamics GP v10. However, they can easily be adapted for previous releases.

1) Setup a checkbook ID for PayPal. Go to Cards > Financial > Checkbook and enter minimal information as shown below.

2) Click on the EFT Bank button to add EFT information for PayPal. Choose Other 1 from the Bank Country/Region drop-down. Also, set the ISO Currency Code field to one of the supported PayPal currency codes and preferably that of your functional currency. For a list of PayPal supported currencies click here.

NOTE: PayPal only supports payments made in one currency code per file.

Leave all other fields empty.

3) Click on the Payables Options button to open the Checkbook EFT Payables Options window to setup the file format and the default file output options for the EFT file. Set the EFT Payment Numbers radio group to Use EFT Numbers — this information is irrelevant to PayPal, but necessary to configure EFT; setup the Default Output Files as needed.

In the File Format radio group select Single Format and type PAYPAL in the field. Add the new format when prompted. This will open the EFT File Format Maintenance window. You will add 5 fields at the Detail line type level as shown in the following illustration.

NOTE: I used a flat file format, but XML can also be used as a format defining the tags and mapping the data in a similar manner.

For each field map the following data:

* Email Address maps to Address Electronic Funds Transfer Master.EFT Bank Account
* Amount maps to PM Paid Transaction History File.Document Amount
* Currency ID maps to Checkbook Transaction Electronic Funds Transfer.ISO Currency Code
* Vendor ID maps to PM Paid Transaction History File.Vendor ID
* Description maps to PM Paid Transaction History File.Transaction Description

4) Click on the Save button to continue. Click here to download a copy of the PAYPAL file format that can be used to be imported into EFT File Format Maintenance window.

5) On the Checkbook Payables Options window, click OK to save and exit the window.

6) On the Checkbook EFT Bank Maintenance window, click OK to save and exit the window.

7) On the Checkbook Maintenance window, click on Save to save the changes.

Setting Up the Vendor Card

1) Open the Vendor Maintenance window. Go to Cards > Purchasing > Vendor and select a vendor ID.

2) Click on the Address button, then choose an address ID (typically the one used for remittances).

3) Click on the EFT Bank button to open the Vendor EFT Bank Maintenance window. Choose Other 1 from the Bank Country/Region drop-down list. Since PayPal uses emails instead of bank accounts, the Bank Account Number field will be replaced with the Vendor Email address.

NOTE: Internet Address information is not exposed to EFT; hence the email address associated to the Vendor Address cannot be used.

This should complete the setup! Save all information and close all windows.

Testing it All!

Use the EFT Prenotes window to generate the prenotes. This will pick up all transactions created in the past and will allow you to get a glimpse at the new PayPal file. Use Microsoft Excel to convert the generated payments file to a tab-delimited file, required by PayPal.

Until next post!

MG.-
Mariano Gomez, MIS, MVP, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

New Article on MSDynamicsWorld – "When Cash Is King: 8 Strategies for Using Microsoft Dynamics GP to Wring Extra Money from Regular Transactions"

Hi folks! After a long writing hiatus from MSDynamicsWorld.com, my new article has finally made it to the headlines. In this edition, I discuss 8 comprehensive strategies to improve your purchasing to disbursement business process and match the Microsoft Dynamics GP application components required to effectively support the proposed strategies. As always, please let me know your thoughts and/or let me know what you would like to see posted on this site.

Until next post!

MG.-
Mariano Gomez, MIS, MVP, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com

Payables Transactions not in GL

Picture this: the auditors are in, they asking for myriads of reports, and precisely one of the things they ask is something you cannot easily achieved from the slur of reports and SmartLists available from within Microsoft Dynamics GP. To make matters worst, the “go to” guy for queries and special requests is out of the office sick with the flu. Well worry no more! If you are asked to show all the payables transactions that were never recorded or posted to GL you can run the following query from SQL Server Management Studio (or Query Analyzer if you still happen to be on SQL Server 2000):

SELECT   a.vendorid,   a.docnumbr,   a.docdate,   a.cntrlnum,   gl.jrnentry,   gl.trxdate,   gl.actindx,   c.actnumst,   d.actdescr,   gl.refrence,   gl.ortrxtyp,   gl.orctrnum,   gl.ormstrid,   gl.ormstrnm,   gl.ordocnumFROM pm00400 a left outer join  ( SELECT jrnentry, trxdate, refrence, ortrxtyp, actindx, orctrnum, ormstrid,      ormstrnm, ordocnum    FROM dbo.GL20000    WHERE series = 4

    UNION ALL

    SELECT jrnentry, trxdate, refrence, ortrxtyp, actindx, orctrnum, ormstrid,      ormstrnm, ordocnum    FROM GL30000    WHERE series = 4  ) gl ON(a.vendorid = gl.ormstrid) and (a.cntrlnum = gl.orctrnum) and (a.docnumbr = gl.ordocnum)LEFT OUTER JOIN GL00105 c ON (gl.actindx = c.actindx)LEFT OUTER JOIN GL00100 d ON (gl.actindx = d.actindx)

Knowing what transactions have not been posted or recorded in GL is particularly useful when attempting to reconcile the AP subsidiary module to General Ledger. It will also allow you to identify those records loaded as begining balances from within AP.

With little or no effort, this query can be added to SmartList Builder (SLB), by creating a SQL Server view with the inner query, and exposing the view to SLB. Don’t forget to run the GRANT.SQL utility script to assign permissions to the DYNGRP.

Until next post!

MG.-
Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC.
http://www.maximumglobalbusiness.com/