Blog / Category
Oracle Application Blog
905 articles in this category
WIP Job Close via Apex Application
Introduction / Issue Closing a WIP job in EBS normally means going through the Discrete Jobs form manually — checking if all transactions are done, confirming t…
Fixing Incomplete AP Open Balance on the Oracle ECC Supplier Balance Dashboard
Introduction/Issue: Even though supplier setup in Oracle Payables was correct, the “Supplier Balance” dashboard on the Oracle Enterprise Command Center (ECC) wa…
Adding Order Date Filter to the Order Management ECC Dashboard
Introduction / Issue Users of the Order Management – Enterprise Command Center dashboard needed a way to narrow down the Orders by Order Date directly from the…
Preventing Duplicate Form Submission in Oracle APEX
Introduction / Issue One of the common issues encountered while developing Oracle APEX applications is duplicate form submission. Users may unintentionally clic…
UI Mistakes Oracle APEX Developers Should Avoid
Introduction / Issue Many Oracle APEX applications successfully meet business requirements but often overlook one critical aspect—the User Interface (UI) and Us…
VBCS – Exporting Data as CSV File From Database
VBCS – Exporting Data as CSV File From Database Introduction: Oracle Visual Builder Cloud Service – Version: 26.04.2 Exporting data from the database table as a…
OIC – Retry Mechanism
OIC – Retry Mechanism Introduction: Oracle Integration Cloud – Version: 24.3.1 Implementing Retry Mechanism in an OIC Integration Why we need to do: OIC…
Implementing Mouse-Centered Screen Zoom Using the Shift Key with JavaScript in Oracle APEX
Introduction Oracle APEX applications often display large amounts of data in Interactive Reports, Interactive Grids, dashboards, and forms. When users need to i…
Fixing FRM-92101 Error After Re-Enabling a Custom Concurrent Program in Oracle EBS
Introduction During a recent Oracle E-Business Suite (EBS) maintenance activity, we needed to re-enable a custom concurrent program that had been disabled for a…
Oracle Fusion Financials 26B – GL AI Agent Setup Guidance
GL_AI_Agent_26B_Blog Oracle Fusion Financials 26B – GL AI Agent Setup Guidance Introduction / Issue: For Oracle Financials consultants planning to enable and ru…
Create a Dynamic List Item in Oracle Form
Procedure :- “ADD_LIST_ELEMENT‘” Using the following procedure Syntax ADD_LIST_ELEMENT(list_id ITEM, list_index VARCHAR2, list_label VARCHAR2, list_value VARCHA…
Create a Clock Timer’s in Oracle Form
Create a Timer: Declare a variable of timer data type. Initialize a variable with one second number data type. Use a CREATE_TIMER built-in with the timer’s name…
Oracle OIC Solution for Full and Partial Shipment Automation
Introduction: In Oracle Order Management, We have two Shipping methods post Sales Order creation, Full Shipment and Partial Shipment. They are carried out by sp…
How a Redwood Page differs from a Classic ADF page
Introduction: Redwood Pages and standard ADF pages represent two different approaches to building user interfaces within Oracle applications. While standard ADF…
Strengthening OCI Access Control with IAM Deny Policies
Oracle Cloud Infrastructure’s Identity and Access Management (IAM) service now supports explicit deny policies, providing administrators with the ability to exp…
Exporting Plan Data in Oracle Fusion Supply Chain Planning
Introduction/ Issue: In Oracle Fusion Supply Chain Planning, planners often need to extract plan measure data from demand or supply plan layouts for reporting a…
Oracle ERP Cloud Period Close Procedures & Runbook Considerations
Introduction/ Issue: Oracle ERP Cloud customers have a need to close their books every period, quarter and/or year. This paper provides guidance on the period-e…
Oracle Forms Personalization – Validating a Record Input
Introduction: The waybill number is a key reference for tracking shipments. To avoid errors and maintain consistency, the field will now be restricted to accept…
How to Customize the Purchase Agreement Page in Oracle Redwood UI
Introduction/ Issue: Oracle Cloud Redwood UI brings a modern interface, but certain key calculations like the “Total Amount” (sum of Released Amount and Agreed…
Frozen Inventory Value Report–All Types in Oracle Apps R12.
Introduction: This blog has the SQL query and View query that can be used for Frozen Inventory Value Report–All Types in Oracle Apps R12. Cause of the issue: Bu…
OAuth 2.0 Setup from OCI
Introduction: Step by step process of creating OAuth 2.0 instance from OCI. Why we need to do: OAuth 2.0 is essentially a secure permission-granting fram…
Personalization Migration: ADF to Redwood Page
Introduction: Migrating personalization from ADF page (Classic UI) to Redwood UI Why we need to do: Oracle is modernizing its Fusion Applications UI/UX,…
AR Transaction Type Mapping from Sales Orders
Issue: The AR transaction types are being wrongly picked up from the Sales order. Case 1: Sales order type “SAMPLE-TOYS” should generate AR transactions with In…
Procedure to create Quality Test & Quality Test Values in OPM through API
Introduction: This blog has the PLSQL query that can be used to create Quality Test & Quality Test Values using oracle seeded API. Cause of…
Streamlining Oracle EBS Application Tier Patching with ETPAT-AT
Patching Oracle E-Business Suite (EBS) application tier technology components has always been a delicate balance of precision, downtime planning, and repeatabil…
ERP Cloud Financials Fusion – Cash Management Auto Reconciliation
ERP Cloud Financials Fusion – Cash Management Auto Reconciliation Introduction Bank Statement Reconciliation – Match Statement Lines and Transactions – Automati…
Oracle PLSQL to Generate XML Tag Using Standard Functionality
declare l_ctx dbms_xmlquery.ctxHandle; l_clob clob; begin l_ctx := dbms_xmlquery.newContext(‘select * from <TABLE_NAME>’); dbms_lob.createtemporary(:g_clob,true…
Oracle R12 SQL Query – Price List or Fee Schedule for Customer
select HP.PARTY_NAME “Customer Name”,HCAA.ACCOUNT_NUMBER,HCAA.ACCOUNT_NAME,qph.name “Price List Name”,HCSUA.SITE_USE_CODE,HCSUA.LOCATION from qp_list_headers qp…
Blob File Move From DB to Directory
Introduction/ Issue: CSV blob file to raw file move from database table blob column to database directory. Why we need to do / Cause of the issue: We can move f…
File Store From Client to DB
Introduction/ Issue: CSV file move from client local machine to database table blob column. Why we need to do / Cause of the issue: We can store the CSV file fr…
Splitting shipping lines
Introduction: Splitting of order lines, this can be done during shipping last part of sales order, generally we come across this scenario, when we want to ship…
Multi-Node EBS with DMZ Setup
Many of you have asked about the best storage options for a multi-node EBS setup involving a DMZ server with a public load balancer. Here are some insights and…
View Output/Log returns “Authentication Failed/File server could not identify source file ” error Concurrent Program – R12.1.3
In Oracle E-Business Suite R12.1.3, when you try to view a report output/log, the following two errors are displayed: File server could not identify source file…
Ship Confirm of multiple deliveries Using API in EBS
PROCEDURE XDM_SHIP_CONFIRM(p_delivery_id_s NUMBER, p_shipping_method VARCHAR2, p_trip_id number, p_close_trip_flag VARCHAR2)IS–Standard Parameters.p_api_version…
How Model-Based Testing is Transforming Oracle Application Testing?
As enterprise IT evolves, software environments are becoming increasingly complex. Since businesses increasingly rely on hybrid setups with a mix of on-premise…
Taming the Cloud Beast: Predictable Budgeting and Cost Optimization for Your Cloud Migration
Migrating to the cloud promises scalability, flexibility, and innovation. But the “cloud beast” of unpredictable costs can quickly derail those benefits if not…
The Never-Ending Upgrade Cycle: How to Keep Up Without Losing Your Mind (and Your Productivity)
Let’s be honest: the constant cycle of software and system upgrades can feel like a never-ending treadmill. Just when you think you’ve caught your breath after…
Why Oracle Testing Automation is Essential for Oracle Applications in Today’s Cloud Environment
Insights from Oracular Businesses using Oracle—whether on-premise, in the cloud, or both—face constant pressure to adapt. Quarterly Oracle updates require compa…
Escheatment of Unclaimed Checks
Escheatment of Unclaimed Checks Overview of Escheatment of Unclaimed Checks Escheatment is the process of transferring abandoned or unclaimed property to the st…
Concurrent Program Failure with Error “FDPSTP failed due to ORA-20100”
User Submitted concurrent Program “XXFM TBI DEBIT CONTROL” in DEV environment after Clone. Program failure happening with Below Error. Oracle Database 19…
Shipment Tracking Number Update using API
procedure xxdm_upd_ship_num_in_orcl(p_delivery_detail_id NUMBER, p_thirsd_party_tracking_number VARCHAR2) is l_index NUMBER; l_msg_return NUMBER; x_return_statu…
No Revaluate Balance option for Legal entity when user trying to run the Revaluation in Month End
10712_shaik-mohammed-shafi_revaluation-issue
Issue with the Oracle Fusion General Ledger where the Journals are not posted and in unposted status.
10712_shaik-mohammed-shafi
Cancelling AP invoice fix
cancelling-invoice-issue-fix-1
Oracle Fusion query to get the Tax_Reporting_Type_Code_For_a_Tax_Regime
Introduction: This SQL query is fetching the data of Tax reporting type code details by passing the tax regime. Cause of the issue: To find if any of the tax re…
Oracle Fusion India_Customer_Trx_GST_Details_Query
Introduction: This SQL query is fetching the data of India customer’s transaction with the company and the customer GST details. Cause of the issue: To find out…
MPS/MRP planning attributes
MPS/MRP planning attributes Navigation path: Inventory>Items>Master Items>General Planning (T) Refer to the table below for the setup of each attribute. …
ASL Setups
Setup supply chain For more information on supplier base setups, please refer to the Purchasing Training Manual. Approved Supplier List Approved supplier list i…
Restricting Self-Approval in Oracle Fusion
Introduction: Addressing the self-approval concern for expenses and other documents after setting up a vacation rule in Oracle Fusion. You can prevent self-appr…
Updating bulk records pending under Inventory Transactions using Oracle Visual Builder Add-in
Introduction: The Oracle Visual Builder Add-in for Excel connects Excel spreadsheets with REST services, enabling you to retrieve, analyze, and edit business da…
Managing EBS Concurrent Processing and Cleaning concurrent processing tables
Managing Oracle E-Business Suite Concurrent Processing and Cleaning concurrent processing tables Introduction: Oracle E-Business Suite (EBS) is a comprehensive…
Edition-Based Redefinition[EBR] –Granualar access in EBS R12
Introduction: Edition-Based Redefinition is an essential feature in Oracle, designed for oracle database administrators, developers who manage application upgra…
Overview of Block Volume in Oracle Cloud Infrastructure
INTRODUCTION: In today’s world, businesses create and uses a huge amount of data, so they need storage solutions that are both efficient and able to grow as nee…
Comparison Between File Storage and Block Volume in OCI
Introduction: In cloud computing, choosing the right storage solution is important to ensure good performance, manage costs, and handle data efficiently. Oracle…
Unable to apply the receipts getting Error ACC DATE NOT IN OPEN PD
Unable to apply the receipts getting Error ACC DATE NOT IN OPEN PD Error message:- This error appears when Accounting Periods are not open in Payables Please ma…
R12 Oracle General Ledger Setup
r12-oracle-general-ledger-setup
Oracle Internet Of Things (IoT)
Dear Readers I would like to explain more about the Oracle IoT Cloud services. Will provide continues blogs on this technology. In this block we will explore wh…
Query to find out the Request Set of a Program and its details in EBS R12
Objective The intent of this document is to find out the request set name of given concurrent Program and the details of programs attached in the Request Set. W…
Query to find out the Receiving Transaction Interface error details in EBS R12
Objective The intent of this document is to find out the error’s details when standard Program not able to process the records in interface table (RCV_TRANSACTI…
Auditing in Oracle E-Business Suite
Navigating Auditing in Oracle E-Business Suite: Introduction: Oracle E-Business Suite (EBS) is a powerful enterprise resource planning (ERP) solut…
Active EBS users and their respective responsibilities in Oracle E-Business Suite
Every time a customer or client asks for a list of EBS Users and their responsibilities, the DBA team would generally execute the “Active Users” Concurrent Prog…
Enhancing Log and Output File Management in Oracle E-Business Suite 12.2
Introduction: Effectively managing large log and output files is crucial for maintaining optimal performance in Oracle E-Business Suite 12.2. Despite the availa…
Identify versions of components in EBS
Useful commands to find the versions of various components used in Oracle E-Business Suite Find Apache Version or Oracle Application server version in R12 Log i…
R12 – Accounting Setup Manager Checklist
r12_setup_check_list Accounting Setup Manager Checklist The following table describes the steps to create accounting setups using Accounting Setup Manager. Each…
Back Date Sales order creation
Back Date Sales Order Creation, we generally come across this case during period closure, post period closure and starting of new month, we need to book AR invo…
Splitting of Shipment Lines
Splitting of order lines, this can be done during shipping last part of sales order, generally we come across this scenario, when we want to ship partial quanti…
AGENT PATCHING from OEM Console
INTRODUCTION :- In this blog post, we will discuss Agent Patching briefly. While we typically perform patching from the backend, using the OEM Console for patch…
Installation of E-Business Suite Support Analyzer Bundle Menu Tool (DOC1939637.1)
Introduction:– EBS Support Analyzers are designed by Oracle Support to proactively assist with diagnosing issues and maintaining Oracle Application environments…
How to apply java certificate to jar files in Oracle E-Business suite – Step by Step
How to apply java certificate to jar files in Oracle E-Business suite – Step by Step Step1: Bringdown app services Step2: Backup below files: cp $NE_BASE/EBSapp…
TXK Delta 13 failed due to missing files
TXK Delta 13(32392507) plus critical TXK patches 33550674, 33535778 failed because of missing files adsign.txt and adkeystore.dat under $APPL_TOP_NE/ad/admin Th…
Adop Phase=cutover Failing With AutoPatch – AidafoGetFileDbVersion: INFO: ORA-01403: No Data Found
AutoPatch – aidafoGetFileDbVersion: INFO: ORA-01403: no data found when apply adop phase=cutover failing with AutoPatch Please follow below steps to overcome th…
Deep Dive – Data Dictionary Views
Introduction:- The current topic should be very familiar to the freshers and DBA’S as we know earlier. In general, The data dictionary is structured in tables a…
Role of Global Names in Database Links
ROLE OF GLOBAL NAMES IN DATABASE LINKS Introduction:- This is mainly for Oracle DBA’S who are widely seen about Database links in their organizations, Let us un…
How to run SQL tuning advisor manually in Oracle Database
Step 1: Get the hash value of the SQL using the SQL ID. Enter the SQL ID select a.plan_hash_value from v$sql a where sql_id=’&a’; Step 2: Create a tuning…
EBS Workflow Mailer Command Line Java Diagnostic Tests for Office365,GMail,On-Premises Email Servers
EBS Workflow Mailer Command Line Java Diagnostic Tests for Office365,GMail,On-Premises Email Servers Very often in Workflow service requests, one will be reques…
E Business Suite AccessGate Redirect To Non SSL URL’s in External Tier
Oracle E-Business Suite 12.1.3 Access Manager, Access Manager issues Actual Behavior In E-Business Suite integrated with OAM, accessing iSupplier will redirect…
SYS and SYSTEM Password no longer needed in E-Business Suite
Introduction: R12.2 is being modernized with many new functionalities and changes in architecture. We, as a DBA, rely on SYS and SYSTEM passwords for any activi…
fs_clone fails with ETCC not run in the database node
Introduction: This blog discusses about ADOP fs_clone error in E-Business suite. The intended audience of this blog are E-Business suite DBA’s On running fs_clo…
ADOP Prepare fails with ORA-20001
Introduction: This blog discusses about ADOP error in E-Business suite. The intended audience of this blog are E-Business suite DBA’s While running ADOP…
Failure of Web Server bridge: No backend server available for connection: timed out after 10 seconds or idempotent set to OFF or method not idempotent.
ERROR ———————– Failure of Web Server bridge: No backend server available for connection: timed out after 10 seconds or idempotent set to OFF or method not idemp…
APP-AR-11526: ORA-00054: resource busy and acquire with NOWAIT specified or timeout
The issue reproduced when AutoInvoice is run in parallel by 2 different users and from 2 different machines in apps R12.1.3. IMPACT: Due to this issue, users ca…
Resolving autoconfig issue with patch file system “adgendbc.sh INSTE8_SETUP”
Problem: In the execution of autoconfig in the patch file system. we faced the following error. [SETUP PHASE] AutoConfig could not successfully execute the foll…
Controlling Diagnostic and Tuning Pack
In this blog, we will see what controls Automatic Workload Repository in Oracle. For those who arrived to this blog and new, you will need to know more about Co…
Increase oacore process in Oracle EBS 12.1.3
How to increase oacore/jvm processes in R12.1.3 Below are the steps to increase oacore process: STEP1: Take a backup of context file and opmn.xml file. a. /u01/…
After upgrade from 11.5.10.2 to 12.2.5, Cannot compile invalids
Scenario: After upgrade from 11.5.10.2 to 12.2.5, Cannot compile invalids Error: ORA-00980: synonym translation is no longer valid Cause : The files in patch 18…
OEM 13c is not reflecting the correct data from all DB nodes after DB 19c upgrade
Scenario: After upgrading the database to 19c, The existing OEM 13c is not reflecting the correct data for all nodes. Solution: Check the OMS agents are updated…
ORACLE EBS 12.1.3 JDBC CONNECTION LEAK ISSUE
Issue: This homepage feature got a new bug with 12.1.3. This bug only affects EBS 12.1.2 and 12.1.3 customers who have OAMainMenuRESTHandler.class version 120.0…
12.2 E-Business Suite FS_CLONE / ADPRECLONE.PL Fails With Error ‘CLONE-20435 Some of the Oracle Homes are excluded during copy operation’
ERROR: SEVERE : Sep 8, 2021 16:51:36 – ERROR – CLONE-20435 Some of the Oracle Homes are excluded during copy operation. SEVERE : Sep 8, 2021 16:51:36 – CAUSE –…
Item Ranging – Oracle Retail Merchandising System Module
Item Maintenance RMS is responsible for the creation and maintenance of all items. RMS uses a flexible data hierarchy for an item, with levels that allow…
Employee As Supplier creation -page error
Employee as Supplier creation page error-Alternative Solution proposed Follow this process for first time creation for employee as supplier Enter expense report…
Data Migration and Conversion Strategy
Data migrations generally result from the introduction of a new system. This may involve an application migration or consolidation in which one or more legacy s…
How to Change the Default Request Group in E-Business Suite Support Analyzer Bundle Menu
It is possible to change the default request group for a given Analyzer at the individual analyzer level. 1.Start the menu with “perl Menu.pl” 2,Select a Produc…
ORA-29532: Java call terminated by uncaught Java exception: java.lang.OutOfMemoryError – ecx_utils.INITIALIZE
When trying to submit one of GL report end with error. The error message shows ORA-29532: Java call terminated by uncaught Java exception. select * from *******…
Timeout Error Encountered for pasteBinary.sh While Running “adop phase=fs_clone”
when attempting to execute “adop phase=fs_clone”, the following error occurs: # Calling ApplyAppsTier… # Creating the FMW Home from ApplyAppsTier Creating FMW H…
Query To Check Concurrent Program Run History in EBS
The following query will help you to find the history of concurrent programs run in your EBS environment. You should connect to ‘apps’ user for executing this q…
Discoverer Java connecting screen become hang or Blank
Environment: E-Business Suite R12.1.3 Discoverer – 11.1.1.6.0 and JRE1.7 Issue: Connecting To Discoverer, EBS Forms become hang or Blank, While check the log fi…
Create Oracle User Login Account Using API
Introduction:- Oracle has a seeded API fnd_user_pkg which has a procedure to create new user login account. Below is the sample script to create user account, c…
Oracle API Script to Copy Responsibilities of one user account to another user account
Introduction:- fnd_user_pkg.addresp is an Oracle Seeded API to add responsibilities from one user account to another user account.Below is the script to copy re…
Hold Pending Concurrent Requests and release them
Here is an easy way to put all the Pending Concurrent requests in hold, if we are going through any situation(Mostly any Production Cutover/Go live scenarios).…
Oracle R12 Query To Find Customer Notes
SELECT hca.account_number, ( SELECT hp.party_name FROM apps.hz_parties hp WHERE hp.party_id = hca.party_id ) customer_name, jnb.creation_date notes_creation_dat…
Oracle R12 GL To AR Transaction Details
SELECT DISTINCT –gcck.concatenated_segments, gcck.segment1 company, gcck.segment2 location, gcck.segment3 product, gcck.segment4 function, gcck.segment5 account…
Oracle SQL Query To Return Negative Numbers Within Bracket
Syntax: to_char(-123456,’99G99G99G999PR’) Query: select to_char(-123456,’99G99G99G999PR’) from dual; Output: <1,23,456>
How to reslove “adadmprf.sh INSTE8_PRF”
Problem: Adcfgclone got complete but at the end it failed for autoconfig with the below error. Error : WARNING: [AutoConfig Error Report] The following report l…
“Output Post Processor” Concurrent Manager not able to start R12.1
Cause: Unable to initialize state monitor. oracle.apps.fnd.cp.gsm.GenCartCommException: ORA-01422: exact fetch returns more than requested number of rows ORA-06…
adop phase=fs_clone [ERROR]: At least one Oracle inventory check has failed.Provide the location of a valid inventory file.
Cause: $ adop phase=fs_clone [ERROR]: At least one Oracle inventory check has failed.Provide the location of a valid inventory file. Solution : Check adop log u…
txkISGConfigurator.xml Fails with Error PatchContextFile Doesn’t Exist
Cause: BUILD FAILED /u01/app/122/fs1/EBSapps/comn/java/classes/oracle/apps/fnd/txk/util/txkISGConfigurator.xml:1756: ${patchContextFile} doesn’t exist ! The iss…
How to Change WLS admin password if we forget.
IF YOU DON’T KNOW CURRENT PASSWORD ———————————– If you forget your administrative password and cannot start the server, the following procedure works for the de…
Seed Data Table has not been prepared for patching at “APPS.WF_EVENTS+”
When applying ADOP patch, we getting below error like “APPS.WF_EVENTS+” ORA-20002: Seed Data Table has not been prepared for patching ORA-06512: at “APPS.WF_EVE…
Keynotes before upgrade the Oracle EBS to 19c database
Container Database (CDB) Architecture: With the Database 19c certification, EBS 12.2 on-premises databases are now certified with the CDB architecture (multiten…
How to create or rebuild Context file in database DB Tier on R12.2
1. Find out the Template file. [oracle@ebs bin]$ cd $ORACLE_HOME [oracle@ebs 12.1.0]$ cd appsutil/template/ [oracle@ebs template]$ pwd /u01/StageR122/PROD/12.1.…
Gather Schema Statistics fails with ORA-20005
Gather Schema Statistics fails with “ORA-20005: Object Statistics Are Locked (Stattype = ALL)” Log file shows the following error, ORACLE error 20005 in FDPSTP…
ORA-39510 When Startup 12.2.0.1 Database From Sqlplus Using Pfile
When trying to startup [nomount] an instance in 12.2.0.1.0 from sqlplus by using a pfile, (startup nomount pfile=’my_pfile’) the following messages are displaye…
There are no active Responsibilities for the use – Oracle EBS 12.1
ISSUE: User was unable to view the responsibilities when logged in. SOLUTION: 1.Check the responsibilities of the user. 2.If the responsibilities are not end da…
12.2 ADOP Prepare Phase Issue : prepare failed with “Use of uninitialized value $result in split” in txkADOPValidations.error
12.2 ADOP Prepare Phase Fails with below error message, Error: Validating configuration on node: [apps]. Log: /u01/install/APPS/fs_ne/EBSapps/log/adop/87/202108…
[ERROR]: The value of s_patch_service_name is not set correctly in atleast one of the context files.
Cause : Query the below parameter in db context file. grep s_patch_service entry $CONTEXT_FILE (This is was not present in my context file, thus the error was t…
adop phase=fs_clone [ERROR]: At least one Oracle inventory check has failed.Provide the location of a valid inventory file.
$adop phase=fs_clone [ERROR]: At least one Oracle inventory check has failed.Provide the location of a valid inventory file. Solution : $cd /oracle/PROD/apps/fs…
Useful CRSCTL Commands
STOP & START CRS: ( run from root user) $GRID_HOME/bin/crsctl stop crs $GRID_HOME/bin/crsctl start crs Enable/Disable auto restart of CRS. $GRID_HOME/bin/crsctl…
How to fix INVALID status for RAC component in dba_registry
Before upgrade or after upgrade, database may show INVALID status for component Real Application Clusters in dba_registry as below . SQL> select comp_nam…
adop prepare phase failing with the error [UNEXPECTED]Nodes are listed in the ADOP_VALID_NODES table but not in the FND_NODES table.
In this post we will fix the error ‘[UNEXPECTED]Nodes are listed in the ADOP_VALID_NODES table but not in the FND_NODES table’. [applmgr@ebstest ~]$ adop phase=…
How to execute empty patching cycle without applying any patch.
n: 12.2 version, we will execute the emptly patching cycle. Can a cutover be run, from fs1 to fs2 outside of patching cycle? For testing purposes, withou…
txkGenADOPWrapper.pl line 143 ) ERRORCODE = 1 ERRORCODE_END WARNING: [AutoConfig Error Report]
txkGenADOPWrapper.pl line 143 ) ERRORCODE = 1 ERRORCODE_END WARNING: [AutoConfig Error Report] The following report lists errors AutoConfig encountered during e…
How to configure patch file system in R12.2
To re-create or configure the patch file system in ebs. Follow the below step’s to configure the patch file system in R12.2. 1. Run adpreclone.pl on run file sy…
ORA-20100: Error: FND_FILE failure. Unable to create file in the directory
Problem details: ————— The Concurrent program is getting completed successfully, able to view output & logfile. But in the log file, it shows below error. Oracl…
upload of Context file and templates to database…ERROR: InDbCtxFile.uploadCtx() : Exception : Error executng BEGIN fnd_gsm_util.upload_context_file(:1,:2,:3,:4,:5); END;: 1; Oracle error -4063: ORA-04063: package body “APPS.FND_GLOBAL” has errors has been detected in FND_GSM_UTIL.upload_context_file.
Error: upload of Context file and templates to database…ERROR: InDbCtxFile.uploadCtx() : Exception : Error executng BEGIN fnd_gsm_util.upload_context_file(:1,:2…
shell script to compile multiple form files in oracle apps
The Below script useful to compile multiple .fmb files from single script. SCRIPT: vi fmcmp.sh #!/bin/bash . APPS_XXXX.env LOGFILE=/d01/backup/fmcmp_`date +%d%b…
How to Create the boot.properties in IDCS Asserter
Problem: When you are running the weblogic in production mode and when you try to start the weblogic using nohup then there is no chance to enter the username a…
TNS-01151: Missing listener name, APPS_XXX, in LISTENER.ORA
Problem Description: In Apps R12.1.3 cloning perl adcfgclone.pl appsTier configuration successfully completed and all the services started except listener servi…
Apps listener fails to start on cloned instance with TNS-12533
Apps listener fails to start on cloned R12.1.3 instance with the following error. Cause: This error occurs when a host names in the tcp.invited_nodes list is in…
Customer LOGO in R12 Login Page(ORACLE EBS)
INTRODUTION The below steps will demonstrate how to add the client organization logo in R12 login page. STEP 1:Get the Logo from the customer. STEP2:Login as sy…
FS_CLONE Fails with no such files or Directory ADOPValidationUtils.pm
When you run adop phase=fs_clone, this will endup with the error no such files or Directory ADOPValidationUtils.pm in a minute or so, this is due to the patch c…
Output Post Processor (FNDOPP) is not Staring
The Output Post Processor (FNDOPP) Manager is not starting, process status is shown as Actual with 0 and the Target is 1 The OPP logfile shows the following exc…
Setting A Discoverer Preference At A User Level
For Discoverer 10g: 1. On the Discoverer 10g Server machine, please set the environment variables by running the following script, . ./$OH/discoverer/discwb.sh…
Log file locations in oracle EBS 12.1.3 and 12.2.4
Log file locations in oracle EBS 12.1.3 and 12.2.4 The Log files locations in Oracle EBS Release 12.1.3 and Oracle EBS R 12.2.4 are given below: 1.Instance star…
R12.2 ONLINE PATCHING (ADOP) PARAMETERS/COMMANDS
R12.2 ONLINE PATCHING (ADOP) PARAMETERS/COMMANDS Online patching is the most important new feature in Oracle E-Business Suite Release 12.2. It is the ability to…
How to Cancel the Request from the backend if frontend not helping to Cancel.
Problem: when we try to cancel a concurrent request using frontend “Cancel Request” button from the Administer > Concurrent > Manager form. We are getting the f…
ADOP ERROR with Following “package body “APPS.AD_ZD_ADOP” has errors (DBD ERROR: OCIStmtExecute)”
Problem: [ERROR] Failed to execute SQL statement: select AD_ZD_ADOP.GET_INVALID_NODES() from dual [ERROR] Error Message: [ERROR] ORA-04063: package body “APPS.A…
[Security:090295]caught unexpected exception, No such object in Weblogic Remote managed Server.
Problem: <[Security:090295]caught unexpected exception, No such object> <[Security:090303]Authentication Failed: User weblogic weblogic.security.providers.authe…
APP-FND-01542: This Applications Server is not authorized to access this database.
By default in Oracle Applications R12, users are not able to access Forms directly via the following url: http://hostname:port/forms/frmservlet The following er…
Uninstalling Oracle Enterprise Command Center Framework in R12.2.8
To uninstall Oracle Enterprise Command Center Framework components, perform the following steps. Stop all the Oracle Enterprise Command Center Framework compone…
Installing Oracle Enterprise Command Center Framework, Release 12.2.8
Installing Oracle Enterprise Command Center Framework, Release 12.2.8 Oracle Database version: 12.2.0.2 Oracle EBS version : 12.2.8 Oracle Enterprise Command Ce…
Invalid objects due to multiple database editions
There are around 1000 plus invalid objects, while looking further observed that this object do not exist in current run edition and there are duplicate/multiple…
Oracle Invisible Columns
Oracle allows columns to be hidden from application. Columns can be made invisible in the CREATE TABLE statement or using an ALTER TABLE statement.By default co…
How to check Oracle Application Modules Licensed or Not
Below script to find Oracle Application Modules are Licensed or Not. Script: SELECT fat.application_name ,fa.application_id ,fpi.patch_level ,decode(fpi.STATUS,…
How to find the OPP log file location in oracle application 11i/R12.1 and R12.2
Below script to find the OPP log file location in oracle E-Business Suite. Script: SELECT fcpp.concurrent_request_id req_id, fcp.node_name, fcp.logfile_name FRO…
What is Edition Based Redefinition (EBR) in Oracle?
Edition Based Redefinition (EBR) is a new feature introduced from Oracle Software Version 11g R2.Edition-based redefinition allows multiple versions of PL/SQL o…
Finding the Weblogic URL and Port from R12.2 Context File
We can find the weblogic URL and port from the CONTEXT_FILE by using the below variable. Steps: 1. Go to Application Tier 2. Set the RUN file system environment…
Oracle data fix remove unposted receipts and Full Merge events
Oracle data fix remove unposted receipts and Full Merge events We have follow the below script. Step:1 Create back up table Create table scratch.A…
utlrp hangs after upgrade to 12c ERP database
We were performing database upgrade from oracle 11g to oracle 12c with Oracle Apps 12.1.3. We faced an issue where database upgrade took < 30 mins. However, it…
SOA webservices not working after 12c ERP db upgrade
After upgrading database from oracle 11g to 12c in Oracle Apps R12.1.3, got a strange issue where SOA webservices- SOAP, REST stopped. The following url showed…
Oracle apps adpatch fails
Many times , when applying patch using adpatch in oracle apps, the patch session session gets terminated because of network problems. The best way to continue t…
FRM-92102 Errors when Accessing a Forms Module
When attempting to access E-Business Suite Forms on a cloned E-Business Suite instance the following error occurs: FRM-92102 A network Error has occurred. The F…
Sometimes the Formsapp.ear deployment fails on Application startup after applying Patches
Error while executing the perl script txkChkFormsDeployment.pl We have determined that you need to redeploy Forms (using txkChkFormsDeployment.pl). But could no…
To Re-build database tier context file in r12.2
adbldxml.pl perl command used to re-create database tier context file. To re-create database tier context file follow the below steps. STEPS : 1. Run adbldxml.p…
BI Publisher program failed due to opp
Error: “Post-processing of request 4667897 failed at 30-Apr-2021 15:54:50 with the error message: One or more post-processing actions failed. Consult the OPP se…
Step by step Creating a Custom Application top for EBS 12.2 Using AD Splice
Introduction: This post is to guide to Create a Custom Application top for EBS 12.2 Using AD Splice. Steps to create a Custom Application top for EBS 12.2 Using…
ADOP Cutover failed while running txkADOPCutOverPhaseCtrlScript.pl
Issue: During an adop online patching cycle, cutover phase failed while running the script txkADOPCutOverPhaseCtrlScript.pl [PROCEDURE] Starting Middle Tier Ser…
UPGRADE JDK 1.6 to 1.7 IN ORACLE EBS12.1.3
UPGRADING JDK FROM 1.6 to 1.7 OF ORACLE HOME 10.1.3 AND 10.1.2 IN ORACLE EBS 12.1.3. STEP1: Download certified Java from MOS follow the DOC ID:143…
Oracle ETRM
The Oracle E-Business Suite Electronic Technical Reference Manual (eTRM) is a repository of database design and dependency information for Oracle E-Business Sui…
Useful TFACTL Commands
1. Check tfactl status with version: tfactl status 2. Check tfactl tool status: tfactl toolstatus 3. Get config details: tfactl print config 4. List of user hav…
PDB Lockdown Profiles In Oracle 12.2
ALTER LOCKDOWN PROFILE statement to alter a PDB lockdown profile. You can use PDB lockdown profiles in a multitenant environment to restrict user operations in…
SGA TARGET ADVISORY IN ORACLE
The V$SGA_TARGET_ADVICE view provides information that helps us in deciding optimal value for SGA_TARGET. MMON background process gather statistics about sga_ta…
The security certificate presented by this website was not issued by a trusted certificate authority
Issue : E-Business Suite 12.2 Applications Technology Stack, Secure Socket Layer Configuration issues . Users report receiving the below message when accessing…
Concurrent program assigned responsibilities
SELECT frt.responsibility_name, frg.request_group_name, frg.description FROM fnd_request_groups frg ,fnd_request_group_units frgu ,fnd_concurrent_programs fcp ,…
Could not initialize the Service Manager FNDSM__ Verify that has been registered for concurrent processing
Cause The issued was found to have been caused by the FND_NODES table having incorrect node entries (old node from the source instance) and that no Service Mana…
SQL Profiles & Baselines
To check SQL Profile : select NAME,SIGNATURE ,STATUS,FORCE_MATCHING from dba_sql_profiles; ENABLE/DISABLE/DROP EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE(‘coe_5273fz2c…
Scripts to identify performance related issues.
one script to Identify all performace issue related to sql query Set echo off set trimspool on set define on column filename new_val filename select to_char(sys…
Undo related queries
Undo Related Queries To check retention guarantee for undo tablespace select tablespace_name,status,contents,logging,retention from dba_tablespaces where tables…
Long running requests
Long running Concurrent Requests:- 1.How to Determine Which Manager Ran a Specific Concurrent Request? col USER_CONCURRENT_QUEUE_NAME for a100 select b.USER_CON…
Oracle R12 Customer TaxPayer ID Update API
/*Upload the customer data that needs to be updated in a temp table*/ CREATE TABLE scratch.RITM0669580 (account_number VARCHAR2(30), tax_payer_id VARCHAR2(20),…
Oracle R12 Customer Remittance Email Update API
DECLARE x_return_status VARCHAR2 (200) := NULL; x_msg_count NUMBER := 0; x_msg_data VARCHAR2 (200) := NULL; t_output VARCHAR2 (200) := NULL; t_msg_dummy VARCHAR…
Oracle R12 Customer Site Remittance Email Update API
DECLARE x_return_status VARCHAR2 (200) := NULL; x_msg_count NUMBER := 0; x_msg_data VARCHAR2 (200) := NULL; t_output VARCHAR2 (200) := NULL; t_msg_dummy VARCHAR…
Oracle R12 AP Invoice Extract With PO And CHECK Details
select aia.INVOICE_NUM ,aia.INVOICE_AMOUNT ,aia.CREATION_DATE,aia.INVOICE_DATE,aia.SOURCE,aia.INVOICE_TYPE_LOOKUP_CODE INVOICE_TYPE, (select aca.STATUS_LOOKUP_C…
Oracle R12 Expense Type Master Based On Operating Unit
SELECT ( SELECT hou.name FROM apps.hr_operating_units hou WHERE hou.organization_id = aerpa.org_id ) operating_unit, aerpa.prompt expense_type, aerpa.flex_descr…
Oracle R12 Item Master Extract With Other Essential Details
select (select category_concat_segs from apps.mtl_item_categories_v where inventory_item_id = msib.inventory_item_id and organization_id = msib.organization_id…
Oracle R12 GL Code Combination With Segment Description
select SEGMENT1 Company, apps.gl_flexfields_pkg.get_description_sql (chart_of_accounts_id,— chart of account id 1, —– Position of segment segment1 —- Segment va…
Oracle R12 Customer Extract Based On Operating Unit
SELECT TO_CHAR(hca.account_number) account_number, ( SELECT name FROM apps.hr_operating_units hou WHERE hou.organization_id = hcasa.org_id ) organization_name,…
Oracle R12 Supplier Master With All Supplier Details
select (SELECT hou.NAME FROM apps.hr_operating_units hou WHERE 1 = 1 AND hou.organization_id = aps.org_id ) ou_name, (SELECT paym.payment_method_code FROM APPS.…
Oracle R12 AR Invoice With Tax Lines and Tax Description Query
SELECT DISTINCT rcta.trx_number invoice_number, –gd.gl_date, hp.party_name customer_name, rctla.line_number, rctla.description, rctla.uom_code, rctla.quantity_i…
Oracle R12 Journal Details Query
SELECT gjst.user_je_source_name source, ( SELECT gjct.user_je_category_name FROM apps.gl_je_categories_tl gjct WHERE gjh.je_category = gjct.je_category_name AND…
Oracle R12 Internal Bank Account Query
SELECT hou.name operating_unit, xep.name legal_entity, cebb.bank_name, cebb.bank_number, cebb.bank_branch_name, cebb.bank_branch_number, cebb.bank_branch_type,…
Cancel approved requisition in ORACLE APPS
INTRODUCTION This blog explains how we cancel approved requisition in oracle apps PROCEDURE CANCEL_REQUISITION (P_BATCH_RUN_ID IN NUMBER,P_…
Validate Third Party Registration
INTRODUCTION This blog explains how we validate Third Party Registration in oracle apps –**************************************************…
Validate Bill to customer
INTRODUCTION This blog explains how we validate Bill to customer in oracle apps –****************************************************** — Bill To…
EBS R12.2 Log file locations
Category Location Start/Stop Logfiles $INST_TOP/logs/appl/admin/log Log files for OPMN and OHS processes $IAS_ORACLE_HOME/ instances//diagnostics/logs Weblogic…
R12.2 EBS Application Services
Group Services Scripts Purposes Root Services Node Manager adnodemgrctl.sh WLS Node Manager controls server instances within a domain providing automatic restar…
Difference between R12.1 & R12.2
Category 12.1 12.2 Forms & Reports, Developer Home, Tools Home 10.1.2 – ORACLE_HOME 10.1.2 ORACLE_HOME OC4J/OHS/OPMN 10.1.3 – IAS-ORACLE_HOME OPMN, oacore-oc4j,…
Clone 19c Oracle Home
a. Create a gold image of the 19c Oracle Home. $ cd $ORACLE_HOME/ $./runInstaller -createGoldImage -destinationLocation /u03/database19c -silent $./runInstaller…
Decrypting Web logic Password Oracle Apps R12.2
INTRODUCTION: The below steps shows how to decrypt the weblogic password for oracle Apps R12.2 STEP 1: Connect to instance as applmgr and source the ENV. su – a…
Running a Concurrent Program in Custom Manager in Oracle Apps R12.2
INTRODUCTION: The below steps shows us how to run a concurrent program in a custom manager, STEP 1: Navigate to System Administrator->Concurrent->Program->Defin…
Patch Analysis in Oracle Apps R12.1
INTRODUCTION The below steps shows us how to take patch analysis on oracle EBS R12.1. STEP 1: Download the Recommended Patch from MOS, move it to the server and…
OPMNCTL startall Fails with “unexpected exit: status 4200”
While trying to start the opmnctl services in idm we are getting 4200 error code as below applmgr@*****:~ $ applmgr@*****:~ $ /*****/fmw/idm/instances/IDM_inst1…
Adop Cutover Failed while starting Admin Server on Run File System
You can see the following error in an adop log file. [UNEXPECTED]Error occurred running “sh $INST_TOP/apps/TEST_tst/admin/scripts/adadminsrvctl.sh start -noprom…
txkADOPValidations.error while Running fs_clone
Fs_clone fails with the following error: Validating configuration on node: [tst-ebs021]. Log: /u01/app/ebstst/fs_ne/EBSapps/log/adop/129/20210205_003039/fs_clon…
ORACLE EBS 12.1.3 JDBC CONNECTION LOCK ISSUE
Issue: This homepage feature got a new bug with 12.1.3. This bug only affects EBS 12.1.2 and 12.1.3 customers who have OAMainMenuRESTHandler.class version 120.0…
ORA-06512: at “APPS.FND_CP_OPP_IPC” XML reports complete in warning
Description : The cloned instance and all managers and OPP services were up and running. However users were reporting of XML reports completed in Error. Issue L…
Concurrent Manager encountered an error while running Oracle*Report Program was terminated by signal 25.
Description : When attempting to run a concurrent program which is accessing file “reports.log” in directory $APPLCSF/$APPLLOG then following error occurs Error…
Regenerate the lost adapcctl.sh, adalnctl.sh, and adcmctl.sh scripts.
Description : If we lost adapcctl.sh, adalnctl.sh, and adcmctl.sh scripts. we can re-generate it, By running autoconfig. Solution : [applabc@apps scripts]$ ls -…
Purging Strategy for E-Business Suite
Purging Strategy for E-Business Suite Introduction: There is no single Archive/Purge routine that is called by all modules within E-Business Suite, instead each…
script to backup application home based on mount point space
This script is used to take backup of oracle application home in specific mount point. First it will check the size of apps home and calculate the mount point f…
oracle ebs r12.1.1 installation on oracle cloud
ORACLE EBS 12.1.1 INSTALLATION STEPS:1 Login into the cloud Create Instance Main menuàcomputeà instanceàcreate instance Give name to the instance Choose availab…
script to monitor apps and db listener running on single node
This script is useful to frequently monitor db and apps listener status running on single node. Run this script in crontab to frequently monitor db&app listener…
script to alert mount point space status without mail alert
This script is used to check the mount points space status without mail alert. Save the mount points name in mn.log file before running this script. #!/bin/bash…
script to get concurrent request outfiles for list of concurrent request id.
This script is useful when the dev Team asks larger number of concurrent request outfiles frequently. Before running this script copy the required concurrent re…
Oracle Ebs R12.1 Post Install Check Error
This error occurred when we install Oracle EBS R12.1 on oracle cloud. CAUSES OF ISSUE The http port number and instance public ip is blocked by the vcn network.…
Purging of Old pending Notification and workflow data
SQL> select count(*),status from wf_notifications group by status; COUNT(*) STATUS ———- ——– 1530 CANCELED 1627 CLOSED 15266 OPEN SQL> select count(*),status, MA…
How to find log files locations in 11i and R12
Alert Log File location: $ORACLE_HOME/admin/$CONTEXT_NAME/bdump/alert_$SID.log Trace file location: $ORACLE_HOME/admin/SID_Hostname/udump Application Tier Logs…
Find the current patchset levels for products in Oracle Apps
To obtain the current patchset levels for each specific E-Business Suite functional product test the following steps: 1. Log into OAM. (Responsibility System Ad…
How to Restart a Crashed ADOP session
If an adop session is running and system crashes then it fails to update the status in the internal repository If we try to run it adop again it will give the f…
Prepare phase failed due to GET_ACTIVE_CP_REQ_ID(c_req_id) error
Prepare phase failed due to GET_ACTIVE_CP_REQ_ID(c_req_id) error While running adop=prepare phase recieved error LOG FILE ———————– Filename =adop_20200822_14041…
fs_clone failed while running txkADOPPreparePhaseSynchronize.pl
fs_clone failed :[UNEXPECTED]Error occurred running”perl/oradata/applmgr/fs1/EBSapps/appl/ad/12.0.0/patch/115/bin/txkADOPPreparePhaseSynchronize.pl When attempt…
Oracleasm lib commands
ASMLIB is a storage management interface. ASMLIB is not required for ASM to function; it is like an add-on library that simplifies the tasks of managing and dis…
All About Oracle DB Audit
The auditing mechanism for Oracle is extremely flexible. Oracle stores information that is relevant to auditing in its data dictionary. Every time when a user a…
ASMCMD useful Commands
Below are some of the useful ASM commands which will be helpful all the time. ASMCMD > showversion ASMCMD > showversion –releasepatch –softwarepatch ASMCMD > sh…
adcfgclone return code = .16.17.18.19.20.21
Where I Faced this Issue? When I ran adcfgclone appsTier on EBS 12.2 I got this error, and it said got error while running autoconfig, but the actual error is f…
Query to fetch on hand quantity of an item for historic date
Introduction This blog explains to get the On hand quantity if a item in inventory for a specific date #Code# SELECT SUM (TRANSACTION_QUANTITY) TARGET_QTY FROM…
Fusion – Global Human Resources Views
Global Human Resources Views EEC_CONTEST_TEMPLATE_HDR_VL EEC_METRICS_VL EEC_REWARDS_VL HCM_EXTENDED_LOOKUP_CODES_VL HR_ALL_ORGANIZATION_UNITS HR_ALL_ORGANIZATIO…
Fusion – Sample Payload For FindCustomerAccount Operation In TCA Account Web Service
SOLUTION Below are some sample payload for findCustomerAccountOperation: <soapenv:Envelope xmlns:soapenv=”http://schemas.xmlsoap.org/soap/envelope/” xmlns:typ=”…
Fusion – Update Supplier, Supplier Site, Supplier Contact Payload – Webservice
<soapenv:Envelope xmlns:soapenv=”http://schemas.xmlsoap.org/soap/envelope/” xmlns:typ=”http://xmlns.oracle.com/apps/prc/poz/suppliers/supplierServiceV2/types/”…
Fusion – Create Supplier, Supplier Site, Supplier Contact Payload – Webservice
<soapenv:Envelope xmlns:soapenv=”http://schemas.xmlsoap.org/soap/envelope/” xmlns:typ=”http://xmlns.oracle.com/apps/prc/poz/suppliers/supplierServiceV2/types/”…
Fusion – Sample Payload To Update Descriptive Flexfield On Internet Supplier Portal Invoice
Sample Payload to update AP Invoice Header level DFF attribute: Payload: <soapenv:Envelope xmlns:soapenv=”http://schemas.xmlsoap.org/soap/envelope/” xmlns:typ=”…
Open Account Balances Data Manager (XLATBDMG) / Accounts Payable Trial Balance (APTBRPT) Performance Issue in R12
The “Accounts Payable Trial Balance” / “Open Account Balances Data Manager” report having severe performance issues. The rebuild it self taken more than 4 hours…
R12.2: OUI CLI Cloning Failed While Running adcfgclone appstier dualfs With Registration Driver regclone.drv Missing.
Error: When attempting to run command adcfgclone.pl appsTier dualfs while cloning the following error occurs. Exception in getting inventory Loc : null StackTra…
R12.2 cloning process adcfgclone fails with error
Error: Below Oracle Homes are already registered in the global inventory: /u01/oracle/R122/fs1/FMW_Home/oracle_common /u01/oracle/R122/fs1/FMW_Home/webtier /u01…
adformsctl.sh: exiting with status 150
Problem: If adoacorectl.sh,adformsctl.sh,adoafmctl.sh not coming up after start the application services adoacorectl.sh: exiting with status 150 adformsctl.sh:…
ORA-20100: Error: FND_FILE failure. Unable to create file in the directory
Problem details: The Concurrent program is getting completed successfully, able to view output & logfile. But in the log file, it shows below error. Oracle erro…
Query to get price list details for an item
Introduction This blog explains how to get pricelist details for an item #Code# SELECT QLH_TL.NAME “List Price” ,QLH_TL.DESCRIPTION “Description” ,QLH_TL.VERSIO…
Concurrent request receiving Could Not Lock Request when canceling
After submitting concurrent request, the phase of the request is RUNNING and never completing. When we search for the database session, it is not having any ses…
FRM-41830 list of values contains no entries
ERROR: FRM-41830 FRM-41830 list of values contains no entries SOLUTION: I have changed the profile option for particular user at the site l…
resolving libdb.so.2: cannot open shared object file: No such file or directory issue
ERROR: libdb.so.2: cannot open shared object file: No such file or directory CAUSE: While creating a new clone with R12.1.3 when starting the Apache i get the e…
How to reset apps password
Introduction This blog explains how to reset the Oracle apps password from the backend. #Code# BEGIN FND_USER_PKG.UPDATEUSER (X_USER_NAME => ‘USERNAME’ , X_OWNE…
Mobile First strategy with Oracle CX Mobile App
Enterprise are emphasizing on building a culture of proactive sales with responsibility of identifying leads,new account openings and agility in closing deals.…
Query To get DFF Details
Introduction A flexfield is a customizable flexible data field that you can customize according to business needs without coding. Oracle Applications uses two t…
OAF Date Converter, Formatter and Validation – Reusable Methods
Various Date Converter, Formatter and Validations Reusable Methods in OAF /**=============================================== * Methods related to Date Operation…
Item Category Creation R12
Introduction An Item category is a logical classification of items that have similar properties. A category set is a distinct group scheme and consists of categ…
Oracle Cloud Guard
Oracle Cloud Guard, a new Oracle Cloud Infrastructure (OCI) service designed to maintain a strong security in OCI. Cloud Guard is just one of the new services t…
Create Requisition interface against the blanket purchase agreement for different Locations
AIM: Component is used for Create the Requisition against the Blanket Purchase Agreement For different Locations. SCRIPT: CREATE OR REPLACE PACKAGE BODY APPS.XX…
Setting NLS language in SQL developer
Sometimes we will observe HR Operating units table will not show any data in database. For this we need to do the set up in SQL Developer. Preferences –> NLS –>…
Special characters validation
For any field if you want throw any exception when entered any special characters. We need to use following pattern to find. Pattern p = Pattern.compile(“[^a-zA…
500 Internal error in OAF
Once any environment get refreshed in EBS, a new dbc file will be generated. Some times we will receive 500 internal error. To over come this we need to replace…
Displaying Number of characters entered in text field
In OAF page, if user want to know how many characters entered in message text input field. Below is the code we need to use in process request. String cu…
Calling External URL in a New window
Suppose if we want to call any external URL in OAF page. So you can call as like below. urlname = https://www.google.com/ pageContext.sendRedirect(urlname);
Global Temporary Table ( GTT) – key facts
The data in a GTT is written to the temporary tablespace, which is not directly protected by redo, so using a GTT improves performance by reducing redo generati…
Oracle 12c SQL Plan Directives – Disable | Enable | Use As Hint
What is it? In previous releases the database stored compilation and execution statistics in a shared sql area which is non persistent. Starting in 12c the data…
ORA-20005: object statistics are locked (stattype = ALL) – Solution
During tuning a query, I found one table has stale statistics. While running gather stats for that table, got below error. Let me demonstrate with a demo table:…
Ignore Duplicate Rows (ORA-00001) with the ignore_row_on_dupkey_index Hint
The simplest method is to add a hint to the query to overcome ORA-00001 Added in 11.2, the ignore_row_on_dupkey_index hint silently ignores duplicate values: in…
Create a Virtual Column:
A virtual column applies a function to a column in the table. The database only computes this at runtime. The value is not stored in the table. So instead of cr…
How to Fix ORA-01450: Maximum Key Length (6398) Exceeded Errors:
The smallest unit of data storage in Oracle Database is the block. This defaults to 8k (8,192 bytes). Each index entry must fit within one block. So the maximum…
EXPDP OF FND_LOBS
ISSUE: ORA-31693: Table data object “APPLSYS”.”FND_LOBS” failed to load/unload and is being skipped due to error: ORA-29913: error in executing ODCIEXTTABLEPOPU…
Concurrent program stuck in OPP
Issue: Concurrent program stuck in OPP Steps to solve the Issue: Query to check concurrent program stuck in OPP SELECT a.request_id, substr(e.user_concurrent_pr…
EBS Application patch
Introduction: EBS Application patch is applied to upgrade the version of application files Steps to apply Patch in EBS instance: Patch Upgrade in EBS instance:…
Node id does not exist for the current application server id
Description: After Clone the Oracle Application R12.1.3 and login the application this error occurs. Solution: Check the Server ID in fnd_node table . Select se…
Oracle Reports are Failing with ORA-00600: internal error code
Description: Customers run the Oracle Report are Failing with APPS.FND_CP_OPP_IPC ORA-00600: internal error code. This issue made low level business impact &nbs…
How to resolve ERROR: FRM-18108 while compiling the form in R12
FRM-18108: Failed to load the following objects. Source Module:APPSTAND Source Object: STANDARD_PC_AND_VA Source Module:APPSTAND Source Object: STANDARD_TOOLBAR…
Fetching particular concurrent request out file remotely
This script fetch & transfer the specific concurrent request log file to remote location. $ vi Connout.sh cat /dev/null > /usr/tmp/ftp.log cat /dev/null > /usr/…
ORA-12547: TNS :lost contact when try to connect to Oracle
ERROR: ORA-12547: TNS :lost contact when try to connect to Oracle There could be many reasons for the above error, but one of the common issue is due to incorre…
Get any Apps User Password from Backend
In this steps we can find any user name’s password in oracle apps. STEP 1: login to Database through Command Prompt #sqlplus / as sysdba STEP 2 : Create Functio…
11i: “APP-FND-01542” Error When Logging Directly Into Forms Via the f60cgi in 11.5.10
Applies to: Oracle E-Business Suite Technology Stack – Version 11.5.10.0 to 11.5.10.0 [Release 11.5] Information in this document applies to any platform. Check…
Adcfgclone AppsTier Failing During Clone Into When Source And Target Are On Same Host
Applies to: Oracle Applications Manager – Version 12.2.4 and later Information in this document applies to any platform. Symptoms E-Business Suite 12.2 Applicat…
ORA-01422: Exact Fetch Returns More Than Requested Number Of Rows (Doc ID 2212698.1)
Applies to: Oracle Applications Manager – Version 12.2.4 and later Information in this document applies to any platform. Symptoms On : 12.2.4 version, Rapidclon…
Unable To Login into EBS With SYSADMIN User After Migrating To New Machine
Applies to: Oracle Application Object Library – Version 12.1.1 to 12.1.3 [Release 12.1] Information in this document applies to any platform. Symptoms Unable to…
Reorganizing FND_LOBS table in Oracle EBS R12.1.3
Reorganizing FND_LOBS table in Oracle EBS R12.1.3 FND_LOBS is usually one of the top 10 table in an EBS environment. It stores all the attachments that have bee…
Adop failed in fs_clone phase “ERROR: Update Moveplan Fail”
Issue: On : 12.2.4 version, ISSUES WITH FUNCTIONALITY OF AD UTILITIES When attempting to run fs_clone after a successful clone the following error occurs. FSClo…
After 12.2 upgrade adop failed ORACLE error 6550 in FDPSTP Cause: FDPSTP
Issue: [STATEMENT] This request finished with an error and produced the following completion message: ORACLE error 6550 in FDPSTP Cause: FDPSTP failed due to OR…
Integration of IDCS with EBS Failed with “No resources currently available in pool to allocate to applications”
Issue: weblogic.jdbc.extensions.PoolLimitSQLException: weblogic.common.resourcepool.ResourceLimitException: No resources currently available in pool <POOL_NAME>…
Weblogic integration failed “when two Managed Server Is came Online Through The Load Balancer in IDCS”
Issue: Integration of IDCS with Weblogic (WLS) with 1 managed server <MANAGED_SERVERX> works fine. Created another managed server-2 with similar configurations…
Output post processor is running has not picked up this request
Issue: Concurrent Program failed:Output post processor is running has not picked up this request Concurrent request marked as warning. User not able to make pay…
FS_Clone failed with java.lang.OutOfMemoryError
Issue: Exception in thread “Thread-1″ java.lang.OutOfMemoryError: GC overhead limit exceeded at java.util.Arrays.copyOf(Arrays.java:2367) at java.lang.AbstractS…
Domain Controller Change – Authentication issues in EBS for one domain
One of the customer has multiple AD domains based on regions across the world. There was sudden authentication issues in one of the domain (one region) and none…
E-Business Suite: WLS (WebLogic Server) Utility
The EBS WLS Utility is a self-service health-check script that that helps customers to service the E-Business WLS environment. The utility is used to review WLS…
EBS Clone Log Parser Utility (Rapid Clone 11i,12.0, 12.1)
Oracle proactive services team has provided the tool for analyzing the clone log files in a readable html format. This is very helpful for the DBA’s to quickly…
E-Business Suite Patching Log Parser
Oracle proactive services team has provided the tool for analyzing the patch log files in a readable html format. This is very helpful for the DBA’s to quickly…
Steps to start Weblogic Admin server without password on Linux environment
For the Admin Server 1) Create a file called boot.properties in this directory: /d01/Middleware/user_projects/domains/domain/servers/AdminServer/security 2) Add…
Forms remain Active even after close in R12 after enabling Java Web Start
In Java web start applications can be launched independently of a browser since they do not rely on a browser plug-in. Users has to close the session properly u…
Supplier Site Import Using FBDI – Fusion Applications
This blog describes the step by step process of Supplier Site migration in Fusion Application using File Based Data Import. FBDI Template: SupplierSiteImportTem…
Supplier Address Import Using FBDI – Fusion Applications
This blog describes the step by step process of Supplier Address migration in Fusion Application using File Based Data Import. FBDI Template: SupplierAddressImp…
Supplier Import Using FBDI – Fusion Applications
This blog describes the step by step process of Supplier migration in Fusion Application using File Based Data Import. FBDI Template: SupplierImportTemplate.xls…
Guidelines for Securing the Network
Network security was improved by using client, listener, and network guidelines to ensure protection. Using SSL is an essential element in these lists, enabling…
Session hang while running adcfgclone.pl during application clone
Case: While doing adcfgclone.pl in appsTier, the session hang and no further output. Even after the restart of server, there is no further progress with adcfgcl…
Disable worklist on the R12 home page
When you login in R12, you could see the workflow notifications listed in the home page. This is an new feature in R12 so that users will get notified whenever…
EBS User session expires before Idle time parameter values.
In oracle EBS, user session expires even before the time reaches the value of Idle time profile option. For example, Whatever the timeout setting the user sessi…
How to find invalid objects created by patch using Oracle utility
Every DBA’s will generate pre and post invalid report before patching activity in oracle application environments. Comparing pre and post reports will give an i…
EBS Analyzer Bundle Menu – A must know for SYSADMIN/DBA’s
Most of the DBA’s know about many analyzers available in oracle support used for service requests in MOS. Most familiar analyzers for DBA’s are Workflow Analyze…
How to find latest recommended patches or patchset in E-business suite?
There are many ways to find latest recommended patch collection or patchset for particual modules in E-business suite. The best way for me is using patchsets.sh…
SSL vs TLS – Still Confusion?
If you are confused with SSL and TLS, this blog will help you to understand at higher level. Before knowing about SSL, lets know about what is SSL? Secure Socke…
How to upload Large files in Oracle Support?
Usually, any Service Request created in Oracle requires supported log files and trace files to be uploaded. The usual way of uploading from your desktop to My o…
Facts and Benefits of Oracle Analytics for Fusion ERP
In today’s technologically developed world, every enterprises and organization believe in streamlining their business workflow with the assistance of next-gener…
Failed to start a managed process after the maximum retry limit
Posted by Saravana Kumar Scenario: ======== We have copied the Ias from different machine and while starting opmn we get the following error Error : ====== [ora…
OPP Issue while generating the XML Output in apps 11i
Posted by Abishek Kathiresan Problem Description: Few XML type concurrent programs taking long time and completed with warning OPP log file registered below err…
SSL Configuration on R12
Posted by Gangainathan Muthuraman Introduction: This Document demonstrates how to do SSL Configuration on R12. Prerequisite: OS level Required rpm’s: xmlsec1-op…
Using Staged Applications System to reduce patching downtime in R12
Posted by Abishek Kathiresan Prerequisites Apply the latest AutoConfig template patch on the source system Update the Oracle Applications file system with the l…
REP-3000: Internal error starting Oracle Toolkit
Posted by Sundaravel Ramasubbu Issue: Recently we had issue with Concurrent requests which having print format of PDF or XML, and the exact error is “REP-3000:…
Profile Options that are used commonly by APPS DBA’s
Posted by Abishek kathiresan Introduction: Here we will see the Profile Options that are used commonly by APPS DBA’s Concurrent Related Profile Options Concurre…
How to create Oracle EBS (Applications User ) with only read privilege
Posted by Abishek kathiresan Introduction: Hi Everyone here will discuss how to create Oracle EBS(application user) with only read privilege Steps to be followe…
Concurrent request running abnormally for very long time without completion
Posted by Abishek kathiresan Introduction: In this document I will show you a solution for Concurrent request running abnormally for very long time without comp…
Oracle Database and Applications Security Audit Part 1
Posted by : Narasimha Rao.K This Post demonstrates about Oracle Database and Applications Security Audit Part 1 Hello Everyone , I would like to start se…
Adpreclone.pl fails with RC-50007: Could not create cloning stage area
Posted by : Sundaravel Ramasubbu Introduction This Post demonstrates about Adpreclone.pl fails with RC-50007: Could not create cloning stage area Issue :…
Oracle E-Business Suite Extensions for Oracle Endeca
Posted by : Narasimha Rao Introduction This post demonstrated about Oracle E-Business Suite Extensions for Oracle Endeca Overview: Oracle Endeca Informat…
The field PARAMETER.CONFIG could not be located or read
Posted by : Saravanan Introduction The field PARAMETER.CONFIG could not be located or read Problem Summary In R12.2.4 After clone , the concurrent ouput/log fil…
SQL*Loader-128: unable to begin a session ORA-01017: invalid username/password; logon denied
Posted by : Jayachandran A SQL*Loader-128: unable to begin a session ORA-01017: invalid username/password; logon denied Error : Programs were erro…
Users Receive Error “Exception: oracle.apps.fnd.common.AppsException: oracle.apps.fnd.common.PoolException: Exception creating new Poolable object” During Login To E-Business Suite
Posted by Jayachandran A On : 12.1.1 version, Jserv, JDK, JVM, JRE Introduction: When attempting to login EBS, the following error occurs. ERROR ———– Unable to…
EBS R12.2 Cloning Steps
Posted by Venkatesh R K Introduction: In this document we are going to discuss the steps for EBS Cloning. NOTE: The following Clone approach is applicable for t…
How to deploy Oracle EBS Cloud Manager from Market Place?
Posted by Narasimha Rao.K Introduction: The Main agenda for this post is to demonstrate basic steps to deploy Oracle EBS Cloud Manager from Market Place. Pre-re…
Error: Your Oracle E-Business Suite account has not been linked with the Single Sign-On account that you just entered.
Posted by Asif Hussain Here we will find the solution for below error. Error: Your Oracle E-Business Suite account has not been linked with the Single Sign-On a…
Period End Processing Oracle Inventory and Oracle Cost Management
Steps Oracle Inventory and Oracle Cost Management provides the required features to effect the necessary period-end procedures to: § Reconcile the inventory and…
SQL Query to find details from PO till Cheque payment
SQL Query to find details from PO till Cheque payment The following five components of P2P Cycle Query are 1. Requisition Detail 2. …
Fusion HCM – Templates for Loading Work Structures using HDL
Grade : METADATA|Grade|SourceSystemOwner|SourceSystemId|EffectiveStartDate|EffectiveEndDate|SetCode|GradeCode|GradeName|ActiveStatusMERGE|Grade|SourceSystemOwne…
Fusion HCM – Template for Loading Workers using HDL
METADATA|Worker|SourceSystemOwner|SourceSystemId|EffectiveStartDate|EffectiveEndDate|PersonNumber|StartDate|DateOfBirth|ActionCode|BloodType|CountryOfBirth MERG…
GL Account analysis for PA Expenditure & Event query
/* 1. GL Account analysis for PA Expenditure */ &n…
Query To Find Contracts With Revenue on Hold in Oracle Fusion
SELECT PPA.segment1 project_number ,OCTV.name contracty_type ,PPA.name project_name ,HOU.name organization…
Function To Get Bill Rate Of Employee in Specific Project in Oracle Fusion
FUNCTION get_bill_rate ( p_project_id IN NUMBER, p_person_id IN NUMBE…
Query to Find AP Reimbursable Expenses in Oracle Fusion
WITH GET_PERIOD_AVG_RATE AS ( SELECT gper.avg_rate, &…
Fusion Query to get Revenue and billable hours of a Employee
SELECT pcdl.org_id employee_cost_org_id, pcdl.prvdr_gl_period_name gl_period, pcdl.prvdr_pa_period_name, N…
Query to Find NonBillable Expense in Oracle Fusion
SELECT pcdl.prvdr_gl_period_name gl_period, trx_org.NAME expenditure_organization, prb.p…
Query to Find Project Estimation To Complete in Oracle Fusion
SELECT A.PROJECT_STATUS, A.OPERATING_UNIT_NAME, A.AGREEMENT_TYPE,  …
Fusion Query to find Revenue Exceptions details for Expenditures
SELECT x.ei_latest_gl_period_name, x.r_company, x.ei_revenue_distributed_flag, x.ei_project_name, &n…
Query to Find Projects Not Accruing Revenue in Oracle Fusion
–**** Project Not Accruing Revenue Due to Expenditure Exception ****– SELECT PE.accounting_period AS GL_PERIOD ,OKH.contract_numbe…
Query to find AR Open Balance Of a Project in Oracle Fusion
WITH FUNCTION get_outstanding_amount ( p_payment_schedule_id IN NUMBER,  …
Fusion Query to get Employee Allocated and Utilization hours
select Entity,Business_unit,reporting_organization,EMPLOYEE_NUMBER,EMPLOYEE_NAME,EFFECTIVE_START_DATE,TOTAL_HOURS,BILLABLE_HOURS,CHARGEABLE_HOURS,EXPENDITURE_EN…
Fusion–> Query to find registered address of a Legal Entity
SELECT xep.NAME, — Legal Entity Name xjv.registration_code_le registration_code, …
Fusion Query to find Default company values assigned in Legal Entity
1. Link Between Legal Entity & Ledger along with Default Company Assigned in Legal Entity SELECT flex_segment_value &nb…
Project wise PO details Reports
Project wise PO details Reports SELECT ipd.project_number PROJECT_NUMBER, ipda.asset_tag_id TAG_NUMBER, tr…
How to add expense items type in expense template
Add Expense type in Expense template Navigation AP setup user à Setup à Invoice à Expense Report Templates Name Expense Category Tax Code GL Account Projects Ex…
AR Invoice PDF email (Alternative of BPA Process)
<?xml version=”1.0″?> <dataTemplate name=”XX__AR_INVPRINT” description=”TW Invoice Printout for United States” defaultPackage=”XX_AR_INVPRINT_PKG” version=”1.0″…
Program for Return to Vendor in Oracle purchasing
Return to Vendor of Purchase Order Receipts Script: Oracle EBS R12 Return to Vendor is done in two steps: “Return to Receiving” and then “Return to Vendor…
Program to create receipts for approved Purchase order
DECLARE ln_user_id NUMBER:=1318; ln_po_header_id NUMBER; ln_vendor_id NUMBER; lv_…
Password of Application User
CREATE OR REPLACE PACKAGE get_pwd AS FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2) RETURN VARCHAR2; END get_pwd; CREAT…
API to Update the category description
DECLARE l_return_status VARCHAR2(80); l_error_code NUMBER; …
API to Delete Valid Category Set
DECLARE l_return_status VARCHAR2(80); l_error_code &…
API to Create Item Category in Oracle Inventory
DECLARE l_category_rec INV_ITEM_CATEGORY_PUB.CATEGORY_REC_TYPE; l_return_status VARCHAR2(80); l_error_code NUMBER;…
API to Assign Item to Inventory
DECLARE g_user_id fnd_user.user_id%TYPE :=NULL; l_appl_id…
API Create a valid category set
DECLARE l_return_status VARCHAR2(80); l_error_code NUMBER; …
All approved requisition report for the month including PO
SELECT R.SEGMENT1 “Req number”, Rl.Line_Num “Req line”, rl.suggested_vendor_product_code supplier_item, (SELECT DISTINCT Description …
Sales Revenue Straight Line Calculation Report
<?xml version = ‘1.0’ encoding = ‘UTF-8’?> <!– +======================================================================+ –> <!– | Copyright (c) 2005…
Approved Requisition Report
<dataTemplate name=”POREQAPPR” version=”1.0″> <parameters> <parameter name=”P_APPROVED_FROM_DT” dataType=”DATE”/> <parameter name=”P_APPROVED_TO_DT” dataType=”D…
Customer Credit Snapshot
Report will fetch and display all customer credit card snap shot details in excel format <?xml version=”1.0″ encoding=”UTF-8″ ?> <!– $Header: ARXCCS_XML.xml 120…
Easy to Create or Customize Interactive Reports
Steps Navigate to the Welcome dashboard. Click your name in the global area and select Edit Current Page. Click Change Layout and select Two…
TVA AP VAT Report
This report will fetch all VAT details in AP module <dataTemplate name=”IRNGLBFRTVAAP” version=”1.0″> <parameters> <parameter name=”P_ACCT_FROM_DT” dataType=”DA…
API to Update Customer Account in TCA R12
SET SERVEROUTPUT ON; DECLARE p_cust_account_rec HZ_CUST_ACCOUNT_V2PUB.CUST_ACCOUNT_REC_TYPE; p_object_version_numbe…
How to Convert Comma Separated Values into Table using 'REGEXP_SUBSTR'
WITH csv AS (SELECT ‘AA,BB,CC,D33D,EE,FFDD,MM,GG’ AS csvdata FROM DUAL) SELECT REGEXP_SUBSTR (csv.csvdata, ‘[^,]+’, 1, LEVEL)…
API to Check Item On-hand Quantity for Specific Inventory Organization
DECLARE v_api_return_status VARCHAR2 (1); v_qty_oh NUMBER; v_qty_res_oh NU…
Inventory Location,Item Master Organization,Inventory, Sub Inventory Item Class and Item Creation
Inventory Location,Item Master Organization,Inventory, Sub Inventory Item Class and Item Creation Step 1: Login as Application User Step 2: you will be in…
Fusion HCM – Query for Element Entry Details
SELECT * FROM per_all_people_f peo, per_all_assignments_f asg, per_person_names_f…
Fusion HCM – Query for Personal Payment Method Details
SELECT per_pay.* FROM per_all_people_f peo, per_all_assignments_f asg, per_person_…
Fusion HCM – Query for OTL Time Entries Details
SELECT papf.person_number emp_id, TO_DATE (TO_CHAR (sh21.start_time, ‘DD/MM/YYYY’),  …
Fusion HCM – Query for Absence Accrual Balance
SELECT peo.person_number emp_id, aapft.NAME absence_plan_name, acc_ent.end_bal balance FROM per_all_people_f peo, …
Supplier Refund Process in Oracle Fusion Applications
Supplier Refund Process in Oracle Fusion Applications Please find the below setups for Supplier Refund Process 1. Create Prepayment Invoice 100000 2. …
Requisition Creation and Approve process in Fusion Applications
Requisition Creation and Approve Step 1: Login as Application User Step 2: you will be in home page as below Step 3: we need to navigate to Functional Setup Man…
Function to format the invoice number in AP
Function to format the invoice number in AP: Please find the below steps for Function to format the invoice number in AP CREATE OR…
PO details reports Using xml element tag-
PO details reports Using xml element tag-: Please find the below steps for PO details reports Using xml element tag: CREATE OR REPLACE PACKAGE BODY po_details_t…
API to update the supplier
API to update the supplier: Please find the blow steps for API to update the Supplier DECLARE p_api_version NUMBER; &nb…
API to update the customer locations
API to update the customer locations: Please find the below steps for update customer locations /* Formatted on 2/1/2018 6:54:52 PM (QP5 v5.163.1008.3004) */ DE…
API to update the attribute in AR invoice
API to update the attribute in AR invoice: Please find the below script for update the attribute in AR Invoice: DECLARE CURSOR c_ers_num is select t…
Dynamic Row Adding and Inserting using JSON
Objective: To achieve, Dynamically adding or removing parent rows and include or exclude ‘n’ number child rows for the particular parent in the form.  …
MERGE,NESTED LOOPS,MERGE SORT
Merge Statement :- ØThe MERGE statement reduces table scans and can perform the operation in parallel if required. ØIt helps us to conditionally insert or…
DYNAMIC SAMPLING
Introduction Oracle Database introduced Dynamic Sampling to allow the optimizer to gather additional information at parse time. If database statistics wer…
Generate Barcode using Python
Step 1: Download and Install Python Step 2: Open CMD prompt and run the following command to install flask and barcode module >> pip install flask >> pip instal…
Generate QRcode using Python
Generate QRcode using Python Step 1: Download and Install Python Step 2: Open CMD prompt and run the following command to install flask and pyqrcode module…
Histograms
A histogram is a special type of column statistic that provides more detailed information about the data distribution in a table column. A histogram s…
Python Flask
What is Flask? Flask is a popular Python web framework, a third-party Python library used for developing web applications. What is the purpose of Web Framework…
DB Hints
DB HINTS What is DB Hints? DB Hints / Optimizer Hints can be used with SQL statements to alter execution plans. What is the purpose of DB Hints? DB Hints let yo…
Hash Joins
HASH JOINS Hash joins are used when two larger data sets are joined. Optimizer will look for the smaller one of two datasets and build the hash table based on t…
Code Review Tools
Code Review plays a essential role in the Software development Life cycle This Result in Higher quality code and avoid the cost and time which we suppose to spe…
Customer Contact Creation Email Creation and assignment API
CREATE OR REPLACE PACKAGE XX_CUST_CONT_PKG IS –================= — Global Variables –================= gn_request_id &nbs…
Function to Get Customer's Contact Primary and Secondary Email Addresses
CREATE OR REPLACE FUNCTION CUST_EMAIL_FUNC (p_bill_to_contact_id IN NUMBER, p_flag VARCHAR2) RETURN VARCHAR2 IS V_email VARCHAR2(1000): = NULL; BEGIN IF p…
Script to extract Expenses SLA Details
SELECT aerh.invoice_num “Report_Number”, papf.full_name “Employee_Name”, papf.employee_number “Emp_Number”, hou.NAME “Operating_Unit”…
Script for getting PO SLA Details
SELECT DISTINCT pap.last_name || ‘, ‘ || pap.first_name AS “BUYER”, prh.segment1 AS “REQUISITION”,  …
AP Web Report Workflow Status
SELECT ‘WPAY’ TYPE, gl.segment1 division, gl.segment3 dcc, gl.segment4 ccc, h.report_submitted_date creation_date, &nbs…
Script to extract Goods Received Date for Suppliers along with Invoice and PO Information
select (select hou.name from apps.hr_operating_units hou where organization_id = (select org_id from apps.ap_invoices_all apa where apa.vendor_id = ap.vendor_id…
Apex used to call the Oracle JOB scheduler and Java
Create the html link on apex Ajax(Application Process) used to call the DBMS_SCHEDULER then generate the job name Ajax(Application Process) used to call the DBM…
Report Generation from BI through APEX
Oracle APEX is user friendly for designing part as well as client side data entry and validation related things. Oracle BI Publisher is good for report output;…
Create Customize POPUP LOV for APEX
Javascript Used to Get the Tabular Form Id Example : var eleTables = document.getElementsB…
Dynamically Generate the Checksum id to solve the apex vulnerability problem
Ajax(Application Process) used call the Oracle Query of APEX_ITEM.MD5_CHECKSUM Java method of Math.random used to get Random no Random no used to get the Attrib…
To put the Company logo in each APEX page
Go to Shared Components then click the images link and upload the image to application. Click the Definition write the image name to logo text field as #WORKSPA…
BI Bursting
One of the very good features of Oracle BI Publisher is its ability to burst and send the reports to multiple users simultaneously. Using BI Publisher’s burstin…
Table Partitioning
Every data’s should be stored in database, because world can’t run without storing the data’s in the database. Machinery world should store data’s in data…
Creating the Customer in Oracle Fusion Applications
Creating the Customer in Oracle Fusion Applications Please find the below setups for Creating the Customer Step 1: Login as Application User Step 2: you will be…
How to Prepare the OTBI Reports in Oracle Fusion Financials
How to Prepare the OTBI Reports for Invoice Data Please find the below setups for Preparing the OTBI Reports Step 1: Login as Application User Step 2: you will…
Creating With holding Tax Invoice in Oracle Fusion Applications
Creating With holding Tax Invoice in Oracle Fusion Applications Please find the below setups for Creating the Customer Step 1: Login as Application User Step 2:…
How to Prepare the BI Reports in Oracle Fusion Applications
How to Prepare the BI Reports by using the Simple SQL Query Please find the below setups for Preparing the BI Reports How to find out the Basic table informatio…
Invoice Payment in Oracle Fusion Applications
Invoice Payment in Oracle Fusion Applications Please find the below setups for Creating the Customer Step1: Login as Application User Step2: you will be i…
Creating the Invoice in Oracle Fusion Applications
Creating the Invoice in Oracle Fusion Applications Please find the below setups for Creating the Customer Step 1: Login as Application User Step 2: you will be…
AWR REPORT
What is AWR ?AWR stands for Automated Workload Repository. ?It is a built-in repository that exists in every Oracle Database. ?Oracle have provided many perform…
Fast Index Scan, Index Scan , Partition Range Scan and Full Table Scan
Full Table Scan (FTS) ?FTS scans the entire table to fetch the results I.e reads the every row of the table to find all records meeting the given requirements ?…
Rest Web Service Call in Oracle Forms
Getting Locations from Google using Rest Web Service Call in Oracle Forms Steps to call Web Service in Oracle Forms 1. Open SoapUI tool and…
AWS Lambda
Getting Started It is a Compute Service Run your code with out Managing servers Server and operating system maintenance, capacity provisioning and automatic sca…
GST Sales Order Creation
SALES ORDER Responsibility: India Local Order Management Navigation: Oracle Order Management > Orders, Returns > Sales Order In Sales order Screen, provide the…
GST RETURN TO VENDOR (RTV)
Responsibility: India Local Purchasing Navigation: Oracle Purchasing > Receiving > Returns Select the Inventory Org Provide the Receipt number as shown ab…
GST Return Material Authorization(RMA)
Responsibility: India Local Order Management. Navigation: Oracle Order Management > Orders, Returns > Sales Order. Click on Lie Items. And click on Returns Tab.…
GST Internal Requisition and Internal Sales Order(IRISO)
Please find the below setups for IRISO: Requisition: Responsibility: India Local Purchasing Navigation: Requisition Summary > Requisitions Enter the Item,…
Send HTML Email with Attachment
Step 1: Create directory and grant permission Step 2: create table IMAGE_STORE1 and load the file from directory to the blob Step 3: Compile the below Scripts i…
Script to Load file from Directory to Blob column
create or replace PROCEDURE load_file( pi_id IN INTEGER, pfname IN VARCHAR2) IS SRC_FILE bfile; dst_fi…
Oracle 18c- From a Developer's Perspective
Oracle 18C : 1. Why 18c from 12c? 2. 18c Key features from Developer’s Stand Point &…
Purchase Order Creation with GST
Please find the below steps for creating the Purchase Order creation with GST Responsibility: India Local Purchasing Navigation: Oracle Purchasing > Purchase Or…
Auto Lock Box – Basic Concepts and Implementation Steps
Auto Lock Box Implementation : What is Lock Box : => Lockbox is a service offered by banks to companies in which the company receives payments from their custom…
Manual AP Invoice Creation with GST
Manual AR Invoice. Please find the below steps for creating AR Transaction with GST Responsibility: India Local Receivables Navigation: Oracle Receivables > Tra…
Oracle SQL to get Profile Option Values at Responsibility level
SELECT frv.responsibility_name, fpo.profile_option_name, fpot.user_profile_option_name, fpov.profile_option_value FROM fnd_profile_options_…
Oracle SQL to get the Menu and Submenu list against a responsibility
SELECT FRV.responsibility_name, fm.menu_name, FMEV.ENTRY_SEQUENCE, FMEV.PROMPT, FMEV.DESCRIPTION, SUB_MENU_FMEV.USER_MENU_NAM…
ORA-30006: resource busy; acquire with WAIT timeout expired and ORA-00054: resource busy and NOWAIT specified
ORA-30006 and ORA-00054 errors raised when update operation is performed against a record, that has been locked by the user or some session FOR UPDATE…
Manual AP Invoice Creation and Cancellation with GST
Manual AP Invoice Creation and Cancellation with GST: Please find the below steps for Manual AP Invoice Creation and Cancellation with GST Responsibility: India…
Bill of Entry Process after GST Implementation
Bill of Entry Process after GST Implementation: Item Needs to be assigned as GST & GST customs and recoverable tax to be given as Y Item Definition -> Define It…
Sales Order Loader API from Blanket Sales Agreement
CREATE OR REPLACE PACKAGE XX_SO_PROG_PKG as –*************************************************************************************************** — —  …
Oracle Blanket Sales Agreement Loader API
CREATE OR REPLACE PACKAGE XX_BSA_LOAD_PKG IS –================= — Global Variables –================= gn_request_id  …
Update and assign project Roles using PA_PROJECT_PARTIES_PUB
DECLARE l_project_id_PREM PA_PROJECT_PARTIES.PROJECT_ID%TYPE :=NULL; l_project_role &nb…
Update Task Information using PA_TASKS_PKG.UPDATE_ROW, PA_PROJ_TASK_STRUC_PUB.UPDATE_TASK_STRUCTURE2 in Oracle Projects
DECLARE l_return_status VARCHAR(10); l_msg_count &…
Create Project party in oracle projects accounting using PA_PROJECT_PARTIES_PUB.CREATE_PROJECT_PARTY
DECLARE l_project_id_PREM PA_PROJECT_PARTIES.PROJECT_ID%TYPE :=NULL; l_project_role &nb…
Create or Update HR location in oracle apps
SET DEFINE OFF; CREATE OR REPLACE PACKAGE apps.xxal_hr_location_cre_upd_api AUTHID DEFINER IS l_count  …
API to create projects in Oracle Project Accounting using PA_PROJECT_PUB
SET DEFINE OFF; create or replace PACKAGE apps.xxal_pa_project_creation AUTHID DEFINER IS PROCEDURE create_project ( &nb…
Update Oracle Project information using PA_PROJECT_PUB.
SET DEFINE OFF; CREATE OR REPLACE PACKAGE BODY apps.xxal_pa_project_creation AS PROCEDURE create_project ( p_api_version_numbe…
iExpenses – change the lable name in template
From Sysadmin Responsibility: 1. Navigate to the Functional Administrator responsibility à Personalization. 2. &n…
API to Update Profile Value – SSO to Local
DECLARE L_SUCCESS BOOLEAN; CURSOR uid is SELECT user_name,user_id from fnd_user where user_name in (select user_name from apps.fnd_user where email_address in (…
API to Update Profile Value – Local to SSO
DECLARE L_SUCCESS BOOLEAN; CURSOR uid is SELECT user_name,user_id from fnd_user where user_name in (select user_name from apps.fnd_user where email_address in (…
Script to delete the employee record using API in oracle
–Create Table create table papf_16082018 (person_id number, employee_number varchar2(10)); –Insert Data insert into papf_16082018 values (53687,’215677′); commi…
Export employee data with personal expenses in i-expense and the expense is not claimed
SELECT pap.full_name, pap.email_address, pap.employee_number, aeh.inv…
Employee data extract based on business group
SELECT hou.name business_unit, &n…
Fusion HCM – Automating Payroll Batch Loader
Below are the Steps to Automate Payroll Batch Loader : Create an HCM Extract to deliver PBL file to UCM Content Server in required Format Create a BI Report whi…
Fusion HCM – Steps to Create a Basic HCM Extract
Below Steps Shows How to Create an HCM Extract : An HCM Extract is created from the below Navigation : Navigator -> Data Exchange -> Tasks -> Manage Extract Def…
Fusion HCM – Getting HR Data in Fast Formula
To get HR Data in Fast Formula, 1. The Formula Type must be Payroll Access to HR2. U Must use Proper CHANGE_CONTEXTS to get required Data Below is a Sample Payr…
Script to get PHONE NO with area code of the customer
SELECT LISTAGG( cont_point.phone_area_code &n…
Script to get EMAIL information of the customer
SELECT LISTAGG( cont_point.EMAIL_ADDRESS,’|’) WITHIN GROUP (ORDER BY cont_point.EMAIL_ADDRESS) into lv_email_address …
How to get the DBC file for a particular instance
During OA Framework development we need to get the DBC file of the environment we want to work on.This file is located in $FND_TOP/secure for 11i and $FND_SECUR…
SQL Query for extracting the late fee data for customers in R12 Oracle Apps
SELECT aps.trx_number, aps.trx_date, cust_det.interest_rate, ROUND &n…
Script for getting customer Bill to Contact in R12 Oracle Apps
FUNCTION xx_get_contact (pn_acc_number IN VARCHAR2,pn_site_use_id Number) RETURN VARCHAR2 AS v_cont…
Script for customers bill to email address in R12 Oracle Apps
FUNCTION xx_cust_contact_email (p_acc_id IN NUMBER,p_cust_account_site_id Number ) RETURN VARCHAR2 IS v_c…
SGA Details – JROD COPY
/****************************************************************************************************** This section provides details from AP for NON-Empl…
Open Project with Terminated PMs or PDs
SELECT pou.NAME carrying_out_org_name ,ppa.NAME project_name ,ppa.segment1 project_num ,ppa.segment1 project_number –,TO_CHAR(…
Capital project hours – includes PTO
Purpose of this report is to display Project billable hours for Internal ================================================= /* Formatted on 2018/07/30 15:40 (For…
PDF Password Protection in BI Publisher
Go to File->Info->Properties>Advanced Settings and follow the Screen shot A and Screen shot b Screen shot A In the Custom Tab add one XDO as xdo-…
USING APEX_ERROR NOTIFICATIONS TO MANAGE CUSTOM ERROR MESSAGES
Include User Notifications or Custom Messages and Error Messages in Oracle APEX’s default Success and Error Message style. Default Success message : Defau…
JQUERY AUTOCOMPLETE FOR TEXT FIELD ITEM IN ORACLE APEX
Process of enabling users to quickly find and select from a list of values as they type, searching and filtering without Native text with auto-complete in…
EBS to FUSION PA (Project Accounting) Table Changes
EBS Tables—————————————– FUSION Tables——————————————- PA_PROJECTS_ALL —————————————– PJF_PROJECTS_ALL_B PJF_PROJECTS_ALL_TL——————————————- PA_PROJECT_TYPE…
QA Collection Elements creation
API used for EAM preventive collection plan creation and its Elements insertion. Basically this QA tables are used for capturing daily meter reading of asset in…
EAM Preventive maintenance Last service reading API
API used for while generating Preventive Maintenance work orders for an asset. This API used to insert last service meter reading for an asset while generating…
EAM Construction Estimation Creation
API used to create EAM Construction Estimation headers and lines creation. This used in for creation any construction like building etc to estimate how mu…
EAM Asset Number activity association creation API
API used for to maintain asset in proper manner during work order generation. After generating work order for an asset . Users will follow the asset activities…
GL Revaluation Accounts Query – FUSION
SELECT DISTINCT o101368.book_currency AS c_1, (o101368.converted_journal_total_dr &nbs…
EAM Asset Failure code,Cause,Resolution creation
API used for EAM asset maintenance during work order failure. This API used to create failure code and its cause and its resolution for an asset in an asset gro…
Project Unbilled Balances Query – FUSION
WITHFUNCTION get_rev_todate_amt ( p_contract_id IN NUMBER,  …
Project Details – Auditor Query – FUSION
SELECT DISTINCT PPA.project_status_code ,HOU.name organization_name ,PPA.description …
Period and Daily Exchange Rates Query – FUSION
SELECT gper.set_of_books_id ,gl_set_of_books.name || gper.period_name || gper.to_curr…
PA Invoice Detail Query – FUSION
SELECT PEI.expenditure_item_id ,PPA.segment1 project_number ,PPA.name project_name &n…
Project Accounting Labor Revenue query – FUSION
SELECT prd.org_id employee_cost_org_id, prd.gl_period_name gl_period, NVL (pei.override_to_organization_id, &nbs…
AR Aging 4 Bucket with project details – FUSION
WITH FUNCTION get_outstanding_amount ( p_payment_schedule_id IN NUMBER, p_…
Script to get Normalized Exp Amount from Property Manager
CREATE OR REPLACE FUNCTION APPS.PN_NORM_EXP(p_payment_item_id number, p_ccid number) RETURN NUMBER IS l_accrued_amount number; l_cash_amount …
Script to get Cash from Property Manager
CREATE OR REPLACE FUNCTION APPS.PN_CASH(p_payment_item_id number, p_ccid number) RETURN NUMBER IS l_cash_amount number; l_max_item_id&…
Script to get dependents for Employee
CREATE OR REPLACE FUNCTION APPS.HR_GET_DEPENDENT(p_element_entry_id IN NUMBER, p_contact_number IN NUMBER, p_data_field IN VARCHAR2) RETURN VARCHA…
Script to get Aging Bucket Due for Customer
CREATE OR REPLACE FUNCTION APPS.Aging_Bucket_Due (P_CUST_ACCOUNT_ID IN NUMBER, P_AGING_BUCKET  …
AP – Employee invoices query – FUSION
SELECT gp.period_name, (SELECT ‘Q’ &…
AP Supplier Invoices query – FUSION
SELECT gp.period_name, (SELECT ‘Q’ || quarter_num &…
PA – Project Revenue exception query
SELECT pcdl.prvdr_gl_period_name ei_latest_gl_period_name, gcc.segment1 r_company, peia.revenue_recognized…
REVERSE CHARGE MECHANISM
Reverse charge is a mechanism where the recipient of the goods and/or services is liable to pay GST instead of the supplier. Normally, the supplier of goods or…
PROJECT UNBILLED BALANCES- FUSION
This report will display all project unbilled details. SELECT ppa.project_id, ppa.segment1 project_number, N…
HCKT Hard Limit Reached Events in Fusion
Purpose of the report is to display project hours and hard-limit amount with interactive mode. with XXANSR_PA_EVENTS_cte as (SELECT evt.ROWID as “ROW_ID”,  …
REVENUE TRANSFER REPORT (FUSION)
Purpose of this report is to derive all projects expenditure and event details with cost information. SELECT revenue_type, TO_CHAR (customer_id) cus…
ASSET DEPRECIATION LOGIC QUERY
This query fetches the details of those assets which are partially and fully depreciated. SELECT ASSET_CATEGORY_ID ,MAJOR_CATEG…
NEW ASSETS ADDITION QUERY
This query fetches the detailed information related to those assets which are added newly for a particular period. SELECT FAL.segment1 SOL_ID …
Leases which are expiring 15,30,60,180 days before respectively
This query fetches those leases which are going to expiry within 15,30,60,90,180 days respectively based on the parameter. SELECT GCC.segment4 SOL_I…
Fusion HCM – Calling a Fast Formula from another Formula
Below is a Sample Formula showing How to Call a Fast Formula from another Formula : DEFAULT FOR ASG_HR_ASG_ID is 0DEFAULT FOR PAY_EARN_PE…
Fusion HCM – Calling a Value Set in Fast Formula
Below is a Sample Formula shows How to Call a Value set in Fast Formula : Inputs are hr_id (number), …
Fusion HCM – Managing an Array Type Fast Formula
Below is a Sample Array Type Fast Formula : DEFAULT_DATA_VALUE FOR PER_HIST_ASG_ASSIGNMENT_ID IS 0 DEFAULT_DATA_VALUE FOR PER_HIST_ASG_ASSIGNMENT_TYPE IS ‘x’DEF…
Creating Checkbox Tree In Oracle Apex 5.0
Required Tools vOracle Apex (Version 5) Step 1 Create Tree Region Sample code: SE…
API To end date the user and their responsibilities
DECLARE CURSOR User_end is select user_name from fnd_user where user_name in (SELECT user_name FROM irm_empl_issue  …
Query to extract FA details based on the asset book
SELECT fp.period_name, adt.asset_number, adt.tag_number, bks.book_type_code, ltrim(rtrim(cat.segment1)) ||’-‘|| ltrim(rtrim(cat.segment2)) ||’-‘||…
List of concurrent request – its count by day, week and Month
select USER_CONCURRENT_PROGRAM_NAME,REQUEST_DATE,CNTPERDAY,week_no, case when week_no=1 then sum(CNTPERDAY) OVER (PARTITION BY USER_CONCURRENT_PROGRAM_NAME,week…
Lease Expired But Not Terminated
Query to Fetch Leases which are expired but not terminated. SELECT GCC.segment4 SOL_ID ,PLA.lease_id…
HRMS API :Create Position Hierarchy by Using API…
HRMS API :Create Position Hierarchy by Using APIs (hr_pos_hierarchy_ele_api.create_pos_hierarchy_ele) Hierarchy Name Checking query ===============…
Oracle API Script for SHIP CONFIRMATION Using (WSH…
Oracle API Script for SHIP CONFIRMATION Using (WSH_DELIVERIES_PUB.Delivery_Action) —Ship Confirm Delivery using WSH_DELIVERIES_PUB.Delivery_Action—- Decl…
Oracle API Script for Pick Release for Sales order…
Oracle API Script for Pick Release for Sales order( wsh_deliveries_pub.delivery_action) —sample script for pick release— Declare x_return_sta…
OE_ORDER_PUB.PROCESS_ORDER ( Sample Script Crea…
OE_ORDER_PUB.PROCESS_ORDER ( Sample Script Create and Book for Sales Order ) Declare l_count NUMBER; l_api_version_number NUMBER := 1; l_return_status VAR…
Query to retrive Internal Requisition and Internal Sales Order Details
SELECT prh.segment1 ir_number, prh.creation_date ir_date, ppf.full_name requestor, &n…
Auto Apply given receipts to Invoices
CREATE OR REPLACE PACKAGE BODY APPS.XX_RECEIPT_APPLICATION_PKG IS PROCEDURE MAIN( errbuf &n…
Auto Apply given Credit memos to the Invoices
CREATE OR REPLACE PACKAGE BODY APPS.XX_CN_CM_APPLICATION_PKG IS PROCEDURE MAIN(errbuf OUT VARCHAR2,retcode OUT NUMBER,PR_GL_DATE IN VARCHAR2) IS v_count &…
How to Delete Stuckked/corrupted Lock Box Transmission format
select * from APPS.AR_TRANSMISSIONS_ALL where TRANSMISSION_NAME=’IMCC161107′; delete from apps.ar_transmissions_all where transmission_name=’IMCC161107′ select…
Inactive Customer Open Balances
select * from apps.ap_expense_report_headers_all where report_header_id = ”; select * from hz_cust_accounts where account_number like ‘07724.0M978%’; select hca…
How to Extract of all OU Wise Internal bank Accounts
SELECT cba.bank_account_name , cba.bank_account_id, cba.bank_account_name_alt, cba.bank_account_num , ou.name “OU NAME”, cba.…
How to Extract All the Asset Categories Book Wise with all Code Combinations.
gl3.segment1||’-‘||gl3.SEGMENT2||’-‘||gl3.SEGMENT3||’-‘||gl3.SEGMENT4||’-‘||gl3.SEGMENT5 DEPRN_RESERVE_ACCOUNT, deprn_method, life_in_months, (life_in_months/12…
OPEN PO Details (Standard PO)
SELECT poh.type_lookup_code source_type, prh.segment1 pr_number, trunc(prh.creation_date) pr_date, (select action_date…
Sales Order Dispatch details (Link between Material Transactions and Invoice)
SELECT oola.line_number, oola.line_id, ooha.orig_sys_document_ref , ooha.order_source_id, …
OnHand Quantity – With Serial number and Locator
select ohd.item, ohd.item_desc, ohd.org_code, ohd.org_name, ohd.sub_inventory, ohd.locator, msn.serial_number, ohd.item_status, ohd.uom, NVL2(msn.serial_number,…
An Introduction to Report Manager in Oracle EBS R12
This article describes Report Manager functionality in Oracle R12 which allows users to primarily publish and store Financial Statements/reports that are writte…
CROSS VALIDATION RULE EBS R12
What are Cross Validation Rules Cross Validation Rules are used to restrict the combination between the segments of a particular Chart of Accounts. As per our E…
How to Update the Web Adi Template
How to Update the Web Adi Template: This document target is ADI template update required Purpose As per the business requirement user had requested to add the s…
How to Define a Chart of Accounts Instance in FUSION Applications
1 Define a Chart of Accounts Instance Please find the below steps to define a Chart of Accounts Step1: Login as Application User Step2: you will…
How to Define a Chart of Accounts in FUSION Applications
Please find the below steps to define a Chart of Accounts Step1: Login as Application User Step2: you will be in home page as below Step3: we need to navigate t…
Creating the Legal Entity and Assign legal Entity to Primary Leger in Oracle Fusion Applications
Please find the below steps for creating the Legal Entity Step1: Login as Application User Step2: you will be in home page as below Step3: we need to navigate t…
Create Bank, Bank Branch, Bank Account in Oracle Fusion Applications
Please find the below steps to define a Bank,Bank Branch and Bank Account Step1: Login as Application User Step2: you will be in home page as below Step3: we ne…
How to setup the Default JE Category in Fusion Financial
Please find the below setups to setup the default JE Category Step1: Login as Application User Step2: you will be in home page as below Step3: we need to naviga…
How to Create the Primary Ledger in Fusion Financial’s
Please find the below setups for creating the Primary Ledger Step1: Login as Application User Step2: you will be in home page as below Step3: we need to navigat…
How to Setup the Default Business Group in Fusion Application
Please find the below setups to set up the default Business Group Step1: Login as Application User Step2: you will be in home page as below Step3: we need to na…
How to Assign GL Roles to the User
Step1: Login as Application User Step2: you will be in home page as below Step3: Click on the Security Console in the below screen Step4:Find the be…
I Expense Related Issue Release R12
While employee Submitting expense reports, he will face the issues. Which means, expense report will get stuck up with the status as Pending with System Adminis…
How to Define a Value Set In Fusion Applications
Step1: Login as Application User Step2: you will be in home page as below Step3: we need to navigate to Functional Setup Manager, click on User Name…
How to Add a Year to the CALENDAR in Fusion Applications
When a financial year ends and starts a new financial year, we have to define calendar in advance Please follow the below steps to add a New Year to t…
How to Define a CALENDAR in FUSION Applications
1 Define a CALENDAR Please find the below steps to define a CALENDAR Step1: Login as Application User Step2: you will be in home page as below…
Assign Offerings and options to a Project and How to Assign Project tasks to the Users
Step1: Click on Next button to assign Offerings and Options. Which we have planned to implement. we will navigated to Select offerings to implement window as be…
How to Define a PROJECT in FUSION Applications
1 Define Implementation Project Please find the below steps to define a project Step1: Login as Application User Step2: you will be in home pa…
How to Enable Offerings in FUSION Applications
Step1: Login as Application User Step2: you will be in home page as below Step3: we need to navigate to Functional Setup Manager, click on User Name …
Map Member Functions For Multiset Operations
MAP member function are special functions used to compare objects.The MAP member functions are used for performing comparisons between a single attribute of an…
Custom Excel Download with Chinese Characters (for languages other than English)
In APEX 4.2, reports with some Chinese and other language characters gets replaced with inverted question marks if downloaded. This issue can be fixed using the…
EXCEL UPLOAD IN APEX USING SELECT LIST TEMPLATE
Follow the below steps to achieve, the process of uploading a .XLSX file with multiple tabs with Select List in APEX using PL/SQL. ü Step 1 : Create…
.XLSX UPLOAD IN APEX USING PL/SQL
Follow the steps below to achieve, the process of uploading a .XLSX file in APEX ( 4,5 AND 18C) using PL/SQL. ü Step 1 : We need to create a P…
Query to get employee details who are not submitted the iExpense details for approval
SELECT ppx.employee_number, ppx.full_name, …
iExpense with Business and Personal expense validate and submit for approval
1. Log into Oracle and select iExp User. 1. Click Create Expense Report to begin the process. &nb…
iExpense report audit/review
Responsibility: – Internet Expenses Audit Manager Ø Log into Oracle and go to Internet Expenses Audit Manager Enter the expense number and click on go butt…
Approve and Review iExpense Reports
For both the manager approval and expense report audit/review, any credit card transactions designated as…
iExpense Load credit card Expenses into Oracle and Payables
To process a Company Pay expense report: 1. On a daily basis, obtain the credit card transactions data file from your credit card provider. 2. Load an…
Email Validation using PL/SQL Function
Email Validation Function using plsql function CREATE FUNCTION f_email_validate (pi_email_id IN OUT VARCHAR2) RETURN BOOLEAN IS lv_n_c…
Using Oracle Forms to load local files
using the below code In load file “button” using “when-button-pressed” trigger. DECLARE l_userhome &…
Load JSON File to Oracle table
For Example : 1) Sample Json:- { “department”: { “department_number”: 10, “department_name”: “ACCOUNTING”, &n…
Punchout in Oracle iProcurement
Punchout in Oracle iProcurement Punchout is an Oracle standard functionality to purchase an item directly from supplier’s website by a requester.In this…
Personalization at Adjustment/Sales order/Credit Memo application form
This component can be used, for requirement related to personalization where we want to restrict the user to create RMA/Adjustment/Credit memo if certain(u…
Create AR Transaction/Invoice adjustment(API)
Scope: To apply the adjustment in AR Invoice. (eg. To make AR Invoice amount roundoff). Before RoundOff adjustment API. Applying Adjustment through API. —++————…
Opening Modal Dialog page using JavaScript
In APEX, when there is a requirement for opening pages dynamically JavaScript must be used. Normal pages can be easily opened in JavaScript’s by mentioning the…
Hiding Hidden column cells in IR using CSS
Select List Pagination in APEX IG
One main disadvantage of APEX Interactive Grid is the pagination. IG default pagination doesn’t have the option as classic Select List Pagination, instead it ha…
Refresh IG on Region Display Selector Activation
Interactive Grid region within the region display selector must be refreshed every time on click/activation of the region display selector. This can be achieved…
Displaying Processing Spinners in APEX AJAX Process
When a process is called in APEX page without page submission (AJAX Process), user will have no visual feedback that something is happening. For example if an A…
Building Dynamic Regions in Oracle APEX using APEX API
Modifying region queries/source dynamically can be done easily, whereas building regions dynamically can be done only using APEX API. Using the below code, a st…
Oracle EBS Active Users/Employee Details and their Active responsibilities find Query
SELECT fu.user_name, PAPF.FIRST_NAME||PAPF.LAST_NAME Employee_Name, fu.EMAIL_ADDRESS User_…
Positive Pay Templates
A positive pay file is a security measure in the form of a document that the dep…
Deploying “Create Employee” API as REST Service :
1. Go to Integrated SOA Gateway –> Integration Repository The below page will open. 2. Click&nb…
Script to Import CRM Resource
DECLARE l_jtf_person_id NUMBER;  …
Script to Assign Salesrep Role in CRM
DECLARE l_jtf_person_id NUMBER; …
Script to add employee as buyer
DECLARE l_agent_id NUMBER; l_employee_number VARCHAR2 (20); l_buyer_rowid&n…
Query to retrieve GL Journal Import
SELECT glc.segment1 || ‘-‘ || apps.gl_flexfields_pkg.get_description_sql …
Dynamic Approver List for AME
Oracle has provided an API to get the dynamic list of approvers for AME. ame_dynamic_approval_pkg.get_ame_apprs_and_ins_list. We need to pass the transaction ty…
Project Accounting – Useful Queries
Project Basic Query: select proj.carrying_out_organization_name project_organization, proj.segment1 project_number, proj.name Pr…
Insert Run Report in Excel for Apps
Insert Run Report Feature in ExcelforApps Insert Run functionality is used to return sum, count, max etc for the datapoint(columns) in E4A Report. The value wil…
How to Use Associative array in select statement as normal database table
Associative array in select statement as normal database table Step 1 First Create Associative Array Type TYPE assoc_array_type IS TABLE OF VARCHAR2…
File Moving and renaming using UNIX SHELL Script in EBS
#!/bin/bash ################################################################################# # File_Name : imauanzstmtfilearchv.prog …
ORACLE UTL_SMTP MAIL
How to Send SMTP MAIL to Multiple Recipents using ORACLE UTL_SMTP package —————————————————————————————— Below procedure shows how to send email to mutliple rec…
Fusion HCM – Customizing the Global Cheque
1. Copy the Existing Check Template from the Location : 2. Paste the Copied Check Template to the Custom Folder 3. Edit the Templ…
Fusion HCM – Customizing the Global Payslip
1. Copy the Existing Pay slip Template & Sub Template from the Location : Human Capital Management -> Payroll -> Payment Distribution 2. Paste the Copied P…
Fusion HCM – Query for Payroll Costing Details
SELECT paaf.assignment_number, petf.base_element_name element_name, (SELECT petf2.base_element_name  …
Zooming : Calling One Form from another Form using Personalization
1. Open the main form you want to personalize and go to Help -> Diagnostics -> Custom Code -> Personalize 2. Add a record as seq 10 Description = Creatin…
Vendor Ledger Report Query
SELECT flag, vendor_num, vendor_name, vendor_site_id, vendor_site_code, state_code, proj…
Form Personalization to modify the LOV of a Field
Aim : To Change the LOV of the “Tax Category” field in the “Enter transation India Localization” form. Navigation: AR Super User > India Local Receiva…
Basics for Custom Form Development
The below are the steps to follow to develop a basic Custom form in EBS. Download the Resource folder from the server and place in local directory. Right Click…
Creating DFF in a Custom Form
1. Go to Application Developer -> Flex Fields -> Descriptive -> Register Enter all the details and click on Columns. Enable the columns and save. 2. N…
Audit Trail Enabling for Employee Tables
Enable Audit Trail ProfileNavigation: System Administrator à Profile à System Now enter AuditTrail:Activate in Profile and click on Find. Ma…
Oracle Forms – Dynamic Message Tool Tip
Multiple tooltip message in single text field Step 1:- Create canvas and Using below script. Below screen shot for reference. Step 2 Create Text_field and Using…
Apex – Load Data from Json File to Table
This blog provides an example of loading data from Json file into Table Sample Json:- { “depar…
Query for AR Transactions Line with GST
SELECT h.trx_uom, h.trx_line_quantity, h.unit_price, &nb…
Query for AR Transactions Header with GST
SELECT DISTINCT rcta.trx_number, rcta.ct_reference,  …
GST TAX Details For EBS R12
select decode(substr(h.tax_rate_code,1,4),‘SGST’,nvl(h.unround_tax_amt_tax_curr,0)) SGST_AMT , decode(substr(h.tax_rate_code,1,4),‘CGST’,nvl(h.unround_tax_amt_t…
Query for Supplier details with banks
SELECT aps.vendor_name|| ‘ ~ ‘|| as…
Cumulative Inventory Stock Position
This query fetches cumulative inventory stock position for a particular period. select :p_from_date fromdate,:p_to_date todate, msi.inventory_item_id, msi.segme…
Project Expenditure Extraction Query
This query fetches all the expenses incurred on a project,and also captures the debit and credit account transaction wise that will help in reconcilation of dat…
AP Invoice Interface Errors & Fix
INVALID DISTRIBUTION ACCT DUPLICATE LINE NUMBER ZX_IMP_TAX_RATE_AMT_MISMATCH &nb…
Supplier Information including GST
The below query fetches all the supplier information including GST Info select (select name from hr_operating_units where organization_id=jpr.org_id) Operating_…
API to Create Bank Account Details
Below procedure will create Supplir Bank account details PROCEDURE create_banks_accounts(p_change_type VARCHAR2) AS ln_count &…
TDS Register Report
Below query will display all TDS related details for GST implementations SELECT aia.invoice_date, aia.doc_sequence_value voucher_num, aia.invoice_…
RO Partywise Account Ledger Report
Below Query will fetch Vendor Wise RO Account Ledger details SELECT * FROM (SELECT asp.vendor_id, aia.gl_date accounting_d…
GL DRILL Down Query From GL into Cost Management Sub ledger Module.
The below query will provide the complete breakup of posted Journals with transaction distribution wise breakup details of Fixed assets(receiving,inventor…
GL DRILL Down Query From GL into Fixed Assets Sub ledger Module.
The below query will provide the complete breakup of posted Journals with transaction distribution wise breakup details of Fixed assets(all sources) sub-l…
Drill Down Query From GL into Project Accounting subledger
The below query will provide the complete breakup of posted Journals with transaction distribution wise breakup details of Project Accounting sub-ledger m…
Mass Update For Supplier Invoice Match
Update the match_option column in AP_SUPPLIERS table. No Oracle API is available to change the Invoice Match Option from one value to another. This has to…
Lockbox – Error Handling
Submit postQuickcash for records struck in the interface. Query with Transmission Name & check Submit postQuickcash then submit. Receivables -> Lockbox Interfac…
Query to find Suppliers/ Sites with 'Receipt' match option.
SELECT aps.vendor_id, aps.vendor_name, DECODE(aps.match_option,’P’,’Purchase Order’,’R’,’Receipt’) matc…
Highlight Data in different colors Using CSS
Use the below code in report query to highlight data with different colors based on conditions CASE &…
Check Data type using JavaScript
Use the below script to perform duplicate data validation along with numeric data type validation in Javascript. <!–duplicate checking for lpn –> <script> funct…
Check duplicate data using Javascript
Use the below Javascript to identify duplicate data – In this sample code, when user enters order number in the tabular form, an error will be thrown if the ord…
ISO Receipt Register -GST Query
SELECT shipment_num, shipped_date, stnno, receipt_num, rcvd_date, send_orgid, send_orgname, recv_orgid, recv_org_name, gst_…
Query to Fetch Invoice details, HSN Code, Third party Registration number and GST Details.
The below query fetches the details of an Invoice along with the HSN Code of a respective item present in an Invoice. GST details can also be fetched. SEL…
Return To Vendor Summay-GST
The below query fetches the data of purchase order wherein those items present in purchase order needs to be returned to Supplier because of some damage caused…
TAX INVOICE RCM(REVERSE CHARGE MECHANISM) – GST
The below query fetches the details of a tax invocie where in Reverse charge mechanism(RCM) is applied in order to claim the extra tax paid by an organiza…
Delivery Challan with GST Details
Query retrieves data of those inventory items which are used for promotional purposes or given as a sample items for a sales executive in order to promote the p…
Oracle Fusion HCM Data Loader (HDL) Keys Significance
HCM Data Loader (HDL) Keys Supported Key Types: HCM Data Loader (HDL) supports 4 different types of keys as listed below GUID – Oracle Fusion Global Uniqu…
Oracle Apps(EBS) – AR Receipt Register Query with Bank statement Header and Line Details
Below query is useful when you required Non Misc Receipts Along with Bank Statement Header , Line Details and Activity name ( like Receipt Write off) SELE…
Query to get the Project Expenditures Defined for an Employee for a Specific Period
SELECT EXPT.EXPENDITURE_TYPE , EXPI.Quantity , CASE WHEN (EXPT.unit_of_measure=’HOURS’) THEN NVL(CDL.PROJECT_BU…
JDeveloper Installation and Setting Environment
Prerequisites Desktop with 1.5 GB RAM 1.Telnet and FTP access to apps and db server 2.Database connectivity details: 3.Apps username…
ORA-01792 maximum number of columns in a table or view is 1000 FROM mtl_parameters
Symptoms:- Concurrent program completed warning with “ORA-01792 maximum number of columns in a table or view is 1000 FROM mtl_parameters” this reaso…
Enabling Create/View Accounting from Toolbar on Receipt Summary Form for a Custom Responsibility
How to Enable Create/View Accounting from Toolbar on Receipt Summary Form for a Custom Responsibility Goal:- What are the steps to enable Create/View Accounting…
Workflow download and upload commands in Oracle apps
Workflow upload WFLOAD <apps/pwd>@<connect_string> 0 Y {UPLOAD | UPGRADE | FORCE} <filepath>[<file_name.wft>] Example: WFLOAD apps/pwd@<connect_string> 0 Y UPLO…
Convert the Amount in to the Word using Function in Oracle Apps EBS R12
Convert the Amount in to the Word using Function in Oracle Apps EBS R12. Function: CREATE OR REPLACE FUNCTION APPS.Get_amount_to_word(P_LC_AMOUNT IN NUMB…
Find the query of Receivable (AR) for the Invoice Number (TRX_NUMBER) Wise, Customer wise, Sales Order Wise, Transaction Date and GL Date Wise in Oracle Apps EBS R12
Find the query of Receivable (AR) for the Invoice Number (TRX_NUMBER) Wise, Customer wise, Sales Order Wise, Transaction Date and GL Date Wise in Oracle Apps EB…
CSS Plan Creation – Inventory Receiving Options Migration
CSS Plan Creation Process for Migration Configuration: Inventory – Receiving Options Steps 1. Connect with Server wherein the CSS…
CSS Plan Creation – Inventory Organization Configuration
CSS Plan Creation Process for Migration Configuration: Inventory Organization Steps 1. Connect with Server wherein the CSS (…
Script to update natural account
CREATE OR REPLACE PROCEDURE XX_GL_EXTRACT_ENABLE_ACCOUNT AS v_set_id number; v_position_account_type number:=3; v_acc_flex_value_set &…
Script to replace junk characters
This script can be used to remove junk characters from a string CREATE OR REPLACE FUNCTION xx_Replace_Junk_Chars_func (p_string IN VARCHAR2) RETURN…
Script to Update Preparer/ Requester in Requisitions using API
Get data to be modified from customer and load it into a staging table. Use this table in the below script to update Preparer/ Requester for PO Requisitions. DE…
Upload Catalogue item Images in iProcurement
Upload catalogue item Images in iProcurement …
Enabling Link to access Excel file in iProcurement
Enabling Link to access Excel file in iProcurement …
An Overview of Oracle Process Manufacturing – A Functional Guide to Setup & Process
What is Oracle Process Manufacturing? How many modules are covering Oracle Process Manufacturing? What are the Major Functionalities in Product Development? Is…
Update/Add lines in Global Blanket Purchase Agreement using "Upload" Functionality in Oracle EBS
Introduction Procurement Team would like to use “Upload” or Excel sheet options to update the Global Blanket Purchase Agreement (GBPA). Using the functiona…
SQL to Find Employee – Supervisor Hierarchy details
Background The Purchase order or Requisitions are not valid unless it is approved. In any of the procurement process, most of the issues occur in the approval o…
Query for finding MTD/YTD/ITD Cost/Revenue and Fee Details for a Project in PA Module
WITH PARAMETER AS (SELECT :PERIOD_NAME AS PERIOD_NAME FROM DUAL) SELECT distinct prj.project_id,prj.segment1, (SELECT ROUND(SUM(tot_burdened_cost),2) &nb…
GL Transactions for Payments
SELECT DISTINCT aca.payment_method_lookup_code trans_type, TO_CHAR (aca.check_number) invoice_num,  …
GL Transactions for AR
SELECT rctt.description trans_type, TO_CHAR (rct.trx_number) invoice_num, TO_CHAR (rct.trx_date) invoice_date, &…
GL Transactions for AR Adjustments
SELECT NULL trans_type, ent.transaction_number invoice_num, (SELECT TO_CHAR (trx_date) FROM ra_cus…
GL Transactions for AP
SELECT /*+ leading (ael aeh) */ aia.invoice_type_lookup_code trans_type, ent.transaction_number invoice_nu…
Extract for Payments – Check and EFT
SELECT papf.employee_number employee_number , aps.vendor_name payee_name…
Steps to Create Excel for Apps Report
GL Wand needs no desktop installation and it is very user friendly for creating many financial reports. It is a very good reporting tool for Oracle E-Business s…
ERROR ORA -00980 Synonym Translation no longer valid
Error Message : ORA -00980 Synonym Translation no longer Valid. Error Area : it is related Database (Table/view) not related to forms When it happen…
OATS Installation issue – Struck/Hang for Long Time
Installation Issue : ‘ Spawning C:OracleATSbindeploy.bat C:OracleATSinstalloats.ear’ and never completes for Long Time ( even for Days) When it Hap…
Create a User in Oracle Load Testing (OLT)
How to Create a User for ORACLE Load Testing LogIn Clink on Start Button ? Oracle Application Testing Suite ?Administrator Login with Administrator User as show…
XML Publisher Report in Oracle Transportation Management (OTM)
Please follow the below steps to create XML Publisher Report in Oracle Transportation Management: Query Template Format Template Report Adding to SHIPMENT_MANAG…
TOOL-TIP FOR THE REPORT COLUMN
The tool-tip displaying report column while hovering on the another report column. STEP 1: l ENAME, DEPARTMENT and MANAGER is the report Colum…
Performance Using hint
Performance issue. The issue was due to joins across remote sites where oracle optimizer …
Query to find open invoices with the Supplier and PO details
Query to find open invoices with the Supplier and PO details SELECT i.invoice_num “Invoice Number”, …
OpenReceipt Report DescriptionThis Open Receipt Re…
Open Receipt Report Description This Open Receipt Report is used to review a list of receipts which are in open status for the date range that you specify…
Learning Pentaho It’seasy to start and learn Penta…
Learning Pentaho It’s easy to start and learn Pentaho on its own. In-build sample code and easy to connect various database with drag and drop option base on Ja…
AR Selected InvoicesPrintDescriptionQueries t…
AR Selected Invoices Print Description Queries to get AR Selected Invoices. /* Adjustments */ SELECT line_adjusted adj_line, tax_adjusted ad…
PurchaseOrder (With Requisition Preparer) D…
Purchase Order (With Requisition Preparer) Description To fetch the Purchase Order along with Requisition Preparer det…
Accounts Payable Trial BalanceReportDescriptionAcc…
Accounts Payable Trial Balance Report Description Account Payable Trial Balance Report meant to verify that total accounts payable liabilities in Payables equal…
Payables AccountingEntries ReportDescriptionTherep…
Payables Accounting Entries Report Description The report lists in detail any transactions that have been accounted with error and all entries that could not be…
TrialBalance – Detail Report DescriptionOracleGene…
Trial Balance – Detail Report Description Oracle General Ledger Trial Balance report is used to satisfy legal, statutory, and internal management reporting…
Radio group with dynamic help text in Oracle APEX 4.2 using CSS
Objective: To show dynamic help text in radio group item in Oracle APEX 4.2 using CSS. Solution: Step 1: Create Radio group item. Step 2: Use below query in rad…
Page refresh button in Oracle APEX 4.2 using JS
Objective: To refresh oracle APEX application page using JavaScript. Solution: If you want to create a page refresh button inside your APEX page you need to add…
Report Layouts and Report Queries in Oracle APEX 4.2
Objective: To create PDF, Excel, RTF, HTML reports using report Layouts and report Queries in Oracle APEX 4.2. Solution: Step 1: Go to Shared Components Step 2:…
Multiple sheet excel report template generation in Oracle BI Publisher 10g
Objective: To create template for generating multiple sheet excel in Oracle BI Publisher 10g. Solution: Template Creation – EXCEL Step 1: Excel contains the bel…
Read and Import XLSX file in Oracle APEX 4.2
Objective: To read and import XLSX file in Oracle APEX 4.2 Scenario: Customer wants to see their investor details in APEX (in the form of IR). Though investor d…
Download Blob Files from Oracle Table in APEX using PL/SQL
Objective: To download blob files (ZIP Files) from table in APEX using Oracle PL/SQL. Scenario: Customer has asked us to give the provision in APEX, which is us…
Excel Download Package in Apex
Step 1: Create below Package in DB. Package Spec create or replace PACKAGE doy_PK_APEX_EXCEL_DOWNLOAD IS TYPE query_res IS…
Custom_Popup_page_APEX 4.2
Step 1: Create a new page with popup template (Page No -2175). Step 2: Create 2 region in old (Calling) Page and write the below scripts in region source.…
Upload and Download of Files from Server
Upload and Download of Files from Server Step 1: Create a File browse item. Step 2: Create a directory in database. Step 3: Create a procedure to upload file in…
LAZY LOADING
Requirement: Lazy loading in Oracle Apex Step1: Create a page Step 2: Create a report region Step 3: Create a hidden page item Ex : P1_HIDDEN Step 4: Create on-…
Multiple indexes for same column
Multiple Indexes SQL>CREATE TABLE t3 (col1 NUMBER, col2 NUMBER); TABLE created. SQL>CREATE INDEX in_t3 ON t3(col1); INDEX created. SQL>CREATE INDEX in_t3_…
AP Invoice On Hold Report
AP Invoice On Hold Report Description Invoice on Hold Report is used to review detailed information about invoices on hold. You can submit the Approval process…
Oracle 12c accessible by clause
PACKAGE Helper authid Definer accessible BY(Good_Unit,Bad_Unit)ISPROCEDURE p;END Helper;PACKAGE body Good_UnitISPROCEDURE pISBEGIN Helper.p(); …END…
Asset RetirementsReportDescriptionAssetRetirements…
Asset Retirements Report Description Asset Retirements Report is used to review the assets you retired for the Book and accounting Period range you choose. The…
Multiple Interactive Reports in Single Page Oracle Apex 4.2
Step 1: Create one interactive report in one page. Step 2: Create other interactive report in second page. Step 3: Create a HTML region in the first page with s…
Uninvoiced Receipts ReportDescriptionUninvoicedRec…
Uninvoiced Receipts Report Description Uninvoiced Receipts report is designed to provide a preview of all accrual entries that will be posted to your account in…
Multiple sheet excel report in Oracle BI Publisher 10g using data template SQL Query
Objective: To generate multiple excel sheet report from OBIP using data template SQL queries. Scenario: Customer needs multiple sheets excel report from OBIP. W…
Generate xls, xlsx file in Oracle Directory Using Oracle PL/SQL
Objective: To generate xls, xlsx file in Oracle Directory using Oracle PL/SQL. Scenario: Customer asks us to generate excel report in oracle directory itself; f…
Generate csv file in Oracle Directory Using Oracle PL/SQL
Objective: To generate excel (csv) file in Oracle Directory using Oracle PL/SQL. Scenario: Customer asks us to generate excel report in oracle directory itself;…
Generate DBF file in Oracle Directory Using Oracle PL/SQL
Objective: To generate Database File (DBF) in Oracle Directory using Oracle PL/SQL. Scenario: Customer asks us to generate database file format report in oracle…
Data template in Oracle BI Publisher 10g
Objective: To generate reports from OBIP using data template SQL queries. Scenario: Customer has given a requirement to develop the report in OBIP. While develo…
Scroll to the top of the page/report in Oracle APEX
Objective: To Scroll the top of the page/report in Oracle APEX application. Scenario: We have encountered an issue that you always stay on the bottom of the pag…
APEX Reports with dynamic help text in Oracle APEX 4.2 using CSS
Objective: To show dynamic help text in APEX reports (Interactive/classical) in Oracle APEX 4.2 using CSS. Solution: Step 1: Create Interactive report Step 2: U…
Generate DDL source code using Oracle SQL
Objective: To generate DDL source code using Oracle SQL. Scenario: Customer asks us to generate all the DDL source code for some database objects. So I just fou…
Split by count excel report in Oracle BI Publisher 10g
Objective: To create a template to split records in excel sheet in Oracle BI Publisher 10g. Solution: Template Creation – EXCEL Step 1: Open the Excel. (See bel…
Stop the user to click a page submit button more than once in Oracle APEX 4.2
Objective: To stop the user to click a APEX page submit button more than once in Oracle APEX 4.2 Scenario: The standard way to push the page data back to the AP…
Call Oracle package in Oracle BI Publisher 10g
Objective: To call Oracle package in Oracle BI Publisher 10g. Scenario: Oracle BI Publisher is just a reporting tool. From which we can download excel, csv, xml…
Suppliers Report (XML)DescriptionSuppliersReport
Suppliers Report (XML) Description Suppliers Report (XML) is used to review the supplier, supplier site and supplier contacts details. 1. Query Fetching Vendor…
AccountPayables Remittance Advice DescriptionAPRem..
Account Payables Remittance Advice Description AP Remittance Advice, a document that describes payments that are being made. The person or company that is makin…
Invisible columns
Oracle Database 12c supports invisible columns, which implies that the visibility of a column.&…
Row limiting using FETCH FIRST
For Top’N’ queries , Oracle Database 12c introduces a new clause, FETCH FIRST,&nbs…
Include or Exclude column in APEX interactive report download
Include column only in download Hidden column cannot be downloaded in interactive report if, Display As : Hidden Include In Export : Yes This can be achieved us…
MINIMUM DATE AND MAXIMUM DATE FOR A DATE PICKER IN A TABULAR FORM
MINIMUM DATE: The Minimum date can be set for a date picker in the tabular form using the following steps. 1. Set SYSDATE as the source value to an item Using P…
Displaying Classical Report in Full page with Less Columns
Step 1: Create classical report with less number of columns in a page. Step 2: Go to the page header, in CSS under Inline CSS mention the code as table.uReportC…
Breadcrumbs Using Page Items
According to the customer’s requirement, there is a table which contains both parent parts and child parts in one table. Once we create a part, a link must be a…
Calculating Sum of 2 or More Fields in Oracle Apex
Step 1: Create all the items which you need to calculate Step 2: In items level Element à HTML Form Element Attributes à Call a function on Key up onkeyup=”tota…
Displaying of a select list and a button based on conditions in one Tabular Form Column
Requirement: For already existing records in data base a select list must display, and for newly added records a button must display for deleting the newly adde…
File Type Validation using Java Script
Step 1: Create a File browse item. Step 2: Create a button to submit the page. Give the Action when Clicked as Defined by Dynamic Action. Step 3: Create a Dynam…
Dynamically ordering of rows
Step 1: Create a tabular form with the query and add a null column at last Step 2: Report attributes à NULL column à HTML Expression <img id=”down_#ROWNUM#” sty…
Modal Dialog in Apex 4.2
Requirement: A modal dialog has to open on button click in Oracle Apex 4.2. Step 1: Create a page Step 2: Create a HTML static Region and cre…
Function to convert Item quantity from one UOM to another UOM
Oracle Standard function APPS.INV_CONVERT.INV_UM_CONVERT can be used to convert quantity from one UOM to another UOM. Eg: To convert from primary UOM to Base UO…
Query to get transactions of inventory items with available onhand quantity
SELECT recs.doc_type, recs.item_number, recs.doc_no, recs.po_number, recs.organization_id, recs.inventory_item_id, recs…
Query to get current onhand quantity of inventory Items.
SELECT ALL mtl.organization_id, orgs.NAME organization_name, mtl.inventory_item_id, mtl.segment1 item_number, &n…
Payroll Employer Costs Extract
SELECT SUM(peevf.screen_entry_value) FROM pay_element_types_f petf ,pay_element_entry_values_f peevf ,pay_element_entries_f peef ,per_all_assignments_f paaf ,pa…
Query to extract iProc Smart Forms
SELECT DISTINCT STORE.NAME store_name, smartforms.template_name request_name, pas.commodity_id, pas.vendor_name supplier, (SELECT NAME FROM apps.hr_operating_un…
AP Supplier Banks Payments Query
SELECT pvs.org_id org_id, iep.default_payment_method_code, alc.lookup_code, hou.NAME org_name, pv.vendor_id supplier_id…
Oracle EBS Table Suffixes
You have lot of tables ending with different suffixes in Oracle Apps database. Did you ever wonder what these denote? Listed below are the different table suffi…
How to programmatically close PO using PO_ACTIONS API
How to programmatically close PO using PO_ACTIONS API PO_ACTIONS.CLOSE_PO is the API used to programmatically close/final close Purchase Order. We have to pass…
Create Bulk GL Code Combinations Using API
DECLARE CURSOR c1 IS SELECT a.rowid row_id,a.* FROM temp_gl_code a WHERE process_flag= ‘N’; &n…
Sales Order details with India Localization tax
SELECT h.header_id header_id, party.party_name sold_to, h.order_number order_number, NVL (TO_CHAR (h.order…
Query to get opening,purchasing,receive,sales and closing quantity of items
SELECT * FROM (SELECT b.item_code, b.item_description item_description, b.primary_uo…
APEX 5- Using Images as an Top Navigation Menu
Overview: 1. Purpose. 2. Benefits. 3. Steps. 4. Screen shots. 5. Conclusion. 1. Purpose: This document describes how…
APEX – Reports with Row Details
Overview: 1. Purpose. 2. Benefits. 3. Steps. 4. Screenshots. 5. Conclusion. 1. Purpose: This document describes how t…
Deep Linking in Oracle APEX :
Deep linking refers to the ability to link to an Oracle Application Express page out of context . When you link to a page out of context and the application req…
Word Wrap in APEX IR:
One of the common problems with APEX IR is word wrap. Add the below code in Column formatting in column attribute: <table style=“word-break: break-all; wid…
APEX – Report Rows Clickable to detailed page
Overview: 1. Purpose. 2. Benefits. 3. Steps 4. Conclusion. 1. Purpose: This document describes how to make a Apex Reports click…
Freeze Columns in Tabular Form/ Report (Classical Report)
Today I would like to share “How to Freeze/Fix Column Header Row of a Classic Report” which I found very useful when the number of report columns/rows are…
Item Category Conversion Categoriesare the method …
Item Category Conversion Categories are the method by which the items in inventory can be separated logically and functionally for planning, purchasing and othe…
Customer Item Cross Reference Conversion
Customer Item Cross Reference Conversion A Customer Items are Item Numbers used by customer for the items you sell to your customers. Customer Item numbers are…
Query to get the Outstanding Amount of Customers in Project Accounting
SELECT ar.customer_number “CUSTOMER NUMEBR”, ar.customer_name CLIENT, (arp.amount_due_original – arp.amount_due_remaining ) “PAID AMOUNT”, …
Shell Script to Migrate our Technical Components during Migration
Shell Script to migrate all technical components in a single step The below query will help us to migrate all the components from one instance to another instan…
performance of functions -12C
Oracle Database 12c includes two new features to enhance the performance of functions when called from SELECT statements. With Oracle 12c, a PL/SQL subprogram c…
Calculate PO Balances
— Query to retrieve customer PO details SELECT obha.order_number, obha.sold_to_org_id, obha.cust_po_number, obha.header…
Using Source Code Control in Oracle SQL Developer
Overview Oracle SQL Developer is a free graphical tool that enhances productivity and simplifies database development tasks. Using Oracle SQL Developer, you can…
External Table
External tables allow Oracle to query data that is stored outside the database in flat files. The ORACLE_LOADER driver can be used to access any data sto…
In-Database Archiving
In-Database Archiving Oracle Database 12c introduces In-Database Archiving to archive the low priority data in a table. The inactive data remains in the da…
BI Publisher Advanced RTF Template Techniques Images, Charts and Graphs
Images and Charts Oracle BI Publisher supports several methods of including images or charts in an Oracle BI Publisher report. • &n…
Customer PO Conversion
Customer PO Conversion Description This blog is used to create a Customer PO conversion. This…
Query for Approval groups
SELECT pcr.control_rule_id, pcga.control_group_name, pcr.object_code, pcr.rule_type_code, pcr.amount_limit, &nbs…
Query for Approval Assignments
SELECT hou.NAME operating_unit, pj.NAME job, pcf.control_function_name, pcga.control_group_name, TO_CHAR (…
How to rectify FUN_INTER_PAY_NOT_VALID error in create accounting
During month close an error with invoices/receipts/transactions “FUN_INTER_PAY_NOT_VALID” is a blocker for the close. When this error occurs f…
Query to find list of suppliers who has attachments
Often times you might have to find a list of suppliers who has a specific attachment. For example, if you need to find list of suppliers who has attached with a…
We are hiring "Oracle Apps DBA"
Search bar for Tabular form with auto complete option for Date values
Requirement: Tabular form Search bar Challenges faced: On default, in tabular form Oracle apex didn…
Shrinking of Navigation Menu on page load
Requirement: Navigation menu should be shrinking on page load. Challenges faced: In Oracle Apex&nb…
Displaying of 7 Consecutive Dates in Report Header
Step 1: Create a date field for selecting the date. Step 2: Create a report for displaying …
Removal of close button from Select2 plugin
Requirement: The users who completed their tasks in selected Delivery PDVD must not be deleted&…
HOLD / UNHOLD in APEX Report
In Report Attributes, Call below function in link – URL function fun(a) { //alert(a); var str = a.substring(4); &…
Key Controlled Report View in APEX
Every application all over world is in fond of key controlled or having shortcuts to…
Global Alert Box in APEX
GLOBAL ALERT BOX is developed as we need customized alert boxes all over application. A&nb…
Full View/ Normal View of Tabular Form Columns in APEX
Step:1: Create relation between table header and table…
Dynamic addition of data on clicking of link in a pop-up
Step:1: On Click of tabular form column of parent report, execute below javascript code to …
Conditional Link in APEX Tabular Form
Conditional link is the common requirement …
Conditional Alert / Comments on Hover
Conditional Alert / Comments on Hover is common requirement, In Old days, it is common&nbs…
Comments Differentiation in Oracle APEX
Comments Differentiation is some recent requirement as new advancements in CSS been implemented.&nbs…
Blank Row Restriction in Oracle APEX
Blank Row Restriction is to avoid unwanted addition of rows in detail report (tabular Form…
OAF Personalization in Special Information Type
We got a requirement to enable only the specific SIT to be displayed in custom responsibility. Generally wh…
Query to get Unearned Revenue Error details selec…
Query to get Unearned Revenue Error details select customer, account_number, invoice_no, &n…
/*Sample script to fetch the Receipt Register alon …
/*Sample script to fetch the Receipt Register along with its TDS and WCT details */ SELECT apps.gl_flexfields_pkg.get_description_sql  …
/* Sample script to fetch the Invoice Register with tax details */
/* Sample script to fetch the Invoice Register with tax details */ SELECT glcc.segment1, glcc.segment2, glcc.segment4, glcc.segment5…
Query to fetch Customer Statement of Account Detai …
Query to fetch Customer Statement of Account Details SELECT rc.customer_name “Customer_Name”, rc.customer_number “Custo…
Query to get the details of invoice with tax info…
Query to get the details of invoice with tax information’s select ctx.customer_trx_id, case …
Add Row on the Top in the APEX Tabular Form
Step 1: Give the Add row button, Action when Button Clicked as Defined by Dynamic ac…
Avoid wrapping of fields in APEX Interactive reports
Due to many columns in the report the data will be wrapped automatically. Resolution: 1. In&nbs…
Sending Calendar Invite via APEX Application
With the below code we can send individual emails to the users with an ICS file …
Download hidden column in APEX classic report
Hidden column cannot be downloaded in classic report if, Display As : Hidden Include In E…
Manual Pagination In Oracle Apex
Step:1 : Create items to set count of rows and display item to represent paginatio…
Losing Data During Validation In Apex Tabular Form !!! – Check In
Step:1: On Submit, Before Computation and Validation – Insert into Collections. BEGIN …
Field Locks In Oracle Apex
Step:1: On-change of items, insert into locks table. Dynamic Action : On-Change &nb…
Third Party Process – Colombia
This page explains the steps involved in generating the Colombia Third party report balances. This process ensures the passage of the third party details to GL.…
Electronic File Transfer in AR – Brazil – Technical Information
Companies in Brazil use bank services to collect customers. The Brazilian Electronic File Transfer for Receivables (CLL F034) provides the functionality to elec…
Brazil Electronic File Transfer in Receivables
Companies in Brazil use bank services to collect customers. The Brazilian Electronic File Transfer for Receivables (CLL F034) provides the functionality to elec…
Excel Merge and Download using Custom Template in APEX
For Merging cells in Excel and for other CSS in download property, i.e to create&nbs…
Get Filtered Columns and Values in APEX Interactive Report
In order to get the filtered columns and their corresponding values in APEX Interactive Re…
Open Script – DataBank – CSV or Text File
Open Script – DataBank It is used to Provide input data to Open Script at run time from a Text File. Click on the below link to read the docum…
Open Script – DataBank – Database Table
Topics 1.DataBank – DataBase Table Use 2.How to Run Iterations Objective:- How to Use DataBase Table…
Discrete Job Migration Along With PO
Discrete Job Migration Along With PO Script CREATE OR REPLACE PACKAGE BODY APPS.XXEUR_CREATE_DISCRETE_JOB AS PROCEDURE main(errbuf …
Open Script – Dynamic Login URL
Dynamically Reading Login URL From Input Dialog Box please click on below link to read the document  …
Intercompany Goods In Transit Journal Entry
Intercompany Goods In Transit Journal Entry Overview Goods In Transit (GIT) will come into picture when goods sent by Sender Organization is not received at Rec…
Intercompany AR Receipt Creation & Application
Intercompany AR Receipt Creation & Application Overview An automatic receipt should be created and applied to the AR transaction when receiver org creates the p…
Intercompany AP inovice creation
Intercompany AP Invoice Creation Overview When procurement happens between inter-organizations, AP invoice needs to be created from receiver organization. This…
Bank Statement To Wire
Bank Statement To Wire Overview · Bank Statement is uploaded automatically from the bank file ·  …
Customer Refund Using Auto Lockbox Overview This c …
Customer Refund Using Auto Lockbox Overview This customization make payment to the customers whoever is having outstanding balance to be paid in which date the…
OPM Batch – Release and Unrelease
OPM Batch Release : The Release_Batch API checks for the validity of the batch validity rule, and ingredient reservations to create transactions. It…
Oracle Flow…
Oracle Flow …
Oracle Test Manager
…
Report Carousel in APEX 5 UT [ Dynamic Content ]
Report Carousel in APEX 5 UT [ …
Notification Message Bar design change in Oracle APEX 4.2 using CSS
Objective: To change the “Notification Message Bar” design throughout the Oracle Application Express 4.2 application. Requirement: Customer has aske…
Highlight Tabular Form Rows on addition from Pop-up
To Highlight Tabular Form Rows on addition from Pop-up: 1. Paste the following code in the dynamic action: On Refresh of regio…
Set Interactive Report Filter with URL Parameters
Interactive Reports are very powerful, the end user can set filters, highlighting, add computed columns, do control breaks or groupings and much more. As a Deve…
Billing Process in Brazil
The billing process in Brazil has the following steps as there are localizations and also the government involved in approving the sequence numbers for the AR t…
Employee Creation in Oracle
Employee creation in Oracle involves the following steps and APIs 1. Create Person Hr_employee_api..create_emplo…
Creating Custom Value Sets and inserting to base tables through API
Step1: Create custom valuesets in source instance. Use the below API’s to move the v…
Creating Custom Menus and inserting to base tables through API
Step1: Create custom menus in source instance. Use the below API’s to move the menus…
Enabling Custom DFF in Custom Oracle Forms
We can create a custom Descriptive Flexfield on custom tables and use it in custom fo…
How to set background in Oracle Apex 5 application using CSS
Objective: To set background in Oracle Apex 5 application using CSS (Cascading Style Sheets). Step&n…
Multiple selection in select list item – Oracle Apex 4.2
Objective: To select multiple value from select list item in Oracle Application Express 4.2. Step&nb…
Search on Double Click in IR Report
Place the below code in HTML Header: <script type=”text/javascript”> $(function(){ $(‘.apexir_WORKSHEET_DATA td’).…
DDL operation while execution
While query execution we cannot do the ddl operation in the earlier versions From 11g we&n…
Copy / Paste Tabular Form Report
Copy / Paste Tabular Form Report is typical requirement as User will Copy From Excel …
Tabular Form Text with MultiSelect Option
Step 1: Create a tabular form. Step 2: In Page Header add the JQuery Library File. <script&nbs…
Progress Bar in Reports in APEX5
APEX can create “Percentage Bars” within a report. This can be acheived using HTML Code&nb…
Mail Level Approval Process
Direct Mail Approval is the trending process as we don’t need to go back to applicati…
Key Controlled Report View
Every application all over world is in fond of key controlled or having shortcuts to …
Cascading – Text With Auto-Complete in Tabular Form
Text with Auto Complete is most common requirement and can be done normally for item level. Bu…
User Hooks in Oracle HRMS
User Hooks There were many times we need to put some extra logic before or after happening of some business event. In Such cases, we use user hook API. It is a…
eAM – Asset Number Conversion
eAM – Asset Number Conv…
Progress Bar in Reports in APEX5
APEX can create “Percentage Bars” within a report. This can be achieved using HTML Code&nb…
The ultimate guide for skinning ADF table
In ADF, Most common used component is table. Previously, I searched for so many blogs,&nbs…
JQuery Id selector in ADF
Some times we have a requirement like have to write some css changes based on id …
ADF Menu | MenuBar | commandMenuItem Skinning
Drop down menus are a common requirement for web applications, In html we can create …
Create Responsibility API
A responsibility is an important configuration which allows the user to navigate to the various menus and form functions within that responsibility.Oracle API i…
Creating Lookup and Lookup Values through API
The custom lookup parameters can be passed to the below API’s to insert data into Oracle standard tables fnd_lookup_values and fnd_lookup_types  …
11i to R12 Table Changes
Accounts Payable 11i R12 PO_VENDORS AP_SUPPLIERS PO_VENDOR_SITES_ALL AP_SUPPLIER_SITES_ALL PO_VENDOR_CONTACTS AP_SUPPLIER_CONTACTS AP_SUPPLIER_INT_REJECTIONS AP…
Oracle HRMS – Create Employee API
This blog describes the process and technical information required for creating new employee using API. In any business organization employee should there to do…
Create, Update Organization API
This blog describes the technical information required for creating Inventory organization and their classification using API.In order to create Inventory organ…
Query to Link PO, AP, GL, ZX, CE, RCV & XLA
SELECT glimp.je_header_id je_header_id, glimp.je_line_num je_line_num, xal.accounted_cr acco…
Query to Link PO,AP,GL,TAX RATE & XLA
SELECT r.je_header_id je_header_id, r.je_line_num je_line_num, 200 application_id, aeh.ledger_id set_of_books_id, ‘INV’ trx…
Steps to Controller Extension in OAF Page
To extend the OA Framework page you have 2 options, extend the VO or extend/create the controller. Extending a VO is on a global level and you pick this option…
To Resolve error “JSP files must reside in the server root directory or a subdirectory beneath it”
The error “JSP files must reside in the server root directory or a subdirectory beneath it” occurs during compilation if the JSP files are located in other than…
Enable Oracle apps Diagnostics for certain users
Steps 1 Navigate to System Administrator responsibility> Profile> System> Steps 2 Enter profile name: Utilities: Diagnostics Enter Application User for whom you…
Steps to Export OAF personalization across Instances
This article explains the personalization steps and how to export the personalization file from one instance to another instance. Screen 1 Provide the document…
OAF Search page
1. Create a New Workspace and Project Right click Workspaces and click create new OAworkspace and name it as Sr…
Assignment Set Conversion
Assignment Set: Once sourcing rules has been defined, you must assign them to particular items and/or organizations. These assignments are grouped together in a…
Sourcing Rule Conversion
Sourcing Rule: Sourcing Rule Provides list of approved suppliers where we can rank the suppliers on a percentage basis. This is useful for the buyers/requestors…
Lot-Wise Onhand Availability across Inventory Orgs
Standard functionality in the SO Form, provides the On-Hand visibility of an Item at the Specific Inventory Org (Ship From Org) level. In case the Visibil…
Displaying images in a Report
Purpose: This document shows user how to include images in report &…
Role based Authorization in Oracle APEX5
Objective: To implement Role based Authorization in Oracle APEX Application, in version 5. Application&nb…
Apex.server.process
This function calls a PL/SQL on-demand process defined on page or application level. It’s an wrapper of the jQuery.ajax function and supports all the setting th…
Create / Update Location API
We define a location in order to identify the exact address of an organization or employee work location. Locations can be local as well as global. Local locati…
Create Bank and Bank Branch Using API
When performing Master Data Migration for Supplier or Customer business also needs the migration of Associated Banks and Bank Branches.In order to perform this…
Restrict Copy Paste & Right Click In Apex Page
PURPOSE: The Purpose of this component is to restrict the copy option from the user level&…
Creating Interactive Report as Excel format with applying font for particular column values
PURPOSE: If we want to show any report in excel format in page level with the differe…
Search Bar in Tabular Form
PURPOSE: The search button in tabular form is helpful to identify the exact data in the&nb…
Multiple File Upload in Oracle APEX
This method supports the ability for file inputs to accept and upload multiple files into datab…
Oracle Forms – Stacked Canvas creation over the Content Canvas
This article explains the technical aspects of developing a Stacked Canvas in Oracle Forms. Canvas is a surface inside a window container on which we pla…
Validating the Custom(User Entered) Exchange Rate
Validating the Custom(User Entered) Exchange Rate à Need to Enable the setup in the Payable Options for Enabling the Exchange Rate or Functional Amount Columns…
LOV Changing Using Custom.pll
à Need to Create a new Procedure in the custom.pll à Need to find the Standard Lov name form the application by placing the cursor on the particular field form…
Dynamic Logo Chaining in BI Publisher Report
Dynamic Logo Chaining in BI Publisher Report Need to Created a Lookup where we can map the required Logo image names Need to create a formula column in re…
Invoking On-Demand Alert from FND_REQUEST.SUBMIT_REQUEST
We can invoke the On-Demand Alerts from PL/SQL programs using the Concurrent Program submission API. Steps provided below: Consider the below Periodic al…
Oracle Periodic Alert Setup
Periodic Alert: Periodic alert, checks the database for information according to a schedule you define. In a periodic alert specify the following: • A SQL Selec…
Oracle WIP_Repetitive Schedules_Setup Steps and Process Flow in R12.2
Repetitive Manufacturing in Work in Process Table of Contents? What is Repetitive Manufacturing? Setups required to perform Repetitive Manufacturing Transaction…
ORACLE WORK IN PROCESS SETUP DOCUMENT IN R12.2
Table of Contents? STEP 1: Setup Prerequisites Oracle Inventory Oracle Bills of Material Oracle Engineering Oracle Cost Management Oracle Project Manufacturing…
Datafix Script to clear the RCV stuck pending transactions for Receipts
Scripts used for clearing the RCV stuck pending transactions Scenario: While receiving or correcting a PO, some time the transactions get stuck with out Process…
Bills Of Materials Setup Document with Costing
PROFILE OPTIONS IN BILLS OF MATERIALS Profile Option User System Administrator Requirements User User Resp App Site Required? Default Value BOM: Check for Dupli…
Discrete Jobs End to End Cycle with Cost Roll-up in Standard Cost Organization in R12.2
STEP 1: CREATE ITEMS Responsibility: Inventory Navigation: Items > Master Items Select the Organization as “M1 – Seattle Manufacturing“ Create the below items;…
Blanket Sales Agreement with Approvals and Release in Oracle R12.2
Setups Required: 1. Transaction Type: Create an order transaction type. Sales Document type should be “Sales Agreement“. Fulfillment flow: Sales Agreement – Gen…
Back to Back Sales Order Cycle Setups and Process Flow in R12.2
Back to Back Orders in Oracle Setups Required: Step1: Create Transaction type. Use Order Fulfillment flow as Order Flow – Generic. Use the Line flow and validat…
ADDING A FAVICON TO APEX
A Favicon (Favorite Icon), also known as a Shortcut Icon, Web site Icon, Tab Icon&nb…
DYNAMICALLY ADDING ROWS IN TABULAR FORM IN ORACLE APEX 4.2
A good web design involves the better user interaction and ability to fetch the data …
RUPEE SYMBOL IN REPORTS IN ORACLE APEX 4.2 USING HTML
Most of the Applications includes Accounts Reports like Employee Payroll Reports, Company Purchase&n…
COORDINATE MAPPING IN ORACLE APEX
Coordinate Mapping: Line coordinates are used to specify the position of a line just as po…
HIGHLIGHT MANDATORY ITEMS THROUGHOUT THE APPLICATION
Here is how to highlight form text items, which Value Required attribute is set to “Y…
DYNAMIC DATE RANGE IN APEX DATEPICKER
Using this method you can set the range of selectable dates by entering a Minimum and…
HOW TO PLAY VIDEOS IN ORACLE APEX 4.2
Viral advertising is a proven technique that will assist to enhance your enterprise internet&nb…
CAPTURE LOG (LOGIN, LOGOUT, SESSION, SYSTEM) DETAILS IN ORACLE APEX 4.2
To Capture User Login Details: User Log Reports provides the detailed information about the use…
SLIDESHOW OF IMAGES IN APEX
Websites that include many images, such as art or photo gallery sites, can benefit by …
DISABLING KEYS USING JAVASCRIPT IN APEX
Application customization, by using JavaScript “Key Disabling” can be achieved, thereby preventing t…
Item Code as Qualifier and Percentage Discounts based on Item Code
Item Code as Qualifier in Advanced Pricing Business Case: A World leading soft drinks manufacturer in Vietnam likes to implement Advance Pricing to incor…
Move Order Approval Process in R12.2
Move Order Approval Method through Planner. Before we learn about Move order, let us check the only setup for the move orders in organization Parameters. Create…
Security Hierarchy functionality Setups and Flow in Oracle Purchasing
Using Security Hierarchies Security hierarchies let you control who has access to certain documents. For example, you can create a security hierarchy in which o…
Unable to View or Add any Items in Sales Orders
&nbs…
ACCOUNTS RECEIVABLES -TRANSACTION AND RECEIPT…
ACCOUNTS RECEIVABLES -TRANSACTION AND RECEIPTS CREATION Creation Date April 15, 2015 Last Updated &n…
Table of ContentsSupplier Creation in Oracle Pa…
Table of Contents Supplier Creation in Oracle Payables Overview of Suppliers : Supplier Creation procedure: A) General information: B) Address Book Page: C) Con…
Withholding Tax
Table of content: 1 Background ………………………………………………………………………………………………………… 2 1.1 Definition ……………………………………………………………………………………………………… 2 1.2 Purpose ………………
iProcurement Setup Document and it's basic flows in R12.2
iProcurement Setup Document Mandatory Setups for iProcurement: Profile Options Shopping Category Creation Category Hierarchy Top Category (Browsing Category) –…
White Paper on Advanced Approval using Approval Management Engine – AME
“If you want something new, you have to stop doing something old”- as Peter F. Drucker says many organizations are stop doing old and keep innovating new techno…
AR Transactions and Tax Query – India Localization
SELECT TO_CHAR (trx_date, ‘DD-MON-YYYY’) ar_posting_date, TO_CHAR (jatt.excise_invoice_date, ‘DD-MON-YYYY’) excise_invoice_date, &nbs…
BI Publisher Report with Excel Template
Step1:Concurrent Program Create a Concurrent Program with Exceutable File Name as XDODTEXE Step:2 Data Xml Template Create a Data Xml Template for the Query Ste…
Region Interface – Shipping
Interface Tables: WSH_REGIONS_INTERFACE WSH_REGIONS_TL_INTERFACE Base Tables: WSH_RE…
How to enable the MOAC parameter “Operating Unit” for a Concurrent Program
You can notice that the new field “Operating Unit” which is new field in R12 and by default is in disabled mode:- With the introduction of multi-org access cont…
Defaulting Requisition Sourcing details using Sourcing Rules
Sourcing Rules in Oracle R12: You can define sourcing rules that specify how to replenish items in an organization, such as purchased items in plants. Sourcing…
UOM and Conversions
Define Units Of Measure: Define units of measure, unit of measure classes, and base units of measure for tracking, moving, storing, and counting items. You can…
Contract Purchase Agreement Creation Process in R12.2
Definition: Contract Purchase Agreements in Oracle EBS You create contract purchase agreements with your suppliers to agree on specific terms and conditions wit…
Sale Quote Configuration with Creation and Approval process in R12
Oracle Sales Quote – Approval – Setup Oracle Sale Quote is the system that offers excellent opportunity for the sales guys to create/follow up and play with the…
Allow Negative Quantity functionality in Shipping Process
Purpose: The purpose of this Document is to provide some troubleshooting guide for issue where sales orders are not getting backordered even though insufficient…
Price Tolerance Percentage and Price Tolerance Amount Functionality in Purchase Orders – R12.2
GOAL: Understanding the Price Tolerance Amount functionality in Oracle Purchasing. Exercise 1: Price Tolerance (%) options in Purchasing. Step 1: Raise a requis…
Order Management Under Shipment Tolerance functionality while splitting the order lines in R12.2
Remained Order Line gets cancelled after we split the Order line in shipping tranasaction form and ship the line. This document makes you understand the…
Updating Unit List Price in Blanket Sales Agreement in R12.2
Updating Unit List Price in Blanket Sales Agreement Exercise 1: Firstly we will try to create a Blanket Sales Agreement. Responsibility: Order management Super…
Group Shipments functionality of Autocreate in R12.2
This document provides information on Group Shipments functionality for Auto created Purchase Documents. New feature in R12.2 + related to Group Shipments…
AP Pre Payments Query: AP XLA GL
SELECT glcc.segment1 COMPANY, glcc.segment2 LOCATION, glcc.segment3 COST_CENTER, glcc.segment4 ACCOUNT, glcc.segment5 PRODUCT_LINE, …
AP Distribution Query: AP XLA GL
SELECT glcc.segment1 company, glcc.segment2 LOCATION, glcc.segment3 cost_center, glcc.segment4 ACCOUNT, &nb…
AR Adjustments Query: AR XLA GL
SELECT glcc.segment1 company, glcc.segment2 LOCATION, glcc.segment3 cost_center, glcc.segment4 ACCOUNT, &nb…
AR Transactions Query: AR XLA GL
SELECT glcc.segment1 company, glcc.segment2 LOCATION, glcc.segment3 cost_center, glcc.segment4 ACCOUNT, &nb…
AR Receipt Query: AR XLA GL
SELECT glcc.segment1 company, glcc.segment2 LOCATION, glcc.segment3 cost_center, glcc.segment4 ACCOUNT, &nb…
AR Receipt Distribution Query: AR XLA GL
SELECT glcc.segment1 co, glcc.segment2 loc, glcc.segment3 cc, glcc.segment4 acct, glcc.segment5 prod, glcc.segment6 chan,&nb…
Report Builder Components
Report Builder Components 1. Data Model 2. Layout Model 3. Object Navigator 4. Report Triggers 5. Parameter Form 6. Program Units 7. Attached Libraries Data Mod…
AIM Document Specifications
AIM Document Specifications: 1. Business Process Architecture (BP) 2. Business Requirements Definition (RD) 3. Business Requirements Mapping 4. Application and…
How to find error messages while doing Oracle workflow activities from backend.
Finding error messages while doing workflow activities from backend. Approach: Find a workflow internal name &nb…
Setting Timeout for a Notification in Oracle Workflow
Requirement: Timeout period should be 48 hours for a notification. Approach: 1. Go to particular Notification Label 2. Select the properties 3. Select the Node…
Displaying subtotal and grand total based on the group column in BI Publisher Template
For Example Display the sub total values based on Account code and show grand total end of the report. The report has only one group (G_ACCT) and group co…
Implementing Search Functionality using LOV during query in Oracle Forms
Requirement : Enable ‘Execure-Query’ using an LOV field (non-database items) . Approach: When user clicks on CTRL+F11 we need set dynamic where clause. No…
Parsing Comma Separated string in WHERE Clause of SQL Statement in Oracle Forms
In some cases, we get a comma separated string as output (say from another select statement or block) that we would need to pass to the IN clause of a Select st…
How to change a LOV query Using Forms Personalization
We can change the LOV query using form personalization. Sample provided below (Payment form). Requirement: We have to change the “type” field lov qu…
Problems while creating the New Locations in Oracle R12.2
Problems while creating the New Locations in Oracle R12 ERROR DETAIL: Not able to Create the new Locations as well as Update the existing Locations in Oracle. E…
General Ledger Primer Oracle Genera..
GENERAL LEDGER PRIMER ORACLE GENERAL LEDGER PROCESS 10/8/2014 Srinivasa Ramanujam Contents ? Manual Journal Entry Journal Approval Process Daily Rates Entry Cal…
AMEX Payment Process
DOYEN SYSTEMS PRIVATE LIMITED AMEX PAYMENT PROCESS CONFIG AND TESTING DOC Doyen 11/20/2014 [Type the abstract of the document here. The abstract is typically a…
Inventory Module Setup in Oracle EBS R12.2
What is Oracle Inventory? An Oracle Inventory Module is a facility where you store and transact items. Before you can use Oracle Inventory, you must defin…
Trigger Enhancements in Oracle Database 11g Release 1
· Execution Order of Triggers · Compound Triggers · &…
Session State Protection in APEX
Session State Protection is a built-in functionality that prevents hackers from tampering with the URLs within your application. URL tampering can adversely aff…
Clearing Session State in APEX
As you develop your applications, you may find it useful to clear the cached value for specific items, all items on a page, all pages in an application, or the…
Multiple User Interface for Oracle APEX
User Interface User Interfaces · Within a single Application you can have Desktop and Mobile Pages with individu…
Integrating With Fusion Application Using Services (APEX)
Fusion Applications provides web services that allow external systems to integrate with Fusion Applications. There are two types of services: ADF services and c…
MTL Material Transactions Interface
Solution : This blog explains the technical approach for creating an automated interface to accept user supplied material transactions from External Systems and…
Daily Exchange Currency Rates Interface
Solution : URL will be passed as parameter where we can identify the currency rate conversion amount. FROM_CURRENCY , TO_CURRENCY and the date for when it has t…
Oracle Order Management Setup Document in R12.2
Definition: Oracle Order management is one of the most important part of the oracle EBS. The Order Management Application provides many flexible features enabli…
IR & ISO – Internal Sales Order Cycle with Setup Steps in R12.2
Definition: Internal Requisition/Internal Sales Order provide the mechanism for requesting and transferring material from one inventory organization to other in…
RMA – Return Material Authorizations in R12.2
Return Material Authorizations in OM Order Management provides sophisticated tracking of your return material authorizations (RMA). Returns from a customer occu…
PO Return/Cancellation Issue – Primary quantity XX exceeds available subinventory quantity YY
Issue and Resolution Document for PO Returns and Cancellation Issue 1. Issue: Buyers are created the Purchase Orders with wrong items and they have submitted fo…
BSA – Blanket Sales Agreement and Releases in Oracle R12.2
Definition: Blanket Sales Agreements are used when you have specific characteristics related to a purchasing agreement between a customer and a supplier. These…
Back 2 Back and Dropship Order Process in Oracle R12.2
This order type applies when customer orders a product which you do not stock or manufacture. In turn customer places the order, you place the order with suppli…
ADC – Automatic Document Creation in Oracle Purchasing R12.2
Concept: ADC is created for the documents like Blanket Purchase Agreement, Approved Quotes, and Contract Purchase Agreements to Release the Purchase Orders auto…
Physical Counting in Oracle Inventory – R12.2
PHYSICAL COUNTING IN ORACLE EBS R12.2 Counting is used to verify the System On hand Quantity with Actual (Physical Qty) Quantity of the item and perform the adj…
BPA With Allow Price Override and Price Limit Functionality – R12.2
Allow Price Override and Price Limit in BPA Case 1: Checking Allow Price Override Step 1 – Create and Approve BPA: Responsibility: Purchasing. NAV: Purchase Ord…
Un-Ordered Receipts in Oracle Purchasing – R12.2
Un-Ordered Receipts Allow Unordered receipts allows to receive the goods without a PO. But these goods can only sent to receiving area and not for the sub-inven…
Substitute Receipts in Oracle Purchasing – R12.2
Substitute Receipts This option can be used when we can receive the substitute item. Means we can receive item B, where as we ordered item A. Firstly for this w…
Express Receipts in Oracle Purchasing – R12.2
Express Transactions The express transaction function is a quick method of entering receipts and receiving transactions. Step1: Create a PO. Create a purchase o…
Cascade Receipts in Oracle Purchasing – R12.2
Cascade Receipts The Cascade function facilitates the distribution of a given quantity of an item and UOM from a single supplier across multiple shipments and d…
Blind Receipts in Oracle Purchasing – R12.2
&nbs…
MANUAL SHIP CONFIRM PROCESS IN ORDER MANAGEMENT ORACLE EBS R12.2
MANUAL SHIP CONFIRM PROCESS IN ORDER MANAGEMENT ORACLE EBS R12.2 Concept: Once the pick release and pick confirm process is over, the details will be assigned t…
PO Approval Issue – Workflow Error at "Is Document Complete" Activity
PO Approval Issue – Workflow Error at “Is Document Complete” Activity Issue: One Purchase Order (PO# 549131) have been created without the “Supplier” and “Suppl…
BPA – Blanket Purchase Agreement with Cumulative Pricing
Cumulative Pricing in Blanket Purchase Agreement Cumulative: Price Breaks will be applied to the cumulative quantity on all released shipments for the item. Ste…
Blanket Purchase Agreement (BPA) with Minimum Release Amount Functionality
Minimum Releases in Blanket Purchase Agreement Step 1 – Create Items: Responsibility: Purchasing Super User Navigation: Items > Master Items. Create two differe…
How to Remove Error Workflow Notifications from Work list
How to Remove Error Workflow Notifications from Work list Issue: In some of the Oracle users Work-list, will have multiple notifications with Error messag…
OAF – Creating Dyanmic Table Region in ProcessFormRequest
Creating a Dynamic table region and attaching the VO to this dynamic Table region in ProcessFormRequest method of controller. Consider the requirement, If…
Useful SQL Queries – Oracle EBS
Query to find Runtime, Status and Argument of a Concurrent Request SELECT fcp.user_concurrent_program_name, fcr.requested_by, fcr.request_id, …
Excel Template for BI Publisher Report
This blog explains the process and steps to develop BI data templates with excel. Steps in Brief: Create xml data template. Create concurrent program wit…
Min-Max Planning Method in Oracle Apps – Planning Level: Organization, Source Type: Inventory, Item: Buy
Planning Level: Organization, Source Type: Inventory, Item: Buy Responsibility: Inventory Navigation: Items > M…
Min-Max Planning Method with various Scenarios in Oracle Apps
Order Import Issue – Item with inventory_item_id=XXXXXX is not defined in Item validation Org XXX
IR-ISO – Order Import Issue Issue: Approved Internal Requisitions are not converted into Internal Sales Orders. Description: In Purchasing Responsibility, an In…
Adding Condition While Calling the Bursting Program
In the Below Program we would be restricting the Mails for which the outstanding balance is Less that Zero. Statement Generation Program Bursting AR Statement P…
Sending E-mail with Excel Attachment Using UTL_SMTP
The utl_smtp package is used to send e-mails from any database to a valid recipient/receipents with or without attachements.The generic code is given to send e-…
Setting VO query dynamically in OAF
Generally while creating the View Objects we define a query to it. While rendering the OAF page these view objects get executed (either by framework or by the j…
Performance Issue – Hierarchical query LEVEL
Sample Query with Performance Issue: SELECT progress_date, LEVEL slno FROM &nbs…
Load XML data into Oracle table
Steps to Load XML File to Oracle Tables Solution : Sample XML file has been used for illustration Step 1 : Create a directory using the below command. The below…
Credit Check Functionality in Order Management
1) Overview: The ultimate goal of Credit Management processes is to minimize the financial risk that your organization assumes as a result of day-to-…
Water Mark Text in XML Publisher report
This blog outlines the steps involved to include the Watermark Text to XML reports. Let’s assume a base template as below : Step 1: Create a xml tag for t…
Oracle Application Express 4.0 EA2
Oracle Application Express 4.0 Early Adopter Phase II (EA2) is now available. Fill out the survey and request a NEW workspace on oracle’s hosted instance @ …
Oracle BI Publisher Introduction
Oracle XML/BI Publisher is a very good reporting tool, which outputs reports into many for…
Oracle BI Publisher Desktop
Making the relationship between Microsoft word and BI Publisher will be very convenient for&nbs…
Apex Interactive Report Cosmetics
To Change the Report Field Title color .apexir_WORKSHEET_DATA th div {font-size:12px; color:black;…
Hide the Report Column based on User Condition
Get the interactive repot table id Example : var apexform = $x{interactive report tablei…
Append the Data Base value to Tabular Form Read only Column
Get the interactive repot table id Example : var apexform = $x{tableid};Mention the rows…
Configuring APEX to use BI Publisher
To use BI Publisher as your Report Server, first, access the Report Printing parameters:Go …
Append the Data Base value to Tabular Form Select List
Ajax(Application Process) used to Get the value from Data base Return value as node …
Apex used to call the Oracle JOB scheduler and Java
l Create the html link on apex l Ajax(Application Process) used to call the DBMS_SCHEDULER …
Report Generation from BI through APEX
Oracle APEX is user friendly for designing part as well as client side data entry and…
Create Customize POPUP LOV for APEX
Javascript Used to Get the Tabular Form Id Example : var eleTables = document.getElementsByTagName(“…
Dynamically Generate the Checksum id to solve the apex vulnerability problem
Ajax(Application Process) used call the Oracle Query of APEX_ITEM.MD5_CHECKSUMJava method of Math.…
To put the Company logo in each APEX page
l Go to Shared Components then click the images link and upload the image to applicat…
BI Bursting
One of the very good features of Oracle BI Publisher is its ability to burst and …
Table Partitioning
Every data’s should be stored in database, because world can’t run without storing the dat…
AP Invoice liability (Trial Balance) Query: R12 AP GL PO RCV XLA
SELECT NVL (f.currency_code, d.invoice_currency_code) currency_code, d.invoice_amount, d.wfapproval_…
Query to Link Shipments, AP Invoice, GL and SLA Tables: R12 AP GL RCV XLA
SELECT gjh.doc_sequence_value vch_num, gjl.effective_date, xal.currency_code, xal.currency_conversion_rate con_rate, xal.ent…
Query to Link AP Invoices, Payments, GL and SLA Tables: R12 AP GL XLA
SELECT /*+ Index(xdl XLA_DISTRIBUTION_LINKS_N3) */ DISTINCT gjh.doc_se…
Query to Link AP Payments, GL and SLA Tables: R12 AP GL XLA
SELECT SUM (xal.accounted_cr), SUM (xal.accounted_dr), aia.invoice_amount, invoice_num &n…
Query to Link AP Invoices, GL and SLA Tables: R12 AP GL XLA
SELECT SUM (xal.accounted_cr), SUM (xal.accounted_dr), aia.invoice_amount, invoice_num &n…
JBO-25006: Invalid parameter value – Resolution
Error: JBO-25006: Invalid parameter value PosViewShipmentsVO for destination passed to method ViewLinkImpl.setDestination. Explanation: view def…
How to Resolve the Shipping Exception Issue – Schedule Date has been Postponed – Exception Name "WSH_CHANGE_SCHED_DATE"
Issue: Sometimes, we may change the Schedule Ship Dates in the Sales Orders. When we try to do the Ship Confirm for those changed orders, system will not allow…
How to Add or Assign the Printer to a desired Reports
How to Add/Assign the Printer to a desired Report Responsibility: Order Management Super User Navigation: Setup > Shipping > Documents > Choose Printers “Choose…
How to Process the Stuck Purchase Orders with an Pending "Respond to Changes" Action
Issue: Requisitions are created using either from the Core Purchasing or Internet Procurement responsibilities. Then the requisitions will be converted into Pur…
Receiving Transaction Processor errors out with error numbers RVTTH-445, RVTBM-008 & RVTBM-120
RECEIVING TRANSACTION PROCESSOR ERROR ISSUE Title: Receiving Transaction Processor errors out with error numbers RVTTH-445, RVTBM-008 & RVTBM-120 Issue Descript…
PO Workflow Error – Document Manager Failed with Error Number 3 while processing Standard Purchase Order XXXXXX
Reset a Purchase Order Statuses From In Process or Pre-Approved To Incomplete/Requires Reapproval Title: Document Manager Failed with Error Number 3 while proce…
APEX upgrade from 4.0 to 4.2 – Technical Issues
1. Problem: Login issue – unable to login to the Apex application. &nbs…
Useful Discoverer Queries
Query #1 – To List the Discoverer Reports SELECT DOC_ID, DOC_NAME, DOC_DEVELOPER_KEY, &nbs…
Setups for Discoverer Desktop
Setups for Discoverer Desktop The following steps have to be implemented after installation of Discoverer Administrator and Discoverer Desktop: 1. In Discoverer…
Eliminating block.field issue while upgrading from EBS R11i to R12 versions
Eliminating block.field issue while upgrading from EBS R11i to R12 versions Problem In 11i, Customers are accessed through Oracle forms where :block.field…
Oracle Multitenant in Database 12c Enterprise Edition
Oracle Multitenant – an Oracle Database 12c Enterprise Edition option – introduces a new architecture that enables customers to easily consolidate multiple data…
Oracle Application Express in Oracle Database 12c
Oracle Application Express in Oracle Database 12c Oracle Application Express Release 4.2.0.00.08 is installed by default in Oracle Database 12c. One of the majo…