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.
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.
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:
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.
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 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}');
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 |
The following tables are extracted from the source system:
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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. |
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 |
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 |
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 |
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 |
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) |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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) |
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 |
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 |
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) |
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 |
| 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. |
| 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 |
| 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 |
Invoice_items_base table expects only a single Delivery_item_ID per record (a many-to-one relation). To match this constraint, only the latest Delivery_item_ID is matched to each Invoice_item_ID out-of-the-box.RA_CUST_TRX_TYPES_ALL has a column titled ZD_EDITION_NAME that is listed in the documentation as part of the unique ID, but is often not required. If duplication issues arise in this table, pull in the column to create a unique ID.