Stands for Data model
Stands for Report
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.
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.
Stands for Data model
Stands for Report
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.
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.
To create the Job in CData make sure to follow the steps below.
In the Advanced tab in the Job Settings panel, define the following settings:
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"/>
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.
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.
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 |
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. |
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. |
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. |
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. |
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. |
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. |
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. |
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. |
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. |
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. |
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. |
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. |
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. |
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. |
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. |
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. |
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. |
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. |
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. |
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. |
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. |
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. |
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. |
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. |
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) |
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. |
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) |
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. |
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 |
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. |
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. |
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. |
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. |
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. |
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. |
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. |
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. |
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. |
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. |
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). |
| 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 can be created in the following ways:
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 |
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. |
Each of the next subsections will clarify the transformations.
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.
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.
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.
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.
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 |
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 |
| 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. |
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];
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.
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.
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.
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.
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.
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.
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.