Search 800 + Posts

Feb 8, 2024

How to find the reversal journal details of a journal in EBS

 When a journal is reversed, the following fields will be populated in the Original Journal related to the Reversal Journal: ACCRUAL_REV_FLAG, ACCRUAL_REV_STATUS, ACCRUAL_REV_JE_HEADER_ID, ACCRUAL_REV_PERIOD_NAME, ACCRUAL_REV_EFFECTIVE_DATE


Execute below query to identify the reversal journal. The below query will give you the reversed journal header name, header id and period.

SELECT
    glhr.je_batch_id    "Reversal JE Batch ID",
    glhr.name           "Reversal JE Name",
    glhr.je_header_id   "Reversal JE Header ID",
    glhr.period_name    "Reversal JE Period Name"
FROM
    gl_je_headers   glho,
    gl_je_headers   glhr
WHERE
    glho.accrual_rev_je_header_id = glhr.je_header_id
    AND glho.accrual_rev_je_header_id IS NOT NULL
    AND glho.je_header_id = &je_header_id;

Feb 5, 2024

Bulk loading data into Oracle Autonomous Data Warehouse (ADW) using Oracle Integration Cloud (OIC).

This document explores bulk loading data into Oracle Autonomous Data Warehouse (ADW) using Oracle Integration Cloud (OIC). It covers the benefits, steps involved, configuration details, and best practices for a successful implementation.

For technical details please refer my other post at Bulk Load ADW Feature with OIC

Benefits of Bulk Loading with OIC:

  • Performance: Bulk loading significantly improves data loading speed compared to traditional methods like SQL*Loader , Loading data using Read/Write file in OIC or Insert Feature of ADW Database adapter.
  • Security: Utilize OIC's built-in security features and access control for secure data transfer.
  • Automation: Set up scheduled integrations for regular data updates without manual intervention.
  • Scalability: Handle large datasets efficiently with OIC's cloud-based scalability.

Configuration Involved:

  1. Ensure your data is in a supported format (CSV, JSON, etc.).
  2. Structure the data according to your target ADW table schema.
  3. Create OIC Integration , App Driven Orchestration (ADO) or for event-triggered loads or Schedule Based Orchestration (SBO) for regular schedules.
  4. Configure Adapter:
    1. Specify the ADW connection details and target table.
    2. Select the "Invoke Bulk Load from Object Storage" operation.
    3. Provide the Object Storage bucket and file information.
    4. Optionally, configure file format settings.
    5. Configure Table name that you would like to load and column names that you would like to populate.
  5. Test and Deploy:
    • Test the integration thoroughly with test data.
    • Deploy the integration and monitor its performance.

Process Flow :

  1. Once OIC flow triggered it will download data file(s) from SFTP server
  2. Load data file into temp memory
  3. Call ADW adapter , and since we have configure ADW adapter with Operation : "Perform Bulk Data Import Operation" , it will load data file into OCI bucket and then load data into ADW Table



Feb 3, 2024

Oracle ADW Bulk Load with OIC to Improve Data Load Performance

The Oracle ADW Bulk Load feature typically refers to the ability to efficiently load large volumes of data into an Oracle ADW instance using specialized techniques to improve performance.

My Test Case - 
Load 3 files , Each file has 15 Columns  and 300K records. Total Time taken is 1 minutes 30 sec to load 900K records

In this blog I will go over steps , how we setup an OIC orchestration that will invoke ADW Adapter with operation as Bulk Load.

First let's talk about some important features about  ADW Bulk Load

High-Volume Data Loading: Oracle ADW Bulk Load is designed for scenarios where you need to load a large volume of data into your Autonomous Data Warehouse instance efficiently.

Performance Optimization: Bulk loading techniques are employed to optimize the data loading process. This includes methods such as direct path loading, parallel processing, and minimizing logging for faster inserts.

Minimized Resource Utilization: The bulk load feature aims to minimize resource utilization and maximize throughput during the data loading process.

Parallel Loading: Bulk loading often involves parallel loading, where multiple threads or processes are used to load data concurrently, speeding up the overall loading time.

Integration with OIC  & ADW Adapter:

In the context of Oracle Integration Cloud, OIC ADW Adapter provides a set of pre-built, reusable connectors and components for interacting with Oracle ADW. The adapter is used within OIC integrations to facilitate seamless communication between different systems.

Feel free to reach us at inquiry@bizinsightinc.com for any additional information on this Integration

Prerequisites for creating in OIC flow with ADW Adapter for Bulk Load:

  1. An Oracle Integration Cloud instance
  2. An Oracle Autonomous Transaction Processing (ADW) database
  3. A configured Oracle ATP Adapter connection within Oracle Integration Cloud
  4. A file containing the data to be loaded, in a supported format (CSV, XML, or JSON)
  5. An Oracle Object Storage bucket to temporarily store the file

Steps:

Create an Integration:

  1. Access the Oracle Integration Cloud console.
  2. Create a new integration.
  3. Choose a trigger based on your needs (e.g., scheduled trigger for regular imports, or an HTTP trigger for on-demand imports).

Add Oracle ATP Adapter:

  1. Drag and drop the Oracle ADW Adapter onto the integration canvas.
  2. Select the "Perform Bulk Data Import Operations" operation.

Configure Source File:

  1. Specify the Object Storage bucket and file path where the data is located.
  2. If needed, use a mapper to transform the data format.

Map Fields:

  1. Map the fields in the source file to the corresponding columns in the ADW database table.
  2. Ensure data types align correctly.

Specify Target Table:

  1. Provide the name of the ADW database table where you want to load the data.

Configure Security (if needed):

  1. While Configuring ADW Adapter connection in OIC , use JDBC With OCI Signature security policy, provide required details like compartment OCID and wallet password.

Deploy and Test:

  1. Save and deploy the integration.
  2. Test it to ensure successful data loading into the ATP database.
Setup ADW Connection 


For Additional Details on how to setup Private keys  refer https://blogs.oracle.com/integration/post/oci-signature-in-rest-adapter-for-oci-rest-apis

Once Connection is setup , login to OCI and execute below steps
 
1. Setup Compartment and Bucket in OIC
Note - for my testing I have created my Bucket in root compartment
Setup OCI Compartment Policy

Note -
I have created bucket Under Root compartment /Tenancy , that is why you don't see any Compartment name in below Screen shot ( Rather it says "in Tenancy") , but if you create bucket in a Compartment , then please define same policy for your Compartment

OIC Orchestration - 
 
1.Create Scheduled based Orchestration
 
2.Add FTP connection to download files from SFTP server - This is the server where you will place the file
 
3.Add ADW DB adapter and configure that for Bulk Load - Here you will setup OICI Bucket /Table Name in ADW as well as table structure
One execution , OIC will move the File to OIC Bucket and then insert data into ADW Folder . While configuring BULK Load for ADW adapter you have option to delete rthe file from OCI bucket once data from file is loaded into ADW table ( for my testing I have Unckeck that and that is why you see the files remain in OCI bucket once OIC job completed)

Configure ADW Adapter
Setup OIC Bucket  ( I have uncheck the check box "Delete file")
 
Setup the columns that you would like to load from File in OCI bucket , I have selected all the Columns
 
OCI Flow


Jan 28, 2024

Supplier Conversion from Oracle EBS or any Other On-Premise/Cloud system to Oracle Fusion with FBDI/OIC

Converting supplier data from Oracle E-Business Suite (EBS) or any Other On-Premise/Cloud system to Oracle Fusion Procurement Cloud involves careful planning and execution. Below are high levels design process that we usually implement and execute for Conversion. This is a generic layout of conversion process , you should always consider specific requirements and configurations of your organization while designing conversion process.



Feel free to reach us at inquiry@bizinsightinc.com for any additional information on this Integration

Step#1 - Preparation

  1. Analyze data: Analyze your EBS supplier data to identify records for conversion. we usually focus on active suppliers with accurate and complete information. Consider excluding inactive or incomplete records to avoid data issues in Fusion.

  2. Map data fields: Create field mapping between EBS and Fusion. Ensure field names and data formats align to avoid mapping errors during import. 

  3. Data cleansing: Cleanse your EBS supplier data. Address inconsistencies, duplicates, and missing values ( this is a golden opportunity to get rid of duplicate data as well as supplier consolidation before we start using Oracle Fusion). Oracle has tools like Data Quality to ensure data integrity.

  4. Choose conversion method: Select the appropriate conversion method based on your volume and complexity of data , and then work with Business/IT to load the data as per required Fusion templates. We usually use below 2 options :

    • File-based Data Import (FBDI): Suitable for moderate-sized datasets requiring manual file generation and upload.

    • Oracle Integration Cloud (OIC): Handles complex integrations and high-volume data transfers, majority of time we create OIC orchestration flow to do complex validation and mapping and then call FBDI jobs to load data in Oracle Fusion

Step#2 -Conversion

  1. Extract data: Extract the supplier data from EBS or any source system based on your requirement ( for example you might be interested in converting Active Supplier). While extracting the data we usually take care of couple of things 

    1. Make sure that you have all the elements that are required for FBDI template for Supplier.

    2. If some of required data elements are not available in source system/EBS then make note of that and we might beed to add those 

      1. either manually in FBDI template 

      2. Or if we are planning to use integration tool like OIC then we derived these missing values in OIC

  2. Transform data (optional): Depending on your data mapping setup, perform any necessary data transformations during extraction or as a separate step ( Manual updates in FBDI or Transformation in OIC flow) .

    At the end of this Step you have data ready for Supplier /Site/Address/Site Assignment/Contact as per respective FBDI Template ( if you are planning to load manually ) or But if we are calling the Load jobs from OIC then OIC will create FBDI file and call the Load ESS Jobs.

  3. Load data: 

    1. for manual Import ,Run the FBDI jobs for 

      1. Import Supplier 

      2. Import Addresses

      3. Import Site

      4. Import Site Assignments

      5. Import Contacts

      6. Import Supplier Business classification

      7. Import Supplier Attachment ( If any)

    2. For loading data with FBDI +OIC then execute following Steps

      1. Set up SaaS + File datapters+DB connection in OIC.

      2. Create OIC orchestration for 3.1.1/3.1.2/3.1.3/3.1.4/3.1.5/3.1.6/3.1.7.Most of time we create Scheduled Orchestration for Supplier conversions and Run one OIC job at a time , in above sequence. Also Mark the errors records. 

      3. One of advantage of doing OIC jobs is , in case there is requirements to load supplier on regular basis from external system  then we can convert these Scheduled orchestration into App based orchestration and use same integrations for real time Supplier load.

      4. I am working on a separate blog with Technical details especially for OIC flow. 

  4. Validate data: Validate the imported data for accuracy and completeness. Leverage Fusion's validation tools and review error logs to identify and address any inconsistencies.

Step#3 - Post-conversion:

  1. Reconciliation: Reconcile the migrated data with your original EBS supplier records. Ensure all critical information is transferred correctly and reflects the latest updates.

  2. Testing: Test supplier functionalities in Fusion, verify supplier profiles, address creation, purchase order workflows, and other key activities.