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 #686706 is a reply to message #686705] |
Sun, 27 November 2022 03:27  |
John Watson
Messages: 8963
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Nishant, that code is impossible for an outsider to debug. For example:
No comments. How can anyone assist without embedded descriptions of the row sources and what you are trying to do with them?
Those table aliases: e, a, and so on. You may be abe to see "a.whatever" and know to what row source it refers, no-one else does. You need to use meaningful aliases.
You aren't using ansi join syntax. Your WHERE clause is an incomprehensible jumble of join predicates and filter predicates. You could at least describe the table relationships.
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.
|
|
|
|
|
|
| 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: 8963
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: Thu Jan 23 11:35:56 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
