Oracle JDE Order-to-Cash app template

• Extraction
  • System configuration
  • Setting up the source connection in CData Sync
  • Setting up the destination connection in CData Sync
  • Creating the extraction job in CData Sync
• Input fields
  • Input types
  • Input tables & fields
• Configuring transformations
  • Seed files
  • Dbt variables
• Design specifications
  • Entities
  • Activities
  • Design details

Extraction

This app template uses Oracle JD Edwards 8.0 or higher as source system, further referenced in this documentation as Oracle JDE.

Extraction for the system is done using CData Sync. To set-up the extraction, make sure you have a valid license for CData Sync and have installed CData Sync.

System configuration

To be able to retrieve the required data from Oracle JDE, the modules Sales Management and Accounts Receivable are mandatory. Other tables could be used for master data purposes.

To connect via CData, the user establishing the connection must have appropriate read permissions on each of the objects which are being extracted. All fields included in the input tables must be visible for the user extracting the data.

Setting up the source connection in CData Sync

• In CData - Connections, add a new source connection for Oracle. If this option is not in the list, click + Add More, search for Oracle and click Download & install
• Name the connection Oracle_JDE_Connection.
• Fill out the fields for Server, Service Name, User, Password with the correct values.
• Go to the Advanced tab and set Port to the correct value.
• Create and test the connection.

Setting up the destination connection in CData Sync

To set up the destination connection, follow the steps as defined in Create a destination connection (Automation Cloud). Note: If you are using Automation Suite, make sure to select AUTOMATION SUITE as the DELIVERY option and select the correct version of Automation Suite you are using.

Creating the extraction job in CData Sync

To create the Job in CData make sure to follow the steps below.

• In CData - Jobs, create a new job.
• Select Oracle_JDE_Connection as source and select the destination connections created for the Oracle JDE Order-to-Cash app template and create the job.

Advanced job settings

In the Advanced tab in the Job Settings panel, define the following settings:

• Select the Alter Schema option.
• Select the Drop Table option.
• If you are using Automation Suite, add the Destination Schema. Fill out the value you retrieved when you created the destination connection.

Setting up environment variables in the extraction job

Important: Make sure you edit the Pre-job Event.

CData Sync allows the use of environment variables in order to drive specific extraction logic in each query.

In order to setup the environment variables:

<!-- Modify environment variables here. -->
<!-- Variable start_extraction_date and end_extraction_date must be populated.--> 
<!-- The following values are just an example (start being = 01-01-1900 and end being = 01-01-2300).--> 
<api:set attr="out.env:start_extraction_date"  value= 000001 />
<api:set attr="out.env:end_extraction_date"  value= 400001 />
<api:push item="out" />

By default, end_extraction_date will default to today's date. start_extraction_date must always be populated. The dates should be formatted in julian format, as this is the format used in JDE internally. Use the following to calculate the correct value:

To transform 31/12/2021 into a JDE julian format (CYYDDD is an integer) C = First 2 digits from the year minus the 19 (JDE uses 01-01-1900 as base to create date fields), in our example: C = 20 - 19 = 1 YY = The last 2 digits of the year, in our example: YY = 21 DDD = Is the day of the year (1-365 range, except for leap years that is 1-366), in our example: DDD = 365 So 31/12/2021 in JDE format is 121365.

Setting up the data ingestion call

After the data has been successfully loaded, the Process Mining platform needs to start the data ingestion process. This is done by calling the End of Upload API. In order to set this up, follow the steps below:

<api:set attr="http.url" value="END_OF_UPLOAD_API"/> 
<!-- <api:set attr="http.verbosity" value="5"/> -->
<!-- <api:set attr="http.logfile" value="D:/mydir/cdata_log.txt"/> -->
<api:call op="httpPost" in="http"/>

Table Replication

Once the job is correctly setup, click on Add Custom Query under the Tasks tab and paste the following queries (each query needs to maintain the semicolon at the end). Make sure you save all changes.

REPLICATE [F0005] SELECT [DRKY] AS [drky], [DRRT] AS [drrt], [DRSY] AS [drsy], [DRDL01] AS [drdl01] FROM [JDECTL920].[F0005];
REPLICATE [F0006] SELECT [MCMCU] AS [mcmcu], [MCDL01] AS [mcdl01], [MCRP01] AS [mcrp01] FROM [JDEDATA920].[F0006];
REPLICATE [F0010] SELECT [CCCO] AS [ccco], [CCCRCD] AS [cccrcd], [CCNAME] AS [ccname] FROM [JDEDATA920].[F0010];
REPLICATE [F0014] SELECT [PNPTC] AS [pnptc], [PNPTD] AS [pnptd] FROM [JDEDATA920].[F0014];
REPLICATE [F0015] SELECT [CXAN8] AS [cxan8], [CXCRCD] AS [cxcrcd], [CXCRDC] AS [cxcrdc], [CXRTTYP] AS [cxrttyp], [CXEFT] AS [cxeft], [CXCRR] AS [cxcrr] FROM [JDEDATA920].[F0015] WHERE ([CXEFT] >= '{env:start_extraction_date}') AND ([CXEFT] <= '{env:end_extraction_date}');
REPLICATE [F0092] SELECT [ULAN8] AS [ulan8], [ULUSER] AS [uluser] FROM [JDESY920].[F0092];
REPLICATE [F0101] SELECT [ABAN8] AS [aban8], [ABALPH] AS [abalph], [ABMCU] AS [abmcu], [ABAT1] as [abat1] FROM [JDEDATA920].[F0101];
REPLICATE [F0116] SELECT [ALAN8] AS [alan8], [ALEFTB] AS [aleftb], [ALCTY1] AS [alcty1], [ALCOUN] AS [alcoun], [ALADDS] AS [aladds], [ALCTR] AS [alctr] FROM [JDEDATA920].[F0116];
REPLICATE [F03012] SELECT [AIAN8] AS [aian8], [AICO] AS [aico], [AICPGP] AS [aicpgp], [AIAC11] AS [aiac11] FROM [JDEDATA920].[F03012];
REPLICATE [F4101] SELECT [IMDSC1] AS [imdsc1], [IMLITM] AS [imlitm] FROM [JDEDATA920].[F4101];
REPLICATE [F40203] SELECT [FSDCTO] AS [fsdcto], [FSTRTY] AS [fstrty], [FSSTDS] AS [fsstds], [FSLNTY] AS [fslnty] FROM [JDEDATA920].[F40203];
REPLICATE [F40205] SELECT [LFLNTY] AS [lflnty], [LFLNDS] AS [lflnds] FROM [JDEDATA920].[F40205];
REPLICATE [F03b11] SELECT [RPKCO] AS [rpkco],  [RPDOC] AS [rpdoc], [RPDCT] AS [rpdct], [RPDIVJ] AS [rpdivj], [RPDGJ] AS [rpdgj], [RPVDGJ] AS [rpvdgj], [RPPOST] AS [rppost], [RPTORG] AS [rptorg], [RPDICJ] AS [rpdicj], [RPSFX] AS [rpsfx], [RPAAP] AS [rpaap] FROM [JDEDATA920].[F03B11] WHERE ([RPUPMJ] >= '{env:start_extraction_date}') AND ([RPUPMJ] <= '{env:end_extraction_date}');
REPLICATE [F03b14] SELECT [RZPYID] AS [rzpyid], [RZKCO] AS [rzkco], [RZDOC] AS [rzdoc], [RZDCT] AS [rzdct], [RZDCTG] AS [rzdctg], [RZDICJ] AS [rzdicj],[RZUPMJ] AS [rzupmj], [RZTORG] AS [rztorg], [RZRC5] AS [rzrc5] ,[RZSFX] AS [rzsfx], [RZCKNU] AS [rzcknu] FROM [JDEDATA920].[F03B14] WHERE ([RZUPMJ] >= '{env:start_extraction_date}') AND ([RZUPMJ] <= '{env:end_extraction_date}');
REPLICATE [F42199] SELECT [SLEMCU] AS [slemcu], [SLDOCO] AS [sldoco], [SLKCO] AS [slkco], [SLDCT] AS [sldct], [SLDOC] AS [sldoc], [SLLNID] AS [sllnid], [SLKCOO] AS [slkcoo], [SLDCTO] AS [sldcto], [SLLTTR] AS [sllttr], [SLSOBK] AS [slsobk], [SLTORG] AS [sltorg], [SLUSER] AS [sluser], [SLUPMJ] AS [slupmj], [SLTDAY] AS [sltday], [SLNXTR] AS [slnxtr], [SLAN8] AS [slan8], [SLSHAN] AS [slshan], [SLAEXP] AS [slaexp], [SLFEA] AS [slfea], [SLMCU] AS [slmcu], [SLPRP1] AS [slprp1], [SLPRP2] AS [slprp2], [SLCRCD] AS [slcrcd], [SLIVD] AS [slivd], [SLTRDJ] AS [sltrdj], [SLRSDJ] AS [slrsdj], [SLDRQJ] AS [sldrqj], [SLFRTH] AS [slfrth], [SLLNTY] AS [sllnty], [SLLITM] AS [sllitm], [SLSRP1] AS [slsrp1], [SLSRP2] AS [slsrp2], [SLSRP3] AS [slsrp3], [SLSRP4] AS [slsrp4], [SLSRP5] AS [slsrp5], [SLPTC] AS [slptc], [SLPRP4] AS [slprp4], [SLUOM] AS [sluom], [SLUORG] AS [sluorg], [SLLOCN] AS [sllocn] FROM [JDEDATA920].[F42199] WHERE ([SLUPMJ] >= '{env:start_extraction_date}') AND ([SLUPMJ] <= '{env:end_extraction_date}');

Note If it is required to filter by Companies, the REPLICATE queries for tables that contain Company field must be changed.

Below is a list of tables and their Company field.

Add the IN function to the WHERE clause and put all companies between brackets.

Below is an example for F0010 table: REPLICATE [F0010] SELECT [CCCO] AS [ccco], [CCCRCD] AS [cccrcd], [CCNAME] AS [ccname] FROM [JDEDATA920].[F0010] WHERE [CCCO] IN ('Company1', 'Company2');

Make sure you Save all changes.

Input fields

The following tables include the list of fields per input table, their description, data type to be used when formatting the input and the filter flag to identify those that are being used to filter data.

Input types

Below is an overview of the different field types and their default format settings.

Notes

• For all JDE tables, blanks and nulls do not mean the same thing. Note that CData Sync must not replace these values. For many of the fields, a blank could mean a standard/default value. And a null just means there is no value at all.
• Table names and field names are case-sensitive. Always make sure that the field names (column headers) in your dataset match the field names in the table below and that the file names match the table names.

Input tables & fields

F0005

Stores user-defined codes and their descriptions.

F0006

Stores information about branch, plant, warehouse, and business unit information, such as company, description, and category codes that are assigned to them.

F0010

Stores company definitions, including number and name, fiscal date pattern, and current period.

F0014

Stores standard payment terms.

F0015

Stores currency exchange rates.

F0092

Stores information about the user.

F0101

Stores address book information, including employees, companies, suppliers, and customers.

F0116

Stores addresses, address lines, postal codes, and effective dates for address book numbers.

Note: This table must not be filtered by dates as addresses could have effective dates from decades ago and still be valid today

F03012

The Customer Master by line of Business table stores specific information for each client & company combination.

F4101

Stores basic information about each item.

F40203

Stores information about order activity rules, such as order type, line type, last status, and next status. This table contains also descriptions for last statuses.

F40205

The Line type file contains information related to line types and their descriptions.

F03B11

The Customer Ledger is the transactional table for invoices which stores information at item level.

Note: the time based filter is done using rpupmj field. The latter is not later used in any logics and that is why is not part of the input file for F03b11

F03B14

The Receipts Detail is the transactional payments table that stores information at item level.

F42199

Historical sales orders table that contains information at item level.

Configuring transformations

Seed files

Automation_estimates_raw

This seed file is used to add automation-related properties to each activity, used for the automation potential dashboard. For more information, see Simulating Automation Potential.

Due_dates_configuration_raw

This seed file is used to define properties for the due dates. For more information, see Due Dates.

Setup_activities

Setup_order_types

There is no specific entity for deliveries in JDE. Instead, the same sales order item ID is being used for both purposes. There is an automated logic in the connector to split events that belong to deliveries from those for Sales orders. This split is done by selecting the minimum status of an order for which they become statuses that belong to Delivery item entity. If the automated logic does not find the expected value, there is the possibility to manually select that value. This is done by the use of this seed file.

Setup_automated_users

There are not always automated flags or ways to use algorithms to find out what activities were made automatically. In this case, you can manually set those users that are deemed automated.

The setup_automated_users.csv seed file contains the information of all automated users that need to be flagged manually.

Dbt variables

Design specifications

Entities

Activities

Automatically generated activities based on F42199 table

• Activities generated from a concatenation of the last status of the order and its description from the F40203 table.

    *F42199.Status_code_last* + ' - ' + *F40203.Description_status*
    Examples: 
        520 - Enter Sales Order
        524 - Approve Sales Order
  

• There are cases where the Status_code_last gets stuck and stops changing with subsequent activities. For these cases, there are specific logics to populate the correct status and, therefore, its activity.

• Activities that do not change the status of an order, are named as the concatenation of the last status and 'Same-status Activity'.

    *F42199.Status_code_last* + ' - ' + *F40203.Description_status*
    Examples: 
        520 - Same-status Activity
        524 - Same-status Activity
  

The last status and its description are not standard and are mostly customizable for each JDE implementation. Therefore, it is not possible to know the complete list of activities in advance. Also, the same status may mean different activities depending on the order type.

Automatically generated activities based on F03b14 table

Activities generated by the concatenation of the verb Process and the document type of the journal entry.

    'Process ' + F03b14_input."Document_type_journal_entry"
    Example:
        Process Cash Receipts

Default Activities

Activities created based on the transactional tables information. Most of these activities apply to the Accounts Receivable module.

Note on Activity_order attribute

The last_status_code in JDE is used for tracking the activities that have happened to each order item. These statuses are 3-digit numbers that go from 100 to 999. All statuses greater than 900 represent backorders, line splits, cancellations and purges.

Activity orders are set following different logics according to the module where they were generated.

Sales Management activities

• For activities from Sales Management module, the last status code is used as the activity order, with the exception of the cases where the activity is a "Same-status Activity". For these cases the logic adds 1 to the activity order.

  Examples:

  Activity	Activity order
  520 - Enter Sales Order	520
  520 - Same-status Activity	521

Note that the last status that a record could have in the F42199 historical table is 999 which means 'Ready to Purge'. Based on that information, the Accounts Receivable Module starts with Activity orders equal to '1000' value.

Accounts Receivable activities

• For the Accounts Receivable module activities, activity orders were predefined in the models that generate the activities.

Design details

Limitations

• All activities based on F42199 table (Sales Management module) were created using the Status_code_last field. However, there are times where that status gets stuck at a single value. For those cases, there are logics created that will help populate the activity with the right value. But it is required to have at least some information to reconstruct it correctly. When that information is not available, the connector will assign an activity named as the concatenation of Status_code_last and 'Same-status Activity' text.

• All activities coming from Accounts Receivable module do not have a specific timestamp to be used as the event_end. Solutions were set to create the best approximate timestamp but they could potentially not match the exact moment when they happened. This could be seen as activities being shown in the wrong order.

Known issues

• Display currency: in the rare case where the client does not have the same reporting currency code across multiple companies, it may happen that currency conversion rates are not available for converting everything into a single currency.

Common problems

• Blanks replaced by null: the connector was built expecting that all blanks in source system remain as blanks (instead of being converted to nulls) by CDATA. Analysts must confirm that CDATA is properly set to not do this replacement.