This app template uses Oracle Cloud/Oracle Fusion as source system, which in the remainder of this documentation will be referenced as Oracle Cloud. Extraction for the system is done by exporting CSV files and load this data using CData Sync. Make sure you have a valid license for CData Sync and have installed CData Sync.
Oracle Cloud is a Software as a Service (SaaS) and it gets constant updates that are deployed quarterly, naming its version by the concatenation of the year (2 digits) and a letter that represents the quarter (A, B, C and D). This extraction was tested for Oracle Cloud v.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.
Stands for Data model
Stands for Report
Below is a description of the steps to execute Oracle Cloud reports. There are two kinds of reports. Reports that refer to transactional data (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.
To set up the destination connection, follow the steps as defined in Create a destination connection (Automation Cloud). Note: If you are using Automation Suite, make sure to select AUTOMATION SUITE
as the DELIVERY
option and select the correct version of Automation Suite you are using.
To create the Job in CData make sure to follow the steps below.
In the Advanced tab in the Job Settings panel, define the following settings:
After the data has been successfully loaded, the Process Mining platform needs to start the data ingestion process. This is done by calling the End of Upload API. In order to set this up, follow the steps below:
Step | Action |
---|---|
1 | Go to the Events tab in the Job Settings panel of the job you are creating |
2 | Edit the Post-Job Event section to add the code displayed below after <!-- Code goes here -- > . Do not modify the api:info details that are shown by default. |
3 | Fill out the End of Upload API with the value provided. |
4 | In case you need a debugging log for this call, uncomment the two lines provided in the code below and provide a location for the log file. |
5 | Click on Save Changes. |
<api:set attr="http.url" value="END_OF_UPLOAD_API"/>
<!-- <api:set attr="http.verbosity" value="5"/> -->
<!-- <api:set attr="http.logfile" value="D:/mydir/cdata_log.txt"/> -->
<api:call op="httpPost" in="http"/>
Once the job is correctly setup, click on Add Custom Query under the Tasks tab and paste the following queries (each query needs to maintain the semicolon at the end). Make sure you save all changes.
REPLICATE [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.
The following tables include the list of fields per input table, their description, data type to be used when formatting the input and the filter flag to identify those that are being used to filter data.
Below is an overview of the different field types and their default format settings.
Field type | Description |
---|---|
boolean | true , false , 1 , 0 |
datetime | YYYY-MM-DDThh24:mi:ss.ff3 |
double | Decimal separator: . (dot); thousand separator: none |
integer | Thousand separator: none |
text | N/A |
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 |
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) |
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 |
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) |
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) |
Descriptions for most of Oracle Cloud codes.
Field | Type | Stores | Filtering |
---|---|---|---|
LOOKUP_TYPE | Text | Lookup_type | |
LOOKUP_CODE | Text | Lookup_code | |
MEANING | Text | Meaning |
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 |
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. |
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. |
Stores basic information about third parties.
Field | Type | Stores | Filtering |
---|---|---|---|
PARTY_ID | Text | Party identifier (Primary key) | |
PARTY_NAME | Text | Name of this party |
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 |
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) |
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 |
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) |
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 |
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) |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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) |
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 |
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 |
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. |
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 |
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) |
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 |
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 |
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 |
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 can be created in the following ways:
Activities were created from transactional tables whenever there was enough information available (i.e. event_end and user information)
Each of these activities have a specific DBT model where they're being created and the names of the models derived from the activity names.
Entity | Activity | 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 |
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 entities 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 |
Each of the next subsections will clarify the transformations.
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.
Accounting_documents_base This entity 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 entity 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 entity contains multiple master data tables to populate the supplier attribute fields.
Purchase_requisitions_base Records included in this entity 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).
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 entity (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 entity.
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 entity 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.
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 |
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:
PO_ACTION_HISTORY
RCV_TRANSACTIONS
AP_INV_APRVL_HIST_ALL
AP_HOLDS_ALL
Field | Type | Description | Example |
---|---|---|---|
Object_Type_Code | varchar(50) | Code for the entity (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.
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.
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. |
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];
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.
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.
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.
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.
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.