Oracle Cloud Order-to-Cash app template


Extraction

This app template uses Oracle Cloud/Oracle Fusion as source system, which in the remainder of this documentation will be referenced as Oracle Cloud. Extraction for the system is done by exporting CSV files and load this data using CData Sync. Make sure you have a valid license for CData Sync and have installed CData Sync.

System configuration

Oracle Cloud is a Software as a Service (SaaS) and it gets constant updates that are deployed quarterly, naming its version by the concatenation of the year (2 digits) and a letter that represents the quarter (A, B, C and D). This extraction was tested for Oracle Cloud v.23D and 24A.

The following modules must be used, including the following information:

The user maintaining the data models and reports should have the BI Administrator role.

Note: To generate the csv files it is required to use Oracle Transactional Business Intelligence (OTBI) tool.

Follow the steps below to export the correct tables to CSV files, which can then be loaded into a Process Mining Order-to-Cash process app.

Upload object to Oracle Cloud

  1. Sign in to Oracle Cloud and navigate to Reports & Analytics - Browse Catalog. See the illustration below.
  1. Once the window is displayed, select SHARED FOLDERS - Unarchive. A new box will be displayed
  1. Click OK to start the import of the object. Once it has finished, you will be able to see the folder and the structured sub folders. See the illustration below.
  1. Click on each of the sub folders to view data models and reports.

Report execution

Below is a description of the steps to execute Oracle Cloud reports. There are two kinds of reports. Reports that refer to transactional data (sales orders, deliveries, invoices, payments) and reports that refer to master data (for example: lookups, territories, daily rates, etc.)

Reports for transactional data will contain date ranges which are mandatory in order to execute the report.

Note: The illustrations belong to another business process but they represents clearly the steps to follow in order to execute Oracle Cloud reports.

  1. Select the report you want to execute. Click on Open
  1. To export the report, select the gear icon at the top right corner of the page, EXPORT > CSV The report will be downloaded locally.

Setting up the source connection in CData Sync

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.

Advanced job settings

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

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:

Step Action
1 Go to the Events tab in the Job Settings panel of the job you are creating
2 Edit the Post-Job Event section to add the code displayed below after <!-- Code goes here -- >. Do not modify the api:info details that are shown by default.
3 Fill out the End of Upload API with the value provided.
4 In case you need a debugging log for this call, uncomment the two lines provided in the code below and provide a location for the log file.
5 Click on Save Changes.
<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 [AR_ADJUSTMENTS_ALL_R_AR_ADJUSTMENTS_ALL_R] SELECT * FROM [AR_ADJUSTMENTS_ALL_R_AR_ADJUSTMENTS_ALL_R.csv]; REPLICATE [AR_CASH_RECEIPTS_ALL_R_AR_CASH_RECEIPTS_ALL_R] SELECT * FROM [AR_CASH_RECEIPTS_ALL_R_AR_CASH_RECEIPTS_ALL_R.csv]; REPLICATE [AR_LOOKUPS_R_AR_LOOKUPS_R] SELECT * FROM [AR_LOOKUPS_R_AR_LOOKUPS_R.csv]; REPLICATE [AR_RECEIPT_METHODS_R_AR_RECEIPT_METHODS_R] SELECT * FROM [AR_RECEIPT_METHODS_R_AR_RECEIPT_METHODS_R.csv]; REPLICATE [AR_RECEIVABLE_APPLICATIONS_ALL_R_AR_RECEIVABLE_APPLICATIONS_ALL_R] SELECT * FROM [AR_RECEIVABLE_APPLICATIONS_ALL_R_AR_RECEIVABLE_APPLICATIONS_ALL_R.csv]; REPLICATE [DOO_FULFILL_LINES_ALL_R_DOO_FULFILL_LINES_ALL_R] SELECT * FROM [DOO_FULFILL_LINES_ALL_R_DOO_FULFILL_LINES_ALL_R.csv]; REPLICATE [DOO_HEADERS_ALL_R_DOO_HEADERS_ALL_R] SELECT * FROM [DOO_HEADERS_ALL_R_DOO_HEADERS_ALL_R.csv]; REPLICATE [DOO_HOLD_CODES_TL_R_DOO_HOLD_CODES_TL_R] SELECT * FROM [DOO_HOLD_CODES_TL_R_DOO_HOLD_CODES_TL_R.csv]; REPLICATE [DOO_HOLD_INSTANCES_R_DOO_HOLD_INSTANCES_R] SELECT * FROM [DOO_HOLD_INSTANCES_R_DOO_HOLD_INSTANCES_R.csv]; REPLICATE [DOO_LINES_ALL_R_DOO_LINES_ALL_R] SELECT * FROM [DOO_LINES_ALL_R_DOO_LINES_ALL_R.csv]; REPLICATE [DOO_MANUAL_PRICE_ADJUSTMENTS_R_DOO_MANUAL_PRICE_ADJUSTMENTS_R] SELECT * FROM [DOO_MANUAL_PRICE_ADJUSTMENTS_R_DOO_MANUAL_PRICE_ADJUSTMENTS_R.csv]; REPLICATE [DOO_STATUSES_B_R_DOO_STATUSES_B_R] SELECT * FROM [DOO_STATUSES_B_R_DOO_STATUSES_B_R.csv]; REPLICATE [DOO_STATUSES_TL_R_DOO_STATUSES_TL_R] SELECT * FROM [DOO_STATUSES_TL_R_DOO_STATUSES_TL_R.csv]; REPLICATE [DOO_STEP_INSTANCE_DETAILS_R_DOO_STEP_INSTANCE_DETAILS_R] SELECT * FROM [DOO_STEP_INSTANCE_DETAILS_R_DOO_STEP_INSTANCE_DETAILS_R.csv]; REPLICATE [EGP_ITEM_CLASSES_B_R_EGP_ITEM_CLASSES_B_R] SELECT * FROM [EGP_ITEM_CLASSES_B_R_EGP_ITEM_CLASSES_B_R.csv]; REPLICATE [EGP_SYSTEM_ITEMS_R_EGP_SYSTEM_ITEMS_R] SELECT * FROM [EGP_SYSTEM_ITEMS_R_EGP_SYSTEM_ITEMS_R.csv]; REPLICATE [FA_FUSION_SOAINFRA_WFTASK_R_FA_FUSION_SOAINFRA_WFTASK_R] SELECT * FROM [FA_FUSION_SOAINFRA_WFTASK_R_FA_FUSION_SOAINFRA_WFTASK_R.csv]; REPLICATE [FND_LOOKUP_VALUES_TL_R_FND_LOOKUP_VALUES_TL_R] SELECT * FROM [FND_LOOKUP_VALUES_TL_R_FND_LOOKUP_VALUES_TL_R.csv]; REPLICATE [FND_LOOKUPS_R_FND_LOOKUPS_R] SELECT * FROM [FND_LOOKUPS_R_FND_LOOKUPS_R.csv]; REPLICATE [FND_TERRITORIES_B_R_FND_TERRITORIES_B_R] SELECT * FROM [FND_TERRITORIES_B_R_FND_TERRITORIES_B_R.csv]; REPLICATE [FND_TERRITORIES_TL_R_FND_TERRITORIES_TL_R] SELECT * FROM [FND_TERRITORIES_TL_R_FND_TERRITORIES_TL_R.csv]; REPLICATE [GL_DAILY_RATES_R_GL_DAILY_RATES_R] SELECT * FROM [GL_DAILY_RATES_R_GL_DAILY_RATES_R.csv]; REPLICATE [HR_LOCATIONS_R_HR_LOCATIONS_R] SELECT * FROM [HR_LOCATIONS_R_HR_LOCATIONS_R.csv]; REPLICATE [HR_ORGANIZATION_UNITS_F_TL_R_HR_ORGANIZATION_UNITS_F_TL_R] SELECT * FROM [HR_ORGANIZATION_UNITS_F_TL_R_HR_ORGANIZATION_UNITS_F_TL_R.csv]; REPLICATE [HR_ORGANIZATION_UNITS_R_HR_ORGANIZATION_UNITS_R] SELECT * FROM [HR_ORGANIZATION_UNITS_R_HR_ORGANIZATION_UNITS_R.csv]; REPLICATE [HZ_PARTIES_R_HZ_PARTIES_R] SELECT * FROM [HZ_PARTIES_R_HZ_PARTIES_R.csv]; REPLICATE [INV_UNITS_OF_MEASURE_B_R_INV_UNITS_OF_MEASURE_B_R] SELECT * FROM [INV_UNITS_OF_MEASURE_B_R_INV_UNITS_OF_MEASURE_B_R.csv]; REPLICATE [INV_UNITS_OF_MEASURE_TL_R_INV_UNITS_OF_MEASURE_TL_R] SELECT * FROM [INV_UNITS_OF_MEASURE_TL_R_INV_UNITS_OF_MEASURE_TL_R.csv]; REPLICATE [MSC_PAYMENT_TERMS_TL_R_MSC_PAYMENT_TERMS_TL_R] SELECT * FROM [MSC_PAYMENT_TERMS_TL_R_MSC_PAYMENT_TERMS_TL_R.csv]; REPLICATE [PER_USERS_R_PER_USERS_R] SELECT * FROM [PER_USERS_R_PER_USERS_R.csv]; REPLICATE [RA_CUSTOMER_TRX_ALL_R_RA_CUSTOMER_TRX_ALL_R] SELECT * FROM [RA_CUSTOMER_TRX_ALL_R_RA_CUSTOMER_TRX_ALL_R.csv]; REPLICATE [RA_CUSTOMER_TRX_LINES_ALL_R_RA_CUSTOMER_TRX_LINES_ALL_R] SELECT * FROM [RA_CUSTOMER_TRX_LINES_ALL_R_RA_CUSTOMER_TRX_LINES_ALL_R.csv]; REPLICATE [RA_TERMS_B_R_RA_TERMS_B_R] SELECT * FROM [RA_TERMS_B_R_RA_TERMS_B_R.csv]; REPLICATE [RA_TERMS_LINES_DISCOUNTS_R_RA_TERMS_LINES_DISCOUNTS_R] SELECT * FROM [RA_TERMS_LINES_DISCOUNTS_R_RA_TERMS_LINES_DISCOUNTS_R.csv]; REPLICATE [RA_TERMS_LINES_R_RA_TERMS_LINES_R] SELECT * FROM [RA_TERMS_LINES_R_RA_TERMS_LINES_R.csv]; REPLICATE [WSH_DELIVERY_ASSIGNMENTS_R_WSH_DELIVERY_ASSIGNMENTS_R] SELECT * FROM [WSH_DELIVERY_ASSIGNMENTS_R_WSH_DELIVERY_ASSIGNMENTS_R.csv]; REPLICATE [WSH_DELIVERY_DETAILS_R_WSH_DELIVERY_DETAILS_R] SELECT * FROM [WSH_DELIVERY_DETAILS_R_WSH_DELIVERY_DETAILS_R.csv]; REPLICATE [WSH_EXCEPTIONS_R_WSH_EXCEPTIONS_R] SELECT * FROM [WSH_EXCEPTIONS_R_WSH_EXCEPTIONS_R.csv]; REPLICATE [WSH_NEW_DELIVERIES_R_WSH_NEW_DELIVERIES_R] SELECT * FROM [WSH_NEW_DELIVERIES_R_WSH_NEW_DELIVERIES_R.csv]; REPLICATE [XLE_ENTITY_PROFILES_R_XLE_ENTITY_PROFILES_R] SELECT * FROM [XLE_ENTITY_PROFILES_R_XLE_ENTITY_PROFILES_R.csv];

Note on CSV files and their parameters P_DATE_FROM and P_DATE_TO are the date parameters used for filtering the source tables while doing the extraction. Oracle Cloud includes them automatically in the output csv file and there was no setup that could be used for removing them.


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.

Field type Description
boolean true, false, 1, 0
datetime YYYY-MM-DDThh24:mi:ss.ff3
double Decimal separator: . (dot); thousand separator: none
integer Thousand separator: none
text N/A

Input tables & fields

AR_ADJUSTMENTS_ALL

This table stores information about adjustments made to invoices, debit memos, credit memos, and other receivable transactions.

Field Type Description
ADJUSTMENT_ID text Adjustment identifier.
CUSTOMER_TRX_ID text Identifier of customer transaction associated with this adjustment.
CREATED_BY text Who column: indicates the user who created the row.
CREATION_DATE datetime Who column: indicates the date and time of the creation of the row.
COMMENTS text User-entered comments about the adjustment.
TYPE text Lookup code for adjustment type in AR_LOOKUPS.

AR_CASH_RECEIPTS_ALL

This table contains detailed records of cash receipts transactions, including information about the payment method, amount received, customer details, and other relevant data.

Field Type Description
CASH_RECEIPT_ID text Cash receipt identifier. (Primary key)
AMOUNT double Amount of the payment entry, denormalized from the history record to allow querying on amount.
CREATED_BY text Who column: indicates the user who created the row.
CREATION_DATE datetime Who column: indicates the date and time of the creation of the row.
EXCHANGE_DATE datetime Date for which rate selected.
CURRENCY_CODE text Currency code of the payment batch associated with this payment entry.
ORG_ID text Indicates the identifier of the business unit associated to the row.
LAST_UPDATE_DATE datetime Who column: indicates the date and time of the last update of the row.
LEGAL_ENTITY_ID text Identifies the legal entity to receive this receipt.
RECEIPT_DATE datetime This is the receipt date.
REVERSAL_DATE datetime Date payment entry reversed.
RECEIPT_METHOD_ID text Identifies the payment method of the receipt.
RECEIPT_NUMBER text This is the receipt number displayed in the system.
STATUS text Identifies whether the status of this payment entry is applied, unapplied, unidentified, insufficient funds, reverse payment or stop payment.
TYPE text Identifies the category of the payment entry - either CASH or MISC; when coding, please use = 'MISC' or != 'MISC' (instead of = 'CASH') because of internal customization.

AR_LOOKUPS

This table contains lookup codes and their corresponding meanings or values within the Receivables module.

Field Type Description
LOOKUP_CODE text Code related to Receivable objects
LOOKUP_TYPE text Type related to the Receivable object.
MEANING text User-friendly meaning of the Receivable object.

AR_RECEIVABLE_APPLICATIONS_ALL

This table stores detailed information about how payments are applied to specific invoices.

Field Type Description
RECEIVABLE_APPLICATION_ID text Identifies the receivable application. (Primary key)
AMOUNT_APPLIED double For same currency applications, the total amount of the application in the currency of the receipt and transaction. For cross currency applications, the total amount of the application in the currency of the transaction.
APPLIED_CUSTOMER_TRX_ID text Identifies the debit item or credit memo to which a payment or credit memo is applied.
APPLICATION_TYPE text Identifies the type of application done against the invoice.
CREATED_BY text Who column: indicates the user who created the row.
CREATION_DATE datetime Who column: indicates the date and time of the creation of the row.
CASH_RECEIPT_ID text Identifies the payment being applied.
CUSTOMER_TRX_ID text Identifies the debit item or credit memo being applied
GL_POSTED_DATE date Date the application was posted to General Ledger.

AR_RECEIPT_METHODS

This table contains essential information related to receipt methods used in managing customer payments.

Field Type Description
RECEIPT_METHOD_ID text Identifier of the receipt method. (Primary key)
NAME text Receipt method name.

DOO_FULFILL_LINES_ALL

This table stores detailed information about the lines or items that are fulfilled as part of sales orders.

Field Type Description
FULFILL_LINE_ID text Unique Identifier for a fulfillment line.(Primary key)
FULFILL_ORG_ID text Reference to the unique identifier for the fulfillment organization.
LATEST_ACCEPTABLE_SHIP_DATE datetime The latest date that the customer is willing to have a warehouse ship a product.
LINE_ID text Reference to the unique identifier for the orchestration order line.
PAYMENT_TERM_ID text Payment Terms used by billing during Invoice generation.
SELLING_PROFIT_CENTER_BU_ID text Reference to Unique Identifier of Profit Center Business Unit.
SHIP_TO_PARTY_ID text Reference to the unique identifier for ship-to Party.
STATUS_CODE text Current fulfillment status of the fulfillment line.

DOO_HEADERS_ALL

This table stores information about order headers, which represent the overarching details and attributes associated with a sales order.

Field Type Description
HEADER_ID text Unique identifier of the orchestration order.(Primary key)
CREATED_BY text Who column: indicates the user who created the row.
CREATION_DATE datetime Who column: indicates the date and time of the creation of the row.
LEGAL_ENTITY_ID text Reference to the unique identifier for the legal entity.
ORDER_TYPE_CODE text Name of the type of order that was placed.
ORDER_NUMBER text Representation of a sales order used in communication with fulfillment systems.
REQUEST_ARRIVAL_DATE datetime The original date the customer requested the item arrive on.
SALES_CHANNEL_CODE text Name of the type of Sales Channel that was used like Web,Phone, CPQ, webstore, etc.
SOURCE_ORG_ID text Reference to the unique identifier for the organization of the source order.
SOLD_TO_PARTY_ID text Reference to the unique identifier for the sold-to Party.
SUBMITTED_BY text The user who submitted the order.
SUBMITTED_DATE datetime The date and time of the submission of the order.
TRANSACTIONAL_CURRENCY_CODE text Currency code captured at the order header level.

DOO_HOLD_CODES_TL

This table stores translations of hold codes into multiple languages for internationalization purposes.

Field Type Description
HOLD_CODE_ID text Reference to the unique identifier for hold codes.(Primary key)
LANGUAGE text Indicates the code of the language into which the contents of the translatable columns are translated.(Primary key)
ORCHESTRATION_APPLICATION_ID text Reference to the unique identifier of the Orchestration Application.(Primary key)
HOLD_NAME text Name given by user for the hold code.

DOO_HOLD_INSTANCES

This table stores information about instances of holds placed on orders, indicating why an order is being held and providing details necessary for hold management and resolution.

Field Type Description
HOLD_INSTANCE_ID text Unique identifier of the hold instance.(Primary key)
CREATED_BY text Who column: indicates the user who created the row.
CREATION_DATE datetime Who column: indicates the date and time of the creation of the row.
HOLD_CODE_ID text Reference to the unique identifier for hold codes.
HOLD_COMMENTS text Comments the user who is initiating the hold adds to the hold.
HOLD_RELEASE_COMMENTS text Comments added by user while releasing a hold.
ORCHESTRATION_APPLICATION_ID text Orchestration Application Identifier that owns the transaction to which Holds belong to.
RELEASE_DATE datetime The date when the hold is released.
RELEASE_USER_ID text The user identifier that requested to release this hold instance.
TRANSACTION_ENTITY_NAME1 text Entity name corresponding to Transaction Entity Id1.
TRANSACTION_ENTITY_ID1 text Identifier of the Transaction Entity that has a Hold Applied.

DOO_LINES_ALL

This table stores detailed information about individual order lines, including the products or services ordered, quantities, pricing, and other attributes.

Field Type Description
LINE_ID text Unique identifier of the orchestration order line.(Primary key)
CATEGORY_CODE text A code used to group and process journal entries with similar characteristics such as adjustments, accruals, or reclassifications.
CREATION_DATE datetime Who column: indicates the date and time of the creation of the row.
CREATED_BY text Who column: indicates the user who created the row.
HEADER_ID text Reference to the unique identifier for the orchestration order.
INVENTORY_ITEM_ID text Reference to the unique identifier for the inventory item.
LINE_NUMBER integer Representation of a sales order line used in communication with fulfillment systems.
ORDERED_QTY double The quantity of a good or service ordered.
ORDERED_UOM text Unit of Measure for the quantity ordered- A predetermined amount or quantity or quality, such as of length, time, heat, value that is adopted as a standard of measurement.
UNIT_SELLING_PRICE double The price per unit that a buyer paid for an item.
UNIT_LIST_PRICE double The base selling price before applying discounts. It typically defaults from the item definition.

DOO_MANUAL_PRICE_ADJUSTMENTS

This table contains adjustments made to order lines selling prices for various reasons, such as discounts, promotions, or special pricing agreements.

Field Type Description
MANUAL_PRICE_ADJUSTMENT_ID text Unique Internal Identifier of Manual Price Adjustment.(Primary key)
COMMENTS text Comments on manual Price Adjustments.
CREATED_BY text Who column: indicates the user who created the row.
CREATION_DATE datetime Who column: indicates the date and time of the creation of the row.
PARENT_ENTITY_ID text Entity Id against which Price Adjustment is Applied.
REASON_CODE text Code which explains rationale for a manual discount.

DOO_STATUSES_B

This table contains the list of all possible order statuses and their translations. The status of orders throughout the order lifecycle and providing visibility into their current state.

Field Type Description
STATUS_ID text Unique identifier of the status.(Primary key)
STATUS_CODE text Status code.

DOO_STATUSES_TL

This table contains the translations of statuses associated with various entities or processes within the order processing workflow.

Field Type Description
STATUS_ID text Reference to the unique identifier of the status.(Primary key)
LANGUAGE text Indicates the code of the language into which the contents of the translatable columns are translated.(Primary key).
DISPLAY_NAME text Process class that is displayed to user.

DOO_STEP_INSTANCE_DETAILS

This table contains information about step instances within the order orchestration process. Details of each step instance executed during the processing of an order, providing visibility into the actions taken.

Field Type Description
STEP_INSTANCE_DETAIL_ID text Unique identifier for the details of a step instance.(Primary key)
CREATION_DATE datetime Indicates the date and time of the creation of the row.
CREATED_BY text Who column: indicates the user who created the row.
LINE_ID text Reference to the unique identifier for the orchestration order line.
STATUS_CODE text State or condition of a step.
TRANSACTION_ENTITY_NAME text Type of the Application Entity.

EGP_ITEM_CLASSES_B

This table serves as a repository for defining and managing item classes.

Field Type Description
ITEM_CLASS_ID text Table column indicating Id of the item class from which the attribute group is inherited.(Primary key)
ITEM_CLASS_CODE text A unique identification assigned to an item class which can be easily associated with the UNSPC category.

EGP_SYSTEM_ITEMS

This table contains information about items or products that an organization deals with in its operations.

Field Type Description
INVENTORY_ITEM_ID text Attribute indicating the unique identifier of the item.
ORGANIZATION_ID text Foreign Key to HR_ALL_ORGANIZATION_UNITS_F.
DESCRIPTION text Statement, picture in words, or account that describes; descriptive representation.
ITEM_CATALOG_GROUP_ID text Provides metadata common to all items that share the category. For example, the item catalog category "Engine" describes attributes, functions and other characteristics common to several item numbers.

FA_FUSION_SOAINFRA_WFTASK

This table stores information related to workflow tasks. These workflows consist of a sequence of activities or tasks that are performed to complete a specific business process.

Field Type Description
IDENTIFICATIONKEY text Unique identifier of the business object related to the workflow
APPLICATIONCONTEXT text Module related to the workflow
OUTCOME text Action code related to the workflow (e.g. APPROVE)
CREATEDDATE datetime Indicates the date and time of the creation of the row.
CREATOR text Who column: indicates the user who created the row.
PERCENTAGECOMPLETE double Workflow completion percentage
TITLE text Description of the action performed or requested for the workflow
UPDATEDDATE datetime Indicates the date and time of the updated of the row.
UPDATEDBY text Who column: indicates the user who updated the row.

FND_LOOKUP_VALUES_TL

This table contains the translation for the lookup values. Lookup values are used to define and maintain reference data, such as statuses or types.

Field Type Description
ENTERPRISE_ID text ENTERPRISE_ID.(Primary key)
LOOKUP_CODE text Code related to the Financial object. (Primary key)
LOOKUP_TYPE text Type related to the Financial object. (Primary key)
LANGUAGE text Indicates the code of the language into which the contents of the translatable columns are translated.(Primary key)
SANDBOX_ID text Sandbox code related to the Financial object. (Primary key)
SET_ID text Identifies a set of reference data shared across business units and other entities. Also known as Reference Data Sets, they are used to filter reference data in transactional UIs..(Primary key)
VIEW_APPLICATION_ID text View application code related to the Financial object. (Primary key)
MEANING text Meaning related to the Financial object.

FND_LOOKUPS

This table contains lookup types and their associated values. Lookups are used to define and maintain reference data, such as statuses or types.

Field Type Description
LOOKUP_CODE text Code related to the Financial object. (Primary key)
MEANING text Meaning related to the Financial object..
LOOKUP_TYPE text Type related to the Financial object.

FND_TERRITORIES_B

This table contains information related to territories. Territories are geographical regions that are defined for sales, marketing, and service activities to facilitate effective management of customer accounts.

Field Type Description
ENTERPRISE_ID text Enterprise code related to the territory .(Primary key)
TERRITORY_CODE text Identifier of the territory.(Primary key)
NLS_TERRITORY text Indicates the description of the territory.

FND_TERRITORIES_TL

This table contains translations of territory names and descriptions. It allows users to define territories with names and descriptions in multiple languages, facilitating localization and internationalization.

Field Type Description
ENTERPRISE_ID text Enterprise code related to the territory .(Primary key)
TERRITORY_CODE text Identifier of the territory.(Primary key)
LANGUAGE text Indicates the code of the language into which the contents of the translatable columns are translated.(Primary key)
DESCRIPTION text DESCRIPTION.

GL_DAILY_RATES

This table is used to store daily exchange rates for currencies, which are utilized for currency conversion and financial reporting purposes.

Field Type Description
FROM_CURRENCY text Currency that will be converted from.(Primary key)
TO_CURRENCY text Currency that will be converted to.(Primary key)
CONVERSION_DATE datetime Currency conversion date of a daily rate.(Primary key)
CONVERSION_TYPE text Currency conversion type of a daily rate.(Primary key)
CREATION_DATE datetime Who column: indicates the date and time of the creation of the row.
CONVERSION_RATE text Currency conversion rate of a daily rate.

HR_LOCATIONS

This table stores information related to physical locations associated with an organizations workforce. These locations could include offices, branches, facilities, or any other physical sites where employees may work or where business operations are conducted.

Field Type Description
LOCATION_ID text Code of the location .(Primary key)
LOCATION_NAME text Location description.

HR_ORGANIZATION_UNITS

This table contains information about organizational units within an enterprise. These units represent different levels of organizational hierarchy, such as departments, divisions, business units, and other structural components.

Field Type Description
ORGANIZATION_ID text Organizational unit ID
NAME text Denotes the translated name for the Organization Unit.

HR_ORGANIZATION_UNITS_F_TL

This table contains translations of organization unit names and descriptions.

Field Type Description
ORGANIZATION_ID text Organizational unit ID(Primary key)
LANGUAGE text Indicates the code of the language into which the contents of the translatable columns are translated.(Primary key)
NAME text Denotes the translated name for the Organization Unit.
EFFECTIVE_START_DATE datetime Date Effective Entity: indicates the date at the beginning of the date range within which the row is effective.(Primary key)
EFFECTIVE_END_DATE datetime Date Effective Entity: indicates the date at the end of the date range within which the row is effective.(Primary key)

HZ_PARTIES

This table stores information about parties, which are entities or individuals with whom a business interacts. Parties can include customers, suppliers, employees, organizations, and other entities involved in business transactions.

Field Type Description
PARTY_ID text Party identifier.(Primary key)
CITY text City of the Identifying address.
COUNTRY text The country listed in the TERRITORY_CODE column of the FND_TERRITORY table. for the Identifying address.
PARTY_NAME text Name of this party.

INV_UNITS_OF_MEASURE_B

This table contains information about units of measure (UOMs) for items and products.

Field Type Description
UNIT_OF_MEASURE_ID text Unique identifier of the Unit of Measure (UOM).(Primary key)
UOM_CODE text Unique short code assigned to a Unit of Measure (UOM)

INV_UNITS_OF_MEASURE_TL

This table contains the translated information for each unit of measure (UOM) names and descriptions.

Field Type Description
LANGUAGE text Indicates the code of the language into which the contents of the translatable columns are translated.(Primary key)
UNIT_OF_MEASURE_ID text Unique identifier of the Unit of Measure (UOM).(Primary key)
DESCRIPTION text Translatable Unit of Measure (UOM) description.

MSC_PAYMENT_TERMS_TL

This table is used to store translations of payment term names and descriptions, for localization and internationalization efforts within the application.

Field Type Description
TERM_ID text This value indicates the payment term identifier.(Primary key)
LANGUAGE text Indicates the code of the language into which the contents of the translatable columns are translated.(Primary key)
NAME text Name

PER_USERS

This table stores information about users or employees in the system.

Field Type Description
USER_ID text Mandatory Primary Key.
USER_GUID text The latest user Guid of the user.
USERNAME text The latest principal username of the user.

RA_CUSTOMER_TRX_ALL

This table contains information about customer transactions. It records various types of transactions, including invoices, credit memos, debit memos, and other receivable transactions.

Field Type Description
CUSTOMER_TRX_ID text Transaction identifier.(Primary key)
CREATED_BY text Who column: indicates the user who created the row.
CREATION_DATE datetime Who column: indicates the date and time of the creation of the row.
DOCUMENT_CREATION_DATE datetime Date when the document is created in the source application.
EXCHANGE_DATE datetime Currency conversion date.
INVOICE_CURRENCY_CODE text Invoice currency code.
LEGAL_ENTITY_ID text Legal entity identifier.
PREVIOUS_CUSTOMER_TRX_ID text Previous transaction identifier.
ORG_ID text Indicates the identifier of the business unit associated to the row.
POSTING_CONTROL_ID text The latest principal username of the user.
RECEIPT_METHOD_ID text Posting status identifier.
TERM_ID text Payment term identifier.
TERM_DUE_DATE datetime Payment term due date identifier.
TRX_CLASS text Class of the transaction.
TRX_DATE datetime Transaction date.
TRX_NUMBER text Transaction number.

RA_CUSTOMER_TRX_LINES_ALL

This table contains detailed information about transaction lines within customer transactions. It records individual line items associated with invoices, credit memos, debit memos, and other receivable transactions.

Field Type Description
CUSTOMER_TRX_LINE_ID text Transaction line identifier.(Primary key)
CUSTOMER_TRX_ID text Transaction identifier.
CREATED_BY text Who column: indicates the user who created the row.
CREATION_DATE datetime Who column: indicates the date and time of the creation of the row.
DESCRIPTION text Line description.
EXTENDED_AMOUNT double Transaction line amount.
ORG_ID text Indicates the identifier of the business unit associated to the row.
QUANTITY_INVOICED double Quantity of invoice line.
INTERFACE_LINE_ATTRIBUTE7 text Interface line attribute value.
INVENTORY_ITEM_ID text Inventory item identifier. Mutually exclusive with the column MEMO_LINE_ID.
PREVIOUS_CUSTOMER_TRX_LINE_ID text Credit memo lines invoice line identifier.
LINE_TYPE text Receivables lookup code for STD_LINE_TYPE.
WAREHOUSE_ID text Foreign key to the HR_ORGANIZATIONS table. The warehouse identifies the ship-from location and can be used to control taxation.

RA_TERMS_B

This table contains information about payment terms. Payment terms define the conditions under which a customer must pay for goods or services purchased from a company.

Field Type Description
TERM_ID text Payment term identifier.(Primary key)
NAME text Payment term name.

RA_TERMS_LINES

This table contains the line-level details within payment terms. It specify the conditions and terms that apply to individual invoice lines or transactions.

Field Type Description
SEQUENCE_NUM text Installment number.(Primary key)
TERM_ID text Payment term identifier.(Primary key)
DUE_DAYS double Number of days until due.

RA_TERMS_LINES_DISCOUNTS

This table contains the information about line-level discounts associated with payment terms.

Field Type Description
LAST_UPDATED_BY text Who column: indicates the user who last updated the row.(Primary key)
LAST_UPDATE_DATE datetime Who column: indicates the date and time of the last update of the row.(Primary key)
TERMS_LINES_DISCOUNT_ID text Payment term discount identifier..(Primary key)
DISCOUNT_PERCENT double A percent to indicate the discount rate that customers with this payment term can take for this installment if they pay on or before this payment schedule lines due date.
DISCOUNT_DAYS double The number of days after the invoices transaction date that this discount is available to your customer
TERM_ID text Foreign key to the RA_TERMS table.

WSH_DELIVERY_ASSIGNMENTS

This table stores information related to the assignment of delivery tasks to specific resources or entities within the delivery process.

Field Type Description
DELIVERY_ASSIGNMENT_ID text Primary Key for assignment of a shipment line to a shipment.(Primary key)
CREATION_DATE datetime Who column: indicates the date and time of the creation of the row.
DELIVERY_ID text Foreign Key to WSH_NEW_DELIVERIES.
DELIVERY_DETAIL_ID text Foreign key to WSH_DELIVERY_DETAILS.

WSH_DELIVERY_DETAILS

This table stores information about the delivery specifics associated with each shipment, such as shipping addresses, delivery dates, carrier information, and tracking details.

Field Type Description
DELIVERY_DETAIL_ID text Primary Key to identify delivery line.(Primary key)
CREATED_BY text Who column: indicates the user who created the row.
CREATION_DATE datetime Who column: indicates the date and time of the creation of the row.
CURRENCY_CODE text Currency code for the unit price.
DATE_SCHEDULED datetime Date on which the line is scheduled.
DELIVERED_QUANTITY double Delivered quantity in requested quantity unit of measure.
FOB_CODE text Code that determines freight on board.
FREIGHT_TERMS_CODE text Code that determines the freight terms.
ITEM_DESCRIPTION text Description of the item on the shipment line.
INVENTORY_ITEM_ID text Foreign key to EGP_SYSTEM_ITEMS.
PRODUCT_TYPE text Tax Determinant: Product type code of the inventory item. Possible values are GOODS and SERVICES.
SALES_ORDER_NUMBER text Sales Order number from order capture system.
SALES_ORDER_LINE_NUMBER text Sales Order line number from order capture system.
SHIP_FROM_LOCATION_ID text Location identifier of shipping from warehouse.
SHIPPED_QUANTITY double Shipped quantity in requested quantity unit of measure.
SOURCE_SHIPMENT_ID text Identifies the shipment from the source system.
SELLING_PRICE double Saves the actual selling price as opposed to UNIT_PRICE.
SUBINVENTORY text Subinventory of item for the shipment line.
UNIT_PRICE double Unit price for the shipment line.

WSH_EXCEPTIONS

This table stores information about exceptions encountered during various stages of order processing and shipment execution.

Field Type Description
EXCEPTION_ID text Primary key of the shipping exception.(Primary key)
CREATED_BY text Who column: indicates the user who created the row.
CREATION_DATE datetime Who column: indicates the date and time of the creation of the row.
DELIVERY_ID text The delivery id associated with the exception.
DELIVERY_DETAIL_ID text The delivery detail id associated with the exception.
MESSAGE text Error message field will be populated if an error is encountered while processing the Exception.

WSH_NEW_DELIVERIES

This table stores information about shipments that have been created and are ready to be processed and executed.

Field Type Description
DELIVERY_ID text Primary Key to identify delivery.(Primary key)
CREATED_BY text Who column: indicates the user who created the row.
CREATION_DATE datetime Who column: indicates the date and time of the creation of the row.
DELIVERY_TYPE text Type of delivery - STANDARD, CONSOLIDATED.
ORGANIZATION_ID text This identifies the inventory organization.

XLE_ENTITY_PROFILES

This table contains related to entity profiles, which represent various entities such as legal entities, business units, and other organizational units within an enterprise.

Field Type Description
LEGAL_ENTITY_ID text Unique identifier of the legal entity.(Primary key)
NAME text Name of the legal entity that belongs to your own corporate structure (enterprise).

Design specifications

Entities

Entity Transactional tables Master data tables
Accounting documents Invoices, Ar_cash_receipts_all, Ar_receivable_applications_all Ar_receipt_methods
Deliveries Wsh_new_deliveries, Wsh_delivery_assignments, Wsh_delivery_details Hr_organization_units_f_tl
Delivery items Wsh_delivery_details, Wsh_delivery_assignments, Doo_fulfill_lines_all Currency_exchange_rates, Hr_locations, Egp_system_items, Egp_item_classes_b
Invoice cancellations N/A N/A
Invoice items Ra_customer_trx_lines_all, Wsh_delivery_assignments Currency_exchange_rates, Egp_system_items, Egp_item_classes_b, Hr_organization_units
Invoices Ra_customer_trx_all, Ar_cash_receipts_all Ar_receipt_methods, Ar_receivable_applications_all, Ra_terms_lines_discounts, Ra_terms_lines, Ra_terms_b, Xle_entity_profiles
Payments Ar_receivable_applications_all, Ar_cash_receipts_all, Ra_customer_trx_all Currency_exchange_rates, Xle_entity_profiles
Sales order items Doo_lines_all, Doo_fulfill_lines_all, Doo_headers_all Currency_exchange_rates, Egp_system_items, Egp_item_classes_b, Fnd_territories_b, Fnd_territories_tl, Hz_parties, Hr_organization_units_f_tl, Inv_units_of_measure_b, Inv_units_of_measure_tl, Msc_payment_terms_tl
Sales orders Doo_headers_all Xle_entity_profiles, Hz_parties, Fnd_territories_b, Fnd_lookup_values_tl, Hr_organization_units_f_tl

Activities

Activities can be created in the following ways:

  1. Activities that can be created based on the transactional table information (i.e. DOO_HEADERS_ALL, DOO_LINES_ALL).
  2. Activities that can be created based on the historical tables information (i.e. created and the names of the models derived from the activity names.DOO_STEP_INSTANCE_DETAILS)

1: Activity information that can be created from transactional tables.

Activities were created from transactional tables whenever there was enough information available (i.e. event_end and user information)

Each of these activities have a specific DBT model where they're being created and the names of the models derived from the activity names.

Entity Activity Transactional table Master Data tables & Seed files
Sales orders Create Sales Order DOO_HEADERS_ALL Activity_configuration_raw, Setup_users
Sales orders Submit Sales Order DOO_HEADERS_ALL Activity_configuration_raw, Setup_users
Sales orders Request Sales Order Approval FA_FUSION_SOAINFRA_WFTASK Activity_configuration_raw, Setup_users
Sales orders Approve Sales Order FA_FUSION_SOAINFRA_WFTASK Activity_configuration_raw, Setup_users
Sales orders Reject Sales Order FA_FUSION_SOAINFRA_WFTASK Activity_configuration_raw, Setup_users
Sales orders Hold Sales Order DOO_HOLD_INSTANCES DOO_HOLD_CODES_TL, Activity_configuration_raw, Setup_users
Sales orders Release Sales Order DOO_HOLD_INSTANCES DOO_HOLD_CODES_TL, PER_USERS, Activity_configuration_raw, Setup_users
Sales order items Create Sales Order Item DOO_LINES_ALL Activity_configuration_raw, Setup_users
Sales order items Hold Sales Order Item DOO_HOLD_INSTANCES DOO_HOLD_CODES_TL, Activity_configuration_raw, Setup_users
Sales order items Release Sales Order Item DOO_HOLD_INSTANCES DOO_HOLD_CODES_TL, PER_USERS, Activity_configuration_raw, Setup_users
Sales order items Create Sales Order Item Price Adjustment DOO_MANUAL_PRICE_ADJUSTMENTS, DOO_FULFILL_LINES_ALL FND_LOOKUPS,Activity_configuration_raw, Setup_users
Deliveries Create Delivery WSH_NEW_DELIVERIES Activity_configuration_raw, Setup_users
Deliveries Create Delivery Exception WSH_EXCEPTIONS Activity_configuration_raw, Setup_users
Delivery items Create Delivery Item WSH_DELIVERY_DETAILS Activity_configuration_raw, Setup_users
Delivery item Create Delivery Item WSH_EXCEPTIONS Activity_configuration_raw, Setup_users
Invoices Create Invoice RA_CUSTOMER_TRX_ALL Activity_configuration_raw, Setup_users
Invoices Apply Credit Memo RA_CUSTOMER_TRX_ALL Activity_configuration_raw, Setup_users
Invoices Apply Debit Memo RA_CUSTOMER_TRX_ALL Activity_configuration_raw, Setup_users
Invoices Create Invoice Adjustment AR_ADJUSTMENTS_ALL AR_LOOKUPS, Activity_configuration_raw, Setup_users
Invoice items Create Invoice Item RA_CUSTOMER_TRX_ALL Activity_configuration_raw, Setup_users
Payments Create Payment AR_RECEIVABLE_APPLICATIONS_ALL, AR_CASH_RECEIPTS_ALL Activity_configuration_raw, Setup_users
Payments Apply Payment AR_RECEIVABLE_APPLICATIONS_ALL, AR_CASH_RECEIPTS_ALL Activity_configuration_raw, Setup_users
Payments Unapply Payment AR_RECEIVABLE_APPLICATIONS_ALL, AR_CASH_RECEIPTS_ALL Activity_configuration_raw, Setup_users
Payments Reverse Payment AR_RECEIVABLE_APPLICATIONS_ALL, AR_CASH_RECEIPTS_ALL Activity_configuration_raw, Setup_users

2: Activity information that can be created from historical tables.

All the activities derived from historical Oracle Cloud Order-to-Cash tables were created using a specific naming approach that could be summarized as follows:

'Change' + [Entity_name] + ' status to ' + [Status_description]
Example: Change Sales Order Item status to Completed

The most standard activities were set up in advance in the Activity_configuration_raw seed file mentioned in section Customizing the Transformations. For new statuses that derives in new activities, the analyst should add them. The lack of setups for a particular activity will not break the model.

The following models belong to the Approach 2:

Below, there are examples of standard statuses, the entity, the activity name assigned to them and their descriptions:

Entity Status Activity name Description
Sales Order Items Awaiting Billing Change Sales Order Item status to Awaiting Billing The sales order item is awaiting to be billed.

Design details

Each of the next subsections will clarify the transformations.

Oracle Cloud ERD vs App Template ERD

The graphics below shows the Oracle Cloud ERD and the App Template ERD:

Oracle Cloud ERD

App Template ERD

As could be seen, Oracle cloud contains some intermediate tables between entities, which are not supported by the App Template ERD. The tables are:

Also, there is no Accounting documents entity nor Invoice cancellations entity in Oracle Cloud. In the next section, all specific logics created to match the Oracle Cloud ERD to the expected relationships in App template will be described.

Supporting tables

Currency_exchange_rates Companies may use more than one currency exchange type, therefore there may be more than 1 conversion rate for the same day. In Oracle Cloud there could be multiple currency conversion types.

The predefined types of conversion rates are: spot, corporate, fixed and user. They are selected based on how fluctuating a currency could be.

The next list contains their main uses:

And the system does not allow users to work with any non domestic currency that does not have a record in the GL_DAILY_RATES table.

Though the system allows users to have multiple conversion types for the same currency conversion it is very uncommon.

However, the logic must work for any possible scenario. Thus, to avoid generating duplicates, an average is calculated for each day. In other words, the average is done to collapse multiple conversion types into one.

Delivery_item_relationship This supporting model will select the last Delivery ID assigned to each Delivery item ID. This model will be used later on in the Deliveries base entity to populate the fields Incoterms and Shipping conditions.

Delivery_item_header_relationship The relationship between the delivery item and its header is stored in the Wsh_delivery_assignments Oracle Cloud table. This supporting model will select the last delivery header assigned to each delivery item to create a delivery item-header entity relationship that fits into the one expected in the App template. The supporting model will be used later on in the Delivery_items_base to populate the field Delivery_ID.

Discount_calculation_1 and Discount_calculation_2 In Oracle Cloud, the discounts are related to the Payment term set for the Invoice. Also, they are stored in separate tables (RA_TERMS_LINES and RA_TERMS_LINES_DISCOUNTS) and could be more than two. These supporting models will retrieve only the first two discounts information associated with the Invoice in order to match with the expected App template fields (Discount percentage 1 and 2 and Discount period 1 and 2). The supporting models will be used later on in the Invoices_base entity.

Fulfillment_line_grouping Oracle Cloud stores further information and also the relationship between Sales order items and Delivery Items in a table called Doo_fulfill_lines_all. One Sales order item could have multiple records in this table. This supporting model will ensure that the latest information for the line (e.g. payment term, storage location, profit center, customer) will be brought in the Sales_order_items_base entity.

Invoice_item_delivery_item_relationship There is no direct relationship between Invoice Items transactional table (RA_CUSTOMER_TRX_LINES_ALL) and the Delivery Items transactional table (WSH_DELIVERY_DETAILS).

The Invoice items table, Ra_customer_trx_lines_all_input, only contains information regarding delivery headers, thus, it is needed to bring Wsh_delivery_assignments table to get all Delivery detail IDs.

When doing that the logic is bringing much more records than necessary, and it is needed to filter them by the material ID (Inventory_item_id) that can be found in table Wsh_delivery_details table. For those cases that have the same Inventory_item_id for the same delivery header it will be selected just one of them to avoid duplicates.

It does not matter which of them is selected, the logic will always connect the invoice with the correct sales order item anyways. This supporting model will be used later on to populate the field Delivery_item_ID in the Invoice_items_base entity.

Below is an example of how the logic works:

The logic will group the records and will take Delivery D1 ID for Paper items, and, as the logic orders by the CREATION_DATE, when joining it will only do it with one record, the 19 March Paper Delivery ID, selecting its CUSTOMER_TRX_LINE_ID In other words, each Customer_trx_line_id within each combination of Delivery_id and Inventory_item_id will appear only once in the final result set, corresponding to the most recent delivery assignment for that specific combination.

_Note:_the delivery ID is currently stored in the Invoice Items transactional table in a field called "INTERFACE_LINE_ATTRIBUTE7". There is no certainty if this will be constant across every single Oracle Cloud Implementation.

Latest_acceptable_date

This supporting model will bring the latest acceptable_ship_date associated with the sales order item, as this information is stored in the Doo_fulfill_lines_all table. This supporting model will be used later on to populate the field Latest_expected_delivery_date in the Sales_order_items_base entity.

Payment_information This supporting model will bring the latest information related to the payments applied to an invoice. The attributes will be used later on in the Accounting_documents_base entity.

Payment_method This supporting model retrieves the latest receipt application done the invoice based on the Ar_receivable_applications_all_input table. This is needed to populate the field ‘Payment method’ which is stored in the previously mentioned table.

Planned_delivery_date The Deliveries_base field Planned_delivery_date will be populated with the latest information stored in the Delivery items table (Wsh_delivery_details) associated.

Entities

Accounting_documents_base

This entity does not exist in Oracle Cloud so CUSTOMER_TRX_ID from RA_CUSTOMER_TRX_ALL which is used as both Invoice ID and Accounting document ID.

In Oracle Cloud, the standard process for invoicing is called 'Auto Invoice', where the information to create the invoice in is taken directly from the Order Management module. An amount smaller than the Order amount would retrieve a system error at least multiple specific set ups were done.

The lines amount of the invoice created, could only be modified inside the transaction of the invoice. For that reason, the table RA_CUSTOMER_TRX_ALL does not contain a field related to the total amount of the invoice and the value field of the model was left null.

Deliveries_base

Deliveries table WSH_NEW_DELIVERIES is used for creating this entity. Also, in order to complete the information related to the planned delivery date, a specific logic brings the date scheduled for the delivery of the line based on WSH_DELIVERY_DETAILS table. In case of more than one line exists, the latest scheduled date is taken.

Delivery_items_base

Transactional table WSH_DELIVERY_DETAILS was used as the backbone for this entity. In Oracle Cloud, the relationship between the Sales order item and the Delivery item is stored in the table DOO_FULFILL_LINES_ALL, where the Sales Order item ID is not a primary key.

For that reason, a join is done between the fields WSH_DELIVERY_DETAILS.SOURCE_SHIPMENT_ID which means the Fulfill line ID and DOO_FULFILL_LINES_ALL.FULFILL_LINE_ID to take only one Sales Order item ID for each Delivery Item ID.

This way there is no duplication as the Sales Order Item ID (DOO_FULFILL_LINES_ALL.LINE_ID) is taken from this join done on the primary key of the DOO_FULFILL_LINES_ALL table (FULFILL_LINE_ID).

Invoices_base

Oracle Cloud stores Invoices transactional information in the RA_CUSTOMER_TRX_ALL table. The Invoice discounts information is stored in a separate table called RA_TERMS_LINES_DISCOUNTS. Specific logics were created to bring the first two discounts associated to an invoice.

Note 1: It is possible to have more than two discounts in an invoice if the payment term associated allows it, if that is the case, the rest of the discounts will be missed.

Note 2: There are special cases where the document in the RA_CUSTOMER_TRX_ALL table is a credit/debit memo. When a credit/debit memo is done, this is not directly related to the invoice ID in the mentioned table, therefore they were filtered out of the Invoices_base entity (at input level).

Invoice_items_base

This model was created using the RA_CUSTOMER_TRX_LINES_ALL transactional table. The relationship between the Invoice item and the Delivery (at header level) is stored in the WSH_DELIVERY_ASSIGNMENTS table. In that table, the delivery item ID is not a primary key. For that reason, a specific logic to bring only one per Invoice item was designed.

Payments_base

This entity is created based in the Oracle Cloud payment applications table (AR_RECEIVABLE_APPLICATIONS_ALL). When a receipt is created, it should be applied to the corresponding Invoice or Invoices. The mentioned table stores this applications and also the reverses (un-applications) done. The receipt reverse could be done also at header level, and this table will create a record to un-apply each application previously done.

When a receipt is created in Oracle Cloud, it is done without being associated to any Invoice. At this point, a record is created in the AR_CASH_RECEIPTS_ALL table and also a record in the AR_RECEIVABLE_APPLICATIONS_ALL table but without any Invoice ID associated in the field APPLIED_CUSTOMER_TRX_ID. Later on, when the 'application' of the receipt is done to the Invoice, a record is created in the AR_RECEIVABLE_APPLICATIONS_ALL table, and this record is associated to the Invoice ID which the receipt has been applied to.

In the front-end of the system, the user can see in the same screen the Receipt and its applications. Also, it is possible to apply one receipt to multiple Invoices.

A Cash receipt ID without an application associated will not be connected to any Invoice. Also, the Application ID itself will not bring enough information about the payment received. The approach to create the concatenated ID had taken into account all these information.

Sales_orders_base

Transactional table DOO_HEADERS_ALL was used as the backbone for this entity. In Oracle Cloud, the prefix DOO means Distributed Order Orchestration.

Sales_order_items_base

Sales Order Items table DOO_LINES_ALL is used for creating this entity. Also, the table DOO_FULFILL_LINES_ALL is used to include more information on the fulfillment line for a DOO Sales Order. One Sales Order Item could have multiple fulfillment lines, specific logics were created to avoid duplications when bringing attributes.

Events

Delivery_creation_events

All delivery creation events are tracked with the transactional table WSH_NEW_DELIVERIES. Seed files are used to add specific attributes. Please see the Customizing the Transformations section.

Delivery_item_creation_events

The delivery item creation events are created the transactional table WSH_DELIVERY_DETAILS. Seed files are used to add specific attributes. Please see the Customizing the Transformations section.

Delivery_exception_events

This model is created based on the WSH_EXCEPTIONS using a specific filter to bring exceptions done only at item level.

Invoice_adjustment_events

In Oracle Cloud, it is possible to create an adjustment at the invoice, For example, tax, freight, and finance charges associated. This model is created using the AR_ADJUSTMENTS_ALL transactional table which contains records for all invoice adjustments.

Invoice_creation_events

The transactional table RA_CUSTOMER_TRX_ALL is used to generate this model, also seed files are used to add specific attributes.

Invoice_application_events

The transactional table AR_RECEIVABLE_APPLICATIONS_ALL is used to generate this model, specific logics were created to identify if a credit/debit memo has been applied to the invoice.

Invoice_item_creation_events

The table RA_CUSTOMER_TRX_LINES_ALL is used to create this model. Seed files are used to add specific attributes. Please see the Customizing the Transformations section.

Payment_creation_events

The receipt creation is stored in the AR_CASH_RECEIPTS_ALL transactional table and this is used to create this model. This model will only track the payments associated to invoices, for that reason the Payment ID is taken from the AR_RECEIVABLE_APPLICATIONS_ALL table, by doing an inner join.

Payment_application_events

This model is created based on the AR_RECEIVABLE_APPLICATIONS_ALL which stores all payment applications done to an invoice. The mentioned table will be filtered only to bring the records that actually apply to invoices (where Invoice ID is not null). Also, this model will contain applies and un-applies, that can only be identified by the field Amount_applied (when it is equal or grater to zero, it belongs to a payment application, otherwise, negative values will belong to un-applications of the payments).

Payment_reverse_events

The reverse of a payment (only possible to do at receipt header level) is stored in the AR_CASH_RECEIPTS_ALL transactional table and this is used to create this model. This model will only track the payments associated to invoices, for that reason the Payment ID is taken from the AR_RECEIVABLE_APPLICATIONS_ALL table, by doing an inner join. Also, this model contains specific logics to identify the record that actually correspond to a reverse.

Sales_order_approval_events

This model contains all the events of sales order approvals.

In Oracle Cloud, approvals happen at header level, so the logic was created following that approach. The process starts if the sales order approvals workflow is enabled in the Oracle Cloud Workflow Management area. This workflow generates the list of approvers based on the default rules. Approval notifications are sent to the first set of approvers on the list. After they respond, the notifications go to the next set of approvers. This process repeats until all approvals are complete. The approvers can either approve or reject the entire invoice.

The table used to create this model is FA_FUSION_SOAINFRA_WFTASK which belongs to the Oracle Cloud Workflow Management system. As this table contains workflows for different purposes, specific filters were applied to bring only sales order approvals.

Sales_order_approval_events

Sales order approval requests are created based on the FA_FUSION_SOAINFRA_WFTASK using specific filters to bring only approval requests for the sales order.

Sales_order_creation_events

DOO_HEADERS_ALL which is the sales orders main transactional table is used to create this model.

Sales_order_reject_events

Sales order rejects are created based on the FA_FUSION_SOAINFRA_WFTASK using specific filters to bring only the rejections for the sales order.

Sales_order_release_events

The DOO_HOLD_INSTANCES which is the sales orders main holds table is used to create this model and a specific filter to bring only holds at header level is done.

Sales_order_submission_events

DOO_HEADERS_ALL which is the sales orders main transactional table is used to create this model.

Sales_order_item_change_events

This model follows the second approach in the Activities section to be created. The historical tables used is DOO_STEP_INSTANCE_DETAILS.

Sales_order_item_creation_events

DOO_LINES_ALL which is the sales orders main transactional table is used to create this model.

Sales_order_item_hold_events

DOO_HOLD_INSTANCES which is the sales orders main holds table is used to create this model and a specific filter to bring only holds at item level is done.

Sales_order_item_release_events

The DOO_HOLD_INSTANCES which is the sales orders main holds table is used to create this model and a specific filter to bring only holds at item level is done.

Sales_order_item_price_adjustments_events

As mentioned before, in Oracle Cloud it is possible to create adjustments. This model uses the DOO_PRICE_ADJUSTMENTS transactional table which contains records for all sales order item price adjustments due to reasons as discounts, promotions for a fulfillment line, etc.

Automated flag in Event models

The event models use a logic to flag them as automated.

First, the logic checks if the activity is flagged as Automated = 'Y' in the seed file. If that is the case, the event is considered automated. If not, the logic checks if the user contains "FUSION_APPS" which means that it is an automated user in Oracle Cloud. If so, then the event is flagged as automated.


Customizing the transformations

Seed files

Activity_configuration_raw

This seed file is used when it is difficult to automatically populate activity fields like ACTIVITY_CATEGORY, ACTIVITY_ORDER and AUTOMATED_ACTIVITY. This information should be provided by the user in the activity_configuration_raw file.

Field Type Description Example
Activity varchar(255) The name of the activity. Create Sales Order
Activity_category varchar(255) The type of activity that takes place (valid values Change, Set block, Remove block or NULL) NULL
Activity_order varchar(255) The number that defines in which order activities are executed in case they have the same Event end 1
Automated_activity varchar(255) Flag that defines automated activities. Valid values: Y, N or blank. If 'Y' then this activity will be considered automated Y
Activity_trigger varchar(255) Field describing the reason for the executed activity Sales order is approved due to the workflow that was set in the system.
Event_start varchar(255) The timestamp associated with the start of executing the event. 2024-02-18 16:33:22 GMT

Setup_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_users.csv seed file contains the information of all automated users that need to be flagged manually. There is no need to add all users that contain with 'FUSION_APPS' as the logic is already flagging them as Automated.

Field Type Description Example
User varchar(255) Username as stored in transactional tables SCM_IMPL
Automated_user varchar(255) Y or N(blank) value to identify automated users Y
User_department varchar(255) Department associated to the user Sales office
User_function varchar(255) Role associated to the user Seller
User_type varchar(255) Categorization associated to the user System user

DBT Variables

Variable Type Description
date_format string / integer Specifies the date format which will be used when converting to DateTime type.
datetime_format string / integer Determines the format for displaying dates. If the target type is 'snowflake', it uses the format 'YYYY-MM-DD'; otherwise, it defaults to 23.
display_currency string Currency code used for all "value" attributes. All amounts (Values) will be converted to the selected display_currency.
lookup_type_values_tl string Type that brings the expected distribution channel associated to the sales order.
lookup_type string Type that brings the expected adjustment type associated.
language string Language used to translate descriptions.

Limitations, known issues, common problems

Limitations

Loading of files

Loading too many records could overload the Oracle Cloud system and can interrupt the creation of the CSV file from the report. To avoid this the report should be filtered by a time window. Example: Create the report and filter by 01-01-2024 until 01-06-2024 and extract the first CSV file. After, open the same report and modify the date filter to 02-06-2024 until 31-12-2024. This will generate REPORT.csv and REPORT (1).csv and next step is run the following power shell script:

# PATH_FOR_FILE_N is the path where the file is located. For example: C:\Users\your_user\Desktop\your_folder\REPORT.csv and C:\Users\your_user\Desktop\your_folder\REPORT (1).csv # The files path needs to be separate by comma and you can add N number of files. This script is used to merge multiple csv files into one. Get-Content 'PATH_FOR_REPORT', 'PATH_FOR_REPORT_2', 'PATH_FOR_REPORT_N'| Set-Content -Encoding ASCII REPORT_MERGED.csv

As the last step replace in the replicate to get REPORT_MERGED.csv instead of REPORT.csv as below:

REPLICATE [REPORT] SELECT * FROM [REPORT_MERGED];

Note:

REPLICATE [AP_HOLDS_ALL_R_AP_HOLDS_ALL_R] SELECT * FROM [AP_HOLDS_ALL_R_AP_HOLDS_ALL_R_MERGED];

Delivery ID - Delivery Item relationship

In Oracle Cloud a Delivery item ID can be linked to a Delivery ID and also a Parent Delivery ID. As the UiPath model for O2C only allows 1 link, this connector is only using the Delivery ID, disregarding any Parents.

Oracle Cloud updates

Though there is a high confidence that new versions of Oracle Cloud will still be compatible with the app template, it is not possible to predict what changes will surface and what their impact could be.

Connection between Invoice items and Deliveries

The "Interface_line_attribute7" field is the only field available to connect Invoice items and Deliveries. This field represents the Delivery ID in the Invoice Items table (RA_CUSTOMER_TRX_LINES_ALL). As the naming of the field is not representing exactly the 'Delivery ID' (e.g. it is not called DELIVERY_ID), it is uncertain if it could vary across the Oracle Cloud implementations or versions.

Known issues

Display currency

The currency conversion logic takes into account the "Transactional Currency" (as this is the only one available in the tables and also the one used during the transaction performed). When a transaction is done in a different currency (e.g. Transaction Currency = EUR and Display Currency = USD), Oracle Cloud needs to have the conversion rate available (stored in the GL_DAILY_RATES table). In the rare case where the client does not have the same currency codes across its multiple companies, it may happen that currency conversion rates are not available for converting everything into a single currency. For more details related to how Oracle Cloud works with currency conversion, please refer to Design Details.

CSV files names

The name of the "raw" tables match the name of the CSV file created during extraction. CSV files take the name from the report that generated them but it is a known issue that Oracle duplicates the name of the report when naming the csv file. It is possible to rename the files manually after the extraction but it was decided to keep everything as standard as possible.

Oracle Cloud ERD vs App Template ERD

Please note that there are many differences between which the entities are and how the entities are connected. Specific logics previously described in Design Details section were created to make the Connector work for App Template.

Common problems

Blanks replaced by null

The app template was built expecting that all blanks were converted to nulls by CDATA or any other tool used for the extraction purposes. Analysts must confirm that CDATA is properly set to do this replacement.