Hello,
one sql reported that has ORA -01427 SINGLE SUBQUERY RETURNS MORE ROWS issues.i know which sql have the issue
SELECT ooha.order_number
,
ottt_h.name
order_type,
oola.line_number,
oola.shipment_number,
oola.flow_status_code,
flv.MEANING
SHIP_STATUS,
oola.ordered_item,
msi.description,
(SELECT MAX (msi_com_assy.segment1)
FROM apps.oe_order_lines_all oola_com_assy,
apps.mtl_system_items_b msi_com_assy
WHERE oola_com_assy.top_model_line_id = oola.top_model_line_id
AND oola_com_assy.inventory_item_id =
msi_com_assy.inventory_item_id
AND oola_com_assy.ship_from_org_id =
msi_com_assy.organization_id
AND oola_com_assy.open_flag = 'Y'
AND oola_com_assy.booked_flag = 'Y'
AND oola_com_assy.cancelled_flag = 'N'
AND ( ( msi_com_assy.description LIKE '%COM ASSY%'
AND msi_com_assy.item_type <> 'CONFIGURED_ITEM')
OR ( msi_com_assy.item_type IN ('MI', 'BI')
AND msi_com_assy.description LIKE '%SENSOR%')))
COMMON_ASSEMBLY,
ottt_l.name
line_type,
(SELECT MAX (msi_com_assy.segment1)
FROM apps.oe_order_lines_all oola_com_assy,
apps.mtl_system_items_b msi_com_assy
WHERE oola_com_assy.top_model_line_id = oola.top_model_line_id
AND oola_com_assy.inventory_item_id =
msi_com_assy.inventory_item_id
AND oola_com_assy.ship_from_org_id =
msi_com_assy.organization_id
AND oola_com_assy.open_flag = 'Y'
AND oola_com_assy.booked_flag = 'Y'
AND oola_com_assy.cancelled_flag = 'N'
AND msi_com_assy.item_type = 'ATO'
AND msi_com_assy.segment1 NOT LIKE '%MTO%'
AND msi_com_assy.segment1 NOT LIKE 'FT_%'
AND msi_com_assy.segment1 NOT LIKE 'FE_%'
AND msi_com_assy.segment1 NOT LIKE '%OSS%'
AND msi_com_assy.segment1 NOT LIKE '%MOC%'
AND msi_com_assy.segment1 NOT LIKE 'AW%'
AND msi_com_assy.description NOT LIKE '%TRANSMITTER%'
AND msi_com_assy.description NOT LIKE '%CONTROLLER%')
SENSOR_BASE_MODEL,
(SELECT MAX (msi_com_assy.segment1)
FROM apps.oe_order_lines_all oola_com_assy,
apps.mtl_system_items_b msi_com_assy
WHERE oola_com_assy.top_model_line_id = oola.top_model_line_id
AND oola_com_assy.inventory_item_id =
msi_com_assy.inventory_item_id
AND oola_com_assy.ship_from_org_id =
msi_com_assy.organization_id
AND oola_com_assy.open_flag = 'Y'
AND oola_com_assy.booked_flag = 'Y'
AND oola_com_assy.cancelled_flag = 'N'
AND msi_com_assy.item_type = 'ATO'
AND msi_com_assy.segment1 NOT LIKE 'M-%'
AND msi_com_assy.segment1 NOT LIKE '%OSS%'
AND msi_com_assy.segment1 NOT LIKE '%MOC%'
AND msi_com_assy.segment1 NOT LIKE 'AW%'
AND msi_com_assy.description LIKE '%TRANSMITTER%'
AND msi_com_assy.description LIKE '%CONTROLLER%')
TRANSMITTER_BASE_MODEL,
(SELECT msi_base_item.segment1
FROM apps.mtl_system_items_b msi_base_item
WHERE msi.base_item_id = msi_base_item.inventory_item_id
AND msi.organization_id = msi_base_item.organization_id)
Star_Model_Base_Model -- this replaces subquery10 in original sql
,
(SELECT RTRIM (
XMLAGG (XMLELEMENT (E, xxl.MODEL_STRING, ' | ').EXTRACT (
'//text()') ORDER BY xxl.MODEL_STRING).GetClobVal (),
' | ')
FROM apps.XXOM_3LP_SYM_ORA_ORDER_LINES xxl
WHERE xxl.header_id = oola.header_id
AND xxl.PARENTLINENBR = oola.line_number) --------------------------------last
MODEL_STRINGS,
msi.planner_code,
mp.organization_code
ship_warehouse --add by saya 20180213
,
xola.requested_delivery_date,
oola.request_date --add by saya to capture opsd
,
xola.original_promise_date,
oola.promise_date,
oola.schedule_ship_date -- this replaces subquery15 in original sql
,
(SELECT MAX (TRUNC (holds.LAST_UPDATE_DATE))
FROM apps.oe_order_lines_all oola_holds,
apps.oe_order_holds_all holds,
apps.oe_hold_sources_all ohsa,
apps.oe_hold_definitions ohd
WHERE holds.line_id = oola_holds.line_id
AND holds.header_id = oola_holds.header_id
AND holds.HOLD_RELEASE_ID IS NOT NULL
AND oola_holds.header_id = oola.header_id
AND oola_holds.line_number = oola.line_number
AND oola_holds.shipment_number = oola.shipment_number
AND holds.hold_source_id = ohsa.hold_source_id
AND ohsa.hold_id = ohd.hold_id
AND ohd.name IN
('EMR Schedule Review Hold',
'PICK - VALIDATE ATP HOLD')
and rownum =1)
LAST_SCHED_HOLD_REL_DATE, ------------last
oola.ordered_quantity,
xxwip.SUM_RELEASED_QTY
RELEASED_WIP_QTY,
xxwip.SUM_PENDING_QTY
PENDING_RELEASE,
oola.ORDERED_QUANTITY - NVL (oola.SHIPPED_QUANTITY, 0)
BACKLOG_QTY,
packed_data.PACKED_QTY -- this gives rise to poor performance
,
wdd.PICKED_QUANTITY,
oola.SHIPPED_QUANTITY,
oola.CANCELLED_QUANTITY,
(SELECT os.set_name
FROM apps.OE_SETS os
WHERE oola.ship_set_id = os.SET_ID)
ship_set_name -- this replaces subquery6 in original sql
,
(SELECT RTRIM (
XMLAGG (XMLELEMENT (E, ohd.name, ' | ').EXTRACT (
'//text()') ORDER BY ohd.name).GetClobVal (),
' | ')
FROM apps.oe_order_lines_all oola_holds,
apps.oe_order_holds_all holds,
apps.oe_hold_sources_all ohsa,
apps.oe_hold_definitions ohd
WHERE holds.line_id = oola_holds.line_id
AND holds.header_id = oola_holds.header_id
AND holds.released_flag = 'N'
AND oola_holds.header_id = oola.header_id
AND oola_holds.line_number = oola.line_number
AND oola_holds.shipment_number = oola.shipment_number
AND holds.hold_source_id = ohsa.hold_source_id
AND ohsa.hold_id = ohd.hold_id)
ORDER_LINE_HOLDS -- this replaces subquery5 in original sql
,
(SELECT RTRIM (
XMLAGG (XMLELEMENT (E, oola_oss.ordered_item, ' | ').EXTRACT (
'//text()') ORDER BY oola_oss.ordered_item).GetClobVal (),
' | ')
FROM apps.oe_order_lines_all oola_oss
WHERE oola_oss.ordered_item LIKE '%-OSS%'
AND oola.header_id = oola_oss.header_id
AND oola.top_model_line_id = oola_oss.top_model_line_id
AND oola_oss.item_type_code = 'OPTION')
OSS_ITEMS,
msi.ATP_FLAG,
oola.DELIVERY_LEAD_TIME,
ooha.flow_status_code
header_status -- this replaces subquery 11 and 12 in original sql
,
-- (SELECT SUM (oola_p.UNIT_SELLING_PRICE * oola_p.PRICING_QUANTITY)
-- FROM apps.oe_order_lines_all oola_p
-- WHERE oola_p.header_id = oola.Header_id
-- AND oola_p.line_number = oola.line_number
-- AND oola_p.UNIT_SELLING_PRICE > 0)
-- SUM_EXTENDED_PRICE, --------------last
xxwip.MAX_TRACK_ID,
xxwip.MIN_ALLOCATION_TIME_STAMP ,
-- xxwip.MAX_WIP_STATUS
-- MAX_WIP_STATUS_In_DispatchFrom,
wdjv.wip_entity_name
MAX_WIP_JOB,
wdjv.status_type_disp
MAX_WO_Status,
xxwip.MIN_START_DATE,
xxwip.MAX_COMPLETION_DATE,
xxwip.MAX_ASSEMBLE_TO,
xxwip.MAX_ON_HAND_QTY,
oola.creation_date,
ooha.booked_flag,
msi.SHIPPABLE_ITEM_FLAG,
msi.INVENTORY_ITEM_FLAG,
msi.ITEM_TYPE,
oola.SCHEDULE_STATUS_CODE,
oola.FREIGHT_CARRIER_CODE,
oola.FOB_POINT_CODE,
oola.SHIPMENT_PRIORITY_CODE,
oola.SHIPPING_INSTRUCTIONS,
oola.FREIGHT_TERMS_CODE,
hl_ship.COUNTRY,
hl_ship.ADDRESS1,
hl_ship.ADDRESS2,
hl_ship.ADDRESS3,
hl_ship.POSTAL_CODE,
hl_ship.CITY,
hl_ship.STATE,
hl_ship.PROVINCE,
hl_ship.COUNTY,
hp_bill.PARTY_NAME
bill_to_customer,
hl_bill.COUNTRY
bill_to_country,
oola.PLANNING_PRIORITY,
ooha.CUST_PO_NUMBER,
wdd.RELEASED_STATUS,
flv.DESCRIPTION
shipping_desc,
ooha.transactional_curr_code
order_currency,
(SELECT xxsch.oracle_login
FROM apps.xxom_3lp_sym_ora_order_hdr xxhdr,
apps.XXONT_SOM_SCHEDULER xxsch
WHERE xxhdr.header_id = oola.header_id
AND xxhdr.order_admin = xxsch.subscriber_id) -------------------problematic
order_admin,
oola.actual_shipment_date,
wsh.delivery_status,
wsh.delivery_name
,
weldment_item.component_item_number
weldment_star_item --
,
(SELECT TO_CHAR (oer.creation_date, 'YYYY-MON-DD HH24:MI:SS')
FROM apps.OE_REASONS oer,
( SELECT oer1.ENTITY_CODE,
oer1.ENTITY_ID,
oer1.HEADER_ID,
MAX (oer1.CREATION_DATE) Max_Creation_Date
FROM apps.OE_REASONS oer1
WHERE oer1.entity_code = 'LINE'
GROUP BY oer1.ENTITY_CODE, oer1.ENTITY_ID, oer1.HEADER_ID) xx
WHERE 1 = 1
AND oer.entity_code = 'LINE'
AND oer.ENTITY_ID = oola.line_id
AND oer.HEADER_ID = oola.HEADER_ID
AND xx.ENTITY_CODE = oer.ENTITY_CODE
AND xx.ENTITY_ID = oer.ENTITY_ID
AND xx.HEADER_ID = oer.HEADER_ID
AND xx.Max_Creation_Date = oer.creation_date
AND (oer.reason_code IS NOT NULL OR oer.comments IS NOT NULL))
Reason_Date,
(SELECT oer.reason_code || ' | ' || NVL (oer.COMMENTS, ' ')
FROM apps.OE_REASONS oer,
( SELECT oer1.ENTITY_CODE,
oer1.ENTITY_ID,
oer1.HEADER_ID,
MAX (oer1.CREATION_DATE) Max_Creation_Date
FROM apps.OE_REASONS oer1
WHERE oer1.entity_code = 'LINE'
GROUP BY oer1.ENTITY_CODE, oer1.ENTITY_ID, oer1.HEADER_ID) xx
WHERE 1 = 1
AND oer.entity_code = 'LINE'
AND oer.ENTITY_ID = oola.line_id
AND oer.HEADER_ID = oola.HEADER_ID
AND xx.ENTITY_CODE = oer.ENTITY_CODE
AND xx.ENTITY_ID = oer.ENTITY_ID
AND xx.HEADER_ID = oer.HEADER_ID
AND xx.Max_Creation_Date = oer.creation_date
AND (oer.reason_code IS NOT NULL OR oer.comments IS NOT NULL))
Last_Reason_Info,
HOLDS_INFO.applied_hold_date,
HOLDS_INFO.Released_hold_date,
HOLDS_INFO.Release_reason_code,
HOLDS_INFO.release_comment
FROM apps.oe_order_lines_all oola,
apps.oe_order_headers_all ooha,
apps.oe_transaction_types_tl ottt_h,
apps.oe_transaction_types_tl ottt_l,
apps.oe_order_lines_all oola_t ,
apps.xxom_3lp_sym_ora_order_lines xola ,
apps.hz_cust_site_uses_all hcsua_ship,
apps.hz_cust_acct_sites_all hcasa_ship,
apps.hz_party_sites hps_ship,
apps.hz_locations hl_ship,
apps.hz_cust_accounts hca_ship,
apps.hz_parties hp_ship ,
apps.hz_cust_site_uses_all hcsua_bill,
apps.hz_cust_acct_sites_all hcasa_bill,
apps.hz_party_sites hps_bill,
apps.hz_locations hl_bill,
apps.hz_cust_accounts hca_bill,
apps.hz_parties hp_bill,
apps.wsh_delivery_details wdd,
APPS.FND_LOOKUP_VALUES_BAK flv,
apps.mtl_system_items_b msi,
apps.mtl_parameters mp -- this replaces subquery8 in original sql
,
(( SELECT xld.line_id line_id, SUM (xld.quantity) packed_qty
FROM xxom.XXOM_LPNS_DETAILS xld, xxom.XXOM_LPNS xl
WHERE xld.CONTAINER_ID = xl.CONTAINER_ID AND xl.VOID_FLAG = 'N'
GROUP BY xld.line_id)
UNION
( SELECT xld.ship_id line_id, SUM (xld.quantity) packed_qty
FROM xxom.XXOM_LPNS_DETAILS xld, xxom.XXOM_LPNS xl
WHERE xld.CONTAINER_ID = xl.CONTAINER_ID AND xl.VOID_FLAG = 'N'
GROUP BY xld.ship_id)) packed_data -- this replaces subquery 13 and 14 in the original sql
,
( SELECT xxwip.HEADER_ID,
xxwip.LINE,
--change by saya for get WO status
xxwip.organization_id,
MAX (xxwip.wip_entity_id)
AS MAX_wip_entity_id,
MAX (xxwip.WIP_JOB)
AS MAX_WIP_JOB,
MIN (xxwip.START_DATE)
AS MIN_START_DATE,
MAX (xxwip.COMPLETION_DATE)
AS MAX_COMPLETION_DATE,
MAX (xxwip.ASSEMBLE_TO)
AS MAX_ASSEMBLE_TO,
MAX (xxwip.LAST_OPERATION_TIME_STAMP)
AS MAX_LAST_OPERATION_TIME_STAMP,
MAX (xxwip.TRACK_ID)
AS MAX_TRACK_ID,
MIN (xxwip.ALLOCATION_TIME_STAMP)
AS MIN_ALLOCATION_TIME_STAMP,
MAX (xxwip.WIP_STATUS)
AS MAX_WIP_STATUS,
SUM (xxwip.RELEASED_QTY)
AS SUM_RELEASED_QTY,
SUM (xxwip.PENDING_QTY)
AS SUM_PENDING_QTY,
MAX (xxwip.ON_HAND_QTY)
AS MAX_ON_HAND_QTY
FROM APPS.XXWIP_RELEASE_FORM_MMI_V xxwip
GROUP BY xxwip.HEADER_ID, xxwip.LINE, xxwip.organization_id) xxwip --add by saya to get wo status
,
apps.wip_discrete_jobs_v wdjv,
(SELECT DISTINCT
wnd.name delivery_name,
flv.meaning delivery_status,
wda.delivery_detail_id
FROM apps.wsh_new_deliveries wnd,
apps.fnd_lookup_values flv,
apps.wsh_delivery_assignments wda
WHERE flv.lookup_type = 'DELIVERY_STATUS'
AND flv.language = 'US'
AND wnd.status_code = flv.lookup_code
AND wnd.delivery_id = wda.delivery_id) wsh -- added by Mark Brooks 1-Sep-2017
,
(SELECT child_structures.assembly_item_id,
child_structures.organization_id,
child_structures.bill_sequence_id,
child_structures.structure_type_id,
child_components.component_item_id,
child_components.item_num,
child_components.component_quantity,
child_components.component_sequence_id,
child_msi.segment1 component_item_number,
child_msi.item_type,
child_msi.inventory_item_status_code
FROM bom.bom_structures_b child_structures,
bom.bom_components_b child_components,
inv.mtl_system_items_b child_msi
WHERE child_structures.bill_sequence_id =
child_components.bill_sequence_id
AND child_components.component_item_id =
child_msi.inventory_item_id
AND child_structures.organization_id =
child_msi.organization_id
AND child_msi.item_type = 'CONFIGURED_ITEM'
AND child_msi.segment1 LIKE 'AW%*%') weldment_item --add by saya 20180312
,
(SELECT holds.line_id,
holds.header_id,
holds.creation_date
applied_hold_date,
holds.Last_update_date
Released_hold_date,
--ohr.Release_reason_code,
(SELECT FLV.DESCRIPTION
FROM APPS.FND_LOOKUP_VALUES FLV
WHERE FLV.LANGUAGE = 'US'
AND FLV.LOOKUP_CODE = ohr.Release_reason_code
AND FLV.LOOKUP_TYPE = 'RELEASE_REASON')
Release_reason_code,
ohr.release_comment
FROM apps.oe_order_holds_all holds,
apps.oe_hold_sources_all ohsa,
apps.oe_hold_definitions ohd,
apps.oe_hold_Releases ohr
WHERE 1 = 1
AND holds.creation_date IN
(SELECT MIN (oha2.creation_date)
FROM apps.oe_order_holds_all oha2
WHERE oha2.line_id(+) = holds.line_id
AND oha2.header_id(+) = holds.header_id)
AND holds.HOLD_RELEASE_ID IS NOT NULL
AND holds.hold_source_id = ohsa.hold_source_id(+)
AND ohsa.hold_id = ohd.hold_id(+)
AND ohsa.hold_release_id = ohr.hold_release_id(+)
AND ohd.name IN
('EMR Schedule Review Hold',
'PICK - VALIDATE ATP HOLD')
) HOLDS_INFO --add by TCarlson 20210216
WHERE oola.ship_from_org_id = 4681
AND oola.open_flag = 'Y'
AND oola.booked_flag = 'Y'
AND oola.cancelled_flag = 'N'
AND oola.flow_status_code != 'CLOSED'
AND oola.ordered_quantity > 0 -- this replaces subquery4 in the original SQL. I could not see a good need for the original SQL' use of partition by
AND oola.header_id = ooha.header_id
AND ooha.flow_status_code != 'CLOSED'
AND oola.line_type_id = ottt_l.transaction_type_id
AND ottt_l.language = USERENV ('LANG')
--add by saya for order type
AND ooha.order_type_id = ottt_h.transaction_type_id
AND ottt_h.language = USERENV ('LANG')
AND oola.top_model_line_id = oola_t.line_id(+)
-- ship to joins
AND oola.ship_to_org_id = hcsua_ship.site_use_id
AND hcsua_ship.cust_acct_site_id = hcasa_ship.cust_acct_site_id
AND hcasa_ship.party_site_id = hps_ship.party_site_id
AND hps_ship.location_id = hl_ship.location_id
AND hcasa_ship.cust_account_id = hca_ship.cust_account_id
AND hca_ship.party_id = hp_ship.party_id
-- bill to joins
AND oola.invoice_to_org_id = hcsua_bill.site_use_id
AND hcsua_bill.cust_acct_site_id = hcasa_bill.cust_acct_site_id
AND hcasa_bill.party_site_id = hps_bill.party_site_id
AND hps_bill.location_id = hl_bill.location_id
AND hcasa_bill.cust_account_id = hca_bill.cust_account_id
AND hca_bill.party_id = hp_bill.party_id
-- packed data joins
AND oola.line_id = packed_data.line_id(+) -- this sql results in bad performance
-- WIP view joins
AND oola.header_id = xxwip.header_id(+)
--change by saya as the condition is incorrect
--AND oola.line_id=xxwip.line(+)
AND oola.line_number = xxwip.line(+)
-- delivery detail joins, change to left join by saya
AND oola.line_id = wdd.source_line_id(+)
AND oola.header_id = wdd.source_header_id(+)
AND wdd.source_code(+) = 'OE'
AND flv.LOOKUP_TYPE(+) = 'PICK_STATUS'
AND flv.enabled_flag(+) = 'Y'
AND wdd.released_status = flv.lookup_code(+)
AND flv.language(+) = USERENV ('LANG')
-- msi joins
AND oola.inventory_item_id = msi.inventory_item_id
AND oola.ship_from_org_id = msi.organization_id
AND oola.ship_from_org_id = mp.organization_id
-- wsh joints
AND wdd.delivery_detail_id = wsh.delivery_detail_id(+)
--add by saya to capture opsd
AND oola.header_id = xola.header_id
AND oola.line_id = xola.line_id
AND oola.org_id = xola.org_id
AND xxwip.organization_id = wdjv.organization_id(+)
AND xxwip.MAX_wip_entity_id = wdjv.wip_entity_id(+)
--add by saya 20180312 for weldment * item
AND oola.inventory_item_id = weldment_item.assembly_item_id(+)
AND oola.ship_from_org_id = weldment_item.organization_id(+)
--add the condition by saya only screen the standard or * item line.
AND oola.item_type_code IN ('STANDARD', 'CONFIG')
AND ooha.order_number NOT IN ('31336568', '31336582')
AND HOLDS_INFO.line_id(+) = oola.line_id
AND HOLDS_INFO.header_id(+) = oola.header_id;
Issues is with below sql
SELECT holds.line_id,
holds.header_id,
holds.creation_date
applied_hold_date,
holds.Last_update_date
Released_hold_date,
--ohr.Release_reason_code,
(SELECT FLV.DESCRIPTION
FROM APPS.FND_LOOKUP_VALUES FLV
WHERE FLV.LANGUAGE = 'US'
AND FLV.LOOKUP_CODE = ohr.Release_reason_code
AND FLV.LOOKUP_TYPE = 'RELEASE_REASON')
Release_reason_code,
ohr.release_comment
FROM apps.oe_order_holds_all holds,
apps.oe_hold_sources_all ohsa,
apps.oe_hold_definitions ohd,
apps.oe_hold_Releases ohr
WHERE 1 = 1
AND holds.creation_date IN
(SELECT MIN (oha2.creation_date)
FROM apps.oe_order_holds_all oha2
WHERE oha2.line_id(+) = holds.line_id
AND oha2.header_id(+) = holds.header_id)
AND holds.HOLD_RELEASE_ID IS NOT NULL
AND holds.hold_source_id = ohsa.hold_source_id(+)
AND ohsa.hold_id = ohd.hold_id(+)
AND ohsa.hold_release_id = ohr.hold_release_id(+)
AND ohd.name IN
('EMR Schedule Review Hold',
'PICK - VALIDATE ATP HOLD')
) HOLDS_INFO
and second one
SELECT child_structures.assembly_item_id,
child_structures.organization_id,
child_structures.bill_sequence_id,
child_structures.structure_type_id,
child_components.component_item_id,
child_components.item_num,
child_components.component_quantity,
child_components.component_sequence_id,
child_msi.segment1 component_item_number,
child_msi.item_type,
child_msi.inventory_item_status_code
FROM bom.bom_structures_b child_structures,
bom.bom_components_b child_components,
inv.mtl_system_items_b child_msi
WHERE child_structures.bill_sequence_id =
child_components.bill_sequence_id
AND child_components.component_item_id =
child_msi.inventory_item_id
AND child_structures.organization_id =
child_msi.organization_id
AND child_msi.item_type = 'CONFIGURED_ITEM'
AND child_msi.segment1 LIKE 'AW%*%') weldment_item
how to fix this error,i am not sure of this