Oracle JDE Order-to-Cash app template

• Introduction
  • Order-to-Cash process
    • Sales Management
    • Accounts Receivable
    • Monitoring the Order-to-Cash process in Process Mining
• Oracle JDE configuration
  • Source system
  • Environmental settings
• Configuring CData Sync for Oracle JDE
  • Setting up the source connection
  • Creating the extraction job
    • Setting up environment variables in the extraction job
    • Table Replication
• Input fields
  • Input types
  • F0005
  • F0006
  • F0010
  • F0014
  • F0015
  • F0092
  • F0101
  • F0116
  • F03012
  • F4101
  • F40203
  • F40205
  • F03B11
  • F03B14
  • F42199
• Design specifications
  • Entities
  • Activities
    • Automatically generated activities based on F42199 table
    • Automatically generated activities based on F03b14 table
    • Default Activities
• Customizing the transformations
  • Configuration
    • Seed files
    • Dbt variables
• Limitations, known issues, common problems
  • Limitations
  • Known issues
  • Common problems

Introduction

Order-to-Cash process

The Order-to-Cash process is an end-to-end process that usually starts when a sales order is created. After that, it is shipped and invoiced to the customer. Finally the process ends with the collection of the payment from the customer.

Sales Management

The Sales management module includes all activities related to:

• Sales orders;
• Deliveries;
• Invoices (that will later be interfaced to Accounts Receivable).

Accounts Receivable

The Accounts Receivable module includes all activities related to:

• Invoices.
• Payments.

Monitoring the Order-to-Cash process in Process Mining

With the Oracle JDE Order-to-Cash app template you can create process apps that give insight in how your Order-to-Cash process actually performs. With the Order-to-Cash process app business users can easily monitor the Order-to-Cash process to check the progress and quality of the process on a regular basis. If deviations are detected, you can take action to improve or change the process or parts of the process. In the Order-to-Cash app template, default KPIs are defined, which enable you to keep an overview of the process.

Oracle JDE configuration

Source system

The Oracle JDE modules Sales Management and Accounts Receivable are mandatory. Other tables could be used for master data purposes.

Environmental settings

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.

Configuring CData Sync for Oracle JDE

Below is a description on how to use CData Sync to set up a source connection and load data into a Process Mining Oracle JDE Order-to-Cash process app.

In general, you should follow the steps as described in Loading data using CData Sync (Snowflake) or Loading data using CData Sync (SQL Server) to set up data loading using CData Sync. Since specific settings are required when using Oracle JDE, pay attention to the steps described below.

Setting up the source connection

• Select Oracle as the source system to which you want to create a connection from the list.

Creating the extraction job

Define the following settings in the Replicate Options section in the Advanced tab in the Job Settings panel.

• Under Destination Schema, setup the target data schema in the SQL Server or Snowflake database.
• Select the Drop Table option.

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.

Be mindful of choosing a start_extraction_date that encompasses the data that you want to capture, as orders are the starting point of the order to cash process.

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" />

Important: Do not modify the api:info details that are shown by default.

In order to modify the environment variables, modify the values within the Events tab. By default, end_extraction_date will default to today's date. start_extraction_date must always be populated using JDE julian format. 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.

Table Replication

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

Default Extraction

The following script is prepared for scenarios where Currency Conversions and Person Accounts are not used and are disabled. In case any of these are used, please add the scripts in the Alternative Scenarios section.

REPLICATE [F0005_raw] SELECT [DRKY] AS [drky], [DRRT] AS [drrt], [DRSY] AS [drsy], [DRDL01] AS [drdl01] FROM [JDECTL920].[F0005];
REPLICATE [F0006_raw] SELECT [MCMCU] AS [mcmcu], [MCDL01] AS [mcdl01], [MCRP01] AS [mcrp01] FROM [JDEDATA920].[F0006];
REPLICATE [F0010_raw] SELECT [CCCO] AS [ccco], [CCCRCD] AS [cccrcd], [CCNAME] AS [ccname] FROM [JDEDATA920].[F0010];
REPLICATE [F0014_raw] SELECT [PNPTC] AS [pnptc], [PNPTD] AS [pnptd] FROM [JDEDATA920].[F0014];
REPLICATE [F0015_raw] 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_raw] SELECT [ULAN8] AS [ulan8], [ULUSER] AS [uluser] FROM [JDESY920].[F0092];
REPLICATE [F0101_raw] SELECT [ABAN8] AS [aban8], [ABALPH] AS [abalph], [ABMCU] AS [abmcu], [ABAT1] as [abat1] FROM [JDEDATA920].[F0101];
REPLICATE [F0116_raw] SELECT [ALAN8] AS [alan8], [ALEFTB] AS [aleftb], [ALCTY1] AS [alcty1], [ALCOUN] AS [alcoun], [ALADDS] AS [aladds], [ALCTR] AS [alctr] FROM [JDEDATA920].[F0116];
REPLICATE [F03012_raw] SELECT [AIAN8] AS [aian8], [AICO] AS [aico], [AICPGP] AS [aicpgp], [AIAC11] AS [aiac11] FROM [JDEDATA920].[F03012];
REPLICATE [F4101_raw] SELECT [IMDSC1] AS [imdsc1], [IMLITM] AS [imlitm] FROM [JDEDATA920].[F4101];
REPLICATE [F40203_raw] SELECT [FSDCTO] AS [fsdcto], [FSTRTY] AS [fstrty], [FSSTDS] AS [fsstds], [FSLNTY] AS [fslnty] FROM [JDEDATA920].[F40203];
REPLICATE [F40205_raw] SELECT [LFLNTY] AS [lflnty], [LFLNDS] AS [lflnds] FROM [JDEDATA920].[F40205];
REPLICATE [F03b11_raw] 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_raw] 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_raw] 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_raw] 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.

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.

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.

Customizing the transformations

Configuration

Seed files can be used for variables that require a list of values. Seed files are available in the seeds folder of the app transformations. You can edit the seeds files when editing app transformations.

Seed files

The following seed files are available for Oracle NetSuite Order-to-Cash:

Setup_activities.csv

Setup_order_types.csv

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.csv

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

Limitations, known issues, common problems

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.