In this step you will create a transformation map that will do the following:

• Select records from the Employee file (XEMPLOYEE) based on the employee codes from the file iiiTUTEMPNO.csv. Your processing sequence will receive the CSV file as an email attachment

• Write the selected employee information to the file iiiTUTEMPDET.xml

1. In the Navigator, select Definitions, Transformation maps

2. Use the  button on the toolbar to create a new Transformation map with the following details:

   ID	iiiTUTMAP03
   Description	Employee details XML from CSV selection list.
   Map Type	Unspecified Type
   Restartable	Yes
   Status	Active

3. Save the transformation map.
4. Click the  button to open the MapForce mapping tool.
   You may get a warning message stating that LANSA Composer supports a subset of MapForce mapping functionality.
   Click Continue to proceed to MapForce. You will see an empty map.
5. To insert the Request CSV file, choose Insert Text File using the Insert menu. Then select Use simple processing for standard CSV ... and press the Continue button.
   
   

6. In the Text import / export window, click the Input file button. Use this dialog to navigate to select the file 
   

   \\<SRV400>\ifs\LANSA_Composer_licpgmlib\lic\Tutorial\iiiTUTEMPNO.csv

    which you created earlier.

7. Check the First row contains field names checkbox.
8. In the Text enclosed in radio button group, select Not. Your selection will look like this.
   
9. Press OK. Mapforce will display the file iiiTUTEMPNO.csv visualization.
   
10. Insert the Database element using the Insert Menu.
    
11. Use the existing ODBC connection which you created in an earlier module.
    1. Login using your workshop IBM i User ID and Password. You may be prompted for a number of logins.
    2. Upon successful connection to the database, you will get a Select Tables, Views to Insert dialog.

    3. Expand the list of libraries, schemas, or collections using the drop-down menu next to the small arrow.  After selecting the library, schema or collection containing the required file (for example, LICLICLIB or LC_DTA on a Windows server), locate the file XEMPLOYEE and select it using the check box.

       The library name should be the same one used in the database connection string used when creating the Database configuration previously.

       
       

       
       

       jdbc:as400://<SRV400>/<LIBRARY>;naming=sql;errors=full;date format=iso; true;translate binary=true

       
       

       
12. Click OK to return to the main window. You will see the XEMPLOYEE table Visualization displayed.
13. From the Insert menu, insert the XML Schema/File.
    
    1. Use the Insert menu, XML Schema/File ... navigate to the . . . .\Tutorials folder and select schema file iiiTUTEMPDET.dtd file.
    2. Use the Browse button to select the XML file iiiTUTEMPDET.xml
       
       Your XML file structure will be displayed in the map.c .  Rearrange your mapping screen to look like this.
       The map functionality required will select Employee Details from the database only when the Employee Number is listed in the CSV file.
14. From the toolbar or Insert menu, insert an SQL-WHERE Condition.
15. Connect the XEMPLOYEE table to the SQL-WHERE table input. This will open SQL-WHERE properties window.

16. In SQL-WHERE properties window, type in the select criteria:
    On Windows
    

    xEmployeeIdentification = :inEmpNo

    
    

    On IBM i
    

    xEmployID = :inEmpNo

    
    Press OK
    

17. Connect the EmployeeNumber output from CSV file to the input inEmpNo of SQL-WHERE condition.
18. Connect the resulting output of SQL-WHERE to the Employee input element of the XML file.
    Your map should look like the following:
    
19. Finally, connect the data fields from the database outputs to the corresponding XML file inputs.
    
    Your final map will look like this:
    
20. Save the map and close MapForce.

21. On the Details tab, click Prepare to generate the transformation map code.
    Click Prepare on the Prepare Transformation Map dialog.
    Remember to look for an IBM i Signon which may be hidden by other windows.

    Use Windows Task Manager to switch to the IBM i Signon dialog.

    
    

    Wait for the completion message and Close the Prepare Transformation Map dialog.