Home » RDBMS Server » Performance Tuning » Sql having performance issue (19c)
 Sql having performance issue [message #686704] |
Sun, 27 November 2022 02:53  |
 |
nishant87
Messages: 53
Registered: September 2013
Location: india
|
Member |
|
|
One of the sql having performance issue and the issue is Min(from_date) and max(from_date).After commenting out data comes very quickly as it is in with select clause.I am attaching sql text.how to handle Min(from_date) and Max(from_date)
WITH
EXCEPTIONS
AS
(SELECT e.organization_code
ORG,
e.exception_type,
e.order_type_id,
e.item_segments
ITEM,
si.sr_inventory_item_id,
e.inventory_item_id,
e.item_description
ITEM_DESCRIPTION,
e.supplier_name
SUPPLIER_NAME,
e.supplier_site
SUPPLIER_SITE,
e.order_type
ORDER_TYPE,
e.quantity
QUANTITY,
e.old_due_date
CURRENTLY_SCHED_DUE_DATE,
e.new_dock_date
SUGGESTED_DOCK_DATE,
e.planner_code
PLANNER_CODE,
e.excess_duration
max_excess_duration,
(SELECT COUNT (*) - 1
FROM apps.bom_calendar_dates
WHERE calendar_code =
(SELECT calendar_code
FROM apps.mtl_parameters
WHERE organization_id = e.organization_id)
AND calendar_date >=
(SELECT MIN (from_date)
FROM [email]apps.msc_exception_details_v@xxbetsytobilly.emrsn.com[/email]
WHERE plan_id = e.plan_id
AND exception_type = e.exception_type
AND sr_instance_id = 81
AND organization_id = e.organization_id
AND inventory_item_id =
e.inventory_item_id
AND category_set_id = 24)
AND calendar_date <=
(SELECT MAX (TO_DATE)
FROM [email]apps.msc_exception_details_v@xxbetsytobilly.emrsn.com[/email]
WHERE plan_id = e.plan_id
AND exception_type = e.exception_type
AND sr_instance_id = 81
AND organization_id = e.organization_id
AND inventory_item_id =
e.inventory_item_id
AND category_set_id = 24)
AND seq_num IS NOT NULL)
shortage_duration2,
e.shortage_duration
shortage_duration,
ROUND (e.below_ss_qty_pct / 100, 3)
MAX_PERCENT_BELOW_SS,
e.from_date
EARLIEST_FROM_DATE,
e.TO_DATE
LATEST_TO_DATE,
ROUND ((e.quantity / e.below_ss_qty_pct) * 100, 0)
SAFETY_STOCK_QUANTITY,
e.source_organization_code,
e.category_name
COMMODITY_CODE,
e.order_date,
e.transaction_id,
e.organization_id,
s.order_number,
s.po_line_id,
(SELECT MIN (msr.sourcing_rule_name)
FROM [email]apps.msc_sr_assignments@xxbetsytobilly.emrsn.com[/email] msa,
[email]apps.msc_sourcing_rules@xxbetsytobilly.emrsn.com[/email] msr
WHERE msa.inventory_item_id = e.inventory_item_id
AND msa.organization_id = e.organization_id
AND msa.sr_instance_id = 81
AND msr.sr_instance_id = 81
AND msa.sourcing_rule_id = msr.sourcing_rule_id)
sourcing_rule,
si.replenish_to_order_flag,
si.planning_make_buy_code,
e.buyer_name,
e.exception_type_text
FROM [email]apps.msc_exception_details_v@xxbetsytobilly.emrsn.com[/email] e,
[email]apps.msc_system_items@xxbetsytobilly.emrsn.com[/email] si,
[email]apps.msc_supplies@xxbetsytobilly.emrsn.com[/email] s
WHERE e.plan_id = 1108
AND e.exception_type IN (2,
6,
7,
8,
10,
20,
15,
16)
AND e.sr_instance_id = 81
AND e.organization_id IN (16597,5000,10505,4154)
AND ( 'ALL' IN ('ALL')
OR e.planner_code IN ('ALL'))
AND e.category_set_id = 24 -- Inventory category set
AND e.inventory_item_id = si.inventory_item_id
AND e.organization_id = si.organization_id
AND e.plan_id = si.plan_id
AND e.sr_instance_id = si.sr_instance_id
AND e.plan_id = s.plan_id(+)
AND e.sr_instance_id = s.sr_instance_id(+)
AND e.transaction_id = s.transaction_id(+)),
PLANNEDORDERS
AS
(SELECT 'Recommendation',
mo.organization_code,
mo.item_segments,
mo.description,
mo.order_number,
mo.order_type_text,
mo.quantity_rate,
mo.new_dock_date,
mo.new_due_date,
mo.abc_class,
mo.planner_code,
si.full_lead_time
processing_lead_time,
mo.source_organization_code,
mo.new_order_date,
mo.inventory_item_id,
mo.organization_id,
si.planning_make_buy_code,
mo.buyer_name,
si.sr_inventory_item_id,
si.replenish_to_order_flag,
si.full_lead_time,
(SELECT MIN (msr.sourcing_rule_name)
FROM [email]apps.msc_sr_assignments@xxbetsytobilly.emrsn.com[/email] msa,
[email]apps.msc_sourcing_rules@xxbetsytobilly.emrsn.com[/email] msr
WHERE msa.inventory_item_id = mo.inventory_item_id
AND msa.organization_id = mo.organization_id
AND msa.sr_instance_id = 81
AND msr.sr_instance_id = 81
AND msa.sourcing_rule_id = msr.sourcing_rule_id)
sourcing_rule
FROM [email]apps.msc_orders_v@xxbetsytobilly.emrsn.com[/email] mo,
[email]apps.msc_system_items@xxbetsytobilly.emrsn.com[/email] si
WHERE mo.source_table = 'MSC_SUPPLIES'
AND mo.organization_id IN (16597,5000,10505,4154)
AND mo.plan_id = 1108
AND mo.order_type = 5 --Planned Orders
AND mo.days_from_today < 2
AND mo.category_set_id = 24
AND ( 'ALL' IN ('ALL')
OR mo.planner_code IN ('ALL'))
AND mo.inventory_item_id = si.inventory_item_id
AND mo.organization_id = si.organization_id
AND mo.plan_id = si.plan_id
AND mo.sr_instance_id = si.sr_instance_id),
X
AS
( /***** PO in receiving' and Purchase orders *****/
SELECT 1
AS querypart,
--regexp_replace(listagg(e.exception_type_text,',') WITHIN GROUP (Order by e.exception_type),'([^,]+)(,\1)+', '\1') EXCEPTION_TYPE
TO_CHAR (
REGEXP_REPLACE (
RTRIM (
XMLAGG (XMLELEMENT (e,
e.exception_type_text,
',').EXTRACT ('//text()') ORDER BY
e.exception_type).getclobval (),
', '),
'([^,]+)(,\1)+',
'\1'))
EXCEPTION_TYPE,
e.ORG,
e.item
ITEM,
e.item_description
ITEM_DESCRIPTION,
e.supplier_name
SUPPLIER_NAME,
e.supplier_site
SUPPLIER_SITE,
REGEXP_REPLACE (
(SELECT LISTAGG (
aslinner.primary_vendor_item, ', ')
WITHIN GROUP (ORDER BY
aslinner.primary_vendor_item)
FROM apps.po_approved_supplier_list aslinner
WHERE 1 = 1
AND aslinner.item_id = msib.inventory_item_id
AND aslinner.owning_organization_id =
msib.organization_id),
'([^,]+)(,\1)*(,|$)')
supplier_item /* comma list required*/
,
pha.segment1
order_number,
pla.line_num,
e.order_type
ORDER_TYPE,
e.quantity
QUANTITY,
e.CURRENTLY_SCHED_DUE_DATE,
e.SUGGESTED_DOCK_DATE,
a.abc_class_name
ABC_CLASSIFICATION,
e.planner_code
PLANNER_CODE,
NVL (e.buyer_name, ppf.full_name)
BUYER_CODE,
msib.full_lead_time
processing_lead_time,
MAX (e.max_excess_duration)
max_excess_duration,
MAX (e.shortage_duration2)
shortage_duration2,
pla.quantity * unit_price
amt,
pha.currency_code,
MAX (e.shortage_duration)
shortage_duration,
e.MAX_PERCENT_BELOW_SS,
mp.description
plnr_description,
MIN (e.EARLIEST_FROM_DATE)
EARLIEST_FROM_DATE,
MAX (e.LATEST_TO_DATE)
LATEST_TO_DATE,
e.SAFETY_STOCK_QUANTITY,
e.source_organization_code,
pla.creation_date
po_line_creation_date,
e.COMMODITY_CODE,
msib.attribute5
awu,
msib.attribute6
fwu,
e.order_date,
fcl.meaning
item_type,
msib.inventory_item_id,
msib.organization_id,
NVL (mc.segment1, e.replenish_to_order_flag)
assemble_to_order_flag,
q.comment1
comments,
e.sourcing_rule,
e.inventory_item_id
ascp_item_id,
DECODE (msib.release_time_fence_code,
1, 'Cumulative total lead time',
2, 'Cumulative manufacturing lead time',
3, 'Item total lead time',
4, 'User-defined time fence',
5, 'Do not autorelease',
6, 'Kanban Item (Do Not Release)')
release_time_fence_code,
msib.release_time_fence_days
FROM EXCEPTIONS e,
apps.mtl_abc_assignments_v a,
apps.MTL_SYSTEM_ITEMS_B msib,
apps.mtl_planners mp,
apps.po_lines_all pla,
apps.po_headers_all pha,
apps.fnd_common_lookups fcl,
apps.per_all_people_f ppf,
apps.mtl_item_categories mic,
apps.mtl_categories_b mc,
(SELECT ROWNUM,
qpcv.character6 item,
mp.organization_id,
qpcv.comment1,
qpcv.character2
FROM apps.qa_plans qp,
apps.mtl_parameters mp,
apps.qa_results qpcv
WHERE SUBSTR (qp.NAME, 1, 21) = 'EMR MATERIAL SHORTAGE'
AND mp.organization_id IN (16597,5000,10505,4154)
AND mp.organization_code = SUBSTR (qp.NAME, -3)
AND qp.plan_id = qpcv.plan_id
AND ( 'ALL' IN ('SHAIKH, IMRAN')
OR qpcv.character14 IN ('SHAIKH, IMRAN'))
AND ( 'ALL' IN ('ALL')
OR qpcv.character15 IN ('ALL'))) q
WHERE ( 'ALL' IN ('SHAIKH, IMRAN')
OR NVL (ppf.full_name, 'NONE') IN ('SHAIKH, IMRAN'))
AND ( e.planning_make_buy_code = 2
OR msib.item_type IN ('OP', 'OA'))
AND e.sr_inventory_item_id = a.inventory_item_id(+)
AND e.organization_id = a.organization_id(+)
AND a.assignment_group_id(+) IN (128222,2595,1168,3805)
AND e.organization_id = msib.organization_id
AND e.sr_inventory_item_id = msib.inventory_item_id
AND e.planner_code = mp.planner_code(+)
AND e.organization_id = mp.organization_id(+)
AND e.po_line_id = pla.po_line_id
AND pla.po_header_id = pha.po_header_id
AND e.order_type IN ('PO in receiving', 'Purchase order')
AND msib.item_type = fcl.lookup_code
AND fcl.lookup_type = 'ITEM_TYPE'
AND ('ALL' IN ('ALL') OR fcl.meaning IN ('ALL'))
AND msib.buyer_id = ppf.person_id(+)
---and nvl(effective_end_date,sysdate+1) > sysdate --Commented by SLK on 31-Jan-2018
AND NVL (ppf.effective_end_date, SYSDATE + 1) > SYSDATE ---Added by SLK on 31-Jan-2018
AND msib.organization_id = mic.organization_id(+)
AND msib.inventory_item_id = mic.inventory_item_id(+)
AND mic.category_set_id(+) = 1100001406
AND mic.category_id = mc.category_id(+)
AND msib.segment1 = q.item(+)
AND msib.organization_id = q.organization_id(+)
AND q.character2(+) = 'Open'
GROUP BY e.org,
e.item,
e.item_description,
e.supplier_name,
e.supplier_site,
pha.segment1,
pla.line_num,
e.order_type,
e.quantity,
e.CURRENTLY_SCHED_DUE_DATE,
e.SUGGESTED_DOCK_DATE,
a.abc_class_name,
e.planner_code,
msib.full_lead_time,
pla.quantity * unit_price,
pha.currency_code,
e.SAFETY_STOCK_QUANTITY,
mp.description,
e.MAX_PERCENT_BELOW_SS,
e.source_organization_code,
pla.creation_date,
e.COMMODITY_CODE,
msib.attribute5,
msib.attribute6,
e.order_date,
fcl.meaning,
msib.inventory_item_id,
msib.organization_id,
NVL (e.buyer_name, ppf.full_name),
NVL (mc.segment1, e.replenish_to_order_flag),
q.comment1,
e.inventory_item_id,
e.organization_id,
e.sourcing_rule,
e.inventory_item_id,
msib.release_time_fence_code,
msib.release_time_fence_days
UNION /***** Purchase Requisitions *******/
SELECT 2
AS querypart,
e.exception_type_text
EXCEPTION_TYPE,
e.ORG,
e.item,
e.item_description
ITEM_DESCRIPTION,
e.supplier_name
SUPPLIER_NAME,
e.supplier_site
SUPPLIER_SITE,
REGEXP_REPLACE (
(SELECT LISTAGG (
aslinner.primary_vendor_item, ', ')
WITHIN GROUP (ORDER BY
aslinner.primary_vendor_item)
FROM apps.po_approved_supplier_list aslinner
WHERE 1 = 1
AND aslinner.item_id = msib.inventory_item_id
AND aslinner.owning_organization_id =
msib.organization_id),
'([^,]+)(,\1)*(,|$)')
supplier_item /* comma list required*/
,
rha.segment1
order_num,
rla.line_num,
e.order_type
ORDER_TYPE,
e.quantity
QUANTITY,
e.CURRENTLY_SCHED_DUE_DATE,
e.SUGGESTED_DOCK_DATE,
a.abc_class_name
ABC_CLASSIFICATION,
e.planner_code
PLANNER_CODE,
NVL (e.buyer_name, ppf.full_name)
BUYER_CODE,
msib.full_lead_time
processing_lead_time,
e.max_excess_duration,
e.shortage_duration2,
rla.quantity * unit_price
amt,
rla.currency_code,
e.shortage_duration,
e.MAX_PERCENT_BELOW_SS,
mp.description
plnr_description,
e.EARLIEST_FROM_DATE,
e.LATEST_TO_DATE,
e.SAFETY_STOCK_QUANTITY,
e.source_organization_code,
NULL
po_line_creation_date,
e.COMMODITY_CODE,
msib.attribute5
awu,
msib.attribute6
fwu,
e.order_date,
fcl.meaning,
msib.inventory_item_id,
msib.organization_id,
NVL (mc.segment1, e.replenish_to_order_flag)
assemble_to_order_flag,
q.comment1
comments,
e.sourcing_rule,
e.inventory_item_id
ascp_item_id,
DECODE (msib.release_time_fence_code,
1, 'Cumulative total lead time',
2, 'Cumulative manufacturing lead time',
3, 'Item total lead time',
4, 'User-defined time fence',
5, 'Do not autorelease',
6, 'Kanban Item (Do Not Release)')
release_time_fence_code,
msib.release_time_fence_days
FROM EXCEPTIONS e,
apps.mtl_abc_assignments_v a,
apps.MTL_SYSTEM_ITEMS_B msib,
apps.mtl_planners mp,
apps.po_requisition_lines_all rla,
apps.po_requisition_headers_all rha,
apps.fnd_common_lookups fcl,
apps.per_all_people_f ppf,
apps.mtl_item_categories mic,
apps.mtl_categories_b mc,
(SELECT ROWNUM,
qpcv.character6 item,
mp.organization_id,
qpcv.comment1,
qpcv.character2
FROM apps.qa_plans qp,
apps.mtl_parameters mp,
apps.qa_results qpcv
WHERE SUBSTR (qp.NAME, 1, 21) = 'EMR MATERIAL SHORTAGE'
AND mp.organization_id IN (16597,5000,10505,4154)
AND mp.organization_code = SUBSTR (qp.NAME, -3)
AND qp.plan_id = qpcv.plan_id
AND ( 'ALL' IN ('SHAIKH, IMRAN')
OR qpcv.character14 IN ('SHAIKH, IMRAN'))
AND ( 'ALL' IN ('ALL')
OR qpcv.character15 IN ('ALL'))) q
WHERE ( 'ALL' IN ('SHAIKH, IMRAN')
OR NVL (NVL (e.buyer_name, ppf.full_name), 'NONE') IN
('SHAIKH, IMRAN'))
AND ( e.planning_make_buy_code = 2
OR msib.item_type IN ('OP', 'OA'))
AND e.sr_inventory_item_id = a.inventory_item_id(+)
AND e.organization_id = a.organization_id(+)
AND a.assignment_group_id(+) IN (128222,2595,1168,3805)
AND e.organization_id = msib.organization_id
AND e.sr_inventory_item_id = msib.inventory_item_id
AND e.planner_code = mp.planner_code(+)
AND e.organization_id = mp.organization_id(+)
AND e.po_line_id = rla.requisition_line_id
AND rla.requisition_header_id = rha.requisition_header_id
AND e.order_type = 'Purchase requisition'
AND msib.item_type = fcl.lookup_code
AND fcl.lookup_type = 'ITEM_TYPE'
AND ('ALL' IN ('ALL') OR fcl.meaning IN ('ALL'))
AND msib.buyer_id = ppf.person_id(+)
---and nvl(effective_end_date,sysdate+1) > sysdate --Commented by SLK on 31-Jan-2018
AND NVL (ppf.effective_end_date, SYSDATE + 1) > SYSDATE ---Added by SLK on 31-Jan-2018
AND msib.organization_id = mic.organization_id(+)
AND msib.inventory_item_id = mic.inventory_item_id(+)
AND mic.category_set_id(+) = 1100001406
AND mic.category_id = mc.category_id(+)
AND msib.segment1 = q.item(+)
AND msib.organization_id = q.organization_id(+)
AND q.character2(+) = 'Open'
UNION /*** not in Purchase order, Purchase requisition, Work order, non-standard job, PO in receiving ***/
SELECT 3
AS querypart,
e.exception_type_text
EXCEPTION_TYPE,
e.ORG,
e.ITEM,
e.item_description
ITEM_DESCRIPTION,
e.supplier_name
SUPPLIER_NAME,
e.supplier_site
SUPPLIER_SITE,
REGEXP_REPLACE (
(SELECT LISTAGG (
aslinner.primary_vendor_item, ', ')
WITHIN GROUP (ORDER BY
aslinner.primary_vendor_item)
FROM apps.po_approved_supplier_list aslinner
WHERE 1 = 1
AND aslinner.item_id = msib.inventory_item_id
AND aslinner.owning_organization_id =
msib.organization_id),
'([^,]+)(,\1)*(,|$)')
supplier_item /* comma list required*/
,
CASE
WHEN e.exception_type = 10
THEN
(SELECT MIN (rha.segment1)
FROM [email]apps.msc_supplies@xxbetsytobilly.emrsn.com[/email] ms,
apps.rcv_shipment_lines rsl,
apps.po_requisition_lines_all rla,
apps.po_requisition_headers_all rha
WHERE ms.transaction_id = e.transaction_id
AND ms.po_line_id = rsl.shipment_line_id
AND rsl.requisition_line_id =
rla.requisition_line_id
AND rla.requisition_header_id =
rha.requisition_header_id)
ELSE
e.order_number
END
ORDER_NUMBER,
CASE
WHEN e.exception_type = 10
THEN
(SELECT MIN ((rla.line_num))
FROM [email]apps.msc_supplies@xxbetsytobilly.emrsn.com[/email] ms,
apps.rcv_shipment_lines rsl,
apps.po_requisition_lines_all rla,
apps.po_requisition_headers_all rha
WHERE ms.transaction_id = e.transaction_id
AND ms.po_line_id = rsl.shipment_line_id
AND rsl.requisition_line_id =
rla.requisition_line_id
AND rla.requisition_header_id =
rha.requisition_header_id
AND rha.segment1 =
(SELECT MIN (rha1.segment1)
FROM [email]apps.msc_supplies@xxbetsytobilly.emrsn.com[/email]
ms1,
apps.rcv_shipment_lines rsl1,
apps.po_requisition_lines_all
rla1,
apps.po_requisition_headers_all
rha1
WHERE ms1.transaction_id =
e.transaction_id
AND ms1.po_line_id =
rsl1.shipment_line_id
AND rsl1.requisition_line_id =
rla1.requisition_line_id
AND rla1.requisition_header_id =
rha1.requisition_header_id))
ELSE
NULL
END
line_num,
e.order_type
ORDER_TYPE,
e.quantity
QUANTITY,
e.CURRENTLY_SCHED_DUE_DATE,
e.SUGGESTED_DOCK_DATE,
a.abc_class_name
ABC_CLASSIFICATION,
e.planner_code
PLANNER_CODE,
NVL (e.buyer_name, ppf.full_name)
BUYER_CODE,
msib.full_lead_time
processing_lead_time,
e.max_excess_duration,
e.shortage_duration2,
NULL
amt,
NULL
currency_code,
e.shortage_duration,
e.MAX_PERCENT_BELOW_SS,
mp.description
plnr_description,
e.EARLIEST_FROM_DATE,
e.LATEST_TO_DATE,
e.SAFETY_STOCK_QUANTITY,
e.source_organization_code,
NULL
po_line_creation_date,
e.COMMODITY_CODE,
msib.attribute5
awu,
msib.attribute6
fwu,
e.order_date,
fcl.meaning,
msib.inventory_item_id,
msib.organization_id,
NVL (mc.segment1, e.replenish_to_order_flag)
assemble_to_order_flag,
q.comment1
comments,
e.sourcing_rule,
e.inventory_item_id
ascp_item_id,
DECODE (msib.release_time_fence_code,
1, 'Cumulative total lead time',
2, 'Cumulative manufacturing lead time',
3, 'Item total lead time',
4, 'User-defined time fence',
5, 'Do not autorelease',
6, 'Kanban Item (Do Not Release)')
release_time_fence_code,
msib.release_time_fence_days
FROM EXCEPTIONS e,
apps.mtl_abc_assignments_v a,
apps.MTL_SYSTEM_ITEMS_B msib,
apps.mtl_planners mp,
apps.fnd_common_lookups fcl,
apps.per_all_people_f ppf,
apps.mtl_item_categories mic,
apps.mtl_categories_b mc,
(SELECT ROWNUM,
qpcv.character6 item,
mp.organization_id,
qpcv.comment1,
qpcv.character2
FROM apps.qa_plans qp,
apps.mtl_parameters mp,
apps.qa_results qpcv
WHERE SUBSTR (qp.NAME, 1, 21) = 'EMR MATERIAL SHORTAGE'
AND mp.organization_id IN (16597,5000,10505,4154)
AND mp.organization_code = SUBSTR (qp.NAME, -3)
AND qp.plan_id = qpcv.plan_id
AND ( 'ALL' IN ('SHAIKH, IMRAN')
OR qpcv.character14 IN ('SHAIKH, IMRAN'))
AND ( 'ALL' IN ('ALL')
OR qpcv.character15 IN ('ALL'))) q
WHERE ( 'ALL' IN ('SHAIKH, IMRAN')
OR NVL (NVL (e.buyer_name, ppf.full_name), 'NONE') IN
('SHAIKH, IMRAN'))
AND ( e.planning_make_buy_code = 2
OR msib.item_type IN ('OP', 'OA'))
AND e.sr_inventory_item_id = a.inventory_item_id(+)
AND e.organization_id = a.organization_id(+)
AND a.assignment_group_id(+) IN (128222,2595,1168,3805)
AND e.organization_id = msib.organization_id
AND e.sr_inventory_item_id = msib.inventory_item_id
AND e.planner_code = mp.planner_code(+)
AND e.organization_id = mp.organization_id(+)
AND e.order_type_id NOT IN (1,
2,
3,
7,
8) -- not in Purchase order, Purchase requisition, Work order, non-standard job, PO in receiving
AND msib.item_type = fcl.lookup_code
AND fcl.lookup_type = 'ITEM_TYPE'
AND ('ALL' IN ('ALL') OR fcl.meaning IN ('ALL'))
AND msib.buyer_id = ppf.person_id(+)
---and nvl(effective_end_date,sysdate+1) > sysdate --Commented by SLK on 31-Jan-2018
AND NVL (ppf.effective_end_date, SYSDATE + 1) > SYSDATE ---Added by SLK on 31-Jan-2018
AND msib.organization_id = mic.organization_id(+)
AND msib.inventory_item_id = mic.inventory_item_id(+)
AND mic.category_set_id(+) = 1100001406
AND mic.category_id = mc.category_id(+)
AND msib.segment1 = q.item(+)
AND msib.organization_id = q.organization_id(+)
AND q.character2(+) = 'Open'
UNION
SELECT 4
AS querypart,
e.exception_type_text,
e.ORG,
e.ITEM,
e.item_description
ITEM_DESCRIPTION,
e.supplier_name
SUPPLIER_NAME,
e.supplier_site
SUPPLIER_SITE,
REGEXP_REPLACE (
(SELECT LISTAGG (
aslinner.primary_vendor_item, ', ')
WITHIN GROUP (ORDER BY
aslinner.primary_vendor_item)
FROM apps.po_approved_supplier_list aslinner
WHERE 1 = 1
AND aslinner.item_id = msib.inventory_item_id
AND aslinner.owning_organization_id =
msib.organization_id),
'([^,]+)(,\1)*(,|$)')
supplier_item /* comma list required*/
,
e.order_number
ORDER_NUMBER,
NULL
line_num,
e.order_type
ORDER_TYPE,
MIN (e.quantity)
QUANTITY,
e.CURRENTLY_SCHED_DUE_DATE,
e.SUGGESTED_DOCK_DATE,
a.abc_class_name
ABC_CLASSIFICATION,
e.planner_code
PLANNER_CODE,
NVL (e.buyer_name, ppf.full_name)
BUYER_CODE,
msib.full_lead_time
processing_lead_time,
MAX (e.max_excess_duration)
max_excess_duration,
MAX (e.shortage_duration2),
NULL
amt,
NULL
currency_code,
MAX (e.shortage_duration),
e.MAX_PERCENT_BELOW_SS,
mp.description
plnr_description,
MIN (e.EARLIEST_FROM_DATE),
MAX (e.LATEST_TO_DATE),
e.SAFETY_STOCK_QUANTITY,
e.source_organization_code,
NULL
po_line_creation_date,
e.COMMODITY_CODE,
msib.attribute5
awu,
msib.attribute6
fwu,
e.order_date,
fcl.meaning,
msib.inventory_item_id,
msib.organization_id,
NVL (mc.segment1, e.replenish_to_order_flag)
assemble_to_order_flag,
q.comment1
comments,
e.sourcing_rule,
e.inventory_item_id
ascp_item_id,
DECODE (msib.release_time_fence_code,
1, 'Cumulative total lead time',
2, 'Cumulative manufacturing lead time',
3, 'Item total lead time',
4, 'User-defined time fence',
5, 'Do not autorelease',
6, 'Kanban Item (Do Not Release)')
release_time_fence_code,
msib.release_time_fence_days
FROM EXCEPTIONS e,
apps.mtl_abc_assignments_v a,
apps.MTL_SYSTEM_ITEMS_B msib,
apps.mtl_planners mp,
apps.fnd_common_lookups fcl,
apps.per_all_people_f ppf,
apps.mtl_item_categories mic,
apps.mtl_categories_b mc,
(SELECT ROWNUM,
qpcv.character6 item,
mp.organization_id,
qpcv.comment1,
qpcv.character2
FROM apps.qa_plans qp,
apps.mtl_parameters mp,
apps.qa_results qpcv
WHERE SUBSTR (qp.NAME, 1, 21) = 'EMR MATERIAL SHORTAGE'
AND mp.organization_id IN (16597,5000,10505,4154)
AND mp.organization_code = SUBSTR (qp.NAME, -3)
AND qp.plan_id = qpcv.plan_id
AND ( 'ALL' IN ('SHAIKH, IMRAN')
OR qpcv.character14 IN ('SHAIKH, IMRAN'))
AND ( 'ALL' IN ('ALL')
OR qpcv.character15 IN ('ALL'))) q
WHERE e.exception_type = 2
AND ( 'ALL' IN ('SHAIKH, IMRAN')
OR NVL (NVL (e.buyer_name, ppf.full_name), 'NONE') IN
('SHAIKH, IMRAN'))
AND ( e.planning_make_buy_code = 2
OR msib.item_type IN ('OP', 'OA'))
AND e.sr_inventory_item_id = a.inventory_item_id(+)
AND e.organization_id = a.organization_id(+)
AND a.assignment_group_id(+) IN (128222,2595,1168,3805)
AND e.organization_id = msib.organization_id
AND e.sr_inventory_item_id = msib.inventory_item_id
AND e.planner_code = mp.planner_code(+)
AND e.organization_id = mp.organization_id(+)
AND msib.item_type = fcl.lookup_code
AND fcl.lookup_type = 'ITEM_TYPE'
AND ('ALL' IN ('ALL') OR fcl.meaning IN ('ALL'))
AND msib.buyer_id = ppf.person_id(+)
---and nvl(effective_end_date,sysdate+1) > sysdate --Commented by SLK on 31-Jan-2018
AND NVL (ppf.effective_end_date, SYSDATE + 1) > SYSDATE ---Added by SLK on 31-Jan-2018
AND msib.organization_id = mic.organization_id(+)
AND msib.inventory_item_id = mic.inventory_item_id(+)
AND mic.category_set_id(+) = 1100001406
AND mic.category_id = mc.category_id(+)
AND msib.segment1 = q.item(+)
AND msib.organization_id = q.organization_id(+)
AND q.character2(+) = 'Open'
GROUP BY e.org,
e.exception_type_text,
e.item,
e.item_description,
e.supplier_name,
e.supplier_site,
e.order_number,
e.order_type,
e.CURRENTLY_SCHED_DUE_DATE,
e.SUGGESTED_DOCK_DATE,
a.abc_class_name,
e.planner_code,
msib.full_lead_time,
mp.description,
e.source_organization_code,
e.COMMODITY_CODE,
msib.attribute5,
msib.attribute6,
MAX_PERCENT_BELOW_SS,
e.order_date,
fcl.meaning,
msib.inventory_item_id,
msib.organization_id,
NVL (e.buyer_name, ppf.full_name),
NVL (mc.segment1, e.replenish_to_order_flag),
q.comment1,
e.inventory_item_id,
e.organization_id-- , e.max_excess_duration
,
e.SAFETY_STOCK_QUANTITY,
e.sourcing_rule,
e.inventory_item_id,
msib.release_time_fence_code,
msib.release_time_fence_days
UNION
SELECT 5
AS querypart,
e.exception_type_text,
e.ORG,
e.ITEM,
e.item_description
ITEM_DESCRIPTION,
e.supplier_name
SUPPLIER_NAME,
e.supplier_site
SUPPLIER_SITE,
REGEXP_REPLACE (
(SELECT LISTAGG (
aslinner.primary_vendor_item, ', ')
WITHIN GROUP (ORDER BY
aslinner.primary_vendor_item)
FROM apps.po_approved_supplier_list aslinner
WHERE 1 = 1
AND aslinner.item_id = msib.inventory_item_id
AND aslinner.owning_organization_id =
msib.organization_id),
'([^,]+)(,\1)*(,|$)')
supplier_item /* comma list required*/
,
e.order_number
ORDER_NUMBER,
NULL
line_num,
e.order_type
ORDER_TYPE,
MIN (e.quantity)
QUANTITY,
e.CURRENTLY_SCHED_DUE_DATE,
e.SUGGESTED_DOCK_DATE,
a.abc_class_name
ABC_CLASSIFICATION,
e.planner_code
PLANNER_CODE,
NVL (e.buyer_name, ppf.full_name)
BUYER_CODE,
msib.full_lead_time
processing_lead_time,
MAX (e.max_excess_duration),
MAX (e.shortage_duration2),
NULL
amt,
NULL
currency_code,
MAX (e.shortage_duration),
MIN (e.MAX_PERCENT_BELOW_SS)
MAX_PERCENT_BELOW_SS,
mp.description
plnr_description,
MIN (e.EARLIEST_FROM_DATE),
MAX (e.LATEST_TO_DATE),
MAX (e.SAFETY_STOCK_QUANTITY)
SAFETY_STOCK_QUANTITY,
e.source_organization_code,
NULL
po_line_creation_date,
e.COMMODITY_CODE,
msib.attribute5
awu,
msib.attribute6
fwu,
e.order_date,
fcl.meaning,
msib.inventory_item_id,
msib.organization_id,
NVL (mc.segment1, e.replenish_to_order_flag)
assemble_to_order_flag,
q.comment1
comments,
e.sourcing_rule,
e.inventory_item_id
ascp_item_id,
DECODE (msib.release_time_fence_code,
1, 'Cumulative total lead time',
2, 'Cumulative manufacturing lead time',
3, 'Item total lead time',
4, 'User-defined time fence',
5, 'Do not autorelease',
6, 'Kanban Item (Do Not Release)')
release_time_fence_code,
msib.release_time_fence_days
FROM EXCEPTIONS e,
apps.mtl_abc_assignments_v a,
apps.MTL_SYSTEM_ITEMS_B msib,
apps.mtl_planners mp,
apps.fnd_common_lookups fcl,
apps.per_all_people_f ppf,
apps.mtl_item_categories mic,
apps.mtl_categories_b mc,
(SELECT ROWNUM,
qpcv.character6 item,
mp.organization_id,
qpcv.comment1,
qpcv.character2
FROM apps.qa_plans qp,
apps.mtl_parameters mp,
apps.qa_results qpcv
WHERE SUBSTR (qp.NAME, 1, 21) = 'EMR MATERIAL SHORTAGE'
AND mp.organization_id IN (16597,5000,10505,4154)
AND mp.organization_code = SUBSTR (qp.NAME, -3)
AND qp.plan_id = qpcv.plan_id
AND ( 'ALL' IN ('SHAIKH, IMRAN')
OR qpcv.character14 IN ('SHAIKH, IMRAN'))
AND ( 'ALL' IN ('ALL')
OR qpcv.character15 IN ('ALL'))) q
WHERE e.exception_type = 20
AND ( 'ALL' IN ('SHAIKH, IMRAN')
OR NVL (NVL (e.buyer_name, ppf.full_name), 'NONE') IN
('SHAIKH, IMRAN'))
AND ( e.planning_make_buy_code = 2
OR msib.item_type IN ('OP', 'OA'))
AND e.sr_inventory_item_id = a.inventory_item_id(+)
AND e.organization_id = a.organization_id(+)
AND a.assignment_group_id(+) IN (128222,2595,1168,3805)
AND e.organization_id = msib.organization_id
AND e.sr_inventory_item_id = msib.inventory_item_id
AND e.planner_code = mp.planner_code(+)
AND e.organization_id = mp.organization_id(+)
AND msib.item_type = fcl.lookup_code
AND fcl.lookup_type = 'ITEM_TYPE'
AND ('ALL' IN ('ALL') OR fcl.meaning IN ('ALL'))
AND msib.buyer_id = ppf.person_id(+)
---and nvl(effective_end_date,sysdate+1) > sysdate --Commented by SLK on 31-Jan-2018
AND NVL (ppf.effective_end_date, SYSDATE + 1) > SYSDATE ---Added by SLK on 31-Jan-2018
AND msib.organization_id = mic.organization_id(+)
AND msib.inventory_item_id = mic.inventory_item_id(+)
AND mic.category_set_id(+) = 1100001406
AND mic.category_id = mc.category_id(+)
AND msib.segment1 = q.item(+)
AND msib.organization_id = q.organization_id(+)
AND q.character2(+) = 'Open'
GROUP BY e.org,
e.exception_type_text,
e.item,
e.item_description,
e.supplier_name,
e.supplier_site,
e.order_number,
e.order_type,
e.CURRENTLY_SCHED_DUE_DATE,
e.SUGGESTED_DOCK_DATE,
a.abc_class_name,
e.planner_code,
e.buyer_name,
msib.full_lead_time,
mp.description,
e.source_organization_code,
e.COMMODITY_CODE,
msib.attribute5,
msib.attribute6,
e.order_date,
fcl.meaning,
msib.inventory_item_id,
msib.organization_id,
NVL (e.buyer_name, ppf.full_name),
NVL (mc.segment1, e.replenish_to_order_flag),
q.comment1,
e.inventory_item_id,
e.organization_id-- , e.MAX_PERCENT_BELOW_SS
-- , e.SAFETY_STOCK_QUANTITY
,
e.sourcing_rule,
e.inventory_item_id,
msib.release_time_fence_code,
msib.release_time_fence_days
UNION /*** Planned Orders *****/
SELECT 6
AS querypart,
'Recommendation',
mo.organization_code,
mo.item_segments,
mo.description,
NULL
supplier,
NULL
supplier_site,
REGEXP_REPLACE (
(SELECT LISTAGG (
aslinner.primary_vendor_item, ', ')
WITHIN GROUP (ORDER BY
aslinner.primary_vendor_item)
FROM apps.po_approved_supplier_list aslinner
WHERE 1 = 1
AND aslinner.item_id = msib.inventory_item_id
AND aslinner.owning_organization_id =
msib.organization_id),
'([^,]+)(,\1)*(,|$)')
supplier_item /* comma list required*/
,
order_number,
NULL
line,
mo.order_type_text,
mo.quantity_rate,
new_dock_date,
mo.new_due_date,
mo.abc_class,
mo.planner_code,
NVL (mo.buyer_name, ppf.full_name),
mo.full_lead_time
processing_lead_time,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
mp.description,
NULL,
NULL,
NULL,
mo.source_organization_code,
NULL,
NULL,
NULL,
NULL,
mo.new_order_date,
fcl.meaning,
msib.inventory_item_id,
msib.organization_id,
NVL (mc.segment1, mo.replenish_to_order_flag)
assemble_to_order_flag,
q.comment1
comments,
mo.sourcing_rule,
mo.inventory_item_id
ascp_item_id,
DECODE (msib.release_time_fence_code,
1, 'Cumulative total lead time',
2, 'Cumulative manufacturing lead time',
3, 'Item total lead time',
4, 'User-defined time fence',
5, 'Do not autorelease',
6, 'Kanban Item (Do Not Release)')
release_time_fence_code,
msib.release_time_fence_days
FROM PLANNEDORDERS mo,
apps.mtl_system_items_b msib,
apps.fnd_common_lookups fcl,
apps.per_all_people_f ppf,
apps.mtl_planners mp,
apps.mtl_item_categories mic,
apps.mtl_categories_b mc,
(SELECT ROWNUM,
qpcv.character6 item,
mp.organization_id,
qpcv.comment1,
qpcv.character2
FROM apps.qa_plans qp,
apps.mtl_parameters mp,
apps.qa_results qpcv
WHERE SUBSTR (qp.NAME, 1, 21) = 'EMR MATERIAL SHORTAGE'
AND mp.organization_id IN (16597,5000,10505,4154)
AND mp.organization_code = SUBSTR (qp.NAME, -3)
AND qp.plan_id = qpcv.plan_id
AND ( 'ALL' IN ('SHAIKH, IMRAN')
OR qpcv.character14 IN ('SHAIKH, IMRAN'))
AND ( 'ALL' IN ('ALL')
OR qpcv.character15 IN ('ALL'))) q
WHERE mo.sr_inventory_item_id = msib.inventory_item_id
AND mo.organization_id = msib.organization_id
AND ( mo.planning_make_buy_code = 2
OR msib.item_type IN ('OP', 'OA'))
AND ( 'ALL' IN ('SHAIKH, IMRAN')
OR NVL (NVL (mo.buyer_name, ppf.full_name), 'NONE') IN
('SHAIKH, IMRAN'))
AND msib.item_type = fcl.lookup_code
AND fcl.lookup_type = 'ITEM_TYPE'
AND ('ALL' IN ('ALL') OR fcl.meaning IN ('ALL'))
AND msib.buyer_id = ppf.person_id(+)
AND NVL (ppf.effective_end_date, SYSDATE + 1) > SYSDATE
AND mo.planner_code = mp.planner_code(+)
AND mo.organization_id = mp.organization_id(+)
AND msib.organization_id = mic.organization_id(+)
AND msib.inventory_item_id = mic.inventory_item_id(+)
AND mic.category_set_id(+) = 1100001406
AND mic.category_id = mc.category_id(+)
AND msib.segment1 = q.item(+)
AND msib.organization_id = q.organization_id(+)
AND q.character2(+) = 'Open'
UNION
SELECT 7
AS querypart,
REGEXP_REPLACE (
LISTAGG (e.exception_type_text, ',')
WITHIN GROUP (ORDER BY e.exception_type),
'([^,]+)(,\1)+',
'\1')
EXCEPTION_TYPE,
e.ORG,
e.ITEM,
e.item_description
ITEM_DESCRIPTION,
pv.vendor_name
SUPPLIER_NAME,
pvs.vendor_site_code
SUPPLIER_SITE,
REGEXP_REPLACE (
(SELECT LISTAGG (
aslinner.primary_vendor_item, ', ')
WITHIN GROUP (ORDER BY
aslinner.primary_vendor_item)
FROM apps.po_approved_supplier_list aslinner
WHERE 1 = 1
AND aslinner.item_id = msib.inventory_item_id
AND aslinner.owning_organization_id =
msib.organization_id),
'([^,]+)(,\1)*(,|$)')
supplier_item /* comma list required*/
,
'WO' || we.wip_entity_name || ', PO' || pha.segment1
order_number,
pla.line_num
line_num,
'OSP-' || e.order_type
ORDER_TYPE,
e.quantity
QUANTITY,
e.CURRENTLY_SCHED_DUE_DATE,
e.SUGGESTED_DOCK_DATE,
a.abc_class_name
ABC_CLASSIFICATION,
e.planner_code
PLANNER_CODE,
NVL (e.buyer_name, ppf.full_name)
BUYER_CODE,
msib.full_lead_time
processing_lead_time,
MAX (e.max_excess_duration)
max_excess_duration,
MAX (e.shortage_duration2),
pla.quantity * unit_price
amt,
pha.currency_code
currency_code,
MAX (e.shortage_duration)
shortage_duration,
MIN (e.MAX_PERCENT_BELOW_SS)
MAX_PERCENT_BELO
[Updated on: Sun, 27 November 2022 02:57] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Sql having performance issue [message #686708 is a reply to message #686707] |
Sun, 27 November 2022 03:57  |
|
nishant87
Messages: 53
Registered: September 2013
Location: india
|
Member |
|
|
if i have to tune this part only how to do
WITH
EXCEPTIONS
AS
(SELECT e.organization_code
ORG,
e.exception_type,
e.order_type_id,
e.item_segments
ITEM,
si.sr_inventory_item_id,
e.inventory_item_id,
e.item_description
ITEM_DESCRIPTION,
e.supplier_name
SUPPLIER_NAME,
e.supplier_site
SUPPLIER_SITE,
e.order_type
ORDER_TYPE,
e.quantity
QUANTITY,
e.old_due_date
CURRENTLY_SCHED_DUE_DATE,
e.new_dock_date
SUGGESTED_DOCK_DATE,
e.planner_code
PLANNER_CODE,
e.excess_duration
max_excess_duration,
(SELECT COUNT (*) - 1
FROM apps.bom_calendar_dates
WHERE calendar_code =
(SELECT calendar_code
FROM apps.mtl_parameters
WHERE organization_id = e.organization_id)
AND calendar_date >=
(SELECT MIN (from_date)
FROM apps.msc_exception_details_v@xxbetsytobilly.emrsn.com
WHERE plan_id = e.plan_id
AND exception_type = e.exception_type
AND sr_instance_id = 81
AND organization_id = e.organization_id
AND inventory_item_id =
e.inventory_item_id
AND category_set_id = 24)
AND calendar_date <=
(SELECT MAX (TO_DATE)
FROM apps.msc_exception_details_v@xxbetsytobilly.emrsn.com
WHERE plan_id = e.plan_id
AND exception_type = e.exception_type
AND sr_instance_id = 81
AND organization_id = e.organization_id
AND inventory_item_id =
e.inventory_item_id
AND category_set_id = 24)
AND seq_num IS NOT NULL)
shortage_duration2,
e.shortage_duration
shortage_duration,
ROUND (e.below_ss_qty_pct / 100, 3)
MAX_PERCENT_BELOW_SS,
e.from_date
EARLIEST_FROM_DATE,
e.TO_DATE
LATEST_TO_DATE,
ROUND ((e.quantity / e.below_ss_qty_pct) * 100, 0)
SAFETY_STOCK_QUANTITY,
e.source_organization_code,
e.category_name
COMMODITY_CODE,
e.order_date,
e.transaction_id,
e.organization_id,
s.order_number,
s.po_line_id,
(SELECT MIN (msr.sourcing_rule_name)
FROM apps.msc_sr_assignments@xxbetsytobilly.emrsn.com msa,
apps.msc_sourcing_rules@xxbetsytobilly.emrsn.com msr
WHERE msa.inventory_item_id = e.inventory_item_id
AND msa.organization_id = e.organization_id
AND msa.sr_instance_id = 81
AND msr.sr_instance_id = 81
AND msa.sourcing_rule_id = msr.sourcing_rule_id)
sourcing_rule,
si.replenish_to_order_flag,
si.planning_make_buy_code,
e.buyer_name,
e.exception_type_text
FROM apps.msc_exception_details_v@xxbetsytobilly.emrsn.com e,
apps.msc_system_items@xxbetsytobilly.emrsn.com si,
apps.msc_supplies@xxbetsytobilly.emrsn.com s
WHERE e.plan_id = 1108
AND e.exception_type IN (2,
6,
7,
8,
10,
20,
15,
16)
AND e.sr_instance_id = 81
AND e.organization_id IN (16597,5000,10505,4154)
AND ( 'ALL' IN ('ALL')
OR e.planner_code IN ('ALL'))
AND e.category_set_id = 24 -- Inventory category set
AND e.inventory_item_id = si.inventory_item_id
AND e.organization_id = si.organization_id
AND e.plan_id = si.plan_id
AND e.sr_instance_id = si.sr_instance_id
AND e.plan_id = s.plan_id(+)
AND e.sr_instance_id = s.sr_instance_id(+)
AND e.transaction_id = s.transaction_id(+)
|
|
|
|
|
|
| Re: Sql having performance issue [message #686712 is a reply to message #686709] |
Mon, 28 November 2022 12:28 |
John Watson
Messages: 8976
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Quote:If you re-write it to comprehensible form, perhaps people will have a better chance of assisting. Be sure to provide the exec plan, too.
All I can say at the moment is that you are projecting scalar (well, I hope scalar) subqueries which is usually a Bad Thing.
|
|
|
|
Goto Forum:
Current Time: Sat May 03 11:56:34 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
