Use of Import Account Statement in General Journal

Today I would like to share one special functionality which ax gives us to upload Journal lines available in excel file or some other file formats. (The document may be XML or non XML.). This function we find in General Ledger à Journalsà General Journal à Linesà Functionà import transaction from account statement.

Once we select it , all the transactions from that imported document will come to Journal Lines. and then we can post it. This is one kind of facility through this different kind of transaction passed (Viz. Credit Cards, Bank Statement).

But for this we have to make proper set up. In short we have to set up as follows in Basic –> Set Up –>Import.

1. Global Settings – For location of Log file

2. Documents – For defining Document to import and to define document class (Always use AxdTrvPBSMaindata)

3. Transformations – For defining type of transformation for this document. (Always use Binary to transform non-XML files or XLST to transform XML files

4. Formats – To define import document format and to map parameters.

5. Now make set up in General Ledger –> Set Up –> Methods of importing account statement and define Bridging account. Lateron, we can bring all these bridged lines in journal by calling Bridged transactions.

6. Then post it.

Thanks for reading.

Procedur to Upload any Template

While Uploading anything as master [here I have uploaded Chart
of Accounts] I have observed following point

1. For Importing Master it will be the best if we use AdministrationàPeriodicàExcel Spreadsheets àTemplate Wizards

2. It will give us template of import for a table. (Here, Ledgertable).

3. This template will have three worksheets (1. For Definition of File, 2. For giving Look Up reference for different field 3. Actual Template)

4. At the same time it will create a ‘Definition Groups’. It will help us to import master.

5. If we go Definition Groups and then click ‘Table’ Button, we will get Export Status and Import Status. Sometime when we import data it shows that ‘Data Can not be imported’ because the import status shows ‘Imported In Total’. So, in that case we have to change status manually as ‘ Import’.

6. Now, copy the related data into that template.

7. Then to import file we should use AdministrationàPeriodicàExcel Spreadsheets àMicrosoft Office Excel Import.

8. Then select the Definition Group and file name we want to import. But don’t forget to check ‘Advanced’ Tab of Microsoft Office Excel Import dialogbox. Because there we will get ‘Import Rule’. It has following selection

a. Insert only new records :: To upload only new records

b. Insert only records into empty tables :: To upload only records into empty tables. So if there is any record in table it will not be uploaded.

c. Insert new and update existing records :: To upload new record at the same time to update existing records. So, any master is already exists and we want to make some change that master and at the same time we want to upload some new records in that master we should use this option. Even if we want to update present Table only then also we should use this option.

d. Clear tables before inserting records :: Try to use it in very less instances.

e. Use Definition group settings :: This will use the definition group settings .

f. One more point to note that if we want to upload only one column of the template then we have to map that column in Definition Groupà Table option. It will help us to select few columns and to update those columns only.

9. Now the file will be uploaded.

I have faced one issue when uploading chart of accounts , where it is not uploaded because ledgertable accountnumber is text field and it was stopping me to upload my account number in number. So, after adding string before this number it becomes text and I have uploaded it.

Regards

Financial Entry Generated By different Inventory Transaction Type

Before going into details of Journal Entry in General Ledger for different Inventory Transaction we should know

1. How many types of transactions are there? [This you
will will get from Enum ‘InventTransType’]

2. The stages of transaction. Obviously for any item transaction there must be two effect

a. Receipt

b. Issue

3. So better to know what are the different status of issues and Receipt which we will get from Enum ‘StatusIssue’ and ‘StatusReceipt’ respectively.

Now please look into details of three Enums discussed above.

Inventory Transaction Type

Name	Value	Description
Sales	0	Sales order
Production	2	Production
Purch	3	Purchase order
InventTransaction	4	Transaction
InventLossProfit	5	Profit/Loss
InventTransfer	6	Transfer
SummedUp	7	Weighted average inventory closing
ProdLine	8	Production line
BOMLine	9	BOM line
BOMMain	10	BOM
WMSOrder	11	Output order
Project	12	Project
InventCounting	13	Counting
WMSTransport	14	Pallet transport
QuarantineOrder	15	Quarantine order
DEL_SFAQuotation	16	Outdated
Asset	20	Fixed assets
TransferOrderShip	21	Transfer order shipment
TransferOrderReceive	22	Transfer order receive
TransferOrderScrap	23	Transfer order scrap
SalesQuotation	24	Quotation
QualityOrder	25	Quality order

Status Issue

Enums	ENum#	Enum Name	Financial Implication(No/Yes)
None	0	No
Sold	1	Sold	Yes
Deducted	2	Deducted	Yes
Picked	3	Picked	No
ReservPhysical	4	Reserved physical	No
ReservOrdered	5	Reserved ordered	No
OnOrder	6	On order	No
QuotationIssue	7	Quotation issue	No

Status Receipt

Enums	ENum#	Enum Name	Financial Implication(No/Yes)
None	0	No
Purchased	1	Purchased	Yes
Received	2	Received	Yes(If Selected In Parameter)
Registered	3	Registered	No
Arrived	4	Arrived	No
Ordered	5	Ordered	No
QuotationReceipt	6	Quotation receipt	No

Here, we should know that for any combination of Inventory Transaction Type and Issue Status/Receipt Status one Inventory transaction gets generated. Exception is status Deducted and Received. Because this is some preliminary status system is creating before invoice posting.

But for every combination there is not Financial Entry. Financial Entry of the inventory transaction involved only if

a. Receipt status is Received and Purchased

b. Issue Status is Deducted and Sold

Now let us discuss different financial entry system Generates when we go for inventory transaction, which depend on Inventory Transaction Type

For Inventory transaction Type ‘Sales Order’ ::

When Issue Status ‘Deducted’–

Sales Order Packing Slip Offset DR

Sales Order Packing Slip

When Issue Status ‘Sold’-

Sales Order Packing Slip DR

Sales Order Packing Slip Offset

Sales Order Consumption DR

Sales Order Issue

Customer Balance DR

Sales Revenue

For Inventory transaction Type ‘Purchase Order’ ::

When Receive Status ‘Received’–

Purchase Order Packing Slip DR

Purchase Order Packing Slip Offset

When Receive Status ‘Purchased’-

Purchase Order Packing Slip Offset DR

Purchase Order Packing Slip

Purchase, Receipt Dr

Creditor

For Inventory transaction Type ‘Transaction’ ::

Inventory loss a/c

Inventory Receipt—– Dr                                                             

Inventory Issue

Inventory Profit

For Inventory transaction Type ‘Profit/Loss’ ::

Inventory loss a/c—–Dr

Inventory Receipt—– Dr                                                             

Inventory Issue

Inventory Profit

 

For Inventory transaction Type ‘Weighted average inventory closing’::

Inventory loss a/c—–Dr

Inventory Receipt—– Dr

Sales Order Consumption –Dr                                                  

Inventory Issue

Inventory Profit

Sales Order Issue

For Inventory transaction Type ‘Counting’::

Inventory loss a/c—–Dr

Inventory Receipt—– Dr                                                             

Inventory Issue

Inventory Profit

 

For Inventory transaction Type ‘Fixed assets’::

Fixed Assets, Debit                         Dr

Fixed Assets Issue                           Cr

 

Inventory, Receipt                          Dr

Fixed Assets, Credit                        Cr

 

For Inventory transaction Type ‘Transfer order shipment’ ::

Whenever ‘Transfer Order Shipment’ gets created it automatically creates “Transfer order receive”. So following two transaction comes into existence just after ‘Transfer order shipment’

Inventory interunit receivable                   Dr

Inventory Issue                                                                CR

Inventory Receipt account                           Dr

Inventory interunit payable                        CR

For Inventory transaction Type ‘Transfer’ above entries comes together ::

Inventory interunit receivable                   Dr

Inventory Issue                                                                CR

Inventory Receipt account                           Dr

Inventory interunit payable                        CR

I have not updated this Blog with other transaction type which I am going to do very shortly.

Regards

Tutorial: refresh, reread, research, executeQuery – which one to use?

Before starting this Blog, I should say that it has been taken from another blog as follows

Actually Posted by Vanya Kashperuk at 10:30 PM in http://kashperuk.blogspot.com/2010/03/tutorial-reread-refresh-research.html

I find this Useful so I have kept in My blog

X++ developers seem to be having a lot of trouble with these 4 datasource methods, no matter how senior they are in AX.
So I decided to make a small hands-on tutorial, demonstrating the common usage scenario for each of the methods. I have ordered the methods based on the impact on the rows being displayed in the grid.
You can download the xpo with the tutorial on my SkyDrive.

1. Common mistakes

Often, developers call 2 of the mentioned methods in the following order:

formDataSource.refresh()
formDataSource.research()

or

formDataSource.reread()
formDataSource.research()

or

formDataSource.research()
formDataSource.executeQuery()

or

formDataSource.research()
formDataSource.refresh() / formDataSource.reread()

All of these are wrong, or at least partially redundant.
Hopefully, after reading the full post, there will be no questions as to why they are wrong. Leave a comment to this post if one of them is still unclear, and I will try to explain in more detail.

2. Refresh

This method basically refreshes the data displayed in the form controls with whatever is stored in the form cache for that particular datasource record. Calling refresh() method will NOT reread the record from the database. So if changes happened to the record in another process, these will not be shown after executing refresh().

refreshEx

Does a redraw of the grid rows, depending on the optional argment for specifying the number of the record to refresh (and this means the actual row number in the grid, which is less useful for AX devs). Special argument values include -1, which means that all records will be redrawn, and -2, which redraws all marked records and records with displayOptions. Default argument value is -2.
This method should be used sparingly, in cases where multiple rows from the grid are updated, resulting in changes in their displayOptions, as an example. So you should avoid using it as a replacement for refresh(), since they actually have completely different implementations in the kernel.
Also, note, that refreshEx() only redraws the grid, so the controls not in the grid might still contain outdated values. Refresh() updates everything, since this is its intention.

3. Reread

Calling reread() will query the database and re-read the current record contents into the datasource form cache. This will not display the changes on the form until a redraw of the grid contents happens (for example, when you navigate away from the row or re-open the form).
You should not use it to refresh the form data if you have through code added or removed records. For this, you would use a different method described below.

How are these 2 methods commonly used?

Usually, when you change some values in the current record through some code (for example, when the user clicks on a button), and update the database by calling update method on the table buffer, you would want to show the user the changes that happened.
In this case, you would call reread() method to update the datasource form cache with the values from the database (this will not update the screen), and then call refresh() to actually redraw the grid and show the changes to the user.

Clicking buttons with SaveRecord == Yes

Each button has a property SaveRecord, which is by default set to Yes. Whenever you click a button, the changes you have done in the current record are saved to the database. So calling reread will not restore the original record values, as some expect. If that is the user expectation, you as a developer should set the property to No.

4. Research

Calling research() will rerun the existing form query against the database, therefore updating the list with new/removed records as well as updating all existing rows. This will honor any existing filters and sorting on the form, that were set by the user.

Research(true)

The research method starting with AX 2009 accepts an optional boolean argument _retainPosition. If you call research(true), the cursor position in the grid will be preserved after the data has been refreshed. This is an extremely useful addition, which solves most of the problems with cursor positioning (findRecord method is the alternative, but this method is very slow).

5. ExecuteQuery

Calling executeQuery() will also rerun the query and update/add/delete the rows in the grid. The difference in behavior from research is described below.
ExecuteQuery should be used if you have modified the query in your code and need to refresh the form to display the data based on the updated query.

formDataSource.queryRun().query() vs formDataSource.query()

An important thing to mention here is that the form has 2 instances of the query object – one is the original datasource query (stored in formDataSource.query()), and the other is the currently used query with any user filters applied (stored in formDataSource.queryRun().query()).
When the research method is called, a new instance of the queryRun is created, using the formDataSource.queryRun().query() as the basis. Therefore, if the user has set up some filters on the displayed data, those will be preserved.
This is useful, for example, when multiple users work with a certain form, each user has his own filters set up for displaying only relevant data, and rows get inserted into the underlying table externally (for example, through AIF).
Calling executeQuery, on the other hand, will use the original query as the basis, therefore removing any user filters.
This is a distinction that everyone should understand when using research/executeQuery methods in order to prevent possible collisions with the user filters when updating the query.

Regards

Discount in Dynamics AX 2009

In AX, Discount from Vendors has been specified into two part

A. Discount Based on Payment (Cash Discount) :: Depends upon payment term specified in Cash Discount Code. AX facilitates set up of ledger for Cash Discount in 4 levels (From Higher to Lower) as follows

1. System Accounts

2. Cash Discount Code

3. Ledger Posting Group of Sales Tax Code

4. Alternative Cash Discount account in Vendor’s Open transaction editing

[Note: If there is any difference between payable
amount after Cash Discount and actual discount obtained, it will be booked as
per parameter as follows

<![if !supportLists]>i. If parameter says it is Unspecific then it will go to Ledger account for Cash Discount Difference

ii. But if parameter says it is specific then it will go to Settle account defined in Posting Profile]

B. Discount Based On Trading::

1. Line Discount – It Can be defined at Header and Line Level as follows

1. Discount :: Discount means lumpsum discount amount

2. Discount Pct :: Discount Pct means % of discount. It will be calculated after deducting Discount Amount

2. Multiline Discount – It Can be defined at Header and Line Level. It will always have Percent.

3. Total Discount [Always it needs Vendor’s Invoice Discount account set up to post
discount. And it will not be deducted from Item Prices.]

[Note: if Calculation of Line Discount and multiline
discount becomes applicable for a certain purchase line then the
calculation depends on the A/P Parameter as follows

Line Calculates the
discount based on the line discount only

Multiline Calculates the
discount based on the multiline discount only.

MAX (Line, Multiline) Calculates the discount based on the
larger of the two discounts.

MIN (Line, Multiline) Calculates the discount base
on the smaller of the two discounts.

Line + Multiline Adds the two discounts,
and then calculates the discount.

Line * Multiline Calculates the discount
based on line discount, and then calculates the discount based on the multiline
discount.]

[Note: Always Posting of Receipt
and Discount depends on Parameters set in A/P Parameter. But always there are
following 6 combinations

Each vendor – Each item.

Groups of vendors – Groups of
items.

All vendors – All items.

Each item – Each vendor.

Groups of items – Groups of
vendors.

All items – All vendors.

If we
select Vendor, Microsoft Dynamics AX starts to search from each vendor. If we
select , the application starts to search from each item.]

Regards

Error : Decimal rounding of the Physical Updating quantity in the inventory unit is incorrect – Dynamics AX 2009 Inventory Module

While implemented Trade & Logistic in our company we did not know the purpose of defining inventory unit in Item Details. Because this field is not mandatory at all. Today I have come to know that this is because if in any case we are using conversion from one unit to inventory unit and if there is decimal factor then system will throw an error as follows

“Decimal rounding of the Physical Updating quantity in the inventory unit is incorrect”.

For example we are using material x in liter and we receive it in KG then we need to use conversion scale which can give decimal factor with three decimals. In this case it will show this error.

Use of Method of Payment in Dynamics AX 2009

This is very small part in AX. But it can contribute a lot in Business Process Reengineering. Actually it will be more helpful when we want to post payment/other transaction which is related to payment term along with updation of invoice.

Here we can define following

1. Whether we want to Post Payment Voucher automatically, when we post Purchase Order/Invoice Journal. [Select File Format à‘Automatic Posting’]

2. What should be the Journal name (Vendor Disbursement Type) of Payment Voucher. (Select File Format à‘Journal Name’)

3. What should be the status shown by default on payment line (Select Overview àPayment Status)

4. The payment should be made against which ledger account. In most of the case it is Bank/Cash account. And then select that particular account number. And even we can define transaction type which will select automatic offest acount while posting that transaction lines. [Select Overview à Posting Group]

5. In the above case (#4) we can select even a ledger account where this payment entry will be temporarily posted and then that temporary posted transaction can be paid finally. [Select Overview à Posting Group à Bridging Posting and
Bridging Account]

6. What should be the basis of calculating total amount for payment . It may be

a. only for this invoice or

b. total amount due on a certain due date or

c. Total amount due on a week which covers that due date or

d. Total Invoices value.

[Select
Overview àPeriod]

7. Whether for calculating cash discount any grace period is there or not. [Select Overview àGrace]

8. We can define payment type here. On the basis of that different relevant document can be prepared. [Payment Type]

9. Through Payment control we can define that for a particular type of payment term some extra field should be mandatory. For example , in case of check payment we should make check number field mandatory. This is kind of data validation we can add to control the data entry to facilitate different reporting.

10. If we define dimension then those dimension will be added in payment journal autotamtically. Then if we want we can change it lateron.

Regards

Welcome