Purchase accrual month end reconciliation

During month end, usually there is a need to reconcile between the total purchase accrual for the month with the Inventory module. Purchase accrual GL account (Packing slip offset) is the GL account that records the total value for all the goods received but not yet invoiced (GRNI) for the month.

To reconcile this GL account with Inventory module, you can use the standard Item transactions report (Item management> Reports> Transactions> Item transactions). But usually the common assumption for retrieving the GRNI value is to filter the report for all items with the Receipt status as Received. This is incorrect if you back date print the report for prior month, where the stock that has been received (but not yet invoiced) in prior month has been invoiced now. Eg. Current month is Feb, you want to print all GRNI for Jan, and some stock that are received in Jan has been invoiced now in Feb. This is because Receive status for the Invent transactions have been changed to Invoice status now in Feb, and there is no way to retrieve back the original status on Jan.

There are two possible ways to solve this issue. Firstly, completely avoid this situation to happen by make it a business practice to have all the stock that received this month, are invoiced on the same month before the month end closing. This is the ideal situation.

In the not so ideal situation, there is possible to retrieve the correct GRNI value by tweaking the filtering criteria on the Item transaction report. We need to create the filter criteria so that the Item transaction report shows all purchase received transactions for current period that is either NOT yet invoiced, OR received in current month but invoiced after the current month. The report needs filter criteria as below:-

• Physical date – Start and end date of current period (Eg. 01/01/2009..31/01/2009)
• Financial date – Empty (indicates not yet invoiced) OR invoiced after the current period. (Eg. ‘ ‘, >31/01/2009)

Physical date indicates the Receive date for the item transaction, where Financial date is the Invoice date for the transaction. In this case, the Physical cost amount in the report should be tally with the Packing slip offset GL account.

Issues on Service item

Usually for service item, you do not require to set up the Packing slip and Packing slip offset posting account in the Inventory profile. But there is one exception case in Dynamics AX 4.0.

If you have set up the Inventory Cost price in the Item master for a service item, and Inventory module group is having Post physical inventory activated, and Post packing slip in ledger is selected in Purchase parameters, Dynamics AX will give you a posting error if you do not set up these two accounts in the Inventory profile when you are trying to post a Purchase invoice.

Once you have set up these 2 accounts, the purchase invoice posting will have no more error. But in fact, Dynamics AX will not post any entries in these 2 accounts during the posting. It is just a logical bug in posting checking classes. So, it is good to make sure Inventory cost price is always zero for service item.

Another weird cases for service item is in fixed asset acquisition using purchase order. When you purchase a service item and linking it to a Fixed asset number in purchase order line, the invoice posting entry will be
Dr Purchase - receipt, Cr Vendor balance.
But when you try to do the return on the purchase of fixed asset using the same method with negative quantity, Dynamics AX will require you to set up a Stock, receipt account in the inventory posting profile. The posting entries will be
Dr Vendor balance, Cr Purchase, receipt;
Dr Stock - receipt, Cr Fixed asset
Thus, it is clearly that when you do purchase return on fixed asset using service item, Dynamics AX 4.0 incorrectly creates an additional return posting entry which is actually for stock item.

Import Opening Stock Balance into Dynamics AX

When we import opening stock balance into inventory journal using the standard import/export functionality, the most common problem is dealing with Inventory Dimension id (InventDimId). We might know the warehouse and batch, but we do not know what is the InventDimId that represents the combination of these two inventory dimension.

One way to do this is to lookup the InventDimId value manually before performing the import. This is only possible if very few inventory dimensions are used.

An alternative is using the Custom import functionality to import stock balance CSV file into Tag counting journal.

There is a conversion functionality in the Custom import, where you can write a little X++ code to find the InventDimId using the inventory dimensions.

Here is an example of finding the InventDimId using Site and Warehouse dimension.

You must select Run conversion checkbox to activate the conversion. You may also use the compile icon (on the right hand) to validate the X++ code.

The purpose to use Tag counting journal instead of Movement/Counting journal is we let the system to create the Lot ID when we post the Tag counting journal into Counting journal.

DynamicHeight problem in X++ report

When you developing X++ report, very frequent you will need to print string fields with large string size (eg. Name, Description, DeliveryAddress) or memo type of string. In this situation, you will have difficulty to determine the right Height property to use in the report. The default Auto option in the ReportStringControl follows the DisplayHeight property of the inherited Extented Data Type (EDT), which could be not long enough to display the full string. You can't fix the Height value also, unless the content of the ReportStringControl is known to have some fixed format during the development.

One quick solution for this problem is using DynamicHeight. Set the DynamicHeight property to Yes will let the system to determine the right Height value at runtime. But this is not a perfect solution. Usually DynamicHeight will have some weird problems on the report formating if you have a complex report design.

An alternative to DynamicHeight is to calculate the Height of the ReportStringControl at your own and set the Height based on the string length of the content at run time. Use the heightOfWordWrappedString100mm() method to determine the height of words and set the Height based on this value. You could do this in the executeSection() of the SectionGroup, before the call to super().

Here is the example to do that:

public void executeSection()
{    
    ReportStringControl ctrl = this.control(fieldnum(PurchLine_1,DeliveryAddress));
    ;
    ctrl.height100mm(ctrl.heightOfWordWrappedString100mm(PurchLine_1.DeliveryAddress));
    super();
}

Customer payment journal - Oversettlement

In Axapta 3.0, when you post a customer payment journal, sometimes it will just give you a dumb error message "Update has been canceled." without explaining what is going wrong that prevent you from posting the journal. One of the reasons that causing this error is due to you are trying to perform an oversettlement for the customer account, in other words, you are trying to making your company owing money to your customer and have a negative customer balance.

To simulate this problem, say there is an open credit note (CN) transaction -$100 and invoice transaction $25. Due to whatever reason, the customer is going to pay you $20, and you want to offset the balance from the CN. So, in the customer payment journal, you click Funtions\Settlement button and mark these 2 transactions. Then, in the journal lines, you manually overwrite the Credit amount to $20 since you are going to receive only $20 from the customer. You validate the journal and system is telling you Journal is OK. But when you post the journal, the above-mentioned error message is prompt out.

Without debugging the X++ code, you might need some time to find out the cause. The error is actually thrown out from \Classes\CustVendSettle\checkCreditRemainAmountCur() method.

First, let's look at some of the details for CustTrans table's AmountCur and SettleAmountCur fields.
When CustTrans.AmountCur > 0, it is a Invoice transaction.
When CustTrans.AmountCur < 0, it is a Payment transaction.

When posting a customer payment:-
CustTrans.AmountCur is negative value, because you are crediting the customer balance. CustTrans.SettleAmountCur must be same sign as CustTrans.AmountCur, so in this case, it could only be either zero, same negative value as AmountCur or differrent negative value.
1) Zero value
No invoice is marked for settlement against the payment transaction.

2) Same negative value as AmountCur
Payment transation is fully settled with marked invoice transaction(s) that have total invoice amount same or larger than payment amount.
Eg1. payment = 100, marked invoice = 100, so AmountCur = -100, SettleAmountCur = -100 Eg2. payment = 100, marked invoice = 120, so AmountCur = -100, SettleAmountCur = -100 (Thus, the payment transaction is fully settled, but there is 20 balance for the invoice transaction)

3) Different negative value, but smaller value than AmountCur.
This is a partial settlement for the payment transaction.
Eg. payment = 100, marked invoice = 20, so AmountCur = -100, SettleAmountCur = -20

When CustTrans.SettleAmountCur is positive, it means it is in the oversettlement situation. In the above senario, the SettleAmountCur is $75 (This is derived from the sum amount of marked transactions CN-$100 and Invoice $25, but the sign would be inverted because it is a settlement. In a normal situation eg. mark against a invoice transaction $10, the CustTrans.SettleAmountCur will be sum of the invoice amount with inverted sign, -$10).

In to the above senario, due to the different sign of CustTrans.AmountCur (-$20) and CustTrans.SettleAmountCur (+$75), the system throw an error "Update has been canceled."

Solution?
First, you should only mark the invoice transaction $25 in the payment journal. Then, use the Open transaction editing function to settle the balance $5 in the invoice transaction against the CN transaction.

In Dynamics AX 4.0 onwards, it has been improved as it is giving a more meaningful error message "Update has been canceled because of a potential oversettlement. The Balance on a customer- or vendor transaction must not exceed the amount.".

Customer overpayment

When you perform an AR Open transaction editing, you could fully settle a payment transaction that has larger amount than an invoice transaction.

What happen to the overpayment amount?
This is depends on 2 parameters specified in Accounts receivable\Parameters\Settlement tab, Maximum overpayment or underpayment and Maximum penny difference.

If the overpayment amount is larger than the amount specified in Maximum overpayment or underpayment field, no settlement will happen to overpayment amount and it will remain as balance amount for the payment transaction to be settled in future.
If the opposite case, system will automatic fully settle both payment and invoice transaction.

What ledger account is posted to for the auto settlement depends on the second parameter, Maximum penny difference.
If the overpayment amount is equal or less than the amount specified in Maximum penny difference field, the difference will be posted to the penny difference ledger account that is specified in the General ledger\Setup\Posting\System accounts form. If the overpayment amount is greater than the amount specified in Maximum penny difference field, than it will be posted to the Customer cash discount ledger account that is specified in the System accounts form.

Below are the voucher transactions for automatic fully settlement for overpayment:-
Posting type: Penny difference in default currency
Dr AR 5
Cr Penny difference/customer cash discount 5

The same logic should be used for underpayment, though I have not yet tested it out.