Account specific Cost Center access.

Hi Everyone,

During financial system implementation I have faced this challenge.
How to choose specific valid account / subaccount (cost center) combination?

For example, all balance sheet accounts need to get tied to a single sub account, while Profit and Loss ones have to go to multiple, depending on the account nature and sub location.

Second issue - how to guarantee global validation rules for all these combinations. To say in Order Management or Purchasing when we choose a product, system should validate accounts and subaccounts based on predefined rules...

In most of the systems we should specifically indicate valid combinations, then make a template, to be used with each new account or sub added to the system. Looks tedious.
This approach seems to be not very effective, especially is you have hundreds of cost centers.

In Acumatica we have restriction groups, that can help to define validation rules.
I will be using Acumatica demo system to show how to configure it.
Let say we want all B/S account to get only tied to subaccount 00-00-00-00-000, while PnL account to all other subaccounts, excluding 00-00-00-00-000.

1. Open GL->Setup Section->Account Access screen. Lets Create a group Balance Sheet. In our case it should be group of type A. And because we do not need "by user" restrictions lets leave first tab intact and move straight to the second tab "Accounts":

My BS accounts are starting from 1,2 or 3. Lets make a filter to display only BS accounts on the grid for easier and faster selection :)

2. Lets select all BS accounts in the Accounts Tab. And then Save the group.

3. Lets get to Subaccount Segments Tab. And select 00-00-00-00-000 only.

Then Second segment, all the same 00 until Fifth:

4. Balance Sheet group is ready, now to make the restriction balanced we should create a group PnL to indicate possible subaccount combinations for PnL accounts. Lets make all except 00 available for them. Procedure is similar. Create a group, leave Users intact, mark all PnL Accounts, mark All Sub segments except 00, Save. Here are screen shoots:

Filter:

Accounts:

Subaccounts:

All till Fifth:

We are done, lets check on GL Journal Entry screen whether our look-ups and validation works. I will choose BS account and F3 on subaccount to see a look-up for values allowed:

Seems OK, only 00 is shown. Lets try to cheat the system and enter other than 00 subaccount then go to the next line and see if it validates the invalid entry:

System prompts for invalid Location Cost Centre (first segment) for my Subaccount.

Now lets try on PnL Account, should allow all except 00:

Seems OK, no 00 in the look-up list. Lets then enter 00 and try on the next line:

For the second line it was OK, because it does not contain 00 segments, but attempt to manually enter 00 sub in the third line resulted in Error message for all involved Cost Centres.

Have a nice day,
Sergey.

Who is On-Line? Active Users.

Dear Operation Managers and IT Administrators!

Knowing your hard work, I dedicated this post for you.

So how can we know, how many users are still on-line, sitting late in the office or working from home, preventing us from doing maintenance? Currently there is no way to see it at one glance. Which pushed me to create an inquiry.

First challenge we have here, a table where all this info stored is secured, meaning we cannot easily retrieve these data into a Generic Inquiry. So we have to create a database view first. Please refer to my earlier post on Adding View to Report on how exactly a view can be published into Acumatica data access class. I will just put below the text of the select statement:
CREATE view ActiveUsers as
select FirstName,LastName,CompanyID,
CAST(DAY(LastLoginDate) as varchar(2))+'/'+CAST(MONTH(LastLoginDate) as varchar(2))+'/'+CAST(YEAR(LastLoginDate) as varchar(4)) as LoginDate ,
CAST(CAST(LastLoginDate AS time(0)) as varchar(8)) as LoginTime 
from users where IsOnLine = 1 

Graph object should have the following code:

<Graph ClassName="ActiveUsers" Source="#CDATA" IsNew="True" FileType="NewDac"><CDATA name="Source"><![CDATA[using System;
using PX.Data;
namespace PX.Objects.SM
{
[Serializable]
public class ActiveUsers: Cst_ActiveUsers, IBqlTable
{    #region FirstName
    [PXDBString(255, IsUnicode = true, InputMask = "")]
    [PXUIField(DisplayName = "First Name")]
    public string FirstName { get; set; }
    public class firstName : IBqlField{}
    #endregion
    #region LastName
    [PXDBString(255, IsUnicode = true, InputMask = "")]
    [PXUIField(DisplayName = "Last Name")]
    public string LastName { get; set; }
    public class lastName : IBqlField{}
    #endregion
    #region LoginDate
    [PXDBString(10, InputMask = "")]
    [PXUIField(DisplayName = "Login Date")]
    public string LoginDate { get; set; }
    public class loginDate : IBqlField{}
    #endregion
    #region LoginTime
    [PXDBString(8, InputMask = "")]
    [PXUIField(DisplayName = "Login Time")]
    public string LoginTime { get; set; }
    public class loginTime : IBqlField{}
    #endregion
      }}]]></CDATA></Graph>


With this view we can retrieve User's First and Last names, Date and Time of when they logged into the system. Once view is published, it is time to create a Generic Inquiry, using this DAC.

Let's call Inquiry - Active Users.
Under parameters I just specified - Filter, and data area - Users.
Its all non-mandatory. You can give any name based on your Inquiry specific.

Table name we can choose from a lookup screen, which should show us now under SM namespace object ActiveUsers. Alias is optional.

This GI will be dead simple. No relations, no sorting or parameters. Lets move straight to the last tab, Results Grid and select columns we need.

Nice feature we have here, Width. Please specify the width of the column, based on estimated Name length.
Save. And press button Add to Site Map. It will navigate us to the Site Map tree, where I added my GI to System Manager, Audit section.

Now lets try it out.

Looks good, seems I am the only one at this time...

Please note, Acumatica keeps track of time and date in GMT time zone, so if you need to calculate your local time, add/subtract accordingly.

Cheers,
Sergey.

Sending Report over E-mail. Scheduling.

Hi Everyone,

Today's topic is automation and integration.
How to send a report over the e-mail?
First let's choose a report we want to send, lets take Aged AR:

Then we can use a Send button on top to manually send it over e-mail. It works fine for one-time send.

Then enter e-mail address into mailer screen:

Resulting e-mail will get delivered into recipient mail box.

What if we want to schedule printing of this report every morning for a list or recipients, like our AR department. Then we have to go back to report and:

1. Create a template with specifying address to send:

 2. Choose this template from the list and click "Schedule template":

3. Set up a schedule for sending at particular time/date/event:

Then we setup hours and conditions if any, in my case I setup daily at specific time.
Then at that time we will get an e-mail with report:

All the best,
Sergey.

Sending Notifications.

Hi All,

One of the common questions, when implementing automation for operations is - how can we send an email notification to a person that such and such is happened?
It could be for example Purchase Order is created and we want short e-mail to get send to procurement chief.

Using Notifications in Acumatica is a natural answer to all that.

Task: I want to send a notification to Steve Church once the purchase order status is changed to Open (meaning PO is approved/printed).

Step 1. We should set appropriate e-mail to the user Steve Church

Step 2. Now need to create a notification in system manager.

Please note, Screen ID should be Purchase Order, since we want changing of the status in THIS screen to trigger a notification event. In the Message body I want to be mentioned that system should include actual PO number that was created, so I use standard Template Notification syntax of ((fieldname)). I also want actual PO Form to get attached to my e-mail, so user can review it, I just added Data Source: Report with format PDF.

On the next tabs I mentioned Event Conditions, Addresses, Parameters, which are self explanatory:

3. Now we have to decide if we send these notifications using a schedule or manually calling a process. I wanted to use a manual process, but schedule can be added that will trigger it every say 1 minute.

4. Now we can finally go to the Purchase Order and "open" one, to see what will happen:

After it was saved lets take a look at Send Notification screen. My note is pending (since I was lazy to create a schedule) so I run the process manually:

5. Enjoy reading e-mail

Have Fun,
Sergey.

Sales Order Import, including Multiple Orders.

Hi Everyone.

What to do when we need quickly and effectively import numerous data into ERP. We can use Integration Services, embedded into our product. Lets go through 5 minute process of importing.
Where we are going to import. Sales order screen in Acumatica ERP.

From where we are going to import our orders? From Excel file.

What need to be done on Acumatica to achieve it?
First we should create Data Provider and attach our file to it. In theory we can use different means of data provision. From Salesforce web site, from comma separated file, from SQL directly etc. So we should tell the system where are data coming from.

Then we should attach our file to it and indicate in the File Name value column internal or external link to it

I used internal link to the file sitting inside Acumatica DB:

Last step for provider is to fill up the schema which is done by pressing these two buttons subsequently on the Schema tab:

Next step will be to map columns inside the file and our screen. This is done inside Import Scenario screen.
I am going to show resulting screen. Basically on the left are fields from the screen and on the right are values from our import file:

Now we can run the import:

And here is the resulting Sales Order:

Well, this is good to import one single order, but what if we have multiple orders in a file?
And what if it includes the same customer in couple of orders?

Solution is very siple, lets amend column [Number] in our source file or simply amend data, prepared for the import to split 5 lines into 3 different Sales Orders.

Here is what I changed in a data representation inside Acumatica:

Please note that I am using column Number as sequential number for my orders, not as actual Sales Order number, that will be issued by the system automatically.

Based on what was amended, it should end up with 3 sales orders. SO #1 and SO #3 for customer ABARTENDE and SO #2 for customer ABCSTUDIO.

Lets run the import for it and here is the result:

Works like a charm.

Have a great day,
Sergey.