Importing job lines directly into the Eralis Job tables does not result in any SAP Business One related journal transactions, and can be extremely difficult due to identifying fields and primary key matching. No General Ledger balances or transactions are created. This is used for importing completed historical transactions where the GL entries already exist in the previous system.
Import Scenarios
Before importing Job lines, you will need to decide what level of Job detail will be imported and whether the General Ledger balances will be imported in advance or built during the import.
There are several common scenarios:
| Scenario | Conditions | Detail | Import Method |
|---|---|---|---|
| 1 | Import historical data from another system. | Microsoft DTS | |
| Eralis Job | Job lines will be imported as fully processed. | ||
| General Ledger | These lines are fully processed so no GL entries are required in SAP Business One. | ||
| 2 | Import opening balances from another system with GL entries. | Copy and paste from Excel | |
| Eralis Job | Job lines will be imported as open lines. | ||
| General Ledger | A General Ledger entry is required for each open line to build an accurate work in progress balance. | ||
| 3 | Import opening balances from another system without GL entries. | Microsoft DTS | |
| Eralis Job | Job lines will be imported as open lines. | ||
| General Ledger | No General Ledger entry or stock movement is made as these have already been imported as DTW opening balances. | ||
Import Templates
Job lines and Eralis Job quote lines are imported using the following template: EnpriseJobCosting_ImportTemplates_JobsLines.xls.
Complete the templates ensuring that each row’s mandatory fields are completed with relevant data. Refer to the import template spreadsheet worksheets for instructions on what data types can be inserted on each column.
Import Process (Microsoft DTS)
The import process for a SQL database is completed using Microsoft Data Transformation Services. For SAP HANA database, refer to the import process section below.
The following tables can be imported to using this process:
- ENPRISE_JOBCOST_JOBLINES
- ENPRISE_JOBCOST_NOTES
- Ensure the import template is closed prior to beginning the import.
- Select Import Data from the All Tasks menu by right-clicking on the database you wish to import to.
- The DTS Wizard will load and guide you through the import procedure.
- Select Microsoft Excel as the Data Source, then select the import file from its saved location and Excel Version. Tick the First row has column names option.
- Select the Destination as either SQL Native Client or Microsoft OLE DB Provider for SQL Server. Specify the server connection details and database for import.
- Select Copy data from one or more tables or views to specify that you wish to copy the data from the source spreadsheet.
- Tick the Source Column checkbox for each worksheet(s) you intend to import and select the Eralis Job table with the same name from the drop-down selection in the Destination column.
- Select each row / table that you are importing and click the Edit Mappings button in the footer of the window to map fields from the spreadsheet to fields in the Eralis Job tables.
- Select Append rows to destination table radio button in the header of the Column Mappings screen.
- In the Mappings grid, Microsoft DTS will attempt to map the spreadsheet (Source) to the Eralis Job table (Destination) automatically based on column names.
If changes to the suggested mappings are required, select the correct column or change the destination to <ignore> from the drop-down list to change the source or not import any data.
Important Note: The SEQNO field is an identity and should be set to <ignore>.
- Repeat this process for each of the tables that you are importing to.
- Specify whether you wish to run the DTS package immediately or save it and execute later.
- Microsoft DTS will give a status report once the DTS package has been run. Where errors occur, you can drill down on the rows for a detailed message on the issue. Update the import template or DTS package and re-run the import.
Hints
- If several instances of the same worksheet show as source options during the import, select the worksheet which ends in $.
- Imports sometimes fail because of blank rows at the end of an excel worksheet. To eliminate blank rows, highlight only the rows and columns with data for import and copy and paste these to a new worksheet, save it and then import the new worksheet.
Import Process (SAP HANA Studio)
The import process for a HANA database is completed using the HANA Content Import Wizard.
The following tables can be imported to using this process:
- ENPRISE_JOBCOST_JOBLINES
- ENPRISE_JOBCOST_NOTES
- Ensure the import template is closed prior to beginning the import.
- Use the File menu option at the top. Select File then Import.
- Import options will be displayed in the pop-up screen. Under SAP HANA Content, select Data from Local File. The import wizard will load and guide you through the import procedure.
- Select the target database.
- Provide the Source File by using the Browse button to select the import file from its saved location. In the File Details, select the worksheet you intend to import and tick the Header row exists. Choose Existing target table and use the Select table button to select the Eralis Job table.
- In the Source File grid, click on the Mapping Menu icon and select Map by Name. This will attempt to map the spreadsheet (Source) to the Eralis Job table (Target). You can see the preview at the bottom.
- Review the Summary screen to confirm the Source and Target information. Click Finish to start the import.
- Once the import has started, you will be able to see the job progress and the status from the bottom of HANA studio.
- Repeat this process for each of the tables that you are importing to.
Comments
0 comments
Article is closed for comments.