OIC interview questions

---

🚀 Oracle Integration Cloud (OIC) Gen3 – Interview Q&A & Real-Time Insights

📅 Updated: July 26, 2025
✍️ By: Aks]

---

Oracle Integration Cloud (OIC) Gen3 is the latest evolution in Oracle’s cloud integration suite, bringing faster runtimes, enhanced observability, and a simplified development experience.

This blog addresses some frequently asked interview questions and real-time use case clarifications for professionals working with OIC Gen3.

---

🔁 1. What is the Difference Between FTP Adapter and File Adapter?

Feature	FTP Adapter	File Adapter
Use Case	Connects to FTP/SFTP servers	Accesses on-premise file systems
Deployment	Cloud-native	Requires OIC Connectivity Agent
Polling Support	✅ Yes	✅ Yes (via agent)
Transfer Type	Remote files over network	Local or network-shared files
Common Scenario	Download/upload files from a bank FTP	Reading data files from an on-premise legacy system

---

📦 2. What is FBDI Integration?

FBDI (File-Based Data Import) is Oracle Fusion’s standard method to load bulk data via interface tables using CSV/XML templates.

🔧 Typical FBDI Integration Steps:

1. Generate CSV file based on FBDI template (e.g., Suppliers, Journals).

2. Upload file to UCM (via ERP Cloud Adapter).

3. Invoke ERP Import job (e.g., Import Payables Invoices).

4. Monitor status and fetch results using callback or polling.

🧠 Tip: Always validate CSV structure and lookup codes before upload.

---

🔗 3. What Connections Are Required for FBDI Integration?

Connection	Purpose
ERP Cloud Adapter	Upload to UCM and run import jobs
FTP Adapter (Optional)	Retrieve CSV file from a remote location
SOAP Adapter	Callback/monitor import job status (if needed)
Email Adapter	Send notifications post import

---

📂 4. Does FTP Adapter Support Polling?

✅ Yes!

The FTP Adapter in OIC Gen3 supports file polling. You can configure:

• Directory path

• Polling frequency

• File patterns (e.g., *.csv)

• Archival/move after processing

👉 Use Cases: Fetching daily reports, invoices, or orders dropped by external systems.

---

🧮 5. How to Calculate the Top 2nd Salary (SQL)?

SELECT MAX(salary) AS second_highest_salary
FROM employees
WHERE salary < (
  SELECT MAX(salary) FROM employees
);

🔎 This query works by excluding the highest salary and fetching the next max value.

---

🔄 6. What is the Difference Between Synchronous and Asynchronous Integration?

Feature	Synchronous	Asynchronous
Client Waits?	✅ Yes	❌ No
Response Time	Immediate	Background / Delayed
Use Case	Real-time APIs	Bulk processing, FBDI, callbacks
Example	Get Employee Info by ID	Upload journal file, run job, send callback

---

🔃 7. Can We Convert Scheduled Integration to App-Driven?

🚫 No, this is not supported directly.

• Scheduled integrations are time-triggered and don’t accept external requests.

• You can recreate the flow logic in a new App-Driven integration.

🔧 Workaround: Copy artifacts and logic manually between integrations.

---

🔁 8. Can We Convert App-Driven to Scheduled Integration?

🚫 No, direct conversion is not supported.

• App-driven integrations are designed to be invoked by APIs or events.

• Scheduled integrations work on time-based triggers.

🛠 You must build a new scheduled integration with similar flow logic.

---

✨ Bonus Tip: Best Practices for OIC Gen3

• Use global fault handlers for robust error management.

• Group related integrations using lookup tables and packages.

• Schedule data purging for long-running integrations to keep performance optimal.

---

📌 Conclusion

OIC Gen3 offers robust capabilities for hybrid integrations, event-driven orchestration, and bulk data handling via FBDI. Understanding how adapters differ, how to design for sync/async patterns, and when to use scheduled vs app-driven flows is key for both developers and architects.

🔎 Whether you're preparing for an interview or implementing real-time use cases, mastering these patterns will give you a solid edge in working with Oracle Cloud integrations.

---

OTBi Reports Drill down Report

Here’s a visually enhanced blog post crafted for Blogspot or any modern CMS. It now includes screenshots to illustrate navigation and drilling behavior within OTBI.

---

📊 Creating Drill-Down Reports in OTBI (Oracle Fusion)

🗓 Updated: July 26, 2025
✍️ Author: [Aks]

---

🔍 What is OTBI Drill‑Down?

Oracle Transactional Business Intelligence (OTBI) offers interactive reporting within Oracle Fusion. With drill-down functionality, users can seamlessly transition from summary figures to detailed data—right from the same dashboard.

This guide shows you how to create interactive drill-downs using Action Links, transforming summary-level reporting into dynamic data discovery.

---

💼 Business Use Case: Supplier Invoices

Imagine a dashboard showing total invoice amounts by supplier. With a drill-down:

• Clicking a supplier name opens a detailed listing of invoices for that supplier.

• Enables self-service exploration without navigating multiple screens.

---

🛠 Step-by-Step: Build Drill‑Down in OTBI

Step 1: Summary Report (Summary Report)

• Select columns like:
  • Supplier Name
  • Invoice Count
  • Invoice Amount

• Save it as Summary Report

---

Step 2: Detail Report (Detail Report)

• Include granular fields:
  • Invoice Number
  • Invoice Date
  • Amount

• Add filter prompt:

  "Supplier"."Supplier Name" = @{SupplierName}
  

• Save as Detail Report

---

Step 3: Configure Action Link in Summary

In Summary Report:

1. Edit the Supplier Name column.

2. Go to Column Properties → Interaction.

3. Set Primary Interaction to Action Links.

4. Click Add Action Link:

   • Choose Navigate to BI Content

   • Select Detail Report

   • Map Supplier Name to @{SupplierName}

This creates the clickable link—from summary to detail.

---

✅ Test the Drill

• Run the Summary Report.

• Click on a Supplier Name—your Detail Report opens with filtered data.

• Ensure both reports are saved and reside in shared folders and use the same subject area.

---

🧯 Troubleshooting Tips

• Ensure the prompt variable in Detail matches the column name exactly.

• Both reports must use the same subject area.

• Confirm the Action Link mapping is correct.

• Use reports in dashboard view, not edit mode, for links to work properly.

---

📸 Visual Guide: Screens & Routing

1. Summary with action links on Supplier Name (Image 1)

2. Editing Action Link settings (Image 2)

3. Drilled detail report after click (Image 3)

4. Advanced drill via deep link configuration (Image 4)

These visual cues help navigate to the right settings and confirm linkage results.

---

📌 Tips & Extra Use Cases

• Extend drill-down to other fields like Business Unit, GL Period, Employee ID.

• For deeper analytics, create multi-level drill routines (e.g. invoice → payment status).

• Consider deep links to Fusion application pages for direct navigation—especially for expense reports or project cost details (docs.oracle.com, oracle.com, kaamilant.com, oracle-hub, tangenz.com, docs.oracle.com).

---

🚀 Why Drill‑Down Reports Deliver Value

• Enables self-service data exploration directly within OTBI.

• Eliminates manual extracts or duplicate report generation.

• Streamlines workflows by allowing navigational depth without leaving dashboards.

OIC GEN 3 ISSUES - Fixing NXSD Schema "No Data Found" or "No File to Process" Errors

🧠 Oracle Integration Cloud – Fixing NXSD Schema "No Data Found" or "No File to Process" Errors

📅 Updated: July 26, 2025
🔧 Category: Oracle Integration / File-Based Integrations
✍️ Author: Aks

---

⚠️ Problem Statement

You’ve built a file-based integration in OIC or SOA Suite using the NXSD schema (Native Format Builder). But during testing or runtime, you get errors like:

• “No data found or file is empty”

• “No file to process”

• “NXSD parser error – Data is not present in expected format”

---

🧪 Common Symptoms

• The integration completes with warning or faulted status.

• Logs or activity stream shows:

  JCA-11611: Failed to parse file.
  Reason: Data not present in expected format.
  

• No records are inserted or mapped.

• You receive a blank payload or <null> in the target node.

---

🔍 Root Cause

These issues almost always boil down to mismatch between your NXSD schema definition and the actual data in the file.

💡 NXSD (Native eXternal Schema Definition) is used in OIC/SOA to parse flat files (CSV, TXT, fixed-length, etc.). It acts like an XSD for non-XML data.

---

🛠️ Step-by-Step Fix Guide

✅ 1. Check File Format vs Schema

• Open your file and compare:

  • Delimiter used (comma, pipe, tab)

  • Number of fields per line

  • Presence of header rows

• Match these against the NXSD schema (.xsd with NXSD annotations).

📎 Example: If your NXSD expects 5 fields but the file contains 6, parsing will fail.

---

✅ 2. Validate NXSD Schema in JDeveloper or OIC

• If using JDeveloper:

  • Right-click NXSD file → Run Native Format Tester

  • Load sample data → See if parsing works

• In OIC, download and test file locally or review logs from the Activity Stream.

---

✅ 3. Set Correct Record Delimiter and Field Count

In NXSD definition:

• Check recordSeparator, e.g., \n, \r\n

• Ensure each <element> tag matches a field in the row

• If the file has blank lines or extra separators, parsing may break

<nsxsd:element name="Employee">
  <nsxsd:field name="EmpId"/>
  <nsxsd:field name="Name"/>
  <nsxsd:field name="Salary"/>
</nsxsd:element>

---

✅ 4. Use IgnoreEmptyLines="true" (Optional)

Add this attribute to <nxsd:format> if your file has blank lines:

<nxsd:format name="MyFormat" type="delimited" delimiter="|" ignoreEmptyLines="true" />

---

✅ 5. Watch for Header Rows

If your file contains headers, you must skip them:

<nxsd:format name="Header" skipLines="1" ... />

Alternatively, handle this in OIC using the file adapter configuration (uncheck “First row as header”).

---

✅ 6. Validate File Encoding

• Ensure your file is UTF-8 encoded (especially if created in Excel or external systems)

• Use Notepad++ or VS Code to confirm encoding

---

✅ 7. Use File Test Flow

If you're unsure, create a dummy integration that reads the file and logs its content. This helps isolate whether the error is with:

• File reading

• NXSD parsing

• Mapping logic

---

🔄 Example: Fix for a 3-Column CSV with Pipe Delimiter

✅ Input File

101|John Smith|65000
102|Lisa Wong|72000

❌ Common Mistake in NXSD

Using comma , instead of pipe |:

<nxsd:format delimiter="," />

✅ Corrected NXSD Snippet

<nxsd:format delimiter="|" recordSeparator="\n" />
<nxsd:element name="Employee">
  <nxsd:field name="EmpId"/>
  <nxsd:field name="Name"/>
  <nxsd:field name="Salary"/>
</nxsd:element>

---

📋 Summary Table

Issue	Possible Fix
File is blank	Check if file is being dropped correctly
File delimiter mismatch	Update NXSD with correct `delimiter="
Unexpected number of fields	Match schema elements with actual columns
Blank lines in file	Use ignoreEmptyLines="true"
File includes headers	Use skipLines="1" or handle via File Adapter UI
Encoding issues	Convert file to UTF-8

---

🎯 Conclusion

NXSD schema parsing issues can be frustrating but are often caused by small misconfigurations. By validating your file structure, NXSD definitions, and adapter settings, you can quickly isolate and fix the issue.

✅ Always test your schema with sample data and use logging and tracing to confirm what’s being parsed. 

🔒 Oracle Fusion HCM – Fix for Missing Data, Address, or Legislative Information

📅 Updated: July 26, 2024    
📌 Category: Oracle Fusion HCM / Security Setup
✍️ By: Aks

---

❓ Issue Overview

Are you or your users facing a scenario where the employee data, address, or legislative information is not visible in Oracle Fusion?

You’ve assigned the right job roles — like Human Capital Specialist — but when opening Person Management or Manage Employment, the details are missing.

---

🧠 Root Cause

This typically occurs when:

• A Data Role is not associated with the user.

• Security Profiles are misconfigured or not assigned.

• The Person Security Profile is filtering out person records.

• The Legislative Data Group visibility is restricted.

---

✅ Resolution – Create a Proper Data Role and Assign It

Here’s a step-by-step guide to fix it via Setup and Maintenance:

---

🔧 Step 1: Go to Setup and Maintenance

• Navigate to:
  My Client Groups → Setup and Maintenance

---

📍 Step 2: Search for “Manage Data Roles and Security Profiles”

1. Click Search Tasks.

2. Enter Manage Data Roles and Security Profiles.

3. Select the result and open the page.

---

🧰 Step 3: Create a Data Role

1. Click Create Data Role.

2. On the Select Role page:

   • Select the base role:
     Human Capital Management Specialist (or relevant job role).

3. Click Next.

---

🔐 Step 4: Configure Security Profiles

1. Person Security Profile

   • Choose an existing one (e.g., View All Workers)
     OR create a new profile with:

     • Person Type: Employee, Contingent

     • Access Level: All

     • Include terminated workers (optional)

2. Assignment Security Profile – Choose based on access needs.

3. LDG Security Profile

   • Select appropriate Legislative Data Group (e.g., US LDG).

💡 Ensure the LDG profile matches the country for which you're managing employees. If missing, legislative info won't show.

4. Click Next, then Review and Submit.

---

👤 Step 5: Assign the Data Role to the User

1. Navigate to My Client Groups → Users.

2. Search for the affected user.

3. Click Edit → Roles.

4. Add the newly created Data Role.

5. Save and Confirm.

---

🔄 Step 6: Sign Out and Back In

Changes won’t take effect immediately. Have the user:

• Sign out

• Wait 1–2 minutes

• Sign in again

---

🧪 Test It

1. Go to Person Management.

2. Search for a known employee.

3. Confirm the Address, Legislative Info, and Employment Details are now visible.

---

🧯 Troubleshooting Tips

🔎 Symptom	🔧 Suggestion
Only name shows, no address or job	Check LDG Security Profile
Can’t search for people at all	Verify Person Security Profile scope
Error: “You do not have access to view this data”	Ensure data role is assigned and active

---

📌 Summary

Fix Summary	Status
Create Data Role on Human Capital Specialist	✅ Done
Assign Person & LDG Security Profiles	✅ Done
Add Role to User	✅ Done
Re-login to activate access	✅ Done

---

💬 Final Thoughts

Oracle Fusion’s role-based access model is powerful but strict. Without a proper Data Role and Security Profile, even core HR users won’t see basic information. Always configure both job roles and data roles to ensure access.

---

💡 Tip: You can create Country-Specific Data Roles to limit access based on LDG, especially useful in multinational orgs.

---

✅ Need Help Setting Up Profiles?

Drop your comments below or connect with us for step-by-step workshops on Fusion security, role configuration, and workforce structure setup.

Key Setups in ORACLE Fusion HCM

🛠️ Oracle Fusion Setup & Maintenance: Your Ultimate Guide

Oracle Fusion includes a robust Setup and Maintenance work area—also known as the Functional Setup Manager (FSM)—which serves as the central hub for configuring enterprise modules like HCM, SCM, Asset Maintenance, and more.

🔍 Navigating the Functional Setup Manager

Log in with an Application Implementation Consultant role to access setup tasks. Navigate via Navigator → Setup and Maintenance or the dedicated icon on the Home page. :contentReference[oaicite:1]{index=1}

🔧 Generating HCM Setup Task List

After selecting the appropriate HCM offering (such as Global HR, Payroll, Benefits), you can generate a task list tailored to your configuration needs. Tasks include setting up enterprise structures, currencies, profiles, payroll definitions, compensation, and more. :contentReference[oaicite:2]{index=2}

⚙️ Steps to Configure Asset Maintenance

• Enable Maintenance Management in the Offerings list of the SCM or Asset module.
• Define master data: Set up plant parameters, work centers, resources, meter templates, warranty setups, and more.
• Configure lookups, Descriptive Flexfields (DFFs), Key Flexfields (KFFs), and value sets.
• Set asset group rules, attachments, meter readings, preventive programs.
• Define Asset Maintenance parameters for IoT, work order defaults, validation organization, user enforcement options, and more. :contentReference[oaicite:3]{index=3}

📊 Setup Methods Comparison

Oracle supports multiple methods to create setup data:

Object	Manual Setup	REST API	File-based Import
Work Area, Work Center, Resources	Yes	Yes	No
Standard Operations, Assets, Meter Templates	Yes	Yes	Yes (for some)
Supplier Warranty, Forecast Programs	Yes	Yes	No

This approach helps match your implementation strategy—whether it’s manual, integrations via REST APIs, or bulk import. :contentReference[oaicite:4]{index=4}

📌 Optional Configurations (Based on Licensing)

• License and configure Help Desk and IoT Asset Monitoring.
• Set up smart search, AI Assist, guided journeys, and validation rules.
• Customize lookups and value sets for Maintenance and Asset modules.:contentReference[oaicite:5]{index=5}

✅ Best Practices at a Glance

1. Sign in with appropriate roles (e.g., Application Implementation Consultant or IT Security Manager).
2. Select the correct offering that aligns with your business requirements.
3. Generate HCM or Maintenance task lists tailored to chosen features.
4. Define enterprise structures, security profiles, lookup values, and flexfields.
5. Enable optional features like AI Assist, Warranty, and Guided Journeys depending on your license.
6. Choose configuration method wisely: Manual > REST > Import, based on object type.
7. Review parameters thoroughly—especially IoT credentials and Maintenance rules.

💡 Why It Matters

Proper setup using FSM ensures:

• Accurate master data management
• Efficient operations and automation
• Robust audit trails and compliance
• Seamless integration with API, reporting, and extensions

📚 Related Articles

• OIC Integration & Interview Q&A
• How to Print WHO Columns in Oracle Reports

📝 Feedback or Questions?

Feel free to leave a comment below or reach out via our Contact Page.

Unable to Create Worker from Front End in Oracle Fusion HCM

Fix: Unable to Create Worker from Front End in Oracle Fusion HCM

---

🚩 Issue Overview

Users sometimes face an issue where they cannot create a new worker via the front end in Oracle Fusion HCM. The system either does not allow the creation or throws errors related to the employee number generation.

---

🔍 Root Cause

This usually happens because the Employee Number Generation setting is not properly configured. If the system expects manual entry but it's set to automatic, or vice versa, the front end creation process can fail.

---

🛠️ Quick Fix: Set Employee Number Generation to Manual

Step 1: Navigate to Setup and Maintenance

1. Log in to Oracle Fusion as an administrator or someone with setup privileges.

2. From the homepage, click on the Setup and Maintenance icon.

---

Step 2: Search for HCM Enterprise Setup

In the Setup and Maintenance search bar, enter:

HCM Enterprise Setup

Select the task Manage Enterprise HCM Setup from the results.

---

Step 3: Set Employee Number Generation to Manual

In the HCM Enterprise Setup page:

• Find the option Employee Number Generation.

• Change the value from Automatic to Manual.

---

Step 4: Save and Test

• Save your changes.

• Go back to the front end and try to create a new worker.

• You should now be able to enter the employee number manually and successfully create the worker record.

---

💡 Why Manual Employee Number Generation?

Setting the employee number generation to manual ensures:

• The system does not auto-assign employee numbers which may conflict with business processes.

• Admins have full control over numbering and can avoid duplication issues.

---

⚠️ Additional Tips

• Ensure the user has the required privileges to create workers.

• Clear browser cache or try another browser if changes don't take effect immediately.

• Check for any custom validations or workflows that may block worker creation.

---

📌 Summary

Issue	Unable to create worker from front end
Cause	Employee Number Generation not set to manual
Fix	Setup & Maintenance → HCM Enterprise Setup → Set employee number generation to manual
Result	Allows manual employee number entry, enabling worker creation

---

💬 Have you faced this issue?

Leave a comment below or reach out for further help!

How to Use Triggers to Get Delivery Details When Orders Are Backordered

---

🚩 Business Scenario

In many supply chain and order management systems, orders sometimes go into a backorder status when inventory is insufficient to fulfill the order immediately. To streamline operations and improve visibility, businesses want to automatically retrieve delivery details whenever an order is backordered.

This blog explains how to implement a trigger that fires when orders become backordered and fetches relevant delivery details.

---

🔍 What is a Trigger?

A trigger is a procedural code automatically executed in response to certain events on a database table or application event—like when an order’s status changes.

In this case, the trigger activates when an order status is updated to "Backordered" and then retrieves delivery information related to that order.

---

🛠️ Step-by-Step Implementation

Step 1: Identify the Event to Trigger On

• Trigger should fire after update on the orders table.

• Condition: Order status changes to Backordered.

---

Step 2: Write the Trigger Logic

• Fetch the delivery details related to the order (such as delivery date, delivery address, shipping method).

• Store or log these details for visibility or further processing.

---

Sample SQL Trigger (Oracle PL/SQL)

CREATE OR REPLACE TRIGGER trg_backorder_delivery
AFTER UPDATE OF order_status ON orders
FOR EACH ROW
WHEN (NEW.order_status = 'Backordered')
DECLARE
  v_delivery_date   DATE;
  v_delivery_address VARCHAR2(200);
  v_shipping_method VARCHAR2(50);
BEGIN
  SELECT delivery_date, delivery_address, shipping_method
  INTO v_delivery_date, v_delivery_address, v_shipping_method
  FROM deliveries
  WHERE order_id = :NEW.order_id;

  -- Example: Insert into a log table or notify the user
  INSERT INTO backorder_delivery_log(order_id, delivery_date, delivery_address, shipping_method, log_date)
  VALUES (:NEW.order_id, v_delivery_date, v_delivery_address, v_shipping_method, SYSDATE);
  
  -- Optionally, send notification or trigger other business logic here

END;
/

---

Step 3: Test the Trigger

• Update an order’s status to Backordered.

• Verify the trigger fires and delivery details are fetched and logged as expected.

---

💡 Best Practices

• Always test triggers in a development or test environment before deploying to production.

• Avoid complex logic inside triggers; keep them efficient to prevent slowdowns.

• Log activities for auditing and troubleshooting.

---

🔗 Integration Tips

• This trigger can be extended to integrate with messaging queues or notification systems for real-time alerts.

• Use Oracle Integration Cloud or similar middleware to orchestrate advanced workflows based on backorder events.

---

📌 Summary

Aspect	Details
Trigger Event	After update of order status
Trigger Condition	Order status = 'Backordered'
Action	Retrieve and log delivery details
Use Case	Improve visibility on backordered orders

---

🎯 Conclusion

Using triggers to automatically fetch delivery details when orders are backordered streamlines supply chain visibility and enables proactive customer communication. This simple automation enhances operational efficiency and customer satisfaction.

Custom Check Printing in Oracle Apps R12: Step-by-Step Guide

---

💡 Introduction

Oracle E-Business Suite R12 offers robust Payment Process functionality, including check printing. Businesses often need custom check layouts that match branding, comply with regulations, or include extra info.

This blog guides you through customizing check printing in Oracle Apps R12—from setup to template deployment.

---

🎯 Why Custom Check Printing?

• Match company branding & legal requirements

• Add invoice or payment details

• Support bank-specific formats

• Improve check readability & security

---

🔍 Standard Check Printing Flow in Oracle R12

Oracle Payments handles check printing using:

• Check Print Program (APXCKMND) to generate payment data

• BI Publisher (XML Publisher) to merge data with templates

• Standard templates included but customizable

---

🛠️ How to Implement Custom Check Printing

---

Step 1: Understand Data Flow

• Payment batch processed → XML data generated → Template merges data → Check output produced

---

Step 2: Customize XML Data (If Needed)

You may want to include additional fields in the XML data sent to BI Publisher.

Sample XML snippet with added invoice_number:

<Check>
  <SupplierName>ABC Corp</SupplierName>
  <PaymentAmount>1500.00</PaymentAmount>
  <InvoiceNumber>INV-12345</InvoiceNumber>
  <PaymentDate>2025-07-26</PaymentDate>
</Check>

---

Step 3: Design/Modify BI Publisher Template

Use Microsoft Word with the BI Publisher plugin:

• Insert XML fields via Insert > Quick Parts > Field

• Format fonts, add company logos, borders, and signature lines

Sample template editor screenshot:

---

Step 4: Upload Template to Oracle

• Navigate to XML Publisher > Template Manager

• Upload your Word (RTF) template

• Assign it to the Check Print report

• Set it as default

---

Step 5: Test Check Printing

Run the Check Print Program (APXCKMND) for your payment batch and verify the output.

---

📝 Tips & Best Practices

Tip	Description
Backup originals	Always keep a copy of default templates
Test thoroughly	Use test batches to validate formatting
Secure fonts	Use clear, standard fonts to prevent fraud
Maintain version control	Track changes for audit and rollback purposes

---

🔗 Oracle Documentation

• Oracle Payments User Guide

• BI Publisher Developer Guide

---

🎯 Conclusion

Custom check printing empowers organizations to meet unique requirements with Oracle Apps R12. Leveraging XML data and BI Publisher templates ensures flexible, professional outputs.

---

💬 Questions?

Leave a comment below or ask for custom template samples or video tutorials!

---

If you'd like, I can prepare a sample custom template or a video walkthrough for the full process. Just let me know!

Sample sql for giving custom rownumbering depending on some conditions

Custom Row Numbering in SQL Using ROW_NUMBER()

In SQL, the ROW_NUMBER() function is extremely useful when you want to assign a unique sequential number to rows within a result set. This is especially helpful when partitioning data or performing advanced reporting operations.

📌 When to Use ROW_NUMBER()

• To assign a unique row number per group or partition
• To filter the first or last row in each group
• To remove duplicates while retaining the latest or earliest record

💻 SQL Syntax Example

SELECT ename,
       ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY column2) AS enum
FROM emp;

🔍 Explanation

• ENAME – Employee name column from the emp table.
• ROW_NUMBER() – Assigns a unique row number starting at 1 for each partition.
• PARTITION BY column1 – Resets the row numbering for each group defined by column1.
• ORDER BY column2 – Determines the order in which row numbers are assigned within each group.
• enum – An alias for the generated row number column.

📈 Common Use Cases

1. Getting the top-ranked employee per department
2. Removing duplicates while keeping the most recent entry
3. Paginating results in a web application

💡 Pro Tip

You can replace ROW_NUMBER() with RANK() or DENSE_RANK() depending on whether you want to allow duplicate ranks.

Using ROW_NUMBER() is a clean and powerful way to handle advanced SQL queries where row-based logic is required. Mastering it can significantly simplify your analytical tasks!

Top 25 Oracle Apps Interview Question and Answers.

🔍 Top 25 Oracle Apps Technical Interview Questions with Answers

Looking to crack your next Oracle Apps technical interview? This guide covers 25 of the most frequently asked questions along with detailed explanations to help you understand core Oracle concepts, tools, and best practices. Whether you're preparing for a developer, consultant, or support role, these Q&As will give you the edge you need.

1. How to use WHO columns in RDF reports?

WHO columns are standard audit columns: CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN. In RDF reports, you should add these columns to the SELECT statement and populate them using user exits like FND_SRWINIT and FND_GLOBAL.USER_ID.

2. What are user exits in Oracle Apps?

User exits are used in Oracle Reports to access AOL (Application Object Library) features. Common ones:

• FND_SRWINIT – Initializes AOL context

• FND_USEREXIT – Calls other exits

• FND_FORMAT_CURRENCY, FND_STANDARD_DATE – Format utilities

3. What is PRAGMA AUTONOMOUS_TRANSACTION?

This allows a PL/SQL block to perform a transaction (commit/rollback) independently of the main transaction. Useful for logging or auditing.

PRAGMA AUTONOMOUS_TRANSACTION;

4. What is BULK COLLECT?

BULK COLLECT allows fetching multiple rows into PL/SQL collections in a single context switch, improving performance.

SELECT empno, ename BULK COLLECT INTO l_empnos, l_names FROM emp;

5. Error handling in BULK COLLECT

Use SAVE EXCEPTIONS in combination with FORALL to continue processing even if some DML operations fail. Capture errors using SQL%BULK_EXCEPTIONS.

6. Dynamic logo in XML Publisher

Use a FORM-FIELD placeholder in the RTF template and pass the image path as a concurrent program parameter. Use url: prefix for dynamic URL.

<?xdofx: url:{LOGO_URL}?>

7. Multilanguage XML Reports

Use XLIFF or translation templates in BI Publisher. Upload different RTFs for each language or use conditional statements in RTF for dynamic labels.

8. Difference between Alerts and Triggers

• Alerts are Oracle Workflow-based notifications that run periodically.

• Triggers are DB-level code that fires automatically on DML actions.

9. How to use WHO columns in Oracle Forms

Assign WHO columns using fnd_standard.set_who in WHEN-NEW-FORM-INSTANCE trigger. This populates audit columns automatically.

10. Difference between Procedure and Function

• Procedure: Does not return a value directly; used for DML.

• Function: Must return a value; often used in SQL queries.

11. What is Bursting in XML Publisher?

Bursting lets you split a report output and deliver it to multiple recipients or destinations (email, printer, etc.) based on control XML.

12. Triggers in XML Publisher

Not DB triggers. Refers to logic in Data Template like beforeReport, afterReport, beforeData, afterData, etc.

13. Sequence of Trigger Firing in RDF Reports

1. Before Parameter Form

2. After Parameter Form

3. Before Report

4. Between Pages

5. After Report

14. Tables in O2C and P2P Cycle

The Order to Cash (O2C) cycle refers to the end-to-end process of receiving and fulfilling customer orders, from order entry to cash receipt. The Procure to Pay (P2P) cycle involves acquiring goods and services from suppliers and making payments.

• O2C: OE_ORDER_HEADERS_ALL, OE_ORDER_LINES_ALL, AR_INVOICE_HEADERS_ALL

• P2P: PO_HEADERS_ALL, PO_LINES_ALL, AP_INVOICES_ALL, AP_SUPPLIERS

• O2C: OE_ORDER_HEADERS_ALL, OE_ORDER_LINES_ALL, AR_INVOICE_HEADERS_ALL

• P2P: PO_HEADERS_ALL, PO_LINES_ALL, AP_INVOICES_ALL, AP_SUPPLIERS

15. How to find Requisition related to PO

Join PO_HEADERS_ALL → PO_REQ_DISTRIBUTIONS_ALL → REQUISITION_HEADERS_ALL using REQUISITION_LINE_ID.

16. Form Personalization vs Customization

• Personalization: Using Oracle Forms personalization for UI changes without coding.

• Customization: Changing the form itself using Forms Developer.

17. Conversion vs Interface

• Conversion: One-time data load.

• Interface: Repeated data movement via interface tables.

18. UNION vs UNION ALL

• UNION: Removes duplicates.

• UNION ALL: Keeps all rows including duplicates.

19. 2-Way, 3-Way, 4-Way Matching

• 2-Way: PO vs Invoice

• 3-Way: PO vs Invoice vs Receipt

• 4-Way: Adds inspection

20. Types of Purchase Orders (POs)

• Standard PO

• Planned PO

• Blanket PO

• Contract PO

21. What is Drop Shipment?

A sales order where the item is shipped directly from supplier to customer without going through inventory.

22. What is IR/ISO?

• IR: Internal Requisition

• ISO: Internal Sales Order Used for transferring items between inventory organizations.

23. TCA Architecture

Trading Community Architecture standardizes customer, supplier, and partner data. Key tables:

• HZ_PARTIES

• HZ_CUST_ACCOUNTS

• HZ_LOCATIONS

24. Mandatory Parameters for Standard API Calls

• p_api_version

• p_init_msg_list

• p_commit

• x_return_status

• x_msg_count

• x_msg_data

Use FND_MSG_PUB to retrieve messages.

Example Usage:

BEGIN
  hr_employee_api.create_employee (
    p_api_version        => 1.0,
    p_init_msg_list      => FND_API.G_TRUE,
    p_commit             => FND_API.G_FALSE,
    p_validation_level   => FND_API.G_VALID_LEVEL_FULL,
    x_return_status      => l_return_status,
    x_msg_count          => l_msg_count,
    x_msg_data           => l_msg_data
  );

  IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
    FND_MSG_PUB.GET (l_msg_count, l_msg_data);
    DBMS_OUTPUT.put_line('Error: ' || l_msg_data);
  END IF;
END;

• p_api_version

• p_init_msg_list

• p_commit

• x_return_status

• x_msg_count

• x_msg_data

Use FND_MSG_PUB to retrieve messages.

---

Would you like these turned into a PDF download or posted individually for SEO benefit?

Multilingual Xml reports