Oracle-Cloud-Purchase-to-Pay.md

Oracle-Cloud-Purchase-to-Pay.md

Introduction

Purchase-to-Pay process

The Purchase-to-Pay process is an end-to-end process that starts with a requisition order and ends up with the payment of the supplier's invoice. The Purchase-to-Pay process consists of steps such as: Creating & approving the purchase requisition, creating, approving & closing the purchase order, receiving the goods, creating & approving the invoice and executing the outgoing payment. With the Oracle Cloud Purchase-to-Pay app template you can create process apps that give insight in how your Purchase-to-Pay process actually performs. For example, how long it takes for a purchase request to become an actual Purchase Order, how long it takes to process the Purchase Orders, and most important to what extent are you paying in time. For all steps in the process, Purchase-to-Pay provides insights into the throughput times.

Oracle Cloud and Oracle Fusion are the same product. Therefore, their names are considered synonyms in this documentation and they will all be referred to as Oracle Cloud.

  • Requisition orders
  • Contract purchase agreement
  • Blanket orders
  • Purchase orders

Blanket orders and contracts will be considered, for all purposes, a type of Purchase order.

Accounts Payable module includes all events happening to either:

  • Invoices matched to Purchase orders
  • Standalone invoices

Oracle Cloud configuration

Source system

Oracle Cloud v.21D and 22A.

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

The following modules must be used:

  • Self-service procurement (Purchase requisitions module)
  • Purchasing
  • Accounts payable

The model was created using standard Oracle Cloud tables that are required for running a Purchase-to-Pay process.

Environmental settings

The user maintaining the data models and reports should have the BI Administrator role.

Loading data from Oracle Fusion Cloud

Note: To generate the csv files it is required to use Oracle Transactional Business Intelligence (OTBI) tool.

Follow the steps below to load data from Oracle Cloud into a Oracle Cloud Purchase-to-Pay process app in Process Mining..

Upload object to Oracle Cloud

  • Sign in to Oracle Cloud and navigate to Reports & Analytics - Browse Catalog. See the illustration below.
  • Once the window is displayed, select SHARED FOLDERS - Unarchive. A new box will be displayed
    • Browse and select the file UiPath.catalog that contains the structured reports and data models.
    • Select ALL from the Replace options;
    • Select **INHERIT ** from the ACL options.
  • 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.
  • Click on each of the sub folders to view data models and reports
    • Stands for Data model

    • Stands for Report

    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.

  • Select the report you want to execute. Click on Open
    • Transactional reports required date ranges to filter the information. They are mandatory.
    • Master data reports do not require any filter to be done.
  • To export the report, select the gear icon at the top right corner of the page, EXPORT > CSV The report will be downloaded locally.
  • Load from File Data Import

    In order to load the CSV files into the data model, CData Sync will be used. In general, you should follow the steps as described in Loading data using CData Sync (Snowflake) or Loading data using CData Sync (SQL Server) to set up data loading using CData Sync. Since specific settings are required when using CSV files from Oracle Fusion Cloud, pay attention to the steps described below.

    Setting up the source connection

    • Follow the steps described in the section Setting up a source connection for csv or tsv.
    • Oracle Cloud creates CSV files with pipe(|) as delimiter when they are manually extracted. If CSV files are pipe delimited, specify in your source connection under FMT the pipe character |.

    Note: When creating reports if a field is completely filled with nulls it will remove the column from the generated CSV file. Therefore, when this happens the report will replace the nulls to the string 'null' to keep the column in the generated CSV file.

    Define the following settings in the Replicate Options section in the Advanced tab in the Job Settings panel.

    • Under Destination Schema, setup the target data schema in the SQL Server or Snowflake database.
    • Select the Drop Table option.

    Table Replication

    Once the job is correctly setup, click on Add Custom Query under the Tables tab and paste the following queries (each query needs to maintain the semicolon at the end). Make sure you save all changes.

    REPLICATE [PO_LINES_ALL_R_PO_LINES_ALL_R_raw] select * from PO_LINES_ALL_R_PO_LINES_ALL_R; REPLICATE [POR_REQUISITION_HEADERS_ALL_R_POR_REQUISITION_HEADERS_ALL_R_raw] select * from POR_REQUISITION_HEADERS_ALL_R_POR_REQUISITION_HEADERS_ALL_R; REPLICATE [POR_REQUISITION_LINES_ALL_R_POR_REQUISITION_LINES_ALL_R_raw] select * from POR_REQUISITION_LINES_ALL_R_POR_REQUISITION_LINES_ALL_R; REPLICATE [POZ_SUPPLIER_SITES_V_R_POZ_SUPPLIER_SITES_V_R_raw] select * from POZ_SUPPLIER_SITES_V_R_POZ_SUPPLIER_SITES_V_R; REPLICATE [POZ_SUPPLIERS_R_POZ_SUPPLIERS_R_raw] select * from POZ_SUPPLIERS_R_POZ_SUPPLIERS_R; REPLICATE [RCV_SHIPMENT_LINES_R_RCV_SHIPMENT_LINES_R_raw] select * from RCV_SHIPMENT_LINES_R_RCV_SHIPMENT_LINES_R; REPLICATE [RCV_TRANSACTIONS_R_RCV_TRANSACTIONS_R_raw] select * from RCV_TRANSACTIONS_R_RCV_TRANSACTIONS_R; REPLICATE [XLE_ENTITY_PROFILES_R_XLE_ENTITY_PROFILES_R_raw] select * from XLE_ENTITY_PROFILES_R_XLE_ENTITY_PROFILES_R; REPLICATE [AP_HOLDS_ALL_R_AP_HOLDS_ALL_R_raw] select * from AP_HOLDS_ALL_R_AP_HOLDS_ALL_R; REPLICATE [AP_INV_APRVL_HIST_ALL_R_AP_INV_APRVL_HIST_ALL_R_raw] 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_raw] select * from AP_INVOICE_LINES_ALL_R_AP_INVOICE_LINES_ALL_R; REPLICATE [AP_INVOICE_PAYMENTS_ALL_R_AP_INVOICE_PAYMENTS_ALL_R_raw] select * from AP_INVOICE_PAYMENTS_ALL_R_AP_INVOICE_PAYMENTS_ALL_R; REPLICATE [AP_INVOICES_ALL_R_AP_INVOICES_ALL_R_raw] select * from AP_INVOICES_ALL_R_AP_INVOICES_ALL_R; REPLICATE [AP_PAYMENT_SCHEDULES_ALL_R_AP_PAYMENT_SCHEDULES_ALL_R_raw] select * from AP_PAYMENT_SCHEDULES_ALL_R_AP_PAYMENT_SCHEDULES_ALL_R; REPLICATE [AP_SYSTEM_PARAMETERS_ALL_R_AP_SYSTEM_PARAMETERS_ALL_R_raw] select * from AP_SYSTEM_PARAMETERS_ALL_R_AP_SYSTEM_PARAMETERS_ALL_R; REPLICATE [AP_TERMS_LINES_R_AP_TERMS_LINES_R_raw] select * from AP_TERMS_LINES_R_AP_TERMS_LINES_R; REPLICATE [AP_TERMS_TL_R_AP_TERMS_TL_R_raw] select * from AP_TERMS_TL_R_AP_TERMS_TL_R; REPLICATE [EGP_CATEGORIES_TL_R_EGP_CATEGORIES_TL_R_raw] select * from EGP_CATEGORIES_TL_R_EGP_CATEGORIES_TL_R; REPLICATE [FND_LOOKUPS_R_FND_LOOKUPS_R_raw] select * from FND_LOOKUPS_R_FND_LOOKUPS_R; REPLICATE [FND_TERRITORIES_TL_R_FND_TERRITORIES_TL_R_raw] select * from FND_TERRITORIES_TL_R_FND_TERRITORIES_TL_R; REPLICATE [GL_DAILY_RATES_R_GL_DAILY_RATES_R_raw] select * from GL_DAILY_RATES_R_GL_DAILY_RATES_R; REPLICATE [HR_ORGANIZATION_UNITS_F_TL_R_HR_ORGANIZATION_UNITS_F_TL_R_raw] select * from HR_ORGANIZATION_UNITS_F_TL_R_HR_ORGANIZATION_UNITS_F_TL_R; REPLICATE [HZ_PARTIES_R_HZ_PARTIES_R_raw] select * from HZ_PARTIES_R_HZ_PARTIES_R; REPLICATE [IBY_PAYMENT_METHODS_TL_R_IBY_PAYMENT_METHODS_TL_R_raw] 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_raw] 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_raw] 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_raw] 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_raw] select * from PER_LOCATION_DETAILS_F_TL_R_PER_LOCATION_DETAILS_F_TL_R; REPLICATE [PER_USERS_R_PER_USERS_R_raw] select * from PER_USERS_R_PER_USERS_R; REPLICATE [PO_ACTION_HISTORY_R_PO_ACTION_HISTORY_R_raw] select * from PO_ACTION_HISTORY_R_PO_ACTION_HISTORY_R; REPLICATE [PO_HEADERS_ALL_R_PO_HEADERS_ALL_R_raw] select * from PO_HEADERS_ALL_R_PO_HEADERS_ALL_R; REPLICATE [PO_LINE_LOCATIONS_ALL_R_PO_LINE_LOCATIONS_ALL_R_raw] select * from PO_LINE_LOCATIONS_ALL_R_PO_LINE_LOCATIONS_ALL_R; REPLICATE [PO_LINE_TYPES_TL_R_PO_LINE_TYPES_TL_R_raw] 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 typeDescription
    booleantrue, false, 1, 0
    datetimeYYYY-MM-DDThh24:mi:ss.ff3
    doubleDecimal separator: . (dot); thousand separator: none
    integerThousand separator: none
    textN/A

    AP_HOLDS_ALL

    Contains information about holds placed on invoices.

    FieldTypeStoresFiltering
    CREATED_BYTextIndicates the user who created the row.
    CREATION_DATEDatetimeIndicates the date and time of the creation of the row.
    HELD_BYTextUser that placed hold on invoice
    HOLD_DETAILSTextDetails of the hold that is placed on the invoice line.
    HOLD_LOOKUP_CODETextName of the hold that is applied on an invoice line.
    HOLD_REASONTextReason for hold being placed on invoice
    INVOICE_IDTextUnique invoice identifier that identifies the invoice against which a hold is placed. (Foreign key)
    LAST_UPDATE_DATEDatetimeIndicates the date and time of the last update of the row.
    LAST_UPDATED_BYTextIndicates the user who last updated the row.
    RELEASE_LOOKUP_CODETextRelease name that released the hold on the invoice.
    RELEASE_REASONTextReason 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.

    FieldTypeStoresFiltering
    BASE_CURRENCY_CODETextFunctional currency code associated with the ledger.
    ORG_IDTextIndicates the identifier of the business unit associated with the row. (Primary key)

    AP_TERMS_LINES

    Stores detail information about payment terms.

    FieldTypeStoresFiltering
    TERM_IDTextA unique identifier for the payment term.(Primary key)
    DUE_DAYSIntegerNumber of days after terms date, used to calculate due date of invoice payment line
    SEQUENCE_NUMIntegerNumber of the payment term line.(Primary key)
    DISCOUNT_PERCENTDoublePercentage used to calculate discount available for invoice payment line
    DISCOUNT_DAYSIntegerNumber of days after terms date, used to calculate discount date for invoice payment line
    DISCOUNT_DAYS_2IntegerNumber of days after terms date, used to calculate second discount available for invoice payment line
    DISCOUNT_PERCENT_2DoublePercentage used to calculate second discount available for invoice payment line

    AP_TERMS_TL

    Language-dependent descriptions for payment terms.

    FieldTypeStoresFiltering
    TERM_IDTextA unique identifier for the payment term.(Primary key)
    NAMETextName of the payment term that is associated with the invoice.
    DESCRIPTIONTextDescription of the payment term.
    LANGUAGETextIndicates 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.

    FieldTypeStoresFiltering
    CATEGORY_IDTextCategory an item is assigned to when during new item creation (Primary key)
    CATEGORY_NAMETextTranslatable name of the category
    LANGUAGETextIndicates 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.

    FieldTypeStoresFiltering
    LOOKUP_TYPETextLookup_type
    LOOKUP_CODETextLookup_code
    MEANINGTextMeaning

    FND_TERRITORIES_TL

    Language-dependent description for territories.

    FieldTypeStoresFiltering
    TERRITORY_CODETextTerritory_code (Primary key)
    LANGUAGETextIndicates the code of the language into which the contents of the translatable columns are translated (Primary key)
    TERRITORY_SHORT_NAMETextTerritory_short_name

    GL_DAILY_RATES

    Contains the daily conversion rates for foreign currency transactions.

    FieldTypeStoresFiltering
    FROM_CURRENCYTextCurrency that will be converted from (Primary key)
    TO_CURRENCYTextCurrency that will be converted to (Primary key)
    CONVERSION_DATEDatetimeCurrency conversion date of a daily rate (Primary key)
    CONVERSION_RATEDoubleCurrency conversion rate of a daily rate
    CREATION_DATEDatetimeIndicates the date and time of the creation of the row.

    HR_ORGANIZATION_UNIT_F_TL

    Language-dependent description for organization units.

    FieldTypeStoresFiltering
    ORGANIZATION_IDTextForeign Key
    LANGUAGETextIndicates the code of the language into which the contents of the translatable columns are translated.
    NAMETextDenotes the translated name for the Organization Unit.
    EFFECTIVE_START_DATEDatetimeDate Effective Entity: indicates the date at the beginning of the date range within which the row is effective.
    EFFECTIVE_END_DATEDatetimeDate 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.

    FieldTypeStoresFiltering
    PARTY_IDTextParty identifier (Primary key)
    PARTY_NAMETextName of this party

    IBY_PAYMENT_METHODS_TL

    Language-dependent description for Payment terms.

    FieldTypeStoresFiltering
    PAYMENT_METHOD_CODETextUser-entered primary key (Primary key)
    LANGUAGETextIndicates the code of the language into which the contents of the translatable columns are translated
    PAYMENT_METHOD_NAMETextPayment 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.

    FieldTypeStoresFiltering
    UOM_CODETextUnique short code assigned to a Unit of Measure
    UNIT_OF_MEASURE_IDTextUnique identifier of the unit of measure (Primary key)

    INV_UNITS_OF_MEASURE_TL

    Language-dependent description of units of measure.

    FieldTypeStoresFiltering
    UNIT_OF_MEASURE_IDTextUnique identifier of the unit of measure (Primary key)
    LANGUAGETextIndicates the code of the language into which the contents of the translatable columns are translated (Primary key)
    DESCRIPTIONTextTranslatable Unit of Measure description

    PER_LOCATION_DETAILS_F

    Stores the location-details-related attributes

    FieldTypeStoresFiltering
    LOCATION_IDTextForeign Key
    LOCATION_DETAILS_IDTextSystem generated primary key column (Primary key)
    EFFECTIVE_START_DATEDatetimeDate Effective Entity: indicates the date at the beginning of the date range within which the row is effective (Primary key)
    EFFECTIVE_END_DATEDatetimeDate 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.

    FieldTypeStoresFiltering
    LOCATION_DETAILS_IDTextSystem generated primary key column (Primary key)
    EFFECTIVE_START_DATEDatetimeDate Effective Entity: indicates the date at the beginning of the date range within which the row is effective (Primary key)
    EFFECTIVE_END_DATEDatetimeDate Effective Entity: indicates the date at the end of the date range within which the row is effective (Primary key)
    LANGUAGETextIndicates the code of the language into which the contents of the translatable columns are translated (Primary key)
    LOCATION_NAMETextConcatenation of Location Name & Alternate Location Code

    PER_USERS

    Stores one record per Oracle Cloud user.

    FieldTypeStoresFiltering
    USERNAMETextThe latest principal username of the user
    USER_IDTextMandatory Primary Key Updatable While New Key Generation (Primary key)
    PERSON_IDTextPerson id in HCM for this user (if available)

    PO_LINE_TYPES_TL

    Language-dependent description for Purchasing document line types.

    FieldTypeStoresFiltering
    LINE_TYPE_IDTextLine type unique identifier (Primary key)
    LANGUAGETextIndicates the code of the language into which the contents of the translatable columns are translated
    DESCRIPTIONTextDescription
    LINE_TYPETextDocument line type

    POZ_SUPPLIER_SITES_V

    Stores basic supplier sites information.

    FieldTypeStoresFiltering
    VENDOR_SITE_IDTextSupplier site unique identifier (Primary key)
    COUNTRYTextCountry code from the TERRITORY_CODE column in the FND_TERRITORY table
    PROVINCETextProvince
    STATETextState

    POZ_SUPPLIERS

    Stores supplier attributes.

    FieldTypeStoresFiltering
    VENDOR_IDTextSupplier unique identifier (Primary key)
    PARTY_IDTextParty identifier
    ONE_TIME_FLAGTextIndicates whether the supplier is a one-time supplier

    XLE_ENTITY_PROFILES

    Contains the general information for legal entities.

    FieldTypeStoresFiltering
    LEGAL_ENTITY_IDTextUnique identifier of the legal entity (Primary key)
    NAMETextName 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.

    FieldTypeStoresFiltering
    INVOICE_IDTextUnique identifier for invoice that is undergoing the approval cycle (Foreign key)
    RESPONSETextResponse of the approver on a given invoice
    APPROVER_IDTextLogin username of approver for given invoice
    CREATION_DATEDatetimeIndicates the date and time of the creation of the row
    HISTORY_TYPETextApproval type required on an invoice
    APPROVER_COMMENTSTextComments provided by the approver on a given invoice

    AP_INVOICE_LINES_ALL_INPUT

    Contains records for invoice lines.

    FieldTypeStoresFiltering
    INVOICE_IDTextInvoice line number associated with the event (Primary key and Foreign key)
    LINE_NUMBERTextNumber of the invoice line (Primary key)
    AMOUNTDoubleLine amount in invoice currency
    ORG_IDTextIndicates the identifier of the business unit associated to the row (Foreign key)
    CREATED_BYTextIndicates the user who created the row
    CREATION_DATEDatetimeIndicates the date and time of the creation of the row
    DESCRIPTIONTextDescription of the invoice line
    BASE_AMOUNTDoubleLine amount in functional currency
    PO_LINE_IDTextA 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_INVOICEDDoubleQuantity of items for matched invoice lines, price corrections, quantity corrections or unmatched invoice lines
    UNIT_MEAS_LOOKUP_CODETextUnit of measure for quantity_invoiced
    ITEM_DESCRIPTIONTextDescription of the item in the invoice line
    LINE_TYPE_LOOKUP_CODETextType of invoice line. Valid values from invoice line type lookup codes

    AP_INVOICE_PAYMENTS_ALL_INPUT

    Contains records of invoice payments for suppliers.

    FieldTypeStoresFiltering
    INVOICE_IDTextUnique invoice identifier that identifies the invoice to which the payment belongs
    INVOICE_PAYMENT_IDTextUnique identifier of the payment (Primary key)
    CREATION_DATEDatetimeIndicates the date and time of the creation of the row
    AMOUNTDoublePayment amount that is paid
    PAYMENT_NUMTextNumber identifying a payment
    CREATED_BYTextIndicates the user who created the row
    PAYMENT_CURRENCY_CODETextThe currency that the payment is made in
    REVERSAL_INV_PMT_IDTextIdentifier 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.

    FieldTypeStoresFiltering
    INVOICE_IDTextUnique invoice identifier (Primary key)
    INVOICE_NUMTextUnique number for supplier invoice
    INVOICE_AMOUNTDoubleInvoice amount in transaction currency
    INVOICE_TYPE_LOOKUP_CODETextInvoice category, such as standard, credit memo, or prepayment (Foreign key)
    TERMS_IDTextPayment terms identifier used on the invoice (Foreign key)
    CREATION_DATEDatetimeIndicates the date and time of the creation of the row
    GL_DATEDatetimeAccounting date to default to invoice distributions
    LEGAL_ENTITY_IDTextUnique identifier of the legal entity (Foreign key)
    CREATED_BYTextIndicates the user who created the row
    TERMS_DATEDatetimeDate used with payment terms to calculate scheduled payment of an invoice
    INVOICE_CURRENCY_CODETextCurrency code used on the invoice
    PAYMENT_METHOD_CODETextIndicates the payment method, such as check, cash, or credit (Foreign key)
    CANCELLED_DATEDatetimeDate when the invoice was canceled
    CANCELLED_BYTextUser ID of person who canceled an invoice
    CANCELLED_AMOUNTDoubleOriginal amount of canceled invoice

    AP_PAYMENT_SCHEDULES_ALL_INPUT

    Contains information about scheduled payments for an invoice.

    FieldTypeStoresFiltering
    CREATION_DATEDatetimeIndicated the date and time of the creation of the row
    PAYMENT_STATUS_FLAGTextFlag that indicates if payment has been made
    INVOICE_IDTextIdentifier of the invoice referenced in payment schedule (Primary key)
    PAYMENT_NUMTextNumber 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).

    FieldTypeStoresFiltering
    OBJECT_IDTextDocument header unique identifier (Primary key)
    OBJECT_TYPE_CODETextDocument type (Primary key)
    CREATION_DATEDatetimeIndicates the date and time of the creation of the row
    CREATED_BYTextIndicates the user who created the row
    ACTION_CODETextApproval or control action type (Primary key)
    ACTION_DATEDatetimeApproval or control action date
    ROLE_CODETextRole of the action performer
    PERFORMER_IDTextUnique identifier of the person taking the action
    NOTETextNote for next approver or reason for control action

    Contains header information for your purchasing documents.

    FieldTypeStoresFiltering
    PO_HEADER_IDTextDocument header unique identifier (Primary key and Foreign key)
    CREATION_DATEDatetimeIndicates the date and time of the creation of the row
    DOCUMENT_STATUSTextHeader status. Possible values are: OPEN, REJECTED, CANCELED, CLOSE, INCOMPLETE, WITHDRAWN.
    SOLDTO_LE_IDTextSold to Legal Entity unique identifier. Indicates the party which is financially responsible for the purchases on a purchase order (Foreign key)
    CREATED_BYTextIndicates the user who created the row
    VENDOR_IDTextSupplier unique identifier (Foreign key)
    SEGMENT1TextKey Flexfield: segment of the key flexfield
    PRC_BU_IDTextBusiness unit unique identifier of the purchase order to be created (Foreign key)
    TYPE_LOOKUP_CODETextType of the document. Possible values are: STANDARD, BLANKET, CONTRACT.
    CURRENCY_CODETextUnique identifier for the currency
    SHIP_TO_LOCATION_IDTextShip-to location unique identifier (Foreign key)
    VENDOR_SITE_IDTextSupplier site unique identifier (Foreign key)
    RATEDoubleCurrency conversion rate

    PO_LINE_LOCATIONS_ALL_INPUT

    Contains information about purchase order shipment schedules.

    FieldTypeStoresFiltering
    CREATION_DATEDatetimeIndicates the date and time of the creation of the row
    PO_LINE_IDTextDocument line unique identifier (Foreign key)
    PROMISED_DATEDatetimePromised Delivery Date.

    PO_LINES_ALL_INPUT

    Stores current information about each purchase order line.

    FieldTypeStoresFiltering
    PO_LINE_IDTextDocument line unique identifier (Primary key and Foreign key)
    PO_HEADER_IDTextDocument header unique identifier (Foreign key)
    LINE_NUMTextLine number
    CATEGORY_IDTextItem category unique identifier (Foreign key)
    ITEM_DESCRIPTIONTextItem description
    CREATION_DATEDatetimeIndicates the date and time of the creation of the row
    UNIT_PRICEDoubleUnit price for the line
    UOM_CODETextUnit of measure for the quantity ordered (Foreign key)
    CREATED_BYTextIndicates the user who created the row
    QUANTITYDoubleQuantity ordered on the line
    CLOSED_DATEDatetimeDate the line is closed
    CLOSED_BYTextUnique identifier of the employee who closed the line
    CANCELLED_BYTextUnique identifier of the employee who canceled the line
    CANCEL_REASONTextCancellation reason provided by employee
    CANCEL_DATEDatetimeCancellation date
    AMOUNTDoubleBudget Amount for temp labor standard Po_lines
    CLOSED_REASONTextDescribes why the line is closed

    Stores information about requisition headers.

    FieldTypeStoresFiltering
    REQUISITION_HEADER_IDTextRequisition header unique identifier (Primary key)
    REQUISITION_NUMBERTextRequisition number
    CREATION_DATEDatetimeIndicates the date and time of the creation of the row
    REQ_BU_IDTextBusiness unit unique identifier (Foreign key)

    POR_REQUISITION_LINES_ALL_INPUT

    Stores information about requisition items.

    FieldTypeStoresFiltering
    REQUISITION_LINE_IDTextRequisition line unique identifier (Primary key and Foreign key)
    CREATION_DATEDatetimeIndicates the date and time of the creation of the row
    ITEM_DESCRIPTIONTextItem description
    LINE_STATUSTextContains status of the requisition line
    CATEGORY_IDTextItem category unique identifier (Foreign key)
    LINE_TYPE_IDTextNumber identifier for the line type. Possible values are: 1, 6. (Foreign key)
    QUANTITYDoubleQuantity ordered
    REQUESTER_IDTextRequester unique identifier (Foreign key)
    LINE_NUMBERTextLine number
    REQUISITION_HEADER_IDTextRequisition header unique identifier (Foreign key)
    CURRENCY_CODETextUnique identifier for the currency
    UOM_CODETextCode for the unit of measure (Foreign key)
    PO_LINE_IDTextPurchase order line unique identifier (Foreign key)
    UNIT_PRICEDoubleUnit price in functional currency
    CURRENCY_UNIT_PRICEDoubleForeign currency unit price
    CREATED_BYTextIndicates the user who created the row
    AMOUNTDoubleAmount on the requisition line
    CURRENCY_AMOUNTDoubleForeign currency amount on the requisition line

    RCV_SHIPMENT_LINES_INPUT

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

    FieldTypeStoresFiltering
    SHIPMENT_LINE_IDTextThis column stores the receiving shipment receiving receipt line unique identifier. This is the primary key for the row (Primary key)
    PO_LINE_IDTextThis column stores the purchase order line unique identifier (Foreign key)
    CREATION_DATEDatetimeIndicates the date and time of the creation of the row
    QUANTITY_RECEIVEDDoubleThis column stores the net quantity received for the receiving shipment receipt line
    AMOUNT_RECEIVEDDoubleAmount shipped for a fixed price service receipt line in purchase order currency

    RCV_TRANSACTIONS_INPUT

    Stores historical information about receiving transactions.

    FieldTypeStoresFiltering
    CREATION_DATEDatetimeIndicates the date and time of the creation of the row
    CREATED_BYTextIndicates the user who created the row
    TRANSACTION_TYPETextReceiving transaction type.
    TRANSACTION_DATEDatetimeThis column stores the transaction date for the receiving transactions
    QUANTITYDoubleThis column stores the receiving transaction quantity stored in the receiving transaction UOM
    UOM_CODETextThis column stores the unit of measure code for the receiving transaction (Foreign key)
    SHIPMENT_LINE_IDTextThis column stores the receiving shipment receiving receipt header unique identifier (Foreign key)
    SOURCE_DOCUMENT_CODETextThis column stores the source document code for the receiving transactions (Foreign key)
    DESTINATION_TYPE_CODETextThis column stores the destination type code when the receiving transaction is created (Foreign key)
    CURRENCY_CODETextThis column stores the currency code for the receiving transaction
    USER_ENTERED_FLAGTextThis column indicates where the current receiving transaction row is user-entered or automatically generated
    AMOUNTDoubleTransaction Amount in purchase order currency

    Design specifications

    Entities

    EntityTransactional tablesMaster data tables
    Purchase requisitionsPor_requisition_lines_all, Por_requisition_headers_allEgp_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 ordersPo_headers_allXle_entity_profiles, Hr_organization_units_f_tl, Poz_suppliers, Hz_parties, Fnd_territories_tl, Poz_supplier_sites_v
    Purchase order itemsPo_lines_all, Por_requisition_lines_all, Rcv_shipment_lines, Po_line_locations_all, Po_headers_allEgp_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 receiptRcv_shipment_lines
    InvoiceAp_invoices_allXle_entity_profiles,, Ap_terms_lines, Iby_payment_methods_tl, Ap_terms_tl, Fnd_lookups
    Invoice itemAp_invoice_lines_all, Ap_invoices_allHr_organization_units_f_tl, Inv_units_of_measure_b, Inv_units_of_measure_tl, Ap_system_parameters_all, Gl_daily_rates
    Accounting documentsAp_invoices_all
    PaymentsAp_invoice_payments_all, Ap_payment_schedules_all

    Activities

    Activities can be created in the following ways:

  • Activities that can be created based on the transactional table information (i.e. Por_requisition_lines_all, Po_headers_all, Po_lines_all).
  • 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.

    EntityActivityActivity codeTransactional/ Historical tableMaster data table
    Purchase requisitionsCreate Purchase RequisitionCREATE_PRPor_requisition_lines_allSetup_users
    Purchase ordersCreate Purchase OrderCREATE_POPo_headers_allSetup_users
    Purchase order itemsCreate Purchase Order ItemCREATE_PO_ITEMPo_lines_allSetup_users
    Purchase order itemsCancel Purchase Order ItemnullPo_lines_allPer_users
    Purchase order itemsClose Purchase Order ItemnullPo_lines_allPer_users
    InvoicesCreate InvoiceCREATE_INVOICEAp_invoices_allSetup_users
    InvoicesCancel InvoicenullAp_invoices_allSetup_users
    Invoice itemsCreate Invoice ItemCREATE_INVOICE_ITEMAp_invoice_lines_allSetup_users
    PaymentsExecute Outgoing PaymentCREATE_OUTGOING_PAYMENTAp_invoice_payments_allSetup_users
    PaymentsVoid Outgoing PaymentnullAp_invoice_payments_allSetup_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:

    • the option 2 activities are already tracked by option 1 activities, thus generating duplicates (e.g., cancel lines)
    • the required activities must be at header level and the actual activities are happening at item level (e.g., PO_action_history table contains information that happens at item level but it is using header_id to identify them)
    • when finding out that some of those activities are irrelevant for the Purchase-to-Pay process

    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:

    • Goods_receipt_events
    • Invoice_approval_events
    • Invoice_hold_events
    • Invoice_hold_release_events
    • Invoice_item_hold_events
    • Invoices_item_hold_release_events
    • Purchase_order_events
    • Purchase_requisition_events

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

    Action_codeActivityDescription
    REQUISITION REASSIGNEDPurchase requisition reassignedPurchase Requisition is assigned to a new preparer
    OPENPurchase order openedPO document that has not been fully received or billed. Delivery of goods is not complete
    CORRECTGoods received-quantity correctedItems were corrected on receipt (for less or more)
    ORA_RESUBMITTEDInvoice approval resubmittedInvoice 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.

    EntityActivityActivity codeTransactional/ Historical tableMaster data table
    Purchase requisitionsSetup_activities."Activity name". If null, then Po_action_history."Action_code"Setup_activities."Activity code"Po_action_historyPor_requisition_lines_allPer_usersHz_partiesSetup_activities
    Purchase ordersSetup_activities."Activity name". If null then Po_action_history."Action_code"Setup_activities."Activity code"Po_action_historyPer_usersHz_partiesSetup_activities
    Goods receiptSetup_activities."Activity name"Setup_activities."Activity code"Rcv_transactionsSetup_activitiesUom_descriptionSetup_usersFnd_lookups
    InvoicesSetup_activities."Activity name". If null then Ap_inv_aprvl_hist_all.ResponseSetup_activities."Activity code"Ap_inv_aprvl_hist_allSetup_activities Setup_users
    InvoicesSetup_activities."Activity name". If null then Ap_holds_all_input.Hold_lookup_codeSetup_activities."Activity code"Ap_holds_all_inputSetup_activities, Setup_users
    InvoicesSetup_activities."Activity name". If null then Ap_holds_all_input.Release_lookup_codeSetup_activities."Activity code"Ap_holds_all_inputSetup_activities, Setup_users
    Invoice itemsSetup_activities."Activity name". If null then Ap_holds_all_input.Hold_lookup_codeSetup_activities."Activity code"Ap_holds_all_inputSetup_activities, Setup_users
    Invoice itemsSetup_activities."Activity name". If null then Ap_holds_all_input.Release_lookup_codeSetup_activities."Activity code"Ap_holds_all_inputSetup_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:

    • Spot: for currencies with fluctuating conversion rates, or when exact currency conversion is needed.
    • Corporate: for setting a standard rate across your organization for a stable currency.
    • User: for infrequent entries where daily rates for the entered foreign currency aren't set up. This currency rate is manually set when creating the document that requires that specific currency conversion.
    • Fixed: for rates where the conversion is constant between two currencies. Regardless of the type of currency conversion used, Oracle Cloud always stores one single record per day for each conversion type and the currencies involved (e.g. USD to EUR).

    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.

    Entities

    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:

    • Y
    • N
    • P (partial)

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

    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:

    • Transfers
    • Inventory (receipts done for stock items)
    • Purchase order (non-stock items)

    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 valueDestination description
    DROP SHIPDrop ship reception
    EXPENSENon-inventory items, they only impact expense accounts
    INVENTORYInventory items, they impact Inventory accounts
    MANUFACTURINGItems used for kits (BOM (bill of materials)) or manufacturing
    RECEIVINGInitial 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.

    Customizing the transformations

    Seed files

    Seed files can be used for variables that require a list of values. Seed files are available in the seeds folder of the app transformations. You can edit the seeds files when editing app transformations.

    The following seed files are available for Oracle Cloud Purchase-to-Pay:

    • setup_activities.csv
    • setup_users.csv

    setup_activities.csv

    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
    FieldTypeDescriptionExample
    Object_Type_Codevarchar(50)Code for the entity (Valid values: REQ, PO, GR, INV, INV_HOLD)REQ
    Action_Codevarchar(50)Code used in the aforementioned tables that represent each activity in Oracle CloudAPPROVE
    Activity_Namevarchar(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_Categoryvarchar(50)The type of activity that takes place (valid values Change, Set block, Remove block or NULL)NULL
    Activity_codevarchar(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 appAPPROVE_PR
    Activity_OrderIntegerThe number that defines in which order activities are executed in case they have the same Event end2
    Action_Code_Removal_Flagvarchar(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 logicN
    Automated_flagvarchar(1)Flag that defines automated activities. Valid values: Y, N or blank. If 'Y' then this activity will be considered automatedY
    Descriptionvarchar(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.csv

    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.

    FieldTypeDescriptionExample
    User_namevarchar(64)Username as stored in transactional tablesCRMOD_APPS_OSN_INTEG_APPID
    Automated_user_flagvarchar(1)Y or N(blank) value to identify automated usersY

    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

    VariableTypeDescription
    datetime_formatstring / integerSpecifies the date format which will be used when converting to DateTime type. SQL Server format value is 127. Snowflake format is 'YYYY-MM-DDThh24:mi:ss.ff3'.
    display_currencystringCurrency code used for all "value" attributes. All amounts (Values) will be converted to the selected display_currency.
    languagestringLanguage used to translate descriptions.
    start_datestring / integerThis date is used to replace values that are not a valid date for SQL Server(minimum date is 01-01-1753) and for Snowflake(minimum date is 01-01-1582).

    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:

    • Be mindful with the date or any filter to avoid getting duplicate records. This will be reflected in the test phase returning an error when a table has duplicate records.
    • The "REPORT" should be replaced with the report name for e.g AP_HOLDS_ALL_R_AP_HOLDS_ALL_R.csv and for the REPORT (1) would be AP_HOLDS_ALL_R_AP_HOLDS_ALL_R (1).csv. The file created should be changed to AP_HOLDS_ALL_R_AP_HOLDS_ALL_R_MERGED.CSV. Therefore it is needed to replace the original REPLICATE query for the report in CDATA Sync with:

    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.

    ncG1vNJzZmicn5jCrrHNrZitoZ%2Bjvaqv066pnqqVpbxvrsuomWebn6eyb8PIp5uor6Nju6bAjp%2BgpZ2jZJ2zu8KeqqyXfZ67qrrGaGlpamJjfnF7rquYnKSVYpCtu9SdZImtopi1or%2FEZquoZYCWxnCb0ZqapZ1deLmwwcNmh66qk52utLGMraZmiJGue6nAzKU%3D