Dynamics AX 2012 Ledger transactions/balances Import using Excel Add-in

Purpose:The purpose of this document is to illustrate how to use Microsoft Dynamics AX 2012 Excel Add-in for import of ledger transactions.

Challenge: Data model changes in Microsoft Dynamics related to high normalization and introduction of surrogate keys made some imports more complex. In fact the data model forming General Journal was not dramatically changed and import principle remains the same – populate the journal and then post the journal. However some information which is usually automatically generated in Microsoft Dynamics AX 2012 Rich Client by means of number sequences such as voucher number will have to be provided.

Solution: Microsoft Dynamics AX 2012 ships with General Journal AIF Web Service (LedgerGeneralJournalService) which can be used in integration scenarios. Also General Journal AIF Web Service can be used in Excel for import of ledger transactions. In order to import ledger transactions using Excel the mentioned AIF Web Service will be used.

Assumption: The assumption is that appropriate reference data such as main accounts, etc. was created in advance.

Data Model:

Table Name	Table Description
LedgerJournalTable	The LedgerJournalTable table contains all the defaulting and state information pertaining to a single journal. The transaction details of a journal are managed in the LedgerJournalTrans table.
LedgerJournalTrans	The LedgerJournalTrans table contains the transaction detail information that pertains to a single journal. The individual transaction lines are also referred to as voucher lines. The journal is a record in the LedgerJournalTable table.

Data Model Diagram:

Walkthrough:

Connection

Add Data

Field Chooser

Accounting structure

LedgerJournalTable

Field Name	Field Description
	Journal batch number
	Name
	Description

LedgerJournalTrans

Field Name	Field Description
	Journal batch number
	Voucher
	Date
	Company accounts
	Account type
	LedgerDimension
	LedgerDimension.MainAccount
	LedgerDimension.Department
	Description
	Debit
	Credit
	Currency

Sequence:

1. Publish All

Result:

Dynamics AX – General Journal

Dynamics AX – General Journal lines

Dynamics AX - Posting

SQL Trace:

Summary: For the purposes of small or medium data migration (data conversion) where performance is not a concern Excel can be used for import of ledger transactions into Microsoft Dynamics AX 2012. General Journal AIF Web Service (LedgerGeneralJournalService) can be used in Excel for import of ledger transactions. Excel template can be created and used for import of ledger transactions.

Indian Localization in AX 2012

Indian Localization in AX 2012

Indian localization in AX 2012 R2...

Indian Localization Parameters – General Ledger



Stock Transfer activation parameter as part of Indian localization

Stock Transfer

Company Information – Tax details

Indian localization – General Ledger

EXIM -

Formula Designer:

Withholding Tax TDS Threshold not calculated correctly

Withholding Tax TDS Threshold not calculated correctly

Symptoms:

1.            To generate Withholding  Tax in general journal line, then you have already key in the withholding tax group (set as TDS type and within the withholding tax set "Apply threshold" to "True" with the threshold reference being set up).

2.            But when click "Withholding tax" button in the journal line, there is no withholding records being created/calculated.

Issue:

There is "hidden" setup for tax value data which for my case i need to spent 2 days debugging to resolve this issue.

You must fill in this setup at the leaf node of your threshold designer screen.

The table name is Taxvaluedata_IN.

Resolution:

Just go to designer from Withhold profile. Follow exact step on following picture:

project types AX 2012

project types that you can create in Project management and accounting. The primary difference among these project types, apart from their different purposes, lies in how they are set up for costs and revenue recognition.
The project type that you choose depends on the purpose of the project. The following table describes the typical use of each project type.

Project type

Time and material

In Time and material projects, the customer is billed for all costs that are incurred on a project. These include costs for hours, expenses, items, and fees.

Fixed-price

The invoices in fixed-price projects consist of on-account transactions. These projects are invoiced according to a billing schedule that is based on a project contract. Revenue for a fixed-price project can be calculated and posted throughout the project by using the completed percentage method, or when the project is finished, by using the completed contract method. Companies can often benefit from using the value of the work in process (WIP) to calculate the degree of completion of a project or group of projects.

Investment

Investment projects are projects that do not produce immediate earnings. They are typically used for long-term internal projects in which the costs have to be capitalized. The following are also true of investment projects:
Only costs for items, hours, and expenses can be recorded for an investment project.
Costs in an investment project are tracked and controlled by using the Project management and accounting Estimate feature.
Investment projects can be set up with an optional maximum capitalization limit.
As an investment project progresses, you record its costs in WIP accounts, where the costs are held until the project is completed. When the project is eliminated, you transfer the WIP value to a fixed asset, a ledger account, or a new project.
NoteNote
Transactions on investment projects are not reflected in the Post costs form, Accrue revenue form, or Create invoice proposals form.

Cost project

Like Investment projects, Cost projects are typically used to track internal projects, and only hours, expenses, and items can be recorded for costs projects. Cost projects are usually of shorter duration than investment projects. Unlike Investment projects, Cost projects cannot be capitalized to balance sheet accounts. Instead, their project transactions are posted only to profit and loss accounts.
NoteNote
Transactions on cost projects are not reflected in the Post costs form, Accrue revenue form, or Create invoice proposals form.
Because Cost projects are typically used to track internal projects, they do not ordinarily have to be associated with a customer account. However, if your setup requires that item requirements be created for purchase orders, you do have to associate the Cost project with a customer. This is because item requirements are managed as sales order lines, and the system requires that a customer be specified. However, this setup will not result in item requirements being created automatically from a purchase order. For Cost projects, the Create item requirement setting is ignored.
If you do need an item requirement in a Cost project, you can create one manually, so long as a customer is associated with the project. You can select the Create item requirement option in the General area in the Project management and accounting parameters form.

Internal

Internal projects are used to track costs on a project that is internal to your organization. This type of project can provide a planning tool to manage resource consumption.
NoteNote
Transactions on internal projects are not reflected in the Accrue revenue form or Create invoice proposals form.

Time project

Time projects are used to track time that is associated with non-chargeable and non-productive activities, such as a project to track sick time for workers. Transactions in Time projects are not posted to the ledger. Instead, they are included in worker utilization reports.
Only hour transactions can be recorded in Time projects. You use an hour journal or timesheet to register these hours to the project. After the hours are registered, they appear as project transactions, but without a corresponding voucher transaction.
NoteNote
Transactions on time projects are not reflected in the Post costs form, Accrue revenue form, or Create invoice proposals form.
See also

Project management and accounting

use Project management and accounting to plan, create, manage, control and complete projects for your organization. Customer-focused projects can be set up on a time and materials or fixed-price basis. You can also use the module to manage costs for internal and investment projects.

You can create project quotations that can be converted to projects. You create project contracts with one or more funding sources that will be invoiced for project costs and fees. Funding sources can include customers, internal organizations, and grants. A project contract can have one or more projects assigned to it. Each project that you create can have one or more subprojects and activities that comprise the project work structure.

For flexible project planning, you can integrate the project management and accounting module with Microsoft Project Server.

You can create and monitor project budgets for cost control. Employees and contractors can enter project time sheets and expense reports to record project-related time and expenses.

You can create service industry-focused projects that consist primarily of worker services by drawing on features such as contract management, quotations, budgeting, project policies, project parameters, and categories.

You can assign attributes for project skills and experience to workers. You can also search for and assign workers to a project based on worker skills and availability, or based on the requirements for a project.

You can assign indirect costs, define the calculations for indirect cost amounts, and allocate indirect costs to a project. Indirect costs are calculated based on worker hours that are added to a project.

You can record invoiced amounts that are retained by a customer until the progress on a project reaches an agreed-upon stage. You can also retain a percentage of vendor invoices until you confirm the quality of work by a vendor who is a subcontractor on a project, or until you receive payment from a customer or other funding source.

You can set up billing rules that track progress on a project, and define when and how to calculate customer invoice amounts for advances, project milestones, completed units, amounts retained by customers, and administrative fees.

Project managers can view reports that provide project details and analyze project financials from a variety of perspectives.

Business processes

Introduction to Microsoft Dynamics AX Project Management and Accounting Add-in.

The Project Management and Accounting Add-in integrates with Project management and accounting and Human resources, so that you can manage projects that consist primarily of worker services. You can use the features for project management and accounting, such as contract management, quotations, budgeting, project policies, and project parameters and categories, together with the Project Management and Accounting Add-in.

Use the Project Management and Accounting Add-in to identify and schedule workers who have specific project experience, and to manage direct and indirect costs for service projects. You can also use the Project Management and Accounting Add-in to invoice customers for billing scenarios that are specific to the service industry, and to manage payments to vendors who are subcontractors on your projects.

Key Features

The Project Management and Accounting Add-in includes the following features:

• Other worker attributes – Assign attributes for project skills and experience to a worker. For more information, .
• Resource-based scheduling – Search for workers, and assign workers to projects when worker skills and availability match the requirements and schedules of the projects. For more information
• Project-based scheduling – Search for workers, and assign workers to projects, based on the requirements of the projects. For more information, .
• Resource-assignment analysis – View analyses of project activities by worker, by department, and by project. For more information.
• Indirect cost components – Identify indirect costs, define calculations for indirect cost amounts, and allocate indirect costs to a project. For more information.
• Define billing rules to calculate customer invoice amounts in the following scenarios:
  • Customer advance – Create a customer invoice before a project starts. For more information, see .
  • Project milestone – Create a customer invoice when a project activity is completed.
  • Per unit – Create a customer invoice when a specific unit of the project is delivered.
  • Customer retention – Create a customer invoice, but withhold part of the invoice amount until the project is completed.
  • Administrative fee – Create a customer invoice, and add an administrative fee that you and the customer have agreed on.
  • Customer payment retention release – Create a customer invoice when amounts that were retained by a customer are released by the customer. For more information, 
• Vendor payment retention – Retain part of a payment to a vendor. For example, you can retain a percentage of vendor invoices until you confirm the quality of the work that the vendor performed on a project. For more information about vendor payment retention,
• Pay when paid – Schedule payments to vendors when you receive payments from customers for projects. For more information about pay-when-paid projects, 

Before you begin to work in the Project Management and Accounting Add-in, set up parameters for project management and accounting. 

Use of Currency Converter Form in Ax 2012.

There are scenarios when a company wants to track its financial information in currency other than the company accounting currency. Currency converter setup helps to achieve this requirement. This setup allows user to setup numeric conversion for online transactions. If the Conversion check box is selected, the system will automatically convert transaction amounts in grids using the exchange rates in the lower pane. To setup currency converter, refer the following form;

General Ledger -> Setup -> Currency -> Currencies

This setup allows users to track its financial information other than its accounting company currency in forms and reports. This conversion is completed by double-clicking the Currency converter in the Status bar in these forms.

Enter the date used for the conversion in the Date of rate field, eg. 22-01-2013.Then, click a currency to select the new currency. The Currency converter form will automatically close and the Currency field on the status bar displays the new currency.

But remember that exchange rates for the two currencies for the specified date or date range must exist in the system. All the forms, inquiries, and reports that originally displayed amounts in the accounting currency now display the currency selected.

If you want to reset the currency to its default accounting currency, then open the Currency converter form. Click the Reset button to return to the ledger accounting currency.