B Using the Batch Import Process to Upload Information for the PCard System

This appendix contains these topics:

• Section B.1, "Creating the Template for the Z File,"

• Section B.2, "Exporting Address Book Information,"

• Section B.3, "Updating the Z-File Template (Spreadsheet),"

• Section B.4, "Uploading the Z-File,"

• Section B.5, "Processing Transactions to the PCard File,"

• Section B.6, "Uploading Changes to Employee Credit Card and Merchant Information Files."

The batch import process enables you to import data from a .csv file directly into a JD Edwards World database file. To preserve system integrity, the system controls which files it allows for batch import. These files are usually Z files or EDI files; a file that you process to validate the information in a batch mode before updating other files in the database.

In the PCard system, you can use the batch import process to upload information into the following files instead of entering it manually:

• Employee Card Information (F01P01)

• Merchant Information (F01P02)

• Purchase Card Bank Transmissions (F01PBKZ)

  Although you can use the batch import process to upload transactions to F01PBKZ, you typically use it for testing purposes only.

To use the batch import process you download a template (spreadsheet) that corresponds to the file you want to update and save it as a .csv or .xml file. The template contains the field headers, field names, and descriptions to identify the columns that you complete. For employee card and merchant information, you provide the spreadsheet to the bank for them to update it. Once you receive the updated spreadsheet, you the import process to upload the appropriate Z file (F01P01Z for employee card information and F01P02Z for merchants). After you successfully upload information to the Z file, you run the corresponding batch import program (Purchase Card Revisions - P01P01Z or Merchant Revisions - P01P02Z) to process the records to the PCard files (F01P01 and F01P02, respectively). Additionally, you can use the import process to update changes to existing records in F01P01 or F01P02.

To import employee credit card information, you must provide the address book number and alpha name of the employee. Similarly, if you track merchants, you must provide the address book number and alpha name of the merchant. You can use the batch export process to create a spreadsheet that provides the address book information that you need to include on the spreadsheet.

Before You Begin

To use the batch import process, you must set up an IFS (Integrated File System) directory and map it to your PC; otherwise, you must create a custom program to update the desired Z files directly.

For information about setting up the system for import and export, see Work with Import/Export in the JD Edwards World Technical Tools Guide.

B.1 Creating the Template for the Z File

To use the batch import process, you must populate an Excel spreadsheet and save it as a .csv or .xml file. You can download the templates to use, as well as review the availability of templates, by doing the following:

1. From menu G00PCIE (Import/Export), choose Batch Import-Template Create (P00CRTCSV).

2. On Batch Import-Template Create, press F1 (Field Sensitive Help) in the Batch Import File field.

   The system displays all templates available. Enter F01P in the Skip To Code field to display the PCard templates that are available.

3. Use Option 4 to select the appropriate template for the process:

   • Employee Purchase Card Z File (F01P01Z)

   • PCard Merchant Z File (F01P02Z)

   • Purchase Card - Bank Transmissions (F01PBKZ)

4. In the Import Export File field, enter the name that you want to assign to the template that the system creates followed by .csv or .xml (for example, ccinfo.csv or merchant.xml).

5. If you want to save the spreadsheet as an .xml file enter Y in the Excel XML Y/N field; otherwise, leave this field blank to save the spreadsheet as a .csv file.

6. Enter the IFS path in the corresponding field or press F4 (Search IFS Path) to select the IFS path from the list that appears.

7. Press Enter.

8. Press F6 (Create Template).

The system stores the template on your PC in the directory that is mapped to your IFS directory.

B.2 Exporting Address Book Information

The template (spreadsheet) that you use for importing the data into F01P01Z and F01P02Z includes the fields for address book number. Instead of manually populating these fields, you can export the Reports by Address report (P01301) using the One Line per Address format, and then copy and paste the address book number and alpha name into the fields of the template you create.

B.2.1 Before You Begin

Use a category code, search type, or a value in some other field to identify the employee and supplier (merchant) address book records that you want to export.

To export the Address Book Report

1. From menu G0121 (Periodic Processes), choose Reports by Address (P01301).

2. Review the runtime message and press F6 (Execute).

3. On the Reports by Address version list, copy the XJDE0001 version.

4. After you copy the version, use Option 2 (Change Version) to change it and select Additional Parameters from the DREAM Writer Menu window.

5. On Additional Parameters, press F6 (Batch Export Parameters).

6. On Spooled File Export Parms, complete the fields according to the print screen using your import/export file name and IFS path:

   Figure B-1 Spooled File Export Parameters screen

   
   Description of "Figure B-1 Spooled File Export Parameters screen"
   
   

7. Press Enter and then press F3 twice to return to the Reports by Address versions list screen.

8. Use Option 2 to review the Processing Options and Data Selection.

9. On Processing Options Revisions, verify that the processing option (Select the format that you wish to print) is set to 2.

10. On Data Selection, set up the criteria to print a report of the suppliers (merchants) that you use.

Run the report to export the data to the spreadsheet.

B.3 Updating the Z-File Template (Spreadsheet)

This task is specific to completing the spreadsheets to process information to the Employee Purchase Card Z File (F01P01Z) and PCard Merchant Z File (P01P02Z). The information for completing the template for the Purchase Card Bank Transmissions file (F01PBKZ) is located in: Appendix C - Field Specifications for the Purchase Card - Bank Transmissions File (F01PBKZ).

1. Access the template that you created for the import process (for example, ccinfo.csv or merchantinfo.xml), and cut and paste the values for the Address Book Number and Alpha Name from the address book report spreadsheet to the template if you are tracking merchants.

2. Complete the following fields for each address book record that you copied. You can use the same value for ZZEDUS, ZZEDBT, and ZZEDTC; however, the value for Transaction Number (EDTN) must be unique.

   • ZZEDUS - Enter a user ID

   • ZZEDBT - Enter a batch number

   • ZZEDTN - Enter a unique transaction number

   • ZZEDTC - Enter A

3. If you are importing employee card information, complete the following required fields:

   • ZZBUE - Enter the employee's business unit. If you leave this field blank, the system uses the value from the Responsible Business Unit field (MCU) from the employee's address book record (F0101).

   • ZZAN8 - Enter the address book number of the employee.

   • ZZCARD - Enter the credit card type, such as AMEX or VISA. The credit card type you specify must exist in UDC 00/CA (Credit Card Type).

   • ZZBANK - Enter the address book number of the bank.

4. If you are importing merchant information, complete the following required fields:

   • ZZCARD - Enter the credit card type, such as AMEX or VISA. The credit card type you specify must exist in UDC 00/CA (Credit Card Type).

   • ZZAN8 - If you track merchants, enter the address book number of the merchant.

   • ZZALPH - Enter the alpha name of the merchant.

   • ZZMID# - Enter the bank's merchant ID. You can coordinate with the bank to update the Merchant ID by sending them the spreadsheet. They can update the merchant ID based on the name or any other information you can provide to them to identify the merchant.

   All other fields are optional or are provided by your bank. You can delete columns from the spreadsheet if you are not using them. You can reorder the columns any way you want, but the first row of data must be on line 5.

   This is an example of how you might update the spreadsheet for Merchant Information:

   Figure B-2 Merchant Information Spreadsheet

   
   Description of "Figure B-2 Merchant Information Spreadsheet"
   
   

5. After you update the spreadsheet, you must save it. When you click Save, the system displays this message:

   Figure B-3 Microsoft Excel Message on Save screen

   
   Description of "Figure B-3 Microsoft Excel Message on Save screen"
   
   

6. Click Yes.

   When you exit the spreadsheet, you might receive the following message, depending on your Excel File settings:

   Figure B-4 Microsoft Excel Message on Exit screen

   
   Description of "Figure B-4 Microsoft Excel Message on Exit screen"
   
   

7. Click No.

   The CSV format does not retain cell formatting; therefore, if you choose Yes, the system prompts you to save the spreadsheet in an .xls or alternate format.

After you complete the spreadsheet, you can send it to your bank to update:

• The credit card information for each employee.

• The merchant ID and merchant category code fields for each supplier.

B.4 Uploading the Z-File

When you receive the spreadsheet from the bank or if you are ready to upload the completed information, you run the Batch Import-CSV or Excel XML program (P00BUL) to generate the records in the F01P02Z file.

1. From menu G00PCIE (Import/Export), choose Batch Import-CSV or Excel XML (P00BUL).

2. On Batch Import-CSV or Excel XML, complete the following fields:

   • Action Code - Enter A.

   • Batch Import File - Enter F01P01Z, F01P02Z, or F01PBKZ.

   • Import Name - Enter a name to assign to the format.

   • Import Export File - Enter the name of the template (spreadsheet) that you updated (for example, ccinfo.csv or merchant.csv).

   • Excel XML (Y/N)

   • IFS Path

3. Press Enter.

4. Reinquire on the Batch Import File and Import Name.

5. Press F6 (Continue).

   The system uploads the information from the spreadsheet to the Z-File.

B.5 Processing Transactions to the PCard File

After you upload the information from the spreadsheet to Z-File, you must process the information to the Employee Card Information (F01P01) or the Merchant Information (F01P02) file using the corresponding program:

• Purchase Card Revisions (P01P01Z)

• Merchant Revisions (P01P02Z)

Note:

After you upload F01PBKZ, you are ready to process PCard transactions and statements; you do not need to run an additional program.

The programs do not provide a proof mode; however, if the system encounters errors, it does not process the transaction.

Review the processing options for each program.

From menu G01P01Z (Purchase Card Z File Processes), choose Purchase Card Revisions (P01P01Z) or Merchant Revisions (P01P02Z).

B.6 Uploading Changes to Employee Credit Card and Merchant Information Files

Instead of manually changing merchant or employee credit card information, you can use the batch import process to upload changes. The only difference between adding new records and changing existing records is that you use the value C, instead of A, in the Transaction Action field (EDTC) on the spreadsheet (template).

If you decide to process updates to the Employee Card Information file (F01P01), the Employee/Card Revisions program (P01P01) includes the processing option for Z-File Processing that you use to specify a special character to indicate that you want to clear a field. For example, suppose you use category code 01 to track the employee's job and the employee moves to another position that no longer requires use of a credit card. If you attempt to clear a value using the Z-file, the system does not distinguish that you are changing the value of the field. You assign a special character to the fields that you want to clear on the F01P01Z spreadsheet and then enter that character in the processing option for Z-File Processing. The system locates all fields with the special character that you specify in the processing option and clears the value in the field.