Hi All,
I have the enclosed query running rather slowly, and I'd like to enhance it
such that it finishes in a few seconds. The query is a SELECT statement on
a view and I enclosed the view text.
Please review the accompanying query and monitoring report and provide
input for improvements.
I appreciate your aid and support in advance.
Best Regards,
AMIT
SELECT ship_to_organization_code PDC
, (
SELECT aps.segment1
FROM po_headers_all poh
, ap_suppliers aps
, xxpo01t_asn_inbound asn_l --MITS
WHERE 1 = 1
AND ROWNUM = 1
AND asn_l.header_seq_id = asn_lines.header_seq_id --MITS
AND asn_l.line_seq_id = asn_lines.line_seq_id --MITS
AND asn_l.file_id = asn_lines.file_id--MITS
AND poh.attribute1 (+) = asn_lines.document_num
AND poh.org_id = asn_h.org_id
AND aps.vendor_id (+) = poh.vendor_id
) vendor_number
, (
SELECT apss.vendor_site_code
FROM ap_suppliers asa
, ap_supplier_sites_all apss
, po_headers_all poh
, xxpo01t_asn_inbound asn_l --MITS
WHERE 1 = 1
AND ROWNUM = 1
AND asn_l.header_seq_id = asn_lines.header_seq_id --MITS
AND asn_l.line_seq_id = asn_lines.line_seq_id --MITS
AND asn_l.file_id = asn_lines.file_id--MITS
AND asa.vendor_id = apss.vendor_id
AND poh.vendor_id = asa.vendor_id (+)
AND poh.vendor_site_id = apss.vendor_site_id (+)
AND poh.org_id = asn_h.org_id
AND poh.attribute1 (+) = asn_lines.document_num
) vendor_site_code
,
--aps.segment1 vendor_number,
-- apss.vendor_site_code,
asn_lines.shipped_date
, TO_CHAR (asn_lines.line_seq_id) line_seq_id
, asn_lines.expected_receipt_date
, asn_lines.invoice_num
, asn_lines.item_num
, asn_lines.item_description
, asn_lines.quantity_shipped Quantity
, asn_lines.po_unit_price FOB
, asn_lines.document_num po_number
, asn_lines.document_line_num po_line_num
, (
SELECT poh.segment1
FROM po_headers_all poh
, xxpo01t_asn_inbound asn_l
WHERE 1 = 1
AND ROWNUM = 1
AND poh.attribute1 (+) = asn_l.document_num
AND poh.org_id = asn_l.org_id
AND asn_l.header_seq_id = asn_lines.header_seq_id
AND asn_l.line_seq_id = asn_lines.line_seq_id
AND asn_l.file_id = asn_lines.file_id
--Start of MITS CR-190509-0077
AND poh.type_lookup_code = 'STANDARD'
AND NVL (poh.cancel_flag, 'N') = 'N'
AND NVL (poh.closed_code, 'OPEN') = 'OPEN'
--End of MITS CR-190509-0077
) ebs_po_num
, asn_lines.container_num
, asn_lines.attribute11 Case_num
, --asn_lines.attribute10 Case_num,
asn_lines.currency_code currency
, asn_lines.currency_conversion_date exchange_date
, asn_lines.currency_conversion_rate exchange_rate
, asn_lines.currency_conversion_type exchange_type
, asn_lines.attribute4 arrangement_num
, asn_lines.attribute5 arrangement_line_num
,
--start of 1.1 shipped_seeded_part
asn_lines.substitute_item_num
,
--end of 1.1 shipped_seeded_part
asn_lines.CREATED_BY created_by
, asn_lines.CREATION_DATE creation_date
, fnd_profile.value ('USER_ID') last_updated_by
, sysdate LAST_UPDATE_DATE
, fnd_profile.value ('LOGIN_ID') LAST_UPDATE_LOGIN
, asn_lines.file_id file_id
, TO_CHAR (asn_lines.header_seq_id) header_seq_id
, TO_CHAR (asn_lines.line_seq_id) line_id
, asn_lines.invoice_num rsn_num --CR8_OF_20
FROM XXPO01T_ASN_AG_LAYOUT ASN_H
, xxpo01t_asn_inbound asn_lines
-- ,xxif01w_interface_errors asn_errors
WHERE asn_lines.invoice_num = asn_h.invoice_number
AND asn_lines.header_seq_id = asn_h.header_seq_id
AND asn_lines.file_id = asn_h.file_id
AND asn_h.process_flag = '2'
AND asn_lines.org_id = fnd_profile.value ('ORG_ID')
-- Start of CR8_OF_20
AND (test_flag = 'E'
OR EXISTS (
SELECT orig_line_ref
FROM XXIF01W_INTERFACE_ERRORS asn_e
WHERE 1 = 1
AND asn_e.orig_line_ref = to_char (asn_lines.line_seq_id)
AND asn_e.orig_sys_ref = to_char (asn_lines.header_seq_id)
AND asn_e.attribute2 = to_char (asn_lines.file_id)
AND asn_e.interface_cd = 'OF075'
AND ebs_request_id = (
SELECT MAX (ebs_request_id)
FROM XXIF01W_INTERFACE_ERRORS asn_err
WHERE 1 = 1
AND asn_err.orig_sys_ref = asn_e.orig_sys_ref
AND asn_err.interface_cd = asn_e.interface_cd
)
)
) -- End of CR8_OF_20
UNION ALL -- UNION CR8_OF_20
SELECT ship_to_organization_code
PDC
, (
SELECT aps.segment1
FROM ap_suppliers aps
, ap_supplier_sites_all apss
, xxpo01t_asn_inbound asn_l --MITS
WHERE 1 = 1
AND ROWNUM = 1
AND asn_l.header_seq_id = asn_lines.header_seq_id --MITS
AND asn_l.line_seq_id = asn_lines.line_seq_id --MITS
AND asn_l.file_id = asn_lines.file_id--MITS
AND apss.vendor_site_code = asn_lines.vendor_site_code
AND aps.vendor_id = apss.vendor_id
---and poh.org_id = asn_lines.org_id
--and aps.vendor_id = poh.vendor_id
)
vendor_number
, asn_lines.vendor_site_code
, asn_lines.shipped_date
, TO_CHAR (asn_lines.line_seq_id)
line_seq_id
, asn_lines.expected_receipt_date
, asn_lines.invoice_num
, asn_lines.item_num
, asn_lines.item_description
, asn_lines.quantity_shipped
Quantity
, NULL
FOB
, asn_lines.document_num
po_number
, asn_lines.document_line_num
po_line_num
, (
SELECT poh.segment1
FROM po_headers_all poh
, xxpo01t_asn_inbound asn_l
WHERE 1 = 1
AND ROWNUM = 1
AND poh.attribute1 (+) = asn_l.document_num
AND poh.org_id = asn_l.org_id
AND asn_l.header_seq_id = asn_lines.header_seq_id
AND asn_l.line_seq_id = asn_lines.line_seq_id
AND asn_l.file_id = asn_lines.file_id
--Start of MITS CR-190509-0077
AND poh.type_lookup_code = 'STANDARD'
AND NVL (poh.cancel_flag, 'N') = 'N'
AND NVL (poh.closed_code, 'OPEN') = 'OPEN'
--End of MITS CR-190509-0077
)
ebs_po_num
, DECODE (interface_transaction_id, '856', asn_lines.container_num,
'856FORD', NULL)
container_number
, --asn_lines.container_num,-- CR16_OF_38 For FORD ASN
NULL
Case_num
, NULL
currency
, NULL
exchange_date
, NULL
exchange_rate
, NULL
exchange_type
, NULL
arrangement_num
, NULL
arrangement_line_num
,
--start of 1.1 shipped_seeded_part
asn_lines.substitute_item_num
,
--end of 1.1 shipped_seeded_part
asn_lines.CREATED_BY
created_by
, asn_lines.CREATION_DATE
creation_date
, fnd_profile.value ('USER_ID')
last_updated_by
, sysdate
LAST_UPDATE_DATE
, fnd_profile.value ('LOGIN_ID')
LAST_UPDATE_LOGIN
, asn_lines.file_id
file_id
, TO_CHAR (asn_lines.header_seq_id)
header_seq_id
, TO_CHAR (asn_lines.line_seq_id)
line_id
, DECODE (interface_transaction_id, '856FORD', DECODE (comments,
'DUP_ASN_FORD_SUP', attribute17, shipment_num), '856',
shipment_num) rsn_num --asn_lines.shipment_num rsn_num --CR8_OF_20
--CR16_OF_38 FORD ASN
FROM xxpo01t_asn_inbound asn_lines
WHERE 1 = 1
AND interface_transaction_id IN ('856', '856FORD') -- = '856' -- CR16_OF_38
For FORD ASN
AND process_flag = '2'
--Start of CR8_OF_20
AND EXISTS (
SELECT orig_line_ref
FROM XXIF01W_INTERFACE_ERRORS asn_e
WHERE 1 = 1
AND asn_e.orig_line_ref = to_char (asn_lines.line_seq_id)
AND asn_e.orig_sys_ref = to_char (asn_lines.header_seq_id)
AND asn_e.attribute2 = to_char (asn_lines.file_id)
AND asn_e.interface_cd IN ('OF168', 'OF335') -- = 'OF168' --
CR16_OF_38 For FORD ASN
AND ebs_request_id = (
SELECT MAX (ebs_request_id)
FROM XXIF01W_INTERFACE_ERRORS asn_err
WHERE 1 = 1
AND asn_err.orig_sys_ref = asn_e.orig_sys_ref
AND asn_err.interface_cd = asn_e.interface_cd
)
) -- End of CR8_OF_20
Modified -
SELECT ship_to_organization_code PDC
, (
SELECT aps.segment1
FROM po_headers_all poh
, ap_suppliers aps
, xxpo01t_asn_inbound asn_l --MITS
WHERE 1 = 1
AND ROWNUM = 1
AND asn_l.header_seq_id = asn_lines.header_seq_id --MITS
AND asn_l.line_seq_id = asn_lines.line_seq_id --MITS
AND asn_l.file_id = asn_lines.file_id--MITS
AND poh.attribute1 (+) = asn_lines.document_num
AND poh.org_id = asn_h.org_id
AND aps.vendor_id (+) = poh.vendor_id
) vendor_number
, (
SELECT apss.vendor_site_code
FROM ap_suppliers asa
, ap_supplier_sites_all apss
, po_headers_all poh
, xxpo01t_asn_inbound asn_l --MITS
WHERE 1 = 1
AND ROWNUM = 1
AND asn_l.header_seq_id = asn_lines.header_seq_id --MITS
AND asn_l.line_seq_id = asn_lines.line_seq_id --MITS
AND asn_l.file_id = asn_lines.file_id--MITS
AND asa.vendor_id = apss.vendor_id
AND poh.vendor_id = asa.vendor_id (+)
AND poh.vendor_site_id = apss.vendor_site_id (+)
AND poh.org_id = asn_h.org_id
AND poh.attribute1 (+) = asn_lines.document_num
) vendor_site_code
,
--aps.segment1 vendor_number,
-- apss.vendor_site_code,
asn_lines.shipped_date
, TO_CHAR (asn_lines.line_seq_id) line_seq_id
, asn_lines.expected_receipt_date
, asn_lines.invoice_num
, asn_lines.item_num
, asn_lines.item_description
, asn_lines.quantity_shipped Quantity
, asn_lines.po_unit_price FOB
, asn_lines.document_num po_number
, asn_lines.document_line_num po_line_num
, (
SELECT poh.segment1
FROM po_headers_all poh
, xxpo01t_asn_inbound asn_l
WHERE 1 = 1
AND ROWNUM = 1
AND poh.attribute1 (+) = asn_l.document_num
AND poh.org_id = asn_l.org_id
AND asn_l.header_seq_id = asn_lines.header_seq_id
AND asn_l.line_seq_id = asn_lines.line_seq_id
AND asn_l.file_id = asn_lines.file_id
--Start of MITS CR-190509-0077
AND poh.type_lookup_code = 'STANDARD'
AND NVL (poh.cancel_flag, 'N') = 'N'
AND NVL (poh.closed_code, 'OPEN') = 'OPEN'
--End of MITS CR-190509-0077
) ebs_po_num
, asn_lines.container_num
, asn_lines.attribute11 Case_num
, --asn_lines.attribute10 Case_num,
asn_lines.currency_code currency
, asn_lines.currency_conversion_date exchange_date
, asn_lines.currency_conversion_rate exchange_rate
, asn_lines.currency_conversion_type exchange_type
, asn_lines.attribute4 arrangement_num
, asn_lines.attribute5 arrangement_line_num
,
--start of 1.1 shipped_seeded_part
asn_lines.substitute_item_num
,
--end of 1.1 shipped_seeded_part
asn_lines.CREATED_BY created_by
, asn_lines.CREATION_DATE creation_date
, fnd_profile.value ('USER_ID') last_updated_by
, sysdate LAST_UPDATE_DATE
, fnd_profile.value ('LOGIN_ID') LAST_UPDATE_LOGIN
, asn_lines.file_id file_id
, TO_CHAR (asn_lines.header_seq_id) header_seq_id
, TO_CHAR (asn_lines.line_seq_id) line_id
, asn_lines.invoice_num rsn_num --CR8_OF_20
FROM XXPO01T_ASN_AG_LAYOUT ASN_H
, xxpo01t_asn_inbound asn_lines
-- ,xxif01w_interface_errors asn_errors
WHERE asn_lines.invoice_num = asn_h.invoice_number
AND asn_lines.header_seq_id = asn_h.header_seq_id
AND asn_lines.file_id = asn_h.file_id
AND asn_h.process_flag = '2'
AND asn_lines.org_id = fnd_profile.value ('ORG_ID')
-- Start of CR8_OF_20
AND (test_flag = 'E'
OR EXISTS (
SELECT /*+ No_unnest(@SQ_101) */
orig_line_ref
FROM XXIF01W_INTERFACE_ERRORS asn_e
WHERE 1 = 1
AND asn_e.orig_line_ref = to_char (asn_lines.line_seq_id)
AND asn_e.orig_sys_ref = to_char (asn_lines.header_seq_id)
AND asn_e.attribute2 = to_char (asn_lines.file_id)
AND asn_e.interface_cd = 'OF075'
AND ebs_request_id = (
SELECT /*+ qb_name(SQ_101) */
MAX (ebs_request_id)
FROM XXIF01W_INTERFACE_ERRORS asn_err
WHERE 1 = 1
AND asn_err.orig_sys_ref = asn_e.orig_sys_ref
AND asn_err.interface_cd = asn_e.interface_cd
)
)
) -- End of CR8_OF_20
UNION ALL -- UNION CR8_OF_20
SELECT ship_to_organization_code
PDC
, (
SELECT aps.segment1
FROM ap_suppliers aps
, ap_supplier_sites_all apss
, xxpo01t_asn_inbound asn_l --MITS
WHERE 1 = 1
AND ROWNUM = 1
AND asn_l.header_seq_id = asn_lines.header_seq_id --MITS
AND asn_l.line_seq_id = asn_lines.line_seq_id --MITS
AND asn_l.file_id = asn_lines.file_id--MITS
AND apss.vendor_site_code = asn_lines.vendor_site_code
AND aps.vendor_id = apss.vendor_id
---and poh.org_id = asn_lines.org_id
--and aps.vendor_id = poh.vendor_id
)
vendor_number
, asn_lines.vendor_site_code
, asn_lines.shipped_date
, TO_CHAR (asn_lines.line_seq_id)
line_seq_id
, asn_lines.expected_receipt_date
, asn_lines.invoice_num
, asn_lines.item_num
, asn_lines.item_description
, asn_lines.quantity_shipped
Quantity
, NULL
FOB
, asn_lines.document_num
po_number
, asn_lines.document_line_num
po_line_num
, (
SELECT poh.segment1
FROM po_headers_all poh
, xxpo01t_asn_inbound asn_l
WHERE 1 = 1
AND ROWNUM = 1
AND poh.attribute1 (+) = asn_l.document_num
AND poh.org_id = asn_l.org_id
AND asn_l.header_seq_id = asn_lines.header_seq_id
AND asn_l.line_seq_id = asn_lines.line_seq_id
AND asn_l.file_id = asn_lines.file_id
--Start of MITS CR-190509-0077
AND poh.type_lookup_code = 'STANDARD'
AND NVL (poh.cancel_flag, 'N') = 'N'
AND NVL (poh.closed_code, 'OPEN') = 'OPEN'
--End of MITS CR-190509-0077
)
ebs_po_num
, DECODE (interface_transaction_id, '856', asn_lines.container_num,
'856FORD', NULL)
container_number
, --asn_lines.container_num,-- CR16_OF_38 For FORD ASN
NULL
Case_num
, NULL
currency
, NULL
exchange_date
, NULL
exchange_rate
, NULL
exchange_type
, NULL
arrangement_num
, NULL
arrangement_line_num
,
--start of 1.1 shipped_seeded_part
asn_lines.substitute_item_num
,
--end of 1.1 shipped_seeded_part
asn_lines.CREATED_BY
created_by
, asn_lines.CREATION_DATE
creation_date
, fnd_profile.value ('USER_ID')
last_updated_by
, sysdate
LAST_UPDATE_DATE
, fnd_profile.value ('LOGIN_ID')
LAST_UPDATE_LOGIN
, asn_lines.file_id
file_id
, TO_CHAR (asn_lines.header_seq_id)
header_seq_id
, TO_CHAR (asn_lines.line_seq_id)
line_id
, DECODE (interface_transaction_id, '856FORD', DECODE (comments,
'DUP_ASN_FORD_SUP', attribute17, shipment_num), '856',
shipment_num) rsn_num --asn_lines.shipment_num rsn_num --CR8_OF_20
--CR16_OF_38 FORD ASN
FROM xxpo01t_asn_inbound asn_lines
WHERE 1 = 1
AND interface_transaction_id IN ('856', '856FORD') -- = '856' -- CR16_OF_38
For FORD ASN
AND process_flag = '2'
--Start of CR8_OF_20
AND EXISTS (
SELECT /*+ No_unnest(@SQ_201) */
orig_line_ref
FROM XXIF01W_INTERFACE_ERRORS asn_e
WHERE 1 = 1
AND asn_e.orig_line_ref = to_char (asn_lines.line_seq_id)
AND asn_e.orig_sys_ref = to_char (asn_lines.header_seq_id)
AND asn_e.attribute2 = to_char (asn_lines.file_id)
AND asn_e.interface_cd IN ('OF168', 'OF335') -- = 'OF168' --
CR16_OF_38 For FORD ASN
AND ebs_request_id = (
SELECT /*+ qb_name(SQ_201) */
MAX (ebs_request_id)
FROM XXIF01W_INTERFACE_ERRORS asn_err
WHERE 1 = 1
AND asn_err.orig_sys_ref = asn_e.orig_sys_ref
AND asn_err.interface_cd = asn_e.interface_cd
)
) -- End of CR8_OF_20
