Export Purchase Order Data to MS Excel template with Dynamics AX 2012 ( Document Management )

Often, the customer asked to modify the layout of the standard reports to meet existing templates or tastes. and as we all know that changing the layout in the SSRS reports will take long time. Instead we can use one of the Dynamics AX functionality to export the customer data to MS Excel Template in simple way and short time.  

The Document management functionality in Microsoft Dynamics AX give you the ability to attach files to records. For example, you can attach PDF, Microsoft Word, or Microsoft Excel files to a purchase order or a sales order. You can also fill data to MS Excel and MS Word templates from Microsoft Dynamics AX data. 

 
This Article will discuss how to use MS Excel template rather than customize the standard SSRS reports in Dynamics AX 2012. 

In the following sections we will discuss how to export data from Microsoft Dynamics AX Purchase order to MS Excel templates.

This article divided into three parts.

• Prerequisites (Step by step with screenshots )
• Setup (Step by step with screenshots )
• Implementation (Step by step with screenshots )

Prerequisites:                                                            

Create purchase Order Template by following these steps:

1. Open new Microsoft Excel document ( I use Office 2010)
   
2. design the Purchase Order layout (Add company logo, Report header,report label).To simplify the subject I will add a few labels as follow:
• Purchase Order No.
• Vendor Name
• Item Code
• Description
• Qty
• Unit Price
  
  
  
3. Add Hyperlink to each Label ( Hyperlink will be used to map the Purchase order fields to the MS Excel template )
1. The Following Screenshot illustrate how to add the Hyperlink to the template field. for example in the following screenshot I added a new hyperlink which referee to cell B9 where the purchase order number will be placed
   
   
   
2. Repeat step 3 to create new hyperlink for each field. in my case I am going to add 6 hyperlinks for (Purchase Order No.,Vendor Name, Item Code, Description, Qty, Unit Price)
   
3. Save the Excel file as Excel 97-2003 Template
   
   
   

Setup:                                                                           

1. Go to Organization Administration > Setup > Document Management > Document Management Parameter
   
2. Click the number sequence Tab > Assign  number sequence to the Document file counter
   
3. Click the File types Tab > Click Add Button > Add the Microsoft word template file type if not exist and close the screen
   
   
   
4. Go to Organization Administration > Setup > Document types
   
5. Click new and follow the steps in the following screenshot to add new document type
   
   
   
6. Click the Option Button
   
   
   
   
   
7. from the Table drop down list select the "PurchTable"
   
   
   
8. in the template file field click the folder icon and select the Sales order template that we create before
   
    
9. Go to the Field Tab
   
   
   
10. in the data field select the "PurchaseId" (this field contain the Purchase Order Number/Code)
    
    
    
11. in the bookmark field write the Hyperlink name Which corresponds to "PurchaseId" in this case write "Sheet1!B9"
    
    
    
12. repeat steps from 10 to 11 with all fields that you want to export and link it to the  corresponds Hyperlink ( make sure to select the "PurchLine" table in the Data Table Filed before mapping the Purchase Order line fileds to the  corresponds  hyperlink see the orange rectangle)
    

Implementation:                                                      

1. Go to > Account Payables > Common--> purchase Order > All purchase Orders
   
    
2. Select any purchase Order and Click the Attachment Button
   
   
   
3. Click the New Button and Select the Workbook
   
   
   
4. the System will start the export process
   
   
   
5. New document will be created and the Purchase Order Data will be populated  automatically.
   
   
   
6. that's all. Cool :)
   
7. 
   

Configure and use one-time supplier functionality in Dynamics AX 2012

One-time Supplies is common practice in trade business. one time supplier is a vendor who supply items or service for one time.
Dynamics AX help organization to separate the regular vendors accounts form the one time vendors.
in the following steps we will illustrate how to Configure and use one-time supplier functionality in Dynamics AX 2012.



1- Go to Accounts Payable --> Setup --> Accounts payable parameters.

2- in the Account Payable Parameters select the Number sequences tab then set the number sequence for one-time supplier. This sequence number will be used to generate automatic account number for the one time supplier during the purchase order creation.

3- Select the General Tab then select the "one-time vendor account" this account Information is automatically copied when you create a one-time vendor account.

4- Now we are ready to use the one-time suppliers. Go to Accounts payable --> Purchase Orders.

5- Create New Purchase Order.

6- Select One-time supplier checkbox, enter the supplier Name. Note that the system automatically generate the vendor account by using the number sequence that we assign in step 2. Complete the purchase  order as usual.

7- if we check the all vendors list we will notes that the one-time supplier have been created.

8- and all the vendor account information that we specified in step 3 has been copied to the new supplier except the account number and account name.

AX 2012 | moving average costing method - Part 5

This is the last part of the  moving average costing method in Dynamics AX 2012. in this part we will discuss the reports which show how moving average was calculated.

If you sort transactions on the inventory value report according to transaction time, transactions are listed chronologically, and you can view the costs from a moving average perspective. This way, you can verify that the cost of a moving average product has not been changed retroactively, even though the product has, for example, been adjusted by using backdating.

 
In the following  2 examples, you’ll see how the cost of a product changes with the flow of business events. The two examples that follow are based on the same business events, but they are sorted differently.

• The first example  is sorted by transaction time. With this sorting order, the business events are aligned with the moving average perspective, where transactions are listed in chronological order.

• The second example is sorted by posting date. This sorting order shows the financial impact of the business events; however, for a product that is calculated by moving average, the calculation of the average unit cost is not reflected the way you would assume for a moving average product.

To print the inventory value report according to transaction time do the following:

1- Go to Inventory and warehouse management > Setup > Inventory > Inventory value reports to create an inventory value report setup.

2-  In the Inventory value reports form, click New, enter an ID and a name for the report, and then, in the Range field, select Transaction time. The transaction time is the actual date that the transaction is reported and the moving average cost for the product is updated.

3- Click Inventory and warehouse management > Reports > Status > Inventory value > Inventory value.

4- In the ID field, select the ID of the report that you created, Under Date interval, enter your date interval information, In the From date and To datefields, enter a date interval, and then click OK to run the report.

5- please note that there is two transaction was posted in 07/01/2015. but they appear in the report according to the transaction time (08/30/2015)

To print the inventory value report according to posted date do the following:

6- Go to Inventory and warehouse management > Setup > Inventory > Inventory value reports to create an inventory value report setup.

7- Select the ID which you created in step 2 then change the range field toposting date. save and close.

8-   Repeat step 4 and 5 to print the report.

9- please note that transactions are not listed chronologically, so the two transaction was posted in 07/01/2015 is listed on 08/01/2015 as a transfer from the previous month.

AX 2012 | moving average costing method - Part 4

In this part we will discuss production costing with moving average. If you use moving average in production costing, costs can be based on the current costs of raw materials rather than on the cost price that was originally registered for the item master. When the finished good is reported as finished, the cost reflects the estimated product cost calculation that is executed by a release of production orders.

The original cost calculation of the finished good might have happened months ago, and thus it might be outdated. With moving average, the result of a product cost calculation that is executed during the estimation process can be adopted rather than the cost from the original cost calculation of the finished good.



Example: Use current costs in a BOM calculation

In this example, you have a finished product, D0007 (Speaker Pro Kit), and a two raw material products, M0023 (Speaker Cable Banana Plugs 24K ), M0024 (Speaker Cable In-wall 50 Ft) Furthermore, the following details apply:

• the three products are calculated by moving average.
• The D0007 consumes 1 piece of M0023  raw material and 1 piece of M0024  raw material.
• A bill of materials (BOM) calculation has been activated with a unit cost of 10.00 for each piece of raw material.

You create a production order for product D0007 , and you buy 1 new piece of each raw material M0023 ,M0024  for the production order at a price of 20.00 for each item item. which is higher than the cost that is activated in the product (5.25 for M0023 and 19.07 for M0024). During the estimation process of the production order, the cost price of 20.00 is applied, and this is the cost price that is included when you report the product as finished.

To see this example in action please follow the steps below: 

1- First make sure the  three products are calculated by moving average.

2- Check the row material cost for item M0023 in the released item form

3- Check the row material cost for item M0024 in the released item form

4- Make sure that the three products don't have quantity on hand.  

5- now go to  Inventory and warehouse management > Journals > Inventory adjustment, and then create a new line.

6- Select product M0023, M0024  ,select the site, warehouse then  enter 1in the Quantity field and 20.00 in the Cost price field,for each item then post the journal.

7- Go to Production control > Production orders > All production orders. and then create a production order for product D0007.( make sure to select the warehouse. i forgot to do it before the screen shoot)

8- From the All production orders form, start a production of D0007, click Estimate to run an estimation of the BOM, and then click OK.

9- In the Start form, on the General tab, select Always in both the Automatic route consumption and the Automatic BOM consumption fields, and then click OK.

10- In the All production orders form select the production order then click Report as finished, and then click OK.

11- On the View tab, Click the calculate price notice that the adjusted cost of 20.00 per unit has been applied for consumption of each of the two pieces of raw material.

AX 2012 | moving average costing method - Part 3

After we discussed the Handling of price differences between a product receipt and the invoice in part 1 and Revaluation for moving average in part 2. in this post we will discuss the Backdating with moving average.

When you backdate a product receipt or an invoice, it is revalued to the current moving average cost. Also, a backdated issue is posted at the current moving average cost. With backdating, you cannot change the moving average going forward.


Example: Backdate a transaction

In this example, you want to backdate a transaction by adding a quantity of 1, because of a receipt that happened before the current date. The current moving average cost is 15.00, and you set the price of the backdated transaction to 20.00. When you backdate the receipt, the following cost calculations occur:

• Your inventory is updated by the quantity of 1 as of the date of the backdate, but the moving average cost remains 15.00.
•  The additional 5.00 out of the 20.00 is expensed as of the date of the backdate.

to see the previous example in action please follow the steps:

1- Go to account parables >  Common  > purchase orders > create new purchase order with backdate

2- In the purchase order lines enter item no D0007 with quantity of 1, price 20. then confirm > go to product receipt > enter backdate in the product receipt date field to then post (make sure to select the same site warehouse that you used in part 1 and part 2) .

3- to see the voucher go to receive  > product receipt > voucher.

4- as you can see The additional 5.00 out of the 20.00 is posted to the Price difference for moving average account.