Blog / Category
SQL Queries
128 articles in this category
Preventing Duplicate Form Submission in Oracle APEX
Introduction / Issue One of the common issues encountered while developing Oracle APEX applications is duplicate form submission. Users may unintentionally clic…
UI Mistakes Oracle APEX Developers Should Avoid
Introduction / Issue Many Oracle APEX applications successfully meet business requirements but often overlook one critical aspect—the User Interface (UI) and Us…
How to Unlock Locked SQL Server Logins
How to Unlock Locked SQL Server Logins Introduction / Issue During day-to-day SQL Server administration, users may report that they are unable to connect to the…
SQL Server Audit File Enabled for Both Failed and Successful Logins
SQL Server Audit File Enabled for Both Failed and Successful Logins Introduction During a routine SQL Server security review, it was observed that SQL Server Au…
Forecast-Based Component Demand in Fusion SCP
Introduction/ Issue: Developing a custom BI Publisher (BIP) report in Oracle Fusion Supply Planning to provide visibility into component dependent demand based…
Building an Operational Metrics Dashboard for Banking Operations
Introduction A mid-sized financial services organization needed to provide real-time visibility into critical operational metrics across 25 branches. The operat…
Assembly and Component Items Demand Shortage Report in Fusion SCP
Introduction/ Issue: Developing a BIP report in Fusion to identify component demand shortages for assembly items and improve supply planning decisions. Why we n…
The Oracle Migration Challenge – Datatype Conversion Nightmares
When Database Migrations Go Wrong: Snowflake to Oracle Every database migration project starts with optimism: “We’ve mapped the tables, the data looks similar –…
From Slow to Lightning Fast: Optimizing SQL Bulk Operations with Python’s executemany()
The Problem: When Good Code Goes Slow Picture this: You have a Python application that needs to insert thousands of records into a database. Your code works per…
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…
Streams in Snowflake
In snowflake, Streams are a powerful feature designed for tracking changes (like inserts, updates, and deletes) to a table. They allow you to capture data chang…
Snowpipe (a continuous data ingestion pipeline) in Snowflake
To create a new Snowpipe (a continuous data ingestion pipeline) in Snowflake, follow these steps: Prerequisites Storage Integration: Set up a cloud storage inte…
Understanding SQL Server Execution Plans: A Beginner’s Guide
SQL Server execution plans are valuable tools for developers and database administrators aiming to optimize query performance.
SQL Server Indexes: The Ultimate GPS for Your Data Journey
We all have noticed how some SQL Server queries feel like a luxury express train, zooming to their destination, while others resemble a sluggish car through tra…
SQL Server Database and Login migration
Introduction; The Data Migration Assistant (DMA) tool is a Microsoft utility designed to simplify the migration of databases from older SQL Server versions or o…
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…
Conquering Primary Key Violation – SQL Server Replication
Transactional replication in SQL Server is a robust feature that allows you to replicate data from a primary database (publisher) to one or more secondary datab…
EMPLOYEES HOLIDAY CALENDAR LIST (Core HCM)
Introduction: This SQL query is used to fetch the Holidays list for Employees like Employee number, Holiday name, Start date, End date etc… Cause of the issue:…
EMPLOYEE SALARY DETAILS QUERY (Core HCM)
Introduction: This SQL query is used to fetch the data of Employees Salary details (CTC)like Annual salary, Fixed CTC, Employee number, DOJ, Base element name e…
Virtual Private Database (VPD) in Oracle APEX for securing data access
Overview: This blog talks about VPD (Virtual Private Database) in Oracle Apex, a feature that helps address security concerns by restricting access to data at t…
Active EBS users and their respective responsibilities in Oracle E-Business Suite
Every time a customer or client asks for a list of EBS Users and their responsibilities, the DBA team would generally execute the “Active Users” Concurrent Prog…
Enhancing Log and Output File Management in Oracle E-Business Suite 12.2
Introduction: Effectively managing large log and output files is crucial for maintaining optimal performance in Oracle E-Business Suite 12.2. Despite the availa…
STEPS TO INSTALL SQLSERVER IN WINDOWS 10
STEP – 1 1 First download .netframework 3.5 Completed. Now the .net framework 3.5 has been installed in the windows system STEP-2 -Download software for…
EMPLOYEE WORK SCHEDULE UPDATE (Core-HCM)
Introduction: This SQL query is used to fetching the data of Employees Work Schedule changes like Work Schedule id, Name etc… In this Last update date is used a…
EMPLOYEE ASSIGNMENT UPDATE (Core-HCM)
Introduction: This SQL query is used to fetching the data of Employees Assignment changes like Manager change, location change etc… In this Last update d…
Generate OTP and Integrate SMS API
1. Overview This document talks about how to generate OTP & Integration of SMS. 2. Technologies and Tools Used The following technologies has been used to Integ…
Oracle 23-C New Features Table Value Constructor
Introduction The database’s SQL engine now supports a VALUES clause for many types of statements. This new clause allows for materializing rows of data on the f…
Employee Revoke (Core Hr-HCM)
Introduction: This SQL query is used to fetching the data for Employees who are Revoked means cancelled the termination those employees will fetch based on last…
Wheebox Assessment tests (ORC-HCM) Query
Introduction: This SQL query is used to fetching the data for candidates who are applied various assessment tests (Wheebox) at the time of recruiting like one c…
Query to get Data Accesses of Users – Oracle Fusion
Introduction: This blog has the SQL query that can be used to pull the data access details of all the users irrespective of their status. Cause of the issue: Bu…
Sales Daily Pick Report
1.Overview This document talks about getting the sales daily stock pick report in oracle apps r12 2.Technologies and Tools Used The following technologies have…
SQL Server installation Slipstream
Slipstream in SQL Server What is the slipstream how to use slipstream to install SQL server? Slipstream is a technique used by System Admins to easily update th…
How to uninstall patch
How to uninstall patch file when need to downgrade and remove it What is the patch file. RTM is the original build version of the product when you download the…
WHAT IS THE DIFFERENCE BETWEEN SRVCTL AND CRSCTL?
SERVER CONTROL UTILITY (SRVCTL) Server Control Utility (SRVCTL): It is used to administer Oracle Real Application Clusters (Oracle RAC) databases and instances.…
How to run SQL tuning advisor manually in Oracle Database
Step 1: Get the hash value of the SQL using the SQL ID. Enter the SQL ID select a.plan_hash_value from v$sql a where sql_id=’&a’; Step 2: Create a tuning…
Fusion HCM Candidate Query
Introduction: This SQL query is used to fetching the data for candidates who are attended various assessment tests at the time of recruiting. In this assessment…
CREATE TABLE AS STATEMENT (by copying from existing table)
Use SQL CREATE TABLE AS statement to create a table from an existing table by copying the existing table’s columns. What happens to the METADATA and DATA when c…
19C LISTAGG DISTINCT
Overview The LISTAGG aggregate function now supports duplicate elimination by using the new DISTINCT keyword. The LISTAGG aggregate function orders the rows for…
Oracle Analytical Functions
The easiest way to understand analytic functions is to start by looking at aggregate functions. An aggregate function aggregates data from several rows into a s…
Get working days between two working date excluding weekends
This function which will help you to get number of working days between two date excluding the weekends. This will help other to get logic between two working d…
oracle unpivot function
The Oracle UNPIVOT clause allows you to transpose columns to rows. The UNPIVOT clause is opposite to the PIVOT clause except that it does not de-aggregate data…
Hold Pending Concurrent Requests and release them
Here is an easy way to put all the Pending Concurrent requests in hold, if we are going through any situation(Mostly any Production Cutover/Go live scenarios).…
Oracle R12 Query To Find Customer Notes
SELECT hca.account_number, ( SELECT hp.party_name FROM apps.hz_parties hp WHERE hp.party_id = hca.party_id ) customer_name, jnb.creation_date notes_creation_dat…
Oracle R12 GL To AR Transaction Details
SELECT DISTINCT –gcck.concatenated_segments, gcck.segment1 company, gcck.segment2 location, gcck.segment3 product, gcck.segment4 function, gcck.segment5 account…
Oracle 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>
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(…
Create a New PL/SQL Library
The steps in this section will show you how to create a new PL/SQL library, then create a function that will live in this library. To create the library: Launch…
How to fix INVALID status for RAC component in dba_registry
Before upgrade or after upgrade, database may show INVALID status for component Real Application Clusters in dba_registry as below . SQL> select comp_nam…
Generate DDL By comparing 2 Schema.
Table of Contents Overview Technologies and Tools Used Use Case Architecture Screen Shot 1. Overview This document will be helpful to compares Prod and Dev sche…
Job DDL
Creates the DDL for the specified job:- SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON BEGIN DBMS_METADATA.set…
DDL of Foreign key
Script to create foreign key DDL on all tables. SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON BEGIN DBMS_META…
The SQLAccess Advisor
DBMS_ADVISOR :- 1. Gather a number of SQL statements that will form the tuning workload. 2. Check that the user running DBMS_ADVISOR has the ADVISOR privilege,…
Converting rows to columns using Oracle Pivot clause
The Oracle introduced the PIVOT clause from oracle 11g. This makes converting rows to column easy. The value in the new columns must be the result of an aggrega…
Find the Invalid Email Address using Oracle SQL and PL/SQL
Objective: To find the validity of the email address using Oracle Pl/sql and to find the invalid email address from a object like table using Oracle Sql. Using…
To Find IP Address of the User who login into Apex Application
This query will help to sql, Plsql and Apex Developer to find the user IP Address. Normally we tracking only page id, username and time of the user for login Hi…
Query to Check locks on a table
Select object_id, session_id, oracle_username, os_user_name, Process, locked_mode From sys.v_$locked_object; Select a.object_name, b.oracle_username From all_ob…
Oracle R12 Customer TaxPayer ID Update API
/*Upload the customer data that needs to be updated in a temp table*/ CREATE TABLE scratch.RITM0669580 (account_number VARCHAR2(30), tax_payer_id VARCHAR2(20),…
Oracle R12 Customer Remittance Email Update API
DECLARE x_return_status VARCHAR2 (200) := NULL; x_msg_count NUMBER := 0; x_msg_data VARCHAR2 (200) := NULL; t_output VARCHAR2 (200) := NULL; t_msg_dummy VARCHAR…
Oracle R12 Customer Site Remittance Email Update API
DECLARE x_return_status VARCHAR2 (200) := NULL; x_msg_count NUMBER := 0; x_msg_data VARCHAR2 (200) := NULL; t_output VARCHAR2 (200) := NULL; t_msg_dummy VARCHAR…
Oracle R12 AP Invoice Extract With PO And CHECK Details
select aia.INVOICE_NUM ,aia.INVOICE_AMOUNT ,aia.CREATION_DATE,aia.INVOICE_DATE,aia.SOURCE,aia.INVOICE_TYPE_LOOKUP_CODE INVOICE_TYPE, (select aca.STATUS_LOOKUP_C…
Oracle R12 Expense Type Master Based On Operating Unit
SELECT ( SELECT hou.name FROM apps.hr_operating_units hou WHERE hou.organization_id = aerpa.org_id ) operating_unit, aerpa.prompt expense_type, aerpa.flex_descr…
Oracle R12 Item Master Extract With Other Essential Details
select (select category_concat_segs from apps.mtl_item_categories_v where inventory_item_id = msib.inventory_item_id and organization_id = msib.organization_id…
Oracle R12 GL Code Combination With Segment Description
select SEGMENT1 Company, apps.gl_flexfields_pkg.get_description_sql (chart_of_accounts_id,— chart of account id 1, —– Position of segment segment1 —- Segment va…
Oracle R12 Customer Extract Based On Operating Unit
SELECT TO_CHAR(hca.account_number) account_number, ( SELECT name FROM apps.hr_operating_units hou WHERE hou.organization_id = hcasa.org_id ) organization_name,…
Oracle R12 Supplier Master With All Supplier Details
select (SELECT hou.NAME FROM apps.hr_operating_units hou WHERE 1 = 1 AND hou.organization_id = aps.org_id ) ou_name, (SELECT paym.payment_method_code FROM APPS.…
Oracle R12 AR Invoice With Tax Lines and Tax Description Query
SELECT DISTINCT rcta.trx_number invoice_number, –gd.gl_date, hp.party_name customer_name, rctla.line_number, rctla.description, rctla.uom_code, rctla.quantity_i…
Oracle R12 Journal Details Query
SELECT gjst.user_je_source_name source, ( SELECT gjct.user_je_category_name FROM apps.gl_je_categories_tl gjct WHERE gjh.je_category = gjct.je_category_name AND…
Oracle R12 Internal Bank Account Query
SELECT hou.name operating_unit, xep.name legal_entity, cebb.bank_name, cebb.bank_number, cebb.bank_branch_name, cebb.bank_branch_number, cebb.bank_branch_type,…
Oracleasm lib commands
ASMLIB is a storage management interface. ASMLIB is not required for ASM to function; it is like an add-on library that simplifies the tasks of managing and dis…
All About Oracle DB Audit
The auditing mechanism for Oracle is extremely flexible. Oracle stores information that is relevant to auditing in its data dictionary. Every time when a user a…
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 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…
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…
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…
Customer Site Usage Import Error Query – FBDI Oracle Fusion
The below query extracts the error details along with interface attributes of Customer Site Usages import using FBDI in Fusion Applications. SELECT distinct err…
Customer Site Import Error Query – FBDI Oracle Fusion
The below query extracts the error details along with interface attributes of Customer Sites import using FBDI in Fusion Applications. SELECT distinct err.messa…
Customer Contact Points Import Error Query – FBDI Oracle Fusion
The below query extracts the error details along with interface attributes of Customer Contact Points import using FBDI in Fusion Applications. SELECT distinct…
Customer Account Import Error Query – FBDI
The below query extracts the error details along with interface attributes of Customer Account import using FBDI in Fusion Applications. SELECT distinct err.mes…
Global Temporary Table ( GTT) – key facts
The data in a GTT is written to the temporary tablespace, which is not directly protected by redo, so using a GTT improves performance by reducing redo generati…
Oracle 12c SQL Plan Directives – Disable | Enable | Use As Hint
What is it? In previous releases the database stored compilation and execution statistics in a shared sql area which is non persistent. Starting in 12c the data…
ORA-20005: object statistics are locked (stattype = ALL) – Solution
During tuning a query, I found one table has stale statistics. While running gather stats for that table, got below error. Let me demonstrate with a demo table:…
Ignore Duplicate Rows (ORA-00001) with the ignore_row_on_dupkey_index Hint
The simplest method is to add a hint to the query to overcome ORA-00001 Added in 11.2, the ignore_row_on_dupkey_index hint silently ignores duplicate values: in…
Create a Virtual Column:
A virtual column applies a function to a column in the table. The database only computes this at runtime. The value is not stored in the table. So instead of cr…
How to Fix ORA-01450: Maximum Key Length (6398) Exceeded Errors:
The smallest unit of data storage in Oracle Database is the block. This defaults to 8k (8,192 bytes). Each index entry must fit within one block. So the maximum…
How to Find underscore_parameters in a database during Audit process
Impact: Unknown underscore parameters without proper advice from metalink, would impact the database. Solution: We need to ensure that we have proper advises fr…
Script to Find Apex Page hits
Below script is for finding the Apex page hits ********************************************************************************************************** /home/…
Script to Find Apex Page Time
Below script is for finding the Apex Page Time ********************************************************************************************************** /home/…
Script to move files to FTP server
We can use the below script for moving files (like export dumps/RMAN backups) to FTP server for safety purpose. ************************************************…
Query to get HCM Extract Input Values in BI Report
Introduction This Post Provides the SQL Query that returns HCM Extract Input Values in BI Report Query SELECT TO_DATE (par_val.flow_param_value, ‘YYYY-MM-DD’) F…
Employee’s Supervisor Details Query
Introduction This Post Provides the SQL Query that returns Employee’s Supervisor Details Query SELECT papf.person_number employee_number, ppnf.full_name employe…
Getting HCM Extract Input Values in Fast Formula
Introduction This Post Provides the Detail to get HCM Extract Input Values in Fast Formula Sample Fast Formula PERSON_NUMBER_PARAM = GET_PARAMETER_VALUE(‘PERSON…
Query to get Last Run Parameter Value in BI Report
Introduction This Post Provides the SQL Query to get Last Run Parameter Value in BI Report Query SELECT TO_DATE (SUBSTR (rpj.VALUE, INSTR (rpj.VALUE, ‘”}’) – 10…
Employee Assignment Details Query
Introduction This Post Provides the SQL Query that returns the Assignment Details of the Employees Query SELECT peo.person_number employee_number, loc.in…
Query to Fetch Invoice details, HSN Code, Third party Registration number and GST Details.
The below query fetches the details of an Invoice along with the HSN Code of a respective item present in an Invoice. GST details can also be fetched. SEL…
Return To Vendor Summay-GST
The below query fetches the data of purchase order wherein those items present in purchase order needs to be returned to Supplier because of some damage caused…
Delivery Challan with GST Details
Query retrieves data of those inventory items which are used for promotional purposes or given as a sample items for a sales executive in order to promote the p…
Find the query of Receivable (AR) for the Invoice Number (TRX_NUMBER) Wise, Customer wise, Sales Order Wise, Transaction Date and GL Date Wise in Oracle Apps EBS R12
Find the query of Receivable (AR) for the Invoice Number (TRX_NUMBER) Wise, Customer wise, Sales Order Wise, Transaction Date and GL Date Wise in Oracle Apps EB…
Query to get transactions of inventory items with available onhand quantity
SELECT recs.doc_type, recs.item_number, recs.doc_no, recs.po_number, recs.organization_id, recs.inventory_item_id, recs…
Query to get current onhand quantity of inventory Items.
SELECT ALL mtl.organization_id, orgs.NAME organization_name, mtl.inventory_item_id, mtl.segment1 item_number, &n…
Payroll Employer Costs Extract
SELECT SUM(peevf.screen_entry_value) FROM pay_element_types_f petf ,pay_element_entry_values_f peevf ,pay_element_entries_f peef ,per_all_assignments_f paaf ,pa…
AP Supplier Banks Payments Query
SELECT pvs.org_id org_id, iep.default_payment_method_code, alc.lookup_code, hou.NAME org_name, pv.vendor_id supplier_id…
Sales Order details with India Localization tax
SELECT h.header_id header_id, party.party_name sold_to, h.order_number order_number, NVL (TO_CHAR (h.order…
Query to get opening,purchasing,receive,sales and closing quantity of items
SELECT * FROM (SELECT b.item_code, b.item_description item_description, b.primary_uo…
Query for Approval groups
SELECT pcr.control_rule_id, pcga.control_group_name, pcr.object_code, pcr.rule_type_code, pcr.amount_limit, &nbs…
Query for Approval Assignments
SELECT hou.NAME operating_unit, pj.NAME job, pcf.control_function_name, pcga.control_group_name, TO_CHAR (…
Query to find list of suppliers who has attachments
Often times you might have to find a list of suppliers who has a specific attachment. For example, if you need to find list of suppliers who has attached with a…
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…
AP Pre Payments Query: AP XLA GL
SELECT glcc.segment1 COMPANY, glcc.segment2 LOCATION, glcc.segment3 COST_CENTER, glcc.segment4 ACCOUNT, glcc.segment5 PRODUCT_LINE, …
AP Distribution Query: AP XLA GL
SELECT glcc.segment1 company, glcc.segment2 LOCATION, glcc.segment3 cost_center, glcc.segment4 ACCOUNT, &nb…
AR Adjustments Query: AR XLA GL
SELECT glcc.segment1 company, glcc.segment2 LOCATION, glcc.segment3 cost_center, glcc.segment4 ACCOUNT, &nb…
AR Transactions Query: AR XLA GL
SELECT glcc.segment1 company, glcc.segment2 LOCATION, glcc.segment3 cost_center, glcc.segment4 ACCOUNT, &nb…
AR Receipt Query: AR XLA GL
SELECT glcc.segment1 company, glcc.segment2 LOCATION, glcc.segment3 cost_center, glcc.segment4 ACCOUNT, &nb…
AR Receipt Distribution Query: AR XLA GL
SELECT glcc.segment1 co, glcc.segment2 loc, glcc.segment3 cc, glcc.segment4 acct, glcc.segment5 prod, glcc.segment6 chan,&nb…
Trigger Enhancements in Oracle Database 11g Release 1
· Execution Order of Triggers · Compound Triggers · &…
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, …
Performance Issue – Hierarchical query LEVEL
Sample Query with Performance Issue: SELECT progress_date, LEVEL slno FROM &nbs…
Table Partitioning
Every data’s should be stored in database, because world can’t run without storing the dat…
AP Invoice liability (Trial Balance) Query: R12 AP GL PO RCV XLA
SELECT NVL (f.currency_code, d.invoice_currency_code) currency_code, d.invoice_amount, d.wfapproval_…
Query to Link Shipments, AP Invoice, GL and SLA Tables: R12 AP GL RCV XLA
SELECT gjh.doc_sequence_value vch_num, gjl.effective_date, xal.currency_code, xal.currency_conversion_rate con_rate, xal.ent…
Query to Link AP Invoices, Payments, GL and SLA Tables: R12 AP GL XLA
SELECT /*+ Index(xdl XLA_DISTRIBUTION_LINKS_N3) */ DISTINCT gjh.doc_se…
Query to Link AP Payments, GL and SLA Tables: R12 AP GL XLA
SELECT SUM (xal.accounted_cr), SUM (xal.accounted_dr), aia.invoice_amount, invoice_num &n…
Query to Link AP Invoices, GL and SLA Tables: R12 AP GL XLA
SELECT SUM (xal.accounted_cr), SUM (xal.accounted_dr), aia.invoice_amount, invoice_num &n…