Vendor catalogs import
In Microsoft Dynamics AX, purchasing professionals can create and maintain catalogs for company employees to use when they order items and services for internal use. When you create a procurement catalog you can add the items and services that you make available to employees, either by importing the vendor catalog data or by adding the vendor catalog data to the product master manually. If you use the catalog import process the vendor can send you the product catalog data and you can upload it by using the Microsoft Dynamics AX client.
The product data that the vendor submits to you, in the form of a catalog maintenance request (CMR) file, must be in XML file format. The CMR file should contain all of the details for the products that the vendor supplies to your company.
Importing vendor catalog data
To import vendor catalog data, you must complete the following tasks:
- Set up a project in data management workspace. Here you have to define your data mapping rules.
- Set up a procurement category hierarchy, and assign your vendors to procurement categories. If you use commodity codes, add the commodity codes to the procurement categories.
- Configure the vendor for catalog import.
- Configure workflow for catalog import.
- Create a CMR file template and share this with your vendor.
- Create a vendor catalog. The catalog maintenance request (CMR) files that you receive from your vendor are grouped in this catalog.
- Upload the CMR file.
- Review, approve, or reject the products in the vendor catalog. Details that can be reviewed include the product name, description, pricing, or order quantity requirements. Approved products are added to the product master and are released to the selected legal entities. Only approved products can be added to the procurement catalog. The products are now automatically mapped to the procurement categories in AX.
For the current version of Microsoft Dynamics AX, we are using the Data Import/Export Framework and a predefined composite entity (you can read more on data entities here).
The product data that the vendor submits to you, should still be in the form of a catalog maintenance request (CMR) file, must be in XML file format.
Set up the system to import vendor catalog data
You will need to set up the system to support the vendor catalog import scenario, by creating an import job for vendor catalogs.
- On the main dashboard, click the Data management tile to open the data management workspace.
- Click the Import tile to create a new data project.
- Enter a valid job name, source data format, and entity name.
Do note for data source we support only XML-element or XML-attribute. Entity name should be “Vendor catalogs”.
Upload an XML mapping data file for vendor catalogs data entity. The XML mapping data file defines which fields you expect to import from your vendor CMR files. These fields will appear as your source and you will need to map these fields to Dynamics AX internal schema reflected by the staging schema.
In this way you have the possibility to map the XML schema template that you provide to your vendors, to the internal schema defined in Dynamics AX. There can be only one mapping defined at a time.
The internal schema we currently support you can also see it below and it reflects a catalog maintenance request entity which includes product details entities with the associated product pricing, order quantity requirements and detailed product descriptions entities.
This is how the initial XML file should like:
<?xml version="1.0" encoding="utf-8"?><Document> <CatVendorCatalogMaintenanceRequestEntity> <UploadDateTime></UploadDateTime> <CatVendorProductCandidateEntity> <ProductCategoryHierarchyName></ProductCategoryHierarchyName> <ProductCategoryName></ProductCategoryName> <ActionType></ActionType> <ProductNumber></ProductNumber> <ProductSubtype></ProductSubtype> <SearchName></SearchName> <BarCode></BarCode> <ProductColorId></ProductColorId> <ProductConfigurationId></ProductConfigurationId> <ProductSizeId></ProductSizeId> <ProductStyleId></ProductStyleId> <CatVendorProductCandidatePurchasePriceEntity> <CurrencyCode></CurrencyCode> <UnitSymbol></UnitSymbol> <Price></Price> </CatVendorProductCandidatePurchasePriceEntity> <CatVendorProductCandidateSalesPriceEntity> <CurrencyCode></CurrencyCode> <UnitSymbol></UnitSymbol> <Price></Price> <SuggestedPrice></SuggestedPrice> </CatVendorProductCandidateSalesPriceEntity> <CatVendorProductCandidateDefaultOrderSettingsEntity> <UnitSymbol></UnitSymbol> <LeadTime></LeadTime> <OrderQuantityMultiples></OrderQuantityMultiples> <MaximumOrderQuantity></MaximumOrderQuantity> <MinimumOrderQuantity></MinimumOrderQuantity> <StandardOrderQuantity></StandardOrderQuantity> </CatVendorProductCandidateDefaultOrderSettingsEntity> <CatVendorProductCandidateTranslationEntity> <ProductName></ProductName> <Description></Description> <LanguageId></LanguageId></CatVendorProductCandidateTranslationEntity> </CatVendorProductCandidateEntity> </CatVendorCatalogMaintenanceRequestEntity></Document>
In the mapping file you have to include UploadDateTime field at the level of CatVendorCatalogMaintenanceRequestEntity (see example above) . This field is used internally to track the upload time for the catalog maintenance request and it should not be included in the actual CMR files.
You can also include the field ActionType at the level of CatVendorProductCandidateEntity. This field can be used to explicitly specify the type of action you want to take for the product: add, update or delete. The values supported for these fields in the CRM file are: Add, Update and Delete.
Go to the View map and here you will see the vendor catalog composite entity and also the individual entities defined in the composite entity: CatVendorCatalogMaintenanceRequestEntity, CatVendorProductCandidateEntity, CatVendorProductCandidatePurchasePriceEntity, CatVendorProductCandidateSalesPriceEntity, CatVendorProductCandidateDefaultOrderSettingsEntity, CatVendorProductCandidateTranslationEntity
You can click each entity data card to set up, review, or modify field maps, and to set up XSLT-based transforms that must be applied to inbound data.
o Source – These are inbound CMR files Typically data format includes – CSV, xml, tab delimited ; This will be the xml schema you will use for you CMR files.
o Staging – These are auto generated tables which map very closely with data entity. When “data management enabled” is true staging tables are generated to provide intermediary storage. This enables the framework to do high volume file parsing, transformation and some validations.
Note: important, mandatory fields marked with red star should be always mapped to a field.
To map your own field to an existing field in the fixed schema, you can do this by drag a drop from source field to destination field and pressing save.
Note: The mapped fields are case sensitive, which means the CMR files should use exact field names.
Also if fields were defined in the mapping even they are not mandatory they are expected to be present in the input. That means if for example BarCode was presented in the initial mapping file, we expect to see it in xml file even though it doesn’t contain any value.
- Click Save. At this point your system is configured to support importing of vendor catalog files.Currently the product related data we support is: product name, description, translations. pricing or order quantity requirements.
NOTE: You should not import data directly from data management workspace. The vendor catalog maintenance request files should be imported in the context of a vendor catalog which also enforces a process of review and approval.
Set up a procurement category hierarchy
You won’t be able to import products from categories where the vendor is not approved for procurement. To approve the vendor:
- Go to Procurement and sourcing > Procurement categories.
- Select the category. Add the vendor to the list of approved vendors in the Vendors fast tab.
Configure the vendor for catalog import
In order to be able to import catalogs for a particular vendor it has to be enabled for catalog import. There are two ways to achieve that
- For a specific vendor go to Procurement > Set up > Configure vendor for catalog import
- If you don’t do this you will be prompted if you want to enable the vendor for catalog import when creating a new catalog for the vendor
Set up workflow
After a catalog maintenance request (CMR) file has been successfully uploaded, the purchasing professional can review the product details in the file. The vendor indicates whether the product is new, modified, or must be deleted. Information about the product pricing, product descriptions, product attributes, and order quantity requirements are also included in the CMR file. As an approver, you can indicate whether products are made available to selected legal entities and approve or reject the products in the file. Approved products are added to the product master and are released to the selected legal entities.
To support the approval process we are leveraging another powerful feature of AX: workflow processing.
You are allowed to set up rules for automated approval of vendor catalogs and specify one or more reviewers if manual approval is required. To enable the vendor catalog import functionality it is required to set up two types of workflows: Catalog import product approval (line-level), Catalog import approval (catalog-level). We should always define both workflows since the Catalog import approval will always require Catalog import product approval to approve products, either manually or automatically.
Catalog import product approval
This type of workflow processes all the products that are included in the CMR file. Completion of all of the individual line-level workflow completes the overall catalog import workflow. In order to create a product approval workflow:
- Click Procurement and sourcing > Setup > Procurement and sourcing workflows.
- On the Action Pane, click New.
- Select Catalog import product approval and then click Create workflow.
The common catalog import product approval workflow should look like this:
Set up approvers
- Double click the Catalog import product approval element.
- Click the Step 1 element.
- Click Assignment in the action pane.
- The simplest assignment would be User->Admin.
Set up automatic actions
Automatic actions allow the workflow framework to automatically approve or reject the products in the imported vendor catalog which meet certain conditions. In order to set up an automatic action you need to:
- Select the Catalog import product approval element.
- Click Automatic actions in the action pane.
- Click the Enable automatic actions check box
- Setup the conditions for auto approval/rejectionThere is one type of condition which I would like to focus on. You can specify Product candidate.Price delta as a parameter of the automatic action. The price delta is calculated as a ratio: (new price – old price) / old price. So if you want to make sure that the price delta is within 20% you need to set the condition to Product candidate.Price delta <= 0.2
- Select the type of automatic action (approve/reject)
You can also setup auto approval for condition that IsAutomatedApproval enabled,.
IsAutomatedApproval setting is controled directly on the vendor catalog page. On the Action Pane, on the Catalogs tab, in the Maintain group, click Enable automated approval. This sets the Automated approval field to Enabled.
Catalog import approval
This type of workflow is used for setting up the rules for approving the whole catalog. When you configure this workflow, you can reference the Catalog import product approval workflow that you configured earlier. The common setup would be to automatically approve the whole catalog import after all the products have been approved:
In the properties of the Vendor catalog lines (products) element you need to reference the catalog import product approval that you created earlier.
Import a catalog from a vendor
First you have to set up a catalog for your vendor. The catalog maintenance request (CMR) files that you receive from your vendor are grouped in this catalog. After you set up the catalog, you can upload CMR files for the vendor. You can also view the details and the event log for new or existing CMR files that have been added to the vendor catalog
Use this procedure to create a new vendor catalog. This is the catalog to which you upload a catalog maintenance request (CMR) file for a vendor. If you delete the vendor catalog, you can no longer import CMR files to it. If you still want to import catalog data from the vendor, you must create a new catalog for the vendor
- Go to Procurement and sourcing > Catalogs > Vendor catalogs and create new catalog.
- In the New catalog form, in the Vendor field, select the vendor that you are creating the catalog for.
- Enter a name and description for the catalog, and then click Save.
Upload a catalog maintenance request (CRM) file. Go to Catalog file history tab, click Upload file.
- In the Upload file dialog box, browse to the location of the CMR file that you created. The maximum file size allowed to upload is 10 MB per file.
- Enter an effective date and an expiration date. These dates define the date range in which the pricing for the products in the CMR file is valid. When trade agreement are created it will include these dates.
- Select one of the following update types for the CMR file:
- Add updates to the existing vendor catalog – Add product updates to an existing catalog.
- Replace the existing vendor catalog with a new catalog – Add a new catalog, or replace an existing catalog with a new catalog.
- If you are creating a new catalog to replace an existing catalog, all items and services that are in that catalog are overwritten by the matching items and services in the new CMR file. Any existing items and services that are not in the new CMR file are deleted. Any items and services that are included in the new CMR file, but that are not included in the existing catalog, are treated as new products
- Select category hiererachy type you want to associate the product data. You can also select either procurement category hierarchy or commodity code hierarchy.
- Click OK to start the upload process for the CMR file.
When you upload the CMR file, the file is validated against the category hierarchy type selected and the categories the vendor is allowed for procurement. If the validation fails and some of the data is invalid the catalog upload process will fail. You can view the details for the CMR file upload status in the event log. After the CMR file is uploaded successfully, you can review the details of the CMR file in the vendor catalog.
- Start processing – The CMR file has been submitted for import.
- Start catalog upload – The CMR file has been submitted and is in process.
- Catalog upload failed – An error occurred after the file was submitted for processing, and the CMR file was not imported.
- Catalog upload complete – The CMR file was successfully uploaded.
- Invalid CMR – The CMR file does not comply with the current schema for catalog import. If the CMR file was created by using an outdated schema, the CMR file must be recreated by using the current schema. You can also use advanced troubleshooting to narrow done the cause of the failure.
- Pending approval – The CMR file is in review and waiting for approval by the purchasing agent.
- Product rejections – Products in the CMR file were rejected for import into the procurement catalog. Product rejections are indicated by a warning during the import process, and the file continues to be processed for products that are approved.
- Approval complete – The purchasing professional has completed the review of the products and images that are contained in the CMR file.
- Finish processing – Approved products have been added to the product master in Microsoft Dynamics AX and trade agreement journals have been created. The data in the CMR file has been passed to the archive folder for the vendor.
After the products in the CMR file are reviewed and approved, you can release the approved products to the legal entities in which the vendor is authorized to supply products, and appropriate trade agreements can be created.
Validate and approve imported catalogs
- Click Procurement and sourcing > Common > Catalogs > Vendor catalogs. On the Vendor catalogs list page, double-click a vendor catalog.
- In the Update catalog form, on the Catalog file history fast tab, select a CMR file, and then click Details.
- In the Catalog approval page form, review the product details for the products that are included in the CMR file. You can use the Item status field to view all items or only items that have a selected status. Select one of the following options:
- All items – View all products that are included in the CMR file. This is the default setting.
- Add – View only new products. After a new product is approved, it is added to the product master. When the product is released to the legal entity, the corresponding trade agreement is created and the product appears on the Released products list page.
- Update – View only existing products that are being modified. After a product is approved, the modifications are applied to the existing product in the product master. New trade agreements are created only if there are price changes for the product.
- Delete – View only products that are no longer offered by the vendor and should be deleted. After the product request is approved, the product is no longer available for purchase.
- To filter product changes by legal entity, select a legal entity in the Buying legal entity field. You can view the price, name and description and purchase quantity changes by legal entity.
Note A legal entity appears in the list only if vendor products that have been imported are approved and released to the legal entity. If no products have been released to a legal entity for the vendor, the list is empty.
- In the lower pane, on the Price tab, review the pricing for a selected product. The current price and new price are displayed in the currency and unit of measure in which the product is offered.
- On the Name and description tab, review the name and description of a selected product in specified languages.
- On the Purchase quantity tab, review changes to the purchase order quantity requirements and purchase lead time for the selected product.
- In the upper pane, select the products that you want to approve, and in the workflow message bar, click Actions > Approve. Approved products are added to the product master.
- In the upper pane, select the products that you want to reject, and in the workflow message bar, click Actions > Reject. Products that are rejected are not added to the product master.
- Click Release products to legal entity, and then in the Release products to legal entity form, select the legal entities in which the vendor’s approved products will be available. Corresponding trade agreements will be created for the products in these legal entities. Products must be added to the product master and be available in the legal entities before they can appear in the procurement catalog.
Note If you do not release the approved products to the legal entities by using the Catalog approval page form, you can release approved products to legal entities by using either the Vendor catalogs list page or the Update catalog form.
We have a first level of logs to see if anything failed during the import of a file.
However we don’t expose yet all the detailed information here in regards with data management framework processing.
For this now we have an advanced ways of seeing more details when import fails, by looking at execution details of the import job by going to data management workspace:
For the failed job, I can drill through and see more details
By going further into view staging data and viewing data you can identity there is a problem in this case in product data: