Oracle-EBS-Purchase-to-Pay.md
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
- In CData - Connections, add a new source connection for Oracle. If this option is not in the list, click + Add More, search for Oracle and click Download & install
- Name the connection Oracle_Connection.
- Fill out the fields for Server, Service Name, User, Password with the correct values.
- Go to the Advanced tab and set Port to the correct value.
- Create and test the connection.
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.
To create the Job in CData make sure to follow the steps below.
- In CData - Jobs, create a new job.
- Select Oracle_Connection as source and select the destination connections created for the Oracle E-Business Purchase-to-Pay app template and create the job.
Advanced job settings
In the Advanced tab in the Job Settings panel, define the following settings:
- Select the Alter Schema option.
- Select the Drop Table option.
- If you are using Automation Suite, add the Destination Schema. Fill out the value you retrieved when you created the destination connection.
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 [PO_REQUISITION_HEADERS_ALL] SELECT [REQUISITION_HEADER_ID], [AUTHORIZATION_STATUS], [ORG_ID], [SEGMENT1], [TYPE_LOOKUP_CODE] FROM [PO].[PO_REQUISITION_HEADERS_ALL]; REPLICATE [PO_REQUISITION_LINES_ALL] SELECT [REQUISITION_HEADER_ID], [REQUISITION_LINE_ID], [LINE_LOCATION_ID], TO_CHAR([CREATION_DATE], 'yyyy-MM-dd hh:mm:ss') AS [CREATION_DATE], [CREATED_BY], [ITEM_DESCRIPTION], [QUANTITY], [UNIT_MEAS_LOOKUP_CODE], [UNIT_PRICE], [LINE_NUM], [RATE], [CATEGORY_ID] FROM [PO].[PO_REQUISITION_LINES_ALL] WHERE ([CREATION_DATE] >= '{env:start_extraction_date}') AND ([CREATION_DATE] <= '{env:end_extraction_date}'); REPLICATE [PO_HEADERS_ALL] SELECT [PO_HEADER_ID], [AUTHORIZATION_STATUS], [TYPE_LOOKUP_CODE], [COMMENTS], TO_CHAR([CREATION_DATE], 'yyyy-MM-dd hh:mm:ss') AS [CREATION_DATE], [CREATED_BY], [ORG_ID], [BILL_TO_LOCATION_ID], [SHIP_TO_LOCATION_ID], [VENDOR_ID], [SEGMENT1], [RATE] FROM [PO].[PO_HEADERS_ALL] WHERE ([CREATION_DATE] >= '{env:start_extraction_date}') AND ([CREATION_DATE] <= '{env:end_extraction_date}'); REPLICATE [PO_LINES_ALL] SELECT [PO_LINE_ID], [PO_HEADER_ID], [CATEGORY_ID], TO_CHAR([CREATION_DATE], 'yyyy-MM-dd hh:mm:ss') AS [CREATION_DATE], [ITEM_DESCRIPTION], [UNIT_MEAS_LOOKUP_CODE], [QUANTITY], [LINE_NUM], [UNIT_PRICE] FROM [PO].[PO_LINES_ALL] WHERE ([CREATION_DATE] >= '{env:start_extraction_date}') AND ([CREATION_DATE] <= '{env:end_extraction_date}'); REPLICATE [PO_LINE_LOCATIONS_ALL] SELECT [LINE_LOCATION_ID], [PO_LINE_ID], TO_CHAR([PROMISED_DATE], 'yyyy-MM-dd hh:mm:ss') AS [PROMISED_DATE], TO_CHAR([NEED_BY_DATE], 'yyyy-MM-dd hh:mm:ss') AS [NEED_BY_DATE] FROM [PO].[PO_LINE_LOCATIONS_ALL]; REPLICATE [RCV_SHIPMENT_LINES] SELECT [SHIPMENT_LINE_ID], TO_CHAR([CREATION_DATE], 'yyyy-MM-dd hh:mm:ss') AS [CREATION_DATE], [CREATED_BY], [PO_LINE_ID], [COMMENTS] FROM [PO].[RCV_SHIPMENT_LINES] WHERE ([CREATION_DATE] >= '{env:start_extraction_date}') AND ([CREATION_DATE] <= '{env:end_extraction_date}'); REPLICATE [RCV_TRANSACTIONS] SELECT [COMMENTS#1] AS [COMMENTS], [CREATED_BY], [CUSTOMER_ID], [TRANSACTION_TYPE], TO_CHAR([TRANSACTION_DATE], 'yyyy-MM-dd hh:mm:ss') AS [TRANSACTION_DATE], [SHIPMENT_LINE_ID], [PO_LINE_ID] FROM [PO].[RCV_TRANSACTIONS]; REPLICATE [AP_CHECKS_ALL] SELECT [CHECK_ID], TO_CHAR([CREATION_DATE], 'yyyy-MM-dd hh:mm:ss') AS [CREATION_DATE], [CREATED_BY] FROM [AP].[AP_CHECKS_ALL] WHERE ([CREATION_DATE] >= '{env:start_extraction_date}') AND ([CREATION_DATE] <= '{env:end_extraction_date}'); REPLICATE [AP_INVOICES_ALL] SELECT [INVOICE_ID], [ORG_ID], TO_CHAR([CREATION_DATE], 'yyyy-MM-dd hh:mm:ss') AS [CREATION_DATE], [CREATED_BY], [DESCRIPTION], [EXCHANGE_RATE], TO_CHAR([INVOICE_DATE], 'yyyy-MM-dd hh:mm:ss') AS [INVOICE_DATE], TO_CHAR([TERMS_DATE], 'yyyy-MM-dd hh:mm:ss') AS [TERMS_DATE], TO_CHAR([GL_DATE], 'yyyy-MM-dd hh:mm:ss') AS [GL_DATE], [TERMS_ID], [PAYMENT_METHOD_CODE], [INVOICE_TYPE_LOOKUP_CODE], [INVOICE_NUM] FROM [AP].[AP_INVOICES_ALL] WHERE ([CREATION_DATE] >= '{env:start_extraction_date}') AND ([CREATION_DATE] <= '{env:end_extraction_date}'); REPLICATE [AP_INVOICE_LINES_ALL] SELECT [INVOICE_ID], [LINE_NUMBER], [AMOUNT], [ITEM_DESCRIPTION], [PERIOD_NAME], [PO_LINE_ID], [QUANTITY_INVOICED], [UNIT_MEAS_LOOKUP_CODE], [MANUFACTURER] FROM [AP].[AP_INVOICE_LINES_ALL] WHERE ([CREATION_DATE] >= '{env:start_extraction_date}') AND ([CREATION_DATE] <= '{env:end_extraction_date}'); REPLICATE [AP_INVOICE_PAYMENTS_ALL] SELECT [INVOICE_ID], [INVOICE_PAYMENT_ID], [CHECK_ID], [POSTED_FLAG], TO_CHAR([CREATION_DATE], 'yyyy-MM-dd hh:mm:ss') AS [CREATION_DATE], [CREATED_BY] FROM [AP].[AP_INVOICE_PAYMENTS_ALL] WHERE ([CREATION_DATE] >= '{env:start_extraction_date}') AND ([CREATION_DATE] <= '{env:end_extraction_date}'); REPLICATE [AP_SUPPLIERS] SELECT [VENDOR_ID], [VENDOR_NAME], [ONE_TIME_FLAG], [PARTY_ID] FROM [AP].[AP_SUPPLIERS]; REPLICATE [AP_TERMS_LINES] SELECT [TERM_ID], [DISCOUNT_PERCENT], [DISCOUNT_DAYS], [DISCOUNT_PERCENT_2], [DISCOUNT_DAYS_2], [DISCOUNT_MONTHS_FORWARD], [DISCOUNT_MONTHS_FORWARD_2], [DUE_DAYS], [ATTRIBUTE_CATEGORY], [SEQUENCE_NUM] FROM [AP].[AP_TERMS_LINES] WHERE [SEQUENCE_NUM] = 1; REPLICATE [HZ_CUST_ACCOUNTS] SELECT [CUST_ACCOUNT_ID], [ACCOUNT_NAME], [PARTY_ID] FROM [AR].[HZ_CUST_ACCOUNTS]; REPLICATE [HR_LOCATIONS_ALL] SELECT [LOCATION_ID], [DESCRIPTION] FROM [HR].[HR_LOCATIONS_ALL]; REPLICATE [HR_ALL_ORGANIZATION_UNITS] SELECT [ORGANIZATION_ID], [NAME] FROM [HR].[HR_ALL_ORGANIZATION_UNITS]; REPLICATE [HZ_PARTIES] SELECT [PARTY_ID], [COUNTRY], [COUNTY] FROM [AR].[HZ_PARTIES]; REPLICATE [PO_ACTION_HISTORY] SELECT [OBJECT_ID], [OBJECT_TYPE_CODE], [ACTION_CODE], TO_CHAR([ACTION_DATE], 'yyyy-MM-dd hh:mm:ss') AS [ACTION_DATE], [SEQUENCE_NUM], [NOTE], [EMPLOYEE_ID] FROM [PO].[PO_ACTION_HISTORY] WHERE [ACTION_CODE] IN ('ACCEPT', 'APPROVE', 'HOLD', 'CLOSE', 'OPEN', 'REJECT', 'CANCEL') AND ([ACTION_DATE] >= '{env:start_extraction_date}') AND ([ACTION_DATE] <= '{env:end_extraction_date}'); REPLICATE [PO_DOCUMENT_TYPES_ALL_TL] SELECT [DOCUMENT_TYPE_CODE], [DOCUMENT_SUBTYPE], [ORG_ID], [TYPE_NAME], [LANGUAGE] FROM [PO].[PO_DOCUMENT_TYPES_ALL_TL] WHERE [LANGUAGE] = 'US'; REPLICATE [PO_LINES_ARCHIVE_ALL] SELECT [PO_LINE_ID], TO_CHAR([LAST_UPDATE_DATE], 'yyyy-MM-dd hh:mm:ss') AS [LAST_UPDATE_DATE], [LAST_UPDATED_BY], [PO_HEADER_ID], [UNIT_PRICE], [QUANTITY], [REVISION_NUM] FROM [PO].[PO_LINES_ARCHIVE_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 [MTL_CATEGORIES_B] SELECT [CATEGORY_ID], [ATTRIBUTE_CATEGORY] FROM [INV].[MTL_CATEGORIES_B];
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:
List of all files in the sample data directory, without the csv extension:
- AP_CHECKS_ALL
- AP_INVOICE_LINES_ALL
- AP_INVOICE_PAYMENTS_ALL
- AP_INVOICES_ALL
- AP_SUPPLIERS
- AP_TERMS_LINES
- FND_USER
- HR_ALL_ORGANIZATION_UNITS
- HR_LOCATIONS_ALL
- HZ_CUST_ACCOUNTS
- HZ_PARTIES
- MTL_CATEGORIES_B
- PO_ACTION_HISTORY
- PO_DOCUMENT_TYPES_ALL_TL
- PO_HEADERS_ALL
- PO_LINE_LOCATIONS_ALL
- PO_LINES_ALL
- PO_LINES_ARCHIVE_ALL
- PO_REQUISITION_HEADERS_ALL
- PO_REQUISITION_LINES_ALL
- RCV_SHIPMENT_LINES
- RCV_TRANSACTIONS
AP_CHECKS_ALL
More information on this table can be found here.
| Field | Type | Description |
|---|---|---|
| CHECK_ID | Text | Payment 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) |
Filtering: CREATION_DATE is used for time-based filtering.
AP_INVOICE_LINES_ALL
More information on this table can be found here.
| Field | Type | Description |
|---|---|---|
| INVOICE_ID | Text | Invoice identifier |
| LINE_NUMBER | Text | Invoice line number |
| AMOUNT | Double | Line amount in invoice currency |
| ITEM_DESCRIPTION | Text | Item description |
| PERIOD_NAME | Text | Accounting date for invoice line |
| PO_LINE_ID | Text | Purchase order line unique identifier |
| QUANTITY_INVOICED | Text | Quantity invoiced |
| UNIT_MEAS_LOOKUP_CODE | Text | Unit of measure for QUANTITY_INVOICED |
| MANUFACTURER | Text | Name of manufacturer |
Filtering: CREATION_DATE is used for time-based filtering.
AP_INVOICE_PAYMENTS_ALL
More information on this table can be found here.
| Field | Type | Description |
|---|---|---|
| INVOICE_ID | Text | Invoice identifier |
| INVOICE_PAYMENT_ID | Text | Invoice payment unique identifier |
| CHECK_ID | Text | The payment identifier is a unique sequential number to identify a payment. Foreign key to the AP_CHECKS_ALL |
| POSTED_FLAG | Text | Flag that indicates if the payment has been accounted (Y or N) |
| CREATION_DATE | Datetime | Date when this row was created |
| CREATED_BY | Text | User who created this row (foreign key to FND_USER.USER_ID) |
Filtering: CREATION_DATE is used for time-based filtering.
AP_INVOICES_ALL
More information on this table can be found here.
| Field | Type | Description |
|---|---|---|
| INVOICE_ID | Text | Invoice identifier |
| ORG_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) |
| DESCRIPTION | Text | Description |
| EXCHANGE_RATE | Double | Exchange rate for foreign currency invoice |
| INVOICE_DATE | Datetime | Invoice date |
| TERMS_DATE | Datetime | Date used with payment terms to calculate scheduled payment of an invoice |
| GL_DATE | Datetime | Accounting date to default to invoice distributions |
| TERMS_ID | Integer | Payment terms identifier |
| PAYMENT_METHOD_CODE | Text | Payment method identifier |
| INVOICE_TYPE_LOOKUP_CODE | Text | Type of invoice |
| INVOICE_NUM | Text | Invoice number |
Filtering: CREATION_DATE is used for time-based filtering.
AP_SUPPLIERS
More information on this table can be found here.
| Field | Type | Description |
|---|---|---|
| VENDOR_ID | Text | Supplier unique identifier |
| VENDOR_NAME | Text | Supplier name |
| ONE_TIME_FLAG | Text | Indicates whether the supplier is a one-time supplier (Y or N) |
| PARTY_ID | Text | Party Identifier |
AP_TERMS_LINES
More information on this table can be found here.
| Field | Type | Description |
|---|---|---|
| TERM_ID | Text | Term identifier |
| DISCOUNT_PERCENT | Double | Percentage used to calculate discount available for invoice payment line |
| DISCOUNT_DAYS | Integer | Number of days after terms date, used to calculate discount date for invoice payment line |
| DISCOUNT_PERCENT_2 | Double | Percentage used to calculate second discount available for invoice payment line |
| DISCOUNT_DAYS_2 | Integer | Number of days after terms date, used to calculate second discount available for invoice payment line |
| DUE_DAYS | Integer | Number of days after terms date, used to calculate due date of invoice payment line |
| ATTRIBUTE_CATEGORY | Text | Attribute description |
| SEQUENCE_NUM | Integer | Sequence number |
FND_USER
More information on this table can be found here.
| Field | Type | Description |
|---|---|---|
| USER_ID | Text | Application user identifier |
| USER_NAME | Text | Application username |
| DESCRIPTION | Text | User Description |
HR_ALL_ORGANIZATION_UNITS
More information on this table can be found here.
| Field | Type | Description |
|---|---|---|
| ORGANIZATION_ID | Text | Organization identifier |
| NAME | Text | Name of the organization |
HR_LOCATIONS_ALL
More information on this table can be found here.
| Field | Type | Description |
|---|---|---|
| LOCATION_ID | Text | System-generated primary key column |
| DESCRIPTION | Text | Brief description of the location |
HZ_CUST_ACCOUNTS
More information on this table can be found here.
| Field | Type | Description |
|---|---|---|
| CUST_ACCOUNT_ID | Text | Customer account identifier |
| ACCOUNT_NAME | Text | Account name |
| PARTY_ID | Text | Party identifier |
HZ_PARTIES
More information on this table can be found here.
| Field | Type | Description |
|---|---|---|
| PARTY_ID | Text | Party identifier |
| COUNTRY | Text | Country of the Identifying address |
| COUNTY | Text | County of the Identifying address |
MTL_CATEGORIES_B
More information on this table can be found here.
| Field | Type | Description |
|---|---|---|
| CATEGORY_ID | Text | Category identifier |
| ATTRIBUTE_CATEGORY | Text | Attribute description |
PO_ACTION_HISTORY
More information on this table can be found here.
| Field | Type | Description |
|---|---|---|
| OBJECT_ID | Text | Document header unique identifier |
| OBJECT_TYPE_CODE | Text | Document type |
| ACTION_CODE | Text | Approval or control action type |
| ACTION_DATE | Datetime | Approval or control action date |
| SEQUENCE_NUM | Integer | Sequence of the approval or control action for a document |
| NOTE | Text | Note for next approver or reason for control action |
| EMPLOYEE_ID | Text | Unique identifier of the employee taking the action |
Filtering: ACTION_DATE is used for time-based filtering. Furthermore, only records for which the ACTION_CODE is any of the following values are selected: ACCEPT, APPROVE, HOLD, CLOSE, OPEN, REJECT, CANCEL.
PO_DOCUMENT_TYPES_ALL_TL
More information on this table can be found here.
| Field | Type | Description |
|---|---|---|
| DOCUMENT_TYPE_CODE | Text | Document type |
| DOCUMENT_SUBTYPE | Text | Document subtype |
| ORG_ID | Integer | Organization identifier |
| TYPE_NAME | Text | Document type name |
| LANGUAGE | Text | Language |
More information on this table can be found here.
| Field | Type | Description |
|---|---|---|
| PO_HEADER_ID | Text | Purchase order header unique identifier |
| AUTHORIZATION_STATUS | Text | Authorization status of the purchase order |
| TYPE_LOOKUP_CODE | Text | Type of the document |
| COMMENTS | Text | Descriptive comments for the document |
| CREATION_DATE | Datetime | Date when this row was created |
| CREATED_BY | Text | User who created this row (foreign key to FND_USER.USER_ID) |
| ORG_ID | Text | Organization identifier |
| BILL_TO_LOCATION_ID | Text | Bill-to location unique identifier |
| SHIP_TO_LOCATION_ID | Text | Ship-to location unique identifier |
| VENDOR_ID | Text | Supplier unique identifier |
| SEGMENT1 | Text | Key flexfield segment (contains front-end documentation identifier) |
| RATE | Double | Currency conversion rate |
Filtering: CREATION_DATE is used for time-based filtering.
PO_LINE_LOCATIONS_ALL
More information on this table can be found here.
| Field | Type | Description |
|---|---|---|
| LINE_LOCATION_ID | Text | Document shipment schedule unique identifier |
| PO_LINE_ID | Text | Purchase order line unique identifier |
| PROMISED_DATE | Datetime | Supplier promised delivery date |
| NEED_BY_DATE | Datetime | Need-by date for the shipment schedule |
PO_LINES_ALL
More information on this table can be found here.
| Field | Type | Description |
|---|---|---|
| PO_LINE_ID | Text | Purchase order line unique identifier |
| PO_HEADER_ID | Text | Purchase order header unique identifier |
| CATEGORY_ID | Text | Item category unique identifier |
| CREATION_DATE | Datetime | Date when this row was created |
| ITEM_DESCRIPTION | Text | Item description |
| UNIT_MEAS_LOOKUP_CODE | Text | Unit of measure |
| QUANTITY | Double | Quantity ordered on the line |
| LINE_NUM | Double | Line number |
| UNIT_PRICE | Double | Unit price in functional currency |
Filtering: CREATION_DATE is used for time-based filtering.
PO_LINES_ARCHIVE_ALL
More information on this table can be found here.
| Field | Type | Description |
|---|---|---|
| PO_LINE_ID | Text | Purchase order line unique identifier |
| CREATION_DATE | Datetime | Date when this row was created |
| LAST_UPDATE_DATE | Date | date when a user last updated this row |
| LAST_UPDATED_BY | Text | User who last updated this row (foreign key to FND_USER.USER_ID) |
| PO_HEADER_ID | Text | Purchase order header unique identifier |
| UNIT_PRICE | Double | Unit price for the line |
| QUANTITY | Double | Quantity ordered on the line |
| REVISION_NUM | Integer | Document revision number |
Filtering: CREATION_DATE is used for time-based filtering.
More information on this table can be found here.
| Field | Type | Description |
|---|---|---|
| REQUISITION_HEADER_ID | Text | Requisition header unique identifier |
| AUTHORIZATION_STATUS | Text | Authorization status type |
| ORG_ID | Text | Organization identifier |
| SEGMENT1 | Text | Key flexfield segment (contains front-end documentation identifier) |
| TYPE_LOOKUP_CODE | Text | Requisition type |
PO_REQUISITION_LINES_ALL
More information on this table can be found here.
| Field | Type | Description |
|---|---|---|
| REQUISITION_HEADER_ID | Text | Requisition header unique identifier |
| REQUISITION_LINE_ID | Text | Requisition line unique identifier |
| LINE_LOCATION_ID | Text | Document shipment schedule unique identifier |
| CREATION_DATE | Datetime | Row creation date |
| CREATED_BY | Text | User who created this row (foreign key to FND_USER.USER_ID) |
| ITEM_DESCRIPTION | Text | Item description |
| QUANTITY | Double | Quantity ordered |
| UNIT_MEAS_LOOKUP_CODE | Text | Unit of measure |
| UNIT_PRICE | Double | Unit price in functional currency |
| LINE_NUM | Double | Line number |
| RATE | Double | Currency conversion rate |
| CATEGORY_ID | Text | Item category unique identifier |
Filtering: CREATION_DATE is used for time-based filtering.
RCV_SHIPMENT_LINES
More information on this table can be found here.
| Field | Type | Description |
|---|---|---|
| SHIPMENT_LINE_ID | Text | Receipt shipment line unique 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) |
| PO_LINE_ID | Text | Purchase order line unique identifier |
| COMMENTS | Text | Receiver comments |
Filtering: CREATION_DATE is used for time-based filtering.
RCV_TRANSACTIONS
More information on this table can be found here.
| Field | Type | Description |
|---|---|---|
| COMMENTS | Text | Receiver comments |
| CREATED_BY | Text | User who created this row (foreign key to FND_USER.USER_ID) |
| CUSTOMER_ID | Text | Customer unique identifier |
| TRANSACTION_TYPE | Text | Receiving transaction type |
| TRANSACTION_DATE | Datetime | Transaction date |
| SHIPMENT_LINE_ID | Text | Receipt shipment line unique identifier |
| PO_LINE_ID | Text | Purchase order line unique identifier |
Filtering: CREATION_DATE is used for time-based filtering.
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. |
| Language | Text | Language used for PO Document types (PO_DOCUMENT_TYPES_ALL_TL). Default value is US |
Design specifications
Entities
| Entity | Input Data |
|---|---|
| Purchase_requisitions_base | PO_REQUISITION_LINES_ALL,PO_REQUISITION_HEADERS_ALL,PO_REQUISITION_HEADERS_ALL,PO_DOCUMENT_TYPES_ALL_TL,MTL_CATEGORIES_B,FND_USERS |
| Purchase_orders_base | PO_HEADERS_ALL,PO_DOCUMENT_TYPES_ALL_TL,HR_ALL_ORGANIZATION_UNITS,FND_USERS,AP_SUPPLIERS,HZ_PARTIES,HR_ALL_ORGANIZATION_UNITS |
| Purchase_order_items_base | PO_LINES_ALL,PO_LINE_LOCATIONS_ALL,PO_REQUISITION_LINES_ALL,PO_HEADERS_ALL,HR_LOCATIONS_ALL,HZ_CUST_ACCOUNTS,MTL_CATEGORIES_B,RCV_TRANSACTIONS,HZ_PARTIES |
| Goods_receipt_base | RCV_SHIPMENT_LINES |
| Invoices_base | AP_INVOICES_ALL,AP_TERMS_ALL,FND_USERS,HR_ALL_ORGANIZATION_UNITS |
| Invoice_items_base | AP_INVOICES_LINES_ALL,AP_INVOICES_ALL |
| Accounting_documents_base | AP_INVOICE_PAYMENTS_ALL |
| Payments_base | AP_INVOICE_PAYMENTS_ALL |
Activities
| Activity | Entity | Description |
|---|---|---|
| Create purchase requisition item | Purchase Requisition | This identifies the creation of a purchase requisition item. |
| <ACTION> purchase requisition | Purchase Requisition | This identifies a change to the status of a purchase requisition. The name is based on the value of ACTION_CODE (e.g. Approve, Reject) |
| Create purchase order | Purchase Order | This identifies the creation of a purchase order. |
| <ACTION> purchase order | Purchase Order | This identifies a series of changes to the status of a purchase order. The name is based on the value of ACTION_CODE (e.g. Approve, Reject) |
| Create purchase order item | Purchase order item | This identifies the creation action of a purchase order item. |
| Change purchase order item unit price | Purchase order item | This identifies the change action to a purchase order item's unit price. |
| Change purchaase order item unit quantity | Purchase order item | This identifies the change action to a purchase order item's quantity. |
| Create goods receipt | Goods receipt | This identifies the creation action of a shipment item. |
| Receive goods receipt | Goods receipt | This identifies the receipt of a shipment. |
| Create return to vendor | Goods receipt | This identifies the creation of a return to vendor. |
| Create goods receipt Correction | Goods receipt | This identifies the creation of a goods receipt correction. |
| Create goods receipt Transfer | Goods receipt | This identifies the creation of a goods receipt transfer. |
| Accept goods receipt | Goods receipt | This identifies the acception of a goods receipt. |
| Reject goods receipt | Goods receipt | This identifies the rejection of a goods receipt. |
| Create invoice | Invoice | This identifies the creation action of an invoice. |
| Create invoice item | Invoice item | This identifies the creation action of an invoice item. |
| Create payment | Payment | This identifies the creation action of a payment. |
| Pay invoice | Payment | This identifies the payment of an invoice. |
Design details
Known limitations
- The usage of Segment1 relies on the field being used for customized item ID's, which are user-defined. If Segment1 is used in another manner, the joins may result in errors.
Known issues
- The connector follows a 1:1 relationship between Purchase Requisition Item and Purchase Order Item, despite this being, on occasion, a n:1 relationship. The Purchase Order Item relates to the Purchase Requisition Item via Line Location ID, and matching quantity, in order to circumvent this.
- The
TO_CHARquery statements for CData extraction may occasionally cut the timestamp off (i.e. provide2020-01-) rather than return the full timestamp. When this occurs, re-run the query and the issue should be rectified.
Troubleshooting
- The
COMMENTScolumn inRCV_TRANSACTIONSmay be titledCOMMENTS#1in your version of Oracle EBS. Keep this in mind if there is a failure on the extraction of this table.
ncG1vNJzZmiZoKXBprnPpZitnaOZvKS%2FjZujqJpemLyzsY2woKecn6zAb7rErWadp5Oquqa605qroqeeZMKqvMCtn4lqgIS%2Foq%2FLnnx7i1%2BEv6Kvy55kfnqDYp22vsKhmKydXam8bpzAsmWhrJ2h