Blog / Category
EBS Technical
558 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…
Oracle EBS OAF vs Forms Personalization
Oracle EBS OAF Personalization vs Forms Personalization – Understanding the Difference Introduction Oracle E-Business Suite (EBS) provides multiple ways to cust…
API to create party and link to the Supplier
Introduction / Issue As part of TCA, the supplier needs to be linked to the party. This API establishes the link between the Party and the Supplier. Why We Need…
Update Order Type in Customer Standard in Oracle Apps
DECLARE l_init_msg_list VARCHAR2 (2000) := fnd_api.g_true; l_cust_site_use_rec hz_cust_account_site_v2pub.cust_site_use_rec_type; l_object_version_number NUMBER…
Query to derive Item Cost
SELECT cict.item_number, cict.description, msi.item_type, cict.PLANNING_MAKE_BUY_CODE, mis.inventory_item_status_code_tl AS item_status, sum(cdcv.item_cost)item…
Fixing Stuck Trips When Delivery Is Closed but Trip Stop Interface Isn’t Triggered (Oracle WMS)
Introduction / Issue In Oracle Shipping Execution (WSH), it’s common to run into a situation where a delivery has been closed, but the associated trip remains o…
Extracting Bank Detail Reports in Oracle Apps R12
Introduction In Oracle Applications R12, supplier bank account details are not stored in a single, easily queryable table. They’re spread across the Oracle Paym…
How to Populate a Placeholder Column Using the Before Report Trigger in Oracle Reports
Blog Structure Introduction Oracle Reports provides Placeholder Columns to store values that are not directly retrieved from the SQL query. These values…
Integrating Oracle EBS with Oracle Apex
Introduction / Issue Organizations often develop custom applications in Oracle APEX to extend the functionality of Oracle E-Business Suite (EBS). However, one c…
WIP Move Transaction
Automating Oracle EBS WIP Move Transactions with PL/SQL Moving a discrete job through its routing — queue, scrap, hold, or across plants — is another WIP activi…
Applying and Releasing Sales Order Holds Based on Customer Item Cross-Reference Commodity Code
Applying and Releasing Sales Order Holds Based on Customer Item Cross-Reference Commodity Code In Oracle Order Management, business rules often require dynamic…
Supplier Master Details in Oracle Apps R12
Introduction: This blog provides a consolidated SQL query that can be used to generate a Supplier Master Details Report in Oracle Apps R12 (Payables). The repor…
Sample Code for AP PO Accrual Write off and Close PO
PROCEDURE main ( errbuff OUT NOCOPY VARCHAR2, retcode OUT NOCOPY VARCHAR2, p_file_name IN VARCHAR2 ) IS BEGIN start_process (p_file_name); — write_output; EXCEP…
Query for AP Invoices Pending for Approval
SELECT DISTINCT aiha.invoice_id, response, aiha.creation_date, ai.invoice_date, (SELECT vendor_name FROM apps.po_vendors WHERE vendor_id = ai.vendor_id) vendorn…
Creation of Bursting using XML Data Template
Step 1: Create XML Data Template <?xml version=”1.0″ encoding=”UTF-8″ ?> <dataTemplate name=”AP_INVOICE_DETAILS” description=”AP_INVOICE_DETAILS” version=”1.0″>…
New Development Report Using XML Data Template
New Development Report Using XML Data Template Introduction XML Publisher (BI Publisher) provides multiple ways to generate reports in Oracle E-Business Suite.…
API to create Quality samples and the results
Introduction/ Issue: This blog contains an API and that can be used in Oracle EBS to create Quality samples and the corresponding results. Why we need to do / C…
Standardizing Custom Table Development for Oracle EBS R12.2 Online Patching
With the transition to Oracle E-Business Suite (EBS) R12.2, the technical landscape for customizations shifted significantly. The introduction of Online Patchin…
Lot Genealogy Report
Introduction: This blog has the SQL query that retrieves the complete lot genealogy, detailing the full lifecycle of product creation. Cause of the issue: Busin…
Quality Test Sample Throughput Report
Introduction: This blog has the SQL query that retrieves all Quality test samples along with their corresponding results. Cause of the issue: Business wants a r…
API to Update Item Status in Oracle E-Business Suite
API to Update Item Status in Oracle E-Business Suite Introduction In Oracle E-Business Suite (EBS), Item Status controls key aspects of how an item behaves acro…
API to Update the Item Categories in Oracle EBS
API to Update the Item Categories in Oracle EBS Introduction In Oracle E-Business Suite (EBS), item categories play a crucial role in organizing and classifying…
Query to find the historical Quantity for an item
SELECT SUM (target_qty) FROM (SELECT segment1, description, mtl.primary_uom_code, moqv.subinventory_code subinv, moqv.inventory_item_id item_id, SUM (transactio…
Pre-requisite for the Sales Orders purging
Introduction In the oracle EBS the concept of Purging and before purging the order management related tables, pre-requisite to be done for the smooth purging pr…
OPM Uncompleted batches query
Introduction/ Issue: This SQL query is designed to extract Batch Header and Material Details from Oracle Process Manufacturing (OPM). It consolidates informatio…
Shipping Purge (Purge SRS) Step by Step process
Step 1: Deploy Oracle SR Package → Description: Apply the Oracle-provided script Compile the package /*=========================================================…
Shipping Process Archival – Step-by-Step
Step 1: Take Table Counts Before Purging Before performing any purge, get the record counts of the affected tables to ensure you can verify the archival later.…
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…
Item Where-Used Analysis Report (Excel – PL/SQL)
Introduction: This blog has the SQL query that can be used for Item where used Report in Oracle Apps R12. Cause of the issue: Business wants a PLSQL excel based…
Procedure to create Quality Test Values alone in OPM through API
Introduction: This blog has the PLSQL query that can be used to create Quality Test Values alone using oracle seeded API. Cause of the issu…
Procedure to create Quality Spec WIP Validity Rule in OPM through API
Introduction: This blog has the PLSQL query that can be used to create Quality Spec WIP Validity Rule using oracle seeded API. Cause of the…
Diagnosing oacore stuck thread Issues in Oracle EBS 12.2
Overview: In Oracle EBS 12.2 environments, it’s not uncommon to face login failures or form loading issues. These are often caused by oacore server anomalies in…
Creating a New Modifier using Oracle API
When there is a business requirement to create a New modifier, we can make use of this code to Register as a Concurrent Program and create a new Modifier after…
Customizing the Hook Package IBY_FD_EXTRACT_EXT_PUB
Oracle Payments provides the IBY_FD_EXTRACT_EXT_PUB extensibility package to construct custom XML element structure that can be added to the payment XML extract…
Delivery is Shipped Status but Order is Not Closed
Delivery is Shipped Status but Order is Not Closed Introduction In Oracle EBS, there are instances where a delivery reaches the Shipped status, but the associat…
Extracting Data for Oracle Supply Chain Planning Cloud
Extracting Data for Oracle Supply Chain Planning Cloud – Steps to Customize the Integration Logic Introduction Oracle Supply Chain Planning (SCP) Cloud i…
Query to extract Active BOL_NOTES for all Active Customers
SELECT hp.party_name “CUSTOMER_NAME”, fdv.category_description “Catergory Description”, fdv.document_id, fdv.title, fdst.short_text, fdst.media_id, oarev.attrib…
Query for Project & Change Order Approval Action History:
SELECT ROWNUM, ACTION_DATE, ACTION, from_user, from_role, to_user, to_role, Details, SEQUENCE, NOTIFICATION_ID, ACTION_TYPE FROM (SELECT ACTION_DATE, ACTION, fr…
API to Load Values in Value Set
API to Load Values into Value Sets DECLARE —————————-Local Variables————————— l_enabled_flag VARCHAR2 (2); l_summary_flag VARCHAR2 (2); l_who_type FND_FLEX_LOAD…
Employee Import API
CREATE OR REPLACE PACKAGE BODY XX_IMPORT_EMPLOYEES_NEW IS PROCEDURE XX_create_employee IS CURSOR Cur0 IS SELECT MESSAGE,ROWID FROM XX_PER_ALL_PEOPLE_F_INT WHERE…
GL & XLA Queries
— GL & XLA Query SELECT glcc.concatenated_segments ACCOUNT, ac.customer_number, ac.customer_name, xlal.currency_code, xlal.accounted_dr accounted_dr, xlal.accou…
Update the Project Status using API
Introduction: This blog has the SQL Script to update the Project Status using API along with each task. Cause of the issue: To update status of the proje…
API Script To Update Supplier Details
This blog has the script to update the supplier details using API AP_VENDOR_PUB_PKG.UPDATE_VENDOR. Cause of the issue: Some of the fields are not visible…
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…
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…
Query to extract modifiers have been applied to a particular sales order
oracle-ebs-modifier-applied-against-so SELECT a.NAME “Modifier Name”,b.INVENTORY_ITEM_ID, b.list_line_no “Modifier Line No”, a.description “Modifier Description…
Query to extract payment terms associated with a customer
oracle-ebs-customer-payment-terms SELECT customer_name, site_use_code, location, payment_term_id, name FROM apps.hz_cust_site_uses_all a1, hz_cust_acct_sites_al…
Mass Update the Customer Matesr Price List
Introduction: We have a requirement when the new pricelist has been created that needs to be updated in the customer master account level and Bill To and Ship T…
Unapply Incorrect Closed Claims
Introduction: We have a Receipt with a “Claim Investigation” where the Claim # was not populated. The below script will help to resolve that issue. w do…
Applying Claim in AR Receipts
declare ln_trx_number VARCHAR2 (50); v_api_version NUMBER := 1.0; — V_init_msg_list VARCHAR2(1) := FND_API.G_FALSE; v_init_msg_list VARCHAR2 (1) := fnd_api.g_tr…
GL Data Extract in EBS for both Receivables and Payables
exec mo_global.set_policy_context(‘S’,:&p_org_id); SELECT DISTINCT gjb.NAME journal_batch_name, gjh.NAME, gjh.default_effective_date gldate, msi.segment1 item,…
Orcale EBS-Query to get active users along with their active responsibilities
LIST OF ALL ACTIVE USERS ALONG WITH THEIR ACTIVE RESPONSIBILITIES Introduction: This query is used to get all active users along with their active responsibilit…
Oracle EBS-Query to get Service Contract Details
Query to get the Service Contract Details Introduction: This query is used to fetch the service contract details.In this we will be passing contract_numb…
Script to upload multiple LDT files for a Concurrent Program/Alert/Form/ValueSet/XML Definations in a Single time
Script to upload multiple LDT files for a Concurrent Program/Alert/Form/ValueSet/XML Definations in a Single time Use-Case: In general during the migrati…
Move concurrent program from One Instance to Another Instance having same name as old instance. Working as same as under old instance same responsibility and request group
Move concurrent program from One Instance to Another Instance having same name as old instance. Working as same as under old instance same responsibility and re…
Inventory Onhand By Location Report
Introduction: This SQL query is used to fetch the data Inventory Onhand By Location Cause of the issue: Business wants a report that details Inventory Onhand By…
Backlog Report With Freight Details
Introduction: This SQL query is used to fetching the data Backlog Orders with freight Details Cause of the issue: Business wants a report that det…
Query to get details of all Concurrent programs used by a Request Set
Introduction:-This script will provide all concurrent programs under request set Code:- SELECT rs.user_request_set_name “Set_Name”, rs.request_set_name “Set_cod…
Country wise Catalog and Non Catalog Po’s in Oracle EBS
Introduction:- Using the below SQL script we can get the Catalog and Non Catalog Po’s details from country wise. Code:- SELECT P.SEGMENT1 “PO Number” ,PL.LINE_N…
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…
Register a printer in oracle
Introduction: This blog is done to register a printer in oracle. Cause of the issue: Printer is not setup in oracle. How do we solve: Go to…
Query to get the Printer Name
Introduction: This query is used to fetch the printer name .In this we will be passing concurrent program id and level value id(user id) as the parameter so tha…
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…
Procedure to Upload Purchase Quotations
PROCEDURE load_purchase_quotation as lv_currency_code fnd_currencies_vl.currency_code%type; lv_verify_flag varchar2 (1); lv_error_message varchar2 (5000); ln_ve…
Item Where Used Extract Query
Introduction: This SQL query is used to fetching the data of FG Items where used details like Component Item, Component Item Description, Parent ,Parent…
Bill of Materials Explosion Extract Query
Introduction: This SQL query is used to fetching the data of Bill of Materials Explosion details like Order Level, Root Assembly, Assembly Item, Assembly…
Service contract reconciliation Report
SELECT okha.sts_code,okha.scs_code,okha.date_approved,okha.estimated_amount, xscb.price_list_id, xscb.customer_bill_to_number, xscb.customer_ship_to_number, xsc…
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…
Tally Integration
Tally Data Integration & Data Migration Tally Integration : Comprehensive and medium-scale organizations use different applications to maintain their business a…
AP Invoice To GL Link Query with additional attribute values
This query to Trace an AP Invoice to the GL. There is an Invoice in AP which is correctly Validated, Accounted, Approved etc. This will useful To find the GL He…
AP Bank details query with additional attributes values
The supplier (or External) bank account information are stored in the table called ce_bank_accounts. The bank and bank branches information are stored in CE_BAN…
Workflow Script Deassign user from role
Oracle Workflow delivers a complete workflow management system that supports business process based integration. Its technology enables modeling, automation, an…
GL Flexi ield Query with additional details
GL module used to maintain credit and Debit details in oracle. Gl code combinations are used maintain details of company with respect to legal entity level. Thi…
AP PO Vendor Creation interface with additional attributes
Payables modules are used to create supplier related information in oracle. In this module we can create supplier invoice bank etc will be maintained. This scri…
Inventory Lot Creation Interface
Inventory module used to create items in oracle. This module will link with AP, PO, GL modules. In inventory we have lot concepts where we will create lots , so…
Setup a new store in Oracle RMS
To Setup a new store in RMS follow the steps given below Step 1: First open the browser and type the given URL: http://shk-oratestapp02.hk.oracle.se:7783/Retail…
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 *******…
Query to get submitted Request set Parameter details
Query: select name,argument_text from (SELECT prog.user_concurrent_program_name || DECODE (req.description, NULL, NULL, ‘:’ || req.description) name, req.reques…
Query to get Request Set Details
Query: SELECT rs.user_request_set_name “Request Set”, rss.display_sequence seq, cp.user_concurrent_program_name “Concurrent Program”, e.executable_name, e.execu…
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 R12 AR Lockbox Query
SELECT hou.name operating_unit, xep.name legal_entity, ala.lockbox_number “NUMBER”, absa.name batch_source, cebb.bank_name, –cebb.bank_number, –cebb.bank_branch…
Oracle SQL Query To Find Square Root For A Number
Syntax: SQRT(number); Query: select SQRT(25) from dual; Output: 5
Oracle SQL Query To Find Power Of A Number
Syntax: POWER(<base number>, <exponent number>) Query: select POWER(3,3) from dual; Output: This function return base number raised to the exponent’s power. 27
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>
Employee Expense Payment Query
In this blog, you will find the SQL query to extract the Employee expense payments in Oracle apps R12.1.3 Query: select aps.vendor_name “Supplier”, aca.AMOUNT “…
Customer Wise Accrual Details in Trade Management
Introduction This Query will help to Extract the Customer Wise Accrual Details in Trade Management in Oracle Apps. SQL Query: SELECT DECODE (qualifier_context,…
Query to Extract the Credit Memo Data
Introduction This Query will help to Extract the Credit Memo Data in Oracle Apps. SQL Query: SELECT CASE rcta.interface_header_context WHEN ‘CLAIM’ THEN ‘TRADE…
PO Receipt Details Query
Introduction This Query will help to Fetch PO Receipt Details Data in Oracle Apps. SQL Query: SELECT pv.vendor_name, poh.ship_to_location, poh.bill_to_location,…
Item Master Extract Report
Introduction This Query will help to Fetch the Item Master Extract Report Data in Oracle Apps. SQL Query: SELECT DISTINCT (SELECT organization_code FROM mtl_par…
On hand Inventory by Revision Report
Introduction This Query will help to Fetch the On hand Inventory by Revision Report Data in Oracle Apps. SQL Query: /* Formatted on 2021/09/06 15:26 (Formatter…
How to change the “Node Manager” Log Level in Oracle WebLogic Server – Version 10.3 and later
How to change the Node Manager Log Level? Log Level for the Node Manager can be set in the “NodeManager.properties” file usually located under “WL_HOME\common\n…
How to disable field based on parameter value
By using following script we can get parameter value in page level. Based on value we can set rendered property for the fields as follows: public void processRe…
How to terminate instance number in Install Base
Using below script we can terminate instance number in Install Base. UPDATE csi_item_instances SET active_end_date = SYSDATE WHERE instance_id = l_instance_id;
Query to handle with zero if no onhand quantities in Inventory or onhand is NULL
Objective: Query to handle with zero if no onhand quantities in Inventory or onhand is NULL SQL: Select NVL(MAX(COUNT(moq.TRANSACTION_QUANTITY)),0) –MAX(…
If else xdoxslt function syntax -RTF
The normal behavior of if-else condition looks like If <contion> then <value> –for true condition else <value> end in RTF it can be achieved using following syn…
Employee Supplier Prepayment Account details extract query
/*Query to get the Prepayment Account details of the Employee Suppliers */ SELECT * FROM ( SELECT DISTINCT pbg.name business_group_name, papf.employee_number, p…
How to add custom/fields PO XML generation
In this blog, We will see how can we add the custom XML segments/fields that can be extended in PO XML output. PO_CUSTOM_XMLGEN_PKG package is the place where t…
PO XML Views in Oracle
In Oracle Purchasing, The PO Output for Communication program is responsible for sending the PO copies to vendors with respect to the communication details capt…
Recurring GL Journals extract query
In Oracle Financials, We have a provision to create a recurring journal in GL application. This blog will helping in extracting that journals. SELECT b.name bat…
AME Rule changes audit queries
In Oracle E-Biz applications Approvals Management Engine(AME) is widely used for approvals in Purchasing,HR modules. Especially an audit enabled systems, We nee…
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…
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…
SQL Query to retrieve User Roles – Fusion Applications
Query: SELECT pu.username,prdt.role_id, prdt.role_name, prd.role_common_name FROM per_user_roles pur, per_users pu, per_roles_dn_tl prdt, per_roles_dn prd WHERE…
Query to get Organization detail in Fusion
Overview : Below query to give the organization information in Fusion Query: SELECT hra.organization_id as bu_id ,hro.name organization_name ,hra.busines…
Query to Extract the IPV Report Data
Introduction This Query will help to Fetch the IPV Report Data in Oracle Apps. SQL Query: BEGIN fnd_client_info.set_org_context (:p_org); END; SELECT glps.set_o…
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…
How to set default value for Message Text Input
By using below command in controller, we can able to set default value to message Text Input. import oracle.apps.fnd.framework.webui.beans.message.OAMess…
Query to Fetch the Unapplied Receipts Report
Introduction This Query will help to Fetch the Unapplied Receipts Report Data SQL Query: SELECT COMPANY COMPANY, CASE WHEN CUSTOMER_NAME IS NULL THEN ‘ **** Uni…
Create Journal on a Spreadsheet Fusion
Please make sure to open the period where you want to post the journal. Let us create the following journal on a spreadsheet- Navigation- Go to General Accounti…
List of Suppliers which have not made payments since 2 years
Introduction: AP-query to get List of Suppliers which have not made payments since 2 years in Oracle EBS either invoice or payment. Query: select distinct a.ven…
Query to get loaded work structure (Grade) in Fusion HCM
Objective: Post doing the conversion in Fusion HCM, it is important to reconcile the match between the conversion records and loaded records. In order to match…
Query to get loaded work structure (organization) in Fusion HCM
Objective: Post doing the conversion in Fusion HCM, it is important to reconcile the match between the conversion records and loaded records. In order to match…
Query to get loaded work structure (Location) in Fusion HCM
Objective: Post doing the conversion in Fusion HCM, it is important to reconcile the match between the conversion records and loaded records. In order to match…
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 ,…
System variable to get current language by default in Oracle Fusion BIP report
Objective : Sometimes having a requirement to build the BIP report to display records other than English (ex: Arabic, French, German etc). In Order to build suc…
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…
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…
Sales Order Details
INTRODUCTION This blog explains how we get complete order details about the for a sales order in oracle apps CODE SELECT OOH.ORDER_NUMBER , OOH.FLOW_STATUS_CODE…
Resolve API error — (hz_cust_site_uses cannot be locked as it has been updated by another user)
Objective: Intent to publish this blog to get rid of this below common API Error while updating the Customer site uses. Error: hz_cust_site_uses cannot be locke…
Query to fetch the Records from AP Expense to GL
Introduction This Query will help to Fetch Records From AP Expense to GL SQL Query: SELECT aerha.invoice_num expense_invoice_number, papf.full_name employee_nam…
Onhand Inventory by Revision Report
Onhand Inventory by Revision Report Introduction This Query will help to generate the On-hand Inventory by Revision Report Script: Step 1: Create Package Spec C…
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…
Autoinvoice Program Error correction Script on Duplicate Invoice Number
Header : Load data into oracle receivables transactions Usage of Heading : Useful to collect amount from customers that has been consumed through invoice number…
Autoinvoice Program Error correction Script on Offset Account and Revenue Account
please_complete_offset_account_ra_interface_lines_all Header : Load data into oracle receivables transactions Usage of Heading : Useful to collect amount from c…
Autoinvoice Program Error correction Script on Rule Start date
Header : Load data into oracle receivables transactions Usage of Heading : Useful to collect amount from customers that has been consumed through invoice number…
Oracle GL Transaction Register Report
Header : Outbound details from oracle general ledger Usage of Heading : Useful to get General ledger transaction details information data Description of post :…
Form Personalization on Purchasing Receipt Standard Form
Header : How to apply Form Personalization on Purchasing Receipt Standard Form Usage of Heading : Useful in the purchasing module in receipt of goods Descriptio…
How to Enable DFF in Bank Account Transfer Page
Header : How to Enable DFF in Bank Account Transfer Page Usage of Heading : Useful in the cash management module in banking page Description of post : DFF means…
SQL Query to Extract the Corporate Card Inter-Company Accounting Details in Oracle APPS R12
SQL Query to Extract the Corporate Card Inter-Company Accounting Details in Oracle APPS R12 Introduction This script will help to extract the complete Corporate…
Understanding XML Bursting in BI Reports
XML Bursting is used to split a single XML file in to multiple XML blocks as the per the defined XML Element. Below are the Steps involved. Generate the XML Fil…
Alternate Options for Using LISTAGG Function
If we have a Requirement to Concatenate the list of values in to a Single Field than we can achieve it with LISTAGG Function. As Listagg Function having Size li…
AR Tax code update API
Introduction: Requested to update the Tax code in customer Ship-to. Solution: We have update the Tax code by using the oracle API. API: DECLARE x_return_status…
Purchase order Detail Report
Introduction: User wants to pull the Open Purchase order details data. Solution: We provide the view to fulfill their requirement. View: CREATE OR REPLACE VIEW…
Program to extend the customer sites into the multiple orgs.
Objective: The below code logic is used to extend the customer sites into the different org. (eg. Suppose customer sites exist in 82 org and the requirement is…
Autoconfig fails after clone error code (adgentns.pl fails: AC-50480: Internal error occurred)
Description: Once we completed clone successfully after that we run the Autoconfig its completed with error. error code (AC-50480: Internal error occurred) Caus…
Approval Limit change extract for any Job/Assignment change
This sql query will be useful to extract the approval limits that are changed for any employee’s job/assignments supervisor change SQL Query SELECT pbg.name bus…
Requisition to Invoice Payment Status extract query
This sql query will help to get the payment status of the requisition , PO , Invoice and its payment status SELECT DISTINCT prh.segment1 req_number, prh.authori…
How to amend Colombian Withholding Tax type certificate footer information
This post will help you to understand, how effectively you can amend the “Colombian Withholding Tax type certificate footer information” There is a standard set…
Supplier Bill To Location Update script
The requirement is to update/attach a new Bill To Location to existing supplier sites. The below script can be used to update the Bill To Location for the exist…
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.…
ORA-01578, ORA-01110 index table block corruption
One of the index table FND_CONCURRENT_REQUESTS_N9’s block is corrupted. Causes of issue: Abnormal server down Steps to solve this issue: 1.Find table name of co…
Custom Form Development – Steps to follow
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…
Customer Profile Update Issue – Query to fetch Customers with No TAX Profile
When the tax profile is missing for a customer, we cannot edit the customer profile details from the Customer dashboard. So to identify such customers where we…
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 – Query to extract Oracle Enterprise Contract Clauses
Cloud Enterprise Contract Clause Query ============================ select ch.contract_number, cab.label||’ ‘||caa.article_title from okc_k_headers_vl ch,okc_k_…
Fusion – Query to extract Customer Details
select hp.party_name customer_name, a1.account_number customer_number, a3.STATUS site_status, SITE_USE_CODE,LOCATION,PRIMARY_FLAG ,PARTY_SITE_NUMBER,A5.ADDRESS1…
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 – Global Human Resources Tables
Global Human Resources Tables EEC_CONTEST_HDR EEC_CONTEST_METRIC_DETAILS EEC_CONTEST_PARTICIPANTS EEC_CONTEST_REWARDS EEC_CONTEST_STAGES EEC_CONTEST_TEMPLATE_DT…
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=”…
EBS – API to Create Bank Branch Account creation in single procedure
CREATE OR REPLACE PROCEDURE INSERT_SINGLE_BANKS IS x_response_rec apps.iby_fndcpt_common_pub.result_rec_type; l_init_msg_list VARCHAR2 (2000); o_bank_id NUMBER;…
Query to Fetch the price adjustment not populated in order lines for the Customer have the Accrual in Trade Management
Introduction: This script will help to extract the price adjustment not populated in order lines for the Customer have the Accrual in Trade Management Script: 1…
Sales Order Release Hold
create or replace PACKAGE xdmc_release_hold_pkg AUTHID CURRENT_USER AS PROCEDURE call_fnd_global ( p_user_id NUMBER, p_resp_id NUMBER, p_resp_appl_id NUMBER );…
API to create Miscellaneous Receipt in Inventory.
Objective. The objective of the below API is used to increase the onhand quantity in Inventory through Miscellaneous Receipt. Sample Code. set serveroutput on d…
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…
Update the Scheduled Ship Date in Order lines Using API
Introduction: This Post illustrates the steps to Update the schedule ship date in Order Lines using API. Script to Update the Scheduled Ship Date in Order Lines…
How to check the record history updates on Vendor Master Data
Using the below query we can track the DML operations made on Supplier’s.It is useful script for Auditing and Tracking purposes. select DISTINCT a.segmen…
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…
Active Inventory Item Query
SELECT ( SELECT organization_name FROM org_organization_definitions WHERE organization_id = msi.organization_id ) org_name, msi.segment1 item_number, msi.descri…
GL TO AR DRILL DOWN QUERY
SELECT gjjlv.period_name “Period Name”, gjb.name “Batch Name”, gjjlv.header_name “Journal Entry For”, gjjlv.je_source “Source”, gjjlv.je_category “Category”, gl…
Concurrent programs run count Weekly breakup query
Pass the from and to date parameter. select USER_CONCURRENT_PROGRAM_NAME,REQUEST_DATE,CNTPERDAY,week_no, case when week_no=1 then sum(CNTPERDAY) OVER (PARTITION…
Create user and add responsibility using API
Introduction This blog explains how to create user and add responsibility for a user from the backend. #Code# DECLARE lc_user_name VARCHAR2(100) := RAJAN_TEST’;…
Enable/Disable IR Download button based on User Role
By default IR region allows to either enable Download button to all users or disable Download button to all users. Suppose if there is a requirement to enable/d…
Cascading Select list in IR Oracle APEX
By default the Interactive Reporting Region allows end users to customize reports. This interactive reporting region allows users to customize the layout of the…
SQL query to get oracle form personalization details
Overview: Below query give the form personalization details. Query: SELECT DISTINCT a.ID, a.SEQUENCE, a.function_name, a.form_name, a.enabled, c.user_for…
GST Number Updation for a Customer Record
PL/SQL Anonymous Block to update Customer GST number: As we do not have any Standard API / Process to update the GST number in Oracle, the only way to correct t…
AP-AR Netting Technical Overview
Oracle AP/AR Netting allows you to net your Payable invoice balances against your Receivables invoice balances for those Customers who are also your Suppliers.…
What does AP Invoice Validation process do Internally when actioned
Invoice Validate is also one another step to ensure/Validate the Invoice Data by the System user before it submits for the Payment. Invoice Validation also vali…
Understanding the Customer Creation Page in TCA perspective
Historically, the customer data model was owned by Oracle Receivables (AR). However, as the data model changed and more complex relationships and attributes had…
CSB related table Info and related Table Status & Queries
Some of the Important Tables related to CSB: iby_trxn_summaries_all – Holds the information related the CSB current batch ( DEBIT_AUTHORIZATION_ID, MBATCHID (Ho…
Possibilities of having Different Receipt Maturity dates in a Single batch
When the Automatic Receipt master program is planned to execute on “28-FEB-20” and when there are a couple of invoices that were created with Due date as “24-FE…
Automatic Receipts and Direct debit related programs
Automatic Receipts and Direct Debit feature: Instead of manually entering receipts, you can use the Receivables automatic receipts feature to automatically gene…
Security Rules extract query
SQL is used to extract the security rules that are defined in the system. The parameter is Accounting Flex Structure SELECT ffvr.flex_value_rule_name, ffvr.desc…
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…
Execution of Prepayment Matching Program (ARPREMAT) Scheduled 12 Hours After AutoInvoice is Completed
The prepayment matching program is the child program of the Autoinvoice import program and it is expected to trigger immediately after the Autoinvoice import is…
Supplier Bank Details
The below is the query which will provide the supplier bank details: SELECT vendor_name supplier_name, segment1 supplier_number, ass.vendor_site_code site_code,…
OAF Date Converter, Formatter and Validation – Reusable Methods
Various Date Converter, Formatter and Validations Reusable Methods in OAF /**=============================================== * Methods related to Date Operation…
Open Accounting Periods Validation
create or replace PROCEDURE xx_open_period ( errbuf OUT VARCHAR2, retcode OUT VARCHAR2, errmsg OUT VARCHAR2, p_from_org_id IN NUMBER, p_to_org_id IN NUMBER ) IS…
AP Invoice Validation
create or replace PROCEDURE XX_VALIDATE_DEBIT_INVOICE(errbuf OUT VARCHAR2, retcode OUT VARCHAR2, p_batch_run_id IN NUMBER, p_org_id IN NUMBER, P_stg_id IN NUMBE…
How to raise bundle exception in OAF
If we want to display multiple error messages in OAF page. We need to use the code as follows: arraylist errmsg = new ArrayList(); if(pageContext.getParameter(“…
GL Budget Upload Interface Program
Introduction: This Post illustrates the steps required to upload the GL budget data using the interface program. Steps Create the Staging Table. Compile the giv…
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…
Supplier with Bank and Tax details query
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.i…
AP Invoice posted register query
select aia.INVOICE_NUM ,aba.batch_name ,aia.INVOICE_AMOUNT ,aia.PAY_GROUP_LOOKUP_CODE INv_group ,aia.INVOICE_CURRENCY_CODE ,aia.CREATION_DATE ,To_char(aia.creat…
API to update Customer Bill to Role
set serveroutput on; DECLARE l_contact_point_rec HZ_CUST_ACCOUNT_ROLE_V2PUB.CUST_ACCOUNT_ROLE_REC_TYPE; l_role_id NUMBER := 0; l_cust_acct_id NUMBER := 0; l_obj…
API to Update Customer Name
SET SERVEROUTPUT ON; SET DEFINE OFF; DECLARE l_organization_rec HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE; l_party_rec HZ_PARTY_V2PUB.PARTY_REC_TYPE; –l_party_obj_ve…
Account Receivable Collection Report
AIM: Bellow query is used for getting the data of AR Collection report for last four weeks in account receivable. Script: select ( SELECT TERRITORY_SHORT_NAME F…
Iexpence Report Query
AIM: This query is used for Iexpence Report query. Script: select APERH.EMPLOYEE_ID ,APERH.INVOICE_NUM EXPENSE_NUMBER ,DECODE(APERH.WORKFLOW_APPROVED_FLAG,’S’,’…
RA Customer transaction in Account receivable.
AIM: This query is used for RA Customer transaction in Account receivable. Script : SELECT hp.party_name CUSTOMER_NAME, rcta.TRX_NUMBER INVOICE_NUMBER, rcta.trx…
AR Receipt in account receivable.
AIM: This query is used for getting the data of AR Receipt in account receivable. Script: SELECT ABA.NAME Receipt_Batch ,DECODE(ACRA.STATUS,’APP’,’APPLIED’,’UNI…
TDS Depot Automation Report
This Query is used for getting the AP invoice for TDS during the current financial Year. Script: SELECT SEGMENT1 SUPPLIER_NUMBER ,VENDOR_NAME SUPPLIER_NAME ,ACT…
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…
SQL Query to get Catalog and Non Catalog Po’s in Oracle apps
Introduction: Using the below SQL script we can get the Catalog and Non Catalog Po’s details from country wise. Script: SELECT TO_CHAR(TO_DATE(p.creation_date,’…
Query to get Bill Code details
Introduction:-This query will provide complete Bill Code and GL String details from Back end Code:- SELECT MSIB.SEGMENT1 Catalog_Item, MSIB.INVENTORY_ITEM_STATU…
Query to get asset details in oracle apps r12
Introduction: This query will provide the complete asset details in oracle apps. We can use this query to develop the custom fixed assets reports in oracle apps…
SQL Query to get CVR rule details from Oracle EBS R12
Introduction: A cross-validation rule defines whether a value of a particular segment can be combined with specific values of other segments.The same details we…
Create a new Request Group using API in Oracle apps
Introduction: Through the below script, we can create a new Request Group from back end. Script: BEGIN FND_PROGRAM.REQUEST_GROUP (REQUEST_GROUP => ‘System Admin…
Oracle Learning Management – Training History report
The sample query can be used to extract the Training history report for an employee in Oracle OLM(Learning Management) Query : Oracle Learning Management – Trai…
Oracle PMS : Employee KRA (Objectives & competencies) report query
The below sample query can be used to extract the objectives , competencies for an employee in Oracle EBS PMS Query: KRA Report (Objectives & Competencies) for…
Employee Absence History Query – Oracle EBS
The below sample query can be used to extract the employee absence history in Oracle EBS. Query: Absence history for an person id, if person id is not given on…
How to delete the XML Publisher report templates and data definitions
XML Publisher report data definitions and data template definitions codes names are unique in nature, if you want to delete the defined templates and definition…
How to create Payroll Assignment Set using Oracle API
The sample script is used to create an assignment set using an API. You can create an assignment from the assignment set creation window in Oracle but you will…
How to delete an element entries using Oracle API
There are scenarios where we are need to delete the element entries created. Here is sample script which can be used to delete(i.e purge, Date track delete mode…
Inventory Cycle Count Report
Introduction: This Post illustrates the steps required to fetch the Inventory Cycle Count Report. Script to fetch the Inventory Cycle Count Details SELECT TO_CH…
Account Alias Issue Using API
Introduction: This Post illustrates the steps required to Process the Account alias issue Using API. Script DECLARE l_transaction_interface_id NUMBER; l_trx_typ…
Get Item Available to Transact Quantity
Introduction: This Post illustrates the steps required to get the Item Available to Transact Quantity in Inventory using API. Script FUNCTION xx_item_availble_t…
Update the Item Status using API
Introduction: This Post illustrates the steps required to update the Item Status using API. Script to Update the Item Status DECLARE p_item_number VARCHAR2 (300…
Send the Email in Autonomous Data Warehouse
Introduction: This Post illustrates the steps required to Send the Email in Autonomous Data Warehouse. Script to Send Email CREATE OR REPLACE PROCEDURE xx_sampl…
Query for Item with BOM Details
Introduction: This Post illustrates the steps required to Fetch Item with BOM Details. Script to Fetch the Item with BOM Details SELECT msi.segment1 item, msi.d…
Receivables Party Site details Extract script in R12
receivables_party_site_details_along_with_location This script useful to extract all the AR party site details with location and account details as well as addr…
Purchase Order meta data details extract in R12
meta_data_po_deatils_on_specific_dates This script is useful to get all the details of purchase order with requisition details item details item category along…
AP invoice Post Register Details Extract Query in R12
ap_invoice_post_register_details_of_specific_org This script is useful to provide details of AP invoice post register details along with PO number check number…
API Delete Orphan Events in AR invoices not in XLA in R12
api_events api_events This is useful script while doing period close on every month basis. Some time there will be no relation will generate between AR invoice…
Payables VMD Data Extract in R12
vmdvendormetadata_extract_query This reusable component very useful in pulling payables vendor metadata that will have all the the information vendor payments b…
AR invoice Number change Script for Brazil Country in R12
invoice-numberchange-script-for-brazil This is useful script that will help for Brazil Country. This script will find the invoice number of Brazil localization…
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 –>…
Customer Bill_To Address in Oracle Apps R12
Please find below query to get customer Bill_To Address SELECT DECODE (hcsua.site_use_code, ‘BILL_TO’, hl.address1 || ‘, ‘ || hl.address2 || ‘, ‘ || hl.c…
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…
How to Delete AP Invoice Data from the Oracle Apps EBS R12.
API “AP_AI_TABLE_HANDLER_PKG.DELETE_ROW” can be used to delete AP Invoice Data. This Script can be used when invoice accounting is not done. DECLARE CURS…
How to Delete ‘INCOMPLETE’ and ‘APPROVED’ Purchase Order (PO) using API in Oracle Apps EBS R12 – API to delete purchase Orders
Below Pseudocode, DECLARE L_RESULT BOOLEAN; L_PO_HEADER_ID NUMBER; — := <PO_HEADER_ID> ; L_TYPE_LOOKUP_CODE VARCHAR2(20); — := <LOOKUP_CODE> ; L_VALIDATI…
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) pivot_char FROM DUAL, csv CONNECT…
Convert the Amount into the Word using Function in Oracle Apps R12
Create or Replace Function AMT_IN_WORDS ( P_TOT_AMT float, P_TRANSACTION_CURR Varchar2) return varchar2 IS InvalidNumberFormatModel EXCEPTION; PRAGMA EXCEPTION_…
How to disable of the Add New Record Button, Clear Button and Delete Button from the Oracle apps Custom Form Oracle Apps R12.
Overview In order to disable New Record Button, Clear Button and Delete Button, Please follow below step Write the code in the Form Level Trigger (WHEN-NEW-BLOC…
How to make DFF field(attributes) uneditable or Disable through Form Personalization
When the Disposition Fields = (‘Complete’,’Accept’,’Accept with Variance’,’Reject’) Then Disable or READ ONLY the DFF Help -> diagnose -> custom code-> personal…
Change group query associated to LOV Using Forms Personalization in Oracle Apps R12
Overview Requirement: We have to change the “type” field LOV query for populating the custom values. To achieve this we have to follow the below process. Step-1…
To display all data for a particular block while opening the EBS form.
Overview Sometimes having a requirement to display all block data whenever form is open. In order to achieve please follow below step, Open Form builder> Open y…
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);
Validation script for block corruption
Introduction: The following script is to validate if there are any corrupted rowids in FND_LOBS table Why we need to do this? When we run this validation script…
Eligible Po Details for Supplier Wise
SELECT * FROM (SELECT POH.SEGMENT1 PO_NUMBER, PRL.RELEASE_NUM RELEASE_NUM, DECODE ( PRL.PO_RELEASE_ID, NULL, POH.clm_document_number, POH.clm_document_number ||…
Advance Shipment Notice details Query.
SELECT DISTINCT RHI.SHIPMENT_NUM, RHI.SHIPPED_DATE, RHI.EXPECTED_RECEIPT_DATE, RHI.FREIGHT_CARRIER_CODE, RHI.INVOICE_NUM, RHI.WAYBILL_AIRBILL_NUM, RHI.NUM_OF_CO…
Query to find the stuck records in Inventory Transaction
Introduction: This Post illustrates the steps required to check the stuck records in Inventory transactions. Scripts: –Unprocessed Material Transactions SELECT…
Query for Fetch the Open Purchase order Data in Oracle EBS
Introduction: This Post illustrates the steps required to fetch the Open Purchase Order Data in Oracle EBS. Script: SELECT pv.segment1 vendor_number, pv.vendor_…
Query for Fetch the Sales History Data in Oracle EBS
Introduction: This Post illustrates the steps required to fetch the Sales History Data in Oracle EBS. Script: SELECT customer_number, customer_name, orga…
Change the Employee User accounts to ‘SSO’ from ‘LOCAL’
Introduction: This Post illustrates the steps required to Change the Employee User accounts to ‘SSO’ from ‘LOCAL’ in Oracle EBS . Script: DECLARE l_success BOOL…
Purge the Employee Records using API in Oracle EBS R12
Introduction: This Post illustrates the steps required to delete the employee records in Oracle EBS HRMS Module. Script: Step 1 –Create Table create table papf_…
Query for Fetch the Invoice lines not transferred to GL in Oracle
Introduction: This Post illustrates the steps required to Fetch the Invoice lines not transferred to GL in Oracle . Sql Script: SELECT rct.trx_number invoice_nu…
Query for Fetch the On Hand Quantity with Available To Transact
Introduction: This Post illustrates the steps required to On Hand Quantity with Available To Transact in Oracle EBS. Script to Fetch the On Hand Quantity with A…
Query for AR to GL in 11i
Introduction: This Post illustrates the steps required to fetch data from AR to GL in Oracle EBS 11i. Script SELECT je_header_id, line_number, trx_number…
Query for Open Work Order
Introduction: This Post illustrates the steps required to Fetch Open work orders in Oracle EBS. Query to Fetch the Open Work Orders SELECT DISTINCT a.job…
Query for Open sales Order
Introduction: This Post illustrates the steps required to Fetch Open sales orders in Oracle EBS. Script to Fetch the Open sales Orders SELECT customer_nu…
Program To Change the Item Cost Groups
Introduction This Post illustrate steps required to Change the Item Cost Groups in Oracle EBS. Script to Change the Item Cost Groups DECLARE p_item VARCHAR2; p_…
API To Create Bank Account Details in Oracle EBS-R12
Introduction This Post illustrate steps required to import bank account details of the suppliers in Oracle EBS R12. Script to import Bank account details for su…
Query for Lease Expired But Not Terminated
Introduction: This Post illustrates the steps required to Fetch Leases which are Expired but not terminated in Oracle EBS R12. Script to Leases Expired but not…
Covert to Operating Unit Timezone
Below is the function to get current operating unit Timezone. FUNCTION convert_to_ou_tz(p_date DATE, p_org_id NUMBER DEFAULT fnd_global.org_id) RETURN DATE IS l…
How to find out correct version of Jdeveloper
You can find correct jdeveloper version using following link. https://support.oracle.com/portal/ Search as Jdev with OA Extension.
Remove Entire column in RTF Template
By using following tag we can remove entire column in RTF. <?if@column:ORG_ID!=XXX?>Sales Order/Cust PO<?end if?>
Sales Order Update
Below is the standard API to update sales order deliver to information based on shipment method. Shipment method & deliver to information are maintaining in cus…
OAMessageChoiceBean
Based on the condition we need to disable message choice in CO. OAMessageChoiceBean DispButtonValue = (OAMessageChoiceBean)webBean.findChildRecursive(“Dispositi…
JPX Import Error
When we do import, sometimes we are facing error as Exception in thread “main” java.lang.NoClassDefFoundError: Then you need to change jpximport.bat and import.…
An Introduction to Report Manager in Oracle EBS R12
Description This article describes Report Manager functionality in Oracle R12 which allows users to primarily publish and store Financial Statements/reports tha…
Query to Fetch Invoice details, HSN Code, Third party Registration number and GST Details.
Introduction: 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 fetc…
Delivery Challan with GST Details
Introduction This Post is Described about the Delivery Challan with GST Details in ebs r12 Query retrieves data of those inventory items which are used f…
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 organization a…
Return To Vendor Summay-GST
Introduction 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 d…
RMA Receipt Query-GST
Introduction This Post Is Described about the RMA Receipt Query-GST In oracle apps R12. select orgid,ORGCODE, ORGNAME, account_number, party_name,city, L…
GL Account analysis for PA Expenditure & Event query
Introduction This Post illustrates the steps required to get the General ledger analysis for PA Expenditure & Event in oracle Apps Script for GL Account analysi…
Fast Index Scan, Index Scan, Partition Range Scan and Full Table Scan
Description: A full index scan is where Oracle reads the data from the index, and the index is in the order required by the query. A fast full index scan is sim…
PDF Password Protection in BI Publisher
Description: This password is required for opening the document. It enables users to open the document only. This property is enabled only when “Enable PDF Secu…
Manual AR Invoice Creation with GST
Description Invoices and Credit Memos in Oracle AR: Invoice: A document that will convey how much the customer has to pay for the goods and services he has boug…
BI Bursting
Description: A bursting definition is a component of the data model. After you have defined the data sets for the data model, you can set up one or more burstin…
Water Mark Text in XML Publisher report
Description: Watermarking enables certain rights over content, which has been successfully applied in multimedia applications. However, it is not trivial to use…
Setting Timeout for a Notification in Oracle Workflow
Description: The below process provides knowledge about how to set timeout for a notification in oracle workflow. Requirement: The timeout period should be 48 h…
Receiving Transaction Processor errors out with error numbers RVTTH-445, RVTBM-008 & RVTBM-120
Description: Receiving Transaction Processor errors out with error numbers RVTTH-445, RVTBM-008 & RVTBM-120 Issue Description: When attempting to take a…
OAF – Creating Dyanmic Table Region in ProcessFormRequest
Description: Creating a Dynamic table region and attaching the VO to this dynamic Table region in ProcessFormRequest method of controller. Consider the r…
Implementing Search Functionality using LOV during query in Oracle Forms
Description: Oracle Fusion applications have support for attachments using functionality delivered by Oracle Enterprise Crawl and Search Framework (ECSF). This…
How to Resolve the Shipping Exception Issue – Schedule Date has been Postponed – Exception Name “WSH_CHANGE_SCHED_DATE”
Description: Sometimes, we may change the Schedule Ship Dates in the Sales Orders. When we try to do the Ship Confirm for those revised orders, the system will…
How to Process the Stuck Purchase Orders with an Pending “Respond to Changes” Action
Description: Requisitions are created using either from the Core Purchasing or Internet Procurement responsibilities. Then the requisitions will be converted in…
How to find error messages while doing Oracle workflow activities from backend
Description: The below workflow help to find out the error messages while doing oracle workflow activities from backend. We are finding error messages while doi…
How to change a LOV query Using Forms Personalization
Description: We can change the LOV query using form personalization. Sample provided below (Payment form). Requirement: We have to change the “type” fiel…
How to Add or Assign the Printer to a desired Reports
Description: Oracle Applications provides you with predefined printer types, print styles, and printer drivers. Use the Printer Types form to query the combinat…
Excel Template for BI Publisher Report
Description: This blog explains the process and steps to develop BI data templates with excel. Steps in Brief: Create XML data template. Create a concurr…
Eliminating block. field issue while upgrading from EBS R11i to R12 versions
Description: Problem: In 11i, Customers are accessed through Oracle forms where: block. Field reference is allowed to bring values in value sets dynamically. Bu…
Highlight Data in different colors Using CSS
Introduction This Post is about to Highlight Data in different colors Using CSS in Oracle EBS R12. Use the below code in report query to highlight data w…
GL DRILL Down Query From GL into Fixed Assets Sub ledger Module.
Introduction This Post is about to GL DRILL Down Query From GL into Fixed Assets Sub ledger Module in oracle ebs r12. The below query will provide the co…
GL DRILL Down Query From GL into Cost Management Sub ledger Module.
Introduction The below query will provide the complete breakup of posted Journals with transaction distribution wise breakup details of Fixed assets(receiving,i…
AP Invoice Interface Errors & Fix
Introduction This Post is about to AP Invoice Interface Errors & Fix in oracle ebs R12. DUPLICATE LINE NUMBERINVALID DISTRIBUTION ACCT ZX_IMP_TAX_RATE_AMT_MISMA…
TDS Register Report
Introduction This Post is About to fetch Vendor Wise RO Account Ledger details in oracle ebs R12. Below Query will fetch Vendor Wise RO Account Ledger details S…
API to Create Bank Account Details
Introduction This Post is About to API to Create Bank Account Details in oracle ebs r12. Below procedure will create Supplir Bank account details PROCEDU…
Supplier Information including GST
Introduction This Post is sabout to fetches all the supplier information including GST Info in oracle ebs R12. The below query fetches all the supplier i…
JBO-25006: Invalid parameter value – Resolution
Description: Error: JBO-25006: Invalid parameter value PosViewShipmentsVO for destination passed to method ViewLinkImpl.setDestination. Explanation: view def mi…
Query to Link AP Invoices, GL and SLA Tables: R12 AP GL XLA
Description: We will discuss about xla tables in r12. We all know that accounting from sub-ledgers transfer to general ledger through the xla /SL module. So to…
Query to Link AP Payments, GL and SLA Tables: R12 AP GL XLA
Description: All accounting performed before transfer to the GL. Accounting data generated and stored in “Accounting Events” tables before transfer to GL. Run “…
Query to Link AP Invoices, Payments, GL and SLA Tables: R12 AP GL XLA
Description: In R12, AP does not store any accounting information. Reconciliation of the posted transactions is done using data in the Sub ledger Accounting (SL…
Query to Link Shipments, AP Invoice, GL and SLA Tables: R12 AP GL RCV XLA
Description: In R12, we have tables that holds link between any sub-ledger data to General ledger data. The below query gives you the important columns availabl…
AP Invoice liability (Trial Balance) Query: R12 AP GL PO RCV XLA
Description: Account payable trial balance is used to verify payable liabilities that are mentioned in the general ledger. This helps the businesses while recon…
Project Expenditure Extraction Query
Introduction This query fetches all the expenses incurred on a project,and also captures the debit and credit account transaction wise that will help in reconci…
Table Partitioning
Description: Every data should be stored in the database because the world can’t run without storing the data’s in the database. Machinery world should save dat…
Query for Supplier details with banks
Introduction This Post is about to Supplier details with banks query in oracle r12. Query for Supplier details with banks. SELECT aps.vendor_name|| ‘ ~ ‘…
GST TAX Details For EBS R12
Introduction This post is about to GST TAX Details query in EBS R12 select decode(substr(h.tax_rate_code,1,4),’SGST’,nvl(h.unround_tax_amt_tax_curr,0)) SGST_AMT…
Query for AR Transactions Header with GST
Introduction This Post is about to AR Transactions Header with GST Query. SELECT DISTINCT rcta.trx_number, rcta.ct_reference, lep.legal_entity_id, lep.NA…
Steps to Controller Extension in OAF Page
Description: 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 pi…
Query for AR Transactions Line with GST
Introduction this query is used for AR Transactions Line with GST Query. Query for AR Transactions Line with GST. SELECT h.trx_uom, h.trx_line_quantity,…
Forms Data Loader Automation Script with Playback option New
Description: Oracle Forms supports record and playback feature. This feature is available in every version of Oracle Forms. When we run Oracle Forms in record m…
Query to Link PO, AP, GL, TAX RATE & XLA
Description: A database query can be either a select query or an action query. A select query is a data retrieval query, while an action query asks for addition…
Query to Link PO, AP, GL, ZX, CE, RCV & XLA
Description: In database management systems, query by example (QBE) refers to a method of forming queries in which the database program displays a new record wi…
Audit Trail Enabling for Employee Tables
Introduction This Post is About to Audit Trail Enabling For Employee Table Enable Audit Trail Profile Navigation: System Administrator à Profile à System…
Creating DFF in a Custom Form
Introduction This Post Is To Creating DFF in a Custom Form. QUERY TO CREATE DFF IN CUSTOMER FORM. 1. Go to Application Developer -> Flex Fields -> Descriptive -…
Create, Update Organization API
Description This blog describes the technical information required for creating an Inventory organization and their classification using API. In order to create…
Oracle HRMS – Create Employee API
Description: This blog describes the process and technical information required for creating new employee using API. In any business organization employee shoul…
Query of 11i to R12 Table Changes
Description: In this article, I am going to cover minimum database object level changes done by Oracle in the upgrade like object obsolete, package level change…
Creating Lookup and Lookup Values through API
Description: 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 *fn…
ADF Menu | Menu Bar | command Menu Item Skinning
Description: Dropdown menus are a common requirement for web applications. In HTML we can create using elements like ul, li, div e.t.c. In ADF, We can achieve u…
The ultimate guide for skinning ADF table
Description: In ADF, Most commonly used component is table. Previously, I searched for so many blogs; I didn’t get any skinning related stuff to fulfill my requ…
Displaying subtotal and total based on the group column in BI Publisher Template
Description: Business information processing requires advanced calculations, including complex ranking, subtotals, moving averages, and lead/lag comparisons. Th…
BI Publisher Report with Excel Template
Description: The below process explains the step by step details about BI publisher report with excel template with screenshots. Step1: Concurrent Program Creat…
Append the DataBase value to Tabular Form Read-only Column
Description: “Tabular format” is simply information presented in the form of a table with rows and columns. Most office productivity software programs. The belo…
Useful SQL Queries – Oracle EBS
Description: To enhance performance, aggregate and analytic functions can each perform in parallel: multiple processes can simultaneously execute all of these f…
Useful Discoverer Queries
Description: One of the distinct steps is to find if all the necessary columns are available in the EUL, and this can be visually done through scanning the Busi…
Setting the VO query dynamically in OAF
Description: Generally, while creating the View Objects, we define a query to it. While rendering the OAF page, these view objects get executed (either by frame…
Sending E-mail with Excel Attachment Using UTL_SMTP
Description: The utl_smtp package is used to send e-mails from any database to a valid recipient/recipients with or without attachments. The generic code is giv…
Performance Issue – Hierarchical query LEVEL
Description: A parts hierarchy, particularly the case of parts in a kit with the kit/assembly having multiple levels. Another example is parts succession, where…
Oracle Test Manager
Description: Oracle Test Manager is an easy to use tool that allows you to organize and manage your overall testing process. It provides a single unified…
Oracle OpenScript
Oracle OpenScript Description: OpenScript is the next-generation environment for developing Oracle Application Testing Suite scripts for Web application testing…
Sending E-mail with Excel Attachment Using UTL_SMTP
Description: The utl_smtp package is used to send e-mails from any database to a valid recipient/recipients with or without attachments. The generic code is giv…
How to query BI bursting
Description: 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…
Report Builder Components
Description: Report Builder is a report creation tool which allows users to create, manage, and publish reports to SQL Server Reporting Services. We can also cr…
Region Interface – Shipping
Description: The post and steps describe the process of Region Interface – Shipping to create the country, state, city, and area Pincode with the help of interf…
Parsing Comma Separated string in WHERE Clause of SQL Statement in Oracle Forms
Description: 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 o…
MTL Material Transactions Interface
Description: This blog explains the technical approach for creating an automated interface to accept user-supplied material transactions from External Systems a…
How to programmatically close PO using PO_ACTIONS API
Introduction This post is about to close PO using PO_ACTIONS API programmatically. PO_ACTIONS.CLOSE_PO is the API used to programmatically close/final close Pur…
Split by count excel report in Oracle BI Publisher 10g
Introduction 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 b…
Data template in Oracle BI Publisher 10g
Introduction Customer has given a requirement to develop the report in OBIP. While developing it, the developer came to know two datasets of SQL queries require…
DBF file Generate in Oracle Directory Using Oracle PL/SQL.
Introduction The customer asks us to generate a database file format report in oracle directory itself; from there customer will download the report. Ste…
Generate CSV file in Oracle Directory Using Oracle PL/SQL
Introduction The customer asks us to generate excel report in oracle directory itself; from there customer will download the report. The report should be in the…
Multiple sheet excel report template generation in Oracle BI Publisher 10g
Introduction This post is about how to create a template for generating multiple sheet excel in Oracle BI Publisher 10g. Template Creation – EXCEL Step 1…
Performance Using hint
Introduction This post is about Performance Using hint in Oracle EBS R12. The issue was due to joins across remote sites where oracle optimizer might Wro…
Extract for Payments – Check and EFT
Introduction This Post illustrates the steps required to Extract for Payments – Check and EFT in Oracle EBS R12. Script to used for Extract for Payments…
SQL to Find Employee – Supervisor Hierarchy details
Introduction 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…
Load XML data into Oracle table
Description: The XmlDocument class is an in-memory representation of an XML document. It implements the W3C XML Document Object Model (DOM) Level 1 Core and the…
Form Personalization to modify the LOV of a Field
Introduction To Change the LOV of the “Tax Category” field in the “Enter transaction India Localization” form In oracle EBS R12 Navigation: AR Super User…
Insert Run Report in Excel for Apps
Introduction This Post illustrates the steps required to Insert Run functionality is used to return the sum, count, max, etc. for the datapoint(columns) in the…
Deploying “Create Employee” API as REST Service :
Introduction This Post illustrates steps Deploying “Create Employee” API as REST Service: in Oracle EBS R12. Go to Integrated SOA Gateway –> Integration Reposit…
Create AR Transaction/Invoice adjustment(API)
Interoduction This Post is about to Create AR Transaction/Invoice adjustment(API) Scope: To apply the adjustment in AR Invoice. (eg. To make AR Invoice amount r…
Personalization at Adjustment/Sales order/Credit Memo application form
Introduction This component can be used for requirements related to personalization where we want to restrict the user to create an RMA/Adjustment/Credit memo i…
How to Update the Web Adi Template
Introduction This post is about How to Update the Web Adi Template in Oracle EBS R12. Purpose As per the business requirement user had requested to add s…
Daily Exchange Currency Rates Interface
Description: URL will be passed as a parameter where we can identify the currency rate conversion amount. FROM_CURRENCY, TO_CURRENCY, and the date for when it h…
AR Transactions Query
Description: The below Post indicates the responsibility to check the AR Transactions Query on AR XLA GL SELECT glcc.segment1 company, glcc.segment2 LOCA…
How to Oracle BI Publisher Introduction
Description: Oracle XML/BI Publisher is a very good reporting tool, which outputs reports into many formats like Word, Excel, PowerPoint, PDF and even to Adobe…
Query to get the details of invoice with tax information’s
Description: Query to get the details of invoice with tax information’s select ctx.customer_trx_id, case when ( hrloc.address_line_1 is not null and ( hrloc.add…
Displaying of 7 Consecutive Dates in Report Header
Description: Displaying of 7 Consecutive Dates in Report Header Step 1: Create a date field for selecting the date. Step 2: Create a report for displayin…
How to JQuery Id selector in ADF
Description: The ADF components will render with some mixed special characters. For this case, if you want to find the id, We need do add ‘\\’ before to the spe…
AR Receipt Query: AR XLA GL
Description: The below steps describe the responsibilities to check the AR Receipt Query on AR XLA GL. SELECT glcc.segment1 company, glcc.segment2…
AR Receipt Distribution Query: AR XLA GL
Description: The below process explains the responsibilities of AR Receipt distribution query on AR XLA GL SELECT glcc.segment1 co, glcc.segment2 loc, gl…
AR Adjustments Query: AR XLA GL
Description: The below post explains the code and step to create AR Adjustments Query of AR XLA GL SELECT glcc.segment1 company, glcc.segment2 LOCATION,…
Append the DataBase value to Tabular Form Select List
Description: Ajax(Application Process) used to Get the value from Database Return value as node and attribute. Example : l_Opt_Xml.firstChild.nodeValue and l_Op…
Append the DataBase value to Tabular Form Read-only Column
Description: “Tabular format” is simply information presented in the form of a table with rows and columns. Most office productivity software programs. The belo…
Period and Daily Exchange Rates Query – FUSION
Introduction This Post illustrates the steps required to Period and Daily Exchange Rates in Fusion Application. Script to Period and Daily Exchange Rates in Fus…
How to query profile options and values API
Description: Profile Option values control the behavior of Oracle Apps; in other words, they determine how Oracle Apps should run. The value for a profile optio…
Query to get opening, purchasing, receive, sales and closing quantity of items
Introduction This post is about to get an opening, purchasing, receive, sales, and closing quantity of items query. Script to get opening, purchasing, re…
Sales Order details with India Localization tax
Introduction This Post is about to Sales Order details with India Localization tax in Oracle EBS R12. Script to Sales Order details with India Localizati…
Update and assign project Roles using PA_PROJECT_PARTIES_PUB
Introduction This Post illustrates the steps required to Update and assign project Roles using PA_PROJECT_PARTIES_PUB in Oracle EBS. Script to Update and assign…
Oracle EBS Table Suffixes
introduction You have a lot of tables ending with different suffixes in the Oracle Apps database. Listed below are the different types of tables. ALL: Table end…
Query to extract iProc Smart Forms
Introduction This Post is about extract iProc Smart Forms query in Oracle EBS R12. SELECT DISTINCT STORE.NAME store_name, smartforms.template_name reques…
Export employee data with personal expenses in i-expense and the expense is not claimed in oracle apps
Introduction This Post illustrates the steps required to Export employee data with personal expenses in i-expense and the expense is not claimed in Oracle EBS R…
Payroll Employer Costs Extract
Introduction This Post is about to Payroll Employer Costs Extract in Oracle EBS R12. Script to Payroll Employer Costs Extract. SELECT SUM(peevf.screen_en…
Update Oracle Project information using PA_PROJECT_PUB.
Introduction This Post illustrates the steps required to Update Oracle Project information using PA_PROJECT_PUB in Oracle EBS R12 Script to Update Oracle Projec…
How to Create Responsibility API
Description: A responsibility is an important configuration which allows the user to navigate to the various menus and form functions within that responsibility…
Query to get transactions of inventory items with available onhand quantity
Introduction This post is about to get transactions of inventory items with available onhand quantity. Script to get transactions of inventory items with…
Account Payables Remittance Advice.
Introduction This post is about AP Remittance Advice, a document that describes payments that are being made. The person or company that is causing the payment…
Suppliers Report (XML)
Introduction This Post described Suppliers Report (XML) is used to review the supplier, supplier site, and supplier contact details. Query Fetching Vendo…
Query of 11i to R12 Table Changes
Description: In this page, going to cover minimum database object level changes done by Oracle in the upgrade like object obsolete, package level changes, API c…
Call Oracle package in Oracle BI Publisher 10g
Call Oracle package in Oracle BI Publisher 10g Introduction This Post is about Oracle BI Publisher is just a reporting tool. From which we can download excel, C…
eAM-Asset Number Conversion
Description: The eAM Asset Number Open Interface enables you to import Asset Numbers into eAM, using a batch process. Optionally import Asset Number attributes.…
AP Pre Payments Query: AP XLA GL
Description: The below post describe the steps to create AP Pre payments Query: AP XLA GL SELECT glcc.segment1 COMPANY, glcc.segment2 LOCATION, glcc.segm…
AP Distribution Query: AP XLA GL
Description: The below process guide to create AP Distribution Query in AP XLA GL SELECT glcc.segment1 company, glcc.segment2 LOCATION, glcc.segment3 cos…
Oracle 12c accessible by clause
Introduction This Post illustrates steps required to Oracle 12c accessible by clause in Oracle EBS R12. PACKAGE Helper authed Definer accessible BY(Good_…
AP Invoice On Hold Report
Introduction Invoice on Hold Report is used to review detailed information about invoices on hold. You can submit the Approval process before submitting this re…
Multiple indexes for same column
Introduction This post is about to create multiple indexes for the same column in Oracle EBS R12. Script to create multiple indexes for the same column.…
Trial Balance – Detail Report
Introduction Oracle General Ledger Trial Balance report is used to satisfy legal, statutory, and internal management reporting requirements in Portugal. There a…
Query for Create or Update HR location in oracle apps
Introduction This Post illustrates the steps required to Create or Update HR location in oracle application. Script to Create or Update HR location in oracle ap…
Query to get Normalized Exp Amount from Property Manager
Introduction This Post illustrates the steps required to get Normalized Exp Amount from Property Manager Script to get Normalized Exp Amount from Property Manag…
Payables Accounting Entries Report
Introduction The report lists in detail any transactions that have been accounted for with error and all entries that could not be transferred to the general le…
Accounts Payable Trial Balance Report
Description Account Payable Trial Balance Report meant to verify that total accounts payable liabilities in Payables equal those in the general ledger. Before c…
Purchase Order (With Requisition Preparer)
Introduction To fetch the Purchase Order along with Requisition Preparer details, we can use the following queries. /* Query 1 To Fetch Cancel PO*/ SELECT actio…
AR Selected Invoices Print
Introduction This script is used Queries to get AR Selected Invoices. Query to using for AR Selected Invoices print. /* Adjustments */ SELECT line_adjusted adj_…
Open Receipt Report
Introduction This Open Receipt Report is used to review a list of receipts which are in open status for the date range that you specify. Script used for Open Re…
Query to find open invoices with the Supplier and PO details
Introduction This Post is about Query to find open invoices with the Supplier and PO details in Oracle EBS R12. Script to find open invoices with the Sup…
Query to get the Menu and Submenu list against a responsibility in oracle apps
Introduction This Post illustrates the steps required to get the Menu and Submenu list against a responsibility in Oracle EBS R12. Script t0 get the Menu and Su…
ERROR ORA -00980 Synonym Translation no longer valid
Introduction This Post is about to ORA -00980 Synonym Translation no longer valid Error in Oracle EBS R12. Error Message : ORA -00980 Synonym Translation…
Untitled
Introduction This Post is used for GL Transactions for AP in Oracle EBS R12. Script used for GL Transactions for AP. SELECT /*+ leading (ael aeh) */ aia.…
GL Transactions for AR Adjustments
Introduction This Post illustrates the steps required to GL Transactions for AR Adjustments in Oracle EBS R12. Script to GL Transactions for AR Adjustmen…
GL Transactions for AR
Introduction This Post is about to GL Transactions for AR in Oracle EBS R12. Script used for GL Transactions for AR. SELECT rctt.description trans_type,…
How to Use Associative array in select statement as a normal database table
Introduction This Post illustrates steps required to Associative array in select statement as a normal database table in Oracle EBS R12. Step 1 First Create Ass…
Query for finding MTD/YTD/ITD Cost/Revenue and Fee Details for a Project in PA Module
Introduction This Post illustrates the steps required to finding MTD/YTD/ITD Cost/Revenue and Fee Details for a Project in PA Module Oracle EBS R12. Script to f…
Zooming: Calling One Form from another Form using Personalization
Introduction This Post illustrates steps required to Calling One Form from another Form using Personalization in Oracle EBS R12. Open the main form you w…
File Moving and renaming using UNIX SHELL Script in EBS
Inroduction This Post is about File moving and renaming using unix shell script in Oracle EBS R12. #!/bin/bash ##########################################…
How to Use Associative array in select statement as a normal database table
Introduction This Post illustrates steps required to Associative array in select statement as a normal database table in Oracle EBS R12. Step 1 First Create Ass…
Vendor Ledger Report Query
Introduction This Post illustrates steps required to Vendor Ledger Report Query in Oracle EBS R12. Script to Vendor Ledger Report Query. SELECT flag, vendor_num…
Dynamic Approver List for AME
Introduction This Post illustrates steps required to Oracle has provided an API to get the dynamic list of approvers for AME.ame_dynamic_approval_pkg.get_ame_ap…
Query to retrieve GL Journal Import
Introduction This Post illustrates the steps required to retrieve GL Journal Import in Oracle EBS R12. Script to retrieve GL Journal Import SELECT glc.segment1…
Oracle EBS Active Users/Employee Details and their Active responsibilities find Query
Introduction This Post is about Active Users/Employee Details and their Active responsibilities find Query Script to Active Users/Employee Details and their Act…
Load JSON File to Oracle table
Introduction This Post illustrates steps required to Load JSON File to Oracle table in Oracle EBS R12. For Example : 1) Sample JSON:- { “department”: { “departm…
Map Member Functions For Multiset Operations
Introduction This Post is about to Map Member Functions For Multiset Operations in Oracle EBS R12.MAP member functions are special functions used to compare obj…
Auto Apply given receipts to Invoices in oracle apps
Introduction This Post illustrates the steps required for Auto Apply given receipts to Invoices Script to Auto Apply given receipts to invoices. CREATE OR REPLA…
Sales Order Loader API from Blanket Sales Agreement
Introduction: This Post illustrates the steps required to Sales Order Loader API from Blanket Sales Agreement in Oracle EBS R12 Script to Sales Order Loader API…
API to Check Item On-hand Quantity for Specific Inventory Organization
Introduction This Post is about to Check on hand Quantity For Specific Inventory Organization in Oracle EBS R12. Script to Check on hand Quantity for spe…
Fusion HCM – Query for Personal Payment Method Details
Introduction: This Post illustrates the steps required to get Personal Payment Method Details in Fusion HCM Script to Personal Payment Method Details in Fusion…
Query to Element Entry Details in Fusion Application(HCM)
Introduction This Post illustrates the steps required to Element Entry Details in fusion Application Script to Element Entry Details in Fusion Application SELEC…
Sales Order Dispatch details (Link between Material Transactions and Invoice)
Introduction This Post is about to Sales Order Dispatch details (Link between Material Transactions and Invoice) in Oracle EBS R12. Script to Sales Order…
Query to Calling a Value Set in Fast Formula-fusion HCM
Below is a Sample Formula shows How to Call a Value set in Fast Formula : Inputs are hr_id (number), eff_st_date(text), eff_end_date(text), mode (text) l…
How to Extract All the Asset Categories Book Wise with all Code combinations.
Introduction This post is about How to Extract All the Asset Categories Book Wise with all Code Combinations in Oracle EBS R12. Script to Extract All the…
Query for API to Update Profile Value – SSO to Local
Introduction This Post illustrates the steps required to update the profile value –SSO to LOCAL through API in Oracle EBS R12 Script to through API Update Profi…
About How to Delete Stuckked/corrupted Lock Box Transmission format.
Introduction This Post Is About How to Delete Stuckked/corrupted Lock Box Transmission format. Script to Delete Stuckked/corrupted Lock Box Transmission…
Oracle Sql to get Profile Option Values at Responsibility level
Introduction This Post illustrates the steps required to get Profile Option Values at Responsibility level in Oracle EBS R12 Script to get Profile Option Values…
How to Convert Comma Separated Values into Table using ‘REGEXP_SUBSTR.’
Introduction This Post is about to Convert Comma Separated Values into Table using WITH CSV’ REGEXP_SUBSTR.’ AS (SELECT ‘AA,BB,CC,D33D,EE,FFDD,MM,GG’ AS csvdata…
API to Update Customer Account in TCA R12
Introduction This Post is about to Update Customer Account in TCA R12. Script to Update Customer account in TCA R12. DECLARE p_cust_account_rec HZ_CUST_A…
Report Will Fetch All Vat Details In The Ap Module
Introduction This Post is about to Report Will Fetch All VAT details in the AP Module in Oracle EBS-R12. <dataTemplate name=”IRNGLBFRTVAAP” version=”1.0″…
Query for Employee data extraction based on business group
Introduction This Post illustrates the steps required to Extract the employee data based on business group in Oracle EBS R12 Script to Employee data extraction…
Query for PO Blanket details along with receipt and Indian tax details
Introduction This Post illustrates the steps required to PO Blanket details along with receipt and Indian tax details in Oracle EBS R12. Script to PO Bla…
Untitled
Introduction This Post illustrate steps required to India AR GST tax report in Oracle EBS R12. Query to India AR GST tax report . WITH Parameter AS (SELE…
Script to get Oracle i-expense Line attachments
Introduction This Post illustrates steps required to get Oracle i-expense Line attachments in Oracle EBS R12. SELECT fl.* FROM apps.fnd_documents_tl fdtl…
Receivables to GL Link in 11i
Introduction This Post is about to Receivables to GL Link in 11i. SELECT je_header_id, line_number, trx_number, myrank, item_number,customer_number, cust…
Function to calculate Item Cost
Introduction This Post illustrates the steps required Function to calculate Item Cost in Oracle EBS R12. Script to Function to calculate Item Cost. CREATE OR RE…
Query to get Detail Customer Information
Introduction This Post illustrates the steps required to get Detail Customer Information query in Oracle EBS R12. Script to get Detail Customer Informati…
Query to Get Employee Supervisor Relationships
Introduction This Post is about to Get Employee Supervisor Relationships Query in oracle R12. Query to Get Employee Supervisor Relationships. SELECT DISTINCT LP…
API To end date the user and responsibilities in Oracle apps
Introduction This Post illustrates the steps required to through API(Application program interface) end date the user and their responsibilities in Oracle EBS R…
Script to get PHONE NO with area code of the customer in Oracle apps
Introduction This Post illustrates the steps required to get PHONE NO with area code of the customer in Oracle EBS R12. Script to get PHONE NO with area code of…
Oracle API Script for SHIP CONFORMATION Using-(WSH_DELIVERIES_PUB.Delivery_Action)
Introduction This Post illustrates the steps required to ship conform the sales order using Oracle API Script in Oracle EBS R12 Script to Oracle API for SHIP CO…
Oracle API Script for pick Release the Sales order
Introduction This Post illustrates the steps required to pick release for sales order through oracle API script in Oracle EBS R12 Script to Oracle API Script fo…
Script for Create and book sales order in oracle apps
Introduction: This Post illustrates the steps required to create and book sales in Oracle EBS R12 Script to OE_ORDER_PUB.PROCESS_ORDER ( Sample Script Create an…
Query for retrieve the Internal Requisition and Internal Sales Order Details in oracle apps
Introduction This Post illustrates the steps required to retrieve Internal Requisition and Internal Sales Order Details in Oracle EBS R12. Script to Query retri…
Script to delete the employee record using API in oracle Apps
Introduction This Post illustrates the steps required to delete the employee record using API in Oracle EBS R12 Script to delete the employee record using API –…
Script to get Cash from Property Manager
Introduction This Post illustrates the steps required to get Cash from Property Manager in oracle Apps Script to get Cash from Property Manager CREATE OR REPLAC…
Script for get EMAIL information of the customer in oracle Apps
Introduction This Post illustrates the steps required to get EMAIL information of the customer in Oracle EBS R12 Script to get EMAIL information of the customer…
SQL Query for extracting the late fee data for customers in R12 Oracle Apps
Introduction This Post illustrates the steps required to extracting the late fee data for customers in Oracle EBS R12. Script to SQL Query for extracting the la…
Script for customers bill to email address in R12 Oracle Apps
Introduction This Post illustrates the steps required to get customers bill to email address in Oracle EBS R12 Script to customers bill to email address in R12…
Script to getting customer Bill to Contact in R12 Oracle Apps
Introduction: This Post illustrates the steps required to getting customer Bill to Contact in Oracle EBS R12. Script to Script for getting customer Bill to Cont…
GL Revaluation Accounts Query in FUSION
Introduction This Post illustrates the steps required to do GL Revaluation Accounts in FUSION Application Script to GL Revaluation Accounts SELECT DISTINCT o101…
Query for API to Update Profile Value – Local to SSO
Introduction This Post illustrates the steps required to Update the profile value Local to SSO through API in Oracle EBS R12 Script to Update Profile Value – Lo…
Query to extract FA details based on the asset book
Introduction: This Post illustrates the steps required to extract the FA details based on the asset book in Oracle EBS R12 Script to Query to extract FA details…
Update Task Information using PA_TASKS_PKG.UPDATE_ROW, PA_PROJ_TASK_STRUC_PUB.UPDATE_TASK_STRUCTURE2 in Oracle Projects
Description: You can update information for tasks with the Open status. You can answer questions, add comments, and attach references. You can also view status,…
Oracle Blanket Sales Agreement Loader API
Description: A blanket sales order is a long-term agreement between a seller and a customer. A blanket order is typically made when a customer has committed to…
Oracle 18c- From a Developer’s Perspective
Description: Oracle Database 18c is the same database software that is running on-premises as in the Oracle Cloud. It enables end-users to take their in-house d…
ORA-30006: resource busy; acquire with WAIT timeout expired and ORA-00054: resource busy and NOWAIT specified
Description: 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 UPD…
AWR REPORT
Description Introduced in Oracle 10g and having evolved from the Statspack functionality of Oracle 8i/9i, the Automatic Workload Repository report or AWR report…
SGA Details – JROD COPY
Description: The System Global Area (SGA) is a group of shared memory structures, known as SGA components, that contain data and control information for one Ora…
Script to get Aging Bucket Due for Customer
Description: Aging reports help to identify how old the customer Invoice due for the payment. This helps to identify and to decide those Invoice is due last 30…
QA Collection Elements creation
Description: The collection elements that you define can be added to collection plans to determine what quality results data to collect, track, and report. API…
Script to Open Project with Terminated PMs or PDs
Description: Oracle Projects provides default processes for each workflow. You can modify the default processes and create additional methods to accommodate the…
Query to New Assets Addition in Oracle apps
Description: This query fetches the detailed information related to those assets which are added newly for a particular period. Script to New Asset Addition SEL…
List of concurrent request – its count by Day, Week and Month
Description: When you define a concurrent program, you can list those programs you want it to be incompatible with. You can also list the program as inconsisten…
Leases which are expiring 15, 30, 60, 180 days before respectively
Description: This query fetches those leases which are going to expire within 15,30,60,90,180 days, respectively, based on the parameter. SELECT GCC.segment4 SO…
Query to Create Position Hierarchy by Using APIs
Description: The Hierarchy Diagrammer, included with Oracle HRMS, enables you to create your position hierarchies graphically, and make intuitive drag-and-drop…
EBS to FUSION PA (Project Accounting) Table Changes
Description: One of the most common requirement for every technical consultant working in oracle and Related technologies is to find out specific database table…
EAM Preventive maintenance Last service reading API
Description: EAM enables you to generate Work Orders automatically using a preventive maintenance schedule, based on Runtime Rules, Day Interval Rules, and List…
Query to EAM Construction Estimation Creation
Description: Construction units are defined at the organization level, and can include only those activities that are assigned to the same inventory organizatio…
EAM Asset Number activity association creation API
Description: Oracle Enterprise Asset Management provides several open interfaces and APIs, enabling you to link with non-Oracle applications, applications you b…
Query to EAM Asset Failure Code, Cause, Resolution creation
Description: Failure Analysis enables you to track an asset failure from the time it is reported until its resolution. Statistics from your collected failure da…
Capital project hours – includes PTO
Description: A capital project, defined as any project which requires capital flows for completion, usually refers to a project which requires large sums of cap…
ASSET DEPRECIATION LOGIC QUERY
Description: Oracle Assets calculates depreciation using either the recoverable cost or the recoverable net book value as a basis. If the depreciation method us…
Script for Extracting InterCompany batch details
Introduction This Post illustrates steps required to Extracting InterCompany batch details in Oracle EBS R12. Extracting InterCompany batch details Query SELECT…
Script for getting PO SLA Details
Introduction This Post illustrates steps required to getting PO SLA Details in Oracle EBS R12. Script to getting PO SLA Details SELECT DISTINCT pap.last_name ||…
Script for getting Sales Tax Transactions
Introduction This Post illustrates the steps required to getting Sales Tax Transactions Query in Oracle EBS R12. Script to getting Sales Tax Transactions Query…
Script to add employee as buyer
Introduction This Post illustrate steps required to add employee as buyer in Oracle EBS R12. Script to add employees as buyer DECLARE l_agent_id NUMBER; l_emplo…
Script to Assign Salesrep Role in CRM
Introduction This Post Is About to Script to Assign Salesrep Role in CRM in Oracle. DECLARE l_jtf_person_id NUMBER; /*Assign Value*/ lc_resource_id jtf_rs_resou…
Script to extract Expenses SLA Details
Introduction This Post illustrate steps required to extract Expenses SLA Details in Oracle EBS R12. Query to extract Expenses SLA Details SELECT aerh.invoice_nu…
Script to extract Goods Received Date for Suppliers along with Invoice and PO Information
Introduction This Post illustrates the steps required to extract Goods Received Date for Suppliers along with Invoice and PO Information in Oracle EBS R12. Quer…
Script to get Oracle i-expense Line attachments
Introduction This Post illustrates steps required to get Oracle i-expense Line attachments in Oracle EBS R12. Query SELECT fl.* FROM apps.fnd_documents_tl fdtl,…
Script to get Project Listing
Introduction This Post illustrates the steps required to get Project Listing Query in Oracle EBS R12. Script to get Project Listing Query SELECT pt.project_id,…
Script to Import CRM Resource
Introduction This Post illustrate steps required to Import CRM Resource in Oracle EBS R12. Script to Import CRM Resource DECLARE l_jtf_person_id NUMBER; /*Assig…
Suppliers Report (XML)
Introduction This Post described Suppliers Report (XML) is used to review the supplier, supplier site, and supplier contact details. 1.Query Fetching Vendor Det…
Trial Balance – Detail Report
Introduction Oracle General Ledger Trial Balance report is used to satisfy legal, statutory, and internal management reporting requirements in Portugal. There a…
Uninvoiced Receipts Report
Introduction Uninvoiced Receipts report is designed to provide a preview of all accrual entries that will be posted to your account in a specific month. Script…
Using Oracle Forms to load local files
Introduction This Post illustrates steps required to using the below code In load file “button” using “when-button-pressed” trigger. DECLARE l_userhome V…
Vendor Ledger Report Query
Introduction This Post illustrates steps required to Vendor Ledger Report Query in Oracle EBS R12. Script to Vendor Ledger Report Query SELECT flag, vendor_num,…
Zooming: Calling One Form from another Form using Personalization
Introduction This Post illustrates steps required to Calling One Form from another Form using Personalization in Oracle EBS R12. Step 1 Open the main form you w…
Easy to Create or Customize Interactive Reports
Introduction This Post is About to Steps for Create or Customize Interactive Reports in Oracle EBS-R12. Steps Navigate to the Welcome to dashboard. Click your n…
Dynamic Approvel List for AME
Introduction This Post illustrates steps required to Oracle has provided an API to get the dynamic list of approvers for AME. We need to pass the transaction ty…
Customer Credit Snapshot Report
Introduction This Post illustrates steps required to Report will fetch and display all customer credit card snapshot details in excel format in Oracle EBS R12.…
API To Create Bulk GL Code Combinations Using API in Oracle EBS-R12
Introduction This Post illustrate the steps required to Create Bulk GL Code Combinations Using API in Oracle EBS R12. Script to Create Bulk GL Code Combinations…
Basics for Custom Form Development
Introduction The below are the steps to follow to develop a basic Custom form in EBS. Download the Resource folder from the server and place it in the local dir…
Auto Apply given Credit memos to the Invoices
Introduction This Post explain steps required to Apply Credit Memos to the Invoices through API in Oracle EBS R12 Script to Apply Credit Memos to the Invoices t…
Asset Retirements Report
Introduction Asset Retirements Report is used to review the assets you retired for the Book and accounting Period range you choose. The report is sorted by bala…
AR Selected Invoices Print
Introduction This script is used Queries to get AR Selected Invoices. Script to using for AR Selected Invoices Print /* Adjustments */ SELECT line_adjusted adj_…
API to update the supplier
Introduction This Post is About to Update the supplier Through API. Script for API to update the Supplier DECLARE p_api_version NUMBER; p_init_msg_list VARCHAR2…
API to update the customer locations
Introduction This Post illustrates the steps required to update the customer locations in Oracle EBS R12. Below script for update customer locations DECLARE p_l…
API to update the attribute in AR invoice
Introduction This Post is about to update the attribute in AR invoice Through API In Oracle EBS R12. Please find the below script for update the attribute in AR…
API to update customer Profile in Oracle Applications
Introduction This Post illustrates the steps required to update customer Profile in Oracle EBS R12. Script to update customer Profile in Oracle Applications DEC…
API to Update a Customer Account TCA R12
Introduction This Post illustrates the steps required to Update a Customer Account TCA in Oracle EBS R12. Script to Update a Customer Account API TCA R12 DECLAR…
API to Check Item On-hand Quantity for Specific Inventory Organization
Introduction This Post is about to Check on hand Quantity for Specific Inventory Organization in Oracle EBS R12. Script to check on hand Quantity for specific i…
API for Bank Account Creation in Oracle Apps
Introduction This Post illustrates the steps required to Bank Account Creation API in Oracle EBS R12. Script to Bank Account Creation API DECLARE p_init_msg_lis…
AP Web Report Workflow Status
Introduction This Post illustrate steps required to AP Web Report Workflow Status in Oracle EBS R12. Script to AP Web Report Workflow Status SELECT ‘WPAY’ TYPE,…
AP Supplier Banks Payments Query
Introduction This Post is about to AP Supplier bank payments Query. Query for getting AP Supplier Bank Payments Query SELECT pvs.org_id org_id, iep.default_paym…
AP Invoice On Hold Report
Introduction Invoice on Hold Report is used to review detailed information about invoices on hold. You can submit the Approval process before submit this report…
Accounts Payable Trial Balance Report
Description Account Payable Trial Balance Report meant to verify that total accounts payable liabilities in Payable’s equal those in the general ledger. Before…
Account Payable Remittance Advice
Introduction This post is about AP Remittance Advice, a document that describes payments that are being made. The person or company that is causing the payment…
API to Create Project Party in Oracle Project Accounting
Introduction This Post illustrates the steps required to Create Project party in oracle projects accounting using the API PA_PROJECT_PARTIES_PUB.CREATE_PROJECT_…
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…
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…
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…
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…
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…
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…
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…
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…
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…
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…
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…
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…
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 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…
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…