NVL condition is causing performance issue
One of the NVL condition in the sql is causing performance issue.When i comment out that part,query completing faster.i just want to know any alternalte way to handle that and also there is condition of union select 'X' from dual also sems problematic
```
WITH /*+TEST_JJ3*/
A
AS
(SELECT mp.organization_code,
we.wip_entity_name
work_order,
DECODE (wdj.status_type,
1, 'Unreleased',
3, 'Released',
4, 'Complete',
5, 'Complete',
6, 'Hold',
7, 'Cancelled',
8, 'Pending bill of material load',
9, 'Failed bill of material load',
10, 'Pending routing load',
11, 'Closed',
12, 'Closed',
13, 'Pending - mass loaded',
14, 'Pending close',
15, 'Failed close')
wo_status,
-- wdj.scheduled_start_date
-- wo_start_date,
XX.order_number
so_number,
XX.line_number
so_line,
XX.set_name
so_ship_set,
XX.shipment_priority_code
so_priority,
XX.promise_date,
XX.request_date,
wdj.start_quantity
qty,
CASE
WHEN msi.segment1 LIKE 'R-%*%'
THEN
NVL (XX.model_string, msi.segment1)
ELSE
COALESCE (
CASE
WHEN msi.segment1 LIKE 'R-%'
THEN
XX.model_string
END,
(SELECT DISTINCT
FIRST_VALUE (
lpl.model_string)
OVER (
PARTITION BY ol.top_model_line_id
ORDER BY
CASE
WHEN lpl.model_string
IS NOT NULL
THEN
0
ELSE
1
END,
ol.line_number,
ol.shipment_number,
ol.option_number,
ol.component_number,
ol.service_number,
ol.line_id)
FROM apps.oe_order_lines_all ol,
apps.XXOM_3LP_SYM_ORA_ORDER_LINES lpl
WHERE ol.top_model_line_id =
COALESCE (xx.top_model_line_id,
xx.line_id)
AND ol.header_id = xx.header_id
AND ol.line_id = lpl.line_id
AND lpl.model_string IS NOT NULL),
msi.segment1)
END
item,
wdj.attribute3
wo_notes,
CASE
WHEN SUBSTR (xx.assembly_type, 1, 2) IN ('IS', 'IP', 'I')
THEN
(SELECT MIN (ordered_item)
FROM apps.oe_order_lines_all
WHERE header_id = xx.header_id
AND line_number = xx.line_number
AND ordered_item LIKE 'RA-%*%')
ELSE
NULL
END
ra_star_item,
DECODE (wdj.firm_planned_flag, 1, 'Yes', 'No')
firm_planned_flag,
(SELECT attribute3
FROM APPS.XXWIP_DISJOB_ATTR_RMT
WHERE organization_id = wdj.organization_id
AND wip_entity_id = wdj.wip_entity_id
AND ROWNUM = 1)
orig_wo_start_date,
wdj.scheduled_completion_date,
xx.schedule_ship_date,
NVL (xx.original_promise_date, xx.promise_date)
original_promise_date,
wdj.attribute2
wo_cause_code,
xx.order_admin,
xx.order_type,
xx.customer,
xx.header_id,
NVL (
wl.line_code,
(SELECT mc.segment1
FROM apps.mtl_item_categories mic,
apps.mtl_categories_b mc
WHERE mic.inventory_item_id = wdj.primary_item_id
AND mic.organization_id = wdj.organization_id
AND mic.category_set_id = 1100009407
AND mic.category_id = mc.category_id
AND ROWNUM = 1))
emr_wip_prod_line,
NVL (
(SELECT ola.flow_status_code
FROM apps.oe_order_lines_all ola
WHERE xx.header_id = ola.header_id
AND xx.line_number = ola.line_number
AND ola.item_type_code = 'CONFIG'
AND ROWNUM = 1),
wsa.flow_status_code)
line_status,
'WO'
start_date_type,
msi.segment1
item_number,
(SELECT m.segment1
FROM apps.wip_requirement_operations wro,
apps.mtl_system_items_b m
WHERE wro.organization_id IN (:OrgId)
AND wro.wip_entity_id = we.wip_entity_id
AND wro.inventory_item_id = m.inventory_item_id
AND wro.organization_id = m.organization_id
AND m.segment1 LIKE NVL (:Item, '%')
AND ROWNUM = 1)
selected_item,
xx.booked_date,
xx.line_id,
xx.top_model_line_id
FROM apps.wip_discrete_jobs wdj,
apps.wip_entities we,
( SELECT MIN (ol.line_id) so_line_id,
x.wip_entity_name wo,
x.organization_id org_id,
x.wip_entity_id,
ol.flow_status_code
FROM (SELECT e.wip_entity_name,
e.wip_entity_id,
u.line_id so_line,
e.organization_id,
l.flow_status_code
FROM apps.xxurd_so_unit u,
apps.wip_entities e,
apps.oe_order_lines_all l
WHERE u.wip_order_number = e.wip_entity_name
AND u.line_id = l.line_id
AND l.ship_from_org_id = e.organization_id) x,
ont.oe_order_lines_all ol
WHERE x.so_line = ol.line_id
GROUP BY x.wip_entity_name,
x.organization_id,
x.wip_entity_id,
ol.flow_status_code) wsa,
(SELECT ol.line_id,
lpl.model_string,
oh.order_number,
ol.line_number,
os.set_name,
ol.shipment_priority_code,
ol.promise_date,
ol.request_date,
ol.item_type_code,
assembly_type,
oh.header_id,
ol.ordered_item_id,
ol.schedule_ship_date,
lpl.original_promise_date,
xss.subscriber_name order_admin,
ott.name order_type,
p.party_name customer,
lpl.assemble_to,
oh.booked_date,
ol.top_model_line_id
FROM apps.oe_order_lines_all ol,
apps.oe_sets os,
apps.oe_order_headers_all oh,
apps.XXOM_3LP_SYM_ORA_ORDER_LINES lpl,
apps.xxom_3lp_sym_ora_order_hdr lph,
apps.xxont_som_scheduler xss,
apps.oe_transaction_types_tl ott,
apps.HZ_CUST_ACCOUNTS CA,
apps.HZ_PARTIES P
WHERE ol.header_id = oh.header_id
AND ol.line_id = lpl.line_id
AND ol.ship_set_id = os.set_id(+)
AND oh.header_id = lph.header_id
AND lph.order_admin = xss.subscriber_id
AND oh.order_type_id = ott.transaction_type_id
AND ott.LANGUAGE = 'US'
AND OH.SOLD_TO_ORG_ID = CA.CUST_ACCOUNT_ID
AND P.PARTY_ID = CA.PARTY_ID) XX,
apps.mtl_system_items_b msi,
apps.mtl_parameters mp,
apps.wip_lines wl
WHERE wdj.organization_id IN (:OrgId)
AND wdj.status_type IN (1, 3, 6)
AND wdj.wip_entity_id = we.wip_entity_id
AND we.wip_entity_id = wsa.wip_entity_id(+)
AND wsa.so_line_id = XX.line_id(+)
AND ( NVL (XX.model_string, '%') LIKE
NVL (:ModelString, '%')
OR NVL (msi.segment1, '%') LIKE NVL (:ModelString, '%'))
AND EXISTS
(SELECT 'X'
FROM apps.wip_requirement_operations wro,
apps.mtl_system_items_b m
WHERE wro.organization_id IN (:OrgId)
AND wro.wip_entity_id = we.wip_entity_id
AND wro.inventory_item_id =
m.inventory_item_id
AND wro.organization_id = m.organization_id
AND m.segment1 LIKE NVL (:Item, '%')
AND ROWNUM = 1
UNION
SELECT 'X'
FROM DUAL
WHERE :Item IS NULL
AND NOT EXISTS
(SELECT 'Y'
FROM apps.wip_requirement_operations
WHERE wip_entity_id =
we.wip_entity_id))
AND wdj.primary_item_id = msi.inventory_item_id
AND wdj.organization_id = msi.organization_id
AND msi.organization_id = mp.organization_id
AND wdj.line_id = wl.line_id(+)
UNION
SELECT mp.organization_code,
NULL
work_order,
NULL
wo_status,
-- (SELECT MAX (org_dates.dat)
-- FROM org_dates
-- WHERE org_dates.organization_id = mp.organization_id
-- AND org_dates.dat <=
-- CASE
-- WHEN TO_CHAR (
-- TRUNC (xx.schedule_ship_date)
-- - NVL (xx.PICK_PACK_LEAD_TIME, 0),
-- 'DY',
-- 'nls_date_language=english') IN
-- ('SAT', 'SUN')
-- THEN
-- NEXT_DAY (
-- TRUNC (xx.schedule_ship_date)
-- - NVL (xx.PICK_PACK_LEAD_TIME, 0)
-- - 7,
-- 'Friday')
-- ELSE
-- TRUNC (xx.schedule_ship_date)
-- - NVL (xx.PICK_PACK_LEAD_TIME, 0)
-- END)
-- wo_start_date,
XX.order_number
so_number,
XX.line_number
so_line,
XX.set_name
so_ship_set,
XX.shipment_priority_code
so_priority,
XX.promise_date,
XX.request_date,
XX.ordered_quantity
qty,
CASE
WHEN msi.segment1 LIKE 'R-%*%'
THEN
NVL (XX.model_string, msi.segment1)
ELSE
COALESCE (
CASE
WHEN msi.segment1 LIKE 'R-%'
THEN
XX.model_string
END,
(SELECT DISTINCT
FIRST_VALUE (
lpl.model_string)
OVER (
PARTITION BY ol.top_model_line_id
ORDER BY
CASE
WHEN lpl.model_string
IS NOT NULL
THEN
0
ELSE
1
END,
ol.line_number,
ol.shipment_number,
ol.option_number,
ol.component_number,
ol.service_number,
ol.line_id)
FROM apps.oe_order_lines_all ol,
apps.XXOM_3LP_SYM_ORA_ORDER_LINES lpl
WHERE ol.top_model_line_id =
COALESCE (xx.top_model_line_id,
xx.line_id)
AND ol.header_id = xx.header_id
AND ol.line_id = lpl.line_id
AND lpl.model_string IS NOT NULL),
msi.segment1)
END
item,
NULL
wo_notes,
CASE
WHEN SUBSTR (xx.assembly_type, 1, 2) IN ('IS', 'IP', 'I')
THEN
(SELECT MIN (ordered_item)
FROM apps.oe_order_lines_all
WHERE header_id = xx.header_id
AND line_number = xx.line_number
AND ordered_item LIKE 'RA-%*%')
ELSE
NULL
END
ra_star_item,
CASE
WHEN SUBSTR (xx.assembly_type, 1, 2) IN ('IS', 'IP', 'I')
THEN
(SELECT MIN (u2.wip_order_number)
FROM ont.oe_order_lines_all ol2,
apps.xxurd_so_unit u2
WHERE ol2.header_id = xx.header_id
AND ol2.line_number = xx.line_number
AND ol2.ordered_item LIKE 'RA-%'
AND ol2.item_type_code = 'MODEL'
AND ol2.line_id = u2.line_id)
ELSE
NULL
END
ra_wo,
NULL
firm_planned_flag,
NULL
orig_wo_start_date,
NULL
scheduled_completion_date,
xx.schedule_ship_date,
NVL (xx.original_promise_date, xx.promise_date)
original_promise_date,
NULL
wo_cause_code,
xx.order_admin,
xx.order_type,
xx.customer,
xx.header_id,
(SELECT MAX (mc1.segment1)
FROM apps.mtl_item_categories mic1,
apps.mtl_categories_b mc1
WHERE msi.inventory_item_id = mic1.inventory_item_id
AND msi.organization_id = mic1.organization_id
AND mic1.category_set_id = 1100009407
AND mic1.category_id = mc1.category_id)
emr_wip_prod_line,
XX.flow_status_code
line_status,
CASE
WHEN xx.item_type_code = 'CONFIG' THEN 'STAR'
ELSE 'PP'
END
start_date_type,
msi.segment1
item_number,
(SELECT msit.segment1
FROM apps.oe_order_lines_all ool,
apps.mtl_system_items_b msit
WHERE xx.ship_from_org_id = ool.ship_from_org_id
AND xx.header_id = ool.header_id
AND NVL (xx.top_model_line_id, xx.line_id) =
NVL (ool.top_model_line_id, ool.line_id)
AND ool.inventory_item_id = msit.inventory_item_id
AND ool.ship_from_org_id = msit.organization_id
AND msit.segment1 LIKE NVL (:Item, '%')
AND :Item IS NOT NULL
AND ROWNUM = 1)
selected_item,
xx.booked_date,
xx.line_id,
xx.top_model_line_id
FROM (SELECT ol.line_id,
ol.top_model_line_id,
lpl.model_string,
oh.order_number,
ol.line_number,
os.set_name,
ol.shipment_priority_code,
ol.promise_date,
ol.request_date,
ol.item_type_code,
assembly_type,
oh.header_id,
ol.ordered_item_id,
ol.schedule_ship_date,
lpl.original_promise_date,
xss.subscriber_name
order_admin,
ott.name
order_type,
p.party_name
customer,
lpl.assemble_to,
ol.flow_status_code,
ol.ordered_item,
ol.ship_from_org_id,
ol.ordered_quantity,
oh.booked_date,
(SELECT MAX (lpl2.PICK_PACK_LEAD_TIME)
FROM apps.oe_order_lines_all ol2,
apps.XXOM_3LP_SYM_ORA_ORDER_LINES lpl2
WHERE ol2.top_model_line_id =
COALESCE (ol.top_model_line_id,
ol.line_id)
AND ol2.header_id = ol.header_id
AND ol2.line_id = lpl2.line_id
AND lpl2.model_string IS NOT NULL)
PICK_PACK_LEAD_TIME
FROM apps.oe_order_lines_all ol,
apps.oe_sets os,
apps.oe_order_headers_all oh,
apps.XXOM_3LP_SYM_ORA_ORDER_LINES lpl,
apps.xxom_3lp_sym_ora_order_hdr lph,
apps.xxont_som_scheduler xss,
apps.oe_transaction_types_tl ott,
apps.HZ_CUST_ACCOUNTS CA,
apps.HZ_PARTIES P
WHERE ol.header_id = oh.header_id
AND ol.ship_from_org_id IN (:OrgId)
AND oh.booked_flag = 'Y'
AND ol.open_flag = 'Y'
AND ol.cancelled_flag = 'N'
AND ol.line_id = lpl.line_id
AND ol.ship_set_id = os.set_id(+)
AND oh.header_id = lph.header_id
AND lph.order_admin = xss.subscriber_id
AND oh.order_type_id = ott.transaction_type_id
AND ott.LANGUAGE = 'US'
AND OH.SOLD_TO_ORG_ID = CA.CUST_ACCOUNT_ID
AND P.PARTY_ID = CA.PARTY_ID
AND ( ( ol.item_type_code = 'CONFIG'
AND ol.flow_status_code = 'SUPPLY_ELIGIBLE')
OR ( ol.item_type_code = 'STANDARD'
AND ol.flow_status_code =
'AWAITING_SHIPPING')
OR ( ol.item_type_code = 'MODEL'
AND ol.flow_status_code = 'BOOKED'
AND NOT EXISTS
(SELECT 'X'
FROM apps.oe_order_lines_all ool
WHERE ol.ship_from_org_id =
ool.ship_from_org_id
AND ol.header_id =
ool.header_id
AND NVL (
ol.top_model_line_id,
ol.line_id) =
NVL (
ool.top_model_line_id,
ool.line_id)
AND ool.item_type_code =
'CONFIG')))) XX,
apps.mtl_system_items_b msi,
apps.mtl_parameters mp
WHERE ( NVL (XX.model_string, '%') LIKE
NVL (:ModelString, '%')
OR NVL (msi.segment1, '%') LIKE NVL (:ModelString, '%'))
AND EXISTS
(SELECT 'X'
FROM apps.oe_order_lines_all ool,
apps.mtl_system_items_b msit
WHERE xx.ship_from_org_id =
ool.ship_from_org_id
AND xx.header_id = ool.header_id
AND NVL (xx.top_model_line_id, xx.line_id) =
NVL (ool.top_model_line_id, ool.line_id)
AND ool.inventory_item_id =
msit.inventory_item_id
AND ool.ship_from_org_id =
msit.organization_id
AND msit.segment1 LIKE NVL (:Item, '%')
AND :Item IS NOT NULL
UNION
SELECT 'X'
FROM DUAL
WHERE :Item IS NULL)
AND XX.ordered_item_id = msi.inventory_item_id
AND XX.ship_from_org_id = msi.organization_id
AND msi.organization_id = mp.organization_id)
```