Friday, February 21, 2025

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

🔒 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”

Click Search Tasks.
Enter Manage Data Roles and Security Profiles.
Select the result and open the page.

🧰 Step 3: Create a Data Role

Click Create Data Role.
On the Select Role page:
- Select the base role:
  Human Capital Management Specialist (or relevant job role).
Click Next.

🔐 Step 4: Configure Security Profiles

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)
Assignment Security Profile – Choose based on access needs.
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.

Click Next, then Review and Submit.

👤 Step 5: Assign the Data Role to the User

Navigate to My Client Groups → Users.
Search for the affected user.
Click Edit → Roles.
Add the newly created Data Role.
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

Go to Person Management.
Search for a known employee.
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

Sign in with appropriate roles (e.g., Application Implementation Consultant or IT Security Manager).
Select the correct offering that aligns with your business requirements.
Generate HCM or Maintenance task lists tailored to chosen features.
Define enterprise structures, security profiles, lookup values, and flexfields.
Enable optional features like AI Assist, Warranty, and Guided Journeys depending on your license.
Choose configuration method wisely: Manual > REST > Import, based on object type.
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

📝 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

Log in to Oracle Fusion as an administrator or someone with setup privileges.
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!

Thursday, November 7, 2024

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

🎯 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!

Saturday, July 1, 2023

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

Getting the top-ranked employee per department
Removing duplicates while keeping the most recent entry
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!

Saturday, November 12, 2022

🔍 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

Before Parameter Form
After Parameter Form
Before Report
Between Pages
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?

Friday, November 11, 2022

Multilingual Xml reports

How to call dynamic logo in Xml reports

Mandatory Parameters In calling Oracle APPS Standard API.

Mandatory Parameters In calling Oracle APPS Standard API.

API Stands for Application Programming Interface is PL/SQL packaged procedure which can be used as an alternative entry point into the system to the traditional online forms(forms, OAF pages,..).

The advantage being that the same logic used by the seeded online forms can

Standard IN Parameters:--

1) p_api_version IN NUMBER
This must match the version number of the API. An unexpected error is returned if the calling program version number is incompatible with the current API version number

Example: p_api_version => 1.0

2) p_init_msg_list IN VARCHAR2
The valid values for this parameter are:
--> FND_API.G_TRUE
--> FND_API.G_FALSE

By default API will take FND_API.G_FALSE

Example: p_init_msg_list => fnd_api.g_true

3) p_commit IN VARCHAR2
The valid values for this parameter are:
* FND_API.G_TRUE --> True
* FND_API.G_FALSE --> False

If set to true, then the API commits before returning to the calling program.
If set to false, then it is the calling program’s responsibility to commit the transaction.

Example:- p_commit => fnd_api.g_true

4) p_validation_level IN VARCHAR2

The valid values for this parameter are:
* fnd_api.g_valid_level_full

Default = FND_API.G_VALID_LEVEL_FULL
If set to full, then the API validates all the IN parameter values.

Standard OUT Parameters

1) x_return_status OUT NOCOPY VARCHAR2
Indicates the return status of the API. Every API must return one of the following states as parameter x_return_status after the API is called:

S (Success)
E (Error)
U (Unexpected error)

2) x_msg_count OUT NOCOPY NUMBER
Holds the number of messages in the message list.

3) x_msg_data OUT NOCOPY VARCHAR2
Holds the encoded message if x_msg_count i

Wednesday, November 2, 2022

Triggers in Froms, rdf Reports, Xml reports, Forms Personalization

Oracle apps interview questions and Answers

1. What are the triggers in rdf report

> before parameter form

>after PARAMETER FORM

> BEFORE REPORT

>BETWEEN PAGES

>AFTER REPORT

2. Triggers in XML reports

>>>before report

>>>after report

>>>default package

3. Triggers in forms Personalization

>>>when new form instance instance

>>>when new block instance

>>>when new record instacne

>>>when new item instance

>>>when validate record

4. Triggers in oracle forms

>>>pre form triggers

>>>post form triggers

>>>when button pressed

>>>when validate record