Data flow from Purchase invoice screen to Journal entry on via AP/AR Payment Posting using Automatic Journal

In our last blog, we see how to flow date from payment screen to Journal entry using auto journal. In this, we’ll see how we can flow data from Purchase invoice to journal entry while posting payment. The “Supplier doc. no.” field present on Management tab in Purchase invoice screen for the invoice entries linked in Payment screen is flowed to “Reference” field under Reference tab on Journal Entry screen. All this will be done with the use of Automatic Journals, which creates the Journal data based on Posting of a Payment.

First, to navigate on the following screens below are the steps:

Payment/Receipt screen: All > A/P-A/R accounting > Payment/Receipt entry
Purchase invoice screen: All > Purchasing > Invoices
Journal Entry: All > Financials > Journal Entry
Automatic Journals: All > Setup > Automatic Journals

For the purpose of making things a bit more clear visually, below are the screenshots fields locations on their respective screens:

New Stuff: Project snapshots in Project management

[Linked invoice no. on Payment] [Supplier doc no. field on Purchase invoice] [Reference field on Journal Entry]

Now once we’re familiar with the screens, we’ll first find out the Automatic journal code used for the posted Payment. Follow the below mentioned steps:

Step 1: Check on the respective payment entry whether it’s posted or not. You can do that by checking the POST button (on the right top side on version 12 Sage X3) being enabled or hidden. If it’s hidden the Payment is posted and it’s Journal entry is already created, if not then please post it first.

Step 2: Jump onto its respective Journal entry screen via Accounting document button as shown below.

[Accounting document on Payment Invoice]

Step 3: Once on the Journal Entry screen, scroll below to find the Source tab in that look for Source field which’ll show “Automatic generation” and next to it will be Source transaction field showing “PRINC”. This is the Automatic Journal code we’re looking for.

[Automatic Journals code on Journal Entry]

Now that we’ve noted the Automatic Journal code, next steps involve us flowing the data.

Step 4: Now in a new tab, open Automatic Journal and on the left-list search for PRINC. Scroll below to the formula section on screen and check below for the Journal Reference field as shown:

[Journal reference field on Automatic Journals]

Step 5: The Supplier doc. no. field data is stored in PINVOICE table as “Source Document” from where the data will be flowing to the Reference field on the Journal Entry screen.
Now we’ve to use that field data from Purchase invoice screen so below we need to check where in PINVOICE table it is storing:

[PINVOICE table in Table dictionary]

Step 6: Now for the Supplier doc. no. on Purchase invoice (linked on Payment screen in lines) screen, we’ll have to create a Specific script for a function. The Script name we used here is SPESUPDOCNO, below is the function:

[GETSUPPDOC function]

This function is used to retrieve the Supplier doc. no. data stored in PINVOICE table based on the Invoice number present on the Payment screen.

Step 7: Since we need to flow this data on the Journal Entry reference field, we’ll again go over to the Automatic Journals module with PRINC code. There on scrolling below we can find the Journal Reference field in the formulas section and write the instruction to call the function as shown:

[Calling function in Journal Reference field]

Here we’re passing the Payment no. as a parameter in the function which’ll retrieve the Supplier doc. no. data from the respective invoice no. linked on this Payment.
Note: Before making any change on the Automatic Journal formula field, keep a backup of the existing value present on the fields in case of mistake.

Step 8: Once the changes are done Save the Automatic Journal and perform a restart of the application, this is necessary to take effect.

Step 9: After restart, open the Payment screen and first cancel the previously posted entry using the Accounting cancellation option below the Accounting document as shown in Step 2. Give some time and then repost the Payment, and jump to the Journal Entry screen to see if the data is flowing properly.

[Supplier doc no. field on Purchase invoice] [Data flowed over to Reference field on Journal Entry from Purchase Invoice]

In this way, we can make use of the Automatic Journals module to flow data without making changes in Specific scripts.