**********************
******************
Updated:09/19/2008 Copyright © 1996-2008 Skip Stein
Data Conversion Design Spec: Account Receivable
|
Instructions to Complete Document
Approvals
Purpose (Functional)
Summary (Functional)
General information (Functional/Technical)
Inputs and outputs (Technical).
Data conversion mapping tables (Functional/Technical)
Key objects (Technical)
Detailed procedures (Technical)
Program logic (Technical)
Program application code (Technical)
Program calls (Technical)
Error handling (Technical)
Data conversion testing (Technical)
Backup and recovery (Technical)
Assumptions (Functional/Technical)
Questions/Open Issues (Functional/Technical)
Overview
Requester: | |
Secondary Contact: | |
Date Required: |
|
Functional Area: | All Teams |
Brief Description: | Automatic Account Receivable Conversion into J.D. Edwards |
Instructions to Complete Document
The data conversion requirements document will be completed for all automated data conversions. This document will be initiated by the Technical team and passed to the Functional teams after completing the appropriate sections. Some sections may require both the Functional and Technical teams working together to complete a section. Each section below has a primary owner assigned, however additional comments should be added to the sections by both teams when necessary.
1. Complete Purpose Section
2. Complete Summary Section
3. Complete General Information Section
4. Obtain Project Management Approval
5. Define Mapping Tables
6. Technical Team completes detail technical sections
7. Pass document to Functional Team for final review
8. Start Development Process
Approvals
Signature here approves that the technical team can provide the detailed technical solution.
Request Approval | |||
Signature Name 1: | Date: | ||
Signature Name 2: | Date: | ||
Signature here approves that the technical team can begin the work for this data conversions
Development Approach Approval | |||
Signature Name 1: | Date: | ||
Signature Name 2: | Date: | ||
Signature here approves that the data conversion works properly in the Test/Prototype Environment. This request can now be moved into the CRP Environment.
Test/Prototype Approval | |||
Signature Name 1: | Date: | ||
Signature Name 2: | Date: | ||
Signature here approves that the data conversion works properly in the CRP Environment. This request can now be moved into the Production Environment.
CRP Approval | |||
Signature Name 1: | Date: | ||
Signature Name 2: | Date: | ||
Signature here approves that the data conversion works properly in the Production Environment. This request can now be closed.
Production Approval | |||
Signature Name 1: | Date: | ||
Signature Name 2: | Date: | ||
The purpose of this document is to provide information for the program specifications for the Account Receivable data conversion. This conversion will support the activities to convert all Account Receivable Open transactions.
From System | To System | |
Name | J.D. Edwards | |
Operating System | [System] | Xe |
Database | [Database] | AS/400 |
A conversion program will be created to import the Account Receivable information from the legacy database into the J.D. Edwards Account Receivable Interoperability tables (F03B11Z1) . The permanent OneWorld tables primarily affected by this transaction are the A/R Ledger Table (F03B11).
General information (Functional/Technical)
Volume: (Functional/Technical)
Database 1 | Database 2 | |
A/R Ledger | ||
| ||
Type of data: (Functional/Technical)
All required data from the Legacy System will be extracted and merged from different sources into an access file prior to conversion. All cleansing will transpire prior to the data conversion into J.D. Edwards
Dependencies: (Technical)
Address Book data conversion
Customer Master data Conversion
System Setup
UDC table setup
Key contacts: (Functional/Technical)
Name | Phone # | E-Mail | |
Business Owner | [Phone#] | [E-Mail] | |
IT Owner | [E-Mail] |
Security: (Technical)
This is a one-time data conversion program and as such is designed to be run only by IT. The functional team will be handling the final posting of the batch on the General Ledger.
Reconciliation: (Functional)
The Accounts Receivable team will manage the reconciliation process. This team will be responsible for reconciling the conversion, identifying required corrections to either the mapping table or the converted documents, and signing off on the final conversion
The technical team will provide reports to facilitate this process.
The following tools will be used to complete the reconciliation.
R599001 - Master X-ref Integrity report
R03B110ZI - Invoice Batch Processor report
P03B11Z1 - Batch Invoice Revision (on-line inquiry) to validate the different fields.
P03B4201B: J.D. Edwards Customer Ledger (on-line Inquiry)
R03B413A: J.D. Edwards As-of AR Summary Report
R03B4201B: J.D. Edwards Open A/R Detail Report
Performance considerations: (Technical)
The first part of the conversion where all the legacy data is extracted will run against a copy of the existing system, this will help to speed up performance.
If the rest of the conversion programs run during the daytime, the performance will be slower than if they run during the night or weekend.
Inputs and outputs (Technical).
Legacy file - Legacy System
Spreadsheets - Manual entry values
F590311A - A/R Access table
F590311 - A/R Middleware
F599001 - Master Cross Reference
F03B11Z - A/R Transaction Batch table (Z)
The following files will be used as sources for the data conversion:
F03B11Z1: Accounts Receivable Conversion
The following is a list of fields that have unique requirements. This list does not cover all of the required fields.
Description | Data Item | Length | Program Requirements |
Company | CO | 00099 for Source Division | |
Address Number | AN8 | Program refers to the A/Book cross reference to retrieve a new number for a customer | |
Invoice Number | VINV | ||
Document Number | DOC | Use next number. Leave blank in the program. | |
Document Type | DCT | Program populates RI for regular inf, or RM for Credit memos | |
Payment Terms | PTC | From the Customer Master |
The following tables will be the final output from the conversion:
F03B11 - A/R Ledger table
Data conversion mapping tables (Functional/Technical)
Please refer to the following table for details on the data conversion mapping:
K:\Publications\C. JD Edwards Implementation\Detailed Design Source Division\Technical\Data Conversions.
The following objects are involved in the data conversion process:
Legacy file - Customer/Vendor information from Legacy System
F599001 - Master Cross reference Table
F590311 - A/R Middleware Table
F590311A - A/R Access Table
F03B11Z - A/R Transaction Batch table (Z)
R599001 - Master Cross Reference integrity report
R5990029 - Data Extraction
R590301Z - Custom Conversion program, it writes to the Z table
R03B110Z - A/R- Batch Upload program
P03B11Z1 - A/R Batch Revision
Detailed procedures (Technical)
1 The Legacy data from all the Source Division Systems as well as the supplemental data provided by the Functional users will be merged into an Access database located in the SAMPLE CLIENT server.
2 The access file created will be exported into the AS/400.
3 An extract program will populate the middleware table (F590311) with the information from the legacy system. See Data Conversion Mapping Table for detail on the A/R conversion.
4 A conversion program will be written to populate the cross-reference tables with the information from the middleware tables.
5 Exception reports will be generated against the Cross-reference tables for validation purposes. Any discrepancies between the cross reference and the data dictionary values will be listed on the reports.
6 Functional Users will use the reports to make the necessary corrections to either the legacy data or to the Cross-reference.
7 A conversion program will be written to take the information from the Middleware file and load it into the F03B11Z1 . See Data Conversion Mapping Table for detail item conversion. No General Ledger information will be extracted, this will be a manual entry.
8 The program R03B11Z1 will perform all necessary validations. If errors occur in running of R03B11Z1, they will be printed on the edit report. Or, the error message can be read using the Work Center.
9 J.D. Edwards batch application will convert the batch file F03B11Z1 to the Accounts Receivable Ledger table (F03B11). Set processing option for G/L out of balance for manual entry of G/L. The out of balance will produce an offsetting entry in the Journal that will zero out the journal entry and allow for the manual entry of GL.
10 After the batch has been successfully processed, the field VJEDSP is updated from a 0 to a 1. These records remain in the F03B11 until they are purged.
11 The R03B414A and R03B4201B Aging Reports, which gives detail and summary information of A/R will be one of the tools used to validate the conversion.
12 Enter GL conversion balance manually. It will include AR (reason for out-of-balance flag.)
13 Run purge R03B11Z1P.
R590311E Accounts Receivable Conversion Export into Middleware file
Processing Options
Division Code
R590311 Version
Suppress Report Detail Output.
Data Selection
Set the Division Code = Processing Option Division Code
If the R590311 version processing option is blank, set the value equal to the R590311E version.
Initialize record counters. Count the number of records that are updated, inserted and the total number of records processed.
Process F590311A ACCESS Accounts Receivable file until EOF
Determine if corresponding F590311 record exists
If F590311 record exists
Update the F590311 Middleware Accounts Receivable record
Increment the update record counter.
In record detail section, indicate that the record was updated.
If F590311 record does not exist
Insert the F590311A ACCESS Accounts Receivable record
Increment the insert record counter.
In record detail section, indicate that the record was inserted.
Update the total records counter.
If the Suppress Report Detail Output processing option equals 1
Suppress section write.
End of Loop
Print the updated, inserted and total records processed at the end of the report.
Call R590311 Accounts Receivable Conversion Load Cross Reference batch application.