AP: Payables Invoice Conversion Lean Spec Through FBDI Deloitte

Techno-Functional Specification (Conversion)

CNV-AP-01 Accounts Payable Invoice Conversion.

Document Information

Document Identification	CNV-AP-01
Document Name	Functional Specification (Conversion)
Project Name
Document Author
Document Version	0.1
Document Status	Draft
Date Released

Document Edit History

Version	Date	Additions/Modifications	Prepared/Revised by
1.0		Initial Version

Document Review/Approval History

Date	Document Version	Name	Organization/Title	Comments

Distribution of Final Document

The following people are designated recipients of the final version of this document:

Name	Organization/Title

Table of Contents

1 Introduction 6

1.1 Objective 6

1.2 Dependencies and Prerequisites 6

2 Conversion Description 7

2.1 Assumptions 7

2.2 Proposed Process 7

2.3 Application Setup Requirements 8

3 Conversion Specification 9

3.1 Data Mapping Layout 9

3.2 Cross Reference/Data Transformation 15

3.3 Business Rules 15

3.4 Data Criteria Specifications 16

3.4.1 Data Selection and Filtering Criteria 16

3.4.2 Pre Extract Clean up Criteria 16

3.4.3 Post Extract Clean up Criteria 16

3.4.4 Pre Conversion Cleanup Criteria 16

3.4.5 Post Conversion Acceptance Criteria 17

3.4.6 Pre-Conversion Import Clean-up Criteria 17

3.4.7 Post Conversion Import Acceptance Criteria 17

3.4.8 Non-Converted Data 17

4 Conversion Validation 18

4.1 Data Validation 18

4.2 Post Conversion Steps 19

5 Acceptance Criteria 20

5.1 Acceptance Criteria 20

6 Technical Sections 21

6.1 Steps to download the pre-defined template 21

6.2 Steps to generate the CSV file from the pre-defined template 22

6.3 Steps to upload the CSV file into UCM 24

6.4 Running the standard process (ESS Job) to load the CSV file to the interface table 26

6.5 Steps to execute Standard import programs (ESS Job) to load the data into base table 27

6.6 Steps to generate the output report with the load statistics 28

7 Technical Specifications 29

7.1 Approach 29

7.2 Validation and Filter on Extract 29

7.3 Table and view usage 30

7.4 SQL Statements 31

7.5 Sample template for Extract 31

8 Open and Closed Issues 32

8.1 Open Issues 32

8.2 Closed Issues 32

9 Object Control 33

10 Appendix A – Misc Attachments 34

1 Introduction

1.1 Objective

This functional specification document is intended to provide the functional team all the key information, assumptions, rules, and logic that are required to develop the Accounts Payable (AP) Invoice Conversion to extract, cleanse and convert the relevant data from the R12 system to the ERP Fusion Cloud target system.

The objective of the Invoice conversion is to convert all unpaid invoices and partially paid invoices (retainage invoices) from the R12 legacy system to the ERP Fusion cloud.

1.2 Dependencies and Prerequisites

1. Required configuration / setup would be available for conversion.

a. General Ledger (GL)

i. Chart of account values

b. Accounts Payable (AP)

I. All supplier and supplier sites should be available prior to conversion.

II. All Payment Terms should be set up prior to conversion.

III. All Payment Methods should be set up prior to conversion.

IV. All Pay Groups should be set up prior to conversion. Pay Group of “Retainage” will be set up for retainage invoices.

V. Any invoice header and invoice line DFFs need to be set up prior to conversion.

2. All data translations would be identified and mapped prior to the conversion taking place.

2 Conversion Description

2.1 Assumptions

· All invoices that have not been paid but are scheduled to be paid, will be paid prior to conversion.

· It is assumed that a legacy system data clean-up will be performed to eliminate or reduce errors in the destination.

· Invoices that have been created in error or have been on-hold for an extended period of time will be reviewed and closed.

· All the data will be provided in the prescribed format (a sample template is provided.)

· All unpaid and partially paid open invoices will be converted. For partially paid invoices, only the remaining balance will be converted (e.g. retainage invoices).

· Two separate extracts are needed-one for unpaid invoices and another for retainage invoices.

· Partially paid invoices (Retainage invoices) are recognized as “Fully Paid” in the R12 source system and paid under a dummy bank account called “RETAINAGE WITHHOLDING”. The balance will be converted as unpaid. Dummy payments against these invoices will be ran after the invoice has been converted.

· All payment requests for employees need to be cleared prior to extraction or cutover.

· All the prerequisite setups and configurations will be in place.

· Data file provided by Dekalb will be final.

· Invoice approvals will be turned off during conversion. Invoice approvals will then be turned on after the conversion.

· Once data is loaded, the corresponding GL entries created will be reversed in the GL. A separate GL balance conversion is going to be done by GL team.

· The remaining invoice balance distribution will be split accordingly by percentage of how the invoice was created. If the invoice distribution is 80/20 split, then the retainage balance should be also the same.

2.2 Proposed Process

· Extract data from the legacy system:

o Unpaid invoices will be extracted from the R12 source in the Cloud provided file format

o Separate extract for partially paid invoices (Retainage Invoices) will be extracted from the R12 source in the Cloud provided file format

· Data Transformation: Data transformation according to Transformation Rules will be done so as to get the data extract to be fed to the target system conversion template.

o Invoice data file will be reviewed for readiness to load into Cloud Payables and issues identified (if any) will be communicated back to Payables team for correction

· Load the data:

The following steps will be followed for loading of data to Oracle.

o Download Payables Standard Invoice Import Template from OER

o Populate the template and convert to zip files.

o First upload the Payables Standard Invoice Import template using File Import and Export Task, into the account ‘fin/payables/import’

o After the file is uploaded into cloud, Run Load interface file for Import.

o The import process for Payables Standard Invoice Import Template – Import Payables Invoices

o Once the invoice is imported into invoice interface tables.

o Go to Navigator -> Tools -> Scheduled Processes. Click on “Schedule New Process” button.

o Search for "Import Payables Invoices program" and Submit to load the data into base table

· Error Handling

o All errors will be tracked on a tracker and root causes will be identified.

o Issues will be resolved and conversion template will be created only with the error records.

o Post validation from Business, these validated records will be loaded into the target system.

2.3 Application Setup Requirements

Please refer to section 1.2 Dependencies and Prerequisites for application setup requirement.

3 Conversion Specification

3.1 Data Mapping Layout

Attached below is the standard invoice import template in which the invoice data will be received from R12 source system. Dekalb to populate the invoice data with the guidance of Deloitte

Below is the mapping template table:

All the fields marked with “*” are mandatory in the below table.

Cloud AP field	Source Data Field	Meaning	Transformation Logic	Example
AP_INVOICES_INTERFACES
*Invoice ID			Unique identifier for this invoice. You assign the same value to the invoice's lines in the AP_INVOICE_LINES_INTERFACE table to identify the data as belonging to the same invoice.
*Business Unit		Business Unit. Dekalb only has one BU.		Dekalb County
*Source		Indicates the feeder system from which an invoice is imported.		Conversion
*Invoice Number	Invoice Number	Invoice Number	Pull from R12
*Invoice Amount	Invoice Amount	Invoice Amount	Pull from R12
*Invoice Date	Invoice Date	Invoice Date	Pull from R12
**Supplier Name	Supplier Name	Supplier Name	Pull from R12
**Supplier Number	Supplier Number	Supplier Number	Pull from R12
*Supplier Site	Supplier Site	Supplier Site	Pull from R12
Invoice Currency	Invoice Currency	Invoice Currency	Pull from R12
Payment Currency	Payment Currency	Payment Currency	Pull from R12
Description	Invoice Description	Invoice Description	Pull from R12
*Invoice Type	Invoice Type	Invoice Type	Pull from R12	STANDARD
Legal Entity			Blank
Customer Tax Registration Number		A unique sequence of letters and numbers assigned to a party or party site, by a tax authority, when it is registered and by which the party or party site registration will be identified.	Blank
First-Party Tax Registration Number		A unique sequence of letters and numbers assigned to a first party by a tax authority when it is registered, and by which the first party registration is identified.	Blank
Supplier Tax Registration Number		A unique sequence of letters and numbers assigned to a supplier or supplier site by a tax authority when it is registered, and by which the supplier or supplier site registration is identified.	Blank
*Payment Terms	Payment Terms	Payment Terms	Pull from R12
Terms Date	Terms Date	Terms Date	Pull from R12
Goods Received Date	Goods Received Date	Goods Received Date	Pull from R12
Invoice Received Date	Invoice Received Date	Invoice Received Date	Pull from R12
Accounting Date	Accounting Date	Accounting Date	Pull from R12
Payment Method	Payment Method	Payment Method	Pull from R12
Pay Group	Pay Group	Pay Group	Pull from R12 for unpaid invoices For Retainage invoice extracts, default PAY GROUP to RETAINAGE
Pay Alone	Pay Alone	Indicates whether an invoice should be paid with its own payment document, without including other invoices for the supplier.	Pull from R12
Discountable Amount		Invoice amount applicable to discount.	Blank
Prepayment Number		Invoice number of the prepayment.	Blank
Prepayment Line Number		Line on a prepayment that can be applied to an invoice.	Blank
Prepayment Application Amount		The amount of prepayment applied to the invoice.	Blank
Invoice Includes Prepayment			Blank
Conversion Rate Type			Blank
Conversion Date			Blank
Conversion Rate			Blank
Liability Combination	Liability Account	Liability Account	Pull from R12
Document Category Code		Value used to classify documents for document sequencing purposes.	Blank
Voucher Number		A unique internal identifier assigned to a document. Required only if the document category code has an assignment type of Manual.	Blank
Withholding Tax Group		Group used to apply multiple withholding taxes to an invoice line.	Blank
Calculate Tax During Import			Blank
Add Tax To Invoice Amount			Blank
Attribute Category
Attribute 1	Hold for Pickup	Segment of the descriptive flexfield used to hold user-defined information for the current table.	Hold for Pickup DFF-Pull value from R12
Attribute 2
Attribute 3
Attribute 4
Attribute Number 1
Attribute Number 2

Cloud AP field	Source Data Field	Meaning	Transformation Logic	Example
AP_INVOICES_LINES_INTERFACES
*Invoice ID		Unique identifier for this invoice. You assign the same value to these invoice's lines in the AP_INVOICE_LINES_INTERFACE table to identify the data as belonging to the same invoice
Line Number	Line Number	Line Number	Pull from R12
*Line Type	Line Type	Line Type	Pull from R12
*Amount	Amount	Amount	Pull from R12
Invoice Quantity	Invoice Quantity	Invoice Quantity	Pull from R12
Unit Price	Unit Price	Unit Price	Pull from R12
UOM	UOM	UOM	Pull from R12
Description	Description	Description	Pull from R12
PO Number			Blank/ No PO reference conversion on invoices
PO Line Number			Blank/ No PO reference conversion on invoices
Item Description			Blank/ No PO reference conversion on invoices
PO Release Number			Blank/ No PO reference conversion on invoices
Purchasing Category			Blank/ No PO reference conversion on invoices
Receipt Number			Blank/ No PO reference conversion on invoices
Receipt Line Number			Blank/ No PO reference conversion on invoices
Distribution Combination		Account combination used for creating invoice distribution.	Pull from R12
Distribution Set			Blank
Accounting Date	Accounting Date	Accounting Date	Pull from R12
Income Tax Type		The 1099 type of the payments made to a supplier. Some of the valid value are MISC1, MISC2, MISC3, MISC5, GOV 1, GOV 6, GOV 6A, GOV 7, and INT 1.	Pull from R12
Income Tax Region		Tax regions such as states and territories of the United States. Income tax regions are used for United States 1099 combined filing reporting.	Blank
Prorate Across All Item Lines		Indicates that the freight or miscellaneous line is prorated across all item lines on an invoice. The valid values are Y, N, and null. If left null it is taken as Y.	Pull from R12
Line Group Number		A number assigned to the invoice lines for the purpose of prorating charges to a group of lines with the identical line group number value.
Track as Asset		Indicates that the invoice line should be transferred to Oracle Fusion Assets. The valid values are Y, N, and null. If left null, it will be derived from the account type of the natural account segment of code combination associated to the line.	Should be No as FA is converting assets as well	N
Attribute Category		User-defined descriptive flexfield context sensitive segments configured to capture additional information on the invoice.
Attribute 1		Segment of the descriptive flexfield used to hold user-defined information for the current table. Enter a value if this field is enabled.	PO NUMBER
Attribute 2
Attribute 3
Attribute 4
Attribute Number 1		Segment of the descriptive flexfield used to hold user-defined information for the current table. Enter a value if this field is enabled.
Attribute Number 2
Expenditure Item Date			Not converting POETA information on invoices. Should be Blank.
Project Number			Not converting POETA information on invoices. Should be Blank.
Task Number			Not converting POETA information on invoices. Should be Blank.
Expenditure Type			Not converting POETA information on invoices. Should be Blank.
Expenditure Organization			Not converting POETA information on invoices. Should be Blank.

3.2 Cross Reference/Data Transformation

Not applicable.

3.3 Business Rules

Below provides the business rules that will drive input data conversion.

· All unpaid and partially paid open invoices will be converted. For partially paid invoices, only the remaining balance/installments will be converted (e.g. for retainage invoices).

· Only balance/installments of the unpaid invoices/partially paid invoices are going to be converted. The remaining invoice balance distribution will be split accordingly by percentage of how the invoice was created. If the invoice distribution is 80/20 split, then the retainage balance should be also the same

· Pay Group called RETAINAGE will be used for Retainage-related extracts. This way it is easy to identify payments for retainage to be made.

· PO line information reference will not be converted for the invoice as we are converting only open Purchase order balances. Once it has been invoiced and matched, that PO line will be closed and will not be carried over. PO information will be captured however in a descriptive flexfield.

· POETA-related information on invoices will not be converted. The GL account distribution will be converted.

* Need further discussion on extract

3.4 Data Criteria Specifications

3.4.1 Data Selection and Filtering Criteria

· All unpaid and partially paid open invoices will be converted. For partially paid invoices, only the remaining balance/installments will be converted.

· For retainage invoices, only the unpaid portion of the retainage invoice will be brought over. City’s current retainage business process is to run payments out of a Dummy retainage account, these retainage invoices will be selected based on the “paid invoices” out of the retainage bank account. They will be converted as unpaid invoice.

· For open invoices to be converted, if the invoice is on hold in R12, the Oracle Payables (invoice/invoice lines) FBDI template has no mapping for invoices on hold. AP will need to clean-up invoices on hold prior to conversion otherwise they will come in without the holds.

3.4.2 Pre Extract Clean up Criteria

Invoices with any holds are taken care of. The holds need to be fixed or the invoice needs to be cancelled. All the Payables period-end activities also need to be completed prior to extraction.

3.4.3 Post Extract Clean up Criteria

N/A

3.4.4 Pre Conversion Cleanup Criteria

N/A

3.4.5 Post Conversion Acceptance Criteria

N/A

3.4.6 Pre-Conversion Import Clean-up Criteria

N/A

3.4.7 Post Conversion Import Acceptance Criteria

The business will need to verify imported invoices.

3.4.8 Non-Converted Data

The City will have access to their R12 instance for transactions not converted (e.g. paid invoices.)

4 Conversion Validation

4.1 Data Validation

The following are Data Conversion Controls for audit purposes:

A formal process should exist to ensure data completeness between databases during conversion processes:

1. Row counts and control totals (i.e. check sum, hash total, etc.) are compared between the source database and the staging database to verify that the eligible records were extracted completely and accurately from the source database to the staging database. Extracted dollar amounts vs converted dollar amount are also compared.

2. Row counts and control totals (i.e. check sum, hash total, etc.) are compared between the staging database and the target database to verify that the eligible records were extracted completely and accurately from the staging database to the target database. Extracted dollar amounts vs converted dollar amount are also compared.

3. All issues identified during a data conversion are identified and resolved:

Records that are not moved to production, such as non-selected and/or error/exception records are accumulated in a tracking file and retained by IT during the conversion process. As the conversion process completes, the tracking file is communicated by IT to the Business Owner and a resolution of these records is determined and documented.

4. Users perform testing to ensure the accuracy of the data conversion:

Testing of the converted records in both the test environment and the production environment is performed to verify the accuracy of the conversion. Accuracy will be performed by validating specific values for a sample from the population of converted records.

Some reports users can run to validate data:

o Payables Invoice Aging Report

o Payables Invoice Register

o Accounts Payable Trial Balance

4.2 Post Conversion Steps

1. Add a step to run invoice validation. If there are any invoice holds, they have to be manually put on hold prior to validation.

2. Once retainage invoices are converted, run dummy payments on these invoices.

3. Run the Create Accounting Process to transfer invoice accounting to the Oracle General Ledger.

4. Post and reverse the journals in GL as they are already included in the General Ledger balances from the General Ledger conversion. This will be for both invoices and dummy payments.

5. Run the Accounts Payable Trial Balance report to confirm conversion results.

6. Check sums, row counts, of data converted from R12 to Oracle will be done to validate data from Oracle to Oracle’s test instance.

7. Check sums, row counts, of data converted from Oracle’s staging instance will be done to validate data from to Oracle’s staging instance to Oracle production.

8. Reconcile sample invoices and invoice lines from the original source system (R12) to target system

5 Acceptance Criteria

5.1 Acceptance Criteria

100% of validated data will be acceptable for conversion:

1. Total invoices converted from Oracle should equal total in Oracle.

2. Account balances converted from Oracle should equal total balances in Oracle.

3. Records that are not moved to production, such as non-selected and/or error/exception records are accumulated in a tracking file and retained by IT during the conversion process. As the conversion process completes, the tracking file is communicated by IT to the Business Owner and a resolution of these records is determined and documented.

4. Error handling is described in Section 2.2.

6 Technical Sections

6.1 Steps to download the pre-defined template

1. Login to OER as Guest.

2. Search for the seeded template for the invoice entity as shown in the below screen shot by filling search criteria as follows i.e.,

a. Type: File Based Data Import.

b. Version: Current Version of the cloud ERP.

c. Product Family: Select the family of the entity for which you are trying to import the data.

3. Now click on the XLSM template shown in the Details tab & save the template to your local desktop.

6. Steps to generate the CSV file from the pre-defined template

Data mapping to extract data from source system is provided in the section 3.1.1 of this document.

1. Open the Template that was downloaded in step 6.1.

2. Fill the data in the required columns as shown in the below screen shot based and save the template. All the required columns are prefixed with “ * ” and you can see that in screen shot below.

3. Fill the details for Invoice Headers and Invoice Lines.

4. Now go to “Instructions and CSV Generation” tab in the template and click on “Generate CSV” Button as shown in the below screen shot.

5. Then save the zip files & csv files on your local Desktop when prompted.

Note: Similarly, the data for Supplier Site, Supplier Addresses, Site Assignments, site contacts and supplier banks need to be filled and the zip files needs to be saved to the local desktop.

6.3 Steps to upload the CSV file into UCM

1. Login to the application.

2. Go to Navigator -> Tools -> File Import and Export.

3. Click on upload Icon as shown in the screen shot:

a. Provide the Zip file location that was generated and saved on your local desktop in step 6.3 for “File” Column & for “Account” Column based on the entity your converting selects corresponding value from the list available as shown in the screen shot.

For converting Invoice Data, we need to select “fin/payables/import” from available options for Account.

b. Click on “Save and Close” Button. Data is loaded to UCM server now.

6.4 Running the standard process (ESS Job) to load the CSV file to the interface table

1. Login to Application.

2. Go to Navigator -> Tools -> Scheduled Processes. Click on “Schedule New Process” button.

3. Search for “Load Interface file for Import” ESS program and click on “Ok” button.

4. Provide suitable values for the parameters:

Import Process – Based on the entity data being converted select the suitable value from the List.

Data File – Select the Data file that was uploaded in step 6.4.

For loadings Invoice data, we need to select “Import Payables Invoices” from list provided for “Import process” column.

5. Click on Submit.

6. Refresh the Page and make sure all the Load interface programs are completed successfully.

Please have a look at the below screen shot where the programs that have got kicked off after submit button is clicked have been highlighted for your reference.

The log file of the “Load File to Interface” ESS job will let you know how many records have been successfully loaded to the interface.

6.5 Steps to execute Standard import programs (ESS Job) to load the data into base table

1. Login to Application.

2. Go to Navigator -> Tools -> Scheduled Processes. Click on “Schedule New Process” button.

3. Search for the Standard Import ESS job based on the entity data for which conversion is being executed.

4. For transferring the Invoice Data from interface to Base tables, we need to select “Import Payables Invoices” from the list.

5. Provide values for the required Parameters and click on “Submit” button.

6. Once the ESS job is successfully executed the output will tell us the no of records successfully imported and rejected with error message.

6.6 Steps to generate the output report with the load statistics

A custom OTBI/BI report has to be developed with the required columns to check whether the records imported successfully through step 6.6 have been populated with correct data (or) not.

7 Technical Specifications

7.1 Approach

Details of the invoices and invoice lines will be fetched based on the criteria and condition mentioned in point 3.4.1 above.

7.2 Validation and Filter on Extract

· Unpaid invoices will be considered in conversion.

Eg:-

SELECT aia.invoice_id

FROM ap_invoices_all aia

WHERE aia.payment_status_flag = 'N'

AND aia.cancelled_date IS NULL

· Invoices which are fully paid under dummy account “RETAINAGE WITHHOLDING” needs to extracted.

Eg:-

SELECT ai.invoice_id

FROM ap_invoice_payments_all AIP,

ap_invoices_all AI,

ap_payment_schedules_all APS,

ap_checks_all AC

WHERE AIP.invoice_id = AI.invoice_id

AND APS.invoice_id = AIP.invoice_id

AND AIP.check_id = AC.check_id

AND APS.payment_num = AIP.payment_num

AND ac.bank_account_name =

'RETAINAGE WITHHOLDING'

AND ai.payment_status_flag = 'Y'

· Partially paid invoices will be considered in conversion.

Eg:-

SELECT aia.invoice_id

FROM ap_invoices_all aia, ap_payment_schedules_all aps

WHERE aia.payment_status_flag = 'P'

AND aia.invoice_id = aps.invoice_id

AND ( NVL (aps.amount_remaining, 0)

* NVL (aia.exchange_rate, 1)) != 0

AND aia.cancelled_date IS NULL

7.3 Table and view usage

Table/View Name	Owner	Select	Insert	Update	Delete
ap_invoices_all	APPS	Y	N	N	N
ap_payment_schedules_all	APPS	Y	N	N	N
hr_locations	APPS	Y	N	N	N
po_vendors	APPS	Y	N	N	N
ap_awt_groups	APPS	Y	N	N	N
po_vendor_sites_all	APPS	Y	N	N	N
gl_code_combinations_kfv	APPS	Y	N	N	N
ap_terms	APPS	Y	N	N	N
org_organization_definitions	APPS	Y	N	N	N
po_headers_all	APPS	Y	N	N	N
po_lines_all	APPS	Y	N	N	N
po_line_locations_all	APPS	Y	N	N	N
po_distributions_all	APPS	Y	N	N	N
po_releases_all	APPS	Y	N	N	N
pa_projects_all	APPS	Y	N	N	N
pa_tasks	APPS	Y	N	N	N
ap_invoice_distributions_all	APPS	Y	N	N	N