Oracle Cloud Purchase-to-Pay app template

Extraction

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

System configuration

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

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 Purchase-to-Pay process app.

Upload object to Oracle Cloud

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

Report execution

Below is a description of the steps to execute Oracle Cloud reports. There are two kinds of reports. Reports that refer to transactional data (purchase requisitions, purchase orders, goods receipts, 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.

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

Setting up the source connection in CData Sync

Setting up the destination connection in CData Sync

To set up the destination connection, follow the steps as defined in Create a destination connection (Automation Cloud). Note: If you are using Automation Suite, make sure to select AUTOMATION SUITE as the DELIVERY option and select the correct version of Automation Suite you are using.

Creating the extraction job in CData Sync

To create the Job in CData make sure to follow the steps below.

Advanced job settings

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

If you are using Automation Suite, set the Destination Schema in the Settings panel on the overview tab. Use the schema name you retrieved when you created the destination connection.

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, go to Task tab, click + Add Tasks, enable the Custom Query option and paste the following queries (each query needs to maintain the semicolon at the end). Make sure you save all changes.

REPLICATE [PO_LINES_ALL_R_PO_LINES_ALL_R] select * from PO_LINES_ALL_R_PO_LINES_ALL_R;
REPLICATE [POR_REQUISITION_HEADERS_ALL_R_POR_REQUISITION_HEADERS_ALL_R] select * from POR_REQUISITION_HEADERS_ALL_R_POR_REQUISITION_HEADERS_ALL_R;
REPLICATE [POR_REQUISITION_LINES_ALL_R_POR_REQUISITION_LINES_ALL_R] select * from POR_REQUISITION_LINES_ALL_R_POR_REQUISITION_LINES_ALL_R;
REPLICATE [POZ_SUPPLIER_SITES_V_R_POZ_SUPPLIER_SITES_V_R] select * from POZ_SUPPLIER_SITES_V_R_POZ_SUPPLIER_SITES_V_R;
REPLICATE [POZ_SUPPLIERS_R_POZ_SUPPLIERS_R] select * from POZ_SUPPLIERS_R_POZ_SUPPLIERS_R;
REPLICATE [RCV_SHIPMENT_LINES_R_RCV_SHIPMENT_LINES_R] select * from RCV_SHIPMENT_LINES_R_RCV_SHIPMENT_LINES_R;
REPLICATE [RCV_TRANSACTIONS_R_RCV_TRANSACTIONS_R] select * from RCV_TRANSACTIONS_R_RCV_TRANSACTIONS_R;
REPLICATE [XLE_ENTITY_PROFILES_R_XLE_ENTITY_PROFILES_R] select * from XLE_ENTITY_PROFILES_R_XLE_ENTITY_PROFILES_R;
REPLICATE [AP_HOLDS_ALL_R_AP_HOLDS_ALL_R] select * from AP_HOLDS_ALL_R_AP_HOLDS_ALL_R;
REPLICATE [AP_INV_APRVL_HIST_ALL_R_AP_INV_APRVL_HIST_ALL_R] select * from AP_INV_APRVL_HIST_ALL_R_AP_INV_APRVL_HIST_ALL_R;
REPLICATE [AP_INVOICE_LINES_ALL_R_AP_INVOICE_LINES_ALL_R] select * from AP_INVOICE_LINES_ALL_R_AP_INVOICE_LINES_ALL_R;
REPLICATE [AP_INVOICE_PAYMENTS_ALL_R_AP_INVOICE_PAYMENTS_ALL_R] select * from AP_INVOICE_PAYMENTS_ALL_R_AP_INVOICE_PAYMENTS_ALL_R;
REPLICATE [AP_INVOICES_ALL_R_AP_INVOICES_ALL_R] select * from AP_INVOICES_ALL_R_AP_INVOICES_ALL_R;
REPLICATE [AP_PAYMENT_SCHEDULES_ALL_R_AP_PAYMENT_SCHEDULES_ALL_R] select * from AP_PAYMENT_SCHEDULES_ALL_R_AP_PAYMENT_SCHEDULES_ALL_R;
REPLICATE [AP_SYSTEM_PARAMETERS_ALL_R_AP_SYSTEM_PARAMETERS_ALL_R] select * from AP_SYSTEM_PARAMETERS_ALL_R_AP_SYSTEM_PARAMETERS_ALL_R;
REPLICATE [AP_TERMS_LINES_R_AP_TERMS_LINES_R] select * from AP_TERMS_LINES_R_AP_TERMS_LINES_R;
REPLICATE [AP_TERMS_TL_R_AP_TERMS_TL_R] select * from AP_TERMS_TL_R_AP_TERMS_TL_R;
REPLICATE [EGP_CATEGORIES_TL_R_EGP_CATEGORIES_TL_R] select * from EGP_CATEGORIES_TL_R_EGP_CATEGORIES_TL_R;
REPLICATE [FND_LOOKUPS_R_FND_LOOKUPS_R] select * from FND_LOOKUPS_R_FND_LOOKUPS_R;
REPLICATE [FND_TERRITORIES_TL_R_FND_TERRITORIES_TL_R] select * from FND_TERRITORIES_TL_R_FND_TERRITORIES_TL_R;
REPLICATE [GL_DAILY_RATES_R_GL_DAILY_RATES_R] select * from GL_DAILY_RATES_R_GL_DAILY_RATES_R;
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;
REPLICATE [HZ_PARTIES_R_HZ_PARTIES_R] select * from HZ_PARTIES_R_HZ_PARTIES_R;
REPLICATE [IBY_PAYMENT_METHODS_TL_R_IBY_PAYMENT_METHODS_TL_R] select * from IBY_PAYMENT_METHODS_TL_R_IBY_PAYMENT_METHODS_TL_R;
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;
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;
REPLICATE [PER_LOCATION_DETAILS_F_R_PER_LOCATION_DETAILS_F_R] select * from PER_LOCATION_DETAILS_F_R_PER_LOCATION_DETAILS_F_R;
REPLICATE [PER_LOCATION_DETAILS_F_TL_R_PER_LOCATION_DETAILS_F_TL_R] select * from PER_LOCATION_DETAILS_F_TL_R_PER_LOCATION_DETAILS_F_TL_R;
REPLICATE [PER_USERS_R_PER_USERS_R] select * from PER_USERS_R_PER_USERS_R;
REPLICATE [PO_ACTION_HISTORY_R_PO_ACTION_HISTORY_R] select * from PO_ACTION_HISTORY_R_PO_ACTION_HISTORY_R;
REPLICATE [PO_HEADERS_ALL_R_PO_HEADERS_ALL_R] select * from PO_HEADERS_ALL_R_PO_HEADERS_ALL_R;
REPLICATE [PO_LINE_LOCATIONS_ALL_R_PO_LINE_LOCATIONS_ALL_R] select * from PO_LINE_LOCATIONS_ALL_R_PO_LINE_LOCATIONS_ALL_R;
REPLICATE [PO_LINE_TYPES_TL_R_PO_LINE_TYPES_TL_R] select * from PO_LINE_TYPES_TL_R_PO_LINE_TYPES_TL_R;

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

Input fields

The following tables include the list of fields per input table, their description, data type to be used when formatting the input and the filter flag to identify those that are being used to filter data.

Input types

Below is an overview of the different field types and their default format settings.

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

Input tables & fields

AP_HOLDS_ALL

Contains information about holds placed on invoices.

Field Type Stores Filtering
CREATED_BY Text Indicates the user who created the row.
CREATION_DATE Datetime Indicates the date and time of the creation of the row.
HELD_BY Text User that placed hold on invoice
HOLD_DETAILS Text Details of the hold that is placed on the invoice line.
HOLD_LOOKUP_CODE Text Name of the hold that is applied on an invoice line.
HOLD_REASON Text Reason for hold being placed on invoice
INVOICE_ID Text Unique invoice identifier that identifies the invoice against which a hold is placed. (Foreign key)
LAST_UPDATE_DATE Datetime Indicates the date and time of the last update of the row.
LAST_UPDATED_BY Text Indicates the user who last updated the row.
RELEASE_LOOKUP_CODE Text Release name that released the hold on the invoice.
RELEASE_REASON Text Reason for release being placed on invoice

AP_SYSTEM_PARAMETERS_ALL

Contains the parameters and default values defined to operate the Oracle Cloud Accounts Payable application system.

Field Type Stores Filtering
BASE_CURRENCY_CODE Text Functional currency code associated with the ledger.
ORG_ID Text Indicates the identifier of the business unit associated with the row. (Primary key)

AP_TERMS_LINES

Stores detail information about payment terms.

Field Type Stores Filtering
TERM_ID Text A unique identifier for the payment term.(Primary key)
DUE_DAYS Integer Number of days after terms date, used to calculate due date of invoice payment line
SEQUENCE_NUM Integer Number of the payment term line.(Primary key)
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_DAYS_2 Integer Number of days after terms date, used to calculate second discount available for invoice payment line
DISCOUNT_PERCENT_2 Double Percentage used to calculate second discount available for invoice payment line

AP_TERMS_TL

Language-dependent descriptions for payment terms.

Field Type Stores Filtering
TERM_ID Text A unique identifier for the payment term.(Primary key)
NAME Text Name of the payment term that is associated with the invoice.
DESCRIPTION Text Description of the payment term.
LANGUAGE Text Indicates the code of the language into which the contents of the translatable columns are translated.(Primary key)

EGP_CATEGORIES_TL

Language-dependent description for material groups.

Field Type Stores Filtering
CATEGORY_ID Text Category an item is assigned to when during new item creation (Primary key)
CATEGORY_NAME Text Translatable name of the category
LANGUAGE Text Indicates the code of the language into which the contents of the translatable columns are translated (Primary key)

FND_LOOKUPS

Descriptions for most of Oracle Cloud codes.

Field Type Stores Filtering
LOOKUP_TYPE Text Lookup_type
LOOKUP_CODE Text Lookup_code
MEANING Text Meaning

FND_TERRITORIES_TL

Language-dependent description for territories.

Field Type Stores Filtering
TERRITORY_CODE Text Territory_code (Primary key)
LANGUAGE Text Indicates the code of the language into which the contents of the translatable columns are translated (Primary key)
TERRITORY_SHORT_NAME Text Territory_short_name

GL_DAILY_RATES

Contains the daily conversion rates for foreign currency transactions.

Field Type Stores Filtering
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_RATE Double Currency conversion rate of a daily rate
CREATION_DATE Datetime Indicates the date and time of the creation of the row.

HR_ORGANIZATION_UNIT_F_TL

Language-dependent description for organization units.

Field Type Stores Filtering
ORGANIZATION_ID Text Foreign Key
LANGUAGE Text Indicates the code of the language into which the contents of the translatable columns are translated.
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.
EFFECTIVE_END_DATE Datetime Date Effective Entity: indicates the date at the end of the date range within which the row is effective.

HZ_PARTIES_INPUT

Stores basic information about third parties.

Field Type Stores Filtering
PARTY_ID Text Party identifier (Primary key)
PARTY_NAME Text Name of this party

IBY_PAYMENT_METHODS_TL

Language-dependent description for Payment terms.

Field Type Stores Filtering
PAYMENT_METHOD_CODE Text User-entered primary key (Primary key)
LANGUAGE Text Indicates the code of the language into which the contents of the translatable columns are translated
PAYMENT_METHOD_NAME Text Payment method name

INV_UNITS_OF_MEASURE_B

Contains the units of measure that are used to transact an item in Oracle Cloud inventory and other Oracle Cloud products.

Field Type Stores Filtering
UOM_CODE Text Unique short code assigned to a Unit of Measure
UNIT_OF_MEASURE_ID Text Unique identifier of the unit of measure (Primary key)

INV_UNITS_OF_MEASURE_TL

Language-dependent description of units of measure.

Field Type Stores Filtering
UNIT_OF_MEASURE_ID Text Unique identifier of the unit of measure (Primary key)
LANGUAGE Text Indicates the code of the language into which the contents of the translatable columns are translated (Primary key)
DESCRIPTION Text Translatable Unit of Measure description

PER_LOCATION_DETAILS_F

Stores the location-details-related attributes

Field Type Stores Filtering
LOCATION_ID Text Foreign Key
LOCATION_DETAILS_ID Text System generated primary key column (Primary key)
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)

PER_LOCATION_DETAILS_F_TL

Language-dependent description for location details.

Field Type Stores Filtering
LOCATION_DETAILS_ID Text System generated primary key column (Primary key)
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)
LANGUAGE Text Indicates the code of the language into which the contents of the translatable columns are translated (Primary key)
LOCATION_NAME Text Concatenation of Location Name & Alternate Location Code

PER_USERS

Stores one record per Oracle Cloud user.

Field Type Stores Filtering
USERNAME Text The latest principal username of the user
USER_ID Text Mandatory Primary Key Updatable While New Key Generation (Primary key)
PERSON_ID Text Person id in HCM for this user (if available)

PO_LINE_TYPES_TL

Language-dependent description for Purchasing document line types.

Field Type Stores Filtering
LINE_TYPE_ID Text Line type unique identifier (Primary key)
LANGUAGE Text Indicates the code of the language into which the contents of the translatable columns are translated
DESCRIPTION Text Description
LINE_TYPE Text Document line type

POZ_SUPPLIER_SITES_V

Stores basic supplier sites information.

Field Type Stores Filtering
VENDOR_SITE_ID Text Supplier site unique identifier (Primary key)
COUNTRY Text Country code from the TERRITORY_CODE column in the FND_TERRITORY table
PROVINCE Text Province
STATE Text State

POZ_SUPPLIERS

Stores supplier attributes.

Field Type Stores Filtering
VENDOR_ID Text Supplier unique identifier (Primary key)
PARTY_ID Text Party identifier
ONE_TIME_FLAG Text Indicates whether the supplier is a one-time supplier

XLE_ENTITY_PROFILES

Contains the general information for legal entities.

Field Type Stores Filtering
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

AP_INV_APRVL_HIST_ALL_INPUT

Contains the approval and rejection history of each invoice that passes through the Invoice Approval Workflow process.

Field Type Stores Filtering
INVOICE_ID Text Unique identifier for invoice that is undergoing the approval cycle (Foreign key)
RESPONSE Text Response of the approver on a given invoice
APPROVER_ID Text Login username of approver for given invoice
CREATION_DATE Datetime Indicates the date and time of the creation of the row
HISTORY_TYPE Text Approval type required on an invoice
APPROVER_COMMENTS Text Comments provided by the approver on a given invoice

AP_INVOICE_LINES_ALL_INPUT

Contains records for invoice lines.

Field Type Stores Filtering
INVOICE_ID Text Invoice line number associated with the event (Primary key and Foreign key)
LINE_NUMBER Text Number of the invoice line (Primary key)
AMOUNT Double Line amount in invoice currency
ORG_ID Text Indicates the identifier of the business unit associated to the row (Foreign key)
CREATED_BY Text Indicates the user who created the row
CREATION_DATE Datetime Indicates the date and time of the creation of the row
DESCRIPTION Text Description of the invoice line
BASE_AMOUNT Double Line amount in functional currency
PO_LINE_ID Text A line identifier for the line record on a transaction. The Purchase Order Line describes an item or service, with a price and quantity ordered (Foreign key)
QUANTITY_INVOICED Double Quantity of items for matched invoice lines, price corrections, quantity corrections or unmatched invoice lines
UNIT_MEAS_LOOKUP_CODE Text Unit of measure for quantity_invoiced
ITEM_DESCRIPTION Text Description of the item in the invoice line
LINE_TYPE_LOOKUP_CODE Text Type of invoice line. Valid values from invoice line type lookup codes

AP_INVOICE_PAYMENTS_ALL_INPUT

Contains records of invoice payments for suppliers.

Field Type Stores Filtering
INVOICE_ID Text Unique invoice identifier that identifies the invoice to which the payment belongs
INVOICE_PAYMENT_ID Text Unique identifier of the payment (Primary key)
CREATION_DATE Datetime Indicates the date and time of the creation of the row
AMOUNT Double Payment amount that is paid
PAYMENT_NUM Text Number identifying a payment
CREATED_BY Text Indicates the user who created the row
PAYMENT_CURRENCY_CODE Text The currency that the payment is made in
REVERSAL_INV_PMT_ID Text Identifier for invoice payment reversed through current invoice payment

AP_INVOICES_ALL_INPUT

Contains records for invoices you enter. There is one row for each one.

Field Type Stores Filtering
INVOICE_ID Text Unique invoice identifier (Primary key)
INVOICE_NUM Text Unique number for supplier invoice
INVOICE_AMOUNT Double Invoice amount in transaction currency
INVOICE_TYPE_LOOKUP_CODE Text Invoice category, such as standard, credit memo, or prepayment (Foreign key)
TERMS_ID Text Payment terms identifier used on the invoice (Foreign key)
CREATION_DATE Datetime Indicates the date and time of the creation of the row
GL_DATE Datetime Accounting date to default to invoice distributions
LEGAL_ENTITY_ID Text Unique identifier of the legal entity (Foreign key)
CREATED_BY Text Indicates the user who created the row
TERMS_DATE Datetime Date used with payment terms to calculate scheduled payment of an invoice
INVOICE_CURRENCY_CODE Text Currency code used on the invoice
PAYMENT_METHOD_CODE Text Indicates the payment method, such as check, cash, or credit (Foreign key)
CANCELLED_DATE Datetime Date when the invoice was canceled
CANCELLED_BY Text User ID of person who canceled an invoice
CANCELLED_AMOUNT Double Original amount of canceled invoice

AP_PAYMENT_SCHEDULES_ALL_INPUT

Contains information about scheduled payments for an invoice.

Field Type Stores Filtering
CREATION_DATE Datetime Indicated the date and time of the creation of the row
PAYMENT_STATUS_FLAG Text Flag that indicates if payment has been made
INVOICE_ID Text Identifier of the invoice referenced in payment schedule (Primary key)
PAYMENT_NUM Text Number used to identify a payment (Primary key)

PO_ACTION_HISTORY_INPUT

Contains information about the approval and control history of your purchasing documents (Purchase requisitions and all purchase order types are included).

Field Type Stores Filtering
OBJECT_ID Text Document header unique identifier (Primary key)
OBJECT_TYPE_CODE Text Document type (Primary key)
CREATION_DATE Datetime Indicates the date and time of the creation of the row
CREATED_BY Text Indicates the user who created the row
ACTION_CODE Text Approval or control action type (Primary key)
ACTION_DATE Datetime Approval or control action date
ROLE_CODE Text Role of the action performer
PERFORMER_ID Text Unique identifier of the person taking the action
NOTE Text Note for next approver or reason for control action

PO_HEADERS_ALL_INPUT

Contains header information for your purchasing documents.

Field Type Stores Filtering
PO_HEADER_ID Text Document header unique identifier (Primary key and Foreign key)
CREATION_DATE Datetime Indicates the date and time of the creation of the row
DOCUMENT_STATUS Text Header status. Possible values are: OPEN, REJECTED, CANCELED, CLOSE, INCOMPLETE, WITHDRAWN.
SOLDTO_LE_ID Text Sold to Legal Entity unique identifier. Indicates the party which is financially responsible for the purchases on a purchase order (Foreign key)
CREATED_BY Text Indicates the user who created the row
VENDOR_ID Text Supplier unique identifier (Foreign key)
SEGMENT1 Text Key Flexfield: segment of the key flexfield
PRC_BU_ID Text Business unit unique identifier of the purchase order to be created (Foreign key)
TYPE_LOOKUP_CODE Text Type of the document. Possible values are: STANDARD, BLANKET, CONTRACT.
CURRENCY_CODE Text Unique identifier for the currency
SHIP_TO_LOCATION_ID Text Ship-to location unique identifier (Foreign key)
VENDOR_SITE_ID Text Supplier site unique identifier (Foreign key)
RATE Double Currency conversion rate

PO_LINE_LOCATIONS_ALL_INPUT

Contains information about purchase order shipment schedules.

Field Type Stores Filtering
CREATION_DATE Datetime Indicates the date and time of the creation of the row
PO_LINE_ID Text Document line unique identifier (Foreign key)
PROMISED_DATE Datetime Promised Delivery Date.

PO_LINES_ALL_INPUT

Stores current information about each purchase order line.

Field Type Stores Filtering
PO_LINE_ID Text Document line unique identifier (Primary key and Foreign key)
PO_HEADER_ID Text Document header unique identifier (Foreign key)
LINE_NUM Text Line number
CATEGORY_ID Text Item category unique identifier (Foreign key)
ITEM_DESCRIPTION Text Item description
CREATION_DATE Datetime Indicates the date and time of the creation of the row
UNIT_PRICE Double Unit price for the line
UOM_CODE Text Unit of measure for the quantity ordered (Foreign key)
CREATED_BY Text Indicates the user who created the row
QUANTITY Double Quantity ordered on the line
CLOSED_DATE Datetime Date the line is closed
CLOSED_BY Text Unique identifier of the employee who closed the line
CANCELLED_BY Text Unique identifier of the employee who canceled the line
CANCEL_REASON Text Cancellation reason provided by employee
CANCEL_DATE Datetime Cancellation date
AMOUNT Double Budget Amount for temp labor standard Po_lines
CLOSED_REASON Text Describes why the line is closed

POR_REQUISITION_HEADERS_ALL_INPUT

Stores information about requisition headers.

Field Type Stores Filtering
REQUISITION_HEADER_ID Text Requisition header unique identifier (Primary key)
REQUISITION_NUMBER Text Requisition number
CREATION_DATE Datetime Indicates the date and time of the creation of the row
REQ_BU_ID Text Business unit unique identifier (Foreign key)

POR_REQUISITION_LINES_ALL_INPUT

Stores information about requisition items.

Field Type Stores Filtering
REQUISITION_LINE_ID Text Requisition line unique identifier (Primary key and Foreign key)
CREATION_DATE Datetime Indicates the date and time of the creation of the row
ITEM_DESCRIPTION Text Item description
LINE_STATUS Text Contains status of the requisition line
CATEGORY_ID Text Item category unique identifier (Foreign key)
LINE_TYPE_ID Text Number identifier for the line type. Possible values are: 1, 6. (Foreign key)
QUANTITY Double Quantity ordered
REQUESTER_ID Text Requester unique identifier (Foreign key)
LINE_NUMBER Text Line number
REQUISITION_HEADER_ID Text Requisition header unique identifier (Foreign key)
CURRENCY_CODE Text Unique identifier for the currency
UOM_CODE Text Code for the unit of measure (Foreign key)
PO_LINE_ID Text Purchase order line unique identifier (Foreign key)
UNIT_PRICE Double Unit price in functional currency
CURRENCY_UNIT_PRICE Double Foreign currency unit price
CREATED_BY Text Indicates the user who created the row
AMOUNT Double Amount on the requisition line
CURRENCY_AMOUNT Double Foreign currency amount on the requisition line

RCV_SHIPMENT_LINES_INPUT

Stores information about items that have been shipped and/or received.

Field Type Stores Filtering
SHIPMENT_LINE_ID Text This column stores the receiving shipment receiving receipt line unique identifier. This is the primary key for the row (Primary key)
PO_LINE_ID Text This column stores the purchase order line unique identifier (Foreign key)
CREATION_DATE Datetime Indicates the date and time of the creation of the row
QUANTITY_RECEIVED Double This column stores the net quantity received for the receiving shipment receipt line
AMOUNT_RECEIVED Double Amount shipped for a fixed price service receipt line in purchase order currency

RCV_TRANSACTIONS_INPUT

Stores historical information about receiving transactions.

Field Type Stores Filtering
CREATION_DATE Datetime Indicates the date and time of the creation of the row
CREATED_BY Text Indicates the user who created the row
TRANSACTION_TYPE Text Receiving transaction type.
TRANSACTION_DATE Datetime This column stores the transaction date for the receiving transactions
QUANTITY Double This column stores the receiving transaction quantity stored in the receiving transaction UOM
UOM_CODE Text This column stores the unit of measure code for the receiving transaction (Foreign key)
SHIPMENT_LINE_ID Text This column stores the receiving shipment receiving receipt header unique identifier (Foreign key)
SOURCE_DOCUMENT_CODE Text This column stores the source document code for the receiving transactions (Foreign key)
DESTINATION_TYPE_CODE Text This column stores the destination type code when the receiving transaction is created (Foreign key)
CURRENCY_CODE Text This column stores the currency code for the receiving transaction
USER_ENTERED_FLAG Text This column indicates where the current receiving transaction row is user-entered or automatically generated
AMOUNT Double Transaction Amount in purchase order currency

Design specifications

Objects

Entity Transactional tables Master data tables
Purchase requisitions Por_requisition_lines_all, Por_requisition_headers_all Egp_categories_tl, Po_line_types_tl, Per_users, Ap_system_parameters_all, Inv_units_of_measure_b, Inv_units_of_measure_tl, Gl_daily_rates
Purchase orders Po_headers_all Xle_entity_profiles, Hr_organization_units_f_tl, Poz_suppliers, Hz_parties, Fnd_territories_tl, Poz_supplier_sites_v
Purchase order items Po_lines_all, Por_requisition_lines_all, Rcv_shipment_lines, Po_line_locations_all, Po_headers_all Egp_categories_tl, Ap_system_parameters_all, Per_location_details_f, Per_location_details_f_tl, Inv_units_of_measure_b, Inv_units_of_measure_tl
Goods receipt Rcv_shipment_lines
Invoice Ap_invoices_all Xle_entity_profiles,, Ap_terms_lines, Iby_payment_methods_tl, Ap_terms_tl, Fnd_lookups
Invoice item Ap_invoice_lines_all, Ap_invoices_all Hr_organization_units_f_tl, Inv_units_of_measure_b, Inv_units_of_measure_tl, Ap_system_parameters_all, Gl_daily_rates
Accounting documents Ap_invoices_all
Payments Ap_invoice_payments_all, Ap_payment_schedules_all

Activities

Activities can be created in the following ways:

  1. Activities that can be created based on the transactional table information (i.e. Por_requisition_lines_all, Po_headers_all, Po_lines_all).
  2. Activities that can be created based on the historical tables information (i.e. Po_action_history, Ap_inv_aprv_hist_all).

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

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

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

Entity Activity Activity code Transactional/ Historical table Master data table
Purchase requisitions Create Purchase Requisition CREATE_PR Por_requisition_lines_all Setup_users
Purchase orders Create Purchase Order CREATE_PO Po_headers_all Setup_users
Purchase order items Create Purchase Order Item CREATE_PO_ITEM Po_lines_all Setup_users
Purchase order items Cancel Purchase Order Item null Po_lines_all Per_users
Purchase order items Close Purchase Order Item null Po_lines_all Per_users
Invoices Create Invoice CREATE_INVOICE Ap_invoices_all Setup_users
Invoices Cancel Invoice null Ap_invoices_all Setup_users
Invoice items Create Invoice Item CREATE_INVOICE_ITEM Ap_invoice_lines_all Setup_users
Payments Execute Outgoing Payment CREATE_OUTGOING_PAYMENT Ap_invoice_payments_all Setup_users
Payments Void Outgoing Payment null Ap_invoice_payments_all Setup_users

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

Activities that are recorded in historical tables are called Action_codes. These Action_codes are mostly standard across Oracle Cloud implementations. However, there is always the possibility of customized action_codes or the creation and modification of standard codes defined by Oracle Cloud.

As an example, depending on the implementation, withdraw of invoice approvals can be called WITHDRAWN or WITHDRAW.

For all standard Action_codes, it is possible to set them up in advance in the setup_activities seed-file. All non-standard or new Action_codes should be added.

It is possible to filter out activities that were generated following this approach, in case:

This is done by the field Action_code_removal_flag in the Setup_activities supporting table. All activities will be removed from the model if they have the flag set to "Y".

The following models belong to the option 2:

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

Action_code Activity Description
REQUISITION REASSIGNED Purchase requisition reassigned Purchase Requisition is assigned to a new preparer
OPEN Purchase order opened PO document that has not been fully received or billed. Delivery of goods is not complete
CORRECT Goods received-quantity corrected Items were corrected on receipt (for less or more)
ORA_RESUBMITTED Invoice approval resubmitted Invoice is resubmitted due to changes; a new approval is needed

The table below displays the basic logic used for naming the activities and selecting the activity code. Note that for all objects the Activity code column should be populated by the activity code from the Setup_activities table.

Entity Activity Activity code Transactional/ Historical table Master data table
Purchase requisitions Setup_activities."Activity name". If null, then Po_action_history."Action_code" Setup_activities."Activity code" Po_action_historyPor_requisition_lines_all Per_usersHz_partiesSetup_activities
Purchase orders Setup_activities."Activity name". If null then Po_action_history."Action_code" Setup_activities."Activity code" Po_action_history Per_usersHz_partiesSetup_activities
Goods receipt Setup_activities."Activity name" Setup_activities."Activity code" Rcv_transactions Setup_activitiesUom_descriptionSetup_usersFnd_lookups
Invoices Setup_activities."Activity name". If null then Ap_inv_aprvl_hist_all.Response Setup_activities."Activity code" Ap_inv_aprvl_hist_all Setup_activities Setup_users
Invoices Setup_activities."Activity name". If null then Ap_holds_all_input.Hold_lookup_code Setup_activities."Activity code" Ap_holds_all_input Setup_activities, Setup_users
Invoices Setup_activities."Activity name". If null then Ap_holds_all_input.Release_lookup_code Setup_activities."Activity code" Ap_holds_all_input Setup_activities, Setup_users
Invoice items Setup_activities."Activity name". If null then Ap_holds_all_input.Hold_lookup_code Setup_activities."Activity code" Ap_holds_all_input Setup_activities, Setup_users
Invoice items Setup_activities."Activity name". If null then Ap_holds_all_input.Release_lookup_code Setup_activities."Activity code" Ap_holds_all_input Setup_activities, Setup_users

Design details

Each of the next subsections will clarify the transformations.

Supporting tables

Currency_exchange_rates 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.

Locations_aggregated This table takes the latest promise date for each Purchase order item which will be used for creating the latest expected delivery date. It is generated by taking the maximum (latest) promised date. This is a date that the supplier has promised to deliver the ordered item or service.

Locations_description This table is used to bring the name for location_id's. It also brings the effective dates for each name that a location_id could have had.

Requisition_lines_aggregated Oracle Cloud records the relationship between purchase requisition and purchase orders in Purchase requisitions tables, resulting in a many to one relationship as many requisitions could be linked to the same Purchase order item. However, this functionality is not commonly used; businesses usually use one to one relationship. In order to provide a relationship that can work with the data model, this table is only taking the first requisition that was matched to a purchase order item and it will drop any subsequent matches, thus creating a one-to-one relationship.

Shipment_lines_aggregated This table is used in the logic for Delivery complete and Latest_actual_delivery_date attributes in the Purchase order items base table. It sums all the quantities and amounts received for each purchase order item.

Supplier_name This table is used to bring the name and the one_time_flag of the supplier.

Uom_description This table is used to bring the description of the unit of measure based on language.

Objects

Accounting_documents_base This object does not exist in Oracle Cloud so Invoice_id from Ap_invoices_all is used as both Invoice ID and Accounting document ID.

Goods_receipt_base Receiving table Rcv_shipment_lines is used for creating this entity. Oracle Cloud allows managing inbound logistics such as receiving, inspecting, and putting away material. This table stores information about items that have been shipped or received and about the default destination for in-transit shipments.

Invoice_items_base Transactional table Ap_invoice_lines_all was used as the backbone for this entity.

Invoices_base Oracle Cloud allows payment terms with installments. And for each installment, it also allows for up to 3 dates for available discounts. The logic will only take the first installment and the first 2 dates for discounts as these are the only values that can be mapped to DA.

It is worth mentioning that even though all payment terms are applied automatically, some users may have access to manually overwrite the payment terms information, creating different discount dates, amounts or even adding installments. In this case, the information provided to DA will differ from that on Oracle Cloud. However, this manual overwriting should not happen often.

Briefly, payment terms are defined at business unit level, at supplier level and during the purchase order creation. When creating an invoice, if it is associated with a Purchase order, the latter provides the payment terms for the invoice. If it is not associated, payment terms are taken from the supplier site. If the supplier site does not have payment terms, the payment terms are set automatically by the default ones in the business unit. As mentioned before, it is still possible to manually overwrite the automated payment terms on any invoice when creating it.

Payments_base This table was created using two Oracle Cloud transactional tables Ap_invoice_payments_all, which contains all payments done in the system and Ap_payment_schedules_all, which contains information about scheduled payments for an invoice. In Oracle Cloud, payments can be related to the schedules by using Invoice_id and its Payment_num fields; these 2 fields represent respectively the invoice and the installment that is being paid for it.

Oracle Cloud has a field called payment_status_flag that works at installment level, not invoice level. This flag indicates the status of the installment. Valid values are:

This means that a single invoice could have multiple payment_status_flag populated with 'Y' value, should they have multiple installments. The logic to populate Payments base's attribute Payment_is_complete only sets TRUE when the last installment is paid in full.

The model assumes that if the last installment is paid in full, then the entire invoice is paid in full.

As a final comment, it is important to mention that Oracle Cloud assigns a separate Payment_id to a record that voids a previous Payment_id. For traceability purposes the logic will not generate a separate record for voids in this object table. For the events, the payment and its void will both be connected to the original Payment_id. This also means that the activities regarding payments and its void (i.e. Create_payment_void) will be both connected to the original Payment_id.

Purchase_order_items_base The Purchase_requisition_ID field will be populated with the first requisition that was linked to the purchase order item.

Purchase_orders_base The logic for this object contains multiple master data tables to populate the supplier attribute fields.

Purchase_requisitions_base Records included in this object represent Purchase requisition lines. Records from the requisition's headers table of Oracle Cloud (Por_requisition_headers_all) were also taken to populate further information required in the model (i.e. the user-friendly name to identify the purchase requisition).

Events

Goods_receipt_events All goods receipt events will be tracked with only one table (Rcv_transactions) as it contains all the information required. It is possible that a reception does not have a purchase order item associated with it, and it will not break the transformations.

The Rcv_transactions table contains transactions of distinct types:

The logic filters out transfers as they are out of scope for a P2P process.

Event_detail attribute was populated with the type of destination (Destination_type_code in Oracle Cloud) of the goods receipt. Destination types for Oracle Cloud are listed below with their descriptions.

Destination value Destination description
DROP SHIP Drop ship reception
EXPENSE Non-inventory items, they only impact expense accounts
INVENTORY Inventory items, they impact Inventory accounts
MANUFACTURING Items used for kits (BOM (bill of materials)) or manufacturing
RECEIVING Initial reception of the items from a PO.

This table contains the logic for removing unwanted activities.

Invoice_approval_events This table contains all the events of invoice approvals. In Oracle Cloud, approvals happen at header level, so the logic was created following that approach. This table contains all the events of invoice approvals.

In Oracle Cloud, approvals happen at header level, so the logic was created following that approach. The process starts if the invoice approvals workflow is enabled. 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.

Invoice_cancellation_events This table contains all the events of invoice cancellations. Cancellations follow the same approach given for Invoice approvals (only happening at header level).

Invoice_creation_events This table contains all the events of invoice creations and they are derived from the transactional table used for the creation of the object (Ap_invoices_all).

Invoice_hold_events This table contains all invoice hold events created using the Ap_holds_all table. The latter contains records that represent holds at header or item level. These records also include hold-release information when that action took place. To create invoice_hold_events at header level, item holds were filtered out and treated separately. The hold release events are also treated separately.

This table contains the logic for removing unwanted activities.

Invoice_hold_release_events This table contains all the events of the hold release of an invoice using the Ap_holds_all table, applying filters to only capture header-level hold-release records.

This table contains the logic for removing unwanted activities.

Invoice_item_creation_events This table contains all the events of the creation of an invoice, it was created using the transactional table Ap_invoice_lines_all.

Invoice_item_hold_events This table contains all the events of the hold release of an invoice using the Ap_holds_all table, applying filters to only capture item-level hold records.

This table contains the logic for removing unwanted activities.

Invoice_item_hold_release_events This table contains all the events of the hold release of an invoice using the Ap_holds_all table, applying filters to only capture item-level hold-release records.

This table contains the logic for removing unwanted activities.

Invoice_Payment_executed_events This table contains all the events of the payment of an invoice, it was created using the transactional table Ap_invoice_payments_all. As previously discussed, this table also contains void payment events that must be filtered out by using the Reversal_inv_pmt_id field, which is only populated when it is a void activity.

Invoice_payment_void_events This table contains all the void payment events, and it was created using the transactional table Ap_invoice_payments_all. In order to void a payment, Oracle Cloud generates a new Payment_id that is offsetting the original amount that is void. These new Payment_id's are not being used. Instead, the logic assigns the original Payment_id that can be found in the Reversal_inv_pmt_id field.

Purchase_order_creation_events This table contains all the events of the creation of a purchase order at header level; it was created using the transactional table Po_headers_all.

Purchase_order_events This table was created using the Po_action_history table. This is a historical table which contains information about all Oracle Cloud purchasing documents, so it was filtered by its document type field (Object_type_code) to only select events related to Purchase orders.

This table contains the logic for removing unwanted activities.

Purchase_order_item_cancellation_events This table was created using the transactional table Po_lines_all. In Oracle Cloud, if the transactional table has its cancelled_by and cancel_date fields populated, that means the purchase order was canceled. This logic is used for filtering the records that were canceled.

Purchase_order_item_close_events This table was created using the transactional table Po_lines_all. In Oracle Cloud, close activity at item level is not part of the P2P process, so it is classified as "Change" in Activity_category and it is always done manually by a user.

Purchase_order_item_creation_events This table was created using the transactional table Po_lines_all.

Purchase_requisition_creation_events This table was created using the transactional table Por_requisition_lines_all. The logic considers the creation of the purchase requisition at item level the same as its object.

Purchase_requisition_events This table was created using the Po_action_history table. As mentioned before, this is a historical table which contains information about all Oracle Cloud purchasing documents, so it was filtered by its document type field (Object_type_code) to only select events related to Purchase requisition orders.

As the data model does not have an object for Purchase requisition headers, all activities happening at that level were assigned to each item.

This table also contains the logic for removing unwanted activities.

Customizing the 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

Setup_activities

Each Oracle Cloud implementation will contain a customized set of activities that cannot be known in advance. These activities are called Action_codes. Therefore, it is difficult to automatically populate activity fields like ACTIVITY_CATEGORY, ACTIVITY_CODE and AUTOMATED_FLAG. This information must be provided in the setup_activities.csv file.

The setup_activities.csv only applies to those activities that are generated based on the following tables:

Field Type Description Example
Object_Type_Code varchar(50) Code for the object (Valid values: REQ, PO, GR, INV, INV_HOLD) REQ
Action_Code varchar(50) Code used in the aforementioned tables that represent each activity in Oracle Cloud APPROVE
Activity_Name varchar(100) The name of the activity. It is set manually by the consultant or the business following any naming convention from either the business or UiPath. Purchase requisition is approved
Activity_Category varchar(50) The type of activity that takes place (valid values Change, Set block, Remove block or NULL) NULL
Activity_code varchar(50) The code for the activity that takes place. Valid values are defined by UiPath and the DA model. They are usually used to populate metrics and charts in the P2P DA app APPROVE_PR
Activity_Order Integer The number that defines in which order activities are executed in case they have the same Event end 2
Action_Code_Removal_Flag varchar(1) Flag that removes an activity from the Events_end table. Valid values: Y, N or blank. If Y then that activity will be filtered out from model. Any other value (N or blank) is not used in any logic N
Automated_flag varchar(1) Flag that defines automated activities. Valid values: Y, N or blank. If 'Y' then this activity will be considered automated Y
Description varchar(500) Detailed description of the activity. It is not used at all in any logic. It's only used for storing notes on what the activity represents. Purchase requisition is approved, following the workflow that was set in the system.

This file comes populated by default with all common and standard ACTION_CODES from Oracle Cloud. However, each Oracle Cloud implementation could potentially have other codes not included. In order to confirm that all codes have a record in the setup_activities file, there is an auxiliary table Setup_activities_auxiliary.sql that gets created when running the transformations.

Open the Setup_activities_auxiliary table and search for all those records where the OBJECT_TYPE_CODE is null. That will list all activities that are missing their setups. Then, these activities must be added in the setup_activities.csv seed file and after that the transformations must be run again (commands: dbt seed and then dbt run)

However, if no data is created for a particular activity (defined in the setup_activities.csv as ACTION_CODE) then that activity will still be included in the model but instead of having a user-friendly name it will have the original ACTION_CODE. That activity will also lack all other fields (ACTIVITY_CATEGORY, ACTIVITY_ORDER, etc).

This seed file will also filter out all activities with Action_Code_Removal_Flag = 'Y'. This is used for removing activities that are not relevant, or that are being tracked with other tables or any other reason it seemed fit to remove them.

Setup_users

There are not always automated flags or ways to use algorithms to find out what activities were made automatically. In this case, you can manually set those users that are deemed automated.

The setup_users.csv seed file contains the information of all automated users that need to be flagged manually. There is no need to add all users that start with 'FUSION_APPS' as the logic is already flagging them as Automated.

Field Type Description Example
User_name varchar(64) Username as stored in transactional tables CRMOD_APPS_OSN_INTEG_APPID
Automated_user_flag varchar(1) Y or N(blank) value to identify automated users Y

The auxiliary table setup_users_auxiliary.sql was created to help you with finding possible automated users. This table contains a list of usernames, PERSON_FLAG and THIRD_PARTY_FLAG.

A first approach is to check for those users with PERSON_FLAG= 'N' as they may not be employees. Once that filter is done, usernames can also be used to spot automated users.

Last, third party users can be identified with the THIRD_PARTY_FLAG and checked with business users to find out whether their activities are automated.

DBT Variables

Variable Type Description
datetime_format string / integer Specifies the date format which will be used when converting to DateTime type.
display_currency string Currency code used for all "value" attributes. All amounts (Values) will be converted to the selected display_currency.
language string Language used to translate descriptions.
start_date string / integer This date is used to replace values that are not a valid date for the database.

Limitations, known issues, common problems

Limitations

Loading of files

Loading too many records could overload the Oracle Cloud system and can interrupt the creation of the CSV file from the report. To avoid this the report should be filtered by a time window. Example: Create the report and filter by 01-01-2021 until 01-06-2021 and extract the first CSV file. After, open the same report and modify the date filter to 02-06-2021 until 31-12-2021. 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];

Oracle Cloud updates

Though there is a high confidence that new versions of Oracle Cloud will still be compatible with the app template, it is not possible to predict what changes will surface and what their impact could be. There is one new feature that Oracle is working on (with no ETA nor specific information shared to the public) to make the table PO_action_history to start storing historical information at item level. It is currently tracking all events at header level, even for those that are actually item events. When this feature comes into place, it is advised to review the app template as it may need updates as well.

Contracts and Blanket orders

Relationships between Contracts or Blanket orders to Purchase orders are not being tracked in the DA, therefore, these orders have small number of activities associated to them and they are also not linked to the End-to-end process which is based in either a Purchase_order_id or an Invoice_id.

Known issues

Display currency

In the rare case where the client does not have the same reporting currency code across its multiple companies, it may happen that currency conversion rates are not available for converting everything into a single currency.

CSV files names

The name of the "raw" tables match the name of the CSV file created during extraction. CSV files take the name from the report that generated them but it's 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 that would differ from what the automatic extraction would do with the SOAP script. In order to keep both ways of extraction, it was decided to keep everything as standard as possible.

Common problems

Blanks replaced by null

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