DEV Community: Naveen

Using PL/SQL in Oracle Fusion BI Publisher Reports for Dynamic Data Filtering

Naveen — Sun, 19 Jul 2026 07:14:19 +0000

Oracle Fusion BI Publisher supports PL/SQL Data Sets, allowing report developers to implement business logic directly within a report.

This approach is useful when report data needs to be dynamically filtered or transformed based on user input.

Requirement

In this example, users can select a Supplier Type parameter from the following values:

SUPPLIER
PUBLIC SECTOR COMPANIES
INSURANCE
INTERCOMPANY

Based on the selected value, the report retrieves matching supplier records from the Fusion Procurement supplier table (POZ_SUPPLIERS_V)

Click to view the full PL/SQL Data Set code

DECLARE

    ----------------------------------------------------------------------------
    -- Define a REF CURSOR type.
    -- BI Publisher uses a cursor to return the final result set back to
    -- the report output.
    ----------------------------------------------------------------------------
    TYPE refcursor IS REF CURSOR;

    ----------------------------------------------------------------------------
    -- Cursor variable that will hold the query result.
    -- BI Publisher expects the data to be returned through xdo_cursor.
    ----------------------------------------------------------------------------
    xdo_cursor REFCURSOR;

    ----------------------------------------------------------------------------
    -- Variable used to store the supplier type returned by the function.
    ----------------------------------------------------------------------------
    l_supplier_filter VARCHAR2(100);

    ----------------------------------------------------------------------------
    -- Local PL/SQL Function: Supplier_type
    --
    -- Purpose:
    --   Accepts the report parameter P_VENDOR_TYPE and validates the value.
    --
    -- Allowed Values:
    --   SUPPLIER
    --   PUBLIC SECTOR COMPANIES
    --   INSURANCE
    --   INTERCOMPANY
    --
    -- Returns:
    --   The corresponding supplier type value.
    --   If no valid value is found, returns 'NA'.
    ----------------------------------------------------------------------------
    FUNCTION Supplier_type (
        p_in_vendor_type IN VARCHAR2
    )
    RETURN VARCHAR2
    IS
        ----------------------------------------------------------------------------
        -- Variable to hold the function result.
        ----------------------------------------------------------------------------
        l_supplier_type_rtn VARCHAR2(100);

    BEGIN

        ----------------------------------------------------------------------------
        -- Check if the selected supplier type is SUPPLIER.
        ----------------------------------------------------------------------------
        IF p_in_vendor_type = 'SUPPLIER' THEN

            l_supplier_type_rtn := 'SUPPLIER';

        ----------------------------------------------------------------------------
        -- Check if the selected supplier type is PUBLIC SECTOR COMPANIES.
        ----------------------------------------------------------------------------
        ELSIF p_in_vendor_type = 'PUBLIC SECTOR COMPANIES' THEN

            l_supplier_type_rtn := 'PUBLIC SECTOR COMPANIES';

        ----------------------------------------------------------------------------
        -- Check if the selected supplier type is INSURANCE.
        ----------------------------------------------------------------------------
        ELSIF p_in_vendor_type = 'INSURANCE' THEN

            l_supplier_type_rtn := 'INSURANCE';

        ----------------------------------------------------------------------------
        -- Check if the selected supplier type is INTERCOMPANY.
        ----------------------------------------------------------------------------
        ELSIF p_in_vendor_type = 'INTERCOMPANY' THEN

            l_supplier_type_rtn := 'INTERCOMPANY';

        ----------------------------------------------------------------------------
        -- If none of the above values match, return NA.
        ----------------------------------------------------------------------------
        ELSE

            l_supplier_type_rtn := 'NA';

        END IF;

        ----------------------------------------------------------------------------
        -- Return the validated supplier type value.
        ----------------------------------------------------------------------------
        RETURN l_supplier_type_rtn;

    EXCEPTION

        ----------------------------------------------------------------------------
        -- Handle any unexpected errors and return NA.
        ----------------------------------------------------------------------------
        WHEN OTHERS THEN

            RETURN 'NA';

    END Supplier_type;

BEGIN

    ----------------------------------------------------------------------------
    -- Pass the BI Publisher report parameter P_VENDOR_TYPE
    -- into the Supplier_type function.
    --
    -- Example Input:
    --   SUPPLIER
    --   INSURANCE
    --   PUBLIC SECTOR COMPANIES
    --   INTERCOMPANY
    --
    -- Example Output:
    --   SUPPLIER
    --   INSURANCE
    --   PUBLIC SECTOR COMPANIES
    --   INTERCOMPANY
    ----------------------------------------------------------------------------
    l_supplier_filter := Supplier_type(:P_VENDOR_TYPE);

    ----------------------------------------------------------------------------
    -- Open the cursor and return the result set to BI Publisher.
    ----------------------------------------------------------------------------
    OPEN :xdo_cursor FOR

        ----------------------------------------------------------------------------
        -- Retrieve supplier information from Oracle Fusion Procurement
        -- Supplier View (POZ_SUPPLIERS_V).
        ----------------------------------------------------------------------------
        SELECT
               ----------------------------------------------------------------------------
               -- Supplier Name
               ----------------------------------------------------------------------------
               vendor_name,

               ----------------------------------------------------------------------------
               -- Supplier Type
               ----------------------------------------------------------------------------
               vendor_type_lookup_code,

               ----------------------------------------------------------------------------
               -- Organization Type
               ----------------------------------------------------------------------------
               organization_type_lookup_code,

               ----------------------------------------------------------------------------
               -- Business Relationship
               ----------------------------------------------------------------------------
               business_relationship,

               ----------------------------------------------------------------------------
               -- Display the selected parameter value in the report output.
               ----------------------------------------------------------------------------
               l_supplier_filter supplier_type

        FROM   poz_suppliers_v

        ----------------------------------------------------------------------------
        -- Filter suppliers based on the selected Vendor Type parameter.
        --
        -- Example:
        -- If P_VENDOR_TYPE = 'INSURANCE'
        -- Then the query becomes:
        --
        -- WHERE vendor_type_lookup_code = 'INSURANCE'
        ----------------------------------------------------------------------------
        WHERE  vendor_type_lookup_code = l_supplier_filter;

END;
/

Create Data model in Fusion

As below

Preview the data by passing parameters value.

Save sample data & move to create a report

Conclusion

This PL/SQL-based BI Publisher Data Set demonstrates how Oracle Fusion report developers can combine report parameters, custom functions, and cursor-based queries to create dynamic reports.

By processing the user-selected supplier type through a PL/SQL function and applying it to the query filter, the report becomes more flexible, maintainable, and aligned with business requirements.

This technique is especially useful in Procurement reporting scenarios where supplier classifications drive reporting outcomes.

Real-Time Reporting: How to Push ATP Data to Oracle Fusion BI Publisher

Naveen — Sun, 19 Jul 2026 02:51:45 +0000

Most Oracle Fusion implementations follow a standard pattern: transferring data from Fusion Applications into Oracle Autonomous Transaction Processing (ATP) for analytics

But what happens when you need to go the other way ?

In this project, I designed a solution for a common business pain point: fetching processed data from ATP, generating a dynamic BI Publisher report, and distributing it via Oracle Integration Cloud (OIC) notifications.

The Business Challenge

Organizations store massive volumes of processed data in ATP—everything from billing records to custom integration staging tables. While ATP is great for storage, business users need:

Professional, formatted report templates.
Automated, scheduled delivery.
Rich HTML email notifications.

Our solution automates this entire flow, removing manual effort and custom scripting.

Solution Architecture

The flow moves from data storage to user delivery in a seamless loop:

Technologies Used
Oracle Technology Purpose Oracle ATP Source Data Repository OIC Orchestration Layer Fusion BI Publisher Report Design & Execution SOAP Services Remote Report Execution OCI Hosting Platform

Implementation Steps

Step 1: Create Scheduled Integration in OIC

We start with a Scheduled Integration. This removes human dependency and allows for daily, weekly, or custom business intervals.

Step 2: Connect to ATP and Execute SQL

Using the ATP Adapter, we establish a secure connection to fetch the "Active" records.

SELECT 
    * from emp

Step 2.1: ATP Data to Stage File in OIC

Here we used stage file action in OIC to write the ATP data into a file, which further we mapped to SOAP services as content.

Click to see Loaded sample xml file in the stage file

<?xml version="1.0" encoding="UTF-8" ?><root>
   <row>
      <EMPNO>7839</EMPNO>
      <ENAME>KING</ENAME>
      <JOB>PRESIDENT</JOB>
      <MGR/>
      <HIREDATE>1981-11-17T00:00:00.000+00:00</HIREDATE>
      <SAL>5000</SAL>
      <COMM/>
      <DEPTNO>10</DEPTNO>
   </row>
   <row>
      <EMPNO>7698</EMPNO>
      <ENAME>BLAKE</ENAME>
      <JOB>MANAGER</JOB>
      <MGR>7839</MGR>
      <HIREDATE>1981-05-01T00:00:00.000+00:00</HIREDATE>
      <SAL>2850</SAL>
      <COMM/>
      <DEPTNO>30</DEPTNO>
   </row>
</root>

Here the mapping reference of stage writes from ATP

Step 3: Build Fusion BI Publisher Data Model

The incoming ATP data is structured into an XML format. This serves as the data source for our report.

Click to see Magic in the Fusion

Create Data model with the XML FILE

Loaded the same sample XML File which we used earlier

Preview Data & save as Sample Data

Don't forgot save it in folders.

Create report based on above Data model

Keep the report path with you

Step 4: Execute Fusion Run Report SOAP Service

This is the core of the integration. OIC invokes the runReport()
operation via the BI Publisher SOAP service.

Below is the mapper reference!!

Key Insight: The SOAP service is highly flexible. It accepts the XML data, applies your RTF/Excel template, and generates the output in your choice of format (PDF, Excel, CSV).

Click to see How reportBytes converts to DecodeBase64

Using assignment, I converted to DecodeBase64

Step 5: Send OIC HTML Notification

Finally, we use the OIC Notification Action to deliver the report.

Click to see Sample Email Template:

Sample Email Template:

<html>
<body>
<p>Hi Nj</p>
<p>Generate excel file using BIP report<br>
DB data - XML Data Model fusion - report xlsx</p>

<p>{bfy}</p>

<p>Best Regards,<br>Naveen Jejji<br>OIC Dev</p>


<div class="logo">
<img src ="https://osidigital.com/wp-content/uploads/2018/08/osi_logo_rgb.png" alt="company logo"
width ="250">
</div>
</body>
</html>

Key Innovation: Flipping the Script

The Paradigm Shift

Traditional: Fusion → ATP (Data Archiving)
Our Approach: ATP → Fusion BI Publisher → OIC (Dynamic Reporting)

This leverages ATP as the "Source of Truth" while utilizing Fusion’s powerful formatting engine.

Measurable Outcomes

Metric Improvement Manual Effort Reduced by 90% Delivery Time Hours to Minutes Automation 100% Automated

Conclusion

This project demonstrates that the Oracle ecosystem is most powerful when its components work in a "reverse" architecture.

By moving data from ATP back into the Fusion reporting engine, we provide users with the professional outputs they need without manual intervention.

I’d love to hear your thoughts:

Have you implemented a similar "reverse" integration? What challenges did you face with the SOAP service limits? Let's discuss in the comments!

Automating Customer Termination Lifecycle Using Oracle Integration Cloud, ATP, and Oracle Service Cloud

Naveen — Sat, 04 Jul 2026 16:29:34 +0000

Introduction

I built an automated customer termination framework using Oracle Fusion Cloud, OIC, ATP, and Oracle Service Cloud.

It synchronizes termination data across systems, eliminating manual delays, billing errors and operational overhead while ensuring consistent customer lifecycle management.

Solution Architecture

The automated lifecycle flow

Integration Flow

Step 1
Customer termination information is identified in Oracle Fusion.

 Fusion → BIP report with “last run” parameter → OIC → ATP → Service Cloud.

Fusion → BIP report with “last run” parameter → OIC

A Quick Business Validation
The integration invokes Oracle Service Cloud APIs.
I added to check whether customer is available in RightNow or Not using RESTAPI

SerachCustomers in RightNow

Step 2
Based on above response, we used switch in OIC -To handle creation & update of customer in RightNow(Oracle Service Cloud (RightNow) invoke):

Oracle Service Cloud (RightNow) invoke

Business Value Delivered

The solution successfully delivered:

Automated customer termination processing
Improved synchronization between Oracle Fusion and Oracle Service Cloud
Reduced billing risk
Strong audit capabilities
Better support visibility
Enterprise-grade scalability

Conclusion
By leveraging Oracle Integration Cloud, ATP, and Oracle Service Cloud, we successfully automated the customer termination lifecycle and eliminated several manual operational challenges.

The solution demonstrates how Oracle's cloud platform can be used to build reliable, scalable, and business-critical integrations that improve efficiency while maintaining data integrity across enterprise applications.

Stop Using Q! Use Finder for Faster Oracle OIC REST Integrations

Naveen — Sun, 14 Jun 2026 16:36:19 +0000

Learn how to use the Finder parameter in Oracle Fusion REST APIs instead of Q for better performance, cleaner OIC mappings, and optimized integrations.

While Finder works instantly in Postman, OIC requires it to be constructed as a single parameter using XPath expressions, that’s where most developers struggle

In OIC, integrating with Oracle Fusion Various REST APIs is a common requirement for extracting and processing business data. One of the key decisions developers face is how to filter data efficiently using query parameters.

Most developers are familiar with the q parameter, but Oracle also provides a powerful alternative the finder option.

What is finder in Oracle Fusion REST APIs?
The finder parameter is a predefined query mechanism provided by Oracle Fusion REST APIs to retrieve data using named queries and bind parameters

Eg:

...?finder=findPurchaseOrderSchedules;
bindPONumber=AE01-P001531;
bindPOItemNumber=664-8455

It behaves like parameterized SQL queries (bind variables), improving performance and readability.

What is q Parameter?
The q parameter is used to apply dynamic filters similar to a SQL WHERE clause
Eg:

...?q=PONumber='AE01-P001531' and POItemNumber='664-8455'

Flexible but less structured
Can lead to complex expressions

let's Walk into OIC......!

Make sure you have required REST connections.
Create Application based Integration with POST method.

Note:The below fields are we need to pass for another REST API.

Call Get Method with finder using rest call. Below reference, which we will call

Add Finder as review parameter for the endpoint

Now we will map to finder respectively as below.

End flow as below

TESTING

STRUGGLING WITH IRREGULAR CSV FILES IN OIC? HERE’S THE NXSD SOLUTION

Naveen — Wed, 10 Jun 2026 17:14:01 +0000

Handling non-uniform CSV/TXT files in Oracle Integration Cloud (OIC) is a common real-time integration challenge. These files often contain inconsistent structures like headers, mixed record types, or variable columns. This blog explains how to process such files using NXSD (Native Format Schema Definition) and trigger notifications effectively.

UNDERSTANDING THE CHALLENGE

Below is csv/txt file, which is Variable number of columns

Code,Message,Date
F,Hello,nj@gmail.com,957351,01-06-2026
S,Hey,ab@gmail.com,45466,02-06-2026
P,Bye,ck@gmail.com,87979,03-06-2026

This kind of files we need to process in OIC with most effectively.

STEP 1: CREATE FLEXIBLE NXSD SCHEMA
NXSD enables parsing flat files into structured XML.

Note: Observe & understand the below script with respective the challenge mentioned.

<?xml version="1.0" encoding="UTF-8" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            xmlns:nxsd="http://xmlns.oracle.com/pcbpel/nxsd"
            xmlns:tns="http://TargetNamespace.com/fileReference/read"
            targetNamespace="http://TargetNamespace.com/fileReference/read"
            elementFormDefault="qualified"
            attributeFormDefault="unqualified"
            nxsd:version="NXSD"
            nxsd:stream="chars"
            nxsd:encoding="UTF8">

    <xsd:element name="Root-Element">
        <xsd:complexType>
            <xsd:sequence>

                <xsd:element name="headerset">
                    <xsd:complexType>
                        <xsd:sequence>
                            <xsd:element ref="tns:header"/>
                        </xsd:sequence>
                    </xsd:complexType>
                </xsd:element>

                <xsd:element name="lineset">
                    <xsd:complexType>
                        <xsd:sequence>
                            <xsd:element ref="tns:line" minOccurs="1" maxOccurs="unbounded"/>
                        </xsd:sequence>
                    </xsd:complexType>
                </xsd:element>

            </xsd:sequence>
        </xsd:complexType>
    </xsd:element>
    <xsd:element name="header" type="tns:headerType"/>
    <xsd:element name="line" type="tns:lineType"/>
    <xsd:complexType name="headerType">
        <xsd:sequence>
            <xsd:element name="C1" type="xsd:string"
                         nxsd:style="terminated"
                         nxsd:terminatedBy=","
                         nxsd:quotedBy="&quot;"/>
            <xsd:element name="C2" type="xsd:string"
                         nxsd:style="terminated"
                         nxsd:terminatedBy=","
                         nxsd:quotedBy="&quot;"/>
            <xsd:element name="C3" type="xsd:string"
                         nxsd:style="terminated"
                         nxsd:terminatedBy="${eol}"
                         nxsd:quotedBy="&quot;"/>
        </xsd:sequence>
    </xsd:complexType>
    <xsd:complexType name="lineType">
     <xsd:sequence>
            <xsd:element name="C1" type="xsd:string"
                         nxsd:style="terminated"
                         nxsd:terminatedBy=","
                         nxsd:quotedBy="&quot;"/>
            <xsd:element name="C2" type="xsd:string"
                         nxsd:style="terminated"
                         nxsd:terminatedBy=","
                         nxsd:quotedBy="&quot;"/>
            <xsd:element name="C3" type="xsd:string"
                         nxsd:style="terminated"
                         nxsd:terminatedBy=","
                         nxsd:quotedBy="&quot;"/>
            <xsd:element name="C4" type="xsd:string"
                         nxsd:style="terminated"
                         nxsd:terminatedBy=","
                         nxsd:quotedBy="&quot;"/>
            <xsd:element name="C5" type="xsd:string"
                         nxsd:style="terminated"
                         nxsd:terminatedBy="${eol}"
                         nxsd:quotedBy="&quot;"/>               
        </xsd:sequence>
    </xsd:complexType>
</xsd:schema>

Save the above script as XX_FILENAME.xsd

STEP 2: BUILD OIC INTEGRATION

Create Application Based Integration which accepts the request body as Binay Format.

Read file using stage action as below

Remember to upload your XSD file here as below in the stage read file operation

STEP 3: Write the file for Notification

Using stage action with Write Operation , write a file which has been read in the early step
make sure upload the same XSD file for sample data.

STEP 4: SEND NOTIFICATIONS

Hope we are familiar with OIC notification as below map data from write operation.

Finally, we read & write the non-uniform csv file in OIC successfully.

Note: ORACLE DOC reference for XSD Script

Calling OIC Integration from Oracle Fusion via HTTPS

Naveen — Mon, 08 Jun 2026 02:37:20 +0000

Oracle Fusion Applications often require integration with external or internal services to extend business capabilities. Oracle Integration Cloud (OIC) serves as a powerful middleware platform to orchestrate such integrations.

One of the most common patterns is invoking an OIC integration from Fusion using an HTTPS connection, ensuring secure and reliable communication.

In this blog, we'll walk through the end-to-end process, including setup, configuration, and best practices

Step 1: Create an OIC Integration

Login to Oracle Integration Cloud
Navigate to Integrations → Create
Choose:REST Adapter (recommended for HTTPS calls)

Configure:

 * Resource URI (e.g., /customer/update)
 * HTTP Method (POST/GET)
 * Request/Response payload (JSON/XML)

Reference

Request Payload:

Make sure you selected
What is the media-type of Request Body? (Content-Type Header)
URL-encoded for https call acceptance

Response Payload:
I drop the xml reference here for response payload

<?xml version="1.0" encoding="UTF-8"?>
<xm:schema xmlns:xm="http://www.w3.org/2001/XMLSchema">
<!-- Root element-->
<xm:element name="root" type="root"/>
<!-- Complex type-->
    <xm:complexType name="root">
      <xm:sequence>
        <xm:element name="outcome" type="xm:float"/>
        <xm:element name="Instance_ID" type="xm:string"/>
              </xm:sequence>
            </xm:complexType>
</xm:schema>

Step 2: Create an JavaScript function using libraries

Navigations

Under Design -->libraries-->upload your js function

Step 3:Call Js function in integration

call Js function in your designing & map respective inputs to your function

Step 4: Map the response of integration

As below maps the final response.

Map the primary business identifiers
Active the Integration
Try to run & collect your endpoint Metadata

Step 5: Fusion BIP https connection configuration
Now we will map the integration connection details in fusion .

So for that we will login fusion/analytics-->Under profile Icon right side [Administration]-->Publisher[Manage Publisher]

Under HTTP creation data source with OIC login details

Step 6: Fusion BIP Data Model creation

Now choose the Data Source as HTTPS connection

fill respective details as below

Step 7: Testing

Run the data model with passing parameters , it will hit the integration as Input which we earlier given as request payload.
The response will display as below, which is back from integration.

Now we will see the Integration track Instance

By following the steps above setting up OIC, configuring HTTPS calls, handling authentication, and securing communication you can build reliable and scalable integrations.

Change VBCS Default Oracle Background to a Custom Image

Naveen — Sat, 06 Jun 2026 16:01:02 +0000

Oracle Visual Builder Cloud Service (VBCS) provides a clean default UI, but often, applications require branding or a more personalized look and feel. One simple yet effective customization is replacing the default background with a custom image.

In this blog, we'll walk through how to change the default Oracle VBCS background to a custom image in a few easy steps.

Step 1: Upload Your Custom Image

By default, applications have UI like below

Let's upload your custom image in VBCS application

Using Control & Navigation will upload image

Now we will upload image in data as below

Theme also choose here

Run Preview of your application page & Inspect image for entire VBCS Url

Step 2: Add Custom CSS

Make sure you will remove the image component from the page after you got the Url

Step 3: Final Preview

Note: Use optimized images (for faster loading)

Agentless Polling in Oracle Integration Using Public Gateway and ATP

Naveen — Fri, 05 Jun 2026 15:13:41 +0000

Enabling Secure Inbound Polling Without Connectivity Agent

In Oracle Integration (OIC), polling-based integrations are a common way to process database-driven events. This blog walks through a simple and practical polling use case using the classic EMP table, enhanced with a custom column to control processing.

Use Case Overview
We want to:

Add a column to track whether a record needs processing
Use OIC to poll records from EMP table
Process only specific rows
Update status after processing

Step 1: Create ATP Connection
The Below is Reference for Oracle ATP connection in OIC.

Step 2: Create Integration

I Prefer the Application Integration, Polling mechanism will work in the Initial Endpoint in the Integration.
Call ATP Here as like below.

Make sure choose the Review the polling strategy and specify polling options

Note: Apply your requirement handling in the read & unread value W.R.T perform on the Column [HIKE is a column for my case]

Whenever I update in HIKE column to "NO", The integration polling happens using ATP Polling Event & It will update the respective rows to "YES"

The Final Integration Design as below
I mapped EMPNAME as Primary Business identifier for track.

Step 3:TEST
I will update as below

Step 4:Integration Observation

It will automatically calls & update the field to "YES"

Full Flow Summary

DB Record Update → HIKE = 'NO'
OIC Polling Query Runs
Record is Picked
Integration Logic Executes
Record Updated → HIKE = 'YES'

Conclusion

This EMP table polling example demonstrates a lightweight and efficient pattern for OIC integrations:

Add a flag column
Poll using a filter condition
Process and update status

Combined with OIC Public Gateway and ATP Adapter, this becomes a fully cloud-native solution without any Connectivity Agent dependency.

OIC Developer Guide to Create AR Customer Contact Email & Phone in Oracle Fusion Using SOAP (mergePerson)

Naveen — Thu, 04 Jun 2026 16:01:53 +0000

In Oracle Fusion, AR Customer Contacts are stored as Party Relationships. Adding or updating contact details such as phone number and email address requires interaction with the Foundation Person Service SOAP API.
This guide walks you through:

Identifying required data (PartyId & RelationshipId)
Using SOAP API to create contact points
Verifying data in backend tables

Step 1: Identify Customer Contact Details
Relationship Information

select * from fusion.hz_relationships where relationship_id=300000159607279

Party Information

select * from fusion.hz_parties where party_id= 300000159607277

Step 2: Use SOAP API to Create Contact Details
WSDL Endpoint:
Use SOAP API /crmService/FoundationPartiesPersonService to add new phone number and email for this AR Customer Contact

Operation:mergePerson
This API is used to update or merge person-related information, including contact points.

Step 3: Sample SOAP Request
Below is a working payload to create both email and phone:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
                  xmlns:typ="http://xmlns.oracle.com/apps/cdm/foundation/parties/personService/applicationModule/types/"
                  xmlns:per="http://xmlns.oracle.com/apps/cdm/foundation/parties/personService/"
                  xmlns:rel="http://xmlns.oracle.com/apps/cdm/foundation/parties/relationshipService/"
                  xmlns:con="http://xmlns.oracle.com/apps/cdm/foundation/parties/contactPointService/">
   <soapenv:Header/>
   <soapenv:Body>
      <typ:mergePerson>
         <typ:personParty>
            <per:PartyId>300000159607277</per:PartyId>

            <per:Relationship>
               <rel:RelationshipId>300000159607279</rel:RelationshipId>

               <!-- Email Creation -->
               <rel:Email>
                  <con:OwnerTableName>HZ_PARTIES</con:OwnerTableName>
                  <con:CreatedByModule>HZ_WS</con:CreatedByModule>
                  <con:ContactPointPurpose>BUSINESS</con:ContactPointPurpose>
                  <con:EmailAddress>Addtest@test.com</con:EmailAddress>
               </rel:Email>

               <!-- Phone Creation -->
               <rel:Phone>
                  <con:OwnerTableName>HZ_PARTIES</con:OwnerTableName>
                  <con:CreatedByModule>HZ_WS</con:CreatedByModule>
                  <con:ContactPointPurpose>BUSINESS</con:ContactPointPurpose>
                  <con:PhoneNumber>2128811223</con:PhoneNumber>
                  <con:PhoneExtension>999</con:PhoneExtension>
                  <con:PhoneLineType>MOBILE</con:PhoneLineType>
               </rel:Phone>

            </per:Relationship>
         </typ:personParty>
      </typ:mergePerson>
   </soapenv:Body>
</soapenv:Envelope>

Step 4: Validate Data in Frontend Screen

Step 5: Validate Data in Backend Tables
After successful API execution, verify using:

SELECT CONTACT_POINT_ID,
       CONTACT_POINT_TYPE,
       PHONE_TYPE,
       OWNER_TABLE_ID,
       RELATIONSHIP_ID,
       PHONE_NUMBER,
       EMAIL_ADDRESS
FROM fusion.hz_contact_points
WHERE creation_date > SYSDATE - 1
  AND created_by_module = 'HZ_WS'
  AND owner_table_name = 'HZ_PARTIES';

Step 6: From OIC

With same WSDL :https:/crmService/FoundationPartiesPersonService?WSDL
create Connection.
Call the connection in OIC with mergePerson Operation
Pass respective Values to it

Conclusion:
We successfully updated the Customer communication details using SOAP WSDL in Oracle Fusion ♥

Enabling Oracle Fusion BI Publisher Report Access via Custom Roles & Springboard

Naveen — Tue, 02 Jun 2026 16:16:47 +0000

In Oracle Fusion, BI Publisher (BIP) reports are widely used for operational & analytical. However, a common requirement in enterprise implementations is to provide secure, role-based access to specific reports and make them easily accessible through the Springboard (home page).
This blog walks you through how to:

Restrict BI Publisher report access to specific users using custom roles
Surface the report in the Springboard for quick access.

Let's start with Custom Role

Step 1: Create a Custom Role
Navigations: Under Home page-->Tools-->Security console
create new role

continue with next upto User, In the user section add respective users

Step 2: Expose Report in Springboard
Now let’s improve usability by making the report accessible directly from the home page.
Enable Sandbox
Mostly sandbox appears under Configuration in the springboard

Other way to access sandbox is from the navigations.

Once click on the sandbox, you will reflect new page like below

click on the create Sandbox and select above marked things.
Provide respective details like Sandbox name & publish yes

The next move is to click on structure

Then carefully we have to Create Group

let provide Groups as below here EL Expression important to note.
Because it plays a key role in the access reports.

followed by save & continue.

Time for create Page Entry
where actually report path provides here.

Note: we always create page entry under groups i.e. REPORT HUBSPOT is my group earlier.

In the dialog box copy entire report path with extension as like above
and validate & save -close safely.

Test the report access
By click on the home icon you will go to springboard there you will see your created group & page likes

Publish the Sanbox

Once you published , won't altered with the same sandbox. So be caution while doing activity & avoid personal bias

Final words:

By combining BI Publisher security with Fusion role-based access control and Springboard customization, you can deliver a seamless and secure reporting experience for users.

This approach not only enhances usability but also ensures compliance with organizational security standards.

Calling GET API & Mapping Response in VBCS (Service Connection)

Naveen — Sun, 31 May 2026 14:46:10 +0000

In Oracle VBCS, integrating a GET API and displaying its response on the UI is one of the most common use cases. In this blog, we’ll walk through how to:

Create a GET API (ORDS)
Configure Service Connection in VBCS
Call API using Event Trigger
Map API response to UI components

1. Sample GET API (ORDS)

Let’s first create a simple GET API to fetch data from nj_dump(NJ_DUMP is a table).

Below is code snippet for the creations GET API using PLSQL

BEGIN
  ORDS.define_template(
    p_module_name => 'nj_api',
    p_pattern     => 'get_data'
  );
END;
/

BEGIN
  ORDS.define_handler(
    p_module_name => 'nj_api',
    p_pattern     => 'get_data',
    p_method      => 'GET',
    p_source_type => ORDS.source_type_query,
    p_source      => '
      SELECT name, num, dob, email
      FROM nj_dump
      WHERE num = nvl(:num,num)
    '
  );
END;
/

ENDPOINT looks like
GET /ords//nj_api/get_data?num=101
Before moving to VBCS kindly test it in POSTMAN or Curl
Note: I prefer Basic Authorization.

2. Create Service Connection in VBCS
Navigations: Go to Services → Service Connections→ Chose define by endpoint
Click Create
Enter: respective Details

3. Add GET Endpoint
Paste your respectively GET URL in the below bar & proceed next

Once you done with GET REQUEST endpoint , you will able to see like this

4. Create Page Variables
Under Web apps we will create type & variables.

Hope you choose the type from endpoint
you will your endpoints here like this, pick get for our case.

In the next step we have to select the needed column to display in the VBCS UI

successfully create type, it will help us to create variable in the next page.

Now go to variables & create variable, make sure select the type which you created earlier.

5.Assign Variables to the form/page

I created form layout, if code needed, I would provide you at end of blog.

In the same manner add all variables to the respective columns in the form.

6. Call GET API Using Event Trigger
Now whenever we hit the Search button, The get REST endpoint will be called return the data to form.

For that we need to create an event action trigger there we will assign the get endpoint & collect the response from it and assign the same type.

7. Pass Query Parameter
choose the respective actions here & map the num Variable to the GET API endpoint

Mapper reference

8. Testing
Working as expected:

9. End-to-End Flow

User enters num
Event Trigger fires (button / field change)
GET API is called via Service Connection
Data is fetched from DB
Response stored in apiResponse
UI automatically updates

We successfully completed GET API calling in VBCS with help of Service connection & on button event trigger.

Try with Variable onValueChanged Event instead of button

Form code:

Oracle Fusion Report Scheduling with Skip Conditions

Naveen — Sat, 30 May 2026 14:48:41 +0000

In Oracle Fusion Applications, report scheduling plays a crucial role in automating business processes such as invoice generation, reconciliation, and operational reporting. However, not every scheduled job needs to run every time especially when there’s no relevant data.

This is where skip conditions come into play.

Skip conditions allow you to control whether a scheduled report or job should execute based on predefined logic.

Instead of wasting system resources, unnecessary runs are avoided, making scheduling smarter and more efficient.

Okay Let's drive-in into how we can save system resources.

1. Create your query in the Data Model.

2.Write Event Trigger
Simple choose Event Trigger in the same data model. Select Event Type as Scheduled.

FYR

Now write logic for when it should be skip. In my case when Data model no return any data, then won't triggers to clients.

SQL Query Reference when no data returns from data model.
SELECT DISTINCT 'true' FROM DUAL WHERE EXISTS ( SELECT 1 FROM abc//here your data model conditional logic )
3.Build Report with your template & schedule it
Time to schedule the report as below. You can add your parameters also in your data model.

choose your delivery channel. -In the Schedule Tab, select your frequency & Define Schedule Trigger(It's important here)
Here pick your event trigger which has written early in the data model . FYR

Conclsuion
By adopting skip conditions, organizations can:

Reduce system overhead
Improve report relevance
Enhance overall scheduling efficiency