Oracle EBS Order-to-Cash


Extraction

This app template uses Oracle E-Business Suite (EBS), versions 12.2.1 or higher as source system.

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

System configuration

To be able to extract data from Oracle EBS, a user should be created with permissions to read the data. Next to the username and corresponding password, the port used to connect to the server hosting the Oracle database and the service name of the Oracle database needs to be available. You can obtain this value by querying global_name (select * from global_name) using the Oracle SQL*PLUS command line.

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 environment variables in the extraction job

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

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

Variable Description Comment
start_extraction_date Defines first date for which data will be extracted. Mandatory
end_extraction_date Last date for which data will be extracted.

In order to setup the environment variables:

Step Action
1 Go to the Events tab in the Job Settings panel of the job you are creating
2 Edit the Pre-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 Click on Save Changes.
<!-- Modify environment variables here. --> <!-- Variable start_extraction_date must be populated.--> <api:set attr="out.env:start_extraction_date" value= "01/jan/2000" /> <!-- In case a specific end date is needed, replace the value string with the required date in the Date format listed in Oracle EBS. --> <!-- i.e api:set attr="out.env:end_extraction_date" value="01/jan/2000" --> <api:set attr="out.env:end_extraction_date" value= "01/jan/3000" /> <api:push item="out" />

By default, end_extraction_date will default to today's date. start_extraction_date must always be populated.

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 Tables tab and paste the following queries (each query needs to maintain the semicolon at the end). Make sure you save all changes.

Use the following custom query when creating the job:

REPLICATE [AR_ADJUSTMENTS_ALL] SELECT [ADJUSTMENT_ID], [AMOUNT], TO_CHAR([APPLY_DATE], 'yyyy-MM-dd hh:mm:ss') AS [APPLY_DATE], [AUTOMATICALLY_GENERATED], [CREATED_BY], [CUSTOMER_TRX_ID], [ORG_ID], [STATUS] FROM [AR].[AR_ADJUSTMENTS_ALL] WHERE ([CREATION_DATE] >= '{env:start_extraction_date}') AND ([CREATION_DATE] <= '{env:end_extraction_date}'); REPLICATE [AR_CASH_RECEIPT_HISTORY_ALL] SELECT [AMOUNT], [CASH_RECEIPT_HISTORY_ID], [CASH_RECEIPT_ID], TO_CHAR([CREATION_DATE], 'yyyy-MM-dd hh:mm:ss') AS [CREATION_DATE], [ORG_ID], [STATUS], [CREATED_BY] FROM [AR].[AR_CASH_RECEIPT_HISTORY_ALL] WHERE ([CREATION_DATE] >= '{env:start_extraction_date}') AND ([CREATION_DATE] <= '{env:end_extraction_date}'); REPLICATE [AR_CASH_RECEIPTS_ALL] SELECT [AMOUNT], TO_CHAR([RECEIPT_DATE], 'yyyy-MM-dd hh:mm:ss') AS [RECEIPT_DATE], [CASH_RECEIPT_ID], TO_CHAR([CREATION_DATE], 'yyyy-MM-dd hh:mm:ss') AS [CREATION_DATE], [ORG_ID], TO_CHAR([REVERSAL_DATE], 'yyyy-MM-dd hh:mm:ss') AS [REVERSAL_DATE], [STATUS],[CREATED_BY] FROM [AR].[AR_CASH_RECEIPTS_ALL] WHERE ([CREATION_DATE] >= '{env:start_extraction_date}') AND ([CREATION_DATE] <= '{env:end_extraction_date}'); REPLICATE [AR_RECEIVABLE_APPLICATIONS_ALL] SELECT [AMOUNT_APPLIED], [APPLICATION_TYPE], [APPLIED_CUSTOMER_TRX_ID], [APPLIED_CUSTOMER_TRX_LINE_ID], [CASH_RECEIPT_HISTORY_ID], [CASH_RECEIPT_ID], TO_CHAR([CREATION_DATE], 'yyyy-MM-dd hh:mm:ss') AS [CREATION_DATE], [CUSTOMER_TRX_ID], [ORG_ID], [RECEIVABLE_APPLICATION_ID], [STATUS], [CREATED_BY] FROM [AR].[AR_RECEIVABLE_APPLICATIONS_ALL] WHERE ([CREATION_DATE] >= '{env:start_extraction_date}') AND ([CREATION_DATE] <= '{env:end_extraction_date}'); REPLICATE [FND_USER] SELECT [USER_ID], [USER_NAME], [DESCRIPTION] FROM [APPLSYS].[FND_USER]; REPLICATE [HR_ALL_ORGANIZATION_UNITS] SELECT [ORGANIZATION_ID], [LOCATION_ID], [NAME] FROM [HR].[HR_ALL_ORGANIZATION_UNITS]; REPLICATE [HR_LOCATIONS_ALL] SELECT [COUNTRY], [LOCATION_ID], [REGION_1], [REGION_2], [REGION_3] FROM [HR].[HR_LOCATIONS_ALL]; REPLICATE [HZ_CUST_ACCOUNTS] SELECT [CUST_ACCOUNT_ID], [PARTY_ID] FROM [AR].[HZ_CUST_ACCOUNTS]; REPLICATE [HZ_PARTIES] SELECT [COUNTRY], [COUNTY], [PARTY_ID], [PARTY_NAME] FROM [AR].[HZ_PARTIES]; REPLICATE [MTL_MATERIAL_TRANSACTIONS] SELECT [CREATED_BY], TO_CHAR([CREATION_DATE], 'yyyy-MM-dd hh:mm:ss') AS [CREATION_DATE], [MOVE_ORDER_LINE_ID], [ORGANIZATION_ID], [SHIPMENT_NUMBER], [SOURCE_LINE_ID], [TRANSACTION_QUANTITY], [TRANSACTION_SOURCE_TYPE_ID], [TRANSACTION_TYPE_ID] FROM [INV].[MTL_MATERIAL_TRANSACTIONS] WHERE ([CREATION_DATE] >= '{env:start_extraction_date}') AND ([CREATION_DATE] <= '{env:end_extraction_date}'); REPLICATE [MTL_TXN_REQUEST_LINES] SELECT [CREATED_BY], TO_CHAR([CREATION_DATE], 'yyyy-MM-dd hh:mm:ss') AS [CREATION_DATE], [INVENTORY_ITEM_ID], [LINE_ID], [ORGANIZATION_ID], [QUANTITY] FROM [INV].[MTL_TXN_REQUEST_LINES] WHERE ([CREATION_DATE] >= '{env:start_extraction_date}') AND ([CREATION_DATE] <= '{env:end_extraction_date}'); REPLICATE [OE_HOLD_RELEASES] SELECT [CREATED_BY], TO_CHAR([CREATION_DATE], 'yyyy-MM-dd hh:mm:ss') AS [CREATION_DATE], [HOLD_RELEASE_ID], [ORDER_HOLD_ID], [RELEASE_REASON_CODE] FROM [ONT].[OE_HOLD_RELEASES] WHERE ([CREATION_DATE] >= '{env:start_extraction_date}') AND ([CREATION_DATE] <= '{env:end_extraction_date}'); REPLICATE [OE_ORDER_HEADERS_ALL] SELECT TO_CHAR([BOOKED_DATE], 'yyyy-MM-dd hh:mm:ss') AS [BOOKED_DATE], [CONVERSION_RATE], TO_CHAR([CREATION_DATE], 'yyyy-MM-dd hh:mm:ss') AS [CREATION_DATE], [HEADER_ID], [ORDER_NUMBER], [ORDER_SOURCE_ID], [ORDER_TYPE_ID], [ORG_ID], TO_CHAR([QUOTE_DATE], 'yyyy-MM-dd hh:mm:ss') AS [QUOTE_DATE], [QUOTE_NUMBER], TO_CHAR([REQUEST_DATE], 'yyyy-MM-dd hh:mm:ss') AS [REQUEST_DATE], [SHIP_FROM_ORG_ID], [SOLD_TO_ORG_ID], [CREATED_BY] FROM [ONT].[OE_ORDER_HEADERS_ALL] WHERE ([CREATION_DATE] >= '{env:start_extraction_date}') AND ([CREATION_DATE] <= '{env:end_extraction_date}'); REPLICATE [OE_ORDER_HOLDS_ALL] SELECT [CREATED_BY], TO_CHAR([CREATION_DATE], 'yyyy-MM-dd hh:mm:ss') AS [CREATION_DATE], [HEADER_ID], [LINE_ID], [ORDER_HOLD_ID] FROM [ONT].[OE_ORDER_HOLDS_ALL] WHERE ([CREATION_DATE] >= '{env:start_extraction_date}') AND ([CREATION_DATE] <= '{env:end_extraction_date}'); REPLICATE [OE_ORDER_LINES_ALL] SELECT [CANCELLED_FLAG], [CANCELLED_QUANTITY], TO_CHAR([CREATION_DATE], 'yyyy-MM-dd hh:mm:ss') AS [CREATION_DATE], [HEADER_ID], [INVENTORY_ITEM_ID], [LINE_ID], [LINE_CATEGORY_CODE], [LINE_NUMBER], [LINE_TYPE_ID], [ORDERED_ITEM_ID], [ORDERED_QUANTITY], [ORG_ID], [PAYMENT_TERM_ID], TO_CHAR([PROMISE_DATE], 'yyyy-MM-dd hh:mm:ss') AS [PROMISE_DATE], [REFERENCE_CUSTOMER_TRX_LINE_ID], [SHIP_FROM_ORG_ID], [SHIP_TO_ORG_ID], [SHIPPING_QUANTITY], [UNIT_SELLING_PRICE], [CREATED_BY] FROM [ONT].[OE_ORDER_LINES_ALL] WHERE ([CREATION_DATE] >= '{env:start_extraction_date}') AND ([CREATION_DATE] <= '{env:end_extraction_date}'); REPLICATE [OE_ORDER_LINES_HISTORY] SELECT [HEADER_ID], [LINE_ID], [ORDERED_ITEM_ID], [ORDERED_QUANTITY], [ORG_ID], [HIST_CREATED_BY], TO_CHAR([HIST_CREATION_DATE], 'yyyy-MM-dd hh:mm:ss') AS [HIST_CREATION_DATE], [HIST_TYPE_CODE] FROM [ONT].[OE_ORDER_LINES_HISTORY] WHERE ([CREATION_DATE] >= '{env:start_extraction_date}') AND ([CREATION_DATE] <= '{env:end_extraction_date}'); REPLICATE [OE_PRICE_ADJUSTMENTS] SELECT [ADJUSTED_AMOUNT], [PRICE_ADJUSTMENT_ID], [AUTOMATIC_FLAG], [CREATED_BY], TO_CHAR([CREATION_DATE], 'yyyy-MM-dd hh:mm:ss') AS [CREATION_DATE], [LINE_ID], TO_CHAR([REDEEMED_DATE], 'yyyy-MM-dd hh:mm:ss') AS [REDEEMED_DATE], [REDEEMED_FLAG] FROM [ONT].[OE_PRICE_ADJUSTMENTS] WHERE ([CREATION_DATE] >= '{env:start_extraction_date}') AND ([CREATION_DATE] <= '{env:end_extraction_date}'); REPLICATE [RA_CUST_TRX_TYPES_ALL] SELECT [CUST_TRX_TYPE_ID], [ORG_ID], [TYPE] FROM [AR].[RA_CUST_TRX_TYPES_ALL]; REPLICATE [RA_CUSTOMER_TRX_ALL] SELECT [CUST_TRX_TYPE_ID], [COMPLETE_FLAG], TO_CHAR([CREATION_DATE], 'yyyy-MM-dd hh:mm:ss') AS [CREATION_DATE], [CUSTOMER_TRX_ID], [EXCHANGE_RATE], [INTERFACE_HEADER_ATTRIBUTE1], [INTERFACE_HEADER_CONTEXT], [INVOICE_CURRENCY_CODE], [ORG_ID], [TRX_NUMBER], [CREATED_BY] FROM [AR].[RA_CUSTOMER_TRX_ALL] WHERE ([CREATION_DATE] >= '{env:start_extraction_date}') AND ([CREATION_DATE] <= '{env:end_extraction_date}'); REPLICATE [RA_CUSTOMER_TRX_LINES_ALL] SELECT [CREATED_BY], TO_CHAR([CREATION_DATE], 'yyyy-MM-dd hh:mm:ss') AS [CREATION_DATE], [CUSTOMER_TRX_ID], [CUSTOMER_TRX_LINE_ID], [DESCRIPTION], [INTERFACE_LINE_ATTRIBUTE1], [INTERFACE_LINE_ATTRIBUTE6], [QUANTITY_INVOICED], [SET_OF_BOOKS_ID], [UNIT_SELLING_PRICE], [ORG_ID] FROM [AR].[RA_CUSTOMER_TRX_LINES_ALL] WHERE ([CREATION_DATE] >= '{env:start_extraction_date}') AND ([CREATION_DATE] <= '{env:end_extraction_date}'); REPLICATE [WSH_DELIVERY_ASSIGNMENTS] SELECT [DELIVERY_ASSIGNMENT_ID], [DELIVERY_DETAIL_ID], [DELIVERY_ID], [CREATED_BY] FROM [WSH].[WSH_DELIVERY_ASSIGNMENTS] WHERE ([CREATION_DATE] >= '{env:start_extraction_date}') AND ([CREATION_DATE] <= '{env:end_extraction_date}'); REPLICATE [WSH_DELIVERY_DETAILS] SELECT [DELIVERY_DETAIL_ID], [MOVE_ORDER_LINE_ID], [ORGANIZATION_ID], [SOURCE_LINE_ID], [SHIPPED_QUANTITY] FROM [WSH].[WSH_DELIVERY_DETAILS] WHERE ([CREATION_DATE] >= '{env:start_extraction_date}') AND ([CREATION_DATE] <= '{env:end_extraction_date}'); REPLICATE [WSH_EXCEPTIONS] SELECT [CREATED_BY], TO_CHAR([CREATION_DATE], 'yyyy-MM-dd hh:mm:ss') AS [CREATION_DATE], [DELIVERY_DETAIL_ID], [ERROR_MESSAGE], [EXCEPTION_NAME], [MESSAGE] FROM [WSH].[WSH_EXCEPTIONS] WHERE ([CREATION_DATE] >= '{env:start_extraction_date}') AND ([CREATION_DATE] <= '{env:end_extraction_date}');

Input data

Input types

The following table provides an overview of the different field types and their default format settings.

Field type Description
boolean true, false, 1, 0
date yyyy-mm-dd
datetime yyyy-mm-dd hh:mm:ss
double Decimal separator: . (dot); thousand separator: none
integer Thousand separator: none
text N/A

Input tables & fields

The following tables are extracted from the source system:

AR_ADJUSTMENTS_ALL

More information on this table can be found here.

Field Data type Label
ADJUSTMENT_ID Text Adjustment unique identifier
APPLY_DATE Datetime Application date of the adjustment
AUTOMATICALLY_GENERATED Boolean Boolean automatic identifier
CUSTOMER_TRX_ID Text Invoice ID
CREATED_BY Text User who created this row (foreign key to FND_USER.USER_ID)
STATUS Text Status of the row
AMOUNT Double Amount on the req line
ORG_ID Text Organization identifier

AR_CASH_RECEIPT_HISTORY_ALL

More information on this table can be found here.

Field Data type Label
CASH_RECEIPT_HISTORY_ID Text Primary Key for table
CASH_RECEIPT_ID Text Identifier of the cash receipt
CREATED_BY Text User who created this row (foreign key to FND_USER.USER_ID)
STATUS Text Status of the row
CREATION_DATE Datetime Date when this row was created
AMOUNT Double similar to the original amount of the cash receipt but the cleared amount might be different from the remitted due to any charges the bank took
ORG_ID Text The ID for the Operating Unit which entered the Receipt Line as created

AR_CASH_RECEIPTS_ALL

More information on this table can be found here.

Field Data type Label
CASH_RECEIPT_ID Text Primary Key for table. Identifier of the cash receipt
STATUS Text Identifies whether the status of this payment entry is applied, unapplied, unidentified, insufficient funds, reverse payment or stop payment
REVERSAL_DATE Datetime the date a payment was reversed
RECEIPT_DATE Datetime the date a payment was received
CREATION_DATE Datetime the date when this row was created
CREATED_BY Text User who created this row (foreign key to FND_USER.USER_ID)
AMOUNT Double similar to the original amount of the cash receipt but the cleared amount might be different from the remitted due to any charges the bank took
ORG_ID Text The ID for the Operating Unit which entered the Receipt Line as created

AR_RECEIVABLE_APPLICATIONS_ALL

More information on this table can be found here.

Field Data type Label
AMOUNT_APPLIED Double The total amount of the application in the currency of the receipt and transaction
RECEIVABLE_APPLICATION_ID Text Unique ID for the table
APPLICATION_TYPE Text Lookup code for the type of application
APPLIED_CUSTOMER_TRX_ID Text Identifies the debit item or credit memo to which a payment or credit memo is applied
APPLIED_CUSTOMER_TRX_LINE_ID Text Unique ID for the table
CASH_RECEIPT_ID Text Identifies the payment being applied
CASH_RECEIPT_HISTORY_ID Text Foreign key to the AR_CASH_RECEIPT_HISTORY table identifying the row that was current when the receivable application was created
CUSTOMER_TRX_ID Text Identifies the debit item or credit memo being applied
CREATED_BY Text User who created this row (foreign key to FND_USER.USER_ID)
CREATION_DATE Datetime Date when this row was created
STATUS Text Lookup type for the status of the application
ORG_ID Text The ID for the Operating Unit which entered the Receipt Line as created

FND_USER

More information on this table can be found here.

Field Data type Label
USER_ID Text Application user identifier
USER_NAME Text Application username
DESCRIPTION Text Description

HR_LOCATIONS_ALL

More information on this table can be found here.

Field Data type Label
LOCATION_ID Text System-generated primary key column
COUNTRY Text Country
REGION_1 Text County (US, UK); Province (CA)
REGION_2 Text State (US); Province of work (CA)
REGION_3 Text Additional address segment

HR_ALL_ORGANIZATION_UNITS

More information on this table can be found here.

Field Data type Label
ORGANIZATION_ID Text Organization identifier
LOCATION_ID Text Foreign key to HR_LOCATIONS. Default work site location for all assignments to this organization
NAME Text Name of the organization

HZ_CUST_ACCOUNTS

More information on this table can be found here.

Field Data type Label
CUST_ACCOUNT_ID Text Unique ID for the table
PARTY_ID Text Party Identifier and foreign key to the HZ_PARTY table.

HZ_PARTIES

More information on this table can be found here.

Field Data type Label
PARTY_ID Text Party identifier
PARTY_NAME Text Name of this party
COUNTRY Text Country of the Identifying address
COUNTY Text County of the Identifying address

MTL_MATERIAL_TRANSACTIONS

More information on this table can be found here.

Field Data type Label
CREATION_DATE Datetime Date when this row was created
CREATED_BY Text User who created this row (foreign key to FND_USER.USER_ID)
MOVE_ORDER_LINE_ID Text The move order line that this transaction line is tied to
ORGANIZATION_ID Text Organization identifier
SHIPMENT_NUMBER Text In transit shipment identifier
SOURCE_LINE_ID Text User entered source line identifier for outside transactions
TRANSACTION_QUANTITY Double Transaction quantity
TRANSACTION_SOURCE_TYPE_ID Text Transaction source type identifier
TRANSACTION_TYPE_ID Text Transaction type identifier

MTL_TXN_REQUEST_LINES

More information on this table can be found here.

Field Data type Label
INVENTORY_ITEM_ID Text Inventory item identifier
LINE_ID Text Identifier for the Order Line move and PK for the table
ORGANIZATION_ID Text Organization identifier
CREATION_DATE Datetime Date when this row was created
CREATED_BY Text User who created this row (foreign key to FND_USER.USER_ID)
QUANTITY Double The Quantity Requested

OE_HOLD_RELEASES

More information on this table can be found here.

Field Data type Label
HOLD_RELEASE_ID Text Primary Key for the table
ORDER_HOLD_ID Text ID of Order Releases from Hold (Foreign key to OE_ORDER_HOLDS_ALL)
CREATION_DATE Datetime Date of the hold being released
CREATED_BY Text User who created this row (foreign key to FND_USER.USER_ID)
RELEASE_REASON_CODE Text Foreign key to lookup_type in oe_lookups where lookup_type = RELEASE_REASON

OE_ORDER_HOLDS_ALL

More information on this table can be found here.

Field Data type Label
ORDER_HOLD_ID Text Primary Key for the table
HEADER_ID Text Sales Order Header Identifier
LINE_ID Text Line ID if the Hold of a line level holds
CREATION_DATE Datetime Date of the hold being created
CREATED_BY Text User who created this row (foreign key to FND_USER.USER_ID)

OE_PRICE_ADJUSTMENTS

More information on this table can be found here.

Field Data type Label
PRICE_ADJUSTMENT_ID Text Primary Key for the table
LINE_ID Text Order Line Identifier
REDEEMED_DATE Datetime Date discount was redeemed
REDEEMED_FLAG Boolean Flag discount was redeemed Y
AUTOMATIC_FLAG Boolean Notification if the column was created by a manual or automated user
CREATION_DATE Datetime Date of the hold being created
CREATED_BY Text User who created this row (foreign key to FND_USER.USER_ID)
ADJUSTED_AMOUNT DOUBLE Stores the Adjusted Amount at the adjustment level

OE_ORDER_HEADERS_ALL

More information on this table can be found here.

Field Data type Label
HEADER_ID Text Primary Key and Order Header Identifier
CREATION_DATE Datetime Date when this row was created
QUOTE_DATE Datetime Date of Quote
QUOTE_NUMBER Text Quote Number
ORG_ID Text Operating Unit which performed this transaction
CREATED_BY Text User who created this row (foreign key to FND_USER.USER_ID)
ORDER_SOURCE_ID Text Source of the order
ORDER_TYPE_ID Text Order Transaction Type Identifier
ORDER_NUMBER Text User-visible number of the order
SOLD_TO_ORG_ID Text Sold to customer
REQUEST_DATE Datetime Request date for the order
BOOKED_DATE Datetime Indicates the date when the order was booked
SHIP_FROM_ORG_ID Text Ship from Organization ID
CONVERSION_RATE Double Rate of the currency conversion

OE_ORDER_LINES_ALL

More information on this table can be found here.

Field Data type Label
LINE_ID Text Primary Key and Order Line Identifier
HEADER_ID Text Sales Order Header Identifier
CANCELLED_FLAG Boolean Indicates whether line is completely cancelled
CANCELLED_QUANTITY Integer Cancelled Quantity
CREATION_DATE Datetime Date of the Line being created
CREATED_BY Text User who created this row (foreign key to FND_USER.USER_ID)
INVENTORY_ITEM_ID Text Identifying ID of the Inventory Item
LINE_CATEGORY_CODE Text Indicates whether the line is a regular order or return
LINE_NUMBER Text Sales Order Line Number
LINE_TYPE_ID Text Identifier of the Transaction Line Type
ORDERED_ITEM_ID Text Provides the ID of the Order Item
ORDERED_QUANTITY Double The number of Ordered Items for the Line
ORG_ID Text The ID for the Operating Unit which entered the Order Line
PAYMENT_TERM_ID Text The ID associated with the Payment Terms
PROMISE_DATE Datetime The Date the customer was customer was promised to receive item
REFERENCE_CUSTOMER_TRX_LINE_ID Text The ID for the invoice line this return line references
SHIP_FROM_ORG_ID Text The Identifier for the organization (warehouse) the Line Items are shipped from
SHIP_TO_ORG_ID Text The Identifier for the organization receiving the Line Item
SHIPPING_QUANTITY Double The Quantity of line items shipped
UNIT_SELLING_PRICE Double The actual price the customer is charged for the Line Item

OE_ORDER_LINES_HISTORY

More information on this table can be found here.

Field Data type Label
ORG_ID Text Organization responsible for order line
HEADER_ID Text Sales Order Header Identifier
HIST_TYPE_CODE Text Indicates which action resulted in history(Cancel/Split/Update/Version/Quantity)
HIST_CREATED_BY Text The person creating the line
HIST_CREATION_DATE Datetime Date of the Line being created
LINE_ID Text Primary Key and the System Generated Line Identifier
ORDERED_ITEM_ID Text Provides the ID of the Order Item
ORDERED_QUANTITY Double The number of Ordered Items for the Line

RA_CUST_TRX_TYPES_ALL

More information on this table can be found here.

Field Data type Label
CUST_TRX_TYPE_ID Text Unique ID for the table
TYPE Text Transaction type identifying invoices, commitments, bills receivable, and credit memos
ORG_ID Text Organization responsible for order line

RA_CUSTOMER_TRX_ALL

More information on this table can be found here.

Field Data type Label
CUSTOMER_TRX_ID Text Unique ID for the table
CUST_TRX_TYPE_ID Text Transaction type identifier
TRX_NUMBER Text Transaction number
INTERFACE_HEADER_ATTRIBUTE1 Text Interface header attribute value
INVOICE_CURRENCY_CODE Text Invoice currency
EXCHANGE_RATE Double Exchange rate
INTERFACE_HEADER_CONTEXT Text Interface header context
ORG_ID Text Organization responsible for order line
CREATED_BY Text User who created this row (foreign key to FND_USER.USER_ID)
CREATION_DATE Datetime Date of the row being created
COMPLETE_FLAG Boolean Indicates if the invoice is complete. Y for yes, N otherwise

RA_CUSTOMER_TRX_LINES_ALL

More information on this table can be found here.

Field Data type Label
CUSTOMER_TRX_LINE_ID Text Invoice line identifier
CUSTOMER_TRX_ID Text Invoice identifier
INTERFACE_LINE_ATTRIBUTE1 Text Interface line attribute value
INTERFACE_LINE_ATTRIBUTE6 Text Interface line attribute value
DESCRIPTION Text Line description
QUANTITY_INVOICED Double Quantity of invoice line
UNIT_SELLING_PRICE Double Selling price per unit for a transaction line
SET_OF_BOOKS_ID Text Set of Books identifier
ORG_ID Text Organization responsible for order line
CREATED_BY Text User who created this row (foreign key to FND_USER.USER_ID)
CREATION_DATE Datetime Date of the row being created

WSH_DELIVERY_ASSIGNMENTS

More information on this table can be found here.

Field Data type Label
DELIVERY_ASSIGNMENT_ID Text Primary Key for assignment
DELIVERY_DETAIL_ID Text Foreign key to WSH_DELIVERY_DETAILS
DELIVERY_ID Text The delivery ID associated with the delivery exception
CREATED_BY Text User who created this row (foreign key to FND_USER.USER_ID)

WSH_DELIVERY_DETAILS

More information on this table can be found here.

Field Data type Label
DELIVERY_DETAIL_ID Text The delivery detail id associated with the delivery exception
SOURCE_LINE_ID Text OE sales order line ID
SHIPPED_QUANTITY Text As shipping quantity for customer- Quantity Shipped
MOVE_ORDER_LINE_ID Text Move order line identifier
ORGANIZATION_ID Text The Organization ID

WSH_EXCEPTIONS

More information on this table can be found here.

Field Data type Label
CREATION_DATE Datetime Date of the Line being created
CREATED_BY Text User who created this row (foreign key to FND_USER.USER_ID)
DELIVERY_DETAIL_ID Text The delivery detail ID associated with the delivery exception
EXCEPTION_NAME Text The exception definition
ERROR_MESSAGE Text Error message field will be populated if an error is encountered while processing the Exception
MESSAGE Text Message associated with the exception

Configuring transformations

Seed files

Automation_estimates_raw

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

Field Type Description
Activity Text Display name for the activity
Event_cost Double Cost associated with the activity
Event_processing_time Integer Processing time associated with the activity (in milliseconds)

Due_dates_configuration_raw

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

Field Type Description
Due_date Text The name of the due date
Due_date_type Text The Due date type
Fixed_costs Boolean An indication whether costs are fixed or time based
Cost Double Fixed costs: The amount of costs. Variable costs: The amount of costs per Time and Time_type
Time Integer A number indicating the amount of time in case of time-based costs
Time_type Text Type of time period for cost calculations. This can be any of the following values: day, hour, minute, second or millisecond

Dbt variables

Variable Type Description
date_format Text Format for parsing date fields.
datetime_format Text Format for parsing datetime fields.
Automated_users List of Integer List of automated users. The values referenced are USER_ID values from the FND_USER table.

Design specifications

Entities

Entity Input Data
Accounting documents AR_RECEIVABLE_APPLICATIONS_ALL
Deliveries WSH_DELIVERY_ASSIGNMENTS
Delivery items WSH_DELIVERY_DETAILS, WSH_DELIVERY_ASSIGNMENTS, RA_CUSTOMER_TRX_LINES_ALL
Invoice cancellations AR_RECEIVABLE_APPLICATIONS_ALL
Invoice items RA_CUSTOMER_TRX_LINES_ALL, RA_CUSTOMER_TRX_ALL, FND_USER, WSH_DELIVERY_DETAILS
Invoices RA_CUSTOMER_TRX_ALL
Payments AR_RECEIVABLE_APPLICATIONS_ALL, AR_CASH_RECEIPT_HISTORY_ALL, FND_USER
Sales order items OE_ORDER_LINES_ALL, OE_ORDER_HEADERS_ALL, RA_CUSTOMER_TRX_LINES_ALL, HR_ALL_ORGANIZATION_UNITS, HR_LOCATIONS_ALL
Sales orders OE_ORDER_HEADERS_ALL, HZ_CUST_ACCOUNTS, HZ_PARTIES, HR_ALL_ORGANIZATION_UNITS, HR_LOCATIONS_ALL

Activities

Activity Entity Description
Book sales order Sales order This identifies when a Sales Order is Booked
Create quote Sales order This identifies when a quote is created prior to becoming a sales order
Hold sales order Sales order This identifies when a Sales Order is put on Hold
Release sales order Sales order This identifies when a held Sales Order is Released
Create sales order item Sales order item Identifies the creation of a Sales Order Item
Create sales order item return Sales order item Identifies the return of a Sales Order Item
Adjust price Sales order item Identifies price adjustment of a Sales Order Item
Redeem discount Sales order item Identifies a discount redemption for Sales Order Item
Ship item Sales order item Identifies the shipment of a Sales Order Item
Cancel sales order item Sales order item Identifies sales order line cancellation status
Split sales order item Sales order item Identifies sales order line split
Update sales order item Sales order item Identifies sales order line update
Update sales order item quantity Sales order item Identifies sales order line quantity update
Change sales order item versioning Sales order item Identifies sales order line versioning
Apply credit memo Invoice This identifies when a Credit Memo is used
Apply receipt Invoice This identifies a receipt is applied to an Invoice
Approve invoice adjustment Invoice This identifies an Invoice Adjustment Approval
Reject invoice adjustment Invoice This identifies an Invoice Adjustment Rejection
Request invoice adjustment Invoice This identifies an Invoice Adjustment Request
Other invoice adjustment Invoice This identifies an Invoice Adjustment
Create chargeback Invoice This identifies the creation of a Chargeback
Create credit memo Invoice This identifies the creation of a Credit Memo
Create debit memo Invoice This identifies the creation of a Debit Memo
Create deposit Invoice This identifies the creation of a Deposit
Create guarantee Invoice This identifies the creation of a Guarantee
Create invoice Invoice This identifies the creation of an Invoice
Create receipt Invoice This identifies when a receipt is generated for an Invoice item
Reverse receipt Invoice Identifies reversal of Invoice Receipt
Create invoice item Invoice item This identifies when an Invoice Item is created
Change schedule date Delivery item This identifies a change of Scheduled Delivery Date
Confirm item pick Delivery item Identifies picking of item for Delivery
Release item pick Delivery item Identifies release of item for Delivery
Set shipping exception Delivery item This identifies a Shipping Exception
Create payment receipt Payment Identifies the creation of a Payment Receipt
Clear payment Payment Identifies the clearance of a Payment
Remit payment Payment Identifies the remittance of a Payment
Reverse payment Payment Identifies the reversal of a Payment
Confirm payment Payment Identifies the confirmation of a Payment

Design details

Known limitations