SQL Query optimization
Query Optimization is very important while in development and also when project grows big we have optimize the existing scripts.
Below are some best practises to optimize the Query
- Tables must have proper joins
- Ensure the joins should be with primary key
- Group by and Order by clause should be used only when needed
- Use only IN clause and subquery when needed
- Avoid Select DISTINCT
- Avoid Select * from
- Use Index when needed
Here we will demonstrate Index with a use case
explain plan for
SELECT DISTINCT
ooh.creation_date order_creation_date,
CASE
WHEN wdd.source_header_type_name = 'INTERNAL' THEN
'INTERNAL'
ELSE
'WHOLESALE'
END order_type,
ooh.order_number,
ooh.cust_po_number,
hp.party_name customer_name,
hl.address1
|| '-'
|| hl.address2
|| '-'
|| hl.address3 customer_site,
ooh.booked_date booked_date,
-- ooh.booked_date,
ooh.flow_status_code order_status,
CASE
WHEN released_flag = 'Y' THEN
'RELEASED'
WHEN released_flag = 'N' THEN
'HOLD'
ELSE
'OPEN'
END hold_status,
CASE
WHEN released_flag = 'Y' THEN
oohl.last_update_date
ELSE
NULL
END hold_release_date,
CASE
WHEN wdd.released_status = 'C' THEN
'INVOICED'
WHEN wdd.released_status = 'R' THEN
'READY TO RELEASE'
WHEN wdd.released_status = 'Y' THEN
'STAGED - AWAITING SHIP CONFIRM'
ELSE
'RELEASED TO WAREHOUSE'
END ship_status,
wnd.name delivery_number,
ool.ship_from_org_id organization_id,
ooh.header_id,
ooh.sold_to_org_id,
hp.party_id
FROM
apps.oe_order_headers_all ooh,
ar.hz_cust_accounts hca,
ar.hz_parties hp,
hz_party_sites hps,
hz_cust_acct_sites_all hcasa,
hz_cust_site_uses_all hcsua,
hz_locations hl,
(select header_id, RELEASED_FLAG , max(LAST_UPDATE_DATE) LAST_UPDATE_DATE from oe_order_holds_all group by header_id, RELEASED_FLAG) oohl, --Changes done by 4iapps on 02-May-2024 to restrict Line and Header Order hold Duplicates
wsh_new_deliveries wnd,
wsh_delivery_assignments wda,
wsh_delivery_details wdd,
apps.oe_order_lines_all ool
WHERE
1 = 1
AND ooh.sold_to_org_id = hca.cust_account_id
AND hca.party_id = hp.party_id
AND oohl.header_id (+) = ooh.header_id
AND hps.party_id = hp.party_id
AND hcasa.party_site_id = hps.party_site_id
AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
AND ooh.ship_to_org_id = hcsua.site_use_id
AND hps.location_id = hl.location_id (+)
AND ooh.header_id = wdd.source_header_id (+)
AND wdd.delivery_detail_id = wda.delivery_detail_id (+)
AND ool.line_id = wdd.source_line_id (+)
AND wdd.released_status NOT IN ( 'B', 'D' )
AND wda.delivery_id = wnd.delivery_id (+)
AND ooh.header_id = ool.header_id
AND hps.location_id = hps.location_id
AND hps.party_site_id = hps.party_site_id
AND hps.party_site_number = hps.party_site_number
AND ooh.header_id = ooh.header_id
GROUP BY
ooh.creation_date,
wdd.source_header_type_name,
ooh.order_number,
ooh.cust_po_number,
hp.party_name,
hl.address1
|| '-'
|| hl.address2
|| '-'
|| hl.address3,
ooh.booked_date,
ooh.flow_status_code,
released_flag,
wnd.name,
oohl.last_update_date,
wdd.released_status,
ool.ship_from_org_id,
ooh.header_id,
ooh.sold_to_org_id,
hp.party_id
order by ooh.header_id desc;
Once the above code is run then execute the below query
SELECT * FROM TABLE(dbms_xplan.display);
The above query gives us the cost of the query and if we observe, on line 21 Table access is full.
So we first create a Index on OE_ORDER_HEADERS_all based on the joins. As joins are proper and required we are creating index.
create index ont.XX_OE_ORDER_HDRS_N1 on ont.OE_ORDER_HEADERS_all (header_id,ship_to_org_id,sold_to_org_id,booked_date) nologging;
explain plan for
SELECT DISTINCT
ooh.creation_date order_creation_date,
CASE
WHEN wdd.source_header_type_name = 'INTERNAL' THEN
'INTERNAL'
ELSE
'WHOLESALE'
END order_type,
ooh.order_number,
ooh.cust_po_number,
hp.party_name customer_name,
hl.address1
|| '-'
|| hl.address2
|| '-'
|| hl.address3 customer_site,
ooh.booked_date booked_date,
-- ooh.booked_date,
ooh.flow_status_code order_status,
CASE
WHEN released_flag = 'Y' THEN
'RELEASED'
WHEN released_flag = 'N' THEN
'HOLD'
ELSE
'OPEN'
END hold_status,
CASE
WHEN released_flag = 'Y' THEN
oohl.last_update_date
ELSE
NULL
END hold_release_date,
CASE
WHEN wdd.released_status = 'C' THEN
'INVOICED'
WHEN wdd.released_status = 'R' THEN
'READY TO RELEASE'
WHEN wdd.released_status = 'Y' THEN
'STAGED - AWAITING SHIP CONFIRM'
ELSE
'RELEASED TO WAREHOUSE'
END ship_status,
wnd.name delivery_number,
ool.ship_from_org_id organization_id,
ooh.header_id,
ooh.sold_to_org_id,
hp.party_id
FROM
apps.oe_order_headers_all ooh,
ar.hz_cust_accounts hca,
ar.hz_parties hp,
hz_party_sites hps,
hz_cust_acct_sites_all hcasa,
hz_cust_site_uses_all hcsua,
hz_locations hl,
(select header_id, RELEASED_FLAG , max(LAST_UPDATE_DATE) LAST_UPDATE_DATE from oe_order_holds_all group by header_id, RELEASED_FLAG) oohl, --Changes done by 4iapps on 02-May-2024 to restrict Line and Header Order hold Duplicates
wsh_new_deliveries wnd,
wsh_delivery_assignments wda,
wsh_delivery_details wdd,
apps.oe_order_lines_all ool
WHERE
1 = 1
AND ooh.sold_to_org_id = hca.cust_account_id
and hca.cust_account_id = hca.cust_account_id+0
AND hca.party_id = hp.party_id
and hp.party_id=hp.party_id+0
AND oohl.header_id (+) = ooh.header_id
AND hps.party_id = hp.party_id
AND hcasa.party_site_id = hps.party_site_id
AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
AND ooh.ship_to_org_id = hcsua.site_use_id
AND hps.location_id = hl.location_id (+)
AND ooh.header_id = wdd.source_header_id (+)
AND wdd.delivery_detail_id = wda.delivery_detail_id (+)
AND ool.line_id = wdd.source_line_id (+)
AND wdd.released_status NOT IN ( 'B', 'D' )
AND wda.delivery_id = wnd.delivery_id (+)
AND ooh.header_id = ool.header_id
AND hps.location_id = hps.location_id
AND hps.party_site_id = hps.party_site_id
AND hps.party_site_number = hps.party_site_number
AND ooh.header_id = ooh.header_id
GROUP BY
ooh.creation_date,
wdd.source_header_type_name,
ooh.order_number,
ooh.cust_po_number,
hp.party_name,
hl.address1
|| '-'
|| hl.address2
|| '-'
|| hl.address3,
ooh.booked_date,
ooh.flow_status_code,
released_flag,
wnd.name,
oohl.last_update_date,
wdd.released_status,
ool.ship_from_org_id,
ooh.header_id,
ooh.sold_to_org_id,
hp.party_id
order by ooh.header_id desc;
After creating Index and forcing Index as attached in the above query now while running plan gives the below result.
SELECT * FROM TABLE(dbms_xplan.display);
We observe a drastic change in costing. But this way the query can be optmized
Query to get Scheduled Request Set and Scheduled Concurrent Programs
Scheduled Request Set
SELECT DISTINCT r.request_id
, u.user_name requestor
, u.description requested_by
, CASE
WHEN pt.user_concurrent_program_name = 'Report Set'
THEN DECODE(
r.description
, NULL, pt.user_concurrent_program_name
, r.description
|| ' ('
|| pt.user_concurrent_program_name
|| ')'
)
ELSE pt.user_concurrent_program_name
END job_name
, u.email_address
, frt.responsibility_name requested_by_resp
, r.request_date
, r.
requested_start_date
, DECODE(
r.hold_flag
, 'Y', 'Yes'
, 'N', 'No'
) on_hold
, r.printer
, r.number_of_copies print_count
, r.argument_text PARAMETERS
, r.resubmit_interval resubmit_every
, r.resubmit_interval_unit_code resubmit_time_period
, TO_CHAR((r.requested_start_date), 'HH24:MI:SS') start_time,
NVL2(
r.resubmit_interval
, 'Periodically'
, NVL2(
r.release_class_id
, 'On specific days'
, 'Once'
)
) AS schedule_type
FROM apps.fnd_user u
, apps.fnd_printer_styles_tl s
, apps.fnd_concurrent_requests r
, apps.fnd_responsibility_tl frt
, apps.fnd_concurrent_programs_tl pt
, apps.fnd_concurrent_programs pb
WHERE pb.application_id = r.program_application_id
AND r.responsibility_id = frt.responsibility_id
AND pb.concurrent_program_id = pt.concurrent_program_id
AND u.user_id = r.requested_by
AND s.printer_style_name(+) = r.print_style
AND r.phase_code = 'P'
AND pb.concurrent_program_id = r.concurrent_program_id
AND pb.application_id = pt.application_id
AND pt.user_concurrent_program_name = 'Report Set'
Scheduled Concurrent Program
select r.request_id,
p.user_concurrent_program_name ||
case
when p.user_concurrent_program_name = 'Report Set' then
(select ' - ' || s.user_request_set_name
from apps.fnd_request_sets_tl s
where s.application_id = r.argument1
and s.request_set_id = r.argument2
and language = 'US'
)
when p.user_concurrent_program_name = 'Check Periodic Alert' then
(select ' - ' || a.alert_name
from apps.alr_alerts a
where a.application_id = r.argument1
and a.alert_id = r.argument2
and language = 'US'
)
end concurrent_program_name,
decode(c.class_type,
'P', 'Periodic',
'S', 'On Specific Days',
'X', 'Advanced',
c.class_type
) schedule_type,
case
when c.class_type = 'P' then
'Repeat every ' ||
substr(c.class_info, 1, instr(c.class_info, ':') - 1) ||
decode(substr(c.class_info, instr(c.class_info, ':', 1, 1) + 1, 1),
'N', ' minutes',
'M', ' months',
'H', ' hours',
'D', ' days') ||
decode(substr(c.class_info, instr(c.class_info, ':', 1, 2) + 1, 1),
'S', ' from the start of the prior run',
'C', ' from the completion of the prior run')
when c.class_type = 'S' then
nvl2(dates.dates, 'Dates: ' || dates.dates || '. ', null) ||
decode(substr(c.class_info, 32, 1), '1', 'Last day of month ') ||
decode(sign(to_number(substr(c.class_info, 33))),
'1', 'Days of week: ' ||
decode(substr(c.class_info, 33, 1), '1', 'Su ') ||
decode(substr(c.class_info, 34, 1), '1', 'Mo ') ||
decode(substr(c.class_info, 35, 1), '1', 'Tu ') ||
decode(substr(c.class_info, 36, 1), '1', 'We ') ||
decode(substr(c.class_info, 37, 1), '1', 'Th ') ||
decode(substr(c.class_info, 38, 1), '1', 'Fr ') ||
decode(substr(c.class_info, 39, 1), '1', 'Sa '))
end schedule,
r.requested_start_date next_run,
case
when p.user_concurrent_program_name != 'Report Set' and
p.user_concurrent_program_name != 'Check Periodic Alert' then
r.argument_text
end argument_text,
r.hold_flag on_hold,
c.date1 start_date,
c.date2 end_date,
c.class_info, user_name
from apps.fnd_concurrent_requests r,
applsys.fnd_conc_release_classes c,
apps.fnd_concurrent_programs_tl p,
apps.fnd_user usr,
(SELECT release_class_id,
substr(max(SYS_CONNECT_BY_PATH(s, ' ')), 2) dates ,a
FROM (select release_class_id,
rank() over(partition by release_class_id order by s) a,
s
from (select c.class_info,
l,
c.release_class_id,
decode(substr(c.class_info, l, 1), '1', to_char(l)) s
from (select level l
from dual
connect by level <= 31),
apps.fnd_conc_release_classes c
where c.class_type = 'S')
where s is not null)
CONNECT BY PRIOR
(a || release_class_id) = (a - 1) || release_class_id
group by release_class_id,a) dates
where r.phase_code = 'P'
and c.application_id = r.release_class_app_id
and c.release_class_id = r.release_class_id
and nvl(c.date2, sysdate + 1) > sysdate
and c.class_type is not null
and p.concurrent_program_id = r.concurrent_program_id
and p.application_id = r.program_application_id
and p.language = 'US'
and dates.release_class_id(+) = r.release_class_id
and usr.user_id = requested_by
order by requested_by,on_hold, next_run;
Transfer files from Windows to Linux Using SSH
Currently I’m transferring files from Windows 11 to Debian Linux 12
Step 1: Ensure that ssh is installed and enabled
$ sudo apt install ssh
$ sudo systemctl enable --now ssh
$sudo ufw allow 22/tcp
Step 2: Get IP address from linux
$ ip addr
Step 3: Use a File transfer client in windows to transfer files. Here Im using WinSCP
Adding a user as Superuser in Debian 12
Step 1: Log in as the Root User
Step 2: Add a New User in Debian
root@host# sudo adduser username
Step 3: Add User to the Sudo Group
root@host# sudo usermod -aG sudo username
Step 4. Adding the Username to the Sudoers File
Open /etc/sudoers file in nano editor
root@host# nano /etc/sudoers
In the file navigate to the below line with # User privilege specification and add your user
# User privilege specification
root ALL=(ALL:ALL) ALL
username ALL=(ALL:ALL) ALL
Save the file by ctrl+o and exit using ctrl+x
Oracle apps EBS Workflow Error Query
SELECT
workflowitemeo.item_type,
workflowitemeo.item_key,
workflowitemeo.root_activity,
activityeo.display_name process_name,
workflowitemeo.root_activity_version,
workflowitemeo.owner_role,
workflowitemeo.parent_item_type,
workflowitemeo.parent_item_key,
workflowitemeo.parent_context,
workflowitemeo.begin_date,
workflowitemeo.end_date,
workflowitemeo.user_key,
workflowitemtypeeo.name,
workflowitemtypeeo.display_name,
wf_directory.getroledisplayname2(workflowitemeo.owner_role) AS role_name,
wf_fwkmon.getitemstatus(workflowitemeo.item_type,workflowitemeo.item_key,workflowitemeo.end_date,workflowitemeo.root_activity,workflowitemeo
.root_activity_version) AS status_code,
wf_fwkmon.getroleemailaddress(workflowitemeo.owner_role) AS role_email,
DECODE( (
SELECT
COUNT(0)
FROM
wf_items wi2
WHERE
workflowitemeo.item_type = wi2.parent_item_type
AND workflowitemeo.item_key = wi2.parent_item_key
),0,'WfMonNoChildren','WfMonChildrenExist') AS child_switcher
FROM
wf_items workflowitemeo,
wf_item_types_vl workflowitemtypeeo,
wf_activities_vl activityeo
WHERE
workflowitemeo.item_type = workflowitemtypeeo.name
AND activityeo.item_type = workflowitemeo.item_type
AND activityeo.name = workflowitemeo.root_activity
AND activityeo.version = workflowitemeo.root_activity_version
and workflowitemeo.BEGIN_DATE >= sysdate-60
and workflowitemtypeeo.NAME = 'HRSSA'
and wf_fwkmon.getitemstatus(workflowitemeo.item_type,workflowitemeo.item_key,workflowitemeo.end_date,workflowitemeo.root_activity,workflowitemeo
.root_activity_version) like 'ERR%'
and activityeo.display_name = 'Business and Training Travel Request'
;
Find / Identify correct Jdeveloper version for EBS Oracle apps Version
We have to get the EBS application OA Framework version. Based on it we can download Jdeveloper version
Steps to identify EBS application OA Framework
- Open your Oracle Applications homepage
- At the bottom left you can see “About this Page”. Click on it.
- Click on “Technology Components” tab
- Check OA Framework version.
Open the below Oracle document ID and download the relevant Jdeveloper patch
How to Find the Correct Version of JDeveloper to Use with E-Business Suite 11i or Release 12.x (Doc ID 416708.1)
Once downloaded follow the below steps
Installation/Deploying Steps:
- Unzip the downloaded Patch (Say in D:).
- Rename the folder (Say OAF)
- You can see 3 folders under it(D:\OAF). They are jdevbin, jdevdoc, jdevhome
- Place the dbc file in D:\OAF\jdevhome\jdev\dbc_files\secure
Location of dbc file in 11i
$FND_TOP/secure OR $FND_SECURE
Location of dbc file in R12
$INST_TOP/appl/fnd/12.0.0/secure - Set the Environment Variables
Right click on My Computer > Click on Properties > Click on Advanced System Settings > Click on Environment Variables > Click on New > Give the following
Variable Name: JDEV_USER_HOME
Variable Value: D:\OAF\jdevhome\jdev
Finally click on OK - Create a shortcut on your Desktop. Goto D:\OAF\jdevbin\jdev\bin
Right click on jdevW > Send to > Desktop (Shortcut) [For Windows] - Double click on the Desktop Shortcut to open JDeveloper
Query to find all responsibility assigned to multiple users in Oracle apps/EBS
SELECT distinct
fu.user_name "" user name"",
frt.responsibility_name ""Responsibility Name"",
furg.start_date ""Start Date"",
furg.end_date ""End Date"",
fr.responsibility_key ""Responsibility Key""
,papf.full_name
-- ,fa.application_short_name ""Application Short Name""
, haou.NAME org_name
,pp.NAME position_name
FROM
fnd_user_resp_groups_direct furg,
applsys.fnd_user fu,
applsys.fnd_responsibility_tl frt,
applsys.fnd_responsibility fr,
applsys.fnd_application_tl fat,
applsys.fnd_application fa
,(select * from per_all_people_f where trunc(sysdate) between effective_start_date and Effective_end_date and current_employee_flag = 'Y') papf
,(select * from per_all_assignments_f where trunc(sysdate) between effective_start_date and Effective_end_date ) paaf
,hr_all_organization_units haou
,per_positions pp
WHERE
furg.user_id = fu.user_id
AND furg.responsibility_id = frt.responsibility_id
AND fr.responsibility_id = frt.responsibility_id
AND fa.application_id = fat.application_id
AND fr.application_id = fat.application_id
AND frt.language = userenv('LANG')
-- AND upper(fu.user_name) = upper('SYSADMIN') -- <change it>
and frt.responsibility_name = 'XX Purchasing Requestor' -- <change it>
-- AND (furg.end_date IS NULL OR furg.end_date >= TRUNC(SYSDATE)) --<For Active Resposibility>
and papf.person_id(+) = fu.employee_id
and paaf.person_id(+) = papf.person_id
and paaf.ORGANIZATION_ID = haou.ORGANIZATION_ID(+)
and paaf.position_id = pp.position_id (+)
order by fu.user_name;
Applications getting crashing in Windows – Increase Virtual RAM
My applications were crashing once I started to open 3 or 4 applications and I have contacted our SYSADMIN. Then they said to follow the below steps
- Press Windows key + I to open the Settings app.
- Head to System > About.
- Select Advanced system settings.
- Under Performance, select Settings.
- Open the Advanced tab. Under Virtual memory, select Change. Here are your Virtual Memory options. Change as per in the screenshot above shown.
Restricting the output of specific Concurrent Reports in Oracle apps EBS
The requirement can be achieved by Forms Personalization.
Ensure the forms personalization is enabled in the Instance. Once it is enabled do as the below steps as per the Screenshot.
Navigation : <Your> Responsibility –> View –> Requests –> Help –> Diagnostics –> Custom Code –> Personalize
Add a New record with below details
Now once the Conditions and Actions are completed test the functionality by submitting the report. Here as shown in the below screenshot format cannot be changed
pacesettergraam 