Oracle JDE Purchase-to-Pay app template

• Introduction
  • Purchase-to-Pay process
    • Procurement
    • Accounts Payable
    • Monitoring the Purchase-to-Pay 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
  • F4101
  • F40203
  • F0411
  • F0414
  • F43121
  • F43199
• Design specifications
  • Entities
  • Activities
    • Procurement module
    • Accounts Payable module
  • Design details
• Customizing the transformations
  • Configuration
    • Seed files
    • Dbt variables
• Limitations, known issues, common problems
  • Known issues
  • Common problems

Introduction

Purchase-to-Pay process

The Purchase-to-Pay process is an end-to-end process that starts with a requisition order and ends up with the payment of the supplier's invoice. The Purchase-to-Pay process consists of steps such as: Creating & approving the purchase requisition, creating, approving & closing the purchase order, receiving the goods, creating & approving the invoice and executing the outgoing payment.

Procurement

The Procurement module includes all steps related to orders for any of the following type:

• Requisition orders;
• Quote orders (that are excluded from the model);
• Blanket orders;
• Purchase orders.

Accounts Payable

The Accounts Payable module includes the following objects:

• Invoice matched to Purchase orders;
• Standalone invoices.

Monitoring the Purchase-to-Pay process in Process Mining

With the Oracle JDE Purchase-to-Pay app template you can create process apps that give insight in how your Purchase-to-Pay process actually performs. For example, how long it takes for a purchase request to become an actual Purchase Order, how long it takes to process the Purchase Orders, and most important to what extent are you paying in time. For all steps in the process, Purchase-to-Pay provides insights into the throughput times.

Oracle JDE configuration

Source system

The Oracle JDE modules Procurement and Accounts payable 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 Purchase-to-Pay 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

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] 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 [F0411_raw] SELECT [RPDOC] AS [rpdoc], [RPDCT] AS [rpdct], [RPKCO] AS [rpkco], [RPDCTA] AS [rpdcta], [RPSFX] AS [rpsfx], [RPAN8] AS [rpan8], [RPVOD] AS [rpvod], [RPTORG] AS [rptorg], [RPAAP] as [rpaap], [RPBCRC] AS [rpbcrc], [RPAG] AS [rpag], [RPCRCD] AS [rpcrcd], [RPACR] AS [rpacr], [RPPTC] AS [rpptc], [RPPYIN] AS [rppyin], [RPUPMJ] AS [rpupmj], [RPDICJ] AS [rpdicj], [RPDIVJ] AS [rpdivj], [RPDGJ] AS [rpdgj], [RPDDJ] AS [rpddj], [RPDDNJ] AS [rpddnj], [RPADSC] AS [rpadsc], [RPADSA] AS [rpadsa] FROM [JDEDATA920].[F0411] WHERE ([RPUPMJ] >= '{env:start_extraction_date}') AND ([RPUPMJ] <= '{env:end_extraction_date}');
REPLICATE [F0414_raw] SELECT [RNPYID] AS [rnpyid], [RNKCO] AS [rnkco], [RNDOC] AS [rndoc], [RNDCT] AS [rndct], [RNDCTM] AS [rndctm], [RNUSER] AS [rnuser], [RNPID] AS [rnpid], [RNAN8] AS [rnan8], [RNRC5] AS [rnrc5], [RNUPMJ] AS [rnupmj] FROM [JDEDATA920].[F0414] WHERE ([RNUPMJ] >= '{env:start_extraction_date}') AND ([RNUPMJ] <= '{env:end_extraction_date}') AND [RNDCT] IS NOT NULL;
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 [F43121_raw] SELECT [PRMATC] AS [prmatc], [PRKCO] AS [prkco], [PRDOC] AS [prdoc], [PRDCT] AS [prdct], [PRKCOO] AS [prkcoo], [PRDOCO] AS [prdoco], [PRDCTO] AS [prdcto], [PRLNID] AS [prlnid], [PRAN8] AS [pran8], [PRSFXO] AS [prsfxo], [PRNLIN] AS [prnlin], [PRLITM] AS [prlitm], [PRUOM] AS [pruom], [PRUREC] AS [prurec], [PRAREC] AS [prarec], [PRCRCD] AS [prcrcd], [PRFEC] AS [prfec], [PRUPMJ] AS [prupmj], [PRTDAY] AS [prtday], [PRUSER] AS [pruser], [PRPID] AS [prpid] FROM [JDEDATA920].[F43121] WHERE ([PRUPMJ] >= '{env:start_extraction_date}') AND ([PRUPMJ] <= '{env:end_extraction_date}') AND [PRMATC] IN ('2','3');
REPLICATE [F43199_raw] SELECT [OLOPDJ] AS [olopdj], [OLAEXP] AS [olaexp], [OLFEA] AS [olfea], [OLSHAN] AS [olshan], [OLKCOO] AS [olkcoo], [OLDOCO] AS [oldoco], [OLDCTO] AS [oldcto], [OLLNID] AS [ollnid], [OLOCTO] AS [olocto], [OLMCU] AS [olmcu], [OLPDS1] AS [olpds1], [OLPDS2] AS [olpds2], [OLPDP1] AS [olpdp1], [OLPDP2] AS [olpdp2], [OLLITM] AS [ollitm], [OLAN8] AS [olan8], [OLLNTY] AS [ollnty], [OLCRCD] AS [olcrcd], [OLUORG] AS [oluorg], [OLUOPN] AS [oluopn], [OLUOM] AS [oluom],
[OLTRDJ] AS [oltrdj], [OLDGL] AS [oldgl], [OLLTTR] AS [ollttr], [OLNXTR] AS [olnxtr], [OLUPMJ] AS [olupmj], [OLTDAY] AS [oltday], [OLUSER] AS [oluser], [OLTORG] AS [oltorg], [OLPID] AS [olpid], [OLOKCO] AS [olokco], [OLOORN] AS [oloorn], [OLOGNO] AS [ologno], [OLDRQT] AS [oldrqt], [OLUKID] AS [olukid], [OLLOCN] AS [ollocn] FROM [JDEDATA920].[F43199] WHERE ([OLUPMJ] >= '{env:start_extraction_date}') AND ([OLUPMJ] <= '{env:end_extraction_date}') AND [OLLTTR] IS NOT NULL;

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 that entity.

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 and what kinds of access the user has.

F0101

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

F0116

Stores addresses, address lines, postal codes, and effective dates for address book numbers. Also, the stored information includes the fields that need to be protected for data privacy.

F4101

Stores basic information about each item, including Item number, Description, Search keys Category codes, Default units of measure, Process groups for the JD Edwards EnterpriseOne Warehouse Management system, Item dimension group.

F40203

Stores information about order activity rules, such as order type, line type, last status, and next status.

F0411

Stores information about Accounts Payable Ledger.

F0414

Stores information about Accounts Payable Matching Document Detail.

F43121

Stores receipt record and voucher record information with details such as open quantity and open amount.

F43199

Stores detail information for each purchase order line, such as item number, price, quantity ordered, and purchase amount (PA) ledger type.

Design specifications

Entities

Activities

Procurement module

For the Procurement module, activities are generated from a concatenation of the last status and its description which are not standard and mostly customizable for each JDE implementation.

Procurement activities: F43199.Status_Code_Last + ' - ' + F40203.Description_Status

Examples:

105 - Create New Item

105 - Reject Requisition

Below is a list of activities that can exist for the Procurement module. (Note that the actual names depend on the JDE implementation)

• Purchase requisitions
  • Enter purchase requisition
  • Approve purchase requisition
  • Reject purchase requisition
  • Close purchase requisition
• Blanket orders
  • Enter blanket order
  • Approve blanket order
  • Print blanket order
  • Release blanket order
• Purchase orders
  • Enter purchase order
  • Approve purchase order
  • Reject purchase order
  • Print purchase order
• Goods receipt
  • Goods receipt

Accounts Payable module

The Accounts Payable module does not have historical information nor customized activities like mentioned for Procurement. Therefore, all activities were created in advance.

The following activities are available for both Procurement and Accounts Payable modules:

Note on Activity_order attribute Activity orders are set following different logics according to the module where they were generated. For all activities from procurement module, the last status code is used as the activity order. For AP activities, they were predefined in the models that generate them.

The following table shows the ranges used for each entity in the procurement module.

The following is the complete list of all Accounts Payable activities and their Activity order.

Design details

The procurement module in JDE records transactions for all types of orders in the same table F43199 and there is no classification done in the system that could be used for splitting between all entities. Therefore, knowing what type of orders belong to each entity needs an extra effort. P2p_entity_list will be used to automatically classify all order types into different entities.

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. And, in a standard implementation of JDE, sections of that range are defined for each entity.

All statuses greater than 900 do not represent any particular entity and must be filtered out. Statuses over 900 are related to cancellations and purges.

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 JDE Purchase-to-Pay:

Setup_activities.csv

Setup_order_types.csv

As Oracle JDE stores all type of orders in the same table, it is required to classify each of them into entities. For remote cases where one of the order types could not be classified automatically, the following message is displayed in the P2p_entity_list table: Order type not defined in a single entity.

In this case, the order type must be classified manually by adding it to the setup_order_type file.

Setup_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_users.csv seed file contains the information of all automated users that need to be flagged manually.

Dbt variables

Limitations, known issues, common problems

Known issues

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

Common problems

Order types not being classified in any entity: If one of the values of the P2p_entity_list.Entity is 'Order type not defined in a single entity' a manual intervention must be done.